package query

import (
	"fmt"
	"sort"
	"strings"
)

// ValuesBatch sets multiple rows for INSERT (Batch Insert)
func (b *Builder) ValuesBatch(records []map[string]interface{}) *Builder {
	if b.queryType != QueryTypeInsert {
		return b
	}
	b.batchValues = records
	return b
}

// Set sets values for UPDATE query
func (b *Builder) Set(data map[string]interface{}) *Builder {
	if b.queryType != QueryTypeUpdate {
		return b
	}

	b.values = data
	return b
}

// OnDuplicateKeyUpdate adds ON DUPLICATE KEY UPDATE clause for INSERT
func (b *Builder) OnDuplicateKeyUpdate(data map[string]interface{}) *Builder {
	if b.queryType != QueryTypeInsert {
		return b
	}
	b.updateValues = data
	return b
}

func (b *Builder) buildInsert() (string, []interface{}) {
	var cols []string
	var params []interface{}

	// Case 1: Batch Insert
	if len(b.batchValues) > 0 {
		// Determine columns: Union of all keys or just from the first record?
		// To follow strictness, we should probably scan consistency or use exact keys if provided.
		// For robustness, let's collect all unique keys from all records.
		// OR, rely on the fact that SObjects usually come with consistent keys.
		// Let's use the union of keys to be safe.
		keyMap := make(map[string]bool)
		for _, rec := range b.batchValues {
			for k := range rec {
				keyMap[k] = true
			}
		}

		// Sort keys for deterministic SQL (and correct column mapping)
		// Iterating map is random.
		keys := make([]string, 0, len(keyMap))
		for k := range keyMap {
			keys = append(keys, k)
		}
		sort.Strings(keys)
		for _, k := range keys {
			cols = append(cols, k)
		}

		// Mapping Logical -> Physical column names for the INSERT statement
		var physicalCols []string
		for _, col := range cols {
			physicalCols = append(physicalCols, fmt.Sprintf("`%s`", b.resolveColumnName(col)))
		}

		// Build Rows
		var rowPlaceholders []string
		for _, rec := range b.batchValues {
			var rowParams []string
			for _, col := range cols {
				val := rec[col]
				if r, ok := val.(Raw); ok {
					rowParams = append(rowParams, string(r))
				} else {
					rowParams = append(rowParams, "?")
					params = append(params, val)
				}
			}
			rowPlaceholders = append(rowPlaceholders, fmt.Sprintf("(%s)", strings.Join(rowParams, ", ")))
		}

		sql := fmt.Sprintf("INSERT INTO `%s` (%s) VALUES %s",
			b.table,
			strings.Join(physicalCols, ", "),
			strings.Join(rowPlaceholders, ", "))

		if len(b.updateValues) > 0 {
			var updates []string
			keys := make([]string, 0, len(b.updateValues))
			for k := range b.updateValues {
				keys = append(keys, k)
			}
			sort.Strings(keys)
			for _, key := range keys {
				val := b.updateValues[key]
				// Special handling for "VALUES(col)" standard SQL reference?
				// Or just static values?
				// Often in batch upsert, we want `col = VALUES(col)`.
				// If the user passed `constants.FieldX: constants.FieldX`, we should detect that?
				// Or explicitly support `Values(col)`.
				// For now simple static values or Raw SQL.

				if r, ok := val.(Raw); ok {
					updates = append(updates, fmt.Sprintf("`%s` = %s", b.resolveColumnName(key), string(r)))
				} else {
					updates = append(updates, fmt.Sprintf("`%s` = ?", b.resolveColumnName(key)))
					params = append(params, val)
				}
			}
			sql += fmt.Sprintf(" ON DUPLICATE KEY UPDATE %s", strings.Join(updates, ", "))
		}

		return sql, params
	}

	// Case 2: Single Record Insert
	var placeholders []string

	keys := make([]string, 0, len(b.values))
	for k := range b.values {
		keys = append(keys, k)
	}
	sort.Strings(keys)
	for _, key := range keys {
		val := b.values[key]
		cols = append(cols, fmt.Sprintf("`%s`", b.resolveColumnName(key)))
		if r, ok := val.(Raw); ok {
			placeholders = append(placeholders, string(r))
		} else {
			placeholders = append(placeholders, "?")
			params = append(params, val)
		}
	}

	sql := fmt.Sprintf("INSERT INTO `%s` (%s) VALUES (%s)",
		b.table,
		strings.Join(cols, ", "),
		strings.Join(placeholders, ", "))

	if len(b.updateValues) > 0 {
		var updates []string
		keys := make([]string, 0, len(b.updateValues))
		for k := range b.updateValues {
			keys = append(keys, k)
		}
		sort.Strings(keys)
		for _, key := range keys {
			val := b.updateValues[key]
			if r, ok := val.(Raw); ok {
				updates = append(updates, fmt.Sprintf("`%s` = %s", b.resolveColumnName(key), string(r)))
			} else {
				updates = append(updates, fmt.Sprintf("`%s` = ?", b.resolveColumnName(key)))
				params = append(params, val)
			}
		}
		sql += fmt.Sprintf(" ON DUPLICATE KEY UPDATE %s", strings.Join(updates, ", "))
	}

	return sql, params
}

func (b *Builder) buildUpdate() (string, []interface{}) {
	// Process deferred conditions (aliasing)
	b.processDeferredWheres()

	var setClauses []string
	var params []interface{}

	keys := make([]string, 0, len(b.values))
	for k := range b.values {
		keys = append(keys, k)
	}
	sort.Strings(keys)
	for _, key := range keys {
		val := b.values[key]
		if r, ok := val.(Raw); ok {
			setClauses = append(setClauses, fmt.Sprintf("`%s` = %s", b.resolveColumnName(key), string(r)))
		} else {
			setClauses = append(setClauses, fmt.Sprintf("`%s` = ?", b.resolveColumnName(key)))
			params = append(params, val)
		}
	}

	sql := fmt.Sprintf("UPDATE `%s` SET %s", b.table, strings.Join(setClauses, ", "))

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

	return sql, params
}

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

	sql := fmt.Sprintf("DELETE FROM `%s`", b.table)

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

	return sql
}

// BulkInsertOrdered generates a multi-row INSERT with explicit column order
// This version ensures consistent column ordering across all records
func BulkInsertOrdered(table string, columns []string, records []map[string]interface{}, resolver func(string) string) (string, []interface{}) {
	if len(records) == 0 || len(columns) == 0 {
		return "", nil
	}

	// Format columns
	var cols []string
	for _, col := range columns {
		physicalName := col
		if resolver != nil {
			physicalName = resolver(col)
		}
		cols = append(cols, fmt.Sprintf("`%s`", physicalName))
	}

	// Generate placeholder rows and collect params
	var rows []string
	var params []interface{}
	placeholders := make([]string, len(columns))
	for i := range placeholders {
		placeholders[i] = "?"
	}
	rowPlaceholder := "(" + strings.Join(placeholders, ", ") + ")"

	for _, record := range records {
		rows = append(rows, rowPlaceholder)
		for _, col := range columns {
			params = append(params, record[col])
		}
	}

	sql := fmt.Sprintf("INSERT INTO `%s` (%s) VALUES %s",
		table,
		strings.Join(cols, ", "),
		strings.Join(rows, ", "))

	return sql, params
}