-- 新高电力：补齐“生产管控”缺失的自动编码规则。
-- 可重复执行：规则或分段已存在时不会重复插入。

START TRANSACTION;

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 'PRO_MPS_CODE', '生产计划编码', '生产主计划（MPS）自动编码', 20,
       b'0', NULL, NULL, 0, '生产管控必要规则', '1', NOW(), '1', NOW(), b'0'
WHERE NOT EXISTS (
    SELECT 1 FROM mes_md_auto_code_rule WHERE code = 'PRO_MPS_CODE' AND deleted = b'0'
);

SET @pro_mps_rule_id = (
    SELECT id FROM mes_md_auto_code_rule
    WHERE code = 'PRO_MPS_CODE' AND deleted = b'0' ORDER BY id LIMIT 1
);

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 @pro_mps_rule_id, 1, 3, 3, NULL, 'MPS', NULL, NULL, b'0', NULL,
       '固定前缀', '1', NOW(), '1', NOW(), b'0'
WHERE NOT EXISTS (
    SELECT 1 FROM mes_md_auto_code_part
    WHERE rule_id = @pro_mps_rule_id AND sort = 1 AND 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 @pro_mps_rule_id, 2, 2, 8, 'yyyyMMdd', NULL, NULL, NULL, b'0', NULL,
       '业务日期', '1', NOW(), '1', NOW(), b'0'
WHERE NOT EXISTS (
    SELECT 1 FROM mes_md_auto_code_part
    WHERE rule_id = @pro_mps_rule_id AND sort = 2 AND 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 @pro_mps_rule_id, 3, 4, 4, NULL, NULL, 1, 1, b'1', 3,
       '按日循环流水号', '1', NOW(), '1', NOW(), b'0'
WHERE NOT EXISTS (
    SELECT 1 FROM mes_md_auto_code_part
    WHERE rule_id = @pro_mps_rule_id AND sort = 3 AND deleted = b'0'
);

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 'PRO_MAINTENANCE_PLAN_CODE', '电力作业计划编码', '电力作业计划自动编码', 16,
       b'0', NULL, NULL, 0, '生产管控必要规则', '1', NOW(), '1', NOW(), b'0'
WHERE NOT EXISTS (
    SELECT 1 FROM mes_md_auto_code_rule
    WHERE code = 'PRO_MAINTENANCE_PLAN_CODE' AND deleted = b'0'
);

SET @pro_maintenance_plan_rule_id = (
    SELECT id FROM mes_md_auto_code_rule
    WHERE code = 'PRO_MAINTENANCE_PLAN_CODE' AND deleted = b'0' ORDER BY id LIMIT 1
);

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 @pro_maintenance_plan_rule_id, 1, 3, 3, NULL, 'MP_', NULL, NULL, b'0', NULL,
       '固定前缀', '1', NOW(), '1', NOW(), b'0'
WHERE NOT EXISTS (
    SELECT 1 FROM mes_md_auto_code_part
    WHERE rule_id = @pro_maintenance_plan_rule_id AND sort = 1 AND 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 @pro_maintenance_plan_rule_id, 2, 2, 8, 'yyyyMMdd', NULL, NULL, NULL, b'0', NULL,
       '业务日期', '1', NOW(), '1', NOW(), b'0'
WHERE NOT EXISTS (
    SELECT 1 FROM mes_md_auto_code_part
    WHERE rule_id = @pro_maintenance_plan_rule_id AND sort = 2 AND 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 @pro_maintenance_plan_rule_id, 3, 4, 4, NULL, NULL, 1, 1, b'1', 3,
       '按日循环流水号', '1', NOW(), '1', NOW(), b'0'
WHERE NOT EXISTS (
    SELECT 1 FROM mes_md_auto_code_part
    WHERE rule_id = @pro_maintenance_plan_rule_id AND sort = 3 AND deleted = b'0'
);

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 'TM_TOOL_CODE', '工装夹具编码', '工装夹具台账自动编码', 12,
       b'0', NULL, NULL, 0, '生产管控必要规则', '1', NOW(), '1', NOW(), b'0'
WHERE NOT EXISTS (
    SELECT 1 FROM mes_md_auto_code_rule WHERE code = 'TM_TOOL_CODE' AND deleted = b'0'
);

SET @tm_tool_rule_id = (
    SELECT id FROM mes_md_auto_code_rule
    WHERE code = 'TM_TOOL_CODE' AND deleted = b'0' ORDER BY id LIMIT 1
);

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 @tm_tool_rule_id, 1, 3, 3, NULL, 'TL_', NULL, NULL, b'0', NULL,
       '固定前缀', '1', NOW(), '1', NOW(), b'0'
WHERE NOT EXISTS (
    SELECT 1 FROM mes_md_auto_code_part
    WHERE rule_id = @tm_tool_rule_id AND sort = 1 AND 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 @tm_tool_rule_id, 2, 4, 6, NULL, NULL, 1, 1, b'0', NULL,
       '连续流水号', '1', NOW(), '1', NOW(), b'0'
WHERE NOT EXISTS (
    SELECT 1 FROM mes_md_auto_code_part
    WHERE rule_id = @tm_tool_rule_id AND sort = 2 AND deleted = b'0'
);

COMMIT;