-- 调整 summary_evaluation_value 数据
WITH t1 AS (
    SELECT
        summary_evaluation_value.evaluation_item_id,
        summary_evaluation_value.value,
        summary_evaluation_value.id AS value_id,
        summary_evaluation_value.score,
        summary_evaluation.company_id
    FROM
        summary_evaluation_value
        JOIN summary_evaluation ON summary_evaluation_value.summary_evaluation_id = summary_evaluation.id
        JOIN evaluation_item_used ON evaluation_item_used.id = summary_evaluation_value.evaluation_item_id
    WHERE
        summary_evaluation_value.value = summary_evaluation_value.score
        AND summary_evaluation."types" = 1
        AND evaluation_item_used.weight > 0
    ORDER BY
        summary_evaluation_value.created_at
),
t2 AS (
    SELECT
        t1.value_id,
        cast(evaluation_item_used.weight AS float) AS weight,
    cast(t1.value AS float) AS value,
    cast(t1.value AS float) * cast(evaluation_item_used.weight AS float) AS real_value
FROM
    evaluation_item_used
    JOIN t1 ON t1.evaluation_item_id = evaluation_item_used.id)
UPDATE
    summary_evaluation_value
SET
    score = t2.real_value
FROM
    t2
WHERE
    summary_evaluation_value.id = t2.value_id;

-- 调整summary_evaluation 数据
WITH t1 AS (
    SELECT
        sum(cast(summary_evaluation_value.score AS float)) AS total_score,
        summary_evaluation_value.summary_evaluation_id
    FROM
        summary_evaluation_value
        JOIN summary_evaluation ON summary_evaluation_value.summary_evaluation_id = summary_evaluation.id
        JOIN evaluation_item_used ON evaluation_item_used.id = summary_evaluation_value.evaluation_item_id
    WHERE
        summary_evaluation."types" = 1
        AND evaluation_item_used.weight > 0
    GROUP BY
        summary_evaluation_value.summary_evaluation_id)
UPDATE
    summary_evaluation
SET
    total_score = t1.total_score
FROM
    t1
WHERE
    summary_evaluation.id = t1.summary_evaluation_id