-- 只读：三份补丁成功后、仍停写时执行。所有 status=OK、异常计数=0；还需原字段快照一致。
SELECT DATABASE() AS db_name, VERSION() AS mysql_version;

WITH expected AS (
  SELECT 'biz_pallet_inventory' AS tab, 'tunnel_id' AS col, 'bigint' AS typ, 'YES' AS nullable
  UNION ALL SELECT 'biz_pallet_inventory', 'trace_no', 'varchar(64)', 'YES'
  UNION ALL SELECT 'biz_pallet_inventory', 'site_arrived_at', 'datetime', 'YES'
  UNION ALL SELECT 'biz_usage_record', 'tunnel_id', 'bigint', 'YES'
  UNION ALL SELECT 'biz_usage_record', 'trace_no', 'varchar(64)', 'YES'
  UNION ALL SELECT 'biz_usage_record', 'batch_no_snapshot', 'varchar(64)', 'YES'
  UNION ALL SELECT 'biz_usage_record', 'fifo_skip_reason', 'varchar(500)', 'YES'
  UNION ALL SELECT 'biz_usage_record', 'fifo_recommendation', 'json', 'YES'
  UNION ALL SELECT 'biz_usage_record', 'line_id', 'bigint', 'YES'
  UNION ALL SELECT 'biz_return_item', 'tunnel_id_snapshot', 'bigint', 'YES'
  UNION ALL SELECT 'biz_return_item', 'trace_no_snapshot', 'varchar(64)', 'YES'
  UNION ALL SELECT 'biz_return_item', 'batch_no_snapshot', 'varchar(64)', 'YES'
  UNION ALL SELECT 'biz_pallet_event', 'before_tunnel_id', 'bigint', 'YES'
  UNION ALL SELECT 'biz_pallet_event', 'after_tunnel_id', 'bigint', 'YES'
  UNION ALL SELECT 'biz_pallet_event', 'trace_no', 'varchar(64)', 'YES'
  UNION ALL SELECT 'biz_pallet_event', 'batch_no_snapshot', 'varchar(64)', 'YES'
  UNION ALL SELECT 'biz_pallet_event', 'fifo_skip_reason', 'varchar(500)', 'YES'
  UNION ALL SELECT 'biz_pallet_event', 'fifo_recommendation', 'json', 'YES'
  UNION ALL SELECT 'biz_filling_record', 'production_date', 'date', 'YES'
  UNION ALL SELECT 'biz_filling_record', 'expiry_date', 'date', 'YES'
  UNION ALL SELECT 'biz_shipment_order', 'receipt_state', 'varchar(16)', 'NO'
  UNION ALL SELECT 'biz_shipment_order', 'received_by', 'bigint', 'YES'
  UNION ALL SELECT 'biz_shipment_order', 'received_at', 'datetime', 'YES'
  UNION ALL SELECT 'biz_shipment_order', 'receipt_idempotency_key', 'varchar(64)', 'YES'
  UNION ALL SELECT 'biz_shipment_order', 'receipt_request_hash', 'varchar(64)', 'YES'
)
SELECT e.tab, e.col, c.column_type, c.is_nullable,
       CASE WHEN c.column_name IS NULL THEN 'MISSING'
            WHEN c.column_type <> e.typ OR c.is_nullable <> e.nullable THEN 'MISMATCH'
            ELSE 'OK' END AS status
FROM expected e LEFT JOIN information_schema.columns c
  ON c.table_schema = DATABASE() AND c.table_name = e.tab AND c.column_name = e.col
ORDER BY e.tab, e.col;

WITH expected AS (
  SELECT 'biz_usage_record' AS tab, 'tunnel_id' AS col, 'biz_tunnel' AS ref_tab, 'tunnel_id' AS ref_col
  UNION ALL SELECT 'biz_return_item', 'tunnel_id_snapshot', 'biz_tunnel', 'tunnel_id'
  UNION ALL SELECT 'biz_pallet_inventory', 'tunnel_id', 'biz_tunnel', 'tunnel_id'
  UNION ALL SELECT 'biz_pallet_event', 'before_tunnel_id', 'biz_tunnel', 'tunnel_id'
  UNION ALL SELECT 'biz_pallet_event', 'after_tunnel_id', 'biz_tunnel', 'tunnel_id'
  UNION ALL SELECT 'biz_shipment_order', 'received_by', 'sys_user', 'user_id'
)
SELECT e.tab, e.col,
       CASE WHEN EXISTS (SELECT 1 FROM information_schema.key_column_usage k
                          WHERE k.table_schema = DATABASE() AND k.table_name = e.tab AND k.column_name = e.col
                            AND k.referenced_table_schema = DATABASE()
                            AND k.referenced_table_name = e.ref_tab AND k.referenced_column_name = e.ref_col)
            THEN 'OK' ELSE 'MISSING' END AS status
FROM expected e;

-- 三个 CHECK 必须存在且 ENFORCED=YES；人工核对枚举含 IN_TRANSIT、TUNNEL_ENTERED、SHIPMENT_RECEIVED。
SELECT t.table_name, t.constraint_name, t.enforced, c.check_clause
FROM information_schema.table_constraints t JOIN information_schema.check_constraints c
  ON c.constraint_schema = t.constraint_schema AND c.constraint_name = t.constraint_name
WHERE t.constraint_schema = DATABASE()
  AND t.constraint_name IN ('ck_biz_pallet_inventory_flow_state', 'ck_biz_pallet_event_event_type',
                            'ck_biz_shipment_order_receipt_state');
SELECT index_name, non_unique, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns_in_order
FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = 'biz_shipment_order'
  AND index_name = 'uq_biz_shipment_receipt_site_key'
GROUP BY index_name, non_unique;

-- 必须恰有5行且编号/父菜单/权限与操作说明一致；不应自动出现任何普通角色映射。
SELECT menu_id, parent_id, menu_type, perms FROM sys_menu
WHERE menu_id IN (520, 1116, 1117, 1118, 1119) ORDER BY menu_id;
SELECT COUNT(*) AS unexpected_role_grants FROM sys_role_menu WHERE menu_id IN (520, 1116, 1117, 1118, 1119);

SELECT COUNT(*) AS unexpected_receipt_history FROM biz_shipment_order
WHERE (status = 'CONFIRMED' AND receipt_state <> 'LEGACY')
   OR (status = 'DRAFT' AND receipt_state <> 'PENDING')
   OR received_by IS NOT NULL OR received_at IS NOT NULL
   OR receipt_idempotency_key IS NOT NULL OR receipt_request_hash IS NOT NULL;
SELECT COUNT(*) AS unexpected_inventory_history FROM biz_pallet_inventory
WHERE tunnel_id IS NOT NULL OR trace_no IS NOT NULL OR site_arrived_at IS NOT NULL;
SELECT COUNT(*) AS unexpected_usage_history FROM biz_usage_record
WHERE tunnel_id IS NOT NULL OR trace_no IS NOT NULL OR batch_no_snapshot IS NOT NULL
   OR fifo_skip_reason IS NOT NULL OR fifo_recommendation IS NOT NULL;
SELECT COUNT(*) AS unexpected_return_history FROM biz_return_item
WHERE tunnel_id_snapshot IS NOT NULL OR trace_no_snapshot IS NOT NULL OR batch_no_snapshot IS NOT NULL;
SELECT COUNT(*) AS unexpected_event_history FROM biz_pallet_event
WHERE before_tunnel_id IS NOT NULL OR after_tunnel_id IS NOT NULL OR trace_no IS NOT NULL
   OR batch_no_snapshot IS NOT NULL OR fifo_skip_reason IS NOT NULL OR fifo_recommendation IS NOT NULL;
SELECT COUNT(*) AS remaining_migration_routines FROM information_schema.routines
WHERE routine_schema = DATABASE() AND routine_name IN ('repair_prod_existing_tunnel_20261008',
      'migrate_shipment_receipt_20261008', 'migrate_usage_fifo_20261008');