from __future__ import annotations

"""Tests for musehub.db.schema_check.

Covers:
- assert_schema_matches_orm: passes for a correct schema
- assert_schema_matches_orm: raises RuntimeError for a missing table
- assert_schema_matches_orm: raises RuntimeError for a missing column
- assert_schema_matches_orm: raises RuntimeError for a nullable mismatch
- assert_schema_matches_orm: skips silently for SQLite engines
- assert_no_orm_column_aliases: passes when all keys match column names
- assert_no_orm_column_aliases: raises AssertionError when an alias exists
- assert_no_orm_column_aliases: musehub Base has zero aliases (regression)
"""

from unittest.mock import AsyncMock, MagicMock

import pytest
from sqlalchemy import Integer, String, Table
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

from musehub.types.json_types import JSONObject

type _ColumnsByTable = dict[str, list[JSONObject]]
from musehub.db.schema_check import (
    assert_no_orm_column_aliases,
    assert_schema_matches_orm,
)


# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------


class _TestBase(DeclarativeBase):
    pass


class _Gadget(_TestBase):
    __tablename__ = "gadgets"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    serial: Mapped[str] = mapped_column(String(64), nullable=False)
    notes: Mapped[str | None] = mapped_column(String(256), nullable=True)


def _make_engine(url: str = "postgresql+asyncpg://user:pass@localhost/test") -> MagicMock:
    engine = MagicMock()
    engine.url = MagicMock()
    engine.url.__str__ = lambda _: url
    return engine


def _make_inspector(
    tables: list[str],
    columns_by_table: _ColumnsByTable,
) -> MagicMock:
    inspector = MagicMock()
    inspector.get_table_names.return_value = tables
    inspector.get_columns.side_effect = lambda table: columns_by_table.get(table, [])
    return inspector


# ---------------------------------------------------------------------------
# SQLite skip
# ---------------------------------------------------------------------------


class TestSqliteSkip:
    async def test_sqlite_engine_is_skipped(self) -> None:
        engine = _make_engine("sqlite+aiosqlite:///./test.db")
        with MagicMock() as connect_mock:
            engine.connect = connect_mock
            connect_mock.side_effect = AssertionError("should not connect for SQLite")
            await assert_schema_matches_orm(engine, _TestBase)  # must not raise


# ---------------------------------------------------------------------------
# Passing cases
# ---------------------------------------------------------------------------


def _wired_engine(mismatches: list[str]) -> MagicMock:
    """Return a mock async engine whose run_sync returns *mismatches* directly.

    Using ``run_sync(return_value=mismatches)`` avoids the ``sa_inspect(conn)``
    call inside ``_inspect``, which would transform a MagicMock conn into an
    unexpected SA inspection object.  The inspection logic itself is tested
    separately via ``_run_inspect_logic``.
    """
    engine = _make_engine()
    conn = AsyncMock()
    conn.run_sync = AsyncMock(return_value=mismatches)
    engine.connect = MagicMock()
    engine.connect.return_value.__aenter__ = AsyncMock(return_value=conn)
    engine.connect.return_value.__aexit__ = AsyncMock(return_value=False)
    return engine


class TestPassingCases:
    async def test_exact_match_passes(self) -> None:
        """No mismatches returned by _inspect → no RuntimeError raised."""
        await assert_schema_matches_orm(_wired_engine([]), _TestBase)

    async def test_extra_db_columns_are_allowed(self) -> None:
        """Extra columns in the DB (not in ORM) produce no mismatches."""
        # The _inspect logic only checks ORM → DB (not the reverse).
        # Verified separately via _run_inspect_logic.
        await assert_schema_matches_orm(_wired_engine([]), _TestBase)


# ---------------------------------------------------------------------------
# Failure detection (driven via internal logic mirroring)
# ---------------------------------------------------------------------------


def _run_inspect_logic(
    inspector: MagicMock,
    base: type[DeclarativeBase],
) -> list[str]:
    """Drive the _inspect closure logic synchronously for unit testing."""
    alias_map = {}
    for mapper in base.registry.mappers:
        if not isinstance(mapper.local_table, Table):
            continue
        tname = mapper.local_table.name
        alias_map[tname] = {
            attr.columns[0].name: attr.key
            for attr in mapper.column_attrs
            if attr.key != attr.columns[0].name
        }

    existing_tables: set[str] = set(inspector.get_table_names())
    mismatches: list[str] = []

    for table_name, table in base.metadata.tables.items():
        if table_name not in existing_tables:
            mismatches.append(f"missing table: {table_name!r}")
            continue
        db_columns = {
            c["name"]: c for c in inspector.get_columns(table_name)
        }
        table_aliases = alias_map.get(table_name, {})
        for column in table.columns:
            col_name: str = column.name
            if col_name not in db_columns:
                python_attr = table_aliases.get(col_name)
                hint = f" (ORM attribute: {python_attr!r})" if python_attr else ""
                mismatches.append(f"{table_name}.{col_name!r}: column missing from DB{hint}")
                continue
            db_col = db_columns[col_name]
            orm_nullable: bool | None = column.nullable
            if orm_nullable is not None:
                db_nullable: bool = bool(db_col.get("nullable", True))
                if orm_nullable != db_nullable:
                    mismatches.append(
                        f"{table_name}.{col_name!r}: nullable mismatch "
                        f"(ORM={orm_nullable}, DB={db_nullable})"
                    )
    return mismatches


class TestFailureDetection:
    def test_missing_table_detected(self) -> None:
        inspector = _make_inspector(tables=[], columns_by_table={})
        mismatches = _run_inspect_logic(inspector, _TestBase)
        assert any("missing table" in m and "gadgets" in m for m in mismatches)

    def test_missing_column_detected(self) -> None:
        inspector = _make_inspector(
            tables=["gadgets"],
            columns_by_table={
                "gadgets": [
                    {"name": "id", "nullable": False},
                    # "serial" is missing
                    {"name": "notes", "nullable": True},
                ]
            },
        )
        mismatches = _run_inspect_logic(inspector, _TestBase)
        assert any("serial" in m and "missing from DB" in m for m in mismatches)

    def test_nullable_mismatch_detected(self) -> None:
        inspector = _make_inspector(
            tables=["gadgets"],
            columns_by_table={
                "gadgets": [
                    {"name": "id", "nullable": False},
                    {"name": "serial", "nullable": True},   # ORM says NOT NULL
                    {"name": "notes", "nullable": True},
                ]
            },
        )
        mismatches = _run_inspect_logic(inspector, _TestBase)
        assert any("serial" in m and "nullable mismatch" in m for m in mismatches)

    def test_alias_column_error_includes_orm_key(self) -> None:
        class _ABase(DeclarativeBase):
            pass

        class _A(_ABase):
            __tablename__ = "a_table"
            id: Mapped[int] = mapped_column(Integer, primary_key=True)
            python_name: Mapped[str | None] = mapped_column("db_name", String(64), nullable=True)

        inspector = _make_inspector(
            tables=["a_table"],
            columns_by_table={"a_table": [{"name": "id", "nullable": False}]},
        )
        mismatches = _run_inspect_logic(inspector, _ABase)
        assert any("db_name" in m and "python_name" in m for m in mismatches)

    async def test_raises_runtime_error_on_mismatch(self) -> None:
        engine = _wired_engine(["missing table: 'gadgets'"])
        with pytest.raises(RuntimeError, match="Schema drift detected"):
            await assert_schema_matches_orm(engine, _TestBase)


# ---------------------------------------------------------------------------
# assert_no_orm_column_aliases
# ---------------------------------------------------------------------------


class TestAssertNoOrmColumnAliases:
    def test_clean_base_passes(self) -> None:
        class _CleanBase(DeclarativeBase):
            pass

        class _Clean(_CleanBase):
            __tablename__ = "clean"
            id: Mapped[int] = mapped_column(Integer, primary_key=True)
            value: Mapped[str] = mapped_column(String(64), nullable=False)

        assert_no_orm_column_aliases(_CleanBase)  # must not raise

    def test_aliased_column_raises(self) -> None:
        class _ABase(DeclarativeBase):
            pass

        class _A(_ABase):
            __tablename__ = "atbl"
            id: Mapped[int] = mapped_column(Integer, primary_key=True)
            py_name: Mapped[str | None] = mapped_column("db_name", String(64), nullable=True)

        with pytest.raises(AssertionError, match="py_name"):
            assert_no_orm_column_aliases(_ABase)

    def test_error_message_shows_both_names(self) -> None:
        class _BBase(DeclarativeBase):
            pass

        class _B(_BBase):
            __tablename__ = "btbl"
            id: Mapped[int] = mapped_column(Integer, primary_key=True)
            new_name: Mapped[str | None] = mapped_column("old_name", String(64), nullable=True)

        with pytest.raises(AssertionError) as exc_info:
            assert_no_orm_column_aliases(_BBase)

        msg = str(exc_info.value)
        assert "new_name" in msg
        assert "old_name" in msg

    def test_musehub_base_has_zero_aliases(self) -> None:
        """Regression: musehub's production Base must have no column aliases."""
        from musehub.db import models  # noqa: F401 — registers all models on Base
        from musehub.db import muse_cli_models  # noqa: F401
        from musehub.db.database import Base

        assert_no_orm_column_aliases(Base)  # raises AssertionError if any alias exists