package analytics

import (
	"context"
	"fmt"
	"strings"

	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/formulastorage"
	"github.com/nexuscrm/shared/pkg/models"
)

type validationFormulaQueryExecutor interface {
	ExecuteRawSQL(ctx context.Context, sql string, params []interface{}) ([]models.SObject, error)
}

// NewValidationActiveFormulaResolver projects formula-backed active fields
// through the formula-view/compiler SQL path instead of re-running the raw
// formula text through pkg/formula's narrower runtime evaluator.
func NewValidationActiveFormulaResolver(
	viewSvc *FormulaViewService,
	queryExec validationFormulaQueryExecutor,
) func(ctx context.Context, schema *models.ObjectMetadata, record models.SObject, field *models.FieldMetadata) (interface{}, error) {
	if viewSvc == nil || queryExec == nil {
		return nil
	}

	return func(ctx context.Context, schema *models.ObjectMetadata, record models.SObject, field *models.FieldMetadata) (interface{}, error) {
		return viewSvc.evaluateValidationActiveFormulaField(ctx, schema, record, field, queryExec)
	}
}

func (s *FormulaViewService) evaluateValidationActiveFormulaField(
	ctx context.Context,
	schema *models.ObjectMetadata,
	record models.SObject,
	field *models.FieldMetadata,
	queryExec validationFormulaQueryExecutor,
) (interface{}, error) {
	if s == nil || schema == nil || field == nil || queryExec == nil {
		return nil, nil
	}

	expr, lookupRules, err := s.formulaExpressionAndRulesForField(ctx, schema, *field)
	if err != nil {
		return nil, err
	}
	if strings.TrimSpace(expr) == "" {
		return nil, nil
	}

	returnType := "Text"
	if field.ReturnType != nil && strings.TrimSpace(string(*field.ReturnType)) != "" {
		returnType = string(*field.ReturnType)
	}

	defs, err := s.attachSelfProjectionDeps(ctx, schema.APIName, schema, []FormulaDefinition{{
		Name:             field.APIName,
		ObjectName:       schema.APIName,
		Expression:       expr,
		ReturnType:       returnType,
		LookupValueRules: lookupRules,
	}})
	if err != nil {
		return nil, err
	}
	if len(defs) != 1 {
		return nil, fmt.Errorf("expected exactly one validation formula definition for %s.%s", schema.APIName, field.APIName)
	}
	def := defs[0]

	ast, err := Parse(def.Expression)
	if err != nil {
		return nil, fmt.Errorf("parse active formula %s.%s: %w", schema.APIName, field.APIName, err)
	}

	validJoins, err := s.validationFormulaJoinSpecs(ctx, schema.APIName, schema, []FormulaDefinition{def})
	if err != nil {
		return nil, err
	}

	aggJoins, err := s.validationFormulaAggregateJoins(ctx, schema, def)
	if err != nil {
		return nil, err
	}

	deepJoins, err := s.resolveDeepJoinChains(ctx, schema.APIName, schema, []FormulaDefinition{def})
	if err != nil {
		return nil, err
	}

	joinAliases := make(map[string]string)
	ambiguousTargets := crossRefAmbiguousTargetKeys(validJoins)
	for _, j := range validJoins {
		logicalTarget := j.TargetObject
		if strings.TrimSpace(logicalTarget) == "" {
			logicalTarget = j.TargetTable
		}
		for _, key := range crossRefObjectKeysForPlan(ambiguousTargets, logicalTarget, j) {
			addJoinAliasKey(joinAliases, key, j.Alias)
		}
		addJoinAliasKey(joinAliases, j.Alias, j.Alias)
	}

	crossRefFieldAliases := s.buildCrossRefFieldAliasMap(ctx, validJoins)
	fieldTypes := s.buildFieldTypeMap(ctx, schema, validJoins, deepJoins, crossRefFieldAliases)
	functionExecutables, err := s.activeFunctionExecutables(ctx)
	if err != nil {
		return nil, fmt.Errorf("resolve formula function authority for %s.%s: %w", schema.APIName, field.APIName, err)
	}
	compileCtx := &CompileContext{
		TableAlias:           "t",
		JoinAliases:          joinAliases,
		CrossRefFieldAliases: crossRefFieldAliases,
		DeepJoinMap:          buildDeepJoinMap(deepJoins),
		QualifyFields:        true,
		FieldTypes:           fieldTypes,
		FunctionExecutables:  functionExecutables,
	}

	sqlExpr, err := Compile(ast, compileContextForFormula(compileCtx, def))
	if err != nil {
		return nil, fmt.Errorf("compiling %s.%s: %w", schema.APIName, field.APIName, err)
	}

	builder := NewDSLViewBuilder()
	sqlExpr, err = builder.resolveAggregateMarkers(sqlExpr, ViewBuildConfig{AggJoins: aggJoins}, aggregateJoinReference)
	if err != nil {
		return nil, fmt.Errorf("resolving aggregates for %s.%s: %w", schema.APIName, field.APIName, err)
	}
	sqlExpr = fromFormulaDomainSQL(sqlExpr, def.ReturnType)

	sourceSQL, params, err := validationFormulaSourceSQL(schema, record)
	if err != nil {
		return nil, err
	}

	var sql strings.Builder
	sql.WriteString("SELECT ")
	sql.WriteString(sqlExpr)
	sql.WriteString(" AS `Value`\nFROM (\n  ")
	sql.WriteString(sourceSQL)
	sql.WriteString("\n) t")

	appendFormulaAggregateJoins(&sql, aggJoins, "t", "", "  ")
	appendFormulaRelationshipJoins(&sql, validJoins, "t", "", ViewName)
	appendFormulaDeepJoins(&sql, deepJoins, "", ViewName)

	sql.WriteString("\nLIMIT 1")

	rows, err := queryExec.ExecuteRawSQL(ctx, sql.String(), params)
	if err != nil {
		return nil, err
	}
	if len(rows) == 0 {
		return nil, nil
	}
	value, ok := validationQueryFieldValue(rows[0], "Value")
	if !ok {
		return nil, fmt.Errorf("validation formula query for %s.%s returned no Value column", schema.APIName, field.APIName)
	}
	return value, nil
}

func (s *FormulaViewService) validationFormulaJoinSpecs(
	ctx context.Context,
	objectName string,
	schema *models.ObjectMetadata,
	defs []FormulaDefinition,
) ([]JoinSpec, error) {
	usedAliases := make(map[string]bool)
	joins := make([]JoinSpec, 0, len(schema.Fields))
	for _, f := range schema.Fields {
		if join, ok := relationshipJoinSpecForField(f, usedAliases); ok {
			joins = append(joins, join)
		}
	}

	usedCrossRefs, err := formulaCrossRefObjects(defs)
	if err != nil {
		return nil, err
	}
	ambiguousTargets := crossRefAmbiguousTargetKeys(joins)
	validJoins := make([]JoinSpec, 0, len(joins))
	for _, j := range joins {
		if !joinReferencedByFormula(j, usedCrossRefs, ambiguousTargets) {
			continue
		}

		targetSchema := s.metadata.GetSchema(ctx, j.TargetTable)
		readsActiveComputed := false
		if targetSchema != nil {
			for _, resolvedField := range crossRefFieldsReadOnTarget(j, defs, targetSchema, ambiguousTargets) {
				if formulastorage.SchemaFieldIsActiveComputed(targetSchema, resolvedField) {
					readsActiveComputed = true
					break
				}
			}
		}

		if strings.EqualFold(j.TargetTable, objectName) && readsActiveComputed {
			j.TargetObject = j.TargetTable
			j.TargetTable = ViewName(j.TargetTable)
			j.UsesCurrentObjectProjection = true
			validJoins = append(validJoins, j)
			continue
		}

		actualTable, err := s.joinTargetForFields(ctx, j.TargetTable, readsActiveComputed)
		if err != nil {
			return nil, fmt.Errorf("reference target table %q for relationship %q is not materialized", j.TargetTable, j.RelationshipName)
		}
		j.TargetObject = j.TargetTable
		j.TargetTable = actualTable
		validJoins = append(validJoins, j)
	}
	return validJoins, nil
}

func (s *FormulaViewService) validationFormulaAggregateJoins(
	ctx context.Context,
	schema *models.ObjectMetadata,
	def FormulaDefinition,
) ([]AggregateJoinSpec, error) {
	ast, err := Parse(def.Expression)
	if err != nil {
		return nil, err
	}

	aggregates := extractAggregates(ast)
	if len(aggregates) == 0 {
		return nil, nil
	}

	aggJoins := make([]AggregateJoinSpec, 0, len(aggregates))
	for idx, agg := range aggregates {
		spec, err := s.resolveAggregateJoinSpec(ctx, schema, agg, idx)
		if err != nil {
			return nil, fmt.Errorf("aggregate in %s.%s: %w", schema.APIName, def.Name, err)
		}
		aggJoins = append(aggJoins, spec)
	}
	return aggJoins, nil
}

func validationFormulaSourceSQL(
	schema *models.ObjectMetadata,
	record models.SObject,
) (string, []interface{}, error) {
	if schema == nil {
		return "", nil, fmt.Errorf("validation formula source schema is required")
	}

	seen := make(map[string]bool)
	cols := make([]string, 0, len(schema.Fields)+1)
	params := make([]interface{}, 0, len(schema.Fields)+1)
	addColumn := func(alias string, value interface{}) {
		if alias == "" || seen[strings.ToLower(alias)] {
			return
		}
		seen[strings.ToLower(alias)] = true
		cols = append(cols, fmt.Sprintf("? AS `%s`", alias))
		params = append(params, value)
	}

	for _, field := range schema.Fields {
		addColumn(field.APIName, validationFormulaRecordValue(record, field.APIName))
	}

	if alias := logicalNamePhysicalAlias(schema); alias != "" {
		addColumn(constants.FieldName, validationFormulaLogicalNameValue(record, alias))
	}

	if len(cols) == 0 {
		addColumn(constants.FieldID, nil)
	}
	return "SELECT " + strings.Join(cols, ", "), params, nil
}

func validationFormulaRecordValue(record models.SObject, fieldName string) interface{} {
	if record == nil {
		return nil
	}
	if value, ok := record[fieldName]; ok {
		return value
	}
	for key, value := range record {
		if strings.EqualFold(key, fieldName) {
			return value
		}
	}
	return nil
}

func validationFormulaLogicalNameValue(record models.SObject, physicalAlias string) interface{} {
	if value := validationFormulaRecordValue(record, constants.FieldName); value != nil {
		return value
	}
	return validationFormulaRecordValue(record, physicalAlias)
}

func validationQueryFieldValue(record models.SObject, fieldName string) (interface{}, bool) {
	if value, ok := record[fieldName]; ok {
		return value, true
	}
	for key, value := range record {
		if strings.EqualFold(key, fieldName) {
			return value, true
		}
	}
	return nil, false
}