package analytics

import (
	"fmt"
	"strings"

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

const maxSQLAliasLength = 64

func relationshipNameForField(field models.FieldMetadata) string {
	if field.RelationshipName != nil {
		if rel := strings.TrimSpace(*field.RelationshipName); rel != "" {
			return rel
		}
	}
	return deriveRelationshipName(field.APIName)
}

func relationshipTraversalNamesForField(field models.FieldMetadata) []string {
	seen := map[string]bool{}
	var names []string
	add := func(name string) {
		name = strings.TrimSpace(name)
		if name == "" || seen[strings.ToLower(name)] {
			return
		}
		seen[strings.ToLower(name)] = true
		names = append(names, name)
	}
	if field.RelationshipName != nil {
		add(*field.RelationshipName)
	}
	add(deriveRelationshipName(field.APIName))
	return names
}

func relationshipTraversalNamesForJoin(join JoinSpec) []string {
	field := models.FieldMetadata{
		APIName:     join.SourceField,
		ReferenceTo: []string{join.TargetObject},
	}
	if strings.TrimSpace(join.RelationshipName) != "" {
		field.RelationshipName = &join.RelationshipName
	}
	return relationshipTraversalNamesForField(field)
}

func makeJoinAlias(seed, prefix string, used map[string]bool) string {
	base := joinAliasCandidate(seed, prefix)
	if used == nil {
		return base
	}
	alias := base
	for i := 2; used[alias]; i++ {
		alias = withAliasSuffix(base, fmt.Sprintf("_%d", i))
	}
	used[alias] = true
	return alias
}

func addJoinAliasKey(joinAliases map[string]string, key, alias string) {
	key = strings.TrimSpace(key)
	alias = strings.TrimSpace(alias)
	if joinAliases == nil || key == "" || alias == "" {
		return
	}
	joinAliases[key] = alias
	lowerKey := strings.ToLower(key)
	if lowerKey != key {
		joinAliases[lowerKey] = alias
	}
}

func joinAliasCandidate(seed, prefix string) string {
	body := sanitizeAliasBody(seed)
	if body == "" {
		body = "rel"
	}
	if prefix == "" {
		prefix = "j_"
	}
	return withAliasSuffix(prefix+body, "")
}

func sanitizeAliasBody(seed string) string {
	lower := strings.ToLower(strings.TrimSpace(seed))
	var b strings.Builder
	for i := 0; i < len(lower); i++ {
		ch := lower[i]
		valid := (ch >= 'a' && ch <= 'z') || (ch >= '0' && ch <= '9') || ch == '_'
		if !valid {
			ch = '_'
		}
		b.WriteByte(ch)
	}
	return strings.Trim(b.String(), "_")
}

func withAliasSuffix(base, suffix string) string {
	maxBase := maxSQLAliasLength - len(suffix)
	if maxBase < 1 {
		maxBase = 1
	}
	if len(base) > maxBase {
		base = strings.TrimRight(base[:maxBase], "_")
		if base == "" {
			base = "j"
		}
	}
	return base + suffix
}