-- 工艺路线工序产出：按产品归属（product_id 为空表示多产品通用）
CREATE TABLE IF NOT EXISTS `mes_pro_route_process_output` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  `route_process_id` bigint NOT NULL COMMENT '工艺路线工序编号',
  `route_id` bigint NOT NULL COMMENT '工艺路线编号',
  `process_id` bigint NOT NULL COMMENT '工序编号',
  `product_id` bigint DEFAULT NULL COMMENT '归属产品编号，为空表示多产品通用',
  `item_id` bigint NOT NULL COMMENT '产出物料编号',
  `sort` int DEFAULT NULL COMMENT '排序',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_route_process_id` (`route_process_id`) USING BTREE,
  KEY `idx_route_id` (`route_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='MES 工艺路线工序产出归属';

-- 存量迁移：拆分 output_item_id。
-- 规则：产出物料是当前路线绑定的产品 → 归属到自身（产品专属）；否则 → 通用（NULL）。
INSERT INTO `mes_pro_route_process_output`
  (`route_process_id`, `route_id`, `process_id`, `product_id`, `item_id`, `sort`)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 100
)
SELECT p.`id`,
       p.`route_id`,
       p.`process_id`,
       rp.`item_id` AS product_id,
       CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(p.`output_item_id`, ',', seq.n), ',', -1)) AS UNSIGNED) AS item_id,
       seq.n
FROM `mes_pro_route_process` p
JOIN seq
  ON seq.n <= 1 + LENGTH(p.`output_item_id`) - LENGTH(REPLACE(p.`output_item_id`, ',', ''))
LEFT JOIN `mes_pro_route_product` rp
  ON rp.`route_id` = p.`route_id`
 AND rp.`deleted` = 0
 AND rp.`item_id` = CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(p.`output_item_id`, ',', seq.n), ',', -1)) AS UNSIGNED)
WHERE p.`deleted` = 0
  AND p.`output_item_id` IS NOT NULL
  AND p.`output_item_id` <> ''
  AND TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(p.`output_item_id`, ',', seq.n), ',', -1)) <> ''
  AND NOT EXISTS (
    SELECT 1 FROM `mes_pro_route_process_output` o
    WHERE o.`route_process_id` = p.`id`
      AND o.`item_id` = CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(p.`output_item_id`, ',', seq.n), ',', -1)) AS UNSIGNED)
  );