Create scheduler method for removing expired events

[LKASTI]

Expired events, events where 24 hours have already passed since their end time, must be removed from the database on a 24 hour basis, or any other elapsed time we agree on. This can be done with the @ Scheduled annotation with parameters that specify intervals of time.

Resources: https://spring.io/guides/gs/scheduling-tasks

[LKASTI]

I have started working on this method.

[LKASTI]

I'm trying to create the SQL query for checking for expired events, and I've noticed an implementation feature that kind of conflicts with the kind of query I'm creating.

When start and end datetimes are scraped from the event page, if there is no end datetime specified on the page, the script element will still contain an endDate. The problem is that the end datetime will default to 00:00:00, which means a start datetime can occur after the end datetime.

Here's an example of this: Chess Pep Rally

I could try to write my query differently to account for this, but I think this will also cause issues when we put in the constraint that a startTime must always occur after endTIme.

Going to try to identify the part in the page scraper service code that will causes this outcome and discuss with @jonathan-jlam .

[LKASTI]

This is the SQL statement to delete expired events

DELETE FROM event WHERE eventid IN (
    SELECT eventid
        FROM(
        SELECT DISTINCT eventid 
        FROM event NATURAL JOIN appointment
        WHERE unix_timestamp(NOW()) >= unix_timestamp(ADDTIME(end_time, '1 00:00:00'))
    ) AS e
 );

However, unfortunately it seems that when an Event is deleted, it's associated Appointment is not deleted. Which makes you think, is the onDelete Cascade constraint enabled in the database and/or SpringBoot, which it is.

After doing much research into this issue, the only conclusions I could come to was that this is caused by the One-To-One relationship or custom delete queries created in the repository file.

Regardless I have re-written the query to just get all the eventIDs of expired events, then another query is called in the event service file implementation to delete a batch of events given their IDs. This seemed to cascade the delete to associated appointments.

SELECT eventid 
FROM event NATURAL JOIN appointment
WHERE unix_timestamp(NOW()) >= unix_timestamp(ADDTIME(end_time, '1 00:00:00'));

Storing this ^ call's resulting IDs into a field and then calling repository.deleteAllById(expiredEventIDs); works as intended. This method is called in ScheduledTasks.java on a 24 hour interval.

It's also worth noting that I had to remove the cascading annotation parameter from the Many-To-One relationship for the location field in the Appointment class. This is because multiple Appointments can share the same location, so we shouldn't cascade delete the associated appointment.

Code pushed to 'Landin' branch, and will be merged later today into main and BackEnd.