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)

);

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));

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));

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));

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));

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) );

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));

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));

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));

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));

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));

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));

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));

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 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));

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));

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));

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));

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,}$') );}

ALTER TABLE UserRole ADD CONSTRAINT FKUserRole510371 FOREIGN KEY (user_id) REFERENCES "User" (user_id); ALTER TABLE UserRole ADD CONSTRAINT FKUserRole537901 FOREIGN KEY (role_id) REFERENCES Role (role_id); ALTER TABLE Host ADD CONSTRAINT FKHost364816 FOREIGN KEY (user_id) REFERENCES "User" (user_id); ALTER TABLE Guest ADD CONSTRAINT FKGuest463773 FOREIGN KEY (user_id) REFERENCES "User" (user_id); ALTER TABLE City ADD CONSTRAINT FKCity681686 FOREIGN KEY (country_id) REFERENCES Country (country_id); ALTER TABLE Address ADD CONSTRAINT FKAddress542818 FOREIGN KEY (country_id) REFERENCES Country (country_id); ALTER TABLE Address ADD CONSTRAINT FKAddress943654 FOREIGN KEY (city_id) REFERENCES City (city_id); ALTER TABLE Property ADD CONSTRAINT FKProperty146200 FOREIGN KEY (host_id) REFERENCES Host (host_id); ALTER TABLE Property ADD CONSTRAINT FKProperty26489 FOREIGN KEY (property_type_id) REFERENCES PropertyType (property_type_id); ALTER TABLE Property ADD CONSTRAINT FKProperty394643 FOREIGN KEY (address_id) REFERENCES Address (address_id); ALTER TABLE PropertyImage ADD CONSTRAINT FKPropertyIm748462 FOREIGN KEY (property_id) REFERENCES Property (property_id); ALTER TABLE Room ADD CONSTRAINT FKRoom250935 FOREIGN KEY (property_id) REFERENCES Property (property_id); ALTER TABLE Availability ADD CONSTRAINT FKAvailabili408544 FOREIGN KEY (property_id) REFERENCES Property (property_id); ALTER TABLE Availability ADD CONSTRAINT FKAvailabili235433 FOREIGN KEY (room_id) REFERENCES Room (room_id); ALTER TABLE Booking ADD CONSTRAINT FKBooking733456 FOREIGN KEY (guest_id) REFERENCES Guest (guest_id); ALTER TABLE Booking ADD CONSTRAINT FKBooking932562 FOREIGN KEY (property_id) REFERENCES Property (property_id); ALTER TABLE Booking ADD CONSTRAINT FKBooking711414 FOREIGN KEY (room_id) REFERENCES Room (room_id); ALTER TABLE BookingGuest ADD CONSTRAINT FKBookingGue951829 FOREIGN KEY (booking_id) REFERENCES Booking (booking_id); ALTER TABLE Payment ADD CONSTRAINT FKPayment809900 FOREIGN KEY (booking_id) REFERENCES Booking (booking_id); ALTER TABLE Review ADD CONSTRAINT FKReview586694 FOREIGN KEY (guest_id) REFERENCES Guest (guest_id); ALTER TABLE Review ADD CONSTRAINT FKReview785800 FOREIGN KEY (property_id) REFERENCES Property (property_id); ALTER TABLE Review ADD CONSTRAINT FKReview705219 FOREIGN KEY (booking_id) REFERENCES Booking (booking_id); ALTER TABLE Favorite ADD CONSTRAINT FKFavorite639091 FOREIGN KEY (guest_id) REFERENCES Guest (guest_id); ALTER TABLE Favorite ADD CONSTRAINT FKFavorite838197 FOREIGN KEY (property_id) REFERENCES Property (property_id); ALTER TABLE Message ADD CONSTRAINT FKMessage71564 FOREIGN KEY (sender_user_id) REFERENCES "User" (user_id); ALTER TABLE Message ADD CONSTRAINT FKMessage798241 FOREIGN KEY (booking_id) REFERENCES Booking (booking_id); ALTER TABLE Discount ADD CONSTRAINT FKDiscount526380 FOREIGN KEY (property_id) REFERENCES Property (property_id); ALTER TABLE PropertyAmenity ADD CONSTRAINT FKPropertyAm351867 FOREIGN KEY (property_id) REFERENCES Property (property_id); ALTER TABLE Payment ADD CONSTRAINT FKPayment187362 FOREIGN KEY (payment_method_id) REFERENCES PaymentMethod (payment_method_id); ALTER TABLE BookingGuest ADD CONSTRAINT FKBookingGue340084 FOREIGN KEY (guest_id) REFERENCES Guest (guest_id); ALTER TABLE PropertyAmenity ADD CONSTRAINT FKPropertyAm118382 FOREIGN KEY (amenity_id) REFERENCES Amenity (amenity_id); ALTER TABLE Discount ADD CONSTRAINT FKDiscount498768 FOREIGN KEY (host_id) REFERENCES Host (host_id); ALTER TABLE Message ADD CONSTRAINT FKMessage715611 FOREIGN KEY (reciever_user_id) REFERENCES "User" (user_id); ALTER TABLE Message ADD CONSTRAINT FKMessage307221 FOREIGN KEY (property_id) REFERENCES Property (property_id);