"""Unit coverage for the Dashboard Designer's query-engine extensions (Phase
1/3 of the Dashboard Designer): the "between" date-range operator, date
bucketing for chart x-axis grouping, and the custom-expression evaluator
(app/crud/expr_eval.py) — each exercised directly, no DB needed. Also covers
how filters become SQL (app/crud/record_sql.py) — the shape of a few
translated conditions, then DB-backed checks that /datasets/query returns the
right rows through them. tests/test_record_sql.py compares the SQL path with
the Python evaluation across every operator."""

import uuid

import pytest
from sqlalchemy.dialects import postgresql

from app.crud import record_sql
from app.crud.dataset_query import _bucket_date, _eval_operator
from app.crud.expr_eval import ExpressionError, evaluate_expression, parse_expression
from app.models.master import MasterRecord


def test_between_operator_matches_inside_range():
    assert _eval_operator("between", "2026-03-15", ["2026-03-01", "2026-03-31"]) is True


def test_between_operator_excludes_outside_range():
    assert _eval_operator("between", "2026-04-01", ["2026-03-01", "2026-03-31"]) is False


def test_between_operator_handles_full_timestamps():
    assert _eval_operator("between", "2026-03-15T10:30:00", ["2026-03-01", "2026-03-31"]) is True


def test_between_operator_never_raises_on_non_date_value():
    assert _eval_operator("between", "not-a-date", ["2026-03-01", "2026-03-31"]) is False


def test_bucket_date_day():
    assert _bucket_date("2026-03-15T10:30:00", "day") == "2026-03-15"


def test_bucket_date_week_buckets_to_monday():
    assert _bucket_date("2026-03-18", "week") == "2026-03-16"  # Wednesday -> that week's Monday


def test_bucket_date_month():
    assert _bucket_date("2026-03-15", "month") == "2026-03"


def test_bucket_date_invalid_value_does_not_raise():
    assert _bucket_date("not-a-date", "month") == "(invalid)"


def test_parse_expression_accepts_valid_grammar():
    parse_expression("SUM(revenue) / SUM(orders)")  # does not raise


def test_parse_expression_rejects_bare_field_name():
    with pytest.raises(ExpressionError):
        parse_expression("revenue / orders")


def test_parse_expression_rejects_import():
    with pytest.raises(ExpressionError):
        parse_expression("__import__('os').system('echo pwned')")


def test_parse_expression_rejects_unknown_function():
    with pytest.raises(ExpressionError):
        parse_expression("TOTAL(revenue)")


def test_parse_expression_rejects_empty_string():
    with pytest.raises(ExpressionError):
        parse_expression("")


def test_evaluate_expression_basic_ratio():
    rows = [{"revenue": 100, "orders": 4}, {"revenue": 50, "orders": 1}]
    assert evaluate_expression("SUM(revenue) / SUM(orders)", rows) == 30.0


def test_evaluate_expression_count():
    rows = [{"revenue": 100}, {"revenue": 50}, {"revenue": 25}]
    assert evaluate_expression("COUNT()", rows) == 3.0


def test_evaluate_expression_division_by_zero_returns_zero():
    assert evaluate_expression("SUM(revenue) / SUM(orders)", []) == 0.0


def test_evaluate_expression_complex_formula():
    rows = [{"revenue": 200, "cost": 150}]
    assert evaluate_expression("(SUM(revenue) - SUM(cost)) / SUM(revenue) * 100", rows) == 25.0


# ---- Filters as SQL (pure) --------------------------------------------------------------------------


def _sql(filter_: dict, field_schema: list[dict] | None = None) -> tuple[str, dict, bool]:
    src = record_sql.RecordSource("master", uuid.uuid4(), field_schema or [])
    clause = record_sql.filter_clause(src, filter_, uuid.uuid4())
    compiled = clause.sql.compile(dialect=postgresql.dialect())
    return str(compiled), compiled.params, clause.exact


def test_equals_uses_the_gin_containment_operator():
    sql, params, exact = _sql({"field": "revenue", "operator": "equals", "value": 100})
    assert "@>" in sql
    assert {"revenue": 100} in params.values()
    assert exact


def test_in_ors_one_containment_check_per_value():
    sql, params, exact = _sql({"field": "status", "operator": "in", "value": ["active", "pending"]})
    assert sql.count("@>") == 2
    assert {"status": "active"} in params.values() and {"status": "pending"} in params.values()
    assert exact


def test_field_names_are_inlined_so_field_indexes_can_match():
    sql, _, _ = _sql({"field": "revenue", "operator": "gt", "value": 5})
    assert "field_values -> 'revenue'" in sql
    sql, _, _ = _sql({"field": "it's", "operator": "equals", "value": "x"})
    assert "'it''s'" in sql


def test_id_filter_targets_the_primary_key_not_field_values():
    record_id = str(uuid.uuid4())
    sql, _, exact = _sql({"field": "id", "operator": "equals", "value": record_id})
    assert "master_records.id =" in sql and "field_values" not in sql
    assert exact


def test_audit_date_range_targets_the_column():
    sql, _, exact = _sql({"field": "created_at", "operator": "between", "value": ["2026-09-01", "2026-09-30"]})
    assert "master_records.created_at >=" in sql and "field_values" not in sql
    assert exact


def test_declared_audit_name_reads_field_values():
    sql, _, _ = _sql({"field": "created_at", "operator": "equals", "value": "x"}, [{"name": "created_at", "type": "text"}])
    assert "field_values" in sql


def test_contains_pattern_that_differs_between_repr_and_json_is_inexact():
    assert _sql({"field": "tags", "operator": "contains", "value": "urgent"})[2] is True
    assert _sql({"field": "tags", "operator": "contains", "value": "a, b"})[2] is False


# ---- Filters as SQL (DB-backed correctness) ---------------------------------------
# Proves /datasets/query still returns the right rows now that "equals"/"in"
# filters against a Master are pre-filtered in SQL via the new GIN index —
# a regression back to a broken pushdown predicate would either return too
# few rows (a real bug) or, if the pushdown were silently dropped entirely,
# would still pass these particular assertions (Python-only filtering was
# already correct) — see the SQL-shape tests above for that regression.


async def _make_master_with_records(client, auth_headers) -> tuple[str, dict[str, str]]:
    resp = await client.post(
        "/masters",
        headers=auth_headers,
        json={
            "name": f"Pushdown Orders {uuid.uuid4().hex[:6]}",
            "field_schema": [
                {"name": "revenue", "type": "number"},
                {"name": "status", "type": "single_select", "options": ["active", "pending", "closed"]},
            ],
        },
    )
    master_id = resp.json()["id"]
    ids = {}
    for revenue, status in [(100, "active"), (50, "pending"), (200, "closed")]:
        rec = await client.post(
            f"/masters/{master_id}/records",
            headers=auth_headers,
            json={"field_values": {"revenue": revenue, "status": status}},
        )
        ids[status] = rec.json()["id"]
    return master_id, ids


async def test_query_equals_filter_on_numeric_field(client, auth_headers, test_tenant):
    master_id, ids = await _make_master_with_records(client, auth_headers)
    resp = await client.post(
        "/datasets/query",
        headers=auth_headers,
        json={"source_type": "master", "source_id": master_id, "filters": [{"field": "revenue", "operator": "equals", "value": 100}]},
    )
    assert resp.status_code == 200
    rows = resp.json()["rows"]
    assert [r["id"] for r in rows] == [ids["active"]]


async def test_query_in_filter_on_text_field(client, auth_headers, test_tenant):
    master_id, ids = await _make_master_with_records(client, auth_headers)
    resp = await client.post(
        "/datasets/query",
        headers=auth_headers,
        json={
            "source_type": "master",
            "source_id": master_id,
            "filters": [{"field": "status", "operator": "in", "value": ["active", "pending"]}],
        },
    )
    assert resp.status_code == 200
    returned_ids = {r["id"] for r in resp.json()["rows"]}
    assert returned_ids == {ids["active"], ids["pending"]}


async def test_query_equals_filter_with_no_match_returns_empty(client, auth_headers, test_tenant):
    master_id, _ids = await _make_master_with_records(client, auth_headers)
    resp = await client.post(
        "/datasets/query",
        headers=auth_headers,
        json={"source_type": "master", "source_id": master_id, "filters": [{"field": "revenue", "operator": "equals", "value": 999}]},
    )
    assert resp.status_code == 200
    assert resp.json()["rows"] == []