-- 设备台账 -> 设备物料 -> 设备仓入库所需主数据修复
-- 可重复执行：有历史数据时恢复；整行缺失时自动创建。
-- 业务代码按 code 分别查询 MDM/MES 主键，不再要求两套表 ID 对齐。

INSERT INTO `mdm_item_category`
    (`parent_id`, `code`, `name`, `item_type`, `item_or_product`, `sort`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT 0, 'EQUIPMENT', '设备', 4, 'ITEM', 1, 0, '系统内置设备分类', 'system', NOW(), 'system', NOW(), b'0'
WHERE NOT EXISTS (SELECT 1 FROM `mdm_item_category` WHERE `code` = 'EQUIPMENT');

INSERT INTO `mes_md_item_type`
    (`parent_id`, `code`, `name`, `item_or_product`, `sort`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT 0, 'EQUIPMENT', '设备', 'ITEM', 1, 0, '系统内置设备分类', 'system', NOW(), 'system', NOW(), b'0'
WHERE NOT EXISTS (SELECT 1 FROM `mes_md_item_type` WHERE `code` = 'EQUIPMENT');

INSERT INTO `mdm_unit_measure`
    (`code`, `name`, `primary_flag`, `primary_id`, `change_rate`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT 'TAI', '台', b'1', NULL, 1, 0, '系统内置设备计量单位', 'system', NOW(), 'system', NOW(), b'0'
WHERE NOT EXISTS (SELECT 1 FROM `mdm_unit_measure` WHERE `code` = 'TAI');

INSERT INTO `mes_md_unit_measure`
    (`code`, `name`, `primary_flag`, `primary_id`, `change_rate`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT 'TAI', '台', b'1', NULL, 1, 0, '系统内置设备计量单位', 'system', NOW(), 'system', NOW(), b'0'
WHERE NOT EXISTS (SELECT 1 FROM `mes_md_unit_measure` WHERE `code` = 'TAI');

INSERT INTO `mes_wm_warehouse`
    (`code`, `name`, `type`, `frozen`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT 'SYS-EQUIPMENT', '设备仓', 2, b'0', '设备台账自动入库专用仓', 'system', NOW(), 'system', NOW(), b'0'
WHERE NOT EXISTS (SELECT 1 FROM `mes_wm_warehouse` WHERE `code` = 'SYS-EQUIPMENT');

INSERT INTO `mes_wm_warehouse_location`
    (`code`, `name`, `warehouse_id`, `frozen`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT 'SYS-EQUIP-LOC-01', '设备仓默认库区', w.`id`, b'0', '设备台账自动入库默认库区', 'system', NOW(), 'system', NOW(), b'0'
FROM `mes_wm_warehouse` w
WHERE w.`code` = 'SYS-EQUIPMENT'
  AND NOT EXISTS (SELECT 1 FROM `mes_wm_warehouse_location` WHERE `code` = 'SYS-EQUIP-LOC-01')
LIMIT 1;

INSERT INTO `mes_wm_warehouse_area`
    (`code`, `name`, `type`, `location_id`, `status`, `frozen`, `allow_item_mixing`, `allow_batch_mixing`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT 'SYS-EQUIP-BIN-01', '设备仓默认库位', 1, l.`id`, 0, b'0', b'1', b'1',
       '设备台账自动入库默认库位', 'system', NOW(), 'system', NOW(), b'0'
FROM `mes_wm_warehouse_location` l
WHERE l.`code` = 'SYS-EQUIP-LOC-01'
  AND NOT EXISTS (SELECT 1 FROM `mes_wm_warehouse_area` WHERE `code` = 'SYS-EQUIP-BIN-01')
LIMIT 1;

UPDATE `mdm_item_category`
SET `deleted` = b'0', `status` = 0, `updater` = 'system', `update_time` = NOW()
WHERE `code` = 'EQUIPMENT';

UPDATE `mes_md_item_type`
SET `deleted` = b'0', `status` = 0, `updater` = 'system', `update_time` = NOW()
WHERE `code` = 'EQUIPMENT';

UPDATE `mdm_unit_measure`
SET `deleted` = b'0', `status` = 0, `updater` = 'system', `update_time` = NOW()
WHERE `code` = 'TAI';

UPDATE `mes_md_unit_measure`
SET `deleted` = b'0', `status` = 0, `updater` = 'system', `update_time` = NOW()
WHERE `code` = 'TAI';

UPDATE `mes_wm_warehouse`
SET `deleted` = b'0', `type` = 2, `frozen` = b'0',
    `updater` = 'system', `update_time` = NOW()
WHERE `code` = 'SYS-EQUIPMENT';

UPDATE `mes_wm_warehouse_location` l
JOIN `mes_wm_warehouse` w ON w.`code` = 'SYS-EQUIPMENT' AND w.`deleted` = b'0'
SET l.`warehouse_id` = w.`id`, l.`deleted` = b'0', l.`frozen` = b'0',
    l.`updater` = 'system', l.`update_time` = NOW()
WHERE l.`code` = 'SYS-EQUIP-LOC-01';

UPDATE `mes_wm_warehouse_area` a
JOIN `mes_wm_warehouse_location` l ON l.`code` = 'SYS-EQUIP-LOC-01' AND l.`deleted` = b'0'
SET a.`location_id` = l.`id`, a.`deleted` = b'0', a.`status` = 0,
    a.`frozen` = b'0', a.`allow_item_mixing` = b'1', a.`allow_batch_mixing` = b'1',
    a.`updater` = 'system', a.`update_time` = NOW()
WHERE a.`code` = 'SYS-EQUIP-BIN-01';

-- 部署校验：以下查询应各返回 1 条且 deleted=0。
SELECT `id`, `code`, `deleted` FROM `mdm_item_category` WHERE `code` = 'EQUIPMENT';
SELECT `id`, `code`, `deleted` FROM `mes_md_item_type` WHERE `code` = 'EQUIPMENT';
SELECT `id`, `code`, `deleted` FROM `mdm_unit_measure` WHERE `code` = 'TAI';
SELECT `id`, `code`, `deleted` FROM `mes_md_unit_measure` WHERE `code` = 'TAI';
SELECT `id`, `code`, `deleted` FROM `mes_wm_warehouse` WHERE `code` = 'SYS-EQUIPMENT';
SELECT `id`, `code`, `deleted` FROM `mes_wm_warehouse_location` WHERE `code` = 'SYS-EQUIP-LOC-01';
SELECT `id`, `code`, `deleted` FROM `mes_wm_warehouse_area` WHERE `code` = 'SYS-EQUIP-BIN-01';