package soql

import (
	"errors"
	"strings"
	"testing"

	apperrors "github.com/nexuscrm/backend/pkg/errors"
)

func TestParseSimpleQuery(t *testing.T) {
	input := "SELECT id, name FROM Account LIMIT 5"
	l := NewLexer(input)
	p := NewParser(l)

	req, err := p.Parse()
	if err != nil {
		t.Fatalf("Parse() failed: %v", err)
	}

	if req.ObjectAPIName != "Account" {
		t.Errorf("expected ObjectAPIName 'Account', got '%s'", req.ObjectAPIName)
	}

	if req.Limit != 5 {
		t.Errorf("expected Limit 5, got %d", req.Limit)
	}
}

func TestParseWhereClause(t *testing.T) {
	input := "SELECT id FROM Contact WHERE email = 'test@example.com' orDER BY name DESC"
	l := NewLexer(input)
	p := NewParser(l)

	req, err := p.Parse()
	if err != nil {
		t.Fatalf("Parse() failed: %v", err)
	}

	if req.ObjectAPIName != "Contact" {
		t.Errorf("expected ObjectAPIName 'Contact', got '%s'", req.ObjectAPIName)
	}

	// Basic check on filter expression transformation
	// Parser currently transforms = to ==
	expectedFilter := "email == 'test@example.com'"
	if req.FilterExpr != expectedFilter {
		t.Errorf("expected FilterExpr '%s', got '%s'", expectedFilter, req.FilterExpr)
	}

	if req.SortField != "name" {
		t.Errorf("expected SortField 'name', got '%s'", req.SortField)
	}

	if req.SortDirection != "DESC" {
		t.Errorf("expected SortDirection 'DESC', got '%s'", req.SortDirection)
	}
}

func TestParseComprehensive(t *testing.T) {
	tests := []struct {
		name          string
		input         string
		expectedObj   string
		expectedWhere string
		expectedLimit int
		expectedOrder string
		expectError   bool
	}{
		// Basic
		{"Basic Select", "SELECT id FROM Account", "Account", "", 0, "", false},
		{"Basic Limit", "SELECT id FROM Account LIMIT 10", "Account", "", 10, "", false},

		// Where Clauses
		{"Simple Where", "SELECT id FROM Contact WHERE email = 'test@test.com'", "Contact", "email == 'test@test.com'", 0, "", false},
		{"Where and", "SELECT id FROM Lead WHERE status = 'Open' and rating > 5", "Lead", "status == 'Open' and rating > 5", 0, "", false},
		{"Where or", "SELECT id FROM Task WHERE priority = 'High' or status = 'Urgent'", "Task", "priority == 'High' or status == 'Urgent'", 0, "", false},
		{"Where Not Equal", "SELECT id FROM Opportunity WHERE stage != 'Closed Won'", "Opportunity", "stage != 'Closed Won'", 0, "", false},
		{"Where In", "SELECT id FROM Account WHERE type IN ('Customer', 'Partner')", "Account", "type in ['Customer', 'Partner']", 0, "", false},
		{"Where Like", "SELECT id FROM Contact WHERE name LIKE 'A%'", "Contact", "name startsWith 'A'", 0, "", false},

		// Order By
		{"Order By Asc", "SELECT id FROM Case orDER BY createdDate ASC", "Case", "", 0, "createdDate", false},
		{"Order By Desc", "SELECT id FROM Case orDER BY createdDate DESC", "Case", "", 0, "createdDate", false},
		{"Order By Implicit", "SELECT id FROM Case orDER BY createdDate", "Case", "", 0, "createdDate", false},

		// Complex Combinations
		{"Complex 1", "SELECT id FROM Account WHERE type = 'Customer' and revenue > 10000 orDER BY revenue DESC LIMIT 5", "Account", "type == 'Customer' and revenue > 10000", 5, "revenue", false},
		{"Complex 2", "SELECT id FROM Contact WHERE (email != null or phone != null) and active = true", "Contact", "( email != null or phone != null ) and active == true", 0, "", false},

		// Edge Cases
		{"Case Insensitive Keywords", "select id from account where name = 'foo' limit 1", "account", "name == 'foo'", 1, "", false},
		{"Whitespace Handling", "SELECT   id   FROM   Account   WHERE   name   =   'foo'", "Account", "name == 'foo'", 0, "", false},

		// Errors
		{"Missing SELECT", "id FROM Account", "", "", 0, "", true},
		{"Missing FROM", "SELECT id Account", "", "", 0, "", true},
		{"Missing Object", "SELECT id FROM", "", "", 0, "", true},

		// --- Strict Verification Expansion (100+ Goal) ---

		// Field Lists
		{"Select Star", "SELECT * FROM Account", "Account", "", 0, "", false}, // Parser allows * as it blindly accepts token
		// Actually Parser: expects identifiers separated by commas. '*' is Operator, but Parser consumes it.

		{"Select Single Field", "SELECT Name FROM Account", "Account", "", 0, "", false},
		{"Select Two Fields", "SELECT Name, Id FROM Account", "Account", "", 0, "", false},
		{"Select Many Fields", "SELECT Name, Id, CreatedDate, OwnerId FROM Account", "Account", "", 0, "", false},
		{"Select With Alias", "SELECT Name n FROM Account", "Account", "", 0, "", false},

		// Limits
		{"Limit 0", "SELECT Id FROM Account LIMIT 0", "Account", "", 0, "", false},
		{"Limit Large", "SELECT Id FROM Account LIMIT 999999", "Account", "", 999999, "", false},

		// Order By Variations
		{"Order By Field", "SELECT Id FROM Account orDER BY Name", "Account", "", 0, "Name", false},
		{"Order By Field ASC", "SELECT Id FROM Account orDER BY Name ASC", "Account", "", 0, "Name", false},
		{"Order By Field DESC", "SELECT Id FROM Account orDER BY Name DESC", "Account", "", 0, "Name", false},
		{"Order By Field Mixed Case", "SELECT Id FROM Account orDER BY nAmE DeSc", "Account", "", 0, "nAmE", false},

		// Where: Comparisons
		{"Where Equals Number", "SELECT Id FROM Opp WHERE Amount = 100", "Opp", "Amount == 100", 0, "", false},
		{"Where Equals String", "SELECT Id FROM Opp WHERE Name = 'Deal'", "Opp", "Name == 'Deal'", 0, "", false},
		{"Where Equals Bool True", "SELECT Id FROM Opp WHERE IsClosed = true", "Opp", "IsClosed == true", 0, "", false},
		{"Where Equals Bool False", "SELECT Id FROM Opp WHERE IsClosed = false", "Opp", "IsClosed == false", 0, "", false},
		{"Where Not Equals", "SELECT Id FROM Opp WHERE Amount != 0", "Opp", "Amount != 0", 0, "", false},
		{"Where GT", "SELECT Id FROM Opp WHERE Amount > 100", "Opp", "Amount > 100", 0, "", false},
		{"Where LT", "SELECT Id FROM Opp WHERE Amount < 100", "Opp", "Amount < 100", 0, "", false},
		{"Where GTE", "SELECT Id FROM Opp WHERE Amount >= 100", "Opp", "Amount >= 100", 0, "", false},
		{"Where LTE", "SELECT Id FROM Opp WHERE Amount <= 100", "Opp", "Amount <= 100", 0, "", false},

		// Where: Logic
		{"Where and Basic", "SELECT Id FROM A WHERE X=1 and Y=2", "A", "X == 1 and Y == 2", 0, "", false},
		{"Where or Basic", "SELECT Id FROM A WHERE X=1 or Y=2", "A", "X == 1 or Y == 2", 0, "", false},
		{"Where and or Mixed", "SELECT Id FROM A WHERE X=1 and Y=2 or Z=3", "A", "X == 1 and Y == 2 or Z == 3", 0, "", false}, // Precedence handled by backend formula engine typically
		{"Where Parens", "SELECT Id FROM A WHERE (X=1 or Y=2) and Z=3", "A", "( X == 1 or Y == 2 ) and Z == 3", 0, "", false},

		// Where: Lists (IN)
		{"Where IN Strings", "SELECT Id FROM A WHERE Stage IN ('A', 'B')", "A", "Stage in ['A', 'B']", 0, "", false},
		{"Where IN Numbers", "SELECT Id FROM A WHERE Score IN (1, 2, 3)", "A", "Score in [1, 2, 3]", 0, "", false},

		// Where: Like
		{"Where LIKE Prefix", "SELECT Id FROM A WHERE Name LIKE 'Abc%'", "A", "Name startsWith 'Abc'", 0, "", false},
		{"Where LIKE Suffix", "SELECT Id FROM A WHERE Name LIKE '%Abc'", "A", "Name endsWith 'Abc'", 0, "", false},
		{"Where LIKE Contains", "SELECT Id FROM A WHERE Name LIKE '%Abc%'", "A", "Name contains 'Abc'", 0, "", false},

		// Whitespace Torture
		{"No Space Equals", "SELECT Id FROM A WHERE Name='B'", "A", "Name == 'B'", 0, "", false},
		{"Extra Space Parens", "SELECT Id FROM A WHERE (  Name = 'B'  )", "A", "( Name == 'B' )", 0, "", false},
		{"Newlines", "SELECT Id\nFROM Account\nWHERE Name = 'X'", "Account", "Name == 'X'", 0, "", false},

		// Case Sensitivity
		{"Lowercase Keywords", "select id from account where name = 'x' order by name desc limit 5", "account", "name == 'x'", 5, "name", false},
		{"Mixed Case Keywords", "SeLeCt Id FrOm AcCoUnT", "AcCoUnT", "", 0, "", false},

		// Quoted Strings with Special Chars
		{"String with Space", "SELECT Id FROM A WHERE Name = 'Start End'", "A", "Name == 'Start End'", 0, "", false},
		{"String with Punctuation", "SELECT Id FROM A WHERE Name = 'O''Reilly'", "A", "Name == 'O\\'Reilly'", 0, "", false}, // Escaped for Formula
		{"String with Escape", "SELECT Id FROM A WHERE Name = 'O\\'Reilly'", "A", "Name == 'O\\'Reilly'", 0, "", false},

		// System Fields
		{"Select System Fields", "SELECT Id, CreatedDate, LastModifiedById FROM Account", "Account", "", 0, "", false},

		// Reserved Keyword as Object
		{"Group Object Name", "SELECT Id FROM Group", "Group", "", 0, "", false},

		// Invalid Syntax
		{"Double FROM", "SELECT Id FROM FROM Account", "", "", 0, "", true},
		{"Double SELECT", "SELECT SELECT Id FROM Account", "", "", 0, "", true},
		{"Trailing Comma", "SELECT Id, Name, FROM Account", "", "", 0, "", true},
		{"Where Missing Op", "SELECT Id FROM A WHERE Name 'X'", "", "", 0, "", true}, // Fails Parser now!
		{"Limit Non-Number", "SELECT Id FROM A LIMIT 'Five'", "", "", 0, "", true},
		{"Order By Missing Field", "SELECT Id FROM A orDER BY", "", "", 0, "", true},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			l := NewLexer(tt.input)
			p := NewParser(l)
			req, err := p.Parse()

			if tt.expectError {
				if err == nil {
					t.Errorf("expected error, got nil")
				}
				return
			}

			if err != nil {
				t.Fatalf("unexpected error: %v", err)
			}

			if req.ObjectAPIName != tt.expectedObj {
				t.Errorf("Object: expected %s, got %s", tt.expectedObj, req.ObjectAPIName)
			}

			// Validate Where (FilterExpr)
			// Normalize spaces for comparison if needed, currently exact match checked
			if req.FilterExpr != tt.expectedWhere {
				t.Errorf("Where: expected '%s', got '%s'", tt.expectedWhere, req.FilterExpr)
			}

			if tt.expectedLimit != 0 && req.Limit != tt.expectedLimit {
				t.Errorf("Limit: expected %d, got %d", tt.expectedLimit, req.Limit)
			}

			if tt.expectedOrder != "" && req.SortField != tt.expectedOrder {
				t.Errorf("Sort: expected %s, got %s", tt.expectedOrder, req.SortField)
			}
		})
	}
}

func TestParseError(t *testing.T) {
	input := "DELETE FROM Account" // Invalid for SELECT parser
	l := NewLexer(input)
	p := NewParser(l)

	_, err := p.Parse()
	if err == nil {
		t.Errorf("Expected parse error for invalid query, got nil")
	}
}

// TestSubQueryParsing covers SOQL subquery parsing for child relationships.
func TestSubQueryParsing(t *testing.T) {
	tests := []struct {
		name              string
		input             string
		expectedObj       string
		expectedFields    []string
		expectedSubQCount int
		subQueryChecks    []struct {
			relName      string
			fields       []string
			filterExpr   string
			orderByCount int
			limit        int
		}
		expectError bool
	}{
		{
			name:              "Basic subquery",
			input:             "SELECT Id, (SELECT Id FROM Contacts) FROM Account",
			expectedObj:       "Account",
			expectedFields:    []string{"Id"},
			expectedSubQCount: 1,
			subQueryChecks: []struct {
				relName      string
				fields       []string
				filterExpr   string
				orderByCount int
				limit        int
			}{
				{relName: "Contacts", fields: []string{"Id"}, filterExpr: "", orderByCount: 0, limit: 0},
			},
		},
		{
			name:              "Subquery with multiple fields",
			input:             "SELECT Id, Name, (SELECT Id, FirstName, LastName FROM Contacts) FROM Account",
			expectedObj:       "Account",
			expectedFields:    []string{"Id", "Name"},
			expectedSubQCount: 1,
			subQueryChecks: []struct {
				relName      string
				fields       []string
				filterExpr   string
				orderByCount int
				limit        int
			}{
				{relName: "Contacts", fields: []string{"Id", "FirstName", "LastName"}, filterExpr: "", orderByCount: 0, limit: 0},
			},
		},
		{
			name:              "Subquery with WHERE",
			input:             "SELECT Id, (SELECT Id FROM Contacts WHERE Active = true) FROM Account",
			expectedObj:       "Account",
			expectedFields:    []string{"Id"},
			expectedSubQCount: 1,
			subQueryChecks: []struct {
				relName      string
				fields       []string
				filterExpr   string
				orderByCount int
				limit        int
			}{
				{relName: "Contacts", fields: []string{"Id"}, filterExpr: "Active == true", orderByCount: 0, limit: 0},
			},
		},
		{
			name:              "Subquery with ORDER BY and LIMIT",
			input:             "SELECT Id, (SELECT Id FROM StepsAndWorkitems ORDER BY CreatedDate ASC LIMIT 10) FROM ProcessInstance",
			expectedObj:       "ProcessInstance",
			expectedFields:    []string{"Id"},
			expectedSubQCount: 1,
			subQueryChecks: []struct {
				relName      string
				fields       []string
				filterExpr   string
				orderByCount int
				limit        int
			}{
				{relName: "StepsAndWorkitems", fields: []string{"Id"}, filterExpr: "", orderByCount: 1, limit: 10},
			},
		},
		{
			name:              "Multiple subqueries",
			input:             "SELECT Id, (SELECT Id FROM Contacts), (SELECT Id FROM Opportunities) FROM Account",
			expectedObj:       "Account",
			expectedFields:    []string{"Id"},
			expectedSubQCount: 2,
			subQueryChecks: []struct {
				relName      string
				fields       []string
				filterExpr   string
				orderByCount int
				limit        int
			}{
				{relName: "Contacts", fields: []string{"Id"}, filterExpr: "", orderByCount: 0, limit: 0},
				{relName: "Opportunities", fields: []string{"Id"}, filterExpr: "", orderByCount: 0, limit: 0},
			},
		},
		{
			name:              "Mixed fields and subqueries",
			input:             "SELECT Id, Name, (SELECT Id, Email FROM Contacts WHERE Email != null ORDER BY Email ASC LIMIT 5), Industry FROM Account",
			expectedObj:       "Account",
			expectedFields:    []string{"Id", "Name", "Industry"},
			expectedSubQCount: 1,
			subQueryChecks: []struct {
				relName      string
				fields       []string
				filterExpr   string
				orderByCount int
				limit        int
			}{
				{relName: "Contacts", fields: []string{"Id", "Email"}, filterExpr: "Email != null", orderByCount: 1, limit: 5},
			},
		},
		{
			name:              "Subquery with function call in SELECT",
			input:             "SELECT Id, (SELECT COUNT(Id) FROM Contacts) FROM Account",
			expectedObj:       "Account",
			expectedFields:    []string{"Id"},
			expectedSubQCount: 1,
			subQueryChecks: []struct {
				relName      string
				fields       []string
				filterExpr   string
				orderByCount int
				limit        int
			}{
				{relName: "Contacts", fields: []string{"COUNT(Id)"}, filterExpr: "", orderByCount: 0, limit: 0},
			},
		},
		{
			name:              "Subquery with function and plain fields in SELECT",
			input:             "SELECT Id, (SELECT COUNT(Id), Name FROM Contacts) FROM Account",
			expectedObj:       "Account",
			expectedFields:    []string{"Id"},
			expectedSubQCount: 1,
			subQueryChecks: []struct {
				relName      string
				fields       []string
				filterExpr   string
				orderByCount int
				limit        int
			}{
				{relName: "Contacts", fields: []string{"COUNT(Id)", "Name"}, filterExpr: "", orderByCount: 0, limit: 0},
			},
		},
		{
			name:              "Subquery with function and alias in SELECT",
			input:             "SELECT Id, (SELECT FORMAT(Amount) TotalAmount FROM LineItems) FROM Account",
			expectedObj:       "Account",
			expectedFields:    []string{"Id"},
			expectedSubQCount: 1,
			subQueryChecks: []struct {
				relName      string
				fields       []string
				filterExpr   string
				orderByCount int
				limit        int
			}{
				{relName: "LineItems", fields: []string{"FORMAT(Amount) TotalAmount"}, filterExpr: "", orderByCount: 0, limit: 0},
			},
		},
		{
			name:              "Subquery with WHERE and ORDER BY",
			input:             "SELECT Id, (SELECT Id, Subject FROM Tasks WHERE Status = 'Open' ORDER BY CreatedDate DESC) FROM Contact",
			expectedObj:       "Contact",
			expectedFields:    []string{"Id"},
			expectedSubQCount: 1,
			subQueryChecks: []struct {
				relName      string
				fields       []string
				filterExpr   string
				orderByCount int
				limit        int
			}{
				{relName: "Tasks", fields: []string{"Id", "Subject"}, filterExpr: "Status == 'Open'", orderByCount: 1, limit: 0},
			},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			l := NewLexer(tt.input)
			p := NewParser(l)
			req, err := p.Parse()

			if tt.expectError {
				if err == nil {
					t.Errorf("expected error, got nil")
				}
				return
			}

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

			if req.ObjectAPIName != tt.expectedObj {
				t.Errorf("Object: expected %s, got %s", tt.expectedObj, req.ObjectAPIName)
			}

			// Check regular fields
			for _, f := range tt.expectedFields {
				found := false
				for _, sf := range req.SelectFields {
					if sf == f {
						found = true
						break
					}
				}
				if !found {
					t.Errorf("expected field %q in SelectFields %v", f, req.SelectFields)
				}
			}

			// Check subqueries
			if len(req.SubQueries) != tt.expectedSubQCount {
				t.Fatalf("SubQueries: expected %d, got %d", tt.expectedSubQCount, len(req.SubQueries))
			}

			for i, check := range tt.subQueryChecks {
				sq := req.SubQueries[i]
				if sq.RelationshipName != check.relName {
					t.Errorf("SubQuery[%d] RelationshipName: expected %q, got %q", i, check.relName, sq.RelationshipName)
				}
				if len(sq.SelectFields) != len(check.fields) {
					t.Errorf("SubQuery[%d] fields count: expected %d, got %d (%v)", i, len(check.fields), len(sq.SelectFields), sq.SelectFields)
				} else {
					for j, f := range check.fields {
						if sq.SelectFields[j] != f {
							t.Errorf("SubQuery[%d] field[%d]: expected %q, got %q", i, j, f, sq.SelectFields[j])
						}
					}
				}
				if sq.FilterExpr != check.filterExpr {
					t.Errorf("SubQuery[%d] FilterExpr: expected %q, got %q", i, check.filterExpr, sq.FilterExpr)
				}
				if len(sq.OrderBy) != check.orderByCount {
					t.Errorf("SubQuery[%d] OrderBy count: expected %d, got %d", i, check.orderByCount, len(sq.OrderBy))
				}
				if sq.Limit != check.limit {
					t.Errorf("SubQuery[%d] Limit: expected %d, got %d", i, check.limit, sq.Limit)
				}
			}
		})
	}
}

// TestApprovalQueries covers the exact SOQL queries used by the approval pages.
// These were previously failing with "expected FROM".
func TestApprovalQueries(t *testing.T) {
	tests := []struct {
		name          string
		input         string
		expectedObj   string
		expectedGroup string
		expectFields  []string
		expectSortN   int // number of orDER BY fields
	}{
		{
			name:          "Aggregate with alias and GROUP BY",
			input:         "SELECT ProcessDefinitionId, COUNT(Id) PendingCount FROM ProcessInstance WHERE Status = 'Pending' GROUP BY ProcessDefinitionId",
			expectedObj:   "ProcessInstance",
			expectedGroup: "ProcessDefinitionId",
			expectFields:  []string{"ProcessDefinitionId", "COUNT(Id) PendingCount"},
		},
		{
			name:        "Dot-notation relationship fields with multi-field orDER BY",
			input:       "SELECT Id, Name, DeveloperName, Description, State, TableEnumOrId, LockType, CreatedDate, CreatedBy.Name, LastModifiedDate, LastModifiedBy.Name FROM ProcessDefinition orDER BY State ASC, Name ASC",
			expectedObj: "ProcessDefinition",
			expectFields: []string{
				"Id", "Name", "DeveloperName", "Description", "State",
				"TableEnumOrId", "LockType", "CreatedDate",
				"CreatedBy.Name", "LastModifiedDate", "LastModifiedBy.Name",
			},
			expectSortN: 2,
		},
		{
			name:        "Multi-level relationship traversal",
			input:       "SELECT Id, ProcessInstanceId, ProcessInstance.TargetObjectId, ProcessInstance.TargetObject.Name, ProcessInstance.ProcessDefinition.TableEnumOrId, CreatedDate, ActorId FROM ProcessInstanceWorkitem orDER BY CreatedDate DESC",
			expectedObj: "ProcessInstanceWorkitem",
			expectFields: []string{
				"Id", "ProcessInstanceId",
				"ProcessInstance.TargetObjectId",
				"ProcessInstance.TargetObject.Name",
				"ProcessInstance.ProcessDefinition.TableEnumOrId",
				"CreatedDate", "ActorId",
			},
			expectSortN: 1,
		},
		{
			name:         "Workitem query with WHERE and ORDER BY",
			input:        "SELECT Id, ProcessInstanceId, CreatedDate, ActorId, Comments FROM ProcessInstanceWorkitem WHERE ActorId = '005xx0000001234' ORDER BY CreatedDate DESC",
			expectedObj:  "ProcessInstanceWorkitem",
			expectFields: []string{"Id", "ProcessInstanceId", "CreatedDate", "ActorId", "Comments"},
			expectSortN:  1,
		},
		{
			name:         "ProcessInstance pending filter with ORDER BY",
			input:        "SELECT Id, TargetObjectId, ProcessDefinitionId FROM ProcessInstance WHERE Status = 'Pending' ORDER BY CreatedDate DESC",
			expectedObj:  "ProcessInstance",
			expectFields: []string{"Id", "TargetObjectId", "ProcessDefinitionId"},
			expectSortN:  1,
		},
		{
			name:         "Simple ProcessDefinition select",
			input:        "SELECT Id, TableEnumOrId FROM ProcessDefinition",
			expectedObj:  "ProcessDefinition",
			expectFields: []string{"Id", "TableEnumOrId"},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			l := NewLexer(tt.input)
			p := NewParser(l)
			req, err := p.Parse()
			if err != nil {
				t.Fatalf("Parse() failed: %v", err)
			}
			if req.ObjectAPIName != tt.expectedObj {
				t.Errorf("Object: expected %s, got %s", tt.expectedObj, req.ObjectAPIName)
			}
			if tt.expectedGroup != "" && req.GroupBy != tt.expectedGroup {
				t.Errorf("GroupBy: expected %s, got %s", tt.expectedGroup, req.GroupBy)
			}
			for _, f := range tt.expectFields {
				found := false
				for _, sf := range req.SelectFields {
					if sf == f {
						found = true
						break
					}
				}
				if !found {
					t.Errorf("expected field %q in SelectFields %v", f, req.SelectFields)
				}
			}
			if tt.expectSortN > 0 && len(req.OrderBy) != tt.expectSortN {
				t.Errorf("orDER BY: expected %d sort fields, got %d (%v)", tt.expectSortN, len(req.OrderBy), req.OrderBy)
			}
		})
	}
}

func TestOrderByFunction(t *testing.T) {
	tests := []struct {
		name      string
		input     string
		sortField string
		sortDir   string
	}{
		{
			name:      "ORDER BY COUNT(Id) DESC",
			input:     "SELECT Status, COUNT(Id) FROM Quote GROUP BY Status ORDER BY COUNT(Id) DESC LIMIT 10",
			sortField: "COUNT(Id)",
			sortDir:   "DESC",
		},
		{
			name:      "ORDER BY SUM(Amount) ASC",
			input:     "SELECT OwnerId, SUM(Amount) FROM Opportunity GROUP BY OwnerId ORDER BY SUM(Amount) ASC",
			sortField: "SUM(Amount)",
			sortDir:   "ASC",
		},
		{
			name:      "ORDER BY field then function",
			input:     "SELECT Status, COUNT(Id) cnt FROM Quote GROUP BY Status ORDER BY Status ASC",
			sortField: "Status",
			sortDir:   "ASC",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			l := NewLexer(tt.input)
			p := NewParser(l)
			req, err := p.Parse()
			if err != nil {
				t.Fatalf("Parse() failed: %v", err)
			}
			if req.SortField != tt.sortField {
				t.Errorf("SortField: expected %q, got %q", tt.sortField, req.SortField)
			}
			if req.SortDirection != tt.sortDir {
				t.Errorf("SortDirection: expected %q, got %q", tt.sortDir, req.SortDirection)
			}
		})
	}
}

func TestOrderByFunctionEdgeCases(t *testing.T) {
	successTests := []struct {
		name      string
		input     string
		sortField string
		sortDir   string
		orderBy   []struct {
			field string
			dir   string
		}
		limit int
	}{
		{
			name:      "Nested function: TOSTRING(COUNT(Id))",
			input:     "SELECT COUNT(Id) FROM Account GROUP BY Status ORDER BY TOSTRING(COUNT(Id)) DESC",
			sortField: "TOSTRING(COUNT(Id))",
			sortDir:   "DESC",
			orderBy: []struct {
				field string
				dir   string
			}{{"TOSTRING(COUNT(Id))", "DESC"}},
		},
		{
			name:      "Empty parens: COUNT()",
			input:     "SELECT COUNT(Id) FROM Account ORDER BY COUNT() DESC",
			sortField: "COUNT()",
			sortDir:   "DESC",
			orderBy: []struct {
				field string
				dir   string
			}{{"COUNT()", "DESC"}},
		},
		{
			name:      "Multiple function fields in ORDER BY",
			input:     "SELECT COUNT(Id), SUM(Amount) FROM Opportunity GROUP BY StageName ORDER BY COUNT(Id) DESC, SUM(Amount) ASC",
			sortField: "COUNT(Id)",
			sortDir:   "DESC",
			orderBy: []struct {
				field string
				dir   string
			}{{"COUNT(Id)", "DESC"}, {"SUM(Amount)", "ASC"}},
		},
		{
			name:      "Function with multi-args",
			input:     "SELECT Id FROM Account ORDER BY FORMAT(a, b) DESC",
			sortField: "FORMAT(a,b)",
			sortDir:   "DESC",
			orderBy: []struct {
				field string
				dir   string
			}{{"FORMAT(a,b)", "DESC"}},
		},
		{
			name:      "Function then LIMIT",
			input:     "SELECT COUNT(Id) FROM Account GROUP BY Status ORDER BY COUNT(Id) DESC LIMIT 5",
			sortField: "COUNT(Id)",
			sortDir:   "DESC",
			orderBy: []struct {
				field string
				dir   string
			}{{"COUNT(Id)", "DESC"}},
			limit: 5,
		},
		{
			name:      "Mixed 3-field: plain, function, plain",
			input:     "SELECT COUNT(Id) FROM Opportunity GROUP BY StageName ORDER BY StageName DESC, COUNT(Id) ASC, Name ASC",
			sortField: "StageName",
			sortDir:   "DESC",
			orderBy: []struct {
				field string
				dir   string
			}{{"StageName", "DESC"}, {"COUNT(Id)", "ASC"}, {"Name", "ASC"}},
		},
		{
			name:      "Default direction with function",
			input:     "SELECT COUNT(Id) FROM Account GROUP BY Status ORDER BY COUNT(Id)",
			sortField: "COUNT(Id)",
			sortDir:   "ASC",
			orderBy: []struct {
				field string
				dir   string
			}{{"COUNT(Id)", "ASC"}},
		},
	}

	for _, tt := range successTests {
		t.Run(tt.name, func(t *testing.T) {
			l := NewLexer(tt.input)
			p := NewParser(l)
			req, err := p.Parse()
			if err != nil {
				t.Fatalf("Parse() failed: %v", err)
			}
			if req.SortField != tt.sortField {
				t.Errorf("SortField: expected %q, got %q", tt.sortField, req.SortField)
			}
			if req.SortDirection != tt.sortDir {
				t.Errorf("SortDirection: expected %q, got %q", tt.sortDir, req.SortDirection)
			}
			if len(req.OrderBy) != len(tt.orderBy) {
				t.Fatalf("OrderBy count: expected %d, got %d (%v)", len(tt.orderBy), len(req.OrderBy), req.OrderBy)
			}
			for i, expected := range tt.orderBy {
				if req.OrderBy[i].Field != expected.field {
					t.Errorf("OrderBy[%d].Field: expected %q, got %q", i, expected.field, req.OrderBy[i].Field)
				}
				if req.OrderBy[i].Direction != expected.dir {
					t.Errorf("OrderBy[%d].Direction: expected %q, got %q", i, expected.dir, req.OrderBy[i].Direction)
				}
			}
			if tt.limit != 0 && req.Limit != tt.limit {
				t.Errorf("Limit: expected %d, got %d", tt.limit, req.Limit)
			}
		})
	}

	errorTests := []struct {
		name      string
		input     string
		errSubstr string
	}{
		{
			name:      "Mismatched parens: missing closing paren",
			input:     "SELECT Id FROM Account ORDER BY COUNT(Id",
			errSubstr: "unexpected EOF in ORDER BY function",
		},
		{
			name:      "Extra open paren: COUNT((Id)",
			input:     "SELECT Id FROM Account ORDER BY COUNT((Id)",
			errSubstr: "unexpected EOF in ORDER BY function",
		},
	}

	for _, tt := range errorTests {
		t.Run(tt.name, func(t *testing.T) {
			l := NewLexer(tt.input)
			p := NewParser(l)
			_, err := p.Parse()
			if err == nil {
				t.Fatalf("expected error containing %q, got nil", tt.errSubstr)
			}
			if !strings.Contains(err.Error(), tt.errSubstr) {
				t.Errorf("expected error containing %q, got %q", tt.errSubstr, err.Error())
			}
		})
	}
}

func TestSubQueryOrderByFunction(t *testing.T) {
	tests := []struct {
		name    string
		input   string
		orderBy []struct {
			field string
			dir   string
		}
		limit       int
		expectError bool
		errSubstr   string
	}{
		{
			name:  "Subquery ORDER BY function",
			input: "SELECT Id, (SELECT Id FROM Contacts ORDER BY COUNT(Id) DESC) FROM Account",
			orderBy: []struct {
				field string
				dir   string
			}{{"COUNT(Id)", "DESC"}},
		},
		{
			name:  "Subquery ORDER BY function then LIMIT",
			input: "SELECT Id, (SELECT Id FROM Contacts ORDER BY COUNT(Id) DESC LIMIT 5) FROM Account",
			orderBy: []struct {
				field string
				dir   string
			}{{"COUNT(Id)", "DESC"}},
			limit: 5,
		},
		{
			name:  "Subquery ORDER BY nested function",
			input: "SELECT Id, (SELECT Id FROM Contacts ORDER BY TOSTRING(COUNT(Id)) ASC) FROM Account",
			orderBy: []struct {
				field string
				dir   string
			}{{"TOSTRING(COUNT(Id))", "ASC"}},
		},
		{
			name:  "Subquery ORDER BY function default direction",
			input: "SELECT Id, (SELECT Id FROM Contacts ORDER BY COUNT(Id)) FROM Account",
			orderBy: []struct {
				field string
				dir   string
			}{{"COUNT(Id)", "ASC"}},
		},
		{
			name:        "Subquery ORDER BY mismatched parens",
			input:       "SELECT Id, (SELECT Id FROM Contacts ORDER BY COUNT(Id",
			expectError: true,
			errSubstr:   "unexpected EOF in subquery ORDER BY function",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			l := NewLexer(tt.input)
			p := NewParser(l)
			req, err := p.Parse()

			if tt.expectError {
				if err == nil {
					t.Fatalf("expected error containing %q, got nil", tt.errSubstr)
				}
				if !strings.Contains(err.Error(), tt.errSubstr) {
					t.Errorf("expected error containing %q, got %q", tt.errSubstr, err.Error())
				}
				return
			}

			if err != nil {
				t.Fatalf("Parse() failed: %v", err)
			}
			if len(req.SubQueries) != 1 {
				t.Fatalf("expected 1 subquery, got %d", len(req.SubQueries))
			}
			sq := req.SubQueries[0]
			if len(sq.OrderBy) != len(tt.orderBy) {
				t.Fatalf("SubQuery OrderBy count: expected %d, got %d (%v)", len(tt.orderBy), len(sq.OrderBy), sq.OrderBy)
			}
			for i, expected := range tt.orderBy {
				if sq.OrderBy[i].Field != expected.field {
					t.Errorf("SubQuery OrderBy[%d].Field: expected %q, got %q", i, expected.field, sq.OrderBy[i].Field)
				}
				if sq.OrderBy[i].Direction != expected.dir {
					t.Errorf("SubQuery OrderBy[%d].Direction: expected %q, got %q", i, expected.dir, sq.OrderBy[i].Direction)
				}
			}
			if tt.limit != 0 && sq.Limit != tt.limit {
				t.Errorf("SubQuery Limit: expected %d, got %d", tt.limit, sq.Limit)
			}
		})
	}
}

func TestFunctionAllowlist(t *testing.T) {
	// Allowed functions should parse successfully
	allowedTests := []struct {
		name      string
		input     string
		sortField string
	}{
		{"COUNT in ORDER BY", "SELECT COUNT(Id) FROM Account GROUP BY Status ORDER BY COUNT(Id) DESC", "COUNT(Id)"},
		{"SUM in ORDER BY", "SELECT SUM(Amount) FROM Opp GROUP BY Stage ORDER BY SUM(Amount) ASC", "SUM(Amount)"},
		{"AVG in ORDER BY", "SELECT AVG(Amount) FROM Opp GROUP BY Stage ORDER BY AVG(Amount) DESC", "AVG(Amount)"},
		{"MIN in ORDER BY", "SELECT MIN(Amount) FROM Opp GROUP BY Stage ORDER BY MIN(Amount) ASC", "MIN(Amount)"},
		{"MAX in ORDER BY", "SELECT MAX(Amount) FROM Opp GROUP BY Stage ORDER BY MAX(Amount) DESC", "MAX(Amount)"},
		{"Case insensitive: count", "SELECT count(Id) FROM Account GROUP BY Status ORDER BY count(Id) DESC", "count(Id)"},
		{"Case insensitive: Count", "SELECT Count(Id) FROM Account GROUP BY Status ORDER BY Count(Id) DESC", "Count(Id)"},
		{"COUNT in SELECT only", "SELECT COUNT(Id) FROM Account", ""},
		{"SUM in SELECT only", "SELECT SUM(Amount) FROM Opp", ""},
		{"FORMAT in SELECT", "SELECT FORMAT(Amount) FROM Opp", ""},
		{"TOLABEL in SELECT", "SELECT TOLABEL(Status) FROM Opp", ""},
		{"CALENDAR_MONTH in SELECT", "SELECT CALENDAR_MONTH(CreatedDate) FROM Account", ""},
		{"DISTANCE in SELECT", "SELECT DISTANCE(Location__c, GEOLOCATION(37.78, -122.39), 'mi') FROM Account", ""},
		{"DISTANCE in ORDER BY", "SELECT Id FROM Account ORDER BY DISTANCE(Location__c, GEOLOCATION(37.78, -122.39), 'mi') ASC", "DISTANCE(Location__c,GEOLOCATION(37.78,-122.39),mi)"},
		{"GEOLOCATION in SELECT", "SELECT GEOLOCATION(37.78, -122.39) FROM Account", ""},
	}

	for _, tt := range allowedTests {
		t.Run(tt.name, func(t *testing.T) {
			l := NewLexer(tt.input)
			p := NewParser(l)
			req, err := p.Parse()
			if err != nil {
				t.Fatalf("expected allowed function to parse, got error: %v", err)
			}
			if tt.sortField != "" && req.SortField != tt.sortField {
				t.Errorf("SortField: expected %q, got %q", tt.sortField, req.SortField)
			}
		})
	}

	// Disallowed functions should return errors
	disallowedTests := []struct {
		name      string
		input     string
		errSubstr string
	}{
		{"SLEEP in ORDER BY", "SELECT Id FROM Account ORDER BY SLEEP(5) DESC", "unrecognized function in ORDER BY: SLEEP"},
		{"BENCHMARK in ORDER BY", "SELECT Id FROM Account ORDER BY BENCHMARK(1000) DESC", "unrecognized function in ORDER BY: BENCHMARK"},
		{"LOAD_FILE in ORDER BY", "SELECT Id FROM Account ORDER BY LOAD_FILE(x) DESC", "unrecognized function in ORDER BY: LOAD_FILE"},
		{"SLEEP in SELECT", "SELECT SLEEP(5) FROM Account", "unrecognized function in SELECT: SLEEP"},
		{"BENCHMARK in SELECT", "SELECT BENCHMARK(1000) FROM Account", "unrecognized function in SELECT: BENCHMARK"},
		{"Unknown func in subquery ORDER BY", "SELECT Id, (SELECT Id FROM Contacts ORDER BY SLEEP(5) DESC) FROM Account", "unrecognized function in subquery ORDER BY: SLEEP"},
		{"Unknown func in subquery SELECT", "SELECT Id, (SELECT SLEEP(5) FROM Contacts) FROM Account", "unrecognized function in subquery SELECT: SLEEP"},
	}

	for _, tt := range disallowedTests {
		t.Run(tt.name, func(t *testing.T) {
			l := NewLexer(tt.input)
			p := NewParser(l)
			_, err := p.Parse()
			if err == nil {
				t.Fatalf("expected error for disallowed function, got nil")
			}
			if !strings.Contains(err.Error(), tt.errSubstr) {
				t.Errorf("expected error containing %q, got %q", tt.errSubstr, err.Error())
			}
		})
	}
}

func TestSubqueryErrorsReturnValidationError(t *testing.T) {
	tests := []struct {
		name  string
		input string
	}{
		{
			name:  "unrecognized function in subquery SELECT",
			input: "SELECT (SELECT SLEEP(1) FROM Contacts) FROM Account",
		},
		{
			name:  "unexpected token in subquery",
			input: "SELECT (SELECT Id FROM Contacts HAVING count > 1) FROM Account",
		},
		{
			name:  "missing closing paren in subquery",
			input: "SELECT (SELECT Id FROM Contacts",
		},
		{
			name:  "unrecognized function in subquery ORDER BY",
			input: "SELECT (SELECT Id FROM Contacts ORDER BY SLEEP(1)) FROM Account",
		},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			l := NewLexer(tt.input)
			p := NewParser(l)
			_, err := p.Parse()
			if err == nil {
				t.Fatal("expected error, got nil")
			}
			var ve *apperrors.ValidationError
			if !errors.As(err, &ve) {
				t.Errorf("expected *ValidationError, got %T: %v", err, err)
			}
		})
	}
}

func TestParseCallExpressionDepthGuard(t *testing.T) {
	// Build a query with nesting depth exceeding maxCallDepth (10).
	query := "SELECT Id FROM Account WHERE "
	for i := 0; i < 11; i++ {
		query += "DISTANCE("
	}
	query += "1"
	for i := 0; i < 11; i++ {
		query += ")"
	}

	l := NewLexer(query)
	p := NewParser(l)
	_, err := p.Parse()
	if err == nil {
		t.Fatal("expected error for deeply nested function calls, got nil")
	}
	var ve *apperrors.ValidationError
	if !errors.As(err, &ve) {
		t.Fatalf("expected *ValidationError, got %T: %v", err, err)
	}
	if !strings.Contains(err.Error(), "nesting depth exceeded") {
		t.Errorf("expected 'nesting depth exceeded' in error, got: %s", err.Error())
	}
}

func TestParseCallExpressionAtMaxDepth(t *testing.T) {
	// Build a query with exactly maxCallDepth (10) nesting — should succeed.
	query := "SELECT Id FROM Account WHERE "
	for i := 0; i < 10; i++ {
		query += "DISTANCE("
	}
	query += "1"
	for i := 0; i < 10; i++ {
		query += ")"
	}
	query += " = 5"

	l := NewLexer(query)
	p := NewParser(l)
	_, err := p.Parse()
	if err != nil {
		t.Fatalf("expected no error for depth=10, got: %v", err)
	}
}