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

// Check if user is logged in and is staff
$sessionRole = $_SESSION['role'] ?? ($_SESSION['user_role'] ?? '');
if (!isset($_SESSION['user_id']) || $sessionRole !== 'staff') {
    echo json_encode(['success' => false, 'message' => 'Unauthorized']);
    exit;
}

try {
    $hasUserId = false;
    $hasAppointmentTime = false;
    $hasLegacyTime = false;
    $hasMessage = false;
    $hasUpdatedAt = false;
    $hasCustomerId = false;

    $col = $conn->query("SHOW COLUMNS FROM appointments LIKE 'user_id'");
    if ($col && $col->num_rows > 0) $hasUserId = true;
    if ($col instanceof mysqli_result) $col->close();

    $col = $conn->query("SHOW COLUMNS FROM appointments LIKE 'appointment_time'");
    if ($col && $col->num_rows > 0) $hasAppointmentTime = true;
    if ($col instanceof mysqli_result) $col->close();

    $col = $conn->query("SHOW COLUMNS FROM appointments LIKE 'time'");
    if ($col && $col->num_rows > 0) $hasLegacyTime = true;
    if ($col instanceof mysqli_result) $col->close();

    $col = $conn->query("SHOW COLUMNS FROM appointments LIKE 'message'");
    if ($col && $col->num_rows > 0) $hasMessage = true;
    if ($col instanceof mysqli_result) $col->close();

    $col = $conn->query("SHOW COLUMNS FROM appointments LIKE 'updated_at'");
    if ($col && $col->num_rows > 0) $hasUpdatedAt = true;
    if ($col instanceof mysqli_result) $col->close();

    $col = $conn->query("SHOW COLUMNS FROM appointments LIKE 'customer_id'");
    if ($col && $col->num_rows > 0) $hasCustomerId = true;
    if ($col instanceof mysqli_result) $col->close();

    $selectUserId = $hasUserId ? "a.user_id" : "NULL AS user_id";
    $selectTime = $hasAppointmentTime
        ? "a.appointment_time AS appointment_time"
        : ($hasLegacyTime ? "a.time AS appointment_time" : "NULL AS appointment_time");
    $selectMessage = $hasMessage ? "a.message" : "NULL AS message";
    $selectUpdatedAt = $hasUpdatedAt ? "a.updated_at" : "a.created_at AS updated_at";
    $joinCustomers = $hasCustomerId ? "LEFT JOIN customers c ON a.customer_id = c.id" : "LEFT JOIN customers c ON 1=0";

    // Get all scheduled visits/appointments
    $stmt = $conn->prepare("
        SELECT 
            a.id,
            {$selectUserId},
            CONCAT(c.firstname, ' ', c.lastname) as customer_name,
            c.phone,
            c.email,
            a.property_id,
            p.title as property_title,
            p.city,
            a.appointment_date,
            {$selectTime},
            {$selectMessage},
            a.status,
            a.created_at,
            {$selectUpdatedAt}
        FROM appointments a
        {$joinCustomers}
        LEFT JOIN properties p ON a.property_id = p.id
        WHERE a.status IN ('pending', 'approved', 'declined', 'scheduled', 'completed')
        ORDER BY CASE WHEN a.status = 'pending' THEN 0 ELSE 1 END, a.appointment_date ASC, appointment_time ASC
        LIMIT 100
    ");
    if (!$stmt) {
        throw new Exception('Failed to prepare visits query');
    }
    
    $stmt->execute();
    $result = $stmt->get_result();

    $appointments = [];
    while ($row = $result->fetch_assoc()) {
        $appointments[] = [
            'id' => intval($row['id']),
            'user_id' => intval($row['user_id']),
            'customer_name' => $row['customer_name'],
            'phone' => $row['phone'],
            'email' => $row['email'],
            'property_id' => $row['property_id'] ? intval($row['property_id']) : null,
            'property_title' => $row['property_title'],
            'city' => $row['city'],
            'appointment_date' => $row['appointment_date'],
            'appointment_time' => $row['appointment_time'],
            'message' => $row['message'],
            'status' => $row['status'],
            'created_at' => $row['created_at']
        ];
    }
    $stmt->close();

    echo json_encode(['success' => true, 'appointments' => $appointments, 'count' => count($appointments)]);
} catch (Exception $e) {
    echo json_encode(['success' => false, 'message' => $e->getMessage()]);
}
?>
