let db# = sql_open#("mydata.db")
if PntToNum(db#) = 0 then
    println "Failed: " + sql_errormsg$()
endif

println "SQLite: " + sql_version$()

' In-memory database
let memdb# = sql_open#()

sql_close(db#)

' Create table
sql_exec(db#, "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")

' Insert with parameters
let params# = json_object#()
json_sets#(params#, "name", "John")
json_setn#(params#, "age", 30)
sql_exec(db#, "INSERT INTO users (name, age) VALUES (:name, :age)", params#)

' Query
let cursor# = sql_query#(db#, "SELECT * FROM users")
while sql_step(cursor#) = 1
    println sql_gets$(cursor#, "name")
end while
sql_finalize(cursor#)

let stmt# = sql_prepare#(db#, "INSERT INTO users (name, age) VALUES (:name, :age)")

let params# = json_object#()
json_sets#(params#, "name", "Alice")
json_setn#(params#, "age", 25)
sql_bindjson#(stmt#, params#)
sql_step(stmt#)

' Reuse with new data
sql_reset#(stmt#)
sql_clearbind#(stmt#)
json_sets#(params#, "name", "Bob")
json_setn#(params#, "age", 35)
sql_bindjson#(stmt#, params#)
sql_step(stmt#)

sql_finalize(stmt#)

let cursor# = sql_query#(db#, "SELECT * FROM users")
while sql_step(cursor#) = 1
    println sql_gets$(cursor#, "name") + " - age " + str$(sql_getn(cursor#, "age"))
    if sql_isn(cursor#, "email") = 1 then
        println "  (no email)"
    endif
end while
sql_finalize(cursor#)

' Insert with JSON
let user# = json_object#()
json_sets#(user#, "name", "Charlie")
json_setn#(user#, "age", 28)
sql_insertjson(db#, "users", user#)
println "New ID: " + str$(sql_lastid(db#))

' Update with JSON
let upd# = json_object#()
json_setn#(upd#, "age", 29)
sql_updatejson(db#, "users", upd#, "name = 'Charlie'")

' Fetch all as JSON array
let cursor# = sql_query#(db#, "SELECT * FROM users")
sql_step(cursor#)
let all# = sql_fetchall#(cursor#)
println json_pretty$(all#)
sql_finalize(cursor#)

sql_begin(db#)

let ok = sql_exec(db#, "UPDATE accounts SET balance = balance - 100 WHERE id = 1")
if ok = 1 then
    ok = sql_exec(db#, "UPDATE accounts SET balance = balance + 100 WHERE id = 2")
endif

if ok = 1 then
    sql_commit(db#)
    println "Transfer complete"
else
    sql_rollback(db#)
    println "Transfer failed"
endif

' Table introspection
let tables# = sql_tables#(db#)
for i = 0 to json_len(tables#) - 1
    let tbl$ = json_items$(tables#, i)
    println "Table: " + tbl$
    
    let cols# = sql_columns#(db#, tbl$)
    for j = 0 to json_len(cols#) - 1
        let col# = json_item#(cols#, j)
        println "  " + json_gets$(col#, "name") + " (" + json_gets$(col#, "type") + ")"
    next
next

let safe$ = sql_escape$("O'Brien")   ' "O''Brien"
let quoted$ = sql_quote$("O'Brien") ' "'O''Brien'"

let db# = sql_open#("contacts.db")
sql_exec(db#, "CREATE TABLE IF NOT EXISTS contacts (id INTEGER PRIMARY KEY, name TEXT, phone TEXT, email TEXT)")

' Insert via JSON
let c1# = json_object#()
json_sets#(c1#, "name", "Alice Smith")
json_sets#(c1#, "phone", "555-0100")
json_sets#(c1#, "email", "alice@example.com")
sql_insertjson(db#, "contacts", c1#)

' Query
let cursor# = sql_query#(db#, "SELECT * FROM contacts ORDER BY name")
while sql_step(cursor#) = 1
    println sql_gets$(cursor#, "name") + " - " + sql_gets$(cursor#, "phone")
end while
sql_finalize(cursor#)

' Update
let upd# = json_object#()
json_sets#(upd#, "email", "alice.smith@example.com")
sql_updatejson(db#, "contacts", upd#, "name = 'Alice Smith'")

sql_close(db#)

let db# = sql_open#("products.db")
sql_exec(db#, "CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY, name TEXT, price REAL, stock INTEGER)")

let products# = json_array#()
let p1# = json_object#()
json_sets#(p1#, "name", "Widget")
json_setn#(p1#, "price", 9.99)
json_setn#(p1#, "stock", 100)
json_push#(products#, p1#)

let p2# = json_object#()
json_sets#(p2#, "name", "Gadget")
json_setn#(p2#, "price", 19.99)
json_setn#(p2#, "stock", 50)
json_push#(products#, p2#)

sql_begin(db#)
for i = 0 to json_len(products#) - 1
    sql_insertjson(db#, "products", json_item#(products#, i))
next
sql_commit(db#)

println "Inserted " + str$(json_len(products#)) + " products"
sql_close(db#)