package models

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

type CygxSearchKeyWord struct {
	Id               int `orm:"column(id);" description:"id"`
	KeyWord          string
	UserId           int
	CreateTime       time.Time
	Mobile           string `description:"手机号"`
	Email            string `description:"邮箱"`
	CompanyId        int    `description:"公司id"`
	CompanyName      string `description:"公司名称"`
	RealName         string `description:"用户实际名称"`
	RegisterPlatform int    `description:"来源 1小程序，2：网页"`
}

// 新增搜索
func AddSearchKeyWord(item *CygxSearchKeyWord) (lastId int64, err error) {
	o := orm.NewOrm()
	lastId, err = o.Insert(item)
	return
}

// 获取用户搜索这个关键词的最新时间
func GetNewSearchKeyWordByThisUser(uid int, keyWord string) (item *CygxSearchKeyWord, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM cygx_search_key_word WHERE user_id = ? AND key_word = ? ORDER BY id DESC LIMIT 1 `
	err = o.Raw(sql, uid, keyWord).QueryRow(&item)
	return
}

// 获取用户搜索词汇频率较高的词
func GetSearchKeyWordTop(condition string, pars []interface{}) (items []*CygxSearchKeyWord, err error) {
	o := orm.NewOrm()
	sql := `SELECT key_word, MAX(create_time) as create_time, COUNT(*) AS num FROM cygx_search_key_word  WHERE 1=1  ` + condition + ` GROUP BY key_word ORDER BY num DESC,create_time DESC LIMIT 12 `
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

func GetCygxSearchKeyWordList(condition string) (items []*CygxSearchKeyWord, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM cygx_search_key_word_log   WHERE 1 = 1 ` + condition + `  GROUP BY user_id  `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 修改用户搜索的相关信息
func UpdateCygxSearchKeyWord(wxUser *WxUserItem) (err error) {
	o := orm.NewOrm()
	var sql string
	if wxUser.Mobile != "" {
		sql = `UPDATE cygx_search_key_word_log SET email=?,company_id=?,company_name=?,user_id=?,real_name=? WHERE mobile=? `
		_, err = o.Raw(sql, wxUser.Email, wxUser.CompanyId, wxUser.CompanyName, wxUser.UserId, wxUser.RealName, wxUser.Mobile).Exec()
	} else if wxUser.Email != "" {
		sql = `UPDATE cygx_search_key_word_log SET mobile=?,company_id=?,company_name=?,user_id=?,real_name=? WHERE email=? `
		_, err = o.Raw(sql, wxUser.Mobile, wxUser.CompanyId, wxUser.CompanyName, wxUser.UserId, wxUser.RealName, wxUser.Email).Exec()
	}
	return
}

// 获取用户搜索词汇频率较高的词
func GetSearchKeyWordinitCrm16_5() (items []*CygxSearchKeyWord, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	mobile,
	key_word,
	register_platform,
	MAX(create_time) as  create_time
	
FROM
	cygx_search_key_word 
WHERE
	create_time > '2024-09-01' 
	AND user_id > 0 
GROUP BY
	mobile,
	key_word`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}
