-- ============================================================
-- 物料分类统一迁移：mes_md_item_type -> mdm_item_category
--
-- 执行时机：上线前执行一次（MES 分类接口切换底层存储之前）
-- 特性：
--   1. 按原 ID 迁移，保证 mes_md_item.item_type_id、mdm_item.category_id 等历史引用继续有效
--   2. 包含已逻辑删除记录，避免引用断链
--   3. 可重复执行（主键冲突时更新业务字段，不会产生重复数据）
-- ============================================================

INSERT INTO mdm_item_category
    (id, parent_id, code, name, item_type, item_or_product, sort, status, remark,
     creator, create_time, updater, update_time, deleted)
SELECT new.id,
       COALESCE(new.parent_id, 0),
       new.code,
       new.name,
       CASE WHEN UPPER(new.item_or_product) = 'PRODUCT' THEN 2 ELSE 1 END,
       new.item_or_product,
       COALESCE(new.sort, 0),
       COALESCE(new.status, 0),
       new.remark,
       COALESCE(new.creator, ''),
       COALESCE(new.create_time, CURRENT_TIMESTAMP),
       COALESCE(new.updater, ''),
       COALESCE(new.update_time, CURRENT_TIMESTAMP),
       COALESCE(new.deleted, b'0')
FROM mes_md_item_type AS new
WHERE new.name IS NOT NULL
ON DUPLICATE KEY UPDATE
    parent_id       = COALESCE(new.parent_id, 0),
    code            = new.code,
    name            = new.name,
    item_type       = CASE WHEN UPPER(new.item_or_product) = 'PRODUCT' THEN 2 ELSE 1 END,
    item_or_product = new.item_or_product,
    sort            = COALESCE(new.sort, 0),
    status          = COALESCE(new.status, 0),
    remark          = new.remark,
    deleted         = COALESCE(new.deleted, b'0');

-- 迁移结果核对：两侧 ID 应完全一致
-- SELECT (SELECT COUNT(*) FROM mes_md_item_type) AS mes_cnt,
--        (SELECT COUNT(*) FROM mdm_item_category) AS mdm_cnt,
--        (SELECT COUNT(*) FROM mes_md_item_type t
--         WHERE NOT EXISTS (SELECT 1 FROM mdm_item_category c WHERE c.id = t.id)) AS missing_cnt;