-- 工艺路线产品增加报工产出仓库绑定（可重复执行）
SET @ddl := IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mes_pro_route_product' AND COLUMN_NAME = 'warehouse_id') = 0,
    'ALTER TABLE `mes_pro_route_product` ADD COLUMN `warehouse_id` bigint NULL COMMENT ''报工产出仓库ID'' AFTER `item_id`',
    'SELECT 1'
);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @ddl := IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mes_pro_route_product' AND COLUMN_NAME = 'location_id') = 0,
    'ALTER TABLE `mes_pro_route_product` ADD COLUMN `location_id` bigint NULL COMMENT ''报工产出库区ID'' AFTER `warehouse_id`',
    'SELECT 1'
);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @ddl := IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mes_pro_route_product' AND COLUMN_NAME = 'area_id') = 0,
    'ALTER TABLE `mes_pro_route_product` ADD COLUMN `area_id` bigint NULL COMMENT ''报工产出库位ID'' AFTER `location_id`',
    'SELECT 1'
);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 历史路线产品使用虚拟线边库兜底，新建/编辑时由接口强制选择具体仓库、库区、库位。
UPDATE `mes_pro_route_product` p
JOIN `mes_wm_warehouse` w ON w.`code` = 'WIP_VIRTUAL_WAREHOUSE' AND w.`deleted` = b'0'
JOIN `mes_wm_warehouse_location` l ON l.`code` = 'WIP_VIRTUAL_LOCATION' AND l.`deleted` = b'0'
JOIN `mes_wm_warehouse_area` a ON a.`code` = 'WIP_VIRTUAL_AREA' AND a.`deleted` = b'0'
SET p.`warehouse_id` = COALESCE(p.`warehouse_id`, w.`id`),
    p.`location_id` = COALESCE(p.`location_id`, l.`id`),
    p.`area_id` = COALESCE(p.`area_id`, a.`id`),
    p.`updater` = 'system', p.`update_time` = NOW()
WHERE p.`deleted` = b'0'
  AND (p.`warehouse_id` IS NULL OR p.`location_id` IS NULL OR p.`area_id` IS NULL);