DELIMITER $$

CREATE PROCEDURE sp_scheduled_tasks_batch_delete_id (IN p_cutoff_date VARCHAR(50))
BEGIN

    DECLARE v_rows_deleted INT DEFAULT 0;
    DECLARE v_batch_size INT DEFAULT 10000;
    DECLARE v_max_id BIGINT DEFAULT 0;
    DECLARE v_sleep_seconds DECIMAL(5,2) DEFAULT 0.1;
    DECLARE v_parsed_date DATE;
    DECLARE v_now DATETIME DEFAULT NOW();
    DECLARE v_hours_to_keep TINYINT DEFAULT 24;
    DECLARE error_message VARCHAR(255);

    -- Attempt to parse the date string using a specific format (e.g., 'YYYY-MM-DD')
    SET v_parsed_date = STR_TO_DATE(p_cutoff_date, '%Y-%m-%d');

    -- Check if the parsing was successful and if the original string matches the re-formatted date
    IF v_parsed_date IS NULL OR p_cutoff_date != DATE_FORMAT(v_parsed_date, '%Y-%m-%d') THEN
        -- If not in the correct format or parsing failed, signal an error and exit
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid date format. Please use YYYY-MM-DD.';
    END IF;
    
    -- If the date is within the defined number of hours to retainthe  data.
    IF p_cutoff_date >= DATE_SUB(NOW(), INTERVAL v_hours_to_keep HOUR) AND p_cutoff_date <= NOW() THEN
        SET error_message = CONCAT('Error: The provided date is cannot be with the last ', v_hours_to_keep, ' hours from the current time.');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = error_message;
    END IF;
    
    -- If the date is in the future, exit
    IF p_cutoff_date > NOW() THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: The provided date is in the future.';
    END IF;
    
    SET v_max_id = (SELECT MAX(id) FROM scheduled_tasks s WHERE s.started <= p_cutoff_date);
    
    SELECT CONCAT(v_now, ' Starting batch deletion for data older than or equal to: ', p_cutoff_date) AS Message;

    batch_delete_loop: LOOP
        
        SET v_now = NOW();
        
        DELETE FROM scheduled_tasks 
        WHERE id <= v_max_id
        LIMIT v_batch_size;

        SET v_rows_deleted = ROW_COUNT();

        SELECT CONCAT(v_now, ' Batch deleted ', v_rows_deleted, ' rows. Continuing...') AS Progress;

        IF v_rows_deleted = 0 THEN
            LEAVE batch_delete_loop;
        END IF;

        IF v_sleep_seconds > 0 THEN
            DO SLEEP(v_sleep_seconds);
        END IF;

    END LOOP batch_delete_loop;

    SELECT CONCAT(NOW(), ' Batch deletion completed for data older than or equal to: ', p_cutoff_date) AS Message;

END$$

DELIMITER ;
