package system

import (
	"github.com/beego/beego/v2/client/orm"
	"time"
)

// ResearchGroup 研究方向分组表
type ResearchGroup struct {
	ResearchGroupId   int       `orm:"column(research_group_id);pk" description:"研究方向分组ID"`
	ResearchGroupName string    `json:"research_group_name" description:"研究方向分组名称"`
	ParentId          int       `json:"parent_id" description:"父类ID"`
	ChartPermissionId int       `json:"chart_permission_id" description:"品种权限ID"`
	Sort              int       `json:"sort" description:"排序"`
	State             int       `json:"state" description:"显示状态 0-不显示 1-显示"`
	CreateTime        time.Time `json:"create_time" description:"创建时间"`
	ModifyTime        time.Time `json:"modify_time" description:"更新时间"`
}

// GetResearchGroupList 获取研究方向分组列表
func GetResearchGroupList() (list []*ResearchGroup, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM research_group WHERE state = 1 ORDER BY sort`
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// GetResearchGroupById 主键获取分组
func GetResearchGroupById(groupId int) (item *ResearchGroup, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM research_group WHERE research_group_id = ? LIMIT 1`
	err = o.Raw(sql, groupId).QueryRow(&item)
	return
}

// ResearchGroupRelation 研究方向关系表
type ResearchGroupRelation struct {
	Id              int `orm:"column(id);pk"`
	ResearchGroupId int `json:"research_group_id" description:"分组ID"`
	AdminId         int `json:"admin_id" description:"研究员ID"`
}

// ResearchGroupRelationListByAdminId 根据研究员ID获取研究方向分组关系列表
func ResearchGroupRelationListByAdminId(adminId int) (list []*ResearchGroupRelation, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM research_group_relation WHERE admin_id = ?`
	_, err = o.Raw(sql, adminId).QueryRows(&list)
	return
}

// ResearchGroupRelation 研究方向分组关系表
type ResearchGroupRelationItem struct {
	ResearchGroupId int    `json:"research_group_id" description:"研究方向分组ID"`
	AdminId         int    `json:"admin_id" description:"研究员ID"`
	AdminName       string `json:"admin_name" description:"研究员姓名"`
}

// GetResearchGroupRelationList 获取研究方向分组关系列表
func GetResearchGroupRelationList(include int) (list []*ResearchGroupRelationItem, err error) {
	// 是否包含已禁止的研究员, 默认不包含
	condition := ` WHERE b.enabled = 1 `
	if include == 1 {
		condition = ` WHERE 1=1 `
	}
	o := orm.NewOrm()
	sql := `SELECT
				a.research_group_id,
				a.admin_id,
				b.real_name AS admin_name
			FROM
				research_group_relation AS a
			INNER JOIN admin AS b ON a.admin_id = b.admin_id `
	sql += condition
	sql += ` ORDER BY
				a.research_group_id ASC,
				a.admin_id ASC`
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// UpdateAdminResearchGroup 更新研究员研究方向分组
func UpdateAdminResearchGroup(adminId int, items []*ResearchGroupRelation) (err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()
	sql := `DELETE FROM research_group_relation WHERE admin_id = ?`
	if _, err = o.Raw(sql, adminId).Exec(); err != nil {
		return
	}
	itemsLen := len(items)
	if itemsLen > 0 {
		_, err = o.InsertMulti(itemsLen, items)
	}
	return
}

// AdminResearchGroupList 研究员研究方向分组列表
type AdminResearchGroupList struct {
	AdminId      int    `json:"admin_id"`
	GroupNameStr string `json:"group_name_str"`
}

// GetAdminResearchGroupListByAdminId 获取研究员研究方向分组列表
func GetAdminResearchGroupListByAdminId(adminIds string) (list []*AdminResearchGroupList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
				a.admin_id,
				GROUP_CONCAT(b.research_group_name) AS group_name_str
			FROM
				research_group_relation AS a
			INNER JOIN research_group AS b ON a.research_group_id = b.research_group_id
			WHERE
				a.admin_id IN (` + adminIds + `)
			GROUP BY
				a.admin_id`
	_, err = o.Raw(sql).QueryRows(&list)
	return
}