<?php

namespace Tests\Feature;

use App\Domain\Shared\Context\BranchContext;
use App\Domain\Shared\Models\Academy;
use App\Models\User;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Route;
use Tests\TestCase;

/**
 * Watch the SQL a dashboard actually runs, and refuse any query that reads a
 * branch-owned table without saying which branch.
 *
 *   DB_CONNECTION=pgsql DB_DATABASE=oc_sport_test ./vendor/bin/phpunit --filter DashboardQueriesRespectBranchTest
 *
 * The other two suites cannot catch this. BranchIsolationTest proves the model
 * scope narrows a model, and BranchScopedScreensTest looks for another branch's
 * *records* in the rendered HTML — but a dashboard renders totals, not records.
 * A revenue widget that quietly summed every branch would show a number that is
 * simply wrong, with no uuid anywhere to give it away, and both suites would
 * pass.
 *
 * Dashboards are also where the raw query builder lives, because that is what
 * aggregates are written in — and a raw DB::table() goes straight past every
 * global scope. So this asserts at the only layer that sees both: the wire.
 *
 * The rule is deliberately crude, because a strict SQL parse would be worse
 * than useless here: any query that reads a branch-owned table must mention
 * `branch_id` *somewhere* — in its own WHERE, in a join, or in a subquery the
 * scope put there. A query that never says the words is one that never asked.
 */
class DashboardQueriesRespectBranchTest extends TestCase
{
    /**
     * Tables where every row belongs to exactly one branch, and reading them
     * without a branch predicate means reading every branch.
     */
    private const BRANCH_OWNED = [
        'participants', 'training_groups', 'enrollments', 'training_sessions',
        'attendance_records', 'invoices', 'payments', 'transactions', 'expenses',
        'pos_transactions', 'cash_sessions', 'facilities', 'space_reservations',
        'training_programs', 'base_prices', 'products', 'inventory_levels',
        'inventory_movements', 'payslips', 'trainer_advances',
    ];

    /**
     * Every screen whose job is to show a number.
     *
     * Named explicitly rather than discovered, so that adding a dashboard and
     * forgetting to add it here is a visible omission in review rather than a
     * silent gap in coverage.
     */
    private const DASHBOARDS = [
        'dashboard',
        'financial.overview',
        'reports.hub',
        'reports.attendance',
        'payroll.dashboard',
        'payroll.advances',
        'receptionist.dashboard',
        'pos.history',
        'cash-sessions.list',
        'inventory.movements',
        'invoices.list',
        'expenses.list',
        'enrollments.list',
        'participants.list',
        'groups.list',
        'attendance.list',
    ];

    private User $owner;

    protected function setUp(): void
    {
        parent::setUp();

        if (config('database.default') !== 'pgsql') {
            $this->markTestSkipped('Needs a restored Postgres tenant; see the class comment.');
        }

        if (! $academy = Academy::first()) {
            $this->markTestSkipped('No academy in the restored tenant.');
        }

        app()->instance('current_academy', $academy);

        $owner = User::withoutGlobalScopes()
            ->whereHas('primaryRole', fn ($q) => $q->where('slug', 'academy_owner'))
            ->first();

        if (! $owner) {
            $this->markTestSkipped('No academy_owner in the restored tenant.');
        }

        $this->owner = $owner;
    }

    private function branchId(): int
    {
        return (int) DB::table('training_groups')
            ->whereNotNull('branch_id')->orderBy('branch_id')->value('branch_id');
    }

    /**
     * Does this query read a branch-owned table?
     *
     * Matches the quoted identifier Postgres emits ("participants") as well as
     * the bare word, so `participant_id` on some other table does not count.
     */
    private function tablesRead(string $sql): array
    {
        $hit = [];

        foreach (self::BRANCH_OWNED as $table) {
            if (preg_match('/(?:from|join|into|update)\s+"?'.$table.'"?\b/i', $sql)) {
                $hit[] = $table;
            }
        }

        return $hit;
    }

    public function test_no_dashboard_reads_a_branch_owned_table_without_naming_a_branch(): void
    {
        $branchId = $this->branchId();
        $offenders = [];

        foreach (self::DASHBOARDS as $name) {
            if (! Route::has($name)) {
                $this->fail("Route {$name} no longer exists — update the dashboard list.");
            }

            $captured = [];
            DB::listen(function ($query) use (&$captured) {
                $captured[] = $query->sql;
            });

            $response = $this->actingAs($this->owner)
                ->withSession([BranchContext::KEY => $branchId])
                ->get(route($name));

            // A screen the owner cannot open tells us nothing either way.
            if ($response->getStatusCode() !== 200) {
                continue;
            }

            foreach ($captured as $sql) {
                $tables = $this->tablesRead($sql);

                if ($tables === []) {
                    continue;
                }

                if (stripos($sql, 'branch_id') !== false) {
                    continue;
                }

                $offenders[] = $name.' → '.implode(',', $tables).' :: '
                    .substr(preg_replace('/\s+/', ' ', $sql), 0, 260);
            }
        }

        $this->assertSame(
            [],
            $offenders,
            "These queries read a branch-owned table without naming a branch:\n\n"
                .implode("\n\n", array_unique($offenders))
        );
    }


    /**
     * The branch ids a query actually binds into a `branch_id` comparison.
     *
     * Laravel's bindings are positional, so the value belonging to a predicate
     * is found by counting the placeholders that precede it. Crude string work,
     * but the alternative is trusting that a query which merely *mentions*
     * branch_id mentions the right one — which is the whole question here.
     *
     * @return array<int, mixed>
     */
    private function boundBranchValues(string $sql, array $bindings): array
    {
        preg_match_all('/\?/', $sql, $m, PREG_OFFSET_CAPTURE);
        $placeholders = array_map(fn ($x) => $x[1], $m[0]);

        if ($placeholders === []) {
            return [];
        }

        // `"branch_id" = ?`, `t."branch_id" = ?`, `"branch_id" in (?, ?)`.
        preg_match_all(
            '/"?branch_id"?\s*(?:=|<>|!=|in)\s*\(?/i',
            $sql,
            $pm,
            PREG_OFFSET_CAPTURE
        );

        $values = [];

        foreach ($pm[0] as [$text, $offset]) {
            $end = $offset + strlen($text);
            $isIn = stripos($text, 'in') !== false;

            foreach ($placeholders as $index => $pos) {
                if ($pos < $end) {
                    continue;
                }

                // For `= ?` only the next placeholder belongs to the predicate.
                // For `in (…)` take every placeholder up to the closing paren.
                if (! $isIn) {
                    $values[] = $bindings[$index] ?? null;
                    break;
                }

                $close = strpos($sql, ')', $end);

                if ($close !== false && $pos > $close) {
                    break;
                }

                $values[] = $bindings[$index] ?? null;
            }
        }

        return $values;
    }

    /**
     * Run a callback with every executed query captured.
     *
     * @return array<int, array{sql: string, bindings: array}>
     */
    private function captureQueries(callable $work): array
    {
        $captured = [];

        DB::listen(function ($query) use (&$captured) {
            $captured[] = ['sql' => $query->sql, 'bindings' => $query->bindings];
        });

        $work();

        return $captured;
    }

    /**
     * @param  array<int, array{sql: string, bindings: array}>  $queries
     * @return string[]
     */
    private function wrongBranchBindings(array $queries, int $expected, string $label): array
    {
        $wrong = [];

        foreach ($queries as $query) {
            foreach ($this->boundBranchValues($query['sql'], $query['bindings']) as $value) {
                if ($value === null || $value === '') {
                    continue;
                }

                // Positional extraction misreads a handful of queries where
                // branch_id sits in a subquery with its own placeholders, and
                // lands on a date or a status string instead. Those are dropped
                // rather than guessed at: the check that matters is that no
                // *branch id* other than the selected one is ever bound, and a
                // misread cannot manufacture one.
                if (! is_numeric($value)) {
                    continue;
                }

                if ((int) $value !== $expected) {
                    $wrong[] = $label.' bound branch '.$value.' while branch '.$expected
                        .' was selected :: '.substr(preg_replace('/\s+/', ' ', $query['sql']), 0, 200);
                }
            }
        }

        return $wrong;
    }

    public function test_every_dashboard_binds_the_branch_the_user_selected(): void
    {
        $branches = DB::table('training_groups')
            ->whereNotNull('branch_id')->distinct()->orderBy('branch_id')
            ->pluck('branch_id')->map(fn ($id) => (int) $id)->all();

        $this->assertGreaterThanOrEqual(2, count($branches), 'Needs two branches carrying data.');

        $wrong = [];

        // Both branches, because a widget that hardcoded the main branch would
        // look perfectly correct when the main branch is the one selected.
        foreach ($branches as $branchId) {
            foreach (self::DASHBOARDS as $name) {
                $response = null;

                $queries = $this->captureQueries(function () use ($name, $branchId, &$response) {
                    $response = $this->actingAs($this->owner)
                        ->withSession([BranchContext::KEY => $branchId])
                        ->get(route($name));
                });

                if ($response->getStatusCode() !== 200) {
                    continue;
                }

                $wrong = array_merge($wrong, $this->wrongBranchBindings($queries, $branchId, $name));
            }
        }

        $this->assertSame(
            [],
            array_values(array_unique($wrong)),
            "A dashboard filtered by a branch other than the selected one:\n\n"
                .implode("\n\n", array_unique($wrong))
        );
    }

    public function test_every_dashboard_widget_binds_the_selected_branch(): void
    {
        // Mounted directly, so a widget is covered even where it is not
        // currently embedded in a page — EnrollmentTrends and RevenueWidget are
        // both written and neither is on a view today.
        $widgets = [
            \App\Livewire\Dashboard\EnrollmentTrends::class,
            \App\Livewire\Dashboard\OverdueRenewalsAlert::class,
            \App\Livewire\Dashboard\ProductRevenueWidget::class,
            \App\Livewire\Dashboard\RenewalsDueWidget::class,
            \App\Livewire\Dashboard\RevenueWidget::class,
            \App\Livewire\Dashboard\SubscriptionRevenueWidget::class,
            \App\Livewire\Dashboard\TrainerDuesWidget::class,
        ];

        $branches = DB::table('training_groups')
            ->whereNotNull('branch_id')->distinct()->orderBy('branch_id')
            ->pluck('branch_id')->map(fn ($id) => (int) $id)->all();

        $wrong = [];
        $mounted = 0;

        foreach ($branches as $branchId) {
            foreach ($widgets as $widget) {
                $this->actingAs($this->owner);
                session([BranchContext::KEY => $branchId]);
                app()->forgetInstance(\App\Domain\Shared\Context\BranchContext::class);
                app(\App\Domain\Shared\Context\BranchScopeState::class)->activate($branchId);

                $queries = $this->captureQueries(function () use ($widget) {
                    \Livewire\Livewire::test($widget);
                });

                $mounted++;
                $wrong = array_merge(
                    $wrong,
                    $this->wrongBranchBindings($queries, $branchId, class_basename($widget))
                );
            }
        }

        app(\App\Domain\Shared\Context\BranchScopeState::class)->deactivate();

        $this->assertGreaterThan(0, $mounted, 'No widget was mounted — the test proved nothing.');
        $this->assertSame(
            [],
            array_values(array_unique($wrong)),
            "A widget filtered by a branch other than the selected one:\n\n"
                .implode("\n\n", array_unique($wrong))
        );
    }

    /**
     * The counterpart to the query check: the numbers themselves must move when
     * the branch does.
     *
     * A widget that ignored the branch would return the academy-wide figure for
     * every branch, so the per-branch figures would sum to far more than the
     * whole. Asserting on the underlying aggregate rather than on scraped HTML,
     * because a number's position in the markup is not what is being tested.
     */
    public function test_branch_totals_add_up_to_the_academy_total(): void
    {
        $branches = DB::table('branches')->whereNull('deleted_at')->pluck('id');

        $metrics = [
            'participants' => fn () => \App\Domain\Participant\Models\Participant::count(),
            'enrolments' => fn () => \App\Domain\Training\Models\Enrollment::count(),
            'invoices' => fn () => \App\Domain\Financial\Models\Invoice::count(),
            'payments' => fn () => \App\Domain\Financial\Models\Payment::sum('amount'),
            'attendance' => fn () => \App\Domain\Attendance\Models\AttendanceRecord::count(),
            'groups' => fn () => \App\Domain\Training\Models\TrainingGroup::count(),
        ];

        $state = app(\App\Domain\Shared\Context\BranchScopeState::class);

        foreach ($metrics as $label => $metric) {
            $state->deactivate();
            $whole = $metric();

            $summed = 0;
            foreach ($branches as $id) {
                $state->activate((int) $id);
                $summed += $metric();
            }
            $state->deactivate();

            $this->assertSame(
                (int) $whole,
                (int) $summed,
                "The per-branch {$label} figures do not add up to the academy total — "
                    ."either a branch is double-counting, or rows belong to no branch at all."
            );
        }
    }
}
