package analytics

import (
	"fmt"
	"strings"

	"github.com/nexuscrm/backend/pkg/logging"
	"github.com/nexuscrm/backend/pkg/utils"
)

const (
	// AggMarkerPrefix is the delimiter used for aggregate placeholder markers in compiled SQL.
	// Format: ##AGG##<FUNC>##<ChildObject>##<ChildField>##<ForeignKey>##
	AggMarkerPrefix = "##AGG##"

	// DefaultDecimalPrecision is the DECIMAL precision used for VALUE() casts.
	DefaultDecimalPrecision = 18
	// DefaultDecimalScale is the DECIMAL scale used for VALUE() casts.
	DefaultDecimalScale = 6
)

// CompileContext provides the compiler with table alias mappings for
// cross-table references and aggregate subqueries.
type CompileContext struct {
	// TableAlias is the alias for the current (parent) table, e.g. "t"
	TableAlias string

	// JoinAliases maps relationship names to SQL aliases.
	// e.g. {"Account": "a", "Owner": "own", "Opportunity": "o"}
	JoinAliases map[string]string

	// QualifyFields: if true, prefix bare FieldRef with TableAlias.
	QualifyFields bool

	// DeepJoinMap maps multi-hop cross-table refs to resolved SQL expressions.
	// Key: "Owner.Profile.Name" → Value: "dj_prof.`Name`"
	// Populated from resolved DeepJoinChain data by the ViewBuilder.
	DeepJoinMap map[string]string

	// FieldTypes maps identifier paths to their Salesforce field type so the
	// compiler can emit type-aware SQL (e.g. DATEDIFF for date - date instead
	// of the YYYYMMDD numeric subtraction MySQL would perform by default).
	//
	// Keys:
	//   - bare field: "Order_End_Date__c"
	//   - cross-ref: "Opportunity.CloseDate"
	//   - deep ref:  "Owner.Profile.BirthDate"
	//
	// Values are lowercased Salesforce types: "date", "datetime", "currency",
	// "double", "percent", "int", "string", "picklist", "boolean", etc.
	// Missing keys are treated as unknown and fall back to type-naive emission.
	FieldTypes map[string]string
}

// Compile translates an AST node into a TiDB-compatible SQL expression.
func Compile(node Node, ctx *CompileContext) (string, error) {
	if ctx == nil {
		ctx = &CompileContext{TableAlias: "t", JoinAliases: map[string]string{}}
	}
	return compile(node, ctx)
}

func compile(node Node, ctx *CompileContext) (string, error) {
	switch n := node.(type) {
	case *NumberLit:
		return n.Value, nil

	case *StringLit:
		return "'" + utils.EscapeSQLString(n.Value) + "'", nil

	case *BoolLit:
		if n.Value {
			return "1", nil
		}
		return "0", nil

	case *NullLit:
		return "NULL", nil

	case *FieldRef:
		if ctx.QualifyFields && ctx.TableAlias != "" {
			return fmt.Sprintf("%s.`%s`", ctx.TableAlias, n.Name), nil
		}
		return fmt.Sprintf("`%s`", n.Name), nil

	case *CrossRef:
		alias, ok := ctx.JoinAliases[n.Object]
		if !ok {
			// Try case-insensitive lookup
			for k, v := range ctx.JoinAliases {
				if strings.EqualFold(k, n.Object) {
					alias = v
					ok = true
					break
				}
			}
		}
		if !ok {
			// Fallback: for unresolvable cross-refs (e.g. custom __r relationships),
			// use the physical column on the current table (SF snapshot value).
			logging.Default.Warn(fmt.Sprintf("formula_dsl_compiler: CrossRef fallback — object %q not in JoinAliases, using physical column %q on current table", n.Object, n.Field))
			if ctx.QualifyFields && ctx.TableAlias != "" {
				return fmt.Sprintf("%s.`%s`", ctx.TableAlias, n.Field), nil
			}
			return fmt.Sprintf("`%s`", n.Field), nil
		}
		return fmt.Sprintf("%s.`%s`", alias, n.Field), nil

	case *DeepCrossRef:
		// Check if this deep ref was resolved to a chain JOIN
		if ctx.DeepJoinMap != nil {
			key := strings.Join(n.Parts, ".")
			if resolved, ok := ctx.DeepJoinMap[key]; ok {
				return resolved, nil
			}
		}
		// Fallback: use physical column on current table (SF snapshot value)
		fieldName := n.Parts[len(n.Parts)-1]
		logging.Default.Warn(fmt.Sprintf("formula_dsl_compiler: DeepCrossRef fallback — path %q not resolved, using physical column %q", strings.Join(n.Parts, "."), fieldName))
		if ctx.QualifyFields && ctx.TableAlias != "" {
			return fmt.Sprintf("%s.`%s`", ctx.TableAlias, fieldName), nil
		}
		return fmt.Sprintf("`%s`", fieldName), nil

	case *BinaryOp:
		return compileBinaryOp(n, ctx)

	case *UnaryOp:
		return compileUnaryOp(n, ctx)

	case *LogicalOp:
		return compileLogicalOp(n, ctx)

	case *FuncCall:
		return compileFuncCall(n, ctx)

	case *IfExpr:
		return compileIf(n, ctx)

	case *CaseExpr:
		return compileCase(n, ctx)

	case *AggregateExpr:
		return compileAggregate(n, ctx)

	default:
		return "", fmt.Errorf("unsupported AST node type: %T", node)
	}
}

func compileBinaryOp(n *BinaryOp, ctx *CompileContext) (string, error) {
	left, err := compile(n.Left, ctx)
	if err != nil {
		return "", err
	}
	right, err := compile(n.Right, ctx)
	if err != nil {
		return "", err
	}

	switch n.Op {
	case "&":
		// String concatenation → CONCAT
		return fmt.Sprintf("CONCAT(%s, %s)", left, right), nil
	case "^":
		// Exponent → POWER
		return fmt.Sprintf("POWER(%s, %s)", left, right), nil
	case "<>":
		return fmt.Sprintf("(%s != %s)", left, right), nil
	case "-":
		// Salesforce semantics for date arithmetic diverge from MySQL:
		//   date1 - date2       → number of days (integer)
		//   date - number       → date shifted back by N days
		// MySQL, given raw `-`, coerces date values to YYYYMMDD integers
		// and returns the arithmetic difference (e.g. 20240330 - 20230531
		// = 9799 instead of 304), which silently corrupts every formula
		// that does date subtraction (Term__c, Closed_Days__c, ValidDays__c).
		// Detect the types and emit DATEDIFF / DATE_SUB instead.
		if isDateLike(n.Left, ctx) && isDateLike(n.Right, ctx) {
			return fmt.Sprintf("DATEDIFF(%s, %s)", left, right), nil
		}
		if isDateLike(n.Left, ctx) && isNumeric(n.Right, ctx) {
			return fmt.Sprintf("DATE_SUB(%s, INTERVAL (%s) DAY)", left, right), nil
		}
		return fmt.Sprintf("(%s %s %s)", left, n.Op, right), nil
	case "+":
		// Salesforce: date + number → date shifted forward by N days.
		if isDateLike(n.Left, ctx) && isNumeric(n.Right, ctx) {
			return fmt.Sprintf("DATE_ADD(%s, INTERVAL (%s) DAY)", left, right), nil
		}
		if isNumeric(n.Left, ctx) && isDateLike(n.Right, ctx) {
			return fmt.Sprintf("DATE_ADD(%s, INTERVAL (%s) DAY)", right, left), nil
		}
		return fmt.Sprintf("(%s %s %s)", left, n.Op, right), nil
	default:
		return fmt.Sprintf("(%s %s %s)", left, n.Op, right), nil
	}
}

// isDateLike reports whether a node evaluates to a date/datetime value.
// Date-returning functions (TODAY, NOW, DATE, DATEVALUE, ADDMONTHS, DATE_ADD,
// DATE_SUB) are recognised regardless of context. Field references fall back
// to the FieldTypes map populated by the ViewBuilder from the live schema;
// missing entries are treated as non-date (safe fallback to raw `-`).
func isDateLike(node Node, ctx *CompileContext) bool {
	switch n := node.(type) {
	case *FieldRef:
		return isDateType(lookupFieldType(ctx, n.Name))
	case *CrossRef:
		return isDateType(lookupFieldType(ctx, n.Object+"."+n.Field))
	case *DeepCrossRef:
		return isDateType(lookupFieldType(ctx, strings.Join(n.Parts, ".")))
	case *FuncCall:
		switch strings.ToUpper(n.Name) {
		case "TODAY", "NOW", "DATE", "DATEVALUE", "ADDMONTHS", "DATE_ADD", "DATE_SUB":
			return true
		}
		return false
	case *IfExpr:
		return isDateLike(n.Then, ctx) || isDateLike(n.Else, ctx)
	case *CaseExpr:
		for _, br := range n.Branches {
			if isDateLike(br.Result, ctx) {
				return true
			}
		}
		return isDateLike(n.Default, ctx)
	}
	return false
}

// isNumeric reports whether a node evaluates to a numeric value — either a
// number literal or a field whose type is currency/double/int/percent.
// A number literal adjacent to a date operand is the trigger for DATE_ADD
// / DATE_SUB; without this guard we would over-eagerly rewrite every `-`.
func isNumeric(node Node, ctx *CompileContext) bool {
	switch n := node.(type) {
	case *NumberLit:
		return true
	case *FieldRef:
		return isNumericType(lookupFieldType(ctx, n.Name))
	case *CrossRef:
		return isNumericType(lookupFieldType(ctx, n.Object+"."+n.Field))
	case *DeepCrossRef:
		return isNumericType(lookupFieldType(ctx, strings.Join(n.Parts, ".")))
	case *FuncCall:
		switch strings.ToUpper(n.Name) {
		case "VALUE", "LEN", "YEAR", "MONTH", "DAY", "ROUND", "FLOOR", "CEILING", "CEIL", "ABS", "MOD":
			return true
		}
		return false
	case *BinaryOp:
		// Nested arithmetic that is itself numeric (e.g. a date-date DATEDIFF
		// returns number — so (date - date) + 1 stays as (..) + 1).
		if n.Op == "-" && isDateLike(n.Left, ctx) && isDateLike(n.Right, ctx) {
			return true
		}
		switch n.Op {
		case "+", "-", "*", "/", "^":
			return isNumeric(n.Left, ctx) || isNumeric(n.Right, ctx)
		}
	case *UnaryOp:
		if n.Op == "-" {
			return isNumeric(n.Operand, ctx)
		}
	}
	return false
}

func lookupFieldType(ctx *CompileContext, key string) string {
	if ctx == nil || ctx.FieldTypes == nil {
		return ""
	}
	return ctx.FieldTypes[key]
}

func isDateType(t string) bool {
	switch strings.ToLower(t) {
	case "date", "datetime":
		return true
	}
	return false
}

func isNumericType(t string) bool {
	switch strings.ToLower(t) {
	case "currency", "double", "int", "integer", "long", "number", "percent":
		return true
	}
	return false
}

func compileUnaryOp(n *UnaryOp, ctx *CompileContext) (string, error) {
	operand, err := compile(n.Operand, ctx)
	if err != nil {
		return "", err
	}
	switch n.Op {
	case "-":
		return fmt.Sprintf("(-%s)", operand), nil
	case "NOT":
		return fmt.Sprintf("(NOT %s)", operand), nil
	default:
		return "", fmt.Errorf("unsupported unary op: %s", n.Op)
	}
}

func compileLogicalOp(n *LogicalOp, ctx *CompileContext) (string, error) {
	parts := make([]string, len(n.Operands))
	for i, op := range n.Operands {
		s, err := compile(op, ctx)
		if err != nil {
			return "", err
		}
		parts[i] = s
	}
	return "(" + strings.Join(parts, " "+n.Op+" ") + ")", nil
}

func compileFuncCall(n *FuncCall, ctx *CompileContext) (string, error) {
	// Map SF function names to TiDB equivalents
	upper := strings.ToUpper(n.Name)

	switch upper {
	case "ISBLANK":
		if len(n.Args) != 1 {
			return "", fmt.Errorf("ISBLANK requires 1 argument")
		}
		arg, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("(%s IS NULL OR %s = '')", arg, arg), nil

	case "ISPICKVAL":
		if len(n.Args) != 2 {
			return "", fmt.Errorf("ISPICKVAL requires 2 arguments")
		}
		field, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		val, err := compile(n.Args[1], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("(%s = %s)", field, val), nil

	case "TEXT":
		if len(n.Args) != 1 {
			return "", fmt.Errorf("TEXT requires 1 argument")
		}
		// Picklist is already VARCHAR in TiDB — pass through
		return compile(n.Args[0], ctx)

	case "TODAY":
		return "CURDATE()", nil

	case "NOW":
		return "NOW()", nil

	case "LEN":
		if len(n.Args) != 1 {
			return "", fmt.Errorf("LEN requires 1 argument")
		}
		arg, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("CHAR_LENGTH(%s)", arg), nil

	case "CONTAINS":
		if len(n.Args) != 2 {
			return "", fmt.Errorf("CONTAINS requires 2 arguments")
		}
		field, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		substr, err := compile(n.Args[1], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("(%s LIKE CONCAT('%%', %s, '%%'))", field, substr), nil

	case "BEGINS":
		if len(n.Args) != 2 {
			return "", fmt.Errorf("BEGINS requires 2 arguments")
		}
		field, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		prefix, err := compile(n.Args[1], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("(%s LIKE CONCAT(%s, '%%'))", field, prefix), nil

	case "FIND":
		if len(n.Args) < 2 || len(n.Args) > 3 {
			return "", fmt.Errorf("FIND requires 2-3 arguments")
		}
		search, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		str, err := compile(n.Args[1], ctx)
		if err != nil {
			return "", err
		}
		if len(n.Args) == 3 {
			pos, err := compile(n.Args[2], ctx)
			if err != nil {
				return "", err
			}
			return fmt.Sprintf("LOCATE(%s, %s, %s)", search, str, pos), nil
		}
		return fmt.Sprintf("LOCATE(%s, %s)", search, str), nil

	case "NULLVALUE":
		if len(n.Args) != 2 {
			return "", fmt.Errorf("NULLVALUE requires 2 arguments")
		}
		field, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		def, err := compile(n.Args[1], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("IFNULL(%s, %s)", field, def), nil

	case "BLANKVALUE":
		if len(n.Args) != 2 {
			return "", fmt.Errorf("BLANKVALUE requires 2 arguments")
		}
		field, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		def, err := compile(n.Args[1], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("IFNULL(NULLIF(%s, ''), %s)", field, def), nil

	case "SUBSTITUTE":
		if len(n.Args) != 3 {
			return "", fmt.Errorf("SUBSTITUTE requires 3 arguments")
		}
		text, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		old, err := compile(n.Args[1], ctx)
		if err != nil {
			return "", err
		}
		new, err := compile(n.Args[2], ctx)
		if err != nil {
			return "", err
		}
		// In SF, NULL as replacement means empty string. TiDB REPLACE returns NULL if any arg is NULL.
		return fmt.Sprintf("REPLACE(%s, %s, IFNULL(%s, ''))", text, old, new), nil

	case "MID":
		if len(n.Args) != 3 {
			return "", fmt.Errorf("MID requires 3 arguments")
		}
		text, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		start, err := compile(n.Args[1], ctx)
		if err != nil {
			return "", err
		}
		length, err := compile(n.Args[2], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("SUBSTRING(%s, %s, %s)", text, start, length), nil

	case "LEFT":
		if len(n.Args) != 2 {
			return "", fmt.Errorf("LEFT requires 2 arguments")
		}
		text, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		length, err := compile(n.Args[1], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("LEFT(%s, %s)", text, length), nil

	case "RIGHT":
		if len(n.Args) != 2 {
			return "", fmt.Errorf("RIGHT requires 2 arguments")
		}
		text, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		length, err := compile(n.Args[1], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("RIGHT(%s, %s)", text, length), nil

	case "ADDMONTHS":
		if len(n.Args) != 2 {
			return "", fmt.Errorf("ADDMONTHS requires 2 arguments")
		}
		date, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		months, err := compile(n.Args[1], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("DATE_ADD(%s, INTERVAL %s MONTH)", date, months), nil

	case "YEAR":
		if len(n.Args) != 1 {
			return "", fmt.Errorf("YEAR requires 1 argument")
		}
		arg, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("YEAR(%s)", arg), nil

	case "MONTH":
		if len(n.Args) != 1 {
			return "", fmt.Errorf("MONTH requires 1 argument")
		}
		arg, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("MONTH(%s)", arg), nil

	case "DAY":
		if len(n.Args) != 1 {
			return "", fmt.Errorf("DAY requires 1 argument")
		}
		arg, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("DAY(%s)", arg), nil

	case "ROUND":
		if len(n.Args) < 1 || len(n.Args) > 2 {
			return "", fmt.Errorf("ROUND requires 1-2 arguments")
		}
		val, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		if len(n.Args) == 2 {
			places, err := compile(n.Args[1], ctx)
			if err != nil {
				return "", err
			}
			return fmt.Sprintf("ROUND(%s, %s)", val, places), nil
		}
		return fmt.Sprintf("ROUND(%s)", val), nil

	case "CEILING":
		if len(n.Args) != 1 {
			return "", fmt.Errorf("CEILING requires 1 argument")
		}
		arg, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("CEIL(%s)", arg), nil

	case "FLOOR":
		if len(n.Args) != 1 {
			return "", fmt.Errorf("FLOOR requires 1 argument")
		}
		arg, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("FLOOR(%s)", arg), nil

	case "ABS":
		if len(n.Args) != 1 {
			return "", fmt.Errorf("ABS requires 1 argument")
		}
		arg, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("ABS(%s)", arg), nil

	case "MAX", "MIN":
		// These can be regular SQL functions with multiple args
		args := make([]string, len(n.Args))
		for i, a := range n.Args {
			s, err := compile(a, ctx)
			if err != nil {
				return "", err
			}
			args[i] = s
		}
		if len(args) == 2 {
			if upper == "MIN" {
				return fmt.Sprintf("LEAST(%s, %s)", args[0], args[1]), nil
			}
			return fmt.Sprintf("GREATEST(%s, %s)", args[0], args[1]), nil
		}
		return fmt.Sprintf("%s(%s)", upper, strings.Join(args, ", ")), nil

	case "TRIM":
		if len(n.Args) != 1 {
			return "", fmt.Errorf("TRIM requires 1 argument")
		}
		arg, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("TRIM(%s)", arg), nil

	case "UPPER":
		if len(n.Args) != 1 {
			return "", fmt.Errorf("UPPER requires 1 argument")
		}
		arg, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("UPPER(%s)", arg), nil

	case "LOWER":
		if len(n.Args) != 1 {
			return "", fmt.Errorf("LOWER requires 1 argument")
		}
		arg, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("LOWER(%s)", arg), nil

	case "LPAD":
		if len(n.Args) != 3 {
			return "", fmt.Errorf("LPAD requires 3 arguments")
		}
		args := make([]string, 3)
		for i := range n.Args {
			s, err := compile(n.Args[i], ctx)
			if err != nil {
				return "", err
			}
			args[i] = s
		}
		return fmt.Sprintf("LPAD(%s, %s, %s)", args[0], args[1], args[2]), nil

	case "RPAD":
		if len(n.Args) != 3 {
			return "", fmt.Errorf("RPAD requires 3 arguments")
		}
		args := make([]string, 3)
		for i := range n.Args {
			s, err := compile(n.Args[i], ctx)
			if err != nil {
				return "", err
			}
			args[i] = s
		}
		return fmt.Sprintf("RPAD(%s, %s, %s)", args[0], args[1], args[2]), nil

	case "VALUE":
		// VALUE(text) → CAST to number
		if len(n.Args) != 1 {
			return "", fmt.Errorf("VALUE requires 1 argument")
		}
		arg, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("CAST(%s AS DECIMAL(%d,%d))", arg, DefaultDecimalPrecision, DefaultDecimalScale), nil

	case "DATEVALUE":
		if len(n.Args) != 1 {
			return "", fmt.Errorf("DATEVALUE requires 1 argument")
		}
		arg, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("DATE(%s)", arg), nil

	case "DATETIMEVALUE":
		if len(n.Args) != 1 {
			return "", fmt.Errorf("DATETIMEVALUE requires 1 argument")
		}
		arg, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("TIMESTAMP(%s)", arg), nil

	case "ISNULL":
		if len(n.Args) != 1 {
			return "", fmt.Errorf("ISNULL requires 1 argument")
		}
		arg, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("(%s IS NULL)", arg), nil

	case "ISNUMBER":
		if len(n.Args) != 1 {
			return "", fmt.Errorf("ISNUMBER requires 1 argument")
		}
		arg, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("(%s REGEXP '^-?[0-9]+(\\\\.[0-9]+)?$')", arg), nil

	case "CASESAFEID":
		// SF CASESAFEID returns the 18-char ID; in TiDB IDs are already 18-char
		if len(n.Args) != 1 {
			return "", fmt.Errorf("CASESAFEID requires 1 argument")
		}
		return compile(n.Args[0], ctx)

	case "HYPERLINK":
		// HYPERLINK(url, label) → just return the label (2nd arg) or url if only 1 arg
		if len(n.Args) >= 2 {
			return compile(n.Args[1], ctx)
		}
		if len(n.Args) == 1 {
			return compile(n.Args[0], ctx)
		}
		return "''", nil

	case "INCLUDES":
		// INCLUDES(multiselect, value) → multi-select picklist contains check
		if len(n.Args) != 2 {
			return "", fmt.Errorf("INCLUDES requires 2 arguments")
		}
		field, err := compile(n.Args[0], ctx)
		if err != nil {
			return "", err
		}
		val, err := compile(n.Args[1], ctx)
		if err != nil {
			return "", err
		}
		return fmt.Sprintf("FIND(%s, %s) > 0", val, field), nil

	case "BR":
		// BR() → newline in formula output
		return "'\\n'", nil

	case "IMAGE":
		// IMAGE(url, alt, ...) → just return the URL
		if len(n.Args) >= 1 {
			return compile(n.Args[0], ctx)
		}
		return "''", nil

	case "DATE":
		// DATE(year, month, day) → STR_TO_DATE
		if len(n.Args) == 3 {
			y, err := compile(n.Args[0], ctx)
			if err != nil {
				return "", fmt.Errorf("DATE arg 1: %w", err)
			}
			m, err := compile(n.Args[1], ctx)
			if err != nil {
				return "", fmt.Errorf("DATE arg 2: %w", err)
			}
			d, err := compile(n.Args[2], ctx)
			if err != nil {
				return "", fmt.Errorf("DATE arg 3: %w", err)
			}
			return fmt.Sprintf("STR_TO_DATE(CONCAT(%s,'-',%s,'-',%s), '%%Y-%%m-%%d')", y, m, d), nil
		}
		if len(n.Args) == 1 {
			arg, err := compile(n.Args[0], ctx)
			if err != nil {
				return "", fmt.Errorf("DATE arg: %w", err)
			}
			return fmt.Sprintf("DATE(%s)", arg), nil
		}
		return "NULL", nil

	default:
		// Only allow known-safe SQL functions to prevent injection of dangerous functions
		allowedFuncs := map[string]bool{
			"COALESCE": true, "CONCAT": true, "CONCAT_WS": true, "NULLIF": true,
			"IFNULL": true, "ISNULL": true, "CAST": true, "CONVERT": true,
			"ROUND": true, "FLOOR": true, "CEILING": true, "CEIL": true,
			"POWER": true, "POW": true, "SQRT": true, "MOD": true, "SIGN": true,
			"SUBSTRING": true, "SUBSTR": true, "REPLACE": true, "REVERSE": true,
			"LPAD": true, "RPAD": true, "LTRIM": true, "RTRIM": true,
			"DATE_FORMAT": true, "STR_TO_DATE": true, "DATE_ADD": true, "DATE_SUB": true,
			"DATEDIFF": true, "TIMESTAMPDIFF": true, "CURDATE": true, "NOW": true,
			"GREATEST": true, "LEAST": true, "LENGTH": true, "CHAR_LENGTH": true,
		}
		if !allowedFuncs[upper] {
			return "", fmt.Errorf("unsupported function: %s", upper)
		}
		args := make([]string, len(n.Args))
		for i, a := range n.Args {
			s, err := compile(a, ctx)
			if err != nil {
				return "", err
			}
			args[i] = s
		}
		return fmt.Sprintf("%s(%s)", upper, strings.Join(args, ", ")), nil
	}
}

func compileIf(n *IfExpr, ctx *CompileContext) (string, error) {
	cond, err := compile(n.Condition, ctx)
	if err != nil {
		return "", err
	}
	then, err := compile(n.Then, ctx)
	if err != nil {
		return "", err
	}
	elseVal, err := compile(n.Else, ctx)
	if err != nil {
		return "", err
	}
	return fmt.Sprintf("CASE WHEN %s THEN %s ELSE %s END", cond, then, elseVal), nil
}

func compileCase(n *CaseExpr, ctx *CompileContext) (string, error) {
	expr, err := compile(n.Expr, ctx)
	if err != nil {
		return "", err
	}

	var sb strings.Builder
	sb.WriteString("CASE ")
	sb.WriteString(expr)

	for _, b := range n.Branches {
		val, err := compile(b.Value, ctx)
		if err != nil {
			return "", err
		}
		result, err := compile(b.Result, ctx)
		if err != nil {
			return "", err
		}
		sb.WriteString(" WHEN ")
		sb.WriteString(val)
		sb.WriteString(" THEN ")
		sb.WriteString(result)
	}

	if n.Default != nil {
		def, err := compile(n.Default, ctx)
		if err != nil {
			return "", err
		}
		sb.WriteString(" ELSE ")
		sb.WriteString(def)
	}

	sb.WriteString(" END")
	return sb.String(), nil
}

// compileAggregate generates a correlated subquery for rollup aggregates.
// In the View DDL builder, these get hoisted into LEFT JOIN subqueries.
// For the expression compiler, we emit a placeholder that the DDL builder
// can recognize and transform.
func compileAggregate(n *AggregateExpr, ctx *CompileContext) (string, error) {
	// Emit a marker that the DDL builder will replace with a proper subquery JOIN.
	// Format: ##AGG##<FUNC>##<ChildObject>##<ChildField>##<ForeignKey>##
	fk := n.ForeignKey
	if fk == "" {
		fk = "_"
	}
	field := n.ChildField
	if field == "" {
		field = "*"
	}
	return fmt.Sprintf("%s%s##%s##%s##%s##", AggMarkerPrefix, n.Func, n.ChildObject, field, fk), nil
}