package models

import (
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
	"time"
)

// EnglishReportEmail 英文研报-邮箱/客户联系人
type EnglishReportEmail struct {
	Id              int       `orm:"column(id);pk" description:"邮箱ID"`
	CompanyId       int       `description:"客户ID"`
	Name            string    `description:"联系人名称"`
	Email           string    `description:"邮箱地址"`
	Mobile          string    `description:"手机号"`
	CountryCode     string    `description:"区号，86、852、886等"`
	BusinessCardUrl string    `description:"名片"`
	ViewTotal       int       `description:"累计点击量/阅读量"`
	LastViewTime    time.Time `description:"最后阅读时间"`
	IsDeleted       int       `description:"删除状态：0-正常；1-已删除"`
	Enabled         int       `description:"邮箱状态：1:有效，0:禁用"`
	AdminId         int       `description:"创建人ID"`
	AdminName       string    `description:"创建人姓名"`
	Status          int       `description:"1:正式,2:临时,3:终止"`
	CompanyName     string    `description:"公司名称"`
	CreateTime      time.Time `description:"创建时间"`
	ModifyTime      time.Time `description:"更新时间"`
	RegisterTime    time.Time `description:"注册时间"`
}

func (item *EnglishReportEmail) TableName() string {
	return "english_report_email"
}

// EnglishReportEmailSaveReq 保存邮箱请求体
type EnglishReportEmailSaveReq struct {
	Id              int    `description:"邮箱ID, 大于0为编辑"`
	CompanyId       int    `description:"客户ID"`
	Name            string `description:"客户名称"`
	Email           string `description:"邮箱地址"`
	Mobile          string `description:"手机号"`
	CountryCode     string `description:"区号，86、852、886等"`
	BusinessCardUrl string `description:"名片"`
	Enabled         int    `description:"邮箱状态：1:有效，0:禁用"`
}

func (item *EnglishReportEmail) Create() (err error) {
	o := orm.NewOrmUsingDB("rddp")
	id, err := o.Insert(item)
	if err != nil {
		return
	}
	item.Id = int(id)
	return
}

func (item *EnglishReportEmail) Update(cols []string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	_, err = o.Update(item, cols...)
	return
}

// GetEnglishReportEmailById 主键获取邮箱
func GetEnglishReportEmailById(id int) (item *EnglishReportEmail, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM english_report_email WHERE is_deleted = 0 AND id = ? LIMIT 1`
	err = o.Raw(sql, id).QueryRow(&item)
	return
}

// EnglishReportEmailPageListResp 分页列表响应体
type EnglishReportEmailPageListResp struct {
	List   []*EnglishReportEmailResp
	Paging *paging.PagingItem `description:"分页数据"`
}

// EnglishReportEmailResp 邮箱响应体
type EnglishReportEmailResp struct {
	Id                  int       `description:"邮箱ID"`
	CompanyId           int       `description:"客户ID"`
	Name                string    `description:"客户名称"`
	Email               string    `description:"邮箱地址"`
	Mobile              string    `description:"手机号"`
	CountryCode         string    `description:"区号，86、852、886等"`
	BusinessCardUrl     string    `description:"名片"`
	AdminName           string    `description:"创建人姓名"`
	CreateTime          string    `description:"创建时间"`
	ViewTotal           int       `description:"累计点击量"`
	Enabled             int       `description:"邮箱状态：1:有效，0:禁用"`
	CompanyName         string    `description:"公司名称"`
	RegisterCompanyName string    `description:"注册公司名称"`
	Status              int       `description:"1:正式,2:临时,3:终止"`
	LastViewTime        time.Time `description:"最后阅读时间"`
	IsDeleted           int       `description:"删除状态：0-正常；1-已删除"`
	AdminId             int       `description:"创建人ID"`
	ModifyTime          string    `description:"更新时间"`
	RegisterTime        string    `description:"注册时间"`
}

// EnglishReportEmailRespItem 邮箱响应体
type EnglishReportEmailRespItem struct {
	Id                  int       `description:"邮箱ID"`
	Name                string    `description:"客户名称"`
	Email               string    `description:"邮箱地址"`
	Mobile              string    `description:"手机号"`
	CountryCode         string    `description:"区号，86、852、886等"`
	BusinessCardUrl     string    `description:"名片"`
	AdminName           string    `description:"创建人姓名"`
	CreateTime          time.Time `description:"创建时间"`
	ViewTotal           int       `description:"累计点击量"`
	Enabled             int       `description:"邮箱状态：1:有效，0:禁用"`
	CompanyName         string    `description:"公司名称"`
	RegisterCompanyName string    `description:"注册公司名称"`
	RegisterTime        time.Time `description:"注册时间"`
}

// GetEnglishReportEmailPageList 获取邮箱列表-分页
func GetEnglishReportEmailPageList(condition string, pars []interface{}, order string, startSize, pageSize int) (total int, list []*EnglishReportEmailRespItem, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT a.id,a.name,a.email,a.mobile,a.country_code,a.business_card_url,a.view_total,a.company_id,
a.last_view_time,a.admin_id,a.admin_name,a.create_time,a.modify_time,a.enabled,a.status,a.is_deleted,a.register_time,
b.company_name AS company_name,a.company_name AS register_company_name FROM english_report_email AS a LEFT JOIN 
	english_company AS b ON a.company_id = b.company_id
WHERE a.is_deleted = 0 `
	sql += condition
	if order != "" {
		sql += order
	} else {
		sql += ` ORDER BY a.create_time DESC`
	}
	totalSQl := `SELECT COUNT(1) total FROM (` + sql + `) z`
	if err = o.Raw(totalSQl, pars).QueryRow(&total); err != nil {
		return
	}
	sql += ` LIMIT ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
	return
}

// GetEnglishReportEmailList 获取邮箱列表
func GetEnglishReportEmailList(condition string, pars []interface{}, order string) (list []*EnglishReportEmail, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM english_report_email WHERE is_deleted = 0 `
	sql += condition
	if order != "" {
		sql += order
	} else {
		sql += ` ORDER BY create_time DESC`
	}
	_, err = o.Raw(sql, pars).QueryRows(&list)
	return
}

// GetEnglishReportEmailByEmail 地址获取邮箱
func GetEnglishReportEmailByEmail(email string) (item *EnglishReportEmailResp, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT a.id,a.name,a.email,a.mobile,a.country_code,a.business_card_url,a.view_total,
a.last_view_time,a.admin_id,a.admin_name,a.create_time,a.modify_time,a.enabled,a.status,
b.company_name AS company_name,a.company_name AS register_company_name FROM english_report_email AS a LEFT JOIN 
	english_company AS b ON a.company_id = b.company_id WHERE a.is_deleted = 0 AND a.email = ? LIMIT 1`
	err = o.Raw(sql, email).QueryRow(&item)
	return
}

// EnglishReportEmailDelReq 删除邮箱请求体
type EnglishReportEmailDelReq struct {
	EmailId int `description:"邮箱ID"`
}

// EnglishReportEmailDelReq 删除邮箱请求体
type EnglishReportEditEnabledReq struct {
	EmailId int `description:"邮箱ID"`
	Enabled int `description:"1:有效，0:禁用"`
}

// DelEnglishReportEmail 删除邮箱
func DelEnglishReportEmail(id int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `DELETE FROM english_report_email WHERE id = ? LIMIT 1`
	_, err = o.Raw(sql, id).Exec()
	return
}

// EnglishReportEmailSendReq 群发邮件请求体
type EnglishReportEmailSendReq struct {
	ReportId      int    `description:"报告ID"`
	EmailIds      string `description:"邮箱IDs"`
	Theme         string `description:"邮件主题"`
	EnPermissions []int  `description:"品种权限IDs"`
	NoCompanyIds  []int  `description:"禁止接收邮件的英文客户IDs"`
}

// EnglishReportEmailConf 英文研报邮件配置
type EnglishReportEmailConf struct {
	FromAlias     string `description:"发信人昵称" json:"from_alias"`
	SendAuthGroup string `description:"群发邮件权限组, 英文逗号分隔" json:"send_auth_group"`
}

// GetEnglishCompanyViewPageList 获取联系人点击量列表-分页
func GetEnglishCompanyViewPageList(condition string, pars []interface{}, order string, startSize, pageSize int) (total int, list []*EnglishReportEmail, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM english_report_email WHERE view_total > 0 `
	sql += condition
	if order != "" {
		sql += order
	} else {
		sql += ` ORDER BY create_time DESC`
	}

	totalSQl := `SELECT COUNT(1) total FROM (` + sql + `) z`
	if err = o.Raw(totalSQl, pars).QueryRow(&total); err != nil {
		return
	}
	sql += ` LIMIT ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
	return
}

// MultiCreateEnglishEmail 批量新增英文邮箱/联系人
func MultiCreateEnglishEmail(items []*EnglishReportEmail, logs []*EnglishReportEmailOpLog) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	tx, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.Commit()
		}
	}()

	// 新增联系人
	_, err = tx.InsertMulti(len(items), items)
	if err != nil {
		return
	}

	// 新增日志
	_, err = tx.InsertMulti(len(logs), logs)
	return
}

// EnglishReportEmailResendReq 邮件重发请求体
type EnglishReportEmailResendReq struct {
	ReportId int `description:"报告ID"`
	SendId   int `description:"发送ID, 0表示批量发送全部失败邮件"`
}

// UpdateEnglishEmailEnabledByCompanyId 更新客户下所有联系人状态
func UpdateEnglishEmailEnabledByCompanyId(companyId, enabled int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE english_report_email SET enabled = ? WHERE company_id = ?`
	_, err = o.Raw(sql, enabled, companyId).Exec()
	return
}

// EnglishReportMoveReq 移动至当前客户请求体
type EnglishReportMoveReq struct {
	EmailId   int `description:"邮箱ID"`
	CompanyId int `description:"公司id"`
}

// GetEnglishReportEmailListWithCompany 获取邮箱列表
func GetEnglishReportEmailListWithCompany(condition string, pars []interface{}, order string) (list []*EnglishReportEmailRespItem, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT a.id,a.name,a.email,a.mobile,a.country_code,a.business_card_url,a.view_total,
a.last_view_time,a.admin_id,a.admin_name,a.create_time,a.modify_time,a.enabled,a.status,
b.company_name AS company_name,a.company_name AS register_company_name FROM english_report_email AS a LEFT JOIN 
	english_company AS b ON a.company_id = b.company_id WHERE is_deleted = 0 `
	sql += condition
	if order != "" {
		sql += order
	} else {
		sql += ` ORDER BY create_time DESC`
	}
	_, err = o.Raw(sql, pars).QueryRows(&list)
	return
}

// GetEnCompanyIdsByKeyword 关键词获取英文客户IDs
func GetEnCompanyIdsByKeyword(keyword string) (companyIds []int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT DISTINCT
				a.company_id
			FROM
				english_company AS a
			LEFT JOIN english_report_email AS b ON a.company_id = b.company_id AND b.is_deleted = 0
			WHERE
				a.is_deleted = 0 AND a.status = 1 AND (b.email LIKE ? OR b.mobile LIKE ? OR a.company_name LIKE ? OR a.country_code LIKE ? )`
	_, err = o.Raw(sql, keyword, keyword, keyword, keyword).QueryRows(&companyIds)
	return
}