package query

import (
	"fmt"
	"strings"
)

// isValidIdentifier checks if a name contains only safe characters (alphanumeric + underscore + dot).
// Backtick-wrapped identifiers (e.g. `Name`) are accepted after stripping the backticks.
func isValidIdentifier(name string) bool {
	// Strip surrounding backticks if present
	n := strings.TrimPrefix(strings.TrimSuffix(name, "`"), "`")
	if len(n) == 0 {
		return false
	}
	for _, c := range n {
		if !((c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z') || (c >= '0' && c <= '9') || c == '_' || c == '.') {
			return false
		}
	}
	return true
}

// sanitizeDirection normalizes and validates sort direction, defaulting to ASC
func sanitizeDirection(direction string) string {
	d := strings.ToUpper(strings.TrimSpace(direction))
	if d == "ASC" || d == "DESC" {
		return d
	}
	return "ASC"
}

// OrderBy adds ORDER BY clause
func (b *Builder) OrderBy(field string, direction string) *Builder {
	if b.queryType != QueryTypeSelect {
		return b
	}

	// Resolve logical -> physical
	resolvedField := b.resolveColumnName(field)

	// Validate column name to prevent SQL injection
	if !isValidIdentifier(resolvedField) {
		return b
	}

	// Validate direction
	direction = sanitizeDirection(direction)

	// Add table prefix if not present
	col := resolvedField
	if !strings.Contains(resolvedField, ".") && !strings.Contains(resolvedField, "`") {
		col = fmt.Sprintf("`%s`.`%s`", b.table, resolvedField)
	}

	b.orderBy = fmt.Sprintf("ORDER BY %s %s", col, direction)
	return b
}

// AddOrderBy adds an additional ORDER BY clause
func (b *Builder) AddOrderBy(field string, direction string) *Builder {
	if b.queryType != QueryTypeSelect {
		return b
	}

	// Resolve logical -> physical
	resolvedField := b.resolveColumnName(field)

	// Validate column name to prevent SQL injection
	if !isValidIdentifier(resolvedField) {
		return b
	}

	// Validate direction
	direction = sanitizeDirection(direction)

	// Add table prefix if not present
	col := resolvedField
	if !strings.Contains(resolvedField, ".") && !strings.Contains(resolvedField, "`") {
		col = fmt.Sprintf("`%s`.`%s`", b.table, resolvedField)
	}

	if b.orderBy == "" {
		b.orderBy = fmt.Sprintf("ORDER BY %s %s", col, direction)
	} else {
		b.orderBy += fmt.Sprintf(", %s %s", col, direction)
	}
	return b
}

// OrderByRaw sets a raw ORDER BY clause (caller is responsible for safety)
func (b *Builder) OrderByRaw(sql string, params []interface{}) *Builder {
	if b.queryType != QueryTypeSelect {
		return b
	}
	b.orderBy = fmt.Sprintf("ORDER BY %s", sql)
	b.params = append(b.params, params...)
	return b
}

// GroupBy adds GROUP BY clause
func (b *Builder) GroupBy(field string) *Builder {
	if b.queryType != QueryTypeSelect {
		return b
	}

	// Resolve logical -> physical
	resolvedField := b.resolveColumnName(field)

	col := resolvedField
	if !strings.Contains(resolvedField, ".") && !strings.Contains(resolvedField, "`") {
		col = fmt.Sprintf("`%s`.`%s`", b.table, resolvedField)
	}

	if b.groupBy == "" {
		b.groupBy = fmt.Sprintf("GROUP BY %s", col)
	} else {
		b.groupBy += fmt.Sprintf(", %s", col)
	}
	return b
}

// GroupByRaw adds a raw GROUP BY clause
func (b *Builder) GroupByRaw(sql string) *Builder {
	if b.queryType != QueryTypeSelect {
		return b
	}

	b.groupBy = fmt.Sprintf("GROUP BY %s", sql)
	return b
}

// HavingRaw adds a raw HAVING clause
func (b *Builder) HavingRaw(sql string, params []interface{}) *Builder {
	if b.queryType != QueryTypeSelect {
		return b
	}

	if sql != "" {
		b.havingClauses = append(b.havingClauses, sql)
		b.params = append(b.params, params...)
	}
	return b
}

// Limit adds LIMIT clause
func (b *Builder) Limit(n int) *Builder {
	if b.queryType != QueryTypeSelect {
		return b
	}

	b.limit = &n
	return b
}

// Offset adds OFFSET clause
func (b *Builder) Offset(n int) *Builder {
	if b.queryType != QueryTypeSelect {
		return b
	}

	b.offset = &n
	return b
}

// Distinct adds DISTINCT to the SELECT clause
func (b *Builder) Distinct() *Builder {
	if b.queryType != QueryTypeSelect {
		return b
	}

	b.distinct = true
	return b
}