package query

import (
	"fmt"
	"strings"

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

// Select specifies which fields to select
func (b *Builder) Select(fields []string) *Builder {
	if b.queryType != QueryTypeSelect {
		return b
	}

	if len(fields) == 1 && fields[0] == "*" {
		b.fields = append(b.fields, "*")
		return b
	}

	for _, field := range fields {
		// Resolve logical -> physical
		resolvedField := b.resolveColumnName(field)

		// Add field with table prefix if not already prefixed or quoted
		col := resolvedField
		if !strings.Contains(resolvedField, ".") && !strings.Contains(resolvedField, "`") && resolvedField != "*" {
			col = fmt.Sprintf("`%s`.`%s`", b.table, resolvedField)
		}

		// STRICT ALIASING: If resolved name differs from logical field name, we MUST alias it back.
		// Or even if it's the same, it's safer to alias to guarantee the SObject key matches the requested field.
		// However, to keep SQL clean, we only alias if needed or if complex.
		// The key requirement is: The output column name MUST match 'field' (logical name).

		// Case 1: Logical schema differs from physical definition
		// Case 2: We want to ensure consistent casing/naming
		// STRICT ALIASING: Always alias to ensure casing matches logical definition.
		// Use result column name matching strictly the requested field name.
		if resolvedField != field {
			b.fields = append(b.fields, fmt.Sprintf("%s AS `%s`", col, field))
		} else {
			b.fields = append(b.fields, col)
		}
	}

	return b
}

// AddSelectRaw adds a raw select expression
func (b *Builder) AddSelectRaw(expression string, alias ...string) *Builder {
	if b.queryType != QueryTypeSelect {
		return b
	}

	if len(alias) > 0 && alias[0] != "" {
		b.fields = append(b.fields, fmt.Sprintf("%s as `%s`", expression, alias[0]))
	} else {
		b.fields = append(b.fields, expression)
	}
	return b
}

// WithOptimizerHint sets an inline TiDB optimizer-hint block emitted right
// after the SELECT keyword (e.g. "/*+ SET_VAR(tidb_enforce_mpp=ON) */").
// Only valid on SELECT queries; ignored otherwise.
func (b *Builder) WithOptimizerHint(hint string) *Builder {
	if b.queryType != QueryTypeSelect {
		return b
	}
	b.optimizerHint = hint
	return b
}

// UseIndex selects one table-local index for this SELECT. It is intended for
// hot paths whose owning component already verifies that exact physical index
// at readiness, so data growth cannot make the optimizer fall back to a broad
// but superficially cheaper access path.
func (b *Builder) UseIndex(index string) *Builder {
	if b.queryType != QueryTypeSelect {
		return b
	}
	b.useIndex = strings.TrimSpace(index)
	return b
}

func (b *Builder) buildSelect() string {
	// Process deferred conditions (aliasing)
	b.processDeferredWheres()

	var parts []string

	// SELECT
	// Ensure ID is present if not skipped
	if !b.skipID && b.queryType == QueryTypeSelect {
		hasID := false
		idCol := b.resolveColumnName(constants.FieldID)

		// Check both resolved physical name AND logical name in fields
		// Fields are already resolved in Select(), but user might have added raw strings?
		// Actually Select() resolves them.

		for _, f := range b.fields {
			if strings.Contains(f, idCol) || f == "*" {
				hasID = true
				break
			}
		}

		if !hasID {
			// Prepend ID
			b.fields = append([]string{fmt.Sprintf("`%s`.`%s`", b.table, idCol)}, b.fields...)
		}
	}

	fields := "*"
	if len(b.fields) > 0 {
		fields = strings.Join(b.fields, ", ")
	}

	// Build the SELECT clause with DISTINCT if needed
	selectClause := "SELECT "
	if b.optimizerHint != "" {
		selectClause += b.optimizerHint + " "
	}
	if b.distinct {
		selectClause += "DISTINCT "
	}
	selectClause += fmt.Sprintf("%s FROM `%s`", fields, b.table)
	if b.useIndex != "" {
		selectClause += fmt.Sprintf(" USE INDEX (`%s`)", strings.ReplaceAll(b.useIndex, "`", "``"))
	}
	parts = append(parts, selectClause)

	// JOINs
	if len(b.joins) > 0 {
		parts = append(parts, strings.Join(b.joins, " "))
	}

	// WHERE
	if len(b.whereClauses) > 0 {
		parts = append(parts, fmt.Sprintf("WHERE %s", strings.Join(b.whereClauses, " AND ")))
	}

	// GROUP BY
	if b.groupBy != "" {
		parts = append(parts, b.groupBy)
	}

	// HAVING
	if len(b.havingClauses) > 0 {
		parts = append(parts, fmt.Sprintf("HAVING %s", strings.Join(b.havingClauses, " AND ")))
	}

	// ORDER BY
	if b.orderBy != "" {
		parts = append(parts, b.orderBy)
	}

	// LIMIT
	if b.limit != nil {
		parts = append(parts, fmt.Sprintf("LIMIT %d", *b.limit))
	}

	// OFFSET
	if b.offset != nil {
		parts = append(parts, fmt.Sprintf("OFFSET %d", *b.offset))
	}

	return strings.Join(parts, " ")
}