-- 1.0.146.sql
-- S2-MDP-SCHEDULE-KPI-1
-- 为 S2 生产排程建立 stg -> std -> dwd -> KPI 链路的基础表与实体配置。

CREATE TABLE IF NOT EXISTS mdp_stg_schedule (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    factory_id VARCHAR(64) 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) 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,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_mdp_stg_schedule (tenant_id, source_table, source_row_id),
    KEY idx_mdp_stg_schedule_batch (sync_batch_id),
    KEY idx_mdp_stg_schedule_biz (source_biz_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S2生产排程贴源层';

CREATE TABLE IF NOT EXISTS mdp_std_work_order_schedule (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    factory_id BIGINT NULL,
    source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP',
    work_order VARCHAR(100) NOT NULL,
    sales_order_no VARCHAR(100) NULL,
    item_code VARCHAR(100) NULL,
    item_name VARCHAR(200) NULL,
    site_code VARCHAR(50) NULL,
    status VARCHAR(50) NULL,
    priority DECIMAL(18,6) NULL,
    urgent_flag TINYINT NOT NULL DEFAULT 0,
    qty_ordered DECIMAL(18,6) NULL,
    qty_completed DECIMAL(18,6) NULL,
    order_date DATETIME NULL,
    due_date DATETIME NULL,
    release_date DATETIME NULL,
    prod_line VARCHAR(100) NULL,
    source_biz_key VARCHAR(200) NULL,
    sync_batch_id VARCHAR(100) NOT NULL,
    sync_time DATETIME NOT NULL,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_std_work_order_schedule (tenant_id, work_order),
    KEY idx_std_work_order_schedule_batch (sync_batch_id),
    KEY idx_std_work_order_schedule_due (tenant_id, due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S2标准工单排程';

CREATE TABLE IF NOT EXISTS mdp_std_operation_schedule (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    factory_id BIGINT NULL,
    source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP',
    work_order VARCHAR(100) NOT NULL,
    op_no VARCHAR(50) NULL,
    work_center VARCHAR(100) NULL,
    line_code VARCHAR(100) NULL,
    item_code VARCHAR(100) NULL,
    plan_date DATETIME NULL,
    prod_date DATETIME NULL,
    start_time DATETIME NULL,
    end_time DATETIME NULL,
    ord_qty DECIMAL(18,6) NULL,
    comp_qty DECIMAL(18,6) NULL,
    run_crew DECIMAL(18,6) NULL,
    employee VARCHAR(200) NULL,
    source_biz_key VARCHAR(200) NULL,
    sync_batch_id VARCHAR(100) NOT NULL,
    sync_time DATETIME NOT NULL,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_std_operation_schedule (tenant_id, source_biz_key),
    KEY idx_std_operation_schedule_work_order (tenant_id, work_order),
    KEY idx_std_operation_schedule_batch (sync_batch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S2标准工序排程';

CREATE TABLE IF NOT EXISTS dwd_order_schedule_trans (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    factory_id BIGINT NOT NULL DEFAULT 1,
    stat_date DATE NOT NULL,
    work_order VARCHAR(100) NOT NULL,
    sales_order_no VARCHAR(100) NULL,
    item_code VARCHAR(100) NULL,
    item_name VARCHAR(200) NULL,
    site_code VARCHAR(50) NULL,
    prod_line VARCHAR(100) NULL,
    status VARCHAR(50) NULL,
    urgent_flag TINYINT NOT NULL DEFAULT 0,
    qty_ordered DECIMAL(18,6) NULL,
    qty_completed DECIMAL(18,6) NULL,
    order_date DATETIME NULL,
    due_date DATETIME NULL,
    release_date DATETIME NULL,
    first_plan_date DATETIME NULL,
    last_plan_date DATETIME NULL,
    first_start_time DATETIME NULL,
    last_end_time DATETIME NULL,
    operation_count INT NOT NULL DEFAULT 0,
    scheduled_qty DECIMAL(18,6) NULL,
    completed_op_qty DECIMAL(18,6) NULL,
    schedule_cycle_days DECIMAL(18,6) NULL,
    schedule_satisfaction_flag TINYINT NOT NULL DEFAULT 0,
    wip_qty DECIMAL(18,6) NULL,
    resource_person_count DECIMAL(18,6) NULL,
    calc_batch_id VARCHAR(100) NOT NULL,
    calc_time DATETIME NOT NULL,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_dwd_order_schedule_trans (tenant_id, work_order, calc_batch_id),
    KEY idx_dwd_order_schedule_trans_batch (calc_batch_id),
    KEY idx_dwd_order_schedule_trans_stat (tenant_id, stat_date),
    KEY idx_dwd_order_schedule_trans_order (tenant_id, sales_order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S2订单工单排程DWD';

INSERT INTO mdp_entity
(tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name, sync_mode, batch_size, status, remark)
SELECT 0, s.id, v.entity_code, v.entity_name, 'TABLE', v.source_table_name, 'mdp_stg_schedule', 'FULL', 5000, 1, v.remark
FROM mdp_source s
JOIN (
    SELECT 'S2_WORK_ORDER_MASTER' AS entity_code, 'S2工单主数据' AS entity_name, 'WorkOrdMaster' AS source_table_name, '工单主数据进入 S2 贴源层' AS remark
    UNION ALL SELECT 'S2_WORK_ORDER_ROUTING', 'S2工单工艺路线', 'WorkOrdRouting', '工单工艺路线进入 S2 贴源层'
    UNION ALL SELECT 'S2_WORK_ORDER_DETAIL', 'S2工单物料明细', 'WorkOrdDetail', '工单物料需求进入 S2 贴源层'
    UNION ALL SELECT 'S2_PERIOD_SEQUENCE_DET', 'S2工序排程计划', 'PeriodSequenceDet', '工序间衔接与排程计划进入 S2 贴源层'
    UNION ALL SELECT 'S2_SCHEDULE_RESULT_OP', 'S2工序排产结果', 'ScheduleResultOpMaster', '工序排产结果进入 S2 贴源层'
) v
WHERE s.tenant_id=0 AND s.source_code='AIDOPDEV_MYSQL'
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),
    batch_size=VALUES(batch_size),
    status=VALUES(status),
    remark=VALUES(remark),
    update_time=CURRENT_TIMESTAMP;