-- 创建新表 summary_evaluation
CREATE TABLE public.summary_evaluation (
    id bigserial NOT NULL,
    company_id int8 NULL,
    evaluation_project_id int8 NULL,
    evaluation_project_name text NULL,
    cycle_id int8 NULL,
    cycle_name text NULL,
    target_user jsonb NULL,
    target_department jsonb NULL,
    executor jsonb NULL,
    "types" int8 NULL,
    status text NULL,
    check_result text NULL,
    begin_time timestamptz NULL,
    end_time timestamptz NULL,
    total_score text NULL,
    created_at timestamptz NULL,
    updated_at timestamptz NULL,
    deleted_at timestamptz NULL,
    node_id int8 NULL DEFAULT 0,
    total_rating jsonb NULL,
    CONSTRAINT summary_evaluation_pkey PRIMARY KEY (id)
);

-- 创建新表 summary_evaluation_value
CREATE TABLE public.summary_evaluation_value (
    id bigserial NOT NULL,
    evaluation_item_id int8 NULL,
    summary_evaluation_id int8 NULL,
    value text NULL,
    score text NULL,
    "types" int8 NULL,
    remark text NULL,
    created_at timestamptz NULL,
    updated_at timestamptz NULL,
    weight numeric NULL DEFAULT 0,
    rating jsonb NULL,
    CONSTRAINT summary_evaluation_value_pkey PRIMARY KEY (id)
);

-- 创建新表 evaluation_item_used
CREATE TABLE public.evaluation_item_used (
    id bigserial NOT NULL,
    company_id int8 NULL,
    evaluation_project_id int8 NULL,
    node_id int8 NULL,
    node_type int8 NULL,
    sort_by int8 NULL,
    category text NULL,
    "name" text NULL,
    prompt_title text NULL,
    prompt_text text NULL,
    entry_items jsonb NULL,
    rule_type int8 NULL,
    "rule" jsonb NULL,
    weight numeric NULL DEFAULT 0,
    required int8 NULL,
    created_at timestamptz NULL,
    updated_at timestamptz NULL,
    evaluator_id int8 NULL DEFAULT 0,
    CONSTRAINT evaluation_item_used_pkey PRIMARY KEY (id)
);

-- 创建新表 log_sms
CREATE TABLE public.log_sms (
    id bigserial NOT NULL,
    phone text NULL,
    template_id int8 NULL,
    "template" text NULL,
    value jsonb NULL,
    created_at timestamptz NULL,
    "result" text NULL,
    status text NULL,
    "from" text NULL,
    "index" int8 NULL,
    execute_at timestamptz NULL,
    CONSTRAINT log_sms_pkey PRIMARY KEY (id)
);

-- 变更evaluation_project表字段
ALTER TABLE public.evaluation_project
    ADD summary_state int4 NOT NULL DEFAULT 0;

COMMENT ON COLUMN public.evaluation_project.summary_state IS '周期评估是否下发';

-- 处理evaluation_project表字段summary_state 初始值
UPDATE
    evaluation_project
SET
    summary_state = 1
WHERE
    end_time <= now()
    AND deleted_at ISNULL;

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

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

-- 抽取evaluation_project旧数据到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'