<?php
// db/dashboard-read.php

// Asegura que la salida sea JSON.
header('Content-Type: application/json');

// Incluye la conexión a la base de datos.
include_once 'db_connection.php';

// Array de respuesta por defecto.
$response = ['success' => false, 'data' => [], 'message' => 'No se pudieron cargar los datos.'];

try {
    // Array para almacenar todos los datos del dashboard.
    $data = [];
    $today = date('Y-m-d');

    // --- 1. Contar Citas Pendientes ---
    $stmt_pending = $pdo->prepare("SELECT COUNT(id) FROM unique_appointments WHERE status IN ('pending', 'confirmed') AND appointment_date >= ?");
    $stmt_pending->execute([$today]);
    $data['pendingAppointments'] = (int)$stmt_pending->fetchColumn();

    // --- 2. Contar Prospectos en Seguimiento ---
    $tracking_statuses = ['Cotizacion enviada', 'Seguimiento', 'Recordatorio de seguimiento', 'Ultimo recordatorio'];
    $placeholders = implode(',', array_fill(0, count($tracking_statuses), '?'));
    $stmt_prospects_count = $pdo->prepare("SELECT COUNT(id) FROM leads WHERE status IN ($placeholders)");
    $stmt_prospects_count->execute($tracking_statuses);
    $data['trackingProspects'] = (int)$stmt_prospects_count->fetchColumn();

    // --- 3. Contar Tareas Pendientes ---
    $stmt_tasks_count = $pdo->prepare("SELECT COUNT(id) FROM tasks WHERE status = 'pending' AND due_date <= ?");
    $stmt_tasks_count->execute([$today]);
    $data['pendingTasks'] = (int)$stmt_tasks_count->fetchColumn();

    // --- 4. Obtener Próximas Citas Confirmadas (VERSIÓN FINAL CORREGIDA) ---
    $stmt_appointments = $pdo->prepare("
        SELECT
            ua.id,
            ua.appointment_date AS fecha,
            ua.appointment_time AS hora,
            ua.status,
            -- ✅ ESTA ES LA CORRECCIÓN CLAVE:
            -- Usa COALESCE para tomar el primer nombre que no sea nulo.
            -- Usa CONCAT_WS para evitar errores si el nombre o apellido están vacíos.
            COALESCE(
                NULLIF(TRIM(CONCAT_WS(' ', c.first_name, c.last_name)), ''), 
                NULLIF(TRIM(CONCAT_WS(' ', l.first_name, l.last_name)), ''),
                'Cita sin nombre asignado'
            ) AS associated_name,
            IFNULL(ua.notes, 'Cita Programada') AS servicio
        FROM unique_appointments ua
        LEFT JOIN clients c ON ua.client_id = c.id
        LEFT JOIN leads l ON ua.lead_id = l.id
        WHERE ua.status = 'confirmed' AND ua.appointment_date >= ?
        ORDER BY ua.appointment_date ASC, ua.appointment_time ASC
        LIMIT 5
    ");
    $stmt_appointments->execute([$today]);
    $data['upcomingAppointments'] = $stmt_appointments->fetchAll(PDO::FETCH_ASSOC);

    // --- 5. Obtener Lista de Prospectos en Seguimiento ---
    $stmt_prospects_list = $pdo->prepare("SELECT id, first_name, last_name, email, phone, mobile, status, created_at FROM leads WHERE status IN ($placeholders) ORDER BY created_at DESC LIMIT 5");
    $stmt_prospects_list->execute($tracking_statuses);
    $prospects_list = [];
    foreach ($stmt_prospects_list->fetchAll(PDO::FETCH_ASSOC) as $prospect) {
        $prospect['estado'] = $prospect['status'];
        unset($prospect['status']);
        $prospects_list[] = $prospect;
    }
    $data['trackingProspectsList'] = $prospects_list;

    // --- 6. Obtener Todas las Tareas ---
    $stmt_all_tasks = $pdo->query("
        SELECT 
            t.id, t.title, t.description, t.due_date, t.priority, t.status, t.client_id, t.lead_id,
            CONCAT(c.first_name, ' ', c.last_name) AS client_name,
            CONCAT(l.first_name, ' ', l.last_name) AS prospect_name
        FROM tasks t
        LEFT JOIN clients c ON t.client_id = c.id
        LEFT JOIN leads l ON t.lead_id = l.id
        ORDER BY t.status ASC, t.due_date ASC
    ");
    $data['allTasks'] = $stmt_all_tasks->fetchAll(PDO::FETCH_ASSOC);

    // Respuesta final exitosa.
    $response['success'] = true;
    $response['data'] = $data;

} catch (PDOException $e) {
    // Manejo de errores de base de datos.
    $response['message'] = 'Error de Base de Datos al cargar dashboard: ' . $e->getMessage();
    error_log($response['message']); 
}

// Imprimir la respuesta final en formato JSON.
echo json_encode($response);
?>