package dao

import (
	"fmt"
	"time"

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

type TaskDao struct {
	transactionContext *pgTransaction.TransactionContext
}

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

func (d *TaskDao) catchTaskIdByPermission(userId int) string {
	sqlStr := `with
	-- 人员自身以及全下级
	recursive t_user as (
		(
		select "user".id,"user".parent_id 
		from "user" 
		where "user".id=%d
		)
		union
		(
		select "child_user".id,"child_user".parent_id 
		from "user" as "child_user"
		join t_user as "parent_user" on "parent_user".id="child_user".parent_id 
		where "child_user".deleted_at isnull
		)
	),
	-- 根据任务负责人和相关人员查询
	t_task_0 as (
		(select task.id from task 
		join t_user on task.leader ->>'id'=t_user.id::text 
		where task.deleted_at isnull
		)
		union
		(select task.id from task where task.related_user@>'[%d]' and task.deleted_at isnull)
	),
	t_task_ignore as (
		select * from  task_ignore where task_ignore.user_id =%d
	),
	-- 过滤取消关注的
	t_task_1 as (
		select t_task_0.id
		from  t_task_0
		left join t_task_ignore on t_task_0.id=t_task_ignore.task_id 
		where  t_task_ignore.id isnull 
	)`
	return fmt.Sprintf(sqlStr, userId, userId, userId)
}

type ListTaskCondition struct {
	Limit      int  //分页
	Offset     int  //分页
	CompanyId  int  //公司id
	UserId     int  //谁要查看任务数据
	OnlyMy     bool //只查看我负责的任务
	SearchWord string
}

// 任务和里程碑列表
type ListTaskStage struct {
	TaskId          int       `pg:"task_id"`
	TaskName        string    `pg:"task_name"`
	TaskStatus      int       `pg:"task_status"`
	TaskAliasName   string    `pg:"task_alias_name"`
	LeaderName      string    `pg:"leader_name"`
	LeaderId        string    `pg:"leader_id"`
	LevelName       string    `pg:"level_name"`
	Level           int       `pg:"level"`
	Anomaly         int       `pg:"anomaly"`
	WarnFlag        int       `pg:"warn_flag"`
	UpdatedAt       time.Time `pg:"updated_at"`
	CreatedAt       time.Time `pg:"created_at"`
	StageId         int       `pg:"stage_id"`
	StageName       string    `pg:"stage_name"`
	StageSortBy     int       `pg:"stage_sort_by"`
	PlanCompletedAt int       `pg:"plan_completed_at"`
	RealCompletedAt int       `pg:"real_completed_at"`
}

// 获取任务总数，用于页面展示; 有过滤查看权限
func (d *TaskDao) CountTaskNotHrbp(param ListTaskCondition) (int, error) {
	task1 := d.catchTaskIdByPermission(param.UserId)
	withSql := task1 + `select count(*) from task
	join t_task_1 on task.id =t_task_1.id
	where 1=1 and task.deleted_at isnull and task.company_id=?`
	condition := []interface{}{param.CompanyId}
	whereSql := ``
	if param.OnlyMy {
		condition = append(condition, param.UserId)
		whereSql += ` and task.leader ->>'id' = '?' `
	}
	if param.SearchWord != "" {
		condition = append(condition, param.SearchWord, param.SearchWord)
		whereSql += ` and (task.leader->>'name' like ? or task.alias like ?)`
	}

	sqlStr := withSql + whereSql
	tx := d.transactionContext.PgTx
	var cnt int
	_, err := tx.QueryOne(pg.Scan(&cnt), sqlStr, condition...)
	return cnt, err
}

// 获取任务总数，用于页面展示; 无过滤查看权限
func (d *TaskDao) CountTaskByHrbp(param ListTaskCondition) (int, error) {

	withSql := `with 
	t_task_ignore as (
		select task_ignore.task_id,task_ignore.id  from  task_ignore where task_ignore.user_id =?
	)select count(*) from task
	left join t_task_ignore on t_task_ignore.task_id=task.id
	where 1=1 and task.deleted_at isnull and t_task_ignore.id isnull and task.company_id=?`
	condition := []interface{}{param.UserId, param.CompanyId}
	whereSql := ``
	if param.OnlyMy {
		condition = append(condition, param.UserId)
		whereSql += ` and task.leader ->>'id' = '?' `
	}
	if param.SearchWord != "" {
		condition = append(condition, param.SearchWord, param.SearchWord)
		whereSql += ` and (task.leader->>'name' like ? or task.alias like ?)`
	}
	sqlStr := withSql + whereSql
	tx := d.transactionContext.PgTx
	var cnt int
	_, err := tx.QueryOne(pg.Scan(&cnt), sqlStr, condition...)
	return cnt, err
}

// 统计里程碑异常的数量,以非hrbp角色统计
func (d *TaskDao) CountTaskStageAnomalyNotHrbp(param ListTaskCondition) (int, error) {
	task1 := d.catchTaskIdByPermission(param.UserId)
	withSql := task1 + ` select count(*)
	from task
	join t_task_1 on task.id =t_task_1.id
	where 1=1
	and task.company_id =?
	and task.deleted_at isnull
	and (
		cast(COALESCE(task.current_stage ->>'planCompletedAt','0') as int)<floor(extract(epoch from now()))
		or 
		task.warn_flag = 1
	)
	`
	condition := []interface{}{param.CompanyId}
	whereSql := ``
	if param.OnlyMy {
		condition = append(condition, param.UserId)
		whereSql += ` and task.leader ->>'id' = '?' `
	}
	if param.SearchWord != "" {
		condition = append(condition, param.SearchWord, param.SearchWord)
		whereSql += ` and (task.leader->>'name' like ? or task.alias like ?)`
	}
	sqlStr := withSql + whereSql
	tx := d.transactionContext.PgTx
	var cnt int
	_, err := tx.QueryOne(pg.Scan(&cnt), sqlStr, condition...)
	return cnt, err
}

// 统计里程碑异常的数量,以hrbp角色统计
func (d *TaskDao) CountTaskStageAnomalyByHrbp(param ListTaskCondition) (int, error) {
	withSql := `with 
	t_task_ignore as (
		select task_ignore.task_id,task_ignore.id  from  task_ignore where task_ignore.user_id =?
	)select count(*)
	from task
	left join t_task_ignore on t_task_ignore.task_id=task.id
	where 1=1
	and task.company_id =?
	and task.deleted_at isnull
	and t_task_ignore.id isnull 
	and (
		cast(COALESCE(task.current_stage ->>'planCompletedAt','0') as int)<floor(extract(epoch from now()))
		or 
		task.warn_flag = 1
	)`
	condition := []interface{}{param.UserId, param.CompanyId}
	whereSql := ``
	if param.OnlyMy {
		condition = append(condition, param.UserId)
		whereSql += ` and task.leader ->>'id' = '?' `
	}
	if param.SearchWord != "" {
		condition = append(condition, param.SearchWord, param.SearchWord)
		whereSql += ` and (task.leader->>'name' like ? or task.alias like ?)`
	}

	sqlStr := withSql + whereSql
	tx := d.transactionContext.PgTx
	var cnt int
	_, err := tx.QueryOne(pg.Scan(&cnt), sqlStr, condition...)
	return cnt, err
}

type TaskData3 struct {
	TaskId     int    `pg:"task_id"`
	TaskName   string `pg:"task_name"`
	TaskAlias  string `pg:"task_alias"`
	LeaderName string `pg:"leader_name"`
	LeaderId   string `pg:"leader_id"`
}

// 根据负责人获取超期未完成的异常的里程碑任务
func (d *TaskDao) TaskStageAnomalyByLeader(leaderId []string) ([]TaskData3, error) {
	sqlStr := `select 
	task.id as task_id,
	task."name" as task_name ,
	task.alias as task_alias,
	task.leader ->>'id' as leader_id,
	task.leader ->>'name' as leader_name
from task where 1=1
and task.deleted_at isnull 
and (
 cast(COALESCE(task.current_stage ->>'planCompletedAt','0') as int) between 1 and floor(extract(epoch from now())) 
) 
and task.leader ->>'id' in(?) `

	result := []TaskData3{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, pg.In(leaderId))
	return result, err
}

// 根据任务相关人获取超期未完成的异常的里程碑任务
func (d *TaskDao) TaskStageAnomalyByRelatedUser(relatedUserId int) ([]TaskData3, error) {
	sqlStr := `select 
	task.id as task_id,
	task."name" as task_name ,
	task.alias as task_alias,
	task.leader ->>'id' as leader_id,
	task.leader ->>'name' as leader_name
from task where 1=1
and task.deleted_at isnull 
and (
 cast(COALESCE(task.current_stage ->>'planCompletedAt','0') as int) between 1 and floor(extract(epoch from now())) 
) 
and task.related_user @>? `

	relatedUser := fmt.Sprintf("[%d]", relatedUserId)
	result := []TaskData3{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, relatedUser)
	return result, err
}

type TaskData2 struct {
	Id         int    `pg:"id"`
	Name       string `pg:"name"`
	Alias      string `pg:"alias"`
	LeaderName string `pg:"leader_name"`
	Anomaly    string `pg:"anomaly"`
}

// 根据任务负责任务获取异常的任务
func (d *TaskDao) TaskAnomalyByLeader(leaderId []string, anomaly int) ([]TaskData2, error) {
	sqlStr := `select 
	task."id",
	task."name" ,
	task.alias ,
	task.leader ->>'name' as leader_name,
	task.anomaly 
	from task 
	where task.anomaly >=? and task.leader ->>'id' in(?) and task.deleted_at isnull `
	result := []TaskData2{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, anomaly, pg.In(leaderId))
	return result, err
}

// 统计任务反馈异常的数量,以非hrbp角色统计
func (d *TaskDao) CountTaskAnomalyNotHrbp(param ListTaskCondition) (int, error) {
	task1 := d.catchTaskIdByPermission(param.UserId)
	withSql := task1 + ` select count(*) 
	from task
	join t_task_1 on task.id =t_task_1.id
	where 1=1 
	and task.anomaly>0 and company_id=? and task.deleted_at isnull `
	condition := []interface{}{param.CompanyId}
	whereSql := ``
	if param.OnlyMy {
		condition = append(condition, param.UserId)
		whereSql += ` and task.leader ->>'id' = '?' `
	}
	if param.SearchWord != "" {
		condition = append(condition, param.SearchWord, param.SearchWord)
		whereSql += ` and (task.leader->>'name' like ? or task.alias like ?)`
	}
	sqlStr := withSql + whereSql
	tx := d.transactionContext.PgTx
	var cnt int
	_, err := tx.QueryOne(pg.Scan(&cnt), sqlStr, condition...)
	return cnt, err
}

// 统计任务反馈异常的数量,以hrbp角色统计
func (d *TaskDao) CountTaskAnomalyByHrbp(param ListTaskCondition) (int, error) {
	withSql := `with 
	t_task_ignore as (
		select task_ignore.task_id,task_ignore.id  from  task_ignore where task_ignore.user_id =?
	)select count(*) 
	from task
	left join t_task_ignore on t_task_ignore.task_id=task.id
	where 1=1 and task.anomaly>0 and task.company_id=? and  t_task_ignore.id isnull and task.deleted_at isnull`
	condition := []interface{}{param.UserId, param.CompanyId}
	whereSql := ``
	if param.OnlyMy {
		condition = append(condition, param.UserId)
		whereSql += ` and task.leader ->>'id' = '?' `
	}
	if param.SearchWord != "" {
		condition = append(condition, param.SearchWord, param.SearchWord)
		whereSql += ` and (task.leader->>'name' like ? or task.alias like ?)`
	}
	sqlStr := withSql + whereSql
	tx := d.transactionContext.PgTx
	var cnt int
	_, err := tx.QueryOne(pg.Scan(&cnt), sqlStr, condition...)
	return cnt, err
}

// 任务数据
type TaskData1 struct {
	Name       string `pg:"name"`
	Alias      string `pg:"alias"`
	LeaderId   string `pg:"leader_id"`
	LeaderName string `pg:"leader_name"`
	LevelName  string `pg:"level_name"`
}

// 获取任务列表
func (d *TaskDao) ListTask2(userId int) ([]TaskData1, error) {
	task1 := d.catchTaskIdByPermission(userId)
	sqlStr := task1 + ` select task."name" ,task.alias ,
	task.leader ->>'id' as leader_id,
	task.leader ->>'name' as leader_name,
	task.level_name 
	from task  
	join t_task_1 on task.id =t_task_1.id
	where task.deleted_at isnull
	`
	result := []TaskData1{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr)
	return result, err
}

// 获取任务列表
func (d *TaskDao) ListTask2ForHrbp(userId int, companyId int) ([]TaskData1, error) {
	sqlStr := `with 
	t_task_ignore as (
		select task_ignore.task_id,task_ignore.id  from  task_ignore where task_ignore.user_id =?
	)select task."name" ,task.alias ,
	task.leader ->>'id' as leader_id,
	task.leader ->>'name' as leader_name,
	task.level_name 
	from task
	left join t_task_ignore on t_task_ignore.task_id=task.id
	where 1=1 and task.company_id=? and task.deleted_at isnull`
	result := []TaskData1{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, userId, companyId)
	return result, err

}

type TaskData4 struct {
	TaskId      int    `pg:"task_id"`
	TaskName    string `pg:"task_name"`
	TaskAlias   string `pg:"task_alias"`
	LeaderName  string `pg:"leader_name"`
	LeaderId    int    `pg:"leader_id"`
	RelatedUser []int  `pg:"related_user,type:jsonb"`
}

// TaskStageAnomalyAll 获取所有里程碑异常的任务，
func (d TaskDao) TaskStageAnomalyAll() ([]TaskData4, error) {
	sqlStr := `select 
	task.id as task_id,
	task."name" as task_name ,
	task.alias as task_alias,
	task.leader ->>'id' as leader_id,
	task.leader ->>'name' as leader_name,
	task.related_user 
from task where 1=1
and task.deleted_at isnull 
and (
 cast(COALESCE(task.current_stage ->>'planCompletedAt','0') as int) between 1 and floor(extract(epoch from now())) 
) `
	result := []TaskData4{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr)
	return result, err
}

type TaskData5 struct {
	TaskId      int    `pg:"task_id"`
	TaskName    string `pg:"task_name"`
	TaskAlias   string `pg:"task_alias"`
	LeaderName  string `pg:"leader_name"`
	LeaderId    int    `pg:"leader_id"`
	RelatedUser []int  `pg:"related_user,type:jsonb"`
	Anomaly     int    `pg:"anomaly"`
}

// TaskRecordAnomalyAll 获取所有反馈异常的任务，
func (d TaskDao) TaskRecordAnomalyAll() ([]TaskData5, error) {
	sqlStr := `select 
	task.id as task_id,
	task."name" as task_name ,
	task.alias as task_alias,
	task.anomaly,
	task.leader ->>'id' as leader_id,
	task.leader ->>'name' as leader_name,
	task.related_user 
from task where 1=1
and task.deleted_at isnull 
and task.anomaly>0 `
	result := []TaskData5{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr)
	return result, err
}

type ListTask struct {
	SortBy1          int              `pg:"sort_by_1"`
	CPlanCompletedAt int              `pg:"c_plan_completed_at"`
	SortBy2          int              `pg:"sort_by_2"`
	TaskId           int              `pg:"task_id"`
	Alias            string           `pg:"alias"`
	LeaderName       string           `pg:"leader_name"`
	Level            int              `pg:"level"`
	LevelName        string           `pg:"level_name"`
	Anomaly          int              `pg:"anomaly"`
	Status           int              `pg:"status"`
	UpdatedAt        time.Time        `pg:"updated_at"`
	CreatedAt        time.Time        `pg:"created_at"`
	CurrentStage     domain.TaskStage `pg:"current_stage"` // 当前执行的里程碑
	LastStage        domain.TaskStage `pg:"last_stage"`    // 上一个完成的里程碑
}

// (新)获取任务，用于页面展示; 无过滤查看权限
// userid 谁要查看任务数据
// limit 分页
// offset 分页
func (d *TaskDao) ListTaskByHrbpV2(param ListTaskCondition) ([]ListTask, error) {
	sqlStr := `
	with 
	t_task_ignore as (
		select task_ignore.task_id,task_ignore.id  from  task_ignore where task_ignore.user_id =?
	),
	t_task as (
		select 
		cast(COALESCE(task.current_stage ->>'planCompletedAt','0') as int)  as c_plan_completed_at,
		cast(COALESCE(task.last_stage ->>'planCompletedAt','0') as int)  as l_plan_completed_at,
		cast(COALESCE(task.last_stage ->>'realCompletedAt','0') as int) as l_real_completed_at,
		floor(extract(epoch from now())) as now_time,
		task.id ,
		task.alias ,
		task.leader ->>'name' as leader_name,
		task."level",
		task.level_name ,
		task.anomaly ,
		task.status ,
		task.updated_at,
		task.current_stage,
		task.last_stage,
		task.created_at 
		from task 
		left join  t_task_ignore on t_task_ignore.task_id=task.id
		where 1=1 and t_task_ignore.id isnull and task.company_id=?
		and task.deleted_at isnull
		%s
	),
	t_task_1 as (
		select 
		(case 
			when t_task.c_plan_completed_at=0 then 0
			when t_task.c_plan_completed_at-now_time>0 then 0
			else t_task.c_plan_completed_at-now_time
			end) as sort_by_1,
		(case
			when t_task.l_plan_completed_at=0 then 0
			when t_task.l_plan_completed_at-t_task.l_real_completed_at>0 then 0
			else t_task.l_plan_completed_at-t_task.l_real_completed_at
		end) as sort_by_2,
		t_task.c_plan_completed_at ,
		t_task.id as task_id,
		t_task.alias ,
		t_task.leader_name,
		t_task."level",
		t_task.level_name ,
		t_task.anomaly ,
		t_task.status ,
		t_task.updated_at ,
		t_task.current_stage,
		t_task.last_stage,
		t_task.created_at
		from t_task  
		where 1=1
	)select *
	from t_task_1
	order by t_task_1.sort_by_1,
	t_task_1.sort_by_2,t_task_1.anomaly desc,
	t_task_1."level",t_task_1."created_at"
	limit ? offset ?
	`
	condition := []interface{}{param.UserId, param.CompanyId}
	whereSql := ``
	if param.OnlyMy {
		condition = append(condition, param.UserId)
		whereSql += ` and task.leader ->>'id' = '?' `
	}
	if param.SearchWord != "" {
		condition = append(condition, param.SearchWord, param.SearchWord)
		whereSql += ` and (task.leader->>'name' like ? or task.alias like ?)`
	}
	strSql2 := fmt.Sprintf(sqlStr, whereSql)
	condition = append(condition, param.Limit, param.Offset)
	result := []ListTask{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, strSql2, condition...)
	return result, err
}

func (d *TaskDao) ListTaskNotHrbpV2(param ListTaskCondition) ([]ListTask, error) {
	withsql := d.catchTaskIdByPermission(param.UserId)
	strSql := withsql + `
	,t_task as (
		select 
		cast(COALESCE(task.current_stage ->>'planCompletedAt','0') as int)  as c_plan_completed_at,
		cast(COALESCE(task.last_stage ->>'planCompletedAt','0') as int)  as l_plan_completed_at,
		cast(COALESCE(task.last_stage ->>'realCompletedAt','0') as int) as l_real_completed_at,
		floor(extract(epoch from now())) as now_time,
		task.id ,
		task.alias ,
		task.leader ->>'name' as leader_name,
		task."level",
		task.level_name ,
		task.anomaly ,
		task.current_stage,
		task.last_stage,
		task.status ,
		task.updated_at,
		task.created_at 
		from task 
		join t_task_1 on t_task_1.id=task.id
		where 1=1 and task.company_id=? and task.deleted_at isnull
		%s
	),
	tt_task_1 as (
		select 
		(case 
			when t_task.c_plan_completed_at=0 then 0
			when t_task.c_plan_completed_at-now_time>0 then 0
			else t_task.c_plan_completed_at-now_time
			end) as sort_by_1,
		(case
			when t_task.l_plan_completed_at=0 then 0
			when t_task.l_plan_completed_at-t_task.l_real_completed_at>0 then 0
			else t_task.l_plan_completed_at-t_task.l_real_completed_at
		end) as sort_by_2,
		t_task.c_plan_completed_at ,
		t_task.id as task_id,
		t_task.alias ,
		t_task.leader_name,
		t_task."level",
		t_task.level_name ,
		t_task.anomaly ,
		t_task.current_stage,
		t_task.last_stage,
		t_task.status ,
		t_task.updated_at ,
		t_task.created_at
		from t_task  
		where 1=1
	)select *
	from tt_task_1
	order by tt_task_1.sort_by_1,
	tt_task_1.sort_by_2,tt_task_1.anomaly desc,
	tt_task_1."level",tt_task_1."created_at"
	limit ? offset ?
	`
	condition := []interface{}{param.CompanyId}
	whereSql := ``
	if param.OnlyMy {
		condition = append(condition, param.UserId)
		whereSql += ` and task.leader ->>'id' = '?' `
	}
	if param.SearchWord != "" {
		condition = append(condition, param.SearchWord, param.SearchWord)
		whereSql += ` and (task.leader->>'name' like ? or task.alias like ?)`
	}
	strSql2 := fmt.Sprintf(strSql, whereSql)
	condition = append(condition, param.Limit, param.Offset)
	result := []ListTask{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, strSql2, condition...)
	return result, err
}