-- 本机开发环境海外费用推送结构修复。固定目标：baiyue_finance。
-- 仅将两个既有摘要字段不足 71 位的长度扩至 71；不改数据、费用、账单或余额。
-- 保留原字段的空值约束、字符集及排序规则；缺表、缺列或异常字段类型时拒绝执行。
-- 执行须单独授权。使用独立连接、遇错停止；不得以本文件代替完整数据库初始化。
USE baiyue_finance;

-- 执行前只读核对；sha256: 前缀 7 位加摘要 64 位，共 71 位。
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE,
       CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'baiyue_finance'
  AND ((TABLE_NAME = 'fin_inbox_event' AND COLUMN_NAME = 'payload_hash')
    OR (TABLE_NAME = 'fin_fee_source' AND COLUMN_NAME = 'source_hash'));

DELIMITER $$
CREATE PROCEDURE finance_fee_hash_width_repair_20261023()
BEGIN
    DECLARE checked_columns INT DEFAULT 0;
    DECLARE column_index INT DEFAULT 0;
    DECLARE target_table VARCHAR(64);
    DECLARE target_column VARCHAR(64);
    DECLARE current_length BIGINT;
    DECLARE nullable_value VARCHAR(3);
    DECLARE charset_value VARCHAR(64);
    DECLARE collation_value VARCHAR(64);
    DECLARE comment_value TEXT;

    IF DATABASE() <> 'baiyue_finance' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '目标必须是 baiyue_finance';
    END IF;
    SELECT COUNT(*) INTO checked_columns
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'baiyue_finance' AND DATA_TYPE IN ('char', 'varchar')
      AND CHARACTER_MAXIMUM_LENGTH >= 64
      AND COLUMN_DEFAULT IS NULL AND EXTRA = ''
      AND ((TABLE_NAME = 'fin_inbox_event' AND COLUMN_NAME = 'payload_hash')
        OR (TABLE_NAME = 'fin_fee_source' AND COLUMN_NAME = 'source_hash'));
    IF checked_columns <> 2 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '摘要字段缺失或类型异常，停止修复';
    END IF;

    WHILE column_index < 2 DO
        SET target_table = IF(column_index = 0, 'fin_inbox_event', 'fin_fee_source');
        SET target_column = IF(column_index = 0, 'payload_hash', 'source_hash');
        SELECT CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_COMMENT
          INTO current_length, nullable_value, charset_value, collation_value, comment_value
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = 'baiyue_finance' AND TABLE_NAME = target_table
          AND COLUMN_NAME = target_column;
        IF current_length < 71 THEN
            SET @finance_fee_hash_width_ddl = CONCAT(
                'ALTER TABLE baiyue_finance.`', target_table, '` MODIFY COLUMN `', target_column,
                '` VARCHAR(71) CHARACTER SET ', charset_value, ' COLLATE ', collation_value,
                IF(nullable_value = 'YES', ' NULL', ' NOT NULL'),
                ' COMMENT ', QUOTE(comment_value));
            PREPARE finance_fee_hash_width_stmt FROM @finance_fee_hash_width_ddl;
            EXECUTE finance_fee_hash_width_stmt;
            DEALLOCATE PREPARE finance_fee_hash_width_stmt;
        END IF;
        SET column_index = column_index + 1;
    END WHILE;
END$$
DELIMITER ;

CALL finance_fee_hash_width_repair_20261023();
DROP PROCEDURE finance_fee_hash_width_repair_20261023;
SET @finance_fee_hash_width_ddl = NULL;

-- 执行后两列长度均应至少为 71；结构已正确时不执行 ALTER。
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE,
       CHARACTER_MAXIMUM_LENGTH >= 71 AS width_ok
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'baiyue_finance'
  AND ((TABLE_NAME = 'fin_inbox_event' AND COLUMN_NAME = 'payload_hash')
    OR (TABLE_NAME = 'fin_fee_source' AND COLUMN_NAME = 'source_hash'));