-- ============================================================
-- MES 物料 ID 迁移脚本
-- 目的：将 mes_md_item.id 更新为与 mdm_item.id 一致
-- 执行前请备份数据库！
-- ============================================================

-- 1. 创建临时映射表
DROP TEMPORARY TABLE IF EXISTS tmp_item_id_mapping;
CREATE TEMPORARY TABLE tmp_item_id_mapping (
    old_id BIGINT NOT NULL,
    new_id BIGINT NOT NULL,
    code VARCHAR(64),
    PRIMARY KEY (old_id)
);

-- 2. 插入需要迁移的物料映射数据
INSERT INTO tmp_item_id_mapping (old_id, new_id, code)
SELECT id, mdm_item_id, code
FROM mes_md_item
WHERE mdm_item_id IS NOT NULL AND id != mdm_item_id;

-- 查看需要迁移的数据
SELECT * FROM tmp_item_id_mapping;

-- 3. 禁用外键检查（避免外键约束阻止更新）
SET FOREIGN_KEY_CHECKS = 0;

-- 4. 更新所有引用 mes_md_item.id 的表
-- 注意：以下语句会更新所有 item_id 字段

-- 4.1 基础数据表
UPDATE mes_md_item_batch_config t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_md_product_bom t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_md_product_sip t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_md_product_sop t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

-- 4.2 生产相关表
UPDATE mes_pro_card t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_pro_feedback t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_pro_route_product t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_pro_route_product_bom t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_pro_task t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_pro_task_issue t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_pro_work_order_bom t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

-- 4.3 质量相关表
UPDATE mes_qc_indicator_result t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_qc_ipqc t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_qc_iqc t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_qc_oqc t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_qc_rqc t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_qc_template_item t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

-- 4.4 仓库管理表 - 入库
UPDATE mes_wm_arrival_notice_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_item_receipt_detail t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_item_receipt_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_misc_receipt_detail t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_misc_receipt_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_outsource_receipt_detail t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_outsource_receipt_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_product_receipt_detail t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_product_receipt_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

-- 4.5 仓库管理表 - 出库
UPDATE mes_wm_item_consume_detail t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_item_consume_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_misc_issue_detail t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_misc_issue_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_outsource_issue_detail t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_outsource_issue_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_product_issue_detail t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_product_issue_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_return_issue_detail t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_return_issue_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

-- 4.6 仓库管理表 - 销售/退货
UPDATE mes_wm_product_sales_detail t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_product_sales_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_return_sales_detail t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_return_sales_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_return_vendor_detail t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_return_vendor_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_sales_notice_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

-- 4.7 仓库管理表 - 其他
UPDATE mes_wm_batch t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_material_stock t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_package_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_sn t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_stock_reserve t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_stock_taking_plan_param t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_stock_taking_task_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_stock_taking_task_result t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_transaction t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_transfer_detail t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_transfer_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

-- 4.8 生产产出表
UPDATE mes_wm_product_produce_detail t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

UPDATE mes_wm_product_produce_line t
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
SET t.item_id = m.new_id;

-- 5. 更新主表 mes_md_item 的 id
-- 由于 id 是主键，需要先删除旧记录再插入新记录
-- 创建临时表保存完整数据
DROP TEMPORARY TABLE IF EXISTS tmp_mes_md_item;
CREATE TEMPORARY TABLE tmp_mes_md_item AS
SELECT * FROM mes_md_item;

-- 删除需要迁移的记录
DELETE FROM mes_md_item WHERE id IN (SELECT old_id FROM tmp_item_id_mapping);

-- 使用新 ID 重新插入
INSERT INTO mes_md_item (
    id, mdm_item_id, code, name, specification, unit_measure_id, item_type_id,
    status, safe_stock_flag, min_stock, max_stock, high_value, batch_flag, remark,
    creator, create_time, updater, update_time, deleted
)
SELECT
    m.new_id AS id,  -- 使用 MDM 物料 ID
    t.mdm_item_id,
    t.code, t.name, t.specification, t.unit_measure_id, t.item_type_id,
    t.status, t.safe_stock_flag, t.min_stock, t.max_stock, t.high_value, t.batch_flag, t.remark,
    t.creator, t.create_time, t.updater, t.update_time, t.deleted
FROM tmp_mes_md_item t
JOIN tmp_item_id_mapping m ON t.id = m.old_id;

-- 6. 重新启用外键检查
SET FOREIGN_KEY_CHECKS = 1;

-- 7. 清理临时表
DROP TEMPORARY TABLE IF EXISTS tmp_item_id_mapping;
DROP TEMPORARY TABLE IF EXISTS tmp_mes_md_item;

-- 8. 验证迁移结果
SELECT id, mdm_item_id, code, name
FROM mes_md_item
WHERE mdm_item_id IS NOT NULL;

-- 检查是否还有 id != mdm_item_id 的记录
SELECT COUNT(*) AS need_migration_count
FROM mes_md_item
WHERE mdm_item_id IS NOT NULL AND id != mdm_item_id;