pizzasql/pkg/executor/executor.go

package executor

import (
	"fmt"
	"math"
	"math/rand"
	"sort"
	"strconv"
	"strings"

	"github.com/danfragoso/pizzasql-next/pkg/analyzer"
	"github.com/danfragoso/pizzasql-next/pkg/lexer"
	"github.com/danfragoso/pizzasql-next/pkg/parser"
	"github.com/danfragoso/pizzasql-next/pkg/storage"
)

// Executor executes SQL statements.
type Executor struct {
	schema   *storage.SchemaManager
	table    *storage.TableManager
	analyzer *analyzer.Analyzer
	catalog  *analyzer.Catalog

	// Multi-database support
	attachedDatabases map[string]*DatabaseConnection // alias -> connection
	currentDatabase   string                         // current database alias (default is "main")

	// Transaction state
	inTransaction bool
	savepoints    []string     // stack of savepoint names
	txLog         []txLogEntry // transaction log for rollback

	// Subquery context for correlated subqueries
	outerRow storage.Row

	// Per-query cache for non-correlated IN (SELECT ...) subquery results.
	// Keyed by subquery AST pointer; valid for one top-level Execute call.
	subqueryCache map[*parser.SelectStmt]*Result

	// In-memory view registry: view name (lowercase) → SELECT AST.
	views map[string]*parser.SelectStmt
}

// DatabaseConnection represents an attached database.
type DatabaseConnection struct {
	Alias  string
	Path   string // Database path or identifier
	Schema *storage.SchemaManager
	Table  *storage.TableManager
}

// txLogEntry represents a transaction log entry for rollback support.
type txLogEntry struct {
	operation string // "INSERT", "UPDATE", "DELETE"
	table     string
	key       string
	oldData   storage.Row // for UPDATE/DELETE, the original row data
}

// New creates a new executor.
func New(schema *storage.SchemaManager, table *storage.TableManager) *Executor {
	catalog := analyzer.NewCatalog()
	executor := &Executor{
		schema:            schema,
		table:             table,
		analyzer:          analyzer.New(catalog),
		catalog:           catalog,
		attachedDatabases: make(map[string]*DatabaseConnection),
		currentDatabase:   "main",
		views:             make(map[string]*parser.SelectStmt),
	}

	// Register the main database
	executor.attachedDatabases["main"] = &DatabaseConnection{
		Alias:  "main",
		Path:   schema.GetDatabaseName(),
		Schema: schema,
		Table:  table,
	}

	return executor
}

// SyncCatalog synchronizes the analyzer catalog with the storage schema.
func (e *Executor) SyncCatalog() error {
	tables, err := e.schema.ListTables()
	if err != nil {
		return err
	}

	for _, tableName := range tables {
		schema, err := e.schema.GetSchema(tableName)
		if err != nil {
			continue
		}
		// Drop table from catalog if it exists, then recreate with updated schema
		e.catalog.DropTable(tableName)
		e.catalog.CreateTable(schema.ToAnalyzerTableInfo())
	}

	return nil
}

// Execute executes a SQL statement.
func (e *Executor) Execute(stmt parser.Statement) (*Result, error) {
	e.subqueryCache = make(map[*parser.SelectStmt]*Result)
	defer func() { e.subqueryCache = nil }()

	// PRAGMA doesn't need analysis
	if pragma, ok := stmt.(*parser.PragmaStmt); ok {
		return e.executePragma(pragma)
	}

	// EXPLAIN doesn't need analysis
	if explain, ok := stmt.(*parser.ExplainStmt); ok {
		return e.executeExplain(explain)
	}

	// Transaction statements don't need analysis
	switch s := stmt.(type) {
	case *parser.BeginStmt:
		return e.executeBegin(s)
	case *parser.CommitStmt:
		return e.executeCommit(s)
	case *parser.RollbackStmt:
		return e.executeRollback(s)
	case *parser.SavepointStmt:
		return e.executeSavepoint(s)
	case *parser.ReleaseStmt:
		return e.executeRelease(s)
	case *parser.CreateIndexStmt:
		return e.executeCreateIndex(s)
	case *parser.DropIndexStmt:
		return e.executeDropIndex(s)
	case *parser.CreateViewStmt:
		return e.executeCreateView(s)
	case *parser.DropViewStmt:
		return e.executeDropView(s)
	case *parser.AttachStmt:
		return e.executeAttach(s)
	case *parser.DetachStmt:
		return e.executeDetach(s)
	}

	// Analyze first — create a fresh analyzer per call so concurrent requests
	// don't share mutable scope state (e.analyzer.scope would race otherwise).
	a := analyzer.New(e.catalog)
	if err := a.Analyze(stmt); err != nil {
		return nil, err
	}

	switch s := stmt.(type) {
	case *parser.SelectStmt:
		return e.executeSelect(s)
	case *parser.InsertStmt:
		return e.executeInsert(s)
	case *parser.UpdateStmt:
		return e.executeUpdate(s)
	case *parser.DeleteStmt:
		return e.executeDelete(s)
	case *parser.CreateTableStmt:
		return e.executeCreateTable(s)
	case *parser.DropTableStmt:
		return e.executeDropTable(s)
	case *parser.CreateIndexStmt:
		return e.executeCreateIndex(s)
	case *parser.DropIndexStmt:
		return e.executeDropIndex(s)
	case *parser.AlterTableStmt:
		return e.executeAlterTable(s)
	default:
		return nil, fmt.Errorf("unsupported statement type: %T", stmt)
	}
}

// executeSelect executes a SELECT statement (or compound SELECT).
func (e *Executor) executeSelect(stmt *parser.SelectStmt) (*Result, error) {
	if stmt.Compound != nil {
		return e.executeCompound(stmt.Compound)
	}
	if len(stmt.From) == 0 {
		// SELECT without FROM (e.g., SELECT 1+1)
		return e.executeSelectExpr(stmt)
	}

	// Check if FROM clause is a subquery (derived table)
	if stmt.From[0].Subquery != nil {
		return e.executeSelectFromSubquery(stmt)
	}

	tableName := stmt.From[0].Name

	// Transparently expand view references as derived-table subqueries.
	if viewDef, ok := e.views[strings.ToLower(tableName)]; ok {
		alias := stmt.From[0].Alias
		if alias == "" {
			alias = tableName
		}
		modifiedStmt := *stmt
		modifiedFrom := make([]parser.TableRef, len(stmt.From))
		copy(modifiedFrom, stmt.From)
		modifiedFrom[0] = parser.TableRef{Subquery: viewDef, Alias: alias}
		modifiedStmt.From = modifiedFrom
		return e.executeSelectFromSubquery(&modifiedStmt)
	}

	schema, err := e.schema.GetSchema(tableName)
	if err != nil {
		return nil, err
	}

	// Multi-table FROM (comma-separated implicit cross join): collect and cross join all tables,
	// then apply WHERE after. Don't push WHERE down here — conditions reference multiple tables.
	isMultiTable := len(stmt.From) > 1 && stmt.From[0].Join == nil

	// Try to use index for WHERE clause (single-table only)
	var rows []storage.Row
	usedIndex := false

	if stmt.Where != nil && !isMultiTable {
		// Check if we can use an index
		colName, colValue, isEquality := e.extractIndexableCondition(stmt.Where)
		if isEquality {
			// Look for an index on this column
			indexes, _ := e.schema.ListTableIndexes(tableName)
			for _, idx := range indexes {
				if len(idx.Columns) == 1 && strings.EqualFold(idx.Columns[0].Name, colName) {
					// Use this index
					rows, err = e.table.SelectByIndex(tableName, idx.Name, colValue)
					if err == nil {
						usedIndex = true
					}
					break
				}
			}
		}
	}

	// Fall back to full table scan if no index used
	if !usedIndex {
		var filterErr error
		var filter func(storage.Row) bool
		if stmt.Where != nil && stmt.From[0].Alias == "" && !isMultiTable {
			filter = func(row storage.Row) bool {
				val, ferr := e.evalExpr(stmt.Where, row)
				if ferr != nil {
					filterErr = ferr
					return false
				}
				return toBool(val)
			}
		}
		rows, err = e.table.Select(tableName, filter)
		if filterErr != nil {
			return nil, filterErr
		}
	}
	if err != nil {
		return nil, err
	}

	// Add table alias to rows if there's an explicit alias
	if stmt.From[0].Alias != "" {
		for i := range rows {
			rows[i] = e.addTableAlias(rows[i], stmt.From[0].Alias)
		}
	} else if isMultiTable {
		// For multi-table cross joins without alias, prefix columns with table name
		// so WHERE can distinguish t3.a3 from t7.a7.
		for i := range rows {
			rows[i] = e.addTableAlias(rows[i], tableName)
		}
	}

	// Apply WHERE for single-table with alias (after alias mapping so alias.col refs work)
	if stmt.Where != nil && stmt.From[0].Alias != "" && !isMultiTable {
		var filterErr error
		var filtered []storage.Row
		for _, row := range rows {
			val, ferr := e.evalExpr(stmt.Where, row)
			if ferr != nil {
				filterErr = ferr
				break
			}
			if toBool(val) {
				filtered = append(filtered, row)
			}
		}
		if filterErr != nil {
			return nil, filterErr
		}
		rows = filtered
	}

	// Handle explicit JOINs from the first FROM entry (single-table+JOIN path)
	if !isMultiTable && len(stmt.From) > 0 && stmt.From[0].Join != nil {
		rows, err = e.executeJoins(stmt.From[0], rows)
		if err != nil {
			return nil, err
		}
	}

	// Handle implicit cross joins (comma-separated FROM tables)
	if isMultiTable {
		// Build the column-set for each table so we can push WHERE conditions down.
		type tableInfo struct {
			alias   string
			name    string
			colsSet map[string]bool // lower-case column names for this table
		}

		allTableInfos := make([]tableInfo, len(stmt.From))
		for i, tref := range stmt.From {
			alias := tref.Alias
			if alias == "" {
				alias = tref.Name
			}
			sch, _ := e.schema.GetSchema(tref.Name)
			cols := map[string]bool{}
			if sch != nil {
				for _, c := range sch.Columns {
					cols[strings.ToLower(c.Name)] = true
				}
			}
			allTableInfos[i] = tableInfo{alias: alias, name: tref.Name, colsSet: cols}
		}

		// Split WHERE into AND-clauses and determine which tables each clause touches.
		var andClauses []parser.Expr
		if stmt.Where != nil {
			andClauses = splitANDClauses(stmt.Where)
		}

		// For each table, collect conditions that reference only its own columns.
		tableFilters := make([][]parser.Expr, len(stmt.From))
		var crossFilters []parser.Expr
		for _, clause := range andClauses {
			refs := collectColumnRefs(clause)
			ownerIdx := -1
			cross := false
			for _, ref := range refs {
				colLower := strings.ToLower(ref)
				found := -1
				for i, ti := range allTableInfos {
					if ti.colsSet[colLower] {
						if found == -1 {
							found = i
						} else if found != i {
							cross = true
							break
						}
					}
				}
				if cross {
					break
				}
				if found != -1 {
					if ownerIdx == -1 {
						ownerIdx = found
					} else if ownerIdx != found {
						cross = true
						break
					}
				}
			}
			if cross || ownerIdx == -1 {
				crossFilters = append(crossFilters, clause)
			} else {
				tableFilters[ownerIdx] = append(tableFilters[ownerIdx], clause)
			}
		}

		// Build cross-condition adjacency: for each cross filter, record which table indices it touches.
		type crossEdge struct{ a, b int }
		var crossEdges []crossEdge
		for _, clause := range crossFilters {
			refs := collectColumnRefs(clause)
			touched := map[int]bool{}
			for _, ref := range refs {
				cl := strings.ToLower(ref)
				for j, ti := range allTableInfos {
					if ti.colsSet[cl] {
						touched[j] = true
					}
				}
			}
			idxs := make([]int, 0, len(touched))
			for j := range touched {
				idxs = append(idxs, j)
			}
			if len(idxs) == 2 {
				crossEdges = append(crossEdges, crossEdge{idxs[0], idxs[1]})
			}
		}

		// Helper: find cross-conditions applicable when seenSet is fully present.
		applyWhenSeen := func(seenSet map[int]bool, pending []parser.Expr) (applicable, still []parser.Expr) {
			for _, clause := range pending {
				refs := collectColumnRefs(clause)
				ok := true
				for _, ref := range refs {
					cl := strings.ToLower(ref)
					found := false
					for j, tti := range allTableInfos {
						if tti.colsSet[cl] && seenSet[j] {
							found = true
							break
						}
					}
					if !found {
						ok = false
						break
					}
				}
				if ok {
					applicable = append(applicable, clause)
				} else {
					still = append(still, clause)
				}
			}
			return
		}

		// Helper: inline cross-join two row-sets, applying a predicate.
		inlineJoin := func(left, right []storage.Row, pred parser.Expr) []storage.Row {
			out := make([]storage.Row, 0, len(left))
			for _, l := range left {
				for _, r := range right {
					m := make(storage.Row, len(l)+len(r))
					for k, v := range l {
						m[k] = v
					}
					for k, v := range r {
						m[k] = v
					}
					if pred != nil {
						val, _ := e.evalExpr(pred, m)
						if !toBool(val) {
							continue
						}
					}
					out = append(out, m)
				}
			}
			return out
		}

		// Pre-join connected components of "cross-only" tables (0 single-table filters,
		// connected via cross conditions to other cross-only tables).
		// This prevents n^k explosions when bare tables are joined last.
		crossOnlySet := map[int]bool{}
		for i := range stmt.From {
			if len(tableFilters[i]) > 0 {
				continue
			}
			for _, ce := range crossEdges {
				if ce.a == i || ce.b == i {
					crossOnlySet[i] = true
					break
				}
			}
		}

		// BFS: find connected components among cross-only tables.
		compOf := make([]int, len(stmt.From))
		for i := range compOf {
			compOf[i] = -1
		}
		nComps := 0
		for start := range stmt.From {
			if !crossOnlySet[start] || compOf[start] != -1 {
				continue
			}
			queue := []int{start}
			compOf[start] = nComps
			for len(queue) > 0 {
				cur := queue[0]
				queue = queue[1:]
				for _, ce := range crossEdges {
					var nb int = -1
					if ce.a == cur && crossOnlySet[ce.b] {
						nb = ce.b
					} else if ce.b == cur && crossOnlySet[ce.a] {
						nb = ce.a
					}
					if nb >= 0 && compOf[nb] == -1 {
						compOf[nb] = nComps
						queue = append(queue, nb)
					}
				}
			}
			nComps++
		}

		// Group cross-only tables by component.
		compTbls := make([][]int, nComps)
		for i, c := range compOf {
			if c >= 0 {
				compTbls[c] = append(compTbls[c], i)
			}
		}

		// Pre-join each component with ≥2 tables; collect results as virtual units.
		type virtualUnit struct {
			tableIdxs map[int]bool
			rows      []storage.Row
		}
		var virtuals []virtualUnit
		preJoined := map[int]bool{} // original table indices consumed into virtuals
		remaining := make([]parser.Expr, len(crossFilters))
		copy(remaining, crossFilters)

		for _, comp := range compTbls {
			if len(comp) < 2 {
				continue
			}
			// Pick seed: table with most cross-edges within component.
			seed := comp[0]
			for _, idx := range comp[1:] {
				degIdx, degSeed := 0, 0
				for _, ce := range crossEdges {
					if ce.a == idx || ce.b == idx {
						degIdx++
					}
					if ce.a == seed || ce.b == seed {
						degSeed++
					}
				}
				if degIdx > degSeed {
					seed = idx
				}
			}

			// Load seed.
			seedRows, rerr := e.table.Select(stmt.From[seed].Name, nil)
			if rerr != nil {
				return nil, rerr
			}
			for j := range seedRows {
				seedRows[j] = e.addTableAlias(seedRows[j], allTableInfos[seed].alias)
			}
			vSeen := map[int]bool{seed: true}

			// Greedy within-component join.
			compSet := map[int]bool{}
			for _, idx := range comp {
				compSet[idx] = true
			}
			for len(vSeen) < len(comp) {
				// Pick next table in component with cross-edge to vSeen.
				nextC := -1
				for _, idx := range comp {
					if vSeen[idx] {
						continue
					}
					for _, ce := range crossEdges {
						if (ce.a == idx && vSeen[ce.b]) || (ce.b == idx && vSeen[ce.a]) {
							nextC = idx
							break
						}
					}
					if nextC >= 0 {
						break
					}
				}
				if nextC < 0 {
					for _, idx := range comp {
						if !vSeen[idx] {
							nextC = idx
							break
						}
					}
				}

				nextRows, rerr := e.table.Select(stmt.From[nextC].Name, nil)
				if rerr != nil {
					return nil, rerr
				}
				for j := range nextRows {
					nextRows[j] = e.addTableAlias(nextRows[j], allTableInfos[nextC].alias)
				}
				vSeen[nextC] = true

				appl, still := applyWhenSeen(vSeen, remaining)
				remaining = still
				var pred parser.Expr
				if len(appl) > 0 {
					pred = combineAND(appl)
				}
				seedRows = inlineJoin(seedRows, nextRows, pred)
			}

			virtuals = append(virtuals, virtualUnit{tableIdxs: vSeen, rows: seedRows})
			for idx := range vSeen {
				preJoined[idx] = true
			}
		}

		// Build greedy order for non-pre-joined tables.
		// Score: single-table filter count + 1000 × cross-edges to already-joined.
		orderNonPJ := make([]int, 0, len(stmt.From)-len(preJoined))
		inOrderNPJ := make([]bool, len(stmt.From))

		best := -1
		for j := range stmt.From {
			if preJoined[j] {
				continue
			}
			if best < 0 || len(tableFilters[j]) > len(tableFilters[best]) {
				best = j
			}
		}
		if best >= 0 {
			orderNonPJ = append(orderNonPJ, best)
			inOrderNPJ[best] = true
		}
		for len(orderNonPJ)+len(preJoined) < len(stmt.From) {
			joined := map[int]bool{}
			for _, idx := range orderNonPJ {
				joined[idx] = true
			}
			nextIdx := -1
			nextScore := -1
			for j := range stmt.From {
				if inOrderNPJ[j] || preJoined[j] {
					continue
				}
				score := len(tableFilters[j])
				for _, ce := range crossEdges {
					if (ce.a == j && joined[ce.b]) || (ce.b == j && joined[ce.a]) {
						score += 1000
					}
				}
				if score > nextScore {
					nextScore = score
					nextIdx = j
				}
			}
			if nextIdx < 0 {
				for j := range stmt.From {
					if !inOrderNPJ[j] && !preJoined[j] {
						nextIdx = j
						break
					}
				}
			}
			if nextIdx >= 0 {
				orderNonPJ = append(orderNonPJ, nextIdx)
				inOrderNPJ[nextIdx] = true
			}
		}

		// Load the initial rows for the first non-pre-joined table (or use the already-loaded rows).
		seenTables := map[int]bool{}
		if len(orderNonPJ) > 0 {
			first := orderNonPJ[0]
			if first != 0 {
				rows, err = e.table.Select(stmt.From[first].Name, nil)
				if err != nil {
					return nil, err
				}
				for i := range rows {
					rows[i] = e.addTableAlias(rows[i], allTableInfos[first].alias)
				}
			}
			if len(tableFilters[first]) > 0 {
				pred := combineAND(tableFilters[first])
				var filtered []storage.Row
				for _, row := range rows {
					val, _ := e.evalExpr(pred, row)
					if toBool(val) {
						filtered = append(filtered, row)
					}
				}
				rows = filtered
			}
			seenTables[first] = true

			// Join remaining non-pre-joined tables.
			for _, idx := range orderNonPJ[1:] {
				ti := allTableInfos[idx]
				rightRows, rerr := e.table.Select(stmt.From[idx].Name, nil)
				if rerr != nil {
					return nil, rerr
				}
				for j := range rightRows {
					rightRows[j] = e.addTableAlias(rightRows[j], ti.alias)
				}
				if len(tableFilters[idx]) > 0 {
					pred := combineAND(tableFilters[idx])
					var filtered []storage.Row
					for _, row := range rightRows {
						val, _ := e.evalExpr(pred, row)
						if toBool(val) {
							filtered = append(filtered, row)
						}
					}
					rightRows = filtered
				}
				seenTables[idx] = true
				appl, still := applyWhenSeen(seenTables, remaining)
				remaining = still
				var pred parser.Expr
				if len(appl) > 0 {
					pred = combineAND(appl)
				}
				rows = inlineJoin(rows, rightRows, pred)
			}
		} else {
			// All tables were pre-joined; start with empty placeholder.
			rows = []storage.Row{{}}
		}

		// Integrate virtual (pre-joined) units into the result.
		for _, vu := range virtuals {
			for idx := range vu.tableIdxs {
				seenTables[idx] = true
			}
			appl, still := applyWhenSeen(seenTables, remaining)
			remaining = still
			var pred parser.Expr
			if len(appl) > 0 {
				pred = combineAND(appl)
			}
			rows = inlineJoin(rows, vu.rows, pred)
		}

		// Apply any remaining conditions (shouldn't normally happen).
		if len(remaining) > 0 {
			pred := combineAND(remaining)
			var filtered []storage.Row
			for _, row := range rows {
				val, _ := e.evalExpr(pred, row)
				if toBool(val) {
					filtered = append(filtered, row)
				}
			}
			rows = filtered
		}

		// Also process any JOIN clauses within FROM entries (mixed comma+JOIN syntax).
		for _, tref := range stmt.From {
			if tref.Join != nil {
				rows, err = e.executeJoins(tref, rows)
				if err != nil {
					return nil, err
				}
			}
		}
	}

	// Handle GROUP BY
	if len(stmt.GroupBy) > 0 {
		return e.executeGroupBy(stmt, rows, schema)
	}

	// Check for aggregate functions without GROUP BY
	hasAggregate := e.hasAggregates(stmt.Columns)
	if hasAggregate {
		return e.executeAggregateSelect(stmt, rows, schema)
	}

	// Apply ORDER BY
	if len(stmt.OrderBy) > 0 {
		e.sortRows(rows, resolveOrderByPositions(stmt.OrderBy, stmt.Columns))
	}

	// Apply LIMIT/OFFSET
	if stmt.Offset != nil {
		offset := e.evalIntExpr(stmt.Offset)
		if offset < len(rows) {
			rows = rows[offset:]
		} else {
			rows = nil
		}
	}
	if stmt.Limit != nil {
		limit := e.evalIntExpr(stmt.Limit)
		if limit < len(rows) {
			rows = rows[:limit]
		}
	}

	// Build result
	result := NewResult("SELECT")

	// For multi-table or JOIN queries, collect all table refs for SELECT * expansion.
	hasJoin := len(stmt.From) > 0 && stmt.From[0].Join != nil
	allTableRefs := collectAllTableRefs(stmt.From)

	// Determine columns
	for i, col := range stmt.Columns {
		if col.Alias != "" {
			result.AddColumn(col.Alias)
		} else if ref, ok := col.Expr.(*parser.ColumnRef); ok {
			result.AddColumn(ref.Column)
		} else if col.Star {
			if isMultiTable || hasJoin {
				// Add columns from ALL joined tables in order
				for _, tref := range allTableRefs {
					sch, _ := e.schema.GetSchema(tref.Name)
					if sch != nil {
						for _, c := range sch.Columns {
							result.AddColumn(c.Name)
						}
					}
				}
			} else {
				// Handle SELECT * - add all columns from schema
				for _, c := range schema.Columns {
					result.AddColumn(c.Name)
				}
			}
		} else {
			result.AddColumn(fmt.Sprintf("column%d", i+1))
		}
	}

	// Add rows - evaluate each select expression
	for _, row := range rows {
		values := make([]interface{}, 0)
		for _, col := range stmt.Columns {
			if col.Star {
				if isMultiTable || hasJoin {
					// For multi-table SELECT *, extract columns using qualified names
					for _, tref := range allTableRefs {
						sch, _ := e.schema.GetSchema(tref.Name)
						if sch != nil {
							for _, c := range sch.Columns {
								qualKey := tref.Alias + "." + c.Name
								val, ok := row[qualKey]
								if !ok {
									val = row[c.Name]
								}
								values = append(values, val)
							}
						}
					}
				} else {
					// For SELECT *, add all columns in order
					for _, c := range schema.Columns {
						if storage.IsRowIDColumn(c.Name) {
							values = append(values, row["_rowid_"])
						} else {
							values = append(values, row[c.Name])
						}
					}
				}
			} else {
				// Evaluate the expression
				val, err := e.evalExpr(col.Expr, row)
				if err != nil {
					return nil, err
				}
				values = append(values, val)
			}
		}
		result.AddRow(values...)
	}

	// Apply DISTINCT if specified
	if stmt.Distinct {
		result.Rows = e.applyDistinct(result.Rows)
	}

	return result, nil
}

// executeCompound executes a compound SELECT (UNION / UNION ALL / INTERSECT / EXCEPT).
func (e *Executor) executeCompound(c *parser.CompoundSelect) (*Result, error) {
	left, err := e.executeSelect(c.Left)
	if err != nil {
		return nil, err
	}
	right, err := e.executeSelect(c.Right)
	if err != nil {
		return nil, err
	}

	rowKey := func(row []interface{}) string {
		parts := make([]string, len(row))
		for i, v := range row {
			if v == nil {
				parts[i] = "\x00NULL"
			} else {
				parts[i] = fmt.Sprintf("%v", v)
			}
		}
		return strings.Join(parts, "\x01")
	}

	result := NewResult("SELECT")
	for _, col := range left.Columns {
		result.AddColumn(col)
	}

	switch c.Op {
	case parser.SetOpUnion:
		seen := map[string]bool{}
		for _, row := range left.Rows {
			k := rowKey(row)
			if !seen[k] {
				seen[k] = true
				result.AddRow(row...)
			}
		}
		for _, row := range right.Rows {
			k := rowKey(row)
			if !seen[k] {
				seen[k] = true
				result.AddRow(row...)
			}
		}
	case parser.SetOpUnionAll:
		for _, row := range left.Rows {
			result.AddRow(row...)
		}
		for _, row := range right.Rows {
			result.AddRow(row...)
		}
	case parser.SetOpIntersect:
		rightSet := map[string]bool{}
		for _, row := range right.Rows {
			rightSet[rowKey(row)] = true
		}
		seen := map[string]bool{}
		for _, row := range left.Rows {
			k := rowKey(row)
			if rightSet[k] && !seen[k] {
				seen[k] = true
				result.AddRow(row...)
			}
		}
	case parser.SetOpExcept:
		rightSet := map[string]bool{}
		for _, row := range right.Rows {
			rightSet[rowKey(row)] = true
		}
		seen := map[string]bool{}
		for _, row := range left.Rows {
			k := rowKey(row)
			if !rightSet[k] && !seen[k] {
				seen[k] = true
				result.AddRow(row...)
			}
		}
	}

	// Apply compound-level ORDER BY / LIMIT / OFFSET if present.
	if len(c.OrderBy) > 0 {
		e.sortResultRows(result, c.OrderBy, nil, nil)
	}
	if c.Limit != nil {
		limitVal, err := e.evalExpr(c.Limit, nil)
		if err == nil {
			limit := int(toFloat(limitVal))
			if limit < len(result.Rows) {
				result.Rows = result.Rows[:limit]
			}
		}
	}
	if c.Offset != nil {
		offsetVal, err := e.evalExpr(c.Offset, nil)
		if err == nil {
			offset := int(toFloat(offsetVal))
			if offset >= len(result.Rows) {
				result.Rows = nil
			} else if offset > 0 {
				result.Rows = result.Rows[offset:]
			}
		}
	}

	return result, nil
}

// executeSelectExpr executes a SELECT without FROM.
func (e *Executor) executeSelectExpr(stmt *parser.SelectStmt) (*Result, error) {
	// If any column contains an aggregate, treat as single-group aggregate over one implicit row.
	if e.hasAggregates(stmt.Columns) {
		return e.executeAggregateSelect(stmt, []storage.Row{{}}, nil)
	}

	result := NewResult("SELECT")

	// Determine columns
	for i, col := range stmt.Columns {
		if col.Alias != "" {
			result.AddColumn(col.Alias)
		} else {
			result.AddColumn(fmt.Sprintf("column%d", i+1))
		}
	}

	// Evaluate expressions
	values := make([]interface{}, len(stmt.Columns))
	for i, col := range stmt.Columns {
		val, err := e.evalExpr(col.Expr, nil)
		if err != nil {
			return nil, err
		}
		values[i] = val
	}
	result.AddRow(values...)

	return result, nil
}

// executeSelectFromSubquery executes a SELECT with a subquery in FROM clause.
func (e *Executor) executeSelectFromSubquery(stmt *parser.SelectStmt) (*Result, error) {
	// Execute the subquery to get the derived table
	subqueryResult, err := e.executeSelect(stmt.From[0].Subquery)
	if err != nil {
		return nil, fmt.Errorf("subquery error: %w", err)
	}

	// Convert subquery result to rows for further processing
	derivedRows := make([]storage.Row, 0, subqueryResult.RowCount)
	for _, rowValues := range subqueryResult.Rows {
		row := make(storage.Row)
		for i, col := range subqueryResult.Columns {
			row[col] = rowValues[i]
		}
		derivedRows = append(derivedRows, row)
	}

	// Handle JOINs if present
	if stmt.From[0].Join != nil {
		derivedRows, err = e.executeJoin(stmt.From[0], derivedRows)
		if err != nil {
			return nil, err
		}
	}

	// Apply WHERE clause on derived table
	if stmt.Where != nil {
		filteredRows := make([]storage.Row, 0)
		for _, row := range derivedRows {
			val, err := e.evalExpr(stmt.Where, row)
			if err != nil {
				continue
			}
			if toBool(val) {
				filteredRows = append(filteredRows, row)
			}
		}
		derivedRows = filteredRows
	}

	// Handle GROUP BY
	if len(stmt.GroupBy) > 0 {
		// Create a temporary schema from subquery columns
		tempSchema := &storage.Schema{
			Name:    "derived",
			Columns: make([]storage.Column, len(subqueryResult.Columns)),
		}
		for i, col := range subqueryResult.Columns {
			tempSchema.Columns[i] = storage.Column{
				Name: col,
				Type: "ANY",
			}
		}
		return e.executeGroupBy(stmt, derivedRows, tempSchema)
	}

	// Check for aggregate functions without GROUP BY
	hasAggregate := e.hasAggregates(stmt.Columns)
	if hasAggregate {
		tempSchema := &storage.Schema{
			Name:    "derived",
			Columns: make([]storage.Column, len(subqueryResult.Columns)),
		}
		for i, col := range subqueryResult.Columns {
			tempSchema.Columns[i] = storage.Column{
				Name: col,
				Type: "ANY",
			}
		}
		return e.executeAggregateSelect(stmt, derivedRows, tempSchema)
	}

	// Apply ORDER BY
	if len(stmt.OrderBy) > 0 {
		e.sortRows(derivedRows, resolveOrderByPositions(stmt.OrderBy, stmt.Columns))
	}

	// Apply LIMIT/OFFSET
	if stmt.Offset != nil {
		offset := e.evalIntExpr(stmt.Offset)
		if offset < len(derivedRows) {
			derivedRows = derivedRows[offset:]
		} else {
			derivedRows = nil
		}
	}
	if stmt.Limit != nil {
		limit := e.evalIntExpr(stmt.Limit)
		if limit < len(derivedRows) {
			derivedRows = derivedRows[:limit]
		}
	}

	// Build result
	result := NewResult("SELECT")

	// Determine output columns
	if stmt.Columns[0].Star {
		// SELECT * from derived table
		for _, col := range subqueryResult.Columns {
			result.AddColumn(col)
		}
	} else {
		// Specific columns
		for _, col := range stmt.Columns {
			if col.Alias != "" {
				result.AddColumn(col.Alias)
			} else if colRef, ok := col.Expr.(*parser.ColumnRef); ok {
				result.AddColumn(colRef.Column)
			} else {
				result.AddColumn("column")
			}
		}
	}

	// Add rows
	for _, row := range derivedRows {
		if stmt.Columns[0].Star {
			// SELECT * - use all columns
			values := make([]interface{}, len(subqueryResult.Columns))
			for i, col := range subqueryResult.Columns {
				values[i] = row[col]
			}
			result.AddRow(values...)
		} else {
			// Specific columns - evaluate expressions
			values := make([]interface{}, len(stmt.Columns))
			for i, col := range stmt.Columns {
				val, err := e.evalExpr(col.Expr, row)
				if err != nil {
					return nil, err
				}
				values[i] = val
			}
			result.AddRow(values...)
		}
	}

	return result, nil
}

// executeAggregateSelect executes a SELECT with aggregate functions.
func (e *Executor) executeAggregateSelect(stmt *parser.SelectStmt, rows []storage.Row, schema *storage.Schema) (*Result, error) {
	result := NewResult("SELECT")

	// Determine columns and evaluate aggregates
	for i, col := range stmt.Columns {
		if col.Alias != "" {
			result.AddColumn(col.Alias)
		} else if col.Star {
			result.AddColumn("*")
		} else {
			result.AddColumn(fmt.Sprintf("column%d", i+1))
		}
	}

	// Calculate values
	values := make([]interface{}, len(stmt.Columns))
	for i, col := range stmt.Columns {
		val, err := e.evalAggregateExpr(col.Expr, rows)
		if err != nil {
			return nil, err
		}
		values[i] = val
	}
	result.AddRow(values...)

	return result, nil
}

// executeGroupBy executes a GROUP BY query.
func (e *Executor) executeGroupBy(stmt *parser.SelectStmt, rows []storage.Row, schema *storage.Schema) (*Result, error) {
	// Group rows
	groups := make(map[string][]storage.Row)
	for _, row := range rows {
		key := e.buildGroupKey(stmt.GroupBy, row)
		groups[key] = append(groups[key], row)
	}

	result := NewResult("SELECT")

	// Determine columns
	columnNames := make([]string, len(stmt.Columns))
	for i, col := range stmt.Columns {
		if col.Alias != "" {
			columnNames[i] = col.Alias
			result.AddColumn(col.Alias)
		} else if ref, ok := col.Expr.(*parser.ColumnRef); ok {
			columnNames[i] = ref.Column
			result.AddColumn(ref.Column)
		} else {
			columnNames[i] = fmt.Sprintf("column%d", i+1)
			result.AddColumn(columnNames[i])
		}
	}

	// Process each group
	for _, groupRows := range groups {
		// Apply HAVING
		if stmt.Having != nil {
			val, err := e.evalAggregateExpr(stmt.Having, groupRows)
			if err != nil || val == nil || !toBool(val) {
				continue
			}
		}

		values := make([]interface{}, len(stmt.Columns))
		for i, col := range stmt.Columns {
			if e.isAggregate(col.Expr) {
				val, err := e.evalAggregateExpr(col.Expr, groupRows)
				if err != nil {
					return nil, err
				}
				values[i] = val
			} else {
				// Use first row's value for non-aggregate columns
				val, err := e.evalExpr(col.Expr, groupRows[0])
				if err != nil {
					return nil, err
				}
				values[i] = val
			}
		}
		result.AddRow(values...)
	}

	// Apply DISTINCT
	if stmt.Distinct {
		result.Rows = e.applyDistinct(result.Rows)
	}

	// Apply ORDER BY
	if len(stmt.OrderBy) > 0 {
		e.sortResultRows(result, stmt.OrderBy, stmt.Columns, columnNames)
	}

	// Apply LIMIT/OFFSET
	if stmt.Offset != nil {
		offset := e.evalIntExpr(stmt.Offset)
		if offset < len(result.Rows) {
			result.Rows = result.Rows[offset:]
		} else {
			result.Rows = nil
		}
		result.RowCount = len(result.Rows)
	}
	if stmt.Limit != nil {
		limit := e.evalIntExpr(stmt.Limit)
		if limit < len(result.Rows) {
			result.Rows = result.Rows[:limit]
		}
		result.RowCount = len(result.Rows)
	}

	return result, nil
}

// executeJoins recursively processes all JOIN clauses in a table reference.
func (e *Executor) executeJoins(tableRef parser.TableRef, leftRows []storage.Row) ([]storage.Row, error) {
	if tableRef.Join == nil || tableRef.Join.Table == nil {
		return leftRows, nil
	}

	// Get the right table name and its data
	rightTableRef := tableRef.Join.Table
	rightTable := rightTableRef.Name
	rightRows, err := e.table.Select(rightTable, nil)
	if err != nil {
		return nil, err
	}

	// Perform the join between left and right
	var result []storage.Row
	leftTableName := tableRef.Name
	leftAlias := tableRef.Alias
	rightAlias := rightTableRef.Alias

	// If leftAlias is empty, use the table name
	if leftAlias == "" {
		leftAlias = leftTableName
	}
	if rightAlias == "" {
		rightAlias = rightTable
	}

	switch tableRef.Join.Type {
	case parser.JoinInner:
		for _, left := range leftRows {
			for _, right := range rightRows {
				merged := e.mergeRows(left, right, leftAlias, rightAlias)
				if tableRef.Join.Condition != nil {
					match, _ := e.evalExpr(tableRef.Join.Condition, merged)
					if toBool(match) {
						result = append(result, merged)
					}
				} else {
					result = append(result, merged)
				}
			}
		}

	case parser.JoinLeft:
		for _, left := range leftRows {
			matched := false
			for _, right := range rightRows {
				merged := e.mergeRows(left, right, leftAlias, rightAlias)
				if tableRef.Join.Condition != nil {
					match, _ := e.evalExpr(tableRef.Join.Condition, merged)
					if toBool(match) {
						result = append(result, merged)
						matched = true
					}
				}
			}
			if !matched {
				// Add left row with nulls for right
				result = append(result, left)
			}
		}

	case parser.JoinCross:
		for _, left := range leftRows {
			for _, right := range rightRows {
				result = append(result, e.mergeRows(left, right, leftAlias, rightAlias))
			}
		}
	}

	// Recursively process any additional joins
	if rightTableRef.Join != nil {
		return e.executeJoins(*rightTableRef, result)
	}

	return result, nil
}

// executeJoin executes a JOIN operation.
func (e *Executor) executeJoin(tableRef parser.TableRef, leftRows []storage.Row) ([]storage.Row, error) {
	join := tableRef.Join
	if join == nil || join.Table == nil {
		return leftRows, nil
	}

	rightTable := join.Table.Name
	rightRows, err := e.table.Select(rightTable, nil)
	if err != nil {
		return nil, err
	}

	var result []storage.Row

	switch join.Type {
	case parser.JoinInner:
		for _, left := range leftRows {
			for _, right := range rightRows {
				merged := e.mergeRows(left, right, tableRef.Alias, join.Table.Alias)
				if join.Condition != nil {
					match, _ := e.evalExpr(join.Condition, merged)
					if toBool(match) {
						result = append(result, merged)
					}
				} else {
					result = append(result, merged)
				}
			}
		}

	case parser.JoinLeft:
		for _, left := range leftRows {
			matched := false
			for _, right := range rightRows {
				merged := e.mergeRows(left, right, tableRef.Alias, join.Table.Alias)
				if join.Condition != nil {
					match, _ := e.evalExpr(join.Condition, merged)
					if toBool(match) {
						result = append(result, merged)
						matched = true
					}
				}
			}
			if !matched {
				// Add left row with nulls for right
				result = append(result, left)
			}
		}

	case parser.JoinCross:
		for _, left := range leftRows {
			for _, right := range rightRows {
				result = append(result, e.mergeRows(left, right, tableRef.Alias, join.Table.Alias))
			}
		}
	}

	return result, nil
}

// mergeRows merges two rows with optional table aliases.
func (e *Executor) mergeRows(left, right storage.Row, leftAlias, rightAlias string) storage.Row {
	result := make(storage.Row)
	for k, v := range left {
		// Copy the key as-is (it might already be qualified)
		result[k] = v
		// Only add qualified name if the key is NOT already qualified and we have an alias
		if leftAlias != "" && !strings.Contains(k, ".") {
			result[leftAlias+"."+k] = v
		}
	}
	for k, v := range right {
		// For unqualified names, only add if they don't already exist
		// This prevents right table columns from overwriting left table columns
		if !strings.Contains(k, ".") {
			if _, exists := result[k]; !exists {
				result[k] = v
			}
			// Add qualified name for right table
			if rightAlias != "" {
				result[rightAlias+"."+k] = v
			}
		} else {
			// Already qualified, just copy it
			result[k] = v
		}
	}
	return result
}

// collectAllTableRefs returns a flat list of (alias, tableName) pairs for all tables
// referenced in a FROM clause, following both implicit (comma) and explicit JOIN chains.
func collectAllTableRefs(from []parser.TableRef) []parser.TableRef {
	var refs []parser.TableRef
	for _, tref := range from {
		cur := tref
		for {
			// Shallow copy to hold only this table (no join chain)
			flat := parser.TableRef{Name: cur.Name, Alias: cur.Alias}
			if flat.Alias == "" {
				flat.Alias = flat.Name
			}
			refs = append(refs, flat)
			if cur.Join == nil || cur.Join.Table == nil {
				break
			}
			cur = *cur.Join.Table
		}
	}
	return refs
}

// addTableAlias adds table-qualified names to a row.
func (e *Executor) addTableAlias(row storage.Row, alias string) storage.Row {
	result := make(storage.Row)
	for k, v := range row {
		result[k] = v
		// Don't add alias to already-qualified names
		if !strings.Contains(k, ".") {
			result[alias+"."+k] = v
		}
	}
	return result
}

// executeInsert executes an INSERT statement.
func (e *Executor) executeInsert(stmt *parser.InsertStmt) (*Result, error) {
	tableName := stmt.Table.Name
	schema, err := e.schema.GetSchema(tableName)
	if err != nil {
		return nil, err
	}

	// INSERT ... SELECT: materialise the SELECT result and bulk-insert.
	if stmt.Select != nil {
		sel, err := e.executeSelect(stmt.Select)
		if err != nil {
			return nil, err
		}
		rows := make([]storage.Row, 0, len(sel.Rows))
		for _, selRow := range sel.Rows {
			row := make(storage.Row)
			if len(stmt.Columns) > 0 {
				for i, col := range stmt.Columns {
					if i < len(selRow) {
						row[col] = selRow[i]
					}
				}
			} else {
				for i, col := range schema.Columns {
					if i < len(selRow) {
						row[col.Name] = selRow[i]
					}
				}
			}
			rows = append(rows, row)
		}
		count, err := e.table.InsertBulk(tableName, rows)
		if err != nil {
			return nil, err
		}
		result := NewResult("INSERT")
		result.SetRowCount(count)
		return result, nil
	}

	count := 0
	for _, values := range stmt.Values {
		row := make(storage.Row)

		if len(stmt.Columns) > 0 {
			// Named columns
			for i, col := range stmt.Columns {
				if i < len(values) {
					val, err := e.evalExpr(values[i], nil)
					if err != nil {
						return nil, err
					}
					row[col] = val
				}
			}
		} else {
			// All columns in order
			for i, col := range schema.Columns {
				if i < len(values) {
					val, err := e.evalExpr(values[i], nil)
					if err != nil {
						return nil, err
					}
					row[col.Name] = val
				}
			}
		}

		err := e.table.Insert(tableName, row)
		if err != nil {
			// Handle conflict based on OnConflict action
			if strings.Contains(err.Error(), "duplicate") {
				switch stmt.OnConflict {
				case parser.ConflictIgnore:
					// Silently ignore the duplicate
					continue
				case parser.ConflictReplace:
					// Delete existing row and insert new one
					pkValue := row[schema.PrimaryKey]
					if pkValue != nil {
						e.table.Delete(tableName, func(r storage.Row) bool {
							return fmt.Sprintf("%v", r[schema.PrimaryKey]) == fmt.Sprintf("%v", pkValue)
						})
						// Try insert again
						if err := e.table.Insert(tableName, row); err != nil {
							return nil, err
						}
					}
				case parser.ConflictAbort, parser.ConflictFail:
					return nil, err
				case parser.ConflictRollback:
					// In a real implementation, this would rollback the transaction
					return nil, err
				default:
					return nil, err
				}
			} else {
				return nil, err
			}
		}
		count++
	}

	result := NewResult("INSERT")
	result.SetRowCount(count)
	return result, nil
}

// executeUpdate executes an UPDATE statement.
func (e *Executor) executeUpdate(stmt *parser.UpdateStmt) (*Result, error) {
	tableName := stmt.Table.Name

	// Build filter
	var filter func(storage.Row) bool
	if stmt.Where != nil {
		filter = func(row storage.Row) bool {
			val, err := e.evalExpr(stmt.Where, row)
			if err != nil {
				return false
			}
			return toBool(val)
		}
	}

	// Use UpdateFunc to evaluate expressions per-row (supports self-referencing like balance = balance + 100)
	updateFn := func(row storage.Row) (storage.Row, error) {
		updates := make(storage.Row)
		for _, assign := range stmt.Set {
			val, err := e.evalExpr(assign.Value, row)
			if err != nil {
				return nil, err
			}
			updates[assign.Column] = val
		}
		return updates, nil
	}

	count, err := e.table.UpdateFunc(tableName, updateFn, filter)
	if err != nil {
		return nil, err
	}

	result := NewResult("UPDATE")
	result.SetRowCount(count)
	return result, nil
}

// executeDelete executes a DELETE statement.
func (e *Executor) executeDelete(stmt *parser.DeleteStmt) (*Result, error) {
	tableName := stmt.Table.Name

	// Build filter
	var filter func(storage.Row) bool
	if stmt.Where != nil {
		filter = func(row storage.Row) bool {
			val, err := e.evalExpr(stmt.Where, row)
			if err != nil {
				return false
			}
			return toBool(val)
		}
	}

	count, err := e.table.Delete(tableName, filter)
	if err != nil {
		return nil, err
	}

	result := NewResult("DELETE")
	result.SetRowCount(count)
	return result, nil
}

// executeCreateTable executes a CREATE TABLE statement.
func (e *Executor) executeCreateTable(stmt *parser.CreateTableStmt) (*Result, error) {
	// Check if exists
	if e.schema.TableExists(stmt.Table.Name) {
		if stmt.IfNotExists {
			result := NewResult("CREATE TABLE")
			return result, nil
		}
		return nil, fmt.Errorf("table already exists: %s", stmt.Table.Name)
	}

	// Build schema
	schema := &storage.Schema{
		Name: stmt.Table.Name,
	}

	for _, colDef := range stmt.Columns {
		col := storage.Column{
			Name:     colDef.Name,
			Type:     colDef.Type.Name,
			Nullable: true,
		}

		for _, constraint := range colDef.Constraints {
			switch constraint.Type {
			case parser.ConstraintPrimaryKey:
				col.PrimaryKey = true
				col.Nullable = false
				schema.PrimaryKey = col.Name
			case parser.ConstraintNotNull:
				col.Nullable = false
			case parser.ConstraintDefault:
				if constraint.Default != nil {
					val, _ := e.evalExpr(constraint.Default, nil)
					col.Default = val
				}
			case parser.ConstraintAutoIncrement:
				schema.AutoIncrement = true
			}
		}

		schema.Columns = append(schema.Columns, col)
	}

	// Handle table-level constraints
	for _, constraint := range stmt.Constraints {
		if constraint.Type == parser.ConstraintPrimaryKey && len(constraint.Columns) > 0 {
			schema.PrimaryKey = constraint.Columns[0]
			for i := range schema.Columns {
				if strings.EqualFold(schema.Columns[i].Name, schema.PrimaryKey) {
					schema.Columns[i].PrimaryKey = true
					schema.Columns[i].Nullable = false
				}
			}
		}
	}

	if err := e.schema.CreateTable(schema); err != nil {
		return nil, err
	}

	// Update analyzer catalog
	e.catalog.CreateTable(schema.ToAnalyzerTableInfo())

	result := NewResult("CREATE TABLE")
	return result, nil
}

// executeDropTable executes a DROP TABLE statement.
func (e *Executor) executeDropTable(stmt *parser.DropTableStmt) (*Result, error) {
	for _, tableRef := range stmt.Tables {
		if !e.schema.TableExists(tableRef.Name) {
			if stmt.IfExists {
				continue
			}
			return nil, fmt.Errorf("table not found: %s", tableRef.Name)
		}

		// First, drop all indexes associated with this table
		indexes, _ := e.schema.ListTableIndexes(tableRef.Name)
		for _, idx := range indexes {
			// Clear index entries
			columns := make([]string, len(idx.Columns))
			for i, col := range idx.Columns {
				columns[i] = col.Name
			}
			e.table.ClearIndex(idx.Name, tableRef.Name, columns)
			// Drop the index schema
			e.schema.DropIndex(idx.Name)
		}

		// Then, truncate all data rows
		e.table.Truncate(tableRef.Name)

		// Finally, drop the table schema
		if err := e.schema.DropTable(tableRef.Name); err != nil {
			return nil, err
		}

		// Update analyzer catalog
		e.catalog.DropTable(tableRef.Name)
	}

	result := NewResult("DROP TABLE")
	return result, nil
}

// executeCreateIndex creates a new index.
func (e *Executor) executeCreateIndex(stmt *parser.CreateIndexStmt) (*Result, error) {
	// Check if index already exists
	if e.schema.IndexExists(stmt.Name) {
		if stmt.IfNotExists {
			result := NewResult("CREATE INDEX")
			return result, nil
		}
		return nil, fmt.Errorf("index already exists: %s", stmt.Name)
	}

	// Verify table exists
	if !e.schema.TableExists(stmt.Table) {
		return nil, fmt.Errorf("table not found: %s", stmt.Table)
	}

	// Verify columns exist
	schema, err := e.schema.GetSchema(stmt.Table)
	if err != nil {
		return nil, err
	}

	for _, col := range stmt.Columns {
		if _, found := schema.GetColumn(col.Name); !found {
			return nil, fmt.Errorf("column not found: %s", col.Name)
		}
	}

	// Create storage index
	index := &storage.Index{
		Name:   stmt.Name,
		Table:  stmt.Table,
		Unique: stmt.Unique,
	}

	for _, col := range stmt.Columns {
		index.Columns = append(index.Columns, storage.IndexColumn{
			Name: col.Name,
			Desc: col.Desc,
		})
	}

	if err := e.schema.CreateIndex(index); err != nil {
		return nil, err
	}

	// Build index entries for existing rows
	columns := make([]string, len(stmt.Columns))
	for i, col := range stmt.Columns {
		columns[i] = col.Name
	}
	if err := e.table.BuildIndex(stmt.Name, stmt.Table, columns); err != nil {
		// Rollback index creation on failure
		e.schema.DropIndex(stmt.Name)
		return nil, fmt.Errorf("failed to build index: %w", err)
	}

	result := NewResult("CREATE INDEX")
	return result, nil
}

// executeDropIndex drops an index.
func (e *Executor) executeDropIndex(stmt *parser.DropIndexStmt) (*Result, error) {
	if !e.schema.IndexExists(stmt.Name) {
		if stmt.IfExists {
			result := NewResult("DROP INDEX")
			return result, nil
		}
		return nil, fmt.Errorf("index not found: %s", stmt.Name)
	}

	// Get index info to clear entries
	index, err := e.schema.GetIndex(stmt.Name)
	if err == nil && index != nil {
		columns := make([]string, len(index.Columns))
		for i, col := range index.Columns {
			columns[i] = col.Name
		}
		e.table.ClearIndex(stmt.Name, index.Table, columns)
	}

	if err := e.schema.DropIndex(stmt.Name); err != nil {
		return nil, err
	}

	result := NewResult("DROP INDEX")
	return result, nil
}

func (e *Executor) executeCreateView(stmt *parser.CreateViewStmt) (*Result, error) {
	name := strings.ToLower(stmt.View.Name)

	if _, exists := e.views[name]; exists {
		if stmt.IfNotExists {
			return NewResult("CREATE VIEW"), nil
		}
		return nil, fmt.Errorf("view already exists: %s", stmt.View.Name)
	}

	e.views[name] = stmt.Select

	// Derive view columns from SELECT list for catalog registration.
	var viewCols []analyzer.ColumnInfo
	hasStar := false
	for _, col := range stmt.Select.Columns {
		if col.Star {
			hasStar = true
			break
		}
		colName := col.Alias
		if colName == "" {
			if ref, ok := col.Expr.(*parser.ColumnRef); ok {
				colName = ref.Column
			} else {
				colName = fmt.Sprintf("col_%d", len(viewCols))
			}
		}
		viewCols = append(viewCols, analyzer.ColumnInfo{
			Name:      colName,
			TableName: stmt.View.Name,
			Type:      analyzer.TypeAny,
			Nullable:  true,
		})
	}
	// For SELECT *, pull columns from the underlying table(s).
	if hasStar && len(stmt.Select.From) > 0 {
		baseName := stmt.Select.From[0].Name
		if schema, err := e.schema.GetSchema(baseName); err == nil {
			for _, c := range schema.Columns {
				viewCols = append(viewCols, analyzer.ColumnInfo{
					Name:      c.Name,
					TableName: stmt.View.Name,
					Type:      analyzer.TypeAny,
					Nullable:  true,
				})
			}
		}
	}

	// Register in catalog so the analyzer accepts SELECT FROM this view.
	e.catalog.CreateTable(&analyzer.TableInfo{ //nolint:errcheck
		Name:    stmt.View.Name,
		Columns: viewCols,
		IsView:  true,
	})

	return NewResult("CREATE VIEW"), nil
}

func (e *Executor) executeDropView(stmt *parser.DropViewStmt) (*Result, error) {
	for _, ref := range stmt.Views {
		name := strings.ToLower(ref.Name)
		if _, exists := e.views[name]; !exists {
			if stmt.IfExists {
				continue
			}
			return nil, fmt.Errorf("view not found: %s", ref.Name)
		}
		delete(e.views, name)
		e.catalog.DropTable(ref.Name) //nolint:errcheck
	}
	return NewResult("DROP VIEW"), nil
}

// executeAlterTable executes an ALTER TABLE statement.
func (e *Executor) executeAlterTable(stmt *parser.AlterTableStmt) (*Result, error) {
	switch action := stmt.Action.(type) {
	case *parser.AddColumnAction:
		return e.executeAlterTableAddColumn(stmt.Table, action)
	case *parser.DropColumnAction:
		return e.executeAlterTableDropColumn(stmt.Table, action)
	case *parser.RenameTableAction:
		return e.executeAlterTableRename(stmt.Table, action)
	case *parser.RenameColumnAction:
		return e.executeAlterTableRenameColumn(stmt.Table, action)
	default:
		return nil, fmt.Errorf("unsupported ALTER TABLE action: %T", action)
	}
}

// executeAlterTableAddColumn adds a column to a table.
func (e *Executor) executeAlterTableAddColumn(table string, action *parser.AddColumnAction) (*Result, error) {
	col := storage.Column{
		Name:     action.Column.Name,
		Type:     action.Column.Type.Name,
		Nullable: true,
	}

	// Process column constraints
	for _, constraint := range action.Column.Constraints {
		switch constraint.Type {
		case parser.ConstraintPrimaryKey:
			col.PrimaryKey = true
			col.Nullable = false
		case parser.ConstraintNotNull:
			col.Nullable = false
		case parser.ConstraintDefault:
			if constraint.Default != nil {
				val, _ := e.evalExpr(constraint.Default, nil)
				col.Default = val
			}
		}
	}

	if err := e.schema.AddColumn(table, col); err != nil {
		return nil, err
	}

	// Update catalog
	e.SyncCatalog()

	result := NewResult("ALTER TABLE")
	return result, nil
}

// executeAlterTableDropColumn drops a column from a table.
func (e *Executor) executeAlterTableDropColumn(table string, action *parser.DropColumnAction) (*Result, error) {
	if err := e.schema.DropColumn(table, action.Column); err != nil {
		return nil, err
	}

	// Update catalog
	e.SyncCatalog()

	result := NewResult("ALTER TABLE")
	return result, nil
}

// executeAlterTableRename renames a table.
func (e *Executor) executeAlterTableRename(table string, action *parser.RenameTableAction) (*Result, error) {
	if err := e.schema.RenameTable(table, action.NewName); err != nil {
		return nil, err
	}

	// Update catalog
	e.SyncCatalog()

	result := NewResult("ALTER TABLE")
	return result, nil
}

// executeAlterTableRenameColumn renames a column.
func (e *Executor) executeAlterTableRenameColumn(table string, action *parser.RenameColumnAction) (*Result, error) {
	if err := e.schema.RenameColumn(table, action.OldName, action.NewName); err != nil {
		return nil, err
	}

	// Update catalog
	e.SyncCatalog()

	result := NewResult("ALTER TABLE")
	return result, nil
}

// Transaction execution methods

// executeBegin starts a new transaction.
func (e *Executor) executeBegin(stmt *parser.BeginStmt) (*Result, error) {
	if e.inTransaction {
		return nil, fmt.Errorf("cannot start a transaction within a transaction")
	}

	e.inTransaction = true
	e.savepoints = nil
	e.txLog = nil

	result := NewResult("BEGIN")
	return result, nil
}

// executeCommit commits the current transaction.
func (e *Executor) executeCommit(stmt *parser.CommitStmt) (*Result, error) {
	if !e.inTransaction {
		return nil, fmt.Errorf("cannot commit: no transaction in progress")
	}

	// Clear transaction state
	e.inTransaction = false
	e.savepoints = nil
	e.txLog = nil

	result := NewResult("COMMIT")
	return result, nil
}

// executeRollback rolls back the current transaction or to a savepoint.
func (e *Executor) executeRollback(stmt *parser.RollbackStmt) (*Result, error) {
	if !e.inTransaction {
		return nil, fmt.Errorf("cannot rollback: no transaction in progress")
	}

	if stmt.Savepoint != "" {
		// Rollback to savepoint
		return e.rollbackToSavepoint(stmt.Savepoint)
	}

	// Full rollback - undo all operations in reverse order
	for i := len(e.txLog) - 1; i >= 0; i-- {
		entry := e.txLog[i]
		if err := e.undoOperation(entry); err != nil {
			// Log error but continue with rollback
			continue
		}
	}

	// Clear transaction state
	e.inTransaction = false
	e.savepoints = nil
	e.txLog = nil

	result := NewResult("ROLLBACK")
	return result, nil
}

// executeSavepoint creates a savepoint.
func (e *Executor) executeSavepoint(stmt *parser.SavepointStmt) (*Result, error) {
	if !e.inTransaction {
		// SQLite allows SAVEPOINT outside transaction (starts implicit transaction)
		e.inTransaction = true
		e.txLog = nil
	}

	// Add savepoint marker
	e.savepoints = append(e.savepoints, stmt.Name)

	result := NewResult("SAVEPOINT")
	return result, nil
}

// executeRelease releases a savepoint.
func (e *Executor) executeRelease(stmt *parser.ReleaseStmt) (*Result, error) {
	if !e.inTransaction {
		return nil, fmt.Errorf("cannot release savepoint: no transaction in progress")
	}

	// Find and remove the savepoint
	found := false
	for i := len(e.savepoints) - 1; i >= 0; i-- {
		if e.savepoints[i] == stmt.Name {
			e.savepoints = e.savepoints[:i]
			found = true
			break
		}
	}

	if !found {
		return nil, fmt.Errorf("no such savepoint: %s", stmt.Name)
	}

	result := NewResult("RELEASE")
	return result, nil
}

// executeAttach attaches a database.
func (e *Executor) executeAttach(stmt *parser.AttachStmt) (*Result, error) {
	// Check if alias already exists
	if _, exists := e.attachedDatabases[stmt.Alias]; exists {
		return nil, fmt.Errorf("database alias already exists: %s", stmt.Alias)
	}

	// Reserved alias check
	if strings.EqualFold(stmt.Alias, "temp") || strings.EqualFold(stmt.Alias, "temporary") {
		return nil, fmt.Errorf("reserved database alias: %s", stmt.Alias)
	}

	// Get the pool from the main schema manager
	pool := e.schema.GetPool()

	// Create new schema and table managers for the attached database
	// In PizzaKV, each database is just a different namespace/prefix
	schema := storage.NewSchemaManager(pool, stmt.FilePath)
	table := storage.NewTableManager(pool, schema, stmt.FilePath)

	// Register the database connection
	e.attachedDatabases[stmt.Alias] = &DatabaseConnection{
		Alias:  stmt.Alias,
		Path:   stmt.FilePath,
		Schema: schema,
		Table:  table,
	}

	// Sync the catalog with the attached database's tables
	tables, _ := schema.ListTables()
	for _, tableName := range tables {
		tSchema, err := schema.GetSchema(tableName)
		if err != nil {
			continue
		}
		// Add with database prefix
		tableInfo := tSchema.ToAnalyzerTableInfo()
		tableInfo.Name = stmt.Alias + "." + tableInfo.Name
		e.catalog.CreateTable(tableInfo)
	}

	result := NewResult("ATTACH")
	return result, nil
}

// executeDetach detaches a database.
func (e *Executor) executeDetach(stmt *parser.DetachStmt) (*Result, error) {
	// Cannot detach main database
	if strings.EqualFold(stmt.Alias, "main") {
		return nil, fmt.Errorf("cannot detach main database")
	}

	// Check if database exists
	if _, exists := e.attachedDatabases[stmt.Alias]; !exists {
		return nil, fmt.Errorf("no such database: %s", stmt.Alias)
	}

	// Remove from attached databases
	delete(e.attachedDatabases, stmt.Alias)

	// Note: We don't remove from catalog as that would be more complex
	// In a production system, we'd need to track which tables belong to which database

	result := NewResult("DETACH")
	return result, nil
}

// rollbackToSavepoint rolls back to a specific savepoint.
func (e *Executor) rollbackToSavepoint(name string) (*Result, error) {
	// Find savepoint index
	savepointIdx := -1
	for i := len(e.savepoints) - 1; i >= 0; i-- {
		if e.savepoints[i] == name {
			savepointIdx = i
			break
		}
	}

	if savepointIdx == -1 {
		return nil, fmt.Errorf("no such savepoint: %s", name)
	}

	// Count operations to undo (operations after the savepoint)
	// For simplicity, we track savepoint positions by counting log entries
	// In a real implementation, we'd track log positions per savepoint

	// Undo operations in reverse order
	for i := len(e.txLog) - 1; i >= 0; i-- {
		entry := e.txLog[i]
		if err := e.undoOperation(entry); err != nil {
			continue
		}
	}

	// Remove savepoints after the target
	e.savepoints = e.savepoints[:savepointIdx+1]

	result := NewResult("ROLLBACK")
	return result, nil
}

// undoOperation reverses a single operation.
func (e *Executor) undoOperation(entry txLogEntry) error {
	switch entry.operation {
	case "INSERT":
		// Delete the inserted row
		_, err := e.table.Delete(entry.table, func(r storage.Row) bool {
			// Match by primary key stored in entry.key
			pk := e.getPrimaryKey(entry.table)
			if pk == "" {
				return false
			}
			return fmt.Sprintf("%v", r[pk]) == entry.key
		})
		return err

	case "DELETE":
		// Re-insert the deleted row
		if entry.oldData != nil {
			return e.table.Insert(entry.table, entry.oldData)
		}

	case "UPDATE":
		// Restore the old data
		if entry.oldData != nil {
			pk := e.getPrimaryKey(entry.table)
			if pk != "" {
				// Delete current row and insert old data
				e.table.Delete(entry.table, func(r storage.Row) bool {
					return fmt.Sprintf("%v", r[pk]) == entry.key
				})
				return e.table.Insert(entry.table, entry.oldData)
			}
		}
	}
	return nil
}

// getPrimaryKey returns the primary key column name for a table.
func (e *Executor) getPrimaryKey(tableName string) string {
	schema, err := e.schema.GetSchema(tableName)
	if err != nil {
		return ""
	}
	return schema.PrimaryKey
}

// extractIndexableCondition extracts column name and value from a simple equality condition.
// Returns (column, value, true) if the expression is column = literal.
func (e *Executor) extractIndexableCondition(expr parser.Expr) (string, interface{}, bool) {
	binExpr, ok := expr.(*parser.BinaryExpr)
	if !ok {
		return "", nil, false
	}

	// Only handle equality for now
	if binExpr.Op != lexer.TokenEq {
		return "", nil, false
	}

	// Check for column = literal pattern
	colRef, leftIsCol := binExpr.Left.(*parser.ColumnRef)
	litExpr, rightIsLit := binExpr.Right.(*parser.LiteralExpr)

	if leftIsCol && rightIsLit {
		val, _ := e.evalLiteral(litExpr)
		return colRef.Column, val, true
	}

	// Check for literal = column pattern
	litExpr, leftIsLit := binExpr.Left.(*parser.LiteralExpr)
	colRef, rightIsCol := binExpr.Right.(*parser.ColumnRef)

	if leftIsLit && rightIsCol {
		val, _ := e.evalLiteral(litExpr)
		return colRef.Column, val, true
	}

	return "", nil, false
}

// executePragma executes a PRAGMA statement.
func (e *Executor) executePragma(stmt *parser.PragmaStmt) (*Result, error) {
	switch stmt.Name {
	case "table_info":
		return e.pragmaTableInfo(stmt.Arg)
	case "table_list":
		return e.pragmaTableList()
	case "database_list":
		return e.pragmaDatabaseList()
	case "version":
		return e.pragmaVersion()
	default:
		return nil, fmt.Errorf("unknown pragma: %s", stmt.Name)
	}
}

// pragmaTableInfo returns column information for a table.
func (e *Executor) pragmaTableInfo(tableName string) (*Result, error) {
	if tableName == "" {
		return nil, fmt.Errorf("table_info requires a table name")
	}

	schema, err := e.schema.GetSchema(tableName)
	if err != nil {
		return nil, err
	}

	result := NewResult("PRAGMA")
	result.AddColumn("cid")
	result.AddColumn("name")
	result.AddColumn("type")
	result.AddColumn("notnull")
	result.AddColumn("dflt_value")
	result.AddColumn("pk")

	for i, col := range schema.Columns {
		notnull := 0
		if !col.Nullable {
			notnull = 1
		}
		pk := 0
		if col.PrimaryKey {
			pk = 1
		}
		result.AddRow(int64(i), col.Name, col.Type, int64(notnull), col.Default, int64(pk))
	}

	return result, nil
}

// pragmaTableList returns a list of all tables.
func (e *Executor) pragmaTableList() (*Result, error) {
	tables, err := e.schema.ListTables()
	if err != nil {
		return nil, err
	}

	result := NewResult("PRAGMA")
	result.AddColumn("schema")
	result.AddColumn("name")
	result.AddColumn("type")

	for _, t := range tables {
		result.AddRow("main", t, "table")
	}

	return result, nil
}

// pragmaDatabaseList returns a list of databases.
func (e *Executor) pragmaDatabaseList() (*Result, error) {
	result := NewResult("PRAGMA")
	result.AddColumn("seq")
	result.AddColumn("name")
	result.AddColumn("file")

	// We only have one database
	result.AddRow(int64(0), "main", "")

	return result, nil
}

// pragmaVersion returns the PizzaSQL version.
func (e *Executor) pragmaVersion() (*Result, error) {
	result := NewResult("PRAGMA")
	result.AddColumn("version")
	result.AddRow("PizzaSQL 1.0.0")
	return result, nil
}

// executeExplain executes an EXPLAIN statement.
func (e *Executor) executeExplain(stmt *parser.ExplainStmt) (*Result, error) {
	result := NewResult("EXPLAIN")

	if stmt.QueryPlan {
		// EXPLAIN QUERY PLAN format
		result.AddColumn("id")
		result.AddColumn("parent")
		result.AddColumn("notused")
		result.AddColumn("detail")

		plan := e.generateQueryPlan(stmt.Statement)
		for i, step := range plan {
			result.AddRow(int64(i), int64(0), int64(0), step)
		}
	} else {
		// Simple EXPLAIN format
		result.AddColumn("addr")
		result.AddColumn("opcode")
		result.AddColumn("p1")
		result.AddColumn("p2")
		result.AddColumn("p3")
		result.AddColumn("p4")
		result.AddColumn("p5")
		result.AddColumn("comment")

		ops := e.generateOpcodes(stmt.Statement)
		for i, op := range ops {
			result.AddRow(int64(i), op, int64(0), int64(0), int64(0), "", int64(0), "")
		}
	}

	return result, nil
}

// generateQueryPlan generates a simple query plan description.
func (e *Executor) generateQueryPlan(stmt parser.Statement) []string {
	var plan []string

	switch s := stmt.(type) {
	case *parser.SelectStmt:
		if len(s.From) > 0 {
			plan = append(plan, fmt.Sprintf("SCAN TABLE %s", s.From[0].Name))
			if s.Where != nil {
				plan = append(plan, "FILTER")
			}
			if len(s.OrderBy) > 0 {
				plan = append(plan, "SORT")
			}
			if s.Limit != nil {
				plan = append(plan, "LIMIT")
			}
		} else {
			plan = append(plan, "SCALAR EXPRESSION")
		}
	case *parser.InsertStmt:
		plan = append(plan, fmt.Sprintf("INSERT INTO %s", s.Table.Name))
	case *parser.UpdateStmt:
		plan = append(plan, fmt.Sprintf("SCAN TABLE %s", s.Table.Name))
		plan = append(plan, "UPDATE")
	case *parser.DeleteStmt:
		plan = append(plan, fmt.Sprintf("SCAN TABLE %s", s.Table.Name))
		plan = append(plan, "DELETE")
	default:
		plan = append(plan, "EXECUTE")
	}

	return plan
}

// generateOpcodes generates simplified opcodes for EXPLAIN.
func (e *Executor) generateOpcodes(stmt parser.Statement) []string {
	var ops []string

	switch s := stmt.(type) {
	case *parser.SelectStmt:
		ops = append(ops, "Init")
		if len(s.From) > 0 {
			ops = append(ops, "OpenRead")
			ops = append(ops, "Rewind")
			ops = append(ops, "Column")
			ops = append(ops, "ResultRow")
			ops = append(ops, "Next")
			ops = append(ops, "Close")
		} else {
			ops = append(ops, "Integer")
			ops = append(ops, "ResultRow")
		}
		ops = append(ops, "Halt")
	case *parser.InsertStmt:
		ops = append(ops, "Init")
		ops = append(ops, "OpenWrite")
		ops = append(ops, "NewRowid")
		ops = append(ops, "Insert")
		ops = append(ops, "Close")
		ops = append(ops, "Halt")
	case *parser.UpdateStmt:
		ops = append(ops, "Init")
		ops = append(ops, "OpenWrite")
		ops = append(ops, "Rewind")
		ops = append(ops, "Column")
		ops = append(ops, "Update")
		ops = append(ops, "Next")
		ops = append(ops, "Close")
		ops = append(ops, "Halt")
	case *parser.DeleteStmt:
		ops = append(ops, "Init")
		ops = append(ops, "OpenWrite")
		ops = append(ops, "Rewind")
		ops = append(ops, "Delete")
		ops = append(ops, "Next")
		ops = append(ops, "Close")
		ops = append(ops, "Halt")
	default:
		ops = append(ops, "Init")
		ops = append(ops, "Halt")
	}

	return ops
}

// evalExpr evaluates an expression.
func (e *Executor) evalExpr(expr parser.Expr, row storage.Row) (interface{}, error) {
	if expr == nil {
		return nil, nil
	}
	switch ex := expr.(type) {
	case *parser.LiteralExpr:
		return e.evalLiteral(ex)
	case *parser.ColumnRef:
		return e.evalColumnRef(ex, row)
	case *parser.BinaryExpr:
		return e.evalBinaryExpr(ex, row)
	case *parser.UnaryExpr:
		return e.evalUnaryExpr(ex, row)
	case *parser.FunctionCall:
		return e.evalFunctionCall(ex, row)
	case *parser.ParenExpr:
		return e.evalExpr(ex.Expr, row)
	case *parser.CaseExpr:
		return e.evalCaseExpr(ex, row)
	case *parser.InExpr:
		return e.evalInExpr(ex, row)
	case *parser.BetweenExpr:
		return e.evalBetweenExpr(ex, row)
	case *parser.LikeExpr:
		return e.evalLikeExpr(ex, row)
	case *parser.IsNullExpr:
		return e.evalIsNullExpr(ex, row)
	case *parser.CastExpr:
		return e.evalCastExpr(ex, row)
	case *parser.SubqueryExpr:
		return e.evalSubqueryExpr(ex, row)
	case *parser.ExistsExpr:
		return e.evalExistsExpr(ex, row)
	default:
		return nil, fmt.Errorf("unsupported expression type: %T", expr)
	}
}

func (e *Executor) evalLiteral(lit *parser.LiteralExpr) (interface{}, error) {
	switch lit.Type {
	case lexer.TokenNumber:
		// Check for scientific notation (e.g., 1e+06) or decimal point
		if strings.Contains(lit.Value, ".") || strings.ContainsAny(lit.Value, "eE") {
			f, err := strconv.ParseFloat(lit.Value, 64)
			if err != nil {
				return nil, err
			}
			// If it's a whole number (no fractional part), return as int64
			if f == float64(int64(f)) {
				return int64(f), nil
			}
			return f, nil
		}
		return strconv.ParseInt(lit.Value, 10, 64)
	case lexer.TokenString:
		return lit.Value, nil
	case lexer.TokenNULL:
		return nil, nil
	case lexer.TokenTRUE:
		return true, nil
	case lexer.TokenFALSE:
		return false, nil
	default:
		return lit.Value, nil
	}
}

func (e *Executor) evalColumnRef(ref *parser.ColumnRef, row storage.Row) (interface{}, error) {
	if row == nil {
		return nil, fmt.Errorf("no row context for column: %s", ref.Column)
	}

	// Check for ROWID aliases (rowid, oid, _rowid_)
	if storage.IsRowIDColumn(ref.Column) {
		if val, ok := row["_rowid_"]; ok {
			return val, nil
		}
		return nil, nil
	}

	// For qualified column references (table.column):
	//
	// Resolution order:
	//   1. Exact qualified key in outer row  ("t1.b" → outer)
	//   2. Case-insensitive qualified in outer row
	//   3. Exact qualified key in current row ("x.b"  → inner alias)
	//   4. Case-insensitive qualified in current row
	//   5. Unqualified in outer row — only reached when qualified lookup in current
	//      row failed, meaning the qualifier refers to an outer table not the inner
	//      alias (e.g. "t1.b" in a subquery "FROM t1 AS x" resolves here).
	//   6. Unqualified in current row (last resort)
	if ref.Table != "" {
		if e.outerRow != nil {
			// Step 1-2: qualified lookup in outer row
			if val, ok := e.outerRow[ref.Table+"."+ref.Column]; ok {
				return val, nil
			}
			for k, v := range e.outerRow {
				if strings.EqualFold(k, ref.Table+"."+ref.Column) {
					return v, nil
				}
			}
		}

		// Step 3-4: qualified lookup in current row
		if val, ok := row[ref.Table+"."+ref.Column]; ok {
			return val, nil
		}
		for k, v := range row {
			if strings.EqualFold(k, ref.Table+"."+ref.Column) {
				return v, nil
			}
		}

		// Step 5: qualified lookup failed in current row — try outer row unqualified.
		// This handles correlated subqueries where the qualifier names an outer table
		// (e.g. "t1.b" when the inner FROM is "t1 AS x", so current row has "x.b"
		// but no "t1.b").
		if e.outerRow != nil {
			if val, ok := e.outerRow[ref.Column]; ok {
				return val, nil
			}
			for k, v := range e.outerRow {
				if strings.EqualFold(k, ref.Column) {
					return v, nil
				}
			}
		}
	}

	// Step 6: unqualified fallback in current row (handles unqualified refs and
	// single-table queries like "SELECT t1.a FROM t1" where rows have plain keys).
	if val, ok := row[ref.Column]; ok {
		return val, nil
	}
	for k, v := range row {
		if strings.EqualFold(k, ref.Column) {
			return v, nil
		}
	}

	// For unqualified refs with an outer row context (ref.Table == "").
	if e.outerRow != nil && ref.Table == "" {
		if val, ok := e.outerRow[ref.Column]; ok {
			return val, nil
		}
		for k, v := range e.outerRow {
			if strings.EqualFold(k, ref.Column) {
				return v, nil
			}
		}
	}

	return nil, nil // Column not found, return NULL
}

func (e *Executor) evalBinaryExpr(expr *parser.BinaryExpr, row storage.Row) (interface{}, error) {
	left, err := e.evalExpr(expr.Left, row)
	if err != nil {
		return nil, err
	}
	right, err := e.evalExpr(expr.Right, row)
	if err != nil {
		return nil, err
	}

	switch expr.Op {
	case lexer.TokenPlus:
		if left == nil || right == nil {
			return nil, nil
		}
		if isIntVal(left) && isIntVal(right) {
			return toInt64(left) + toInt64(right), nil
		}
		return toFloat(left) + toFloat(right), nil
	case lexer.TokenMinus:
		if left == nil || right == nil {
			return nil, nil
		}
		if isIntVal(left) && isIntVal(right) {
			return toInt64(left) - toInt64(right), nil
		}
		return toFloat(left) - toFloat(right), nil
	case lexer.TokenStar:
		if left == nil || right == nil {
			return nil, nil
		}
		if isIntVal(left) && isIntVal(right) {
			return toInt64(left) * toInt64(right), nil
		}
		return toFloat(left) * toFloat(right), nil
	case lexer.TokenSlash:
		if left == nil || right == nil {
			return nil, nil
		}
		// Integer division when both operands are integers (truncates toward zero, matching SQLite)
		if isIntVal(left) && isIntVal(right) {
			ri := toInt64(right)
			if ri == 0 {
				return nil, nil
			}
			return toInt64(left) / ri, nil
		}
		r := toFloat(right)
		if r == 0 {
			return nil, nil // Division by zero returns NULL
		}
		return toFloat(left) / r, nil
	case lexer.TokenPercent:
		if left == nil || right == nil {
			return nil, nil
		}
		if isIntVal(left) && isIntVal(right) {
			ri := toInt64(right)
			if ri == 0 {
				return nil, nil
			}
			return toInt64(left) % ri, nil
		}
		return int64(toFloat(left)) % int64(toFloat(right)), nil
	case lexer.TokenEq:
		if left == nil || right == nil {
			return nil, nil
		}
		return compare(left, right) == 0, nil
	case lexer.TokenNeq:
		if left == nil || right == nil {
			return nil, nil
		}
		return compare(left, right) != 0, nil
	case lexer.TokenLt:
		if left == nil || right == nil {
			return nil, nil
		}
		return compare(left, right) < 0, nil
	case lexer.TokenLte:
		if left == nil || right == nil {
			return nil, nil
		}
		return compare(left, right) <= 0, nil
	case lexer.TokenGt:
		if left == nil || right == nil {
			return nil, nil
		}
		return compare(left, right) > 0, nil
	case lexer.TokenGte:
		if left == nil || right == nil {
			return nil, nil
		}
		return compare(left, right) >= 0, nil
	case lexer.TokenAND:
		// Three-value logic: FALSE AND x = FALSE; NULL AND TRUE = NULL; TRUE AND TRUE = TRUE
		if left != nil && !toBool(left) {
			return false, nil
		}
		if right != nil && !toBool(right) {
			return false, nil
		}
		if left == nil || right == nil {
			return nil, nil
		}
		return true, nil
	case lexer.TokenOR:
		// Three-value logic: TRUE OR x = TRUE; NULL OR FALSE = NULL; FALSE OR FALSE = FALSE
		if left != nil && toBool(left) {
			return true, nil
		}
		if right != nil && toBool(right) {
			return true, nil
		}
		if left == nil || right == nil {
			return nil, nil
		}
		return false, nil
	case lexer.TokenConcat:
		return toString(left) + toString(right), nil
	default:
		return nil, fmt.Errorf("unsupported operator: %v", expr.Op)
	}
}

func (e *Executor) evalUnaryExpr(expr *parser.UnaryExpr, row storage.Row) (interface{}, error) {
	val, err := e.evalExpr(expr.Operand, row)
	if err != nil {
		return nil, err
	}

	switch expr.Op {
	case lexer.TokenMinus:
		if val == nil {
			return nil, nil
		}
		if isIntVal(val) {
			return -toInt64(val), nil
		}
		return -toFloat(val), nil
	case lexer.TokenPlus:
		if val == nil {
			return nil, nil
		}
		if isIntVal(val) {
			return toInt64(val), nil
		}
		return toFloat(val), nil
	case lexer.TokenNOT:
		if val == nil {
			return nil, nil // NOT NULL = NULL
		}
		return !toBool(val), nil
	default:
		return val, nil
	}
}

func (e *Executor) evalFunctionCall(fn *parser.FunctionCall, row storage.Row) (interface{}, error) {
	name := strings.ToUpper(fn.Name)

	// Evaluate arguments
	args := make([]interface{}, len(fn.Args))
	for i, arg := range fn.Args {
		val, err := e.evalExpr(arg, row)
		if err != nil {
			return nil, err
		}
		args[i] = val
	}

	switch name {
	case "UPPER":
		if len(args) > 0 {
			if args[0] == nil {
				return nil, nil // NULL propagation
			}
			return strings.ToUpper(toString(args[0])), nil
		}
	case "LOWER":
		if len(args) > 0 {
			if args[0] == nil {
				return nil, nil // NULL propagation
			}
			return strings.ToLower(toString(args[0])), nil
		}
	case "LENGTH":
		if len(args) > 0 {
			if args[0] == nil {
				return nil, nil // NULL propagation
			}
			return int64(len(toString(args[0]))), nil
		}
	case "ABS":
		if len(args) > 0 {
			if args[0] == nil {
				return nil, nil
			}
			v := toFloat(args[0])
			if v < 0 {
				return -v, nil
			}
			return v, nil
		}
	case "COALESCE":
		for _, arg := range args {
			if arg != nil {
				return arg, nil
			}
		}
		return nil, nil
	case "NULLIF":
		if len(args) >= 2 && compare(args[0], args[1]) == 0 {
			return nil, nil
		}
		if len(args) > 0 {
			return args[0], nil
		}
	case "IFNULL":
		if len(args) >= 2 {
			if args[0] == nil {
				return args[1], nil
			}
			return args[0], nil
		}
	case "TYPEOF":
		if len(args) > 0 {
			switch args[0].(type) {
			case nil:
				return "null", nil
			case int64, int:
				return "integer", nil
			case float64:
				return "real", nil
			case string:
				return "text", nil
			case []byte:
				return "blob", nil
			default:
				return "text", nil
			}
		}
	case "SUBSTR", "SUBSTRING":
		if len(args) >= 2 {
			s := toString(args[0])
			start := int(toFloat(args[1])) - 1 // SQL is 1-indexed
			if start < 0 {
				start = 0
			}
			if start >= len(s) {
				return "", nil
			}
			if len(args) >= 3 {
				length := int(toFloat(args[2]))
				if start+length > len(s) {
					length = len(s) - start
				}
				return s[start : start+length], nil
			}
			return s[start:], nil
		}
	case "TRIM":
		if len(args) > 0 {
			return strings.TrimSpace(toString(args[0])), nil
		}
	case "REPLACE":
		if len(args) >= 3 {
			return strings.ReplaceAll(toString(args[0]), toString(args[1]), toString(args[2])), nil
		}

	// Additional SQLite functions
	case "PRINTF":
		if len(args) > 0 {
			format := toString(args[0])
			fmtArgs := make([]interface{}, len(args)-1)
			for i := 1; i < len(args); i++ {
				fmtArgs[i-1] = args[i]
			}
			return fmt.Sprintf(format, fmtArgs...), nil
		}
	case "HEX":
		if len(args) > 0 {
			s := toString(args[0])
			return strings.ToUpper(fmt.Sprintf("%x", []byte(s))), nil
		}
	case "UNHEX":
		if len(args) > 0 {
			s := toString(args[0])
			var result []byte
			for i := 0; i < len(s)-1; i += 2 {
				var b byte
				fmt.Sscanf(s[i:i+2], "%x", &b)
				result = append(result, b)
			}
			return string(result), nil
		}
	case "RANDOM":
		return rand.Int63(), nil
	case "RANDOMBLOB":
		if len(args) > 0 {
			n := int(toFloat(args[0]))
			if n <= 0 {
				n = 1
			}
			if n > 1000000 {
				n = 1000000
			}
			blob := make([]byte, n)
			rand.Read(blob)
			return string(blob), nil
		}
	case "ZEROBLOB":
		if len(args) > 0 {
			n := int(toFloat(args[0]))
			if n <= 0 {
				n = 1
			}
			if n > 1000000 {
				n = 1000000
			}
			return string(make([]byte, n)), nil
		}
	case "INSTR":
		if len(args) >= 2 {
			s := toString(args[0])
			substr := toString(args[1])
			idx := strings.Index(s, substr)
			if idx < 0 {
				return int64(0), nil
			}
			return int64(idx + 1), nil // SQL is 1-indexed
		}
	case "GLOB":
		if len(args) >= 2 {
			pattern := toString(args[0])
			s := toString(args[1])
			return matchGlob(pattern, s), nil
		}
	case "ROUND":
		if len(args) > 0 {
			v := toFloat(args[0])
			decimals := 0
			if len(args) >= 2 {
				decimals = int(toFloat(args[1]))
			}
			mult := 1.0
			for i := 0; i < decimals; i++ {
				mult *= 10
			}
			return float64(int64(v*mult+0.5)) / mult, nil
		}
	case "MAX":
		if len(args) > 0 {
			max := args[0]
			for _, arg := range args[1:] {
				if compare(arg, max) > 0 {
					max = arg
				}
			}
			return max, nil
		}
	case "MIN":
		if len(args) > 0 {
			min := args[0]
			for _, arg := range args[1:] {
				if compare(arg, min) < 0 {
					min = arg
				}
			}
			return min, nil
		}
	case "CONCAT":
		var result strings.Builder
		for _, arg := range args {
			result.WriteString(toString(arg))
		}
		return result.String(), nil
	}

	return nil, nil
}

func (e *Executor) evalCaseExpr(expr *parser.CaseExpr, row storage.Row) (interface{}, error) {
	var operand interface{}
	if expr.Operand != nil {
		var err error
		operand, err = e.evalExpr(expr.Operand, row)
		if err != nil {
			return nil, err
		}
	}

	for _, when := range expr.Whens {
		cond, err := e.evalExpr(when.Condition, row)
		if err != nil {
			return nil, err
		}

		var match bool
		if expr.Operand != nil {
			// Simple CASE: CASE operand WHEN val THEN ... — NULL operand matches nothing
			if operand == nil {
				continue
			}
			match = compare(operand, cond) == 0
		} else {
			// Searched CASE: CASE WHEN cond THEN ... — NULL condition is falsy
			match = toBool(cond)
		}

		if match {
			return e.evalExpr(when.Result, row)
		}
	}

	if expr.Else != nil {
		return e.evalExpr(expr.Else, row)
	}

	return nil, nil
}

func (e *Executor) evalInExpr(expr *parser.InExpr, row storage.Row) (interface{}, error) {
	left, err := e.evalExpr(expr.Left, row)
	if err != nil {
		return nil, err
	}

	// Handle subquery: IN (SELECT ...)
	if expr.Subquery != nil {
		var result *Result
		var err error
		// Cache non-correlated subquery results for the duration of this query.
		// Safe when outerRow is nil (no outer context that the subquery could reference).
		if e.subqueryCache != nil && e.outerRow == nil {
			if cached, ok := e.subqueryCache[expr.Subquery]; ok {
				result = cached
			} else {
				result, err = e.executeSelect(expr.Subquery)
				if err == nil {
					e.subqueryCache[expr.Subquery] = result
				}
			}
		} else {
			result, err = e.executeSelect(expr.Subquery)
		}
		if err != nil {
			return nil, fmt.Errorf("IN subquery error: %w", err)
		}
		if len(result.Columns) != 1 {
			return nil, fmt.Errorf("subquery in IN must return exactly one column")
		}
		// SQL three-valued logic: if left is NULL → NULL; if any match → true; if any NULL → NULL; else false.
		if left == nil {
			return nil, nil
		}
		sawNull := false
		for _, resultRow := range result.Rows {
			if len(resultRow) == 0 {
				continue
			}
			v := resultRow[0]
			if v == nil {
				sawNull = true
				continue
			}
			if compare(left, v) == 0 {
				if expr.Not {
					return false, nil
				}
				return true, nil
			}
		}
		if sawNull {
			return nil, nil
		}
		if expr.Not {
			return true, nil
		}
		return false, nil
	}

	// Handle value list: IN (1, 2, 3).
	// Empty list: always FALSE (IN) / TRUE (NOT IN), even for NULL.
	if len(expr.Values) == 0 {
		return expr.Not, nil
	}
	// SQL three-valued logic: if left is NULL → NULL; if any match → true/false;
	// if list contains NULL and no match → NULL.
	if left == nil {
		return nil, nil
	}
	sawNull := false
	for _, val := range expr.Values {
		v, err := e.evalExpr(val, row)
		if err != nil {
			return nil, err
		}
		if v == nil {
			sawNull = true
			continue
		}
		if compare(left, v) == 0 {
			if expr.Not {
				return false, nil
			}
			return true, nil
		}
	}
	if sawNull {
		return nil, nil
	}
	if expr.Not {
		return true, nil
	}
	return false, nil
}

func (e *Executor) evalBetweenExpr(expr *parser.BetweenExpr, row storage.Row) (interface{}, error) {
	val, err := e.evalExpr(expr.Left, row)
	if err != nil {
		return nil, err
	}
	low, err := e.evalExpr(expr.Low, row)
	if err != nil {
		return nil, err
	}
	high, err := e.evalExpr(expr.High, row)
	if err != nil {
		return nil, err
	}

	if expr.Not {
		// NOT BETWEEN is equivalent to: val < low OR val > high
		// We need to handle NULL using three-valued OR logic:
		// NULL OR TRUE = TRUE
		// NULL OR FALSE = NULL
		// NULL OR NULL = NULL
		var lessThan, greaterThan interface{}

		if val == nil || low == nil {
			lessThan = nil // NULL
		} else {
			lessThan = compare(val, low) < 0
		}

		if val == nil || high == nil {
			greaterThan = nil // NULL
		} else {
			greaterThan = compare(val, high) > 0
		}

		// Implement three-valued OR
		if toBool(lessThan) || toBool(greaterThan) {
			return true, nil
		}
		if lessThan == nil || greaterThan == nil {
			return nil, nil // NULL
		}
		return false, nil
	} else {
		// BETWEEN is equivalent to: val >= low AND val <= high
		// Three-value logic: if val < low → FALSE (regardless of high); if val >= low and high is NULL → NULL
		if val == nil {
			return nil, nil
		}
		if low == nil {
			return nil, nil // val >= NULL = NULL
		}
		if compare(val, low) < 0 {
			return false, nil // val < low → definitely not in range
		}
		// val >= low: now check upper bound
		if high == nil {
			return nil, nil // val <= NULL = NULL
		}
		return compare(val, high) <= 0, nil
	}
}

func (e *Executor) evalLikeExpr(expr *parser.LikeExpr, row storage.Row) (interface{}, error) {
	val, err := e.evalExpr(expr.Left, row)
	if err != nil {
		return nil, err
	}
	pattern, err := e.evalExpr(expr.Pattern, row)
	if err != nil {
		return nil, err
	}

	s := toString(val)
	p := toString(pattern)

	// Convert SQL LIKE pattern to simple matching
	// % matches any sequence, _ matches single character
	matched := matchLike(s, p)
	if expr.Not {
		return !matched, nil
	}
	return matched, nil
}

func (e *Executor) evalIsNullExpr(expr *parser.IsNullExpr, row storage.Row) (interface{}, error) {
	val, err := e.evalExpr(expr.Left, row)
	if err != nil {
		return nil, err
	}

	isNull := val == nil
	if expr.Not {
		return !isNull, nil
	}
	return isNull, nil
}

func (e *Executor) evalCastExpr(expr *parser.CastExpr, row storage.Row) (interface{}, error) {
	val, err := e.evalExpr(expr.Expr, row)
	if err != nil {
		return nil, err
	}

	if val == nil {
		return nil, nil // CAST(NULL AS any) = NULL
	}

	typeName := strings.ToUpper(expr.Type.Name)
	switch {
	case strings.Contains(typeName, "INT"):
		return int64(toFloat(val)), nil
	case strings.Contains(typeName, "REAL"), strings.Contains(typeName, "FLOAT"), strings.Contains(typeName, "DOUBLE"):
		return toFloat(val), nil
	case strings.Contains(typeName, "TEXT"), strings.Contains(typeName, "CHAR"):
		return toString(val), nil
	default:
		return val, nil
	}
}

// evalSubqueryExpr executes a scalar subquery and returns its value.
// A scalar subquery must return exactly one column. It returns:
// - The single value if the subquery returns one row
// - NULL if the subquery returns no rows
// - Error if the subquery returns more than one row (for strict SQL compliance)
func (e *Executor) evalSubqueryExpr(expr *parser.SubqueryExpr, row storage.Row) (interface{}, error) {
	// Save and set outer row context for correlated subqueries
	savedOuter := e.outerRow
	e.outerRow = row
	defer func() { e.outerRow = savedOuter }()

	// Execute the subquery
	result, err := e.executeSelect(expr.Query)
	if err != nil {
		return nil, fmt.Errorf("subquery error: %w", err)
	}

	// Check for empty result
	if result.RowCount == 0 {
		return nil, nil // Return NULL for empty subquery
	}

	// Check column count
	if len(result.Columns) == 0 {
		return nil, fmt.Errorf("subquery must return at least one column")
	}

	// For scalar subquery, return first column of first row
	// Note: Strict SQL would error if more than one row is returned
	// but we follow SQLite behavior which just returns the first value
	if len(result.Rows) > 0 && len(result.Rows[0]) > 0 {
		return result.Rows[0][0], nil
	}

	return nil, nil
}

// evalExistsExpr evaluates an EXISTS expression.
// Returns true if the subquery returns at least one row, false otherwise.
func (e *Executor) evalExistsExpr(expr *parser.ExistsExpr, row storage.Row) (interface{}, error) {
	// Save and set outer row context for correlated subqueries
	savedOuter := e.outerRow
	e.outerRow = row
	defer func() { e.outerRow = savedOuter }()

	// Execute the subquery
	result, err := e.executeSelect(expr.Subquery)
	if err != nil {
		return nil, fmt.Errorf("EXISTS subquery error: %w", err)
	}

	// EXISTS returns true if any rows are returned
	return len(result.Rows) > 0, nil
}

// evalAggregateExpr evaluates an aggregate expression over multiple rows.
func (e *Executor) evalAggregateExpr(expr parser.Expr, rows []storage.Row) (interface{}, error) {
	fn, ok := expr.(*parser.FunctionCall)
	if !ok {
		// Not a function call - could be a binary expression with aggregates inside
		// Evaluate it with the aggregate evaluation context
		return e.evalExprWithAggregates(expr, rows)
	}

	name := strings.ToUpper(fn.Name)

	switch name {
	case "COUNT":
		if fn.Star {
			return int64(len(rows)), nil
		}
		if fn.Distinct {
			seen := make(map[interface{}]struct{})
			for _, row := range rows {
				if len(fn.Args) > 0 {
					val, _ := e.evalExpr(fn.Args[0], row)
					if val != nil {
						seen[val] = struct{}{}
					}
				}
			}
			return int64(len(seen)), nil
		}
		count := int64(0)
		for _, row := range rows {
			if len(fn.Args) > 0 {
				val, _ := e.evalExpr(fn.Args[0], row)
				if val != nil {
					count++
				}
			}
		}
		return count, nil

	case "SUM":
		var sum float64
		hasValues := false
		var seen map[interface{}]struct{}
		if fn.Distinct {
			seen = make(map[interface{}]struct{})
		}
		for _, row := range rows {
			if len(fn.Args) > 0 {
				val, _ := e.evalExpr(fn.Args[0], row)
				if val != nil {
					if fn.Distinct {
						key := fmt.Sprintf("%v", val)
						if _, exists := seen[key]; exists {
							continue
						}
						seen[key] = struct{}{}
					}
					sum += toFloat(val)
					hasValues = true
				}
			}
		}
		if !hasValues {
			return nil, nil
		}
		return sum, nil

	case "AVG":
		var sum float64
		count := 0
		for _, row := range rows {
			if len(fn.Args) > 0 {
				val, _ := e.evalExpr(fn.Args[0], row)
				if val != nil {
					sum += toFloat(val)
					count++
				}
			}
		}
		if count == 0 {
			return nil, nil
		}
		return sum / float64(count), nil

	case "MIN":
		var min interface{}
		for _, row := range rows {
			if len(fn.Args) > 0 {
				val, _ := e.evalExpr(fn.Args[0], row)
				if val != nil && (min == nil || compare(val, min) < 0) {
					min = val
				}
			}
		}
		return min, nil

	case "MAX":
		var max interface{}
		for _, row := range rows {
			if len(fn.Args) > 0 {
				val, _ := e.evalExpr(fn.Args[0], row)
				if val != nil && (max == nil || compare(val, max) > 0) {
					max = val
				}
			}
		}
		return max, nil

	default:
		// Try scalar function
		if len(rows) > 0 {
			return e.evalFunctionCall(fn, rows[0])
		}
		return nil, nil
	}
}

// evalExprWithAggregates evaluates an expression that may contain aggregate functions
func (e *Executor) evalExprWithAggregates(expr parser.Expr, rows []storage.Row) (interface{}, error) {
	switch ex := expr.(type) {
	case *parser.BinaryExpr:
		left, err := e.evalExprWithAggregates(ex.Left, rows)
		if err != nil {
			return nil, err
		}
		right, err := e.evalExprWithAggregates(ex.Right, rows)
		if err != nil {
			return nil, err
		}

		// Apply the binary operator
		switch ex.Op {
		case lexer.TokenPlus:
			if left == nil || right == nil {
				return nil, nil
			}
			return toFloat(left) + toFloat(right), nil
		case lexer.TokenMinus:
			if left == nil || right == nil {
				return nil, nil
			}
			return toFloat(left) - toFloat(right), nil
		case lexer.TokenStar:
			if left == nil || right == nil {
				return nil, nil
			}
			return toFloat(left) * toFloat(right), nil
		case lexer.TokenSlash:
			if left == nil || right == nil {
				return nil, nil
			}
			r := toFloat(right)
			if r == 0 {
				return nil, nil
			}
			return toFloat(left) / r, nil
		case lexer.TokenPercent:
			if left == nil || right == nil {
				return nil, nil
			}
			return int64(toFloat(left)) % int64(toFloat(right)), nil
		case lexer.TokenEq:
			return compare(left, right) == 0, nil
		case lexer.TokenNeq:
			return compare(left, right) != 0, nil
		case lexer.TokenLt:
			return compare(left, right) < 0, nil
		case lexer.TokenLte:
			return compare(left, right) <= 0, nil
		case lexer.TokenGt:
			return compare(left, right) > 0, nil
		case lexer.TokenGte:
			return compare(left, right) >= 0, nil
		case lexer.TokenAND:
			return toBool(left) && toBool(right), nil
		case lexer.TokenOR:
			return toBool(left) || toBool(right), nil
		case lexer.TokenConcat:
			return toString(left) + toString(right), nil
		default:
			return nil, fmt.Errorf("unsupported operator: %v", ex.Op)
		}
	case *parser.FunctionCall:
		return e.evalAggregateExpr(expr, rows)
	case *parser.ParenExpr:
		// Evaluate the inner expression
		return e.evalExprWithAggregates(ex.Expr, rows)
	case *parser.UnaryExpr:
		operand, err := e.evalExprWithAggregates(ex.Operand, rows)
		if err != nil {
			return nil, err
		}
		// Apply the unary operator
		switch ex.Op {
		case lexer.TokenPlus:
			return operand, nil
		case lexer.TokenMinus:
			if operand == nil {
				return nil, nil
			}
			return -toFloat(operand), nil
		case lexer.TokenNOT:
			return !toBool(operand), nil
		default:
			return nil, fmt.Errorf("unsupported unary operator: %v", ex.Op)
		}
	case *parser.CastExpr:
		// Evaluate the inner expression first
		val, err := e.evalExprWithAggregates(ex.Expr, rows)
		if err != nil {
			return nil, err
		}
		// For aggregate context, we just return the value
		// The actual casting will be handled elsewhere if needed
		return val, nil
	default:
		// Non-aggregate expression
		// For literals and constants, we can evaluate without a row
		if _, ok := expr.(*parser.LiteralExpr); ok {
			// Create a dummy empty row for literal evaluation
			return e.evalExpr(expr, storage.Row{})
		}
		// For other expressions, use first row if available
		if len(rows) > 0 {
			return e.evalExpr(expr, rows[0])
		}
		return nil, nil
	}
}

// Helper functions

func (e *Executor) getSelectColumns(stmt *parser.SelectStmt, schema *storage.Schema) []string {
	var columns []string
	for _, col := range stmt.Columns {
		if col.Star {
			for _, c := range schema.Columns {
				columns = append(columns, c.Name)
			}
		} else if col.Alias != "" {
			columns = append(columns, col.Alias)
		} else if ref, ok := col.Expr.(*parser.ColumnRef); ok {
			columns = append(columns, ref.Column)
		} else {
			columns = append(columns, fmt.Sprintf("column%d", len(columns)+1))
		}
	}
	return columns
}

func (e *Executor) hasAggregates(columns []parser.SelectColumn) bool {
	for _, col := range columns {
		if e.isAggregate(col.Expr) {
			return true
		}
	}
	return false
}

func (e *Executor) isAggregate(expr parser.Expr) bool {
	if fn, ok := expr.(*parser.FunctionCall); ok {
		name := strings.ToUpper(fn.Name)
		switch name {
		case "COUNT", "SUM", "AVG", "MIN", "MAX", "TOTAL", "GROUP_CONCAT":
			return true
		}
		return false
	}
	switch ex := expr.(type) {
	case *parser.UnaryExpr:
		return e.isAggregate(ex.Operand)
	case *parser.BinaryExpr:
		return e.isAggregate(ex.Left) || e.isAggregate(ex.Right)
	case *parser.ParenExpr:
		return e.isAggregate(ex.Expr)
	case *parser.CaseExpr:
		for _, w := range ex.Whens {
			if e.isAggregate(w.Result) {
				return true
			}
		}
		if ex.Else != nil {
			return e.isAggregate(ex.Else)
		}
	case *parser.CastExpr:
		return e.isAggregate(ex.Expr)
	}
	return false
}

func (e *Executor) buildGroupKey(groupBy []parser.Expr, row storage.Row) string {
	var parts []string
	for _, expr := range groupBy {
		val, _ := e.evalExpr(expr, row)
		parts = append(parts, fmt.Sprintf("%v", val))
	}
	return strings.Join(parts, "|")
}

// resolveOrderByPositions replaces positional ORDER BY expressions (e.g. ORDER BY 1)
// with the corresponding SELECT column expressions per SQL-92 semantics.
func resolveOrderByPositions(orderBy []parser.OrderByItem, selectCols []parser.SelectColumn) []parser.OrderByItem {
	result := make([]parser.OrderByItem, len(orderBy))
	for i, item := range orderBy {
		if lit, ok := item.Expr.(*parser.LiteralExpr); ok {
			if pos, err := strconv.Atoi(lit.Value); err == nil && pos >= 1 && pos <= len(selectCols) {
				col := selectCols[pos-1]
				if col.Expr != nil {
					result[i] = parser.OrderByItem{Expr: col.Expr, Desc: item.Desc}
					continue
				}
			}
		}
		result[i] = item
	}
	return result
}

func (e *Executor) sortRows(rows []storage.Row, orderBy []parser.OrderByItem) {
	sort.Slice(rows, func(i, j int) bool {
		for _, item := range orderBy {
			vi, _ := e.evalExpr(item.Expr, rows[i])
			vj, _ := e.evalExpr(item.Expr, rows[j])
			cmp := compare(vi, vj)
			if cmp != 0 {
				if item.Desc {
					return cmp > 0
				}
				return cmp < 0
			}
		}
		return false
	})
}

// sortResultRows sorts Result.Rows based on ORDER BY clauses.
// It handles column aliases by matching them against the select columns.
func (e *Executor) sortResultRows(result *Result, orderBy []parser.OrderByItem, selectColumns []parser.SelectColumn, columnNames []string) {
	orderBy = resolveOrderByPositions(orderBy, selectColumns)
	sort.Slice(result.Rows, func(i, j int) bool {
		for _, item := range orderBy {
			var vi, vj interface{}
			var rowI, rowJ storage.Row

			// Check if ORDER BY references a column alias
			if ref, ok := item.Expr.(*parser.ColumnRef); ok && ref.Table == "" {
				// Look for matching alias in select columns
				for idx, name := range columnNames {
					if strings.EqualFold(name, ref.Column) {
						if idx < len(result.Rows[i]) {
							vi = result.Rows[i][idx]
							vj = result.Rows[j][idx]
							goto compare
						}
					}
				}
			}

			// If not found as alias, try to evaluate the expression
			// Create temporary rows from result rows for evaluation
			rowI = e.resultRowToStorageRow(result, i)
			rowJ = e.resultRowToStorageRow(result, j)
			vi, _ = e.evalExpr(item.Expr, rowI)
			vj, _ = e.evalExpr(item.Expr, rowJ)

		compare:
			cmp := compare(vi, vj)
			if cmp != 0 {
				if item.Desc {
					return cmp > 0
				}
				return cmp < 0
			}
		}
		return false
	})
}

// resultRowToStorageRow converts a Result row back to storage.Row for expression evaluation.
func (e *Executor) resultRowToStorageRow(result *Result, rowIdx int) storage.Row {
	row := make(storage.Row)
	for colIdx, colName := range result.Columns {
		if colIdx < len(result.Rows[rowIdx]) {
			row[colName] = result.Rows[rowIdx][colIdx]
		}
	}
	return row
}

func (e *Executor) evalIntExpr(expr parser.Expr) int {
	val, _ := e.evalExpr(expr, nil)
	return int(toFloat(val))
}

// Type conversion helpers

func isIntVal(v interface{}) bool {
	switch val := v.(type) {
	case int64:
		return true
	case int:
		return true
	case float64:
		return val == math.Trunc(val) && !math.IsInf(val, 0) && !math.IsNaN(val)
	case bool:
		return true
	default:
		_ = val
		return false
	}
}

func toInt64(v interface{}) int64 {
	switch val := v.(type) {
	case int64:
		return val
	case int:
		return int64(val)
	case float64:
		return int64(val)
	case bool:
		if val {
			return 1
		}
		return 0
	default:
		return 0
	}
}

func toFloat(v interface{}) float64 {
	switch val := v.(type) {
	case nil:
		return 0
	case int64:
		return float64(val)
	case int:
		return float64(val)
	case float64:
		return val
	case bool:
		if val {
			return 1
		}
		return 0
	case string:
		f, _ := strconv.ParseFloat(val, 64)
		return f
	default:
		return 0
	}
}

func toBool(v interface{}) bool {
	switch val := v.(type) {
	case nil:
		return false
	case bool:
		return val
	case int64:
		return val != 0
	case int:
		return val != 0
	case float64:
		return val != 0
	case string:
		return val != "" && val != "0" && strings.ToLower(val) != "false"
	default:
		return false
	}
}

func toString(v interface{}) string {
	if v == nil {
		return ""
	}
	return fmt.Sprintf("%v", v)
}

func compare(a, b interface{}) int {
	if a == nil && b == nil {
		return 0
	}
	if a == nil {
		return -1
	}
	if b == nil {
		return 1
	}

	// Try numeric comparison
	fa, oka := toNumeric(a)
	fb, okb := toNumeric(b)
	if oka && okb {
		if fa < fb {
			return -1
		}
		if fa > fb {
			return 1
		}
		return 0
	}

	// String comparison
	sa := toString(a)
	sb := toString(b)
	return strings.Compare(sa, sb)
}

func toNumeric(v interface{}) (float64, bool) {
	switch val := v.(type) {
	case int64:
		return float64(val), true
	case int:
		return float64(val), true
	case float64:
		return val, true
	case string:
		f, err := strconv.ParseFloat(val, 64)
		return f, err == nil
	default:
		return 0, false
	}
}

// splitANDClauses flattens a tree of AND binary expressions into a slice of leaf conditions.
func splitANDClauses(expr parser.Expr) []parser.Expr {
	if bin, ok := expr.(*parser.BinaryExpr); ok && bin.Op == lexer.TokenAND {
		left := splitANDClauses(bin.Left)
		right := splitANDClauses(bin.Right)
		return append(left, right...)
	}
	return []parser.Expr{expr}
}

// collectColumnRefs returns all unqualified column names referenced in an expression.
func collectColumnRefs(expr parser.Expr) []string {
	var refs []string
	var walk func(parser.Expr)
	walk = func(e parser.Expr) {
		if e == nil {
			return
		}
		switch n := e.(type) {
		case *parser.ColumnRef:
			refs = append(refs, n.Column)
		case *parser.BinaryExpr:
			walk(n.Left)
			walk(n.Right)
		case *parser.UnaryExpr:
			walk(n.Operand)
		case *parser.InExpr:
			walk(n.Left)
			for _, v := range n.Values {
				walk(v)
			}
		case *parser.BetweenExpr:
			walk(n.Left)
			walk(n.Low)
			walk(n.High)
		case *parser.LikeExpr:
			walk(n.Left)
			walk(n.Pattern)
		case *parser.IsNullExpr:
			walk(n.Left)
		case *parser.CaseExpr:
			walk(n.Operand)
			for _, w := range n.Whens {
				walk(w.Condition)
				walk(w.Result)
			}
			walk(n.Else)
		case *parser.FunctionCall:
			for _, a := range n.Args {
				walk(a)
			}
		case *parser.ParenExpr:
			walk(n.Expr)
		}
	}
	walk(expr)
	return refs
}

// combineAND combines a list of expressions with AND.
func combineAND(clauses []parser.Expr) parser.Expr {
	if len(clauses) == 0 {
		return nil
	}
	result := clauses[0]
	for _, c := range clauses[1:] {
		result = &parser.BinaryExpr{Left: result, Op: lexer.TokenAND, Right: c}
	}
	return result
}

// matchLike matches a string against a SQL LIKE pattern.
func matchLike(s, pattern string) bool {
	// Simple implementation - convert to lowercase for case-insensitive matching
	s = strings.ToLower(s)
	pattern = strings.ToLower(pattern)

	return matchLikeHelper(s, pattern)
}

func matchLikeHelper(s, p string) bool {
	if p == "" {
		return s == ""
	}

	if p[0] == '%' {
		// % matches any sequence
		for i := 0; i <= len(s); i++ {
			if matchLikeHelper(s[i:], p[1:]) {
				return true
			}
		}
		return false
	}

	if s == "" {
		return false
	}

	if p[0] == '_' || p[0] == s[0] {
		return matchLikeHelper(s[1:], p[1:])
	}

	return false
}

// matchGlob matches a string against a GLOB pattern.
// GLOB uses * for any sequence and ? for single character (case-sensitive).
func matchGlob(pattern, s string) bool {
	return matchGlobHelper(pattern, s)
}

func matchGlobHelper(p, s string) bool {
	if p == "" {
		return s == ""
	}

	if p[0] == '*' {
		// * matches any sequence
		for i := 0; i <= len(s); i++ {
			if matchGlobHelper(p[1:], s[i:]) {
				return true
			}
		}
		return false
	}

	if s == "" {
		return false
	}

	if p[0] == '?' || p[0] == s[0] {
		return matchGlobHelper(p[1:], s[1:])
	}

	// Handle character classes [...]
	if p[0] == '[' {
		end := strings.Index(p, "]")
		if end > 0 {
			class := p[1:end]
			match := false
			negate := false
			if len(class) > 0 && class[0] == '^' {
				negate = true
				class = class[1:]
			}
			for _, c := range class {
				if byte(c) == s[0] {
					match = true
					break
				}
			}
			if negate {
				match = !match
			}
			if match {
				return matchGlobHelper(p[end+1:], s[1:])
			}
		}
	}

	return false
}

// applyDistinct removes duplicate rows from the result
func (e *Executor) applyDistinct(rows [][]interface{}) [][]interface{} {
	if len(rows) == 0 {
		return rows
	}

	seen := make(map[string]bool)
	uniqueRows := make([][]interface{}, 0)

	for _, row := range rows {
		// Create a key from all column values
		key := ""
		for i, val := range row {
			if i > 0 {
				key += "\x00" // Use null byte as separator
			}
			key += fmt.Sprintf("%v", val)
		}

		if !seen[key] {
			seen[key] = true
			uniqueRows = append(uniqueRows, row)
		}
	}

	return uniqueRows
}