import usqlite, os

DB = "/sqltest.db"
try:
   os.remove(DB)
except OSError:
   pass

print("VER", usqlite.sqlite_version)

con = usqlite.connect(DB)

# executemany() usqlite-style: one multi-statement string, no params
con.executemany(
   "BEGIN TRANSACTION;"
   "CREATE TABLE readings(id INTEGER PRIMARY KEY, name TEXT, temp REAL);"
   "COMMIT;")

# parameterized execute() -> exercises the patched float bind path (REAL param)
for name, temp in (("alice", 3.14), ("bob", 2.71), ("carol", 42.0)):
   con.execute("INSERT INTO readings(name, temp) VALUES (?, ?)", (name, temp))

with con.execute("SELECT id, name, temp FROM readings ORDER BY id") as cur:
   for row in cur:
       print("ROW", row[0], row[1], row[2])

with con.execute("SELECT COUNT(*), SUM(temp) FROM readings") as cur:
   r = list(cur)[0]
   n, total = r[0], r[1]
print("AGG", n, total)
con.close()

# reopen -> proves on-disk persistence + a float parameter in WHERE
con2 = usqlite.connect(DB)
names = []
with con2.execute("SELECT name FROM readings WHERE temp > ? ORDER BY name", (3.0,)) as cur:
   for row in cur:

       names.append(row[0])
con2.close()
print("FILTER", names)

ok = (usqlite.sqlite_version == "3.47.0"
     and n == 3
     and abs(total - 47.85) < 1e-6
     and names == ["alice", "carol"])
print("PEAK", usqlite.mem_peak())
print("RESULT", "PASS" if ok else "FAIL")

from aw.engine import Engine