Tables.sql

-- Author : Tushar Gaonkar (https://github.com/TusharGaonkar)

-- Rooms Table Schema
create table if not exists
  public.rooms (
    id bigint generated by default as identity,
    created_at timestamp with time zone not null default now(),
    name text not null,
    "maxCapacity" smallint not null,
    "regularPrice" numeric not null,
    discount numeric null,
    description text null,
    image text null,
    constraint rooms_pkey primary key (id)
  ) tablespace pg_default;

-- Guests Table Schema
create table if not exists
  public.guests (
    id bigint primary key generated always as identity,
    created_at timestamp with time zone not null default now(),
    "fullName" text not null,
    email text null,
    nationality text not null,
    "nationalID" text not null,
    "countryFlag" text null
  ) tablespace pg_default;

-- Bookings Table Schema
  create table if not exists
  public.bookings (
    id bigint generated by default as identity,
    created_at timestamp with time zone not null default now(),
    "startDate" timestamp without time zone not null,
    "endDate" timestamp without time zone not null,
    "numNights" smallint null,
    "numGuests" smallint null,
    "roomPrice" numeric not null,
    "extrasPrice" numeric null,
    "totalPrice" numeric not null,
    status text not null,
    "hasBreakfast" boolean null,
    "isPaid" boolean not null default false,
    observations text null,
    "roomID" bigint not null,
    "guestID" bigint not null,
    constraint bookings_pkey primary key (id),
    constraint bookings_guestID_fkey foreign key ("guestID") references guests (id),
    constraint bookings_roomID_fkey foreign key ("roomID") references rooms (id)
  ) tablespace pg_default;

-- Settings Table Schema
create table if not exists
  public.settings (
    id bigint generated by default as identity,
    created_at timestamp with time zone not null default now(),
    "minBookingLength" smallint not null default 1,
    "maxBookingLength" smallint not null default 10,
    "maxGuestsPerBooking" smallint not null default 3,
    "breakfastPrice" numeric not null default 0,
    constraint settings_pkey primary key (id)
  ) tablespace pg_default;