-- 数据修复
-- 需备份 evaluation_item_used 表数据

WITH t1 AS (
    SELECT
        evaluation_project.company_id,
        evaluation_project.id AS project_id,
        jsonb_array_elements(evaluation_project."template" #> '{linkNodes}') AS nodes
    FROM
        evaluation_project
			WHERE evaluation_project."template" #> '{linkNodes}'<>'null'
    ORDER BY
        id
),
t2 AS (
    SELECT
        t1.company_id,
        t1.project_id,
        t1.nodes ->> 'id' AS node_id,
        t1.nodes ->> 'type' AS node_type,
        jsonb_array_elements(t1.nodes #> '{nodeContents}') AS node_contents
    FROM
        t1
		WHERE t1.nodes #> '{nodeContents}'<>'null'
	),
t3 AS(
    SELECT
			t2.company_id,
			t2.project_id,
			cast(t2.node_id AS int8) AS node_id,
			cast(t2.node_type AS int8) AS node_type,
			t2.node_contents ->> 'category' AS "category",
			t2.node_contents ->> 'name' AS "name",
			cast(t2.node_contents ->> 'ruleId' AS int8) AS "rule_id"
	FROM
			t2
),
t4 as (
	 SELECT
			t3.project_id,
			t3.node_type,
			t3."category",
			t3."name",
			row_to_json( "evaluation_rule".*) as "rule",
			"evaluation_rule"."type" as rule_type
	FROM t3
	join "evaluation_rule" on t3.rule_id= "evaluation_rule".id
),
t5 as (
  SELECT  
		evaluation_item_used."id",
		evaluation_item_used.evaluation_project_id,
		evaluation_item_used.node_type,
		evaluation_item_used.category,
		evaluation_item_used."name"
	FROM evaluation_item_used 
	WHERE "rule"='{}' or "rule" ISNULL
),
t6 as (
	SELECT t5."id" ,
	t4."rule",
	t4.rule_type
	FROM t5 ,t4
	WHERE t5.evaluation_project_id=t4.project_id
	and t5.node_type=t4.node_type
	and t5.category=t4.category
	and t5."name"=t4."name"
)
update evaluation_item_used 
set "rule"=t6."rule",
rule_type=t6."rule_type"
FROM t6
WHERE evaluation_item_used."id"=t6."id"
;

-- 修复数据

with t1 as 
( SELECT 
evaluation_rule.id::TEXT,
evaluation_rule."name",
evaluation_rule.remark,
evaluation_rule.company_id::TEXT as "companyId",
evaluation_rule.creator_id::TEXT as "creatorId",
evaluation_rule."type",
evaluation_rule.rating,
evaluation_rule.score, 
evaluation_rule.sys_type as "sysType"
FROM evaluation_rule
),
t2 as (
SELECT t1.id, row_to_json(t1.*) as row_j FROM t1
),
t3 as (
SELECT evaluation_item_used.id, t2.row_j
FROM evaluation_item_used
join  t2 on t2.id= evaluation_item_used."rule"->>'id'
WHERE evaluation_item_used."rule"->'companyId' ISNULL 
and evaluation_item_used."rule"->'id' is not null
)
update evaluation_item_used 
SET "rule"=t3.row_j
FROM t3
WHERE evaluation_item_used.id = t3.id
;
-- 修改开始结束时间
UPDATE summary_evaluation 
set begin_time =  '2023-03-31 23:59:00+08',
end_time =  '2023-04-02 23:59:00+08'
WHERE id=36
;

UPDATE summary_evaluation 
set begin_time =  '2023-04-02 23:59:00+08',
end_time =  '2023-04-04 23:59:00+08'
WHERE id=37

-- 修改数据summary_evaluation 表数据
with t1 as (
SELECT summary_evaluation.id,summary_evaluation.evaluation_project_id
FROM summary_evaluation 
WHERE to_char( summary_evaluation.begin_time, 'YYYY-MM-DD') ='2023-03-31'
and to_char( summary_evaluation.end_time, 'YYYY-MM-DD') ='2023-04-02'
and company_id=1
)
update summary_evaluation 
set end_time=end_time+'1 day'
FROM t1
WHERE summary_evaluation.evaluation_project_id=t1.evaluation_project_id