import pytest

from app.core.config import Settings
from app.core.exceptions import AmbiguousRequestError, QueryPlanValidationError, UnsupportedRequestError
from app.planning.models import FilterSpec, RawQueryPlan
from app.planning.operators import FilterOperator, Operation
from app.validation.query_validator import validate_query_plan


@pytest.fixture
def settings() -> Settings:
    return Settings(mysql_password="x")


def test_valid_simple_list_plan(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(
        operation=Operation.LIST,
        entity="employees",
        fields=["name", "salary"],
        filters=[FilterSpec(field="department", operator=FilterOperator.EQUALS, value="IT")],
        limit=50,
    )
    plan = validate_query_plan(raw, fake_snapshot, fake_vocab, settings)
    assert plan.primary_table == "employees"
    assert {f.column for f in plan.select_fields} == {"name", "salary"}
    assert plan.filters[0][0].column == "department"
    assert plan.limit == 50


def test_unknown_entity_raises(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(operation=Operation.LIST, entity="spaceships", fields=[])
    with pytest.raises(QueryPlanValidationError):
        validate_query_plan(raw, fake_snapshot, fake_vocab, settings)


def test_unknown_column_raises(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(operation=Operation.LIST, entity="employees", fields=["warp_speed"])
    with pytest.raises(QueryPlanValidationError):
        validate_query_plan(raw, fake_snapshot, fake_vocab, settings)


def test_operator_type_mismatch_raises(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(
        operation=Operation.LIST,
        entity="employees",
        fields=["name"],
        filters=[FilterSpec(field="name", operator=FilterOperator.GREATER_THAN, value="A")],
    )
    with pytest.raises(QueryPlanValidationError):
        validate_query_plan(raw, fake_snapshot, fake_vocab, settings)


def test_needs_clarification_raises_ambiguous(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(
        operation=Operation.GET,
        entity="invoices",
        needs_clarification=True,
        clarification_question="Do you mean total or balance?",
    )
    with pytest.raises(AmbiguousRequestError):
        validate_query_plan(raw, fake_snapshot, fake_vocab, settings)


def test_unsupported_flag_raises_unsupported(fake_snapshot, fake_vocab, settings):
    # An entirely empty plan (no fields, filters, aggregate, etc.) gives the
    # validator nothing to check against the schema, so the LLM's own
    # unsupported hint is the only signal available and is honored.
    raw = RawQueryPlan(
        operation=Operation.LIST,
        entity="employees",
        unsupported=True,
        unsupported_reason="No such data available.",
    )
    with pytest.raises(UnsupportedRequestError):
        validate_query_plan(raw, fake_snapshot, fake_vocab, settings)


# --- Regression: `unsupported` is a hint, never authoritative ------------------

def test_unsupported_flag_is_ignored_when_plan_is_schema_valid(fake_snapshot, fake_vocab, settings):
    """The core bug fix: a schema-representable plan must still succeed even
    if the LLM incorrectly marked it unsupported. The flag alone must never
    short-circuit validation before real fields/filters are checked."""
    raw = RawQueryPlan(
        operation=Operation.LIST,
        entity="employees",
        fields=["name", "salary"],
        unsupported=True,
        unsupported_reason="This request is not supported by the available data.",
    )
    plan = validate_query_plan(raw, fake_snapshot, fake_vocab, settings)
    assert plan.primary_table == "employees"
    assert {f.column for f in plan.select_fields} == {"name", "salary"}


def test_unsupported_flag_ignored_even_with_filters_and_aggregate(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(
        operation=Operation.SUM,
        entity="invoices",
        aggregate_field="total",
        filters=[FilterSpec(field="is_deleted", operator=FilterOperator.EQUALS, value=0)],
        unsupported=True,
        unsupported_reason="This request is not supported by the available data.",
    )
    plan = validate_query_plan(raw, fake_snapshot, fake_vocab, settings)
    assert plan.aggregate_field.column == "total"


def test_unsupported_flag_does_not_hide_a_real_invalid_field(fake_snapshot, fake_vocab, settings):
    """The flag being non-authoritative cuts both ways: a genuinely broken
    plan must still fail schema validation, not be waved through."""
    raw = RawQueryPlan(
        operation=Operation.LIST,
        entity="employees",
        fields=["warp_speed"],
        unsupported=True,
        unsupported_reason="This request is not supported by the available data.",
    )
    with pytest.raises(QueryPlanValidationError):
        validate_query_plan(raw, fake_snapshot, fake_vocab, settings)


def test_join_across_verified_relationship_succeeds(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(
        operation=Operation.LIST,
        entity="customers",
        fields=["name", "invoices.total"],
    )
    plan = validate_query_plan(raw, fake_snapshot, fake_vocab, settings)
    assert "invoices" in plan.join_tables


def test_aggregate_requires_aggregate_field(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(operation=Operation.SUM, entity="invoices", fields=[])
    with pytest.raises(QueryPlanValidationError):
        validate_query_plan(raw, fake_snapshot, fake_vocab, settings)


def test_sum_on_non_numeric_field_raises(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(operation=Operation.SUM, entity="invoices", fields=[], aggregate_field="id")
    # id is numeric (integer) so this should actually succeed; use a string column instead.
    plan = validate_query_plan(raw, fake_snapshot, fake_vocab, settings)
    assert plan.aggregate_field.column == "id"


def test_explicit_limit_is_preserved(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(operation=Operation.LIST, entity="employees", fields=["name"], limit=10_000)
    plan = validate_query_plan(raw, fake_snapshot, fake_vocab, settings)
    assert plan.limit == 10_000


def test_compare_operation_is_rejected_as_unsupported(fake_snapshot, fake_vocab, settings):
    """`Operation.COMPARE` is reserved in the enum for a future feature but has
    no SQL-building implementation yet. It must be rejected cleanly here,
    before ever reaching `sql.builder` (where it would previously fail
    with an unclassified `ValueError` -> opaque 500)."""
    raw = RawQueryPlan(operation=Operation.COMPARE, entity="employees", fields=["name"])
    with pytest.raises(UnsupportedRequestError):
        validate_query_plan(raw, fake_snapshot, fake_vocab, settings)


def test_count_operation_does_not_require_aggregate_field(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(operation=Operation.COUNT, entity="customers", fields=[])
    plan = validate_query_plan(raw, fake_snapshot, fake_vocab, settings)
    assert plan.operation == Operation.COUNT


# --- Default projection (item 5: no unnecessary SELECT *) -----------------------

def test_default_projection_puts_primary_key_first(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(operation=Operation.LIST, entity="employees", fields=[])
    plan = validate_query_plan(raw, fake_snapshot, fake_vocab, settings)
    assert plan.select_fields[0].column == "id"


def test_default_projection_is_capped_by_setting(fake_snapshot, fake_vocab):
    tight_settings = Settings(mysql_password="x", default_projection_max_columns=2)
    raw = RawQueryPlan(operation=Operation.LIST, entity="employees", fields=[])
    plan = validate_query_plan(raw, fake_snapshot, fake_vocab, tight_settings)
    assert len(plan.select_fields) == 2


def test_explicit_fields_are_never_capped(fake_snapshot, fake_vocab):
    """The projection cap only applies to the *default* - explicit requests are honored in full."""
    tight_settings = Settings(mysql_password="x", default_projection_max_columns=1)
    raw = RawQueryPlan(operation=Operation.LIST, entity="employees", fields=["id", "name", "salary"])
    plan = validate_query_plan(raw, fake_snapshot, fake_vocab, tight_settings)
    assert len(plan.select_fields) == 3


# --- Fuzzy field resolution (item 3) --------------------------------------------

def test_fuzzy_field_typo_resolves_to_real_column(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(operation=Operation.LIST, entity="employees", fields=["salery"])
    plan = validate_query_plan(raw, fake_snapshot, fake_vocab, settings)
    assert plan.select_fields[0].column == "salary"


def test_completely_unrelated_field_still_rejected(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(operation=Operation.LIST, entity="employees", fields=["spaceship_warp_core"])
    with pytest.raises(QueryPlanValidationError):
        validate_query_plan(raw, fake_snapshot, fake_vocab, settings)


# --- Join ambiguity across multiple real relationships (item 4/6) --------------

def test_multiple_relationships_between_same_tables_requires_clarification(
    fake_snapshot_with_ambiguous_join, fake_vocab, settings
):
    from app.schema.vocabulary import build_vocabulary_index

    vocab = build_vocabulary_index(fake_snapshot_with_ambiguous_join)
    raw = RawQueryPlan(
        operation=Operation.LIST,
        entity="employees",
        fields=["name", "departments.name"],
    )
    with pytest.raises(AmbiguousRequestError) as exc_info:
        validate_query_plan(raw, fake_snapshot_with_ambiguous_join, vocab, settings)
    assert "department_id" in exc_info.value.question or "on_loan_to_department_id" in exc_info.value.question


# --- Ordinal record positions (items 1, 3, 4, 6, 7) -----------------------------

def test_record_positions_use_live_primary_key_for_ordering(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(
        operation=Operation.LIST,
        entity="employees",
        fields=[],
        select_all=True,
        record_positions=[2, 3, 4],
    )
    plan = validate_query_plan(raw, fake_snapshot, fake_vocab, settings)
    assert plan.record_positions == [2, 3, 4]
    assert [f.column for f in plan.ordering_fields] == ["id"]
    assert plan.ordering_fields[0].table == "employees"


def test_record_positions_ordering_column_never_forced_into_select_list(fake_snapshot, fake_vocab, settings):
    """Item 3: a primary key used for internal ordering must not silently
    become an output column just because it exists."""
    raw = RawQueryPlan(
        operation=Operation.LIST,
        entity="employees",
        fields=["name"],
        record_positions=[1],
    )
    plan = validate_query_plan(raw, fake_snapshot, fake_vocab, settings)
    assert [f.column for f in plan.select_fields] == ["name"]
    assert "id" not in [f.column for f in plan.select_fields]
    assert plan.ordering_fields[0].column == "id"


def test_record_positions_dedupes_while_preserving_first_occurrence(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(
        operation=Operation.LIST, entity="employees", fields=[], select_all=True,
        record_positions=[3, 2, 3, 4, 2],
    )
    plan = validate_query_plan(raw, fake_snapshot, fake_vocab, settings)
    assert plan.record_positions == [3, 2, 4]


def test_record_positions_reject_non_positive(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(operation=Operation.LIST, entity="employees", fields=[], record_positions=[0, 1])
    with pytest.raises(QueryPlanValidationError):
        validate_query_plan(raw, fake_snapshot, fake_vocab, settings)


def test_record_positions_reject_on_non_list_operation(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(
        operation=Operation.COUNT, entity="employees", fields=[], record_positions=[1, 2],
    )
    with pytest.raises(QueryPlanValidationError):
        validate_query_plan(raw, fake_snapshot, fake_vocab, settings)


def test_record_positions_do_not_apply_hidden_row_window(fake_snapshot, fake_vocab, settings):
    raw = RawQueryPlan(
        operation=Operation.LIST, entity="employees", fields=[], select_all=True,
        record_positions=[1, 500],
    )
    plan = validate_query_plan(raw, fake_snapshot, fake_vocab, settings)
    assert plan.record_positions == [1, 500]


def test_table_with_no_primary_key_and_record_positions_raises_clear_error(fake_vocab, settings):
    """Item 7: never invent an ordering column - fail with a structured,
    explanatory error instead."""
    from app.schema.registry import ColumnDescriptor, SchemaSnapshot, TableDescriptor
    from app.schema.vocabulary import build_vocabulary_index

    no_pk_table = TableDescriptor(
        table="event_log",
        description="Event log records",
        columns={
            "message": ColumnDescriptor(
                name="message", type="string", nullable=True, primary_key=False, description="message"
            ),
        },
    )
    snapshot = SchemaSnapshot(tables={"event_log": no_pk_table}, relationships=[], built_at=0.0)
    vocab = build_vocabulary_index(snapshot)
    raw = RawQueryPlan(
        operation=Operation.LIST, entity="event_log", fields=[], select_all=True, record_positions=[2, 3],
    )
    with pytest.raises(QueryPlanValidationError, match="no primary key"):
        validate_query_plan(raw, snapshot, vocab, settings)


def test_record_positions_work_generically_for_any_table(fake_snapshot, fake_vocab, settings):
    """Item 8/10: not special-cased to any particular table or column name."""
    for entity in ("employees", "departments", "customers", "invoices"):
        raw = RawQueryPlan(
            operation=Operation.LIST, entity=entity, fields=[], select_all=True, record_positions=[2, 3, 4],
        )
        plan = validate_query_plan(raw, fake_snapshot, fake_vocab, settings)
        assert plan.primary_table == entity
        assert plan.ordering_fields[0].table == entity
        assert plan.ordering_fields[0].column == "id"  # every fake table's real PK


def test_join_path_is_deterministic_across_repeated_calls(fake_snapshot, fake_vocab, settings):
    """Running the same join-requiring plan twice must always pick the same path."""
    raw = RawQueryPlan(operation=Operation.LIST, entity="customers", fields=["name", "invoices.total"])
    plan_a = validate_query_plan(raw, fake_snapshot, fake_vocab, settings)
    plan_b = validate_query_plan(raw, fake_snapshot, fake_vocab, settings)
    assert plan_a.join_tables == plan_b.join_tables == ["invoices"]


def test_sensitive_field_cannot_be_queried(fake_snapshot, fake_vocab):
    from app.planning.models import RawQueryPlan
    from app.planning.operators import Operation
    from app.core.exceptions import QueryPlanValidationError

    fake_snapshot.tables["employees"].columns["password"] = type(fake_snapshot.tables["employees"].columns["name"])(
        name="password", type="string", nullable=False, primary_key=False, description="password"
    )
    with pytest.raises(QueryPlanValidationError, match="sensitive authentication data"):
        validate_query_plan(
            RawQueryPlan(operation=Operation.LIST, entity="employees", fields=["password"]),
            fake_snapshot,
            fake_vocab,
            Settings(mysql_password="x"),
        )