package analytics

import (
	"encoding/json"
	"os"
	"path/filepath"
	"regexp"
	"strings"
	"testing"

	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/formulastorage"
	"github.com/nexuscrm/shared/pkg/models"
)

func TestActiveFormulaSmokeFixtureBuildsViewDDL(t *testing.T) {
	fixturePath := filepath.Clean(filepath.Join("..", "backend", "testdata", "packs", "active-formula-smoke", "objects", "work_item.json"))
	content, err := os.ReadFile(fixturePath)
	if err != nil {
		t.Fatalf("read active formula smoke fixture: %v", err)
	}
	if strings.Contains(string(content), "__c") {
		t.Fatalf("active formula smoke fixture contains tenant/custom suffix payload: %s", fixturePath)
	}

	var objects []struct {
		APIName string `json:"apiName"`
		Fields  []struct {
			APIName            string `json:"apiName"`
			Type               string `json:"type"`
			FormulaMode        string `json:"formulaMode"`
			FormulaStorageMode string `json:"formulaStorageMode"`
			Formula            string `json:"formula"`
			ReturnType         string `json:"returnType"`
		} `json:"fields"`
	}
	if err := json.Unmarshal(content, &objects); err != nil {
		t.Fatalf("parse active formula smoke fixture: %v", err)
	}
	if len(objects) != 1 || objects[0].APIName != "WorkItem" {
		t.Fatalf("fixture objects = %#v, want one WorkItem object", objects)
	}

	var baseColumns []string
	var formulas []FormulaDefinition
	for _, field := range objects[0].Fields {
		resolved, err := formulastorage.Resolve(formulastorage.Spec{
			FieldType:          field.Type,
			Formula:            field.Formula,
			FormulaMode:        field.FormulaMode,
			FormulaStorageMode: field.FormulaStorageMode,
			HasFormula:         strings.TrimSpace(field.Formula) != "",
			HasReturnType:      strings.TrimSpace(field.ReturnType) != "",
		})
		if err != nil {
			t.Fatalf("formulastorage.Resolve(%s) error = %v", field.APIName, err)
		}
		if resolved.RequiresFormulaView() {
			formulas = append(formulas, FormulaDefinition{
				Name:       field.APIName,
				Expression: field.Formula,
				ReturnType: field.ReturnType,
			})
			continue
		}
		baseColumns = append(baseColumns, field.APIName)
	}
	if len(formulas) != 2 {
		t.Fatalf("active formula count = %d, want 2", len(formulas))
	}

	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    objects[0].APIName,
		PhysicalTable: strings.ToLower(objects[0].APIName),
		TableAlias:    "t",
		BaseColumns:   baseColumns,
		Formulas:      formulas,
	})
	if err != nil {
		t.Fatalf("BuildViewDDL(active-formula-smoke) error = %v", err)
	}
	for _, want := range []string{"CREATE OR REPLACE", "`NameEcho`", "`NameOrFallback`", "IFNULL(NULLIF(t.`Name`, ''), 'Untitled')"} {
		if !strings.Contains(ddl, want) {
			t.Fatalf("active formula smoke DDL missing %s:\n%s", want, ddl)
		}
	}
}

func TestBuildViewDDLActiveFormulaIsblankOnDateAndNumericEmitsTypeAwareGuard(t *testing.T) {
	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "SyntheticFormula",
		PhysicalTable: "synthetic_formula",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Date_Field__c", "Months_Field__c"},
		FieldTypes: map[string]string{
			"Date_Field__c":   string(constants.FieldTypeDate),
			"Months_Field__c": string(constants.FieldTypeDouble),
		},
		Formulas: []FormulaDefinition{
			{
				Name:       "Derived_End_Date__c",
				Expression: "IF(AND(NOT(ISBLANK(Date_Field__c)), NOT(ISBLANK(Months_Field__c))), __DATE_ADD_MONTHS__(Date_Field__c, ROUND(Months_Field__c, 0)) - 1, null)",
				ReturnType: string(constants.FieldTypeDate),
			},
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}

	// Both guards keep the null-only shape (no empty-string arm). The date
	// operand reads bare; the numeric operand reads through the blank-as-zero
	// coalesce (#4152), which makes its ISBLANK vacuously false — the vendor's
	// BlankAsZero behavior for numeric blank tests.
	for field, want := range map[string]string{
		"Date_Field__c":   "t.`Date_Field__c` IS NULL",
		"Months_Field__c": "COALESCE(t.`Months_Field__c`, 0) IS NULL",
	} {
		pattern := regexp.MustCompile("`" + regexp.QuoteMeta(field) + "`\\s*=\\s*''")
		if pattern.MatchString(ddl) {
			t.Fatalf("DDL contains stale ISBLANK empty-string comparison for %s:\n%s", field, ddl)
		}
		if !strings.Contains(ddl, want) {
			t.Fatalf("DDL missing type-aware IS NULL guard %q:\n%s", want, ddl)
		}
	}
}

func TestBuildViewDDLActivePercentFormulaRestoresStorageDomain(t *testing.T) {
	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "SyntheticFormula",
		PhysicalTable: "synthetic_formula",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "ListPrice", "UnitNetPrice"},
		FieldTypes: map[string]string{
			"ListPrice":    string(constants.FieldTypeCurrency),
			"UnitNetPrice": string(constants.FieldTypeCurrency),
		},
		Formulas: []FormulaDefinition{
			{
				Name:       "DiscountPct",
				Expression: "(ListPrice - UnitNetPrice) / ListPrice",
				ReturnType: string(constants.FieldTypePercent),
			},
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}

	if !strings.Contains(ddl, "/ COALESCE(t.`ListPrice`, 0))) * 100 AS `DiscountPct`") {
		t.Fatalf("percent-returning formula is not restored to storage domain:\n%s", ddl)
	}
}

func TestBuildViewDDLActivePercentFieldReadsUseFormulaDomain(t *testing.T) {
	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "SyntheticFormula",
		PhysicalTable: "synthetic_formula",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Probability"},
		FieldTypes: map[string]string{
			"Probability": string(constants.FieldTypePercent),
		},
		Formulas: []FormulaDefinition{
			{
				Name:       "WeightedScore",
				Expression: "Probability * 1.5",
				ReturnType: string(constants.FieldTypeDouble),
			},
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}

	if !strings.Contains(ddl, "(COALESCE(t.`Probability`, 0) / 100.0) * 1.5") {
		t.Fatalf("percent field read is not normalized to formula domain:\n%s", ddl)
	}
	if strings.Contains(ddl, "* 100 AS `WeightedScore`") {
		t.Fatalf("non-percent formula output was restored as percent:\n%s", ddl)
	}
}

func TestBuildViewDDLChainedActivePercentFormulaRoundTripsDomain(t *testing.T) {
	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "SyntheticFormula",
		PhysicalTable: "synthetic_formula",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Probability"},
		FieldTypes: map[string]string{
			"Probability": string(constants.FieldTypePercent),
		},
		Formulas: []FormulaDefinition{
			{
				Name:       "BaseProbability",
				Expression: "Probability",
				ReturnType: string(constants.FieldTypePercent),
			},
			{
				Name:       "AdjustedProbability",
				Expression: "BaseProbability * 1.5",
				ReturnType: string(constants.FieldTypePercent),
			},
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}

	for _, want := range []string{
		"COALESCE(t.`Probability`, 0) / 100.0",
		"COALESCE(L0.`BaseProbability`, 0) / 100.0",
		"* 100 AS `AdjustedProbability`",
	} {
		if !strings.Contains(ddl, want) {
			t.Fatalf("chained percent DDL missing %q:\n%s", want, ddl)
		}
	}
}

func TestBuildViewDDLOuterLayerCrossRefPercentPassThroughUsesFormulaDomain(t *testing.T) {
	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "Child",
		PhysicalTable: "child",
		TableAlias:    "c",
		BaseColumns:   []string{"Id", "ParentId"},
		FieldTypes: map[string]string{
			"Parent.Score": string(constants.FieldTypePercent),
		},
		Formulas: []FormulaDefinition{
			{
				Name:       "BaseScore",
				Expression: "1",
				ReturnType: string(constants.FieldTypeDouble),
			},
			{
				Name:       "ParentWeightedScore",
				Expression: "BaseScore + Parent.Score",
				ReturnType: string(constants.FieldTypeDouble),
			},
		},
		Joins: []JoinSpec{
			{
				Alias:            "j_parent",
				TargetObject:     "Parent",
				TargetTable:      "parent",
				SourceField:      "ParentId",
				TargetField:      "Id",
				RelationshipName: "Parent",
			},
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}

	if !strings.Contains(ddl, "j_parent.`Score` AS `_xref_Parent_Score`") {
		t.Fatalf("DDL does not pass cross-ref field through inner layer:\n%s", ddl)
	}
	if !strings.Contains(ddl, "COALESCE(L0.`_xref_Parent_Score`, 0) / 100.0") {
		t.Fatalf("outer-layer cross-ref pass-through percent is not normalized:\n%s", ddl)
	}
}

func TestBuildMultiLayerViewResolvesCrossRefLogicalNameAliases(t *testing.T) {
	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "WorkItem",
		PhysicalTable: "workitem",
		TableAlias:    "wi",
		BaseColumns:   []string{"Id", "Project__c"},
		Formulas: []FormulaDefinition{
			{
				Name:       "L0ProjectName",
				Expression: "Project__r.Name",
				ReturnType: "Text",
			},
			{
				Name:       "L1CombinedName",
				Expression: "L0ProjectName & Project__r.Name",
				ReturnType: "Text",
			},
		},
		Joins: []JoinSpec{
			{
				Alias:            "j_project",
				TargetTable:      "Project__c",
				SourceField:      "Project__c",
				TargetField:      "Id",
				RelationshipName: "Project__r",
			},
		},
		CrossRefFieldAliases: map[string]string{
			"Project__r.Name": "DisplayName__c",
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}

	if !strings.Contains(ddl, "j_project.`DisplayName__c` AS `L0ProjectName`") {
		t.Fatalf("DDL does not resolve L0 cross-ref Project__r.Name to DisplayName__c:\n%s", ddl)
	}
	if !strings.Contains(ddl, "j_project.`DisplayName__c` AS `_xref_Project__r_Name`") {
		t.Fatalf("DDL does not resolve L1 pass-through Project__r.Name to DisplayName__c:\n%s", ddl)
	}
	if strings.Contains(ddl, "j_project.`Name`") {
		t.Fatalf("DDL still selects logical Project__r.Name from the joined table:\n%s", ddl)
	}
}

func TestBuildViewDDLRequiresMetadataJoinForCrossRef(t *testing.T) {
	_, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "Issue",
		PhysicalTable: "issue",
		TableAlias:    "i",
		BaseColumns:   []string{"Id", "ProjectRef__c"},
		Formulas: []FormulaDefinition{
			{
				Name:       "ProjectName__c",
				Expression: "Project__r.Name",
				ReturnType: "Text",
			},
		},
	})
	if err == nil {
		t.Fatal("BuildViewDDL() error = nil, want missing metadata join failure")
	}
	if !strings.Contains(err.Error(), "Issue.ProjectName__c") {
		t.Fatalf("BuildViewDDL() error = %q, want object+field context", err.Error())
	}
	if !strings.Contains(err.Error(), "unresolved cross-reference Project__r.Name") {
		t.Fatalf("BuildViewDDL() error = %q, want unresolved Project__r.Name", err.Error())
	}
}

func TestBuildViewDDLUsesDeclaredCustomRelationshipJoin(t *testing.T) {
	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "Issue",
		PhysicalTable: "issue",
		TableAlias:    "i",
		BaseColumns:   []string{"Id", "ProjectRef__c"},
		Formulas: []FormulaDefinition{
			{
				Name:       "ProjectName__c",
				Expression: "Project__r.Name",
				ReturnType: "Text",
			},
		},
		Joins: []JoinSpec{
			{
				Alias:            "j_project",
				TargetTable:      "Project__c",
				SourceField:      "ProjectRef__c",
				TargetField:      "Id",
				RelationshipName: "Project__r",
			},
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}
	if !strings.Contains(ddl, "j_project.`Name` AS `ProjectName__c`") {
		t.Fatalf("DDL does not project through declared relationship join:\n%s", ddl)
	}
	if strings.Contains(ddl, "Project__rId") {
		t.Fatalf("DDL inferred relationship source field from object name:\n%s", ddl)
	}
}

func TestBuildViewDDLIssue1301CrossObjectBlankSemantics(t *testing.T) {
	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "Order",
		PhysicalTable: "order",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "OpportunityId", "Partner_Name__c"},
		Formulas: []FormulaDefinition{
			{
				Name:       "Sales_Channel_Type__c",
				Expression: `IF(TEXT(Opportunity.Sales_Channel_Type__c) = "", IF(Partner_Name__c = "", "Direct", "Indirect"), TEXT(Opportunity.Sales_Channel_Type__c))`,
				ReturnType: "Text",
			},
		},
		Joins: []JoinSpec{
			{
				Alias:            "j_opp",
				TargetTable:      "Opportunity",
				SourceField:      "OpportunityId",
				TargetField:      "Id",
				RelationshipName: "Opportunity",
			},
		},
		FieldTypes: map[string]string{
			"Partner_Name__c":                   "reference",
			"Opportunity.Sales_Channel_Type__c": "picklist",
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}

	wantParts := []string{
		"CASE WHEN (IFNULL(COALESCE(j_opp.`Sales_Channel_Type__c`, '') = '', TRUE)) THEN",
		"CASE WHEN (IFNULL(t.`Partner_Name__c` = '', TRUE)) THEN 'Direct' ELSE 'Indirect' END",
		"ELSE COALESCE(j_opp.`Sales_Channel_Type__c`, '') END AS `Sales_Channel_Type__c`",
	}
	for _, want := range wantParts {
		if !strings.Contains(ddl, want) {
			t.Fatalf("BuildViewDDL() missing %q in DDL:\n%s", want, ddl)
		}
	}
}

func TestFindFKForRelationshipTreatsMasterDetailAsMetadataJoinSource(t *testing.T) {
	relName := "Parent__r"
	schema := &models.ObjectMetadata{
		APIName: "Child__c",
		Fields: []models.FieldMetadata{
			{
				APIName:          "Parent__c",
				Type:             constants.FieldTypeMasterDetail,
				ReferenceTo:      []string{"Parent__c"},
				RelationshipName: &relName,
			},
		},
	}

	fkField, targetObj := findFKForRelationship(schema, "Parent__r")
	if fkField != "Parent__c" || targetObj != "Parent__c" {
		t.Fatalf("findFKForRelationship() = (%q, %q), want (Parent__c, Parent__c)", fkField, targetObj)
	}
}

func TestRelationshipJoinSpecForFieldBuildsMasterDetailCrossRefJoin(t *testing.T) {
	relName := "Parent__r"
	field := models.FieldMetadata{
		APIName:          "Parent__c",
		Type:             constants.FieldTypeMasterDetail,
		ReferenceTo:      []string{"Parent__c"},
		RelationshipName: &relName,
	}

	join, ok := relationshipJoinSpecForField(field, map[string]bool{})
	if !ok {
		t.Fatal("relationshipJoinSpecForField() ok = false, want true")
	}

	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "Child__c",
		PhysicalTable: "child__c",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Parent__c"},
		Formulas: []FormulaDefinition{
			{
				Name:       "CopiedParentValue__c",
				Expression: "Parent__r.CustomField__c",
				ReturnType: "Text",
			},
		},
		Joins: []JoinSpec{join},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}
	if !strings.Contains(ddl, "LEFT JOIN `Parent__c` j_parent__r ON t.`Parent__c` = j_parent__r.`Id`") {
		t.Fatalf("DDL does not include master-detail metadata join:\n%s", ddl)
	}
	if !strings.Contains(ddl, "j_parent__r.`CustomField__c` AS `CopiedParentValue__c`") {
		t.Fatalf("DDL does not compile Parent__r.CustomField__c through metadata join:\n%s", ddl)
	}
}

func TestResolveAggregateMarkersByLogicalChildObject(t *testing.T) {
	agg := &AggregateExpr{
		Func:        "SUM",
		ChildObject: "WorkLog__c",
		ChildField:  "Hours__c",
	}
	markerSQL, err := compileAggregate(agg, nil)
	if err != nil {
		t.Fatalf("compileAggregate() error = %v", err)
	}

	spec := AggregateJoinSpec{
		Alias:       "rsq0",
		ChildObject: "WorkLog__c",
		ChildTable:  "worklog__c",
		AggFunc:     "SUM",
		AggField:    "Hours__c",
		ForeignKey:  "ParentIssue__c",
		ParentKey:   "Id",
		OutputAlias: "agg_0",
	}
	join, ok := findAggregateJoinForMarker(aggregateMarkerFromExpr(*agg), []AggregateJoinSpec{spec})
	if !ok {
		t.Fatal("findAggregateJoinForMarker() did not resolve logical child object marker")
	}
	if join.ChildTable != "worklog__c" {
		t.Fatalf("join.ChildTable = %q, want worklog__c", join.ChildTable)
	}

	sql, err := NewDSLViewBuilder().resolveAggregateMarkers(
		markerSQL+" AS `TotalHours__c`",
		ViewBuildConfig{AggJoins: []AggregateJoinSpec{spec}},
		aggregateJoinReference,
	)
	if err != nil {
		t.Fatalf("resolveAggregateMarkers() error = %v", err)
	}
	if strings.Contains(sql, AggMarkerPrefix) {
		t.Fatalf("resolved SQL still contains aggregate marker:\n%s", sql)
	}
	// SUM aggregates read through the blank-as-zero coalesce (#4152): a
	// childless parent's subquery miss reads 0, matching stored-rollup reads.
	if !strings.Contains(sql, "COALESCE(rsq0.`agg_0`, 0) AS `TotalHours__c`") {
		t.Fatalf("resolved SQL does not project the matched aggregate alias:\n%s", sql)
	}
}

func TestResolveAggregateMarkersDoesNotCrossReplaceFields(t *testing.T) {
	hoursMarker, err := compileAggregate(&AggregateExpr{
		Func:        "SUM",
		ChildObject: "WorkLog__c",
		ChildField:  "Hours__c",
	}, nil)
	if err != nil {
		t.Fatalf("compileAggregate(hours) error = %v", err)
	}
	costMarker, err := compileAggregate(&AggregateExpr{
		Func:        "SUM",
		ChildObject: "WorkLog__c",
		ChildField:  "Cost__c",
	}, nil)
	if err != nil {
		t.Fatalf("compileAggregate(cost) error = %v", err)
	}

	sql, err := NewDSLViewBuilder().resolveAggregateMarkers(
		hoursMarker+" AS `TotalHours__c`, "+costMarker+" AS `TotalCost__c`",
		ViewBuildConfig{AggJoins: []AggregateJoinSpec{
			{
				Alias:       "rsq0",
				ChildObject: "WorkLog__c",
				ChildTable:  "worklog__c",
				AggFunc:     "SUM",
				AggField:    "Hours__c",
				ForeignKey:  "ParentIssue__c",
				ParentKey:   "Id",
				OutputAlias: "agg_0",
			},
			{
				Alias:       "rsq1",
				ChildObject: "WorkLog__c",
				ChildTable:  "worklog__c",
				AggFunc:     "SUM",
				AggField:    "Cost__c",
				ForeignKey:  "ParentIssue__c",
				ParentKey:   "Id",
				OutputAlias: "agg_1",
			},
		}},
		aggregateJoinReference,
	)
	if err != nil {
		t.Fatalf("resolveAggregateMarkers() error = %v", err)
	}
	if !strings.Contains(sql, "COALESCE(rsq0.`agg_0`, 0) AS `TotalHours__c`") {
		t.Fatalf("resolved SQL does not map Hours marker to rsq0:\n%s", sql)
	}
	if !strings.Contains(sql, "COALESCE(rsq1.`agg_1`, 0) AS `TotalCost__c`") {
		t.Fatalf("resolved SQL does not map Cost marker to rsq1:\n%s", sql)
	}
	if strings.Contains(sql, "COALESCE(rsq0.`agg_0`, 0) AS `TotalCost__c`") {
		t.Fatalf("resolved SQL cross-replaced Cost marker with Hours alias:\n%s", sql)
	}
}

func TestResolveAggregateMarkersRejectsUnmatchedMarker(t *testing.T) {
	costMarker, err := compileAggregate(&AggregateExpr{
		Func:        "SUM",
		ChildObject: "WorkLog__c",
		ChildField:  "Cost__c",
	}, nil)
	if err != nil {
		t.Fatalf("compileAggregate() error = %v", err)
	}

	_, err = NewDSLViewBuilder().resolveAggregateMarkers(
		costMarker,
		ViewBuildConfig{AggJoins: []AggregateJoinSpec{{
			Alias:       "rsq0",
			ChildObject: "WorkLog__c",
			ChildTable:  "worklog__c",
			AggFunc:     "SUM",
			AggField:    "Hours__c",
			ForeignKey:  "ParentIssue__c",
			ParentKey:   "Id",
			OutputAlias: "agg_0",
		}}},
		aggregateJoinReference,
	)
	if err == nil {
		t.Fatal("resolveAggregateMarkers() error = nil, want unresolved aggregate marker failure")
	}
	if !strings.Contains(err.Error(), "unresolved aggregate marker") {
		t.Fatalf("resolveAggregateMarkers() error = %q, want unresolved aggregate marker", err.Error())
	}
}

// TestBuildViewDDLIsAppNeutralForCRMAndNonCRMObjects locks in issue #69
// acceptance criterion 5: the formula/view DDL builder must produce
// structurally equivalent output for a CRM-named object (e.g. "Account") and
// a non-CRM app-pack object (e.g. "Issue") given equivalent metadata.
// Reintroducing a hardcoded standard-object branch — the kind removed by
// PR #80 (formula alias literals) and PR #83 (formula_view_translator
// isStandardObject whitelist) — would make the two DDLs diverge.
func TestBuildViewDDLIsAppNeutralForCRMAndNonCRMObjects(t *testing.T) {
	cases := []struct {
		label         string
		objectName    string
		physicalTable string
	}{
		{"CRM-named object", "Account", "account"},
		{"non-CRM app-pack object", "Issue", "issue"},
	}

	ddls := make([]string, len(cases))
	for i, c := range cases {
		ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
			ObjectName:    c.objectName,
			PhysicalTable: c.physicalTable,
			TableAlias:    "t",
			BaseColumns:   []string{"Id", "Owner__c"},
			Formulas: []FormulaDefinition{
				{
					Name:       "OwnerName__c",
					Expression: "Owner__r.Name",
					ReturnType: "Text",
				},
			},
			Joins: []JoinSpec{
				{
					Alias:            "j_owner",
					TargetTable:      "user__c",
					SourceField:      "Owner__c",
					TargetField:      "Id",
					RelationshipName: "Owner__r",
				},
			},
		})
		if err != nil {
			t.Fatalf("[%s] BuildViewDDL() error = %v", c.label, err)
		}
		ddls[i] = ddl
	}

	normalize := func(ddl, objectName, physicalTable string) string {
		ddl = strings.ReplaceAll(ddl, objectName, "<OBJ>")
		ddl = strings.ReplaceAll(ddl, physicalTable, "<obj>")
		return ddl
	}
	crm := normalize(ddls[0], cases[0].objectName, cases[0].physicalTable)
	nonCRM := normalize(ddls[1], cases[1].objectName, cases[1].physicalTable)
	if crm != nonCRM {
		t.Fatalf("formula view DDL builder is not app-neutral; CRM and non-CRM produce different shapes.\nCRM (Account) DDL:\n%s\n\nnon-CRM (Issue) DDL:\n%s", ddls[0], ddls[1])
	}
}

func TestResolveAggregateMarkersSupportsPassThroughAlias(t *testing.T) {
	markerSQL, err := compileAggregate(&AggregateExpr{
		Func:        "SUM",
		ChildObject: "WorkLog__c",
		ChildField:  "Hours__c",
	}, nil)
	if err != nil {
		t.Fatalf("compileAggregate() error = %v", err)
	}

	spec := AggregateJoinSpec{
		Alias:       "rsq0",
		ChildObject: "WorkLog__c",
		ChildTable:  "worklog__c",
		AggFunc:     "SUM",
		AggField:    "Hours__c",
		ForeignKey:  "ParentIssue__c",
		ParentKey:   "Id",
		OutputAlias: "agg_0",
	}
	sql, err := NewDSLViewBuilder().resolveAggregateMarkers(
		"BaseEstimate__c + "+markerSQL+" AS `TotalWork__c`",
		ViewBuildConfig{AggJoins: []AggregateJoinSpec{spec}},
		func(aj AggregateJoinSpec) string {
			return "L0.`" + aggregatePassThroughAlias(aj) + "`"
		},
	)
	if err != nil {
		t.Fatalf("resolveAggregateMarkers() error = %v", err)
	}
	if strings.Contains(sql, AggMarkerPrefix) {
		t.Fatalf("resolved SQL still contains aggregate marker:\n%s", sql)
	}
	if !strings.Contains(sql, "L0.`_agg_rsq0_agg_0`") || !strings.Contains(sql, "AS `TotalWork__c`") {
		t.Fatalf("resolved SQL does not route the aggregate through the pass-through alias:\n%s", sql)
	}
}

// TestBuildViewDDL_AggregateJoinHasIsDeletedGuard verifies that the single-layer
// aggregate LEFT JOIN subquery injects WHERE `IsDeleted` = 0 between FROM and GROUP BY,
// aligning view-aggregate semantics with the rollup engine which applies the same filter
// (rollup_repository.go:113,175).
func TestBuildViewDDL_AggregateJoinHasIsDeletedGuard(t *testing.T) {
	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "Issue",
		PhysicalTable: "issue",
		TableAlias:    "i",
		BaseColumns:   []string{"Id"},
		Formulas: []FormulaDefinition{
			{
				Name:       "EchoId__c",
				Expression: "Id",
				ReturnType: "Text",
			},
		},
		AggJoins: []AggregateJoinSpec{
			{
				Alias:       "rsq0",
				ChildObject: "WorkLog__c",
				ChildTable:  "worklog__c",
				AggFunc:     "SUM",
				AggField:    "Hours__c",
				ForeignKey:  "ParentIssue__c",
				ParentKey:   "Id",
				OutputAlias: "agg_0",
			},
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}
	// The subquery must exclude soft-deleted child records.
	want := "FROM `worklog__c`\n  WHERE `" + constants.ColIsDeleted + "` = 0\n  GROUP BY"
	if !strings.Contains(ddl, want) {
		t.Fatalf("single-layer aggregate subquery missing IsDeleted guard.\nwant substring: %q\ngot DDL:\n%s", want, ddl)
	}
}

// TestBuildViewDDL_MultiLayerAggregateJoinHasIsDeletedGuard verifies that the
// multi-layer aggregate LEFT JOIN subquery (inner layer of a nested DDL) also
// injects WHERE `IsDeleted` = 0 between FROM and GROUP BY.
func TestBuildViewDDL_MultiLayerAggregateJoinHasIsDeletedGuard(t *testing.T) {
	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "Issue",
		PhysicalTable: "issue",
		TableAlias:    "i",
		BaseColumns:   []string{"Id", "Estimate__c"},
		Formulas: []FormulaDefinition{
			{
				Name:       "BaseEstimate__c",
				Expression: "Estimate__c * 2",
				ReturnType: "Double",
			},
			{
				Name:       "TotalWork__c",
				Expression: "BaseEstimate__c + 1",
				ReturnType: "Double",
			},
		},
		AggJoins: []AggregateJoinSpec{
			{
				Alias:       "rsq0",
				ChildObject: "WorkLog__c",
				ChildTable:  "worklog__c",
				AggFunc:     "SUM",
				AggField:    "Hours__c",
				ForeignKey:  "ParentIssue__c",
				ParentKey:   "Id",
				OutputAlias: "agg_0",
			},
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}
	// Multi-layer DDL wraps the inner layer in additional subquery context, so
	// we match the guard independent of surrounding whitespace.
	if !strings.Contains(ddl, "FROM `worklog__c`") {
		t.Fatalf("multi-layer DDL does not reference child table worklog__c:\n%s", ddl)
	}
	guardClause := "WHERE `" + constants.ColIsDeleted + "` = 0"
	if !strings.Contains(ddl, guardClause) {
		t.Fatalf("multi-layer aggregate subquery missing IsDeleted guard.\nwant substring: %q\ngot DDL:\n%s", guardClause, ddl)
	}
	// Verify the guard appears before GROUP BY in the DDL (order check).
	fromIdx := strings.Index(ddl, "FROM `worklog__c`")
	guardIdx := strings.Index(ddl, guardClause)
	groupByIdx := strings.Index(ddl[fromIdx:], "GROUP BY")
	guardRelIdx := guardIdx - fromIdx
	if guardRelIdx < 0 || guardRelIdx > groupByIdx {
		t.Fatalf("IsDeleted guard does not appear between FROM and GROUP BY in multi-layer DDL:\n%s", ddl)
	}
}

// TestBuildViewDDL_PhysicalNameAliasedInSingleLayer verifies that single-layer DDL
// (all formulas depend only on base fields) also applies BaseColumnAliases.
func TestBuildViewDDL_PhysicalNameAliasedInSingleLayer(t *testing.T) {
	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "Order",
		PhysicalTable: "order",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Name"},
		BaseColumnAliases: map[string]string{
			"Name": "OrderNumber",
		},
		Formulas: []FormulaDefinition{
			{
				Name:       "Label__c",
				Expression: "Name",
				ReturnType: "Text",
			},
		},
	})
	if err != nil {
		t.Fatalf("single-layer BuildViewDDL error: %v", err)
	}
	if !strings.Contains(ddl, "t.`OrderNumber` AS `Name`") {
		t.Errorf("single-layer DDL must alias physical column: t.`OrderNumber` AS `Name`\nDDL:\n%s", ddl)
	}
	if strings.Contains(ddl, "t.`Name`") {
		t.Errorf("single-layer DDL must not emit t.`Name` when PhysicalName=OrderNumber\nDDL:\n%s", ddl)
	}
}

// TestBuildViewDDL_PhysicalNameAliasedInL0AndOuterLayerUsesLogical is a regression
// test for the PhysicalName base-column aliasing fix (scope item 2, Test F).
//
// When a field has APIName="Name" but PhysicalName="OrderNumber", L0 must emit
// t.`OrderNumber` AS `Name` so that the outer projection (L1+) can reference the
// logical alias L0.`Name` instead of the non-existent L0.`OrderNumber`.
func TestBuildViewDDL_PhysicalNameAliasedInL0AndOuterLayerUsesLogical(t *testing.T) {
	// L0 formula computes something simple; L1 depends on it AND references bare Name.
	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "Order",
		PhysicalTable: "order",
		TableAlias:    "t",
		// "Name" is the API name; "OrderNumber" is the physical DB column.
		BaseColumns: []string{"Id", "Name"},
		BaseColumnAliases: map[string]string{
			"Name": "OrderNumber",
		},
		Formulas: []FormulaDefinition{
			{
				Name:       "TaxRate__c",
				Expression: "0.1",
				ReturnType: "Number",
			},
			{
				// L1 formula: depends on TaxRate__c (L0) and also references Name.
				Name:       "DisplayLabel__c",
				Expression: "TaxRate__c & Name",
				ReturnType: "Text",
				ExtraDeps:  []string{"TaxRate__c"},
			},
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL error: %v", err)
	}

	// L0 must alias the physical column to the logical name.
	if !strings.Contains(ddl, "t.`OrderNumber` AS `Name`") {
		t.Errorf("L0 base column must emit physical alias: t.`OrderNumber` AS `Name`\nDDL:\n%s", ddl)
	}

	// L0 must NOT emit the bare logical name as a column reference.
	if strings.Contains(ddl, "t.`Name`") {
		t.Errorf("L0 must not emit t.`Name` when PhysicalName=OrderNumber; got:\n%s", ddl)
	}

	// Outer layer (L1) must reference L0.`Name` (the L0 alias), not L0.`OrderNumber`.
	if strings.Contains(ddl, "L0.`OrderNumber`") {
		t.Errorf("outer layer must not reference L0.`OrderNumber`; should use L0.`Name`\nDDL:\n%s", ddl)
	}
	if !strings.Contains(ddl, "L0.`Name`") {
		t.Errorf("outer layer must reference L0.`Name`\nDDL:\n%s", ddl)
	}
}

// TestBuildViewDDL_BacktickedPhysicalNameIsIgnoredNotForwarded is a regression test
// for the bootstrap metadata shape where PhysicalName is stored as a backtick-wrapped
// alias (e.g. "`RecipientId`") rather than a raw column name.
//
// Per persistence.actualColumnName semantics: backtick-wrapped PhysicalName means
// "the DB column IS the API name"; it must NOT be forwarded as a physical alias.
// Without this guard, the emitted SQL would be t.“RecipientId“ (double-backtick),
// which is invalid and would break active-formula view rebuild for any such object.
func TestBuildViewDDL_BacktickedPhysicalNameIsIgnoredNotForwarded(t *testing.T) {
	// Simulate a bootstrap metadata field with a backtick-wrapped PhysicalName.
	// analyticsPhysicalColumnName must detect this and treat it as "use the API name".
	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "Notification",
		PhysicalTable: "notification",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "RecipientId"},
		// Bootstrap metadata sometimes stores aliases as backtick-wrapped strings.
		BaseColumnAliases: map[string]string{
			// This should be ignored by the view builder — it means "no physical alias".
			// Callers that follow the SSoT (analyticsPhysicalColumnName) will never put
			// a backtick-wrapped value here; this test verifies the view builder is robust
			// even if a stale caller passes one through.
		},
		Formulas: []FormulaDefinition{
			{
				Name:       "Display__c",
				Expression: "RecipientId",
				ReturnType: "Text",
			},
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL error with bootstrap-style backtick PhysicalName: %v", err)
	}
	// No double-backtick sequences must appear in the DDL.
	if strings.Contains(ddl, "``") {
		t.Errorf("DDL must not contain double-backtick sequences (invalid SQL from raw backtick-wrapped PhysicalName):\n%s", ddl)
	}
	// Column must appear as plain t.`RecipientId`, not aliased.
	if !strings.Contains(ddl, "t.`RecipientId`") {
		t.Errorf("DDL must contain t.`RecipientId` as a plain base column:\n%s", ddl)
	}
}

// TestBuildViewDDLActiveFormulaCaseInsideNestedIfAndCrossFormulaReference is
// the regression guard for the pattern reported as "CASE() not implemented"
// where a multi-layer active formula uses CASE(...) as a sub-expression inside
// nested IF / AND / OR and references another active formula on the same
// object.
//
// Three properties are locked here:
//
//  1. CASE compiles to standard SQL CASE/WHEN/ELSE/END inside the outer-layer
//     formula expression.
//  2. A dependent formula that references a sibling active formula resolves
//     to the inner-layer alias (L0.<sibling>), not the physical table.
//  3. The CASE result type merges cleanly with the surrounding numeric
//     comparison so the outer IF still classifies as a string-returning
//     branch — i.e., the column does not silently degrade to NULL.
//
// The structural shape mirrors a real production-style formula that combines
// currency normalization (CASE), threshold comparison (>=), short-circuit
// boolean composition (AND / OR / IF), and a derived classification field
// referencing an upstream computed flag.
func TestBuildViewDDLActiveFormulaCaseInsideNestedIfAndCrossFormulaReference(t *testing.T) {
	classifierFlag := `IF(OR(ISPICKVAL(Category__c, "Alpha"), ISPICKVAL(Category__c, "Beta")), "Yes", "No")`
	tierFormula := `IF(
        OR(
            AND(
                CASE(Currency__c,
                    "JPY", Revenue__c/147.6,
                    "CNY", Revenue__c/7.27,
                    Revenue__c) >= IF(ISPICKVAL(Tag__c, "Hot"), 500000000, 1000000000),
                Headcount__c >= IF(ISPICKVAL(Tag__c, "Hot"), 500, 1000),
                Flag__c = "Yes"
            ),
            MonthlyAmount__c >= IF(ISPICKVAL(Tag__c, "Hot"), 500000, 1000000)
        ),
        "S",
        IF(
            OR(
                AND(
                    OR(
                        CASE(Currency__c,
                            "JPY", Revenue__c/147.6,
                            "CNY", Revenue__c/7.27,
                            Revenue__c) >= IF(ISPICKVAL(Tag__c, "Hot"), 50000000, 100000000),
                        Headcount__c >= IF(ISPICKVAL(Tag__c, "Hot"), 125, 250)
                    ),
                    Flag__c = "Yes"
                ),
                MonthlyAmount__c >= IF(ISPICKVAL(Tag__c, "Hot"), 125000, 250000)
            ),
            "M",
            "L"
        )
    )`

	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "SyntheticTier",
		PhysicalTable: "synthetic_tier",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Currency__c", "Revenue__c", "Headcount__c", "MonthlyAmount__c", "Category__c", "Tag__c"},
		FieldTypes: map[string]string{
			"Currency__c":      "string",
			"Revenue__c":       "currency",
			"Headcount__c":     "double",
			"MonthlyAmount__c": "currency",
			"Category__c":      "picklist",
			"Tag__c":           "picklist",
			"Flag__c":          "string",
		},
		Formulas: []FormulaDefinition{
			{Name: "Flag__c", Expression: classifierFlag, ReturnType: "Text"},
			{Name: "Tier__c", Expression: tierFormula, ReturnType: "Text"},
		},
		FunctionExecutables: testISPICKVALFunctionExecutables(t, "value == expected"),
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}

	// L0 must materialize the upstream Flag__c so the dependent Tier__c can
	// read it as a column reference.
	if !strings.Contains(ddl, "AS `Flag__c`") {
		t.Errorf("DDL missing L0 projection `AS \\`Flag__c\\``:\n%s", ddl)
	}
	if !strings.Contains(ddl, "L0.`Flag__c`") && !strings.Contains(ddl, "`L0`.`Flag__c`") {
		t.Errorf("Tier__c must reference inner-layer L0.`Flag__c`:\n%s", ddl)
	}
	// CASE expression must compile to SQL CASE/WHEN/ELSE/END, not be dropped
	// or substituted with NULL.
	caseFragments := []string{
		"CASE",
		"WHEN 'JPY'",
		"WHEN 'CNY'",
		"ELSE",
		"END",
	}
	for _, frag := range caseFragments {
		if !strings.Contains(ddl, frag) {
			t.Errorf("DDL missing CASE fragment %q (would indicate silent CASE drop):\n%s", frag, ddl)
		}
	}
	// Tier__c output column must be present (regression guard for the failure
	// mode where a formula silently disappears from the projection).
	if !strings.Contains(ddl, "AS `Tier__c`") {
		t.Errorf("DDL missing dependent formula `AS \\`Tier__c\\``:\n%s", ddl)
	}
	// Both string literal branches of the outer/inner IF must reach the
	// compiled SQL, otherwise type-merging dropped a branch.
	for _, branch := range []string{"'S'", "'M'", "'L'"} {
		if !strings.Contains(ddl, branch) {
			t.Errorf("DDL missing IF branch literal %s:\n%s", branch, ddl)
		}
	}
}

// TestBuildViewDDL_LookupValuePKMatchHoistedToJoin verifies that a single-PK-match
// lookupValue projection (match on Id) is emitted as a LEFT JOIN + alias reference
// rather than a correlated scalar subquery. This eliminates the TiDB per-row Apply
// and lets the planner choose an IndexJoin instead.
//
// The ViewBuildConfig already carries HoistedJoinAlias in the LookupValueRule and
// the corresponding JoinSpec in cfg.Joins — this mirrors what buildViewFromDSL
// produces after its hoisting pass.
func TestBuildViewDDL_LookupValuePKMatchHoistedToJoin(t *testing.T) {
	// Simulate PricebookEntry.Name = LOOKUPVALUE("Product2", "Name", "Id", Product2Id)
	// with ExcludeDeleted: true, already hoisted by buildViewFromDSL.
	rule := LookupValueRule{
		TargetTable:      "Product2",
		OutputField:      constants.FieldName,
		ExcludeDeleted:   true,
		HoistedJoinAlias: "lv_product2",
		MatchPairs: []LookupValueMatchRule{
			{MatchField: constants.FieldID, SourceField: "Product2Id"},
		},
	}

	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "PricebookEntry",
		PhysicalTable: "pricebookentry",
		TableAlias:    "t",
		BaseColumns:   []string{constants.FieldID, "Product2Id"},
		Formulas: []FormulaDefinition{
			{
				Name:             constants.FieldName,
				Expression:       `LOOKUPVALUE("Product2", "Name", "Id", Product2Id)`,
				ReturnType:       "Text",
				LookupValueRules: []LookupValueRule{rule},
			},
		},
		Joins: []JoinSpec{
			{
				Alias:               "lv_product2",
				TargetObject:        "Product2",
				TargetTable:         "Product2",
				SourceField:         "Product2Id",
				TargetField:         constants.FieldID,
				FilterTargetDeleted: true,
			},
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}

	// Must contain a LEFT JOIN on the target table.
	if !strings.Contains(ddl, "LEFT JOIN `Product2` lv_product2") {
		t.Errorf("DDL missing expected LEFT JOIN on Product2:\n%s", ddl)
	}
	// The ON clause must include the IsDeleted soft-delete predicate.
	isDeletedPredicate := "AND (lv_product2.`" + constants.ColIsDeleted + "` = 0 OR lv_product2.`" + constants.ColIsDeleted + "` IS NULL)"
	if !strings.Contains(ddl, isDeletedPredicate) {
		t.Errorf("DDL missing IsDeleted predicate in ON clause:\nwant substring: %q\ngot DDL:\n%s", isDeletedPredicate, ddl)
	}
	// Must NOT contain a correlated scalar subquery for this field.
	if strings.Contains(ddl, "(SELECT lv.`"+constants.FieldName+"` FROM `Product2` lv") {
		t.Errorf("DDL contains correlated subquery for a PK-match lookupValue (should be JOIN):\n%s", ddl)
	}
	// The projected column must reference the alias directly.
	if !strings.Contains(ddl, "lv_product2.`"+constants.FieldName+"`") {
		t.Errorf("DDL missing alias column reference lv_product2.Name:\n%s", ddl)
	}
}

// TestBuildViewDDL_LookupValueNonPKMatchKeepsSubquery verifies that a multi-pair
// non-PK lookupValue (e.g. CampaignMemberStatus matched on CampaignId + Label)
// still emits the correlated scalar subquery form unchanged.
func TestBuildViewDDL_LookupValueNonPKMatchKeepsSubquery(t *testing.T) {
	rule := LookupValueRule{
		TargetTable:    "StatusCatalog__c",
		OutputField:    "IsTerminal__c",
		ExcludeDeleted: true,
		MatchPairs: []LookupValueMatchRule{
			{MatchField: "WorkflowId__c", SourceField: "WorkflowId__c"},
			{MatchField: "Label__c", SourceField: "Status__c"},
		},
	}

	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "WorkItem",
		PhysicalTable: "workitem",
		TableAlias:    "t",
		BaseColumns:   []string{constants.FieldID, "WorkflowId__c", "Status__c"},
		Formulas: []FormulaDefinition{
			{
				Name:             "IsTerminal__c",
				Expression:       `LOOKUPVALUE("StatusCatalog__c", "IsTerminal__c", "WorkflowId__c", WorkflowId__c, "Label__c", Status__c)`,
				ReturnType:       "Boolean",
				LookupValueRules: []LookupValueRule{rule},
			},
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}

	// Must still use the correlated scalar subquery (non-PK multi-match).
	if !strings.Contains(ddl, "(SELECT lv.`IsTerminal__c` FROM `StatusCatalog__c` lv") {
		t.Errorf("DDL missing correlated subquery for non-PK-match lookupValue:\n%s", ddl)
	}
	// Must NOT contain a LEFT JOIN for StatusCatalog (no hoisting expected).
	if strings.Contains(ddl, "LEFT JOIN `StatusCatalog__c`") {
		t.Errorf("DDL contains unexpected LEFT JOIN for non-PK-match lookupValue:\n%s", ddl)
	}
}

// TestBuildViewDDL_LookupValueTwoPKMatchesSameTargetDistinctAliases verifies that
// two PK-match lookupValues targeting the same object get distinct lv_-prefixed aliases
// so the DDL is valid (no "Not unique alias" error in TiDB).
// The test pre-sets HoistedJoinAlias on each rule, mirroring what buildViewFromDSL
// produces via makeJoinAlias collision-avoidance.
func TestBuildViewDDL_LookupValueTwoPKMatchesSameTargetDistinctAliases(t *testing.T) {
	rule1 := LookupValueRule{
		TargetTable:      "Product2",
		OutputField:      constants.FieldName,
		HoistedJoinAlias: "lv_product2",
		MatchPairs:       []LookupValueMatchRule{{MatchField: constants.FieldID, SourceField: "Product2Id"}},
	}
	rule2 := LookupValueRule{
		TargetTable:      "Product2",
		OutputField:      "Description",
		HoistedJoinAlias: "lv_product2_2",
		MatchPairs:       []LookupValueMatchRule{{MatchField: constants.FieldID, SourceField: "Product2Id"}},
	}

	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "OrderItem",
		PhysicalTable: "orderitem",
		TableAlias:    "t",
		BaseColumns:   []string{constants.FieldID, "Product2Id"},
		Formulas: []FormulaDefinition{
			{
				Name:             constants.FieldName,
				Expression:       `LOOKUPVALUE("Product2", "Name", "Id", Product2Id)`,
				ReturnType:       "Text",
				LookupValueRules: []LookupValueRule{rule1},
			},
			{
				Name:             "ProductDescription__c",
				Expression:       `LOOKUPVALUE("Product2", "Description", "Id", Product2Id)`,
				ReturnType:       "Text",
				LookupValueRules: []LookupValueRule{rule2},
			},
		},
		Joins: []JoinSpec{
			{
				Alias:        "lv_product2",
				TargetObject: "Product2",
				TargetTable:  "Product2",
				SourceField:  "Product2Id",
				TargetField:  constants.FieldID,
			},
			{
				Alias:        "lv_product2_2",
				TargetObject: "Product2",
				TargetTable:  "Product2",
				SourceField:  "Product2Id",
				TargetField:  constants.FieldID,
			},
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}

	// Both aliases must appear in the DDL.
	if !strings.Contains(ddl, "LEFT JOIN `Product2` lv_product2") {
		t.Errorf("DDL missing first lv_product2 JOIN:\n%s", ddl)
	}
	if !strings.Contains(ddl, "LEFT JOIN `Product2` lv_product2_2") {
		t.Errorf("DDL missing second lv_product2_2 JOIN:\n%s", ddl)
	}
	// Each formula must reference its own alias.
	if !strings.Contains(ddl, "lv_product2.`"+constants.FieldName+"`") {
		t.Errorf("DDL missing lv_product2.Name projection:\n%s", ddl)
	}
	if !strings.Contains(ddl, "lv_product2_2.`Description`") {
		t.Errorf("DDL missing lv_product2_2.Description projection:\n%s", ddl)
	}
}

// TestBuildViewDDL_LookupValuePKMatchAndCrossRefCoexist verifies that a CrossRef
// formula join (j_-prefixed) and a PK-match lookupValue JOIN (lv_-prefixed) for the
// same target object both appear in the DDL with distinct aliases.
func TestBuildViewDDL_LookupValuePKMatchAndCrossRefCoexist(t *testing.T) {
	rule := LookupValueRule{
		TargetTable:      "Account",
		OutputField:      constants.FieldName,
		HoistedJoinAlias: "lv_account",
		MatchPairs:       []LookupValueMatchRule{{MatchField: constants.FieldID, SourceField: "AccountId"}},
	}

	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "Contact",
		PhysicalTable: "contact",
		TableAlias:    "t",
		BaseColumns:   []string{constants.FieldID, "AccountId"},
		Formulas: []FormulaDefinition{
			{
				// CrossRef formula: Account.Industry
				Name:       "AccountIndustry__c",
				Expression: "Account.Industry",
				ReturnType: "Text",
			},
			{
				// PK-match lookupValue: Account.Name via Id
				Name:             "AccountName__c",
				Expression:       `LOOKUPVALUE("Account", "Name", "Id", AccountId)`,
				ReturnType:       "Text",
				LookupValueRules: []LookupValueRule{rule},
			},
		},
		Joins: []JoinSpec{
			// CrossRef join for Account.Industry
			{
				Alias:            "j_account",
				TargetObject:     "Account",
				TargetTable:      "Account",
				SourceField:      "AccountId",
				TargetField:      constants.FieldID,
				RelationshipName: "Account",
			},
			// Hoisted lookupValue join for Account.Name
			{
				Alias:              "lv_account",
				TargetObject:       "Account",
				TargetTable:        "Account",
				SourceField:        "AccountId",
				TargetField:        constants.FieldID,
				HoistedLookupValue: true,
			},
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}

	// Both the CrossRef join and the lookupValue join must be present.
	if !strings.Contains(ddl, "LEFT JOIN `Account` j_account") {
		t.Errorf("DDL missing CrossRef j_account JOIN:\n%s", ddl)
	}
	if !strings.Contains(ddl, "LEFT JOIN `Account` lv_account") {
		t.Errorf("DDL missing lookupValue lv_account JOIN:\n%s", ddl)
	}

	// BUG-1 regression: the CrossRef formula (Account.Industry) must resolve to
	// the j_account alias, NOT the lv_account alias. Before the fix, the lv_
	// join was appended after the j_ join and overwrote the "Account" key in
	// joinAliases (last-wins), causing Account.Industry → lv_account.Industry.
	if !strings.Contains(ddl, "j_account.`Industry`") {
		t.Errorf("BUG-1 regression: CrossRef Account.Industry should use j_account alias, got DDL:\n%s", ddl)
	}
	if strings.Contains(ddl, "lv_account.`Industry`") {
		t.Errorf("BUG-1 regression: CrossRef Account.Industry must NOT use lv_account alias, got DDL:\n%s", ddl)
	}
}

// TestBuildViewDDLConsumerChainReadsBlankAsZero is an HONESTY PIN, not a
// feature test. It asserts a property this PR does NOT fix and must not be
// claimed to fix.
//
// null provenance makes Quote.Bonus_Rate__c project SQL NULL when its prepayment
// ratio is undefined (Salesforce #Error). But the L1 layer reads that L0 alias
// back through toFormulaDomainSQL, which under #4152 blank-as-zero emits
// COALESCE(L0.`Bonus_Rate__c`, 0). So the three sibling formulas that
// consume it inherit a hard 0, NOT a blank.
//
// That is not new and not a defect introduced here: the formula's outer ELSE is
// already a literal NULL, so every non-Cloud quote in production already travels
// this exact path today. Restoring true #Error propagation across a formula-layer
// boundary needs a companion error-flag column per formula — one SQL NULL cannot
// carry two meanings — which is deliberately out of scope for null provenance.
//
// This test exists so that nobody can later assert "the siblings inherit the
// blank" without a red build.
func TestBuildViewDDLConsumerChainReadsBlankAsZero(t *testing.T) {
	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "Quote",
		PhysicalTable: "quote",
		TableAlias:    "t",
		BaseColumns: []string{
			"Id", "Product_Family__c", "Sales_Channel_Type__c",
			"Years__c", "Prepaid__c",
			"MinCommit__c", "Base_Rate__c",
			"Cap_A__c",
			"Cap_B__c",
		},
		FieldTypes: map[string]string{
			"Product_Family__c":     string(constants.FieldTypePicklist),
			"Sales_Channel_Type__c": string(constants.FieldTypeString),
			"Years__c":              string(constants.FieldTypeDouble),
			"Prepaid__c":            string(constants.FieldTypeDouble),
			"MinCommit__c":          string(constants.FieldTypeDouble),
			"Base_Rate__c":          string(constants.FieldTypePercent),
			"Cap_A__c":              string(constants.FieldTypePercent),
			"Cap_B__c":              string(constants.FieldTypePercent),
		},
		Formulas: []FormulaDefinition{
			{
				Name:       "Bonus_Rate__c",
				Expression: tieredBonusFormula,
				ReturnType: string(constants.FieldTypePercent),
			},
			{
				Name: "Bonus_Capped_A__c",
				Expression: "IF(Bonus_Rate__c>Cap_A__c," +
					"Cap_A__c,Bonus_Rate__c)",
				ReturnType: string(constants.FieldTypePercent),
			},
			{
				Name: "Bonus_Capped_B__c",
				Expression: "IF(Bonus_Rate__c>Cap_B__c," +
					"Cap_B__c,Bonus_Rate__c)",
				ReturnType: string(constants.FieldTypePercent),
			},
			{
				Name:       "Net_Rate__c",
				Expression: "Base_Rate__c-Bonus_Rate__c",
				ReturnType: string(constants.FieldTypePercent),
			},
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}

	// L0 layer: the fix is present — the prepay-ratio conditions propagate NULL.
	if strings.Count(ddl, "WHEN TRUE THEN") != 4 {
		t.Fatalf("expected the 4 null provenance strict guards in the L0 layer, got %d:\n%s",
			strings.Count(ddl, "WHEN TRUE THEN"), ddl)
	}

	// L1 layer: the sentinel is laundered back to 0 on read. THIS IS THE POINT.
	if !strings.Contains(ddl, "COALESCE(L0.`Bonus_Rate__c`, 0) / 100.0") {
		t.Fatalf("expected the downstream read to blank-as-zero the L0 alias "+
			"(#4152 semantics; null provenance does NOT change it):\n%s", ddl)
	}

	// Net_Rate__c has no IF at all, so no control-flow fix
	// can ever reach it — it is pure subtraction over the coalesced read.
	if !strings.Contains(ddl, "AS `Net_Rate__c`") {
		t.Fatalf("expected the no-IF consumer formula in the DDL:\n%s", ddl)
	}
}