"""Canary tests — run these first to rule out infra before debugging test failures.

Layer 1: DB reachable
Layer 2: All ORM tables exist in the test schema
Layer 3: TRUNCATE isolation works (no state bleeds between tests)
Layer 4: Session factory is wired to the test DB (not prod)
Layer 5: Unique constraints fire correctly (proving TRUNCATE reset identity)
"""
from __future__ import annotations

import pytest
import pytest_asyncio
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

from musehub.db.database import Base


def test_hello() -> None:
    print("hello world")


class TestDBReachable:
    """Layer 1 — can we talk to Postgres at all?"""

    async def test_connection(self, db_session: AsyncSession) -> None:
        result = await db_session.execute(text("SELECT 1"))
        assert result.scalar() == 1

    async def test_postgres_version(self, db_session: AsyncSession) -> None:
        result = await db_session.execute(text("SELECT version()"))
        version = result.scalar()
        assert version is not None
        assert "PostgreSQL" in version


class TestSchemaComplete:
    """Layer 2 — every ORM model has a matching table in the test DB."""

    async def test_all_orm_tables_exist(self, db_session: AsyncSession) -> None:
        result = await db_session.execute(
            text(
                "SELECT tablename FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename"
            )
        )
        live_tables = {row[0] for row in result.fetchall()}
        orm_tables = {t.name for t in Base.metadata.tables.values()}
        missing = orm_tables - live_tables
        assert not missing, (
            f"ORM models have no matching DB table — run create_all or add a migration:\n"
            f"{'\n'.join(f'  {t}' for t in sorted(missing))}"
        )

    async def test_no_orphan_db_tables(self, db_session: AsyncSession) -> None:
        """Tables in DB but not in ORM — usually a dropped model without a migration."""
        result = await db_session.execute(
            text(
                "SELECT tablename FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename"
            )
        )
        live_tables = {row[0] for row in result.fetchall()}
        orm_tables = {t.name for t in Base.metadata.tables.values()}
        # alembic_version is managed by Alembic, not our ORM — exclude it.
        orphans = live_tables - orm_tables - {"alembic_version"}
        assert not orphans, (
            f"DB has tables with no ORM model — dead schema, needs a DROP migration:\n"
            f"{'\n'.join(f'  {t}' for t in sorted(orphans))}"
        )


class TestTruncateIsolation:
    """Layer 3 — each test starts with a clean slate."""

    async def test_insert_is_visible_within_test(self, db_session: AsyncSession) -> None:
        from datetime import datetime, timezone
        from musehub.core.genesis import compute_identity_id, compute_repo_id
        from musehub.db.musehub_repo_models import MusehubRepo
        _created_at = datetime.now(tz=timezone.utc)
        _owner_id = compute_identity_id(b"canary")
        r = MusehubRepo(
            repo_id=compute_repo_id(_owner_id, "canary", "code", _created_at.isoformat()),
            name="canary", owner="canary",
            slug="canary", visibility="public", owner_user_id=_owner_id,
            created_at=_created_at, updated_at=_created_at,
        )
        db_session.add(r)
        await db_session.flush()
        result = await db_session.execute(
            text("SELECT COUNT(*) FROM musehub_repos WHERE owner = 'canary'")
        )
        assert result.scalar() == 1

    async def test_previous_test_data_is_gone(self, db_session: AsyncSession) -> None:
        """This runs after test_insert_is_visible_within_test — canary row must be gone."""
        result = await db_session.execute(
            text("SELECT COUNT(*) FROM musehub_repos WHERE owner = 'canary'")
        )
        assert result.scalar() == 0, (
            "Canary row from previous test is still present — TRUNCATE isolation broken"
        )


class TestTruncateCoverage:
    """Layer 3b — TRUNCATE SQL covers every table in Base.metadata.

    This catches the recurring "model registered after TRUNCATE SQL was
    pre-computed" bug:  conftest builds _TRUNCATE_SQL at import time from
    Base.metadata.sorted_tables.  If a DB model module is imported for the
    first time inside a test (after that point), its table is NOT in the
    TRUNCATE — so data from that test leaks into later tests.

    Fix: ensure every model module is imported in conftest BEFORE
    _TRUNCATE_SQL is computed.
    """

    def test_truncate_sql_covers_all_orm_tables(self) -> None:
        import tests.conftest as cf
        from musehub.db.database import Base

        orm_tables = {t.name for t in Base.metadata.tables.values()}
        # Parse the table names out of the pre-computed TRUNCATE statement.
        # Format: "TRUNCATE table1, table2, ... RESTART IDENTITY CASCADE"
        truncate_body = cf._TRUNCATE_SQL.split("TRUNCATE ", 1)[1]
        truncate_body = truncate_body.split(" RESTART ")[0]
        truncated = {t.strip() for t in truncate_body.split(",")}
        missing = orm_tables - truncated
        assert not missing, (
            "These ORM tables are NOT in conftest._TRUNCATE_SQL — data from "
            "tests that use them will leak into later tests.\n"
            "Fix: import the model module in conftest.py BEFORE _TRUNCATE_SQL "
            f"is computed (i.e. before line ~168):\n"
            f"{'\n'.join(f'  {t}' for t in sorted(missing))}"
        )


class TestSessionWiring:
    """Layer 4 — db_session fixture is pointing at the test DB, not prod."""

    async def test_connected_to_test_database(self, db_session: AsyncSession) -> None:
        result = await db_session.execute(text("SELECT current_database()"))
        db_name = result.scalar()
        assert "test" in db_name, (
            f"Tests are running against '{db_name}', not the test DB — "
            "check TEST_DATABASE_URL and the db_session fixture"
        )

    async def test_asyncsessionlocal_uses_test_engine(self, db_session: AsyncSession) -> None:
        """AsyncSessionLocal() (used by executors) must point at the test DB."""
        from musehub.db import database
        result = await db_session.execute(text("SELECT current_database()"))
        test_db = result.scalar()
        # The conftest swaps database._async_session_factory; verify it's active.
        async with database._async_session_factory() as s:
            r2 = await s.execute(text("SELECT current_database()"))
            executor_db = r2.scalar()
        assert executor_db == test_db, (
            f"database._async_session_factory points at '{executor_db}' "
            f"but db_session is on '{test_db}' — fixture swap broken"
        )