"""Offline demo:  python demo.py

The "model" here is a stand-in: it asks for one SQL query (scripted), then writes its answer from the
REAL rows that query returned from sql/project/retail.db. Everything between (the SDK, the tool loop,
the read-only SQL gateway, structured parsing, cost tracking) is the real project code.
With OPENAI_API_KEY set, `python cli.py` runs the same assistant against a real model.
"""

import json
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent.parent))      # for fake_openai.py
from fake_openai import fake_client                                   # noqa: E402

from analyst import Assistant, ReadOnlyDB                             # noqa: E402

QUESTION = "Which product category earned the most revenue from completed orders in 2024?"
SQL = """
SELECT p.category,
       ROUND(SUM(oi.quantity * oi.unit_price * (1 - COALESCE(oi.discount_pct, 0) / 100.0)), 2) AS revenue,
       COUNT(DISTINCT o.order_id) AS orders
FROM order_items oi
JOIN orders o   ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status = 'COMPLETED' AND oi.quantity > 0
GROUP BY p.category
ORDER BY revenue DESC
""".strip()


def stand_in_model(body: dict):
    """Writes the final structured answer from the tool output it was sent (the real query result)."""
    table = next(item["output"] for item in body["input"] if item.get("type") == "function_call_output")
    header, *rows = [line.split(" | ") for line in table.splitlines() if " | " in line]
    top, runner_up = rows[0], rows[1]
    answer = {
        "summary": f"{top[0]} earned the most revenue from completed orders: INR {float(top[1]):,.2f} "
                   f"across {top[2]} orders, ahead of {runner_up[0]} (INR {float(runner_up[1]):,.2f}).",
        "key_numbers": [{"label": f"{r[0]} revenue (INR)", "value": f"{float(r[1]):,.2f}"} for r in rows],
        "sql": [SQL],
        "caveats": ["Lines with zero or negative quantity were excluded.",
                    "Discounts with no value were treated as 0%."],
    }
    return json.dumps(answer)


client = fake_client(script=[{"tool_call": "run_sql", "arguments": {"query": SQL}}], responder=stand_in_model)
assistant = Assistant(client, ReadOnlyDB())
answer, turn = assistant.ask(QUESTION)

print("Q:", QUESTION)
print("tools used:", [(name, (args.get("query", "")[:40] + "…") if args else "") for name, args in turn.tool_calls])
print("\n" + answer.summary)
for kn in answer.key_numbers:
    print(f"  - {kn.label}: {kn.value}")
print("caveats:", "; ".join(answer.caveats))
print(f"\ntokens: {turn.input_tokens} in / {turn.output_tokens} out  ≈ ${turn.cost_usd:.6f} "
      "(fake token counts, real price table)")