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