schema.sql

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    phone TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL CHECK (role IN ('passenger', 'driver', 'admin'))
);

CREATE TABLE IF NOT EXISTS drivers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    license_number TEXT,
    vehicle_info TEXT,
    id_doc_path TEXT,
    license_doc_path TEXT,
    vehicle_doc_path TEXT,
    verification_status TEXT NOT NULL DEFAULT 'pending'
        CHECK (verification_status IN ('pending', 'approved', 'rejected')),
    FOREIGN KEY(user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS driver_status (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    driver_id INTEGER NOT NULL,
    is_online INTEGER NOT NULL DEFAULT 0,
    last_change TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(driver_id) REFERENCES drivers(id)
);
-- Rides table
CREATE TABLE IF NOT EXISTS rides (
                                     id INTEGER PRIMARY KEY AUTOINCREMENT,
                                     passenger_id INTEGER NOT NULL,
                                     pickup_address TEXT NOT NULL,
                                     dropoff_address TEXT NOT NULL,
                                     pickup_lat REAL,
                                     pickup_lng REAL,
                                     dropoff_lat REAL,
                                     dropoff_lng REAL,
                                     notes TEXT,
                                     status TEXT DEFAULT 'requested', -- requested, estimated, waiting, accepted, completed, cancelled
                                     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                                     FOREIGN KEY (passenger_id) REFERENCES users(id)
    );