fn jsonStr(key: String, value: String): String = "\"" + key + "\":\"" + value + "\""

fn jsonNum(key: String, value: Int): String = "\"" + key + "\":" + toString(value)

fn jsonObj(content: String): String = toString(" + content + ")

fn insertUser(connId: Int, name: String, email: String): Int with {Console, Postgres} = {
    let sql = "INSERT INTO users (name, email) VALUES ('" + name + "', '" + email + "') RETURNING id"
    Console.print("Inserting user: " + name)
    match Postgres.queryOne(connId, sql) {
    Some(row) => {
    Console.print("  Inserted with ID: " + toString(row.id))
    row.id
},
    None => {
    Console.print("  Insert failed")
    -1
},
}
}

fn getUsers(connId: Int): Unit with {Console, Postgres} = {
    Console.print("Fetching all users...")
    let rows = Postgres.query(connId, "SELECT id, name, email FROM users ORDER BY id")
    Console.print("  Found " + toString(List.length(rows)) + " users:")
    List.forEach(rows, fn(row: { id: Int, name: String, email: String }): Unit => {
    Console.print("    - " + toString(row.id) + ": " + row.name + " <" + row.email + ">")
})
}

fn getUserById(connId: Int, id: Int): Unit with {Console, Postgres} = {
    let sql = "SELECT id, name, email FROM users WHERE id = " + toString(id)
    Console.print("Looking up user " + toString(id) + "...")
    match Postgres.queryOne(connId, sql) {
    Some(row) => Console.print("  Found: " + row.name + " <" + row.email + ">"),
    None => Console.print("  User not found"),
}
}

fn updateUserEmail(connId: Int, id: Int, newEmail: String): Unit with {Console, Postgres} = {
    let sql = "UPDATE users SET email = '" + newEmail + "' WHERE id = " + toString(id)
    Console.print("Updating user " + toString(id) + " email to " + newEmail)
    let affected = Postgres.execute(connId, sql)
    Console.print("  Rows affected: " + toString(affected))
}

fn deleteUser(connId: Int, id: Int): Unit with {Console, Postgres} = {
    let sql = "DELETE FROM users WHERE id = " + toString(id)
    Console.print("Deleting user " + toString(id))
    let affected = Postgres.execute(connId, sql)
    Console.print("  Rows affected: " + toString(affected))
}

fn transactionDemo(connId: Int): Unit with {Console, Postgres} = {
    Console.print("")
    Console.print("=== Transaction Demo ===")
    Console.print("Beginning transaction...")
    Postgres.beginTx(connId)
    insertUser(connId, "TxUser1", "tx1@example.com")
    insertUser(connId, "TxUser2", "tx2@example.com")
    Console.print("Users before commit:")
    getUsers(connId)
    Console.print("Committing transaction...")
    Postgres.commit(connId)
    Console.print("Transaction committed!")
}

fn main(): Unit with {Console, Postgres} = {
    Console.print("========================================")
    Console.print("  PostgreSQL Demo")
    Console.print("========================================")
    Console.print("")
    Console.print("Connecting to PostgreSQL...")
    let connStr = "host=localhost user=testuser password=testpass dbname=testdb"
    let connId = Postgres.connect(connStr)
    Console.print("Connected! Connection ID: " + toString(connId))
    Console.print("")
    Console.print("Creating users table...")
    Postgres.execute(connId, "CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL)")
    Console.print("")
    Console.print("Clearing existing data...")
    Postgres.execute(connId, "DELETE FROM users")
    Console.print("")
    Console.print("=== Inserting Users ===")
    let id1 = insertUser(connId, "Alice", "alice@example.com")
    let id2 = insertUser(connId, "Bob", "bob@example.com")
    let id3 = insertUser(connId, "Charlie", "charlie@example.com")
    Console.print("")
    Console.print("=== All Users ===")
    getUsers(connId)
    Console.print("")
    Console.print("=== Single User Lookup ===")
    getUserById(connId, id2)
    Console.print("")
    Console.print("=== Update User ===")
    updateUserEmail(connId, id2, "bob.new@example.com")
    getUserById(connId, id2)
    Console.print("")
    Console.print("=== Delete User ===")
    deleteUser(connId, id3)
    getUsers(connId)
    Console.print("")
    transactionDemo(connId)
    Console.print("")
    Console.print("=== Final State ===")
    getUsers(connId)
    Console.print("")
    Console.print("Closing connection...")
    Postgres.close(connId)
    Console.print("Done!")
}

let output = run main() with {}