<?php

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

/**
 * Admin-issued invitations are how a member gets a portal account: staff issue
 * a link, the member sets a password, and phone-or-email plus password works
 * from then on.
 *
 * Only the SHA-256 of the token is stored. A raw token in the database is a
 * password in the database — and this one would also have rendered into the
 * 500 error page's `$inputData` dump before that was gated.
 *
 * `users.email_is_synthetic` exists because most guardians have no email while
 * `users.email` is NOT NULL UNIQUE — and that constraint cannot be relaxed:
 * 2024_01_01_000002 declares it inside Schema::create, so Postgres emits a
 * UNIQUE CONSTRAINT, which cannot be made partial without a DROP CONSTRAINT in
 * up(); CREATE INDEX CONCURRENTLY cannot run inside a migration transaction;
 * and password_reset_tokens.email is the primary key the broker keys on.
 * So portal accounts get p{uuid}@portal.invalid — RFC 2606, guaranteed
 * non-routable, so it can never bounce off the mail server — and every mail
 * path skips a synthetic address.
 *
 * Deliberately NOT added: UNIQUE(academy_id, phone) on users.
 * 2026_08_30_000004 logged that it left duplicates in place on live data and
 * that they need a manual merge. A unique index would hard-fail on at least one
 * client, and a failed migration then blocks every later migration on that
 * client forever. Phone login rejects an ambiguous match instead, and the merge
 * screen exists to clear the duplicates first.
 */
return new class extends Migration
{
    public function up(): void
    {
        if (! Schema::hasTable('portal_invitations')) {
            Schema::create('portal_invitations', function (Blueprint $table) {
                $table->id();
                $table->uuid('uuid')->unique();
                $table->foreignId('academy_id')->constrained()->cascadeOnDelete();
                $table->foreignId('person_id')->constrained('people')->cascadeOnDelete();
                $table->foreignId('participant_id')->nullable()->constrained()->nullOnDelete();

                // The token itself never touches the database.
                $table->string('token_hash', 64)->unique();

                $table->string('channel', 16)->default('link');   // link | whatsapp | email
                $table->string('sent_to', 120)->nullable();
                $table->timestamp('expires_at');
                $table->timestamp('consumed_at')->nullable();
                $table->foreignId('consumed_by')->nullable()->constrained('users')->nullOnDelete();
                $table->timestamp('revoked_at')->nullable();
                $table->foreignId('created_by')->constrained('users');
                $table->string('created_ip', 45)->nullable();
                $table->timestamps();

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

            if (DB::getDriverName() === 'pgsql') {
                DB::statement("ALTER TABLE portal_invitations ADD CONSTRAINT portal_invitations_channel_check CHECK (channel IN ('link','whatsapp','email'))");

                // One live invitation per person. Re-inviting revokes the old
                // one rather than leaving two working links in the wild.
                DB::statement('CREATE UNIQUE INDEX portal_invitations_one_live ON portal_invitations (academy_id, person_id) WHERE consumed_at IS NULL AND revoked_at IS NULL');
            }
        }

        if (Schema::hasTable('users') && ! Schema::hasColumn('users', 'email_is_synthetic')) {
            Schema::table('users', function (Blueprint $table) {
                $table->boolean('email_is_synthetic')->default(false)->after('email');
                $table->timestamp('portal_last_seen_at')->nullable()->after('email_is_synthetic');
            });

            // Anything already carrying a .invalid address predates this flag.
            DB::table('users')->where('email', 'like', '%@portal.invalid')->update(['email_is_synthetic' => true]);
        }
    }

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

        if (Schema::hasTable('users') && Schema::hasColumn('users', 'email_is_synthetic')) {
            Schema::table('users', function (Blueprint $table) {
                $table->dropColumn(['email_is_synthetic', 'portal_last_seen_at']);
            });
        }
    }
};
