package analytics

import (
	"fmt"
	"strings"

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

// compileFuncUsesMultipicklistContainsLowering reports whether the named function
// is registered with the multipicklist "contains all" SQL-lowering capability
// flag, so the compiler dispatches on the flag instead of the hardcoded function
// name. It is nil-safe: absent an executables registry or the flag, it is false.
func compileFuncUsesMultipicklistContainsLowering(ctx *CompileContext, name string) bool {
	execs := activeFunctionExecutables(ctx)
	if execs == nil {
		return false
	}
	def, ok := execs.LookupFunctionDefinition(strings.ToUpper(name))
	return ok && def.SQLLowering == formula.SQLLoweringMultipicklistContainsAll
}

// compileIncludesNativeSQL emits the native multi-select picklist "contains"
// SQL for the multiselect(field, value) membership test. Arity (exactly 2) is
// enforced by the shared catalog. This is the shared emission used both by the
// by-name case and by functions carrying the multipicklist_contains_all flag.
func compileIncludesNativeSQL(n *FuncCall, ctx *CompileContext) (string, error) {
	if err := checkFunctionArity(ctx, "INCLUDES", len(n.Args)); err != nil {
		return "", err
	}
	field, err := compile(n.Args[0], ctx)
	if err != nil {
		return "", err
	}
	if tokens, ok := includesStringLiteralRHSTokens(n.Args[1]); ok {
		return compileIncludesMultiTokenSQL(field, tokens), nil
	}
	val, err := compile(n.Args[1], ctx)
	if err != nil {
		return "", err
	}
	return utils.MultiPicklistJSONContainsSQL(field, val, true), nil
}

func compileExecutableFunctionCall(n *FuncCall, upper string, ctx *CompileContext) (string, bool, error) {
	executables := activeFunctionExecutables(ctx)
	if executables == nil {
		return "", false, nil
	}
	definition, ok := executables.LookupFunctionDefinition(upper)
	if !ok {
		return "", false, nil
	}
	if err := checkFunctionArity(ctx, upper, len(n.Args)); err != nil {
		return "", true, err
	}
	switch strings.TrimSpace(definition.Evaluator.Kind) {
	case formula.FormulaExprV1EvaluatorKind:
		sql, err := compileFormulaExprV1ExecutableFunction(definition, n, ctx)
		if err != nil {
			return "", true, fmt.Errorf("%s SQL lowering: %w", upper, err)
		}
		return sql, true, nil
	default:
		return "", true, fmt.Errorf("%s SQL lowering: unsupported evaluator kind %q", upper, definition.Evaluator.Kind)
	}
}

func compileFormulaExprV1ExecutableFunction(definition formula.FunctionDefinition, call *FuncCall, ctx *CompileContext) (string, error) {
	if ctx == nil {
		ctx = &CompileContext{TableAlias: "t"}
	}
	body := strings.TrimSpace(definition.Evaluator.Body)
	if body == "" {
		return "", fmt.Errorf("%s body is required", formula.FormulaExprV1EvaluatorKind)
	}
	if len(definition.Evaluator.Params) > len(call.Args) {
		return "", fmt.Errorf("%s params length %d exceeds argument count %d", formula.FormulaExprV1EvaluatorKind, len(definition.Evaluator.Params), len(call.Args))
	}
	bodyAST, err := Parse(body)
	if err != nil {
		return "", fmt.Errorf("parse evaluator body: %w", err)
	}

	boundSQL := cloneStringMap(ctx.BoundIdentifierSQL)
	boundTypes := cloneStringMap(ctx.BoundIdentifierTypes)
	for i, rawParam := range definition.Evaluator.Params {
		param := strings.TrimSpace(rawParam)
		if param == "" {
			return "", fmt.Errorf("%s has empty param name at index %d", formula.FormulaExprV1EvaluatorKind, i)
		}
		argSQL, err := compile(call.Args[i], ctx)
		if err != nil {
			return "", fmt.Errorf("arg %d: %w", i+1, err)
		}
		if boundSQL == nil {
			boundSQL = map[string]string{}
		}
		boundSQL[param] = argSQL
		if typ := fieldTypeForBundleArg(definition, i); typ != "" {
			if boundTypes == nil {
				boundTypes = map[string]string{}
			}
			boundTypes[param] = typ
		}
	}

	next := *ctx
	next.BoundIdentifierSQL = boundSQL
	next.BoundIdentifierTypes = boundTypes
	return compile(bodyAST, &next)
}

func fieldTypeForBundleArg(definition formula.FunctionDefinition, index int) string {
	specDef := definition.Spec()
	argSpec, ok := specDef.ArgSpecAt(index)
	if !ok {
		return ""
	}
	if argSpec.DateLikeLiteral {
		return string(constants.FieldTypeDate)
	}
	switch argSpec.Type {
	case spec.TypeString:
		return string(constants.FieldTypeString)
	case spec.TypeNumber:
		return "number"
	case spec.TypeBoolean:
		return string(constants.FieldTypeBoolean)
	case spec.TypeDate:
		return string(constants.FieldTypeDate)
	case spec.TypeDatetime:
		return "datetime"
	default:
		return ""
	}
}

func cloneStringMap(in map[string]string) map[string]string {
	if len(in) == 0 {
		return nil
	}
	out := make(map[string]string, len(in))
	for k, v := range in {
		out[k] = v
	}
	return out
}

func compileLookupValue(n *FuncCall, ctx *CompileContext) (string, error) {
	if len(n.Args) < 4 || len(n.Args)%2 != 0 {
		return "", fmt.Errorf("LOOKUPVALUE requires target table, output field, and one or more field/value pairs")
	}

	targetTable, ok := stringLiteralArg(n.Args[0])
	if !ok {
		return "", fmt.Errorf("LOOKUPVALUE target table must be a string literal")
	}
	outputField, ok := stringLiteralArg(n.Args[1])
	if !ok {
		return "", fmt.Errorf("LOOKUPVALUE output field must be a string literal")
	}
	if err := validateIdentifier(targetTable, "LOOKUPVALUE target table"); err != nil {
		return "", err
	}
	if err := validateIdentifier(outputField, "LOOKUPVALUE output field"); err != nil {
		return "", err
	}

	pairs := make([]lookupValueArg, 0, (len(n.Args)-2)/2)
	for i := 2; i < len(n.Args); i += 2 {
		matchField, ok := stringLiteralArg(n.Args[i])
		if !ok {
			return "", fmt.Errorf("LOOKUPVALUE match field at argument %d must be a string literal", i+1)
		}
		if err := validateIdentifier(matchField, "LOOKUPVALUE match field"); err != nil {
			return "", err
		}
		pairs = append(pairs, lookupValueArg{matchField: matchField, value: n.Args[i+1]})
	}

	rule, ok := lookupValueRuleFor(ctx, targetTable, outputField, pairs)
	if !ok {
		return "", fmt.Errorf("LOOKUPVALUE is only available to allowlisted standard projections")
	}

	// Fast path: this lookupValue was hoisted to a pre-registered LEFT JOIN.
	// Emit <alias>.<outputField> instead of a correlated scalar subquery.
	if rule.HoistedJoinAlias != "" {
		if err := validateIdentifier(rule.HoistedJoinAlias, "lookupValue join alias"); err != nil {
			return "", err
		}
		return fmt.Sprintf("%s.`%s`", rule.HoistedJoinAlias, outputField), nil
	}

	predicates := make([]string, 0, len(pairs)+1)
	for _, pair := range pairs {
		valueSQL, err := compile(pair.value, ctx)
		if err != nil {
			return "", err
		}
		predicates = append(predicates, fmt.Sprintf("lv.`%s` = %s", pair.matchField, valueSQL))
	}
	if rule.ExcludeDeleted {
		predicates = append(predicates, "(lv.`IsDeleted` = 0 OR lv.`IsDeleted` IS NULL)")
	}

	return fmt.Sprintf(
		"(SELECT lv.`%s` FROM `%s` lv WHERE %s LIMIT 1)",
		outputField,
		targetTable,
		strings.Join(predicates, " AND "),
	), nil
}

type lookupValueArg struct {
	matchField string
	value      Node
}

func lookupValueRuleFor(ctx *CompileContext, targetTable, outputField string, pairs []lookupValueArg) (LookupValueRule, bool) {
	if ctx == nil || len(ctx.AllowedLookupValues) == 0 {
		return LookupValueRule{}, false
	}
	for _, rule := range ctx.AllowedLookupValues {
		if !strings.EqualFold(rule.TargetTable, targetTable) || !strings.EqualFold(rule.OutputField, outputField) {
			continue
		}
		if len(rule.MatchPairs) != len(pairs) {
			continue
		}

		matches := true
		for i, pair := range pairs {
			rulePair := rule.MatchPairs[i]
			if !strings.EqualFold(rulePair.MatchField, pair.matchField) {
				matches = false
				break
			}
			sourceField, ok := fieldRefName(pair.value)
			if !ok || !strings.EqualFold(rulePair.SourceField, sourceField) {
				matches = false
				break
			}
		}
		if matches {
			return rule, true
		}
	}
	return LookupValueRule{}, false
}

func fieldRefName(node Node) (string, bool) {
	ref, ok := node.(*FieldRef)
	if !ok {
		return "", false
	}
	return ref.Name, true
}

func stringLiteralArg(node Node) (string, bool) {
	lit, ok := node.(*StringLit)
	if !ok {
		return "", false
	}
	return lit.Value, true
}