<?php

function getUserFullName($pdo, $user_id = 1) {
    try {
        $stmt = $pdo->prepare("SELECT full_name FROM users WHERE id = :user_id");
        $stmt->execute([':user_id' => $user_id]);
        $result = $stmt->fetchColumn();
        return $result ? $result : 'Usuario'; 
    } catch (PDOException $e) {
        error_log("Error al obtener nombre de usuario: " . $e->getMessage());
        return 'Usuario';
    }
}


function fetchDailySummary($pdo) {
    $summary = [
        'appointments' => [],
        'tasks' => [],
        'invoices_due_today' => 0,
        'invoices_overdue' => 0,
        'follow_up_clients' => 0,
        'follow_up_list' => [],
        'strategic_route' => "",
        'estimates_pending' => 0,
        'invoices_sent_pending' => 0,
        'today_income' => 0.00,
        'today_expenses' => 0.00,
        'active_complaints' => [],
        'active_projects' => [],
        'today_route' => null
    ];

    $today = date('Y-m-d');
    $day_name_es = date('l');
    $day_map = [
        'Monday' => 'Lunes',
        'Tuesday' => 'Martes',
        'Wednesday' => 'Miércoles',
        'Thursday' => 'Jueves',
        'Friday' => 'Viernes',
        'Saturday' => 'Sábado',
        'Sunday' => 'Domingo'
    ];
    $route_day = $day_map[$day_name_es] ?? $day_name_es;

    $week_number = (int)date('W');
    $is_even_week = $week_number % 2 == 0;
    $target_week_number = 1;

    try {
        $stmt_agenda = $pdo->prepare("
            SELECT 
                ua.id, 
                ua.appointment_time, 
                s.name as service_name,
                CONCAT(c.first_name, ' ', c.last_name) as client_name
            FROM unique_appointments ua
            LEFT JOIN clients c ON ua.client_id = c.id
            LEFT JOIN services s ON ua.service_id = s.id
            WHERE ua.appointment_date = :today AND ua.status IN ('confirmed', 'pending')
            ORDER BY ua.appointment_time ASC
        ");
        $stmt_agenda->execute([':today' => $today]);
        $appointments = $stmt_agenda->fetchAll(PDO::FETCH_ASSOC);

        foreach ($appointments as $appt) {
            $time = date('h:i A', strtotime($appt['appointment_time']));
            $title = "Cita Con " . htmlspecialchars($appt['client_name']) . " (" . htmlspecialchars($appt['service_name']) . ")";
            $summary['appointments'][] = ['id' => $appt['id'], 'time' => $time, 'title' => $title];
        }

        $stmt_tasks = $pdo->prepare("
            SELECT id, title, priority
            FROM tasks 
            WHERE DATE(due_date) = :today AND status = 'pending' 
            ORDER BY FIELD(priority, 'Alta', 'Media', 'Baja'), due_date ASC
        ");
        $stmt_tasks->execute([':today' => $today]);
        $tasks_from_db = $stmt_tasks->fetchAll(PDO::FETCH_ASSOC);

        $summary['tasks'] = array_map(function($task) {
            $task['is_emergency'] = ($task['priority'] === 'Alta');
            return $task;
        }, $tasks_from_db);

        
        $invoice_pending_statuses = ['pending', 'enviada', 'generado', 'atrasada']; 
        $invoice_placeholders = implode(',', array_fill(0, count($invoice_pending_statuses), '?'));

        $stmt_due_today = $pdo->prepare("SELECT COUNT(id) FROM invoices WHERE DATE(due_date) = CURDATE() AND status IN ($invoice_placeholders)");
        $stmt_due_today->execute($invoice_pending_statuses);
        $summary['invoices_due_today'] = (int)$stmt_due_today->fetchColumn();

        $stmt_overdue = $pdo->prepare("SELECT COUNT(id) FROM invoices WHERE (due_date < CURDATE() OR status = 'atrasada') AND status IN ($invoice_placeholders)");
        $stmt_overdue->execute($invoice_pending_statuses);
        $summary['invoices_overdue'] = (int)$stmt_overdue->fetchColumn();


        $estimate_pending_statuses = ['enviado', 'generado']; 
        $estimate_placeholders = implode(',', array_fill(0, count($estimate_pending_statuses), '?'));
        
        $stmt_estimates = $pdo->prepare("SELECT COUNT(id) FROM estimates WHERE status IN ($estimate_placeholders)");
        $stmt_estimates->execute($estimate_pending_statuses);
        $summary['estimates_pending'] = (int)$stmt_estimates->fetchColumn(); 

        $stmt_invoices_sent = $pdo->prepare("SELECT COUNT(id) FROM invoices WHERE status IN ($invoice_pending_statuses) AND (due_date >= CURDATE() OR due_date IS NULL)");
        $stmt_invoices_sent->execute($invoice_pending_statuses);
        $summary['invoices_sent_pending'] = (int)$stmt_invoices_sent->fetchColumn();
        
        $active_lead_statuses = [
            'En Seguimiento', 'Presentar Solución', 'Introducir Servicio', 
            'Cotizacion Solicitada', 'Seguimiento Post Envío', 'Seguimiento Recordatorio', 'Negociación Final'
        ];
        $placeholders = implode(',', array_fill(0, count($active_lead_statuses), '?'));

        $stmt_leads = $pdo->prepare("
            SELECT 
                CONCAT(first_name, ' ', last_name) as name, 
                email, 
                phone, 
                mobile, 
                id,
                status
            FROM leads 
            WHERE status IN ($placeholders)
            ORDER BY created_at ASC 
            LIMIT 5
        ");
        $stmt_leads->execute($active_lead_statuses);
        $summary['follow_up_list'] = $stmt_leads->fetchAll(PDO::FETCH_ASSOC);
        $summary['follow_up_clients'] = count($summary['follow_up_list']); 
        
        if (!empty($summary['follow_up_list'])) {
            foreach ($summary['follow_up_list'] as $lead) {
                $status_label = match($lead['status']) {
                    'Cotizacion Solicitada', 'Seguimiento Post Envío', 'Seguimiento Recordatorio', 'Negociación Final' => ' (COTIZACIÓN)',
                    default => ' (LEAD)'
                };
            }
        }
        
        $stmt_route = $pdo->prepare("
            SELECT 
                r.id as route_id, 
                r.route_name,
                c.id as client_id,
                CONCAT(c.first_name, ' ', c.last_name) as client_name,
                c.street_address,
                c.city
            FROM routes r
            JOIN route_clients rc ON r.id = rc.route_id
            JOIN clients c ON rc.client_id = c.id
            WHERE r.route_day = :route_day 
              AND r.week_number = :week_number
            ORDER BY r.sort_order ASC, rc.order_index ASC
            LIMIT 10
        ");
        $stmt_route->execute([':route_day' => $route_day, ':week_number' => $target_week_number]);
        $route_clients = $stmt_route->fetchAll(PDO::FETCH_ASSOC);

        if (!empty($route_clients)) {
            $route_name = htmlspecialchars($route_clients[0]['route_name']);
            $summary['today_route'] = [
                'name' => $route_name,
                'clients' => $route_clients,
            ];
        }

        $stmt_complaints = $pdo->prepare("
            SELECT 
                c.id, 
                c.title, 
                cl.first_name, 
                cl.last_name, 
                c.status
            FROM complaints c
            LEFT JOIN clients cl ON c.client_id = cl.id
            WHERE c.status IN ('Recibido', 'Resolviendo', 'Acción Requerida')
            ORDER BY c.updated_at DESC
            LIMIT 3
        ");
        $stmt_complaints->execute();
        $summary['active_complaints'] = $stmt_complaints->fetchAll(PDO::FETCH_ASSOC);

        $stmt_projects = $pdo->prepare("
            SELECT 
                p.name, 
                CONCAT(cl.first_name, ' ', cl.last_name) as client_name, 
                p.due_date,
                p.id
            FROM projects p
            LEFT JOIN clients cl ON p.client_id = cl.id
            WHERE p.status IN ('Aprobado', 'En Progreso')
            ORDER BY p.due_date ASC
            LIMIT 3
        ");
        $stmt_projects->execute();
        $summary['active_projects'] = $stmt_projects->fetchAll(PDO::FETCH_ASSOC);


        $stmt_income = $pdo->prepare("SELECT SUM(amount) FROM finances WHERE type = 'ingreso' AND transaction_date = :today");
        $stmt_income->execute([':today' => $today]);
        $summary['today_income'] = (float)$stmt_income->fetchColumn() ?: 0.00;
        $stmt_expenses = $pdo->prepare("SELECT SUM(amount) FROM finances WHERE type = 'gasto' AND transaction_date = :today");
        $stmt_expenses->execute([':today' => $today]);
        $summary['today_expenses'] = (float)$stmt_expenses->fetchColumn() ?: 0.00;


        $has_major_work = !empty($summary['appointments']) || count($summary['tasks']) > 2 || !empty($summary['today_route']);

        if (!$has_major_work) {
            $proactive_tasks = [
                ['title' => 'Llamar a 2 clientes antiguos para ofrecer el servicio nuevamente.', 'is_emergency' => true],
                ['title' => 'Publicar 1 Reel en redes sociales mostrando un resultado "antes y después".', 'is_emergency' => true],
                ['title' => 'Revisar el estado de tus herramientas e insumos de trabajo.', 'is_emergency' => false],
                ['title' => 'Enviar un mensaje de agradecimiento personalizado a tus últimos 3 clientes.', 'is_emergency' => true],
                ['title' => 'Dedicar 20 minutos a <b>ver 3 ideas</b> de cómo otros negocios (como el tuyo) consiguen clientes.', 'is_emergency' => false],
                ['title' => 'Pedir una reseña (review) a un cliente reciente que quedó satisfecho.', 'is_emergency' => true],
                ['title' => '<b>Subir las mejores fotos</b> de tu último trabajo a tus redes sociales (Facebook o Instagram).', 'is_emergency' => false]
            ];
            shuffle($proactive_tasks); 
            
            $summary['tasks'][] = array_pop($proactive_tasks);
            $summary['tasks'][] = array_pop($proactive_tasks);

            $strategic_routes = [
                "Enfócate en <b>CONSEGUIR MÁS CLIENTES</b>. Tu meta: llenar tu agenda de citas para la próxima semana.",
                "Tu objetivo hoy: <b>ORGANIZAR MEJOR TU DÍA</b>. Un negocio ordenado te da más tranquilidad.",
                "Enfócate en <b>TUS CLIENTES ACTUALES</b>. Un cliente contento es un cliente que vuelve... y te recomienda.",
                "Tu objetivo hoy: <b>QUE TE VEAN MÁS EN INTERNET</b>. Aumenta tu presencia online para atraer nuevos clientes."
            ];
            shuffle($strategic_routes); 
            $summary['strategic_route'] = $strategic_routes[0];
            
        } else {
            $summary['strategic_route'] = "Hoy Es Un Día De Ejecución Enfocada. ¡Prioriza la Ruta y las Tareas Clave!";
        }

    } catch (PDOException $e) {
        error_log("Error al cargar el resumen diario: " . $e->getMessage());
    }

    return $summary;
}
?>