get open time slots for bookings where bookings can be overlapped if there's more than one slot for booking (postgres)

availability_function.sql

-- table structure for reference 
-- `areas`
-- column  type          nullable
-- ------------------------------
-- id	  int4	        NO	
-- name	  varchar(11)   YES	
-- slots  int4	        NO	
-- 
-- `avail`
-- column           type    nullable
-- ---------------------------------
-- id	            int4	NO	
-- dayofweek	    int4	YES	
-- available_from	time	YES	
-- available_to	    time	YES	
-- area_id	        int4	YES	
-- 
-- 
-- `bookings`
-- column       type        nullable
-- ---------------------------------
-- id	        int4	    NO	
-- area_id	    int4	    YES	
-- start_time	timestamp	YES	
-- end_time	    timestamp	YES	
-- status	    varchar(30)	YES	


-- Clean up
DROP FUNCTION IF EXISTS get_availability (integer, date);
DROP VIEW IF EXISTS timeslots;

-- Create the timeslots with an interval of 15 mins from start of day
CREATE VIEW timeslots AS
SELECT
	CAST(
		'00:00' AS TIME) AS timeslot
UNION
SELECT
	CAST(
		'00:00' + interval '15 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '30 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '45 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '60 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '75 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '90 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '105 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '120 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '135 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '150 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '165 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '180 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '195 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '210 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '225 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '240 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '255 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '270 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '285 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '300 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '315 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '330 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '345 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '360 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '375 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '390 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '405 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '420 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '435 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '450 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '465 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '480 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '495 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '510 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '525 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '540 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '555 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '570 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '585 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '600 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '615 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '630 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '645 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '660 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '675 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '690 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '705 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '720 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '735 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '750 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '765 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '780 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '795 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '810 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '825 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '840 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '855 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '870 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '885 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '900 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '915 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '930 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '945 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '960 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '975 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '990 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1005 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1020 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1035 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1050 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1065 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1080 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1095 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1110 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1125 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1140 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1155 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1170 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1185 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1200 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1215 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1230 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1245 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1260 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1275 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1290 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1305 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1320 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1335 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1350 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1365 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1380 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1395 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1410 min' AS TIME)
UNION
SELECT
	CAST(
		'00:00' + interval '1425 min' AS TIME)
ORDER BY
	timeslot ASC;


-- Create a function of availability that takes in the area it's supposed to look at 
-- and the booking date the availability is to be searched for
CREATE OR REPLACE FUNCTION get_availability (_area_id int, _booking_date date)
	RETURNS TABLE (
		timeslot time, slots int, booked_slots bigint)
	LANGUAGE plpgsql
	AS $$
BEGIN
	RETURN query
	SELECT
		*
	FROM (
		SELECT
			timeslots.timeslot,
			areas.slots,
			count(
				CASE WHEN date(bookings.start_time) + timeslots.timeslot >= bookings.start_time
					AND date(bookings.start_time) + timeslots.timeslot < bookings.end_time THEN
					bookings.id
				END) AS booked_slots
		FROM
			bookings
			INNER JOIN areas ON bookings.area_id = areas.id
			INNER JOIN avail ON bookings.area_id = avail.area_id
				AND avail.dayofweek = EXTRACT(dow FROM _booking_date)
				INNER JOIN timeslots ON timeslots.timeslot >= avail.available_from
					AND timeslots.timeslot < avail.available_to
			WHERE
				bookings.area_id = _area_id
			GROUP BY
				timeslots.timeslot,
				areas.slots
			ORDER BY
				timeslots.timeslot) AS avails
WHERE
	avails.booked_slots < avails.slots;
END
$$;

-- example of how to use the query
-- select * from get_availability(1,date '2022-06-01');