package analytics

import (
	"fmt"
	"regexp"
	"strings"
)

// reSafeIdentifier matches valid SQL identifiers: alphanumeric, underscores, starting with a letter.
var reSafeIdentifier = regexp.MustCompile(`^[a-zA-Z][a-zA-Z0-9_]*$`)

// 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 (e.g. "a", "own", "o")
	TargetTable string // Physical table name (e.g. "account")
	SourceField string // FK column on parent table (e.g. "AccountId")
	TargetField string // PK column on target table (e.g. "Id")
}

// AggregateJoinSpec describes a LEFT JOIN subquery for rollup aggregates.
type AggregateJoinSpec struct {
	Alias       string // Unique alias for the subquery (e.g. "rsq0")
	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")
}

// DeepJoinChain describes a multi-hop JOIN chain for deep cross-table refs like Owner.Profile.Name.
// It generates chained LEFT JOINs and maps the DeepCrossRef to the final alias.field.
type DeepJoinChain struct {
	// Parts matches the DeepCrossRef.Parts (e.g. ["Owner", "Profile", "Name"])
	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")
	TargetTable string // Physical table name
	SourceAlias string // Alias of the previous hop (or cfg.TableAlias for the first hop)
	SourceField string // FK column on the source table (e.g. "OwnerId", "ProfileId")
	TargetField string // PK column on the target table (e.g. "Id")
}

// ViewBuildConfig holds all input needed to generate a CREATE OR REPLACE VIEW DDL.
type ViewBuildConfig struct {
	ObjectName    string // e.g. "Quote"
	PhysicalTable string // e.g. "quote" (lowercase)
	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

	// 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
}

// DSLViewBuilder generates TiDB View DDL from formula definitions using the
// DSL parser/compiler pipeline with a clean AST-based approach.
type DSLViewBuilder struct{}

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

// 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)
	}

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

	layers := dag.Layers()

	// 2. Auto-detect JOINs from cross-table refs if not pre-specified
	joinAliases := make(map[string]string)
	for _, j := range cfg.Joins {
		joinAliases[strings.ToLower(j.TargetTable)] = j.Alias
		joinAliases[j.TargetTable] = j.Alias
		// Also derive the relationship name from the SourceField for lookup
		// so formulas that use the SF relationship-name syntax find the JOIN:
		//   - Standard FK: OwnerId    → "Owner"      (e.g. Owner.Name)
		//   - Custom FK:   Account__c → "Account__r" (e.g. Account__r.Name)
		if relName := deriveRelationshipName(j.SourceField); relName != "" && relName != j.SourceField {
			joinAliases[relName] = j.Alias
			joinAliases[strings.ToLower(relName)] = j.Alias
		}
		// Also map the alias itself (for self-reference)
		joinAliases[j.Alias] = j.Alias
	}

	// Collect all used aliases for uniqueness checking
	usedAliases := make(map[string]bool)
	for _, j := range cfg.Joins {
		usedAliases[j.Alias] = true
	}

	// Add auto-detected cross-table refs (skip if alias already exists for this object)
	// Skip custom relationship names ending in __r — they need special resolution
	// that requires DB access, so we fall back to the physical column instead.
	for _, cr := range dag.CrossTableRefs() {
		lowerObj := strings.ToLower(cr.Object)
		if strings.HasSuffix(lowerObj, "__r") {
			// Custom relationship — can't auto-generate JOIN, skip
			continue
		}
		if _, ok := joinAliases[lowerObj]; !ok {
			if _, ok2 := joinAliases[cr.Object]; !ok2 {
				alias := generateShortAlias(cr.Object)
				// Ensure unique alias
				if usedAliases[alias] {
					alias = alias + "_" + lowerObj[:min(3, len(lowerObj))]
				}
				for usedAliases[alias] {
					alias = alias + "x"
				}
				usedAliases[alias] = true
				joinAliases[lowerObj] = alias
				joinAliases[cr.Object] = alias
				cfg.Joins = append(cfg.Joins, JoinSpec{
					Alias:       alias,
					TargetTable: lowerObj,
					SourceField: cr.Object + "Id",
					TargetField: "Id",
				})
			}
		}
	}

	// 3. Build compile context (with DeepJoinMap for multi-hop refs)
	deepJoinMap := buildDeepJoinMap(cfg.DeepJoins)
	ctx := &CompileContext{
		TableAlias:    cfg.TableAlias,
		JoinAliases:   joinAliases,
		DeepJoinMap:   deepJoinMap,
		QualifyFields: true,
		FieldTypes:    cfg.FieldTypes,
	}

	// 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)
}

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

	// Base columns
	for i, col := range cfg.BaseColumns {
		if i > 0 || len(cfg.BaseColumns) > 0 {
			if i > 0 {
				sb.WriteString(",\n")
			}
		}
		sb.WriteString(fmt.Sprintf("  %s.`%s`", cfg.TableAlias, col))
	}

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

		// DeepCrossRef: if resolved via DeepJoinMap, compile normally; otherwise
		// fall back to the physical column if it exists, or skip entirely.
		if containsDeepCrossRef(ast) && !allDeepRefsResolved(ast, ctx.DeepJoinMap) {
			if baseColumnExists(def.Name, cfg.BaseColumns) {
				sb.WriteString(",\n")
				sb.WriteString(fmt.Sprintf("  %s.`%s`", cfg.TableAlias, def.Name))
			}
			// else: no physical column and deep refs unresolved — omit from view
			continue
		}

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

		// Handle aggregate markers
		sql = b.resolveAggregateMarkers(sql, cfg)

		sb.WriteString(",\n")
		sb.WriteString(fmt.Sprintf("  %s AS `%s`", sql, def.Name))
	}

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

	// Aggregate JOINs
	for _, aj := range cfg.AggJoins {
		sb.WriteString(fmt.Sprintf("\nLEFT JOIN (\n  SELECT `%s`, %s(%s) AS `%s`\n  FROM `%s`\n  GROUP BY `%s`\n) %s ON %s.`%s` = %s.`%s`",
			aj.ForeignKey, aj.AggFunc,
			formatAggField(aj.AggField),
			aj.OutputAlias,
			aj.ChildTable, aj.ForeignKey,
			aj.Alias,
			aj.Alias, aj.ForeignKey,
			cfg.TableAlias, aj.ParentKey,
		))
	}

	// Cross-table JOINs
	for _, j := range cfg.Joins {
		sb.WriteString(fmt.Sprintf("\nLEFT JOIN `%s` %s ON %s.`%s` = %s.`%s`",
			j.TargetTable, j.Alias,
			cfg.TableAlias, j.SourceField,
			j.Alias, j.TargetField,
		))
	}

	// Deep JOINs (multi-hop chains like Owner→User→Profile)
	for _, dj := range cfg.DeepJoins {
		for _, hop := range dj.Hops {
			sb.WriteString(fmt.Sprintf("\nLEFT JOIN `%s` %s ON %s.`%s` = %s.`%s`",
				hop.TargetTable, hop.Alias,
				hop.SourceAlias, hop.SourceField,
				hop.Alias, hop.TargetField,
			))
		}
	}

	return sb.String(), nil
}

// 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

	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) {
				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, ".")
				if !deepXrefSeen[key] {
					deepXrefSeen[key] = true
					// Find matching chain in cfg.DeepJoins
					for _, dj := range cfg.DeepJoins {
						if strings.Join(dj.Parts, ".") == key {
							deepXrefCols = append(deepXrefCols, deepXrefCol{
								Parts: dj.Parts, FinalAlias: dj.FinalAlias, FinalField: dj.FinalField,
							})
							break
						}
					}
				}
			}
		}
	}

	// 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
	}

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

	var innerSQL strings.Builder
	innerSQL.WriteString("SELECT\n")

	// Base columns
	for i, col := range cfg.BaseColumns {
		if i > 0 {
			innerSQL.WriteString(",\n")
		}
		innerSQL.WriteString(fmt.Sprintf("    %s.`%s`", cfg.TableAlias, col))
	}

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

		// DeepCrossRef: if resolved, compile; otherwise passthrough physical column if it exists
		if containsDeepCrossRef(ast) && !allDeepRefsResolved(ast, l0Ctx.DeepJoinMap) {
			if baseColumnExists(def.Name, cfg.BaseColumns) {
				innerSQL.WriteString(",\n")
				innerSQL.WriteString(fmt.Sprintf("    %s.`%s`", cfg.TableAlias, def.Name))
			}
			continue
		}

		sql, err := Compile(ast, l0Ctx)
		if err != nil {
			return "", fmt.Errorf("compiling L0 %s: %w", def.Name, err)
		}
		sql = b.resolveAggregateMarkers(sql, cfg)
		innerSQL.WriteString(",\n")
		innerSQL.WriteString(fmt.Sprintf("    %s AS `%s`", sql, def.Name))
	}

	// 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")
		innerSQL.WriteString(fmt.Sprintf("    %s.`%s` AS `%s`", alias, xc.Field, colAlias))
	}

	// 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))
	}

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

	for _, aj := range cfg.AggJoins {
		innerSQL.WriteString(fmt.Sprintf("\n  LEFT JOIN (\n    SELECT `%s`, %s(%s) AS `%s`\n    FROM `%s`\n    GROUP BY `%s`\n  ) %s ON %s.`%s` = %s.`%s`",
			aj.ForeignKey, aj.AggFunc,
			formatAggField(aj.AggField),
			aj.OutputAlias,
			aj.ChildTable, aj.ForeignKey,
			aj.Alias,
			aj.Alias, aj.ForeignKey,
			cfg.TableAlias, aj.ParentKey,
		))
	}

	for _, j := range cfg.Joins {
		innerSQL.WriteString(fmt.Sprintf("\n  LEFT JOIN `%s` %s ON %s.`%s` = %s.`%s`",
			j.TargetTable, j.Alias,
			cfg.TableAlias, j.SourceField,
			j.Alias, j.TargetField,
		))
	}

	// Deep JOINs (multi-hop chains like Owner→User→Profile)
	for _, dj := range cfg.DeepJoins {
		for _, hop := range dj.Hops {
			innerSQL.WriteString(fmt.Sprintf("\n  LEFT JOIN `%s` %s ON %s.`%s` = %s.`%s`",
				hop.TargetTable, hop.Alias,
				hop.SourceAlias, hop.SourceField,
				hop.Alias, hop.TargetField,
			))
		}
	}

	// 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)

		// Build JoinAliases for outer layers: map cross-ref objects to the layer alias
		// so that CrossRef compilation uses layerAlias.`_xref_Object_Field` columns.
		outerJoinAliases := make(map[string]string)
		// We don't set real JOIN aliases — instead we handle CrossRef via xrefAliasMap below.

		// 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,
			FieldTypes:    ctx.FieldTypes,
		}

		var outerSQL strings.Builder
		outerSQL.WriteString("SELECT\n")
		outerSQL.WriteString(fmt.Sprintf("    %s.*", layerAlias))

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

			// Before compiling, rewrite CrossRef nodes to FieldRef nodes
			// that reference the pass-through columns from L0.
			rewrittenAST := rewriteCrossRefsToFieldRefs(ast, xrefAliasMap)

			sql, err := Compile(rewrittenAST, outerCtx)
			if err != nil {
				return "", fmt.Errorf("compiling L%d %s: %w", layerIdx, def.Name, err)
			}
			outerSQL.WriteString(",\n")
			outerSQL.WriteString(fmt.Sprintf("    %s AS `%s`", sql, def.Name))
		}

		outerSQL.WriteString(fmt.Sprintf("\n  FROM (\n    %s\n  ) %s",
			indent(currentSQL, "    "), layerAlias))

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

	// Final wrapper
	outerAlias := fmt.Sprintf("L%d", len(layers)-1)
	ddl := fmt.Sprintf("%s `%s` AS\nSELECT %s.* FROM (\n  %s\n) %s",
		CreateOrReplaceViewStmt, viewName, outerAlias, indent(currentSQL, "  "), outerAlias)

	return ddl, nil
}

// rewriteCrossRefsToFieldRefs replaces CrossRef nodes with FieldRef nodes
// that reference the pass-through column aliases from L0 (e.g. _xref_Object_Field).
// This allows outer layers to reference cross-table data without needing JOINs.
func rewriteCrossRefsToFieldRefs(node Node, xrefAliasMap map[string]string) Node {
	if node == nil {
		return nil
	}
	switch n := node.(type) {
	case *CrossRef:
		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 alias, ok := 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),
			Right: rewriteCrossRefsToFieldRefs(n.Right, xrefAliasMap),
		}
	case *UnaryOp:
		return &UnaryOp{
			Op:      n.Op,
			Operand: rewriteCrossRefsToFieldRefs(n.Operand, xrefAliasMap),
		}
	case *LogicalOp:
		newOps := make([]Node, len(n.Operands))
		for i, o := range n.Operands {
			newOps[i] = rewriteCrossRefsToFieldRefs(o, xrefAliasMap)
		}
		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)
		}
		return &FuncCall{Name: n.Name, Args: newArgs}
	case *IfExpr:
		return &IfExpr{
			Condition: rewriteCrossRefsToFieldRefs(n.Condition, xrefAliasMap),
			Then:      rewriteCrossRefsToFieldRefs(n.Then, xrefAliasMap),
			Else:      rewriteCrossRefsToFieldRefs(n.Else, xrefAliasMap),
		}
	case *CaseExpr:
		newBranches := make([]CaseBranch, len(n.Branches))
		for i, b := range n.Branches {
			newBranches[i] = CaseBranch{
				Value:  rewriteCrossRefsToFieldRefs(b.Value, xrefAliasMap),
				Result: rewriteCrossRefsToFieldRefs(b.Result, xrefAliasMap),
			}
		}
		var newDefault Node
		if n.Default != nil {
			newDefault = rewriteCrossRefsToFieldRefs(n.Default, xrefAliasMap)
		}
		return &CaseExpr{
			Expr:     rewriteCrossRefsToFieldRefs(n.Expr, xrefAliasMap),
			Branches: newBranches,
			Default:  newDefault,
		}
	default:
		// Leaf nodes (NumberLit, StringLit, BoolLit, NullLit, FieldRef, DeepCrossRef, AggregateExpr)
		return n
	}
}

// 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
}

// allDeepRefsResolved returns true if every DeepCrossRef in the AST
// has a corresponding entry in the DeepJoinMap.
func allDeepRefsResolved(node Node, djMap map[string]string) bool {
	if djMap == nil {
		return false
	}
	allResolved := true
	walkAST(node, func(n Node) {
		if dcr, ok := n.(*DeepCrossRef); ok {
			key := strings.Join(dcr.Parts, ".")
			if _, found := djMap[key]; !found {
				allResolved = false
			}
		}
	})
	return allResolved
}

// resolveAggregateMarkers replaces __AGG__...__ markers with references
// to the aggregate JOIN aliases.
func (b *DSLViewBuilder) resolveAggregateMarkers(sql string, cfg ViewBuildConfig) string {
	// The AST emits markers like: ##AGG##SUM##Opportunity##Amount##_##
	// AggJoinSpec may have lowercase child table: "opportunity"
	// We need case-insensitive matching on the child object name.
	for _, aj := range cfg.AggJoins {
		// Try exact match first
		aggField := aj.AggField
		if aggField == "*" {
			aggField = "*"
		}
		fk := aj.ForeignKey
		if fk == "" {
			fk = "_"
		}

		// Build all possible markers (case variants for child table)
		replacement := fmt.Sprintf("%s.`%s`", aj.Alias, aj.OutputAlias)

		// Exact case
		marker := fmt.Sprintf(AggMarkerPrefix + "%s##%s##%s##%s##", aj.AggFunc, aj.ChildTable, aggField, fk)
		sql = strings.ReplaceAll(sql, marker, replacement)

		// Title case (e.g. "Opportunity" vs "opportunity")
		titled := titleCase(aj.ChildTable)
		marker2 := fmt.Sprintf(AggMarkerPrefix + "%s##%s##%s##%s##", aj.AggFunc, titled, aggField, fk)
		sql = strings.ReplaceAll(sql, marker2, replacement)

		// Also match when AST has a different field name (e.g. AST:"Id", spec:"*")
		// Scan for any remaining markers with this func+child combo
		scanPrefix := fmt.Sprintf(AggMarkerPrefix + "%s##%s##", aj.AggFunc, aj.ChildTable)
		scanPrefix2 := fmt.Sprintf(AggMarkerPrefix + "%s##%s##", aj.AggFunc, titled)

		for _, pfx := range []string{scanPrefix, scanPrefix2} {
			for idx := strings.Index(sql, pfx); idx >= 0; idx = strings.Index(sql, pfx) {
				rest := sql[idx+len(pfx):]
				// Find next ## (end of field)
				e1 := strings.Index(rest, "##")
				if e1 < 0 {
					break
				}
				rest2 := rest[e1+2:]
				e2 := strings.Index(rest2, "##")
				if e2 < 0 {
					break
				}
				fullMarker := sql[idx : idx+len(pfx)+e1+2+e2+2]
				sql = strings.ReplaceAll(sql, fullMarker, replacement)
			}
		}
	}
	return sql
}

// generateShortAlias creates a short SQL alias from an object name.
func generateShortAlias(name string) string {
	lower := strings.ToLower(name)
	switch lower {
	case "account":
		return "a"
	case "contact":
		return "ct"
	case "opportunity":
		return "o"
	case "quote":
		return "q"
	case "order":
		return "ord"
	case "lead":
		return "l"
	case "user", "owner":
		return "own"
	case "contract":
		return "ctr"
	case "task":
		return "tsk"
	default:
		// First 3 chars
		if len(lower) >= 3 {
			return lower[:3]
		}
		return lower
	}
}

// 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 baseColumnExists(name string, baseColumns []string) bool {
	for _, col := range baseColumns {
		if strings.EqualFold(col, name) {
			return true
		}
	}
	return false
}

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")
}