package models

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

// VarietyClassify 标签库分类表
type VarietyClassify struct {
	VarietyClassifyId int       `orm:"column(variety_classify_id);pk" description:"标签分类ID"`
	ClassifyName      string    `json:"classify_name" description:"标签分类名称"`
	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:"更新时间"`
}

// GetVarietyClassifyList 获取标签分类列表
func GetVarietyClassifyList() (list []*VarietyClassify, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM variety_classify WHERE state = 1 ORDER BY sort`
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// GetVarietyClassifyById 主键获取分类
func GetVarietyClassifyById(classifyId int) (item *VarietyClassify, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM variety_classify WHERE variety_classify_id = ? LIMIT 1`
	err = o.Raw(sql, classifyId).QueryRow(&item)
	return
}

// VarietyTag 标签表
type VarietyTag struct {
	VarietyTagId                int       `orm:"column(variety_tag_id);pk" description:"标签ID"`
	VarietyClassifyId           int       `json:"variety_classify_id" description:"标签分类ID"`
	VarietyClassifyName         string    `json:"variety_classify_name" description:"标签分类名称"`
	TagName                     string    `json:"tag_name" description:"标签名称"`
	ChartPermissionClassifyName string    `json:"chart_permission_classify_name" description:"品种权限分类名"`
	ChartPermissionId           int       `json:"chart_permission_id" description:"品种权限ID"`
	ChartPermissionName         string    `json:"chart_permission_name" description:"品种权限名称"`
	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:"更新时间"`
}

// GetVarietyTagById 主键获取标签
func GetVarietyTagById(tagId int) (item *VarietyTag, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM variety_tag WHERE variety_tag_id = ? LIMIT 1`
	err = o.Raw(sql, tagId).QueryRow(&item)
	return
}

// GetVarietyTagList 获取标签列表
func GetVarietyTagList() (list []*VarietyTag, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM variety_tag WHERE state = 1 ORDER BY sort`
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// GetVarietyTagListByClassifyId 根据分类获取标签列表
func GetVarietyTagListByClassifyId(classifyId int) (list []*VarietyTag, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM variety_tag WHERE variety_classify_id = ? AND state = 1 ORDER BY sort`
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// ResearchVarietyTagRelation 研究员标签关系表
type ResearchVarietyTagRelation struct {
	Id           int `orm:"column(id);pk"`
	VarietyTagId int `json:"variety_tag_id" description:"标签ID"`
	AdminId      int `json:"admin_id" description:"研究员ID"`
}

// ResearchVarietyTagRelationListByAdminId 根据研究员ID获取标签关系列表
func ResearchVarietyTagRelationListByAdminId(adminId int) (list []*ResearchVarietyTagRelation, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM research_variety_tag_relation WHERE admin_id = ?`
	_, err = o.Raw(sql, adminId).QueryRows(&list)
	return
}

type ResearchTagRelation struct {
	Id              int `json:"id"`
	ResearchGroupId int `json:"research_group_id" description:"分组ID"`
	AdminId         int `json:"admin_id" description:"研究员ID"`
}

// ResearchTagRelationListByAdminId 根据研究员ID获取标签关系列表
func ResearchTagRelationListByAdminId(adminId int) (list []*ResearchTagRelation, err error) {
	o := orm.NewOrm()
	sql := `SELECT id, variety_tag_id AS research_group_id, admin_id FROM research_variety_tag_relation WHERE admin_id = ?`
	_, err = o.Raw(sql, adminId).QueryRows(&list)
	return
}

// ResearchVarietyTagRelationItem 研究员标签关系
type ResearchVarietyTagRelationItem struct {
	VarietyTagId int    `json:"variety_tag_id" description:"标签ID"`
	AdminId      int    `json:"admin_id" description:"研究员ID"`
	AdminName    string `json:"admin_name" description:"研究员姓名"`
}

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

// UpdateAdminVarietyTagRelation 更新研究员标签关系
func UpdateAdminVarietyTagRelation(adminId int, items []*ResearchVarietyTagRelation) (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_variety_tag_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
}

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

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