-- ============================================================================
-- 把「问卷库模板」题目的选项默认值（svy_lib_template_targetoption.default_value）
-- 同步到「问卷任务模板」题目选项（svy_task_template_targetoption.default_value）
--
-- 表名说明（问题选项的默认值其实不在 script 表里，而在 option 表里）：
--   库   题目 svy_lib_template_script  ── id ← scriptid ── 库   选项 svy_lib_template_targetoption
--   任务 题目 svy_task_template_script ── id ← scriptid ── 任务 选项 svy_task_template_targetoption
--   default_value 语义：1=否（默认值），2=是（默认选项），NULL=未设置
--
-- 任务模板 → 库模板 的映射链（实测 smartor_nhfy）：
--   svy_task_template_script.templateid
--     = svy_task_template.id
--     → svy_task_template.templateid = svy_lib_template.svyid
--     → svy_lib_template_script.svyid，题面 script_content 相同即同一道题
--     → svy_lib_template_targetoption.scriptid = svy_lib_template_script.id
--        且 optioncontent 相同即同一个选项
--
-- 实测覆盖（2026-11-06，smartor_nhfy）：
--   任务模板 710 个，能映射到库模板的 663 个（另外 47 个模板 / 879 个选项没有库来源，
--   本脚本不会动它们，需要人工处理）；
--   任务题目 6409 道，按题面能对上库题目 6353 道（99.1%）；
--   任务选项 16405 个，能对上库选项 16369 个（99.8%）。
--
-- 三个方案，按需要只跑其中一个：
--   方案A（推荐）：只把 lib 里标了默认(=2)的选项推到 task，不动 task 已有的其它值
--                  → 实测影响 34 行
--   方案B（完全对齐）：lib=2 → 2，lib=1/NULL → 1（不写 NULL）
--                  → 实测影响 86 行（额外 52 行是把 task 里有、lib 里没有的默认去掉）
--   方案C（严格镜像）：连 NULL 一起照抄
--                  → 实测影响 10867 行，其中 10782 行会被置成 NULL，通常不建议
--
-- 执行前请先跑第 0 步备份、第 1 步预检。
-- ============================================================================


-- ---------------------------------------------------------------------------
-- 0. 备份（只备份会被改动的那几列，整表太大没必要）
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bak_svy_task_targetoption_dv_20261106 AS
SELECT id,
       taskid,
       templateID,
       scriptid,
       optioncontent,
       default_value
FROM svy_task_template_targetoption
WHERE del_flag = '0';

-- 行数应与下面一致（实测 17403 行）
SELECT COUNT(*) AS bak_rows FROM bak_svy_task_targetoption_dv_20261106;

-- 变更前分布（实测：1=16397，2=1005，4=1）
SELECT default_value, COUNT(*) AS cnt
FROM svy_task_template_targetoption
WHERE del_flag = '0'
GROUP BY default_value
ORDER BY cnt DESC;


-- ---------------------------------------------------------------------------
-- 1. 预检：看看 lib 的默认值同步到 task 会改多少行、改成什么
-- ---------------------------------------------------------------------------
-- 1.1 汇总（按方案A/B/C 分别预估）
SELECT '方案A 只推 lib=2' AS plan,
       SUM(ld.dv = 2 AND COALESCE(o.default_value, 0) <> 2) AS will_change
FROM svy_task_template_targetoption o
         JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
         JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
         JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
         JOIN (
    SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
    FROM svy_lib_template_script l
             JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
    WHERE l.del_flag = '0'
    GROUP BY l.svyid, l.script_content, lo.optioncontent
) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
WHERE o.del_flag = '0'
UNION ALL
SELECT '方案B 1/2 完全对齐',
       SUM(COALESCE(o.default_value, 1) <> (CASE WHEN ld.dv = 2 THEN 2 ELSE 1 END))
FROM svy_task_template_targetoption o
         JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
         JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
         JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
         JOIN (
    SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
    FROM svy_lib_template_script l
             JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
    WHERE l.del_flag = '0'
    GROUP BY l.svyid, l.script_content, lo.optioncontent
) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
WHERE o.del_flag = '0'
UNION ALL
SELECT '方案C 严格镜像(含NULL)',
       SUM(COALESCE(o.default_value, -1) <> COALESCE(ld.dv, -1))
FROM svy_task_template_targetoption o
         JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
         JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
         JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
         JOIN (
    SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
    FROM svy_lib_template_script l
             JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
    WHERE l.del_flag = '0'
    GROUP BY l.svyid, l.script_content, lo.optioncontent
) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
WHERE o.del_flag = '0';

-- 1.2 逐行差异清单（改动前先看一眼，确认没有不该改的）
SELECT o.id AS task_option_id,
       o.templateid AS task_template,
       tt.svyname,
       LEFT(s.script_content, 30) AS question,
       o.optioncontent,
       o.default_value AS task_now,
       ld.dv AS lib_value
FROM svy_task_template_targetoption o
         JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
         JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
         JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
         JOIN (
    SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
    FROM svy_lib_template_script l
             JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
    WHERE l.del_flag = '0'
    GROUP BY l.svyid, l.script_content, lo.optioncontent
) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
WHERE o.del_flag = '0'
  AND COALESCE(o.default_value, 1) <> (CASE WHEN ld.dv = 2 THEN 2 ELSE 1 END)
ORDER BY o.templateid, s.id, o.id;

-- 1.3 顺带看看哪些 task 选项在库里找不到（题面被改过/新加选项），这些不会被同步
SELECT COUNT(*) AS unmapped_option_rows
FROM svy_task_template_targetoption o
         JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
         JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
         JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
         LEFT JOIN svy_lib_template_script l
                   ON l.svyid = lt.svyid AND l.script_content = s.script_content AND l.del_flag = '0'
         LEFT JOIN svy_lib_template_targetoption lo
                   ON lo.scriptid = l.id AND lo.del_flag = '0' AND lo.optioncontent = o.optioncontent
WHERE o.del_flag = '0'
  AND lo.id IS NULL;


-- ---------------------------------------------------------------------------
-- 2. 方案A（推荐）：把库里的默认选项(=2)同步到任务模板选项
--    实测影响 34 行；不动 task 里已有的 1 / NULL，避免误删任务侧单独配过的默认项
--    （MySQL 多表 UPDATE 不能带 ORDER BY / LIMIT，下面的条件已经能定位到行）
-- ---------------------------------------------------------------------------
UPDATE svy_task_template_targetoption o
    JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
    JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
    JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
    JOIN (
        SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
        FROM svy_lib_template_script l
                 JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
        WHERE l.del_flag = '0'
        GROUP BY l.svyid, l.script_content, lo.optioncontent
    ) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
SET o.default_value = 2
WHERE o.del_flag = '0'
  AND ld.dv = 2
  AND COALESCE(o.default_value, 0) <> 2;


-- ---------------------------------------------------------------------------
-- 3. 方案B：和库完全对齐（lib=2 → 2，lib=1/NULL → 1），实测影响 86 行
--    注意：会把 task 里「有默认、但库里没标默认」的 52 行改成 1
-- ---------------------------------------------------------------------------
-- UPDATE svy_task_template_targetoption o
--     JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
--     JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
--     JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
--     JOIN (
--         SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
--         FROM svy_lib_template_script l
--                  JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
--         WHERE l.del_flag = '0'
--         GROUP BY l.svyid, l.script_content, lo.optioncontent
--     ) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
-- SET o.default_value = CASE WHEN ld.dv = 2 THEN 2 ELSE 1 END
-- WHERE o.del_flag = '0'
--   AND COALESCE(o.default_value, 1) <> (CASE WHEN ld.dv = 2 THEN 2 ELSE 1 END);


-- ---------------------------------------------------------------------------
-- 4. 方案C：严格镜像库里的值（连 NULL 一起照抄），实测影响 10867 行，
--    其中 10782 行会被置成 NULL（库里这些选项本身没设默认值）。一般不建议。
-- ---------------------------------------------------------------------------
-- UPDATE svy_task_template_targetoption o
--     JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
--     JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
--     JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
--     JOIN (
--         SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
--         FROM svy_lib_template_script l
--                  JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
--         WHERE l.del_flag = '0'
--         GROUP BY l.svyid, l.script_content, lo.optioncontent
--     ) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
-- SET o.default_value = ld.dv
-- WHERE o.del_flag = '0'
--   AND COALESCE(o.default_value, -1) <> COALESCE(ld.dv, -1);


-- ---------------------------------------------------------------------------
-- 5. 收窄范围示例（只想动某几个任务模板时，在 WHERE 里加条件即可）
--    任务模板 id 来自 svy_task_template.id（= svy_task_template_script.templateid）
-- ---------------------------------------------------------------------------
-- 5.1 只同步任务模板 514（全院科满意度；实测已一致，改 0 行）
-- UPDATE svy_task_template_targetoption o
--     JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
--     JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
--     JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
--     JOIN (
--         SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
--         FROM svy_lib_template_script l
--                  JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
--         WHERE l.del_flag = '0'
--         GROUP BY l.svyid, l.script_content, lo.optioncontent
--     ) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
-- SET o.default_value = 2
-- WHERE o.del_flag = '0'
--   AND ld.dv = 2
--   AND COALESCE(o.default_value, 0) <> 2
--   AND s.templateid IN (514);

-- 5.2 只同步某个机构下的任务模板（orgid 在两个 option 表上都有）
--   AND o.orgid = ' xxx '


-- ---------------------------------------------------------------------------
-- 6. 校验
-- ---------------------------------------------------------------------------
-- 6.1 改动后分布（方案A 之后应为：1=16363，2=1039，4=1）
SELECT default_value, COUNT(*) AS cnt
FROM svy_task_template_targetoption
WHERE del_flag = '0'
GROUP BY default_value
ORDER BY cnt DESC;

-- 6.2 仍与库不一致的行数（方案A 之后应为 10833，差的都是 lib 侧未设置/为1 的行）
SELECT COUNT(*) AS still_diff
FROM svy_task_template_targetoption o
         JOIN svy_task_template_script s ON s.id = o.scriptid AND s.del_flag = '0'
         JOIN svy_task_template tt ON tt.id = s.templateid AND tt.del_flag = '0'
         JOIN svy_lib_template lt ON lt.svyid = tt.templateid AND lt.del_flag = '0'
         JOIN (
    SELECT l.svyid, l.script_content, lo.optioncontent, MAX(lo.default_value) AS dv
    FROM svy_lib_template_script l
             JOIN svy_lib_template_targetoption lo ON lo.scriptid = l.id AND lo.del_flag = '0'
    WHERE l.del_flag = '0'
    GROUP BY l.svyid, l.script_content, lo.optioncontent
) ld ON ld.svyid = lt.svyid AND ld.script_content = s.script_content AND ld.optioncontent = o.optioncontent
WHERE o.del_flag = '0'
  AND COALESCE(o.default_value, 0) <> COALESCE(ld.dv, 0);

-- 6.3 每题「默认选项的个数」分布：正常应最多 1 个。
--     实测跑完方案A 后：0 个=5624 题，1 个=1017 题，2 个=8 题，3 个=2 题
--     （库里本身就有 1 道题挂了 2 个默认项；带 2/3 个默认项的题目属于同步前就存在的问题）
SELECT c AS default_options_per_question, COUNT(*) AS questions
FROM (SELECT s.id, SUM(o.default_value = 2) c
      FROM svy_task_template_script s
               JOIN svy_task_template_targetoption o ON o.scriptid = s.id AND o.del_flag = '0'
      WHERE s.del_flag = '0'
      GROUP BY s.id) x
GROUP BY c
ORDER BY c;


-- ---------------------------------------------------------------------------
-- 7. 回滚（只把被改过的那几列写回备份值）
-- ---------------------------------------------------------------------------
-- UPDATE svy_task_template_targetoption o
--     JOIN bak_svy_task_targetoption_dv_20261106 b ON b.id = o.id
-- SET o.default_value = b.default_value
-- WHERE o.del_flag = '0';
--
-- 确认没问题后再删备份表：
-- DROP TABLE bak_svy_task_targetoption_dv_20261106;