package models

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

type WxUserWhite struct {
	Id                  int `orm:"column(id);pk"`
	Mobile              string
	CreatedTime         time.Time
	UserCreatedTime     time.Time
	OutboundMobile      string `description:"外呼手机号"`
	Status              string `description:"客户状态'试用','永续','冻结','流失','正式','潜在'"`
	CountryCode         string `description:"区号"`
	OutboundCountryCode string `description:"外呼手机号区号"`
	CompanyName         string `description:"公司名称"`
	PermissionName      string `description:"拥有权限分类"`
	RealName            string `description:"姓名"`
	SellerName          string `description:"销售"`
}

// 添加
func AddWxUserWhite(item *WxUserWhite) (lastId int64, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	lastId, err = o.Insert(item)
	return
}

// 批量添加
func AddAddWxUserWhiteMulti(items []*WxUserWhite) (err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	if len(items) > 0 {
		//批量添加新的关注记录
		_, err = o.InsertMulti(len(items), items)
	}
	return
}

// 获取用户手机号白名单
func GetWxUserWhiteMobile() (mobileStr string, err error) {
	sql := ` SELECT
			GROUP_CONCAT( DISTINCT u.mobile SEPARATOR ',' ) AS mobileStr 
			FROM
				wx_user AS u
				INNER JOIN company AS c ON c.company_id = u.company_id
				INNER JOIN company_report_permission AS p ON p.company_id = u.company_id
				INNER JOIN chart_permission AS b ON b.chart_permission_id = p.chart_permission_id
				INNER JOIN company_product AS cp ON cp.company_id = u.company_id 
			WHERE
				1 = 1 
				AND cp.product_id = 2 
				AND u.mobile NOT IN ( SELECT mobile FROM wx_user_white ) 
				AND u.mobile != ''
				AND cp.status IN ( '正式', '试用' ) `
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql).QueryRow(&mobileStr)
	return
}

// 获取用户外呼手机号白名单
func GetWxUserWhiteOutboundMobile() (mobileStr string, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := ` SELECT
			GROUP_CONCAT( DISTINCT u.outbound_mobile SEPARATOR ',' ) AS outboundmobileStr 
			FROM
				wx_user AS u
				INNER JOIN company AS c ON c.company_id = u.company_id
				INNER JOIN company_report_permission AS p ON p.company_id = u.company_id
				INNER JOIN chart_permission AS b ON b.chart_permission_id = p.chart_permission_id
				INNER JOIN company_product AS cp ON cp.company_id = u.company_id 
			WHERE
				1 = 1 
				AND cp.product_id = 2 
				AND u.outbound_mobile NOT IN ( SELECT outbound_mobile FROM wx_user_white ) 
				AND u.outbound_mobile NOT IN ( SELECT mobile FROM wx_user_white ) 
				AND cp.status IN ( '正式', '试用' )
				AND u.mobile != ''
				AND u.mobile !=  u.outbound_mobile `
	err = o.Raw(sql).QueryRow(&mobileStr)
	return
}

// 获取冻结用户白名单
func GetFrozenUserWhiteList() (items []*WxUserWhite, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := `SELECT
				* 
			FROM
				wx_user_white AS w 
			WHERE
				w.mobile NOT IN (
				SELECT
					mobile 
				FROM
					wx_user AS u
					INNER JOIN company AS c ON c.company_id = u.company_id
					INNER JOIN company_report_permission AS p ON p.company_id = u.company_id
					INNER JOIN chart_permission AS b ON b.chart_permission_id = p.chart_permission_id
					INNER JOIN company_product AS cp ON cp.company_id = u.company_id 
				WHERE
					1 = 1 
					AND cp.product_id = 2 
					AND cp.STATUS IN ( '正式', '试用' ) 
					AND mobile != '' 
				) 
				AND w.outbound_mobile = '' ` //OR w.outbound_mobile NOT IN ( SELECT outbound_mobile FROM wx_user )`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取冻结用户白名单外呼号
func GetFrozenUserWhiteListOutbound() (items []*WxUserWhite, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := `SELECT
				* 
			FROM
				wx_user_white AS w 
			WHERE
				w.outbound_mobile NOT IN (
				SELECT
					outbound_mobile 
				FROM
					wx_user AS u
					INNER JOIN company AS c ON c.company_id = u.company_id
					INNER JOIN company_report_permission AS p ON p.company_id = u.company_id
					INNER JOIN chart_permission AS b ON b.chart_permission_id = p.chart_permission_id
					INNER JOIN company_product AS cp ON cp.company_id = u.company_id 
				WHERE
					1 = 1 
					AND cp.product_id = 2 
					AND cp.STATUS IN ( '正式', '试用' ) 
					AND outbound_mobile != '') 
            AND w.mobile = ''`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 删除数据
func DeleteWxUserWhite(item *WxUserWhite) (err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	if item.Mobile != "" {
		sql := ` DELETE FROM wx_user_white WHERE mobile = ?`
		_, err = o.Raw(sql, item.Mobile).Exec()
	} else if item.OutboundMobile != "" {
		sql := ` DELETE FROM wx_user_white WHERE outbound_mobile = ?`
		_, err = o.Raw(sql, item.OutboundMobile).Exec()
	}

	return
}