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

-- 1.0.125.sql
-- S1-MDP-FOUNDATION-1
-- 为 S1 产销协同首批数据中台试点补齐基础落库对象与 mdp_entity 登记。
--
-- 范围：
--   1) 兜底创建通用 MDP 配置/日志表：mdp_source / mdp_entity / mdp_field_mapping / mdp_sync_log / mdp_transform_run_log。
--   2) 新增 S1 贴源层：mdp_stg_so / mdp_stg_ship_trans。
--   3) 新增 S1 标准层：mdp_std_so / mdp_std_ship_trans。
--   4) 新增 S1 DWD 明细宽表：dwd_ship_trans。
--   5) 登记 S1 首批源实体到 mdp_entity，并写入基础血缘字段映射。
--
-- 安全边界：
--   * 仅 CREATE TABLE IF NOT EXISTS、INSERT ... ON DUPLICATE KEY UPDATE 和 TEMPORARY TABLE。
--   * 不删除、不清空、不改写业务运行表；不切换任何接口读路径。
--   * 不修改 S2/S3/S4/S7 既有业务写入规则和数据口径。
--
-- 幂等性：
--   * 表结构使用 CREATE TABLE IF NOT EXISTS。
--   * mdp_source / mdp_entity / mdp_field_mapping 使用唯一键 upsert。
--   * 重复执行不会重复登记实体或字段映射。
--
-- 执行入口：AutoVersionUpdate.UseAutoVersionUpdate()，csproj Version=1.0.125 主节点首次启动时触发。
-- 2026-05-25

SET @tenant_id := 0;
SET @source_code := 'AIDOPDEV_MYSQL';

-- ─── 1) MDP 通用底座兜底 ───
CREATE TABLE IF NOT EXISTS mdp_source (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    source_code VARCHAR(50) NOT NULL,
    source_name VARCHAR(100) NOT NULL,
    source_type ENUM('DB','API') NOT NULL,
    status TINYINT DEFAULT 1,
    db_type ENUM('MySQL','SQLServer','Oracle','PostgreSQL'),
    db_host VARCHAR(200),
    db_port INT,
    db_name VARCHAR(100),
    db_user VARCHAR(100),
    db_password_enc VARCHAR(500),
    db_extra_params VARCHAR(500),
    api_base_url VARCHAR(500),
    api_auth_type ENUM('NONE','TOKEN','OAUTH2','APIKEY'),
    api_auth_config JSON,
    last_health_check DATETIME,
    health_status TINYINT DEFAULT 0,
    health_msg VARCHAR(500),
    remark VARCHAR(500),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_tenant_source (tenant_id, source_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='MDP数据源配置';

CREATE TABLE IF NOT EXISTS mdp_entity (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    source_id BIGINT NOT NULL,
    entity_code VARCHAR(100) NOT NULL,
    entity_name VARCHAR(200) NOT NULL,
    entity_type ENUM('TABLE','VIEW','API') NOT NULL DEFAULT 'TABLE',
    source_table_name VARCHAR(200),
    source_api_path VARCHAR(500),
    api_config_id BIGINT,
    target_table_name VARCHAR(200),
    sync_mode ENUM('FULL','INCR','CDC','PAGE','CURSOR','TIME_WINDOW','NONE') DEFAULT 'INCR',
    incr_column VARCHAR(100),
    batch_size INT DEFAULT 5000,
    response_data_path VARCHAR(200),
    dedup_key_path VARCHAR(200),
    last_cursor VARCHAR(500),
    last_sync_to DATETIME,
    job_id VARCHAR(100),
    status TINYINT DEFAULT 1,
    remark VARCHAR(500),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_tenant_entity (tenant_id, entity_code),
    KEY idx_source (source_id),
    KEY idx_api_config (api_config_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='MDP同步实体配置';

CREATE TABLE IF NOT EXISTS mdp_field_mapping (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    entity_id BIGINT NOT NULL,
    source_field VARCHAR(200) NOT NULL,
    target_field VARCHAR(200) NOT NULL,
    field_type ENUM('DIRECT','JSONPATH','SCRIPT','CONST','LOOKUP') DEFAULT 'DIRECT',
    transform_script TEXT,
    const_value VARCHAR(500),
    lookup_table VARCHAR(200),
    is_required TINYINT DEFAULT 0,
    default_value VARCHAR(500),
    sort_order INT DEFAULT 0,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_entity_field (entity_id, target_field)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='MDP字段映射配置';

CREATE TABLE IF NOT EXISTS mdp_sync_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    entity_id BIGINT NOT NULL,
    source_code VARCHAR(50) NOT NULL,
    entity_name VARCHAR(200),
    sync_batch_id VARCHAR(50) NOT NULL,
    sync_type ENUM('FULL','INCR') NOT NULL,
    trigger_type ENUM('AUTO','MANUAL') DEFAULT 'AUTO',
    sync_start DATETIME,
    sync_end DATETIME,
    duration_ms INT,
    rows_read BIGINT DEFAULT 0,
    rows_insert BIGINT DEFAULT 0,
    rows_update BIGINT DEFAULT 0,
    rows_skip BIGINT DEFAULT 0,
    rows_error BIGINT DEFAULT 0,
    status ENUM('RUNNING','SUCCESS','PARTIAL','FAILED') DEFAULT 'RUNNING',
    error_msg TEXT,
    error_sample JSON,
    KEY idx_tenant_entity_time (tenant_id, entity_id, sync_start),
    KEY idx_batch (sync_batch_id),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='MDP同步执行日志';

CREATE TABLE IF NOT EXISTS mdp_transform_run_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    job_code VARCHAR(100) NOT NULL,
    job_name VARCHAR(200) NOT NULL,
    trigger_type VARCHAR(30) NOT NULL DEFAULT 'AUTO',
    batch_id VARCHAR(100) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'RUNNING',
    start_time DATETIME NOT NULL,
    end_time DATETIME NULL,
    duration_ms INT NULL,
    stage_rows INT NOT NULL DEFAULT 0,
    standard_rows INT NOT NULL DEFAULT 0,
    dwd_rows INT NOT NULL DEFAULT 0,
    error_message TEXT NULL,
    summary_json JSON NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_batch (batch_id),
    KEY idx_job_start (job_code, start_time),
    KEY idx_status_start (status, start_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='MDP整轮同步转换运行日志';

-- ─── 2) S1 贴源层 ───
CREATE TABLE IF NOT EXISTS mdp_stg_so (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NULL,
    factory_id BIGINT NULL,
    company_id BIGINT NULL,
    source_system VARCHAR(50) NOT NULL,
    source_table VARCHAR(100) NOT NULL,
    source_row_id VARCHAR(200) NULL,
    source_biz_key VARCHAR(300) NOT NULL,
    sync_batch_id VARCHAR(64) NOT NULL,
    sync_time DATETIME NOT NULL,
    process_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    process_message VARCHAR(1000) NULL,
    raw_data JSON NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_batch_status (sync_batch_id, process_status),
    UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key),
    KEY idx_tenant_time (tenant_id, sync_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S1订单/评审贴源表';

CREATE TABLE IF NOT EXISTS mdp_stg_ship_trans LIKE mdp_stg_so;
ALTER TABLE mdp_stg_ship_trans COMMENT='S1发货/ASN/联动贴源表';

-- ─── 3) S1 标准层 ───
CREATE TABLE IF NOT EXISTS mdp_std_so (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    factory_id BIGINT NULL,
    company_id BIGINT NULL,
    source_system VARCHAR(50) NOT NULL,
    order_id BIGINT NULL,
    order_entry_id BIGINT NULL,
    order_no VARCHAR(100) NOT NULL,
    order_line VARCHAR(50) NULL,
    order_type VARCHAR(50) NULL,
    customer_id BIGINT NULL,
    customer_no VARCHAR(100) NULL,
    customer_name VARCHAR(200) NULL,
    customer_order_no VARCHAR(100) NULL,
    country VARCHAR(100) NULL,
    item_code VARCHAR(100) NULL,
    item_name VARCHAR(200) NULL,
    item_spec VARCHAR(300) NULL,
    map_number VARCHAR(300) NULL,
    map_name VARCHAR(300) NULL,
    bom_number VARCHAR(300) NULL,
    unit VARCHAR(50) NULL,
    order_qty DECIMAL(18,6) NULL,
    delivered_notice_qty DECIMAL(18,6) NULL,
    delivered_qty DECIMAL(18,6) NULL,
    price DECIMAL(18,6) NULL,
    tax_price DECIMAL(18,6) NULL,
    amount DECIMAL(18,6) NULL,
    total_amount DECIMAL(18,6) NULL,
    order_date DATETIME NULL,
    customer_request_date DATETIME NULL,
    plan_delivery_date DATETIME NULL,
    promised_delivery_date DATETIME NULL,
    capacity_date DATETIME NULL,
    material_ready_date DATETIME NULL,
    planner_no VARCHAR(100) NULL,
    planner_name VARCHAR(100) NULL,
    order_status VARCHAR(50) NULL,
    review_status VARCHAR(50) NULL,
    review_stage VARCHAR(100) NULL,
    flow_state VARCHAR(100) NULL,
    progress VARCHAR(50) NULL,
    urgent TINYINT NULL,
    closed TINYINT NULL,
    deleted_flag TINYINT NOT NULL DEFAULT 0,
    source_table VARCHAR(100) NOT NULL,
    source_row_id VARCHAR(200) NULL,
    source_biz_key VARCHAR(300) NOT NULL,
    sync_batch_id VARCHAR(64) NOT NULL,
    sync_time DATETIME NOT NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_source_key (tenant_id, source_system, source_table, source_biz_key),
    KEY idx_order_line (tenant_id, order_no, order_line),
    KEY idx_customer_date (tenant_id, customer_no, plan_delivery_date),
    KEY idx_item_date (tenant_id, item_code, plan_delivery_date),
    KEY idx_status_date (tenant_id, order_status, review_status, plan_delivery_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S1标准销售订单/评审对象';

CREATE TABLE IF NOT EXISTS mdp_std_ship_trans (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    factory_id BIGINT NULL,
    company_id BIGINT NULL,
    source_system VARCHAR(50) NOT NULL,
    trans_type VARCHAR(50) NOT NULL,
    plan_id BIGINT NULL,
    plan_no VARCHAR(100) NULL,
    plan_line VARCHAR(50) NULL,
    shipper_rec_id BIGINT NULL,
    shipper_no VARCHAR(100) NULL,
    shipper_line VARCHAR(50) NULL,
    order_id BIGINT NULL,
    order_entry_id BIGINT NULL,
    order_no VARCHAR(100) NULL,
    order_line VARCHAR(50) NULL,
    customer_no VARCHAR(100) NULL,
    customer_name VARCHAR(200) NULL,
    country VARCHAR(100) NULL,
    item_code VARCHAR(100) NULL,
    item_name VARCHAR(200) NULL,
    item_spec VARCHAR(300) NULL,
    qty DECIMAL(18,6) NULL,
    plan_qty DECIMAL(18,6) NULL,
    qty_to_ship DECIMAL(18,6) NULL,
    picking_qty DECIMAL(18,6) NULL,
    real_qty DECIMAL(18,6) NULL,
    weight DECIMAL(18,6) NULL,
    gross_weight DECIMAL(18,6) NULL,
    net_weight DECIMAL(18,6) NULL,
    volume DECIMAL(18,6) NULL,
    order_date DATETIME NULL,
    plan_ship_date DATETIME NULL,
    actual_ship_date DATETIME NULL,
    site VARCHAR(100) NULL,
    shipping_site VARCHAR(200) NULL,
    shipping_address VARCHAR(500) NULL,
    consignee VARCHAR(200) NULL,
    telephone VARCHAR(100) NULL,
    status VARCHAR(50) NULL,
    confirm_status VARCHAR(50) NULL,
    linkage_status VARCHAR(50) NULL,
    source_table VARCHAR(100) NOT NULL,
    source_row_id VARCHAR(200) NULL,
    source_biz_key VARCHAR(300) NOT NULL,
    sync_batch_id VARCHAR(64) NOT NULL,
    sync_time DATETIME NOT NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_source_key (tenant_id, source_system, source_table, source_biz_key),
    KEY idx_order_line (tenant_id, order_no, order_line),
    KEY idx_plan (tenant_id, plan_no, plan_line),
    KEY idx_shipper (tenant_id, shipper_no, shipper_line),
    KEY idx_ship_date (tenant_id, actual_ship_date, plan_ship_date),
    KEY idx_status_date (tenant_id, status, plan_ship_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S1标准发货/ASN/联动对象';

-- ─── 4) S1 DWD 明细宽表 ───
CREATE TABLE IF NOT EXISTS dwd_ship_trans (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    factory_id BIGINT NULL,
    company_id BIGINT NULL,
    stat_date DATE NOT NULL,
    order_id BIGINT NULL,
    order_entry_id BIGINT NULL,
    order_no VARCHAR(100) NOT NULL,
    order_line VARCHAR(50) NULL,
    customer_no VARCHAR(100) NULL,
    customer_name VARCHAR(200) NULL,
    country VARCHAR(100) NULL,
    item_code VARCHAR(100) NULL,
    item_name VARCHAR(200) NULL,
    item_spec VARCHAR(300) NULL,
    order_qty DECIMAL(18,6) NULL,
    planned_ship_qty DECIMAL(18,6) NULL,
    shipped_qty DECIMAL(18,6) NULL,
    remaining_qty DECIMAL(18,6) NULL,
    order_date DATETIME NULL,
    customer_request_date DATETIME NULL,
    plan_delivery_date DATETIME NULL,
    promised_delivery_date DATETIME NULL,
    plan_ship_date DATETIME NULL,
    actual_ship_date DATETIME NULL,
    review_status VARCHAR(50) NULL,
    order_status VARCHAR(50) NULL,
    delivery_status VARCHAR(50) NULL,
    linkage_status VARCHAR(50) NULL,
    risk_level VARCHAR(50) NULL,
    source_system VARCHAR(50) NULL,
    source_table VARCHAR(100) NULL,
    source_row_id VARCHAR(200) NULL,
    source_biz_key VARCHAR(300) NULL,
    sync_batch_id VARCHAR(64) NULL,
    calc_batch_id VARCHAR(64) NOT NULL,
    calc_time DATETIME NOT NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_order_line_stat (tenant_id, stat_date, order_no, order_line, item_code),
    KEY idx_order_line (tenant_id, order_no, order_line),
    KEY idx_customer_date (tenant_id, customer_no, stat_date),
    KEY idx_item_date (tenant_id, item_code, stat_date),
    KEY idx_status_date (tenant_id, delivery_status, risk_level, stat_date),
    KEY idx_batch (tenant_id, calc_batch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S1订单交付发货DWD宽表';

-- ─── 5) S1 数据源与实体登记 ───
INSERT INTO mdp_source
(tenant_id, source_code, source_name, source_type, status, db_type, db_host, db_port, db_name, db_user, db_password_enc, remark)
VALUES
(@tenant_id, @source_code, 'aidopdev 当前项目库', 'DB', 1, 'MySQL', NULL, 3306, 'aidopdev', NULL, NULL, 'S1产销协同首批迁移数据源，不在配置表保存明文密码')
ON DUPLICATE KEY UPDATE
    source_name = VALUES(source_name),
    source_type = VALUES(source_type),
    status = VALUES(status),
    db_type = VALUES(db_type),
    db_port = VALUES(db_port),
    db_name = VALUES(db_name),
    remark = VALUES(remark),
    update_time = CURRENT_TIMESTAMP;

SELECT @source_id := id
FROM mdp_source
WHERE tenant_id = @tenant_id
  AND source_code = @source_code
LIMIT 1;

CREATE TEMPORARY TABLE tmp_s1_mdp_entity (
    entity_code VARCHAR(100) PRIMARY KEY,
    entity_name VARCHAR(200) NOT NULL,
    source_table_name VARCHAR(200) NOT NULL,
    target_table_name VARCHAR(200) NOT NULL,
    sync_mode VARCHAR(20) NOT NULL,
    incr_column VARCHAR(100) NULL,
    source_row_id_expr VARCHAR(200) NULL,
    source_biz_key_expr VARCHAR(500) NOT NULL,
    remark VARCHAR(500) NULL
);

INSERT INTO tmp_s1_mdp_entity
(entity_code, entity_name, source_table_name, target_table_name, sync_mode, incr_column, source_row_id_expr, source_biz_key_expr, remark)
VALUES
('S1_SEORDER', 'S1销售订单主表', 'crm_seorder', 'mdp_stg_so', 'INCR', 'update_time', 'Id', 'COALESCE(bill_no, CAST(Id AS CHAR))', '订单主表，进入订单标准层'),
('S1_SEORDER_ENTRY', 'S1销售订单明细', 'crm_seorderentry', 'mdp_stg_so', 'INCR', 'update_time', 'Id', 'CONCAT(COALESCE(bill_no, ''''), '':'', COALESCE(CAST(entry_seq AS CHAR), CAST(Id AS CHAR)))', '订单明细，进入订单标准层'),
('S1_SEORDER_CHANGE', 'S1销售订单变更', 'crm_seorder_change', 'mdp_stg_so', 'INCR', 'update_time', 'Id', 'CONCAT(COALESCE(bill_no, ''''), '':'', CAST(Id AS CHAR))', '订单变更，进入订单评审/状态上下文'),
('S1_CONTRACT_REVIEW', 'S1合同评审主表', 'ado_contract_review', 'mdp_stg_so', 'INCR', 'UpdateTime', 'RecID', 'COALESCE(BillNo, CAST(RecID AS CHAR))', '合同评审主表，进入订单评审上下文'),
('S1_CONTRACT_REVIEW_FLOW', 'S1合同评审流程节点', 'ado_contract_review_flow', 'mdp_stg_so', 'FULL', NULL, 'RecID', 'CONCAT(COALESCE(ReviewBillNo, ''''), '':'', CAST(StageNo AS CHAR), '':'', CAST(RecID AS CHAR))', '合同评审节点，空表或全量同步均应成功'),
('S1_SHIPPING_PLAN', 'S1发货计划主表', 'ShippingPlan', 'mdp_stg_ship_trans', 'INCR', 'UpdateTime', 'RecID', 'COALESCE(LotSerial, CAST(RecID AS CHAR))', '发货计划主表，进入发货标准层'),
('S1_SHIPPING_PLAN_DETAIL', 'S1发货计划明细', 'ShippingPlanDetail', 'mdp_stg_ship_trans', 'INCR', 'UpdateTime', 'RecID', 'CONCAT(COALESCE(CAST(plan_id AS CHAR), ''''), '':'', COALESCE(OrdNbr, ''''), '':'', CAST(RecID AS CHAR))', '发货计划明细，进入发货标准层'),
('S1_ASN_SHIPPER_MASTER', 'S1 ASN发货主表', 'ASNBOLShipperMaster', 'mdp_stg_ship_trans', 'INCR', 'UpdateTime', 'RecID', 'COALESCE(Id, CONCAT(COALESCE(OrdNbr, ''''), '':'', CAST(RecID AS CHAR)))', 'ASN发货主表，进入发货标准层'),
('S1_ASN_SHIPPER_DETAIL', 'S1 ASN发货明细', 'ASNBOLShipperDetail', 'mdp_stg_ship_trans', 'INCR', 'UpdateTime', 'RecID', 'CONCAT(COALESCE(Id, ''''), '':'', COALESCE(CAST(Line AS CHAR), CAST(RecID AS CHAR)))', 'ASN发货明细，进入发货标准层'),
('S1_LINKAGE_PLAN', 'S1计划联动看板', 'LinkagePlan', 'mdp_stg_ship_trans', 'INCR', 'update_time', 'id', 'CONCAT(COALESCE(bill_no, ''''), '':'', COALESCE(item_number, ''''), '':'', CAST(id AS CHAR))', '计划联动事实，进入订单交付DWD上下文');

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, status, remark)
SELECT
    @tenant_id,
    @source_id,
    entity_code,
    entity_name,
    'TABLE',
    source_table_name,
    target_table_name,
    sync_mode,
    incr_column,
    5000,
    1,
    remark
FROM tmp_s1_mdp_entity
ON DUPLICATE KEY UPDATE
    source_id = VALUES(source_id),
    entity_name = VALUES(entity_name),
    entity_type = VALUES(entity_type),
    source_table_name = VALUES(source_table_name),
    target_table_name = VALUES(target_table_name),
    sync_mode = VALUES(sync_mode),
    incr_column = VALUES(incr_column),
    batch_size = VALUES(batch_size),
    status = VALUES(status),
    remark = VALUES(remark),
    update_time = CURRENT_TIMESTAMP;

INSERT INTO mdp_field_mapping
(entity_id, source_field, target_field, field_type, transform_script, const_value, is_required, sort_order)
SELECT e.id, 'tenant_id', 'tenant_id', 'DIRECT', NULL, NULL, 0, 10
FROM mdp_entity e
JOIN tmp_s1_mdp_entity t ON e.entity_code = t.entity_code
WHERE e.tenant_id = @tenant_id
ON DUPLICATE KEY UPDATE source_field=VALUES(source_field), field_type=VALUES(field_type), sort_order=VALUES(sort_order);

INSERT INTO mdp_field_mapping
(entity_id, source_field, target_field, field_type, transform_script, const_value, is_required, sort_order)
SELECT e.id, 'CONST:AIDOP', 'source_system', 'CONST', NULL, 'AIDOP', 1, 20
FROM mdp_entity e
JOIN tmp_s1_mdp_entity t ON e.entity_code = t.entity_code
WHERE e.tenant_id = @tenant_id
ON DUPLICATE KEY UPDATE source_field=VALUES(source_field), field_type=VALUES(field_type), const_value=VALUES(const_value), is_required=VALUES(is_required), sort_order=VALUES(sort_order);

INSERT INTO mdp_field_mapping
(entity_id, source_field, target_field, field_type, transform_script, const_value, is_required, sort_order)
SELECT e.id, CONCAT('CONST:', t.source_table_name), 'source_table', 'CONST', NULL, t.source_table_name, 1, 30
FROM mdp_entity e
JOIN tmp_s1_mdp_entity t ON e.entity_code = t.entity_code
WHERE e.tenant_id = @tenant_id
ON DUPLICATE KEY UPDATE source_field=VALUES(source_field), field_type=VALUES(field_type), const_value=VALUES(const_value), is_required=VALUES(is_required), sort_order=VALUES(sort_order);

INSERT INTO mdp_field_mapping
(entity_id, source_field, target_field, field_type, transform_script, const_value, is_required, sort_order)
SELECT e.id, IFNULL(t.source_row_id_expr, ''), 'source_row_id', 'SCRIPT', t.source_row_id_expr, NULL, 0, 40
FROM mdp_entity e
JOIN tmp_s1_mdp_entity t ON e.entity_code = t.entity_code
WHERE e.tenant_id = @tenant_id
ON DUPLICATE KEY UPDATE source_field=VALUES(source_field), field_type=VALUES(field_type), transform_script=VALUES(transform_script), sort_order=VALUES(sort_order);

INSERT INTO mdp_field_mapping
(entity_id, source_field, target_field, field_type, transform_script, const_value, is_required, sort_order)
SELECT e.id, t.source_biz_key_expr, 'source_biz_key', 'SCRIPT', t.source_biz_key_expr, NULL, 1, 50
FROM mdp_entity e
JOIN tmp_s1_mdp_entity t ON e.entity_code = t.entity_code
WHERE e.tenant_id = @tenant_id
ON DUPLICATE KEY UPDATE source_field=VALUES(source_field), field_type=VALUES(field_type), transform_script=VALUES(transform_script), is_required=VALUES(is_required), sort_order=VALUES(sort_order);

INSERT INTO mdp_field_mapping
(entity_id, source_field, target_field, field_type, transform_script, const_value, is_required, sort_order)
SELECT e.id, 'TO_JSON(row)', 'raw_data', 'SCRIPT', 'TO_JSON(row)', NULL, 0, 60
FROM mdp_entity e
JOIN tmp_s1_mdp_entity t ON e.entity_code = t.entity_code
WHERE e.tenant_id = @tenant_id
ON DUPLICATE KEY UPDATE source_field=VALUES(source_field), field_type=VALUES(field_type), transform_script=VALUES(transform_script), sort_order=VALUES(sort_order);

DROP TEMPORARY TABLE tmp_s1_mdp_entity;