-- 海外采购旧流程专属表退役，MySQL 8.0+，目标仅 baiyue_lastleg。
-- 必须先停止旧版 Lastleg 写入并部署本次分离版本，验证新流程后在维护窗口执行。
-- 永久删除下面明确列出的 21 张旧采购表及其记录，DDL 隐式提交；禁止 mysql --force。
-- 保留主单、审核流水、采购附件、分批收货、出库及幂等六表；不改任何共用财务、资金、文件或仓储表。
-- 旧资金表有记录或旧状态采购未处理时拒绝，不自动清除、回填或伪造资金完成。
-- 运行者须在同一连接显式 SET @procurement_retirement_release='procurement-six-tables-20261027';
-- 文件准备不表示数据库已执行。先审核备份及运行版本，按清单整份执行，遇错停止。
USE baiyue_lastleg;
DELIMITER $$
DROP PROCEDURE IF EXISTS retire_procurement_legacy_tables$$
CREATE PROCEDURE retire_procurement_legacy_tables()
BEGIN
  IF DATABASE()<>'baiyue_lastleg' OR COALESCE(@procurement_retirement_release,'')<>'procurement-six-tables-20261027' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Confirm the six-table procurement release and target schema first';
  END IF;
  IF (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name IN (
    'll_overseas_procurement_order','ll_overseas_procurement_status_history','ll_overseas_procurement_file',
    'll_procurement_workflow_receipt','ll_procurement_workflow_outbound','ll_procurement_workflow_command'))<>6 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Six active procurement tables must exist before retiring legacy tables';
  END IF;
  IF EXISTS(SELECT 1 FROM ll_overseas_procurement_order WHERE status NOT IN
    ('PENDING_REVIEW','RECEIVING','WAIT_CUSTOMER_CONFIRM','CONFIRMED','IN_TRANSIT','DELIVERING','COMPLETED','REJECTED','CANCELLED')) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Legacy procurement orders remain; resolve them before retirement';
  END IF;
  -- 仅只读核对共用资金记录；不删除资金命令或回执，不把已投递当作已结清。
  IF EXISTS(SELECT 1 FROM ll_event_outbox WHERE aggregate_type='PROCUREMENT_FEE')
      OR EXISTS(SELECT 1 FROM ll_wallet_event_inbox
        WHERE JSON_UNQUOTE(JSON_EXTRACT(payload_json,'$.payload.businessType'))='PROCUREMENT_FEE') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Legacy procurement wallet events exist; financial review is required';
  END IF;
  IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='ll_overseas_procurement_fee') THEN
    SET @procurement_retirement_financial_rows=0;
    SET @procurement_retirement_check_sql='SELECT COUNT(*) INTO @procurement_retirement_financial_rows FROM baiyue_lastleg.ll_overseas_procurement_fee';
    PREPARE procurement_retirement_check FROM @procurement_retirement_check_sql;
    EXECUTE procurement_retirement_check;
    DEALLOCATE PREPARE procurement_retirement_check;
    IF @procurement_retirement_financial_rows<>0 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Legacy procurement financial records remain; do not delete financial evidence';
    END IF;
  END IF;
  IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='ll_procurement_wallet_attempt') THEN
    SET @procurement_retirement_financial_rows=0;
    SET @procurement_retirement_check_sql='SELECT COUNT(*) INTO @procurement_retirement_financial_rows FROM baiyue_lastleg.ll_procurement_wallet_attempt';
    PREPARE procurement_retirement_check FROM @procurement_retirement_check_sql;
    EXECUTE procurement_retirement_check;
    DEALLOCATE PREPARE procurement_retirement_check;
    IF @procurement_retirement_financial_rows<>0 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Legacy procurement financial records remain; do not delete financial evidence';
    END IF;
  END IF;
  -- 不关闭外键校验；来自其他业务表的外键也会使这条精确删除失败。
  DROP TABLE IF EXISTS
    ll_procurement_closure_item,
    ll_procurement_closure,
    ll_procurement_control_note,
    ll_procurement_fee_correction_item,
    ll_procurement_fee_correction,
    ll_procurement_repack,
    ll_procurement_estimate_review,
    ll_procurement_label_item,
    ll_procurement_label_set,
    ll_procurement_shipment_item,
    ll_procurement_shipment,
    ll_procurement_carton_source,
    ll_procurement_carton,
    ll_procurement_manifest,
    ll_procurement_disposition,
    ll_procurement_receipt,
    ll_procurement_budget_authorization,
    ll_procurement_command,
    ll_procurement_flow,
    ll_procurement_wallet_attempt,
    ll_overseas_procurement_fee;
END$$
CALL retire_procurement_legacy_tables()$$
DROP PROCEDURE retire_procurement_legacy_tables$$
DELIMITER ;

-- 若上面任一步失败必须停止。剩余旧表数应为 0；此 SELECT 不代替客户端退出码核对。
SELECT COUNT(*) AS remaining_legacy_procurement_tables
FROM information_schema.tables WHERE table_schema='baiyue_lastleg' AND table_name IN (
'll_overseas_procurement_fee',
'll_procurement_wallet_attempt',
'll_procurement_flow',
'll_procurement_command',
'll_procurement_budget_authorization',
'll_procurement_receipt',
'll_procurement_disposition',
'll_procurement_manifest',
'll_procurement_carton',
'll_procurement_carton_source',
'll_procurement_shipment',
'll_procurement_shipment_item',
'll_procurement_label_set',
'll_procurement_label_item',
'll_procurement_estimate_review',
'll_procurement_repack',
'll_procurement_fee_correction',
'll_procurement_fee_correction_item',
'll_procurement_control_note',
'll_procurement_closure',
'll_procurement_closure_item');