testing available timeslots

[Dimitris Papageorgiou]

I am building an appointments scheduling software and I have made a rather complex query to get the available timeslots for booking. My issue has to do with testing. What is the route to take in order to catch all possible edge cases. How to go with that. I assume nothing. Following is the query and the DDL for the 3 tables involved.

SELECT time_fifteen_minutes.TimeColumnMinutes AS AvailableTimeslots,
CASE
WHEN store_open.open_time_b IS NULL THEN "First"
WHEN time_fifteen_minutes.TimeColumnMinutes >= store_open.open_time
AND time_fifteen_minutes.TimeColumnMinutes <= SUBTIME(store_open.close_time, "100")
THEN "First"
WHEN time_fifteen_minutes.TimeColumnMinutes >= store_open.open_time_b
AND time_fifteen_minutes.TimeColumnMinutes <= SUBTIME(store_open.close_time_b, "100")
THEN "Second"
ELSE "Unknown Shift"
END AS ShiftLabel
FROM time_fifteen_minutes
INNER JOIN store_open
ON
CASE
WHEN store_open.open_time_b IS NULL
THEN time_fifteen_minutes.TimeColumnMinutes >= store_open.open_time
AND time_fifteen_minutes.TimeColumnMinutes <= SUBTIME(store_open.close_time, "100")
ELSE
(time_fifteen_minutes.TimeColumnMinutes >= store_open.open_time
AND time_fifteen_minutes.TimeColumnMinutes <= SUBTIME(store_open.close_time, "100"))
OR
(time_fifteen_minutes.TimeColumnMinutes >= store_open.open_time_b
AND time_fifteen_minutes.TimeColumnMinutes <= SUBTIME(store_open.close_time_b, "100"))
END
LEFT JOIN (SELECT
TimeColumnMinutes AS A
FROM time_fifteen_minutes
INNER JOIN appointments
ON ADDTIME(time_fifteen_minutes.TimeColumnMinutes,?) >= appointments.startime
AND time_fifteen_minutes.TimeColumnMinutes <= SUBTIME(appointments.endTime,"100")
WHERE appointments.bookedfor = ( SELECT bus_user_ID FROM business_users
WHERE URL_identifier = ?)
AND appointments.startDate = ?
)
AS NonAvailable
ON NonAvailable.A = time_fifteen_minutes.TimeColumnMinutes
WHERE NonAvailable.A IS NULL
AND store_open.day = DAYOFWEEK( ? )
AND store_open.b_user_ID = ( SELECT bus_user_ID
FROM business_users
WHERE URL_identifier = ?)
AND IF(STR_TO_DATE(?, "%Y-%m-%d") = CURRENT_DATE(),TimeColumnMinutes > CURRENT_TIME(),TRUE)');

The query so far as expected, I just need advice on testing edge cases.

The DDLs now:

CREATE TABLE `appointments` (
`apID` int unsigned NOT NULL AUTO_INCREMENT,
`userID` mediumint unsigned NOT NULL,
`bookedfor` mediumint unsigned NOT NULL COMMENT '≤ί άΫΪ▐ ΪύΊ ≤Ϊ▐Έύ άΊάή±▄÷ΎΊΪάώ Ϊά ±άΊΪίέΎ² ήώά ΪΎΊ ΎΏΎ▀ΎΊ ΆΈί▀ΊΎΊΪάώ Ϊά ±άΊΪίέΎ² Άάώ ίΉ÷άΊ▀όΎΊΪάώ ≤ΪΎ backend ΪΎΫ bookedfor business_user',
`appont_close_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`startDate` date NOT NULL,
`startime` time NOT NULL,
`endDate` date NOT NULL,
`endTime` time NOT NULL,
`apps_origin` enum('frontend','backend') NOT NULL,
`deleted` tinyint unsigned DEFAULT NULL,
`cancelled` tinyint unsigned NOT NULL DEFAULT '0',
`timezone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'Europe/Athens',
PRIMARY KEY (`apID`),
KEY `startDate` (`startDate`),
KEY `bookedfor` (`bookedfor`),
KEY `to_userID` (`userID`),
CONSTRAINT `appointments_bookedfor_ac466374_fk` FOREIGN KEY (`bookedfor`) REFERENCES `business_users` (`bus_user_id`),
CONSTRAINT `to_userID` FOREIGN KEY (`userID`) REFERENCES `users` (`user_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1376 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `store_open` (
`store_open_ID` int NOT NULL AUTO_INCREMENT,
`b_user_ID` mediumint unsigned DEFAULT NULL,
`open_time` time NOT NULL,
`close_time` time NOT NULL,
`open_time_b` time DEFAULT NULL,
`close_time_b` time DEFAULT NULL,
`day` tinyint NOT NULL,
PRIMARY KEY (`store_open_ID`),
KEY `b_user_ID` (`b_user_ID`),
KEY `day` (`day`),
CONSTRAINT `store_open_b_user_ID_697f288c_fk` FOREIGN KEY (`b_user_ID`) REFERENCES `business_users` (`bus_user_id`),
CONSTRAINT `store_open_ibfk_1` FOREIGN KEY (`day`) REFERENCES `weekdays` (`dayID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=469 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='schedule.php: the weekly schedule of the business';


CREATE TABLE `time_fifteen_minutes` (
`TimeColumnMinutes` time DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;