demo5.sql

/* \timing */
/** SELECT * FROM pg_create_logical_replication_slot('schedule', 'wal2json'); */

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

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

CREATE TABLE availability (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    room_id int REFERENCES room (id) ON DELETE CASCADE,
    availability_rule_id int REFERENCES availability_rule (id) ON DELETE CASCADE,
    available_date date,
    available_range tstzrange
);
ALTER TABLE availability REPLICA IDENTITY FULL;

CREATE INDEX availability_available_range_gist_idx
    ON availability
    USING gist(available_range);

CREATE TABLE unavailability (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    room_id int REFERENCES room (id) ON DELETE CASCADE,
    unavailable_date date,
    unavailable_range tstzrange
);
ALTER TABLE unavailability REPLICA IDENTITY FULL;

CREATE INDEX unavailability_unavailable_range_gist_idx
    ON unavailability
    USING gist(unavailable_range);

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

/**
 * Helper functions and Triggers
 */

/**
 * 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 availability_rule_bulk_insert(availability_rule availability_rule, day_of_week int)
RETURNS void
AS $$
    INSERT INTO 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
 */


/** 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 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 room
    LEFT OUTER JOIN 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 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 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 calendar_generate_available($1, $2) cal
      LEFT OUTER JOIN 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 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 calendar_manage(room_id int, calendar_date date)
RETURNS void
AS $$
    WITH delete_calendar AS (
        DELETE FROM calendar
        WHERE
            room_id = $1 AND
            calendar_date = $2
    )
    INSERT INTO calendar (room_id, status, calendar_date, calendar_range)
    SELECT $1, c.status, $2, c.calendar_range
    FROM calendar_generate_calendar($1, tstzrange($2, $2 + 1)) c
$$ LANGUAGE SQL;