#!/usr/bin/env python3
import json
import sqlite3
from pathlib import Path


connection = sqlite3.connect(":memory:")
connection.executescript(
    """
    create table orders(id integer primary key, status text, amount integer);
    insert into orders values (1, 'paid', 120), (2, 'refunded', 80), (3, 'paid', 200);
    """
)


def verify(sql):
    try:
        rows = connection.execute(sql).fetchall()
        return {"passed": rows == [(320,)], "rows": rows, "feedback": "expected paid total = 320"}
    except sqlite3.Error as exc:
        return {"passed": False, "error": str(exc), "feedback": "SQL must execute before semantic checking"}


attempts = [
    "select sum(amount) from orders",  # 把已退款订单也算进去了
    "select sum(amount) from orders where status = 'paid'",
]
trace = [{"attempt": i + 1, "sql": sql, "verification": verify(sql)} for i, sql in enumerate(attempts)]
result = {"terminal": "success" if trace[-1]["verification"]["passed"] else "failed", "trace": trace}
Path(__file__).with_name("experiment-result.json").write_text(
    json.dumps(result, ensure_ascii=False, indent=2) + "\n", encoding="utf-8"
)
print(json.dumps(result, ensure_ascii=False, indent=2))