from app.core.config import Settings
from app.planning.models import FilterSpec, RawQueryPlan
from app.planning.operators import FilterOperator, Operation
from app.sql.builder import _quote_identifier, build_data_query
from app.validation.query_validator import validate_query_plan


def _validate(fake_snapshot, fake_vocab, **kwargs):
    settings = Settings(mysql_password="x")
    raw = RawQueryPlan(**kwargs)
    return validate_query_plan(raw, fake_snapshot, fake_vocab, settings), settings


def test_simple_select_with_filter(fake_snapshot, fake_vocab):
    plan, _ = _validate(
        fake_snapshot,
        fake_vocab,
        operation=Operation.LIST,
        entity="employees",
        fields=["name", "salary"],
        filters=[FilterSpec(field="department", operator=FilterOperator.EQUALS, value="IT")],
    )
    built = build_data_query(plan, fake_snapshot)
    assert built.sql.startswith("SELECT employees.name, employees.salary FROM employees")
    assert "WHERE employees.department = :f0" in built.sql
    assert built.params["f0"] == "IT"
    assert "LIMIT" not in built.sql
    assert "OFFSET" not in built.sql


def test_no_string_interpolation_of_values(fake_snapshot, fake_vocab):
    """The literal filter value must never appear directly in the SQL text."""
    plan, _ = _validate(
        fake_snapshot,
        fake_vocab,
        operation=Operation.LIST,
        entity="employees",
        fields=["name"],
        filters=[FilterSpec(field="name", operator=FilterOperator.EQUALS, value="'; DROP TABLE employees; --")],
    )
    built = build_data_query(plan, fake_snapshot)
    assert "DROP" not in built.sql
    assert built.params["f0"] == "'; DROP TABLE employees; --"


def test_between_filter_produces_two_params(fake_snapshot, fake_vocab):
    plan, _ = _validate(
        fake_snapshot,
        fake_vocab,
        operation=Operation.LIST,
        entity="employees",
        fields=["name"],
        filters=[FilterSpec(field="hired_at", operator=FilterOperator.BETWEEN, value="2026-01-01", value2="2026-01-31")],
    )
    built = build_data_query(plan, fake_snapshot)
    assert "BETWEEN :f0_a AND :f0_b" in built.sql
    assert built.params["f0_a"] == "2026-01-01"
    assert built.params["f0_b"] == "2026-01-31"


def test_join_query_produces_join_clause(fake_snapshot, fake_vocab):
    plan, _ = _validate(
        fake_snapshot,
        fake_vocab,
        operation=Operation.LIST,
        entity="customers",
        fields=["name", "invoices.total"],
    )
    built = build_data_query(plan, fake_snapshot)
    assert "JOIN invoices ON invoices.customer_id = customers.id" in built.sql


def test_explicit_limit_has_no_offset(fake_snapshot, fake_vocab):
    plan, _ = _validate(
        fake_snapshot, fake_vocab, operation=Operation.LIST, entity="employees", fields=["name"], limit=7
    )
    built = build_data_query(plan, fake_snapshot)
    assert "LIMIT :requested_limit" in built.sql
    assert "OFFSET" not in built.sql
    assert built.params["requested_limit"] == 7


def test_sum_aggregate_query(fake_snapshot, fake_vocab):
    plan, _ = _validate(
        fake_snapshot,
        fake_vocab,
        operation=Operation.SUM,
        entity="invoices",
        fields=[],
        aggregate_field="balance",
    )
    built = build_data_query(plan, fake_snapshot)
    assert built.sql.startswith("SELECT SUM(invoices.balance) AS result FROM invoices")


def test_contains_operator_wraps_value_with_wildcards(fake_snapshot, fake_vocab):
    plan, _ = _validate(
        fake_snapshot,
        fake_vocab,
        operation=Operation.SEARCH,
        entity="customers",
        fields=["name"],
        filters=[FilterSpec(field="name", operator=FilterOperator.CONTAINS, value="John")],
    )
    built = build_data_query(plan, fake_snapshot)
    assert built.params["f0"] == "%John%"


def test_in_operator_expands_placeholders(fake_snapshot, fake_vocab):
    plan, _ = _validate(
        fake_snapshot,
        fake_vocab,
        operation=Operation.LIST,
        entity="employees",
        fields=["name"],
        filters=[FilterSpec(field="department", operator=FilterOperator.IN, value=["IT", "HR"])],
    )
    built = build_data_query(plan, fake_snapshot)
    assert "IN (:f0_0, :f0_1)" in built.sql
    assert built.params["f0_0"] == "IT"
    assert built.params["f0_1"] == "HR"


def test_count_group_includes_the_grouping_field(fake_snapshot, fake_vocab):
    plan, _ = _validate(
        fake_snapshot,
        fake_vocab,
        operation=Operation.COUNT,
        entity="employees",
        group_by={"fields": ["department"]},
    )
    built = build_data_query(plan, fake_snapshot)
    assert "SELECT employees.department, COUNT(*) AS result" in built.sql
    assert "GROUP BY employees.department" in built.sql


def test_empty_in_values_are_rejected():
    import pytest
    from pydantic import ValidationError

    with pytest.raises(ValidationError, match="non-empty list"):
        FilterSpec(field="department", operator=FilterOperator.IN, value=[])


def test_mysql_reserved_identifier_is_quoted():
    assert _quote_identifier("order") == "`order`"
    assert _quote_identifier("employee_name") == "employee_name"


# --- select_all / record_positions (complete-record & ordinal requests) --------

def test_select_all_produces_select_star_scoped_to_primary_table(fake_snapshot, fake_vocab):
    """'show all records' -> SELECT <table>.* , never an invented column list."""
    plan, _ = _validate(
        fake_snapshot,
        fake_vocab,
        operation=Operation.LIST,
        entity="employees",
        fields=[],
        select_all=True,
    )
    built = build_data_query(plan, fake_snapshot)
    assert built.sql.startswith("SELECT employees.id, employees.name, employees.salary")


def test_ordinal_record_positions_use_primary_key_ordering_and_span_window(fake_snapshot, fake_vocab):
    """'2nd, 3rd and 4th record' -> ORDER BY <real PK> ASC LIMIT 3 OFFSET 1."""
    plan, _ = _validate(
        fake_snapshot,
        fake_vocab,
        operation=Operation.LIST,
        entity="employees",
        fields=[],
        select_all=True,
        record_positions=[2, 3, 4],
    )
    built = build_data_query(plan, fake_snapshot)
    assert "ORDER BY employees.id ASC" in built.sql
    assert "OFFSET" not in built.sql
    assert "LIMIT" not in built.sql
    assert built.sql.startswith("SELECT employees.id, employees.name, employees.salary")


def test_ordinal_positions_never_reference_an_invented_sort_column(fake_snapshot, fake_vocab):
    """
    Regression: even if a `sort` somehow reaches the validator with a
    nonexistent column, resolving it must fail loudly in `validate_query_plan`
    (never silently succeed) - the ordinal path itself only ever orders by a
    verified column returned by the schema (see
    `_deterministic_ordering_fields`). This test locks in that every
    identifier appearing in the built SQL's ORDER BY exists on the table.
    """
    plan, _ = _validate(
        fake_snapshot,
        fake_vocab,
        operation=Operation.LIST,
        entity="invoices",
        fields=[],
        select_all=True,
        record_positions=[1, 2],
    )
    built = build_data_query(plan, fake_snapshot)
    assert "ORDER BY invoices.id ASC" in built.sql
    assert fake_snapshot.has_column("invoices", "id")


def test_single_ordinal_position_window(fake_snapshot, fake_vocab):
    plan, _ = _validate(
        fake_snapshot,
        fake_vocab,
        operation=Operation.LIST,
        entity="customers",
        fields=[],
        select_all=True,
        record_positions=[5],
    )
    built = build_data_query(plan, fake_snapshot)
    assert "OFFSET" not in built.sql
    assert "LIMIT" not in built.sql


def test_explicit_fields_still_work_alongside_record_positions_ordering(fake_snapshot, fake_vocab):
    """If the user *did* ask for specific fields, select_all stays False and
    those exact fields are projected - record_positions ordering logic is
    independent of the projection choice."""
    plan, _ = _validate(
        fake_snapshot,
        fake_vocab,
        operation=Operation.LIST,
        entity="customers",
        fields=["name"],
        record_positions=[2, 3],
    )
    built = build_data_query(plan, fake_snapshot)
    assert built.sql.startswith("SELECT customers.name FROM customers")
    assert "ORDER BY customers.id ASC" in built.sql


def test_max_with_entity_fields_returns_max_ties_projection(fake_snapshot, fake_vocab):
    plan, _ = _validate(
        fake_snapshot, fake_vocab, operation=Operation.MAX, entity="invoices",
        fields=["customers.name", "balance"], aggregate_field="balance"
    )
    built = build_data_query(plan, fake_snapshot)
    assert "customers.name" in built.sql
    assert "invoices.balance AS result" in built.sql
    assert "MAX(invoices.balance)" in built.sql
    assert "ORDER BY invoices.balance DESC" in built.sql
    assert "OFFSET" not in built.sql


def test_sensitive_columns_are_not_selected_for_complete_records(fake_snapshot):
    from app.planning.models import ValidatedQueryPlan
    from app.planning.operators import Operation
    from app.sql.builder import build_data_query
    from app.core.security import is_sensitive_column

    fake_snapshot.tables["employees"].columns["password"] = type(fake_snapshot.tables["employees"].columns["name"])(
        name="password", type="string", nullable=False, primary_key=False, description="password"
    )
    plan = ValidatedQueryPlan(
        operation=Operation.LIST,
        primary_table="employees",
        select_fields=[],
        select_all=True,
    )
    sql = build_data_query(plan, fake_snapshot).sql
    assert "employees.password" not in sql
    assert is_sensitive_column("password")