Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.11%
1 / 889
5.26%
1 / 19
 CRAP
0.00%
0 / 1
SendFinanceReport
0.11%
1 / 889
5.26%
1 / 19
 50679.35
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 renderPreview
0.00%
0 / 10
0.00%
0 / 1
 12
 buildPreviewHtml
0.00%
0 / 33
0.00%
0 / 1
 132
 handle
0.00%
0 / 111
0.00%
0 / 1
 1056
 getReportMonth
0.00%
0 / 23
0.00%
0 / 1
 132
 getResumenData
0.00%
0 / 152
0.00%
0 / 1
 1122
 getZenitalSkRegionsForCompany
0.00%
0 / 14
0.00%
0 / 1
 20
 seriesAttributionMap
0.00%
0 / 29
0.00%
0 / 1
 2
 sedeSimplificationMap
0.00%
0 / 8
0.00%
0 / 1
 2
 jorgeRegionWhitelist
0.00%
0 / 43
0.00%
0 / 1
 2
 getZenitalRegionMap
0.00%
0 / 1
0.00%
0 / 1
 2
 getZenitalSucursalesMap
0.00%
0 / 1
0.00%
0 / 1
 2
 aggregateAllReports
0.00%
0 / 156
0.00%
0 / 1
 870
 buildReportHtml
0.00%
0 / 85
0.00%
0 / 1
 756
 buildMonthlyReportHtml
0.00%
0 / 81
0.00%
0 / 1
 650
 buildAllRegionsReportHtml
0.00%
0 / 109
0.00%
0 / 1
 1482
 buildCombinedReportHtml
0.00%
0 / 12
0.00%
0 / 1
 2
 buildCombinedAllRegionsReportHtml
0.00%
0 / 13
0.00%
0 / 1
 6
 extractReportBody
0.00%
0 / 7
0.00%
0 / 1
 12
1<?php
2
3namespace App\Console\Commands;
4
5use App\Models\TblCompanies;
6use App\Models\TblFinanceBudgetAnual;
7use App\Models\TblFinanceMonthConfig;
8use App\Models\TblFinancePrevisionAnual;
9use App\Models\TblFinanceRegions;
10use App\Models\TblFinanceReportRecipients;
11use App\Models\TblFinanceReportSemanal;
12use App\Models\TblFinanceResumenAnual;
13use App\Models\TblFinanceSedes;
14use App\Jobs\Email\SendFinanceRecipientEmail;
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;
22use SendGrid\Mail\Mail;
23
24class SendFinanceReport extends Command
25{
26    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}';
27
28    protected $description = 'Send the weekly finance report to all active recipients';
29
30    public function __construct(private ZenitalCatalog $zenital)
31    {
32        parent::__construct();
33    }
34
35    /**
36     * FIRE-1148: returns the all-regions aggregated HTML that `--test` mode
37     * currently echoes to stdout. Used by the "Vista previa" path in
38     * FinanceController::test_report so the preview doesn't have to spin
39     * up the full pipeline via Artisan::call (which iterates every recipient,
40     * builds every per-company body, and is race-prone via Artisan::output()).
41     *
42     * Deliberately calls the EXACT SAME private build methods the cron uses,
43     * so the HTML the user sees in the preview is byte-identical to the
44     * email body the recipients will receive.
45     *
46     * FIRE-1148 follow-up: cached for 5 minutes in the 'finance' domain. The
47     * preview is a snapshot of the all-regions email body — 5 min of stale-
48     * ness is acceptable (no live financial editing happens in this view),
49     * and any sede mutation invalidates the whole 'finance' domain through
50     * ensureSedesExist. Drops a cold ~2.5 s render to a Redis HIT (< 5 ms)
51     * for every request inside the TTL window.
52     */
53    public function renderPreview(?int $month = null, string $type = 'weekly'): string
54    {
55        if ($month !== null) {
56            $year = (int) date('Y');
57        } elseif ($type === 'monthly') {
58            $prev = Carbon::now()->subMonth();
59            $month = $prev->month;
60            $year = $prev->year;
61        } else {
62            $month = (int) date('n');
63            $year = (int) date('Y');
64        }
65
66        $cacheKey = sprintf('finance.preview.%s.%d.%d', $type, $year, $month);
67
68        return ResultCache::rememberKey('finance', $cacheKey, 300, fn (): string => $this->buildPreviewHtml($year, $month, $type));
69    }
70
71    /**
72     * The actual preview-render work. Always called inside the cache wrapper
73     * in renderPreview() — kept as a separate method so the cached path is
74     * trivially auditable and so tests can target the un-cached build.
75     */
76    private function buildPreviewHtml(int $year, int $month, string $type): string
77    {
78        // Same finance-companies discovery the cron uses for the all-regions
79        // recipient branch (see handle() L101-107).
80        $companyIds = TblFinanceSedes::where('is_active', 1)
81            ->pluck('company_id')
82            ->unique()
83            ->toArray();
84
85        $reportsByCompany = [];
86        foreach ($companyIds as $companyId) {
87            $company = TblCompanies::find($companyId);
88            if (! $company) {
89                continue;
90            }
91
92            $monthData = $this->getResumenData($companyId, $year, $month, 'weekly');
93            $ytdData = $type === 'monthly'
94                ? $this->getResumenData($companyId, $year, $month, 'monthly')
95                : null;
96
97            $hasData = ! empty($monthData['actuals_ytd']) || ! empty($monthData['by_sede'])
98                || ($ytdData && (! empty($ytdData['actuals_ytd']) || ! empty($ytdData['by_sede'])));
99            if (! $hasData) {
100                continue;
101            }
102
103            $reportsByCompany[$companyId] = [
104                'company' => $company,
105                'weekly' => $monthData,
106                'monthly' => $ytdData,
107            ];
108        }
109
110        if (empty($reportsByCompany)) {
111            return '<p>No finance data available for current year/month.</p>';
112        }
113
114        $aggregatedData = $this->aggregateAllReports($reportsByCompany, $type);
115        $aggregatedReport = [
116            'company' => (object) ['name' => 'Grupo Fire (Todas las regiones)'],
117            'data' => $aggregatedData,
118        ];
119
120        if ($type === 'monthly') {
121            return $this->buildCombinedAllRegionsReportHtml($aggregatedReport, $year, $month, true);
122        }
123
124        $singleAgg = ['company' => $aggregatedReport['company'], 'data' => $aggregatedData['weekly']];
125
126        return $this->buildAllRegionsReportHtml($singleAgg, $year, $month, 'Report Semanal');
127    }
128
129    private $months = [
130        1 => 'Enero', 2 => 'Febrero', 3 => 'Marzo', 4 => 'Abril',
131        5 => 'Mayo', 6 => 'Junio', 7 => 'Julio', 8 => 'Agosto',
132        9 => 'Septiembre', 10 => 'Octubre', 11 => 'Noviembre', 12 => 'Diciembre',
133    ];
134
135    public function handle(): int
136    {
137        $type = $this->option('type') ?? 'weekly';
138        $isTest = $this->option('test');
139
140        // Determine the target month based on report type:
141        // - weekly: current month (month-to-date view, sent every Saturday)
142        // - monthly: previous month closed (sent on WD+5 of the following month)
143        // Manual --month overrides both defaults (used for preview/testing).
144        if ($this->option('month')) {
145            $month = (int) $this->option('month');
146            $year = (int) date('Y');
147        } elseif ($type === 'monthly') {
148            // Previous month closed
149            $prevMonth = Carbon::now()->subMonth();
150            $month = $prevMonth->month;
151            $year = $prevMonth->year;
152        } else {
153            // Current month for weekly MTD
154            $month = (int) date('n');
155            $year = (int) date('Y');
156        }
157
158        $recipients = TblFinanceReportRecipients::where('is_active', 1)->get();
159
160        if ($recipients->isEmpty()) {
161            $this->info('No active recipients found.');
162
163            return 0;
164        }
165
166        // Berta audit follow-up — Bug C: a single email can appear in
167        // multiple recipient rows (one for "Todas las regiones" + one
168        // per specific company), which makes that person receive several
169        // copies of essentially the same digest. Dedupe by lower-cased
170        // email; when there's a NULL company_id row we keep it (broader
171        // = the all-regions summary the operator usually wants), else
172        // we keep the row with the lowest id.
173        $byEmail = [];
174        foreach ($recipients as $r) {
175            $key = mb_strtolower(trim((string) $r->email));
176            if ($key === '') {
177                continue;
178            }
179            if (! isset($byEmail[$key])) {
180                $byEmail[$key] = $r;
181
182                continue;
183            }
184            $kept = $byEmail[$key];
185            if ($kept->company_id !== null && $r->company_id === null) {
186                $byEmail[$key] = $r;
187            } elseif ($kept->company_id !== null && $r->company_id !== null && $r->id < $kept->id) {
188                $byEmail[$key] = $r;
189            }
190        }
191        $recipients = collect(array_values($byEmail));
192
193        $companyIds = $recipients->pluck('company_id')->filter()->unique()->toArray();
194
195        // If any recipient has NULL company_id (all regions), include all finance companies
196        $hasAllRegionRecipient = $recipients->contains(fn ($r) => $r->company_id === null);
197        if ($hasAllRegionRecipient) {
198            $financeCompanyIds = TblFinanceSedes::where('is_active', 1)
199                ->pluck('company_id')
200                ->unique()
201                ->toArray();
202            $companyIds = array_values(array_unique(array_merge($companyIds, $financeCompanyIds)));
203        }
204
205        $reportsByCompany = [];
206        foreach ($companyIds as $companyId) {
207            $company = TblCompanies::find($companyId);
208            if (! $company) {
209                continue;
210            }
211
212            // weekly = MTD (single month). monthly = single month + YTD
213            $monthData = $this->getResumenData($companyId, $year, $month, 'weekly');
214            $ytdData = $type === 'monthly'
215                ? $this->getResumenData($companyId, $year, $month, 'monthly')
216                : null;
217
218            $hasData = ! empty($monthData['actuals_ytd']) || ! empty($monthData['by_sede'])
219                || ($ytdData && (! empty($ytdData['actuals_ytd']) || ! empty($ytdData['by_sede'])));
220            if (! $hasData) {
221                continue;
222            }
223
224            $reportsByCompany[$companyId] = [
225                'company' => $company,
226                'weekly' => $monthData,
227                'monthly' => $ytdData,
228            ];
229        }
230
231        if (empty($reportsByCompany)) {
232            $this->info('No finance data available for current year/month.');
233
234            return 0;
235        }
236
237        // Separate "all regions" recipients (NULL company_id) from company-specific ones
238        $allRegionRecipients = $recipients->filter(fn ($r) => $r->company_id === null);
239        $companyRecipients = $recipients->filter(fn ($r) => $r->company_id !== null);
240
241        $recipientsByCompany = $companyRecipients->groupBy('company_id');
242
243        $sent = 0;
244        foreach ($recipientsByCompany as $companyId => $companyGroupRecipients) {
245            if (! isset($reportsByCompany[$companyId])) {
246                continue;
247            }
248
249            $report = $reportsByCompany[$companyId];
250            if ($type === 'monthly') {
251                $html = $this->buildCombinedReportHtml($report, $year, $month, true);
252                $subject = "Cierre Facturación - {$report['company']->name} - {$this->months[$month]} {$year}";
253            } else {
254                $singleReport = ['company' => $report['company'], 'data' => $report['weekly']];
255                $html = $this->buildReportHtml($singleReport, $year, $month);
256                $subject = "Reporte Semanal - {$report['company']->name} - {$this->months[$month]} {$year}";
257            }
258
259            if ($isTest) {
260                $this->info("--- Report for {$report['company']->name} (recipients: {$companyGroupRecipients->pluck('email')->implode(', ')}) ---");
261                $sent += $companyGroupRecipients->count();
262
263                continue;
264            }
265
266            foreach ($companyGroupRecipients as $recipient) {
267                // FIRE-1148: dispatched to the 'email' queue. The worker
268                // (FIRE-1147 infra) calls SendGrid, logs to SendgridLogger,
269                // and retries on 4xx/5xx via AbstractSendgridJob. The
270                // command no longer waits for the SendGrid roundtrip per
271                // recipient — the per-cron wall time drops from minutes
272                // to seconds.
273                SendFinanceRecipientEmail::dispatch(
274                    $recipient->email,
275                    $recipient->name,
276                    $subject,
277                    $html,
278                )->onQueue('email');
279                $sent++;
280                $this->info("Dispatched to {$recipient->email}");
281            }
282        }
283
284        // Send aggregated report to "all regions" recipients
285        if ($allRegionRecipients->isNotEmpty() && ! empty($reportsByCompany)) {
286            $aggregatedData = $this->aggregateAllReports($reportsByCompany, $type);
287            $aggregatedReport = ['company' => (object) ['name' => 'Grupo Fire (Todas las regiones)'], 'data' => $aggregatedData];
288
289            if ($type === 'monthly') {
290                $aggregatedHtml = $this->buildCombinedAllRegionsReportHtml($aggregatedReport, $year, $month, true);
291                $aggregatedSubject = "Cierre Facturación - Grupo Fire - {$this->months[$month]} {$year}";
292            } else {
293                $singleAgg = ['company' => $aggregatedReport['company'], 'data' => $aggregatedData['weekly']];
294                $aggregatedHtml = $this->buildAllRegionsReportHtml($singleAgg, $year, $month, 'Report Semanal');
295                $aggregatedSubject = "Reporte Semanal - Grupo Fire - {$this->months[$month]} {$year}";
296            }
297
298            if ($isTest) {
299                $this->info('--- Aggregated Report for Grupo Fire (Todas las regiones) ---');
300                $this->info('Recipients: '.$allRegionRecipients->pluck('email')->implode(', '));
301                $this->line($aggregatedHtml);
302                $sent += $allRegionRecipients->count();
303            } else {
304                foreach ($allRegionRecipients as $recipient) {
305                    // FIRE-1148: dispatched to the 'email' queue, same as
306                    // the per-company branch above.
307                    SendFinanceRecipientEmail::dispatch(
308                        $recipient->email,
309                        $recipient->name,
310                        $aggregatedSubject,
311                        $aggregatedHtml,
312                    )->onQueue('email');
313                    $sent++;
314                    $this->info("Dispatched to {$recipient->email}");
315                }
316            }
317        }
318
319        // FIRE-1148: "sent" is now "dispatched to the email queue" — actual
320        // SendGrid delivery happens in the worker. Worded the same way so
321        // the existing operational logs / dashboards keep matching.
322        $this->info("Finance report sent to {$sent} recipients.");
323        Log::channel('third-party')->info("finance:send-report — Dispatched to {$sent} recipients.");
324
325        return 0;
326    }
327
328    /**
329     * Determine which month to report on.
330     *
331     * Priority:
332     * 1. force_report_month override from tbl_finance_month_config (current month row)
333     * 2. Previous month's close_date — if today <= close_date, still report previous month
334     * 3. Default WD+5 logic: if <= 5 working days have passed, report on previous month
335     */
336    private function getReportMonth(): int
337    {
338        $today = Carbon::now();
339        $year = $today->year;
340        $currentMonth = $today->month;
341
342        // Check for force override on current month
343        $config = TblFinanceMonthConfig::where('year', $year)
344            ->where('month', $currentMonth)
345            ->first();
346
347        if ($config && $config->force_report_month) {
348            return (int) $config->force_report_month;
349        }
350
351        // Check previous month's close_date
352        $prevMonth = $currentMonth === 1 ? 12 : $currentMonth - 1;
353        $prevYear = $currentMonth === 1 ? $year - 1 : $year;
354        $prevConfig = TblFinanceMonthConfig::where('year', $prevYear)
355            ->where('month', $prevMonth)
356            ->first();
357
358        if ($prevConfig && $prevConfig->close_date) {
359            // If today is before the close date, still report on previous month
360            if ($today->lte(Carbon::parse($prevConfig->close_date))) {
361                return $prevMonth;
362            }
363
364            return $currentMonth;
365        }
366
367        // Default: WD+5 logic
368        $workingDays = 0;
369        for ($day = 1; $day <= $today->day; $day++) {
370            $date = Carbon::create($today->year, $currentMonth, $day);
371            if ($date->isWeekday()) {
372                $workingDays++;
373            }
374        }
375
376        return $workingDays <= 5 ? $prevMonth : $currentMonth;
377    }
378
379    /**
380     * Fetch resumen data from Zenital + MySQL (same logic as FinanceController::list_resumen).
381     */
382    public function getResumenData(int $companyId, int $year, int $month, string $type = 'monthly'): array
383    {
384        $isWeekly = $type === 'weekly';
385
386        // Build Zenital sede map for this company, translating to local tbl_finance_sedes IDs
387        $zenitalMap = $this->getZenitalSucursalesMap();
388        $localSedes = TblFinanceSedes::where('company_id', $companyId)
389            ->where('is_active', 1)
390            ->pluck('id', 'name')
391            ->toArray();
392
393        $zenitalToSede = [];
394        foreach ($zenitalMap as $zenitalId => $info) {
395            if ($info['company_id'] === $companyId) {
396                // Map to local sede ID by name match, fallback to Zenital sede_id
397                $zenitalToSede[$zenitalId] = $localSedes[$info['nombre']] ?? $info['sede_id'];
398            }
399        }
400
401        // Query Zenital for actuals (current year + n-1)
402        // Weekly = current month only; Monthly = year-to-date
403        //
404        // Berta audit follow-up — Bug E: pre-fix, this method was called
405        // once per company × once per month/YTD, and each Zenital query
406        // could hang for ~30s on a trans-Atlantic round trip with no
407        // timeout. The whole monthly preview took ~11 minutes locally
408        // — Berta's browser was timing out long before the email
409        // generation finished, and the preview pane fell back to the
410        // last-rendered (weekly) HTML, making it look like "weekly =
411        // monthly". Same PGCONNECT_TIMEOUT + statement_timeout pattern
412        // as FinanceController::list_resumen.
413        putenv('PGCONNECT_TIMEOUT='.env('ZENITAL_DB_CONNECT_TIMEOUT', 5));
414        $idx = [];
415        if (! empty($zenitalToSede)) {
416            try {
417                DB::connection('zenital')->statement('SET statement_timeout = '.(int) env('ZENITAL_DB_STATEMENT_TIMEOUT_MS', 15000));
418
419                // Per-invoice dedup before summing: Zenital STG had every
420                // Alcarrena invoice recorded twice in fact_facturacion under
421                // the same sk_sucursal=150 (6,459 rows vs 3,285 distinct
422                // num_factura — exactly 2x). A naive SUM doubled La Mancha
423                // from 1,36 M€ to 2,73 M€ on the YTD email. Collapse to one
424                // row per (num_factura, sk_sucursal, ano) by taking MAX of
425                // the amount (duplicates carry the same value), THEN SUM.
426                $skKeys = array_keys($zenitalToSede);
427                $skPlaceholders = implode(',', array_fill(0, count($skKeys), '?'));
428                $monthClause = $isWeekly ? 'd.num_mes = ?' : 'd.num_mes <= ?';
429                $bindings = array_merge(
430                    array_map('intval', $skKeys),
431                    [$year, $year - 1, $month],
432                );
433
434                $facturacion = DB::connection('zenital')->select("
435                    SELECT sk_sucursal, ano, SUM(invoice_amount) AS total
436                    FROM (
437                        SELECT f.num_factura, f.sk_sucursal, d.ano,
438                               MAX(f.base_imponible) AS invoice_amount
439                        FROM fact_facturacion f
440                        JOIN dim_fecha d ON d.sk_fecha = f.sk_fecha_emision
441                        WHERE f.sk_sucursal IN ({$skPlaceholders})
442                          AND d.ano IN (?, ?)
443                          AND {$monthClause}
444                        GROUP BY f.num_factura, f.sk_sucursal, d.ano
445                    ) per_invoice
446                    GROUP BY sk_sucursal, ano
447                ", $bindings);
448
449                foreach ($facturacion as $row) {
450                    $sedeId = $zenitalToSede[$row->sk_sucursal] ?? $row->sk_sucursal;
451                    $idx[$sedeId][$row->ano] = ($idx[$sedeId][$row->ano] ?? 0) + (float) $row->total;
452                }
453            } catch (\Exception $e) {
454                Log::channel('third-party')->warning("finance:send-report — Zenital query failed for company {$companyId}: {$e->getMessage()}");
455            }
456        }
457
458        // Catalunya empty-actuals follow-up: GESTIONA-sourced invoices in
459        // Zenital often arrive with sk_sucursal = -1 (unmapped sucursal at
460        // ETL) and only the sk_region populated. The entire Catalunya
461        // company's April 2026 facturación lived there — 605K€ that
462        // never landed on any sede. Pull these region-only totals as a
463        // synthetic `region-{titanRegionId}` bucket so they sum into the
464        // company's actuals AND fall on the correct row in the aggregated
465        // "Todas las regiones" detail. Filter to fuente_datos='GESTIONA'
466        // so we don't double-count CUEN/CANO rows that already arrive
467        // via tbl_finance_report_semanal (Drive imports).
468        // Mirror of FinanceController::list_report_semanal lines 1248-1342.
469        // Most Desconocido GESTIONA invoices have a real sucursal label on
470        // dim_cliente_facturacion.sucursal even though fact_facturacion
471        // carries sk_sucursal = -1. Power BI groups by the client label, so
472        // we re-attribute the same way before falling back to a region
473        // bucket. Pre-fix the email skipped this step, kept the whole pile
474        // in 'region-{titanRegionId}', and double-counted any portion the
475        // in-app re-attributed to a sede that already had a manual override
476        // (Castellón Apr 2026 was inflated by ~59K€ this way: ~59K€ of
477        // client_sucursal = "Cano Lopera"/"Extincas" Desconocido invoices
478        // were summed in the region bucket AND shadowed by the operator's
479        // tbl_finance_resumen_anual override on those sedes).
480        $zenitalSkRegions = $this->getZenitalSkRegionsForCompany($companyId);
481        if (! empty($zenitalSkRegions)) {
482            try {
483                $localByName = TblFinanceSedes::where('is_active', 1)
484                    ->whereNotNull('company_id')
485                    ->get()
486                    ->keyBy(fn ($s) => mb_strtolower(trim((string) $s->name)));
487                $clientSucursalAliases = [
488                    'jomar alcarreña' => 'alcarrena',
489                ];
490
491                // Per-invoice dedup — same rationale as the sucursal pass
492                // above. fact_facturacion can carry duplicate rows per
493                // num_factura; collapse before summing.
494                $regionKeys = array_keys($zenitalSkRegions);
495                $regionPlaceholders = implode(',', array_fill(0, count($regionKeys), '?'));
496                $monthClause = $isWeekly ? 'd.num_mes = ?' : 'd.num_mes <= ?';
497                $bindings = array_merge(
498                    array_map('intval', $regionKeys),
499                    [$year, $year - 1, $month],
500                );
501
502                // BI department rules ("Proceso de relleno de clientes sin
503                // Sucursal"): when fact_facturacion.sk_sucursal = -1 AND
504                // dim_cliente_servicio has no sucursal, the invoice is
505                // attributed to a sede based on the invoice series letter
506                // (UPPER(LEFT(num_factura, 1))) and the sk_region. Implemented
507                // by self::seriesAttributionMap(). Catalunya Serie R stays
508                // unattributed (BI rule: "se dejará en blanco"). The
509                // dim_cliente_facturacion fallback handles series the BI
510                // doc doesn't cover (Castellón Y, Baleares N, etc.).
511                $unattributed = DB::connection('zenital')->select("
512                    SELECT sk_region, serie, client_sucursal, ano, SUM(invoice_amount) AS total
513                    FROM (
514                        SELECT f.num_factura, f.sk_region,
515                               UPPER(SUBSTRING(f.num_factura FROM 1 FOR 1)) AS serie,
516                               c.sucursal AS client_sucursal, d.ano,
517                               MAX(f.base_imponible) AS invoice_amount
518                        FROM fact_facturacion f
519                        JOIN dim_fecha d ON d.sk_fecha = f.sk_fecha_emision
520                        LEFT JOIN dim_cliente_facturacion c
521                            ON c.sk_cliente_facturacion = f.sk_cliente_facturacion
522                        WHERE f.sk_sucursal = -1
523                          AND f.fuente_datos = 'GESTIONA'
524                          AND f.sk_region IN ({$regionPlaceholders})
525                          AND d.ano IN (?, ?)
526                          AND {$monthClause}
527                        GROUP BY f.num_factura, f.sk_region, serie, c.sucursal, d.ano
528                    ) per_invoice
529                    GROUP BY sk_region, serie, client_sucursal, ano
530                ", $bindings);
531
532                $seriesMap = self::seriesAttributionMap();
533                $simplification = self::sedeSimplificationMap();
534
535                foreach ($unattributed as $row) {
536                    $ano = (int) $row->ano;
537                    $total = (float) $row->total;
538                    $skRegion = (int) $row->sk_region;
539                    $serie = mb_strtoupper(trim((string) ($row->serie ?? '')));
540
541                    // BI rule lookup.
542                    $bySerieTarget = $seriesMap[$skRegion][$serie]
543                        ?? $seriesMap[$skRegion]['*']
544                        ?? null;
545
546                    $sede = null;
547                    if ($bySerieTarget !== null && $bySerieTarget !== '__DROP__') {
548                        $canonical = $simplification[$bySerieTarget] ?? $bySerieTarget;
549                        $sede = $localByName[$canonical] ?? null;
550                    }
551
552                    // No client_sucursal fallback. Per BI rules: only invoices
553                    // whose series matches one of the explicit mappings get
554                    // attributed to a specific sede. Everything else stays
555                    // in the regional "Sin asignar" bucket (the BI doc's
556                    // "Serie R se dejará en blanco" guidance, generalised to
557                    // all unmapped series). Counts in the region total via
558                    // the region-X synthetic entry.
559
560                    if ($sede !== null) {
561                        $idx[(int) $sede->id][$ano] = ($idx[(int) $sede->id][$ano] ?? 0) + $total;
562
563                        continue;
564                    }
565
566                    $titanRegionId = $zenitalSkRegions[$skRegion] ?? null;
567                    if (! $titanRegionId) {
568                        continue;
569                    }
570                    // Synthetic key — string prefix prevents collision with
571                    // numeric sede_ids. aggregateAllReports special-cases it.
572                    $regionKey = 'region-'.$titanRegionId;
573                    $idx[$regionKey][$ano] = ($idx[$regionKey][$ano] ?? 0) + $total;
574                }
575            } catch (\Exception $e) {
576                Log::channel('third-party')->warning("finance:send-report — Zenital unattributed query failed for company {$companyId}: {$e->getMessage()}");
577            }
578        }
579
580        // Budget from MySQL — weekly = current month only; monthly = YTD
581        $budgetQuery = TblFinanceBudgetAnual::where('company_id', $companyId)
582            ->where('year', $year);
583        if ($isWeekly) {
584            $budgetQuery->where('month', '=', $month);
585        } else {
586            $budgetQuery->where('month', '<=', $month);
587        }
588        $budgetBySede = $budgetQuery->get()
589            ->groupBy('sede_id')
590            ->map(fn ($rows) => (float) $rows->sum('amount'));
591
592        // Prevision from MySQL — weekly = current month only; monthly = YTD
593        $previsionQuery = TblFinancePrevisionAnual::where('company_id', $companyId)
594            ->where('year', $year);
595        if ($isWeekly) {
596            $previsionQuery->where('month', '=', $month);
597        } else {
598            $previsionQuery->where('month', '<=', $month);
599        }
600        $previsionBySede = $previsionQuery->get()
601            ->groupBy('sede_id')
602            ->map(fn ($rows) => (float) $rows->sum('amount'));
603
604        // Google-Drive-imported actuals for sedes outside Zenital (Aeroextinción,
605        // Cano Lopera, Cuenfa, Extincas, etc). Aggregated from tbl_finance_report_semanal,
606        // matches the Reporte Semanal UI. Local key space is sede_id (not sk_sucursal)
607        // so it doesn't collide with the Zenital-keyed $idx above.
608        $localActualsQuery = TblFinanceReportSemanal::where('company_id', $companyId)
609            ->whereIn('year', [$year, $year - 1]);
610        if ($isWeekly) {
611            $localActualsQuery->where('month', '=', $month);
612        } else {
613            $localActualsQuery->where('month', '<=', $month);
614        }
615        $localBySede = [];
616        foreach ($localActualsQuery->get() as $row) {
617            if ($row->actuals === null) {
618                continue;
619            }
620            $localBySede[$row->sede_id][(int) $row->year] = ($localBySede[$row->sede_id][(int) $row->year] ?? 0) + (float) $row->actuals;
621        }
622
623        // Berta audit follow-up — Bug D: Resumen Anual manual edits
624        // override Zenital/Drive actuals + N-1 for the same (sede, month).
625        // Pre-fix the weekly email pulled only Zenital + Drive and ignored
626        // manual edits typed on the Resumen tab — so a Resumen-edited
627        // Valencia would show 776.160 in the email but 807.943 in the
628        // in-app tab. Now both paths agree.
629        $resumenQuery = TblFinanceResumenAnual::where('company_id', $companyId)
630            ->where('year', $year);
631        if ($isWeekly) {
632            $resumenQuery->where('month', '=', $month);
633        } else {
634            $resumenQuery->where('month', '<=', $month);
635        }
636        $resumenBySede = [];
637        foreach ($resumenQuery->get() as $row) {
638            $resumenBySede[$row->sede_id]['actuals'] = ($resumenBySede[$row->sede_id]['actuals'] ?? 0) + (float) ($row->actuals ?? 0);
639            $resumenBySede[$row->sede_id]['actuals_n1'] = ($resumenBySede[$row->sede_id]['actuals_n1'] ?? 0) + (float) ($row->actuals_n1 ?? 0);
640        }
641
642        // Merge all sede_ids
643        $allSedeIds = array_unique(array_merge(
644            array_keys($idx),
645            $budgetBySede->keys()->toArray(),
646            $previsionBySede->keys()->toArray(),
647            array_keys($localBySede),
648            array_keys($resumenBySede)
649        ));
650
651        $actualsYtd = 0;
652        $actualsN1Ytd = 0;
653        $budgetYtd = 0;
654        $previsionYtd = 0;
655        $bySede = [];
656
657        foreach ($allSedeIds as $sedeId) {
658            // Precedence:
659            //   1. Resumen Anual manual edits (Bug D fix) — operator
660            //      typed the value, must win.
661            //   2. Zenital DWH actuals — authoritative when present.
662            //   3. Drive-imported aggregates — fallback for sedes not in
663            //      the Zenital map.
664            $hasResumen = isset($resumenBySede[$sedeId]);
665            $resumenActuals = $hasResumen ? ($resumenBySede[$sedeId]['actuals'] ?? null) : null;
666            $resumenN1 = $hasResumen ? ($resumenBySede[$sedeId]['actuals_n1'] ?? null) : null;
667
668            $actuals = ($resumenActuals !== null && $resumenActuals != 0)
669                ? $resumenActuals
670                : ($idx[$sedeId][$year] ?? $localBySede[$sedeId][$year] ?? 0);
671            $n1 = ($resumenN1 !== null && $resumenN1 != 0)
672                ? $resumenN1
673                : ($idx[$sedeId][$year - 1] ?? $localBySede[$sedeId][$year - 1] ?? 0);
674            $budget = $budgetBySede[$sedeId] ?? 0;
675            $prevision = $previsionBySede[$sedeId] ?? 0;
676
677            if ($actuals == 0 && $n1 == 0 && $budget == 0 && $prevision == 0) {
678                continue;
679            }
680
681            $actualsYtd += $actuals;
682            $actualsN1Ytd += $n1;
683            $budgetYtd += $budget;
684            $previsionYtd += $prevision;
685
686            $bySede[$sedeId] = [
687                'actuals' => $actuals,
688                'actuals_n1' => $n1,
689                'budget' => $budget,
690                'prevision' => $prevision,
691            ];
692        }
693
694        return [
695            'actuals_ytd' => $actualsYtd,
696            'actuals_n1_ytd' => $actualsN1Ytd,
697            'budget_ytd' => $budgetYtd,
698            'prevision_ytd' => $previsionYtd,
699            'by_sede' => $bySede,
700        ];
701    }
702
703    /**
704     * Returns map: zenital sk_region => titan region_id, restricted to
705     * the regions that belong to $companyId via tbl_finance_sedes. Used
706     * to attribute the "Desconocido sucursal" GESTIONA invoices in
707     * Zenital (which only carry sk_region) back to the right Titan
708     * region. Empty result is fine — just means nothing to attribute.
709     */
710    private function getZenitalSkRegionsForCompany(int $companyId): array
711    {
712        $titanRegionsForCompany = TblFinanceSedes::where('company_id', $companyId)
713            ->where('is_active', 1)
714            ->whereNotNull('region_id')
715            ->pluck('region_id')
716            ->unique()
717            ->toArray();
718        if (empty($titanRegionsForCompany)) {
719            return [];
720        }
721
722        $all = $this->getZenitalRegionMap();
723        $filtered = [];
724        foreach ($all as $skRegion => $titanRegionId) {
725            if (in_array($titanRegionId, $titanRegionsForCompany, true)) {
726                $filtered[$skRegion] = $titanRegionId;
727            }
728        }
729
730        return $filtered;
731    }
732
733    /**
734     * BI department rules for Desconocido (sk_sucursal = -1) invoices.
735     * Source: "Proceso de relleno de clientes sin Sucursal.docx".
736     *
737     * Map structure: [sk_region][serie_letter] => folded_sede_name.
738     * Special values:
739     *   - '*'         catch-all: any series in that region maps here
740     *                 (used for sk_region 14/15/18/20 which have one sede)
741     *   - '__DROP__'  do NOT re-attribute, do NOT fallback to client_sucursal;
742     *                 the invoice stays in the regional "Sin asignar" bucket
743     *                 (Catalunya R series per BI doc).
744     *
745     * sede names are lowercase + accent-folded so they match $localByName
746     * keys built by foldName / mb_strtolower(trim($name)) callers.
747     */
748    public static function seriesAttributionMap(): array
749    {
750        return [
751            14 => ['*' => 'oasys'],       // sk_region 14 (Oasys) — all to Oasys
752            15 => ['*' => 'crespo'],      // sk_region 15 (Crespo) — all to Crespo
753            18 => ['*' => 'togasa'],      // sk_region 18 (Togasa) — all to Togasa
754            20 => ['*' => 'extinfuego'],  // sk_region 20 (Extinfuego/Alicante) — all to Extinfuego
755            19 => [                       // Valencia
756                'L' => 'guipons',
757                'V' => 'vivo',            // accent-folded ("Vivó" → "vivo")
758            ],
759            21 => [                       // Cataluña
760                'T' => 'gallex',
761                'S' => 'master centella',
762                'R' => '__DROP__',        // BI: "Serie R se dejará en blanco"
763            ],
764            22 => [                       // Almería (→ Andalucía in Titan)
765                'B' => 'drago',
766            ],
767            23 => [                       // Madrid (+ La Mancha via series J)
768                'C' => 'enfire',
769                'D' => 'exfire',
770                'A' => 'montoya',
771                'B' => 'precoin',
772                'E' => 'anin',
773                'F' => 'exconin',
774                'G' => 'rosegur',
775                'H' => 'grupo fire madrid',
776                'J' => 'alcarrena',       // La Mancha sede ("Alcarreña" folded)
777            ],
778        ];
779    }
780
781    /**
782     * BI "simplificación" of Grupo Fire X sucursales (from the same doc).
783     * After attribution, these sucursal names are rewritten so they roll up
784     * to the canonical Grupo Fire X sede. Keys and values are folded names.
785     */
786    public static function sedeSimplificationMap(): array
787    {
788        return [
789            'grupo fire gava'     => 'grupo fire cataluna',   // Gavà → Cataluña
790            'grupo fire gavà'     => 'grupo fire cataluna',
791            'grupo fire rubi'     => 'grupo fire cataluna',   // Rubí → Cataluña
792            'grupo fire rubí'     => 'grupo fire cataluna',
793            'grupo fire centella' => 'master centella',       // Centella sub → Master Centella
794            'grupo fire gallex'   => 'gallex',                // Gallex sub → Gallex
795        ];
796    }
797
798    /**
799     * Jorge's canonical sede → Titan region mapping, extracted from
800     * "20260408 Grupo Fire Ventas_Canal - (v. Marzo).xlsx" — sheet "Ventas".
801     * Keys are folded sede names (lowercase + accent-stripped). Values are
802     * Titan region IDs (see tbl_finance_regions). Anything NOT in this map
803     * is EXCLUDED from per-region totals so we don't accidentally count
804     * sedes Jorge's BI doesn't surface (Grupo Fire Guadalajara, Grupo Fire
805     * MOF, cross-company phantom entries, etc.).
806     *
807     * Returns: [folded_name => titan_region_id]
808     */
809    public static function jorgeRegionWhitelist(): array
810    {
811        // Titan region IDs (from tbl_finance_regions):
812        //   1 Catalunya, 2 La Mancha, 3 Valencia, 4 Alicante, 5 Aragón,
813        //   6 Madrid, 8 Andalucía, 11 Baleares, 12 Castellón, 23 Castilla y León
814        return [
815            // Catalunya (Jorge: Clemente, Josmafoc, Ingesfoc, Lluis_Moff,
816            //   Sat Valles, Cisemex, NioExtin, Gallex, Centella, Fire Business CAT)
817            'clemente'             => 1,
818            'extintores clemente'  => 1,
819            'josmafoc'             => 1,
820            'ingesfoc'             => 1,
821            'lluis_moff'           => 1,
822            'sat valles'           => 1,
823            'cisemex'              => 1,
824            'nioextin'             => 1,
825            'gallex'               => 1,
826            'centella'             => 1,
827            'master centella'      => 1,
828            'fire business cat'    => 1,
829            'grupo fire cataluna'  => 1,
830
831            // Madrid (Jorge: Precoin, EnFire, Montoya, ExFire, Anin, ExConin,
832            //   Rosegur, Segurtrex, ICF, Fire Business MAD)
833            'precoin'              => 6,
834            'enfire'               => 6,
835            'montoya'              => 6,
836            'exfire'               => 6,
837            'anin'                 => 6,
838            'exconin'              => 6,
839            'rosegur'              => 6,
840            'segurtrex'            => 6,
841            'icf'                  => 6,
842            'fire business mad'    => 6,
843            'grupo fire madrid'    => 6,
844
845            // La Mancha (Jorge: Alcarrena)
846            'alcarrena'            => 2,
847            'jomar alcarrena'      => 2,
848
849            // Aragón (Jorge: Oasys)
850            'oasys'                => 5,
851
852            // Andalucía (Jorge: Drago, Robles, Aeroextinción)
853            'drago'                => 8,
854            'robles'               => 8,
855            'aeroextincion'        => 8,
856
857            // Valencia (Jorge: Guipons, Cuenfa, AirFeu, Vivo)
858            'guipons'              => 3,
859            'cuenfa'               => 3,
860            'airfeu'               => 3,
861            'vivo'                 => 3,
862
863            // Castilla y León (Jorge: Togasa, Crespo)
864            'togasa'               => 23,
865            'crespo'               => 23,
866
867            // Alicante (Jorge: ExtinFuego)
868            'extinfuego'           => 4,
869
870            // Castellón (Jorge: Cano Lopera, Extincas)
871            'cano lopera'          => 12,
872            'extincas'             => 12,
873
874            // Baleares (Jorge: Segucor, Ni Foc Ni Fum)
875            'segucor'              => 11,
876            'ni foc ni fum'        => 11,
877        ];
878    }
879
880    /**
881     * Build sk_region (Zenital) → region_id (Titan) lookup. Most regions
882     * match by name; a small alias set handles Zenital's sub-entity rows
883     * (Oasys/Crespo/Togasa/Extinfuego/Almería) that point at a parent
884     * Titan region. Cached per-process.
885     */
886    private function getZenitalRegionMap(): array
887    {
888        // FIRE-1148: shared with FinanceController via the ZenitalCatalog
889        // service — same Redis-cached lookup, same alias map.
890        return $this->zenital->regionMap();
891    }
892
893    /**
894     * Runtime Zenital sucursal map — must stay logically in sync with
895     * FinanceController::getZenitalSucursalesMap (same lookup, same
896     * aliases). See that method for the full rationale; tl;dr the
897     * hardcoded sk_sucursal IDs we used before were stale after a
898     * Zenital DWH rekey, and weekly Catalunya actuals came back as 0.
899     */
900    private function getZenitalSucursalesMap(): array
901    {
902        // FIRE-1148: shared with FinanceController via the ZenitalCatalog
903        // service — same Redis-cached lookup, same SCD-2 dedup, same alias.
904        return $this->zenital->sucursalesMap();
905    }
906
907    public function aggregateAllReports(array $reportsByCompany, string $type = 'weekly'): array
908    {
909        $sections = $type === 'monthly' ? ['weekly', 'monthly'] : ['weekly'];
910        $result = ['weekly' => [], 'monthly' => []];
911
912        // Berta audit follow-up — Item 4: pre-fix this method grouped by
913        // `tbl_companies.company_id`, so Valencia / Alicante / Castellón
914        // (three regions sharing company_id=30) appeared as a single
915        // "Comunidad Valenciana" row. Now we look up each sede's
916        // `region_id` and aggregate by `tbl_finance_regions.id` so the
917        // email matches the in-app dropdown grouping.
918        // Pull region_id for both active AND inactive sedes — bookkeeping
919        // tables (budget_anual / prevision_anual / resumen_anual /
920        // report_semanal) still reference legacy sedes whose is_active was
921        // flipped to 0 when a duplicate was deduped. Pre-fix those rows
922        // landed in the "company-{id}" fallback bucket and rendered as a
923        // duplicate "Grupo Fire {company-name}" row next to the real
924        // region; the legacy sede's region_id is still the correct one.
925        $sedeRegion = TblFinanceSedes::pluck('region_id', 'id')->toArray();
926        $sedeCompany = TblFinanceSedes::pluck('company_id', 'id')->toArray();
927        $regionNames = TblFinanceRegions::where('is_active', 1)
928            ->pluck('name', 'id')
929            ->toArray();
930        // Budget/previsión rows on inactive sedes are stale duplicates the
931        // Budget tab UI never renders (it iterates is_active=1). Catalunya
932        // inactive sedes 1/6/8/9 carry ~1 M€ of legacy budget that was
933        // never zeroed when the active replacements were created. We keep
934        // inactive sedes for ACTUALS (some carry the real seed data) but
935        // skip them when summing budget/previsión.
936        $activeSedeIds = array_flip(TblFinanceSedes::where('is_active', 1)->pluck('id')->all());
937
938        // Backstop for hard-orphan sede_ids (e.g. 117/118/123/124/1000040
939        // for Catalunya — Zenital hardcoded sede_ids that never got a
940        // matching tbl_finance_sedes row). Map company → most common
941        // region of its active sedes; if a sede_id is unknown but its
942        // owning company has a clear "home" region, route the row there
943        // instead of inventing a "Grupo Fire {company}" duplicate.
944        $companyPrimaryRegion = [];
945        $regionCountsByCompany = TblFinanceSedes::where('is_active', 1)
946            ->whereNotNull('region_id')
947            ->selectRaw('company_id, region_id, COUNT(*) as c')
948            ->groupBy('company_id', 'region_id')
949            ->get()
950            ->groupBy('company_id');
951        foreach ($regionCountsByCompany as $companyId => $rows) {
952            $best = $rows->sortByDesc('c')->first();
953            if ($best) {
954                $companyPrimaryRegion[(int) $companyId] = (int) $best->region_id;
955            }
956        }
957
958        // FIRE follow-up: dedup-by-sede-name. The bookkeeping tables hold
959        // historical rows under DEACTIVATED duplicate sedes — sometimes the
960        // legacy (inactive) sede has the real seed data and the active sede
961        // has only a placeholder, sometimes it's the opposite. Summing both
962        // double-counts (Valencia inflated by ~1,7 M€ on YTD Apr 2026).
963        // Strategy: collapse sedes by lowercased name; for each name pick
964        // the entry with the LARGER actuals (the one with the real data
965        // wins, the placeholder loses). Accent folding lets "Vivó" and
966        // "Vivo", "AirFeu" and "Airfeu" collapse together too.
967        $sedeNames = TblFinanceSedes::pluck('name', 'id')->toArray();
968        $foldName = static function (string $name): string {
969            $name = mb_strtolower(trim($name));
970            $name = strtr($name, [
971                'á' => 'a', 'é' => 'e', 'í' => 'i', 'ó' => 'o', 'ú' => 'u',
972                'ñ' => 'n', 'à' => 'a', 'è' => 'e', 'ì' => 'i', 'ò' => 'o',
973                'ù' => 'u', 'ü' => 'u',
974            ]);
975
976            return $name;
977        };
978
979        // Jorge's canonical sede → region whitelist (from his "Ventas" sheet
980        // in 20260408 Grupo Fire Ventas_Canal - v. Marzo.xlsx). Aggregator
981        // uses this as the SOLE source of truth for which sedes count and
982        // which region each belongs to. Anything not in the whitelist is
983        // excluded so Grupo Fire Guadalajara / Grupo Fire MOF / cross-
984        // company phantom entries don't leak into region totals.
985        // Sede name simplification ("Grupo Fire Gavà" → "Grupo Fire Cataluña",
986        // etc., per the BI doc) is applied BEFORE the whitelist lookup so
987        // legacy sub-entity names roll up correctly.
988        $jorgeWhitelist = self::jorgeRegionWhitelist();
989        $jorgeSimplification = self::sedeSimplificationMap();
990
991        foreach ($sections as $section) {
992            // Stage 1a — dedup actuals/N-1 by sede name (the seed-data
993            // ambiguity fix). Synthetic "region-{id}" entries are kept
994            // under a unique key so they're never deduped against a real
995            // sede.
996            //
997            // Stage 1b (separate accumulators) — budget and previsión are
998            // sede-keyed in MySQL (tbl_finance_budget_anual /
999            // tbl_finance_prevision_anual), each typed by the operator on
1000            // ONE specific sede record (the active one). Pre-fix we kept
1001            // budget/prevision INSIDE the deduped actuals entry — so when
1002            // the inactive sede won the dedup for actuals (e.g. AirFeu
1003            // inactive sede 19 with 1 M€ in seed data beat active sede 24
1004            // with 55 K€), its budget=0 was used and the active sede's
1005            // real 1,8 M€ budget got dropped. Valencia P. Negocio
1006            // collapsed from 3 M€ to 1,1 M€ that way. Fix: track
1007            // budget/prevision per region INDEPENDENTLY, summed from
1008            // every by_sede entry (no dedup) — budgets don't have the
1009            // active/inactive seed-data problem actuals do, so summing
1010            // is safe.
1011            $byName = [];
1012            $byRegionBudget = [];
1013            $byRegionPrevision = [];
1014            $budgetTotalYtd = 0;
1015            $previsionTotalYtd = 0;
1016
1017            foreach ($reportsByCompany as $companyId => $report) {
1018                $data = $report[$section];
1019                if (! $data) {
1020                    continue;
1021                }
1022
1023                foreach (($data['by_sede'] ?? []) as $sedeId => $sedeVals) {
1024                    if (is_string($sedeId) && str_starts_with($sedeId, 'region-')) {
1025                        // Region-level unattributed buckets stay distinct
1026                        // — never collide with a sede record.
1027                        $key = $sedeId.'|'.$companyId;
1028                        $regionId = (int) substr($sedeId, 7);
1029                        $regionName = $regionNames[$regionId] ?? null;
1030                        $byName[$key] = [
1031                            'sedeId' => $sedeId,
1032                            'companyId' => $companyId,
1033                            'regionId' => $regionId,
1034                            'regionName' => $regionName,
1035                            'vals' => $sedeVals,
1036                        ];
1037                        // Region-level synthetic entries already carry
1038                        // their own budget/previsión = 0 (Desconocido
1039                        // doesn't have a target). Skip the side tally so
1040                        // we don't double-add anything.
1041
1042                        continue;
1043                    }
1044
1045                    $name = $sedeNames[$sedeId] ?? '';
1046                    $nameKey = $name !== '' ? $foldName($name) : 'sede-'.$sedeId;
1047                    // Apply the BI sucursal "simplificación" first: Gavà /
1048                    // Rubí → Grupo Fire Cataluña, Grupo Fire Centella →
1049                    // Master Centella, Grupo Fire Gallex → Gallex. The
1050                    // whitelist below uses these canonical names.
1051                    $nameKey = $jorgeSimplification[$nameKey] ?? $nameKey;
1052
1053                    // Jorge's whitelist overrides everything: only count
1054                    // sedes whose folded name appears in his "Ventas" sheet,
1055                    // and force their region attribution to match his.
1056                    // If a sede isn't in the whitelist, route its actuals
1057                    // to the region's "Sin asignar" bucket so the region
1058                    // total still counts the money (matching Jorge's
1059                    // behaviour of leaving the sucursal blank but keeping
1060                    // the invoice in the region).
1061                    if (! isset($jorgeWhitelist[$nameKey])) {
1062                        $fallbackRegion = $sedeRegion[$sedeId]
1063                            ?? $companyPrimaryRegion[(int) $companyId]
1064                            ?? null;
1065                        if ($fallbackRegion !== null) {
1066                            $regionBucketKey = 'region-'.$fallbackRegion.'|whitelist|'.$companyId;
1067                            if (! isset($byName[$regionBucketKey])) {
1068                                $byName[$regionBucketKey] = [
1069                                    'sedeId' => 'region-'.$fallbackRegion,
1070                                    'companyId' => $companyId,
1071                                    'regionId' => $fallbackRegion,
1072                                    'regionName' => $regionNames[$fallbackRegion] ?? null,
1073                                    'vals' => ['actuals' => 0, 'actuals_n1' => 0, 'budget' => 0, 'prevision' => 0],
1074                                ];
1075                            }
1076                            $byName[$regionBucketKey]['vals']['actuals'] += (float) ($sedeVals['actuals'] ?? 0);
1077                            $byName[$regionBucketKey]['vals']['actuals_n1'] += (float) ($sedeVals['actuals_n1'] ?? 0);
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}