<?php
require_once __DIR__ . '/../../connect.php';
try {
    $southwestLat = filterRequest("southwestLat");
    $southwestLon = filterRequest("southwestLon");
    $northeastLat = filterRequest("northeastLat");
    $northeastLon = filterRequest("northeastLon");

    $sql = "
SELECT
    cl.driver_id,
    cl.latitude,
    cl.longitude,
    cl.heading,
    cl.speed,
    cl.status,
    cl.created_at,
    cl.updated_at,
    d.phone,
    d.email,
    d.birthdate,
    d.first_name,
    d.last_name,
    d.gender,
    d.maritalStatus,
    cr.make,
    cr.car_plate,
    cr.model,
    cr.color,
    cr.vin,
    cr.color_hex,
    cr.year,
    dt.token,
    COALESCE(AVG(rd.rating), 0) AS ratingDriver,
    COUNT(rd.id) AS ratingCount,
    TIMESTAMPDIFF(YEAR, d.birthdate, CURDATE()) AS age,
    (
        SELECT COALESCE(AVG(sub.behavior_score), 100)
        FROM (
            SELECT behavior_score
            FROM driver_behavior db
            WHERE db.driver_id = cl.driver_id
            ORDER BY db.id DESC
            LIMIT 5
        ) AS sub
    ) AS ai_behavior_score
FROM
    car_locations cl
LEFT JOIN driver d ON
    d.id = cl.driver_id
LEFT JOIN CarRegistration cr ON
    cr.driverID = cl.driver_id
LEFT JOIN driverToken dt ON
    dt.captain_id = cl.driver_id
LEFT JOIN ratingDriver rd ON
    rd.driver_id = cl.driver_id
WHERE
    cl.latitude >= :southwestLat AND cl.latitude <= :northeastLat 
    AND cl.longitude >= :southwestLon AND cl.longitude <= :northeastLon
    AND cl.status = 'off'
    AND cl.updated_at >= NOW() - INTERVAL 5 SECOND
    AND (cr.make NOT LIKE '%دراجة%' OR cr.model NOT LIKE '%دراجة%')
    AND d.gender = 'Female'
GROUP BY cl.driver_id
ORDER BY ratingDriver DESC, cl.updated_at DESC
LIMIT 10;
";

    $stmt = $con->prepare($sql);
    $stmt->bindParam(':southwestLat', $southwestLat);
    $stmt->bindParam(':southwestLon', $southwestLon);
    $stmt->bindParam(':northeastLat', $northeastLat);
    $stmt->bindParam(':northeastLon', $northeastLon);

    $stmt->execute();
    $car_locations = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if ($car_locations) {
        jsonSuccess($car_locations);
    } else {
        jsonError("No car locations found");
    }
} catch (PDOException $e) {
    error_log("[getfemalbehavior.php] " . $e->getMessage());
    jsonError("An internal error occurred. Please try again later.");
}
?>