package query

import (
	"strings"

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

// QueryType represents the type of SQL query
type QueryType string

const (
	QueryTypeSelect QueryType = "SELECT"
	QueryTypeInsert QueryType = "INSERT"
	QueryTypeUpdate QueryType = "UPDATE"
	QueryTypeDelete QueryType = "DELETE"
)

// QueryResult represents the built SQL query and parameters
type QueryResult struct {
	SQL    string
	Params []interface{}
}

// deferredWhere holds a condition that needs
// late resolution (aliasing) using the schema
type deferredWhere struct {
	Field  string
	Values []interface{}
}

// Raw represents a raw SQL string that should not be parameterized
type Raw string

// Builder is a fluent SQL query builder
// Method implementations are split across domain-specific files:
//   - builder_select.go   — Select, AddSelectRaw, buildSelect
//   - builder_where.go    — Where, WhereRaw, WhereOp, WhereIn, etc.
//   - builder_joins.go    — Join, JoinOn
//   - builder_clauses.go  — OrderBy, GroupBy, Limit, Offset
//   - builder_write.go    — ValuesBatch, Set, buildInsert, buildUpdate, buildDelete
type Builder struct {
	queryType     QueryType
	table         string
	fields        []string
	joins         []string
	whereClauses  []string
	havingClauses []string
	params        []interface{}
	orderBy       string
	groupBy       string
	limit         *int
	offset        *int
	values        map[string]interface{}
	batchValues   []map[string]interface{}
	updateValues  map[string]interface{}

	// Deferred conditions for late aliasing
	deferredWheres []deferredWhere

	// Metadata context for smart features
	schema *models.ObjectMetadata
	// Config
	skipID bool
	distinct bool
}

// From creates a new SELECT query builder
func From(table string) *Builder {
	return &Builder{
		queryType:      QueryTypeSelect,
		table:          table,
		fields:         make([]string, 0),
		joins:          make([]string, 0),
		whereClauses:   make([]string, 0),
		havingClauses:  make([]string, 0),
		params:         make([]interface{}, 0),
		deferredWheres: make([]deferredWhere, 0),
	}
}

// Insert creates a new INSERT query builder
func Insert(table string, data map[string]interface{}) *Builder {
	return &Builder{
		queryType:      QueryTypeInsert,
		table:          table,
		values:         data,
		params:         make([]interface{}, 0),
		deferredWheres: make([]deferredWhere, 0),
	}
}

// Update creates a new UPDATE query builder
func Update(table string) *Builder {
	return &Builder{
		queryType:      QueryTypeUpdate,
		table:          table,
		values:         make(map[string]interface{}),
		whereClauses:   make([]string, 0),
		params:         make([]interface{}, 0),
		deferredWheres: make([]deferredWhere, 0),
	}
}

// Delete creates a new DELETE query builder
func Delete(table string) *Builder {
	return &Builder{
		queryType:      QueryTypeDelete,
		table:          table,
		whereClauses:   make([]string, 0),
		params:         make([]interface{}, 0),
		deferredWheres: make([]deferredWhere, 0),
	}
}

// WithMetadata attaches object metadata for smart features
func (b *Builder) WithMetadata(schema *models.ObjectMetadata) *Builder {
	b.schema = schema
	return b
}

// SkipID disables automatic ID column selection
func (b *Builder) SkipID() *Builder {
	b.skipID = true
	return b
}

// resolveColumnName maps logical field name to physical column name using schema
// Enhanced to support heuristic aliasing for System Tables without loaded schema
func (b *Builder) resolveColumnName(field string) string {
	// 0. Safety Check removed: Physical Names naturally align with API Names.

	// 1. Explicit Schema Lookup
	if b.schema != nil {
		for _, f := range b.schema.Fields {
			if strings.EqualFold(f.APIName, field) {
				return queryColumnName(f.APIName, f.PhysicalName)
			}
		}
	}

	return field
}

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

	trimmed := strings.TrimSpace(physicalName)
	if strings.HasPrefix(trimmed, "`") && strings.HasSuffix(trimmed, "`") {
		return apiName
	}

	return trimmed
}

// Build constructs the final SQL query
func (b *Builder) Build() QueryResult {
	var sql string
	var params []interface{}

	switch b.queryType {
	case QueryTypeSelect:
		sql = b.buildSelect()
		params = b.params

	case QueryTypeInsert:
		sql, params = b.buildInsert()

	case QueryTypeUpdate:
		sql, params = b.buildUpdate()

	case QueryTypeDelete:
		sql = b.buildDelete()
		params = b.params
	}

	return QueryResult{
		SQL:    sql,
		Params: params,
	}
}