package query

import (
	"fmt"
	"log"
	"strings"

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

// Where adds a WHERE condition
// Supports deferred aliasing if a simple identifier is passed
func (b *Builder) Where(condition string, value ...interface{}) *Builder {
	// Heuristic: If condition is a simple identifier (AlphaNumeric + _) AND has values
	// Treat it as a deferred field lookup.
	// e.g. Where("id", "123") -> logic will late-resolve column names
	// and generate "`table`.`Id` = ?"
	isSimple := true
	for _, c := range condition {
		if !((c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z') || (c >= '0' && c <= '9') || c == '_') {
			isSimple = false
			break
		}
	}

	// Must have value to imply equality check.
	// If Where("id"), it's raw SQL "id" (boolean check).
	if isSimple && len(value) > 0 {
		b.deferredWheres = append(b.deferredWheres, deferredWhere{
			Field:  condition,
			Values: value,
		})
		return b
	}

	// Standard Raw Logic
	b.whereClauses = append(b.whereClauses, condition)
	if len(value) > 0 {
		b.params = append(b.params, value...)
	}
	return b
}

// WhereRaw adds a raw WHERE condition with parameters
func (b *Builder) WhereRaw(sql string, params []interface{}) *Builder {
	if sql != "" {
		b.whereClauses = append(b.whereClauses, sql)
		b.params = append(b.params, params...)
	}
	return b
}

// WhereOp adds a WHERE condition with a specific operator
// Validates operator against allow-list to prevent SQL injection
func (b *Builder) WhereOp(field string, operator string, value interface{}) *Builder {
	// Validate operator
	validOps := map[string]bool{
		"=": true, "!=": true, "<>": true,
		">": true, "<": true, ">=": true, "<=": true,
		"LIKE": true, "NOT LIKE": true,
		"IN": true, "NOT IN": true,
		"IS": true, "IS NOT": true,
	}
	opUpper := strings.ToUpper(strings.TrimSpace(operator))
	if !validOps[opUpper] {
		log.Printf("⚠️ Warning: Invalid operator '%s' passed to WhereOp. defaulting to '='", operator)
		opUpper = "="
	}

	// IN / NOT IN with slice value — delegate to WhereIn
	if opUpper == "IN" || opUpper == "NOT IN" {
		if vals, ok := value.([]interface{}); ok {
			if opUpper == "NOT IN" {
				return b.whereInInternal(field, vals, true)
			}
			return b.WhereIn(field, vals)
		}
	}

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

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

	clause := fmt.Sprintf("%s %s ?", col, opUpper)
	b.whereClauses = append(b.whereClauses, clause)
	b.params = append(b.params, value)
	return b
}

// whereInInternal adds a NOT IN clause (WhereIn handles IN)
func (b *Builder) whereInInternal(field string, values []interface{}, negate bool) *Builder {
	if len(values) == 0 {
		if negate {
			return b // NOT IN (empty) is always true — no filter needed
		}
		b.whereClauses = append(b.whereClauses, "1=0")
		return b
	}
	resolvedField := b.resolveColumnName(field)
	col := resolvedField
	if !strings.Contains(resolvedField, ".") && !strings.Contains(resolvedField, "`") && b.table != "" {
		col = fmt.Sprintf("`%s`.`%s`", b.table, resolvedField)
	}
	placeholders := make([]string, len(values))
	for i := range values {
		placeholders[i] = "?"
	}
	op := "IN"
	if negate {
		op = "NOT IN"
	}
	clause := fmt.Sprintf("%s %s (%s)", col, op, strings.Join(placeholders, ", "))
	b.whereClauses = append(b.whereClauses, clause)
	b.params = append(b.params, values...)
	return b
}

// WhereNot adds a strict inequality condition (shortcut for WhereOp(field, "!=", value))
func (b *Builder) WhereNot(field string, value interface{}) *Builder {
	return b.WhereOp(field, "!=", value)
}

// WhereIn adds an IN clause with automatic column aliasing
func (b *Builder) WhereIn(field string, values []interface{}) *Builder {
	if len(values) == 0 {
		// IN (empty) is always false
		b.whereClauses = append(b.whereClauses, "1=0")
		return b
	}

	// Resolve logical -> physical
	resolvedField := b.resolveColumnName(field)
	col := resolvedField
	if !strings.Contains(resolvedField, ".") && !strings.Contains(resolvedField, "`") && b.table != "" {
		col = fmt.Sprintf("`%s`.`%s`", b.table, resolvedField)
	}

	placeholders := make([]string, len(values))
	for i := range values {
		placeholders[i] = "?"
	}

	clause := fmt.Sprintf("%s IN (%s)", col, strings.Join(placeholders, ", "))
	b.whereClauses = append(b.whereClauses, clause)
	b.params = append(b.params, values...)

	return b
}

// processDeferredWheres resolves deferred WHERE clauses using the (now populated) schema
func (b *Builder) processDeferredWheres() {
	if len(b.deferredWheres) == 0 {
		return
	}

	for _, dw := range b.deferredWheres {
		// Resolve alias
		col := b.resolveColumnName(dw.Field)

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

		// Construct condition "col = ?"
		// Assuming single value equality because Where() only defers if simple identifier and has params
		// (Currently we support 1 param? Or variadic params?)
		// Logic: If multiple params, it's weird for equality.
		// Usually Where("col", val).
		// If multiple vals, GORM implies "col IN vals", but standard Builder usually implies "col = val".
		// We'll assume simple equality for first param, ignoring extra?
		// Or append all params?

		// Detect Operator (if passed as second value)
		op := "="
		usageVals := dw.Values

		if len(dw.Values) > 1 {
			if operator, ok := dw.Values[1].(string); ok {
				op = operator
				// If operator is present, assume first value is the target value
				usageVals = []interface{}{dw.Values[0]}
			}
		}

		clause := fmt.Sprintf("%s %s ?", col, op)
		b.whereClauses = append(b.whereClauses, clause)
		b.params = append(b.params, usageVals...)
	}

	// Clear to ensure idempotency if called multiple times (though shouldn't happen)
	b.deferredWheres = nil
}

// ExcludeDeleted adds is_deleted = 0 condition
func (b *Builder) ExcludeDeleted() *Builder {
	col := b.resolveColumnName(constants.FieldIsDeleted)
	return b.Where(fmt.Sprintf("`%s`.%s = %d", b.table, col, constants.IsDeletedFalse))
}

// OnlyDeleted adds is_deleted = 1 condition.
func (b *Builder) OnlyDeleted() *Builder {
	col := b.resolveColumnName(constants.FieldIsDeleted)
	return b.Where(fmt.Sprintf("`%s`.%s = %d", b.table, col, constants.IsDeletedTrue))
}

// ApplySecurity applies row-level security based on sharing model
// This will be implemented by the SharingService
func (b *Builder) ApplySecurity(securitySQL string, securityParams []interface{}) *Builder {
	if securitySQL != "" {
		b.whereClauses = append(b.whereClauses, securitySQL)
		b.params = append(b.params, securityParams...)
	}
	return b
}