"""Unit tests for the Synapse DDL — fresh database and migration in one path.

The bug this file exists for: an index over a column the migration adds used to
live in `_DDL`, which runs *first*. On a database that already had the table
(any real deployment) the batch died with `column "doc" does not exist` before
the ALTER ever ran, and the whole synapse schema stayed on the previous shape
while the app started anyway.
"""

import re

from navi.synapse._ddl import _DDL, _MIGRATE, ensure_tables
from tests.conftest_factory import FakeConnection, FakePool


def _statements(sql: str) -> str:
    return re.sub(r"--[^\n]*", "", sql)


def test_migration_creates_every_column_the_ddl_declares():
    """A column added to the CREATE TABLE must be added to the ALTER batch too,
    or existing databases never get it (IF NOT EXISTS makes one batch serve both)."""
    ddl, migrate = _statements(_DDL), _statements(_MIGRATE)
    for table, columns in (
        ("synapse_settings", ["dispatcher_instructions", "reaction_session_ttl_minutes"]),
        ("synapse_instruction_versions", ["doc"]),
    ):
        block = re.search(rf"CREATE TABLE IF NOT EXISTS {table} \((.*?)\n\);", ddl, re.DOTALL)
        assert block, f"{table} is no longer declared in _DDL — update this test"
        for column in columns:
            assert column in block.group(1), f"{table}.{column} missing from the CREATE TABLE"
            assert f"ALTER TABLE {table} ADD COLUMN IF NOT EXISTS {column}" in migrate


def test_no_index_the_migration_needs_is_created_before_it():
    """_DDL runs before _MIGRATE: an index on a migrated column there fails the
    batch on an existing database (the new column does not exist yet)."""
    migrated_columns = set(re.findall(r"ADD COLUMN IF NOT EXISTS (\w+)", _MIGRATE))
    assert migrated_columns, "no columns found in the migration"
    for match in re.finditer(r"CREATE INDEX IF NOT EXISTS \w+\s+ON (\w+) \(([^)]*)\)", _DDL):
        columns = {part.strip() for part in match.group(2).split(",")}
        assert not (columns & migrated_columns), (
            f"_DDL creates an index on {match.group(1)} over migrated column(s) "
            f"{sorted(columns & migrated_columns)} — move it into _MIGRATE"
        )


async def test_ensure_tables_runs_ddl_then_migration():
    conn = FakeConnection()
    await ensure_tables(FakePool(conn))
    executed = [call[1] for call in conn.calls if call[0] == "execute"]
    assert executed == [_DDL, _MIGRATE]