-- ============================================================================
-- (sub_id, scriptid) 去重 + 唯一索引 —— 根治"同一患者同一题堆出多份重复明细"
--                                    南华附一（nhfy）2026-10-21
--
-- 【为什么要做】
--   保存答案/补默认值的 upsert 走 isExistServiceSubtaskDetail，历史上只看 del_flag=0；
--   明细一旦被逻辑删除就查不到 → 下一次保存必然 INSERT，副本越积越多
--   （2026-09-08 那 10 位患者各堆了 2~6 份，答案完全相同）。
--   代码侧已改为「忽略删除标记取最新一行 + 更新后 reviveServiceSubtaskDetail 复活」，
--   本脚本再从数据库层加一道唯一约束兜底。
--
-- 【现状（2026-10-21 实测 smartor_nhfy）】
--   明细总数 346,628；sub_id / scriptid 均无 NULL
--   重复组（同 sub_id+scriptid 多于 1 行）：5,389 组，涉及 12,680 行
--   若"每组只留 1 行"，需要物理删除 7,291 行（绝大多数是 del_flag=1 的历史副本）
--
-- 【执行顺序】建议低峰期，按 0 → 4 顺序执行；第 3 步是物理删除，务必先跑第 2 步备份
-- 【回滚】第 5 步；删掉的行走 bak_ssd_dup_20261021 还原
-- ============================================================================


-- ----------------------------------------------------------------------------
-- 【0】评估（只读，随时可跑）
-- ----------------------------------------------------------------------------
SELECT COUNT(*)                                   AS dup_groups,
       SUM(rows_cnt)                              AS rows_in_dup_groups,
       SUM(rows_cnt - 1)                          AS rows_to_delete_if_keep_one
FROM (SELECT sub_id, scriptid, COUNT(*) AS rows_cnt
      FROM service_subtask_detail
      WHERE sub_id IS NOT NULL AND scriptid IS NOT NULL
      GROUP BY sub_id, scriptid
      HAVING COUNT(*) > 1) x;

-- 重复最多的 10 组，看看是不是都长一个样
SELECT sub_id, scriptid, COUNT(*) AS rows_cnt,
       SUM(del_flag = 0) AS live_cnt,
       COUNT(DISTINCT COALESCE(asrtext, '')) AS distinct_answers
FROM service_subtask_detail
GROUP BY sub_id, scriptid
HAVING COUNT(*) > 1
ORDER BY rows_cnt DESC
LIMIT 10;


-- ----------------------------------------------------------------------------
-- 【1】备份"将被删除"的行（约 7,291 行）
--      保留规则：同一 (sub_id, scriptid) 保留 1 行 —— 优先 del_flag=0，其次 id 最大
-- ----------------------------------------------------------------------------
-- DROP TABLE IF EXISTS bak_ssd_dup_20261021;
-- CREATE TABLE bak_ssd_dup_20261021 AS
-- SELECT d.*
-- FROM service_subtask_detail d
-- JOIN (SELECT sub_id, scriptid,
--              SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY (del_flag = 0) DESC, id DESC), ',', 1) AS keep_id
--       FROM service_subtask_detail
--       WHERE sub_id IS NOT NULL AND scriptid IS NOT NULL
--       GROUP BY sub_id, scriptid
--       HAVING COUNT(*) > 1) k
--   ON k.sub_id = d.sub_id AND k.scriptid = d.scriptid
-- WHERE d.id <> k.keep_id;
-- SELECT COUNT(*) FROM bak_ssd_dup_20261021;   -- 预期 ≈ 7291


-- ----------------------------------------------------------------------------
-- 【2】备份自检（确认备份表里没有"每组都应该保留的那一行"）
-- ----------------------------------------------------------------------------
-- SELECT COUNT(*) AS wrong_rows
-- FROM bak_ssd_dup_20261021 b
-- JOIN (SELECT sub_id, scriptid,
--              SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY (del_flag = 0) DESC, id DESC), ',', 1) AS keep_id
--       FROM service_subtask_detail
--       GROUP BY sub_id, scriptid) k
--   ON k.sub_id = b.sub_id AND k.scriptid = b.scriptid
-- WHERE b.id = k.keep_id;   -- 预期 0


-- ----------------------------------------------------------------------------
-- 【3】物理删除重复行（每组保留 1 行）
-- ----------------------------------------------------------------------------
-- DELETE d
-- FROM service_subtask_detail d
-- JOIN (SELECT sub_id, scriptid,
--              SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY (del_flag = 0) DESC, id DESC), ',', 1) AS keep_id
--       FROM service_subtask_detail
--       WHERE sub_id IS NOT NULL AND scriptid IS NOT NULL
--       GROUP BY sub_id, scriptid
--       HAVING COUNT(*) > 1) k
--   ON k.sub_id = d.sub_id AND k.scriptid = d.scriptid
-- WHERE d.id <> k.keep_id;


-- ----------------------------------------------------------------------------
-- 【4】加唯一索引
--      ⚠️ 前提：第 3 步已跑过（否则必然 ORA/1062 失败）
--      ⚠️ 346k 行的 ADD UNIQUE 会短时锁表，建议低峰执行；
--         如果生产版本 MySQL 支持 online DDL，可用 ALGORITHM=INPLACE, LOCK=NONE
-- ----------------------------------------------------------------------------
-- ALTER TABLE service_subtask_detail
--     ADD UNIQUE KEY uk_ssd_sub_script (sub_id, scriptid),
--     ALGORITHM = INPLACE, LOCK = NONE;

-- 校验
-- SHOW INDEX FROM service_subtask_detail WHERE Key_name = 'uk_ssd_sub_script';
-- SELECT COUNT(*) AS dup_groups_should_be_0 FROM (
--   SELECT sub_id, scriptid FROM service_subtask_detail
--   GROUP BY sub_id, scriptid HAVING COUNT(*) > 1) x;


-- ----------------------------------------------------------------------------
-- 【5】回滚
-- ----------------------------------------------------------------------------
-- ALTER TABLE service_subtask_detail DROP INDEX uk_ssd_sub_script;
-- INSERT INTO service_subtask_detail SELECT * FROM bak_ssd_dup_20261021;
-- DROP TABLE bak_ssd_dup_20261021;


-- ============================================================================
-- 【加索引之后必须注意】
--   唯一索引生效后，并发保存同一 (sub_id, scriptid) 时"后到的那一条 INSERT"会抛
--   DuplicateKeyException。代码侧已经做了两件事降低风险：
--     ① isExistServiceSubtaskDetail 改为"忽略 del_flag 取最新一行"→ 正常保存走 UPDATE，不会 INSERT；
--     ② /smartor/subtaskAnswer/saveQuestionAnswer 加了 Redis 幂等占位（60 秒，同一 pid+tid+subid 只处理一次）。
--   但如果将来还有别的写入路径直接 INSERT 明细，建议在插入处 catch DuplicateKeyException 后转 UPDATE。
-- ============================================================================