<?php

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

/**
 * Give the catalogue a branch of its own.
 *
 * 2026_09_13_000001 treated programmes, products and prices as *shared*: a null
 * `branch_id` meant "every branch uses this row". That was the cautious reading,
 * because a missing base price is a hard failure that stops a sale, and pinning
 * the academy-wide price to one branch would have left the others unable to sell
 * anything.
 *
 * It is not what the product actually wants. A branch is meant to read as its
 * own installation: its own programmes, its own groups, its own members, its own
 * pitches, its own products and its own prices. A programme offered at one
 * branch turning up in another branch's dropdown is the same bug as a group
 * doing it — the shared bucket just hid it behind a plausible-sounding rule.
 *
 * So the catalogue moves to the strict bucket, and this migration makes the data
 * safe for that before the models start enforcing it. The check that made it
 * safe to do: in the live tenant, no group points at a programme in another
 * branch, no base price prices a programme in another branch, and only three
 * catalogue rows in total had no branch at all. There is nothing to tear apart —
 * the catalogue was already per-branch in practice and merely unlabelled.
 *
 * Deliberately NOT moved:
 *
 *   `events` goes the other way and loses its trait entirely. An event is an
 *   academy-wide occasion — a tournament, an open day — and is one of the few
 *   things that genuinely is not per branch. The column stays (dropping it
 *   would be destructive, and a future per-branch event is plausible); nothing
 *   reads it.
 *
 *   `employees`, `trainers`, `guardians` and `holidays` stay shared, because a
 *   person is the one thing that legitimately belongs to two branches at once.
 *   A coach who works Monday at one pitch and Wednesday at another needs one
 *   record visible from both, not two records that drift. Their nulls are
 *   narrowed below rather than removed: anyone who demonstrably belongs to a
 *   single branch is pinned to it, and only genuinely shared people keep the
 *   null. In the live tenant that leaves no trainer and no employee shared, so
 *   the practical result is per-branch anyway.
 *
 * Safe to re-run, additive, and guarded throughout, like its predecessor.
 */
return new class extends Migration
{
    /**
     * Catalogue tables that already have the column and only need their nulls
     * resolved before the strict scope starts hiding them.
     */
    private const TIGHTEN = [
        'training_programs',
        'products',
        'base_prices',
        'pricing_rules',
        'promotions',
        'receipt_templates',
        'warehouses',
        'wallets',
    ];

    /**
     * Catalogue tables that never had a branch at all.
     */
    private const NEW_COLUMNS = [
        'kits' => 'academy_id',
        'product_categories' => 'academy_id',
    ];

    public function up(): void
    {
        $this->addColumns();
        $this->backfillFromUsage();
        $this->fallbackToMainBranch();
        $this->narrowSharedPeople();
        $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);
                }

                $column->constrained('branches')->nullOnDelete();
            });
        }
    }

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

    /**
     * Where a catalogue row is demonstrably used by exactly one branch, that is
     * its branch — a far better answer than the main-branch fallback, and the
     * only one that keeps a second branch's programme out of the first's lists.
     */
    private function backfillFromUsage(): void
    {
        // A programme belongs where its groups run. COUNT(DISTINCT) = 1 so a
        // programme genuinely shared between two branches is left for the
        // fallback rather than being pinned to whichever branch sorted first.
        $this->fill('training_programs', "
            UPDATE training_programs t SET branch_id = d.branch_id
            FROM (
                SELECT training_program_id, MIN(branch_id) AS branch_id
                FROM training_groups
                WHERE branch_id IS NOT NULL AND deleted_at IS NULL
                GROUP BY training_program_id
                HAVING COUNT(DISTINCT branch_id) = 1
            ) d
            WHERE d.training_program_id = t.id AND t.branch_id IS NULL
        ");

        // A price belongs with the thing it prices. Programmes are resolved
        // above first, so this inherits the answer rather than racing it.
        $this->fill('base_prices', "
            UPDATE base_prices t SET branch_id = p.branch_id
            FROM training_programs p
            WHERE p.id = t.priceable_id
              AND t.priceable_type LIKE '%TrainingProgram'
              AND t.branch_id IS NULL
              AND p.branch_id IS NOT NULL
        ");

        $this->fill('base_prices', "
            UPDATE base_prices t SET branch_id = pr.branch_id
            FROM products pr
            WHERE pr.id = t.priceable_id
              AND t.priceable_type LIKE '%Product'
              AND t.branch_id IS NULL
              AND pr.branch_id IS NOT NULL
        ");

        // A wallet is a member's balance and goes wherever the member is.
        if (Schema::hasTable('wallets') && Schema::hasColumn('wallets', 'participant_id')) {
            $this->fill('wallets', "
                UPDATE wallets 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
            ");
        }
    }

    /**
     * Anything left unattributed goes to the academy's main branch.
     *
     * `is_main DESC, id ASC` rather than is_main alone: several tenants have
     * is_main false on every branch, and without the id tiebreaker Postgres is
     * free to return a different one each run.
     */
    private function fallbackToMainBranch(): void
    {
        $tables = array_merge(self::TIGHTEN, array_keys(self::NEW_COLUMNS));

        foreach ($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
            ");
        }
    }

    /**
     * People stay shared, but only where sharing is real.
     *
     * A guardian with no children at all is not shared — they are unattributed,
     * and leaving them null puts them in every branch's list for no reason. One
     * with children at two branches genuinely is shared and keeps the null.
     */
    private function narrowSharedPeople(): void
    {
        if (! Schema::hasTable('guardian_participant')) {
            return;
        }

        $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 AND p.deleted_at IS NULL
                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
        ");

        // Childless guardians: nothing ties them anywhere, so file them at the
        // main branch rather than showing them to every branch forever.
        $this->fill('guardians', "
            UPDATE guardians 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
              AND NOT EXISTS (SELECT 1 FROM guardian_participant gp WHERE gp.guardian_id = t.id)
        ");
    }

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

    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';

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

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

            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();
    }

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

        DB::statement($sql);
    }

    /**
     * Only the added columns come off. The backfilled branch ids stay: they are
     * the branch each row has always actually belonged to, and re-deriving them
     * on a re-run is wasted work, not a correction.
     */
    public function down(): void
    {
        foreach (array_keys(self::NEW_COLUMNS) as $table) {
            if (! Schema::hasTable($table) || ! Schema::hasColumn($table, 'branch_id')) {
                continue;
            }

            Schema::table($table, function (Blueprint $blueprint) use ($table) {
                $blueprint->dropForeign([$table.'_branch_id_foreign']);
                $blueprint->dropColumn('branch_id');
            });
        }
    }
};
