SET @inputStartDate = "2024-01-02 12:00:00";
SET @inputEndDate = "2024-01-02 13:30:00";


SET @inputRoomId = 11;

SELECT NOT EXISTS ( SELECT r.name  FROM room r
LEFT JOIN calendarEvent c ON r.id = c.roomId
LEFT JOIN calendarEventPeriod p ON c.id = p.calendarEventId

WHERE ((
/* inputs between event dates */
((@inputStartDate  BETWEEN c.startDate AND c.endDate) AND (@inputEndDate  BETWEEN c.startDate AND c.endDate) AND (r.id = @inputRoomId))

AND (SELECT DATE_FORMAT(@inputStartDate, "%H:%i")  BETWEEN (SELECT DATE_FORMAT(c.startDate, "%H:%i")) AND (SELECT DATE_FORMAT(c.endDate, "%H:%i")))

AND (SELECT DATE_FORMAT(@inputEndDate, "%H:%i"))  BETWEEN (SELECT DATE_FORMAT(c.startDate, "%H:%i")) AND (SELECT DATE_FORMAT(c.endDate, "%H:%i")))

/*  event dates between inputs */
AND ((SELECT DATE_FORMAT(c.startDate, "%H:%i")  BETWEEN (SELECT DATE_FORMAT(@inputStartDate, "%H:%i")) AND (SELECT DATE_FORMAT(@inputEndDate, "%H:%i")))

AND (SELECT DATE_FORMAT(c.endDate, "%H:%i"))  BETWEEN (SELECT DATE_FORMAT(@inputStartDate, "%H:%i")) AND (SELECT DATE_FORMAT(@inputEndDate, "%H:%i"))

AND ((c.startDate  BETWEEN @inputStartDate AND @inputEndDate) OR (c.endDate BETWEEN @inputStartDate AND @inputEndDate)))


AND(
    ((c.period = 5) AND (p.day = (DAY(@inputStartDate))) AND (p.month = (MONTH(@inputStartDate))))
     OR((c.period = 4) AND (p.day = (DAY(@inputStartDate))))
     OR ((c.period = 3) AND (p.dayOfWeek = (DAYOFWEEK(@inputStartDate))))
     OR (c.period = 2)
     OR (c.period = 1)
     ))) AS isRoomAvailable