CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role VARCHAR(20) NOT NULL,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(30) NOT NULL UNIQUE,
    email VARCHAR(150) DEFAULT NULL,
    city VARCHAR(100) NOT NULL DEFAULT 'الفيوم',
    wallet_balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    rating DECIMAL(3,2) NOT NULL DEFAULT 4.80,
    vehicle_type VARCHAR(50) DEFAULT NULL,
    plate_number VARCHAR(50) DEFAULT NULL,
    vehicle_model VARCHAR(100) DEFAULT NULL,
    vehicle_color VARCHAR(50) DEFAULT NULL,
    women_only TINYINT(1) NOT NULL DEFAULT 0,
    token VARCHAR(128) DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_users_role (role),
    INDEX idx_users_token (token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS places (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(100) NOT NULL,
    name VARCHAR(150) NOT NULL,
    scope VARCHAR(20) NOT NULL,
    latitude DECIMAL(10,6) NOT NULL,
    longitude DECIMAL(10,6) NOT NULL,
    INDEX idx_places_scope (scope),
    INDEX idx_places_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rides (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    rider_id INT UNSIGNED NOT NULL,
    driver_id INT UNSIGNED DEFAULT NULL,
    pickup_name VARCHAR(255) NOT NULL,
    pickup_lat DECIMAL(10,6) NOT NULL,
    pickup_lng DECIMAL(10,6) NOT NULL,
    destination_name VARCHAR(255) NOT NULL,
    destination_lat DECIMAL(10,6) NOT NULL,
    destination_lng DECIMAL(10,6) NOT NULL,
    inside_city TINYINT(1) NOT NULL,
    trip_type VARCHAR(50) NOT NULL,
    vehicle_type VARCHAR(50) NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    trip_details TEXT NOT NULL,
    estimated_fare DECIMAL(12,2) NOT NULL,
    distance_km DECIMAL(10,2) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'pending',
    requested_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    accepted_at DATETIME DEFAULT NULL,
    completed_at DATETIME DEFAULT NULL,
    CONSTRAINT fk_rides_rider FOREIGN KEY (rider_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_rides_driver FOREIGN KEY (driver_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_rides_status (status),
    INDEX idx_rides_rider (rider_id),
    INDEX idx_rides_driver (driver_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS deliveries (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    status VARCHAR(30) NOT NULL,
    price DECIMAL(12,2) NOT NULL,
    pickup_name VARCHAR(255) NOT NULL,
    pickup_lat DECIMAL(10,6) NOT NULL,
    pickup_lng DECIMAL(10,6) NOT NULL,
    dropoff_name VARCHAR(255) NOT NULL,
    dropoff_lat DECIMAL(10,6) NOT NULL,
    dropoff_lng DECIMAL(10,6) NOT NULL,
    weight_text VARCHAR(100) NOT NULL,
    distance_text VARCHAR(100) NOT NULL,
    icon_name VARCHAR(50) NOT NULL,
    INDEX idx_deliveries_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role VARCHAR(20) NOT NULL,
    title VARCHAR(150) NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_notifications_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;