package expression

import (
	"fmt"
	"strings"

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

	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/kernel/pkg/formula/spec"
)

// SQLWalker converts an expr AST to SQL
type SQLWalker struct {
	builder          strings.Builder
	args             []interface{}
	err              error
	mapper           func(string) string
	functionLowerer  SQLFunctionLowerer
	boundIdentifiers map[string]SQLFragment
	allowMemberPaths bool
}

// SQLFragment is a compiled SQL expression plus its placeholder arguments.
type SQLFragment struct {
	SQL  string
	Args []interface{}
}

// SQLFunctionLowerer lets higher layers provide SQL lowering for functions
// whose vocabulary is not owned by the generic expression walker.
type SQLFunctionLowerer func(SQLFunctionCallContext) (SQLFragment, bool, error)

// SQLFunctionCallContext is the function-call data exposed to a package-owned
// lowerer. CompileArg compiles one original argument through the same walker
// options and returns its SQL fragment.
type SQLFunctionCallContext struct {
	Name       string
	Args       []ast.Node
	CompileArg func(ast.Node) (SQLFragment, error)
}

// SQLOptions configures expression-to-SQL lowering.
type SQLOptions struct {
	Mapper           func(string) string
	FunctionLowerer  SQLFunctionLowerer
	BoundIdentifiers map[string]SQLFragment
	AllowMemberPaths bool
}

// isNilNode checks if a node represents a null/nil value
// In expr-lang, null can be either a NilNode or an IdentifierNode with value "null", "nil", or "NULL"
func isNilNode(node ast.Node) bool {
	if _, ok := node.(*ast.NilNode); ok {
		return true
	}
	if id, ok := node.(*ast.IdentifierNode); ok {
		val := strings.ToLower(id.Value)
		return val == "null" || val == "nil"
	}
	return false
}

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

// ToSQLWithMapper converts an expression string to a SQL WHERE clause with identifier mapping
func ToSQLWithMapper(expression string, mapper func(string) string) (string, []interface{}, error) {
	return ToSQLWithOptions(expression, SQLOptions{Mapper: mapper})
}

// ToSQLWithOptions converts an expression string to SQL with extension points
// for package-owned function lowering.
func ToSQLWithOptions(expression string, opts SQLOptions) (string, []interface{}, error) {
	// Parse the expression using expr parser directly to get AST
	// We use standard parser.Parse because we just need the tree structure
	tree, err := parser.Parse(expression)
	if err != nil {
		return "", nil, apperrors.NewValidationError("expression", fmt.Sprintf("failed to parse expression: %v", err))
	}

	walker := &SQLWalker{
		args:             make([]interface{}, 0),
		mapper:           opts.Mapper,
		functionLowerer:  opts.FunctionLowerer,
		boundIdentifiers: cloneSQLFragments(opts.BoundIdentifiers),
		allowMemberPaths: opts.AllowMemberPaths,
	}

	// Start manual traversal
	walker.walk(&tree.Node)

	if walker.err != nil {
		return "", nil, walker.err
	}

	return walker.builder.String(), walker.args, nil
}

// Visit implements ast.Visitor
// Note: Based on probe test, signature must be Visit(node *ast.Node)
func (w *SQLWalker) walk(node *ast.Node) {
	w.walkWithDepth(node, 0)
}

func (w *SQLWalker) walkWithDepth(node *ast.Node, depth int) {
	const MaxRecursionDepth = 100
	if depth > MaxRecursionDepth {
		w.err = apperrors.NewValidationError("expression", fmt.Sprintf("formula complexity exceeded max depth (%d)", MaxRecursionDepth))
		return
	}
	if w.err != nil {
		return
	}
	if node == nil || *node == nil {
		return
	}

	n := *node

	switch v := n.(type) {
	case *ast.BinaryNode:
		w.visitBinary(v, depth+1)
	case *ast.IdentifierNode:
		if fragment, ok := w.lookupBoundIdentifier(v.Value); ok {
			w.writeFragment(fragment)
			return
		}
		val := v.Value
		if w.mapper != nil {
			val = w.mapper(val)
		}
		w.builder.WriteString(val)
	case *ast.MemberNode:
		if !w.allowMemberPaths {
			w.err = apperrors.NewValidationError("expression", "unsupported member path")
			return
		}
		path, ok := memberPath(v)
		if !ok {
			w.err = apperrors.NewValidationError("expression", fmt.Sprintf("unsupported member path: %T", n))
			return
		}
		if fragment, ok := w.lookupBoundIdentifier(path); ok {
			w.writeFragment(fragment)
			return
		}
		val := path
		if w.mapper != nil {
			val = w.mapper(val)
		}
		w.builder.WriteString(val)
	case *ast.IntegerNode:
		w.builder.WriteString("?")
		w.args = append(w.args, v.Value)
	case *ast.FloatNode:
		w.builder.WriteString("?")
		w.args = append(w.args, v.Value)
	case *ast.StringNode:
		w.builder.WriteString("?")
		w.args = append(w.args, v.Value)
	case *ast.BoolNode:
		w.builder.WriteString("?")
		w.args = append(w.args, v.Value)
	case *ast.NilNode:
		w.builder.WriteString("NULL")
	case *ast.UnaryNode:
		if v.Operator == "not" || v.Operator == "!" {
			w.builder.WriteString("NOT (")
			w.walkWithDepth(&v.Node, depth+1)
			w.builder.WriteString(")")
		} else {
			w.err = fmt.Errorf("unsupported unary operator: %s", v.Operator)
		}
	case *ast.CallNode:
		w.visitCall(v, depth+1)
	default:
		w.err = apperrors.NewValidationError("expression", fmt.Sprintf("unsupported node type: %T", n))
	}
}

func memberPath(node ast.Node) (string, bool) {
	switch n := node.(type) {
	case *ast.IdentifierNode:
		if strings.TrimSpace(n.Value) == "" {
			return "", false
		}
		return n.Value, true
	case *ast.MemberNode:
		base, ok := memberPath(n.Node)
		if !ok || base == "" {
			return "", false
		}
		prop, ok := memberNodeProperty(n.Property)
		if !ok || strings.TrimSpace(prop) == "" {
			return "", false
		}
		return base + "." + prop, true
	default:
		return "", false
	}
}

func (w *SQLWalker) visitBinary(node *ast.BinaryNode, depth int) {
	// Check for null comparisons which need special SQL syntax
	// Note: In expr-lang, null can be either NilNode or IdentifierNode with value "null"/"nil"
	rightIsNil := isNilNode(node.Right)
	leftIsNil := isNilNode(node.Left)

	if rightIsNil || leftIsNil {
		// Handle IS NULL / IS NOT NULL
		var fieldNode ast.Node
		if rightIsNil {
			fieldNode = node.Left
		} else {
			fieldNode = node.Right
		}

		w.builder.WriteString("(")
		w.walkWithDepth(&fieldNode, depth+1)
		switch node.Operator {
		case "==":
			w.builder.WriteString(" IS NULL")
		case "!=":
			w.builder.WriteString(" IS NOT NULL")
		default:
			w.err = apperrors.NewValidationError("expression", fmt.Sprintf("unsupported operator for null comparison: %s", node.Operator))
		}
		w.builder.WriteString(")")
		return
	}

	w.builder.WriteString("(")

	// Left
	w.walkWithDepth(&node.Left, depth+1)

	w.builder.WriteString(" ")

	// Operator mapping
	switch node.Operator {
	case "==":
		w.builder.WriteString("=")
	case "&&":
		w.builder.WriteString("AND")
	case "||":
		w.builder.WriteString("OR")

	case "in":
		if arrNode, ok := node.Right.(*ast.ArrayNode); ok {
			w.builder.WriteString("IN (")
			for i, elem := range arrNode.Nodes {
				if i > 0 {
					w.builder.WriteString(", ")
				}
				elemNode := elem
				w.walkWithDepth(&elemNode, depth+1)
			}
			w.builder.WriteString("))")
			return
		}
		w.builder.WriteString("LIKE ")
		if strArg, ok := node.Right.(*ast.StringNode); ok {
			w.builder.WriteString("?")
			w.args = append(w.args, "%"+strArg.Value+"%")
		} else {
			w.err = apperrors.NewValidationError("expression", "in operator requires string literal or array on right side")
		}
		w.builder.WriteString(")")
		return

	case "contains":
		w.builder.WriteString("LIKE ")
		if strArg, ok := node.Right.(*ast.StringNode); ok {
			w.builder.WriteString("?")
			w.args = append(w.args, "%"+strArg.Value+"%")
		} else {
			w.err = apperrors.NewValidationError("expression", "contains operator requires string literal on right side")
		}
		w.builder.WriteString(")")
		return

	case "startsWith":
		// name startsWith 'text' → name LIKE 'text%'
		w.builder.WriteString("LIKE ")
		if strArg, ok := node.Right.(*ast.StringNode); ok {
			w.builder.WriteString("?")
			w.args = append(w.args, strArg.Value+"%")
		} else {
			w.err = apperrors.NewValidationError("expression", "startsWith operator requires string literal on right side")
		}
		w.builder.WriteString(")")
		return

	case "endsWith":
		// name endsWith 'text' → name LIKE '%text'
		w.builder.WriteString("LIKE ")
		if strArg, ok := node.Right.(*ast.StringNode); ok {
			w.builder.WriteString("?")
			w.args = append(w.args, "%"+strArg.Value)
		} else {
			w.err = apperrors.NewValidationError("expression", "endsWith operator requires string literal on right side")
		}
		w.builder.WriteString(")")
		return

	case "matches":
		// name matches 'regex' → name REGEXP 'regex'
		w.builder.WriteString("REGEXP ")
		if strArg, ok := node.Right.(*ast.StringNode); ok {
			w.builder.WriteString("?")
			w.args = append(w.args, strArg.Value)
		} else {
			w.err = apperrors.NewValidationError("expression", "matches operator requires string literal on right side")
		}
		w.builder.WriteString(")")
		return

	default:
		w.builder.WriteString(node.Operator)
	}

	w.builder.WriteString(" ")

	// Right
	w.walkWithDepth(&node.Right, depth+1)

	w.builder.WriteString(")")
}

func (w *SQLWalker) visitCall(node *ast.CallNode, depth int) {
	// Extract function name from Callee
	callee, ok := node.Callee.(*ast.IdentifierNode)
	if !ok {
		w.err = apperrors.NewValidationError("expression", fmt.Sprintf("unsupported callee type: %T", node.Callee))
		return
	}

	fnName := strings.ToUpper(callee.Value)
	if w.functionLowerer != nil {
		fragment, handled, err := w.functionLowerer(SQLFunctionCallContext{
			Name: fnName,
			Args: append([]ast.Node(nil), node.Arguments...),
			CompileArg: func(arg ast.Node) (SQLFragment, error) {
				return w.compileFragment(arg, depth+1)
			},
		})
		if err != nil {
			w.err = err
			return
		}
		if handled {
			w.writeFragment(fragment)
			return
		}
	}

	if err := checkCatalogFunctionArity(fnName, len(node.Arguments)); err != nil {
		w.err = err
		return
	}

	switch fnName {
	case "UPPER":
		w.builder.WriteString("UPPER(")
		w.walkArgs(node.Arguments, depth+1)
		w.builder.WriteString(")")

	case "LOWER":
		w.builder.WriteString("LOWER(")
		w.walkArgs(node.Arguments, depth+1)
		w.builder.WriteString(")")

	case "LEN":
		w.builder.WriteString("CHAR_LENGTH(")
		w.walkArgs(node.Arguments, depth+1)
		w.builder.WriteString(")")

	case "IF":
		// IF(cond, true, false) -> IF(cond_sql, true_sql, false_sql)
		w.builder.WriteString("IF(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(", ")
		arg1 := node.Arguments[1]
		w.walkWithDepth(&arg1, depth+1)
		w.builder.WriteString(", ")
		arg2 := node.Arguments[2]
		w.walkWithDepth(&arg2, depth+1)
		w.builder.WriteString(")")

	case "TODAY":
		// TODAY() -> CURDATE()
		w.builder.WriteString("CURDATE()")

	case "NOW":
		// NOW() -> NOW()
		w.builder.WriteString("NOW()")

	case "DATE_ADD":
		// The catalog declares Date even when the input is DateTime. This walker
		// has no schema types, so normalize unconditionally to keep SQL and the
		// date-only runtime evaluator byte-compatible.
		w.builder.WriteString("DATE(DATE_ADD(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(", INTERVAL ")
		arg1 := node.Arguments[1]
		w.walkWithDepth(&arg1, depth+1)
		w.builder.WriteString(" DAY))")

	case "__DATE_ADD_MONTHS__":
		// __DATE_ADD_MONTHS__(date, months) — SF-clamp last-day anchoring.
		// Emit the same SF-clamp CASE as the DSL compiler.
		w.builder.WriteString("DATE(CASE WHEN DAY(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(") = DAY(LAST_DAY(")
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(")) THEN LAST_DAY(DATE_ADD(")
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(", INTERVAL ")
		arg1 := node.Arguments[1]
		w.walkWithDepth(&arg1, depth+1)
		w.builder.WriteString(" MONTH)) ELSE DATE_ADD(")
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(", INTERVAL ")
		w.walkWithDepth(&arg1, depth+1)
		w.builder.WriteString(" MONTH) END)")

	case "__DATE_ADD_MONTHS_TARGET_CLAMP__":
		w.builder.WriteString("DATE(DATE_ADD(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(", INTERVAL ")
		arg1 := node.Arguments[1]
		w.walkWithDepth(&arg1, depth+1)
		w.builder.WriteString(" MONTH))")

	case "__DATE_DIFF_DAYS__":
		w.builder.WriteString("DATEDIFF(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(", ")
		arg1 := node.Arguments[1]
		w.walkWithDepth(&arg1, depth+1)
		w.builder.WriteString(")")

	case "CONTAINS":
		// CONTAINS(field, 'text') -> field LIKE '%text%'
		arg0 := node.Arguments[0]
		w.walk(&arg0)
		w.builder.WriteString(" LIKE ")
		// Get the string value and wrap with %
		strArg, ok := node.Arguments[1].(*ast.StringNode)
		if !ok {
			w.err = apperrors.NewValidationError("expression", "CONTAINS second argument must be a string")
			return
		}
		w.builder.WriteString("?")
		w.args = append(w.args, "%"+strArg.Value+"%")

	case "STARTS_WITH":
		// STARTS_WITH(field, 'text') -> field LIKE 'text%'
		if len(node.Arguments) != 2 {
			w.err = apperrors.NewValidationError("expression", "STARTS_WITH requires 2 arguments")
			return
		}
		arg0 := node.Arguments[0]
		w.walk(&arg0)
		w.builder.WriteString(" LIKE ")
		strArg, ok := node.Arguments[1].(*ast.StringNode)
		if !ok {
			w.err = apperrors.NewValidationError("expression", "STARTS_WITH second argument must be a string")
			return
		}
		w.builder.WriteString("?")
		w.args = append(w.args, strArg.Value+"%")

	case "ENDS_WITH":
		// ENDS_WITH(field, 'text') -> field LIKE '%text'
		if len(node.Arguments) != 2 {
			w.err = apperrors.NewValidationError("expression", "ENDS_WITH requires 2 arguments")
			return
		}
		arg0 := node.Arguments[0]
		w.walk(&arg0)
		w.builder.WriteString(" LIKE ")
		strArg, ok := node.Arguments[1].(*ast.StringNode)
		if !ok {
			w.err = apperrors.NewValidationError("expression", "ENDS_WITH second argument must be a string")
			return
		}
		w.builder.WriteString("?")
		w.args = append(w.args, "%"+strArg.Value)

	case "TRUTHY":
		// TRUTHY(field) -> (field != 0) — boolean coercion for TINYINT(1) columns
		w.builder.WriteString("(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(" != 0)")

	case "NOT":
		// NOT(expr) -> NOT(expr_sql)
		w.builder.WriteString("NOT(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(")")

	case "AND":
		// Salesforce-style variadic AND(a, b, ...) -> (a AND b AND ...).
		// Paired with normalizeFormulaExpression's infix `AND` -> `&&` rewrite,
		// this lets both forms survive SQL compilation. Issue #1124.
		w.builder.WriteString("(")
		for i := range node.Arguments {
			if i > 0 {
				w.builder.WriteString(" AND ")
			}
			arg := node.Arguments[i]
			w.walkWithDepth(&arg, depth+1)
		}
		w.builder.WriteString(")")

	case "OR":
		// Salesforce-style variadic OR(a, b, ...) -> (a OR b OR ...).
		// See AND case above. Issue #1124.
		w.builder.WriteString("(")
		for i := range node.Arguments {
			if i > 0 {
				w.builder.WriteString(" OR ")
			}
			arg := node.Arguments[i]
			w.walkWithDepth(&arg, depth+1)
		}
		w.builder.WriteString(")")

	case "ISBLANK":
		// ISBLANK(field) -> (field IS NULL OR CAST(field AS CHAR) = '')
		// Salesforce v63 parity: nil and empty string ("") are blank; whitespace-only
		// strings are NOT blank (a field with spaces is not empty per ISBLANK docs).
		// CAST(field AS CHAR) prevents MySQL/TiDB implicit coercion where
		// numeric_col = '' evaluates as numeric_col = 0 (over-sharing risk).
		w.builder.WriteString("(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(" IS NULL OR CAST(")
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(" AS CHAR) = '')")

	case "FLOWISBLANK":
		// FLOWISBLANK is a Flow-filter-only helper. Flow's Is Blank operator on
		// text treats whitespace-only strings as blank, unlike formula ISBLANK.
		if len(node.Arguments) != 1 {
			w.err = apperrors.NewValidationError("expression", "FLOWISBLANK requires 1 argument")
			return
		}
		w.builder.WriteString("(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(" IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(CAST(")
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(" AS CHAR), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''), ' ', '') = '')")

	case "ISNULL":
		// ISNULL(field) -> (field IS NULL)
		// Matches formula engine: true only for nil, NOT for empty string
		w.builder.WriteString("(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(" IS NULL)")

	case "NULLVALUE":
		// NULLVALUE(field, substitute) -> COALESCE(NULLIF(CAST(field AS CHAR), ''), substitute)
		// Matches formula engine: substitutes both NULL and empty string.
		// Uses CAST(field AS CHAR) to avoid MySQL/TiDB implicit coercion where
		// NULLIF(numeric_col, '') treats 0 as '' (matching zero values).
		w.builder.WriteString("COALESCE(NULLIF(CAST(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(" AS CHAR), ''), ")
		arg1 := node.Arguments[1]
		w.walkWithDepth(&arg1, depth+1)
		w.builder.WriteString(")")

	case "CASE":
		// CASE(expr, val1, result1, val2, result2, ..., else_result)
		// -> CASE expr WHEN val1 THEN result1 WHEN val2 THEN result2 ... ELSE else_result END
		w.builder.WriteString("CASE ")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		// Pairs: (val, result)
		for i := 1; i < len(node.Arguments)-1; i += 2 {
			w.builder.WriteString(" WHEN ")
			argVal := node.Arguments[i]
			w.walkWithDepth(&argVal, depth+1)
			w.builder.WriteString(" THEN ")
			argRes := node.Arguments[i+1]
			w.walkWithDepth(&argRes, depth+1)
		}
		w.builder.WriteString(" ELSE ")
		argElse := node.Arguments[len(node.Arguments)-1]
		w.walkWithDepth(&argElse, depth+1)
		w.builder.WriteString(" END")

	case "YEAR":
		// YEAR(date) -> YEAR(date)
		w.builder.WriteString("YEAR(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(")")

	case "MONTH":
		// MONTH(date) -> MONTH(date)
		w.builder.WriteString("MONTH(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(")")

	case "DAY":
		// DAY(date) -> DAY(date)
		w.builder.WriteString("DAY(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(")")

	case "LPAD":
		// LPAD(str, len, padstr) ->
		//   IF(len IS NULL OR padstr IS NULL OR padstr = '', NULL, IF(len <= 0, '', LPAD(COALESCE(str, ''), len, padstr)))
		// Guard order matches the runtime evaluator exactly:
		//   1. len == nil → NULL  (runtime returns nil)
		//   2. pad == nil → NULL  (runtime returns nil)
		//   3. pad == ""  → NULL  (runtime returns nil; takes precedence over len<=0)
		//   4. len <= 0   → ''   (runtime returns "")
		//   5. nil str    → ''   (COALESCE; runtime treats nil/missing text as empty string)
		//   6. normal padding
		// When pad='' AND len<=0, the pad guard fires first and SQL returns NULL,
		// matching the runtime which also returns nil in that case.
		arg0 := node.Arguments[0]
		arg1 := node.Arguments[1]
		arg2 := node.Arguments[2]
		w.builder.WriteString("IF(")
		arg1a := node.Arguments[1]
		w.walkWithDepth(&arg1a, depth+1)
		w.builder.WriteString(" IS NULL OR ")
		arg2a := node.Arguments[2]
		w.walkWithDepth(&arg2a, depth+1)
		w.builder.WriteString(" IS NULL OR ")
		arg2b := node.Arguments[2]
		w.walkWithDepth(&arg2b, depth+1)
		w.builder.WriteString(" = '', NULL, IF(")
		arg1b := node.Arguments[1]
		w.walkWithDepth(&arg1b, depth+1)
		w.builder.WriteString(" <= 0, '', LPAD(COALESCE(")
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(", ''), ")
		w.walkWithDepth(&arg1, depth+1)
		w.builder.WriteString(", ")
		w.walkWithDepth(&arg2, depth+1)
		w.builder.WriteString(")))")

	case "RPAD":
		// RPAD(str, len, padstr) ->
		//   IF(len IS NULL OR padstr IS NULL OR padstr = '', NULL, IF(len <= 0, '', RPAD(COALESCE(str, ''), len, padstr)))
		// Same guard-order as LPAD: nil len → NULL, nil pad → NULL, empty pad → NULL, len<=0 → ''.
		arg0 := node.Arguments[0]
		arg1 := node.Arguments[1]
		arg2 := node.Arguments[2]
		w.builder.WriteString("IF(")
		arg1a := node.Arguments[1]
		w.walkWithDepth(&arg1a, depth+1)
		w.builder.WriteString(" IS NULL OR ")
		arg2a := node.Arguments[2]
		w.walkWithDepth(&arg2a, depth+1)
		w.builder.WriteString(" IS NULL OR ")
		arg2b := node.Arguments[2]
		w.walkWithDepth(&arg2b, depth+1)
		w.builder.WriteString(" = '', NULL, IF(")
		arg1b := node.Arguments[1]
		w.walkWithDepth(&arg1b, depth+1)
		w.builder.WriteString(" <= 0, '', RPAD(COALESCE(")
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(", ''), ")
		w.walkWithDepth(&arg1, depth+1)
		w.builder.WriteString(", ")
		w.walkWithDepth(&arg2, depth+1)
		w.builder.WriteString(")))")

	default:
		w.err = apperrors.NewValidationError("expression", fmt.Sprintf("unsupported function: %s", callee.Value))
	}
}

func checkCatalogFunctionArity(name string, argc int) error {
	// Flow-only SQL helpers (for example FLOWISBLANK) are not formula catalog
	// members; their small local guards live in the switch cases above.
	if _, ok := spec.DefaultRegistry().Lookup(name); !ok {
		return nil
	}
	if err := spec.CheckArity(name, argc); err != nil {
		return apperrors.NewValidationError("expression", err.Error())
	}
	return nil
}

// Helper to walk multiple args with comma separation
func (w *SQLWalker) walkArgs(args []ast.Node, depth int) {
	for i, arg := range args {
		if i > 0 {
			w.builder.WriteString(", ")
		}
		argNode := arg
		w.walkWithDepth(&argNode, depth)
	}
}

func (w *SQLWalker) compileFragment(arg ast.Node, depth int) (SQLFragment, error) {
	argNode := arg
	sub := &SQLWalker{
		args:             make([]interface{}, 0),
		mapper:           w.mapper,
		functionLowerer:  w.functionLowerer,
		boundIdentifiers: cloneSQLFragments(w.boundIdentifiers),
		allowMemberPaths: w.allowMemberPaths,
	}
	sub.walkWithDepth(&argNode, depth)
	if sub.err != nil {
		return SQLFragment{}, sub.err
	}
	return SQLFragment{SQL: sub.builder.String(), Args: append([]interface{}(nil), sub.args...)}, nil
}

func (w *SQLWalker) lookupBoundIdentifier(name string) (SQLFragment, bool) {
	if w == nil || len(w.boundIdentifiers) == 0 {
		return SQLFragment{}, false
	}
	if fragment, ok := w.boundIdentifiers[name]; ok {
		return fragment, true
	}
	for key, fragment := range w.boundIdentifiers {
		if strings.EqualFold(key, name) {
			return fragment, true
		}
	}
	return SQLFragment{}, false
}

func (w *SQLWalker) writeFragment(fragment SQLFragment) {
	w.builder.WriteString(fragment.SQL)
	w.args = append(w.args, fragment.Args...)
}

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