-- 1.0.263: 补齐剩余有 stg 目标的 *_API（除 T8 无 target 实体）
-- 顺带：S4 stg 若缺 create_time 则补齐（契约/Writer 兼容）
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_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_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;

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, '1.0.263 API pair', NOW(), NOW()
FROM (
  SELECT 'S1_CONTRACT_REVIEW_FLOW_API' code, 'S1合同评审流API' name, 'ado_contract_review_flow' src_tbl, '/api/contract-review-flow' path, 'mdp_stg_so' stg, 'ReviewBillNo,StageNo,RecID' biz
  UNION ALL SELECT 'S1_LINKAGE_PLAN_API','S1联动计划API','LinkagePlan','/api/linkage-plan','mdp_stg_ship_trans','bill_no,item_number,id'
  UNION ALL SELECT 'S1_PRODUCT_DESIGN_API','S1产品设计API','ado_product_design','/api/product-design','mdp_stg_so','BillNo'
  UNION ALL SELECT 'S1_PRODUCT_DESIGN_BOM_API','S1产品设计BOM API','ado_product_design_bom','/api/product-design-bom','mdp_stg_so','ProductDesignId,Id'
  UNION ALL SELECT 'S1_PRODUCT_DESIGN_ROUTING_API','S1产品设计工艺API','ado_product_design_routing','/api/product-design-routing','mdp_stg_so','ProductDesignId,Id'
  UNION ALL SELECT 'S1_REQUIREMENT_EXAMINE_DETAIL_API','S1需求核验明细API','b_bom_child_examine','/api/requirement-examine-detail','mdp_stg_so','examine_id,item_number,Id'
  UNION ALL SELECT 'S1_REQUIREMENT_EXAMINE_RESULT_API','S1需求核验结果API','b_examine_result','/api/requirement-examine-result','mdp_stg_so','bill_no,morder_no,Id'
  UNION ALL SELECT 'S1_SEORDER_CHANGE_API','S1订单变更API','crm_seorder_change','/api/sales-order-change','mdp_stg_so','bill_no,Id'
  UNION ALL SELECT 'S1_SHIPPING_PLAN_DETAIL_API','S1发货计划明细API','ShippingPlanDetail','/api/shipping-plan-detail','mdp_stg_ship_trans','plan_id,OrdNbr,RecID'
  UNION ALL SELECT 'S2_PERIOD_SEQUENCE_DET_API','S2期次序列API','PeriodSequenceDet','/api/period-sequence','mdp_stg_schedule','Domain,WorkOrds,Op,Sequence'
  UNION ALL SELECT 'S3_CONSIGNEE_SUPPLIER_API','S3收货地址API','ConsigneeAddressMaster','/api/consignee','mdp_stg_supplier','Domain,Address'
  UNION ALL SELECT 'S3_INVENTORY_API','S3库存API','InvMaster','/api/inventory','mdp_stg_work_order_material','Domain,ItemNum,Location'
  UNION ALL SELECT 'S3_ITEM_NEW_API','S3新物料API','ic_item','/api/item-new','mdp_stg_item','tenant_id,number'
  UNION ALL SELECT 'S3_RECEIPT_DETAIL_API','S3收货明细API','PurOrdRctDetail','/api/s3-receipt-detail','mdp_stg_receipt','Domain,Receiver,Line'
  UNION ALL SELECT 'S3_ROUTING_OUTSOURCE_API','S3委外工艺API','RoutingOpDetail','/api/routing-outsource','mdp_stg_work_order_material','Domain,RoutingCode,Op,SupplierCode'
  UNION ALL SELECT 'S3_SHIPPER_DETAIL_API','S3发货明细API','scm_shdzb','/api/shipper-detail','mdp_stg_delivery','glid,id'
  UNION ALL SELECT 'S3_SOURCE_LIST_API','S3货源清单API','srm_purchase','/api/source-list','mdp_stg_source_list','number,supplier_number'
  UNION ALL SELECT 'S3_WORK_ORDER_MASTER_API','S3工单主API','WorkOrdMaster','/api/s3-work-order','mdp_stg_work_order_material','Domain,WorkOrd'
  UNION ALL SELECT 'S3_WORK_ORDER_DETAIL_API','S3工单明细API','WorkOrdDetail','/api/s3-work-order-detail','mdp_stg_work_order_material','Domain,WorkOrd,Op,ItemNum'
  UNION ALL SELECT 'S3_WORK_ORDER_ROUTING_API','S3工单工艺API','WorkOrdRouting','/api/s3-work-order-routing','mdp_stg_work_order_material','Domain,WorkOrd,OP'
) v
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity e WHERE e.entity_code=v.code);
