<?php
/**
 * Get Broker's Scheduled Visits
 * Returns: Scheduled appointments for broker
 */

header('Content-Type: application/json');
require_once '../../config.php';
require_once __DIR__ . '/broker-identity.php';

$session_role = $_SESSION['role'] ?? ($_SESSION['user_role'] ?? '');
if (!isset($_SESSION['user_id']) || $session_role !== 'broker') {
    echo json_encode(['success' => false, 'message' => 'Unauthorized']);
    exit;
}

$session_broker_id = (int)$_SESSION['user_id'];
$brokerCtx = resolveBrokerContext($conn, $session_broker_id);
$broker_id = (int)$brokerCtx['property_broker_id'];
$fallback_broker_id = $session_broker_id !== $broker_id ? $session_broker_id : 0;

try {
    $hasAppointmentFullName = false;
    $hasAppointmentEmail = false;
    $hasAppointmentPhone = false;

    $fullNameCol = $conn->query("SHOW COLUMNS FROM appointments LIKE 'full_name'");
    if ($fullNameCol && $fullNameCol->num_rows > 0) {
        $hasAppointmentFullName = true;
    }
    if ($fullNameCol instanceof mysqli_result) {
        $fullNameCol->close();
    }

    $emailCol = $conn->query("SHOW COLUMNS FROM appointments LIKE 'email'");
    if ($emailCol && $emailCol->num_rows > 0) {
        $hasAppointmentEmail = true;
    }
    if ($emailCol instanceof mysqli_result) {
        $emailCol->close();
    }

    $phoneCol = $conn->query("SHOW COLUMNS FROM appointments LIKE 'phone'");
    if ($phoneCol && $phoneCol->num_rows > 0) {
        $hasAppointmentPhone = true;
    }
    if ($phoneCol instanceof mysqli_result) {
        $phoneCol->close();
    }

    $appointmentFullNameSelect = $hasAppointmentFullName
        ? "a.full_name AS appointment_full_name"
        : "NULL AS appointment_full_name";
    $appointmentEmailSelect = $hasAppointmentEmail
        ? "a.email AS appointment_email"
        : "NULL AS appointment_email";
    $appointmentPhoneSelect = $hasAppointmentPhone
        ? "a.phone AS appointment_phone"
        : "NULL AS appointment_phone";

    $conn->query("
        CREATE TABLE IF NOT EXISTS property_assignment_workflow (
            id INT AUTO_INCREMENT PRIMARY KEY,
            property_id INT NOT NULL UNIQUE,
            broker_id INT NULL,
            workflow_status VARCHAR(40) NOT NULL DEFAULT 'assigned',
            admin_review_status VARCHAR(20) NOT NULL DEFAULT 'pending',
            broker_confirmed_at DATETIME NULL,
            work_started_at DATETIME NULL,
            work_finished_at DATETIME NULL,
            admin_reviewed_by INT NULL,
            admin_reviewed_at DATETIME NULL,
            admin_notes VARCHAR(255) NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_broker_id (broker_id),
            INDEX idx_workflow_status (workflow_status),
            INDEX idx_admin_review_status (admin_review_status)
        )
    ");

    $stmt = $conn->prepare("
        SELECT 
            a.id, a.customer_id, a.property_id, a.appointment_date, 
            a.appointment_time, a.status, a.message, a.created_at,
            c.firstname AS customer_firstname,
            c.lastname AS customer_lastname,
            c.email AS customer_email,
            c.phone AS customer_phone,
            u.full_name AS user_full_name,
            u.email AS user_email,
            u.phone AS user_phone,
            {$appointmentFullNameSelect},
            {$appointmentEmailSelect},
            {$appointmentPhoneSelect},
            p.title as property_title, p.city as property_city
        FROM appointments a
        JOIN properties p ON a.property_id = p.id
        LEFT JOIN customers c ON a.customer_id = c.id
        LEFT JOIN users u ON a.customer_id = u.id
        WHERE (
            a.user_id = ?
            OR p.broker_id = ?
            OR (? > 0 AND p.broker_id = ?)
        )
          AND LOWER(COALESCE(a.status, 'pending')) IN ('pending', 'approved', 'scheduled', 'confirmed', 'completed', 'done', 'finished')
        ORDER BY COALESCE(a.appointment_date, DATE(a.created_at)) DESC, COALESCE(a.appointment_time, '00:00:00') DESC
        LIMIT 50
    ");
    $stmt->bind_param('iiii', $session_broker_id, $broker_id, $fallback_broker_id, $fallback_broker_id);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $visits = [];
    while ($row = $result->fetch_assoc()) {
        $first = trim((string)($row['customer_firstname'] ?? ''));
        $last = trim((string)($row['customer_lastname'] ?? ''));
        $customerName = trim($first . ' ' . $last);
        if ($customerName === '') {
            $customerName = trim((string)($row['user_full_name'] ?? ''));
        }
        if ($customerName === '') {
            $customerName = trim((string)($row['appointment_full_name'] ?? ''));
        }

        $customerEmail = trim((string)($row['customer_email'] ?? ''));
        if ($customerEmail === '') {
            $customerEmail = trim((string)($row['user_email'] ?? ''));
        }
        if ($customerEmail === '') {
            $customerEmail = trim((string)($row['appointment_email'] ?? ''));
        }

        $customerPhone = trim((string)($row['customer_phone'] ?? ''));
        if ($customerPhone === '') {
            $customerPhone = trim((string)($row['user_phone'] ?? ''));
        }
        if ($customerPhone === '') {
            $customerPhone = trim((string)($row['appointment_phone'] ?? ''));
        }

        $row['customer_name'] = $customerName !== '' ? $customerName : 'N/A';
        $row['email'] = $customerEmail;
        $row['phone'] = $customerPhone;
        $visits[] = $row;
    }
    $stmt->close();

    // Add synthetic "done" site-visit entries when admin approves broker finished work.
    $workflowStmt = $conn->prepare("
        SELECT
            aw.property_id,
            COALESCE(aw.work_finished_at, aw.updated_at) AS finished_at,
            p.title AS property_title,
            p.city AS property_city
        FROM property_assignment_workflow aw
        JOIN properties p ON p.id = aw.property_id
        WHERE (aw.broker_id = ? OR (? > 0 AND aw.broker_id = ?))
          AND LOWER(COALESCE(aw.workflow_status, '')) = 'finished_approved'
        ORDER BY COALESCE(aw.work_finished_at, aw.updated_at) DESC
        LIMIT 20
    ");
    if ($workflowStmt) {
        $workflowStmt->bind_param('iii', $broker_id, $fallback_broker_id, $fallback_broker_id);
        $workflowStmt->execute();
        $workflowResult = $workflowStmt->get_result();
        while ($row = $workflowResult->fetch_assoc()) {
            $finishedAt = (string)($row['finished_at'] ?? '');
            $visits[] = [
                'id' => 'wf_' . (int)$row['property_id'],
                'customer_id' => null,
                'property_id' => (int)$row['property_id'],
                'appointment_date' => $finishedAt !== '' ? substr($finishedAt, 0, 10) : date('Y-m-d'),
                'appointment_time' => $finishedAt !== '' ? substr($finishedAt, 11, 8) : '00:00:00',
                'status' => 'completed',
                'message' => 'Work finished and approved by admin',
                'created_at' => $finishedAt !== '' ? $finishedAt : date('Y-m-d H:i:s'),
                'firstname' => null,
                'lastname' => null,
                'email' => null,
                'phone' => '',
                'property_title' => (string)($row['property_title'] ?? 'Property'),
                'property_city' => (string)($row['property_city'] ?? ''),
                'customer_name' => 'N/A'
            ];
        }
        $workflowStmt->close();
    }

    usort($visits, static function ($a, $b) {
        $aDate = strtotime((string)($a['appointment_date'] ?? '1970-01-01') . ' ' . (string)($a['appointment_time'] ?? '00:00:00'));
        $bDate = strtotime((string)($b['appointment_date'] ?? '1970-01-01') . ' ' . (string)($b['appointment_time'] ?? '00:00:00'));
        return $bDate <=> $aDate;
    });
    if (count($visits) > 50) {
        $visits = array_slice($visits, 0, 50);
    }

    echo json_encode([
        'success' => true,
        'appointments' => $visits,
        'count' => count($visits)
    ]);

} catch (Exception $e) {
    echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
}
?>
