source: DB/kreiranje.sql

DROP EXTENSION IF EXISTS "uuid-ossp" cascade;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

DROP TABLE IF EXISTS payment;
DROP TABLE IF EXISTS drive;
DROP TABLE IF EXISTS request;
DROP TABLE IF EXISTS passenger;
DROP TABLE IF EXISTS car;
DROP TABLE IF EXISTS driver;
DROP TABLE IF EXISTS admin_table;


CREATE TABLE admin_table (
        admin_id uuid DEFAULT uuid_generate_v4(),
        email VARCHAR(255) NOT NULL,
        encrypted_password VARCHAR(255) NOT NULL,
        first_name VARCHAR(255) NOT NULL,
        surname VARCHAR(255) NOT NULL,
        PRIMARY KEY (admin_id)
);

CREATE TABLE driver(
        driver_id uuid DEFAULT uuid_generate_v4(),
        email VARCHAR(255) NOT NULL,
        encrypted_password VARCHAR(255) NOT NULL,
        first_name VARCHAR(255) NOT NULL,
        surname VARCHAR(255) NOT NULL,
        profile_picture BYTEA NULL,
        price_per_km float NULL,
        status VARCHAR(255) NOT NULL DEFAULT 'Available',
        is_approved BOOLEAN NOT NULL DEFAULT 'false',
        driver_level VARCHAR(255) NOT NULL DEFAULT 'Beginner',
        num_grades INT NOT NULL DEFAULT 0,
        grade FLOAT,
        admin_id uuid,
        PRIMARY KEY(driver_id),
        CONSTRAINT FK_admin FOREIGN KEY(admin_id) REFERENCES admin_table(admin_id)
);

CREATE TABLE car(
        car_id uuid DEFAULT uuid_generate_v4(),
        license_plate VARCHAR(16) NOT NULL,
        make VARCHAR(255) NOT NULL,
        car_year INT NOT NULL,
        model VARCHAR(255) NOT NULL,
        driver_id uuid NOT NULL,
        PRIMARY KEY (car_id),
        CONSTRAINT FK_driver FOREIGN KEY(driver_id) REFERENCES driver(driver_id)
);

CREATE TABLE passenger(
        passenger_id uuid DEFAULT uuid_generate_v4(),
        email VARCHAR(255) NOT NULL,
        encrypted_password VARCHAR(255) NOT NULL,
        first_name VARCHAR(255) NOT NULL,
        surname VARCHAR(255) NOT NULL,
        PRIMARY KEY(passenger_id)
);

CREATE TABLE request(
        request_id uuid DEFAULT uuid_generate_v4(),
        status VARCHAR(255) NOT NULL DEFAULT 'Created',
        city_address VARCHAR(255) NULL,
        street_address VARCHAR(255) NULL,
        number_address INT NULL,
        latitude FLOAT NOT NULL,
        longitude FLOAT NOT NULL,       
        passenger_id uuid NOT NULL,
        chosen_driver_id uuid,
        confirmed_by_driver_id uuid,
        PRIMARY KEY (request_id),
        CONSTRAINT FK_chosen_driver FOREIGN KEY(chosen_driver_id) REFERENCES driver(driver_id),
        CONSTRAINT FK_confirmed_by_driver FOREIGN KEY(confirmed_by_driver_id) REFERENCES driver(driver_id),
        CONSTRAINT FK_passenger FOREIGN KEY(passenger_id) REFERENCES passenger(passenger_id)
);

CREATE TABLE drive(
        drive_id uuid DEFAULT uuid_generate_v4(),
        grade FLOAT NULL,
        km_travelled FLOAT NOT NULL DEFAULT 0.0,
        start_time TIMESTAMP NULL,
        end_time TIMESTAMP NULL,
        status VARCHAR(255) DEFAULT 'Started',
        destination_latitude FLOAT,
        destination_longitude FLOAT,
        driver_id uuid NOT NULL,
        request_id uuid NOT NULL,
        car_id uuid NOT NULL,
        PRIMARY KEY(drive_id),
        CONSTRAINT FK_car FOREIGN KEY(car_id) REFERENCES car(car_id),
        CONSTRAINT FK_request FOREIGN KEY(request_id) REFERENCES request(request_id),
        CONSTRAINT FK_driver FOREIGN KEY(driver_id) REFERENCES driver(driver_id)
);

CREATE TABLE payment(
        payment_id uuid DEFAULT uuid_generate_v4(),
        total_sum_payed FLOAT NOT NULL,
        drive_id uuid NOT NULL,
        passenger_id uuid NOT NULL,
        PRIMARY KEY(payment_id),
        CONSTRAINT FK_drive FOREIGN KEY(drive_id) REFERENCES drive(drive_id),
        CONSTRAINT FK_passenger FOREIGN KEY(passenger_id) REFERENCES passenger(passenger_id)
);

ALTER TABLE payment
ADD COLUMN driver_tip FLOAT NOT NULL DEFAULT 0.0;