Hotel reservations

CREATE TYPE reservation_status AS ENUM ('pending', 'confirmed', 'checked_in', 'checked_out', 'cancelled');

CREATE TABLE properties (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL,
    city text NOT NULL,
    country text NOT NULL
);

CREATE TABLE room_types (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    property_id uuid NOT NULL,
    name text NOT NULL,
    capacity int NOT NULL,
    UNIQUE (property_id, name)
);

CREATE TABLE rooms (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    room_type_id uuid NOT NULL,
    number text NOT NULL,
    floor int,
    UNIQUE (room_type_id, number)
);

CREATE TABLE rates (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    room_type_id uuid NOT NULL,
    starts_on date NOT NULL,
    ends_on date NOT NULL,
    nightly_price numeric(10,2) NOT NULL,
    CHECK (ends_on >= starts_on)
);
CREATE INDEX rates_room_type_id_idx ON rates (room_type_id);

CREATE TABLE guests (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    full_name text NOT NULL,
    email text NOT NULL,
    phone text
);

CREATE TABLE reservations (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    guest_id uuid NOT NULL,
    room_type_id uuid NOT NULL,
    room_id uuid,
    check_in date NOT NULL,
    check_out date NOT NULL,
    status reservation_status DEFAULT 'pending' NOT NULL,
    total numeric(10,2),
    CHECK (check_out > check_in)
);
CREATE INDEX reservations_dates_idx ON reservations (room_type_id, check_in, check_out);
CREATE INDEX reservations_guest_id_idx ON reservations (guest_id);
CREATE INDEX reservations_room_id_idx ON reservations (room_id);

CREATE TABLE reservation_extras (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    reservation_id uuid NOT NULL,
    description text NOT NULL,
    amount numeric(10,2) NOT NULL
);
CREATE INDEX reservation_extras_reservation_id_idx ON reservation_extras (reservation_id);

ALTER TABLE room_types ADD FOREIGN KEY (property_id) REFERENCES properties (id) ON DELETE CASCADE;

ALTER TABLE rooms ADD FOREIGN KEY (room_type_id) REFERENCES room_types (id);

ALTER TABLE rates ADD FOREIGN KEY (room_type_id) REFERENCES room_types (id) ON DELETE CASCADE;

ALTER TABLE reservations ADD FOREIGN KEY (guest_id) REFERENCES guests (id);

ALTER TABLE reservations ADD FOREIGN KEY (room_type_id) REFERENCES room_types (id);

ALTER TABLE reservations ADD FOREIGN KEY (room_id) REFERENCES rooms (id);

ALTER TABLE reservation_extras ADD FOREIGN KEY (reservation_id) REFERENCES reservations (id) ON DELETE CASCADE;