<?php
ob_start();

ini_set('display_errors', 0);
ini_set('log_errors', 1);
ini_set('error_log', __DIR__ . '/../logs/php_errors.log');
error_reporting(E_ALL);

header('Content-Type: application/json');
$response = ['success' => false, 'data' => [], 'message' => 'ERROR DESCONOCIDO'];

try {
    require_once 'db_connection.php';
    require_once __DIR__ . '/../files/guardiankey.php'; 

    $data = [];
    $today = date('Y-m-d');

    function sanitize_output($value) {
        return $value !== null ? htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8') : null;
    }

    function process_monthly_data(&$monthly_data_ref, $db_rows, $metrics_map) {
        foreach ($db_rows as $row) {
            $month_key = $row['month_key'];
            if (!isset($monthly_data_ref[$month_key])) {
                $monthly_data_ref[$month_key] = [];
            }
            foreach ($metrics_map as $db_col => $data_key) {
                if (isset($row[$db_col])) {
                    $monthly_data_ref[$month_key][$data_key] = ($data_key === 'facturacion') ? (float)$row[$db_col] : (int)$row[$db_col];
                }
            }
        }
    }

    $tracking_statuses = ['Cotizacion Solicitada', 'En Seguimiento','Presentar Solución', 'Introducir Servicio', 'Seguimiento Post Envío', 'Negociación Final'];
    $tracking_placeholders = implode(',', array_fill(0, count($tracking_statuses), '?'));

    $overdue_invoice_statuses = ['pendiente', 'vencida', 'atrasada'];
    $overdue_invoice_placeholders_sql = "'" . implode("','", array_map('addslashes', $overdue_invoice_statuses)) . "'";

    $pending_estimate_statuses = ['generado', 'enviado']; 
    $pending_estimate_placeholders_sql = "'" . implode("','", array_map('addslashes', $pending_estimate_statuses)) . "'";

    $sql_counts = "
        SELECT
            (SELECT COUNT(id) FROM unique_appointments WHERE status IN ('pending', 'confirmed') AND appointment_date >= ?) AS pendingAppointments,
            (SELECT COUNT(id) FROM leads WHERE status IN ($tracking_placeholders)) AS trackingProspects,
            (SELECT COUNT(id) FROM tasks WHERE status = 'pending' AND due_date <= ?) AS pendingTasks,
            (SELECT COUNT(id) FROM estimates WHERE status IN ($pending_estimate_placeholders_sql)) AS pendingEstimates,
            (SELECT COUNT(id) FROM invoices WHERE status IN ($overdue_invoice_placeholders_sql) AND (due_date <= ? OR status = 'atrasada')) AS overdueInvoices
    ";
    $stmt_counts = $pdo->prepare($sql_counts);

    $count_params = [$today]; 
    $count_params = array_merge($count_params, $tracking_statuses); 
    $count_params[] = $today; 
    $count_params[] = $today; 

    $stmt_counts->execute($count_params);

    $counts = $stmt_counts->fetch(PDO::FETCH_ASSOC);
    if ($counts) {
      $data = array_merge($data, $counts);
    } else {
       $data = array_merge($data, [
          'pendingAppointments' => 0, 'trackingProspects' => 0, 'pendingTasks' => 0,
          'pendingEstimates' => 0, 'overdueInvoices' => 0
       ]);
       error_log("dashboard-read.php: La consulta de conteos no devolvió resultados.");
    }

    $stmt_appointments = $pdo->prepare("SELECT ua.appointment_date AS fecha, ua.appointment_time AS hora, ua.status, COALESCE( NULLIF(TRIM(CONCAT_WS(' ', c.first_name, c.last_name)), ''), NULLIF(TRIM(CONCAT_WS(' ', l.first_name, l.last_name)), ''), 'Cita sin nombre') 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]);
    $appointments = [];
    while ($row = $stmt_appointments->fetch(PDO::FETCH_ASSOC)) {
        $row['associated_name'] = sanitize_output($row['associated_name']);
        $row['servicio'] = sanitize_output($row['servicio']);
        $appointments[] = $row;
    }
    $data['upcomingAppointments'] = $appointments;

    $stmt_prospects_list = $pdo->prepare("SELECT id, first_name, last_name, email, phone, mobile, status, created_at FROM leads WHERE status IN ($tracking_placeholders) ORDER BY created_at DESC LIMIT 5");
    $stmt_prospects_list->execute($tracking_statuses);
    $prospects_list = [];
    $stmt_prospects_list->execute($tracking_statuses);
    $prospects_list = [];
    while ($row = $stmt_prospects_list->fetch(PDO::FETCH_ASSOC)) {
         $prospects_list[] = [
             'id' => $row['id'], 
             'first_name' => sanitize_output($row['first_name']),
             'last_name' => sanitize_output($row['last_name']),
             'email' => sanitize_output($row['email']), 
             'phone' => sanitize_output($row['phone']),
             'mobile' => sanitize_output($row['mobile']),
             'estado' => htmlspecialchars_decode($row['status'] ?? '', ENT_QUOTES), 
             'created_at' => $row['created_at'] 
         ];
    }
    $data['trackingProspectsList'] = $prospects_list;

    $sql_pending_estimates = "SELECT e.external_id AS id, e.estimate_date AS fecha, e.total_amount AS monto, e.status AS estado, COALESCE( NULLIF(TRIM(CONCAT_WS(' ', c.first_name, c.last_name)), ''), NULLIF(TRIM(CONCAT_WS(' ', l.first_name, l.last_name)), ''), 'Estimado sin nombre') AS associated_name FROM estimates e LEFT JOIN clients c ON e.client_id = c.id LEFT JOIN leads l ON e.lead_id = l.id WHERE e.status IN ($pending_estimate_placeholders_sql) ORDER BY e.estimate_date ASC LIMIT 5";
    $stmt_pending_estimates = $pdo->prepare($sql_pending_estimates);
    $stmt_pending_estimates->execute();
    $pending_estimates_list = [];
    while ($row = $stmt_pending_estimates->fetch(PDO::FETCH_ASSOC)) {
        $row['associated_name'] = sanitize_output($row['associated_name']);
        $row['estado'] = sanitize_output($row['estado']); 
        $pending_estimates_list[] = $row;
    }
    $data['pendingEstimatesList'] = $pending_estimates_list;

    $sql_overdue_invoices = "
        SELECT
            i.external_id AS id, i.invoice_date AS fecha, i.due_date AS vencimiento,
            i.total_amount AS monto, i.status AS estado,
            COALESCE( NULLIF(TRIM(CONCAT_WS(' ', c.first_name, c.last_name)), ''), NULLIF(TRIM(CONCAT_WS(' ', l.first_name, l.last_name)), ''), 'Factura sin nombre') AS associated_name
        FROM invoices i
        LEFT JOIN clients c ON i.client_id = c.id
        LEFT JOIN leads l ON i.lead_id = l.id
        WHERE i.status IN ($overdue_invoice_placeholders_sql) 
          AND (i.status = 'atrasada' OR i.due_date <= ?) 
        ORDER BY i.due_date ASC
        LIMIT 5
    ";
    $stmt_overdue_invoices = $pdo->prepare($sql_overdue_invoices);
    $stmt_overdue_invoices->execute([$today]); 

    $overdue_invoices_list = [];
    while ($row = $stmt_overdue_invoices->fetch(PDO::FETCH_ASSOC)) {
        $row['associated_name'] = sanitize_output($row['associated_name']);
         $row['estado'] = sanitize_output($row['estado']); 
        $overdue_invoices_list[] = $row;
    }
    $data['overdueInvoicesList'] = $overdue_invoices_list;


    $stmt_all_tasks = $pdo->query("SELECT t.*, COALESCE(TRIM(CONCAT_WS(' ', c.first_name, c.last_name)), c.email, c.phone) AS client_name, COALESCE(TRIM(CONCAT_WS(' ', l.first_name, l.last_name)), l.email, l.phone) 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");
    $tasks = [];
    if ($stmt_all_tasks) {
        while ($row = $stmt_all_tasks->fetch(PDO::FETCH_ASSOC)) {
            $row = array_map('sanitize_output', $row); 
            $tasks[] = $row;
        }
    } else {
         error_log("dashboard-read.php: Falló la consulta de todas las tareas.");
    }
    $data['allTasks'] = $tasks;

    $data['monthly_data'] = [];
    $leads_raw = $pdo->query("SELECT DATE_FORMAT(created_at, '%Y-%m') AS month_key, COUNT(id) AS count FROM leads GROUP BY month_key");
    if($leads_raw) process_monthly_data($data['monthly_data'], $leads_raw->fetchAll(PDO::FETCH_ASSOC), ['count' => 'leads']);
    $clients_raw = $pdo->query("SELECT DATE_FORMAT(created_at, '%Y-%m') AS month_key, COUNT(id) AS count FROM clients GROUP BY month_key");
     if($clients_raw) process_monthly_data($data['monthly_data'], $clients_raw->fetchAll(PDO::FETCH_ASSOC), ['count' => 'clientes']);
    $services_sold_raw = $pdo->query("SELECT DATE_FORMAT(i.invoice_date, '%Y-%m') AS month_key, COUNT(ii.id) AS services_count FROM invoices i JOIN invoice_items ii ON i.id = ii.invoice_id WHERE LOWER(i.status) = 'pagada' GROUP BY month_key");
    if($services_sold_raw) process_monthly_data($data['monthly_data'], $services_sold_raw->fetchAll(PDO::FETCH_ASSOC), ['services_count' => 'serviciosVendidos']);
    $facturacion_raw = $pdo->query("SELECT DATE_FORMAT(invoice_date, '%Y-%m') AS month_key, SUM(total_amount) AS total_sum, COUNT(id) AS count FROM invoices WHERE LOWER(status) = 'pagada' GROUP BY month_key");
    if($facturacion_raw) process_monthly_data($data['monthly_data'], $facturacion_raw->fetchAll(PDO::FETCH_ASSOC), ['total_sum' => 'facturacion', 'count' => 'facturasPagadas']);

    $response['success'] = true;
    $response['data'] = $data;
    $response['message'] = 'DATOS CARGADOS';

} catch (PDOException $e) {
    error_log("Error PDO en dashboard-read.php: " . $e->getMessage() . " SQL Intentado: " . ($sql_counts ?? $sql_overdue_invoices ?? 'N/A'));
    $response['message'] = 'ERROR DE BASE DE DATOS';
} catch (Exception $e) {
    error_log("Error general en dashboard-read.php: " . $e->getMessage());
    $response['message'] = 'ERROR INESPERADO';
}

if (ob_get_level() > 0) {
    ob_end_clean();
}
echo json_encode($response);
exit;
?>