Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.09%
1 / 1174
3.70%
1 / 27
 CRAP
0.00%
0 / 1
PresupuestosService
0.09%
1 / 1174
3.70%
1 / 27
 135446.24
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 normalizeG3wAcceptance
0.00%
0 / 10
0.00%
0 / 1
 30
 syncByDate
0.00%
0 / 96
0.00%
0 / 1
 812
 fetchFromGestiona
0.00%
0 / 23
0.00%
0 / 1
 42
 syncById
0.00%
0 / 281
0.00%
0 / 1
 9120
 syncModifiedBudgetById
0.00%
0 / 208
0.00%
0 / 1
 4290
 classifyLineAsLabor
0.00%
0 / 10
0.00%
0 / 1
 56
 calculateBudgetMargin
0.00%
0 / 54
0.00%
0 / 1
 380
 syncErrorBudgets
0.00%
0 / 55
0.00%
0 / 1
 182
 syncBudgetsWorks
0.00%
0 / 56
0.00%
0 / 1
 156
 notifyErrors
0.00%
0 / 3
0.00%
0 / 1
 2
 generateQuoteId
0.00%
0 / 33
0.00%
0 / 1
 90
 normalizeStatus
0.00%
0 / 10
0.00%
0 / 1
 12
 normalizeSegment
0.00%
0 / 8
0.00%
0 / 1
 6
 normalizeType
0.00%
0 / 16
0.00%
0 / 1
 20
 normalizeSource
0.00%
0 / 21
0.00%
0 / 1
 56
 saveDocument
0.00%
0 / 45
0.00%
0 / 1
 72
 formatBytes
0.00%
0 / 6
0.00%
0 / 1
 6
 updateLogs
0.00%
0 / 34
0.00%
0 / 1
 20
 checkEmailInvalid
0.00%
0 / 8
0.00%
0 / 1
 12
 checkRequiredFields
0.00%
0 / 18
0.00%
0 / 1
 420
 isBlacklistedEmail
0.00%
0 / 8
0.00%
0 / 1
 30
 syncExistingDataWithWarnings
0.00%
0 / 14
0.00%
0 / 1
 30
 syncByIds
0.00%
0 / 64
0.00%
0 / 1
 240
 syncNullBudget
0.00%
0 / 21
0.00%
0 / 1
 30
 getAlternativeClientData
0.00%
0 / 26
0.00%
0 / 1
 132
 checkAproval
0.00%
0 / 45
0.00%
0 / 1
 182
1<?php
2
3namespace App\Services;
4
5use App\Http\Controllers\Quotations;
6use App\Models\TblBudgetStatus;
7use App\Models\TblBudgetTypes;
8use App\Models\TblCompanies;
9use App\Models\TblFiles;
10use App\Models\TblG3wLastUpdate;
11use App\Models\TblG3WOrdersUpdateLogs;
12use App\Models\TblInvoicesExceptions;
13use App\Models\TblLastFollowUpDate;
14use App\Models\TblProjectTypes;
15use App\Models\TblQuotations;
16use App\Models\TblSegmentG3wMapping;
17use App\Models\TblSegments;
18use App\Models\TblSourceG3wMapping;
19use App\Models\TblSources;
20use App\Models\TblStatusG3wMapping;
21use App\Models\TblTypeG3wMapping;
22use App\Models\TblUserG3wMapping;
23use App\Models\TblUsers;
24use App\Models\TblVipClients;
25use Google\Service\AdMob\Date;
26use Illuminate\Database\QueryException;
27use Illuminate\Support\Carbon;
28use Illuminate\Support\Facades\DB;
29use Illuminate\Support\Facades\Log;
30use Illuminate\Support\Facades\Storage;
31use Mockery\Exception;
32use SendGrid\Mail\Mail;
33use Illuminate\Contracts\Routing\ResponseFactory;
34use Illuminate\Http\JsonResponse;
35use Illuminate\Http\Response;
36
37class PresupuestosService extends GestionaService
38{
39    public function __construct(private readonly Quotations $quotationsController, private readonly WorkService $workSevice)
40    {
41        parent::__construct();
42    }
43
44    /**
45     * FIRE-1024 (2026-04-30): G3W returns acceptance dates in a half-dozen
46     * empty representations — null, empty string, '0000-00-00', '0000/00/00',
47     * '0000-00-00 00:00:00', sometimes whitespace. Normalise all of them to
48     * PHP null so MySQL stores NULL, regardless of strict mode.
49     *
50     * Pre-fix the creation path at line 656 only caught two of these forms;
51     * the modification path at line 864 already used `empty()` + array but
52     * had its own logic bug (see FIRE-1024 investigation Bug 2). Centralise
53     * here so every site reads the same source of truth.
54     */
55    private function normalizeG3wAcceptance(mixed $raw): ?string
56    {
57        if ($raw === null) {
58            return null;
59        }
60        if (! is_string($raw)) {
61            return null;
62        }
63        $trimmed = trim($raw);
64        if ($trimmed === '') {
65            return null;
66        }
67        if (in_array($trimmed, ['0000-00-00', '0000/00/00', '0000-00-00 00:00:00'], true)) {
68            return null;
69        }
70        return $trimmed;
71    }
72
73    /**
74     * Synchronize budgets as of a specific date.
75     * It also manages the last synchronized ID.
76     *
77     * @param string $date Default today's date
78     * @param string $name Who's launch the function
79     */
80    public function syncByDate($date = null, $name = null, $region = "Cataluña"): array
81    {
82        try {
83            if ($region === 'Catalunya') {
84                $region = 'Cataluña';
85            }
86
87            $g3wActive = TblCompanies::where('region', $region)->first()->g3W_active;
88
89            if (! $g3wActive) {
90                $startCronDateTime = date('Y-m-d H:i:s');
91                $this->updateLogs(['id' => 0, 'error' => 'La sincronización esta desactivada en la region '.$region.'.'], 0, [], $startCronDateTime, 'System', $region);
92                throw new Exception("La sincronización con G3W debe estar desactivada en la region '$region'.");
93            }
94
95            $this->setSyncStatus(1, $region);
96            $this->syncExistingDataWithWarnings();
97
98            $successfulSyncs = 0;
99            $failedSyncs = [];
100            $successIdSyncs = [];
101
102            $startCronDateTime = date('Y-m-d H:i:s');
103
104            $date ??= date('Y-m-d');
105
106            $budgets = $this->request('get', 'presupuesto?fecha='.$date, $region, []);
107
108            if (! $budgets) {
109                TblG3wLastUpdate::where('region', $region)->first()?->update(['updatingNow' => 0]);
110                $startCronDateTime = date('Y-m-d H:i:s');
111                $this->updateLogs(['id' => 0, 'error' => 'No hay presupuestos que sincronizar para la region '.$region.'.'], 0, [], $startCronDateTime, 'System', $region);
112
113                return [
114                    'success' => true,
115                    'message' => 'No budgets to upload.',
116                ];
117            }
118
119            if (is_string($budgets)) {
120                $budgets = json_decode($budgets, true);
121            }
122
123            if (! is_array($budgets) || empty($budgets)) {
124                throw new \Exception('No budgets to process.');
125            }
126
127            $company = TblCompanies::where('region', $region)->first();
128
129            foreach ($budgets as $budget) {
130                if (! is_array($budget) || ! isset($budget['ID'])) {
131                    continue;
132                }
133
134                if (
135                    TblQuotations::where('internal_quote_id', $budget['ID'])
136                        ->where('company_id', $company->company_id)
137                        ->exists()
138                ) {
139                    continue;
140                }
141
142                if (
143                    in_array($company->company_id, [18, 22])
144                &&
145                    TblQuotations::where('internal_quote_id', $budget['ID'])
146                        ->whereIn('company_id', [18, 22])
147                        ->exists()
148                ) {
149                    continue;
150                }
151
152                $result = DB::transaction(fn() => $this->syncById($budget['ID'], $region));
153
154                if ($result['success']) {
155                    $successfulSyncs++;
156                    $successIdSyncs[] = [
157                        'id' => $budget['ID'],
158                    ];
159                } else {
160                    if (!str_contains((string) $result['error'], 'No se ha encontrado el presupuesto')) {
161                        $failedSyncs[] = [
162                            'id' => $budget['ID'],
163                            'error' => $result['error'] ?? 'Unknown error',
164                        ];
165                    }
166                }
167            }
168
169            $ids = array_filter(array_column($budgets, 'ID'), is_numeric(...));
170            if (empty($ids)) {
171                throw new \Exception('No valid IDs found in budgets.');
172            }
173            $lastId = max($ids);
174
175            TblG3wLastUpdate::where('region', $region)->first()->update([
176                'g3w_id' => $lastId,
177                'updatingNow' => 0,
178            ]);
179
180            $lastUpdate = TblG3wLastUpdate::where('region', $region)->first();
181
182            $time = '';
183
184            if ($lastUpdate) {
185                $updatedAt = Carbon::parse($lastUpdate->updated_at)->subHour();
186                $time = $updatedAt->format('H:i');
187            }
188
189            $modifiedBudgets = (str_contains($time, ':')) ?
190                $this->request('get', 'presupuesto/fechamodificacion/' . $date . "/" . $time, $region, []) :
191                $this->request('get', 'presupuesto/fechamodificacion/' . $date, $region, []);
192
193            if (is_array($modifiedBudgets) && ! empty($modifiedBudgets)) {
194                foreach ($modifiedBudgets as $budget) {
195                    // FIRE-1029: catch QueryException at the loop level so
196                    // a single deadlock victim rolls back its own
197                    // transaction (now that syncModifiedBudgetById rethrows
198                    // DB faults) without aborting the whole sync run.
199                    try {
200                        $result = DB::transaction(fn() => $this->syncModifiedBudgetById($budget['ID'], $region));
201                    } catch (QueryException $qe) {
202                        $result = ['success' => false, 'error' => "Error actualizando el presupuesto {$budget['ID']}: ".$qe->getMessage()];
203                    }
204
205                    if ($result['success']) {
206                        $successfulSyncs++;
207                        $successIdSyncs[] = [
208                            'id' => $budget['ID'],
209                        ];
210                    } else {
211                        if ($result['error']) {
212                            if (!str_contains((string) $result['error'], 'No se encuentra el presupuesto con ID en G3W') &&
213                                !str_contains((string) $result['error'], 'No se ha encontrado el presupuesto')) {
214                                $failedSyncs[] = [
215                                    'id' => $budget['ID'],
216                                    'error' => $result['error'] ?? 'Unknown error',
217                                ];
218                            }
219                        }
220                    }
221                }
222            }
223
224            // FIRE-1025: zero-date sweep moved to the daily
225            // `quotations:cleanup-zero-dates` artisan command. Pre-fix this
226            // UPDATE ran at the end of every G3W sync cycle (5 call sites
227            // here) and deadlocked on `idx_acceptance_date` when concurrent
228            // syncs piled up (see InnoDB MONITOR OUTPUT on 2026-04-27 with
229            // multiple "WE ROLL BACK TRANSACTION (1)" entries on this exact
230            // statement). FIRE-1001 normalises `aceptacion` on write so no
231            // new zero-dates can be created — the daily cleanup handles
232            // legacy rows.
233
234            $this->updateLogs($failedSyncs, $successfulSyncs, $successIdSyncs, $startCronDateTime, $name, $region);
235
236            return [
237                'success' => true,
238                'message' => 'Synchronization completed.',
239            ];
240
241        } catch (\Exception $e) {
242            Log::channel('g3w')->error('Error sincronizando los presupuestos: '.$e->getMessage());
243
244            if (TblG3wLastUpdate::where('region', $region)->first()->updatingNow === 1) {
245                TblG3wLastUpdate::where('region', $region)->first()->update(['updatingNow' => 0]);
246
247            }
248
249            $startCronDateTime = date('Y-m-d H:i:s');
250            $this->updateLogs(['id' => 0, 'error' => $e->getMessage()], 0, [], $startCronDateTime, 'System', $region);
251
252            return ['success' => false, 'error' => $e->getMessage()];
253        }
254    }
255
256    /**
257     * FIRE-976: Fetch a budget from Gestiona and return normalized status + amount
258     * without creating/updating anything in Titan.
259     *
260     * @return array{success: bool, data?: array, error?: string}
261     */
262    public function fetchFromGestiona($id, string $region): array
263    {
264        try {
265            $budget = $this->request('get', "presupuesto/{$id}", $region, []);
266
267            if (!isset($budget['presupuesto']) || !is_array($budget['presupuesto'])) {
268                return ['success' => false, 'error' => 'No se ha encontrado el presupuesto en Gestiona'];
269            }
270
271            $presupuesto = $budget['presupuesto'];
272
273            // Resolve status name from Gestiona status list
274            $statusList = $this->request('get', 'presupuesto/tiposestado', $region, []);
275            $nameStatus = collect($statusList)->firstWhere('ID', $presupuesto['estado'])['nombre'] ?? null;
276
277            // Normalize to FST status
278            if (!$nameStatus) {
279                $statusId = TblBudgetStatus::where('name', 'Sin estado en G3W')->first()->budget_status_id;
280            } else {
281                $statusId = $this->normalizeStatus($nameStatus);
282                if ($statusId === TblBudgetStatus::where('name', 'Sin estado en G3W')->first()->budget_status_id) {
283                    $statusId = TblBudgetStatus::where('name', 'Estado no reconocido en FST')->first()->budget_status_id;
284                }
285            }
286
287            $statusName = TblBudgetStatus::where('budget_status_id', $statusId)->value('name');
288
289            return [
290                'success' => true,
291                'data' => [
292                    'internal_quote_id' => $presupuesto['cod_presupuesto'],
293                    'amount' => $presupuesto['importe'] ?? null,
294                    'budget_status_id' => $statusId,
295                    'budget_status' => $statusName,
296                ],
297            ];
298        } catch (\Exception $e) {
299            return ['success' => false, 'error' => $e->getMessage()];
300        }
301    }
302
303    /**
304     * Synchronizes a budget by its ID.
305     *
306     * @param $id int ID to search in G3W
307     */
308    public function syncById($id, $region): array
309    {
310        try {
311            Log::channel('allInfoQuotationsG3w')->info("Sincronizando presupuesto {$id}");
312            $g3wWarning = 0;
313            $g3wWarningFields = null;
314            $budget = $this->request('get', "presupuesto/{$id}", $region, []);
315
316            if (! isset($budget['presupuesto']) || ! is_array($budget['presupuesto'])) {
317                Log::channel('allInfoQuotationsG3w')->info('El presupuesto no contiene los datos esperados.');
318                throw new \Exception('El presupuesto no contiene los datos esperados.');
319            }
320
321            $companyId = TblCompanies::where('region', $region)->first()->company_id;
322
323            $existsQuery = TblQuotations::where('internal_quote_id', $budget['presupuesto']['cod_presupuesto']);
324            if (in_array($companyId, [18, 22])) {
325                $existsQuery->whereIn('company_id', [18, 22]);
326            } else {
327                $existsQuery->where('company_id', $companyId);
328            }
329            if ($existsQuery->exists()) {
330                Log::channel('allInfoQuotationsG3w')->info('El presupuesto ya existe, procedemos a modificarlo.');
331                $resultEdit = $this->syncModifiedBudgetById($id, $region);
332
333                return $resultEdit;
334                Log::channel('allInfoQuotationsG3w')->info("El presupuesto ya existe, procedemos a modificarlo.");
335                return $this->syncModifiedBudgetById($id, $region);
336            }
337
338            $statusList = $this->request('get', 'presupuesto/tiposestado', $region, []);
339
340            $collection = collect($statusList);
341
342            Log::channel('allInfoQuotationsG3w')->info('Estado del presupuesto: '.$budget['presupuesto']['estado']);
343
344            $nameStatus = $collection->firstWhere('ID', $budget['presupuesto']['estado'])['nombre'] ?? null;
345
346            Log::channel('allInfoQuotationsG3w')->info('Estado del presupuesto en FST: '.$nameStatus);
347
348            if (! $nameStatus) {
349                $statusID = TblBudgetStatus::where('name', 'Sin estado en G3W')->first()->budget_status_id;
350            } else {
351                $statusID = $this->normalizeStatus($nameStatus);
352
353                if ($statusID === TblBudgetStatus::where('name', 'Sin estado en G3W')->first()->budget_status_id) {
354                    $statusID = TblBudgetStatus::where('name', 'Estado no reconocido en FST')->first()->budget_status_id;
355                }
356            }
357
358            Log::channel('allInfoQuotationsG3w')->info('ID del estado del presupuesto en FST: '.$statusID);
359
360            $company = $this->request('get', "servicio/{$budget['presupuesto']['cod_servicio']}", $region, []);
361
362            if (! $company || (! isset($company['servicio']) && ! isset($company['cliente']))) {
363                $company = $this->request('get', "cliente/{$budget['presupuesto']['cod_cliente']}", $region, []);
364            }
365
366            $companyName = $company['servicio']['nombre_servicio'] ?? $company['cliente']['empresa'] ?? null;
367            $companyTelephone = $company['servicio']['telefono'] ?? $company['cliente']['telefono'] ?? null;
368            $companyEmail = $company['servicio']['email'] ?? $company['cliente']['email'] ?? null;
369
370            Log::channel('allInfoQuotationsG3w')->info('Nombre de la empresa: '.$companyName);
371
372            if($companyEmail) {
373                $companyEmail = trim((string) $companyEmail);
374
375                if (str_contains($companyEmail, ';')) {
376                    $companyEmail = str_replace(';', ',', $companyEmail);
377                }
378            }
379
380            Log::channel('allInfoQuotationsG3w')->info('Email de la empresa: '.$companyEmail);
381
382            if (! $companyName || ($budget['presupuesto']['cod_cliente'] === 9818 && in_array($companyId, [18, 22]))) {
383                $companyName = $this->getAlternativeClientData($budget['presupuesto']['datos_cliente_alternativo'])['name'];
384            }
385
386            if (! $companyTelephone || ($budget['presupuesto']['cod_cliente'] === 9818 && in_array($companyId, [18, 22]))) {
387                $companyTelephone = $this->getAlternativeClientData($budget['presupuesto']['datos_cliente_alternativo'])['number'];
388            }
389
390            if (! $companyEmail || ($budget['presupuesto']['cod_cliente'] === 9818 && in_array($companyId, [18, 22]))) {
391                $companyEmail = $this->getAlternativeClientData($budget['presupuesto']['datos_cliente_alternativo'])['email'];
392            }
393
394            if ($statusID === 2 && $this->checkEmailInvalid((string) $companyEmail)) {
395                Log::channel('allInfoQuotationsG3w')->info('FIRE-864: G3W "Enviado" en creación con email inválido ('.$companyEmail.') → Correo erroneo (22).');
396                $statusID = 22;
397            }
398
399            Log::channel('allInfoQuotationsG3w')->info('Nombre de la empresa tras alternative client data: '.$companyName);
400
401            $segmentID = $this->normalizeSegment($company['servicio']['tipo_servicio'] ?? 'Otro');
402
403            Log::channel('allInfoQuotationsG3w')->info('Segmento de la empresa: '.$segmentID);
404
405            $companyId = TblCompanies::where('region', $region)->first()->company_id;
406
407            if (! isset($budget['presupuesto']['documento']) || empty($budget['presupuesto']['documento'])) {
408                Log::channel('allInfoQuotationsG3w')->info('El presupuesto no tiene documento asociado.');
409                throw new \Exception('El presupuesto no tiene documento asociado. Creelo y vuelva a intentarlo.');
410            }
411
412            $companyNameFormatted = '';
413
414            if (! empty($companyName)) {
415                $companyNameFormatted = str_replace(' ', '_', $companyName);
416            }
417
418            $nameDocument = $companyNameFormatted
419                ? $budget['presupuesto']['cod_presupuesto'].'_'.$companyNameFormatted
420                : $budget['presupuesto']['cod_presupuesto'];
421
422            Log::channel('allInfoQuotationsG3w')->info('Nombre del documento: '.$nameDocument);
423
424            $typeId = $this->normalizeType($budget['presupuesto']['origen_presupuesto'] ?? null, $region);
425
426            Log::channel('allInfoQuotationsG3w')->info('Tipo de presupuesto: '.$typeId);
427
428            if (empty($budget['presupuesto']['fecha_creacion'])) {
429                $budget['presupuesto']['fecha_creacion'] = Carbon::now()->format('Y-m-d H:i:s');
430            }
431
432            $work = $this->request('get', "presupuesto/trabajos/{$id}", $region, []);
433
434            $workIds = [];
435
436            if (is_array($work)) {
437                foreach ($work as $item) {
438                    if (isset($item['ID'])) {
439                        $workIds[] = $item['ID'];
440                    }
441                }
442            }
443
444            $idsConcatenados = implode('/', $workIds);
445
446            Log::channel('allInfoQuotationsG3w')->info('Trabajos: '.$idsConcatenados);
447
448            $createdByUser = TblUserG3wMapping::where('name_g3w', $budget['presupuesto']['usuario'])->first();
449            $createdBy = null;
450            $comercial = null;
451
452            if(!$createdByUser){
453                TblUserG3wMapping::create([
454                    "name_g3w" => $budget["presupuesto"]["usuario"]?? null
455                ]);
456                $comercial = $budget["presupuesto"]["usuario"];
457                $g3wWarning = 1;
458            } else {
459                $createdBy = TblUsers::where('id', $createdByUser->id_fst)->value('name')
460                    ?? null;
461
462                $comercial = $createdBy;
463            }
464
465            Log::channel('allInfoQuotationsG3w')->info('Comercial: '.$comercial);
466
467            $comercialUser = TblUserG3wMapping::where('name_g3w', $budget['presupuesto']['cod_comercial_presupuesto'])->first();
468
469            if(!$comercialUser && $budget["presupuesto"]["cod_comercial_presupuesto"]){
470                TblUserG3wMapping::create([
471                    "name_g3w" => $budget["presupuesto"]["cod_comercial_presupuesto"]?? null
472                ]);
473            } else if($comercialUser){
474                $comercial = TblUsers::where("id", $comercialUser->id_fst)->value('name')
475                    ?? null;
476            }
477
478            Log::channel('allInfoQuotationsG3w')->info('Comercial limpio: '.$comercial);
479
480            $source = $budget['presupuesto']['cod_empresa_presupuesto'] ?? null;
481            $sourceId = ($source) ? $this->normalizeSource($source, $region) : null;
482
483            Log::channel('allInfoQuotationsG3w')->info('Source: '.$source);
484
485            if ($statusID == 12) {
486                $companyLimit = TblCompanies::where('company_id', $companyId)->first();
487                $inProgressCount = TblQuotations::where('budget_status_id', 12)->where('company_id', $companyId)->where('commercial', $comercial)->count();
488                if ($companyLimit->process_limit <= $inProgressCount) {
489                    Log::channel('allInfoQuotationsG3w')->info("Se ha alcanzado el número máximo de pedidos en curso ({$companyLimit->process_limit}) permitido por usuario para esta empresa.");
490                    throw new \Exception("No se pudo guardar el documento del presupuesto {$id} al crearlo. Error: Se ha alcanzado el número máximo de pedidos en curso (5) permitido por usuario para esta empresa.");
491                }
492            }
493
494            // Jomar
495            if ($companyId == 18 && $source == 9) {
496                $companyId = 22;
497            }
498
499            $lastFollowUp = TblLastFollowUpDate::where('company_id', $companyId)->where('budget_type_id', $typeId)->first();
500            $workingDays = 10;
501            if ($lastFollowUp && $lastFollowUp->last_follow_up_date) {
502                $workingDays = $lastFollowUp->last_follow_up_date;
503            }
504
505            $date = Carbon::now();
506
507            $daysAdded = 0;
508            while ($daysAdded < $workingDays) {
509                $date->addDay();
510                if ($date->isWeekday()) {
511                    $daysAdded++;
512                }
513            }
514
515            $lastFollowUpDate = $date;
516
517            if (
518                in_array($statusID, [13, 14]) ||
519                !$sourceId ||
520                (!$typeId || $typeId == 0 || $typeId == 16) ||
521                empty(trim((string) $companyName)) ||
522                empty(trim((string) $companyEmail))
523                // || $this->checkEmailInvalid($companyEmail)
524            ) {
525                $g3wWarning = 1;
526            }
527
528            $row = (object) [
529                'client' => $companyName,
530                'email' => $companyEmail,
531                'budget_status_id' => $statusID,
532                'commercial' => $comercial,
533                'source_id' => $sourceId,
534                'budget_type_id' => $typeId,
535                "amount" => $budget["presupuesto"]["importe"] ?? null
536            ];
537
538            $g3wWarningFields = $this->checkRequiredFields($row);
539
540            if ($g3wWarningFields != null && $g3wWarningFields != '') {
541                $g3wWarning = 1;
542            }
543
544            if ($g3wWarningFields !== null && str_contains($g3wWarningFields, 'Email')) {
545                $statusID = 22;
546            }
547
548            Log::channel('allInfoQuotationsG3w')->info('G3W warning: '.$g3wWarning);
549
550            $reasonForNotFollowingUp = null;
551
552            // iker chacori and juan carlos
553            $excludingUsers = ['igc', '030', '031', 'fcl', '018', '021', 'jcsp', '026', '029', 'EXMOF', 'MGF'];
554
555            $facilityUser = false;
556
557            if (
558                (in_array($budget['presupuesto']['usuario'], $excludingUsers) || in_array($budget['presupuesto']['cod_comercial_presupuesto'], $excludingUsers))
559                && $region == 'Cataluña'
560            ) {
561                $reasonForNotFollowingUp = 1;
562                $facilityUser = true;
563            }
564
565            if ($segmentID == 3) {
566                $reasonForNotFollowingUp = 2;
567            } elseif ($segmentID == 2) {
568                $reasonForNotFollowingUp = 1;
569            }
570
571            $client = $this->request('get', "cliente/{$budget['presupuesto']['cod_cliente']}", $region, []);
572
573            if (
574                $client['cliente']['tipo_cliente'] === 'Grandes Cuentas' ||
575                $client['cliente']['tipo_cliente'] === 'Grandes Clientes' ||
576                TblInvoicesExceptions::where('cif', $client['cliente']['cliente_cif'])->exists()
577            ) {
578                $reasonForNotFollowingUp = 2;
579            }
580
581            $isVip = false;
582
583            $cData = $client['cliente'] ?? [];
584            $isVip = TblVipClients::whereIn('company_id', [$companyId, 0])
585                ->where(function ($query) use ($cData) {
586                    $query->whereRaw('1 = 0');
587                    if (! empty($cData['cod_cliente'])) {
588                        $query->orWhere('id_client', $cData['cod_cliente']);
589                    }
590                    if (! empty($cData['empresa'])) {
591                        $query->orWhere('name', $cData['empresa']);
592                    }
593                    if (! empty($cData['cliente_cif'])) {
594                        $query->orWhere('cif', $cData['cliente_cif']);
595                    }
596                    if (! empty($cData['email'])) {
597                        $query->orWhere('email', $cData['email']);
598                    }
599                    if (! empty($cData['telefono'])) {
600                        $query->orWhere('phone', $cData['telefono']);
601                    }
602                })
603                ->exists();
604
605            if (! $isVip) {
606                $sData = $company['servicio'] ?? [];
607                $isVip = TblVipClients::whereIn('company_id', [$companyId, 0])
608                    ->where(function ($query) use ($sData) {
609                        $query->whereRaw('1 = 0');
610                        if (! empty($sData['cod_servicio'])) {
611                            $query->orWhere('id_client', $sData['cod_servicio']);
612                        }
613                        if (! empty($sData['nombre_servicio'])) {
614                            $query->orWhere('name', $sData['nombre_servicio']);
615                        }
616                        if (! empty($sData['servicio_cif'])) {
617                            $query->orWhere('cif', $sData['servicio_cif']);
618                        }
619                        if (! empty($sData['email'])) {
620                            $query->orWhere('email', $sData['email']);
621                        }
622                        if (! empty($sData['telefono'])) {
623                            $query->orWhere('phone', $sData['telefono']);
624                        }
625                    })
626                    ->exists();
627            }
628
629            if ($isVip) {
630                $reasonForNotFollowingUp = 2;
631            }
632
633            Log::channel('allInfoQuotationsG3w')->info('Reason for not following up: '.$reasonForNotFollowingUp);
634
635            $defaultUser = 0;
636
637            if (! $comercialUser) {
638                Log::channel('allInfoQuotationsG3w')->info('Default user');
639                if ($companyId == 18) {
640                    $defaultUser = 94;
641                }
642
643                if ($companyId == 19) {
644                    $defaultUser = 68;
645                }
646
647                if ($companyId == 22) {
648                    $defaultUser = 153;
649                }
650
651                if ($companyId == 30) {
652                    $defaultUser = 124;
653                }
654                Log::channel('allInfoQuotationsG3w')->info('Default user: '.$defaultUser);
655            }
656
657            if(config("app.env") === "local"){
658                if($defaultUser == 0){
659                    $defaultUser = 92;
660                }
661            }
662
663            $generateNumber = $this->generateQuoteId($companyId);
664            Log::channel('allInfoQuotationsG3w')->info('Generate number: '.$generateNumber['id']);
665
666            $g3wNewId = $generateNumber['id'];
667            $newQuoteId = $generateNumber['number'];
668
669            $forApproval = self::checkAproval($companyId, $typeId, 2, $budget['presupuesto']['importe'], $newQuoteId, $g3wNewId, $comercialUser?->id_fst ?? $defaultUser, $comercial);
670
671            Log::channel('allInfoQuotationsG3w')->info('For approval: '.$forApproval);
672
673            // FIRE-1024: normalise once and reuse across acceptance_date /
674            // accepted_at / accepted_by. The pre-fix inline ternary only
675            // caught '0000-00-00' and '0000/00/00' — empty string and the
676            // datetime form '0000-00-00 00:00:00' both leaked through.
677            $g3wAcceptance = $this->normalizeG3wAcceptance($budget['presupuesto']['aceptacion'] ?? null);
678
679            $g3wArray = [
680                'internal_quote_id' => $budget["presupuesto"]["cod_presupuesto"],
681                'quote_id' => $newQuoteId,
682                'company_id' => $companyId,
683                'customer_type_id' => 2,
684                'segment_id' => $segmentID,
685                'budget_type_id' => $typeId == 16 ? null : $typeId,
686                'budget_status_id' => $statusID,
687                'source_id' => $sourceId,
688                'client' => $companyName,
689                'phone_number' => $companyTelephone,
690                'email' => $companyEmail,
691                // FIRE-1001: G3W returns '0000-00-00' (dashes) for an absent
692                // date; older payloads used slashes — accept both. Same
693                // rationale as the update branch.
694                'issue_date' => in_array(($budget['presupuesto']['fecha_creacion'] ?? null), ['0000-00-00', '0000/00/00'], true) ? null : ($budget['presupuesto']['fecha_creacion'] ?? null),
695                'request_date' => in_array(($budget['presupuesto']['fecha_creacion'] ?? null), ['0000-00-00', '0000/00/00'], true) ? null : ($budget['presupuesto']['fecha_creacion'] ?? null),
696                // FIRE-1001 + FIRE-1024: pull acceptance date from G3W's
697                // `aceptacion` field, normalised via
698                // `normalizeG3wAcceptance()` so every empty representation
699                // (null, '', '0000-00-00', '0000/00/00',
700                // '0000-00-00 00:00:00', whitespace) lands as PHP null.
701                // accepted_by is only stamped when there's a real date —
702                // "System accepted on (no date)" is meaningless.
703                'acceptance_date' => $g3wAcceptance,
704                'accepted_at' => ($nameStatus === 'Aceptado') ? $g3wAcceptance : null,
705                'accepted_by' => ($nameStatus === 'Aceptado' && $g3wAcceptance !== null) ? 'System' : null,
706                'amount' => $budget['presupuesto']['importe'] ?? null,
707                'last_follow_up_date' => $facilityUser ? null : $lastFollowUpDate,
708                'commercial' => $comercial,
709                'created_at' => $budget['presupuesto']['fecha_creacion'] ?? null,
710                'created_by' => $createdBy,
711                'has_attachment' => 1,
712                'cost_of_labor' => 0,
713                'total_cost_of_job' => 0,
714                'invoice_margin' => 0,
715                'margin_for_the_company' => 0,
716                'revenue_per_date_per_worked' => 0,
717                'gross_margin' => 100,
718                'labor_percentage' => 0,
719                'sync_import' => 1,
720                'box_work_g3w' => $idsConcatenados,
721                'segment_by_g3w' => $company['servicio']['tipo_servicio'] ?? null,
722                'source_by_g3w' => $budget['presupuesto']['cod_empresa_presupuesto'] ?? null,
723                'status_by_g3w' => $nameStatus,
724                'type_by_g3w' => $budget['presupuesto']['origen_presupuesto'] ?? null,
725                'user_create_by_g3w' => $budget['presupuesto']['usuario'],
726                'user_commercial_by_g3w' => ! empty($budget['presupuesto']['cod_comercial_presupuesto']) ? $budget['presupuesto']['cod_comercial_presupuesto'] : null,
727                'g3w_warning' => $g3wWarning,
728                'g3w_warning_fields' => $g3wWarningFields,
729                'reason_for_not_following_up_id' => $reasonForNotFollowingUp,
730                'for_add' => 0,
731                'for_approval' => $forApproval,
732            ];
733
734            TblQuotations::where('id', $g3wNewId)->update($g3wArray);
735
736            $this->quotationsController->addUpdateLog($g3wNewId, 'G3W', null, null, null, 2);
737
738            Log::channel('allInfoQuotationsG3w')->info('Pasamos el insert.');
739
740            $companyNameFormatted = '';
741
742            if (! empty($companyName)) {
743                $companyNameFormatted = str_replace(' ', '_', $companyName);
744            }
745
746            $nameDocument = $companyNameFormatted
747                ? $budget['presupuesto']['cod_presupuesto'].'_'.$companyNameFormatted
748                : $budget['presupuesto']['cod_presupuesto'];
749
750            Log::channel('allInfoQuotationsG3w')->info('Nombre del documento: '.$nameDocument);
751
752            $responseSaveDocument = $this->saveDocument($budget['presupuesto']['documento'], $nameDocument, $g3wNewId, $newQuoteId, 'G3W');
753            $responseDataSaveDocument = $responseSaveDocument->getData();
754
755            if (! isset($responseDataSaveDocument->success) || ! $responseDataSaveDocument->success) {
756                Log::channel('allInfoQuotationsG3w')->info("No se pudo guardar el documento del presupuesto {$id} al crearlo. Error: ".($responseDataSaveDocument->error ?? 'Desconocido'));
757                throw new \Exception("No se pudo guardar el documento del presupuesto {$id} al crearlo. Error: ".($responseDataSaveDocument->error ?? 'Desconocido'));
758            }
759
760            /** @phpstan-ignore-next-line */
761            $documentName = $responseDataSaveDocument->documentName;
762
763            // FIRE-1025: zero-date sweep moved to the daily
764            // `quotations:cleanup-zero-dates` artisan command. Pre-fix this
765            // UPDATE ran at the end of every G3W sync cycle (5 call sites
766            // here) and deadlocked on `idx_acceptance_date` when concurrent
767            // syncs piled up (see InnoDB MONITOR OUTPUT on 2026-04-27 with
768            // multiple "WE ROLL BACK TRANSACTION (1)" entries on this exact
769            // statement). FIRE-1001 normalises `aceptacion` on write so no
770            // new zero-dates can be created — the daily cleanup handles
771            // legacy rows.
772
773            return [
774                'success' => true,
775                'id' => $g3wNewId,
776            ];
777
778        } catch (\Exception $e) {
779            $errorMessage = $e->getMessage();
780
781            return ['success' => false, 'error' => "Error sincronizando el presupuesto {$id}: ".$errorMessage];
782        }
783    }
784
785    /**
786     * @return array
787     */
788    public function syncModifiedBudgetById(string $id, $region = null): array{
789        try {
790            Log::channel('allInfoQuotationsG3w')->info('Modificando presupuesto: '.$id);
791            $statusID = null;
792            $materialId = [
793                'Cataluña' => [102561, 102562, 102568, 102569],
794                'Madrid' => [562, 100026, 100027],
795                'Comunidad Valenciana' => [562],
796            ];
797
798            $statusToChange = [
799                'Enviado',
800                'Aceptado',
801                'Rechazado',
802            ];
803
804            $g3wWarning = 0;
805            $g3wWarningFields = null;
806            $companyId = TblCompanies::where('region', $region)->first()->company_id;
807
808            $budget = $this->request('get', "presupuesto/{$id}", $region, []);
809
810            $existsQuery = TblQuotations::where('internal_quote_id', $budget['presupuesto']['cod_presupuesto']);
811            if (in_array($companyId, [18, 22])) {
812                $existsQuery->whereIn('company_id', [18, 22]);
813            } else {
814                $existsQuery->where('company_id', $companyId);
815            }
816            if (! $existsQuery->exists()) {
817                Log::channel('allInfoQuotationsG3w')->info("No se encuentra el presupuesto con ID en G3W $id. Es posible que sea debido a que se creo antes de la integracion de FST con G3W.");
818                throw new \Exception("No se encuentra el presupuesto con ID en G3W $id. Es posible que sea debido a que se creo antes de la integracion de FST con G3W.");
819            }
820
821            $company = $this->request('get', "servicio/{$budget['presupuesto']['cod_servicio']}", $region, []);
822
823            $companyName = $company['servicio']['nombre_servicio'] ?? $company['cliente']['empresa'] ?? null;
824
825            // $company = $this->request('get', "servicio/{$budget["presupuesto"]["cod_servicio"]}", $region, []);
826
827            /*if(!$company || !isset($company['servicio'])){
828                $company = $this->request('get', "cliente/{$budget["presupuesto"]["cod_cliente"]}", $region, []);
829            }*/
830
831            // $companyName = (isset($company['servicio'])) ? $company["servicio"]["nombre_servicio"] : $company["cliente"]["empresa"];
832            // $companyTelephone = (isset($company['servicio'])) ? $company["servicio"]["telefono"] : $company["cliente"]["telefono"] ;
833
834            // $segmentID = $this->normalizeSegment($company["servicio"]["tipo_servicio"]?? "Otro");
835
836            if (! isset($budget['presupuesto']['documento']) || ! $budget['presupuesto']['documento']) {
837                Log::channel('allInfoQuotationsG3w')->info('El presupuesto no tiene documento asociado. Creelo y vuelva a intentarlo.');
838                throw new \Exception('El presupuesto no tiene documento asociado. Creelo y vuelva a intentarlo.');
839            }
840
841            $typeId = $this->normalizeType($budget['presupuesto']['origen_presupuesto'], $region);
842
843            Log::channel('allInfoQuotationsG3w')->info('Tipo de presupuesto: '.$typeId);
844
845            $statusList = $this->request('get', 'presupuesto/tiposestado', $region, []);
846
847            $collection = collect($statusList);
848
849            $nameStatus = $collection->firstWhere('ID', $budget['presupuesto']['estado'])['nombre'] ?? null;
850
851            Log::channel('allInfoQuotationsG3w')->info('Estado de presupuesto: '.$nameStatus);
852
853            if (! $nameStatus) {
854                $statusID = TblBudgetStatus::where('name', 'Sin estado en G3W')->first()->budget_status_id;
855            } else {
856                $statusID = $this->normalizeStatus($nameStatus);
857
858                if ($statusID === TblBudgetStatus::where('name', 'Sin estado en G3W')->first()->budget_status_id) {
859                    $statusID = TblBudgetStatus::where('name', 'Estado no reconocido en FST')->first()->budget_status_id;
860                }
861            }
862
863            Log::channel('allInfoQuotationsG3w')->info('ID de estado: '.$statusID);
864
865            $rowQuery = TblQuotations::where('internal_quote_id', $budget['presupuesto']['cod_presupuesto']);
866            if (in_array($companyId, [18, 22])) {
867                $rowQuery->whereIn('company_id', [18, 22]);
868            } else {
869                $rowQuery->where('company_id', $companyId);
870            }
871            $row = $rowQuery->first();
872
873            if ($row) {
874                // FIRE-864 (c): G3W says "Enviado" (2) but Titan has no record
875                // of sending (no x_message_id). Decision by email validity:
876                //   - email valid    → keep "Enviado" (2). We can't verify
877                //                      Titan-side, but the address is plausible.
878                //   - email invalid  → "Correo erroneo" (22). G3W claims sent
879                //                      but the address can't actually receive.
880                // If Titan HAS sent it (x_message_id set), leave 2 — the
881                // SendGrid webhook/backfill will flip to 22 on bounce anyway.
882                if ($statusID === 2 && empty($row->x_message_id) && $this->checkEmailInvalid((string) $row->email)) {
883                    Log::channel('allInfoQuotationsG3w')->info("FIRE-864: G3W Enviado en edición sin envío Titan y email inválido (quote {$row->id}, email {$row->email}) → Correo erroneo (22).");
884                    $statusID = 22;
885                }
886
887                if (
888                    in_array($row->budget_status_id, [13, 14]) ||
889                    !$row->source_id ||
890                    (!$typeId || $typeId == 0 || $typeId == 16) ||
891                    empty(trim((string) $row->client)) || // change for $companyName when we implement
892                    empty(trim((string) $row->email))
893                    // || $this->checkEmailInvalid($row->email)
894                ) {
895                    $g3wWarning = 1;
896                }
897
898                $g3wWarningFields = $this->checkRequiredFields($row);
899
900                if ($g3wWarningFields != null && $g3wWarningFields != '') {
901                    $g3wWarning = 1;
902                }
903
904                // FIRE-1001 + FIRE-1024: G3W is the source of truth for the
905                // acceptance date on G3W-imported quotations. The original
906                // FIRE-1001 logic preserved Titan's previous value when G3W
907                // came back empty ("Titan remembers what G3W forgot"), but
908                // Berta filed FIRE-1024 because that hides intentional
909                // un-acceptances done in G3W: the user clears `aceptacion`
910                // upstream and Titan keeps showing the old date.
911                //
912                // Reversal: always mirror G3W. If G3W is empty, Titan goes
913                // null. If status moved away from Aceptado, accepted_at also
914                // goes null (the row is no longer accepted, so the audit
915                // metadata shouldn't claim otherwise).
916                //
917                // The audit log entries at addUpdateLog() below (and the
918                // FIRE-982 audit infra) capture the before/after, so any
919                // unintended clearing is recoverable.
920                $g3wAcceptanceDate = $this->normalizeG3wAcceptance($budget['presupuesto']['aceptacion'] ?? null);
921                $newAcceptanceDate = $g3wAcceptanceDate;
922                $newAcceptedAt = ($nameStatus === 'Aceptado') ? $g3wAcceptanceDate : null;
923
924                Log::channel('allInfoQuotationsG3w')->info('G3W Warning: '.$g3wWarning);
925
926                if ($row->budget_type_id !== $typeId) {
927                    $this->quotationsController->addUpdateLog($row->id, 'G3W', 'budget_type_id', $row->budget_type_id, $typeId, 4);
928                }
929
930                if (in_array($nameStatus, $statusToChange) || $row->budget_status_id == 6) {
931                    $this->quotationsController->addUpdateLog($row->id, 'G3W', 'budget_status_id', $row->budget_status_id, $statusID, 4);
932                }
933
934                if ($row->amount !== $budget['presupuesto']['importe']) {
935                    $this->quotationsController->addUpdateLog($row->id, 'G3W', 'amount', $row->amount, $budget['presupuesto']['importe'], 4);
936                }
937
938                if ($row->updated_by !== 'System') {
939                    $this->quotationsController->addUpdateLog($row->id, 'G3W', 'updated_by', $row->updated_by, 'G3W', 4);
940                }
941
942                if ($row->has_attachment !== 1) {
943                    $this->quotationsController->addUpdateLog($row->id, 'G3W', 'has_attachment', $row->has_attachment, 1, 4);
944                }
945
946                if ($row->sync_import_edited !== 1) {
947                    $this->quotationsController->addUpdateLog($row->id, 'G3W', 'sync_import_edited', $row->sync_import_edited, 1, 4);
948                }
949
950                if ($nameStatus === 'Aceptado') {
951                    $this->quotationsController->addUpdateLog($row->id, 'G3W', null, $row->acceptance_date, $newAcceptanceDate, 4);
952                    $this->quotationsController->addUpdateLog($row->id, 'G3W', null, $row->accepted_at, $newAcceptedAt, 4);
953                    $this->quotationsController->addUpdateLog($row->id, 'G3W', null, $row->accepted_by, 'G3W', 4);
954                }
955
956                Log::channel('allInfoQuotationsG3w')->info('Pasamos el add update log');
957
958                // FIRE-1001: classify each G3W line as labor or material.
959                // Logic lives in a static helper so it can be unit-tested
960                // without spinning up the full sync. See
961                // `classifyLineAsLabor` below for the rationale.
962                $totalCostOfMaterial = 0;
963                $totalLabor = 0;
964                foreach ($budget['presupuesto']['lineas'] as $linea) {
965                    $precioCompra = (float) ($linea['precio_compra'] ?? 0);
966                    $unidades = (float) ($linea['unidades'] ?? 0);
967
968                    if (self::classifyLineAsLabor($linea, $materialId[$region] ?? [])) {
969                        $totalLabor += $precioCompra * $unidades;
970                    } else {
971                        $totalCostOfMaterial += $precioCompra * $unidades;
972                    }
973                }
974
975                $companyInfo = TblCompanies::where('region', $region)->first();
976
977                $hoursPerDay = (float) ($companyInfo->hours_per_worker_per_day ?? 0);
978                $costOfHour = (float) ($companyInfo->cost_of_hour ?? 0);
979                $dailyRate = $hoursPerDay * $costOfHour;
980                $numberOfDays = $dailyRate > 0 ? $totalLabor / $dailyRate : 0;
981
982                // FIRE-1001: don't clobber user-edited people/days on every
983                // re-sync. Sync seeds defaults only when the row hasn't been
984                // configured yet — once Berta has set "2 técnicos × 20 días"
985                // we keep them. (`calculateBudgetMargin` then derives
986                // `cost_of_labor` from those preserved values, and the
987                // frontend recompute on save stays consistent.)
988                $peopleAssigned = (int) ($row->people_assigned_to_the_job ?? 0) > 0
989                    ? (int) $row->people_assigned_to_the_job
990                    : 1;
991                $durationInDays = (float) ($row->duration_of_job_in_days ?? 0) > 0
992                    ? (float) $row->duration_of_job_in_days
993                    : $numberOfDays;
994
995                $resultMargin = $this->calculateBudgetMargin(
996                    $budget['presupuesto']['importe'] ?? 0,
997                    $row->commission_pct ?? 0,
998                    $peopleAssigned,
999                    $durationInDays,
1000                    $totalCostOfMaterial,
1001                    $hoursPerDay,
1002                    $costOfHour,
1003                    $row->segment_id ?? 0,
1004                    $companyInfo->general_costs ?? 0
1005                );
1006
1007                Log::channel('allInfoQuotationsG3w')->info('Pasamos el calculo de margen.');
1008
1009                $forApproval = $row->for_approval;
1010
1011                $isWorkAccepted = false;
1012
1013                try {
1014                    $work = $this->request('get', "presupuesto/trabajos/{$id}", $region, []);
1015
1016                    if (! empty($work)) {
1017                        $dataToSend = [
1018                            'ids' => array_column($work, 'ID'),
1019                        ];
1020
1021                        $worksStatus = $this->request('post', 'trabajo/estados', $region, $dataToSend);
1022
1023                        if (is_array($worksStatus)) {
1024                            foreach ($worksStatus as $item) {
1025                                if (($item['estado'] ?? null) === 'Aceptado') {
1026                                    $isWorkAccepted = true;
1027                                    break;
1028                                }
1029                            }
1030                        }
1031                    }
1032                } catch (\Exception $worksErr) {
1033                    // G3W's trabajo/estados endpoint is known to return raw SQL
1034                    // in the error body for some quotes (their internal query
1035                    // is malformed). Treat as "no accepted works" and continue
1036                    // the sync so the quotation still updates.
1037                    Log::channel('allInfoQuotationsG3w')->warning(
1038                        "G3W trabajos/estados falló para presupuesto {$id} (region {$region}). Se continúa con isWorkAccepted=false. Error: ".$worksErr->getMessage()
1039                    );
1040                }
1041
1042                if (
1043                    ($row->budget_type_id !== $typeId ||
1044                    // $row->customer_type_id !== $customerTypeId ||
1045                    $row->amount !== $budget['presupuesto']['importe']) &&
1046                    ! $isWorkAccepted
1047                ) {
1048                    // Commercial lookup tolerates a missing user (G3W name not in
1049                    // tbl_users). checkAproval handles a null commercialId by
1050                    // skipping the approval notification, not crashing the sync.
1051                    $comercialUser = TblUsers::where('name', $row->commercial)->first();
1052                    if (! $comercialUser) {
1053                        $mapping = TblUserG3wMapping::where('name_g3w', $row->commercial)->first();
1054                        if ($mapping && $mapping->id_fst) {
1055                            $comercialUser = TblUsers::where('id', $mapping->id_fst)->first();
1056                        }
1057                    }
1058                    $forApproval = self::checkAproval($companyId, $typeId, $row->customer_type_id, $budget['presupuesto']['importe'], $row->quote_id, $row->id, $comercialUser?->id, $row->commercial);
1059                }
1060
1061                $row->update(
1062                    [
1063                        //'segment_id' => $segmentID,
1064                        'budget_type_id' => $typeId == 16 ? null : $typeId,
1065                        // 'client' => $companyName?? null,
1066                        // 'phone_number' => $companyTelephone?? null,
1067                        'budget_status_id' => (in_array($nameStatus, $statusToChange) || $row->budget_status_id == 6) ? $statusID : $row->budget_status_id,
1068                        'acceptance_date' => $newAcceptanceDate,
1069                        'accepted_at' => $newAcceptedAt,
1070                        // FIRE-1024: don't claim "System" accepted the quote
1071                        // when there's no real acceptance date. If status
1072                        // moved away from Aceptado, also clear accepted_by.
1073                        'accepted_by' => ($nameStatus === 'Aceptado' && $newAcceptanceDate !== null) ? 'System' : null,
1074                        'amount' => $budget['presupuesto']['importe'] ?? null,
1075                        'updated_by' => 'System',
1076                        'updated_at' => Carbon::now()->format('Y-m-d H:i:s'),
1077                        'has_attachment' => 1,
1078                        'sync_import_edited' => 1,
1079                        'segment_by_g3w' => $company['servicio']['tipo_servicio'] ?? null,
1080                        'source_by_g3w' => $budget['presupuesto']['cod_empresa_presupuesto'] ?? null,
1081                        'status_by_g3w' => $nameStatus,
1082                        'type_by_g3w' => $budget['presupuesto']['origen_presupuesto'] ?? null,
1083                        'user_create_by_g3w' => $budget['presupuesto']['usuario'] ?? null,
1084                        'user_commercial_by_g3w' => ! empty($budget['presupuesto']['cod_comercial_presupuesto']) ? $budget['presupuesto']['cod_comercial_presupuesto'] : null,
1085                        'g3w_warning' => $g3wWarning,
1086                        'g3w_warning_fields' => $g3wWarningFields,
1087                        'cost_of_labor' => $resultMargin['cost_of_labor'],
1088                        'total_cost_of_job' => $resultMargin['total_cost_of_job'],
1089                        'invoice_margin' => $resultMargin['invoice_margin'],
1090                        'margin_for_the_company' => $resultMargin['margin_for_the_company'],
1091                        'margin_on_invoice_per_day_per_worker' => $resultMargin['margin_on_invoice_per_day_per_worker'],
1092                        'commission_cost' => $resultMargin['commission_cost'],
1093                        'revenue_per_date_per_worked' => $resultMargin['revenue_per_date_per_worked'],
1094                        'gross_margin' => $resultMargin['gross_margin'],
1095                        'labor_percentage' => $resultMargin['labor_percentage'],
1096                        'estimated_cost_of_materials' => $totalCostOfMaterial,
1097                        'people_assigned_to_the_job' => $peopleAssigned,
1098                        'duration_of_job_in_days' => $durationInDays,
1099                        'for_approval' => $forApproval,
1100                    ]
1101                );
1102            }
1103
1104            Log::channel('allInfoQuotationsG3w')->info('Pasamos el update');
1105
1106            $companyNameFormatted = '';
1107
1108            if (! empty($companyName)) {
1109                $companyNameFormatted = str_replace(' ', '_', $companyName);
1110            }
1111
1112            $nameDocument = $companyNameFormatted
1113                ? $budget['presupuesto']['cod_presupuesto'].'_'.$companyNameFormatted
1114                : $budget['presupuesto']['cod_presupuesto'];
1115
1116            Log::channel('allInfoQuotationsG3w')->info('Nombre del documento: '.$nameDocument);
1117
1118            if (! $row) {
1119                Log::channel('allInfoQuotationsG3w')->info('No se encontró la cotización con internal_quote_id: '.$budget['presupuesto']['cod_presupuesto']);
1120                throw new \Exception("No se encontró la cotización con internal_quote_id: {$budget['presupuesto']['cod_presupuesto']}");
1121            }
1122
1123            $response = $this->saveDocument($budget['presupuesto']['documento'], $nameDocument, $row->id, $row->quote_id, 'G3W');
1124            $responseData = $response->getData();
1125
1126            Log::info('Response data: '.json_encode($responseData));
1127
1128            if (! isset($responseData->success) || ! $responseData->success) {
1129                Log::channel('allInfoQuotationsG3w')->info("No se pudo guardar el documento del presupuesto {$id} al editar. Error: ".($responseData->error ?? 'Desconocido'));
1130                throw new \Exception("No se pudo guardar el documento del presupuesto {$id} al editar. Error: ".($responseData->error ?? 'Desconocido'));
1131            }
1132
1133            $work = $this->request('get', "presupuesto/trabajos/{$id}", $region, []);
1134
1135            if (! empty($work) && isset($work[0]['ID'])) {
1136                $workId = $work[0]['ID'];
1137                $albaran = $this->request('get', "albaran/{$workId}", $region, []);
1138
1139                if (isset($albaran['albaran']) && isset($albaran['albaran']['certificado'])) {
1140                    $certificado = $albaran['albaran']['certificado'];
1141                    $nameCertificado = $id.'_certificado';
1142
1143                    $response = $this->saveDocument($certificado, $nameCertificado, $row->id, $row->quote_id, 'G3W');
1144                    $responseData = $response->getData();
1145
1146                    if (! isset($responseData->success) || ! $responseData->success) {
1147                        Log::channel('allInfoQuotationsG3w')->info("No se pudo guardar el certificado del presupuesto {$id} al editar. Error: ".($responseData->error ?? 'Desconocido'));
1148                        throw new \Exception("No se pudo guardar el certificado del presupuesto {$id} al editar. Error: ".($responseData->error ?? 'Desconocido'));
1149                    }
1150
1151                }
1152            }
1153
1154            Log::channel('allInfoQuotationsG3w')->info('Pasamos la subida de albaran.');
1155
1156            // FIRE-1025: zero-date sweep moved to the daily
1157            // `quotations:cleanup-zero-dates` artisan command. Pre-fix this
1158            // UPDATE ran at the end of every G3W sync cycle (5 call sites
1159            // here) and deadlocked on `idx_acceptance_date` when concurrent
1160            // syncs piled up (see InnoDB MONITOR OUTPUT on 2026-04-27 with
1161            // multiple "WE ROLL BACK TRANSACTION (1)" entries on this exact
1162            // statement). FIRE-1001 normalises `aceptacion` on write so no
1163            // new zero-dates can be created — the daily cleanup handles
1164            // legacy rows.
1165
1166            return [
1167                'success' => true,
1168            ];
1169
1170        } catch (QueryException $e) {
1171            // FIRE-1029 (quote 160286 / 17939, 26-27/04): let DB-layer
1172            // failures bubble up to the caller's `DB::transaction(...)`
1173            // wrapper so the whole sync — including the `addUpdateLog`
1174            // INSERTs earlier in this method — rolls back atomically.
1175            //
1176            // Pre-fix this catch swallowed every QueryException and
1177            // returned `success: false`. From DB::transaction's view the
1178            // closure returned normally, so it issued COMMIT instead of
1179            // ROLLBACK, which during the FIRE-1025 deadlock storm
1180            // produced 100+ ghost change-log rows on quote 17939 while
1181            // `$row->update([...])` never landed (row's `updated_at`
1182            // stayed frozen at 2026-04-14 throughout). syncByDate also
1183            // saw `success: false` and routed the error correctly into
1184            // tbl_g3w_orders_update_logs — but only when the txn was
1185            // already dead, so the error trace was incomplete.
1186            //
1187            // Logical errors (G3W returned no documento, status not in
1188            // map, etc.) still throw plain `\Exception` and continue to
1189            // be returned gracefully via the catch below.
1190            throw $e;
1191        } catch (\Exception $e) {
1192            return ['success' => false, 'error' => "Error actualizando el presupuesto {$id}: ".$e->getMessage()];
1193        }
1194    }
1195
1196    /**
1197     * FIRE-1001: classify a G3W presupuesto line as labor (true) or
1198     * material (false). The original sync code only checked the
1199     * per-region `cod_material` allow-list (`$materialId` in
1200     * `syncModifiedBudgetById`). That list is incomplete — it only
1201     * covers Cataluña / Madrid / Comunidad Valenciana, and even there
1202     * it doesn't enumerate every labor code G3W actually emits. Labor
1203     * lines that fell through were summed into materials, and when the
1204     * frontend later recomputed `cost_of_labor = people × days × hours
1205     * × cost`, the labor hiding inside materials got added a second
1206     * time → "se duplica la mano de obra".
1207     *
1208     * Description-based detection covers the gap: any line whose
1209     * descripcion mentions "mano de obra" / "horas de trabajo" /
1210     * "horas técnico|oficial|peón" is treated as labor regardless of
1211     * its `cod_material`.
1212     *
1213     * @param  array<string,mixed>  $linea       Single line from $budget['presupuesto']['lineas']
1214     * @param  list<int|string>     $laborCodes  Per-region cod_material allow-list (may be empty)
1215     */
1216    public static function classifyLineAsLabor(array $linea, array $laborCodes): bool
1217    {
1218        $code = $linea['cod_material'] ?? null;
1219        if ($code !== null && in_array($code, $laborCodes, true)) {
1220            return true;
1221        }
1222        $desc = mb_strtolower((string) ($linea['descripcion'] ?? ''));
1223        if ($desc === '') {
1224            return false;
1225        }
1226        return str_contains($desc, 'mano de obra')
1227            || str_contains($desc, 'mano obra')
1228            || preg_match('/\bhoras?\s+(de\s+)?trabajo\b/u', $desc) === 1
1229            || preg_match('/\bhoras?\s+(de\s+)?(t[eé]cnico|oficial|peon|peón)\b/u', $desc) === 1;
1230    }
1231
1232    /**
1233     * @return float[]|int[]|null[]
1234     */
1235    function calculateBudgetMargin($amount, $commission_pct, $people_assigned_to_the_job, $duration_of_job_in_days, $estimated_cost_of_materials, $hours_per_worker_per_day, $cost_of_hour, $segmentId, $generalCosts): array {
1236        $results = [
1237            'cost_of_labor' => 0,
1238            'total_cost_of_job' => 0,
1239            'invoice_margin' => null,
1240            'margin_for_the_company' => null,
1241            'margin_on_invoice_per_day_per_worker' => null,
1242            'commission_cost' => null,
1243            'revenue_per_date_per_worked' => 0,
1244            'gross_margin' => 0,
1245            'labor_percentage' => 0,
1246        ];
1247
1248        $parseFloat = function($value): int|float {
1249            if ($value === null || $value === '') return 0;
1250            $cleanValue = str_replace(',', '.', (string)$value);
1251            return is_numeric($cleanValue) ? (float)$cleanValue : 0;
1252        };
1253
1254        $amount = $parseFloat($amount ?? 0);
1255        $commissionPct = $parseFloat($commission_pct ?? 0);
1256        $peopleAssigned = $parseFloat($people_assigned_to_the_job ?? 0);
1257        $durationInDays = $parseFloat($duration_of_job_in_days ?? 0);
1258        $estimatedMaterials = $parseFloat($estimated_cost_of_materials ?? 0);
1259
1260        if ($amount >= 0 && $peopleAssigned >= 0 && $durationInDays >= 0) {
1261
1262            $costOfLabor = $durationInDays * $peopleAssigned * ($hours_per_worker_per_day ?? 0) * ($cost_of_hour ?? 0);
1263
1264            $totalCostOfJob = $costOfLabor + $estimatedMaterials;
1265
1266            if ($commissionPct > 0 && $amount > 0) {
1267                $commissionCost = ($commissionPct / 100) * $amount;
1268            } else {
1269                $commissionCost = 0;
1270            }
1271
1272            if ($totalCostOfJob > 0 && $amount > 0) {
1273                if ($segmentId == 7) {
1274                    $invoiceMargin = (($amount - $totalCostOfJob - $commissionCost) / $amount) * 100;
1275                } else {
1276                    $invoiceMargin = (($amount - $totalCostOfJob) / $amount) * 100;
1277                }
1278
1279                $marginForTheCompany = $invoiceMargin - $generalCosts;
1280
1281                $marginOnInvoicePerDayPerWorker = ($amount - $estimatedMaterials - $costOfLabor) /
1282                                                ($durationInDays ?: 1) /
1283                                                ($peopleAssigned ?: 1);
1284            } else {
1285                $invoiceMargin = 0;
1286                $marginForTheCompany = 0;
1287                $marginOnInvoicePerDayPerWorker = 0;
1288            }
1289
1290            if ($costOfLabor == 0) {
1291                $revenuePerDayWorked = 0;
1292                $laborPercentage = 0;
1293            } else {
1294                $revenuePerDayWorked = ($amount / ($peopleAssigned ?: 1) / ($durationInDays ?: 1));
1295                $laborPercentage = ($costOfLabor / $amount) * 100;
1296            }
1297
1298            $grossMargin = $amount > 0 ? (($amount - $estimatedMaterials) / $amount) * 100 : 0;
1299
1300            $results['cost_of_labor'] = $costOfLabor;
1301            $results['total_cost_of_job'] = $totalCostOfJob;
1302            $results['invoice_margin'] = $invoiceMargin;
1303            $results['margin_for_the_company'] = $marginForTheCompany;
1304            $results['margin_on_invoice_per_day_per_worker'] = $marginOnInvoicePerDayPerWorker;
1305            $results['commission_cost'] = $commissionCost;
1306            $results['revenue_per_date_per_worked'] = $revenuePerDayWorked;
1307            $results['gross_margin'] = $grossMargin;
1308            $results['labor_percentage'] = $laborPercentage;
1309
1310        } else {
1311            foreach ($results as $key => $val) {
1312                $results[$key] = null;
1313            }
1314        }
1315
1316        return $results;
1317    }
1318
1319    /**
1320     * Synchronize budgets that gave us an error.
1321     *
1322     * @param string $name Who's launch the function
1323     */
1324    public function syncErrorBudgets($name = null, $region = null): array
1325    {
1326        try {
1327            if ($region === 'Catalunya') {
1328                $region = 'Cataluña';
1329            }
1330
1331            $g3wActive = TblCompanies::where('region', $region)->first()->g3W_active;
1332
1333            if (! $g3wActive) {
1334                throw new Exception("La sincronización con G3W debe estar desactivada en la region '$region'.");
1335            }
1336
1337            $this->setSyncStatus(1, $region);
1338
1339            $successfulSyncs = 0;
1340            $failedSyncs = [];
1341            $successIdSyncs = [];
1342
1343            $startCronDateTime = date('Y-m-d H:i:s');
1344
1345            $company = TblCompanies::where('region', $region)->first();
1346
1347            if (! $company) {
1348                throw new \Exception('No company found for region: '.$region);
1349            }
1350            $company_id = $company->company_id;
1351            $logs = TblG3WOrdersUpdateLogs::whereNotNull('sync_error_ids')
1352                ->where('company_id', $company_id)
1353                ->get(['sync_error_ids']);
1354
1355            $allSyncErrorIds = [];
1356
1357            foreach ($logs as $log) {
1358                if (is_string($log->sync_error_ids)) {
1359                    $decodedIds = json_decode($log->sync_error_ids, true);
1360                    if (is_array($decodedIds)) {
1361                        $log->sync_error_ids = json_encode($decodedIds); // Fixed: Encode array back to JSON string
1362                        $allSyncErrorIds = array_merge($allSyncErrorIds, $decodedIds); // Fixed: Merge the decoded array
1363                    }
1364                }
1365            }
1366
1367            $allSyncErrorIds = array_unique($allSyncErrorIds);
1368
1369            foreach ($allSyncErrorIds as $idSyncError) {
1370                $result = DB::transaction(fn() => $this->syncById($idSyncError, $region));
1371
1372                if ($result['success']) {
1373                    $successfulSyncs++;
1374                    $successIdSyncs[] = [
1375                        'id' => $idSyncError,
1376                    ];
1377                } else {
1378                    if (!str_contains((string) $result['error'], 'No se ha encontrado el presupuesto')) {
1379                        $failedSyncs[] = [
1380                            'id' => $idSyncError,
1381                            'error' => $result['error'] ?? 'Unknown error',
1382                        ];
1383                    }
1384                }
1385            }
1386
1387            TblG3wLastUpdate::where('region', $region)->first()->update(['updatingNow' => 0]);
1388
1389            $company = TblCompanies::where('region', $region)->first();
1390            if (! $company) {
1391                throw new \Exception('No company found for region: '.$region);
1392            }
1393            $company_id = $company->company_id;
1394
1395            $logs = TblG3WOrdersUpdateLogs::whereNotNull('sync_error_ids')
1396                ->where('company_id', $company_id)
1397                ->update(['sync_error_ids' => []]);
1398
1399            $this->updateLogs($failedSyncs, $successfulSyncs, $successIdSyncs, $startCronDateTime, $name, $region);
1400
1401            return [
1402                'success' => true,
1403                'message' => 'Synchronization of failed budgets completed.',
1404            ];
1405
1406        } catch (\Exception $e) {
1407            Log::channel('g3w')->error('Error when synchronizing error budgets: '.$e->getMessage());
1408
1409            if (TblG3wLastUpdate::where('region', $region)->first()->updatingNow === 1) {
1410                TblG3wLastUpdate::where('region', $region)->first()->update(['updatingNow' => 0]);
1411            }
1412
1413            return ['success' => false, 'error' => $e->getMessage()];
1414        }
1415    }
1416
1417    public function syncBudgetsWorks($name = null, $region = null): array{
1418        try {
1419            if ($region === 'Catalunya') {
1420                $region = 'Cataluña';
1421            }
1422
1423            $company = TblCompanies::where('region', $region)->first();
1424
1425            if (! $company) {
1426                throw new \Exception("No se encontró la compañía para la región '$region'.");
1427            }
1428            $g3wActive = $company->g3W_active;
1429
1430            if (! $g3wActive) {
1431                throw new Exception("La sincronización con G3W debe estar desactivada en la region '$region'.");
1432            }
1433
1434            $this->workSevice->getG3wTasksExecuted($region, 1);
1435
1436            $this->setSyncStatus(1, $region);
1437
1438            $successfulSyncs = 0;
1439            $failedSyncs = [];
1440            $successIdSyncs = [];
1441
1442            $startCronDateTime = date('Y-m-d H:i:s');
1443
1444            $quotesIds = TblQuotations::where(function ($query): void {
1445                $query->where('sync_import', 1)
1446                    ->orWhere('sync_import_edited', 1);
1447            })
1448                ->where('budget_type_id', 1)
1449                ->where(function ($query): void {
1450                    $query->whereNull('box_work_g3w')
1451                        ->orWhere('box_work_g3w', '0');
1452                })
1453                ->get();
1454
1455            foreach ($quotesIds as $quoteId) {
1456                $work = $this->request('get', "presupuesto/trabajos/{$quoteId->internal_quote_id}", $region, []);
1457
1458                sleep(2);
1459
1460                $workIds = [];
1461
1462                if (is_array($work)) {
1463                    foreach ($work as $item) {
1464                        if (isset($item['ID'])) {
1465                            $workIds[] = $item['ID'];
1466                        }
1467                    }
1468                }
1469
1470                $idsConcatenados = implode('/', $workIds);
1471
1472                $wasUpdated = $quoteId->update(['box_work_g3w' => $idsConcatenados]);
1473
1474                if ($wasUpdated) {
1475                    $successfulSyncs++;
1476                    $successIdSyncs[] = [
1477                        'id' => $quoteId->internal_quote_id,
1478                    ];
1479                } else {
1480                    $failedSyncs[] = [
1481                        'id' => $quoteId->internal_quote_id,
1482                        'error' => "Error updating the internal quote id $quoteId, work $idsConcatenados.",
1483                    ];
1484
1485                }
1486            }
1487
1488            $g3wUpdate = TblG3wLastUpdate::where('region', $region)->first();
1489            if ($g3wUpdate) {
1490                $g3wUpdate->update(['updatingNow' => 0]);
1491            }
1492
1493            // FIRE-1025: zero-date sweep moved to the daily
1494            // `quotations:cleanup-zero-dates` artisan command. Pre-fix this
1495            // UPDATE ran at the end of every G3W sync cycle (5 call sites
1496            // here) and deadlocked on `idx_acceptance_date` when concurrent
1497            // syncs piled up (see InnoDB MONITOR OUTPUT on 2026-04-27 with
1498            // multiple "WE ROLL BACK TRANSACTION (1)" entries on this exact
1499            // statement). FIRE-1001 normalises `aceptacion` on write so no
1500            // new zero-dates can be created — the daily cleanup handles
1501            // legacy rows.
1502
1503            $this->updateLogs($failedSyncs, $successfulSyncs, $successIdSyncs, $startCronDateTime, $name, $region, 'Orders Works');
1504
1505            return [
1506                'success' => true,
1507                'message' => 'Synchronization of budgets works completed.',
1508            ];
1509
1510        } catch (\Exception $e) {
1511            Log::channel('g3w')->error('Error when synchronizing budgets works: '.$e->getMessage());
1512
1513            if (TblG3wLastUpdate::where('region', $region)->first()->updatingNow === 1) {
1514                TblG3wLastUpdate::where('region', $region)->first()->update(['updatingNow' => 0]);
1515            }
1516
1517            return ['success' => false, 'error' => $e->getMessage()];
1518        }
1519    }
1520
1521    /**
1522     * @param $failedSyncs
1523     */
1524    public function notifyErrors($failedSyncs): void
1525    {
1526        $errorDetails = array_map(fn(array $failure) => "Budget ID: {$failure['id']}, Error: {$failure['error']}", $failedSyncs);
1527        $message = implode("\n", $errorDetails);
1528        /*Mail::luis, rick & chris, tech@fire.es*/
1529
1530        Log::channel('g3w')->error('Error notification sent to ricardo.alemany@fire.es');
1531    }
1532
1533    /**
1534     * Function to generate the next QuoteID
1535     *
1536     * @return array{id: int, number: string}
1537     */
1538    /*public function generateQuoteId($companyId)
1539    {
1540        if ($companyId == 0) {
1541            $latestQuoteId = TblQuotations::orderBy('id', 'DESC')->first()->quote_id ?? null;
1542        } else {
1543            $latestQuoteId = TblQuotations::where('company_id', $companyId)
1544                ->orderBy('id', 'DESC')
1545                ->first()
1546                ->quote_id ?? null;
1547        }
1548
1549        if (!$latestQuoteId) {
1550            throw new \Exception("Error generando el # en titan");
1551        }
1552
1553        if (is_numeric($latestQuoteId)) {
1554            return (string)((int)$latestQuoteId + 1);
1555        }
1556
1557        preg_match('/([A-Z]+)(\d+)/', $latestQuoteId, $matches);
1558
1559        if (count($matches) < 3) {
1560            throw new \Exception("El formato del Ãºltimo Quote ID no es válido: {$latestQuoteId}");
1561        }
1562
1563        $prefix = $matches[1];
1564        $numericPart = $matches[2];
1565
1566        $incrementedNumber = str_pad((int)$numericPart + 1, strlen($numericPart), '0', STR_PAD_LEFT);
1567
1568        $newQuoteId = $prefix . $incrementedNumber;
1569
1570        return $newQuoteId;
1571    }*/
1572    public function generateQuoteId($companyId): array|ResponseFactory|Response
1573    {
1574        try {
1575
1576            $companyId = addslashes((string) $companyId);
1577            $latestBudget = [];
1578            $number = 0;
1579            $beforeLastId = null;
1580
1581            $x = true;
1582
1583            if ($companyId == 0) {
1584                $latestBudget = TblQuotations::orderByRaw('CAST(quote_id AS DOUBLE) DESC')->value('quote_id');
1585            } else {
1586                $latestBudget = TblCompanies::where('company_id', $companyId)->value('last_id');
1587
1588                if ($latestBudget == null) {
1589                    $latestBudget = TblQuotations::where('company_id', $companyId)->orderByRaw('id DESC')->value('quote_id');
1590                    $beforeLastId = $latestBudget;
1591                }
1592            }
1593
1594            $number = $latestBudget;
1595
1596            while ($x) {
1597
1598                if(is_numeric(substr((string) $number, -1))) {
1599                    $number++;
1600                } else {
1601                    $number .= '1';
1602                }
1603
1604                $check = 0;
1605
1606                if ($companyId == 0) {
1607                    $check = TblQuotations::where('quote_id', (string) $number)->count();
1608                } else {
1609                    $check = TblQuotations::where('company_id', $companyId)->where('quote_id', (string) $number)->count();
1610                }
1611
1612                if ($check == 0) {
1613                    $x = false;
1614                }
1615            }
1616
1617            $result = TblQuotations::create(['quote_id' => $number, 'company_id' => $companyId, 'for_add' => 1]);
1618
1619            if ($beforeLastId == null) {
1620                $beforeLastId = $number;
1621            }
1622
1623            $query = "UPDATE tbl_companies SET last_id = '{$number}', before_last_id = CASE WHEN before_last_id IS NULL THEN '{$beforeLastId}' ELSE before_last_id END WHERE company_id = {$companyId}";
1624            DB::select($query);
1625
1626            return [
1627                'id' => $result->id,
1628                'number' => $number
1629            ];
1630
1631        } catch (\Exception $e) {
1632            throw $e;
1633        }
1634    }
1635
1636    /**
1637     * Function to normalice the status provided by G3W.
1638     *
1639     * @param  $status  String Row status of G3W
1640     * @return int ID normalized in FST
1641     *
1642     * @throws \Exception
1643     */
1644    private function normalizeStatus($status)
1645    {
1646        if (! $status) {
1647            return TblBudgetStatus::where('name', 'Sin estado en G3W')->first()->budget_status_id;
1648        }
1649
1650        $statusMapping = TblStatusG3wMapping::where('name_g3w', $status)->first();
1651
1652        if (!$statusMapping) {
1653            TblStatusG3wMapping::create([
1654                "name_g3w" => $status,
1655                "budget_status_id" => 0
1656            ]);
1657            return TblBudgetStatus::where('name', "Estado no reconocido en FST")->first()->budget_status_id;
1658        }
1659
1660        return $statusMapping->budget_status_id;
1661
1662    }
1663
1664    /**
1665     * Function to normalice the segment provided by G3W.
1666     *
1667     * @param  $status  String Row status of G3W
1668     * @return int ID normalized in FST
1669     *
1670     * @throws \Exception
1671     */
1672    private function normalizeSegment($segment)
1673    {
1674        $segmentMapping = TblSegmentG3wMapping::where("name_g3w", $segment)->first();
1675
1676        if(!$segmentMapping){
1677            TblSegmentG3wMapping::create([
1678                "name_g3w" => $segment,
1679                "segment_id" => 0
1680            ]);
1681            return TblSegments::where('name', "Otro")->first()->segment_id?? 9;
1682        }
1683
1684        return $segmentMapping->segment_id;
1685    }
1686
1687    /**
1688     * Function to normalize the budget type provided by G3W.
1689     *
1690     * @param  int  $type  ID del tipo proporcionado por la API.
1691     * @param  string  $region  Región (Madrid o Cataluña).
1692     * @return int ID normalizado del presupuesto en el sistema.
1693     *
1694     * @throws \Exception
1695     */
1696    private function normalizeType($type, $region)
1697    {
1698        if ($region === 'Catalunya') {
1699            $region = 'Cataluña';
1700        }
1701
1702        $budgetTypeMapping = TblTypeG3wMapping::where('id_g3w', $type)
1703            ->where('region', $region)
1704            ->first();
1705
1706        if (!$budgetTypeMapping) {
1707            TblTypeG3wMapping::create([
1708                "id_g3w" => $type?? null,
1709                "budget_type_name" => null,
1710                "region" => $region
1711            ]);
1712            throw new \Exception("El estado '$type' no existe en la base de datos.");
1713        }
1714
1715        $budgetType = TblBudgetTypes::where('name', $budgetTypeMapping->budget_type_name)->first();
1716
1717        if (! $budgetType) {
1718            return 16;
1719        }
1720
1721        return $budgetType->budget_type_id;
1722    }
1723
1724    private function normalizeSource($id_call, $region)
1725    {
1726        $regionTxt = '';
1727        $region = $region == 'Catalunya' ? 'Cataluña' : $region;
1728
1729        if (! $id_call) {
1730            $sourceDefault = TblSources::where('name', 'G3W/Gestiona')->first();
1731
1732            return $sourceDefault->source_id ?? 20;
1733        }
1734
1735        $sourceMapping = TblSourceG3wMapping::where('id_g3w', $id_call)
1736            ->where('region', $region)
1737            ->first();
1738
1739        if(!$sourceMapping){
1740            TblSourceG3wMapping::create([
1741                "id_g3w" => $id_call,
1742                "source_name" => null,
1743                "region" => $region
1744            ]);
1745            return null;
1746        }
1747
1748        $sourceId = TblSources::where('name', $sourceMapping->source_name)->first();
1749
1750        if (! $sourceId && $region == 'Andalucía') {
1751            return 68;
1752        }
1753
1754        if (! $sourceId) {
1755            return null;
1756        }
1757
1758        return $sourceId->source_id;
1759
1760    }
1761
1762    /**
1763     * @param $document
1764     * @return JsonResponse
1765     */
1766    public function saveDocument($document, $nameDocument = null, $quotationId = null, $quoteId = null, $uploadedBy = null, $isInternal = null)
1767    {
1768
1769        try {
1770
1771            $binaryData = base64_decode((string) $document);
1772
1773            if (! $binaryData) {
1774                throw new \Exception('Los datos Base64 no son válidos.');
1775            }
1776
1777            $documentName = $nameDocument
1778                ? preg_replace('/[^A-Za-z0-9_\-.]/', '', (string) $nameDocument)
1779                : 'document_' . time() . '.pdf';
1780
1781            if (! preg_match('/\.pdf$/i', $documentName)) {
1782                $documentName .= '.pdf';
1783            }
1784
1785            $filename = pathinfo($documentName, PATHINFO_FILENAME).'_'.time().'.pdf';
1786
1787            $fileSize = strlen($binaryData);
1788            $mimeType = 'application/pdf';
1789
1790            $fileDataBase = TblFiles::where('quotation_id', $quotationId)->get();
1791
1792            if ($fileDataBase->isNotEmpty()) {
1793                foreach ($fileDataBase as $fileData) {
1794                    if ($fileData->original_name == $documentName) {
1795                        $fileData->delete();
1796                    }
1797                }
1798            }
1799
1800            $s3path = Storage::disk('s3')->put(
1801                'uploads/'.$filename,
1802                $binaryData,
1803                [
1804                    'ContentType' => $mimeType,
1805                ]
1806            );
1807
1808            $file = TblFiles::create([
1809                'quotation_id' => $quotationId,
1810                'original_name' => $documentName,
1811                'filename' => $filename,
1812                'uploaded_by' => $uploadedBy,
1813                'file_size' => $fileSize,
1814                'mime_type' => $mimeType,
1815                'uploaded_at' => date('Y-m-d H:i:s'),
1816            ]);
1817
1818            $this->quotationsController->addUpdateLog($quotationId, $uploadedBy, 'upload_attachment', null, $filename, 4);
1819
1820            return response()->json([
1821                'success' => true,
1822                'message' => 'Documento guardado correctamente en la base de datos.',
1823                'filename' => $filename,
1824                'fileId' => $file->file_id,
1825                'documentName' => $documentName,
1826            ], 200);
1827
1828        } catch (\Exception $e) {
1829            return response()->json([
1830                'success' => false,
1831                'error' => $e->getMessage(),
1832            ], 500);
1833        }
1834    }
1835
1836    /**
1837     * Formatear bytes a formato legible
1838     */
1839    private function formatBytes($bytes, $precision = 2)
1840    {
1841        $units = ['B', 'KB', 'MB', 'GB', 'TB'];
1842
1843        $bytes = max($bytes, 0);
1844        $pow = floor(($bytes ? log($bytes) : 0) / log(1024));
1845        $pow = min($pow, count($units) - 1);
1846
1847        $bytes /= pow(1024, $pow);
1848
1849        return round($bytes, $precision) . ' ' . $units[$pow];
1850    }
1851
1852    /**
1853     * @param $failedSyncs
1854     * @param $successfulSyncs
1855     * @param $startCronDateTime
1856     * @param $name
1857     */
1858    public function updateLogs($failedSyncs, $successfulSyncs, $successIdSyncs, $startCronDateTime, $name, $region, $process = null): void {
1859        Log::channel('g3w')->error($failedSyncs);
1860
1861        if ($region === 'Catalunya') {
1862            $region = 'Cataluña';
1863        }
1864
1865        $companyId = TblCompanies::where('region', $region)->first()->company_id;
1866        $syncStatus = 'Success';
1867        $failedSyncsTxt = '';
1868
1869        if (! empty($failedSyncs)) {
1870            $syncStatus = ($successfulSyncs > 0) ? 'Partially failed' : 'Failed';
1871            $this->notifyErrors($failedSyncs);
1872            $errorsArray = array_column($failedSyncs, 'error');
1873            $failedSyncsTxt = implode(', ', $errorsArray);
1874        }
1875
1876        $idsError = array_map(intval(...), array_column($failedSyncs, 'id'));
1877        $idsError = array_unique($idsError);
1878        $idsErrorCount = count($idsError);
1879        $idsErrorJson = json_encode($idsError);
1880
1881        $idsSuccess = array_map(intval(...), array_column($successIdSyncs, 'id'));
1882        $idsSuccess = array_unique($idsSuccess);
1883        $idsSuccessCount = count($idsSuccess);
1884        $idsSuccessJson = json_encode($idsSuccess);
1885
1886        TblG3WOrdersUpdateLogs::create(
1887            [
1888                "company_id" => $companyId,
1889                "to_process" => $process?? "Orders",
1890                "status" => $syncStatus,
1891                "sync_succesfull" => $idsSuccessCount,
1892                "sync_error" => $idsErrorCount,
1893                "sync_error_message" => $failedSyncsTxt,
1894                "sync_error_ids" => $idsErrorJson,
1895                "sync_success_ids" => $idsSuccessJson,
1896                "processed_by" => $name,
1897                "started_at" => $startCronDateTime,
1898                "ended_at" => TblG3wLastUpdate::where("region", $region)->first()->updated_at->format('Y-m-d H:i:s'),
1899            ]
1900        );
1901    }
1902
1903    private function checkEmailInvalid($email)
1904    {
1905        $emailPattern = "/^[\w\.\-]+@([\w\-]+\.)+[a-zA-Z]{2,}$/";
1906
1907        $emails = explode(',', $email);
1908
1909        $emailInvalid = false;
1910
1911        foreach ($emails as $email) {
1912            if (! preg_match($emailPattern, $email)) {
1913                $emailInvalid = true;
1914                break;
1915            }
1916        }
1917
1918        return $emailInvalid;
1919    }
1920
1921    private function checkRequiredFields($data): ?string{
1922
1923        $g3wWarningFields = [];
1924
1925        if ($data->budget_status_id == 13 || $data->budget_status_id == 14) {
1926            array_push($g3wWarningFields, 'Estado');
1927        }
1928
1929        if ($data->budget_type_id == null || $data->budget_type_id == '' || $data->budget_type_id == 0 || $data->budget_type_id == 16) {
1930            array_push($g3wWarningFields, 'Tipo');
1931        }
1932
1933        if ($data->commercial == null || $data->commercial == '') {
1934            array_push($g3wWarningFields, 'Comercial');
1935        }
1936
1937        if ($data->source_id == null || $data->source_id == '') {
1938            array_push($g3wWarningFields, 'Fuente');
1939        }
1940
1941        if ($data->email == null || $data->email == '' || $this->isBlacklistedEmail($data->email)) {
1942            array_push($g3wWarningFields, 'Email');
1943        }
1944
1945        if ($data->client == null || $data->client == '') {
1946            array_push($g3wWarningFields, 'Datos cliente');
1947        }
1948
1949        if (($data->amount == null || $data->amount == 0) && in_array($data->budget_status_id, [1, 2, 3, 11, 17])) {
1950            array_push($g3wWarningFields, 'Importe');
1951        }
1952
1953        if (! empty($g3wWarningFields)) {
1954            return implode(', ', $g3wWarningFields);
1955        } else {
1956            return null;
1957        }
1958    }
1959
1960    private function isBlacklistedEmail(?string $email): bool
1961    {
1962        if (! $email || trim($email) === '') {
1963            return true;
1964        }
1965
1966        $pattern = '/^no@|^nomail@nomail|^notiene@notiene|tiene\.email|test\.com|prueba\.com/i';
1967
1968        $emails = explode(',', $email);
1969        foreach ($emails as $e) {
1970            if (preg_match($pattern, trim($e))) {
1971                return true;
1972            }
1973        }
1974
1975        return false;
1976    }
1977
1978    public function syncExistingDataWithWarnings(): void
1979    {
1980
1981        $budgets = TblQuotations::where('g3w_warning', 1)->get();
1982
1983        if (count($budgets) > 0) {
1984            foreach ($budgets as $item) {
1985                $g3wWarning = 0;
1986                $g3wWarningFields = $this->checkRequiredFields($item);
1987
1988                if ($g3wWarningFields != null && $g3wWarningFields != '') {
1989                    $g3wWarning = 1;
1990                }
1991
1992                TblQuotations::where('id', $item->id)->update(
1993                    [
1994                        'g3w_warning' => $g3wWarning,
1995                        'g3w_warning_fields' => $g3wWarningFields
1996                    ]
1997                );
1998
1999                $g3wWarningFields = null;
2000            }
2001        }
2002    }
2003
2004    public function syncByIds($ids, $region, $date, $user="System"): array
2005    {
2006        try {
2007            if (! $ids) {
2008                throw new \Exception('No ids provided');
2009            }
2010
2011            $arrayIds = explode(",", (string) $ids);
2012
2013            $g3wActive = TblCompanies::where('region', $region)->first()->g3W_active;
2014
2015            if (! $g3wActive) {
2016                throw new Exception("La sincronización con G3W debe estar desactivada en la region '$region'.");
2017            }
2018
2019            $this->setSyncStatus(1, $region);
2020            $this->syncExistingDataWithWarnings();
2021
2022            $successfulSyncs = 0;
2023            $failedSyncs = [];
2024            $successIdSyncs = [];
2025
2026            $startCronDateTime = date('Y-m-d H:i:s');
2027
2028            $company = TblCompanies::where('region', $region)->first();
2029
2030            $isNullInDate = TblQuotations::where("company_id", $company->company_id)
2031                ->where(function ($query): void {
2032                    $query->where("sync_import", 1)
2033                        ->orWhere("sync_import_edited", 1);
2034                })
2035                ->whereDate('created_at', $date)
2036                ->where('internal_quote_id', null)
2037                ->exists();
2038
2039            foreach ($arrayIds as $id) {
2040                $result['success'] = false;
2041                $result['error'] = "Error en sync by Ids";
2042                if($isNullInDate){
2043                    $result['success'] = DB::transaction(fn() => $this->syncNullBudget($id, $region, $company->company_id, $date));
2044                }
2045
2046                if(!$result['success']){
2047                    $result = DB::transaction(fn() => $this->syncById($id, $region));
2048                }
2049
2050                if ($result['success']) {
2051                    $successfulSyncs++;
2052                    $successIdSyncs[] = [
2053                        'id' => $id,
2054                    ];
2055
2056                    $quote = TblQuotations::where('company_id', $company->company_id)
2057                        ->where('internal_quote_id', $id)
2058                        ->first();
2059
2060                    if (! $quote && $company->company_id == 18) {
2061                        $quote = TblQuotations::where('company_id', 22)
2062                            ->where('internal_quote_id', $id)
2063                            ->first();
2064                    }
2065
2066                    if (! $quote && $company->company_id == 22) {
2067                        $quote = TblQuotations::where('company_id', 18)
2068                            ->where('internal_quote_id', $id)
2069                            ->first();
2070                    }
2071
2072                    if($quote){
2073                        $quote->update([
2074                            "created_at" => Carbon::parse($date)
2075                        ]);
2076                    }
2077
2078                } else {
2079                    if (!str_contains((string) $result['error'], 'No se ha encontrado el presupuesto')) {
2080                        $failedSyncs[] = [
2081                            'id' => $id,
2082                            'error' => $result['error'] ?? 'Unknown error',
2083                        ];
2084                    }
2085                }
2086            }
2087
2088            $this->setSyncStatus(0, $region);
2089
2090            // FIRE-1025: zero-date sweep moved to the daily
2091            // `quotations:cleanup-zero-dates` artisan command. Pre-fix this
2092            // UPDATE ran at the end of every G3W sync cycle (5 call sites
2093            // here) and deadlocked on `idx_acceptance_date` when concurrent
2094            // syncs piled up (see InnoDB MONITOR OUTPUT on 2026-04-27 with
2095            // multiple "WE ROLL BACK TRANSACTION (1)" entries on this exact
2096            // statement). FIRE-1001 normalises `aceptacion` on write so no
2097            // new zero-dates can be created — the daily cleanup handles
2098            // legacy rows.
2099
2100            $this->updateLogs($failedSyncs, $successfulSyncs, $successIdSyncs, $startCronDateTime, $user, $region);
2101
2102            return [
2103                'success' => true,
2104                'message' => 'Synchronization completed.',
2105            ];
2106        } catch (\Exception $e) {
2107            Log::channel('g3w')->error('Error sincronizando los presupuestos: '.$e->getMessage());
2108
2109            if (TblG3wLastUpdate::where('region', $region)->first()->updatingNow === 1) {
2110                TblG3wLastUpdate::where('region', $region)->first()->update(['updatingNow' => 0]);
2111            }
2112
2113            return ['success' => false, 'error' => $e->getMessage()];
2114        }
2115
2116    }
2117
2118    private function syncNullBudget(string $id, $region, $companyId, $date): bool{
2119        $budget = $this->request('get', "presupuesto/{$id}", $region, []);
2120
2121        if (! isset($budget['presupuesto']) || ! is_array($budget['presupuesto'])) {
2122            throw new \Exception('El presupuesto no contiene los datos esperados.');
2123        }
2124
2125        $quote = TblQuotations::where("company_id", $companyId)
2126            ->where(function ($query): void {
2127                $query->where("sync_import", 1)
2128                    ->orWhere("sync_import_edited", 1);
2129            })
2130            ->whereDate('created_at', $date)
2131            ->where('internal_quote_id', null)
2132            ->where('source_by_g3w', $budget['presupuesto']['cod_empresa_presupuesto'] ?? null)
2133            ->where('type_by_g3w', $budget['presupuesto']['origen_presupuesto'] ?? null)
2134            ->where('user_create_by_g3w', $budget['presupuesto']['usuario'])
2135            ->where('user_commercial_by_g3w', ! empty($budget['presupuesto']['cod_comercial_presupuesto']) ? $budget['presupuesto']['cod_comercial_presupuesto'] : null)
2136            ->first();
2137
2138        if (! $quote) {
2139            return false;
2140        }
2141
2142        $quote->update([
2143            "internal_quote_id" => $id
2144        ]);
2145
2146        return true;
2147    }
2148
2149    function getAlternativeClientData($texto): array {
2150        preg_match('/[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}/', (string) $texto, $email);
2151        preg_match('/\b\d{9}\b/', (string) $texto, $number);
2152
2153        $nombre = null;
2154        $lineas = explode("\n", (string) $texto);
2155        foreach ($lineas as $linea) {
2156
2157            $linea = trim($linea);
2158
2159            if (empty($linea) ||
2160                preg_match('/@|\b\d{9}\b|C\/|CALLE|AVENIDA|PLAZA|CARRER|\d{5}/i', $linea)) {
2161                continue;
2162            }
2163
2164            if (preg_match('/^[A-Za-zÁÉÍÓÚáéíóúÑñ\s\.]+$/', $linea) &&
2165                ! preg_match('/\d/', $linea) &&
2166                substr_count($linea, ' ') >= 1 &&
2167                strlen($linea) > 5) {
2168
2169                $candidatos[] = $linea;
2170            }
2171        }
2172
2173        if (!empty($candidatos)) {
2174            usort($candidatos, function($a, $b): int {
2175                $scoreA = (!str_contains($a, '.') ? 2 : 0) + strlen($a);
2176                $scoreB = (!str_contains($b, '.') ? 2 : 0) + strlen($b);
2177                return $scoreB - $scoreA;
2178            });
2179            $nombre = rtrim($candidatos[0], '.');
2180        }
2181
2182        return [
2183            'email' => $email[0] ?? null,
2184            'number' => $number[0] ?? null,
2185            'name' => $nombre,
2186        ];
2187    }
2188
2189    private static function checkAproval($companyId, $budgetTypeId, $customerTypeId, $amount, $quoteId, $id, $commercialId, $comercial): ?int{
2190        $forApproval = null;
2191
2192        $company = TblCompanies::where('company_id', $companyId)->first();
2193        $project = TblProjectTypes::where('company_id', $companyId)->where('budget_type_id', $budgetTypeId)->first();
2194        $customerTypeIds = [];
2195
2196        if($project){
2197            if(!empty($project->customer_type_ids)){
2198                $customerTypeIds = array_map(intval(...), explode(',', (string) $project->customer_type_ids));
2199            }
2200            if ($project->minimum_order_size != null && in_array($customerTypeId, $customerTypeIds)) {
2201                if ($amount >= $project->minimum_order_size) {
2202                    $forApproval = 1;
2203                }
2204            }
2205            $minimumOrderSize = $project->minimum_order_size;
2206        }else{
2207            if(!empty($company->customer_type_ids)){
2208                $customerTypeIds = array_map(intval(...), explode(',', (string) $company->customer_type_ids));
2209            }
2210            if ($company->minimum_order_size != null && in_array($customerTypeId, $customerTypeIds)) {
2211                if ($amount >= $company->minimum_order_size) {
2212                    $forApproval = 1;
2213                }
2214            }
2215            $minimumOrderSize = $company->minimum_order_size;
2216        }
2217
2218        if ($forApproval === 1) {
2219            $quotations = new Quotations;
2220
2221            if (! $commercialId) {
2222                // value('id') returns null if no row matches — avoids crashing
2223                // the whole sync when G3W's commercial name doesn't map to a user.
2224                $commercialId = TblUsers::where('name', $comercial)->value('id');
2225            }
2226
2227            // If we still can't resolve the commercial, DO NOT skip the
2228            // notification — the technical approver still needs to know a
2229            // quote requires review. send_approval_notification is null-safe
2230            // for a missing commercial (no BCC/CC to commercial) and the
2231            // primary recipients (tbl_approvers) still get the email.
2232            if (! $commercialId) {
2233                Log::channel('allInfoQuotationsG3w')->warning(
2234                    "checkAproval: no se encontró usuario para el comercial '{$comercial}' (quote_id {$quoteId}, id {$id}). Se notifica solo al aprobador técnico."
2235                );
2236            }
2237
2238            $quotations->send_approval_notification(
2239                $amount,
2240                $budgetTypeId,
2241                $customerTypeId,
2242                $minimumOrderSize,
2243                $quoteId,
2244                $id,
2245                $company->name,
2246                'System',
2247                $commercialId,
2248                0,
2249                null,
2250                $company->company_id,
2251                'orders',
2252                0,
2253                0,
2254                null,
2255                'es'
2256            );
2257        }
2258
2259        return $forApproval;
2260    }
2261}