<?php

declare(strict_types=1);

// Asegúrate de que esta ruta apunte correctamente a tu archivo api.php
require_once __DIR__ . '/../core/api.php';

function fetchDailySummary($pdo, $user_id) {
    $summary = [
        'appointments' => [],
        'tasks' => [],
        // Widget Counters
        'invoices_overdue' => 0, 
        'quotes_pending' => 0, 
        'follow_up_clients' => 0,
        'active_complaints_count' => 0,
        'invoices_sent_pending' => 0, 
        // Detailed Lists for the Checklist
        'invoices_overdue_list' => [],
        'quotes_pending_list' => [],
        'follow_up_list' => [],
        'active_complaints' => [],
        'active_projects' => [],
        
        'today_route' => null,
        'strategic_route' => '',
        'today_income' => 0.00,
        'today_expenses' => 0.00
    ];

    $todayDate = date('Y-m-d');
    
    // Day and Week Configuration (Kept in Spanish for DB mapping: routes.route_day)
    $days = [
        'Monday' => 'Lunes', 'Tuesday' => 'Martes', 'Wednesday' => 'Miércoles', 
        'Thursday' => 'Jueves', 'Friday' => 'Viernes', 'Saturday' => 'Sábado', 'Sunday' => 'Domingo'
    ];
    $currentDayName = $days[date('l')] ?? '';
    $routeWeekNumber = (int)date('W') % 2 === 0 ? 2 : 1; 

    try {
        // =================================================================
        // 1. FINANCE AND OPERATION
        // =================================================================
        $params = [':user_id' => $user_id, ':today' => $todayDate];

        // 1.1 Overdue Invoices
        $sqlOverdue = "
            SELECT i.id, i.external_id, i.total_amount, i.due_date,
                   COALESCE(CONCAT(c.first_name, ' ', c.last_name), 'Deleted Client') as client_name
            FROM invoices i
            LEFT JOIN clients c ON i.client_id = c.id
            WHERE i.user_id = :user_id 
              AND i.status NOT IN ('pagada') 
              AND i.due_date < :today
            ORDER BY i.due_date ASC
        ";
        $stmtOverdue = $pdo->prepare($sqlOverdue);
        $stmtOverdue->execute($params);
        $summary['invoices_overdue_list'] = $stmtOverdue->fetchAll(PDO::FETCH_ASSOC);
        $summary['invoices_overdue'] = count($summary['invoices_overdue_list']);

        // 1.2 Pending Sent Invoices
        $stmtPending = $pdo->prepare("SELECT COUNT(*) FROM invoices WHERE user_id = :user_id AND status IN ('enviado', 'enviada', 'generado') AND (due_date > :today OR due_date IS NULL)");
        $stmtPending->execute($params);
        $summary['invoices_sent_pending'] = (int)$stmtPending->fetchColumn();

        // 1.3 Pending Quotes (Estimates) - Using 'quotes' table
        $sqlEstimates = "
            SELECT q.id, q.external_id, q.total_amount,
                   COALESCE(CONCAT(c.first_name, ' ', c.last_name), 'Prospect') as client_name
            FROM quotes q
            LEFT JOIN clients c ON q.client_id = c.id
            WHERE q.user_id = :user_id 
              AND q.status IN ('sent', 'generated', 'approved') 
        ";
        $stmtEstimates = $pdo->prepare($sqlEstimates);
        $stmtEstimates->execute([':user_id' => $user_id]);
        $summary['quotes_pending_list'] = $stmtEstimates->fetchAll(PDO::FETCH_ASSOC); 
        $summary['quotes_pending'] = count($summary['quotes_pending_list']); 

        // 1.4 Today's Income/Expenses
        $stmtFinances = $pdo->prepare("
            SELECT 
                COALESCE(SUM(CASE WHEN transaction_type = 'income' THEN amount ELSE 0 END), 0) as income_today,
                COALESCE(SUM(CASE WHEN transaction_type = 'expense' THEN amount ELSE 0 END), 0) as expense_today
            FROM transactions 
            WHERE user_id = :user_id AND transaction_date = :today
        ");
        $stmtFinances->execute($params);
        $finances = $stmtFinances->fetch(PDO::FETCH_ASSOC);
        $summary['today_income'] = (float)$finances['income_today'];
        $summary['today_expenses'] = (float)$finances['expense_today'];

        // =================================================================
        // 2. APPOINTMENTS
        // =================================================================
        $stmtAppt = $pdo->prepare("
            SELECT 
                ua.id, 
                TIME_FORMAT(ua.appointment_time, '%h:%i %p') as time, 
                COALESCE(s.name, 'General Appointment') as service_name,
                COALESCE(CONCAT(c.first_name, ' ', c.last_name), CONCAT(l.first_name, ' ', l.last_name)) as client_name,
                COALESCE(c.city, l.city) as city
            FROM unique_appointments ua
            LEFT JOIN clients c ON ua.client_id = c.id
            LEFT JOIN leads l ON ua.lead_id = l.id
            LEFT JOIN services s ON ua.service_id = s.id
            WHERE ua.user_id = :user_id AND ua.appointment_date = :today 
            AND ua.status IN ('confirmed', 'pending')
            ORDER BY ua.appointment_time ASC
        ");
        $stmtAppt->execute([':user_id' => $user_id, ':today' => $todayDate]);
        $rawAppts = $stmtAppt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($rawAppts as $appt) {
            $summary['appointments'][] = [
                'id' => $appt['id'],
                'time' => $appt['time'],
                'title' => "Appointment: " . $appt['client_name'] . " (" . $appt['service_name'] . ")"
            ];
        }

        // =================================================================
        // 3. DAILY ROUTE
        // =================================================================
        $stmtRoute = $pdo->prepare("
            SELECT r.id, r.route_name 
            FROM routes r
            WHERE r.user_id = :user_id AND r.route_day = :day_name AND r.week_number = :week_num AND r.is_active = 1
            LIMIT 1
        ");
        $stmtRoute->execute([':user_id' => $user_id, ':day_name' => $currentDayName, ':week_num' => $routeWeekNumber]);
        $route = $stmtRoute->fetch(PDO::FETCH_ASSOC);

        if ($route) {
            $stmtClients = $pdo->prepare("
                SELECT 
                    c.id, 
                    CONCAT(c.first_name, ' ', c.last_name) as client_name,
                    c.street_address,
                    c.city
                FROM route_clients rc
                JOIN clients c ON rc.client_id = c.id
                WHERE rc.route_id = :route_id
                ORDER BY rc.order_index ASC
            ");
            $stmtClients->execute([':route_id' => $route['id']]);
            $summary['today_route'] = [
                'name' => $route['route_name'],
                'clients' => $stmtClients->fetchAll(PDO::FETCH_ASSOC)
            ];
        }

        // =================================================================
        // 4. PENDING TASKS
        // =================================================================
        $stmtTasks = $pdo->prepare("
            SELECT id, title, priority, due_date
            FROM tasks 
            WHERE user_id = :user_id AND status = 'pending' 
            AND due_date <= :today
            ORDER BY FIELD(priority, 'High', 'Medium', 'Low') ASC, due_date ASC
        ");
        $stmtTasks->execute([':user_id' => $user_id, ':today' => $todayDate]);
        $rawTasks = $stmtTasks->fetchAll(PDO::FETCH_ASSOC);
        
        $summary['tasks'] = array_map(function($t) use ($todayDate) {
            $t['is_emergency'] = ($t['priority'] === 'High' || $t['due_date'] < $todayDate);
            return $t;
        }, $rawTasks);

        // =================================================================
        // 5. FOLLOW-UP LEADS
        // =================================================================
        $stmtLeads = $pdo->prepare("
            SELECT id, CONCAT(first_name, ' ', last_name) as name, phone, mobile, status
            FROM leads 
            WHERE user_id = :user_id AND status IN ('New', 'Contacted', 'Qualified', 'Proposal Sent', 'Negotiation')
            ORDER BY updated_at DESC
            LIMIT 10
        ");
        $stmtLeads->execute([':user_id' => $user_id]);
        $summary['follow_up_list'] = $stmtLeads->fetchAll(PDO::FETCH_ASSOC);
        $summary['follow_up_clients'] = count($summary['follow_up_list']);

        // =================================================================
        // 6. ACTIVE COMPLAINTS
        // =================================================================
        $stmtComp = $pdo->prepare("
            SELECT c.id, c.title, cl.first_name, cl.last_name 
            FROM complaints c
            LEFT JOIN clients cl ON c.client_id = cl.id
            WHERE c.user_id = :user_id AND c.status IN ('Recibido', 'Resolviendo', 'Acción Requerida')
            ORDER BY c.created_at ASC
        ");
        $stmtComp->execute([':user_id' => $user_id]);
        $summary['active_complaints'] = $stmtComp->fetchAll(PDO::FETCH_ASSOC);
        $summary['active_complaints_count'] = count($summary['active_complaints']);

        // =================================================================
        // 7. ACTIVE PROJECTS
        // =================================================================
        $stmtProj = $pdo->prepare("
            SELECT p.id, p.name, p.due_date, CONCAT(cl.first_name, ' ', cl.last_name) as client_name
            FROM projects p
            LEFT JOIN clients cl ON p.client_id = cl.id
            WHERE p.user_id = :user_id AND p.status IN ('In Process', 'Approved') 
            ORDER BY p.due_date ASC
            LIMIT 5
        ");
        $stmtProj->execute([':user_id' => $user_id]);
        $summary['active_projects'] = $stmtProj->fetchAll(PDO::FETCH_ASSOC);

    } catch (Exception $e) {
        error_log("Critical Error in fetchDailySummary: " . $e->getMessage());
    }

    // =================================================================
    // STRATEGIC LOGIC
    // =================================================================
    $has_major_work = !empty($summary['appointments']) || 
                      count($summary['tasks']) > 0 || 
                      !empty($summary['today_route']) || 
                      !empty($summary['active_complaints']) || 
                      !empty($summary['invoices_overdue_list']) || 
                      !empty($summary['quotes_pending_list']);

    if (!$has_major_work) {
        $proactive_tasks = [
            ['title' => 'CRECIMIENTO: Llama a 3 clientes antiguos inactivos para ofrecer una promoción de reactivación.', 'is_emergency' => true],
            ['title' => 'MARKETING: Graba y sube 1 Historia o Reel mostrando un resultado "Antes y Después".', 'is_emergency' => true],
            ['title' => 'VENTAS: Revisa cotizaciones antiguas (hace 30 días) y haz una llamada de seguimiento final.', 'is_emergency' => true]
        ];
        
        for ($i = 0; $i < 3; $i++) {
            if (isset($proactive_tasks[$i])) {
                $task = $proactive_tasks[$i];
                $task['id'] = 'growth_' . rand(1000, 9999);
                $task['priority'] = 'High';
                $summary['tasks'][] = $task;
            }
        }

        $strategic_routes = [
            "MODO CAZADOR: Tu agenda está ligera. Llena los espacios vacíos.",
            "MODO ORGANIZACIÓN: Aprovecha la calma para organizar tus finanzas.",
            "MODO VISIBILIDAD: Haz marketing hoy."
        ];
        shuffle($strategic_routes); 
        $summary['strategic_route'] = $strategic_routes[0];
        
    } else {
        // Se utilizan placeholders en inglés que serán traducidos en el frontend
        if (!empty($summary['active_complaints'])) {
            $summary['strategic_route'] = "RED_ALERT: Resolve the " . count($summary['active_complaints']) . " open complaints NOW.";
        } elseif (!empty($summary['invoices_overdue_list'])) {
            $summary['strategic_route'] = "CRITICAL_CASHFLOW: " . count($summary['invoices_overdue_list']) . " overdue invoices.";
        } elseif (!empty($summary['quotes_pending_list'])) {
            $summary['strategic_route'] = "SALES_CLOSE: " . count($summary['quotes_pending_list']) . " pending quotes.";
        } elseif (!empty($summary['today_route'])) {
            $summary['strategic_route'] = "EXECUTION: Focus on the assigned route.";
        } elseif (count($summary['tasks']) > 0) {
            $summary['strategic_route'] = "LASER_FOCUS: Finish tasks one by one.";
        } else {
            $summary['strategic_route'] = "MAINTENANCE: Total quality in appointments.";
        }
    }

    return $summary;
}