package dao

import (
	"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
}

// 根据评估的人执行人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.QueryOne(&result, sqlStr, condition...)
	return result, err
}

//获取评估周期中的绩效考核日期
type AssessCycleDay struct {
	BeginDay  string `json:"beginDay"`
	CycleId   string `json:"cycleId"`
	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.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.QueryOne(&result, sqlStr, condition...)
	return result, err
}

//根据周期的id和日期获取员工填写的评估内容

/**
with assess_list as(
select
 staff_assess.id  as staff_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,
 to_char( staff_assess.begin_time,'YYYY-MM-DD') as begin_day
from  staff_assess
where staff_assess.cycle_id = 1594521861704650752
and  to_char( staff_assess.begin_time,'YYYY-MM-DD') ='2022-11-22'
and staff_assess."types" ='self'
limit 5 offset  0
)
select
assess_list.target_user_id,
assess_list.target_user_name,
assess_list.begin_day,
assess_list.evaluation_project_id,
staff_assess_content.value ,
staff_assess_content.sort_by ,
staff_assess_content.category ,
staff_assess_content."name" ,
staff_assess_content.weight
from staff_assess_content
right join assess_list on assess_list.staff_assess_id = staff_assess_content.staff_assess_id

**/

//获取员工填写评估内容
type UserAssessContent struct {
	TargetUserId        string //被评估人的id
	TargetUserName      string //被评估人的名称
	BeginDay            string //评估的日期
	EvaluationProjectId string //项目id
	Value               string //评估填写的值
	SortBy              int    //评估项顺序
	Category            string //分类
	Name                string //名称
	Weight              int    //权重
}

type SearchConditin1 struct {
	CycleId      int    //周期id
	BeginDay     string //评估的日期
	UserName     string //被评估人的名称
	Limit        int    //分页
	Offset       int    //分页
	DepartmentId []int  //
	OperaterId   int    //用户的id是谁在搜索数据
	IsHrBp       bool   //
}

//根据周期的id和日期获取员工填写的评估内容
//cycleId 周期id
//beginDay 评估任务的生成日期
//userName 被评估的员工名称，
// limit offset  分页搜索
func (d *StaffAssessDao) SearchUserAssessContent(param SearchConditin1) ([]UserAssessContent, error) {
	sqlStr := `with assess_list as(
		select
		 staff_assess.id  as staff_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,
		 to_char( staff_assess.begin_time,'YYYY-MM-DD') as begin_day
		from  staff_assess
		where staff_assess.cycle_id = ?
		and  to_char( staff_assess.begin_time,'YYYY-MM-DD') ='?'
		and staff_assess."types" ='self'
		and staff_assess.target_user ->>'userName' like '?'
		limit ? offset ?
		)
		select
		assess_list.target_user_id,
		assess_list.target_user_name,
		assess_list.begin_day,
		assess_list.evaluation_project_id,
		staff_assess_content.value ,
		staff_assess_content.sort_by ,
		staff_assess_content.category ,
		staff_assess_content."name" ,
		staff_assess_content.weight
		from staff_assess_content
		right join assess_list on assess_list.staff_assess_id = staff_assess_content.staff_assess_id 
		order by convert_to(assess_list.target_user_name,'GBK') `

	tx := d.transactionContext.PgTx
	condition := []interface{}{
		param.CycleId, param.BeginDay, "%" + param.UserName + "%", param.Limit, param.Offset,
	}
	var result []UserAssessContent
	_, err := tx.QueryOne(&result, sqlStr, condition...)
	return result, err
}

// 根据用户的查看权限 合并用户
// 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= 526
// ),
// t_department as (
// 	select  department.id::text  as depart_id  from  department where charge_user_ids @>'[3330208201102336]'
// ),
// -- 如果是部门管理员 获取用户列表
// t_user_1 as (
// 	select t_user_department.user_id from t_user_department
// 	join t_department on t_user_department.depart_id = t_department.depart_id
// ),
// -- 如果是hrbp
// t_user_2 as(
// 	select jsonb_array_elements_text (evaluation_project.recipients) as user_id
// 	from evaluation_project
// 	where evaluation_project.cycle_id =1592787960795762688
// 	and evaluation_project.hr_bp = 1
// ),
// -- 如果是项目管理员
// t_user_3 as(
// 	select jsonb_array_elements_text (evaluation_project.recipients) as user_id
// 	from evaluation_project
// 	where evaluation_project.cycle_id =1592787960795762688
// 	and evaluation_project.pmp =1
// 	and evaluation_project.pmp_ids @>'[3330208201102336]'
// ),
// -- 合并用户
// t_user_all as(
// 	select t_user_1.user_id::text  from t_user_1
// 	union
// 	select t_user_2.user_id::text  from t_user_2
// 	union
// 	select t_user_3.user_id::text  from t_user_3
// ),
// -- 根据用户提取评估列表
// t_staff_assess as(
// 	select staff_assess.id as assess_id,
// 	staff_assess.target_user->>'userId' as user_id,
// 	staff_assess.target_user->>'userName' as user_name
// 	from staff_assess
// 	join t_user_all on staff_assess.target_user->>'userId'= t_user_all.user_id
// 	where staff_assess.cycle_id = 1592787960795762688
// 	and to_char(staff_assess.begin_time,'YYYY-MM-DD')='2022-11-24'
// 	and staff_assess."types" ='self'
// 	limit 1 offset  0
// )