package expression

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

	"github.com/expr-lang/expr"
	"github.com/expr-lang/expr/vm"

	"github.com/nexuscrm/kernel/pkg/formula/spec"
	"github.com/nexuscrm/kernel/pkg/utils"
)

// MultiPicklistFieldEnvKey is an optional environment entry used by the
// INCLUDES AST adapter. Values are keyed by formula field path, for example
// "Field", "__Record.Field", or "_prior_Field".
const MultiPicklistFieldEnvKey = "__ncrm_multipicklist_fields"

// Engine is a wrapper around antonmedv/expr
type Engine struct {
	programCache     map[string]*vm.Program
	functions        map[string]func(params ...interface{}) (interface{}, error)
	functionRegistry *spec.Registry
	mu               sync.RWMutex
}

// EngineOption configures an expression engine.
type EngineOption func(*Engine)

// WithFunctionRegistry installs a snapshot of registry as the engine-local
// function catalog used by built-in runtime evaluator guards.
func WithFunctionRegistry(registry *spec.Registry) EngineOption {
	return func(e *Engine) {
		if registry == nil {
			e.functionRegistry = spec.DefaultRegistry().Clone()
			return
		}
		e.functionRegistry = registry.Clone()
	}
}

// NewEngine creates a new expression engine
func NewEngine(options ...EngineOption) *Engine {
	engine := &Engine{
		programCache:     make(map[string]*vm.Program),
		functions:        make(map[string]func(params ...interface{}) (interface{}, error)),
		functionRegistry: spec.DefaultRegistry().Clone(),
	}
	for _, option := range options {
		if option != nil {
			option(engine)
		}
	}
	if engine.functionRegistry == nil {
		engine.functionRegistry = spec.DefaultRegistry().Clone()
	}
	return engine
}

// Evaluate compiles (if needed) and runs an expression against the given environment
func (e *Engine) Evaluate(expression string, env map[string]interface{}) (interface{}, error) {
	env = ensureExpressionEnv(env)
	program, err := e.getProgram(expression, env, false)
	if err != nil {
		return nil, err
	}

	output, err := expr.Run(program, env)
	if err != nil {
		return nil, err
	}
	return output, nil
}

// EvaluateWithSQLNullDivision behaves like Evaluate but compiles the
// expression with SQL-parity division: every `/` lowers to __NCRM_SQLDIV__,
// where a zero divisor (or a non-finite quotient) yields nil instead of the
// IEEE +Inf/NaN that the native float operator produces. Formula-FIELD
// evaluation under blank-as-zero (#4152) uses this so a blank-coerced zero
// divisor cannot leak a non-finite number into enclosing branches or
// comparisons — the SQL projection renders the same division as NULL. All
// other callers keep Evaluate and the native operator, byte-for-byte.
// Programs are cached separately per compilation mode.
func (e *Engine) EvaluateWithSQLNullDivision(expression string, env map[string]interface{}) (interface{}, error) {
	env = ensureExpressionEnv(env)
	program, err := e.getProgram(expression, env, true)
	if err != nil {
		return nil, err
	}

	output, err := expr.Run(program, env)
	if err != nil {
		return nil, err
	}
	return output, nil
}

func ensureExpressionEnv(env map[string]interface{}) map[string]interface{} {
	if env == nil {
		return map[string]interface{}{MultiPicklistFieldEnvKey: map[string]interface{}{}}
	}
	if _, ok := env[MultiPicklistFieldEnvKey]; ok {
		return env
	}
	withDefaults := make(map[string]interface{}, len(env)+1)
	for k, v := range env {
		withDefaults[k] = v
	}
	withDefaults[MultiPicklistFieldEnvKey] = map[string]interface{}{}
	return withDefaults
}

// RegisterFunctionSpec registers a function spec in the engine's arity
// catalog without requiring an evaluator. This allows bundle functions that
// call existing evaluators (e.g. a formula_expr_v1 body that delegates to a
// compiled-in function) to pass arity validation without duplicating the
// evaluator. Must be called before the engine compiles any expression that
// references the function.
func (e *Engine) RegisterFunctionSpec(fn spec.FunctionSpec) error {
	if strings.TrimSpace(fn.Name) == "" {
		return fmt.Errorf("expression: function spec name is required")
	}
	e.mu.Lock()
	defer e.mu.Unlock()
	if e.functionRegistry == nil {
		e.functionRegistry = spec.DefaultRegistry().Clone()
	}
	if _, exists := e.functionRegistry.Lookup(fn.Name); exists {
		return fmt.Errorf("expression: function spec %q is already registered", fn.Name)
	}
	if err := e.functionRegistry.Register(fn); err != nil {
		return err
	}
	e.programCache = make(map[string]*vm.Program)
	return nil
}

// RegisterFunction registers a custom evaluator. It rejects invalid names,
// nil evaluators, and duplicate evaluator names so callers cannot silently
// replace an active runtime implementation.
func (e *Engine) RegisterFunction(name string, fn func(params ...interface{}) (interface{}, error)) error {
	if strings.TrimSpace(name) == "" {
		return fmt.Errorf("expression: function name is required")
	}
	if fn == nil {
		return fmt.Errorf("expression: function %q has nil evaluator", name)
	}
	e.mu.Lock()
	defer e.mu.Unlock()
	if e.functions == nil {
		e.functions = make(map[string]func(params ...interface{}) (interface{}, error))
	}
	if _, exists := e.functions[name]; exists {
		return fmt.Errorf("expression: duplicate evaluator registration for function %q", name)
	}
	e.functions[name] = fn
	// Clear cache as available functions changed
	e.programCache = make(map[string]*vm.Program)
	return nil
}

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) getProgram(expression string, env map[string]interface{}, sqlNullDivision bool) (*vm.Program, error) {
	// Programs compiled with the SQL-parity division patch are distinct
	// artifacts; cache them under a variant key so the two modes never share
	// a compiled program. The NUL prefix cannot collide with authored text.
	cacheKey := expression
	if sqlNullDivision {
		cacheKey = "\x00sqldiv\x00" + expression
	}
	e.mu.RLock()
	if prog, ok := e.programCache[cacheKey]; ok {
		e.mu.RUnlock()
		return prog, nil
	}
	e.mu.RUnlock()

	e.mu.Lock()
	defer e.mu.Unlock()

	// Double check
	if prog, ok := e.programCache[cacheKey]; ok {
		return prog, nil
	}

	// Define standard functions
	options := []expr.Option{
		expr.Env(env),
		expr.Function("TODAY", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("TODAY", len(params)); err != nil {
				return nil, err
			}
			return time.Now().Format("2006-01-02"), nil
		}),
		expr.Function("NOW", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("NOW", len(params)); err != nil {
				return nil, err
			}
			return time.Now().Format("2006-01-02 15:04:05"), nil
		}),
		expr.Function("LEN", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("LEN", len(params)); err != nil {
				return nil, err
			}
			if params[0] == nil {
				return 0, nil // Salesforce: LEN(null) = 0
			}
			s, ok := params[0].(string)
			if !ok {
				return nil, fmt.Errorf("LEN argument must be string")
			}
			return len([]rune(s)), nil
		}),
		expr.Function("UPPER", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("UPPER", len(params)); err != nil {
				return nil, err
			}
			if params[0] == nil {
				return "", nil // Salesforce: UPPER(null) = ""
			}
			s, ok := params[0].(string)
			if !ok {
				return nil, fmt.Errorf("UPPER argument must be string")
			}
			return strings.ToUpper(s), nil
		}),
		expr.Function("LOWER", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("LOWER", len(params)); err != nil {
				return nil, err
			}
			if params[0] == nil {
				return "", nil // Salesforce: LOWER(null) = ""
			}
			s, ok := params[0].(string)
			if !ok {
				return nil, fmt.Errorf("LOWER argument must be string")
			}
			return strings.ToLower(s), nil
		}),
		expr.Function("ROUND", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("ROUND", len(params)); err != nil {
				return nil, err
			}
			val, err := toFloat(params[0])
			if err != nil {
				return nil, fmt.Errorf("ROUND arg 1 must be number")
			}
			prec, err := toInt(params[1])
			if err != nil {
				return nil, fmt.Errorf("ROUND arg 2 must be integer")
			}

			// Salesforce-compatible half-up rounding (ROUND(2.5)=3, not banker's rounding)
			mult := 1.0
			for i := 0; i < prec; i++ {
				mult *= 10
			}
			if val >= 0 {
				return math.Floor(val*mult+0.5) / mult, nil
			}
			return -math.Floor(-val*mult+0.5) / mult, nil
		}),
		expr.Function("IF", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("IF", len(params)); err != nil {
				return nil, err
			}
			cond, ok := params[0].(bool)
			if !ok {
				return nil, fmt.Errorf("IF condition must be boolean")
			}
			if cond {
				return params[1], nil
			}
			return params[2], nil
		}),
		expr.Function("OR", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("OR", len(params)); err != nil {
				return nil, err
			}
			for _, p := range params {
				if truthy(p) {
					return true, nil
				}
			}
			return false, nil
		}),
		expr.Function("AND", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("AND", len(params)); err != nil {
				return nil, err
			}
			for _, p := range params {
				if !truthy(p) {
					return false, nil
				}
			}
			return true, nil
		}),
		expr.Function("NOT", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("NOT", len(params)); err != nil {
				return nil, err
			}
			return !truthy(params[0]), nil
		}),
		expr.Function("TRUTHY", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("TRUTHY", len(params)); err != nil {
				return nil, err
			}
			return truthy(params[0]), nil
		}),
		expr.Function("BEGINS", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("BEGINS", len(params)); err != nil {
				return nil, err
			}
			s, ok1 := asString(params[0])
			prefix, ok2 := asString(params[1])
			if !ok1 || !ok2 {
				return nil, fmt.Errorf("BEGINS arguments must be strings")
			}
			return strings.HasPrefix(s, prefix), nil
		}),
		expr.Function("FIND", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("FIND", len(params)); err != nil {
				return nil, err
			}
			substr, ok1 := asString(params[0])
			s, ok2 := asString(params[1])
			if !ok1 || !ok2 {
				return nil, fmt.Errorf("FIND arguments 1 and 2 must be strings")
			}
			start := 0
			if len(params) == 3 {
				n, err := toInt(params[2])
				if err != nil {
					return nil, fmt.Errorf("FIND arg 3 must be integer")
				}
				if n > 0 {
					start = n - 1
				}
			}
			runes := []rune(s)
			if start >= len(runes) {
				return 0, nil
			}
			idx := strings.Index(string(runes[start:]), substr)
			if idx < 0 {
				return 0, nil
			}
			// Convert byte offset back to rune offset
			runeIdx := len([]rune(string(runes[start:])[:idx]))
			return runeIdx + start + 1, nil
		}),
		expr.Function("SUBSTITUTE", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("SUBSTITUTE", len(params)); err != nil {
				return nil, err
			}
			text, ok1 := asString(params[0])
			oldText, ok2 := asString(params[1])
			newText, ok3 := asString(params[2])
			if !ok1 || !ok2 || !ok3 {
				return nil, fmt.Errorf("SUBSTITUTE first 3 arguments must be strings")
			}
			if len(params) == 4 {
				instanceNum, err := toInt(params[3])
				if err != nil {
					return nil, fmt.Errorf("SUBSTITUTE arg 4 must be integer")
				}
				if instanceNum <= 0 {
					return text, nil
				}
				count := 0
				return regexp.MustCompile(regexp.QuoteMeta(oldText)).ReplaceAllStringFunc(text, func(match string) string {
					count++
					if count == instanceNum {
						return newText
					}
					return match
				}), nil
			}
			return strings.ReplaceAll(text, oldText, newText), nil
		}),
		// __DATE_ADD_MONTHS__: vendor-free month-addition primitive. Implements
		// Salesforce last-day anchoring: if the input is the last day of its
		// month, the result is the last day of the target month; otherwise the
		// result day is min(inputDay, lastDayOfTargetMonth).
		// Called by the L1 ADDMONTHS bundle: __DATE_ADD_MONTHS__(arg0, arg1).
		expr.Function("__DATE_ADD_MONTHS__", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("__DATE_ADD_MONTHS__", len(params)); err != nil {
				return nil, err
			}
			t, err := parseDate(params[0])
			if err != nil {
				return nil, fmt.Errorf("__DATE_ADD_MONTHS__: %w", err)
			}
			months, err := toInt(params[1])
			if err != nil {
				return nil, fmt.Errorf("__DATE_ADD_MONTHS__ arg 2 must be integer")
			}
			result, valid := addMonthsSF(t, months)
			if !valid {
				return nil, nil
			}
			return result.Format("2006-01-02"), nil
		}),
		// __DATE_ADD_MONTHS_TARGET_CLAMP__: neutral calendar-month addition. It keeps
		// the input day when the target month has it and otherwise clamps to the
		// target month's final day. Unlike the L1 ADDMONTHS implementation above,
		// an input that merely happens to be month-end is not anchored to future
		// month ends.
		expr.Function("__DATE_ADD_MONTHS_TARGET_CLAMP__", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("__DATE_ADD_MONTHS_TARGET_CLAMP__", len(params)); err != nil {
				return nil, err
			}
			t, err := parseDate(params[0])
			if err != nil {
				return nil, fmt.Errorf("__DATE_ADD_MONTHS_TARGET_CLAMP__: %w", err)
			}
			months, err := toInt(params[1])
			if err != nil {
				return nil, fmt.Errorf("__DATE_ADD_MONTHS_TARGET_CLAMP__ arg 2 must be integer")
			}
			result, valid := addMonthsClamp(t, months)
			if !valid {
				return nil, nil
			}
			return result.Format("2006-01-02"), nil
		}),
		expr.Function("__DATE_DIFF_DAYS__", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("__DATE_DIFF_DAYS__", len(params)); err != nil {
				return nil, err
			}
			end, err := parseDate(params[0])
			if err != nil {
				return nil, fmt.Errorf("__DATE_DIFF_DAYS__ arg 1: %w", err)
			}
			start, err := parseDate(params[1])
			if err != nil {
				return nil, fmt.Errorf("__DATE_DIFF_DAYS__ arg 2: %w", err)
			}
			return calendarDayNumber(end) - calendarDayNumber(start), nil
		}),
		expr.Function("DATEVALUE", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("DATEVALUE", len(params)); err != nil {
				return nil, err
			}
			t, err := parseDate(params[0])
			if err != nil {
				return nil, fmt.Errorf("DATEVALUE: %w", err)
			}
			return t.Format("2006-01-02"), nil
		}),
		expr.Function("DATETIMEVALUE", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("DATETIMEVALUE", len(params)); err != nil {
				return nil, err
			}
			if params[0] == nil {
				return nil, nil
			}
			switch v := params[0].(type) {
			case time.Time:
				// Already a DateTime (hydrated field value from existing record); pass through.
				return v, nil
			case string:
				for _, layout := range []string{
					"2006-01-02 15:04:05",
					"2006-01-02T15:04:05Z",
					time.RFC3339,
					"2006-01-02",
				} {
					if t, err := time.Parse(layout, v); err == nil {
						return t, nil
					}
				}
				return nil, nil // Salesforce: invalid string → null
			default:
				// int, float, bool, etc. → null per Salesforce semantics.
				return nil, nil
			}
		}),
		expr.Function("DATE_ADD", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("DATE_ADD", len(params)); err != nil {
				return nil, err
			}
			t, err := parseDate(params[0])
			if err != nil {
				return nil, fmt.Errorf("DATE_ADD: %w", err)
			}
			days, err := toInt(params[1])
			if err != nil {
				return nil, fmt.Errorf("DATE_ADD days must be integer")
			}
			result, valid := addDaysSQLDate(t, days)
			if !valid {
				return nil, nil
			}
			return result.Format("2006-01-02"), nil
		}),
		// --- Salesforce migration: new formula functions ---
		expr.Function("ISBLANK", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("ISBLANK", len(params)); err != nil {
				return nil, err
			}
			v := params[0]
			if v == nil {
				return true, nil
			}
			if s, ok := v.(string); ok {
				return s == "", nil
			}
			return false, nil
		}),
		expr.Function("ISNULL", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("ISNULL", len(params)); err != nil {
				return nil, err
			}
			return params[0] == nil, nil
		}),
		expr.Function("INCLUDES", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("INCLUDES", len(params)); err != nil {
				return nil, err
			}
			fieldItems := extractMultiPicklistItems(params[0])
			if len(fieldItems) == 0 {
				return false, nil
			}
			searchVal, ok := asString(params[1])
			if !ok {
				return nil, fmt.Errorf("INCLUDES arg 2 must be string")
			}
			searchVal = strings.TrimSpace(searchVal)
			if searchVal == "" {
				return false, nil
			}
			return utils.MultiPicklistContainsAllTokens(fieldItems, []string{searchVal}), nil
		}),
		expr.Function("__NCRM_MPINCLUDESALL__", func(params ...interface{}) (interface{}, error) {
			if len(params) != 2 {
				return nil, fmt.Errorf("__NCRM_MPINCLUDESALL__ requires 2 arguments")
			}
			fieldItems := extractMultiPicklistItems(params[0])
			if len(fieldItems) == 0 {
				return false, nil
			}
			searchVal, ok := asString(params[1])
			if !ok {
				return nil, fmt.Errorf("__NCRM_MPINCLUDESALL__ arg 2 must be string")
			}
			searchItems := extractMultiPicklistItems(searchVal)
			if len(searchItems) == 0 {
				return false, nil
			}
			return utils.MultiPicklistContainsAllTokens(fieldItems, searchItems), nil
		}),
		expr.Function("__NCRM_MPFIELD__", func(params ...interface{}) (interface{}, error) {
			if len(params) != 3 {
				return nil, fmt.Errorf("__NCRM_MPFIELD__ requires 3 arguments")
			}
			path, _ := params[1].(string)
			options := multiPicklistOptionsForPath(params[2], path)
			if len(options) == 0 {
				return params[0], nil
			}
			return schemaAwareMultiPicklistValue{value: params[0], options: options}, nil
		}),
		expr.Function("TEXT", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("TEXT", len(params)); err != nil {
				return nil, err
			}
			if params[0] == nil {
				return "", nil
			}
			return fmt.Sprintf("%v", params[0]), nil
		}),
		expr.Function("CASE", func(params ...interface{}) (interface{}, error) {
			// Arity >=4 and even (value/result pairs + default), enforced by the
			// spec catalog's CASE bounds + even-arity predicate.
			if err := e.checkArity("CASE", len(params)); err != nil {
				return nil, err
			}
			expression := params[0]
			for i := 1; i < len(params)-1; i += 2 {
				if fmt.Sprintf("%v", expression) == fmt.Sprintf("%v", params[i]) {
					return params[i+1], nil
				}
			}
			return params[len(params)-1], nil
		}),
		expr.Function("LEFT", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("LEFT", len(params)); err != nil {
				return nil, err
			}
			s, ok := params[0].(string)
			if !ok {
				if params[0] == nil {
					return "", nil
				}
				return nil, fmt.Errorf("LEFT arg 1 must be string")
			}
			n, err := toInt(params[1])
			if err != nil {
				return nil, fmt.Errorf("LEFT arg 2 must be integer")
			}
			runes := []rune(s)
			if n > len(runes) {
				n = len(runes)
			}
			if n < 0 {
				n = 0
			}
			return string(runes[:n]), nil
		}),
		expr.Function("RIGHT", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("RIGHT", len(params)); err != nil {
				return nil, err
			}
			s, ok := params[0].(string)
			if !ok {
				if params[0] == nil {
					return "", nil
				}
				return nil, fmt.Errorf("RIGHT arg 1 must be string")
			}
			n, err := toInt(params[1])
			if err != nil {
				return nil, fmt.Errorf("RIGHT arg 2 must be integer")
			}
			runes := []rune(s)
			if n > len(runes) {
				n = len(runes)
			}
			if n < 0 {
				n = 0
			}
			return string(runes[len(runes)-n:]), nil
		}),
		expr.Function("MID", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("MID", len(params)); err != nil {
				return nil, err
			}
			s, ok := params[0].(string)
			if !ok {
				if params[0] == nil {
					return "", nil
				}
				return nil, fmt.Errorf("MID arg 1 must be string")
			}
			start, err := toInt(params[1])
			if err != nil {
				return nil, fmt.Errorf("MID arg 2 must be integer")
			}
			length, err := toInt(params[2])
			if err != nil {
				return nil, fmt.Errorf("MID arg 3 must be integer")
			}
			runes := []rune(s)
			// Salesforce MID is 1-based: convert to 0-based index
			start = start - 1
			if start < 0 {
				start = 0
			}
			if start > len(runes) {
				return "", nil
			}
			end := start + length
			if end > len(runes) {
				end = len(runes)
			}
			return string(runes[start:end]), nil
		}),
		expr.Function("LPAD", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("LPAD", len(params)); err != nil {
				return nil, err
			}
			text := ""
			if params[0] != nil {
				s, ok := params[0].(string)
				if !ok {
					return nil, fmt.Errorf("LPAD arg 1 must be string")
				}
				text = s
			}
			if params[1] == nil {
				return nil, nil
			}
			n, err := toInt(params[1])
			if err != nil {
				return nil, fmt.Errorf("LPAD arg 2 must be integer")
			}
			if params[2] == nil {
				return nil, nil
			}
			pad, ok := params[2].(string)
			if !ok {
				return nil, fmt.Errorf("LPAD arg 3 must be string")
			}
			if pad == "" {
				return nil, nil
			}
			if n <= 0 {
				return "", nil
			}
			runes := []rune(text)
			if len(runes) >= n {
				// Truncate: keep leftmost n runes (Salesforce LPAD truncates from the right)
				return string(runes[:n]), nil
			}
			needed := n - len(runes)
			padRunes := []rune(strings.Repeat(pad, needed/len([]rune(pad))+1))
			return string(padRunes[:needed]) + text, nil
		}),
		expr.Function("RPAD", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("RPAD", len(params)); err != nil {
				return nil, err
			}
			text := ""
			if params[0] != nil {
				s, ok := params[0].(string)
				if !ok {
					return nil, fmt.Errorf("RPAD arg 1 must be string")
				}
				text = s
			}
			if params[1] == nil {
				return nil, nil
			}
			n, err := toInt(params[1])
			if err != nil {
				return nil, fmt.Errorf("RPAD arg 2 must be integer")
			}
			if params[2] == nil {
				return nil, nil
			}
			pad, ok := params[2].(string)
			if !ok {
				return nil, fmt.Errorf("RPAD arg 3 must be string")
			}
			if pad == "" {
				return nil, nil
			}
			if n <= 0 {
				return "", nil
			}
			runes := []rune(text)
			if len(runes) >= n {
				// Truncate: keep leftmost n runes (right-truncate)
				return string(runes[:n]), nil
			}
			needed := n - len(runes)
			padRunes := []rune(strings.Repeat(pad, needed/len([]rune(pad))+1))
			return text + string(padRunes[:needed]), nil
		}),
		expr.Function("CONTAINS", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("CONTAINS", len(params)); err != nil {
				return nil, err
			}
			s, ok1 := params[0].(string)
			substr, ok2 := params[1].(string)
			if !ok1 || !ok2 {
				if params[0] == nil || params[1] == nil {
					return false, nil
				}
				return nil, fmt.Errorf("CONTAINS arguments must be strings")
			}
			return strings.Contains(s, substr), nil
		}),
		expr.Function("YEAR", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("YEAR", len(params)); err != nil {
				return nil, err
			}
			t, err := parseDate(params[0])
			if err != nil {
				return nil, fmt.Errorf("YEAR: %w", err)
			}
			return t.Year(), nil
		}),
		expr.Function("MONTH", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("MONTH", len(params)); err != nil {
				return nil, err
			}
			t, err := parseDate(params[0])
			if err != nil {
				return nil, fmt.Errorf("MONTH: %w", err)
			}
			return int(t.Month()), nil
		}),
		expr.Function("DAY", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("DAY", len(params)); err != nil {
				return nil, err
			}
			t, err := parseDate(params[0])
			if err != nil {
				return nil, fmt.Errorf("DAY: %w", err)
			}
			return t.Day(), nil
		}),
		expr.Function("CEILING", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("CEILING", len(params)); err != nil {
				return nil, err
			}
			val, err := toFloat(params[0])
			if err != nil {
				return nil, fmt.Errorf("CEILING argument must be number")
			}
			return math.Ceil(val), nil
		}),
		expr.Function("ABS", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("ABS", len(params)); err != nil {
				return nil, err
			}
			val, err := toFloat(params[0])
			if err != nil {
				return nil, fmt.Errorf("ABS argument must be number")
			}
			return math.Abs(val), nil
		}),
		expr.Function("MOD", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("MOD", len(params)); err != nil {
				return nil, err
			}
			a, err := toFloat(params[0])
			if err != nil {
				return nil, fmt.Errorf("MOD arg 1 must be number")
			}
			b, err := toFloat(params[1])
			if err != nil {
				return nil, fmt.Errorf("MOD arg 2 must be number")
			}
			if b == 0 {
				return nil, fmt.Errorf("MOD division by zero")
			}
			return math.Mod(a, b), nil
		}),
		expr.Function("NULLVALUE", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("NULLVALUE", len(params)); err != nil {
				return nil, err
			}
			if params[0] == nil {
				return params[1], nil
			}
			if s, ok := params[0].(string); ok && s == "" {
				return params[1], nil
			}
			return params[0], nil
		}),
		expr.Function("BLANKVALUE", func(params ...interface{}) (interface{}, error) {
			if err := e.checkArity("BLANKVALUE", len(params)); err != nil {
				return nil, err
			}
			if params[0] == nil {
				return params[1], nil
			}
			if s, ok := params[0].(string); ok && s == "" {
				return params[1], nil
			}
			return params[0], nil
		}),
	}

	// Add custom functions
	for name, fn := range e.functions {
		options = append(options, expr.Function(name, fn))
	}

	// __SFPLUS__: Salesforce-semantics binary add/concat. Registered here so the
	// AST patcher (Fix C) can rewrite all `+` BinaryNodes to call this function,
	// restoring the text-vs-numeric null-coercion distinction that is lost when
	// `replaceFormulaOperators` converts `&` (text concat) to `+`. Issue #468.
	//
	// Runtime type inspection decides context:
	//   - Either non-nil operand is a number  → numeric; propagate nil, add numbers.
	//   - Either non-nil operand is a string  → text; coerce nil → "", concatenate.
	//   - Both nil                            → nil (conservative; no type hint).
	options = append(options, expr.Function("__SFPLUS__", func(params ...interface{}) (interface{}, error) {
		if len(params) != 2 {
			return nil, fmt.Errorf("__SFPLUS__ requires 2 arguments")
		}
		left, right := params[0], params[1]
		isNum := func(v interface{}) bool {
			if v == nil {
				return false
			}
			switch v.(type) {
			case int, int32, int64, float32, float64:
				return true
			}
			return false
		}
		if isNum(left) || isNum(right) {
			// Numeric context: propagate nil.
			if left == nil || right == nil {
				return nil, nil
			}
			l, err := toFloat(left)
			if err != nil {
				return nil, fmt.Errorf("__SFPLUS__ left operand: %w", err)
			}
			r, err := toFloat(right)
			if err != nil {
				return nil, fmt.Errorf("__SFPLUS__ right operand: %w", err)
			}
			return l + r, nil
		}
		// Text context: coerce nil → "".
		if left == nil && right == nil {
			return nil, nil // both nil, no type hint; conservative
		}
		coerce := func(v interface{}) string {
			if v == nil {
				return ""
			}
			if s, ok := v.(string); ok {
				return s
			}
			return fmt.Sprintf("%v", v)
		}
		return coerce(left) + coerce(right), nil
	}))

	options = append(options, expr.Function("__NCRM_SQLDIV__", func(params ...interface{}) (interface{}, error) {
		// SQL-parity division for blank-as-zero formula-field evaluation
		// (#4152). Reachable only through sqlNullDivisionPatcher rewrites —
		// authored formulas cannot name double-underscore identifiers
		// (rejectInternalLoweringArtifacts). Zero divisor or non-finite
		// quotient -> nil, mirroring the SQL projection where the same
		// division renders NULL in a SELECT. Nil operands keep erroring so a
		// deliberately-unloaded operand (computed-elsewhere skip) still fails
		// the whole evaluation closed.
		if len(params) != 2 {
			return nil, fmt.Errorf("__NCRM_SQLDIV__ requires 2 arguments, got %d", len(params))
		}
		if params[0] == nil || params[1] == nil {
			return nil, fmt.Errorf("__NCRM_SQLDIV__: nil operand")
		}
		left, err := toFloat(params[0])
		if err != nil {
			return nil, err
		}
		right, err := toFloat(params[1])
		if err != nil {
			return nil, err
		}
		if right == 0 {
			return nil, nil
		}
		quotient := left / right
		if math.IsInf(quotient, 0) || math.IsNaN(quotient) {
			return nil, nil
		}
		return quotient, nil
	}))

	// AST patchers: Fix A (lazy IF) + Fix B (null-propagating chains) + Fix C (text-concat null-coerce).
	// Two passes: first collects inner-member markers, second applies rewrites.
	marker := newInnerMemberMarker()
	options = append(options,
		expr.Patch(marker),
		expr.Patch(newSFFormulaPatcher(marker, e.functionRegistry)),
	)
	if sqlNullDivision {
		options = append(options, expr.Patch(&sqlNullDivisionPatcher{}))
	}

	// Compile
	program, err := expr.Compile(expression, options...)
	if err != nil {
		return nil, err
	}

	e.programCache[cacheKey] = program
	return program, nil
}

// Validation helper
func (e *Engine) Validate(expression string, env map[string]interface{}) error {
	env = ensureExpressionEnv(env)
	_, err := e.getProgram(expression, env, false)
	return err
}

func toFloat(v interface{}) (float64, error) {
	switch val := v.(type) {
	case float64:
		return val, nil
	case int:
		return float64(val), nil
	case int32:
		return float64(val), nil
	case int64:
		return float64(val), nil
	case float32:
		return float64(val), nil
	case string:
		var f float64
		_, err := fmt.Sscanf(val, "%f", &f)
		return f, err
	}
	return 0, fmt.Errorf("cannot convert %T to float", v)
}

func toInt(v interface{}) (int, error) {
	switch val := v.(type) {
	case int:
		return val, nil
	case float64:
		return int(val), nil
	case int64:
		return int(val), nil
	case float32:
		return int(val), nil
	case string:
		var i int
		_, err := fmt.Sscanf(val, "%d", &i)
		return i, err
	}
	return 0, fmt.Errorf("cannot convert %T to int", v)
}

func parseDate(v interface{}) (time.Time, error) {
	if v == nil {
		return time.Time{}, fmt.Errorf("date is nil")
	}
	if t, ok := v.(time.Time); ok {
		return t, nil
	}
	// Handle days-since-epoch from TODAY() arithmetic
	if days, ok := v.(int); ok {
		return epoch.AddDate(0, 0, days), nil
	}
	if days, ok := v.(float64); ok {
		return epoch.AddDate(0, 0, int(days)), nil
	}
	s, ok := v.(string)
	if !ok {
		return time.Time{}, fmt.Errorf("date must be string or time.Time, got %T", v)
	}
	for _, layout := range []string{"2006-01-02", "2006-01-02 15:04:05", "2006-01-02T15:04:05Z", time.RFC3339} {
		if t, err := time.Parse(layout, s); err == nil {
			return t, nil
		}
	}
	return time.Time{}, fmt.Errorf("cannot parse date: %s", s)
}

// daysInMonth returns the number of days in the given year/month.
func daysInMonth(year int, month time.Month) int {
	// Day 0 of next month = last day of this month.
	return time.Date(year, month+1, 0, 0, 0, 0, 0, time.UTC).Day()
}

// addMonthsSF implements Salesforce month-addition semantics:
//   - If the input day is the last day of its month, the result is anchored to
//     the last day of the target month.
//   - Otherwise, the result day is min(inputDay, lastDayOfTargetMonth).
//
// Example: 2019-02-28 +1mo = 2019-03-31 (Feb 28 is last day → last day of Mar).
// Example: 2024-01-31 +1mo = 2024-02-29 (day 31 clamped to Feb's last day).
const (
	sqlDateMinYear = 1
	sqlDateMaxYear = 9999
)

func isSQLDate(t time.Time) bool {
	return t.Year() >= sqlDateMinYear && t.Year() <= sqlDateMaxYear
}

func sqlDateTargetYearMonth(t time.Time, months int) (int, time.Month, bool) {
	if !isSQLDate(t) {
		return 0, 0, false
	}
	sourceMonth := int64(t.Year())*12 + int64(t.Month()-1)
	minMonth := int64(sqlDateMinYear * 12)
	maxMonth := int64(sqlDateMaxYear*12 + 11)
	delta := int64(months)
	// Compare before adding so even a machine-sized extreme month count cannot
	// overflow the month index and wrap back into the SQL DATE range.
	if delta < minMonth-sourceMonth || delta > maxMonth-sourceMonth {
		return 0, 0, false
	}
	targetMonth := sourceMonth + delta
	return int(targetMonth / 12), time.Month(targetMonth%12 + 1), true
}

func addMonthsSF(t time.Time, months int) (time.Time, bool) {
	y, m, d := t.Date()
	lastDayInput := daysInMonth(y, m)
	isLastDay := d == lastDayInput

	targetYear, targetMonth, valid := sqlDateTargetYearMonth(t, months)
	if !valid {
		return time.Time{}, false
	}

	lastDayTarget := daysInMonth(targetYear, targetMonth)
	targetDay := d
	if isLastDay {
		targetDay = lastDayTarget
	} else if targetDay > lastDayTarget {
		targetDay = lastDayTarget
	}

	return time.Date(targetYear, targetMonth, targetDay, t.Hour(), t.Minute(), t.Second(), t.Nanosecond(), t.Location()), true
}

func addMonthsClamp(t time.Time, months int) (time.Time, bool) {
	_, _, d := t.Date()
	targetYear, targetMonth, valid := sqlDateTargetYearMonth(t, months)
	if !valid {
		return time.Time{}, false
	}
	if last := daysInMonth(targetYear, targetMonth); d > last {
		d = last
	}
	return time.Date(targetYear, targetMonth, d, t.Hour(), t.Minute(), t.Second(), t.Nanosecond(), t.Location()), true
}

func addDaysSQLDate(t time.Time, days int) (time.Time, bool) {
	if !isSQLDate(t) {
		return time.Time{}, false
	}
	sourceDay := calendarDayNumber(t)
	delta := int64(days)
	if delta < sqlDateMinDay-sourceDay || delta > sqlDateMaxDay-sourceDay {
		return time.Time{}, false
	}
	return t.AddDate(0, 0, days), true
}

// calendarDayNumber returns a timezone-independent civil-day ordinal. Using
// Unix seconds instead of time.Time.Sub avoids time.Duration overflow for
// otherwise valid, widely separated Date values.
func calendarDayNumber(t time.Time) int64 {
	midnightUTC := time.Date(t.Year(), t.Month(), t.Day(), 0, 0, 0, 0, time.UTC)
	return midnightUTC.Unix() / (24 * 60 * 60)
}

var (
	sqlDateMinDay = calendarDayNumber(time.Date(sqlDateMinYear, time.January, 1, 0, 0, 0, 0, time.UTC))
	sqlDateMaxDay = calendarDayNumber(time.Date(sqlDateMaxYear, time.December, 31, 0, 0, 0, 0, time.UTC))
)

func asString(v interface{}) (string, bool) {
	if v == nil {
		return "", true
	}
	s, ok := v.(string)
	return s, ok
}

// epoch is the reference point for DaysSinceEpoch / DaysToDateStr.
var epoch = time.Date(2000, 1, 1, 0, 0, 0, 0, time.UTC)

// DaysSinceEpoch converts a time.Time to an integer day count so
// TODAY()+30 works as native arithmetic in the expr engine.
func DaysSinceEpoch(t time.Time) int {
	return int(t.Truncate(24*time.Hour).Sub(epoch).Hours() / 24)
}

// DaysToDateStr converts a DaysSinceEpoch int back to "2006-01-02".
func DaysToDateStr(days int) string {
	return epoch.AddDate(0, 0, days).Format("2006-01-02")
}

func truthy(v interface{}) bool {
	switch val := v.(type) {
	case nil:
		return false
	case bool:
		return val
	case string:
		return val != "" && strings.ToLower(val) != "false" && val != "0"
	case int:
		return val != 0
	case int64:
		return val != 0
	case float64:
		return val != 0
	default:
		return true
	}
}

type schemaAwareMultiPicklistValue struct {
	value   interface{}
	options []string
}

func extractMultiPicklistItems(v interface{}) []string {
	switch tv := v.(type) {
	case nil:
		return nil
	case schemaAwareMultiPicklistValue:
		return extractSchemaAwareMultiPicklistItems(tv.value, tv.options)
	case []byte:
		return extractMultiPicklistItems(string(tv))
	case string:
		s := strings.TrimSpace(tv)
		if s == "" {
			return nil
		}
		return dedupeTrimmedStrings(strings.Split(s, ";"))
	case []string:
		return dedupeTrimmedStrings(tv)
	case []interface{}:
		out := make([]string, 0, len(tv))
		for _, item := range tv {
			if s, ok := item.(string); ok {
				out = append(out, s)
				continue
			}
			if item != nil {
				out = append(out, fmt.Sprintf("%v", item))
			}
		}
		return dedupeTrimmedStrings(out)
	default:
		return dedupeTrimmedStrings([]string{fmt.Sprintf("%v", v)})
	}
}

func extractSchemaAwareMultiPicklistItems(v interface{}, options []string) []string {
	optionSet := multiPicklistOptionSet(options)
	switch tv := v.(type) {
	case nil:
		return nil
	case schemaAwareMultiPicklistValue:
		return extractSchemaAwareMultiPicklistItems(tv.value, tv.options)
	case []byte:
		return extractAuthoritativeMultiPicklistJSON(string(tv), optionSet)
	case string:
		s := strings.TrimSpace(tv)
		if s == "" {
			return nil
		}
		if _, declared := optionSet[s]; declared {
			return []string{s}
		}
		if strings.HasPrefix(s, "[") {
			if items, ok := multiPicklistItemsFromJSONString(s, optionSet); ok {
				return dedupeTrimmedStrings(items)
			}
		}
		return dedupeTrimmedStrings(strings.Split(s, ";"))
	case []string:
		return dedupeTrimmedStrings(multiPicklistItemsFromStrings(tv, optionSet))
	case []interface{}:
		out := make([]string, 0, len(tv))
		for _, item := range tv {
			switch typed := item.(type) {
			case nil:
				continue
			case string:
				out = append(out, multiPicklistItemsFromStrings([]string{typed}, optionSet)...)
			case []string:
				out = append(out, multiPicklistItemsFromStrings(typed, optionSet)...)
			case []interface{}:
				out = append(out, extractSchemaAwareMultiPicklistItems(typed, options)...)
			default:
				out = append(out, fmt.Sprintf("%v", item))
			}
		}
		return dedupeTrimmedStrings(out)
	default:
		return extractSchemaAwareMultiPicklistItems(fmt.Sprintf("%v", v), options)
	}
}

func extractAuthoritativeMultiPicklistJSON(value string, optionSet map[string]struct{}) []string {
	s := strings.TrimSpace(value)
	if s == "" {
		return nil
	}
	if strings.HasPrefix(s, "[") {
		if items, ok := multiPicklistItemsFromJSONString(s, optionSet); ok {
			return dedupeTrimmedStrings(items)
		}
	}
	return dedupeTrimmedStrings(strings.Split(s, ";"))
}

func multiPicklistItemsFromStrings(values []string, optionSet map[string]struct{}) []string {
	out := make([]string, 0, len(values))
	for _, value := range values {
		trimmed := strings.TrimSpace(value)
		if trimmed == "" {
			continue
		}
		if _, declared := optionSet[trimmed]; declared {
			out = append(out, value)
			continue
		}
		if strings.HasPrefix(trimmed, "[") {
			if nested, ok := multiPicklistItemsFromJSONString(trimmed, optionSet); ok {
				out = append(out, nested...)
				continue
			}
		}
		out = append(out, value)
	}
	return out
}

func multiPicklistItemsFromJSONString(value string, optionSet map[string]struct{}) ([]string, bool) {
	var arr []interface{}
	if err := json.Unmarshal([]byte(value), &arr); err != nil {
		return nil, false
	}
	return multiPicklistItemsFromJSONArray(arr, optionSet), true
}

func multiPicklistItemsFromJSONArray(arr []interface{}, optionSet map[string]struct{}) []string {
	out := make([]string, 0, len(arr))
	for _, item := range arr {
		s, ok := item.(string)
		if !ok {
			if item != nil {
				out = append(out, fmt.Sprintf("%v", item))
			}
			continue
		}
		trimmed := strings.TrimSpace(s)
		if trimmed == "" {
			continue
		}
		if _, declared := optionSet[trimmed]; declared {
			out = append(out, s)
			continue
		}
		if strings.HasPrefix(trimmed, "[") {
			if nested, ok := multiPicklistItemsFromJSONString(trimmed, optionSet); ok {
				out = append(out, nested...)
				continue
			}
		}
		out = append(out, s)
	}
	return out
}

func multiPicklistOptionSet(options []string) map[string]struct{} {
	if len(options) == 0 {
		return nil
	}
	out := make(map[string]struct{}, len(options))
	for _, option := range options {
		if option == "" {
			continue
		}
		out[option] = struct{}{}
	}
	return out
}

func multiPicklistOptionsForPath(raw interface{}, path string) []string {
	if path == "" || raw == nil {
		return nil
	}
	switch fields := raw.(type) {
	case map[string][]string:
		return fields[path]
	case map[string]interface{}:
		return optionsFromRaw(fields[path])
	default:
		return nil
	}
}

func optionsFromRaw(raw interface{}) []string {
	switch v := raw.(type) {
	case nil:
		return nil
	case []string:
		return v
	case []interface{}:
		out := make([]string, 0, len(v))
		for _, item := range v {
			if item == nil {
				continue
			}
			out = append(out, fmt.Sprintf("%v", item))
		}
		return out
	case map[string]interface{}:
		if opts, ok := v["options"]; ok {
			return optionsFromRaw(opts)
		}
		return nil
	default:
		return nil
	}
}

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