<?php

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

/**
 * Consent, and the right to leave.
 *
 * Two things this product does today that it has no record of permission for:
 * it publishes children's photographs on a public website, and it sends
 * marketing over WhatsApp. There is no consent record anywhere in the schema —
 * not a column, not a table.
 *
 * And Apple 5.1.1(v) and Google both refuse an app that lets an account be
 * created without letting it be deleted from inside the app. That is not a
 * nice-to-have on the native shell; it is the difference between a submission
 * being reviewed and being rejected.
 *
 * Consents are **versioned and append-only**. A consent is a statement about a
 * particular text at a particular moment, so updating a row would destroy the
 * only thing that makes it evidence. Withdrawal is a new row, not an edit.
 *
 * A deletion request is a request, not a deletion. This is a financial system:
 * invoices, payments and ledger rows must survive a member leaving, so the
 * request is reviewed, personal data is redacted, and the accounting record
 * stays. Anything else would let a member erase an academy's books.
 */
return new class extends Migration
{
    private const TYPES = [
        'terms', 'privacy', 'photo_media', 'marketing', 'medical_treatment', 'data_processing',
    ];

    public function up(): void
    {
        if (! Schema::hasTable('consents')) {
            Schema::create('consents', function (Blueprint $table) {
                $table->id();
                $table->uuid('uuid')->unique();
                $table->foreignId('academy_id')->constrained()->cascadeOnDelete();

                // Who the consent is ABOUT — usually a child.
                $table->foreignId('participant_id')->nullable()->constrained()->cascadeOnDelete();
                // Who GAVE it — the adult who is answerable for that decision.
                $table->foreignId('given_by')->constrained('users');
                $table->foreignId('person_id')->nullable()->constrained('people')->nullOnDelete();

                $table->string('type', 32);
                $table->boolean('granted');
                $table->string('document_version', 20);
                $table->text('document_hash')->nullable();

                $table->timestamp('granted_at');
                $table->string('ip_address', 45)->nullable();
                $table->string('user_agent', 255)->nullable();
                $table->jsonb('metadata')->default('{}');

                // No updated_at, deliberately: see the class comment.
                $table->timestamp('created_at')->nullable();

                $table->index(['academy_id', 'participant_id', 'type']);
                $table->index(['academy_id', 'type', 'granted_at']);
            });

            if (DB::getDriverName() === 'pgsql') {
                $list = implode(', ', array_map(fn ($t) => "'{$t}'", self::TYPES));
                DB::statement("ALTER TABLE consents ADD CONSTRAINT consents_type_check CHECK (type IN ({$list}))");

                // Append-only in the database, not merely by convention. A
                // consent that can be edited is not evidence of anything.
                DB::unprepared(<<<'SQL'
                    CREATE OR REPLACE FUNCTION consents_are_append_only() RETURNS trigger AS $$
                    BEGIN
                        RAISE EXCEPTION 'consents are append-only; record a new row to withdraw';
                    END;
                    $$ LANGUAGE plpgsql;

                    CREATE TRIGGER consents_no_update
                    BEFORE UPDATE OR DELETE ON consents
                    FOR EACH ROW EXECUTE FUNCTION consents_are_append_only();
                SQL);
            }
        }

        if (! Schema::hasTable('account_deletion_requests')) {
            Schema::create('account_deletion_requests', function (Blueprint $table) {
                $table->id();
                $table->uuid('uuid')->unique();
                $table->foreignId('academy_id')->constrained()->cascadeOnDelete();
                $table->foreignId('user_id')->constrained()->cascadeOnDelete();

                $table->string('status', 20)->default('pending');
                $table->text('reason')->nullable();

                // A deletion cannot be undone, so it does not happen the moment
                // an angry parent taps a button. The window is the cooling-off
                // period, and it is also how long an outstanding balance has to
                // be settled.
                $table->timestamp('requested_at');
                $table->timestamp('eligible_at');
                $table->timestamp('completed_at')->nullable();
                $table->timestamp('cancelled_at')->nullable();

                $table->foreignId('handled_by')->nullable()->constrained('users')->nullOnDelete();
                $table->text('admin_notes')->nullable();
                $table->jsonb('blockers')->default('[]');
                $table->string('request_ip', 45)->nullable();
                $table->timestamps();

                $table->index(['academy_id', 'status', 'eligible_at']);
            });

            if (DB::getDriverName() === 'pgsql') {
                DB::statement("ALTER TABLE account_deletion_requests ADD CONSTRAINT account_deletion_requests_status_check CHECK (status IN ('pending','blocked','completed','cancelled'))");
                DB::statement("CREATE UNIQUE INDEX account_deletion_requests_one_open ON account_deletion_requests (user_id) WHERE status IN ('pending','blocked')");
            }
        }

        // A redaction marker on people, so a completed deletion is visible
        // without having to reconstruct it from the request table.
        if (Schema::hasTable('people') && ! Schema::hasColumn('people', 'redacted_at')) {
            Schema::table('people', function (Blueprint $table) {
                $table->timestamp('redacted_at')->nullable()->after('deleted_at');
            });
        }
    }

    public function down(): void
    {
        if (DB::getDriverName() === 'pgsql') {
            DB::unprepared('DROP TRIGGER IF EXISTS consents_no_update ON consents; DROP FUNCTION IF EXISTS consents_are_append_only();');
        }

        Schema::dropIfExists('account_deletion_requests');
        Schema::dropIfExists('consents');

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