package excel

import (
	sql2 "database/sql"
	"eta_gn/eta_api/global"
	"eta_gn/eta_api/utils"
	"fmt"
	"time"
)

// ExcelEdbMapping excel与指标的关系表
type ExcelEdbMapping struct {
	ExcelEdbMappingId int       `orm:"column(excel_edb_mapping_id);pk" gorm:"primaryKey" `
	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:"修改时间"`
}

// Add 添加excel与指标的关系
func (e *ExcelEdbMapping) Add() (err error) {
	err = global.DmSQL["data"].Create(e).Error

	return
}

// Update 更新 excel表格基础信息
func (e *ExcelEdbMapping) Update(cols []string) (err error) {
	err = global.DmSQL["data"].Select(cols).Updates(e).Error
	return
}

// GetExcelEdbMappingByExcelInfoId 根据excel的id获取配置关系
func GetExcelEdbMappingByExcelInfoId(excelInfoId int) (items []*ExcelEdbMapping, err error) {
	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 = global.DmSQL["data"].Raw(sql, excelInfoId).Find(&items).Error

	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) {
	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 = global.DmSQL["data"].Raw(sql, excelInfoId).Find(&items).Error

	return
}

type ExcelEdbMappingWithParentIdItem struct {
	ExcelInfoId int
	ParentId    int
}

func GetNoCustomAnalysisExcelEdbMapping(edbInfoId int) (items []ExcelEdbMappingWithParentIdItem, err error) {
	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 = global.DmSQL["data"].Raw(sql, edbInfoId).Find(&items).Error
	return
}

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

// GetAllExcelEdbMappingByExcelInfoIds 根据excel的id获取所有的指标
func GetAllExcelEdbMappingByExcelInfoIds(excelInfoIds []int) (items []*ExcelEdbMapping, err error) {
	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 = global.DmSQL["data"].Raw(sql, excelInfoIds).Find(&items).Error
	return
}

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

// GetUserExcelEdbMappingByEdbInfoIdAndSource 根据指标id获取所属用户的配置关系
func GetUserExcelEdbMappingByEdbInfoIdAndSource(userId, edbInfoId int, sources []int) (items []*ExcelEdbMapping, err error) {
	sql := ` SELECT a.*  FROM excel_edb_mapping AS a 
           JOIN excel_info AS b on a.excel_info_id = b.excel_info_id
           WHERE 1=1 AND a.sys_user_id = ? AND b.edb_info_id = ? AND b.source in (` + utils.GetOrmInReplace(len(sources)) + `) `
	err = global.DmSQL["data"].Raw(sql, userId, edbInfoId, sources).Find(&items).Error
	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) {
	sql := `DELETE FROM excel_edb_mapping WHERE source = ? AND edb_info_id = ? LIMIT 1`
	err = global.DmSQL["data"].Exec(sql, utils.CUSTOM_ANALYSIS_TABLE, edbInfoId).Error
	return
}

// GetExcelEdbMappingItemByExcelInfoIdOrKeyword 根据表格ID或关键词获取指标
func GetExcelEdbMappingItemByExcelInfoIdOrKeyword(excelInfoId int, keyword string) (items []*ExcelEdbMappingItem, err error) {
	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 = global.DmSQL["data"].Raw(sql, pars...).Find(&items).Error
	return
}

// GetCountRelationExcelEdbInfoListMappingByCondition
// @Description: 根据条件获取表格关联指标的数量
// @author: Roc
// @datetime 2024-12-26 10:37:48
// @param condition string
// @param pars []interface{}
// @return total int
// @return err error
func GetCountRelationExcelEdbInfoListMappingByCondition(condition string, pars []interface{}) (total int, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT COUNT(1) total FROM excel_edb_mapping AS a 
	JOIN excel_info AS b on a.excel_info_id = b.excel_info_id WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	var totalNull sql2.NullInt64
	err = o.Raw(sql, pars...).Scan(&totalNull).Error
	if err != nil {
		return
	}

	total = int(totalNull.Int64)

	return
}

// GetUserIdListRelationExcelEdbInfoListMappingByCondition
// @Description: 根据条件获取表格关联指标的用户id列表
// @author: Roc
// @datetime 2024-12-26 10:37:48
// @param condition string
// @param pars []interface{}
// @return total int
// @return err error
func GetUserIdListRelationExcelEdbInfoListMappingByCondition(condition string, pars []interface{}) (userIdList []int, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT b.sys_user_id FROM excel_edb_mapping AS a 
	JOIN excel_info AS b on a.excel_info_id = b.excel_info_id WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY b.sys_user_id `

	err = o.Raw(sql, pars...).Scan(&userIdList).Error

	return
}