Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00% covered (danger)
0.00%
0 / 148
0.00% covered (danger)
0.00%
0 / 5
CRAP
0.00% covered (danger)
0.00%
0 / 1
InvoicesImportSkipsFromLog
0.00% covered (danger)
0.00%
0 / 148
0.00% covered (danger)
0.00%
0 / 5
552
0.00% covered (danger)
0.00%
0 / 1
 handle
0.00% covered (danger)
0.00%
0 / 71
0.00% covered (danger)
0.00%
0 / 1
90
 parseLog
0.00% covered (danger)
0.00%
0 / 35
0.00% covered (danger)
0.00%
0 / 1
20
 classifyEvents
0.00% covered (danger)
0.00%
0 / 16
0.00% covered (danger)
0.00%
0 / 1
20
 resolveRegions
0.00% covered (danger)
0.00%
0 / 20
0.00% covered (danger)
0.00%
0 / 1
20
 buildPrefixMap
0.00% covered (danger)
0.00%
0 / 6
0.00% covered (danger)
0.00%
0 / 1
6
1<?php
2
3namespace App\Console\Commands;
4
5use App\Models\TblInvoiceReminderSkips;
6use Carbon\Carbon;
7use Illuminate\Console\Command;
8use Illuminate\Support\Facades\DB;
9use Illuminate\Support\Facades\Log;
10
11/**
12 * FIRE-1293 (skip telemetry historical import) — parses a Laravel log file
13 * containing reminder-send failures and writes each failure into
14 * `tbl_invoice_reminder_skips` so the KPI dashboard can render real
15 * historical "not sent" counts (matching docs/info/invoice_reminders_report_v3_by_day.csv).
16 *
17 * Recognised patterns — both contribute to a (date, invoice) universe that
18 * is then deduplicated and classified:
19 *
20 *   [...] production.ERROR:   No hay emails válidos para enviar factura X
21 *   [...] production.WARNING: Email inválido omitido: bad@x para factura Y
22 *
23 * Classification (1 row inserted per distinct (date, invoice) pair):
24 *   - If the pair appears in ERROR lines  → skip_reason = 'no_valid_email'
25 *     (all addresses failed validation, reminder NOT sent)
26 *   - If the pair appears only in WARNING → skip_reason = 'partial_invalid_emails'
27 *     (some addresses failed but the reminder still went to the valid ones —
28 *      a data-quality issue, not a true skip)
29 *
30 * Region resolution mirrors docs/info/invoice_reminders_report_v2.sql:
31 *   1. tbl_invoice_reminders        (by invoice_number)
32 *   2. tbl_invoice_next_reminders   (by invoice_number)
33 *   3. tbl_invoice_administrators   (by invoice_number)
34 *   4. invoice-prefix → region map  (derived from tbl_invoice_reminders)
35 *   5. 'Unknown' fallback so nothing silently drops
36 *
37 * Idempotent: a row is skipped if an entry with the same (invoice_number,
38 * skip_reason, created_at) already exists. Re-running on the same log file
39 * is a no-op.
40 */
41class InvoicesImportSkipsFromLog extends Command
42{
43    protected $signature = 'invoices:import-skips-from-log {path} {--dry-run}';
44
45    protected $description = 'FIRE-1293: import historical reminder-send failures from a Laravel log file into tbl_invoice_reminder_skips';
46
47    public function handle(): int
48    {
49        $path = (string) $this->argument('path');
50        if (! is_readable($path)) {
51            $this->error("Cannot read log file: {$path}");
52
53            return self::FAILURE;
54        }
55
56        $dryRun = (bool) $this->option('dry-run');
57
58        // Both regexes anchor on ASCII suffixes ("para enviar factura X" /
59        // "para factura X") because the body has Spanish characters that are
60        // double-encoded in the log file (válidos instead of válidos).
61        $errorPattern = '/^\[(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\]\s+production\.ERROR:.*?para enviar factura\s+(\S+)/u';
62        $warningPattern = '/^\[(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\]\s+production\.WARNING:.*?para factura\s+(\S+)/u';
63
64        $this->info("Parsing {$path}...");
65        $parsed = $this->parseLog($path, $errorPattern, $warningPattern);
66        $this->info(sprintf(
67            'Parsed %d ERROR + %d WARNING lines (%d total lines scanned)',
68            $parsed['error_matches'],
69            $parsed['warning_matches'],
70            $parsed['lines'],
71        ));
72
73        if (empty($parsed['errorEvents']) && empty($parsed['warningEvents'])) {
74            $this->info('Nothing to import.');
75
76            return self::SUCCESS;
77        }
78
79        // Build classified event list (one row per distinct (date, invoice)
80        // pair, ERROR wins over WARNING).
81        $classified = $this->classifyEvents($parsed['errorEvents'], $parsed['warningEvents']);
82
83        $allInvoices = array_unique(array_column($classified, 'invoice_number'));
84        $regionByInvoice = $this->resolveRegions($allInvoices);
85        $prefixMap = $this->buildPrefixMap();
86
87        $counts = ['no_valid_email' => 0, 'partial_invalid_emails' => 0];
88        $skippedDuplicates = 0;
89        $unknownRegion = 0;
90
91        foreach ($classified as $event) {
92            $region = $regionByInvoice[$event['invoice_number']]
93                ?? ($prefixMap[substr($event['invoice_number'], 0, 1)] ?? 'Unknown');
94
95            if ($region === 'Unknown') {
96                $unknownRegion++;
97            }
98
99            // Idempotency: same (invoice_number, skip_reason, created_at) → skip.
100            $exists = TblInvoiceReminderSkips::where('invoice_number', $event['invoice_number'])
101                ->where('skip_reason', $event['reason'])
102                ->where('created_at', $event['timestamp'])
103                ->exists();
104
105            if ($exists) {
106                $skippedDuplicates++;
107
108                continue;
109            }
110
111            if (! $dryRun) {
112                TblInvoiceReminderSkips::create([
113                    'invoice_number' => $event['invoice_number'],
114                    'region' => $region,
115                    'skip_reason' => $event['reason'],
116                    'reminder_type' => null,
117                    'client_code' => null,
118                    'amount' => null,
119                    'context' => ['source' => 'invoices.log import'],
120                    'created_at' => $event['timestamp'],
121                ]);
122            }
123
124            $counts[$event['reason']]++;
125        }
126
127        $totalInserted = $counts['no_valid_email'] + $counts['partial_invalid_emails'];
128        $this->line(sprintf(
129            'Inserted: %d (no_valid_email=%d, partial_invalid_emails=%d)  |  Duplicates: %d  |  Unknown region: %d%s',
130            $totalInserted,
131            $counts['no_valid_email'],
132            $counts['partial_invalid_emails'],
133            $skippedDuplicates,
134            $unknownRegion,
135            $dryRun ? '  (dry-run, nothing written)' : '',
136        ));
137
138        Log::channel('g3w_invoices')->info('invoices:import-skips-from-log finished', [
139            'path' => $path,
140            'lines_scanned' => $parsed['lines'],
141            'error_matches' => $parsed['error_matches'],
142            'warning_matches' => $parsed['warning_matches'],
143            'inserted_no_valid_email' => $counts['no_valid_email'],
144            'inserted_partial' => $counts['partial_invalid_emails'],
145            'duplicates' => $skippedDuplicates,
146            'unknown_region' => $unknownRegion,
147            'dry_run' => $dryRun,
148        ]);
149
150        return self::SUCCESS;
151    }
152
153    /**
154     * @return array{
155     *   lines:int,
156     *   error_matches:int,
157     *   warning_matches:int,
158     *   errorEvents:array<string,array{timestamp:string,invoice_number:string,date:string}>,
159     *   warningEvents:array<string,array{timestamp:string,invoice_number:string,date:string}>
160     * }
161     *
162     * Events are keyed by "$date|$invoiceNumber" so duplicates within the
163     * same day are collapsed up-front. The first timestamp seen wins.
164     */
165    private function parseLog(string $path, string $errorPattern, string $warningPattern): array
166    {
167        $lines = 0;
168        $errorMatches = 0;
169        $warningMatches = 0;
170        $errorEvents = [];
171        $warningEvents = [];
172
173        $fh = fopen($path, 'r');
174        try {
175            while (($line = fgets($fh)) !== false) {
176                $lines++;
177
178                if (preg_match($errorPattern, $line, $m) === 1) {
179                    $errorMatches++;
180                    $timestamp = Carbon::createFromFormat('Y-m-d H:i:s', $m[1])->toDateTimeString();
181                    $date = substr($timestamp, 0, 10);
182                    $key = $date.'|'.$m[2];
183                    $errorEvents[$key] = $errorEvents[$key] ?? [
184                        'timestamp' => $timestamp,
185                        'invoice_number' => $m[2],
186                        'date' => $date,
187                    ];
188
189                    continue;
190                }
191
192                if (preg_match($warningPattern, $line, $m) === 1) {
193                    $warningMatches++;
194                    $timestamp = Carbon::createFromFormat('Y-m-d H:i:s', $m[1])->toDateTimeString();
195                    $date = substr($timestamp, 0, 10);
196                    $key = $date.'|'.$m[2];
197                    $warningEvents[$key] = $warningEvents[$key] ?? [
198                        'timestamp' => $timestamp,
199                        'invoice_number' => $m[2],
200                        'date' => $date,
201                    ];
202                }
203            }
204        } finally {
205            fclose($fh);
206        }
207
208        return [
209            'lines' => $lines,
210            'error_matches' => $errorMatches,
211            'warning_matches' => $warningMatches,
212            'errorEvents' => $errorEvents,
213            'warningEvents' => $warningEvents,
214        ];
215    }
216
217    /**
218     * Tag each distinct (date, invoice) pair with the appropriate reason.
219     * ERROR wins over WARNING — if both exist for the same pair, only the
220     * ERROR row is emitted (all addresses failed, not just some).
221     *
222     * @param  array<string,array{timestamp:string,invoice_number:string,date:string}>  $errorEvents
223     * @param  array<string,array{timestamp:string,invoice_number:string,date:string}>  $warningEvents
224     * @return array<int,array{timestamp:string,invoice_number:string,reason:string}>
225     */
226    private function classifyEvents(array $errorEvents, array $warningEvents): array
227    {
228        $out = [];
229
230        foreach ($errorEvents as $event) {
231            $out[] = [
232                'timestamp' => $event['timestamp'],
233                'invoice_number' => $event['invoice_number'],
234                'reason' => 'no_valid_email',
235            ];
236        }
237
238        foreach ($warningEvents as $key => $event) {
239            if (isset($errorEvents[$key])) {
240                // Already covered by the ERROR row — the reminder was a true
241                // skip, not a partial. Don't double-count.
242                continue;
243            }
244            $out[] = [
245                'timestamp' => $event['timestamp'],
246                'invoice_number' => $event['invoice_number'],
247                'reason' => 'partial_invalid_emails',
248            ];
249        }
250
251        return $out;
252    }
253
254    /**
255     * @param  array<int,string>  $invoiceNumbers
256     * @return array<string,string> invoice_number => region
257     */
258    private function resolveRegions(array $invoiceNumbers): array
259    {
260        if (empty($invoiceNumbers)) {
261            return [];
262        }
263
264        // Single round-trip per source table — much cheaper than per-row lookup.
265        $fromReminders = DB::table('tbl_invoice_reminders')
266            ->whereIn('invoice_number', $invoiceNumbers)
267            ->whereNotNull('region')
268            ->pluck('region', 'invoice_number')
269            ->toArray();
270
271        $remaining = array_diff($invoiceNumbers, array_keys($fromReminders));
272        $fromNext = empty($remaining) ? [] : DB::table('tbl_invoice_next_reminders')
273            ->whereIn('invoice_number', $remaining)
274            ->whereNotNull('region')
275            ->pluck('region', 'invoice_number')
276            ->toArray();
277
278        $remaining = array_diff($remaining, array_keys($fromNext));
279        $fromAdmins = empty($remaining) ? [] : DB::table('tbl_invoice_administrators')
280            ->whereIn('invoice_number', $remaining)
281            ->whereNotNull('region')
282            ->pluck('region', 'invoice_number')
283            ->toArray();
284
285        return $fromReminders + $fromNext + $fromAdmins;
286    }
287
288    /**
289     * @return array<string,string> prefix (1 char) => region
290     */
291    private function buildPrefixMap(): array
292    {
293        // Most-frequent region per first-letter prefix, computed from the
294        // local reminder population. Mirrors docs/info/invoice_prefix_map.sql.
295        $rows = DB::select("
296            SELECT prefix, region
297            FROM (
298                SELECT LEFT(invoice_number, 1) AS prefix,
299                       region,
300                       COUNT(*)               AS cnt,
301                       ROW_NUMBER() OVER (PARTITION BY LEFT(invoice_number, 1)
302                                          ORDER BY COUNT(*) DESC, region) AS rn
303                FROM tbl_invoice_reminders
304                WHERE invoice_number IS NOT NULL AND invoice_number <> ''
305                GROUP BY LEFT(invoice_number, 1), region
306            ) ranked
307            WHERE rn = 1
308        ");
309
310        $map = [];
311        foreach ($rows as $row) {
312            $map[$row->prefix] = $row->region;
313        }
314
315        return $map;
316    }
317}