= Relational Model =

=== Relational diagram ===

Ова е деталниот релационен модел за системот GEOCAB (Taxi Services Application). Моделот е изработен во Visual Paradigm и содржи 16 табели кои ја опфаќаат комплетната логика на бизнис процесот.

[[Image(RelationalModel.jpg, width=800)]]

----

=== Descriptive documentation and argumentation ===

Врз основа на приложениот DDL и релационата шема, еве ги клучните сегменти на моделот:

 * '''Управување со возила и тарифи (`Vehicle_ownership`, `Pricing_rules`):'''
Моделот овозможува флексибилност преку разделување на физичките возила од нивните модели и типови. Табелата `Pricing_rules` е директно поврзана со `Vehicle_types`, што овозможува системот автоматски да пресметува `base_price` и `price_per_km` во зависност од тоа дали корисникот избрал стандардно или луксузно возило.

 * '''Следење на активност во реално време (`Active_drivers`):'''
За да се оптимизираат перформансите, динамичните податоци (моментална локација, достапност и работно време) се издвоени во `Active_drivers`. Ова овозможува брзо пребарување на слободни возачи без да се оптоварува главната табела `Drivers` со чести промени.

 * '''Логика на возења и трансакции (`Rides`, `Payments`):'''
Табелата `Rides` е централна точка каде се поврзуваат корисникот, возачот и две локации (pickup и dropoff). Специфично е моделирана табелата `Cancellations` која следи кој го откажал возењето (USER, DRIVER или ADMIN) и дали се наплаќа пенали (`cancellation_fee`).

 * '''Интегритет и ограничувања:'''
Во сите табели се применети строги ограничувања (`CONSTRAINT`), како што се проверки за валидност на е-маил, телефонски број и рејтинг (0-5). Релациите користат `ON DELETE RESTRICT` за клучните матични податоци (Status, Locations) за да се спречи случајно бришење на историски записи.

----
=== DDL Scripts (PostgreSQL) ===
{{{
CREATE TABLE Status (
                        status_id SERIAL PRIMARY KEY,
                        status_tip VARCHAR(255) NOT NULL,
                        status_text VARCHAR(255) NOT NULL

);

CREATE TABLE Vehicles_model (
                                Vehicles_model_id SERIAL PRIMARY KEY,
                                Model VARCHAR(255) NOT NULL
);

CREATE TABLE Locations (
                           location_id SERIAL PRIMARY KEY,
                           latitude NUMERIC(9,6) NOT NULL,
                           longitude NUMERIC(9,6) NOT NULL,
                           grad VARCHAR(255) NOT NULL,
                           naselba VARCHAR(255) NOT NULL,
                           ulica VARCHAR(255) NOT NULL,
                           broj VARCHAR(255) NOT NULL,
                           created_at TIMESTAMP NOT NULL
);

CREATE TABLE Users (
                       User_id SERIAL PRIMARY KEY,
                       name VARCHAR(255) NOT NULL,
                       username VARCHAR(255) NOT NULL UNIQUE,
                       phone VARCHAR(255) NOT NULL UNIQUE,
                       email VARCHAR(255) NOT NULL UNIQUE,
                       password_hash TEXT NOT NULL,
                       created_at TIMESTAMP NOT NULL,
                       status_id INT NOT NULL,

                       CONSTRAINT chk_users_phone CHECK (phone ~ '^[0-9+]+$'),
                       CONSTRAINT chk_users_email CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$'),

                       CONSTRAINT FK_Users_Status
                           FOREIGN KEY (status_id) REFERENCES Status(status_id)
                               ON DELETE RESTRICT
                               ON UPDATE CASCADE
);

CREATE TABLE Drivers (
                         driver_id SERIAL PRIMARY KEY,
                         name VARCHAR(255) NOT NULL,
                         phone VARCHAR(255) NOT NULL UNIQUE,
                         license_number VARCHAR(255) NOT NULL UNIQUE,
                         registration_date DATE NOT NULL,
                         rating NUMERIC(3,2) NOT NULL,
                         status_id INT NOT NULL,

                         CONSTRAINT chk_drivers_phone CHECK (phone ~ '^[0-9+]+$'),
                         CONSTRAINT chk_drivers_rating CHECK (rating BETWEEN 0 AND 5),

                         CONSTRAINT FK_Drivers_Status
                             FOREIGN KEY (status_id) REFERENCES Status(status_id)
                                 ON DELETE RESTRICT
                                 ON UPDATE CASCADE
);

CREATE TABLE Admins (
                        admins_id SERIAL PRIMARY KEY,
                        name VARCHAR(255) NOT NULL,
                        username VARCHAR(255) NOT NULL UNIQUE,
                        phone VARCHAR(255) NOT NULL UNIQUE,
                        email VARCHAR(255) NOT NULL UNIQUE,
                        password_hash TEXT NOT NULL,
                        created_at TIMESTAMP NOT NULL,
                        status_id INT NOT NULL,

                        CONSTRAINT chk_admins_phone CHECK (phone ~ '^[0-9+]+$'),
                        CONSTRAINT chk_admins_email CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$'),

                        CONSTRAINT FK_Admins_Status
                            FOREIGN KEY (status_id) REFERENCES Status(status_id)
                                ON DELETE RESTRICT
                                ON UPDATE CASCADE
);

CREATE TABLE Vehicle_types (
                               vehicle_type_id SERIAL PRIMARY KEY,
                               type_name VARCHAR(255) NOT NULL,
                               description VARCHAR(255) NOT NULL,
                               Vehicles_model_id INT NOT NULL,

                               CONSTRAINT FK_Vehicle_types_Model
                                   FOREIGN KEY (Vehicles_model_id)
                                       REFERENCES Vehicles_model(Vehicles_model_id)
                                       ON DELETE RESTRICT
                                       ON UPDATE CASCADE
);

CREATE TABLE Vehicle_ownership (
                                   Vehicle_ownership_id SERIAL PRIMARY KEY,
                                   Plate VARCHAR(255) NOT NULL UNIQUE,
                                   Traffic_permit VARCHAR(255) NOT NULL UNIQUE,
                                   Color VARCHAR(255) NOT NULL,
                                   year INT NOT NULL,
                                   vehicle_type_id INT NOT NULL,

                                   CONSTRAINT chk_vehicle_year CHECK (year >= 1900),

                                   CONSTRAINT FK_Vehicle_ownership_Type
                                       FOREIGN KEY (vehicle_type_id)
                                           REFERENCES Vehicle_types(vehicle_type_id)
                                           ON DELETE RESTRICT
                                           ON UPDATE CASCADE
);

CREATE TABLE Pricing_rules (
                               tariff_id SERIAL PRIMARY KEY,
                               base_price NUMERIC(10,2) NOT NULL,
                               price_per_km NUMERIC(10,2) NOT NULL,
                               vehicle_type_id INT NOT NULL,

                               CONSTRAINT chk_price CHECK (base_price >= 0 AND price_per_km >= 0),

                               CONSTRAINT FK_Pricing_rules_Vehicle_types
                                   FOREIGN KEY (vehicle_type_id)
                                       REFERENCES Vehicle_types(vehicle_type_id)
                                       ON DELETE RESTRICT
                                       ON UPDATE CASCADE
);

CREATE TABLE Drivers_Vehicle_ownership (
                                           driver_id INT NOT NULL,
                                           Vehicle_ownership_id INT NOT NULL,

                                           PRIMARY KEY (driver_id, Vehicle_ownership_id),

                                           CONSTRAINT FK_DVO_Drivers
                                               FOREIGN KEY (driver_id)
                                                   REFERENCES Drivers(driver_id)
                                                    ON DELETE CASCADE,

                                           CONSTRAINT FK_DVO_Vehicles
                                               FOREIGN KEY (Vehicle_ownership_id)
                                                   REFERENCES Vehicle_ownership(Vehicle_ownership_id)
                                                   ON DELETE CASCADE
);

CREATE TABLE Active_drivers (
                                driver_id INT PRIMARY KEY,
                                current_location_id INT NOT NULL,
                                is_available BOOLEAN NOT NULL,
                                working_start TIMESTAMP NOT NULL,
                                working_end TIMESTAMP NOT NULL,

                                CONSTRAINT FK_Active_drivers_Drivers
                                    FOREIGN KEY (driver_id)
                                        REFERENCES Drivers(driver_id)
                                        ON DELETE CASCADE,

                                CONSTRAINT FK_Active_drivers_Locations
                                    FOREIGN KEY (current_location_id)
                                        REFERENCES Locations(location_id)
                                        ON DELETE RESTRICT
);

CREATE TABLE Rides (
                       ride_id SERIAL PRIMARY KEY,
                       user_id INT NOT NULL,
                       driver_id INT NOT NULL,
                       pickup_location_id INT NOT NULL,
                       dropoff_location_id INT NOT NULL,
                       status_id INT NOT NULL,
                       request_time TIMESTAMP NOT NULL,
                       pickup_time TIMESTAMP NOT NULL,
                       start_time TIMESTAMP NOT NULL,
                       end_time TIMESTAMP NOT NULL,
                       distance_km NUMERIC(6,2) NOT NULL,
                       final_price NUMERIC(10,2) NOT NULL,
                       discount_percentage NUMERIC(5,2) NOT NULL,

                       CONSTRAINT chk_distance CHECK (distance_km >= 0),
                       CONSTRAINT chk_discount CHECK (discount_percentage BETWEEN 0 AND 100),

                       CONSTRAINT FK_Rides_Users
                           FOREIGN KEY (user_id) REFERENCES Users(User_id) ON DELETE RESTRICT,

                       CONSTRAINT FK_Rides_Drivers
                           FOREIGN KEY (driver_id) REFERENCES Drivers(driver_id) ON DELETE RESTRICT,

                       CONSTRAINT FK_Rides_Pickup_Location
                           FOREIGN KEY (pickup_location_id) REFERENCES Locations(location_id) ON DELETE RESTRICT,

                       CONSTRAINT FK_Rides_Dropoff_Location
                           FOREIGN KEY (dropoff_location_id) REFERENCES Locations(location_id) ON DELETE RESTRICT,

                       CONSTRAINT FK_Rides_Status
                           FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT
);
CREATE TABLE Payment_methods(
                                payment_method_id SERIAL PRIMARY KEY,
                                payment_tip  VARCHAR(255) NOT NULL
);

CREATE TABLE Payments (
                          payment_id SERIAL PRIMARY KEY,
                          amount NUMERIC(10,2) NOT NULL,
                          payment_method_id INT NOT NULL,
                          payment_time TIMESTAMP NOT NULL,
                          ride_id INT NOT NULL,
                          status_id INT NOT NULL,

                          CONSTRAINT chk_amount CHECK (amount >= 0),

                          CONSTRAINT FK_Payments_Rides
                              FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE,

                          CONSTRAINT FK_Payments_Status
                              FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT,

                          CONSTRAINT FK_Payments_Method
                              FOREIGN KEY (payment_method_id) REFERENCES Payment_methods(payment_method_id) ON DELETE RESTRICT
);


CREATE TABLE Ratings (
                         rating_id SERIAL PRIMARY KEY,
                         score INT NOT NULL,
                         comment VARCHAR(255),
                         created_at TIMESTAMP NOT NULL,
                         ride_id INT NOT NULL,
                         User_id INT NOT NULL,
                         driver_id INT NOT NULL,

                         CONSTRAINT chk_score CHECK (score BETWEEN 1 AND 5),

                         CONSTRAINT FK_Ratings_Rides
                             FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE,

                         CONSTRAINT FK_Ratings_Users
                             FOREIGN KEY (User_id) REFERENCES Users(User_id) ON DELETE CASCADE,

                         CONSTRAINT FK_Ratings_Drivers
                             FOREIGN KEY (driver_id) REFERENCES Drivers(driver_id) ON DELETE CASCADE
);

CREATE TABLE Cancellations (
                               cancellation_id SERIAL PRIMARY KEY,
                               cancelled_by_type VARCHAR(255) NOT NULL,
                               cancelled_by_id INT NOT NULL,
                               reason VARCHAR(255) NOT NULL,
                               cancellation_fee NUMERIC(10,2) NOT NULL,
                               created_at TIMESTAMP NOT NULL,
                               ride_id INT NOT NULL,

                               CONSTRAINT chk_fee CHECK (cancellation_fee >= 0),
                               CONSTRAINT chk_cancelled_by_type CHECK (cancelled_by_type IN ('USER', 'DRIVER', 'ADMIN')),

                               CONSTRAINT FK_Cancellations_Rides
                                   FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE
);
CREATE TABLE Messages(
                         message_id SERIAL PRIMARY KEY,
                         message_text VARCHAR(255) NOT NULL

);

CREATE TABLE Notifications (
                               notification_id SERIAL PRIMARY KEY,
                               message_id INT NOT NULL,
                               sent_time TIMESTAMP NOT NULL,
                               User_id INT NOT NULL,
                               ride_id INT,
                               status_id INT NOT NULL,

                               CONSTRAINT FK_Notifications_Users
                                   FOREIGN KEY (User_id) REFERENCES Users(User_id) ON DELETE CASCADE,

                               CONSTRAINT FK_Notifications_Rides
                                   FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE,

                               CONSTRAINT FK_Notifications_Status
                                   FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT,

                               CONSTRAINT FK_Notifications_Messages
                                    FOREIGN KEY (message_id) REFERENCES Messages(message_id) ON DELETE CASCADE
);