"""P6: orphan-team cleanup SQL semantics.

These tests exercise the exact SQL used by `cleanup_orphan_teams` against a
live Postgres branch. Marked `integration` so they're skipped without
`TEST_DATABASE_URL`. The query joins `team LEFT JOIN restaurant` and filters
on `createdAt < NOW() - INTERVAL '24 hours'`; we seed both shapes and assert
the right teams are picked up (or not).
"""

from __future__ import annotations

import os
import uuid
from collections.abc import AsyncIterator
from datetime import UTC, datetime, timedelta
from urllib.parse import parse_qs, urlencode, urlparse, urlunparse

import pytest
import pytest_asyncio
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncConnection, create_async_engine

ORPHAN_TEAM_SELECT = text(
    """
    SELECT t.id FROM team t
    LEFT JOIN restaurant r ON r.team_id = t.id
    WHERE r.id IS NULL
      AND t."createdAt" < NOW() - INTERVAL '24 hours'
    ORDER BY t.id
    """
)

TEST_DATABASE_URL = os.getenv("TEST_DATABASE_URL", "")

pytestmark = [
    pytest.mark.integration,
    pytest.mark.skipif(
        not TEST_DATABASE_URL,
        reason="TEST_DATABASE_URL not set; integration tests skipped",
    ),
]


def _build_async_db_url(raw: str) -> str:
    url = raw.replace("postgresql://", "postgresql+asyncpg://", 1)
    parsed = urlparse(url)
    query = parse_qs(parsed.query)
    query.pop("sslmode", None)
    query.pop("channel_binding", None)
    cleaned = parsed._replace(query=urlencode({k: v[0] for k, v in query.items()}))
    return urlunparse(cleaned)


@pytest_asyncio.fixture
async def auth_db() -> AsyncIterator[AsyncConnection]:
    engine = create_async_engine(_build_async_db_url(TEST_DATABASE_URL))
    async with engine.connect() as conn:
        # Temp shadow tables that match BA's column shape for the query under
        # test. We intentionally don't use the real BA tables so the test is
        # hermetic — it doesn't depend on running BA migrate first.
        await conn.execute(
            text(
                "CREATE TEMP TABLE team ("
                "id text PRIMARY KEY, "
                "name text NOT NULL, "
                '"organizationId" text NOT NULL, '
                '"createdAt" timestamptz NOT NULL DEFAULT NOW(), '
                '"updatedAt" timestamptz'
                ")"
            )
        )
        await conn.execute(
            text(
                "CREATE TEMP TABLE restaurant ("
                "id text PRIMARY KEY, "
                "name text NOT NULL, "
                "slug text NOT NULL, "
                "team_id text NOT NULL REFERENCES team(id) ON DELETE RESTRICT"
                ")"
            )
        )
        try:
            yield conn
        finally:
            await conn.rollback()
    await engine.dispose()


async def _insert_team(
    conn: AsyncConnection, *, name: str, age: timedelta, team_id: str | None = None
) -> str:
    tid = team_id or f"team-{uuid.uuid4().hex[:8]}"
    created_at = datetime.now(UTC) - age
    await conn.execute(
        text(
            'INSERT INTO team (id, name, "organizationId", "createdAt") '
            "VALUES (:id, :name, :org, :created)"
        ),
        {"id": tid, "name": name, "org": "org-1", "created": created_at},
    )
    return tid


async def _insert_restaurant_for_team(conn: AsyncConnection, team_id: str) -> None:
    await conn.execute(
        text("INSERT INTO restaurant (id, name, slug, team_id) VALUES (:id, :n, :s, :t)"),
        {
            "id": f"r-{uuid.uuid4().hex[:8]}",
            "n": f"R {team_id}",
            "s": f"r-{team_id}",
            "t": team_id,
        },
    )


@pytest.mark.asyncio
async def test_returns_team_older_than_24h_without_restaurant(
    auth_db: AsyncConnection,
) -> None:
    orphan = await _insert_team(auth_db, name="ancient", age=timedelta(hours=48))
    found = (await auth_db.execute(ORPHAN_TEAM_SELECT)).fetchall()
    assert [row[0] for row in found] == [orphan]


@pytest.mark.asyncio
async def test_excludes_team_younger_than_24h(auth_db: AsyncConnection) -> None:
    await _insert_team(auth_db, name="fresh", age=timedelta(hours=2))
    found = (await auth_db.execute(ORPHAN_TEAM_SELECT)).fetchall()
    assert found == []


@pytest.mark.asyncio
async def test_excludes_team_with_restaurant(auth_db: AsyncConnection) -> None:
    bound = await _insert_team(auth_db, name="bound", age=timedelta(days=30))
    await _insert_restaurant_for_team(auth_db, bound)
    found = (await auth_db.execute(ORPHAN_TEAM_SELECT)).fetchall()
    assert found == []


@pytest.mark.asyncio
async def test_picks_only_orphans_in_mixed_population(
    auth_db: AsyncConnection,
) -> None:
    orphan_old = await _insert_team(auth_db, name="orphan-old", age=timedelta(days=10))
    await _insert_team(auth_db, name="orphan-new", age=timedelta(minutes=10))
    bound = await _insert_team(auth_db, name="bound", age=timedelta(days=5))
    await _insert_restaurant_for_team(auth_db, bound)

    found = (await auth_db.execute(ORPHAN_TEAM_SELECT)).fetchall()
    assert [row[0] for row in found] == [orphan_old]