package query

import (
	"testing"

	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
)

// ---------------------------------------------------------------------------
// Constructor tests
// ---------------------------------------------------------------------------

func TestFrom(t *testing.T) {
	b := From("Account")
	assert.Equal(t, QueryTypeSelect, b.queryType)
	assert.Equal(t, "Account", b.table)
	assert.NotNil(t, b.fields)
	assert.NotNil(t, b.joins)
	assert.NotNil(t, b.whereClauses)
	assert.NotNil(t, b.havingClauses)
	assert.NotNil(t, b.params)
	assert.NotNil(t, b.deferredWheres)
}

func TestInsert(t *testing.T) {
	data := map[string]interface{}{"Name": "Acme"}
	b := Insert("Account", data)
	assert.Equal(t, QueryTypeInsert, b.queryType)
	assert.Equal(t, "Account", b.table)
	assert.Equal(t, "Acme", b.values["Name"])
}

func TestUpdate(t *testing.T) {
	b := Update("Account")
	assert.Equal(t, QueryTypeUpdate, b.queryType)
	assert.Equal(t, "Account", b.table)
	assert.NotNil(t, b.values)
	assert.NotNil(t, b.whereClauses)
}

func TestDelete(t *testing.T) {
	b := Delete("Account")
	assert.Equal(t, QueryTypeDelete, b.queryType)
	assert.Equal(t, "Account", b.table)
	assert.NotNil(t, b.whereClauses)
}

// ---------------------------------------------------------------------------
// WithMetadata / SkipID
// ---------------------------------------------------------------------------

func TestWithMetadata(t *testing.T) {
	schema := &models.ObjectMetadata{APIName: "Account"}
	b := From("Account").WithMetadata(schema)
	assert.Equal(t, schema, b.schema)
}

func TestSkipID(t *testing.T) {
	b := From("Account").SkipID()
	assert.True(t, b.skipID)
}

// ---------------------------------------------------------------------------
// resolveColumnName
// ---------------------------------------------------------------------------

func TestResolveColumnName_NoSchema(t *testing.T) {
	b := From("Account")
	assert.Equal(t, "Name", b.resolveColumnName("Name"))
}

func TestResolveColumnName_WithSchema_Match(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "MyField", PhysicalName: "my_field_db"},
		},
	}
	b := From("Account").WithMetadata(schema)
	assert.Equal(t, "my_field_db", b.resolveColumnName("MyField"))
}

func TestResolveColumnName_WithSchema_CaseInsensitive(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "MyField", PhysicalName: "my_field_db"},
		},
	}
	b := From("Account").WithMetadata(schema)
	assert.Equal(t, "my_field_db", b.resolveColumnName("myfield"))
}

func TestResolveColumnName_WithSchema_EmptyPhysicalName(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "MyField", PhysicalName: ""},
		},
	}
	b := From("Account").WithMetadata(schema)
	// Falls back to the input field name when PhysicalName is empty
	assert.Equal(t, "MyField", b.resolveColumnName("MyField"))
}

func TestResolveColumnName_WithSchema_NoMatch(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "OtherField", PhysicalName: "other_db"},
		},
	}
	b := From("Account").WithMetadata(schema)
	assert.Equal(t, "UnknownField", b.resolveColumnName("UnknownField"))
}

// ---------------------------------------------------------------------------
// Build: SELECT path
// ---------------------------------------------------------------------------

func TestBuild_SimpleSelect(t *testing.T) {
	q := From("Account").Select([]string{"Name"}).Build()
	// Should auto-prepend Id
	assert.Contains(t, q.SQL, "`Account`.`"+constants.FieldID+"`")
	assert.Contains(t, q.SQL, "`Account`.`Name`")
	assert.Contains(t, q.SQL, "SELECT")
	assert.Contains(t, q.SQL, "FROM `Account`")
}

func TestBuild_SelectStar(t *testing.T) {
	q := From("Account").Select([]string{"*"}).Build()
	assert.Contains(t, q.SQL, "SELECT *")
	// * suppresses auto-Id prepend
	assert.NotContains(t, q.SQL, "AS")
}

func TestBuild_SelectSkipID(t *testing.T) {
	q := From("Account").SkipID().Select([]string{"Name"}).Build()
	// Should NOT contain the auto-prepended Id column
	sql := q.SQL
	assert.Contains(t, sql, "`Account`.`Name`")
	// Count occurrences of FieldID — should not appear as a standalone column
	// (It should only appear if explicitly selected, which it isn't here)
	// The SQL should just be "SELECT `Account`.`Name` FROM `Account`"
	assert.Equal(t, "SELECT `Account`.`Name` FROM `Account`", sql)
}

func TestBuild_SelectWithIdAlreadyPresent(t *testing.T) {
	q := From("Account").Select([]string{constants.FieldID, "Name"}).Build()
	// Id already present, should not be duplicated
	assert.Contains(t, q.SQL, "`Account`.`"+constants.FieldID+"`")
	assert.Contains(t, q.SQL, "`Account`.`Name`")
}

func TestBuild_NoFieldsDefaultsStar(t *testing.T) {
	// When no Select() is called at all, but skipID is set
	q := From("Account").SkipID().Build()
	assert.Contains(t, q.SQL, "SELECT * FROM `Account`")
}

// ---------------------------------------------------------------------------
// AddSelectRaw
// ---------------------------------------------------------------------------

func TestAddSelectRaw_WithAlias(t *testing.T) {
	q := From("Account").SkipID().AddSelectRaw("COUNT(*)", "total").Build()
	assert.Contains(t, q.SQL, "COUNT(*) as `total`")
}

func TestAddSelectRaw_WithoutAlias(t *testing.T) {
	q := From("Account").SkipID().AddSelectRaw("COUNT(*)").Build()
	assert.Contains(t, q.SQL, "COUNT(*)")
	assert.NotContains(t, q.SQL, " as ")
}

func TestAddSelectRaw_OnNonSelect(t *testing.T) {
	b := Update("Account")
	b.AddSelectRaw("COUNT(*)", "total")
	// Should be a no-op for non-SELECT queries
	assert.Empty(t, b.fields)
}

// ---------------------------------------------------------------------------
// Select on non-SELECT query
// ---------------------------------------------------------------------------

func TestSelect_OnNonSelect(t *testing.T) {
	b := Update("Account")
	b.Select([]string{"Name"})
	assert.Nil(t, b.fields) // fields not initialized for Update
}

// ---------------------------------------------------------------------------
// Where
// ---------------------------------------------------------------------------

func TestWhere_SimpleIdentifier(t *testing.T) {
	// Simple identifier with value → deferred
	q := From("Account").Where("Name", "Acme").Build()
	assert.Contains(t, q.SQL, "`Account`.`Name` = ?")
	assert.Equal(t, []interface{}{"Acme"}, q.Params)
}

func TestWhere_RawCondition(t *testing.T) {
	q := From("Account").Where("`Account`.`Name` = ?", "Acme").Build()
	assert.Contains(t, q.SQL, "`Account`.`Name` = ?")
	assert.Equal(t, []interface{}{"Acme"}, q.Params)
}

func TestWhere_RawNoValue(t *testing.T) {
	q := From("Account").Where("is_active").Build()
	assert.Contains(t, q.SQL, "WHERE is_active")
	assert.Empty(t, q.Params)
}

func TestWhere_DeferredWithOperator(t *testing.T) {
	// When 2 values: first is the actual value, second is interpreted as operator
	q := From("Account").Where("Amount", 100, ">").Build()
	assert.Contains(t, q.SQL, "`Account`.`Amount` > ?")
	assert.Equal(t, []interface{}{100}, q.Params)
}

// ---------------------------------------------------------------------------
// WhereRaw
// ---------------------------------------------------------------------------

func TestWhereRaw(t *testing.T) {
	q := From("Account").WhereRaw("`x` > ?", []interface{}{10}).Build()
	assert.Contains(t, q.SQL, "WHERE `x` > ?")
	assert.Equal(t, []interface{}{10}, q.Params)
}

func TestWhereRaw_Empty(t *testing.T) {
	b := From("Account")
	b.WhereRaw("", nil)
	assert.Empty(t, b.whereClauses)
}

// ---------------------------------------------------------------------------
// WhereOp
// ---------------------------------------------------------------------------

func TestWhereOp_ValidOperators(t *testing.T) {
	ops := []struct {
		op     string
		expect string
	}{
		{"=", "="},
		{"!=", "!="},
		{"<>", "<>"},
		{">", ">"},
		{"<", "<"},
		{">=", ">="},
		{"<=", "<="},
		{"LIKE", "LIKE"},
		{"NOT LIKE", "NOT LIKE"},
		{"IN", "IN"},
		{"NOT IN", "NOT IN"},
		{"IS", "IS"},
		{"IS NOT", "IS NOT"},
	}
	for _, tc := range ops {
		t.Run(tc.op, func(t *testing.T) {
			q := From("Account").WhereOp("Name", tc.op, "val").Build()
			assert.Contains(t, q.SQL, tc.expect+" ?")
			assert.Equal(t, []interface{}{"val"}, q.Params)
		})
	}
}

func TestWhereOp_InvalidOperator(t *testing.T) {
	q := From("Account").WhereOp("Name", "INVALID_OP", "val").Build()
	// Should default to =
	assert.Contains(t, q.SQL, "`Account`.`Name` = ?")
}

func TestWhereOp_WithSchema(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "MyField", PhysicalName: "my_field_db"},
		},
	}
	q := From("Account").WithMetadata(schema).WhereOp("MyField", "=", "x").Build()
	assert.Contains(t, q.SQL, "`Account`.`my_field_db` = ?")
}

// ---------------------------------------------------------------------------
// WhereNot
// ---------------------------------------------------------------------------

func TestWhereNot(t *testing.T) {
	q := From("Account").WhereNot("Status", "Closed").Build()
	assert.Contains(t, q.SQL, "`Account`.`Status` != ?")
	assert.Equal(t, []interface{}{"Closed"}, q.Params)
}

// ---------------------------------------------------------------------------
// WhereIn
// ---------------------------------------------------------------------------

func TestWhereIn(t *testing.T) {
	vals := []interface{}{"a", "b", "c"}
	q := From("Account").WhereIn("Status", vals).Build()
	assert.Contains(t, q.SQL, "`Account`.`Status` IN (?, ?, ?)")
	assert.Equal(t, vals, q.Params)
}

func TestWhereIn_Empty(t *testing.T) {
	q := From("Account").WhereIn("Status", []interface{}{}).Build()
	assert.Contains(t, q.SQL, "1=0")
}

func TestWhereIn_SingleValue(t *testing.T) {
	q := From("Account").WhereIn("Id", []interface{}{"abc"}).Build()
	assert.Contains(t, q.SQL, "`Account`.`Id` IN (?)")
	assert.Equal(t, []interface{}{"abc"}, q.Params)
}

// ---------------------------------------------------------------------------
// ExcludeDeleted
// ---------------------------------------------------------------------------

func TestExcludeDeleted(t *testing.T) {
	q := From("Account").ExcludeDeleted().Build()
	assert.Contains(t, q.SQL, "`Account`."+constants.FieldIsDeleted+" = 0")
}

// ---------------------------------------------------------------------------
// ApplySecurity
// ---------------------------------------------------------------------------

func TestApplySecurity(t *testing.T) {
	q := From("Account").ApplySecurity("owner_id = ?", []interface{}{"user1"}).Build()
	assert.Contains(t, q.SQL, "WHERE owner_id = ?")
	assert.Equal(t, []interface{}{"user1"}, q.Params)
}

func TestApplySecurity_Empty(t *testing.T) {
	b := From("Account")
	b.ApplySecurity("", nil)
	assert.Empty(t, b.whereClauses)
}

// ---------------------------------------------------------------------------
// OrderBy / AddOrderBy
// ---------------------------------------------------------------------------

func TestOrderBy(t *testing.T) {
	q := From("Account").Select([]string{"Name"}).OrderBy("Name", "ASC").Build()
	assert.Contains(t, q.SQL, "ORDER BY `Account`.`Name` ASC")
}

func TestOrderBy_OnNonSelect(t *testing.T) {
	b := Update("Account")
	b.OrderBy("Name", "ASC")
	assert.Empty(t, b.orderBy)
}

func TestOrderBy_WithDottedField(t *testing.T) {
	q := From("Account").Select([]string{"Name"}).OrderBy("t.Name", "DESC").Build()
	// Field already has dot notation — should not be wrapped
	assert.Contains(t, q.SQL, "ORDER BY t.Name DESC")
}

func TestOrderBy_WithBacktickedField(t *testing.T) {
	q := From("Account").Select([]string{"Name"}).OrderBy("`Name`", "ASC").Build()
	assert.Contains(t, q.SQL, "ORDER BY `Name` ASC")
}

func TestAddOrderBy(t *testing.T) {
	q := From("Account").SkipID().Select([]string{"Name"}).
		OrderBy("Name", "ASC").
		AddOrderBy("CreatedDate", "DESC").
		Build()
	assert.Contains(t, q.SQL, "ORDER BY `Account`.`Name` ASC, `Account`.`CreatedDate` DESC")
}

func TestAddOrderBy_WhenEmpty(t *testing.T) {
	q := From("Account").SkipID().Select([]string{"Name"}).
		AddOrderBy("Name", "ASC").
		Build()
	assert.Contains(t, q.SQL, "ORDER BY `Account`.`Name` ASC")
}

func TestOrderBy_InvalidDirection(t *testing.T) {
	q := From("Account").Select([]string{"Name"}).OrderBy("Name", "DESC; DROP TABLE x").Build()
	// Invalid direction should default to ASC
	assert.Contains(t, q.SQL, "ORDER BY `Account`.`Name` ASC")
	assert.NotContains(t, q.SQL, "DROP")
}

func TestOrderBy_CaseInsensitiveDirection(t *testing.T) {
	q := From("Account").Select([]string{"Name"}).OrderBy("Name", "desc").Build()
	assert.Contains(t, q.SQL, "ORDER BY `Account`.`Name` DESC")
}

func TestOrderBy_MaliciousColumn(t *testing.T) {
	b := From("Account").Select([]string{"Name"}).OrderBy("Name; DROP TABLE Account", "ASC")
	assert.Empty(t, b.orderBy, "malicious column name should be rejected")
}

func TestAddOrderBy_InvalidDirection(t *testing.T) {
	q := From("Account").SkipID().Select([]string{"Name"}).
		OrderBy("Name", "ASC").
		AddOrderBy("CreatedDate", "invalid").
		Build()
	assert.Contains(t, q.SQL, "ORDER BY `Account`.`Name` ASC, `Account`.`CreatedDate` ASC")
}

func TestAddOrderBy_MaliciousColumn(t *testing.T) {
	q := From("Account").SkipID().Select([]string{"Name"}).
		OrderBy("Name", "ASC").
		AddOrderBy("1; DROP TABLE--", "DESC").
		Build()
	// Malicious column should be ignored, only the first ORDER BY remains
	assert.Contains(t, q.SQL, "ORDER BY `Account`.`Name` ASC")
	assert.NotContains(t, q.SQL, "DROP")
}

// ---------------------------------------------------------------------------
// GroupBy / GroupByRaw
// ---------------------------------------------------------------------------

func TestGroupBy(t *testing.T) {
	q := From("Account").SkipID().AddSelectRaw("COUNT(*)", "cnt").GroupBy("Status").Build()
	assert.Contains(t, q.SQL, "GROUP BY `Account`.`Status`")
}

func TestGroupBy_Multiple(t *testing.T) {
	q := From("Account").SkipID().AddSelectRaw("COUNT(*)", "cnt").
		GroupBy("Status").GroupBy("Type").Build()
	assert.Contains(t, q.SQL, "GROUP BY `Account`.`Status`, `Account`.`Type`")
}

func TestGroupBy_OnNonSelect(t *testing.T) {
	b := Update("Account")
	b.GroupBy("Status")
	assert.Empty(t, b.groupBy)
}

func TestGroupByRaw(t *testing.T) {
	q := From("Account").SkipID().AddSelectRaw("COUNT(*)").GroupByRaw("YEAR(created_date)").Build()
	assert.Contains(t, q.SQL, "GROUP BY YEAR(created_date)")
}

func TestGroupByRaw_OnNonSelect(t *testing.T) {
	b := Update("Account")
	b.GroupByRaw("YEAR(created_date)")
	assert.Empty(t, b.groupBy)
}

// ---------------------------------------------------------------------------
// HavingRaw
// ---------------------------------------------------------------------------

func TestHavingRaw(t *testing.T) {
	q := From("Account").SkipID().
		AddSelectRaw("COUNT(*)", "cnt").
		GroupBy("Status").
		HavingRaw("COUNT(*) > ?", []interface{}{5}).
		Build()
	assert.Contains(t, q.SQL, "HAVING COUNT(*) > ?")
	assert.Equal(t, []interface{}{5}, q.Params)
}

func TestHavingRaw_Empty(t *testing.T) {
	b := From("Account")
	b.HavingRaw("", nil)
	assert.Empty(t, b.havingClauses)
}

func TestHavingRaw_OnNonSelect(t *testing.T) {
	b := Update("Account")
	b.HavingRaw("COUNT(*) > ?", []interface{}{5})
	assert.Empty(t, b.havingClauses)
}

// ---------------------------------------------------------------------------
// Limit / Offset
// ---------------------------------------------------------------------------

func TestLimit(t *testing.T) {
	q := From("Account").SkipID().Select([]string{"Name"}).Limit(10).Build()
	assert.Contains(t, q.SQL, "LIMIT 10")
}

func TestOffset(t *testing.T) {
	q := From("Account").SkipID().Select([]string{"Name"}).Limit(10).Offset(20).Build()
	assert.Contains(t, q.SQL, "LIMIT 10")
	assert.Contains(t, q.SQL, "OFFSET 20")
}

func TestLimit_OnNonSelect(t *testing.T) {
	b := Update("Account")
	b.Limit(10)
	assert.Nil(t, b.limit)
}

func TestOffset_OnNonSelect(t *testing.T) {
	b := Update("Account")
	b.Offset(10)
	assert.Nil(t, b.offset)
}

// ---------------------------------------------------------------------------
// Join
// ---------------------------------------------------------------------------

func TestJoin(t *testing.T) {
	q := From("Account").SkipID().
		Select([]string{"Name"}).
		Join("LEFT JOIN", "Contact", "c", "`Account`.`Id` = `c`.`AccountId`").
		Build()
	assert.Contains(t, q.SQL, "LEFT JOIN `Contact` as `c` ON `Account`.`Id` = `c`.`AccountId`")
}

func TestJoin_OnNonSelect(t *testing.T) {
	b := Update("Account")
	b.Join("LEFT JOIN", "Contact", "c", "on_clause")
	assert.Empty(t, b.joins)
}

func TestJoinOn(t *testing.T) {
	q := From("Account").SkipID().
		Select([]string{"Name"}).
		JoinOn("INNER JOIN", "Contact", "c", "Account.Id", "=", "c.AccountId").
		Build()
	assert.Contains(t, q.SQL, "INNER JOIN `Contact` as `c` ON Account.Id = c.AccountId")
}

func TestJoinOn_OnNonSelect(t *testing.T) {
	b := Update("Account")
	b.JoinOn("LEFT JOIN", "Contact", "c", "a.Id", "=", "c.AccountId")
	assert.Empty(t, b.joins)
}

// ---------------------------------------------------------------------------
// Build: INSERT path
// ---------------------------------------------------------------------------

func TestBuild_SingleInsert(t *testing.T) {
	data := map[string]interface{}{"Name": "Acme"}
	q := Insert("Account", data).Build()
	assert.Contains(t, q.SQL, "INSERT INTO `Account`")
	assert.Contains(t, q.SQL, "`Name`")
	assert.Contains(t, q.SQL, "VALUES (?)")
	assert.Equal(t, []interface{}{"Acme"}, q.Params)
}

func TestBuild_InsertWithRaw(t *testing.T) {
	data := map[string]interface{}{
		"Name":        "Acme",
		"CreatedDate": Raw("NOW()"),
	}
	q := Insert("Account", data).Build()
	assert.Contains(t, q.SQL, "NOW()")
	// Raw values should not be in params
	// Only "Acme" should be parameterized
	assert.Contains(t, q.Params, "Acme")
	for _, p := range q.Params {
		assert.NotEqual(t, Raw("NOW()"), p)
	}
}

func TestBuild_InsertWithOnDuplicateKey(t *testing.T) {
	data := map[string]interface{}{"Name": "Acme"}
	q := Insert("Account", data).
		OnDuplicateKeyUpdate(map[string]interface{}{"Name": "Updated"}).
		Build()
	assert.Contains(t, q.SQL, "ON DUPLICATE KEY UPDATE")
	assert.Contains(t, q.SQL, "`Name` = ?")
}

func TestBuild_InsertWithOnDuplicateKey_RawValue(t *testing.T) {
	data := map[string]interface{}{"Name": "Acme"}
	q := Insert("Account", data).
		OnDuplicateKeyUpdate(map[string]interface{}{"UpdatedDate": Raw("NOW()")}).
		Build()
	assert.Contains(t, q.SQL, "ON DUPLICATE KEY UPDATE")
	assert.Contains(t, q.SQL, "NOW()")
}

// ---------------------------------------------------------------------------
// ValuesBatch (batch insert)
// ---------------------------------------------------------------------------

func TestValuesBatch(t *testing.T) {
	records := []map[string]interface{}{
		{"Name": "Acme"},
		{"Name": "Beta"},
	}
	b := Insert("Account", map[string]interface{}{}).ValuesBatch(records)
	q := b.Build()
	assert.Contains(t, q.SQL, "INSERT INTO `Account`")
	assert.Contains(t, q.SQL, "`Name`")
	// Should have 2 rows of placeholders
	assert.Equal(t, []interface{}{"Acme", "Beta"}, q.Params)
}

func TestValuesBatch_OnNonInsert(t *testing.T) {
	b := Update("Account")
	b.ValuesBatch([]map[string]interface{}{{"Name": "X"}})
	assert.Nil(t, b.batchValues)
}

func TestValuesBatch_WithRaw(t *testing.T) {
	records := []map[string]interface{}{
		{"Name": "Acme", "Created": Raw("NOW()")},
	}
	q := Insert("Account", map[string]interface{}{}).ValuesBatch(records).Build()
	assert.Contains(t, q.SQL, "NOW()")
	// Only "Acme" should be parameterized (or nil for missing keys)
}

func TestValuesBatch_WithOnDuplicateKey(t *testing.T) {
	records := []map[string]interface{}{
		{"Name": "Acme"},
	}
	q := Insert("Account", map[string]interface{}{}).
		ValuesBatch(records).
		OnDuplicateKeyUpdate(map[string]interface{}{"Name": Raw("VALUES(`Name`)")}).
		Build()
	assert.Contains(t, q.SQL, "ON DUPLICATE KEY UPDATE")
	assert.Contains(t, q.SQL, "VALUES(`Name`)")
}

// ---------------------------------------------------------------------------
// Set
// ---------------------------------------------------------------------------

func TestSet(t *testing.T) {
	b := Update("Account").Set(map[string]interface{}{"Name": "Updated"})
	assert.Equal(t, "Updated", b.values["Name"])
}

func TestSet_OnNonUpdate(t *testing.T) {
	b := From("Account")
	b.Set(map[string]interface{}{"Name": "X"})
	// Should be a no-op — values should remain nil for SELECT
	assert.Nil(t, b.values)
}

// ---------------------------------------------------------------------------
// OnDuplicateKeyUpdate on non-INSERT
// ---------------------------------------------------------------------------

func TestOnDuplicateKeyUpdate_OnNonInsert(t *testing.T) {
	b := Update("Account")
	b.OnDuplicateKeyUpdate(map[string]interface{}{"Name": "X"})
	assert.Nil(t, b.updateValues)
}

// ---------------------------------------------------------------------------
// Build: UPDATE path
// ---------------------------------------------------------------------------

func TestBuild_Update(t *testing.T) {
	q := Update("Account").
		Set(map[string]interface{}{"Name": "Updated"}).
		Where("Id", "abc").
		Build()
	assert.Contains(t, q.SQL, "UPDATE `Account` SET")
	assert.Contains(t, q.SQL, "`Name` = ?")
	assert.Contains(t, q.SQL, "WHERE")
	require.Len(t, q.Params, 2)
}

func TestBuild_UpdateWithRaw(t *testing.T) {
	q := Update("Account").
		Set(map[string]interface{}{"Counter": Raw("Counter + 1")}).
		Where("Id", "abc").
		Build()
	assert.Contains(t, q.SQL, "`Counter` = Counter + 1")
}

func TestBuild_UpdateNoWhere(t *testing.T) {
	q := Update("Account").
		Set(map[string]interface{}{"Name": "Updated"}).
		Build()
	assert.Contains(t, q.SQL, "UPDATE `Account` SET `Name` = ?")
	assert.NotContains(t, q.SQL, "WHERE")
}

// ---------------------------------------------------------------------------
// Build: DELETE path
// ---------------------------------------------------------------------------

func TestBuild_Delete(t *testing.T) {
	q := Delete("Account").Where("Id", "abc").Build()
	assert.Contains(t, q.SQL, "DELETE FROM `Account`")
	assert.Contains(t, q.SQL, "WHERE")
	assert.Equal(t, []interface{}{"abc"}, q.Params)
}

func TestBuild_DeleteNoWhere(t *testing.T) {
	q := Delete("Account").Build()
	assert.Equal(t, "DELETE FROM `Account`", q.SQL)
	assert.Empty(t, q.Params)
}

// ---------------------------------------------------------------------------
// BulkInsertOrdered
// ---------------------------------------------------------------------------

func TestBulkInsertOrdered_Basic(t *testing.T) {
	columns := []string{"Name", "Status"}
	records := []map[string]interface{}{
		{"Name": "Acme", "Status": "Active"},
		{"Name": "Beta", "Status": "Inactive"},
	}
	sql, params := BulkInsertOrdered("Account", columns, records, nil)
	assert.Contains(t, sql, "INSERT INTO `Account`")
	assert.Contains(t, sql, "`Name`")
	assert.Contains(t, sql, "`Status`")
	assert.Contains(t, sql, "(?, ?), (?, ?)")
	assert.Equal(t, []interface{}{"Acme", "Active", "Beta", "Inactive"}, params)
}

func TestBulkInsertOrdered_WithResolver(t *testing.T) {
	columns := []string{"MyField"}
	records := []map[string]interface{}{
		{"MyField": "val"},
	}
	resolver := func(col string) string {
		if col == "MyField" {
			return "my_field_db"
		}
		return col
	}
	sql, params := BulkInsertOrdered("Account", columns, records, resolver)
	assert.Contains(t, sql, "`my_field_db`")
	assert.Equal(t, []interface{}{"val"}, params)
}

func TestBulkInsertOrdered_EmptyRecords(t *testing.T) {
	sql, params := BulkInsertOrdered("Account", []string{"Name"}, nil, nil)
	assert.Empty(t, sql)
	assert.Nil(t, params)
}

func TestBulkInsertOrdered_EmptyColumns(t *testing.T) {
	records := []map[string]interface{}{{"Name": "Acme"}}
	sql, params := BulkInsertOrdered("Account", nil, records, nil)
	assert.Empty(t, sql)
	assert.Nil(t, params)
}

func TestBulkInsertOrdered_MissingKeyInRecord(t *testing.T) {
	columns := []string{"Name", "Status"}
	records := []map[string]interface{}{
		{"Name": "Acme"}, // Missing "Status"
	}
	sql, params := BulkInsertOrdered("Account", columns, records, nil)
	assert.Contains(t, sql, "(?, ?)")
	// Missing key yields nil
	assert.Equal(t, []interface{}{"Acme", nil}, params)
}

// ---------------------------------------------------------------------------
// Full pipeline: SELECT with multiple clauses
// ---------------------------------------------------------------------------

func TestBuild_SelectFullPipeline(t *testing.T) {
	q := From("Account").SkipID().
		Select([]string{"Name", "Status"}).
		WhereOp("Status", "=", "Active").
		WhereNot("Type", "Internal").
		OrderBy("Name", "ASC").
		Limit(25).
		Offset(50).
		Build()

	assert.Contains(t, q.SQL, "SELECT `Account`.`Name`, `Account`.`Status`")
	assert.Contains(t, q.SQL, "FROM `Account`")
	assert.Contains(t, q.SQL, "WHERE")
	assert.Contains(t, q.SQL, "`Account`.`Status` = ?")
	assert.Contains(t, q.SQL, "`Account`.`Type` != ?")
	assert.Contains(t, q.SQL, "ORDER BY `Account`.`Name` ASC")
	assert.Contains(t, q.SQL, "LIMIT 25")
	assert.Contains(t, q.SQL, "OFFSET 50")
	assert.Equal(t, []interface{}{"Active", "Internal"}, q.Params)
}

func TestBuild_SelectWithJoinAndGroupBy(t *testing.T) {
	q := From("Account").SkipID().
		AddSelectRaw("COUNT(`Contact`.`Id`)", "contact_count").
		Join("LEFT JOIN", "Contact", "c", "`Account`.`Id` = `c`.`AccountId`").
		GroupBy("Name").
		HavingRaw("COUNT(`Contact`.`Id`) > ?", []interface{}{3}).
		Build()

	assert.Contains(t, q.SQL, "LEFT JOIN `Contact` as `c`")
	assert.Contains(t, q.SQL, "GROUP BY `Account`.`Name`")
	assert.Contains(t, q.SQL, "HAVING COUNT(`Contact`.`Id`) > ?")
	assert.Equal(t, []interface{}{3}, q.Params)
}

// ---------------------------------------------------------------------------
// Multiple WHERE conditions combined with AND
// ---------------------------------------------------------------------------

func TestBuild_MultipleWhereConditions(t *testing.T) {
	q := From("Account").SkipID().
		Select([]string{"Name"}).
		WhereOp("Status", "=", "Active").
		WhereOp("Type", "=", "Enterprise").
		Build()

	assert.Contains(t, q.SQL, "WHERE `Account`.`Status` = ? AND `Account`.`Type` = ?")
	assert.Equal(t, []interface{}{"Active", "Enterprise"}, q.Params)
}

// ---------------------------------------------------------------------------
// Select with schema aliasing (physical != logical)
// ---------------------------------------------------------------------------

func TestBuild_SelectWithAliasing(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "Id", PhysicalName: "Id"},
			{APIName: "DisplayName", PhysicalName: "display_name_col"},
		},
	}
	q := From("Account").WithMetadata(schema).SkipID().
		Select([]string{"DisplayName"}).Build()
	assert.Contains(t, q.SQL, "`Account`.`display_name_col` AS `DisplayName`")
}

// ---------------------------------------------------------------------------
// processDeferredWheres idempotency
// ---------------------------------------------------------------------------

func TestDeferredWheres_ProcessedOnce(t *testing.T) {
	b := From("Account").Where("Name", "Acme")
	require.Len(t, b.deferredWheres, 1)
	// Build triggers processDeferredWheres
	q := b.Build()
	assert.Contains(t, q.SQL, "`Account`.`Name` = ?")
	// After build, deferredWheres should be nil (cleared)
	assert.Nil(t, b.deferredWheres)
}

// ---------------------------------------------------------------------------
// WhereIn with schema aliasing
// ---------------------------------------------------------------------------

func TestWhereIn_WithSchema(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "Status", PhysicalName: "status_col"},
		},
	}
	q := From("Account").WithMetadata(schema).
		WhereIn("Status", []interface{}{"A", "B"}).Build()
	assert.Contains(t, q.SQL, "`Account`.`status_col` IN (?, ?)")
}

// ---------------------------------------------------------------------------
// OrderBy with schema aliasing
// ---------------------------------------------------------------------------

func TestOrderBy_WithSchema(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "SortField", PhysicalName: "sort_field_db"},
		},
	}
	q := From("Account").WithMetadata(schema).SkipID().
		Select([]string{"*"}).
		OrderBy("SortField", "ASC").Build()
	assert.Contains(t, q.SQL, "ORDER BY `Account`.`sort_field_db` ASC")
}

// ---------------------------------------------------------------------------
// GroupBy with schema aliasing
// ---------------------------------------------------------------------------

func TestGroupBy_WithSchema(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "Category", PhysicalName: "category_col"},
		},
	}
	q := From("Account").WithMetadata(schema).SkipID().
		AddSelectRaw("COUNT(*)").
		GroupBy("Category").Build()
	assert.Contains(t, q.SQL, "GROUP BY `Account`.`category_col`")
}

// ---------------------------------------------------------------------------
// Build with deferred wheres using schema resolution
// ---------------------------------------------------------------------------

func TestDeferredWheres_WithSchema(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "OwnerId", PhysicalName: "owner_id_col"},
		},
	}
	q := From("Account").WithMetadata(schema).
		Where("OwnerId", "user123").Build()
	assert.Contains(t, q.SQL, "`Account`.`owner_id_col` = ?")
	assert.Contains(t, q.Params, "user123")
}

// ---------------------------------------------------------------------------
// Insert with schema resolution
// ---------------------------------------------------------------------------

func TestBuild_InsertWithSchema(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "DisplayName", PhysicalName: "display_name_col"},
		},
	}
	data := map[string]interface{}{"DisplayName": "Test"}
	q := Insert("Account", data).WithMetadata(schema).Build()
	assert.Contains(t, q.SQL, "`display_name_col`")
	assert.Equal(t, []interface{}{"Test"}, q.Params)
}

// ---------------------------------------------------------------------------
// Update with schema resolution
// ---------------------------------------------------------------------------

func TestBuild_UpdateWithSchema(t *testing.T) {
	schema := &models.ObjectMetadata{
		Fields: []models.FieldMetadata{
			{APIName: "DisplayName", PhysicalName: "display_name_col"},
		},
	}
	q := Update("Account").WithMetadata(schema).
		Set(map[string]interface{}{"DisplayName": "New"}).Build()
	assert.Contains(t, q.SQL, "`display_name_col` = ?")
	assert.Equal(t, []interface{}{"New"}, q.Params)
}