CREATE TABLE "User" (

user_id int8 NOT NULL, first_name varchar(100) NOT NULL, last_name varchar(100) NOT NULL, email varchar(255) NOT NULL UNIQUE, password_hash text NOT NULL, phone varchar(30), date_of_birth date, profile_image varchar(500), created_at timestamp NOT NULL, status varchar(30) NOT NULL, PRIMARY KEY (user_id), CONSTRAINT chk_user_email_format

CHECK (email LIKE '^{[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')}

); CREATE TABLE Role (

role_id int8 NOT NULL, role_name varchar(50) NOT NULL UNIQUE, PRIMARY KEY (role_id)

); CREATE TABLE UserRole (

user_id int8 NOT NULL, role_id int8 NOT NULL, assigned_at timestamp NOT NULL, PRIMARY KEY (user_id, role_id), CONSTRAINT FKUserRole510371 FOREIGN KEY (user_id) REFERENCES "User" (user_id), CONSTRAINT FKUserRole537901 FOREIGN KEY (role_id) REFERENCES Role (role_id)

); CREATE TABLE Host (

host_id int8 NOT NULL, user_id int8 NOT NULL UNIQUE, host_description text, host_rating numeric(3, 2), is_verified bool NOT NULL, joined_at timestamp NOT NULL, PRIMARY KEY (host_id), CONSTRAINT FKHost364816 FOREIGN KEY (user_id) REFERENCES "User" (user_id)

); CREATE TABLE Guest (

guest_id int8 NOT NULL, user_id int8 NOT NULL UNIQUE, preferences text, loyalty_points int4 DEFAULT 0 NOT NULL, created_at timestamp NOT NULL, PRIMARY KEY (guest_id), CONSTRAINT FKGuest463773 FOREIGN KEY (user_id) REFERENCES "User" (user_id)

); CREATE TABLE Country (

country_id int8 NOT NULL, country_name varchar(100) NOT NULL UNIQUE, country_code varchar(3) NOT NULL UNIQUE, PRIMARY KEY (country_id)

); CREATE TABLE City (

city_id int8 NOT NULL, country_id int8 NOT NULL, city_name varchar(100) NOT NULL, postal_code varchar(20) NOT NULL UNIQUE, PRIMARY KEY (city_id), CONSTRAINT FKCity681686 FOREIGN KEY (country_id) REFERENCES Country (country_id)

); CREATE TABLE Address (

address_id int8 NOT NULL, country_id int8 NOT NULL, city_id int8 NOT NULL, street varchar(150) NOT NULL, street_number varchar(20), latitude numeric(10, 6), longitude numeric(10, 6), PRIMARY KEY (address_id), CONSTRAINT FKAddress542818 FOREIGN KEY (country_id) REFERENCES Country (country_id), CONSTRAINT FKAddress943654 FOREIGN KEY (city_id) REFERENCES City (city_id)

); CREATE TABLE PropertyType (

property_type_id int8 NOT NULL, type_name varchar(50) NOT NULL UNIQUE, PRIMARY KEY (property_type_id)

); CREATE TABLE Property (

property_id int8 NOT NULL, host_id int8 NOT NULL, property_type_id int8 NOT NULL, address_id int8 NOT NULL, title varchar(150) NOT NULL, description text, max_guests int4 NOT NULL, bedroom_count int4, bathroom_count int4 NOT NULL, base_price numeric(10, 2) NOT NULL, check_in_time time, check_out_time time, status varchar(30) NOT NULL, created_at timestamp NOT NULL, PRIMARY KEY (property_id), CONSTRAINT FKProperty146200 FOREIGN KEY (host_id) REFERENCES Host (host_id), CONSTRAINT FKProperty26489 FOREIGN KEY (property_type_id) REFERENCES PropertyType (property_type_id), CONSTRAINT FKProperty394643 FOREIGN KEY (address_id) REFERENCES Address (address_id)

); CREATE TABLE Amenity (

amenity_id int8 NOT NULL, amenity_name varchar(100) NOT NULL UNIQUE, description text, PRIMARY KEY (amenity_id)

); CREATE TABLE PropertyAmenity (

property_id int8 NOT NULL, amenity_id int8 NOT NULL, PRIMARY KEY (property_id, amenity_id), CONSTRAINT FKPropertyAm351867 FOREIGN KEY (property_id) REFERENCES Property (property_id), CONSTRAINT FKPropertyAm118382 FOREIGN KEY (amenity_id) REFERENCES Amenity (amenity_id)

); CREATE TABLE PropertyImage (

image_id int8 NOT NULL, property_id int8 NOT NULL, image_url varchar(500) NOT NULL, caption varchar(255), is_primary bool NOT NULL, uploaded_at timestamp NOT NULL, PRIMARY KEY (image_id), CONSTRAINT FKPropertyIm748462 FOREIGN KEY (property_id) REFERENCES Property (property_id)

); CREATE TABLE Room (

room_id int8, property_id int8, room_name varchar(100), room_type varchar(50), capacity int4 NOT NULL, price_per_night numeric(10, 2) NOT NULL, status varchar(30) NOT NULL, PRIMARY KEY (room_id), CONSTRAINT FKRoom250935 FOREIGN KEY (property_id) REFERENCES Property (property_id)

); CREATE TABLE Availability (

availability_id int8 NOT NULL, property_id int8 NOT NULL, room_id int8 NOT NULL, available_date date NOT NULL, is_available bool NOT NULL, special_price numeric(10, 2), PRIMARY KEY (availability_id), CONSTRAINT FKAvailabili408544 FOREIGN KEY (property_id) REFERENCES Property (property_id), CONSTRAINT FKAvailabili235433 FOREIGN KEY (room_id) REFERENCES Room (room_id)

); CREATE TABLE Booking (

booking_id int8 NOT NULL, guest_id int8 NOT NULL, property_id int8 NOT NULL, room_id int8 NOT NULL, check_in_date date NOT NULL, check_out_date date NOT NULL, guest_count int4 NOT NULL, total_price numeric(10, 2) NOT NULL, booking_status varchar(30) NOT NULL, created_at timestamp NOT NULL, PRIMARY KEY (booking_id), CONSTRAINT chk_booking_dates CHECK (check_out_date > check_in_date), CONSTRAINT FKBooking733456 FOREIGN KEY (guest_id) REFERENCES Guest (guest_id), CONSTRAINT FKBooking932562 FOREIGN KEY (property_id) REFERENCES Property (property_id), CONSTRAINT FKBooking711414 FOREIGN KEY (room_id) REFERENCES Room (room_id)

); CREATE TABLE BookingGuest (

booking_guest_id int8 NOT NULL, booking_id int8 NOT NULL, guest_id int8 NOT NULL, full_name varchar(100) NOT NULL, age int4 NOT NULL, document_number varchar(50), is_primary_guest bool NOT NULL, PRIMARY KEY (booking_guest_id), CONSTRAINT FKBookingGue951829 FOREIGN KEY (booking_id) REFERENCES Booking (booking_id), CONSTRAINT FKBookingGue340084 FOREIGN KEY (guest_id) REFERENCES Guest (guest_id)

); CREATE TABLE PaymentMethod (

payment_method_id int8 NOT NULL, method_name varchar(50) NOT NULL UNIQUE, pay_online bool NOT NULL, PRIMARY KEY (payment_method_id)

); CREATE TABLE Payment (

payment_id int8 NOT NULL, booking_id int8 NOT NULL, amount numeric(10, 2) NOT NULL, payment_date timestamp NOT NULL, payment_method_id int8 NOT NULL, payment_status varchar(30) NOT NULL, transaction_reference varchar(100), PRIMARY KEY (payment_id), CONSTRAINT FKPayment809900 FOREIGN KEY (booking_id) REFERENCES Booking (booking_id), CONSTRAINT FKPayment187362 FOREIGN KEY (payment_method_id) REFERENCES PaymentMethod (payment_method_id)

); CREATE TABLE Review (

review_id int8 NOT NULL, booking_id int8 NOT NULL UNIQUE, guest_id int8 NOT NULL, property_id int8 NOT NULL, rating int4 NOT NULL, comment text, review_date timestamp NOT NULL, PRIMARY KEY (review_id), CONSTRAINT chk_rating_range CHECK (rating >= 1 AND rating <= 5), CONSTRAINT FKReview586694 FOREIGN KEY (guest_id) REFERENCES Guest (guest_id), CONSTRAINT FKReview785800 FOREIGN KEY (property_id) REFERENCES Property (property_id), CONSTRAINT FKReview705219 FOREIGN KEY (booking_id) REFERENCES Booking (booking_id)

); CREATE TABLE Favorite (

favorite_id int8 NOT NULL, guest_id int8 NOT NULL, property_id int8 NOT NULL, created_at timestamp NOT NULL, PRIMARY KEY (favorite_id), CONSTRAINT unique_const UNIQUE (guest_id, property_id), CONSTRAINT FKFavorite639091 FOREIGN KEY (guest_id) REFERENCES Guest (guest_id), CONSTRAINT FKFavorite838197 FOREIGN KEY (property_id) REFERENCES Property (property_id)

); CREATE TABLE Message (

message_id int8 NOT NULL, sender_user_id int8 NOT NULL, reciever_user_id int8 NOT NULL, booking_id int8 NOT NULL, property_id int8 NOT NULL, message_text text NOT NULL, sent_at timestamp NOT NULL, is_read bool NOT NULL, PRIMARY KEY (message_id), CONSTRAINT FKMessage71564 FOREIGN KEY (sender_user_id) REFERENCES "User" (user_id), CONSTRAINT FKMessage798241 FOREIGN KEY (booking_id) REFERENCES Booking (booking_id), CONSTRAINT FKMessage715611 FOREIGN KEY (reciever_user_id) REFERENCES "User" (user_id), CONSTRAINT FKMessage307221 FOREIGN KEY (property_id) REFERENCES Property (property_id)

); CREATE TABLE Discount (

discount_id int8 NOT NULL, property_id int8 NOT NULL, host_id int8 NOT NULL, discount_type varchar(30) NOT NULL, discount_value numeric(10, 2) NOT NULL, start_date date NOT NULL, end_date date NOT NULL, is_active bool NOT NULL, PRIMARY KEY (discount_id), CONSTRAINT chk_discount_dates CHECK (end_date >= start_date), CONSTRAINT FKDiscount526380 FOREIGN KEY (property_id) REFERENCES Property (property_id), CONSTRAINT FKDiscount498768 FOREIGN KEY (host_id) REFERENCES Host (host_id)

);