-- S3 first batch MDP/DWD DDL for aidopdev.
-- Scope:
--   - mdp_stg_* source staging tables
--   - mdp_std_* S3 standard tables
--   - dwd_supplier_delivery as S3 extension of the global dwd_po_trans purchase fact
--   - dwd_material_readiness / dwd_material_shortage
--
-- Not included:
--   - dwd_process_outsource_delivery, postponed until the S3/S6 outsource boundary is frozen.
--   - metric_value_day and common MDP metadata tables, which are owned by the global design.
--
-- This script creates structures only. Data backfill is executed separately and can be rerun.

CREATE TABLE IF NOT EXISTS mdp_stg_supplier (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NULL,
    factory_id BIGINT NULL,
    company_id BIGINT NULL,
    source_system VARCHAR(50) NOT NULL,
    source_table VARCHAR(100) NOT NULL,
    source_row_id VARCHAR(200) NULL,
    source_biz_key VARCHAR(300) NOT NULL,
    sync_batch_id VARCHAR(64) NOT NULL,
    sync_time DATETIME NOT NULL,
    process_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    process_message VARCHAR(1000) NULL,
    raw_data JSON NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_batch_status (sync_batch_id, process_status),
    UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key),
    KEY idx_tenant_time (tenant_id, sync_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3供应商贴源表';

CREATE TABLE IF NOT EXISTS mdp_stg_item LIKE mdp_stg_supplier;
ALTER TABLE mdp_stg_item COMMENT='S3物料贴源表';

CREATE TABLE IF NOT EXISTS mdp_stg_source_list LIKE mdp_stg_supplier;
ALTER TABLE mdp_stg_source_list COMMENT='S3货源清单贴源表';

CREATE TABLE IF NOT EXISTS mdp_stg_supply_demand LIKE mdp_stg_supplier;
ALTER TABLE mdp_stg_supply_demand COMMENT='S3供应需求贴源表';

CREATE TABLE IF NOT EXISTS mdp_stg_purchase_order LIKE mdp_stg_supplier;
ALTER TABLE mdp_stg_purchase_order COMMENT='S3采购订单贴源表';

CREATE TABLE IF NOT EXISTS mdp_stg_delivery LIKE mdp_stg_supplier;
ALTER TABLE mdp_stg_delivery COMMENT='S3交货/送货贴源表';

CREATE TABLE IF NOT EXISTS mdp_stg_receipt LIKE mdp_stg_supplier;
ALTER TABLE mdp_stg_receipt COMMENT='S3收货/退货贴源表';

CREATE TABLE IF NOT EXISTS mdp_stg_work_order_material LIKE mdp_stg_supplier;
ALTER TABLE mdp_stg_work_order_material COMMENT='S3工单物料贴源表';

CREATE TABLE IF NOT EXISTS mdp_std_supplier_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,
    supplier_code VARCHAR(100) NOT NULL,
    supplier_name VARCHAR(200) NULL,
    supplier_type VARCHAR(50) NULL,
    quota_rate DECIMAL(18,6) NULL,
    lead_time DECIMAL(18,6) NULL,
    min_qty DECIMAL(18,6) NULL,
    packaging_qty DECIMAL(18,6) NULL,
    price DECIMAL(18,6) NULL,
    currency_type VARCHAR(50) NULL,
    effective_date DATETIME NULL,
    expire_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_supplier (tenant_id, item_code, supplier_code),
    KEY idx_supplier (tenant_id, supplier_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3标准货源清单';

CREATE TABLE IF NOT EXISTS mdp_std_supply_demand (
    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,
    demand_no VARCHAR(100) NOT NULL,
    demand_line VARCHAR(50) NULL,
    demand_type VARCHAR(50) NULL,
    item_code VARCHAR(100) NOT NULL,
    item_name VARCHAR(200) NULL,
    required_qty DECIMAL(18,6) NULL,
    required_date DATETIME NULL,
    supplier_code VARCHAR(100) 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, required_date),
    KEY idx_status (tenant_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3标准供应需求';

CREATE TABLE IF NOT EXISTS mdp_std_purchase_order (
    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,
    po_no VARCHAR(100) NOT NULL,
    po_line VARCHAR(50) NOT NULL,
    po_type VARCHAR(50) NULL,
    supplier_code VARCHAR(100) NULL,
    item_code VARCHAR(100) NOT NULL,
    item_name VARCHAR(200) NULL,
    order_qty DECIMAL(18,6) NULL,
    received_qty DECIMAL(18,6) NULL,
    returned_qty DECIMAL(18,6) NULL,
    due_date DATETIME NULL,
    need_date DATETIME NULL,
    order_date DATETIME NULL,
    status VARCHAR(50) NULL,
    buyer VARCHAR(100) NULL,
    work_order VARCHAR(100) 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_po_line (tenant_id, po_no, po_line),
    KEY idx_supplier_due (tenant_id, supplier_code, due_date),
    KEY idx_item_due (tenant_id, item_code, due_date),
    KEY idx_work_order (tenant_id, work_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3标准采购订单';

CREATE TABLE IF NOT EXISTS mdp_std_delivery_schedule (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    source_system VARCHAR(50) NOT NULL,
    delivery_plan_no VARCHAR(100) NOT NULL,
    po_no VARCHAR(100) NOT NULL,
    po_line VARCHAR(50) NOT NULL,
    item_code VARCHAR(100) NOT NULL,
    supplier_code VARCHAR(100) NULL,
    supplier_name VARCHAR(200) NULL,
    schedule_qty DECIMAL(18,6) NULL,
    sent_qty DECIMAL(18,6) NULL,
    rest_qty DECIMAL(18,6) NULL,
    return_qty DECIMAL(18,6) NULL,
    request_date DATETIME NULL,
    need_date DATETIME NULL,
    submit_date DATETIME NULL,
    last_sent_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),
    UNIQUE KEY uk_delivery_plan (tenant_id, delivery_plan_no),
    KEY idx_po_line (tenant_id, po_no, po_line),
    KEY idx_need_date (tenant_id, need_date),
    KEY idx_supplier_need (tenant_id, supplier_code, need_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3标准交货计划';

CREATE TABLE IF NOT EXISTS mdp_std_delivery_result (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    source_system VARCHAR(50) NOT NULL,
    delivery_no VARCHAR(100) NOT NULL,
    delivery_line VARCHAR(50) NULL,
    delivery_plan_no VARCHAR(100) NULL,
    po_no VARCHAR(100) NULL,
    po_line VARCHAR(50) NULL,
    item_code VARCHAR(100) NULL,
    delivery_qty DECIMAL(18,6) NULL,
    receipt_qty DECIMAL(18,6) NULL,
    return_qty DECIMAL(18,6) NULL,
    receipt_date DATETIME NULL,
    qc_status VARCHAR(50) NULL,
    event_time DATETIME 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_delivery (tenant_id, delivery_no),
    KEY idx_po_line (tenant_id, po_no, po_line),
    KEY idx_event_time (tenant_id, event_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3标准交货结果';

CREATE TABLE IF NOT EXISTS mdp_std_material_readiness (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    source_system VARCHAR(50) NOT NULL,
    work_order VARCHAR(100) NOT NULL,
    op_code VARCHAR(50) NULL,
    item_code VARCHAR(100) NULL,
    component_item_code VARCHAR(100) NOT NULL,
    required_qty DECIMAL(18,6) NULL,
    issued_qty DECIMAL(18,6) NULL,
    received_qty DECIMAL(18,6) NULL,
    available_qty DECIMAL(18,6) NULL,
    in_transit_qty DECIMAL(18,6) NULL,
    incoming_qty DECIMAL(18,6) NULL,
    shortage_qty DECIMAL(18,6) NULL,
    ready_status VARCHAR(50) NULL,
    need_date DATETIME NULL,
    supplier_code VARCHAR(100) 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_work_order (tenant_id, work_order),
    KEY idx_component_date (tenant_id, component_item_code, need_date),
    KEY idx_ready_status (tenant_id, ready_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3标准齐套结果';

CREATE TABLE IF NOT EXISTS mdp_std_process_outsource_order (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    source_system VARCHAR(50) NOT NULL,
    work_order VARCHAR(100) NOT NULL,
    op_code VARCHAR(50) NOT NULL,
    routing_code VARCHAR(100) NULL,
    supplier_code VARCHAR(100) NULL,
    po_no VARCHAR(100) NULL,
    po_line VARCHAR(50) NULL,
    order_qty DECIMAL(18,6) NULL,
    completed_qty DECIMAL(18,6) NULL,
    due_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_work_op (tenant_id, work_order, op_code),
    KEY idx_supplier_due (tenant_id, supplier_code, due_date),
    KEY idx_po_line (tenant_id, po_no, po_line)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3标准工序外协订单';

CREATE TABLE IF NOT EXISTS dwd_supplier_delivery (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    stat_date DATE NOT NULL,
    po_no VARCHAR(100) NOT NULL,
    po_line VARCHAR(50) NOT NULL,
    po_type VARCHAR(50) NULL,
    supplier_code VARCHAR(100) NULL,
    supplier_name VARCHAR(200) NULL,
    item_code VARCHAR(100) NOT NULL,
    item_name VARCHAR(200) NULL,
    order_qty DECIMAL(18,6) NULL,
    schedule_qty DECIMAL(18,6) NULL,
    delivery_qty DECIMAL(18,6) NULL,
    receipt_qty DECIMAL(18,6) NULL,
    return_qty DECIMAL(18,6) NULL,
    remaining_qty DECIMAL(18,6) NULL,
    due_date DATETIME NULL,
    need_date DATETIME NULL,
    last_delivery_date DATETIME NULL,
    last_receipt_date DATETIME NULL,
    delivery_status VARCHAR(50) NULL,
    risk_level VARCHAR(50) NULL,
    source_system VARCHAR(50) NULL,
    sync_batch_id VARCHAR(64) 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_po_line_stat (tenant_id, po_no, po_line, stat_date),
    KEY idx_stat_date (tenant_id, stat_date),
    KEY idx_supplier_date (tenant_id, supplier_code, stat_date),
    KEY idx_item_date (tenant_id, item_code, stat_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3供应商采购交付宽表';

CREATE TABLE IF NOT EXISTS dwd_material_readiness (
    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) NULL,
    parent_item_code VARCHAR(100) NULL,
    component_item_code VARCHAR(100) NOT NULL,
    component_item_name VARCHAR(200) NULL,
    required_qty DECIMAL(18,6) NULL,
    cumulative_required_qty DECIMAL(18,6) NULL,
    stock_available_qty DECIMAL(18,6) NULL,
    qc_pending_qty DECIMAL(18,6) NULL,
    in_transit_qty DECIMAL(18,6) NULL,
    delivery_reply_qty DECIMAL(18,6) NULL,
    available_qty DECIMAL(18,6) NULL,
    shortage_qty DECIMAL(18,6) NULL,
    ready_status VARCHAR(50) NULL,
    supplier_code VARCHAR(100) NULL,
    supplier_name VARCHAR(200) NULL,
    need_date DATETIME 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_component_stat (tenant_id, stat_date, work_order, op_code, component_item_code),
    KEY idx_work_order (tenant_id, work_order),
    KEY idx_item_date (tenant_id, component_item_code, stat_date),
    KEY idx_status_date (tenant_id, ready_status, stat_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3工单物料齐套宽表';

CREATE TABLE IF NOT EXISTS dwd_material_shortage (
    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) NULL,
    component_item_code VARCHAR(100) NOT NULL,
    shortage_qty DECIMAL(18,6) NOT NULL DEFAULT 0,
    shortage_reason VARCHAR(200) NULL,
    expected_supply_date DATETIME NULL,
    supplier_code VARCHAR(100) NULL,
    related_po_no VARCHAR(100) NULL,
    related_delivery_plan_no VARCHAR(100) NULL,
    risk_level VARCHAR(50) 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,
    KEY idx_work_order (tenant_id, work_order),
    KEY idx_item_date (tenant_id, component_item_code, stat_date),
    KEY idx_risk_date (tenant_id, risk_level, stat_date),
    KEY idx_supplier_date (tenant_id, supplier_code, stat_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S3物料缺料事实表';