Jone

package jone

import (
    "github.com/Grandbusta/jone"
    _ "github.com/jackc/pgx/v5/stdlib" // PostgreSQL driver
)

var Config = jone.Config{
    Client: "postgresql",
    Connection: jone.Connection{
        Host:     "localhost",
        Port:     "5432",
        User:     "postgres",
        Password: "password",
        Database: "my_db",
    },
    Migrations: jone.Migrations{
        TableName: "jone_migrations",
    },
}

var DB = jone.New(&Config)

package jone

import (
    "github.com/Grandbusta/jone"
    _ "github.com/go-sql-driver/mysql" // MySQL driver
)

var Config = jone.Config{
    Client: "mysql",
    Connection: jone.Connection{
        Host:     "localhost",
        Port:     "3306",
        User:     "root",
        Password: "password",
        Database: "my_db",
    },
    Migrations: jone.Migrations{
        TableName: "jone_migrations",
    },
}

var DB = jone.New(&Config)

import (
    "time"

    "github.com/Grandbusta/jone"
    _ "github.com/jackc/pgx/v5/stdlib" // Driver
)

var Config = jone.Config{
    Client: "postgresql",
    Connection: jone.Connection{
        Host:     "localhost",
        Port:     "5432",
        User:     "postgres",
        Password: "password",
        Database: "my_db",
    },
    Pool: jone.Pool{
        MaxOpenConns:    10,              // Max open connections (0 = unlimited)
        MaxIdleConns:    5,               // Max idle connections (0 = default 2)
        ConnMaxLifetime: 30 * time.Minute, // Max connection reuse time (0 = no limit)
        ConnMaxIdleTime: 5 * time.Minute,  // Max idle time before close (0 = no limit)
    },
    Migrations: jone.Migrations{
        TableName: "jone_migrations",
    },
}

func Up(s *jone.Schema) {
    s.CreateTable("users", func(t *jone.Table) {
        t.Increments("id")
        t.String("name").Length(100).NotNullable()
        t.String("email").Length(255).NotNullable().Unique()
        t.Text("bio").Nullable()
        t.Boolean("active").Default(true)
        t.Timestamps() // created_at, updated_at
    })
}

t.String("name").Length(100)         // Set length
t.String("email").NotNullable()      // NOT NULL
t.String("status").Default("new")    // Default value
t.String("code").Unique()            // Unique constraint
t.String("notes").Nullable()         // Explicitly nullable
t.String("title").Comment("...")     // Column comment
t.BigInt("user_id").Unsigned()       // Unsigned (MySQL)
t.BigInt("id").Primary()             // Primary key
t.Decimal("price").Precision(10).Scale(2) // DECIMAL(10,2)

// Create index
t.Index("email")
t.Index("first_name", "last_name").Name("idx_full_name")
t.Index("data").Using("gin")  // Index method (btree, hash, gin, gist)

// Unique index
t.Unique("email")
t.Unique("org_id", "slug").Name("uq_org_slug")

t.BigInt("user_id").NotNullable()
t.Foreign("user_id").References("users", "id").OnDelete("CASCADE")
t.Foreign("org_id").References("orgs", "id").OnDelete("SET NULL").OnUpdate("CASCADE")
t.Foreign("custom").References("table", "col").Name("fk_custom_name")

t.Timestamps() // Adds created_at and updated_at

func Up(s *jone.Schema) {
    s.Table("users", func(t *jone.Table) {
        t.String("phone").Nullable()        // Add column
        t.DropColumn("legacy_field")        // Drop column
        t.RenameColumn("name", "full_name") // Rename column
        t.SetNullable("bio")                // Make nullable
        t.DropNullable("email")             // Make not nullable
        t.SetDefault("active", true)        // Set default
        t.DropDefault("active")             // Drop default
        t.DropIndex("email")                // Drop index
        t.DropForeign("user_id")            // Drop foreign key
    })
}

func Down(s *jone.Schema) {
    s.DropTable("users")
    // or
    s.DropTableIfExists("users")
}

s.RenameTable("old_name", "new_name")
s.HasTable("users")           // Check if table exists
s.HasColumn("users", "email") // Check if column exists

// DDL statements
s.Raw("CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\"")
s.Raw("CREATE INDEX CONCURRENTLY idx_users_email ON users(email)")

// Data migrations with parameters
s.Raw("INSERT INTO settings (key, value) VALUES ($1, $2)", "version", "1.0")
s.Raw("UPDATE users SET status = $1 WHERE created_at < $2", "legacy", "2020-01-01")

import jonecfg "myapp/jone"

// Single row insert with map
result, err := jonecfg.DB.Insert(map[string]any{
    "name":  "John",
    "email": "john@example.com",
}).Into("users").Exec()

// Multi-row insert
result, err := jonecfg.DB.Insert([]map[string]any{
    {"name": "John", "email": "john@example.com"},
    {"name": "Jane", "email": "jane@example.com"},
}).Into("users").Exec()

// Struct insert (uses db tags or snake_case field names)
type User struct {
    Name  string `db:"name"`
    Email string `db:"email"`
}
result, err := jonecfg.DB.Insert(User{Name: "John", Email: "john@example.com"}).Into("users").Exec()

// Raw SQL expressions
result, err := jonecfg.DB.Insert(map[string]any{
    "name":       "John",
    "created_at": jone.Fn.Now(), // CURRENT_TIMESTAMP
}).Into("users").Exec()

// Skip conflicting rows (ON CONFLICT DO NOTHING)
result, err := jonecfg.DB.Insert(map[string]any{
    "email": "john@example.com",
    "name":  "John",
}).OnConflict().Ignore().Into("users").Exec()

// Get generated values back (PostgreSQL RETURNING)
rows, err := jonecfg.DB.Insert(map[string]any{
    "name": "John",
}).Into("users").ExecReturning("id")
id := rows[0]["id"]

// SELECT with parameterized WHERE
rows, err := jonecfg.DB.Select("id", "name").From("users").
    Where("age", ">", 18).              // (column, operator, value)
    Where("active", true).              // (column, value) → implied =
    WhereIn("status", []string{"active", "pending"}).
    WhereNull("deleted_at").
    Where(func(g *jone.WhereGroup) {    // parenthesized group
        g.Where("role", "admin").OrWhere("verified", true)
    }).
    OrderBy("created_at", "desc").
    Limit(10).
    Exec()

// First row as a map (sql.ErrNoRows if none)
row, err := jonecfg.DB.Select("*").From("users").Where("id", 1).First()

// All rows as []map[string]any (empty when none match)
users, err := jonecfg.DB.Select("*").From("users").Where("active", true).All()

// Joins — qualified columns are quoted ("users.id" → "users"."id"),
// tables support "as" aliasing
rows, err := jonecfg.DB.Select("users.name", "o.total").From("users").
    Join("orders as o", "users.id", "o.user_id").
    Where("o.total", ">", 100).
    Exec()

// Derived tables — name a subquery with As() and select from it
sub := jone.Select("user_id").From("orders").GroupBy("user_id").As("t")
rows, err := jonecfg.DB.Select("*").From(sub).Exec()

// CTEs, unions, and row locking
rows, err = jonecfg.DB.Select("*").From("totals").
    With("totals", jone.Select("user_id").From("orders").GroupBy("user_id")).
    Exec()
rows, err = jonecfg.DB.Select("name").From("users").
    Union(jone.Select("name").From("archived_users")).Exec()
row, err = jonecfg.DB.Select("*").From("jobs").
    Where("status", "queued").ForUpdate().SkipLocked().First()

// UPDATE — the data is passed directly to Update; SET and WHERE are both
// parameterized
result, err := jonecfg.DB.Update("users", map[string]any{
    "name":       "Alice",
    "updated_at": jone.Fn.Now(),
}).Where("id", 1).Exec()

// Structs work too, using `db` tags with snake_case fallback (same as Insert),
// and there's a single-pair shorthand
result, err = jonecfg.DB.Update("users", user).Where("id", 1).Exec()
result, err = jonecfg.DB.Update("books", "title", "Slaughterhouse Five").Where("id", 42).Exec()

// DELETE
result, err := jonecfg.DB.Delete("users").Where("active", false).Exec()

// TRUNCATE — remove all rows
result, err = jonecfg.DB.Truncate("sessions").Exec()

// Raw SQL — ? placeholders are rebound per dialect ($n on PostgreSQL)
rows, err := jonecfg.DB.RawQuery("SELECT * FROM users WHERE age > ?", 21).All()
result, err = jonecfg.DB.RawQuery("UPDATE users SET status = ? WHERE id = ?", "legacy", 7).Exec()

// jone/migrations/20260123000000_create_users/migration.go
package m20260123000000

import "github.com/Grandbusta/jone"

func Up(s *jone.Schema) {
    s.CreateTable("users", func(t *jone.Table) {
        t.Increments("id")
        t.String("email").NotNullable().Unique()
        t.String("password_hash").NotNullable()
        t.String("name").Length(100)
        t.Boolean("verified").Default(false)
        t.Timestamps()
    })

    s.CreateTable("posts", func(t *jone.Table) {
        t.Increments("id")
        t.BigInt("user_id").NotNullable()
        t.String("title").NotNullable()
        t.Text("content")
        t.Timestamps()

        t.Foreign("user_id").References("users", "id").OnDelete("CASCADE")
        t.Index("user_id")
    })
}

func Down(s *jone.Schema) {
    s.DropTableIfExists("posts")
    s.DropTableIfExists("users")
}