package autocode

import (
	"github.com/flipped-aurora/gin-vue-admin/server/global"
	"github.com/flipped-aurora/gin-vue-admin/server/model/autocode"
	autoCodeReq "github.com/flipped-aurora/gin-vue-admin/server/model/autocode/request"
	"github.com/flipped-aurora/gin-vue-admin/server/model/common/request"
	"gorm.io/gorm"
)

type ProblemInfoService struct {
}

// CreateProblemInfo 创建ProblemInfo记录
// Author [piexlmax](https://github.com/piexlmax)
func (problemInfoService *ProblemInfoService) CreateProblemInfo(problemInfo autocode.ProblemInfo) (err error) {
	err = global.GVA_DB.Create(&problemInfo).Error
	return err
}

// DeleteProblemInfo 删除ProblemInfo记录
// Author [piexlmax](https://github.com/piexlmax)
func (problemInfoService *ProblemInfoService) DeleteProblemInfo(problemInfo autocode.ProblemInfo) (err error) {
	err = global.GVA_DB.Delete(&problemInfo).Error
	return err
}

// DeleteProblemInfoByIds 批量删除ProblemInfo记录
// Author [piexlmax](https://github.com/piexlmax)
func (problemInfoService *ProblemInfoService) DeleteProblemInfoByIds(ids request.IdsReq) (err error) {
	err = global.GVA_DB.Delete(&[]autocode.ProblemInfo{}, "id in ?", ids.Ids).Error
	return err
}

// UpdateProblemInfo 更新ProblemInfo记录
// Author [piexlmax](https://github.com/piexlmax)
func (problemInfoService *ProblemInfoService) UpdateProblemInfo(problemInfo autocode.ProblemInfo) (err error) {
	err = global.GVA_DB.Where("id=?", problemInfo.ID).Save(problemInfo).Error
	return err
}

func (problemInfoService *ProblemInfoService) UpdateProblemInfoCount(id uint) (err error) {
	err = global.GVA_DB.Model(&autocode.ProblemInfo{}).Where("id=?", id).Update("count", gorm.Expr("count+1")).Error
	return err
}

// GetProblemInfo 根据id获取ProblemInfo记录
// Author [piexlmax](https://github.com/piexlmax)
func (problemInfoService *ProblemInfoService) GetProblemInfo(id uint) (err error, problemInfo autocode.ProblemInfo) {
	err = global.GVA_DB.Where("id = ?", id).First(&problemInfo).Error
	return
}

func (problemInfoService *ProblemInfoService) GetProblemInfoCount() (err error, countInfo []autocode.ProblemCount) {
	global.GVA_DB.Raw("SELECT statistics.matterid as matterid,pt.problem as problem,statistics.mattercount as mattercount,statistics.num as num,statistics.mattercount / num as proportion FROM ( SELECT  substring_index( substring_index( a.matter, '|', b.help_topic_id + 1 ), '|',- 1 ) AS matterid,  count( 1 ) AS mattercount," +
		"  (  SELECT   count( 1 )   FROM   problem_info a   JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.matter ) - length( REPLACE ( a.matter, '|', '' ) ) + 1 ) WHERE ( a.matter != '' AND a.matter IS NOT NULL )) num  FROM  problem_info a  JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.matter ) - length( REPLACE ( a.matter, '|', '' ) ) + 1 ) " +
		" WHERE  ( a.matter != '' AND a.matter IS NOT NULL )  GROUP BY  matterid  ) statistics INNER JOIN problem_type pt ON pt.id = statistics.matterid").Scan(&countInfo)
	return err, countInfo
}

func (problemInfoService *ProblemInfoService) GetProblemInfoSum() (err error, countInfo []autocode.ProblemSum) {
	global.GVA_DB.Raw("SELECT statistics1.unit_id unit_id,statistics1.unit_name unit_name,statistics1.mattercount totalnum,statistics2.mattercount handnum,statistics1.num,statistics2.mattercount/statistics1.mattercount handproportion FROM (" +
		"SELECT a.unit_id,a.unit_name,count(substring_index(substring_index(a.matter,'|',b.help_topic_id+1),'|',-1)) AS mattercount,(" +
		"SELECT count(1) FROM problem_info a JOIN mysql.help_topic b ON b.help_topic_id< (length(a.matter)-length(" +
		"REPLACE (a.matter,'|',''))+1) WHERE (a.matter !='' AND a.matter IS NOT NULL)) num FROM problem_info a JOIN mysql.help_topic b ON b.help_topic_id< (length(a.matter)-length(" +
		"REPLACE (a.matter,'|',''))+1) WHERE (a.matter !='' AND a.matter IS NOT NULL) GROUP BY unit_id) statistics1 INNER JOIN (" +
		"SELECT a.unit_id,count(substring_index(substring_index(a.matter,'|',b.help_topic_id+1),'|',-1)) AS mattercount FROM problem_info a JOIN mysql.help_topic b ON b.help_topic_id< (length(a.matter)-length(" +
		"REPLACE (a.matter,'|',''))+1) WHERE (a.matter !='' AND a.matter IS NOT NULL) AND a.`status` !='Untreated' GROUP BY unit_id) statistics2 ON statistics1.unit_id=statistics2.unit_id").Scan(&countInfo)
	return err, countInfo
}

func (problemInfoService *ProblemInfoService) GetPLaceRate() (err error, countInfo []autocode.PlaceRate) {
	global.GVA_DB.Raw("SELECT statistics.site_type site_type,dict.label label,statistics.mattercount mattercount,statistics.num num,statistics.mattercount/num proportion FROM (" +
		"SELECT a.site_type,count(substring_index(substring_index(a.matter,'|',b.help_topic_id+1),'|',-1)) AS mattercount,(" +
		"SELECT count(1) FROM problem_info a JOIN mysql.help_topic b ON b.help_topic_id< (length(a.matter)-length(" +
		"REPLACE (a.matter,'|',''))+1) WHERE (a.matter !='' AND a.matter IS NOT NULL)) num FROM problem_info a JOIN mysql.help_topic b ON b.help_topic_id< (length(a.matter)-length(" +
		"	REPLACE (a.matter,'|',''))+1) WHERE (a.matter !='' AND a.matter IS NOT NULL) GROUP BY site_type) statistics INNER JOIN sys_dictionary_details dict ON dict.`value`=statistics.site_type AND dict.sys_dictionary_id=8 AND dict.deleted_at IS NULL;").Scan(&countInfo)
	return err, countInfo
}

// GetProblemInfoInfoList 分页获取ProblemInfo记录
// Author [piexlmax](https://github.com/piexlmax)
func (problemInfoService *ProblemInfoService) GetProblemInfoInfoList(info autoCodeReq.ProblemInfoSearch) (err error, infoList []autocode.ProblemInfo, total int64) {
	limit := info.PageSize
	offset := info.PageSize * (info.Page - 1)
	// 创建db
	db := global.GVA_DB.Model(&autocode.ProblemInfo{})
	if info.SiteType != "" {
		db.Where("site_type=?", info.SiteType)
	}
	if info.UnitId != nil && *info.UnitId != 0 {
		db.Where("unit_id=?", info.UnitId)
	}
	if info.Status != "" {
		db.Where("status=?", info.Status)
	}
	if info.Oper != 0 {
		db.Where("oper=?", info.Oper)
	}
	if !info.CreatedAtStart.IsZero() && !info.CreatedAtEnd.IsZero() {
		db.Where("created_at between ? and ?", info.CreatedAtStart, info.CreatedAtEnd)
	}
	var problemInfos []autocode.ProblemInfo
	// 如果有条件搜索 下方会自动创建搜索语句
	err = db.Count(&total).Error
	err = db.Limit(limit).Offset(offset).Order("created_at desc").Find(&problemInfos).Error
	return err, problemInfos, total
}