package Certificate

import (
	"ColdVerify_server/lib"
	"ColdVerify_server/logs"
	"github.com/beego/beego/v2/adapter/orm"
	orm2 "github.com/beego/beego/v2/client/orm"
	_ "github.com/go-sql-driver/mysql"
	"strconv"
	"strings"
	"time"
)

type Certificate struct {
	Id          int    `orm:"column(ID);size(11);auto;pk"`
	T_sn        string `orm:"size(256);null"` // 证书编号
	T_layout_no string `orm:"size(256);null"` // 布局编号

	T_State    int       `orm:"size(200);default(1)"`                                  //  0删除  1 正常
	CreateTime time.Time `orm:"column(create_time);type(timestamp);null;auto_now_add"` //auto_now 每次 model 保存时都会对时间自动更新
	UpdateTime time.Time `orm:"column(update_time);type(timestamp);null;auto_now"`     //auto_now_add 第一次保存时才设置时间
}
type Certificate_R struct {
	T_sn string `orm:"size(256);null"` // 编号
}

func (t *Certificate) TableName() string {
	return "certificate" // 数据库名称   // ************** 替换 FormulaList **************
}

func init() {
	//注册模型
	orm.RegisterModel(new(Certificate))

}

// ----------------

// 获取 ById
func Read_Certificate_ById(id int) (r Certificate, is bool) {
	o := orm.NewOrm()
	r = Certificate{Id: id}
	err := o.Read(&r) // o.Read(&r,"Tokey") 如果不是 主键 就得指定字段名
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return r, false
	}
	return r, true
}

// 获取 By
func Read_Certificate(T_layout_no string) (r Certificate, is bool) {

	o := orm.NewOrm()
	qs := o.QueryTable(new(Certificate))
	err := qs.Filter("T_layout_no", T_layout_no).Filter("T_State", 1).One(&r)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return r, false
	}

	return r, true
}

// 获取 By
func Read_Certificate_ByT_layout_no(T_layout_no string) (r Certificate, is bool) {

	o := orm.NewOrm()
	qs := o.QueryTable(new(Certificate))
	err := qs.Filter("T_layout_no", T_layout_no).Filter("T_State", 1).One(&r)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return r, false
	}

	return r, true
}

// 添加
func Add_Certificate(r Certificate) (id int64, is bool) {
	o := orm.NewOrm()
	id, err := o.Insert(&r)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return 0, false
	}
	return id, true
}

// 删除
func Delete_Certificate(v Certificate) bool {
	o := orm.NewOrm()
	if num, err := o.Delete(&v); err == nil {
		logs.Println("Number of records deleted in database:", num)
	} else {
		logs.Error(lib.FuncName(), err)
		return false
	}

	return true
}

// 删除
func Delete_Certificate_(v Certificate) bool {
	o := orm.NewOrm()
	v.T_State = 0
	if num, err := o.Update(&v, "T_State"); err == nil {
		logs.Println("Number of records updated in database:", num)
	} else {
		logs.Error(lib.FuncName(), err)
		return false
	}

	return true
}

// 修改
func Update_Certificate(m Certificate, cols ...string) bool {
	o := orm.NewOrm()
	if num, err := o.Update(&m, cols...); err == nil {
		logs.Println("Number of records updated in database:", num)
		return true
	} else {
		logs.Error(lib.FuncName(), err)
	}
	return false
}

type Certificate_ struct {
	Id int `orm:"column(ID)"`
	//T_sn             string // 编号
	T_Certificate_sn string // 编号
	T_layout_no      string // 布局编号
	T_release_time   string `orm:"column(t_release_time)"` // 发布时间
	T_failure_time   string `orm:"column(t_failure_time)"` // 失效时间
	T_pdf            string // pdf链接
}

// 获取列表
func Read_Certificate_List(T_Certificate_sn, T_layout_no, Time_start, Time_end string, T_release_time_sort, T_failure_time_sort, T_layout_no_sort, page int, page_z int) ([]Certificate_, int) {

	o := orm.NewOrm()
	var offset int
	if page <= 1 {
		offset = 0
	} else {
		offset = (page - 1) * page_z
	}

	var maps_z []orm2.ParamsList
	var maps []Certificate_

	sqlWhere := ""
	if len(T_Certificate_sn) > 0 {
		sqlWhere += " AND pdf.t__certificate_sn like \"%" + T_Certificate_sn + "%\""
	}
	if len(T_layout_no) > 0 {
		sqlWhere += " AND c.t_layout_no like \"%" + T_layout_no + "%\""
	}
	if len(Time_start) > 0 {
		sqlWhere += " AND t_failure_time >= \"" + Time_start + "\""
	}
	if len(Time_end) > 0 {
		sqlWhere += " AND t_failure_time <= \"" + Time_end + "\""
	}

	// 获取总条数
	sql := "SELECT COUNT(ID) FROM certificate AS c LEFT JOIN (SELECT t_release_time,t_failure_time,t_pdf,t__certificate_sn,t_layout_no FROM (SELECT DISTINCT * FROM `certificate_pdf` WHERE t__state=1 ORDER BY id DESC) f GROUP BY t_layout_no) AS pdf ON pdf.t_layout_no=c.t_layout_no WHERE t__state=1"
	sql = sql + sqlWhere
	logs.Println(sql)
	_, err := o.Raw(sql).ValuesList(&maps_z)
	if err != nil {
		return maps, 0
	}
	if len(maps_z) == 0 {
		return maps, 0
	}
	//sql = "SELECT * FROM Certificate AS c LEFT JOIN (SELECT any_value(t_release_time) t_release_time,any_value(t_failure_time) t_failure_time,any_value(t_pdf) t_pdf,t__certificate_sn FROM (SELECT DISTINCT * FROM `CertificatePdf` WHERE t__state=1 ORDER BY id DESC) f GROUP BY t__certificate_sn) AS pdf ON pdf.t__certificate_sn=c.t_sn WHERE t__state=1"
	sql = "SELECT *,c.t_layout_no as t_layout_no FROM certificate AS c LEFT JOIN (SELECT t_release_time,t_failure_time,t_pdf,t__certificate_sn,t_layout_no FROM (SELECT DISTINCT * FROM `certificate_pdf` WHERE t__state=1 ORDER BY id DESC) f GROUP BY t_layout_no) AS pdf ON pdf.t_layout_no=c.t_layout_no WHERE t__state=1"

	sql = sql + sqlWhere
	sqlOrder := make([]string, 0)

	if T_release_time_sort == 1 {
		sqlOrder = append(sqlOrder, "pdf.t_release_time ASC")
	} else if T_release_time_sort == 2 {
		sqlOrder = append(sqlOrder, "pdf.t_release_time DESC")
	}

	if T_failure_time_sort == 1 {
		sqlOrder = append(sqlOrder, "pdf.t_failure_time ASC")
	} else if T_failure_time_sort == 2 {
		sqlOrder = append(sqlOrder, "pdf.t_failure_time DESC")
	}
	if T_layout_no_sort == 1 {
		sqlOrder = append(sqlOrder, "c.t_layout_no ASC")
	} else if T_layout_no_sort == 2 {
		sqlOrder = append(sqlOrder, "c.t_layout_no DESC")
	}

	if len(sqlOrder) > 0 {
		sql += " ORDER BY " + strings.Join(sqlOrder, ", ")
	}
	if page_z != 9999 {
		sql += " LIMIT " + strconv.Itoa(offset) + "," + strconv.Itoa(page_z)
	}

	logs.Println(sql)
	_, err = o.Raw(sql).QueryRows(&maps)

	cnt, _ := strconv.Atoi(maps_z[0][0].(string))
	return maps, cnt
}