-- ============================================
-- 供应商统一迁移至 SRM
-- 将 ERP/MES 表的 supplier_id/vendor_id 从 erp_supplier.id 迁移为 srm_supplier.id
-- 删除 srm_supplier.erp_supplier_id 桥接字段
-- 执行前请备份数据库！
-- ============================================

-- 1. 迁移 ERP 表 supplier_id (erp_supplier.id → srm_supplier.id)
UPDATE erp_purchase_request r
INNER JOIN srm_supplier s ON s.erp_supplier_id = r.supplier_id
SET r.supplier_id = s.id;

UPDATE erp_purchase_order o
INNER JOIN srm_supplier s ON s.erp_supplier_id = o.supplier_id
SET o.supplier_id = s.id;

UPDATE erp_purchase_in i
INNER JOIN srm_supplier s ON s.erp_supplier_id = i.supplier_id
SET i.supplier_id = s.id;

UPDATE erp_purchase_return r
INNER JOIN srm_supplier s ON s.erp_supplier_id = r.supplier_id
SET r.supplier_id = s.id;

UPDATE erp_finance_payment p
INNER JOIN srm_supplier s ON s.erp_supplier_id = p.supplier_id
SET p.supplier_id = s.id;

UPDATE erp_stock_in si
INNER JOIN srm_supplier s ON s.erp_supplier_id = si.supplier_id
SET si.supplier_id = s.id;

-- 2. 迁移 MES 表 vendor_id (erp_supplier.id → srm_supplier.id)
UPDATE mes_wm_arrival_notice a
INNER JOIN srm_supplier s ON s.erp_supplier_id = a.vendor_id
SET a.vendor_id = s.id;

UPDATE mes_wm_item_receipt r
INNER JOIN srm_supplier s ON s.erp_supplier_id = r.vendor_id
SET r.vendor_id = s.id;

UPDATE mes_wm_outsource_receipt r
INNER JOIN srm_supplier s ON s.erp_supplier_id = r.vendor_id
SET r.vendor_id = s.id;

UPDATE mes_wm_outsource_issue i
INNER JOIN srm_supplier s ON s.erp_supplier_id = i.vendor_id
SET i.vendor_id = s.id;

UPDATE mes_wm_return_vendor r
INNER JOIN srm_supplier s ON s.erp_supplier_id = r.vendor_id
SET r.vendor_id = s.id;

UPDATE mes_qc_iqc q
INNER JOIN srm_supplier s ON s.erp_supplier_id = q.vendor_id
SET q.vendor_id = s.id;

UPDATE mes_pro_work_order w
INNER JOIN srm_supplier s ON s.erp_supplier_id = w.vendor_id
SET w.vendor_id = s.id;

UPDATE mes_wm_material_stock ms
INNER JOIN srm_supplier s ON s.erp_supplier_id = ms.vendor_id
SET ms.vendor_id = s.id;

UPDATE mes_wm_batch b
INNER JOIN srm_supplier s ON s.erp_supplier_id = b.vendor_id
SET b.vendor_id = s.id;

-- 3. 删除 srm_supplier 的 erp_supplier_id 桥接字段
ALTER TABLE srm_supplier DROP INDEX idx_erp_supplier_id;
ALTER TABLE srm_supplier DROP COLUMN erp_supplier_id;

-- 4. srm_supplier_apply 如果存在 erp_supplier_id 则删除
ALTER TABLE srm_supplier_apply DROP COLUMN IF EXISTS erp_supplier_id;