package dao

import (
	"fmt"
	"strconv"

	"github.com/go-pg/pg/v10"
	pgTransaction "github.com/linmadan/egglib-go/transaction/pg"
	"gitlab.fjmaimaimai.com/allied-creation/performance/pkg/domain"
)

type StaffAssessDao struct {
	transactionContext *pgTransaction.TransactionContext
}

func NewStaffAssessDao(options map[string]interface{}) *StaffAssessDao {
	var transactionContext *pgTransaction.TransactionContext
	if value, ok := options["transactionContext"]; ok {
		transactionContext = value.(*pgTransaction.TransactionContext)
	}
	return &StaffAssessDao{
		transactionContext: transactionContext,
	}
}

type CountData struct {
	TargetUserId    int
	InviteTotal     int
	InviteCompleted int
}

// 获取员工邀请的人完成360评估的数量
func (d *StaffAssessDao) CountInviteAssessByTargetUser(userIds []int, assessTaskId int) ([]CountData, error) {
	sqlStr := `SELECT staff_assess.target_user->>'userId' as target_user_id,
 count(staff_assess."id") AS invite_total,
 sum(
	case WHEN staff_assess.status='completed' THEN 1
	ELSE 0 END
 ) as invite_completed
 FROM staff_assess 
 WHERE staff_assess.target_user->>'userId' IN(?)
 AND staff_assess.types IN ('invite_same_super','invite_diff_super')
 AND staff_assess.staff_assess_task_id = ?
 GROUP BY target_user_id`

	userIdList := []string{}
	for _, v := range userIds {
		uid := strconv.Itoa(v)
		userIdList = append(userIdList, uid)
	}
	condition := []interface{}{
		pg.In(userIdList), assessTaskId,
	}
	tx := d.transactionContext.PgTx
	result := []CountData{}
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

func (d *StaffAssessDao) CountTargetUserInviteAssess1(userIds []int, cycleId int, beginDay string) ([]CountData, error) {
	sqlStr := `SELECT 
	staff_assess.target_user->>'userId' as target_user_id,
	count(staff_assess."id") AS invite_total,
	sum(
	   case WHEN staff_assess.status='completed' THEN 1
	   ELSE 0 END
	) as invite_completed
	FROM staff_assess 
	WHERE staff_assess.target_user->>'userId' IN(?)
	AND staff_assess.types IN ('invite_same_super','invite_diff_super')
	and to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD') = ?
	and staff_assess.cycle_id =?
	GROUP BY target_user_id`
	userIdStr := []string{}
	for _, v := range userIds {
		str := strconv.Itoa(v)
		userIdStr = append(userIdStr, str)
	}
	condition := []interface{}{
		pg.In(userIdStr), beginDay, cycleId,
	}
	tx := d.transactionContext.PgTx
	result := []CountData{}
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

// 根据评估的人执行人id，搜索 executorId参与的评估任务
func (d *StaffAssessDao) SearchAssessTaskMe(executorId int, companyId int, limit int, offset int) ([]*domain.StaffAssessTask, error) {

	if limit < 0 {
		limit = 20
	}
	if offset < 0 {
		offset = 0
	}

	sqlStr := `SELECT DISTINCT staff_assess_task.* FROM staff_assess_task 
	JOIN staff_assess ON staff_assess_task."id" = staff_assess."staff_assess_task_id"
	WHERE staff_assess.company_id=?
	and staff_assess_task.deleted_at isnull
	and staff_assess.executor->>'userId'='?'
	order by staff_assess_task.id desc 
	limit ? offset ?
	`
	tx := d.transactionContext.PgTx
	condition := []interface{}{
		companyId, executorId, limit, offset,
	}
	result := []*domain.StaffAssessTask{}
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err

}

// 搜索 executorId 参与的评估任务
func (d *StaffAssessDao) CountAssessTaskMe(executorId int, companyId int) (int, error) {
	sqlStr := `SELECT count( DISTINCT staff_assess_task."id") FROM staff_assess_task 
	JOIN staff_assess ON staff_assess_task."id" = staff_assess."staff_assess_task_id"
	WHERE staff_assess.company_id=?
	and staff_assess_task.deleted_at isnull
	and staff_assess.executor->>'userId'='?'
	`
	tx := d.transactionContext.PgTx
	condition := []interface{}{
		companyId, executorId,
	}
	result := 0
	_, err := tx.QueryOne(pg.Scan(&result), sqlStr, condition...)
	return result, err
}

//获取所以已经执行的评估周期
type AssessCycle struct {
	CycleId   string `json:"cycleId"` //周期id
	CompanyId string `json:"companyId"`
	CycleName string `json:"cycleName"` //周期名称
}

//获取所以已经执行的评估周期
func (d *StaffAssessDao) AllAssessCycleList(companyId int) ([]AssessCycle, error) {
	sqlStr := `select  
	distinct 
	staff_assess_task.cycle_id ,
	staff_assess_task.company_id ,
	staff_assess_task.cycle_name 
	from  staff_assess_task 
	where  staff_assess_task.company_id = ?`

	tx := d.transactionContext.PgTx
	condition := []interface{}{
		companyId,
	}
	result := []AssessCycle{}
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

//获取评估周期中的绩效考核日期
type AssessCycleDay struct {
	BeginDay  string `json:"beginDay"`
	CycleId   int    `json:"cycleId"`
	CycleName string `json:"cycleName"`
	CompanyId string `json:"companyId"`
}

//获取评估周期中的绩效考核日期
func (d *StaffAssessDao) AllAssessCycleDayList(companyId int, cycleId int) ([]AssessCycleDay, error) {
	sqlStr := `select distinct staff_assess_task.begin_day ,
	staff_assess_task.cycle_id ,
	staff_assess_task.cycle_name,
	staff_assess_task.company_id 
	from staff_assess_task 
	where staff_assess_task.cycle_id = ?
	and company_id =? `

	tx := d.transactionContext.PgTx
	condition := []interface{}{
		cycleId, companyId,
	}
	result := []AssessCycleDay{}
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

//获取员工填写评估内容
type UserAssessContent struct {
	AssessId            string `json:"assessId"`
	ContentId           int    `json:"contentId"`
	TargetUserId        string `json:"targetUserId"`        //被评估人的id
	TargetUserName      string `json:"targetUserName"`      //被评估人的名称
	BeginDay            string `json:"beginDay"`            //评估的日期
	EvaluationProjectId string `json:"evaluationProjectId"` //项目id
	Value               string `json:"value"`               //评估填写的值
	SortBy              int    `json:"sortBy"`              //评估项顺序
	Category            string `json:"category"`            //评估项分类
	ContentName         string `json:"contentName"`         //评估项名称
	Weight              int    `json:"weight"`              //权重
}

type SearchConditin1 struct {
	CompanyId      int      //公司id
	AssessId       int      //评估任务id
	CycleId        int      //周期id
	BeginDay       string   //评估的日期
	TargetUserName string   //被评估人的名称
	TargetUserId   []string //查询指定的人
	Limit          int      //分页
	Offset         int      //分页
	OperaterId     int      //用户的id是谁在搜索数据
	Hrbp           int      //
}

//项目管理-成员列表 根据周期的id和日期获取员工填写的评估内容
//companyId int 公司id
//cycleId int, 评估周期id
//userId int, 用户id,谁要查看数据
//beginDay string, 周期中执行项目的时间
//hrbp 是否搜索HRBP角色的用户可以查看，1：是;-1：否
//limit int, 分页条数
//offset int 分页偏移
func (d *StaffAssessDao) SearchUserAssessContent(param SearchConditin1) ([]UserAssessContent, error) {
	if param.Offset < 0 {
		param.Offset = 0
	}
	if param.Limit < 0 {
		param.Limit = 20
	}
	sqlStr := ` select 
	t_staff_assess_1.target_user_id,t_staff_assess_1.target_user_name,t_staff_assess_1.begin_day,
	t_staff_assess_1.assess_id,staff_assess_content.id as content_id,
	staff_assess_content.value ,staff_assess_content.sort_by ,
	staff_assess_content.category ,staff_assess_content."name" as content_name ,
	staff_assess_content.weight
	from t_staff_assess_1
	left join staff_assess_content  on t_staff_assess_1.assess_id = staff_assess_content.staff_assess_id 
	where 1=1
	`
	condition := []interface{}{}
	if len(param.TargetUserName) > 0 {
		sqlStr += ` and t_staff_assess_1.target_user_name like ? `
		condition = append(condition, "%"+param.TargetUserName+"%")
	}
	//加入排序
	sqlStr += ` order by convert_to(t_staff_assess_1.target_user_name,'GBK'),staff_assess_content.sort_by `
	//获取前置sql语句
	sqlStr0 := d.useTStaffAssess(param.CompanyId, param.CycleId, param.OperaterId, param.BeginDay, param.Hrbp, param.Limit, param.Offset)
	sqlStr = sqlStr0 + sqlStr
	tx := d.transactionContext.PgTx
	result := []UserAssessContent{}
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

//项目管理-成员列表  根据周期的id和日期获取员工填写的评估内容,数量统计
//companyId int 公司id
//cycleId int, 评估周期id
//userId int, 用户id,谁要查看数据
//beginDay string, 周期中执行项目的时间
//hrbp 是否搜索HRBP角色的用户可以查看，1：是;-1：否
//limit int, 分页条数
//offset int 分页偏移
func (d *StaffAssessDao) CountUserAssess(param SearchConditin1) (int, error) {
	sqlStr := ` select 
	count( distinct t_staff_assess_1.target_user_id) cnt
	from t_staff_assess_1
	where 1=1 `
	condition := []interface{}{}
	if len(param.TargetUserName) > 0 {
		sqlStr += ` and t_staff_assess_1.target_user_name like ? `
		condition = append(condition, "%"+param.TargetUserName+"%")
	}
	//获取前置sql语句
	sqlStr0 := d.useTStaffAssess(param.CompanyId, param.CycleId, param.OperaterId, param.BeginDay, param.Hrbp, 5000, 0)
	sqlStr = sqlStr0 + sqlStr
	tx := d.transactionContext.PgTx
	var result int
	_, err := tx.QueryOne(pg.Scan(&result), sqlStr, condition...)
	return result, err
}

//生成的sql 根据用户的查看权限 ,获取可查看的评估任务，
//companyId int 公司id
//cycleId int, 评估周期id
//userId int, 用户id,谁要查看数据
//beginDay string, 周期中执行项目的时间
//hrbp 是否搜索HRBP角色的用户可以查看，1：是;-1：否
//limit int, 分页条数
//offset int 分页偏移
func (d *StaffAssessDao) useTStaffAssess(companyId int, cycleId int, userId int, beginDay string, hrbp int, limit int, offset int) string {
	sqlstr := `
	set time zone 'PRC';
	with t_user_department as (
		select "user".id as user_id ,jsonb_array_elements_text ("user".department_id)  as depart_id from "user" 
		where "user".company_id= %d
	),
	t_department as (
		select  department.id::text  as depart_id  from  department where charge_user_ids @>'[%d]'
	),
	t_user_1 as (
		select t_user_department.user_id::text from t_user_department
		join t_department on t_user_department.depart_id = t_department.depart_id
	),
	t_project_1 as(
		select evaluation_project.id as project_id
		from evaluation_project 
		where evaluation_project.cycle_id =%d
		and evaluation_project.hr_bp = %d
	),
	t_project_2 as(
		select evaluation_project.id as project_id
		from evaluation_project 
		where evaluation_project.cycle_id =%d
		and evaluation_project.pmp =1
		and evaluation_project.pmp_ids @>'["%d"]'
	),
	t_project_3 as (
		select t_project_2.project_id from t_project_2
		union 
		select t_project_1.project_id from t_project_1
	),
	t_staff_assess_0 as (
		select staff_assess.id as assess_id,
		staff_assess.target_user->>'userId' as target_user_id,
		staff_assess.target_user->>'userName' as target_user_name,
		to_char(staff_assess.begin_time,'YYYY-MM-DD') as begin_day,
		staff_assess.evaluation_project_id
		from staff_assess
		where staff_assess.cycle_id = %d
		and to_char(staff_assess.begin_time,'YYYY-MM-DD')='%s'
		and staff_assess."types" ='self'
	),
	t_staff_assess_1 as (
		(select t_staff_assess_0.assess_id,
		t_staff_assess_0.target_user_id,
		t_staff_assess_0.target_user_name,
		t_staff_assess_0.begin_day
		from t_staff_assess_0
		join t_project_3  on t_staff_assess_0.evaluation_project_id = t_project_3.project_id
		) union (select t_staff_assess_0.assess_id,
		t_staff_assess_0.target_user_id,
		t_staff_assess_0.target_user_name,
		t_staff_assess_0.begin_day
		from t_staff_assess_0
		join t_user_1 on t_staff_assess_0.target_user_id=t_user_1.user_id
		)
		limit %d offset %d
	)
	`
	params := []interface{}{
		companyId, userId, cycleId, hrbp, cycleId, userId, cycleId, beginDay, limit, offset,
	}

	sqlstr = fmt.Sprintf(sqlstr, params...)
	return sqlstr
}

type ExportData1 struct {
	AssessId            string
	ContentId           int
	TargetUserId        string //被评估人的id
	TargetUserName      string //被评估人的名称
	BeginDay            string //评估的日期
	EvaluationProjectId string //项目id
	Value               string //评估填写的值
	SortBy              int    //评估项顺序
	Category            string //评估项分类
	ContentName         string //评估项名称
	Weight              int    //权重
	PromptText          string //评估标准
	Remark              []domain.AssessContemtRemark
}

//项目管理-成员列表  导出数据
func (d *StaffAssessDao) ExportDataUserAssess(param SearchConditin1) ([]ExportData1, error) {
	if param.Offset < 0 {
		param.Offset = 0
	}
	if param.Limit < 0 {
		param.Limit = 5000
	}
	sqlStr := ` select 
	t_staff_assess_1.target_user_id,t_staff_assess_1.target_user_name,t_staff_assess_1.begin_day,
	t_staff_assess_1.assess_id,staff_assess_content.id as content_id,
	staff_assess_content.value ,staff_assess_content.sort_by ,
	staff_assess_content.category ,staff_assess_content."name" as content_name ,
	staff_assess_content.weight,staff_assess_content.prompt_text ,staff_assess_content.remark 
	from t_staff_assess_1
	left join staff_assess_content  on t_staff_assess_1.assess_id = staff_assess_content.staff_assess_id 
	where 1=1 
	`
	condition := []interface{}{}
	if len(param.TargetUserName) > 0 {
		sqlStr += ` and t_staff_assess_1.target_user_name like ? `
		condition = append(condition, "%"+param.TargetUserName+"%")
	}
	if len(param.TargetUserId) > 0 {
		sqlStr += ` and t_staff_assess_1.target_user_id in (?) `
		condition = append(condition, pg.In(param.TargetUserId))
	}
	//加入排序
	sqlStr += ` order by convert_to(t_staff_assess_1.target_user_name,'GBK'),staff_assess_content.sort_by `
	//获取前置sql语句
	sqlStr0 := d.useTStaffAssess(param.CompanyId, param.CycleId, param.OperaterId, param.BeginDay, param.Hrbp, param.Limit, param.Offset)
	sqlStr = sqlStr0 + sqlStr
	tx := d.transactionContext.PgTx
	var result []ExportData1
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

type AssessCycleDayMe struct {
	BeginDay  string `json:"beginDay"`
	CycleId   int    `json:"cycleId"`
	CycleName string `json:"cycleName"`
}

// 根据评估的人执行人id，搜索 executorId参与的评估周期
func (d *StaffAssessDao) SearchAssessCycleMe(executorId int, companyId int, limit int, offset int) ([]AssessCycleDayMe, error) {
	if limit < 0 {
		limit = 20
	}
	if offset < 0 {
		offset = 0
	}
	sqlStr := `
	SELECT 
	staff_assess_task.cycle_id ,staff_assess_task.cycle_name ,staff_assess_task.begin_day
	FROM staff_assess_task 
	JOIN staff_assess ON staff_assess_task."id" = staff_assess."staff_assess_task_id"
	WHERE staff_assess.company_id=?
	and staff_assess_task.deleted_at isnull
	and staff_assess.executor->>'userId'='?'
	group by staff_assess_task.cycle_id ,staff_assess_task.cycle_name ,staff_assess_task.begin_day
	order by staff_assess_task.begin_day desc 
	limit ? offset ?
	`
	tx := d.transactionContext.PgTx
	condition := []interface{}{
		companyId, executorId, limit, offset,
	}
	result := []AssessCycleDayMe{}
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

// 根据评估的人执行人id，统计executorId参与的评估周期
func (d *StaffAssessDao) CountAssessCycleMe(executorId int, companyId int) (int, error) {
	sqlStr := `
	select count(DISTINCT (staff_assess_task.cycle_id,staff_assess_task.begin_day )) as cnt
	FROM staff_assess_task 
	JOIN staff_assess ON staff_assess_task."id" = staff_assess."staff_assess_task_id"
	WHERE staff_assess.company_id=?
	and staff_assess_task.deleted_at isnull
	and staff_assess.executor->>'userId'='?'
	`
	tx := d.transactionContext.PgTx
	condition := []interface{}{
		companyId, executorId,
	}
	var result int
	_, err := tx.QueryOne(pg.Scan(&result), sqlStr, condition...)
	return result, err
}