CREATE TABLE countries (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  code TEXT UNIQUE,
  currency TEXT NOT NULL,
  language TEXT NOT NULL
);

CREATE TABLE regions (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  country_id INT NOT NULL,
  FOREIGN KEY (country_id) REFERENCES countries(id)
);

CREATE TABLE cities (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  population INT CHECK (population >= 0),
  region_id INT NOT NULL,
  FOREIGN KEY (region_id) REFERENCES regions(id)
);

CREATE TABLE coordinates (
  id BIGSERIAL PRIMARY KEY,
  latitude DECIMAL(9,6) NOT NULL,
  longitude DECIMAL(9,6) NOT NULL
);

CREATE TABLE locations (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  location_type VARCHAR(100) NOT NULL,
  city_id INT NOT NULL,
  coordinate_id INT,
  FOREIGN KEY (city_id) REFERENCES cities(id) ,
  FOREIGN KEY (coordinate_id) REFERENCES coordinates(id) ON DELETE SET NULL
);

CREATE TABLE airports (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  code TEXT UNIQUE,
  location_id INT NOT NULL,
  FOREIGN KEY (location_id) REFERENCES locations(id)
);

CREATE TABLE transport_stations (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  station_type TEXT NOT NULL,
  location_id INT NOT NULL,
  FOREIGN KEY (location_id) REFERENCES locations(id)
);

CREATE TABLE user_roles (
  id BIGSERIAL PRIMARY KEY,
  role_name VARCHAR(100) UNIQUE NOT NULL,
  description TEXT
);

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  username TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  name TEXT NOT NULL,
  discount_percentage DECIMAL(5,2) DEFAULT 0 CHECK (discount_percentage BETWEEN 0 AND 100)
);

CREATE TABLE user_role_map (
  id BIGSERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  role_id INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ,
  FOREIGN KEY (role_id) REFERENCES user_roles(id)
);

CREATE TABLE trips (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT
);

CREATE TABLE trip_executions (
  id BIGSERIAL PRIMARY KEY,
  trip_id INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  CHECK (end_date >= start_date),
  FOREIGN KEY (trip_id) REFERENCES trips(id)
);

CREATE TABLE trip_participants (
  id BIGSERIAL PRIMARY KEY,
  trip_execution_id INT NOT NULL,
  user_id INT NOT NULL,
  passport_number TEXT,
  status TEXT DEFAULT 'pending',
  FOREIGN KEY (trip_execution_id) REFERENCES trip_executions(id) ,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE facultatives (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  location_id INT NOT NULL,
  trip_participant_id INT NOT NULL,
  FOREIGN KEY (location_id) REFERENCES locations(id) ,
  FOREIGN KEY (trip_participant_id) REFERENCES trip_participants(id)
);

CREATE TABLE attractions (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  entry_fee DECIMAL(6,2) DEFAULT 0 CHECK (entry_fee >= 0),
  facultative_id INT NOT NULL,
  FOREIGN KEY (facultative_id) REFERENCES facultatives(id)
);

CREATE TABLE attraction_types (
  id BIGSERIAL PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  description TEXT
);

CREATE TABLE attraction_type_map (
  id BIGSERIAL PRIMARY KEY,
  attraction_id INT NOT NULL,
  type_id INT NOT NULL,
  FOREIGN KEY (attraction_id) REFERENCES attractions(id) ,
  FOREIGN KEY (type_id) REFERENCES attraction_types(id)
);

CREATE TABLE hotels (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  star_rating INT CHECK (star_rating BETWEEN 1 AND 5),
  location_id INT NOT NULL,
  FOREIGN KEY (location_id) REFERENCES locations(id)
);

CREATE TABLE hotel_rooms (
  id BIGSERIAL PRIMARY KEY,
  room_type TEXT NOT NULL,
  capacity INT CHECK (capacity > 0),
  price DECIMAL(6,2) CHECK (price >= 0),
  hotel_id INT NOT NULL,
  FOREIGN KEY (hotel_id) REFERENCES hotels(id)
);

CREATE TABLE hotel_trip_choices (
  id BIGSERIAL PRIMARY KEY,
  hotel_id INT NOT NULL,
  trip_id INT NOT NULL,
  FOREIGN KEY (hotel_id) REFERENCES hotels(id) ,
  FOREIGN KEY (trip_id) REFERENCES trips(id)
);

CREATE TABLE trip_participants_hotel_rooms (
  id BIGSERIAL PRIMARY KEY,
  trip_participant_id INT NOT NULL,
  hotel_room_id INT NOT NULL,
  check_in DATE,
  check_out DATE,
  CHECK (check_out >= check_in),
  FOREIGN KEY (trip_participant_id) REFERENCES trip_participants(id) ,
  FOREIGN KEY (hotel_room_id) REFERENCES hotel_rooms(id)
);

CREATE TABLE trip_locations (
  id BIGSERIAL PRIMARY KEY,
  trip_id INT NOT NULL,
  location_id INT NOT NULL,
  visit_order INT CHECK (visit_order > 0),
  day_number INT CHECK (day_number > 0),
  FOREIGN KEY (trip_id) REFERENCES trips(id) ,
  FOREIGN KEY (location_id) REFERENCES locations(id)
);

CREATE TABLE flight_routes (
  id BIGSERIAL PRIMARY KEY,
  from_airport_id INT NOT NULL,
  to_airport_id INT NOT NULL,
  CHECK (from_airport_id <> to_airport_id),
  FOREIGN KEY (from_airport_id) REFERENCES airports(id) ,
  FOREIGN KEY (to_airport_id) REFERENCES airports(id)
);

CREATE TABLE flight_bookings (
  id BIGSERIAL PRIMARY KEY,
  trip_execution_id INT NOT NULL,
  trip_participant_id INT NOT NULL,
  flight_route_id INT NOT NULL,
  flight_number TEXT NOT NULL,
  price DECIMAL(8,2) CHECK (price >= 0),
  departure_datetime TIMESTAMP,
  arrival_datetime TIMESTAMP,
  FOREIGN KEY (trip_execution_id) REFERENCES trip_executions(id) ,
  FOREIGN KEY (trip_participant_id) REFERENCES trip_participants(id) ,
  FOREIGN KEY (flight_route_id) REFERENCES flight_routes(id)
);

CREATE TABLE transport_routes (
  id BIGSERIAL PRIMARY KEY,
  distance DECIMAL(6,2) CHECK (distance >= 0),
  estimated_time INT CHECK (estimated_time > 0),
  from_station_id INT NOT NULL,
  to_station_id INT NOT NULL,
  FOREIGN KEY (from_station_id) REFERENCES transport_stations(id) ,
  FOREIGN KEY (to_station_id) REFERENCES transport_stations(id)
);

CREATE TABLE bus_bookings (
  id BIGSERIAL PRIMARY KEY,
  trip_execution_id INT NOT NULL,
  trip_participant_id INT NOT NULL,
  route_id INT NOT NULL,
  bus_number TEXT,
  price DECIMAL(8,2) CHECK (price >= 0),
  FOREIGN KEY (trip_execution_id) REFERENCES trip_executions(id) ,
  FOREIGN KEY (trip_participant_id) REFERENCES trip_participants(id) ,
  FOREIGN KEY (route_id) REFERENCES transport_routes(id)
);

CREATE TABLE reviews (
  id BIGSERIAL PRIMARY KEY,
  comment TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  trip_participant_id INT NOT NULL,
  rating INT CHECK (rating BETWEEN 1 AND 5),
  FOREIGN KEY (trip_participant_id) REFERENCES trip_participants(id)
);

CREATE TABLE photos_url (
  id BIGSERIAL PRIMARY KEY,
  url TEXT NOT NULL,
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  review_id INT NOT NULL,
  FOREIGN KEY (review_id) REFERENCES reviews(id)
);