Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 111
0.00%
0 / 1
 CRAP
0.00%
0 / 1
ClearEmailProcessing
0.00%
0 / 111
0.00%
0 / 1
 1260
0.00%
0 / 1
 handle
0.00%
0 / 111
0.00%
0 / 1
 1260
1<?php
2
3namespace App\Console\Commands;
4
5use App\Models\TblQuotations;
6use App\Models\TblSendgridWebhook;
7use Illuminate\Console\Command;
8use Illuminate\Support\Facades\Cache;
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                    FROM tbl_quotations 
44                    WHERE (x_status IS NOT NULL AND x_status != 'Completed') 
45                    AND x_message_id IS NOT NULL";
46
47            $result = DB::select($query);
48
49            $emailErrors = ['deferred', 'bounce', 'dropped', 'spamreport', 'invalid'];
50            
51            if(count($result) > 0){
52
53                $quoteIds = [];
54
55                for ($i = 0; $i < count($result); $i++) {
56                    $emails = explode(',', str_replace(' ', '', $result[$i]->email));
57                    $xMessageId = $result[$i]->x_message_id;
58                    $xOrderStatus = $result[$i]->x_status;
59
60                    $sendGrid = TblSendgridWebhook::where('x_message_id', $xMessageId)->first();
61
62                    if ($sendGrid) {
63
64                        $events = json_decode((string) $sendGrid->json_body, true);
65                        $xStatus = "processed";
66                        $isProcessed = 0;
67                        $isDelivered = 0;
68                        $isError = 0;
69                        $eventsUpdated = false;
70
71                        foreach ($emails as $email) {
72
73                            $emailEvents = array_filter($events, fn(array $event): bool => strtolower((string) $event['email']) === strtolower($email));
74
75                            $statuses = array_unique(array_column($emailEvents, 'event'));
76                            $eventCount = count($statuses);
77
78                            if ($eventCount === 1 && in_array('processed', $statuses)) {
79                                $xStatus = 'processed';
80                            }
81
82                            if ($eventCount == 2) {
83                                if (in_array('processed', $statuses) && in_array('delivered', $statuses)) {
84                                    $xStatus = 'delivered';
85                                }
86
87                                foreach ($emailErrors as $e) {
88                                    if (in_array('processed', $statuses) && in_array($e, $statuses)) {
89                                        $xStatus = $e;
90                                    }
91                                }
92                            } elseif ($eventCount > 2) {
93                                if (in_array('processed', $statuses) && in_array('delivered', $statuses)) {
94                                    $xStatus = 'delivered';
95                                }
96
97                                if ($xStatus != 'delivered') {
98                                    foreach ($emailErrors as $e) {
99                                        if (in_array('processed', $statuses) && in_array($e, $statuses)) {
100                                            $xStatus = $e;
101                                            break;
102                                        }
103                                    }
104                                }
105                            }
106
107                            if ($xStatus == 'processed') {
108                                $query = urlencode('msg_id LIKE "'.$xMessageId.'%" AND to_email IN ("'.$email.'")');
109
110                                $response = Http::withToken(config('services.sendgrid.api_key'))
111                                    ->get("https://api.sendgrid.com/v3/messages?limit=10&query={$query}");
112
113                                $data = $response->json();
114
115                                if (! empty($data['messages'])) {
116                                    $xStatus = $data['messages'][0]['status'] ?? 'processed';
117
118                                    if($xStatus == "delivered"){                   
119                                        $data['messages'][0]['timestamp'] = strtotime((string) $data['messages'][0]['last_event_time']);
120                                        unset($data['messages'][0]['last_event_time']);
121
122                                        $data['messages'][0]['email'] = $data['messages'][0]['to_email'];
123                                        unset($data['messages'][0]['to_email']);
124
125                                        $data['messages'][0]['event'] = $data['messages'][0]['status'];
126                                        unset($data['messages'][0]['status']);
127
128                                        $data['messages'][0]['click'] = $data['messages'][0]['clicks_count'];
129                                        unset($data['messages'][0]['clicks_count']);
130
131                                        $data['messages'][0]['open'] = $data['messages'][0]['opens_count'];
132                                        unset($data['messages'][0]['opens_count']);
133
134                                        array_push($events, $data['messages'][0]);
135                                        $eventsUpdated = true;
136                                    }
137                                }
138                            }
139
140                            if ($xStatus == 'processed') {
141                                $isProcessed++;
142                            } elseif ($xStatus == 'delivered') {
143                                $isDelivered++;
144                            } else {
145                                $isError++;
146                            }
147                        }
148
149                        if ($eventsUpdated) {
150                            TblSendgridWebhook::where('x_message_id', $xMessageId)->update(
151                                [
152                                    'updated_at' => date('Y-m-d H:i:s'), 
153                                    'json_body' => json_encode($events)
154                                ]
155                            );
156                        }
157
158                        if (count($emails) == $isDelivered) {
159                            $xStatus = 'Completed';
160                        } elseif ($isProcessed > 0) {
161                            $xStatus = 'Processing';
162                        } elseif ($isError > 0) {
163                            if ($xStatus == 'bounce') {
164                                $xStatus = 'Error - Bounce';
165                            } else {
166                                $xStatus = 'Error';
167                            }
168                        }
169
170                        if ($xOrderStatus != $xStatus) {
171                            $updatePayload = ['x_status' => $xStatus];
172
173                            // FIRE-864: when SendGrid confirms the email was
174                            // bounced / dropped, flag the quotation itself as
175                            // "Correo erróneo" (budget_status_id = 22) so the
176                            // sales flow reflects the real delivery state.
177                            if (in_array($xStatus, ['Error', 'Error - Bounce'], true)) {
178                                $updatePayload['budget_status_id'] = 22;
179                            }
180
181                            TblQuotations::where('id', $result[$i]->id)->update($updatePayload);
182
183                            Log::channel('clear_email_processing')->info($xMessageId.': OK');
184                        }
185                    }
186                }                
187
188                if(count($quoteIds) > 0){
189                    TblQuotations::whereIn('id', $quoteIds)->update(
190                        [
191                            'x_status' => null,
192                            'x_message_id' => null
193                        ]
194                    );
195
196                    TblSendgridWebhook::whereIn('quotation_id', $quoteIds)->delete();
197                }
198
199            }
200
201            $query = "SELECT 
202                        a.x_message_id
203                    FROM 
204                        tbl_quotations a 
205                    LEFT JOIN 
206                        tbl_sendgrid_webhook b 
207                        ON a.x_message_id = b.x_message_id 
208                    WHERE 
209                        a.x_status = 'Processing'
210                        AND a.x_status IS NOT NULL
211                        AND (b.updated_at < NOW() - INTERVAL 30 DAY OR b.x_message_id IS NULL)";
212
213            $result = DB::select($query);
214
215            $idsCollection = collect($result)->pluck('x_message_id');
216
217            $idsString = $idsCollection->map(fn($id) => "'" . $id . "'")
218                            ->implode(',');
219
220            $totalCount = $idsCollection->count();
221
222            if ($totalCount > 0) {
223                $query = "DELETE FROM tbl_sendgrid_webhook
224                            WHERE x_message_id IN ({$idsString})";
225
226                $result = DB::select($query);
227
228                $query = "UPDATE tbl_quotations 
229                            SET x_message_id = NULL, x_status = NULL 
230                            WHERE x_message_id IN ({$idsString})";
231
232                $result = DB::select($query);
233            }
234
235            TblQuotations::whereNull('x_message_id')->where('x_status', 'Processing')->update(['x_status' => null]);
236
237            Cache::flush();
238
239        } catch (\Exception $e) {
240            Log::channel('clear_email_processing')->error($e->getMessage());
241        }
242    }
243}