SET GLOBAL event_scheduler = ON;

DELIMITER $$

CREATE EVENT IF NOT EXISTS clear_and_reset_intraday_data
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    -- Create a temporary table to hold today's data
    CREATE TEMPORARY TABLE tmp_intraday_histories AS
    SELECT * FROM tbl_intraday_histories WHERE DATE(createdAt) >= CURDATE();

    -- Truncate the main table to reset AUTO_INCREMENT
    TRUNCATE TABLE tbl_intraday_histories;

    -- Insert back today's data
    INSERT INTO tbl_intraday_histories SELECT * FROM tmp_intraday_histories;

    -- Drop the temporary table
    DROP TEMPORARY TABLE tmp_intraday_histories;
END$$

DELIMITER ;