package metadata

import (
	"context"
	"encoding/json"
	"fmt"
	"reflect"
	"strconv"
	"strings"
	"sync"
	"time"
	"unicode"

	"github.com/nexuscrm/kernel/pkg/formula"
	"github.com/nexuscrm/kernel/pkg/ports"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/nexuscrm/shared/pkg/queryspec"
)

const validationRecordSetBindingQueryLimit = 500

// Keep exact truth-table prechecks bounded. Record-set bindings are boolean
// formula inputs and real rules carry only a handful; above this limit the
// runtime preserves the compatibility path and resolves them normally.
const validationRecordSetBindingIndependenceLimit = 8

type validationRecordSetBindingQueryCacheContextKey struct{}

type validationRecordSetBindingQueryKey struct {
	objectAPIName string
	filterExpr    string
	selectKey     string
	sortField     string
	sortDir       string
	limit         int
}

type validationRecordSetBindingQueryCache struct {
	mu      sync.Mutex
	results map[validationRecordSetBindingQueryKey]*models.QueryResult
}

// validationRuleOutcomeIndependentOfRecordSetBindings evaluates the rule over
// every possible boolean assignment of its record-set bindings. If every
// assignment produces the same boolean, no record-set query can affect the
// rule outcome and resolving those bindings would be work without a consumer.
//
// The enumeration is exact rather than a monotonicity guess, so NOT/OR/XOR-like
// formulas remain safe. Rules with relationship operands keep the established
// path to avoid multiplying relationship reads during the precheck.
func (vs *ValidationService) validationRuleOutcomeIndependentOfRecordSetBindings(
	ctx context.Context,
	formulaEngine *formula.Engine,
	schema *models.ObjectMetadata,
	record map[string]interface{},
	prior map[string]interface{},
	rule *models.ValidationRule,
	userContext map[string]interface{},
	baseFields map[string]interface{},
) (bool, bool) {
	if vs == nil || formulaEngine == nil || rule == nil || len(rule.RecordSetBindings) == 0 ||
		len(rule.RecordSetBindings) > validationRecordSetBindingIndependenceLimit ||
		len(extractValidationRelationRoots(rule.Condition)) != 0 {
		return false, false
	}

	seen := make(map[string]struct{}, len(rule.RecordSetBindings))
	names := make([]string, 0, len(rule.RecordSetBindings))
	for i := range rule.RecordSetBindings {
		binding := rule.RecordSetBindings[i]
		if err := vs.validateRecordSetBindingBasic(schema, binding, seen); err != nil {
			return false, false
		}
		names = append(names, strings.TrimSpace(binding.Name))
	}

	expression := rewriteValidationFormulaExpression(rule.Condition, schema)
	var expected bool
	for mask := 0; mask < 1<<uint(len(names)); mask++ {
		fields := make(map[string]interface{}, len(baseFields)+len(names))
		for name, value := range baseFields {
			fields[name] = value
		}
		for i, name := range names {
			fields[name] = mask&(1<<uint(i)) != 0
		}
		result, err := formulaEngine.Evaluate(expression, &formula.Context{
			Record: cloneValidationFormulaMap(record),
			Prior:  cloneValidationFormulaMap(prior),
			User:   userContext,
			Fields: fields,
			Schema: schema,
		})
		value, ok := result.(bool)
		if err != nil || !ok {
			return false, false
		}
		if mask == 0 {
			expected = value
			continue
		}
		if value != expected {
			return false, false
		}
	}
	return expected, true
}

func withValidationRecordSetBindingQueryCache(ctx context.Context) context.Context {
	if ctx == nil {
		ctx = context.Background()
	}
	return context.WithValue(ctx, validationRecordSetBindingQueryCacheContextKey{}, &validationRecordSetBindingQueryCache{
		results: make(map[validationRecordSetBindingQueryKey]*models.QueryResult),
	})
}

func validationRecordSetBindingQuery(
	ctx context.Context,
	key validationRecordSetBindingQueryKey,
	load func() (*models.QueryResult, error),
) (*models.QueryResult, error) {
	if ctx == nil {
		return load()
	}
	if err := ctx.Err(); err != nil {
		return nil, err
	}
	cache, _ := ctx.Value(validationRecordSetBindingQueryCacheContextKey{}).(*validationRecordSetBindingQueryCache)
	if cache == nil {
		return load()
	}
	key.objectAPIName = strings.ToLower(strings.TrimSpace(key.objectAPIName))
	key.filterExpr = strings.TrimSpace(key.filterExpr)
	key.sortField = strings.TrimSpace(key.sortField)
	key.sortDir = strings.ToLower(strings.TrimSpace(key.sortDir))

	cache.mu.Lock()
	result, ok := cache.results[key]
	cache.mu.Unlock()
	if ok {
		return cloneValidationRecordSetQueryResult(result), nil
	}

	result, err := load()
	if err != nil {
		return nil, err
	}
	cache.mu.Lock()
	cache.results[key] = cloneValidationRecordSetQueryResult(result)
	cache.mu.Unlock()
	return cloneValidationRecordSetQueryResult(result), nil
}

func cloneValidationRecordSetQueryResult(result *models.QueryResult) *models.QueryResult {
	if result == nil {
		return nil
	}
	cloned := *result
	if len(result.Records) == 0 {
		cloned.Records = nil
		return &cloned
	}
	cloned.Records = make([]models.SObject, len(result.Records))
	for i, record := range result.Records {
		if record == nil {
			continue
		}
		clonedRecord := make(models.SObject, len(record))
		for name, value := range record {
			clonedRecord[name] = cloneValidationRecordSetQueryValue(value)
		}
		cloned.Records[i] = clonedRecord
	}
	return &cloned
}

func cloneValidationRecordSetQueryValue(value interface{}) interface{} {
	switch typed := value.(type) {
	case models.SObject:
		cloned := make(models.SObject, len(typed))
		for name, nested := range typed {
			cloned[name] = cloneValidationRecordSetQueryValue(nested)
		}
		return cloned
	case map[string]interface{}:
		cloned := make(map[string]interface{}, len(typed))
		for name, nested := range typed {
			cloned[name] = cloneValidationRecordSetQueryValue(nested)
		}
		return cloned
	case []interface{}:
		cloned := make([]interface{}, len(typed))
		for i, nested := range typed {
			cloned[i] = cloneValidationRecordSetQueryValue(nested)
		}
		return cloned
	case []models.SObject:
		cloned := make([]models.SObject, len(typed))
		for i, record := range typed {
			value := cloneValidationRecordSetQueryValue(record)
			cloned[i], _ = value.(models.SObject)
		}
		return cloned
	default:
		return value
	}
}

type resolvedRecordSetFilter struct {
	filter models.ValidationRuleRecordSetFilter
	op     string
	value  interface{}
	field  *models.FieldMetadata
}

type recordSetSchemaLookup func(objectAPIName string) *models.ObjectMetadata

func (vs *ValidationService) resolveRecordSetBindingFields(
	ctx context.Context,
	schema *models.ObjectMetadata,
	record models.SObject,
	rule *models.ValidationRule,
	userContext map[string]interface{},
) (map[string]interface{}, error) {
	if rule == nil || len(rule.RecordSetBindings) == 0 {
		return nil, nil
	}
	if vs.recordSetResolve == nil {
		return nil, fmt.Errorf("recordSetBindings are declared but no resolver is configured")
	}

	fields := make(map[string]interface{}, len(rule.RecordSetBindings))
	seen := map[string]struct{}{}
	for i := range rule.RecordSetBindings {
		binding := rule.RecordSetBindings[i]
		if err := vs.validateRecordSetBindingBasic(schema, binding, seen); err != nil {
			return nil, err
		}
		result, err := vs.recordSetResolve(ctx, schema, record, rule, binding, userContext)
		if err != nil {
			return nil, err
		}
		fields[strings.TrimSpace(binding.Name)] = result
	}
	return fields, nil
}

func (vs *ValidationService) validateRecordSetBindingBasic(schema *models.ObjectMetadata, binding models.ValidationRuleRecordSetBinding, seen map[string]struct{}) error {
	name := strings.TrimSpace(binding.Name)
	if name == "" {
		return fmt.Errorf("recordSetBinding name is required")
	}
	if !isValidIdentifier(name) {
		return fmt.Errorf("recordSetBinding %q has an invalid name", name)
	}
	if _, ok := seen[strings.ToLower(name)]; ok {
		return fmt.Errorf("recordSetBinding %q is duplicated", name)
	}
	seen[strings.ToLower(name)] = struct{}{}
	if field := validationFieldByKey(schema, name); field != nil {
		return fmt.Errorf("recordSetBinding %q collides with field %s", name, field.APIName)
	}
	if mode := strings.TrimSpace(binding.Mode); !strings.EqualFold(mode, "exists") {
		return fmt.Errorf("recordSetBinding %q has unsupported mode %q", name, binding.Mode)
	}
	if strings.TrimSpace(binding.ObjectAPIName) == "" {
		return fmt.Errorf("recordSetBinding %q requires objectApiName", name)
	}
	if len(binding.Filters) == 0 {
		return fmt.Errorf("recordSetBinding %q requires at least one filter", name)
	}
	dummy := make([]bool, len(binding.Filters))
	for i := range dummy {
		dummy[i] = true
	}
	logicExpr, err := recordSetFilterLogicExpression(binding.FilterLogic, dummy)
	if err != nil {
		return fmt.Errorf("recordSetBinding %q has invalid filterLogic: %w", name, err)
	}
	if _, err := vs.formula.Evaluate(logicExpr, &formula.Context{}); err != nil {
		return fmt.Errorf("recordSetBinding %q has malformed filterLogic: %w", name, err)
	}
	for i, filter := range binding.Filters {
		if strings.TrimSpace(filter.Field) == "" {
			return fmt.Errorf("recordSetBinding %q filter %d requires field", name, i+1)
		}
		if _, ok := normalizeRecordSetOperator(filter.Operator); !ok {
			return fmt.Errorf("recordSetBinding %q filter %d has unsupported operator %q", name, i+1, filter.Operator)
		}
		source, ok := normalizeRecordSetValueSource(filter.ValueSource)
		if !ok {
			return fmt.Errorf("recordSetBinding %q filter %d has unsupported valueSource %q", name, i+1, filter.ValueSource)
		}
		if source != "literal" {
			if _, ok := filter.Value.(string); !ok {
				return fmt.Errorf("recordSetBinding %q filter %d value must be a path string for valueSource %q", name, i+1, filter.ValueSource)
			}
		}
	}
	return nil
}

// NewValidationRecordSetBindingResolver builds the generic v1 resolver for
// validation-rule recordSetBindings. The concrete business rule stays in
// metadata; this resolver only evaluates the structured binding contract.
func NewValidationRecordSetBindingResolver(
	metadataSvc *MetadataService,
	querySvc ports.QueryService,
	relationResolve FormulaRelationResolver,
) RecordSetBindingResolver {
	if metadataSvc == nil || querySvc == nil {
		return nil
	}
	engine := formula.NewEngine()

	return func(ctx context.Context, rootSchema *models.ObjectMetadata, rootRecord models.SObject, rule *models.ValidationRule, binding models.ValidationRuleRecordSetBinding, userContext map[string]interface{}) (bool, error) {
		if ctx == nil {
			ctx = context.Background()
		}
		schemaCtx, cancelSchema := context.WithTimeout(ctx, 5*time.Second)
		defer cancelSchema()

		candidateSchema := metadataSvc.GetSchema(schemaCtx, binding.ObjectAPIName)
		if candidateSchema == nil {
			return false, fmt.Errorf("recordSetBinding %q references unknown object %q", binding.Name, binding.ObjectAPIName)
		}
		lookup := func(objectAPIName string) *models.ObjectMetadata {
			if strings.EqualFold(objectAPIName, "RecordType") {
				return recordTypeValidationPseudoSchema()
			}
			lookupCtx, cancelLookup := context.WithTimeout(ctx, 5*time.Second)
			defer cancelLookup()
			return metadataSvc.GetSchema(lookupCtx, objectAPIName)
		}
		if err := validateRecordSetBindingSchemas(rootSchema, candidateSchema, binding, lookup); err != nil {
			return false, err
		}

		resolved, err := resolveRecordSetFilters(ctx, rootSchema, candidateSchema, rootRecord, rule, binding, userContext, relationResolve, lookup)
		if err != nil {
			return false, err
		}
		// #1747: short-circuit a deterministically-false binding without querying (and
		// without rendering a nil-ordered leaf to SQL). This is what stops the Draft-Order
		// 500: its date recordPaths resolve to nil, the AND-joined binding is unsatisfiable.
		if recordSetBindingDeterministicallyFalse(binding.FilterLogic, resolved, engine) {
			return false, nil
		}
		filterExpr := recordSetPushdownFilter(binding.FilterLogic, resolved)
		queryCtx, cancelQuery := context.WithTimeout(
			constants.WithSecurityContext(ctx, constants.SystemContext, "validation rule record-set binding"),
			10*time.Second,
		)
		defer cancelQuery()
		selectFields := validationRecordSetBindingSelectFields(binding)
		result, err := validationRecordSetBindingQuery(ctx, validationRecordSetBindingQueryKey{
			objectAPIName: binding.ObjectAPIName,
			filterExpr:    filterExpr,
			selectKey:     validationRecordSetBindingSelectKey(selectFields),
			limit:         validationRecordSetBindingQueryLimit,
		}, func() (*models.QueryResult, error) {
			return querySvc.QueryWithSpec(queryCtx, queryspec.QuerySpec{
				ObjectAPIName:      binding.ObjectAPIName,
				SelectFields:       selectFields,
				Filter:             queryspec.FormulaPredicate(filterExpr),
				Limit:              validationRecordSetBindingQueryLimit,
				ReferenceHydration: queryspec.ReferenceHydrationExplicitOnly,
			}, nil)
		})
		if err != nil {
			return false, fmt.Errorf("query recordSetBinding %q: %w", binding.Name, err)
		}
		return recordSetResultExists(ctx, candidateSchema, result, rule, binding, resolved, relationResolve, lookup, engine)
	}
}

func validationRecordSetBindingSelectFields(binding models.ValidationRuleRecordSetBinding) []string {
	fields := []string{constants.FieldID}
	seen := map[string]struct{}{strings.ToLower(constants.FieldID): {}}
	for _, filter := range binding.Filters {
		field := strings.TrimSpace(filter.Field)
		if field == "" {
			continue
		}
		key := strings.ToLower(field)
		if _, exists := seen[key]; exists {
			continue
		}
		seen[key] = struct{}{}
		fields = append(fields, field)
	}
	return fields
}

func validationRecordSetBindingSelectKey(fields []string) string {
	keys := make([]string, len(fields))
	for i, field := range fields {
		keys[i] = strings.ToLower(strings.TrimSpace(field))
	}
	return strings.Join(keys, "\x1f")
}

func resolveRecordSetFilters(
	ctx context.Context,
	rootSchema *models.ObjectMetadata,
	candidateSchema *models.ObjectMetadata,
	rootRecord models.SObject,
	rule *models.ValidationRule,
	binding models.ValidationRuleRecordSetBinding,
	userContext map[string]interface{},
	relationResolve FormulaRelationResolver,
	lookup recordSetSchemaLookup,
) ([]resolvedRecordSetFilter, error) {
	out := make([]resolvedRecordSetFilter, 0, len(binding.Filters))
	for i, filter := range binding.Filters {
		op, ok := normalizeRecordSetOperator(filter.Operator)
		if !ok {
			return nil, fmt.Errorf("recordSetBinding %q filter %d has unsupported operator %q", binding.Name, i+1, filter.Operator)
		}
		value, err := resolveRecordSetFilterValue(ctx, rootSchema, rootRecord, rule, filter, userContext, relationResolve, lookup)
		if err != nil {
			return nil, fmt.Errorf("recordSetBinding %q filter %d: %w", binding.Name, i+1, err)
		}
		field, err := recordSetTerminalField(candidateSchema, filter.Field, lookup)
		if err != nil {
			return nil, fmt.Errorf("recordSetBinding %q filter %d field path %q is invalid: %w", binding.Name, i+1, filter.Field, err)
		}
		out = append(out, resolvedRecordSetFilter{filter: filter, op: op, value: value, field: field})
	}
	return out, nil
}

func resolveRecordSetFilterValue(
	ctx context.Context,
	rootSchema *models.ObjectMetadata,
	rootRecord models.SObject,
	rule *models.ValidationRule,
	filter models.ValidationRuleRecordSetFilter,
	userContext map[string]interface{},
	relationResolve FormulaRelationResolver,
	lookup recordSetSchemaLookup,
) (interface{}, error) {
	source, _ := normalizeRecordSetValueSource(filter.ValueSource)
	switch source {
	case "literal":
		return filter.Value, nil
	case "recordPath":
		path, _ := filter.Value.(string)
		return resolveRecordSetPathValue(ctx, rootSchema, rootRecord, rule, path, relationResolve, lookup)
	case "userPath":
		path, _ := filter.Value.(string)
		return resolveUserPathValue(userContext, path), nil
	default:
		return nil, fmt.Errorf("unsupported valueSource %q", filter.ValueSource)
	}
}

func validateRecordSetBindingSchemas(rootSchema, candidateSchema *models.ObjectMetadata, binding models.ValidationRuleRecordSetBinding, lookup recordSetSchemaLookup) error {
	for i, filter := range binding.Filters {
		if err := validateRecordSetPath(candidateSchema, filter.Field, lookup); err != nil {
			return fmt.Errorf("recordSetBinding %q filter %d field path %q is invalid: %w", binding.Name, i+1, filter.Field, err)
		}
		source, _ := normalizeRecordSetValueSource(filter.ValueSource)
		if source == "recordPath" {
			path, _ := filter.Value.(string)
			if err := validateRecordSetPath(rootSchema, path, lookup); err != nil {
				return fmt.Errorf("recordSetBinding %q filter %d recordPath %q is invalid: %w", binding.Name, i+1, path, err)
			}
		}
	}
	return nil
}

func validateRecordSetPath(schema *models.ObjectMetadata, path string, lookup recordSetSchemaLookup) error {
	parts := splitRecordSetPath(path)
	if len(parts) == 0 {
		return fmt.Errorf("path is empty")
	}
	currentSchema := schema
	for i, part := range parts {
		if currentSchema == nil {
			return fmt.Errorf("schema is unavailable")
		}
		if i == len(parts)-1 {
			if validationFieldByKey(currentSchema, part) == nil && !recordTypePseudoField(currentSchema, part) {
				return fmt.Errorf("unknown field %q on %s", part, currentSchema.APIName)
			}
			return nil
		}
		nextSchema, err := recordSetRelationTargetSchema(currentSchema, models.SObject{}, part, lookup)
		if err != nil {
			return err
		}
		currentSchema = nextSchema
	}
	return nil
}

func recordSetTerminalField(schema *models.ObjectMetadata, path string, lookup recordSetSchemaLookup) (*models.FieldMetadata, error) {
	parts := splitRecordSetPath(path)
	if len(parts) == 0 {
		return nil, fmt.Errorf("path is empty")
	}
	currentSchema := schema
	for i, part := range parts {
		if currentSchema == nil {
			return nil, fmt.Errorf("schema is unavailable")
		}
		if i == len(parts)-1 {
			if field := validationFieldByKey(currentSchema, part); field != nil {
				return field, nil
			}
			if recordTypePseudoField(currentSchema, part) {
				return nil, nil
			}
			return nil, fmt.Errorf("unknown field %q on %s", part, currentSchema.APIName)
		}
		nextSchema, err := recordSetRelationTargetSchema(currentSchema, models.SObject{}, part, lookup)
		if err != nil {
			return nil, err
		}
		currentSchema = nextSchema
	}
	return nil, nil
}

func resolveRecordSetPathValue(
	ctx context.Context,
	schema *models.ObjectMetadata,
	record models.SObject,
	rule *models.ValidationRule,
	path string,
	relationResolve FormulaRelationResolver,
	lookup recordSetSchemaLookup,
) (interface{}, error) {
	parts := splitRecordSetPath(path)
	if len(parts) == 0 {
		return nil, fmt.Errorf("path is empty")
	}
	currentSchema := schema
	currentRecord := record
	for i, part := range parts {
		if currentSchema == nil {
			return nil, fmt.Errorf("schema is unavailable")
		}
		if i == len(parts)-1 {
			field := validationFieldByKey(currentSchema, part)
			if field == nil && !recordTypePseudoField(currentSchema, part) {
				return nil, fmt.Errorf("unknown field %q on %s", part, currentSchema.APIName)
			}
			if field != nil {
				if v, ok := currentRecord[field.APIName]; ok {
					return v, nil
				}
			}
			return currentRecord[part], nil
		}
		nextSchema, err := recordSetRelationTargetSchema(currentSchema, currentRecord, part, lookup)
		if err != nil {
			return nil, err
		}
		related, ok := nestedRecordSetValue(currentRecord, part)
		if !ok {
			if relationResolve == nil {
				return nil, nil
			}
			var err error
			related, err = relationResolve(ctx, currentSchema.APIName, currentRecord, part)
			if err != nil {
				return nil, err
			}
			if related == nil {
				return nil, nil
			}
		}
		related = applyValidationRuleRelationCompatibility(rule, part, related)
		currentSchema = nextSchema
		currentRecord = related
	}
	return nil, nil
}

func recordSetRelationTargetSchema(schema *models.ObjectMetadata, record models.SObject, relationName string, lookup recordSetSchemaLookup) (*models.ObjectMetadata, error) {
	_, target := resolveValidationRelation(schema, record, relationName)
	if target == "" {
		return nil, fmt.Errorf("unknown relation %q on %s", relationName, schema.APIName)
	}
	if strings.EqualFold(target, "RecordType") {
		return recordTypeValidationPseudoSchema(), nil
	}
	if lookup == nil {
		return nil, fmt.Errorf("schema lookup is unavailable for %s", target)
	}
	targetSchema := lookup(target)
	if targetSchema == nil {
		return nil, fmt.Errorf("unknown relation target %q for %s.%s", target, schema.APIName, relationName)
	}
	return targetSchema, nil
}

func recordSetCandidateMatches(
	ctx context.Context,
	candidateSchema *models.ObjectMetadata,
	candidate models.SObject,
	rule *models.ValidationRule,
	filterLogic string,
	filters []resolvedRecordSetFilter,
	relationResolve FormulaRelationResolver,
	lookup recordSetSchemaLookup,
	engine *formula.Engine,
) (bool, error) {
	matches := make([]bool, len(filters))
	for i, resolved := range filters {
		left, err := resolveRecordSetPathValue(ctx, candidateSchema, candidate, rule, resolved.filter.Field, relationResolve, lookup)
		if err != nil {
			return false, err
		}
		ok, err := compareRecordSetValues(left, resolved.op, resolved.value, resolved.field)
		if err != nil {
			return false, err
		}
		matches[i] = ok
	}
	expr, err := recordSetFilterLogicExpression(filterLogic, matches)
	if err != nil {
		return false, err
	}
	result, err := engine.Evaluate(expr, &formula.Context{})
	if err != nil {
		return false, fmt.Errorf("evaluate filterLogic %q: %w", filterLogic, err)
	}
	matched, _ := result.(bool)
	return matched, nil
}

func recordSetResultExists(
	ctx context.Context,
	candidateSchema *models.ObjectMetadata,
	result *models.QueryResult,
	rule *models.ValidationRule,
	binding models.ValidationRuleRecordSetBinding,
	filters []resolvedRecordSetFilter,
	relationResolve FormulaRelationResolver,
	lookup recordSetSchemaLookup,
	engine *formula.Engine,
) (bool, error) {
	if result == nil {
		return false, nil
	}
	if engine == nil {
		engine = formula.NewEngine()
	}
	for _, candidate := range result.Records {
		matched, err := recordSetCandidateMatches(ctx, candidateSchema, candidate, rule, binding.FilterLogic, filters, relationResolve, lookup, engine)
		if err != nil {
			return false, err
		}
		if matched {
			return true, nil
		}
	}
	if !result.Done {
		return false, fmt.Errorf("recordSetBinding %q exceeded the %d-row safety limit", binding.Name, validationRecordSetBindingQueryLimit)
	}
	return false, nil
}

func recordSetPushdownFilter(filterLogic string, filters []resolvedRecordSetFilter) string {
	indexes := recordSetConjunctiveFilterIndexes(filterLogic, len(filters))
	if len(indexes) == 0 {
		return ""
	}
	parts := make([]string, 0, len(indexes))
	for _, idx := range indexes {
		filter := filters[idx]
		if strings.Contains(filter.filter.Field, ".") {
			continue
		}
		expr := recordSetFilterPushdownExpr(filter)
		if expr == "" {
			continue
		}
		parts = append(parts, expr)
	}
	return strings.Join(parts, " && ")
}

func recordSetConjunctiveFilterIndexes(filterLogic string, filterCount int) []int {
	logic := strings.TrimSpace(filterLogic)
	if logic == "" {
		indexes := make([]int, filterCount)
		for i := range indexes {
			indexes[i] = i
		}
		return indexes
	}
	upper := strings.ToUpper(logic)
	if strings.Contains(upper, "OR") || strings.Contains(upper, "NOT") || strings.Contains(upper, "!") {
		return nil
	}
	refs, err := recordSetReferencedFilterIndexes(logic, filterCount)
	if err != nil {
		return nil
	}
	return refs
}

func recordSetReferencedFilterIndexes(filterLogic string, filterCount int) ([]int, error) {
	seen := map[int]struct{}{}
	for i := 0; i < len(filterLogic); {
		if unicode.IsDigit(rune(filterLogic[i])) {
			start := i
			for i < len(filterLogic) && unicode.IsDigit(rune(filterLogic[i])) {
				i++
			}
			n, err := strconv.Atoi(filterLogic[start:i])
			if err != nil || n < 1 || n > filterCount {
				return nil, fmt.Errorf("filter reference %q is out of range", filterLogic[start:i])
			}
			seen[n-1] = struct{}{}
			continue
		}
		i++
	}
	indexes := make([]int, 0, len(seen))
	for i := 0; i < filterCount; i++ {
		if _, ok := seen[i]; ok {
			indexes = append(indexes, i)
		}
	}
	return indexes, nil
}

// recordSetMaxSatisfiabilityFreeIndices caps the exact satisfiability enumeration in
// recordSetBindingDeterministicallyFalse. Above this many distinct REFERENCED
// non-nil-ordered filters, the short-circuit gives up (the query + in-memory recheck
// decide). Real bindings reference only a handful of filters, so this never fires in
// practice; it is purely a backstop against a pathological 2^k blow-up.
const recordSetMaxSatisfiabilityFreeIndices = 20

// recordSetBindingDeterministicallyFalse reports whether NO candidate can satisfy the
// binding once every ordered-comparison-against-nil leaf (a guaranteed no-match —
// compareRecordSetOrdered no-matches a nil operand) is pinned false. It is an EXACT
// satisfiability check over the filters filterLogic actually REFERENCES: enumerate all
// 2^k assignments of the referenced non-nil-ordered ("free") indices; if filterLogic is
// false under EVERY assignment, the binding is unsatisfiable. Sound and COMPLETE for any
// logic incl. NOT/OR (no monotonicity assumption). Unreferenced filters are ignored (they
// cannot change the result and must not count against the bound). Bounded: above the
// referenced-free-index cap it gives up (return false -> the query decides; the
// nil-ordered pushdown guard still prevents the walker crash).
func recordSetBindingDeterministicallyFalse(filterLogic string, resolved []resolvedRecordSetFilter, engine *formula.Engine) bool {
	matches := make([]bool, len(resolved)) // nil-ordered (and unreferenced) leaves stay false
	// Indexes filterLogic actually references; an empty filterLogic AND-joins every
	// filter, so all are referenced. Unreferenced filters cannot change the result and
	// must not count against the satisfiability bound.
	var referenced []int
	if strings.TrimSpace(filterLogic) == "" {
		referenced = make([]int, len(resolved))
		for i := range referenced {
			referenced[i] = i
		}
	} else {
		refs, err := recordSetReferencedFilterIndexes(filterLogic, len(resolved))
		if err != nil {
			return false // malformed filterLogic -> don't short-circuit; the query decides
		}
		referenced = refs
	}
	freeIdx := make([]int, 0, len(resolved))
	anyNullOrdered := false
	for _, idx := range referenced {
		if resolved[idx].value == nil && recordSetIsOrderedOp(resolved[idx].op) {
			anyNullOrdered = true // pinned false (matches[idx] stays false)
		} else {
			freeIdx = append(freeIdx, idx)
		}
	}
	if !anyNullOrdered {
		return false // no guaranteed-false leaf referenced -> nothing to short-circuit
	}
	if len(freeIdx) > recordSetMaxSatisfiabilityFreeIndices {
		return false // too many referenced free filters to enumerate exactly; let the query decide
	}
	for mask := 0; mask < (1 << len(freeIdx)); mask++ {
		for j, idx := range freeIdx {
			matches[idx] = mask&(1<<uint(j)) != 0
		}
		logicExpr, err := recordSetFilterLogicExpression(filterLogic, matches)
		if err != nil {
			return false
		}
		v, err := engine.Evaluate(logicExpr, &formula.Context{})
		if err != nil {
			return false
		}
		if b, ok := v.(bool); !ok || b {
			return false // satisfiable under this assignment -> not deterministically false
		}
	}
	return true // false under every assignment of the free leaves
}

// recordSetIsOrderedOp reports whether op is an ordered comparison (<, <=, >, >=).
func recordSetIsOrderedOp(op string) bool {
	switch op {
	case "lessOrEqual", "greaterOrEqual", "lessThan", "greaterThan":
		return true
	}
	return false
}

func recordSetFilterPushdownExpr(filter resolvedRecordSetFilter) string {
	field := strings.TrimSpace(filter.filter.Field)
	// #1747: an ordered comparison against nil would render `field OP NULL`, which the
	// formula->SQL walker rejects (its null branch only handles == / !=) -> HTTP 500. It
	// is also a deterministic no-match (compareRecordSetOrdered no-matches a nil operand),
	// so drop it from the pushdown: recordSetPushdownFilter skips an empty leaf, which only
	// widens the conjunction (over-fetch the in-memory recheck corrects). equality-vs-nil
	// is unaffected (it still renders, and the walker supports IS NULL / IS NOT NULL).
	if filter.value == nil && recordSetIsOrderedOp(filter.op) {
		return ""
	}
	switch filter.op {
	case "equals":
		return fmt.Sprintf("%s == %s", field, recordSetFormulaLiteralForField(filter.value, filter.field))
	case "notEquals":
		return fmt.Sprintf("%s != %s", field, recordSetFormulaLiteralForField(filter.value, filter.field))
	case "lessOrEqual":
		return fmt.Sprintf("%s <= %s", field, recordSetFormulaLiteralForField(filter.value, filter.field))
	case "greaterOrEqual":
		return fmt.Sprintf("%s >= %s", field, recordSetFormulaLiteralForField(filter.value, filter.field))
	case "lessThan":
		return fmt.Sprintf("%s < %s", field, recordSetFormulaLiteralForField(filter.value, filter.field))
	case "greaterThan":
		return fmt.Sprintf("%s > %s", field, recordSetFormulaLiteralForField(filter.value, filter.field))
	case "in", "notIn":
		values, ok := recordSetValueSlice(filter.value)
		if !ok || len(values) == 0 {
			return ""
		}
		literals := make([]string, 0, len(values))
		for _, value := range values {
			literals = append(literals, recordSetFormulaLiteralForField(value, filter.field))
		}
		if filter.op == "in" {
			return fmt.Sprintf("%s in [%s]", field, strings.Join(literals, ","))
		}
		return fmt.Sprintf("!(%s in [%s])", field, strings.Join(literals, ","))
	default:
		return ""
	}
}

func recordSetFilterLogicExpression(filterLogic string, matches []bool) (string, error) {
	logic := strings.TrimSpace(filterLogic)
	if logic == "" {
		if len(matches) == 0 {
			return "true", nil
		}
		parts := make([]string, len(matches))
		for i, matched := range matches {
			parts[i] = strconv.FormatBool(matched)
		}
		return strings.Join(parts, " && "), nil
	}
	var b strings.Builder
	for i := 0; i < len(logic); {
		ch := rune(logic[i])
		if unicode.IsSpace(ch) {
			b.WriteByte(' ')
			i++
			continue
		}
		if unicode.IsDigit(ch) {
			start := i
			for i < len(logic) && unicode.IsDigit(rune(logic[i])) {
				i++
			}
			n, err := strconv.Atoi(logic[start:i])
			if err != nil || n < 1 || n > len(matches) {
				return "", fmt.Errorf("filter reference %q is out of range", logic[start:i])
			}
			b.WriteString(strconv.FormatBool(matches[n-1]))
			continue
		}
		if isRecordSetLogicIdentStart(ch) {
			start := i
			for i < len(logic) && isRecordSetLogicIdentPart(rune(logic[i])) {
				i++
			}
			switch strings.ToUpper(logic[start:i]) {
			case "AND":
				b.WriteString("&&")
			case "OR":
				b.WriteString("||")
			case "NOT":
				b.WriteString("!")
			default:
				return "", fmt.Errorf("unsupported token %q", logic[start:i])
			}
			continue
		}
		switch logic[i] {
		case '(':
			b.WriteByte('(')
		case ')':
			b.WriteByte(')')
		default:
			return "", fmt.Errorf("unsupported character %q", logic[i])
		}
		i++
	}
	return b.String(), nil
}

func isRecordSetLogicIdentStart(ch rune) bool {
	return (ch >= 'A' && ch <= 'Z') || (ch >= 'a' && ch <= 'z')
}

func isRecordSetLogicIdentPart(ch rune) bool {
	return isRecordSetLogicIdentStart(ch)
}

func compareRecordSetValues(left interface{}, op string, right interface{}, field *models.FieldMetadata) (bool, error) {
	switch op {
	case "equals":
		return recordSetValuesEqual(left, right, field), nil
	case "notEquals":
		return !recordSetValuesEqual(left, right, field), nil
	case "in", "notIn":
		values, ok := recordSetValueSlice(right)
		if !ok {
			return false, fmt.Errorf("%s operator requires an array literal value", op)
		}
		found := false
		for _, value := range values {
			if recordSetValuesEqual(left, value, field) {
				found = true
				break
			}
		}
		if op == "notIn" {
			return !found, nil
		}
		return found, nil
	case "lessOrEqual", "greaterOrEqual", "lessThan", "greaterThan":
		cmp, ok := compareRecordSetOrdered(left, right)
		if !ok {
			return false, nil
		}
		switch op {
		case "lessOrEqual":
			return cmp <= 0, nil
		case "greaterOrEqual":
			return cmp >= 0, nil
		case "lessThan":
			return cmp < 0, nil
		case "greaterThan":
			return cmp > 0, nil
		}
	}
	return false, fmt.Errorf("unsupported operator %q", op)
}

func recordSetValuesEqual(left interface{}, right interface{}, field *models.FieldMetadata) bool {
	if left == nil || right == nil {
		return left == nil && right == nil
	}
	switch recordSetFieldType(field) {
	case constants.FieldTypeDate:
		if lt, lok := recordSetTime(left); lok {
			if rt, rok := recordSetTime(right); rok {
				return sameRecordSetCalendarDate(lt, rt)
			}
		}
	case constants.FieldTypeDatetime:
		if lt, lok := recordSetTime(left); lok {
			if rt, rok := recordSetTime(right); rok {
				return lt.Equal(rt)
			}
		}
	}
	if lf, lok := recordSetFloat(left); lok {
		if rf, rok := recordSetFloat(right); rok {
			return lf == rf
		}
	}
	if lt, lok := recordSetTime(left); lok {
		if rt, rok := recordSetTime(right); rok {
			return lt.Equal(rt)
		}
	}
	return valuesEqualForValidation(left, right)
}

func recordSetFieldType(field *models.FieldMetadata) constants.SchemaFieldType {
	if field == nil {
		return ""
	}
	return constants.NormalizeFieldType(string(field.Type))
}

func sameRecordSetCalendarDate(left, right time.Time) bool {
	leftYear, leftMonth, leftDay := left.Date()
	rightYear, rightMonth, rightDay := right.Date()
	return leftYear == rightYear && leftMonth == rightMonth && leftDay == rightDay
}

func compareRecordSetOrdered(left interface{}, right interface{}) (int, bool) {
	if left == nil || right == nil {
		return 0, false
	}
	if lf, lok := recordSetFloat(left); lok {
		if rf, rok := recordSetFloat(right); rok {
			switch {
			case lf < rf:
				return -1, true
			case lf > rf:
				return 1, true
			default:
				return 0, true
			}
		}
	}
	if lt, lok := recordSetTime(left); lok {
		if rt, rok := recordSetTime(right); rok {
			switch {
			case lt.Before(rt):
				return -1, true
			case lt.After(rt):
				return 1, true
			default:
				return 0, true
			}
		}
	}
	return strings.Compare(fmt.Sprint(left), fmt.Sprint(right)), true
}

func recordSetFloat(value interface{}) (float64, bool) {
	switch v := value.(type) {
	case float64:
		return v, true
	case float32:
		return float64(v), true
	case int:
		return float64(v), true
	case int64:
		return float64(v), true
	case int32:
		return float64(v), true
	case json.Number:
		f, err := v.Float64()
		return f, err == nil
	case string:
		f, err := strconv.ParseFloat(strings.TrimSpace(v), 64)
		return f, err == nil
	default:
		return 0, false
	}
}

func recordSetTime(value interface{}) (time.Time, bool) {
	switch v := value.(type) {
	case time.Time:
		return v, true
	case string:
		s := strings.TrimSpace(v)
		if s == "" {
			return time.Time{}, false
		}
		for _, layout := range []string{
			time.RFC3339Nano,
			time.RFC3339,
			"2006-01-02 15:04:05.999999999",
			"2006-01-02 15:04:05.999999",
			"2006-01-02 15:04:05",
			"2006-01-02",
		} {
			if parsed, err := time.Parse(layout, s); err == nil {
				return parsed, true
			}
		}
	}
	return time.Time{}, false
}

func recordSetValueSlice(value interface{}) ([]interface{}, bool) {
	switch v := value.(type) {
	case []interface{}:
		return v, true
	case []string:
		out := make([]interface{}, len(v))
		for i := range v {
			out[i] = v[i]
		}
		return out, true
	}
	rv := reflect.ValueOf(value)
	if !rv.IsValid() || rv.Kind() != reflect.Slice {
		return nil, false
	}
	out := make([]interface{}, rv.Len())
	for i := 0; i < rv.Len(); i++ {
		out[i] = rv.Index(i).Interface()
	}
	return out, true
}

func recordSetFormulaLiteralForField(value interface{}, field *models.FieldMetadata) string {
	if value == nil {
		return "nil"
	}
	switch v := value.(type) {
	case string:
		return `"` + strings.ReplaceAll(strings.ReplaceAll(v, `\`, `\\`), `"`, `\"`) + `"`
	case bool:
		return strconv.FormatBool(v)
	case time.Time:
		if recordSetFieldType(field) == constants.FieldTypeDate {
			return `"` + v.Format("2006-01-02") + `"`
		}
		return `"` + formatRecordSetDatetimeLiteral(v) + `"`
	default:
		return fmt.Sprint(v)
	}
}

func formatRecordSetDatetimeLiteral(value time.Time) string {
	formatted := value.Format("2006-01-02 15:04:05.999999")
	if strings.Contains(formatted, ".") {
		formatted = strings.TrimRight(formatted, "0")
		formatted = strings.TrimRight(formatted, ".")
	}
	return formatted
}

func normalizeRecordSetOperator(raw string) (string, bool) {
	key := strings.ToLower(strings.ReplaceAll(strings.ReplaceAll(strings.ReplaceAll(strings.TrimSpace(raw), "_", ""), "-", ""), " ", ""))
	switch key {
	case "equals", "equal", "eq", "=", "==":
		return "equals", true
	case "notequals", "notequal", "ne", "neq", "!=", "<>":
		return "notEquals", true
	case "lessorequal", "lte", "<=":
		return "lessOrEqual", true
	case "greaterorequal", "gte", ">=":
		return "greaterOrEqual", true
	case "lessthan", "lt", "<":
		return "lessThan", true
	case "greaterthan", "gt", ">":
		return "greaterThan", true
	case "in":
		return "in", true
	case "notin":
		return "notIn", true
	default:
		return "", false
	}
}

func normalizeRecordSetValueSource(raw string) (string, bool) {
	key := strings.ToLower(strings.ReplaceAll(strings.ReplaceAll(strings.ReplaceAll(strings.TrimSpace(raw), "_", ""), "-", ""), " ", ""))
	switch key {
	case "literal":
		return "literal", true
	case "recordpath":
		return "recordPath", true
	case "userpath":
		return "userPath", true
	default:
		return "", false
	}
}

func splitRecordSetPath(path string) []string {
	cleaned := strings.TrimSpace(path)
	cleaned = strings.TrimPrefix(cleaned, "$Record.")
	cleaned = strings.TrimPrefix(cleaned, "record.")
	raw := strings.Split(cleaned, ".")
	parts := make([]string, 0, len(raw))
	for _, part := range raw {
		part = strings.TrimSpace(part)
		if part != "" {
			parts = append(parts, part)
		}
	}
	return parts
}

func nestedRecordSetValue(record models.SObject, key string) (models.SObject, bool) {
	if record == nil {
		return nil, false
	}
	value, ok := record[key]
	if !ok {
		return nil, false
	}
	switch v := value.(type) {
	case models.SObject:
		return v, true
	case map[string]interface{}:
		return models.SObject(v), true
	default:
		return nil, false
	}
}

func resolveUserPathValue(userContext map[string]interface{}, path string) interface{} {
	if userContext == nil {
		return nil
	}
	cleaned := strings.TrimPrefix(strings.TrimSpace(path), "$")
	switch {
	case strings.HasPrefix(cleaned, "User."):
		cleaned = "__User." + strings.TrimPrefix(cleaned, "User.")
	case strings.HasPrefix(cleaned, "Profile."):
		cleaned = "__Profile." + strings.TrimPrefix(cleaned, "Profile.")
	case strings.HasPrefix(cleaned, "UserRole."):
		cleaned = "__UserRole." + strings.TrimPrefix(cleaned, "UserRole.")
	}
	parts := splitRecordSetPath(cleaned)
	var current interface{} = userContext
	for _, part := range parts {
		switch m := current.(type) {
		case map[string]interface{}:
			if v, ok := m[part]; ok {
				current = v
				continue
			}
			found := false
			for k, v := range m {
				if strings.EqualFold(k, part) {
					current = v
					found = true
					break
				}
			}
			if !found {
				return nil
			}
		case models.SObject:
			if v, ok := m[part]; ok {
				current = v
				continue
			}
			return nil
		default:
			return nil
		}
	}
	return current
}

func recordTypeValidationPseudoSchema() *models.ObjectMetadata {
	return &models.ObjectMetadata{
		APIName: "RecordType",
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID},
			{APIName: constants.FieldName},
			{APIName: "DeveloperName"},
		},
	}
}

func recordTypePseudoField(schema *models.ObjectMetadata, fieldName string) bool {
	return schema != nil && strings.EqualFold(schema.APIName, "RecordType") &&
		(strings.EqualFold(fieldName, constants.FieldID) ||
			strings.EqualFold(fieldName, constants.FieldName) ||
			strings.EqualFold(fieldName, "DeveloperName"))
}