Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 103
0.00%
0 / 3
 CRAP
0.00%
0 / 1
BackfillEmailStatus
0.00%
0 / 103
0.00%
0 / 3
 930
0.00%
0 / 1
 handle
0.00%
0 / 72
0.00%
0 / 1
 420
 aggregateFinalStatus
0.00%
0 / 12
0.00%
0 / 1
 30
 appendEventToWebhook
0.00%
0 / 19
0.00%
0 / 1
 30
1<?php
2
3namespace App\Console\Commands;
4
5use App\Models\TblQuotations;
6use App\Models\TblSendgridWebhook;
7use Illuminate\Console\Command;
8use Illuminate\Support\Facades\Http;
9use Illuminate\Support\Facades\Log;
10
11/**
12 * FIRE-864 — re-polls SendGrid's Activity API for every quotation whose
13 * tracked status is still "processed" (or missing) and updates the local
14 * record with the real final status.
15 *
16 * Runs on a schedule (see Kernel.php) so the bug Jorge reported — emails
17 * that stay at "processed" forever when the webhook fails to deliver —
18 * gets corrected automatically. Also updates budget_status_id to 22
19 * ("Correo erróneo") when SendGrid confirms a bounce/drop.
20 *
21 * Kept separate from clear:email-processing because:
22 *   - clear:email-processing only handles rows where x_status is NOT NULL
23 *     and != 'Completed'. It ignores NULL x_status rows where the webhook
24 *     never fired at all.
25 *   - This command also targets those NULL rows, and handles the one-time
26 *     cleanup Jorge asked for (limpiar todos los emails en processed).
27 *
28 * Safe to run repeatedly: fetches at most --limit rows per run (default
29 * 200) to keep API usage bounded. Rate-limits itself to avoid tripping
30 * SendGrid's Activity API quotas.
31 */
32class BackfillEmailStatus extends Command
33{
34    protected $signature = 'email:backfill-status
35                            {--limit=200 : Maximum number of quotations to check per run}
36                            {--sleep-ms=200 : Milliseconds to wait between SendGrid API calls}
37                            {--dry-run : Report what would change without writing}';
38
39    protected $description = 'Re-poll SendGrid for all emails stuck in processed/null status and update Titan (FIRE-864)';
40
41    private const ERROR_EVENTS = ['deferred', 'bounce', 'dropped', 'spamreport', 'invalid', 'blocked'];
42
43    private const CORREO_ERRONEO_BUDGET_STATUS_ID = 22;
44
45    public function handle(): int
46    {
47        $limit = (int) $this->option('limit');
48        $sleepMs = (int) $this->option('sleep-ms');
49        $dryRun = (bool) $this->option('dry-run');
50
51        $apiKey = config('services.sendgrid.api_key');
52        if (empty($apiKey)) {
53            $this->error('services.sendgrid.api_key is not configured. Aborting.');
54
55            return 1;
56        }
57
58        $rows = TblQuotations::query()
59            ->whereNotNull('x_message_id')
60            ->where(function ($q): void {
61                $q->whereNull('x_status')
62                    ->orWhereIn('x_status', ['processed', 'Processing']);
63            })
64            ->orderByDesc('updated_at')
65            ->limit($limit)
66            ->get(['id', 'email', 'x_message_id', 'x_status', 'budget_status_id']);
67
68        if ($rows->isEmpty()) {
69            $this->info('No quotations match the backfill criteria.');
70
71            return 0;
72        }
73
74        $this->info("Checking {$rows->count()} quotations…");
75
76        $stats = ['checked' => 0, 'updated' => 0, 'bounced' => 0, 'delivered' => 0, 'still_processed' => 0, 'failed' => 0];
77
78        foreach ($rows as $row) {
79            $stats['checked']++;
80
81            $emails = array_filter(array_map('trim', explode(',', (string) $row->email)));
82            if (empty($emails) || empty($row->x_message_id)) {
83                continue;
84            }
85
86            $finalStatuses = [];
87
88            foreach ($emails as $email) {
89                $query = urlencode('msg_id LIKE "'.$row->x_message_id.'%" AND to_email IN ("'.$email.'")');
90
91                try {
92                    $response = Http::withToken($apiKey)
93                        ->timeout(15)
94                        ->get("https://api.sendgrid.com/v3/messages?limit=10&query={$query}");
95                } catch (\Throwable $e) {
96                    Log::channel('clear_email_processing')->warning("email:backfill-status — SendGrid API error for quote {$row->id}: ".$e->getMessage());
97                    $stats['failed']++;
98
99                    continue;
100                }
101
102                if (! $response->ok()) {
103                    Log::channel('clear_email_processing')->warning("email:backfill-status — SendGrid returned {$response->status()} for quote {$row->id}.");
104                    $stats['failed']++;
105
106                    continue;
107                }
108
109                $body = $response->json();
110                $status = $body['messages'][0]['status'] ?? 'processed';
111                $finalStatuses[] = $status;
112
113                // Mirror the event-shape the webhook would have delivered
114                if ($status === 'delivered' && isset($body['messages'][0])) {
115                    $this->appendEventToWebhook($row->x_message_id, $body['messages'][0], $dryRun);
116                }
117
118                if ($sleepMs > 0) {
119                    usleep($sleepMs * 1000);
120                }
121            }
122
123            $newStatus = $this->aggregateFinalStatus($finalStatuses, count($emails));
124
125            $updatePayload = [];
126            if ($newStatus !== null && $newStatus !== $row->x_status) {
127                $updatePayload['x_status'] = $newStatus;
128            }
129
130            if (
131                in_array($newStatus, ['Error', 'Error - Bounce'], true)
132                && (int) $row->budget_status_id !== self::CORREO_ERRONEO_BUDGET_STATUS_ID
133            ) {
134                $updatePayload['budget_status_id'] = self::CORREO_ERRONEO_BUDGET_STATUS_ID;
135                $stats['bounced']++;
136            }
137
138            if ($newStatus === 'Completed') {
139                $stats['delivered']++;
140            }
141
142            if ($newStatus === 'Processing') {
143                $stats['still_processed']++;
144            }
145
146            if (! empty($updatePayload)) {
147                $stats['updated']++;
148                if ($dryRun) {
149                    $this->line("[dry-run] quote {$row->id} (msg {$row->x_message_id}): ".json_encode($updatePayload));
150                } else {
151                    TblQuotations::where('id', $row->id)->update($updatePayload);
152                    Log::channel('clear_email_processing')->info("email:backfill-status updated quote {$row->id}: ".json_encode($updatePayload));
153                }
154            }
155        }
156
157        $this->info(sprintf(
158            'Backfill complete: checked=%d updated=%d bounced=%d delivered=%d still_processed=%d failed=%d',
159            $stats['checked'], $stats['updated'], $stats['bounced'],
160            $stats['delivered'], $stats['still_processed'], $stats['failed']
161        ));
162
163        Log::channel('clear_email_processing')->info('email:backfill-status finished: '.json_encode($stats));
164
165        return 0;
166    }
167
168    /**
169     * Collapse the per-recipient final statuses into the single x_status
170     * value Titan expects on tbl_quotations.
171     */
172    private function aggregateFinalStatus(array $statuses, int $totalEmails): ?string
173    {
174        if (empty($statuses)) {
175            return null;
176        }
177
178        $delivered = count(array_filter($statuses, fn ($s) => $s === 'delivered'));
179        $errored = count(array_filter($statuses, fn ($s) => in_array($s, self::ERROR_EVENTS, true)));
180        $bounced = count(array_filter($statuses, fn ($s) => in_array($s, ['bounce', 'dropped', 'blocked'], true)));
181
182        if ($delivered === $totalEmails) {
183            return 'Completed';
184        }
185        if ($bounced > 0) {
186            return 'Error - Bounce';
187        }
188        if ($errored > 0) {
189            return 'Error';
190        }
191
192        return 'Processing';
193    }
194
195    /**
196     * Store the retrieved event in tbl_sendgrid_webhook so the "Eventos de
197     * correo electrónico" modal reflects reality without another API call.
198     */
199    private function appendEventToWebhook(string $xMessageId, array $message, bool $dryRun): void
200    {
201        $row = TblSendgridWebhook::where('x_message_id', $xMessageId)->first();
202        if (! $row) {
203            return;
204        }
205
206        $events = json_decode((string) $row->json_body, true) ?: [];
207
208        // Shape-match the webhook event format the UI expects
209        $event = [
210            'email' => $message['to_email'] ?? null,
211            'event' => $message['status'] ?? 'processed',
212            'timestamp' => isset($message['last_event_time']) ? strtotime((string) $message['last_event_time']) : null,
213            'open' => $message['opens_count'] ?? 0,
214            'click' => $message['clicks_count'] ?? 0,
215            'source' => 'activity-api-backfill',
216        ];
217
218        $events[] = $event;
219
220        if ($dryRun) {
221            return;
222        }
223
224        TblSendgridWebhook::where('x_message_id', $xMessageId)->update([
225            'json_body' => json_encode($events),
226            'updated_at' => date('Y-m-d H:i:s'),
227        ]);
228    }
229}