"""Opt-in PostGIS checks using transaction-local temporary model tables only."""

import asyncio
import os

import pytest
from psycopg import AsyncConnection, sql
from psycopg.rows import dict_row

from app.infra.db.postgresql import spatial_queries


def test_nearby_model_queries_with_real_postgis(monkeypatch):
    dsn = os.environ.get("MCP_SPATIAL_TEST_DSN")
    if not dsn:
        pytest.skip("MCP_SPATIAL_TEST_DSN must point to a PostGIS database; only TEMP tables are written")
    monkeypatch.setitem(spatial_queries._MODEL_RELATIONS, "pipe", sql.Identifier("pg_temp", "mcp_model_pipes"))
    monkeypatch.setitem(spatial_queries._MODEL_RELATIONS, "junction", sql.Identifier("pg_temp", "mcp_model_junctions"))

    async def check():
        conn = await AsyncConnection.connect(dsn, row_factory=dict_row)
        async with conn, conn.transaction(force_rollback=True):
            await conn.execute("CREATE TEMP TABLE mcp_model_pipes (id text PRIMARY KEY, diameter float, geom geometry(LineString,3857)) ON COMMIT DROP")
            await conn.execute("CREATE TEMP TABLE mcp_model_junctions (id text PRIMARY KEY, geom geometry(Point,3857)) ON COMMIT DROP")
            await conn.execute("CREATE INDEX ON mcp_model_pipes USING gist (geom)")
            # The crossing pipe has distant endpoints, while its middle is at the center.
            for pipe_id, wkt in [
                ("crossing", "LINESTRING(121.49 30.9,121.51 30.9)"),
                ("near-a", "LINESTRING(121.4999 30.9005,121.5001 30.9005)"),
                ("near-b", "LINESTRING(121.4999 30.9005,121.5001 30.9005)"),
                ("outside", "LINESTRING(121.4999 30.91,121.5001 30.91)"),
            ]:
                await conn.execute("INSERT INTO mcp_model_pipes VALUES (%s,300,ST_Transform(ST_GeomFromText(%s,4326),3857))", (pipe_id, wkt))
            await conn.execute("INSERT INTO mcp_model_pipes VALUES ('missing',300,NULL),('empty',300,ST_GeomFromText('LINESTRING EMPTY',3857))")
            await conn.execute("INSERT INTO mcp_model_junctions VALUES ('junction',ST_Transform(ST_SetSRID(ST_MakePoint(121.5,30.9002),4326),3857))")

            async def nearby(kind="pipe", **overrides):
                values = dict(center=(121.5, 30.9), radius_m=100, limit=10, offset=0)
                return await spatial_queries.nearby_elements(conn, kind, **(values | overrides))

            rows = await nearby()
            assert [row["element_id"] for row in rows] == ["crossing", "near-a", "near-b"]
            assert rows[0]["distance_m"] < 1
            assert 54 < rows[1]["distance_m"] < 57
            assert rows[1]["properties"]["diameter"] == 300 and "geom" not in rows[1]["properties"]
            # 55 m geodesic is ~65 m in Web Mercator here: a 60 m radius must include it.
            assert len(await nearby(radius_m=60)) == 3
            assert len(await nearby(radius_m=50)) == 1
            assert [row["element_id"] for row in await nearby(limit=1, offset=2)] == ["near-b"]
            assert await nearby(center=(0, 0)) == []
            assert [row["element_id"] for row in await nearby("junction")] == ["junction"]
            assert await nearby(center=(0, 90)) == []

            # A point west of the dateline must find a model east of it.
            await conn.execute("INSERT INTO mcp_model_pipes VALUES ('dateline',300,ST_Transform(ST_GeomFromText('LINESTRING(179.9999 30,179.9999 30.0001)',4326),3857))")
            assert [row["element_id"] for row in await nearby(center=(-179.9999, 30), radius_m=100)] == ["dateline"]

    asyncio.run(check())