// UserN -> User (Nullable -> Plain)
user := userN.ToUser()

// User -> UserN (Plain -> Nullable)
userN := user.ToUserN()

package main

import (
    "context"
    "database/sql"
    "fmt"

    "yourproject/models"

    _ "github.com/go-sql-driver/mysql"
    "gopkg.in/guregu/null.v3"
)

func main() {
    db, err := sql.Open("mysql", "root:password@tcp(127.0.0.1:3306)/mydb?parseTime=true")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    ctx := context.TODO()
    userModel := models.NewUserModel(db)

    // Create a user
    id, err := userModel.Save(ctx, models.UserN{
        Name:  null.StringFrom("Tom"),
        Email: null.StringFrom("[email protected]"),
        Age:   null.IntFrom(25),
    })
    fmt.Printf("New user ID: %d\n", id)

    // Query a user
    user, err := userModel.Find(ctx, id)
    fmt.Printf("Name: %s, Email: %s\n", user.Name.String, user.Email.String)

    // Convert to Plain struct
    u := user.ToUser()
    fmt.Printf("Name: %s, Age: %d\n", u.Name, u.Age)
}

import (
    "github.com/mylxsw/eloquent/query"
    "github.com/mylxsw/eloquent/migrate"
)

// Switch to PostgreSQL mode (call once at startup, before any database operations)
query.SetDialect(query.PostgreSQLDialect{})
migrate.SetMigrateDialect(migrate.PostgreSQLMigrateDialect{})

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

db, err := sql.Open("mysql", "root:password@tcp(127.0.0.1:3306)/mydb?parseTime=true")
if err != nil {
    panic(err)
}
defer db.Close()

import (
    "database/sql"

    _ "github.com/lib/pq"
    "github.com/mylxsw/eloquent/query"
    "github.com/mylxsw/eloquent/migrate"
)

// Set dialect before opening the connection
query.SetDialect(query.PostgreSQLDialect{})
migrate.SetMigrateDialect(migrate.PostgreSQLMigrateDialect{})

db, err := sql.Open("postgres", "host=127.0.0.1 port=5432 user=postgres password=secret dbname=mydb sslmode=disable")
if err != nil {
    panic(err)
}
defer db.Close()

userModel := models.NewUserModel(db)
ctx := context.TODO()

// Method 1: Using Save with a Nullable struct
id, err := userModel.Save(ctx, models.UserN{
    Name:  null.StringFrom("Tom"),
    Email: null.StringFrom("[email protected]"),
})

// Method 2: Using Create with KV pairs
id, err := userModel.Create(ctx, query.KV{
    models.FieldUserName:  "Tom",
    models.FieldUserEmail: "[email protected]",
})

// Method 3: Converting from a Plain struct
user := models.User{Name: "Tom", Email: "[email protected]"}
id, err := userModel.Save(ctx, user.ToUserN())

// Find by ID
user, err := userModel.Find(ctx, 1)

// Find the first matching record
user, err := userModel.First(ctx, query.Builder().Where("name", "Tom"))

// Get all records
users, err := userModel.Get(ctx)

// Get with conditions
users, err := userModel.Get(ctx, query.Builder().
    Where("age", ">", 18).
    OrderBy("created_at", "DESC").
    Limit(10),
)

// Select specific fields
users, err := userModel.Get(ctx, query.Builder().Select("id", "name"))

// Pagination
users, meta, err := userModel.Paginate(ctx, 1, 15)  // Page 1, 15 per page
// meta.Total    - total record count
// meta.LastPage - last page number
// meta.Page     - current page
// meta.PerPage  - records per page

// Count
count, err := userModel.Count(ctx)

// Check existence
exists, err := userModel.Exists(ctx, query.Builder().Where("email", "[email protected]"))

user, _ := userModel.Find(ctx, 1)

// Method 1: Read directly from Nullable struct
name := user.Name.String           // Direct read, empty string if NULL
id := user.Id.Int64                // Direct int64 read
valid := user.Name.Valid           // Check if field is NULL

// Method 2: Convert to Plain struct
u := user.ToUser()
fmt.Println(u.Name)    // string type
fmt.Println(u.Age)     // int64 type

// Method 3: Convert to a custom struct (matched by field name)
type UserView struct {
    Name  string
    Email string
}
var view UserView
err := user.As(&view)

// Method 1: Update specific fields via model method
affected, err := userModel.UpdateFields(ctx,
    query.KV{models.FieldUserName: "Jerry"},
    query.Builder().Where("id", 1),
)

// Method 2: Save via model instance (updates all modified fields)
user, _ := userModel.Find(ctx, 1)
user.Name = null.StringFrom("Jerry")
err := user.Save(ctx)  // Requires SetModel or obtained via Find

// Delete by ID (soft delete if enabled)
affected, err := userModel.DeleteById(ctx, 1)

// Delete with conditions
affected, err := userModel.Delete(ctx, query.Builder().Where("age", "<", 18))

// Force delete (physical delete even if soft delete is enabled)
affected, err := userModel.ForceDeleteById(ctx, 1)

// Equals (default operator)
query.Builder().Where("name", "Tom")

// Specify operator
query.Builder().Where("age", ">", 18)
query.Builder().Where("name", "LIKE", "%Tom%")

// Available operator constants
query.EQ   // =
query.NEQ  // !=
query.GT   // >
query.GTE  // >=
query.LT   // <
query.LTE  // <=
query.LIKE // LIKE

query.Builder().Where("name", "Tom").OrWhere("name", "Jerry")
// WHERE name = 'Tom' OR name = 'Jerry'

query.Builder().WhereIn("id", 1, 2, 3)
query.Builder().WhereNotIn("status", 0, -1)

// When using slices, convert first
ids := []int{1, 2, 3}
query.Builder().WhereIn("id", query.ToAnys(ids)...)

query.Builder().WhereNull("deleted_at")
query.Builder().WhereNotNull("email")

query.Builder().WhereBetween("age", 18, 30)
query.Builder().WhereNotBetween("age", 0, 17)

// WHERE (name = 'Tom' AND age > 18) OR (name = 'Jerry')
query.Builder().
    WhereGroup(func(builder query.Condition) {
        builder.Where("name", "Tom").Where("age", ">", 18)
    }).
    OrWhereGroup(func(builder query.Condition) {
        builder.Where("name", "Jerry")
    })

keyword := "Tom"
query.Builder().When(
    func() bool { return keyword != "" },
    func(builder query.Condition) {
        builder.Where("name", "LIKE", "%"+keyword+"%")
    },
)

query.Builder().WhereRaw("YEAR(created_at) = ?", 2024)

// WHERE id IN (SELECT user_id FROM orders WHERE amount > 100)
query.Builder().WhereIn("id",
    query.Builder().Table("orders").Select("user_id").Where("amount", ">", 100),
)

// WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)
query.Builder().WhereExist(
    query.Builder().Table("orders").Select(query.Raw("1")).WhereColumn("orders.user_id", "=", "users.id"),
)

query.Builder().
    OrderBy("created_at", "DESC").
    OrderBy("id", "ASC").
    Limit(10).
    Offset(20).
    GroupBy("status").
    Having(func(builder query.Condition) {
        builder.WhereRaw("COUNT(*) > ?", 5)
    })

query.Builder().
    Table("users AS u").
    Select("u.name", "r.name AS role_name").
    LeftJoin("roles AS r", func(builder query.Condition) {
        builder.WhereColumn("u.role_id", "=", "r.id")
    })

query.Builder().Table("table1").Select("name").
    Union(query.Builder().Table("table2").Select("name"), false)  // false=UNION ALL, true=UNION DISTINCT

query.Builder().Select("id", "name", query.Raw("DATE(created_at) AS create_date"))

user, _ := userModel.Find(ctx, 1)

// belongsTo - Query the user's role
role, err := user.Role().First(ctx)

// belongsTo - Create a related object and auto-bind the foreign key
roleId, err := user.Role().Create(ctx, models.RoleN{
    Name: null.StringFrom("admin"),
})

// belongsTo - Associate / Dissociate
err = user.Role().Associate(ctx, role)     // Bind
err = user.Role().Dissociate(ctx)          // Unbind

// hasOne - Query user extension
ext, err := user.UserExt().First(ctx)
exists, err := user.UserExt().Exists(ctx)

// belongsToMany - Query all organizations for the user
orgs, err := user.Organizations().Get(ctx)
count, err := user.Organizations().Count(ctx)

// belongsToMany - Attach / Detach
err = user.Organizations().Attach(ctx, org)      // Insert into pivot table
err = user.Organizations().Detach(ctx, org)      // Remove from pivot table
err = user.Organizations().DetachAll(ctx)         // Remove all associations
orgId, err := user.Organizations().Create(ctx, models.OrganizationN{
    Name: null.StringFrom("Engineering"),
})

// Soft delete (sets deleted_at)
userModel.DeleteById(ctx, 1)

// Normal query (automatically excludes deleted records)
users, _ := userModel.Get(ctx)

// Query including deleted records
users, _ := userModel.WithTrashed().Get(ctx)

// Force delete (physically removes from database)
userModel.ForceDeleteById(ctx, 1)

// Register a global scope (typically in an init function)
models.AddGlobalScopeForUser("active_only", func(builder query.Condition) {
    builder.Where("status", 1)
})

// Automatically applied during queries
users, _ := userModel.Get(ctx) // Automatically adds WHERE status = 1

// Temporarily disable a global scope
users, _ := userModel.WithoutGlobalScopes("active_only").Get(ctx)

// Register a local scope
models.AddLocalScopeForUser("recent", func(builder query.Condition) {
    builder.WhereRaw("created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)")
})

// Explicitly enable
users, _ := userModel.WithLocalScopes("recent").Get(ctx)

import "github.com/mylxsw/eloquent/migrate"

m := migrate.NewManager(db).Init(context.TODO())

// Create a new table
m.Schema("20240101_001").Create("users", func(builder *migrate.Builder) {
    builder.Increments("id")
    builder.String("name", 100).Comment("Username")
    builder.String("email", 255).Unique().Comment("Email")
    builder.String("password", 255)
    builder.Timestamps(0)
})

// Modify an existing table (add columns)
m.Schema("20240101_002").Table("users", func(builder *migrate.Builder) {
    builder.TinyInteger("status", false, true).
        Default(migrate.StringExpr("1")).
        Comment("Status: 0-disabled 1-enabled")
    builder.SoftDeletes("deleted_at", 0)
})

// Run migrations
if err := m.Run(context.TODO()); err != nil {
    panic(err)
}

builder.String("name", 100).
    Nullable(true).          // Allow NULL
    Default(migrate.StringExpr("default_value")). // Default value (string)
    Default(migrate.RawExpr("CURRENT_TIMESTAMP")). // Default value (raw expression)
    Comment("Username").     // Column comment (MySQL only)
    Unique().                // Unique index
    After("id").             // Place after a specific column (MySQL only)
    First().                 // Place as the first column (MySQL only)
    Charset("utf8mb4").      // Character set (MySQL only)
    Collation("utf8mb4_unicode_ci"). // Collation (MySQL only)
    Unsigned().              // Unsigned (MySQL only, ignored on PostgreSQL)
    Change()                 // Modify an existing column (instead of adding)

builder.Index("", "name", "email")         // Create index (name auto-generated)
builder.Index("idx_name", "name")          // Create named index
builder.Unique("", "email")                // Create unique index
builder.Primary("", "id")                  // Create primary key

builder.DropIndex("idx_name")              // Drop index
builder.DropUnique("unique_name")          // Drop unique index
builder.DropColumn("column1", "column2")   // Drop columns

builder.Foreign("", "user_id").
    References("id").
    On("users").
    OnDelete("CASCADE").
    OnUpdate("CASCADE")

builder.DropForeign("foreign_key_name")    // Drop foreign key

// Drop table
m.Schema("20240102_001").Drop("temp_table")
m.Schema("20240102_002").DropIfExists("temp_table")

// Raw SQL migration
m.Schema("20240102_003").Raw("custom_table", func() []string {
    return []string{
        `CREATE TABLE custom_table (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255))`,
    }
})

import "github.com/mylxsw/eloquent/query"

err := eloquent.Transaction(db, func(tx query.Database) error {
    userModel := models.NewUserModel(tx)  // Use tx instead of db

    id, err := userModel.Save(ctx, models.UserN{
        Name: null.StringFrom("Tom"),
    })
    if err != nil {
        return err  // Returning an error triggers automatic rollback
    }

    // ... more operations

    return nil  // Returning nil triggers automatic commit
})

import "github.com/mylxsw/eloquent/event"

em := event.NewEventManager(event.NewMemoryEventStore())
event.SetDispatcher(em)

// Listen for SQL execution events (useful for logging, performance monitoring)
em.Listen(func(evt event.QueryExecutedEvent) {
    fmt.Printf("[SQL] %s | args: %v | time: %s\n", evt.SQL, evt.Bindings, evt.Time)
})

// Listen for transaction events
em.Listen(func(evt event.TransactionBeginningEvent) {
    fmt.Println("Transaction started")
})
em.Listen(func(evt event.TransactionCommittedEvent) {
    fmt.Println("Transaction committed")
})
em.Listen(func(evt event.TransactionRolledBackEvent) {
    fmt.Println("Transaction rolled back")
})

// Listen for migration events
em.Listen(func(evt event.MigrationStartedEvent) {
    fmt.Printf("Running migration: %s\n", evt.SQL)
})

import "github.com/mylxsw/eloquent"

dbOp := eloquent.DB(db)
ctx := context.TODO()

// Insert
id, err := dbOp.Insert(ctx, "users", query.KV{
    "name":  "Tom",
    "email": "[email protected]",
})

// Query
results, err := dbOp.Query(ctx,
    eloquent.Build("users").Select("id", "name").Where("age", ">", 18),
    func(row eloquent.Scanner) (any, error) {
        var id int64
        var name string
        err := row.Scan(&id, &name)
        return map[string]any{"id": id, "name": name}, err
    },
)

// Generic query (Go 1.18+)
type UserRow struct {
    Id   int64
    Name string
}
users, err := eloquent.Query[UserRow](ctx, db,
    eloquent.Build("users").Select("id", "name"),
    func(row eloquent.Scanner) (UserRow, error) {
        var u UserRow
        err := row.Scan(&u.Id, &u.Name)
        return u, err
    },
)

// Raw SQL query
results, err := dbOp.Query(ctx,
    eloquent.Raw("SELECT * FROM users WHERE age > ?", 18),
    func(row eloquent.Scanner) (any, error) {
        // ...
    },
)

// Update
affected, err := dbOp.Update(ctx,
    eloquent.Build("users").Where("id", 1),
    query.KV{"name": "Jerry"},
)

// Delete
affected, err := dbOp.Delete(ctx, eloquent.Build("users").Where("id", 1))

// Execute a statement with no return value
err := dbOp.Statement(ctx, "TRUNCATE TABLE users")