-- ============================================================
-- 1.0.207.sql
-- S5-OUTSOURCE-ISSUE-MDP-PIPELINE-1 (版本卫生顺延自 1.0.206：S5-OUTSOURCE-ISSUE-MDP-VERSION-HYGIENE-1)
--
-- 委外发料单 DOP 内部数据中台只读链路建表。
-- 源：aidopdev.NbrMaster / NbrDetail，业务类型 Type='CA'（头明细关联 NbrDetail.NbrRecID -> NbrMaster.RecID）。
--   贴源层：mdp_stg_outsource_issue / mdp_stg_outsource_issue_detail（raw_data JSON 信封）
--   标准层：mdp_std_outsource_issue（头·已确认字段） / mdp_std_outsource_issue_detail（明细·已确认 9 列）
--
-- 边界：仅 CREATE TABLE IF NOT EXISTS；不 INSERT/UPDATE/DELETE；不动 NbrMaster/NbrDetail；
--       明细「发料数量/已发数/批次号」3 候选列本批后置，不落 typed 字段。
-- ============================================================

CREATE TABLE IF NOT EXISTS mdp_stg_outsource_issue (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    factory_id BIGINT NULL DEFAULT 1,
    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_outsrc_issue (tenant_id, source_table, source_row_id),
    KEY idx_mdp_stg_outsrc_issue_batch (sync_batch_id),
    KEY idx_mdp_stg_outsrc_issue_biz (source_biz_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S5委外发料单头贴源层';

CREATE TABLE IF NOT EXISTS mdp_stg_outsource_issue_detail (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    factory_id BIGINT NULL DEFAULT 1,
    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_outsrc_issue_dtl (tenant_id, source_table, source_row_id),
    KEY idx_mdp_stg_outsrc_issue_dtl_batch (sync_batch_id),
    KEY idx_mdp_stg_outsrc_issue_dtl_biz (source_biz_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S5委外发料单明细贴源层';

CREATE TABLE IF NOT EXISTS mdp_std_outsource_issue (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    factory_id BIGINT NULL DEFAULT 1,
    source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP',
    bill_no VARCHAR(24) NOT NULL,
    issue_date DATETIME NULL,
    outsource_no VARCHAR(60) NULL,
    work_order VARCHAR(64) NULL,
    department_code VARCHAR(20) NULL,
    department_name VARCHAR(255) NULL,
    issuer VARCHAR(255) NULL,
    status VARCHAR(8) NULL,
    status_desc VARCHAR(20) NULL,
    remark VARCHAR(200) NULL,
    create_user VARCHAR(24) NULL,
    source_create_time DATETIME NULL,
    eff_date DATETIME 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_mdp_std_outsrc_issue (tenant_id, bill_no),
    KEY idx_mdp_std_outsrc_issue_date (tenant_id, issue_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S5委外发料单头标准层';

CREATE TABLE IF NOT EXISTS mdp_std_outsource_issue_detail (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    std_head_id BIGINT NULL,
    bill_no VARCHAR(24) NOT NULL,
    line SMALLINT NOT NULL DEFAULT 0,
    item_num VARCHAR(24) NULL,
    item_name TEXT NULL,
    um VARCHAR(8) NULL,
    qty_ord DECIMAL(18,6) NULL DEFAULT 0,
    location_from VARCHAR(8) NULL,
    location_to VARCHAR(8) NULL,
    status VARCHAR(8) NULL,
    remark TEXT 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_mdp_std_outsrc_issue_dtl (tenant_id, source_biz_key),
    KEY idx_mdp_std_outsrc_issue_dtl_head (std_head_id),
    KEY idx_mdp_std_outsrc_issue_dtl_bill (tenant_id, bill_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S5委外发料单明细标准层（发料数量/已发数/批次号 3 候选列后置）';