<?php

namespace Tests\Feature;

use App\Livewire\Financial\FinancialOverview;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use ReflectionMethod;
use Tests\TestCase;

/**
 * The financial overview's top-programs query is raw SQL against Postgres.
 *
 * Postgres fixes each prepared-statement parameter's type during parse
 * analysis, so a placeholder whose only typing context is `:x IS NULL` is
 * rejected with SQLSTATE 42P08 — the statement never reaches the comparison
 * that would have typed it. The optional branch/academy filters must therefore
 * be appended only when they apply, never bound as a NULL sentinel.
 *
 * The suite runs on SQLite, which types placeholders at bind time and would
 * happily execute the broken form; the guard below is a source scan for that
 * reason, and the execution tests prove the SQL and its bindings agree.
 */
class FinancialTopProgramsQueryTest extends TestCase
{
    /**
     * The branch is an argument now rather than a component property: the
     * overview used to hold it in a #[Url] string the browser could set, and it
     * comes from BranchContext instead.
     */
    private function buildQuery(?int $branchId, ?int $academyId): array
    {
        $component = new FinancialOverview();

        $method = new ReflectionMethod($component, 'buildTopProgramsQuery');
        $method->setAccessible(true);

        return $method->invoke($component, '2026-01-01', '2026-12-31', $academyId, $branchId);
    }

    public function test_no_raw_sql_binds_a_placeholder_as_a_null_sentinel(): void
    {
        $offenders = [];

        $files = new \RecursiveIteratorIterator(new \RecursiveDirectoryIterator(app_path()));

        foreach ($files as $file) {
            if ($file->getExtension() !== 'php') {
                continue;
            }

            foreach (file($file->getPathname()) as $i => $line) {
                if (preg_match('/[(\s](:\w+|\?)\s+IS\s+NULL\s+OR/i', $line)) {
                    $offenders[] = $file->getPathname() . ':' . ($i + 1) . ' ' . trim($line);
                }
            }
        }

        $this->assertSame([], $offenders, implode("\n", array_merge(
            ['Bound placeholders used as NULL sentinels — Postgres cannot type these (42P08):'],
            $offenders
        )));
    }

    public function test_every_placeholder_in_the_query_has_a_binding_and_every_binding_is_used(): void
    {
        $cases = [
            'no filters' => [null, null],
            'branch only' => [3, null],
            'academy only' => [null, 7],
            'both filters' => [3, 7],
        ];

        foreach ($cases as $label => [$branchId, $academyId]) {
            [$sql, $bindings] = $this->buildQuery($branchId, $academyId);

            preg_match_all('/:(\w+)/', $sql, $matches);
            $placeholders = array_values(array_unique($matches[1]));
            $bound = array_keys($bindings);

            sort($placeholders);
            sort($bound);

            $this->assertSame($placeholders, $bound, "[{$label}] placeholders and bindings disagree; PDO would reject the statement.");
        }
    }

    public function test_the_branch_filter_is_omitted_rather_than_bound_as_null(): void
    {
        [$sql, $bindings] = $this->buildQuery(null, null);

        $this->assertStringNotContainsString('branch_id', $sql);
        $this->assertStringNotContainsString('academy_id', $sql);
        $this->assertArrayNotHasKey('branch_id', $bindings);
        $this->assertArrayNotHasKey('academy_id', $bindings);

        [$sql, $bindings] = $this->buildQuery(3, 7);

        $this->assertStringContainsString('AND p.branch_id = :branch_id', $sql);
        $this->assertStringContainsString('AND p.academy_id = :academy_id', $sql);
        $this->assertSame(3, $bindings['branch_id']);
        $this->assertSame(7, $bindings['academy_id']);

        // training_programs is a SHARED table: branch_id NULL means every
        // branch runs the programme, so a strict equality here would drop the
        // academy-wide programmes and their revenue out of the chart.
        $this->assertStringContainsString('AND (tp.branch_id = :branch_id_tp OR tp.branch_id IS NULL)', $sql);
        $this->assertSame(3, $bindings['branch_id_tp']);
    }

    public function test_the_built_query_executes_with_its_bindings(): void
    {
        if (config('database.default') !== 'sqlite') {
            $this->markTestSkipped('Builds its own schema; runs on the in-memory SQLite connection.');
        }

        $this->createMinimalSchema();

        foreach ([[null, null], [3, null], [null, 7], [3, 7]] as [$branchId, $academyId]) {
            [$sql, $bindings] = $this->buildQuery($branchId, $academyId);

            $this->assertIsArray(DB::select($sql, $bindings));
        }
    }

    private function createMinimalSchema(): void
    {
        Schema::create('invoice_items', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('invoice_id');
            $table->string('itemable_type')->nullable();
            $table->bigInteger('total_amount');
        });

        Schema::create('invoices', function (Blueprint $table) {
            $table->id();
            $table->string('billable_type')->nullable();
            $table->unsignedBigInteger('billable_id')->nullable();
            $table->bigInteger('subtotal_amount')->default(0);
        });

        Schema::create('enrollments', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('invoice_id')->nullable();
            $table->unsignedBigInteger('participant_id')->nullable();
            $table->unsignedBigInteger('training_program_id')->nullable();
        });

        Schema::create('payments', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('invoice_id')->nullable();
            $table->unsignedBigInteger('branch_id')->nullable();
            $table->unsignedBigInteger('academy_id')->nullable();
            $table->bigInteger('amount')->default(0);
            $table->string('status')->default('confirmed');
            $table->string('direction')->default('inbound');
            $table->date('payment_date')->nullable();
        });

        Schema::create('training_programs', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('branch_id')->nullable();
            $table->string('name_ar')->nullable();
        });
    }
}
