-- 变更user表字段
ALTER TABLE "public"."user"
    ADD COLUMN "parent_id" int8 DEFAULT 0;

COMMENT ON COLUMN "public"."user"."parent_id" IS '上级ID';

-- 抽取旧数据到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
    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)
    INSERT INTO public.evaluation_item_used (company_id, evaluation_project_id, node_id, node_type, sort_by, category, "name", prompt_title, prompt_text, entry_items, rule_type, "rule", weight, required, created_at, updated_at, evaluator_id)
    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,
    0,
    t2.node_contents ->> 'category' AS "category",
    t2.node_contents ->> 'name' AS "name",
    t2.node_contents ->> 'promptTitle' AS "prompt_title",
    t2.node_contents ->> 'promptText' AS "prompt_text",
    t2.node_contents -> 'entryItems' AS "entry_items",
    cast(t2.node_contents #>> '{rule,type}' AS int8) AS rule_type,
    t2.node_contents -> 'rule' AS "rule",
    cast(t2.node_contents ->> 'weight' AS decimal) AS "weight",
    cast(t2.node_contents ->> 'required' AS int8) AS "required",
    now(),
    now(),
    cast(t2.node_contents ->> 'evaluatorId' AS int8) AS "evaluator_id"
FROM
    t2;

-- evaluation_item_used 初始赋值 sort_by
UPDATE
    evaluation_item_used
SET
    sort_by = evaluation_item_used.id;

-- evaluation_item_used 修正required 的值
UPDATE
    evaluation_item_used
SET
    required = 1
WHERE
    required = 0
    OR required ISNULL;

--  evaluation_item_used修正weight的 值
UPDATE
    evaluation_item_used
SET
    weight = 0
WHERE
    weight ISNULL;

-- evaluation_item_used 修正rule的值
UPDATE
    evaluation_item_used
SET
    "rule" = '{}'
WHERE
    "rule" = 'null'