package main

import (
    "context"
    "fmt"
    "log"

    "github.com/portablesql/psql"
    _ "github.com/portablesql/psql-sqlite" // or psql-mysql / psql-pgsql
)

type User struct {
    psql.Name `sql:"users"`
    ID        uint64 `sql:",key=PRIMARY"`
    Username  string `sql:",type=VARCHAR,size=128"`
    Email     string `sql:",type=VARCHAR,size=255,key=UNIQUE:email"`
}

func main() {
    be, err := psql.New(":memory:") // engine detected from the DSN
    if err != nil {
        log.Fatal(err)
    }
    ctx := be.Plug(context.Background())

    // Insert: the table is created (or migrated) on first use
    err = psql.Insert(ctx, &User{ID: 1, Username: "alice", Email: "alice@example.com"})
    if psql.IsDuplicate(err) {
        log.Println("already registered")
    }

    // Load one record, change it, write back only the changed columns
    alice, err := psql.Get[User](ctx, map[string]any{"Email": "alice@example.com"})
    if err != nil {
        log.Fatal(err)
    }
    alice.Username = "alice.smith"
    err = psql.Update(ctx, alice)

    // Transactions (nested ones become savepoints)
    err = psql.Tx(ctx, func(ctx context.Context) error {
        return psql.Insert(ctx, &User{ID: 2, Username: "bob", Email: "bob@example.com"})
    })

    // Iterate over matches (Go 1.23 range-over-func)
    it, err := psql.Iter[User](ctx, map[string]any{"Username": &psql.Like{Like: "a%"}},
        psql.Sort(psql.S("Username", "ASC")))
    for u := range it {
        fmt.Println(u.Username)
    }

    // Query builder: renders ? or $1 placeholders depending on the engine
    query, args, err := psql.B().Select("Username").From("users").
        Where(psql.Gt(psql.F("ID"), 1)).Limit(0, 10).RenderArgs(ctx)
    fmt.Println(query, args, err)
}