<?php

namespace Tests\Feature;

use App\Models\EconomicCluster;
use App\Models\InvestmentDossier;
use App\Models\Opportunity;
use App\Models\Project;
use App\Models\Sector;
use App\Models\User;
use App\Services\InvestmentCatalogue;
use App\Services\InvestmentRelations;
use App\Services\InvestmentRelationshipRepair;
use App\Services\InvestmentTerritorialValidation;
use Illuminate\Database\QueryException;
use Illuminate\Foundation\Testing\RefreshDatabase;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
use Inertia\Testing\AssertableInertia as Assert;
use Tests\TestCase;

class InvestmentRelationshipTest extends TestCase
{
    use RefreshDatabase;

    private function fixture(): array
    {
        $parent = null;
        $ids = [];
        foreach (['country', 'province', 'district', 'admin_post', 'locality', 'site'] as $type) {
            $id = (string) Str::uuid();
            DB::table('geo_locations')->insert(['id' => $id, 'code' => $id, 'name' => 'QA '.$type, 'location_type' => $type, 'parent_id' => $parent,
                'data_classification' => 'public', 'source_system' => 'cartografia-workbook', 'external_id' => '964a187ad32ea553b9e112cd159caa90b900050c42b7329594a75bcd798a96ff:'.$id]);
            $ids[$type] = $id;
            $parent = $id;
        }
        $project = Project::factory()->create(['code' => 'A99', 'name' => 'QA Anchor', 'status' => 'draft', 'data_classification' => 'internal']);
        DB::table('geo_locations')->where('id', $ids['site'])->update(['source_system' => 'QA documented site', 'external_id' => null]);
        $dossier = InvestmentDossier::create(['project_id' => $project->id, 'title' => 'QA Anchor dossier', 'source' => 'QA synthetic dossier source',
            'content' => ['location' => 'QA province, district and site', 'description' => 'Unique documented component needle'], 'publication_status' => 'published', 'published_at' => now()->subMinute(), 'approval_basis' => 'document_owner', 'approval_evidence' => 'Synthetic QA fixture disclosure only']);
        $cluster = EconomicCluster::create(['code' => 'QA-CLU', 'name' => 'QA Cluster', 'sector' => 'QA', 'status' => 'planned']);
        $clusterDossier = InvestmentDossier::create(['cluster_id' => $cluster->id, 'title' => 'QA Cluster dossier', 'source' => 'QA synthetic cluster source',
            'content' => ['anchors' => 'A99; QA documented proposal'], 'publication_status' => 'published', 'published_at' => now()->subMinute(), 'approval_basis' => 'document_owner', 'approval_evidence' => 'Synthetic QA fixture disclosure only']);
        DB::table('cluster_projects')->insert(['id' => (string) Str::uuid(), 'cluster_id' => $cluster->id, 'project_id' => $project->id, 'role' => 'anchor', 'source_system' => 'upgrade-v4']);
        DB::table('project_locations')->insert(['id' => (string) Str::uuid(), 'project_id' => $project->id, 'geo_location_id' => $ids['site'], 'role' => 'primary', 'data_classification' => 'public']);
        DB::table('geo_geometries')->insert(['id' => (string) Str::uuid(), 'geo_location_id' => $ids['site'], 'geometry_type' => 'point', 'data_classification' => 'public',
            'geom' => DB::raw('ST_SetSRID(ST_MakePoint(32.5,-25.9),4326)'), 'source_system' => 'QA documented coordinates fixture']);
        $sector = Sector::create(['name' => 'QA sector', 'slug' => 'qa-sector', 'is_active' => true]);
        $opportunity = Opportunity::create(['sector_id' => $sector->id, 'reference' => 'QA-REL', 'title' => 'QA opportunity', 'slug' => 'qa-rel', 'status' => 'published', 'published_at' => now()->subMinute()]);
        DB::table('opportunity_projects')->insert(['opportunity_id' => $opportunity->id, 'project_id' => $project->id]);

        return compact('ids', 'project', 'dossier', 'cluster', 'clusterDossier', 'opportunity', 'sector');
    }

    public function test_project_location_and_dossier_navigation_are_bidirectional_without_changing_project_status(): void
    {
        $f = $this->fixture();
        $this->get('/carteira/'.$f['dossier']->id)->assertInertia(fn (Assert $p) => $p->has('geography.locations', 1)->has('geography.locations.0.path', 4)
            ->where('geography.locations.0.role', 'primary')->where('geography.locations.0.coordinates.0.geometry.coordinates.0', 32.5)->has('opportunities', 1)->has('geography.clusters', 1));
        $this->get('/oportunidades/qa-rel?from_dossier='.$f['dossier']->id)->assertInertia(fn (Assert $p) => $p->where('dossierOrigin.id', $f['dossier']->id)
            ->where('context.projects.0.dossiers.0.id', $f['dossier']->id)->has('context.projects.0.geography.locations', 1));
        $this->get('/oportunidades/qa-rel?from_dossier='.$f['clusterDossier']->id)->assertNotFound();
        $this->assertSame('draft', $f['project']->fresh()->status);
    }

    public function test_search_and_all_territorial_levels_follow_explicit_project_relations(): void
    {
        $f = $this->fixture();
        $filters = [];
        foreach (['province', 'district', 'admin_post', 'locality'] as $type) {
            $filters[$type.'_id'] = $f['ids'][$type];
            $this->assertSame([$f['opportunity']->id], app(InvestmentCatalogue::class)->query($filters)->pluck('id')->all());
            $this->get('/carteira?'.http_build_query($filters))->assertInertia(fn (Assert $p) => $p->has('records.data', 2));
        }
        $this->assertSame([$f['opportunity']->id], app(InvestmentCatalogue::class)->query(['q' => 'component needle'])->pluck('id')->all());
        $this->assertSame([$f['opportunity']->id], app(InvestmentCatalogue::class)->query(['geo_location_id' => $f['ids']['province']])->pluck('id')->all());
        $map = app(InvestmentCatalogue::class)->map($filters);
        $this->assertSame(1, $map['georeferenced_count']);
        $this->assertSame('linked_project_site', $map['features'][0]['properties']['location_basis']);
    }

    public function test_cluster_member_locations_do_not_create_opportunity_project_relations_or_a_cluster_point(): void
    {
        $f = $this->fixture();
        DB::table('opportunity_projects')->delete();
        DB::table('opportunity_clusters')->insert(['opportunity_id' => $f['opportunity']->id, 'cluster_id' => $f['cluster']->id]);
        $this->get('/carteira/'.$f['clusterDossier']->id)->assertInertia(fn (Assert $p) => $p->has('geography.locations', 0)
            ->has('geography.members', 1)->where('geography.members.0.validation_status', 'pending')->has('geography.members.0.locations', 1)->has('opportunities', 1));
        $this->get('/carteira/'.$f['dossier']->id.'?from='.$f['clusterDossier']->id)->assertOk();
        $this->assertSame([$f['opportunity']->id], app(InvestmentCatalogue::class)->query(['province_id' => $f['ids']['province']])->pluck('id')->all());
        $this->assertSame([], app(InvestmentCatalogue::class)->context($f['opportunity']->id)['projects']->all());
        $this->assertSame(0, app(InvestmentCatalogue::class)->map([])['georeferenced_count']);
        $this->assertDatabaseCount('opportunity_projects', 0);
    }

    public function test_private_revoked_and_deleted_relationships_are_not_disclosed(): void
    {
        $f = $this->fixture();
        DB::table('project_locations')->update(['data_classification' => 'confidential']);
        DB::table('cluster_projects')->update(['validation_status' => 'revoked']);
        $this->assertSame([], app(InvestmentRelations::class)->projectLocations($f['project']->id));
        $this->assertSame(0, app(InvestmentRelations::class)->memberships()->count());
        $this->assertSame(0, app(InvestmentCatalogue::class)->map([])['georeferenced_count']);
        $f['dossier']->update(['publication_status' => 'draft']);
        $this->get('/carteira/'.$f['dossier']->id)->assertNotFound();
    }

    public function test_multiple_documented_sites_are_shown_separately_without_choosing_one(): void
    {
        $f = $this->fixture();
        $id = (string) Str::uuid();
        DB::table('geo_locations')->insert(['id' => $id, 'code' => $id, 'name' => 'QA second site', 'location_type' => 'site', 'parent_id' => $f['ids']['locality'], 'data_classification' => 'public']);
        DB::table('project_locations')->insert(['id' => (string) Str::uuid(), 'project_id' => $f['project']->id, 'geo_location_id' => $id, 'role' => 'secondary', 'data_classification' => 'public']);
        DB::table('geo_geometries')->insert(['id' => (string) Str::uuid(), 'geo_location_id' => $id, 'geometry_type' => 'point', 'data_classification' => 'public', 'geom' => DB::raw('ST_SetSRID(ST_MakePoint(32.6,-25.8),4326)')]);
        $map = app(InvestmentCatalogue::class)->map([]);
        $this->assertSame(1, $map['georeferenced_count']);
        $this->assertCount(0, $map['unlocated']);
        $this->assertCount(2, $map['features']);
        $this->assertSame(2, $map['marker_count']);
        $this->assertCount(2, app(InvestmentRelations::class)->projectLocations($f['project']->id));
    }

    public function test_direct_context_impact_and_missing_geography_remain_distinct(): void
    {
        $f = $this->fixture();
        $catalogue = app(InvestmentCatalogue::class);
        $this->assertEmpty($catalogue->context($f['opportunity']->id)['opportunity_locations']);
        $this->assertSame([$f['opportunity']->id], $catalogue->query(['territory_basis' => 'context'])->pluck('id')->all());
        $this->assertSame([], $catalogue->query(['territory_basis' => 'direct'])->pluck('id')->all());
        DB::table('opportunity_locations')->insert(['opportunity_id' => $f['opportunity']->id, 'geo_location_id' => $f['ids']['district']]);
        $location = $catalogue->context($f['opportunity']->id)['opportunity_locations'][0];
        $this->assertSame('opportunity', $location['role']);
        $this->assertSame([$f['opportunity']->id], $catalogue->query(['q' => 'QA district'])->pluck('id')->all());
        $this->assertNull($location['source_reference']);
        $this->assertEmpty($location['coordinates']);
        $this->assertSame([], $catalogue->query(['territory_basis' => 'context'])->pluck('id')->all());
        $this->assertSame([$f['opportunity']->id], $catalogue->query(['territory_basis' => 'direct', 'geo_location_id' => $f['ids']['district']])->pluck('id')->all());
        $this->assertSame([], $catalogue->query(['territory_basis' => 'direct', 'geo_location_id' => $f['ids']['site']])->pluck('id')->all());
        DB::table('project_locations')->update(['role' => 'impact']);
        $this->assertSame('impact', $catalogue->context($f['opportunity']->id)['projects'][0]->geography['locations'][0]['role']);
        $this->assertSame(0, $catalogue->map([])['georeferenced_count']);
        DB::table('opportunity_locations')->delete();
        DB::table('opportunity_projects')->delete();
        $this->assertEmpty($catalogue->context($f['opportunity']->id)['opportunity_locations']);
        $this->assertSame(1, $catalogue->query([])->count());
        $this->assertCount(1, $catalogue->map([])['unlocated']);
    }

    public function test_invalid_coordinates_are_rejected_and_polygon_provenance_is_preserved(): void
    {
        $f = $this->fixture();
        DB::table('opportunity_locations')->insert(['opportunity_id' => $f['opportunity']->id, 'geo_location_id' => $f['ids']['site']]);
        DB::table('geo_geometries')->insert(['id' => (string) Str::uuid(), 'geo_location_id' => $f['ids']['site'], 'geometry_type' => 'polygon', 'data_classification' => 'public',
            'geom' => DB::raw("ST_GeomFromText('POLYGON((32 -26,33 -26,33 -25,32 -25,32 -26))',4326)"), 'source_system' => 'QA boundary', 'properties' => json_encode(['source_date' => '2026-10-08'])]);
        $context = app(InvestmentCatalogue::class)->context($f['opportunity']->id);
        $this->assertSame('2026-10-08', $context['opportunity_locations'][0]['boundaries'][0]['source_date']);
        $this->assertSame('opportunity_site', app(InvestmentCatalogue::class)->map([])['features'][0]['properties']['location_basis']);
        $this->assertCount(1, app(InvestmentCatalogue::class)->map([])['areas']);
        try {
            DB::transaction(fn () => DB::table('geo_geometries')->where('geo_location_id', $f['ids']['site'])->where('geometry_type', 'point')->update(['geom' => DB::raw('ST_SetSRID(ST_MakePoint(32.7,95),4326)')]));
            $this->fail('Invalid coordinates must be rejected by the existing database guard.');
        } catch (QueryException $error) {
            $this->assertSame('23514', $error->getCode());
        }
        $this->assertSame(1, app(InvestmentCatalogue::class)->map([])['georeferenced_count']);
        $this->assertCount(1, app(InvestmentCatalogue::class)->context($f['opportunity']->id)['opportunity_locations'][0]['coordinates']);
    }

    public function test_repair_requires_authenticated_management_is_transactional_and_replays_without_duplicates(): void
    {
        $f = $this->fixture();
        $directory = storage_path('framework/testing/relationship-repair-'.Str::uuid());
        mkdir($directory, 0700, true);
        config(['investment.repair_directory' => $directory]);
        $dump = $directory.'/synthetic-qa-backup.dump';
        file_put_contents($dump, 'synthetic isolated QA proof fixture');
        $target = ['database' => DB::connection()->getDatabaseName(), 'port' => 55441];
        file_put_contents($directory.'/backup-proof.json', json_encode(['restore_verified' => true, 'dump_path' => $dump, 'sha256' => hash_file('sha256', $dump), 'target' => $target]));
        $row = ['project_id' => $f['project']->id, 'code' => 'A99', 'dossier_id' => $f['dossier']->id, 'dossier_version' => 1, 'geo_location_id' => $f['ids']['province'],
            'role' => 'secondary', 'source_reference' => $f['dossier']->source, 'source_quote' => $f['dossier']->content['location'], 'reason' => 'QA documented province',
            'expected_project_version' => $f['project']->fresh()->version_no, 'data_classification' => 'public'];
        file_put_contents($directory.'/preview.json', json_encode(['target' => $target, 'locations' => [$row], 'memberships' => [], 'prepared_opportunities' => [], 'pending' => []]));
        $hash = hash_file('sha256', $directory.'/preview.json');
        $this->get('/management/relationship-repair')->assertRedirect('/login');
        $manager = User::factory()->create();
        $this->actingAs($manager)->get('/management/relationship-repair')->assertForbidden();
        config(['portal.managers' => [$manager->email]]);
        $this->get('/management/relationship-repair')->assertOk();
        $this->postJson('/management/relationship-repair', ['hash' => str_repeat('0', 64)])->assertConflict();
        $invalid = [...$row, 'expected_project_version' => $row['expected_project_version'] + 7, 'role' => 'impact'];
        file_put_contents($directory.'/preview.json', json_encode(['target' => $target, 'locations' => [$row, $invalid], 'memberships' => []]));
        $this->postJson('/management/relationship-repair', ['hash' => hash_file('sha256', $directory.'/preview.json')])->assertConflict();
        $this->assertDatabaseCount('project_locations', 1);
        file_put_contents($directory.'/preview.json', json_encode(['target' => $target, 'locations' => [$row], 'memberships' => [], 'prepared_opportunities' => [], 'pending' => []]));
        $hash = hash_file('sha256', $directory.'/preview.json');
        $this->postJson('/management/relationship-repair', ['hash' => $hash])->assertOk()->assertJsonPath('locations_created', 1);
        $this->postJson('/management/relationship-repair', ['hash' => $hash])->assertOk()->assertJsonPath('replayed', true);
        $this->assertDatabaseCount('project_locations', 2);
        $this->assertSame('draft', $f['project']->fresh()->status);
        $this->assertSame('published', $f['dossier']->fresh()->publication_status);
        $changed = [...$row, 'role' => 'impact'];
        file_put_contents($directory.'/preview.json', json_encode(['target' => $target, 'locations' => [$changed], 'memberships' => []]));
        $this->postJson('/management/relationship-repair', ['hash' => hash_file('sha256', $directory.'/preview.json')])->assertConflict();
        $this->assertDatabaseCount('project_locations', 2);
        @unlink($directory.'/preview.json');
        @unlink($directory.'/backup-proof.json');
        @unlink($dump);
        @rmdir($directory);
    }

    private function repairPlan(array $rows): array
    {
        $directory = storage_path('framework/testing/territorial-repair-'.Str::uuid());
        mkdir($directory, 0700, true);
        config(['investment.repair_directory' => $directory]);
        $dump = $directory.'/qa.dump';
        file_put_contents($dump, 'Synthetic QA fixture; not an active backup');
        $target = ['database' => DB::connection()->getDatabaseName(), 'port' => 55441];
        file_put_contents($directory.'/backup-proof.json', json_encode(['restore_verified' => true, 'dump_path' => $dump, 'sha256' => hash_file('sha256', $dump), 'target' => $target]));
        file_put_contents($directory.'/preview.json', json_encode(['target' => $target, 'locations' => $rows, 'memberships' => [], 'pending' => [], 'prepared_opportunities' => []]));
        $user = User::factory()->create();
        config(['portal.managers' => [$user->email]]);
        $this->actingAs($user);

        return app(InvestmentRelationshipRepair::class)->preview();
    }

    private function territorialRow(array $f, string $level = 'province', string $role = 'secondary'): array
    {
        return ['project_id' => $f['project']->id, 'code' => 'A99', 'dossier_id' => $f['dossier']->id, 'dossier_version' => 1,
            'geo_location_id' => $f['ids'][$level], 'role' => $role, 'source_reference' => $f['dossier']->source,
            'source_quote' => $f['dossier']->content['location'], 'reason' => 'Explicit synthetic QA document',
            'expected_project_version' => $f['project']->fresh()->version_no, 'data_classification' => 'public'];
    }

    public function test_individual_territories_without_coordinates_apply_sequentially_and_audit_actor_date_and_source(): void
    {
        $f = $this->fixture();
        $preview = $this->repairPlan([$this->territorialRow($f), $this->territorialRow($f, 'district', 'impact')]);
        $this->assertSame('grounded', $preview['territorial'][0]['status']);
        $this->assertCount(0, $preview['territorial'][0]['coordinates']);
        $this->assertCount(1, $preview['territorial'][0]['current']);
        $this->get('/management/relationship-repair')->assertInertia(fn (Assert $p) => $p->where('territorial.0.status', 'grounded')->where('territorial.1.proposed.role', 'impact'));
        foreach ([0, 1] as $index) {
            $fresh = app(InvestmentRelationshipRepair::class)->preview();
            $request = ['hash' => $fresh['hash'], 'row_id' => $fresh['territorial'][$index]['row_id'], 'validation_hash' => $fresh['territorial'][$index]['validation_hash']];
            $this->postJson('/management/relationship-repair', $request)->assertOk()->assertJsonPath('locations_created', 1);
            $this->postJson('/management/relationship-repair', $request)->assertOk()->assertJsonPath('replayed', true);
        }
        $this->assertDatabaseCount('project_locations', 3);
        $history = app(InvestmentRelationshipRepair::class)->preview()['history'];
        $this->assertCount(2, $history);
        $this->assertSame(auth()->id(), $history[0]['actor_id']);
        $this->assertNotEmpty($history[0]['date']);
        $this->assertSame($f['dossier']->source, $history[0]['data']['sources'][0]['source_reference']);
        $this->assertSame('draft', $f['project']->fresh()->status);
    }

    public function test_pending_and_contradictory_rows_are_retained_while_grounded_rows_apply(): void
    {
        $f = $this->fixture();
        $good = $this->territorialRow($f);
        $pending = [...$this->territorialRow($f, 'district'), 'source_quote' => ''];
        $bad = [...$this->territorialRow($f, 'locality', 'impact'), 'province_id' => (string) Str::uuid()];
        $preview = $this->repairPlan([$good, $pending, $bad]);
        $this->assertSame(['grounded', 'pending', 'contradictory'], array_column($preview['territorial'], 'status'));
        foreach ([1, 2] as $index) {
            $this->postJson('/management/relationship-repair', ['hash' => $preview['hash'], 'row_id' => $preview['territorial'][$index]['row_id'], 'validation_hash' => $preview['territorial'][$index]['validation_hash']])->assertConflict();
        }
        $this->postJson('/management/relationship-repair', ['hash' => $preview['hash']])->assertOk()->assertJsonPath('locations_created', 1);
        $this->assertDatabaseCount('project_locations', 2);
        $this->assertDatabaseMissing('project_locations', ['project_id' => $f['project']->id, 'geo_location_id' => $f['ids']['locality']]);
        $this->assertSame('contradictory', app(InvestmentRelationshipRepair::class)->preview()['territorial'][2]['status']);
    }

    public function test_coordinate_inclusion_and_broken_hierarchy_are_checked_without_inventing_boundaries(): void
    {
        $f = $this->fixture();
        $row = $this->territorialRow($f, 'site');
        $validator = app(InvestmentTerritorialValidation::class);
        $without = $validator->validate($row);
        $this->assertSame('grounded', $without['status']);
        $this->assertSame([], $without['coordinate_checks']);
        DB::table('geo_geometries')->insert(['id' => (string) Str::uuid(), 'geo_location_id' => $f['ids']['province'], 'geometry_type' => 'polygon', 'data_classification' => 'public',
            'geom' => DB::raw('ST_MakeEnvelope(32,-26,33,-25,4326)'), 'source_system' => 'Synthetic QA boundary']);
        $inside = $validator->validate($row);
        $this->assertSame('grounded', $inside['status']);
        $this->assertTrue($inside['coordinate_checks'][0]['compatible']);
        DB::table('geo_geometries')->where('geo_location_id', $f['ids']['site'])->update(['geom' => DB::raw('ST_SetSRID(ST_MakePoint(35,-25.9),4326)')]);
        $outside = $validator->validate($row);
        $this->assertSame('contradictory', $outside['status']);
        $this->assertFalse($outside['coordinate_checks'][0]['compatible']);
        $this->assertSame('contradictory', $validator->validate([...$row, 'coordinates' => [181, -25]])['status']);
        DB::table('geo_locations')->where('id', $f['ids']['admin_post'])->update(['deleted_at' => now()]);
        $broken = $validator->validate($this->territorialRow($f, 'locality'));
        $this->assertSame('contradictory', $broken['status']);
        $this->assertStringContainsString('antepassado indisponível', implode(' ', $broken['issues']));
    }

    public function test_individual_application_rejects_changed_territorial_preview_and_unknown_row(): void
    {
        $f = $this->fixture();
        $preview = $this->repairPlan([$this->territorialRow($f)]);
        $this->postJson('/management/relationship-repair', ['hash' => $preview['hash'], 'row_id' => str_repeat('a', 64), 'validation_hash' => $preview['territorial'][0]['validation_hash']])->assertUnprocessable();
        DB::table('geo_locations')->where('id', $f['ids']['province'])->update(['name' => 'Changed QA territory']);
        $this->postJson('/management/relationship-repair', ['hash' => $preview['hash'], 'row_id' => $preview['territorial'][0]['row_id'], 'validation_hash' => $preview['territorial'][0]['validation_hash']])->assertConflict();
        $this->assertDatabaseCount('project_locations', 1);
    }
}