-- 订单-计划-产品 全链路：增加"产品类型(工件类型)"字段
-- 背景：
--   1) 生产订单没有产品类型字段，产品类型只能从 BOM 的 product_cate_id 推导，
--      BOM 未维护工件类型时订单无法排产、计划无法匹配产品；
--   2) 生产计划没有产品类型字段，只能通过 计划->订单->BOM 三级关联反查，
--      既慢又容易因 BOM 变更导致历史计划的产品类型错乱；
--   3) 需要"当前计划/当前产品"接口，客户端/下单页面据此判断当前在生产哪种产品。
-- 本脚本负责：
--   1) mes_product_order 增加 product_cate_id（产品类型，存 mes_product_cate.sn）
--   2) mes_product_plan  增加 product_cate_id（产品类型，存 mes_product_cate.sn）
--   3) 存量数据回填
-- 说明：字段必须先执行本脚本再发布应用。

-- ---------- 1. 生产订单增加产品类型 ----------
SET @col_exists = (SELECT COUNT(1) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mes_product_order' AND COLUMN_NAME = 'product_cate_id');
SET @ddl = IF(@col_exists = 0,
  'ALTER TABLE `mes_product_order` ADD COLUMN `product_cate_id` VARCHAR(50) DEFAULT NULL COMMENT ''产品类型(工件类型编号)'' AFTER `bom_id`',
  'SELECT ''mes_product_order.product_cate_id 已存在'' AS message');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------- 2. 生产计划增加产品类型 ----------
SET @col_exists = (SELECT COUNT(1) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mes_product_plan' AND COLUMN_NAME = 'product_cate_id');
SET @ddl = IF(@col_exists = 0,
  'ALTER TABLE `mes_product_plan` ADD COLUMN `product_cate_id` VARCHAR(50) DEFAULT NULL COMMENT ''产品类型(工件类型编号)'' AFTER `order_id`',
  'SELECT ''mes_product_plan.product_cate_id 已存在'' AS message');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------- 3. 存量回填：订单按 BOM 的工件类型补齐 ----------
UPDATE `mes_product_order` o
  JOIN `wms_bom` b ON b.`id` = o.`bom_id`
  SET o.`product_cate_id` = b.`product_cate_id`
 WHERE (o.`product_cate_id` IS NULL OR o.`product_cate_id` = '')
   AND b.`product_cate_id` IS NOT NULL AND b.`product_cate_id` <> '';

-- ---------- 4. 存量回填：计划按订单的产品类型补齐（订单为空时用 BOM 兜底） ----------
UPDATE `mes_product_plan` p
  JOIN `mes_product_order` o ON o.`id` = p.`order_id`
  SET p.`product_cate_id` = IFNULL(NULLIF(o.`product_cate_id`, ''),
        (SELECT b.`product_cate_id` FROM `wms_bom` b WHERE b.`id` = o.`bom_id`))
 WHERE p.`product_cate_id` IS NULL OR p.`product_cate_id` = '';

-- ---------- 5. 索引 ----------
SET @idx_exists = (SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mes_product_order' AND INDEX_NAME = 'idx_mpo_product_cate');
SET @ddl = IF(@idx_exists = 0,
  'CREATE INDEX `idx_mpo_product_cate` ON `mes_product_order` (`product_cate_id`)',
  'SELECT ''idx_mpo_product_cate 已存在'' AS message');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists = (SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mes_product_plan' AND INDEX_NAME = 'idx_mpp_product_cate');
SET @ddl = IF(@idx_exists = 0,
  'CREATE INDEX `idx_mpp_product_cate` ON `mes_product_plan` (`product_cate_id`)',
  'SELECT ''idx_mpp_product_cate 已存在'' AS message');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------- 6. 验证 ----------
-- 6.1 字段
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('mes_product_order','mes_product_plan')
   AND COLUMN_NAME = 'product_cate_id';
-- 6.2 订单未回填（这些订单需在下单时补选产品类型）
SELECT id, sn, bom_id FROM mes_product_order
 WHERE product_cate_id IS NULL OR product_cate_id = '';
-- 6.3 计划未回填（同上，排产时按订单自动补齐）
SELECT p.id, p.order_id, p.day, p.nums FROM mes_product_plan p
 WHERE p.product_cate_id IS NULL OR p.product_cate_id = '';
-- 6.4 计划与订单产品类型不一致（应为 0 行）
SELECT p.id, p.product_cate_id AS plan_cate, o.product_cate_id AS order_cate
  FROM mes_product_plan p JOIN mes_product_order o ON o.id = p.order_id
 WHERE IFNULL(p.product_cate_id,'') <> IFNULL(o.product_cate_id,'');
-- 6.5 计划与产品类型主数据不一致（应为 0 行）
SELECT DISTINCT p.product_cate_id FROM mes_product_plan p
 WHERE p.product_cate_id IS NOT NULL AND p.product_cate_id <> ''
   AND NOT EXISTS (SELECT 1 FROM mes_product_cate c WHERE c.sn = p.product_cate_id);

SELECT '订单-计划 产品类型脚本执行完成' AS message;