"""SyneHQ SQL evaluation kit v1. Synthetic fixtures; no model calls or credentials."""
import argparse
import collections
import json
import sqlite3
import time

TASKS = [
    {"id": "paid_revenue", "prompt": "Return paid revenue per country as country,total. Exclude pending orders. Sort country ascending.", "sql": "SELECT country, SUM(amount) AS total FROM orders WHERE status='paid' GROUP BY country ORDER BY country"},
    {"id": "distinct_customers", "prompt": "Return the count of distinct non-null customers on paid orders as customers.", "sql": "SELECT COUNT(DISTINCT customer_id) AS customers FROM orders WHERE status='paid'"},
    {"id": "no_orders", "prompt": "Return customer IDs with no orders as id, ascending. Include customers with no matching order even if some orders have NULL customer IDs.", "sql": "SELECT c.id FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id=c.id) ORDER BY c.id"},
    {"id": "half_open_month", "prompt": "Return paid revenue in January 2026 UTC as total. Include January 1 and exclude February 1.", "sql": "SELECT SUM(amount) AS total FROM orders WHERE status='paid' AND created_at >= '2026-01-01' AND created_at < '2026-02-01'"},
    {"id": "zero_vs_null", "prompt": "Return each customer and paid revenue as id,total. Customers with no paid orders must have zero. Sort by id.", "sql": "SELECT c.id, COALESCE(SUM(CASE WHEN o.status='paid' THEN o.amount ELSE 0 END),0) AS total FROM customers c LEFT JOIN orders o ON o.customer_id=c.id GROUP BY c.id ORDER BY c.id"},
]

def fixture(variant):
    db = sqlite3.connect(":memory:")
    db.executescript("CREATE TABLE customers(id INTEGER PRIMARY KEY); CREATE TABLE orders(id INTEGER PRIMARY KEY,customer_id INTEGER,country TEXT,status TEXT,amount REAL,created_at TEXT);")
    db.executemany("INSERT INTO customers VALUES (?)", [(1,), (2,), (3,), (4,)])
    rows = [(1,1,"IN","paid",10,"2026-01-01"), (2,1,"IN","paid",20,"2026-01-31"), (3,2,"US","pending",999,"2026-01-15"), (4,None,"US","paid",5,"2026-02-01"), (5,2,"US","paid",0,"2026-01-20")]
    if variant == 1: rows += [(6,1,"IN","paid",-3,"2026-01-25"), (7,3,"GB","paid",12,"2025-12-31")]
    if variant == 2: rows = []
    db.executemany("INSERT INTO orders VALUES (?,?,?,?,?,?)", rows)
    db.commit()
    return db

def execute(db, query):
    allowed = {sqlite3.SQLITE_SELECT, sqlite3.SQLITE_READ, sqlite3.SQLITE_FUNCTION, sqlite3.SQLITE_RECURSIVE}
    db.set_authorizer(lambda action, a, b, c, d: sqlite3.SQLITE_OK if action in allowed and not (action == sqlite3.SQLITE_FUNCTION and (b or "").lower() == "load_extension") else sqlite3.SQLITE_DENY)
    deadline = time.monotonic() + 1
    db.set_progress_handler(lambda: int(time.monotonic() > deadline), 1000)
    cursor = db.execute(query)
    rows = cursor.fetchmany(1001)
    if len(rows) > 1000: raise ValueError("Result row budget exceeded")
    return [column[0] for column in cursor.description], rows

def evaluate(answers):
    results = []
    for task in TASKS:
        checks = []
        for variant in range(3):
            db = fixture(variant)
            try:
                expected = execute(db, task["sql"])
                actual = execute(db, answers.get(task["id"], ""))
                checks.append(actual == expected)
            except (sqlite3.Error, ValueError): checks.append(False)
            finally: db.close()
        results.append({"task": task["id"], "passed": all(checks), "fixturesPassed": sum(checks), "fixtures": 3})
    return results

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--answers", help="JSON object mapping task IDs to SQL; generate externally using the published prompts")
    parser.add_argument("--self-test", action="store_true")
    parser.add_argument("--tasks", action="store_true")
    args = parser.parse_args()
    if args.tasks:
        print(json.dumps([{k:v for k,v in t.items() if k != "sql"} for t in TASKS], indent=2)); return
    if args.self_test:
        assert all(r["passed"] for r in evaluate({t["id"]:t["sql"] for t in TASKS}))
        assert not any(r["passed"] for r in evaluate({t["id"]:"DROP TABLE orders" for t in TASKS}))
        print("Harness self-test passed: reference answers pass 15 fixtures; writes are rejected. No model was evaluated."); return
    if not args.answers: parser.error("use --tasks, --self-test, or --answers")
    with open(args.answers) as file: answers = json.load(file)
    print(json.dumps({"harnessVersion":1,"sqliteVersion":sqlite3.sqlite_version,"results":evaluate(answers)}, indent=2))

if __name__ == "__main__": main()