Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
0.00% |
0 / 205 |
|
0.00% |
0 / 11 |
CRAP | |
0.00% |
0 / 1 |
| RestoreDeletedCommercials | |
0.00% |
0 / 205 |
|
0.00% |
0 / 11 |
3906 | |
0.00% |
0 / 1 |
| handle | |
0.00% |
0 / 17 |
|
0.00% |
0 / 1 |
56 | |||
| deletedUsers | |
0.00% |
0 / 22 |
|
0.00% |
0 / 1 |
42 | |||
| renamedAwayNames | |
0.00% |
0 / 15 |
|
0.00% |
0 / 1 |
56 | |||
| restoreUsers | |
0.00% |
0 / 21 |
|
0.00% |
0 / 1 |
56 | |||
| insertUser | |
0.00% |
0 / 15 |
|
0.00% |
0 / 1 |
2 | |||
| restoreCompanyMemberships | |
0.00% |
0 / 17 |
|
0.00% |
0 / 1 |
30 | |||
| reassignQuotations | |
0.00% |
0 / 42 |
|
0.00% |
0 / 1 |
110 | |||
| liveActiveNames | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
6 | |||
| releasableOwners | |
0.00% |
0 / 8 |
|
0.00% |
0 / 1 |
12 | |||
| candidateQuotations | |
0.00% |
0 / 2 |
|
0.00% |
0 / 1 |
2 | |||
| loadCandidateMap | |
0.00% |
0 / 42 |
|
0.00% |
0 / 1 |
182 | |||
| 1 | <?php |
| 2 | |
| 3 | namespace App\Console\Commands; |
| 4 | |
| 5 | use Illuminate\Console\Command; |
| 6 | use Illuminate\Support\Facades\DB; |
| 7 | use Illuminate\Support\Facades\Log; |
| 8 | |
| 9 | /** |
| 10 | * FIRE-1752 follow-up — recover users erased by the old hard delete. |
| 11 | * |
| 12 | * The historical Users::delete_users() removed the tbl_users row and its |
| 13 | * tbl_company_users rows outright, and rewrote tbl_quotations.created_by to |
| 14 | * 'Fire Service Titan'. It never touched `commercial`, but a name that no |
| 15 | * longer resolves to a user is equally unusable in the UI. |
| 16 | * |
| 17 | * The only surviving copy of those users is App\Audit\QueryAuditor's DELETE |
| 18 | * capture in tbl_audit_logs: its `delete` branch iterates the pre-state rows |
| 19 | * rather than re-fetching, so it recorded the complete row in `old_data` |
| 20 | * (password / remember_token / api_token / token_expires_at redacted; name, |
| 21 | * email, role_id, G3W_code intact). |
| 22 | * |
| 23 | * Two stages, deliberately separate, dry-run unless --apply is passed: |
| 24 | * |
| 25 | * --users recreate the tbl_users rows (INACTIVE) and their |
| 26 | * tbl_company_users memberships |
| 27 | * --quotations set `commercial` back on the quotations that demonstrably |
| 28 | * belonged to them |
| 29 | * |
| 30 | * Run the report, read it, then apply. There is no undo. |
| 31 | */ |
| 32 | class RestoreDeletedCommercials extends Command |
| 33 | { |
| 34 | protected $signature = 'titan:restore-deleted-commercials |
| 35 | {--users : Stage 1 — recreate the tbl_users rows} |
| 36 | {--quotations : Stage 2 — reassign commercial on their quotations} |
| 37 | {--user= : Limit to a single deleted user id} |
| 38 | {--release-from= : Comma-separated placeholder owners to reclaim from (overrides the default list)} |
| 39 | {--apply : Actually write. Without it, nothing is modified.}'; |
| 40 | |
| 41 | protected $description = 'FIRE-1752: recreate hard-deleted users from the audit log and reassign their quotations.'; |
| 42 | |
| 43 | private const MARKER = 'FIRE-1752 restore'; |
| 44 | |
| 45 | /** |
| 46 | * Active users who are placeholders rather than working commercials. |
| 47 | * |
| 48 | * A budget parked on one of these is nobody's in practice — it landed |
| 49 | * there when its real commercial was removed, not because anyone chose to |
| 50 | * own it. So the "never take work from a live commercial" guard is lifted |
| 51 | * for these names only. |
| 52 | * |
| 53 | * Verified against the live data before being listed here: |
| 54 | * Fire Service Titan (id 45) 17,224 quotations |
| 55 | * Duarte Sarmento (id 191) 4,005 quotations |
| 56 | * Duarte Sarmiento Fire (id 232) 32 quotations |
| 57 | * |
| 58 | * Deliberately NOT included: 'Automatizacion' (id 212, is_system_account). |
| 59 | * It looks similar but nothing was reassigned to it, so reclaiming from it |
| 60 | * has no evidence behind it — add it with --release-from if that changes. |
| 61 | */ |
| 62 | private const DEFAULT_RELEASABLE_OWNERS = [ |
| 63 | 'Fire Service Titan', |
| 64 | 'Duarte Sarmento', |
| 65 | 'Duarte Sarmiento Fire', |
| 66 | ]; |
| 67 | |
| 68 | /** Lowercased commercial name => candidate quotations. Built once. */ |
| 69 | private ?array $candidatesByName = null; |
| 70 | |
| 71 | /** deletedUsers() is called from several places; the audit read is expensive. */ |
| 72 | private ?array $deletedUsersCache = null; |
| 73 | |
| 74 | public function handle(): int |
| 75 | { |
| 76 | $apply = (bool) $this->option('apply'); |
| 77 | |
| 78 | if (! $this->option('users') && ! $this->option('quotations')) { |
| 79 | $this->error('Choose a stage: --users and/or --quotations.'); |
| 80 | |
| 81 | return self::FAILURE; |
| 82 | } |
| 83 | |
| 84 | if (! $apply) { |
| 85 | $this->warn('DRY RUN — nothing will be written. Add --apply to commit.'); |
| 86 | } |
| 87 | |
| 88 | $deleted = $this->deletedUsers(); |
| 89 | |
| 90 | if (empty($deleted)) { |
| 91 | $this->info('No hard-deleted users found in tbl_audit_logs.'); |
| 92 | $this->line('Either none were deleted, or the deletions predate the audit table (created 2026-04-29).'); |
| 93 | |
| 94 | return self::SUCCESS; |
| 95 | } |
| 96 | |
| 97 | $this->info(sprintf('Found %d hard-deleted user(s) in the audit log.', count($deleted))); |
| 98 | |
| 99 | if ($this->option('users')) { |
| 100 | $this->restoreUsers($deleted, $apply); |
| 101 | } |
| 102 | |
| 103 | if ($this->option('quotations')) { |
| 104 | $this->reassignQuotations($deleted, $apply); |
| 105 | } |
| 106 | |
| 107 | return self::SUCCESS; |
| 108 | } |
| 109 | |
| 110 | /** |
| 111 | * Every user the old delete removed, reconstructed from the audit row. |
| 112 | * Keyed by the original user id — restoring that same id is what makes the |
| 113 | * orphaned references in tbl_user_g3w_mapping.id_fst, tbl_approvers_v2, |
| 114 | * tbl_users_permissions and tbl_menus_users resolve again. |
| 115 | */ |
| 116 | private function deletedUsers(): array |
| 117 | { |
| 118 | if ($this->deletedUsersCache !== null) { |
| 119 | return $this->deletedUsersCache; |
| 120 | } |
| 121 | |
| 122 | $only = $this->option('user'); |
| 123 | |
| 124 | // Raw, with the index forced. tbl_audit_logs is ~29 GB and carries a |
| 125 | // separate index per column: given `auditable_type` AND `event` the |
| 126 | // optimizer can choose idx_audit_event, which matches every `deleted` |
| 127 | // row on every table and then filters — unusably slow. idx_audit_target |
| 128 | // leads on auditable_type, which is the selective half here. |
| 129 | // |
| 130 | // ORDER BY is done in PHP for the same reason: sorting in SQL adds a |
| 131 | // filesort over the JSON payload columns. |
| 132 | $sql = 'SELECT auditable_id, old_data, created_at, id |
| 133 | FROM tbl_audit_logs USE INDEX (idx_audit_target) |
| 134 | WHERE auditable_type = ? AND event = ?'; |
| 135 | $bindings = ['tbl_users', 'deleted']; |
| 136 | |
| 137 | if ($only) { |
| 138 | $sql .= ' AND auditable_id = ?'; |
| 139 | $bindings[] = (int) $only; |
| 140 | } |
| 141 | |
| 142 | $rows = DB::select($sql, $bindings); |
| 143 | usort($rows, fn ($a, $b) => $b->id <=> $a->id); |
| 144 | |
| 145 | $out = []; |
| 146 | |
| 147 | foreach ($rows as $row) { |
| 148 | $data = json_decode($row->old_data ?? '', true); |
| 149 | |
| 150 | if (! is_array($data) || empty($data['name'])) { |
| 151 | continue; |
| 152 | } |
| 153 | |
| 154 | // Keep the newest audit row per user id if a user was somehow |
| 155 | // deleted more than once. |
| 156 | $out[(int) $row->auditable_id] ??= [ |
| 157 | 'id' => (int) $row->auditable_id, |
| 158 | 'data' => $data, |
| 159 | 'deleted_at' => $row->created_at, |
| 160 | ]; |
| 161 | } |
| 162 | |
| 163 | return $this->deletedUsersCache = $out; |
| 164 | } |
| 165 | |
| 166 | /** |
| 167 | * Names that left tbl_users via a RENAME rather than a delete. Those users |
| 168 | * still exist under a new name — recreating them would produce a duplicate |
| 169 | * person, so they are excluded from restoration entirely. |
| 170 | */ |
| 171 | private function renamedAwayNames(): array |
| 172 | { |
| 173 | // Index forced for the same reason as deletedUsers(): on a 29 GB table |
| 174 | // idx_audit_event would match every `updated` row across every table. |
| 175 | $rows = DB::select( |
| 176 | "SELECT old_data, new_data |
| 177 | FROM tbl_audit_logs USE INDEX (idx_audit_target) |
| 178 | WHERE auditable_type = 'tbl_users' AND event = 'updated'" |
| 179 | ); |
| 180 | |
| 181 | $names = []; |
| 182 | |
| 183 | foreach ($rows as $row) { |
| 184 | $old = json_decode($row->old_data ?? '', true); |
| 185 | $new = json_decode($row->new_data ?? '', true); |
| 186 | |
| 187 | if (! is_array($old) || ! is_array($new)) { |
| 188 | continue; |
| 189 | } |
| 190 | |
| 191 | $oldName = $old['name'] ?? null; |
| 192 | $newName = $new['name'] ?? null; |
| 193 | |
| 194 | if ($oldName && $newName && $oldName !== $newName) { |
| 195 | $names[mb_strtolower($oldName)] = $newName; |
| 196 | } |
| 197 | } |
| 198 | |
| 199 | return $names; |
| 200 | } |
| 201 | |
| 202 | private function restoreUsers(array $deleted, bool $apply): void |
| 203 | { |
| 204 | $this->newLine(); |
| 205 | $this->line('── Stage 1 · recreate tbl_users ──'); |
| 206 | |
| 207 | $renamed = $this->renamedAwayNames(); |
| 208 | $rows = []; |
| 209 | |
| 210 | foreach ($deleted as $id => $entry) { |
| 211 | $name = $entry['data']['name']; |
| 212 | $verdict = 'restore'; |
| 213 | $detail = ''; |
| 214 | |
| 215 | if (isset($renamed[mb_strtolower($name)])) { |
| 216 | $verdict = 'skip'; |
| 217 | $detail = 'rename, now "'.$renamed[mb_strtolower($name)].'"'; |
| 218 | } elseif (DB::table('tbl_users')->where('id', $id)->exists()) { |
| 219 | $verdict = 'skip'; |
| 220 | $detail = 'id '.$id.' already in use'; |
| 221 | } elseif (DB::table('tbl_users')->where('name', $name)->exists()) { |
| 222 | $verdict = 'skip'; |
| 223 | $detail = 'name already exists'; |
| 224 | } |
| 225 | |
| 226 | $rows[] = [$id, $name, $entry['data']['email'] ?? '', $verdict, $detail]; |
| 227 | |
| 228 | if ($verdict === 'restore' && $apply) { |
| 229 | $this->insertUser($id, $entry); |
| 230 | } |
| 231 | } |
| 232 | |
| 233 | $this->table(['id', 'name', 'email', 'action', 'why'], $rows); |
| 234 | } |
| 235 | |
| 236 | /** |
| 237 | * Reinsert the user with their ORIGINAL id, inactive. Only columns that |
| 238 | * exist on tbl_users today are written, so an audit row captured before a |
| 239 | * later schema change still restores cleanly. |
| 240 | */ |
| 241 | private function insertUser(int $id, array $entry): void |
| 242 | { |
| 243 | $data = $entry['data']; |
| 244 | |
| 245 | unset($data['password'], $data['remember_token'], $data['api_token'], $data['token_expires_at']); |
| 246 | |
| 247 | $data['id'] = $id; |
| 248 | $data['is_active'] = 0; |
| 249 | $data['inactivated_at'] = $entry['deleted_at']; |
| 250 | $data['inactivated_by'] = self::MARKER; |
| 251 | $data['updated_by'] = self::MARKER; |
| 252 | |
| 253 | $columns = DB::getSchemaBuilder()->getColumnListing('tbl_users'); |
| 254 | $payload = array_intersect_key($data, array_flip($columns)); |
| 255 | |
| 256 | DB::transaction(function () use ($id, $payload): void { |
| 257 | DB::table('tbl_users')->insert($payload); |
| 258 | $this->restoreCompanyMemberships($id); |
| 259 | }); |
| 260 | |
| 261 | Log::info('FIRE-1752 restore: recreated user', ['user_id' => $id, 'name' => $payload['name'] ?? null]); |
| 262 | $this->info(" restored #{$id} ".($payload['name'] ?? '')); |
| 263 | } |
| 264 | |
| 265 | /** |
| 266 | * tbl_company_users was hard-deleted in the same request, so the audit log |
| 267 | * is the only record of which companies the user belonged to. |
| 268 | */ |
| 269 | private function restoreCompanyMemberships(int $userId): void |
| 270 | { |
| 271 | $rows = DB::select( |
| 272 | "SELECT old_data |
| 273 | FROM tbl_audit_logs USE INDEX (idx_audit_target) |
| 274 | WHERE auditable_type = 'tbl_company_users' AND event = 'deleted'" |
| 275 | ); |
| 276 | |
| 277 | foreach ($rows as $row) { |
| 278 | $data = json_decode($row->old_data ?? '', true); |
| 279 | |
| 280 | if (! is_array($data) || (int) ($data['user_id'] ?? 0) !== $userId) { |
| 281 | continue; |
| 282 | } |
| 283 | |
| 284 | $exists = DB::table('tbl_company_users') |
| 285 | ->where('user_id', $userId) |
| 286 | ->where('company_id', $data['company_id']) |
| 287 | ->exists(); |
| 288 | |
| 289 | if (! $exists) { |
| 290 | DB::table('tbl_company_users')->insert([ |
| 291 | 'user_id' => $userId, |
| 292 | 'company_id' => $data['company_id'], |
| 293 | ]); |
| 294 | } |
| 295 | } |
| 296 | } |
| 297 | |
| 298 | private function reassignQuotations(array $deleted, bool $apply): void |
| 299 | { |
| 300 | $this->newLine(); |
| 301 | $this->line('── Stage 2 · reassign commercial ──'); |
| 302 | |
| 303 | $liveActive = $this->liveActiveNames(); |
| 304 | $releasable = $this->releasableOwners(); |
| 305 | $summary = []; |
| 306 | |
| 307 | $this->line(' reclaiming from: '.implode(', ', array_values($releasable))); |
| 308 | $this->newLine(); |
| 309 | |
| 310 | foreach ($deleted as $entry) { |
| 311 | $name = $entry['data']['name']; |
| 312 | $candidates = $this->candidateQuotations($name); |
| 313 | |
| 314 | $eligible = []; |
| 315 | $reclaimed = 0; |
| 316 | $protected = 0; |
| 317 | |
| 318 | foreach ($candidates as $row) { |
| 319 | $current = $row->commercial_now === null ? '' : mb_strtolower($row->commercial_now); |
| 320 | |
| 321 | // A placeholder owner isn't a real owner — the budget landed |
| 322 | // there when its commercial was removed. Reclaim it. |
| 323 | if (isset($releasable[$current])) { |
| 324 | $eligible[] = (int) $row->id; |
| 325 | $reclaimed++; |
| 326 | |
| 327 | continue; |
| 328 | } |
| 329 | |
| 330 | // Otherwise never take a budget away from a commercial who is |
| 331 | // here and working: those reassignments were a deliberate |
| 332 | // business decision and are not ours to revert. |
| 333 | if ($current !== '' && isset($liveActive[$current])) { |
| 334 | $protected++; |
| 335 | |
| 336 | continue; |
| 337 | } |
| 338 | |
| 339 | $eligible[] = (int) $row->id; |
| 340 | } |
| 341 | |
| 342 | $summary[] = [$name, count($candidates), count($eligible), $reclaimed, $protected]; |
| 343 | |
| 344 | if ($apply && $eligible !== []) { |
| 345 | DB::table('tbl_quotations') |
| 346 | ->whereIn('id', $eligible) |
| 347 | ->update([ |
| 348 | 'commercial' => $name, |
| 349 | 'updated_by' => self::MARKER, |
| 350 | 'updated_at' => now(), |
| 351 | ]); |
| 352 | |
| 353 | Log::info('FIRE-1752 restore: reassigned quotations', [ |
| 354 | 'commercial' => $name, |
| 355 | 'count' => count($eligible), |
| 356 | ]); |
| 357 | } |
| 358 | } |
| 359 | |
| 360 | $this->table( |
| 361 | ['commercial', 'candidates', 'would reassign', ' ...from placeholder', 'held (live owner)'], |
| 362 | $summary |
| 363 | ); |
| 364 | |
| 365 | if (! $apply) { |
| 366 | $this->warn('Nothing written. Re-run with --apply once the numbers look right.'); |
| 367 | } |
| 368 | } |
| 369 | |
| 370 | private function liveActiveNames(): array |
| 371 | { |
| 372 | $names = []; |
| 373 | |
| 374 | foreach (DB::table('tbl_users')->where('is_active', '<>', 0)->pluck('name') as $n) { |
| 375 | $names[mb_strtolower($n)] = true; |
| 376 | } |
| 377 | |
| 378 | return $names; |
| 379 | } |
| 380 | |
| 381 | /** |
| 382 | * Placeholder owners to reclaim from, keyed lowercase for lookup. |
| 383 | * --release-from replaces the default list entirely rather than adding to |
| 384 | * it, so a run can be narrowed to one account when that is all you mean. |
| 385 | * |
| 386 | * @return array<string, string> |
| 387 | */ |
| 388 | private function releasableOwners(): array |
| 389 | { |
| 390 | $raw = (string) $this->option('release-from'); |
| 391 | |
| 392 | $names = $raw === '' |
| 393 | ? self::DEFAULT_RELEASABLE_OWNERS |
| 394 | : array_filter(array_map('trim', explode(',', $raw))); |
| 395 | |
| 396 | $out = []; |
| 397 | foreach ($names as $n) { |
| 398 | $out[mb_strtolower($n)] = $n; |
| 399 | } |
| 400 | |
| 401 | return $out; |
| 402 | } |
| 403 | |
| 404 | /** |
| 405 | * Quotations that this person was demonstrably the commercial of, drawn |
| 406 | * from both surviving change records, and which no longer name them. |
| 407 | * |
| 408 | * tbl_quotations_log — written by Quotations::addUpdateLog() from the |
| 409 | * controller, so it covers ordinary UI edits. |
| 410 | * |
| 411 | * tbl_audit_logs — full-row snapshots, so `commercial` is preserved on |
| 412 | * any audited update of the row even when it wasn't the field changing. |
| 413 | */ |
| 414 | private function candidateQuotations(string $name): array |
| 415 | { |
| 416 | $this->loadCandidateMap(); |
| 417 | |
| 418 | return $this->candidatesByName[mb_strtolower($name)] ?? []; |
| 419 | } |
| 420 | |
| 421 | /** |
| 422 | * Build the whole name -> candidate-quotations map in one pass. |
| 423 | * |
| 424 | * Previously this ran per deleted user, so with nine of them it scanned |
| 425 | * tbl_quotations_log (6.2M rows, no index on quotation_id) nine times and |
| 426 | * JSON-extracted `commercial` from every tbl_quotations audit row in a |
| 427 | * 29 GB table nine times. It did not finish in 30 minutes. |
| 428 | * |
| 429 | * Three queries total now, regardless of how many users are being |
| 430 | * restored: one per evidence source, plus one to read the quotations' |
| 431 | * current commercial. |
| 432 | */ |
| 433 | private function loadCandidateMap(): void |
| 434 | { |
| 435 | if ($this->candidatesByName !== null) { |
| 436 | return; |
| 437 | } |
| 438 | |
| 439 | $names = array_values(array_filter(array_map( |
| 440 | fn ($e) => $e['data']['name'] ?? null, |
| 441 | $this->deletedUsers() |
| 442 | ))); |
| 443 | |
| 444 | if ($names === []) { |
| 445 | $this->candidatesByName = []; |
| 446 | |
| 447 | return; |
| 448 | } |
| 449 | |
| 450 | $in = implode(',', array_fill(0, count($names), '?')); |
| 451 | |
| 452 | // Evidence source 1: the controller-written per-field change log. |
| 453 | $rows = DB::select( |
| 454 | "SELECT l.old_value AS name, l.quotation_id AS id |
| 455 | FROM tbl_quotations_log l |
| 456 | WHERE l.field = 'commercial' |
| 457 | AND l.old_value IN ({$in})", |
| 458 | $names |
| 459 | ); |
| 460 | |
| 461 | // Evidence source 2: full-row audit snapshots, which cover the bulk |
| 462 | // paths that bypass the controller entirely. |
| 463 | $rows = array_merge($rows, DB::select( |
| 464 | "SELECT a.old_data->>'$.commercial' AS name, a.auditable_id AS id |
| 465 | FROM tbl_audit_logs a USE INDEX (idx_audit_target) |
| 466 | WHERE a.auditable_type = 'tbl_quotations' |
| 467 | AND a.event = 'updated' |
| 468 | AND (a.old_data->>'$.commercial') IN ({$in})", |
| 469 | $names |
| 470 | )); |
| 471 | |
| 472 | $idsByName = []; |
| 473 | $allIds = []; |
| 474 | foreach ($rows as $r) { |
| 475 | if ($r->name === null || $r->id === null) { |
| 476 | continue; |
| 477 | } |
| 478 | $key = mb_strtolower($r->name); |
| 479 | $idsByName[$key][(int) $r->id] = true; |
| 480 | $allIds[(int) $r->id] = true; |
| 481 | } |
| 482 | |
| 483 | // Current owner for every candidate, in one read. |
| 484 | $current = []; |
| 485 | foreach (array_chunk(array_keys($allIds), 5000) as $chunk) { |
| 486 | $ph = implode(',', array_fill(0, count($chunk), '?')); |
| 487 | foreach (DB::select("SELECT id, commercial FROM tbl_quotations WHERE id IN ({$ph})", $chunk) as $q) { |
| 488 | $current[(int) $q->id] = $q->commercial; |
| 489 | } |
| 490 | } |
| 491 | |
| 492 | $map = []; |
| 493 | foreach ($idsByName as $key => $ids) { |
| 494 | foreach (array_keys($ids) as $id) { |
| 495 | // Gone from tbl_quotations entirely (hard-deleted) — nothing |
| 496 | // to reassign. |
| 497 | if (! array_key_exists($id, $current)) { |
| 498 | continue; |
| 499 | } |
| 500 | // Already theirs. |
| 501 | if ($current[$id] !== null && mb_strtolower($current[$id]) === $key) { |
| 502 | continue; |
| 503 | } |
| 504 | $map[$key][] = (object) ['id' => $id, 'commercial_now' => $current[$id]]; |
| 505 | } |
| 506 | } |
| 507 | |
| 508 | $this->candidatesByName = $map; |
| 509 | } |
| 510 | } |