get_schedules.php

<?php
header('Content-Type: application/json');
require_once 'db/config.php';

if (!isset($_GET['expert_id'])) {
    echo json_encode([]);
    exit();
}

$expert_id = $_GET['expert_id'];

// Get available schedules for the expert that don't have appointments or have available slots
$sql = "
    SELECT s.*, 
           COALESCE(COUNT(a.id), 0) as appointment_count
    FROM schedule s
    LEFT JOIN appointment a ON s.id = a.schedule_id AND a.status != 'cancelled'
    WHERE s.expert_id = ? 
    AND s.date >= CURDATE()
    AND s.is_available = 1
    GROUP BY s.id
    HAVING appointment_count = 0
    ORDER BY s.date ASC, s.start_time ASC
";

$stmt = $conn->prepare($sql);
if ($stmt) {
    $stmt->bind_param("i", $expert_id);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $schedules = [];
    while ($row = $result->fetch_assoc()) {
        $schedules[] = $row;
    }
    
    echo json_encode($schedules);
    $stmt->close();
} else {
    echo json_encode([]);
}
?>