-- 指定 TEST 的海外费用/仓租结构补齐；实例必须由执行者另行核对并获准。
-- 仅新增 ll_storage_rent_inbound 和 ll_finance_fee_source.reviewer_name。
-- 使用独立连接，支持 DELIMITER，遇错停止；禁止 mysql --force。
-- 不更新费用、库存、钱包或历史计租数据，不重放空库初始化。
-- DDL 隐式提交；失败先回查结构，禁止删除已建表/字段来重跑。
USE baiyue_lastleg;

-- 仅清理本脚本可能遗留的专用校验过程，不删除业务对象。
DROP PROCEDURE IF EXISTS overseas_fee_rent_structure_add_20261026;
DELIMITER $$
CREATE PROCEDURE overseas_fee_rent_structure_add_20261026()
BEGIN
    DECLARE inbound_exists INT DEFAULT 0;
    DECLARE reviewer_exists INT DEFAULT 0;

    IF DATABASE()<>'baiyue_lastleg' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='目标必须为 baiyue_lastleg';
    END IF;
    -- 在首个业务 DDL 前核对两张依赖表及新增字段的定位列。
    IF (SELECT COUNT(*) FROM information_schema.TABLES
        WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN ('ll_finance_fee_source','ll_storage_rent_lot')
          AND TABLE_TYPE='BASE TABLE' AND ENGINE='InnoDB')<>2
       OR (SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ll_finance_fee_source'
          AND COLUMN_NAME='reviewer_id' AND DATA_TYPE='varchar' AND CHARACTER_MAXIMUM_LENGTH=128)<>1 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='费用来源或计租批次基础结构不匹配，停止补齐';
    END IF;

    SELECT COUNT(*) INTO reviewer_exists FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ll_finance_fee_source' AND COLUMN_NAME='reviewer_name';
    IF reviewer_exists>0 AND (SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ll_finance_fee_source' AND COLUMN_NAME='reviewer_name'
          AND DATA_TYPE='varchar' AND CHARACTER_MAXIMUM_LENGTH=255 AND IS_NULLABLE='YES'
          AND COLUMN_DEFAULT IS NULL AND EXTRA='')<>1 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='已存在的审核人名称字段与正式定义不同，停止且不覆盖';
    END IF;

    SELECT COUNT(*) INTO inbound_exists FROM information_schema.TABLES
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ll_storage_rent_inbound';
    -- 已存在的同名表必须完整匹配；不以 IF NOT EXISTS 掩盖半成品或异构表。
    IF inbound_exists>0 THEN
        IF (SELECT COUNT(*) FROM information_schema.TABLES
            WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ll_storage_rent_inbound'
              AND TABLE_TYPE='BASE TABLE' AND ENGINE='InnoDB' AND TABLE_COLLATION='utf8mb4_unicode_ci')<>1
           OR (SELECT COUNT(*) FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ll_storage_rent_inbound')<>4
           OR (SELECT COUNT(*) FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ll_storage_rent_inbound'
              AND IS_NULLABLE='NO' AND COLUMN_DEFAULT IS NULL AND EXTRA=''
              AND ((COLUMN_NAME IN ('receipt_id','lot_id') AND COLUMN_TYPE='bigint')
                OR (COLUMN_NAME='quantity' AND COLUMN_TYPE='decimal(24,12)')
                OR (COLUMN_NAME='created_at' AND COLUMN_TYPE='datetime(3)')))<>4 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='已存在的计租实收表字段与正式定义不同，停止且不覆盖';
        END IF;
        IF (SELECT COUNT(*) FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ll_storage_rent_inbound' AND INDEX_NAME='PRIMARY')<>1
           OR (SELECT COUNT(*) FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ll_storage_rent_inbound' AND INDEX_NAME='PRIMARY'
              AND COLUMN_NAME='receipt_id' AND SEQ_IN_INDEX=1 AND NON_UNIQUE=0 AND SUB_PART IS NULL)<>1
           OR (SELECT COUNT(*) FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ll_storage_rent_inbound'
              AND INDEX_NAME='idx_storage_rent_inbound_lot')<>1
           OR (SELECT COUNT(*) FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ll_storage_rent_inbound'
              AND INDEX_NAME='idx_storage_rent_inbound_lot' AND COLUMN_NAME='lot_id'
              AND SEQ_IN_INDEX=1 AND NON_UNIQUE=1 AND SUB_PART IS NULL)<>1 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='计租实收表主键或批次索引不匹配，停止且不覆盖';
        END IF;
    END IF;

    -- 全部前置检查通过后才补缺项；重跑时保持已存在的业务结构和数据。
    IF reviewer_exists=0 THEN
        ALTER TABLE ll_finance_fee_source
            ADD COLUMN reviewer_name VARCHAR(255) NULL COMMENT '审核人名称快照' AFTER reviewer_id;
    END IF;
    IF inbound_exists=0 THEN
        CREATE TABLE ll_storage_rent_inbound (
          receipt_id bigint NOT NULL COMMENT '实际入库凭证主键，防止重复累计计租数量',
          lot_id bigint NOT NULL COMMENT '头程同单同仓计租批次主键',
          quantity decimal(24,12) NOT NULL COMMENT '本次实收追加计租箱数',
          created_at datetime(3) NOT NULL COMMENT '计租登记时间，采用协调世界时',
          PRIMARY KEY (receipt_id),
          KEY idx_storage_rent_inbound_lot (lot_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='头程实收计租幂等登记';
    END IF;
END$$
DELIMITER ;
CALL overseas_fee_rent_structure_add_20261026();
DROP PROCEDURE overseas_fee_rent_structure_add_20261026;

SHOW CREATE TABLE ll_storage_rent_inbound;
SELECT COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ll_finance_fee_source' AND COLUMN_NAME='reviewer_name';
SELECT 'TABLE_COLUMN_READY' AS structure_status;
-- TABLE_COLUMN_READY 只表示上述表/字段补齐；免费仓租另执行已审阅的
-- overseas-storage-rent-zero-fee-repair.sql，不能据此宣称完整功能上线。