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

-- MDP-SYNC-CONFIG-CENTER-1
-- 数据中台同步配置中心：任务/步骤/参数/公式/调度业务配置表 + S1-S4 种子数据 + 菜单更名

SET NAMES utf8mb4;

-- CodeFirst 可能已创建不完整/错误结构的配置表，重建前先清理（本阶段无业务数据）
DROP TABLE IF EXISTS mdp_sync_task_step;
DROP TABLE IF EXISTS mdp_sync_task_param;
DROP TABLE IF EXISTS mdp_sync_task_formula;
DROP TABLE IF EXISTS mdp_sync_task_schedule;
DROP TABLE IF EXISTS mdp_sync_task;

CREATE TABLE IF NOT EXISTS mdp_sync_task (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    task_code VARCHAR(100) NOT NULL,
    task_name VARCHAR(200) NOT NULL,
    task_type VARCHAR(40) NOT NULL DEFAULT 'SERVICE_SYNC',
    business_domain_code VARCHAR(100) NULL,
    business_domain_name VARCHAR(200) NULL,
    consumer_modules VARCHAR(500) NULL,
    source_system_code VARCHAR(100) NULL,
    service_key VARCHAR(100) NULL,
    job_code VARCHAR(100) NULL,
    schedule_job_id VARCHAR(100) NULL,
    status TINYINT NOT NULL DEFAULT 1,
    owner_role VARCHAR(100) NULL,
    config_version INT NOT NULL DEFAULT 1,
    description VARCHAR(1000) NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_mdp_sync_task_tenant_code (tenant_id, task_code),
    KEY idx_mdp_sync_task_job_code (job_code),
    KEY idx_mdp_sync_task_schedule_job (schedule_job_id),
    KEY idx_mdp_sync_task_domain (business_domain_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='MDP同步任务业务配置';

CREATE TABLE IF NOT EXISTS mdp_sync_task_step (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    task_code VARCHAR(100) NOT NULL,
    step_code VARCHAR(100) NOT NULL,
    step_name VARCHAR(200) NOT NULL,
    stage_type VARCHAR(40) NOT NULL,
    service_method_key VARCHAR(100) NULL,
    enabled TINYINT NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    description VARCHAR(1000) NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_mdp_sync_task_step (tenant_id, task_code, step_code),
    KEY idx_mdp_sync_task_step_task (tenant_id, task_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='MDP同步任务步骤配置';

CREATE TABLE IF NOT EXISTS mdp_sync_task_param (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    task_code VARCHAR(100) NOT NULL,
    scope_type VARCHAR(40) NOT NULL DEFAULT 'TASK',
    scope_code VARCHAR(100) NOT NULL DEFAULT '',
    param_key VARCHAR(100) NOT NULL,
    param_name VARCHAR(200) NOT NULL,
    param_type VARCHAR(40) NOT NULL DEFAULT 'STRING',
    param_value TEXT NULL,
    default_value TEXT NULL,
    required TINYINT NOT NULL DEFAULT 0,
    editable TINYINT NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    description VARCHAR(1000) NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_mdp_sync_task_param (tenant_id, task_code, scope_type, scope_code, param_key),
    KEY idx_mdp_sync_task_param_task (tenant_id, task_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='MDP同步任务参数配置';

CREATE TABLE IF NOT EXISTS mdp_sync_task_formula (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    task_code VARCHAR(100) NOT NULL,
    step_code VARCHAR(100) NULL,
    formula_code VARCHAR(100) NOT NULL,
    formula_name VARCHAR(200) NOT NULL,
    metric_code VARCHAR(100) NULL,
    formula_expr VARCHAR(1000) NULL,
    formula_preview VARCHAR(1000) NULL,
    formula_refs TEXT NULL,
    calc_rule TEXT NULL,
    direction VARCHAR(40) NOT NULL DEFAULT 'higher_is_better',
    yellow_threshold DECIMAL(18,6) NULL,
    red_threshold DECIMAL(18,6) NULL,
    version_no INT NOT NULL DEFAULT 1,
    is_enabled TINYINT NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    description VARCHAR(1000) NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_mdp_sync_task_formula (tenant_id, task_code, formula_code, version_no),
    KEY idx_mdp_sync_task_formula_metric (tenant_id, metric_code),
    KEY idx_mdp_sync_task_formula_task (tenant_id, task_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='MDP同步任务公式配置';

CREATE TABLE IF NOT EXISTS mdp_sync_task_schedule (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    task_code VARCHAR(100) NOT NULL,
    schedule_job_id VARCHAR(100) NULL,
    schedule_mode VARCHAR(40) NOT NULL DEFAULT 'CRON',
    cron_expr VARCHAR(200) NULL,
    cron_desc VARCHAR(500) NULL,
    timezone VARCHAR(100) NOT NULL DEFAULT 'Asia/Shanghai',
    auto_enabled TINYINT NOT NULL DEFAULT 1,
    manual_enabled TINYINT NOT NULL DEFAULT 1,
    retry_enabled TINYINT NOT NULL DEFAULT 1,
    max_retry_count INT NOT NULL DEFAULT 3,
    retry_interval_seconds INT NOT NULL DEFAULT 300,
    timeout_seconds INT NOT NULL DEFAULT 3600,
    misfire_policy VARCHAR(40) NULL,
    sync_window_type VARCHAR(40) NOT NULL DEFAULT 'FULL',
    sync_window_value VARCHAR(200) NULL,
    last_schedule_time DATETIME NULL,
    next_schedule_time DATETIME NULL,
    admin_job_config_json TEXT NULL,
    description VARCHAR(1000) NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_mdp_sync_task_schedule (tenant_id, task_code),
    KEY idx_mdp_sync_task_schedule_job (schedule_job_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='MDP同步任务调度业务配置';

INSERT INTO mdp_sync_task
(tenant_id, task_code, task_name, task_type, business_domain_code, business_domain_name, consumer_modules, source_system_code, service_key, job_code, schedule_job_id, status, config_version, description)
VALUES
(0, 'S1_MDP_SYNC_TRANSFORM', '订单交付域 MDP 同步', 'SERVICE_SYNC', 'order_delivery', '订单交付域', 'S1,S2,S3,S4,S7,S9', 'AIDOPDEV_MYSQL', 'S1_MDP_SYNC_TRANSFORM', 'S1_MDP_SYNC_TRANSFORM', 'job_s1_mdp_sync_transform', 1, 1, '订单交付域贴源/标准/DWD/KPI 同步任务'),
(0, 'S2_MDP_SYNC_TRANSFORM', '工单排程域 MDP 同步', 'SERVICE_SYNC', 'work_schedule', '工单排程域', 'S2,S3,S5,S6,S8,S9', 'AIDOPDEV_MYSQL', 'S2_MDP_SYNC_TRANSFORM', 'S2_MDP_SYNC_TRANSFORM', 'job_s2_mdp_sync_transform', 1, 1, '工单排程域贴源/标准/DWD/KPI 同步任务'),
(0, 'S3_MDP_SYNC_TRANSFORM', '供应采购域 MDP 同步', 'SERVICE_SYNC', 'supply_purchase', '供应采购域', 'S3,S4,S5,S8,S9', 'AIDOPDEV_MYSQL', 'S3_MDP_SYNC_TRANSFORM', 'S3_MDP_SYNC_TRANSFORM', 'job_s3_mdp_sync_transform', 1, 1, '供应采购域贴源/标准/DWD/KPI 同步任务'),
(0, 'S4_MDP_SYNC_TRANSFORM', '采购执行域 MDP 同步', 'SERVICE_SYNC', 'purchase_execution', '采购执行域', 'S4,S5,S8,S9', 'AIDOPDEV_MYSQL', 'S4_MDP_SYNC_TRANSFORM', 'S4_MDP_SYNC_TRANSFORM', 'job_s4_mdp_sync_transform', 1, 1, '采购执行域贴源/标准/DWD/KPI 同步任务')
ON DUPLICATE KEY UPDATE
    task_name = VALUES(task_name),
    business_domain_code = VALUES(business_domain_code),
    business_domain_name = VALUES(business_domain_name),
    consumer_modules = VALUES(consumer_modules),
    source_system_code = VALUES(source_system_code),
    service_key = VALUES(service_key),
    job_code = VALUES(job_code),
    schedule_job_id = VALUES(schedule_job_id),
    status = VALUES(status),
    description = VALUES(description),
    update_time = CURRENT_TIMESTAMP;

INSERT INTO mdp_sync_task_schedule
(tenant_id, task_code, schedule_job_id, schedule_mode, cron_desc, auto_enabled, manual_enabled, retry_enabled, max_retry_count, retry_interval_seconds, timeout_seconds, sync_window_type, description)
VALUES
(0, 'S1_MDP_SYNC_TRANSFORM', 'job_s1_mdp_sync_transform', 'CRON', '按 Admin.NET 任务调度执行', 1, 1, 1, 3, 300, 3600, 'FULL', 'S1 同步调度业务配置'),
(0, 'S2_MDP_SYNC_TRANSFORM', 'job_s2_mdp_sync_transform', 'CRON', '按 Admin.NET 任务调度执行', 1, 1, 1, 3, 300, 3600, 'FULL', 'S2 同步调度业务配置'),
(0, 'S3_MDP_SYNC_TRANSFORM', 'job_s3_mdp_sync_transform', 'CRON', '按 Admin.NET 任务调度执行', 1, 1, 1, 3, 300, 3600, 'FULL', 'S3 同步调度业务配置'),
(0, 'S4_MDP_SYNC_TRANSFORM', 'job_s4_mdp_sync_transform', 'CRON', '按 Admin.NET 任务调度执行', 1, 1, 1, 3, 300, 3600, 'FULL', 'S4 同步调度业务配置')
ON DUPLICATE KEY UPDATE
    schedule_job_id = VALUES(schedule_job_id),
    schedule_mode = VALUES(schedule_mode),
    cron_desc = VALUES(cron_desc),
    auto_enabled = VALUES(auto_enabled),
    manual_enabled = VALUES(manual_enabled),
    retry_enabled = VALUES(retry_enabled),
    max_retry_count = VALUES(max_retry_count),
    retry_interval_seconds = VALUES(retry_interval_seconds),
    timeout_seconds = VALUES(timeout_seconds),
    sync_window_type = VALUES(sync_window_type),
    description = VALUES(description),
    update_time = CURRENT_TIMESTAMP;

INSERT INTO mdp_sync_task_step
(tenant_id, task_code, step_code, step_name, stage_type, enabled, sort_order, description)
SELECT 0, v.task_code, v.step_code, v.step_name, v.stage_type, v.enabled, v.sort_order, v.description
FROM (
    SELECT 'S1_MDP_SYNC_TRANSFORM' AS task_code, 'SOURCE_CONNECT' AS step_code, '来源连接' AS step_name, 'SOURCE_CONNECT' AS stage_type, 1 AS enabled, 10 AS sort_order, '登记数据源与实体连接' AS description
    UNION ALL SELECT 'S1_MDP_SYNC_TRANSFORM', 'STAGING', '贴源同步', 'STAGING', 1, 20, '源表抽取写入 mdp_stg_*'
    UNION ALL SELECT 'S1_MDP_SYNC_TRANSFORM', 'STANDARD', '标准层转换', 'STANDARD', 1, 30, 'mdp_stg_* -> mdp_std_*'
    UNION ALL SELECT 'S1_MDP_SYNC_TRANSFORM', 'DWD', 'DWD宽表', 'DWD', 1, 40, '标准层沉淀 DWD 宽表'
    UNION ALL SELECT 'S1_MDP_SYNC_TRANSFORM', 'KPI', '指标写入', 'KPI', 1, 50, '计算并写入 KPI 指标'
    UNION ALL SELECT 'S1_MDP_SYNC_TRANSFORM', 'QUALITY_CHECK', '质量校验', 'QUALITY_CHECK', 0, 60, '数据质量校验（预留）'
    UNION ALL SELECT 'S2_MDP_SYNC_TRANSFORM', 'SOURCE_CONNECT', '来源连接', 'SOURCE_CONNECT', 1, 10, '登记数据源与实体连接'
    UNION ALL SELECT 'S2_MDP_SYNC_TRANSFORM', 'STAGING', '贴源同步', 'STAGING', 1, 20, '源表抽取写入 mdp_stg_*'
    UNION ALL SELECT 'S2_MDP_SYNC_TRANSFORM', 'STANDARD', '标准层转换', 'STANDARD', 1, 30, 'mdp_stg_* -> mdp_std_*'
    UNION ALL SELECT 'S2_MDP_SYNC_TRANSFORM', 'DWD', 'DWD宽表', 'DWD', 1, 40, '标准层沉淀 DWD 宽表'
    UNION ALL SELECT 'S2_MDP_SYNC_TRANSFORM', 'KPI', '指标写入', 'KPI', 1, 50, '计算并写入 KPI 指标'
    UNION ALL SELECT 'S2_MDP_SYNC_TRANSFORM', 'QUALITY_CHECK', '质量校验', 'QUALITY_CHECK', 0, 60, '数据质量校验（预留）'
    UNION ALL SELECT 'S3_MDP_SYNC_TRANSFORM', 'SOURCE_CONNECT', '来源连接', 'SOURCE_CONNECT', 1, 10, '登记数据源与实体连接'
    UNION ALL SELECT 'S3_MDP_SYNC_TRANSFORM', 'STAGING', '贴源同步', 'STAGING', 1, 20, '源表抽取写入 mdp_stg_*'
    UNION ALL SELECT 'S3_MDP_SYNC_TRANSFORM', 'STANDARD', '标准层转换', 'STANDARD', 1, 30, 'mdp_stg_* -> mdp_std_*'
    UNION ALL SELECT 'S3_MDP_SYNC_TRANSFORM', 'DWD', 'DWD宽表', 'DWD', 1, 40, '标准层沉淀 DWD 宽表'
    UNION ALL SELECT 'S3_MDP_SYNC_TRANSFORM', 'KPI', '指标写入', 'KPI', 1, 50, '计算并写入 KPI 指标'
    UNION ALL SELECT 'S3_MDP_SYNC_TRANSFORM', 'QUALITY_CHECK', '质量校验', 'QUALITY_CHECK', 0, 60, '数据质量校验（预留）'
    UNION ALL SELECT 'S4_MDP_SYNC_TRANSFORM', 'SOURCE_CONNECT', '来源连接', 'SOURCE_CONNECT', 1, 10, '登记数据源与实体连接'
    UNION ALL SELECT 'S4_MDP_SYNC_TRANSFORM', 'STAGING', '贴源同步', 'STAGING', 1, 20, '源表抽取写入 mdp_stg_s4_*'
    UNION ALL SELECT 'S4_MDP_SYNC_TRANSFORM', 'STANDARD', '标准层转换', 'STANDARD', 1, 30, 'mdp_stg_s4_* -> mdp_std_s4_*'
    UNION ALL SELECT 'S4_MDP_SYNC_TRANSFORM', 'DWD', 'DWD宽表', 'DWD', 1, 40, '采购执行 DWD 宽表'
    UNION ALL SELECT 'S4_MDP_SYNC_TRANSFORM', 'KPI', '指标写入', 'KPI', 1, 50, '计算并写入 KPI 指标'
    UNION ALL SELECT 'S4_MDP_SYNC_TRANSFORM', 'QUALITY_CHECK', '质量校验', 'QUALITY_CHECK', 0, 60, '数据质量校验（预留）'
) v
ON DUPLICATE KEY UPDATE
    step_name = VALUES(step_name),
    stage_type = VALUES(stage_type),
    enabled = VALUES(enabled),
    sort_order = VALUES(sort_order),
    description = VALUES(description),
    update_time = CURRENT_TIMESTAMP;

UPDATE SysMenu
SET Title = '同步配置中心',
    Remark = '外部系统标准化接入、同步任务、字段映射、参数公式、调度策略与运行追踪'
WHERE Id = 1320990000404;