package db

import (
	"io/ioutil"
	"olympus/oncall/backend/model"
	"olympus/oncall/backend/pkg/log"
	"sort"

	"github.com/spf13/viper"
	"gopkg.in/yaml.v2"
)

// TL's view: generate this round and next round's schedule.
func GetCategoryShiftList(categoryId int) (*model.TShiftList, error) {
	categoryInfo, err := GetCategoryInfo(categoryId)
	if err != nil {
		return nil, err
	}
	thisRound := categoryInfo.ThisRound
	if err != nil {
		return nil, err
	}

	content, err := ioutil.ReadFile(viper.GetViper().GetString("round-path"))
	if err != nil {
		log.Error("msg", "fail to load round file")
	}
	round := &Round{}

	err = yaml.Unmarshal(content, round)
	if err != nil {
		log.Error("msg", "fail to unmarshal round file")
	}
	var shift Shift
	for _, v := range round.Shifts {
		if v.Category == categoryId {
			shift = *v
			break
		}
	}
	if shift.Category == 0 {
		log.Error("msg", "cannot find category robin information", "category id", categoryId)
		return nil, nil
	}
	// 1. calculate the total weeks(total_week) of next round. round-length * period
	categoryInf, err := GetCategoryInfo(categoryId)
	if err != nil {
		return nil, err
	}
	weekTotal := len(shift.Robin) * categoryInf.Period

	currentWeek, err := GetTempWeekId()
	if err != nil {
		return nil, err
	}
	var categoryName string
	const GET_CATEGORY_LIST = `
		SELECT
			concat(c1.category_name, ' - ', c2.category_name) as category_name,
			shift.engineer_id as engineer_id,
			engineer_name,
			if(
				engineer_en_name is null,
				"",
				concat ("(", engineer_en_name, ")")
			) as engineer_en_name,
			team_name,
			week_id,
			shift.category_id as category_id,
			offset,
			offset2
		FROM
			categories AS c1,
			categories AS c2,
			shift,
			engineers,
			teams,
			t_week_id
		WHERE
			c1.category_id = c2.parent_category_id
			AND c2.category_id = shift.category_id
			AND shift.engineer_id = engineers.engineer_id
			AND engineers.team_id = teams.team_id
			AND week_id >= ?
			AND week_id = t_week_id.id
			AND shift.category_id = ?
		ORDER BY
			week_id,
			engineer_name`
	shiftViewRes := make([]ShiftViewList, 0)
	res := GetDB().Raw(GET_CATEGORY_LIST, thisRound, categoryId).Scan(&shiftViewRes)
	if res.Error != nil {
		log.Error("msg", "error: get overview", "error info", res.Error)
		return nil, err
	}

	shiftMap := make(model.TShiftList)
	for _, v := range shiftViewRes {
		categoryName = v.CategoryName
		if _, exist := shiftMap[v.CategoryName]; exist {
			shiftMap[v.CategoryName] = append(shiftMap[v.CategoryName], &model.EWPair{EngineerId: v.EngineerId, Name: v.EngineerName, EnName: v.EngineerEnName, Team: v.TeamName, WeekId: v.WeekId, CategoryId: v.CategoryId, Week: v.Offset + " - " + v.Offset2, OnDuty: v.WeekId == currentWeek, Changeable: v.WeekId >= currentWeek})
		} else {
			shiftMap[v.CategoryName] = make([]*model.EWPair, 0)
			shiftMap[v.CategoryName] = append(shiftMap[v.CategoryName], &model.EWPair{EngineerId: v.EngineerId, Name: v.EngineerName, EnName: v.EngineerEnName, Team: v.TeamName, WeekId: v.WeekId, CategoryId: v.CategoryId, Week: v.Offset + " - " + v.Offset2, OnDuty: v.WeekId == currentWeek, Changeable: v.WeekId >= currentWeek})
		}
	}

	const GET_NULL_WEEK = `
		select
			t_week_id.id as week_id,
			offset,
			offset2
		from
			t_week_id
			left join (
				select
					*
				from
					shift
				where
					category_id = ?
			) as r on t_week_id.id = r.week_id
		where
			t_week_id.id between ?
			and ?
			and r.engineer_id is null`

	type NullSchedule struct {
		WeekId  float32 `gorm:"column:week_id"`
		Offset  string  `gorm:"column:offset"`
		Offset2 string  `gorm:"column:offset2"`
	}
	nullScheduleList := make([]NullSchedule, 0)
	resSql := GetDB().Raw(GET_NULL_WEEK, categoryId, thisRound, categoryInfo.NextRound+float32(weekTotal-1)).Scan(&nullScheduleList)
	if resSql.Error != nil {
		log.Error("msg", "error: get null week schedule", "category id", categoryId, "error info", res.Error)
		return nil, err
	}

	for _, v := range nullScheduleList {
		shiftMap[categoryName] = append(shiftMap[categoryName], &model.EWPair{EngineerId: -1, Name: "TBD", EnName: "TBD", Team: "TBD", WeekId: v.WeekId, CategoryId: categoryId, Week: v.Offset + " - " + v.Offset2, OnDuty: false, Changeable: v.WeekId >= currentWeek})
	}
	sort.SliceStable(shiftMap[categoryName], func(i, j int) bool {
		return shiftMap[categoryName][i].WeekId < shiftMap[categoryName][j].WeekId
	})
	return &shiftMap, nil
}

func GetRobin(categoryId int) ([]string, error) {
	categoryInf, err := GetCategoryInfo(categoryId)
	if err != nil {
		return make([]string, 0), err
	}
	const GET_ROBIN = `
		select
			engineer_name
		from
			shift
			left join engineers on shift.engineer_id = engineers.engineer_id
		where
			week_id >= ?
			and category_id = ?
		order by
			week_id`
	res := make([]string, 0)
	resSql := GetDB().Raw(GET_ROBIN, categoryInf.NextRound, categoryId).Scan(&res)
	if resSql.Error != nil {
		log.Error("msg", "error: get robin", "category id", categoryId, "error info", resSql.Error)
		return res, resSql.Error
	}
	return res, nil
}

func GetTeams(categoryId int) ([]*model.Team, error) {
	const GET_TEAMS_BY_CATEGORY = `
		select
			team_id,
			team_name
		from
			teams
		where
			category_id = ?`
	res := make([]*model.Team, 0)
	resSql := GetDB().Raw(GET_TEAMS_BY_CATEGORY, categoryId).Scan(&res)
	if resSql.Error != nil {
		log.Error("msg", "error: get teams", "category id", categoryId, "error info", resSql.Error)
	}
	return res, nil
}

// Get all the information related to a category.
func GetCategoryInfo(categoryId int) (*CategoryInf, error) {
	const GET_ALL_CATEGORY_INFO = `
		select
			c.category_name as category_name,
			c.category_id as category_id,
			period,
			engineer_num,
			role_num,
			this_round,
			next_round
		from
			policies as p
			left join categories as c on c.category_id = p.category_id
		where
			c.category_id = ?`
	var categoryInf CategoryInf
	res := GetDB().Raw(GET_ALL_CATEGORY_INFO, categoryId).Scan(&categoryInf)
	if res.Error != nil {
		log.Error("msg", "error: get category information", "category id", categoryId, "error info", res.Error)
		return nil, res.Error
	}
	return &categoryInf, nil
}