package analytics

import (
	"context"
	"fmt"
	"strings"

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

func (s *FormulaViewService) buildCrossRefFieldAliasMap(
	ctx context.Context,
	joins []JoinSpec,
) map[string]string {
	out := make(map[string]string)
	ambiguousTargets := crossRefAmbiguousTargetKeys(joins)
	for _, j := range joins {
		// Use the logical object name (TargetObject) for schema lookup: TargetTable
		// may be a formula view name (e.g. "v_account") when view-routed, which has
		// no metadata entry and would return nil from GetSchema.
		lookupKey := j.TargetObject
		if lookupKey == "" {
			lookupKey = j.TargetTable
		}
		related := s.metadata.GetSchema(ctx, lookupKey)
		if related == nil {
			continue
		}
		if metadataFieldExists(related, constants.FieldName) {
			continue
		}
		nameField := metadataNameField(related)
		if nameField == "" || strings.EqualFold(nameField, constants.FieldName) {
			continue
		}
		for _, key := range crossRefObjectKeysForPlan(ambiguousTargets, related.APIName, j) {
			out[key+"."+constants.FieldName] = nameField
		}
	}
	return out
}

// buildCrossRefFieldExpressionMap lowers virtual compound relationship fields
// from immutable schema members. The resulting SQL is internal compiler input,
// not App-authored SQL: identifiers come only from validated metadata and JOIN
// aliases produced by the view planner.
func (s *FormulaViewService) buildCrossRefFieldExpressionMap(
	ctx context.Context,
	joins []JoinSpec,
) (map[string]string, error) {
	out := make(map[string]string)
	ambiguousTargets := crossRefAmbiguousTargetKeys(joins)
	for _, join := range joins {
		lookupKey := join.TargetObject
		if lookupKey == "" {
			lookupKey = join.TargetTable
		}
		related := s.metadata.GetSchema(ctx, lookupKey)
		if related == nil {
			continue
		}
		logicalProjection := strings.EqualFold(
			strings.TrimSpace(join.TargetTable),
			ViewName(related.APIName),
		)
		for index := range related.Fields {
			field := &related.Fields[index]
			if !field.IsCompound || len(field.ComposedFrom) == 0 {
				continue
			}
			expression, err := compoundRelationshipReadExpression(
				related,
				field,
				join.Alias,
				logicalProjection,
			)
			if err != nil {
				return nil, fmt.Errorf(
					"relationship compound field %s.%s: %w",
					related.APIName,
					field.APIName,
					err,
				)
			}
			for _, key := range crossRefObjectKeysForPlan(ambiguousTargets, related.APIName, join) {
				out[key+"."+field.APIName] = expression
			}
		}
	}
	return out, nil
}

func compoundRelationshipReadExpression(
	schema *models.ObjectMetadata,
	field *models.FieldMetadata,
	tableAlias string,
	logicalProjection bool,
) (string, error) {
	if schema == nil || field == nil || strings.TrimSpace(tableAlias) == "" {
		return "", fmt.Errorf("schema, field, and table alias are required")
	}
	if !field.IsCompound || len(field.ComposedFrom) == 0 {
		return "", fmt.Errorf("field is not a declared compound projection")
	}
	parts := make([]string, 0, len(field.ComposedFrom))
	seen := make(map[string]struct{}, len(field.ComposedFrom))
	for _, memberName := range field.ComposedFrom {
		member := models.FindField(schema, memberName)
		if member == nil {
			return "", fmt.Errorf("member %s is absent", memberName)
		}
		memberKey := strings.ToLower(strings.TrimSpace(member.APIName))
		if memberKey == strings.ToLower(strings.TrimSpace(field.APIName)) {
			return "", fmt.Errorf("field contains itself")
		}
		if _, duplicate := seen[memberKey]; duplicate {
			return "", fmt.Errorf("member %s is repeated", member.APIName)
		}
		seen[memberKey] = struct{}{}
		if member.IsVirtual || member.IsCompound {
			return "", fmt.Errorf("nested virtual member %s is unsupported", member.APIName)
		}
		columnName := member.APIName
		if !logicalProjection && strings.TrimSpace(member.PhysicalName) != "" {
			if physicalName := analyticsPhysicalColumnName(member.APIName, member.PhysicalName); physicalName != "" {
				columnName = physicalName
			}
		}
		parts = append(parts, fmt.Sprintf(
			"NULLIF(TRIM(%s.`%s`), '')",
			tableAlias,
			columnName,
		))
	}
	return fmt.Sprintf(
		"NULLIF(TRIM(CONCAT_WS(' ', %s)), '')",
		strings.Join(parts, ", "),
	), nil
}

// buildFieldTypeMap assembles the FieldType lookup the compiler consults to
// decide whether a BinaryOp between two operands is date-arithmetic. Keys:
//   - "<field>"         for fields on the primary schema
//   - "<object>.<field>" for fields reachable via a 1-hop JOIN
//   - "<a>.<b>.<c>"      for fields reachable via a multi-hop DeepJoin
//
// Unresolved keys default to empty string, which the compiler treats as
// unknown (safe fallback to the type-naive emission that existed before).
func (s *FormulaViewService) buildFieldTypeMap(
	ctx context.Context,
	schema *models.ObjectMetadata,
	joins []JoinSpec,
	deepJoins []DeepJoinChain,
	crossRefFieldAliases map[string]string,
) map[string]string {
	out := make(map[string]string)
	ambiguousTargets := crossRefAmbiguousTargetKeys(joins)
	if schema != nil {
		for _, f := range schema.Fields {
			out[f.APIName] = effectiveFieldType(f)
		}
	}
	// 1-hop cross-refs: look up each joined object's schema by logical name
	// (TargetObject) — TargetTable may be a formula view name (e.g. "v_account")
	// when view-routed, which has no metadata entry and returns nil from GetSchema.
	for _, j := range joins {
		lookupKey := j.TargetObject
		if lookupKey == "" {
			lookupKey = j.TargetTable
		}
		related := s.metadata.GetSchema(ctx, lookupKey)
		if related == nil {
			continue
		}
		// AST CrossRef.Object matches the relationship name's object token,
		// typically the relationship name, but formulas may also use the target
		// object's API name. Record every key shape accepted by the join matcher.
		for _, f := range related.Fields {
			for _, objectKey := range crossRefObjectKeysForPlan(ambiguousTargets, related.APIName, j) {
				out[objectKey+"."+f.APIName] = effectiveFieldType(f)
			}
		}
		for logical, physical := range crossRefFieldAliases {
			logicalObject, logicalField, ok := splitCrossRefFieldKey(logical)
			if !ok || !strings.EqualFold(logicalField, constants.FieldName) {
				continue
			}
			if !crossRefKeyMatchesJoin(logicalObject, related.APIName, j, ambiguousTargets) {
				continue
			}
			for _, f := range related.Fields {
				if strings.EqualFold(f.APIName, physical) {
					out[logical] = effectiveFieldType(f)
					break
				}
			}
		}
	}
	// Deep joins: type of the final field at the end of the multi-hop chain.
	for _, dj := range deepJoins {
		if len(dj.Parts) < 2 {
			continue
		}
		// The final hop's logical object carries the field schema. Use LogicalObject
		// for metadata lookup — TargetTable may be a formula view name (e.g.
		// "v_profile") when view-routed, which has no metadata entry.
		if len(dj.Hops) == 0 {
			continue
		}
		finalHop := dj.Hops[len(dj.Hops)-1]
		finalLookup := finalHop.LogicalObject
		if finalLookup == "" {
			finalLookup = finalHop.TargetTable
		}
		related := s.metadata.GetSchema(ctx, finalLookup)
		if related == nil {
			continue
		}
		for _, f := range related.Fields {
			if strings.EqualFold(f.APIName, dj.FinalField) {
				out[strings.Join(dj.Parts, ".")] = effectiveFieldType(f)
				break
			}
		}
	}
	return out
}

func metadataFieldExists(schema *models.ObjectMetadata, fieldName string) bool {
	if schema == nil {
		return false
	}
	for _, f := range schema.Fields {
		if strings.EqualFold(f.APIName, fieldName) {
			return true
		}
	}
	return false
}

func metadataNameField(schema *models.ObjectMetadata) string {
	if schema == nil {
		return ""
	}
	for _, f := range schema.Fields {
		if f.IsNameField {
			return f.APIName
		}
	}
	return ""
}

// logicalNamePhysicalAlias returns the physical display column APIName to use as a
// "Name" alias in the base-object projection, for objects that have no physical
// "Name" column (e.g. sole-autonumber objects where IsNameField is on OrderNumber).
// Returns "" when the IsNameField is view-computed — those fields are produced by
// the formula view itself and must not be treated as physical base-table columns.
func logicalNamePhysicalAlias(schema *models.ObjectMetadata) string {
	if schema == nil || metadataFieldExists(schema, constants.FieldName) {
		return ""
	}
	for _, f := range schema.Fields {
		if !f.IsNameField {
			continue
		}
		if formulastorage.IsActiveComputedField(&f) {
			return ""
		}
		if !strings.EqualFold(f.APIName, constants.FieldName) {
			if f.PhysicalName != "" {
				return analyticsPhysicalColumnName(f.APIName, f.PhysicalName)
			}
			return f.APIName
		}
	}
	return ""
}

func resolveReferencedField(schema *models.ObjectMetadata, leaf string) (string, error) {
	if schema == nil {
		return "", fmt.Errorf("target schema is missing")
	}
	leaf = strings.TrimSpace(leaf)
	if leaf == "" {
		return "", fmt.Errorf("target field is required")
	}
	for _, f := range schema.Fields {
		if strings.EqualFold(f.APIName, leaf) {
			return f.APIName, nil
		}
	}
	if strings.EqualFold(leaf, constants.FieldName) {
		if nameField := metadataNameField(schema); nameField != "" {
			return nameField, nil
		}
	}
	return "", fmt.Errorf("field %q not found on %s", leaf, schema.APIName)
}

func crossRefObjectKeysForPlan(ambiguousTargets map[string]bool, objectAPIName string, join JoinSpec) []string {
	seen := make(map[string]bool)
	keys := make([]string, 0, 4)
	// Prefer the logical object name for key generation; fall back to TargetTable
	// only when TargetObject is not set (e.g. callers that pre-date the split).
	targetKey := crossRefJoinTargetKey(join)
	includeTargetAliases := true
	if ambiguousTargets != nil && ambiguousTargets[strings.ToLower(targetKey)] {
		includeTargetAliases = false
	}
	if includeTargetAliases {
		for _, key := range []string{
			objectAPIName,
			targetKey,
		} {
			if key == "" || seen[strings.ToLower(key)] {
				continue
			}
			seen[strings.ToLower(key)] = true
			keys = append(keys, key)
		}
	}
	for _, key := range relationshipTraversalNamesForJoin(join) {
		if key == "" || seen[strings.ToLower(key)] {
			continue
		}
		seen[strings.ToLower(key)] = true
		keys = append(keys, key)
	}
	return keys
}

func crossRefJoinTargetKey(join JoinSpec) string {
	targetKey := strings.TrimSpace(join.TargetObject)
	if targetKey == "" {
		targetKey = strings.TrimSpace(join.TargetTable)
	}
	return targetKey
}

func crossRefAmbiguousTargetKeys(joins []JoinSpec) map[string]bool {
	counts := make(map[string]int)
	for _, join := range joins {
		// Hoisted lookupValue joins are referenced only by a direct alias column
		// ref; they must not contribute to the CrossRef ambiguity count so that a
		// coexisting j_ join for the same target is not incorrectly marked ambiguous.
		if join.HoistedLookupValue {
			continue
		}
		targetKey := strings.ToLower(crossRefJoinTargetKey(join))
		if targetKey == "" {
			continue
		}
		counts[targetKey]++
	}
	ambiguous := make(map[string]bool)
	for key, count := range counts {
		if count > 1 {
			ambiguous[key] = true
		}
	}
	return ambiguous
}

func splitCrossRefFieldKey(key string) (string, string, bool) {
	parts := strings.Split(key, ".")
	if len(parts) != 2 || parts[0] == "" || parts[1] == "" {
		return "", "", false
	}
	return parts[0], parts[1], true
}

func crossRefKeyMatchesJoin(keyObject, objectAPIName string, join JoinSpec, ambiguousTargets map[string]bool) bool {
	for _, candidate := range crossRefObjectKeysForPlan(ambiguousTargets, objectAPIName, join) {
		if strings.EqualFold(keyObject, candidate) {
			return true
		}
	}
	return false
}

func relationshipJoinSpecForField(f models.FieldMetadata, usedAliases map[string]bool) (JoinSpec, bool) {
	if !constants.IsReferenceType(f.Type) {
		return JoinSpec{}, false
	}
	target, ok := formulaFieldPrimaryTarget(f)
	if !ok {
		return JoinSpec{}, false
	}
	relName := relationshipNameForField(f)
	return JoinSpec{
		Alias:            makeJoinAlias(relName, "j_", usedAliases),
		TargetTable:      target,
		SourceField:      f.APIName,
		TargetField:      "Id",
		RelationshipName: relName,
	}, true
}

func formulaCrossRefObjects(defs []FormulaDefinition) (map[string]bool, error) {
	objects := make(map[string]bool)
	for _, def := range defs {
		ast, err := Parse(def.Expression)
		if err != nil {
			return nil, fmt.Errorf("parse active formula %s: %w", def.Name, err)
		}
		for _, ref := range extractCrossRefs(ast) {
			if ref.Object != "" {
				objects[strings.ToLower(ref.Object)] = true
			}
		}
	}
	return objects, nil
}

func joinReferencedByFormula(join JoinSpec, crossRefObjects map[string]bool, ambiguousTargets map[string]bool) bool {
	if len(crossRefObjects) == 0 {
		return false
	}
	for _, key := range crossRefObjectKeysForPlan(ambiguousTargets, join.TargetTable, join) {
		if crossRefObjects[strings.ToLower(key)] {
			return true
		}
	}
	return false
}

func crossRefFieldsReadOnTarget(
	join JoinSpec,
	defs []FormulaDefinition,
	targetSchema *models.ObjectMetadata,
	ambiguousTargets map[string]bool,
) []string {
	logicalTarget := join.TargetObject
	if logicalTarget == "" {
		logicalTarget = join.TargetTable
	}
	fields := make([]string, 0)
	for _, def := range defs {
		ast, err := Parse(def.Expression)
		if err != nil {
			continue
		}
		for _, ref := range extractCrossRefs(ast) {
			if !crossRefKeyMatchesJoin(ref.Object, logicalTarget, join, ambiguousTargets) {
				continue
			}
			resolvedField, err := resolveReferencedField(targetSchema, ref.Field)
			if err != nil {
				continue
			}
			fields = append(fields, resolvedField)
		}
	}
	return fields
}