-- =============================================
-- 修复：工序流转（生产流转卡）-> 流转卡编码 -> 生成，报“编码规则不存在”
-- 原因：mes_md_auto_code_rule 中缺少 code = 'PRO_CARD_CODE' 的规则数据
-- 说明：本脚本可重复执行，不会删除/覆盖已有规则
-- 生成格式：LZK + yyyyMMdd + 3 位流水号（按天循环），例如 LZK20261019001
-- =============================================

-- 1. 流转卡编码规则主表
INSERT INTO mes_md_auto_code_rule
    (code, name, description, max_length, padded, padded_char, padded_method, status, remark, creator, updater)
SELECT 'PRO_CARD_CODE', '工序流转编码', '用于生成工序流转（生产流转卡）编码', 14, b'0', NULL, NULL, 0, NULL, '1', '1'
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM mes_md_auto_code_rule WHERE code = 'PRO_CARD_CODE'
);

-- 2. 固定前缀 LZK
INSERT INTO mes_md_auto_code_part
    (rule_id, sort, type, length, fix_character, serial_start_no, serial_step, cycle_flag, remark, creator, updater)
SELECT r.id, 1, 3, 3, 'LZK', NULL, NULL, NULL, '固定前缀', '1', '1'
FROM mes_md_auto_code_rule r
WHERE r.code = 'PRO_CARD_CODE'
  AND NOT EXISTS (
      SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 1
  );

-- 3. 日期 yyyyMMdd
INSERT INTO mes_md_auto_code_part
    (rule_id, sort, type, length, date_format, serial_start_no, serial_step, cycle_flag, remark, creator, updater)
SELECT r.id, 2, 2, 8, 'yyyyMMdd', NULL, NULL, NULL, '日期', '1', '1'
FROM mes_md_auto_code_rule r
WHERE r.code = 'PRO_CARD_CODE'
  AND NOT EXISTS (
      SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 2
  );

-- 4. 3 位流水号（按天循环，从 1 开始，步长 1）
INSERT INTO mes_md_auto_code_part
    (rule_id, sort, type, length, serial_start_no, serial_step, cycle_flag, cycle_method, remark, creator, updater)
SELECT r.id, 3, 4, 3, 1, 1, b'1', 3, '流水号-按天循环', '1', '1'
FROM mes_md_auto_code_rule r
WHERE r.code = 'PRO_CARD_CODE'
  AND NOT EXISTS (
      SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 3
  );