"""Standalone test of the parts of chat_with_data.py that don't need a live API key:
the SQL safety allowlist and the actual read-only database execution + rejection path.
"""
import sys
sys.path.insert(0, ".")
from chat_with_data import is_safe_select, run_sql_query

print("=== is_safe_select() checks ===")
cases = [
    ("SELECT * FROM vendor_spend", True),
    ("  select vendor_name from vendor_spend  ", True),
    ("WITH t AS (SELECT 1) SELECT * FROM t", True),
    ("DROP TABLE vendor_spend", False),
    ("DELETE FROM dim_employee", False),
    ("SELECT * FROM vendor_spend; DROP TABLE vendor_spend;", False),
    ("UPDATE dim_employee SET base_salary_annual = 0", False),
    ("SELECT * FROM vendor_spend WHERE vendor_name = 'DROP'", False),  # contains the word DROP - correctly over-cautious
    ("ATTACH DATABASE '/etc/passwd' AS x", False),
]
all_pass = True
for sql, expected in cases:
    got = is_safe_select(sql)
    ok = got == expected
    all_pass &= ok
    print(f"  {'PASS' if ok else 'FAIL'}  expected={expected!s:5} got={got!s:5}  {sql[:60]}")

print("\n=== run_sql_query() against the real seeded demo DB ===")
r1 = run_sql_query(
    "SELECT vendor_name, total_spend_2025, teams_using_tool FROM vendor_spend "
    "WHERE teams_using_tool <= 2 ORDER BY total_spend_2025 DESC"
)
print("Low-adoption, high-cost vendors:", r1)

r2 = run_sql_query("DELETE FROM vendor_spend")
print("\nAttempted DELETE (should be refused by the allowlist):", r2)

r3 = run_sql_query("SELECT total_cost FROM fact_cost_monthly ORDER BY month_date")
print("\nMonthly total cost:", r3)

print("\nALL CHECKS PASSED" if all_pass else "\nSOME CHECKS FAILED")