DatabaseCreation: ddl.sql

create table AppUser
(
    id            serial primary key,
    first_name    varchar(20) not null check ( char_length(first_name) >= 1 ) default 'UNKNOWN',
    last_name     varchar(20) not null check ( char_length(last_name) >= 1 )  default 'USER',
    email         varchar(254) check ( email is null or email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' ),
    username      varchar(30) not null unique,
    password_hash text        not null check (char_length(password_hash) >= 8),
    date_of_birth date        not null check ( date_of_birth >= date '1900-01-01' and date_of_birth <= current_date),
    phone_number  varchar(15) not null check (phone_number ~ '^\+?[0-9]{7,15}$')
);

create table Dispatcher
(
    user_id int4 primary key references AppUser (id) on update cascade on delete cascade
);

create table Admin
(
    user_id int4 primary key references AppUser (id) on update cascade on delete cascade
);

create table DriverLicense
(
    id          serial primary key,
    issue_date  date        not null check (issue_date >= DATE '1900-01-01' and issue_date <= CURRENT_DATE),
    expire_date date,
    license_id  varchar(20) not null,
    check ( expire_date is null or expire_date > issue_date )
);

create table DriverLicenseCategory
(
    id            serial primary key,
    category_name varchar(5) not null unique
);
insert into DriverLicenseCategory (category_name)
values ('AM'),
       ('A1'),
       ('A2'),
       ('A'),
       ('B'),
       ('C1'),
       ('C'),
       ('D1'),
       ('D'),
       ('BE'),
       ('C1E'),
       ('CE'),
       ('D1E'),
       ('DE'),
       ('F'),
       ('G'),
       ('T');

create table Driver
(
    user_id           int4 primary key references AppUser (id) on update cascade on delete cascade,
    driver_license_id int4 not null references DriverLicense (id) on update cascade on delete restrict,
    latitude          double precision check ( latitude >= -90 and latitude <= 90 ),
    longitude         double precision check ( longitude >= -180 and longitude <= 180 )
);

create table DriverLicense_DriverLicenseCategory
(
    driver_license_id          int4 references DriverLicense (id) on update cascade on delete cascade,
    driver_license_category_id int4 references DriverLicenseCategory (id) on update cascade on delete restrict,
    primary key (driver_license_id,
                 driver_license_category_id)
);

create table FreelanceDriver
(
    driver_user_id  integer not null
        primary key
        references driver
            on update cascade on delete cascade,
    pricing_info_id int4    not null references pricinginfo (id),
    area_id         int4    not null references area (id)
);

create table Company
(
    id           serial primary key,
    name         varchar(50) not null check ( char_length(name) >= 1 and name ~ '^[A-Za-z0-9 &''.-]+$'),
    date_founded date        not null check ( date_founded <= current_date and date_founded >= date '1800-01-01' ),
    tax_number   varchar(20) not null check ( tax_number ~ '^[A-Z0-9]{5,20}$' ),
    phone_number varchar(15) not null check ( phone_number ~ '^\+?[0-9]{7,15}$' ),
    email        varchar(254) check ( email is null or email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' ),
    website      varchar(255) CHECK ( website is null or website ~* '^(https?:\/\/)?([a-z0-9-]+\.)+[a-z]{2,}(\/.*)?$'),
    active       bool        not null default true
);

create table EmploymentHistory
(
    employee_user_id int4 not null references AppUser (id) on update cascade on delete cascade,
    start_date       date not null check ( start_date <= current_date ),
    end_date         date,
    company_id       int4 not null references Company (id) on update cascade on delete restrict,
    primary key (employee_user_id,
                 start_date,
                 company_id),
    check (end_date is null or (end_date > start_date and end_date <= current_date))
);

create table Area
(
    id        serial primary key,
    latitude  double precision not null check ( latitude >= -90 and latitude <= 90 ),
    longitude double precision not null check ( longitude >= -180 and longitude <= 180 ),
    radius    decimal          not null check ( radius > 0 and radius <= 100000), -- in meters, max is 100km
    name      varchar(100)     not null check ( (char_length(trim(name)) >= 2) )
);

create table CurrencyCatalog
(
    id       serial primary key,
    currency varchar(5) not null unique
);
insert into CurrencyCatalog (currency)
values ('USD'), -- US Dollar
       ('EUR'), -- Euro
       ('GBP'), -- British Pound
       ('MKD'), -- Macedonian Denar
       ('CHF'), -- Swiss Franc
       ('JPY'), -- Japanese Yen
       ('CNY'), -- Chinese Yuan
       ('AUD'), -- Australian Dollar
       ('CAD'), -- Canadian Dollar
       ('NZD'), -- New Zealand Dollar
       ('SEK'), -- Swedish Krona
       ('NOK'), -- Norwegian Krone
       ('DKK'), -- Danish Krone
       ('RSD'), -- Serbian Dinar
       ('BGN'), -- Bulgarian Lev
       ('TRY'), -- Turkish Lira
       ('INR'), -- Indian Rupee
       ('BRL'), -- Brazilian Real
       ('ZAR'), -- South African Rand
       ('SGD'), -- Singapore Dollar
       ('HKD'), -- Hong Kong Dollar
       ('KRW'), -- South Korean Won
       ('MXN'), -- Mexican Peso
       ('PLN'), -- Polish Zloty
       ('CZK'), -- Czech Koruna
       ('HUF'), -- Hungarian Forint
       ('ILS'), -- Israeli Shekel
       ('AED'), -- UAE Dirham
       ('SAR'), -- Saudi Riyal
       ('THB'); -- Thai Baht

create type unit_type as enum ('kilometer', 'minute');
create table PricingInfo
(
    id          serial primary key,
    value       numeric(19, 2) not null check ( value > 0 ),
    unit        unit_type      not null,
    currency_id int4           not null references CurrencyCatalog (id) on update cascade on delete restrict
);

create table Company_Area
(
    company_id      int4 not null references Company (id) on update cascade on delete cascade,
    area_id         int4 not null references Area (id) on update cascade on delete cascade,
    pricing_info_id int4 not null references PricingInfo (id) on update cascade on delete restrict,
    primary key (company_id, area_id)
);

create table Brand
(
    id           serial primary key,
    name         varchar(50) not null check ( char_length(name) >= 1 and name ~ '^[A-Za-z0-9 &''.-]+$'),
    date_founded date        not null check ( date_founded <= current_date and date_founded >= date '1800-01-01' ),
    phone_number varchar(15) check ( phone_number is null or phone_number ~ '^\+?[0-9]{7,15}$' ),
    email        varchar(254) check ( email is null or email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' )
);

create table ModelFuelTypeCatalog
(
    id   serial primary key,
    type varchar(20) not null unique check (char_length(trim(type)) > 1)
);
insert into ModelFuelTypeCatalog (type)
values ('Petrol'),
       ('Diesel'),
       ('Electric'),
       ('Hybrid'),
       ('Plug-in Hybrid'),
       ('Hydrogen');
create table ModelTransmissionCatalog
(
    id           serial primary key,
    transmission varchar(20) not null unique check (char_length(trim(transmission)) > 1)
);
insert into ModelTransmissionCatalog (transmission)
values ('Automatic'),
       ('Manual');

create table Model
(
    id                            serial primary key,
    name                          varchar(50) not null check (char_length(trim(name)) >= 2),
    engine_capacity_cc            decimal check (engine_capacity_cc > 0),
    body_style                    varchar(50) check (body_style is null or char_length(trim(body_style)) >= 2),
    brand_id                      int4        not null references Brand (id) on update cascade on delete restrict,
    model_fuel_type_catalog_id    int4        not null references ModelFuelTypeCatalog (id) on update cascade on delete restrict,
    model_transmission_catalog_id int4        not null references ModelTransmissionCatalog (id) on update cascade on delete restrict
);

create table Vehicle
(
    VIN                   varchar(17) primary key,
    passenger_capacity    int4 not null check (passenger_capacity > 0 and passenger_capacity <= 50) default 1,
    year                  int4 not null check (year > 1950 and year <= extract(year from current_date)),
    wheelchair_accessible bool not null                                                             default false,
    model_id              int4 not null references Model (id) on update cascade on delete restrict,
    category_id           int4 not null references DriverLicenseCategory (id) on update cascade on delete restrict
);

create table ServiceHistory
(
    id                  serial primary key,
    date                date           not null check ( date <= current_date ),
    price               numeric(19, 2) not null check ( price > 0 ),
    currency_catalog_id int4           not null references CurrencyCatalog (id) on update cascade on delete restrict,
    fault_description   text,
    fix_description     text,
    vehicle_VIN         varchar(17) references Vehicle (VIN) on update cascade on delete restrict
);

create table VehicleOwnership
(
    vehicle_VIN              varchar(17) not null references Vehicle (VIN) on update cascade on delete restrict,
    company_id               int4        references Company (id) on update cascade on delete set null,
    freelance_driver_user_id int4        references FreelanceDriver (driver_user_id) on update cascade on delete set null,
    from_date                date        not null check ( from_date <= current_date ),
    to_date                  date,
    license_plate            varchar(20) check ( license_plate is null or license_plate ~ '^[A-Z0-9-]{1,20}$' ),
    primary key (vehicle_VIN, from_date),
    check (to_date is null or to_date > from_date)
);

create table Registration
(
    id                          serial primary key,
    vehicle_VIN                 varchar(17) not null,
    registration_date           date        not null check ( registration_date < current_date ),
    expiration_date             date,
    vehicle_ownership_from_date date        not null,
    check ( expiration_date is null or expiration_date > registration_date ),
    foreign key (vehicle_VIN, vehicle_ownership_from_date) references VehicleOwnership (vehicle_VIN, from_date) on update cascade on delete restrict,
    foreign key (vehicle_VIN) references Vehicle (VIN) on update cascade on delete restrict
);

create table Customer
(
    user_id int4 primary key references AppUser (id) on update cascade on delete cascade
);

create table CustomerPreference
(
    id                       serial primary key,
    seqno                    int4 not null check ( seqno > 0 ),
    customer_user_id         int4 not null references Customer (user_id) on update cascade on delete cascade,
    freelance_driver_user_id int4 references FreelanceDriver (driver_user_id) on update cascade on delete set null,
    company_id               int4 references Company (id) on update cascade on delete set null,
    check (
        (freelance_driver_user_id is not null and company_id is null)
            or
        (freelance_driver_user_id is null and company_id is not null)
        ),
    unique (customer_user_id, seqno)
);

create type request_status as enum ('pending', 'accepted', 'in_progress', 'completed', 'cancelled');
create table Request
(
    id                         serial primary key,
    customer_user_id           int4             not null references Customer (user_id) on update cascade on delete cascade,
    start_latitude             double precision not null check ( start_latitude >= -90 and start_latitude <= 90 ),
    start_longitude            double precision not null check ( start_longitude >= -180 and start_longitude <= 180 ),
    end_latitude               double precision not null check ( end_latitude >= -90 and end_latitude <= 90 ),
    end_longitude              double precision not null check ( end_longitude >= -180 and end_longitude <= 180 ),
    timestamp                  timestamp        not null default current_timestamp,
    number_of_adult_passengers int4             not null default 1 check (number_of_adult_passengers >= 1),
    number_of_children         int4                      default 0 check (number_of_children >= 0),
    status                     request_status   not null default 'pending',
    female_driver              bool             not null default false,
    luggage                    bool             not null default false,
    luggage_count              int4             not null default 0 check ( luggage_count >= 0 ),
    baby_seat_count            int4             not null default 0 check ( baby_seat_count >= 0 )
);

create table Waypoints
(
    id         serial primary key,
    latitude   double precision not null check ( latitude >= -90 and latitude <= 90 ),
    longitude  double precision not null check ( longitude >= -180 and longitude <= 180 ),
    seqno      int4             not null check ( seqno > 0 ),
    request_id int4 references Request (id) on update cascade on delete cascade,
    unique (request_id, seqno)
);

create type offer_status as enum ('pending', 'accepted', 'rejected', 'cancelled', 'completed');
create table Offer
(
    id                  serial primary key,
    status              offer_status   NOT NULL                     default 'pending',
    created_at          timestamp      not null                     default current_timestamp,
    request_id          int4           not null references Request (id) on update cascade on delete restrict,
    dispatcher_user_id  int4           references Dispatcher (user_id) on update cascade on delete set null,
    driver_user_id      int4           references Driver (user_id) on update cascade on delete set null,
    price               numeric(19, 2) not null check ( price > 0 ) default 1,
    currency_catalog_id int4 references CurrencyCatalog (id) on update cascade on delete restrict,
    ETA                 timestamp,
    customer_user_id    int4           references Customer (user_id) on update cascade on delete set null,
    check ( driver_user_id <> dispatcher_user_id )
);

create type ride_status as enum ('scheduled', 'in_progress', 'completed', 'cancelled');
create table Ride
(
    id                serial primary key,
    start_time        timestamp      not null default current_timestamp,
    end_time          timestamp,
    distance_traveled numeric(10, 2) not null default 0 check ( distance_traveled >= 0 ),
    vehicle_VIN       varchar(17)    not null references Vehicle (VIN) on update cascade on delete restrict,
    driver_user_id    int4           not null references Driver (user_id) on update cascade on delete restrict,
    request_id        int4           not null references Request (id) on update cascade on delete restrict,
    status            ride_status    not null default 'scheduled',
    offer_id          int4           not null references Offer (id) on update cascade on delete restrict,
    check ( end_time is null or end_time > start_time )
);

create table Review
(
    id               serial primary key,
    rating           numeric(3, 2) not null default 0 check ( rating >= 0 and rating <= 5 ),
    comment          text check ( char_length(comment) <= 1000 ),
    ride_id          int4          not null references Ride (id) on update cascade on delete cascade,
    customer_user_id int4          not null references Customer (user_id) on update cascade on delete cascade
);

create table Report
(
    ride_id          int4 references Ride (id) on update cascade on delete cascade,
    customer_user_id int4 references Customer (user_id) on update cascade on delete cascade,
    message          text check ( char_length(message) <= 1000 ),
    title            text check ( char_length(title) <= 1000 ),
    created_at       timestamp not null default current_timestamp,
    latitude         double precision check ( latitude >= -90 and latitude <= 90 ),
    longitude        double precision check ( longitude >= -180 and longitude <= 180 ),
    reason           text check (char_length(reason) <= 500),
    primary key (ride_id,
                 customer_user_id)
);

create table ChatMessage
(
    id           serial primary key,
    message      text      not null check ( char_length(message) > 0 and char_length(message) < 2000),
    timestamp    timestamp not null default current_timestamp,
    user_id_from int4      references AppUser (id) on update cascade on delete set null,
    ride_id      int4      references Ride (id) on update cascade on delete set null
);

create table Location
(
    id        serial primary key,
    latitude  double precision not null check ( latitude >= -90 and latitude <= 90 ),
    longitude double precision not null check ( longitude >= -180 and longitude <= 180 ),
    timestamp timestamp        not null,
    ride_id   int4             references Ride (id) on update cascade on delete set null
);

create table Payment
(
    id                  serial primary key,
    completed_ride_id   int4           references Ride (id) on update cascade on delete set null,
    total_amount        numeric(19, 2) not null check ( total_amount > 0 ),
    currency_catalog_id int4 references CurrencyCatalog (id) on update cascade on delete restrict
);

create type payment_method_type as enum ('CASH','CARD');
create table CustomerPayment
(
    customer_user_id    int4           not null default -1 references Customer (user_id) on update cascade on delete set default,
    payment_id          int4           not null default -1 references Payment (id) on update cascade on delete set default,
    amount              numeric(19, 2) not null check ( amount > 0 ),
    currency_catalog_id int4           not null references CurrencyCatalog (id) on update cascade on delete restrict,
    timestamp           timestamp      not null default current_timestamp,
    payment_method      payment_method_type,
    transaction_id      varchar(64) unique,
    primary key (customer_user_id,
                 payment_id)
);

create table driver_vehicle
(
    id          serial primary key,
    vin_vehicle varchar(17) not null references vehicle (vin) on delete restrict on update cascade,
    id_driver   int         not null references driver (user_id) on delete restrict on update cascade,
    time_from   timestamp   not null,
    time_to     timestamp
);