Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 163
0.00%
0 / 8
 CRAP
0.00%
0 / 1
SendgridActivitySync
0.00%
0 / 163
0.00%
0 / 8
 2070
0.00%
0 / 1
 handle
0.00%
0 / 61
0.00%
0 / 1
 132
 fetchWindow
0.00%
0 / 38
0.00%
0 / 1
 72
 handleNonOkResponse
0.00%
0 / 13
0.00%
0 / 1
 12
 dedupeByMsgId
0.00%
0 / 9
0.00%
0 / 1
 20
 buildWebhookTypeIndex
0.00%
0 / 21
0.00%
0 / 1
 72
 classify
0.00%
0 / 6
0.00%
0 / 1
 30
 classifyByType
0.00%
0 / 8
0.00%
0 / 1
 30
 normalize
0.00%
0 / 7
0.00%
0 / 1
 2
1<?php
2
3namespace App\Console\Commands;
4
5use App\Models\TblSendgridActivity;
6use App\Models\TblSendgridWebhook;
7use Carbon\Carbon;
8use Illuminate\Console\Command;
9use Illuminate\Http\Client\Response;
10use Illuminate\Support\Facades\Http;
11use Illuminate\Support\Facades\Log;
12
13class SendgridActivitySync extends Command
14{
15    protected $signature = 'sendgrid:sync-activity
16                            {--date= : UTC calendar day to sync (YYYY-MM-DD). Defaults to yesterday.}';
17
18    protected $description = 'Pull SendGrid Email Activity for a given UTC day, classify each message by subject, and upsert into tbl_sendgrid_activity.';
19
20    /**
21     * SendGrid Activity API hard cap. /v3/messages returns at most 1000 rows
22     * per query with no offset pagination — to exceed 1000 we narrow the
23     * time window and recurse.
24     */
25    private const ACTIVITY_API_PAGE_LIMIT = 1000;
26
27    /**
28     * Stop recursing into time-bucket halves once a bucket is this short.
29     * Sub-minute windows are almost never going to contain >1000 messages;
30     * if they do, the extra rows past 1000 are dropped and we log a warning.
31     */
32    private const MIN_BUCKET_SECONDS = 60;
33
34    /**
35     * tbl_sendgrid_webhook.type (stamped at send time, keyed by x_message_id)
36     * → canonical type stored in tbl_sendgrid_activity.
37     *
38     * Each send site in the codebase stamps one of these values right after
39     * SendGrid returns 202. That stamp is the authoritative source of truth
40     * for classification — we look it up here so the sync doesn't need to
41     * guess from email subject text (which is dynamic for customer-facing
42     * sends and could drift if templates change for internal sends).
43     *
44     * Send sites that produce each value (audited 2026-05):
45     *   sendToClient        → Quotations::send_email_to_client (+ backup mailer)
46     *   followUps           → Quotations::send_email_follow_ups (+ backup mailer)
47     *   approval            → Quotations::send_for_approval (Q.php:429)
48     *   approval_margin     → Quotations::send_approval_margin_notification (Q.php:601)
49     *   approved            → Quotations::send_approved_notification (Q.php:871)
50     *   rejected            → Quotations::send_rejected_notification (Q.php:1144)
51     *   assignment          → Quotations::send_assignment_notification (Q.php:2452)
52     *   acceptance          → Quotations::send_acceptance_notification (Q.php:8245)
53     *                       + Commands\ResendAcceptanceEmails
54     *   permission_request  → Quotations::request_permission_commercial (Q.php:10077)
55     *   itv_reminder        → Itv.php + Commands\ItvEmailReminders
56     *   invoice             → Services\FacturasService
57     *   invoice_credit_days → Commands\SendEmailInvoiceNewCreditDays
58     *   finance_report      → Commands\SendFinanceReport
59     *   g3w_warnings        → Commands\SendG3WEmailReminders
60     *   ai_email            → AI::send_email
61     */
62    private const WEBHOOK_TYPE_MAP = [
63        // Customer-facing (already stamped today)
64        'sendToClient' => 'send_to_client',
65        'followUps' => 'follow_up',
66
67        // Internal notifications (Step 2 — add stamps at send sites)
68        'approval' => 'approval',
69        'approval_margin' => 'approval',
70        'approved' => 'approval',
71        'rejected' => 'approval',
72        'assignment' => 'assignment',
73        'acceptance' => 'acceptance',
74        'permission_request' => 'permission_request',
75
76        // Domain-specific reminders / reports
77        'itv_reminder' => 'itv_reminder',
78        'invoice' => 'invoice_reminder',
79        'invoice_credit_days' => 'invoice_reminder',
80        'finance_report' => 'finance_report',
81        'g3w_warnings' => 'g3w_warnings',
82        'ai_email' => 'ai_email',
83    ];
84
85    /**
86     * Subject-keyword → type. Used as a fallback when no tbl_sendgrid_webhook
87     * row matches (i.e. for internal notification emails whose subjects come
88     * from server/resources/lang/es/language.php — those have predictable
89     * static text). Order matters: the first matching group wins, so put
90     * narrow patterns above broad fallbacks. Each haystack is lower-cased and
91     * Spanish-accent-stripped before comparison, so the needles below must
92     * already be in that normalized form.
93     */
94    private const TYPE_PATTERNS = [
95        'approval' => [
96            'necesita tu aprobacion',     // send_for_approval / send_approval_margin
97            'ha sido aprobado',            // send_approved_notification
98            'ha sido rechazado',           // send_rejected_notification
99        ],
100        'acceptance' => [
101            'marcado como aceptado',       // send_acceptance_notification
102        ],
103        'follow_up' => [
104            'resumen de notificaciones',   // send_follow_up_notification (daily digest)
105            'en estado de "solicitud"',    // send_request_notification (curly quotes)
106            'en estado de solicitud',      // send_request_notification (straight quotes)
107        ],
108        'permission_request' => [
109            'peticion de cambio de comercial',
110        ],
111        'g3w_warnings' => [
112            'revision de warnings en g3w', // SendG3WEmailReminders
113        ],
114        'invoice_reminder' => [
115            'condiciones comerciales',     // SendEmailInvoiceNewCreditDays
116            'envio de factura',            // FacturasService
117            'factura',
118            'invoice',
119        ],
120        'itv_reminder' => [
121            'inspeccion itv',
122            'inspeccion de mantenimiento',
123            'recordatorio urgente: inspeccion',
124            'cita inspeccion',
125            'cita de inspeccion',
126        ],
127        'assignment' => [
128            'te han asignado el presupuesto',
129            'has creado un nuevo presupuesto',
130        ],
131        // Broad fallback: any other quotation-related subject. Must stay LAST
132        // so the more-specific internal-notification patterns above win first.
133        'send_to_client' => [
134            'presupuesto',
135            'pedido',
136        ],
137    ];
138
139    public function handle(): int
140    {
141        $apiKey = config('services.sendgrid.api_key');
142        if (empty($apiKey)) {
143            $this->error('services.sendgrid.api_key is not configured. Aborting.');
144
145            return 1;
146        }
147
148        $dateOpt = $this->option('date');
149        try {
150            $day = $dateOpt
151                ? Carbon::createFromFormat('Y-m-d', $dateOpt, 'UTC')->startOfDay()
152                : Carbon::yesterday('UTC')->startOfDay();
153        } catch (\Throwable $e) {
154            $this->error("Invalid --date value '{$dateOpt}'. Expected YYYY-MM-DD.");
155
156            return 1;
157        }
158
159        $start = $day->copy();
160        $end = $day->copy()->endOfDay();
161
162        $this->info("Syncing SendGrid activity for {$day->toDateString()} (UTC)…");
163
164        $messages = $this->fetchWindow($apiKey, $start, $end);
165
166        if ($messages === null) {
167            $this->error('SendGrid API failed; see log channel email_log for details.');
168
169            return 2;
170        }
171
172        $stats = ['fetched' => count($messages), 'inserted' => 0, 'updated' => 0];
173        $perType = [];
174
175        $webhookTypeIndex = $this->buildWebhookTypeIndex(
176            array_filter(array_map(fn ($m) => $m['msg_id'] ?? null, $messages))
177        );
178
179        foreach ($messages as $msg) {
180            $msgId = (string) ($msg['msg_id'] ?? '');
181            if ($msgId === '') {
182                continue;
183            }
184
185            $subject = (string) ($msg['subject'] ?? '');
186            $type = $this->classify($msgId, $subject, $webhookTypeIndex);
187            $perType[$type] = ($perType[$type] ?? 0) + 1;
188
189            $existing = TblSendgridActivity::where('msg_id', $msgId)->exists();
190
191            TblSendgridActivity::updateOrCreate(
192                ['msg_id' => $msgId],
193                [
194                    'from_email' => $msg['from_email'] ?? null,
195                    'to_email' => $msg['to_email'] ?? null,
196                    'subject' => $subject !== '' ? $subject : null,
197                    'type' => $type,
198                    'status' => $msg['status'] ?? null,
199                    'opens_count' => (int) ($msg['opens_count'] ?? 0),
200                    'clicks_count' => (int) ($msg['clicks_count'] ?? 0),
201                    'last_event_time' => isset($msg['last_event_time'])
202                        ? Carbon::parse((string) $msg['last_event_time'])->utc()
203                        : null,
204                    'sent_date' => $day->toDateString(),
205                ]
206            );
207
208            $existing ? $stats['updated']++ : $stats['inserted']++;
209        }
210
211        ksort($perType);
212        $perTypeStr = implode(' ', array_map(fn ($k, $v) => "{$k}={$v}", array_keys($perType), $perType));
213
214        $this->info(sprintf(
215            'Done: fetched=%d inserted=%d updated=%d',
216            $stats['fetched'], $stats['inserted'], $stats['updated']
217        ));
218        if ($perTypeStr !== '') {
219            $this->info("By type: {$perTypeStr}");
220        }
221
222        Log::channel('email_log')->info('sendgrid:sync-activity finished: '.json_encode([
223            'date' => $day->toDateString(),
224            'stats' => $stats,
225            'per_type' => $perType,
226        ]));
227
228        return 0;
229    }
230
231    /**
232     * Fetch every message in [$start, $end] from SendGrid's Activity API,
233     * narrowing the window via halving when a single query saturates the
234     * 1000-row page limit. Returns null on hard API failure (so the caller
235     * can exit non-zero); returns [] when the window is genuinely empty.
236     */
237    private function fetchWindow(string $apiKey, Carbon $start, Carbon $end): ?array
238    {
239        $query = sprintf(
240            'last_event_time BETWEEN TIMESTAMP "%s" AND TIMESTAMP "%s"',
241            $start->toIso8601ZuluString(),
242            $end->toIso8601ZuluString(),
243        );
244
245        try {
246            $response = Http::withToken($apiKey)
247                ->timeout(30)
248                ->get('https://api.sendgrid.com/v3/messages', [
249                    'limit' => self::ACTIVITY_API_PAGE_LIMIT,
250                    'query' => $query,
251                ]);
252        } catch (\Throwable $e) {
253            Log::channel('email_log')->warning('sendgrid:sync-activity API exception: '.$e->getMessage(), [
254                'start' => $start->toIso8601ZuluString(),
255                'end' => $end->toIso8601ZuluString(),
256            ]);
257
258            return null;
259        }
260
261        if (! $response->ok()) {
262            return $this->handleNonOkResponse($response, $start, $end);
263        }
264
265        $body = $response->json();
266        $messages = is_array($body) ? ($body['messages'] ?? []) : [];
267
268        // Saturated the 1000-row cap → split the window and merge the halves.
269        // Stop splitting once the bucket gets implausibly small.
270        if (count($messages) >= self::ACTIVITY_API_PAGE_LIMIT) {
271            $spanSeconds = $end->diffInSeconds($start);
272            if ($spanSeconds <= self::MIN_BUCKET_SECONDS) {
273                Log::channel('email_log')->warning('sendgrid:sync-activity hit page cap on minimum bucket — some events may be dropped', [
274                    'start' => $start->toIso8601ZuluString(),
275                    'end' => $end->toIso8601ZuluString(),
276                ]);
277
278                return $messages;
279            }
280
281            $mid = $start->copy()->addSeconds((int) floor($spanSeconds / 2));
282            $left = $this->fetchWindow($apiKey, $start, $mid);
283            if ($left === null) {
284                return null;
285            }
286            $right = $this->fetchWindow($apiKey, $mid->copy()->addSecond(), $end);
287            if ($right === null) {
288                return null;
289            }
290
291            return $this->dedupeByMsgId(array_merge($left, $right));
292        }
293
294        return $messages;
295    }
296
297    /**
298     * 429 means "back off"; everything else 4xx/5xx means "we have no idea
299     * how to proceed, log it and tell the caller to bail."
300     */
301    private function handleNonOkResponse(Response $response, Carbon $start, Carbon $end): ?array
302    {
303        $status = $response->status();
304
305        if ($status === 429) {
306            $retryAfter = max(1, min(120, (int) ($response->header('Retry-After') ?: 30)));
307            $this->warn("  SendGrid 429 — sleeping {$retryAfter}s before retrying window.");
308            sleep($retryAfter);
309
310            return $this->fetchWindow(config('services.sendgrid.api_key'), $start, $end);
311        }
312
313        Log::channel('email_log')->warning('sendgrid:sync-activity non-OK response', [
314            'status' => $status,
315            'body' => $response->body(),
316            'start' => $start->toIso8601ZuluString(),
317            'end' => $end->toIso8601ZuluString(),
318        ]);
319
320        return null;
321    }
322
323    /**
324     * Halving on second boundaries can leave a 1-second overlap if both
325     * halves see the same edge-case message. Dedupe so upserts stay O(N).
326     */
327    private function dedupeByMsgId(array $messages): array
328    {
329        $seen = [];
330        $out = [];
331        foreach ($messages as $m) {
332            $id = $m['msg_id'] ?? null;
333            if ($id === null || isset($seen[$id])) {
334                continue;
335            }
336            $seen[$id] = true;
337            $out[] = $m;
338        }
339
340        return $out;
341    }
342
343    /**
344     * Build a hash of bare-x_message_id → canonical type by pre-loading every
345     * tbl_sendgrid_webhook row whose x_message_id is a prefix of any msg_id
346     * in this batch. One query instead of N round-trips.
347     *
348     * SendGrid Activity API msg_ids are formatted as
349     *   "<x_message_id>.<filter>.<timestamp>.<recipient_index>"
350     * (see Quotations.php BackfillEmailStatus, which already relies on this).
351     * We take everything before the first '.' as the candidate bare ID and
352     * look it up. Falls back to a literal-equality check for edge cases
353     * where the suffix isn't dot-separated.
354     */
355    private function buildWebhookTypeIndex(array $msgIds): array
356    {
357        if (empty($msgIds)) {
358            return [];
359        }
360
361        $candidates = [];
362        foreach ($msgIds as $msgId) {
363            $msgId = (string) $msgId;
364            $bare = strstr($msgId, '.', true);
365            if ($bare !== false && $bare !== '') {
366                $candidates[$bare] = true;
367            }
368            // Also try the full id in case there's no dot suffix.
369            $candidates[$msgId] = true;
370        }
371
372        if (empty($candidates)) {
373            return [];
374        }
375
376        $rows = TblSendgridWebhook::query()
377            ->whereIn('x_message_id', array_keys($candidates))
378            ->whereIn('type', array_keys(self::WEBHOOK_TYPE_MAP))
379            ->get(['x_message_id', 'type']);
380
381        $index = [];
382        foreach ($rows as $row) {
383            $canonical = self::WEBHOOK_TYPE_MAP[$row->type] ?? null;
384            if ($canonical !== null) {
385                $index[(string) $row->x_message_id] = $canonical;
386            }
387        }
388
389        return $index;
390    }
391
392    /**
393     * Authoritative-first classifier. Prefers the webhook stamp for sends
394     * whose subject is dynamic per-company (send_to_client, follow_ups),
395     * falls back to subject-keyword matching for the internal-notification
396     * sends whose subjects come from language.php.
397     */
398    private function classify(string $msgId, string $subject, array $webhookTypeIndex): string
399    {
400        $bare = strstr($msgId, '.', true);
401        if ($bare !== false && $bare !== '' && isset($webhookTypeIndex[$bare])) {
402            return $webhookTypeIndex[$bare];
403        }
404        if (isset($webhookTypeIndex[$msgId])) {
405            return $webhookTypeIndex[$msgId];
406        }
407
408        return $this->classifyByType($subject);
409    }
410
411    private function classifyByType(string $subject): string
412    {
413        if ($subject === '') {
414            return 'other';
415        }
416
417        $normalized = $this->normalize($subject);
418
419        foreach (self::TYPE_PATTERNS as $type => $needles) {
420            foreach ($needles as $needle) {
421                if (str_contains($normalized, $needle)) {
422                    return $type;
423                }
424            }
425        }
426
427        return 'other';
428    }
429
430    private function normalize(string $s): string
431    {
432        $lower = mb_strtolower($s, 'UTF-8');
433
434        return strtr($lower, [
435            'á' => 'a', 'é' => 'e', 'í' => 'i', 'ó' => 'o', 'ú' => 'u',
436            'ñ' => 'n', 'ü' => 'u',
437            '“' => '"', '”' => '"', '«' => '"', '»' => '"',
438            'ʼ' => "'", '’' => "'", '‘' => "'",
439        ]);
440    }
441}