<?php

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

/**
 * Staff-scan check-in.
 *
 * `qr_check_in_enabled` has been a toggle in system settings and a seeded row
 * since 2026_07_27 with **zero functional readers** — the product advertised a
 * feature that did not exist. This is the schema half of building it.
 *
 * `participants.checkin_key_version` is the revocation mechanism: the pass
 * secret is derived from it, so incrementing it kills every outstanding code
 * for that member at once. One integer, atomic, auditable — no secret column to
 * leak and no per-token revocation list to sweep.
 *
 * `checkin_consumptions` is what makes a screenshot worthless. The unique key
 * on (academy, participant, counter) is checked by an INSERT … ON CONFLICT DO
 * NOTHING inside the same transaction as the attendance write: zero rows
 * inserted means the code was already used. A Cache::has/Cache::put pair would
 * be a time-of-check-to-time-of-use race, and two scanners at one gate is
 * exactly when it would lose.
 */
return new class extends Migration
{
    public function up(): void
    {
        if (Schema::hasTable('participants') && ! Schema::hasColumn('participants', 'checkin_key_version')) {
            Schema::table('participants', function (Blueprint $table) {
                $table->unsignedInteger('checkin_key_version')->default(1)->after('uuid');
            });
        }

        if (! Schema::hasTable('checkin_consumptions')) {
            Schema::create('checkin_consumptions', function (Blueprint $table) {
                $table->id();
                $table->foreignId('academy_id')->constrained()->cascadeOnDelete();
                $table->foreignId('participant_id')->constrained()->cascadeOnDelete();
                $table->foreignId('training_session_id')->nullable()->constrained()->nullOnDelete();
                $table->foreignId('scanned_by')->constrained('users');
                $table->foreignId('branch_id')->nullable()->constrained('branches')->nullOnDelete();

                // The 30-second window the accepted code belonged to — not
                // "now". Recording now would leave the accepted neighbouring
                // counter reusable.
                $table->bigInteger('counter');
                $table->integer('skew_steps')->default(0);
                $table->string('scanner_ip', 45)->nullable();
                $table->timestamp('created_at')->nullable();

                $table->unique(['academy_id', 'participant_id', 'counter'], 'checkin_consumptions_once');
                $table->index(['academy_id', 'created_at']);
            });
        }
    }

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

        if (Schema::hasTable('participants') && Schema::hasColumn('participants', 'checkin_key_version')) {
            Schema::table('participants', function (Blueprint $table) {
                $table->dropColumn('checkin_key_version');
            });
        }
    }
};
