package formula

import (
	"encoding/json"
	"errors"
	"fmt"
	"math"
	"math/big"
	"regexp"
	"strconv"
	"strings"
	"sync"
	"time"

	"github.com/expr-lang/expr"
	"github.com/expr-lang/expr/ast"
	"github.com/expr-lang/expr/parser"

	"github.com/nexuscrm/kernel/pkg/expression"
	"github.com/nexuscrm/kernel/pkg/formula/spec"
	"github.com/nexuscrm/kernel/pkg/passwordhash"
	"github.com/nexuscrm/kernel/pkg/schemautil"
	"github.com/nexuscrm/kernel/pkg/utils"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// ErrRelationFetch is returned (wrapped via %w) by Evaluate when the caller-
// supplied Context.Fetcher returns a non-nil error for any relation referenced
// by the expression. Callers that need to distinguish a transient relation-
// fetch failure (metadata-cache miss / DB blip) from a genuine evaluation
// failure (parse / function-call error) MUST gate on errors.Is(err,
// formula.ErrRelationFetch). The semantic difference is load-bearing for
// callers like records.computeFormulaFields, which must NOT corrupt a stored
// formula value with nil on a transient fetch error — see issue #2057.
var ErrRelationFetch = errors.New("formula: relation fetcher failed")

const normalizedFormulaCacheMaxEntries = 4096

// Pre-compiled regexes used in normalizeFormulaExpression (P1-B: avoid recompiling per eval).
var (
	blockCommentPattern  = regexp.MustCompile(`(?s)/\*.*?\*/`)
	typedRelationPattern = regexp.MustCompile(`:([A-Za-z_][A-Za-z0-9_]*)\.`)
	dollarVarPattern     = regexp.MustCompile(`\$([A-Za-z_])`)
	isChangedPattern     = regexp.MustCompile(`ISCHANGED\(\s*(?:__Record\.)?([A-Za-z_][A-Za-z0-9_]*)\s*\)`)
	priorValuePattern    = regexp.MustCompile(`PRIORVALUE\(\s*(?:__Record\.)?([A-Za-z_][A-Za-z0-9_]*)\s*\)`)
	// Capture dotted identifier paths (e.g. `Parent__r.Flag__c`) so the
	// whole path is wrapped in TRUTHY(...). A bare-identifier capture would
	// leave the dotted prefix stranded — `Parent__r.Field__c == true` became
	// `Parent__r.TRUTHY(Field__c)` instead of `TRUTHY(Parent__r.Field__c)`.
	eqTruePattern     = regexp.MustCompile(`([A-Za-z_][A-Za-z0-9_]*(?:\.[A-Za-z_][A-Za-z0-9_]*)*)\s*==\s*true\b`)
	eqFalsePattern    = regexp.MustCompile(`([A-Za-z_][A-Za-z0-9_]*(?:\.[A-Za-z_][A-Za-z0-9_]*)*)\s*==\s*false\b`)
	funcEqTruePattern = regexp.MustCompile(`\)\s*==\s*true\b`)
	isNewPattern      = regexp.MustCompile(`ISNEW\(\s*\)`)
	isClonePattern    = regexp.MustCompile(`ISCLONE\(\s*\)`)
	// Rewrites TODAY()+30 → DATE_ADD(TODAY(), 30), TODAY()-7 → DATE_ADD(TODAY(), -7)
	todayArithPattern = regexp.MustCompile(`(TODAY|NOW)\(\s*\)\s*([+-])\s*(\d+)`)
	// Strips Salesforce merge-field braces: {!$Record.Foo} → $Record.Foo
	// Must run BEFORE dollarVarPattern so the $ rewrite sees a clean identifier.
	mergeFieldPattern = regexp.MustCompile(`\{!([^}]+)\}`)

	// Matches __Record.<Ident>. — the shape of a cross-object relationship
	// reference after normalizeFormulaExpression has rewritten `$Record` to
	// `__Record`. The trailing dot ensures we only capture identifiers that
	// are actually used as a relationship step (there must be at least one
	// field access after), not bare field references like __Record.Name.
	recordRelationPattern = regexp.MustCompile(`__Record\.([A-Za-z_][A-Za-z0-9_]*)\.`)

	// bareRelationPattern matches a bare identifier used as a parent-record
	// reference prefix (e.g. "Parent__r" in TEXT(Parent__r.Region__c),
	// "RecordType" in RecordType.Name, "Lookup__r" in Lookup__r.Name) —
	// the Salesforce implicit-record-context shorthand where a lookup
	// relationship name is used without $Record.
	// Group 1: boundary char (non-identifier, non-dot, non-quote) or string
	// start. Group 2: the relationship identifier (underscore allowed so
	// custom __r names like Parent__r are matched). Group 3: first char of
	// the dotted segment (alpha/underscore excludes bare function calls).
	// Identifiers starting with "__" are filtered by extractBareRelations.
	bareRelationPattern = regexp.MustCompile(`(^|[^._A-Za-z0-9"'])([A-Za-z][A-Za-z0-9_]*)\.([A-Za-z_])`)

	keywordPatterns = map[*regexp.Regexp]string{
		regexp.MustCompile(`(?i)\bnull\b`):  "nil",
		regexp.MustCompile(`(?i)\btrue\b`):  "true",
		regexp.MustCompile(`(?i)\bfalse\b`): "false",
	}

	// Infix boolean operator rewrites. Salesforce Flow metadata + our filter
	// builder (executor_helpers.buildFilterFromConditionsImpl) emit uppercase
	// `AND` / `OR` as infix boolean operators. expr-lang's parser only
	// understands lowercase `and` / `or` (or `&&` / `||`), so we rewrite.
	// Go's RE2 doesn't support lookaheads, so we capture the following
	// non-paren character and reinject it via a substitution template.
	// Regex issue #1124.
	andInfixPattern = regexp.MustCompile(`(?i)\bAND\b(\s*)([^(\s])`)
	orInfixPattern  = regexp.MustCompile(`(?i)\bOR\b(\s*)([^(\s])`)
	// Infix `AND(` / `OR(` — the Salesforce Flow importer emits forms like
	// `X AND(Y OR Z)` where `AND` is infix and `(` is the start of a
	// subexpression. Disambiguate from the call form `AND(a, b, c)` by
	// requiring a preceding expression-terminator character (identifier-char,
	// digit, closing paren/bracket, quote). Call form at the start of the
	// expression or after `(`, `,`, `&&`, `||`, `!` leaves `AND(` / `OR(`
	// untouched for the walker's variadic handling. Issue #1124.
	andInfixParenPattern = regexp.MustCompile(`([)\w'"\]])(\s*)\bAND\s*\(`)
	orInfixParenPattern  = regexp.MustCompile(`([)\w'"\]])(\s*)\bOR\s*\(`)
	// `IS NULL` / `IS NOT NULL` from SF-compat filter-builder output. After
	// keywordPatterns runs `NULL` has already been lowercased to `nil`.
	// Rewrite to expr-lang's native null comparison so the SQL walker can
	// emit `IS NULL` / `IS NOT NULL`.
	isNotNullPattern = regexp.MustCompile(`(?i)\bIS\s+NOT\s+nil\b`)
	isNullPattern    = regexp.MustCompile(`(?i)\bIS\s+nil\b`)

	// Salesforce-compat: `<identPath> = ""` / `<identPath> <> ""` on a text
	// field is TRUE for NULL values (Salesforce has no NULL/"" distinction
	// for text). expr-lang evaluates `nil == ""` as false, so the naive
	// rewrite resolves blank-check branches incorrectly and the formula
	// silently returns nil — which executeRecordUpdate then persists as a
	// NULL column. Rewrite the pattern to ISBLANK, which handles nil and
	// empty string uniformly. Issue #1680. Pattern matches identifiers and
	// dotted paths (`__Record.Parent__r.Field__c`); complex LHS expressions
	// like function calls fall through to literal expr-lang semantics.
	emptyStringEqPattern = regexp.MustCompile(`([A-Za-z_][\w.]*)\s*==\s*""`)
	emptyStringNePattern = regexp.MustCompile(`([A-Za-z_][\w.]*)\s*!=\s*""`)

	functionPatternsMu sync.RWMutex
	functionPatterns   map[string]*regexp.Regexp
)

// runtimeOnlyNoNormalizeFuncs lists catalog functions that are deliberately NOT
// case-normalized by normalizeFormulaExpression. They are runtime-internal: the
// normalizer (or an upstream emitter) produces them already in canonical
// upper-case, and a user never types them in raw Salesforce input. Normalizing
// them would mask a real authoring mistake, so their expr-lang registration is
// intentionally case-sensitive — TestValidateSyntax_RejectsCaseVariants pins
// that lowercase spellings fail closed.
//
// The reason each is excluded from the normalize set:
//   - TRUTHY            — synthesized by the `== true` rewrite, always upper-case.
//   - ISNULLX/ISBLANKX/ISCHANGEDX — synthesized lowering targets for ISNULL/
//     ISBLANK/ISCHANGED, always upper-case.
//   - SAFE*/STRICTSAFE* — synthesized comparison lowering targets, always
//     upper-case.
//   - DATE_ADD          — synthesized by the TODAY()+N / NOW()+N arithmetic
//     rewrite, always upper-case (todayArithPattern emits `DATE_ADD(`).
//   - BCRYPT            — a registered runtime function with no Salesforce
//     case-insensitive authoring contract; spelled exactly.
//
// Every name here MUST be a catalog function (the reconciliation guard asserts
// the set is a subset of the catalog) so this stays a pure exclusion, never a
// second place that invents a function name.
var runtimeOnlyNoNormalizeFuncs = map[string]bool{
	"TRUTHY":                  true,
	"ISNULLX":                 true,
	"ISBLANKX":                true,
	"ISCHANGEDX":              true,
	"__NCRM_FIELDUNCHANGED__": true,
	"SAFELT":                  true,
	"SAFEGT":                  true,
	"SAFELTE":                 true,
	"SAFEGTE":                 true,
	"STRICTSAFELT":            true,
	"STRICTSAFEGT":            true,
	"STRICTSAFELTE":           true,
	"STRICTSAFEGTE":           true,
	"DATE_ADD":                true,
	"BCRYPT":                  true,
}

// normalizeOnlySpecialForms lists the case-normalized names that are NOT catalog
// functions: Salesforce context predicates the normalizer rewrites into sentinel
// variables (ISCHANGED/PRIORVALUE/ISNEW/ISCLONE) immediately after the
// case-canonicalization pass. They are author-facing spellings that must be
// upper-cased so the dedicated rewrite regexes (isChangedPattern, priorValuePattern,
// isNewPattern, isClonePattern) match, but they never reach the runtime function
// registry — so they have no catalog entry by design. The reconciliation guard
// asserts this set is disjoint from the catalog.
var normalizeOnlySpecialForms = []string{
	"ISCHANGED", "PRIORVALUE", "ISNEW", "ISCLONE",
}

// normalizedFunctionNames returns the canonical upper-case set of function names
// the frozen-path normalizer must case-canonicalize before the (case-sensitive)
// expr-lang parser sees them. It derives the set from the single source of truth
// — the spec catalog — minus the runtime-only functions that are never authored
// in raw input (runtimeOnlyNoNormalizeFuncs), plus the non-catalog special forms
// (normalizeOnlySpecialForms). The catalog is the only place function names are
// declared; this function reads it and applies two explicit, reasoned, and
// guard-tested deltas rather than maintaining a parallel hardcoded list.
func normalizedFunctionNames(registry *spec.Registry) []string {
	if registry == nil {
		registry = spec.DefaultRegistry()
	}
	names := make([]string, 0, registry.Len())
	for _, name := range registry.Names() {
		if runtimeOnlyNoNormalizeFuncs[name] {
			continue
		}
		names = append(names, name)
	}
	names = append(names, normalizeOnlySpecialForms...)
	return names
}

func buildFunctionPatterns(registry *spec.Registry) map[string]*regexp.Regexp {
	names := normalizedFunctionNames(registry)
	patterns := make(map[string]*regexp.Regexp, len(names))
	for _, fn := range names {
		patterns[fn] = regexp.MustCompile(`(?i)\b` + regexp.QuoteMeta(fn) + `\s*\(`)
	}
	return patterns
}

func init() {
	rebuildGlobalFunctionPatterns()
}

// rebuildGlobalFunctionPatterns rebuilds the package-level functionPatterns
// from spec.DefaultRegistry() plus all functions in the currently active
// runtime bundles. This ensures bundle-backed functions (e.g. IMAGE) that
// are no longer in the default catalog still get case-normalized by
// normalizeFormulaExpression(), which is used by package-level callers like
// ToSQLWithMapper.
func rebuildGlobalFunctionPatterns() {
	patterns := buildFunctionPatterns(spec.DefaultRegistry())
	bundles := activeRuntimeFunctionBundles()
	for _, bundle := range bundles {
		for _, fn := range bundle.Functions {
			name := strings.ToUpper(fn.Name)
			if _, exists := patterns[name]; !exists {
				patterns[name] = regexp.MustCompile(`(?i)\b` + regexp.QuoteMeta(name) + `\s*\(`)
			}
		}
	}
	functionPatternsMu.Lock()
	functionPatterns = patterns
	functionPatternsMu.Unlock()
}

// getGlobalFunctionPatterns returns a snapshot of the current package-level
// function patterns for use by callers that normalize raw formula text outside
// of an Engine (e.g. ToSQLWithMapper).
func getGlobalFunctionPatterns() map[string]*regexp.Regexp {
	functionPatternsMu.RLock()
	defer functionPatternsMu.RUnlock()
	return functionPatterns
}

// Context holds the evaluation context for formulas
type Context struct {
	Record    map[string]interface{}                                                                   `json:"record"`
	Prior     map[string]interface{}                                                                   `json:"prior"`
	User      map[string]interface{}                                                                   `json:"user"`
	Env       map[string]interface{}                                                                   `json:"env"`
	Variables map[string]interface{}                                                                   `json:"variables"` // Flow Variables
	Fields    map[string]interface{}                                                                   `json:"-"`         // Catch-all for unmapped fields
	Extra     map[string]json.RawMessage                                                               `json:"-"`         // For custom unmarshaling
	Fetcher   func(record map[string]interface{}, relationName string) (map[string]interface{}, error) `json:"-"`
	IsVisible func(fieldName string) (bool, error)                                                     `json:"-"` // Optional FLS check
	Schema    *models.ObjectMetadata                                                                   `json:"-"` // Optional schema for field-aware functions such as INCLUDES.
	// UserSession is the typed user session associated with this formula evaluation.
	// Populated from FlowRuntimeContext.User by buildFlowFormulaContext when a user
	// is present. May be nil for system-initiated or test evaluations.
	// The User (map) field above is populated from UserSession.ToMap() for formula
	// expression resolution ($User.<field> references). This field provides the
	// strongly-typed surface for T22c's generic $User/$Profile resolver.
	UserSession *models.UserSession `json:"-"`
	// ForceNotNew overrides the ISNEW() sentinel to false regardless of Prior.
	// Set this on non-record-trigger (e.g. Scheduled) entry gates where Prior==nil
	// does NOT mean the record is being created — it means there is no prior
	// snapshot. Without this override the engine would compute _is_new_=true
	// (Prior==nil) and ISNEW()→true fires every scanned row.
	ForceNotNew bool `json:"-"`
	// TreatBlankNumericAsZero applies the Salesforce
	// formulaTreatBlanksAs=BlankAsZero default to this evaluation: after record
	// preparation, blank numeric operands (schemautil.IsBlankAsZeroNumericType
	// over the field's EffectiveFieldType) read as 0 instead of nil.
	//
	// This is a FORMULA-FIELD semantic, not an evaluator-wide one — the vendor
	// scopes blank handling to formula fields (the attribute lives on the
	// formula field's metadata; rules and flows have no such option). Only
	// formula-FIELD computation callers (records/formula_compute) set this;
	// approval criteria, flow expressions, field defaults and sharing rules
	// keep nil propagation. Validation rules never reach this flag either —
	// their evaluation has its OWN pre-existing nil-numeric coercion in the
	// metadata layer (coerceForFormulaEval / ensureFormulaFieldsPresent in
	// nexuscrm-metadata/validation_formula_rewrite.go), which this flag neither
	// replaces nor extends. The SQL projection applies the same formula-field
	// rule in the view compiler (toFormulaDomainSQL), which keeps the two
	// evaluation paths agreeing per the #3908 parity requirement.
	//
	// Scope note: the flag governs the root record and Prior. Cross-object
	// operands get the rule from the relation FETCHER the caller installs
	// (records/formula_compute's fetcher zeroes fetched parents and
	// synthesizes vendor-shaped null-parent skeletons); a caller that sets
	// this flag with a different fetcher gets root-only semantics.
	TreatBlankNumericAsZero bool `json:"-"`
}

// UnmarshalJSON implements custom JSON unmarshaling to capture extra fields
func (c *Context) UnmarshalJSON(data []byte) error {
	var temp map[string]interface{}
	if err := json.Unmarshal(data, &temp); err != nil {
		return err
	}

	if c.Fields == nil {
		c.Fields = make(map[string]interface{})
	}

	for key, value := range temp {
		switch key {
		case "record":
			if recordMap, ok := value.(map[string]interface{}); ok {
				c.Record = recordMap
			}
		case "prior":
			if priorMap, ok := value.(map[string]interface{}); ok {
				c.Prior = priorMap
			}
		case "user":
			if userMap, ok := value.(map[string]interface{}); ok {
				c.User = userMap
			}
		case "env":
			if envMap, ok := value.(map[string]interface{}); ok {
				c.Env = envMap
			}
		case "variables":
			if varMap, ok := value.(map[string]interface{}); ok {
				c.Variables = varMap
			}
		default:
			c.Fields[key] = value
		}
	}
	return nil
}

// Engine is the formula evaluation engine backed by expression engine
type Engine struct {
	exprEngine          *expression.Engine
	functionRegistry    *spec.Registry
	functionPatterns    map[string]*regexp.Regexp
	registeredFunctions map[string]func(params ...interface{}) (interface{}, error)
	skipRuntimeBundles  bool
	normalizedMu        sync.RWMutex
	normalizedCache     map[string]string
}

// EngineOption configures a formula engine.
type EngineOption func(*Engine)

// WithFunctionRegistry installs a snapshot of registry as the engine-local
// function catalog used by syntax and schema validation. Evaluation still
// requires matching function implementations to be registered on the engine.
func WithFunctionRegistry(registry *spec.Registry) EngineOption {
	return func(e *Engine) {
		if registry == nil {
			e.functionRegistry = spec.DefaultRegistry().Clone()
			return
		}
		e.functionRegistry = registry.Clone()
	}
}

// WithoutRuntimeFunctionBundles creates an engine that does not auto-load the
// process runtime bundle snapshot. Explicit bundle-registration paths use this
// to avoid registering the same active bundle twice.
func WithoutRuntimeFunctionBundles() EngineOption {
	return func(e *Engine) {
		e.skipRuntimeBundles = true
	}
}

// NewEngine creates a new formula engine.
func NewEngine(options ...EngineOption) *Engine {
	engine := &Engine{
		functionRegistry:    spec.DefaultRegistry().Clone(),
		registeredFunctions: make(map[string]func(params ...interface{}) (interface{}, error)),
		normalizedCache:     make(map[string]string),
	}
	engine.exprEngine = expression.NewEngine(expression.WithFunctionRegistry(engine.functionRegistry))
	for _, option := range options {
		if option != nil {
			option(engine)
		}
	}
	if engine.functionRegistry == nil {
		engine.functionRegistry = spec.DefaultRegistry().Clone()
	}
	engine.rebuildExpressionEngine()
	if len(options) == 0 {
		engine.functionPatterns = getGlobalFunctionPatterns()
	} else {
		engine.functionPatterns = buildFunctionPatterns(engine.functionRegistry)
	}
	engine.registerBuiltinFunctions()
	if !engine.skipRuntimeBundles {
		// NewEngine cannot return an error. RegisterFunctionBundles compiles into
		// a cloned registry before mutating the engine, so a conflicting runtime
		// bundle leaves this engine on its pre-bundle fail-closed path.
		if registry, err := ActiveRuntimeExecutableFunctionRegistry(); err == nil && registry.hasEvaluators() {
			_ = engine.registerExecutableFunctionRegistry(registry)
		}
	}
	return engine
}

// ToSQL converts an expression string to a SQL WHERE clause and arguments
func ToSQL(expr string) (string, []interface{}, error) {
	return ToSQLWithMapper(expr, nil)
}

// ToSQLWithMapper converts an expression string to a SQL WHERE clause with identifier mapping
func ToSQLWithMapper(expr string, mapper func(string) string) (string, []interface{}, error) {
	opts, err := runtimeExpressionSQLOptions(mapper)
	if err != nil {
		return "", nil, err
	}
	return expression.ToSQLWithOptions(normalizeFormulaExpression(expr), opts)
}

type SQLOptions = expression.SQLOptions

func ToSQLWithOptions(expr string, opts SQLOptions) (string, []interface{}, error) {
	runtimeOpts, err := runtimeExpressionSQLOptions(opts.Mapper)
	if err != nil {
		return "", nil, err
	}
	runtimeOpts.AllowMemberPaths = opts.AllowMemberPaths
	runtimeOpts.BoundIdentifiers = cloneExpressionSQLFragments(opts.BoundIdentifiers)
	if opts.FunctionLowerer != nil {
		runtimeLowerer := runtimeOpts.FunctionLowerer
		callerLowerer := opts.FunctionLowerer
		runtimeOpts.FunctionLowerer = func(ctx expression.SQLFunctionCallContext) (expression.SQLFragment, bool, error) {
			fragment, handled, err := callerLowerer(ctx)
			if handled || err != nil {
				return fragment, handled, err
			}
			if runtimeLowerer == nil {
				return expression.SQLFragment{}, false, nil
			}
			return runtimeLowerer(ctx)
		}
	}
	return expression.ToSQLWithOptions(normalizeFormulaExpression(expr), runtimeOpts)
}

func runtimeExpressionSQLOptions(mapper func(string) string) (expression.SQLOptions, error) {
	registry, err := ActiveRuntimeExecutableFunctionRegistry()
	if err != nil {
		return expression.SQLOptions{}, err
	}
	return expression.SQLOptions{
		Mapper:          mapper,
		FunctionLowerer: executableExpressionSQLFunctionLowerer(registry),
	}, nil
}

func executableExpressionSQLFunctionLowerer(registry *ExecutableFunctionRegistry) expression.SQLFunctionLowerer {
	var lowerer expression.SQLFunctionLowerer
	lowerer = func(ctx expression.SQLFunctionCallContext) (expression.SQLFragment, bool, error) {
		if fragment, handled, err := lowerInternalQueryPredicateExpressionSQL(ctx); handled || err != nil {
			return fragment, handled, err
		}
		if registry == nil {
			return expression.SQLFragment{}, false, nil
		}
		definition, ok := registry.LookupFunctionDefinition(ctx.Name)
		if !ok {
			return expression.SQLFragment{}, false, nil
		}
		if err := definition.Spec().Validate(len(ctx.Args)); err != nil {
			return expression.SQLFragment{}, true, err
		}
		// Dispatch on the registered SQL-lowering capability flag; the by-name
		// check is retained transitionally as a fallback and is removed
		// in PR-B once an L1 bundle sets the flag.
		if definition.SQLLowering == SQLLoweringMultipicklistContainsAll || strings.EqualFold(ctx.Name, "INCLUDES") {
			fragment, err := lowerINCLUDESExpressionSQL(ctx)
			return fragment, true, err
		}
		switch strings.TrimSpace(definition.Evaluator.Kind) {
		case FormulaExprV1EvaluatorKind:
			fragment, err := lowerFormulaExprV1ExpressionSQL(definition, ctx, lowerer)
			return fragment, true, err
		default:
			return expression.SQLFragment{}, true, fmt.Errorf("%s SQL lowering: unsupported evaluator kind %q", ctx.Name, definition.Evaluator.Kind)
		}
	}
	return lowerer
}

func lowerINCLUDESExpressionSQL(ctx expression.SQLFunctionCallContext) (expression.SQLFragment, error) {
	if len(ctx.Args) != 2 {
		return expression.SQLFragment{}, fmt.Errorf("INCLUDES requires 2 arguments")
	}
	field, err := ctx.CompileArg(ctx.Args[0])
	if err != nil {
		return expression.SQLFragment{}, fmt.Errorf("INCLUDES arg 1: %w", err)
	}
	if tokens, ok := includesStringLiteralRHSTokens(ctx.Args[1]); ok {
		return lowerINCLUDESMultiTokenSQL(field, tokens), nil
	}
	value, err := ctx.CompileArg(ctx.Args[1])
	if err != nil {
		return expression.SQLFragment{}, fmt.Errorf("INCLUDES arg 2: %w", err)
	}
	args := append([]interface{}(nil), field.Args...)
	args = append(args, value.Args...)
	return expression.SQLFragment{
		SQL:  utils.MultiPicklistJSONContainsSQL(field.SQL, value.SQL, true),
		Args: args,
	}, nil
}

func includesStringLiteralRHSTokens(arg ast.Node) ([]string, bool) {
	rhs, ok := arg.(*ast.StringNode)
	if !ok || !strings.Contains(rhs.Value, ";") {
		return nil, false
	}
	return engineDedupeTrimmed(strings.Split(rhs.Value, ";")), true
}

func lowerINCLUDESMultiTokenSQL(field expression.SQLFragment, tokens []string) expression.SQLFragment {
	tokenSQLs := make([]string, 0, len(tokens))
	args := make([]interface{}, 0, len(tokens)*(len(field.Args)+1))
	for _, token := range tokens {
		tokenSQLs = append(tokenSQLs, "?")
		args = append(args, field.Args...)
		args = append(args, token)
	}
	return expression.SQLFragment{
		SQL:  utils.MultiPicklistJSONContainsAllSQL(field.SQL, tokenSQLs),
		Args: args,
	}
}

func cloneExpressionSQLFragments(in map[string]expression.SQLFragment) map[string]expression.SQLFragment {
	if len(in) == 0 {
		return nil
	}
	out := make(map[string]expression.SQLFragment, len(in))
	for key, fragment := range in {
		out[key] = expression.SQLFragment{
			SQL:  fragment.SQL,
			Args: append([]interface{}(nil), fragment.Args...),
		}
	}
	return out
}

func lowerFormulaExprV1ExpressionSQL(definition FunctionDefinition, ctx expression.SQLFunctionCallContext, lowerer expression.SQLFunctionLowerer) (expression.SQLFragment, error) {
	body := strings.TrimSpace(definition.Evaluator.Body)
	if body == "" {
		return expression.SQLFragment{}, fmt.Errorf("%s body is required", FormulaExprV1EvaluatorKind)
	}
	if len(definition.Evaluator.Params) > len(ctx.Args) {
		return expression.SQLFragment{}, fmt.Errorf("%s params length %d exceeds argument count %d", FormulaExprV1EvaluatorKind, len(definition.Evaluator.Params), len(ctx.Args))
	}

	bound := make(map[string]expression.SQLFragment, len(definition.Evaluator.Params))
	for i, rawParam := range definition.Evaluator.Params {
		param := strings.TrimSpace(rawParam)
		if param == "" {
			return expression.SQLFragment{}, fmt.Errorf("%s has empty param name at index %d", FormulaExprV1EvaluatorKind, i)
		}
		fragment, err := ctx.CompileArg(ctx.Args[i])
		if err != nil {
			return expression.SQLFragment{}, fmt.Errorf("arg %d: %w", i+1, err)
		}
		bound[param] = expression.SQLFragment{
			SQL:  fragment.SQL,
			Args: append([]interface{}(nil), fragment.Args...),
		}
	}

	sql, args, err := expression.ToSQLWithOptions(body, expression.SQLOptions{
		FunctionLowerer:  lowerer,
		BoundIdentifiers: bound,
	})
	if err != nil {
		return expression.SQLFragment{}, fmt.Errorf("%s SQL lowering: %w", strings.TrimSpace(definition.Name), err)
	}
	return expression.SQLFragment{SQL: sql, Args: args}, nil
}

// Validate validates a formula expression syntax
func (e *Engine) Validate(expression string, env map[string]interface{}) error {
	if err := rejectInternalLoweringArtifacts(expression); err != nil {
		return err
	}
	return e.exprEngine.Validate(e.normalizeFormulaExpression(expression), env)
}

// ValidateSyntax checks that a formula expression is syntactically well-formed
// AND only references functions the runtime evaluator can actually resolve
// AND has well-formed shape (no method-call callees) AND has correct arity at
// every call site.
//
// Why this exists: Engine.Validate compiles in expr-lang's strict mode, where
// every identifier must be present in the supplied env. Metadata-time linters
// (bootstrap setup verify, schema formula-save preflight) do not have a real
// record / user / repo to build env from, and the runtime evaluator
// (Engine.Evaluate) deliberately constructs env per save from
// ctx.Record + ctx.User + ctx.Variables + ctx.Fetcher-augmented relations.
// Calling Validate with an empty env therefore rejects every formula that
// references a sibling field, a $Record.<Relation>.<Field> path, a bare
// relationship path, or an engine builtin — i.e., almost every shipped
// frozen formula. This produces false-positive InvalidFormula violations
// at startup and breaks public-core greenfield readiness.
//
// ValidateSyntax has two tiers:
//
//  1. Parse: compile with expr.AllowUndefinedVariables() so identifier
//     references compile through (the runtime evaluator resolves them at
//     Evaluate time) while genuine parse-level errors — unmatched parens,
//     unterminated strings, dangling operators — still surface.
//
//  2. Static AST walker (pkg/formula/runtime_functions.go) — fail-closed
//     by default. Rejects:
//     - call sites whose callee identifier is not in
//     runtimeFunctionSignatures (VLOOKUP / REGEX / any unimplemented
//     Salesforce function — IANTHEREAL P1 on PR #268),
//     - call sites whose callee shape is not an *ast.IdentifierNode
//     (method-call forms `Parent__r.Name()` / `Foo.BAR(Amount)` — the
//     IANTHEREAL R9 reflect.Value.Call panic class),
//     - call sites whose argument count is outside the declared
//     [Min, Max] bounds in runtimeFunctionSignatures (`IF(Amount)`,
//     `BLANKVALUE()`, `NOT()` — IANTHEREAL Round-11 P1 replacement
//     for the previous DryEvaluate-against-stubs arity gate),
//     - any AST node type the walker does not explicitly handle.
//
// Round-11 fail-closed retraction (IANTHEREAL 2026-05-07T09:50Z, PR #268):
// previously this entry-point was paired with DryEvaluate against synthetic
// stubs to catch reflect-boundary panics and arity errors at runtime. That
// approach was strictly worse than the static gate because (1) stubs could
// not reproduce typed runtime data — `Amount > 0`, `Amount + Total`, and
// `Email & Phone` all raised type errors against stub maps even though they
// evaluate cleanly at record-write time, producing false-positive
// InvalidFormula violations on healthy frozen formulas, and (2) stub
// coverage required hard-coding application vocabulary (ParentId /
// RelatedId / LookupId) into pkg/formula, violating the L0/L1
// layering contract documented in CLAUDE.md. Static checks cover the same
// defect class without either pathology.
//
// The expression is run through the same normalizeFormulaExpression
// pipeline as Evaluate so Salesforce-flavored syntax (`{!$Record.Foo}`,
// `=`/`<>` operators, infix `AND`/`OR`, `$Profile`-style globals,
// `TODAY()+N` → `DATE_ADD(TODAY(), N)` rewrite) lints consistently with
// what the runtime sees. That includes accepting Salesforce-standard function
// spellings the runtime canonicalizes (`abs(...)`, `if(...)`, `today()+30`)
// while still failing closed on unregistered names and non-normalized custom
// extensions (`bcrypt(...)`, `safelt(...)`).
// internalLoweringPrefixes lists the token prefixes that the
// normalizeFormulaExpression lowering step emits as output.  They are valid
// inside a post-normalize expression but must never appear in author-written
// (raw, pre-normalize) formula text — exposing them as a saveable public
// contract would turn NexusCRM-internal implementation details into a
// non-Salesforce formula surface (public-core-boundary violation).
//
// All tokens currently emitted by normalizeFormulaExpression:
//   - __NCRM_FIELDUNCHANGED__ (ISCHANGED lowering, engine.go:1192)
//   - __ncrm_multipicklist_fields (ISCHANGED lowering env key, engine.go:1192)
//
// The prefixes "__NCRM_" and "__ncrm_" cover both and any future sibling
// lowering tokens that follow the same convention.
var internalLoweringPrefixes = []string{"__NCRM_", "__ncrm_"}

// rejectInternalLoweringArtifacts returns an error when the raw (pre-normalize)
// expression contains any internal lowering artifact as an author-written
// identifier.  It must be called BEFORE normalizeFormulaExpression so that
// author-written occurrences are rejected while still allowing the normalizer's
// own lowering output to exist in the post-normalize form consumed by the runtime.
func rejectInternalLoweringArtifacts(raw string) error {
	// Scan with string-literal contents blanked: a reserved prefix appearing as
	// literal DATA (e.g. Name = '__NCRM_FIELDUNCHANGED__') is not an author-written
	// identifier and must not be rejected.  Author-written identifiers live outside
	// quotes and are still caught.
	scan := stripStringLiterals(raw)
	for _, prefix := range internalLoweringPrefixes {
		// Case-sensitive: the lowering always emits the exact casing above.
		if strings.Contains(scan, prefix) {
			// Find the first offending token for a precise error message.
			idx := strings.Index(scan, prefix)
			end := idx + len(prefix)
			for end < len(scan) && (scan[end] == '_' || (scan[end] >= 'A' && scan[end] <= 'Z') || (scan[end] >= 'a' && scan[end] <= 'z') || (scan[end] >= '0' && scan[end] <= '9')) {
				end++
			}
			token := scan[idx:end]
			return fmt.Errorf("formula references reserved internal identifier %q; not allowed in authored formulas", token)
		}
	}
	return nil
}

func (e *Engine) ValidateSyntax(expression string) error {
	if err := rejectInternalLoweringArtifacts(expression); err != nil {
		return err
	}
	normalized := e.normalizeFormulaExpression(expression)
	if _, err := expr.Compile(normalized, expr.AllowUndefinedVariables()); err != nil {
		return err
	}
	return e.validateRuntimeFunctionCalls(normalized)
}

// Evaluate evaluates a formula expression with the given context
func (e *Engine) Evaluate(formulaExpression string, ctx *Context) (interface{}, error) {
	if ctx == nil {
		ctx = &Context{}
	}
	normalizedExpr := e.normalizeFormulaExpression(formulaExpression)
	record := cloneFormulaRecordMap(ctx.Record)
	prior := cloneFormulaRecordMap(ctx.Prior)
	if ctx.Schema != nil {
		// Capture which keys the caller actually supplied BEFORE preparation
		// seeds absent schema fields as nil: blank-as-zero must distinguish
		// "loaded and blank" (coerce to 0) from "never loaded" (a virtual
		// active formula or a not-yet-computed sibling — keep nil so the
		// evaluation fails closed instead of fabricating a plausible 0).
		var rawRecordKeys, rawPriorKeys map[string]struct{}
		if ctx.TreatBlankNumericAsZero {
			rawRecordKeys = presentKeySet(record)
			rawPriorKeys = presentKeySet(prior)
		}
		PrepareRecordForEvaluation(record, ctx.Schema)
		PrepareRecordForEvaluation(prior, ctx.Schema)
		if ctx.TreatBlankNumericAsZero {
			ZeroBlankNumericOperands(record, ctx.Schema, rawRecordKeys)
			ZeroBlankNumericOperands(prior, ctx.Schema, rawPriorKeys)
		}
	}
	// Flatten context for generic expression engine
	env := make(map[string]interface{})
	multiPicklistFields := buildFormulaMultiPicklistFieldEnv(ctx.Schema, ctx)

	// 0. Cross-object lazy fetch. When the caller supplies ctx.Fetcher, scan the
	// normalized expression for relation references and populate the related
	// records via the Fetcher before flattening. Two ref forms are handled:
	//   a) __Record.X.Y — Salesforce $Record. shorthand (e.g. $Record.Parent__r.Name)
	//   b) bare X.Y    — Salesforce implicit-record-context shorthand (e.g.
	//                    Parent__r.Region__c on a child record, RecordType.Name).
	// Both go through the same fetcher and augmentRecordWithRelations so a
	// relation already populated by form (a) is not re-fetched by form (b).
	// Issue #2092 — bare X.Y was previously not augmented, causing "unknown
	// name Parent__r" errors for valid cross-object formula fields.
	if ctx.Fetcher != nil && record != nil {
		relationScan := formulaRelationScanForEvaluation(normalizedExpr, ctx)
		if relations := relationScan.recordRelations; len(relations) > 0 {
			augmented, err := augmentRecordWithRelations(record, relations, ctx.Fetcher)
			if err != nil {
				// Transient fetcher failure (metadata-cache miss / DB blip).
				// Surface as ErrRelationFetch so consumers can distinguish
				// from a genuine evaluation failure and avoid corrupting a
				// stored formula value with nil on a fleeting error. Issue
				// #2057.
				return nil, fmt.Errorf("%w: %v", ErrRelationFetch, err)
			}
			record = augmented
		}
		// Step 0b: also augment bare X.Y relation refs. augmentRecordWithRelations
		// is idempotent: relations already in `record` (from step 0a above or
		// supplied inline by the caller) are silently skipped.
		if bareRels := relationScan.bareRelations; len(bareRels) > 0 {
			augmented, err := augmentRecordWithRelations(record, bareRels, ctx.Fetcher)
			if err != nil {
				return nil, fmt.Errorf("%w: %v", ErrRelationFetch, err)
			}
			record = augmented
		}
		// Step 0c: augment multi-hop chains (depth ≥ 2, e.g. $Record.A__r.B__r.Name).
		// Single-hop refs are already populated by step 0a; this step walks each
		// additional hop in the chain so deeply nested relationship fields resolve
		// without a separate fetcher call per expression evaluation.
		if chains := relationScan.recordRelationChains; len(chains) > 0 {
			augmented, err := augmentRecordWithRelationChain(record, chains, ctx.Fetcher)
			if err != nil {
				return nil, err
			}
			record = augmented
		}
	}

	// 1. Add top-level objects
	if record != nil {
		// Apply FLS filtering if IsVisible is provided
		var recordMap map[string]interface{}
		if ctx.IsVisible != nil {
			recordMap = make(map[string]interface{})
			for k, v := range record {
				visible, err := ctx.IsVisible(k)
				if err != nil {
					return nil, err
				}
				if visible {
					recordMap[k] = v
				}
			}
		} else {
			recordMap = record
		}

		env["record"] = recordMap
		// Also flatten record fields into top level for convenience (e.g. Amount instead of record.Amount)
		// This matches previous behavior where plain identifiers looked in record
		for k, v := range recordMap {
			env[k] = v
		}
	}
	if prior != nil {
		env["prior"] = prior
	}
	if ctx.User != nil {
		env["user"] = ctx.User
		// Flatten user context entries (e.g. $Profile, $UserRole) into top-level scope
		// so Salesforce merge fields like $Profile.Name resolve via member access
		for k, v := range ctx.User {
			env[k] = v
		}
	}
	if ctx.Env != nil {
		env["env"] = ctx.Env
	}
	if ctx.Variables != nil {
		// Inject variables into top-level scope (simplest for access)
		// Or should they be namespaced? Usually Flow Variables are just identifiers.
		// Let's namespace them under 'var' AND put at top level if no conflict?
		// For safety, let's put them in 'var' namespace AND top level, prioritizing Record fields.
		env["var"] = ctx.Variables
		for k, v := range ctx.Variables {
			// Only inject if not already present (Record fields take precedence or vice-versa?
			// Usually Variables override Record fields in Flow Context.
			env[k] = v
		}
	}

	// 1b. Ensure env["__Record"] references the (possibly augmented) record so
	// any cross-object relations lazy-loaded above are visible through the
	// normalized `__Record.<Relation>.<Field>` access path. Overrides the
	// original Record that buildFlowFormulaContext routed via Variables.
	if record != nil {
		env["__Record"] = record
	}

	// 2. Add extra fields (overrides)
	if ctx.Fields != nil {
		for k, v := range ctx.Fields {
			// Apply FLS to extra fields too if they are considered fields
			if ctx.IsVisible != nil {
				visible, err := ctx.IsVisible(k)
				if err != nil {
					return nil, err
				}
				if !visible {
					continue
				}
			}
			env[k] = v
		}
	}

	// 3. Inject prior values with _prior_ prefix for ISCHANGED / PRIORVALUE support.
	if prior != nil {
		env["_has_prior_"] = true
		for k, v := range prior {
			env["_prior_"+k] = v
		}
	} else {
		// On insert there is no prior row. _prior_<field> seeding has three contracts:
		//   compile: every schema field needs a _prior_<field> key, else ISCHANGED/
		//     PRIORVALUE (which lower to a bare _prior_<field> identifier) fail to
		//     compile → fail-closed over-reject on create (#1780).
		//   value, record-create (ForceNotNew=false): Salesforce returns the field's
		//     CURRENT value for PRIORVALUE on create, not null (SF help
		//     customize_functions_priorvalue: "on the creation of a new object,
		//     PRIORVALUE returns the current value of the new object"; #1785 IANTHEREAL
		//     P1) — but ONLY for FLS-visible fields, so PRIORVALUE never becomes an FLS
		//     side channel for hidden current values (#1785 Codex HIGH 2).
		//   value, scheduled / non-record-trigger no-prior (ForceNotNew=true): stays
		//     nil — those gates rely on PRIORVALUE==nil (#1785 Codex HIGH 1).
		// ISCHANGED stays false on create regardless: it is guarded by _has_prior_=false
		// and short-circuits before the value compare, so seeding current values here
		// does NOT flip ISCHANGED to true.
		env["_has_prior_"] = false
		recordCreate := !ctx.ForceNotNew
		if record != nil {
			for k := range record {
				if !recordCreate {
					env["_prior_"+k] = nil
					continue
				}
				visible := true
				if ctx.IsVisible != nil {
					var err error
					visible, err = ctx.IsVisible(k)
					if err != nil {
						return nil, err
					}
				}
				if visible {
					env["_prior_"+k] = record[k]
				} else {
					env["_prior_"+k] = nil
				}
			}
		}
	}

	// 4. Inject ISNEW / ISCLONE sentinels.
	// ISNEW: true when Prior is nil (record is being created), unless ForceNotNew
	// is set (e.g. Scheduled entry gate where Prior==nil means no snapshot, not create).
	// ISCLONE: always false for now (clone operation not yet implemented).
	if ctx.ForceNotNew {
		env["_is_new_"] = false
	} else {
		env["_is_new_"] = ctx.Prior == nil
	}
	env["_is_clone_"] = false
	env[expression.MultiPicklistFieldEnvKey] = multiPicklistFields

	// 5. Evaluate. Apply the Salesforce empty-string semantics rewrite
	// post-normalize, scoped to the Evaluate path (see helper godoc).
	//
	// MP-aware ISCHANGED is handled inside normalizeFormulaExpression: the
	// isChangedPattern lowering now emits __NCRM_FIELDUNCHANGED__("fieldName", ...)
	// which detects multi-picklist fields at eval time via the __ncrm_multipicklist_fields
	// env entry. No pre-normalize string rewrite needed. Issue #1684 / PR #2047 Bug 2.
	finalExpr := applySalesforceEmptyStringSemantics(normalizedExpr)
	if ctx.TreatBlankNumericAsZero {
		// SQL-parity division (#4152): with blank numerics coerced to 0, a
		// blank divisor becomes a zero divisor, and the native float operator
		// would yield +Inf/NaN — a finite-looking wrong branch selector in
		// enclosing IF/comparisons. The SQL projection renders that division
		// as NULL, so this mode lowers `/` to a nil-on-zero division instead.
		return e.exprEngine.EvaluateWithSQLNullDivision(finalExpr, env)
	}
	return e.exprEngine.Evaluate(finalExpr, env)
}

// PrepareRecordForEvaluation coerces storage/runtime record values into the
// shared formula-evaluation contract for the supplied schema.
func PrepareRecordForEvaluation(record map[string]interface{}, schema *models.ObjectMetadata) {
	if record == nil || schema == nil {
		return
	}
	for _, field := range schema.Fields {
		val, ok := record[field.APIName]
		if !ok {
			// Field absent from the payload (e.g. create with partial fields):
			// seed nil so a formula referencing it compiles and resolves to nil
			// instead of raising "unknown name <field>". This matters for the
			// ISCHANGED/PRIORVALUE lowering, which emits a BARE current-value
			// identifier (isChangedPattern, this file) that expr-lang resolves
			// against the flattened top-level env at COMPILE time; an absent key
			// makes the whole gate/formula fail to compile → evaluateFilterFormulaGate
			// fails closed → record-triggered flow over-rejects on create (#1780).
			// Only schema-declared fields are seeded; a genuine typo (not in
			// schema.Fields) still errors → fails closed (consistent with #637).
			// Symmetric to the #1733 Round-13 empty-$Record__Old seed (prior dim).
			record[field.APIName] = nil
			continue
		}

		ft := schemautil.EffectiveFieldType(field)
		if val == nil {
			continue
		}

		if strVal, isStr := val.(string); isStr && strVal == "" {
			switch ft {
			case constants.FieldTypeDate, constants.FieldTypeDatetime,
				constants.FieldTypeDouble, constants.FieldTypeCurrency, constants.FieldTypePercent,
				constants.FieldTypeBoolean, constants.FieldTypeReference:
				record[field.APIName] = nil
				continue
			}
		}

		if ft == constants.FieldTypeBoolean {
			switch v := val.(type) {
			case int64:
				record[field.APIName] = v != 0
			case int:
				record[field.APIName] = v != 0
			case float64:
				record[field.APIName] = v != 0
			}
			continue
		}

		switch ft {
		case constants.FieldTypeDouble, constants.FieldTypeCurrency, constants.FieldTypePercent:
			switch typed := val.(type) {
			case schemautil.ExactDecimal:
				if f, err := strconv.ParseFloat(typed.String(), 64); err == nil {
					record[field.APIName] = f
				}
			case string:
				strVal := typed
				if strVal == "" {
					continue
				}
				if f, err := strconv.ParseFloat(strVal, 64); err == nil {
					record[field.APIName] = f
				}
			}
		}

		if ft == constants.FieldTypePercent {
			switch v := record[field.APIName].(type) {
			case float64:
				record[field.APIName] = v / 100.0
			case int:
				record[field.APIName] = float64(v) / 100.0
			case int64:
				record[field.APIName] = float64(v) / 100.0
			}
		}

	}
}

// isComputedElsewhereField reports whether a field's value is produced by a
// mechanism other than direct storage of a caller-written value: formula
// (active or frozen), metadata-owned read projection, or compound derivation.
// For these, absence from a write payload means "not loaded", never "blank".
func isComputedElsewhereField(field *models.FieldMetadata) bool {
	if field == nil {
		return false
	}
	if field.Formula != nil && *field.Formula != "" {
		return true
	}
	if field.ProjectionConfig != nil {
		return true
	}
	return field.IsCompound
}

// presentKeySet snapshots which field keys a record map carries. Used to
// distinguish caller-supplied values from PrepareRecordForEvaluation's
// absent-key nil seeding when applying blank-as-zero.
func presentKeySet(record map[string]interface{}) map[string]struct{} {
	if record == nil {
		return nil
	}
	out := make(map[string]struct{}, len(record))
	for k := range record {
		out[k] = struct{}{}
	}
	return out
}

// ZeroBlankNumericOperands rewrites nil numeric field values to 0 after
// PrepareRecordForEvaluation, implementing Context.TreatBlankNumericAsZero
// (the vendor's BlankAsZero default, #4152). It only touches schema-declared
// fields whose EffectiveFieldType is in the shared blank-as-zero numeric
// bucket, so booleans (tinyint-backed) and dates keep nil.
//
// rawPresent is the key set the record carried BEFORE preparation seeded
// absent fields as nil (nil rawPresent means "treat every key as
// caller-supplied", the right reading for a fully-loaded storage row or a
// synthesized null-parent skeleton). It exists for one distinction:
//
//   - a COMPUTED-ELSEWHERE field that the caller never supplied is not
//     "blank" — a virtual active formula (whose value lives only in the SQL
//     projection), a sibling frozen formula that has not been computed yet, a
//     metadata-owned read projection (ProjectionConfig), or a compound field
//     derived from its members. Coercing those to 0 would fabricate a
//     plausible-looking number out of a value that was simply never loaded,
//     so they keep nil and the evaluation fails closed (stored NULL), exactly
//     as before this change.
//   - every other blank read — a caller-supplied nil, or an absent plain
//     column (a create payload that omitted an optional numeric) — is a
//     genuine blank and reads as 0.
//
// Percent fields become float64(0), which is already in the 0–1 fraction
// domain PrepareRecordForEvaluation establishes for percent operands (0/100
// and 0 coincide), so running after the percent division is correct for
// blanks and required for non-blanks.
func ZeroBlankNumericOperands(record map[string]interface{}, schema *models.ObjectMetadata, rawPresent map[string]struct{}) {
	if record == nil || schema == nil {
		return
	}
	for i := range schema.Fields {
		field := &schema.Fields[i]
		if !schemautil.IsBlankAsZeroNumericType(string(schemautil.EffectiveFieldType(*field))) {
			continue
		}
		v, ok := record[field.APIName]
		if !ok || v != nil {
			continue
		}
		if isComputedElsewhereField(field) && rawPresent != nil {
			if _, suppliedByCaller := rawPresent[field.APIName]; !suppliedByCaller {
				continue
			}
		}
		record[field.APIName] = float64(0)
	}
}

func cloneFormulaRecordMap(record map[string]interface{}) map[string]interface{} {
	if record == nil {
		return nil
	}
	out := make(map[string]interface{}, len(record))
	for k, v := range record {
		out[k] = v
	}
	return out
}

func buildFormulaMultiPicklistFieldEnv(schema *models.ObjectMetadata, ctx *Context) map[string]interface{} {
	if schema == nil || len(schema.Fields) == 0 {
		return nil
	}
	out := make(map[string]interface{})
	for i := range schema.Fields {
		field := &schema.Fields[i]
		if constants.NormalizeFieldType(string(field.Type)) != constants.FieldTypeMultiPicklist {
			continue
		}
		// options may be nil/empty (no picklist values configured yet). We still
		// register the field key so that __NCRM_FIELDUNCHANGED__ can detect the
		// field as multi-picklist for set-aware ISCHANGED. INCLUDES degrades
		// gracefully when options is empty (returns the raw field value).
		options := field.Options.Values()
		for _, key := range formulaMultiPicklistFieldKeys(
			field.APIName,
			formulaBareIdentifierResolvesToRecord(ctx, field.APIName),
			formulaNamespaceResolvesToRecord(ctx, "record"),
			formulaNamespaceResolvesToPrior(ctx, "prior"),
		) {
			out[key] = options
		}
	}
	if len(out) == 0 {
		return nil
	}
	return out
}

func formulaMultiPicklistFieldKeys(apiName string, includeBare, includeRecord, includePrior bool) []string {
	if apiName == "" {
		return nil
	}
	keys := []string{
		"__Record." + apiName,
		"__Record__Old." + apiName,
		"__Record__Prior." + apiName,
		"_prior_" + apiName,
	}
	if includeRecord {
		keys = append(keys, "record."+apiName)
	}
	if includePrior {
		keys = append(keys, "prior."+apiName)
	}
	if includeBare {
		keys = append(keys, apiName)
	}
	return keys
}

func formulaBareIdentifierResolvesToRecord(ctx *Context, apiName string) bool {
	if ctx == nil || ctx.Record == nil || apiName == "" {
		return false
	}
	if _, ok := ctx.Record[apiName]; !ok {
		return false
	}
	return !formulaNameIsShadowed(ctx, apiName)
}

func formulaNamespaceResolvesToRecord(ctx *Context, root string) bool {
	return ctx != nil && ctx.Record != nil && root != "" && !formulaNameIsShadowed(ctx, root)
}

func formulaNamespaceResolvesToPrior(ctx *Context, root string) bool {
	return ctx != nil && ctx.Prior != nil && root != "" && !formulaNameIsShadowed(ctx, root)
}

func formulaNameIsShadowed(ctx *Context, name string) bool {
	if ctx == nil || name == "" {
		return false
	}
	if ctx.User != nil {
		if _, shadowed := ctx.User[name]; shadowed {
			return true
		}
	}
	if ctx.Variables != nil {
		if _, shadowed := ctx.Variables[name]; shadowed {
			return true
		}
	}
	if ctx.Fields != nil {
		if _, shadowed := ctx.Fields[name]; shadowed {
			return true
		}
	}
	return false
}

// RegisterFunction registers a formula evaluator and mirrors it into the
// expression engine. Invalid names, nil evaluators, and duplicate evaluator
// registrations fail instead of silently replacing active runtime behavior.
func (e *Engine) RegisterFunction(name string, fn func(params ...interface{}) (interface{}, error)) error {
	if strings.TrimSpace(name) == "" {
		return fmt.Errorf("formula: function name is required")
	}
	if fn == nil {
		return fmt.Errorf("formula: function %q has nil evaluator", name)
	}
	if e.registeredFunctions == nil {
		e.registeredFunctions = make(map[string]func(params ...interface{}) (interface{}, error))
	}
	if _, exists := e.registeredFunctions[name]; exists {
		return fmt.Errorf("formula: duplicate evaluator registration for function %q", name)
	}
	e.registeredFunctions[name] = fn
	if e.exprEngine != nil {
		if err := e.exprEngine.RegisterFunction(name, fn); err != nil {
			delete(e.registeredFunctions, name)
			return err
		}
	}
	return nil
}

func (e *Engine) rebuildExpressionEngine() {
	e.exprEngine = expression.NewEngine(expression.WithFunctionRegistry(e.functionRegistry))
	for name, fn := range e.registeredFunctions {
		if err := e.exprEngine.RegisterFunction(name, fn); err != nil {
			panic(fmt.Sprintf("formula: rebuilding expression engine: %v", err))
		}
	}
}

func (e *Engine) checkArity(name string, argc int) error {
	if e == nil || e.functionRegistry == nil {
		return spec.DefaultRegistry().CheckArity(name, argc)
	}
	return e.functionRegistry.CheckArity(name, argc)
}

func (e *Engine) mustRegisterFunction(name string, fn func(params ...interface{}) (interface{}, error)) {
	if err := e.RegisterFunction(name, fn); err != nil {
		panic(fmt.Sprintf("formula: register builtin function %q: %v", name, err))
	}
}

// registerBuiltinFunctions registers formula-specific functions
func (e *Engine) registerBuiltinFunctions() {
	// BCRYPT for password hashing
	e.mustRegisterFunction("BCRYPT", func(args ...interface{}) (interface{}, error) {
		if err := e.checkArity("BCRYPT", len(args)); err != nil {
			return nil, err
		}
		password, ok := args[0].(string)
		if !ok {
			return nil, fmt.Errorf("BCRYPT argument must be string")
		}
		// Hash the password using auth package
		return passwordhash.Hash(password)
	})
	e.mustRegisterFunction("ISNULLX", func(args ...interface{}) (interface{}, error) {
		if err := e.checkArity("ISNULLX", len(args)); err != nil {
			return nil, err
		}
		wasNull, ok := args[1].(bool)
		if !ok {
			wasNull = false
		}
		return wasNull || args[0] == nil, nil
	})
	e.mustRegisterFunction("ISBLANKX", func(args ...interface{}) (interface{}, error) {
		if err := e.checkArity("ISBLANKX", len(args)); err != nil {
			return nil, err
		}
		wasNull, ok := args[1].(bool)
		if !ok {
			wasNull = false
		}
		if wasNull || args[0] == nil {
			return true, nil
		}
		if s, ok := args[0].(string); ok {
			return s == "", nil
		}
		return false, nil
	})
	e.mustRegisterFunction("ISCHANGEDX", func(args ...interface{}) (interface{}, error) {
		if err := e.checkArity("ISCHANGEDX", len(args)); err != nil {
			return nil, err
		}
		hasPrior, ok := args[0].(bool)
		if !ok || !hasPrior {
			return false, nil
		}
		currentWasNull, _ := args[3].(bool)
		priorWasNull, _ := args[4].(bool)
		if currentWasNull != priorWasNull {
			return true, nil
		}
		if currentWasNull && priorWasNull {
			return false, nil
		}
		return fmt.Sprintf("%v", args[1]) != fmt.Sprintf("%v", args[2]), nil
	})
	// __NCRM_FIELDUNCHANGED__(fieldName, cur, prior, mpFields) — eval-time helper
	// synthesized by the ISCHANGED normalizer lowering. Returns true when the field
	// has NOT changed (so the negation in the caller produces ISCHANGED semantics).
	//
	// If fieldName appears as a key in the mpFields map (injected from the
	// __ncrm_multipicklist_fields env entry), the comparison is set-aware: semicolon-
	// separated values "a;b" and "b;a" are equal (Salesforce ISCHANGED ignores order).
	// For all other field types, falls back to Sprintf string compare — identical to
	// the previous inline `$1 != _prior_$1` behavior.
	//
	// This runs at eval-time on the parsed AST, AFTER comment-strip in normalize —
	// making it immune to the comment-injection bypass class (e.g.
	// ISCHANGED/*x*/(MPField)) that afflicted the pre-normalize string-rewrite
	// approach. Issue #1684 / PR #2047 Bug 2.
	e.mustRegisterFunction("__NCRM_FIELDUNCHANGED__", func(args ...interface{}) (interface{}, error) {
		if err := e.checkArity("__NCRM_FIELDUNCHANGED__", len(args)); err != nil {
			return nil, err
		}
		fieldName, _ := args[0].(string)
		cur := args[1]
		prior := args[2]
		mpFields := args[3]

		// Detect multi-picklist by checking if fieldName (bare API name, e.g. "Tags__c")
		// is registered in the MP-fields map under any of its known key forms.
		isMP := false
		if fieldName != "" && mpFields != nil {
			switch m := mpFields.(type) {
			case map[string]interface{}:
				_, isMP = m[fieldName]
				if !isMP {
					_, isMP = m["__Record."+fieldName]
				}
			case map[string][]string:
				_, isMP = m[fieldName]
				if !isMP {
					_, isMP = m["__Record."+fieldName]
				}
			}
		}

		if isMP {
			// All-shapes extractor: handle every serialization that records /
			// flow-input wiring can produce (string semicolon-join, JSON-array
			// string, []string, []interface{}, []byte).  Cannot call
			// automation.extractMultiPicklistItems (import cycle); mirror its
			// logic here as engineExtractMPItems.
			return engineMPSetEqual(cur, prior), nil
		}
		// Non-MP: raw Sprintf compare (same as the old inline `$1 != _prior_$1`).
		return fmt.Sprintf("%v", cur) == fmt.Sprintf("%v", prior), nil
	})
	e.mustRegisterFunction("SAFELT", func(args ...interface{}) (interface{}, error) {
		return e.safeCompareFormulaArgs(args, func(cmp int) bool { return cmp < 0 }, "SAFELT")
	})
	e.mustRegisterFunction("SAFEGT", func(args ...interface{}) (interface{}, error) {
		return e.safeCompareFormulaArgs(args, func(cmp int) bool { return cmp > 0 }, "SAFEGT")
	})
	e.mustRegisterFunction("SAFELTE", func(args ...interface{}) (interface{}, error) {
		return e.safeCompareFormulaArgs(args, func(cmp int) bool { return cmp <= 0 }, "SAFELTE")
	})
	e.mustRegisterFunction("SAFEGTE", func(args ...interface{}) (interface{}, error) {
		return e.safeCompareFormulaArgs(args, func(cmp int) bool { return cmp >= 0 }, "SAFEGTE")
	})
	e.mustRegisterFunction("STRICTSAFELT", func(args ...interface{}) (interface{}, error) {
		return e.strictCompareFormulaArgs(args, func(cmp int) bool { return cmp < 0 }, "STRICTSAFELT")
	})
	e.mustRegisterFunction("STRICTSAFEGT", func(args ...interface{}) (interface{}, error) {
		return e.strictCompareFormulaArgs(args, func(cmp int) bool { return cmp > 0 }, "STRICTSAFEGT")
	})
	e.mustRegisterFunction("STRICTSAFELTE", func(args ...interface{}) (interface{}, error) {
		return e.strictCompareFormulaArgs(args, func(cmp int) bool { return cmp <= 0 }, "STRICTSAFELTE")
	})
	e.mustRegisterFunction("STRICTSAFEGTE", func(args ...interface{}) (interface{}, error) {
		return e.strictCompareFormulaArgs(args, func(cmp int) bool { return cmp >= 0 }, "STRICTSAFEGTE")
	})
	e.mustRegisterFunction("DATE", func(args ...interface{}) (interface{}, error) {
		if err := e.checkArity("DATE", len(args)); err != nil {
			return nil, err
		}
		if args[0] == nil || args[1] == nil || args[2] == nil {
			return nil, nil
		}
		year, err := formulaIntArg("DATE", "year", args[0])
		if err != nil {
			return nil, err
		}
		month, err := formulaIntArg("DATE", "month", args[1])
		if err != nil {
			return nil, err
		}
		day, err := formulaIntArg("DATE", "day", args[2])
		if err != nil {
			return nil, err
		}
		if year < 1700 || year > 4000 {
			return nil, fmt.Errorf("DATE year %d is outside the valid Salesforce date range (1700–4000)", year)
		}
		d := time.Date(year, time.Month(month), day, 0, 0, 0, 0, time.UTC)
		if d.Year() != year || int(d.Month()) != month || d.Day() != day {
			return nil, fmt.Errorf("DATE(%d, %d, %d) is not a valid calendar date", year, month, day)
		}
		return d.Format("2006-01-02"), nil
	})
	e.mustRegisterFunction("DATETIMEFROMEPOCHMILLIS", func(args ...interface{}) (interface{}, error) {
		if err := e.checkArity("DATETIMEFROMEPOCHMILLIS", len(args)); err != nil {
			return nil, err
		}
		if args[0] == nil {
			return nil, nil
		}
		milliseconds, err := formulaEpochMillisArg("DATETIMEFROMEPOCHMILLIS", "value", args[0])
		if err != nil {
			return nil, err
		}
		return time.UnixMilli(milliseconds).UTC(), nil
	})

}

// formulaIntArg converts a formula argument to int, accepting int, int64, and float64.
// float64 is the common runtime type when arguments originate from JSON-decoded numbers.
func formulaIntArg(fn, argName string, v interface{}) (int, error) {
	switch n := v.(type) {
	case int:
		return n, nil
	case int64:
		return int(n), nil
	case float64:
		return int(n), nil
	default:
		return 0, fmt.Errorf("%s %s must be a number, got %T", fn, argName, v)
	}
}

// formulaEpochMillisArg accepts integral numeric formula values and strict
// base-10 strings. Connector payloads, including HubSpot properties, preserve
// epoch-millisecond values as strings even when the source system declares the
// property numeric.
func formulaEpochMillisArg(fn, argName string, v interface{}) (int64, error) {
	switch n := v.(type) {
	case int:
		return int64(n), nil
	case int8:
		return int64(n), nil
	case int16:
		return int64(n), nil
	case int32:
		return int64(n), nil
	case int64:
		return n, nil
	case uint:
		if uint64(n) > math.MaxInt64 {
			break
		}
		return int64(n), nil
	case uint8:
		return int64(n), nil
	case uint16:
		return int64(n), nil
	case uint32:
		return int64(n), nil
	case uint64:
		if n <= math.MaxInt64 {
			return int64(n), nil
		}
	case float32:
		return formulaEpochMillisFloatArg(fn, argName, float64(n))
	case float64:
		return formulaEpochMillisFloatArg(fn, argName, n)
	case json.Number:
		value, err := strconv.ParseInt(n.String(), 10, 64)
		if err == nil {
			return value, nil
		}
	case string:
		value, err := strconv.ParseInt(n, 10, 64)
		if err == nil {
			return value, nil
		}
	}
	return 0, fmt.Errorf("%s %s must be an integral epoch-millisecond number, got %T", fn, argName, v)
}

func formulaEpochMillisFloatArg(fn, argName string, value float64) (int64, error) {
	if math.IsNaN(value) || math.IsInf(value, 0) || math.Trunc(value) != value || value < math.MinInt64 || value >= -math.MinInt64 {
		return 0, fmt.Errorf("%s %s must be an integral epoch-millisecond number, got %v", fn, argName, value)
	}
	return int64(value), nil
}

func (e *Engine) safeCompareFormulaArgs(args []interface{}, check func(int) bool, name string) (interface{}, error) {
	if err := e.checkArity(name, len(args)); err != nil {
		return nil, err
	}
	cmp, ok, err := safeFormulaCompare(args[0], args[1])
	if err != nil {
		return nil, err
	}
	if !ok {
		return false, nil
	}
	return check(cmp), nil
}

func (e *Engine) strictCompareFormulaArgs(args []interface{}, check func(int) bool, name string) (interface{}, error) {
	if err := e.checkArity(name, len(args)); err != nil {
		return nil, err
	}
	cmp, ok, err := safeFormulaCompare(args[0], args[1])
	if err != nil {
		return nil, err
	}
	if !ok {
		return true, nil
	}
	return check(cmp), nil
}

// formulaTimeKind distinguishes a date-only value from a datetime value so
// that safeFormulaCompare can apply Salesforce v63 mixed-compare semantics:
// when one operand is a DATE and the other is a DATETIME, the DATETIME side
// is truncated to its UTC calendar date before comparison.
type formulaTimeKind int

const (
	kindDate     formulaTimeKind = iota // date-only, no time component
	kindDatetime                        // full timestamp with time component
)

func safeFormulaCompare(left interface{}, right interface{}) (int, bool, error) {
	if isFormulaSafeCompareBlank(left) || isFormulaSafeCompareBlank(right) {
		return 0, false, nil
	}
	leftT, leftKind, leftDateErr := parseFormulaComparableTime(left)
	rightT, rightKind, rightDateErr := parseFormulaComparableTime(right)
	if leftDateErr == nil && rightDateErr == nil {
		return compareFormulaTimes(leftT, leftKind, rightT, rightKind), true, nil
	}
	leftNumber, leftNumberErr := parseFormulaComparableNumber(left)
	rightNumber, rightNumberErr := parseFormulaComparableNumber(right)
	if leftNumberErr == nil && rightNumberErr == nil {
		switch leftNumber.Cmp(rightNumber) {
		case -1:
			return -1, true, nil
		case 1:
			return 1, true, nil
		default:
			return 0, true, nil
		}
	}

	leftString, leftOK := left.(string)
	rightString, rightOK := right.(string)
	if leftOK && rightOK {
		switch {
		case leftString < rightString:
			return -1, true, nil
		case leftString > rightString:
			return 1, true, nil
		default:
			return 0, true, nil
		}
	}

	return 0, false, nil
}

// compareFormulaTimes applies Salesforce v63 comparison semantics:
//   - DATE vs DATE:         compare by calendar day (year/month/day) in UTC
//   - DATETIME vs DATETIME: compare by full UTC instant
//   - mixed DATE/DATETIME:  truncate the DATETIME side to its UTC calendar date,
//     then compare by calendar day — matching v63 implicit truncation behavior
func compareFormulaTimes(leftT time.Time, leftKind formulaTimeKind, rightT time.Time, rightKind formulaTimeKind) int {
	if leftKind == kindDate || rightKind == kindDate {
		// At least one side is a date-only value: compare by UTC calendar day.
		ld := time.Date(leftT.Year(), leftT.Month(), leftT.Day(), 0, 0, 0, 0, time.UTC)
		rd := time.Date(rightT.Year(), rightT.Month(), rightT.Day(), 0, 0, 0, 0, time.UTC)
		switch {
		case ld.Before(rd):
			return -1
		case ld.After(rd):
			return 1
		default:
			return 0
		}
	}
	// Both are datetimes: compare full UTC instants.
	switch {
	case leftT.Before(rightT):
		return -1
	case leftT.After(rightT):
		return 1
	default:
		return 0
	}
}

func isFormulaSafeCompareBlank(value interface{}) bool {
	if value == nil {
		return true
	}
	if s, ok := value.(string); ok {
		return strings.TrimSpace(s) == ""
	}
	return false
}

// parseFormulaComparableTime parses value into a time.Time and a formulaTimeKind.
// All returned time.Time values are normalized to UTC.
//
// Kind detection rules:
//   - Layout "2006-01-02"              → kindDate
//   - Layout "2006-01-02 15:04:05"    → kindDatetime
//   - time.RFC3339 / RFC3339Nano       → kindDatetime
//   - time.Time input                  → kindDatetime (always)
//
// A bare time.Time carries no schema context to disambiguate Date vs Datetime,
// so we never guess. Callers holding a schema-known Date field must convert it
// to a "2006-01-02" string before calling the formula engine — the string
// parser is the canonical way to produce kindDate. The previous heuristic
// (clock == 00:00:00 + Location == UTC ⇒ kindDate) over-matched legitimate
// DATETIME values landing on midnight UTC and silently truncated them to
// date-only, returning cmp=0 for `2026-05-06T00:00:00Z` vs `2026-05-06T12:00:00Z`.
func parseFormulaComparableTime(value interface{}) (time.Time, formulaTimeKind, error) {
	switch v := value.(type) {
	case time.Time:
		return v.UTC(), kindDatetime, nil
	case string:
		s := strings.TrimSpace(v)
		if s == "" {
			return time.Time{}, kindDatetime, fmt.Errorf("blank time")
		}
		// Date-only layout (no time component) → kindDate.
		if t, err := time.Parse("2006-01-02", s); err == nil {
			return t.UTC(), kindDate, nil
		}
		// Datetime layouts → kindDatetime.
		for _, layout := range []string{"2006-01-02 15:04:05", time.RFC3339, time.RFC3339Nano} {
			if t, err := time.Parse(layout, s); err == nil {
				return t.UTC(), kindDatetime, nil
			}
		}
	}
	return time.Time{}, kindDatetime, fmt.Errorf("not time comparable")
}

func parseFormulaComparableNumber(value interface{}) (*big.Rat, error) {
	switch v := value.(type) {
	case json.Number:
		return parseFormulaComparableDecimal(v.String())
	case float64:
		return parseFormulaComparableFloat(v)
	case float32:
		return parseFormulaComparableFloat(float64(v))
	case int:
		return big.NewRat(int64(v), 1), nil
	case int64:
		return big.NewRat(v, 1), nil
	case int32:
		return big.NewRat(int64(v), 1), nil
	case uint:
		return new(big.Rat).SetInt(new(big.Int).SetUint64(uint64(v))), nil
	case uint64:
		return new(big.Rat).SetInt(new(big.Int).SetUint64(v)), nil
	case uint32:
		return new(big.Rat).SetInt(new(big.Int).SetUint64(uint64(v))), nil
	case []byte:
		return parseFormulaComparableDecimal(string(v))
	case string:
		return parseFormulaComparableDecimal(v)
	}
	return nil, fmt.Errorf("not number comparable")
}

func parseFormulaComparableFloat(value float64) (*big.Rat, error) {
	rat := new(big.Rat).SetFloat64(value)
	if rat == nil {
		return nil, fmt.Errorf("invalid number")
	}
	return rat, nil
}

func parseFormulaComparableDecimal(value string) (*big.Rat, error) {
	trimmed := strings.TrimSpace(value)
	if trimmed == "" {
		return nil, fmt.Errorf("blank number")
	}
	if strings.Contains(trimmed, "/") {
		return nil, fmt.Errorf("not number comparable")
	}
	rat, ok := new(big.Rat).SetString(trimmed)
	if !ok {
		return nil, fmt.Errorf("not number comparable")
	}
	return rat, nil
}

func (e *Engine) normalizeFormulaExpression(expression string) string {
	if e == nil || e.functionPatterns == nil {
		return normalizeFormulaExpression(expression)
	}
	if e.normalizedCache != nil {
		e.normalizedMu.RLock()
		normalized, ok := e.normalizedCache[expression]
		e.normalizedMu.RUnlock()
		if ok {
			return normalized
		}
	}
	normalized := normalizeFormulaExpressionWithPatterns(expression, e.functionPatterns)
	e.normalizedMu.Lock()
	if e.normalizedCache == nil {
		e.normalizedCache = make(map[string]string)
	}
	if len(e.normalizedCache) >= normalizedFormulaCacheMaxEntries {
		e.normalizedCache = make(map[string]string)
	}
	e.normalizedCache[expression] = normalized
	e.normalizedMu.Unlock()
	return normalized
}

func normalizeFormulaExpression(expression string) string {
	return normalizeFormulaExpressionWithPatterns(expression, getGlobalFunctionPatterns())
}

func (e *Engine) clearNormalizedFormulaCache() {
	if e == nil {
		return
	}
	e.normalizedMu.Lock()
	e.normalizedCache = make(map[string]string)
	e.normalizedMu.Unlock()
}

func normalizeFormulaExpressionWithPatterns(expression string, patterns map[string]*regexp.Regexp) string {
	expr := strings.TrimSpace(expression)
	if expr == "" {
		return expr
	}

	// Remove Salesforce-style block comments used in some imported formulas.
	// Uses (?s) flag so . matches \n for multiline block comments (P1-G).
	expr = blockCommentPattern.ReplaceAllString(expr, " ")

	expr = strings.ReplaceAll(expr, "\r\n", " ")
	expr = strings.ReplaceAll(expr, "\n", " ")
	expr = strings.ReplaceAll(expr, "\r", " ")

	// Extract string literals into placeholders BEFORE collapsing whitespace so
	// that space-pad literals like " " or "  " are preserved intact.  The
	// placeholders (__STR_N__) contain no whitespace, so strings.Fields is safe
	// to run on them.
	var literals []string
	expr, literals = extractStringLiterals(expr)

	expr = strings.Join(strings.Fields(expr), " ")

	// Strip Salesforce merge-field braces: {!$Record.Foo} → $Record.Foo
	// Must run before $ and function normalization so inner content is clean.
	expr = mergeFieldPattern.ReplaceAllString(expr, "$1")

	// Salesforce polymorphic/typed relation syntax: Owner:User.Name -> Owner.Name
	expr = typedRelationPattern.ReplaceAllString(expr, ".")

	// Normalize Salesforce global variable prefix: $Profile.Name -> __Profile.Name
	// The $ character is not valid in expr-lang identifiers
	expr = dollarVarPattern.ReplaceAllString(expr, "__$1")

	// Normalize booleans/nulls
	for re, replacement := range keywordPatterns {
		expr = re.ReplaceAllString(expr, replacement)
	}

	// Normalize function names used in imported Salesforce formulas.
	for fn, re := range patterns {
		upper := strings.ToUpper(fn)
		expr = re.ReplaceAllStringFunc(expr, func(_ string) string {
			return upper + "("
		})
	}

	// Rewrite ISCHANGED(FieldName) — guarded by _has_prior_ so insert always returns false.
	// Lowered to __NCRM_FIELDUNCHANGED__ so the eval-time helper can set-compare
	// multi-picklist fields (reorder is not a change) while falling back to raw
	// Sprintf compare for all other field types. The field name is embedded as a
	// string literal so the helper can look it up in the MP-fields env at eval time.
	// This runs AFTER blockCommentPattern strips comments, making it comment-proof
	// (no pre-normalize string rewrite needed). Issue #1684 / PR #2047 Bug 2.
	expr = isChangedPattern.ReplaceAllString(expr, `(_has_prior_ && !__NCRM_FIELDUNCHANGED__("$1", $1, _prior_$1, __ncrm_multipicklist_fields))`)

	// Rewrite PRIORVALUE(FieldName) to _prior_FieldName
	expr = priorValuePattern.ReplaceAllString(expr, "_prior_$1")

	// Rewrite ISNEW() and ISCLONE() to sentinel variables
	expr = isNewPattern.ReplaceAllString(expr, "_is_new_")
	expr = isClonePattern.ReplaceAllString(expr, "_is_clone_")

	// Rewrite TODAY()+N and TODAY()-N to DATE_ADD(TODAY(), N) / DATE_ADD(TODAY(), -N)
	// so date arithmetic works without changing TODAY()'s return type.
	// Also handles NOW()+N.
	expr = todayArithPattern.ReplaceAllString(expr, "DATE_ADD($1(), $2$3)")

	// replaceFormulaOperators is already string-safe (uses its own quote tracking)
	// but strings are already extracted, so it operates on placeholders only.
	expr = replaceFormulaOperators(expr)

	// Salesforce-compat AND/OR/IS NULL rewrites must run AFTER replaceFormulaOperators
	// so callers that go through Evaluate (validation rules, sharing-rule criteria)
	// see a consistent expression: `=` is already `==`, `<>` is already `!=`, etc.
	// Running them earlier produced malformed expressions when the upstream form
	// still contained Salesforce `=`. Issue #1124 / #1128 regression.
	//
	// Salesforce-compat: rewrite `IS NULL` / `IS NOT NULL` (already lowercased
	// to `IS nil` / `IS NOT nil` by keywordPatterns) to expr-lang null
	// comparisons so the SQL walker emits `IS NULL` / `IS NOT NULL`.
	// Sequence: NOT variant first so it doesn't get consumed by the shorter one.
	expr = isNotNullPattern.ReplaceAllString(expr, "!= nil")
	expr = isNullPattern.ReplaceAllString(expr, "== nil")

	// Salesforce-compat: disambiguate infix `AND(` / `OR(` (expression
	// followed by AND and a subexpression) from call-form `AND(a, b, ...)`
	// by looking at the preceding character. Infix form rewrites to `&& (`
	// / `|| (`; call form stays and is handled by the walker. Must run
	// before the plain infix rewrite so the `(` doesn't confuse it.
	expr = andInfixParenPattern.ReplaceAllString(expr, "$1$2&& (")
	expr = orInfixParenPattern.ReplaceAllString(expr, "$1$2|| (")

	// Salesforce-compat: rewrite uppercase infix `AND` / `OR` to `&&` / `||`.
	// Call-form `AND(` / `OR(` is left alone so walker can treat it as a
	// variadic boolean function (handled in visitCall). The regex uses a
	// character-class follow-up capture (RE2 has no lookaheads) that excludes
	// `(`, so only the infix form matches.
	expr = andInfixPattern.ReplaceAllString(expr, "&& $2")
	expr = orInfixPattern.ReplaceAllString(expr, "|| $2")

	// Rewrite boolean comparisons to handle type mismatches (DB int64 vs bool)
	// Must run AFTER replaceFormulaOperators which converts Salesforce "=" to "=="
	// 1. "field == true" → "TRUTHY(field)" (handles int64/string/bool)
	expr = eqTruePattern.ReplaceAllString(expr, "TRUTHY($1)")
	// 2. "field == false" → "NOT(TRUTHY(field))"
	expr = eqFalsePattern.ReplaceAllString(expr, "NOT(TRUTHY($1))")
	// 3. "FUNC(...) == true" → remove redundant "== true" (func already returns bool)
	expr = funcEqTruePattern.ReplaceAllString(expr, ")")

	// Restore string literals back into the expression
	expr = restoreStringLiterals(expr, literals)
	return expr
}

// applySalesforceEmptyStringSemantics rewrites `<identPath> == ""` and
// `<identPath> != ""` to `ISBLANK(<identPath>)` and `!ISBLANK(<identPath>)`.
// Runs ONLY on the Evaluate path — not on ToSQL / Validate / relation
// extraction — because ValidationRule and formula-view SQL paths existed
// pre-fix and any semantic shift there would be a wider change than this
// PR's blast radius. Issue #1680.
func applySalesforceEmptyStringSemantics(expr string) string {
	expr = emptyStringEqPattern.ReplaceAllString(expr, "ISBLANK($1)")
	expr = emptyStringNePattern.ReplaceAllString(expr, "!ISBLANK($1)")
	return expr
}

func replaceFormulaOperators(expression string) string {
	var b strings.Builder
	inSingle := false
	inDouble := false

	for i := 0; i < len(expression); i++ {
		ch := expression[i]

		switch ch {
		case '\'':
			if !inDouble {
				inSingle = !inSingle
			}
			b.WriteByte(ch)
			continue
		case '"':
			if !inSingle {
				inDouble = !inDouble
			}
			b.WriteByte(ch)
			continue
		}

		if inSingle || inDouble {
			b.WriteByte(ch)
			continue
		}

		// Salesforce string concatenation
		if ch == '&' {
			if i+1 < len(expression) && expression[i+1] == '&' {
				b.WriteString("&&")
				i++
				continue
			}
			b.WriteByte('+')
			continue
		}

		// Salesforce inequality
		if ch == '<' && i+1 < len(expression) && expression[i+1] == '>' {
			b.WriteString("!=")
			i++
			continue
		}

		// Salesforce equality
		if ch == '=' {
			prev := byte(0)
			next := byte(0)
			if i > 0 {
				prev = expression[i-1]
			}
			if i+1 < len(expression) {
				next = expression[i+1]
			}
			if prev != '!' && prev != '<' && prev != '>' && prev != '=' && next != '=' {
				b.WriteString("==")
				continue
			}
		}

		b.WriteByte(ch)
	}

	return b.String()
}

// extractStringLiterals replaces all string literals (single- and double-quoted)
// with numbered placeholders and returns the modified expression plus the extracted strings.
// This allows regex-based rewrites to operate safely without modifying string contents.
func extractStringLiterals(expr string) (string, []string) {
	var b strings.Builder
	var literals []string
	i := 0
	for i < len(expr) {
		ch := expr[i]
		if ch == '"' || ch == '\'' {
			quote := ch
			var lit strings.Builder
			lit.WriteByte(ch)
			i++
			for i < len(expr) {
				c := expr[i]
				// Handle backslash-escaped quotes: \"  \'
				if c == '\\' && i+1 < len(expr) && expr[i+1] == quote {
					lit.WriteByte(c)
					lit.WriteByte(expr[i+1])
					i += 2
					continue
				}
				// Handle doubled-quote escape: ""  ''
				if c == quote && i+1 < len(expr) && expr[i+1] == quote {
					lit.WriteByte(c)
					lit.WriteByte(expr[i+1])
					i += 2
					continue
				}
				lit.WriteByte(c)
				i++
				if c == quote {
					break
				}
			}
			b.WriteString(fmt.Sprintf("__STR_%d__", len(literals)))
			literals = append(literals, lit.String())
		} else {
			b.WriteByte(ch)
			i++
		}
	}
	return b.String(), literals
}

// restoreStringLiterals replaces placeholders back with the original string literals.
func restoreStringLiterals(expr string, literals []string) string {
	for i, lit := range literals {
		expr = strings.Replace(expr, fmt.Sprintf("__STR_%d__", i), lit, 1)
	}
	return expr
}

// extractRecordRelations returns the distinct relationship-segment names
// referenced by a normalized expression as __Record.<Name>.<Field>… The
// result is order-preserved per first occurrence so callers that iterate
// for lazy fetching observe deterministic behaviour across evaluations.
type formulaRelationScan struct {
	recordRelations      []string
	recordRelationChains [][]string
	bareRelations        []string
}

func formulaRelationScanForEvaluation(normalizedExpr string, ctx *Context) formulaRelationScan {
	scan, ok := activeFormulaRelationScan(normalizedExpr, ctx)
	if ok {
		return scan
	}
	return formulaRelationScan{
		recordRelations:      extractRecordRelations(normalizedExpr),
		recordRelationChains: extractRecordRelationChains(normalizedExpr),
		bareRelations:        extractBareRelations(normalizedExpr),
	}
}

type formulaStaticBool struct {
	known bool
	value bool
}

type activeFormulaRelationCollector struct {
	isNew        bool
	hasPrior     bool
	recordSeen   map[string]struct{}
	bareSeen     map[string]struct{}
	chainSeen    map[string]struct{}
	recordRels   []string
	bareRels     []string
	recordChains [][]string
}

func activeFormulaRelationScan(normalizedExpr string, ctx *Context) (formulaRelationScan, bool) {
	tree, err := parser.Parse(normalizedExpr)
	if err != nil || tree == nil || tree.Node == nil {
		return formulaRelationScan{}, false
	}
	c := &activeFormulaRelationCollector{
		isNew:      ctx == nil || (!ctx.ForceNotNew && ctx.Prior == nil),
		hasPrior:   ctx != nil && ctx.Prior != nil,
		recordSeen: make(map[string]struct{}),
		bareSeen:   make(map[string]struct{}),
		chainSeen:  make(map[string]struct{}),
	}
	c.collect(tree.Node)
	return formulaRelationScan{
		recordRelations:      c.recordRels,
		recordRelationChains: c.recordChains,
		bareRelations:        c.bareRels,
	}, true
}

func (c *activeFormulaRelationCollector) collect(node ast.Node) formulaStaticBool {
	if node == nil {
		return formulaStaticBool{}
	}
	switch n := node.(type) {
	case *ast.BoolNode:
		return formulaStaticBool{known: true, value: n.Value}
	case *ast.ConstantNode:
		if b, ok := n.Value.(bool); ok {
			return formulaStaticBool{known: true, value: b}
		}
	case *ast.IdentifierNode:
		switch n.Value {
		case "_is_new_":
			return formulaStaticBool{known: true, value: c.isNew}
		case "_has_prior_":
			return formulaStaticBool{known: true, value: c.hasPrior}
		case "_is_clone_":
			return formulaStaticBool{known: true, value: false}
		}
	case *ast.UnaryNode:
		value := c.collect(n.Node)
		if (n.Operator == "!" || strings.EqualFold(n.Operator, "not")) && value.known {
			return formulaStaticBool{known: true, value: !value.value}
		}
		return formulaStaticBool{}
	case *ast.BinaryNode:
		op := strings.ToLower(strings.TrimSpace(n.Operator))
		switch op {
		case "&&", "and":
			left := c.collect(n.Left)
			if left.known && !left.value {
				return formulaStaticBool{known: true, value: false}
			}
			right := c.collect(n.Right)
			if left.known && left.value && right.known {
				return formulaStaticBool{known: true, value: right.value}
			}
			if right.known && !right.value {
				return formulaStaticBool{known: true, value: false}
			}
			return formulaStaticBool{}
		case "||", "or":
			left := c.collect(n.Left)
			if left.known && left.value {
				return formulaStaticBool{known: true, value: true}
			}
			right := c.collect(n.Right)
			if left.known && !left.value && right.known {
				return formulaStaticBool{known: true, value: right.value}
			}
			if right.known && right.value {
				return formulaStaticBool{known: true, value: true}
			}
			return formulaStaticBool{}
		default:
			c.collect(n.Left)
			c.collect(n.Right)
		}
	case *ast.CallNode:
		return c.collectCall(n)
	case *ast.ConditionalNode:
		cond := c.collect(n.Cond)
		if cond.known {
			if cond.value {
				return c.collect(n.Exp1)
			}
			return c.collect(n.Exp2)
		}
		c.collect(n.Exp1)
		c.collect(n.Exp2)
	case *ast.ChainNode:
		return c.collect(n.Node)
	case *ast.MemberNode:
		if path, ok := formulaMemberPath(n); ok {
			c.addMemberPath(path)
			return formulaStaticBool{}
		}
		c.collect(n.Node)
		c.collect(n.Property)
	case *ast.SliceNode:
		c.collect(n.Node)
		c.collect(n.From)
		c.collect(n.To)
	case *ast.BuiltinNode:
		for _, arg := range n.Arguments {
			c.collect(arg)
		}
	case *ast.PredicateNode:
		c.collect(n.Node)
	case *ast.VariableDeclaratorNode:
		c.collect(n.Value)
		c.collect(n.Expr)
	case *ast.SequenceNode:
		for _, child := range n.Nodes {
			c.collect(child)
		}
	case *ast.ArrayNode:
		for _, child := range n.Nodes {
			c.collect(child)
		}
	case *ast.MapNode:
		for _, pair := range n.Pairs {
			c.collect(pair)
		}
	case *ast.PairNode:
		c.collect(n.Key)
		c.collect(n.Value)
	}
	return formulaStaticBool{}
}

func (c *activeFormulaRelationCollector) collectCall(n *ast.CallNode) formulaStaticBool {
	name := ""
	if ident, ok := n.Callee.(*ast.IdentifierNode); ok {
		name = strings.ToUpper(ident.Value)
	}
	switch name {
	case "NOT":
		if len(n.Arguments) != 1 {
			for _, arg := range n.Arguments {
				c.collect(arg)
			}
			return formulaStaticBool{}
		}
		value := c.collect(n.Arguments[0])
		if value.known {
			return formulaStaticBool{known: true, value: !value.value}
		}
		return formulaStaticBool{}
	case "AND":
		if len(n.Arguments) == 0 {
			return formulaStaticBool{}
		}
		allKnownTrue := true
		for _, arg := range n.Arguments {
			value := c.collect(arg)
			if value.known {
				if !value.value {
					return formulaStaticBool{known: true, value: false}
				}
				continue
			}
			allKnownTrue = false
		}
		if allKnownTrue {
			return formulaStaticBool{known: true, value: true}
		}
		return formulaStaticBool{}
	case "OR":
		if len(n.Arguments) == 0 {
			return formulaStaticBool{}
		}
		allKnownFalse := true
		for _, arg := range n.Arguments {
			value := c.collect(arg)
			if value.known {
				if value.value {
					return formulaStaticBool{known: true, value: true}
				}
				continue
			}
			allKnownFalse = false
		}
		if allKnownFalse {
			return formulaStaticBool{known: true, value: false}
		}
		return formulaStaticBool{}
	default:
		c.collect(n.Callee)
		for _, arg := range n.Arguments {
			c.collect(arg)
		}
	}
	return formulaStaticBool{}
}

func formulaMemberPath(node ast.Node) ([]string, bool) {
	switch n := node.(type) {
	case *ast.IdentifierNode:
		return []string{n.Value}, true
	case *ast.ChainNode:
		return formulaMemberPath(n.Node)
	case *ast.MemberNode:
		base, ok := formulaMemberPath(n.Node)
		if !ok {
			return nil, false
		}
		prop, ok := n.Property.(*ast.StringNode)
		if !ok || prop.Value == "" {
			return nil, false
		}
		return append(base, prop.Value), true
	default:
		return nil, false
	}
}

func (c *activeFormulaRelationCollector) addMemberPath(path []string) {
	if len(path) < 2 {
		return
	}
	root := path[0]
	if root == "__Record" {
		if len(path) < 3 {
			return
		}
		hops := path[1 : len(path)-1]
		c.addRecordRelation(hops[0])
		if len(hops) >= 2 {
			c.addRecordRelationChain(hops)
		}
		return
	}
	if formulaIsReservedBareRelationRoot(root) || strings.HasPrefix(root, "__") {
		return
	}
	c.addBareRelation(root)
}

func (c *activeFormulaRelationCollector) addRecordRelation(name string) {
	if name == "" {
		return
	}
	if _, ok := c.recordSeen[name]; ok {
		return
	}
	c.recordSeen[name] = struct{}{}
	c.recordRels = append(c.recordRels, name)
}

func (c *activeFormulaRelationCollector) addBareRelation(name string) {
	if name == "" {
		return
	}
	if _, ok := c.bareSeen[name]; ok {
		return
	}
	c.bareSeen[name] = struct{}{}
	c.bareRels = append(c.bareRels, name)
}

func (c *activeFormulaRelationCollector) addRecordRelationChain(chain []string) {
	if len(chain) < 2 {
		return
	}
	key := strings.Join(chain, ".")
	if _, ok := c.chainSeen[key]; ok {
		return
	}
	c.chainSeen[key] = struct{}{}
	c.recordChains = append(c.recordChains, append([]string(nil), chain...))
}

func formulaIsReservedBareRelationRoot(root string) bool {
	switch root {
	case "record", "prior", "user", "env", "var":
		return true
	default:
		return false
	}
}

// recordRelationChainPattern matches multi-hop cross-object relationship chains
// of the form __Record.<seg1>.<seg2>[.<segN>].<leaf> where the chain has 2 or
// more relationship hops before the leaf field. Single-hop refs are already
// handled by recordRelationPattern / extractRecordRelations; this pattern
// captures only chains of depth ≥ 2 so the augmenter can walk them
// segment-by-segment.
var recordRelationChainPattern = regexp.MustCompile(`__Record((?:\.[A-Za-z_][A-Za-z0-9_]*){2,})\.[A-Za-z_]`)

// extractRecordRelationChains returns the multi-hop relationship segments for
// each chain of depth ≥ 2 found in normalizedExpr. For example,
// __Record.A__r.B__r.Name yields [["A__r", "B__r"]].
// Single-hop refs are excluded; those are handled by extractRecordRelations.
func extractRecordRelationChains(normalizedExpr string) [][]string {
	matches := recordRelationChainPattern.FindAllStringSubmatch(normalizedExpr, -1)
	if len(matches) == 0 {
		return nil
	}
	type key = string
	seen := make(map[key]struct{}, len(matches))
	var out [][]string
	for _, m := range matches {
		chainStr := m[1] // e.g. ".A__r.B__r"
		seenKey := chainStr
		if _, dup := seen[seenKey]; dup {
			continue
		}
		seen[seenKey] = struct{}{}
		// Split on "." and drop the empty leading element.
		parts := strings.Split(chainStr, ".")
		var segs []string
		for _, p := range parts {
			if p != "" {
				segs = append(segs, p)
			}
		}
		if len(segs) >= 2 {
			out = append(out, segs)
		}
	}
	return out
}

// augmentRecordWithRelationChain walks each multi-hop chain segment-by-segment,
// calling fetcher at each step to populate intermediate and terminal relations.
// It never mutates the input record; each hop that requires fetching produces a
// new copy. If fetcher returns (nil, nil) for an intermediate hop (definitive
// null FK), the chain is terminated with an empty map installed at that segment
// (same convention as augmentRecordWithRelations). If fetcher returns an error,
// ErrRelationFetch is returned immediately.
func augmentRecordWithRelationChain(
	record map[string]interface{},
	chains [][]string,
	fetcher func(record map[string]interface{}, relationName string) (map[string]interface{}, error),
) (map[string]interface{}, error) {
	if len(chains) == 0 || fetcher == nil {
		return record, nil
	}
	// Work on a shallow copy so the caller's record is never mutated.
	result := make(map[string]interface{}, len(record))
	for k, v := range record {
		result[k] = v
	}

	for _, chain := range chains {
		current := map[string]interface{}(result)
		// chainPath tracks the nested path we are building so we can
		// install results into the correct nested map.
		path := make([]map[string]interface{}, 0, len(chain))
		path = append(path, result)

		for i, seg := range chain {
			existing, ok := current[seg]
			if ok && existing != nil {
				// Already populated — step into it.
				if next, castOk := existing.(map[string]interface{}); castOk {
					current = next
					if i < len(chain)-1 {
						path = append(path, next)
					}
				}
				continue
			}
			// Fetch the related record at this hop.
			related, err := fetcher(current, seg)
			if err != nil {
				return nil, fmt.Errorf("%w: chain %v hop %q: %v", ErrRelationFetch, chain, seg, err)
			}
			var installed map[string]interface{}
			if related == nil {
				// Definitive null: install empty map so the expression engine
				// resolves downstream field accesses as nil rather than panicking
				// on "unknown name". Same convention as augmentRecordWithRelations.
				installed = map[string]interface{}{}
			} else {
				installed = related
			}
			// Shallow-copy the current level so parent maps are not mutated.
			parent := path[len(path)-1]
			newParent := make(map[string]interface{}, len(parent))
			for k, v := range parent {
				newParent[k] = v
			}
			newParent[seg] = installed
			// Stitch the new parent back up the path.
			if len(path) == 1 {
				// Root level — update result directly.
				for k, v := range newParent {
					result[k] = v
				}
			} else {
				// Propagate change up through path.
				for pi := len(path) - 2; pi >= 0; pi-- {
					grandparent := path[pi]
					newGP := make(map[string]interface{}, len(grandparent))
					for k, v := range grandparent {
						newGP[k] = v
					}
					childSeg := chain[pi]
					newGP[childSeg] = newParent
					if pi == 0 {
						for k, v := range newGP {
							result[k] = v
						}
					}
					newParent = newGP
					_ = grandparent
				}
			}
			path = append(path, installed)
			current = installed
			if related == nil {
				break // Chain terminated at definitive null.
			}
		}
	}
	return result, nil
}

// A relationship segment is any identifier following `__Record.` that is
// itself followed by another `.` — i.e. used as a step, not a terminal
// field access. Bare field refs like `__Record.Name` are deliberately
// excluded because they need no lazy fetch.
func extractRecordRelations(normalizedExpr string) []string {
	matches := recordRelationPattern.FindAllStringSubmatch(normalizedExpr, -1)
	if len(matches) == 0 {
		return nil
	}
	seen := make(map[string]struct{}, len(matches))
	out := make([]string, 0, len(matches))
	for _, m := range matches {
		name := m[1]
		if _, dup := seen[name]; dup {
			continue
		}
		seen[name] = struct{}{}
		out = append(out, name)
	}
	return out
}

// extractBareRelations returns relation names used as bare dotted prefixes in
// normalizedExpr — e.g. "Parent__r" from TEXT(Parent__r.Region__c), "RecordType"
// from RecordType.Name. These are the Salesforce implicit-record-context
// shorthand where a parent lookup relationship name is used directly without
// the $Record. qualifier. Issue #2092.
//
// Complements extractRecordRelations (which handles __Record.X.Y form after
// $Record. normalization). Both sets are consumed by Evaluate's step-0
// augmentation so bare X.Y paths and $Record.X.Y paths go through the same
// fetcher and the same augmentRecordWithRelations idempotency guard.
func extractBareRelations(normalizedExpr string) []string {
	// Strip string literals so quoted content (e.g. "See Parent__r.Name") never
	// matches as a real relation reference. normalizeFormulaExpression restores
	// literals after its own rewrites, so the expression received here may still
	// contain raw quoted strings.
	stripped, _ := extractStringLiterals(normalizedExpr)
	matches := bareRelationPattern.FindAllStringSubmatch(stripped, -1)
	if len(matches) == 0 {
		return nil
	}
	seen := make(map[string]struct{}, len(matches))
	out := make([]string, 0, len(matches))
	for _, m := range matches {
		ident := m[2]
		if strings.HasPrefix(ident, "__") {
			continue // normalised global vars (__Record, __Profile, etc.)
		}
		if formulaIsReservedBareRelationRoot(ident) {
			continue
		}
		if _, dup := seen[ident]; dup {
			continue
		}
		seen[ident] = struct{}{}
		out = append(out, ident)
	}
	return out
}

// augmentRecordWithRelations invokes fetcher for each named relation that is
// not already populated inline on record, returning a new map with the
// fetched records installed under the relation name. The input record is
// never mutated. When no relations require fetching (all inline or already
// returned definitive-null) the original record is returned so the hot path
// stays allocation-free.
//
// Error contract:
//
//   - (nil, nil) from fetcher: definitive null (FK cleared / orphan / unknown
//     relation). Skip silently — formula evaluator treats as null parent.
//
//   - (_, err) from fetcher: indeterminate (transient repo error, metadata-
//     cache miss). Propagate the error so Evaluate can wrap it with
//     ErrRelationFetch and let consumers preserve old values rather than
//     corrupt them with nil. Issue #2057.
func augmentRecordWithRelations(
	record map[string]interface{},
	relations []string,
	fetcher func(record map[string]interface{}, relationName string) (map[string]interface{}, error),
) (map[string]interface{}, error) {
	var augmented map[string]interface{}
	for _, rel := range relations {
		if existing, ok := record[rel]; ok && existing != nil {
			continue // caller already supplied the related record inline
		}
		related, err := fetcher(record, rel)
		if err != nil {
			// Indeterminate: surface to caller. Do NOT swallow — pre-#2057
			// behavior corrupted stored formula values on transient blips.
			return nil, fmt.Errorf("relation %q: %w", rel, err)
		}
		if related == nil {
			// Definitive null: expose as an empty map so the expression engine
			// resolves the identifier (no "unknown name X") and any field access
			// returns nil via map key lookup rather than panicking. Installing nil
			// instead of an empty map would cause "type nil has no field X" in
			// the expr-lang VM because interface{}(nil) has no type to reflect on.
			// Issue #2181 r2.
			if augmented == nil {
				augmented = make(map[string]interface{}, len(record)+len(relations))
				for k, v := range record {
					augmented[k] = v
				}
			}
			augmented[rel] = map[string]interface{}{}
			continue
		}
		if augmented == nil {
			augmented = make(map[string]interface{}, len(record)+len(relations))
			for k, v := range record {
				augmented[k] = v
			}
		}
		augmented[rel] = related
	}
	if augmented != nil {
		return augmented, nil
	}
	return record, nil
}

// engineExtractMPItems mirrors automation.extractMultiPicklistItems but lives
// inside pkg/formula to avoid an import cycle (formula ← automation).
// Handles every serialization the records layer can produce:
//   - string: semicolon-joined OR JSON-array string (e.g. `["a","b"]`)
//   - []string: already split
//   - []interface{}: elements converted via fmt.Sprintf
//   - []byte: re-interpreted as string then recurse
//
// Items are trimmed; empty items and duplicates are discarded.
func engineExtractMPItems(v interface{}) []string {
	switch tv := v.(type) {
	case nil:
		return nil
	case []byte:
		return engineExtractMPItems(string(tv))
	case string:
		s := strings.TrimSpace(tv)
		if s == "" {
			return nil
		}
		if s[0] == '[' {
			var arr []string
			if err := json.Unmarshal([]byte(s), &arr); err == nil {
				return engineDedupeTrimmed(arr)
			}
			// Malformed JSON: fall through to semicolon split.
		}
		return engineDedupeTrimmed(strings.Split(s, ";"))
	case []string:
		return engineDedupeTrimmed(tv)
	case []interface{}:
		out := make([]string, 0, len(tv))
		for _, it := range tv {
			if s, ok := it.(string); ok {
				out = append(out, s)
			} else if it != nil {
				out = append(out, fmt.Sprintf("%v", it))
			}
		}
		return engineDedupeTrimmed(out)
	}
	return []string{fmt.Sprintf("%v", v)}
}

func engineDedupeTrimmed(in []string) []string {
	if len(in) == 0 {
		return nil
	}
	seen := make(map[string]struct{}, len(in))
	out := make([]string, 0, len(in))
	for _, s := range in {
		s = strings.TrimSpace(s)
		if s == "" {
			continue
		}
		if _, dup := seen[s]; !dup {
			seen[s] = struct{}{}
			out = append(out, s)
		}
	}
	if len(out) == 0 {
		return nil
	}
	return out
}

// engineMPCanonicalKey returns a stable sorted key for any MP serialization.
func engineMPCanonicalKey(v interface{}) string {
	items := engineExtractMPItems(v)
	if len(items) == 0 {
		return ""
	}
	// Insertion sort (small cardinality — typically <20 items).
	for i := 1; i < len(items); i++ {
		for j := i; j > 0 && items[j] < items[j-1]; j-- {
			items[j], items[j-1] = items[j-1], items[j]
		}
	}
	return strings.Join(items, "\x00")
}

// engineMPSetEqual reports whether two MP values represent the same set.
// Handles all serialization shapes via engineExtractMPItems.
func engineMPSetEqual(a, b interface{}) bool {
	return engineMPCanonicalKey(a) == engineMPCanonicalKey(b)
}