package dao

import (
	"fmt"
	"strconv"

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

type DataStaffAssessContent2 struct {
	CycleId             string                       `pg:"cycle_id"`              //周期id
	CycleName           string                       `pg:"cycle_name"`            //周期名称
	EvaluationProjectId string                       `pg:"evaluation_project_id"` //
	TargetUserId        string                       `pg:"target_user_id"`        //被评估人的id
	TargetUserName      string                       `pg:"target_user_name"`      //被评估人的名称
	BeginDay            string                       `pg:"begin_day"`             //评估的日期
	Value               string                       `pg:"value"`                 //评估填写的值
	Category            string                       `pg:"category"`              //评估项分类
	ContentName         string                       `pg:"content_name"`          //评估项名称
	Weight              float64                      `pg:"weight"`                //权重
	PromptText          string                       `pg:"prompt_text"`           //评估标准
	Remark              []domain.AssessContemtRemark `pg:"remark"`
}

// SearchStaffAssessContent2 用于员工绩效-综合管理-导出绩效-个人
// 获取所有评估的填写内容
// companyId 公司id
// cycleId 周期id
// operaterId 操作人、查看人的id
// hrbp 操作人、查看人是否hrbp 1：是;-1：否
func (d *StaffAssessDao) SearchStaffAssessContent2(companyId int, cycleId int, operaterId int, hrbp int, exportUserIds []string) (
	[]DataStaffAssessContent2, error) {
	withSql := d.catchProjectIdByPermission(companyId, cycleId, operaterId, hrbp)
	sqlStr := ` select 
	staff_assess.target_user ->>'userId' as target_user_id,
	staff_assess.evaluation_project_id,
	staff_assess.target_user ->>'userName' as target_user_name,
	to_char(staff_assess.begin_time,'YYYY-MM-DD') as begin_day,
	staff_assess.cycle_id,
	staff_assess.cycle_name,
	staff_assess_content.value ,
	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 staff_assess
join staff_assess_content  on staff_assess.id = staff_assess_content.staff_assess_id 
where 1=1 
and staff_assess.cycle_id =? and "types" ='self'
and staff_assess.evaluation_project_id in (
	select t_project_4.project_id from t_project_4
) `

	condition := []interface{}{cycleId}
	if len(exportUserIds) > 0 {
		sqlStr += ` and staff_assess.target_user->>'userId' in(?) `
		condition = append(condition, pg.In(exportUserIds))
	}

	sqlStr = withSql + sqlStr + ` order by staff_assess.begin_time `
	result := []DataStaffAssessContent2{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

type DataEvaluationItemUsed2 struct {
	CycleId             string  `pg:"cycle_id"`              //周期id
	CycleName           string  `pg:"cycle_name"`            //周期名称
	EvaluationProjectId string  `pg:"evaluation_project_id"` //评估的项目
	TargetUserId        string  `pg:"target_user_id"`        //被评估人的id
	TargetUserName      string  `pg:"target_user_name"`      //被评估人的名称
	BeginDay            string  `pg:"begin_day"`             //评估的日期
	Category            string  `pg:"category"`              //评估项分类
	ContentName         string  `pg:"content_name"`          //评估项名称
	Weight              float64 `pg:"weight"`                //权重
	PromptText          string  `pg:"prompt_text"`           //评估标准
}

// SearchEvaluationItemUsed2 用于员工绩效-综合管理-导出绩效-个人
// 获取所有评估的填写项
// companyId  公司id
// cycleId 周期id
// operaterId 操作人、查看人的id
// hrbp 操作人、查看人是否hrbp 1：是;-1：否
func (d *StaffAssessDao) SearchEvaluationItemUsed2(companyId int, cycleId int, operaterId int, hrbp int, exportUserIds []string) (
	[]DataEvaluationItemUsed2, error) {
	withSql := d.catchProjectIdByPermission(companyId, cycleId, operaterId, hrbp)
	sqlStr := `select 
	staff_assess.target_user ->>'userId' as target_user_id,
	staff_assess.evaluation_project_id ,
	staff_assess.target_user ->>'userName' as target_user_name,
	to_char(staff_assess.begin_time,'YYYY-MM-DD') as begin_day,
	staff_assess.cycle_id,
	staff_assess.cycle_name,
	evaluation_item_used.category ,
	evaluation_item_used."name" as content_name ,
	evaluation_item_used.weight,
	evaluation_item_used.prompt_text 
from staff_assess
join evaluation_item_used  on evaluation_item_used.evaluation_project_id  = staff_assess.evaluation_project_id 
where 1=1 
and staff_assess.cycle_id =? and "types" ='self'
and staff_assess.evaluation_project_id in (
	select t_project_4.project_id  from t_project_4 
)  `
	condition := []interface{}{cycleId}
	if len(exportUserIds) > 0 {
		sqlStr += ` and staff_assess.target_user->>'userId' in(?) `
		condition = append(condition, pg.In(exportUserIds))
	}
	sqlStr = withSql + sqlStr + " order by evaluation_item_used.sort_by "
	result := []DataEvaluationItemUsed2{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

// sql 语句片段
// 按照人员可查看权限的过滤
// companyId int 公司id (必填)
// cycleId int, 评估周期id (必填)
// userId int, 用户id,谁要查看数据 (必填)
// hrbp 是否搜索HRBP角色的用户可以查看，1：是;-1：否 (必填)
func (d *StaffAssessDao) catchProjectIdByPermission(companyId int, cycleId int, operaterId int, hrbp int) string {
	withSql := `
	set time zone 'PRC';
	with 
	t_project_0 as(
		select evaluation_project.id as project_id,
		jsonb_array_elements_text(evaluation_project.recipients) as target_user_id,
		evaluation_project.pmp,
		evaluation_project.pmp_ids,
		evaluation_project.hr_bp
		from evaluation_project
		where evaluation_project.cycle_id =%d 
		and evaluation_project.deleted_at isnull
	),
	-- 查我的下级员工
	t_user_1 as (
		select  "user".id::text  as user_id  from "user" 
		where "user".parent_id =%d and company_id=%d
	),
	-- 如果是HRBP
	t_project_1 as(
		select t_project_0.project_id,t_project_0.target_user_id
		from t_project_0 
		where t_project_0.hr_bp =%d
	),
	-- 如果的项目管理员
	t_project_2 as(
		select t_project_0.project_id,t_project_0.target_user_id
		from t_project_0 
		where t_project_0.pmp =1
		and t_project_0.pmp_ids @>'["%d"]'
	),
	-- 如果是上级员工
	t_project_3 as (
		select t_project_0.project_id,t_project_0.target_user_id
		from t_project_0 
		join t_user_1 on t_user_1.user_id = t_project_0.target_user_id
	),
	-- 合并数据
	t_project_4 as (
		select t_project_2.project_id,t_project_2.target_user_id from t_project_2
		union 
		select t_project_1.project_id,t_project_1.target_user_id from t_project_1
		union
		select t_project_3.project_id,t_project_3.target_user_id from t_project_3
	)`
	params := []interface{}{cycleId, operaterId, companyId, hrbp, operaterId}

	return fmt.Sprintf(withSql, params...)
}

type TargetUserCycleProject struct {
	CycleId               string `pg:"cycle_id" json:"cycleId"`     //周期id
	CycleName             string `pg:"cycle_name" json:"cycleName"` //周期名称
	EvaluationProjectId   string `pg:"evaluation_project_id" json:"evaluationProjectId"`
	EvaluationProjectName string `pg:"evaluation_project_name" json:"evaluationProjectName"`
	TargetUserId          string `pg:"target_user_id" json:"targetUserId"`
}

// 获取目标员工的自评周期和项目
func (d *StaffAssessDao) SearchTargetUserCycleProject(companyId int, targetUserId int, limit int, offset int) ([]TargetUserCycleProject, error) {
	sqlStr := `select distinct 
	staff_assess.cycle_name,
	staff_assess.cycle_id ,
	staff_assess.evaluation_project_id,
	staff_assess.target_user ->>'userId' as target_user_id,
	staff_assess.evaluation_project_name 
	from staff_assess ,staff_assess_task
	where 1=1
	and staff_assess_task.id =staff_assess.staff_assess_task_id 
	and staff_assess."types" ='self'
	and staff_assess_task.deleted_at isnull 
	and staff_assess.deleted_at isnull 
	and staff_assess.end_time <=now()
	and staff_assess.target_user ->>'userId'='?'
	and staff_assess.company_id=?
	`
	sqlStr += ` order by  cycle_id  desc limit ? offset ? `
	condition := []interface{}{targetUserId, companyId, limit, offset}
	result := []TargetUserCycleProject{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

type SearchConditin2 struct {
	CompanyId      int      //公司id
	CycleId        int      //周期id
	BeginDayList   []string //评估的日期
	TargetUserName string   //被评估人的名称
	TargetUserId   []string //查询指定的人
	OperaterId     int      //用户的id是谁在搜索数据
	Hrbp           int      //
}

// 项目管理-成员列表  导出数据
func (d *StaffAssessDao) ExportDataUserAssessV2(param SearchConditin2) ([]ExportData1, error) {
	withSql := d.catchProjectIdByPermission(param.CompanyId, param.CycleId, param.OperaterId, param.Hrbp)
	sqlStr := `select 
	staff_assess.target_user->>'userId' as target_user_id ,
	staff_assess.target_user->>'userName' as target_user_name ,
	to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD') as begin_day,
	staff_assess.id as assess_id,
	staff_assess.cycle_id,
	staff_assess.cycle_name,
	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 staff_assess
join t_project_4 on staff_assess.evaluation_project_id =t_project_4.project_id 
left join staff_assess_content  on staff_assess.id = staff_assess_content.staff_assess_id 
where 1=1 
and staff_assess."types" ='self'
`
	condition := []interface{}{}
	if len(param.TargetUserName) > 0 {
		condition = append(condition, "%"+param.TargetUserName+"%")
		sqlStr += ` and staff_assess.target_user->>'userName' like ? `
	}
	if len(param.BeginDayList) > 0 {
		condition = append(condition, pg.In(param.BeginDayList))
		sqlStr += ` and to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD') in(?) `
	}
	if len(param.TargetUserId) > 0 {
		condition = append(condition, pg.In(param.TargetUserId))
		sqlStr += ` and staff_assess.target_user->>'userId' in (?) `
	}

	sqlStr = withSql + sqlStr + ` order by convert_to(staff_assess.target_user->>'userName','GBK'),begin_day,staff_assess_content.sort_by `
	tx := d.transactionContext.PgTx
	var result []ExportData1
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

type ExecutorUser struct {
	UserId   string `pg:"user_id"`
	UserName string `pg:"user_name"`
}

// 获取没有进行过360邀请的员工
func (d *StaffAssessDao) ListTargetUserNoInvite(companyId int, cycleId int, beginDay string) ([]ExecutorUser, error) {
	sqlStr := `with t1 as (
		select staff_assess.target_user ->>'userId' as user_id,
		staff_assess.target_user ->>'userName' as user_name
		from  staff_assess 
		where 1=1 
		and cycle_id =?
		and company_id =?
		and to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD') =?
		and "types" in('invite_same_super','invite_diff_super')
		
	)
	select staff_assess.target_user ->>'userId' as user_id,
	staff_assess.target_user ->>'userName' as user_name
	from  staff_assess 
	left join  t1 on staff_assess.target_user ->>'userId'=t1.user_id
	where 1=1 
	and staff_assess.cycle_id =?
	and company_id = ?
	and to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD') =?
	and staff_assess."types"='self'
	and t1.user_id isnull 
	`
	condition := []interface{}{cycleId, companyId, beginDay, cycleId, companyId, beginDay}
	tx := d.transactionContext.PgTx
	var result []ExecutorUser
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

type UserSelfStaffAssess struct {
	AssessId              string `pg:"assess_id"`
	EvaluationProjectName string `pg:"evaluation_project_name"` //项目名称
	CompanyId             string `pg:"company_id"`              //公司id
	ContentId             int    `pg:"content_id"`              //
	TargetUserId          string `pg:"target_user_id"`          //被评估人的id
	TargetUserName        string `pg:"target_user_name"`        //被评估人的名称
	BeginDay              string `pg:"begin_day"`               //评估的日期
	EvaluationProjectId   string `pg:"evaluation_project_id"`   //项目id
	CycleId               string `pg:"cycle_id"`                //周期id
}

type SearchConditin3 struct {
	CompanyId      int    //公司id
	CycleId        int    //周期id
	BeginDay       string //评估的日期
	TargetUserName string //被评估人的名称
	Limit          int    //分页
	Offset         int    //分页
	OperaterId     int    //用户的id是谁在搜索数据
	Hrbp           int    //
	Status         string // 评估完成状态
}

// 根据周期的id和日期获取员工评估的评估任务。有进行查看权限过滤
// companyId int 公司id ,必填
// cycleId int, 评估周期id, 必填
// userId int, 用户id,谁要查看数据
// beginDay string, 周期中执行项目的时间
// hrbp 是否搜索HRBP角色的用户可以查看，1：是;-1：否
// limit int, 分页条数
// offset int 分页偏移
func (d *StaffAssessDao) SearchUserAssessContentV2(param SearchConditin3) ([]UserAssessContent, error) {
	withSql := d.catchProjectIdByPermission(param.CompanyId, param.CycleId, param.OperaterId, param.Hrbp)
	sqlStr := `select
	staff_assess.cycle_id ,
	staff_assess.id as assess_id,
	staff_assess.target_user ->>'userId' as target_user_id,
	staff_assess.target_user ->>'userName' as target_user_name,
	staff_assess.evaluation_project_id ,
	staff_assess.evaluation_project_name ,
	staff_assess.company_id ,
	to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD') as begin_day
	from staff_assess 
	join t_project_4 on staff_assess.evaluation_project_id =t_project_4.project_id and staff_assess.target_user ->>'userId' = t_project_4.target_user_id
	where 1=1 
	and staff_assess.cycle_id =?
	and staff_assess.deleted_at isnull 
	and staff_assess.types='self'
	`
	condition := []interface{}{param.CycleId}
	if len(param.BeginDay) > 0 {
		condition = append(condition, param.BeginDay)
		sqlStr += ` and to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD')=? `
	}
	if len(param.TargetUserName) > 0 {
		condition = append(condition, "%"+param.TargetUserName+"%")
		sqlStr += ` and staff_assess.target_user ->>'userName' like ? `
	}
	if len(param.Status) > 0 {
		condition = append(condition, param.Status)
		sqlStr += ` and staff_assess.status=? `
	}
	condition = append(condition, param.Limit, param.Offset)
	sqlStr += ` order by convert_to(staff_assess.target_user ->>'userName','GBK') limit ? offset ? `

	withSql2 := withSql + `,t_staff_assess_1 as( ` + sqlStr + ` )`
	sqlStr2 := ` ,t_item_used as (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,
		t_staff_assess_1.cycle_id,
		 evaluation_item_used.sort_by ,
		evaluation_item_used.category ,
		evaluation_item_used."name" as content_name ,
		evaluation_item_used.weight,
		evaluation_item_used."rule" 
		from t_staff_assess_1
		join evaluation_item_used on t_staff_assess_1.evaluation_project_id =evaluation_item_used.evaluation_project_id 
	 )
	 select t_item_used.* ,staff_assess_content.value,staff_assess_content.level_value
	 from t_item_used
	 left join staff_assess_content on staff_assess_content.staff_assess_id=t_item_used.assess_id
	 and t_item_used.category =staff_assess_content.category and t_item_used.content_name = staff_assess_content."name" 
	 where 1=1`
	sqlStr2 = withSql2 + sqlStr2
	tx := d.transactionContext.PgTx
	var result []UserAssessContent
	_, err := tx.Query(&result, sqlStr2, condition...)
	return result, err
}

// 根据周期的id和日期获取员工评估的评估任务数量。有进行查看权限过滤
// companyId int 公司id
// cycleId int, 评估周期id
// userId int, 用户id,谁要查看数据
// beginDay string, 周期中执行项目的时间
// hrbp 是否搜索HRBP角色的用户可以查看，1：是;-1：否
func (d *StaffAssessDao) CountUserSelfStaffAssess(param SearchConditin3) (int, error) {
	withSql := d.catchProjectIdByPermission(param.CompanyId, param.CycleId, param.OperaterId, param.Hrbp)
	sqlStr := `select 
	count(*)
	from staff_assess 
	join t_project_4 on staff_assess.evaluation_project_id =t_project_4.project_id and staff_assess.target_user ->>'userId' = t_project_4.target_user_id
	where 1=1 
	and staff_assess.cycle_id =?
	and staff_assess.deleted_at isnull 
	and staff_assess.types='self'
	`
	condition := []interface{}{param.CycleId}
	if len(param.BeginDay) > 0 {
		condition = append(condition, param.BeginDay)
		sqlStr += ` and to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD')=? `
	}
	if len(param.TargetUserName) > 0 {
		condition = append(condition, "%"+param.TargetUserName+"%")
		sqlStr += ` and staff_assess.target_user ->>'userName' like ? `
	}
	if len(param.Status) > 0 {
		condition = append(condition, param.Status)
		sqlStr += ` and staff_assess.status=? `
	}

	sqlStr2 := withSql + sqlStr
	tx := d.transactionContext.PgTx
	var result int
	_, err := tx.Query(pg.Scan(&result), sqlStr2, condition...)
	return result, err
}

type CycleAndProjectItem struct {
	CycleId               string `pg:"cycle_id"`
	CycleName             string `pg:"cycle_name"`
	EvaluationProjectId   string `pg:"evaluation_project_id"`
	EvaluationProjectName string `pg:"evaluation_project_name"`
	TargetUserId          string `pg:"target_user_id"`
}

// 获取某个人的自评的周期和项目
func (d *StaffAssessDao) ListCycleAndProject(userId int, companyId int, cycleId int, offset int, limit int) (result []CycleAndProjectItem, err error) {
	sqlStr := `select 
	distinct 
	staff_assess.cycle_id ,
	staff_assess.cycle_name ,
	staff_assess.evaluation_project_id ,
	staff_assess.evaluation_project_name ,
	target_user->>'userId' as target_user_id
	from staff_assess
	join evaluation_cycle on staff_assess.cycle_id =evaluation_cycle.id 
	where 1=1
	and staff_assess.company_id=?
	and staff_assess.target_user->>'userId'=?
	and staff_assess."types"='self'
    and staff_assess.deleted_at isnull 
	`

	condition := []interface{}{companyId, strconv.Itoa(userId)}
	if cycleId > 0 {
		condition = append(condition, cycleId)
		sqlStr += ` and staff_assess.cycle_id=? `
	}
	condition = append(condition, limit, offset)
	sqlStr += ` order by staff_assess.cycle_id desc limit ? offset ? `

	tx := d.transactionContext.PgTx
	_, err = tx.Query(&result, sqlStr, condition...)
	return result, err
}