<?php

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

/**
 * Public self-registration, shipped **off**.
 *
 * This is the workstream that writes into `people` and `participants` with no
 * member of staff in the loop — the tables the whole ERP hangs off. An open
 * signup form with no verification is a spam and duplicate-record generator,
 * so phone verification is not an enhancement here, it is the precondition.
 *
 * `portal.self_registration_enabled` defaults to **false** on every tenant,
 * including new ones. The code ships; nothing changes for anybody until an
 * academy turns it on deliberately. A public form that starts accepting
 * strangers because a deploy happened is not a decision anyone made.
 *
 * `auth_otp_mode` is re-seeded to 'sms' here. It was seeded as 'demo' by
 * 2026_07_27_000004, and 'demo' was the setting the deleted API's `0000`
 * bypass keyed on. The bypass is gone, but leaving a setting called demo mode
 * in place for a new tenant to inherit is an invitation.
 */
return new class extends Migration
{
    public function up(): void
    {
        if (! Schema::hasTable('otp_verifications')) {
            Schema::create('otp_verifications', function (Blueprint $table) {
                $table->id();
                $table->foreignId('academy_id')->constrained()->cascadeOnDelete();
                $table->string('phone', 20);

                // Only the hash. A one-time code sitting in plaintext in a
                // table is a password sitting in plaintext in a table, and the
                // cache is not a safer place for it — it is a less auditable one.
                $table->string('code_hash', 64);

                $table->string('purpose', 24)->default('registration');
                $table->unsignedTinyInteger('attempts')->default(0);
                $table->timestamp('expires_at');
                $table->timestamp('verified_at')->nullable();
                $table->string('request_ip', 45)->nullable();
                $table->timestamps();

                $table->index(['academy_id', 'phone', 'purpose']);
                $table->index('expires_at');
            });

            if (DB::getDriverName() === 'pgsql') {
                DB::statement("ALTER TABLE otp_verifications ADD CONSTRAINT otp_verifications_purpose_check CHECK (purpose IN ('registration','password_reset','phone_change'))");

                // One live code per phone per purpose. Requesting again
                // supersedes rather than leaving several valid codes alive.
                DB::statement("CREATE UNIQUE INDEX otp_verifications_one_live ON otp_verifications (academy_id, phone, purpose) WHERE verified_at IS NULL");
            }
        }

        // A self-registered participant is not a member yet.
        if (Schema::hasTable('participants') && ! Schema::hasColumn('participants', 'approved_at')) {
            Schema::table('participants', function (Blueprint $table) {
                $table->timestamp('approved_at')->nullable()->after('registration_source');
                $table->foreignId('approved_by')->nullable()->after('approved_at')
                    ->constrained('users')->nullOnDelete();
                $table->jsonb('duplicate_candidates')->nullable()->after('approved_by');
            });

            // Everything that already exists was created by staff, so it is
            // approved by definition — backfilled rather than left null, which
            // would make every existing member look like a pending signup.
            DB::statement('UPDATE participants SET approved_at = created_at WHERE approved_at IS NULL');

            Schema::table('participants', function (Blueprint $table) {
                $table->index(['academy_id', 'approved_at'], 'participants_academy_approved_index');
            });
        }

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

        foreach (DB::table('academies')->pluck('id') as $academyId) {
            $this->seed($academyId, 'portal', 'portal.self_registration_enabled', '0', 'boolean');
            $this->seed($academyId, 'portal', 'portal.self_registration_requires_approval', '1', 'boolean');

            // Re-seeded rather than assumed: 2026_07_27_000004 only touched
            // academies that existed when it ran, so a tenant created since
            // then would otherwise inherit the old default.
            DB::table('system_settings')
                ->where('academy_id', $academyId)
                ->where('key', 'auth_otp_mode')
                ->where('value', 'demo')
                ->update(['value' => 'sms', 'updated_at' => now()]);

            $this->seed($academyId, 'mobile_app', 'auth_otp_mode', 'sms', 'string');
        }
    }

    private function seed(int $academyId, string $group, string $key, string $value, string $type): void
    {
        $exists = DB::table('system_settings')
            ->where('academy_id', $academyId)
            ->where('key', $key)
            ->exists();

        if ($exists) {
            return;
        }

        DB::table('system_settings')->insert([
            'academy_id' => $academyId,
            'key' => $key,
            'value' => $value,
            'group' => $group,
            'type' => $type,
            'created_at' => now(),
            'updated_at' => now(),
        ]);
    }

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

        if (Schema::hasTable('participants') && Schema::hasColumn('participants', 'approved_at')) {
            Schema::table('participants', function (Blueprint $table) {
                $table->dropIndex('participants_academy_approved_index');
                $table->dropConstrainedForeignId('approved_by');
                $table->dropColumn(['approved_at', 'duplicate_candidates']);
            });
        }
    }
};
