schema-qual.sql

-- This is based on the code from
-- https://raw.githubusercontent.com/CrunchyData/postgres-realtime-demo/main/examples/demo/demo1.sql
-- which was originally released under the Apache 2.0 License
-- https://github.com/CrunchyData/postgres-realtime-demo/blob/main/LICENSE

/**
 * Schema
 */
CREATE TABLE public.room (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE public.availability_rule (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    room_id int NOT NULL REFERENCES public.room (id) ON DELETE CASCADE,
    days_of_week int[] NOT NULL,
    start_time time NOT NULL,
    end_time time NOT NULL,
    generate_weeks_into_future int NOT NULL DEFAULT 52
);

CREATE TABLE public.availability (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    room_id int NOT NULL REFERENCES public.room (id) ON DELETE CASCADE,
    availability_rule_id int NOT NULL REFERENCES public.availability_rule (id) ON DELETE CASCADE,
    available_date date NOT NULL,
    available_range tstzrange NOT NULL
);
CREATE INDEX availability_available_range_gist_idx
    ON availability
    USING gist(available_range);

CREATE TABLE public.unavailability (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    room_id int NOT NULL REFERENCES public.room (id) ON DELETE CASCADE,
    unavailable_date date NOT NULL,
    unavailable_range tstzrange NOT NULL
);
CREATE INDEX unavailability_unavailable_range_gist_idx
    ON unavailability
    USING gist(unavailable_range);

CREATE TABLE public.calendar (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    room_id int NOT NULL REFERENCES public.room (id) ON DELETE CASCADE,
    status text NOT NULL,
    calendar_date date NOT NULL,
    calendar_range tstzrange NOT NULL
);
CREATE INDEX calendar_room_id_calendar_date_idx
    ON calendar (room_id, calendar_date);

/**
 * Helper functions and Triggers
 */

/**
 * ROOM: Need to create an initial calendar for all the data in the room indicating all
 *  the times that everything is closed
 */
CREATE OR REPLACE FUNCTION public.room_insert()
RETURNS trigger
AS $$
    BEGIN
        IF TG_OP = 'INSERT' THEN
             INSERT INTO public.calendar (
                room_id,
                status,
                calendar_date,
                calendar_range
            )
            SELECT
                NEW.id, 'closed', calendar_date, tstzrange(calendar_date, calendar_date + '1 day'::interval)
            FROM generate_series(
                date_trunc('week', CURRENT_DATE),
                date_trunc('week', CURRENT_DATE + '52 weeks'::interval),
                '1 day'::interval
            ) calendar_date;
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

/**
 * Only need to fire trigger inserting a room as UPDATEs do not affect the timings and DELETEs
 * are cascaded
 */
CREATE TRIGGER room_insert
AFTER INSERT ON public.room
FOR EACH ROW
EXECUTE PROCEDURE public.room_insert();


/**
 * AVAILABILITY RULE: Ensure that updates to general availability
 */

/** Helper: Bulk create availability rules; day_of_week ~ isodow (Mon: 1 - Sat: 7) */
CREATE OR REPLACE FUNCTION public.availability_rule_bulk_insert(availability_rule availability_rule, day_of_week int)
RETURNS void
AS $$
    INSERT INTO public.availability (
        room_id,
        availability_rule_id,
        available_date,
        available_range
    )
    SELECT
        $1.room_id,
        $1.id,
        available_date::date + $2 - 1,
        tstzrange(
            /** start of range */
            (available_date::date + $2 - 1) + $1.start_time,
            /** end of range */
            /** check if there is a time wraparound, if so, increment by a day */
            CASE $1.end_time <= $1.start_time
                WHEN TRUE THEN (available_date::date + $2) + $1.end_time
                ELSE (available_date::date + $2 - 1) + $1.end_time
            END
        )
    FROM
        generate_series(
            date_trunc('week', CURRENT_DATE),
            date_trunc('week', CURRENT_DATE) + ($1.generate_weeks_into_future::text || ' weeks')::interval,
            '1 week'::interval
        ) available_date;
$$ LANGUAGE SQL;

/**
 * availability_rule trigger function
 */
CREATE OR REPLACE FUNCTION public.availability_rule_manage()
RETURNS trigger
AS $trigger$
    DECLARE
        day_of_week int;
    BEGIN
        IF TG_OP = 'INSERT' THEN
            /** Loop over the days of the week */
            FOREACH day_of_week IN ARRAY NEW.days_of_week
            LOOP
                PERFORM public.availability_rule_bulk_insert(NEW, day_of_week);
            END LOOP;
        ELSIF TG_OP = 'UPDATE' THEN
            /** Update is tricky if the days_of_week has changed */
            IF OLD.days_of_week IS DISTINCT FROM NEW.days_of_week THEN
                /** NAIVE: We will delete everything and re-insert */
                DELETE FROM public.availability
                WHERE availability_rule_id = NEW.id;
                /** insertion */
                FOREACH day_of_week IN ARRAY NEW.days_of_week
                LOOP
                    PERFORM public.availability_rule_bulk_insert(NEW, day_of_week);
                END LOOP;
            ELSE
                /** Otherwise, just modify the start/end time ranges */
                UPDATE public.availability
                SET
                    available_range = tstzrange(
                        /** start of range */
                        available_date + NEW.start_time,
                        /** end of range */
                        /** check if there is a time wraparound, if so, increment by a day */
                        CASE NEW.end_time <= NEW.start_time
                            WHEN TRUE THEN (available_date + 1) + NEW.end_time
                            ELSE available_date + NEW.end_time
                        END
                    )
                WHERE availability_rule_id = NEW.id;
            END IF;
        END IF;
        RETURN NEW;
    END;
$trigger$
LANGUAGE plpgsql;

/** availability_rule trigger only fires on insert or update as DELETE is cascaded */
CREATE TRIGGER availability_rule_insert_or_update
AFTER INSERT OR UPDATE ON public.availability_rule
FOR EACH ROW
EXECUTE PROCEDURE public.availability_rule_manage();

/** AVAILABILITY, UNAVAILABILITY, and CALENDAR */

/** Helper function: generate the available chunks of time within a block of time for a day within a calendar */
CREATE OR REPLACE FUNCTION public.calendar_generate_available(room_id int, calendar_range tstzrange)
RETURNS TABLE(status text, calendar_range tstzrange)
AS $$
  SELECT *
  FROM
  (
    SELECT
        'closed',
        unnest(tstzmultirange($2) - COALESCE(range_agg(availability.available_range), tstzmultirange())) AS calendar_range
    FROM public.room
    LEFT OUTER JOIN public.availability ON
        availability.room_id = room.id AND
        availability.available_range && $2
    WHERE
      room.id = $1
    GROUP BY room.id
    HAVING room.id IS NOT NULL
    UNION
    SELECT
        'available' AS status,
        availability.available_range AS calendar_range
    FROM public.availability
    WHERE
        availability.room_id = $1 AND
        availability.available_range && $2
  ) c
  ORDER BY lower(c.calendar_range)
$$ LANGUAGE SQL STABLE;

/**
 * Helper function: combine the closed and available chunks of time with the unavailable chunks
 * of time to output the final calendar for the given `calendar_range`
 */
CREATE OR REPLACE FUNCTION public.calendar_generate_calendar(room_id int, calendar_range tstzrange)
RETURNS TABLE (status text, calendar_range tstzrange)
AS $$
    SELECT *
    FROM (
      SELECT
        cal.status, unnest(tstzmultirange(cal.calendar_range) - COALESCE(tstzmultirange(unavailability.unavailable_range), tstzmultirange())) calendar_range
      FROM public.calendar_generate_available($1, $2) cal
      LEFT OUTER JOIN public.unavailability ON
        unavailability.room_id = $1 AND
        unavailability.unavailable_range && $2 AND
        cal.calendar_range && unavailability.unavailable_range
      UNION
      SELECT 'unavailable' AS status, unavailable_range AS calendar_range
      FROM public.unavailability
      WHERE
        unavailability.room_id = $1 AND
        unavailability.unavailable_range && $2
    ) c
    ORDER BY lower(c.calendar_range);
$$ LANGUAGE SQL STABLE;

/**
 * Helper function: substitute the data within the `calendar`; this can be used
 *  for all updates that occur on `availability` and `unavailability`
 */
CREATE OR REPLACE FUNCTION public.calendar_manage(room_id int, calendar_date date)
RETURNS void
AS $$
    WITH delete_calendar AS (
        DELETE FROM public.calendar
        WHERE
            room_id = $1 AND
            calendar_date = $2
    )
    INSERT INTO public.calendar (room_id, status, calendar_date, calendar_range)
    SELECT $1, c.status, $2, c.calendar_range
    FROM public.calendar_generate_calendar($1, tstzrange($2, $2 + 1)) c
$$ LANGUAGE SQL;

/** Now, the trigger functions for availability and unavailability  */
CREATE OR REPLACE FUNCTION public.availability_manage()
RETURNS trigger
AS $trigger$
    BEGIN
        IF TG_OP = 'DELETE' THEN
            PERFORM public.calendar_manage(OLD.room_id, OLD.available_date);
            RETURN OLD;
        END IF;
        PERFORM public.calendar_manage(NEW.room_id, NEW.available_date);
        RETURN NEW;
    END;
$trigger$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.unavailability_manage()
RETURNS trigger
AS $trigger$
    BEGIN
        IF TG_OP = 'DELETE' THEN
            PERFORM public.calendar_manage(OLD.room_id, OLD.unavailable_date);
            RETURN OLD;
        END IF;
        PERFORM public.calendar_manage(NEW.room_id, NEW.unavailable_date);
        RETURN NEW;
    END;
$trigger$
LANGUAGE plpgsql;

/** And the triggers, applied to everything */
CREATE TRIGGER availability_manage
AFTER INSERT OR UPDATE OR DELETE ON public.availability
FOR EACH ROW
EXECUTE PROCEDURE public.availability_manage();

CREATE TRIGGER unavailability_manage
AFTER INSERT OR UPDATE OR DELETE ON public.unavailability
FOR EACH ROW
EXECUTE PROCEDURE public.unavailability_manage();