package yb

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"hongze/hz_crm_api/utils"
	"time"
)

type CommunityAudioListenLog struct {
	Id                       int    `gorm:"primaryKey;column:id;type:int(10) unsigned;not null" `
	CommunityQuestionAudioID int    `gorm:"column:community_question_audio_id;type:int(10) unsigned;not null"`          //问答音频ID
	CommunityQuestionID      int    `gorm:"column:community_question_id;type:int(10) unsigned;not null;default:0" `     // 社区问题ID
	UserID                   int    `gorm:"index:idx_user_id;column:user_id;type:int(10) unsigned;not null;default:0" ` // 点击音频的用户ID
	SourceAgent              int    `gorm:"column:source_agent;type:tinyint(4);default:1" `                             // 操作来源，1：小程序，2：小程序 pc 3:弘则研究公众号，4:web pc
	CreateTime               string `gorm:"column:create_time;type:datetime;default:CURRENT_TIMESTAMP"`                 // 创建日志时间
}

// TableName get sql table name.获取数据库表名
func (l *CommunityAudioListenLog) TableName() string {
	return "yb_community_audio_listen_log"
}

type QuestionAudioLogItem struct {
	UserId         int
	NewSourceAgent int
	LastCreateTime string
	ClickNum       int `description:"点击量"`
}

// GetAudioLogListByQuestionId 查询回答音频点击量列表
func GetAudioLogListByQuestionId(questionId int, startSize, pageSize int) (list []*QuestionAudioLogItem, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	user_id,
	IF(source_agent=4,2,source_agent) as new_source_agent,
	count(*) as click_num,
	MAX( create_time ) as last_create_time
FROM
	yb_community_audio_listen_log 
	WHERE community_question_id=?
GROUP BY
	user_id,
	new_source_agent
	ORDER BY MAX( create_time ) DESC, id DESC
	LIMIT ?,?`
	_, err = o.Raw(sql, questionId, startSize, pageSize).QueryRows(&list)
	return
}

// GetAudioLogListTotalByQuestionId 查询回答音频点击量列表总数
func GetAudioLogListTotalByQuestionId(questionId int) (total int64, err error) {
	o := orm.NewOrm()
	sql := `select count(*) from (SELECT
	user_id,
	IF(source_agent=4,2,source_agent) as new_source_agent
FROM
	yb_community_audio_listen_log 
	WHERE community_question_id=?
GROUP BY
	user_id,
	new_source_agent) as b
	`
	err = o.Raw(sql, questionId).QueryRow(&total)
	return
}

// GetAudioLogTotalByQuestionId 查询
func GetAudioLogTotalByQuestionId(questionId int) (total int64, err error) {
	o := orm.NewOrm()
	sql := `select count(*)
FROM
	yb_community_audio_listen_log 
	WHERE community_question_id=?
	`
	err = o.Raw(sql, questionId).QueryRow(&total)
	return
}

// UserCommunityQuestionVisitCount 用户问答社区音频点击量
type UserCommunityQuestionVisitCount struct {
	VisitCount          int    `json:"visit_count"`
	CommunityQuestionId int    `json:"community_question_id"`
	NewSource           int    `json:"new_source"`
	RecentTime          string `json:"recent_time"`
}

// GetCommunityQuestionVisitCountByUserId 问答社区音频点击量统计-根据用户
func GetCommunityQuestionVisitCountByUserId(userId, startSize, pageSize int, orderRule string) (total int, list []*UserCommunityQuestionVisitCount, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS visit_count, community_question_id, IF(source_agent=4,2,source_agent) as new_source, MAX(create_time) AS recent_time
			FROM yb_community_audio_listen_log WHERE user_id = ?
			GROUP BY community_question_id, new_source`

	if orderRule != `` {
		sql += ` ORDER BY ` + orderRule
	} else {
		sql += ` ORDER BY recent_time DESC`
	}

	totalSQL := `SELECT COUNT(1) total FROM (` + sql + `) z `
	err = o.Raw(totalSQL, userId).QueryRow(&total)
	if err != nil {
		return
	}

	sql += ` LIMIT ?,?`
	_, err = o.Raw(sql, userId, startSize, pageSize).QueryRows(&list)
	return
}

// CompanyUserYbProductVisitCount 联系人分产品阅读统计
type CompanyUserYbProductVisitCount struct {
	VisitCount int `json:"visit_count"`
	UserId     int `json:"user_id"`
}

// GetCompanyUserYbProductVisitCountByProductType 获取联系人分产品阅读统计
func GetCompanyUserYbProductVisitCountByProductType(userIds []int, productType, productId int) (list []*CompanyUserYbProductVisitCount, err error) {
	arrLen := len(userIds)
	if arrLen == 0 {
		return
	}
	inReplace := utils.GetOrmInReplace(arrLen)
	o := orm.NewOrm()
	sql := ``
	pars := make([]interface{}, 0)
	pars = append(pars, userIds)

	switch productType {
	case 1: // 语音播报
		sql = `SELECT COUNT(1) AS visit_count, user_id FROM yb_voice_broadcast_statistics WHERE user_id IN (` + inReplace + `) AND section_id=? GROUP BY user_id`
		pars = append(pars, productId)
	case 2: // 视频社区
		sql = `SELECT COUNT(1) AS visit_count, user_id FROM yb_community_video_play_log WHERE user_id IN (` + inReplace + `) AND type=? GROUP BY user_id `
		pars = append(pars, productId)
	case 3: // 问答社区
		sql = `SELECT COUNT(1) AS visit_count, user_id FROM yb_community_audio_listen_log WHERE user_id IN (` + inReplace + `) GROUP BY user_id `
	case 99: // 全部
		sql = `SELECT COUNT(1) AS visit_count, t.user_id FROM (
					SELECT user_id, 1 AS product_id FROM yb_voice_broadcast_statistics WHERE user_id IN (` + inReplace + `)
					UNION ALL
					SELECT user_id, 2 FROM yb_community_video_play_log WHERE user_id IN (` + inReplace + `)
					UNION ALL
					SELECT user_id, 3 FROM yb_community_audio_listen_log WHERE user_id IN (` + inReplace + `)
				) AS t
				GROUP BY t.user_id`
		_, err = o.Raw(sql, pars, pars, pars).QueryRows(&list)
		return
	}

	_, err = o.Raw(sql, pars).QueryRows(&list)
	return
}

// ProductVisitCount 分产品阅读统计详情
type ProductVisitCount struct {
	VisitCount  int    `description:"visit_count"`
	ProductType int    `description:"product_type"`
	ProductId   int    `description:"product_id"`
	ProductName string `description:"product_name"`
	RecentTime  string `description:"recent_time"`
}

// GetProductVisitCountByUserId 获取分产品阅读统计详情-根据用户
func GetProductVisitCountByUserId(userId int, startSize, pageSize int, orderRule string) (total int, list []*ProductVisitCount, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM (
				SELECT COUNT(1) AS visit_count, 1 AS product_type, section_id AS product_id, section_name AS product_name, MAX(create_time) AS recent_time 
					FROM yb_voice_broadcast_statistics WHERE user_id = ? GROUP BY section_id
				UNION ALL
				SELECT COUNT(1), 2, type AS product_id, if(type=1,"5分钟小视频","线上路演") AS product_name, MAX(create_time) FROM yb_community_video_play_log WHERE user_id = ? GROUP BY type
				UNION ALL
				SELECT COUNT(1), 3, 0, "问答社区" AS product_name, MAX(create_time) FROM yb_community_audio_listen_log WHERE user_id = ?
			) AS c `

	if orderRule != `` {
		sql += `ORDER BY ` + orderRule
	} else {
		sql += `ORDER BY recent_time DESC`
	}

	totalSQL := `SELECT COUNT(1) total FROM (` + sql + `) z `
	err = o.Raw(totalSQL, userId, userId, userId).QueryRow(&total)
	if err != nil {
		return
	}

	sql += ` LIMIT ?,?`
	_, err = o.Raw(sql, userId, userId, userId, startSize, pageSize).QueryRows(&list)
	return
}

// 联系人分产品阅读统计
type SellerYbProductCompanyCount struct {
	Num      int `json:"num"`
	SellerId int `json:"seller_id"`
}

// GetSellerYbProductCountByProductType 获取销售分产品阅读的记录数
func GetSellerYbProductCountByProductType(productId, productType int, companyStatus string, startDate, endDate time.Time) (list []*SellerYbProductCompanyCount, err error) {
	o := orm.NewOrm()
	sql := ``
	pars := make([]interface{}, 0)

	switch productType {
	case 1: // 语音播报
		sql = `SELECT COUNT(*) AS num, seller_id FROM yb_voice_broadcast_statistics WHERE company_status =? AND section_id=? and create_time >= ? and create_time <= ? GROUP BY seller_id`
		pars = append(pars, companyStatus, productId, startDate, endDate)
	case 2: // 视频社区
		sql = `SELECT COUNT(*) AS num, seller_id FROM yb_community_video_play_log WHERE company_status =? AND type=? and create_time >= ? and create_time <= ? GROUP BY seller_id `
		pars = append(pars, companyStatus, productId, startDate, endDate)
	case 3: // 问答社区
		sql = `SELECT COUNT(*) AS num, seller_id FROM yb_community_audio_listen_log WHERE company_status =? and create_time >= ? and create_time <= ?  GROUP BY seller_id `
		pars = append(pars, companyStatus, startDate, endDate)
	}

	_, err = o.Raw(sql, pars).QueryRows(&list)
	return
}

type SellerYbUserLogReq struct {
	PageSize      int    `description:"每页数据条数"`
	CurrentIndex  int    `description:"当前页页码，从1开始"`
	SortParam     string `description:"排序字段参数，用来排序的字段， 枚举值：'viewTotal':总阅读次数 、 'viewTime':阅读时间 、 'roadShowTotal':累计路演次数 、'expireDay'：到期时间 、 'createTime'：创建时间 、 'formalTime': 转正时间 、 'freezeTime'：冻结时间 、'lossTime'：流失时间" 、'deadline'：距离未完成的任务的截止日期的天数、'ybUserLog': 按照用户的点击量`
	SortType      string `description:"如何排序，是正序还是倒序，枚举值：asc 正序,desc 倒叙 "`
	SellerIds     string `description:"销售ID, 用','拼接 "`
	ProductType   int    `description:"产品类型"`
	ProductId     int    `description:"产品ID"`
	CompanyStatus string `description:"客户状态"`
	StartDate     string `description:"起始时间"`
	EndDate       string `description:"截止时间"`
}

// 联系人分产品阅读统计
type SellerYbProductUserCount struct {
	Num           int
	UserId        int
	RealName      string
	CompanyId     int
	CompanyStatus string
	CompanyName   string
}

// GetSellerYbProductUserCountByProductType 获取销售分产品阅读的用户记录汇总数
func GetSellerYbProductUserCountByProductType(productId, productType int, companyStatus string, startDate, endDate time.Time, sellerIds string, startSize, pageSize int, orderStr string) (list []*SellerYbProductUserCount, total int64, err error) {
	o := orm.NewOrm()
	sql := ``
	pars := make([]interface{}, 0)
	switch productType {
	case 1: // 语音播报
		sql = `SELECT COUNT(*) AS num, user_id, real_name, company_id, company_status, company_name FROM yb_voice_broadcast_statistics WHERE company_status =? AND section_id=? and create_time >= ? and create_time <= ? and seller_id in ` + sellerIds + ` GROUP BY user_id, company_id`
		pars = append(pars, companyStatus, productId, startDate, endDate)
	case 2: // 视频社区
		sql = `SELECT COUNT(*) AS num, user_id, real_name, company_id, company_status, company_name FROM yb_community_video_play_log WHERE company_status =? AND type=? and create_time >= ? and create_time <= ? and seller_id in ` + sellerIds + ` GROUP BY user_id, company_id`
		pars = append(pars, companyStatus, productId, startDate, endDate)
	case 3: // 问答社区
		sql = `SELECT COUNT(*) AS num, user_id, real_name, company_id, company_status, company_name FROM yb_community_audio_listen_log WHERE company_status =? and create_time >= ? and create_time <= ?  and seller_id in ` + sellerIds + ` GROUP BY user_id, company_id`
		pars = append(pars, companyStatus, startDate, endDate)
	}
	totalSQL := `SELECT COUNT(1) total FROM (` + sql + `) z `
	err = o.Raw(totalSQL, pars).QueryRow(&total)
	if err != nil {
		return
	}
	if orderStr != "" {
		sql += orderStr
	}
	sql += ` LIMIT ?,?`
	pars = append(pars, startSize, pageSize)
	_, err = o.Raw(sql, pars).QueryRows(&list)
	return
}

// UserYbProductVisitCount 用户研报产品阅读量信息
type UserYbProductVisitCount struct {
	ProductType int    `description:"类型名称: 问答社区/线上路演/语音播报板块名称等"`
	VisitCount  int    `description:"点击量"`
	TitleId     int    `description:"对应的不同产品的ID"`
	Title       string `description:"标题"`
	SectionId   int    `description:"语音播报板块ID"`
	NewSource   int    `description:"来源：1-小程序 2-PC端"`
	RecentTime  string `description:"最近点击时间"`
	VideoType   int    `description:"视频社区类型: 1-视频社区; 2-路演视频"`
}

// GetUserAllYbProductVisitCountPageList 获取用户所有研报产品阅读量-分页
func GetUserAllYbProductVisitCountPageList(userId, startSize, pageSize int, orderRule string) (total int, items []*UserYbProductVisitCount, err error) {
	o := orm.NewOrm()
	sub := `(
				SELECT
					1 AS product_type,
					COUNT(1) AS visit_count,
					broadcast_id AS title_id,
					broadcast_name AS title,
					section_id,
					IF (source = 4, 2, source) AS new_source,
					MAX(create_time) AS recent_time,
					0 AS video_type
			FROM
				yb_voice_broadcast_statistics
			WHERE
				user_id = ?
			GROUP BY
				broadcast_id,
				new_source
			)

			UNION ALL
			(
				SELECT
					2 AS product_type,
					COUNT(1) AS visit_count,
					community_video_id,
					"",
					0,
					IF (
						source_agent = 4,
						2,
						source_agent
					) AS new_source,
					MAX(create_time) AS recent_time,
					type AS video_type
			FROM
				yb_community_video_play_log
			WHERE
				user_id = ?
			GROUP BY
				community_video_id,
				new_source
			)

			UNION ALL
			(
				SELECT
					3 AS product_type,
					COUNT(1) AS visit_count,
					community_question_id,
					"",
					0,
					IF (
						source_agent = 4,
						2,
						source_agent
					) AS new_source,
					MAX(create_time) AS recent_time,
					0 AS video_type
			FROM
				yb_community_audio_listen_log
			WHERE
				user_id = ?
			GROUP BY
				community_question_id,
				new_source
			)`

	totalSql := fmt.Sprintf(`SELECT COUNT(1) FROM (%s) AS t`, sub)
	err = o.Raw(totalSql, userId, userId, userId).QueryRow(&total)
	if err != nil {
		return
	}

	order := `ORDER BY recent_time DESC`
	if orderRule != "" {
		order = `ORDER BY ` + orderRule
	}

	sql := fmt.Sprintf(`SELECT * FROM (%s) AS t %s LIMIT ?, ?`, sub, order)
	_, err = o.Raw(sql, userId, userId, userId, startSize, pageSize).QueryRows(&items)
	return
}
