package persistence

import (
	"fmt"
	"strings"

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

// validFilterOps is the set of supported SQL comparison operators for criteria filters.
var validFilterOps = map[string]bool{
	"=": true, "!=": true, "<": true, ">": true, "<=": true, ">=": true,
	"LIKE": true, "NOT LIKE": true,
	"IN": true, "NOT IN": true,
}

// resolvePhysicalName maps a logical API name to its physical column name using the schema.
// Returns the original name if no physical mapping exists or schema is nil.
func resolvePhysicalName(schema *models.ObjectMetadata, apiName string) string {
	if schema == nil {
		return apiName
	}
	for _, f := range schema.Fields {
		if strings.EqualFold(f.APIName, apiName) {
			return actualColumnName(f.APIName, f.PhysicalName)
		}
	}
	return apiName
}

func actualColumnName(apiName, physicalName string) string {
	if physicalName == "" {
		return apiName
	}

	trimmed := strings.TrimSpace(physicalName)
	// Bootstrap metadata sometimes stores Salesforce-facing aliases as backticked values
	// while the actual DB column remains the logical API name.
	if strings.HasPrefix(trimmed, "`") && strings.HasSuffix(trimmed, "`") {
		return apiName
	}

	return trimmed
}

// newFieldMapper returns a mapper function suitable for formula.ToSQLWithMapper.
// It resolves API names to physical column names using the schema.
func newFieldMapper(schema *models.ObjectMetadata) func(string) string {
	return func(identifier string) string {
		return resolvePhysicalName(schema, identifier)
	}
}

// schemaHasIsDeleted checks if the schema has an IsDeleted field.
func schemaHasIsDeleted(schema *models.ObjectMetadata) bool {
	for _, f := range schema.Fields {
		if strings.EqualFold(f.APIName, constants.FieldIsDeleted) {
			return true
		}
	}
	return false
}

// isValidFieldName checks if a field name is safe (alphanumeric + underscore + dot)
func isValidFieldName(name string) bool {
	if len(name) == 0 {
		return false
	}
	for _, c := range name {
		if !((c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z') || (c >= '0' && c <= '9') || c == '_' || c == '.') {
			return false
		}
	}
	return true
}

// replaceIdentifiers safely replaces identifiers in a SQL string using a map, ignoring content in quotes.
// It is case-insensitive for matching keys in the map.
func replaceIdentifiers(sql string, replacements map[string]string) string {
	var sb strings.Builder
	var tokenBuilder strings.Builder
	inQuote := false
	var quoteChar rune

	flushToken := func() {
		if tokenBuilder.Len() > 0 {
			token := tokenBuilder.String()
			// Check if token matches a replacement (case-insensitive)
			if replacement, ok := replacements[strings.ToLower(token)]; ok {
				sb.WriteString(replacement)
			} else {
				sb.WriteString(token)
			}
			tokenBuilder.Reset()
		}
	}

	for _, r := range sql {
		if inQuote {
			sb.WriteRune(r)
			if r == quoteChar {
				inQuote = false
			}
			continue
		}

		if r == '\'' || r == '"' || r == '`' {
			flushToken()
			inQuote = true
			quoteChar = r
			sb.WriteRune(r)
			continue
		}

		if (r >= 'a' && r <= 'z') || (r >= 'A' && r <= 'Z') || (r >= '0' && r <= '9') || r == '_' {
			tokenBuilder.WriteRune(r)
		} else {
			flushToken()
			sb.WriteRune(r)
		}
	}
	flushToken()

	return sb.String()
}

// isValidFilterOp checks if the given operator is in the valid set.
func isValidFilterOp(op string) bool {
	return validFilterOps[strings.ToUpper(op)]
}

// buildFieldReplacements builds a map of API name → qualified physical column for HAVING clause resolution.
func buildFieldReplacements(schema *models.ObjectMetadata) map[string]string {
	replacements := make(map[string]string)
	for _, f := range schema.Fields {
		colName := actualColumnName(f.APIName, f.PhysicalName)
		if colName != f.APIName {
			replacements[strings.ToLower(f.APIName)] = fmt.Sprintf("`%s`.`%s`", schema.APIName, colName)
		}
	}
	return replacements
}

// buildQualifiedFieldReplacements builds a map of both API names and physical column
// names to fully-qualified columns for contexts where additional joins can make
// unqualified filter fields ambiguous.
func buildQualifiedFieldReplacements(schema *models.ObjectMetadata, tableAlias string) map[string]string {
	replacements := make(map[string]string)
	if schema == nil || tableAlias == "" {
		return replacements
	}

	for _, f := range schema.Fields {
		colName := actualColumnName(f.APIName, f.PhysicalName)
		qualified := fmt.Sprintf("`%s`.`%s`", tableAlias, colName)
		replacements[strings.ToLower(f.APIName)] = qualified
		replacements[strings.ToLower(colName)] = qualified
	}

	return replacements
}