package analytics

import (
	"context"
	"strings"
	"testing"

	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	kernelschema "github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// ---------------------------------------------------------------------------
// Layer A — Runtime cutover: JOIN target routing based on active-computed field
// ---------------------------------------------------------------------------

// svcTestStrPtr is a local helper to create *string pointers for test fixtures.
func svcTestStrPtr(v string) *string { return &v }

// buildTestSchema builds a simple *models.ObjectMetadata for use in tests.
func buildTestSchema(apiName string, fields []models.FieldMetadata) *models.ObjectMetadata {
	return &models.ObjectMetadata{APIName: apiName, Fields: fields}
}

// activeFormulaField returns a formula-mode-active FieldMetadata.
func activeFormulaField(apiName, formula string) models.FieldMetadata {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)
	return models.FieldMetadata{
		APIName:     apiName,
		Type:        constants.FieldTypeFormula,
		Formula:     svcTestStrPtr(formula),
		FormulaMode: &mode,
		ReturnType:  &rt,
	}
}

// activeFormulaFieldWithIsNameField returns a formula-mode-active FieldMetadata with IsNameField=true.
func activeFormulaFieldWithIsNameField(apiName, formula string) models.FieldMetadata {
	f := activeFormulaField(apiName, formula)
	f.IsNameField = true
	return f
}

func activeWriteMaterializedFormulaFieldWithIsNameField(apiName, formula string) models.FieldMetadata {
	f := activeFormulaFieldWithIsNameField(apiName, formula)
	storageMode := "write_materialized"
	f.FormulaStorageMode = &storageMode
	return f
}

// fkField returns a reference FieldMetadata with the given relationship name.
func fkField(apiName, relName, refTo string) models.FieldMetadata {
	return models.FieldMetadata{
		APIName:          apiName,
		Type:             constants.FieldTypeReference,
		ReferenceTo:      []string{refTo},
		RelationshipName: svcTestStrPtr(relName),
	}
}

func rollupSummaryField(apiName, summaryObject, summaryField, relationshipField, calcType string) models.FieldMetadata {
	return models.FieldMetadata{
		APIName: apiName,
		Type:    constants.FieldTypeRollupSummary,
		RollupConfig: &models.RollupConfig{
			SummaryObject:     summaryObject,
			SummaryField:      summaryField,
			RelationshipField: relationshipField,
			CalcType:          calcType,
		},
	}
}

// buildViewForTest constructs a FormulaViewService with nil db (so
// materializedTableName returns lower(apiName) as the physical table name,
// which is sufficient for DDL-emission assertions) and runs buildViewFromDSL.
func buildViewForTest(
	t *testing.T,
	objectName string,
	schemas map[string]*models.ObjectMetadata,
	formulaFields []models.FieldMetadata,
) string {
	t.Helper()
	meta := newFakeMetadata(schemas)
	svc := &FormulaViewService{
		db:       nil, // nil db: materializedTableName returns lower(name)
		metadata: meta,
		logger:   noopLogger(),
		viewSet:  make(map[string]bool),
	}
	schema := schemas[objectName]
	if schema == nil {
		t.Fatalf("buildViewForTest: schema for %s not found in schemas map", objectName)
	}
	ddl, err := svc.buildViewFromDSL(context.Background(), objectName, schema, formulaFields, 0, true)
	if err != nil {
		t.Fatalf("buildViewFromDSL(%s): unexpected error: %v", objectName, err)
	}
	return ddl
}

// TestPreviewDDLReturnsTypedNotFound locks the #1624 fix: PreviewDDL must return a
// TYPED NotFound (which the boundary renders as 404) for both an unknown object and
// an object with no active computed fields — not a bare error (which fell through to
// a generic HTTP 500) and not ("",nil) (which would let the pack-verify compile-proof
// pass fail-open on a cache/DB desync).
func TestPreviewDDLReturnsTypedNotFound(t *testing.T) {
	plain := buildTestSchema("Account", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
	})
	svc := &FormulaViewService{metadata: newFakeMetadata(map[string]*models.ObjectMetadata{"Account": plain})}

	if _, err := svc.PreviewDDL(context.Background(), "Account"); err == nil {
		t.Fatal("no active computed fields: expected a typed NotFound error, got nil (fail-open for the compile-proof)")
	} else if !apperrors.IsNotFound(err) {
		t.Fatalf("no active computed fields: expected NotFound (404), got %T: %v", err, err)
	}

	if _, err := svc.PreviewDDL(context.Background(), "DoesNotExist"); err == nil || !apperrors.IsNotFound(err) {
		t.Fatalf("unknown object: expected NotFound (404), got %v", err)
	}
}

func TestBuildFieldTypeMapIndexesRelationshipNameForCrossRefPercent(t *testing.T) {
	parentSchema := buildTestSchema("Parent__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Score__c", Type: constants.FieldTypePercent},
	})
	childSchema := buildTestSchema("Child__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("Parent__c", "Parent__r", "Parent__c"),
	})
	svc := &FormulaViewService{
		metadata: newFakeMetadata(map[string]*models.ObjectMetadata{
			"Child__c":  childSchema,
			"Parent__c": parentSchema,
		}),
	}

	fieldTypes := svc.buildFieldTypeMap(context.Background(), childSchema, []JoinSpec{{
		Alias:            "j_parent",
		TargetTable:      "Parent__c",
		SourceField:      "Parent__c",
		TargetField:      "Id",
		RelationshipName: "Parent__r",
	}}, nil, nil)

	for _, key := range []string{"Parent__r.Score__c", "Parent__c.Score__c"} {
		if got := fieldTypes[key]; got != string(constants.FieldTypePercent) {
			t.Fatalf("fieldTypes[%q] = %q, want percent", key, got)
		}
	}
}

// ---------------------------------------------------------------------------
// Test 1 — One-hop JOIN routes to v_<target> when reading active-computed field
// ---------------------------------------------------------------------------

// TestBuildViewFromDSL_CrossRefJoinRoutesToView verifies that when formula A.Parent.Foo__c
// reads an active-computed field (Foo__c) on the join target (Account), the emitted
// JOIN targets `v_account` (the formula view) instead of the physical `account` table.
//
// Without Layer A, materializedTableName returns the physical table name ("account"),
// so the DDL would emit LEFT JOIN `account` — and at runtime `account.foo__c` does not
// exist (only `v_account.foo__c`).
func TestBuildViewFromDSL_CrossRefJoinRoutesToView(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)
	fooMode := "active"

	accountSchema := buildTestSchema("Account", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
		// Foo__c is active-computed on Account — lives in v_account, not physical table.
		{
			APIName:     "Foo__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Name"),
			FormulaMode: &fooMode,
			ReturnType:  &rt,
		},
	})

	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("ParentId", "Parent", "Account"),
		// Reads Foo__c — active-computed on Account → JOIN must target v_account.
		{
			APIName:     "ParentFoo__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Parent.Foo__c"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	formulaFields := []models.FieldMetadata{objASchema.Fields[2]}
	ddl := buildViewForTest(t, "ObjA", map[string]*models.ObjectMetadata{
		"ObjA":    objASchema,
		"Account": accountSchema,
	}, formulaFields)

	// The JOIN must target v_account (the formula view), not the physical account table.
	if !strings.Contains(ddl, "LEFT JOIN `v_account`") {
		t.Errorf("DDL does not JOIN v_account for active-computed Foo__c:\n%s", ddl)
	}
	if strings.Contains(ddl, "LEFT JOIN `account`") && !strings.Contains(ddl, "LEFT JOIN `v_account`") {
		t.Errorf("DDL incorrectly JOINs physical `account` instead of `v_account`:\n%s", ddl)
	}
}

// ---------------------------------------------------------------------------
// Test 2 — One-hop JOIN stays on physical table when reading non-computed field
// ---------------------------------------------------------------------------

// TestBuildViewFromDSL_CrossRefJoinStaysPhysicalForBaseField verifies that when a
// formula reads only physical fields on the join target (e.g. Account.Name), the
// emitted JOIN targets the physical `account` table (not `v_account`).
//
// Routing physical-field reads to v_account is unnecessary (the physical table has
// the field) and would create a false view-build dependency.
func TestBuildViewFromDSL_CrossRefJoinStaysPhysicalForBaseField(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	// Account has NO active-computed fields — all fields are physical.
	accountSchema := buildTestSchema("Account", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
	})

	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("ParentId", "Parent", "Account"),
		// Reads Name — a plain physical field on Account → JOIN should stay physical.
		{
			APIName:     "ParentName__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Parent.Name"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	formulaFields := []models.FieldMetadata{objASchema.Fields[2]}
	ddl := buildViewForTest(t, "ObjA", map[string]*models.ObjectMetadata{
		"ObjA":    objASchema,
		"Account": accountSchema,
	}, formulaFields)

	// With nil db, materializedTableName returns lower(apiName) = "account".
	if strings.Contains(ddl, "LEFT JOIN `v_account`") {
		t.Errorf("DDL incorrectly routes physical Name read to v_account:\n%s", ddl)
	}
	if !strings.Contains(ddl, "LEFT JOIN `account`") {
		t.Errorf("DDL does not JOIN physical `account` for physical Name field:\n%s", ddl)
	}
}

func TestBuildViewFromDSL_UsesReadSourceBaseTableAndLogicalAlias(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	recordTypeSchema := &models.ObjectMetadata{
		APIName: "RecordType",
		ReadSource: &models.ObjectReadSourceConfig{
			Kind:          "physical_table",
			PhysicalTable: "_System_RecordType",
		},
		Fields: []models.FieldMetadata{
			{APIName: "Id", Type: constants.FieldTypeString},
			{APIName: "Name", Type: constants.FieldTypeString, IsNameField: true, PhysicalName: "record_type_name"},
			{
				APIName:     "DisplayName__c",
				Type:        constants.FieldTypeFormula,
				Formula:     svcTestStrPtr("Name"),
				FormulaMode: &mode,
				ReturnType:  &rt,
			},
		},
	}

	ddl := buildViewForTest(t, "RecordType", map[string]*models.ObjectMetadata{
		"RecordType": recordTypeSchema,
	}, []models.FieldMetadata{recordTypeSchema.Fields[2]})

	if !strings.Contains(ddl, "FROM `_System_RecordType` t") {
		t.Fatalf("DDL does not read from readSource base table:\n%s", ddl)
	}
	if !strings.Contains(ddl, "t.`record_type_name` AS `Name`") {
		t.Fatalf("DDL does not preserve logical Name alias over physical readSource column:\n%s", ddl)
	}
}

// ---------------------------------------------------------------------------
// Test 3 — Deep chain last hop routes to v_<obj> when reading active-computed field
// ---------------------------------------------------------------------------

// TestBuildViewFromDSL_DeepJoinLastHopRoutesToView verifies that when a deep
// cross-ref (e.g. Owner.Profile.Name where Profile.Name is active-computed)
// resolves the last hop, the last DeepJoinHop.TargetTable is `v_profile` (not
// `profile`).
//
// Intermediate hops (e.g. the Owner→User hop for UserSchema) read Id only
// (a physical column) and must stay on the physical table.
func TestBuildViewFromDSL_DeepJoinLastHopRoutesToView(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)
	computedMode := "active"

	profileSchema := buildTestSchema("Profile", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		// Name is active-computed on Profile — lives in v_profile.
		{
			APIName:     "Name",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("'computed'"),
			FormulaMode: &computedMode,
			ReturnType:  &rt,
		},
	})

	userSchema := buildTestSchema("User", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
		fkField("ProfileId", "Profile", "Profile"),
	})

	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("OwnerId", "Owner", "User"),
		// Deep ref: Owner→User→Profile, reads Name which is active-computed on Profile.
		{
			APIName:     "OwnerProfileName__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Owner.Profile.Name"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	formulaFields := []models.FieldMetadata{objASchema.Fields[2]}
	ddl := buildViewForTest(t, "ObjA", map[string]*models.ObjectMetadata{
		"ObjA":    objASchema,
		"User":    userSchema,
		"Profile": profileSchema,
	}, formulaFields)

	// Last hop (User→Profile) should target v_profile because Profile.Name is active-computed.
	if !strings.Contains(ddl, "LEFT JOIN `v_profile`") {
		t.Errorf("DDL deep chain last hop does not target v_profile for active-computed Profile.Name:\n%s", ddl)
	}
	// Intermediate hop (ObjA→User) should target physical `user` (reading Id only).
	if strings.Contains(ddl, "LEFT JOIN `v_user`") {
		t.Errorf("DDL intermediate hop incorrectly targets v_user (intermediate joins read Id only):\n%s", ddl)
	}
}

// ---------------------------------------------------------------------------
// Test 4 — Deep chain last hop stays physical when reading non-computed field
// ---------------------------------------------------------------------------

// TestBuildViewFromDSL_DeepJoinLastHopStaysPhysicalForBaseField verifies that when
// a deep cross-ref reads a physical field at the last hop, the join stays on the
// physical table, not the view.
func TestBuildViewFromDSL_DeepJoinLastHopStaysPhysicalForBaseField(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	// Profile has no active-computed fields — Name is physical.
	profileSchema := buildTestSchema("Profile", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
	})

	userSchema := buildTestSchema("User", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
		fkField("ProfileId", "Profile", "Profile"),
	})

	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("OwnerId", "Owner", "User"),
		{
			APIName:     "OwnerProfileName__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Owner.Profile.Name"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	formulaFields := []models.FieldMetadata{objASchema.Fields[2]}
	ddl := buildViewForTest(t, "ObjA", map[string]*models.ObjectMetadata{
		"ObjA":    objASchema,
		"User":    userSchema,
		"Profile": profileSchema,
	}, formulaFields)

	// Neither hop should target a view — both read physical fields.
	if strings.Contains(ddl, "LEFT JOIN `v_") {
		t.Errorf("DDL incorrectly routes physical field read to a view table:\n%s", ddl)
	}
	if !strings.Contains(ddl, "LEFT JOIN `profile`") {
		t.Errorf("DDL does not JOIN physical `profile` for physical Name field:\n%s", ddl)
	}
}

// ---------------------------------------------------------------------------
// Test 5 — Layer C: L2-cross-pack-object ordering (R9 BLOCK keystone case)
// ---------------------------------------------------------------------------

// TestOrderForBuild_L2CrossPackObjectDependency verifies the R9 BLOCK keystone case:
// pack-owned object A has an L2-admin-added active formula reading an active-computed
// field on pack-owned object B. No pack-payload edge is declared. The live-metadata
// dependency resolver must produce B before A.
//
// This is exactly the scenario that caused the R9 BLOCK in PR #868: the pack-payload-
// only ordering was unaware of this L2-introduced edge, so the wrong order was used.
func TestOrderForBuild_L2CrossPackObjectDependency(t *testing.T) {
	ctx := context.Background()
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	objBSchema := &models.ObjectMetadata{
		APIName: "ObjB",
		Fields: []models.FieldMetadata{
			{APIName: "Id", Type: constants.FieldTypeString},
			// ObjB has its own active-computed field → has a v_objb view.
			{
				APIName:     "ComputedLabel__c",
				Type:        constants.FieldTypeFormula,
				Formula:     svcTestStrPtr("Name"),
				FormulaMode: &mode,
				ReturnType:  &rt,
			},
		},
	}

	objASchema := &models.ObjectMetadata{
		APIName: "ObjA",
		Fields: []models.FieldMetadata{
			{APIName: "Id", Type: constants.FieldTypeString},
			fkField("BId", "B", "ObjB"),
			// L2-admin-added formula: reads ComputedLabel__c — active-computed on ObjB.
			// No pack-payload edge declared; must be discovered from live metadata.
			{
				APIName:     "BMirror__c",
				Type:        constants.FieldTypeFormula,
				Formula:     svcTestStrPtr("B.ComputedLabel__c"),
				FormulaMode: &mode,
				ReturnType:  &rt,
			},
		},
	}

	svc := &FormulaViewService{
		metadata: newFakeMetadata(map[string]*models.ObjectMetadata{
			"ObjA": objASchema,
			"ObjB": objBSchema,
		}),
	}

	ordered, err := svc.OrderForBuild(ctx, []string{"ObjA", "ObjB"})
	if err != nil {
		t.Fatalf("OrderForBuild: unexpected error: %v", err)
	}
	if len(ordered) != 2 {
		t.Fatalf("OrderForBuild: got %d items, want 2: %v", len(ordered), ordered)
	}

	posA, posB := -1, -1
	for i, name := range ordered {
		switch name {
		case "ObjA":
			posA = i
		case "ObjB":
			posB = i
		}
	}
	if posB >= posA {
		t.Errorf("OrderForBuild = %v: ObjB (pos %d) must come before ObjA (pos %d) because ObjA reads active-computed field on ObjB", ordered, posB, posA)
	}
}

func TestOrderForBuild_LogicalNameActiveComputedDependency(t *testing.T) {
	ctx := context.Background()
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	objBSchema := &models.ObjectMetadata{
		APIName: "ObjB",
		Fields: []models.FieldMetadata{
			{APIName: "Id", Type: constants.FieldTypeString},
			{
				APIName:     "Subject",
				Type:        constants.FieldTypeFormula,
				IsNameField: true,
				Formula:     svcTestStrPtr("'computed subject'"),
				FormulaMode: &mode,
				ReturnType:  &rt,
			},
		},
	}

	objASchema := &models.ObjectMetadata{
		APIName: "ObjA",
		Fields: []models.FieldMetadata{
			{APIName: "Id", Type: constants.FieldTypeString},
			fkField("BId", "B", "ObjB"),
			{
				APIName:     "BName__c",
				Type:        constants.FieldTypeFormula,
				Formula:     svcTestStrPtr("B.Name"),
				FormulaMode: &mode,
				ReturnType:  &rt,
			},
		},
	}

	svc := &FormulaViewService{
		metadata: newFakeMetadata(map[string]*models.ObjectMetadata{
			"ObjA": objASchema,
			"ObjB": objBSchema,
		}),
	}

	ordered, err := svc.OrderForBuild(ctx, []string{"ObjA", "ObjB"})
	if err != nil {
		t.Fatalf("OrderForBuild: unexpected error: %v", err)
	}

	posA, posB := -1, -1
	for i, name := range ordered {
		switch name {
		case "ObjA":
			posA = i
		case "ObjB":
			posB = i
		}
	}
	if posB < 0 || posA < 0 || posB >= posA {
		t.Fatalf("OrderForBuild = %v: ObjB must come before ObjA because B.Name resolves to active-computed ObjB.Subject", ordered)
	}
}

// ---------------------------------------------------------------------------
// Test 6 — Cycle detection: returns named error, no lexical fallback
// ---------------------------------------------------------------------------

// TestOrderForBuild_CycleBetweenActiveComputedObjects verifies that when two
// objects have circular active-computed field reads (A reads B.computed, B reads
// A.computed), OrderForBuild returns a named "circular derived-view dependency"
// error and does NOT return a partial ordering.
func TestOrderForBuild_CycleBetweenActiveComputedObjects(t *testing.T) {
	ctx := context.Background()
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	objASchema := &models.ObjectMetadata{
		APIName: "CycleX",
		Fields: []models.FieldMetadata{
			{APIName: "Id", Type: constants.FieldTypeString},
			fkField("YId", "Y", "CycleY"),
			{
				APIName:     "ComputedFromY__c",
				Type:        constants.FieldTypeFormula,
				Formula:     svcTestStrPtr("Y.ComputedFromX__c"),
				FormulaMode: &mode,
				ReturnType:  &rt,
			},
		},
	}

	objBSchema := &models.ObjectMetadata{
		APIName: "CycleY",
		Fields: []models.FieldMetadata{
			{APIName: "Id", Type: constants.FieldTypeString},
			fkField("XId", "X", "CycleX"),
			{
				APIName:     "ComputedFromX__c",
				Type:        constants.FieldTypeFormula,
				Formula:     svcTestStrPtr("X.ComputedFromY__c"),
				FormulaMode: &mode,
				ReturnType:  &rt,
			},
		},
	}

	svc := &FormulaViewService{
		metadata: newFakeMetadata(map[string]*models.ObjectMetadata{
			"CycleX": objASchema,
			"CycleY": objBSchema,
		}),
	}

	result, err := svc.OrderForBuild(ctx, []string{"CycleX", "CycleY"})
	if err == nil {
		t.Fatalf("OrderForBuild(cycle): expected error, got result = %v (no lexical fallback permitted)", result)
	}
	if result != nil {
		t.Errorf("OrderForBuild(cycle): got non-nil result %v alongside error; want nil result", result)
	}
	errMsg := err.Error()
	if !strings.Contains(strings.ToLower(errMsg), "circular") {
		t.Errorf("OrderForBuild cycle error = %q, want 'circular' in message", errMsg)
	}
}

// ---------------------------------------------------------------------------
// Regression tests — TargetObject / LogicalObject split (CI Codex HIGH findings)
// ---------------------------------------------------------------------------

// TestBuildViewFromDSL_CrossRefAliasResolvedAfterViewRouting is a regression test for the
// bug where buildCrossRefFieldAliasMap called GetSchema(j.TargetTable) after the JOIN had
// been rerouted to v_<obj>, causing GetSchema to return nil and the Name→physical-field
// alias to be silently dropped.
//
// Setup: Account's logical "name" is stored in Subject (IsNameField=true on Subject; no
// field named "Name"). ObjA reads Parent.Name. After view-routing the JOIN to v_account
// (because Account.Computed__c is active-computed), the DDL must still resolve the Name
// alias to Subject — proving that schema lookup uses TargetObject (logical) not TargetTable.
func TestBuildViewFromDSL_CrossRefAliasResolvedAfterViewRouting(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)
	computedMode := "active"

	// Account: no "Name" field; Subject carries IsNameField. Has an active-computed
	// field to force the JOIN to route to v_account.
	accountSchema := buildTestSchema("Account", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Subject", Type: constants.FieldTypeString, IsNameField: true},
		{
			APIName:     "Computed__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Subject"),
			FormulaMode: &computedMode,
			ReturnType:  &rt,
		},
	})

	// ObjA reads Parent.Name — logical "Name" which should map to Subject on Account.
	// Also reads Parent.Computed__c so that crossRefReadsActiveComputedOnTarget is true
	// and the JOIN is routed to v_account.
	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("ParentId", "Parent", "Account"),
		{
			APIName:     "ParentName__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Parent.Computed__c"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	formulaFields := []models.FieldMetadata{objASchema.Fields[2]}
	svc := &FormulaViewService{
		db: nil,
		metadata: newFakeMetadata(map[string]*models.ObjectMetadata{
			"ObjA":    objASchema,
			"Account": accountSchema,
		}),
		logger:  noopLogger(),
		viewSet: make(map[string]bool),
	}

	// Verify the alias map is populated correctly even after view-routing.
	joins := []JoinSpec{
		{
			Alias:            "j_parent",
			TargetObject:     "Account",
			TargetTable:      "v_account", // already rerouted
			SourceField:      "ParentId",
			TargetField:      "Id",
			RelationshipName: "Parent",
		},
	}
	ctx := context.Background()
	aliasMap := svc.buildCrossRefFieldAliasMap(ctx, joins)

	// Must map logical "Name" references through "Parent" or "Account" to Subject.
	foundAlias := false
	for k, v := range aliasMap {
		if strings.EqualFold(v, "Subject") && strings.HasSuffix(strings.ToLower(k), ".name") {
			foundAlias = true
			break
		}
	}
	if !foundAlias {
		t.Errorf("buildCrossRefFieldAliasMap returned %v: expected a key ending in .Name mapped to Subject (logical Account schema used for lookup, not v_account view name)", aliasMap)
	}

	// The full DDL path must also succeed without error.
	ddl, err := svc.buildViewFromDSL(ctx, "ObjA", objASchema, formulaFields, 0, true)
	if err != nil {
		t.Fatalf("buildViewFromDSL after view-routing: unexpected error: %v", err)
	}
	if ddl == "" {
		t.Error("buildViewFromDSL returned empty DDL")
	}
}

func TestBuildViewFromDSL_PrefersRelationshipAliasWhenMultipleJoinsShareTargetObject(t *testing.T) {
	accountSchema := buildTestSchema("Account", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "BillingCountryCode", Type: constants.FieldTypeString},
	})
	orderSchema := buildTestSchema("Order", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("AccountId", "Account", "Account"),
		fkField("Partner_Name__c", "Partner_Name__r", "Account"),
		activeFormulaField("Account_Country_Code__c", "TEXT(Account.BillingCountryCode)"),
		activeFormulaField("Partner_Country_Code__c", "TEXT(Partner_Name__r.BillingCountryCode)"),
	})

	svc := &FormulaViewService{
		db: nil,
		metadata: newFakeMetadata(map[string]*models.ObjectMetadata{
			"Order":   orderSchema,
			"Account": accountSchema,
		}),
		logger:  noopLogger(),
		viewSet: make(map[string]bool),
	}

	ddl, err := svc.buildViewFromDSL(context.Background(), "Order", orderSchema, []models.FieldMetadata{
		orderSchema.Fields[3],
		orderSchema.Fields[4],
	}, 0, true)
	if err != nil {
		t.Fatalf("buildViewFromDSL() error = %v", err)
	}

	if !strings.Contains(ddl, "j_account.`BillingCountryCode`") {
		t.Fatalf("DDL must resolve Account.BillingCountryCode through the Account relationship alias:\n%s", ddl)
	}
	if !strings.Contains(ddl, "j_partner_name__r.`BillingCountryCode`") {
		t.Fatalf("DDL must preserve Partner_Name__r.BillingCountryCode through the partner join:\n%s", ddl)
	}
	if strings.Contains(ddl, "j_partner_name__r.`BillingCountryCode` AS `Account_Country_Code__c`") {
		t.Fatalf("DDL incorrectly bound Account_Country_Code__c to the partner join:\n%s", ddl)
	}
}

// TestBuildViewFromDSL_DeepRefFieldTypeResolvedAfterViewRouting is a regression test for
// the bug where buildFieldTypeMap read Hops[last].TargetTable for GetSchema after the hop
// had been rerouted to v_<obj>, causing the schema to be nil and the field type to be
// dropped — so date-arithmetic formulas fell back to raw MySQL subtraction instead of
// emitting DATEDIFF.
//
// Setup: deep ref Owner.Profile.CloseDate__c where CloseDate__c is a Date field on Profile
// (active-computed, so the last hop is rerouted to v_profile). The formula subtracts
// TODAY() — with correct field-type metadata the compiler emits DATEDIFF; without it the
// compiler falls back to the type-naive "(a - b)" form. The test asserts DATEDIFF is
// present in the DDL.
func TestBuildViewFromDSL_DeepRefFieldTypeResolvedAfterViewRouting(t *testing.T) {
	mode := "active"
	computedMode := "active"
	dateRT := models.FieldType(constants.FieldTypeDate)
	strRT := models.FieldType(constants.FieldTypeString)

	profileSchema := buildTestSchema("Profile", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		// CloseDate__c is an active-computed Date field on Profile → last hop goes to v_profile.
		{
			APIName:     "CloseDate__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("TODAY()"),
			FormulaMode: &computedMode,
			ReturnType:  &dateRT,
		},
	})

	userSchema := buildTestSchema("User", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
		fkField("ProfileId", "Profile", "Profile"),
	})

	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("OwnerId", "Owner", "User"),
		// Date subtraction: Owner.Profile.CloseDate__c - TODAY()
		// → must emit DATEDIFF if field type is resolved correctly.
		{
			APIName:     "DaysOpen__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Owner.Profile.CloseDate__c - TODAY()"),
			FormulaMode: &mode,
			ReturnType:  &strRT,
		},
	})

	formulaFields := []models.FieldMetadata{objASchema.Fields[2]}
	ddl := buildViewForTest(t, "ObjA", map[string]*models.ObjectMetadata{
		"ObjA":    objASchema,
		"User":    userSchema,
		"Profile": profileSchema,
	}, formulaFields)

	// The last DeepJoinHop routes to v_profile, but field type must still be resolved
	// from the logical Profile schema — so the compiler sees CloseDate__c as Date and
	// emits DATEDIFF instead of raw subtraction.
	if !strings.Contains(ddl, "DATEDIFF") {
		t.Errorf("DDL does not contain DATEDIFF for date-arithmetic deep ref after view-routing:\n%s\n\nExpected DATEDIFF(Owner.Profile.CloseDate__c - TODAY()) but compiler fell back to type-naive subtraction, indicating field-type metadata was dropped when last hop was rerouted to v_profile", ddl)
	}
}

func TestBuildViewFromDSL_RollupSummaryReferenceUsesStoredColumn(t *testing.T) {
	mode := "active"
	currencyRT := models.FieldType(constants.FieldTypeCurrency)
	filter := "Status__c = 'Approved'"

	parentSchema := buildTestSchema("Grant__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "InitialAmount__c", Type: constants.FieldTypeCurrency},
		func() models.FieldMetadata {
			f := rollupSummaryField("ApprovedChildSum__c", "ChildGrant__c", "Amount__c", "GrantId__c", "SUM")
			f.RollupConfig.Filter = &filter
			return f
		}(),
		{
			APIName:     "Total__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("InitialAmount__c + BLANKVALUE(ApprovedChildSum__c, 0)"),
			FormulaMode: &mode,
			ReturnType:  &currencyRT,
		},
	})

	childSchema := buildTestSchema("ChildGrant__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Amount__c", Type: constants.FieldTypeCurrency},
		{APIName: "Status__c", Type: constants.FieldTypeString},
		fkField("GrantId__c", "Grant__r", "Grant__c"),
	})

	schemas := map[string]*models.ObjectMetadata{
		"Grant__c":      parentSchema,
		"ChildGrant__c": childSchema,
	}
	validateFormulaForTestOK(t, "Grant__c", schemas, "InitialAmount__c + BLANKVALUE(ApprovedChildSum__c, 0)")

	ddl := buildViewForTest(t, "Grant__c", map[string]*models.ObjectMetadata{
		"Grant__c":      parentSchema,
		"ChildGrant__c": childSchema,
	}, []models.FieldMetadata{parentSchema.Fields[3]})

	if !strings.Contains(ddl, "t.`ApprovedChildSum__c`") {
		t.Fatalf("DDL must read the stored rollupsummary column from the parent projection:\n%s", ddl)
	}
	if strings.Contains(ddl, "Status__c = 'Approved'") {
		t.Fatalf("DDL unexpectedly inlined the rollup filter into the formula view instead of reading the stored column:\n%s", ddl)
	}
	if strings.Contains(ddl, "rsq0") {
		t.Fatalf("DDL unexpectedly synthesized an aggregate join for a stored rollupsummary reference:\n%s", ddl)
	}
}

func TestBuildViewFromDSL_PercentRollupReferenceUsesFormulaDomain(t *testing.T) {
	mode := "active"
	percentRT := models.FieldType(constants.FieldTypePercent)
	doubleRT := models.FieldType(constants.FieldTypeDouble)
	rollup := rollupSummaryField("DiscountRollup__c", "Child__c", "Discount__c", "ParentId", "AVG")
	rollup.ReturnType = &percentRT
	rollup.RollupConfig.ResultType = &percentRT

	parentSchema := buildTestSchema("Parent__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		rollup,
		{
			APIName:     "Remaining__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("1 - DiscountRollup__c"),
			FormulaMode: &mode,
			ReturnType:  &doubleRT,
		},
	})

	ddl := buildViewForTest(
		t,
		"Parent__c",
		map[string]*models.ObjectMetadata{"Parent__c": parentSchema},
		[]models.FieldMetadata{parentSchema.Fields[2]},
	)

	if !strings.Contains(ddl, "COALESCE(t.`DiscountRollup__c`, 0) / 100.0") {
		t.Fatalf("compiled DDL does not normalize the stored percent rollup into formula domain:\n%s", ddl)
	}
}

// TestBuildViewFromDSL_RollupSummaryResultTypesDriveDateAndNumericArithmetic
// proves that Formula Views compile from the resolver-owned scalar result of a
// stored rollup, rather than treating every rollupsummary as the container
// type. MIN/MAX must retain their source formula's date type so date subtraction
// emits DATEDIFF; numeric COUNT/SUM/AVG results remain numeric and use ordinary
// arithmetic.
func TestBuildViewFromDSL_RollupSummaryResultTypesDriveDateAndNumericArithmetic(t *testing.T) {
	mode := "active"
	dateRT := models.FieldType(constants.FieldTypeDate)
	doubleRT := models.FieldType(constants.FieldTypeDouble)

	lineItemSchema := buildTestSchema("LineItem__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{
			APIName:    "Start_Date_Formula__c",
			Type:       constants.FieldTypeFormula,
			Formula:    svcTestStrPtr("ServiceDate"),
			ReturnType: &dateRT,
		},
		{
			APIName:    "End_Date_Formula__c",
			Type:       constants.FieldTypeFormula,
			Formula:    svcTestStrPtr("EndDate"),
			ReturnType: &dateRT,
		},
		{APIName: "Amount__c", Type: constants.FieldTypeCurrency},
		{APIName: "Units__c", Type: constants.FieldTypeInt},
		fkField("OrderId", "Order", "Order"),
	})

	lookup := func(objectName string) (*models.ObjectMetadata, error) {
		if objectName == lineItemSchema.APIName {
			return lineItemSchema, nil
		}
		return nil, nil
	}
	resolvedRollup := func(apiName, summaryField, calcType, sourceSQLType string) models.FieldMetadata {
		t.Helper()
		resolved, err := kernelschema.ResolveRollup("Order", models.RollupConfig{
			SummaryObject:     lineItemSchema.APIName,
			SummaryField:      summaryField,
			RelationshipField: "OrderId",
			CalcType:          calcType,
		}, lookup, sourceSQLType)
		if err != nil {
			t.Fatalf("ResolveRollup(%s): %v", apiName, err)
		}
		return models.FieldMetadata{
			APIName:      apiName,
			Type:         constants.FieldTypeRollupSummary,
			RollupConfig: &resolved.CanonicalConfig,
		}
	}

	startRollup := resolvedRollup("Overall_Start_Date__c", "Start_Date_Formula__c", "MIN", "DATE")
	endRollup := resolvedRollup("Overall_End_Date__c", "End_Date_Formula__c", "MAX", "DATE")
	totalRollup := resolvedRollup("Total_Amount__c", "Amount__c", "SUM", "DECIMAL(18,2)")
	averageRollup := resolvedRollup("Average_Units__c", "Units__c", "AVG", "INT")
	countRollup := resolvedRollup("Line_Item_Count__c", "", "COUNT", "")

	orderSchema := buildTestSchema("Order", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		startRollup,
		endRollup,
		totalRollup,
		averageRollup,
		countRollup,
		{
			APIName:     "Term__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Overall_End_Date__c - Overall_Start_Date__c"),
			FormulaMode: &mode,
			ReturnType:  &doubleRT,
		},
		{
			APIName:     "Numeric_Delta__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Average_Units__c - Total_Amount__c"),
			FormulaMode: &mode,
			ReturnType:  &doubleRT,
		},
	})

	schemas := map[string]*models.ObjectMetadata{
		"Order":       orderSchema,
		"LineItem__c": lineItemSchema,
	}
	svc := &FormulaViewService{metadata: newFakeMetadata(schemas)}
	fieldTypes := svc.buildFieldTypeMap(context.Background(), orderSchema, nil, nil, nil)
	for field, want := range map[string]string{
		"Overall_Start_Date__c": string(constants.FieldTypeDate),
		"Overall_End_Date__c":   string(constants.FieldTypeDate),
		"Total_Amount__c":       string(constants.FieldTypeCurrency),
		"Average_Units__c":      string(constants.FieldTypeDouble),
		"Line_Item_Count__c":    string(constants.FieldTypeInt),
	} {
		if got := fieldTypes[field]; got != want {
			t.Fatalf("fieldTypes[%q] = %q, want %q", field, got, want)
		}
	}

	ddl := buildViewForTest(t, "Order", schemas, []models.FieldMetadata{
		orderSchema.Fields[6],
		orderSchema.Fields[7],
	})
	if !strings.Contains(ddl, "DATEDIFF(t.`Overall_End_Date__c`, t.`Overall_Start_Date__c`)") {
		t.Fatalf("date MIN/MAX rollup arithmetic must compile to DATEDIFF:\n%s", ddl)
	}
	if strings.Contains(ddl, "(t.`Overall_End_Date__c` - t.`Overall_Start_Date__c`)") {
		t.Fatalf("date MIN/MAX rollup arithmetic must not use raw subtraction:\n%s", ddl)
	}
	if !strings.Contains(ddl, "(COALESCE(t.`Average_Units__c`, 0) - COALESCE(t.`Total_Amount__c`, 0))") {
		t.Fatalf("numeric rollup arithmetic must remain ordinary subtraction:\n%s", ddl)
	}
}

func validateFormulaForTestOK(
	t *testing.T,
	objectName string,
	schemas map[string]*models.ObjectMetadata,
	formula string,
) {
	t.Helper()
	meta := newFakeMetadata(schemas)
	svc := &FormulaViewService{
		db:       nil,
		metadata: meta,
		logger:   noopLogger(),
		viewSet:  make(map[string]bool),
	}
	if err := svc.ValidateFormulaForObject(context.Background(), objectName, formula); err != nil {
		t.Fatalf("ValidateFormulaForObject(%s, %q): unexpected error: %v", objectName, formula, err)
	}
}

func validateFormulaForFieldTestError(
	t *testing.T,
	objectName string,
	fieldAPIName string,
	schemas map[string]*models.ObjectMetadata,
	formula string,
) error {
	t.Helper()
	meta := newFakeMetadata(schemas)
	svc := &FormulaViewService{
		db:       nil,
		metadata: meta,
		logger:   noopLogger(),
		viewSet:  make(map[string]bool),
	}
	err := svc.ValidateFormulaForField(context.Background(), objectName, fieldAPIName, formula)
	if err == nil {
		t.Fatalf("ValidateFormulaForField(%s, %s, %q): expected error, got nil", objectName, fieldAPIName, formula)
	}
	return err
}

// ---------------------------------------------------------------------------
// Tests for self-referential active-computed reads via inner projection (#886)
// ---------------------------------------------------------------------------

// TestBuildViewFromDSL_SelfRefActiveComputedUsesInnerProjection verifies that a
// 1-hop self-referential JOIN (e.g. Parent.ComputedX__c on the same object)
// succeeds by reading the parent value from a derived inner projection instead
// of emitting a recursive `LEFT JOIN v_<obj>` inside `CREATE VIEW v_<obj>`.
func TestBuildViewFromDSL_SelfRefActiveComputedUsesInnerProjection(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	// ObjA has ComputedX__c (active-computed) and a self-ref relationship Parent.
	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
		fkField("ParentId", "Parent", "ObjA"),
		// ComputedX__c is active-computed on ObjA itself.
		{
			APIName:     "ComputedX__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("'computed'"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
		// Mixed physical self-ref read on the same relationship must keep the
		// L0 physical join even though ParentComputed__c needs an outer projection.
		{
			APIName:     "ParentName__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Parent.Name"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
		// SelfRef formula reads Parent.ComputedX__c — self-ref to active-computed.
		{
			APIName:     "ParentComputed__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Parent.ComputedX__c"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	formulaFields := []models.FieldMetadata{objASchema.Fields[3], objASchema.Fields[4], objASchema.Fields[5]}
	ddl := buildViewForTest(t, "ObjA", map[string]*models.ObjectMetadata{
		"ObjA": objASchema,
	}, formulaFields)

	if strings.Contains(ddl, "LEFT JOIN `v_obja`") {
		t.Fatalf("DDL must not recursively JOIN v_obja for self-ref active-computed reads:\n%s", ddl)
	}
	if !strings.Contains(ddl, "LEFT JOIN (") {
		t.Fatalf("DDL must use a derived inner projection for self-ref active-computed reads:\n%s", ddl)
	}
	if !strings.Contains(ddl, "LEFT JOIN `obja` j_parent ON t.`ParentId` = j_parent.`Id`") {
		t.Fatalf("DDL must keep the physical L0 self join for mixed self-ref physical reads:\n%s", ddl)
	}
	if !strings.Contains(ddl, "j_parent.`Name` AS `ParentName__c`") {
		t.Fatalf("DDL must read Parent.Name from the physical self join:\n%s", ddl)
	}
	if !strings.Contains(ddl, ") j_parent ON L0.`ParentId` = j_parent.`Id`") {
		t.Fatalf("DDL must self-join the previous computed layer on ParentId -> Id:\n%s", ddl)
	}
	if !strings.Contains(ddl, "j_parent.`ComputedX__c` AS `ParentComputed__c`") {
		t.Fatalf("DDL must read ComputedX__c from the parent inner projection alias:\n%s", ddl)
	}
}

func TestBuildViewFromDSL_SelfProjectionPreservesLogicalNameAlias(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	// ObjA has no physical Name field. Subject is the logical display field, so
	// Parent.Name must lower to the physical Subject column even in an outer layer.
	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Subject", Type: constants.FieldTypeString, IsNameField: true},
		fkField("ParentId", "Parent", "ObjA"),
		{
			APIName:     "ComputedX__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("'computed'"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
		{
			APIName:     "ParentName__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Parent.Name"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
		{
			APIName:     "ParentComputed__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Parent.ComputedX__c"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	ddl := buildViewForTest(t, "ObjA", map[string]*models.ObjectMetadata{
		"ObjA": objASchema,
	}, []models.FieldMetadata{objASchema.Fields[3], objASchema.Fields[4], objASchema.Fields[5]})

	if strings.Contains(ddl, "j_parent.`Name` AS `ParentName__c`") {
		t.Fatalf("DDL must not read a non-existent physical Name column from the parent projection:\n%s", ddl)
	}
	if !strings.Contains(ddl, "j_parent.`Subject` AS `ParentName__c`") {
		t.Fatalf("DDL must preserve logical Parent.Name -> Subject aliasing in the outer self-projection layer:\n%s", ddl)
	}
}

func TestBuildViewFromDSL_SelfProjectionPreservesDerivedTraversalAlias(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	leadSchema := buildTestSchema("Lead", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Company", Type: constants.FieldTypeString},
		fkField("AssociateLead__c", "Leads", "Lead"),
		{
			APIName:     "ComputedX__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("'computed'"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
		{
			APIName:     "AssociateLeadComputed__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("AssociateLead__r.ComputedX__c"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	ddl := buildViewForTest(t, "Lead", map[string]*models.ObjectMetadata{
		"Lead": leadSchema,
	}, []models.FieldMetadata{leadSchema.Fields[3], leadSchema.Fields[4]})

	if !strings.Contains(ddl, "LEFT JOIN (") {
		t.Fatalf("DDL must use a derived inner projection for AssociateLead__r.ComputedX__c:\n%s", ddl)
	}
	if !strings.Contains(ddl, "j_leads.`ComputedX__c` AS `AssociateLeadComputed__c`") {
		t.Fatalf("DDL must preserve the derived AssociateLead__r traversal alias in the outer self-projection layer:\n%s", ddl)
	}
}

func TestValidateFormulaForObject_SelfRefLogicalNameAlias(t *testing.T) {
	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Subject", Type: constants.FieldTypeString, IsNameField: true},
		fkField("ParentId", "Parent", "ObjA"),
	})
	schemas := map[string]*models.ObjectMetadata{"ObjA": objASchema}

	validateFormulaForTestOK(t, "ObjA", schemas, "Parent.Name")
	validateFormulaForTestOK(t, "ObjA", schemas, "Parent.Parent.Name")
}

func TestBuildViewFromDSL_SelfRefLogicalActiveComputedNameUsesProjection(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("ParentId", "Parent", "ObjA"),
		{
			APIName:     "Subject",
			Type:        constants.FieldTypeFormula,
			IsNameField: true,
			Formula:     svcTestStrPtr("'computed subject'"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
		{
			APIName:     "ParentName__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Parent.Name"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})
	schemas := map[string]*models.ObjectMetadata{"ObjA": objASchema}

	validateFormulaForTestOK(t, "ObjA", schemas, "Parent.Name")
	ddl := buildViewForTest(t, "ObjA", schemas, []models.FieldMetadata{objASchema.Fields[2], objASchema.Fields[3]})

	if !strings.Contains(ddl, ") j_parent ON L0.`ParentId` = j_parent.`Id`") {
		t.Fatalf("DDL must read logical Parent.Name through the self-projection layer when Name resolves to an active-computed field:\n%s", ddl)
	}
	if !strings.Contains(ddl, "j_parent.`Subject` AS `ParentName__c`") {
		t.Fatalf("DDL must lower logical Parent.Name to active-computed Subject on the parent projection:\n%s", ddl)
	}
}

func TestBuildViewFromDSL_DeepSelfRefLogicalActiveComputedNameUsesProjection(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("ParentId", "Parent", "ObjA"),
		{
			APIName:     "Subject",
			Type:        constants.FieldTypeFormula,
			IsNameField: true,
			Formula:     svcTestStrPtr("'computed subject'"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
		{
			APIName:     "GrandparentName__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Parent.Parent.Name"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})
	schemas := map[string]*models.ObjectMetadata{"ObjA": objASchema}

	validateFormulaForTestOK(t, "ObjA", schemas, "Parent.Parent.Name")
	ddl := buildViewForTest(t, "ObjA", schemas, []models.FieldMetadata{objASchema.Fields[2], objASchema.Fields[3]})

	if !strings.Contains(ddl, ".`Subject` AS `GrandparentName__c`") {
		t.Fatalf("DDL must lower logical deep Parent.Parent.Name to active-computed Subject on the final projection:\n%s", ddl)
	}
}

// TestValidateSelfReferentialActiveComputedParity_OneHop verifies that the
// authoring-time validation path accepts the same 1-hop self-ref
// active-computed read that the runtime build path supports.
func TestValidateSelfReferentialActiveComputedParity_OneHop(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("ParentId", "Parent", "ObjA"),
		{
			APIName:     "ComputedX__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("'computed'"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
		{
			APIName:     "ParentComputed__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Parent.ComputedX__c"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	schemas := map[string]*models.ObjectMetadata{"ObjA": objASchema}

	validateFormulaForTestOK(t, "ObjA", schemas, "Parent.ComputedX__c")
	buildViewForTest(t, "ObjA", schemas, []models.FieldMetadata{objASchema.Fields[2], objASchema.Fields[3]})
}

func TestValidateFormulaForObject_SystemPseudoRelationshipParityOwner(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	userSchema := buildTestSchema("User", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString, IsNameField: true},
	})
	accountSchema := buildTestSchema("Account", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "OwnerId", Type: constants.FieldTypeReference},
		{
			APIName:     "OwnerName__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Owner.Name"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	schemas := map[string]*models.ObjectMetadata{
		"Account": accountSchema,
		"User":    userSchema,
	}

	validateFormulaForTestOK(t, "Account", schemas, "Owner.Name")
	ddl := buildViewForTest(t, "Account", schemas, []models.FieldMetadata{accountSchema.Fields[2]})
	if !strings.Contains(ddl, "LEFT JOIN `user`") {
		t.Fatalf("DDL must JOIN User for Owner.Name when OwnerId is only implied by platform metadata:\n%s", ddl)
	}
}

func TestValidateFormulaForObject_SystemPseudoRelationshipParityRecordType(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	recordTypeSchema := &models.ObjectMetadata{
		APIName: "RecordType",
		ReadSource: &models.ObjectReadSourceConfig{
			Kind:          "physical_table",
			PhysicalTable: "_System_RecordType",
		},
		Fields: []models.FieldMetadata{
			{APIName: "Id", Type: constants.FieldTypeString},
			{APIName: "Name", Type: constants.FieldTypeString, IsNameField: true},
		},
	}
	pocSchema := buildTestSchema("POC__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "RecordTypeId", Type: constants.FieldTypeReference},
		{
			APIName:     "RecordTypeName__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("RecordType.Name"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	schemas := map[string]*models.ObjectMetadata{
		"POC__c":     pocSchema,
		"RecordType": recordTypeSchema,
	}

	validateFormulaForTestOK(t, "POC__c", schemas, "RecordType.Name")
	ddl := buildViewForTest(t, "POC__c", schemas, []models.FieldMetadata{pocSchema.Fields[2]})
	if !strings.Contains(ddl, "LEFT JOIN `_System_RecordType`") {
		t.Fatalf("DDL must JOIN _System_RecordType for RecordType.Name when RecordTypeId is only implied by platform metadata:\n%s", ddl)
	}
}

func TestValidateFormulaForField_SelfEditUsesRuntimeDAG(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("ParentId", "Parent", "ObjA"),
		{
			APIName:     "ComputedX__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("'computed'"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	err := validateFormulaForFieldTestError(
		t,
		"ObjA",
		"ComputedX__c",
		map[string]*models.ObjectMetadata{"ObjA": objASchema},
		"Parent.ComputedX__c",
	)
	if !strings.Contains(err.Error(), "circular dependency") {
		t.Fatalf("ValidateFormulaForField error = %q, want circular dependency", err.Error())
	}
}

// TestBuildViewFromDSL_SelfRefBaseFieldSucceeds verifies that a 1-hop self-ref
// JOIN that reads only a physical base field (e.g. Parent.Name) is not rejected —
// intermediate self-refs reading physical fields are safe because joinTargetForFields
// returns the physical table, not v_<obj>.
func TestBuildViewFromDSL_SelfRefBaseFieldSucceeds(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	// ObjA has a self-ref and reads only the physical Name field — no active-computed reads.
	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
		fkField("ParentId", "Parent", "ObjA"),
		// Reads Parent.Name — a physical field. Safe self-ref.
		{
			APIName:     "ParentName__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Parent.Name"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	formulaFields := []models.FieldMetadata{objASchema.Fields[3]}
	ddl := buildViewForTest(t, "ObjA", map[string]*models.ObjectMetadata{
		"ObjA": objASchema,
	}, formulaFields)

	// The JOIN should target the physical obja table, not v_obja.
	if strings.Contains(ddl, "LEFT JOIN `v_obja`") {
		t.Errorf("DDL incorrectly routes physical self-ref Name read to v_obja:\n%s", ddl)
	}
}

// TestBuildViewFromDSL_CustomLookupDerivedTraversalNameSucceeds verifies the
// #1770 shape: an active formula uses the generic field-derived relationship
// name (`AssociateLead__r`) even though metadata carries a different explicit
// RelationshipName (`Leads`).
func TestBuildViewFromDSL_CustomLookupDerivedTraversalNameSucceeds(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	leadSchema := buildTestSchema("Lead", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Company", Type: constants.FieldTypeString},
		fkField("AssociateLead__c", "Leads", "Lead"),
		{
			APIName:     "Associate_Lead_Company__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("AssociateLead__r.Company"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	formulaFields := []models.FieldMetadata{leadSchema.Fields[3]}
	ddl := buildViewForTest(t, "Lead", map[string]*models.ObjectMetadata{
		"Lead": leadSchema,
	}, formulaFields)

	if !strings.Contains(ddl, "LEFT JOIN `lead`") {
		t.Fatalf("DDL does not include self-lookup join for AssociateLead__r.Company:\n%s", ddl)
	}
	if !strings.Contains(ddl, "AS `Associate_Lead_Company__c`") {
		t.Fatalf("DDL does not project AssociateLead__r.Company into Associate_Lead_Company__c:\n%s", ddl)
	}
}

// TestBuildViewFromDSL_CustomLookupDerivedTraversalNameDeepJoinSucceeds
// verifies the same mismatch shape also works through the deep-join planner.
func TestBuildViewFromDSL_CustomLookupDerivedTraversalNameDeepJoinSucceeds(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	userSchema := buildTestSchema("User", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
	})

	leadSchema := buildTestSchema("Lead", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Company", Type: constants.FieldTypeString},
		fkField("OwnerId", "Owner", "User"),
		fkField("AssociateLead__c", "Leads", "Lead"),
		{
			APIName:     "Associate_Lead_Owner_Name__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("AssociateLead__r.Owner.Name"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	formulaFields := []models.FieldMetadata{leadSchema.Fields[4]}
	ddl := buildViewForTest(t, "Lead", map[string]*models.ObjectMetadata{
		"Lead": leadSchema,
		"User": userSchema,
	}, formulaFields)

	if !strings.Contains(ddl, "LEFT JOIN `lead`") || !strings.Contains(ddl, "LEFT JOIN `user`") {
		t.Fatalf("DDL does not include deep-join chain for AssociateLead__r.Owner.Name:\n%s", ddl)
	}
	if !strings.Contains(ddl, "AS `Associate_Lead_Owner_Name__c`") {
		t.Fatalf("DDL does not project AssociateLead__r.Owner.Name into Associate_Lead_Owner_Name__c:\n%s", ddl)
	}
}

// TestBuildViewFromDSL_DeepSelfRefActiveComputedUsesInnerProjection verifies
// that a deep self-referential chain (e.g. Parent.Parent.ComputedX__c) reads
// the final active-computed field from a derived inner projection instead of
// failing closed or recursively joining `v_<obj>`.
func TestBuildViewFromDSL_DeepSelfRefActiveComputedUsesInnerProjection(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("ParentId", "Parent", "ObjA"),
		// ComputedX__c is active-computed on ObjA.
		{
			APIName:     "ComputedX__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("'computed'"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
		// Deep self-ref: Parent.Parent.ComputedX__c — last hop is ObjA, reads active-computed.
		{
			APIName:     "GrandparentComputed__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Parent.Parent.ComputedX__c"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	formulaFields := []models.FieldMetadata{objASchema.Fields[2], objASchema.Fields[3]}
	ddl := buildViewForTest(t, "ObjA", map[string]*models.ObjectMetadata{
		"ObjA": objASchema,
	}, formulaFields)

	if strings.Contains(ddl, "LEFT JOIN `v_obja`") {
		t.Fatalf("DDL must not recursively JOIN v_obja for deep self-ref active-computed reads:\n%s", ddl)
	}
	if !strings.Contains(ddl, "LEFT JOIN (`") && !strings.Contains(ddl, "LEFT JOIN (") {
		t.Fatalf("DDL must use a derived inner projection at the final self-ref hop:\n%s", ddl)
	}
	if !strings.Contains(ddl, "LEFT JOIN `obja` dj_parent ON L0.`ParentId` = dj_parent.`Id`") {
		t.Fatalf("DDL must keep the intermediate self hop on the physical table:\n%s", ddl)
	}
	if !strings.Contains(ddl, ") dj_parent_2 ON dj_parent.`ParentId` = dj_parent_2.`Id`") {
		t.Fatalf("DDL must route the final active-computed hop through a derived inner projection alias:\n%s", ddl)
	}
	if !strings.Contains(ddl, "dj_parent_2.`ComputedX__c` AS `GrandparentComputed__c`") {
		t.Fatalf("DDL must read ComputedX__c from the grandparent inner projection alias:\n%s", ddl)
	}
}

func TestBuildViewFromDSL_DeepSelfReturnActiveComputedUsesInnerProjection(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("OwnerId", "Owner", "User"),
		{
			APIName:     "ComputedX__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("'computed'"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
		{
			APIName:     "OwnerHomeComputed__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Owner.HomeObj.ComputedX__c"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})
	userSchema := buildTestSchema("User", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("HomeObjId", "HomeObj", "ObjA"),
	})

	ddl := buildViewForTest(t, "ObjA", map[string]*models.ObjectMetadata{
		"ObjA": objASchema,
		"User": userSchema,
	}, []models.FieldMetadata{objASchema.Fields[2], objASchema.Fields[3]})

	if strings.Contains(ddl, "LEFT JOIN `v_obja`") {
		t.Fatalf("DDL must not recursively JOIN v_obja for deep self-return active-computed reads:\n%s", ddl)
	}
	if !strings.Contains(ddl, "LEFT JOIN (") {
		t.Fatalf("DDL must use a derived inner projection for the final self-return hop:\n%s", ddl)
	}
	if !strings.Contains(ddl, "dj_homeobj.`ComputedX__c` AS `OwnerHomeComputed__c`") {
		t.Fatalf("DDL must read ComputedX__c from the final self-return projection alias:\n%s", ddl)
	}
}

// TestValidateSelfReferentialActiveComputedParity_Deep verifies that the
// authoring-time validation path accepts the same deep self-ref
// active-computed read that the runtime build path supports.
func TestValidateSelfReferentialActiveComputedParity_Deep(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("ParentId", "Parent", "ObjA"),
		{
			APIName:     "ComputedX__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("'computed'"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
		{
			APIName:     "GrandparentComputed__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Parent.Parent.ComputedX__c"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	schemas := map[string]*models.ObjectMetadata{"ObjA": objASchema}

	validateFormulaForTestOK(t, "ObjA", schemas, "Parent.Parent.ComputedX__c")
	buildViewForTest(t, "ObjA", schemas, []models.FieldMetadata{objASchema.Fields[2], objASchema.Fields[3]})
}

func TestBuildViewFromDSL_Issue1396DeepRelationshipExamples(t *testing.T) {
	profileSchema := buildTestSchema("Profile", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
	})
	userSchema := buildTestSchema("User", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Alias", Type: constants.FieldTypeString},
		fkField("ProfileId", "Profile", "Profile"),
		fkField("ManagerId", "Manager", "User"),
	})
	accountSchema := buildTestSchema("Account", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "BillingCountryCode", Type: constants.FieldTypeString},
		{APIName: "Region__c", Type: constants.FieldTypeString},
	})
	opportunitySchema := buildTestSchema("Opportunity", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("AccountId", "Account", "Account"),
	})
	pocSchema := buildTestSchema("poc__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("CreatedById", "CreatedBy", "User"),
		fkField("Sales_Owner__c", "Sales_Owner__r", "User"),
		fkField("OpportunityId", "Opportunity", "Opportunity"),
		activeFormulaField("CreatedBy_Profile_Name__c", "CreatedBy.Profile.Name"),
		activeFormulaField("Sales_Owner_Manager_Alias__c", "Sales_Owner__r.Manager.Alias"),
		activeFormulaField("Opportunity_Account_Country__c", "Opportunity.Account.BillingCountryCode"),
		activeFormulaField("Opportunity_Account_Region__c", "Opportunity.Account.Region__c"),
	})
	leadSchema := buildTestSchema("Lead", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("OwnerId", "Owner", "User"),
		activeFormulaField("Owner_Profile_Name__c", "Owner.Profile.Name"),
	})
	orderSchema := buildTestSchema("Order", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("OwnerId", "Owner", "User"),
		activeFormulaField("Owner_Profile_Name__c", "Owner.Profile.Name"),
	})

	schemas := map[string]*models.ObjectMetadata{
		"Profile":     profileSchema,
		"User":        userSchema,
		"Account":     accountSchema,
		"Opportunity": opportunitySchema,
		"poc__c":      pocSchema,
		"Lead":        leadSchema,
		"Order":       orderSchema,
	}

	leadDDL := buildViewForTest(t, "Lead", schemas, []models.FieldMetadata{leadSchema.Fields[2]})
	if !strings.Contains(leadDDL, "LEFT JOIN `user`") || !strings.Contains(leadDDL, "LEFT JOIN `profile`") {
		t.Fatalf("Lead DDL does not include Owner.Profile deep join chain:\n%s", leadDDL)
	}
	if !strings.Contains(leadDDL, "dj_profile.`Name` AS `Owner_Profile_Name__c`") {
		t.Fatalf("Lead DDL does not project Owner.Profile.Name:\n%s", leadDDL)
	}

	orderDDL := buildViewForTest(t, "Order", schemas, []models.FieldMetadata{orderSchema.Fields[2]})
	if !strings.Contains(orderDDL, "dj_profile.`Name` AS `Owner_Profile_Name__c`") {
		t.Fatalf("Order DDL does not project Owner.Profile.Name:\n%s", orderDDL)
	}

	pocDDL := buildViewForTest(t, "poc__c", schemas, []models.FieldMetadata{
		pocSchema.Fields[4],
		pocSchema.Fields[5],
		pocSchema.Fields[6],
		pocSchema.Fields[7],
	})
	for _, want := range []string{
		"dj_profile.`Name` AS `CreatedBy_Profile_Name__c`",
		"dj_manager.`Alias` AS `Sales_Owner_Manager_Alias__c`",
		"dj_account.`BillingCountryCode` AS `Opportunity_Account_Country__c`",
		"dj_account_2.`Region__c` AS `Opportunity_Account_Region__c`",
	} {
		if !strings.Contains(pocDDL, want) {
			t.Fatalf("poc__c DDL missing %q:\n%s", want, pocDDL)
		}
	}
}

func TestBuildViewFromDSL_Issue1396DedupesDeepRelationshipChainsCaseInsensitive(t *testing.T) {
	profileSchema := buildTestSchema("Profile", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
	})
	userSchema := buildTestSchema("User", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("ProfileId", "Profile", "Profile"),
	})
	leadSchema := buildTestSchema("Lead", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("OwnerId", "Owner", "User"),
		activeFormulaField("Owner_Profile_Name__c", "Owner.Profile.Name"),
		activeFormulaField("Owner_Profile_Name_Lower__c", "owner.profile.name"),
	})

	ddl := buildViewForTest(t, "Lead", map[string]*models.ObjectMetadata{
		"Profile": profileSchema,
		"User":    userSchema,
		"Lead":    leadSchema,
	}, []models.FieldMetadata{leadSchema.Fields[2], leadSchema.Fields[3]})

	if got := strings.Count(ddl, "LEFT JOIN `user`"); got != 1 {
		t.Fatalf("Lead DDL should reuse one Owner->User join chain regardless of formula casing; got %d joins:\n%s", got, ddl)
	}
	if got := strings.Count(ddl, "LEFT JOIN `profile`"); got != 1 {
		t.Fatalf("Lead DDL should reuse one User->Profile join chain regardless of formula casing; got %d joins:\n%s", got, ddl)
	}
}

func TestValidateFormulaForObject_Issue1396DeepRelationshipExamples(t *testing.T) {
	profileSchema := buildTestSchema("Profile", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
	})
	userSchema := buildTestSchema("User", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Alias", Type: constants.FieldTypeString},
		fkField("ProfileId", "Profile", "Profile"),
		fkField("ManagerId", "Manager", "User"),
	})
	accountSchema := buildTestSchema("Account", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "BillingCountryCode", Type: constants.FieldTypeString},
		{APIName: "Region__c", Type: constants.FieldTypeString},
	})
	opportunitySchema := buildTestSchema("Opportunity", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("AccountId", "Account", "Account"),
	})
	leadSchema := buildTestSchema("Lead", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("OwnerId", "Owner", "User"),
	})
	pocSchema := buildTestSchema("poc__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("CreatedById", "CreatedBy", "User"),
		fkField("Sales_Owner__c", "Sales_Owner__r", "User"),
		fkField("OpportunityId", "Opportunity", "Opportunity"),
	})
	orderSchema := buildTestSchema("Order", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("OwnerId", "Owner", "User"),
	})

	schemas := map[string]*models.ObjectMetadata{
		"Profile":     profileSchema,
		"User":        userSchema,
		"Account":     accountSchema,
		"Opportunity": opportunitySchema,
		"Lead":        leadSchema,
		"poc__c":      pocSchema,
		"Order":       orderSchema,
	}

	validateFormulaForTestOK(t, "Lead", schemas, "Owner.Profile.Name")
	validateFormulaForTestOK(t, "Lead", schemas, "owner.profile.name")
	validateFormulaForTestOK(t, "Order", schemas, "Owner.Profile.Name")
	validateFormulaForTestOK(t, "poc__c", schemas, "CreatedBy.Profile.Name")
	validateFormulaForTestOK(t, "poc__c", schemas, "Sales_Owner__r.Manager.Alias")
	validateFormulaForTestOK(t, "poc__c", schemas, "Opportunity.Account.BillingCountryCode")
	validateFormulaForTestOK(t, "poc__c", schemas, "Opportunity.Account.Region__c")
}

func TestCompileFormulaPreview_Issue1396DeepCrossRefReturnsResolvedSQL(t *testing.T) {
	profileSchema := buildTestSchema("Profile", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
	})
	userSchema := buildTestSchema("User", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("ProfileId", "Profile", "Profile"),
	})
	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("OwnerId", "Owner", "User"),
	})

	svc := &FormulaViewService{
		metadata: newFakeMetadata(map[string]*models.ObjectMetadata{
			"Profile": profileSchema,
			"User":    userSchema,
			"ObjA":    objASchema,
		}),
	}

	sql, err := svc.CompileFormulaPreview(context.Background(), "ObjA", "Owner.Profile.Name")
	if err != nil {
		t.Fatalf("CompileFormulaPreview() error = %v", err)
	}
	if want := "dj_profile.`Name`"; sql != want {
		t.Fatalf("CompileFormulaPreview() = %q, want %q", sql, want)
	}
}

// TestBuildViewFromDSL_DeepSelfRefBaseFieldSucceeds verifies that a deep self-ref
// chain that reads a physical base field at the last hop (e.g. Parent.Parent.Name)
// is not rejected — only active-computed reads on the last self-ref hop are blocked.
func TestBuildViewFromDSL_DeepSelfRefBaseFieldSucceeds(t *testing.T) {
	mode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
		fkField("ParentId", "Parent", "ObjA"),
		// Deep self-ref: Parent.Parent.Name — reads physical Name, safe.
		{
			APIName:     "GrandparentName__c",
			Type:        constants.FieldTypeFormula,
			Formula:     svcTestStrPtr("Parent.Parent.Name"),
			FormulaMode: &mode,
			ReturnType:  &rt,
		},
	})

	formulaFields := []models.FieldMetadata{objASchema.Fields[3]}
	ddl := buildViewForTest(t, "ObjA", map[string]*models.ObjectMetadata{
		"ObjA": objASchema,
	}, formulaFields)

	// Neither hop should target a view.
	if strings.Contains(ddl, "LEFT JOIN `v_obja`") {
		t.Errorf("DDL incorrectly routes physical self-ref Name read to v_obja:\n%s", ddl)
	}
}

func TestResolveAggregateJoinSpec_ExplicitForeignKey(t *testing.T) {
	ctx := context.Background()
	issueSchema := buildTestSchema("Issue__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
	})
	workLogSchema := buildTestSchema("WorkLog__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("ParentIssue__c", "ParentIssue", "Issue__c"),
		{APIName: "Hours__c", Type: constants.FieldTypeDouble},
	})

	svc := &FormulaViewService{
		metadata: newFakeMetadata(map[string]*models.ObjectMetadata{
			"Issue__c":   issueSchema,
			"WorkLog__c": workLogSchema,
		}),
	}
	spec, err := svc.resolveAggregateJoinSpec(ctx, issueSchema, AggregateExpr{
		Func:        "SUM",
		ChildObject: "WorkLog__c",
		ChildField:  "Hours__c",
		ForeignKey:  "ParentIssue__c",
	}, 0)
	if err != nil {
		t.Fatalf("resolveAggregateJoinSpec() error = %v", err)
	}
	if spec.ForeignKey != "ParentIssue__c" {
		t.Fatalf("spec.ForeignKey = %q, want ParentIssue__c", spec.ForeignKey)
	}

	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "Issue__c",
		PhysicalTable: "issue__c",
		TableAlias:    "i",
		BaseColumns:   []string{"Id"},
		Formulas: []FormulaDefinition{{
			Name:       "EchoId__c",
			Expression: "Id",
			ReturnType: "Text",
		}},
		AggJoins: []AggregateJoinSpec{spec},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}
	if !strings.Contains(ddl, "`ParentIssue__c`") {
		t.Fatalf("DDL does not use explicit aggregate foreign key ParentIssue__c:\n%s", ddl)
	}
}

func TestResolveAggregateJoinSpec_AmbiguousForeignKey(t *testing.T) {
	ctx := context.Background()
	issueSchema := buildTestSchema("Issue__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
	})
	workLogSchema := buildTestSchema("WorkLog__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("ParentIssue__c", "ParentIssue", "Issue__c"),
		fkField("EscalatedIssue__c", "EscalatedIssue", "Issue__c"),
		{APIName: "Hours__c", Type: constants.FieldTypeDouble},
	})

	svc := &FormulaViewService{
		metadata: newFakeMetadata(map[string]*models.ObjectMetadata{
			"Issue__c":   issueSchema,
			"WorkLog__c": workLogSchema,
		}),
	}
	_, err := svc.resolveAggregateJoinSpec(ctx, issueSchema, AggregateExpr{
		Func:        "SUM",
		ChildObject: "WorkLog__c",
		ChildField:  "Hours__c",
	}, 0)
	if err == nil {
		t.Fatal("resolveAggregateJoinSpec() error = nil, want aggregate ambiguity failure")
	}
	if !strings.Contains(err.Error(), "multiple reference fields on WorkLog__c point to aggregate parent Issue__c") {
		t.Fatalf("resolveAggregateJoinSpec() error = %q, want WorkLog__c ambiguity", err.Error())
	}
}

func TestResolveAggregateJoinSpec_CountWildcard(t *testing.T) {
	ctx := context.Background()
	issueSchema := buildTestSchema("Issue__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
	})
	workLogSchema := buildTestSchema("WorkLog__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("ParentIssue__c", "ParentIssue", "Issue__c"),
		{APIName: "Hours__c", Type: constants.FieldTypeDouble},
	})

	svc := &FormulaViewService{
		metadata: newFakeMetadata(map[string]*models.ObjectMetadata{
			"Issue__c":   issueSchema,
			"WorkLog__c": workLogSchema,
		}),
	}
	spec, err := svc.resolveAggregateJoinSpec(ctx, issueSchema, AggregateExpr{
		Func:        "COUNT",
		ChildObject: "WorkLog__c",
		ChildField:  "*",
	}, 0)
	if err != nil {
		t.Fatalf("resolveAggregateJoinSpec() error = %v", err)
	}
	if spec.AggField != "*" {
		t.Fatalf("spec.AggField = %q, want *", spec.AggField)
	}

	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "Issue__c",
		PhysicalTable: "issue__c",
		TableAlias:    "i",
		BaseColumns:   []string{"Id"},
		Formulas: []FormulaDefinition{{
			Name:       "EchoId__c",
			Expression: "Id",
			ReturnType: "Text",
		}},
		AggJoins: []AggregateJoinSpec{spec},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}
	if !strings.Contains(ddl, "COUNT(*)") {
		t.Fatalf("DDL does not preserve COUNT(*) aggregate semantics:\n%s", ddl)
	}
}

func TestResolveAggregateJoinSpec_RejectsNonCountWildcard(t *testing.T) {
	ctx := context.Background()
	issueSchema := buildTestSchema("Issue__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
	})
	workLogSchema := buildTestSchema("WorkLog__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("ParentIssue__c", "ParentIssue", "Issue__c"),
		{APIName: "Hours__c", Type: constants.FieldTypeDouble},
	})

	svc := &FormulaViewService{
		metadata: newFakeMetadata(map[string]*models.ObjectMetadata{
			"Issue__c":   issueSchema,
			"WorkLog__c": workLogSchema,
		}),
	}
	_, err := svc.resolveAggregateJoinSpec(ctx, issueSchema, AggregateExpr{
		Func:        "SUM",
		ChildObject: "WorkLog__c",
		ChildField:  "*",
	}, 0)
	if err == nil {
		t.Fatal("resolveAggregateJoinSpec() error = nil, want wildcard aggregate rejection")
	}
	if !strings.Contains(err.Error(), "SUM aggregate does not support wildcard child fields") {
		t.Fatalf("resolveAggregateJoinSpec() error = %q, want wildcard rejection", err.Error())
	}
}

// ---------------------------------------------------------------------------
// Test E — IsNameField regression: sole-autonumber pack object (scope item 1)
// ---------------------------------------------------------------------------

// TestBuildCrossRefFieldAliasMap_SoleAutonumberUsesIsNameField is a regression test
// for the removal of any "count autonumber fields" heuristic (scope item 1, Test E).
//
// Order has TWO autonumber fields. Only OrderNumber has IsNameField=true.
// buildCrossRefFieldAliasMap must map "Order.Name" → "OrderNumber" by reading
// IsNameField, not by counting autonumber fields (which would fail for two).
func TestBuildCrossRefFieldAliasMap_SoleAutonumberUsesIsNameField(t *testing.T) {
	// Two autonumber fields: OrderNumber (IsNameField=true) and LineNumber (IsNameField=false).
	// A "sole-autonumber" heuristic would break here; IsNameField reads correctly.
	orderSchema := buildTestSchema("Order", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "OrderNumber", Type: constants.FieldTypeAutonumber, IsNameField: true},
		{APIName: "LineNumber", Type: constants.FieldTypeAutonumber, IsNameField: false},
		activeFormulaField("TaxTotal__c", "Amount * 0.1"),
	})

	svc := &FormulaViewService{
		db:       nil,
		metadata: newFakeMetadata(map[string]*models.ObjectMetadata{"Order": orderSchema}),
		logger:   noopLogger(),
		viewSet:  make(map[string]bool),
	}

	joins := []JoinSpec{
		{
			Alias:            "j_order",
			TargetObject:     "Order",
			TargetTable:      "order",
			SourceField:      "OrderId",
			TargetField:      "Id",
			RelationshipName: "Order",
		},
	}

	aliasMap := svc.buildCrossRefFieldAliasMap(context.Background(), joins)

	// Must map logical "Name" to "OrderNumber" via IsNameField=true, not by counting.
	foundAlias := false
	for k, v := range aliasMap {
		if strings.EqualFold(v, "OrderNumber") && strings.HasSuffix(strings.ToLower(k), ".name") {
			foundAlias = true
			break
		}
	}
	if !foundAlias {
		t.Errorf("buildCrossRefFieldAliasMap = %v: expected a key ending in .Name mapped to OrderNumber (reads IsNameField, not autonumber count)", aliasMap)
	}

	// Must NOT map to LineNumber (the non-IsNameField autonumber).
	for k, v := range aliasMap {
		if strings.EqualFold(v, "LineNumber") && strings.HasSuffix(strings.ToLower(k), ".name") {
			t.Errorf("buildCrossRefFieldAliasMap mapped .Name → LineNumber (wrong field): key=%s val=%s", k, v)
		}
	}
}

func TestBuildViewFromDSLReadsRelationshipCompoundFieldFromMembers(t *testing.T) {
	leadSchema := buildTestSchema("Lead", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{
			APIName:      "Name",
			Type:         constants.FieldTypeString,
			IsNameField:  true,
			IsVirtual:    true,
			VirtualKind:  constants.VirtualFieldCompound,
			IsCompound:   true,
			ComposedFrom: []string{"FirstName", "LastName"},
		},
		{APIName: "FirstName", PhysicalName: "first_name", Type: constants.FieldTypeString},
		{APIName: "LastName", Type: constants.FieldTypeString},
	})
	campaignMemberSchema := buildTestSchema("CampaignMember", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		fkField("LeadId", "Lead", "Lead"),
		activeFormulaField("Name", "Lead.Name"),
	})
	svc := &FormulaViewService{
		metadata: newFakeMetadata(map[string]*models.ObjectMetadata{
			"Lead":           leadSchema,
			"CampaignMember": campaignMemberSchema,
		}),
		logger:  noopLogger(),
		viewSet: make(map[string]bool),
	}

	ddl, err := svc.buildViewFromDSL(
		context.Background(),
		"CampaignMember",
		campaignMemberSchema,
		[]models.FieldMetadata{campaignMemberSchema.Fields[2]},
		0,
		true,
	)
	if err != nil {
		t.Fatal(err)
	}
	want := "NULLIF(TRIM(CONCAT_WS(' ', NULLIF(TRIM(j_lead.`first_name`), ''), NULLIF(TRIM(j_lead.`LastName`), ''))), '') AS `Name`"
	if !strings.Contains(ddl, want) {
		t.Fatalf("compound relationship DDL missing %q:\n%s", want, ddl)
	}
	if strings.Contains(ddl, "j_lead.`Name`") {
		t.Fatalf("compound relationship DDL reads stale physical Name:\n%s", ddl)
	}
}

func TestBuildCrossRefFieldExpressionMapFailsClosedOnMissingCompoundMember(t *testing.T) {
	leadSchema := buildTestSchema("Lead", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{
			APIName:      "Name",
			Type:         constants.FieldTypeString,
			IsVirtual:    true,
			VirtualKind:  constants.VirtualFieldCompound,
			IsCompound:   true,
			ComposedFrom: []string{"FirstName", "LastName"},
		},
		{APIName: "FirstName", Type: constants.FieldTypeString},
	})
	svc := &FormulaViewService{
		metadata: newFakeMetadata(map[string]*models.ObjectMetadata{"Lead": leadSchema}),
	}
	_, err := svc.buildCrossRefFieldExpressionMap(context.Background(), []JoinSpec{{
		Alias:            "j_lead",
		TargetObject:     "Lead",
		TargetTable:      "lead",
		RelationshipName: "Lead",
	}})
	if err == nil || !strings.Contains(err.Error(), "member LastName is absent") {
		t.Fatalf("missing compound member error = %v", err)
	}
}

// TestBuildViewFromDSL_OrderWithTwoAutonumbersBuildsViewUsingIsNameField verifies that
// a full view build for Order (two autonumber fields, one IsNameField=true) succeeds
// and the emitted DDL uses the correct name-field APIName in cross-ref resolution.
func TestBuildViewFromDSL_OrderWithTwoAutonumbersBuildsViewUsingIsNameField(t *testing.T) {
	orderSchema := buildTestSchema("Order", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "OrderNumber", Type: constants.FieldTypeAutonumber, IsNameField: true},
		{APIName: "LineNumber", Type: constants.FieldTypeAutonumber, IsNameField: false},
		activeFormulaField("TaxTotal__c", "1"),
	})

	ddl := buildViewForTest(t, "Order", map[string]*models.ObjectMetadata{"Order": orderSchema}, []models.FieldMetadata{orderSchema.Fields[3]})

	// View must build without error and include the formula projection.
	if !strings.Contains(ddl, "TaxTotal__c") {
		t.Errorf("DDL missing TaxTotal__c formula projection:\n%s", ddl)
	}
}

// ---------------------------------------------------------------------------
// Regression test: backtick-wrapped PhysicalName must not produce invalid SQL
// ---------------------------------------------------------------------------

// TestBuildViewFromDSL_BacktickedPhysicalNameDoesNotProduceDoubleBracket is a regression
// test for the Codex-Review blocker found in PR #1161.
//
// Bootstrap metadata (backend/internal/bootstrap/system_tables.json) stores some
// Salesforce-facing aliases as backtick-wrapped PhysicalName values, e.g.:
//
//	Notification.recipient_id → PhysicalName="`RecipientId`"
//
// persistence.actualColumnName (the SSoT) interprets backtick-wrapped values as
// "the DB column IS the API name" and returns apiName. analyticsPhysicalColumnName
// must follow the same rule: it must NOT forward the backtick-wrapped string into
// BaseColumnAliases, which would produce invalid SQL like t.“RecipientId“.
func TestBuildViewFromDSL_BacktickedPhysicalNameDoesNotProduceDoubleBracket(t *testing.T) {
	// Simulate a bootstrap object with a backtick-wrapped PhysicalName.
	notifSchema := buildTestSchema("Notification", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		// PhysicalName is stored as backtick-wrapped in bootstrap metadata.
		{APIName: "RecipientId", Type: constants.FieldTypeString, PhysicalName: "`RecipientId`"},
		activeFormulaField("Display__c", "RecipientId"),
	})

	ddl := buildViewForTest(t, "Notification",
		map[string]*models.ObjectMetadata{"Notification": notifSchema},
		[]models.FieldMetadata{notifSchema.Fields[2]},
	)

	// No double-backtick must appear — that is invalid SQL.
	if strings.Contains(ddl, "``") {
		t.Errorf("DDL contains double-backtick (invalid SQL from raw backtick PhysicalName):\n%s", ddl)
	}
	// RecipientId must appear as a plain base column, not aliased from a backtick value.
	if !strings.Contains(ddl, "t.`RecipientId`") {
		t.Errorf("DDL must contain t.`RecipientId` as a plain base column:\n%s", ddl)
	}
}

// ---------------------------------------------------------------------------
// Regression tests — base-object bare Name refs on non-physical-Name objects
// (issue #1079)
// ---------------------------------------------------------------------------

// TestBuildViewFromDSL_MultiLayerBaseObjectNameRef verifies that when a formula
// on a sole-autonumber object (no physical Name column, IsNameField on OrderNumber)
// is in an outer formula layer and also directly references "Name", the emitted DDL
// exposes t.`OrderNumber` AS `Name` in the L0 inner projection so outer layers can
// reference L0.`Name` without an unknown-column error.
func TestBuildViewFromDSL_MultiLayerBaseObjectNameRef(t *testing.T) {
	// Order__c: sole-autonumber display field, no physical Name column.
	orderSchema := buildTestSchema("Order__c", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "OrderNumber", Type: constants.FieldTypeString, IsNameField: true},
		activeFormulaField("EchoName__c", "Name"),
		// DoubleName__c depends on EchoName__c (pushes to outer layer) AND references Name directly.
		activeFormulaField("DoubleName__c", "EchoName__c & \" - \" & Name"),
	})
	schemas := map[string]*models.ObjectMetadata{"Order__c": orderSchema}

	ddl := buildViewForTest(t, "Order__c", schemas, []models.FieldMetadata{
		orderSchema.Fields[2], // EchoName__c
		orderSchema.Fields[3], // DoubleName__c
	})

	// L0 inner projection must expose Name as an alias for the physical column.
	if !strings.Contains(ddl, "AS `Name`") {
		t.Errorf("inner projection must expose `Name` alias so outer layers can reference L0.`Name`:\n%s", ddl)
	}
	// L0 must reference the physical column, not a non-existent t.`Name`.
	if strings.Contains(ddl, "t.`Name`") {
		t.Errorf("DDL must not contain non-existent t.`Name` reference:\n%s", ddl)
	}
	// The outer formula column must be present.
	if !strings.Contains(ddl, "`DoubleName__c`") {
		t.Errorf("DDL must contain DoubleName__c outer-layer formula:\n%s", ddl)
	}
}

// TestBuildViewFromDSL_BaseObjectNameRefUsesPhysicalName verifies that when an object's
// IsNameField has a PhysicalName override (source-backed metadata), logicalNamePhysicalAlias
// returns the physical column name rather than the API name, so the base projection emits
// t.`<physCol>` AS `Name` instead of t.`<apiName>` AS `Name`.
func TestBuildViewFromDSL_BaseObjectNameRefUsesPhysicalName(t *testing.T) {
	// CaseObj: CaseNumber is the display field with a PhysicalName override.
	caseSchema := buildTestSchema("CaseObj", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "CaseNumber", Type: constants.FieldTypeString, IsNameField: true, PhysicalName: "case_num"},
		activeFormulaField("Echo__c", "Name"),
	})
	schemas := map[string]*models.ObjectMetadata{"CaseObj": caseSchema}

	ddl := buildViewForTest(t, "CaseObj", schemas, []models.FieldMetadata{
		caseSchema.Fields[2], // Echo__c
	})

	// Must emit the physical column, not the API name.
	if !strings.Contains(ddl, "t.`case_num`") {
		t.Errorf("DDL must reference physical column t.`case_num` when PhysicalName is set:\n%s", ddl)
	}
	// Must not reference the API name as a physical column.
	if strings.Contains(ddl, "t.`CaseNumber`") {
		t.Errorf("DDL must not reference t.`CaseNumber` (API name) when PhysicalName is set:\n%s", ddl)
	}
}

// TestBuildViewFromDSL_BaseObjectNameRefActiveComputedSkipsPhysicalAlias verifies that
// when an object's IsNameField has FormulaMode=active (i.e., Name is computed by the
// formula view itself), a bare "Name" formula reference resolves through the computed
// field rather than an invalid physical column reference.
//
// Regression for issue #1079 (R3 path): both t.`Subject` AS `Name` (wrong physical
// alias injection) and t.`Name` (non-existent physical column) must be absent. The
// correct DDL places Subject in L0 and Echo__c in L1 referencing L0.`Subject`.
func TestBuildViewFromDSL_BaseObjectNameRefActiveComputedSkipsPhysicalAlias(t *testing.T) {
	// ObjA: Subject is the display field, but it is active-computed — not a physical column.
	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		activeFormulaFieldWithIsNameField("Subject", "\"computed subject\""),
		activeFormulaField("Echo__c", "Name"),
	})
	schemas := map[string]*models.ObjectMetadata{"ObjA": objASchema}

	ddl := buildViewForTest(t, "ObjA", schemas, []models.FieldMetadata{
		objASchema.Fields[1], // Subject (active-computed IsNameField)
		objASchema.Fields[2], // Echo__c
	})

	// Must NOT emit t.`Subject` AS `Name` — Subject is active-computed, not a
	// physical column, so it cannot be aliased as a base-table projection for Name.
	if strings.Contains(ddl, "t.`Subject` AS `Name`") {
		t.Errorf("DDL must not project t.`Subject` AS `Name` when Subject is active-computed:\n%s", ddl)
	}
	// Must NOT emit t.`Name` — there is no physical Name column on this object.
	// Prior to the fix, Echo__c = Name compiled to t.`Name` in L0, which is invalid SQL.
	if strings.Contains(ddl, "t.`Name`") {
		t.Errorf("DDL must not reference non-existent physical column t.`Name`:\n%s", ddl)
	}
	// Echo__c must reference the computed Subject column from the inner layer,
	// not the non-existent physical Name column.
	if !strings.Contains(ddl, "L0.`Subject`") {
		t.Errorf("DDL must reference L0.`Subject` for the Echo__c formula (bare Name resolves through active-computed display field):\n%s", ddl)
	}
	// The formula Echo__c must be present.
	if !strings.Contains(ddl, "`Echo__c`") {
		t.Errorf("DDL must contain Echo__c formula:\n%s", ddl)
	}
}

func TestBuildViewFromDSL_BaseObjectNameRefActiveWriteMaterializedUsesPhysicalAlias(t *testing.T) {
	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		activeWriteMaterializedFormulaFieldWithIsNameField("Subject", "\"stored subject\""),
		activeFormulaField("Echo__c", "Name"),
	})
	schemas := map[string]*models.ObjectMetadata{"ObjA": objASchema}

	ddl := buildViewForTest(t, "ObjA", schemas, []models.FieldMetadata{
		objASchema.Fields[1],
		objASchema.Fields[2],
	})

	if !strings.Contains(ddl, "t.`Subject` AS `Name`") {
		t.Errorf("DDL must project stored active write_materialized display field as Name:\n%s", ddl)
	}
	if strings.Contains(ddl, "L0.`Subject`") {
		t.Errorf("DDL must keep bare Name references on the physical alias path, not rewrite through computed Subject:\n%s", ddl)
	}
	if strings.Contains(ddl, "t.`Name`") {
		t.Errorf("DDL must not reference non-existent physical column t.`Name`:\n%s", ddl)
	}
}

// TestBuildViewFromDSL_ActiveComputedNameRewriteDoesNotCorruptCrossRef verifies that
// the bare-Name rewrite (Name → active-computed display field) operates at the AST level
// and does NOT corrupt cross-ref Name references (e.g. Parent.Name → Parent.Subject).
//
// Regression for issue #1079 (R3 path): the text-based rewriteBareFieldRef treated '.'
// as a word-boundary character, so in 'Name & Parent.Name' the .Name suffix fired the
// word-boundary check and was incorrectly rewritten to .Subject. The AST-based rewrite
// replaces only *FieldRef{Name:"Name"} nodes (bare references), leaving *CrossRef and
// *DeepCrossRef nodes (dotted references) untouched.
func TestBuildViewFromDSL_ActiveComputedNameRewriteDoesNotCorruptCrossRef(t *testing.T) {
	// ParentObj: has a physical Name column.
	parentSchema := buildTestSchema("ParentObj", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
	})
	// ObjA: Subject is the active-computed IsNameField (no physical Name column).
	// Echo2__c uses both a bare Name reference (→ Subject) and a cross-ref Parent.Name
	// (must stay as Name, not be corrupted to Parent.Subject).
	objASchema := buildTestSchema("ObjA", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		activeFormulaFieldWithIsNameField("Subject", `"computed subject"`),
		fkField("ParentId", "Parent", "ParentObj"),
		activeFormulaField("Echo2__c", `Name & " - " & Parent.Name`),
	})

	ddl := buildViewForTest(t, "ObjA", map[string]*models.ObjectMetadata{
		"ObjA":      objASchema,
		"ParentObj": parentSchema,
	}, []models.FieldMetadata{
		objASchema.Fields[1], // Subject (active-computed IsNameField)
		objASchema.Fields[3], // Echo2__c
	})

	// The cross-ref Parent.Name must NOT be corrupted to Parent.Subject.
	// (With text-based rewrite, '.' is a non-ident char so word-boundary fires on
	// .Name, replacing Parent.Name → Parent.Subject.)
	if strings.Contains(ddl, "j_parent.`Subject`") {
		t.Errorf("DDL corrupted cross-ref Parent.Name to Parent.Subject (text-rewrite bug):\n%s", ddl)
	}
	// The parent JOIN must reference Name, not Subject.
	if !strings.Contains(ddl, "j_parent.`Name`") {
		t.Errorf("DDL must reference j_parent.`Name` for cross-ref Parent.Name (not j_parent.`Subject`):\n%s", ddl)
	}
	// The bare Name reference must resolve to Subject via L0.
	if !strings.Contains(ddl, "L0.`Subject`") {
		t.Errorf("DDL must reference L0.`Subject` for bare Name in Echo2__c:\n%s", ddl)
	}
}

// TestFormulaExprString_AggregatePreservesForeignKey verifies that formulaExprString
// preserves AggregateExpr.ForeignKey, which nodeString silently drops.
//
// Regression for Codex BLOCK on PR #1194: nodeString serialized SUM(Child.Amount, FK)
// as SUM(Child.Amount), losing the explicit FK on the child-to-parent relationship.
func TestFormulaExprString_AggregatePreservesForeignKey(t *testing.T) {
	node := &AggregateExpr{
		Func:        "SUM",
		ChildObject: "Child__c",
		ChildField:  "Amount__c",
		ForeignKey:  "MyFK__c",
	}
	got := formulaExprString(node)
	want := "SUM(Child__c.Amount__c, MyFK__c)"
	if got != want {
		t.Errorf("formulaExprString = %q, want %q", got, want)
	}
	// Must round-trip: the serialized text must be parseable and yield the same FK.
	parsed, err := Parse(got)
	if err != nil {
		t.Fatalf("re-parsing formulaExprString output failed: %v", err)
	}
	agg, ok := parsed.(*AggregateExpr)
	if !ok {
		t.Fatalf("parsed result is %T, want *AggregateExpr", parsed)
	}
	if agg.ForeignKey != "MyFK__c" {
		t.Errorf("round-trip ForeignKey = %q, want %q", agg.ForeignKey, "MyFK__c")
	}
}

// TestFormulaExprString_StringLitEscapesEmbeddedQuote verifies that formulaExprString
// properly escapes single quotes embedded in string literals.
//
// Regression for Codex BLOCK on PR #1194: nodeString emitted 'O'Reilly' (invalid formula
// text) for a StringLit whose value contained a single quote.
func TestFormulaExprString_StringLitEscapesEmbeddedQuote(t *testing.T) {
	node := &StringLit{Value: "O'Reilly"}
	got := formulaExprString(node)
	want := `'O\'Reilly'`
	if got != want {
		t.Errorf("formulaExprString = %q, want %q", got, want)
	}
	// Must round-trip: the serialized text must be parseable and recover the original value.
	parsed, err := Parse(got)
	if err != nil {
		t.Fatalf("re-parsing formulaExprString output failed: %v", err)
	}
	lit, ok := parsed.(*StringLit)
	if !ok {
		t.Fatalf("parsed result is %T, want *StringLit", parsed)
	}
	if lit.Value != "O'Reilly" {
		t.Errorf("round-trip value = %q, want %q", lit.Value, "O'Reilly")
	}
}

// ---------------------------------------------------------------------------
// Issue #1392 — Missing physical column filter for metadata-declared fields
// ---------------------------------------------------------------------------

// TestBuildViewFromDSL_LookupValueFormulaSourceFieldNotHoisted is a BUG-2
// regression guard. When a lookupValue's match SourceField is itself an
// active-computed (formula) field on the source object, the lookupValue
// definition resides in a layer above L0 in the multi-layer DDL path.
// Hoisting it would place the lv_ JOIN only in the L0 inner FROM while the
// lv_x.field column ref would appear in the outer SELECT (whose FROM is the
// L0 subquery) → TiDB "Unknown table 'lv_x'" at CREATE VIEW time.
//
// The guard in buildViewFromDSL must detect this via
// formulastorage.SchemaFieldIsActiveComputed and skip the hoist, leaving the
// portable correlated-subquery form in the DDL.
//
// This test uses the ProjectionConfig path (not LOOKUPVALUE formula string)
// because the formula-string LOOKUPVALUE is sealed by the allowlist; the
// projection path is the production code path that exercises the hoist loop.
func TestBuildViewFromDSL_LookupValueFormulaSourceFieldNotHoisted(t *testing.T) {
	// The source object has:
	//   - Id (base)
	//   - ComputedKey__c (active formula field — this is the match SourceField)
	//   - TargetName__c (lookupValue projection whose SourceField = ComputedKey__c)
	formulaMode := "active"
	rt := models.FieldType(constants.FieldTypeString)

	// ComputedKey__c is itself an active formula (SchemaFieldIsActiveComputed → true).
	computedKeyField := models.FieldMetadata{
		APIName:     "ComputedKey__c",
		Type:        constants.FieldTypeFormula,
		Formula:     svcTestStrPtr("Id"),
		FormulaMode: &formulaMode,
		ReturnType:  &rt,
	}
	// TargetName__c is a projection-kind lookupValue whose MatchPairs[0].SourceField
	// is ComputedKey__c — an active-computed field on the source object.
	targetNameField := models.FieldMetadata{
		APIName: "TargetName__c",
		Type:    constants.FieldTypeString, // projection output type
		ProjectionConfig: &models.FieldProjectionConfig{
			Kind:         "lookupValue",
			TargetObject: "RefObject",
			SelectField:  "Name",
			Matches: []models.FieldProjectionMatch{
				{TargetField: "Id", SourceField: "ComputedKey__c"},
			},
		},
	}
	sourceSchema := buildTestSchema("SourceObj", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		computedKeyField,
		targetNameField,
	})
	refSchema := buildTestSchema("RefObject", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
	})

	schemas := map[string]*models.ObjectMetadata{
		"SourceObj": sourceSchema,
		"RefObject": refSchema,
	}
	// Pass the computed formula field and the lookupValue projection field together
	// so buildViewFromDSL sees both as formula-domain fields to process.
	formulaFields := []models.FieldMetadata{computedKeyField, targetNameField}

	ddl := buildViewForTest(t, "SourceObj", schemas, formulaFields)

	// The DDL must NOT contain a LEFT JOIN with an lv_ prefix for RefObject:
	// the hoist must have been skipped because ComputedKey__c is a formula field.
	if strings.Contains(ddl, "LEFT JOIN `RefObject` lv_") ||
		strings.Contains(ddl, "LEFT JOIN `refobject` lv_") {
		t.Errorf("BUG-2 regression: lookupValue whose SourceField is active-computed must NOT be hoisted to a LEFT JOIN; got DDL:\n%s", ddl)
	}
	// The DDL must still contain the correlated scalar subquery for the lookupValue.
	if !strings.Contains(ddl, "(SELECT lv.`Name` FROM `refobject` lv") &&
		!strings.Contains(ddl, "(SELECT lv.`Name` FROM `RefObject` lv") {
		t.Errorf("BUG-2 regression: lookupValue whose SourceField is active-computed must keep correlated subquery form; got DDL:\n%s", ddl)
	}
}

// TestBuildViewFromDSL_LookupValuePhysicalAliasedSourceColumn is the round-1 CI
// blocker regression guard. When a hoisted lookupValue's match SourceField has a
// PhysicalName different from its API name, the generated JOIN ON clause must
// reference the PHYSICAL column on the base table (as the old correlated-subquery
// path did through the compiler's BaseFieldPhysicalMap), not the logical API
// name. Otherwise RebuildView references a non-existent column and fails with an
// unknown-column error for metadata-aliased source fields.
func TestBuildViewFromDSL_LookupValuePhysicalAliasedSourceColumn(t *testing.T) {
	// RefKey__c is a base reference column physically stored as ref_key_phys.
	refKeyField := models.FieldMetadata{
		APIName:      "RefKey__c",
		Type:         constants.FieldTypeString,
		PhysicalName: "ref_key_phys",
	}
	// TargetName__c is a single-PK-match lookupValue (eligible for hoisting) whose
	// match SourceField is the physically-aliased base field RefKey__c.
	targetNameField := models.FieldMetadata{
		APIName: "TargetName__c",
		Type:    constants.FieldTypeString,
		ProjectionConfig: &models.FieldProjectionConfig{
			Kind:         "lookupValue",
			TargetObject: "RefObject",
			SelectField:  "Name",
			Matches: []models.FieldProjectionMatch{
				{TargetField: "Id", SourceField: "RefKey__c"},
			},
		},
	}
	sourceSchema := buildTestSchema("SourceObj", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		refKeyField,
		targetNameField,
	})
	refSchema := buildTestSchema("RefObject", []models.FieldMetadata{
		{APIName: "Id", Type: constants.FieldTypeString},
		{APIName: "Name", Type: constants.FieldTypeString},
	})
	schemas := map[string]*models.ObjectMetadata{
		"SourceObj": sourceSchema,
		"RefObject": refSchema,
	}
	formulaFields := []models.FieldMetadata{targetNameField}

	ddl := buildViewForTest(t, "SourceObj", schemas, formulaFields)

	// RefKey__c is a base (non-computed) field, so the lookupValue is hoisted.
	if !strings.Contains(ddl, "LEFT JOIN `RefObject` lv_") {
		t.Fatalf("expected hoisted lv_ JOIN for RefObject; got DDL:\n%s", ddl)
	}
	// The JOIN ON source must be the PHYSICAL column, matching the old subquery.
	if !strings.Contains(ddl, "t.`ref_key_phys` = ") {
		t.Errorf("hoisted lookupValue JOIN ON must reference the physical source column t.`ref_key_phys`; got DDL:\n%s", ddl)
	}
	// It must NOT reference the logical API name as a base-table column.
	if strings.Contains(ddl, "t.`RefKey__c` = ") {
		t.Errorf("hoisted lookupValue JOIN ON must NOT reference the logical API source column t.`RefKey__c`; got DDL:\n%s", ddl)
	}
}