"""Static check: every FK in Base.metadata points at a real table+column.

Catches the R4 class of bug — ``ResearchStrategy.research_id`` declared
``ForeignKey("research.id")`` while the live code wrote UUID strings from
``research_history``. PRAGMA-OFF hid this for ~10 months; PRAGMA-ON in
v1.6.0 made every save fail.
"""

from __future__ import annotations

from local_deep_research.database.models import Base


def test_every_fk_target_table_and_column_exists():
    tables = {t.name: t for t in Base.metadata.sorted_tables}
    problems: list[str] = []
    for table in Base.metadata.sorted_tables:
        for fk in table.foreign_keys:
            target_table_name = fk.column.table.name
            target_col_name = fk.column.name
            target = tables.get(target_table_name)
            if target is None:
                problems.append(
                    f"{table.name}.{fk.parent.name} -> "
                    f"{target_table_name}.{target_col_name}: target table missing"
                )
                continue
            if target_col_name not in target.columns:
                problems.append(
                    f"{table.name}.{fk.parent.name} -> "
                    f"{target_table_name}.{target_col_name}: target column missing"
                )
                continue
            target_col = target.columns[target_col_name]
            if str(fk.parent.type) != str(target_col.type):
                problems.append(
                    f"{table.name}.{fk.parent.name} ({fk.parent.type}) -> "
                    f"{target_table_name}.{target_col_name} ({target_col.type}): "
                    "type mismatch"
                )
    assert not problems, "FK declaration problems:\n  " + "\n  ".join(problems)