"""Tests for CREATE INDEX generator."""
from __future__ import annotations

from pipeline.index_generator import generate_indexes


def _schema(tables):
    return {
        "schema_name": "demo",
        "menus": [{
            "menu_id": 1, "menu_name": "Ops", "sequence_number": 1,
            "description": "ops",
            "submenus": [{
                "submenu_id": 101, "submenu_name": "x",
                "sequence_number": 1, "description": "x",
                "tables": tables,
            }],
        }],
    }


def test_emits_index_for_fk_columns():
    s = _schema([{
        "table_name": "orders",
        "comment": "x",
        "columns": [
            {"name": "id", "type": "SERIAL", "constraints": "PRIMARY KEY"},
            {"name": "customer_id", "type": "INT"},
        ],
        "foreign_keys": [
            {"column": "customer_id", "references": "demo.customers(id)"},
        ],
    }])
    sql = generate_indexes(s)
    assert "CREATE INDEX idx_orders_customer_id ON demo.orders(customer_id);" in sql


def test_emits_index_for_lookup_columns():
    s = _schema([{
        "table_name": "items",
        "comment": "x",
        "columns": [
            {"name": "id", "type": "SERIAL", "constraints": "PRIMARY KEY"},
            {"name": "sku", "type": "VARCHAR(50)"},
            {"name": "code", "type": "VARCHAR(20)"},
            {"name": "is_active", "type": "BOOLEAN"},
        ],
        "foreign_keys": [],
    }])
    sql = generate_indexes(s)
    assert "idx_items_sku" in sql
    assert "idx_items_code" in sql
    assert "idx_items_is_active" in sql


def test_skips_pk_columns():
    s = _schema([{
        "table_name": "orders",
        "comment": "x",
        "columns": [{"name": "id", "type": "SERIAL", "constraints": "PRIMARY KEY"}],
        "foreign_keys": [],
    }])
    sql = generate_indexes(s)
    assert "idx_orders_id" not in sql, "PK already auto-indexed"


def test_skips_menu_submenu():
    s = _schema([
        {"table_name": "menu", "comment": "x", "columns": [{"name": "id", "type": "SERIAL"}], "foreign_keys": []},
        {"table_name": "submenu", "comment": "x", "columns": [{"name": "id", "type": "SERIAL"}], "foreign_keys": []},
    ])
    sql = generate_indexes(s)
    assert "menu" not in sql or sql == ""


def test_returns_empty_when_no_index_candidates():
    s = _schema([{
        "table_name": "trivial",
        "comment": "x",
        "columns": [{"name": "id", "type": "SERIAL", "constraints": "PRIMARY KEY"}],
        "foreign_keys": [],
    }])
    sql = generate_indexes(s)
    # Only PK, no other columns to index — block should be empty
    assert "CREATE INDEX" not in sql


def test_idempotent_output():
    s = _schema([{
        "table_name": "items",
        "comment": "x",
        "columns": [
            {"name": "id", "type": "SERIAL", "constraints": "PRIMARY KEY"},
            {"name": "sku", "type": "VARCHAR(50)"},
        ],
        "foreign_keys": [],
    }])
    sql1 = generate_indexes(s)
    sql2 = generate_indexes(s)
    assert sql1 == sql2


def test_inline_references_treated_as_fk():
    """Column with inline REFERENCES gets indexed."""
    s = _schema([{
        "table_name": "orders",
        "comment": "x",
        "columns": [
            {"name": "id", "type": "SERIAL", "constraints": "PRIMARY KEY"},
            {"name": "status_id", "type": "INT",
             "constraints": "NOT NULL REFERENCES demo.cfg_order_status(id)"},
        ],
        "foreign_keys": [],
    }])
    sql = generate_indexes(s)
    assert "idx_orders_status_id" in sql


def test_stats_recorded():
    s = _schema([{
        "table_name": "orders",
        "comment": "x",
        "columns": [
            {"name": "id", "type": "SERIAL", "constraints": "PRIMARY KEY"},
            {"name": "customer_id", "type": "INT"},
            {"name": "order_date", "type": "DATE"},
            {"name": "is_active", "type": "BOOLEAN"},
        ],
        "foreign_keys": [{"column": "customer_id", "references": "demo.customers(id)"}],
    }])
    generate_indexes(s)
    stats = s["_index_generator"]
    assert stats["tables_processed"] == 1
    assert stats["indexes_emitted"] >= 3
    assert stats["fk_indexes"] >= 1