//go:generate ${GOBIN}/mockgen -destination engineer_mock.go -package=engineer ${GO_MODULE}/pkg/clients/engineer Interface
package engineer

import (
	"fmt"
	"strconv"
	"strings"

	"gorm.io/gorm"

	eutil "github.com/tidbcloud/jiraport/pkg/errors"
)

type Interface interface {
	GetFilterByName(SEEname string) (*bool, error)
	GetEngineerByEmail(email string) (*Engineer, error)
	GetSecondaryByEmail(assigneeEmail string) (*Engineer, error)
	GetEscalationChainEngineerByAssigneeEmail(assigneeEmail string) ([]Engineer, error)
	GetFiremasters() ([]Engineer, error)
	GetEngineersByCategoryIDs(categoryIDs []int) ([]Engineer, error)
	GetSEEs(filter bool) ([]Engineer, error)
	GetComponentsByUserEmail(email string) ([]string, error)
	GetTSEByTenantID(tenant string) (*Engineer, error)
	GetEmailByAccountID(accountID string) (*string, error)
}

type Client struct {
	policyDB *gorm.DB
}

func New(db *gorm.DB) (*Client, error) {
	return &Client{
		policyDB: db,
	}, nil
}

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"`
	JiraUserKey string `gorm:"column:jira_user_key"`
	Bot         string `gorm:"column:bot"`
}

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

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

func (eng *Engineer) Show() string {
	return fmt.Sprintf("%s(%s)", eng.Name, eng.Email)
}

func (eng *Engineer) BotType() string {
	return eng.Bot
}

func (c *Client) GetFilterByName(SEEname string) (*bool, error) {
	sql := `
        SELECT
            eng.filter AS filter,
        FROM
            engineers AS eng
        WHERE
            eng.engineer_name = ?
        LIMIT 1
    `
	filter := false
	res := c.policyDB.Raw(sql, SEEname).Scan(&filter)
	return &filter, eutil.CheckGORMErr(res)
}

func (c *Client) GetEmailByAccountID(accountID string) (*string, error) {
	sql := `select eng.email as email from engineers as eng where eng.jira_user_id = ? limit 1`
	var email string
	res := c.policyDB.Raw(sql, accountID).Scan(&email)
	return &email, eutil.CheckGORMErr(res)
}

func (c *Client) getTempWeekID() (float32, error) {
	sql := `
		SELECT
			MAX(id) as week_id
		FROM
			t_week_id
		WHERE
			offset < now();
		` // #nosec G101
	var weekID float32
	res := c.policyDB.Raw(sql).Scan(&weekID)
	if res.Error != nil {
		return weekID, res.Error
	}
	tempDecimal2, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", weekID), 64)
	return float32(tempDecimal2), nil
}

func getTempDayID(db *gorm.DB) (float32, error) {
	sql := `
		SELECT
			MAX(id) as day_id
		FROM
			t_day_id
		WHERE
			offset < now();
		`
	var dayID float32
	res := db.Raw(sql).Scan(&dayID)
	if res.Error != nil {
		return dayID, res.Error
	}
	tempDecimal2, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", dayID), 64)
	return float32(tempDecimal2), nil
}

func (c *Client) GetEngineerByEmail(email string) (*Engineer, error) {
	sql := `
        SELECT
            eng.engineer_name AS name,
            eng.email AS email,
            eng.open_id AS open_id,
            IF(eng.workplace = 'china',eng.new_user_id, eng.user_id) AS user_id,
            eng.jira_user_key AS jira_user_key,
            IF(eng.workplace = 'china','new_firewatch', 'default') AS bot
        FROM
            engineers AS eng
        WHERE
            eng.email = ?
        LIMIT 1
    `
	engineer := Engineer{}
	res := c.policyDB.Raw(sql, email).Scan(&engineer)
	return &engineer, eutil.CheckGORMErr(res)
}

func (c *Client) GetSecondaryByEmail(assigneeEmail string) (*Engineer, error) {
	sql := `
		SELECT
			e2.engineer_name AS name,
			e2.email AS email,
			e2.open_id AS open_id,
			IF(e2.workplace = 'china',e2.new_user_id, e2.user_id) AS user_id,
			IF(e2.workplace = 'china','new_firewatch', 'default') AS bot
		FROM
			engineers AS e1 natural
			JOIN shift AS s1
			JOIN shift AS s2 ON s1.category_id = s2.category_id
			JOIN engineers AS e2 ON s2.engineer_id = e2.engineer_id
		WHERE
			s1.week_id = ?
			AND s2.week_id = ?
			AND s1.engineer_id <> s2.engineer_id
			AND e1.email = ?
	`
	engineers := make([]Engineer, 0)
	tempWeek, _ := c.getTempWeekID()
	tempDecimal2, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", tempWeek), 64)
	res := c.policyDB.Raw(sql, tempDecimal2, tempDecimal2, assigneeEmail).Scan(&engineers)
	if len(engineers) > 0 {
		return &engineers[0], eutil.CheckGORMErr(res)
	} else {
		return nil, eutil.CheckGORMErr(res)
	}
}

func (c *Client) GetEscalationChainEngineerByAssigneeEmail(assigneeEmail string) ([]Engineer, error) {
	sql := `
        SELECT DISTINCT
            escalation.engineer_name AS name,
            escalation.email AS email,
            escalation.open_id AS open_id,
            IF(escalation.workplace = 'china',escalation.new_user_id, escalation.user_id) AS user_id,
            IF(escalation.workplace = 'china','new_firewatch', 'default') AS bot
        FROM
            engineers AS escalation,
            engineers AS assignee
        WHERE
            assignee.email = ?
            AND assignee.escalation_flag = escalation.engineer_id
    `
	engineers := make([]Engineer, 0)
	res := c.policyDB.Raw(sql, assigneeEmail).Scan(&engineers)
	return engineers, eutil.CheckGORMErr(res)
}

func (c *Client) GetFiremasters() ([]Engineer, error) {
	sql := `
        SELECT DISTINCT
            eng.engineer_name AS name,
            eng.email AS email,
            eng.open_id AS open_id,
            IF(eng.workplace = 'china',eng.new_user_id, eng.user_id) AS user_id,
            eng.jira_user_key AS jira_user_key,
            IF(eng.workplace = 'china','new_firewatch', 'default') AS bot
        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 = ?;
    `
	tempWeekID, _ := c.getTempWeekID()
	engineers := make([]Engineer, 0)
	tempDecimal2, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", tempWeekID), 64)
	res := c.policyDB.Raw(sql, tempDecimal2).Scan(&engineers)
	return engineers, eutil.CheckGORMErr(res)
}

func (c *Client) GetEngineersByCategoryIDs(categoryIDs []int) ([]Engineer, error) {
	sql := `
        SELECT DISTINCT
            eng.engineer_name AS name,
            eng.email AS email,
            eng.open_id AS open_id,
            IF(eng.workplace = 'china',eng.new_user_id, eng.user_id) AS user_id,
            IF(eng.workplace = 'china','new_firewatch', 'default') AS bot
        FROM
            shift,
            engineers AS eng
        WHERE
            shift.category_id IN ?
			AND shift.engineer_id = eng.engineer_id
			AND shift.week_id = ?;
    `
	engineers := make([]Engineer, 0)
	tempWeekID, _ := c.getTempWeekID()
	tempDecimal2, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", tempWeekID), 64)
	res := c.policyDB.Raw(sql, categoryIDs, tempDecimal2).Scan(&engineers)
	return engineers, eutil.CheckGORMErr(res)
}

func (c *Client) GetSEEs(filter bool) ([]Engineer, error) {
	engineers := make([]Engineer, 0)
	dayID, err := getTempDayID(c.policyDB)
	if err != nil {
		return engineers, err
	}
	var sql string
	if filter {
		sql = `
    	SELECT DISTINCT
            eng.engineer_name AS name,
            eng.email AS email,
            eng.open_id AS open_id,
            IF(eng.workplace = 'china',eng.new_user_id, eng.user_id) AS user_id,
            eng.jira_user_key AS jira_user_key,
            IF(eng.workplace = 'china','new_firewatch', 'default') AS bot
        FROM
            shift,
            engineers AS eng,
            teams AS t,
            categories AS c
        WHERE
				IF(c.from_hour <= c.to_hour,
					HOUR(UTC_TIMESTAMP()) BETWEEN c.from_hour AND c.to_hour,
					HOUR(UTC_TIMESTAMP()) >= c.from_hour OR HOUR(UTC_TIMESTAMP()) <= c.to_hour)
				AND eng.team_id = t.team_id
                AND t.category_id = c.category_id
                AND shift.category_id = t.category_id
                AND shift.engineer_id = eng.engineer_id
                AND shift.week_id = ?
                AND c.schedule_type = "t_day_id"
				AND c.filter = 1
				;
    `
	} else {
		sql = `
    	SELECT DISTINCT
            eng.engineer_name AS name,
            eng.email AS email,
            eng.open_id AS open_id,
            IF(eng.workplace = 'china',eng.new_user_id, eng.user_id) AS user_id,
            eng.jira_user_key AS jira_user_key,
            IF(eng.workplace = 'china','new_firewatch', 'default') AS bot
        FROM
            shift,
            engineers AS eng,
            teams AS t,
            categories AS c
        WHERE
				IF(c.from_hour <= c.to_hour,
					HOUR(UTC_TIMESTAMP()) BETWEEN c.from_hour AND c.to_hour,
					HOUR(UTC_TIMESTAMP()) >= c.from_hour OR HOUR(UTC_TIMESTAMP()) <= c.to_hour)
				AND eng.team_id = t.team_id
                AND t.category_id = c.category_id
                AND shift.category_id = t.category_id
                AND shift.engineer_id = eng.engineer_id
                AND shift.week_id = ?
                AND c.schedule_type = "t_day_id"
				;
    `
	}
	res := c.policyDB.Raw(sql, dayID).Scan(&engineers)
	return engineers, eutil.CheckGORMErr(res)
}

func (c *Client) GetComponentsByUserEmail(email string) ([]string, error) {
	sql := `
		SELECT IFNULL(c.components, '') AS components
		FROM engineers AS eng, teams AS t, categories AS c
		WHERE 
			eng.email = ? AND eng.team_id = t.team_id AND t.category_id = c.category_id;
	`
	components := make([]string, 0)
	res := c.policyDB.Raw(sql, email).Scan(&components)
	allComponents := make([]string, 0)
	for _, componentCategory := range components {
		componentCategories := strings.Split(componentCategory, ",")
		for _, component := range componentCategories {
			trimed := strings.TrimSpace(component)
			if trimed != "" {
				allComponents = append(allComponents, trimed)
			}
		}
	}
	return allComponents, eutil.CheckGORMErr(res)
}

func (c *Client) GetTSEByTenantID(tenant string) (*Engineer, error) {
	sql := `SELECT DISTINCT
				eng.engineer_name AS name,
				eng.email AS email,
				eng.open_id AS open_id,
				IF(eng.workplace = 'china',eng.new_user_id, eng.user_id) AS user_id,
				IF(eng.workplace = 'china','new_firewatch', 'default') AS bot
			FROM
				tse_mappings as t,
				engineers AS eng
			WHERE
				t.engineer_id = eng.engineer_id and
				t.tenant_id = ?;`
	var engineer *Engineer
	res := c.policyDB.Raw(sql, tenant).Scan(&engineer)
	return engineer, eutil.CheckGORMErr(res)
}