AiDOPWarehouse/doc/db/mdp/01_mdp_config_and_registry.sql

-- 数据中台：同步配置与实体注册（方案 §4.2 / §4.5.2）
-- 说明：使用 IF NOT EXISTS，可与现有库并存；若列不一致请人工合并，勿直接 DROP 生产表。
-- 字符集建议与业务库一致（utf8mb4）。

SET NAMES utf8mb4;

-- ===== 同步源级配置（与 mdp_source 互补，方案草案）=====
CREATE TABLE IF NOT EXISTS mdp_sync_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    source_code VARCHAR(50) NOT NULL COMMENT 'SAP / MES_OWN / AIDOPDEV_MYSQL',
    source_name VARCHAR(100),
    source_type ENUM('DB','API') NOT NULL,
    db_conn_string_enc VARCHAR(500) COMMENT '加密连接串',
    api_config_id BIGINT,
    sync_frequency VARCHAR(50) COMMENT 'CRON 表达式',
    status TINYINT DEFAULT 1,
    UNIQUE KEY uk_source (source_code)
) COMMENT='同步源配置（条目级）';

-- ===== 数据源 =====
CREATE TABLE IF NOT EXISTS mdp_source (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    source_code VARCHAR(50) NOT NULL COMMENT 'SAP / MES_OWN / WMS_OWN',
    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)
) COMMENT='数据源管理';

-- ===== 同步实体 =====
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),
    stage_code VARCHAR(50) COMMENT '所属管道阶段编码（编排预留）',
    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),
    INDEX idx_source (source_id),
    INDEX idx_api_config (api_config_id)
) COMMENT='同步实体定义';

-- ===== 字段映射 =====
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)
) COMMENT='字段映射';

-- ===== 同步执行日志（单实体粒度）=====
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,
    INDEX idx_tenant_entity_time (tenant_id, entity_id, sync_start),
    INDEX idx_batch (sync_batch_id),
    INDEX idx_status (status)
) COMMENT='同步执行日志';

-- ===== 整链转换运行日志（与 S3 MDP 监控一致，可供 S4/S8 作业复用形态）=====
CREATE TABLE IF NOT EXISTS mdp_transform_run_log (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    job_code VARCHAR(64) NOT NULL DEFAULT '',
    job_name VARCHAR(200),
    trigger_type VARCHAR(32) NOT NULL DEFAULT 'AUTO',
    batch_id VARCHAR(64) NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'RUNNING',
    start_time DATETIME,
    end_time DATETIME,
    duration_ms INT,
    stage_rows INT,
    standard_rows INT,
    dwd_rows INT,
    error_message VARCHAR(2000),
    summary_json TEXT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_job_time (job_code, start_time),
    INDEX idx_batch (batch_id),
    INDEX idx_status (status)
) COMMENT='管道/整链转换运行日志';