<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * InvoiceService::generateNumber() used `count() + 1` against a
 * UNIQUE(academy_id, number) index. Two invoices created at the same instant
 * counted the same rows, produced the same number and the second insert died
 * on the constraint — a guaranteed collision as soon as members can check out
 * from the portal without a receptionist serialising them by hand.
 *
 * A counter row taken with lockForUpdate() serialises allocation per academy
 * and never revisits a number after a soft delete (count() did, because
 * soft-deleted invoices leave the unique index occupied).
 *
 * Seeded from the highest number each academy has actually issued, not from
 * the row count, so a tenant with gaps does not immediately collide.
 */
return new class extends Migration
{
    public function up(): void
    {
        if (! Schema::hasTable('invoice_number_counters')) {
            Schema::create('invoice_number_counters', function (Blueprint $table) {
                $table->foreignId('academy_id')->primary()->constrained()->cascadeOnDelete();
                $table->unsignedBigInteger('next_number')->default(1);
                $table->timestamps();
            });
        }

        if (! Schema::hasTable('academies') || ! Schema::hasTable('invoices')) {
            return;
        }

        foreach (DB::table('academies')->pluck('id') as $academyId) {
            $exists = DB::table('invoice_number_counters')->where('academy_id', $academyId)->exists();

            if ($exists) {
                continue;
            }

            // Read the numeric suffix of every INV-… number this academy has
            // issued, soft-deleted rows included: the unique index still holds
            // them, so reusing one would fail.
            $highest = 0;

            $numbers = DB::table('invoices')
                ->where('academy_id', $academyId)
                ->where('number', 'like', 'INV-%')
                ->pluck('number');

            foreach ($numbers as $number) {
                if (preg_match('/(\d+)$/', (string) $number, $m)) {
                    $highest = max($highest, (int) $m[1]);
                }
            }

            DB::table('invoice_number_counters')->insert([
                'academy_id' => $academyId,
                'next_number' => $highest + 1,
                'created_at' => now(),
                'updated_at' => now(),
            ]);
        }
    }

    public function down(): void
    {
        Schema::dropIfExists('invoice_number_counters');
    }
};
