AiDOPWarehouse/doc/migrations/2026-05-10_s3_mdp_foundation_config.sql

-- S3 first batch MDP foundation and entity configuration for aidopdev.
-- This script creates common MDP configuration/log tables and registers S3 first batch entities.
-- It intentionally does not store database passwords.

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

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同步执行日志';

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, 'S3供应协同首批迁移试点数据源，不在配置表保存明文密码')
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_s3_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_s3_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
('S3_SUPPLIER', 'S3供应商', 'SuppMaster', 'mdp_stg_supplier', 'INCR', 'UpdateTime', 'RecID', 'CONCAT(Domain, '':'', Supp)', '供应商主数据，有样本'),
('S3_CONSIGNEE_SUPPLIER', 'S3供应商地址/补充', 'ConsigneeAddressMaster', 'mdp_stg_supplier', 'INCR', 'UpdateTime', 'RecID', 'CONCAT(Domain, '':'', Address)', '供应商地址补充，业务键待后续实数校验'),
('S3_ITEM_ERP', 'S3旧式物料', 'ItemMaster', 'mdp_stg_item', 'INCR', 'UpdateTime', 'RecID', 'CONCAT(Domain, '':'', ItemNum)', '旧式物料主数据，有样本'),
('S3_ITEM_NEW', 'S3新物料', 'ic_item', 'mdp_stg_item', 'INCR', 'update_time', 'Id', 'CONCAT(tenant_id, '':'', number)', '新物料主数据，有样本'),
('S3_SOURCE_LIST', 'S3货源清单', 'srm_purchase', 'mdp_stg_source_list', 'INCR', 'update_time', 'Id', 'CONCAT(number, '':'', supplier_number)', '货源清单，有样本'),
('S3_SUPPLY_DEMAND', 'S3物料需求计划', 'ic_demandschedule', 'mdp_stg_supply_demand', 'INCR', 'update_time', 'Id', 'CAST(Id AS CHAR)', '当前可为空，空表同步应视为成功'),
('S3_PURCHASE_REQUEST', 'S3采购申请', 'srm_pr_main', 'mdp_stg_supply_demand', 'INCR', 'update_time', 'Id', 'COALESCE(pr_billno, CAST(Id AS CHAR))', '当前可为空，空表同步应视为成功'),
('S3_PURCHASE_ORDER_MASTER', 'S3采购订单主表', 'PurOrdMaster', 'mdp_stg_purchase_order', 'INCR', 'UpdateTime', 'RecID', 'CONCAT(Domain, '':'', PurOrd)', '已有生成验证样本'),
('S3_PURCHASE_ORDER_DETAIL', 'S3采购订单明细', 'PurOrdDetail', 'mdp_stg_purchase_order', 'INCR', 'UpdateTime', 'RecID', 'CONCAT(Domain, '':'', PurOrd, '':'', Line)', '已有生成验证样本'),
('S3_DELIVERY_PLAN', 'S3交货计划', 'srm_polist_ds', 'mdp_stg_delivery', 'INCR', 'updatetime', 'Id', 'dsnum', '已有生成验证样本'),
('S3_SHIPPER_MASTER', 'S3送货单主表', 'scm_shd', 'mdp_stg_delivery', 'FULL', NULL, 'id', 'COALESCE(shddh, CAST(id AS CHAR))', '当前可为空，空表同步应视为成功'),
('S3_SHIPPER_DETAIL', 'S3送货单明细', 'scm_shdzb', 'mdp_stg_delivery', 'FULL', NULL, 'id', 'CONCAT(glid, '':'', id)', '当前可为空，空表同步应视为成功'),
('S3_RECEIPT_MASTER', 'S3收货主表', 'PurOrdRctMaster', 'mdp_stg_receipt', 'INCR', 'UpdateTime', 'RecID', 'CONCAT(Domain, '':'', Receiver)', '当前可为空，业务键待后续实数校验'),
('S3_RECEIPT_DETAIL', 'S3收货明细', 'PurOrdRctDetail', 'mdp_stg_receipt', 'INCR', 'UpdateTime', 'RecID', 'CONCAT(Domain, '':'', Receiver, '':'', Line)', '当前可为空，业务键待后续实数校验'),
('S3_WORK_ORDER_MASTER', 'S3工单主表', 'WorkOrdMaster', 'mdp_stg_work_order_material', 'INCR', 'UpdateTime', 'RecID', 'CONCAT(Domain, '':'', WorkOrd)', '有少量样本'),
('S3_WORK_ORDER_DETAIL', 'S3工单组件', 'WorkOrdDetail', 'mdp_stg_work_order_material', 'INCR', 'UpdateTime', 'RecID', 'CONCAT(Domain, '':'', WorkOrd, '':'', Op, '':'', ItemNum)', '有少量样本'),
('S3_WORK_ORDER_ROUTING', 'S3工单工序', 'WorkOrdRouting', 'mdp_stg_work_order_material', 'INCR', 'UpdateTime', 'RecID', 'CONCAT(Domain, '':'', WorkOrd, '':'', OP)', '有少量样本'),
('S3_ROUTING_OUTSOURCE', 'S3工序外协配置', 'RoutingOpDetail', 'mdp_stg_work_order_material', 'INCR', 'UpdatedAt', 'RecID', 'CONCAT(Domain, '':'', RoutingCode, '':'', Op, '':'', SupplierCode)', '工序外协供应商口径使用 SupplierCode'),
('S3_INVENTORY', 'S3库存', 'InvMaster', 'mdp_stg_work_order_material', 'INCR', 'UpdateTime', 'RecID', 'CONCAT(Domain, '':'', ItemNum, '':'', Location)', '有少量样本');

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_s3_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_s3_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_s3_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_s3_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_s3_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_s3_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_s3_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_s3_mdp_entity;