stardb/builder_test.go

package stardb

import (
	"reflect"
	"testing"
)

func TestNewQueryBuilder(t *testing.T) {
	qb := NewQueryBuilder("users")

	if qb.table != "users" {
		t.Errorf("Expected table 'users', got '%s'", qb.table)
	}

	if len(qb.columns) != 1 || qb.columns[0] != "*" {
		t.Errorf("Expected default columns ['*'], got %v", qb.columns)
	}
}

func TestQueryBuilder_Select(t *testing.T) {
	qb := NewQueryBuilder("users").Select("id", "name", "email")

	expected := []string{"id", "name", "email"}
	if !reflect.DeepEqual(qb.columns, expected) {
		t.Errorf("Expected columns %v, got %v", expected, qb.columns)
	}
}

func TestQueryBuilder_Where(t *testing.T) {
	qb := NewQueryBuilder("users").
		Where("age > ?", 18).
		Where("active = ?", true)

	if len(qb.where) != 2 {
		t.Errorf("Expected 2 where conditions, got %d", len(qb.where))
	}

	if qb.where[0] != "age > ?" {
		t.Errorf("Expected first where 'age > ?', got '%s'", qb.where[0])
	}

	if qb.where[1] != "active = ?" {
		t.Errorf("Expected second where 'active = ?', got '%s'", qb.where[1])
	}

	if len(qb.whereArgs) != 2 {
		t.Errorf("Expected 2 where args, got %d", len(qb.whereArgs))
	}

	if qb.whereArgs[0] != 18 {
		t.Errorf("Expected first arg 18, got %v", qb.whereArgs[0])
	}

	if qb.whereArgs[1] != true {
		t.Errorf("Expected second arg true, got %v", qb.whereArgs[1])
	}
}

func TestQueryBuilder_OrderBy(t *testing.T) {
	qb := NewQueryBuilder("users").OrderBy("name ASC")

	if qb.orderBy != "name ASC" {
		t.Errorf("Expected orderBy 'name ASC', got '%s'", qb.orderBy)
	}
}

func TestQueryBuilder_Limit(t *testing.T) {
	qb := NewQueryBuilder("users").Limit(10)

	if qb.limit != 10 {
		t.Errorf("Expected limit 10, got %d", qb.limit)
	}
}

func TestQueryBuilder_Offset(t *testing.T) {
	qb := NewQueryBuilder("users").Offset(20)

	if qb.offset != 20 {
		t.Errorf("Expected offset 20, got %d", qb.offset)
	}
}

func TestQueryBuilder_Build_Simple(t *testing.T) {
	qb := NewQueryBuilder("users")
	query, args := qb.Build()

	expectedQuery := "SELECT * FROM users"
	if query != expectedQuery {
		t.Errorf("Expected query '%s', got '%s'", expectedQuery, query)
	}

	if len(args) != 0 {
		t.Errorf("Expected 0 args, got %d", len(args))
	}
}

func TestQueryBuilder_Build_WithSelect(t *testing.T) {
	qb := NewQueryBuilder("users").Select("id", "name", "email")
	query, args := qb.Build()

	expectedQuery := "SELECT id, name, email FROM users"
	if query != expectedQuery {
		t.Errorf("Expected query '%s', got '%s'", expectedQuery, query)
	}

	if len(args) != 0 {
		t.Errorf("Expected 0 args, got %d", len(args))
	}
}

func TestQueryBuilder_Build_WithWhere(t *testing.T) {
	qb := NewQueryBuilder("users").
		Where("age > ?", 18).
		Where("active = ?", true)

	query, args := qb.Build()

	expectedQuery := "SELECT * FROM users WHERE age > ? AND active = ?"
	if query != expectedQuery {
		t.Errorf("Expected query '%s', got '%s'", expectedQuery, query)
	}

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

	if args[0] != 18 {
		t.Errorf("Expected first arg 18, got %v", args[0])
	}

	if args[1] != true {
		t.Errorf("Expected second arg true, got %v", args[1])
	}
}

func TestQueryBuilder_Build_WithOrderBy(t *testing.T) {
	qb := NewQueryBuilder("users").OrderBy("created_at DESC")
	query, args := qb.Build()

	expectedQuery := "SELECT * FROM users ORDER BY created_at DESC"
	if query != expectedQuery {
		t.Errorf("Expected query '%s', got '%s'", expectedQuery, query)
	}

	if len(args) != 0 {
		t.Errorf("Expected 0 args, got %d", len(args))
	}
}

func TestQueryBuilder_Build_WithLimit(t *testing.T) {
	qb := NewQueryBuilder("users").Limit(10)
	query, args := qb.Build()

	expectedQuery := "SELECT * FROM users LIMIT 10"
	if query != expectedQuery {
		t.Errorf("Expected query '%s', got '%s'", expectedQuery, query)
	}

	if len(args) != 0 {
		t.Errorf("Expected 0 args, got %d", len(args))
	}
}

func TestQueryBuilder_Build_WithOffset(t *testing.T) {
	qb := NewQueryBuilder("users").Offset(20)
	query, args := qb.Build()

	expectedQuery := "SELECT * FROM users OFFSET 20"
	if query != expectedQuery {
		t.Errorf("Expected query '%s', got '%s'", expectedQuery, query)
	}

	if len(args) != 0 {
		t.Errorf("Expected 0 args, got %d", len(args))
	}
}

func TestQueryBuilder_Build_WithLimitAndOffset(t *testing.T) {
	qb := NewQueryBuilder("users").Limit(10).Offset(20)
	query, args := qb.Build()

	expectedQuery := "SELECT * FROM users LIMIT 10 OFFSET 20"
	if query != expectedQuery {
		t.Errorf("Expected query '%s', got '%s'", expectedQuery, query)
	}

	if len(args) != 0 {
		t.Errorf("Expected 0 args, got %d", len(args))
	}
}

func TestQueryBuilder_Build_Complex(t *testing.T) {
	qb := NewQueryBuilder("users").
		Select("id", "name", "email", "age").
		Where("age > ?", 18).
		Where("active = ?", true).
		Where("country = ?", "US").
		OrderBy("name ASC").
		Limit(10).
		Offset(20)

	query, args := qb.Build()

	expectedQuery := "SELECT id, name, email, age FROM users WHERE age > ? AND active = ? AND country = ? ORDER BY name ASC LIMIT 10 OFFSET 20"
	if query != expectedQuery {
		t.Errorf("Expected query:\n%s\nGot:\n%s", expectedQuery, query)
	}

	expectedArgs := []interface{}{18, true, "US"}
	if len(args) != len(expectedArgs) {
		t.Errorf("Expected %d args, got %d", len(expectedArgs), len(args))
	}

	for i, expected := range expectedArgs {
		if args[i] != expected {
			t.Errorf("Expected arg[%d] = %v, got %v", i, expected, args[i])
		}
	}
}

func TestQueryBuilder_Build_MultipleWhere(t *testing.T) {
	qb := NewQueryBuilder("orders").
		Where("user_id = ?", 123).
		Where("status IN (?, ?, ?)", "pending", "processing", "shipped").
		Where("created_at > ?", "2024-01-01")

	query, args := qb.Build()

	expectedQuery := "SELECT * FROM orders WHERE user_id = ? AND status IN (?, ?, ?) AND created_at > ?"
	if query != expectedQuery {
		t.Errorf("Expected query:\n%s\nGot:\n%s", expectedQuery, query)
	}

	if len(args) != 5 {
		t.Errorf("Expected 5 args, got %d", len(args))
	}

	expectedArgs := []interface{}{123, "pending", "processing", "shipped", "2024-01-01"}
	for i, expected := range expectedArgs {
		if args[i] != expected {
			t.Errorf("Expected arg[%d] = %v, got %v", i, expected, args[i])
		}
	}
}

func TestQueryBuilder_Chaining(t *testing.T) {
	// Test method chaining returns the same builder
	qb := NewQueryBuilder("users")

	qb2 := qb.Select("id", "name")
	if qb != qb2 {
		t.Error("Select should return the same builder instance")
	}

	qb3 := qb.Where("age > ?", 18)
	if qb != qb3 {
		t.Error("Where should return the same builder instance")
	}

	qb4 := qb.OrderBy("name ASC")
	if qb != qb4 {
		t.Error("OrderBy should return the same builder instance")
	}

	qb5 := qb.Limit(10)
	if qb != qb5 {
		t.Error("Limit should return the same builder instance")
	}

	qb6 := qb.Offset(20)
	if qb != qb6 {
		t.Error("Offset should return the same builder instance")
	}

	qb7 := qb.Join("LEFT JOIN orders o ON o.user_id = users.id")
	if qb != qb7 {
		t.Error("Join should return the same builder instance")
	}

	qb8 := qb.GroupBy("users.id")
	if qb != qb8 {
		t.Error("GroupBy should return the same builder instance")
	}

	qb9 := qb.Having("COUNT(o.id) > ?", 1)
	if qb != qb9 {
		t.Error("Having should return the same builder instance")
	}
}

func TestQueryBuilder_EmptyWhere(t *testing.T) {
	qb := NewQueryBuilder("users").Select("id", "name")
	query, args := qb.Build()

	expectedQuery := "SELECT id, name FROM users"
	if query != expectedQuery {
		t.Errorf("Expected query '%s', got '%s'", expectedQuery, query)
	}

	if len(args) != 0 {
		t.Errorf("Expected 0 args, got %d", len(args))
	}
}

func TestQueryBuilder_OnlyLimit(t *testing.T) {
	qb := NewQueryBuilder("users").Limit(5)
	query, args := qb.Build()

	expectedQuery := "SELECT * FROM users LIMIT 5"
	if query != expectedQuery {
		t.Errorf("Expected query '%s', got '%s'", expectedQuery, query)
	}

	if len(args) != 0 {
		t.Errorf("Expected 0 args, got %d", len(args))
	}
}

func TestQueryBuilder_OnlyOffset(t *testing.T) {
	qb := NewQueryBuilder("users").Offset(10)
	query, args := qb.Build()

	expectedQuery := "SELECT * FROM users OFFSET 10"
	if query != expectedQuery {
		t.Errorf("Expected query '%s', got '%s'", expectedQuery, query)
	}

	if len(args) != 0 {
		t.Errorf("Expected 0 args, got %d", len(args))
	}
}

func TestQueryBuilder_ZeroLimit(t *testing.T) {
	qb := NewQueryBuilder("users").Limit(0)
	query, args := qb.Build()

	// Limit 0 should not be included
	expectedQuery := "SELECT * FROM users"
	if query != expectedQuery {
		t.Errorf("Expected query '%s', got '%s'", expectedQuery, query)
	}

	if len(args) != 0 {
		t.Errorf("Expected 0 args, got %d", len(args))
	}
}

func TestQueryBuilder_ZeroOffset(t *testing.T) {
	qb := NewQueryBuilder("users").Offset(0)
	query, args := qb.Build()

	// Offset 0 should not be included
	expectedQuery := "SELECT * FROM users"
	if query != expectedQuery {
		t.Errorf("Expected query '%s', got '%s'", expectedQuery, query)
	}

	if len(args) != 0 {
		t.Errorf("Expected 0 args, got %d", len(args))
	}
}

func TestQueryBuilder_MultipleSelect(t *testing.T) {
	// Last Select should override previous ones
	qb := NewQueryBuilder("users").
		Select("id", "name").
		Select("email", "age")

	query, _ := qb.Build()

	expectedQuery := "SELECT email, age FROM users"
	if query != expectedQuery {
		t.Errorf("Expected query '%s', got '%s'", expectedQuery, query)
	}
}

func TestQueryBuilder_MultipleOrderBy(t *testing.T) {
	// Last OrderBy should override previous ones
	qb := NewQueryBuilder("users").
		OrderBy("name ASC").
		OrderBy("created_at DESC")

	query, _ := qb.Build()

	expectedQuery := "SELECT * FROM users ORDER BY created_at DESC"
	if query != expectedQuery {
		t.Errorf("Expected query '%s', got '%s'", expectedQuery, query)
	}
}

func TestQueryBuilder_ComplexWhereConditions(t *testing.T) {
	qb := NewQueryBuilder("products").
		Select("id", "name", "price").
		Where("category_id = ?", 5).
		Where("price BETWEEN ? AND ?", 10.0, 100.0).
		Where("stock > ?", 0).
		Where("name LIKE ?", "%phone%").
		OrderBy("price DESC").
		Limit(20)

	query, args := qb.Build()

	expectedQuery := "SELECT id, name, price FROM products WHERE category_id = ? AND price BETWEEN ? AND ? AND stock > ? AND name LIKE ? ORDER BY price DESC LIMIT 20"
	if query != expectedQuery {
		t.Errorf("Expected query:\n%s\nGot:\n%s", expectedQuery, query)
	}

	expectedArgs := []interface{}{5, 10.0, 100.0, 0, "%phone%"}
	if len(args) != len(expectedArgs) {
		t.Errorf("Expected %d args, got %d", len(expectedArgs), len(args))
	}

	for i, expected := range expectedArgs {
		if args[i] != expected {
			t.Errorf("Expected arg[%d] = %v, got %v", i, expected, args[i])
		}
	}
}

func TestQueryBuilder_SingleColumn(t *testing.T) {
	qb := NewQueryBuilder("users").Select("COUNT(*)")
	query, args := qb.Build()

	expectedQuery := "SELECT COUNT(*) FROM users"
	if query != expectedQuery {
		t.Errorf("Expected query '%s', got '%s'", expectedQuery, query)
	}

	if len(args) != 0 {
		t.Errorf("Expected 0 args, got %d", len(args))
	}
}

func TestQueryBuilder_JoinLikeWhere(t *testing.T) {
	// Test that WHERE can handle JOIN-like conditions
	qb := NewQueryBuilder("users").
		Select("users.id", "users.name", "orders.total").
		Where("users.id = orders.user_id").
		Where("orders.status = ?", "completed")

	query, args := qb.Build()

	expectedQuery := "SELECT users.id, users.name, orders.total FROM users WHERE users.id = orders.user_id AND orders.status = ?"
	if query != expectedQuery {
		t.Errorf("Expected query:\n%s\nGot:\n%s", expectedQuery, query)
	}

	if len(args) != 1 {
		t.Errorf("Expected 1 arg, got %d", len(args))
	}

	if args[0] != "completed" {
		t.Errorf("Expected arg 'completed', got %v", args[0])
	}
}

func TestQueryBuilder_Build_WithJoinGroupByHaving(t *testing.T) {
	qb := NewQueryBuilder("users u").
		Select("u.id", "u.name", "COUNT(o.id) AS order_count").
		Join("LEFT JOIN orders o ON o.user_id = u.id").
		Where("u.active = ?", true).
		GroupBy("u.id", "u.name").
		Having("COUNT(o.id) > ?", 2).
		OrderBy("order_count DESC")

	query, args := qb.Build()

	expectedQuery := "SELECT u.id, u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE u.active = ? GROUP BY u.id, u.name HAVING COUNT(o.id) > ? ORDER BY order_count DESC"
	if query != expectedQuery {
		t.Errorf("Expected query:\n%s\nGot:\n%s", expectedQuery, query)
	}

	expectedArgs := []interface{}{true, 2}
	if len(args) != len(expectedArgs) {
		t.Fatalf("Expected %d args, got %d", len(expectedArgs), len(args))
	}
	for i, expected := range expectedArgs {
		if args[i] != expected {
			t.Errorf("Expected arg[%d] = %v, got %v", i, expected, args[i])
		}
	}
}

func TestQueryBuilder_Build_HavingWithoutWhere(t *testing.T) {
	qb := NewQueryBuilder("orders").
		Select("user_id", "COUNT(*) AS cnt").
		GroupBy("user_id").
		Having("COUNT(*) >= ?", 3)

	query, args := qb.Build()

	expectedQuery := "SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id HAVING COUNT(*) >= ?"
	if query != expectedQuery {
		t.Errorf("Expected query '%s', got '%s'", expectedQuery, query)
	}
	if len(args) != 1 || args[0] != 3 {
		t.Errorf("Expected args [3], got %v", args)
	}
}

// Benchmark tests
func BenchmarkQueryBuilder_Simple(b *testing.B) {
	for i := 0; i < b.N; i++ {
		qb := NewQueryBuilder("users")
		qb.Build()
	}
}

func BenchmarkQueryBuilder_Complex(b *testing.B) {
	for i := 0; i < b.N; i++ {
		qb := NewQueryBuilder("users").
			Select("id", "name", "email", "age").
			Where("age > ?", 18).
			Where("active = ?", true).
			Where("country = ?", "US").
			OrderBy("name ASC").
			Limit(10).
			Offset(20)
		qb.Build()
	}
}