package dao

import (
	"fmt"

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

type RewardSumaryDao struct {
	transactionContext *pgTransaction.TransactionContext
}

func NewRewardSumaryDao(transactionContext *pgTransaction.TransactionContext) (*RewardSumaryDao, error) {
	if transactionContext == nil {
		return nil, fmt.Errorf("transactionContext参数不能为nil")
	} else {
		return &RewardSumaryDao{
			transactionContext: transactionContext,
		}, nil
	}
}

// 功过看板-日榜
type RewardSummaryDay struct {
	Id                 int    `json:"id"` //id
	RecordDateStr      string `json:"record_date_str"`
	SectionName        string `json:"sectionName"`        //工段名称
	WorkerName         string `json:"workerName"`         //员工名称
	SummaryResult      string `json:"summaryResult"`      //奖惩结果
	YieldResult        string `json:"yieldResult"`        //产效功过结果
	UpToStandardResult string `json:"upToStandardResult"` //合格率功过结果
	AccidentResult1    string `json:"accidentResult1"`    //质量事故功过结果
	AccidentResult2    string `json:"accidentResul2"`     //安全事故功过结果
	AccidentResult3    string `json:"accidentResul3"`     //异物 功过结果
}

// 功过看板-日榜
func (d *RewardSumaryDao) SeachRewardSummaryDay(companyId int, orgId int, recordDate string) ([]RewardSummaryDay, error) {
	sqlStr := `SELECT 
	"id",record_date_str, 
	work_station->>'sectionName' as section_name,
	worker->>'userName' as worker_name,
	summary_result,
	yield_result,
	up_to_standard_result,
	accident_result1,
	accident_result2,
	accident_result3
	FROM manufacture."reward_summary"
	WHERE company_id=?
	and org_id=?
	and record_date_str=?`
	tx := d.transactionContext.PgTx
	var result []RewardSummaryDay
	_, err := tx.Query(&result, sqlStr, companyId, orgId, recordDate)

	for i := range result {
		switch result[i].AccidentResult1 {
		case "1":
			result[i].AccidentResult1 = "功"
		case "0":
			result[i].AccidentResult1 = "不奖不惩"
		case "-1":
			result[i].AccidentResult1 = "过"
		}
		switch result[i].AccidentResult2 {
		case "1":
			result[i].AccidentResult2 = "功"
		case "0":
			result[i].AccidentResult2 = "不奖不惩"
		case "-1":
			result[i].AccidentResult2 = "过"
		}
		switch result[i].AccidentResult3 {
		case "1":
			result[i].AccidentResult3 = "功"
		case "0":
			result[i].AccidentResult3 = "不奖不惩"
		case "-1":
			result[i].AccidentResult3 = "过"
		}
		switch result[i].UpToStandardResult {
		case "1":
			result[i].UpToStandardResult = "功"
		case "0":
			result[i].UpToStandardResult = "不奖不惩"
		case "-1":
			result[i].UpToStandardResult = "过"
		}
		switch result[i].YieldResult {
		case "1":
			result[i].YieldResult = "功"
		case "0":
			result[i].YieldResult = "不奖不惩"
		case "-1":
			result[i].YieldResult = "过"
		}
	}
	return result, err
}

// 功过看板-产效
type RewardSummaryYield struct {
	RecordDateStr string `json:"record_date_str"`
	SectionName   string `json:"sectionName"` //工段名称
	WorkerName    string `json:"workerName"`  //员工名称
	Yield         string `json:"yield"`       //产效
	YieldResult   string `json:"yieldResult"` //产效功过结果
	YieldMax      string `json:"yildMax"`     //产效历史最佳
}

// 功过看板-产效
func (d *RewardSumaryDao) SeachRewardSummaryYield(companyId int, orgId int, recordDate string) ([]RewardSummaryYield, error) {
	// sqlStr := `SELECT
	// record_date_str,
	// work_station->>'sectionName' as section_name,
	// worker->>'userName' as worker_name,
	// yield_result,
	// yield,
	// '24' as yield_max
	// FROM manufacture."reward_summary"
	// WHERE company_id=?
	// and org_id=?
	// and record_date_str=?`

	sqlStr := `with 
	t1 as( SELECT 
	max(yield) as yield_max,
	work_station->>'sectionId' AS section_id,
	worker->>'userId' as worker_id
	FROM manufacture."reward_summary"
	WHERE company_id=? and org_id=?
	GROUP BY section_id,worker_id
	),
	t2 as (SELECT 
		record_date_str, 
		work_station->>'sectionId' AS section_id,
		worker->>'userId' as worker_id,
		work_station->>'sectionName' as section_name,
		worker->>'userName' as worker_name,
		yield_result,
		yield
		FROM manufacture."reward_summary"
		WHERE company_id=? and org_id=?
		and record_date_str=?
	)
	SELECT t2.record_date_str,t2.section_name,t2.worker_name,
	t2.yield_result,t2.yield,t1.yield_max
	FROM t2
	JOIN t1 on t2.section_id=t1.section_id AND t2.worker_id=t2.worker_id`

	tx := d.transactionContext.PgTx
	var result []RewardSummaryYield
	params := []interface{}{
		companyId, orgId,
		companyId, orgId, recordDate,
	}
	_, err := tx.Query(&result, sqlStr, params...)
	for i := range result {
		switch result[i].YieldResult {
		case "1":
			result[i].YieldResult = "功"
		case "0":
			result[i].YieldResult = "不奖不惩"
		case "-1":
			result[i].YieldResult = "过"
		}
	}
	return result, err

}

// 功过看板-合格率
type RewardSummaryUpToStandard struct {
	RecordDateStr      string `json:"record_date_str"`
	SectionName        string `json:"sectionName"`        //工段名称
	WorkerName         string `json:"workerName"`         //员工名称
	UpToStandard       string `json:"upToStandard"`       //合格率
	UpToStandardResult string `json:"upToStandardResult"` //合格率功过结果
	UpToStandardMax    string `json:"upToStandardMax"`    //合格率历史最佳
}

// 功过看板-合格率
func (d *RewardSumaryDao) SeachRewardUpToStandard(companyId int, orgId int, recordDate string) ([]RewardSummaryUpToStandard, error) {
	// sqlStr := `SELECT
	// record_date_str,
	// work_station->>'sectionName' as section_name,
	// worker->>'userName' as worker_name,
	// up_to_standard_result,
	// up_to_standard,
	// '90' as up_to_standard_max
	// FROM manufacture."reward_summary"
	// WHERE company_id=23
	// and org_id=487
	// and record_date_str='2022-10-29'`
	sqlStr := `with 
	t1 as( SELECT 
	max(up_to_standard) as up_to_standard_max,
	work_station->>'sectionId' AS section_id,
	worker->>'userId' as worker_id
	FROM manufacture."reward_summary"
	WHERE company_id=? and org_id=?
	GROUP BY section_id,worker_id
	),
	t2 as (SELECT 
		record_date_str, 
		work_station->>'sectionId' AS section_id,
		worker->>'userId' as worker_id,
		work_station->>'sectionName' as section_name,
		worker->>'userName' as worker_name,
		up_to_standard_result,
		up_to_standard
		FROM manufacture."reward_summary"
		WHERE company_id=? and org_id=?
		and record_date_str=?
	)
	SELECT t2.record_date_str,t2.section_name,t2.worker_name,
	t2.up_to_standard_result,t2.up_to_standard,t1.up_to_standard_max
	FROM t2
	JOIN t1 on t2.section_id=t1.section_id AND t2.worker_id=t2.worker_id`
	tx := d.transactionContext.PgTx
	var result []RewardSummaryUpToStandard
	params := []interface{}{
		companyId, orgId,
		companyId, orgId, recordDate,
	}
	_, err := tx.Query(&result, sqlStr, params...)
	for i := range result {
		switch result[i].UpToStandardResult {
		case "1":
			result[i].UpToStandardResult = "功"
		case "0":
			result[i].UpToStandardResult = "不奖不惩"
		case "-1":
			result[i].UpToStandardResult = "过"
		}
	}
	return result, err
}

// 功过看板-异常
type RewardAccident struct {
	Id              string `json:"id"`
	RecordDateStr   string `json:"record_date_str"`
	SectionName     string `json:"sectionName"`     //工段名称
	WorkerName      string `json:"workerName"`      //员工名称
	AccidentAmount1 string `json:"accidentAmount1"` //质量事故损失金额
	AccidentNum1    string `json:"accidentNum1"`    //质量事故次数
	AccidentResult1 string `json:"accidentResult1"` //质量事故功过结果
	AccidentAmount2 string `json:"accidentAmount2"` //安全事故 损失金额
	AccidentResult2 string `json:"accidentResul2"`  //安全事故功过结果
	AccidentNum2    string `json:"accidentNum2"`    //安全事故 次数
	AccidentNum3    string `json:"accidentNum3"`    //金属异物 次数
	AccidentNum4    string `json:"accidentNum4"`    //非属异物事故次数
	AccidentResult3 string `json:"accidentResul3"`  //异物 功过结果
}

// 功过看板-异常
func (d *RewardSumaryDao) SeachRewardAccident(companyId int, orgId int, recordDate string) ([]RewardAccident, error) {
	sqlStr := `SELECT 
	"id",record_date_str, 
	work_station->>'sectionName' as section_name,
	worker->>'userName' as worker_name,
	accident_num1,accident_amount1,accident_result1,
	accident_num2,accident_amount2,accident_result2,
	accident_num3,accident_num4,accident_result3
	FROM manufacture."reward_summary"
	WHERE company_id=?
	and org_id=?
	and record_date_str=?`
	tx := d.transactionContext.PgTx
	var result []RewardAccident
	_, err := tx.Query(&result, sqlStr, companyId, orgId, recordDate)

	for i := range result {
		switch result[i].AccidentResult1 {
		case "1":
			result[i].AccidentResult1 = "功"
		case "0":
			result[i].AccidentResult1 = "不奖不惩"
		case "-1":
			result[i].AccidentResult1 = "过"
		}
		switch result[i].AccidentResult2 {
		case "1":
			result[i].AccidentResult2 = "功"
		case "0":
			result[i].AccidentResult2 = "不奖不惩"
		case "-1":
			result[i].AccidentResult2 = "过"
		}
		switch result[i].AccidentResult3 {
		case "1":
			result[i].AccidentResult3 = "功"
		case "0":
			result[i].AccidentResult3 = "不奖不惩"
		case "-1":
			result[i].AccidentResult3 = "过"
		}
	}
	return result, err
}

// 功过看板-月榜
type RewardSummaryMonth struct {
	SectionId       string `json:"sectionId"`       //工段名称
	SectionName     string `json:"sectionName"`     //工段名称
	WorkerName      string `json:"workerName"`      //员工名称
	WorkerId        string `json:"workerId"`        //员工名称
	AccidentAmount1 string `json:"accidentAmount1"` //质量事故损失金额
	AccidentNum1    string `json:"accidentNum1"`    //质量事故次数
	AccidentNum3    string `json:"accidentNum3"`    //金属异物事故  次数
	AccidentNum4    string `json:"accidentNum4"`    //非属异物事故  次数
	AccidentAmount2 string `json:"accidentAmount2"` //安全事故 损失金额
	AccidentNum2    string `json:"accidentNum2"`    //安全事故 次数
	YieldAvg        string `json:"yieldAvg"`        //平均产效
	YieldMax        string `json:"yieldMax"`        //产效历史最佳
	UpToStandard    string `json:"upToStandard"`    //合格率
	AmountFine      string `json:"amountFine"`      //惩，的金额
	AmountReward    string `json:"amountReward"`    //奖，的金额
	AmountFinal     string `json:"amountFinal"`     //最终的 奖惩结果
}

// 功过看板-月榜
func (d *RewardSumaryDao) SeachRewardSummaryMonth(companyId int, orgId int, recordMonth string) ([]RewardSummaryMonth, error) {
	sqlStr := `
	SELECT 
	work_station->>'sectionId' AS section_id,
	work_station->>'sectionName' as section_name,
	worker->>'userId' as worker_id,
	worker->>'userName' as worker_name,
	sum(accident_num1) as accident_num1,
	sum(accident_num2) as accident_num2,
	sum(accident_num3) as accident_num3,
	sum(accident_num4) as accident_num4,
	sum(accident_amount1) as accident_amount1,
	sum(accident_amount2) as accident_amount2,
	CAST(sum(summary_result) as decimal(10,2))  as amount_final,
	CAST( 
	sum(CASE WHEN summary_result<0 THEN summary_result ELSE 0 END) as decimal(10,2)
	) as amount_fine,
	CAST( 
	sum(CASE WHEN summary_result>0 THEN summary_result ELSE 0 END) as decimal(10,2)
	) as amount_reward,
	max(yield) as yield_max,
	CAST(avg(yield) as decimal(10,2)) as yield_avg,
	CAST(avg(up_to_standard) as decimal(10,2) ) as up_to_standard
	FROM manufacture.reward_summary
	WHERE company_id=?
	and org_id=?
	and record_month_str=?
	GROUP BY section_id,worker_id,worker_name,section_name
	;
	`
	tx := d.transactionContext.PgTx
	var result []RewardSummaryMonth
	_, err := tx.Query(&result, sqlStr, companyId, orgId, recordMonth)
	return result, err
}