"""Unit tests for the memory DDL — the only path that reaches an existing database.

There is no migration framework here: `MemoryStore._get_pool()` runs `_build_ddl()`
on every process start, and on a database that already has the table only the
`ADD COLUMN IF NOT EXISTS` statements do anything. A column that is declared in the
CREATE TABLE but forgotten in that block exists on a fresh database and nowhere else.
"""

import re

from navi.memory._ddl import _build_ddl


def _facts_table(ddl: list[str]) -> str:
    for stmt in ddl:
        block = re.search(r"CREATE TABLE IF NOT EXISTS memory_facts \((.*?)\n\s*\)", stmt, re.DOTALL)
        if block:
            return block.group(1)
    raise AssertionError("memory_facts is no longer declared in _build_ddl")


def test_merged_into_is_migrated_and_declared():
    for pgvector in (False, True):
        ddl = _build_ddl(pgvector)
        assert any(
            "ALTER TABLE memory_facts ADD COLUMN IF NOT EXISTS merged_into" in stmt
            for stmt in ddl
        ), f"The migration never adds merged_into (pgvector={pgvector})"
        assert "merged_into" in _facts_table(ddl)


def test_no_index_on_the_migrated_column():
    """Almost every read filters `merged_into IS NULL`, so an index over it cannot be
    selective — and one placed in the CREATE list would run before the ALTER that
    adds the column, failing the whole batch on an existing database.
    """
    for pgvector in (False, True):
        for stmt in _build_ddl(pgvector):
            if "CREATE INDEX" in stmt:
                assert "merged_into" not in stmt, f"an index mentions merged_into: {stmt[:80]}"


def test_memory_chunks_embedding_is_migrated_before_its_index():
    """A database that first ran without pgvector holds a `memory_chunks` table
    with no `embedding` column; CREATE TABLE IF NOT EXISTS never adds it, and
    every chunk INSERT then fails on the missing column. The ALTER has to come
    before the HNSW index, which would otherwise fail on the same table.
    """
    ddl = _build_ddl(True)
    alter = next(
        (i for i, stmt in enumerate(ddl)
         if "ALTER TABLE memory_chunks ADD COLUMN IF NOT EXISTS embedding" in stmt),
        None,
    )
    index = next(
        (i for i, stmt in enumerate(ddl) if "idx_memory_chunks_embedding" in stmt),
        None,
    )
    assert alter is not None, "the chunks embedding column is never migrated"
    assert index is not None and alter < index
    assert not any(
        "ALTER TABLE memory_chunks ADD COLUMN" in stmt for stmt in _build_ddl(False)
    ), "a database without pgvector must not get a vector column"