<?php

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

/**
 * Give every branch-owned record a branch of its own.
 *
 * Branch was built as a reporting lens: nine tables carried `branch_id`, the
 * trait that stamped it deliberately added no scope, and each of the 212
 * Livewire components was individually responsible for remembering to filter.
 * Most remembered for their main list query. Almost none remembered for the
 * <select> rendered beside it — so picking a branch narrowed the table and left
 * every dropdown offering the other branches' groups, players, facilities and
 * trainers. On OC-Sport, where only ZSC is live, that surfaced as stray rows
 * from six half-configured branches turning up in pickers all over the app.
 *
 * The fix is to make branch an isolation boundary the same way academy is: a
 * global scope on the model. A scope needs a column to filter on, which is what
 * this migration adds — plus the backfill that gives every existing row the
 * branch it has always actually belonged to.
 *
 * Shape of the change:
 *
 *   - Every new column is nullable and additive, and every step is guarded, so
 *     this is safe to run repeatedly against any populated client database.
 *     Nothing is dropped and nothing is rewritten outside `branch_id`.
 *   - Backfill runs parent-before-child, so a session gets its group's branch
 *     and an attendance record then gets the session's.
 *   - Rows a chain cannot reach fall back to the academy's main branch (or its
 *     lowest-id active branch when no branch is flagged main — OC-Sport has
 *     nine branches and `is_main` false on all of them). Pinning an
 *     unattributable row somewhere is strictly better than leaving it null,
 *     because a null on a strictly-scoped table is a row that is invisible in
 *     every branch at once.
 *
 * Two tables deserve a word:
 *
 *   `transactions` is immutable by house rule — corrections are new reversing
 *   entries, never edits. Writing `branch_id` onto an existing row is
 *   attribution, not accounting: no amount, account, date or type is touched,
 *   and the reversing-entry rule exists to protect the ledger's arithmetic.
 *   The precedent is 2026_09_03_000002, which backfilled this same column the
 *   same way; this migration only catches the rows created since.
 *
 *   Catalogue tables (products, base prices, pricing rules, promotions,
 *   receipt templates, programmes, holidays, warehouses, employees) are NOT
 *   backfilled. A null there means "every branch uses this row", and their
 *   models read it that way. Pinning the academy-wide base price to one branch
 *   would leave every other branch with no active price — and a missing base
 *   price is a hard failure that stops a sale.
 */
return new class extends Migration
{
    /**
     * Tables gaining a branch of their own, with the column the new one sits
     * after. Order is irrelevant here; the backfill order below is not.
     */
    private const NEW_COLUMNS = [
        // Training and attendance
        'enrollments' => 'academy_id',
        'training_sessions' => 'academy_id',
        'attendance_records' => 'academy_id',
        'evaluations' => 'academy_id',
        'waitlists' => 'academy_id',
        'assignments' => 'academy_id',

        // Facilities and space
        'space_layouts' => 'academy_id',
        'space_reservations' => 'academy_id',

        // Inventory
        'inventory_levels' => 'academy_id',
        'inventory_movements' => 'academy_id',
        'stock_counts' => 'academy_id',

        // People and HR
        'trainers' => 'academy_id',
        'payslips' => 'academy_id',
        'trainer_advances' => 'academy_id',
        'trainer_compensations' => 'academy_id',
        'guardians' => 'academy_id',

        // Operations
        'documents' => 'academy_id',
        'events' => 'academy_id',
        'service_requests' => 'academy_id',
        'wallets' => 'academy_id',
        'notification_logs' => 'academy_id',
        'whatsapp_messages' => 'academy_id',
    ];

    private const PARTICIPANT = 'App\\Domain\\Participant\\Models\\Participant';

    private const TRAINING_GROUP = 'App\\Domain\\Training\\Models\\TrainingGroup';

    private const FACILITY = 'App\\Domain\\Facility\\Models\\Facility';

    private const EMPLOYEE = 'App\\Domain\\HR\\Models\\Employee';

    /**
     * Strictly-scoped tables. A null branch on one of these hides the row from
     * every branch, so they get the main-branch fallback at the end.
     *
     * Catalogue tables are deliberately absent: null is a meaningful value
     * there and must survive.
     */
    private const STRICT_TABLES = [
        'enrollments',
        'training_sessions',
        'attendance_records',
        'evaluations',
        'waitlists',
        'assignments',
        'space_layouts',
        'space_reservations',
        'inventory_levels',
        'inventory_movements',
        'stock_counts',
        'payslips',
        'trainer_advances',
        'trainer_compensations',
        'documents',
        'service_requests',
        'invoices',
        'transactions',
        'participants',
    ];

    public function up(): void
    {
        $this->addColumns();
        $this->backfillFromParents();
        $this->repairExistingColumns();
        $this->fallbackToMainBranch();
        $this->addIndexes();
    }

    // ------------------------------------------------------------- columns

    private function addColumns(): void
    {
        foreach (self::NEW_COLUMNS as $table => $after) {
            if (! Schema::hasTable($table) || Schema::hasColumn($table, 'branch_id')) {
                continue;
            }

            Schema::table($table, function (Blueprint $blueprint) use ($table, $after) {
                $column = $blueprint->foreignId('branch_id')->nullable();

                if (Schema::hasColumn($table, $after)) {
                    $column->after($after);
                }

                // nullOnDelete rather than cascade: deleting a branch must
                // never delete a player's attendance history with it.
                $column->constrained('branches')->nullOnDelete();
            });
        }
    }

    // ------------------------------------------------------------ backfill

    /**
     * Fill each new column from the record that actually owns the branch.
     *
     * Strictly parent-before-child: training_sessions must be filled before
     * attendance_records, and trainers before payslips, or the child inherits
     * a null and falls through to the main-branch fallback instead of the
     * branch it genuinely belongs to.
     */
    private function backfillFromParents(): void
    {
        // -- Training ------------------------------------------------------

        $this->fill('enrollments', "
            UPDATE enrollments t SET branch_id = g.branch_id
            FROM training_groups g
            WHERE g.id = t.training_group_id AND t.branch_id IS NULL AND g.branch_id IS NOT NULL
        ");

        $this->fill('training_sessions', "
            UPDATE training_sessions t SET branch_id = g.branch_id
            FROM training_groups g
            WHERE g.id = t.training_group_id AND t.branch_id IS NULL AND g.branch_id IS NOT NULL
        ");

        // A session may name a facility that is not its group's branch; the
        // group is the owner, so it wins. This only reaches sessions the group
        // chain could not fill.
        $this->fill('training_sessions', "
            UPDATE training_sessions t SET branch_id = f.branch_id
            FROM facilities f
            WHERE f.id = t.facility_id AND t.branch_id IS NULL AND f.branch_id IS NOT NULL
        ");

        $this->fill('attendance_records', "
            UPDATE attendance_records t SET branch_id = s.branch_id
            FROM training_sessions s
            WHERE s.id = t.training_session_id AND t.branch_id IS NULL AND s.branch_id IS NOT NULL
        ");

        // Attendance may also be taken for a user (a trainer signing in), and
        // those rows have no session branch worth trusting over the player's.
        $this->fill('attendance_records', "
            UPDATE attendance_records t SET branch_id = p.branch_id
            FROM participants p
            WHERE t.branch_id IS NULL
              AND t.subject_type = '".self::PARTICIPANT."'
              AND t.subject_id = p.id
              AND p.branch_id IS NOT NULL
        ");

        // evaluations.training_group_id is nullable, so the participant is the
        // fallback rather than an afterthought.
        $this->fill('evaluations', "
            UPDATE evaluations t SET branch_id = g.branch_id
            FROM training_groups g
            WHERE g.id = t.training_group_id AND t.branch_id IS NULL AND g.branch_id IS NOT NULL
        ");

        $this->fill('evaluations', "
            UPDATE evaluations t SET branch_id = p.branch_id
            FROM participants p
            WHERE p.id = t.participant_id AND t.branch_id IS NULL AND p.branch_id IS NOT NULL
        ");

        $this->fill('waitlists', "
            UPDATE waitlists t SET branch_id = g.branch_id
            FROM training_groups g
            WHERE g.id = t.training_group_id AND t.branch_id IS NULL AND g.branch_id IS NOT NULL
        ");

        // -- Assignments (polymorphic over group and facility) --------------

        $this->fill('assignments', "
            UPDATE assignments t SET branch_id = g.branch_id
            FROM training_groups g
            WHERE t.branch_id IS NULL
              AND t.assignable_type = '".self::TRAINING_GROUP."'
              AND t.assignable_id = g.id
              AND g.branch_id IS NOT NULL
        ");

        $this->fill('assignments', "
            UPDATE assignments t SET branch_id = f.branch_id
            FROM facilities f
            WHERE t.branch_id IS NULL
              AND t.assignable_type = '".self::FACILITY."'
              AND t.assignable_id = f.id
              AND f.branch_id IS NOT NULL
        ");

        // -- Facilities and space ------------------------------------------

        $this->fill('space_layouts', "
            UPDATE space_layouts t SET branch_id = f.branch_id
            FROM facilities f
            WHERE f.id = t.facility_id AND t.branch_id IS NULL AND f.branch_id IS NOT NULL
        ");

        $this->fill('space_reservations', "
            UPDATE space_reservations t SET branch_id = f.branch_id
            FROM facilities f
            WHERE f.id = t.facility_id AND t.branch_id IS NULL AND f.branch_id IS NOT NULL
        ");

        // -- Inventory ------------------------------------------------------

        foreach (['inventory_levels', 'inventory_movements', 'stock_counts'] as $table) {
            $this->fill($table, "
                UPDATE {$table} t SET branch_id = w.branch_id
                FROM warehouses w
                WHERE w.id = t.warehouse_id AND t.branch_id IS NULL AND w.branch_id IS NOT NULL
            ");
        }

        // -- HR --------------------------------------------------------------

        // trainers.employee_id is nullable — a freelance coach has no employee
        // record — so trainers that cannot be reached stay null, which for a
        // shared-scoped table correctly means "available at every branch".
        $this->fill('trainers', "
            UPDATE trainers t SET branch_id = e.branch_id
            FROM employees e
            WHERE e.id = t.employee_id AND t.branch_id IS NULL AND e.branch_id IS NOT NULL
        ");

        // A coach with no employee record still belongs where they actually
        // coach. Only when every group and every scheduled slot of theirs is at
        // one branch — a coach who genuinely works two branches keeps a null,
        // which for a shared-scoped table correctly reads as "available at
        // both" rather than pinning them arbitrarily to one.
        $this->fill('trainers', "
            UPDATE trainers t SET branch_id = d.branch_id
            FROM (
                SELECT trainer_id, MIN(branch_id) AS branch_id
                FROM (
                    SELECT g.head_trainer_id AS trainer_id, g.branch_id
                    FROM training_groups g
                    WHERE g.head_trainer_id IS NOT NULL AND g.branch_id IS NOT NULL
                    UNION
                    SELECT s.trainer_id, g.branch_id
                    FROM training_schedules s
                    JOIN training_groups g ON g.id = s.training_group_id
                    WHERE s.trainer_id IS NOT NULL AND g.branch_id IS NOT NULL
                ) w
                GROUP BY trainer_id
                HAVING COUNT(DISTINCT branch_id) = 1
            ) d
            WHERE d.trainer_id = t.id AND t.branch_id IS NULL
        ");

        $this->fill('payslips', "
            UPDATE payslips t SET branch_id = tr.branch_id
            FROM trainers tr
            WHERE tr.id = t.trainer_id AND t.branch_id IS NULL AND tr.branch_id IS NOT NULL
        ");

        $this->fill('trainer_advances', "
            UPDATE trainer_advances t SET branch_id = tr.branch_id
            FROM trainers tr
            WHERE tr.id = t.trainer_id AND t.branch_id IS NULL AND tr.branch_id IS NOT NULL
        ");

        // A compensation row is earned at the session's branch, which is the
        // truth even when the trainer is filed elsewhere.
        $this->fill('trainer_compensations', "
            UPDATE trainer_compensations t SET branch_id = s.branch_id
            FROM training_sessions s
            WHERE s.id = t.training_session_id AND t.branch_id IS NULL AND s.branch_id IS NOT NULL
        ");

        $this->fill('trainer_compensations', "
            UPDATE trainer_compensations t SET branch_id = tr.branch_id
            FROM trainers tr
            WHERE tr.id = t.trainer_id AND t.branch_id IS NULL AND tr.branch_id IS NOT NULL
        ");

        // -- Guardians -------------------------------------------------------

        // Only when every child of theirs is at one branch. A guardian with
        // children at two branches keeps a null and stays visible at both,
        // which is the only answer that does not hide a family from a desk
        // that legitimately serves them.
        $this->fill('guardians', "
            UPDATE guardians t SET branch_id = d.branch_id
            FROM (
                SELECT gp.guardian_id, MIN(p.branch_id) AS branch_id
                FROM guardian_participant gp
                JOIN participants p ON p.id = gp.participant_id
                WHERE p.branch_id IS NOT NULL
                GROUP BY gp.guardian_id
                HAVING COUNT(DISTINCT p.branch_id) = 1
            ) d
            WHERE d.guardian_id = t.id AND t.branch_id IS NULL
        ");

        // -- Operations ------------------------------------------------------

        $this->fill('documents', "
            UPDATE documents t SET branch_id = p.branch_id
            FROM participants p
            WHERE t.branch_id IS NULL
              AND t.documentable_type = '".self::PARTICIPANT."'
              AND t.documentable_id = p.id
              AND p.branch_id IS NOT NULL
        ");

        $this->fill('documents', "
            UPDATE documents t SET branch_id = e.branch_id
            FROM employees e
            WHERE t.branch_id IS NULL
              AND t.documentable_type = '".self::EMPLOYEE."'
              AND t.documentable_id = e.id
              AND e.branch_id IS NOT NULL
        ");

        // events.facility_id is nullable and an academy-wide event is a real
        // thing, so a null here survives into the shared read.
        $this->fill('events', "
            UPDATE events t SET branch_id = f.branch_id
            FROM facilities f
            WHERE f.id = t.facility_id AND t.branch_id IS NULL AND f.branch_id IS NOT NULL
        ");

        $this->fill('service_requests', "
            UPDATE service_requests t SET branch_id = p.branch_id
            FROM participants p
            WHERE p.id = t.participant_id AND t.branch_id IS NULL AND p.branch_id IS NOT NULL
        ");

        $this->fill('wallets', "
            UPDATE wallets t SET branch_id = p.branch_id
            FROM participants p
            WHERE t.branch_id IS NULL
              AND t.owner_type = '".self::PARTICIPANT."'
              AND t.owner_id = p.id
              AND p.branch_id IS NOT NULL
        ");

        $this->fill('notification_logs', "
            UPDATE notification_logs t SET branch_id = p.branch_id
            FROM participants p
            WHERE t.branch_id IS NULL
              AND t.recipient_type = '".self::PARTICIPANT."'
              AND t.recipient_id = p.id
              AND p.branch_id IS NOT NULL
        ");

        if (Schema::hasColumn('whatsapp_messages', 'participant_id')) {
            $this->fill('whatsapp_messages', "
                UPDATE whatsapp_messages t SET branch_id = p.branch_id
                FROM participants p
                WHERE p.id = t.participant_id AND t.branch_id IS NULL AND p.branch_id IS NOT NULL
            ");
        }
    }

    // -------------------------------------------------- existing null repair

    /**
     * Tables that already had `branch_id` but were still accumulating nulls.
     *
     * 2026_09_03_000002 added and backfilled invoices and transactions; every
     * row created since then went through code paths that did not always set
     * it. On the restored OC-Sport copy that is 125 invoices and 57 ledger
     * entries which, once the scope is strict, would belong to no branch and
     * appear on no screen.
     */
    private function repairExistingColumns(): void
    {
        $this->fill('invoices', "
            UPDATE invoices i SET branch_id = p.branch_id
            FROM participants p
            WHERE i.branch_id IS NULL
              AND i.billable_type = '".self::PARTICIPANT."'
              AND i.billable_id = p.id
              AND p.branch_id IS NOT NULL
        ");

        $this->fill('invoices', "
            UPDATE invoices i SET branch_id = pay.branch_id
            FROM (
                SELECT DISTINCT ON (invoice_id) invoice_id, branch_id
                FROM payments
                WHERE invoice_id IS NOT NULL AND branch_id IS NOT NULL
                ORDER BY invoice_id, created_at ASC
            ) pay
            WHERE pay.invoice_id = i.id AND i.branch_id IS NULL
        ");

        $this->fill('payments', "
            UPDATE payments t SET branch_id = i.branch_id
            FROM invoices i
            WHERE i.id = t.invoice_id AND t.branch_id IS NULL AND i.branch_id IS NOT NULL
        ");

        $this->fill('transactions', "
            UPDATE transactions t SET branch_id = p.branch_id
            FROM payments p
            WHERE p.id = t.payment_id AND t.branch_id IS NULL AND p.branch_id IS NOT NULL
        ");

        if (Schema::hasColumn('transactions', 'invoice_id')) {
            $this->fill('transactions', "
                UPDATE transactions t SET branch_id = i.branch_id
                FROM invoices i
                WHERE i.id = t.invoice_id AND t.branch_id IS NULL AND i.branch_id IS NOT NULL
            ");
        }

        // A player with no branch is invisible everywhere once the scope is on,
        // and a player is the root of most other chains — so this one runs even
        // though the column is years old.
        $this->fill('participants', "
            UPDATE participants t SET branch_id = g.branch_id
            FROM (
                SELECT e.participant_id, MIN(g.branch_id) AS branch_id
                FROM enrollments e
                JOIN training_groups g ON g.id = e.training_group_id
                WHERE g.branch_id IS NOT NULL
                GROUP BY e.participant_id
                HAVING COUNT(DISTINCT g.branch_id) = 1
            ) g
            WHERE g.participant_id = t.id AND t.branch_id IS NULL
        ");
    }

    // ------------------------------------------------------------- fallback

    /**
     * Anything a chain could not reach is filed at the academy's main branch.
     *
     * `is_main DESC, id ASC` rather than `is_main` alone because no OC-Sport
     * branch is flagged main; without the id tiebreaker Postgres is free to
     * pick a different one per statement, and the same row could land in a
     * different branch than its siblings.
     */
    private function fallbackToMainBranch(): void
    {
        foreach (self::STRICT_TABLES as $table) {
            if (! Schema::hasTable($table) || ! Schema::hasColumn($table, 'branch_id')) {
                continue;
            }

            if (! Schema::hasColumn($table, 'academy_id')) {
                continue;
            }

            $this->fill($table, "
                UPDATE {$table} t SET branch_id = mb.branch_id
                FROM (
                    SELECT DISTINCT ON (academy_id) academy_id, id AS branch_id
                    FROM branches
                    WHERE is_active = true AND deleted_at IS NULL
                    ORDER BY academy_id, is_main DESC, id ASC
                ) mb
                WHERE mb.academy_id = t.academy_id AND t.branch_id IS NULL
            ");
        }
    }

    // -------------------------------------------------------------- indexes

    /**
     * Every scoped read now carries `WHERE branch_id = ?` beside the academy
     * filter that was already there, so the composite is the one that matters.
     */
    private function addIndexes(): void
    {
        foreach (array_keys(self::NEW_COLUMNS) as $table) {
            if (! Schema::hasTable($table) || ! Schema::hasColumn($table, 'branch_id')) {
                continue;
            }

            $name = $table.'_academy_branch_index';
            $columns = Schema::hasColumn($table, 'academy_id')
                ? ['academy_id', 'branch_id']
                : ['branch_id'];

            if ($this->indexExists($name)) {
                continue;
            }

            Schema::table($table, function (Blueprint $blueprint) use ($columns, $name) {
                $blueprint->index($columns, $name);
            });
        }
    }

    private function indexExists(string $name): bool
    {
        return DB::table('pg_indexes')->where('indexname', $name)->exists();
    }

    // --------------------------------------------------------------- helper

    private function fill(string $table, string $sql): void
    {
        if (! Schema::hasTable($table) || ! Schema::hasColumn($table, 'branch_id')) {
            return;
        }

        DB::statement($sql);
    }

    public function down(): void
    {
        foreach (array_keys(self::NEW_COLUMNS) as $table) {
            if (! Schema::hasTable($table) || ! Schema::hasColumn($table, 'branch_id')) {
                continue;
            }

            $name = $table.'_academy_branch_index';

            Schema::table($table, function (Blueprint $blueprint) use ($name) {
                if ($this->indexExists($name)) {
                    $blueprint->dropIndex($name);
                }

                $blueprint->dropConstrainedForeignId('branch_id');
            });
        }
    }
};
