schema.sql

PRAGMA foreign_keys = ON;

CREATE TABLE preferences (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    language TEXT,
    car_type TEXT,
    max_distance REAL,
    min_rider_rating REAL,
    notes TEXT
);

CREATE TABLE cars (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    make TEXT NOT NULL,
    model TEXT NOT NULL,
    year INTEGER NOT NULL,
    plate_number TEXT NOT NULL UNIQUE
);

CREATE TABLE riders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    rating_avg REAL NOT NULL DEFAULT 0.0,
    total_rides INTEGER NOT NULL DEFAULT 0,
    preferences_id INTEGER,
    FOREIGN KEY (preferences_id) REFERENCES preferences(id)
);

CREATE TABLE drivers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    rating_avg REAL NOT NULL DEFAULT 0.0,
    is_available INTEGER NOT NULL DEFAULT 1 CHECK (is_available IN (0, 1)),
    car_id INTEGER,
    preferences_id INTEGER,
    current_location INTEGER,
    language TEXT,
    FOREIGN KEY (car_id) REFERENCES cars(id),
    FOREIGN KEY (preferences_id) REFERENCES preferences(id)
);

CREATE TABLE rides (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    rider_id INTEGER NOT NULL,
    driver_id INTEGER NOT NULL,
    start_location INTEGER NOT NULL,
    end_location INTEGER NOT NULL,
    requested_at TEXT NOT NULL,
    accepted_at TEXT,
    started_at TEXT,
    completed_at TEXT,
    fare REAL,
    status TEXT NOT NULL,
    cancellation_reason TEXT,
    canceled_at TEXT,
    FOREIGN KEY (rider_id) REFERENCES riders(id),
    FOREIGN KEY (driver_id) REFERENCES drivers(id)
);

CREATE TABLE ratings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ride_id INTEGER NOT NULL,
    rater_type TEXT NOT NULL,
    rater_id INTEGER NOT NULL,
    ratee_type TEXT NOT NULL,
    ratee_id INTEGER NOT NULL,
    stars INTEGER NOT NULL CHECK (stars BETWEEN 1 AND 5),
    comment TEXT,
    timestamp TEXT NOT NULL,
    FOREIGN KEY (ride_id) REFERENCES rides(id)
);

CREATE TABLE matching_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    rider_id INTEGER NOT NULL,
    algorithm TEXT NOT NULL,
    graph_model TEXT NOT NULL,
    candidate_drivers TEXT NOT NULL,
    distances TEXT NOT NULL,
    chosen_driver_id INTEGER,
    chosen_distance REAL,
    timestamp TEXT NOT NULL,
    FOREIGN KEY (rider_id) REFERENCES riders(id),
    FOREIGN KEY (chosen_driver_id) REFERENCES drivers(id)
);