"""
Tests the pattern-based semantic description generation in
`schema.registry` directly against synthetic `TableInfo` objects (no live
MySQL needed), proving tables/columns get useful descriptions instead of the
old generic "X field on Y" placeholder.
"""
from __future__ import annotations

from app.schema.loader import ColumnInfo, ForeignKeyInfo, TableInfo
from app.schema.registry import _build_snapshot
from app.schema.relationship_mapper import build_relationship_graph


def _raw_tables() -> dict[str, TableInfo]:
    employees = TableInfo(
        name="employees",
        columns={
            "id": ColumnInfo(name="id", type="INTEGER", nullable=False, primary_key=True),
            "name": ColumnInfo(name="name", type="VARCHAR(255)", nullable=False),
            "email": ColumnInfo(name="email", type="VARCHAR(255)", nullable=True),
            "salary": ColumnInfo(name="salary", type="DECIMAL(10,2)", nullable=True),
            "department_id": ColumnInfo(name="department_id", type="INTEGER", nullable=True),
            "created_at": ColumnInfo(name="created_at", type="DATETIME", nullable=False),
            "status": ColumnInfo(name="status", type="VARCHAR(20)", nullable=True),
        },
        primary_keys=["id"],
        foreign_keys=[ForeignKeyInfo(constrained_columns=["department_id"], referred_table="departments", referred_columns=["id"])],
    )
    departments = TableInfo(
        name="departments",
        columns={
            "id": ColumnInfo(name="id", type="INTEGER", nullable=False, primary_key=True),
            "name": ColumnInfo(name="name", type="VARCHAR(255)", nullable=False),
        },
        primary_keys=["id"],
        foreign_keys=[],
    )
    return {"employees": employees, "departments": departments}


def test_primary_key_gets_identifier_description():
    raw_tables = _raw_tables()
    relationships = build_relationship_graph(raw_tables)
    snapshot = _build_snapshot(raw_tables, relationships)
    assert "identifier" in snapshot.tables["employees"].columns["id"].description.lower()


def test_foreign_key_column_describes_the_target_table():
    raw_tables = _raw_tables()
    relationships = build_relationship_graph(raw_tables)
    snapshot = _build_snapshot(raw_tables, relationships)
    description = snapshot.tables["employees"].columns["department_id"].description
    assert "departments" in description.lower()


def test_email_column_gets_semantic_description():
    raw_tables = _raw_tables()
    relationships = build_relationship_graph(raw_tables)
    snapshot = _build_snapshot(raw_tables, relationships)
    assert "email" in snapshot.tables["employees"].columns["email"].description.lower()


def test_salary_column_gets_semantic_description():
    raw_tables = _raw_tables()
    relationships = build_relationship_graph(raw_tables)
    snapshot = _build_snapshot(raw_tables, relationships)
    description = snapshot.tables["employees"].columns["salary"].description.lower()
    assert "salary" in description or "compensation" in description


def test_created_at_column_gets_timestamp_description():
    raw_tables = _raw_tables()
    relationships = build_relationship_graph(raw_tables)
    snapshot = _build_snapshot(raw_tables, relationships)
    description = snapshot.tables["employees"].columns["created_at"].description.lower()
    assert "created" in description


def test_status_column_gets_semantic_description():
    raw_tables = _raw_tables()
    relationships = build_relationship_graph(raw_tables)
    snapshot = _build_snapshot(raw_tables, relationships)
    assert "status" in snapshot.tables["employees"].columns["status"].description.lower()


def test_table_description_mentions_outgoing_relationship():
    raw_tables = _raw_tables()
    relationships = build_relationship_graph(raw_tables)
    snapshot = _build_snapshot(raw_tables, relationships)
    assert "departments" in snapshot.tables["employees"].description.lower()


def test_table_description_mentions_incoming_relationship():
    raw_tables = _raw_tables()
    relationships = build_relationship_graph(raw_tables)
    snapshot = _build_snapshot(raw_tables, relationships)
    assert "employees" in snapshot.tables["departments"].description.lower()


def test_unrecognized_column_falls_back_to_humanized_name():
    raw_tables = _raw_tables()
    raw_tables["employees"].columns["custom_flag_xyz"] = ColumnInfo(
        name="custom_flag_xyz", type="VARCHAR(10)", nullable=True
    )
    relationships = build_relationship_graph(raw_tables)
    snapshot = _build_snapshot(raw_tables, relationships)
    description = snapshot.tables["employees"].columns["custom_flag_xyz"].description
    assert "Custom flag xyz" in description


def test_platform_scope_contains_primary_and_directly_related_tables():
    raw_tables = _raw_tables()
    relationships = build_relationship_graph(raw_tables)
    snapshot = _build_snapshot(raw_tables, relationships)

    platform = snapshot.for_platform("employees")

    assert set(platform.tables) == {"employees", "departments"}
    assert len(platform.relationships) == 1


def test_platform_scope_rejects_unknown_table():
    raw_tables = _raw_tables()
    relationships = build_relationship_graph(raw_tables)
    snapshot = _build_snapshot(raw_tables, relationships)

    try:
        snapshot.for_platform("does_not_exist")
    except KeyError:
        pass
    else:
        raise AssertionError("Unknown platform table should raise KeyError")


def test_specific_numeric_column_descriptions_do_not_get_overridden_by_total_substrings():
    from app.schema.registry import _describe_column
    assert _describe_column("subtotal", "bills", False, None) == "Subtotal amount before adjustments"
    assert _describe_column("tax_total", "bills", False, None) == "Tax amount"
    assert _describe_column("discount_total", "bills", False, None) == "Discount amount"
    assert _describe_column("zoho_created_time", "customers", False, None) == "Timestamp when the record was created"
    assert _describe_column("zoho_last_modified_time", "customers", False, None) == "Timestamp when the record was last modified"