-- =============================================
-- MDM 数据迁移 SQL 脚本
-- 创建日期: 2026-06-29
-- 说明: 将 MES/ERP/WMS 的物料数据迁移到统一主数据表 md_item
-- 注意: 执行前请备份数据库！
-- =============================================

-- ---------------------------------------------
-- 第一阶段：迁移基础数据（分类、单位、品牌）
-- ---------------------------------------------

-- 1. 迁移 MES 计量单位到 md_unit_measure
INSERT INTO `md_unit_measure` (`id`, `code`, `name`, `primary_flag`, `primary_id`, `change_rate`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT
    id,
    LOWER(REPLACE(name, ' ', '_')) AS code,  -- 使用名称生成编码
    name,
    COALESCE(primary_flag, 0) AS primary_flag,
    primary_id,
    change_rate,
    COALESCE(status, 0) AS status,
    remark,
    creator, create_time, updater, update_time, deleted
FROM `mes_md_unit_measure`
WHERE deleted = 0
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- 2. 迁移 ERP 计量单位到 md_unit_measure（使用新ID范围 10000-19999）
INSERT INTO `md_unit_measure` (`id`, `code`, `name`, `primary_flag`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT
    id + 10000,  -- ERP 单位 ID 偏移 10000
    LOWER(REPLACE(name, ' ', '_')) AS code,
    name,
    0 AS primary_flag,
    COALESCE(status, 0) AS status,
    remark,
    creator, create_time, updater, update_time, deleted
FROM `erp_product_unit`
WHERE deleted = 0
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- 3. 迁移 MES 物料分类到 md_item_category
INSERT INTO `md_item_category` (`id`, `parent_id`, `code`, `name`, `item_type`, `sort`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT
    id,
    parent_id,
    code,
    name,
    CASE item_or_product
        WHEN 'ITEM' THEN 1      -- 原料
        WHEN 'PRODUCT' THEN 3   -- 成品
        ELSE NULL
    END AS item_type,
    sort,
    COALESCE(status, 0) AS status,
    remark,
    creator, create_time, updater, update_time, deleted
FROM `mes_md_item_type`
WHERE deleted = 0
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- 4. 迁移 ERP 产品分类到 md_item_category（使用新ID范围 10000-19999）
INSERT INTO `md_item_category` (`id`, `parent_id`, `code`, `name`, `item_type`, `sort`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT
    id + 10000,
    CASE WHEN parent_id = 0 THEN 0 ELSE parent_id + 10000 END,
    CONCAT('ERP_', code) AS code,  -- 添加前缀避免冲突
    name,
    3 AS item_type,  -- ERP 产品默认为成品
    COALESCE(sort, 0) AS sort,
    COALESCE(status, 0) AS status,
    remark,
    creator, create_time, updater, update_time, deleted
FROM `erp_product_category`
WHERE deleted = 0
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- 5. 迁移 WMS 商品分类到 md_item_category（使用新ID范围 20000-29999）
INSERT INTO `md_item_category` (`id`, `parent_id`, `code`, `name`, `item_type`, `sort`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT
    id + 20000,
    CASE WHEN parent_id = 0 THEN 0 ELSE parent_id + 20000 END,
    CONCAT('WMS_', code) AS code,
    name,
    NULL AS item_type,  -- WMS 分类不区分类型
    COALESCE(sort, 0) AS sort,
    COALESCE(status, 0) AS status,
    remark,
    creator, create_time, updater, update_time, deleted
FROM `wms_item_category`
WHERE deleted = 0
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- 6. 迁移 WMS 品牌到 md_brand
INSERT INTO `md_brand` (`id`, `code`, `name`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT
    id,
    code,
    name,
    0 AS status,
    remark,
    creator, create_time, updater, update_time, deleted
FROM `wms_item_brand`
WHERE deleted = 0
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- ---------------------------------------------
-- 第二阶段：迁移物料主数据
-- ---------------------------------------------

-- 7. 迁移 MES 物料到 md_item
INSERT INTO `md_item` (
    `id`, `code`, `name`, `bar_code`, `specification`,
    `category_id`, `unit_measure_id`, `brand_id`, `item_type`, `status`,
    `purchase_price`, `sales_price`, `cost_price`, `min_price`,
    `safety_stock`, `min_stock`, `max_stock`, `min_order_qty`, `lead_time`,
    `is_batch_managed`, `is_serial_managed`, `barcode_rule_id`,
    `expiry_day`, `high_value`, `weight`, `remark`,
    `creator`, `create_time`, `updater`, `update_time`, `deleted`
)
SELECT
    id,
    code,
    name,
    NULL AS bar_code,  -- MES 没有条码
    specification,
    itemTypeId,
    unitMeasureId,
    NULL AS brand_id,
    1 AS item_type,  -- MES 物料默认为原料
    COALESCE(status, 0),
    NULL, NULL, NULL, NULL,  -- 价格信息 MES 没有
    NULL AS safety_stock,
    min_stock,
    max_stock,
    NULL, NULL,  -- 最小订货量、提前期 MES 没有
    COALESCE(batchFlag, 0),
    0 AS is_serial_managed,
    NULL AS barcode_rule_id,
    NULL AS expiry_day,
    COALESCE(highValue, 0),
    NULL AS weight,
    remark,
    creator, create_time, updater, update_time, deleted
FROM `mes_md_item`
WHERE deleted = 0;

-- 8. 记录 MES 物料迁移映射
INSERT INTO `md_item_migration_map` (`source_module`, `source_table`, `source_id`, `target_item_id`, `create_time`)
SELECT 'MES', 'mes_md_item', id, id, NOW()
FROM `mes_md_item`
WHERE deleted = 0;

-- 9. 迁移 ERP 产品到 md_item（使用新ID范围 100000-199999）
INSERT INTO `md_item` (
    `id`, `code`, `name`, `bar_code`, `specification`,
    `category_id`, `unit_measure_id`, `brand_id`, `item_type`, `status`,
    `purchase_price`, `sales_price`, `cost_price`, `min_price`,
    `safety_stock`, `min_stock`, `max_stock`, `min_order_qty`, `lead_time`,
    `is_batch_managed`, `is_serial_managed`, `barcode_rule_id`,
    `expiry_day`, `high_value`, `weight`, `remark`,
    `creator`, `create_time`, `updater`, `update_time`, `deleted`
)
SELECT
    id + 100000,  -- ERP 产品 ID 偏移 100000
    COALESCE(bar_code, CONCAT('ERP_', id)) AS code,  -- 使用条码作为编码，没有则生成
    name,
    bar_code,
    standard AS specification,
    categoryId + 10000,  -- 使用 ERP 分类映射后的ID
    unitId + 10000,  -- 使用 ERP 单位映射后的ID
    NULL AS brand_id,
    3 AS item_type,  -- ERP 产品默认为成品
    COALESCE(status, 0),
    purchase_price,
    sale_price,
    NULL AS cost_price,
    min_price,
    NULL, NULL, NULL, NULL, NULL,  -- 库存管理 ERP 没有
    0 AS is_batch_managed,
    0 AS is_serial_managed,
    NULL AS barcode_rule_id,
    expiry_day,
    0 AS high_value,
    weight,
    remark,
    creator, create_time, updater, update_time, deleted
FROM `erp_product`
WHERE deleted = 0;

-- 10. 记录 ERP 产品迁移映射
INSERT INTO `md_item_migration_map` (`source_module`, `source_table`, `source_id`, `target_item_id`, `create_time`)
SELECT 'ERP', 'erp_product', id, id + 100000, NOW()
FROM `erp_product`
WHERE deleted = 0;

-- 11. 迁移 WMS 商品到 md_item（使用新ID范围 200000-299999）
INSERT INTO `md_item` (
    `id`, `code`, `name`, `bar_code`, `specification`,
    `category_id`, `unit_measure_id`, `brand_id`, `item_type`, `status`,
    `purchase_price`, `sales_price`, `cost_price`, `min_price`,
    `safety_stock`, `min_stock`, `max_stock`, `min_order_qty`, `lead_time`,
    `is_batch_managed`, `is_serial_managed`, `barcode_rule_id`,
    `expiry_day`, `high_value`, `weight`, `remark`,
    `creator`, `create_time`, `updater`, `update_time`, `deleted`
)
SELECT
    id + 200000,  -- WMS 商品 ID 偏移 200000
    code,
    name,
    NULL AS bar_code,  -- WMS 条码在 SKU 层
    NULL AS specification,
    categoryId + 20000,  -- 使用 WMS 分类映射后的ID
    NULL AS unit_measure_id,  -- WMS 单位是字符串，需要后续处理
    brandId,
    1 AS item_type,  -- WMS 商品默认为原料
    0 AS status,
    NULL, NULL, NULL, NULL,  -- 价格在 SKU 层
    NULL, NULL, NULL, NULL, NULL,
    0, 0, NULL,
    NULL, 0, NULL,
    remark,
    creator, create_time, updater, update_time, deleted
FROM `wms_item`
WHERE deleted = 0;

-- 12. 记录 WMS 商品迁移映射
INSERT INTO `md_item_migration_map` (`source_module`, `source_table`, `source_id`, `target_item_id`, `create_time`)
SELECT 'WMS', 'wms_item', id, id + 200000, NOW()
FROM `wms_item`
WHERE deleted = 0;

-- ---------------------------------------------
-- 第三阶段：迁移批次属性配置
-- ---------------------------------------------

-- 13. 迁移 MES 批次属性配置到 md_item_batch_config
INSERT INTO `md_item_batch_config` (
    `id`, `item_id`,
    `produce_date_flag`, `expire_date_flag`, `receipt_date_flag`,
    `vendor_flag`, `client_flag`, `sales_order_code_flag`, `purchase_order_code_flag`,
    `work_order_flag`, `task_flag`, `workstation_flag`, `tool_flag`, `mold_flag`,
    `lot_number_flag`, `quality_status_flag`,
    `creator`, `create_time`, `updater`, `update_time`, `deleted`
)
SELECT
    id,
    itemId,
    COALESCE(produce_date_flag, 0),
    COALESCE(expire_date_flag, 0),
    COALESCE(receipt_date_flag, 0),
    COALESCE(vendor_flag, 0),
    COALESCE(client_flag, 0),
    COALESCE(sales_order_code_flag, 0),
    COALESCE(purchase_order_code_flag, 0),
    COALESCE(work_order_flag, 0),
    COALESCE(task_flag, 0),
    COALESCE(workstation_flag, 0),
    COALESCE(tool_flag, 0),
    COALESCE(mold_flag, 0),
    COALESCE(lot_number_flag, 0),
    COALESCE(quality_status_flag, 0),
    creator, create_time, updater, update_time, deleted
FROM `mes_md_item_batch_config`
WHERE deleted = 0;

-- ---------------------------------------------
-- 第四阶段：迁移仓库数据
-- ---------------------------------------------

-- 14. 迁移 MES 仓库到 md_warehouse
INSERT INTO `md_warehouse` (`id`, `code`, `name`, `address`, `area`, `type`, `charge_user_id`, `status`, `frozen`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT
    id,
    code,
    name,
    address,
    area,
    NULL AS type,  -- MES 仓库没有类型
    chargeUserId,
    0 AS status,
    COALESCE(frozen, 0),
    remark,
    creator, create_time, updater, update_time, deleted
FROM `mes_wm_warehouse`
WHERE deleted = 0;

-- 15. 迁移 ERP 仓库到 md_warehouse（使用新ID范围 10000-19999）
INSERT INTO `md_warehouse` (`id`, `code`, `name`, `address`, `area`, `type`, `charge_user_id`, `status`, `frozen`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT
    id + 10000,
    CONCAT('ERP_', id) AS code,  -- ERP 仓库没有编码，生成一个
    name,
    address,
    NULL AS area,
    NULL AS type,
    NULL AS charge_user_id,  -- ERP 使用 principal 字符串，无法映射
    COALESCE(status, 0),
    0 AS frozen,
    remark,
    creator, create_time, updater, update_time, deleted
FROM `erp_warehouse`
WHERE deleted = 0;

-- 16. 迁移 WMS 仓库到 md_warehouse（使用新ID范围 20000-29999）
INSERT INTO `md_warehouse` (`id`, `code`, `name`, `address`, `area`, `type`, `charge_user_id`, `status`, `frozen`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT
    id + 20000,
    code,
    name,
    address,
    area,
    NULL AS type,
    chargeUserId,
    COALESCE(status, 0),
    0 AS frozen,
    remark,
    creator, create_time, updater, update_time, deleted
FROM `wms_warehouse`
WHERE deleted = 0;

-- ---------------------------------------------
-- 第五阶段：迁移库存数据
-- ---------------------------------------------

-- 17. 迁移 MES 库存到 wms_stock
INSERT INTO `wms_stock` (`item_id`, `warehouse_id`, `location_id`, `batch_code`, `quantity`, `quantity_locked`, `quantity_available`, `unit_measure_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT
    m.target_item_id AS item_id,  -- 使用映射后的物料ID
    s.warehouseId AS warehouse_id,
    s.locationId AS location_id,
    s.batchCode AS batch_code,
    s.quantity,
    0 AS quantity_locked,
    s.quantity AS quantity_available,
    i.unitMeasureId AS unit_measure_id,
    s.creator, s.create_time, s.updater, s.update_time, s.deleted
FROM `mes_wm_material_stock` s
LEFT JOIN `mes_md_item` i ON i.id = s.itemId
LEFT JOIN `md_item_migration_map` m ON m.source_module = 'MES' AND m.source_table = 'mes_md_item' AND m.source_id = s.itemId
WHERE s.deleted = 0 AND m.target_item_id IS NOT NULL;

-- 18. 迁移 ERP 库存到 wms_stock
INSERT INTO `wms_stock` (`item_id`, `warehouse_id`, `batch_code`, `quantity`, `quantity_locked`, `quantity_available`, `unit_measure_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT
    m.target_item_id AS item_id,
    s.warehouseId + 10000 AS warehouse_id,  -- 使用 ERP 仓库映射后的ID
    NULL AS batch_code,
    s.count AS quantity,
    0 AS quantity_locked,
    s.count AS quantity_available,
    p.unitId + 10000 AS unit_measure_id,  -- 使用 ERP 单位映射后的ID
    s.creator, s.create_time, s.updater, s.update_time, s.deleted
FROM `erp_stock` s
LEFT JOIN `erp_product` p ON p.id = s.productId
LEFT JOIN `md_item_migration_map` m ON m.source_module = 'ERP' AND m.source_table = 'erp_product' AND m.source_id = s.productId
WHERE s.deleted = 0 AND m.target_item_id IS NOT NULL;

-- 19. 迁移 WMS 库存到 wms_stock
INSERT INTO `wms_stock` (`item_id`, `warehouse_id`, `batch_code`, `quantity`, `quantity_locked`, `quantity_available`, `unit_measure_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT
    m.target_item_id AS item_id,
    s.warehouseId + 20000 AS warehouse_id,  -- 使用 WMS 仓库映射后的ID
    NULL AS batch_code,
    s.quantity,
    0 AS quantity_locked,
    s.quantity AS quantity_available,
    NULL AS unit_measure_id,  -- WMS 库存没有单位
    s.creator, s.create_time, s.updater, s.update_time, s.deleted
FROM `wms_inventory` s
LEFT JOIN `md_item_migration_map` m ON m.source_module = 'WMS' AND m.source_table = 'wms_item' AND m.source_id = s.skuId  -- 注意：WMS 库存关联的是 SKU
WHERE s.deleted = 0 AND m.target_item_id IS NOT NULL;

-- =============================================
-- 迁移完成
-- =============================================

-- 查询迁移结果统计
SELECT
    'md_item' AS table_name,
    (SELECT COUNT(*) FROM md_item WHERE deleted = 0) AS count
UNION ALL
SELECT
    'md_item_category',
    (SELECT COUNT(*) FROM md_item_category WHERE deleted = 0)
UNION ALL
SELECT
    'md_unit_measure',
    (SELECT COUNT(*) FROM md_unit_measure WHERE deleted = 0)
UNION ALL
SELECT
    'md_brand',
    (SELECT COUNT(*) FROM md_brand WHERE deleted = 0)
UNION ALL
SELECT
    'md_warehouse',
    (SELECT COUNT(*) FROM md_warehouse WHERE deleted = 0)
UNION ALL
SELECT
    'wms_stock',
    (SELECT COUNT(*) FROM wms_stock WHERE deleted = 0)
UNION ALL
SELECT
    'md_item_migration_map',
    (SELECT COUNT(*) FROM md_item_migration_map);