-- 仓储物流模块采购入库流程优化 - 数据库变更脚本
-- 执行日期: 2026-07-10

-- =====================
-- 1. 到货通知主表变更
-- =====================

-- 添加采购订单编码（冗余字段，用于显示）
ALTER TABLE mes_wm_arrival_notice
ADD COLUMN purchase_order_code VARCHAR(64) COMMENT '采购订单编码（冗余字段）' AFTER purchase_order_id;

-- =====================
-- 2. 到货通知行表变更
-- =====================

-- 添加采购订单行关联
ALTER TABLE mes_wm_arrival_notice_line
ADD COLUMN purchase_order_item_id BIGINT COMMENT '采购订单行编号（关联 ERP 采购订单明细）' AFTER notice_id;

-- 添加索引
ALTER TABLE mes_wm_arrival_notice_line
ADD INDEX idx_purchase_order_item_id (purchase_order_item_id);

-- =====================
-- 3. 采购入库主表变更
-- =====================

-- 添加 BPM 流程实例编号
ALTER TABLE mes_wm_item_receipt
ADD COLUMN process_instance_id VARCHAR(64) COMMENT 'BPM 流程实例编号' AFTER status;

-- =====================
-- 4. 采购入库行表变更
-- =====================

-- 添加采购订单行关联（完整追溯链）
ALTER TABLE mes_wm_item_receipt_line
ADD COLUMN purchase_order_item_id BIGINT COMMENT '采购订单行编号（完整追溯链）' AFTER arrival_notice_line_id;

-- 添加索引
ALTER TABLE mes_wm_item_receipt_line
ADD INDEX idx_purchase_order_item_id (purchase_order_item_id);

-- =====================
-- 5. BPM 分类配置
-- =====================

-- 添加采购入库审批分类（如果不存在）
-- 注意：id 需要根据实际数据库序列生成
INSERT INTO bpm_category (name, code, status, remark, creator, create_time, updater, update_time, deleted)
SELECT '采购入库审批', 'item_receipt_approve', 0, 'MES采购入库单审批分类', '1', NOW(), '1', NOW(), b'0'
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM bpm_category WHERE code = 'item_receipt_approve' AND deleted = b'0');