import pool "pg_pool/pool"

main :: proc() {
    // Initialize the pool
    err := pool.init(
        "postgresql://user:password@localhost/mydb", // or from env file
        min_connections = 2,
        max_connections = 10,
    )
    if err != nil {
        fmt.eprintln("Failed to initialize pool:", err)
        return
    }
    defer pool.destroy_pool()
}

rows, err := pool.query("SELECT id, name FROM users WHERE active = true")
if err != nil {
    fmt.eprintln("Query failed:", err)
    return
}
defer pool.release_query(&rows)

for pool.next_row(&rows) {
    id, _ := pool.scan(&rows, int, 0)
    name, _ := pool.scan(&rows, string, 1, context.allocator)
    defer delete(name)  // scan will allocate when required, and accepts a custom allocator
    
    fmt.printf("User: %d - %s\n", id, name)
}

// Safe from SQL injection
user_id := 42
rows, err := pool.query(
    "SELECT email, created_at FROM users WHERE id = $1", 
    args = {user_id}
)
defer pool.release_query(&rows)

// Start transaction
tx, err := pool.begin()
if err != nil {
    return
}
defer pool.rollback(tx)  // rollback is commit-safe; it can always be called with defer and will only execute if not commited

// Perform operations
pool.exec("UPDATE accounts SET balance = balance - $1 WHERE id = $2", 
    tx, args = {100.00, sender_id})
pool.exec("UPDATE accounts SET balance = balance + $1 WHERE id = $2", 
    tx, args = {100.00, receiver_id})

// Commit if everything succeeded
pool.commit(tx)

User :: struct {
    id:         int    `pg:"id"`,
    email:      string `pg:"email"`,
    full_name:  string `pg:"name"`,      // Maps to 'name' column
    active:     bool   `pg:"is_active"`, // Maps to 'is_active' column
    created_at: time.Time `pg:"created_at"`, // Times are currently always assumed UTC (No Locale mapping yet)
}

// Automatic struct mapping
user := pool.query_row_into(
    "SELECT id, email, name, is_active, created_at FROM users WHERE id = $1",
    User,
    args = {user_id}
)

// Safe upcasts
int2_array, _ := pool.scan(&rows, []i16, 0)  // INT2[] → []i16
int2_array, _ := pool.scan(&rows, []i32, 0)  // INT2[] → []i32 (upcast)
int2_array, _ := pool.scan(&rows, []i64, 0)  // INT2[] → []i64 (upcast)

// Unsafe downcasts (returns error)
int8_array, err := pool.scan(&rows, []i32, 0)  // INT8[] → []i32 (error!)

import "core:math/fixed"

// Using Fixed-point for exact decimal arithmetic
price: pool.Numeric  // alias for core:math/fixed.Fixed52_12
fixed.init_from_f64(&price, 123.45)

pool.exec("INSERT INTO products (price) VALUES ($1)", args = {price})

// Reading back - can scan as Numeric for exact value or f64 with possible precision loss
rows, _ := pool.query("SELECT price FROM products")
defer pool.release_query(&rows)
if pool.next_row(&rows) {
    // Exact: NUMERIC → pool.Numeric (maintains up to 3 decimal places exactly)
    exact_price, _ := pool.scan(&rows, pool.Numeric, 0)
    
    // Approximate: NUMERIC → f64 (may lose precision beyond 15-17 significant digits)
    approx_price, _ := pool.scan(&rows, f64, 0)
}

amount: pool.Money  // i64 storing cents
amount = 12345      // $123.45

pool.exec("UPDATE accounts SET balance = $1", args = {amount})

interval := pool.Interval{
    months: 3,
    days: 15,
    microseconds: 7200000000, // 2 hours
}

pool.exec("UPDATE tasks SET reminder = $1", args = {interval})

// Binary format (default)
rows, _ := pool.query("SELECT * FROM users")

// Text format (when needed for compatibility)
rows, _ := pool.query("SELECT * FROM users", result_format = .Text)

pool.init(
    connection_string,
    min_connections = 2,      // Minimum idle connections
    max_connections = 10,     // Maximum total connections
    max_idle_mins = 5,        // Close idle connections after
    total_cnx_memory = 16*MB, // Total memory for all connections
)

stats := pool.get_pool_stats()
fmt.printf("Active: %d, Idle: %d, Total: %d\n", 
    stats.active_connections,
    stats.idle_connections, 
    stats.total_connections)

// Acquire connection manually (usually not needed)
cnx, err := pool.acquire()
if err != nil {
    return
}
defer pool.release(cnx)

// Use connection for multiple operations
rows1, _ := pool.query("SELECT ...", cnx)
rows2, _ := pool.query("SELECT ...", cnx)

// Get memory stats for the entire pool
stats := pool.get_pool_stats()
fmt.printf("Pool Memory Stats:\n")
fmt.printf("  Total allocated: %d KB\n", stats.total_memory / 1024)
fmt.printf("  Peak usage: %d KB\n", stats.peak_memory_used / 1024)
fmt.printf("  Avg query memory: %d KB\n", stats.avg_last_used / 1024)

// Get memory info for a specific query
rows, _ := pool.query("SELECT * FROM large_table")
defer pool.release_query(&rows)

used, allocated := pool.get_query_memory(&rows)
fmt.printf("Query memory: %d KB used of %d KB allocated\n", 
    used / 1024, allocated / 1024)

// Specify arena size when acquiring connection
cnx, _ := pool.acquire(allocation_size = 256 * mem.Kilobyte)
defer pool.release(cnx)

// Or specify for a single query
rows, _ := pool.query(
    "SELECT large_data FROM big_table",
    arena_size = 1 * mem.Megabyte,
)

// Define type hints for binary format transmission
binary_types := []pool.Postgres_Type {
    {oid = pool.OID_INT4, format = .Binary},
    {oid = pool.OID_FLOAT8, format = .Binary},
    {oid = pool.OID_TIMESTAMP, format = .Binary},
}

// Use with exec or query
_, err := pool.exec(
    "INSERT INTO data (id, value, created) VALUES ($1, $2, $3)",
    types = binary_types,
    args = {42, 3.14159, time.now()},
)

// Define a custom UUID writer for insertion
uuid_writer := pool.Postgres_Type {
    oid = pool.OID_UUID,
    format = .Text,
    writer = proc(buf: ^[dynamic]byte, arg: any, format: pq.Format) -> (size: i32) {
        uuid_str := arg.(string)
        p_bytes := transmute([]byte)uuid_str
        append(buf, ..p_bytes)
        append(buf, 0) // nul terminator for text format
        return i32(len(uuid_str))
    },
}

// Use for insertion
_, err := pool.exec(
    "INSERT INTO users (id, email) VALUES ($1, $2)",
    types = []pool.Postgres_Type{uuid_writer, {}},
    args = {"550e8400-e29b-41d4-a716-446655440000", "user@example.com"},
)

// Define a custom UUID reader
uuid_reader := pool.Postgres_Type {
    reader = proc(bytes: []byte, oid: pq.OID, text_mode: bool, allocator: mem.Allocator) -> (any, pool.Error) {
        if text_mode {
            // Text format: UUID as string
            uuid_str := string(bytes)
            return strings.clone(uuid_str, allocator), nil
        } else {
            // Binary format: 16 bytes
            if len(bytes) != 16 {
                return nil, pool.QueryError.InvalidFormat
            }
            // Convert to string format
            return format_uuid_from_bytes(bytes, allocator), nil
        }
    },
}

// Use when scanning
rows, _ := pool.query("SELECT id FROM users")
defer pool.release_query(&rows)

if pool.next_row(&rows) {
    uuid, _ := pool.scan(&rows, string, 0, custom_type = uuid_reader)
    defer delete(uuid)
    fmt.println("UUID:", uuid)
}

Error :: union {
    mem.Allocator_Error,
    Pool_Error,
    Query_Error,
    DB_Error,
}

// Check specific error types
rows, err := pool.query("SELECT ...")
switch e in err {
case Pool_Error:
    if e == .UnableToAcquireConnection {
        // Handle pool exhaustion
    }
case DB_Error:
    fmt.eprintln("Database error:", e.message)
case Query_Error:
    if e == .NoRows {
        // Handle empty result
    }
}