"""Fictional, fixed SQL exercise. Review all files before running. No AI calls."""
from pathlib import Path
from decimal import Decimal
import csv
import hashlib
import json
import platform
import duckdb

base = Path(__file__).resolve().parent
source = base / "orders-fictional.csv"
before = hashlib.sha256(source.read_bytes()).hexdigest()
con = duckdb.connect(":memory:")
# Explicit CSV types: the fixture's amount is a fixed-point decimal.
con.read_csv(str(source), header=True, columns={
    "order_id": "INTEGER", "order_date": "DATE", "category": "VARCHAR",
    "status": "VARCHAR", "amount": "DECIMAL(12,2)"
}).create_view("orders")

def run(name):
    # Only the two supplied, human-authored files are used in this exercise.
    return [{"category": category, "paid_rows": count, "paid_total": str(total)}
            for category, count, total in con.execute(
                (base / name).read_text(encoding="utf-8")).fetchall()]

good = run("reviewed-query.sql")
wrong = run("missing-filter.sql")
# Independent check uses Python's CSV reader and Decimal, not a second SQL query.
manual = {}
with source.open(newline="", encoding="utf-8") as handle:
    for row in csv.DictReader(handle):
        if (row["status"] == "paid"
                and "2026-10-01" <= row["order_date"] < "2026-11-01"):
            counts, total = manual.get(row["category"], (0, Decimal("0.00")))
            manual[row["category"]] = (counts + 1, total + Decimal(row["amount"]))
expected = [{"category": key, "paid_rows": manual[key][0],
             "paid_total": str(manual[key][1])} for key in sorted(manual)]
assert good == expected == [
    {"category": "Studio", "paid_rows": 3, "paid_total": "600.00"},
    {"category": "Workshop", "paid_rows": 2, "paid_total": "300.00"}]
assert wrong == [
    {"category": "Studio", "paid_rows": 4, "paid_total": "1100.00"},
    {"category": "Workshop", "paid_rows": 3, "paid_total": "700.00"}]
assert before == hashlib.sha256(source.read_bytes()).hexdigest()
print(json.dumps({"python": platform.python_version(), "duckdb": duckdb.__version__,
    "data": "nine fictional order rows; authored SQL; no model",
    "reviewed_query": good, "missing_status_filter": wrong,
    "independent_csv_decimal_check": "passed", "source_bytes_unchanged": True}, indent=2))
con.close()