stardb/testing/stardb_test.go

package testing

import (
	"context"
	"errors"
	"strings"
	"sync"
	"sync/atomic"
	"testing"
	"time"

	"b612.me/stardb"
	_ "modernc.org/sqlite"
)

func TestStarDB_Open(t *testing.T) {
	db := &stardb.StarDB{}
	err := db.Open("sqlite", ":memory:")
	if err != nil {
		t.Errorf("Open failed: %v", err)
	}
	defer db.Close()

	err = db.Ping()
	if err != nil {
		t.Errorf("Ping failed: %v", err)
	}
}

func TestStarDB_Query(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	rows, err := db.Query("SELECT * FROM users WHERE age > ?", 25)
	if err != nil {
		t.Fatalf("Query failed: %v", err)
	}
	defer rows.Close()

	if rows.Length() != 2 {
		t.Errorf("Expected 2 rows, got %d", rows.Length())
	}

	if len(rows.Columns()) != 7 {
		t.Errorf("Expected 7 columns, got %d", len(rows.Columns()))
	}
}

func TestStarDB_QueryContext(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()

	rows, err := db.QueryContext(ctx, "SELECT * FROM users WHERE name = ?", "Alice")
	if err != nil {
		t.Fatalf("QueryContext failed: %v", err)
	}
	defer rows.Close()

	if rows.Length() != 1 {
		t.Errorf("Expected 1 row, got %d", rows.Length())
	}
}

func TestStarDB_QueryRaw(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	rows, err := db.QueryRaw("SELECT name FROM users WHERE age > ? ORDER BY name", 25)
	if err != nil {
		t.Fatalf("QueryRaw failed: %v", err)
	}
	defer rows.Close()

	var (
		name  string
		count int
	)
	for rows.Next() {
		if err := rows.Scan(&name); err != nil {
			t.Fatalf("Scan failed: %v", err)
		}
		count++
	}
	if err := rows.Err(); err != nil {
		t.Fatalf("Rows.Err failed: %v", err)
	}
	if count != 2 {
		t.Errorf("Expected 2 rows, got %d", count)
	}
}

func TestStarDB_QueryRawContext(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()

	rows, err := db.QueryRawContext(ctx, "SELECT name FROM users WHERE name = ?", "Alice")
	if err != nil {
		t.Fatalf("QueryRawContext failed: %v", err)
	}
	defer rows.Close()

	if !rows.Next() {
		t.Fatal("Expected at least one row")
	}
	var name string
	if err := rows.Scan(&name); err != nil {
		t.Fatalf("Scan failed: %v", err)
	}
	if name != "Alice" {
		t.Errorf("Expected name Alice, got %s", name)
	}
}

func TestStarDB_QueryRaw_EmptyQuery(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	_, err := db.QueryRaw("   ")
	if !errors.Is(err, stardb.ErrQueryEmpty) {
		t.Fatalf("Expected ErrQueryEmpty, got %v", err)
	}
}

func TestStarDB_ScanEach(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	var names []string
	err := db.ScanEach("SELECT name FROM users ORDER BY name", func(row *stardb.StarResult) error {
		names = append(names, row.MustString("name"))
		return nil
	})
	if err != nil {
		t.Fatalf("ScanEach failed: %v", err)
	}

	if len(names) != 3 {
		t.Fatalf("Expected 3 rows, got %d", len(names))
	}
	if names[0] != "Alice" || names[1] != "Bob" || names[2] != "Charlie" {
		t.Fatalf("Unexpected names: %v", names)
	}
}

func TestStarDB_ScanEach_Stop(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	count := 0
	err := db.ScanEach("SELECT name FROM users ORDER BY name", func(row *stardb.StarResult) error {
		count++
		if row.MustString("name") == "Bob" {
			return stardb.ErrScanStopped
		}
		return nil
	})
	if err != nil {
		t.Fatalf("ScanEach stop failed: %v", err)
	}
	if count != 2 {
		t.Fatalf("Expected callback count 2, got %d", count)
	}
}

func TestStarDB_ScanEach_NilCallback(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	err := db.ScanEach("SELECT name FROM users", nil)
	if !errors.Is(err, stardb.ErrScanFuncNil) {
		t.Fatalf("Expected ErrScanFuncNil, got %v", err)
	}
}

func TestStarDB_ScanEachORM(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	var user User
	var names []string
	err := db.ScanEachORM("SELECT * FROM users ORDER BY name", &user, func(target interface{}) error {
		u := target.(*User)
		names = append(names, u.Name)
		return nil
	})
	if err != nil {
		t.Fatalf("ScanEachORM failed: %v", err)
	}

	if len(names) != 3 {
		t.Fatalf("Expected 3 names, got %d", len(names))
	}
	if names[0] != "Alice" || names[1] != "Bob" || names[2] != "Charlie" {
		t.Fatalf("Unexpected names: %v", names)
	}
}

func TestStarDB_ScanEachORM_NilCallback(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	var user User
	err := db.ScanEachORM("SELECT * FROM users", &user, nil)
	if !errors.Is(err, stardb.ErrScanORMFuncNil) {
		t.Fatalf("Expected ErrScanORMFuncNil, got %v", err)
	}
}

func TestStarDB_PlaceholderDollar(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	db.SetPlaceholderStyle(stardb.PlaceholderDollar)

	rows, err := db.Query("SELECT name FROM users WHERE name = ? AND age = ?", "Alice", 25)
	if err != nil {
		t.Fatalf("Query with dollar placeholders failed: %v", err)
	}
	defer rows.Close()

	if rows.Length() != 1 {
		t.Fatalf("Expected 1 row, got %d", rows.Length())
	}
	if got := rows.Row(0).MustString("name"); got != "Alice" {
		t.Fatalf("Expected Alice, got %s", got)
	}

	_, err = db.Exec("UPDATE users SET age = ? WHERE name = ?", 26, "Alice")
	if err != nil {
		t.Fatalf("Exec with dollar placeholders failed: %v", err)
	}
}

func TestStarDB_SQLHooks(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	db.SetPlaceholderStyle(stardb.PlaceholderDollar)

	var beforeCount int64
	var afterCount int64
	var mu sync.Mutex
	var beforeQuery string
	var afterQuery string
	var afterErr error

	db.SetSQLHooks(
		func(ctx context.Context, query string, args []interface{}) {
			atomic.AddInt64(&beforeCount, 1)
			mu.Lock()
			beforeQuery = query
			mu.Unlock()
		},
		func(ctx context.Context, query string, args []interface{}, duration time.Duration, err error) {
			atomic.AddInt64(&afterCount, 1)
			mu.Lock()
			afterQuery = query
			afterErr = err
			mu.Unlock()
		},
	)

	if _, err := db.Exec("UPDATE users SET age = ? WHERE name = ?", 31, "Bob"); err != nil {
		t.Fatalf("Exec failed: %v", err)
	}

	if atomic.LoadInt64(&beforeCount) == 0 {
		t.Fatal("Expected before hook to be called")
	}
	if atomic.LoadInt64(&afterCount) == 0 {
		t.Fatal("Expected after hook to be called")
	}
	if !strings.Contains(beforeQuery, "$1") {
		t.Fatalf("Expected converted query in before hook, got %s", beforeQuery)
	}
	if !strings.Contains(afterQuery, "$1") {
		t.Fatalf("Expected converted query in after hook, got %s", afterQuery)
	}
	if afterErr != nil {
		t.Fatalf("Expected nil error in after hook, got %v", afterErr)
	}

	_, execErr := db.Exec("UPDATE table_does_not_exist SET age = ? WHERE name = ?", 31, "Bob")
	if execErr == nil {
		t.Fatal("Expected execution error for invalid table")
	}
	if atomic.LoadInt64(&afterCount) < 2 {
		t.Fatalf("Expected after hook call count >= 2, got %d", atomic.LoadInt64(&afterCount))
	}
	if afterErr == nil {
		t.Fatal("Expected after hook to capture non-nil error for failed SQL")
	}
}

func TestStarDB_SQLHooks_SlowThreshold(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	var afterCount int64
	db.SetSQLHooks(
		nil,
		func(ctx context.Context, query string, args []interface{}, duration time.Duration, err error) {
			atomic.AddInt64(&afterCount, 1)
		},
	)

	db.SetSQLSlowThreshold(time.Hour)
	if got := db.SQLSlowThreshold(); got != time.Hour {
		t.Fatalf("Expected threshold 1h, got %v", got)
	}

	if _, err := db.Exec("UPDATE users SET age = ? WHERE name = ?", 41, "Alice"); err != nil {
		t.Fatalf("Exec failed: %v", err)
	}
	if atomic.LoadInt64(&afterCount) != 0 {
		t.Fatalf("Expected after hook to be skipped under threshold, got %d", afterCount)
	}

	_, err := db.Exec("UPDATE table_does_not_exist SET age = ? WHERE name = ?", 31, "Bob")
	if err == nil {
		t.Fatal("Expected execution error for invalid table")
	}
	if atomic.LoadInt64(&afterCount) != 1 {
		t.Fatalf("Expected error path to trigger after hook, got %d", afterCount)
	}

	db.SetSQLSlowThreshold(0)
	if _, err := db.Exec("UPDATE users SET age = ? WHERE name = ?", 42, "Alice"); err != nil {
		t.Fatalf("Exec failed: %v", err)
	}
	if atomic.LoadInt64(&afterCount) != 2 {
		t.Fatalf("Expected after hook to run after disabling threshold, got %d", afterCount)
	}
}

func TestStarDB_SQLHooks_FingerprintMeta(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	db.SetSQLFingerprintEnabled(true)

	var gotMeta stardb.SQLHookMeta
	var metaFound bool
	db.SetSQLHooks(
		nil,
		func(ctx context.Context, query string, args []interface{}, duration time.Duration, err error) {
			gotMeta, metaFound = stardb.SQLHookMetaFromContext(ctx)
		},
	)

	if _, err := db.Exec("UPDATE   users   SET age = ? WHERE name = ?", 31, "Bob"); err != nil {
		t.Fatalf("Exec failed: %v", err)
	}

	if !metaFound {
		t.Fatal("Expected SQL fingerprint metadata in hook context")
	}
	if gotMeta.Fingerprint != "update users set age = ? where name = ?" {
		t.Fatalf("Unexpected fingerprint: %q", gotMeta.Fingerprint)
	}
}

func TestStarDB_SQLHooks_FingerprintMetaMaskLiterals(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	db.SetSQLFingerprintEnabled(true)
	db.SetSQLFingerprintMode(stardb.SQLFingerprintMaskLiterals)

	var gotMeta stardb.SQLHookMeta
	var metaFound bool
	db.SetSQLHooks(
		nil,
		func(ctx context.Context, query string, args []interface{}, duration time.Duration, err error) {
			gotMeta, metaFound = stardb.SQLHookMetaFromContext(ctx)
		},
	)

	if _, err := db.Exec("UPDATE users SET age = 42 WHERE name = 'Bob' AND age < 100"); err != nil {
		t.Fatalf("Exec failed: %v", err)
	}

	if !metaFound {
		t.Fatal("Expected SQL fingerprint metadata in hook context")
	}
	if gotMeta.Fingerprint != "update users set age = ? where name = ? and age < ?" {
		t.Fatalf("Unexpected fingerprint for mask mode: %q", gotMeta.Fingerprint)
	}
}

func TestStarDB_SQLHooks_FingerprintMetaMaskLiterals_StripComments(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	db.SetSQLFingerprintEnabled(true)
	db.SetSQLFingerprintMode(stardb.SQLFingerprintMaskLiterals)
	db.SetSQLFingerprintKeepComments(false) // default, explicit for clarity

	var gotMeta stardb.SQLHookMeta
	var metaFound bool
	db.SetSQLHooks(
		nil,
		func(ctx context.Context, query string, args []interface{}, duration time.Duration, err error) {
			gotMeta, metaFound = stardb.SQLHookMetaFromContext(ctx)
		},
	)

	if _, err := db.Exec("UPDATE users SET age = 42 /* trace:abc */ WHERE name = 'Bob'"); err != nil {
		t.Fatalf("Exec failed: %v", err)
	}

	if !metaFound {
		t.Fatal("Expected SQL fingerprint metadata in hook context")
	}
	if gotMeta.Fingerprint != "update users set age = ? where name = ?" {
		t.Fatalf("Unexpected fingerprint with stripped comments: %q", gotMeta.Fingerprint)
	}
}

func TestStarDB_SQLHooks_FingerprintMetaMaskLiterals_KeepComments(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	db.SetSQLFingerprintEnabled(true)
	db.SetSQLFingerprintMode(stardb.SQLFingerprintMaskLiterals)
	db.SetSQLFingerprintKeepComments(true)

	var gotMeta stardb.SQLHookMeta
	var metaFound bool
	db.SetSQLHooks(
		nil,
		func(ctx context.Context, query string, args []interface{}, duration time.Duration, err error) {
			gotMeta, metaFound = stardb.SQLHookMetaFromContext(ctx)
		},
	)

	if _, err := db.Exec("UPDATE users SET age = 42 /* trace:abc */ WHERE name = 'Bob'"); err != nil {
		t.Fatalf("Exec failed: %v", err)
	}

	if !metaFound {
		t.Fatal("Expected SQL fingerprint metadata in hook context")
	}
	if gotMeta.Fingerprint != "update users set age = ? /* trace:abc */ where name = ?" {
		t.Fatalf("Unexpected fingerprint with kept comments: %q", gotMeta.Fingerprint)
	}
}

func TestStarDB_SQLFingerprintCounter(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	db.SetSQLFingerprintCounterEnabled(true)
	db.SetSQLFingerprintMode(stardb.SQLFingerprintMaskLiterals)
	db.SetSQLFingerprintKeepComments(false)

	if _, err := db.Exec("UPDATE users SET age = 41 WHERE name = 'Alice'"); err != nil {
		t.Fatalf("Exec failed: %v", err)
	}
	if _, err := db.Exec("UPDATE users SET age = 42 WHERE name = 'Bob'"); err != nil {
		t.Fatalf("Exec failed: %v", err)
	}

	counters := db.SQLFingerprintCounters()
	key := "update users set age = ? where name = ?"
	if counters[key] != 2 {
		t.Fatalf("Expected fingerprint %q count=2, got %d", key, counters[key])
	}

	db.ResetSQLFingerprintCounters()
	if got := len(db.SQLFingerprintCounters()); got != 0 {
		t.Fatalf("Expected counters to be reset, got %d", got)
	}
}

func TestStarDB_SQLFingerprintCounterDisabled(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	db.SetSQLFingerprintCounterEnabled(false)
	if _, err := db.Exec("UPDATE users SET age = 31 WHERE name = ?", "Bob"); err != nil {
		t.Fatalf("Exec failed: %v", err)
	}

	if got := len(db.SQLFingerprintCounters()); got != 0 {
		t.Fatalf("Expected no counters when disabled, got %d", got)
	}
}

func TestStarDB_SQLHooks_FingerprintMetaDisabled(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	db.SetSQLFingerprintEnabled(false)

	metaFound := false
	db.SetSQLHooks(
		nil,
		func(ctx context.Context, query string, args []interface{}, duration time.Duration, err error) {
			_, metaFound = stardb.SQLHookMetaFromContext(ctx)
		},
	)

	if _, err := db.Exec("UPDATE users SET age = ? WHERE name = ?", 31, "Bob"); err != nil {
		t.Fatalf("Exec failed: %v", err)
	}
	if metaFound {
		t.Fatal("Expected no SQL fingerprint metadata when disabled")
	}
}

func TestStarDB_Exec(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	result, err := db.Exec("UPDATE users SET age = ? WHERE name = ?", 26, "Alice")
	if err != nil {
		t.Fatalf("Exec failed: %v", err)
	}

	affected, err := result.RowsAffected()
	if err != nil {
		t.Fatalf("RowsAffected failed: %v", err)
	}

	if affected != 1 {
		t.Errorf("Expected 1 row affected, got %d", affected)
	}
}

func TestStarDB_ExecContext(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	ctx := context.Background()
	result, err := db.ExecContext(ctx, "DELETE FROM users WHERE name = ?", "Charlie")
	if err != nil {
		t.Fatalf("ExecContext failed: %v", err)
	}

	affected, err := result.RowsAffected()
	if err != nil {
		t.Fatalf("RowsAffected failed: %v", err)
	}

	if affected != 1 {
		t.Errorf("Expected 1 row affected, got %d", affected)
	}
}

func TestStarDB_QueryStmt(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	rows, err := db.QueryStmt("SELECT * FROM users WHERE age > ?", 25)
	if err != nil {
		t.Fatalf("QueryStmt failed: %v", err)
	}
	defer rows.Close()

	if rows.Length() != 2 {
		t.Errorf("Expected 2 rows, got %d", rows.Length())
	}
}

func TestStarDB_ExecStmt(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	result, err := db.ExecStmt("UPDATE users SET age = ? WHERE name = ?", 27, "Bob")
	if err != nil {
		t.Fatalf("ExecStmt failed: %v", err)
	}

	affected, err := result.RowsAffected()
	if err != nil {
		t.Fatalf("RowsAffected failed: %v", err)
	}

	if affected != 1 {
		t.Errorf("Expected 1 row affected, got %d", affected)
	}
}

func TestStarDB_Prepare(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	stmt, err := db.Prepare("SELECT * FROM users WHERE name = ?")
	if err != nil {
		t.Fatalf("Prepare failed: %v", err)
	}
	defer stmt.Close()

	rows, err := stmt.Query("Alice")
	if err != nil {
		t.Fatalf("Stmt.Query failed: %v", err)
	}
	defer rows.Close()

	if rows.Length() != 1 {
		t.Errorf("Expected 1 row, got %d", rows.Length())
	}
}

func TestStarStmt_QueryRaw(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	stmt, err := db.Prepare("SELECT name FROM users WHERE name = ?")
	if err != nil {
		t.Fatalf("Prepare failed: %v", err)
	}
	defer stmt.Close()

	rows, err := stmt.QueryRaw("Bob")
	if err != nil {
		t.Fatalf("Stmt.QueryRaw failed: %v", err)
	}
	defer rows.Close()

	if !rows.Next() {
		t.Fatal("Expected one row for Bob")
	}
	var name string
	if err := rows.Scan(&name); err != nil {
		t.Fatalf("Scan failed: %v", err)
	}
	if name != "Bob" {
		t.Errorf("Expected Bob, got %s", name)
	}
}

func TestStarStmt_ScanEach(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	stmt, err := db.Prepare("SELECT name FROM users WHERE age >= ? ORDER BY name")
	if err != nil {
		t.Fatalf("Prepare failed: %v", err)
	}
	defer stmt.Close()

	var names []string
	err = stmt.ScanEach(func(row *stardb.StarResult) error {
		names = append(names, row.MustString("name"))
		return nil
	}, 30)
	if err != nil {
		t.Fatalf("Stmt.ScanEach failed: %v", err)
	}
	if len(names) != 2 || names[0] != "Bob" || names[1] != "Charlie" {
		t.Fatalf("Unexpected names: %v", names)
	}
}

func TestStarStmt_ScanEachORM(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	stmt, err := db.Prepare("SELECT * FROM users WHERE age >= ? ORDER BY name")
	if err != nil {
		t.Fatalf("Prepare failed: %v", err)
	}
	defer stmt.Close()

	var user User
	var names []string
	err = stmt.ScanEachORM(&user, func(target interface{}) error {
		u := target.(*User)
		names = append(names, u.Name)
		return nil
	}, 30)
	if err != nil {
		t.Fatalf("Stmt.ScanEachORM failed: %v", err)
	}
	if len(names) != 2 || names[0] != "Bob" || names[1] != "Charlie" {
		t.Fatalf("Unexpected names: %v", names)
	}
}

func TestStarStmt_SQLHooks(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	var (
		beforeCount int64
		afterCount  int64
	)
	db.SetSQLHooks(
		func(ctx context.Context, query string, args []interface{}) {
			atomic.AddInt64(&beforeCount, 1)
		},
		func(ctx context.Context, query string, args []interface{}, duration time.Duration, err error) {
			atomic.AddInt64(&afterCount, 1)
		},
	)

	stmt, err := db.Prepare("SELECT name FROM users WHERE name = ?")
	if err != nil {
		t.Fatalf("Prepare failed: %v", err)
	}
	defer stmt.Close()

	rows, err := stmt.Query("Alice")
	if err != nil {
		t.Fatalf("Stmt.Query failed: %v", err)
	}
	defer rows.Close()

	if rows.Length() != 1 {
		t.Fatalf("Expected 1 row, got %d", rows.Length())
	}
	if atomic.LoadInt64(&beforeCount) == 0 || atomic.LoadInt64(&afterCount) == 0 {
		t.Fatalf("Expected stmt execution to trigger hooks, before=%d after=%d", beforeCount, afterCount)
	}
}

func TestStarDB_Transaction(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	tx, err := db.Begin()
	if err != nil {
		t.Fatalf("Begin failed: %v", err)
	}

	_, err = tx.Exec("UPDATE users SET age = ? WHERE name = ?", 28, "Alice")
	if err != nil {
		tx.Rollback()
		t.Fatalf("Tx.Exec failed: %v", err)
	}

	err = tx.Commit()
	if err != nil {
		t.Fatalf("Commit failed: %v", err)
	}

	// Verify the change
	rows, err := db.Query("SELECT age FROM users WHERE name = ?", "Alice")
	if err != nil {
		t.Fatalf("Query failed: %v", err)
	}
	defer rows.Close()

	age := rows.Row(0).MustInt("age")
	if age != 28 {
		t.Errorf("Expected age 28, got %d", age)
	}
}

func TestStarDB_TransactionRollback(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	tx, err := db.Begin()
	if err != nil {
		t.Fatalf("Begin failed: %v", err)
	}

	_, err = tx.Exec("UPDATE users SET age = ? WHERE name = ?", 99, "Alice")
	if err != nil {
		t.Fatalf("Tx.Exec failed: %v", err)
	}

	err = tx.Rollback()
	if err != nil {
		t.Fatalf("Rollback failed: %v", err)
	}

	// Verify the change was rolled back
	rows, err := db.Query("SELECT age FROM users WHERE name = ?", "Alice")
	if err != nil {
		t.Fatalf("Query failed: %v", err)
	}
	defer rows.Close()

	age := rows.Row(0).MustInt("age")
	if age == 99 {
		t.Errorf("Expected age to be rolled back, but got %d", age)
	}
}

func TestStarTx_QueryRaw(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	tx, err := db.Begin()
	if err != nil {
		t.Fatalf("Begin failed: %v", err)
	}
	defer tx.Rollback()

	rows, err := tx.QueryRaw("SELECT name FROM users WHERE name = ?", "Charlie")
	if err != nil {
		t.Fatalf("Tx.QueryRaw failed: %v", err)
	}
	defer rows.Close()

	if !rows.Next() {
		t.Fatal("Expected one row for Charlie")
	}
	var name string
	if err := rows.Scan(&name); err != nil {
		t.Fatalf("Scan failed: %v", err)
	}
	if name != "Charlie" {
		t.Errorf("Expected Charlie, got %s", name)
	}
}

func TestStarTx_ScanEach(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	tx, err := db.Begin()
	if err != nil {
		t.Fatalf("Begin failed: %v", err)
	}
	defer tx.Rollback()

	var names []string
	err = tx.ScanEach("SELECT name FROM users WHERE age >= ? ORDER BY name", func(row *stardb.StarResult) error {
		names = append(names, row.MustString("name"))
		return nil
	}, 30)
	if err != nil {
		t.Fatalf("Tx.ScanEach failed: %v", err)
	}
	if len(names) != 2 || names[0] != "Bob" || names[1] != "Charlie" {
		t.Fatalf("Unexpected names: %v", names)
	}
}

func TestStarTx_ScanEachORM(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	tx, err := db.Begin()
	if err != nil {
		t.Fatalf("Begin failed: %v", err)
	}
	defer tx.Rollback()

	var user User
	var names []string
	err = tx.ScanEachORM("SELECT * FROM users WHERE age >= ? ORDER BY name", &user, func(target interface{}) error {
		u := target.(*User)
		names = append(names, u.Name)
		return nil
	}, 30)
	if err != nil {
		t.Fatalf("Tx.ScanEachORM failed: %v", err)
	}
	if len(names) != 2 || names[0] != "Bob" || names[1] != "Charlie" {
		t.Fatalf("Unexpected names: %v", names)
	}
}

func TestStarDB_WithTx(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	err := db.WithTx(func(tx *stardb.StarTx) error {
		_, err := tx.Exec("UPDATE users SET age = ? WHERE name = ?", 41, "Alice")
		return err
	})
	if err != nil {
		t.Fatalf("WithTx failed: %v", err)
	}

	rows, err := db.Query("SELECT age FROM users WHERE name = ?", "Alice")
	if err != nil {
		t.Fatalf("Query failed: %v", err)
	}
	defer rows.Close()

	if got := rows.Row(0).MustInt("age"); got != 41 {
		t.Fatalf("Expected age 41, got %d", got)
	}
}

func TestStarDB_WithTx_RollbackOnError(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	expectedErr := errors.New("business error")
	err := db.WithTx(func(tx *stardb.StarTx) error {
		if _, err := tx.Exec("UPDATE users SET age = ? WHERE name = ?", 55, "Alice"); err != nil {
			return err
		}
		return expectedErr
	})
	if !errors.Is(err, expectedErr) {
		t.Fatalf("Expected %v, got %v", expectedErr, err)
	}

	rows, err := db.Query("SELECT age FROM users WHERE name = ?", "Alice")
	if err != nil {
		t.Fatalf("Query failed: %v", err)
	}
	defer rows.Close()

	if got := rows.Row(0).MustInt("age"); got == 55 {
		t.Fatalf("Expected rollback to keep original age, got %d", got)
	}
}

func TestStarDB_SetMaxConnections(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	db.SetMaxOpenConns(10)
	db.SetMaxIdleConns(5)

	stats := db.Stats()
	if stats.MaxOpenConnections != 10 {
		t.Errorf("Expected MaxOpenConnections 10, got %d", stats.MaxOpenConnections)
	}
}

func TestStarDB_ConcurrentRuntimeAndQuery(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()
	db.SetMaxOpenConns(1)
	db.SetMaxIdleConns(1)

	db.SetSQLHooks(
		func(ctx context.Context, query string, args []interface{}) {},
		func(ctx context.Context, query string, args []interface{}, duration time.Duration, err error) {},
	)

	var wg sync.WaitGroup

	for i := 0; i < 4; i++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			for j := 0; j < 100; j++ {
				rows, err := db.Query("SELECT id FROM users WHERE name = ?", "Alice")
				if err != nil {
					t.Errorf("Concurrent query failed: %v", err)
					return
				}
				_ = rows.Close()
			}
		}()
	}

	for i := 0; i < 2; i++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			for j := 0; j < 100; j++ {
				if j%2 == 0 {
					db.SetPlaceholderStyle(stardb.PlaceholderDollar)
					db.SetSQLSlowThreshold(time.Millisecond)
				} else {
					db.SetPlaceholderStyle(stardb.PlaceholderQuestion)
					db.SetSQLSlowThreshold(0)
				}
			}
		}()
	}

	wg.Wait()
}

func TestStarTx_SQLHooks(t *testing.T) {
	db := setupTestDB(t)
	defer db.Close()

	var beforeCount int64
	var afterCount int64
	db.SetSQLHooks(
		func(ctx context.Context, query string, args []interface{}) {
			atomic.AddInt64(&beforeCount, 1)
		},
		func(ctx context.Context, query string, args []interface{}, duration time.Duration, err error) {
			atomic.AddInt64(&afterCount, 1)
		},
	)

	tx, err := db.Begin()
	if err != nil {
		t.Fatalf("Begin failed: %v", err)
	}
	defer tx.Rollback()

	if _, err := tx.Exec("UPDATE users SET age = ? WHERE name = ?", 27, "Alice"); err != nil {
		t.Fatalf("Tx.Exec failed: %v", err)
	}

	if atomic.LoadInt64(&beforeCount) == 0 || atomic.LoadInt64(&afterCount) == 0 {
		t.Fatalf("Expected tx execution to trigger hooks, before=%d after=%d", beforeCount, afterCount)
	}
}