<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
include_once 'db_connect.php'; 

try {
    /**
     * استعلام محسن:
     * 1. نستخدم subquery لحساب عدد الحجوزات أولاً لضمان الدقة.
     * 2. نربط النتيجة مع جداول الأطباء والمستخدمين والتخصصات.
     * 3. أضفنا d.specialty_id لضمان توافق البيانات مع الـ Model في Flutter.
     */
$sql = "SELECT 
                d.id, 
                d.user_id,
                u.full_name, 
                u.phone, 
                u.city,
                u.address,
                d.specialty_id,
                d.profile_image,
                d.consultation_fee,
                d.latitude,
                d.longitude,
                d.is_online_service, -- تم الإضافة هنا
                d.is_available_now,   -- تم الإضافة هنا
                s.name_ar as specialty_name_ar, 
                s.name_en as specialty_name_en,
                COALESCE(b_counts.total_bookings, 0) as total_bookings 
            FROM doctors d 
            INNER JOIN users u ON d.user_id = u.id
            LEFT JOIN medical_specialties s ON d.specialty_id = s.id
            INNER JOIN (
                SELECT doctor_id, COUNT(id) as total_bookings 
                FROM bookings 
                GROUP BY doctor_id
            ) b_counts ON d.id = b_counts.doctor_id
            ORDER BY total_bookings DESC, d.id DESC 
            LIMIT 6";

    $stmt = $conn->prepare($sql);
    $stmt->execute();
    $doctors = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo json_encode([
        "status" => "success",
        "count" => count($doctors),
        "data" => $doctors
    ]);
} catch (PDOException $e) {
    error_log("Database Error: " . $e->getMessage());
    echo json_encode([
        "status" => "error", 
        "message" => "Database error occurred"
    ]);
}
?>