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:"更新时间"`
}

func (varietyClassify *VarietyClassify) Add() (lastId int64, err error) {
	o := orm.NewOrm()
	lastId, err = o.Insert(varietyClassify)
	if err != nil {
		return
	}
	varietyClassify.VarietyClassifyId = int(lastId)
	return
}

func (varietyClassify *VarietyClassify) Update(cols []string) (err error) {
	o := orm.NewOrm()
	_, err = o.Update(varietyClassify, cols...)
	return
}

// GetEnableVarietyClassifyList 获取启用的标签分类列表
func GetEnableVarietyClassifyList(hasForbidden bool) (list []*VarietyClassify, err error) {
	o := orm.NewOrm()
	sql := ""
	if hasForbidden {
		sql = `SELECT * FROM variety_classify ORDER BY sort ASC`
	} else {
		sql = `SELECT * FROM variety_classify WHERE state = 1 ORDER BY sort ASC`
	}

	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// GetVarietyClassifyPageList 获取标签分类分页列表
func GetVarietyClassifyPageList(condition string, pars []interface{}, orderRule string, startSize, pageSize int) (total int, list []*VarietyClassify, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM variety_classify WHERE 1=1 `
	sql += condition
	if orderRule == "" {
		orderRule = "modify_time DESC"
	}
	sql += ` ORDER BY ` + orderRule

	totalSQL := `SELECT COUNT(1) total FROM (` + sql + `) z`
	if err = o.Raw(totalSQL, pars).QueryRow(&total); err != nil {
		return
	}
	sql += ` LIMIT ?,?`

	_, err = o.Raw(sql, pars, startSize, pageSize).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
}

// GetVarietyClassifyByName 名称获取分类
func GetVarietyClassifyByName(classifyName string) (item *VarietyClassify, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM variety_classify WHERE classify_name = ? LIMIT 1`
	err = o.Raw(sql, classifyName).QueryRow(&item)
	return
}

// GetCountEnableClassifyByClassifyId 根据分类ID获取启用分类数
func GetCountEnableClassifyByClassifyId(classifyId int) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) FROM variety_classify WHERE state = 1 AND variety_classify_id != ?`
	err = o.Raw(sql, classifyId).QueryRow(&count)
	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:"更新时间"`
}

func (varietyTag *VarietyTag) Add() (lastId int64, err error) {
	o := orm.NewOrm()
	lastId, err = o.Insert(varietyTag)
	if err != nil {
		return
	}
	varietyTag.VarietyTagId = int(lastId)
	return
}

func (varietyTag *VarietyTag) Update(cols []string) (err error) {
	o := orm.NewOrm()
	_, err = o.Update(varietyTag, cols...)
	return
}

// 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
}

// GetCountEnableTagByTagId 根据标签ID获取启用标签数
func GetCountEnableTagByTagId(classifyId int) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) FROM variety_tag WHERE state = 1 AND variety_tag_id != ?`
	err = o.Raw(sql, classifyId).QueryRow(&count)
	return
}

// GetEnableVarietyTagList 获取启用状态标签列表
func GetEnableVarietyTagList(hasForbidden bool) (list []*VarietyTag, err error) {
	o := orm.NewOrm()
	var sql string
	if hasForbidden {
		sql = `SELECT * FROM variety_tag ORDER BY sort`
	}else {
		sql = `SELECT * FROM variety_tag WHERE state = 1 ORDER BY sort`
	}

	_, err = o.Raw(sql).QueryRows(&list)
	return
}

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

// GetVarietyTagPageList 获取标签分页列表
func GetVarietyTagPageList(condition string, pars []interface{}, orderRule string, startSize, pageSize int) (total int, list []*VarietyTag, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM variety_tag WHERE 1=1 `
	sql += condition
	if orderRule == "" {
		orderRule = "modify_time DESC"
	}
	sql += ` ORDER BY ` + orderRule

	totalSQL := `SELECT COUNT(1) total FROM (` + sql + `) z`
	if err = o.Raw(totalSQL, pars).QueryRow(&total); err != nil {
		return
	}
	sql += ` LIMIT ?,?`

	_, err = o.Raw(sql, pars, startSize, pageSize).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
}
