-- 新高电力：补齐质检与仓库业务自动编码规则。
-- 可重复执行：已有规则和分段不会重复插入。

START TRANSACTION;

DROP TEMPORARY TABLE IF EXISTS tmp_xgdl_autocode_rule;
CREATE TEMPORARY TABLE tmp_xgdl_autocode_rule (
    code VARCHAR(64) COLLATE utf8mb4_unicode_ci PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255) NOT NULL,
    max_length INT NOT NULL,
    prefix VARCHAR(16) NOT NULL,
    has_date TINYINT NOT NULL,
    serial_length INT NOT NULL
);

INSERT INTO tmp_xgdl_autocode_rule
    (code, name, description, max_length, prefix, has_date, serial_length)
VALUES
    ('QC_INDICATOR_CODE', '质检指标编码', '质检指标自动编码', 12, 'QI_', 0, 6),
    ('QC_TEMPLATE_CODE', '质检方案编码', '质检方案自动编码', 12, 'QT_', 0, 6),
    ('QC_NCR_CODE', '不合格品处理单编码', '不合格品处理单自动编码', 16, 'NCR', 1, 4),
    ('QC_OUTSOURCE_TEST_CODE', '委外检测单编码', '委外检测/试验单自动编码', 16, 'WT', 1, 4),
    ('WM_PRODUCT_ISSUE_CODE', '生产领料单编码', '生产领料出库单自动编码', 16, 'PI', 1, 4),
    ('WM_PRODUCT_RECEIPT_CODE', '产品入库单编码', '产品完工入库单自动编码', 16, 'PR', 1, 4);

INSERT INTO mes_md_auto_code_rule
    (code, name, description, max_length, padded, padded_char, padded_method,
     status, remark, creator, create_time, updater, update_time, deleted)
SELECT t.code, t.name, t.description, t.max_length,
       b'0', NULL, NULL, 0, '质检/仓库必要规则', '1', NOW(), '1', NOW(), b'0'
FROM tmp_xgdl_autocode_rule t
WHERE NOT EXISTS (
    SELECT 1 FROM mes_md_auto_code_rule r
    WHERE r.code = t.code AND r.deleted = b'0'
);

-- 固定前缀。
INSERT INTO mes_md_auto_code_part
    (rule_id, sort, type, length, date_format, fix_character, serial_start_no,
     serial_step, cycle_flag, cycle_method, remark, creator, create_time, updater, update_time, deleted)
SELECT r.id, 1, 3, CHAR_LENGTH(t.prefix), NULL, t.prefix, NULL,
       NULL, b'0', NULL, '固定前缀', '1', NOW(), '1', NOW(), b'0'
FROM tmp_xgdl_autocode_rule t
JOIN mes_md_auto_code_rule r ON r.code = t.code AND r.deleted = b'0'
WHERE NOT EXISTS (
    SELECT 1 FROM mes_md_auto_code_part p
    WHERE p.rule_id = r.id AND p.sort = 1 AND p.deleted = b'0'
);

-- 需要业务日期的单据使用 yyyyMMdd。
INSERT INTO mes_md_auto_code_part
    (rule_id, sort, type, length, date_format, fix_character, serial_start_no,
     serial_step, cycle_flag, cycle_method, remark, creator, create_time, updater, update_time, deleted)
SELECT r.id, 2, 2, 8, 'yyyyMMdd', NULL, NULL,
       NULL, b'0', NULL, '业务日期', '1', NOW(), '1', NOW(), b'0'
FROM tmp_xgdl_autocode_rule t
JOIN mes_md_auto_code_rule r ON r.code = t.code AND r.deleted = b'0'
WHERE t.has_date = 1
  AND NOT EXISTS (
      SELECT 1 FROM mes_md_auto_code_part p
      WHERE p.rule_id = r.id AND p.sort = 2 AND p.deleted = b'0'
  );

-- 有日期的流水号按日循环；基础资料编码使用连续流水号。
INSERT INTO mes_md_auto_code_part
    (rule_id, sort, type, length, date_format, fix_character, serial_start_no,
     serial_step, cycle_flag, cycle_method, remark, creator, create_time, updater, update_time, deleted)
SELECT r.id, IF(t.has_date = 1, 3, 2), 4, t.serial_length, NULL, NULL, 1,
       1, IF(t.has_date = 1, b'1', b'0'), IF(t.has_date = 1, 3, NULL),
       IF(t.has_date = 1, '按日循环流水号', '连续流水号'),
       '1', NOW(), '1', NOW(), b'0'
FROM tmp_xgdl_autocode_rule t
JOIN mes_md_auto_code_rule r ON r.code = t.code AND r.deleted = b'0'
WHERE NOT EXISTS (
    SELECT 1 FROM mes_md_auto_code_part p
    WHERE p.rule_id = r.id
      AND p.sort = IF(t.has_date = 1, 3, 2)
      AND p.deleted = b'0'
);

DROP TEMPORARY TABLE tmp_xgdl_autocode_rule;

COMMIT;