-- sp_generic_shift_dates：按指定时间窗口平移库内日期类字段
-- 修复点：
-- 1) 唯一索引包含日期列的表（如 hrm_attendance_record.uk_user_date）改为整表统一平移，
--    常量偏移不会破坏唯一键，避免 1062 Duplicate entry
-- 2) 单表执行失败不再中断整轮，错误记入预览表后继续处理后续表

DROP PROCEDURE IF EXISTS `sp_generic_shift_dates`;

DELIMITER $$

CREATE DEFINER = `root`@`%` PROCEDURE `sp_generic_shift_dates`(
    IN p_schema VARCHAR(64),
    IN p_start DATETIME,
    IN p_end DATETIME,
    IN p_execute BOOLEAN,
    IN p_exclude_regex VARCHAR(1000),
    IN p_include_regex VARCHAR(1000)
)
BEGIN
    DECLARE v_done BOOL DEFAULT FALSE;
    DECLARE v_table VARCHAR(64);
    DECLARE v_pk VARCHAR(64);
    DECLARE v_pk_count INT;
    DECLARE v_cols LONGTEXT;
    DECLARE v_anchor VARCHAR(64);
    DECLARE v_sql LONGTEXT;
    DECLARE v_seconds BIGINT;
    DECLARE v_count BIGINT DEFAULT 0;
    DECLARE v_tables BIGINT DEFAULT 0;
    DECLARE v_fields INT;
    DECLARE v_uniq_hit INT;
    DECLARE v_uniq_cols VARCHAR(1000);
    DECLARE v_min_expr LONGTEXT;
    DECLARE v_max_expr LONGTEXT;
    DECLARE v_regex VARCHAR(1000);
    DECLARE v_err TEXT;
    DECLARE cur CURSOR FOR
        SELECT t.TABLE_NAME
        FROM information_schema.TABLES t
        WHERE t.TABLE_SCHEMA = p_schema
          AND t.TABLE_TYPE = 'BASE TABLE'
          AND (p_exclude_regex IS NULL OR TRIM(p_exclude_regex) = ''
               OR NOT REGEXP_LIKE(t.TABLE_NAME, p_exclude_regex, 'i'))
        ORDER BY t.TABLE_NAME;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    IF p_schema IS NULL OR p_schema = '' OR p_schema IN ('mysql','information_schema','performance_schema','sys') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid or system schema';
    END IF;
    IF p_start IS NULL OR p_end IS NULL OR p_end < p_start THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid date range';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = p_schema) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Schema not found';
    END IF;

    -- 列清单可能很长，避免 GROUP_CONCAT 被默认 1024 字节截断
    SET SESSION group_concat_max_len = 1048576;

    SET v_seconds = TIMESTAMPDIFF(SECOND, p_start, p_end);
    -- 空串不是 NULL，必须显式归一化：MySQL 8 的 REGEXP_LIKE 遇到空模式会抛 3685
    IF p_include_regex IS NULL OR TRIM(p_include_regex) = '' THEN
        SET v_regex = '(^|_)(create|created|update|updated|modify|modified|order|production|produce|receipt|receive|delivery|ship|stock|inbound|outbound|start|end|finish|complete|plan|actual|inspect|check|approve|audit|pay|payment|due|expire|expiry|effective|business|biz|date|time|timestamp)(_|$)';
    ELSE
        SET v_regex = p_include_regex;
    END IF;

    DROP TEMPORARY TABLE IF EXISTS tmp_date_shift_preview;
    CREATE TEMPORARY TABLE tmp_date_shift_preview (
        table_name VARCHAR(64),
        anchor_column VARCHAR(64),
        shift_mode VARCHAR(16),
        field_count INT,
        affected_rows BIGINT,
        statement_text LONGTEXT
    );

    OPEN cur;
    loop_tables: LOOP
        FETCH cur INTO v_table;
        IF v_done THEN LEAVE loop_tables; END IF;

        SELECT COUNT(*), MAX(k.COLUMN_NAME) INTO v_pk_count, v_pk
        FROM information_schema.KEY_COLUMN_USAGE k
        WHERE k.TABLE_SCHEMA = p_schema AND k.TABLE_NAME = v_table AND k.CONSTRAINT_NAME = 'PRIMARY';
        IF v_pk_count <> 1 THEN ITERATE loop_tables; END IF;

        SELECT COUNT(*) INTO v_fields
        FROM information_schema.COLUMNS c
        WHERE c.TABLE_SCHEMA = p_schema AND c.TABLE_NAME = v_table
          AND c.DATA_TYPE IN ('date','datetime','timestamp')
          AND c.EXTRA NOT LIKE '%VIRTUAL GENERATED%' AND c.EXTRA NOT LIKE '%STORED GENERATED%'
          AND c.EXTRA NOT LIKE '%on update%'
          AND REGEXP_LIKE(c.COLUMN_NAME, v_regex, 'i');
        IF v_fields = 0 THEN ITERATE loop_tables; END IF;

        SELECT c.COLUMN_NAME INTO v_anchor
        FROM information_schema.COLUMNS c
        WHERE c.TABLE_SCHEMA = p_schema AND c.TABLE_NAME = v_table
          AND c.DATA_TYPE IN ('date','datetime','timestamp')
          AND c.EXTRA NOT LIKE '%VIRTUAL GENERATED%' AND c.EXTRA NOT LIKE '%STORED GENERATED%'
          AND c.EXTRA NOT LIKE '%on update%'
          AND REGEXP_LIKE(c.COLUMN_NAME, v_regex, 'i')
        ORDER BY CASE WHEN c.COLUMN_NAME IN ('create_time','created_at','creation_time','created_time') THEN 0 ELSE 1 END,
                 c.ORDINAL_POSITION
        LIMIT 1;

        -- 待平移的日期列中是否有落在唯一索引上的
        SELECT COUNT(*) INTO v_uniq_hit
        FROM information_schema.STATISTICS s
        JOIN information_schema.COLUMNS c
          ON c.TABLE_SCHEMA = s.TABLE_SCHEMA
         AND c.TABLE_NAME = s.TABLE_NAME
         AND c.COLUMN_NAME = s.COLUMN_NAME
        WHERE s.TABLE_SCHEMA = p_schema AND s.TABLE_NAME = v_table AND s.NON_UNIQUE = 0
          AND c.DATA_TYPE IN ('date','datetime','timestamp')
          AND c.EXTRA NOT LIKE '%VIRTUAL GENERATED%' AND c.EXTRA NOT LIKE '%STORED GENERATED%'
          AND c.EXTRA NOT LIKE '%on update%'
          AND REGEXP_LIKE(c.COLUMN_NAME, v_regex, 'i');

        SET @tbl = CONCAT('`', REPLACE(p_schema,'`','``'), '`.`', REPLACE(v_table,'`','``'), '`');
        SET @anchor = CONCAT('`', REPLACE(v_anchor,'`','``'), '`');

        inner_block: BEGIN
            DECLARE EXIT HANDLER FOR SQLEXCEPTION
            BEGIN
                GET DIAGNOSTICS CONDITION 1 v_err = MESSAGE_TEXT;
                INSERT INTO tmp_date_shift_preview
                VALUES (v_table, v_anchor, 'failed', v_fields, 0, CONCAT('[error] ', v_err));
            END;

            IF v_uniq_hit > 0 THEN
                -- 统一平移：整表使用同一偏移，保留唯一键。
                -- 偏移量按所有待平移列的整体跨度计算，保证每一列都落在目标窗口内。
                -- COALESCE 保护：整列为 NULL 时 MIN/MAX 返回 NULL，会让 LEAST/GREATEST 整体变 NULL
                SELECT GROUP_CONCAT(CONCAT('COALESCE(MIN(`', REPLACE(c.COLUMN_NAME,'`','``'), '`), ''9999-12-31 23:59:59'')')
                                    ORDER BY c.ORDINAL_POSITION SEPARATOR ', '),
                       GROUP_CONCAT(CONCAT('COALESCE(MAX(`', REPLACE(c.COLUMN_NAME,'`','``'), '`), ''1000-01-01 00:00:00'')')
                                    ORDER BY c.ORDINAL_POSITION SEPARATOR ', ')
                INTO v_min_expr, v_max_expr
                FROM information_schema.COLUMNS c
                WHERE c.TABLE_SCHEMA = p_schema AND c.TABLE_NAME = v_table
                  AND c.DATA_TYPE IN ('date','datetime','timestamp')
                  AND c.EXTRA NOT LIKE '%VIRTUAL GENERATED%' AND c.EXTRA NOT LIKE '%STORED GENERATED%'
                  AND c.EXTRA NOT LIKE '%on update%'
                  AND REGEXP_LIKE(c.COLUMN_NAME, v_regex, 'i');

                SET @sql = CONCAT('SELECT LEAST(', v_min_expr, '), GREATEST(', v_max_expr,
                                  ') INTO @min_all, @max_all FROM ', @tbl);
                PREPARE stmt FROM @sql;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;

                SET @span_days = TIMESTAMPDIFF(DAY, DATE(@min_all), DATE(@max_all));
                SET @range_days = TIMESTAMPDIFF(DAY, DATE(p_start), DATE(p_end));

                IF @min_all IS NULL OR @min_all >= '9999-01-01' THEN
                    INSERT INTO tmp_date_shift_preview
                    VALUES (v_table, v_anchor, 'skipped', v_fields, 0, 'no non-null date value');
                ELSEIF @span_days > @range_days THEN
                    INSERT INTO tmp_date_shift_preview
                    VALUES (v_table, v_anchor, 'skipped', v_fields, 0,
                            CONCAT('date span ', @span_days, ' days exceeds target range ', @range_days, ' days'));
                ELSE
                    -- 按整日平移，保留时分秒与相对顺序
                    SET @shift_delta = (TIMESTAMPDIFF(DAY, DATE(@min_all), DATE(p_start))
                                        + FLOOR(RAND() * (@range_days - @span_days + 1))) * 86400;
                    -- 随机到 0 天等于没改，强制至少平移 1 天
                    IF @shift_delta = 0 AND @span_days < @range_days THEN
                        SET @shift_delta = 86400;
                    END IF;

                    SELECT GROUP_CONCAT(
                               CONCAT('`', REPLACE(c.COLUMN_NAME,'`','``'), '` = DATE_ADD(`',
                                      REPLACE(c.COLUMN_NAME,'`','``'), '`, INTERVAL @shift_delta SECOND)')
                               ORDER BY c.ORDINAL_POSITION SEPARATOR ', ')
                    INTO v_cols
                    FROM information_schema.COLUMNS c
                    WHERE c.TABLE_SCHEMA = p_schema AND c.TABLE_NAME = v_table
                      AND c.DATA_TYPE IN ('date','datetime','timestamp')
                      AND c.EXTRA NOT LIKE '%VIRTUAL GENERATED%' AND c.EXTRA NOT LIKE '%STORED GENERATED%'
                      AND c.EXTRA NOT LIKE '%on update%'
                      AND REGEXP_LIKE(c.COLUMN_NAME, v_regex, 'i');

                    -- InnoDB 逐行更新唯一键会瞬时冲突，按偏移方向决定扫描顺序：
                    -- 正偏移先动较大的值，负偏移先动较小的值
                    SELECT GROUP_CONCAT(CONCAT('`', REPLACE(d.COLUMN_NAME,'`','``'), '`')
                                        ORDER BY d.ORDINAL_POSITION SEPARATOR ', ')
                    INTO v_uniq_cols
                    FROM (
                        SELECT DISTINCT c.COLUMN_NAME, c.ORDINAL_POSITION
                        FROM information_schema.STATISTICS s
                        JOIN information_schema.COLUMNS c
                          ON c.TABLE_SCHEMA = s.TABLE_SCHEMA
                         AND c.TABLE_NAME = s.TABLE_NAME
                         AND c.COLUMN_NAME = s.COLUMN_NAME
                        WHERE s.TABLE_SCHEMA = p_schema AND s.TABLE_NAME = v_table AND s.NON_UNIQUE = 0
                          AND c.DATA_TYPE IN ('date','datetime','timestamp')
                          AND c.EXTRA NOT LIKE '%VIRTUAL GENERATED%' AND c.EXTRA NOT LIKE '%STORED GENERATED%'
                          AND c.EXTRA NOT LIKE '%on update%'
                          AND REGEXP_LIKE(c.COLUMN_NAME, v_regex, 'i')
                    ) d;

                    SET @update_sql = CONCAT('UPDATE ', @tbl, ' SET ', v_cols,
                                             ' ORDER BY ', v_uniq_cols, ' ',
                                             IF(@shift_delta >= 0, 'DESC', 'ASC'));
                    SET @count_sql = CONCAT('SELECT COUNT(*) INTO @affected FROM ', @tbl);
                    PREPARE stmt FROM @count_sql;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;

                    IF p_execute THEN
                        PREPARE stmt FROM @update_sql;
                        EXECUTE stmt;
                        DEALLOCATE PREPARE stmt;
                    END IF;

                    SET v_count = v_count + @affected;
                    INSERT INTO tmp_date_shift_preview
                    VALUES (v_table, v_anchor, IF(p_execute, 'uniform-executed', 'uniform-preview'),
                            v_fields, @affected, @update_sql);
                    SET v_tables = v_tables + 1;
                END IF;
            ELSE
                -- 逐行随机：每行一个随机目标时间，以锚点整体平移
                SELECT GROUP_CONCAT(
                           CONCAT('`', REPLACE(c.COLUMN_NAME,'`','``'), '` = CASE WHEN `',
                                  REPLACE(c.COLUMN_NAME,'`','``'), '` IS NULL THEN NULL ELSE ',
                                  CASE WHEN c.DATA_TYPE = 'date' THEN 'DATE(' ELSE '' END,
                                  'DATE_ADD(`', REPLACE(c.COLUMN_NAME,'`','``'), '`, INTERVAL TIMESTAMPDIFF(SECOND, `',
                                  REPLACE(v_anchor,'`','``'), '`, __new_anchor) SECOND)',
                                  CASE WHEN c.DATA_TYPE = 'date' THEN ')' ELSE '' END, ' END')
                           ORDER BY c.ORDINAL_POSITION SEPARATOR ', ')
                INTO v_cols
                FROM information_schema.COLUMNS c
                WHERE c.TABLE_SCHEMA = p_schema AND c.TABLE_NAME = v_table
                  AND c.DATA_TYPE IN ('date','datetime','timestamp')
                  AND c.EXTRA NOT LIKE '%VIRTUAL GENERATED%' AND c.EXTRA NOT LIKE '%STORED GENERATED%'
                  AND c.EXTRA NOT LIKE '%on update%'
                  AND REGEXP_LIKE(c.COLUMN_NAME, v_regex, 'i');

                DROP TEMPORARY TABLE IF EXISTS tmp_shift_row_dates;
                SET @sql = CONCAT('CREATE TEMPORARY TABLE tmp_shift_row_dates AS SELECT `',
                                  REPLACE(v_pk,'`','``'), '` AS __pk, DATE_ADD(?, INTERVAL FLOOR(RAND() * ',
                                  v_seconds + 1, ') SECOND) AS __new_anchor FROM ', @tbl,
                                  ' WHERE ', @anchor, ' IS NOT NULL');

                IF p_execute THEN
                    PREPARE stmt FROM @sql;
                    SET @start_date = p_start;
                    EXECUTE stmt USING @start_date;
                    DEALLOCATE PREPARE stmt;

                    SET @update_sql = CONCAT('UPDATE ', @tbl, ' t JOIN tmp_shift_row_dates s ON t.`',
                                             REPLACE(v_pk,'`','``'), '` = s.__pk SET ',
                                             REPLACE(v_cols, '__new_anchor', 's.__new_anchor'));
                    SET @count_sql = 'SELECT COUNT(*) INTO @affected FROM tmp_shift_row_dates';
                    PREPARE stmt FROM @count_sql;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;

                    PREPARE stmt FROM @update_sql;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;

                    SET v_count = v_count + @affected;
                    INSERT INTO tmp_date_shift_preview
                    VALUES (v_table, v_anchor, 'random-executed', v_fields, @affected, @update_sql);
                    DROP TEMPORARY TABLE tmp_shift_row_dates;
                ELSE
                    SET @count_sql = CONCAT('SELECT COUNT(*) INTO @affected FROM ', @tbl, ' WHERE ', @anchor, ' IS NOT NULL');
                    PREPARE stmt FROM @count_sql;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;
                    INSERT INTO tmp_date_shift_preview
                    VALUES (v_table, v_anchor, 'random-preview', v_fields, @affected,
                            CONCAT('Will shift ', v_fields, ' date columns per row; anchor=', v_anchor));
                END IF;
                SET v_tables = v_tables + 1;
            END IF;
        END inner_block;
    END LOOP;
    CLOSE cur;

    SELECT * FROM tmp_date_shift_preview ORDER BY shift_mode, table_name;
    SELECT p_schema AS database_name, p_execute AS executed,
           v_tables AS tables_processed, v_count AS rows_targeted;
END$$

DELIMITER ;