<?php

namespace Tests\Benchmarks;

use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Facades\DB;

require_once __DIR__.'/ModuleAssociationLoadTest.php';

final class ModuleAssociationIndexPlanTest extends ModuleAssociationLoadTest
{
    public function test_synthetic_volume_and_query_plans(): void
    {
        $run = (string) getenv('SGF_ISOLATED_REGRESSION_RUN');
        $this->assertMatchesRegularExpression('/^[a-f0-9]{12}$/D', $run);
        $identity = DB::selectOne("SELECT current_database() AS database,current_user AS role,inet_server_port() AS port,current_setting('cluster_name') AS cluster");
        $this->assertSame('sgf_tempo_test', $identity->database); $this->assertSame('sgf_test_runner', $identity->role);
        $this->assertSame('sgf_modular_regression_'.$run, $identity->cluster); $this->assertSame(55439, (int) $identity->port);
        $f = $this->fixture(10000, true); $actor = $f['identity'];
        $this->actingAs($actor['manager'])->withSession(['auth_version' => $actor['manager']->auth_version]);
        $token = $this->postJson('/api/context', ['tenant_id' => $actor['tenant'], 'company_id' => $actor['company'], 'unit_id' => $actor['unit'], 'module_id' => null])->assertOk()->json('context.token');
        $this->withHeader('X-SGF-Context', $token);
        $capturing = false; $queries = [];
        DB::listen(function (QueryExecuted $q) use (&$capturing, &$queries) { if ($capturing && str_contains($q->sql, 'identity_audit')) $queries[hash('sha256', $q->sql)] = ['sql' => $q->sql, 'bindings' => $q->bindings]; });
        $report = ['date' => '2026-11-02', 'identity' => $identity, 'records_per_area' => 10000, 'variants' => []];
        foreach (['batch', 'indexed_batch'] as $mode) {
            DB::beginTransaction();
            try {
                if ($mode === 'indexed_batch') {
                    DB::statement("CREATE INDEX sgf_qa_assoc_links ON identity_audit (tenant_id,company_id,(details->>'area'),(details->>'record_id'),(details->>'module_id'),id DESC) WHERE action IN ('module.record.associated','module.record.disassociated')");
                    DB::statement("CREATE INDEX sgf_qa_assoc_revision ON identity_audit (tenant_id,company_id,(details->>'area'),(details->>'record_id'),id DESC) WHERE action IN ('module.record.associated','module.record.disassociated','module.record.reviewed')");
                    DB::statement('ANALYZE identity_audit');
                }
                $queries = []; $capturing = true;
                $this->getJson('/api/module-records?area=billing_drafts&per_page=100')->assertOk();
                $this->getJson('/api/module-records?area=clients&state=pending&per_page=25')->assertOk();
                $capturing = false;
                foreach ($queries as $signature => $q) {
                    DB::beginTransaction(); DB::statement("SET LOCAL statement_timeout = '3s'");
                    try {
                        $raw = DB::select('EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON) '.$q['sql'], $q['bindings']);
                        $plan = json_decode($raw[0]->{'QUERY PLAN'}, true, 512, JSON_THROW_ON_ERROR)[0]; $nodes = [];
                        $this->nodesOnly($plan['Plan'], $nodes);
                        $report['variants'][$mode][] = ['signature' => $signature, 'shape' => $q['sql'], 'execution_ms' => $plan['Execution Time'], 'nodes' => $nodes];
                    } catch (\Illuminate\Database\QueryException $e) { $report['variants'][$mode][] = ['signature' => $signature, 'status' => 'plan_timeout', 'sqlstate' => $e->errorInfo[0] ?? $e->getCode()];
                    } finally { DB::rollBack(); }
                }
            } finally { $capturing = false; DB::rollBack(); }
        }
        $this->assertSame(0, (int) DB::selectOne("SELECT count(*) AS n FROM pg_indexes WHERE schemaname='public' AND indexname LIKE 'sgf_qa_assoc_%'")->n);
        $report['indexes_rolled_back'] = true;
        $output = getenv('SGF_ASSOCIATION_LOAD_OUTPUT');
        if (!$output || realpath(dirname($output)) !== realpath(base_path('..'))) throw new \RuntimeException('Unexpected report directory');
        file_put_contents($output, json_encode($report, JSON_PRETTY_PRINT | JSON_THROW_ON_ERROR).PHP_EOL);
    }

    private function nodesOnly(array $node, array &$out): void
    {
        $out[] = array_intersect_key($node, array_flip(['Node Type', 'Relation Name', 'Index Name', 'Actual Rows', 'Actual Loops', 'Rows Removed by Filter', 'Shared Hit Blocks', 'Shared Read Blocks', 'Temp Read Blocks', 'Temp Written Blocks']));
        foreach ($node['Plans'] ?? [] as $child) $this->nodesOnly($child, $out);
    }
}