package excel

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

// ExcelEdbMapping excel与指标的关系表
type ExcelEdbMapping struct {
	ExcelEdbMappingId int       `orm:"column(excel_edb_mapping_id);pk"`
	ExcelInfoId       int       `description:"excel的id"`
	Source            int       `description:"表格来源，1：excel插件的表格，2：自定义表格，3：混合表格，4：自定义分析，默认：1"`
	EdbInfoId         int       `description:"计算指标id"`
	CreateTime        time.Time `description:"创建时间"`
	ModifyTime        time.Time `description:"修改时间"`
}

// AddExcelEdbMappingMulti 批量添加excel与指标的关系
func AddExcelEdbMappingMulti(items []*ExcelEdbMapping) (err error) {
	o := orm.NewOrmUsingDB("data")
	_, err = o.InsertMulti(len(items), items)
	return
}

// Add 添加excel与指标的关系
func (e *ExcelEdbMapping) Add() (err error) {
	o := orm.NewOrmUsingDB("data")
	_, err = o.Insert(e)
	return
}

// Update 更新 excel表格基础信息
func (e *ExcelEdbMapping) Update(cols []string) (err error) {
	o := orm.NewOrmUsingDB("data")
	_, err = o.Update(e, cols...)
	return
}

// GetExcelEdbMappingByEdbInfoId 根据指标id获取配置关系
func GetExcelEdbMappingByEdbInfoId(edbInfoId int) (items []*ExcelEdbMapping, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT *  FROM excel_edb_mapping WHERE 1=1 AND edb_info_id = ? `

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

// GetExcelEdbMappingByExcelInfoId 根据excel的id获取配置关系
func GetExcelEdbMappingByExcelInfoId(excelInfoId int) (items []*ExcelEdbMapping, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT *  FROM excel_edb_mapping AS a 
           join edb_info as b on a.edb_info_id = b.edb_info_id
           WHERE 1=1 AND a.excel_info_id = ? `
	_, err = o.Raw(sql, excelInfoId).QueryRows(&items)

	return
}

type ExcelEdbMappingItem struct {
	EdbInfoId        int    `description:"指标id"`
	UniqueCode       string `description:"唯一编码"`
	EdbName          string `description:"指标名称"`
	ClassifyId       int    `description:"分类id"`
	Frequency        string `description:"频度"`
	Unit             string `description:"单位"`
	CalculateFormula string `json:"-"`
	DateSequenceStr  string `description:"日期序列公式"`
	DataSequenceStr  string `description:"数据序列公式"`
}

// CalculateFormula 计算公式
type CalculateFormula struct {
	DateSequenceStr string `json:"DateSequenceStr"`
	DataSequenceStr string `json:"DataSequenceStr"`
}

// GetAllExcelEdbMappingItemByExcelInfoId 根据品种id获取所有的指标结果集
func GetAllExcelEdbMappingItemByExcelInfoId(excelInfoId int) (items []*ExcelEdbMappingItem, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT a.edb_info_id,a.unique_code,a.edb_name,a.classify_id,a.frequency,a.unit,calculate_formula FROM edb_info AS a 
         JOIN excel_edb_mapping AS b ON a.edb_info_id=b.edb_info_id 
         WHERE b.excel_info_id = ? ORDER BY b.excel_edb_mapping_id ASC `
	_, err = o.Raw(sql, excelInfoId).QueryRows(&items)
	return
}

// GetNoCustomAnalysisExcelEdbMappingCount 根据指标id获取非自定义分析的关联关系
func GetNoCustomAnalysisExcelEdbMappingCount(edbInfoId int) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(1) AS count FROM excel_edb_mapping a 
                          join excel_info b on a.excel_info_id=b.excel_info_id
                          WHERE edb_info_id=? AND a.source != 4 AND b.is_delete = 0`
	err = o.Raw(sql, edbInfoId).QueryRow(&count)
	return
}

type ExcelEdbMappingWithParentIdItem struct {
	ExcelInfoId int
	ParentId    int
}

func GetNoCustomAnalysisExcelEdbMapping(edbInfoId int) (items []ExcelEdbMappingWithParentIdItem, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT b.excel_info_id, b.parent_id FROM excel_edb_mapping a 
                          join excel_info b on a.excel_info_id=b.excel_info_id
                          WHERE edb_info_id=? AND a.source != 4 AND b.is_delete = 0`
	_, err = o.Raw(sql, edbInfoId).QueryRows(&items)
	return
}

// GetAllExcelEdbMappingByExcelInfoId 根据excel的id获取所有的指标
func GetAllExcelEdbMappingByExcelInfoId(excelInfoId int) (items []*ExcelEdbMapping, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT a.* FROM  excel_edb_mapping a
         WHERE a.excel_info_id = ? ORDER BY a.excel_edb_mapping_id ASC `
	_, err = o.Raw(sql, excelInfoId).QueryRows(&items)
	return
}

// GetAllExcelEdbMappingByExcelInfoIds 根据excel的id获取所有的指标
func GetAllExcelEdbMappingByExcelInfoIds(excelInfoIds []int) (items []*ExcelEdbMapping, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT a.* FROM  excel_edb_mapping a
         WHERE a.excel_info_id in (` + utils.GetOrmInReplace(len(excelInfoIds)) + `) ORDER BY a.excel_edb_mapping_id ASC `
	_, err = o.Raw(sql, excelInfoIds).QueryRows(&items)
	return
}

// GetExcelEdbMappingByEdbInfoIdAndSource 根据指标id获取配置关系
func GetExcelEdbMappingByEdbInfoIdAndSource(edbInfoId int, sources []int) (items []*ExcelEdbMapping, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT *  FROM excel_edb_mapping WHERE 1=1 AND edb_info_id = ? AND source in (` + utils.GetOrmInReplace(len(sources)) + `) `

	_, err = o.Raw(sql, edbInfoId, sources).QueryRows(&items)
	return
}

// DeleteCustomAnalysisExcelEdbMappingByEdbInfoId
// @Description: 根据指标id删除与自定义分析表格的关系
// @author: Roc
// @datetime 2023-11-02 13:20:02
// @param edbInfoId int
// @return err error
func DeleteCustomAnalysisExcelEdbMappingByEdbInfoId(edbInfoId int) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `DELETE FROM excel_edb_mapping WHERE source = ? AND edb_info_id = ? LIMIT 1`
	_, err = o.Raw(sql, utils.CUSTOM_ANALYSIS_TABLE, edbInfoId).Exec()

	return
}

// GetExcelEdbMappingItemByExcelInfoIdOrKeyword 根据表格ID或关键词获取指标
func GetExcelEdbMappingItemByExcelInfoIdOrKeyword(excelInfoId int, keyword string) (items []*ExcelEdbMappingItem, err error) {
	o := orm.NewOrmUsingDB("data")
	cond := `b.excel_info_id = ?`
	pars := make([]interface{}, 0)
	pars = append(pars, excelInfoId)
	if keyword != "" {
		cond += ` AND (a.edb_code LIKE ? OR a.edb_name LIKE ?)`
		pars = append(pars, keyword, keyword)
	}
	sql := fmt.Sprintf(`SELECT
			a.edb_info_id,
			a.unique_code,
			a.edb_name,
			a.classify_id,
			a.frequency,
			a.unit,
			calculate_formula
		FROM
			edb_info AS a
		JOIN excel_edb_mapping AS b ON a.edb_info_id = b.edb_info_id
		WHERE
			%s
		ORDER BY
			b.excel_edb_mapping_id ASC`, cond)
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}