package main

import (
    "database/sql"
    "github.com/akhilmhdh/pgsb"
    _ "github.com/lib/pq"
)

func main() {
    db, _ := sql.Open("postgres", "your-connection-string")
    tx, _ := db.Begin()

    s := pgsb.New(tx)

    // Create a users table
    err := s.CreateTable("users", func(t *pgsb.Table) {
        t.Increments("id")
        t.String("email", 255).NotNullable().Unique()
        t.String("name", 255).NotNullable()
        t.Timestamps()
    })

    if err != nil {
        tx.Rollback()
        return
    }

    tx.Commit()
}

t.String("name", 255)           // VARCHAR(255)
t.Text("description")           // TEXT
t.Integer("count")              // INTEGER
t.BigInteger("big_count")       // BIGINT
t.SmallInteger("small_count")   // SMALLINT
t.Float("price")                // REAL
t.Double("big_price")           // DOUBLE PRECISION
t.Decimal("amount", 10, 2)      // DECIMAL(10,2)
t.Boolean("is_active")          // BOOLEAN

t.Date("birth_date")                                    // DATE
t.Time("start_time")                                    // TIME
t.DateTime("created_at")                                // TIMESTAMPTZ
t.DateTime("event_time", DateTimeOptions{              // TIMESTAMPTZ(6)
    UseTz:     true,
    Precision: 6,
})
t.Timestamp("updated_at")                               // Alias for DateTime
t.Timestamps()                                          // created_at & updated_at

t.UUID("external_id")                                   // UUID
t.JSON("data")                                          // JSON
t.JSONB("metadata")                                     // JSONB
t.Binary("file_data")                                   // BYTEA
t.SpecificType("coordinates", "POINT")                  // Custom types
t.SpecificType("tags", "TEXT[]")                        // Arrays

t.Increments("id")                  // SERIAL PRIMARY KEY
t.BigIncrements("id", true)         // BIGSERIAL PRIMARY KEY

t.String("email", 255).NotNullable()                    // NOT NULL
t.String("email", 255).Nullable()                       // Allow NULL (default)
t.String("email", 255).Unique()                         // UNIQUE constraint
t.Integer("user_id").Primary()                          // PRIMARY KEY
t.String("email", 255).DefaultTo("'[email protected]'") // DEFAULT value

t.Integer("age").CheckPositive()                        // age > 0
t.Integer("debt").CheckNegative()                       // debt < 0
t.Integer("score").CheckBetween(0, 100)                 // score BETWEEN 0 AND 100
t.String("status", 20).CheckIn([]string{"active", "inactive"}) // status IN (...)
t.Integer("count").Unsigned()                           // count >= 0

t.Integer("user_id").References("id").InTable("users")
t.Integer("user_id").References("id").InTable("users").OnDelete("CASCADE")
t.Integer("user_id").References("id").InTable("users").OnUpdate("RESTRICT")

// Single column index
t.Index("email")
t.Index("email", "custom_index_name")

// Composite index
t.Index([]string{"user_id", "created_at"})
t.Index([]string{"user_id", "status"}, "idx_user_status")

// Unique constraints
t.Unique("email")
t.Unique([]string{"user_id", "slug"})

// Create enum type and column
t.Enum("status", []string{"draft", "published", "archived"}, EnumOptions{
    UseNative: true,
    EnumName:  "post_status",
}).DefaultTo("'draft'")

// Use existing enum type
t.Enum("status", []string{}, EnumOptions{
    UseNative:    true,
    EnumName:     "post_status",
    ExistingType: true,
})

// Create table
s.CreateTable("users", func(t *Table) {
    t.Increments("id")
    t.String("name", 255).NotNullable()
})

// Create table if not exists
s.CreateTableIfNotExists("users", func(t *Table) {
    t.Increments("id")
    t.String("name", 255).NotNullable()
})

s.AlterTable("users", func(t *TableAlterer) {
    // Add columns
    t.String("avatar_url", 500)
    t.AddColumn("last_login_at", "TIMESTAMPTZ", func(col *Column) {
        col.Nullable = true
    })

    // Rename column
    t.RenameColumn("full_name", "display_name")

    // Drop column
    t.DropColumn("old_field")
})

s.DropTable("users")                // DROP TABLE users
s.DropTableIfExists("users")        // DROP TABLE IF EXISTS users
s.RenameTable("old_users", "users") // ALTER TABLE old_users RENAME TO users

exists, err := s.HasTable("users")
if err != nil {
    return err
}

hasColumn, err := s.HasColumn("users", "email")
if err != nil {
    return err
}

s.CreateTable("orders", func(t *Table) {
    // Primary key
    t.UUID("id").Primary().DefaultTo("gen_random_uuid()")

    // Basic columns
    t.String("order_number", 20).NotNullable().Unique()
    t.BigInteger("user_id").NotNullable()

    // Decimal columns with constraints
    t.Decimal("subtotal", 12, 2).NotNullable().CheckPositive()
    t.Decimal("tax", 12, 2).NotNullable().DefaultTo(0)
    t.Decimal("total", 12, 2).NotNullable()

    // Enum with native PostgreSQL type
    t.Enum("status", []string{"pending", "processing", "shipped", "delivered"},
        EnumOptions{
            UseNative: true,
            EnumName:  "order_status",
        }).NotNullable().DefaultTo("'pending'")

    // Optional columns
    t.Text("notes").Nullable()
    t.DateTime("shipped_at").Nullable()
    t.Timestamps()

    // Foreign key
    t.Foreign("user_id").References("id").InTable("users").OnDelete("CASCADE")

    // Check constraints
    t.Check("total = subtotal + tax", "check_total_calculation")
    t.Check("shipped_at IS NULL OR shipped_at <= delivered_at", "check_shipping_logic")

    // Indexes
    t.Index("user_id")
    t.Index("status")
    t.Index([]string{"created_at", "status"}, "idx_orders_recent_by_status")

    // Table comment
    t.Comment("Orders table with business logic constraints")
})

package migrations

import (
    "context"
    "database/sql"
    "github.com/pressly/goose/v3"
    "github.com/akhilmhdh/pgsb"
)

func init() {
    goose.AddMigrationContext(upCreateUsers, downCreateUsers)
}

func upCreateUsers(ctx context.Context, tx *sql.Tx) error {
    s := pgsb.New(tx)

    return s.CreateTable("users", func(t *pgsb.Table) {
        t.Increments("id")
        t.String("email", 255).NotNullable().Unique()
        t.String("username", 100).NotNullable().Unique()
        t.String("full_name", 255)
        t.Boolean("is_active").DefaultTo(true)
        t.Timestamps()

        t.Index("email")
        t.Comment("Main users table")
    })
}

func downCreateUsers(ctx context.Context, tx *sql.Tx) error {
    s := pgsb.New(tx)
    return s.DropTable("users")
}

s.CreateTable("audit_users", func(t *Table) {
    t.Inherits("audit_base")
    t.Check("table_name = 'users'", "check_users_audit")
})

t.SpecificType("tags", "TEXT[]").DefaultTo("ARRAY[]::TEXT[]")
t.SpecificType("scores", "INTEGER[]")

t.SpecificType("coordinates", "POINT").NotNullable()
t.SpecificType("area", "POLYGON")

t.SpecificType("ip_address", "INET")
t.SpecificType("mac_address", "MACADDR")

// Raw SQL
pgsb.Raw("SELECT NOW()")

// Current timestamp
pgsb.Now() // Returns "CURRENT_TIMESTAMP"