eta_api/models/rag/wechat_article.go

package rag

import (
	"database/sql"
	"eta/eta_api/global"
	"eta/eta_api/utils"
	"fmt"
	"strings"
	"time"
)

type WechatArticle struct {
	WechatArticleId   int       `gorm:"column:wechat_article_id;type:int(10) UNSIGNED;primaryKey;not null;" description:""`
	WechatPlatformId  int       `gorm:"column:wechat_platform_id;type:int(11);comment:归属公众号id;default:0;" description:"归属公众号id"`
	FakeId            string    `gorm:"column:fake_id;type:varchar(255);comment:公众号唯一id;" description:"公众号唯一id"`
	Title             string    `gorm:"column:title;type:varchar(255);comment:标题;" description:"标题"`
	Link              string    `gorm:"column:link;type:varchar(255);comment:链接;" description:"链接"`
	CoverUrl          string    `gorm:"column:cover_url;type:varchar(255);comment:公众号封面;" description:"公众号封面"`
	Description       string    `gorm:"column:description;type:varchar(255);comment:描述;" description:"描述"`
	Content           string    `gorm:"column:content;type:longtext;comment:报告详情;" description:"报告详情"`
	TextContent       string    `gorm:"column:text_content;type:text;comment:文本内容;" description:"文本内容"`
	AbstractStatus    int       `gorm:"column:abstract_status;type:tinyint(4);comment:摘要生成情况，-1：生成失败，0：待生成，1：已生成;default:0;" description:"摘要生成情况，-1：生成失败，0：待生成，1：已生成"`
	Country           string    `gorm:"column:country;type:varchar(255);comment:国家;" description:"国家"`
	Province          string    `gorm:"column:province;type:varchar(255);comment:省;" description:"省"`
	City              string    `gorm:"column:city;type:varchar(255);comment:市;" description:"市"`
	ArticleCreateTime time.Time `gorm:"column:article_create_time;type:datetime;comment:报告创建时间;default:NULL;" description:"报告创建时间"`
	VectorKey         string    `gorm:"column:vector_key;type:varchar(255);comment:向量key标识;" description:"向量key标识"`
	IsDeleted         int       `gorm:"column:is_deleted;type:tinyint(4);comment:是否删除，0：未删除，1： 已删除;default:0;" description:"是否删除，0：未删除，1： 已删除"`
	ModifyTime        time.Time `gorm:"column:modify_time;type:datetime;comment:修改时间;default:NULL;" description:"修改时间"`
	CreateTime        time.Time `gorm:"column:create_time;type:datetime;comment:入库时间;default:NULL;" description:"入库时间"`
}

// TableName get sql table name.获取数据库表名
func (m *WechatArticle) TableName() string {
	return "wechat_article"
}

// WechatArticleColumns get sql column name.获取数据库列名
var WechatArticleColumns = struct {
	WechatArticleID   string
	WechatPlatformID  string
	FakeID            string
	Title             string
	Link              string
	CoverURL          string
	Description       string
	Content           string
	TextContent       string
	AbstractStatus    string
	Country           string
	Province          string
	City              string
	ArticleCreateTime string
	IsDeleted         string
	ModifyTime        string
	CreateTime        string
}{
	WechatArticleID:   "wechat_article_id",
	WechatPlatformID:  "wechat_platform_id",
	FakeID:            "fake_id",
	Title:             "title",
	Link:              "link",
	CoverURL:          "cover_url",
	Description:       "description",
	Content:           "content",
	TextContent:       "text_content",
	AbstractStatus:    "abstract_status",
	Country:           "country",
	Province:          "province",
	City:              "city",
	ArticleCreateTime: "article_create_time",
	IsDeleted:         "is_deleted",
	ModifyTime:        "modify_time",
	CreateTime:        "create_time",
}

type WechatArticleView struct {
	WechatArticleId            int    `gorm:"column:wechat_article_id;type:int(10) UNSIGNED;primaryKey;not null;" description:""`
	WechatPlatformId           int    `gorm:"column:wechat_platform_id;type:int(11);comment:归属公众号id;default:0;" description:"归属公众号id"`
	FakeId                     string `gorm:"column:fake_id;type:varchar(255);comment:公众号唯一id;" description:"公众号唯一id"`
	Title                      string `gorm:"column:title;type:varchar(255);comment:标题;" description:"标题"`
	Link                       string `gorm:"column:link;type:varchar(255);comment:链接;" description:"链接"`
	CoverUrl                   string `gorm:"column:cover_url;type:varchar(255);comment:公众号封面;" description:"公众号封面"`
	Description                string `gorm:"column:description;type:varchar(255);comment:描述;" description:"描述"`
	Content                    string `gorm:"column:content;type:longtext;comment:报告详情;" description:"报告详情"`
	TextContent                string `gorm:"column:text_content;type:text;comment:文本内容;" description:"文本内容"`
	AbstractStatus             int    `gorm:"column:abstract_status;type:tinyint(4);comment:摘要生成情况，-1：生成失败，0：待生成，1：已生成;default:0;" description:"摘要生成情况，-1：生成失败，0：待生成，1：已生成"`
	Abstract                   string `gorm:"column:abstract;type:text;comment:摘要;" description:"摘要"`
	Country                    string `gorm:"column:country;type:varchar(255);comment:国家;" description:"国家"`
	Province                   string `gorm:"column:province;type:varchar(255);comment:省;" description:"省"`
	City                       string `gorm:"column:city;type:varchar(255);comment:市;" description:"市"`
	ArticleCreateTime          string `gorm:"column:article_create_time;type:datetime;comment:报告创建时间;default:NULL;" description:"报告创建时间"`
	ModifyTime                 string `gorm:"column:modify_time;type:datetime;comment:修改时间;default:NULL;" description:"修改时间"`
	CreateTime                 string `gorm:"column:create_time;type:datetime;comment:入库时间;default:NULL;" description:"入库时间"`
	WechatPlatformName         string `gorm:"column:title;type:varchar(255);comment:标题;" description:"微信公众号名称"`
	WechatPlatformRoundHeadImg string `gorm:"column:round_head_img;type:varchar(255);comment:头像;" description:"微信公众号头像"`
	TagName                    string `gorm:"column:tag_name;type:varchar(255);comment:标签名称;" description:"标签名称"`
	TagId                      int    `gorm:"column:tag_id;type:varchar(255);comment:标签id;" description:"标签id"`
}

func (m *WechatArticle) ToView() WechatArticleView {
	var articleCreateTime, modifyTime, createTime string

	if !m.ArticleCreateTime.IsZero() {
		articleCreateTime = m.ArticleCreateTime.Format(utils.FormatDateTime)
	}
	if !m.CreateTime.IsZero() {
		createTime = m.CreateTime.Format(utils.FormatDateTime)
	}
	if !m.ModifyTime.IsZero() {
		modifyTime = m.ModifyTime.Format(utils.FormatDateTime)
	}
	return WechatArticleView{
		WechatArticleId:            m.WechatArticleId,
		WechatPlatformId:           m.WechatPlatformId,
		FakeId:                     m.FakeId,
		Title:                      m.Title,
		Link:                       m.Link,
		CoverUrl:                   m.CoverUrl,
		Description:                m.Description,
		Content:                    m.Content,
		TextContent:                m.TextContent,
		AbstractStatus:             m.AbstractStatus,
		Country:                    m.Country,
		Province:                   m.Province,
		City:                       m.City,
		ArticleCreateTime:          articleCreateTime,
		ModifyTime:                 modifyTime,
		CreateTime:                 createTime,
		WechatPlatformName:         "",
		WechatPlatformRoundHeadImg: "",
	}
}

func (m *WechatArticle) ListToViewList(list []*WechatArticle) (wechatArticleViewList []WechatArticleView) {
	wechatArticleViewList = make([]WechatArticleView, 0)

	for _, v := range list {
		wechatArticleViewList = append(wechatArticleViewList, v.ToView())
	}
	return
}

func (m *WechatArticle) Create() (err error) {
	err = global.DbMap[utils.DbNameAI].Create(&m).Error

	return
}

func (m *WechatArticle) Update(updateCols []string) (err error) {
	err = global.DbMap[utils.DbNameAI].Select(updateCols).Updates(&m).Error

	return
}

func (m *WechatArticle) GetById(id int) (item *WechatArticle, err error) {
	err = global.DbMap[utils.DbNameAI].Where(fmt.Sprintf("%s = ?", WechatArticleColumns.WechatArticleID), id).First(&item).Error

	return
}
func GetArticleById(id int) (item *WechatArticle, err error) {
	err = global.DbMap[utils.DbNameAI].Where(fmt.Sprintf("%s = ?", WechatArticleColumns.WechatArticleID), id).First(&item).Error
	return
}
func (m *WechatArticle) GetByLink(link string) (item *WechatArticle, err error) {
	err = global.DbMap[utils.DbNameAI].Where(fmt.Sprintf("%s = ?", WechatArticleColumns.Link), link).First(&item).Error

	return
}

func (m *WechatArticle) GetListByCondition(field, condition string, pars []interface{}, startSize, pageSize int) (items []*WechatArticle, err error) {
	if field == "" {
		field = "*"
	}
	sqlStr := fmt.Sprintf(`SELECT %s FROM %s WHERE 1=1 AND is_deleted=0 %s  order by article_create_time desc,wechat_article_id desc LIMIT ?,?`, field, m.TableName(), condition)
	pars = append(pars, startSize, pageSize)
	err = global.DbMap[utils.DbNameAI].Raw(sqlStr, pars...).Find(&items).Error

	return
}

func (m *WechatArticle) GetCountByCondition(condition string, pars []interface{}) (total int, err error) {
	var intNull sql.NullInt64
	sqlStr := fmt.Sprintf(`SELECT COUNT(1) total FROM %s WHERE 1=1 AND is_deleted=0 %s`, m.TableName(), condition)
	err = global.DbMap[utils.DbNameAI].Raw(sqlStr, pars...).Scan(&intNull).Error
	if err == nil && intNull.Valid {
		total = int(intNull.Int64)
	}

	return
}

func (m *WechatArticle) GetPageListByCondition(condition string, pars []interface{}, startSize, pageSize int) (total int, items []*WechatArticle, err error) {

	total, err = m.GetCountByCondition(condition, pars)
	if err != nil {
		return
	}
	if total > 0 {
		items, err = m.GetListByCondition(`wechat_article_id,wechat_platform_id,fake_id,title,link,cover_url,description,country,province,city,article_create_time,modify_time,create_time`, condition, pars, startSize, pageSize)
	}

	return
}

type WechatArticleAndPlatform struct {
	WechatArticleId   int       `gorm:"column:wechat_article_id;type:int(10) UNSIGNED;primaryKey;not null;" description:""`
	WechatPlatformId  int       `gorm:"column:wechat_platform_id;type:int(11);comment:归属公众号id;default:0;" description:"归属公众号id"`
	FakeId            string    `gorm:"column:fake_id;type:varchar(255);comment:公众号唯一id;" description:"公众号唯一id"`
	Title             string    `gorm:"column:title;type:varchar(255);comment:标题;" description:"标题"`
	Link              string    `gorm:"column:link;type:varchar(255);comment:链接;" description:"链接"`
	CoverUrl          string    `gorm:"column:cover_url;type:varchar(255);comment:公众号封面;" description:"公众号封面"`
	Description       string    `gorm:"column:description;type:varchar(255);comment:描述;" description:"描述"`
	Content           string    `gorm:"column:content;type:longtext;comment:报告详情;" description:"报告详情"`
	TextContent       string    `gorm:"column:text_content;type:text;comment:文本内容;" description:"文本内容"`
	Abstract          string    `gorm:"column:abstract;type:text;comment:摘要;" description:"摘要"`
	Country           string    `gorm:"column:country;type:varchar(255);comment:国家;" description:"国家"`
	Province          string    `gorm:"column:province;type:varchar(255);comment:省;" description:"省"`
	City              string    `gorm:"column:city;type:varchar(255);comment:市;" description:"市"`
	ArticleCreateTime time.Time `gorm:"column:article_create_time;type:datetime;comment:报告创建时间;default:NULL;" description:"报告创建时间"`
	IsDeleted         int       `gorm:"column:is_deleted;type:tinyint(4);comment:是否删除，0：未删除，1： 已删除;default:0;" description:"是否删除，0：未删除，1： 已删除"`
	ModifyTime        time.Time `gorm:"column:modify_time;type:datetime;comment:修改时间;default:NULL;" description:"修改时间"`
	CreateTime        time.Time `gorm:"column:create_time;type:datetime;comment:入库时间;default:NULL;" description:"入库时间"`
	Nickname          string    `gorm:"column:nickname;type:varchar(255);comment:公众号名称;" description:"nickname"`          // 公众号名称
	Alias             string    `gorm:"column:alias;type:varchar(255);comment:别名;" description:"alias"`                   // 别名
	RoundHeadImg      string    `gorm:"column:round_head_img;type:varchar(255);comment:头像;" description:"round_head_img"` // 头像
}

func (m *WechatArticleAndPlatform) ToView() WechatArticleView {
	var articleCreateTime, modifyTime, createTime string

	if !m.ArticleCreateTime.IsZero() {
		articleCreateTime = m.ArticleCreateTime.Format(utils.FormatDateTime)
	}
	if !m.CreateTime.IsZero() {
		createTime = m.CreateTime.Format(utils.FormatDateTime)
	}
	if !m.ModifyTime.IsZero() {
		modifyTime = m.ModifyTime.Format(utils.FormatDateTime)
	}
	return WechatArticleView{
		WechatArticleId:            m.WechatArticleId,
		WechatPlatformId:           m.WechatPlatformId,
		FakeId:                     m.FakeId,
		Title:                      m.Title,
		Link:                       m.Link,
		CoverUrl:                   m.CoverUrl,
		Description:                m.Description,
		Content:                    m.Content,
		TextContent:                m.TextContent,
		Abstract:                   m.Abstract,
		Country:                    m.Country,
		Province:                   m.Province,
		City:                       m.City,
		ArticleCreateTime:          articleCreateTime,
		ModifyTime:                 modifyTime,
		CreateTime:                 createTime,
		WechatPlatformName:         m.Nickname,
		WechatPlatformRoundHeadImg: m.RoundHeadImg,
	}
}

func (m *WechatArticle) ArticleAndPlatformListToViewList(list []*WechatArticleAndPlatform) (wechatArticleViewList []WechatArticleView) {
	wechatArticleViewList = make([]WechatArticleView, 0)

	for _, v := range list {
		wechatArticleViewList = append(wechatArticleViewList, v.ToView())
	}
	return
}

func (m *WechatArticle) GetListByPlatformCondition(field, condition string, pars []interface{}, startSize, pageSize int) (items []*WechatArticleAndPlatform, err error) {
	if field == "" {
		field = "*"
	}
	sqlStr := fmt.Sprintf(`SELECT %s FROM %s AS a 
          JOIN wechat_platform AS b ON a.wechat_platform_id=b.wechat_platform_id
          WHERE 1=1 AND a.is_deleted=0 %s  order by a.article_create_time DESC,a.wechat_article_id DESC LIMIT ?,?`, field, m.TableName(), condition)
	pars = append(pars, startSize, pageSize)
	err = global.DbMap[utils.DbNameAI].Raw(sqlStr, pars...).Find(&items).Error

	return
}

func (m *WechatArticle) GetCountByPlatformCondition(condition string, pars []interface{}) (total int, err error) {
	var intNull sql.NullInt64
	sqlStr := fmt.Sprintf(`SELECT COUNT(1) total FROM %s AS a 
          JOIN wechat_platform AS b ON a.wechat_platform_id=b.wechat_platform_id 
          WHERE 1=1 AND a.is_deleted=0 %s`, m.TableName(), condition)
	err = global.DbMap[utils.DbNameAI].Raw(sqlStr, pars...).Scan(&intNull).Error
	if err == nil && intNull.Valid {
		total = int(intNull.Int64)
	}

	return
}

func (m *WechatArticle) GetPageListByPlatformCondition(condition string, pars []interface{}, startSize, pageSize int) (total int, items []*WechatArticleAndPlatform, err error) {
	total, err = m.GetCountByPlatformCondition(condition, pars)
	if err != nil {
		return
	}
	if total > 0 {
		items, err = m.GetListByPlatformCondition(`a.wechat_article_id,a.wechat_platform_id,a.fake_id,a.title,a.link,a.cover_url,a.description,a.country,a.province,a.city,a.article_create_time,a.modify_time,a.create_time,b.nickname,b.round_head_img`, condition, pars, startSize, pageSize)
	}

	return
}

type DafWechatArticleItem struct {
	WechatArticleId int    `gorm:"column:wechat_article_id;type:int(10) UNSIGNED;primaryKey;not null;" description:""`
	Title           string `gorm:"column:title;type:varchar(255);comment:标题;" description:"标题"`
	TextContent     string `gorm:"column:text_content;type:text;comment:文本内容;" description:"文本内容"`
}

func (m *WechatArticle) ToDAFView() *DafWechatArticleItem {
	return &DafWechatArticleItem{
		WechatArticleId: m.WechatArticleId,
		Title:           m.Title,
		TextContent:     m.TextContent,
	}
}
func FetchArticleDafTagMappingList(lastId int, excludeIds []int, fetchSize int) (items []*DafWechatArticleItem, id int, err error) {
	var articleList []*WechatArticle
	if len(excludeIds) == 0 {
		err = global.DbMap[utils.DbNameAI].Model(&WechatArticle{}).Select("wechat_article_id,title,text_content").Where("wechat_article_id > ? and is_deleted=?", lastId, 0).Order("wechat_article_id asc").Limit(fetchSize).Find(&articleList).Error
	} else {
		err = global.DbMap[utils.DbNameAI].Model(&WechatArticle{}).Select("wechat_article_id,title,text_content").Where("wechat_article_id > ? and wechat_article_id not in (?) and is_deleted=?", lastId, excludeIds, 0).Order("wechat_article_id asc").Limit(fetchSize).Find(&articleList).Error
	}
	if err != nil {
		return
	}
	for _, article := range articleList {
		items = append(items, article.ToDAFView())
	}
	if len(articleList) > 1 {
		id = articleList[len(articleList)-1].WechatArticleId
	}
	return
}

func GetArticleByTags(labels []string, daysBefore int, fetchSize int, sourceList []string) (items []*DafWechatArticleItem, err error) {
	var tagSql = `select article_id from article_dfa_tag_mapping where (`
	for i := 0; i < len(labels); i++ {
		if i == 0 {
			tagSql += fmt.Sprintf(`find_in_set('%s', tag_name)`, labels[i])
		} else {
			tagSql += fmt.Sprintf(` or find_in_set('%s', tag_name)`, labels[i])
		}
	}
	tagSql += `)`
	endDate := time.Now()
	startDate := endDate.AddDate(0, 0, -daysBefore)
	var limitedCondition string
	if fetchSize > 0 {
		limitedCondition = fmt.Sprintf("limit 0,%d", fetchSize)
	}
	var sourceCondition string
	if len(sourceList) > 0 {
		sourceCondition = fmt.Sprintf("and source in (%s)", strings.Join(sourceList, ","))
	}
	var dateCondition = fmt.Sprintf("and article_create_time between '%s' and  '%s'", startDate.Format(utils.FormatDate), endDate.Format(utils.FormatDate))
	var sql = fmt.Sprintf(`select text_content from wechat_article where   wechat_article_id in (%s) and is_deleted = 0 %s order by article_create_time desc %s`, tagSql, sourceCondition, limitedCondition)
	var sqlWithDays = fmt.Sprintf(`select text_content from wechat_article where   wechat_article_id in (%s) and is_deleted = 0 %s %s %s `, tagSql, dateCondition, sourceCondition, limitedCondition)
	err = global.DbMap[utils.DbNameAI].Raw(sqlWithDays).Find(&items).Error
	if err != nil {
		return
	}
	if len(items) == 0 {
		err = global.DbMap[utils.DbNameAI].Raw(sql).Find(&items).Error
	}
	return
}