Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.11% covered (danger)
0.11%
1 / 889
5.26% covered (danger)
5.26%
1 / 19
CRAP
0.00% covered (danger)
0.00%
0 / 1
SendFinanceReport
0.11% covered (danger)
0.11%
1 / 889
5.26% covered (danger)
5.26%
1 / 19
50679.35
0.00% covered (danger)
0.00%
0 / 1
 __construct
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 renderPreview
0.00% covered (danger)
0.00%
0 / 10
0.00% covered (danger)
0.00%
0 / 1
12
 buildPreviewHtml
0.00% covered (danger)
0.00%
0 / 33
0.00% covered (danger)
0.00%
0 / 1
132
 handle
0.00% covered (danger)
0.00%
0 / 111
0.00% covered (danger)
0.00%
0 / 1
1056
 getReportMonth
0.00% covered (danger)
0.00%
0 / 23
0.00% covered (danger)
0.00%
0 / 1
132
 getResumenData
0.00% covered (danger)
0.00%
0 / 152
0.00% covered (danger)
0.00%
0 / 1
1122
 getZenitalSkRegionsForCompany
0.00% covered (danger)
0.00%
0 / 14
0.00% covered (danger)
0.00%
0 / 1
20
 seriesAttributionMap
0.00% covered (danger)
0.00%
0 / 29
0.00% covered (danger)
0.00%
0 / 1
2
 sedeSimplificationMap
0.00% covered (danger)
0.00%
0 / 8
0.00% covered (danger)
0.00%
0 / 1
2
 jorgeRegionWhitelist
0.00% covered (danger)
0.00%
0 / 43
0.00% covered (danger)
0.00%
0 / 1
2
 getZenitalRegionMap
0.00% covered (danger)
0.00%
0 / 1
0.00% covered (danger)
0.00%
0 / 1
2
 getZenitalSucursalesMap
0.00% covered (danger)
0.00%
0 / 1
0.00% covered (danger)
0.00%
0 / 1
2
 aggregateAllReports
0.00% covered (danger)
0.00%
0 / 156
0.00% covered (danger)
0.00%
0 / 1
870
 buildReportHtml
0.00% covered (danger)
0.00%
0 / 85
0.00% covered (danger)
0.00%
0 / 1
756
 buildMonthlyReportHtml
0.00% covered (danger)
0.00%
0 / 81
0.00% covered (danger)
0.00%
0 / 1
650
 buildAllRegionsReportHtml
0.00% covered (danger)
0.00%
0 / 109
0.00% covered (danger)
0.00%
0 / 1
1482
 buildCombinedReportHtml
0.00% covered (danger)
0.00%
0 / 12
0.00% covered (danger)
0.00%
0 / 1
2
 buildCombinedAllRegionsReportHtml
0.00% covered (danger)
0.00%
0 / 13
0.00% covered (danger)
0.00%
0 / 1
6
 extractReportBody
0.00% covered (danger)
0.00%
0 / 7
0.00% covered (danger)
0.00%
0 / 1
12
1<?php
2
3namespace App\Console\Commands;
4
5use App\Jobs\Email\SendFinanceRecipientEmail;
6use App\Models\TblCompanies;
7use App\Models\TblFinanceBudgetAnual;
8use App\Models\TblFinanceMonthConfig;
9use App\Models\TblFinancePrevisionAnual;
10use App\Models\TblFinanceRegions;
11use App\Models\TblFinanceReportRecipients;
12use App\Models\TblFinanceReportSemanal;
13use App\Models\TblFinanceResumenAnual;
14use App\Models\TblFinanceSedes;
15use App\Services\ResultCache;
16use App\Services\SendgridLogger;
17use App\Services\ZenitalCatalog;
18use Carbon\Carbon;
19use Illuminate\Console\Command;
20use Illuminate\Support\Facades\DB;
21use Illuminate\Support\Facades\Log;
22
23class SendFinanceReport extends Command
24{
25    protected $signature = 'finance:send-report {--test : Output to console instead of sending email} {--month= : Override month (1-12)} {--type=weekly : Report type: weekly or monthly}';
26
27    protected $description = 'Send the weekly finance report to all active recipients';
28
29    public function __construct(private ZenitalCatalog $zenital)
30    {
31        parent::__construct();
32    }
33
34    /**
35     * FIRE-1148: returns the all-regions aggregated HTML that `--test` mode
36     * currently echoes to stdout. Used by the "Vista previa" path in
37     * FinanceController::test_report so the preview doesn't have to spin
38     * up the full pipeline via Artisan::call (which iterates every recipient,
39     * builds every per-company body, and is race-prone via Artisan::output()).
40     *
41     * Deliberately calls the EXACT SAME private build methods the cron uses,
42     * so the HTML the user sees in the preview is byte-identical to the
43     * email body the recipients will receive.
44     *
45     * FIRE-1148 follow-up: cached for 5 minutes in the 'finance' domain. The
46     * preview is a snapshot of the all-regions email body — 5 min of stale-
47     * ness is acceptable (no live financial editing happens in this view),
48     * and any sede mutation invalidates the whole 'finance' domain through
49     * ensureSedesExist. Drops a cold ~2.5 s render to a Redis HIT (< 5 ms)
50     * for every request inside the TTL window.
51     */
52    public function renderPreview(?int $month = null, string $type = 'weekly'): string
53    {
54        if ($month !== null) {
55            $year = (int) date('Y');
56        } elseif ($type === 'monthly') {
57            $prev = Carbon::now()->subMonth();
58            $month = $prev->month;
59            $year = $prev->year;
60        } else {
61            $month = (int) date('n');
62            $year = (int) date('Y');
63        }
64
65        $cacheKey = sprintf('finance.preview.%s.%d.%d', $type, $year, $month);
66
67        return ResultCache::rememberKey('finance', $cacheKey, 300, fn (): string => $this->buildPreviewHtml($year, $month, $type));
68    }
69
70    /**
71     * The actual preview-render work. Always called inside the cache wrapper
72     * in renderPreview() — kept as a separate method so the cached path is
73     * trivially auditable and so tests can target the un-cached build.
74     */
75    private function buildPreviewHtml(int $year, int $month, string $type): string
76    {
77        // Same finance-companies discovery the cron uses for the all-regions
78        // recipient branch (see handle() L101-107).
79        $companyIds = TblFinanceSedes::where('is_active', 1)
80            ->pluck('company_id')
81            ->unique()
82            ->toArray();
83
84        $reportsByCompany = [];
85        foreach ($companyIds as $companyId) {
86            $company = TblCompanies::find($companyId);
87            if (! $company) {
88                continue;
89            }
90
91            $monthData = $this->getResumenData($companyId, $year, $month, 'weekly');
92            $ytdData = $type === 'monthly'
93                ? $this->getResumenData($companyId, $year, $month, 'monthly')
94                : null;
95
96            $hasData = ! empty($monthData['actuals_ytd']) || ! empty($monthData['by_sede'])
97                || ($ytdData && (! empty($ytdData['actuals_ytd']) || ! empty($ytdData['by_sede'])));
98            if (! $hasData) {
99                continue;
100            }
101
102            $reportsByCompany[$companyId] = [
103                'company' => $company,
104                'weekly' => $monthData,
105                'monthly' => $ytdData,
106            ];
107        }
108
109        if (empty($reportsByCompany)) {
110            return '<p>No finance data available for current year/month.</p>';
111        }
112
113        $aggregatedData = $this->aggregateAllReports($reportsByCompany, $type);
114        $aggregatedReport = [
115            'company' => (object) ['name' => 'Grupo Fire (Todas las regiones)'],
116            'data' => $aggregatedData,
117        ];
118
119        if ($type === 'monthly') {
120            return $this->buildCombinedAllRegionsReportHtml($aggregatedReport, $year, $month, true);
121        }
122
123        $singleAgg = ['company' => $aggregatedReport['company'], 'data' => $aggregatedData['weekly']];
124
125        return $this->buildAllRegionsReportHtml($singleAgg, $year, $month, 'Report Semanal');
126    }
127
128    private $months = [
129        1 => 'Enero', 2 => 'Febrero', 3 => 'Marzo', 4 => 'Abril',
130        5 => 'Mayo', 6 => 'Junio', 7 => 'Julio', 8 => 'Agosto',
131        9 => 'Septiembre', 10 => 'Octubre', 11 => 'Noviembre', 12 => 'Diciembre',
132    ];
133
134    public function handle(): int
135    {
136        $type = $this->option('type') ?? 'weekly';
137        $isTest = $this->option('test');
138
139        // Determine the target month based on report type:
140        // - weekly: current month (month-to-date view, sent every Saturday)
141        // - monthly: previous month closed (sent on WD+5 of the following month)
142        // Manual --month overrides both defaults (used for preview/testing).
143        if ($this->option('month')) {
144            $month = (int) $this->option('month');
145            $year = (int) date('Y');
146        } elseif ($type === 'monthly') {
147            // Previous month closed
148            $prevMonth = Carbon::now()->subMonth();
149            $month = $prevMonth->month;
150            $year = $prevMonth->year;
151        } else {
152            // Current month for weekly MTD
153            $month = (int) date('n');
154            $year = (int) date('Y');
155        }
156
157        $recipients = TblFinanceReportRecipients::where('is_active', 1)->get();
158
159        if ($recipients->isEmpty()) {
160            $this->info('No active recipients found.');
161
162            return 0;
163        }
164
165        // Berta audit follow-up — Bug C: a single email can appear in
166        // multiple recipient rows (one for "Todas las regiones" + one
167        // per specific company), which makes that person receive several
168        // copies of essentially the same digest. Dedupe by lower-cased
169        // email; when there's a NULL company_id row we keep it (broader
170        // = the all-regions summary the operator usually wants), else
171        // we keep the row with the lowest id.
172        $byEmail = [];
173        foreach ($recipients as $r) {
174            $key = mb_strtolower(trim((string) $r->email));
175            if ($key === '') {
176                continue;
177            }
178            if (! isset($byEmail[$key])) {
179                $byEmail[$key] = $r;
180
181                continue;
182            }
183            $kept = $byEmail[$key];
184            if ($kept->company_id !== null && $r->company_id === null) {
185                $byEmail[$key] = $r;
186            } elseif ($kept->company_id !== null && $r->company_id !== null && $r->id < $kept->id) {
187                $byEmail[$key] = $r;
188            }
189        }
190        $recipients = collect(array_values($byEmail));
191
192        $companyIds = $recipients->pluck('company_id')->filter()->unique()->toArray();
193
194        // If any recipient has NULL company_id (all regions), include all finance companies
195        $hasAllRegionRecipient = $recipients->contains(fn ($r) => $r->company_id === null);
196        if ($hasAllRegionRecipient) {
197            $financeCompanyIds = TblFinanceSedes::where('is_active', 1)
198                ->pluck('company_id')
199                ->unique()
200                ->toArray();
201            $companyIds = array_values(array_unique(array_merge($companyIds, $financeCompanyIds)));
202        }
203
204        $reportsByCompany = [];
205        foreach ($companyIds as $companyId) {
206            $company = TblCompanies::find($companyId);
207            if (! $company) {
208                continue;
209            }
210
211            // weekly = MTD (single month). monthly = single month + YTD
212            $monthData = $this->getResumenData($companyId, $year, $month, 'weekly');
213            $ytdData = $type === 'monthly'
214                ? $this->getResumenData($companyId, $year, $month, 'monthly')
215                : null;
216
217            $hasData = ! empty($monthData['actuals_ytd']) || ! empty($monthData['by_sede'])
218                || ($ytdData && (! empty($ytdData['actuals_ytd']) || ! empty($ytdData['by_sede'])));
219            if (! $hasData) {
220                continue;
221            }
222
223            $reportsByCompany[$companyId] = [
224                'company' => $company,
225                'weekly' => $monthData,
226                'monthly' => $ytdData,
227            ];
228        }
229
230        if (empty($reportsByCompany)) {
231            $this->info('No finance data available for current year/month.');
232
233            return 0;
234        }
235
236        // Separate "all regions" recipients (NULL company_id) from company-specific ones
237        $allRegionRecipients = $recipients->filter(fn ($r) => $r->company_id === null);
238        $companyRecipients = $recipients->filter(fn ($r) => $r->company_id !== null);
239
240        $recipientsByCompany = $companyRecipients->groupBy('company_id');
241
242        $sent = 0;
243        foreach ($recipientsByCompany as $companyId => $companyGroupRecipients) {
244            if (! isset($reportsByCompany[$companyId])) {
245                continue;
246            }
247
248            $report = $reportsByCompany[$companyId];
249            if ($type === 'monthly') {
250                $html = $this->buildCombinedReportHtml($report, $year, $month, true);
251                $subject = "Cierre Facturación - {$report['company']->name} - {$this->months[$month]} {$year}";
252            } else {
253                $singleReport = ['company' => $report['company'], 'data' => $report['weekly']];
254                $html = $this->buildReportHtml($singleReport, $year, $month);
255                $subject = "Reporte Semanal - {$report['company']->name} - {$this->months[$month]} {$year}";
256            }
257
258            if ($isTest) {
259                $this->info("--- Report for {$report['company']->name} (recipients: {$companyGroupRecipients->pluck('email')->implode(', ')}) ---");
260                $sent += $companyGroupRecipients->count();
261
262                continue;
263            }
264
265            foreach ($companyGroupRecipients as $recipient) {
266                // FIRE-1148: dispatched to the 'email' queue. The worker
267                // (FIRE-1147 infra) calls SendGrid, logs to SendgridLogger,
268                // and retries on 4xx/5xx via AbstractSendgridJob. The
269                // command no longer waits for the SendGrid roundtrip per
270                // recipient — the per-cron wall time drops from minutes
271                // to seconds.
272                SendFinanceRecipientEmail::dispatch(
273                    $recipient->email,
274                    $recipient->name,
275                    $subject,
276                    $html,
277                )->onQueue('email');
278                $sent++;
279                $this->info("Dispatched to {$recipient->email}");
280            }
281        }
282
283        // Send aggregated report to "all regions" recipients
284        if ($allRegionRecipients->isNotEmpty() && ! empty($reportsByCompany)) {
285            $aggregatedData = $this->aggregateAllReports($reportsByCompany, $type);
286            $aggregatedReport = ['company' => (object) ['name' => 'Grupo Fire (Todas las regiones)'], 'data' => $aggregatedData];
287
288            if ($type === 'monthly') {
289                $aggregatedHtml = $this->buildCombinedAllRegionsReportHtml($aggregatedReport, $year, $month, true);
290                $aggregatedSubject = "Cierre Facturación - Grupo Fire - {$this->months[$month]} {$year}";
291            } else {
292                $singleAgg = ['company' => $aggregatedReport['company'], 'data' => $aggregatedData['weekly']];
293                $aggregatedHtml = $this->buildAllRegionsReportHtml($singleAgg, $year, $month, 'Report Semanal');
294                $aggregatedSubject = "Reporte Semanal - Grupo Fire - {$this->months[$month]} {$year}";
295            }
296
297            if ($isTest) {
298                $this->info('--- Aggregated Report for Grupo Fire (Todas las regiones) ---');
299                $this->info('Recipients: '.$allRegionRecipients->pluck('email')->implode(', '));
300                $this->line($aggregatedHtml);
301                $sent += $allRegionRecipients->count();
302            } else {
303                foreach ($allRegionRecipients as $recipient) {
304                    // FIRE-1148: dispatched to the 'email' queue, same as
305                    // the per-company branch above.
306                    SendFinanceRecipientEmail::dispatch(
307                        $recipient->email,
308                        $recipient->name,
309                        $aggregatedSubject,
310                        $aggregatedHtml,
311                    )->onQueue('email');
312                    $sent++;
313                    $this->info("Dispatched to {$recipient->email}");
314                }
315            }
316        }
317
318        // FIRE-1148: "sent" is now "dispatched to the email queue" — actual
319        // SendGrid delivery happens in the worker. Worded the same way so
320        // the existing operational logs / dashboards keep matching.
321        $this->info("Finance report sent to {$sent} recipients.");
322        Log::channel('third-party')->info("finance:send-report — Dispatched to {$sent} recipients.");
323
324        return 0;
325    }
326
327    /**
328     * Determine which month to report on.
329     *
330     * Priority:
331     * 1. force_report_month override from tbl_finance_month_config (current month row)
332     * 2. Previous month's close_date — if today <= close_date, still report previous month
333     * 3. Default WD+5 logic: if <= 5 working days have passed, report on previous month
334     */
335    private function getReportMonth(): int
336    {
337        $today = Carbon::now();
338        $year = $today->year;
339        $currentMonth = $today->month;
340
341        // Check for force override on current month
342        $config = TblFinanceMonthConfig::where('year', $year)
343            ->where('month', $currentMonth)
344            ->first();
345
346        if ($config && $config->force_report_month) {
347            return (int) $config->force_report_month;
348        }
349
350        // Check previous month's close_date
351        $prevMonth = $currentMonth === 1 ? 12 : $currentMonth - 1;
352        $prevYear = $currentMonth === 1 ? $year - 1 : $year;
353        $prevConfig = TblFinanceMonthConfig::where('year', $prevYear)
354            ->where('month', $prevMonth)
355            ->first();
356
357        if ($prevConfig && $prevConfig->close_date) {
358            // If today is before the close date, still report on previous month
359            if ($today->lte(Carbon::parse($prevConfig->close_date))) {
360                return $prevMonth;
361            }
362
363            return $currentMonth;
364        }
365
366        // Default: WD+5 logic
367        $workingDays = 0;
368        for ($day = 1; $day <= $today->day; $day++) {
369            $date = Carbon::create($today->year, $currentMonth, $day);
370            if ($date->isWeekday()) {
371                $workingDays++;
372            }
373        }
374
375        return $workingDays <= 5 ? $prevMonth : $currentMonth;
376    }
377
378    /**
379     * Fetch resumen data from Zenital + MySQL (same logic as FinanceController::list_resumen).
380     */
381    public function getResumenData(int $companyId, int $year, int $month, string $type = 'monthly'): array
382    {
383        $isWeekly = $type === 'weekly';
384
385        // Build Zenital sede map for this company, translating to local tbl_finance_sedes IDs
386        $zenitalMap = $this->getZenitalSucursalesMap();
387        $localSedes = TblFinanceSedes::where('company_id', $companyId)
388            ->where('is_active', 1)
389            ->pluck('id', 'name')
390            ->toArray();
391
392        $zenitalToSede = [];
393        foreach ($zenitalMap as $zenitalId => $info) {
394            if ($info['company_id'] === $companyId) {
395                // Map to local sede ID by name match, fallback to Zenital sede_id
396                $zenitalToSede[$zenitalId] = $localSedes[$info['nombre']] ?? $info['sede_id'];
397            }
398        }
399
400        // Query Zenital for actuals (current year + n-1)
401        // Weekly = current month only; Monthly = year-to-date
402        //
403        // Berta audit follow-up — Bug E: pre-fix, this method was called
404        // once per company × once per month/YTD, and each Zenital query
405        // could hang for ~30s on a trans-Atlantic round trip with no
406        // timeout. The whole monthly preview took ~11 minutes locally
407        // — Berta's browser was timing out long before the email
408        // generation finished, and the preview pane fell back to the
409        // last-rendered (weekly) HTML, making it look like "weekly =
410        // monthly". Same PGCONNECT_TIMEOUT + statement_timeout pattern
411        // as FinanceController::list_resumen.
412        putenv('PGCONNECT_TIMEOUT='.env('ZENITAL_DB_CONNECT_TIMEOUT', 5));
413        $idx = [];
414        if (! empty($zenitalToSede)) {
415            try {
416                DB::connection('zenital')->statement('SET statement_timeout = '.(int) env('ZENITAL_DB_STATEMENT_TIMEOUT_MS', 15000));
417
418                // Per-invoice dedup before summing: Zenital STG had every
419                // Alcarrena invoice recorded twice in fact_facturacion under
420                // the same sk_sucursal=150 (6,459 rows vs 3,285 distinct
421                // num_factura — exactly 2x). A naive SUM doubled La Mancha
422                // from 1,36 M€ to 2,73 M€ on the YTD email. Collapse to one
423                // row per (num_factura, sk_sucursal, ano) by taking MAX of
424                // the amount (duplicates carry the same value), THEN SUM.
425                $skKeys = array_keys($zenitalToSede);
426                $skPlaceholders = implode(',', array_fill(0, count($skKeys), '?'));
427                $monthClause = $isWeekly ? 'd.num_mes = ?' : 'd.num_mes <= ?';
428                $bindings = array_merge(
429                    array_map('intval', $skKeys),
430                    [$year, $year - 1, $month],
431                );
432
433                $facturacion = DB::connection('zenital')->select("
434                    SELECT sk_sucursal, ano, SUM(invoice_amount) AS total
435                    FROM (
436                        SELECT f.num_factura, f.sk_sucursal, d.ano,
437                               MAX(f.base_imponible) AS invoice_amount
438                        FROM fact_facturacion f
439                        JOIN dim_fecha d ON d.sk_fecha = f.sk_fecha_emision
440                        WHERE f.sk_sucursal IN ({$skPlaceholders})
441                          AND d.ano IN (?, ?)
442                          AND {$monthClause}
443                        GROUP BY f.num_factura, f.sk_sucursal, d.ano
444                    ) per_invoice
445                    GROUP BY sk_sucursal, ano
446                ", $bindings);
447
448                foreach ($facturacion as $row) {
449                    $sedeId = $zenitalToSede[$row->sk_sucursal] ?? $row->sk_sucursal;
450                    $idx[$sedeId][$row->ano] = ($idx[$sedeId][$row->ano] ?? 0) + (float) $row->total;
451                }
452            } catch (\Exception $e) {
453                Log::channel('third-party')->warning("finance:send-report — Zenital query failed for company {$companyId}: {$e->getMessage()}");
454            }
455        }
456
457        // Catalunya empty-actuals follow-up: GESTIONA-sourced invoices in
458        // Zenital often arrive with sk_sucursal = -1 (unmapped sucursal at
459        // ETL) and only the sk_region populated. The entire Catalunya
460        // company's April 2026 facturación lived there — 605K€ that
461        // never landed on any sede. Pull these region-only totals as a
462        // synthetic `region-{titanRegionId}` bucket so they sum into the
463        // company's actuals AND fall on the correct row in the aggregated
464        // "Todas las regiones" detail. Filter to fuente_datos='GESTIONA'
465        // so we don't double-count CUEN/CANO rows that already arrive
466        // via tbl_finance_report_semanal (Drive imports).
467        // Mirror of FinanceController::list_report_semanal lines 1248-1342.
468        // Most Desconocido GESTIONA invoices have a real sucursal label on
469        // dim_cliente_facturacion.sucursal even though fact_facturacion
470        // carries sk_sucursal = -1. Power BI groups by the client label, so
471        // we re-attribute the same way before falling back to a region
472        // bucket. Pre-fix the email skipped this step, kept the whole pile
473        // in 'region-{titanRegionId}', and double-counted any portion the
474        // in-app re-attributed to a sede that already had a manual override
475        // (Castellón Apr 2026 was inflated by ~59K€ this way: ~59K€ of
476        // client_sucursal = "Cano Lopera"/"Extincas" Desconocido invoices
477        // were summed in the region bucket AND shadowed by the operator's
478        // tbl_finance_resumen_anual override on those sedes).
479        $zenitalSkRegions = $this->getZenitalSkRegionsForCompany($companyId);
480        if (! empty($zenitalSkRegions)) {
481            try {
482                $localByName = TblFinanceSedes::where('is_active', 1)
483                    ->whereNotNull('company_id')
484                    ->get()
485                    ->keyBy(fn ($s) => mb_strtolower(trim((string) $s->name)));
486                $clientSucursalAliases = [
487                    'jomar alcarreña' => 'alcarrena',
488                ];
489
490                // Per-invoice dedup — same rationale as the sucursal pass
491                // above. fact_facturacion can carry duplicate rows per
492                // num_factura; collapse before summing.
493                $regionKeys = array_keys($zenitalSkRegions);
494                $regionPlaceholders = implode(',', array_fill(0, count($regionKeys), '?'));
495                $monthClause = $isWeekly ? 'd.num_mes = ?' : 'd.num_mes <= ?';
496                $bindings = array_merge(
497                    array_map('intval', $regionKeys),
498                    [$year, $year - 1, $month],
499                );
500
501                // BI department rules ("Proceso de relleno de clientes sin
502                // Sucursal"): when fact_facturacion.sk_sucursal = -1 AND
503                // dim_cliente_servicio has no sucursal, the invoice is
504                // attributed to a sede based on the invoice series letter
505                // (UPPER(LEFT(num_factura, 1))) and the sk_region. Implemented
506                // by self::seriesAttributionMap(). Catalunya Serie R stays
507                // unattributed (BI rule: "se dejará en blanco"). The
508                // dim_cliente_facturacion fallback handles series the BI
509                // doc doesn't cover (Castellón Y, Baleares N, etc.).
510                $unattributed = DB::connection('zenital')->select("
511                    SELECT sk_region, serie, client_sucursal, ano, SUM(invoice_amount) AS total
512                    FROM (
513                        SELECT f.num_factura, f.sk_region,
514                               UPPER(SUBSTRING(f.num_factura FROM 1 FOR 1)) AS serie,
515                               c.sucursal AS client_sucursal, d.ano,
516                               MAX(f.base_imponible) AS invoice_amount
517                        FROM fact_facturacion f
518                        JOIN dim_fecha d ON d.sk_fecha = f.sk_fecha_emision
519                        LEFT JOIN dim_cliente_facturacion c
520                            ON c.sk_cliente_facturacion = f.sk_cliente_facturacion
521                        WHERE f.sk_sucursal = -1
522                          AND f.fuente_datos = 'GESTIONA'
523                          AND f.sk_region IN ({$regionPlaceholders})
524                          AND d.ano IN (?, ?)
525                          AND {$monthClause}
526                        GROUP BY f.num_factura, f.sk_region, serie, c.sucursal, d.ano
527                    ) per_invoice
528                    GROUP BY sk_region, serie, client_sucursal, ano
529                ", $bindings);
530
531                $seriesMap = self::seriesAttributionMap();
532                $simplification = self::sedeSimplificationMap();
533
534                foreach ($unattributed as $row) {
535                    $ano = (int) $row->ano;
536                    $total = (float) $row->total;
537                    $skRegion = (int) $row->sk_region;
538                    $serie = mb_strtoupper(trim((string) ($row->serie ?? '')));
539
540                    // BI rule lookup.
541                    $bySerieTarget = $seriesMap[$skRegion][$serie]
542                        ?? $seriesMap[$skRegion]['*']
543                        ?? null;
544
545                    $sede = null;
546                    if ($bySerieTarget !== null && $bySerieTarget !== '__DROP__') {
547                        $canonical = $simplification[$bySerieTarget] ?? $bySerieTarget;
548                        $sede = $localByName[$canonical] ?? null;
549                    }
550
551                    // No client_sucursal fallback. Per BI rules: only invoices
552                    // whose series matches one of the explicit mappings get
553                    // attributed to a specific sede. Everything else stays
554                    // in the regional "Sin asignar" bucket (the BI doc's
555                    // "Serie R se dejará en blanco" guidance, generalised to
556                    // all unmapped series). Counts in the region total via
557                    // the region-X synthetic entry.
558
559                    if ($sede !== null) {
560                        $idx[(int) $sede->id][$ano] = ($idx[(int) $sede->id][$ano] ?? 0) + $total;
561
562                        continue;
563                    }
564
565                    $titanRegionId = $zenitalSkRegions[$skRegion] ?? null;
566                    if (! $titanRegionId) {
567                        continue;
568                    }
569                    // Synthetic key — string prefix prevents collision with
570                    // numeric sede_ids. aggregateAllReports special-cases it.
571                    $regionKey = 'region-'.$titanRegionId;
572                    $idx[$regionKey][$ano] = ($idx[$regionKey][$ano] ?? 0) + $total;
573                }
574            } catch (\Exception $e) {
575                Log::channel('third-party')->warning("finance:send-report — Zenital unattributed query failed for company {$companyId}: {$e->getMessage()}");
576            }
577        }
578
579        // Budget from MySQL — weekly = current month only; monthly = YTD
580        $budgetQuery = TblFinanceBudgetAnual::where('company_id', $companyId)
581            ->where('year', $year);
582        if ($isWeekly) {
583            $budgetQuery->where('month', '=', $month);
584        } else {
585            $budgetQuery->where('month', '<=', $month);
586        }
587        $budgetBySede = $budgetQuery->get()
588            ->groupBy('sede_id')
589            ->map(fn ($rows) => (float) $rows->sum('amount'));
590
591        // Prevision from MySQL — weekly = current month only; monthly = YTD
592        $previsionQuery = TblFinancePrevisionAnual::where('company_id', $companyId)
593            ->where('year', $year);
594        if ($isWeekly) {
595            $previsionQuery->where('month', '=', $month);
596        } else {
597            $previsionQuery->where('month', '<=', $month);
598        }
599        $previsionBySede = $previsionQuery->get()
600            ->groupBy('sede_id')
601            ->map(fn ($rows) => (float) $rows->sum('amount'));
602
603        // Google-Drive-imported actuals for sedes outside Zenital (Aeroextinción,
604        // Cano Lopera, Cuenfa, Extincas, etc). Aggregated from tbl_finance_report_semanal,
605        // matches the Reporte Semanal UI. Local key space is sede_id (not sk_sucursal)
606        // so it doesn't collide with the Zenital-keyed $idx above.
607        $localActualsQuery = TblFinanceReportSemanal::where('company_id', $companyId)
608            ->whereIn('year', [$year, $year - 1]);
609        if ($isWeekly) {
610            $localActualsQuery->where('month', '=', $month);
611        } else {
612            $localActualsQuery->where('month', '<=', $month);
613        }
614        $localBySede = [];
615        foreach ($localActualsQuery->get() as $row) {
616            if ($row->actuals === null) {
617                continue;
618            }
619            $localBySede[$row->sede_id][(int) $row->year] = ($localBySede[$row->sede_id][(int) $row->year] ?? 0) + (float) $row->actuals;
620        }
621
622        // Berta audit follow-up — Bug D: Resumen Anual manual edits
623        // override Zenital/Drive actuals + N-1 for the same (sede, month).
624        // Pre-fix the weekly email pulled only Zenital + Drive and ignored
625        // manual edits typed on the Resumen tab — so a Resumen-edited
626        // Valencia would show 776.160 in the email but 807.943 in the
627        // in-app tab. Now both paths agree.
628        $resumenQuery = TblFinanceResumenAnual::where('company_id', $companyId)
629            ->where('year', $year);
630        if ($isWeekly) {
631            $resumenQuery->where('month', '=', $month);
632        } else {
633            $resumenQuery->where('month', '<=', $month);
634        }
635        $resumenBySede = [];
636        foreach ($resumenQuery->get() as $row) {
637            $resumenBySede[$row->sede_id]['actuals'] = ($resumenBySede[$row->sede_id]['actuals'] ?? 0) + (float) ($row->actuals ?? 0);
638            $resumenBySede[$row->sede_id]['actuals_n1'] = ($resumenBySede[$row->sede_id]['actuals_n1'] ?? 0) + (float) ($row->actuals_n1 ?? 0);
639        }
640
641        // Merge all sede_ids
642        $allSedeIds = array_unique(array_merge(
643            array_keys($idx),
644            $budgetBySede->keys()->toArray(),
645            $previsionBySede->keys()->toArray(),
646            array_keys($localBySede),
647            array_keys($resumenBySede)
648        ));
649
650        $actualsYtd = 0;
651        $actualsN1Ytd = 0;
652        $budgetYtd = 0;
653        $previsionYtd = 0;
654        $bySede = [];
655
656        foreach ($allSedeIds as $sedeId) {
657            // Precedence:
658            //   1. Resumen Anual manual edits (Bug D fix) — operator
659            //      typed the value, must win.
660            //   2. Zenital DWH actuals — authoritative when present.
661            //   3. Drive-imported aggregates — fallback for sedes not in
662            //      the Zenital map.
663            $hasResumen = isset($resumenBySede[$sedeId]);
664            $resumenActuals = $hasResumen ? ($resumenBySede[$sedeId]['actuals'] ?? null) : null;
665            $resumenN1 = $hasResumen ? ($resumenBySede[$sedeId]['actuals_n1'] ?? null) : null;
666
667            $actuals = ($resumenActuals !== null && $resumenActuals != 0)
668                ? $resumenActuals
669                : ($idx[$sedeId][$year] ?? $localBySede[$sedeId][$year] ?? 0);
670            $n1 = ($resumenN1 !== null && $resumenN1 != 0)
671                ? $resumenN1
672                : ($idx[$sedeId][$year - 1] ?? $localBySede[$sedeId][$year - 1] ?? 0);
673            $budget = $budgetBySede[$sedeId] ?? 0;
674            $prevision = $previsionBySede[$sedeId] ?? 0;
675
676            if ($actuals == 0 && $n1 == 0 && $budget == 0 && $prevision == 0) {
677                continue;
678            }
679
680            $actualsYtd += $actuals;
681            $actualsN1Ytd += $n1;
682            $budgetYtd += $budget;
683            $previsionYtd += $prevision;
684
685            $bySede[$sedeId] = [
686                'actuals' => $actuals,
687                'actuals_n1' => $n1,
688                'budget' => $budget,
689                'prevision' => $prevision,
690            ];
691        }
692
693        return [
694            'actuals_ytd' => $actualsYtd,
695            'actuals_n1_ytd' => $actualsN1Ytd,
696            'budget_ytd' => $budgetYtd,
697            'prevision_ytd' => $previsionYtd,
698            'by_sede' => $bySede,
699        ];
700    }
701
702    /**
703     * Returns map: zenital sk_region => titan region_id, restricted to
704     * the regions that belong to $companyId via tbl_finance_sedes. Used
705     * to attribute the "Desconocido sucursal" GESTIONA invoices in
706     * Zenital (which only carry sk_region) back to the right Titan
707     * region. Empty result is fine — just means nothing to attribute.
708     */
709    private function getZenitalSkRegionsForCompany(int $companyId): array
710    {
711        $titanRegionsForCompany = TblFinanceSedes::where('company_id', $companyId)
712            ->where('is_active', 1)
713            ->whereNotNull('region_id')
714            ->pluck('region_id')
715            ->unique()
716            ->toArray();
717        if (empty($titanRegionsForCompany)) {
718            return [];
719        }
720
721        $all = $this->getZenitalRegionMap();
722        $filtered = [];
723        foreach ($all as $skRegion => $titanRegionId) {
724            if (in_array($titanRegionId, $titanRegionsForCompany, true)) {
725                $filtered[$skRegion] = $titanRegionId;
726            }
727        }
728
729        return $filtered;
730    }
731
732    /**
733     * BI department rules for Desconocido (sk_sucursal = -1) invoices.
734     * Source: "Proceso de relleno de clientes sin Sucursal.docx".
735     *
736     * Map structure: [sk_region][serie_letter] => folded_sede_name.
737     * Special values:
738     *   - '*'         catch-all: any series in that region maps here
739     *                 (used for sk_region 14/15/18/20 which have one sede)
740     *   - '__DROP__'  do NOT re-attribute, do NOT fallback to client_sucursal;
741     *                 the invoice stays in the regional "Sin asignar" bucket
742     *                 (Catalunya R series per BI doc).
743     *
744     * sede names are lowercase + accent-folded so they match $localByName
745     * keys built by foldName / mb_strtolower(trim($name)) callers.
746     */
747    public static function seriesAttributionMap(): array
748    {
749        return [
750            14 => ['*' => 'oasys'],       // sk_region 14 (Oasys) — all to Oasys
751            15 => ['*' => 'crespo'],      // sk_region 15 (Crespo) — all to Crespo
752            18 => ['*' => 'togasa'],      // sk_region 18 (Togasa) — all to Togasa
753            20 => ['*' => 'extinfuego'],  // sk_region 20 (Extinfuego/Alicante) — all to Extinfuego
754            19 => [                       // Valencia
755                'L' => 'guipons',
756                'V' => 'vivo',            // accent-folded ("Vivó" → "vivo")
757            ],
758            21 => [                       // Cataluña
759                'T' => 'gallex',
760                'S' => 'master centella',
761                'R' => '__DROP__',        // BI: "Serie R se dejará en blanco"
762            ],
763            22 => [                       // Almería (→ Andalucía in Titan)
764                'B' => 'drago',
765            ],
766            23 => [                       // Madrid (+ La Mancha via series J)
767                'C' => 'enfire',
768                'D' => 'exfire',
769                'A' => 'montoya',
770                'B' => 'precoin',
771                'E' => 'anin',
772                'F' => 'exconin',
773                'G' => 'rosegur',
774                'H' => 'grupo fire madrid',
775                'J' => 'alcarrena',       // La Mancha sede ("Alcarreña" folded)
776            ],
777        ];
778    }
779
780    /**
781     * BI "simplificación" of Grupo Fire X sucursales (from the same doc).
782     * After attribution, these sucursal names are rewritten so they roll up
783     * to the canonical Grupo Fire X sede. Keys and values are folded names.
784     */
785    public static function sedeSimplificationMap(): array
786    {
787        return [
788            'grupo fire gava' => 'grupo fire cataluna',   // Gavà → Cataluña
789            'grupo fire gavà' => 'grupo fire cataluna',
790            'grupo fire rubi' => 'grupo fire cataluna',   // Rubí → Cataluña
791            'grupo fire rubí' => 'grupo fire cataluna',
792            'grupo fire centella' => 'master centella',       // Centella sub → Master Centella
793            'grupo fire gallex' => 'gallex',                // Gallex sub → Gallex
794        ];
795    }
796
797    /**
798     * Jorge's canonical sede → Titan region mapping, extracted from
799     * "20260408 Grupo Fire Ventas_Canal - (v. Marzo).xlsx" — sheet "Ventas".
800     * Keys are folded sede names (lowercase + accent-stripped). Values are
801     * Titan region IDs (see tbl_finance_regions). Anything NOT in this map
802     * is EXCLUDED from per-region totals so we don't accidentally count
803     * sedes Jorge's BI doesn't surface (Grupo Fire Guadalajara, Grupo Fire
804     * MOF, cross-company phantom entries, etc.).
805     *
806     * Returns: [folded_name => titan_region_id]
807     */
808    public static function jorgeRegionWhitelist(): array
809    {
810        // Titan region IDs (from tbl_finance_regions):
811        //   1 Catalunya, 2 La Mancha, 3 Valencia, 4 Alicante, 5 Aragón,
812        //   6 Madrid, 8 Andalucía, 11 Baleares, 12 Castellón, 23 Castilla y León
813        return [
814            // Catalunya (Jorge: Clemente, Josmafoc, Ingesfoc, Lluis_Moff,
815            //   Sat Valles, Cisemex, NioExtin, Gallex, Centella, Fire Business CAT)
816            'clemente' => 1,
817            'extintores clemente' => 1,
818            'josmafoc' => 1,
819            'ingesfoc' => 1,
820            'lluis_moff' => 1,
821            'sat valles' => 1,
822            'cisemex' => 1,
823            'nioextin' => 1,
824            'gallex' => 1,
825            'centella' => 1,
826            'master centella' => 1,
827            'fire business cat' => 1,
828            'grupo fire cataluna' => 1,
829
830            // Madrid (Jorge: Precoin, EnFire, Montoya, ExFire, Anin, ExConin,
831            //   Rosegur, Segurtrex, ICF, Fire Business MAD)
832            'precoin' => 6,
833            'enfire' => 6,
834            'montoya' => 6,
835            'exfire' => 6,
836            'anin' => 6,
837            'exconin' => 6,
838            'rosegur' => 6,
839            'segurtrex' => 6,
840            'icf' => 6,
841            'fire business mad' => 6,
842            'grupo fire madrid' => 6,
843
844            // La Mancha (Jorge: Alcarrena)
845            'alcarrena' => 2,
846            'jomar alcarrena' => 2,
847
848            // Aragón (Jorge: Oasys)
849            'oasys' => 5,
850
851            // Andalucía (Jorge: Drago, Robles, Aeroextinción)
852            'drago' => 8,
853            'robles' => 8,
854            'aeroextincion' => 8,
855
856            // Valencia (Jorge: Guipons, Cuenfa, AirFeu, Vivo)
857            'guipons' => 3,
858            'cuenfa' => 3,
859            'airfeu' => 3,
860            'vivo' => 3,
861
862            // Castilla y León (Jorge: Togasa, Crespo)
863            'togasa' => 23,
864            'crespo' => 23,
865
866            // Alicante (Jorge: ExtinFuego)
867            'extinfuego' => 4,
868
869            // Castellón (Jorge: Cano Lopera, Extincas)
870            'cano lopera' => 12,
871            'extincas' => 12,
872
873            // Baleares (Jorge: Segucor, Ni Foc Ni Fum)
874            'segucor' => 11,
875            'ni foc ni fum' => 11,
876        ];
877    }
878
879    /**
880     * Build sk_region (Zenital) → region_id (Titan) lookup. Most regions
881     * match by name; a small alias set handles Zenital's sub-entity rows
882     * (Oasys/Crespo/Togasa/Extinfuego/Almería) that point at a parent
883     * Titan region. Cached per-process.
884     */
885    private function getZenitalRegionMap(): array
886    {
887        // FIRE-1148: shared with FinanceController via the ZenitalCatalog
888        // service — same Redis-cached lookup, same alias map.
889        return $this->zenital->regionMap();
890    }
891
892    /**
893     * Runtime Zenital sucursal map — must stay logically in sync with
894     * FinanceController::getZenitalSucursalesMap (same lookup, same
895     * aliases). See that method for the full rationale; tl;dr the
896     * hardcoded sk_sucursal IDs we used before were stale after a
897     * Zenital DWH rekey, and weekly Catalunya actuals came back as 0.
898     */
899    private function getZenitalSucursalesMap(): array
900    {
901        // FIRE-1148: shared with FinanceController via the ZenitalCatalog
902        // service — same Redis-cached lookup, same SCD-2 dedup, same alias.
903        return $this->zenital->sucursalesMap();
904    }
905
906    public function aggregateAllReports(array $reportsByCompany, string $type = 'weekly'): array
907    {
908        $sections = $type === 'monthly' ? ['weekly', 'monthly'] : ['weekly'];
909        $result = ['weekly' => [], 'monthly' => []];
910
911        // Berta audit follow-up — Item 4: pre-fix this method grouped by
912        // `tbl_companies.company_id`, so Valencia / Alicante / Castellón
913        // (three regions sharing company_id=30) appeared as a single
914        // "Comunidad Valenciana" row. Now we look up each sede's
915        // `region_id` and aggregate by `tbl_finance_regions.id` so the
916        // email matches the in-app dropdown grouping.
917        // Pull region_id for both active AND inactive sedes — bookkeeping
918        // tables (budget_anual / prevision_anual / resumen_anual /
919        // report_semanal) still reference legacy sedes whose is_active was
920        // flipped to 0 when a duplicate was deduped. Pre-fix those rows
921        // landed in the "company-{id}" fallback bucket and rendered as a
922        // duplicate "Grupo Fire {company-name}" row next to the real
923        // region; the legacy sede's region_id is still the correct one.
924        $sedeRegion = TblFinanceSedes::pluck('region_id', 'id')->toArray();
925        $sedeCompany = TblFinanceSedes::pluck('company_id', 'id')->toArray();
926        $regionNames = TblFinanceRegions::where('is_active', 1)
927            ->pluck('name', 'id')
928            ->toArray();
929        // Budget/previsión rows on inactive sedes are stale duplicates the
930        // Budget tab UI never renders (it iterates is_active=1). Catalunya
931        // inactive sedes 1/6/8/9 carry ~1 M€ of legacy budget that was
932        // never zeroed when the active replacements were created. We keep
933        // inactive sedes for ACTUALS (some carry the real seed data) but
934        // skip them when summing budget/previsión.
935        $activeSedeIds = array_flip(TblFinanceSedes::where('is_active', 1)->pluck('id')->all());
936
937        // Backstop for hard-orphan sede_ids (e.g. 117/118/123/124/1000040
938        // for Catalunya — Zenital hardcoded sede_ids that never got a
939        // matching tbl_finance_sedes row). Map company → most common
940        // region of its active sedes; if a sede_id is unknown but its
941        // owning company has a clear "home" region, route the row there
942        // instead of inventing a "Grupo Fire {company}" duplicate.
943        $companyPrimaryRegion = [];
944        $regionCountsByCompany = TblFinanceSedes::where('is_active', 1)
945            ->whereNotNull('region_id')
946            ->selectRaw('company_id, region_id, COUNT(*) as c')
947            ->groupBy('company_id', 'region_id')
948            ->get()
949            ->groupBy('company_id');
950        foreach ($regionCountsByCompany as $companyId => $rows) {
951            $best = $rows->sortByDesc('c')->first();
952            if ($best) {
953                $companyPrimaryRegion[(int) $companyId] = (int) $best->region_id;
954            }
955        }
956
957        // FIRE follow-up: dedup-by-sede-name. The bookkeeping tables hold
958        // historical rows under DEACTIVATED duplicate sedes — sometimes the
959        // legacy (inactive) sede has the real seed data and the active sede
960        // has only a placeholder, sometimes it's the opposite. Summing both
961        // double-counts (Valencia inflated by ~1,7 M€ on YTD Apr 2026).
962        // Strategy: collapse sedes by lowercased name; for each name pick
963        // the entry with the LARGER actuals (the one with the real data
964        // wins, the placeholder loses). Accent folding lets "Vivó" and
965        // "Vivo", "AirFeu" and "Airfeu" collapse together too.
966        $sedeNames = TblFinanceSedes::pluck('name', 'id')->toArray();
967        $foldName = static function (string $name): string {
968            $name = mb_strtolower(trim($name));
969            $name = strtr($name, [
970                'á' => 'a', 'é' => 'e', 'í' => 'i', 'ó' => 'o', 'ú' => 'u',
971                'ñ' => 'n', 'à' => 'a', 'è' => 'e', 'ì' => 'i', 'ò' => 'o',
972                'ù' => 'u', 'ü' => 'u',
973            ]);
974
975            return $name;
976        };
977
978        // Jorge's canonical sede → region whitelist (from his "Ventas" sheet
979        // in 20260408 Grupo Fire Ventas_Canal - v. Marzo.xlsx). Aggregator
980        // uses this as the SOLE source of truth for which sedes count and
981        // which region each belongs to. Anything not in the whitelist is
982        // excluded so Grupo Fire Guadalajara / Grupo Fire MOF / cross-
983        // company phantom entries don't leak into region totals.
984        // Sede name simplification ("Grupo Fire Gavà" → "Grupo Fire Cataluña",
985        // etc., per the BI doc) is applied BEFORE the whitelist lookup so
986        // legacy sub-entity names roll up correctly.
987        $jorgeWhitelist = self::jorgeRegionWhitelist();
988        $jorgeSimplification = self::sedeSimplificationMap();
989
990        foreach ($sections as $section) {
991            // Stage 1a — dedup actuals/N-1 by sede name (the seed-data
992            // ambiguity fix). Synthetic "region-{id}" entries are kept
993            // under a unique key so they're never deduped against a real
994            // sede.
995            //
996            // Stage 1b (separate accumulators) — budget and previsión are
997            // sede-keyed in MySQL (tbl_finance_budget_anual /
998            // tbl_finance_prevision_anual), each typed by the operator on
999            // ONE specific sede record (the active one). Pre-fix we kept
1000            // budget/prevision INSIDE the deduped actuals entry — so when
1001            // the inactive sede won the dedup for actuals (e.g. AirFeu
1002            // inactive sede 19 with 1 M€ in seed data beat active sede 24
1003            // with 55 K€), its budget=0 was used and the active sede's
1004            // real 1,8 M€ budget got dropped. Valencia P. Negocio
1005            // collapsed from 3 M€ to 1,1 M€ that way. Fix: track
1006            // budget/prevision per region INDEPENDENTLY, summed from
1007            // every by_sede entry (no dedup) — budgets don't have the
1008            // active/inactive seed-data problem actuals do, so summing
1009            // is safe.
1010            $byName = [];
1011            $byRegionBudget = [];
1012            $byRegionPrevision = [];
1013            $budgetTotalYtd = 0;
1014            $previsionTotalYtd = 0;
1015
1016            foreach ($reportsByCompany as $companyId => $report) {
1017                $data = $report[$section];
1018                if (! $data) {
1019                    continue;
1020                }
1021
1022                foreach (($data['by_sede'] ?? []) as $sedeId => $sedeVals) {
1023                    if (is_string($sedeId) && str_starts_with($sedeId, 'region-')) {
1024                        // Region-level unattributed buckets stay distinct
1025                        // — never collide with a sede record.
1026                        $key = $sedeId.'|'.$companyId;
1027                        $regionId = (int) substr($sedeId, 7);
1028                        $regionName = $regionNames[$regionId] ?? null;
1029                        $byName[$key] = [
1030                            'sedeId' => $sedeId,
1031                            'companyId' => $companyId,
1032                            'regionId' => $regionId,
1033                            'regionName' => $regionName,
1034                            'vals' => $sedeVals,
1035                        ];
1036                        // Region-level synthetic entries already carry
1037                        // their own budget/previsión = 0 (Desconocido
1038                        // doesn't have a target). Skip the side tally so
1039                        // we don't double-add anything.
1040
1041                        continue;
1042                    }
1043
1044                    $name = $sedeNames[$sedeId] ?? '';
1045                    $nameKey = $name !== '' ? $foldName($name) : 'sede-'.$sedeId;
1046                    // Apply the BI sucursal "simplificación" first: Gavà /
1047                    // Rubí → Grupo Fire Cataluña, Grupo Fire Centella →
1048                    // Master Centella, Grupo Fire Gallex → Gallex. The
1049                    // whitelist below uses these canonical names.
1050                    $nameKey = $jorgeSimplification[$nameKey] ?? $nameKey;
1051
1052                    // Jorge's whitelist overrides everything: only count
1053                    // sedes whose folded name appears in his "Ventas" sheet,
1054                    // and force their region attribution to match his.
1055                    // If a sede isn't in the whitelist, route its actuals
1056                    // to the region's "Sin asignar" bucket so the region
1057                    // total still counts the money (matching Jorge's
1058                    // behaviour of leaving the sucursal blank but keeping
1059                    // the invoice in the region).
1060                    if (! isset($jorgeWhitelist[$nameKey])) {
1061                        $fallbackRegion = $sedeRegion[$sedeId]
1062                            ?? $companyPrimaryRegion[(int) $companyId]
1063                            ?? null;
1064                        if ($fallbackRegion !== null) {
1065                            $regionBucketKey = 'region-'.$fallbackRegion.'|whitelist|'.$companyId;
1066                            if (! isset($byName[$regionBucketKey])) {
1067                                $byName[$regionBucketKey] = [
1068                                    'sedeId' => 'region-'.$fallbackRegion,
1069                                    'companyId' => $companyId,
1070                                    'regionId' => $fallbackRegion,
1071                                    'regionName' => $regionNames[$fallbackRegion] ?? null,
1072                                    'vals' => ['actuals' => 0, 'actuals_n1' => 0, 'budget' => 0, 'prevision' => 0],
1073                                ];
1074                            }
1075                            $byName[$regionBucketKey]['vals']['actuals'] += (float) ($sedeVals['actuals'] ?? 0);
1076                            $byName[$regionBucketKey]['vals']['actuals_n1'] += (float) ($sedeVals['actuals_n1'] ?? 0);
1077                        }
1078
1079                        continue;
1080                    }
1081                    $regionId = $jorgeWhitelist[$nameKey];
1082                    $regionName = $regionNames[$regionId] ?? null;
1083
1084                    $candidate = [
1085                        'sedeId' => $sedeId,
1086                        'companyId' => $companyId,
1087                        'regionId' => $regionId,
1088                        'regionName' => $regionName,
1089                        'vals' => $sedeVals,
1090                    ];
1091
1092                    // Side tally: budget/previsión are summed per region
1093                    // for EVERY sede entry (no name-dedup). Inactive
1094                    // sedes carry 0 so they don't move the totals — the
1095                    // real values come from the active sede's row in
1096                    // tbl_finance_budget_anual.
1097                    $bucketKey = $regionId ?? ('company-'.$companyId);
1098                    $budgetVal = (float) ($sedeVals['budget'] ?? 0);
1099                    $previsionVal = (float) ($sedeVals['prevision'] ?? 0);
1100                    // Only count budget/previsión for sedes the Budget tab
1101                    // UI would render (is_active = 1). Inactive duplicates
1102                    // (e.g. Catalunya legacy sedes 1/6/8/9) carry stale
1103                    // budgets that double-count against the active sede's
1104                    // current budget.
1105                    if (isset($activeSedeIds[(int) $sedeId])) {
1106                        $byRegionBudget[$bucketKey] = ($byRegionBudget[$bucketKey] ?? 0) + $budgetVal;
1107                        $byRegionPrevision[$bucketKey] = ($byRegionPrevision[$bucketKey] ?? 0) + $previsionVal;
1108                        $budgetTotalYtd += $budgetVal;
1109                        $previsionTotalYtd += $previsionVal;
1110                    }
1111
1112                    // Dedup has TWO modes depending on whether the colliding
1113                    // entries share the same sede_id:
1114                    //
1115                    //  (a) Same sede_id, different companies → "prefer home
1116                    //      company". This is the ICF case: sede 22 has
1117                    //      company_id=18 in tbl_finance_sedes but
1118                    //      tbl_finance_resumen_anual has rows under
1119                    //      company_id=30 too. The non-home entry is a
1120                    //      phantom from messy data; the home one is real.
1121                    //
1122                    //  (b) Different sede_ids, same folded name → MAX wins
1123                    //      (the legitimate seed-data dedup we've always
1124                    //      done). Active vs inactive AirFeu sedes 24 vs 19
1125                    //      are both real; the one with bigger actuals
1126                    //      carries the seed history Jorge sees.
1127                    $homeCompany = $sedeCompany[$sedeId] ?? null;
1128                    $isPreferred = ($homeCompany !== null && (int) $homeCompany === (int) $companyId);
1129                    $candidate['isPreferred'] = $isPreferred;
1130
1131                    if (! isset($byName[$nameKey])) {
1132                        $byName[$nameKey] = $candidate;
1133
1134                        continue;
1135                    }
1136                    $existing = $byName[$nameKey];
1137                    $existingActuals = (float) ($existing['vals']['actuals'] ?? 0);
1138                    $newActuals = (float) ($sedeVals['actuals'] ?? 0);
1139
1140                    if ((int) $existing['sedeId'] === (int) $sedeId) {
1141                        // Mode (a): same sede across companies.
1142                        $existingPreferred = $existing['isPreferred'] ?? false;
1143                        if ($isPreferred && ! $existingPreferred) {
1144                            $byName[$nameKey] = $candidate;
1145                        } elseif ($isPreferred === $existingPreferred && $newActuals > $existingActuals) {
1146                            $byName[$nameKey] = $candidate;
1147                        }
1148                    } else {
1149                        // Mode (b): different sedes sharing a folded name.
1150                        if ($newActuals > $existingActuals) {
1151                            $byName[$nameKey] = $candidate;
1152                        }
1153                    }
1154                }
1155            }
1156
1157            // Stage 2 — re-aggregate the deduped actuals entries into
1158            // region buckets and recompute the actuals totals from those.
1159            // Budget / previsión are NOT taken from the deduped entries;
1160            // instead use the per-region side tallies built above.
1161            $actualsYtd = 0;
1162            $actualsN1Ytd = 0;
1163            $budgetYtd = $budgetTotalYtd;
1164            $previsionYtd = $previsionTotalYtd;
1165            $byRegion = [];
1166
1167            foreach ($byName as $entry) {
1168                $vals = $entry['vals'];
1169                $actualsYtd += (float) ($vals['actuals'] ?? 0);
1170                $actualsN1Ytd += (float) ($vals['actuals_n1'] ?? 0);
1171
1172                $bucketKey = $entry['regionId'] ?? ('company-'.$entry['companyId']);
1173                $bucketName = $entry['regionName']
1174                    ?? ($reportsByCompany[$entry['companyId']]['company']->name ?? '—');
1175                if (! isset($byRegion[$bucketKey])) {
1176                    $byRegion[$bucketKey] = [
1177                        'name' => $bucketName,
1178                        'actuals' => 0,
1179                        'actuals_n1' => 0,
1180                        'budget' => 0,
1181                        'prevision' => 0,
1182                    ];
1183                }
1184                $byRegion[$bucketKey]['actuals'] += (float) ($vals['actuals'] ?? 0);
1185                $byRegion[$bucketKey]['actuals_n1'] += (float) ($vals['actuals_n1'] ?? 0);
1186            }
1187
1188            // Inject the per-region budget / previsión side tally — kept
1189            // separate from the actuals dedup loop so the active sede's
1190            // budget never gets dropped when an inactive sede wins the
1191            // actuals dedup (Valencia P. Negocio bug: 3 M€ → 1,1 M€).
1192            foreach ($byRegionBudget as $bucketKey => $budgetVal) {
1193                if (! isset($byRegion[$bucketKey])) {
1194                    $byRegion[$bucketKey] = [
1195                        'name' => $regionNames[$bucketKey] ?? ('region '.$bucketKey),
1196                        'actuals' => 0,
1197                        'actuals_n1' => 0,
1198                        'budget' => 0,
1199                        'prevision' => 0,
1200                    ];
1201                }
1202                $byRegion[$bucketKey]['budget'] = $budgetVal;
1203            }
1204            foreach ($byRegionPrevision as $bucketKey => $previsionVal) {
1205                if (! isset($byRegion[$bucketKey])) {
1206                    $byRegion[$bucketKey] = [
1207                        'name' => $regionNames[$bucketKey] ?? ('region '.$bucketKey),
1208                        'actuals' => 0,
1209                        'actuals_n1' => 0,
1210                        'budget' => 0,
1211                        'prevision' => 0,
1212                    ];
1213                }
1214                $byRegion[$bucketKey]['prevision'] = $previsionVal;
1215            }
1216
1217            $result[$section] = [
1218                'actuals_ytd' => $actualsYtd,
1219                'actuals_n1_ytd' => $actualsN1Ytd,
1220                'budget_ytd' => $budgetYtd,
1221                'prevision_ytd' => $previsionYtd,
1222                'by_region' => $byRegion,
1223            ];
1224        }
1225
1226        return $result;
1227    }
1228
1229    private function buildReportHtml(array $report, int $year, int $month, bool $isClosed = false): string
1230    {
1231        $company = $report['company'];
1232        $data = $report['data'];
1233        $monthName = $this->months[$month];
1234        $monthSubtitle = $isClosed ? 'cierre' : 'mes en curso';
1235        $introText = $isClosed
1236            ? "Os comparto el cierre de facturación del mes de <strong>{$monthName} {$year}</strong>"
1237            : "Os comparto el resumen de la facturación del mes de <strong>{$monthName}</strong> hasta la fecha";
1238
1239        $actualsYtd = $data['actuals_ytd'];
1240        $actualsN1Ytd = $data['actuals_n1_ytd'];
1241        $budgetYtd = $data['budget_ytd'];
1242        $previsionYtd = $data['prevision_ytd'];
1243
1244        $diffN1 = $actualsYtd - $actualsN1Ytd;
1245        $pctN1 = $actualsN1Ytd != 0 ? ($diffN1 / $actualsN1Ytd) * 100 : 0;
1246        $diffBudget = $actualsYtd - $budgetYtd;
1247        $pctBudget = $budgetYtd != 0 ? ($diffBudget / $budgetYtd) * 100 : 0;
1248        $diffPrevision = $actualsYtd - $previsionYtd;
1249        $pctPrevision = $previsionYtd != 0 ? ($diffPrevision / $previsionYtd) * 100 : 0;
1250
1251        $signN1 = $diffN1 >= 0 ? '+' : '';
1252        $signBudget = $diffBudget >= 0 ? '+' : '';
1253        $signPrevision = $diffPrevision >= 0 ? '+' : '';
1254
1255        $colorN1 = $diffN1 >= 0 ? '#34c38f' : '#f46a6a';
1256        $colorBudget = $diffBudget >= 0 ? '#34c38f' : '#f46a6a';
1257        $colorPrevision = $diffPrevision >= 0 ? '#34c38f' : '#f46a6a';
1258
1259        $fmt = fn ($v) => number_format($v, 2, ',', '.');
1260        $fmtPct = fn ($v) => number_format($v, 1, ',', '.');
1261
1262        // Load sede names
1263        $sedeNames = TblFinanceSedes::pluck('name', 'id')->toArray();
1264        // Also map Zenital sede_ids to names
1265        foreach ($this->getZenitalSucursalesMap() as $zenitalId => $info) {
1266            $sedeNames[$info['sede_id']] = $sedeNames[$info['sede_id']] ?? $info['nombre'];
1267            $sedeNames[$zenitalId] = $info['nombre'];
1268        }
1269
1270        $html = "
1271        <div style='font-family: Arial, sans-serif; max-width: 900px; margin: 0 auto; color: #333;'>
1272            <p>Hola a todos,</p>
1273            <p>{$introText}</p>
1274
1275            <div style='background-color: #f8f9fa; border-radius: 8px; padding: 20px; margin: 20px 0;'>
1276                <h3 style='margin: 0 0 12px 0; color: #495057;'>Facturación {$monthName} {$year} ({$monthSubtitle})</h3>
1277                <ul style='list-style: none; padding: 0; margin: 0; line-height: 2;'>
1278                    <li>
1279                        <strong>Facturación {$monthName} {$year}:</strong>
1280                        <span style='font-size: 16px; font-weight: bold;'>{$fmt($actualsYtd)}&nbsp;€</span>
1281                    </li>
1282                    <li>
1283                        vs. {$monthName} ".($year - 1).":
1284                        <span style='color: {$colorN1}; font-weight: bold;'>{$signN1}{$fmt($diffN1)}&nbsp;€ ({$signN1}{$fmtPct($pctN1)}%)</span>
1285                    </li>
1286                    <li>
1287                        vs. Budget {$monthName} {$year}:
1288                        <span style='color: {$colorBudget}; font-weight: bold;'>{$signBudget}{$fmt($diffBudget)}&nbsp;€ ({$signBudget}{$fmtPct($pctBudget)}%)</span>
1289                        respecto al objetivo
1290                    </li>
1291                    <li>
1292                        vs. Previsión {$monthName} {$year}:
1293                        <span style='color: {$colorPrevision}; font-weight: bold;'>{$signPrevision}{$fmt($diffPrevision)}&nbsp;€ ({$signPrevision}{$fmtPct($pctPrevision)}%)</span>
1294                        respecto al objetivo
1295                    </li>
1296                </ul>
1297            </div>
1298
1299            <h3 style='color: #495057; margin-top: 30px;'>Detalle por sede — Report Semanal</h3>
1300            <table style='border-collapse: collapse; width: 100%; font-size: 13px;'>
1301                <thead>
1302                    <tr style='background-color: #f8f9fa;'>
1303                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: left;'>Sede</th>
1304                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>Actuals</th>
1305                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>n-1</th>
1306                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>Budget</th>
1307                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>Previsión</th>
1308                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>vs n-1</th>
1309                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>vs Budget</th>
1310                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>vs Previsión</th>
1311                    </tr>
1312                </thead>
1313                <tbody>";
1314
1315        $bySede = $data['by_sede'];
1316        uasort($bySede, fn ($a, $b) => ($b['actuals'] ?? 0) <=> ($a['actuals'] ?? 0) ?: ($b['budget'] ?? 0) <=> ($a['budget'] ?? 0));
1317
1318        foreach ($bySede as $sedeId => $vals) {
1319            $sedeName = $sedeNames[$sedeId] ?? "Sede {$sedeId}";
1320            $sedeActuals = $vals['actuals'];
1321            $sedeN1 = $vals['actuals_n1'];
1322            $sedeBudget = $vals['budget'];
1323            $sedePrevision = $vals['prevision'] ?? 0;
1324
1325            $sedeDiffN1 = $sedeActuals - $sedeN1;
1326            $sedeDiffBudget = $sedeActuals - $sedeBudget;
1327            $sedeDiffPrevision = $sedeActuals - $sedePrevision;
1328
1329            $colorSN1 = $sedeDiffN1 >= 0 ? '#34c38f' : '#f46a6a';
1330            $colorSB = $sedeDiffBudget >= 0 ? '#34c38f' : '#f46a6a';
1331            $colorSP = $sedeDiffPrevision >= 0 ? '#34c38f' : '#f46a6a';
1332            $signSN1 = $sedeDiffN1 >= 0 ? '+' : '';
1333            $signSB = $sedeDiffBudget >= 0 ? '+' : '';
1334            $signSP = $sedeDiffPrevision >= 0 ? '+' : '';
1335
1336            $html .= "<tr>
1337                <td style='border: 1px solid #dee2e6; padding: 6px 8px;'>{$sedeName}</td>
1338                <td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right;'>{$fmt($sedeActuals)}&nbsp;€</td>
1339                <td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right;'>{$fmt($sedeN1)}&nbsp;€</td>
1340                <td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right;'>{$fmt($sedeBudget)}&nbsp;€</td>
1341                <td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right;'>{$fmt($sedePrevision)}&nbsp;€</td>
1342                <td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right; color: {$colorSN1};'>{$signSN1}{$fmt($sedeDiffN1)}&nbsp;€</td>
1343                <td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right; color: {$colorSB};'>{$signSB}{$fmt($sedeDiffBudget)}&nbsp;€</td>
1344                <td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right; color: {$colorSP};'>{$signSP}{$fmt($sedeDiffPrevision)}&nbsp;€</td>
1345            </tr>";
1346        }
1347
1348        // Grand total row
1349        $totalDiffN1 = $actualsYtd - $actualsN1Ytd;
1350        $totalDiffBudget = $actualsYtd - $budgetYtd;
1351        $totalDiffPrevision = $actualsYtd - $previsionYtd;
1352
1353        $html .= "<tr style='background-color: #fff3cd; font-weight: bold;'>
1354            <td style='border: 1px solid #dee2e6; padding: 8px;'>TOTAL</td>
1355            <td style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>{$fmt($actualsYtd)}&nbsp;€</td>
1356            <td style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>{$fmt($actualsN1Ytd)}&nbsp;€</td>
1357            <td style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>{$fmt($budgetYtd)}&nbsp;€</td>
1358            <td style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>{$fmt($previsionYtd)}&nbsp;€</td>
1359            <td style='border: 1px solid #dee2e6; padding: 8px; text-align: right; color: ".($totalDiffN1 >= 0 ? '#34c38f' : '#f46a6a').";'>".($totalDiffN1 >= 0 ? '+' : '')."{$fmt($totalDiffN1)}&nbsp;€</td>
1360            <td style='border: 1px solid #dee2e6; padding: 8px; text-align: right; color: ".($totalDiffBudget >= 0 ? '#34c38f' : '#f46a6a').";'>".($totalDiffBudget >= 0 ? '+' : '')."{$fmt($totalDiffBudget)}&nbsp;€</td>
1361            <td style='border: 1px solid #dee2e6; padding: 8px; text-align: right; color: ".($totalDiffPrevision >= 0 ? '#34c38f' : '#f46a6a').";'>".($totalDiffPrevision >= 0 ? '+' : '')."{$fmt($totalDiffPrevision)}&nbsp;€</td>
1362        </tr>";
1363
1364        $html .= '</tbody></table>';
1365        $html .= "<p style='margin-top: 20px;'><a href='https://fireservicetitan.com/finance' style='color: #556ee6;'>Podéis ver el detalle de negocio en el siguiente enlace</a></p>";
1366        $html .= "<p style='color: #999; font-size: 11px; margin-top: 20px;'>Generado automáticamente por Fire Service Titan el ".Carbon::now()->format('d/m/Y H:i').'</p>';
1367        $html .= '</div>';
1368
1369        return $html;
1370    }
1371
1372    private function buildMonthlyReportHtml(array $report, int $year, int $month): string
1373    {
1374        $company = $report['company'];
1375        $data = $report['data'];
1376        $monthName = $this->months[$month];
1377
1378        $actualsYtd = $data['actuals_ytd'];
1379        $actualsN1Ytd = $data['actuals_n1_ytd'];
1380        $budgetYtd = $data['budget_ytd'];
1381        $previsionYtd = $data['prevision_ytd'];
1382
1383        $diffN1 = $actualsYtd - $actualsN1Ytd;
1384        $pctN1 = $actualsN1Ytd != 0 ? ($diffN1 / $actualsN1Ytd) * 100 : 0;
1385        $diffBudget = $actualsYtd - $budgetYtd;
1386        $pctBudget = $budgetYtd != 0 ? ($diffBudget / $budgetYtd) * 100 : 0;
1387        $diffPrevision = $actualsYtd - $previsionYtd;
1388        $pctPrevision = $previsionYtd != 0 ? ($diffPrevision / $previsionYtd) * 100 : 0;
1389
1390        $signN1 = $diffN1 >= 0 ? '+' : '';
1391        $signBudget = $diffBudget >= 0 ? '+' : '';
1392        $signPrevision = $diffPrevision >= 0 ? '+' : '';
1393
1394        $colorN1 = $diffN1 >= 0 ? '#34c38f' : '#f46a6a';
1395        $colorBudget = $diffBudget >= 0 ? '#34c38f' : '#f46a6a';
1396        $colorPrevision = $diffPrevision >= 0 ? '#34c38f' : '#f46a6a';
1397
1398        $fmt = fn ($v) => number_format($v, 2, ',', '.');
1399        $fmtPct = fn ($v) => number_format($v, 1, ',', '.');
1400
1401        // Load sede names
1402        $sedeNames = TblFinanceSedes::pluck('name', 'id')->toArray();
1403        // Also map Zenital sede_ids to names
1404        foreach ($this->getZenitalSucursalesMap() as $zenitalId => $info) {
1405            $sedeNames[$info['sede_id']] = $sedeNames[$info['sede_id']] ?? $info['nombre'];
1406            $sedeNames[$zenitalId] = $info['nombre'];
1407        }
1408
1409        // Part 1: Monthly = YTD global photo
1410        $html = "
1411        <div style='font-family: Arial, sans-serif; max-width: 900px; margin: 0 auto; color: #333;'>
1412            <p>Hola a todos,</p>
1413            <p>Os comparto el resumen global de facturación acumulada hasta <strong>{$monthName} {$year}</strong></p>
1414
1415            <div style='background-color: #f8f9fa; border-radius: 8px; padding: 20px; margin: 20px 0;'>
1416                <h3 style='margin: 0 0 12px 0; color: #495057;'>Acumulado Enero – {$monthName} {$year} (YTD)</h3>
1417                <ul style='list-style: none; padding: 0; margin: 0; line-height: 2;'>
1418                    <li>
1419                        <strong>Facturación total acumulada {$monthName} {$year}:</strong>
1420                        <span style='font-size: 16px; font-weight: bold;'>{$fmt($actualsYtd)}&nbsp;€</span>
1421                    </li>
1422                    <li>
1423                        vs. Acumulado Enero – {$monthName} ".($year - 1).":
1424                        <span style='color: {$colorN1}; font-weight: bold;'>{$signN1}{$fmt($diffN1)}&nbsp;€ ({$signN1}{$fmtPct($pctN1)}%)</span>
1425                    </li>
1426                    <li>
1427                        vs. Budget {$monthName} {$year}:
1428                        <span style='color: {$colorBudget}; font-weight: bold;'>{$signBudget}{$fmt($diffBudget)}&nbsp;€ ({$signBudget}{$fmtPct($pctBudget)}%)</span>
1429                        respecto al objetivo
1430                    </li>
1431                    <li>
1432                        vs. Previsión {$monthName} {$year}:
1433                        <span style='color: {$colorPrevision}; font-weight: bold;'>{$signPrevision}{$fmt($diffPrevision)}&nbsp;€ ({$signPrevision}{$fmtPct($pctPrevision)}%)</span>
1434                        respecto al objetivo
1435                    </li>
1436                </ul>
1437            </div>
1438
1439            <h3 style='color: #495057; margin-top: 30px;'>Detalle por sede — Cierre Mensual</h3>
1440            <table style='border-collapse: collapse; width: 100%; font-size: 13px;'>
1441                <thead>
1442                    <tr style='background-color: #f8f9fa;'>
1443                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: left;'>Sede</th>
1444                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>Actuals</th>
1445                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>n-1</th>
1446                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>Budget</th>
1447                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>Previsión</th>
1448                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>vs n-1</th>
1449                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>vs Budget</th>
1450                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>vs Previsión</th>
1451                    </tr>
1452                </thead>
1453                <tbody>";
1454
1455        $bySede = $data['by_sede'];
1456        uasort($bySede, fn ($a, $b) => ($b['actuals'] ?? 0) <=> ($a['actuals'] ?? 0) ?: ($b['budget'] ?? 0) <=> ($a['budget'] ?? 0));
1457
1458        foreach ($bySede as $sedeId => $vals) {
1459            $sedeName = $sedeNames[$sedeId] ?? "Sede {$sedeId}";
1460            $sedeActuals = $vals['actuals'];
1461            $sedeN1 = $vals['actuals_n1'];
1462            $sedeBudget = $vals['budget'];
1463            $sedePrevision = $vals['prevision'] ?? 0;
1464
1465            $sedeDiffN1 = $sedeActuals - $sedeN1;
1466            $sedeDiffBudget = $sedeActuals - $sedeBudget;
1467            $sedeDiffPrevision = $sedeActuals - $sedePrevision;
1468
1469            $colorSN1 = $sedeDiffN1 >= 0 ? '#34c38f' : '#f46a6a';
1470            $colorSB = $sedeDiffBudget >= 0 ? '#34c38f' : '#f46a6a';
1471            $colorSP = $sedeDiffPrevision >= 0 ? '#34c38f' : '#f46a6a';
1472            $signSN1 = $sedeDiffN1 >= 0 ? '+' : '';
1473            $signSB = $sedeDiffBudget >= 0 ? '+' : '';
1474            $signSP = $sedeDiffPrevision >= 0 ? '+' : '';
1475
1476            $html .= "<tr>
1477                <td style='border: 1px solid #dee2e6; padding: 6px 8px;'>{$sedeName}</td>
1478                <td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right;'>{$fmt($sedeActuals)}&nbsp;€</td>
1479                <td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right;'>{$fmt($sedeN1)}&nbsp;€</td>
1480                <td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right;'>{$fmt($sedeBudget)}&nbsp;€</td>
1481                <td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right;'>{$fmt($sedePrevision)}&nbsp;€</td>
1482                <td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right; color: {$colorSN1};'>{$signSN1}{$fmt($sedeDiffN1)}&nbsp;€</td>
1483                <td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right; color: {$colorSB};'>{$signSB}{$fmt($sedeDiffBudget)}&nbsp;€</td>
1484                <td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right; color: {$colorSP};'>{$signSP}{$fmt($sedeDiffPrevision)}&nbsp;€</td>
1485            </tr>";
1486        }
1487
1488        // Grand total row
1489        $totalDiffN1 = $actualsYtd - $actualsN1Ytd;
1490        $totalDiffBudget = $actualsYtd - $budgetYtd;
1491        $totalDiffPrevision = $actualsYtd - $previsionYtd;
1492
1493        $html .= "<tr style='background-color: #fff3cd; font-weight: bold;'>
1494            <td style='border: 1px solid #dee2e6; padding: 8px;'>TOTAL</td>
1495            <td style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>{$fmt($actualsYtd)}&nbsp;€</td>
1496            <td style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>{$fmt($actualsN1Ytd)}&nbsp;€</td>
1497            <td style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>{$fmt($budgetYtd)}&nbsp;€</td>
1498            <td style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>{$fmt($previsionYtd)}&nbsp;€</td>
1499            <td style='border: 1px solid #dee2e6; padding: 8px; text-align: right; color: ".($totalDiffN1 >= 0 ? '#34c38f' : '#f46a6a').";'>".($totalDiffN1 >= 0 ? '+' : '')."{$fmt($totalDiffN1)}&nbsp;€</td>
1500            <td style='border: 1px solid #dee2e6; padding: 8px; text-align: right; color: ".($totalDiffBudget >= 0 ? '#34c38f' : '#f46a6a').";'>".($totalDiffBudget >= 0 ? '+' : '')."{$fmt($totalDiffBudget)}&nbsp;€</td>
1501            <td style='border: 1px solid #dee2e6; padding: 8px; text-align: right; color: ".($totalDiffPrevision >= 0 ? '#34c38f' : '#f46a6a').";'>".($totalDiffPrevision >= 0 ? '+' : '')."{$fmt($totalDiffPrevision)}&nbsp;€</td>
1502        </tr>";
1503
1504        $html .= '</tbody></table>';
1505        $html .= "<p style='margin-top: 20px;'><a href='https://fireservicetitan.com/finance' style='color: #556ee6;'>Podéis ver el detalle de negocio en el siguiente enlace</a></p>";
1506        $html .= "<p style='color: #999; font-size: 11px; margin-top: 20px;'>Generado automáticamente por Fire Service Titan el ".Carbon::now()->format('d/m/Y H:i').'</p>';
1507        $html .= '</div>';
1508
1509        return $html;
1510    }
1511
1512    private function buildAllRegionsReportHtml(array $report, int $year, int $month, string $reportLabel, bool $isClosed = false): string
1513    {
1514        $isMonthly = $reportLabel === 'Cierre Mensual';
1515        $data = $report['data'];
1516        $monthName = $this->months[$month];
1517
1518        $actualsYtd = $data['actuals_ytd'];
1519        $actualsN1Ytd = $data['actuals_n1_ytd'];
1520        $budgetYtd = $data['budget_ytd'];
1521        $previsionYtd = $data['prevision_ytd'];
1522
1523        $diffN1 = $actualsYtd - $actualsN1Ytd;
1524        $pctN1 = $actualsN1Ytd != 0 ? ($diffN1 / $actualsN1Ytd) * 100 : 0;
1525        $diffBudget = $actualsYtd - $budgetYtd;
1526        $pctBudget = $budgetYtd != 0 ? ($diffBudget / $budgetYtd) * 100 : 0;
1527        $diffPrevision = $actualsYtd - $previsionYtd;
1528        $pctPrevision = $previsionYtd != 0 ? ($diffPrevision / $previsionYtd) * 100 : 0;
1529
1530        $signN1 = $diffN1 >= 0 ? '+' : '';
1531        $signBudget = $diffBudget >= 0 ? '+' : '';
1532        $signPrevision = $diffPrevision >= 0 ? '+' : '';
1533
1534        $colorN1 = $diffN1 >= 0 ? '#34c38f' : '#f46a6a';
1535        $colorBudget = $diffBudget >= 0 ? '#34c38f' : '#f46a6a';
1536        $colorPrevision = $diffPrevision >= 0 ? '#34c38f' : '#f46a6a';
1537
1538        $fmt = fn ($v) => number_format($v, 2, ',', '.');
1539        $fmtPct = fn ($v) => number_format($v, 1, ',', '.');
1540
1541        $introText = $isMonthly
1542            ? "Os comparto el resumen global de facturación acumulada hasta <strong>{$monthName} {$year}</strong> — Todas las regiones"
1543            : ($isClosed
1544                ? "Os comparto el cierre de facturación del mes de <strong>{$monthName} {$year}</strong> — Todas las regiones"
1545                : "Os comparto el resumen de la facturación del mes de <strong>{$monthName}</strong> hasta la fecha — Todas las regiones");
1546        $ytdLabel = $isMonthly
1547            ? "Acumulado Enero – {$monthName} {$year} (YTD)"
1548            : "Facturación {$monthName} {$year} (".($isClosed ? 'cierre' : 'mes en curso').')';
1549        $vsN1Label = $isMonthly
1550            ? "vs. Acumulado Enero – {$monthName} ".($year - 1)
1551            : "vs. {$monthName} ".($year - 1);
1552        $totalLabel = $isMonthly
1553            ? "Facturación total acumulada {$monthName} {$year}"
1554            : "Facturación {$monthName} {$year}";
1555
1556        $html = "
1557        <div style='font-family: Arial, sans-serif; max-width: 900px; margin: 0 auto; color: #333;'>
1558            <p>Hola a todos,</p>
1559            <p>{$introText}</p>
1560
1561            <div style='background-color: #f8f9fa; border-radius: 8px; padding: 20px; margin: 20px 0;'>
1562                <h3 style='margin: 0 0 12px 0; color: #495057;'>{$ytdLabel}</h3>
1563                <ul style='list-style: none; padding: 0; margin: 0; line-height: 2;'>
1564                    <li>
1565                        <strong>{$totalLabel}:</strong>
1566                        <span style='font-size: 16px; font-weight: bold;'>{$fmt($actualsYtd)}&nbsp;€</span>
1567                    </li>
1568                    <li>
1569                        {$vsN1Label}:
1570                        ".($actualsN1Ytd == 0
1571                            ? "<span style='color: #999;'>—</span>"
1572                            : "<span style='color: {$colorN1}; font-weight: bold;'>".($diffN1 >= 0 ? '▲' : '▼')." {$signN1}{$fmtPct($pctN1)}%</span>")."
1573                    </li>
1574                    <li>
1575                        vs. Budget {$monthName} {$year}:
1576                        ".($budgetYtd == 0
1577                            ? "<span style='color: #999;'>—</span>"
1578                            : "<span style='color: {$colorBudget}; font-weight: bold;'>".($diffBudget >= 0 ? '▲' : '▼')." {$signBudget}{$fmtPct($pctBudget)}%</span>").'
1579                    </li>';
1580
1581        // vs Previsión only shown in the weekly (non-monthly) section
1582        if (! $isMonthly) {
1583            $html .= "
1584                    <li>
1585                        vs. Previsión {$monthName} {$year}:
1586                        ".($previsionYtd == 0
1587                            ? "<span style='color: #999;'>—</span>"
1588                            : "<span style='color: {$colorPrevision}; font-weight: bold;'>".($diffPrevision >= 0 ? '▲' : '▼')." {$signPrevision}{$fmtPct($pctPrevision)}%</span>").'
1589                    </li>';
1590        }
1591
1592        $html .= "
1593                </ul>
1594            </div>
1595
1596            <h3 style='color: #495057; margin-top: 30px;'>Detalle por región — {$reportLabel}</h3>
1597            <table style='border-collapse: collapse; width: 100%; font-size: 13px;'>
1598                <thead>
1599                    <tr style='background-color: #f8f9fa;'>
1600                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: left;'>Región</th>
1601                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>Actuals</th>
1602                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>n-1</th>
1603                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>Budget</th>".
1604                        (! $isMonthly ? "<th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>Previsión</th>" : '')."
1605                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>vs n-1</th>
1606                        <th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>vs Budget</th>".
1607                        (! $isMonthly ? "<th style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>vs Previsión</th>" : '').'
1608                    </tr>
1609                </thead>
1610                <tbody>';
1611
1612        $byRegion = $data['by_region'];
1613        uasort($byRegion, fn ($a, $b) => ($b['actuals'] ?? 0) <=> ($a['actuals'] ?? 0));
1614
1615        // Render a % delta cell: if baseline is 0 (no data), show "—" in gray
1616        $pctCell = function ($actual, $baseline) use ($fmtPct) {
1617            if ($baseline == 0) {
1618                return "<td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right; color: #999;'>—</td>";
1619            }
1620            $pct = (($actual - $baseline) / $baseline) * 100;
1621            $color = $pct >= 0 ? '#34c38f' : '#f46a6a';
1622            $arrow = $pct >= 0 ? '▲' : '▼';
1623            $sign = $pct >= 0 ? '+' : '';
1624
1625            return "<td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right; color: {$color}; font-weight: bold;'>{$arrow} {$sign}{$fmtPct($pct)}%</td>";
1626        };
1627
1628        foreach ($byRegion as $companyId => $vals) {
1629            $regionName = $vals['name'];
1630            $rActuals = $vals['actuals'];
1631            $rN1 = $vals['actuals_n1'];
1632            $rBudget = $vals['budget'];
1633            $rPrevision = $vals['prevision'];
1634
1635            $html .= "<tr>
1636                <td style='border: 1px solid #dee2e6; padding: 6px 8px; font-weight: bold;'>{$regionName}</td>
1637                <td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right;'>{$fmt($rActuals)}&nbsp;€</td>
1638                <td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right;'>{$fmt($rN1)}&nbsp;€</td>
1639                <td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right;'>{$fmt($rBudget)}&nbsp;€</td>".
1640                (! $isMonthly ? "<td style='border: 1px solid #dee2e6; padding: 6px 8px; text-align: right;'>{$fmt($rPrevision)}&nbsp;€</td>" : '').
1641                $pctCell($rActuals, $rN1).
1642                $pctCell($rActuals, $rBudget).
1643                (! $isMonthly ? $pctCell($rActuals, $rPrevision) : '').'
1644            </tr>';
1645        }
1646
1647        // Grand total row — use same pctCell helper
1648        $totalPctCell = function ($actual, $baseline) use ($fmtPct) {
1649            if ($baseline == 0) {
1650                return "<td style='border: 1px solid #dee2e6; padding: 8px; text-align: right; color: #999;'>—</td>";
1651            }
1652            $pct = (($actual - $baseline) / $baseline) * 100;
1653            $color = $pct >= 0 ? '#34c38f' : '#f46a6a';
1654            $arrow = $pct >= 0 ? '▲' : '▼';
1655            $sign = $pct >= 0 ? '+' : '';
1656
1657            return "<td style='border: 1px solid #dee2e6; padding: 8px; text-align: right; color: {$color};'>{$arrow} {$sign}{$fmtPct($pct)}%</td>";
1658        };
1659
1660        $html .= "<tr style='background-color: #fff3cd; font-weight: bold;'>
1661            <td style='border: 1px solid #dee2e6; padding: 8px;'>TOTAL GRUPO FIRE</td>
1662            <td style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>{$fmt($actualsYtd)}&nbsp;€</td>
1663            <td style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>{$fmt($actualsN1Ytd)}&nbsp;€</td>
1664            <td style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>{$fmt($budgetYtd)}&nbsp;€</td>".
1665            (! $isMonthly ? "<td style='border: 1px solid #dee2e6; padding: 8px; text-align: right;'>{$fmt($previsionYtd)}&nbsp;€</td>" : '').
1666            $totalPctCell($actualsYtd, $actualsN1Ytd).
1667            $totalPctCell($actualsYtd, $budgetYtd).
1668            (! $isMonthly ? $totalPctCell($actualsYtd, $previsionYtd) : '').'
1669        </tr>';
1670
1671        $html .= '</tbody></table>';
1672        $html .= "<p style='margin-top: 20px;'><a href='https://fireservicetitan.com/finance' style='color: #556ee6;'>Podéis ver el detalle de negocio en el siguiente enlace</a></p>";
1673        $html .= "<p style='color: #999; font-size: 11px; margin-top: 20px;'>Generado automáticamente por Fire Service Titan el ".Carbon::now()->format('d/m/Y H:i').'</p>';
1674        $html .= '</div>';
1675
1676        return $html;
1677    }
1678
1679    /**
1680     * Combined per-company report: single month table + YTD accumulated table.
1681     *
1682     * @param  bool  $isClosed  true when the reported month is closed (monthly email)
1683     */
1684    private function buildCombinedReportHtml(array $report, int $year, int $month, bool $isClosed = false): string
1685    {
1686        $weeklyReport = ['company' => $report['company'], 'data' => $report['weekly']];
1687        $monthlyReport = ['company' => $report['company'], 'data' => $report['monthly']];
1688
1689        $weeklyHtml = $this->buildReportHtml($weeklyReport, $year, $month, $isClosed);
1690        $monthlyHtml = $this->buildMonthlyReportHtml($monthlyReport, $year, $month);
1691
1692        $monthlyBody = $this->extractReportBody($monthlyHtml);
1693
1694        $footerMarker = "<p style='margin-top: 20px;'><a href='https://fireservicetitan.com/finance'";
1695        $combined = str_replace(
1696            $footerMarker,
1697            "<hr style='border: none; border-top: 2px solid #dee2e6; margin: 40px 0;'>".$monthlyBody.$footerMarker,
1698            $weeklyHtml
1699        );
1700
1701        return $combined;
1702    }
1703
1704    /**
1705     * Combined all-regions report: single month table + YTD accumulated table.
1706     *
1707     * @param  bool  $isClosed  true when the reported month is closed (monthly email)
1708     */
1709    private function buildCombinedAllRegionsReportHtml(array $report, int $year, int $month, bool $isClosed = false): string
1710    {
1711        $weeklyReport = ['company' => $report['company'], 'data' => $report['data']['weekly']];
1712        $monthlyReport = ['company' => $report['company'], 'data' => $report['data']['monthly']];
1713
1714        $weeklyLabel = $isClosed ? 'Cierre' : 'Report Semanal';
1715        $weeklyHtml = $this->buildAllRegionsReportHtml($weeklyReport, $year, $month, $weeklyLabel, $isClosed);
1716        $monthlyHtml = $this->buildAllRegionsReportHtml($monthlyReport, $year, $month, 'Cierre Mensual');
1717
1718        $monthlyBody = $this->extractReportBody($monthlyHtml);
1719
1720        $footerMarker = "<p style='margin-top: 20px;'><a href='https://fireservicetitan.com/finance'";
1721        $combined = str_replace(
1722            $footerMarker,
1723            "<hr style='border: none; border-top: 2px solid #dee2e6; margin: 40px 0;'>".$monthlyBody.$footerMarker,
1724            $weeklyHtml
1725        );
1726
1727        return $combined;
1728    }
1729
1730    /**
1731     * Extract the report body (summary card + table) from a full report HTML,
1732     * stripping the outer div, greeting, and footer.
1733     */
1734    private function extractReportBody(string $html): string
1735    {
1736        // Remove everything before the first summary card
1737        $start = strpos($html, "<div style='background-color: #f8f9fa; border-radius: 8px;");
1738        if ($start === false) {
1739            return $html;
1740        }
1741
1742        // Remove the closing </div> (outer wrapper) and the footer
1743        $footerStart = strpos($html, "<p style='margin-top: 20px;'><a href='https://fireservicetitan.com/finance'");
1744        if ($footerStart === false) {
1745            $footerStart = strrpos($html, '</div>');
1746        }
1747
1748        return substr($html, $start, $footerStart - $start);
1749    }
1750}