-- 批次追溯数据模型补强
-- 说明：字段先允许为空，历史数据回填完成后再评估是否收紧约束。
-- 注意：项目不使用 tenant_id；执行前请确认字段和索引不存在。

ALTER TABLE mes_wm_batch
    ADD COLUMN pro_batch_id BIGINT NULL COMMENT '对应生产批次ID' AFTER id;
ALTER TABLE mes_wm_batch
    ADD INDEX idx_mes_wm_batch_pro_batch_id (pro_batch_id);

ALTER TABLE mes_wm_sn
    ADD COLUMN pro_batch_id BIGINT NULL COMMENT '生产批次ID' AFTER id;
ALTER TABLE mes_wm_sn
    ADD COLUMN bag_code_id BIGINT NULL COMMENT '袋码ID' AFTER pro_batch_id;
ALTER TABLE mes_wm_sn
    ADD COLUMN pallet_id BIGINT NULL COMMENT '托盘ID' AFTER bag_code_id;
ALTER TABLE mes_wm_sn
    ADD COLUMN sales_id BIGINT NULL COMMENT '销售出库单ID' AFTER pallet_id;
ALTER TABLE mes_wm_sn
    ADD INDEX idx_mes_wm_sn_pro_batch_id (pro_batch_id);
ALTER TABLE mes_wm_sn
    ADD INDEX idx_mes_wm_sn_bag_code_id (bag_code_id);

ALTER TABLE mes_qc_seed_quality
    ADD COLUMN batch_id BIGINT NULL COMMENT '生产批次ID' AFTER item_id;
ALTER TABLE mes_qc_seed_quality
    ADD INDEX idx_mes_qc_seed_quality_batch_id (batch_id);

CREATE TABLE mes_trace_event (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
    event_no VARCHAR(64) NOT NULL COMMENT '事件编号',
    event_type VARCHAR(32) NOT NULL COMMENT '事件类型',
    event_category VARCHAR(32) NULL COMMENT '事件分类',
    event_time DATETIME NOT NULL COMMENT '事件时间',
    pro_batch_id BIGINT NULL COMMENT '生产批次ID',
    wm_batch_id BIGINT NULL COMMENT '仓储批次ID',
    bag_code_id BIGINT NULL COMMENT '袋码ID',
    pallet_id BIGINT NULL COMMENT '托盘ID',
    sn_id BIGINT NULL COMMENT 'SN ID',
    item_id BIGINT NULL COMMENT '物料ID',
    biz_type VARCHAR(32) NULL COMMENT '业务类型',
    biz_id BIGINT NULL COMMENT '业务ID',
    biz_code VARCHAR(100) NULL COMMENT '业务编码',
    biz_line_id BIGINT NULL COMMENT '业务明细ID',
    from_node_type VARCHAR(32) NULL COMMENT '来源节点类型',
    from_node_id BIGINT NULL COMMENT '来源节点ID',
    from_node_code VARCHAR(100) NULL COMMENT '来源节点编码',
    to_node_type VARCHAR(32) NULL COMMENT '目标节点类型',
    to_node_id BIGINT NULL COMMENT '目标节点ID',
    to_node_code VARCHAR(100) NULL COMMENT '目标节点编码',
    quantity DECIMAL(18, 6) NULL COMMENT '数量',
    warehouse_id BIGINT NULL COMMENT '仓库ID',
    location_id BIGINT NULL COMMENT '库区ID',
    area_id BIGINT NULL COMMENT '库位ID',
    line_id BIGINT NULL COMMENT '产线ID',
    work_order_id BIGINT NULL COMMENT '工单ID',
    process_id BIGINT NULL COMMENT '工序ID',
    sales_id BIGINT NULL COMMENT '销售出库单ID',
    quality_id BIGINT NULL COMMENT '质检记录ID',
    operator_id BIGINT NULL COMMENT '操作人ID',
    payload_json JSON NULL COMMENT '事件扩展数据',
    content_hash VARCHAR(64) NULL COMMENT '事件内容SHA-256摘要',
    prev_hash VARCHAR(64) NULL COMMENT '前一事件摘要',
    creator VARCHAR(64) NULL DEFAULT '' COMMENT '创建者',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updater VARCHAR(64) NULL DEFAULT '' COMMENT '更新者',
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    deleted BIT(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
    PRIMARY KEY (id),
    UNIQUE KEY uk_mes_trace_event_no (event_no),
    KEY idx_mes_trace_event_batch_time (pro_batch_id, event_time),
    KEY idx_mes_trace_event_biz (biz_type, biz_id, biz_line_id),
    KEY idx_mes_trace_event_nodes (from_node_id, to_node_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='MES统一追溯事件';

-- 历史数据回填示例，确认数据质量后执行：
-- UPDATE mes_wm_batch w JOIN mes_pro_batch p ON p.code = w.code AND p.deleted = b'0'
-- SET w.pro_batch_id = p.id WHERE w.pro_batch_id IS NULL AND w.deleted = b'0';
-- UPDATE mes_qc_seed_quality q JOIN mes_pro_batch p ON p.code = q.batch_code AND p.deleted = b'0'
-- SET q.batch_id = p.id WHERE q.batch_id IS NULL AND q.deleted = b'0';