Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.08% covered (danger)
0.08%
1 / 1216
4.35% covered (danger)
4.35%
1 / 23
CRAP
0.00% covered (danger)
0.00%
0 / 1
FacturasService
0.08% covered (danger)
0.08%
1 / 1216
4.35% covered (danger)
4.35%
1 / 23
37350.18
0.00% covered (danger)
0.00%
0 / 1
 __construct
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 getInvoices
0.00% covered (danger)
0.00%
0 / 18
0.00% covered (danger)
0.00%
0 / 1
6
 loopInvoices
0.00% covered (danger)
0.00%
0 / 212
0.00% covered (danger)
0.00%
0 / 1
650
 loopNextRemindersInvoices
0.00% covered (danger)
0.00%
0 / 73
0.00% covered (danger)
0.00%
0 / 1
90
 loopNextRemindersClients
0.00% covered (danger)
0.00%
0 / 148
0.00% covered (danger)
0.00%
0 / 1
380
 sendInvoice
0.00% covered (danger)
0.00%
0 / 173
0.00% covered (danger)
0.00%
0 / 1
930
 getAllInvoices
0.00% covered (danger)
0.00%
0 / 15
0.00% covered (danger)
0.00%
0 / 1
6
 getAllInvoicesExceptions
0.00% covered (danger)
0.00%
0 / 10
0.00% covered (danger)
0.00%
0 / 1
2
 sendCyCInvoices
0.00% covered (danger)
0.00%
0 / 68
0.00% covered (danger)
0.00%
0 / 1
110
 setAllMonthAdministratorsInvoices
0.00% covered (danger)
0.00%
0 / 52
0.00% covered (danger)
0.00%
0 / 1
182
 sendAdministratorsInvoices
0.00% covered (danger)
0.00%
0 / 88
0.00% covered (danger)
0.00%
0 / 1
42
 checkClientHasFreshdeskTask
0.00% covered (danger)
0.00%
0 / 11
0.00% covered (danger)
0.00%
0 / 1
12
 checkClientHasFreshdeskTaskAll
0.00% covered (danger)
0.00%
0 / 15
0.00% covered (danger)
0.00%
0 / 1
30
 sendCallCenterInvoices
0.00% covered (danger)
0.00%
0 / 80
0.00% covered (danger)
0.00%
0 / 1
306
 addToSheets
0.00% covered (danger)
0.00%
0 / 36
0.00% covered (danger)
0.00%
0 / 1
56
 getGoogleSheetsService
0.00% covered (danger)
0.00%
0 / 24
0.00% covered (danger)
0.00%
0 / 1
56
 writeToGoogleSheet
0.00% covered (danger)
0.00%
0 / 17
0.00% covered (danger)
0.00%
0 / 1
6
 handleGoogleAuthCallback
0.00% covered (danger)
0.00%
0 / 30
0.00% covered (danger)
0.00%
0 / 1
42
 getVencimientosFormateados
0.00% covered (danger)
0.00%
0 / 6
0.00% covered (danger)
0.00%
0 / 1
6
 listCreditDaysOffered
0.00% covered (danger)
0.00%
0 / 77
0.00% covered (danger)
0.00%
0 / 1
240
 recordSkip
0.00% covered (danger)
0.00%
0 / 16
0.00% covered (danger)
0.00%
0 / 1
6
 syncPaymentDateForInvoice
0.00% covered (danger)
0.00%
0 / 13
0.00% covered (danger)
0.00%
0 / 1
30
 syncPaymentDatesForRegion
0.00% covered (danger)
0.00%
0 / 33
0.00% covered (danger)
0.00%
0 / 1
20
1<?php
2
3namespace App\Services;
4
5use App\Models\TblCompanies;
6use App\Models\TblInvoiceAdministrators;
7use App\Models\TblInvoiceReminders;
8use App\Models\TblInvoiceRemindersEmailTemplate;
9use App\Models\TblInvoiceReminderSkips;
10use App\Models\TblInvoicesExceptions;
11use App\Models\TblInvoicesNextReminders;
12use Carbon\Carbon;
13use Google\Client;
14use Google\Service\Sheets;
15use Google\Service\Sheets\ValueRange;
16use Illuminate\Http\Request;
17use Illuminate\Support\Facades\Http;
18use Illuminate\Support\Facades\Log;
19use Mockery\Exception;
20use SendGrid\Mail\Attachment;
21use SendGrid\Mail\Mail;
22
23class FacturasService extends GestionaService
24{
25    public function __construct()
26    {
27        parent::__construct();
28    }
29
30    public function getInvoices($region = 'Cataluña'): array
31    {
32        try {
33            // FIRE-1293 (Berta feedback, 2026-07): run the full flow for every
34            // Gestiona region so the KPI can display "potential" for regions
35            // that haven't opted in yet. The active flag now gates only the
36            // real SendGrid call inside the loops; everything else — skip
37            // telemetry, would-send counting — happens either way.
38            $companyActive = TblCompanies::where('region', $region)
39                ->where('invoice_reminder_active', 1)
40                ->exists();
41
42            $today = date('Y-m-d');
43            $next10days = date('Y-m-d', strtotime('+10 days'));
44            $lastWeek = date('Y-m-d', strtotime('-1 week'));
45
46            $counter = 1;
47
48            $nextWeekInvoices = $this->request('get', 'factura/vence/'.$next10days, $region, []);
49            // $todayInvoices = $this->request('get', 'factura/vence/' . $today, $region, []);
50            $lastWeekInvoices = $this->request('get', 'factura/vence/'.$lastWeek, $region, []);
51
52            $resultNextWeekInvoices = $this->loopInvoices($nextWeekInvoices, 1, $region, $counter, $next10days, $companyActive);
53            /*$counter = $resultNextWeekInvoices["counter"] - 1;
54            if($counter >= 30){
55                return ['success' => true];
56            }*/
57
58            // $resultTodayInvoices = $this->loopInvoices($todayInvoices, 2, $region, $counter);
59            /*$counter = $resultTodayInvoices["counter"] - 1;
60            if($counter >= 30){
61                return ['success' => true];
62            }*/
63
64            $this->loopInvoices($lastWeekInvoices, 3, $region, $counter, $lastWeek, $companyActive);
65
66            $this->loopNextRemindersInvoices($region, $companyActive);
67
68            $this->loopNextRemindersClients($region, $next10days, $lastWeek, $companyActive);
69
70            return ['success' => true];
71        } catch (\Exception $e) {
72            Log::channel('g3w_invoices')->error($e->getMessage());
73            Log::error('Trace: '.$e->getTraceAsString());
74
75            return ['success' => false, 'error' => $e->getMessage()];
76        }
77    }
78
79    public function loopInvoices(array $invoices, $reminder_type, $region, $counter, $date = null, bool $companyActive = true): array
80    {
81        $senders = [];
82        foreach ($invoices['facturas'] as $invoice) {
83            // Continue if region is Comunidad Valenciana and the invoice starts with M
84            if (
85                $region == 'Comunidad Valenciana'
86                && str_starts_with((string) $invoice['ID'], 'M')
87            ) {
88                $this->recordSkip($region, (string) $invoice['ID'], 'region_filter_cv_m_prefix', null, $reminder_type);
89
90                continue;
91            }
92
93            // FIRE-1586: same pattern for Aeroextinción — Andalucía region,
94            // invoice IDs starting with 'C' are Aeroextinción's series and
95            // must not receive automatic reminders (per Berta). recordSkip
96            // so the KPI panel surfaces the count under its own bucket.
97            if (
98                $region == 'Andalucía'
99                && str_starts_with((string) $invoice['ID'], 'C')
100            ) {
101                $this->recordSkip($region, (string) $invoice['ID'], 'region_filter_and_c_prefix', null, $reminder_type);
102
103                continue;
104            }
105
106            // Already scheduled for a future reminder — not a skip, just routed
107            // to a different cron path (tracked in tbl_invoice_next_reminders).
108            $existNextReminderInvoice = TblInvoicesNextReminders::where('invoice_number', $invoice['ID'])
109                ->where('region', $region)
110                ->exists();
111
112            if ($existNextReminderInvoice) {
113                continue;
114            }
115
116            $dataInvoice = $this->request('get', 'factura/'.$invoice['ID'], $region, []);
117
118            if (! isset($dataInvoice['factura'])) {
119                $this->recordSkip($region, (string) $invoice['ID'], 'g3w_factura_missing', null, $reminder_type);
120
121                continue;
122            }
123
124            if ($this->checkClientHasFreshdeskTask($dataInvoice['factura']['cod_cliente'], $region)) {
125                $this->recordSkip(
126                    $region,
127                    (string) ($dataInvoice['factura']['n_factura'] ?? $invoice['ID']),
128                    'freshdesk_task_active',
129                    null,
130                    $reminder_type,
131                    (string) ($dataInvoice['factura']['cod_cliente'] ?? null),
132                );
133
134                continue;
135            }
136
137            $cobrada = $dataInvoice['factura']['cobrada'];
138            $formaPago = $dataInvoice['factura']['forma_pago_factura'];
139
140            // Invoice already paied
141            // Invoice that the payment method is not "transferencia"
142            if (
143                $cobrada !== 'NO'
144                || stripos((string) $formaPago, 'tr') === false
145            ) {
146                $this->recordSkip(
147                    $region,
148                    (string) ($dataInvoice['factura']['n_factura'] ?? $invoice['ID']),
149                    $cobrada !== 'NO' ? 'already_paid' : 'payment_method_not_transfer',
150                    ['cobrada' => $cobrada, 'forma_pago' => $formaPago],
151                    $reminder_type,
152                    (string) ($dataInvoice['factura']['cod_cliente'] ?? null),
153                );
154
155                continue;
156            }
157
158            $existNextReminderClient = TblInvoicesNextReminders::where('id_client', $dataInvoice['factura']['cod_cliente'])
159                ->where('region', $region)
160                ->exists();
161
162            if ($existNextReminderClient) {
163                $this->addToSheets($dataInvoice['factura']['cod_cliente'], $dataInvoice['factura']['n_factura'], $region);
164
165                TblInvoicesNextReminders::create([
166                    'id_client' => $dataInvoice['factura']['cod_cliente'],
167                    'region' => $region,
168                    'invoice_number' => $dataInvoice['factura']['n_factura'],
169                ]);
170
171                continue;
172            }
173
174            $dataClient = null;
175
176            if ($dataInvoice['factura']['cod_cliente']) {
177                $dataClient = $this->request('get', 'cliente/'.$dataInvoice['factura']['cod_cliente'], $region, []);
178            }
179
180            if ($dataClient['cliente']['tipo_cliente'] == 'Administrador') {
181                $codService = $dataInvoice['factura']['lineas'][0]['cod_servicio'];
182                $dataService = $this->request('get', 'servicio/'.$codService, $region, []);
183
184                TblInvoiceAdministrators::create([
185                    'invoice_number' => $dataInvoice['factura']['n_factura'],
186                    'region' => $region,
187                    'name' => $dataClient['cliente']['empresa'],
188                    'CIF' => $dataClient['cliente']['cliente_cif'],
189                    'email' => $dataClient['cliente']['email'],
190                    'service_name' => $dataService['servicio']['nombre_servicio'],
191                    'service_addres' => $dataService['servicio']['direccion'],
192                    'send_date' => $date,
193                    'expiration_date' => $date,
194                    'amount' => $dataInvoice['factura']['importe_total_factura'],
195                ]);
196
197                continue;
198            }
199
200            $exists = TblInvoicesExceptions::where('cif', $dataClient['cliente']['cliente_cif'])
201                ->orWhere('name', $dataClient['cliente']['empresa'])
202                ->orWhere('administrator', $dataClient['cliente']['empresa'])
203                ->orWhere('id_admin_g3w', $invoice['ID'])
204                ->orWhere('invoice_number', $dataInvoice['factura']['n_factura'])
205                ->exists();
206
207            if ($exists) {
208                $this->recordSkip(
209                    $region,
210                    (string) ($dataInvoice['factura']['n_factura'] ?? $invoice['ID']),
211                    'in_exceptions_table',
212                    [
213                        'cif' => $dataClient['cliente']['cliente_cif'] ?? null,
214                        'empresa' => $dataClient['cliente']['empresa'] ?? null,
215                    ],
216                    $reminder_type,
217                    (string) ($dataInvoice['factura']['cod_cliente'] ?? null),
218                );
219
220                continue;
221            }
222
223            $invoice_number = $dataInvoice['factura']['n_factura'] ?? null;
224            $client_name = $dataClient['cliente']['empresa'] ?? null;
225            $client_email = $dataClient['cliente']['email'] ?? $dataClient['cliente']['email_facturacion'] ?? null;
226            $cif = $dataClient['cliente']['cliente_cif'] ?? null;
227            $issued_date = $dataInvoice['factura']['fecha_emision'] ?? null;
228            $expiration_date = $date ?? $dataInvoice['factura']['vencimientos'][0]['fecha_vencimiento'];
229            $amount = $dataInvoice['factura']['importe_total_factura'] ?? null;
230            $collection_date = null;
231            $document = $dataInvoice['factura']['documento'] ?? null;
232            $senders[$cif][] = [
233                'invoice_number' => $invoice_number,
234                'client_name' => $client_name,
235                'client_email' => $client_email,
236                'issued_date' => $issued_date,
237                'expiration_date' => $expiration_date,
238                'document' => $document,
239                'amount' => $amount,
240                'reminder_type' => $reminder_type,
241                'cif' => $cif,
242                'collection_date' => $collection_date,
243                'region' => $region,
244            ];
245
246        }
247
248        if (config('app.env') === 'production') {
249            foreach ($senders as $cif => $invoicesGroup) {
250
251                $totalFacturas = count($invoicesGroup);
252
253                if ($totalFacturas === 1) {
254                    // FIRE-1293 (Berta): if the region is inactive we still
255                    // want to count the potential without emailing anyone.
256                    if (! $companyActive) {
257                        $this->recordSkip(
258                            $region,
259                            (string) $invoicesGroup[0]['invoice_number'],
260                            'region_inactive',
261                            null,
262                            (int) $invoicesGroup[0]['reminder_type'],
263                            null,
264                            (int) ($invoicesGroup[0]['amount'] ?? 0),
265                        );
266                        $counter++;
267
268                        continue;
269                    }
270
271                    // FIRE-1293 (Berta): "sent" = every attempt regardless of
272                    // SendGrid outcome. Create the row first so a subsequent
273                    // provider failure still shows up as a sent attempt in
274                    // tbl_invoice_reminders; the sendgrid_send_failed skip
275                    // stays for the "why did it fail" panel.
276                    TblInvoiceReminders::create([
277                        'invoice_number' => $invoicesGroup[0]['invoice_number'],
278                        'client_name' => $invoicesGroup[0]['client_name'],
279                        'client_email' => $invoicesGroup[0]['client_email'],
280                        'cif' => $invoicesGroup[0]['cif'],
281                        'issued_date' => $invoicesGroup[0]['issued_date'],
282                        'expiration_date' => $invoicesGroup[0]['expiration_date'],
283                        'amount' => $invoicesGroup[0]['amount'],
284                        'collection_date' => $invoicesGroup[0]['collection_date'],
285                        'region' => $invoicesGroup[0]['region'],
286                        'reminder_type' => $invoicesGroup[0]['reminder_type'],
287                    ]);
288
289                    $resultSend = $this->sendInvoice(
290                        $invoicesGroup[0]['invoice_number'],
291                        $invoicesGroup[0]['client_name'],
292                        $invoicesGroup[0]['client_email'],
293                        $invoicesGroup[0]['issued_date'],
294                        $invoicesGroup[0]['expiration_date'],
295                        $invoicesGroup[0]['document'],
296                        $invoicesGroup[0]['amount'],
297                        $invoicesGroup[0]['reminder_type'],
298                        $region);
299
300                    if (! $resultSend['success']) {
301                        $this->recordSkip(
302                            $region,
303                            (string) $invoicesGroup[0]['invoice_number'],
304                            'sendgrid_send_failed',
305                            ['error' => $resultSend['error'] ?? null],
306                            (int) $invoicesGroup[0]['reminder_type'],
307                            null,
308                            (int) ($invoicesGroup[0]['amount'] ?? 0),
309                        );
310                    }
311                } else {
312                    $table = "<table style='border-collapse: collapse; width: 100%; font-family: Arial, sans-serif;'>
313                        <tr>
314                            <th style='border: 1px solid #999; padding: 8px; background-color: #f2f2f2; text-align: left;'>Número Factura</th>
315                            <th style='border: 1px solid #999; padding: 8px; background-color: #f2f2f2; text-align: left;'>Nombre cliente de servicio</th>
316                            <th style='border: 1px solid #999; padding: 8px; background-color: #f2f2f2; text-align: left;'>Dirección del servicio</th>
317                            <th style='border: 1px solid #999; padding: 8px; background-color: #f2f2f2; text-align: left;'>Fecha de emisión</th>
318                            <th style='border: 1px solid #999; padding: 8px; background-color: #f2f2f2; text-align: left;'>Fecha de vencimiento</th>
319                            <th style='border: 1px solid #999; padding: 8px; background-color: #f2f2f2; text-align: left;'>Importe</th>
320                        </tr>";
321                    $documentsBase64 = [];
322
323                    foreach ($invoicesGroup as $invoice) {
324                        $invoiceG3W = $this->request('get', 'factura/'.$invoice['invoice_number'], $region, []);
325                        $invoiceData = $invoiceG3W['factura'];
326
327                        $codService = $invoiceData['lineas'][0]['cod_servicio'];
328                        $dataService = $this->request('get', 'servicio/'.$codService, $region, []);
329                        $dataService = $dataService['servicio'];
330
331                        $invoiceNumber = $invoice['invoice_number'];
332                        $table .= "<tr>
333                            <td class='invoice_number' style='border: 1px solid #999; padding: 8px;'>".$invoiceNumber."</td>
334                            <td class='invoice_service_name' style='border: 1px solid #999; padding: 8px;'>".$dataService['nombre_servicio']."</td>
335                            <td class='invoice_service_addres' style='border: 1px solid #999; padding: 8px;'>".$dataService['direccion']."</td>
336                            <td class='invoice_send_date' style='border: 1px solid #999; padding: 8px;'>".Carbon::now()->format('d-m-Y')."</td>
337                            <td class='invoice_expiration_date' style='border: 1px solid #999; padding: 8px;'>".$invoice['expiration_date']."</td>
338                            <td class='invoice_amount' style='border: 1px solid #999; padding: 8px;'>".$invoice['amount'].' €</td>
339                        </tr>';
340
341                        $documentsBase64[] = [
342                            'content' => $invoiceData['documento'],
343                            'filename' => "Factura_{$invoiceNumber}.pdf",
344                        ];
345                    }
346
347                    $table .= '</table>';
348
349                    // FIRE-1293 (Berta): read-only regions log the potential
350                    // as a skip and don't hit SendGrid. One row per invoice
351                    // in the group so the KPI counts each individually.
352                    if (! $companyActive) {
353                        foreach ($invoicesGroup as $inv) {
354                            $this->recordSkip(
355                                $region,
356                                (string) $inv['invoice_number'],
357                                'region_inactive',
358                                null,
359                                (int) $inv['reminder_type'],
360                                null,
361                                (int) ($inv['amount'] ?? 0),
362                            );
363                        }
364                    } else {
365                        $this->sendInvoice(
366                            $invoicesGroup[0]['invoice_number'],
367                            $invoicesGroup[0]['client_name'],
368                            $invoicesGroup[0]['client_email'],
369                            Carbon::now()->format('d-m-Y'),
370                            $invoicesGroup[0]['expiration_date'],
371                            $documentsBase64,
372                            $invoicesGroup[0]['amount'],
373                            $invoicesGroup[0]['reminder_type'] === '1' ? 5 : 6,
374                            $region,
375                            $table
376                        );
377                    }
378
379                }
380
381                $counter++;
382
383            }
384        }
385
386        return [
387            'success' => true,
388            'counter' => $counter,
389        ];
390    }
391
392    private function loopNextRemindersInvoices($region, bool $companyActive = true): void
393    {
394        $nextReminders = TblInvoicesNextReminders::where('region', $region)
395            ->where('next_reminders', date('Y-m-d'))
396            ->pluck('invoice_number');
397
398        foreach ($nextReminders as $nextReminder) {
399            $dataInvoice = $this->request('get', 'factura/'.$nextReminder, $region, []);
400
401            if ($this->checkClientHasFreshdeskTask($dataInvoice['factura']['cod_cliente'], $region)) {
402                $this->recordSkip(
403                    $region,
404                    (string) ($dataInvoice['factura']['n_factura'] ?? $nextReminder),
405                    'freshdesk_task_active',
406                    null,
407                    2,
408                    (string) ($dataInvoice['factura']['cod_cliente'] ?? null),
409                );
410
411                continue;
412            }
413
414            $cobrada = $dataInvoice['factura']['cobrada'];
415            $formaPago = $dataInvoice['factura']['forma_pago_factura'];
416
417            // Invoice already paied
418            // Invoice that the payment method is not "transferencia"
419            if (
420                $cobrada !== 'NO'
421                || stripos((string) $formaPago, 'tr') === false
422            ) {
423                $this->recordSkip(
424                    $region,
425                    (string) ($dataInvoice['factura']['n_factura'] ?? $nextReminder),
426                    $cobrada !== 'NO' ? 'already_paid' : 'payment_method_not_transfer',
427                    ['cobrada' => $cobrada, 'forma_pago' => $formaPago],
428                    2,
429                    (string) ($dataInvoice['factura']['cod_cliente'] ?? null),
430                );
431
432                continue;
433            }
434
435            $dataClient = $this->request('get', 'cliente/'.$dataInvoice['factura']['cod_cliente'], $region, []);
436
437            $invoice_number = $dataInvoice['factura']['n_factura'] ?? null;
438            $client_name = $dataClient['cliente']['empresa'] ?? null;
439            $client_email = $dataClient['cliente']['email'] ?? $dataClient['cliente']['email_facturacion'] ?? null;
440            $cif = $dataClient['cliente']['cliente_cif'] ?? null;
441            $issued_date = $dataInvoice['factura']['fecha_emision'] ?? null;
442            $expiration_date = $this->getVencimientosFormateados($dataInvoice);
443            $amount = $dataInvoice['factura']['importe_total_factura'] ?? null;
444            $collection_date = null;
445            $document = $dataInvoice['factura']['documento'] ?? null;
446
447            // FIRE-1293 (Berta): read-only regions record potential and skip send.
448            if (! $companyActive) {
449                $this->recordSkip(
450                    $region,
451                    (string) $invoice_number,
452                    'region_inactive',
453                    null,
454                    2,
455                    (string) ($dataInvoice['factura']['cod_cliente'] ?? null),
456                    (int) ($amount ?? 0),
457                );
458
459                continue;
460            }
461
462            // FIRE-1293 (Berta): create sent row first so SendGrid failures
463            // still count as sent attempts in tbl_invoice_reminders.
464            TblInvoiceReminders::create([
465                'invoice_number' => $invoice_number,
466                'client_name' => $client_name,
467                'client_email' => $client_email,
468                'cif' => $cif,
469                'issued_date' => $issued_date,
470                'expiration_date' => $expiration_date,
471                'amount' => $amount,
472                'collection_date' => $collection_date,
473                'region' => $region,
474                'reminder_type' => 2,
475            ]);
476
477            if (config('app.env') === 'production') {
478                $resultSend = $this->sendInvoice($invoice_number, $client_name, $client_email, $issued_date, $expiration_date, $document, $amount, 2, $region);
479
480                if (! $resultSend['success']) {
481                    $this->recordSkip(
482                        $region,
483                        (string) $invoice_number,
484                        'sendgrid_send_failed',
485                        ['error' => $resultSend['error'] ?? null],
486                        2,
487                        (string) ($dataInvoice['factura']['cod_cliente'] ?? null),
488                        (int) ($amount ?? 0),
489                    );
490                }
491            }
492        }
493    }
494
495    private function loopNextRemindersClients($region, string $next10days, string $lastWeek, bool $companyActive = true): void
496    {
497        $diaNext10 = date('j', strtotime($next10days));
498        $diaLastWeek = date('j', strtotime($lastWeek));
499
500        $nextWeekInvoices = TblInvoicesNextReminders::where('payment_day', $diaNext10)->get()->pluck('invoice_number');
501        $lastWeekInvoices = TblInvoicesNextReminders::where('payment_day', $diaLastWeek)->get()->pluck('invoice_number');
502
503        // type 1
504        foreach ($nextWeekInvoices as $reminder) {
505            if (! $reminder) {
506                continue;
507            }
508            $dataInvoice = $this->request('get', 'factura/'.$reminder, $region, []);
509            if ($this->checkClientHasFreshdeskTask($dataInvoice['factura']['cod_cliente'], $region)) {
510                $this->recordSkip(
511                    $region,
512                    (string) ($dataInvoice['factura']['n_factura'] ?? $reminder),
513                    'freshdesk_task_active',
514                    null,
515                    1,
516                    (string) ($dataInvoice['factura']['cod_cliente'] ?? null),
517                );
518
519                continue;
520            }
521
522            $cobrada = $dataInvoice['factura']['cobrada'];
523            $formaPago = $dataInvoice['factura']['forma_pago_factura'];
524
525            // Invoice already paied
526            // Invoice that the payment method is not "transferencia"
527            if (
528                $cobrada !== 'NO'
529                || stripos((string) $formaPago, 'tr') === false
530            ) {
531                $this->recordSkip(
532                    $region,
533                    (string) ($dataInvoice['factura']['n_factura'] ?? $reminder),
534                    $cobrada !== 'NO' ? 'already_paid' : 'payment_method_not_transfer',
535                    ['cobrada' => $cobrada, 'forma_pago' => $formaPago],
536                    1,
537                    (string) ($dataInvoice['factura']['cod_cliente'] ?? null),
538                );
539
540                continue;
541            }
542
543            $dataClient = $this->request('get', 'cliente/'.$dataInvoice['factura']['cod_cliente'], $region, []);
544
545            $invoice_number = $dataInvoice['factura']['n_factura'] ?? null;
546            $client_name = $dataClient['cliente']['empresa'] ?? null;
547            $client_email = $dataClient['cliente']['email'] ?? $dataClient['cliente']['email_facturacion'] ?? null;
548            $cif = $dataClient['cliente']['cliente_cif'] ?? null;
549            $issued_date = $dataInvoice['factura']['fecha_emision'] ?? null;
550            $expiration_date = $this->getVencimientosFormateados($dataInvoice);
551            $amount = $dataInvoice['factura']['importe_total_factura'] ?? null;
552            $collection_date = null;
553            $document = $dataInvoice['factura']['documento'] ?? null;
554
555            // FIRE-1293 (Berta): read-only regions record potential and skip send.
556            if (! $companyActive) {
557                $this->recordSkip(
558                    $region,
559                    (string) $invoice_number,
560                    'region_inactive',
561                    null,
562                    1,
563                    (string) ($dataInvoice['factura']['cod_cliente'] ?? null),
564                    (int) ($amount ?? 0),
565                );
566
567                continue;
568            }
569
570            // FIRE-1293 (Berta): create sent row first so SendGrid failures
571            // still count as sent attempts.
572            TblInvoiceReminders::create([
573                'invoice_number' => $invoice_number,
574                'client_name' => $client_name,
575                'client_email' => $client_email,
576                'cif' => $cif,
577                'issued_date' => $issued_date,
578                'expiration_date' => $expiration_date,
579                'amount' => $amount,
580                'collection_date' => $collection_date,
581                'region' => $region,
582                'reminder_type' => 1,
583            ]);
584
585            if (config('app.env') === 'production') {
586                $resultSend = $this->sendInvoice($invoice_number, $client_name, $client_email, $issued_date, $expiration_date, $document, $amount, 1, $region);
587
588                if (! $resultSend['success']) {
589                    $this->recordSkip(
590                        $region,
591                        (string) $invoice_number,
592                        'sendgrid_send_failed',
593                        ['error' => $resultSend['error'] ?? null],
594                        1,
595                        (string) ($dataInvoice['factura']['cod_cliente'] ?? null),
596                        (int) ($amount ?? 0),
597                    );
598                }
599            }
600        }
601
602        // type 3
603        foreach ($lastWeekInvoices as $reminder) {
604            if (! $reminder) {
605                continue;
606            }
607            $dataInvoice = $this->request('get', 'factura/'.$reminder, $region, []);
608            if ($this->checkClientHasFreshdeskTask($dataInvoice['factura']['cod_cliente'], $region)) {
609                $this->recordSkip(
610                    $region,
611                    (string) ($dataInvoice['factura']['n_factura'] ?? $reminder),
612                    'freshdesk_task_active',
613                    null,
614                    3,
615                    (string) ($dataInvoice['factura']['cod_cliente'] ?? null),
616                );
617
618                continue;
619            }
620
621            $cobrada = $dataInvoice['factura']['cobrada'];
622            $formaPago = $dataInvoice['factura']['forma_pago_factura'];
623
624            // Invoice already paied
625            // Invoice that the payment method is not "transferencia"
626            if (
627                $cobrada !== 'NO'
628                || stripos((string) $formaPago, 'tr') === false
629            ) {
630                $this->recordSkip(
631                    $region,
632                    (string) ($dataInvoice['factura']['n_factura'] ?? $reminder),
633                    $cobrada !== 'NO' ? 'already_paid' : 'payment_method_not_transfer',
634                    ['cobrada' => $cobrada, 'forma_pago' => $formaPago],
635                    3,
636                    (string) ($dataInvoice['factura']['cod_cliente'] ?? null),
637                );
638
639                continue;
640            }
641
642            $dataClient = $this->request('get', 'cliente/'.$dataInvoice['factura']['cod_cliente'], $region, []);
643
644            $invoice_number = $dataInvoice['factura']['n_factura'] ?? null;
645            $client_name = $dataClient['cliente']['empresa'] ?? null;
646            $client_email = $dataClient['cliente']['email'] ?? $dataClient['cliente']['email_facturacion'] ?? null;
647            $cif = $dataClient['cliente']['cliente_cif'] ?? null;
648            $issued_date = $dataInvoice['factura']['fecha_emision'] ?? null;
649            $expiration_date = $this->getVencimientosFormateados($dataInvoice);
650            $amount = $dataInvoice['factura']['importe_total_factura'] ?? null;
651            $collection_date = null;
652            $document = $dataInvoice['factura']['documento'] ?? null;
653
654            // FIRE-1293 (Berta): read-only regions record potential and skip send.
655            if (! $companyActive) {
656                $this->recordSkip(
657                    $region,
658                    (string) $invoice_number,
659                    'region_inactive',
660                    null,
661                    3,
662                    (string) ($dataInvoice['factura']['cod_cliente'] ?? null),
663                    (int) ($amount ?? 0),
664                );
665
666                continue;
667            }
668
669            // FIRE-1293 (Berta): create sent row first so SendGrid failures
670            // still count as sent attempts.
671            TblInvoiceReminders::create([
672                'invoice_number' => $invoice_number,
673                'client_name' => $client_name,
674                'client_email' => $client_email,
675                'cif' => $cif,
676                'issued_date' => $issued_date,
677                'expiration_date' => $expiration_date,
678                'amount' => $amount,
679                'collection_date' => $collection_date,
680                'region' => $region,
681                'reminder_type' => 3,
682            ]);
683
684            if (config('app.env') === 'production') {
685                $resultSend = $this->sendInvoice($invoice_number, $client_name, $client_email, $issued_date, $expiration_date, $document, $amount, 3, $region);
686
687                if (! $resultSend['success']) {
688                    $this->recordSkip(
689                        $region,
690                        (string) $invoice_number,
691                        'sendgrid_send_failed',
692                        ['error' => $resultSend['error'] ?? null],
693                        3,
694                        (string) ($dataInvoice['factura']['cod_cliente'] ?? null),
695                        (int) ($amount ?? 0),
696                    );
697                }
698            }
699        }
700
701    }
702
703    public function sendInvoice($invoice_number, $client_name, $client_email, $issued_date, $expiration_date, $document, $amount, $reminder_type, $region, $table = null, $month = null): array
704    {
705        if (
706            empty($invoice_number) ||
707            empty($client_name) ||
708            empty($client_email) ||
709            empty($issued_date) ||
710            empty($expiration_date) ||
711            empty($document) ||
712            empty($reminder_type)
713        ) {
714            Log::channel('g3w_invoices_not_send')->error("$invoice_number => {
715            'client_name': $client_name,
716            'client_email': $client_email,
717            'issued_date': $issued_date,
718            'expiration_date': $expiration_date,
719            'reminder_type': $reminder_type
720        }");
721
722            // FIRE-1293 — record the skip so the KPI surfaces partial-data
723            // dropouts (commonly: G3W returned an invoice with no document
724            // or no expiration date).
725            $missing = array_keys(array_filter([
726                'invoice_number' => empty($invoice_number),
727                'client_name' => empty($client_name),
728                'client_email' => empty($client_email),
729                'issued_date' => empty($issued_date),
730                'expiration_date' => empty($expiration_date),
731                'document' => empty($document),
732                'reminder_type' => empty($reminder_type),
733            ]));
734            $this->recordSkip(
735                $region,
736                $invoice_number ? (string) $invoice_number : null,
737                'missing_mandatory_field',
738                ['missing' => $missing],
739                $reminder_type ? (int) $reminder_type : null,
740                null,
741                $amount !== null ? (int) $amount : null,
742            );
743
744            return ['success' => false, 'message' => 'Campos obligatorios faltantes'];
745        }
746
747        try {
748            $emailTemplate = TblInvoiceRemindersEmailTemplate::where('type', $reminder_type)->first();
749
750            if (! $emailTemplate) {
751                throw new \Exception('No se encontró la plantilla de correo para este tipo de recordatorio');
752            }
753
754            // FIRE-1562: NO separar por '-'. El guion es válido dentro de dominios
755            // (foo@grupo-fire.es, avc@avc-administraciones.es, etc.). El splitter
756            // previo lo trataba como separador y aniquilaba emails perfectamente
757            // válidos → factura clasificada como no_valid_email → falso positivo
758            // en el panel de "no enviadas". Solo coma, punto-y-coma y whitespace.
759            $clientEmails = preg_split('/\s*[,;]\s*|\s+/', (string) $client_email, -1, PREG_SPLIT_NO_EMPTY);
760            $clientEmails = array_map(trim(...), $clientEmails);
761            $validEmails = [];
762            $invalidEmails = [];
763
764            foreach ($clientEmails as $email) {
765                if (filter_var($email, FILTER_VALIDATE_EMAIL)) {
766                    $validEmails[] = $email;
767                } else {
768                    $invalidEmails[] = $email;
769                    Log::channel('g3w_invoices_not_send')->warning("Email inválido omitido: $email para factura $invoice_number");
770                }
771            }
772
773            // FIRE-1293 — if SOME emails were invalid but at least one valid
774            // address remains, the reminder will go out. Record a soft skip
775            // so the KPI's "partial validation failure" panel reflects the
776            // data-quality issue without conflating it with a true miss.
777            if (! empty($invalidEmails) && ! empty($validEmails)) {
778                $this->recordSkip(
779                    $region,
780                    (string) $invoice_number,
781                    'partial_invalid_emails',
782                    ['invalid_emails' => $invalidEmails, 'valid_count' => count($validEmails)],
783                    (int) $reminder_type,
784                    null,
785                    (int) ($amount ?? 0),
786                );
787            }
788
789            if (empty($validEmails)) {
790                Log::channel('g3w_invoices_not_send')->error("No hay emails válidos para enviar factura $invoice_number");
791
792                // FIRE-1293 — persist this skip natively so the KPI dashboard
793                // captures it without depending on log parsing. Reason code
794                // matches the historical import (invoices:import-skips-from-log).
795                $this->recordSkip(
796                    $region,
797                    (string) $invoice_number,
798                    'no_valid_email',
799                    ['raw_email' => (string) $client_email],
800                    (int) $reminder_type,
801                    null,
802                    (int) ($amount ?? 0),
803                );
804
805                return ['success' => false, 'message' => 'No hay direcciones de correo válidas'];
806            }
807
808            $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
809            $successCount = 0;
810            $failCount = 0;
811
812            foreach ($validEmails as $toEmail) {
813                try {
814                    $email = new Mail;
815
816                    $fromEmail = 'recordatorio.factura@fire.es';
817                    $fromName = 'Recordatorio Facturas Grupo Fire';
818                    $email->setFrom($fromEmail, $fromName);
819                    $email->setReplyTo('recordatorio.facturas@fire.es', 'Recordatorio Facturas Grupo Fire');
820
821                    if ($reminder_type != 4) {
822                        $subject = str_replace(
823                            // ['{{invoice_number}}', '{{expiration_date}}', '{{amount}}'],
824                            ['{{invoice_number}}', '{{expiration_date}}'],
825                            [$invoice_number, $expiration_date, $amount],
826                            $emailTemplate->subject
827                        );
828                    } else {
829                        $subject = str_replace(
830                            // ['{{invoice_number}}', '{{expiration_date}}', '{{amount}}'],
831                            ['{{expiration_date}}'],
832                            [$month],
833                            $emailTemplate->subject
834                        );
835                    }
836
837                    $baseUrl = 'http://aiwf.fire.es/sepa/form/';
838
839                    $params = [
840                        'nombre_deudor' => $client_name,
841                        // 'cif_deudor' => $datos['cif'],
842                        // 'direccion_deudor' => urlencode($datos['direccion_deudor']),
843                        // 'codigo_postal' => urlencode($datos['codigo_postal']),
844                        // 'provincia_pais' => urlencode($datos['provincia_pais']),
845                        // 'iban' => $datos['iban'],
846                        // 'swift' => $datos['swift'],
847                        // 'nombre_apoderado' => urlencode($datos['nombre_apoderado']),
848                        // 'nif_apoderado' => $datos['nif_apoderado'],
849                        'fecha' => $issued_date,
850                        'email_contacto' => $client_email,
851                        'region' => $region,
852                    ];
853
854                    $url = $baseUrl.'?'.http_build_query($params);
855
856                    Carbon::setLocale('es');
857
858                    if ($reminder_type < 4) {
859                        $body = str_replace(
860                            ['{{invoice_number}}', '{{client_name}}', '{{issued_date}}', '{{expiration_date}}', '{{amount}}', '{{url}}'],
861                            [
862                                $invoice_number,
863                                $client_name,
864                                Carbon::createFromFormat('Y-m-d', $issued_date)->isoFormat('D [de] MMMM [de] YYYY'),
865                                $expiration_date,
866                                $amount,
867                                $url,
868                            ],
869                            $emailTemplate->html_content
870                        );
871                    } else {
872                        $body = str_replace(
873                            ['{{invoice_number}}', '{{client_name}}', '{{issued_date}}', '{{expiration_date}}', '{{amount}}', '{{table}}'],
874                            [
875                                $invoice_number,
876                                $client_name,
877                                Carbon::createFromFormat('Y-m-d', $issued_date)->isoFormat('D [de] MMMM [de] YYYY'),
878                                $expiration_date,
879                                $amount,
880                                $table,
881                            ],
882                            $emailTemplate->html_content
883                        );
884                    }
885
886                    $email->setSubject($subject);
887                    $email->addContent('text/html', $body);
888                    $email->addTo($toEmail, $client_name);
889
890                    if (is_array($document)) {
891                        foreach ($document as $doc) {
892                            if (! base64_decode((string) $doc['content'], true)) {
893                                throw new \Exception('El documento no es un base64 válido');
894                            }
895
896                            // Do not set Content-ID on the attachment: with
897                            // disposition=attachment, Gmail treats orphan inline
898                            // parts (Content-ID not referenced in the HTML body)
899                            // as strippable and drops the PDF during delivery.
900                            $attachment = new Attachment;
901                            $attachment->setContent($doc['content']);
902                            $attachment->setType('application/pdf');
903                            $attachment->setFilename($doc['filename']);
904                            $attachment->setDisposition('attachment');
905                            $email->addAttachment($attachment);
906
907                        }
908                    }
909
910                    if (! is_array($document)) {
911                        if (! base64_decode((string) $document, true)) {
912                            throw new \Exception('El documento no es un base64 válido');
913                        }
914
915                        // See the array branch above: no Content-ID on the
916                        // attachment so Gmail does not strip the PDF.
917                        $attachment = new Attachment;
918                        $attachment->setContent($document);
919                        $attachment->setType('application/pdf');
920                        $attachment->setFilename("Factura_{$invoice_number}.pdf");
921                        $attachment->setDisposition('attachment');
922                        $email->addAttachment($attachment);
923                    }
924
925                    try {
926                        $response = $sendgrid->send($email);
927                        SendgridLogger::log($email, $response);
928                    } catch (\Throwable $sendException) {
929                        SendgridLogger::logException($email, $sendException);
930                        throw $sendException;
931                    }
932
933                    if ($response->statusCode() == 202) {
934                        $successCount++;
935                        Log::channel('g3w_invoices_sent')->info("Factura $invoice_number enviada exitosamente a: $toEmail");
936                    } else {
937                        $failCount++;
938                        Log::channel('g3w_invoices_not_send')->error("Error enviando a $toEmail: ".$response->body());
939                    }
940
941                } catch (\Exception $e) {
942                    $failCount++;
943                    Log::channel('g3w_invoices_not_send')->error("Error enviando a $toEmail: ".$e->getMessage());
944
945                    continue;
946                }
947            }
948
949            if ($successCount > 0) {
950                return [
951                    'success' => true,
952                    'message' => "Enviados: $successCount, Fallidos: $failCount",
953                    'sent_count' => $successCount,
954                    'failed_count' => $failCount,
955                ];
956            } else {
957                return [
958                    'success' => false,
959                    'message' => 'Todos los envíos fallaron',
960                    'sent_count' => $successCount,
961                    'failed_count' => $failCount,
962                ];
963            }
964
965        } catch (\Exception $e) {
966            Log::channel('g3w_invoices_not_send')->error("Error general enviando factura $invoice_number: ".$e->getMessage());
967
968            return ['success' => false, 'message' => $e->getMessage()];
969        }
970    }
971
972    public function getAllInvoices($region = 'Cataluña')
973    {
974        if ($region === 'All') {
975            $invoices = TblInvoiceReminders::orderBy('id', 'desc')
976                ->paginate(50);
977        } else {
978            $invoices = TblInvoiceReminders::where('region', $region)
979                ->orderBy('id', 'desc')
980                ->paginate(50);
981        }
982
983        return response()->json([
984            'invoices' => $invoices->items(),
985            'pagination' => [
986                'total' => $invoices->total(),
987                'current_page' => $invoices->currentPage(),
988                'per_page' => $invoices->perPage(),
989                'last_page' => $invoices->lastPage(),
990            ],
991        ]);
992    }
993
994    public function getAllInvoicesExceptions()
995    {
996        $invoices = TblInvoicesExceptions::orderBy('id', 'desc')->paginate(50);
997
998        return response()->json([
999            'invoices' => $invoices->items(),
1000            'pagination' => [
1001                'total' => $invoices->total(),
1002                'current_page' => $invoices->currentPage(),
1003                'per_page' => $invoices->perPage(),
1004                'last_page' => $invoices->lastPage(),
1005            ],
1006        ]);
1007    }
1008
1009    public function sendCyCInvoices(?string $region): array
1010    {
1011        try {
1012            if (! $region) {
1013                throw new Exception('No region provided');
1014            }
1015
1016            $fromDay = 45;
1017            $toDay = 35;
1018            $today = Carbon::createFromDate(date('Y'), date('m'), date('d'));
1019            $todayFormatted = Carbon::now()->format('Y-m-d');
1020
1021            $documentName = $todayFormatted.'.csv';
1022
1023            $filePath = storage_path('app/public/uploads/CyC/'.$todayFormatted.'/'.$region.'/'.$documentName);
1024
1025            if (! file_exists(dirname($filePath))) {
1026                mkdir(dirname($filePath), 0777, true);
1027            }
1028
1029            $file = fopen($filePath, 'w');
1030
1031            fputcsv($file, [
1032                'Service',
1033                'Language',
1034                'Cyc Poliza',
1035                'SP Tax Idenfication Number',
1036                'SP Country',
1037                'BP TaxIdentificationNumber',
1038                'SP Corporate Name',
1039                'BP Country',
1040                'Invoice Number',
1041                'Invoice Issue Date',
1042                'PD Installment Due Date',
1043                'PD Payment Means',
1044                'Total Amount',
1045                'Taxable Base',
1046                'Tax Rate',
1047                'Tax Amount',
1048            ], ';', escape: '\\');
1049
1050            while ($fromDay > $toDay) {
1051                $date = $today->copy()->subDays($fromDay)->format('Y-m-d');
1052                $invoices = $this->request('get', 'factura/vence/'.$date, $region, []);
1053                foreach ($invoices['facturas'] as $invoice) {
1054                    $invoiceData = $this->request('get', 'factura/'.$invoice['ID'], $region, []);
1055                    $invoiceData = $invoiceData['factura'];
1056
1057                    $dataClient = $this->request('get', 'cliente/'.$invoiceData['cod_cliente'], $region, []);
1058                    $invoiceCobrada = $invoiceData['cobrada'];
1059                    $invoiceDocument = $invoiceData['documento'];
1060                    $invoiceNumber = $invoiceData['n_factura'];
1061
1062                    if (
1063                        ! $invoiceDocument ||
1064                        $invoiceCobrada !== 'NO') {
1065                        continue;
1066                    }
1067
1068                    if ($region === 'Cataluña' && stripos((string) $invoiceNumber, 'R') === false) {
1069                        continue;
1070                    }
1071
1072                    fputcsv($file, [
1073                        'grabacionFacturas',
1074                        'ESP',
1075                        '156547',
1076                        'B67795088',
1077                        'ESP',
1078                        $dataClient['cliente']['cliente_cif'],
1079                        $dataClient['cliente']['empresa'],
1080                        'ESP',
1081                        $invoiceNumber,
1082                        $invoiceData['fecha_creacion'],
1083                        $invoiceData['vencimientos'][0]['fecha_vencimiento'],
1084                        $invoiceData['forma_pago_factura'],
1085                        $invoiceData['importe_total_factura'],
1086                        $invoiceData['base_imponible_factura'],
1087                        $invoiceData['iva_factura'] * 100,
1088                        $invoiceData['importe_iva_factura'],
1089                    ], ';', escape: '\\');
1090
1091                }
1092                $fromDay--;
1093            }
1094
1095            fclose($file);
1096
1097            return ['success' => true];
1098        } catch (\Exception $e) {
1099            Log::channel('g3w_invoices')->error($e->getMessage());
1100
1101            return ['success' => false, 'error' => $e->getMessage()];
1102        }
1103    }
1104
1105    public function setAllMonthAdministratorsInvoices($region): array
1106    {
1107        if (! $region) {
1108            return ['success' => false, 'error' => 'No region provided'];
1109        }
1110
1111        try {
1112            $now = Carbon::now();
1113            $month = $now->format('m');
1114        } catch (\Exception $e) {
1115            Log::channel('g3w_invoices')->error('Formato de fecha de mes no válido (esperado YYYY-mm)');
1116
1117            return ['success' => false, 'error' => 'Formato de fecha de mes no válido (esperado YYYY-mm)'];
1118        }
1119
1120        $invoices = $this->request('get', 'factura/vencemesadministrador/'.$month, $region, []);
1121
1122        foreach ($invoices['facturas'] as $invoice) {
1123            $invoiceData = $this->request('get', 'factura/'.$invoice['ID'], $region, []);
1124            $invoiceData = $invoiceData['factura'];
1125
1126            // Continue if region is Comunidad Valenciana and the invoice starts with M
1127            if (
1128                $region == 'Comunidad Valenciana'
1129                && str_starts_with((string) $invoice['ID'], 'M')
1130            ) {
1131                continue;
1132            }
1133
1134            // FIRE-1586: Aeroextinción (Andalucía / prefix C) — no reminders.
1135            if (
1136                $region == 'Andalucía'
1137                && str_starts_with((string) $invoice['ID'], 'C')
1138            ) {
1139                continue;
1140            }
1141
1142            $cobrada = $invoiceData['cobrada'];
1143            $formaPago = $invoiceData['forma_pago_factura'];
1144            // Invoice already paied
1145            // Invoice that the payment method is not "transferencia"
1146            if (
1147                $cobrada !== 'NO'
1148                || stripos((string) $formaPago, 'tr') === false
1149            ) {
1150                continue;
1151            }
1152
1153            $dataClient = [];
1154            if ($invoice['cod_administrador']) {
1155                $dataClient = $this->request('get', 'cliente/'.$invoice['cod_administrador'], $region, []);
1156                $dataClient = $dataClient['cliente'];
1157            }
1158
1159            if ($invoice['cod_cliente']) {
1160                $dataClientAdministrado = $this->request('get', 'cliente/'.$invoice['cod_cliente'], $region, []);
1161                $dataClientAdministrado = $dataClientAdministrado['cliente'];
1162                if ($dataClientAdministrado['tipo_cliente'] === 'Grandes Cuentas') {
1163                    continue;
1164                }
1165            }
1166
1167            /*if (
1168                $dataClient["tipo_cliente"] !== "Administrador"
1169                ){
1170                continue;
1171            }*/
1172
1173            $codService = $invoiceData['lineas'][0]['cod_servicio'];
1174            $dataService = $this->request('get', 'servicio/'.$codService, $region, []);
1175            $dataService = $dataService['servicio'];
1176
1177            TblInvoiceAdministrators::create([
1178                'invoice_number' => $invoiceData['n_factura'],
1179                'region' => $region,
1180                'name' => $dataClient['empresa'],
1181                'CIF' => $dataClient['cliente_cif'],
1182                'email' => $dataClient['email'],
1183                'service_name' => $dataService['nombre_servicio'],
1184                'service_addres' => $dataService['direccion'],
1185                'send_date' => Carbon::now('Europe/Madrid')->toDateString(),
1186                'expiration_date' => Carbon::parse($invoice['fecha_vencimiento'])->toDateString(),
1187                'amount' => $invoiceData['importe_total_factura'],
1188                'cod_administrador' => $invoice['cod_administrador'],
1189                'cod_cliente' => $invoice['cod_cliente'],
1190            ]);
1191        }
1192
1193        return [
1194            'success' => true,
1195            'message' => "Proceso completado para el mes $month en la región $region.",
1196        ];
1197    }
1198
1199    /**
1200     * @return 'No region provided'[]|'Processing complete'[]|bool[]
1201     */
1202    public function sendAdministratorsInvoices($region): array
1203    {
1204        if (! $region) {
1205            return ['success' => false, 'error' => 'No region provided'];
1206        }
1207
1208        $month = [
1209            1 => 'Enero',
1210            2 => 'Febrero',
1211            3 => 'Marzo',
1212            4 => 'Abril',
1213            5 => 'Mayo',
1214            6 => 'Junio',
1215            7 => 'Julio',
1216            8 => 'Agosto',
1217            9 => 'Septiembre',
1218            10 => 'Octubre',
1219            11 => 'Noviembre',
1220            12 => 'Diciembre',
1221        ];
1222
1223        $startOfMonth = Carbon::now()->startOfMonth()->toDateString();
1224        $endOfMonth = Carbon::now()->endOfMonth()->toDateString();
1225
1226        $currentMonthInvoices = TblInvoiceAdministrators::where('region', $region)
1227            ->where('paid', 0)
1228            ->whereBetween('expiration_date', [$startOfMonth, $endOfMonth])
1229            ->orderBy('expiration_date', 'asc')
1230            ->get();
1231
1232        $invoicesGroupedByAdministrator = $currentMonthInvoices->groupBy('cod_administrador');
1233
1234        foreach ($invoicesGroupedByAdministrator as $administratorCode => $administratorInvoices) {
1235            $table = "<table style='border-collapse: collapse; width: 100%; font-family: Arial, sans-serif;'>
1236            <tr>
1237                <th style='border: 1px solid #999; padding: 8px; background-color: #f2f2f2; text-align: left;'>Número Factura</th>
1238                <th style='border: 1px solid #999; padding: 8px; background-color: #f2f2f2; text-align: left;'>Nombre cliente de servicio</th>
1239                <th style='border: 1px solid #999; padding: 8px; background-color: #f2f2f2; text-align: left;'>Dirección del servicio</th>
1240                <th style='border: 1px solid #999; padding: 8px; background-color: #f2f2f2; text-align: left;'>Fecha de emisión</th>
1241                <th style='border: 1px solid #999; padding: 8px; background-color: #f2f2f2; text-align: left;'>Fecha de vencimiento</th>
1242                <th style='border: 1px solid #999; padding: 8px; background-color: #f2f2f2; text-align: left;'>Importe</th>
1243            </tr>";
1244
1245            $documentsBase64 = [];
1246
1247            foreach ($administratorInvoices as $invoice) {
1248                if ($this->checkClientHasFreshdeskTask($invoice->cod_cliente, $invoice->region)) {
1249                    continue;
1250                }
1251
1252                $invoiceNumber = $invoice->invoice_number;
1253                $table .= "<tr>
1254                    <td class='invoice_number' style='border: 1px solid #999; padding: 8px;'>".$invoiceNumber."</td>
1255                    <td class='invoice_service_name' style='border: 1px solid #999; padding: 8px;'>".$invoice->service_name."</td>
1256                    <td class='invoice_service_addres' style='border: 1px solid #999; padding: 8px;'>".$invoice->service_addres."</td>
1257                    <td class='invoice_send_date' style='border: 1px solid #999; padding: 8px;'>".$invoice->send_date."</td>
1258                    <td class='invoice_expiration_date' style='border: 1px solid #999; padding: 8px;'>".$invoice->expiration_date."</td>
1259                    <td class='invoice_amount' style='border: 1px solid #999; padding: 8px;'>".$invoice->amount.' €</td>
1260                </tr>';
1261
1262                $invoice = $this->request('get', 'factura/'.$invoiceNumber, $region, []);
1263                $invoiceData = $invoice['factura'];
1264
1265                $documentsBase64[] = [
1266                    'content' => $invoiceData['documento'],
1267                    'filename' => "Factura_{$invoiceNumber}.pdf",
1268                ];
1269            }
1270
1271            $table .= '</table>';
1272
1273            if (empty($documentsBase64)) {
1274                continue;
1275            }
1276
1277            $monthText = $month[Carbon::now()->format('n')];
1278
1279            $invoiceSendData = $this->sendInvoice(
1280                $administratorInvoices->first()->invoice_number,
1281                $administratorInvoices->first()->name,
1282                $administratorInvoices->first()->email,
1283                $administratorInvoices->first()->send_date,
1284                $administratorInvoices->first()->expiration_date,
1285                $documentsBase64,
1286                $administratorInvoices->first()->amount,
1287                4,
1288                $region,
1289                $table,
1290                $monthText.' de '.Carbon::now()->format('Y')
1291            );
1292
1293            TblInvoiceReminders::create([
1294                'invoice_number' => $administratorInvoices->first()->invoice_number,
1295                'client_name' => $administratorInvoices->first()->name,
1296                'client_email' => $administratorInvoices->first()->email,
1297                'cif' => 'Administradores',
1298                'issued_date' => date('Y-m-d'),
1299                'expiration_date' => $administratorInvoices->first()->expiration_date,
1300                'amount' => $administratorInvoices->first()->amount,
1301                'collection_date' => null,
1302                'region' => $region,
1303                'reminder_type' => 4,
1304            ]);
1305
1306            TblInvoiceReminders::create([
1307                'invoice_number' => $administratorInvoices->first()->invoice_number,
1308                'client_name' => $administratorInvoices->first()->name,
1309                'client_email' => $administratorInvoices->first()->email,
1310                'cif' => 'Administradores',
1311                'issued_date' => date('Y-m-d'),
1312                'expiration_date' => $administratorInvoices->first()->expiration_date,
1313                'amount' => $administratorInvoices->first()->amount,
1314                'collection_date' => null,
1315                'region' => $region,
1316                'reminder_type' => 4,
1317            ]);
1318
1319        }
1320
1321        return ['success' => true, 'message' => 'Processing complete'];
1322    }
1323
1324    private function checkClientHasFreshdeskTask($client, $region): false
1325    {
1326        try {
1327            $response = Http::withBasicAuth('titan_auto', 'n71Mhh8i7FO_h2fF8e4')
1328                ->post('https://aiwf.ibvgroup.com/webhook/client-lookup', [
1329                    'client_id' => $client,
1330                    'region' => $region,
1331                ]);
1332
1333            if ($response->successful()) {
1334                return $response->json('found', false);
1335            }
1336
1337            return false;
1338
1339        } catch (\Exception $e) {
1340            Log::error('Error on checkClientHasFreshdeskTask: '.$e->getMessage());
1341
1342            return false;
1343        }
1344    }
1345
1346    public function checkClientHasFreshdeskTaskAll(): array|false
1347    {
1348        try {
1349            $clients = TblInvoiceAdministrators::all();
1350            $codClients = [];
1351            foreach ($clients as $client) {
1352                $response = Http::withBasicAuth('titan_auto', 'n71Mhh8i7FO_h2fF8e4')
1353                    ->post('https://aiwf.ibvgroup.com/webhook/client-lookup', [
1354                        'client_id' => $client->cod_cliente,
1355                        'region' => $client->region,
1356                    ]);
1357                $data = $response->json();
1358
1359                if (isset($data['found']) && $data['found'] === true) {
1360                    $codClients[] = "{$client->cod_cliente} en {$client->region}";
1361                }
1362            }
1363
1364            return $codClients;
1365
1366        } catch (\Exception $e) {
1367            Log::error('Error on checkClientHasFreshdeskTask: '.$e->getMessage());
1368
1369            return false;
1370        }
1371    }
1372
1373    public function sendCallCenterInvoices()
1374    {
1375        $spreadsheetId = '1JxCICtqPtPrE8B_0nVJZYwIZ67Nv5gPYr9rW73Pz1_Q';
1376        $sheetName = 'Hoja 1';
1377
1378        try {
1379            $googleSheetsService = $this->getGoogleSheetsService();
1380
1381            $region = [
1382                'G3W Cat' => 'Cataluña',
1383                'G3W Mad' => 'Madrid',
1384                'G3W Val' => 'Comunidad Valenciana',
1385                // "G3W Alm"=>"Andalucia",
1386            ];
1387
1388            $fromEmail = [
1389                'G3W Cat' => 'fire@fire.es',
1390                'G3W Mad' => 'atencion@fire.es',
1391                'G3W Val' => 'facturacionval@fire.es',
1392                // "G3W Alm"=>"documentacion.almeria@fire.es",
1393            ];
1394
1395            // A: Entorno, B: ID Factura, C: Email, D: Enviado, E: Fecha Envío
1396            $range = $sheetName.'!A2:E';
1397            $response = $googleSheetsService->spreadsheets_values->get($spreadsheetId, $range);
1398            $rows = $response->getValues();
1399
1400            if (empty($rows)) {
1401                return;
1402            }
1403
1404            foreach ($rows as $index => $row) {
1405                $currentRowNumber = $index + 2;
1406
1407                $entorno = $row[0] ?? null;
1408                $idFactura = $row[1] ?? null;
1409                $emailToSend = $row[2] ?? null;
1410                $enviado = $row[3] ?? 'NO';
1411
1412                if ($idFactura && $emailToSend && strtoupper($enviado) !== 'SI') {
1413
1414                    try {
1415                        // --- AQUÍ LLAMAS A TU LÓGICA DE ENVÍO ---
1416                        if (
1417                            $region[$entorno] == 'Comunidad Valenciana'
1418                            && str_starts_with((string) $idFactura, 'M')
1419                        ) {
1420                            continue;
1421                        }
1422
1423                        // FIRE-1586: Aeroextinción (Andalucía / prefix C).
1424                        if (
1425                            $region[$entorno] == 'Andalucía'
1426                            && str_starts_with((string) $idFactura, 'C')
1427                        ) {
1428                            continue;
1429                        }
1430
1431                        $invoiceData = $this->request('get', 'factura/'.$idFactura, $region[$entorno], []);
1432                        $invoiceData = $invoiceData['factura'];
1433
1434                        $cobrada = $invoiceData['cobrada'];
1435                        $formaPago = $invoiceData['forma_pago_factura'];
1436                        // Invoice already paied
1437                        // Invoice that the payment method is not "transferencia"
1438                        if (
1439                            $cobrada !== 'NO'
1440                            || stripos((string) $formaPago, 'tr') === false
1441                        ) {
1442                            continue;
1443                        }
1444
1445                        $html = '<!doctypehtml><html lang=es><meta charset=UTF-8><meta content="width=device-width,initial-scale=1"name=viewport><title>Recordatorio de Pago - Vencimiento en 1 semana</title><style>body{font-family:Arial,sans-serif;font-size:11;line-height:1.6;max-width:600px;margin:0 auto;padding:20px}@media only screen and (max-width:600px){body{padding:15px}}</style><p>Estimado cliente,</p><p>Le contactamos desde <strong>Grupo Fire</strong>, empresa responsable del mantenimiento de sus extintores, en relación con las <strong>facturas pendientes de pago</strong> que se detallan en los documentos adjuntos.</p><p>Tras revisar nuestra contabilidad, hemos comprobado que, a día de hoy, los importes correspondientes aún no han sido abonados. Por ello, le agradeceríamos que nos indicara si existe alguna incidencia o motivo que haya podido impedir el pago (por ejemplo: cambio de cuenta bancaria, domiciliación no autorizada, disconformidad con la factura, etc.).</p><p>En caso de no existir ninguna incidencia, le agradeceríamos proceder al abono de los importes pendientes a la mayor brevedad. Puede realizar el pago mediante ingreso o transferencia a la cuenta bancaria que figura en las facturas adjuntas.</p><p>Un cordial saludo,<br><img alt=""src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAUAAAAB9CAYAAADTA7ptAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAAO3RFWHRDb21tZW50AHhyOmQ6REFGX1pUUHROLUk6MixqOjU3NDkyNjY0NzQyMDk5MjEyODMsdDoyNDAzMTMxM8+vtAYAAATsaVRYdFhNTDpjb20uYWRvYmUueG1wAAAAAAA8eDp4bXBtZXRhIHhtbG5zOng9J2Fkb2JlOm5zOm1ldGEvJz4KICAgICAgICA8cmRmOlJERiB4bWxuczpyZGY9J2h0dHA6Ly93d3cudzMub3JnLzE5OTkvMDIvMjItcmRmLXN5bnRheC1ucyMnPgoKICAgICAgICA8cmRmOkRlc2NyaXB0aW9uIHJkZjphYm91dD0nJwogICAgICAgIHhtbG5zOmRjPSdodHRwOi8vcHVybC5vcmcvZGMvZWxlbWVudHMvMS4xLyc+CiAgICAgICAgPGRjOnRpdGxlPgogICAgICAgIDxyZGY6QWx0PgogICAgICAgIDxyZGY6bGkgeG1sOmxhbmc9J3gtZGVmYXVsdCc+RGlzZcOxbyBzaW4gdMOtdHVsbyAtIDE8L3JkZjpsaT4KICAgICAgICA8L3JkZjpBbHQ+CiAgICAgICAgPC9kYzp0aXRsZT4KICAgICAgICA8L3JkZjpEZXNjcmlwdGlvbj4KCiAgICAgICAgPHJkZjpEZXNjcmlwdGlvbiByZGY6YWJvdXQ9JycKICAgICAgICB4bWxuczpBdHRyaWI9J2h0dHA6Ly9ucy5hdHRyaWJ1dGlvbi5jb20vYWRzLzEuMC8nPgogICAgICAgIDxBdHRyaWI6QWRzPgogICAgICAgIDxyZGY6U2VxPgogICAgICAgIDxyZGY6bGkgcmRmOnBhcnNlVHlwZT0nUmVzb3VyY2UnPgogICAgICAgIDxBdHRyaWI6Q3JlYXRlZD4yMDI0LTAzLTEzPC9BdHRyaWI6Q3JlYXRlZD4KICAgICAgICA8QXR0cmliOkV4dElkPjY4N2E5Y2JkLTg3NTctNDkxMi1hNDcwLTdjODk2YTI5NTkwNzwvQXR0cmliOkV4dElkPgogICAgICAgIDxBdHRyaWI6RmJJZD41MjUyNjU5MTQxNzk1ODA8L0F0dHJpYjpGYklkPgogICAgICAgIDxBdHRyaWI6VG91Y2hUeXBlPjI8L0F0dHJpYjpUb3VjaFR5cGU+CiAgICAgICAgPC9yZGY6bGk+CiAgICAgICAgPC9yZGY6U2VxPgogICAgICAgIDwvQXR0cmliOkFkcz4KICAgICAgICA8L3JkZjpEZXNjcmlwdGlvbj4KCiAgICAgICAgPHJkZjpEZXNjcmlwdGlvbiByZGY6YWJvdXQ9JycKICAgICAgICB4bWxuczpwZGY9J2h0dHA6Ly9ucy5hZG9iZS5jb20vcGRmLzEuMy8nPgogICAgICAgIDxwZGY6QXV0aG9yPkx1aXMgUmV5ZXM8L3BkZjpBdXRob3I+CiAgICAgICAgPC9yZGY6RGVzY3JpcHRpb24+CgogICAgICAgIDxyZGY6RGVzY3JpcHRpb24gcmRmOmFib3V0PScnCiAgICAgICAgeG1sbnM6eG1wPSdodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAvJz4KICAgICAgICA8eG1wOkNyZWF0b3JUb29sPkNhbnZhPC94bXA6Q3JlYXRvclRvb2w+CiAgICAgICAgPC9yZGY6RGVzY3JpcHRpb24+CiAgICAgICAgCiAgICAgICAgPC9yZGY6UkRGPgogICAgICAgIDwveDp4bXBtZXRhPtdFDlYAAFQBSURBVHic7L15nBxVuf//fk5V9/TsmewEEpKQDRIk7GiQRUS4LIqIiPfiAngBUb4ul+Xyc7kg6r0RBRWM4EVBMYoLIpsiJATZwnINGgKBAElICFmHTGbt6a46z++PU9Xp6elJJjM9mQn25/Xq10x3VZ1z6tQ5Tz37I6qq7AGwarEInlWsDcksWkh22UtUfuo87PBqEuqjKJ7xBnuoZZRRxh4Cf7AH0FsYFGshFCV8fRVt835M+PYmgs2bqP7858mOHYVvEoM9zDLKKGMPghnsAfQWFkEUdNMWWn/0fezmRtR6ZB9/io5vfAvZsBEJlT2EoS2jjDKGAPYIAhgTNYOlbf7tZJ9/HsQDEVQs7S8tZdvV3yRsbCwTwDLKKKPXGJIEUDXi5KK/iiJBQMeDDxI88DCITygBqooARg325Zdo+eH1hC3NqLXb2yijjDLK6AFDkgAC2PijYDUku2wZLT/6X4JsGqMgGIwIIIAiYuh8fDHtd/wSDUJUQ6wqoYaDeyNllFHGkMWQJIAKCI4DNFjY3EjLjTcizY2oNYQoEnOHqogI1lp8LOnf30Xb44+jKCHglZnAMsooowcMSQIoCKhgxRJ0pNl26/+SXfEyoRhELEYtNuL+RMRdI4JVRcKQznnzsGvX4CmoDMlbLKOMMoYAhiR1cEybRUJL+t57yS5cgMG5uBgNUWMQtY5LLLjQYsluWEfH7/5AGKTRwnPKKKOMMiIMAQKooBYbibOhOu2fqJL5xwt0/PSnkA2xhIhR1BhQQ8Qkbm9FFRWwKJ5JkPnLQ9gXX0SwqIaoKpawbBgpo4wychgCBFCwgFpQtRgLKARr19D63e+jnRknEotBFMAgogjG/R63IoKo4IkQqCXb0U7bb+8iDCyoIVCNrqfME5ZRRhnAkCCAkdEjomVWLLa1ldb//Sl2zUpCiV1ietOOM5wIYPCwS/6GffFlQgu+KIoX9VYmgWWUUcZQIICqGBQRxWIwNqT997+n8/HFiDEYVYxqd31fEYhAiCAYIMB2ZOl46AEIs4jaXAuyo0bKKKOMfxoMPgGMrLmWELWW7JNP0z7/l0AGa62L9jCmi75vB43hIYiGWPFQVbKPPI7d2kgggomdq8sksIwyymAwCKA6g4VVxaI4m4QTWln1Gtt++CM0mwEFifV+Shd93047EHe2iCVoaiJ4fkl0tYKYsgRcRhllAINAAK3EHQvGgqgSoti336bl5p8QvPVmFOkh22XVXWLYJEcAAYwxZJ5+FpsJI5eY3pPSMsoo452NQeAAHYeWc1sRi2ct7fPvJPPsYsT3oISuKuoZgldegq1vR0FzikqZBSyjjDIGgQDGIW4qLskpYUDrgr+QvvdeRA0aJT4tJcItjYSNm0EMYZ47TBlllPHPjcEzgqhirJJ++VU6b/wJmm6LRF2D108C2MXZWS02HWBXrkKdTaWsAiyjjDKAQSCAul0QJdv0Nh03/YigqRERDxBMQYRHXxDHB+e+a5bs2jVYwCOk7AhTRhllwCCJwCGKTafpuPV2Msv+0Y1glRIqglGwb21CBESFAeyujDLK2IOw+0VgC2pDMg8uoPPhPyMDnK1FASsG2daCakCRFApllFHGPyl2vwgslvCFl0jfPI+wMxtFbeQdH4BMziqGMN0GmYzzAyyjjDLKYIAJoCVyeo4cnlUVu2E9rd/9PkG6A1frrWvGZhEpqUgsUWwwGrj0CUqUaj/+69LnY91vNs5Mgy3ZGAYDO3uRDFZWnLjfnsa3u8c1FMawoz7z56lwzgaj7MOO+hwK49tVDGhZTIlCzwAsAdqRpvl/b8euXomVADWJAXdJEREU64gbYNSiIs4BWxSxQHsrGEErq4iT7KMyFAIF+4X4RVJsEea/ZOKs2gOB/Lbj//P/7mhcuwP548hPrtsbFF7X33HEbRb7P3+s+b8PxnwVIh5P4Xrb3WPrCwaYA4yID0CoZH73RzKLHkbFIuoSHxg7wIXMrYv9FeNjxOURlMgZW1TRzizNt/6M9G//gAQhGrnJ6B7w8HrCjris/EVaeKyUb+v8jVrIxfTU5444i1Ijv82+Sh2llFYKiUbhfBXjrvL/FjtnIBD3sbNntSPOdShhQDlAIxZVQW1A8Ld/0PyrnyNhgGoCFfAJyYjFH0C3FBFxKbKqKsH3oxpKJiLLhszDfya8+26aPYM3fSrJI4/CJSWEPZUFjGukFOMArbUYY3Ln5S/SUr+x87m9/HH0RAR74iAGgpPYVQ64J261VNxOMY44/+XR0xgLrxtorqtw3mJYa3O/9XTOUMTAisBRslP7xhpavzcXae9E8VAJ8EMh8CCpkosPHggoFrFghtWixsOoJRQwQZbsS6/Q9tOfEqhigoCWG79P3ehv4U2chDfAjOlAIl508aLs6Oigvb2dIAgQEXzfp6qqisrKytz5MVEciHGoKmEYYq0lm826LD3RXxEhmUySTCbxfT9HuAuJ50CMKR/xXBljehTzCpE/1lKNK243DENEhDAMc88tPi/+P5FIkEgkuhDhgVRnFI43f521tLTQ2dmZe8FWV1dTVVUF9DynQwElI4AKroalRCInFrVA8zbabrmZcN0GF4MrBgFCLyaQbhID9UgQujC4XN0PxSAEAp7mJUfYBVhRPPWRsXtHSbcsKIRvv03zDddj394ajSkkeHM97bf8hJqvfgVbWwtWMVHyGMFloR6KPtSFmzOTybBx40YWL17M448/zooVK2hqasptpEQiwbBhw5gwYQL77bcfs2bN4sgjj2TEiBElXagrV67kpz/9KVu3bqW5uZnW1lY6Ojqw1pJOp3ObpbKykvr6evbee2+mT5/OYYcdxrRp06ipqcHLexOVitD85S9/4c477+xGVEaNGsXVV19NVVVVl2NtbW1cf/31rFy5sptYf/LJJ/Oxj32sZJtcVZk/fz5/+MMfEBEymQyZTKYoV1xTU8Nee+3FfvvtxxFHHMEBBxxAXV1dbs5KSRALRe2Ojg5ef/11Fi1axLPPPsuaNWtyz9bzPOrq6pgyZQpz5szh2GOPZdy4cXieN2i6y55QMgIouPhe1RBRn8A4B5f23/yGjqefQQ2krE9A2M0VJbSQJARRQvVICgRiIvtJgKjLHtMnu6wkUBPgTxyPKngYbHsbzfNuJnxtJYLFWA81CSAk/exzePN/RfW/fwY8JVSDh8WKhxEd0rlkrLVs27aNm266iZ///OesXr26qMEhfzMZYzDGcMstt/DJT36yC8HpL9544w2++93vduFo4v57EvlijvDoo4/mmmuu4aijjurGZfVnU4sIL7/8Mr/61a8Iw64eCPvttx9XXXVVjjOO++rs7GTBggU8+eSTOa5PVfE8j7322ouzzz67pLrApUuXct999+W40sLxFxqTRATP8zj88MO55pprOP744zHGdOHS+jK+nlQVr7zyCt/+9rd54IEH2LZtWxd1Sz4WLVrErbfeyoQJE7jooou49NJLSaVSubENBSJYOrlHQQmx+KgoEiqZRYvouOtuPGtIaEjGi0Ph8iCCEYM1SlaEhHEVfY3txEhIgIcaQ1/Lm4taqKkkMX4SIi4lVse995F97FEwLk2+GMXaAEGQMEP697+l45HHkDDjYoejcpyD/7h6hqqybNkyPvShD/Gtb32LVatW5TaQtbab6BcvvpgLq6+vL7m+JgiCXB/F9JHx5s0ncDF3uGjRIv71X/+VRYsWdRl//tj7inzC0pNSf0dicLF7KOXcFRLmQhSqK1SVIAh45pln+PSnP83dd99dkvkqfOmEYchvf/tbTj31VH7961/T1NS0Q2OIqmKtZe3atVx99dV86lOfYsOGDUNKL1hCxY+4fMxiUUKC11+nZd4P0HQHiiKaQDWgu9+L4kqdGzw1YENkyv5UXfAZTG0DnrEYG2Ckj355RvAaRiOjRiNqyPxtCW2/+RVkQayzBoeEEInc4BFmQ9puvons629ERZqsO7e/QcoDBFXltdde4/Of/zxPPfVUTr8WHyvUqRXbEGPGjCm5HjBur5DAxAR39OjRjBgxglQqlTsWjy8MQ9auXct///d/097e3u1++4Oe3IN21fgyEMaHHekUjTHU1NRQW1tLRUVFlzHExGb9+vVcffXVrFu3rkdOu7coJPgPPfQQl112GW+88QZhGHYxzsSqlWQy2e0+wjCks7OTe+65h7lz59LR0dGn8QwESmgEUSwWsZawaRvpG79PuKkJg0ElS1YMRrs/XEVQiVKVSoim6qj7wiUkZh2IP/sQ2r77XYI1a1A1RYhnL5ANSO6/P9JQR7B+PW3fvR5t3IYacbVIVDD4aOSSbcQi1sds3kzbTTfhX3M1NIyIUvcPXRH4hhtu4KmnnspxN/mbuKKigrq6um76ofb2dsIwxPM8JkyYUNI3c08cZ6zz+973vseBBx5INptlxYoVXHfddbz00kvd2nnxxRd5+eWXOeyww0o+tmLYmZ9isXNLYUnvjTV36tSpfPe738XzPDZu3Mgdd9zBo48+2m2uV65cyaJFizj33HP7NJZiY9q6dStf+9rXWL9+fRcOWkQYN24cn/zkJ5kxYwYiwqOPPsrvfvc7WlpaurQRhiG//OUvOf300znhhBOGhAjcZwKoGmLxMFHMhGhk1MgGtP/iDtLP/yPiABSD1814kL8xQsQZORJJqj/7aRIz34UYQ/LAgzFz59I+7yY6n3wCsZ4jsIIriymFb3DXjUWdrk8tWCVx1BGQ7qTlph8TrF+LGBMNxxlW4uLprtCmgLFYNejSpbTe/gtqvnApmIQz8KiimJyDtzG7/yEW6vNeeOEF/vjHP7o7ytuUxhhOOukk5s6dy957743v+3iel7s+CILcYq6vry8pB9jtRZe3ST3P44ADDuCQQw5BRDjiiCOoq6vjrLPO6nZua2srmzZt2mHb/R1bf87L53RKyQUWI74NDQ2ccsopgJubk046iRNPPLHLi0NVSafTLFmyhHPOOYdEItHnceQ/h7vuuovly5d3I37JZJKvfvWrnH/++fi+Iydnn302s2bN4j//8z/JZDJd2mxqauLOO+/khBNOGBJ6wH6seBMlNw2dLVcsEJJZsICOB/6EiGVHWQfyF45REFESJ32A1CmnYSN/PfEsibH7UHf5VdScewFSWYH1I2OLBBR2IKhj1BDHjRqLjBmNN2smbb+7k+DJJ9AuYqwUXL8dapRAlfSf/kTnAw+iYRbXo+ASC/Z95vqLQp3Zn//8ZxobG7txEL7vc9FFFzFz5kwaGhqoq6ujqqoq56JQX19PQ0MDDQ0NA+IG09PY88WmWIE/derULtxpjFj3VEZX/04RYcSIERx//PFdjoPjsjdu3JjTwfaXyLS3t/Pggw92I2YA1dXVHHPMMblnZ4zB930+8pGPMG3atJx1PJ+oP/HEE2zdurVbW4OhG+z7qnd+L4AhVINklOxLr9By80+QzjbUeDtUmXW1UIX4+8+g9vzPQEUF0TYgRBFPsfXVpM47l7qrvoI3ciy+GjxrKKRCKkJoLQl17iqhQNVx7yN8+TUyv7kLK4r0kmMTG0ncnWlabrmFYNly/CBEbGzt1kFTCeYr7tPpNIsXL84RifxFVFNTkxMdC8W1/LYGA7FhI7YQd3R0dLmveNNUVFRQU1MzKGMcaigUzY0x1NXVdRPB8y3VxZ55bxG30drayrJly4qeU1NTw957753zJojHMHLkSGbOnNll7DG2bt2a81Ao7G93o+8EMBqrFRdSlt26ibbv/gDZtg3UIKFxFtgC5Iu+uRuubaDyC5fijRyJHydIEPDURzH4+BjxMMfNYdh35pI4aCahS7HQrX1PhIxxFtuK5DC8Qw+i7Uc/JNveBipoqDnxdYdQBWNQDEFrE63zfkTn1k2EmnXivhTmsdl9yF8omUyGFStWFBWbRo0axfDhw7sc60lc212EMB7Lpk2bWLNmDW+88QbPP/88t912W05szx/f2LFjmT59+m4Z21BH/nOMXx7Nzc3dOGZwRq1EItEvohITs6amJjZs2JAbQ/54Ysmi0OUmlUoxceLEov2n02nWr1/f53GVEv0ygjhxUpF0K+lbbyN4dTmhsYh4GKwjjoUXRQ/RCIRiMIkK6j/3OSqm7++clMUQmScwooBFxQAevhVk4mSqr74G84tf0vGnP0Gm03FiUbp714dFQ5DZ+5P+wz1kN23Ex0PJYk0S0Z2LVGoMYkMEQ0KF4OWXSP/kF1T+x+cwiQTWA08tyO4PGcknVtlslq1bt3YjYLGlNZ/ghWHI+vXr+cpXvsKmTZu6XON5HnPnzmXmzJkD9iaOuZP29nYuuugikskkqkomk8n5k8XnqSo1NTVccskl7LXXXgMynh2NM8aOXgyFutiB5mA2bNjA/Pnz8X2f1tZW1q1bx1133dXlHBGhsrKSww8/PKeT6y8RbG5upr29vahfYuwzWdiHiHRZf/mI10B+H4OlC+y7ESQqbmSxZO95kI5HFiBGnMEDJ44WuyXRSHpWRcRQddopJN7/AcTz8YgXP5jchHhREv3oI+CPGE7N5y7B228yrbf9DPN2I6omOq4krIfW1aCZTrJ/c8YYxy36mC6Usmc4VWLkxoHjVjMLF5CcOpnEmWdgNBGfNSiIiUQcKlWI/Jjf/Gs6Ojp4+umnee2117r87vs+27Zt6yJmlXpR5nMvMQEudEmJuYqZM2dy4YUX8uEPf7iba0ipxlaMMy4c666g1HNWyNWvWrWK888/v0tfhS8NgJEjR3LUUUflzouP97X/IAhyjuyFRH9HyA+13Fk/g4V+SXEiIdllL9H+859B0F1BWgxWIisqhsTsA6n81KcxSb+LMFuUdAogzkorIkiFT+qDp9HwtW9g9p0Mst3ZNpQA41eRffElSqXbV1VskKb5J7eQXfqis4IPshU/35BQuPmMMbS1tQHd/fBiWGtzn/7oinYV+Tq+fJE3/owfP54zzzyT0047jcrKyqLcRSnGUPi9UAfZ2/nYnXrU/DEWOnSDe+4f/OAHc+JnKeYqkUh08R6IsSNOOebsi70UYi51KKBP5EHVlZYM17xF+3fmEra1Ib1Ma+WpE3C9saOo+9z/wwyr77L4gaKMVRwX7NxtTCRmC8mDZ1Hzne9Qcfz7UA9QITQQNG+BzmxJ0997KtCZpfkHN2DffBMZRMfoeMElk8mcqJG/CK21bNy4kaampqIbodim3R1v4tjqO2HCBKZPn860adOoq6vLjclay9KlS7nqqqu47LLLaGlpKSp69af/YgRORMhms7lNW8id9jRfuzvQv9B6bozB87zc/4ceeiiXXHJJ7vz+EOd4Durq6qiuri66htra2nokcm+//XbR/isqKhg5cmSfx1VK9F0Ebmmi5ae3kV27GhHTa2JgDZBIUfnZS2DSxKIEqrgoIZGzdPS/tYCAEfyxo6i98nLMpEm0/+Z3JNraCCTAj8TpUkmqobh4ZF35Ou0/vY2aKy9Hq5yFcndvgnhTJpNJ9t13X1avXt3tvKamJp599llOPfXUnIVusBG//W+44QZmzZoFwN13381VV13VxZKdyWT4zW9+w9lnn8373//+komXqprzjSskgnHEQnwsPj8IgqIuIKrKsGHD+j2m3qK+vp6DDjooN0+x6qKuro5x48YxY8YMPvCBD3QxGvVnzuL5qaurY9SoUbS2tnYTgbds2cKmTZsYPXp0lzXW0dHBG2+8UXTN1dTUMGHChD6Pq5ToFQFUFEIhNBYPQTWk+Z4/IAv/ihgPIzi/u0K3FBznpuJ8/ayAwZD813NIHXcsGA8bK/diCMUJlgCquUSlRgxWneezj4+tqqH6E58gccB0mr85F795C6oeGTFUkCVUwYiH2hDpo1wcG2888ehc9BjelAmkPv5JjOe7WVLnYL27yo7EbiKHHXYYjz76aO63mDgGQcDNN9/M0UcfzbBhw7ro4Arva3chJmRjxoxh8uTJqCqnnHIKN954I+vWresSv9zW1sZjjz2WI4D599fXjW2tzfk9FvoXptNp3nzzTcaNG9eFaw6CgGw2W1QHNnr06AF7+RU+lwMOOICFCxd24+jzXV9KqS6I11FNTQ0zZsxg5cqVXY4bY2hubmbJkiWcdNJJufFaa2lqamL58uVF9az7778/Y8eOHVTdX4zebVUVEItRIMjS+dRisr/8A+2+xeIchnus7qaKxSUgFRWS734PNR/7COCDxZlMhC6Er6dwM4kMK/FRE+kEkeiY55M6/DAafnAD3sFHAJYKQggTGDGoWkRMn8ViEXEitloCCem4/Xdkn1nsIk4QVALU7D53kpgDOOmkk6irqyu6QR9++GHOP/98HnroIVauXJlzQC3mDrM7CWG+g+zYsWOZPHlyt3NEhCeffDJHfHZFL7cjTJkypUsf4O69qamJW265JTdHsX50xYoVvPXWW93GVl1dzaRJk3LXDzRiwp8/d7EONf+3biqlfqKqqooTTzwxF+cLXS36N910E1u3bu3i23nvvffy0ksvdVMnJBIJzjjjjJKOrz/otQisolgV7No1tNzwA7S9FQ+nl3OOwcVT4iCKRHU4/H0nUvXvn0FSNVjjuEEXttZ/5BYyCfyJE6j92ldpn38H6Xv+iEUxanPW5OiKXe9DXSr/wBg8VbJBO803zmP42LHYyVMdIXa+4bsNIsK73/1ujjvuOO65554ckTDGYK0lCALuu+8+Hn30UUaOHEkikWDNmjVFucHBWJAiwrBhw5g1axaPP/54l99VlZUrV7J69WqmTp1akv6MMRxwwAFMmTKF5cuXd3th3Hnnnbz66qvMmjWLyspK1q1bx9///vcuVuv4mgkTJuREud2lPy324hoIC3l+28YYzjzzTObNm5fzOc3v75FHHuGCCy7glFNOoaamhv/7v//jzjvv7BKJEq/Jgw46iJNPPrlk4+svek8A1aJNzbTd+GPshvUYY8gK+FE1tWKT7tKdCp4oQSJFzec/j5k82RUmUhCJ9HilQpRUQcXHGzaMmosuJjlpEq23/BSatxGaEC+0kRW6L+277NWeCiqWRAi6bj1tP/5fqr72Ffy6WvqYt7VfqKio4KqrruKFF15g1apVAN2iAZqbm2lubu62iUvFVfUHMRGfN29e7rd4nI2Njbz44otMnTq1ZJu7pqaGCy64gK985Ss5nV/cZzab5ZlnnuHZZ58taizJ58I+8IEPMG7cuH6Pp7/oj6tLb7H33nvzn//5n3zpS1/KGabiuclkMtx3333cd999uXHkv2Dj+UqlUlx++eWMHz++y7HBRK8ogZUQyVo6599J+rnFYAyhVRI2KjMpXXmrwi7US1B/wXl4hxwcGTFcGFtI0LuojF5CNPq4DKqYZAUVp5xO3dxrSew3GULFikdfY9hUxXF4YjEWssYVfWr/27Okf/3rOJXCoOCQQw5h3rx5TJkyJZd5N19szLdk9uTOMFiEUEQ45JBDSKVS3TZER0cHTz75ZMnigeM5Oe+88/jgBz+Yi2HtyS0m30Uo3/p66KGH8qUvfalLtMVgbeaeOMNStg9wzjnncOWVV1JTU9PFx7RwXRVzq6qpqeHb3/42p512WslF9P6gGwFU4s1iseo4P0JL24MP0X7v3QgeRhVjJEpxH5GzeMHgu/x6CGGUMCH1vuNIfPhMPN84VxYjiAEPv7STIIIYg8FgjIcYwTNC8oADqb32m6Te/wH34CTEivNrEgK0twRRQI1zjLZRxutQIJG1dPzhbtILHiUMslgNHMds4+wxA1djOH9Tvu997+Pee+/lwgsvZNKkSfi+n3v7xu4S8d/YgLLXXntx+OGH87nPfY5JkyaVfCPl9x9/8tuPN8rYsWOZMWNG7pz8TbJ48WKy2Wyuzf4SahEXpXD99dfzH//xH0ycOJGKioqi+rX8j+d5jB8/ngsuuIA777yTffbZp+Tzlc/NxWMpdLUpfKENFPL7NMZQUVHBF7/4RebPn8+xxx7L8OHDu6yneJzxOhMRamtrec973sPtt9/OxRdfnMtjOFQgWjCLCmBtlKPPIGGGYOkytn7l62jLNgxKQJwePv+6qGaGtVEuFh8RxZsxhWHXfhNv9BgC4+Eh7M4MUi5tl4CG2I4MnXf9no7585HWNjqNh6/ujvq8gDXEiqtr5zXUU/ed75CYNh2iPIeuVIBXEj1nT8gXR8BZLV977TWee+45nnvuOVavXs3WrVvJZrMMGzaMcePGMXHixJwubOLEiTl3jlJmhVm7di0PPPBAt43q+z6nn346Y8aMyR0Lw5AFCxYUdedJpVKcffbZuSI7sT6pL8jnVmIOL56rpUuXsmrVKhobG2lra0PE1d0YPnw448eP513veleuXkl+4s/+olDEfvrpp/n73//ejeiNGTMmZ0CI+97dXFQ8by0tLSxZsoSnnnqKF154gTfeeIPW1lastVRUVDB27FhmzpzJscceyxFHHMHIkSNLaqEuFYoTQA1BBA1D7IbNNF1xGeHadYhmyGoFCbKRO4oUXgg4rsioQnUVDf9zHfKud5EwglWXrGB3yolqIxFeBTRA8EgvXkzL939AduObCH6UDrWvBDAqAiUBoSapOHgm9dd8A+ob8IyByPLMAD/sntLOAzmDSKE+q5j7RKk5mUL9VKHepzd6oHxdXE/K/10dV/73fKNRIXHJ57Tyv5faAbqv3NxgEMBi/+fPTeGziucrX40wFPR/UEQEFtQlFlBFM1nafv5zsmvWgAZY6+OLi//t9rgk8paJfOE8L0XdJReTmDUTX8S5ikjPmsKBghWNUt0ranxCsSSOOophc/+HmsOOcmPrB3/mEj442dgQYv/+D1pv+zkEAWiAiisQP5CIF1N+XGg+UctfbIXH8jdyqa2H+f0V/l5Mcd8TEdiZ5bO/4yx0JYl/74l4D2QJ0YG4x4FCsTVVKLYXzi8Mnq65GLo/SXUbNrRK5q+Pkl64AE8Fq+IKhmsI+EWdnp3lQTGipE45Cf/kfwHPOFFQYh++3XvzHjjRXKIIZPHwjOBPnETV179OxVkfxVTkl2bXyDCjufva0Yg9jUV+Q2gsAR7ZB/5MdsFDhKGCNbuN4S3UrxX+v7PfSrkwd9ZWMSLSE5EeKPREnAt/2x3j6Y0haigSxJ3NX/x9qBL27iKwgmIJGjfS9PkvoeveJC8GLe+8Iqb3UFEvxJ81m4ZvXgPDRuZSxg+lmw6tYgkxatAgTWbhIpp/+COkrcVltrYGl+xV3H3vErtuUWuQ+mHU3/A/VEzdHysS2b0ll8NwKM3HQGCgXTOKEYqhOKc742rjc0qtethRf2VsRxEC6ETfbTfPI3PX713MrXjduJhi/mRGQBuGUfft7yAHTKFCE7vVKbi3sKoI1pl0rQIBmeUv0/6DG8kuX0HWhI7HVXLC/q4sICuWRCjIEUdQd/XX8etqEfG66UxKhaEkUuSjN/fYX93XrhKP3orexUT1wv8L+x1sp/JCFPNjHEoYCnPUhQCqY//IvrCMbZd/maCj0xkPJNyhh5uIYFXRVIKGy/4/ku97L9b38dT0OgX97oRzTYl8lmIOzyrZTRvI/PRW2h95FC8TuJT8MdfWW07QmdGx+OBb6i75f1R+5MMgZsiKAWXsGnaH43EZuwfdIkFsJk37Xb8nbG/FaBIhdERiB1BV8HxqP3Ee5n3HgOf8/QazcNAOoTgrtlhQxagTdROj98L/8uV4k6bQ+rPbsdk0xipGDKENe6f8FrChgK94AbT/9jdUHHUU3j77DIjlK3blGMrYkZW5rw7OxYwUvXk+hdZJoMf5K6b3642uLn98xazhA+kOsrt8BEuB3Z1KrBh8xw05SVUVgmXLyD79DJ5JEhBibFSzt5vRw3FHNnKKrj76vVR99MPge4ia3PEhCeNimLFRuc6cBTvEpFJUnn0W3n6T2fbDG5E1a13Z9qjEp6rjGr14gRU+QFU8AzYMXMTMhs20/OkB6s+/AHw/0qf2b/j5oo2q8vbbb5NOp6PhDD2RxxjD6NGjcynaYTtB2LJlS9FUU71BvlvF6NGji9bA6MntJZ6nTCZDJpOhs7OTzs5OOjo66OzsJJ1O545ls9lcSqw4X2BHR0fRTNzgcjRWVlaSSqVyf2tra6mvr6e+vr7HSIqdEdveEoutW7fm/BiHirtJIVKpFCNGjMh9H6wxig2sqnEbWwOl5dvXkn744W4PqNvDUcWKQVTxxu9D/be/iT9x8pCc7F2FtRbUEq5aTevNt5B9+umcTdgqJBCyWNT4eAVRHkV91uobaLjp+3gTxmPwsaLbM9n0Afl9dHR0cPnll7NgwYKivn6DDREXDXD33Xczfvz4Lps6k8nwb//2byxdurRfusDq6mruuOOOovVM8ttNp9PccsstLF++nK1bt9Lc3Exrayutra1kMhmCIMilvoprJsd/wzDM/QVy34shLg3peR6+75NIJEgmk6RSKaqrq9lrr72YNGkS06dP58ADD2TfffdlzJgxVFdX5+4pf+y74nZjreXKK6/k3nvvHbKcoIhwwgkn8L3vfa/HmiK7C76K03+Bkl39OsHTT/dqwsPIQio2pOpTn0TH7zvwo91NcA/Dw0yaRPXXrqLzV7+j/fe/I8xkQJQA8BTQbFSwqbgrQI4Ibt1M+58fpO6C81A/KvrZT04wX9TZsGEDr7/+em5DDqVFb4xh2LBhRYmFqrJu3TpWrlxJGIY5Z+TeIH/D1NXVdUlqkN9+/v+dnZ386le/YsmSJbnfCvuLOcpic1jM8bcYwjAkm80WTUKrql1KTCaTSfbZZx9mzJjB8ccfz6mnnsq+++5LIpHo4je3KwRi06ZNvPrqq10cvIcC8vfGrFmzhsQ6NSIGiyGwIcFfFhC2tQI7t2h5AkJI6pijqDj6WBJDQJ4vFUQEdU6D+LUNVF14PnWXfZlEQz2+KmIDQnx0BwaeLhY4L0F24aOEb29zyWEJc4ldS4F8YjgUFlU+ejO2mHPdlY1azDpbDMV0j/m1UIpxjPkvkvzPzizHxe6r2P3HfVhr6ezs5PXXX+f+++/niiuuYM6cOVx66aX87W9/66Ia6O2zLdS3DhXiF2OorVEDihELTc2kFy3AYLo87J4GbFGkpoHUJ85Dq5JD1+DRR4gSWX0tGEPyxBOo/9a1yAEHoF4CNQHWOjfrotfnc4IWZP1bZJf8zdldSuAbVLjQh6KFuZhOqyeXk75sip1tpp7moZAz3Nk5Ozu3PygkjFu3buW2227j9NNP5+tf/zpvvvlmUQLcm/aGEgqNVkNljZpYtxUu+TvBuvU595AuilcxoFGGmChSQlRJHDsHb/r+eM6EMlj3MCAQBCOuep0Rg+clkZmzqP/Wt6g68UTES+DhEi0oIW66XJhgjNzCFiUjIdlFi9BsxhHX/oytgGAULvqhsgF6M47+btjeckX5f3t73UBhR5xwrGfcvHkzN9xwA+eeey5Lly4tyk3u7B6GyjqA7i+docKZGlVFswHpxx93Bb/pTqHjdE6u6q/LA01tHdWnnoYnGiUBHYIez/2B5IVBRfpOX8AfPozKyy+j7rMXIhW1zo0GD6NCgDiXmriJ2D0D8MWj/ZUVBOvWoVbfae+LnaInN5hStftOg6pLzvr444/z0Y9+tAsRLKN0MEahc9Nm7LIXUFPcMpnP31mbRTQgeeSRmMlTo9q4/cimskfBQ42HJBJUnnEm1Vd8Ec9EleIkjDxqus+DMzEp3rYm7NIXIt1heSGXsWPEnP7rr7/OpZdeytq1a8sEsMQwqEVfe5VsUyPYsHiyAnUcnqLgK6HxqHj/ifgVHoJB9B3G/fWESPQ3xoOET3LO0Zj9pmAJsUbxbYgtIt+6JLOCBlnSzzyDqKVMAMvoLUSE5557jh/84AeEYThkffv2RJjQQPj83yFj3SbV7p75Ktt5PLU+qQlTSc7cn9AziNgch/NOh4qLIBFVUA+vqpLUiSfiiwHrR/NU9Ep8K1jjkVmxAm1rB4aWv14ZQw8xoYv1gvPnz+eFF17IHSuj/zCayRCseBn1XJU2pKfceIqIh8HivfdwTE0dHuJS5BvB/BO8kYw4g4gYF+OsCMlDD4GaaoyxQNIlWSiEmqhcpsDbjQSvvbq7h75TDDRH8U7asLFuuFjK/Pw8eKVAPG/WWhobG/n1r39NNpsdcC5woNqO563Qa2Gw1ofPpi1k39qIEZfVznE4XU+Kql+444kEFUcdiUv9QvdQsHc4JO+vAv6E8cjESeg/lkVZAb1unjEGCMBVybOGzCsrSM4+ZEjMXX5IXakXYs6INITcHkoB3/eZPXs2I0eOpKqqiurq6pzTc1tbG1u2bGHDhg1s2bIlVy8XSvMSuOeee7jiiisYNWrUbiOCpVoXhfHRQwG+Xf8WtLWDJa5/3m0DO97FCbr+qNEkph3gChu9gxZ1XyAiaEUliYPeRXbJUsKkuvjiboJwXDg+cC4ALy1Hs1kkr9D0YKDQNys/VreU7Xuet0u+bEMV8diHDRvGvHnzOPjgg4tGTWWzWRobG1mxYgUPPfQQt99+Oxs2bMgd7ysBsNayatUqXn75ZYYPH56raFdqxC+s2tpaUqlUSduN7726urqLr/GghcKFa9Zgsx0grkx5sZKRGtW1ELX4U6chqWQXa+eevrD7ChEBtaRmzSJdkcCz2YgLlMITUQ3wxScgwK59E02nB50A5nN9qsrFF1/M9OnTc8f7+0zj9n3fZ8SIET0G++8piO8nFuMKEevrEokEo0ePZvTo0cyZM4ePfvSjfOYzn2HJkiX94n7i+VqyZAlz5szpczs760NVSaVSXHfddXzkIx8ZkH4SiUQuDngw4Qer17pY4Dg2tciaNLi8oSKG5Mz9cQXIt5PAPW0h9xf5BN8C3l77IDW1SHOzs/AW4aANgrXOp7yzsRFNp6GuzhmPVJD+Bgf3Efkc4KmnnsqJJ56YO9af51oYTfROWiP591MYMRWLwjHXC3DggQdy7bXXcs4559DS0tIvIhiGIStXruz/TfSA/Hurqqqivr6+aExzfzBU9H8AJly3FhAwO6jW4Q6Dn8RMmOhcXwbZ728wJy3/ARoBU1ePN3I4aihqDBIFFYOKRfChrRXb0gQa5oVB7bbhF0X+wi+sy9uXT2FRnHw9YH7o156MQgJfSBTzfzvqqKM48sgjSxL18tZbb+XScg0ECvMAFDP49OdTqBcezJejCTZucsr5uAZGkTkVBCvgpSpJjBgORbic3YmhtHFUQWuq8MeMwVotpkGA2IVInOlE0xmCt7cC8SIYOvdTRulhjKGuro4jjzyyJHq75ubmHnMRlrFrMLz9Nkjk5NwDUVMiA0llFdTV7xZ2pVjc41AMphYESSYxo0bjhT3EN4qgNta3gbEWGpuwGqseBkf8LWP3Yt999+13FuRCq30Z/YMftreBGMSGSOzoW3CSRplRTHUVWlU1oN4buxLgXWwBFM020ht63dfaJQKiijQ0gLVYT7obQeJTYw5RLGzb5iJEcIaU8lJ+56OysrIkOq+YASgTwf7DF1yVtO2btjs3IuLSxmh1JSaZRDF9ZlpUXUU2VQ9rNE5HA1jCKJmAiBK2tWEbt0JbG6ohpqISranGDKvBq0gBBmtjv53IJGMNmk1j1TqnZatO9xb3IXkMV/6Yov6lIoU1goe6e+zF/blU+uDV1kWZYIqJOBr1HZURAGx7W574m/+3jHcSCl/YpVDfVFZWDpgLDOyYsegrwe0pDdpgw4eocmWskCx2lrojmvAxnueu6MfYAzUYyWLUR9WiatBslnDFK7Qtfhy7ZCnhhk2Eba1IGERUBrxEEqpq0FGj8PbdG2/6NKpPPAnq60Et4eb1NP3Xf5Fo3EZnFJFhxMXvdp3+IvkNNaDh6m/g7/8u1COy5u48xtkZOARJpVwB+GKpwaLx53dtg9BljjEgarDYQTcsxSg0UgyFhbqnInaNAWhtbS1Jm2PGjCGRSJSkrd2Noca59srzVZ2U5mRhkS77uS8whCjOYVhVCVe/SvsdvyL79LPYlpYos4pBRbGIS9IKhNk2TEcrsmUjwcvL8J54goqDD3N1d1UhCNDNjXRu2eLESgVbYNfJL07eZSI6LRlr8cGRIultSWN1yWETPtYqfi8NGrHHkbXaGzq7W1GYN6+vXMtQWeSDhcIXyIYNG/qdB88Yw3777YfneQM+v8Ws9f3lYPPr1gyF9dFr1/8cQYgyovRVEagAaiI+Scn+33O0/s9c7Ka3CY3FRwjiwkFW8QSsCRHrOE+LOq5ODFJZCRVJjERcpDhB3opL3urC+3C1jfNGIAXJuxTI+lCrYUQed+AUWTgvuNBBtRZP4mt6kQiUiDGUuJXBR7EFPxQW6Z6KnK+otXR0dPCPf/yj3wREVTn44INLMbwd9gFu3NlstmRrII6VHkroHQHUiBAFIWptXsqsXZ8YAZcPz0LnS0tp+e/vYBu3oibAU4+MBwnrCJoY370poqJN4HSGgmKNQlUNXkUFoRpHEKOIW8ERPbECXpSpJt7cxGHMBUTRWmwoUZGj3mc3VJx+NOxIR997d614HlYVMSZ+Kwx6bHAxbq+vHEtP0RL/DCh8kVhree2113juuef6xQGKCBMmTOCggw4qyTh3hGw2yy233MIDDzxQkvaqqqqYO3cuI0eOHFIvVd/ZEWLVvER6rAIjCBCIkEi3o51pSCX7nAFaEaxapCNNx89ugy1bELFgPZdcwAqhCfFSlWhtHb7vY32zPdOyVdRajCr+pElQXcn2NK7GpaePjQ0mEqAV7KjhJKftD2IRdQaOeK/HhlkaakBjEta7herIsqKtzahIJKwXPODIYrQ9RT5IdWUuyYSohw4SschXzD/44IOsXbu2aLGgnSFf3DPGcNZZZ1FXV5eLIhhKi75UKHRHib9ba8lkMrS3t9PU1MQrr7zC97//fd58881+9ed5HmeccQb19fUDPp9BELB48eKS9TNs2DD+67/+Cxj86I98+KaiAulIYyNrZrH9qwi+GmxLG7YjjV9bW9zY2QsYZwOlc8kSsv9Y5tJEuTz8qISgkNx7KtVfuggzbjx+qgoSSYidRkKLtUEUg6aYympEnFU51tpJzKMKGHXicNV+06n9n2+7kD7jddUEarSB1YBRPBUUr1cMmXNwhrCpGTEeRRWkOebO6RY9BL+2ju1m6cGzAudv4B/+8Icl0felUimOPfZY6urqSjLGoYD8Tdva2sr111/PhAkTupSujI+1trbS2NhIY2Mj69ev58033yxatnNX+o2Ly5933nm7zQBSyswthe0MlRei7zc0YDvewipRfr8iJnATGUE6OrDN22DU6D5vV0fElMwjC9BMGqOC5nR1zkm44qTjSc4+lMDziJNNi2531DG4Qu6xxVXUi8bTnWsLxcOIy9Rs8VCJXW9kO4ETRcSFpYWRGs9X19POIOpqquimjTgOuud52R5BbZDhDVH/ztgz2CiFb1q+eDdU3vClQr5Ym06nmT9//g7Pg67+en2dj/yEEhdddBEzZ87sc73gMrrD6JixhLhknz3p7iUiDDbdDo1bInGt7wvcZjJ0Ll8GGGwuHtC5kFgRmH0g+AkS4iHWy+XYE4nOE+ehIsY6n0GRKJyv2NhxkS6iQICRAI0KOcUMr+NJfax4eBj8XVDTKIq0tqMbNkacZZFz4rd4/D2ZQEaMcOOLROPBooGliseMuZShZOEbCOQTtGKf/DjXWBzub38iwkc+8hEuvvjibvHHZfQPfnLc3gTPPx/76haNZTXWGR0kmyW7Zh3Jo2zfdYAqaONm7JYtiApqQrbnkhfCykpSI8Zut6U6J0WnsMt7ixogjLyaBQsSYouMyUQ+gNqWJrvidTwiblLUucioIpUp/H0nOP2igJWeozm63AuOeGprK8GWzQgQokWvjY0lVsCrrsLU1eW4xR40D7sNpeDWCrmcdxoHGCM/hVhPx3vzW28gItTU1HDWWWdxzTXXdEkpViZ+pYHvTZqMJ0oAaOSQW7iBQzF4GhCoIbv8ReAjxXVdvYIls7UJrzMk8BQTCmoUo46H8yoqMbVVLu18dIWq0vHUU+hLLzsqIpGOTz2kKknFqafj1VYW5aI0UsBlXlhK54WfjQigI+gup4PFP/QQRnz/B7vu362Oaw03rkebWzGWqNJy11aMuozQRFMmw0dgUlXuLJHolgZnQQ8EodrT8/7tDANF3AtD3A466CC+8IUvcM4555BIJIZUDPw7Bb4/cR9CP4mEAUZ1O0uSB4MSiIevELz8CtrZiVY4ca8vD8S0uXA1wTkXGjWE4giv8X2s8brxcvbpp2j5wx8xnhDHnxkEGTaCxPHH4FVXIEUooIgSiBM1PduJVcEakDC63kbJCfoIUehctowwm8V4SjG9oXO9ceF1FkjsOx5SFXljLC/qf3YkEgkaGhoYN24cM2fO5EMf+hDHHHMMw4cPz2XqLq+T0sOXvcbhV9cTbNuMGlMQFxxD8dSAKNktG+lcvoLk7IP65L6rsYgY1RSJpNicscNms0jYvTKdC5dwUSHOpcQSGsUYAfUALyo3WdCfKrFmLoxzm0UCvI377WMohqoiYZbsP5YinmA1jPSVXQlxLtOOdXHGyanTYA8NZSqj9Kirq+PKK6/kXe96F9OmTWPixIm53Hmw3cBUJoClh++NGg3jxsDWzUV9AMHt51BCxHiYdJbgmadIvWsWeLteQ0Iw2JoUsRZRFdevjdj7dAc0N0NDw/ZrRHJExNrQGTUMiCTQyHqtSJRvrytUPJQQk6xAK2vwCV1yBUA9AzbEr6zodl1vYde9SbhylYs6UUOBg01u/GEU1YIxmOn7Q7mmShkRWlpa+N73vsfYsWM58MADOfPMMzn99NNJJpODvkZK5cw+2PfRE3xNVZCYNoXgxWVAzme3C0ITOexaC8aj/enFVJ3zcWTYsEgPFvmO9OoeFTO8DlORgs50LqrEqEu6Km1p0mtXUTVhgnMsVptLG2WsgCeIOE5VCPDFYsXiG/d7IUQVDFQceBDV3/w6nhXUGBdNAi4CpLfFgHJO0s7UAUr67y9gt20DFVQ8rIbdXiJWLZ4YVC1efQOJKfv1rr89EO90K/BAQFVpampi69atLF++nPvuu4/DDjuMa6+9ljlz5gxa/GwqlcL3/ZL0WVVV1cWCDaXxPMg3SBXGsBebs8LvvkFJHnIInffdCyGEokXtuwYvIgAWu3oNmZdfpuLIo0DFWYihlyKxQUaMxh81kszatSASqR0jb2HjkX3kMcJDjsCkKgixGPFQEXyFjLg0CpEtBItxInVPfiSCG3fSw6upRwgR8V14X25EvYNqbKx2VmntzNC+YAHYLIofCedF5kDEmX8lxEyZhtcwLBL/B59QFPbfH4tlvitITwtwT0bh/fQ0Vzs7Xgz57jIdHR08/vjjfOpTn+Kb3/wm55xzTpd2BxoiQkVFBV/96lc5+eSTc7/151l6nseYMWNKMr78eS0sOdqbZ5S/Tn3U4E2ZijTUE7zd4gwCBfcoRDkDPYNYxc9a0g89TOqQQyFRgRUldkXeGUQEk6ggOfsgsmvfQjUExImOoWJ9Ifvo46RnzST1L6chCQ/rK55aOiNC63IK7iK2ez3nrK/5E9K7NsDVD3W+fdlXXoYX/gHGBw0RTWAJu+lQjUbuRZ5H4qjDQM2QKCta+Gb89Kc/zX779Y87VXUFgUZEfo7vJOzMBaaU/QCsWbOGyy67jDFjxnD88cfv1vUiIuy7777Mnj27ZGJwKV+GcVtBENDW1saKFStYuXIlLS0teJ7HuHHjOOCAAxg9ejTJZLJLSGa+U7sPijdqNP6sA8k89iR+5KuWD1FHNqy61E+eL2SfeZbOla+SnH4AngpRKEkvBg6YgMQJJ2IWLIR0mhByKePFKkG2k44f/JDOxX8lceh7SI4ZhV33lrPexqL2rq7BmH0ryqD17qFozmMvxHYqnffdR6CKaiKyQNseE0uLAamsI3nQ7Ih9HRqJAvI388c+9rEuVeH62s47PRFCRUUFxx57LDU1NUDXjW2MIZlM0tjYyJNPPklbW1u/okA2bdrEZZddxv3338+4ceOAgeMEC4l7fgGjUiRE3ZEo2ts24r9BELB8+XLmz5/PPffcw6pVq7qdX1tby3vf+14+/vGP8y//8i/U1NR0F4ExBkklSL73eDoXLor0Yd1ZwDhawRdx1tPmJrL3/YmKqTMiS2rxKIhCiIDg482ahT/n3XQuWIQYsFFwiRjBWIuqkHlqCbr477R5EhFhp9NTcUkPeit0xx33d9047tO9SeyKZWSeWAxiELJ4FrIG/KL+kYLaLIkZ00hMmID0I6N2KVEophZWb9vVtgox2BzuQKG2tpZrr72W/fffP1f+Mp9IeJ5HOp3mjjvu4IorriCdTve5L1XlxRdf5K677uLSSy8t1S302FchkYpRas6tP4R08+bN/OQnP+Hmm29m48aNud8rKyuprKzMcYVNTU3cf//9LFy4kGOOOYavfe1rHHbYYV2yafuOJbMkDzkEf8wYgsZGLEoSQ1Ztl+wmEkVkGHExwx2P/pXEaaeRmDGNXK3gKPIi1u0Vg8Vgkh61nz4XVq8jeG2Fi+KNVHkuGsVRvMCAsaBYVIzzVbQWNc7o4PyOXdEhG/FoVpyDs2iU3UY9QrUR+xmNqg/z7/LlWDQIaP/j/QRtzRhcH4jkgla6PbTIyp06+j1oRSpmqfvkRlRqFKZuKnTG3RWUglPYEyAiucLe+a4q+bqlZDLJueeey7333svChQuBXReh4/PCMOSPf/wjn/jEJ2hoaBhQ/erOIlz6219h8ohdGZe1lrfeeosvf/nL3HvvvYRhSEVFBbNnz+aUU05h9uzZ1NfXEwQBq1ev5pFHHmHhwoVs2rSJv/zlL6xYsYIbbriBk08+OUcEo6dn8OtrqTzhfRgLPkJGQzCCFeNSvkscxhD71EHY3Ez657cRdmYgyrmXMxL0IKNawNgQFcWbMIWaa76CmT0Lz8ZHo5SkEiU+tSGiIb4NMaqEeIifAAE/bEVtSKBRnkArJMUggWI8cQkQhKhtR6CDmLj2ASbS/bFkCcFjTzj/QhFc9uqegwMNBjNqBObwIxDihBNDk0D0NxvMP1O0QrE6t/nfa2trOeOMM6io6LubVYwVK1bwxhtvDFqIYX+fa+H1vVY76fY465aWFj772c9y9913EwQBkyZN4tZbb+Xhhx/mqquu4pRTTmHOnDkcc8wxfPKTn+T2229n4cKFfPzjHyeRSLBq1So+9alP8de//jU3j8YxdYL6Ht4H3o/U1pE1ShKQ0LqKZ0Xm3FhXvCj91JOkFy1Cwkg/FlldRYu/6YyANb7LPKNKYu+JDPvWXFKXX4a//wEu16B6EBg0FNQ6A0yojsvzPItWV1Cx3xSSH/o3aq+4guTwkREXBlkxiJ8kxOBJAoNP6ENCDYrF6+f60ZY2mu64naCzpddpU0Ut3nveQyKyghVP21DGno7CTW2M4bjjjqOqqir3264QsHxL5ubNm1m6dGmf2tmTEc9BZ2cnc+fO5eGHHwbg+OOP5/777+ess84ilUp1m/uYQ546dSrz5s3j6quvpqqqim3btnHZZZfx6quvOiOIIeJq8EhOnkLysEMJHn2ULIp4gmoPglokq4bGI/vzOwhmTCcxcWIU5+F83opaBDSq94FBjCEUhbpaqs84hapj5pB98y2yK14iXLcOGrch2QCtSCD1w/DGjMWfMB4ZOxZ/5EiktsbFDIvz6WP4cGqv+A+8TODcZ1AsPqGGJIYPJ85IUKz0544QLzabzdD2m98SLluOUY+YY93p9RWVpE4/A5IS6f7skOYCy+gbihGlqVOnMn78eJqamvok9gE5a+eTTz7JJz7xidxv/yxEUFV57rnnuPXWWwmCgNmzZ3PjjTcyefJkHnnkERYsWEBzc3PufBFh9OjRHH744bz73e9mxIgRXHLJJbS0tHDdddfxwgsv8OMf/5jrrrsO35WidHoq9XySHzqVzqefcZmfNYwd37rtVRV1ZMwmyK57g9af3U7dVVcice3TeDAFN2NzJY4sVreXwQQfGkbiDR9BxayZuCDh3AyAdcWKMOKi6NRxfBoH1yl4FUnk8CPx1EVdiI11JJH9VpQ462FfHkLm70tJ3/lLUIsV47jjHtrK19Mkj5tDaspUlxZLADXb86CW8Y5BIVEScXn8Dj/8cJYtW9bnduM2n3nmGTo6OqisrOz3WPcUqCphGPKzn/2MxsZGqqur+cY3vsG0adMAeOSRR7jhhhu6iMrgDFGe53HSSSfxi1/8gtraWr74xS/y6KOPsnjxYu666y7+/d//HWOQnE+aEUNy1kEk3n2ky7yc29zdY2wdF+eyOIt4ZJ98ivbf3QUW5xpCFIVRACMGES/Sl8R9u5oexggJMS6vnon1KlG9D8/geQYv1rfkxuy548aN34uOeRLrZ1x/xnh44mF2UfcQqkWtEqxZQ+u8H6OdWYgjkou0FTGZjrMVkNo6qs84E/ENXnzfYv5p9GT/bMjXB8bfDz/88C7f+9ruW2+9xfLly7ts9IFEvoGsFP1Za7sQqt6kDlNVVq9ezVNPPZVTKRx99NHd3KxUlWnTpjF37lwuvPBCEokEQRCwcOFCHnjgAUSEhoYGPv/5z5NKpdi0aROLFi3aTg+E6MGlKqg88yy0JuVSD6jB9ujTtd25mDBL5x2/JL3wIchmMLaw9lrXK9iRECgSsUfS9Xv+TzljwnYb9XbLc/51+Z3syuJzCRfEKmFbK20/+hF25WsFC7hYey55rK8eBCGVxx2HN2NG3iiLucmU8U6FqjJz5kwaGhr6JbaqKs3NzTz//PMDamjKJ+AiLglD/AnDsF8fcBbtuL34vgr7z79ngNdee40NGzbgeR6nnHJKzv+y8Lx9992Xiy++mLlz5zJ58mTAFXdat24d4HSyJ554ImPGjCEMQxYtWtS9KpxRSB4wnYqTT6bzj3c7/WCRLKndwk1UyGQzcP2NmOoa/HfPic70il4zlOHuxxmANN1B0w9/SPjM/+Xo1s7uw6hFjY8ZMYLKs85C+pA0oow9HzHBmzRpEmPHjmXz5s39IoJBEPDYY49x7rnnkkqlSjxah3hth2HIE088QSaTyR3rj+tNTPDiaydOnMh73/veLj55xSAirFy5ktbWVpLJJIccckjRc1SVtrY2Vq1axdatW2lsbARcmrHYgVxVGTZsGDNmzGDt2rW89NJL3Qmgivz/7Z1tkFRVesd/z7ndPT3TMAgDMgoLGGA01shEIiC4ZmMirhuiVJHarfIlW9koRNRN1P2QFauMZdUqVVobiaUfYmksy6pJWYmaZVkps4uWvI2h3MRI1gFcmNUpHZFheJnXvvecJx/OvT09Mz0ybwhI/6sGumf6nnvO7Xv+9znneZ7/g82kqL7tdtqb9sCnrRBLYX1ZJ1ElcBGu+yQnfroJ8+M06auWet2/QcuCcwNK2NtLz7P/TPTmFlLOYJMaJKcYR2TARBGV37uFYO43zsGxlzFRMMYwY8YMLr30Uvbu3TuupaRzjqamJjo7O8lms6fNoFBV8vk8zz//PC+88MKEty8i3HrrrVx99dWnJEDnHO3t7YUUy2nTppVc/qoqTU1NLF++HFWlr6+PKVOmcMstt7B69WpUtXDczJkzcc5x5MiRoaFrEjtF3IUzmHTHX2Mrq1BjfahMIdbPFyZPTg74erqSBhXckc85sfEJenfsRq31x6gvZKRK8g9J7OBXjeTsyaui3vh+ouiJLjqfeYb8zzcjagiReCUuQ9oqxEDGTpFAhYrFi8muuuGMlbss4+yBMWbAPuBYISIcOnSI/fv3n/bVlKoShiH5fJ4wDAuvi9+P9qf4WFtK83OYMafT6UIubxRFwz5EKisrmTNnTiHlTVVZsGABuVxuwLXK5/OFYPWSs9MQYDBk/mgFFdf9sQ8yjlPAkrS4ZOoXNne9SwQEAgz28GFO/OQn9L3xBhrmcbEOn1MIAauKc2cqnsmisXPDahxC4xR1FhtZ7BeH6XjySbq2vIGLIkTiiJ5SXdV+GlXjUIVgyjSq1q/DVE+NlWrK1t/5ioSolixZMm5pqWRS7969e9zFlsZy3sHvR/tTqq1TQUSora3FGINzjtbW1iFtJiusxYsXs3nzZh544AHS6TQnT55k06ZNtLe3D9iu+/jjjwGYNWvWUAKMA1R8THOmisl3/IBg3nxcEGLExPLuAaXYoBAvF+uXSe9Jep58gpP/8iJysjfW9IswxJJW+IpuXzVUk+ieWEpLfdlMVcV9tI/jDz5EtG0bWesIUJ/7PEzurhABaf8A0BQmZcj+5a2kL6v3+6fiHw5lnJ9IJucll1zC7Nmzx51N4Zxj586dA/bmvq5I9vbmz5/PlClTsNbyzjvvDCC+YiKsrKyktraWlStXFsoIHD16lD179hQ80C0tLezbtw/AK92UPrMClkgMpmY6k/727why1T7izrrY1ivh4R3k6lcrWBW6Ghs58egjhPs+9BIGTgjVojiMOxNLYBNXi/NmuFGBni76tmzl+EP/gN33IWrAYoniaqGeKEu0pQFGI5z44skVf3Idld/5DhRyqBUtVWqvjPMGqsrUqVOpq6sb14on2cdqbm6mra3tvAmETsoEOOfYsmULn376KdB/PQaXD6irqys8bPr6+ti1axfOOaIoorGxkRMnTpDNZrn++utLEKAQZ3MIgYCagFRDA7m710NFFWE6jTg3IqtGDViNSFlL3+7tnNzwMN2v/hvu5FGMS/kaHcPpR51GGBQniljQME90cD8nN/6U4089gT38OWrSCInEviGI0+hKFjA3PudYEDJ1l5G9cx2ay6GFR8TwedFlnB8Q8eUtFy9ePG6ZMFXlk08+4eDBg+eFYy3J6rj55psREfbu3cvLL79MGIaICLNnz2bZsmWsWLGCyy+/HGMM2WyWNWvWsGLFCpYvX05XVxd9fX00Nzfz3HPPEYYhCxYs4JprrhnqBfZTXrzySly5Q9OWzLf/jNxnbXS/9JLP0FAHpApKzqK+VkcCVY2VWoLYYszg2r+gZ9M/Ef7nW1Te8QMqrvxDNOPrcngnSiI0Lz6XGIeIfx+nUBTFAVIwsvzWZGx1FsZRlItSlDni+yY4CTCdx+j5j9c5+cq/I8eOEsQ3p7MWNbG4QbL2lf40tqSXXjxWcCqkL7qQSQ8/hMycSVAcw1iO+ysDP5GXLVtGKpUasQNgOHR3d/Puu+9y3XXXDVGj+bpg8Fhuv/12Ghsb2b9/P0899RT19fWsWrWKu+++m3vuuWfAZ0WExx9/fEBbR44cYcOGDbS2tpJKpVi/fj0XXXTRME4Q8elmIgaMoARICqpu+S6VN98c19X1ObYGh+IYLPAphWBm8KEjeDIxhnDfb+jc8CDHH36QfFMTrqcX1EtGeceII2E3h3iyVevbib0Oqj41Tj3/ouL83iWKl+YCi6/H4ZzFaeQdHdZh2z6j75VX6Pibezj+/Itw4hiYmKiTLJP+gcQc5h8NzhnAIQ5crIEYzJjOpA0bCObOIhCf49yfydIfsH2241ycQF91n8dzvsWLF5PL5Sbk/Nu3bx9ApKUyKM5lDA4dmzNnDo888gi5XI729nbWr19PY2NjQWuxeC8wCbRW9RJaBw8e5K677ioIKdx0002sWbPGpyqOpDO+mm2A5qqpWruWKIpg6xuoSfl9PPVFgkZkkmv85YQRfbuaCN97n/Rll1K58k8xV11FUDMdk8mgoliN84bV59D6eEMKll/ByivkKyuqFtHEdvVKMqKK7ezGtrYS/vKX9O7aRfTZpygWIUWBRU/ZdcVv9RkC4wgFpHo6k3/0AJlFi7yKTRmjxnisl686xnI8qWw1NTXU19ezffv2cRPUgQMHaG1tZd68eYUQkcHnO5tJcLSeYGMMq1ev5tChQ2zcuJG2tjbuvfdetm3bxrp161i4cCG5XI5MJoOq0tPTw+HDh9m6dSvPPvtsIXTo2muvZePGjdTU1IycAL27wmHEYKqrueCH99DlDJ1vvk6gGVTMKKwcg5EQh0E0hevrI/yf98n/+n1S06dhGq6gYtlSTyi1tWiQ8uTkAOOV94nl5+PLg9NkeSqIxpXZRHHd3eQPthDt+S+ipnfpPXQI19vjc3ExcQ3fEJUMpfKdByOQCKdp1FicE1LVk5n00N+TvWoJYgJUvNRV6hyx+M4WjHeijub4M0EKifc2EUbYvn37uNoCOHLkCM3NzcybN2/A385m0hsrklCiTCbDfffdR1VVFY8++igdHR289NJLvPrqqzQ0NLBw4UJmzpxJT08PLS0tfPDBB7S0tBSuyapVq3j66acLDhJVHRkBCgEpcT52TgSpmkTlD++CbIYTP3vVF01XS6mylEPbslgCVCElEaKCA1xKcR1Hcb96m/Ctt6EqR3DxbNJ180ktWIC5uJZgcjWSm4Rks0gqFrNGsfk8prsT23kSPdqBbfmYvo8OwKEW7Odf4IhAhMBCILGCs3o1GkuaFHZEFmCeNGn1+5JSO4vqH99P5sor0SDwgeIaF2E/R/nvTG2qj3finu3OgGQCG2NYunQp2WyW3t7eMY85SfvavXs3N9xwQ8EKPlfIb7TfV3F0STqdZv369VxxxRU89thj7Ny5k87OTnbs2MGOHTsIgqCw/AUfhD5v3jzWrl3LnXfeWcjJTjCyJXDsDY1XmV6SanI1FevuZPK0anoaX0F7O/2S0hmcEYwqThLpq34kDg4Rwakr7K8FztcFJsBbcj3d2I/2YQ80oyaAVApJpZBMGtJpTJDyxcidxYUhhCEahrgowkTx3oiAr9nh6zZoHNKCil8+m+GfAKp+H9SpjceuBAS4QEn/3nwm3X8/6fp6jAmAeIleOOnpR3LDG2OYO3cuDQ0NhX2QsRLC5MmTh4QynQ4U55QuXLiQfD5feD/aSSwiVFZWDqg7m2Bw8K4xhrq6Or+FM46+V1dXD5CkGum1EhHq6+tZvnw5HR0d41r2g7cCnXOFdLLk+s2ZM4dFixaddQ+G4ntzzpw5I/KIFwcwJw8REeFb3/oWixYtoqmpic2bN/Pee+9x7NgxrLWICNlslvnz57Ny5UpuvPFG5s6dSzqdHtKu6Bi/BZ9FYVGn9P1qG13PPIN2HAdxWLWkCHBi/TJzgqADyFQGvPfjEe/BHhQlPjZ4l4pRr2GIQCCOYMkSqu/7EXLxxYhIwXN8pqCqdHd3E0XRuJLVwQeSFt8kpxuJJVNc+Hu0xydjraqqKgS/Dv5MAuccvb29Y/bCFvexqqqKIAhOea0Hj8k5R3d3d8m/jaU/uVxuCJH09PQU0r3OJhRfvyAIBtRUGQ0GZ5aA94y3t7fT19eHMYYpU6ZwwQUXFL6j4faKx0yAVhUTC4OqyxM1H6DzHzehv/lfNKjCaRinxk0QQRRFtSTviwJN/EWBfmdJ8rExEoKqEKgSBWBE0aCCqu99l9z3v4/L+NxEA4g5846PYmmh0ZBgYhUlN1TydP0qMdaUruIxFlsHw3221P+jGWtyjmQ/bzTXqlQa2Xju0eT8QElhgGLllfE8ECcaxdfhy76vkbaT3LNAwfIrHnup9gdfizEToFPr9++SBAnrsMc66P3XRvI/30K+s5sgAJmglMWSnTzFL8eVdyk+Y0VEkbnfYNLadWSWLcdVpEg50DiA25wF9X0TCyrBWCfmmVCtKeW9HCmKLcfh+j6e9r+snZG2VSo8ZTwTfzChfdmYi/fBzjTGev1O1dbglcOpCH/iCNBFIN56MKKoxkHEUUS4Zw89z79IeKC5KA9iYDJtEpesMrys/ACMsJfK0ItT8iYhCaORol/Ex4pBnSOoqiJ9w7epuvU2UjNrwAhCAArWKIEqImfeAhw8McZCgKM5biJRPFlH0/+RTKjhbu2xrQj6rcZCrvsYr3NxH0ZjoZWa+MMdO9rVwOlG0o/ih/W4DJQvoa3iB+Kp7vExE+CQk8YByk5BrCXq6iB87Rd0vv4aHD6MSwUIPosiqR0cIEOCqDWOyZuor2wA7Wr/CwtxbRFIqpQHKjhx4ALSDVcw6a9uI33lUlws3Z+UwSyjjDK+HphgAvTEIgjilMhF0NpKz2uvEb71Nq69AxEhMhajARbBiCMpqF7M2BP11BqwV9TfWQL1MXsas6DBoakMsmA+VX+xhuyKb6KTJxFgEIlwGhCIOWdDXMooo4yhmDACRCFUS7FQlopgrEUd9P3ud4S/+Bm97+zAHWkn5bx2npX+Km+JM+N0mOxFCSQYp9jAE7VTMOkM6bo6Km/6c1LLlpGaOhUnzvuvJT3g6LNhOVFGGWVMDCaUAF28Ayco4rwH2CulgHUuFkr9jPzO7fRufZPow/1YDUmnKkh64XRg7YAJ6losaeWFHqw6nA3J5KrJfPNaMjdeT3rRH6DZCgIHLojzfhWCom4I5RVwGWV8nTCBBKieAMXvpSVU6MTn4qL+tbEADu3tIfrtb+l7Zyf5X/83YesnmK4T/hiTlK/Uoj28fldKbIvFtdnjJXNRmExyTGLheXa2YNKYmhpSv19HxbKlVFx9NUybDnHsmwqkkqDmWOkFwdcvTvpRZsAyyvjaYOIIcBQYEJxqQ+zx47i2NsL/24v9YC9hSwva3gG9vdgoBGu9CrV6VZkkQBkBFxNlIrIgqmAMEgRoOkNQPZngolqCy+pINzQQzLuEoGY6QWXWE6X0R9GXUUYZ5xfOCAEm8Kd24ByIzw8WVbSrC3u4DftFO7btM+wnrdi2z3BHjyKdnUR9vV7R2VpfRD2VRnI5qJ5MMKOW1MW1mNmzMDMvJJg+HZk+HUlVxBac9U4X8TZigjIBllHG+Yf/B52NO1ZZIPV/AAAAAElFTkSuQmCC" style=width:"height: 45px;"></div>';
1446
1447                        $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
1448
1449                        $successCount = 0;
1450                        $failCount = 0;
1451
1452                        $email = new Mail;
1453
1454                        // $fromEmail = 'recordatorio.factura@fire.es';
1455                        $fromName = 'Recordatorio Facturas Grupo Fire';
1456                        $email->setFrom($fromEmail[$entorno], $fromName);
1457                        $email->setReplyTo($fromEmail[$entorno], 'Recordatorio Facturas Grupo Fire');
1458                        $email->setSubject('Envío de factura '.$idFactura);
1459                        $email->addContent('text/html', $html);
1460                        $email->addTo($emailToSend);
1461
1462                        if (! base64_decode((string) $invoiceData['documento'], true)) {
1463                            throw new \Exception('El documento no es un base64 válido');
1464                        }
1465
1466                        // No Content-ID on the attachment: Gmail strips
1467                        // inline-orphan MIME parts and the PDF never arrives.
1468                        $attachment = new Attachment;
1469                        $attachment->setContent($invoiceData['documento']);
1470                        $attachment->setType('application/pdf');
1471                        $attachment->setFilename($idFactura);
1472                        $attachment->setDisposition('attachment');
1473                        $email->addAttachment($attachment);
1474
1475                        try {
1476                            $response = $sendgrid->send($email);
1477                            SendgridLogger::log($email, $response);
1478                        } catch (\Throwable $sendException) {
1479                            SendgridLogger::logException($email, $sendException);
1480                            throw $sendException;
1481                        }
1482
1483                        if ($response->statusCode() != 202) {
1484                            throw new Exception('Envio fallido');
1485                        }
1486
1487                        // --- AQUÍ LLAMAS A TU LÓGICA DE ENVÍO ---
1488
1489                        // $updateRange = $sheetName . '!D' . $currentRowNumber . ':E' . $currentRowNumber;
1490                        $updateData = [[$entorno, $idFactura, $emailToSend, 'Si', date('Y-m-d H:i:s')]];
1491
1492                        $this->writeToGoogleSheet(
1493                            $googleSheetsService,
1494                            $spreadsheetId,
1495                            $sheetName,
1496                            $updateData,
1497                            $currentRowNumber
1498                        );
1499
1500                    } catch (\Exception $e) {
1501                        continue;
1502                    }
1503                }
1504            }
1505
1506            return ['success' => true, 'message' => 'Processing complete'];
1507
1508        } catch (\Exception $e) {
1509            Log::error('Error en sendCallCenterInvoices: '.$e->getMessage());
1510
1511            return response()->json(['success' => false, 'message' => $e->getMessage()]);
1512        }
1513    }
1514
1515    // Google sheets
1516    public function addToSheets($codCliente, $invoice, $region, $spreadsheetId = '15Lc9tJnHDOGp33V9RH86mXtIybJBAWWlW4fe7knhDZY')
1517    {
1518        $sheetName = 'Hoja 1';
1519        $googleSheetsService = null;
1520        $nextRow = 1;
1521
1522        try {
1523            $googleSheetsService = $this->getGoogleSheetsService();
1524            $range = $sheetName.'!A:A';
1525            $response = $googleSheetsService->spreadsheets_values->get($spreadsheetId, $range);
1526            $values = $response->getValues();
1527            $nextRow = $values ? count($values) + 1 : 1;
1528
1529        } catch (\Exception $e) {
1530            if (str_contains($e->getMessage(), 'Primera configuración requerida')) {
1531                return [
1532                    'success' => false,
1533                    'message' => $e->getMessage(),
1534                    'requires_auth' => true,
1535                ];
1536            }
1537
1538            return [
1539                'success' => false,
1540                'message' => 'Error de conexión con Google Sheets: '.$e->getMessage(),
1541            ];
1542        }
1543
1544        if ($googleSheetsService && $nextRow === 1) {
1545            $this->writeToGoogleSheet($googleSheetsService, $spreadsheetId, $sheetName, [
1546                ['ID Cliente', 'Número de Factura', 'Fecha de Compromiso de Pago', 'Fecha de Compromiso de Pago + 10 días', 'Region'],
1547            ], 1);
1548            $nextRow = 2;
1549        }
1550
1551        $fechaCompromiso = date('Y-m-d');
1552        $fechaCompromisoMas10 = date('Y-m-d', strtotime('+10 days'));
1553
1554        $data = [
1555            $codCliente,
1556            $invoice,
1557            $fechaCompromiso,
1558            $fechaCompromisoMas10,
1559            $region,
1560        ];
1561
1562        if ($googleSheetsService) {
1563            $this->writeToGoogleSheet($googleSheetsService, $spreadsheetId, $sheetName, [$data], $nextRow);
1564            $nextRow++;
1565        }
1566
1567    }
1568
1569    private function getGoogleSheetsService(): Sheets
1570    {
1571
1572        $redirectUrl = '';
1573        if (config('app.env') === 'production') {
1574            $redirectUrl = 'https://fireservicetitan.com/api/google-sheets-callback';
1575        }
1576
1577        if (config('app.env') === 'staging') {
1578            $redirectUrl = 'https://stg.fireservicetitan.com/api/google-sheets-callback';
1579        }
1580
1581        if (config('app.env') === 'local') {
1582            $redirectUrl = 'http://localhost:8000/api/google-sheets-callback';
1583        }
1584
1585        $client = new Client;
1586        $client->setAuthConfig(storage_path('app/credentials.json'));
1587        $client->addScope(Sheets::SPREADSHEETS);
1588        $client->setAccessType('offline');
1589        $client->setPrompt('select_account consent');
1590        $client->setRedirectUri($redirectUrl);
1591
1592        $tokenPath = storage_path('app/token.json');
1593
1594        if (file_exists($tokenPath)) {
1595            $accessToken = json_decode(file_get_contents($tokenPath), true);
1596            $client->setAccessToken($accessToken);
1597        }
1598
1599        if ($client->isAccessTokenExpired()) {
1600            if ($client->getRefreshToken()) {
1601                $newToken = $client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
1602                file_put_contents($tokenPath, json_encode($newToken));
1603            } else {
1604                $authUrl = $client->createAuthUrl();
1605                throw new \Exception('Initial setup required. Visit this URL to authorize: '.$authUrl);
1606            }
1607        }
1608
1609        return new Sheets($client);
1610    }
1611
1612    private function writeToGoogleSheet($service, $spreadsheetId, string $sheetName, array $data, int|float $rowNumber)
1613    {
1614        try {
1615            $range = $sheetName.'!A'.$rowNumber.':E'.$rowNumber;
1616
1617            $body = new ValueRange([
1618                'values' => $data,
1619            ]);
1620
1621            $params = [
1622                'valueInputOption' => 'USER_ENTERED',
1623            ];
1624
1625            $result = $service->spreadsheets_values->update(
1626                $spreadsheetId,
1627                $range,
1628                $body,
1629                $params
1630            );
1631
1632            return $result;
1633
1634        } catch (\Exception $e) {
1635            Log::error('❌ Error escribiendo en Google Sheets: '.$e->getMessage());
1636            throw $e;
1637        }
1638    }
1639
1640    public function handleGoogleAuthCallback(Request $request)
1641    {
1642        try {
1643            // Verificar que el código existe
1644            if (! $request->get('code')) {
1645                Log::error('❌ No code parameter found');
1646
1647                return response()->json([
1648                    'success' => false,
1649                    'message' => 'No authorization code provided',
1650                ], 400);
1651            }
1652
1653            $redirectUrl = '';
1654
1655            if (config('app.env') === 'production') {
1656                $redirectUrl = 'https://fireservicetitan.com/api/google-sheets-callback';
1657            }
1658
1659            if (config('app.env') === 'staging') {
1660                $redirectUrl = 'https://stg.fireservicetitan.com/api/google-sheets-callback';
1661            }
1662
1663            if (config('app.env') === 'local') {
1664                $redirectUrl = 'http://localhost:8000/api/google-sheets-callback';
1665            }
1666
1667            $client = new Client;
1668            $client->setAuthConfig(storage_path('app/credentials.json'));
1669            $client->addScope(Sheets::SPREADSHEETS);
1670            $client->setRedirectUri($redirectUrl);
1671
1672            $token = $client->fetchAccessTokenWithAuthCode($request->get('code'));
1673
1674            file_put_contents(storage_path('app/token.json'), json_encode($token));
1675
1676            return response()->json([
1677                'success' => true,
1678                'message' => 'Google Sheets authentication complete! You can now run the invoicing function.',
1679            ]);
1680
1681        } catch (\Exception $e) {
1682            Log::error('Error on Google sheets callback: '.$e->getMessage());
1683            Log::error('Error details: '.$e->getTraceAsString());
1684
1685            return response()->json([
1686                'success' => false,
1687                'message' => 'Error: '.$e->getMessage(),
1688            ], 400);
1689        }
1690    }
1691
1692    private function getVencimientosFormateados(array $dataInvoice): ?string
1693    {
1694        $vencimientos = $dataInvoice['factura']['vencimientos'] ?? [];
1695
1696        if (count($vencimientos) === 0) {
1697            return null;
1698        }
1699
1700        $fechasFormateadas = array_map(fn (array $vencimiento) => Carbon::createFromFormat('Y-m-d', $vencimiento['fecha_vencimiento'])
1701            ->isoFormat('D [de] MMMM [de] YYYY'), $vencimientos);
1702
1703        return implode(' Ã³ ', $fechasFormateadas);
1704    }
1705
1706    public function listCreditDaysOffered($region): array
1707    {
1708        try {
1709
1710            ini_set('max_execution_time', 123456);
1711            $date = now()->subMonth()->toDateString();
1712
1713            $lastMonthClients = $this->request('get', 'cliente/nuevosconunafactura/'.$date, $region, []);
1714
1715            if (! $lastMonthClients) {
1716                throw new \Exception('Error al obtener los clientes o la lista está vacía');
1717            }
1718
1719            $tempData = [];
1720            $monthNames = [
1721                1 => 'Enero', 2 => 'Febrero', 3 => 'Marzo', 4 => 'Abril',
1722                5 => 'Mayo', 6 => 'Junio', 7 => 'Julio', 8 => 'Agosto',
1723                9 => 'Septiembre', 10 => 'Octubre', 11 => 'Noviembre', 12 => 'Diciembre',
1724            ];
1725
1726            foreach ($lastMonthClients as $client) {
1727                $id = $client['ID'];
1728                $clientInvoiceResponse = $this->request('get', 'factura/cliente/'.$id, $region, []);
1729                $invoices = $clientInvoiceResponse['facturas'] ?? [];
1730
1731                // 1. Filtro: Si tiene 2 o más facturas, saltamos (queremos 0 o 1)
1732                if (count($invoices) >= 2) {
1733                    continue;
1734                }
1735
1736                $clientData = $this->request('get', 'cliente/'.$id, $region, []);
1737                $clientData = $clientData['cliente'];
1738
1739                // 2. Inicializamos valores por defecto para clientes SIN factura
1740                $clientValue = 0;
1741                $creditDays = 0;
1742                $invoiceData = null;
1743
1744                // 3. Solo si tiene exactamente 1 factura, buscamos sus datos reales
1745                if (count($invoices) === 1) {
1746                    $invoiceResponse = $this->request('get', 'factura/'.$invoices[0]['numero'], $region, []);
1747                    $invoiceData = $invoiceResponse['factura'];
1748
1749                    $clientValue = $invoiceData['base_imponible_factura'] ?? 0;
1750
1751                    // Calculamos días de crédito solo si existen las fechas
1752                    if (! empty($invoiceData['fecha_creacion']) && ! empty($invoiceData['fecha_emision'])) {
1753                        $creation = Carbon::parse($invoiceData['fecha_creacion']);
1754                        $send = $invoiceData['vencimientos'][0]['fecha_vencimiento']
1755                                    ?? $invoiceData['fecha_emision'];
1756                        $creditDays = $creation->diffInDays($send);
1757                    }
1758                }
1759
1760                $date = ($clientData['fecha_alta'] === '0000-00-00' || empty($clientData['fecha_alta']))
1761                    ? Carbon::now()
1762                    : Carbon::parse($clientData['fecha_alta']);
1763
1764                $year = $date->year;
1765                $monthNum = $date->month;
1766                $nameMes = $monthNames[$monthNum];
1767                $weekNum = 'Semana '.$date->weekOfMonth;
1768
1769                // Estructura de agrupación (agregamos acumuladores)
1770                if (! isset($tempData[$year])) {
1771                    $tempData[$year] = [];
1772                }
1773                if (! isset($tempData[$year][$monthNum])) {
1774                    $tempData[$year][$monthNum] = [
1775                        'month' => $nameMes,
1776                        'value' => 0,
1777                        'credit_days' => 0,
1778                        'weeks' => [],
1779                    ];
1780                }
1781                if (! isset($tempData[$year][$monthNum]['weeks'][$weekNum])) {
1782                    $tempData[$year][$monthNum]['weeks'][$weekNum] = [
1783                        'created_at' => $weekNum,
1784                        'value' => 0,
1785                        'credit_days' => 0,
1786                        'clients' => [],
1787                    ];
1788                }
1789
1790                // Sumamos a los totales del mes/semana
1791                $tempData[$year][$monthNum]['value'] += $clientValue;
1792                $tempData[$year][$monthNum]['weeks'][$weekNum]['value'] += $clientValue;
1793
1794                $tempData[$year][$monthNum]['weeks'][$weekNum]['clients'][] = [
1795                    'cif' => $clientData['cliente_cif'],
1796                    'client_code' => $clientData['cod_cliente'],
1797                    'client_name' => $clientData['empresa'],
1798                    'value' => number_format($clientValue, 2, ',', '.').' €',
1799                    'credit_days' => $creditDays,
1800                    'payment_days' => $clientData['forma_pago'] ?? 'N/A',
1801                    'date' => $date->toDateString(),
1802                    'has_invoice' => count($invoices) === 1, // Flag útil para el frontend
1803                ];
1804            }
1805
1806            // Formateo final
1807            $finalData = [];
1808            foreach ($tempData as $year => $months) {
1809                $yearEntry = ['year' => $year, 'months' => []];
1810                foreach ($months as $m) {
1811                    $m['weeks'] = array_values($m['weeks']);
1812                    $m['value'] = number_format($m['value'], 2, ',', '.').' €';
1813                    $yearEntry['months'][] = $m;
1814                }
1815                $finalData[] = $yearEntry;
1816            }
1817
1818            return ['success' => true, 'data' => $finalData];
1819
1820        } catch (\Exception $e) {
1821            return ['success' => false, 'error' => $e->getMessage()];
1822        }
1823    }
1824
1825    /**
1826     * FIRE-1293 (skip telemetry) — append-only record of a "we considered
1827     * this invoice and decided NOT to remind it" event. Inserts one row per
1828     * skip per cron-day so trends and postmortems don't depend on log-file
1829     * grep.
1830     *
1831     * Wrapped in try/catch so a DB error here can never abort the surrounding
1832     * reminder loop — losing one skip telemetry row is acceptable; losing a
1833     * reminder send is not.
1834     *
1835     * Reason codes are documented inline at each call site. Keep them
1836     * snake_case and stable — future SQL aggregations key off these strings.
1837     *
1838     * @param  array<string,mixed>|null  $context  optional payload (e.g.
1839     *                                             `['forma_pago' => 'CHEQUE']`) — anything that helps explain
1840     *                                             a skip after the fact without re-reading G3W
1841     */
1842    private function recordSkip(
1843        string $region,
1844        ?string $invoiceNumber,
1845        string $reason,
1846        ?array $context = null,
1847        ?int $reminderType = null,
1848        ?string $clientCode = null,
1849        ?int $amount = null,
1850    ): void {
1851        try {
1852            TblInvoiceReminderSkips::create([
1853                'invoice_number' => $invoiceNumber,
1854                'region' => $region,
1855                'skip_reason' => $reason,
1856                'reminder_type' => $reminderType,
1857                'client_code' => $clientCode,
1858                'amount' => $amount,
1859                'context' => $context,
1860            ]);
1861        } catch (\Throwable $e) {
1862            Log::channel('g3w_invoices')->warning('recordSkip failed', [
1863                'region' => $region,
1864                'invoice_number' => $invoiceNumber,
1865                'reason' => $reason,
1866                'error' => $e->getMessage(),
1867            ]);
1868        }
1869    }
1870
1871    /**
1872     * FIRE-1293 — Read `fecha_cobro` from G3W's `factura/{id}` payload and
1873     * persist it into `tbl_invoice_reminders.collection_date` for every
1874     * reminder row sharing the same (invoice_number, region).
1875     *
1876     * Spike (findings/FIRE-1293_Investigation.md, Finding 2) confirmed
1877     * `cobrada='NO'` with empty `fecha_cobro` for unpaid invoices, and
1878     * `cobrada='SI'` with `fecha_cobro='YYYY-MM-DD'` once paid. We only
1879     * persist the date when both signals agree.
1880     *
1881     * @return string|null the synced YYYY-MM-DD date, or null if the
1882     *                     invoice is still unpaid
1883     */
1884    public function syncPaymentDateForInvoice(string $region, string $invoiceNumber): ?string
1885    {
1886        $response = $this->request('get', 'factura/'.$invoiceNumber, $region, []);
1887        $factura = $response['factura'] ?? null;
1888
1889        if (! is_array($factura)) {
1890            return null;
1891        }
1892
1893        $cobrada = $factura['cobrada'] ?? 'NO';
1894        $fechaCobro = $factura['fecha_cobro'] ?? null;
1895
1896        if ($cobrada === 'NO' || ! is_string($fechaCobro) || $fechaCobro === '') {
1897            return null;
1898        }
1899
1900        TblInvoiceReminders::where('invoice_number', $invoiceNumber)
1901            ->where('region', $region)
1902            ->whereNull('collection_date')
1903            ->update(['collection_date' => $fechaCobro]);
1904
1905        return $fechaCobro;
1906    }
1907
1908    /**
1909     * FIRE-1293 — For every distinct (invoice_number, region) pair in
1910     * `tbl_invoice_reminders` that still has a NULL `collection_date`
1911     * and was first sent within `$daysBack` days, hit G3W once and
1912     * persist the payment date if the invoice is now paid.
1913     *
1914     * Default 90-day window: the KPI's attribution rule is 20 days
1915     * from first reminder; 90 leaves a generous tail for batched
1916     * collection runs without polling rows that will never attribute.
1917     *
1918     * @return array{checked:int,resolved:int,unpaid:int,errors:int}
1919     */
1920    public function syncPaymentDatesForRegion(string $region, int $daysBack = 90): array
1921    {
1922        $checked = 0;
1923        $resolved = 0;
1924        $unpaid = 0;
1925        $errors = 0;
1926
1927        $cutoff = Carbon::now()->subDays($daysBack)->toDateTimeString();
1928
1929        $invoiceNumbers = TblInvoiceReminders::where('region', $region)
1930            ->whereNull('collection_date')
1931            ->whereNotNull('invoice_number')
1932            ->where('send_datetime', '>=', $cutoff)
1933            ->distinct()
1934            ->pluck('invoice_number');
1935
1936        foreach ($invoiceNumbers as $invoiceNumber) {
1937            $checked++;
1938            try {
1939                $result = $this->syncPaymentDateForInvoice($region, (string) $invoiceNumber);
1940                if ($result !== null) {
1941                    $resolved++;
1942                } else {
1943                    $unpaid++;
1944                }
1945            } catch (\Throwable $e) {
1946                $errors++;
1947                Log::channel('g3w_invoices')->warning('syncPaymentDateForInvoice failed', [
1948                    'region' => $region,
1949                    'invoice_number' => $invoiceNumber,
1950                    'error' => $e->getMessage(),
1951                ]);
1952            }
1953        }
1954
1955        Log::channel('g3w_invoices')->info('syncPaymentDatesForRegion finished', [
1956            'region' => $region,
1957            'days_back' => $daysBack,
1958            'checked' => $checked,
1959            'resolved' => $resolved,
1960            'unpaid' => $unpaid,
1961            'errors' => $errors,
1962        ]);
1963
1964        return compact('checked', 'resolved', 'unpaid', 'errors');
1965    }
1966}