-- 生产记录关联生产计划（订单-计划-产品-记录 全链路 / 无计划拦截）
-- 背景：
--   1) mes_product_record（生产记录/工位结果）没有计划字段，工位结果层面无法追溯订单与计划；
--   2) 提交工位结果时没有"无计划"校验，无计划工件照样可以过站；
--   3) 生产记录列表/导出无法按订单、计划查询。
-- 本脚本负责：加字段 + 加索引 + 存量回填 + 拦截总开关。
-- 说明：结构补齐脚本，字段必须先执行本脚本再发布应用，否则生产记录相关查询会报错。

-- ---------- 1. 生产记录表增加生产计划字段 ----------
SET @col_exists = (SELECT COUNT(1) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mes_product_record' AND COLUMN_NAME = 'plan_id');
SET @ddl = IF(@col_exists = 0,
  'ALTER TABLE `mes_product_record` ADD COLUMN `plan_id` VARCHAR(64) DEFAULT NULL COMMENT ''生产计划'' AFTER `cate_sn`',
  'SELECT ''mes_product_record.plan_id 已存在'' AS message');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------- 2. 索引：按计划/订单查记录 ----------
SET @idx_exists = (SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mes_product_record' AND INDEX_NAME = 'idx_mpr_plan_id');
SET @ddl = IF(@idx_exists = 0,
  'CREATE INDEX `idx_mpr_plan_id` ON `mes_product_record` (`plan_id`)',
  'SELECT ''idx_mpr_plan_id 已存在'' AS message');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------- 2.1 索引：按工件码查记录（无计划拦截回退查询用）----------
-- 注意：mes_product_record 数据量较大时建索引耗时较长，可在低峰期执行
SET @idx_exists = (SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mes_product_record' AND COLUMN_NAME = 'sn');
SET @ddl = IF(@idx_exists = 0,
  'CREATE INDEX `idx_mpr_sn` ON `mes_product_record` (`sn`)',
  'SELECT ''mes_product_record.sn 已有索引，跳过'' AS message');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------- 3. 存量数据回填：工件有计划 -> 回填到该工件的所有记录 ----------
UPDATE `mes_product_record` r
  JOIN `mes_product` p ON p.`sn` = r.`sn`
  SET r.`plan_id` = p.`plan_id`
 WHERE (r.`plan_id` IS NULL OR r.`plan_id` = '')
   AND p.`plan_id` IS NOT NULL AND p.`plan_id` <> '';

-- ---------- 4. 拦截总开关 ----------
-- 0=不拦截（应急/灰度），1=拦截（提交工位结果时工件必须关联有效生产计划）
-- 注意：直插 js_sys_config 后需重启服务，或在"系统配置"页面重新保存一次触发缓存刷新
DELETE FROM `js_sys_config` WHERE `config_key` = 'mes.plan.intercept';
INSERT INTO `js_sys_config`
  (`id`, `config_name`, `config_key`, `config_value`, `is_sys`, `create_by`, `create_date`, `update_by`, `update_date`, `remarks`)
VALUES
  ('2026102110000001','生产计划无计划拦截','mes.plan.intercept','1','0','system',NOW(),'system',NOW(),
   '提交工位结果时校验工件是否关联有效生产计划；0=关闭拦截，1=开启拦截');

-- ---------- 5. 验证 ----------
-- 5.1 字段与索引
SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mes_product_record' AND COLUMN_NAME = 'plan_id';
-- 5.2 仍有记录未回填（这些工件在生产但没有计划，发布后会拦截）
SELECT r.sn, r.cate_sn, p.plan_id AS product_plan_id
  FROM mes_product_record r
  LEFT JOIN mes_product p ON p.sn = r.sn
 WHERE (r.plan_id IS NULL OR r.plan_id = '') AND r.content <> 'UDF'
 LIMIT 100;
-- 5.3 记录与工件计划不一致（应为 0 行）
SELECT r.id, r.sn, r.plan_id AS record_plan, p.plan_id AS product_plan
  FROM mes_product_record r
  JOIN mes_product p ON p.sn = r.sn
 WHERE IFNULL(r.plan_id,'') <> IFNULL(p.plan_id,'')
 LIMIT 100;
-- 5.4 未关联订单的计划（应为 0 行）
SELECT id, order_id, day, shift_id, nums FROM mes_product_plan
 WHERE order_id IS NULL OR order_id = '';
-- 5.5 开关
SELECT config_key, config_value FROM js_sys_config WHERE config_key = 'mes.plan.intercept';

SELECT '生产记录关联生产计划脚本执行完成' AS message;