<?php

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

/**
 * "Every tenant table has academy_id" is a hard invariant, and four tables
 * break it. Each is reachable from the member portal, where a query that
 * escapes the tenant scope is a cross-academy read rather than a reporting
 * quirk:
 *
 *  - invoice_items            — the portal renders invoice lines
 *  - installments             — the portal lists and pays instalments
 *  - notification_preferences — per-event push preferences
 *  - event_registrations      — links only person_id, so "which of my children
 *                               is registered for this event" is unanswerable
 *                               as modelled; the portal's events screen needs
 *                               exactly that.
 *
 * Every column is nullable and backfilled from its parent. The models keep
 * resolving through their parent relation; the column exists so a query can be
 * scoped directly and an index can be built on it.
 */
return new class extends Migration
{
    public function up(): void
    {
        $this->addAcademyId('invoice_items', 'invoice_id', 'invoices');
        $this->addAcademyId('notification_preferences', null, null);

        // installments reach an academy through payment_plans → invoices.
        if (Schema::hasTable('installments') && ! Schema::hasColumn('installments', 'academy_id')) {
            Schema::table('installments', function (Blueprint $table) {
                $table->foreignId('academy_id')->nullable()->after('id')
                    ->constrained('academies')->cascadeOnDelete();
            });

            if (Schema::hasTable('payment_plans')) {
                DB::statement("
                    UPDATE installments i
                    SET academy_id = pp.academy_id
                    FROM payment_plans pp
                    WHERE pp.id = i.payment_plan_id AND i.academy_id IS NULL
                ");
            }

            Schema::table('installments', function (Blueprint $table) {
                $table->index(['academy_id', 'status', 'due_date'], 'installments_academy_status_due_index');
            });
        }

        // event_registrations already carries academy_id; what it lacks is the
        // participant the registration is for.
        if (Schema::hasTable('event_registrations') && ! Schema::hasColumn('event_registrations', 'participant_id')) {
            Schema::table('event_registrations', function (Blueprint $table) {
                $table->foreignId('participant_id')->nullable()->after('person_id')
                    ->constrained('participants')->nullOnDelete();
            });

            // One person may hold several participant rows across academies;
            // resolve within the registration's own academy only, and only
            // when the answer is unambiguous.
            if (Schema::hasTable('participants')) {
                DB::statement("
                    UPDATE event_registrations er
                    SET participant_id = sub.pid
                    FROM (
                        SELECT p.person_id, p.academy_id, MIN(p.id) AS pid, COUNT(*) AS n
                        FROM participants p
                        GROUP BY p.person_id, p.academy_id
                    ) sub
                    WHERE er.participant_id IS NULL
                      AND er.person_id = sub.person_id
                      AND er.academy_id = sub.academy_id
                      AND sub.n = 1
                ");
            }

            Schema::table('event_registrations', function (Blueprint $table) {
                $table->index(['academy_id', 'participant_id'], 'event_reg_academy_participant_index');
            });
        }
    }

    private function addAcademyId(string $table, ?string $parentKey, ?string $parentTable): void
    {
        if (! Schema::hasTable($table) || Schema::hasColumn($table, 'academy_id')) {
            return;
        }

        Schema::table($table, function (Blueprint $t) {
            $t->foreignId('academy_id')->nullable()->after('id')
                ->constrained('academies')->cascadeOnDelete();
        });

        if ($parentKey && $parentTable && Schema::hasTable($parentTable)) {
            DB::statement("
                UPDATE {$table} c
                SET academy_id = p.academy_id
                FROM {$parentTable} p
                WHERE p.id = c.{$parentKey} AND c.academy_id IS NULL
            ");
        }

        if ($table === 'notification_preferences' && Schema::hasTable('users')) {
            DB::statement("
                UPDATE notification_preferences np
                SET academy_id = u.academy_id
                FROM users u
                WHERE u.id = np.user_id AND np.academy_id IS NULL
            ");
        }

        Schema::table($table, function (Blueprint $t) use ($table) {
            $t->index('academy_id', "{$table}_academy_id_index");
        });
    }

    public function down(): void
    {
        foreach (['invoice_items', 'notification_preferences', 'installments'] as $table) {
            if (Schema::hasTable($table) && Schema::hasColumn($table, 'academy_id')) {
                Schema::table($table, function (Blueprint $t) {
                    $t->dropConstrainedForeignId('academy_id');
                });
            }
        }

        if (Schema::hasTable('event_registrations') && Schema::hasColumn('event_registrations', 'participant_id')) {
            Schema::table('event_registrations', function (Blueprint $t) {
                $t->dropConstrainedForeignId('participant_id');
            });
        }
    }
};
