-- 2026-10-08 近期结构增量汇总（MySQL 8.0.16+）
-- 范围：2026-09-21 至 2026-10-08；用于已有业务库升级，不是空库初始化。
-- 按 USE 显式切换 baiyue_iam / baiyue_firstleg / baiyue_lastleg / baiyue_lastlag_warehouse。
-- 独立文件，无 SOURCE 或外部文件依赖；在目标 MySQL 连接中执行整个文件。
-- 不创建数据库、不写业务行、不含账号或密码；DDL 隐式提交，出错立即停止，不使用 --force。
-- 可重复执行，也支持仅部分新增字段已存在的情况。
-- 来源：recent-schema-incremental-20260923.sql、customs-two-file-incremental-20260926.sql、
-- return-receipt-box-count-incremental-20260929.sql、return-outbound-box-nullable-20260929.sql、
-- return-packing-incremental-20261008.sql 以及 incremental.sql 的 2026-09-29 追加项。

-- 2026-09-23 近期字段与表结构统一增量（MySQL 8.0+）
--
-- 范围：2026-09-21 至 2026-09-22 合入的结构变化。
-- 特性：显式切换到所属 schema；字段和索引均通过 information_schema 判断；可重复执行。
-- 边界：不创建数据库、不删除业务字段/表、不包含权限种子或业务数据修正。
-- 注意：DDL 在 MySQL 中会隐式提交；任一步失败后应停止执行并核对目标实例。

SET NAMES utf8mb4;

-- ============================================================================
-- IAM：国内头程 PDA 操作员绑定作业仓
-- ============================================================================
USE baiyue_iam;

SET @ddl = IF(
  EXISTS (
    SELECT 1 FROM information_schema.tables
     WHERE table_schema = DATABASE() AND table_name = 'iam_membership'
  )
  AND NOT EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'iam_membership'
       AND column_name = 'domestic_warehouse_id'
  ),
  'ALTER TABLE iam_membership ADD COLUMN domestic_warehouse_id BIGINT NULL COMMENT ''国内头程PDA操作员绑定的唯一国内作业仓ID'' AFTER overseas_warehouse_id',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

SET @domestic_warehouse_index_columns = (
  SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index SEPARATOR ',')
    FROM information_schema.statistics
   WHERE table_schema = DATABASE()
     AND table_name = 'iam_membership'
     AND index_name = 'idx_membership_domestic_warehouse'
);
SET @domestic_warehouse_index_non_unique = (
  SELECT MIN(non_unique)
    FROM information_schema.statistics
   WHERE table_schema = DATABASE()
     AND table_name = 'iam_membership'
     AND index_name = 'idx_membership_domestic_warehouse'
);
SET @ddl = CASE
  WHEN NOT EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'iam_membership'
       AND column_name = 'domestic_warehouse_id'
  ) THEN 'SELECT 1'
  WHEN @domestic_warehouse_index_columns IS NULL THEN
    'ALTER TABLE iam_membership ADD KEY idx_membership_domestic_warehouse (tenant_id, domestic_warehouse_id, status)'
  WHEN @domestic_warehouse_index_columns <> 'tenant_id,domestic_warehouse_id,status'
       OR @domestic_warehouse_index_non_unique <> 1 THEN
    'ALTER TABLE iam_membership DROP INDEX idx_membership_domestic_warehouse, ADD KEY idx_membership_domestic_warehouse (tenant_id, domestic_warehouse_id, status)'
  ELSE 'SELECT 1'
END;
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

-- ============================================================================
-- Firstleg：人工 FBA 报价允许缺少船司价格快照
-- ============================================================================
USE baiyue_firstleg;

SET @ddl = IF(
  EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'fl_inbound_fee'
       AND column_name = 'carrier_rate_id' AND is_nullable = 'NO'
  ),
  'ALTER TABLE fl_inbound_fee MODIFY COLUMN carrier_rate_id BIGINT UNSIGNED NULL COMMENT ''船司FBA价格ID；人工报价为空''',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

SET @ddl = IF(
  EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'fl_inbound_fee'
       AND column_name = 'carrier_rate_version' AND is_nullable = 'NO'
  ),
  'ALTER TABLE fl_inbound_fee MODIFY COLUMN carrier_rate_version INT UNSIGNED NULL COMMENT ''船司价格版本快照；人工报价为空''',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

SET @ddl = IF(
  EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'fl_inbound_fee'
       AND column_name = 'rate_effective_from' AND is_nullable = 'NO'
  ),
  'ALTER TABLE fl_inbound_fee MODIFY COLUMN rate_effective_from DATETIME(3) NULL COMMENT ''价格生效开始快照；人工报价为空''',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

-- ============================================================================
-- Firstleg：墨家测量收件箱扩展为箱唛/SKU 双分流
-- ============================================================================

SET @ddl = IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'fl_mojia_measurement_receipt'
       AND column_name = 'inbound_code'
  ),
  'ALTER TABLE fl_mojia_measurement_receipt ADD COLUMN inbound_code VARCHAR(64) NULL COMMENT ''箱码命中后的权威入仓码；SKU记录为空'' AFTER box_code',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

SET @ddl = IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'fl_mojia_measurement_receipt'
       AND column_name = 'measurement_kind'
  ),
  'ALTER TABLE fl_mojia_measurement_receipt ADD COLUMN measurement_kind VARCHAR(16) NOT NULL DEFAULT ''BOX_LABEL'' COMMENT ''测量分流：BOX_LABEL箱唛；SKU一件代发SKU'' AFTER box_code',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

SET @ddl = IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'fl_mojia_measurement_receipt'
       AND column_name = 'weight_kg'
  ),
  'ALTER TABLE fl_mojia_measurement_receipt ADD COLUMN weight_kg DECIMAL(18,3) NOT NULL DEFAULT 0 COMMENT ''设备实测重量kg'' AFTER height_cm',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

SET @ddl = IF(
  EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'fl_mojia_measurement_receipt'
       AND column_name = 'weight_kg'
       AND (column_type <> 'decimal(18,3)' OR is_nullable <> 'NO' OR column_default IS NOT NULL
            OR column_comment <> '设备实测重量kg')
  ),
  'ALTER TABLE fl_mojia_measurement_receipt MODIFY COLUMN weight_kg DECIMAL(18,3) NOT NULL COMMENT ''设备实测重量kg''',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

SET @ddl = IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'fl_mojia_measurement_receipt'
       AND column_name = 'sync_status'
  ),
  'ALTER TABLE fl_mojia_measurement_receipt ADD COLUMN sync_status VARCHAR(24) NULL COMMENT ''SKU同步状态：PENDING/SYNCING/SUCCESS/FAILED'' AFTER status',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

SET @ddl = IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'fl_mojia_measurement_receipt'
       AND column_name = 'sync_failure_code'
  ),
  'ALTER TABLE fl_mojia_measurement_receipt ADD COLUMN sync_failure_code VARCHAR(64) NULL COMMENT ''SKU同步失败业务码'' AFTER sync_status',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

SET @ddl = IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'fl_mojia_measurement_receipt'
       AND column_name = 'sync_failure_reason'
  ),
  'ALTER TABLE fl_mojia_measurement_receipt ADD COLUMN sync_failure_reason VARCHAR(500) NULL COMMENT ''脱敏后的SKU同步失败原因'' AFTER sync_failure_code',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

SET @ddl = IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'fl_mojia_measurement_receipt'
       AND column_name = 'lingxing_sku_id'
  ),
  'ALTER TABLE fl_mojia_measurement_receipt ADD COLUMN lingxing_sku_id VARCHAR(128) NULL COMMENT ''领星SKU内部标识'' AFTER sync_failure_reason',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

SET @ddl = IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'fl_mojia_measurement_receipt'
       AND column_name = 'revision'
  ),
  'ALTER TABLE fl_mojia_measurement_receipt ADD COLUMN revision INT NOT NULL DEFAULT 1 COMMENT ''当前测量版本号'' AFTER lingxing_sku_id',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

SET @ddl = IF(
  EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'fl_mojia_measurement_receipt'
       AND column_name = 'match_failure_code' AND is_nullable = 'NO'
  ),
  'ALTER TABLE fl_mojia_measurement_receipt MODIFY COLUMN match_failure_code VARCHAR(16) NULL COMMENT ''正式箱唛业务匹配失败码''',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

SET @ddl = IF(
  EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'fl_mojia_measurement_receipt'
       AND column_name = 'match_failure_reason' AND is_nullable = 'NO'
  ),
  'ALTER TABLE fl_mojia_measurement_receipt MODIFY COLUMN match_failure_reason VARCHAR(500) NULL COMMENT ''脱敏后的箱唛匹配失败原因''',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

SET @ddl = IF(
  EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'fl_mojia_measurement_receipt'
       AND column_name = 'box_code'
       AND column_comment <> '设备扫描的原始箱码或不带#的SKU'
  ),
  'ALTER TABLE fl_mojia_measurement_receipt MODIFY COLUMN box_code VARCHAR(128) NOT NULL COMMENT ''设备扫描的原始箱码或不带#的SKU''',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

SET @ddl = IF(
  EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'fl_mojia_measurement_receipt'
       AND column_name = 'status'
       AND column_comment <> '原始收件状态；SKU记录使用SKU_PENDING_SYNC'
  ),
  'ALTER TABLE fl_mojia_measurement_receipt MODIFY COLUMN status VARCHAR(24) NOT NULL DEFAULT ''UNMATCHED'' COMMENT ''原始收件状态；SKU记录使用SKU_PENDING_SYNC''',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

-- ============================================================================
-- Lastleg：同一尾程单允许多次换标申请，订单关联由唯一索引改为普通索引
-- ============================================================================
USE baiyue_lastleg;

SET @ddl = IF(
  EXISTS (
    SELECT 1 FROM information_schema.statistics
     WHERE table_schema = DATABASE() AND table_name = 'll_relabel_application'
       AND index_name = 'uk_ll_relabel_application_order'
  ),
  'ALTER TABLE ll_relabel_application DROP INDEX uk_ll_relabel_application_order',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

SET @relabel_order_index_columns = (
  SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index SEPARATOR ',')
    FROM information_schema.statistics
   WHERE table_schema = DATABASE()
     AND table_name = 'll_relabel_application'
     AND index_name = 'idx_ll_relabel_application_order'
);
SET @relabel_order_index_non_unique = (
  SELECT MIN(non_unique)
    FROM information_schema.statistics
   WHERE table_schema = DATABASE()
     AND table_name = 'll_relabel_application'
     AND index_name = 'idx_ll_relabel_application_order'
);
SET @ddl = CASE
  WHEN @relabel_order_index_columns IS NULL THEN
    'ALTER TABLE ll_relabel_application ADD KEY idx_ll_relabel_application_order (lastleg_order_id)'
  WHEN @relabel_order_index_columns <> 'lastleg_order_id'
       OR @relabel_order_index_non_unique <> 1 THEN
    'ALTER TABLE ll_relabel_application DROP INDEX idx_ll_relabel_application_order, ADD KEY idx_ll_relabel_application_order (lastleg_order_id)'
  ELSE 'SELECT 1'
END;
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

-- ============================================================================
-- Overseas Warehouse：A0 系统共享溢出库位标记
-- 注意：正式 schema 名沿用项目现有拼写 baiyue_lastlag_warehouse。
-- ============================================================================
USE baiyue_lastlag_warehouse;

SET @ddl = IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'wh_location'
       AND column_name = 'shared_overflow'
  ),
  'ALTER TABLE wh_location ADD COLUMN shared_overflow TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''是否系统共享溢出库位（A0）'' AFTER location_type',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

SET @ddl = IF(
  EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = DATABASE() AND table_name = 'wh_location'
       AND column_name = 'shared_overflow'
       AND (column_type <> 'tinyint(1)' OR is_nullable <> 'NO'
            OR COALESCE(column_default, '') <> '0'
            OR column_comment <> '是否系统共享溢出库位（A0）')
  ),
  'ALTER TABLE wh_location MODIFY COLUMN shared_overflow TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''是否系统共享溢出库位（A0）''',
  'SELECT 1'
);
PREPARE recent_schema_stmt FROM @ddl;
EXECUTE recent_schema_stmt;
DEALLOCATE PREPARE recent_schema_stmt;

-- 最终只读核对；四行均应返回 READY。
SELECT 'baiyue_iam' AS target_schema,
       IF(
         EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = 'baiyue_iam' AND table_name = 'iam_membership' AND column_name = 'domestic_warehouse_id')
         AND (SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index SEPARATOR ',') FROM information_schema.statistics WHERE table_schema = 'baiyue_iam' AND table_name = 'iam_membership' AND index_name = 'idx_membership_domestic_warehouse') = 'tenant_id,domestic_warehouse_id,status',
         'READY', 'CHECK_REQUIRED'
       ) AS schema_status
UNION ALL
SELECT 'baiyue_firstleg',
       IF(
         (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = 'baiyue_firstleg' AND table_name = 'fl_mojia_measurement_receipt' AND column_name IN ('measurement_kind','weight_kg','sync_status','sync_failure_code','sync_failure_reason','lingxing_sku_id','revision')) = 7
         AND (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = 'baiyue_firstleg' AND table_name = 'fl_inbound_fee' AND column_name IN ('carrier_rate_id','carrier_rate_version','rate_effective_from') AND is_nullable = 'YES') = 3,
         'READY', 'CHECK_REQUIRED'
       )
UNION ALL
SELECT 'baiyue_lastleg',
       IF(
         NOT EXISTS (SELECT 1 FROM information_schema.statistics WHERE table_schema = 'baiyue_lastleg' AND table_name = 'll_relabel_application' AND index_name = 'uk_ll_relabel_application_order')
         AND (SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index SEPARATOR ',') FROM information_schema.statistics WHERE table_schema = 'baiyue_lastleg' AND table_name = 'll_relabel_application' AND index_name = 'idx_ll_relabel_application_order') = 'lastleg_order_id'
         AND (SELECT MIN(non_unique) FROM information_schema.statistics WHERE table_schema = 'baiyue_lastleg' AND table_name = 'll_relabel_application' AND index_name = 'idx_ll_relabel_application_order') = 1,
         'READY', 'CHECK_REQUIRED'
       )
UNION ALL
SELECT 'baiyue_lastlag_warehouse',
       IF(
         EXISTS (
           SELECT 1 FROM information_schema.columns
            WHERE table_schema = 'baiyue_lastlag_warehouse'
              AND table_name = 'wh_location'
              AND column_name = 'shared_overflow'
              AND column_type = 'tinyint(1)'
              AND is_nullable = 'NO'
              AND COALESCE(column_default, '') = '0'
         ),
         'READY', 'CHECK_REQUIRED'
       );


-- 报关两文件流程：回执与真实报关完税文件分离。
-- 仅在已确认目标测试库后执行；所有 DDL 可重复执行，不创建数据库、不写业务数据。
USE baiyue_firstleg;

SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='fl_customs_container' AND column_name='broker_submitted_by'), 'ALTER TABLE fl_customs_container ADD COLUMN broker_submitted_by BIGINT UNSIGNED NULL COMMENT ''发送报关行执行的操作人ID''', 'SELECT 1');
PREPARE recent_customs_stmt FROM @ddl; EXECUTE recent_customs_stmt; DEALLOCATE PREPARE recent_customs_stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='fl_customs_container' AND column_name='broker_submitted_by_name'), 'ALTER TABLE fl_customs_container ADD COLUMN broker_submitted_by_name VARCHAR(64) NULL COMMENT ''发送报关行执行的操作人名称''', 'SELECT 1');
PREPARE recent_customs_stmt FROM @ddl; EXECUTE recent_customs_stmt; DEALLOCATE PREPARE recent_customs_stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='fl_customs_container' AND column_name='broker_submitted_at'), 'ALTER TABLE fl_customs_container ADD COLUMN broker_submitted_at DATETIME(3) NULL COMMENT ''发送报关行执行时间''', 'SELECT 1');
PREPARE recent_customs_stmt FROM @ddl; EXECUTE recent_customs_stmt; DEALLOCATE PREPARE recent_customs_stmt;

CREATE TABLE IF NOT EXISTS fl_customs_final_document (
  id BIGINT UNSIGNED NOT NULL COMMENT '真实报关完税文件ID',
  source_document_id BIGINT UNSIGNED NOT NULL COMMENT '对应冻结客户资料ID',
  file_id BIGINT UNSIGNED NOT NULL COMMENT 'File服务文件ID',
  file_name VARCHAR(255) NOT NULL COMMENT '真实报关完税文件名称快照',
  file_hash CHAR(64) NOT NULL COMMENT '真实报关完税文件SHA-256摘要',
  content_type VARCHAR(128) NULL COMMENT '真实报关完税文件媒体类型',
  size_bytes BIGINT UNSIGNED NOT NULL COMMENT '真实报关完税文件大小（字节）',
  uploaded_by BIGINT UNSIGNED NOT NULL COMMENT '上传操作人ID',
  uploaded_by_name VARCHAR(64) NULL COMMENT '上传操作人名称快照',
  uploaded_at DATETIME(3) NOT NULL COMMENT '上传时间',
  version INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '完税文件版本号',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
  PRIMARY KEY(id),
  UNIQUE KEY uk_customs_final_source(source_document_id),
  CONSTRAINT fk_customs_final_source FOREIGN KEY(source_document_id) REFERENCES fl_customs_customer_document(id)
) ENGINE=InnoDB COMMENT='真实报关完税文件当前版本';


-- 退货/换标近期结构增量汇总（MySQL 8.0+）
--
-- 目标数据库：baiyue_lastleg
-- 覆盖：退货箱级签收、箱级出库、退货合箱预占/追溯、换标箱唛总文件。
-- 本文件只执行 DDL，不写入业务数据；所有字段、索引和约束均按元数据判断，
-- 可在同一个目标库上重复执行。数据库不存在或基线表缺失时会立即报错并停止。

SET NAMES utf8mb4;
USE `baiyue_lastleg`;

-- 2026-09-29：箱维度退货出库不绑定托盘。
SET @ddl = IF(
  EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'll_outbound_pallet_item')
  AND EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_outbound_pallet_item' AND column_name = 'pallet_no' AND is_nullable = 'NO'),
  'ALTER TABLE ll_outbound_pallet_item MODIFY COLUMN pallet_no VARCHAR(128) NULL COMMENT ''托盘号，箱维度出库为空''',
  'SELECT 1'
);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2026-09-29：退货签收单元、SKU 箱数及来源实物追溯。
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_receipt' AND column_name = 'unit_type'), 'ALTER TABLE ll_return_receipt ADD COLUMN unit_type VARCHAR(16) NULL COMMENT ''签收类型 PALLET/BOX/PARCEL''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_receipt' AND column_name = 'unit_code'), 'ALTER TABLE ll_return_receipt ADD COLUMN unit_code VARCHAR(128) NULL COMMENT ''签收单元业务码''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_receipt' AND column_name = 'total_box_count'), 'ALTER TABLE ll_return_receipt ADD COLUMN total_box_count BIGINT NULL COMMENT ''签收 SKU 箱数汇总''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_receipt' AND column_name = 'length'), 'ALTER TABLE ll_return_receipt ADD COLUMN length DECIMAL(12,3) NULL COMMENT ''实测尺寸 cm''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_receipt' AND column_name = 'width'), 'ALTER TABLE ll_return_receipt ADD COLUMN width DECIMAL(12,3) NULL COMMENT ''实测尺寸 cm''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_receipt' AND column_name = 'height'), 'ALTER TABLE ll_return_receipt ADD COLUMN height DECIMAL(12,3) NULL COMMENT ''实测尺寸 cm''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_receipt' AND column_name = 'weight'), 'ALTER TABLE ll_return_receipt ADD COLUMN weight DECIMAL(12,3) NULL COMMENT ''实测重量 kg''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_receipt_item' AND column_name = 'box_count'), 'ALTER TABLE ll_return_receipt_item ADD COLUMN box_count BIGINT NULL COMMENT ''SKU 签收箱数''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_inventory_carton' AND column_name = 'source_unit_type'), 'ALTER TABLE ll_return_inventory_carton ADD COLUMN source_unit_type VARCHAR(32) NULL COMMENT ''来源实物类型''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_inventory_carton' AND column_name = 'source_unit_code'), 'ALTER TABLE ll_return_inventory_carton ADD COLUMN source_unit_code VARCHAR(128) NULL COMMENT ''来源实物业务码''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2026-09-29：换标箱唛总文件。
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_relabel_application' AND column_name = 'carton_total_file_id'), 'ALTER TABLE ll_relabel_application ADD COLUMN carton_total_file_id VARCHAR(128) NULL COMMENT ''箱唛总文件 ID''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_relabel_application' AND column_name = 'carton_total_file_name'), 'ALTER TABLE ll_relabel_application ADD COLUMN carton_total_file_name VARCHAR(255) NULL COMMENT ''箱唛总文件名''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 合箱计划及精确来源/结果快照。
CREATE TABLE IF NOT EXISTS ll_return_packing_plan (
  id BIGINT NOT NULL COMMENT '合箱计划 ID', plan_no VARCHAR(128) NOT NULL COMMENT '计划业务号',
  customer_id VARCHAR(128) NOT NULL COMMENT '客户隔离键', overseas_warehouse_id VARCHAR(128) NOT NULL COMMENT '海外仓 ID',
  return_nos_json JSON NOT NULL COMMENT '来源退货单范围', boxes_json JSON NOT NULL COMMENT '目标箱明细',
  mode VARCHAR(32) NOT NULL COMMENT 'SKU 散件或 BOX 箱合并', status VARCHAR(32) NOT NULL COMMENT '计划状态',
  remark VARCHAR(1000) NULL COMMENT '合箱备注', idempotency_key VARCHAR(255) NOT NULL COMMENT '创建幂等键',
  created_at DATETIME(3) NOT NULL COMMENT '创建时间', updated_at DATETIME(3) NOT NULL COMMENT '更新时间',
  pushed_at DATETIME(3) NULL COMMENT '审核推送时间', completed_at DATETIME(3) NULL COMMENT '作业完成时间',
  PRIMARY KEY (id), UNIQUE KEY uk_ll_return_packing_plan_no (plan_no),
  UNIQUE KEY uk_ll_return_packing_plan_key (customer_id, idempotency_key),
  KEY idx_ll_return_packing_plan_warehouse (overseas_warehouse_id, status, pushed_at)
) ENGINE=InnoDB COMMENT='退货合箱计划';

SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_packing_plan' AND column_name = 'source_items_json'), 'ALTER TABLE ll_return_packing_plan ADD COLUMN source_items_json JSON NULL COMMENT ''精确来源箱和 SKU 锁定数量''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_packing_plan' AND column_name = 'result_marks_json'), 'ALTER TABLE ll_return_packing_plan ADD COLUMN result_marks_json JSON NULL COMMENT ''完成生成或并入的目标箱唛''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_packing_plan' AND column_name = 'request_hash'), 'ALTER TABLE ll_return_packing_plan ADD COLUMN request_hash VARCHAR(64) NULL COMMENT ''创建载荷摘要''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_packing_plan' AND column_name = 'completion_hash'), 'ALTER TABLE ll_return_packing_plan ADD COLUMN completion_hash VARCHAR(64) NULL COMMENT ''完成载荷摘要''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_packing_plan' AND column_name = 'result_labels_json'), 'ALTER TABLE ll_return_packing_plan ADD COLUMN result_labels_json JSON NULL COMMENT ''计划完工标签快照''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_inventory_carton_item' AND column_name = 'packing_reserved_quantity'), 'ALTER TABLE ll_return_inventory_carton_item ADD COLUMN packing_reserved_quantity BIGINT NOT NULL DEFAULT 0 COMMENT ''合箱计划预占件数''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_inventory_carton' AND column_name = 'location_code'), 'ALTER TABLE ll_return_inventory_carton ADD COLUMN location_code VARCHAR(128) NULL COMMENT ''退货箱当前库位''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_inventory_carton' AND column_name = 'putaway_at'), 'ALTER TABLE ll_return_inventory_carton ADD COLUMN putaway_at DATETIME(3) NULL COMMENT ''退货箱上架时间''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_inventory_operation' AND column_name = 'result_json'), 'ALTER TABLE ll_return_inventory_operation ADD COLUMN result_json JSON NULL COMMENT ''幂等操作不可变返回快照''', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS ll_return_packing_trace (
  id BIGINT NOT NULL COMMENT '合箱追溯 ID', customer_id VARCHAR(128) NOT NULL COMMENT '原客户隔离键',
  operation_id BIGINT NOT NULL COMMENT '合箱操作 ID', source_carton_mark VARCHAR(128) NOT NULL COMMENT '来源箱唛',
  target_carton_mark VARCHAR(128) NOT NULL COMMENT '目标箱唛', return_no VARCHAR(128) NOT NULL COMMENT '原始退货单号',
  sku_code VARCHAR(128) NOT NULL COMMENT 'SKU', quantity BIGINT NOT NULL COMMENT '本次移动件数',
  created_at DATETIME(3) NOT NULL COMMENT '发生时间', PRIMARY KEY (id),
  KEY idx_return_packing_trace_order (customer_id, return_no, created_at),
  KEY idx_return_packing_trace_target (customer_id, target_carton_mark, sku_code),
  KEY idx_return_packing_trace_source (customer_id, source_carton_mark, sku_code),
  CONSTRAINT chk_return_packing_trace_quantity CHECK (quantity > 0)
) ENGINE=InnoDB COMMENT='退货合箱逐 SKU 原始退货单不可变追溯';

-- 原子替换两个 CHECK；新定义验证失败时原约束保留。
SET @recent_drop_check_0 = IF(EXISTS (SELECT 1 FROM information_schema.table_constraints WHERE constraint_schema=DATABASE() AND table_name='ll_return_inventory_carton' AND constraint_name='chk_ll_return_inventory_carton_quantity'), 'DROP CHECK chk_ll_return_inventory_carton_quantity, ', '');
SET @recent_drop_check_1 = IF(EXISTS (SELECT 1 FROM information_schema.table_constraints WHERE constraint_schema=DATABASE() AND table_name='ll_return_inventory_carton' AND constraint_name='chk_ll_return_inventory_carton_status'), 'DROP CHECK chk_ll_return_inventory_carton_status, ', '');
SET @ddl = CONCAT('ALTER TABLE ll_return_inventory_carton ', @recent_drop_check_0, @recent_drop_check_1, 'ADD CONSTRAINT chk_ll_return_inventory_carton_quantity CHECK ((status IN (''AVAILABLE'',''PACKING_RESERVED'',''RELABEL_PENDING'',''OUTBOUND_PENDING'',''OUTBOUNDED'',''DISCARD_PENDING'') AND sku_types>0 AND total_quantity>0) OR (status IN (''MERGED'',''DISCARDED'') AND sku_types=0 AND total_quantity=0)), ADD CONSTRAINT chk_ll_return_inventory_carton_status CHECK (status IN (''AVAILABLE'',''PACKING_RESERVED'',''RELABEL_PENDING'',''OUTBOUND_PENDING'',''MERGED'',''OUTBOUNDED'',''DISCARD_PENDING'',''DISCARDED''))');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 同一签收批次可拆成多个退货箱：唯一约束改为普通索引。
SET @ddl = IF(EXISTS (SELECT 1 FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'll_return_inventory_carton' AND index_name = 'uk_ll_return_inventory_carton_receipt'), 'ALTER TABLE ll_return_inventory_carton DROP INDEX uk_ll_return_inventory_carton_receipt', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(NOT EXISTS (SELECT 1 FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'll_return_inventory_carton' AND index_name = 'idx_ll_return_inventory_carton_receipt'), 'ALTER TABLE ll_return_inventory_carton ADD INDEX idx_ll_return_inventory_carton_receipt (source_receipt_id)', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SELECT DATABASE() AS target_database,
       IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_return_receipt' AND column_name IN ('unit_type','unit_code','total_box_count','length','width','height','weight')) = 7, 'READY', 'CHECK_REQUIRED') AS receipt_schema,
       IF(EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'll_return_packing_plan') AND EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'll_return_packing_trace'), 'READY', 'CHECK_REQUIRED') AS packing_schema,
       IF(EXISTS (SELECT 1 FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'll_return_inventory_carton' AND index_name = 'idx_ll_return_inventory_carton_receipt'), 'READY', 'CHECK_REQUIRED') AS carton_index;