CREATE TABLE IF NOT EXISTS hotels(
    id INTEGER PRIMARY KEY, 
    name TEXT NOT NULL, 
    city TEXT NOT NULL, 
    stars INTEGER,
    CONSTRAINT unique_name_by_city UNIQUE (name, city)
);

CREATE TABLE IF NOT EXISTS clients(
    id INTEGER PRIMARY KEY, 
    surname TEXT NOT NULL, 
    first_name VARCHAR(30)
);

CREATE TABLE IF NOT EXISTS rooms(
    id INTEGER NOT NULL,
    hotel_id INTEGER NOT NULL,
    floor_number INTEGER,
    type VARCHAR(15),
    price_per_night_excl_tax REAL NOT NULL,
    CONSTRAINT pk_rooms PRIMARY KEY (id, hotel_id),
    CONSTRAINT fk_hotel_id FOREIGN KEY (hotel_id) REFERENCES hotels(id)
);

CREATE TABLE IF NOT EXISTS occupancies(
    id INTEGER PRIMARY KEY,
    client_id INTEGER NOT NULL,
    room_id INTEGER NOT NULL,
    hotel_id INTEGER NOT NULL,
    date_from DATE NOT NULL,
    date_to DATE, -- NULL if unknown
    CONSTRAINT fk_room_id FOREIGN KEY (room_id, hotel_id)
        REFERENCES rooms(id, hotel_id),
    CONSTRAINT fk_client_id FOREIGN KEY (id)
        REFERENCES clients(id) ON DELETE CASCADE,
    CONSTRAINT fk_hotel_id FOREIGN KEY (hotel_id)
        REFERENCES hotels(id) ON DELETE CASCADE ,
    CONSTRAINT occupancy_date_check CHECK (date_from < date_to)
);

CREATE TABLE IF NOT EXISTS bookings(
    id INTEGER PRIMARY KEY,
    client_id INTEGER NOT NULL,
    room_id INTEGER NOT NULL,
    hotel_id INTEGER NOT NULL,
    date_from DATE NOT NULL,
    date_to DATE NOT NULL,
    CONSTRAINT fk_room_id FOREIGN KEY (room_id, hotel_id)
        REFERENCES rooms(id, hotel_id) ON DELETE CASCADE,
    CONSTRAINT fk_client_id FOREIGN KEY (client_id)
        REFERENCES clients(id) ON DELETE CASCADE,
    CONSTRAINT fk_hotel_id FOREIGN KEY (hotel_id)
        REFERENCES hotels(id) ON DELETE CASCADE,
    CONSTRAINT booking_date_check CHECK (date_from < date_to)
);

