Room booking (no double-booking)

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE people (
    id serial PRIMARY KEY,
    email text NOT NULL UNIQUE,
    name text NOT NULL
);

CREATE TABLE buildings (
    id serial PRIMARY KEY,
    name text NOT NULL,
    address text
);

CREATE TABLE rooms (
    id serial PRIMARY KEY,
    building_id integer NOT NULL,
    name text NOT NULL,
    capacity integer NOT NULL,
    UNIQUE (building_id, name),
    CHECK (capacity > 0)
);

CREATE TABLE amenities (
    id serial PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE room_amenities (
    room_id integer NOT NULL,
    amenity_id integer NOT NULL,
    PRIMARY KEY (room_id, amenity_id)
);

CREATE TABLE bookings (
    id bigserial PRIMARY KEY,
    room_id integer NOT NULL,
    booked_by integer NOT NULL,
    during tstzrange NOT NULL,
    purpose text,
    CONSTRAINT bookings_no_overlap EXCLUDE USING gist (room_id WITH =, during WITH &&)
);
CREATE INDEX bookings_booked_by_idx ON bookings (booked_by);

ALTER TABLE rooms ADD FOREIGN KEY (building_id) REFERENCES buildings (id) ON DELETE CASCADE;

ALTER TABLE room_amenities ADD FOREIGN KEY (room_id) REFERENCES rooms (id) ON DELETE CASCADE;

ALTER TABLE room_amenities ADD FOREIGN KEY (amenity_id) REFERENCES amenities (id) ON DELETE CASCADE;

ALTER TABLE bookings ADD FOREIGN KEY (room_id) REFERENCES rooms (id) ON DELETE CASCADE;

ALTER TABLE bookings ADD FOREIGN KEY (booked_by) REFERENCES people (id);