<?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
{
    private function buildQuery(string $branchId, ?int $academyId): array
    {
        $component = new FinancialOverview();
        $component->branch_id = $branchId;

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

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

    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],
            'branch only' => ['3', null],
            'academy only' => ['', 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);

        $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']);
    }

    public function test_the_built_query_executes_with_its_bindings(): void
    {
        $this->createMinimalSchema();

        foreach ([['', null], ['3', 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->string('name_ar')->nullable();
        });
    }
}
