PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    phone TEXT NOT NULL UNIQUE,
    full_name TEXT NOT NULL,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL CHECK (role IN ('passenger', 'driver')),
    avatar_url TEXT,
    is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1)),
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS drivers (
    user_id INTEGER PRIMARY KEY,
    motorcycle_brand TEXT NOT NULL,
    motorcycle_type TEXT NOT NULL,
    license_photo_path TEXT NOT NULL,
    license_verified INTEGER NOT NULL DEFAULT 0 CHECK (license_verified IN (0, 1)),
    is_online INTEGER NOT NULL DEFAULT 0 CHECK (is_online IN (0, 1)),
    current_lat REAL,
    current_lng REAL,
    last_location_at TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ride_requests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    passenger_id INTEGER NOT NULL,
    pickup_address TEXT NOT NULL,
    pickup_lat REAL NOT NULL,
    pickup_lng REAL NOT NULL,
    destination_address TEXT NOT NULL,
    destination_lat REAL NOT NULL,
    destination_lng REAL NOT NULL,
    status TEXT NOT NULL DEFAULT 'open' CHECK (
        status IN ('open', 'locked', 'picked_up', 'en_route', 'dropped_off', 'completed', 'cancelled')
    ),
    selected_offer_id INTEGER,
    selected_driver_id INTEGER,
    note TEXT,
    requested_at TEXT NOT NULL DEFAULT (datetime('now')),
    picked_up_at TEXT,
    dropped_off_at TEXT,
    completed_at TEXT,
    cancelled_at TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (passenger_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (selected_driver_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS ride_offers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ride_request_id INTEGER NOT NULL,
    driver_id INTEGER NOT NULL,
    offered_fare REAL NOT NULL CHECK (offered_fare > 0),
    currency TEXT NOT NULL DEFAULT 'PHP',
    status TEXT NOT NULL DEFAULT 'pending' CHECK (
        status IN ('pending', 'accepted', 'rejected', 'withdrawn', 'expired')
    ),
    message TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (ride_request_id) REFERENCES ride_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (driver_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sender_user_id INTEGER NOT NULL,
    receiver_user_id INTEGER NOT NULL,
    ride_request_id INTEGER,
    body TEXT NOT NULL,
    is_read INTEGER NOT NULL DEFAULT 0 CHECK (is_read IN (0, 1)),
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    read_at TEXT,
    FOREIGN KEY (sender_user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (receiver_user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (ride_request_id) REFERENCES ride_requests(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS emergency_contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    passenger_user_id INTEGER NOT NULL,
    emergency_contact_user_id INTEGER NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'active', 'revoked')),
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (passenger_user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (emergency_contact_user_id) REFERENCES users(id) ON DELETE CASCADE,
    CHECK (passenger_user_id <> emergency_contact_user_id)
);

CREATE TABLE IF NOT EXISTS user_tokens (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    token_hash TEXT NOT NULL UNIQUE,
    expires_at TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_users_phone ON users(phone);
CREATE INDEX IF NOT EXISTS idx_drivers_online ON drivers(is_online, license_verified);
CREATE INDEX IF NOT EXISTS idx_drivers_location ON drivers(current_lat, current_lng);
CREATE INDEX IF NOT EXISTS idx_ride_requests_passenger_status ON ride_requests(passenger_id, status);
CREATE INDEX IF NOT EXISTS idx_ride_requests_status_requested_at ON ride_requests(status, requested_at);
CREATE INDEX IF NOT EXISTS idx_ride_offers_ride_status ON ride_offers(ride_request_id, status);
CREATE INDEX IF NOT EXISTS idx_ride_offers_driver_status ON ride_offers(driver_id, status);
CREATE INDEX IF NOT EXISTS idx_messages_receiver_created ON messages(receiver_user_id, created_at);
CREATE INDEX IF NOT EXISTS idx_messages_ride_created ON messages(ride_request_id, created_at);
CREATE UNIQUE INDEX IF NOT EXISTS uq_emergency_active_per_passenger
ON emergency_contacts(passenger_user_id)
WHERE status = 'active';

CREATE INDEX IF NOT EXISTS idx_tokens_user ON user_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_tokens_exp ON user_tokens(expires_at);