DELIMITER //
DROP FUNCTION if EXISTs `func_get_task_batch_no`;//
DELIMITER ;

DELIMITER //
CREATE FUNCTION `func_get_task_batch_no`(preInfo VARCHAR(60)) RETURNS varchar(60) CHARSET utf8mb4
BEGIN
    DECLARE v_last_sequence VARCHAR(2);
    DECLARE v_cur_letter CHAR(1);
    DECLARE v_cur_number INT;
    DECLARE v_new_letter CHAR(1);
    DECLARE v_new_number INT;
    DECLARE v_cur_year INT;
    DECLARE v_month_letter CHAR(1);
    DECLARE v_day_num INT;
    DECLARE v_batch_no VARCHAR(60);
    
    DECLARE v_letters VARCHAR(24) DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWX';
    DECLARE v_letter_pos INT;
    
    SET v_cur_year = YEAR(NOW()) % 100;
    SET v_month_letter = CHAR(ASCII('A') + MONTH(NOW()) - 1);
    SET v_day_num = DAY(NOW());
    
    -- 获取当前年份的序列号
    SELECT number_info INTO v_last_sequence
    FROM task_batch_index
    WHERE pre_info = preInfo 
      AND date_info = v_cur_year
    FOR UPDATE;
    
    -- 如果没有记录，从 A0 开始（注意：这是当前要使用的，不是下一个）
    IF v_last_sequence IS NULL THEN
        SET v_cur_letter = 'A';
        SET v_cur_number = 0;
    ELSE
        SET v_cur_letter = SUBSTRING(v_last_sequence, 1, 1);
        SET v_cur_number = CAST(SUBSTRING(v_last_sequence, 2, 1) AS UNSIGNED);
    END IF;
    
    -- 当前使用的序列号就是 v_cur_letter + v_cur_number
    -- 计算下一个序列号（用于更新表，下次使用）
    IF v_cur_number < 9 THEN
        SET v_new_letter = v_cur_letter;
        SET v_new_number = v_cur_number + 1;
    ELSE
        SET v_letter_pos = LOCATE(v_cur_letter, v_letters);
        IF v_letter_pos < 24 THEN
            SET v_new_letter = SUBSTRING(v_letters, v_letter_pos + 1, 1);
            SET v_new_number = 0;
        ELSE
            -- X9 -> A0
            SET v_new_letter = 'A';
            SET v_new_number = 0;
        END IF;
    END IF;
    
    -- 更新表为下一个序列号
    INSERT INTO task_batch_index (pre_info, date_info, number_info)
    VALUES (preInfo, v_cur_year, CONCAT(v_new_letter, v_new_number))
    ON DUPLICATE KEY UPDATE number_info = CONCAT(v_new_letter, v_new_number);
    
    -- 构建最终批次号（使用当前序列号，不是下一个）
    SET v_batch_no = CONCAT(preInfo, '-', 
                            v_cur_letter, v_cur_number,
                            LPAD(v_cur_year, 2, '0'), 
                            v_month_letter, 
                            v_day_num);
    
    RETURN v_batch_no;
END//
DELIMITER ;