package analytics

import (
	"fmt"
	"regexp"
	"strings"

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

// reSafeIdentifier matches safe SQL identifiers used by runtime-owned tables/views.
var reSafeIdentifier = regexp.MustCompile(`^[a-zA-Z_][a-zA-Z0-9_]*$`)
var reAggregateMarker = regexp.MustCompile(regexp.QuoteMeta(AggMarkerPrefix) + `([^#]+)##([^#]+)##([^#]+)##([^#]+)##`)

// validateIdentifier checks that a SQL identifier is safe for backtick-quoting.
// Rejects identifiers containing backticks, semicolons, or other special characters.
func validateIdentifier(name, label string) error {
	if !reSafeIdentifier.MatchString(name) {
		return fmt.Errorf("invalid %s identifier: %q", label, name)
	}
	return nil
}

// ViewColumn describes a column in the generated View.
type ViewColumn struct {
	Name       string // Column name in the view (API name)
	Expression string // SQL expression (or empty for base column pass-through)
	IsFormula  bool   // True if computed by formula
}

// JoinSpec describes a LEFT JOIN needed for cross-table references.
type JoinSpec struct {
	Alias            string // SQL alias
	TargetObject     string // Logical object API name (used for schema/metadata lookups)
	TargetTable      string // SQL table/view name emitted in the JOIN clause
	SourceField      string // FK column on parent table
	TargetField      string // PK column on target table
	RelationshipName string // Metadata relationship name used by formula refs
	// UsesCurrentObjectProjection means this join must target the previous
	// computed inner projection in outer layers rather than a physical table
	// or recursive v_<object> view.
	UsesCurrentObjectProjection bool
	// FilterTargetDeleted, when true, appends an IsDeleted exclusion predicate
	// to the ON clause. This matches the ExcludeDeleted semantics of a
	// lookupValue projection that has been hoisted from a correlated subquery
	// into a pre-registered LEFT JOIN.
	FilterTargetDeleted bool
	// HoistedLookupValue marks a JOIN that was synthesised from a single-PK-match
	// lookupValue hoist. Such joins are referenced only by a direct
	// "<alias>.<field>" column ref emitted by compileLookupValue's fast path and
	// must NOT participate in CrossRef relationship-name resolution. Excluding
	// them prevents the lv_ alias from overwriting the j_ alias for the same
	// target object in the joinAliases map, which would cause CrossRef formulas
	// (e.g. Account.City) to resolve to the lv_ join instead of the j_ join.
	HoistedLookupValue bool
}

// AggregateJoinSpec describes a LEFT JOIN subquery for rollup aggregates.
type AggregateJoinSpec struct {
	Alias       string // Unique alias for the subquery (e.g. "rsq0")
	ChildObject string // Logical child object API name used in formula markers
	ChildTable  string // Physical table name
	AggFunc     string // SUM, COUNT, MIN, MAX, AVG
	AggField    string // Field to aggregate (or "*" for COUNT)
	ForeignKey  string // FK on child table pointing to parent
	ParentKey   string // PK on parent table (typically "Id")
	OutputAlias string // Column alias in the subquery output (e.g. "agg_val")
}

type aggregateMarker struct {
	Func        string
	ChildObject string
	Field       string
	ForeignKey  string
}

// DeepJoinChain describes a multi-hop JOIN chain for deep cross-table refs like Parent.Manager.Name.
// It generates chained LEFT JOINs and maps the DeepCrossRef to the final alias.field.
type DeepJoinChain struct {
	// Parts matches the DeepCrossRef.Parts.
	Parts []string
	// Hops describes each JOIN in the chain (excluding the final field)
	Hops []DeepJoinHop
	// FinalAlias is the SQL alias for the last table (e.g. "dj_prof")
	FinalAlias string
	// FinalField is the field to SELECT from the final table (e.g. "Name")
	FinalField string
}

// DeepJoinHop is a single hop in a multi-hop JOIN chain.
type DeepJoinHop struct {
	Alias         string // SQL alias for this hop's table (e.g. "dj_own", "dj_prof")
	LogicalObject string // Logical object API name (used for schema/metadata lookups)
	TargetTable   string // SQL table/view name emitted in the JOIN clause
	SourceAlias   string // Alias of the previous hop (or cfg.TableAlias for the first hop)
	SourceField   string // FK column on the source table
	TargetField   string // PK column on the target table (e.g. "Id")
	// UsesCurrentObjectProjection means this hop must target the previous
	// computed inner projection in outer layers instead of a physical table
	// or recursive v_<object> view.
	UsesCurrentObjectProjection bool
}

// ViewBuildConfig holds all input needed to generate a CREATE OR REPLACE VIEW DDL.
type ViewBuildConfig struct {
	ObjectName    string   // Object API name
	PhysicalTable string   // Physical table name
	TableAlias    string   // e.g. "t"
	BaseColumns   []string // Non-formula column names to include
	Formulas      []FormulaDefinition
	Joins         []JoinSpec // Pre-specified joins (can also be auto-detected)
	AggJoins      []AggregateJoinSpec
	DeepJoins     []DeepJoinChain // Multi-hop JOIN chains for DeepCrossRef

	// BaseColumnAliases maps APIName → PhysicalName for base (non-formula) columns
	// where the physical DB column name differs from the logical API name.
	// When set, L0 emits `t.PhysicalName AS APIName` instead of `t.APIName`,
	// so outer layers can reference the logical name exposed by L0.
	BaseColumnAliases map[string]string

	// CrossRefFieldAliases maps logical relationship fields to physical
	// metadata-owned fields when an object's logical display field resolves to
	// a differently named physical field.
	CrossRefFieldAliases map[string]string

	// CrossRefFieldExpressions contains complete SQL reads for immutable
	// metadata-owned virtual fields, such as compound Name projections.
	CrossRefFieldExpressions map[string]string

	// FieldTypes threads Salesforce field types into the compiler so it can
	// emit type-aware SQL (e.g. DATEDIFF for date - date rather than the
	// YYYYMMDD numeric subtraction MySQL performs on raw date operands).
	//
	// Keys:
	//   - "<field>"           for fields on the current object
	//   - "<object>.<field>"  for cross-ref sources (matches CrossRef.Object +
	//                         "." + CrossRef.Field in the AST)
	//   - "<a>.<b>.<c>"       for deep refs (strings.Join(DeepCrossRef.Parts, "."))
	//
	// Values are lowercased Salesforce types ("date", "currency", "percent",
	// "string", …). Missing keys default to unknown and the compiler falls
	// back to type-naive emission.
	FieldTypes map[string]string

	// FunctionExecutables is the active formula-function executable bundle
	// registry for SQL lowering. Nil keeps L0 on the built-in catalog; app/runtime
	// callers pass a registry built from installed or candidate metadata.
	FunctionExecutables *formula.ExecutableFunctionRegistry
}

// DSLViewBuilder generates TiDB View DDL from formula definitions using the
// DSL parser/compiler pipeline with a clean AST-based approach.
type DSLViewBuilder struct {
	// maxViewDDLBytes is intentionally unexported: production callers always use
	// the audited default, while package tests can exercise the failure boundary
	// without constructing a megabyte-scale fixture.
	maxViewDDLBytes int
}

// NewDSLViewBuilder creates a new builder.
func NewDSLViewBuilder() *DSLViewBuilder {
	return &DSLViewBuilder{maxViewDDLBytes: maxFormulaViewDDLBytes}
}

func (b *DSLViewBuilder) viewDDLByteLimit() int {
	if b == nil || b.maxViewDDLBytes <= 0 {
		return maxFormulaViewDDLBytes
	}
	return b.maxViewDDLBytes
}

// boundedSQLBuilder prevents cumulative view assembly from exceeding its byte
// budget. In particular, WriteIndented streams nested queries into the target
// builder instead of first allocating another full indented copy.
type boundedSQLBuilder struct {
	builder strings.Builder
	scope   string
	max     int
	err     error
}

func newBoundedSQLBuilder(scope string, maxBytes int) *boundedSQLBuilder {
	return &boundedSQLBuilder{scope: scope, max: maxBytes}
}

func (b *boundedSQLBuilder) WriteString(value string) (int, error) {
	if b.err != nil {
		return 0, b.err
	}
	if err := checkFormulaSQLOutputSize(b.scope, b.builder.Len()+len(value), b.max); err != nil {
		b.err = err
		return 0, err
	}
	return b.builder.WriteString(value)
}

func (b *boundedSQLBuilder) WriteIndented(value, prefix string) {
	if b.err != nil {
		return
	}
	additionalBytes := len(value) + nonEmptyLineCount(value)*len(prefix)
	if err := checkFormulaSQLOutputSize(b.scope, b.builder.Len()+additionalBytes, b.max); err != nil {
		b.err = err
		return
	}

	lineStart := 0
	for lineStart <= len(value) {
		lineEnd := strings.IndexByte(value[lineStart:], '\n')
		if lineEnd < 0 {
			lineEnd = len(value)
		} else {
			lineEnd += lineStart
		}
		if lineEnd > lineStart {
			_, _ = b.builder.WriteString(prefix)
			_, _ = b.builder.WriteString(value[lineStart:lineEnd])
		}
		if lineEnd == len(value) {
			break
		}
		_, _ = b.builder.WriteString("\n")
		lineStart = lineEnd + 1
	}
}

func (b *boundedSQLBuilder) Err() error {
	return b.err
}

func (b *boundedSQLBuilder) String() string {
	return b.builder.String()
}

func nonEmptyLineCount(value string) int {
	count := 0
	lineStart := 0
	for lineStart <= len(value) {
		lineEnd := strings.IndexByte(value[lineStart:], '\n')
		if lineEnd < 0 {
			lineEnd = len(value)
		} else {
			lineEnd += lineStart
		}
		if lineEnd > lineStart {
			count++
		}
		if lineEnd == len(value) {
			break
		}
		lineStart = lineEnd + 1
	}
	return count
}

type formulaSQLWriter interface {
	WriteString(string) (int, error)
}

func qualifyFormulaName(objectName, fieldName string) string {
	objectName = strings.TrimSpace(objectName)
	fieldName = strings.TrimSpace(fieldName)
	switch {
	case objectName == "":
		return fieldName
	case fieldName == "":
		return objectName
	default:
		return objectName + "." + fieldName
	}
}

// BuildViewDDL generates a complete CREATE OR REPLACE VIEW statement.
// It parses all formulas, builds a dependency DAG, determines layer ordering,
// and generates nested subqueries for multi-layer formula dependencies.
func (b *DSLViewBuilder) BuildViewDDL(cfg ViewBuildConfig) (string, error) {
	if len(cfg.Formulas) == 0 {
		return "", fmt.Errorf("no formulas provided for %s", cfg.ObjectName)
	}

	// Validate identifiers to prevent injection via backtick-quoted names
	if err := validateIdentifier(cfg.ObjectName, "ObjectName"); err != nil {
		return "", err
	}
	if cfg.PhysicalTable != "" {
		if err := validateIdentifier(cfg.PhysicalTable, "PhysicalTable"); err != nil {
			return "", err
		}
	}
	for _, j := range cfg.Joins {
		if err := validateIdentifier(j.TargetTable, "JoinTargetTable"); err != nil {
			return "", err
		}
	}

	viewName := "v_" + strings.ToLower(cfg.ObjectName)
	if cfg.TableAlias == "" {
		cfg.TableAlias = "t"
	}
	if cfg.PhysicalTable == "" {
		cfg.PhysicalTable = strings.ToLower(cfg.ObjectName)
	}

	normalizedFormulas := make([]FormulaDefinition, len(cfg.Formulas))
	copy(normalizedFormulas, cfg.Formulas)
	for i := range normalizedFormulas {
		if strings.TrimSpace(normalizedFormulas[i].ObjectName) == "" {
			normalizedFormulas[i].ObjectName = cfg.ObjectName
		}
	}
	fieldTypes := withFormulaReturnTypes(cfg.FieldTypes, normalizedFormulas)

	// 1. Build DAG and get layers
	dag, err := NewFormulaDAG(normalizedFormulas)
	if err != nil {
		return "", fmt.Errorf("DAG construction: %w", err)
	}

	layers := dag.Layers()

	// 2. Build JOIN aliases from the metadata-derived join plan.
	joinAliases := make(map[string]string)
	ambiguousTargets := crossRefAmbiguousTargetKeys(cfg.Joins)
	for _, j := range cfg.Joins {
		// Hoisted lookupValue joins must only be reachable by their own alias
		// (used by compileLookupValue's direct "<alias>.<field>" emission). They
		// must not register CrossRef relationship-name keys (e.g. "Lead") because
		// that would overwrite the j_ alias for the same target and cause CrossRef
		// formulas like Lead.City to silently resolve to the lv_ join instead.
		if j.HoistedLookupValue {
			addJoinAliasKey(joinAliases, j.Alias, j.Alias)
			continue
		}
		logicalTarget := j.TargetObject
		if strings.TrimSpace(logicalTarget) == "" {
			logicalTarget = j.TargetTable
		}
		for _, key := range crossRefObjectKeysForPlan(ambiguousTargets, logicalTarget, j) {
			addJoinAliasKey(joinAliases, key, j.Alias)
		}
		addJoinAliasKey(joinAliases, j.Alias, j.Alias)
	}

	// 3. Build compile context (with DeepJoinMap for multi-hop refs)
	deepJoinMap := buildDeepJoinMap(cfg.DeepJoins)
	ctx := &CompileContext{
		TableAlias:               cfg.TableAlias,
		JoinAliases:              joinAliases,
		CrossRefFieldAliases:     cfg.CrossRefFieldAliases,
		CrossRefFieldExpressions: cfg.CrossRefFieldExpressions,
		DeepJoinMap:              deepJoinMap,
		QualifyFields:            true,
		FieldTypes:               fieldTypes,
		FunctionExecutables:      cfg.FunctionExecutables,
		// Thread physical-name map into L0/single-layer so FieldRef nodes resolve
		// logical API names to their physical DB column equivalents. Outer layers
		// (L1+) omit this: by then the inner projection exposes the logical alias.
		BaseFieldPhysicalMap: cfg.BaseColumnAliases,
	}

	// 4. Generate DDL based on number of layers
	if len(layers) == 1 {
		return b.buildSingleLayerDDL(viewName, cfg, dag, layers[0], ctx)
	}
	return b.buildMultiLayerDDL(viewName, cfg, dag, layers, ctx)
}

func compileContextForFormula(base *CompileContext, def FormulaDefinition) *CompileContext {
	if base == nil {
		return &CompileContext{
			AllowedLookupValues: def.LookupValueRules,
			formulaSQLScope:     qualifyFormulaName(def.ObjectName, def.Name),
		}
	}
	next := *base
	next.AllowedLookupValues = def.LookupValueRules
	next.formulaSQLScope = qualifyFormulaName(def.ObjectName, def.Name)
	return &next
}

// buildSingleLayerDDL: all formulas only depend on base fields.
func (b *DSLViewBuilder) buildSingleLayerDDL(
	viewName string, cfg ViewBuildConfig,
	dag *FormulaDAG, layer []FormulaDefinition, ctx *CompileContext,
) (string, error) {
	sb := newBoundedSQLBuilder(viewName, b.viewDDLByteLimit())
	sb.WriteString(fmt.Sprintf("%s `%s` AS\nSELECT\n", CreateOrReplaceViewStmt, viewName))

	// Base columns — apply PhysicalName aliasing consistent with buildMultiLayerDDL.
	for i, col := range cfg.BaseColumns {
		if i > 0 {
			sb.WriteString(",\n")
		}
		if physCol, ok := cfg.BaseColumnAliases[col]; ok && physCol != "" && physCol != col {
			sb.WriteString(fmt.Sprintf("  %s.`%s` AS `%s`", cfg.TableAlias, physCol, col))
		} else {
			sb.WriteString(fmt.Sprintf("  %s.`%s`", cfg.TableAlias, col))
		}
	}
	if err := sb.Err(); err != nil {
		return "", err
	}

	// Formula columns
	for _, def := range layer {
		ast := dag.AST(def.Name)
		if ast == nil {
			continue
		}

		sql, err := Compile(ast, compileContextForFormula(ctx, def))
		if err != nil {
			return "", fmt.Errorf("compiling %s: %w", qualifyFormulaName(cfg.ObjectName, def.Name), err)
		}

		// Handle aggregate markers
		sql, err = b.resolveAggregateMarkers(sql, cfg, aggregateJoinReference)
		if err != nil {
			return "", fmt.Errorf("resolving aggregates for %s: %w", qualifyFormulaName(cfg.ObjectName, def.Name), err)
		}
		sql = fromFormulaDomainSQL(sql, def.ReturnType)

		sb.WriteString(",\n")
		sb.WriteString(fmt.Sprintf("  %s AS `%s`", sql, def.Name))
		if err := sb.Err(); err != nil {
			return "", err
		}
	}

	// FROM + JOINs
	sb.WriteString(fmt.Sprintf("\nFROM `%s` %s", cfg.PhysicalTable, cfg.TableAlias))

	appendFormulaAggregateJoins(sb, cfg.AggJoins, cfg.TableAlias, "", "  ")
	appendFormulaRelationshipJoins(sb, cfg.Joins, cfg.TableAlias, "", nil)
	appendFormulaDeepJoins(sb, cfg.DeepJoins, "", nil)
	if err := sb.Err(); err != nil {
		return "", err
	}

	return sb.String(), nil
}

// joinSoftDeletePredicate returns the ON-clause suffix that excludes soft-deleted
// rows on the given alias. Used by JoinSpec.FilterTargetDeleted to match the
// ExcludeDeleted semantics of a hoisted lookupValue projection.
func joinSoftDeletePredicate(alias string) string {
	return fmt.Sprintf(" AND (%s.`%s` = %d OR %s.`%s` IS NULL)",
		alias, constants.ColIsDeleted, constants.IsDeletedFalse,
		alias, constants.ColIsDeleted,
	)
}

func appendFormulaAggregateJoins(
	sb formulaSQLWriter,
	aggJoins []AggregateJoinSpec,
	parentAlias string,
	linePrefix string,
	subqueryPrefix string,
) {
	for _, aj := range aggJoins {
		sb.WriteString(fmt.Sprintf("\n%sLEFT JOIN (\n%sSELECT `%s`, %s(%s) AS `%s`\n%sFROM `%s`\n%sWHERE `%s` = %d\n%sGROUP BY `%s`\n%s) %s ON %s.`%s` = %s.`%s`",
			linePrefix,
			subqueryPrefix, aj.ForeignKey, aj.AggFunc,
			formatAggField(aj.AggField),
			aj.OutputAlias,
			subqueryPrefix, aj.ChildTable,
			subqueryPrefix, constants.ColIsDeleted, constants.IsDeletedFalse,
			subqueryPrefix, aj.ForeignKey,
			linePrefix, aj.Alias,
			aj.Alias, aj.ForeignKey,
			parentAlias, aj.ParentKey,
		))
	}
}

func appendFormulaRelationshipJoins(
	sb formulaSQLWriter,
	joins []JoinSpec,
	sourceAlias string,
	linePrefix string,
	currentProjectionTarget func(string) string,
) {
	for _, j := range joins {
		targetTable := formulaJoinTargetTable(j.TargetTable, j.TargetObject, j.UsesCurrentObjectProjection, currentProjectionTarget)
		sb.WriteString(fmt.Sprintf("\n%sLEFT JOIN `%s` %s ON %s.`%s` = %s.`%s`",
			linePrefix, targetTable, j.Alias,
			sourceAlias, j.SourceField,
			j.Alias, j.TargetField,
		))
		if j.FilterTargetDeleted {
			sb.WriteString(joinSoftDeletePredicate(j.Alias))
		}
	}
}

func appendFormulaDeepJoins(
	sb formulaSQLWriter,
	chains []DeepJoinChain,
	linePrefix string,
	currentProjectionTarget func(string) string,
) {
	for _, dj := range chains {
		for _, hop := range dj.Hops {
			targetTable := formulaJoinTargetTable(hop.TargetTable, hop.LogicalObject, hop.UsesCurrentObjectProjection, currentProjectionTarget)
			sb.WriteString(fmt.Sprintf("\n%sLEFT JOIN `%s` %s ON %s.`%s` = %s.`%s`",
				linePrefix, targetTable, hop.Alias,
				hop.SourceAlias, hop.SourceField,
				hop.Alias, hop.TargetField,
			))
		}
	}
}

func formulaJoinTargetTable(targetTable, logicalObject string, usesCurrentProjection bool, currentProjectionTarget func(string) string) string {
	if usesCurrentProjection && currentProjectionTarget != nil && strings.TrimSpace(logicalObject) != "" {
		return currentProjectionTarget(logicalObject)
	}
	return targetTable
}

// buildMultiLayerDDL: nested subqueries for multi-layer formula dependencies.
func (b *DSLViewBuilder) buildMultiLayerDDL(
	viewName string, cfg ViewBuildConfig,
	dag *FormulaDAG, layers [][]FormulaDefinition, ctx *CompileContext,
) (string, error) {
	// Layer 0 (innermost): base columns + JOINs + L0 formulas
	// Layer N (outer): L(N-1).* + LN formulas

	// Pre-scan: collect all CrossRef columns used in L1+ formulas.
	// These need to be explicitly selected in L0 so outer layers can reference them.
	type xrefCol struct {
		Object string
		Field  string
	}
	xrefSeen := make(map[string]bool)
	var xrefCols []xrefCol
	// Also collect DeepCrossRef pass-through info for L1+
	type deepXrefCol struct {
		Parts      []string // e.g. ["Owner", "Profile", "Name"]
		FinalAlias string   // e.g. "dj_prof"
		FinalField string   // e.g. "Name"
	}
	deepXrefSeen := make(map[string]bool)
	var deepXrefCols []deepXrefCol
	type aggregatePassThroughCol struct {
		Spec  AggregateJoinSpec
		Alias string
	}
	aggPassThroughSeen := make(map[string]bool)
	var aggPassThroughCols []aggregatePassThroughCol
	currentProjectionCrossRefObjects := currentProjectionCrossRefObjectKeys(cfg.ObjectName, cfg.Joins)
	currentProjectionDeepRefs := currentProjectionDeepRefKeys(cfg.DeepJoins)

	for layerIdx := 1; layerIdx < len(layers); layerIdx++ {
		for _, def := range layers[layerIdx] {
			ast := dag.AST(def.Name)
			if ast == nil {
				continue
			}
			for _, cr := range extractCrossRefs(ast) {
				if currentProjectionCrossRefObjects[strings.ToLower(cr.Object)] {
					continue
				}
				key := cr.Object + "." + cr.Field
				if !xrefSeen[key] {
					xrefSeen[key] = true
					xrefCols = append(xrefCols, xrefCol{Object: cr.Object, Field: cr.Field})
				}
			}
			// Collect DeepCrossRef used in L1+ formulas
			for _, dcr := range extractDeepCrossRefs(ast) {
				key := strings.Join(dcr.Parts, ".")
				lookupKey := strings.ToLower(key)
				if currentProjectionDeepRefs[lookupKey] {
					continue
				}
				if !deepXrefSeen[lookupKey] {
					deepXrefSeen[lookupKey] = true
					// Find matching chain in cfg.DeepJoins
					for _, dj := range cfg.DeepJoins {
						if strings.EqualFold(strings.Join(dj.Parts, "."), key) {
							deepXrefCols = append(deepXrefCols, deepXrefCol{
								Parts: dj.Parts, FinalAlias: dj.FinalAlias, FinalField: dj.FinalField,
							})
							break
						}
					}
				}
			}
			for _, agg := range extractAggregates(ast) {
				join, ok := findAggregateJoinForMarker(aggregateMarkerFromExpr(agg), cfg.AggJoins)
				if !ok {
					return "", fmt.Errorf("unresolved aggregate marker in %s", qualifyFormulaName(cfg.ObjectName, def.Name))
				}
				if !aggPassThroughSeen[join.Alias] {
					aggPassThroughSeen[join.Alias] = true
					aggPassThroughCols = append(aggPassThroughCols, aggregatePassThroughCol{
						Spec:  join,
						Alias: aggregatePassThroughAlias(join),
					})
				}
			}
		}
	}

	// Build a map of xref alias names: Object.Field → _xref_Object_Field
	xrefAliasMap := make(map[string]string) // "Object.Field" → "_xref_Object_Field"
	for _, xc := range xrefCols {
		aliasName := fmt.Sprintf("_xref_%s_%s", xc.Object, xc.Field)
		xrefAliasMap[xc.Object+"."+xc.Field] = aliasName
	}
	// Also add deep cross-ref pass-through aliases
	for _, dxc := range deepXrefCols {
		key := strings.Join(dxc.Parts, ".")
		aliasName := fmt.Sprintf("_deep_%s", strings.Join(dxc.Parts, "_"))
		xrefAliasMap[key] = aliasName
	}
	fieldTypes := withPassThroughFieldTypes(ctx.FieldTypes, xrefAliasMap)

	// Build innermost query (L0)
	l0Ctx := &CompileContext{
		TableAlias:               cfg.TableAlias,
		JoinAliases:              ctx.JoinAliases,
		CrossRefFieldAliases:     ctx.CrossRefFieldAliases,
		CrossRefFieldExpressions: ctx.CrossRefFieldExpressions,
		QualifyFields:            true,
		DeepJoinMap:              ctx.DeepJoinMap,
		FieldTypes:               fieldTypes,
		FunctionExecutables:      ctx.FunctionExecutables,
		BaseFieldPhysicalMap:     cfg.BaseColumnAliases,
	}

	innerSQL := newBoundedSQLBuilder(viewName, b.viewDDLByteLimit())
	innerSQL.WriteString("SELECT\n")

	// Base columns — emit t.`PhysicalName` AS `APIName` when PhysicalName differs
	// so that outer layers can reference the logical API name exposed by this projection.
	for i, col := range cfg.BaseColumns {
		if i > 0 {
			innerSQL.WriteString(",\n")
		}
		if physCol, ok := cfg.BaseColumnAliases[col]; ok && physCol != "" && physCol != col {
			innerSQL.WriteString(fmt.Sprintf("    %s.`%s` AS `%s`", cfg.TableAlias, physCol, col))
		} else {
			innerSQL.WriteString(fmt.Sprintf("    %s.`%s`", cfg.TableAlias, col))
		}
	}
	if err := innerSQL.Err(); err != nil {
		return "", err
	}

	// L0 formula columns
	for _, def := range layers[0] {
		ast := dag.AST(def.Name)
		if ast == nil {
			continue
		}

		sql, err := Compile(ast, compileContextForFormula(l0Ctx, def))
		if err != nil {
			return "", fmt.Errorf("compiling L0 %s: %w", qualifyFormulaName(cfg.ObjectName, def.Name), err)
		}
		sql, err = b.resolveAggregateMarkers(sql, cfg, aggregateJoinReference)
		if err != nil {
			return "", fmt.Errorf("resolving aggregates for L0 %s: %w", qualifyFormulaName(cfg.ObjectName, def.Name), err)
		}
		sql = fromFormulaDomainSQL(sql, def.ReturnType)
		innerSQL.WriteString(",\n")
		innerSQL.WriteString(fmt.Sprintf("    %s AS `%s`", sql, def.Name))
		if err := innerSQL.Err(); err != nil {
			return "", err
		}
	}

	for _, col := range aggPassThroughCols {
		innerSQL.WriteString(",\n")
		innerSQL.WriteString(fmt.Sprintf("    %s AS `%s`", aggregateJoinReference(col.Spec), col.Alias))
		if err := innerSQL.Err(); err != nil {
			return "", err
		}
	}

	// Cross-ref pass-through columns: select joined fields explicitly in L0
	// so that outer layers can reference them without needing JOINs.
	for _, xc := range xrefCols {
		// Resolve the JOIN alias for this object
		alias := ""
		if a, ok := ctx.JoinAliases[xc.Object]; ok {
			alias = a
		} else {
			// Case-insensitive lookup
			for k, v := range ctx.JoinAliases {
				if strings.EqualFold(k, xc.Object) {
					alias = v
					break
				}
			}
		}
		if alias == "" {
			// No JOIN available for this object — skip (compile will fail gracefully)
			continue
		}
		colAlias := xrefAliasMap[xc.Object+"."+xc.Field]
		innerSQL.WriteString(",\n")
		if expression, found := l0Ctx.resolveCrossRefFieldExpression(xc.Object, xc.Field); found {
			innerSQL.WriteString(fmt.Sprintf("    %s AS `%s`", expression, colAlias))
		} else {
			physicalField := l0Ctx.resolveCrossRefField(xc.Object, xc.Field)
			innerSQL.WriteString(fmt.Sprintf("    %s.`%s` AS `%s`", alias, physicalField, colAlias))
		}
		if err := innerSQL.Err(); err != nil {
			return "", err
		}
	}

	// Deep cross-ref pass-through columns for L1+
	for _, dxc := range deepXrefCols {
		key := strings.Join(dxc.Parts, ".")
		colAlias := xrefAliasMap[key]
		innerSQL.WriteString(",\n")
		innerSQL.WriteString(fmt.Sprintf("    %s.`%s` AS `%s`", dxc.FinalAlias, dxc.FinalField, colAlias))
		if err := innerSQL.Err(); err != nil {
			return "", err
		}
	}

	// FROM + JOINs
	innerSQL.WriteString(fmt.Sprintf("\n  FROM `%s` %s", cfg.PhysicalTable, cfg.TableAlias))

	appendFormulaAggregateJoins(innerSQL, cfg.AggJoins, cfg.TableAlias, "  ", "    ")
	appendFormulaRelationshipJoins(innerSQL, cfg.Joins, cfg.TableAlias, "  ", nil)
	appendFormulaDeepJoins(innerSQL, cfg.DeepJoins, "  ", nil)
	if err := innerSQL.Err(); err != nil {
		return "", err
	}

	// Wrap layers: each outer layer references the inner as a subquery
	currentSQL := innerSQL.String()
	for layerIdx := 1; layerIdx < len(layers); layerIdx++ {
		layerAlias := fmt.Sprintf("L%d", layerIdx-1)
		nextAlias := fmt.Sprintf("L%d", layerIdx)

		layerSelfJoins := requiredCurrentProjectionJoins(cfg.ObjectName, cfg.Joins, dag, layers[layerIdx])
		layerSelfDeepJoins := requiredCurrentProjectionDeepJoins(cfg.DeepJoins, dag, layers[layerIdx])
		preserveCrossRefs := currentProjectionCrossRefObjectKeys(cfg.ObjectName, layerSelfJoins)
		preserveDeepRefs := currentProjectionDeepRefKeys(layerSelfDeepJoins)
		outerJoinAliases := make(map[string]string)
		outerAmbiguousTargets := crossRefAmbiguousTargetKeys(layerSelfJoins)
		for _, join := range layerSelfJoins {
			logicalTarget := join.TargetObject
			if strings.TrimSpace(logicalTarget) == "" {
				logicalTarget = join.TargetTable
			}
			for _, key := range crossRefObjectKeysForPlan(outerAmbiguousTargets, logicalTarget, join) {
				addJoinAliasKey(outerJoinAliases, key, join.Alias)
			}
			addJoinAliasKey(outerJoinAliases, join.Alias, join.Alias)
		}
		outerDeepJoinMap := buildDeepJoinMap(layerSelfDeepJoins)

		// Outer layer context: bare field refs reference the inner layer alias.
		// FieldTypes is threaded through so date arithmetic in outer layers
		// (formulas that depend on other formulas) still compiles correctly —
		// a formula that returns a date in L0 is still date-typed in L1+.
		outerCtx := &CompileContext{
			TableAlias:               layerAlias,
			JoinAliases:              outerJoinAliases,
			QualifyFields:            true,
			DeepJoinMap:              outerDeepJoinMap,
			CrossRefFieldAliases:     ctx.CrossRefFieldAliases,
			CrossRefFieldExpressions: ctx.CrossRefFieldExpressions,
			FieldTypes:               fieldTypes,
			FunctionExecutables:      ctx.FunctionExecutables,
		}

		outerSQL := newBoundedSQLBuilder(viewName, b.viewDDLByteLimit())
		outerSQL.WriteString("SELECT\n")
		outerSQL.WriteString(fmt.Sprintf("    %s.*", layerAlias))
		if err := outerSQL.Err(); err != nil {
			return "", err
		}

		for _, def := range layers[layerIdx] {
			ast := dag.AST(def.Name)
			if ast == nil {
				continue
			}

			// Rewrite only non-self-projection refs to L0 pass-through fields.
			rewrittenAST := rewriteCrossRefsToFieldRefs(ast, xrefAliasMap, preserveCrossRefs, preserveDeepRefs)

			sql, err := Compile(rewrittenAST, compileContextForFormula(outerCtx, def))
			if err != nil {
				return "", fmt.Errorf("compiling L%d %s: %w", layerIdx, qualifyFormulaName(cfg.ObjectName, def.Name), err)
			}
			sql, err = b.resolveAggregateMarkers(sql, cfg, func(aj AggregateJoinSpec) string {
				return fmt.Sprintf("%s.`%s`", layerAlias, aggregatePassThroughAlias(aj))
			})
			if err != nil {
				return "", fmt.Errorf("resolving aggregates for L%d %s: %w", layerIdx, qualifyFormulaName(cfg.ObjectName, def.Name), err)
			}
			sql = fromFormulaDomainSQL(sql, def.ReturnType)
			outerSQL.WriteString(",\n")
			outerSQL.WriteString(fmt.Sprintf("    %s AS `%s`", sql, def.Name))
			if err := outerSQL.Err(); err != nil {
				return "", err
			}
		}

		outerSQL.WriteString("\n  FROM (\n    ")
		outerSQL.WriteIndented(currentSQL, "    ")
		outerSQL.WriteString(fmt.Sprintf("\n  ) %s", layerAlias))
		if err := outerSQL.Err(); err != nil {
			return "", err
		}
		for _, join := range layerSelfJoins {
			outerSQL.WriteString("\n  LEFT JOIN (\n    ")
			outerSQL.WriteIndented(currentSQL, "    ")
			outerSQL.WriteString(fmt.Sprintf("\n  ) %s ON %s.`%s` = %s.`%s`",
				join.Alias,
				layerAlias, join.SourceField,
				join.Alias, join.TargetField,
			))
			if err := outerSQL.Err(); err != nil {
				return "", err
			}
		}
		for _, dj := range layerSelfDeepJoins {
			for _, hop := range dj.Hops {
				sourceAlias := hop.SourceAlias
				if sourceAlias == "t" {
					sourceAlias = layerAlias
				}
				outerSQL.WriteString("\n  LEFT JOIN ")
				if hop.UsesCurrentObjectProjection {
					outerSQL.WriteString("(\n    ")
					outerSQL.WriteIndented(currentSQL, "    ")
					outerSQL.WriteString("\n  )")
				} else {
					outerSQL.WriteString(fmt.Sprintf("`%s`", hop.TargetTable))
				}
				outerSQL.WriteString(fmt.Sprintf(" %s ON %s.`%s` = %s.`%s`",
					hop.Alias,
					sourceAlias, hop.SourceField,
					hop.Alias, hop.TargetField,
				))
				if err := outerSQL.Err(); err != nil {
					return "", err
				}
			}
		}

		currentSQL = outerSQL.String()
		_ = nextAlias // used in next iteration
	}

	// Final wrapper. Stream the nested query through the bounded builder so a
	// near-limit inner statement is rejected before allocating another copy.
	outerAlias := fmt.Sprintf("L%d", len(layers)-1)
	ddl := newBoundedSQLBuilder(viewName, b.viewDDLByteLimit())
	ddl.WriteString(fmt.Sprintf("%s `%s` AS\nSELECT %s.* FROM (\n  ", CreateOrReplaceViewStmt, viewName, outerAlias))
	ddl.WriteIndented(currentSQL, "  ")
	ddl.WriteString(fmt.Sprintf("\n) %s", outerAlias))
	if err := ddl.Err(); err != nil {
		return "", err
	}

	return ddl.String(), nil
}

func withFormulaReturnTypes(fieldTypes map[string]string, formulas []FormulaDefinition) map[string]string {
	out := make(map[string]string, len(fieldTypes)+len(formulas))
	for k, v := range fieldTypes {
		out[k] = v
	}
	for _, def := range formulas {
		if def.Name == "" || strings.TrimSpace(def.ReturnType) == "" {
			continue
		}
		out[def.Name] = strings.ToLower(strings.TrimSpace(def.ReturnType))
	}
	return out
}

func withPassThroughFieldTypes(fieldTypes map[string]string, aliasMap map[string]string) map[string]string {
	if len(aliasMap) == 0 {
		return fieldTypes
	}
	out := make(map[string]string, len(fieldTypes)+len(aliasMap))
	for k, v := range fieldTypes {
		out[k] = v
	}
	for source, alias := range aliasMap {
		if alias == "" {
			continue
		}
		if typ, ok := lookupCaseInsensitive(fieldTypes, source); ok {
			out[alias] = typ
		}
	}
	return out
}

func lookupCaseInsensitive(values map[string]string, key string) (string, bool) {
	if len(values) == 0 {
		return "", false
	}
	if v, ok := values[key]; ok {
		return v, true
	}
	for k, v := range values {
		if strings.EqualFold(k, key) {
			return v, true
		}
	}
	return "", false
}

func fromFormulaDomainSQL(sqlExpr string, returnType string) string {
	if constants.NormalizeFieldType(returnType) == constants.FieldTypePercent {
		return fmt.Sprintf("(%s) * 100", sqlExpr)
	}
	return sqlExpr
}

// rewriteCrossRefsToFieldRefs replaces only non-preserved CrossRef / DeepCrossRef
// nodes with FieldRef nodes that reference L0 pass-through columns
// (e.g. _xref_Object_Field). Preserved refs continue to compile against outer
// self-projection joins.
func rewriteCrossRefsToFieldRefs(
	node Node,
	xrefAliasMap map[string]string,
	preserveCrossRefObjects map[string]bool,
	preserveDeepRefs map[string]bool,
) Node {
	if node == nil {
		return nil
	}
	switch n := node.(type) {
	case *CrossRef:
		if preserveCrossRefObjects[strings.ToLower(n.Object)] {
			return n
		}
		key := n.Object + "." + n.Field
		if alias, ok := xrefAliasMap[key]; ok {
			return &FieldRef{Name: alias}
		}
		return n // No mapping — leave as-is (will compile with JoinAliases or fail)

	case *DeepCrossRef:
		key := strings.Join(n.Parts, ".")
		if preserveDeepRefs[strings.ToLower(key)] {
			return n
		}
		if alias, ok := lookupCaseInsensitive(xrefAliasMap, key); ok {
			return &FieldRef{Name: alias}
		}
		return n // No mapping — leave as-is

	case *BinaryOp:
		return &BinaryOp{
			Op:    n.Op,
			Left:  rewriteCrossRefsToFieldRefs(n.Left, xrefAliasMap, preserveCrossRefObjects, preserveDeepRefs),
			Right: rewriteCrossRefsToFieldRefs(n.Right, xrefAliasMap, preserveCrossRefObjects, preserveDeepRefs),
		}
	case *UnaryOp:
		return &UnaryOp{
			Op:      n.Op,
			Operand: rewriteCrossRefsToFieldRefs(n.Operand, xrefAliasMap, preserveCrossRefObjects, preserveDeepRefs),
		}
	case *LogicalOp:
		newOps := make([]Node, len(n.Operands))
		for i, o := range n.Operands {
			newOps[i] = rewriteCrossRefsToFieldRefs(o, xrefAliasMap, preserveCrossRefObjects, preserveDeepRefs)
		}
		return &LogicalOp{Op: n.Op, Operands: newOps}
	case *FuncCall:
		newArgs := make([]Node, len(n.Args))
		for i, a := range n.Args {
			newArgs[i] = rewriteCrossRefsToFieldRefs(a, xrefAliasMap, preserveCrossRefObjects, preserveDeepRefs)
		}
		return &FuncCall{Name: n.Name, Args: newArgs}
	case *IfExpr:
		return &IfExpr{
			Condition: rewriteCrossRefsToFieldRefs(n.Condition, xrefAliasMap, preserveCrossRefObjects, preserveDeepRefs),
			Then:      rewriteCrossRefsToFieldRefs(n.Then, xrefAliasMap, preserveCrossRefObjects, preserveDeepRefs),
			Else:      rewriteCrossRefsToFieldRefs(n.Else, xrefAliasMap, preserveCrossRefObjects, preserveDeepRefs),
		}
	case *CaseExpr:
		newBranches := make([]CaseBranch, len(n.Branches))
		for i, b := range n.Branches {
			newBranches[i] = CaseBranch{
				Value:  rewriteCrossRefsToFieldRefs(b.Value, xrefAliasMap, preserveCrossRefObjects, preserveDeepRefs),
				Result: rewriteCrossRefsToFieldRefs(b.Result, xrefAliasMap, preserveCrossRefObjects, preserveDeepRefs),
			}
		}
		var newDefault Node
		if n.Default != nil {
			newDefault = rewriteCrossRefsToFieldRefs(n.Default, xrefAliasMap, preserveCrossRefObjects, preserveDeepRefs)
		}
		return &CaseExpr{
			Expr:     rewriteCrossRefsToFieldRefs(n.Expr, xrefAliasMap, preserveCrossRefObjects, preserveDeepRefs),
			Branches: newBranches,
			Default:  newDefault,
		}
	default:
		// Leaf nodes (NumberLit, StringLit, BoolLit, NullLit, FieldRef, DeepCrossRef, AggregateExpr)
		return n
	}
}

func currentProjectionCrossRefObjectKeys(objectName string, joins []JoinSpec) map[string]bool {
	out := make(map[string]bool)
	ambiguousTargets := crossRefAmbiguousTargetKeys(joins)
	for _, join := range joins {
		if !join.UsesCurrentObjectProjection {
			continue
		}
		for _, key := range crossRefObjectKeysForPlan(ambiguousTargets, objectName, join) {
			out[strings.ToLower(key)] = true
		}
	}
	return out
}

func currentProjectionDeepRefKeys(chains []DeepJoinChain) map[string]bool {
	out := make(map[string]bool)
	for _, chain := range chains {
		if len(chain.Hops) == 0 {
			continue
		}
		if !chain.Hops[len(chain.Hops)-1].UsesCurrentObjectProjection {
			continue
		}
		out[strings.ToLower(strings.Join(chain.Parts, "."))] = true
	}
	return out
}

func requiredCurrentProjectionJoins(objectName string, joins []JoinSpec, dag *FormulaDAG, layer []FormulaDefinition) []JoinSpec {
	if len(layer) == 0 || len(joins) == 0 {
		return nil
	}
	var out []JoinSpec
	seen := make(map[string]bool)
	ambiguousTargets := crossRefAmbiguousTargetKeys(joins)
	for _, def := range layer {
		ast := dag.AST(def.Name)
		if ast == nil {
			continue
		}
		for _, ref := range extractCrossRefs(ast) {
			for _, join := range joins {
				if !join.UsesCurrentObjectProjection || seen[join.Alias] {
					continue
				}
				if crossRefKeyMatchesJoin(ref.Object, objectName, join, ambiguousTargets) {
					seen[join.Alias] = true
					out = append(out, join)
				}
			}
		}
	}
	return out
}

func requiredCurrentProjectionDeepJoins(chains []DeepJoinChain, dag *FormulaDAG, layer []FormulaDefinition) []DeepJoinChain {
	if len(layer) == 0 || len(chains) == 0 {
		return nil
	}
	var out []DeepJoinChain
	seen := make(map[string]bool)
	for _, def := range layer {
		ast := dag.AST(def.Name)
		if ast == nil {
			continue
		}
		for _, dcr := range extractDeepCrossRefs(ast) {
			key := strings.ToLower(strings.Join(dcr.Parts, "."))
			if seen[key] {
				continue
			}
			for _, chain := range chains {
				if len(chain.Hops) == 0 || !chain.Hops[len(chain.Hops)-1].UsesCurrentObjectProjection {
					continue
				}
				if strings.EqualFold(strings.Join(chain.Parts, "."), key) {
					seen[key] = true
					out = append(out, chain)
					break
				}
			}
		}
	}
	return out
}

// buildDeepJoinMap converts []DeepJoinChain into a lookup map for the compiler.
// Key: "Owner.Profile.Name" → Value: "dj_prof.`Name`"
func buildDeepJoinMap(chains []DeepJoinChain) map[string]string {
	if len(chains) == 0 {
		return nil
	}
	m := make(map[string]string, len(chains))
	for _, chain := range chains {
		key := strings.Join(chain.Parts, ".")
		m[key] = fmt.Sprintf("%s.`%s`", chain.FinalAlias, chain.FinalField)
	}
	return m
}

// resolveAggregateMarkers replaces exact __AGG__...__ markers with references
// to metadata-resolved aggregate JOIN aliases. Markers are parsed as structured
// tuples so one aggregate field cannot accidentally consume another field on
// the same child object.
func (b *DSLViewBuilder) resolveAggregateMarkers(
	sql string,
	cfg ViewBuildConfig,
	replacementFor func(AggregateJoinSpec) string,
) (string, error) {
	if !strings.Contains(sql, AggMarkerPrefix) {
		return sql, nil
	}
	matches := reAggregateMarker.FindAllStringSubmatchIndex(sql, -1)
	if len(matches) == 0 {
		return "", fmt.Errorf("malformed aggregate marker")
	}

	var out strings.Builder
	last := 0
	for _, match := range matches {
		out.WriteString(sql[last:match[0]])
		marker := aggregateMarker{
			Func:        sql[match[2]:match[3]],
			ChildObject: sql[match[4]:match[5]],
			Field:       sql[match[6]:match[7]],
			ForeignKey:  sql[match[8]:match[9]],
		}
		join, ok := findAggregateJoinForMarker(marker, cfg.AggJoins)
		if !ok {
			return "", fmt.Errorf("unresolved aggregate marker %s(%s.%s, %s)", marker.Func, marker.ChildObject, marker.Field, marker.ForeignKey)
		}
		replacement := replacementFor(join)
		// Blank-as-zero (#4152) for inline aggregates: a childless parent's
		// LEFT-JOIN subquery yields NULL, which would NULL-poison the whole
		// formula while an equivalent stored rollup read (coalesced at the
		// FieldRef seam) reads 0. SUM/COUNT/AVG are numeric by construction,
		// so they coalesce here; MIN/MAX are left bare because the child
		// field's type is not known at marker-resolution time and a date
		// MIN/MAX must not be coerced to 0.
		switch strings.ToUpper(strings.TrimSpace(join.AggFunc)) {
		case "SUM", "COUNT", "AVG":
			replacement = fmt.Sprintf("COALESCE(%s, 0)", replacement)
		}
		out.WriteString(replacement)
		last = match[1]
	}
	out.WriteString(sql[last:])

	result := out.String()
	if strings.Contains(result, AggMarkerPrefix) {
		return "", fmt.Errorf("unresolved aggregate marker")
	}
	return result, nil
}

func aggregateJoinReference(aj AggregateJoinSpec) string {
	return fmt.Sprintf("%s.`%s`", aj.Alias, aj.OutputAlias)
}

func aggregatePassThroughAlias(aj AggregateJoinSpec) string {
	return "_agg_" + sanitizeAliasBody(aj.Alias+"_"+aj.OutputAlias)
}

func aggregateMarkerFromExpr(agg AggregateExpr) aggregateMarker {
	return aggregateMarker{
		Func:        agg.Func,
		ChildObject: agg.ChildObject,
		Field:       normalizeAggregateField(agg.ChildField),
		ForeignKey:  normalizeAggregateForeignKey(agg.ForeignKey),
	}
}

func findAggregateJoinForMarker(marker aggregateMarker, joins []AggregateJoinSpec) (AggregateJoinSpec, bool) {
	for _, join := range joins {
		if aggregateMarkerMatchesJoin(marker, join) {
			return join, true
		}
	}
	return AggregateJoinSpec{}, false
}

func aggregateMarkerMatchesJoin(marker aggregateMarker, join AggregateJoinSpec) bool {
	if !strings.EqualFold(marker.Func, join.AggFunc) {
		return false
	}
	if !aggregateChildMatches(marker.ChildObject, join) {
		return false
	}
	if !strings.EqualFold(normalizeAggregateField(marker.Field), normalizeAggregateField(join.AggField)) {
		return false
	}
	markerFK := normalizeAggregateForeignKey(marker.ForeignKey)
	joinFK := normalizeAggregateForeignKey(join.ForeignKey)
	return markerFK == "_" || strings.EqualFold(markerFK, joinFK)
}

func aggregateChildMatches(child string, join AggregateJoinSpec) bool {
	for _, key := range aggregateMarkerChildKeys(join) {
		if strings.EqualFold(child, key) {
			return true
		}
	}
	return false
}

func normalizeAggregateField(field string) string {
	if strings.TrimSpace(field) == "" {
		return "*"
	}
	return strings.TrimSpace(field)
}

func normalizeAggregateForeignKey(fk string) string {
	if strings.TrimSpace(fk) == "" {
		return "_"
	}
	return strings.TrimSpace(fk)
}

func aggregateMarkerChildKeys(aj AggregateJoinSpec) []string {
	seen := make(map[string]bool)
	var keys []string
	for _, key := range []string{aj.ChildObject, aj.ChildTable, titleCase(aj.ChildTable)} {
		if key == "" || seen[strings.ToLower(key)] {
			continue
		}
		seen[strings.ToLower(key)] = true
		keys = append(keys, key)
	}
	return keys
}

// titleCase uppercases the first byte of s. Replaces deprecated strings.Title for single-word inputs.
func titleCase(s string) string {
	if s == "" {
		return s
	}
	if s[0] >= 'a' && s[0] <= 'z' {
		return string(s[0]-32) + s[1:]
	}
	return s
}

// formatAggField returns the aggregate field expression — `*` for COUNT(*), backtick-quoted otherwise.
func formatAggField(field string) string {
	if field == "*" {
		return "*"
	}
	return "`" + field + "`"
}

func indent(s string, prefix string) string {
	lines := strings.Split(s, "\n")
	for i, line := range lines {
		if line != "" {
			lines[i] = prefix + line
		}
	}
	return strings.Join(lines, "\n")
}