select_test.go

package squildx

import (
	"errors"
	"testing"
	"time"
)

func TestSelectOnly(t *testing.T) {
	q, params, err := New().Select("*").From("users").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if q != "SELECT * FROM users" {
		t.Errorf("got: %s", q)
	}
	if len(params) != 0 {
		t.Errorf("expected 0 params, got %d", len(params))
	}
}

func TestNoWhereClause(t *testing.T) {
	q, params, err := New().Select("id", "name").From("users").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}

	if q != "SELECT id, name FROM users" {
		t.Errorf("got: %s", q)
	}
	if len(params) != 0 {
		t.Errorf("expected 0 params, got %d", len(params))
	}
}

func TestErrNoColumns(t *testing.T) {
	_, _, err := New().Select().From("users").Build()
	if !errors.Is(err, ErrNoColumns) {
		t.Errorf("expected ErrNoColumns, got: %v", err)
	}
}

func TestSelectAppends(t *testing.T) {
	q, _, err := New().Select("id").Select("name", "email").From("users").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}

	expected := "SELECT id, name, email FROM users"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestRemoveSelect(t *testing.T) {
	q, _, err := New().Select("id", "name", "email").RemoveSelect("name").From("users").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}

	expected := "SELECT id, email FROM users"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestRemoveSelectImmutability(t *testing.T) {
	base := New().Select("id", "name", "email").From("users")
	reduced := base.RemoveSelect("name")

	q1, _, err := base.Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	q2, _, err := reduced.Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}

	if q1 != "SELECT id, name, email FROM users" {
		t.Errorf("base was mutated: %s", q1)
	}
	if q2 != "SELECT id, email FROM users" {
		t.Errorf("reduced mismatch: %s", q2)
	}
}

type testUser struct {
	ID        int       `db:"id"`
	FirstName string    `db:"first_name"`
	Email     string    `db:"email"`
	CreatedAt time.Time `db:"created_at"`
}

func TestSelectObjectBasic(t *testing.T) {
	q, _, err := New().SelectObject(testUser{}).From("users").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT id, first_name, email, created_at FROM users"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestSelectObjectPointer(t *testing.T) {
	var u *testUser
	q, _, err := New().SelectObject(u).From("users").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT id, first_name, email, created_at FROM users"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestSelectObjectTablePrefix(t *testing.T) {
	q, _, err := New().SelectObject(testUser{}, "u").From("users u").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT u.id, u.first_name, u.email, u.created_at FROM users u"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestSelectObjectTagPriority(t *testing.T) {
	type row struct {
		A string `squildx:"col_a" db:"wrong_a" json:"also_wrong_a"`
		B string `db:"col_b" json:"wrong_b"`
		C string `json:"col_c,omitempty"`
		D string
	}
	q, _, err := New().SelectObject(row{}).From("t").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT col_a, col_b, col_c, d FROM t"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestSelectObjectSkipDash(t *testing.T) {
	type row struct {
		ID      int    `db:"id"`
		Secret  string `db:"-"`
		Visible string `db:"visible"`
	}
	q, _, err := New().SelectObject(row{}).From("t").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT id, visible FROM t"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestSelectObjectUnexportedSkipped(t *testing.T) {
	type row struct {
		ID       int `db:"id"`
		internal string
	}
	_ = row{internal: "x"}
	q, _, err := New().SelectObject(row{}).From("t").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT id FROM t"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestSelectObjectNamedStructIncluded(t *testing.T) {
	type Nested struct {
		Val string
	}
	type row struct {
		ID     int    `db:"id"`
		Nested Nested `db:"nested"` // named struct field, included as column
	}
	q, _, err := New().SelectObject(row{}).From("t").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT id, nested FROM t"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestSelectObjectSnakeCaseFallback(t *testing.T) {
	type row struct {
		UserID    int
		FirstName string
		HTTPCode  int
	}
	q, _, err := New().SelectObject(row{}).From("t").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT user_id, first_name, http_code FROM t"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestSelectObjectAppendWithSelect(t *testing.T) {
	q, _, err := New().Select("COUNT(*) AS total").SelectObject(testUser{}).From("users").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT COUNT(*) AS total, id, first_name, email, created_at FROM users"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestSelectObjectNonStructError(t *testing.T) {
	_, _, err := New().SelectObject("not a struct").From("t").Build()
	if !errors.Is(err, ErrNotAStruct) {
		t.Errorf("expected ErrNotAStruct, got: %v", err)
	}
}

func TestSelectObjectNilError(t *testing.T) {
	_, _, err := New().SelectObject(nil).From("t").Build()
	if !errors.Is(err, ErrNotAStruct) {
		t.Errorf("expected ErrNotAStruct, got: %v", err)
	}
}

func TestSelectObjectImmutability(t *testing.T) {
	base := New().Select("extra").From("users")
	withObj := base.SelectObject(testUser{})

	q1, _, err := base.Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	q2, _, err := withObj.Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}

	if q1 != "SELECT extra FROM users" {
		t.Errorf("base was mutated: %s", q1)
	}
	expected := "SELECT extra, id, first_name, email, created_at FROM users"
	if q2 != expected {
		t.Errorf("withObj mismatch\n got: %s\nwant: %s", q2, expected)
	}
}

func TestSelectObjectEmbeddedStruct(t *testing.T) {
	type Base struct {
		ID        int    `db:"id"`
		CreatedBy string `db:"created_by"`
	}
	type row struct {
		Base
		Name string `db:"name"`
	}
	q, _, err := New().SelectObject(row{}).From("t").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT id, created_by, name FROM t"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestSelectObjectEmbeddedPointerStruct(t *testing.T) {
	type Base struct {
		ID        int    `db:"id"`
		CreatedBy string `db:"created_by"`
	}
	type row struct {
		*Base
		Name string `db:"name"`
	}
	q, _, err := New().SelectObject(row{}).From("t").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT id, created_by, name FROM t"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestSelectObjectDeeplyNestedEmbed(t *testing.T) {
	type C struct {
		Z string `db:"z"`
	}
	type B struct {
		C
		Y string `db:"y"`
	}
	type A struct {
		B
		X string `db:"x"`
	}
	q, _, err := New().SelectObject(A{}).From("t").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT z, y, x FROM t"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestSelectObjectTimeField(t *testing.T) {
	type row struct {
		ID        int        `db:"id"`
		CreatedAt time.Time  `db:"created_at"`
		UpdatedAt *time.Time `db:"updated_at"`
	}
	q, _, err := New().SelectObject(row{}).From("t").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT id, created_at, updated_at FROM t"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestSelectObjectEmbeddedWithTablePrefix(t *testing.T) {
	type Base struct {
		ID int `db:"id"`
	}
	type row struct {
		Base
		Name string `db:"name"`
	}
	q, _, err := New().SelectObject(row{}, "t").From("things t").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT t.id, t.name FROM things t"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestSelectObjectNamedStructWithDash(t *testing.T) {
	type Metadata struct {
		Val string
	}
	type row struct {
		ID       int      `db:"id"`
		Metadata Metadata `db:"-"`
	}
	q, _, err := New().SelectObject(row{}).From("t").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT id FROM t"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestSelectObjectEmbeddedWithDashTag(t *testing.T) {
	type Base struct {
		ID int `db:"id"`
	}
	type row struct {
		Base `squildx:"-"`
		Name string `db:"name"`
	}
	q, _, err := New().SelectObject(row{}).From("t").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT name FROM t"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestSelectObjectEmbeddedWithNameTag(t *testing.T) {
	type Base struct {
		ID int `db:"id"`
	}
	type row struct {
		Base `db:"base_data"`
		Name string `db:"name"`
	}
	q, _, err := New().SelectObject(row{}).From("t").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT base_data, name FROM t"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}

func TestSelectObjectEmbeddedWithDbDash(t *testing.T) {
	type Base struct {
		ID int `db:"id"`
	}
	type row struct {
		Base `db:"-"`
		Name string `db:"name"`
	}
	q, _, err := New().SelectObject(row{}).From("t").Build()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	expected := "SELECT name FROM t"
	if q != expected {
		t.Errorf("SQL mismatch\n got: %s\nwant: %s", q, expected)
	}
}