-- Terlihat innocent, tapi ini bom waktu
SELECT p.*, t.*
FROM products p, tags t
WHERE p.category = 'electronics';
-- Hasil: 50.000 × 200 = 10.000.000 baris

// ❌ Berbahaya: implicit cross join
func GetProductsWithTagsBad(db *sql.DB) ([]ProductTag, error) {
    rows, err := db.Query(`
        SELECT p.id, p.name, t.name as tag_name
        FROM products p, tags t
        WHERE p.category = 'electronics'
    `)
    return scanRows(rows, err)
}

// ✅ Benar: explicit INNER JOIN dengan kondisi
func GetProductsWithTagsGood(db *sqlx.DB, category string, limit, offset int) ([]Product, error) {
    var products []Product
    err := db.Select(&products, `
        SELECT p.id, p.name, p.price
        FROM products p
        INNER JOIN product_tags pt ON pt.product_id = p.id
        INNER JOIN tags t ON t.id = pt.tag_id
        WHERE p.category = $1
        ORDER BY p.id
        LIMIT $2 OFFSET $3
    `, category, limit, offset)
    if err != nil {
        return nil, fmt.Errorf("get products: %w", err)
    }

    productIDs := extractIDs(products)
    tags, err := getTagsByProductIDs(db, productIDs)
    if err != nil {
        return nil, err
    }
    return mergeProductTags(products, tags), nil
}

// ✅ Cursor-based streaming untuk export data besar
func ExportArticlesCursor(ctx context.Context, db *sql.DB, batchSize int) (<-chan Article, <-chan error) {
    articles := make(chan Article, batchSize)
    errc := make(chan error, 1)

    go func() {
        defer close(articles)
        defer close(errc)

        lastID := 0
        for {
            batch, err := fetchBatch(ctx, db, lastID, batchSize)
            if err != nil {
                errc <- err
                return
            }
            if len(batch) == 0 {
                return
            }
            for _, article := range batch {
                select {
                case articles <- article:
                case <-ctx.Done():
                    errc <- ctx.Err()
                    return
                }
            }
            lastID = batch[len(batch)-1].ID
        }
    }()

    return articles, errc
}

func fetchBatch(ctx context.Context, db *sql.DB, afterID, limit int) ([]Article, error) {
    rows, err := db.QueryContext(ctx, `
        SELECT id, title, content, status, created_at
        FROM articles
        WHERE status = 'published' AND id > $1
        ORDER BY id ASC
        LIMIT $2
    `, afterID, limit)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    return scanArticles(rows)
}

// ✅ Deteksi missing index dengan EXPLAIN ANALYZE
func AnalyzeQuery(ctx context.Context, db *sql.DB, query string, args ...interface{}) error {
    explainQuery := "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + query
    var result []byte
    if err := db.QueryRowContext(ctx, explainQuery, args...).Scan(&result); err != nil {
        return err
    }
    if strings.Contains(string(result), "Seq Scan") {
        log.Warn().Str("query", query).Msg("Sequential scan detected — consider adding index")
    }
    return nil
}

// ✅ Index yang tepat untuk pola query umum
/*
-- Composite index untuk query transaksi per user
CREATE INDEX CONCURRENTLY idx_transactions_user_created
ON transactions(user_id, created_at DESC);

-- Partial index untuk status tertentu saja
CREATE INDEX CONCURRENTLY idx_transactions_pending
ON transactions(created_at) WHERE status = 'pending';
*/

// ✅ Query memanfaatkan composite index dengan cursor pagination
func GetUserTransactions(ctx context.Context, db *sql.DB, userID, limit int, cursor *time.Time) ([]Transaction, error) {
    if cursor == nil {
        rows, err := db.QueryContext(ctx, `
            SELECT id, user_id, amount, status, created_at
            FROM transactions
            WHERE user_id = $1
            ORDER BY created_at DESC LIMIT $2
        `, userID, limit)
        defer rows.Close()
        return scanTransactions(rows, err)
    }
    rows, err := db.QueryContext(ctx, `
        SELECT id, user_id, amount, status, created_at
        FROM transactions
        WHERE user_id = $1 AND created_at < $2
        ORDER BY created_at DESC LIMIT $3
    `, userID, cursor, limit)
    defer rows.Close()
    return scanTransactions(rows, err)
}

// ❌ Anti-pattern: ambil semua kolom
func GetAllUsersBad(db *sqlx.DB) ([]User, error) {
    var users []User
    return users, db.Select(&users, "SELECT * FROM users")
}

// ✅ Gunakan DTO spesifik per use case
type UserListItem struct {
    ID   int    `db:"id"`
    Name string `db:"name"`
}

func GetUserListForDropdown(db *sqlx.DB) ([]UserListItem, error) {
    var users []UserListItem
    return users, db.Select(&users, `
        SELECT id, name FROM users
        WHERE active = true ORDER BY name LIMIT 500
    `)
}

// ❌ Salah: tipe tidak cocok dengan kolom VARCHAR
func GetUserByPhoneBad(db *sql.DB, phone int64) (*User, error) {
    row := db.QueryRow("SELECT id, name FROM users WHERE phone_number = $1", phone)
    var u User
    return &u, row.Scan(&u.ID, &u.Name)
}

// ✅ Benar: gunakan string sesuai tipe kolom
type PhoneNumber string

func GetUserByPhoneGood(db *sql.DB, phone PhoneNumber) (*User, error) {
    row := db.QueryRow("SELECT id, name FROM users WHERE phone_number = $1", string(phone))
    var u User
    return &u, row.Scan(&u.ID, &u.Name)
}

// ❌ Anti-pattern: external call di dalam transaksi DB
func BuyTicketBad(ctx context.Context, db *sql.DB, userID, ticketID int) error {
    tx, _ := db.BeginTx(ctx, nil)
    defer tx.Rollback()
    tx.ExecContext(ctx, "UPDATE tickets SET status='reserved' WHERE id=$1", ticketID)
    validateUserWithExternalAPI(userID)  // ❌ 2 detik lock!
    processPayment(userID, ticketID)     // ❌ 3 detik lock!
    return tx.Commit()
}

// ✅ Pisahkan: validasi → lock singkat → payment di luar transaksi
func BuyTicketGood(ctx context.Context, db *sql.DB, userID, ticketID int) error {
    // Step 1: Validasi SEBELUM buka transaksi
    if err := validateUserWithExternalAPI(userID); err != nil {
        return fmt.Errorf("user validation: %w", err)
    }

    // Step 2: Transaksi DB singkat — hanya operasi DB
    reservationID, err := reserveTicketSkipLocked(ctx, db, userID, ticketID)
    if err != nil {
        return fmt.Errorf("reserve ticket: %w", err)
    }

    // Step 3: Payment SETELAH transaksi DB selesai
    if err := processPayment(userID, reservationID); err != nil {
        _ = cancelReservation(ctx, db, reservationID) // compensating transaction
        return fmt.Errorf("process payment: %w", err)
    }

    return confirmTicket(ctx, db, reservationID)
}

// ✅ SELECT FOR UPDATE SKIP LOCKED untuk high-concurrency
func reserveTicketSkipLocked(ctx context.Context, db *sql.DB, userID, ticketID int) (int, error) {
    var id int
    err := db.QueryRowContext(ctx, `
        WITH locked AS (
            SELECT id FROM tickets
            WHERE id = $1 AND status = 'available'
            FOR UPDATE SKIP LOCKED LIMIT 1
        )
        UPDATE tickets SET status = 'reserved', reserved_by = $2, reserved_at = NOW()
        WHERE id IN (SELECT id FROM locked)
        RETURNING id
    `, ticketID, userID).Scan(&id)
    if err == sql.ErrNoRows {
        return 0, ErrTicketNotAvailable
    }
    return id, err
}

// ✅ Audit index yang tidak terpakai di PostgreSQL
const unusedIndexQuery = `
    SELECT
        schemaname,
        tablename,
        indexname,
        idx_scan,
        pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
    FROM pg_stat_user_indexes
    WHERE idx_scan = 0
      AND indexrelname NOT LIKE 'pg_%'
    ORDER BY pg_relation_size(indexrelid) DESC;
`

func AuditUnusedIndexes(ctx context.Context, db *sql.DB) error {
    rows, err := db.QueryContext(ctx, unusedIndexQuery)
    if err != nil {
        return err
    }
    defer rows.Close()

    log.Info().Msg("=== Unused Indexes (candidates for removal) ===")
    for rows.Next() {
        var schema, table, index, size string
        var scans int64
        rows.Scan(&schema, &table, &index, &scans, &size)
        log.Info().Str("index", index).Str("table", table).Str("size", size).Msg("unused index")
    }
    return rows.Err()
}

// ✅ Untuk bulk insert, gunakan COPY protocol via pgx (jauh lebih cepat)
func BulkInsertEvents(ctx context.Context, pool *pgxpool.Pool, events []Event) error {
    _, err := pool.CopyFrom(
        ctx,
        pgx.Identifier{"events"},
        []string{"user_id", "event_type", "payload", "created_at"},
        pgx.CopyFromSlice(len(events), func(i int) ([]interface{}, error) {
            return []interface{}{
                events[i].UserID,
                events[i].Type,
                events[i].Payload,
                events[i].CreatedAt,
            }, nil
        }),
    )
    return err
}

// ❌ Anti-pattern: update langsung ke satu baris
func UpdateGlobalScoreBad(ctx context.Context, db *sql.DB, delta int) error {
    _, err := db.ExecContext(ctx, `
        UPDATE global_stats SET total_score = total_score + $1
    `, delta)
    return err
}

// ✅ Solusi 1: Write buffer — kumpulkan update, flush berkala
type ScoreBuffer struct {
    mu    sync.Mutex
    delta int
}

var buffer = &ScoreBuffer{}

func AddScore(delta int) {
    buffer.mu.Lock()
    buffer.delta += delta
    buffer.mu.Unlock()
}

func FlushScoreBuffer(ctx context.Context, db *sql.DB) error {
    buffer.mu.Lock()
    delta := buffer.delta
    buffer.delta = 0
    buffer.mu.Unlock()

    if delta == 0 {
        return nil
    }
    _, err := db.ExecContext(ctx, `
        UPDATE global_stats SET total_score = total_score + $1
    `, delta)
    return err
}

// ✅ Solusi 2: Sharded counter — pecah satu baris jadi N baris
func UpdateShardedScore(ctx context.Context, db *sql.DB, userID, delta int) error {
    shardID := userID % 16 // 16 shards
    _, err := db.ExecContext(ctx, `
        INSERT INTO score_shards (shard_id, delta)
        VALUES ($1, $2)
    `, shardID, delta)
    return err
}

func GetTotalScore(ctx context.Context, db *sql.DB) (int64, error) {
    var total int64
    err := db.QueryRowContext(ctx, `
        SELECT COALESCE(SUM(delta), 0) FROM score_shards
    `).Scan(&total)
    return total, err
}

// ❌ Anti-pattern: check-then-insert tanpa atomicity
func CreateUserProfileBad(ctx context.Context, db *sql.DB, userID int) error {
    var count int
    db.QueryRowContext(ctx, "SELECT COUNT(*) FROM profiles WHERE user_id = $1", userID).Scan(&count)
    // Race condition window: goroutine lain bisa insert di sini!
    if count == 0 {
        _, err := db.ExecContext(ctx, "INSERT INTO profiles (user_id) VALUES ($1)", userID)
        return err
    }
    return nil
}

// ✅ Gunakan INSERT ... ON CONFLICT (atomic upsert)
func UpsertUserProfile(ctx context.Context, db *sql.DB, userID int, data ProfileData) error {
    _, err := db.ExecContext(ctx, `
        INSERT INTO profiles (user_id, bio, avatar_url, updated_at)
        VALUES ($1, $2, $3, NOW())
        ON CONFLICT (user_id) DO UPDATE SET
            bio = EXCLUDED.bio,
            avatar_url = EXCLUDED.avatar_url,
            updated_at = NOW()
    `, userID, data.Bio, data.AvatarURL)
    return err
}

// ✅ Atau gunakan INSERT ... ON CONFLICT DO NOTHING untuk idempotent insert
func EnsureUserExists(ctx context.Context, db *sql.DB, userID int) error {
    _, err := db.ExecContext(ctx, `
        INSERT INTO users (id, created_at)
        VALUES ($1, NOW())
        ON CONFLICT (id) DO NOTHING
    `, userID)
    return err
}

// ✅ Set isolation level yang tepat sesuai kebutuhan
func GenerateFinancialReport(ctx context.Context, db *sql.DB) (*Report, error) {
    // Snapshot isolation: semua baca dari snapshot yang konsisten
    tx, err := db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelRepeatableRead, // atau LevelSerializable untuk kritis
        ReadOnly:  true,
    })
    if err != nil {
        return nil, err
    }
    defer tx.Rollback()

    // Semua query di sini membaca dari snapshot waktu tx dimulai
    report, err := buildReport(ctx, tx)
    if err != nil {
        return nil, err
    }

    return report, tx.Commit()
}

/*
Panduan isolation level:
- READ COMMITTED (default PostgreSQL): cocok untuk operasi normal
- REPEATABLE READ: cocok untuk laporan yang butuh konsistensi dalam satu transaksi
- SERIALIZABLE: cocok untuk operasi finansial kritis, tapi lebih lambat
*/

// ✅ Optimistic locking dengan version/updated_at column
type UserProfile struct {
    ID        int       `db:"id"`
    Name      string    `db:"name"`
    Email     string    `db:"email"`
    Version   int       `db:"version"` // increment setiap update
    UpdatedAt time.Time `db:"updated_at"`
}

func UpdateUserProfileOptimistic(ctx context.Context, db *sql.DB, profile UserProfile) error {
    result, err := db.ExecContext(ctx, `
        UPDATE users
        SET name = $1, email = $2, version = version + 1, updated_at = NOW()
        WHERE id = $3 AND version = $4  -- hanya update jika version cocok
    `, profile.Name, profile.Email, profile.ID, profile.Version)
    if err != nil {
        return err
    }

    rows, err := result.RowsAffected()
    if err != nil {
        return err
    }
    if rows == 0 {
        // Versi tidak cocok — ada yang sudah update duluan
        return ErrConflict
    }
    return nil
}

// ✅ Pessimistic locking dengan SELECT FOR UPDATE
func UpdateUserProfilePessimistic(ctx context.Context, db *sql.DB, userID int, fn func(*UserProfile) error) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    var profile UserProfile
    err = tx.QueryRowContext(ctx, `
        SELECT id, name, email, version FROM users WHERE id = $1 FOR UPDATE
    `, userID).Scan(&profile.ID, &profile.Name, &profile.Email, &profile.Version)
    if err != nil {
        return err
    }

    if err := fn(&profile); err != nil {
        return err
    }

    _, err = tx.ExecContext(ctx, `
        UPDATE users SET name = $1, email = $2, version = version + 1 WHERE id = $3
    `, profile.Name, profile.Email, profile.ID)
    if err != nil {
        return err
    }

    return tx.Commit()
}

// ✅ Sticky session: setelah write, baca dari primary untuk beberapa waktu
type DBRouter struct {
    primary  *sql.DB
    replicas []*sql.DB
    // map userID → deadline untuk baca dari primary
    stickyMu sync.RWMutex
    sticky   map[int]time.Time
}

func (r *DBRouter) AfterWrite(userID int) {
    r.stickyMu.Lock()
    r.sticky[userID] = time.Now().Add(5 * time.Second)
    r.stickyMu.Unlock()
}

func (r *DBRouter) ReadDB(userID int) *sql.DB {
    r.stickyMu.RLock()
    deadline, ok := r.sticky[userID]
    r.stickyMu.RUnlock()

    if ok && time.Now().Before(deadline) {
        return r.primary // baca dari primary
    }
    return r.replicas[userID%len(r.replicas)] // baca dari replica
}

// ✅ Atau gunakan token-based consistency
func CreatePost(ctx context.Context, db *DBRouter, userID int, content string) (string, error) {
    postID, err := insertPost(ctx, db.primary, userID, content)
    if err != nil {
        return "", err
    }

    // Tandai bahwa user ini butuh read-your-writes untuk 5 detik ke depan
    db.AfterWrite(userID)
    return postID, nil
}

// ❌ Anti-pattern: God table
// users(id, name, email, phone, address, city, country,
//        stripe_customer_id, plan_type, billing_cycle,
//        ui_theme, ui_language, notification_prefs,
//        last_login, login_count, ...240 kolom lainnya)

// ✅ Decompose berdasarkan domain
/*
users(id, name, email, created_at)              -- core identity
user_billing(user_id, stripe_id, plan, cycle)   -- billing domain
user_preferences(user_id, theme, language, notif_settings)  -- preferences
user_activity(user_id, last_login, login_count)  -- analytics
*/

// ✅ Di Golang, fetch hanya domain yang dibutuhkan
type UserCore struct {
    ID    int    `db:"id"`
    Name  string `db:"name"`
    Email string `db:"email"`
}

type UserBilling struct {
    UserID   int    `db:"user_id"`
    StripeID string `db:"stripe_id"`
    Plan     string `db:"plan"`
}

func GetUserWithBilling(ctx context.Context, db *sqlx.DB, userID int) (*UserCore, *UserBilling, error) {
    var core UserCore
    var billing UserBilling

    // Dua query kecil > satu query dari God table
    errCh := make(chan error, 2)
    go func() { errCh <- db.GetContext(ctx, &core, "SELECT id, name, email FROM users WHERE id=$1", userID) }()
    go func() { errCh <- db.GetContext(ctx, &billing, "SELECT user_id, stripe_id, plan FROM user_billing WHERE user_id=$1", userID) }()

    for i := 0; i < 2; i++ {
        if err := <-errCh; err != nil && err != sql.ErrNoRows {
            return nil, nil, err
        }
    }
    return &core, &billing, nil
}

// ❌ Anti-pattern EAV
// page_attributes: page_id=1, key='title', value='Hello'
// page_attributes: page_id=1, key='slug', value='hello'
// page_attributes: page_id=1, key='published', value='true'

// ✅ Solusi 1: Gunakan kolom biasa untuk atribut yang sudah diketahui
/*
CREATE TABLE pages (
    id          SERIAL PRIMARY KEY,
    title       TEXT NOT NULL,
    slug        TEXT UNIQUE NOT NULL,
    published   BOOLEAN DEFAULT false,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);
*/

// ✅ Solusi 2: JSONB untuk atribut yang benar-benar dinamis
/*
CREATE TABLE pages (
    id       SERIAL PRIMARY KEY,
    title    TEXT NOT NULL,
    metadata JSONB DEFAULT '{}'  -- untuk atribut dinamis
);
CREATE INDEX idx_pages_metadata ON pages USING GIN(metadata);
*/

func GetPageWithMetadata(ctx context.Context, db *sql.DB, slug string) (*Page, error) {
    var page Page
    var metadataJSON []byte
    err := db.QueryRowContext(ctx, `
        SELECT id, title, slug, metadata FROM pages WHERE slug = $1
    `, slug).Scan(&page.ID, &page.Title, &page.Slug, &metadataJSON)
    if err != nil {
        return nil, err
    }
    json.Unmarshal(metadataJSON, &page.Metadata)
    return &page, nil
}

// ✅ Query JSONB dengan index
func GetPagesByTag(ctx context.Context, db *sql.DB, tag string) ([]Page, error) {
    rows, err := db.QueryContext(ctx, `
        SELECT id, title, slug
        FROM pages
        WHERE metadata @> $1::jsonb  -- GIN index dipakai!
    `, fmt.Sprintf(`{"tags": ["%s"]}`, tag))
    defer rows.Close()
    return scanPages(rows, err)
}

// ✅ Denormalize field yang sering dibaca bersamaan
/*
-- Daripada join ke products setiap kali, simpan snapshot saat order dibuat
ALTER TABLE order_items ADD COLUMN product_name TEXT;
ALTER TABLE order_items ADD COLUMN product_price NUMERIC(10,2);

-- Isi saat insert
INSERT INTO order_items (order_id, product_id, product_name, product_price, qty)
SELECT $1, p.id, p.name, p.price, $2
FROM products p WHERE p.id = $3;
*/

// ✅ Di Golang, buat DTO yang flat untuk kebutuhan display
type OrderDisplayItem struct {
    ItemID       int     `db:"item_id"`
    ProductName  string  `db:"product_name"`  // denormalized
    ProductPrice float64 `db:"product_price"` // denormalized
    Qty          int     `db:"qty"`
    Subtotal     float64 `db:"subtotal"`
}

func GetOrderItems(ctx context.Context, db *sql.DB, orderID int) ([]OrderDisplayItem, error) {
    rows, err := db.QueryContext(ctx, `
        SELECT id as item_id, product_name, product_price, qty,
               product_price * qty AS subtotal
        FROM order_items
        WHERE order_id = $1
    `, orderID)
    // Tidak ada JOIN! Data sudah tersimpan saat order dibuat.
    defer rows.Close()
    return scanOrderItems(rows, err)
}

// ✅ Konfigurasi pool yang tepat
func NewDB(dsn string) (*sql.DB, error) {
    db, err := sql.Open("pgx", dsn)
    if err != nil {
        return nil, err
    }
    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(10)
    db.SetConnMaxLifetime(5 * time.Minute)
    db.SetConnMaxIdleTime(2 * time.Minute)
    return db, nil
}

// ✅ Pola yang benar: defer rows.Close() SEGERA setelah cek error
func GetCategories(ctx context.Context, db *sql.DB) ([]Category, error) {
    rows, err := db.QueryContext(ctx, "SELECT id, name FROM categories")
    if err != nil {
        return nil, err
    }
    defer rows.Close() // ← SELALU di sini

    var categories []Category
    for rows.Next() {
        var c Category
        if err := rows.Scan(&c.ID, &c.Name); err != nil {
            return nil, err
        }
        categories = append(categories, c)
    }
    return categories, rows.Err()
}

// ✅ Monitor pool health
func MonitorDBPool(db *sql.DB) {
    stats := db.Stats()
    if float64(stats.InUse)/float64(stats.MaxOpenConnections) > 0.8 {
        log.Warn().
            Int("in_use", stats.InUse).
            Int64("wait_count", stats.WaitCount).
            Msg("DB pool usage > 80% — consider increasing MaxOpenConns or optimizing queries")
    }
}

// ✅ Deteksi table bloat
const bloatQuery = `
    SELECT
        tablename,
        pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS total_size,
        pg_size_pretty(pg_relation_size(tablename::regclass)) AS table_size,
        n_dead_tup,
        n_live_tup,
        ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
    FROM pg_stat_user_tables
    WHERE n_dead_tup > 10000
    ORDER BY n_dead_tup DESC;
`

func CheckTableBloat(ctx context.Context, db *sql.DB) error {
    rows, err := db.QueryContext(ctx, bloatQuery)
    if err != nil {
        return err
    }
    defer rows.Close()

    for rows.Next() {
        var table, totalSize, tableSize string
        var deadTup, liveTup int64
        var deadPct float64
        rows.Scan(&table, &totalSize, &tableSize, &deadTup, &liveTup, &deadPct)

        if deadPct > 20 {
            log.Warn().
                Str("table", table).
                Float64("dead_pct", deadPct).
                Str("total_size", totalSize).
                Msg("Table bloat detected — consider VACUUM ANALYZE")
        }
    }
    return nil
}

// ✅ Untuk tabel high-delete, gunakan partitioning daripada DELETE
/*
-- Partisi tabel sessions per minggu
CREATE TABLE sessions (
    id         UUID DEFAULT gen_random_uuid(),
    user_id    INT NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (expires_at);

-- Drop partisi lama = instant cleanup tanpa bloat
DROP TABLE sessions_2026_w01;
*/

// ✅ Gunakan migrasi bertahap untuk zero-downtime

/*
Step 1: Tambah kolom nullable TANPA default (instant di PostgreSQL 11+)
ALTER TABLE orders ADD COLUMN notes TEXT;

Step 2: Backfill data secara bertahap (tidak lock tabel)
UPDATE orders SET notes = '' WHERE id BETWEEN 1 AND 100000 AND notes IS NULL;
UPDATE orders SET notes = '' WHERE id BETWEEN 100001 AND 200000 AND notes IS NULL;
-- dst, dalam batch kecil

Step 3: Set default dan NOT NULL setelah semua baris terisi
ALTER TABLE orders ALTER COLUMN notes SET DEFAULT '';
ALTER TABLE orders ALTER COLUMN notes SET NOT NULL;
*/

// ✅ Di Golang: jalankan backfill sebagai background job
func BackfillColumnInBatches(ctx context.Context, db *sql.DB, batchSize int) error {
    var minID, maxID int
    db.QueryRowContext(ctx, "SELECT MIN(id), MAX(id) FROM orders WHERE notes IS NULL").
        Scan(&minID, &maxID)

    for start := minID; start <= maxID; start += batchSize {
        end := start + batchSize - 1
        _, err := db.ExecContext(ctx, `
            UPDATE orders SET notes = ''
            WHERE id BETWEEN $1 AND $2 AND notes IS NULL
        `, start, end)
        if err != nil {
            return fmt.Errorf("backfill batch %d-%d: %w", start, end, err)
        }

        // Jeda kecil untuk memberi napas ke database
        time.Sleep(10 * time.Millisecond)
    }
    return nil
}

var orders []Order
db.Find(&orders) // 1 query
for _, o := range orders {
    fmt.Println(o.User.Name) // ← GORM fetch user satu per satu!
}
// Total: 1 + N query

// ❌ Lazy loading trap dengan GORM
func GetOrdersWithUserBad(db *gorm.DB) ([]Order, error) {
    var orders []Order
    db.Find(&orders)
    for _, o := range orders {
        _ = o.User.Name // Trigger query per order!
    }
    return orders, nil
}

// ✅ Gunakan Preload (eager loading) di GORM
func GetOrdersWithUserGood(db *gorm.DB) ([]Order, error) {
    var orders []Order
    err := db.Preload("User").Preload("Items").Find(&orders).Error
    // GORM generate: SELECT * FROM orders; SELECT * FROM users WHERE id IN (...);
    return orders, err
}

// ✅ Atau lebih baik: tulis raw query dengan JOIN untuk kontrol penuh
func GetOrdersWithUserRaw(ctx context.Context, db *sqlx.DB, limit int) ([]OrderWithUser, error) {
    var result []OrderWithUser
    err := db.SelectContext(ctx, &result, `
        SELECT o.id, o.total, o.status,
               u.id as user_id, u.name as user_name, u.email as user_email
        FROM orders o
        INNER JOIN users u ON u.id = o.user_id
        WHERE o.status != 'cancelled'
        ORDER BY o.created_at DESC
        LIMIT $1
    `, limit)
    return result, err
}

// ❌ GORM method chaining yang generate query buruk
func GetActiveUsersWithLatestOrderBad(db *gorm.DB) ([]User, error) {
    var users []User
    db.Where("active = ?", true).
        Preload("Orders", func(db *gorm.DB) *gorm.DB {
            return db.Order("created_at desc")
        }).
        Find(&users)
    return users, nil
    // Generated: nested subquery yang tidak optimal
}

// ✅ Raw SQL untuk query kompleks — transparan dan bisa di-EXPLAIN
const activeUsersWithLatestOrderSQL = `
    SELECT DISTINCT ON (u.id)
        u.id, u.name, u.email,
        o.id as latest_order_id,
        o.total as latest_order_total,
        o.created_at as latest_order_at
    FROM users u
    LEFT JOIN orders o ON o.user_id = u.id
    WHERE u.active = true
    ORDER BY u.id, o.created_at DESC NULLS LAST
    LIMIT $1
`

type UserWithLatestOrder struct {
    ID               int        `db:"id"`
    Name             string     `db:"name"`
    Email            string     `db:"email"`
    LatestOrderID    *int       `db:"latest_order_id"`
    LatestOrderTotal *float64   `db:"latest_order_total"`
    LatestOrderAt    *time.Time `db:"latest_order_at"`
}

func GetActiveUsersWithLatestOrderGood(ctx context.Context, db *sqlx.DB, limit int) ([]UserWithLatestOrder, error) {
    var result []UserWithLatestOrder
    err := db.SelectContext(ctx, &result, activeUsersWithLatestOrderSQL, limit)
    return result, err
}