package data_manage

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

type ChartClassify struct {
	ChartClassifyId   int       `orm:"column(chart_classify_id);pk"`
	ChartClassifyName string    `description:"分类名称"`
	ParentId          int       `description:"父级id"`
	HasData           int       `description:"是否含有指标数据"`
	CreateTime        time.Time `description:"创建时间"`
	ModifyTime        time.Time `description:"修改时间"`
	SysUserId         int       `description:"创建人id"`
	SysUserRealName   string    `description:"创建人姓名"`
	Level             int       `description:"层级"`
	UniqueCode        string    `description:"唯一编码"`
	Sort              int       `description:"排序字段，越小越靠前，默认值：10"`
	Source            int       `description:"1：ETA图库；2：商品价格曲线"`
}

func AddChartClassify(item *ChartClassify) (lastId int64, err error) {
	o := orm.NewOrmUsingDB("data")
	lastId, err = o.Insert(item)
	return
}

type AddChartClassifyReq struct {
	ChartClassifyName string `description:"分类名称"`
	ParentId          int    `description:"父级id,第一级传0"`
	Level             int    `description:"层级,第一级传0，其余传上一级的层级"`
}

func GetChartClassifyCount(chartClassifyName string, parentId, source int) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT COUNT(1) AS count FROM chart_classify WHERE parent_id=? AND source = ? AND chart_classify_name=? `
	err = o.Raw(sql, parentId, source, chartClassifyName).QueryRow(&count)
	return
}

type EditChartClassifyReq struct {
	ChartClassifyName string `description:"分类名称"`
	ChartClassifyId   int    `description:"分类id"`
}

func GetChartClassifyById(classifyId int) (item *ChartClassify, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM chart_classify WHERE chart_classify_id=? `
	err = o.Raw(sql, classifyId).QueryRow(&item)
	return
}

func GetChartClassifyCountById(classifyId int) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT count(1) AS count FROM chart_classify WHERE chart_classify_id=? `
	err = o.Raw(sql, classifyId).QueryRow(&count)
	return
}

func EditChartClassify(classifyId, source int, chartClassifyName string) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `UPDATE chart_classify SET chart_classify_name=?,modify_time=NOW() WHERE chart_classify_id=? AND source = ? `
	_, err = o.Raw(sql, chartClassifyName, classifyId, source).Exec()
	return
}

type DeleteChartClassifyReq struct {
	ChartClassifyId int `description:"分类id"`
	ChartInfoId     int `description:"指标id"`
}

func GetChartInfoCountByClassifyId(classifyId int) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(1) AS count FROM chart_info AS a
				WHERE a.chart_classify_id IN(
				SELECT t.chart_classify_id FROM 
				(
				SELECT rd.*
				FROM (SELECT * FROM chart_classify WHERE parent_id IS NOT NULL) rd,
					 (SELECT @pid := ?) pd 
				WHERE FIND_IN_SET(parent_id, @pid) > 0 
				  AND @pid := CONCAT(@pid, ',', chart_classify_id) 
				UNION SELECT * FROM chart_classify WHERE chart_classify_id = @pid
				)AS t
				) `
	err = o.Raw(sql, classifyId).QueryRow(&count)
	return
}

func DeleteChartClassify(classifyId int) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` DELETE FROM chart_classify
				WHERE chart_classify_id IN(
				SELECT t.chart_classify_id FROM
				(
				SELECT rd.*
				FROM (SELECT * FROM chart_classify WHERE parent_id IS NOT NULL) rd,
				(SELECT @pid := ?) pd
				WHERE FIND_IN_SET(parent_id, @pid) > 0
				AND @pid := CONCAT(@pid, ',', chart_classify_id)
				UNION SELECT * FROM chart_classify WHERE chart_classify_id = @pid
				)AS t
				) `
	_, err = o.Raw(sql, classifyId).Exec()
	return
}

// GetChartClassifyByParentId
// @param source int 1：ETA图库；2：商品价格曲线；3：相关性图表
func GetChartClassifyByParentId(parentId, source int) (items []*ChartClassifyItems, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM chart_classify WHERE parent_id=? AND source = ? order by sort asc,chart_classify_id asc`
	_, err = o.Raw(sql, parentId, source).QueryRows(&items)
	return
}

// GetChartClassifyAll
// @param source int 1：ETA图库；2：商品价格曲线；3：相关性图表
func GetChartClassifyAll(source int) (items []*ChartClassifyItems, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM chart_classify WHERE parent_id<>0 AND source = ? order by sort asc,chart_classify_id asc`
	_, err = o.Raw(sql, source).QueryRows(&items)
	return
}

type ChartClassifyItems struct {
	ChartClassifyId   int `description:"分类id"`
	ChartInfoId       int `description:"指标id"`
	ChartClassifyName string
	ParentId          int
	Level             int    `description:"层级"`
	Sort              int    `description:"排序字段，越小越靠前，默认值：10"`
	UniqueCode        string `description:"唯一编码"`
	Source            int    `description:"来源id"`
	SourceName        string `description:"来源名称"`
	SysUserId         int    `description:"创建人id"`
	SysUserRealName   string `description:"创建人姓名"`
	DateType          int    `description:"日期类型:1:00年至今,2:10年至今,3:15年至今，4：年初至今，5:自定义时间"`
	StartDate         string `description:"自定义开始日期"`
	EndDate           string `description:"自定义结束日期"`
	ChartType         int    `description:"生成样式:1:曲线图,2:季节性图"`
	Calendar          string `description:"公历/农历"`
	SeasonStartDate   string `description:"季节性图开始日期"`
	SeasonEndDate     string `description:"季节性图开始日期"`
	Children          []*ChartClassifyItems
}

type ChartClassifyListResp struct {
	AllNodes []*ChartClassifyItems
	Language string `description:"指标的展示语言，CN：中文，EN：英文"`
}

type ChartClassifyDeleteCheckResp struct {
	DeleteStatus int    `description:"检测状态:0:默认值，如果为0，继续走其他校验，1:该分类下关联图表不可删除,2:确认删除当前目录及包含的子目录吗"`
	TipsMsg      string `description:"提示信息"`
}

type ChartClassifyDeleteCheckReq struct {
	ChartClassifyId int `description:"分类id"`
	ChartInfoId     int `description:"指标id"`
}

func GetChartClassifyCountByClassifyId(chartClassifyId int) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(1) AS count FROM (
			SELECT rd.*
			FROM (SELECT * FROM chart_classify WHERE parent_id IS NOT NULL) rd,
				 (SELECT @pid := ?) pd 
			WHERE FIND_IN_SET(parent_id, @pid) > 0 
			  AND @pid := CONCAT(@pid, ',', chart_classify_id) 
			UNION SELECT * FROM chart_classify WHERE chart_classify_id = @pid
			)AS t
			WHERE t.chart_classify_id<>? `
	err = o.Raw(sql, chartClassifyId, chartClassifyId).QueryRow(&count)
	return
}

func GetChartClassifyByCondition(condition string, pars []interface{}) (item *ChartClassify, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM chart_classify WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&item)
	return
}

// MoveChartClassifyReq 移动图表分类请求参数
type MoveChartClassifyReq struct {
	ClassifyId       int `description:"分类id"`
	ParentClassifyId int `description:"父级分类id"`
	PrevClassifyId   int `description:"上一个兄弟节点分类id"`
	NextClassifyId   int `description:"下一个兄弟节点分类id"`
}

// GetFirstChartClassifyByParentId 获取当前父级图表分类下的排序第一条的数据
func GetFirstChartClassifyByParentId(parentId int) (item *ChartClassify, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM chart_classify WHERE parent_id=? order by sort asc,chart_classify_id asc limit 1`
	err = o.Raw(sql, parentId).QueryRow(&item)
	return
}

// UpdateChartClassifySortByParentId 根据图表父类id更新排序
func UpdateChartClassifySortByParentId(parentId, classifyId, nowSort int, updateSort string) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` update chart_classify set sort = ` + updateSort + ` WHERE parent_id=? and sort > ? `
	if classifyId > 0 {
		sql += ` or ( chart_classify_id > ` + fmt.Sprint(classifyId) + ` and sort= ` + fmt.Sprint(nowSort) + `)`
	}
	_, err = o.Raw(sql, parentId, nowSort).Exec()
	return
}

// Update 更新图表分类基础信息
func (chartClassify *ChartClassify) Update(cols []string) (err error) {
	o := orm.NewOrmUsingDB("data")
	_, err = o.Update(chartClassify, cols...)
	return
}

// GetChartClassifyMaxSort 获取图表分类下最大的排序数
func GetChartClassifyMaxSort(parentId, source int) (sort int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT Max(sort) AS sort FROM chart_classify WHERE parent_id=? AND source = ? `
	err = o.Raw(sql, parentId, source).QueryRow(&sort)
	return
}

type ChartClassifyView struct {
	ChartClassifyId   int    `orm:"column(chart_classify_id);pk"`
	ChartClassifyName string `description:"分类名称"`
	ParentId          int    `description:"父级id"`
}

func GetChartClassifyViewById(classifyId int) (item *ChartClassifyView, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM chart_classify WHERE chart_classify_id=? `
	err = o.Raw(sql, classifyId).QueryRow(&item)
	return
}

func GetChartClassifyByParentIdFromETA(parentId int) (items []*ChartClassifyItems, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM chart_classify WHERE parent_id=? AND source = 1 order by sort asc,chart_classify_id asc`
	_, err = o.Raw(sql, parentId).QueryRows(&items)
	return
}

func GetChartClassifyAllFromETA() (items []*ChartClassifyItems, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM chart_classify WHERE parent_id<>0 AND source = 1 order by sort asc,chart_classify_id asc`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetSecondaryChartClassifyWithSort 获取二级分类-根据父级分类排序
func GetSecondaryChartClassifyWithSort() (items []*ChartClassify, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM chart_classify AS a
			LEFT JOIN (
				SELECT chart_classify_id AS parent_classify_id, sort AS parent_sort FROM chart_classify WHERE parent_id = 0 AND source = 1
			) AS b ON a.parent_id = b.parent_classify_id
			WHERE parent_id > 0 AND source = 1
			ORDER BY b.parent_sort, a.sort`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}