| 1 | DROP EXTENSION IF EXISTS "uuid-ossp" cascade;
|
|---|
| 2 | CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|---|
| 3 |
|
|---|
| 4 | DROP TABLE IF EXISTS payment;
|
|---|
| 5 | DROP TABLE IF EXISTS drive;
|
|---|
| 6 | DROP TABLE IF EXISTS request;
|
|---|
| 7 | DROP TABLE IF EXISTS passenger;
|
|---|
| 8 | DROP TABLE IF EXISTS car;
|
|---|
| 9 | DROP TABLE IF EXISTS driver;
|
|---|
| 10 | DROP TABLE IF EXISTS admin_table;
|
|---|
| 11 |
|
|---|
| 12 |
|
|---|
| 13 | CREATE TABLE admin_table (
|
|---|
| 14 | admin_id uuid DEFAULT uuid_generate_v4(),
|
|---|
| 15 | email VARCHAR(255) NOT NULL,
|
|---|
| 16 | encrypted_password VARCHAR(255) NOT NULL,
|
|---|
| 17 | first_name VARCHAR(255) NOT NULL,
|
|---|
| 18 | surname VARCHAR(255) NOT NULL,
|
|---|
| 19 | PRIMARY KEY (admin_id)
|
|---|
| 20 | );
|
|---|
| 21 |
|
|---|
| 22 | CREATE TABLE driver(
|
|---|
| 23 | driver_id uuid DEFAULT uuid_generate_v4(),
|
|---|
| 24 | email VARCHAR(255) NOT NULL,
|
|---|
| 25 | encrypted_password VARCHAR(255) NOT NULL,
|
|---|
| 26 | first_name VARCHAR(255) NOT NULL,
|
|---|
| 27 | surname VARCHAR(255) NOT NULL,
|
|---|
| 28 | profile_picture BYTEA NULL,
|
|---|
| 29 | price_per_km float NULL,
|
|---|
| 30 | status VARCHAR(255) NOT NULL DEFAULT 'Available',
|
|---|
| 31 | is_approved BOOLEAN NOT NULL DEFAULT 'false',
|
|---|
| 32 | driver_level VARCHAR(255) NOT NULL DEFAULT 'Beginner',
|
|---|
| 33 | num_grades INT NOT NULL DEFAULT 0,
|
|---|
| 34 | grade FLOAT,
|
|---|
| 35 | admin_id uuid,
|
|---|
| 36 | PRIMARY KEY(driver_id),
|
|---|
| 37 | CONSTRAINT FK_admin FOREIGN KEY(admin_id) REFERENCES admin_table(admin_id)
|
|---|
| 38 | );
|
|---|
| 39 |
|
|---|
| 40 | CREATE TABLE car(
|
|---|
| 41 | car_id uuid DEFAULT uuid_generate_v4(),
|
|---|
| 42 | license_plate VARCHAR(16) NOT NULL,
|
|---|
| 43 | make VARCHAR(255) NOT NULL,
|
|---|
| 44 | car_year INT NOT NULL,
|
|---|
| 45 | model VARCHAR(255) NOT NULL,
|
|---|
| 46 | driver_id uuid NOT NULL,
|
|---|
| 47 | PRIMARY KEY (car_id),
|
|---|
| 48 | CONSTRAINT FK_driver FOREIGN KEY(driver_id) REFERENCES driver(driver_id)
|
|---|
| 49 | );
|
|---|
| 50 |
|
|---|
| 51 | CREATE TABLE passenger(
|
|---|
| 52 | passenger_id uuid DEFAULT uuid_generate_v4(),
|
|---|
| 53 | email VARCHAR(255) NOT NULL,
|
|---|
| 54 | encrypted_password VARCHAR(255) NOT NULL,
|
|---|
| 55 | first_name VARCHAR(255) NOT NULL,
|
|---|
| 56 | surname VARCHAR(255) NOT NULL,
|
|---|
| 57 | PRIMARY KEY(passenger_id)
|
|---|
| 58 | );
|
|---|
| 59 |
|
|---|
| 60 | CREATE TABLE request(
|
|---|
| 61 | request_id uuid DEFAULT uuid_generate_v4(),
|
|---|
| 62 | status VARCHAR(255) NOT NULL DEFAULT 'Created',
|
|---|
| 63 | city_address VARCHAR(255) NULL,
|
|---|
| 64 | street_address VARCHAR(255) NULL,
|
|---|
| 65 | number_address INT NULL,
|
|---|
| 66 | latitude FLOAT NOT NULL,
|
|---|
| 67 | longitude FLOAT NOT NULL,
|
|---|
| 68 | passenger_id uuid NOT NULL,
|
|---|
| 69 | chosen_driver_id uuid,
|
|---|
| 70 | confirmed_by_driver_id uuid,
|
|---|
| 71 | PRIMARY KEY (request_id),
|
|---|
| 72 | CONSTRAINT FK_chosen_driver FOREIGN KEY(chosen_driver_id) REFERENCES driver(driver_id),
|
|---|
| 73 | CONSTRAINT FK_confirmed_by_driver FOREIGN KEY(confirmed_by_driver_id) REFERENCES driver(driver_id),
|
|---|
| 74 | CONSTRAINT FK_passenger FOREIGN KEY(passenger_id) REFERENCES passenger(passenger_id)
|
|---|
| 75 | );
|
|---|
| 76 |
|
|---|
| 77 | CREATE TABLE drive(
|
|---|
| 78 | drive_id uuid DEFAULT uuid_generate_v4(),
|
|---|
| 79 | grade FLOAT NULL,
|
|---|
| 80 | km_travelled FLOAT NOT NULL DEFAULT 0.0,
|
|---|
| 81 | start_time TIMESTAMP NULL,
|
|---|
| 82 | end_time TIMESTAMP NULL,
|
|---|
| 83 | status VARCHAR(255) DEFAULT 'Started',
|
|---|
| 84 | destination_latitude FLOAT,
|
|---|
| 85 | destination_longitude FLOAT,
|
|---|
| 86 | driver_id uuid NOT NULL,
|
|---|
| 87 | request_id uuid NOT NULL,
|
|---|
| 88 | car_id uuid NOT NULL,
|
|---|
| 89 | PRIMARY KEY(drive_id),
|
|---|
| 90 | CONSTRAINT FK_car FOREIGN KEY(car_id) REFERENCES car(car_id),
|
|---|
| 91 | CONSTRAINT FK_request FOREIGN KEY(request_id) REFERENCES request(request_id),
|
|---|
| 92 | CONSTRAINT FK_driver FOREIGN KEY(driver_id) REFERENCES driver(driver_id)
|
|---|
| 93 | );
|
|---|
| 94 |
|
|---|
| 95 | CREATE TABLE payment(
|
|---|
| 96 | payment_id uuid DEFAULT uuid_generate_v4(),
|
|---|
| 97 | total_sum_payed FLOAT NOT NULL,
|
|---|
| 98 | drive_id uuid NOT NULL,
|
|---|
| 99 | passenger_id uuid NOT NULL,
|
|---|
| 100 | PRIMARY KEY(payment_id),
|
|---|
| 101 | CONSTRAINT FK_drive FOREIGN KEY(drive_id) REFERENCES drive(drive_id),
|
|---|
| 102 | CONSTRAINT FK_passenger FOREIGN KEY(passenger_id) REFERENCES passenger(passenger_id)
|
|---|
| 103 | );
|
|---|
| 104 |
|
|---|
| 105 | ALTER TABLE payment
|
|---|
| 106 | ADD COLUMN driver_tip FLOAT NOT NULL DEFAULT 0.0;
|
|---|
| 107 |
|
|---|
