package engineer

import (
	"gorm.io/gorm"

	eutil "jiraport/pkg/errors"
)

type Engineer struct {
	Name   string `gorm:"column:name"`
	Email  string `gorm:"column:email"`
	OpenID string `gorm:"column:open_id"`
	UserID string `gorm:"column:user_id"`
}

func (eng *Engineer) Type() string {
	return "person"
}

func (eng *Engineer) Id() string {
	return eng.UserID
}

func GetEngineerByEmail(db *gorm.DB, email string) (*Engineer, error) {
	const SELECT_ENGINEER_BY_EMAIL = `
        SELECT
            eng.engineer_name AS name,
            eng.email AS email,
            eng.open_id AS open_id,
            eng.user_id AS user_id
        FROM
            engineers AS eng
        WHERE
            eng.email = ?
        LIMIT 1
    `
	engineer := Engineer{}
	res := db.Raw(SELECT_ENGINEER_BY_EMAIL, email).Scan(&engineer)
	return &engineer, eutil.CheckGORMErr(res)
}

func GetEscalationEngineerByAssigneeEmail(db *gorm.DB, assigneeEmail string) ([]Engineer, error) {
	const SELECT_ESCALATION_ENGINEER_BY_ASSIGNEE_EMAIL = `
        SELECT DISTINCT
            escalation.engineer_name AS name,
            escalation.email AS email,
            escalation.open_id AS open_id,
            escalation.user_id AS user_id
        FROM
            engineers AS escalation,
            teams AS t1,
            engineers AS assignee,
            teams AS t2
        WHERE
            escalation.team_id = t1.team_id
                AND assignee.team_id = t2.team_id
                AND t1.category_id = t2.category_id
                AND assignee.email = ?
                AND escalation.escalation_flag = 1
                AND escalation.available = 1
    `
	engineers := make([]Engineer, 0)
	res := db.Raw(SELECT_ESCALATION_ENGINEER_BY_ASSIGNEE_EMAIL, assigneeEmail).Scan(&engineers)
	return engineers, eutil.CheckGORMErr(res)
}

func GetEscalationChainEngineerByAssigneeEmail(db *gorm.DB, assigneeEmail string) ([]Engineer, error) {
	const SELECT_ESCALATION_CHAIN_ENGINEER_BY_ASSIGNEE_EMAIL = `
        SELECT DISTINCT
            escalation.engineer_name AS name,
            escalation.email AS email,
            escalation.open_id AS open_id,
            escalation.user_id AS user_id
        FROM
            engineers AS escalation,
            engineers AS assignee
        WHERE
            assignee.email = ?
                AND assignee.escalation_flag = escalation.engineer_id
    `
	engineers := make([]Engineer, 0)
	res := db.Raw(SELECT_ESCALATION_CHAIN_ENGINEER_BY_ASSIGNEE_EMAIL, assigneeEmail).Scan(&engineers)
	return engineers, eutil.CheckGORMErr(res)
}

func GetFiremasters(db *gorm.DB) ([]Engineer, error) {
	const SELECT_FIREMASTERS = `
        SELECT DISTINCT
            eng.engineer_name AS name,
            eng.email AS email,
            eng.open_id AS open_id,
            eng.user_id AS user_id
        FROM
            shift,
            engineers AS eng,
            fire_master_time_cat_map AS tmmap
        WHERE
            tmmap.hour = (SELECT HOUR(UTC_TIMESTAMP()))
                AND shift.category_id = tmmap.category_id
                AND shift.engineer_id = eng.engineer_id
                AND shift.week_id = (SELECT FN_WEEK_ID(NOW()));
    `
	engineers := make([]Engineer, 0)
	res := db.Raw(SELECT_FIREMASTERS).Scan(&engineers)
	return engineers, eutil.CheckGORMErr(res)
}

func GetEngineersByCategoryIDs(db *gorm.DB, categoryIDs []int) ([]Engineer, error) {
	const SELECT_ENGINEERS_BY_CATEGORY_ID = `
        SELECT DISTINCT
            eng.engineer_name AS name,
            eng.email AS email,
            eng.open_id AS open_id,
            eng.user_id AS user_id
        FROM
            shift,
            engineers AS eng
        WHERE
            shift.category_id IN ?
                AND shift.engineer_id = eng.engineer_id
                AND shift.week_id = (SELECT FN_WEEK_ID(NOW()));
    `
	engineers := make([]Engineer, 0)
	res := db.Raw(SELECT_ENGINEERS_BY_CATEGORY_ID, categoryIDs).Scan(&engineers)
	return engineers, eutil.CheckGORMErr(res)
}