package models

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

type CygxArticleDepartment struct {
	DepartmentId int       `orm:"column(department_id);pk;主键ID"`
	CreateTime   time.Time `description:"创建时间"`
	NickName     string    `description:"昵称"`
	Remark       string    `description:"备注"`
	Remarks      string    `description:"备注辅助字段"`
	Content      string    `description:"初始内容"`
}

type CygxArticleDepartmentRep struct {
	DepartmentId int    `description:"作者ID"`
	CreateTime   string `description:"创建时间"`
	NickName     string `description:"昵称"`
	ImgUrl       string `description:"头像链接"`
	FollowNum    int    `description:"是否关注 1是 ，0 否"`
	IsMyFollow   bool   `description:"是否关注"`
	List         []*IndustrialManagementIdName
}

type CygxArticleDepartmentRepPc struct {
	DepartmentId int    `description:"作者ID"`
	CreateTime   string `description:"创建时间"`
	NickName     string `description:"昵称"`
	ImgUrl       string `description:"头像链接"`
	FollowNum    int    `description:"是否关注 1是 ，0 否"`
	IsMyFollow   bool   `description:"是否关注"`
	List         []*IndustrialManagementIdNamePc
}

type CygxArticleDepartmentId struct {
	DepartmentId int `description:"作者ID"`
}

type CygxArticleDepartmentList struct {
	HaveResearch bool               `description:"是否有研选权限"`
	Paging       *paging.PagingItem `description:"分页数据"`
	ListnNew     []*IndustrialManagementIdInt
	List         []*CygxArticleDepartmentRep
}

type CygxArticleDepartmentListPc struct {
	HaveResearch bool               `description:"是否有研选权限"`
	Paging       *paging.PagingItem `description:"分页数据"`
	ListnNew     []*IndustrialManagementIdInt
	List         []*CygxArticleDepartmentRepPc
}

//详情
func GetArticleDepartmentDateil(nickName, remarks string) (item *CygxArticleDepartmentRep, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM cygx_article_department WHERE nick_name = ? AND  remarks = ? LIMIT 1 `
	err = o.Raw(sql, nickName, remarks).QueryRow(&item)
	return
}

func GetArticleDepartmentDateilById(departmentId int) (item *CygxArticleDepartmentRep, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM cygx_article_department WHERE department_id = ? `
	err = o.Raw(sql, departmentId).QueryRow(&item)
	return
}

//数量
func GetArticleDepartmentCount(condition string) (count int, err error) {
	o := orm.NewOrm()
	sql := ` SELECT COUNT(*) count
		FROM
		( SELECT COUNT(1)  FROM cygx_article_department AS d
		INNER JOIN cygx_article AS a ON a.department_id = d.department_id
		INNER JOIN cygx_industrial_article_group_management AS mg ON mg.article_id = a.article_id
		INNER JOIN cygx_industrial_management AS m ON m.industrial_management_id = mg.industrial_management_id WHERE 1= 1  `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY d.department_id ) AS num `

	err = o.Raw(sql).QueryRow(&count)
	return
}

//列表
func GetCygxArticleDepartmentList(startSize, pageSize int, condition string, uid int) (items []*CygxArticleDepartmentRep, err error) {
	o := orm.NewOrm()
	sql := `SELECT d.*,
		( SELECT COUNT( 1 ) FROM cygx_article AS a INNER JOIN cygx_article_history_record as h ON h.article_id = a.article_id WHERE  a.department_id = d.department_id  AND a.publish_status = 1 ) AS art_num,
		( SELECT COUNT( 1 ) FROM cygx_article_department_follow AS f  WHERE f.department_id = d.department_id AND f.user_id = ? AND f.type = 1 ) AS follow_num
		FROM
		cygx_article_department AS d
		INNER JOIN cygx_article AS a ON a.department_id = d.department_id
		INNER JOIN cygx_industrial_article_group_management AS mg ON mg.article_id = a.article_id
		INNER JOIN cygx_industrial_management AS m ON m.industrial_management_id = mg.industrial_management_id  WHERE 1= 1 `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY
		d.department_id
		ORDER BY  art_num DESC  LIMIT ?,?`
	_, err = o.Raw(sql, uid, startSize, pageSize).QueryRows(&items)
	return
}

//列表
func GetCygxArticleDepartmentListPc(startSize, pageSize int, condition string, uid int) (items []*CygxArticleDepartmentRepPc, err error) {
	o := orm.NewOrm()
	sql := `SELECT d.*,
		( SELECT COUNT( 1 ) FROM cygx_article AS a INNER JOIN cygx_article_history_record as h ON h.article_id = a.article_id WHERE  a.department_id = d.department_id  AND a.publish_status = 1 ) AS art_num,
		( SELECT COUNT( 1 ) FROM cygx_article_department_follow AS f  WHERE f.department_id = d.department_id AND f.user_id = ? AND f.type = 1 ) AS follow_num
		FROM
		cygx_article_department AS d
		INNER JOIN cygx_article AS a ON a.department_id = d.department_id
		INNER JOIN cygx_industrial_article_group_management AS mg ON mg.article_id = a.article_id
		INNER JOIN cygx_industrial_management AS m ON m.industrial_management_id = mg.industrial_management_id  WHERE 1= 1 `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY
		d.department_id
		ORDER BY  art_num DESC  LIMIT ?,?`
	_, err = o.Raw(sql, uid, startSize, pageSize).QueryRows(&items)
	return
}

//产业列表
func GetIndustrialSubjectByDepartment(departmentId int) (items []*IndustrialManagementIdInt, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			m.*,
			( SELECT GROUP_CONCAT(DISTINCT s.subject_name ORDER BY s.create_time DESC SEPARATOR '/' ) FROM cygx_industrial_subject AS s WHERE s.industrial_management_id = m.industrial_management_id) AS subject_name,
			( SELECT publish_date FROM cygx_article AS a INNER JOIN cygx_industrial_article_group_management as mg ON mg.article_id = a.article_id  ORDER BY publish_date DESC LIMIT 1  ) AS art_time
		FROM
			cygx_article_department AS d
			INNER JOIN cygx_article AS a ON a.department_id = d.department_id
			INNER JOIN cygx_industrial_article_group_management AS mg ON mg.article_id = a.article_id
			INNER JOIN cygx_industrial_management AS m ON m.industrial_management_id = mg.industrial_management_id 
			WHERE 1= 1
			AND d.department_id = ?
			AND a.publish_status = 1
			GROUP BY a.article_id
			ORDER BY art_time DESC
			LIMIT 4 `
	_, err = o.Raw(sql, departmentId).QueryRows(&items)
	return
}

//最新产业列表
func GetIndustrialSubjectByDepartmentNew() (items []*IndustrialManagementIdInts, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	a.article_id,
	a.industrial_and_subject_ids,
	m.*,
	MAX( a.publish_date ) art_time
FROM
	cygx_article_department AS d
	INNER  JOIN cygx_article AS a ON a.department_id = d.department_id
	LEFT JOIN cygx_industrial_article_group_management AS mg ON mg.article_id = a.article_id
	LEFT JOIN cygx_industrial_management AS m ON m.industrial_management_id = mg.industrial_management_id 
	LEFT JOIN cygx_industrial_article_group_subject AS sg  ON sg.article_id = a.article_id
WHERE
	1 = 1 
	AND a.publish_status = 1 
	AND a.industrial_and_subject_ids != '' 
GROUP BY
	a.industrial_and_subject_ids 
ORDER BY
	art_time DESC 
	LIMIT 6 `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

//获取作者数量
func GetDepartmentCount(departmentId int) (count int, err error) {
	sqlCount := ` SELECT COUNT(1) AS count  FROM cygx_article_department WHERE department_id=? `
	o := orm.NewOrm()
	err = o.Raw(sqlCount, departmentId).QueryRow(&count)
	return
}

type IndustrialSubjectList struct {
	SubjectName string `description:"标的名称"`
}

//最新标的列表
func GetIndustrialSubjectNewList(condition string) (items []*IndustrialSubjectList, err error) {
	o := orm.NewOrm()
	sql := `SELECT s.*
			FROM
				cygx_industrial_subject AS s
				INNER JOIN cygx_industrial_article_group_subject AS sg ON sg.industrial_subject_id = s.industrial_subject_id
				INNER JOIN cygx_article AS art ON art.article_id = sg.article_id
				INNER JOIN cygx_industrial_article_group_management AS mg ON mg.article_id = art.article_id
			WHERE
				1 = 1 
				AND art.publish_status = 1 `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY s.industrial_subject_id ORDER BY s.create_time DESC LIMIT 4`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

//最新标的列表
func GetIndustrialSubjectNewListByDepartmentId(departmentId, industrialManagementId int) (items []*IndustrialManagementIdName, err error) {
	o := orm.NewOrm()
	sql := `SELECT art.article_id,
			( SELECT GROUP_CONCAT( DISTINCT s.subject_name ORDER BY s.industrial_subject_id DESC SEPARATOR '/' )  ) AS subject_name
			FROM
				cygx_article AS art
				INNER JOIN cygx_industrial_article_group_subject AS sg ON sg.article_id = art.article_id
				INNER JOIN cygx_industrial_subject AS s ON s.industrial_subject_id = sg.industrial_subject_id 
			WHERE
				department_id = ?
				AND s.industrial_management_id = ?
				GROUP BY art.article_id
				ORDER BY s.create_time DESC  LIMIT 4`
	_, err = o.Raw(sql, departmentId, industrialManagementId).QueryRows(&items)
	return
}

//最新标的列表
func GetArticleByDepartmentId(departmentId int) (items []*IndustrialManagementIdName, err error) {
	o := orm.NewOrm()
	sql := `SELECT article_id,is_report,MAX( publish_date ) art_time  FROM cygx_article WHERE department_id = ? AND  publish_status = 1 AND subject_ids != '' GROUP  BY subject_ids  ORDER BY art_time DESC  LIMIT 4 `
	_, err = o.Raw(sql, departmentId).QueryRows(&items)
	return
}

func GetSubjectNames(articleId int) (subjects string, err error) {
	sql := ` SELECT GROUP_CONCAT( DISTINCT s.subject_name ORDER BY id ASC SEPARATOR '/' ) AS subject_name 
				FROM
				cygx_industrial_article_group_subject as sg 
				INNER JOIN cygx_industrial_subject as  s ON s.industrial_subject_id = sg.industrial_subject_id 
				WHERE article_id = ?`
	o := orm.NewOrm()
	err = o.Raw(sql, articleId).QueryRow(&subjects)
	return
}

func GetIndustrialNames(articleId int) (Industrial string, err error) {
	sql := ` SELECT
	m.industry_name
FROM
	cygx_industrial_article_group_management as mg 
	INNER JOIN cygx_industrial_management as  m ON m.industrial_management_id = mg.industrial_management_id 
WHERE
	mg.article_id = ?
	ORDER BY  m.industrial_management_id DESC
	LIMIT 1
`
	o := orm.NewOrm()
	err = o.Raw(sql, articleId).QueryRow(&Industrial)
	return
}

type IndustrialManagementIdNamePc struct {
	Title        string `description:"标题"`
	PublishDate  string `description:"发布时间"`
	DepartmentId int    `description:"作者ID"`
	ArticleId    int    `description:"文章id"`
	IndustryName string `description:"产业名称"`
	SubjectName  string `description:"标的名称"`
	IsReport     string `description:"1观点，0纪要"`
	Pv           int    `description:"Pv"`
}

//最新标的列表
func GetArticleByDepartmentIdPc(departmentId int, articleIdGroup string) (items []*IndustrialManagementIdNamePc, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			m.industry_name,
			(SELECT count(1) FROM cygx_article_history_record_newpv as h WHERE h.article_id = art.article_id ) as pv,
			GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR '/' ) AS subject_name,
			art.* 
		FROM
			cygx_article AS art
			LEFT JOIN cygx_industrial_article_group_management AS mg ON mg.article_id = art.article_id
			LEFT JOIN cygx_industrial_management AS m ON m.industrial_management_id = mg.industrial_management_id
			LEFT JOIN cygx_industrial_article_group_subject AS sg ON sg.article_id = art.article_id
			LEFT JOIN cygx_industrial_subject AS s ON s.industrial_subject_id = sg.industrial_subject_id 
		WHERE
			department_id = ? 
			AND publish_status = 1`
	if articleIdGroup != "" {
		sql += ` AND art.article_id IN (` + articleIdGroup + `) `
	}
	sql += ` GROUP BY art.article_id ORDER BY publish_date DESC `
	_, err = o.Raw(sql, departmentId).QueryRows(&items)
	return
}