package dao

import (
	"fmt"
	"time"

	"github.com/go-pg/pg/v10"
	pgTransaction "github.com/linmadan/egglib-go/transaction/pg"
)

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 
		)
	),
	-- 根据任务负责人和相关人员查询
	t_task_0 as (
		(select task.id from task 
		join t_user on task.leader ->>'id'=t_user.id::text 
		)
		union
		(select task.id from task where task.related_user@>'[%d]')
	),
	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"`
}

// 获取任务以及里程碑列表，用于页面展示; 有过滤查看权限
// userid 谁要查看任务数据
// limit 分页
// offset 分页
func (d *TaskDao) ListTaskStageNotHrbp(param ListTaskCondition) ([]ListTaskStage, error) {
	task1 := d.catchTaskIdByPermission(param.UserId)
	withSql := task1 + `,
	-- 获取的里程碑数据，以及排序
	t_task_tage_1 as(
		select 
		task.id as task_id,
		task."name" as task_name,
		task."alias" as task_alias_name,
		task.leader ->>'name' as leader_name,
		task.leader ->>'id' as leader_id,
		task.level_name ,
		task.anomaly ,
		task.updated_at ,
		task.created_at ,
		task."level" ,
		task."warn_flag",
		task."status" as task_status,
		task_stage."id" as stage_id,
		task_stage."name" as stage_name,
		task_stage.sort_by as stage_sort_by,
		task_stage.plan_completed_at,
		task_stage.real_completed_at,
		(case 
			when task_stage.real_completed_at =0 
			then task_stage.plan_completed_at - floor( extract(epoch from now()))
			else task_stage.plan_completed_at - task_stage.real_completed_at
		end) as diff_time
		from  task 
		join  t_task_1 on task.id=t_task_1.id
		join task_stage on task.id =task_stage.task_id 
		where 1=1 and task.company_id=?
		order by diff_time,task."level",task.created_at desc
	),
	-- 按任务数据分页获取
	t_task_page as (
		select distinct t_task_tage_1.task_id 
		from t_task_tage_1 
		where 1=1
		%s
		limit ? offset ?
	)
	select 
	    t_task_tage_1.task_id,
		t_task_tage_1.task_name,
		t_task_tage_1.task_alias_name,
		t_task_tage_1.leader_name,
		t_task_tage_1.level_name ,
		t_task_tage_1.anomaly ,
		t_task_tage_1.updated_at ,
		t_task_tage_1.created_at ,
		t_task_tage_1."level" ,
		t_task_tage_1.plan_completed_at,
		t_task_tage_1.real_completed_at,
		t_task_tage_1.stage_name,
		t_task_tage_1.stage_id,
		t_task_tage_1.stage_sort_by,
		t_task_tage_1."warn_flag"
	from t_task_tage_1
	where t_task_tage_1.task_id in(
	 select t_task_page.task_id from t_task_page
	)`
	condition := []interface{}{param.CompanyId}
	whereSql := ``
	if param.OnlyMy {
		condition = append(condition, param.UserId)
		whereSql += ` and t_task_tage_1.leader_id = '?' `
	}
	if param.SearchWord != "" {
		condition = append(condition, param.SearchWord, param.SearchWord)
		whereSql += ` and (t_task_tage_1.leader_name like ? or t_task_tage_1.task_alias_name like ?)`
	}
	condition = append(condition, param.Limit, param.Offset)
	sqlStr := fmt.Sprintf(withSql, whereSql)
	result := []ListTaskStage{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

// 获取任务总数，用于页面展示; 有过滤查看权限
func (d *TaskDao) CountTaskStageNotHrbp(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=?`
	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
}

// 获取任务以及里程碑列表，用于页面展示; 无过滤查看权限
// userid 谁要查看任务数据
// limit 分页
// offset 分页
func (d *TaskDao) ListTaskStageByHrbp(param ListTaskCondition) ([]ListTaskStage, error) {
	withSql := `with 
	t_task_ignore as (
			select task_ignore.task_id,task_ignore.id  from  task_ignore where task_ignore.user_id =?
	),
	-- 获取的里程碑数据，以及排序
	t_task_tage_1 as(
		select 
		task.id as task_id,
		task."name" as task_name,
		task."alias" as task_alias_name,
		task."status" as task_status,
		task.leader ->>'name' as leader_name,
		task.leader ->>'id' as leader_id,
		task.level_name ,
		task.anomaly ,
		task.updated_at ,
		task."warn_flag",
		task.created_at ,
		task."level" ,
		task_stage."id" as stage_id,
		task_stage."name" as stage_name,
		task_stage.sort_by as stage_sort_by,
		task_stage.plan_completed_at,
		task_stage.real_completed_at,
		(case 
			when task_stage.real_completed_at =0 
			then task_stage.plan_completed_at - floor( extract(epoch from now()))
			else task_stage.plan_completed_at - task_stage.real_completed_at
		end) as diff_time
		from  task 
		join task_stage on task.id =task_stage.task_id 
		where 1=1 and task.company_id=?
		order by diff_time,task."level",task.created_at desc
	),
	-- 按任务数据分页获取
	t_task_page as (
		select distinct t_task_tage_1.task_id 
		from t_task_tage_1
		left join  t_task_ignore on t_task_ignore.task_id=t_task_tage_1.task_id
		where t_task_ignore.id isnull 
		%s
		limit ? offset ?
	)
	select 
		t_task_tage_1.task_id,
		t_task_tage_1.task_name,
		t_task_tage_1.task_alias_name,
		t_task_tage_1.task_status,
		t_task_tage_1.leader_name,
		t_task_tage_1.level_name ,
		t_task_tage_1.anomaly ,
		t_task_tage_1.updated_at ,
		t_task_tage_1.created_at ,
		t_task_tage_1."level" ,
		t_task_tage_1.plan_completed_at,
		t_task_tage_1.real_completed_at,
		t_task_tage_1.stage_name,
		t_task_tage_1.stage_id,
		t_task_tage_1."warn_flag",
		t_task_tage_1.stage_sort_by
	from t_task_tage_1
	where t_task_tage_1.task_id in(
	 select t_task_page.task_id from t_task_page
	)`
	condition := []interface{}{param.UserId, param.CompanyId}
	whereSql := ``
	if param.OnlyMy {
		condition = append(condition, param.UserId)
		whereSql += ` and t_task_tage_1.leader_id = '?' `
	}
	if param.SearchWord != "" {
		condition = append(condition, param.SearchWord, param.SearchWord)
		whereSql += ` and (t_task_tage_1.leader_name like ? or t_task_tage_1.task_alias_name like ?) `
	}
	condition = append(condition, param.Limit, param.Offset)
	sqlStr := fmt.Sprintf(withSql, whereSql)
	result := []ListTaskStage{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

// 获取任务总数，用于页面展示; 无过滤查看权限
func (d *TaskDao) CountTaskStageByHrbp(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=?`
	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_stage 
	join task on task_stage.task_id =task.id 
	join t_task_1 on task.id =t_task_1.id
	where 1=1 and task.company_id=?
	and(
		(task_stage.plan_completed_at <task_stage.real_completed_at)
		or
		(task_stage.real_completed_at=0 and task_stage.plan_completed_at<extract(epoch from now()))
	) `
	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_stage 
	join task on task_stage.task_id =task.id 
	left join t_task_ignore on t_task_ignore.task_id=task.id
	where 1=1 and task.company_id=?
	and(
		(task_stage.plan_completed_at <task_stage.real_completed_at)
		or
		(task_stage.real_completed_at=0 and task_stage.plan_completed_at<extract(epoch from now()))
	) `
	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
}

func (d *TaskDao) IncreaseAnomaly(id []int, incr int) error {
	if len(id) == 0 {
		return nil
	}
	sqlStr := `update  task set anomaly=task.anomaly+?
	where  id in(?)`
	tx := d.transactionContext.PgTx
	_, err := tx.Exec(sqlStr, incr, pg.In(id))
	return err
}

// TaskStageData
type TaskStageData struct {
	Id         int
	Name       string
	LeaderName string
}

// 异常的里程碑任务
func (d *TaskDao) TaskStageAnomaly(leaderId []string) ([]TaskStageData, error) {
	sqlStr := `select 
	task_stage.id,
	task_stage."name" ,
	task.leader ->>'name' as leader_name
	from task_stage 
	join task on task.id=task_stage.task_id 
	where 1=1
	and task_stage.real_completed_at=0 and task_stage.plan_completed_at<extract(epoch from now())
	and task.leader ->>'id' in(?) `
	result := []TaskStageData{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, pg.In(leaderId))
	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) TaskAnomaly(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(?) `
	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>1 `
	condition := []interface{}{}
	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>1 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
}

// 任务数据
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
	`
	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=? `
	result := []TaskData1{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, userId, companyId)
	return result, err

}