"""Tests for FK density enforcer."""
from __future__ import annotations

import copy

from pipeline.fk_density_enforcer import enforce_fk_density


def _base_schema() -> dict:
    """Schema with a business table + cfg table but no FK between them."""
    return {
        "schema_name": "demo",
        "menus": [
            {
                "menu_id": 1, "menu_name": "Ops", "sequence_number": 1,
                "description": "ops",
                "submenus": [
                    {
                        "submenu_id": 101, "submenu_name": "Orders",
                        "sequence_number": 1, "description": "orders",
                        "tables": [
                            {
                                "table_name": "orders",
                                "comment": "Order header",
                                "columns": [
                                    {"name": "id", "type": "SERIAL",
                                     "constraints": "PRIMARY KEY"},
                                    {"name": "status_id", "type": "INT",
                                     "constraints": "NOT NULL"},
                                    {"name": "total", "type": "NUMERIC(10,2)"},
                                ],
                                "foreign_keys": [],
                            }
                        ],
                    }
                ],
            },
            {
                "menu_id": 18, "menu_name": "Configuration", "sequence_number": 18,
                "description": "config",
                "submenus": [
                    {
                        "submenu_id": 1801, "submenu_name": "Order Status",
                        "sequence_number": 1, "description": "x",
                        "tables": [
                            {
                                "table_name": "cfg_order_status",
                                "comment": "lookup",
                                "columns": [
                                    {"name": "cfg_order_status_id", "type": "SERIAL",
                                     "constraints": "PRIMARY KEY"},
                                    {"name": "code", "type": "VARCHAR(50)"},
                                ],
                                "foreign_keys": [],
                            }
                        ],
                    }
                ],
            },
        ],
    }


def test_injects_fk_for_status_id_column():
    s = _base_schema()
    enforce_fk_density(s)
    orders = s["menus"][0]["submenus"][0]["tables"][0]
    fks = orders["foreign_keys"]
    assert any(
        fk["column"] == "status_id"
        and "cfg_order_status(cfg_order_status_id)" in fk["references"]
        for fk in fks
    )
    stats = s["_fk_density_enforcer"]
    assert stats["fks_injected"] == 1


def test_idempotent():
    s = _base_schema()
    enforce_fk_density(s)
    snap = copy.deepcopy(s)
    enforce_fk_density(s)
    # Second pass: no new FKs
    assert s == {**snap, "_fk_density_enforcer": s["_fk_density_enforcer"]}
    assert s["_fk_density_enforcer"]["fks_already_present"] >= 1


def test_skips_when_no_cfg_tables():
    s = _base_schema()
    # Remove all cfg tables
    s["menus"] = [s["menus"][0]]
    enforce_fk_density(s)
    stats = s["_fk_density_enforcer"]
    assert stats["fks_injected"] == 0
    assert stats["cfg_tables_total"] == 0


def test_skips_columns_with_existing_fk():
    s = _base_schema()
    # Pre-add a FK on status_id
    s["menus"][0]["submenus"][0]["tables"][0]["foreign_keys"].append({
        "column": "status_id",
        "references": "demo.cfg_order_status(cfg_order_status_id)",
    })
    enforce_fk_density(s)
    stats = s["_fk_density_enforcer"]
    assert stats["fks_injected"] == 0
    assert stats["fks_already_present"] >= 1


def test_skips_cfg_tables_themselves():
    """cfg_* tables shouldn't get FKs injected — those are the targets, not sources."""
    s = _base_schema()
    enforce_fk_density(s)
    cfg_table = s["menus"][1]["submenus"][0]["tables"][0]
    assert not cfg_table.get("foreign_keys"), \
        "cfg_* table should not have FKs injected by this enforcer"


def test_handles_multiple_cfg_targets():
    """When a business table has multiple status/type columns, all get wired."""
    s = _base_schema()
    # Add a 'priority_id' column + cfg_priority table
    s["menus"][0]["submenus"][0]["tables"][0]["columns"].append({
        "name": "priority_id", "type": "INT",
    })
    s["menus"][1]["submenus"][0]["tables"].append({
        "table_name": "cfg_priority",
        "comment": "priority lookup",
        "columns": [
            {"name": "cfg_priority_id", "type": "SERIAL",
             "constraints": "PRIMARY KEY"},
        ],
        "foreign_keys": [],
    })
    enforce_fk_density(s)
    fks = s["menus"][0]["submenus"][0]["tables"][0]["foreign_keys"]
    fk_cols = {fk["column"] for fk in fks}
    assert "status_id" in fk_cols
    assert "priority_id" in fk_cols
    assert s["_fk_density_enforcer"]["fks_injected"] == 2


def test_matches_compound_name_pattern():
    """orders.order_status_id should match cfg_order_status."""
    s = _base_schema()
    s["menus"][0]["submenus"][0]["tables"][0]["columns"][1]["name"] = "order_status_id"
    enforce_fk_density(s)
    fks = s["menus"][0]["submenus"][0]["tables"][0]["foreign_keys"]
    assert any(fk["column"] == "order_status_id" for fk in fks)


def test_per_table_stats_recorded():
    s = _base_schema()
    enforce_fk_density(s)
    stats = s["_fk_density_enforcer"]
    assert "by_business_table" in stats
    assert stats["by_business_table"].get("orders") == 1


def test_skips_non_int_columns():
    """status as TEXT column shouldn't get FK injection (cfg PKs are INT)."""
    s = _base_schema()
    s["menus"][0]["submenus"][0]["tables"][0]["columns"][1]["type"] = "TEXT"
    enforce_fk_density(s)
    fks = s["menus"][0]["submenus"][0]["tables"][0]["foreign_keys"]
    # TEXT-typed column is skipped
    assert not any(fk["column"] == "status_id" for fk in fks)