Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
0.00% |
0 / 379 |
|
0.00% |
0 / 19 |
CRAP | |
0.00% |
0 / 1 |
| FreshDeskTicketSync | |
0.00% |
0 / 379 |
|
0.00% |
0 / 19 |
17292 | |
0.00% |
0 / 1 |
| handle | |
0.00% |
0 / 28 |
|
0.00% |
0 / 1 |
90 | |||
| syncSingleTicket | |
0.00% |
0 / 32 |
|
0.00% |
0 / 1 |
132 | |||
| syncRegion | |
0.00% |
0 / 48 |
|
0.00% |
0 / 1 |
156 | |||
| httpJson | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
2 | |||
| getWithRateLimit | |
0.00% |
0 / 20 |
|
0.00% |
0 / 1 |
42 | |||
| isAddressedToBajas | |
0.00% |
0 / 8 |
|
0.00% |
0 / 1 |
30 | |||
| isConfirmedBaja | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
12 | |||
| upsertTicket | |
0.00% |
0 / 23 |
|
0.00% |
0 / 1 |
20 | |||
| writeLeaveRow | |
0.00% |
0 / 50 |
|
0.00% |
0 / 1 |
420 | |||
| indexLookupsByNote | |
0.00% |
0 / 9 |
|
0.00% |
0 / 1 |
30 | |||
| fetchClientFinder | |
0.00% |
0 / 23 |
|
0.00% |
0 / 1 |
20 | |||
| fetchClosures | |
0.00% |
0 / 39 |
|
0.00% |
0 / 1 |
156 | |||
| parseClosureTemplate | |
0.00% |
0 / 44 |
|
0.00% |
0 / 1 |
182 | |||
| snap | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
12 | |||
| resolveRegion | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
110 | |||
| normalizeRegion | |
0.00% |
0 / 15 |
|
0.00% |
0 / 1 |
12 | |||
| normalizeYesNo | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
12 | |||
| customField | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
20 | |||
| cleanHtml | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
12 | |||
| 1 | <?php |
| 2 | |
| 3 | namespace App\Console\Commands; |
| 4 | |
| 5 | use App\Models\TblLeave; |
| 6 | use Carbon\Carbon; |
| 7 | use Illuminate\Console\Command; |
| 8 | use Illuminate\Http\Client\ConnectionException; |
| 9 | use Illuminate\Http\Client\PendingRequest; |
| 10 | use Illuminate\Http\Client\Response; |
| 11 | use Illuminate\Support\Facades\Http; |
| 12 | use Illuminate\Support\Facades\Log; |
| 13 | |
| 14 | class FreshDeskTicketSync extends Command |
| 15 | { |
| 16 | protected $signature = 'sync:freshdesk |
| 17 | {--ticket= : Sync only this Freshdesk ticket id (for verification). Requires --region.} |
| 18 | {--region= : Region code (CAT/MAD/ALM/BAL/VAL) the --ticket belongs to.}'; |
| 19 | |
| 20 | protected $description = 'Sync confirmed Bajas tickets from each regional Freshdesk instance into tbl_leave (TITAN Bajas tracker).'; |
| 21 | |
| 22 | /** |
| 23 | * Custom field key in the Freshdesk ticket payload that confirms a baja. |
| 24 | * If your account uses a different internal name (e.g. cf_es_baja), change this constant. |
| 25 | */ |
| 26 | private const BAJA_CUSTOM_FIELD = 'cf_baja'; |
| 27 | |
| 28 | /** |
| 29 | * Confirmation values that count as "Sí" (Spanish accents may be stripped by Freshdesk). |
| 30 | */ |
| 31 | private const BAJA_CONFIRM_VALUES = ['Sí', 'Si', 'SI', 'YES', 'Yes', 'true', '1']; |
| 32 | |
| 33 | /** |
| 34 | * Per-region configuration. company_id is the umbrella TITAN company for that region; |
| 35 | * BAL falls back to null until the team creates the matching tbl_companies row. |
| 36 | */ |
| 37 | private const REGIONS = [ |
| 38 | 'CAT' => ['domain' => 'firebusinesssl-help.freshdesk.com', 'company_id' => 19], |
| 39 | 'MAD' => ['domain' => 'firebusinessmadrid.freshdesk.com', 'company_id' => 18], |
| 40 | 'ALM' => ['domain' => 'firebusinessalmeria.freshdesk.com', 'company_id' => 21], |
| 41 | 'BAL' => ['domain' => null, 'company_id' => 33], |
| 42 | 'VAL' => ['domain' => null, 'company_id' => 30], |
| 43 | ]; |
| 44 | |
| 45 | /** |
| 46 | * 3-letter region code → full Spanish name stored in tbl_leave.region. |
| 47 | * Codes are used for integration payloads (Client Finder webhook); the full |
| 48 | * name is used for display / DB persistence. |
| 49 | */ |
| 50 | private const REGION_NAMES = [ |
| 51 | 'CAT' => 'Cataluña', |
| 52 | 'MAD' => 'Madrid', |
| 53 | 'ALM' => 'Andalucía', |
| 54 | 'BAL' => 'Baleares', |
| 55 | 'VAL' => 'Valencia', |
| 56 | ]; |
| 57 | |
| 58 | private const BAJAS_INTAKE_EMAIL = 'bajas@fire.es'; |
| 59 | |
| 60 | /** |
| 61 | * Only sync tickets that belong to this Freshdesk group. Freshdesk group ids |
| 62 | * are scoped to a single subdomain, so this will match in one region and |
| 63 | * filter out every ticket in the others. |
| 64 | */ |
| 65 | private const BAJAS_GROUP_ID = 203000117233; |
| 66 | |
| 67 | /** |
| 68 | * HTTP retry: how many attempts and base delay (ms). Laravel's HTTP client retries on |
| 69 | * ConnectionException by default, which covers cURL DNS / TCP / TLS failures. |
| 70 | */ |
| 71 | private const HTTP_RETRY_TIMES = 3; |
| 72 | |
| 73 | private const HTTP_RETRY_DELAY_MS = 500; |
| 74 | |
| 75 | private const HTTP_TIMEOUT_S = 30; |
| 76 | |
| 77 | /** |
| 78 | * The Client Finder webhook runs an AI workflow that looks up one client per |
| 79 | * closure note, so it routinely takes ~30s and scales with note count. Give it |
| 80 | * a much larger timeout than the plain Freshdesk REST calls or it gets cut off |
| 81 | * mid-response and the lookup silently comes back empty. |
| 82 | */ |
| 83 | private const CLIENT_FINDER_TIMEOUT_S = 180; |
| 84 | |
| 85 | /** |
| 86 | * Canonical reason_details values stored in tbl_leave.reason_details (see |
| 87 | * modal.component.ts reasonDetailsCanonical). Mixed case is intentional — |
| 88 | * UPPER_SNAKE codes and Title Case both round-trip to legacy DB content. |
| 89 | * Shared by the closure-note parser and the Client Finder `detalle` mapping. |
| 90 | */ |
| 91 | private const REASON_DETAILS_VALUES = [ |
| 92 | 'ATENCION_CLIENTE', 'Cese', 'GRUPO_FIRE', 'LICITACION', |
| 93 | 'Otros', 'PARTICULAR', 'Precio', 'Servicio', 'TRASPASO', |
| 94 | ]; |
| 95 | |
| 96 | public function handle(): int |
| 97 | { |
| 98 | // Conversations endpoint returns full HTML email bodies (sometimes with inline |
| 99 | // images). The default 128M PHP limit isn't enough when fetching dozens. |
| 100 | // ini_set may be locked by php.ini on some installs — verify and warn. |
| 101 | @ini_set('memory_limit', '512M'); |
| 102 | $effective = ini_get('memory_limit'); |
| 103 | if ($effective !== '512M' && $effective !== '-1') { |
| 104 | $this->warn("memory_limit is {$effective} — ini_set was overridden. If you hit OOM, re-run with: php -d memory_limit=1G artisan sync:freshdesk"); |
| 105 | } |
| 106 | |
| 107 | // Targeted single-ticket run, e.g. for staging verification: |
| 108 | // php artisan sync:freshdesk --ticket=35453 --region=MAD |
| 109 | if ($this->option('ticket') !== null) { |
| 110 | return $this->syncSingleTicket(); |
| 111 | } |
| 112 | |
| 113 | foreach (self::REGIONS as $region => $cfg) { |
| 114 | $domain = env('FRESHDESK_DOMAIN_'.$region, $cfg['domain']); |
| 115 | $token = env('FRESHDESK_TOKEN_'.$region); |
| 116 | |
| 117 | if (! $domain || ! $token) { |
| 118 | $this->info("Region {$region}: skipped (missing FRESHDESK_DOMAIN_{$region} or FRESHDESK_TOKEN_{$region})."); |
| 119 | |
| 120 | continue; |
| 121 | } |
| 122 | |
| 123 | $this->info("Region {$region}: syncing {$domain}…"); |
| 124 | |
| 125 | try { |
| 126 | $this->syncRegion($region, $domain, $token, $cfg['company_id']); |
| 127 | } catch (ConnectionException $e) { |
| 128 | $this->warn("Region {$region}: network error — {$e->getMessage()}. Skipping to next region."); |
| 129 | Log::warning('Freshdesk sync: region network error', [ |
| 130 | 'region' => $region, |
| 131 | 'domain' => $domain, |
| 132 | 'error' => $e->getMessage(), |
| 133 | ]); |
| 134 | } catch (\Throwable $e) { |
| 135 | $this->error("Region {$region}: failed — {$e->getMessage()}"); |
| 136 | Log::error('Freshdesk sync: region failed', [ |
| 137 | 'region' => $region, |
| 138 | 'error' => $e->getMessage(), |
| 139 | ]); |
| 140 | } |
| 141 | } |
| 142 | |
| 143 | return self::SUCCESS; |
| 144 | } |
| 145 | |
| 146 | /** |
| 147 | * Verification path: sync exactly one ticket by id, bypassing the group / |
| 148 | * intake-email / cf_baja gate (the operator named the ticket explicitly). |
| 149 | * Intended for staging checks, e.g.: |
| 150 | * php artisan sync:freshdesk --ticket=35453 --region=MAD |
| 151 | */ |
| 152 | private function syncSingleTicket(): int |
| 153 | { |
| 154 | $ticketId = (int) $this->option('ticket'); |
| 155 | $region = strtoupper((string) $this->option('region')); |
| 156 | |
| 157 | if ($ticketId <= 0 || ! isset(self::REGIONS[$region])) { |
| 158 | $this->error('Usage: sync:freshdesk --ticket=<id> --region=<'.implode('|', array_keys(self::REGIONS)).'>'); |
| 159 | |
| 160 | return self::FAILURE; |
| 161 | } |
| 162 | |
| 163 | $domain = env('FRESHDESK_DOMAIN_'.$region, self::REGIONS[$region]['domain']); |
| 164 | $token = env('FRESHDESK_TOKEN_'.$region); |
| 165 | |
| 166 | if (! $domain || ! $token) { |
| 167 | $this->error("Region {$region}: missing FRESHDESK_DOMAIN_{$region} or FRESHDESK_TOKEN_{$region}."); |
| 168 | |
| 169 | return self::FAILURE; |
| 170 | } |
| 171 | |
| 172 | $this->info("Fetching ticket {$ticketId} from {$domain}…"); |
| 173 | |
| 174 | try { |
| 175 | $response = $this->getWithRateLimit( |
| 176 | fn () => $this->httpJson($token)->get("https://{$domain}/api/v2/tickets/{$ticketId}") |
| 177 | ); |
| 178 | } catch (ConnectionException $e) { |
| 179 | $this->error("Ticket fetch failed (network): {$e->getMessage()}"); |
| 180 | |
| 181 | return self::FAILURE; |
| 182 | } |
| 183 | |
| 184 | if (! $response->successful()) { |
| 185 | $this->error("Ticket fetch returned HTTP {$response->status()}."); |
| 186 | |
| 187 | return self::FAILURE; |
| 188 | } |
| 189 | |
| 190 | $ticket = $response->json(); |
| 191 | if (! is_array($ticket) || ! isset($ticket['id'])) { |
| 192 | $this->error('Ticket payload missing or malformed.'); |
| 193 | |
| 194 | return self::FAILURE; |
| 195 | } |
| 196 | |
| 197 | if (! $this->isConfirmedBaja($ticket)) { |
| 198 | $this->warn('Note: ticket does not pass the cf_baja gate — syncing anyway (explicit single-ticket run).'); |
| 199 | } |
| 200 | |
| 201 | $this->upsertTicket($ticket, $region, $domain, $token, self::REGIONS[$region]['company_id']); |
| 202 | |
| 203 | $rows = TblLeave::where('freshdesk_ticket_id', $ticketId)->orderBy('note_id')->get(['id', 'note_id', 'customer_name', 'billing_client_code', 'region']); |
| 204 | $this->info("Synced. tbl_leave now holds {$rows->count()} row(s) for ticket {$ticketId}:"); |
| 205 | foreach ($rows as $r) { |
| 206 | $this->line(" id={$r->id} note_id={$r->note_id} code={$r->billing_client_code} region={$r->region} name={$r->customer_name}"); |
| 207 | } |
| 208 | |
| 209 | return self::SUCCESS; |
| 210 | } |
| 211 | |
| 212 | private function syncRegion(string $region, string $domain, string $token, ?int $defaultCompanyId): void |
| 213 | { |
| 214 | // Freshdesk Filter/Search API caps results at 30 per page and 30 pages |
| 215 | // (~900 records). Per-page size is fixed by the API — there is no |
| 216 | // `per_page` parameter on /search/tickets. |
| 217 | $page = 1; |
| 218 | $maxPage = 30; |
| 219 | $expectedPerPage = 30; |
| 220 | |
| 221 | // Ticket attributes that the Search API natively supports as query |
| 222 | // operands. Filtering server-side trims the response volume and |
| 223 | // dodges the per-page cap. cf_baja stays client-side because the |
| 224 | // SOP treats it as the definitive gate, and it's cheap to recheck. |
| 225 | $query = sprintf('"group_id:%d"', self::BAJAS_GROUP_ID); |
| 226 | |
| 227 | while ($page <= $maxPage) { |
| 228 | $url = sprintf( |
| 229 | 'https://%s/api/v2/search/tickets?query=%s&page=%d', |
| 230 | $domain, |
| 231 | rawurlencode($query), |
| 232 | $page |
| 233 | ); |
| 234 | |
| 235 | $response = $this->getWithRateLimit(fn () => $this->httpJson($token)->get($url)); |
| 236 | |
| 237 | if (! $response->successful()) { |
| 238 | Log::warning('Freshdesk sync: non-success response', [ |
| 239 | 'region' => $region, |
| 240 | 'page' => $page, |
| 241 | 'status' => $response->status(), |
| 242 | ]); |
| 243 | break; |
| 244 | } |
| 245 | |
| 246 | $body = $response->json(); |
| 247 | // Free the raw response body now that we have the parsed array. |
| 248 | unset($response); |
| 249 | |
| 250 | // Search API shape: {"total": int, "results": [ticket, ...]} |
| 251 | $tickets = is_array($body) ? ($body['results'] ?? []) : []; |
| 252 | unset($body); |
| 253 | |
| 254 | if (count($tickets) === 0) { |
| 255 | break; |
| 256 | } |
| 257 | |
| 258 | $ticketCount = count($tickets); |
| 259 | |
| 260 | foreach ($tickets as $ticket) { |
| 261 | // group_id is guaranteed by the search query, but keep the |
| 262 | // existing email criterion as a belt-and-braces initial |
| 263 | // filter and re-check the cf_baja gate. |
| 264 | if (($ticket['group_id'] ?? null) == self::BAJAS_GROUP_ID || $this->isAddressedToBajas($ticket)) { |
| 265 | if (! $this->isConfirmedBaja($ticket)) { |
| 266 | continue; |
| 267 | } |
| 268 | } else { |
| 269 | continue; |
| 270 | } |
| 271 | |
| 272 | try { |
| 273 | $this->upsertTicket($ticket, $region, $domain, $token, $defaultCompanyId); |
| 274 | } catch (\Throwable $e) { |
| 275 | Log::error('Freshdesk sync: failed to upsert ticket', [ |
| 276 | 'region' => $region, |
| 277 | 'ticket_id' => $ticket['id'] ?? null, |
| 278 | 'error' => $e->getMessage(), |
| 279 | ]); |
| 280 | } |
| 281 | } |
| 282 | |
| 283 | // Free the page array and reclaim PHP's internal arena before the next fetch. |
| 284 | unset($tickets); |
| 285 | gc_collect_cycles(); |
| 286 | |
| 287 | // Stop early on a partial final page. |
| 288 | if ($ticketCount < $expectedPerPage) { |
| 289 | break; |
| 290 | } |
| 291 | |
| 292 | $page++; |
| 293 | } |
| 294 | |
| 295 | if ($page > $maxPage) { |
| 296 | Log::warning('Freshdesk sync: hit search pagination cap', [ |
| 297 | 'region' => $region, |
| 298 | 'cap' => $maxPage * $expectedPerPage, |
| 299 | ]); |
| 300 | } |
| 301 | } |
| 302 | |
| 303 | /** |
| 304 | * Authenticated Freshdesk HTTP client. Retries are handled by getWithRateLimit |
| 305 | * — we don't use Laravel's ->retry() here because it auto-throws on non-2xx, |
| 306 | * which would prevent us from inspecting 429 responses. |
| 307 | */ |
| 308 | private function httpJson(string $token): PendingRequest |
| 309 | { |
| 310 | return Http::withBasicAuth($token, 'X') |
| 311 | ->acceptJson() |
| 312 | ->timeout(self::HTTP_TIMEOUT_S); |
| 313 | } |
| 314 | |
| 315 | /** |
| 316 | * Run an HTTP request with retries: |
| 317 | * - 429 (rate limit): wait Retry-After header (clamped 1..120s) |
| 318 | * - ConnectionException (DNS / TCP / TLS): exponential backoff (2..60s) |
| 319 | * Up to 5 attempts. After exhaustion, returns the last response or rethrows. |
| 320 | */ |
| 321 | private function getWithRateLimit(callable $request): Response |
| 322 | { |
| 323 | $maxAttempts = 5; |
| 324 | $response = null; |
| 325 | $lastException = null; |
| 326 | |
| 327 | for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) { |
| 328 | try { |
| 329 | $response = $request(); |
| 330 | } catch (ConnectionException $e) { |
| 331 | $lastException = $e; |
| 332 | $delay = min(60, 2 ** $attempt); |
| 333 | $this->warn(" Connection error: {$e->getMessage()} — retrying in {$delay}s ({$attempt}/{$maxAttempts})."); |
| 334 | sleep($delay); |
| 335 | |
| 336 | continue; |
| 337 | } |
| 338 | |
| 339 | if ($response->status() !== 429) { |
| 340 | return $response; |
| 341 | } |
| 342 | |
| 343 | $retryAfter = (int) ($response->header('Retry-After') ?: 30); |
| 344 | $retryAfter = max(1, min(120, $retryAfter)); |
| 345 | |
| 346 | $this->warn(" Rate-limited (429); waiting {$retryAfter}s before retry ({$attempt}/{$maxAttempts})."); |
| 347 | sleep($retryAfter); |
| 348 | } |
| 349 | |
| 350 | if ($response !== null) { |
| 351 | return $response; |
| 352 | } |
| 353 | |
| 354 | throw $lastException ?? new \RuntimeException('Freshdesk request failed without exception or response'); |
| 355 | } |
| 356 | |
| 357 | /** |
| 358 | * Definitive ingestion gate per SOP §2: the Baja custom field must be "Sí". |
| 359 | * Group / intake-email act as initial filter only. |
| 360 | */ |
| 361 | /** |
| 362 | * True when the ticket's to_emails list contains the bajas intake address. |
| 363 | * Freshdesk returns entries either bare ("bajas@fire.es") or RFC-formatted |
| 364 | * ("\"Bajas Grupo Fire\" <bajas@fire.es>"), so we substring-match. |
| 365 | */ |
| 366 | private function isAddressedToBajas(array $ticket): bool |
| 367 | { |
| 368 | $toEmails = $ticket['to_emails'] ?? []; |
| 369 | if (! is_array($toEmails)) { |
| 370 | return false; |
| 371 | } |
| 372 | |
| 373 | $needle = strtolower(self::BAJAS_INTAKE_EMAIL); |
| 374 | foreach ($toEmails as $entry) { |
| 375 | if (is_string($entry) && str_contains(strtolower($entry), $needle)) { |
| 376 | return true; |
| 377 | } |
| 378 | } |
| 379 | |
| 380 | return false; |
| 381 | } |
| 382 | |
| 383 | private function isConfirmedBaja(array $ticket): bool |
| 384 | { |
| 385 | $customFields = $ticket['custom_fields'] ?? []; |
| 386 | $bajaValue = $customFields[self::BAJA_CUSTOM_FIELD] ?? null; |
| 387 | |
| 388 | if ($bajaValue === null) { |
| 389 | return false; |
| 390 | } |
| 391 | |
| 392 | if (is_bool($bajaValue)) { |
| 393 | return $bajaValue === true; |
| 394 | } |
| 395 | |
| 396 | return in_array(trim((string) $bajaValue), self::BAJA_CONFIRM_VALUES, true); |
| 397 | } |
| 398 | |
| 399 | private function upsertTicket( |
| 400 | array $ticket, |
| 401 | string $region, |
| 402 | string $domain, |
| 403 | string $token, |
| 404 | ?int $defaultCompanyId |
| 405 | ): void { |
| 406 | $ticketId = (int) $ticket['id']; |
| 407 | $cifFromTicket = $this->customField($ticket, 'cf_cif') |
| 408 | ?? $this->customField($ticket, 'cf_cif_nif') |
| 409 | ?? $this->customField($ticket, 'cf_nif'); |
| 410 | |
| 411 | $clientFinder = $this->fetchClientFinder($ticketId, $region); |
| 412 | $lookupsByNote = $this->indexLookupsByNote($clientFinder); |
| 413 | |
| 414 | $closures = $this->fetchClosures($ticketId, $domain, $token); |
| 415 | |
| 416 | // Shared per-ticket context, threaded into one row per closure note. |
| 417 | $ctx = [ |
| 418 | 'ticketId' => $ticketId, |
| 419 | 'cifFromTicket' => $cifFromTicket, |
| 420 | 'region' => $region, |
| 421 | 'domain' => $domain, |
| 422 | 'defaultCompanyId' => $defaultCompanyId, |
| 423 | 'ticketCreatedAt' => isset($ticket['created_at']) ? Carbon::parse($ticket['created_at'])->toDateTimeString() : null, |
| 424 | 'description' => $this->cleanHtml($ticket['description'] ?? null), |
| 425 | 'lookupsByNote' => $lookupsByNote, |
| 426 | 'clientFinder' => $clientFinder, |
| 427 | ]; |
| 428 | |
| 429 | // One tbl_leave row per "=== CIERRE DE BAJA ===" closure note. The Client |
| 430 | // Finder webhook now returns one lookup per note (keyed by note_id), so each |
| 431 | // closure is paired with its own client. A confirmed baja with no closure |
| 432 | // note yet keeps a single ticket-level row (note_id null) so it still shows |
| 433 | // in the tracker. |
| 434 | if (empty($closures)) { |
| 435 | $this->writeLeaveRow($ctx, null, []); |
| 436 | |
| 437 | return; |
| 438 | } |
| 439 | |
| 440 | foreach ($closures as $closure) { |
| 441 | $this->writeLeaveRow($ctx, $closure['note_id'], $closure['template']); |
| 442 | } |
| 443 | } |
| 444 | |
| 445 | /** |
| 446 | * Compose and upsert a single tbl_leave row for one closure note. Matched on |
| 447 | * (freshdesk_ticket_id, note_id) so re-syncs update in place instead of |
| 448 | * duplicating. $noteId is null for the ticket-level fallback row. |
| 449 | */ |
| 450 | private function writeLeaveRow(array $ctx, ?int $noteId, array $template): void |
| 451 | { |
| 452 | $entry = ($noteId !== null) ? ($ctx['lookupsByNote'][$noteId] ?? null) : null; |
| 453 | $lookup = (is_array($entry) && isset($entry['lookup']) && is_array($entry['lookup'])) ? $entry['lookup'] : []; |
| 454 | $clientData = (isset($lookup['client_data']) && is_array($lookup['client_data'])) ? $lookup['client_data'] : []; |
| 455 | // The Client Finder now also returns a structured `detalle` per note — |
| 456 | // the AI's reading of the closure note. Treated as authoritative for the |
| 457 | // fields it covers; the regex-parsed closure template is the fallback. |
| 458 | $detalle = (is_array($entry) && isset($entry['detalle']) && is_array($entry['detalle'])) ? $entry['detalle'] : []; |
| 459 | |
| 460 | // detalle.detalle_motivo → reason_details (e.g. "OTROS" → "Otros"). |
| 461 | $detalleMotivo = (isset($detalle['detalle_motivo']) && trim((string) $detalle['detalle_motivo']) !== '') |
| 462 | ? $this->snap(trim((string) $detalle['detalle_motivo']), self::REASON_DETAILS_VALUES) |
| 463 | : null; |
| 464 | |
| 465 | // detalle.cambio_mantenedor → change_of_maintenance_provider. Arrives as a |
| 466 | // JSON boolean from the webhook (true/false), but tolerate string forms too. |
| 467 | $detalleCambio = null; |
| 468 | if (array_key_exists('cambio_mantenedor', $detalle)) { |
| 469 | $cm = $detalle['cambio_mantenedor']; |
| 470 | if (is_bool($cm)) { |
| 471 | $detalleCambio = $cm ? 'Sí' : 'No'; |
| 472 | } elseif ($cm !== null && $cm !== '') { |
| 473 | $detalleCambio = $this->normalizeYesNo((string) $cm); |
| 474 | } |
| 475 | } |
| 476 | |
| 477 | // 3-letter region code to persist (see resolveRegion for the precedence rules). |
| 478 | $effectiveRegion = $this->resolveRegion($template['region'] ?? null, $lookup, $entry, $ctx['region']); |
| 479 | $companyId = self::REGIONS[$effectiveRegion]['company_id'] ?? $ctx['defaultCompanyId']; |
| 480 | |
| 481 | // Store the matched note's Client Finder entry (incl. reasoning) so admins |
| 482 | // see exactly what the webhook returned for this baja; the ticket-level |
| 483 | // fallback row stores the whole payload instead. |
| 484 | $adminBlob = ($entry !== null) ? $entry : $ctx['clientFinder']; |
| 485 | |
| 486 | $payload = [ |
| 487 | 'company_id' => $companyId, |
| 488 | // DB stores the full Spanish region name; integration payloads keep the 3-letter code. |
| 489 | 'region' => self::REGION_NAMES[$effectiveRegion] ?? $effectiveRegion, |
| 490 | 'freshdesk_ticket_id' => $ctx['ticketId'], |
| 491 | 'note_id' => $noteId, |
| 492 | 'freshdesk_ticket_link' => "https://{$ctx['domain']}/a/tickets/{$ctx['ticketId']}", |
| 493 | // CIF/NIF: Freshdesk ticket field first (SOP §3.1), then the lookup's client_cif. |
| 494 | 'cif_nif' => $ctx['cifFromTicket'] ?? ($lookup['client_cif'] ?? null), |
| 495 | 'date_of_receipt' => $ctx['ticketCreatedAt'], |
| 496 | |
| 497 | // From the Client Finder lookup for this note. |
| 498 | 'customer_name' => $lookup['client_name'] ?? null, |
| 499 | 'customer_email' => $lookup['client_email'] ?? null, |
| 500 | 'customer_telephone_number' => $lookup['client_phone'] ?? null, |
| 501 | 'gestiona_customer_id' => $clientData['sk_cliente_facturacion'] ?? null, |
| 502 | // Best-fit: the new payload returns a full postal address; store it as the branch. |
| 503 | 'branch' => $lookup['client_address'] ?? null, |
| 504 | // Pretty-printed JSON of what the webhook returned for this note. |
| 505 | 'comment_for_administration' => ! empty($adminBlob) |
| 506 | ? json_encode($adminBlob, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT) |
| 507 | : null, |
| 508 | |
| 509 | // Billing client code: closure template first, then the lookup's cod_cliente_facturacion. |
| 510 | 'billing_client_code' => $template['billing_client_code'] ?? ($lookup['cod_cliente_facturacion'] ?? null), |
| 511 | |
| 512 | // From the agent closure template (parser returns English DB-column keys). |
| 513 | // reason_details / change_of_maintenance_provider: webhook `detalle` wins, |
| 514 | // closure template is the fallback. |
| 515 | 'reason_for_cancellation' => $template['reason_for_cancellation'] ?? null, |
| 516 | 'reason_details' => $detalleMotivo ?? ($template['reason_details'] ?? null), |
| 517 | 'reason_details_2' => $template['reason_details_2'] ?? null, |
| 518 | 'change_of_maintenance_provider' => $detalleCambio ?? ($template['change_of_maintenance_provider'] ?? 'No'), |
| 519 | 'action' => $template['action'] ?? null, |
| 520 | 'customer_recovery' => $template['customer_recovery'] ?? null, |
| 521 | |
| 522 | 'description' => $ctx['description'], |
| 523 | 'created_by' => 'System', |
| 524 | 'updated_by' => 'System', |
| 525 | 'updated_at' => now(), |
| 526 | ]; |
| 527 | |
| 528 | // Drop nulls so an upsert doesn't blank existing manually-edited fields, |
| 529 | // but always overwrite link / region / dates / note key. |
| 530 | $alwaysOverwrite = ['freshdesk_ticket_link', 'region', 'company_id', 'date_of_receipt', 'note_id', 'updated_at', 'updated_by']; |
| 531 | $update = array_filter($payload, fn ($v, $k) => $v !== null || in_array($k, $alwaysOverwrite, true), ARRAY_FILTER_USE_BOTH); |
| 532 | |
| 533 | TblLeave::updateOrCreate( |
| 534 | ['freshdesk_ticket_id' => $ctx['ticketId'], 'note_id' => $noteId], |
| 535 | $update |
| 536 | ); |
| 537 | } |
| 538 | |
| 539 | /** |
| 540 | * Index the Client Finder response by note_id. Webhook shape: |
| 541 | * {"count": int, "data": [{"note_id": int, "lookup": {...}, "matched": bool, |
| 542 | * "detalle": {"detalle_motivo": str, "cambio_mantenedor": bool, "writeup": str}}, ...]} |
| 543 | * so each closure note can pull its own matched client and structured detalle. |
| 544 | * Entries without a note_id are skipped. |
| 545 | */ |
| 546 | private function indexLookupsByNote(array $clientFinder): array |
| 547 | { |
| 548 | $data = $clientFinder['data'] ?? []; |
| 549 | if (! is_array($data)) { |
| 550 | return []; |
| 551 | } |
| 552 | |
| 553 | $out = []; |
| 554 | foreach ($data as $entry) { |
| 555 | if (! is_array($entry) || ! isset($entry['note_id'])) { |
| 556 | continue; |
| 557 | } |
| 558 | $out[(int) $entry['note_id']] = $entry; |
| 559 | } |
| 560 | |
| 561 | return $out; |
| 562 | } |
| 563 | |
| 564 | /** |
| 565 | * Call the Client Finder webhook for a ticket. Response shape: |
| 566 | * {"count": int, "data": [{"note_id": int, "created_at": str, "lookup": { |
| 567 | * "cod_cliente_facturacion", "region", "client_name", "client_cif", |
| 568 | * "client_email", "client_phone", "client_address", "client_data": {...}, |
| 569 | * "reasoning"}, "matched": bool, "detalle": { |
| 570 | * "detalle_motivo": str, "cambio_mantenedor": bool, "writeup": str}}, ...]} |
| 571 | * — one lookup + detalle per note. Returns the decoded array, or [] on failure. |
| 572 | */ |
| 573 | private function fetchClientFinder(int $ticketId, string $region): array |
| 574 | { |
| 575 | try { |
| 576 | // throw: false so a slow-gateway 5xx (e.g. 504 while the AI workflow |
| 577 | // runs long) comes back as an inspectable response instead of a |
| 578 | // RequestException — we only retry connection-level failures, and a |
| 579 | // failed lookup must never abort the ticket: the closure fields still |
| 580 | // get written and the lookup backfills on the next sync. |
| 581 | $response = Http::acceptJson() |
| 582 | ->timeout(self::CLIENT_FINDER_TIMEOUT_S) |
| 583 | ->retry(self::HTTP_RETRY_TIMES, self::HTTP_RETRY_DELAY_MS, when: fn ($e) => $e instanceof ConnectionException, throw: false) |
| 584 | ->post('https://aiwf.ibvgroup.com/webhook/client-finder', [ |
| 585 | 'ticket_id' => $ticketId, |
| 586 | 'region' => strtolower($region), |
| 587 | ]); |
| 588 | } catch (ConnectionException $e) { |
| 589 | Log::warning('Freshdesk sync: client-finder unreachable', [ |
| 590 | 'ticket_id' => $ticketId, |
| 591 | 'region' => $region, |
| 592 | 'error' => $e->getMessage(), |
| 593 | ]); |
| 594 | |
| 595 | return []; |
| 596 | } |
| 597 | |
| 598 | if (! $response->successful()) { |
| 599 | Log::warning('Freshdesk sync: client-finder non-success', [ |
| 600 | 'ticket_id' => $ticketId, |
| 601 | 'region' => $region, |
| 602 | 'status' => $response->status(), |
| 603 | ]); |
| 604 | |
| 605 | return []; |
| 606 | } |
| 607 | |
| 608 | $data = $response->json(); |
| 609 | |
| 610 | return is_array($data) ? $data : []; |
| 611 | } |
| 612 | |
| 613 | /** |
| 614 | * Fetch ticket conversations and return every "=== CIERRE DE BAJA ===" closure |
| 615 | * note. A ticket can carry several closures (one baja per client), and the |
| 616 | * Client Finder webhook returns a matching lookup per note, so each note that |
| 617 | * contains the marker becomes its own closure keyed by note id. |
| 618 | * |
| 619 | * Returns a list of ['note_id' => int, 'created_at' => string, 'template' => array], |
| 620 | * or [] when no closure note is present. |
| 621 | */ |
| 622 | private function fetchClosures(int $ticketId, string $domain, string $token): array |
| 623 | { |
| 624 | // Paginate conversations 10 at a time. Freshdesk returns them oldest-first |
| 625 | // and the default un-paginated response caps at 30, so for tickets with many |
| 626 | // comments later closures would be missed. Free each page before the next. |
| 627 | $perPage = 10; |
| 628 | $page = 1; |
| 629 | $maxPages = 10; |
| 630 | $closures = []; // note_id => closure (last parse wins on pagination overlap) |
| 631 | |
| 632 | while ($page <= $maxPages) { |
| 633 | try { |
| 634 | $url = "https://{$domain}/api/v2/tickets/{$ticketId}/conversations?per_page={$perPage}&page={$page}"; |
| 635 | $response = $this->getWithRateLimit(fn () => $this->httpJson($token)->get($url)); |
| 636 | } catch (ConnectionException $e) { |
| 637 | Log::warning('Freshdesk sync: conversations fetch failed', [ |
| 638 | 'ticket_id' => $ticketId, |
| 639 | 'domain' => $domain, |
| 640 | 'error' => $e->getMessage(), |
| 641 | ]); |
| 642 | break; |
| 643 | } |
| 644 | |
| 645 | if (! $response->successful()) { |
| 646 | break; |
| 647 | } |
| 648 | |
| 649 | $conversations = $response->json(); |
| 650 | unset($response); |
| 651 | |
| 652 | if (! is_array($conversations) || count($conversations) === 0) { |
| 653 | break; |
| 654 | } |
| 655 | |
| 656 | $count = count($conversations); |
| 657 | |
| 658 | foreach ($conversations as $conv) { |
| 659 | $body = $conv['body_text'] ?? strip_tags($conv['body'] ?? ''); |
| 660 | if (! $body || stripos($body, '=== CIERRE DE BAJA ===') === false) { |
| 661 | continue; |
| 662 | } |
| 663 | |
| 664 | $noteId = isset($conv['id']) ? (int) $conv['id'] : null; |
| 665 | if ($noteId === null) { |
| 666 | continue; |
| 667 | } |
| 668 | |
| 669 | $closures[$noteId] = [ |
| 670 | 'note_id' => $noteId, |
| 671 | 'created_at' => $conv['created_at'] ?? '', |
| 672 | 'template' => $this->parseClosureTemplate($body), |
| 673 | ]; |
| 674 | } |
| 675 | |
| 676 | unset($conversations); |
| 677 | gc_collect_cycles(); |
| 678 | |
| 679 | if ($count < $perPage) { |
| 680 | break; |
| 681 | } |
| 682 | |
| 683 | $page++; |
| 684 | } |
| 685 | |
| 686 | return array_values($closures); |
| 687 | } |
| 688 | |
| 689 | /** |
| 690 | * Parse the structured closure block. Tolerant of: |
| 691 | * - the comment being on a single line (no real newlines) |
| 692 | * - extra whitespace around colons |
| 693 | * - case / accent variations on the labels |
| 694 | * - free-text values that may contain newlines (e.g. "Acción") |
| 695 | * |
| 696 | * Spanish field labels in the comment are mapped to English DB-column keys. |
| 697 | * Format defined in SOP §4. |
| 698 | */ |
| 699 | private function parseClosureTemplate(string $body): array |
| 700 | { |
| 701 | // Spanish field label → English DB column. ORDER MATTERS: longest first |
| 702 | // so regex alternation prefers "Detalle Motivo 2" over "Detalle Motivo". |
| 703 | $fieldMap = [ |
| 704 | 'Código de cliente facturación' => 'billing_client_code', |
| 705 | 'Cambio de mantenedor' => 'change_of_maintenance_provider', |
| 706 | 'Recuperación cliente' => 'customer_recovery', |
| 707 | 'Detalle Motivo 2' => 'reason_details_2', |
| 708 | 'Motivo Principal' => 'reason_for_cancellation', |
| 709 | 'Detalle Motivo' => 'reason_details', |
| 710 | 'Acción' => 'action', |
| 711 | 'Región' => 'region', |
| 712 | ]; |
| 713 | |
| 714 | $marker = '=== CIERRE DE BAJA ==='; |
| 715 | $pos = stripos($body, $marker); |
| 716 | $block = $pos === false ? $body : substr($body, $pos + strlen($marker)); |
| 717 | |
| 718 | $labels = array_keys($fieldMap); |
| 719 | $alt = implode('|', array_map(fn ($l) => preg_quote($l, '/'), $labels)); |
| 720 | |
| 721 | // Match each label and capture its value up to the next label, a stray |
| 722 | // '===' marker, or end of block. iu = case + unicode, s = . spans newlines. |
| 723 | $pattern = '/('.$alt.')\s*:\s*(.*?)\s*(?=(?:'.$alt.')\s*:|===|$)/ius'; |
| 724 | |
| 725 | $out = []; |
| 726 | if (preg_match_all($pattern, $block, $matches, PREG_SET_ORDER)) { |
| 727 | foreach ($matches as $m) { |
| 728 | $matchedLabel = $m[1]; |
| 729 | $value = trim($m[2]); |
| 730 | |
| 731 | foreach ($fieldMap as $label => $dbCol) { |
| 732 | if (mb_strtolower($label, 'UTF-8') === mb_strtolower($matchedLabel, 'UTF-8')) { |
| 733 | $out[$dbCol] = $value; |
| 734 | break; |
| 735 | } |
| 736 | } |
| 737 | } |
| 738 | } |
| 739 | |
| 740 | if (isset($out['region'])) { |
| 741 | $code = $this->normalizeRegion($out['region']); |
| 742 | if ($code !== null) { |
| 743 | $out['region'] = $code; |
| 744 | } else { |
| 745 | unset($out['region']); |
| 746 | } |
| 747 | } |
| 748 | |
| 749 | if (isset($out['change_of_maintenance_provider'])) { |
| 750 | $v = $this->normalizeYesNo($out['change_of_maintenance_provider']); |
| 751 | $out['change_of_maintenance_provider'] = $v ?? 'No'; // SOP §4.2: unknown → No |
| 752 | } |
| 753 | |
| 754 | if (isset($out['customer_recovery'])) { |
| 755 | $out['customer_recovery'] = $this->normalizeYesNo($out['customer_recovery']); |
| 756 | } |
| 757 | |
| 758 | // Snap controlled-list values to canonical case so the modal dropdowns |
| 759 | // match what's stored (e.g. agent types "EXTERNO" → store "Externo"). |
| 760 | if (isset($out['reason_for_cancellation'])) { |
| 761 | $out['reason_for_cancellation'] = $this->snap($out['reason_for_cancellation'], ['Interno', 'Externo']); |
| 762 | } |
| 763 | if (isset($out['reason_details'])) { |
| 764 | $out['reason_details'] = $this->snap($out['reason_details'], self::REASON_DETAILS_VALUES); |
| 765 | } |
| 766 | if (isset($out['reason_details_2'])) { |
| 767 | $out['reason_details_2'] = $this->snap($out['reason_details_2'], [ |
| 768 | 'Precio - Desacuerdo con tarifa', 'Otros', |
| 769 | ]); |
| 770 | } |
| 771 | |
| 772 | return $out; |
| 773 | } |
| 774 | |
| 775 | /** |
| 776 | * Case-insensitive match against a canonical list. Returns the canonical |
| 777 | * spelling on hit; otherwise returns the input unchanged so unknown values |
| 778 | * are still preserved (just not normalized). |
| 779 | */ |
| 780 | private function snap(string $value, array $canonical): string |
| 781 | { |
| 782 | $needle = mb_strtolower(trim($value), 'UTF-8'); |
| 783 | foreach ($canonical as $option) { |
| 784 | if (mb_strtolower($option, 'UTF-8') === $needle) { |
| 785 | return $option; |
| 786 | } |
| 787 | } |
| 788 | |
| 789 | return $value; |
| 790 | } |
| 791 | |
| 792 | /** |
| 793 | * Decide which region (3-letter code) to persist for a leave row. |
| 794 | * |
| 795 | * A CONFIDENT Client Finder match wins outright — its region comes from the |
| 796 | * actual client billing record, so it overrides both the agent-entered |
| 797 | * closure-template "Región" and the Freshdesk instance the ticket arrived in |
| 798 | * (e.g. the lookup matches VAL even though the webhook was called with "mad"). |
| 799 | * Otherwise the legacy fallback applies: closure template (SOP §3.3) → |
| 800 | * Client Finder lookup → Freshdesk instance. |
| 801 | * |
| 802 | * @param string|null $templateRegion 3-letter code parsed from the closure note (or null) |
| 803 | * @param array $lookup the note's Client Finder `lookup` block |
| 804 | * @param array|null $entry the enclosing Client Finder note entry (carries the top-level `matched` flag) |
| 805 | * @param string $fallbackRegion the Freshdesk instance region code |
| 806 | */ |
| 807 | private function resolveRegion(?string $templateRegion, array $lookup, ?array $entry, string $fallbackRegion): string |
| 808 | { |
| 809 | $lookupRegion = isset($lookup['region']) ? $this->normalizeRegion((string) $lookup['region']) : null; |
| 810 | $lookupRegionKnown = $lookupRegion !== null && isset(self::REGIONS[$lookupRegion]); |
| 811 | // The webhook flags a confident hit on the lookup and/or the enclosing note entry. |
| 812 | $lookupMatched = ! empty($lookup['matched']) || (is_array($entry) && ! empty($entry['matched'])); |
| 813 | |
| 814 | if ($lookupMatched && $lookupRegionKnown) { |
| 815 | return $lookupRegion; |
| 816 | } |
| 817 | if ($templateRegion && isset(self::REGIONS[$templateRegion])) { |
| 818 | return $templateRegion; |
| 819 | } |
| 820 | if ($lookupRegionKnown) { |
| 821 | return $lookupRegion; |
| 822 | } |
| 823 | |
| 824 | return $fallbackRegion; |
| 825 | } |
| 826 | |
| 827 | /** |
| 828 | * Map a region string to its 3-letter code. Accepts the code itself or the |
| 829 | * Spanish region name in any common form (with/without accents, mixed case). |
| 830 | * Returns null if the input doesn't resolve to a known region. |
| 831 | */ |
| 832 | private function normalizeRegion(string $value): ?string |
| 833 | { |
| 834 | $aliases = [ |
| 835 | 'CAT' => ['cat', 'cataluna', 'catalunya', 'catalonia', 'catalan'], |
| 836 | 'MAD' => ['mad', 'madrid', 'comunidad de madrid'], |
| 837 | 'ALM' => ['alm', 'almeria', 'andalucia'], |
| 838 | 'BAL' => ['bal', 'baleares', 'islas baleares', 'balearic islands', 'illes balears'], |
| 839 | 'VAL' => ['val', 'valencia', 'comunidad valenciana', 'valenciana', 'comunitat valenciana'], |
| 840 | ]; |
| 841 | |
| 842 | $norm = strtr(mb_strtolower(trim($value), 'UTF-8'), [ |
| 843 | 'á' => 'a', 'é' => 'e', 'í' => 'i', 'ó' => 'o', 'ú' => 'u', 'ñ' => 'n', 'ü' => 'u', |
| 844 | ]); |
| 845 | $norm = preg_replace('/\s+/', ' ', $norm); |
| 846 | |
| 847 | foreach ($aliases as $code => $list) { |
| 848 | if (in_array($norm, $list, true)) { |
| 849 | return $code; |
| 850 | } |
| 851 | } |
| 852 | |
| 853 | return null; |
| 854 | } |
| 855 | |
| 856 | private function normalizeYesNo(string $value): ?string |
| 857 | { |
| 858 | $v = strtolower(trim($value)); |
| 859 | $v = strtr($v, ['í' => 'i', 'Í' => 'i']); |
| 860 | |
| 861 | if (in_array($v, ['si', 'yes', 'true', '1'], true)) { |
| 862 | return 'Sí'; |
| 863 | } |
| 864 | if (in_array($v, ['no', 'false', '0'], true)) { |
| 865 | return 'No'; |
| 866 | } |
| 867 | |
| 868 | return null; |
| 869 | } |
| 870 | |
| 871 | private function customField(array $ticket, string $key): ?string |
| 872 | { |
| 873 | $val = $ticket['custom_fields'][$key] ?? null; |
| 874 | if ($val === null || $val === '') { |
| 875 | return null; |
| 876 | } |
| 877 | |
| 878 | return is_string($val) ? trim($val) : (string) $val; |
| 879 | } |
| 880 | |
| 881 | private function cleanHtml(?string $html): ?string |
| 882 | { |
| 883 | if ($html === null || $html === '') { |
| 884 | return null; |
| 885 | } |
| 886 | |
| 887 | $clean = str_replace(["\n", "\r"], '', $html); |
| 888 | |
| 889 | return preg_replace('/\s+/', ' ', $clean); |
| 890 | } |
| 891 | } |