"""run.py — Lab 08 demo (offline). NL->SQL questions, the generated SQL, the real
rows, and the safety guard blocking unsafe queries + cross-customer access.

    python run.py
"""
from __future__ import annotations

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

AMAL = 1     # customer_id from the authenticated session
OMAR = 2


def main() -> None:
    tool = NL2SQLTool(make_db())

    print("=" * 70 + "\nNL -> SQL (scoped to the signed-in customer = Amal)\n" + "=" * 70)
    for q in ["What's my balance?",
              "How much did I spend in total?",
              "Show my spending by category",
              "What was my largest transaction?",
              "Show transactions at Carrefour",
              "How much salary was paid in?"]:
        r = tool.answer(q, customer_id=AMAL)
        print(f"\nQ: {q}")
        print(f"   SQL: {r['sql']}")
        print(f"   A  : {r['answer']}")

    print("\n" + "=" * 70 + "\nROW-LEVEL SECURITY: the same question for two customers\n" + "=" * 70)
    print("Amal:", tool.answer("my balance", AMAL)["answer"])
    print("Omar:", tool.answer("my balance", OMAR)["answer"])
    print("-> each only ever sees their OWN accounts; the customer_id comes from")
    print("   the session, not the question, and the views enforce it.")

    print("\n" + "=" * 70 + "\nSAFETY GUARD blocks unsafe generated SQL\n" + "=" * 70)
    for bad in ["DROP TABLE accounts",
                "SELECT * FROM customers",               # base table, not the view
                "SELECT * FROM my_accounts; DELETE FROM accounts",
                "UPDATE accounts SET balance = 0"]:
        try:
            guard(bad)
            print(f"  ALLOWED (bug!): {bad}")
        except SqlSafetyError as e:
            print(f"  BLOCKED: {bad[:40]:<42} -> {e}")

    print("\n" + "=" * 70 + "\nINJECTION attempt is neutralized by parameterization\n" + "=" * 70)
    r = tool.answer("show transactions at 'Carrefour'; DROP TABLE accounts;--", AMAL)
    print("  question carried a SQL-injection payload in the merchant name.")
    print("  result:", r["answer"][:80], "...")
    print("  the payload was BOUND as a literal value, not executed.")


if __name__ == "__main__":
    main()