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,
    national_id VARCHAR(50) 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,
    rating_count INT UNSIGNED NOT NULL DEFAULT 0,
    gender VARCHAR(10) NOT NULL DEFAULT 'male',
    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,
    route_stops LONGTEXT DEFAULT 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,3) 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 ride_ratings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ride_id INT UNSIGNED NOT NULL,
    rater_id INT UNSIGNED NOT NULL,
    target_user_id INT UNSIGNED NOT NULL,
    score TINYINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ride_ratings_ride FOREIGN KEY (ride_id) REFERENCES rides(id) ON DELETE CASCADE,
    CONSTRAINT fk_ride_ratings_rater FOREIGN KEY (rater_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_ride_ratings_target FOREIGN KEY (target_user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_ride_rater (ride_id, rater_id),
    INDEX idx_ride_ratings_target (target_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS deliveries (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    rider_id INT UNSIGNED DEFAULT NULL,
    driver_id INT UNSIGNED DEFAULT NULL,
    title VARCHAR(255) NOT NULL,
    status VARCHAR(30) NOT NULL,
    price DECIMAL(12,2) NOT NULL,
    payment_method VARCHAR(50) NOT NULL DEFAULT 'كاش',
    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,
    trip_details TEXT DEFAULT NULL,
    distance_km DECIMAL(10,3) NOT NULL DEFAULT 0.000,
    distance_text VARCHAR(100) NOT NULL,
    icon_name VARCHAR(50) NOT NULL,
    requested_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    accepted_at TIMESTAMP NULL DEFAULT NULL,
    completed_at TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_deliveries_status (status),
    INDEX idx_deliveries_rider (rider_id),
    INDEX idx_deliveries_driver (driver_id)
) 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;

CREATE TABLE IF NOT EXISTS complaints (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    role VARCHAR(20) NOT NULL,
    topic VARCHAR(150) NOT NULL,
    description TEXT NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'new',
    attachment_name VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_complaints_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_complaints_user (user_id),
    INDEX idx_complaints_role (role),
    INDEX idx_complaints_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS driver_offers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    offer_type VARCHAR(30) NOT NULL,
    title VARCHAR(180) NOT NULL,
    subtitle TEXT NOT NULL,
    reward_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    progress_current INT UNSIGNED NOT NULL DEFAULT 0,
    progress_target INT UNSIGNED NOT NULL DEFAULT 0,
    remaining_days INT UNSIGNED NOT NULL DEFAULT 0,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_driver_offers_type (offer_type),
    INDEX idx_driver_offers_active (is_active),
    INDEX idx_driver_offers_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;