Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
0.00% |
0 / 76 |
|
0.00% |
0 / 4 |
CRAP | |
0.00% |
0 / 1 |
| PurgeSyncNoopAudits | |
0.00% |
0 / 76 |
|
0.00% |
0 / 4 |
812 | |
0.00% |
0 / 1 |
| handle | |
0.00% |
0 / 20 |
|
0.00% |
0 / 1 |
72 | |||
| restoreUpdatedAt | |
0.00% |
0 / 38 |
|
0.00% |
0 / 1 |
182 | |||
| purge | |
0.00% |
0 / 14 |
|
0.00% |
0 / 1 |
42 | |||
| onlyBookkeeping | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
2 | |||
| 1 | <?php |
| 2 | |
| 3 | namespace App\Console\Commands; |
| 4 | |
| 5 | use Illuminate\Console\Command; |
| 6 | use Illuminate\Support\Facades\DB; |
| 7 | |
| 8 | /** |
| 9 | * Cleanup after the G3W sync rewrite loop (2026-10-05). |
| 10 | * |
| 11 | * The sync re-processed every budget Gestiona reported as modified in the last |
| 12 | * hour on each 5-minute run, and saved it even when nothing had changed. Each |
| 13 | * save moved tbl_quotations.updated_at and left a tbl_audit_logs row whose |
| 14 | * old_data and new_data differ only in updated_at / updated_by. |
| 15 | * |
| 16 | * This command: |
| 17 | * 1. (--restore-updated-at) puts each affected quote's updated_at back to the |
| 18 | * value it had before its trailing run of no-op writes, only if nothing has |
| 19 | * touched the quote since. Written straight through PDO so the restore |
| 20 | * itself doesn't create new audit rows. |
| 21 | * 2. deletes those no-op audit rows, in id-ranged batches. |
| 22 | * |
| 23 | * Dry run by default. Restore BEFORE deleting: step 1 reads the rows step 2 |
| 24 | * removes. Deploy the sync fix first, or new no-op rows keep arriving. |
| 25 | * |
| 26 | * php artisan audit:purge-sync-noops --since=2026-10-05 |
| 27 | * php artisan audit:purge-sync-noops --since=2026-10-05 --restore-updated-at --apply |
| 28 | */ |
| 29 | class PurgeSyncNoopAudits extends Command |
| 30 | { |
| 31 | protected $signature = 'audit:purge-sync-noops |
| 32 | {--since= : Start of the window (Y-m-d or Y-m-d H:i:s), required} |
| 33 | {--until= : End of the window (default: now)} |
| 34 | {--restore-updated-at : Also restore tbl_quotations.updated_at on affected quotes} |
| 35 | {--apply : Actually write; without it nothing changes} |
| 36 | {--batch=20000 : Audit ids scanned per DELETE} |
| 37 | {--sleep=200 : Milliseconds to pause between batches}'; |
| 38 | |
| 39 | protected $description = 'Remove no-op G3W sync audit rows (only updated_at changed) and optionally restore updated_at'; |
| 40 | |
| 41 | /** A sync write: CLI request, update of an existing quote. */ |
| 42 | private const SCOPE = "auditable_type = 'tbl_quotations' AND event = 'updated' AND url = 'http://localhost' AND old_data IS NOT NULL"; |
| 43 | |
| 44 | /** Nothing but bookkeeping changed between the two snapshots. */ |
| 45 | private const NOOP = "JSON_REMOVE(old_data, '$.updated_at', '$.updated_by') = JSON_REMOVE(new_data, '$.updated_at', '$.updated_by')"; |
| 46 | |
| 47 | public function handle(): int |
| 48 | { |
| 49 | $since = $this->option('since'); |
| 50 | if (! $since || strtotime($since) === false) { |
| 51 | $this->error('--since is required, e.g. --since=2026-10-05'); |
| 52 | |
| 53 | return self::INVALID; |
| 54 | } |
| 55 | $since = date('Y-m-d H:i:s', strtotime($since)); |
| 56 | $until = date('Y-m-d H:i:s', strtotime($this->option('until') ?: 'now')); |
| 57 | $apply = (bool) $this->option('apply'); |
| 58 | $batch = max(1000, (int) $this->option('batch')); |
| 59 | $sleepMs = max(0, (int) $this->option('sleep')); |
| 60 | |
| 61 | $range = DB::selectOne('SELECT MIN(id) lo, MAX(id) hi FROM tbl_audit_logs WHERE created_at >= ? AND created_at < ?', [$since, $until]); |
| 62 | if (! $range || $range->lo === null) { |
| 63 | $this->info("No audit rows between {$since} and {$until}."); |
| 64 | |
| 65 | return self::SUCCESS; |
| 66 | } |
| 67 | $lo = (int) $range->lo; |
| 68 | $hi = (int) $range->hi; |
| 69 | |
| 70 | $this->info(sprintf('Window %s ā %s, audit ids %dā%d%s', $since, $until, $lo, $hi, $apply ? '' : ' [DRY RUN]')); |
| 71 | |
| 72 | if ($this->option('restore-updated-at')) { |
| 73 | $this->restoreUpdatedAt($lo, $hi, $apply); |
| 74 | } |
| 75 | |
| 76 | $this->purge($lo, $hi, $batch, $sleepMs, $apply); |
| 77 | |
| 78 | return self::SUCCESS; |
| 79 | } |
| 80 | |
| 81 | private function restoreUpdatedAt(int $lo, int $hi, bool $apply): void |
| 82 | { |
| 83 | $quoteIds = DB::table('tbl_audit_logs') |
| 84 | ->whereBetween('id', [$lo, $hi]) |
| 85 | ->whereRaw(self::SCOPE.' AND '.self::NOOP) |
| 86 | ->distinct() |
| 87 | ->pluck('auditable_id'); |
| 88 | |
| 89 | $this->info("Quotes with no-op sync writes: {$quoteIds->count()}"); |
| 90 | |
| 91 | $restored = 0; |
| 92 | $skipped = 0; |
| 93 | $pdo = DB::connection()->getPdo(); |
| 94 | $update = $pdo->prepare('UPDATE tbl_quotations SET updated_at = ? WHERE id = ? AND updated_at = ?'); |
| 95 | |
| 96 | foreach ($quoteIds as $quoteId) { |
| 97 | // Newest first: walk back through the trailing run of no-op writes. |
| 98 | $rows = DB::table('tbl_audit_logs') |
| 99 | ->where('auditable_type', 'tbl_quotations') |
| 100 | ->where('auditable_id', $quoteId) |
| 101 | ->where('event', 'updated') |
| 102 | ->where('id', '<=', $hi) |
| 103 | ->orderByDesc('id') |
| 104 | ->limit(2000) |
| 105 | ->get(['old_data', 'new_data', 'url']); |
| 106 | |
| 107 | $latestNew = null; |
| 108 | $restoreTo = null; |
| 109 | foreach ($rows as $row) { |
| 110 | $old = json_decode((string) $row->old_data, true) ?: []; |
| 111 | $new = json_decode((string) $row->new_data, true) ?: []; |
| 112 | $latestNew ??= $new['updated_at'] ?? null; |
| 113 | if ($row->url !== 'http://localhost' || ! self::onlyBookkeeping($old, $new)) { |
| 114 | break; // last real change: stop here |
| 115 | } |
| 116 | $restoreTo = $old['updated_at'] ?? null; |
| 117 | } |
| 118 | |
| 119 | $current = DB::table('tbl_quotations')->where('id', $quoteId)->value('updated_at'); |
| 120 | // Only when the quote is still exactly as the last no-op left it. |
| 121 | if ($restoreTo === null || $latestNew === null || $current === null |
| 122 | || strtotime((string) $current) !== strtotime((string) $latestNew)) { |
| 123 | $skipped++; |
| 124 | |
| 125 | continue; |
| 126 | } |
| 127 | |
| 128 | $restored++; |
| 129 | if ($apply) { |
| 130 | $update->execute([date('Y-m-d H:i:s', strtotime($restoreTo)), $quoteId, $current]); |
| 131 | } |
| 132 | } |
| 133 | |
| 134 | $this->info(sprintf('updated_at %s: %d quotes, skipped %d (changed since, or no prior value)', |
| 135 | $apply ? 'restored' : 'would be restored', $restored, $skipped)); |
| 136 | } |
| 137 | |
| 138 | private function purge(int $lo, int $hi, int $batch, int $sleepMs, bool $apply): void |
| 139 | { |
| 140 | $total = 0; |
| 141 | $bar = $this->output->createProgressBar((int) ceil(($hi - $lo + 1) / $batch)); |
| 142 | |
| 143 | for ($from = $lo; $from <= $hi; $from += $batch) { |
| 144 | $to = min($hi, $from + $batch - 1); |
| 145 | $where = 'id BETWEEN ? AND ? AND '.self::SCOPE.' AND '.self::NOOP; |
| 146 | |
| 147 | // tbl_audit_logs is on QueryAuditor's skip list, so these deletes |
| 148 | // are not audited themselves. |
| 149 | $total += $apply |
| 150 | ? DB::delete("DELETE FROM tbl_audit_logs WHERE {$where}", [$from, $to]) |
| 151 | : (int) DB::selectOne("SELECT COUNT(*) n FROM tbl_audit_logs WHERE {$where}", [$from, $to])->n; |
| 152 | |
| 153 | $bar->advance(); |
| 154 | if ($apply && $sleepMs > 0) { |
| 155 | usleep($sleepMs * 1000); |
| 156 | } |
| 157 | } |
| 158 | $bar->finish(); |
| 159 | $this->newLine(); |
| 160 | |
| 161 | $this->info(sprintf('No-op audit rows %s: %d', $apply ? 'deleted' : 'that would be deleted', $total)); |
| 162 | } |
| 163 | |
| 164 | /** True when the two snapshots differ in nothing but updated_at / updated_by. */ |
| 165 | public static function onlyBookkeeping(array $old, array $new): bool |
| 166 | { |
| 167 | unset($old['updated_at'], $old['updated_by'], $new['updated_at'], $new['updated_by']); |
| 168 | ksort($old); |
| 169 | ksort($new); |
| 170 | |
| 171 | return $old == $new; |
| 172 | } |
| 173 | } |