DDL, DML and Views: planora_ddl.sql

drop table if exists booking_amenities cascade;
drop table if exists booking_discounts cascade;
drop table if exists room_amenities cascade;
drop table if exists property_amenities cascade;
drop table if exists favorite_listings cascade;
drop table if exists reviews cascade;
drop table if exists payments cascade;
drop table if exists bookings cascade;
drop table if exists discounts cascade;
drop table if exists availability_blocks cascade;
drop table if exists availability_windows cascade;
drop table if exists images cascade;
drop table if exists cancellation_policies cascade;
drop table if exists notifications cascade;
drop table if exists rooms cascade;
drop table if exists room_types cascade;
drop table if exists properties cascade;
drop table if exists listing_types cascade;
drop table if exists amenities cascade;
drop table if exists payment_methods cascade;
drop table if exists hosts cascade;
drop table if exists guests cascade;
drop table if exists admins cascade;
drop table if exists host_applications cascade;
drop table if exists users cascade;
drop table if exists addresses cascade;
drop table if exists countries cascade;


create table countries (
    country_id      BIGSERIAL PRIMARY KEY,
    country_name    varchar(255) NOT NULL,
    country_code    varchar(10)  NOT NULL,
    CONSTRAINT uq_countries_country_code_ci UNIQUE (country_code)
);

create table addresses (
    address_id      BIGSERIAL PRIMARY KEY,
    country_id      BIGINT NOT NULL REFERENCES countries(country_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    zip_code        varchar(50)  NOT NULL ,
    street          varchar(100) NOT NULL,
    city            varchar(100) NOT NULL
);
create table users (
    user_id         BIGSERIAL PRIMARY KEY ,
    email           varchar(255) ,
    password_hash   varchar(255),
    first_name      varchar(255) ,
    last_name       varchar(255) ,
    phone           varchar(30) ,
    created_at      TIMESTAMP  DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP,
    CONSTRAINT uq_users_email_ci UNIQUE (email)
);

INSERT INTO users (user_id, email, password_hash, first_name, last_name)
VALUES (-1, 'unknown@system.local', 'UNKNOWN_HASH', 'Unknown', 'User');


create table admins (
    admin_id        BIGSERIAL PRIMARY KEY ,
    user_id         BIGINT NOT NULL UNIQUE default -1
        REFERENCES users(user_id)
        ON DELETE SET DEFAULT
        ON UPDATE CASCADE
);

INSERT INTO admins (admin_id, user_id)
VALUES (-1, -1);


drop table if exists guests cascade ;
create table guests (
    guest_id        BIGSERIAL PRIMARY KEY,
    user_id         BIGINT NOT NULL UNIQUE default -1
        REFERENCES users(user_id)
        ON DELETE SET DEFAULT
        ON UPDATE CASCADE
);
INSERT INTO guests (guest_id, user_id)
VALUES (-1, -1);

create table host_applications (
    application_id          BIGSERIAL PRIMARY KEY,
    user_id                 BIGINT NOT NULL UNIQUE REFERENCES users(user_id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    application_date        DATE NOT NULL DEFAULT CURRENT_DATE,
    status                  varchar(50) NOT NULL,
    reviewed_by_admin_id    BIGINT REFERENCES admins(admin_id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    review_date             DATE,
    rejection_reason        text,
    CONSTRAINT ck_host_application_status CHECK (
        status IN ('PENDING', 'APPROVED', 'REJECTED', 'UNDER_REVIEW')
    ),
    CONSTRAINT ck_host_application_rejected_reason CHECK (
        (status = 'REJECTED' AND rejection_reason IS NOT NULL)
        OR status <> 'REJECTED'
    ),
    CONSTRAINT ck_host_application_reviewed CHECK (
        (status IN ('APPROVED', 'REJECTED') AND reviewed_by_admin_id IS NOT NULL AND review_date IS NOT NULL)
        OR status NOT IN ('APPROVED', 'REJECTED')
    )
);

create table hosts (
    user_id         BIGINT PRIMARY KEY REFERENCES users(user_id) ON DELETE set default ON UPDATE CASCADE,
    application_id  BIGINT UNIQUE REFERENCES host_applications(application_id) ON DELETE SET DEFAULT ON UPDATE CASCADE
);

create table listing_types (
    listing_type_id BIGSERIAL PRIMARY KEY,
    type_name       varchar(255) NOT NULL UNIQUE,
    description     text
);

create table payment_methods (
    payment_method_id BIGSERIAL PRIMARY KEY,
    method_name       varchar(255) NOT NULL UNIQUE,
    description       text
);

create table amenities (
    amenity_id       BIGSERIAL PRIMARY KEY,
    amenity_name     varchar(100) NOT NULL,
    amenity_type     varchar(255) NOT NULL,
    description      text,
    is_included      BOOLEAN NOT NULL DEFAULT FALSE,
    price            NUMERIC(10,2),
    CONSTRAINT ck_amenity_price_nonnegative CHECK (price IS NULL OR price >= 0)
);

create table properties (
    property_id      BIGSERIAL PRIMARY KEY,
    host_id          BIGINT NOT NULL REFERENCES hosts(user_id) ON DELETE set default ON UPDATE CASCADE,
    listing_type_id  BIGINT NOT NULL REFERENCES listing_types(listing_type_id) ON DELETE restrict ON UPDATE CASCADE,
    address_id       BIGINT NOT NULL REFERENCES addresses(address_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    title            varchar(255) NOT NULL,
    description      text,
    base_price       NUMERIC(10,2) NOT NULL,
    max_guests       INTEGER NOT NULL,
    status           varchar(30) NOT NULL,
    created_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       TIMESTAMP,
    CONSTRAINT ck_property_price_nonnegative CHECK (base_price >= 0),
    CONSTRAINT ck_property_max_guests_positive CHECK (max_guests > 0),
    CONSTRAINT ck_property_status CHECK (
        status IN ('DRAFT', 'ACTIVE', 'INACTIVE', 'ARCHIVED')
    )
);

create table room_types (
    room_type_id     BIGSERIAL PRIMARY KEY,
    type_name        varchar(100) NOT NULL UNIQUE,
    description      text
);

create table rooms (
    room_id              BIGSERIAL PRIMARY KEY,
    property_id          BIGINT NOT NULL REFERENCES properties(property_id) ON DELETE CASCADE ON UPDATE CASCADE,
    room_type_id         BIGINT NOT NULL REFERENCES room_types(room_type_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    room_name            varchar(100) NOT NULL,
    capacity             INTEGER NOT NULL,
    price_per_night      NUMERIC(10,2) NOT NULL,
    description          text,
    status               varchar(50) NOT NULL,
    extra_capacity       INTEGER NOT NULL DEFAULT 0,
    extra_guest_price    NUMERIC(10,2),
    CONSTRAINT ck_room_capacity_positive CHECK (capacity > 0),
    CONSTRAINT ck_room_extra_capacity_nonnegative CHECK (extra_capacity >= 0),
    CONSTRAINT ck_room_price_nonnegative CHECK (price_per_night >= 0),
    CONSTRAINT ck_room_extra_guest_price_nonnegative CHECK (
        extra_guest_price IS NULL OR extra_guest_price >= 0
    ),
    CONSTRAINT ck_room_status CHECK (
        status IN ('ACTIVE', 'INACTIVE', 'MAINTENANCE', 'UNAVAILABLE')
    ),
    CONSTRAINT uq_room_name_per_property UNIQUE (property_id, room_name)
);

create table availability_blocks (
    availability_id      BIGSERIAL PRIMARY KEY,
    room_id              BIGINT NOT NULL REFERENCES rooms(room_id) ON DELETE CASCADE ON UPDATE CASCADE,
    blocked_from_date    DATE NOT NULL,
    blocked_to_date      DATE NOT NULL,
    blocked_from_time    TIMESTAMP NOT NULL,
    blocked_to_time      TIMESTAMP NOT NULL,
    description          varchar(255),
    CONSTRAINT ck_availability_date_range CHECK (blocked_to_date >= blocked_from_date),
    CONSTRAINT ck_availability_time_range CHECK (blocked_to_time > blocked_from_time),
    CONSTRAINT ck_availability_from_time_date CHECK (blocked_from_time::date = blocked_from_date),
    CONSTRAINT ck_availability_to_time_date CHECK (blocked_to_time::date = blocked_to_date)
);
drop table if exists discounts cascade ;
create table discounts (
    discount_id          BIGSERIAL PRIMARY KEY,
    host_id              BIGINT NOT NULL REFERENCES hosts(user_id) ON DELETE set default ON UPDATE CASCADE,
    code                 varchar(255) NOT NULL,
    title                varchar(255) NOT NULL,
    description          text,
    discount_type        varchar(30) NOT NULL,
    discount_value       NUMERIC(10,2) NOT NULL,
    valid_from           DATE NOT NULL,
    valid_to             DATE NOT NULL,
    is_active            BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT uq_discounts_code_ci UNIQUE (code),
    CONSTRAINT ck_discount_type CHECK (discount_type IN ('PERCENTAGE', 'FIXED')),
    CONSTRAINT ck_discount_value_nonnegative CHECK (discount_value >= 0),
    CONSTRAINT ck_discount_percentage_cap CHECK (
        discount_type <> 'PERCENTAGE'
        OR (discount_value >= 0 AND discount_value <= 100)
    ),
    CONSTRAINT ck_discount_fixed_positive CHECK (
        discount_type <> 'FIXED' OR discount_value > 0
    ),
    CONSTRAINT ck_discount_valid_range CHECK (valid_to >= valid_from)
);

create table bookings (
    booking_id           BIGSERIAL PRIMARY KEY,
    guest_id             BIGINT NOT NULL REFERENCES guests(guest_id) ON DELETE set default ON UPDATE CASCADE,
    room_id              BIGINT NOT NULL REFERENCES rooms(room_id) ON DELETE restrict ON UPDATE CASCADE,
    check_in_date        DATE NOT NULL,
    check_out_date       DATE NOT NULL,
    guests_count         INTEGER NOT NULL,
    total_price          NUMERIC(10,2) NOT NULL,
    booking_status       varchar(30) NOT NULL,
    booked_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT ck_booking_date_range CHECK (check_out_date > check_in_date),
    CONSTRAINT ck_booking_guest_count_positive CHECK (guests_count > 0),
    CONSTRAINT ck_booking_total_price_nonnegative CHECK (total_price >= 0),
    CONSTRAINT ck_booking_status CHECK (
        booking_status IN ('PENDING', 'CONFIRMED', 'CANCELLED', 'COMPLETED', 'NO_SHOW')
    )
);

create table payments (
    payment_id           BIGSERIAL PRIMARY KEY,
    booking_id           BIGINT NOT NULL UNIQUE REFERENCES bookings(booking_id) ON DELETE restrict ON UPDATE CASCADE,
    payment_method_id    BIGINT NOT NULL REFERENCES payment_methods(payment_method_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    amount               NUMERIC(10,2) NOT NULL,
    payment_status       varchar(30) NOT NULL,
    paid_at              TIMESTAMP,
    CONSTRAINT ck_payment_amount_nonnegative CHECK (amount >= 0),
    CONSTRAINT ck_payment_status CHECK (
        payment_status IN ('PENDING', 'PAID', 'FAILED', 'REFUNDED', 'PARTIALLY_REFUNDED')
    ),
    CONSTRAINT ck_payment_paid_has_timestamp CHECK (
        (payment_status = 'PAID' AND paid_at IS NOT NULL)
        OR payment_status <> 'PAID'
    )
);
create table reviews (
    review_id            BIGSERIAL PRIMARY KEY,
    booking_id           BIGINT NOT NULL UNIQUE REFERENCES bookings(booking_id) ON DELETE CASCADE ON UPDATE CASCADE,
    guest_id             BIGINT NOT NULL REFERENCES guests(guest_id) ON DELETE set default ON UPDATE CASCADE,
    property_id          BIGINT NOT NULL REFERENCES properties(property_id) ON DELETE CASCADE ON UPDATE CASCADE,
    rating               INTEGER NOT NULL,
    comment              text,
    created_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT ck_review_rating CHECK (rating BETWEEN 1 AND 5)
);

create table favorite_listings (
    favorite_id          BIGSERIAL PRIMARY KEY,
    user_id              BIGINT NOT NULL REFERENCES users(user_id) ON DELETE cascade ON UPDATE CASCADE,
    property_id          BIGINT NOT NULL REFERENCES properties(property_id) ON DELETE cascade ON UPDATE CASCADE,
    created_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_favorite_listings_user_property UNIQUE (user_id, property_id)
);

create table notifications (
    notification_id      BIGSERIAL PRIMARY KEY,
    user_id              BIGINT NOT NULL REFERENCES users(user_id) ON DELETE set default ON UPDATE CASCADE,
    message              TEXT NOT NULL,
    sent_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_read              BOOLEAN NOT NULL DEFAULT FALSE
);

create table cancellation_policies (
    policy_id                BIGSERIAL PRIMARY KEY,
    property_id              BIGINT NOT NULL UNIQUE REFERENCES properties(property_id) ON DELETE CASCADE ON UPDATE CASCADE,
    policy_name              varchar(100) NOT NULL,
    description              TEXT,
    refund_percentage        INTEGER,
    days_before_checking      INTEGER,
    CONSTRAINT ck_policy_refund_percentage CHECK (
        refund_percentage IS NULL OR refund_percentage BETWEEN 0 AND 100
    ),
    CONSTRAINT ck_policy_days_nonnegative CHECK (
        days_before_checking IS NULL OR days_before_checking >= 0
    )
);

create table property_amenities (
    property_id          BIGINT NOT NULL REFERENCES properties(property_id) ON DELETE CASCADE ON UPDATE CASCADE,
    amenity_id           BIGINT NOT NULL REFERENCES amenities(amenity_id) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (property_id, amenity_id)
);

create table room_amenities (
    room_id              BIGINT NOT NULL REFERENCES rooms(room_id) ON DELETE CASCADE ON UPDATE CASCADE,
    amenity_id           BIGINT NOT NULL REFERENCES amenities(amenity_id) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (room_id, amenity_id)
);


create table booking_discounts (
    booking_id           BIGINT NOT NULL REFERENCES bookings(booking_id) ON DELETE CASCADE ON UPDATE CASCADE,
    discount_id          BIGINT NOT NULL REFERENCES discounts(discount_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (booking_id, discount_id)
);

create table booking_amenities (
    booking_amenity_id   BIGSERIAL PRIMARY KEY,
    booking_id           BIGINT NOT NULL REFERENCES bookings(booking_id) ON DELETE CASCADE ON UPDATE CASCADE,
    amenity_id           BIGINT NOT NULL REFERENCES amenities(amenity_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    quantity             INTEGER NOT NULL DEFAULT 1,
    CONSTRAINT uq_booking_amenities_booking_amenity UNIQUE (booking_id, amenity_id),
    CONSTRAINT ck_booking_amenity_quantity_positive CHECK (quantity > 0)
);

create table images (
    image_id             BIGSERIAL PRIMARY KEY,
    entity_type          varchar(30) NOT NULL,
    entity_id            BIGINT NOT NULL,
    property_id          BIGINT REFERENCES properties(property_id) ON DELETE CASCADE ON UPDATE CASCADE,
    room_id              BIGINT REFERENCES rooms(room_id) ON DELETE CASCADE ON UPDATE CASCADE,
    url                  varchar(255) NOT NULL,
    alt_text             varchar(255),
    is_cover             BOOLEAN NOT NULL DEFAULT FALSE,
    sort_order           INTEGER,
    uploaded_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT ck_image_entity_type CHECK (entity_type IN ('PROPERTY', 'ROOM')),
    CONSTRAINT ck_image_sort_order_nonnegative CHECK (sort_order IS NULL OR sort_order >= 0),
    CONSTRAINT ck_image_exactly_one_owner CHECK (
        (property_id IS NOT NULL AND room_id IS NULL AND entity_type = 'PROPERTY')
        OR (property_id IS NULL AND room_id IS NOT NULL AND entity_type = 'ROOM')
    ),
    CONSTRAINT ck_image_entity_id_matches_owner CHECK (
        (entity_type = 'PROPERTY' AND entity_id = property_id AND room_id IS NULL)
        OR (entity_type = 'ROOM' AND entity_id = room_id AND property_id IS NULL)
    )
);

drop table if exists availability_windows cascade ;
CREATE TABLE availability_windows (
    availability_window_id BIGSERIAL PRIMARY KEY,
    room_id                BIGINT NOT NULL REFERENCES rooms(room_id)
                           ON DELETE CASCADE ON UPDATE CASCADE,
    available_date    DATE NOT NULL,
    status text not null
);
COMMIT;