package ppt_english

import (
	"time"

	"github.com/beego/beego/v2/client/orm"
)

// ppt目录和ppt 映射表
type PptEnglishGroupMapping struct {
	GroupPptId      int64     `orm:"column(group_ppt_id);pk;auto" description:"自增序号"`
	GroupId         int64     `description:"ppt目录ID"`
	PptSort         int64     `description:"Ppt的排序"`
	PptId           int64     `description:"ppt ID"`
	CreateTime      time.Time `orm:"auto_now_add;type(datetime)" description:"创建时间"`
	ModifyTime      time.Time `orm:"auto_now;type(datetime)" description:"修改时间"`
	AdminId         int       `description:"移动ppt到该目录的系统用户id"`
	AdminRealName   string    `description:"系统用户名称"`
	ChildGroupPptId int64     `description:"设置共享后的新映射ID"`
	IsMoved         bool      `description:"true表示改PPT被人为移动过"`
}

// AddPptGroupMapping 新增目录和ppt的映射关系
func AddPptGroupMapping(item *PptEnglishGroupMapping) (lastId int64, err error) {
	o := orm.NewOrmUsingDB("rddp")
	lastId, err = o.Insert(item)
	return
}

// GetPptMappingCountByGroupId 查询目录下，ppt的个数
func GetPptMappingCountByGroupId(groupId int64) (total int64, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `select count(*) from ppt_english_group_mapping where group_id=?`
	err = o.Raw(sql, groupId).QueryRow(&total)
	return
}

// GetPptMappingListByGroupId 查询目录下，ppt列表
func GetPptMappingListByGroupId(groupId int64) (list []*PptEnglishGroupMapping, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `select group_ppt_id, group_id, ppt_id, ppt_sort, admin_id, admin_real_name, create_time from ppt_english_group_mapping where group_id=? order by ppt_sort asc, group_ppt_id asc `
	_, err = o.Raw(sql, groupId).QueryRows(&list)
	return
}

// GetPptMappingListByGroupIds 根据分组ID查找
func GetPptMappingListByGroupIds(groupIds []int64) (list []*PptEnglishGroupMapping, err error) {
	_, err = orm.NewOrmUsingDB("rddp").
		QueryTable("ppt_english_group_mapping").
		Filter("group_id__in", groupIds).
		OrderBy("-ppt_sort", "group_ppt_id").
		All(&list)

	return
}

// GetPptMappingByPptId 查询目录下, pptId对应的目录映射关系
func GetPptMappingByPptId(pptId int64) (item *PptEnglishGroupMapping, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` select * from ppt_english_group_mapping where ppt_id=? `
	err = o.Raw(sql, pptId).QueryRow(&item)
	return
}

// GetPptMappingListByGroupId 查询目录下，ppt列表, 降序排列
func GetPptMappingListByGroupIdDesc(groupId int64) (list []*PptEnglishGroupMapping, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `select group_ppt_id, group_id, ppt_id, ppt_sort, admin_id, admin_real_name, create_time from ppt_english_group_mapping where group_id=? order by ppt_sort desc, group_ppt_id desc `
	_, err = o.Raw(sql, groupId).QueryRows(&list)
	return
}

// GetPptMappingByGroupPptId 查询根据映射ID，查询单个映射关系
func GetPptMappingByGroupPptId(groupPptId int64, adminId int) (item *PptEnglishGroupMapping, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `select * from ppt_english_group_mapping where group_ppt_id=? and admin_id=?`
	err = o.Raw(sql, groupPptId, adminId).QueryRow(&item)
	return
}

// GetPptMappingByGroupPptId 查询根据映射ID，查询单个映射关系数量
func GetPptMappingCountByGroupPptId(groupPptId int64, adminId int) (count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `select COUNT(*) AS count from ppt_english_group_mapping where group_ppt_id=? and admin_id=?`
	err = o.Raw(sql, groupPptId, adminId).QueryRow(&count)
	return
}

// Update 更新ppt目录映射的基本信息
func (item *PptEnglishGroupMapping) Update(cols []string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	_, err = o.Update(item, cols...)
	return
}

// AddPptGroupMappingMulti 批量新增ppt和目录的映射关系
func AddPptGroupMappingMulti(list []*PptEnglishGroupMapping) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	_, err = o.InsertMulti(1, list)
	return
}

// UpdatePptGroupMappingSortMulti 批量更新初始排序标识
func UpdatePptGroupMappingSortMulti(groupPptIds []int64) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	p, err := o.Raw("UPDATE ppt_english_group_mapping SET ppt_sort = group_ppt_id WHERE group_ppt_id = ?").Prepare()
	if err != nil {
		return
	}
	defer func() {
		_ = p.Close() // 别忘记关闭 statement
	}()
	for _, v := range groupPptIds {
		_, err = p.Exec(v)
		if err != nil {
			return
		}
	}
	return
}

// DeletePptGroupMapping 删除ppt目录
func DeletePptGroupMapping(groupId int64, adminId int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `DELETE FROM ppt_english_group_mapping WHERE group_ppt_id=? and admin_id=? `
	_, err = o.Raw(sql, groupId, adminId).Exec()
	return
}

// DeletePptGroupMappingByPptId 删除ppt目录
func DeletePptGroupMappingByPptId(pptId int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `DELETE FROM ppt_english_group_mapping WHERE ppt_id=?`
	_, err = o.Raw(sql, pptId).Exec()
	return
}

// MoveUpGroupPptBySort 往上移动ppt
func MoveUpGroupPptBySort(groupId, nextSort, currentSort int64) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `update ppt_english_group_mapping set ppt_sort = ppt_sort + 1 where group_id=? and ppt_sort >= ? and ppt_sort< ?`
	_, err = o.Raw(sql, groupId, nextSort, currentSort).Exec()
	return
}

// MoveDownGroupPptBySort 往下移动ppt
func MoveDownGroupPptBySort(groupId, prevSort, currentSort int64) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `update ppt_english_group_mapping set ppt_sort = ppt_sort - 1 where group_id=? and ppt_sort <= ? and ppt_sort> ? `
	_, err = o.Raw(sql, groupId, prevSort, currentSort).Exec()
	return
}

// GetGroupPptByPptId 根据pptID 查找各个目录下的ppt
func GetGroupPptByPptId(pptId int) (list []*PptEnglishGroupMapping, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `select * from ppt_english_group_mapping where ppt_id=?`
	_, err = o.Raw(sql, pptId).QueryRows(&list)
	return
}

// GetPublicGroupPptByPptIds 根据pptid查询公共目录下的ppt
func GetPublicGroupPptByPptIds(pptIds []string) (list []*PptEnglishGroupMapping, err error) {
	qb, _ := orm.NewQueryBuilder("mysql")
	// 构建查询对象
	qb.Select("p.*").
		From("ppt_english_group as g").
		InnerJoin("ppt_english_group_mapping as p").On("g.group_id = p.group_id").
		Where("g.is_share = 1 and p.ppt_id").In(pptIds...).
		OrderBy("g.share_time asc, p.ppt_sort asc")

	// 导出 SQL 语句
	sql := qb.String()

	// 执行 SQL 语句
	o := orm.NewOrmUsingDB("rddp")
	_, err = o.Raw(sql).QueryRows(&list)
	return
}