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.

31.6 KB
Raw Blame
"""Tests for migration 0010: Add chat tables.

Migration 0010 introduces the chat schema in its final clean shape:
(originally numbered 0009; renumbered to 0010 when main's
0009_default_fetch_mode_summary landed first.)

- chat_sessions with status as Enum (ChatSessionStatus)
- chat_messages with content NOT NULL, no CHECK
- chat_progress_steps as a separate table for transient research
  progress events (no longer mixed into chat_messages)
- research_history.chat_session_id (FK SET NULL) + step_count

The migration is fresh-install only; legacy 0007-shape dev DBs must
be recreated (or the chat tables dropped manually) before running.

Tests cover:
- Fresh-install path: chat tables exist, content is NOT NULL, no
  CHECK constraint, status is Enum-typed.
- Idempotency: re-running migrations on a head DB is a no-op.
- Downgrade is NotImplementedError.
"""

from importlib import import_module
import warnings

import pytest
from alembic import command
from sqlalchemy import create_engine, event, inspect

from sqlalchemy import text
from sqlalchemy.exc import IntegrityError, SAWarning

from local_deep_research.database.alembic_runner import (
    get_alembic_config,
    run_migrations,
    stamp_database,
)


_PARTIAL_UNIQUE_INDEX_NAME = "ux_research_history_chat_session_in_progress"
_MIGRATION_MODULE = (
    "local_deep_research.database.migrations.versions.0010_add_chat_tables"
)


def _run_downgrade_to(engine, revision):
    config = get_alembic_config(engine)
    with engine.begin() as conn:
        config.attributes["connection"] = conn
        command.downgrade(config, revision)


def _create_research_history_with_fk(
    engine,
    *,
    constraint_name="fk_research_history_chat_session_id",
    local_column="chat_session_id",
    referred_table="chat_sessions",
    ondelete="SET NULL",
):
    columns = [
        "id TEXT PRIMARY KEY",
        "query TEXT NOT NULL",
        "mode TEXT NOT NULL",
        "status TEXT NOT NULL",
        "created_at TEXT NOT NULL",
        "chat_session_id TEXT",
    ]
    if local_column != "chat_session_id":
        columns.append(f"{local_column} TEXT")
    constraint_prefix = (
        f"CONSTRAINT {constraint_name} " if constraint_name is not None else ""
    )
    columns.append(
        f"{constraint_prefix}FOREIGN KEY({local_column}) "
        f"REFERENCES {referred_table}(id) ON DELETE {ondelete}"
    )
    with engine.begin() as conn:
        conn.execute(
            text(f"CREATE TABLE research_history ({', '.join(columns)})")
        )


@pytest.fixture
def fresh_engine(tmp_path):
    db_path = tmp_path / "fresh_0010.db"
    engine = create_engine(f"sqlite:///{db_path}")
    yield engine
    engine.dispose()


@pytest.fixture
def fully_migrated_engine(tmp_path):
    db_path = tmp_path / "fully_migrated_0010.db"
    engine = create_engine(f"sqlite:///{db_path}")
    run_migrations(engine)
    yield engine
    engine.dispose()


class TestSchemaShape:
    """Chat schema invariants are in place after 0010 runs."""

    def test_chat_tables_exist(self, fully_migrated_engine):
        insp = inspect(fully_migrated_engine)
        for table in ("chat_sessions", "chat_messages", "chat_progress_steps"):
            assert insp.has_table(table), f"{table} missing"

    def test_chat_messages_content_is_not_null(self, fully_migrated_engine):
        insp = inspect(fully_migrated_engine)
        cols = {c["name"]: c for c in insp.get_columns("chat_messages")}
        assert "content" in cols
        assert cols["content"]["nullable"] is False

    def test_chat_messages_has_no_legacy_check(self, fully_migrated_engine):
        insp = inspect(fully_migrated_engine)
        checks = insp.get_check_constraints("chat_messages")
        names = {c.get("name") for c in checks}
        assert "ck_chat_message_has_content_source" not in names

    def test_chat_session_status_typed(self, fully_migrated_engine):
        """status is typed VARCHAR with default 'active'.

        Note: SQLAlchemy's Enum on SQLite stores as VARCHAR sized to
        the longest enum value but does NOT emit a DB-level CHECK
        unless `create_constraint=True` is explicitly set. The
        codebase relies on ORM-layer validation (ChatSessionStatus(value))
        for value enforcement — same pattern as ChatRole/ChatMessageType.
        """
        insp = inspect(fully_migrated_engine)
        cols = {c["name"]: c for c in insp.get_columns("chat_sessions")}
        assert "status" in cols
        # VARCHAR sized to the longest enum value ('archived' = 8 chars)
        type_str = str(cols["status"]["type"]).upper()
        assert "VARCHAR" in type_str

    def test_research_history_chat_session_id_present(
        self, fully_migrated_engine
    ):
        insp = inspect(fully_migrated_engine)
        cols = {c["name"] for c in insp.get_columns("research_history")}
        assert "chat_session_id" in cols
        assert "step_count" in cols

    def test_research_history_has_exactly_one_chat_session_fk(
        self, fully_migrated_engine
    ):
        """The create_all FK and migration guard must not produce duplicates."""
        with fully_migrated_engine.connect() as conn:
            rows = conn.execute(
                text("PRAGMA foreign_key_list(research_history)")
            ).mappings()
            chat_fks = [
                row
                for row in rows
                if row["from"] == "chat_session_id"
                and row["table"] == "chat_sessions"
                and row["to"] == "id"
            ]

        assert len(chat_fks) == 1
        assert chat_fks[0]["on_delete"] == "SET NULL"

    def test_chat_progress_steps_unique_per_research_seq(
        self, fully_migrated_engine
    ):
        insp = inspect(fully_migrated_engine)
        uniques = insp.get_unique_constraints("chat_progress_steps")
        names = {u.get("name") for u in uniques}
        assert "uq_chat_progress_step_research_seq" in names

    def test_composite_indexes_exist_after_upgrade(self, fully_migrated_engine):
        """Composite (session_id, created_at) indexes serve the load-older
        pagination query in chat/service.py::get_session_messages. Without
        them, SQLite uses the single-column session_id index and sorts in
        memory — break-even at ~500 rows/session.
        """
        insp = inspect(fully_migrated_engine)

        msg_idx = {
            i["name"]: i["column_names"]
            for i in insp.get_indexes("chat_messages")
        }
        assert "ix_chat_messages_session_created" in msg_idx
        assert msg_idx["ix_chat_messages_session_created"] == [
            "session_id",
            "created_at",
        ]

        step_idx = {
            i["name"]: i["column_names"]
            for i in insp.get_indexes("chat_progress_steps")
        }
        assert "ix_chat_progress_steps_session_created" in step_idx
        assert step_idx["ix_chat_progress_steps_session_created"] == [
            "session_id",
            "created_at",
        ]


@pytest.mark.parametrize(
    ("actual_ondelete", "should_raise"),
    [
        ("SET NULL", False),
        ("set null", False),
        ("CASCADE", True),
        (None, True),
    ],
)
def test_fk_guard_requires_matching_delete_action(
    monkeypatch, actual_ondelete, should_raise
):
    """Matching FK endpoints are insufficient when delete behavior differs."""
    migration = import_module(_MIGRATION_MODULE)
    options = (
        {"ondelete": actual_ondelete} if actual_ondelete is not None else {}
    )

    class _Inspector:
        @staticmethod
        def has_table(_table_name):
            return True

        @staticmethod
        def get_foreign_keys(_table_name):
            return [
                {
                    "name": None,
                    "constrained_columns": ["chat_session_id"],
                    "referred_table": "chat_sessions",
                    "referred_columns": ["id"],
                    "options": options,
                }
            ]

    monkeypatch.setattr(migration.op, "get_bind", lambda: object())
    monkeypatch.setattr(migration, "inspect", lambda _bind: _Inspector())

    def _check_fk():
        return migration._fk_exists(
            "research_history",
            "fk_research_history_chat_session_id",
            ["chat_session_id"],
            "chat_sessions",
            ["id"],
            "SET NULL",
        )

    if should_raise:
        with pytest.raises(RuntimeError, match="expected SET NULL"):
            _check_fk()
    else:
        assert _check_fk() is True


class TestIdempotency:
    """Re-running migrations on a head DB is a no-op."""

    def test_double_migrate_no_error(self, fresh_engine):
        run_migrations(fresh_engine)
        # Second run must not raise.
        run_migrations(fresh_engine)
        insp = inspect(fresh_engine)
        assert insp.has_table("chat_progress_steps")


class TestDowngrade:
    """Downgrade is not supported and raises NotImplementedError.

    Why: SQLite ALTER TABLE forbids dropping a column that is the
    target of a FOREIGN KEY definition, and alembic's batch_alter_table
    cannot rebuild research_history due to unnamed legacy constraints
    on that table. The project is dev-stage; recreate the DB to roll
    back. The parametrized stairway/down-leaves-no-residual tests in
    test_alembic_migrations.py exempt 0010 via NON_REVERSIBLE_REVISIONS.
    """

    def test_downgrade_raises_not_implemented(self, fully_migrated_engine):
        with pytest.raises(NotImplementedError):
            _run_downgrade_to(fully_migrated_engine, "0008")


class TestExistingDataBackfill:
    """Verify 0010 leaves pre-existing research_history rows in a sane state.

    Note: we cannot use `run_migrations(target="0008")` to land at the
    pre-0010 state because 0001 uses `Base.metadata.create_all` against the
    live `Base`, which already includes `chat_session_id` and `step_count`.
    Instead we hand-build a minimal pre-0010 `research_history` and stamp
    the DB at 0009 so 0010 forward runs the actual ADD COLUMN path.
    """

    def test_step_count_backfilled_for_existing_rows(self, fresh_engine):
        engine = fresh_engine

        # Hand-build pre-0010 research_history (subset of NOT NULL cols).
        with engine.begin() as conn:
            conn.execute(
                text(
                    "CREATE TABLE research_history ("
                    "  id TEXT PRIMARY KEY, "
                    "  query TEXT NOT NULL, "
                    "  mode TEXT NOT NULL, "
                    "  status TEXT NOT NULL, "
                    "  created_at TEXT NOT NULL"
                    ")"
                )
            )
            conn.execute(
                text(
                    "INSERT INTO research_history (id, query, mode, status, "
                    "created_at) VALUES ('r1', 'q', 'quick', 'completed', "
                    "'2026-01-01T00:00:00')"
                )
            )

        # Stamp at 0009 (main's settings-only fetch_mode migration) so
        # 0010 (chat tables) is the next forward step we exercise. 0010
        # is the migration that actually ADD COLUMNs onto our hand-built
        # research_history shape.
        stamp_database(engine, "0009")
        run_migrations(engine, target="head")

        with engine.connect() as conn:
            row = conn.execute(
                text(
                    "SELECT step_count, chat_session_id "
                    "FROM research_history WHERE id = 'r1'"
                )
            ).first()
            assert row is not None
            # Relies on 0010 using server_default="0" (SQL-side, applied by
            # SQLite ADD COLUMN at DDL time). If a future refactor switches
            # to Python-side default=0, this assertion fails for the pre-
            # existing 'r1' row — exactly the regression we want to catch.
            assert row.step_count == 0
            assert row.chat_session_id is None

    def test_wrong_existing_fk_action_fails_without_duplicate(self, tmp_path):
        """An unsupported FK variant must fail instead of adding a second FK."""
        engine = create_engine(f"sqlite:///{tmp_path}/wrong_fk_action.db")

        @event.listens_for(engine, "connect")
        def _enable_fk(dbapi_connection, _):
            dbapi_connection.execute("PRAGMA foreign_keys = ON")

        try:
            _create_research_history_with_fk(
                engine, constraint_name="wrong_action", ondelete="CASCADE"
            )

            stamp_database(engine, "0009")
            with pytest.raises(RuntimeError, match="expected SET NULL"):
                run_migrations(engine, target="0010")

            with engine.connect() as conn:
                rows = list(
                    conn.execute(
                        text("PRAGMA foreign_key_list(research_history)")
                    ).mappings()
                )

            assert len(rows) == 1
            assert rows[0]["on_delete"] == "CASCADE"
            assert inspect(engine).has_table("chat_sessions") is False
            with engine.connect() as conn:
                assert (
                    conn.exec_driver_sql("PRAGMA foreign_keys").scalar_one()
                    == 1
                )
        finally:
            engine.dispose()

    def test_unnamed_fk_to_wrong_table_fails_before_chat_ddl(self, tmp_path):
        """An unnamed FK on the target column must still match endpoints."""
        engine = create_engine(f"sqlite:///{tmp_path}/wrong_unnamed_fk.db")
        try:
            _create_research_history_with_fk(
                engine,
                constraint_name=None,
                referred_table="other_sessions",
            )
            stamp_database(engine, "0009")

            with pytest.raises(RuntimeError, match="wrong endpoints"):
                run_migrations(engine, target="0010")

            assert inspect(engine).has_table("chat_sessions") is False
        finally:
            engine.dispose()

    def test_duplicate_equivalent_fks_fail_before_reflection_warning(
        self, tmp_path
    ):
        """A partial prior upgrade must not hide a duplicate FK."""
        engine = create_engine(f"sqlite:///{tmp_path}/duplicate_fks.db")
        try:
            with engine.begin() as conn:
                conn.execute(
                    text(
                        "CREATE TABLE research_history ("
                        "id TEXT PRIMARY KEY, "
                        "query TEXT NOT NULL, "
                        "mode TEXT NOT NULL, "
                        "status TEXT NOT NULL, "
                        "created_at TEXT NOT NULL, "
                        "chat_session_id TEXT, "
                        "FOREIGN KEY(chat_session_id) "
                        "REFERENCES chat_sessions(id) ON DELETE SET NULL, "
                        "CONSTRAINT fk_research_history_chat_session_id "
                        "FOREIGN KEY(chat_session_id) "
                        "REFERENCES chat_sessions(id) ON DELETE SET NULL"
                        ")"
                    )
                )
            stamp_database(engine, "0009")

            with warnings.catch_warnings():
                warnings.simplefilter("error", SAWarning)
                with pytest.raises(RuntimeError, match="more than one"):
                    run_migrations(engine, target="0010")

            assert inspect(engine).has_table("chat_sessions") is False
        finally:
            engine.dispose()

    def test_correct_named_fk_is_accepted(self, tmp_path):
        engine = create_engine(f"sqlite:///{tmp_path}/correct_named_fk.db")
        try:
            _create_research_history_with_fk(engine)
            stamp_database(engine, "0009")

            run_migrations(engine, target="0010")

            assert inspect(engine).has_table("chat_sessions")
        finally:
            engine.dispose()

    def test_fk_restore_failure_invalidates_connection_without_masking(
        self, tmp_path
    ):
        engine = create_engine(f"sqlite:///{tmp_path}/fk_restore_failure.db")

        @event.listens_for(engine, "connect")
        def _enable_fk(dbapi_connection, _):
            dbapi_connection.execute("PRAGMA foreign_keys = ON")

        try:
            _create_research_history_with_fk(
                engine, constraint_name="wrong_action", ondelete="CASCADE"
            )
            stamp_database(engine, "0009")
            restore_attempted = False

            @event.listens_for(engine, "before_cursor_execute")
            def _fail_fk_restore(
                _conn, _cursor, statement, _parameters, _context, _executemany
            ):
                nonlocal restore_attempted
                if statement.strip().upper() == "PRAGMA FOREIGN_KEYS = ON":
                    restore_attempted = True
                    raise RuntimeError("injected FK restore failure")

            with pytest.raises(RuntimeError, match="expected SET NULL"):
                run_migrations(engine, target="0010")

            assert restore_attempted is True
            with engine.connect() as conn:
                assert (
                    conn.exec_driver_sql("PRAGMA foreign_keys").scalar_one()
                    == 1
                )
        finally:
            engine.dispose()

    @pytest.mark.parametrize("restore_failure", ["raises", "ignored"])
    def test_successful_upgrade_restore_failure_invalidates_connection(
        self, tmp_path, restore_failure
    ):
        """A committed upgrade must not pool an FK-disabled connection."""
        engine = create_engine(
            f"sqlite:///{tmp_path}/successful_restore_{restore_failure}.db"
        )

        @event.listens_for(engine, "connect")
        def _enable_fk(dbapi_connection, _):
            dbapi_connection.execute("PRAGMA foreign_keys = ON")

        @event.listens_for(engine, "before_cursor_execute")
        def _break_final_fk_restore(
            _conn, cursor, statement, _parameters, _context, _executemany
        ):
            if statement.strip().upper() != "PRAGMA FOREIGN_KEYS = ON":
                return
            if restore_failure == "raises":
                raise RuntimeError("injected successful FK restore failure")
            # SQLite silently ignores the following PRAGMA while this
            # DBAPI-level transaction is active.
            cursor.execute("BEGIN")

        expected = (
            "injected successful FK restore failure"
            if restore_failure == "raises"
            else "foreign-key enforcement remained disabled"
        )
        try:
            with pytest.raises(RuntimeError, match=expected):
                run_migrations(engine)

            # The schema committed before restoration failed. A retry therefore
            # takes the already-at-head fast path and cannot repair a pooled
            # handle; correctness depends on invalidating it above.
            run_migrations(engine)
            with engine.connect() as conn:
                assert (
                    conn.exec_driver_sql("PRAGMA foreign_keys").scalar_one()
                    == 1
                )
        finally:
            engine.dispose()

    def test_keyboard_interrupt_restores_fk_before_pool_return(
        self, tmp_path, monkeypatch
    ):
        engine = create_engine(f"sqlite:///{tmp_path}/fk_interrupt.db")

        @event.listens_for(engine, "connect")
        def _enable_fk(dbapi_connection, _):
            dbapi_connection.execute("PRAGMA foreign_keys = ON")

        def _interrupt_upgrade(*_args, **_kwargs):
            raise KeyboardInterrupt

        monkeypatch.setattr(command, "upgrade", _interrupt_upgrade)
        try:
            with pytest.raises(KeyboardInterrupt):
                run_migrations(engine)

            with engine.connect() as conn:
                assert (
                    conn.exec_driver_sql("PRAGMA foreign_keys").scalar_one()
                    == 1
                )
        finally:
            engine.dispose()

    def test_cancellation_during_cleanup_is_not_masked(
        self, tmp_path, monkeypatch
    ):
        runner = import_module("local_deep_research.database.alembic_runner")
        engine = create_engine(f"sqlite:///{tmp_path}/cleanup_interrupt.db")

        @event.listens_for(engine, "connect")
        def _enable_fk(dbapi_connection, _):
            dbapi_connection.execute("PRAGMA foreign_keys = ON")

        def _fail_upgrade(*_args, **_kwargs):
            raise RuntimeError("injected migration failure")

        def _interrupt_cleanup(conn):
            conn.invalidate()
            raise KeyboardInterrupt

        monkeypatch.setattr(command, "upgrade", _fail_upgrade)
        monkeypatch.setattr(
            runner, "_restore_fk_after_migration", _interrupt_cleanup
        )
        try:
            with pytest.raises(KeyboardInterrupt):
                run_migrations(engine)

            with engine.connect() as conn:
                assert (
                    conn.exec_driver_sql("PRAGMA foreign_keys").scalar_one()
                    == 1
                )
        finally:
            engine.dispose()

    def test_real_cleanup_interrupt_invalidates_connection(
        self, tmp_path, monkeypatch
    ):
        """Cancellation inside the real restore helper cannot pool FK OFF."""
        engine = create_engine(
            f"sqlite:///{tmp_path}/real_cleanup_interrupt.db"
        )

        @event.listens_for(engine, "connect")
        def _enable_fk(dbapi_connection, _):
            dbapi_connection.execute("PRAGMA foreign_keys = ON")

        def _fail_upgrade(*_args, **_kwargs):
            raise RuntimeError("injected migration failure")

        restore_interrupted = False

        @event.listens_for(engine, "before_cursor_execute")
        def _interrupt_real_restore(
            _conn, _cursor, statement, _parameters, _context, _executemany
        ):
            nonlocal restore_interrupted
            if (
                not restore_interrupted
                and statement.strip().upper() == "PRAGMA FOREIGN_KEYS = ON"
            ):
                restore_interrupted = True
                raise KeyboardInterrupt

        monkeypatch.setattr(command, "upgrade", _fail_upgrade)
        try:
            with pytest.raises(KeyboardInterrupt):
                run_migrations(engine)

            assert restore_interrupted is True
            with engine.connect() as conn:
                assert (
                    conn.exec_driver_sql("PRAGMA foreign_keys").scalar_one()
                    == 1
                )
        finally:
            engine.dispose()

    def test_disable_fk_aborts_if_explicit_begin_ignores_toggle(
        self, tmp_path, monkeypatch
    ):
        """Never call Alembic when SQLite did not accept PRAGMA FK OFF."""
        engine = create_engine(f"sqlite:///{tmp_path}/explicit_begin.db")

        @event.listens_for(engine, "connect")
        def _configure_explicit_begin(dbapi_connection, _):
            dbapi_connection.isolation_level = None
            dbapi_connection.execute("PRAGMA foreign_keys = ON")

        @event.listens_for(engine, "begin")
        def _begin_explicitly(conn):
            conn.exec_driver_sql("BEGIN")

        upgrade_called = False

        def _record_upgrade(*_args, **_kwargs):
            nonlocal upgrade_called
            upgrade_called = True

        monkeypatch.setattr(command, "upgrade", _record_upgrade)
        try:
            with pytest.raises(RuntimeError, match="remained enabled"):
                run_migrations(engine)

            assert upgrade_called is False
            with engine.connect() as conn:
                assert (
                    conn.exec_driver_sql("PRAGMA foreign_keys").scalar_one()
                    == 1
                )
        finally:
            engine.dispose()

    def test_disable_failure_restores_fk_before_pool_return(
        self, tmp_path, monkeypatch
    ):
        runner = import_module("local_deep_research.database.alembic_runner")
        engine = create_engine(f"sqlite:///{tmp_path}/fk_disable_failure.db")

        @event.listens_for(engine, "connect")
        def _enable_fk(dbapi_connection, _):
            dbapi_connection.execute("PRAGMA foreign_keys = ON")

        original_disable = runner._disable_fk_for_migration

        def _disable_then_fail(conn):
            original_disable(conn)
            raise RuntimeError("injected post-disable failure")

        monkeypatch.setattr(
            runner, "_disable_fk_for_migration", _disable_then_fail
        )
        try:
            with pytest.raises(RuntimeError, match="post-disable failure"):
                run_migrations(engine)

            with engine.connect() as conn:
                assert (
                    conn.exec_driver_sql("PRAGMA foreign_keys").scalar_one()
                    == 1
                )
        finally:
            engine.dispose()

    @pytest.mark.parametrize(
        ("local_column", "ondelete", "message"),
        [
            ("chat_session_id", "CASCADE", "expected SET NULL"),
            ("other_id", "SET NULL", "wrong endpoints"),
        ],
    )
    def test_expected_fk_name_does_not_bypass_signature_validation(
        self, tmp_path, local_column, ondelete, message
    ):
        engine = create_engine(
            f"sqlite:///{tmp_path}/named_fk_mismatch_{local_column}.db"
        )
        try:
            _create_research_history_with_fk(
                engine,
                local_column=local_column,
                ondelete=ondelete,
            )
            stamp_database(engine, "0009")

            with pytest.raises(RuntimeError, match=message):
                run_migrations(engine, target="0010")

            assert inspect(engine).has_table("chat_sessions") is False
        finally:
            engine.dispose()


# ---------------------------------------------------------------------------
# Partial unique index: at-most-one-in-progress per chat_session_id
#
# Originally lived in a separate migration 0011 with a separate test file;
# folded into 0010 to keep the chat schema landing in a single migration
# (chat is unreleased; one migration is easier to maintain than two).
# ---------------------------------------------------------------------------


@pytest.fixture
def fully_migrated_engine_with_fk(tmp_path):
    """Fully migrated SQLite engine with FK enforcement on every connection.

    Required for the partial-unique-index tests because they depend on
    SQLite enforcing the constraint at INSERT time, which only happens
    when ``PRAGMA foreign_keys = ON`` is active.
    """
    db_path = tmp_path / "0010_partial_unique_test.db"
    engine = create_engine(f"sqlite:///{db_path}")

    @event.listens_for(engine, "connect")
    def _enable_fk(dbapi_connection, _):
        dbapi_connection.execute("PRAGMA foreign_keys = ON")

    run_migrations(engine, target="head")
    yield engine
    engine.dispose()


def _seed_chat_session(conn, sid):
    conn.execute(
        text(
            "INSERT INTO chat_sessions "
            "(id, status, message_count, created_at) "
            "VALUES (:id, 'active', 0, '2026-01-01T00:00:00')"
        ),
        {"id": sid},
    )


def _insert_research(conn, *, rid, sid, status):
    conn.execute(
        text(
            "INSERT INTO research_history "
            "(id, query, mode, status, created_at, chat_session_id) "
            "VALUES (:rid, 'q', 'quick', :status, "
            "'2026-01-01T00:00:00', :sid)"
        ),
        {"rid": rid, "sid": sid, "status": status},
    )


class TestPartialUniqueInProgressIndex:
    """The partial unique index closes a SELECT-then-INSERT race in
    chat/routes.py. Verify the constraint actually fires at the DB."""

    def test_partial_unique_index_exists_after_upgrade(
        self, fully_migrated_engine_with_fk
    ):
        inspector = inspect(fully_migrated_engine_with_fk)
        indexes = {
            idx["name"]: idx
            for idx in inspector.get_indexes("research_history")
        }
        assert _PARTIAL_UNIQUE_INDEX_NAME in indexes
        idx = indexes[_PARTIAL_UNIQUE_INDEX_NAME]
        # SQLAlchemy's SQLite inspector returns 1 / 0 rather than True /
        # False for the unique flag, so compare on truthiness.
        assert bool(idx["unique"])
        assert idx["column_names"] == ["chat_session_id"]

    def test_second_in_progress_for_same_chat_session_blocked(
        self, fully_migrated_engine_with_fk
    ):
        engine = fully_migrated_engine_with_fk
        with engine.begin() as conn:
            _seed_chat_session(conn, "s1")
            _insert_research(conn, rid="r1", sid="s1", status="in_progress")

        with engine.connect() as conn:
            with pytest.raises(IntegrityError):
                with conn.begin():
                    _insert_research(
                        conn, rid="r2", sid="s1", status="in_progress"
                    )

    def test_completed_runs_for_same_chat_session_allowed(
        self, fully_migrated_engine_with_fk
    ):
        """Partial: only in_progress rows are unique; completed history
        of arbitrarily many runs per chat session must remain allowed."""
        engine = fully_migrated_engine_with_fk
        with engine.begin() as conn:
            _seed_chat_session(conn, "s1")
            _insert_research(conn, rid="r1", sid="s1", status="completed")
            _insert_research(conn, rid="r2", sid="s1", status="completed")
            _insert_research(conn, rid="r3", sid="s1", status="failed")

        with engine.connect() as conn:
            count = conn.execute(
                text(
                    "SELECT COUNT(*) FROM research_history "
                    "WHERE chat_session_id='s1'"
                )
            ).scalar()
            assert count == 3

    def test_in_progress_with_null_chat_session_id_unconstrained(
        self, fully_migrated_engine_with_fk
    ):
        """Partial: NULL chat_session_id rows must be unconstrained so
        non-chat research (news, scheduler, direct API) is unaffected."""
        engine = fully_migrated_engine_with_fk
        with engine.begin() as conn:
            conn.execute(
                text(
                    "INSERT INTO research_history "
                    "(id, query, mode, status, created_at) "
                    "VALUES (:rid, 'q', 'quick', 'in_progress', "
                    "'2026-01-01T00:00:00')"
                ),
                {"rid": "r1"},
            )
            conn.execute(
                text(
                    "INSERT INTO research_history "
                    "(id, query, mode, status, created_at) "
                    "VALUES (:rid, 'q', 'quick', 'in_progress', "
                    "'2026-01-01T00:00:00')"
                ),
                {"rid": "r2"},
            )

        with engine.connect() as conn:
            count = conn.execute(
                text(
                    "SELECT COUNT(*) FROM research_history "
                    "WHERE chat_session_id IS NULL"
                )
            ).scalar()
            assert count == 2

    def test_completing_a_run_releases_the_in_progress_slot(
        self, fully_migrated_engine_with_fk
    ):
        """After r1 transitions away from in_progress, r2 must be able
        to claim the slot."""
        engine = fully_migrated_engine_with_fk
        with engine.begin() as conn:
            _seed_chat_session(conn, "s1")
            _insert_research(conn, rid="r1", sid="s1", status="in_progress")

        with engine.begin() as conn:
            conn.execute(
                text(
                    "UPDATE research_history "
                    "SET status='completed' WHERE id='r1'"
                )
            )

        with engine.begin() as conn:
            _insert_research(conn, rid="r2", sid="s1", status="in_progress")

        with engine.connect() as conn:
            count = conn.execute(
                text(
                    "SELECT COUNT(*) FROM research_history "
                    "WHERE chat_session_id='s1' AND status='in_progress'"
                )
            ).scalar()
            assert count == 1