DELIMITER $$

DROP PROCEDURE IF EXISTS `AutoRefreshExpiredOTPs`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `AutoRefreshExpiredOTPs`()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_pass_uid VARCHAR(255);
    DECLARE v_field_uid VARCHAR(255);
    DECLARE v_otp_prefix VARCHAR(10);
    DECLARE v_otp_suffix VARCHAR(10);
    DECLARE v_otp_length INT;
    DECLARE v_otp_type ENUM('numeric', 'alphanumeric');
    DECLARE v_interval_minutes INT;
    DECLARE v_old_otp VARCHAR(255);
    
    -- Cursor for OTPs that need refresh
    -- Now includes field_uid to handle multiple OTPs per pass
    DECLARE cur CURSOR FOR 
        SELECT 
            p.pass_uid,
            p.field_uid,
            COALESCE(p.otp_prefix, ''),
            COALESCE(p.otp_suffix, ''),
            COALESCE(p.otp_length, 6),
            COALESCE(p.otp_type, 'numeric'),
            COALESCE(p.interval_minutes, 5),
            p.otp as old_otp
        FROM pass_otp p
        WHERE p.next_refresh_at <= NOW()
        AND p.field_uid IS NOT NULL;  -- Only process OTPs with field_uid (new schema)
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_pass_uid, v_field_uid, v_otp_prefix, v_otp_suffix, v_otp_length, v_otp_type, v_interval_minutes, v_old_otp;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Generate new OTP
        SET @new_otp = GenerateOTP(v_otp_length, v_otp_type, v_otp_prefix, v_otp_suffix);
        SET @current_time = NOW();
        SET @next_refresh = DATE_ADD(@current_time, INTERVAL v_interval_minutes MINUTE);
        
        -- Update OTP using both pass_uid and field_uid (composite key)
        UPDATE pass_otp 
        SET 
            otp = @new_otp,
            generated_at = @current_time,
            next_refresh_at = @next_refresh
        WHERE pass_uid = v_pass_uid 
        AND field_uid = v_field_uid;
        
        -- Add to queue with field_uid (this is what you actually want)
        INSERT INTO otp_refresh_queue (pass_uid, field_uid, old_otp, new_otp, generated_at)
        VALUES (v_pass_uid, v_field_uid, v_old_otp, @new_otp, @current_time);
        
    END LOOP;
    
    CLOSE cur;
    
    -- No system logs - only actual OTP refresh notifications
END$$

DELIMITER ;


