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

-- 1.0.300：MES/WMS 对接 WP1 入站实体补齐 + WP3 Outbox 幂等索引 + WP8 热关注表
-- 说明：实体默认 status=0；启用源前须带外注入 db_password_enc。不改 165 任何结构。
-- 幂等：WHERE NOT EXISTS / CREATE TABLE IF NOT EXISTS。

-- ========== (1) 贴源表 ==========
CREATE TABLE IF NOT EXISTS `mdp_stg_label` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NOT NULL DEFAULT 0,
  `factory_id` varchar(64) DEFAULT NULL,
  `source_system` varchar(50) NOT NULL DEFAULT 'AIDOP',
  `source_table` varchar(100) NOT NULL,
  `source_row_id` varchar(100) NOT NULL,
  `source_biz_key` varchar(200) DEFAULT NULL,
  `sync_batch_id` varchar(100) NOT NULL,
  `sync_time` datetime NOT NULL,
  `process_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `raw_data` json NOT NULL,
  `process_message` varchar(500) DEFAULT NULL,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stg_label` (`tenant_id`,`source_table`,`source_row_id`),
  KEY `idx_stg_label_biz` (`source_biz_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `mdp_stg_inventory` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NOT NULL DEFAULT 0,
  `factory_id` varchar(64) DEFAULT NULL,
  `source_system` varchar(50) NOT NULL DEFAULT 'AIDOP',
  `source_table` varchar(100) NOT NULL,
  `source_row_id` varchar(100) NOT NULL,
  `source_biz_key` varchar(200) DEFAULT NULL,
  `sync_batch_id` varchar(100) NOT NULL,
  `sync_time` datetime NOT NULL,
  `process_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `raw_data` json NOT NULL,
  `process_message` varchar(500) DEFAULT NULL,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stg_inventory` (`tenant_id`,`source_table`,`source_row_id`),
  KEY `idx_stg_inventory_biz` (`source_biz_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `mdp_stg_inv_trans` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NOT NULL DEFAULT 0,
  `factory_id` varchar(64) DEFAULT NULL,
  `source_system` varchar(50) NOT NULL DEFAULT 'AIDOP',
  `source_table` varchar(100) NOT NULL,
  `source_row_id` varchar(100) NOT NULL,
  `source_biz_key` varchar(200) DEFAULT NULL,
  `sync_batch_id` varchar(100) NOT NULL,
  `sync_time` datetime NOT NULL,
  `process_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `raw_data` json NOT NULL,
  `process_message` varchar(500) DEFAULT NULL,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stg_inv_trans` (`tenant_id`,`source_table`,`source_row_id`),
  KEY `idx_stg_inv_trans_biz` (`source_biz_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `mdp_stg_location` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NOT NULL DEFAULT 0,
  `factory_id` varchar(64) DEFAULT NULL,
  `source_system` varchar(50) NOT NULL DEFAULT 'AIDOP',
  `source_table` varchar(100) NOT NULL,
  `source_row_id` varchar(100) NOT NULL,
  `source_biz_key` varchar(200) DEFAULT NULL,
  `sync_batch_id` varchar(100) NOT NULL,
  `sync_time` datetime NOT NULL,
  `process_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `raw_data` json NOT NULL,
  `process_message` varchar(500) DEFAULT NULL,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stg_location` (`tenant_id`,`source_table`,`source_row_id`),
  KEY `idx_stg_location_biz` (`source_biz_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `mdp_stg_customer` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NOT NULL DEFAULT 0,
  `factory_id` varchar(64) DEFAULT NULL,
  `source_system` varchar(50) NOT NULL DEFAULT 'AIDOP',
  `source_table` varchar(100) NOT NULL,
  `source_row_id` varchar(100) NOT NULL,
  `source_biz_key` varchar(200) DEFAULT NULL,
  `sync_batch_id` varchar(100) NOT NULL,
  `sync_time` datetime NOT NULL,
  `process_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `raw_data` json NOT NULL,
  `process_message` varchar(500) DEFAULT NULL,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stg_customer` (`tenant_id`,`source_table`,`source_row_id`),
  KEY `idx_stg_customer_biz` (`source_biz_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `mdp_stg_line_status` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NOT NULL DEFAULT 0,
  `factory_id` varchar(64) DEFAULT NULL,
  `source_system` varchar(50) NOT NULL DEFAULT 'AIDOP',
  `source_table` varchar(100) NOT NULL,
  `source_row_id` varchar(100) NOT NULL,
  `source_biz_key` varchar(200) DEFAULT NULL,
  `sync_batch_id` varchar(100) NOT NULL,
  `sync_time` datetime NOT NULL,
  `process_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `raw_data` json NOT NULL,
  `process_message` varchar(500) DEFAULT NULL,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stg_line_status` (`tenant_id`,`source_table`,`source_row_id`),
  KEY `idx_stg_line_status_biz` (`source_biz_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `mdp_stg_emp_work` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NOT NULL DEFAULT 0,
  `factory_id` varchar(64) DEFAULT NULL,
  `source_system` varchar(50) NOT NULL DEFAULT 'AIDOP',
  `source_table` varchar(100) NOT NULL,
  `source_row_id` varchar(100) NOT NULL,
  `source_biz_key` varchar(200) DEFAULT NULL,
  `sync_batch_id` varchar(100) NOT NULL,
  `sync_time` datetime NOT NULL,
  `process_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `raw_data` json NOT NULL,
  `process_message` varchar(500) DEFAULT NULL,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stg_emp_work` (`tenant_id`,`source_table`,`source_row_id`),
  KEY `idx_stg_emp_work_biz` (`source_biz_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `mdp_stg_mobile_task` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NOT NULL DEFAULT 0,
  `factory_id` varchar(64) DEFAULT NULL,
  `source_system` varchar(50) NOT NULL DEFAULT 'AIDOP',
  `source_table` varchar(100) NOT NULL,
  `source_row_id` varchar(100) NOT NULL,
  `source_biz_key` varchar(200) DEFAULT NULL,
  `sync_batch_id` varchar(100) NOT NULL,
  `sync_time` datetime NOT NULL,
  `process_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `raw_data` json NOT NULL,
  `process_message` varchar(500) DEFAULT NULL,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stg_mobile_task` (`tenant_id`,`source_table`,`source_row_id`),
  KEY `idx_stg_mobile_task_biz` (`source_biz_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `mdp_stg_line` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NOT NULL DEFAULT 0,
  `factory_id` varchar(64) DEFAULT NULL,
  `source_system` varchar(50) NOT NULL DEFAULT 'AIDOP',
  `source_table` varchar(100) NOT NULL,
  `source_row_id` varchar(100) NOT NULL,
  `source_biz_key` varchar(200) DEFAULT NULL,
  `sync_batch_id` varchar(100) NOT NULL,
  `sync_time` datetime NOT NULL,
  `process_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `raw_data` json NOT NULL,
  `process_message` varchar(500) DEFAULT NULL,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stg_line` (`tenant_id`,`source_table`,`source_row_id`),
  KEY `idx_stg_line_biz` (`source_biz_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `mdp_stg_line_run_rest` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NOT NULL DEFAULT 0,
  `factory_id` varchar(64) DEFAULT NULL,
  `source_system` varchar(50) NOT NULL DEFAULT 'AIDOP',
  `source_table` varchar(100) NOT NULL,
  `source_row_id` varchar(100) NOT NULL,
  `source_biz_key` varchar(200) DEFAULT NULL,
  `sync_batch_id` varchar(100) NOT NULL,
  `sync_time` datetime NOT NULL,
  `process_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `raw_data` json NOT NULL,
  `process_message` varchar(500) DEFAULT NULL,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stg_line_run_rest` (`tenant_id`,`source_table`,`source_row_id`),
  KEY `idx_stg_line_run_rest_biz` (`source_biz_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `mdp_stg_employee` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NOT NULL DEFAULT 0,
  `factory_id` varchar(64) DEFAULT NULL,
  `source_system` varchar(50) NOT NULL DEFAULT 'AIDOP',
  `source_table` varchar(100) NOT NULL,
  `source_row_id` varchar(100) NOT NULL,
  `source_biz_key` varchar(200) DEFAULT NULL,
  `sync_batch_id` varchar(100) NOT NULL,
  `sync_time` datetime NOT NULL,
  `process_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `raw_data` json NOT NULL,
  `process_message` varchar(500) DEFAULT NULL,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stg_employee` (`tenant_id`,`source_table`,`source_row_id`),
  KEY `idx_stg_employee_biz` (`source_biz_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `mdp_stg_emp_duty` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NOT NULL DEFAULT 0,
  `factory_id` varchar(64) DEFAULT NULL,
  `source_system` varchar(50) NOT NULL DEFAULT 'AIDOP',
  `source_table` varchar(100) NOT NULL,
  `source_row_id` varchar(100) NOT NULL,
  `source_biz_key` varchar(200) DEFAULT NULL,
  `sync_batch_id` varchar(100) NOT NULL,
  `sync_time` datetime NOT NULL,
  `process_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `raw_data` json NOT NULL,
  `process_message` varchar(500) DEFAULT NULL,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stg_emp_duty` (`tenant_id`,`source_table`,`source_row_id`),
  KEY `idx_stg_emp_duty_biz` (`source_biz_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ========== (2) 热关注表 ==========
CREATE TABLE IF NOT EXISTS `ado_mdp_hot_watch` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NOT NULL DEFAULT 0,
  `biz_type` varchar(40) NOT NULL,
  `biz_key` varchar(100) NOT NULL,
  `domain` varchar(40) NOT NULL DEFAULT '8010',
  `watch_tables` text,
  `enroll_time` datetime NOT NULL,
  `last_poll_time` datetime DEFAULT NULL,
  `last_snapshot_hash` varchar(64) DEFAULT NULL,
  `poll_interval_sec` int NOT NULL DEFAULT 5,
  `status` int NOT NULL DEFAULT 0,
  `terminate_time` datetime DEFAULT NULL,
  `terminate_reason` varchar(200) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_hot_watch_poll` (`status`,`last_poll_time`),
  KEY `idx_hot_watch_biz` (`tenant_id`,`biz_type`,`biz_key`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='MDP热回读在途关注';

-- ========== (3) Outbox 幂等唯一索引 ==========
SET @idx_exists := (
  SELECT COUNT(1) FROM information_schema.statistics
   WHERE table_schema = DATABASE() AND table_name='mdp_outbox' AND index_name='uk_mdp_outbox_idem'
);
SET @sql := IF(@idx_exists=0,
  'ALTER TABLE mdp_outbox ADD UNIQUE KEY uk_mdp_outbox_idem (idem_key)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ========== (4) 入站实体登记（status=0）==========
INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S5_LABEL_MISSEDPRINT_SQLSERVER', 'S5标签MissedPrint入站(SQLServer)', 'TABLE',
       'MissedPrint', 'mdp_stg_label',
       'INCR', 'UpdateTime', 5000, 'Domain,BarCode', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S5_LABEL_MISSEDPRINT_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S5_LABEL_SINGLE_SQLSERVER', 'S5单件条码入站(SQLServer)', 'TABLE',
       'SingleBarCode', 'mdp_stg_label',
       'INCR', 'UpdateTime', 5000, 'Domain,BarCode', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S5_LABEL_SINGLE_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S5_INV_MASTER_SQLSERVER', 'S5库存汇总入站(SQLServer)', 'TABLE',
       'InvMaster', 'mdp_stg_inventory',
       'INCR', 'UpdateTime', 5000, 'Domain,ItemNum,Location', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S5_INV_MASTER_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S5_INV_TRANS_HIST_SQLSERVER', 'S5进出存入站(SQLServer)', 'TABLE',
       'InvTransHist', 'mdp_stg_inv_trans',
       'INCR', 'CreateTime', 2000, 'RecID', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S5_INV_TRANS_HIST_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S5_LOCATION_DETAIL_SQLSERVER', 'S5库位明细入站(SQLServer)', 'TABLE',
       'LocationDetail', 'mdp_stg_inventory',
       'INCR', 'UpdateTime', 5000, 'Domain,ItemNum,Location,LotSerial', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S5_LOCATION_DETAIL_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S5_LOCATION_MASTER_SQLSERVER', 'S5库位主数据入站(SQLServer)', 'TABLE',
       'LocationMaster', 'mdp_stg_location',
       'FULL', NULL, 5000, 'Domain,Location', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S5_LOCATION_MASTER_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S3_ITEM_MASTER_SQLSERVER', 'S3物料主数据入站(SQLServer)', 'TABLE',
       'ItemMaster', 'mdp_stg_item',
       'INCR', 'UpdateTime', 5000, 'Domain,ItemNum', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S3_ITEM_MASTER_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S3_SUPPLIER_SQLSERVER', 'S3供应商入站(SQLServer)', 'TABLE',
       'SuppMaster', 'mdp_stg_supplier',
       'INCR', 'UpdateTime', 5000, 'Domain,Supplier', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S3_SUPPLIER_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S1_CUSTOMER_SQLSERVER', 'S1客户主数据入站(SQLServer)', 'TABLE',
       'CustMaster', 'mdp_stg_customer',
       'INCR', 'UpdateTime', 5000, 'Domain,Customer', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S1_CUSTOMER_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S2_WORK_ORDER_MASTER_SQLSERVER', 'S2工单主入站(SQLServer)', 'TABLE',
       'WorkOrdMaster', 'mdp_stg_schedule',
       'INCR', 'UpdateTime', 5000, 'Domain,WorkOrd', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S2_WORK_ORDER_MASTER_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S2_WORK_ORDER_DETAIL_SQLSERVER', 'S2工单明细入站(SQLServer)', 'TABLE',
       'WorkOrdDetail', 'mdp_stg_schedule',
       'INCR', 'UpdateTime', 5000, 'Domain,WorkOrd,ItemNum', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S2_WORK_ORDER_DETAIL_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S2_WORK_ORDER_ROUTING_SQLSERVER', 'S2工单工序入站(SQLServer)', 'TABLE',
       'WorkOrdRouting', 'mdp_stg_schedule',
       'INCR', 'UpdateTime', 5000, 'Domain,WorkOrd,OP', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S2_WORK_ORDER_ROUTING_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S3_PURCHASE_ORDER_MASTER_SQLSERVER', 'S3采购主入站(SQLServer)', 'TABLE',
       'PurOrdMaster', 'mdp_stg_purchase_order',
       'INCR', 'UpdateTime', 5000, 'Domain,PurOrd', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S3_PURCHASE_ORDER_MASTER_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S3_PURCHASE_ORDER_DETAIL_SQLSERVER', 'S3采购明细入站(SQLServer)', 'TABLE',
       'PurOrdDetail', 'mdp_stg_purchase_order',
       'INCR', 'UpdateTime', 5000, 'Domain,PurOrd,Line', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S3_PURCHASE_ORDER_DETAIL_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S3_PURCHASE_ORDER_BATCH_SQLSERVER', 'S3采购批次入站(SQLServer)', 'TABLE',
       'PurOrdDetailBatch', 'mdp_stg_purchase_order',
       'INCR', 'UpdateTime', 5000, 'Domain,PurOrd,Line,Batch', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S3_PURCHASE_ORDER_BATCH_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S2_PERIOD_SEQUENCE_SQLSERVER', 'S2日计划入站(SQLServer)', 'TABLE',
       'PeriodSequenceDet', 'mdp_stg_schedule',
       'INCR', 'UpdateTime', 5000, 'Domain,ItemNum,PlanDate,Line,Op', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S2_PERIOD_SEQUENCE_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S3_DELIVERY_PLAN_SQLSERVER', 'S3交货单入站(SQLServer)', 'TABLE',
       'srm_polist_ds', 'mdp_stg_delivery',
       'INCR', 'UpdateTime', 5000, 'Domain,ds_bill', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S3_DELIVERY_PLAN_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S1_ASN_MASTER_SQLSERVER', 'S1发货主入站(SQLServer)', 'TABLE',
       'ASNBOLShipperMaster', 'mdp_stg_ship_trans',
       'INCR', 'UpdateTime', 5000, 'Domain,id', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S1_ASN_MASTER_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S1_ASN_DETAIL_SQLSERVER', 'S1发货明细入站(SQLServer)', 'TABLE',
       'ASNBOLShipperDetail', 'mdp_stg_ship_trans',
       'INCR', 'UpdateTime', 5000, 'Domain,id,Line', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S1_ASN_DETAIL_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S5_IQC_INSPBILL_SQLSERVER', 'S5 IQC检验单入站(SQLServer)', 'TABLE',
       'qms_qcp_inspbill', 'mdp_stg_iqc_pull',
       'FULL', NULL, 2000, 'Domain,BillNbr', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S5_IQC_INSPBILL_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S6_LINE_STATUS_SQLSERVER', 'S6产线状态入站(SQLServer)', 'TABLE',
       'LineStatusDet', 'mdp_stg_line_status',
       'INCR', 'UpdateTime', 2000, 'Domain,Line,ItemNum,WorkOrd,OP', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S6_LINE_STATUS_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S6_EMPLOYEE_STATUS_SQLSERVER', 'S6人员在岗入站(SQLServer)', 'TABLE',
       'EmployeeStatus', 'mdp_stg_line_status',
       'INCR', 'UpdateTime', 2000, 'Domain,Employee', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S6_EMPLOYEE_STATUS_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S6_EMP_WORK_HIST_SQLSERVER', 'S6报工履历入站(SQLServer)', 'TABLE',
       'EmpWorkHist', 'mdp_stg_emp_work',
       'INCR', 'CreateTime', 2000, 'RecID', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S6_EMP_WORK_HIST_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S5_MOBILE_TASK_SQLSERVER', 'S5待办任务入站(SQLServer)', 'TABLE',
       'MobileTask', 'mdp_stg_mobile_task',
       'INCR', 'CreateTime', 2000, 'TaskID', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S5_MOBILE_TASK_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S5_MOBILE_TASK_HIST_SQLSERVER', 'S5任务历史入站(SQLServer)', 'TABLE',
       'MobileTaskHistory', 'mdp_stg_mobile_task',
       'INCR', 'CreateTime', 2000, 'RecID', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S5_MOBILE_TASK_HIST_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S6_LINE_MASTER_SQLSERVER', 'S6产线主数据入站(SQLServer)', 'TABLE',
       'LineMaster', 'mdp_stg_line',
       'FULL', NULL, 2000, 'Domain,Line', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S6_LINE_MASTER_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S6_PROD_LINE_DETAIL_SQLSERVER', 'S6产线工序配置入站(SQLServer)', 'TABLE',
       'ProdLineDetail', 'mdp_stg_line',
       'FULL', NULL, 2000, 'Domain,Line,Part,OP', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S6_PROD_LINE_DETAIL_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S6_LINE_RUN_REST_SQLSERVER', 'S6运行休息入站(SQLServer)', 'TABLE',
       'LineRunRestDet', 'mdp_stg_line_run_rest',
       'INCR', 'CreateTime', 2000, 'RecID', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S6_LINE_RUN_REST_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S6_EMPLOYEE_MASTER_SQLSERVER', 'S6雇员入站(SQLServer)', 'TABLE',
       'EmployeeMaster', 'mdp_stg_employee',
       'FULL', NULL, 2000, 'Domain,Employee', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S6_EMPLOYEE_MASTER_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S5_EMP_WORK_WMS_HIST_SQLSERVER', 'S5仓储作业履历入站(SQLServer)', 'TABLE',
       'EmpWorkWmsHist', 'mdp_stg_emp_work',
       'INCR', 'CreateTime', 2000, 'RecID', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S5_EMP_WORK_WMS_HIST_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S6_RELEASE_DET_SQLSERVER', 'S6报工释放入站(SQLServer)', 'TABLE',
       'ReleaseDet', 'mdp_stg_schedule',
       'INCR', 'UpdateTime', 2000, 'Domain,WorkOrd,OP', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S6_RELEASE_DET_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S2_WORK_ORDER_ISSUED_SQLSERVER', 'S2工单领用入站(SQLServer)', 'TABLE',
       'WorkOrdIssued', 'mdp_stg_schedule',
       'INCR', 'UpdateTime', 2000, 'Domain,WorkOrd,ItemNum', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S2_WORK_ORDER_ISSUED_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S2_WORK_ORDER_IN_STORAGE_SQLSERVER', 'S2完工入库入站(SQLServer)', 'TABLE',
       'WorkOrdInStorage', 'mdp_stg_schedule',
       'INCR', 'UpdateTime', 2000, 'Domain,WorkOrd', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S2_WORK_ORDER_IN_STORAGE_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S5_LOCATION_SHELF_SQLSERVER', 'S5货架入站(SQLServer)', 'TABLE',
       'LocationShelfMaster', 'mdp_stg_location',
       'FULL', NULL, 5000, 'Domain,Location,InvShelf', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S5_LOCATION_SHELF_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S5_EMP_WORK_DUTY_SQLSERVER', 'S5物料职责入站(SQLServer)', 'TABLE',
       'EmpWorkDutyMaster', 'mdp_stg_emp_duty',
       'FULL', NULL, 2000, 'Domain,RecID', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S5_EMP_WORK_DUTY_SQLSERVER');

INSERT INTO mdp_entity
  (tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name,
   sync_mode, incr_column, batch_size, biz_key_expr, status, remark, create_time, update_time)
SELECT 0, (SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER'),
       'S5_LINE_PRINTER_SQLSERVER', 'S5产线打印机入站(SQLServer)', 'TABLE',
       'LinePrinter', 'mdp_stg_emp_duty',
       'FULL', NULL, 2000, 'Domain,RecID', 0,
       'WP1 MES/WMS 冷链实体 disabled', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM mdp_entity WHERE entity_code='S5_LINE_PRINTER_SQLSERVER');

-- ========== (5) 启用提示（不自动启用：密码须带外注入）==========
-- UPDATE mdp_source SET status=1, db_password_enc='<密文>' WHERE source_code='DOPDEMORQ_SQLSERVER';
-- UPDATE mdp_entity SET status=1 WHERE source_id=(SELECT id FROM mdp_source WHERE source_code='DOPDEMORQ_SQLSERVER');