-- S3 MDP full transform runtime rollout for aidopdev.
-- Purpose:
--   - Complete missing S3 first-batch standard/DWD tables.
--   - Record the runtime transform scope now implemented by S3MdpSyncTransformService.
--   - Keep all writes inside mdp_* / dwd_* tables; source business tables are not changed.

CREATE TABLE IF NOT EXISTS mdp_std_supplier (
    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,
    supplier_code VARCHAR(100) NOT NULL,
    supplier_name VARCHAR(200) NULL,
    supplier_type VARCHAR(50) NULL,
    status VARCHAR(50) NULL,
    contact VARCHAR(200) NULL,
    address VARCHAR(500) NULL,
    currency_type VARCHAR(50) 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_biz_key),
    UNIQUE KEY uk_supplier (tenant_id, supplier_code),
    KEY idx_supplier_name (tenant_id, supplier_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3标准供应商';

CREATE TABLE IF NOT EXISTS mdp_std_item (
    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,
    item_code VARCHAR(100) NOT NULL,
    item_name VARCHAR(200) NULL,
    model VARCHAR(200) NULL,
    unit VARCHAR(50) NULL,
    item_type VARCHAR(50) NULL,
    status VARCHAR(50) 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_biz_key),
    UNIQUE KEY uk_item (tenant_id, item_code),
    KEY idx_item_name (tenant_id, item_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3标准物料';

CREATE TABLE IF NOT EXISTS mdp_std_purchase_request (
    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,
    pr_no VARCHAR(100) NOT NULL,
    pr_line VARCHAR(50) NULL,
    item_code VARCHAR(100) NULL,
    item_name VARCHAR(200) NULL,
    supplier_code VARCHAR(100) NULL,
    request_qty DECIMAL(18,6) NULL,
    request_date DATETIME NULL,
    send_date DATETIME NULL,
    arrive_date DATETIME NULL,
    status VARCHAR(50) 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_biz_key),
    KEY idx_item_date (tenant_id, item_code, request_date),
    KEY idx_supplier_date (tenant_id, supplier_code, request_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3标准采购申请';

CREATE TABLE IF NOT EXISTS mdp_std_delivery_exception (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    source_system VARCHAR(50) NOT NULL,
    exception_no VARCHAR(100) NOT NULL,
    exception_type VARCHAR(100) NULL,
    item_code VARCHAR(100) NULL,
    supplier_code VARCHAR(100) NULL,
    po_no VARCHAR(100) NULL,
    po_line VARCHAR(50) NULL,
    need_qty DECIMAL(18,6) NULL,
    exception_time DATETIME NULL,
    remark VARCHAR(1000) NULL,
    status VARCHAR(50) 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_biz_key),
    KEY idx_supplier_time (tenant_id, supplier_code, exception_time),
    KEY idx_item_time (tenant_id, item_code, exception_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3标准交货异常';

CREATE TABLE IF NOT EXISTS dwd_supply_demand (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    stat_date DATE NOT NULL,
    demand_no VARCHAR(100) NOT NULL,
    demand_line VARCHAR(50) NULL,
    demand_type VARCHAR(50) NULL,
    item_code VARCHAR(100) NULL,
    item_name VARCHAR(200) NULL,
    supplier_code VARCHAR(100) NULL,
    required_qty DECIMAL(18,6) NULL,
    fulfilled_qty DECIMAL(18,6) NULL,
    shortage_qty DECIMAL(18,6) NULL,
    required_date DATETIME NULL,
    demand_status VARCHAR(50) NULL,
    source_system VARCHAR(50) NOT NULL,
    sync_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_demand_stat (tenant_id, stat_date, demand_no, demand_line),
    KEY idx_item_date (tenant_id, item_code, stat_date),
    KEY idx_supplier_date (tenant_id, supplier_code, stat_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3供应需求DWD';

CREATE TABLE IF NOT EXISTS dwd_supplier_risk (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    stat_date DATE NOT NULL,
    supplier_code VARCHAR(100) NOT NULL,
    supplier_name VARCHAR(200) NULL,
    item_code VARCHAR(100) NULL,
    risk_type VARCHAR(100) NOT NULL,
    risk_level VARCHAR(50) NOT NULL,
    risk_count INT NOT NULL DEFAULT 0,
    risk_qty DECIMAL(18,6) NULL,
    risk_reason VARCHAR(500) NULL,
    source_system VARCHAR(50) NOT 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_supplier_risk (tenant_id, stat_date, supplier_code, item_code, risk_type),
    KEY idx_level (tenant_id, stat_date, risk_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3供应风险DWD';

CREATE TABLE IF NOT EXISTS dwd_process_outsource_delivery (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    stat_date DATE NOT NULL,
    work_order VARCHAR(100) NOT NULL,
    op_code VARCHAR(50) NOT NULL,
    routing_code VARCHAR(100) NULL,
    supplier_code VARCHAR(100) NULL,
    supplier_name VARCHAR(200) NULL,
    po_no VARCHAR(100) NULL,
    po_line VARCHAR(50) NULL,
    order_qty DECIMAL(18,6) NULL,
    completed_qty DECIMAL(18,6) NULL,
    remaining_qty DECIMAL(18,6) NULL,
    due_date DATETIME NULL,
    delivery_status VARCHAR(50) NULL,
    risk_level VARCHAR(50) NULL,
    source_system VARCHAR(50) NOT 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_work_op_stat (tenant_id, stat_date, work_order, op_code, po_no, po_line),
    KEY idx_supplier_due (tenant_id, supplier_code, due_date),
    KEY idx_work_order (tenant_id, work_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3/S6工序外协交付DWD';

-- Runtime transform entry point:
--   Admin.NET backend service: S3MdpSyncTransformService.RunFullAsync()
--   Furion Schedule job:       S3MdpSyncTransformJob
--
-- Verification examples:
-- SELECT COUNT(*) FROM mdp_std_supplier;
-- SELECT COUNT(*) FROM mdp_std_item;
-- SELECT COUNT(*) FROM mdp_std_supplier_item;
-- SELECT COUNT(*) FROM mdp_std_purchase_order;
-- SELECT COUNT(*) FROM mdp_std_delivery_schedule;
-- SELECT COUNT(*) FROM dwd_supplier_delivery;
-- SELECT COUNT(*) FROM dwd_supplier_risk;
-- SELECT COUNT(*) FROM dwd_process_outsource_delivery;
-- SELECT status, COUNT(*) FROM mdp_sync_log WHERE sync_batch_id LIKE 'S3_MDP_FULL_%' GROUP BY status;