package analytics

import (
	"fmt"
	"strings"
	"testing"

	"github.com/nexuscrm/backend/pkg/utils"
)

// ============================================================================
// Tokenizer Tests
// ============================================================================

func TestTokenize_BasicExpressions(t *testing.T) {
	tests := []struct {
		input    string
		expected []TokenType
	}{
		{"42", []TokenType{TokNumber, TokEOF}},
		{"3.14", []TokenType{TokNumber, TokEOF}},
		{"'hello'", []TokenType{TokString, TokEOF}},
		{`"world"`, []TokenType{TokString, TokEOF}},
		{"TRUE", []TokenType{TokBoolean, TokEOF}},
		{"FALSE", []TokenType{TokBoolean, TokEOF}},
		{"Amount", []TokenType{TokIdentifier, TokEOF}},
		{"Amount__c", []TokenType{TokIdentifier, TokEOF}},
	}

	for _, tt := range tests {
		tokens, err := Tokenize(tt.input)
		if err != nil {
			t.Errorf("Tokenize(%q) error: %v", tt.input, err)
			continue
		}
		if len(tokens) != len(tt.expected) {
			t.Errorf("Tokenize(%q) got %d tokens, want %d: %v", tt.input, len(tokens), len(tt.expected), tokens)
			continue
		}
		for i, tok := range tokens {
			if tok.Type != tt.expected[i] {
				t.Errorf("Tokenize(%q) token[%d] = %v, want %v", tt.input, i, tok.Type, tt.expected[i])
			}
		}
	}
}

func TestTokenize_Operators(t *testing.T) {
	tokens, err := Tokenize("a + b * c - d / e")
	if err != nil {
		t.Fatal(err)
	}
	// a + b * c - d / e → Ident Plus Ident Star Ident Minus Ident Slash Ident EOF
	expected := []TokenType{TokIdentifier, TokPlus, TokIdentifier, TokStar, TokIdentifier,
		TokMinus, TokIdentifier, TokSlash, TokIdentifier, TokEOF}
	if len(tokens) != len(expected) {
		t.Fatalf("got %d tokens, want %d", len(tokens), len(expected))
	}
	for i, tok := range tokens {
		if tok.Type != expected[i] {
			t.Errorf("token[%d] = %v, want %v", i, tok.Type, expected[i])
		}
	}
}

func TestTokenize_Comparison(t *testing.T) {
	tokens, err := Tokenize("x <= 5 AND y <> 'test'")
	if err != nil {
		t.Fatal(err)
	}
	expected := []TokenType{TokIdentifier, TokLE, TokNumber, TokAnd, TokIdentifier,
		TokNE, TokString, TokEOF}
	if len(tokens) != len(expected) {
		t.Fatalf("got %d tokens, want %d: %v", len(tokens), len(expected), tokens)
	}
}

func TestTokenize_DottedRef(t *testing.T) {
	tokens, err := Tokenize("Account.Industry")
	if err != nil {
		t.Fatal(err)
	}
	expected := []TokenType{TokIdentifier, TokDot, TokIdentifier, TokEOF}
	if len(tokens) != len(expected) {
		t.Fatalf("got %d tokens, want %d", len(tokens), len(expected))
	}
	if tokens[0].Value != "Account" || tokens[2].Value != "Industry" {
		t.Errorf("expected Account.Industry, got %s.%s", tokens[0].Value, tokens[2].Value)
	}
}

func TestTokenize_ErrorUnterminatedString(t *testing.T) {
	_, err := Tokenize("'unterminated")
	if err == nil {
		t.Error("expected error for unterminated string")
	}
}

// ============================================================================
// Parser Tests
// ============================================================================

func TestParse_NumberLiteral(t *testing.T) {
	node, err := Parse("42")
	if err != nil {
		t.Fatal(err)
	}
	num, ok := node.(*NumberLit)
	if !ok {
		t.Fatalf("expected NumberLit, got %T", node)
	}
	if num.Value != "42" {
		t.Errorf("expected 42, got %s", num.Value)
	}
}

func TestParse_StringLiteral(t *testing.T) {
	node, err := Parse("'hello world'")
	if err != nil {
		t.Fatal(err)
	}
	str, ok := node.(*StringLit)
	if !ok {
		t.Fatalf("expected StringLit, got %T", node)
	}
	if str.Value != "hello world" {
		t.Errorf("expected 'hello world', got %s", str.Value)
	}
}

func TestParse_BinaryArithmetic(t *testing.T) {
	node, err := Parse("Amount * 0.1")
	if err != nil {
		t.Fatal(err)
	}
	bin, ok := node.(*BinaryOp)
	if !ok {
		t.Fatalf("expected BinaryOp, got %T", node)
	}
	if bin.Op != "*" {
		t.Errorf("expected *, got %s", bin.Op)
	}
}

func TestParse_OperatorPrecedence(t *testing.T) {
	// a + b * c should parse as a + (b * c)
	node, err := Parse("a + b * c")
	if err != nil {
		t.Fatal(err)
	}
	bin, ok := node.(*BinaryOp)
	if !ok {
		t.Fatalf("expected BinaryOp, got %T", node)
	}
	if bin.Op != "+" {
		t.Errorf("top-level should be +, got %s", bin.Op)
	}
	rightMul, ok := bin.Right.(*BinaryOp)
	if !ok {
		t.Fatalf("right should be BinaryOp, got %T", bin.Right)
	}
	if rightMul.Op != "*" {
		t.Errorf("right should be *, got %s", rightMul.Op)
	}
}

func TestParse_CrossTableRef(t *testing.T) {
	node, err := Parse("Account.Industry")
	if err != nil {
		t.Fatal(err)
	}
	cr, ok := node.(*CrossRef)
	if !ok {
		t.Fatalf("expected CrossRef, got %T", node)
	}
	if cr.Object != "Account" || cr.Field != "Industry" {
		t.Errorf("expected Account.Industry, got %s.%s", cr.Object, cr.Field)
	}
}

func TestParse_IFExpression(t *testing.T) {
	node, err := Parse("IF(Amount > 1000, 'High', 'Low')")
	if err != nil {
		t.Fatal(err)
	}
	ifExpr, ok := node.(*IfExpr)
	if !ok {
		t.Fatalf("expected IfExpr, got %T", node)
	}
	// Condition should be a comparison
	cond, ok := ifExpr.Condition.(*BinaryOp)
	if !ok {
		t.Fatalf("condition should be BinaryOp, got %T", ifExpr.Condition)
	}
	if cond.Op != ">" {
		t.Errorf("condition op should be >, got %s", cond.Op)
	}
}

func TestParse_NestedIF(t *testing.T) {
	node, err := Parse("IF(Amount > 1000, IF(Type = 'A', 10, 20), 0)")
	if err != nil {
		t.Fatal(err)
	}
	ifExpr, ok := node.(*IfExpr)
	if !ok {
		t.Fatalf("expected IfExpr, got %T", node)
	}
	_, ok = ifExpr.Then.(*IfExpr)
	if !ok {
		t.Fatalf("then branch should be IfExpr, got %T", ifExpr.Then)
	}
}

func TestParse_CASEExpression(t *testing.T) {
	node, err := Parse("CASE(Status, 'New', 1, 'Active', 2, 0)")
	if err != nil {
		t.Fatal(err)
	}
	caseExpr, ok := node.(*CaseExpr)
	if !ok {
		t.Fatalf("expected CaseExpr, got %T", node)
	}
	if len(caseExpr.Branches) != 2 {
		t.Errorf("expected 2 branches, got %d", len(caseExpr.Branches))
	}
	if caseExpr.Default == nil {
		t.Error("expected default clause")
	}
}

func TestParse_ANDORFunctionForm(t *testing.T) {
	node, err := Parse("AND(a > 1, b < 2, c = 3)")
	if err != nil {
		t.Fatal(err)
	}
	lo, ok := node.(*LogicalOp)
	if !ok {
		t.Fatalf("expected LogicalOp, got %T", node)
	}
	if lo.Op != "AND" {
		t.Errorf("expected AND, got %s", lo.Op)
	}
	if len(lo.Operands) != 3 {
		t.Errorf("expected 3 operands, got %d", len(lo.Operands))
	}
}

func TestParse_StringConcat(t *testing.T) {
	node, err := Parse("FirstName & ' ' & LastName")
	if err != nil {
		t.Fatal(err)
	}
	// Should be & of (& of FirstName, ' ') and LastName
	bin, ok := node.(*BinaryOp)
	if !ok {
		t.Fatalf("expected BinaryOp, got %T", node)
	}
	if bin.Op != "&" {
		t.Errorf("expected &, got %s", bin.Op)
	}
}

func TestParse_ISBLANK(t *testing.T) {
	node, err := Parse("ISBLANK(Email)")
	if err != nil {
		t.Fatal(err)
	}
	fc, ok := node.(*FuncCall)
	if !ok {
		t.Fatalf("expected FuncCall, got %T", node)
	}
	if fc.Name != "ISBLANK" {
		t.Errorf("expected ISBLANK, got %s", fc.Name)
	}
}

func TestParse_AggregateExpr(t *testing.T) {
	node, err := Parse("SUM(Opportunity.Amount)")
	if err != nil {
		t.Fatal(err)
	}
	agg, ok := node.(*AggregateExpr)
	if !ok {
		t.Fatalf("expected AggregateExpr, got %T", node)
	}
	if agg.Func != "SUM" {
		t.Errorf("expected SUM, got %s", agg.Func)
	}
	if agg.ChildObject != "Opportunity" || agg.ChildField != "Amount" {
		t.Errorf("expected Opportunity.Amount, got %s.%s", agg.ChildObject, agg.ChildField)
	}
}

func TestParse_ComplexFormula(t *testing.T) {
	// A realistic CRM formula
	formula := "IF(ISBLANK(Account.Industry), 'Unknown', Account.Industry) & ' - ' & CASE(Status, 'New', 'N', 'Active', 'A', 'X')"
	_, err := Parse(formula)
	if err != nil {
		t.Fatalf("failed to parse complex formula: %v", err)
	}
}

func TestParse_NULL(t *testing.T) {
	node, err := Parse("NULLVALUE(Amount, NULL)")
	if err != nil {
		t.Fatal(err)
	}
	fc, ok := node.(*FuncCall)
	if !ok {
		t.Fatalf("expected FuncCall, got %T", node)
	}
	if len(fc.Args) != 2 {
		t.Fatalf("expected 2 args, got %d", len(fc.Args))
	}
	_, ok = fc.Args[1].(*NullLit)
	if !ok {
		t.Fatalf("second arg should be NullLit, got %T", fc.Args[1])
	}
}

// ============================================================================
// Compiler Tests
// ============================================================================

func TestCompile_SimpleArithmetic(t *testing.T) {
	ctx := &CompileContext{
		TableAlias:    "t",
		JoinAliases:   map[string]string{},
		QualifyFields: true,
	}

	tests := []struct {
		input    string
		expected string
	}{
		{"Amount * 0.1", "(t.`Amount` * 0.1)"},
		{"Price + Tax", "(t.`Price` + t.`Tax`)"},
		{"42", "42"},
		{"'hello'", "'hello'"},
		{"TRUE", "1"},
		{"FALSE", "0"},
	}

	for _, tt := range tests {
		node, err := Parse(tt.input)
		if err != nil {
			t.Errorf("Parse(%q): %v", tt.input, err)
			continue
		}
		sql, err := Compile(node, ctx)
		if err != nil {
			t.Errorf("Compile(%q): %v", tt.input, err)
			continue
		}
		if sql != tt.expected {
			t.Errorf("Compile(%q) = %q, want %q", tt.input, sql, tt.expected)
		}
	}
}

func TestCompile_CrossTableRef(t *testing.T) {
	ctx := &CompileContext{
		TableAlias:    "t",
		JoinAliases:   map[string]string{"Account": "a", "Owner": "own"},
		QualifyFields: true,
	}

	node, err := Parse("Account.Industry")
	if err != nil {
		t.Fatal(err)
	}
	sql, err := Compile(node, ctx)
	if err != nil {
		t.Fatal(err)
	}
	if sql != "a.`Industry`" {
		t.Errorf("got %q, want %q", sql, "a.`Industry`")
	}
}

func TestCompile_IF(t *testing.T) {
	ctx := &CompileContext{
		TableAlias:    "t",
		JoinAliases:   map[string]string{},
		QualifyFields: true,
	}

	node, err := Parse("IF(Amount > 1000, 'High', 'Low')")
	if err != nil {
		t.Fatal(err)
	}
	sql, err := Compile(node, ctx)
	if err != nil {
		t.Fatal(err)
	}
	if !strings.Contains(sql, "CASE WHEN") {
		t.Errorf("expected CASE WHEN, got %s", sql)
	}
	if !strings.Contains(sql, "THEN 'High'") {
		t.Errorf("expected THEN 'High', got %s", sql)
	}
	if !strings.Contains(sql, "ELSE 'Low'") {
		t.Errorf("expected ELSE 'Low', got %s", sql)
	}
}

func TestCompile_CASE(t *testing.T) {
	ctx := &CompileContext{
		TableAlias:    "t",
		JoinAliases:   map[string]string{},
		QualifyFields: true,
	}

	node, err := Parse("CASE(Status, 'New', 1, 'Active', 2, 0)")
	if err != nil {
		t.Fatal(err)
	}
	sql, err := Compile(node, ctx)
	if err != nil {
		t.Fatal(err)
	}
	expected := "CASE t.`Status` WHEN 'New' THEN 1 WHEN 'Active' THEN 2 ELSE 0 END"
	if sql != expected {
		t.Errorf("got:\n  %s\nwant:\n  %s", sql, expected)
	}
}

func TestCompile_ISBLANK(t *testing.T) {
	ctx := &CompileContext{
		TableAlias:    "t",
		JoinAliases:   map[string]string{},
		QualifyFields: true,
	}

	node, err := Parse("ISBLANK(Email)")
	if err != nil {
		t.Fatal(err)
	}
	sql, err := Compile(node, ctx)
	if err != nil {
		t.Fatal(err)
	}
	if !strings.Contains(sql, "IS NULL") || !strings.Contains(sql, "= ''") {
		t.Errorf("expected IS NULL OR = '', got %s", sql)
	}
}

func TestCompile_StringConcat(t *testing.T) {
	ctx := &CompileContext{
		TableAlias:    "t",
		JoinAliases:   map[string]string{},
		QualifyFields: true,
	}

	node, err := Parse("FirstName & ' ' & LastName")
	if err != nil {
		t.Fatal(err)
	}
	sql, err := Compile(node, ctx)
	if err != nil {
		t.Fatal(err)
	}
	if !strings.Contains(sql, "CONCAT") {
		t.Errorf("expected CONCAT, got %s", sql)
	}
}

func TestCompile_SFunctions(t *testing.T) {
	ctx := &CompileContext{
		TableAlias:    "t",
		JoinAliases:   map[string]string{},
		QualifyFields: true,
	}

	tests := []struct {
		input    string
		contains string
	}{
		{"TODAY()", "CURDATE()"},
		{"NOW()", "NOW()"},
		{"LEN(Name)", "CHAR_LENGTH"},
		{"CONTAINS(Name, 'test')", "LIKE CONCAT('%'"},
		{"BEGINS(Name, 'A')", "LIKE CONCAT("},
		{"NULLVALUE(Amount, 0)", "IFNULL"},
		{"BLANKVALUE(Name, 'N/A')", "IFNULL(NULLIF"},
		{"SUBSTITUTE(Name, 'old', 'new')", "REPLACE"},
		{"MID(Name, 1, 3)", "SUBSTRING"},
		{"ADDMONTHS(CloseDate, 3)", "DATE_ADD"},
		{"YEAR(CloseDate)", "YEAR("},
		{"ROUND(Amount, 2)", "ROUND("},
		{"ABS(Amount)", "ABS("},
		{"UPPER(Name)", "UPPER("},
		{"LOWER(Name)", "LOWER("},
		{"TRIM(Name)", "TRIM("},
		{"ISPICKVAL(Status, 'Active')", "= 'Active'"},
		{"FIND('x', Name)", "LOCATE("},
	}

	for _, tt := range tests {
		node, err := Parse(tt.input)
		if err != nil {
			t.Errorf("Parse(%q): %v", tt.input, err)
			continue
		}
		sql, err := Compile(node, ctx)
		if err != nil {
			t.Errorf("Compile(%q): %v", tt.input, err)
			continue
		}
		if !strings.Contains(sql, tt.contains) {
			t.Errorf("Compile(%q) = %q, expected to contain %q", tt.input, sql, tt.contains)
		}
	}
}

func TestCompile_Exponent(t *testing.T) {
	ctx := &CompileContext{TableAlias: "t", JoinAliases: map[string]string{}, QualifyFields: true}
	node, err := Parse("x ^ 2")
	if err != nil {
		t.Fatal(err)
	}
	sql, err := Compile(node, ctx)
	if err != nil {
		t.Fatal(err)
	}
	if !strings.Contains(sql, "POWER") {
		t.Errorf("expected POWER, got %s", sql)
	}
}

func TestCompile_LogicalOperators(t *testing.T) {
	ctx := &CompileContext{TableAlias: "t", JoinAliases: map[string]string{}, QualifyFields: true}
	node, err := Parse("AND(a > 1, b < 2)")
	if err != nil {
		t.Fatal(err)
	}
	sql, err := Compile(node, ctx)
	if err != nil {
		t.Fatal(err)
	}
	if !strings.Contains(sql, " AND ") {
		t.Errorf("expected AND, got %s", sql)
	}
}

func TestCompile_UnknownCrossRef(t *testing.T) {
	ctx := &CompileContext{TableAlias: "t", JoinAliases: map[string]string{}, QualifyFields: true}
	node, err := Parse("Unknown.Field")
	if err != nil {
		t.Fatal(err)
	}
	// Unknown cross-refs now gracefully fall back to the physical column
	sql, err := Compile(node, ctx)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if sql != "t.`Field`" {
		t.Errorf("expected fallback to t.`Field`, got %s", sql)
	}
}

func TestCompile_DeepCrossRefWithJoinMap(t *testing.T) {
	ctx := &CompileContext{
		TableAlias:    "t",
		JoinAliases:   map[string]string{},
		QualifyFields: true,
		DeepJoinMap: map[string]string{
			"Owner.Profile.Name": "dj_prof.`Name`",
		},
	}
	node, err := Parse("Owner.Profile.Name")
	if err != nil {
		t.Fatal(err)
	}
	sql, err := Compile(node, ctx)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if sql != "dj_prof.`Name`" {
		t.Errorf("expected dj_prof.`Name`, got %s", sql)
	}
}

func TestCompile_DeepCrossRefFallback(t *testing.T) {
	ctx := &CompileContext{
		TableAlias:    "t",
		JoinAliases:   map[string]string{},
		QualifyFields: true,
		// No DeepJoinMap → should fall back to physical column
	}
	node, err := Parse("Owner.Profile.Name")
	if err != nil {
		t.Fatal(err)
	}
	sql, err := Compile(node, ctx)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	// Falls back to the last part as physical column
	if sql != "t.`Name`" {
		t.Errorf("expected t.`Name`, got %s", sql)
	}
}

// ============================================================================
// DAG Tests
// ============================================================================

func TestDAG_NoDependencies(t *testing.T) {
	defs := []FormulaDefinition{
		{Name: "Tax", Expression: "Amount * 0.1", ReturnType: "Number"},
		{Name: "Label", Expression: "FirstName & ' ' & LastName", ReturnType: "Text"},
	}

	dag, err := NewFormulaDAG(defs)
	if err != nil {
		t.Fatal(err)
	}

	layers := dag.Layers()
	if len(layers) != 1 {
		t.Errorf("expected 1 layer, got %d", len(layers))
	}
	if len(layers[0]) != 2 {
		t.Errorf("expected 2 formulas in layer 0, got %d", len(layers[0]))
	}
}

func TestDAG_LinearDependency(t *testing.T) {
	defs := []FormulaDefinition{
		{Name: "Tax", Expression: "Amount * 0.1", ReturnType: "Number"},
		{Name: "Total", Expression: "Amount + Tax", ReturnType: "Number"},
		{Name: "Discounted", Expression: "Total * 0.9", ReturnType: "Number"},
	}

	dag, err := NewFormulaDAG(defs)
	if err != nil {
		t.Fatal(err)
	}

	layers := dag.Layers()
	if len(layers) != 3 {
		t.Fatalf("expected 3 layers, got %d", len(layers))
	}

	// L0: Tax (depends only on base field Amount)
	if len(layers[0]) != 1 || layers[0][0].Name != "Tax" {
		t.Errorf("L0 should be [Tax], got %v", layerNames(layers[0]))
	}
	// L1: Total (depends on Tax)
	if len(layers[1]) != 1 || layers[1][0].Name != "Total" {
		t.Errorf("L1 should be [Total], got %v", layerNames(layers[1]))
	}
	// L2: Discounted (depends on Total)
	if len(layers[2]) != 1 || layers[2][0].Name != "Discounted" {
		t.Errorf("L2 should be [Discounted], got %v", layerNames(layers[2]))
	}
}

func TestDAG_DiamondDependency(t *testing.T) {
	defs := []FormulaDefinition{
		{Name: "A", Expression: "Amount * 2", ReturnType: "Number"},
		{Name: "B", Expression: "A + 1", ReturnType: "Number"},
		{Name: "C", Expression: "A + 2", ReturnType: "Number"},
		{Name: "D", Expression: "B + C", ReturnType: "Number"},
	}

	dag, err := NewFormulaDAG(defs)
	if err != nil {
		t.Fatal(err)
	}

	layers := dag.Layers()
	if len(layers) != 3 {
		t.Fatalf("expected 3 layers, got %d", len(layers))
	}

	// L0: A
	if len(layers[0]) != 1 {
		t.Errorf("L0 should have 1 formula, got %d", len(layers[0]))
	}
	// L1: B, C (both depend on A)
	if len(layers[1]) != 2 {
		t.Errorf("L1 should have 2 formulas, got %d", len(layers[1]))
	}
	// L2: D (depends on B and C)
	if len(layers[2]) != 1 {
		t.Errorf("L2 should have 1 formula, got %d", len(layers[2]))
	}
}

func TestDAG_CircularDependency(t *testing.T) {
	defs := []FormulaDefinition{
		{Name: "A", Expression: "B + 1", ReturnType: "Number"},
		{Name: "B", Expression: "A + 1", ReturnType: "Number"},
	}

	_, err := NewFormulaDAG(defs)
	if err == nil {
		t.Error("expected circular dependency error")
	}
}

func TestDAG_TopologicalOrder(t *testing.T) {
	defs := []FormulaDefinition{
		{Name: "Tax", Expression: "Amount * 0.1", ReturnType: "Number"},
		{Name: "Total", Expression: "Amount + Tax", ReturnType: "Number"},
	}

	dag, err := NewFormulaDAG(defs)
	if err != nil {
		t.Fatal(err)
	}

	order := dag.Order()
	taxIdx := indexOf(order, "Tax")
	totalIdx := indexOf(order, "Total")
	if taxIdx > totalIdx {
		t.Errorf("Tax should come before Total in topo order: %v", order)
	}
}

func TestDAG_CrossTableRefs(t *testing.T) {
	defs := []FormulaDefinition{
		{Name: "Industry", Expression: "Account.Industry", ReturnType: "Text"},
		{Name: "OwnerName", Expression: "Owner.Name", ReturnType: "Text"},
	}

	dag, err := NewFormulaDAG(defs)
	if err != nil {
		t.Fatal(err)
	}

	refs := dag.CrossTableRefs()
	if len(refs) != 2 {
		t.Errorf("expected 2 cross-table refs, got %d", len(refs))
	}
}

func TestDAG_AggregateRefs(t *testing.T) {
	defs := []FormulaDefinition{
		{Name: "TotalOpp", Expression: "SUM(Opportunity.Amount)", ReturnType: "Number"},
		{Name: "OppCount", Expression: "COUNT(Opportunity.Id)", ReturnType: "Number"},
	}

	dag, err := NewFormulaDAG(defs)
	if err != nil {
		t.Fatal(err)
	}

	aggs := dag.AggregateRefs()
	if len(aggs) != 2 {
		t.Errorf("expected 2 aggregate refs, got %d", len(aggs))
	}
}

// ============================================================================
// View Builder Tests
// ============================================================================

func TestViewBuilder_SingleLayer(t *testing.T) {
	builder := NewDSLViewBuilder()

	cfg := ViewBuildConfig{
		ObjectName:    "Account",
		PhysicalTable: "account",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Name", "Amount", "Industry"},
		Formulas: []FormulaDefinition{
			{Name: "Tax__c", Expression: "Amount * 0.1", ReturnType: "Number"},
			{Name: "Label__c", Expression: "Name & ' (' & Industry & ')'", ReturnType: "Text"},
		},
	}

	ddl, err := builder.BuildViewDDL(cfg)
	if err != nil {
		t.Fatal(err)
	}

	// Verify structure
	if !strings.Contains(ddl, "CREATE OR REPLACE SQL SECURITY INVOKER VIEW `v_account`") {
		t.Error("missing CREATE OR REPLACE VIEW")
	}
	if !strings.Contains(ddl, "FROM `account` t") {
		t.Error("missing FROM clause")
	}
	if !strings.Contains(ddl, "AS `Tax__c`") {
		t.Error("missing Tax__c formula column")
	}
	if !strings.Contains(ddl, "AS `Label__c`") {
		t.Error("missing Label__c formula column")
	}
	if !strings.Contains(ddl, "t.`Id`") {
		t.Error("missing base column Id")
	}

	t.Logf("Generated DDL:\n%s", ddl)
}

func TestViewBuilder_MultiLayer(t *testing.T) {
	builder := NewDSLViewBuilder()

	cfg := ViewBuildConfig{
		ObjectName:    "Quote",
		PhysicalTable: "quote",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Name", "Amount", "Discount"},
		Formulas: []FormulaDefinition{
			{Name: "Tax__c", Expression: "Amount * 0.1", ReturnType: "Number"},
			{Name: "Total__c", Expression: "Amount + Tax__c", ReturnType: "Number"},
			{Name: "Final__c", Expression: "Total__c * (1 - Discount)", ReturnType: "Number"},
		},
	}

	ddl, err := builder.BuildViewDDL(cfg)
	if err != nil {
		t.Fatal(err)
	}

	// Should have nested subqueries
	if !strings.Contains(ddl, "CREATE OR REPLACE SQL SECURITY INVOKER VIEW `v_quote`") {
		t.Error("missing CREATE OR REPLACE VIEW")
	}
	// L0 should contain Tax__c
	if !strings.Contains(ddl, "AS `Tax__c`") {
		t.Error("missing Tax__c in L0")
	}
	// Multi-layer uses L0, L1 aliases
	if !strings.Contains(ddl, "L0") {
		t.Error("expected L0 alias for multi-layer DDL")
	}

	t.Logf("Generated DDL:\n%s", ddl)
}

func TestViewBuilder_WithCrossTableJoins(t *testing.T) {
	builder := NewDSLViewBuilder()

	cfg := ViewBuildConfig{
		ObjectName:    "Opportunity",
		PhysicalTable: "opportunity",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Name", "Amount", "AccountId"},
		Formulas: []FormulaDefinition{
			{Name: "AccountIndustry__c", Expression: "Account.Industry", ReturnType: "Text"},
			{Name: "WeightedAmount__c", Expression: "Amount * 0.8", ReturnType: "Number"},
		},
		Joins: []JoinSpec{
			{Alias: "a", TargetTable: "account", SourceField: "AccountId", TargetField: "Id"},
		},
	}

	ddl, err := builder.BuildViewDDL(cfg)
	if err != nil {
		t.Fatal(err)
	}

	if !strings.Contains(ddl, "LEFT JOIN `account` a ON t.`AccountId` = a.`Id`") {
		t.Error("missing account JOIN")
	}
	if !strings.Contains(ddl, "a.`Industry`") {
		t.Error("missing cross-table reference a.Industry")
	}

	t.Logf("Generated DDL:\n%s", ddl)
}

func TestViewBuilder_WithAggregateJoins(t *testing.T) {
	builder := NewDSLViewBuilder()

	cfg := ViewBuildConfig{
		ObjectName:    "Account",
		PhysicalTable: "account",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Name"},
		Formulas: []FormulaDefinition{
			{Name: "Total_Opps__c", Expression: "SUM(Opportunity.Amount)", ReturnType: "Number"},
		},
		AggJoins: []AggregateJoinSpec{
			{
				Alias:       "rsq0",
				ChildTable:  "opportunity",
				AggFunc:     "SUM",
				AggField:    "Amount",
				ForeignKey:  "AccountId",
				ParentKey:   "Id",
				OutputAlias: "agg_val",
			},
		},
	}

	ddl, err := builder.BuildViewDDL(cfg)
	if err != nil {
		t.Fatal(err)
	}

	if !strings.Contains(ddl, "LEFT JOIN (") {
		t.Error("missing aggregate subquery JOIN")
	}
	if !strings.Contains(ddl, "SUM(`Amount`)") {
		t.Error("missing SUM aggregate")
	}
	if !strings.Contains(ddl, "GROUP BY") {
		t.Error("missing GROUP BY")
	}

	t.Logf("Generated DDL:\n%s", ddl)
}

func TestViewBuilder_AutoDetectJoins(t *testing.T) {
	builder := NewDSLViewBuilder()

	// Don't specify Joins — they should be auto-detected from cross-table refs
	cfg := ViewBuildConfig{
		ObjectName:    "Opportunity",
		PhysicalTable: "opportunity",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Name"},
		Formulas: []FormulaDefinition{
			{Name: "OwnerName__c", Expression: "Owner.Name", ReturnType: "Text"},
		},
	}

	ddl, err := builder.BuildViewDDL(cfg)
	if err != nil {
		t.Fatal(err)
	}

	if !strings.Contains(ddl, "LEFT JOIN") {
		t.Error("expected auto-detected JOIN")
	}
	if !strings.Contains(ddl, "own.`Name`") {
		t.Error("expected owner alias reference")
	}

	t.Logf("Generated DDL:\n%s", ddl)
}

// ============================================================================
// Integration / End-to-End Tests
// ============================================================================

func TestE2E_RealisticQuoteFormulas(t *testing.T) {
	builder := NewDSLViewBuilder()

	cfg := ViewBuildConfig{
		ObjectName:    "Quote",
		PhysicalTable: "quote",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Name", "Status", "OpportunityId", "AccountId", "OwnerId", "Subtotal", "Discount", "TotalPrice", "CreatedDate"},
		Formulas: []FormulaDefinition{
			{Name: "Tax_Amount__c", Expression: "Subtotal * 0.08", ReturnType: "Number"},
			{Name: "Discount_Amount__c", Expression: "Subtotal * Discount", ReturnType: "Number"},
			{Name: "Net_Amount__c", Expression: "Subtotal - Discount_Amount__c", ReturnType: "Number"},
			{Name: "Grand_Total__c", Expression: "Net_Amount__c + Tax_Amount__c", ReturnType: "Number"},
			{Name: "Is_High_Value__c", Expression: "IF(Grand_Total__c > 10000, TRUE, FALSE)", ReturnType: "Boolean"},
			{Name: "Status_Label__c", Expression: "CASE(Status, 'Draft', 'In Progress', 'Approved', 'Ready', 'Unknown')", ReturnType: "Text"},
			{Name: "Account_Industry__c", Expression: "Account.Industry", ReturnType: "Text"},
			{Name: "Owner_Name__c", Expression: "Owner.Name", ReturnType: "Text"},
		},
		Joins: []JoinSpec{
			{Alias: "a", TargetTable: "account", SourceField: "AccountId", TargetField: "Id"},
			{Alias: "own", TargetTable: "user", SourceField: "OwnerId", TargetField: "Id"},
		},
	}

	ddl, err := builder.BuildViewDDL(cfg)
	if err != nil {
		t.Fatal(err)
	}

	// Verify multi-layer structure
	if !strings.Contains(ddl, "CREATE OR REPLACE SQL SECURITY INVOKER VIEW `v_quote`") {
		t.Error("missing CREATE OR REPLACE VIEW")
	}

	// Verify all formulas appear
	for _, f := range cfg.Formulas {
		if !strings.Contains(ddl, fmt.Sprintf("AS `%s`", f.Name)) {
			t.Errorf("missing formula %s in DDL", f.Name)
		}
	}

	// Verify JOINs
	if !strings.Contains(ddl, "LEFT JOIN `account` a") {
		t.Error("missing account JOIN")
	}
	if !strings.Contains(ddl, "LEFT JOIN `user` own") {
		t.Error("missing user JOIN")
	}

	// Verify SQL patterns
	if !strings.Contains(ddl, "CASE WHEN") {
		t.Error("missing CASE WHEN for IF formula")
	}
	if !strings.Contains(ddl, "CASE t.`Status`") {
		t.Error("missing CASE for Status_Label")
	}

	t.Logf("Generated DDL (%d bytes):\n%s", len(ddl), ddl)
}

func TestE2E_AccountWithRollups(t *testing.T) {
	builder := NewDSLViewBuilder()

	cfg := ViewBuildConfig{
		ObjectName:    "Account",
		PhysicalTable: "account",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Name", "Industry", "OwnerId"},
		Formulas: []FormulaDefinition{
			{Name: "Total_Opportunities__c", Expression: "SUM(Opportunity.Amount)", ReturnType: "Number"},
			{Name: "Opp_Count__c", Expression: "COUNT(Opportunity.Id)", ReturnType: "Number"},
			{Name: "Avg_Opp_Value__c", Expression: "IF(Opp_Count__c > 0, Total_Opportunities__c / Opp_Count__c, 0)", ReturnType: "Number"},
			{Name: "Owner_Name__c", Expression: "Owner.Name", ReturnType: "Text"},
		},
		Joins: []JoinSpec{
			{Alias: "own", TargetTable: "user", SourceField: "OwnerId", TargetField: "Id"},
		},
		AggJoins: []AggregateJoinSpec{
			{Alias: "rsq0", ChildTable: "opportunity", AggFunc: "SUM", AggField: "Amount", ForeignKey: "AccountId", ParentKey: "Id", OutputAlias: "agg_val"},
			{Alias: "rsq1", ChildTable: "opportunity", AggFunc: "COUNT", AggField: "*", ForeignKey: "AccountId", ParentKey: "Id", OutputAlias: "agg_count"},
		},
	}

	ddl, err := builder.BuildViewDDL(cfg)
	if err != nil {
		t.Fatal(err)
	}

	// Verify aggregate JOINs
	if !strings.Contains(ddl, "SUM(`Amount`)") {
		t.Error("missing SUM aggregate")
	}
	if !strings.Contains(ddl, "COUNT(*)") {
		t.Error("missing COUNT aggregate")
	}

	// Verify multi-layer (Avg depends on Total and Count)
	if !strings.Contains(ddl, "AS `Avg_Opp_Value__c`") {
		t.Error("missing Avg formula")
	}

	t.Logf("Generated DDL (%d bytes):\n%s", len(ddl), ddl)
}

func TestE2E_ParseAndCompileRoundTrip(t *testing.T) {
	// Various formulas that should parse and compile cleanly
	formulas := []string{
		"Amount * 0.1",
		"IF(Amount > 0, Amount * 1.08, 0)",
		"CASE(Status, 'New', 1, 'Active', 2, 0)",
		"ISBLANK(Email)",
		"NULLVALUE(Phone, 'N/A')",
		"BLANKVALUE(Description, 'No description')",
		"LEN(Name)",
		"CONTAINS(Name, 'Corp')",
		"BEGINS(Name, 'A')",
		"MID(Name, 1, 5)",
		"SUBSTITUTE(Name, ' ', '_')",
		"ADDMONTHS(TODAY(), 3)",
		"YEAR(CreatedDate)",
		"ROUND(Amount, 2)",
		"ABS(Amount - 100)",
		"UPPER(Name)",
		"LOWER(Email)",
		"TRIM(Name)",
		"AND(Amount > 0, Status = 'Active')",
		"OR(Status = 'New', Status = 'Draft')",
		"NOT(ISBLANK(Email))",
		"FirstName & ' ' & LastName",
		"Amount ^ 2",
		"LEFT(Name, 3)",
		"RIGHT(Name, 3)",
		"VALUE('123')",
	}

	ctx := &CompileContext{
		TableAlias:    "t",
		JoinAliases:   map[string]string{},
		QualifyFields: true,
	}

	for _, f := range formulas {
		node, err := Parse(f)
		if err != nil {
			t.Errorf("Parse(%q): %v", f, err)
			continue
		}
		sql, err := Compile(node, ctx)
		if err != nil {
			t.Errorf("Compile(%q): %v", f, err)
			continue
		}
		if sql == "" {
			t.Errorf("Compile(%q) returned empty string", f)
		}
	}
}

// ============================================================================
// Acceptance Tests — 验收场景
// ============================================================================

// 场景一：简短公式 — 精确验证生成的 SQL
func TestAcceptance_SimpleFormulas(t *testing.T) {
	builder := NewDSLViewBuilder()

	cfg := ViewBuildConfig{
		ObjectName:    "Contact",
		PhysicalTable: "contact",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "FirstName", "LastName", "Email", "Phone", "Amount"},
		Formulas: []FormulaDefinition{
			{Name: "FullName__c", Expression: "FirstName & ' ' & LastName", ReturnType: "Text"},
			{Name: "Tax__c", Expression: "Amount * 0.08", ReturnType: "Number"},
			{Name: "HasEmail__c", Expression: "NOT(ISBLANK(Email))", ReturnType: "Boolean"},
			{Name: "DisplayPhone__c", Expression: "IF(ISBLANK(Phone), 'N/A', Phone)", ReturnType: "Text"},
			{Name: "Priority__c", Expression: "CASE(Amount, 0, 'None', 100, 'Low', 1000, 'Medium', 'High')", ReturnType: "Text"},
		},
	}

	ddl, err := builder.BuildViewDDL(cfg)
	if err != nil {
		t.Fatalf("BuildViewDDL failed: %v", err)
	}

	t.Logf("Simple Formulas DDL:\n%s", ddl)

	// 验证 CREATE VIEW 头
	assertContains(t, ddl, "CREATE OR REPLACE SQL SECURITY INVOKER VIEW `v_contact`", "missing view name")

	// 验证所有 base columns
	for _, col := range cfg.BaseColumns {
		assertContains(t, ddl, fmt.Sprintf("t.`%s`", col), fmt.Sprintf("missing base column %s", col))
	}

	// FullName: & → CONCAT
	assertContains(t, ddl, "CONCAT(", "FullName should use CONCAT")
	assertContains(t, ddl, "AS `FullName__c`", "missing FullName alias")

	// Tax: simple multiply
	assertContains(t, ddl, "(t.`Amount` * 0.08)", "Tax formula incorrect")
	assertContains(t, ddl, "AS `Tax__c`", "missing Tax alias")

	// HasEmail: NOT(ISBLANK) → (NOT (... IS NULL OR ...))
	assertContains(t, ddl, "NOT", "HasEmail should have NOT")
	assertContains(t, ddl, "IS NULL", "HasEmail should check IS NULL")
	assertContains(t, ddl, "AS `HasEmail__c`", "missing HasEmail alias")

	// DisplayPhone: IF → CASE WHEN
	assertContains(t, ddl, "CASE WHEN", "IF should compile to CASE WHEN")
	assertContains(t, ddl, "THEN 'N/A'", "missing THEN branch")
	assertContains(t, ddl, "AS `DisplayPhone__c`", "missing DisplayPhone alias")

	// Priority: CASE → CASE ... WHEN
	assertContains(t, ddl, "CASE t.`Amount`", "CASE should reference Amount")
	assertContains(t, ddl, "WHEN 0 THEN 'None'", "missing first CASE branch")
	assertContains(t, ddl, "WHEN 100 THEN 'Low'", "missing second CASE branch")
	assertContains(t, ddl, "WHEN 1000 THEN 'Medium'", "missing third CASE branch")
	assertContains(t, ddl, "ELSE 'High'", "missing CASE default")
	assertContains(t, ddl, "AS `Priority__c`", "missing Priority alias")

	// 所有公式无依赖 → 单层 SELECT（无嵌套子查询）
	assertNotContains(t, ddl, "L0", "simple formulas should be single-layer, no L0 alias")
	assertContains(t, ddl, "FROM `contact` t", "missing FROM clause")
}

// 场景二：跨表引用 3 层嵌套依赖
// L0: 从 Account/Owner 跨表取值 (CrossRef)
// L1: 用 L0 的公式结果做计算
// L2: 用 L1 的公式结果做计算
func TestAcceptance_CrossTable3Layers(t *testing.T) {
	builder := NewDSLViewBuilder()

	cfg := ViewBuildConfig{
		ObjectName:    "Opportunity",
		PhysicalTable: "opportunity",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Name", "Amount", "StageName", "AccountId", "OwnerId"},
		Formulas: []FormulaDefinition{
			// L0: 跨表取值 — Account.Industry, Owner.Name, 以及简单计算
			{Name: "Acct_Industry__c", Expression: "Account.Industry", ReturnType: "Text"},
			{Name: "Owner_Name__c", Expression: "Owner.Name", ReturnType: "Text"},
			{Name: "Base_Commission__c", Expression: "Amount * 0.05", ReturnType: "Number"},

			// L1: 依赖 L0 公式
			{Name: "Region_Factor__c", Expression: "IF(Acct_Industry__c = 'Technology', 1.5, IF(Acct_Industry__c = 'Finance', 1.3, 1.0))", ReturnType: "Number"},
			{Name: "Commission_With_Region__c", Expression: "Base_Commission__c * Region_Factor__c", ReturnType: "Number"},

			// L2: 依赖 L1 公式
			{Name: "Final_Payout__c", Expression: "IF(Commission_With_Region__c > 5000, Commission_With_Region__c * 0.9, Commission_With_Region__c)", ReturnType: "Number"},
			{Name: "Summary__c", Expression: "Owner_Name__c & ' | ' & Acct_Industry__c & ' | Payout: ' & Final_Payout__c", ReturnType: "Text"},
		},
		Joins: []JoinSpec{
			{Alias: "a", TargetTable: "account", SourceField: "AccountId", TargetField: "Id"},
			{Alias: "own", TargetTable: "user", SourceField: "OwnerId", TargetField: "Id"},
		},
	}

	ddl, err := builder.BuildViewDDL(cfg)
	if err != nil {
		t.Fatalf("BuildViewDDL failed: %v", err)
	}

	t.Logf("3-Layer Cross-Table DDL (%d bytes):\n%s", len(ddl), ddl)

	// --- 结构验证 ---

	// 应该有多层嵌套（L0, L1, L2）
	assertContains(t, ddl, "L0", "missing L0 layer")
	assertContains(t, ddl, "L1", "missing L1 layer")
	assertContains(t, ddl, "L2", "missing L2 layer")

	// --- L0 验证（最内层）：跨表引用 + base 公式 ---

	// Account.Industry → a.`Industry`
	assertContains(t, ddl, "a.`Industry`", "missing cross-table ref to Account.Industry")
	assertContains(t, ddl, "AS `Acct_Industry__c`", "missing Acct_Industry alias")

	// Owner.Name → own.`Name`
	assertContains(t, ddl, "own.`Name`", "missing cross-table ref to Owner.Name")
	assertContains(t, ddl, "AS `Owner_Name__c`", "missing Owner_Name alias")

	// Base_Commission = Amount * 0.05
	assertContains(t, ddl, "(t.`Amount` * 0.05)", "Base_Commission formula incorrect")

	// JOINs must be in the innermost layer
	assertContains(t, ddl, "LEFT JOIN `account` a ON t.`AccountId` = a.`Id`", "missing account JOIN")
	assertContains(t, ddl, "LEFT JOIN `user` own ON t.`OwnerId` = own.`Id`", "missing user JOIN")

	// --- L1 验证（中间层）：引用 L0 的公式结果 ---

	// Region_Factor: IF(Acct_Industry = 'Technology', ...) → 引用 L0.Acct_Industry__c
	assertContains(t, ddl, "AS `Region_Factor__c`", "missing Region_Factor alias")
	assertContains(t, ddl, "'Technology'", "Region_Factor should check Technology")
	assertContains(t, ddl, "'Finance'", "Region_Factor should check Finance")

	// Commission_With_Region: Base_Commission * Region_Factor → 引用两个 L0/L1 公式
	assertContains(t, ddl, "AS `Commission_With_Region__c`", "missing Commission_With_Region alias")

	// --- L2 验证（最外层）：引用 L1 的公式结果 ---

	// Final_Payout: IF(Commission_With_Region > 5000, ...) → 引用 L1 公式
	assertContains(t, ddl, "AS `Final_Payout__c`", "missing Final_Payout alias")
	assertContains(t, ddl, "5000", "Final_Payout should reference 5000 threshold")

	// Summary: 拼接多个公式字段 → CONCAT
	assertContains(t, ddl, "AS `Summary__c`", "missing Summary alias")

	// --- DAG 验证 ---

	dag, err := NewFormulaDAG(cfg.Formulas)
	if err != nil {
		t.Fatalf("DAG construction failed: %v", err)
	}

	layers := dag.Layers()
	if len(layers) < 3 {
		t.Fatalf("expected >= 3 layers, got %d", len(layers))
	}

	// 验证拓扑排序
	order := dag.Order()
	t.Logf("Topological order: %v", order)

	// Base_Commission must be before Commission_With_Region
	assertOrderBefore(t, order, "Base_Commission__c", "Commission_With_Region__c")
	// Region_Factor must be before Commission_With_Region
	assertOrderBefore(t, order, "Region_Factor__c", "Commission_With_Region__c")
	// Commission_With_Region must be before Final_Payout
	assertOrderBefore(t, order, "Commission_With_Region__c", "Final_Payout__c")
	// Acct_Industry must be before Region_Factor
	assertOrderBefore(t, order, "Acct_Industry__c", "Region_Factor__c")

	// 验证跨表引用收集
	crossRefs := dag.CrossTableRefs()
	if len(crossRefs) < 2 {
		t.Errorf("expected >= 2 cross-table refs, got %d", len(crossRefs))
	}
	foundAccount := false
	foundOwner := false
	for _, cr := range crossRefs {
		if cr.Object == "Account" && cr.Field == "Industry" {
			foundAccount = true
		}
		if cr.Object == "Owner" && cr.Field == "Name" {
			foundOwner = true
		}
	}
	if !foundAccount {
		t.Error("missing Account.Industry cross-ref")
	}
	if !foundOwner {
		t.Error("missing Owner.Name cross-ref")
	}
}

// 场景三：跨表 Rollup 计算 + 依赖 Rollup 结果的公式
// 模拟 Account 对象，聚合 Opportunity 和 Order 的数据，再用聚合结果做二次计算
func TestAcceptance_RollupCrossTableCalculation(t *testing.T) {
	builder := NewDSLViewBuilder()

	cfg := ViewBuildConfig{
		ObjectName:    "Account",
		PhysicalTable: "account",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Name", "Industry", "AnnualRevenue", "OwnerId"},
		Formulas: []FormulaDefinition{
			// L0: Rollup 聚合 + 简单公式
			{Name: "Total_Opp_Amount__c", Expression: "SUM(Opportunity.Amount)", ReturnType: "Number"},
			{Name: "Opp_Count__c", Expression: "COUNT(Opportunity.Id)", ReturnType: "Number"},
			{Name: "Max_Order_Value__c", Expression: "MAX(Order.TotalAmount)", ReturnType: "Number"},
			{Name: "Owner_Name__c", Expression: "Owner.Name", ReturnType: "Text"},

			// L1: 依赖 L0 rollup 结果
			{Name: "Avg_Opp_Size__c", Expression: "IF(Opp_Count__c > 0, Total_Opp_Amount__c / Opp_Count__c, 0)", ReturnType: "Number"},
			{Name: "Pipeline_Coverage__c", Expression: "IF(AnnualRevenue > 0, Total_Opp_Amount__c / AnnualRevenue, 0)", ReturnType: "Number"},
			{Name: "Top_Deal_Ratio__c", Expression: "IF(Total_Opp_Amount__c > 0, Max_Order_Value__c / Total_Opp_Amount__c, 0)", ReturnType: "Number"},

			// L2: 依赖 L1 结果
			{Name: "Health_Score__c", Expression: "IF(Avg_Opp_Size__c > 10000 AND Pipeline_Coverage__c > 1.5, 'Excellent', IF(Avg_Opp_Size__c > 5000, 'Good', 'At Risk'))", ReturnType: "Text"},
			{Name: "Account_Summary__c", Expression: "Name & ' [' & Industry & '] ' & Owner_Name__c & ' | Opps: ' & Opp_Count__c & ' | Health: ' & Health_Score__c", ReturnType: "Text"},
		},
		Joins: []JoinSpec{
			{Alias: "own", TargetTable: "user", SourceField: "OwnerId", TargetField: "Id"},
		},
		AggJoins: []AggregateJoinSpec{
			{Alias: "rsq0", ChildTable: "opportunity", AggFunc: "SUM", AggField: "Amount", ForeignKey: "AccountId", ParentKey: "Id", OutputAlias: "agg_sum_amount"},
			{Alias: "rsq1", ChildTable: "opportunity", AggFunc: "COUNT", AggField: "*", ForeignKey: "AccountId", ParentKey: "Id", OutputAlias: "agg_count"},
			{Alias: "rsq2", ChildTable: "order", AggFunc: "MAX", AggField: "TotalAmount", ForeignKey: "AccountId", ParentKey: "Id", OutputAlias: "agg_max_order"},
		},
	}

	ddl, err := builder.BuildViewDDL(cfg)
	if err != nil {
		t.Fatalf("BuildViewDDL failed: %v", err)
	}

	t.Logf("Rollup Cross-Table DDL (%d bytes):\n%s", len(ddl), ddl)

	// --- 结构验证 ---
	assertContains(t, ddl, "CREATE OR REPLACE SQL SECURITY INVOKER VIEW `v_account`", "missing view name")

	// 多层嵌套
	assertContains(t, ddl, "L0", "missing L0 layer")
	assertContains(t, ddl, "L1", "missing L1 layer")

	// --- Rollup JOIN 验证 ---

	// SUM(Amount) from opportunity
	assertContains(t, ddl, "SUM(`Amount`) AS `agg_sum_amount`", "missing SUM aggregate subquery")
	assertContains(t, ddl, "FROM `opportunity`", "missing opportunity table in aggregate")
	assertContains(t, ddl, "GROUP BY `AccountId`", "missing GROUP BY in aggregate")

	// COUNT(*) from opportunity
	assertContains(t, ddl, "COUNT(*) AS `agg_count`", "missing COUNT aggregate subquery")

	// MAX(TotalAmount) from order
	assertContains(t, ddl, "MAX(`TotalAmount`) AS `agg_max_order`", "missing MAX aggregate subquery")
	assertContains(t, ddl, "FROM `order`", "missing order table in aggregate")

	// 3 个 LEFT JOIN subquery
	count := strings.Count(ddl, "LEFT JOIN (")
	if count < 3 {
		t.Errorf("expected >= 3 aggregate LEFT JOIN subqueries, got %d", count)
	}

	// --- Rollup 结果在 SELECT 中被引用 ---

	// rsq0.agg_sum_amount → Total_Opp_Amount__c
	assertContains(t, ddl, "rsq0.`agg_sum_amount`", "rollup SUM not resolved to rsq0 alias")
	assertContains(t, ddl, "AS `Total_Opp_Amount__c`", "missing Total_Opp_Amount alias")

	// rsq1.agg_count → Opp_Count__c
	assertContains(t, ddl, "rsq1.`agg_count`", "rollup COUNT not resolved to rsq1 alias")
	assertContains(t, ddl, "AS `Opp_Count__c`", "missing Opp_Count alias")

	// rsq2.agg_max_order → Max_Order_Value__c
	assertContains(t, ddl, "rsq2.`agg_max_order`", "rollup MAX not resolved to rsq2 alias")
	assertContains(t, ddl, "AS `Max_Order_Value__c`", "missing Max_Order_Value alias")

	// --- L1: 二次计算引用 Rollup 结果 ---

	// Avg = Total / Count
	assertContains(t, ddl, "AS `Avg_Opp_Size__c`", "missing Avg_Opp_Size formula")
	// Pipeline_Coverage = Total / AnnualRevenue
	assertContains(t, ddl, "AS `Pipeline_Coverage__c`", "missing Pipeline_Coverage formula")
	// Top_Deal_Ratio = Max_Order / Total
	assertContains(t, ddl, "AS `Top_Deal_Ratio__c`", "missing Top_Deal_Ratio formula")

	// --- L2: 三次计算引用 L1 结果 ---

	assertContains(t, ddl, "AS `Health_Score__c`", "missing Health_Score formula")
	assertContains(t, ddl, "'Excellent'", "Health_Score missing Excellent branch")
	assertContains(t, ddl, "'Good'", "Health_Score missing Good branch")
	assertContains(t, ddl, "'At Risk'", "Health_Score missing At Risk branch")

	assertContains(t, ddl, "AS `Account_Summary__c`", "missing Account_Summary formula")

	// --- 跨表 JOIN 验证 ---
	assertContains(t, ddl, "LEFT JOIN `user` own ON t.`OwnerId` = own.`Id`", "missing user JOIN for Owner")
	assertContains(t, ddl, "own.`Name`", "missing Owner.Name reference")

	// --- DAG 验证 ---
	dag, err := NewFormulaDAG(cfg.Formulas)
	if err != nil {
		t.Fatalf("DAG construction failed: %v", err)
	}

	layers := dag.Layers()
	t.Logf("Number of layers: %d", len(layers))
	for i, layer := range layers {
		t.Logf("  Layer %d: %v", i, layerNames(layer))
	}

	if len(layers) < 3 {
		t.Fatalf("expected >= 3 layers for rollup + derived + derived, got %d", len(layers))
	}

	// L0 应该包含所有 rollup 和 base 公式
	l0Names := layerNames(layers[0])
	assertSliceContains(t, l0Names, "Total_Opp_Amount__c", "L0 missing Total_Opp_Amount")
	assertSliceContains(t, l0Names, "Opp_Count__c", "L0 missing Opp_Count")
	assertSliceContains(t, l0Names, "Max_Order_Value__c", "L0 missing Max_Order_Value")
	assertSliceContains(t, l0Names, "Owner_Name__c", "L0 missing Owner_Name")

	// L1 应该包含依赖 L0 rollup 的公式
	l1Names := layerNames(layers[1])
	assertSliceContains(t, l1Names, "Avg_Opp_Size__c", "L1 missing Avg_Opp_Size")
	assertSliceContains(t, l1Names, "Pipeline_Coverage__c", "L1 missing Pipeline_Coverage")
	assertSliceContains(t, l1Names, "Top_Deal_Ratio__c", "L1 missing Top_Deal_Ratio")

	// 验证聚合引用收集
	aggRefs := dag.AggregateRefs()
	if len(aggRefs) < 3 {
		t.Errorf("expected >= 3 aggregate refs, got %d", len(aggRefs))
	}

	// 验证拓扑排序
	order := dag.Order()
	t.Logf("Topological order: %v", order)
	assertOrderBefore(t, order, "Total_Opp_Amount__c", "Avg_Opp_Size__c")
	assertOrderBefore(t, order, "Opp_Count__c", "Avg_Opp_Size__c")
	assertOrderBefore(t, order, "Total_Opp_Amount__c", "Pipeline_Coverage__c")
	assertOrderBefore(t, order, "Max_Order_Value__c", "Top_Deal_Ratio__c")
	assertOrderBefore(t, order, "Avg_Opp_Size__c", "Health_Score__c")
	assertOrderBefore(t, order, "Pipeline_Coverage__c", "Health_Score__c")
	assertOrderBefore(t, order, "Health_Score__c", "Account_Summary__c")

	// 无残留 aggregate marker
	assertNotContains(t, ddl, "##AGG##", "unresolved aggregate marker in DDL")
}

// ============================================================================
// Helpers
// ============================================================================

func layerNames(defs []FormulaDefinition) []string {
	names := make([]string, len(defs))
	for i, d := range defs {
		names[i] = d.Name
	}
	return names
}

func indexOf(slice []string, item string) int {
	for i, s := range slice {
		if s == item {
			return i
		}
	}
	return -1
}

func assertContains(t *testing.T, haystack, needle, msg string) {
	t.Helper()
	if !strings.Contains(haystack, needle) {
		t.Errorf("%s: DDL does not contain %q", msg, needle)
	}
}

func assertNotContains(t *testing.T, haystack, needle, msg string) {
	t.Helper()
	if strings.Contains(haystack, needle) {
		t.Errorf("%s: DDL unexpectedly contains %q", msg, needle)
	}
}

func assertOrderBefore(t *testing.T, order []string, before, after string) {
	t.Helper()
	idxBefore := indexOf(order, before)
	idxAfter := indexOf(order, after)
	if idxBefore < 0 {
		t.Errorf("order missing %q", before)
		return
	}
	if idxAfter < 0 {
		t.Errorf("order missing %q", after)
		return
	}
	if idxBefore >= idxAfter {
		t.Errorf("%q (idx=%d) should come before %q (idx=%d) in order %v", before, idxBefore, after, idxAfter, order)
	}
}

// ============================================================================
// Rollup + Multi-Layer Edge Case Tests
// ============================================================================

// 场景：Rollup 聚合结果在多层间跨层传递并参与复杂表达式
func TestMultiLayer_RollupWithCrossTableAndStringConcat(t *testing.T) {
	builder := NewDSLViewBuilder()

	cfg := ViewBuildConfig{
		ObjectName:    "Account",
		PhysicalTable: "account",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Name", "OwnerId"},
		Formulas: []FormulaDefinition{
			// L0: Rollup + CrossRef
			{Name: "Opp_Total__c", Expression: "SUM(Opportunity.Amount)", ReturnType: "Number"},
			{Name: "Owner_Name__c", Expression: "Owner.Name", ReturnType: "Text"},
			// L1: 引用 L0 rollup + 跨表 + 字符串拼接
			{Name: "Summary__c", Expression: "Name & ' (' & Owner_Name__c & ') - $' & Opp_Total__c", ReturnType: "Text"},
			// L1: 引用 L0 rollup 做数学运算
			{Name: "Avg_Monthly__c", Expression: "IF(Opp_Total__c > 0, Opp_Total__c / 12, 0)", ReturnType: "Number"},
			// L2: 引用 L1 结果
			{Name: "Category__c", Expression: "IF(Avg_Monthly__c > 10000, 'Enterprise', IF(Avg_Monthly__c > 1000, 'Mid-Market', 'SMB'))", ReturnType: "Text"},
		},
		Joins: []JoinSpec{
			{Alias: "own", TargetTable: "user", SourceField: "OwnerId", TargetField: "Id"},
		},
		AggJoins: []AggregateJoinSpec{
			{Alias: "rsq0", ChildTable: "opportunity", AggFunc: "SUM", AggField: "Amount", ForeignKey: "AccountId", ParentKey: "Id", OutputAlias: "agg_total"},
		},
	}

	ddl, err := builder.BuildViewDDL(cfg)
	if err != nil {
		t.Fatalf("BuildViewDDL failed: %v", err)
	}

	// 验证 DAG 层级
	dag, err := NewFormulaDAG(cfg.Formulas)
	if err != nil {
		t.Fatalf("DAG failed: %v", err)
	}
	layers := dag.Layers()
	if len(layers) < 3 {
		t.Fatalf("expected >= 3 layers, got %d", len(layers))
	}

	// L0: Rollup + CrossRef (无本层依赖)
	l0 := layerNames(layers[0])
	assertSliceContains(t, l0, "Opp_Total__c", "L0 missing Rollup")
	assertSliceContains(t, l0, "Owner_Name__c", "L0 missing CrossRef")

	// L1: 依赖 L0
	l1 := layerNames(layers[1])
	assertSliceContains(t, l1, "Summary__c", "L1 missing string concat formula")
	assertSliceContains(t, l1, "Avg_Monthly__c", "L1 missing avg formula")

	// L2: 依赖 L1
	l2 := layerNames(layers[2])
	assertSliceContains(t, l2, "Category__c", "L2 missing category formula")

	// DDL 结构验证
	assertContains(t, ddl, "SUM(`Amount`) AS `agg_total`", "missing SUM aggregate")
	assertContains(t, ddl, "LEFT JOIN `user` own", "missing Owner JOIN")
	assertContains(t, ddl, "AS `Summary__c`", "missing Summary formula")
	assertContains(t, ddl, "AS `Category__c`", "missing Category formula")
	assertContains(t, ddl, "'Enterprise'", "missing Enterprise branch")
	assertContains(t, ddl, "'SMB'", "missing SMB branch")
}

// 场景：Rollup 公式引用另一个 Rollup 结果（多 Rollup 交叉依赖）
func TestMultiLayer_MultiRollupCrossReference(t *testing.T) {
	builder := NewDSLViewBuilder()

	cfg := ViewBuildConfig{
		ObjectName:    "Account",
		PhysicalTable: "account",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Name"},
		Formulas: []FormulaDefinition{
			// L0: 两个独立 Rollup
			{Name: "Total_Revenue__c", Expression: "SUM(Opportunity.Amount)", ReturnType: "Number"},
			{Name: "Total_Cost__c", Expression: "SUM(Order.Cost__c)", ReturnType: "Number"},
			// L1: 同时依赖两个 Rollup
			{Name: "Profit__c", Expression: "Total_Revenue__c - Total_Cost__c", ReturnType: "Number"},
			{Name: "Margin__c", Expression: "IF(Total_Revenue__c > 0, (Total_Revenue__c - Total_Cost__c) / Total_Revenue__c * 100, 0)", ReturnType: "Number"},
			// L2: 依赖 L1
			{Name: "Margin_Grade__c", Expression: "IF(Margin__c > 50, 'A', IF(Margin__c > 20, 'B', 'C'))", ReturnType: "Text"},
		},
		AggJoins: []AggregateJoinSpec{
			{Alias: "rsq0", ChildTable: "opportunity", AggFunc: "SUM", AggField: "Amount", ForeignKey: "AccountId", ParentKey: "Id", OutputAlias: "agg_revenue"},
			{Alias: "rsq1", ChildTable: "order", AggFunc: "SUM", AggField: "Cost__c", ForeignKey: "AccountId", ParentKey: "Id", OutputAlias: "agg_cost"},
		},
	}

	ddl, err := builder.BuildViewDDL(cfg)
	if err != nil {
		t.Fatalf("BuildViewDDL failed: %v", err)
	}

	// DAG 验证
	dag, err := NewFormulaDAG(cfg.Formulas)
	if err != nil {
		t.Fatalf("DAG failed: %v", err)
	}
	layers := dag.Layers()
	if len(layers) < 3 {
		t.Fatalf("expected >= 3 layers, got %d", len(layers))
	}

	// L0: 两个 Rollup
	l0 := layerNames(layers[0])
	assertSliceContains(t, l0, "Total_Revenue__c", "L0 missing revenue rollup")
	assertSliceContains(t, l0, "Total_Cost__c", "L0 missing cost rollup")

	// L1: 依赖两个 Rollup 的交叉计算
	l1 := layerNames(layers[1])
	assertSliceContains(t, l1, "Profit__c", "L1 missing Profit")
	assertSliceContains(t, l1, "Margin__c", "L1 missing Margin")

	// L2: 依赖 Margin
	l2 := layerNames(layers[2])
	assertSliceContains(t, l2, "Margin_Grade__c", "L2 missing Margin_Grade")

	// DDL 验证
	assertContains(t, ddl, "rsq0.`agg_revenue`", "Rollup revenue not resolved")
	assertContains(t, ddl, "rsq1.`agg_cost`", "Rollup cost not resolved")
	assertContains(t, ddl, "AS `Profit__c`", "missing Profit formula")
	assertContains(t, ddl, "AS `Margin__c`", "missing Margin formula")
	assertContains(t, ddl, "AS `Margin_Grade__c`", "missing Margin_Grade formula")

	// 两个 LEFT JOIN subquery
	count := strings.Count(ddl, "LEFT JOIN (")
	if count < 2 {
		t.Errorf("expected >= 2 aggregate LEFT JOIN subqueries, got %d", count)
	}
}

// 场景：单个 Rollup 无依赖（单层，不需要嵌套）
func TestSingleLayer_RollupOnly(t *testing.T) {
	builder := NewDSLViewBuilder()

	cfg := ViewBuildConfig{
		ObjectName:    "Account",
		PhysicalTable: "account",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Name"},
		Formulas: []FormulaDefinition{
			{Name: "Total_Amount__c", Expression: "SUM(Opportunity.Amount)", ReturnType: "Number"},
		},
		AggJoins: []AggregateJoinSpec{
			{Alias: "rsq0", ChildTable: "opportunity", AggFunc: "SUM", AggField: "Amount", ForeignKey: "AccountId", ParentKey: "Id", OutputAlias: "agg_val"},
		},
	}

	ddl, err := builder.BuildViewDDL(cfg)
	if err != nil {
		t.Fatal(err)
	}

	// 单层不应有嵌套 subquery
	if strings.Contains(ddl, ") L0") {
		t.Error("single-layer rollup should not generate nested subqueries")
	}

	// 但应有 LEFT JOIN subquery
	assertContains(t, ddl, "LEFT JOIN (", "missing rollup JOIN")
	assertContains(t, ddl, "SUM(`Amount`) AS `agg_val`", "missing aggregate expression")
	assertContains(t, ddl, "AS `Total_Amount__c`", "missing formula alias")
}

// 场景：深层跨表引用 + Rollup 混合在同一 View
func TestMultiLayer_DeepJoinPlusRollup(t *testing.T) {
	builder := NewDSLViewBuilder()

	cfg := ViewBuildConfig{
		ObjectName:    "Quote",
		PhysicalTable: "quote",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Name", "AccountId", "Amount"},
		Formulas: []FormulaDefinition{
			// L0: DeepJoin 和 Rollup 同时
			{Name: "Account_Owner_Name__c", Expression: "Account.Owner.Name", ReturnType: "Text"},
			{Name: "Line_Total__c", Expression: "SUM(QuoteLineItem.TotalPrice)", ReturnType: "Number"},
			// L1: 混合引用
			{Name: "Variance__c", Expression: "Amount - Line_Total__c", ReturnType: "Number"},
		},
		DeepJoins: []DeepJoinChain{
			{
				Parts:      []string{"Account", "Owner", "Name"},
				FinalAlias: "dj_owner",
				FinalField: "Name",
				Hops: []DeepJoinHop{
					{SourceAlias: "t", SourceField: "AccountId", Alias: "dj_acc", TargetTable: "account", TargetField: "Id"},
					{SourceAlias: "dj_acc", SourceField: "OwnerId", Alias: "dj_owner", TargetTable: "user", TargetField: "Id"},
				},
			},
		},
		AggJoins: []AggregateJoinSpec{
			{Alias: "rsq0", ChildTable: "quotelineitem", AggFunc: "SUM", AggField: "TotalPrice", ForeignKey: "QuoteId", ParentKey: "Id", OutputAlias: "agg_line_total"},
		},
	}

	ddl, err := builder.BuildViewDDL(cfg)
	if err != nil {
		t.Fatalf("BuildViewDDL failed: %v", err)
	}

	// DeepJoin
	assertContains(t, ddl, "dj_owner.`Name` AS `Account_Owner_Name__c`", "missing deep join field")
	assertContains(t, ddl, "LEFT JOIN `account` dj_acc", "missing first deep hop")
	assertContains(t, ddl, "LEFT JOIN `user` dj_owner", "missing second deep hop")

	// Rollup
	assertContains(t, ddl, "SUM(`TotalPrice`) AS `agg_line_total`", "missing rollup aggregate")

	// L1 Variance
	assertContains(t, ddl, "AS `Variance__c`", "missing Variance formula")

	// DAG 验证
	dag, err := NewFormulaDAG(cfg.Formulas)
	if err != nil {
		t.Fatalf("DAG failed: %v", err)
	}
	layers := dag.Layers()
	if len(layers) < 2 {
		t.Fatalf("expected >= 2 layers, got %d", len(layers))
	}
	l0 := layerNames(layers[0])
	assertSliceContains(t, l0, "Account_Owner_Name__c", "L0 missing deep join formula")
	assertSliceContains(t, l0, "Line_Total__c", "L0 missing rollup formula")
}

func assertSliceContains(t *testing.T, slice []string, item, msg string) {
	t.Helper()
	for _, s := range slice {
		if s == item {
			return
		}
	}
	t.Errorf("%s: slice %v does not contain %q", msg, slice, item)
}

// ============================================================================
// Bug Fix Regression Tests
// ============================================================================

func TestCompile_MIN_UsesLEAST(t *testing.T) {
	ctx := &CompileContext{TableAlias: "t", JoinAliases: map[string]string{}, QualifyFields: true}

	// Build AST directly since parser treats MIN as aggregate and only allows single-arg
	node := &FuncCall{
		Name: "MIN",
		Args: []Node{
			&FieldRef{Name: "Amount"},
			&NumberLit{Value: "100"},
		},
	}
	sql, err := Compile(node, ctx)
	if err != nil {
		t.Fatal(err)
	}
	if !strings.Contains(sql, "LEAST(") {
		t.Errorf("MIN(a,b) should compile to LEAST, got %q", sql)
	}
	if strings.Contains(sql, "GREATEST(") {
		t.Errorf("MIN(a,b) should NOT use GREATEST, got %q", sql)
	}
}

func TestCompile_MAX_UsesGREATEST(t *testing.T) {
	ctx := &CompileContext{TableAlias: "t", JoinAliases: map[string]string{}, QualifyFields: true}

	// Build AST directly since parser treats MAX as aggregate and only allows single-arg
	node := &FuncCall{
		Name: "MAX",
		Args: []Node{
			&FieldRef{Name: "Amount"},
			&NumberLit{Value: "100"},
		},
	}
	sql, err := Compile(node, ctx)
	if err != nil {
		t.Fatal(err)
	}
	if !strings.Contains(sql, "GREATEST(") {
		t.Errorf("MAX(a,b) should compile to GREATEST, got %q", sql)
	}
}

func TestCompile_FunctionAllowlist_BlocksDangerous(t *testing.T) {
	ctx := &CompileContext{TableAlias: "t", JoinAliases: map[string]string{}, QualifyFields: false}

	dangerous := []string{
		"SLEEP(1)",
		"BENCHMARK(1000000, SHA1('test'))",
		"LOAD_FILE('/etc/passwd')",
	}
	for _, formula := range dangerous {
		node, err := Parse(formula)
		if err != nil {
			// Parse failure is also acceptable — blocks the injection
			continue
		}
		_, err = Compile(node, ctx)
		if err == nil {
			t.Errorf("expected error for dangerous function %q, but got none", formula)
		} else if !strings.Contains(err.Error(), "unsupported function") {
			t.Errorf("expected 'unsupported function' error for %q, got: %v", formula, err)
		}
	}
}

func TestCompile_FunctionAllowlist_AllowsSafe(t *testing.T) {
	ctx := &CompileContext{TableAlias: "t", JoinAliases: map[string]string{}, QualifyFields: false}

	safe := []struct {
		formula  string
		contains string
	}{
		{"COALESCE(Amount, 0)", "COALESCE("},
		{"CONCAT(FirstName, LastName)", "CONCAT("},
		{"ROUND(Amount, 2)", "ROUND("},
		{"GREATEST(a, b)", "GREATEST("},
		{"LEAST(a, b)", "LEAST("},
	}
	for _, tt := range safe {
		node, err := Parse(tt.formula)
		if err != nil {
			t.Errorf("Parse(%q): %v", tt.formula, err)
			continue
		}
		sql, err := Compile(node, ctx)
		if err != nil {
			t.Errorf("Compile(%q) should succeed, got: %v", tt.formula, err)
			continue
		}
		if !strings.Contains(sql, tt.contains) {
			t.Errorf("Compile(%q) = %q, expected to contain %q", tt.formula, sql, tt.contains)
		}
	}
}

func TestEscapeSQLString_BackslashThenQuote(t *testing.T) {
	// Input: a string containing both backslash and single quote
	// The backslash must be escaped FIRST, then the quote
	result := utils.EscapeSQLString(`it's a \path`)
	// Expected: backslash doubled first → \\, then quote doubled → ''
	expected := `it''s a \\path`
	if result != expected {
		t.Errorf("utils.EscapeSQLString(%q) = %q, want %q", `it's a \path`, result, expected)
	}

	// Edge case: backslash immediately before a quote: \'
	result2 := utils.EscapeSQLString(`\'`)
	expected2 := `\\''`
	if result2 != expected2 {
		t.Errorf("utils.EscapeSQLString(%q) = %q, want %q", `\'`, result2, expected2)
	}
}

// ============================================================================
// Edge / Boundary Case Tests
// ============================================================================

// 边界：跨表引用缺少 JOIN alias 时应 fallback 到本表字段
func TestCompile_CrossRef_MissingJoinAlias_Fallback(t *testing.T) {
	ctx := &CompileContext{
		TableAlias:    "t",
		JoinAliases:   map[string]string{}, // 空：没有任何 JOIN 配置
		QualifyFields: true,
	}

	node, err := Parse("Account.Industry")
	if err != nil {
		t.Fatal(err)
	}
	sql, err := Compile(node, ctx)
	if err != nil {
		t.Fatal(err)
	}
	// 没有 Account alias 时，应 fallback 到 t.`Industry`（本表物理列）
	if sql != "t.`Industry`" {
		t.Errorf("missing alias should fallback to local field, got %q", sql)
	}
}

// 边界：深层嵌套 IF（5 层）确保不会栈溢出或截断
func TestCompile_DeeplyNestedIF_5Levels(t *testing.T) {
	ctx := &CompileContext{TableAlias: "t", JoinAliases: map[string]string{}, QualifyFields: true}

	formula := "IF(Amount > 100000, 'S', IF(Amount > 50000, 'A', IF(Amount > 10000, 'B', IF(Amount > 1000, 'C', IF(Amount > 0, 'D', 'F')))))"
	node, err := Parse(formula)
	if err != nil {
		t.Fatalf("Parse failed: %v", err)
	}
	sql, err := Compile(node, ctx)
	if err != nil {
		t.Fatalf("Compile failed: %v", err)
	}

	// 应该产生 5 层嵌套的 CASE WHEN
	caseCount := strings.Count(sql, "CASE WHEN")
	if caseCount != 5 {
		t.Errorf("expected 5 nested CASE WHEN, got %d in: %s", caseCount, sql)
	}
	// 所有分支都应存在
	for _, grade := range []string{"'S'", "'A'", "'B'", "'C'", "'D'", "'F'"} {
		if !strings.Contains(sql, grade) {
			t.Errorf("missing grade %s in SQL: %s", grade, sql)
		}
	}
}

// 边界：公式中的特殊字符字符串（单引号、反斜杠、中文）
func TestCompile_SpecialCharacterStrings(t *testing.T) {
	ctx := &CompileContext{TableAlias: "t", JoinAliases: map[string]string{}, QualifyFields: false}

	tests := []struct {
		input    string
		contains string
	}{
		// 中文字符串
		{`IF(Status = '已关闭', 1, 0)`, "'已关闭'"},
		// 空字符串
		{`IF(Name = '', 'Empty', Name)`, "''"},
		// 数字字符串
		{`CONTAINS(Phone, '+86')`, "'+86'"},
	}

	for _, tt := range tests {
		node, err := Parse(tt.input)
		if err != nil {
			t.Errorf("Parse(%q) failed: %v", tt.input, err)
			continue
		}
		sql, err := Compile(node, ctx)
		if err != nil {
			t.Errorf("Compile(%q) failed: %v", tt.input, err)
			continue
		}
		if !strings.Contains(sql, tt.contains) {
			t.Errorf("Compile(%q) = %q, expected to contain %q", tt.input, sql, tt.contains)
		}
	}
}

// 边界：NULL 字面量在公式中传播
func TestCompile_NULLHandling(t *testing.T) {
	ctx := &CompileContext{TableAlias: "t", JoinAliases: map[string]string{}, QualifyFields: false}

	tests := []struct {
		input    string
		contains string
	}{
		{"NULLVALUE(Amount, 0)", "IFNULL"},
		{"BLANKVALUE(Name, 'N/A')", "IFNULL(NULLIF"},
		{"IF(ISBLANK(Email), NULL, Email)", "NULL"},
		{"NULLVALUE(NULL, 0)", "IFNULL"},
	}

	for _, tt := range tests {
		node, err := Parse(tt.input)
		if err != nil {
			t.Errorf("Parse(%q) failed: %v", tt.input, err)
			continue
		}
		sql, err := Compile(node, ctx)
		if err != nil {
			t.Errorf("Compile(%q) failed: %v", tt.input, err)
			continue
		}
		if !strings.Contains(sql, tt.contains) {
			t.Errorf("Compile(%q) = %q, expected to contain %q", tt.input, sql, tt.contains)
		}
	}
}

// 边界：多层依赖中 NULL 传播 — L0 结果 NULL 时 L1 是否正确处理
func TestMultiLayer_NULLPropagation(t *testing.T) {
	builder := NewDSLViewBuilder()

	cfg := ViewBuildConfig{
		ObjectName:    "Opportunity",
		PhysicalTable: "opportunity",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Amount", "Probability"},
		Formulas: []FormulaDefinition{
			// L0: 可能为 NULL（Amount 或 Probability 为 NULL 时）
			{Name: "Expected_Revenue__c", Expression: "Amount * Probability / 100", ReturnType: "Number"},
			// L1: 用 NULLVALUE 保护 NULL 传播
			{Name: "Safe_Revenue__c", Expression: "NULLVALUE(Expected_Revenue__c, 0)", ReturnType: "Number"},
			// L1: 不保护，直接引用（NULL 应传播）
			{Name: "Revenue_Category__c", Expression: "IF(Expected_Revenue__c > 10000, 'High', 'Low')", ReturnType: "Text"},
			// L2: 依赖 L1 安全值
			{Name: "Monthly_Forecast__c", Expression: "Safe_Revenue__c / 12", ReturnType: "Number"},
		},
	}

	ddl, err := builder.BuildViewDDL(cfg)
	if err != nil {
		t.Fatalf("BuildViewDDL failed: %v", err)
	}

	dag, err := NewFormulaDAG(cfg.Formulas)
	if err != nil {
		t.Fatalf("DAG failed: %v", err)
	}
	layers := dag.Layers()

	// L0: Expected_Revenue
	l0 := layerNames(layers[0])
	assertSliceContains(t, l0, "Expected_Revenue__c", "L0")

	// L1: Safe_Revenue + Revenue_Category 都依赖 L0
	if len(layers) < 3 {
		t.Fatalf("expected >= 3 layers, got %d", len(layers))
	}
	l1 := layerNames(layers[1])
	assertSliceContains(t, l1, "Safe_Revenue__c", "L1 missing NULLVALUE formula")
	assertSliceContains(t, l1, "Revenue_Category__c", "L1 missing category formula")

	// DDL 验证 NULLVALUE 转换为 IFNULL
	assertContains(t, ddl, "IFNULL(", "missing IFNULL for NULL protection")
	assertContains(t, ddl, "AS `Monthly_Forecast__c`", "missing L2 formula")
}

// 边界：所有聚合类型 (SUM/COUNT/MIN/MAX/AVG) 在同一 View 中
func TestViewBuilder_AllAggregateTypes(t *testing.T) {
	builder := NewDSLViewBuilder()

	cfg := ViewBuildConfig{
		ObjectName:    "Account",
		PhysicalTable: "account",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Name"},
		Formulas: []FormulaDefinition{
			{Name: "Total__c", Expression: "SUM(Opportunity.Amount)", ReturnType: "Number"},
			{Name: "Count__c", Expression: "COUNT(Opportunity.Id)", ReturnType: "Number"},
			{Name: "Min_Deal__c", Expression: "MIN(Opportunity.Amount)", ReturnType: "Number"},
			{Name: "Max_Deal__c", Expression: "MAX(Opportunity.Amount)", ReturnType: "Number"},
			{Name: "Avg_Deal__c", Expression: "AVG(Opportunity.Amount)", ReturnType: "Number"},
		},
		AggJoins: []AggregateJoinSpec{
			{Alias: "rsq0", ChildTable: "opportunity", AggFunc: "SUM", AggField: "Amount", ForeignKey: "AccountId", ParentKey: "Id", OutputAlias: "agg_sum"},
			{Alias: "rsq1", ChildTable: "opportunity", AggFunc: "COUNT", AggField: "*", ForeignKey: "AccountId", ParentKey: "Id", OutputAlias: "agg_cnt"},
			{Alias: "rsq2", ChildTable: "opportunity", AggFunc: "MIN", AggField: "Amount", ForeignKey: "AccountId", ParentKey: "Id", OutputAlias: "agg_min"},
			{Alias: "rsq3", ChildTable: "opportunity", AggFunc: "MAX", AggField: "Amount", ForeignKey: "AccountId", ParentKey: "Id", OutputAlias: "agg_max"},
			{Alias: "rsq4", ChildTable: "opportunity", AggFunc: "AVG", AggField: "Amount", ForeignKey: "AccountId", ParentKey: "Id", OutputAlias: "agg_avg"},
		},
	}

	ddl, err := builder.BuildViewDDL(cfg)
	if err != nil {
		t.Fatalf("BuildViewDDL failed: %v", err)
	}

	// 所有 5 种聚合函数都应该出现
	assertContains(t, ddl, "SUM(`Amount`) AS `agg_sum`", "missing SUM")
	assertContains(t, ddl, "COUNT(*) AS `agg_cnt`", "missing COUNT")
	assertContains(t, ddl, "MIN(`Amount`) AS `agg_min`", "missing MIN")
	assertContains(t, ddl, "MAX(`Amount`) AS `agg_max`", "missing MAX")
	assertContains(t, ddl, "AVG(`Amount`) AS `agg_avg`", "missing AVG")

	// 5 个 LEFT JOIN subquery
	count := strings.Count(ddl, "LEFT JOIN (")
	if count < 5 {
		t.Errorf("expected >= 5 aggregate LEFT JOINs, got %d", count)
	}

	// 所有字段都有 alias
	assertContains(t, ddl, "AS `Total__c`", "missing Total alias")
	assertContains(t, ddl, "AS `Count__c`", "missing Count alias")
	assertContains(t, ddl, "AS `Min_Deal__c`", "missing Min_Deal alias")
	assertContains(t, ddl, "AS `Max_Deal__c`", "missing Max_Deal alias")
	assertContains(t, ddl, "AS `Avg_Deal__c`", "missing Avg_Deal alias")
}

// 边界：循环依赖 — 3 节点环 A→B→C→A
func TestDAG_CircularDependency_3Node(t *testing.T) {
	defs := []FormulaDefinition{
		{Name: "A__c", Expression: "C__c + 1", ReturnType: "Number"},
		{Name: "B__c", Expression: "A__c * 2", ReturnType: "Number"},
		{Name: "C__c", Expression: "B__c - 3", ReturnType: "Number"},
	}

	_, err := NewFormulaDAG(defs)
	if err == nil {
		t.Error("expected circular dependency error for A→B→C→A cycle")
	}
}

// 边界：自引用公式（A 引用 A 自身）
func TestDAG_SelfReference(t *testing.T) {
	defs := []FormulaDefinition{
		{Name: "Running_Total__c", Expression: "Running_Total__c + Amount", ReturnType: "Number"},
	}

	_, err := NewFormulaDAG(defs)
	if err == nil {
		t.Error("expected error for self-referencing formula")
	}
}

// 边界：空公式列表应返回明确错误
func TestViewBuilder_EmptyFormulas(t *testing.T) {
	builder := NewDSLViewBuilder()

	cfg := ViewBuildConfig{
		ObjectName:    "Account",
		PhysicalTable: "account",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Name"},
		Formulas:      []FormulaDefinition{},
	}

	_, err := builder.BuildViewDDL(cfg)
	if err == nil {
		t.Fatal("expected error for empty formulas, got nil")
	}
	if !strings.Contains(err.Error(), "no formulas") {
		t.Errorf("expected 'no formulas' error, got: %v", err)
	}
}

// 边界：只有基础列无公式无 Rollup — 应该拒绝创建无意义的 View
func TestViewBuilder_BaseColumnsOnly(t *testing.T) {
	builder := NewDSLViewBuilder()

	cfg := ViewBuildConfig{
		ObjectName:    "Lead",
		PhysicalTable: "lead",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "FirstName", "LastName", "Email"},
		Formulas:      []FormulaDefinition{},
	}

	_, err := builder.BuildViewDDL(cfg)
	if err == nil {
		t.Fatal("expected error for base-only config (no formulas), got nil")
	}
}

func TestCompile_DATE_PropagatesErrors(t *testing.T) {
	ctx := &CompileContext{TableAlias: "t", JoinAliases: map[string]string{}, QualifyFields: true}

	// DATE with 3 args where one references a non-existent cross-table with no join alias
	// should propagate the compile error, not silently return NULL
	node, err := Parse("DATE(YEAR(CloseDate), MONTH(CloseDate), 1)")
	if err != nil {
		t.Fatal(err)
	}
	// This should compile successfully since all args are valid
	sql, err := Compile(node, ctx)
	if err != nil {
		t.Fatalf("expected success for valid DATE(y,m,d), got: %v", err)
	}
	if !strings.Contains(sql, "STR_TO_DATE") {
		t.Errorf("DATE(y,m,d) should use STR_TO_DATE, got %q", sql)
	}
}