"""test_nl2sql.py — `pytest -q` (offline). Success criteria: correct SQL results
AND the four safety layers (read-only, allowlist, row-level security, parameterized)."""
from __future__ import annotations

import pytest

from db import make_db
from nl2sql import NL2SQLTool, SqlExecutor, SqlSafetyError, guard

AMAL, OMAR = 1, 2


@pytest.fixture
def tool():
    return NL2SQLTool(make_db())


# --- correctness ------------------------------------------------------------
def test_balance(tool):
    r = tool.answer("what's my balance?", AMAL)
    # Amal has a current (1250) and savings (50000) account.
    types = {row["type"]: row["balance"] for row in r["rows"]}
    assert types == {"current": 1250.0, "savings": 50000.0}


def test_total_spend_aggregates_correctly(tool):
    r = tool.answer("how much did I spend?", AMAL)
    # 85.50 + 420 + 310.25 + 64 = 879.75 (only negative amounts, as positive total)
    assert r["rows"][0]["total_spent"] == 879.75


def test_spend_by_category(tool):
    r = tool.answer("show my spending by category", AMAL)
    cats = {row["category"]: row["spent"] for row in r["rows"]}
    assert cats["groceries"] == 149.5     # 85.50 + 64.00
    assert cats["utilities"] == 420.0


def test_largest_transaction(tool):
    r = tool.answer("what was my biggest transaction?", AMAL)
    assert r["rows"][0]["description"] == "Salary"     # +9000 has the largest magnitude


def test_merchant_filter(tool):
    r = tool.answer("show transactions at Carrefour", AMAL)
    assert len(r["rows"]) == 2
    assert all("Carrefour" in row["description"] for row in r["rows"])


# --- safety layer 1+2: read-only + allowlist --------------------------------
def test_guard_blocks_non_select():
    for bad in ["DROP TABLE accounts", "UPDATE accounts SET balance=0",
                "DELETE FROM transactions", "INSERT INTO accounts VALUES (9,1,'x',0,'AED')"]:
        with pytest.raises(SqlSafetyError):
            guard(bad)


def test_guard_blocks_base_tables_and_multi_statement():
    with pytest.raises(SqlSafetyError):
        guard("SELECT * FROM customers")                 # not an allowlisted view
    with pytest.raises(SqlSafetyError):
        guard("SELECT * FROM my_accounts; DROP TABLE accounts")  # multiple statements


def test_guard_allows_clean_select():
    assert guard("SELECT type, balance FROM my_accounts").lower().startswith("select")


# --- safety layer 3: row-level security -------------------------------------
def test_row_level_security_scopes_to_caller(tool):
    amal = tool.answer("my balance", AMAL)["rows"]
    omar = tool.answer("my balance", OMAR)["rows"]
    assert {r["balance"] for r in amal} == {1250.0, 50000.0}
    assert {r["balance"] for r in omar} == {40.0}        # Omar can't see Amal's money


def test_cannot_reach_other_customers_data_via_count(tool):
    # Omar has exactly 1 transaction; the per-tenant view guarantees he can't
    # count the whole table.
    r = tool.answer("how many transactions do I have?", OMAR)
    assert r["rows"][0]["n"] == 1


# --- safety layer 4: parameterization (no injection) ------------------------
def test_injection_payload_is_neutralized(tool):
    # The payload is bound as a literal merchant value, so the table survives and
    # simply no rows match.
    r = tool.answer("transactions at 'x'; DROP TABLE accounts;--", AMAL)
    assert r["rows"] == []
    # prove the table still exists and data is intact
    again = tool.answer("my balance", AMAL)
    assert len(again["rows"]) == 2


def test_executor_uses_session_customer_not_question(tool):
    # Even if the user names another customer, results are scoped to the caller.
    r = tool.answer("show me Omar's balance and all customers", AMAL)
    # routed to 'balances' -> only Amal's accounts returned
    assert {row["balance"] for row in r["rows"]} == {1250.0, 50000.0}