AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.260.sql

-- 1.0.260: Phase 2/3 — S2/S4 stg 对齐 canonical；全模块 Mock _API 实体登记
-- 幂等：information_schema 守卫

-- ========== S2 mdp_stg_schedule ==========
SET @ddl := (SELECT IF(
  EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_schedule' AND COLUMN_NAME='create_time'),
  'SELECT 1',
  'ALTER TABLE mdp_stg_schedule ADD COLUMN create_time datetime NULL DEFAULT CURRENT_TIMESTAMP'));
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

SET @ddl := (SELECT IF(
  EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_schedule' AND COLUMN_NAME='process_message'),
  'SELECT 1',
  'ALTER TABLE mdp_stg_schedule ADD COLUMN process_message varchar(500) NULL'));
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

UPDATE mdp_stg_schedule
SET source_biz_key = IFNULL(NULLIF(source_biz_key, ''), IFNULL(source_row_id, CONCAT('row-', id)))
WHERE source_biz_key IS NULL OR source_biz_key = '';

SET @ddl := (SELECT IF(
  EXISTS(SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_schedule' AND INDEX_NAME='uk_source_key'),
  'SELECT 1',
  'ALTER TABLE mdp_stg_schedule ADD UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key)'));
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

-- ========== S4 stg tables ==========
-- mdp_stg_s4_iqc
SET @ddl := (SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_s4_iqc' AND COLUMN_NAME='create_time'),'SELECT 1','ALTER TABLE mdp_stg_s4_iqc ADD COLUMN create_time datetime NULL DEFAULT CURRENT_TIMESTAMP'));
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
SET @ddl := (SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_s4_iqc' AND COLUMN_NAME='process_message'),'SELECT 1','ALTER TABLE mdp_stg_s4_iqc ADD COLUMN process_message varchar(500) NULL'));
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
UPDATE mdp_stg_s4_iqc SET source_biz_key = IFNULL(NULLIF(source_biz_key, ''), IFNULL(source_row_id, CONCAT('row-', id))) WHERE source_biz_key IS NULL OR source_biz_key = '';
SET @ddl := (SELECT IF(EXISTS(SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_s4_iqc' AND INDEX_NAME='uk_source_key'),'SELECT 1','ALTER TABLE mdp_stg_s4_iqc ADD UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key)'));
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

-- mdp_stg_s4_shipment
SET @ddl := (SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_s4_shipment' AND COLUMN_NAME='create_time'),'SELECT 1','ALTER TABLE mdp_stg_s4_shipment ADD COLUMN create_time datetime NULL DEFAULT CURRENT_TIMESTAMP'));
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
SET @ddl := (SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_s4_shipment' AND COLUMN_NAME='process_message'),'SELECT 1','ALTER TABLE mdp_stg_s4_shipment ADD COLUMN process_message varchar(500) NULL'));
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
UPDATE mdp_stg_s4_shipment SET source_biz_key = IFNULL(NULLIF(source_biz_key, ''), IFNULL(source_row_id, CONCAT('row-', id))) WHERE source_biz_key IS NULL OR source_biz_key = '';
SET @ddl := (SELECT IF(EXISTS(SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_s4_shipment' AND INDEX_NAME='uk_source_key'),'SELECT 1','ALTER TABLE mdp_stg_s4_shipment ADD UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key)'));
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

-- mdp_stg_s4_return
SET @ddl := (SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_s4_return' AND COLUMN_NAME='create_time'),'SELECT 1','ALTER TABLE mdp_stg_s4_return ADD COLUMN create_time datetime NULL DEFAULT CURRENT_TIMESTAMP'));
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
SET @ddl := (SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_s4_return' AND COLUMN_NAME='process_message'),'SELECT 1','ALTER TABLE mdp_stg_s4_return ADD COLUMN process_message varchar(500) NULL'));
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
UPDATE mdp_stg_s4_return SET source_biz_key = IFNULL(NULLIF(source_biz_key, ''), IFNULL(source_row_id, CONCAT('row-', id))) WHERE source_biz_key IS NULL OR source_biz_key = '';
SET @ddl := (SELECT IF(EXISTS(SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_s4_return' AND INDEX_NAME='uk_source_key'),'SELECT 1','ALTER TABLE mdp_stg_s4_return ADD UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key)'));
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

-- mdp_stg_s4_shortage
SET @ddl := (SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_s4_shortage' AND COLUMN_NAME='create_time'),'SELECT 1','ALTER TABLE mdp_stg_s4_shortage ADD COLUMN create_time datetime NULL DEFAULT CURRENT_TIMESTAMP'));
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
SET @ddl := (SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_s4_shortage' AND COLUMN_NAME='process_message'),'SELECT 1','ALTER TABLE mdp_stg_s4_shortage ADD COLUMN process_message varchar(500) NULL'));
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
UPDATE mdp_stg_s4_shortage SET source_biz_key = IFNULL(NULLIF(source_biz_key, ''), IFNULL(source_row_id, CONCAT('row-', id))) WHERE source_biz_key IS NULL OR source_biz_key = '';
-- dedupe before unique key (legacy SyncStaging may leave duplicate biz keys)
UPDATE mdp_stg_s4_shortage s
INNER JOIN (
  SELECT source_system, source_table, source_biz_key, MAX(id) AS keep_id
  FROM mdp_stg_s4_shortage
  GROUP BY source_system, source_table, source_biz_key HAVING COUNT(*)>1
) d ON s.source_system=d.source_system AND s.source_table=d.source_table
   AND IFNULL(s.source_biz_key,'')=IFNULL(d.source_biz_key,'') AND s.id<>d.keep_id
SET s.source_biz_key=CONCAT(IFNULL(s.source_biz_key,''), '#id-', s.id);

SET @ddl := (SELECT IF(EXISTS(SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_s4_shortage' AND INDEX_NAME='uk_source_key'),'SELECT 1','ALTER TABLE mdp_stg_s4_shortage ADD UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key)'));
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

-- ========== Phase2 Mock _API 实体（关键对象）==========
INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type,
   source_table_name, source_api_path, target_table_name,
   sync_mode, batch_size, response_data_path, dedup_key_path, biz_key_expr,
   status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='WMS_API'),
       v.code, v.name, 'API', v.src_tbl, v.path, v.stg,
       'INCREMENTAL', 1000, 'data.list', 'bizKey', v.biz,
       1, 'Phase2 API', NOW(), NOW()
FROM (
  SELECT 'S1_SEORDER_API' code, 'S1销售订单API' name, 'crm_seorder' src_tbl, '/api/sales-order' path, 'mdp_stg_so' stg, 'bill_no' biz
  UNION ALL SELECT 'S1_ASN_SHIPPER_DETAIL_API','S1发货明细API','ASNBOLShipperDetail','/api/shipment','mdp_stg_ship_trans','Id,Line'
  UNION ALL SELECT 'S2_WORK_ORDER_MASTER_API','S2工单主API','WorkOrdMaster','/api/work-order','mdp_stg_schedule','Domain,WorkOrd'
  UNION ALL SELECT 'S3_PURCHASE_ORDER_DETAIL_API','S3采购订单明细API','PurOrdDetail','/api/purchase-order','mdp_stg_purchase_order','Domain,PurOrd,Line'
  UNION ALL SELECT 'S3_SUPPLY_DEMAND_API','S3供需API','ic_demandschedule','/api/supply-demand','mdp_stg_supply_demand','Id'
  UNION ALL SELECT 'S3_DELIVERY_PLAN_API','S3交货计划API','srm_polist_ds','/api/delivery','mdp_stg_delivery','dsnum'
  UNION ALL SELECT 'S3_SUPPLIER_API','S3供应商API','SuppMaster','/api/supplier','mdp_stg_supplier','Domain,Supp'
  UNION ALL SELECT 'S3_ITEM_ERP_API','S3物料API','ItemMaster','/api/item','mdp_stg_item','Domain,ItemNum'
  UNION ALL SELECT 'S4_SHIPMENT_EXEC_API','S4发货API','scm_shdzb','/api/s4-shipment','mdp_stg_s4_shipment','glid,id'
  UNION ALL SELECT 'S4_IQC_RECEIPT_API','S4 IQC API','PurOrdRctDetail','/api/s4-iqc','mdp_stg_s4_iqc','Domain,Receiver,Line'
) v
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity e WHERE e.entity_code=v.code);