package expression

import (
	"fmt"
	"strings"

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

	apperrors "github.com/nexuscrm/backend/pkg/errors"
)

// SQLWalker converts an expr AST to SQL
type SQLWalker struct {
	builder strings.Builder
	args    []interface{}
	err     error
	mapper  func(string) string
}

// 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) {
	// 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: mapper,
	}

	// 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:
		val := v.Value
		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 (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)

	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)
		if len(node.Arguments) != 3 {
			w.err = apperrors.NewValidationError("expression", "IF requires 3 arguments")
			return
		}
		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":
		// DATE_ADD(date, days) -> DATE_ADD(date_sql, INTERVAL ? DAY)
		if len(node.Arguments) != 2 {
			w.err = apperrors.NewValidationError("expression", "DATE_ADD requires 2 arguments")
			return
		}
		w.builder.WriteString("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 "CONTAINS":
		// CONTAINS(field, 'text') -> field LIKE '%text%'
		if len(node.Arguments) != 2 {
			w.err = apperrors.NewValidationError("expression", "CONTAINS requires 2 arguments")
			return
		}
		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
		if len(node.Arguments) != 1 {
			w.err = apperrors.NewValidationError("expression", "TRUTHY requires 1 argument")
			return
		}
		w.builder.WriteString("(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(" != 0)")

	case "NOT":
		// NOT(expr) -> NOT(expr_sql)
		if len(node.Arguments) != 1 {
			w.err = apperrors.NewValidationError("expression", "NOT requires 1 argument")
			return
		}
		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.
		if len(node.Arguments) == 0 {
			w.err = apperrors.NewValidationError("expression", "AND requires at least 1 argument")
			return
		}
		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.
		if len(node.Arguments) == 0 {
			w.err = apperrors.NewValidationError("expression", "OR requires at least 1 argument")
			return
		}
		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 "ISPICKVAL":
		// ISPICKVAL(field, "value") -> (field = 'value')
		if len(node.Arguments) != 2 {
			w.err = apperrors.NewValidationError("expression", "ISPICKVAL requires 2 arguments")
			return
		}
		w.builder.WriteString("(")
		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 "ISBLANK":
		// ISBLANK(field) -> (field IS NULL OR CAST(field AS CHAR) = '')
		// Matches formula engine: true for nil or empty string.
		// Uses CAST(field AS CHAR) to avoid MySQL/TiDB implicit coercion where
		// numeric_col = '' evaluates as numeric_col = 0 (over-sharing risk).
		// CAST ensures the comparison is always string-typed.
		if len(node.Arguments) != 1 {
			w.err = apperrors.NewValidationError("expression", "ISBLANK requires 1 argument")
			return
		}
		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 "ISNULL":
		// ISNULL(field) -> (field IS NULL)
		// Matches formula engine: true only for nil, NOT for empty string
		if len(node.Arguments) != 1 {
			w.err = apperrors.NewValidationError("expression", "ISNULL requires 1 argument")
			return
		}
		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).
		if len(node.Arguments) != 2 {
			w.err = apperrors.NewValidationError("expression", "NULLVALUE requires 2 arguments")
			return
		}
		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
		if len(node.Arguments) < 4 || len(node.Arguments)%2 != 0 {
			w.err = apperrors.NewValidationError("expression", "CASE requires at least 4 arguments (expr, val, result, else) with even count")
			return
		}
		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)
		if len(node.Arguments) != 1 {
			w.err = apperrors.NewValidationError("expression", "YEAR requires 1 argument")
			return
		}
		w.builder.WriteString("YEAR(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(")")

	case "MONTH":
		// MONTH(date) -> MONTH(date)
		if len(node.Arguments) != 1 {
			w.err = apperrors.NewValidationError("expression", "MONTH requires 1 argument")
			return
		}
		w.builder.WriteString("MONTH(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(")")

	case "DAY":
		// DAY(date) -> DAY(date)
		if len(node.Arguments) != 1 {
			w.err = apperrors.NewValidationError("expression", "DAY requires 1 argument")
			return
		}
		w.builder.WriteString("DAY(")
		arg0 := node.Arguments[0]
		w.walkWithDepth(&arg0, depth+1)
		w.builder.WriteString(")")

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

// 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)
	}
}