from __future__ import annotations

import copy
import json

import pytest

from app.planning.planner import build_query_plan
from app.planning.models import RawQueryPlan
from app.planning.reconciliation.evidence import _filter_is_supported, _infer_sort_direction, _value_is_stated
from app.planning.reconciliation.field_metrics import _infer_ranking_field
from app.planning.reconciliation.aggregate_repairs import resolve_ranking_clarification
from app.schema.registry import ColumnDescriptor, SchemaSnapshot, TableDescriptor
from app.schema.vocabulary import build_vocabulary_index


class ScriptedLLM:
    def __init__(self, payload: dict):
        self.payload = payload

    async def generate_json(self, *args, **kwargs):
        return json.dumps(self.payload)


def _vendors_snapshot(fake_snapshot):
    snapshot = copy.deepcopy(fake_snapshot)
    snapshot.tables["vendors"] = TableDescriptor(
        table="vendors",
        description="Vendor records",
        columns={
            "id": ColumnDescriptor("id", "integer", False, True, "Unique identifier"),
            "contact_name": ColumnDescriptor("contact_name", "string", True, False, "Contact name"),
            "company_name": ColumnDescriptor("company_name", "string", True, False, "Company name"),
            "outstanding_payable_amount": ColumnDescriptor(
                "outstanding_payable_amount", "decimal", True, False, "Outstanding payable amount"
            ),
            "status": ColumnDescriptor("status", "string", True, False, "Current status"),
        },
    )
    snapshot.tables["qbo_accounts"] = TableDescriptor(
        table="qbo_accounts",
        description="Accounting account records",
        columns={
            "id": ColumnDescriptor("id", "integer", False, True, "Unique identifier"),
            "name": ColumnDescriptor("name", "string", True, False, "Name"),
        },
    )
    snapshot.tables["bank_transactions"] = TableDescriptor(
        table="bank_transactions",
        description="Bank transaction records",
        columns={
            "id": ColumnDescriptor("id", "integer", False, True, "Unique identifier"),
            "amount": ColumnDescriptor("amount", "decimal", True, False, "Monetary amount"),
        },
    )
    return snapshot


def _base_plan(**overrides):
    payload = {
        "operation": "LIST",
        "entity": "vendors",
        "fields": ["qbo_accounts.name"],
        "select_all": False,
        "record_positions": [],
        "filters": [{"field": "status", "operator": "IN", "value": ["Pending", "Draft"]}],
        "sort": {"field": "outstanding_payable_amount", "direction": "DESC"},
        "group_by": None,
        "aggregate_field": None,
        "limit": 100,
        "needs_clarification": False,
        "clarification_question": None,
        "unsupported": False,
        "unsupported_reason": None,
    }
    payload.update(overrides)
    return payload


@pytest.mark.asyncio
async def test_vendor_attribute_request_removes_unrequested_join_filter_sort_and_limit(fake_snapshot):
    snapshot = _vendors_snapshot(fake_snapshot)
    llm = ScriptedLLM(_base_plan())

    result = await build_query_plan(
        "Give me the vendor names and their outstanding payable amounts.",
        snapshot,
        llm,
    )

    sql = result.data_query.sql
    assert "FROM vendors" in sql
    assert "contact_name" in sql
    assert "company_name" in sql
    assert "outstanding_payable_amount" in sql
    assert "qbo_accounts" not in sql
    assert "bank_transactions" not in sql
    assert "WHERE" not in sql
    assert "ORDER BY" not in sql
    assert "LIMIT" not in sql
    assert "OFFSET" not in sql
    assert result.data_query.params == {}


@pytest.mark.asyncio
async def test_explicit_top_limit_and_sort_are_preserved(fake_snapshot):
    snapshot = _vendors_snapshot(fake_snapshot)
    llm = ScriptedLLM(
        _base_plan(
            fields=["contact_name", "outstanding_payable_amount"],
            filters=[],
            sort={"field": "outstanding_payable_amount", "direction": "DESC"},
            limit=100,
        )
    )

    result = await build_query_plan(
        "Show me the top 5 vendors with the highest outstanding payable amount.",
        snapshot,
        llm,
    )

    assert "ORDER BY vendors.outstanding_payable_amount DESC" in result.data_query.sql
    assert "LIMIT :requested_limit" in result.data_query.sql
    assert result.data_query.params["requested_limit"] == 5
    assert "OFFSET" not in result.data_query.sql


@pytest.mark.asyncio
async def test_unrelated_numeric_filter_is_retargeted_to_requested_entity(fake_snapshot):
    snapshot = _vendors_snapshot(fake_snapshot)
    snapshot.tables["invoices"] = copy.deepcopy(snapshot.tables["invoices"])
    snapshot.tables["invoices"].columns["total"].description = "Total amount"

    llm = ScriptedLLM(
        _base_plan(
            entity="invoices",
            fields=[],
            filters=[{"field": "bank_transactions.amount", "operator": "GREATER_THAN", "value": 50000}],
            sort=None,
            limit=None,
        )
    )

    result = await build_query_plan(
        "Show me all invoices created in March 2025 above 50000.",
        snapshot,
        llm,
    )

    sql = result.data_query.sql
    assert "FROM invoices" in sql
    assert "bank_transactions" not in sql
    assert "invoices.total > :f0" in sql
    assert "LIMIT" not in sql
    assert "ORDER BY" not in sql


@pytest.mark.asyncio
async def test_explicit_multi_table_request_keeps_required_join(fake_snapshot):
    llm = ScriptedLLM(
        _base_plan(
            entity="customers",
            fields=["name", "invoices.total"],
            filters=[],
            sort=None,
            limit=None,
        )
    )

    result = await build_query_plan(
        "Show customer names with their invoice totals.",
        fake_snapshot,
        llm,
    )

    assert "JOIN invoices ON invoices.customer_id = customers.id" in result.data_query.sql
    assert "customers.name" in result.data_query.sql
    assert "invoices.total" in result.data_query.sql
    assert "LIMIT" not in result.data_query.sql


# --- Unit tests: deterministic ranking-field resolution (reconciliation) ---


def _payables_receivables_snapshot() -> SchemaSnapshot:
    """A vendors table with several similarly-named monetary columns, the
    same shape of ambiguity the real schema has (payable vs. receivable vs.
    base-currency variant vs. unused-credits variants)."""
    return SchemaSnapshot(
        tables={
            "vendors": TableDescriptor(
                table="vendors",
                description="Vendor/supplier contact records.",
                columns={
                    "id": ColumnDescriptor("id", "integer", False, True, "Unique identifier"),
                    "contact_name": ColumnDescriptor("contact_name", "string", True, False, "Vendor's primary contact person name"),
                    "outstanding_payable_amount": ColumnDescriptor(
                        "outstanding_payable_amount", "decimal", True, False,
                        "Total outstanding amount owed to this vendor (accounts payable balance)",
                    ),
                    "outstanding_payable_amount_bcy": ColumnDescriptor(
                        "outstanding_payable_amount_bcy", "decimal", True, False,
                        "Monetary amount (in base currency)",
                    ),
                    "outstanding_receivable_amount": ColumnDescriptor(
                        "outstanding_receivable_amount", "decimal", True, False, "Monetary amount"
                    ),
                    "unused_credits_payable_amount": ColumnDescriptor(
                        "unused_credits_payable_amount", "decimal", True, False, "Monetary amount"
                    ),
                    "status": ColumnDescriptor("status", "string", True, False, "Current status"),
                },
            )
        },
        relationships=[],
        built_at=0.0,
    )


def test_infer_ranking_field_resolves_canonical_field_without_amount_word():
    snapshot = _payables_receivables_snapshot()
    field = _infer_ranking_field(
        "Show vendors ordered by outstanding payable from highest to lowest.", "vendors", snapshot
    )
    assert field == "outstanding_payable_amount"


def test_infer_ranking_field_resolves_canonical_field_with_exact_phrase():
    snapshot = _payables_receivables_snapshot()
    field = _infer_ranking_field(
        "Show the top 5 vendors by outstanding payable amount.", "vendors", snapshot
    )
    assert field == "outstanding_payable_amount"


def test_infer_ranking_field_does_not_confuse_payable_with_receivable():
    snapshot = _payables_receivables_snapshot()
    field = _infer_ranking_field(
        "Show vendors ranked by outstanding receivable, highest first.", "vendors", snapshot
    )
    assert field == "outstanding_receivable_amount"


def test_infer_ranking_field_selects_base_currency_variant_when_explicit():
    snapshot = _payables_receivables_snapshot()
    field = _infer_ranking_field(
        "Show vendors ordered by outstanding payable in base currency, highest to lowest.",
        "vendors",
        snapshot,
    )
    assert field == "outstanding_payable_amount_bcy"


def test_infer_ranking_field_returns_none_for_genuinely_ambiguous_request():
    snapshot = _payables_receivables_snapshot()
    field = _infer_ranking_field("Show top 5 vendors by amount.", "vendors", snapshot)
    assert field is None


@pytest.mark.parametrize(
    "question,expected",
    [
        ("outstanding payable from highest to lowest", "DESC"),
        ("outstanding payable from lowest to highest", "ASC"),
        ("top 5 vendors by outstanding payable amount", "DESC"),
        ("bottom 3 vendors by outstanding payable amount", "ASC"),            ("vendors sorted by outstanding payable ascending", "ASC"),
            ("vendors sorted by outstanding payable descending", "DESC"),
            # No direction word at all - the neutral reading is ASC, never an
            # invented DESC.
            ("vendors ordered by outstanding payable", "ASC"),
    ],
)
def test_infer_sort_direction(question, expected):
    assert _infer_sort_direction(question) == expected


def test_resolve_ranking_clarification_repairs_plan_in_place():
    snapshot = _payables_receivables_snapshot()
    vocab = build_vocabulary_index(snapshot)
    raw_plan = RawQueryPlan(
        operation="LIST",
        entity="vendors",
        needs_clarification=True,
        clarification_question="Which amount field did you mean?",
    )

    resolved = resolve_ranking_clarification(
        "Show vendors ordered by outstanding payable from highest to lowest.",
        raw_plan,
        snapshot,
        vocab,
    )

    assert resolved is True
    assert raw_plan.needs_clarification is False
    assert raw_plan.clarification_question is None
    assert raw_plan.sort is not None
    assert raw_plan.sort.field == "outstanding_payable_amount"
    assert raw_plan.sort.direction == "DESC"
    assert raw_plan.limit is None


def test_resolve_ranking_clarification_applies_explicit_top_n_limit():
    snapshot = _payables_receivables_snapshot()
    vocab = build_vocabulary_index(snapshot)
    raw_plan = RawQueryPlan(
        operation="LIST",
        entity="vendors",
        needs_clarification=True,
        clarification_question="Which amount field did you mean?",
    )

    resolved = resolve_ranking_clarification(
        "Show the top 5 vendors by outstanding payable amount.", raw_plan, snapshot, vocab
    )

    assert resolved is True
    assert raw_plan.sort.field == "outstanding_payable_amount"
    assert raw_plan.limit == 5


def test_resolve_ranking_clarification_leaves_plan_untouched_without_sort_intent():
    snapshot = _payables_receivables_snapshot()
    vocab = build_vocabulary_index(snapshot)
    raw_plan = RawQueryPlan(
        operation="GET",
        entity="vendors",
        needs_clarification=True,
        clarification_question="Do you mean the contact name or company name?",
    )

    resolved = resolve_ranking_clarification(
        "Show me the vendor name.", raw_plan, snapshot, vocab
    )

    assert resolved is False
    assert raw_plan.needs_clarification is True
    assert raw_plan.sort is None


def test_resolve_ranking_clarification_stays_ambiguous_when_no_clear_field_wins():
    snapshot = _payables_receivables_snapshot()
    vocab = build_vocabulary_index(snapshot)
    raw_plan = RawQueryPlan(
        operation="LIST",
        entity="vendors",
        needs_clarification=True,
        clarification_question="Which amount field did you mean?",
    )

    resolved = resolve_ranking_clarification(
        "Show top 5 vendors by amount.", raw_plan, snapshot, vocab
    )

    assert resolved is False
    assert raw_plan.needs_clarification is True
    assert raw_plan.sort is None

def test_name_inference_ignores_unrelated_name_suffix_columns(fake_snapshot):
    from app.schema.registry import TableDescriptor, ColumnDescriptor
    from app.planning.reconciliation.field_projection import _infer_primary_fields
    fake_snapshot.tables["customers"] = TableDescriptor(
        table="customers", description="Customer contact records", columns={
            "contact_name": ColumnDescriptor("contact_name", "string", True, False, "Customer primary contact person name"),
            "company_name": ColumnDescriptor("company_name", "string", True, False, "Customer company name"),
            "tax_name": ColumnDescriptor("tax_name", "string", True, False, "Tax name"),
            "pricebook_name": ColumnDescriptor("pricebook_name", "string", True, False, "Pricebook name"),
        },
    )
    assert _infer_primary_fields("give all customer names", "customers", fake_snapshot) == ["contact_name", "company_name"]


# --- Regression: empty-collection value must never count as "stated" ------


@pytest.mark.parametrize("empty_value", [[], (), set()])
def test_value_is_stated_rejects_empty_collections(empty_value):
    """`all(...)` over an empty collection is vacuously True in Python - the
    exact trap that let a filter with `value=[]`/`value2=[]` (e.g. a
    malformed BETWEEN) be treated as evidenced by the question and reach SQL
    generation as an invalid, unbindable condition. An empty collection is
    the absence of a value, never evidence one was stated."""
    assert _value_is_stated("What is the total expense amount for each month this year?", empty_value) is False


def test_value_is_stated_still_accepts_a_real_non_empty_collection():
    assert _value_is_stated("Show bills for vendor Acme and vendor Beta", ["Acme", "Beta"]) is True


def test_filter_is_supported_rejects_between_with_empty_values():
    from app.planning.models import FilterSpec
    from app.planning.operators import FilterOperator

    filter_spec = FilterSpec(field="amount", operator=FilterOperator.BETWEEN, value=[], value2=[])
    assert _filter_is_supported(
        "What is the total expense amount for each month this year?", filter_spec
    ) is False