DELIMITER $$

DROP PROCEDURE IF EXISTS `ManagePassOTP`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ManagePassOTP`(
    IN p_pass_uid VARCHAR(255),
    IN p_field_uid VARCHAR(255),
    IN p_otp_prefix VARCHAR(10),
    IN p_otp_suffix VARCHAR(10),
    IN p_otp_length INT,
    IN p_otp_type ENUM('numeric', 'alphanumeric'),
    IN p_interval_minutes INT
)
BEGIN
    DECLARE existing_otp_count INT DEFAULT 0;
    DECLARE current_time_val DATETIME;
    DECLARE new_otp VARCHAR(255);
    DECLARE generated_time_val DATETIME;
    DECLARE next_refresh_val DATETIME;
    DECLARE old_otp VARCHAR(255);
    DECLARE action_type ENUM('new', 'refreshed', 'valid');
    
    SET current_time_val = NOW();
    SET action_type = 'new';
    
    -- Check if OTP already exists for this pass_uid AND field_uid combination
    SELECT COUNT(*) INTO existing_otp_count 
    FROM `pass_otp` 
    WHERE `pass_uid` = p_pass_uid 
    AND `field_uid` = p_field_uid;
    
    -- If OTP exists, check if it needs refresh
    IF existing_otp_count > 0 THEN
        -- Check if refresh time has reached
        SELECT COUNT(*) INTO existing_otp_count
        FROM `pass_otp`
        WHERE `pass_uid` = p_pass_uid 
        AND `field_uid` = p_field_uid
        AND `next_refresh_at` <= current_time_val;
        
        IF existing_otp_count > 0 THEN
            -- Get old OTP before refresh
            SELECT `otp` INTO old_otp 
            FROM `pass_otp` 
            WHERE `pass_uid` = p_pass_uid 
            AND `field_uid` = p_field_uid;
            
            -- Generate new OTP
            SET new_otp = GenerateOTP(p_otp_length, p_otp_type, p_otp_prefix, p_otp_suffix);
            SET generated_time_val = current_time_val;
            SET next_refresh_val = DATE_ADD(current_time_val, INTERVAL p_interval_minutes MINUTE);
            SET action_type = 'refreshed';
            
            -- Update existing OTP record
            UPDATE `pass_otp` 
            SET 
                `otp` = new_otp,
                `generated_at` = generated_time_val,
                `next_refresh_at` = next_refresh_val,
                `time_intarval` = p_interval_minutes,
                `otp_prefix` = p_otp_prefix,
                `otp_suffix` = p_otp_suffix,
                `otp_length` = p_otp_length,
                `otp_type` = p_otp_type,
                `interval_minutes` = p_interval_minutes
            WHERE `pass_uid` = p_pass_uid 
            AND `field_uid` = p_field_uid;
            
            -- Add to refresh queue for notification
            INSERT INTO `otp_refresh_queue` (pass_uid, field_uid, old_otp, new_otp, generated_at)
            VALUES (p_pass_uid, p_field_uid, old_otp, new_otp, generated_time_val);
            
        ELSE
            -- OTP exists but refresh time hasn't reached yet
            SET action_type = 'valid';
        END IF;
    ELSE
        -- Create new OTP record (first time generation)
        SET new_otp = GenerateOTP(p_otp_length, p_otp_type, p_otp_prefix, p_otp_suffix);
        SET generated_time_val = current_time_val;
        SET next_refresh_val = DATE_ADD(current_time_val, INTERVAL p_interval_minutes MINUTE);
        
        INSERT INTO `pass_otp` (
            `pass_uid`, `field_uid`, `otp`, `generated_at`, `next_refresh_at`, `time_intarval`,
            `otp_prefix`, `otp_suffix`, `otp_length`, `otp_type`, `interval_minutes`
        ) VALUES (
            p_pass_uid, p_field_uid, new_otp, generated_time_val, next_refresh_val, p_interval_minutes,
            p_otp_prefix, p_otp_suffix, p_otp_length, p_otp_type, p_interval_minutes
        );
    END IF;
    
    -- Return result
    IF action_type = 'valid' THEN
        -- Return existing OTP details
        SELECT 
            action_type as action,
            otp,
            generated_at,
            next_refresh_at,
            TIMESTAMPDIFF(SECOND, current_time_val, next_refresh_at) as seconds_until_refresh,
            'OTP is still valid' as message
        FROM `pass_otp` 
        WHERE `pass_uid` = p_pass_uid 
        AND `field_uid` = p_field_uid;
    ELSE
        -- Return new/refreshed OTP details
        SELECT 
            action_type as action,
            new_otp as otp,
            generated_time_val as generated_at,
            next_refresh_val as next_refresh_at,
            p_interval_minutes * 60 as seconds_until_refresh,
            CASE 
                WHEN action_type = 'refreshed' THEN 'OTP was refreshed and queued for notification'
                WHEN action_type = 'new' THEN 'New OTP generated'
            END as message;
    END IF;
    
END$$

DELIMITER ;


