Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
81.60% covered (warning)
81.60%
102 / 125
0.00% covered (danger)
0.00%
0 / 1
CRAP
0.00% covered (danger)
0.00%
0 / 1
ClearEmailProcessing
81.60% covered (warning)
81.60%
102 / 125
0.00% covered (danger)
0.00%
0 / 1
49.97
0.00% covered (danger)
0.00%
0 / 1
 handle
81.60% covered (warning)
81.60%
102 / 125
0.00% covered (danger)
0.00%
0 / 1
49.97
1<?php
2
3namespace App\Console\Commands;
4
5use App\Models\TblQuotations;
6use App\Models\TblSendgridWebhook;
7use App\Services\ResultCache;
8use Illuminate\Console\Command;
9use Illuminate\Support\Facades\DB;
10use Illuminate\Support\Facades\Http;
11use Illuminate\Support\Facades\Log;
12
13class ClearEmailProcessing extends Command
14{
15    /**
16     * The name and signature of the console command.
17     *
18     * @var string
19     */
20    protected $signature = 'clear:email-processing';
21
22    /**
23     * The console command description.
24     *
25     * @var string
26     */
27    protected $description = 'Clear email processing';
28
29    /**
30     * Execute the console command.
31     */
32    public function handle(): void
33    {
34        try {
35
36            $startedAt = date('Y-m-d H:i:s');
37
38            $query = "SELECT
39                        id,
40                        email,
41                        x_message_id,
42                        x_status,
43                        budget_status_id
44                    FROM tbl_quotations
45                    WHERE (x_status IS NOT NULL AND x_status != 'Completed')
46                    AND x_message_id IS NOT NULL";
47
48            $result = DB::select($query);
49
50            $emailErrors = ['deferred', 'bounce', 'dropped', 'spamreport', 'invalid'];
51
52            if (count($result) > 0) {
53
54                $quoteIds = [];
55
56                for ($i = 0; $i < count($result); $i++) {
57                    $emails = explode(',', str_replace(' ', '', $result[$i]->email));
58                    $xMessageId = $result[$i]->x_message_id;
59                    $xOrderStatus = $result[$i]->x_status;
60
61                    $sendGrid = TblSendgridWebhook::where('x_message_id', $xMessageId)->first();
62
63                    if ($sendGrid) {
64
65                        $events = json_decode((string) $sendGrid->json_body, true);
66                        $xStatus = 'processed';
67                        $isProcessed = 0;
68                        $isDelivered = 0;
69                        $isError = 0;
70                        $eventsUpdated = false;
71                        // FIRE-1280: only a permanent failure (bounce/dropped/
72                        // spam/invalid) justifies reclassifying the quote as
73                        // "fallo envío - correo erróneo" (21). A transient
74                        // "deferred" must not — the mail may still deliver, and
75                        // the webhook path likewise ignores deferred.
76                        $hasPermanentFailure = false;
77
78                        foreach ($emails as $email) {
79
80                            $emailEvents = array_filter($events, fn (array $event): bool => strtolower((string) $event['email']) === strtolower($email));
81
82                            $statuses = array_unique(array_column($emailEvents, 'event'));
83                            $eventCount = count($statuses);
84
85                            if ($eventCount === 1 && in_array('processed', $statuses)) {
86                                $xStatus = 'processed';
87                            }
88
89                            if ($eventCount == 2) {
90                                if (in_array('processed', $statuses) && in_array('delivered', $statuses)) {
91                                    $xStatus = 'delivered';
92                                }
93
94                                foreach ($emailErrors as $e) {
95                                    if (in_array('processed', $statuses) && in_array($e, $statuses)) {
96                                        $xStatus = $e;
97                                    }
98                                }
99                            } elseif ($eventCount > 2) {
100                                if (in_array('processed', $statuses) && in_array('delivered', $statuses)) {
101                                    $xStatus = 'delivered';
102                                }
103
104                                if ($xStatus != 'delivered') {
105                                    foreach ($emailErrors as $e) {
106                                        if (in_array('processed', $statuses) && in_array($e, $statuses)) {
107                                            $xStatus = $e;
108                                            break;
109                                        }
110                                    }
111                                }
112                            }
113
114                            if ($xStatus == 'processed') {
115                                $query = urlencode('msg_id LIKE "'.$xMessageId.'%" AND to_email IN ("'.$email.'")');
116
117                                $response = Http::withToken(config('services.sendgrid.api_key'))
118                                    ->get("https://api.sendgrid.com/v3/messages?limit=10&query={$query}");
119
120                                $data = $response->json();
121
122                                if (! empty($data['messages'])) {
123                                    $xStatus = $data['messages'][0]['status'] ?? 'processed';
124
125                                    if ($xStatus == 'delivered') {
126                                        $data['messages'][0]['timestamp'] = strtotime((string) $data['messages'][0]['last_event_time']);
127                                        unset($data['messages'][0]['last_event_time']);
128
129                                        $data['messages'][0]['email'] = $data['messages'][0]['to_email'];
130                                        unset($data['messages'][0]['to_email']);
131
132                                        $data['messages'][0]['event'] = $data['messages'][0]['status'];
133                                        unset($data['messages'][0]['status']);
134
135                                        $data['messages'][0]['click'] = $data['messages'][0]['clicks_count'];
136                                        unset($data['messages'][0]['clicks_count']);
137
138                                        $data['messages'][0]['open'] = $data['messages'][0]['opens_count'];
139                                        unset($data['messages'][0]['opens_count']);
140
141                                        array_push($events, $data['messages'][0]);
142                                        $eventsUpdated = true;
143                                    }
144                                }
145                            }
146
147                            if ($xStatus == 'processed') {
148                                $isProcessed++;
149                            } elseif ($xStatus == 'delivered') {
150                                $isDelivered++;
151                            } else {
152                                $isError++;
153                                if (in_array($xStatus, ['bounce', 'dropped', 'spamreport', 'invalid'], true)) {
154                                    $hasPermanentFailure = true;
155                                }
156                            }
157                        }
158
159                        if ($eventsUpdated) {
160                            TblSendgridWebhook::where('x_message_id', $xMessageId)->update(
161                                [
162                                    'updated_at' => date('Y-m-d H:i:s'),
163                                    'json_body' => json_encode($events),
164                                ]
165                            );
166                        }
167
168                        if (count($emails) == $isDelivered) {
169                            $xStatus = 'Completed';
170                        } elseif ($isProcessed > 0) {
171                            $xStatus = 'Processing';
172                        } elseif ($isError > 0) {
173                            if ($xStatus == 'bounce') {
174                                $xStatus = 'Error - Bounce';
175                            } else {
176                                $xStatus = 'Error';
177                            }
178                        }
179
180                        if ($xOrderStatus != $xStatus) {
181                            $updatePayload = ['x_status' => $xStatus];
182
183                            // FIRE-1280: when SendGrid confirms the email was
184                            // bounced / dropped, the send WAS attempted, so the
185                            // quotation is "Fallo envío - correo erróneo"
186                            // (budget_status_id = 21), not "Correo erróneo" (22,
187                            // reserved for addresses we never tried to send to).
188                            // FIRE-1072: don't clobber terminal/in-progress rows
189                            // (Aceptado=3, Rechazado=7, En curso=12); leave rows
190                            // already at 21 untouched.
191                            $protectedStatuses = [3, 7, 12, 21];
192                            if ($hasPermanentFailure
193                                && in_array($xStatus, ['Error', 'Error - Bounce'], true)
194                                && ! in_array((int) $result[$i]->budget_status_id, $protectedStatuses, true)) {
195                                $updatePayload['budget_status_id'] = 21;
196                            }
197
198                            // FIRE-2115: every write to tbl_quotations stamps
199                            // updated_at (TblQuotationsBuilder), so re-saving
200                            // an unchanged status every 10 minutes moved
201                            // updated_at and added an audit row each run.
202                            // Fields not in the SELECT above count as changed.
203                            $changed = array_filter(
204                                $updatePayload,
205                                fn ($value, $field) => ! property_exists($result[$i], $field)
206                                    || (string) $result[$i]->{$field} !== (string) $value,
207                                ARRAY_FILTER_USE_BOTH
208                            );
209                            if ($changed !== []) {
210                                TblQuotations::where('id', $result[$i]->id)->update($updatePayload);
211                            }
212
213                            Log::channel('clear_email_processing')->info($xMessageId.': OK');
214                        }
215                    }
216                }
217
218                if (count($quoteIds) > 0) {
219                    TblQuotations::whereIn('id', $quoteIds)->update(
220                        [
221                            'x_status' => null,
222                            'x_message_id' => null,
223                        ]
224                    );
225
226                    TblSendgridWebhook::whereIn('quotation_id', $quoteIds)->delete();
227                }
228
229            }
230
231            $query = "SELECT 
232                        a.x_message_id
233                    FROM 
234                        tbl_quotations a 
235                    LEFT JOIN 
236                        tbl_sendgrid_webhook b 
237                        ON a.x_message_id = b.x_message_id 
238                    WHERE 
239                        a.x_status = 'Processing'
240                        AND a.x_status IS NOT NULL
241                        AND (b.updated_at < NOW() - INTERVAL 30 DAY OR b.x_message_id IS NULL)";
242
243            $result = DB::select($query);
244
245            $idsCollection = collect($result)->pluck('x_message_id');
246
247            $idsString = $idsCollection->map(fn ($id) => "'".$id."'")
248                ->implode(',');
249
250            $totalCount = $idsCollection->count();
251
252            if ($totalCount > 0) {
253                $query = "DELETE FROM tbl_sendgrid_webhook
254                            WHERE x_message_id IN ({$idsString})";
255
256                $result = DB::select($query);
257
258                $query = "UPDATE tbl_quotations 
259                            SET x_message_id = NULL, x_status = NULL, updated_at = '".date('Y-m-d H:i:s')."'
260                            WHERE x_message_id IN ({$idsString})";
261
262                $result = DB::select($query);
263            }
264
265            TblQuotations::whereNull('x_message_id')->where('x_status', 'Processing')->update(['x_status' => null]);
266
267            // FIRE-1145: was Cache::flush() — email status is shown in list_quotations.
268            ResultCache::forgetDomain('quotations');
269
270        } catch (\Exception $e) {
271            Log::channel('clear_email_processing')->error($e->getMessage());
272        }
273    }
274}