package dao

import (
	"fmt"

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

//车间看板（生产）的展示数据

type BoardShowDao struct {
	transactionContext *pgTransaction.TransactionContext
}

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

type BoardWorkshopDesc struct {
	WorkshopName string `json:"workshopName"`
	Uname        string `json:"uname"`
	Lines        string `json:"lines"`
	Workon       string `json:"workon"`
}

// WorkshopDesc 车间描述
func (d *BoardShowDao) WorkshopDesc(companyId int, orgId int, workshopId int) (*BoardWorkshopDesc, error) {
	sqlStr := `with workshop as (
		select a.workshop_name,a.uname,a.line->>'lineName' linename from (
		 select workshop_name,principal->>'userName' uname
		 , jsonb_array_elements(product_lines) line
		 from manufacture.workshop where company_id = ?
			and org_id = ?
			and workshop_id=?
		) a
		where a.line->>'removed' = '1'
		)
		select max(workshop_name) workshop_name
		,max(uname) uname
		,string_agg(linename, '\') lines,
		'全天' workon 
		from workshop`
	tx := d.transactionContext.PgTx
	var result BoardWorkshopDesc
	_, err := tx.QueryOne(&result, sqlStr, companyId, orgId, workshopId)
	if err != nil {
		return nil, err
	}
	return &result, nil
}

// 在岗人数
func (d *BoardShowDao) OnDutyCount(companyId int, orgId int, workshopId int) (int, error) {
	sqlStr := `select count(distinct product_worker->>'userId') from manufacture.product_attendance_record
	where company_id = ?
		and org_id = ?
		and work_station->>'workshopId'='?'
	 and sign_in>to_date(to_char(now(), 'YYYY-MM-DD'),'YYYY-MM-DD')`
	var cnt int
	tx := d.transactionContext.PgTx
	_, err := tx.QueryOne(pg.Scan(&cnt), sqlStr, companyId, orgId, workshopId)
	return cnt, err
}

// 员工效率排名
type BoardEmployeeWorkpieceRatio struct {
	Uname     string `json:"uname"`
	Today     string `json:"today"`
	Yesterday string `json:"yesterday"`
	History   string `json:"history"`
}

// 员工效率排名
func (d *BoardShowDao) EmployeeWorkpieceRatio(companyId int, orgId int, workshopId int) (
	[]BoardEmployeeWorkpieceRatio, error) {
	sqlStr := `with employee_plan_product as(
		select product_worker->>'userName' as uname,
		(product_weigh - second_level_weigh - CAST(product_record_info->>'totalOtherSecondLevelWeigh' as decimal)) today
		,CAST(product_record_info->>'yesterdayOutputWeight' as decimal) yesterday
		,CAST(product_record_info->>'bestOutputWeight' as decimal) history
		from manufacture.employee_product_record
		where 
		company_id = ?
		and org_id = ?
		and work_station->>'workshopId'='?'
		and work_station->>'sectionName' = '包装'
		and product_record_info->>'productDate'=to_char(now(), 'YYYY-MM-DD')
		),group_employee_plan_product as (
		 select uname,sum(today) today,sum(yesterday) yesterday,sum(history) history from employee_plan_product
		 group by uname
		)
		select * from group_employee_plan_product
		order by today desc`
	result := make([]BoardEmployeeWorkpieceRatio, 0)
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, companyId, orgId, workshopId)
	if err != nil {
		return nil, err
	}
	return result, nil
}

// 时段产能
type BoardTimeSectionProductRecord struct {
	Ts    string `json:"ts"`
	Total string `json:"total"`
	Line  string `json:"line"`
}

// TimeSectionProductRecord 时段产能
func (d *BoardShowDao) TimeSectionProductRecord(companyId int, orgId int, workshopId int, lineIdA int, lineIdB int) (
	[]BoardTimeSectionProductRecord, error,
) {
	sqlStr := `(WITH ts_product as(
		select sum(a.weight) total,a.ts,max(line) line from (
			select 
			cast(device_running_record_info->>'count' as DECIMAL) weight,
			"replace"(to_char(created_at at time ZONE 'Asia/shanghai', 'HH24:') ||	cast(date_part('minute',created_at) as integer)/30*30, ':0', ':00') ts,
			work_station->>'lineName' line
			from manufacture.device_running_record
			where 
			company_id = ?
			and org_id = ?
			and work_station->>'workshopId'='?'
			and work_station->>'lineId' = '?'
			and device_running_record_info->>'deviceType'='CCJ'
			and created_at >to_date(to_char(now(), 'YYYY-MM-DD'),'YYYY-MM-DD')
		) a
		group by a.ts
		order by ts 
	)
	, ts_product_list as (
		select d.ts,ts_product.total,line from (
			select 	to_char(c.ts::timestamp,'HH24:MI') ts from (
				select	generate_series(a.end - interval  '2.5 hour',
				"replace"(to_char(a.end, 'yyyy-mm-dd HH24:') ||	cast(date_part('minute',a.end) as integer)/30*30+30, ':0', ':00')::timestamp,
				'30 minute') ts from (
						select  to_timestamp(to_char(now() at time ZONE 'Asia/shanghai','yyyy-mm-dd HH24'),'yyyy-mm-dd HH24') as end
					) a
				) c 	
		) d left  join ts_product on d.ts = ts_product.ts
	)
	SELECT ts, coalesce(total,0) total ,'南瓜饼1线' line
	from ts_product_list
	)
	union all 
	(WITH ts_product as(
		select sum(a.weight) total,a.ts,max(line) line from (
			select 
			cast(device_running_record_info->>'count' as DECIMAL) weight,
			"replace"(to_char(created_at at time ZONE 'Asia/shanghai', 'HH24:') ||	cast(date_part('minute',created_at) as integer)/30*30, ':0', ':00') ts,
			work_station->>'lineName' line
			from manufacture.device_running_record
			where 
			company_id =  ?
			and org_id = ?
			and work_station->>'workshopId'='?'
			and work_station->>'lineId' = '?'
			and device_running_record_info->>'deviceType'='CCJ'
			and created_at >to_date(to_char(now(), 'YYYY-MM-DD'),'YYYY-MM-DD')
		) a
		group by a.ts
		order by ts 
	)
	, ts_product_list as (
		select d.ts,ts_product.total,line from (
			select 	to_char(c.ts::timestamp,'HH24:MI') ts from (
				select	generate_series(a.end - interval  '2.5 hour',
				"replace"(to_char(a.end, 'yyyy-mm-dd HH24:') ||	cast(date_part('minute',a.end) as integer)/30*30+30, ':0', ':00')::timestamp,
				'30 minute') ts from (
						select  to_timestamp(to_char(now() at time ZONE 'Asia/shanghai','yyyy-mm-dd HH24'),'yyyy-mm-dd HH24') as end
					) a
				) c 	
		) d left  join ts_product on d.ts = ts_product.ts
	)
	SELECT ts, coalesce(total,0) total ,'南瓜饼2线' line
	from ts_product_list
	)`
	condition := []interface{}{
		companyId, orgId, workshopId, lineIdA,
		companyId, orgId, workshopId, lineIdB,
	}
	result := make([]BoardTimeSectionProductRecord, 0)
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

// 今日昨日历史最佳
type BoardTopShow struct {
	Today     string `json:"today"`
	Yesterday string `json:"yesterday"`
	History   string `json:"history"`
}

// TopShop 今日昨日历史最佳
func (d *BoardShowDao) TopShow(companyId int, orgId int, workshopId int) (*BoardTopShow, error) {
	sqlStr := `with today_product as (
		select 
		(case when product_record_type=1 then  cast(product_record_info->>'weigh' as DECIMAL) 
		ELSE -(cast(product_record_info->>'weigh' as DECIMAL))
		END) as weight,
		cast(product_record_info->>'productPlanId' as INTEGER) plan_id
		from manufacture.product_records
		where company_id = ?
		and org_id = ?
		and work_station->>'workshopId'='?'
		and product_record_type in (1,2)
		and created_at>=to_date(to_char(now(), 'YYYY-MM-DD'),'YYYY-MM-DD')
	 ) ,tmp_today_product as(
	 select 
		(select COALESCE(sum(weight),0) weight from today_product) today,
		(select COALESCE(sum(real),0) from manufacture.workshop_plan_completion_record where 
		company_id =?
		and org_id =?
		and created_at > CURRENT_DATE -1 
		and workshop_id = ?
		limit 1
		) yesterday,
		(select max(real) from manufacture.workshop_plan_completion_record where workshop_id = ?) history
	 )
	 select today,
	 cast(yesterday as DECIMAL) yesterday,
	 (case when today > history then today else history end) history
	 from tmp_today_product`
	condition := []interface{}{
		companyId, orgId, workshopId,
		companyId, orgId, workshopId, workshopId,
	}
	var result BoardTopShow
	tx := d.transactionContext.PgTx
	_, err := tx.QueryOne(&result, sqlStr, condition...)
	return &result, err
}

// 二级品占比
type BoardProportionOfSecondLevel struct {
	Sname string `json:"sname"` //品名
	Rate  string `json:"rate"`  //占比
}

// 二级品占比
func (d *BoardShowDao) ProportionOfSecondLevel(companyId int, orgId int, workshopId int) (
	[]BoardProportionOfSecondLevel, error) {
	sqlStr := `with item_product as (
		select sum(a.weight) item_total,max(sname) sname  from (
		 select cast(product_record_info->>'weigh' as DECIMAL) weight
		 ,work_station->>'sectionName' sname
		 ,work_station->>'workStationId' workStationId
		 from manufacture.product_records
		 where  company_id = ?
		 and org_id = ?
		 and work_station->>'workshopId'='?'
		 and product_record_type = 8
		 and product_record_info->>'approveStatus'='2'
		 and created_at >=to_date(to_char(now(), 'YYYY-MM-DD'),'YYYY-MM-DD') 
		 and  work_station->>'sectionName' in ('制模','成型','穿串','包装')
		) a
		group by a.workStationId
		)
		,item_product_rate as(
		select sname,round(item_total/(select sum(item_total) from item_product)*100, 0)  as rate from item_product
		)
		select b.sname, coalesce(b.rate,0) rate from item_product_rate b`
	condition := []interface{}{
		companyId, orgId, workshopId,
	}
	result := make([]BoardProportionOfSecondLevel, 0)
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

// 工段生产信息
type BoardSectionProductInfo struct {
	Sname             string `json:"sname"`
	ProductWeight     string `json:"productWeight"`
	SecondLevelWeight string `json:"secondLevelWeight"`
	TotalWeigh        string `json:"totalWeigh"`
	Qu                string `json:"qu"`
	Sort              string `json:"sort"` //排名
}

// 工段生产信息
func (d *BoardShowDao) SectionProductInfo(companyId int, orgId int, workshopId int) (
	[]BoardSectionProductInfo, error) {
	sqlStr := `with section_product as(
		select 
		work_station->>'sectionName' section,                                 --工段
		CAST(product_record_info->>'outputWeight' as decimal) product_weigh, --产出量
		CAST(product_record_info->>'secondLevelWeight' as decimal) second_level_weigh,  
		CAST(product_record_info->>'qualificationRate' as decimal) qu
		--二级品
		from manufacture.workshop_product_record  
		where company_id = ?
		and org_id = ? 
		and work_station->>'workshopId'='?' 
		and product_date =to_char(now(), 'YYYY-MM-DD')
		), 
		all_section_product as(
		select b.sname,
		COALESCE(a.product_weigh,0) product_weigh,
		COALESCE(a.second_level_weigh,0) second_level_weigh,
		COALESCE(a.qu,0) qu
		from section_product a  right join (
		select unnest(ARRAY ['制模','成型','穿串','包装']) sname
		) b   on a.section = b.sname 
		),group_section_product as(
		select 
		sname,
		sum(product_weigh) product_weigh,
		sum(second_level_weigh) second_level_weigh
		,sum(product_weigh+second_level_weigh) total_weigh
		from all_section_product
		GROUP BY sname
		)
		select *,
		(case when product_weigh=0 or total_weigh=0 then 0 else round(product_weigh*100/total_weigh,1) end) qu,
		(case when sname = '制模' then 1
		when sname='成型' then 2 
		when sname='穿串' then 3 else  4 end) sort 
		from group_section_product
		order by sort`
	condition := []interface{}{
		companyId, orgId, workshopId,
	}
	result := make([]BoardSectionProductInfo, 0)
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

// 当前计划达成率
func (d *BoardShowDao) WorkshopPlanCompletionCurrent(companyId int, orgId int, workshopId int) (string, error) {
	sqlStr := `select rate rate from manufacture.workshop_plan_completion_record where
	company_id = ?
	and org_id = ?
	and workshop_id=?
	order by created_at desc
	limit 1`
	condition := []interface{}{
		companyId, orgId, workshopId,
	}
	var result string
	tx := d.transactionContext.PgTx
	_, err := tx.QueryOne(pg.Scan(&result), sqlStr, condition...)
	return result, err
}

type PlanCompletion5Day struct {
	Ts    string `jsonL:"ts"`
	Total string `json:"total"`
}

// 近5天 计划达成率
func (d *BoardShowDao) WorkshopPlanCompletion5Day(companyId int, orgId int, workshopId int) ([]PlanCompletion5Day, error) {
	sqlStr := `with ts_product as (
		select rate,
		to_char(created_at,'mm-dd') ts
		from manufacture.workshop_plan_completion_record
		where company_id = ?
		and org_id = ?
		and workshop_id= ?
		and created_at > (now()-interval '6d')
		)
		,ts_product_list as (
		 select d.ts,ts_product.rate from (
			select  to_char(c.ts::timestamp,'mm-dd') ts from (
			 select generate_series(now() - interval  '4 day',now(),'1 day') ts
		 ) c ) d left  join ts_product on d.ts = ts_product.ts
		)
		SELECT ts, coalesce(rate,0) total
		from ts_product_list`
	result := make([]PlanCompletion5Day, 0)
	condition := []interface{}{
		companyId, orgId, workshopId,
	}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err

}

// product_plan 批次产能
func (d *BoardShowDao) ProductPlan(companyId int, orgId int, workshopId int) ([]PlanCompletion5Day, error) {
	sqlStr := `with product_record as (
		select 
		 (case when product_record_type=1 then  cast(product_record_info->>'weigh' as DECIMAL) 
		 ELSE -(cast(product_record_info->>'weigh' as DECIMAL))
		 END) as weight,
		 cast(product_record_info->>'productPlanId' as INTEGER) plan_id
		from manufacture.product_records
		 where company_id =?
		 and org_id = ?
		 and work_station->>'workshopId'='?'
		 and work_station->>'sectionName'='包装'
		 and product_record_type in (1,2)
		 and created_at>to_date(to_char((now() -interval '3 day'), 'YYYY-MM-DD'),'YYYY-MM-DD')  --'2022-01-01' --
	 ), product_record_sum as(
		select sum(weight) weight,plan_id 
		from product_record
		 GROUP BY plan_id
	 ) 
	 select 
	 a.product_plan_id
	 ,batch_number                        --批次号
	 ,plan_product_name                   --品名 
	 ,machine                             --机台 
	 ,coalesce(plan_devoted->>'unit','') unit          --规格 
	 ,cast(coalesce(plan_devoted->>'quantity','0') as DECIMAL) quantity  -- 数量
	 ,cast(coalesce(plan_devoted->>'weight','0')as DECIMAL) plan_weight -- 计划重量
	 , coalesce(b.weight,0)    real_weight            --批次实际产能
	 from manufacture.product_plan  a left join product_record_sum b  on a.product_plan_id = b.plan_id
	 where company_id = ?
	 and org_id = ?
	 and workshop->>'workshopId'='?'
	 and product_date >to_date(to_char(now() -interval '3 day', 'YYYY-MM-DD'),'YYYY-MM-DD') 
	 and cast(coalesce(plan_devoted->>'weight','0')as DECIMAL) >0
	 order by created_at desc`
	result := make([]PlanCompletion5Day, 0)
	condition := []interface{}{
		companyId, orgId, workshopId,
		companyId, orgId, workshopId,
	}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}