Skip to content

~95% on SimpleQA (e.g. Qwen3.6-27B on a 3090). Supports all local and cloud LLMs (llama.cpp, Ollama, Google, ...). 10+ search engines - arXiv, PubMed, your private documents. Everything Local & Encrypted.

29.7 KB
Raw Blame
"""Contract tests for the ORM model layer: what the schema DECLARES vs
what the writers actually do.

Scope is ``src/local_deep_research/database/models/`` only (the migration
chain is covered elsewhere).  Five declared-vs-assumed axes:

1. ``Enum(...)`` columns -- does every writer pass a member the column can
   read back?  SQLAlchemy's ``Enum`` defaults to ``validate_strings=False``,
   so an unknown *string* is written through verbatim and only blows up on
   the next READ (``LookupError``).  Whether a bare string is "unknown"
   depends on whether the column is keyed by member NAME or member VALUE,
   which in turn depends on ``values_callable`` -- and on whether the enum
   class subclasses ``str`` (str-mixin members hash as their value, so the
   value string silently resolves).
2. UNIQUE / partial indexes -- is there a writer that violates one under
   normal use?
3. FK ``ondelete`` vs what the deletion services assume.
4. ``nullable=False`` with no default and a writer that omits it.
5. ``UtcDateTime`` columns -- a naive datetime raises INSIDE the query.

Every sweep asserts a floor on how much it examined, so a sweep that
silently stops finding columns fails instead of passing green.

Execution tests use a real on-disk SQLite database (never ``:memory:``)
with ``PRAGMA foreign_keys=ON``, matching
``sqlcipher_utils.py`` which turns FK enforcement on for every real
connection.  No app boot, no SQLCipher.
"""

from __future__ import annotations

import contextlib
import uuid
from datetime import datetime, timezone
from pathlib import Path

import pytest
import sqlalchemy as sa
from sqlalchemy import event
from sqlalchemy.dialects import sqlite as sqlite_dialect
from sqlalchemy.exc import IntegrityError, StatementError
from sqlalchemy.orm import Session

from local_deep_research.database.models import (
    Base,
    ChatMessage,
    ChatMessageType,
    ChatSession,
    DownloadTracker,
    NewsCard,
    ResearchHistory,
    ResearchResource,
)

SQLITE_DIALECT = sqlite_dialect.dialect()

SRC_ROOT = Path(__file__).resolve().parents[2] / "src" / "local_deep_research"


# ---------------------------------------------------------------------------
# Fixtures
# ---------------------------------------------------------------------------


@pytest.fixture
def engine(tmp_path):
    """Real on-disk SQLite engine with FK enforcement on.

    On-disk (not ``:memory:``) so more than one connection sees the same
    schema, which the deletion-cascade and service-driven tests need.
    Function-scoped so no test inherits another's rows.
    """
    db_path = tmp_path / "contracts.db"
    eng = sa.create_engine(f"sqlite:///{db_path}")

    @event.listens_for(eng, "connect")
    def _fk_on(dbapi_conn, _record):  # pragma: no cover - trivial
        cur = dbapi_conn.cursor()
        cur.execute("PRAGMA foreign_keys = ON")
        cur.close()

    Base.metadata.create_all(eng)
    yield eng
    eng.dispose()


@pytest.fixture
def db(engine):
    with Session(engine) as session:
        yield session


# ---------------------------------------------------------------------------
# Helpers -- derived from the DECLARED schema, not from production logic
# ---------------------------------------------------------------------------


def _enum_columns():
    """Every ``Enum(<python enum class>)`` column in the metadata."""
    found = []
    for table in Base.metadata.tables.values():
        for col in table.columns:
            if isinstance(col.type, sa.Enum) and col.type.enum_class:
                found.append((f"{table.name}.{col.name}", col))
    return sorted(found)


def _round_trip(col_type, literal):
    """Bind ``literal`` through the column type and read it back.

    Uses only the public processor API.  Returns the value the ORM would
    hand back on a SELECT, or raises whatever the read raises.
    """
    bind = col_type.bind_processor(SQLITE_DIALECT)
    stored = bind(literal) if bind else literal
    result = col_type.result_processor(SQLITE_DIALECT, None)
    return result(stored) if result else stored


def _keying(col):
    """'name' if the column persists member names, 'value' if values."""
    enum_class = col.type.enum_class
    stored = tuple(col.type.enums)
    if stored == tuple(enum_class.__members__):
        return "name"
    if stored == tuple(m.value for m in enum_class):
        return "value"
    return "other"


def _fk_edges():
    """(child_table, child_col, parent_table, ondelete, child_nullable)."""
    edges = []
    for table in Base.metadata.tables.values():
        for fk in table.foreign_keys:
            edges.append(
                (
                    table.name,
                    fk.parent.name,
                    fk.column.table.name,
                    (fk.ondelete or "NO ACTION").upper(),
                    fk.parent.nullable,
                )
            )
    return sorted(edges)


@contextlib.contextmanager
def _session_cm(engine):
    """One short-lived Session, rolled back on error.

    Stands in for ``database.session_context.get_user_db_session`` so the
    real service methods can be driven without SQLCipher or an app boot.
    """
    with Session(engine) as session:
        # Session.close() on exit discards any open transaction, so a
        # failed write leaves nothing half-applied.
        yield session


def _patch_user_db_session(monkeypatch, module, engine):
    monkeypatch.setattr(
        module,
        "get_user_db_session",
        lambda *_a, **_k: _session_cm(engine),
    )


# ===========================================================================
# 1. Enum columns: does every writer pass a member the column can read back?
# ===========================================================================


# Locked inventory.  A column moving between these buckets silently
# invalidates every row already written under the old keying, so drift here
# must fail loudly rather than be re-derived from the code under test.
EXPECTED_NAME_KEYED = {
    "benchmark_progress.current_dataset",
    "benchmark_results.dataset_type",
    "benchmark_runs.status",
    "news_cards.card_type",
    "news_user_ratings.rating_type",
    "research.mode",
    "research.status",
    "settings.type",
}

EXPECTED_VALUE_KEYED = {
    "chat_messages.message_type",
    "chat_messages.role",
    "chat_sessions.status",
    "collections.embedding_model_type",
    "document_chunks.embedding_model_type",
    "documents.status",
    "download_queue.status",
    "note_syntheses.synthesis_type",
    "note_versions.change_type",
    "rag_indices.embedding_model_type",
    "rag_indices.status",
}


def test_enum_column_keying_inventory_is_locked():
    """Every Enum column persists either member names or member values."""
    columns = _enum_columns()
    assert len(columns) >= 19, (
        "enum sweep examined only "
        f"{len(columns)} columns -- the sweep itself is broken"
    )

    by_keying = {"name": set(), "value": set(), "other": set()}
    for label, col in columns:
        by_keying[_keying(col)].add(label)

    assert by_keying["other"] == set(), (
        "Enum column whose stored literals match neither the member names "
        f"nor the member values: {sorted(by_keying['other'])}"
    )
    assert by_keying["name"] == EXPECTED_NAME_KEYED
    assert by_keying["value"] == EXPECTED_VALUE_KEYED


def test_enum_columns_silently_accept_unreadable_literals():
    """Which Enum columns accept a write they can never read back.

    ``validate_strings`` defaults to False, so a string the column does not
    recognise is stored verbatim; the failure only surfaces on the next
    SELECT as ``LookupError``.  This test enumerates, per column, the
    "natural mistake" literal for that column's keying:

      * name-keyed column -> a writer passing ``member.value``
      * value-keyed column -> a writer passing ``member.name``

    A name-keyed column escapes only when its enum class subclasses
    ``str``: str-mixin members hash as their own value, so the value
    spelling resolves through the member key.  No value-keyed column
    escapes -- the member NAME is never a lookup key.

    This measures the column TYPE, which is what an INSERT binds through.
    Two of these columns additionally carry an explicit mirror CHECK that
    catches the bad literal at the DB layer instead (see
    ``test_no_enum_column_has_a_db_check_constraint_backstop``), and the
    chat writers validate via ``ChatRole(role)`` before the DB is touched.
    The rest have no backstop at any layer.
    """
    columns = _enum_columns()
    assert len(columns) >= 19

    traps = {}
    for label, col in columns:
        enum_class = col.type.enum_class
        keying = _keying(col)
        member = next(iter(enum_class))
        literal = member.value if keying == "name" else member.name
        if not isinstance(literal, str):
            continue
        assert col.type.validate_strings is False, (
            f"{label}: validate_strings flipped on -- update this test"
        )
        try:
            _round_trip(col.type, literal)
        except LookupError:
            traps[label] = literal

    # Both directions are traps.  Name-keyed columns backed by a plain
    # (non-str) Enum lose the value spelling; EVERY value-keyed column
    # loses the name spelling, str-mixin or not, because the lookup table
    # is keyed on members (which hash as their value) plus the stored
    # literals.  ``research.status`` and ``settings.type`` are absent
    # because ResearchStatus/SettingType subclass ``str``, so the value
    # spelling resolves through the member key.
    assert traps == {
        "benchmark_progress.current_dataset": "simpleqa",
        "benchmark_results.dataset_type": "simpleqa",
        "benchmark_runs.status": "pending",
        "chat_messages.message_type": "QUERY",
        "chat_messages.role": "USER",
        "chat_sessions.status": "ACTIVE",
        "collections.embedding_model_type": "SENTENCE_TRANSFORMERS",
        "document_chunks.embedding_model_type": "SENTENCE_TRANSFORMERS",
        "documents.status": "PENDING",
        "download_queue.status": "PENDING",
        "news_cards.card_type": "news",
        "news_user_ratings.rating_type": "relevance",
        "note_syntheses.synthesis_type": "MERGE",
        "note_versions.change_type": "INITIAL",
        "rag_indices.embedding_model_type": "SENTENCE_TRANSFORMERS",
        "rag_indices.status": "ACTIVE",
        "research.mode": "quick",
    }


def test_no_enum_column_has_a_db_check_constraint_backstop():
    """Nothing at the DB level rejects the bad literal on the way in.

    SQLAlchemy 2.0 defaults ``create_constraint=False``, so the trap
    literals above are accepted by SQLite without complaint.  Only two
    tables declare an explicit mirror CHECK.
    """
    guarded = set()
    for table in Base.metadata.tables.values():
        enum_cols = {
            c.name
            for c in table.columns
            if isinstance(c.type, sa.Enum) and c.type.enum_class
        }
        if not enum_cols:
            continue
        for con in table.constraints:
            if isinstance(con, sa.CheckConstraint):
                text = str(con.sqltext)
                for name in enum_cols:
                    if name in text:
                        guarded.add(f"{table.name}.{name}")

    assert guarded == {
        "note_versions.change_type",
        "note_syntheses.synthesis_type",
    }, f"unexpected CHECK coverage on enum columns: {sorted(guarded)}"

    unguarded = {label for label, _ in _enum_columns()} - guarded
    assert len(unguarded) >= 17


def test_card_storage_create_writes_a_row_that_can_never_be_read(db):
    """DEFECT: ``SQLCardStorage.create`` writes the enum VALUE.

    ``news_cards.card_type`` is ``Enum(CardType)`` with no
    ``values_callable``, so it round-trips by member NAME ('NEWS').
    ``card_storage.py`` passes ``data.get("card_type", ..., "news")`` --
    the value.  CardType is a plain ``enum.Enum`` (no ``str`` mixin), so
    the lookup misses, ``validate_strings=False`` lets the write through,
    and every later read of that row raises ``LookupError``.
    """
    from local_deep_research.news.core.card_storage import SQLCardStorage

    storage = SQLCardStorage(db)
    card_id = storage.create(
        {"id": "trap-card", "topic": "Rome", "card_type": "news"}
    )

    with Session(db.get_bind()) as raw:
        stored = raw.execute(
            sa.text("SELECT card_type FROM news_cards WHERE id = :i"),
            {"i": card_id},
        ).scalar_one()
    assert stored == "news", (
        f"expected the raw value spelling to reach the column; got {stored!r}"
    )
    assert stored not in NewsCard.__table__.c.card_type.type.enums

    with pytest.raises(LookupError) as excinfo:
        storage.get(card_id)
    assert "not among the defined enum values" in str(excinfo.value)


def test_card_storage_type_filter_never_matches_a_correct_row(db):
    """The read side is broken symmetrically.

    Even a row written CORRECTLY (``CardType.NEWS`` -> 'NEWS') is invisible
    to the string filters the news feed uses: ``storage_manager`` calls
    ``get_recent(card_types=["news"])`` and ``card_type.in_(["news"])``
    binds 'news' through unchanged.
    """
    from local_deep_research.database.models.news import CardType
    from local_deep_research.news.core.card_storage import SQLCardStorage

    db.add(
        NewsCard(
            id="good-card",
            title="Carthage",
            card_type=CardType.NEWS,
            discovered_at=datetime.now(timezone.utc),
        )
    )
    db.commit()

    storage = SQLCardStorage(db)
    assert storage.get_recent(hours=24, card_types=["news"]) == []
    assert len(storage.get_recent(hours=24, card_types=["NEWS"])) == 1, (
        "the name spelling is the only one that matches"
    )


# ===========================================================================
# 2. UNIQUE / partial indexes vs writers
# ===========================================================================


def test_unique_constraint_inventory_floor():
    """Sweep every UNIQUE surface so a shrinking sweep fails."""
    surfaces = []
    for table in Base.metadata.tables.values():
        for con in table.constraints:
            if isinstance(con, sa.UniqueConstraint):
                surfaces.append(
                    (table.name, tuple(c.name for c in con.columns))
                )
        for idx in table.indexes:
            if idx.unique:
                surfaces.append(
                    (table.name, tuple(c.name for c in idx.columns))
                )
    assert len(surfaces) >= 30, (
        f"unique sweep found only {len(surfaces)} surfaces"
    )
    assert ("chat_messages", ("session_id", "sequence_number")) in surfaces


def test_partial_unique_index_predicate_matches_the_enum_value():
    """``ux_research_history_chat_session_in_progress`` is a string
    predicate over a Text column.  If ``ResearchStatus.IN_PROGRESS``'s
    value ever changes, the index goes silently dead -- it would still be
    created, still be unique, and match zero rows.
    """
    from local_deep_research.constants import ResearchStatus

    table = Base.metadata.tables["research_history"]
    idx = next(
        i
        for i in table.indexes
        if i.name == "ux_research_history_chat_session_in_progress"
    )
    assert idx.unique
    predicates = [
        str(idx.dialect_options["sqlite"]["where"]),
        str(idx.dialect_options["postgresql"]["where"]),
    ]
    for predicate in predicates:
        assert f"'{ResearchStatus.IN_PROGRESS.value}'" in predicate, (
            "partial-index predicate no longer matches the enum value it "
            f"filters on: {predicate}"
        )


def _seed_attempt(db, session_id, research_id, user_msg_id):
    """A research row linked back to its user message, as send_message
    writes it (``research_meta.submission.message_id``)."""
    db.add(
        ResearchHistory(
            id=research_id,
            query="q",
            mode="quick_summary",
            status="completed",
            created_at="2026-01-01T00:00:00+00:00",
            chat_session_id=session_id,
            research_meta={"submission": {"message_id": user_msg_id}},
        )
    )
    db.commit()


def test_chat_message_sequence_collides_after_delete_attempt(
    engine, monkeypatch
):
    """After deleting a non-final chat attempt, the session keeps
    accepting messages and the sequence UNIQUE constraint still holds.

    Premise history: sequence_number was originally allocated from the
    ``message_count`` counter, which ``delete_attempt`` rewinds — deleting
    a non-final attempt rewound the counter onto live rows and every
    subsequent write violated ``uq_chat_message_session_seq``, wedging the
    session. The fix in this PR allocates ``MAX(sequence_number) + 1``
    instead, so the manufactured collision is no longer reachable and this
    test now asserts the post-fix contract: the next message succeeds with
    the correct sequence, and the constraint itself still rejects a direct
    duplicate-sequence insert.
    """
    from local_deep_research.chat import service as chat_service

    _patch_user_db_session(monkeypatch, chat_service, engine)
    monkeypatch.setattr(
        chat_service, "is_research_thread_alive", lambda _rid: False
    )
    monkeypatch.setattr(chat_service, "cleanup_research", lambda _rid: None)
    monkeypatch.setattr(chat_service, "set_termination_flag", lambda _rid: None)

    svc = chat_service.ChatService("wedge-user")
    session_id = svc.create_session(title="wedge")

    research_a = str(uuid.uuid4())
    research_b = str(uuid.uuid4())

    # seq 1,2 -> attempt A ; seq 3,4 -> attempt B
    msg_a_user = svc.add_message(session_id, "user", "q1", "query")
    with Session(engine) as seed:
        _seed_attempt(seed, session_id, research_a, msg_a_user)
    svc.add_message(
        session_id, "assistant", "a1", "response", research_id=research_a
    )
    msg_b_user = svc.add_message(session_id, "user", "q2", "followup")
    with Session(engine) as seed:
        _seed_attempt(seed, session_id, research_b, msg_b_user)
    svc.add_message(
        session_id, "assistant", "a2", "response", research_id=research_b
    )

    with Session(engine) as check:
        assert check.get(ChatSession, session_id).message_count == 4
        assert sorted(
            r[0]
            for r in check.query(ChatMessage.sequence_number).filter_by(
                session_id=session_id
            )
        ) == [1, 2, 3, 4]

    # Delete the OLDER attempt.  Rows 1 and 2 go; rows 3 and 4 remain.
    assert svc.delete_attempt(session_id, research_a) is True

    with Session(engine) as check:
        assert check.get(ChatSession, session_id).message_count == 2
        assert sorted(
            r[0]
            for r in check.query(ChatMessage.sequence_number).filter_by(
                session_id=session_id
            )
        ) == [3, 4]

    # Premise changed in this PR: sequence allocation moved from
    # ``message_count + 1`` (which rewound onto live rows after a
    # non-tail deletion and manufactured UNIQUE violations) to
    # ``MAX(sequence_number) + 1``, so the next write is handed 5 and
    # succeeds where the old allocation collided with row 3.
    next_msg = svc.add_message(session_id, "user", "q3", "followup")
    assert next_msg is not None
    with Session(engine) as check:
        assert check.get(ChatSession, session_id).message_count == 3
        assert sorted(
            r[0]
            for r in check.query(ChatMessage.sequence_number).filter_by(
                session_id=session_id
            )
        ) == [3, 4, 5]

    # The UNIQUE constraint itself is still the backstop: a direct
    # duplicate-sequence insert must raise exactly as before.
    with Session(engine) as dup, pytest.raises(IntegrityError) as excinfo:
        dup.add(
            ChatMessage(
                id=str(uuid.uuid4()),
                session_id=session_id,
                role="user",
                message_type=ChatMessageType.FOLLOWUP,
                content="duplicate sequence",
                sequence_number=5,
            )
        )
        dup.commit()
    assert (
        "UNIQUE constraint failed: chat_messages.session_id, "
        "chat_messages.sequence_number" in str(excinfo.value)
    )


# ===========================================================================
# 3. FK ondelete vs what the deletion services assume
# ===========================================================================


def test_fk_inventory_floor_and_ondelete_actions():
    edges = _fk_edges()
    assert len(edges) >= 55, f"FK sweep found only {len(edges)} edges"
    actions = {e[3] for e in edges}
    assert actions <= {"CASCADE", "SET NULL", "NO ACTION"}


def _cascade_closure(root):
    """Tables removed by a single ``DELETE FROM <root>`` at the DB level."""
    reachable = {root}
    frontier = [root]
    while frontier:
        parent = frontier.pop()
        for child, _col, target, ondelete, _null in _fk_edges():
            if target == parent and ondelete == "CASCADE":
                if child not in reachable:
                    reachable.add(child)
                    frontier.append(child)
    return reachable


def test_research_delete_cascade_is_blocked_by_no_action_parents():
    """Static: the CASCADE closure of ``research_history`` is fenced in by
    two NOT NULL / NO ACTION references, so the DB refuses the delete.
    """
    closure = _cascade_closure("research_history")
    assert "research_resources" in closure
    assert len(closure) >= 8, f"cascade closure too small: {closure}"

    blocking = {
        f"{child}.{col} -> {target}"
        for child, col, target, ondelete, nullable in _fk_edges()
        if target in closure
        and child not in closure
        and ondelete == "NO ACTION"
        and not nullable
    }
    assert blocking == {
        "download_duplicates.resource_id -> research_resources",
        "download_tracker.first_resource_id -> research_resources",
    }


def test_deleting_a_research_with_a_downloaded_pdf_raises(db):
    """Negative control for the raw bulk-delete pattern (not the route).

    A bare ``ResearchHistory ... .delete(synchronize_session=False)``
    that leans entirely on DB-level CASCADE aborts: the cascade
    reaches ``research_resources``, whose rows are still referenced
    NOT NULL / NO ACTION from ``download_tracker.first_resource_id``.
    With ``PRAGMA foreign_keys=ON`` (set for every real connection in
    ``sqlcipher_utils.py``) SQLite aborts the whole statement.  The
    route no longer uses this pattern -- it goes through
    ``_delete_research_with_dependents``, which deletes the blocking
    rows first (see ``test_delete_research_helper_cleans_...`` below
    and ``TestDepth2ResourceChildCascade``) -- so this test pins the
    pattern, not the current route behaviour.
    """
    research_id = str(uuid.uuid4())
    db.add(
        ResearchHistory(
            id=research_id,
            query="dawn of rome",
            mode="detailed_report",
            status="completed",
            created_at="2026-01-01T00:00:00+00:00",
        )
    )
    resource = ResearchResource(
        research_id=research_id,
        title="a paper",
        url="https://example.org/p.pdf",
        created_at="2026-01-01T00:00:00+00:00",
    )
    db.add(resource)
    db.flush()
    db.add(
        DownloadTracker(
            url="https://example.org/p.pdf",
            url_hash="h" * 64,
            first_resource_id=resource.id,
            is_downloaded=True,
        )
    )
    db.commit()

    with pytest.raises(IntegrityError) as excinfo:
        db.query(ResearchHistory).filter(
            ResearchHistory.id == research_id
        ).delete(synchronize_session=False)
        db.commit()
    assert "FOREIGN KEY constraint failed" in str(excinfo.value)
    db.rollback()

    # Still there.
    assert db.get(ResearchHistory, research_id) is not None


def test_delete_research_helper_cleans_download_tracker_rows():
    """The NO ACTION blockers above must be deleted explicitly before
    the ``research_resources`` rows go away -- production 2026-09-23
    proved the DB cascade alone 500s on researches with downloaded
    sources. The cascade is metadata-driven
    (``_research_cascade_plan``), so the tables are not named in the
    helper body; assert instead that the plan classifies the
    download-tracker edges as deletes."""
    from local_deep_research.database.research_cascade import (
        _research_cascade_plan,
    )

    _set_null_steps, delete_steps, _table_to_class = _research_cascade_plan()
    pairs = {(step[0], step[1]) for step in delete_steps}
    assert ("download_tracker", "first_resource_id") in pairs
    assert ("download_duplicates", "resource_id") in pairs
    assert ("download_duplicates", "url_hash") in pairs
    assert ("download_attempts", "url_hash") in pairs


# ===========================================================================
# 4. nullable=False, no default, writer omits it
# ===========================================================================


def _required_columns():
    """NOT NULL, no default, no server_default, not a PK."""
    required = {}
    for table in Base.metadata.tables.values():
        names = [
            c.name
            for c in table.columns
            if not c.nullable
            and not c.primary_key
            and c.default is None
            and c.server_default is None
        ]
        if names:
            required[table.name] = set(names)
    return required


def test_required_column_inventory_floor():
    required = _required_columns()
    total = sum(len(v) for v in required.values())
    assert total >= 150, f"required-column sweep found only {total} columns"
    assert required["news_user_ratings"] == {"card_id", "rating_type"}


def test_rating_storage_writer_targets_a_schema_that_does_not_exist(db):
    """DEFECT: ``SQLRatingStorage`` is written against a different
    ``UserRating`` than the one the models declare.

    It supplies ``user_id / item_id / item_type / relevance_vote /
    quality_rating`` -- none of which are mapped -- and supplies NEITHER of
    the two NOT NULL columns that are (``card_id``, ``rating_type``).  Every
    call raises before a row is even attempted, so ratings written through
    this class are silently lost.
    """
    from local_deep_research.database.models.news import UserRating
    from local_deep_research.news.rating_system.storage import (
        SQLRatingStorage,
    )

    mapped = set(UserRating.__table__.c.keys())
    assumed = {
        "user_id",
        "item_id",
        "item_type",
        "relevance_vote",
        "quality_rating",
        "news_item_id",
        "updated_at",
    }
    assert assumed & mapped == set(), (
        "the storage layer's field names now exist on the model -- "
        "re-check this test"
    )
    assert not hasattr(UserRating, "to_dict")

    with pytest.raises(TypeError):
        SQLRatingStorage(db).create(
            {
                "user_id": "u1",
                "item_id": "card-1",
                "item_type": "card",
                "rating_value": "up",
            }
        )


def test_news_rating_card_id_fk_has_no_ondelete(db):
    """``news_user_ratings.card_id`` is NOT NULL, NO ACTION.

    ``news/api.py::submit_feedback`` documents that it deliberately does
    NOT check the card exists ("news items are generated dynamically") --
    but the FK does, so a vote on a card that was never persisted is
    rejected outright.
    """
    from local_deep_research.database.models.news import (
        RatingType,
        UserRating,
    )

    edge = next(
        e
        for e in _fk_edges()
        if e[0] == "news_user_ratings" and e[1] == "card_id"
    )
    assert edge[2:] == ("news_cards", "NO ACTION", False)

    db.add(
        UserRating(
            card_id="never-persisted",
            rating_type=RatingType.RELEVANCE,
            rating_value="up",
        )
    )
    with pytest.raises(IntegrityError):
        db.commit()
    db.rollback()


# ===========================================================================
# 5. UtcDateTime: a naive datetime raises INSIDE the query
# ===========================================================================


def test_every_datetime_column_is_utcdatetime():
    """No column tolerates a naive datetime; all 130+ reject it."""
    utc_cols, plain = [], []
    for table in Base.metadata.tables.values():
        for col in table.columns:
            name = type(col.type).__name__
            if name == "UtcDateTime":
                utc_cols.append(f"{table.name}.{col.name}")
            elif isinstance(col.type, sa.DateTime):
                plain.append(f"{table.name}.{col.name}")
    assert len(utc_cols) >= 130, (
        f"datetime sweep found only {len(utc_cols)} UtcDateTime columns"
    )
    assert plain == [], (
        f"plain DateTime columns silently accept naive values: {plain}"
    )


def test_naive_datetime_raises_inside_the_query_not_at_parse(db):
    """The bind processor, not the caller, is what rejects a naive value."""
    naive = datetime(2026, 1, 1, 0, 0, 0)
    with pytest.raises(StatementError) as excinfo:
        db.query(ChatMessage).filter(ChatMessage.created_at < naive).all()
    assert "naive datetime is disallowed" in str(excinfo.value)


def test_chat_pagination_rejects_unusable_cursors_before_query_binding(
    engine, monkeypatch
):
    """Invalid client input must not become a UtcDateTime binding error."""
    from local_deep_research.chat import service as chat_service

    _patch_user_db_session(monkeypatch, chat_service, engine)
    svc = chat_service.ChatService("cursor-user")
    session_id = svc.create_session(title="cursor")

    for cursor in ("not-a-date", "2026-01-01T00:00:00"):
        with pytest.raises(chat_service.ChatCursorError):
            svc.get_session_messages(session_id, before_created_at=cursor)

    # The same instant with an offset is fine.
    assert (
        svc.get_session_messages(
            session_id, before_created_at="2026-01-01T00:00:00+00:00"
        )
        == []
    )