-- 允许跳过先到托盘，但需填写原因并保存当时的推荐快照。
-- 在20261008日期、收货补丁之后执行；先停写、备份、恢复演练。
-- 单次增量；不修改历史事实，不为旧记录补造原因或真实收货时间。
-- DDL隐式提交，中途失败不得盲目重跑。
DELIMITER $$
CREATE PROCEDURE migrate_usage_fifo_20261008()
BEGIN
  IF DATABASE() IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Select target database first';
  END IF;
  IF (SELECT COUNT(*) FROM information_schema.tables
      WHERE table_schema = DATABASE() AND engine = 'InnoDB'
        AND table_name IN ('biz_usage_record', 'biz_pallet_event')) <> 2 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Required InnoDB usage and event tables are missing';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                 WHERE table_schema = DATABASE() AND table_name = 'biz_pallet_inventory'
                   AND column_name = 'site_arrived_at') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Apply shipment receipt migration first';
  END IF;
  IF EXISTS (SELECT 1 FROM information_schema.columns
             WHERE table_schema = DATABASE() AND table_name IN ('biz_usage_record', 'biz_pallet_event')
               AND column_name IN ('fifo_skip_reason', 'fifo_recommendation')) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'FIFO columns already exist; do not rerun';
  END IF;
  ALTER TABLE biz_usage_record
    ADD COLUMN fifo_skip_reason VARCHAR(500) NULL COMMENT '跳过先到托盘原因',
    ADD COLUMN fifo_recommendation JSON NULL COMMENT '跳过时先到托盘身份及到货时间快照';
  ALTER TABLE biz_pallet_event
    ADD COLUMN fifo_skip_reason VARCHAR(500) NULL COMMENT '跳过先到托盘原因',
    ADD COLUMN fifo_recommendation JSON NULL COMMENT '跳过时先到托盘身份及到货时间快照';
END$$
DELIMITER ;
CALL migrate_usage_fifo_20261008();
DROP PROCEDURE migrate_usage_fifo_20261008;