-- ============================================================================
-- pat_med_outhosp 系列表按 serialnum 加索引（配合门急诊按天全量采集）
--
-- 为什么要加：
--   1) 采集任务每个半小时把当天上万个诊疗记录整轮拉一遍，靠"整页批量查重"过滤已入库的，
--      查重 SQL 是 where serialnum in (500个)。没有索引就是全表扫描，季度分表几百万行时一次就很慢；
--   2) 唯一索引是"不重复"的最后一道防线：极端并发、断点回退、分页偏移都不会插出重复记录。
--
-- 执行顺序：
--   第 1 步  看清有哪些表、现在有没有索引
--   第 2 步  查重复数据（有重复的话第 3 步加唯一索引会失败，先处理重复）
--   第 3 步  加唯一索引 uk_serialnum（脚本会自动跳过已有唯一索引的表）
--   第 4 步  校验结果
--
-- 注意：ADD INDEX 走 online DDL（ALGORITHM=INPLACE, LOCK=NONE），不阻塞读写，
--       但大表仍会消耗 IO，建议放在业务低峰执行。数据量大时分表一张一张来也可以。
--       如果某张表报错，先单独执行第 2 步看它有没有重复数据。
-- ============================================================================


-- ----------------------------------------------------------------------------
-- 第 1 步：看有哪些 pat_med_outhosp 表，以及 serialnum 上的索引情况
--   has_unique_index = 1 表示已经有唯一索引（不用再加）
--   has_any_index    = 0 表示完全没索引（查询会很慢，必须加）
-- ----------------------------------------------------------------------------
SELECT t.TABLE_NAME,
       t.TABLE_ROWS,
       (SELECT COUNT(*)
          FROM information_schema.STATISTICS s
         WHERE s.TABLE_SCHEMA = t.TABLE_SCHEMA
           AND s.TABLE_NAME = t.TABLE_NAME
           AND s.COLUMN_NAME = 'serialnum'
           AND s.NON_UNIQUE = 0) AS has_unique_index,
       (SELECT COUNT(*)
          FROM information_schema.STATISTICS s
         WHERE s.TABLE_SCHEMA = t.TABLE_SCHEMA
           AND s.TABLE_NAME = t.TABLE_NAME
           AND s.COLUMN_NAME = 'serialnum') AS has_any_index
FROM information_schema.TABLES t
WHERE t.TABLE_SCHEMA = DATABASE()
  AND (t.TABLE_NAME = 'pat_med_outhosp' OR t.TABLE_NAME REGEXP '^pat_med_outhosp_[0-9]{4,8}$')
ORDER BY t.TABLE_NAME;


-- ----------------------------------------------------------------------------
-- 第 2 步：查重复的 serialnum（有重复就必须先清理，否则唯一索引建不上）
--   执行后先看 dup_serialnum_total，等于 0 就可以直接进第 3 步
-- ----------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS check_outhosp_serialnum_dup;
DELIMITER $$
CREATE PROCEDURE check_outhosp_serialnum_dup()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE tbl VARCHAR(64);
    DECLARE cur CURSOR FOR
        SELECT TABLE_NAME
          FROM information_schema.TABLES
         WHERE TABLE_SCHEMA = DATABASE()
           AND (TABLE_NAME = 'pat_med_outhosp' OR TABLE_NAME REGEXP '^pat_med_outhosp_[0-9]{4,8}$');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    DROP TEMPORARY TABLE IF EXISTS tmp_dup_serialnum;
    CREATE TEMPORARY TABLE tmp_dup_serialnum
    (
        table_name VARCHAR(64),
        serialnum  VARCHAR(64),
        cnt        INT
    );

    OPEN cur;
    read_loop:
    LOOP
        FETCH cur INTO tbl;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @s = CONCAT('INSERT INTO tmp_dup_serialnum SELECT ''', tbl,
                        ''', serialnum, COUNT(*) FROM `', tbl,
                        '` WHERE serialnum IS NOT NULL AND serialnum <> ''''',
                        ' GROUP BY serialnum HAVING COUNT(*) > 1');
        PREPARE st FROM @s;
        EXECUTE st;
        DEALLOCATE PREPARE st;
    END LOOP;
    CLOSE cur;

    SELECT * FROM tmp_dup_serialnum ORDER BY cnt DESC LIMIT 200;
    SELECT COUNT(*) AS dup_serialnum_total FROM tmp_dup_serialnum;
END$$
DELIMITER ;

CALL check_outhosp_serialnum_dup();
-- 查完可以删掉这个过程：DROP PROCEDURE IF EXISTS check_outhosp_serialnum_dup;

-- 如果有重复，按下面的思路清理（保留 id 最小的那条，先备份再删）：
--   CREATE TABLE bak_dup_<表名>_<日期> AS
--   SELECT * FROM `pat_med_outhosp_20261012`
--    WHERE serialnum IN ('重复的serialnum1','重复的serialnum2');
--   DELETE t1 FROM `pat_med_outhosp_20261012` t1
--     JOIN `pat_med_outhosp_20261012` t2
--       ON t1.serialnum = t2.serialnum AND t1.id > t2.id
--    WHERE t1.serialnum IN ('重复的serialnum1','重复的serialnum2');


-- ----------------------------------------------------------------------------
-- 第 3 步：给所有 pat_med_outhosp 表加唯一索引 uk_serialnum
--   已有唯一索引的表自动跳过；如果之前建过同名的普通索引 idx_serialnum，会先删掉再建唯一的
-- ----------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS add_outhosp_serialnum_index;
DELIMITER $$
CREATE PROCEDURE add_outhosp_serialnum_index()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE tbl VARCHAR(64);
    DECLARE has_unique INT DEFAULT 0;
    DECLARE has_normal INT DEFAULT 0;
    DECLARE cur CURSOR FOR
        SELECT TABLE_NAME
          FROM information_schema.TABLES
         WHERE TABLE_SCHEMA = DATABASE()
           AND (TABLE_NAME = 'pat_med_outhosp' OR TABLE_NAME REGEXP '^pat_med_outhosp_[0-9]{4,8}$');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    add_loop:
    LOOP
        FETCH cur INTO tbl;
        IF done THEN
            LEAVE add_loop;
        END IF;

        SELECT COUNT(*) INTO has_unique
          FROM information_schema.STATISTICS
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl
           AND COLUMN_NAME = 'serialnum' AND NON_UNIQUE = 0;

        IF has_unique > 0 THEN
            SELECT CONCAT('[跳过] ', tbl, ' 已有 serialnum 唯一索引') AS msg;
        ELSE
            -- 先清掉可能存在的非唯一索引（保留唯一索引一个就够）
            SELECT COUNT(*) INTO has_normal
              FROM information_schema.STATISTICS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl
               AND COLUMN_NAME = 'serialnum' AND NON_UNIQUE = 1 AND INDEX_NAME = 'idx_serialnum';
            IF has_normal > 0 THEN
                SET @s = CONCAT('ALTER TABLE `', tbl, '` DROP INDEX idx_serialnum');
                PREPARE st FROM @s;
                EXECUTE st;
                DEALLOCATE PREPARE st;
            END IF;

            SET @s = CONCAT('ALTER TABLE `', tbl, '` ADD UNIQUE INDEX uk_serialnum (serialnum)');
            PREPARE st FROM @s;
            EXECUTE st;
            DEALLOCATE PREPARE st;
            SELECT CONCAT('[完成] ', tbl, ' 已添加唯一索引 uk_serialnum') AS msg;
        END IF;
    END LOOP;
    CLOSE cur;
END$$
DELIMITER ;

CALL add_outhosp_serialnum_index();
-- 完成后删掉过程：DROP PROCEDURE IF EXISTS add_outhosp_serialnum_index;

-- 如果某张表报 "Duplicate entry ... for key 'uk_serialnum'"，说明它还有重复数据，
-- 回到第 2 步清理该表后，单独执行：
--   ALTER TABLE `pat_med_outhosp_20261012` ADD UNIQUE INDEX uk_serialnum (serialnum);


-- ----------------------------------------------------------------------------
-- 第 4 步：校验（每张表都应该 has_unique_index = 1）
-- ----------------------------------------------------------------------------
SELECT t.TABLE_NAME,
       (SELECT COUNT(*)
          FROM information_schema.STATISTICS s
         WHERE s.TABLE_SCHEMA = t.TABLE_SCHEMA
           AND s.TABLE_NAME = t.TABLE_NAME
           AND s.COLUMN_NAME = 'serialnum'
           AND s.NON_UNIQUE = 0) AS has_unique_index
FROM information_schema.TABLES t
WHERE t.TABLE_SCHEMA = DATABASE()
  AND (t.TABLE_NAME = 'pat_med_outhosp' OR t.TABLE_NAME REGEXP '^pat_med_outhosp_[0-9]{4,8}$')
ORDER BY t.TABLE_NAME;