package packs

import (
	"context"
	"database/sql"
	"encoding/json"
	"os"
	"reflect"
	"regexp"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/nexuscrm/analytics"
	domainschema "github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/metadata"
	"github.com/nexuscrm/persistence"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/fieldprovenance"
	"github.com/nexuscrm/shared/pkg/formulastorage"
	"github.com/nexuscrm/shared/pkg/models"
	schemacatalog "github.com/pingcap-inc/nexuscrm-contract/pkg/schemacatalog"
	"github.com/pingcap-inc/nexuscrm-controlplane/bootstrapimpl"
	"github.com/pingcap-inc/nexuscrm-controlplane/packspec"
)

func installPackFieldProvenance(t *testing.T) {
	t.Helper()
	previous := fieldprovenance.Default()
	t.Cleanup(func() { fieldprovenance.SetDefault(previous) })
	fieldprovenance.SetDefault(fieldprovenance.NewClassifier(nil, nil, nil, nil))
}

func TestRuntimeRequiredPacksAreCleanL0(t *testing.T) {
	var names []string
	for _, pack := range RuntimeRequiredPacks() {
		names = append(names, pack.Name())
		if pack.Category() == AppPack {
			t.Fatalf("RuntimeRequiredPacks() includes app pack %q", pack.Name())
		}
	}
	want := []string{"platform-core", "platform-policy", "platform-config", "platform-ux"}
	if !reflect.DeepEqual(names, want) {
		t.Fatalf("RuntimeRequiredPacks() names = %v, want %v", names, want)
	}
}

func TestPlatformPermissionCatalogIsNonEmpty(t *testing.T) {
	platformRows, err := schemacatalog.StandardPermissions()
	if err != nil {
		t.Fatalf("LoadStandardPermissions() error = %v", err)
	}
	if len(platformRows) == 0 {
		t.Fatal("platform permission catalog is empty")
	}
	seen := map[string]struct{}{}
	for _, row := range platformRows {
		if row.APIName == "" {
			t.Fatal("platform permission catalog has empty apiName")
		}
		if _, ok := seen[row.APIName]; ok {
			t.Fatalf("platform permission catalog contains duplicate apiName %q", row.APIName)
		}
		seen[row.APIName] = struct{}{}
	}
}

func TestStandardObjectSpecSplitKeepsCRMOutOfPlatformCore(t *testing.T) {
	specs, err := parseStandardObjectsSpec()
	if err != nil {
		t.Fatalf("parseStandardObjectsSpec() error = %v", err)
	}

	platformSpecs := filterObjectSpecsByCategory(specs, "system")
	if len(platformSpecs) == 0 {
		t.Fatal("platform spec split returned no system objects")
	}
	for _, spec := range platformSpecs {
		if spec.Category == "crm" {
			t.Fatalf("platform specs include CRM object %s", spec.APIName)
		}
	}
	if got := findObjectSpec(t, platformSpecs, "RecordType"); got.Category != "system" {
		t.Fatalf("RecordType category = %q, want system", got.Category)
	}
	recordTypeSpec := findObjectSpec(t, platformSpecs, "RecordType")
	if recordTypeSpec.ReadSource == nil {
		t.Fatal("RecordType readSource = nil, want canonical _System_RecordType source")
	}
	if recordTypeSpec.ReadSource.Kind != "physical_table" || recordTypeSpec.ReadSource.PhysicalTable != constants.TableRecordType {
		t.Fatalf("RecordType readSource = %+v, want physical_table %s", recordTypeSpec.ReadSource, constants.TableRecordType)
	}
	if err := validateSpecReadSourcesForApply("platform-core", []ObjectSpec{recordTypeSpec}); err != nil {
		t.Fatalf("RecordType platform-core readSource validation = %v, want nil", err)
	}

	crmSpecs := filterObjectSpecsByCategory(specs, "crm")
	if len(crmSpecs) != 0 {
		t.Fatalf("platform spec split returned %d CRM object(s), want 0", len(crmSpecs))
	}
	for _, def := range systemTableDefinitions() {
		if def.TableName == "CampaignMemberStatus" {
			t.Fatal("system_tables.json still declares CRM-owned CampaignMemberStatus")
		}
	}
}

func TestPreservePackOwnedAccessPolicySidecarsRestoresRecordMutationFromSidecarValue(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	selectLive := regexp.QuoteMeta("SELECT `AccessPolicy`, `AccessPolicyRecordMutationPackName`, `AccessPolicyRecordMutationPackValue`, `AccessPolicyLifecyclePackName`, `AccessPolicyLifecyclePackValue` FROM `_System_Object` WHERE LOWER(`ApiName`) = ? AND COALESCE(`IsDeleted`, 0) = 0 LIMIT 1")
	mock.ExpectQuery(selectLive).
		WithArgs("recordtype").
		WillReturnRows(sqlmock.NewRows([]string{
			"AccessPolicy",
			"AccessPolicyRecordMutationPackName",
			"AccessPolicyRecordMutationPackValue",
			"AccessPolicyLifecyclePackName",
			"AccessPolicyLifecyclePackValue",
		}).AddRow(nil, "salesforce-crm-standard", `{"deny":[{"surfaces":["singleCreate"],"message":"closed"}]}`, nil, nil))

	objects := []*models.ObjectMetadata{{
		APIName: "RecordType",
	}}
	if err := preservePackOwnedAccessPolicySidecars(context.Background(), db, objects); err != nil {
		t.Fatalf("preservePackOwnedAccessPolicySidecars: %v", err)
	}
	if objects[0].AccessPolicy == nil || objects[0].AccessPolicy.RecordMutation == nil {
		t.Fatalf("recordMutation was not restored from sidecar value: %+v", objects[0].AccessPolicy)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestPreservePackOwnedAccessPolicySidecarsRejectsMissingOwnedRecordMutationProjection(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	selectLive := regexp.QuoteMeta("SELECT `AccessPolicy`, `AccessPolicyRecordMutationPackName`, `AccessPolicyRecordMutationPackValue`, `AccessPolicyLifecyclePackName`, `AccessPolicyLifecyclePackValue` FROM `_System_Object` WHERE LOWER(`ApiName`) = ? AND COALESCE(`IsDeleted`, 0) = 0 LIMIT 1")
	mock.ExpectQuery(selectLive).
		WithArgs("recordtype").
		WillReturnRows(sqlmock.NewRows([]string{
			"AccessPolicy",
			"AccessPolicyRecordMutationPackName",
			"AccessPolicyRecordMutationPackValue",
			"AccessPolicyLifecyclePackName",
			"AccessPolicyLifecyclePackValue",
		}).AddRow(nil, "salesforce-crm-standard", "null", nil, nil))

	objects := []*models.ObjectMetadata{{
		APIName: "RecordType",
	}}
	err = preservePackOwnedAccessPolicySidecars(context.Background(), db, objects)
	if err == nil {
		t.Fatal("preservePackOwnedAccessPolicySidecars returned nil, want missing owned recordMutation error")
	}
	if !strings.Contains(err.Error(), "AccessPolicy.recordMutation is owned by pack salesforce-crm-standard but live projection and sidecar value are both empty") {
		t.Fatalf("error = %v, want missing owned recordMutation projection", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestPlatformCoreDoesNotOwnCRMRecordMutationPolicies(t *testing.T) {
	specs, err := parseStandardObjectsSpec()
	if err != nil {
		t.Fatalf("parseStandardObjectsSpec() error = %v", err)
	}
	recordType := findObjectSpec(t, specs, "RecordType")
	if recordType.AccessPolicy != nil && recordType.AccessPolicy.RecordMutation != nil {
		t.Fatalf("platform-core RecordType owns recordMutation policy: %+v", recordType.AccessPolicy.RecordMutation)
	}

	blockedAppFacts := map[string]struct{}{
		"Flow":              {},
		"FlowDefinition":    {},
		"ProcessDefinition": {},
		"ProcessAction":     {},
	}
	for _, def := range managedTableDefinitions() {
		if _, ok := blockedAppFacts[def.TableName]; !ok {
			continue
		}
		if def.AccessPolicy != nil && def.AccessPolicy.RecordMutation != nil {
			t.Fatalf("platform bootstrap %s owns recordMutation policy: %+v", def.TableName, def.AccessPolicy.RecordMutation)
		}
	}
}

func TestBuildObjectMetadataMarksCustomObjects(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Account",
			Label:        "Account",
			TableType:    "standard_object",
			Category:     "crm",
			SharingModel: "Private",
			Fields:       []FieldSpec{},
		},
		{
			APIName:      "Org__c",
			Label:        "Org",
			TableType:    "custom_object",
			Category:     "crm",
			SharingModel: "ReadWrite",
			Fields:       []FieldSpec{},
		},
	}

	objects := buildObjectMetadata(specs)
	if len(objects) != 2 {
		t.Fatalf("buildObjectMetadata() returned %d objects, want 2", len(objects))
	}

	if objects[0].APIName != "Account" || objects[0].IsCustom {
		t.Fatalf("standard object metadata = %+v, want IsCustom=false", objects[0])
	}
	if objects[1].APIName != "Org__c" || !objects[1].IsCustom {
		t.Fatalf("custom object metadata = %+v, want IsCustom=true", objects[1])
	}
}

func TestBuildObjectMetadataPopulatesDisplayNameField(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:          "TestObject__c",
			Label:            "Test Object",
			TableType:        "custom_object",
			Category:         "test",
			SharingModel:     "Private",
			DisplayNameField: "ParentId",
			Fields:           []FieldSpec{},
		},
		{
			APIName:          "NoDisplay__c",
			Label:            "No Display",
			TableType:        "custom_object",
			Category:         "test",
			SharingModel:     "Private",
			DisplayNameField: "",
			Fields:           []FieldSpec{},
		},
	}

	objects := buildObjectMetadata(specs)
	if len(objects) != 2 {
		t.Fatalf("buildObjectMetadata() returned %d objects, want 2", len(objects))
	}

	if objects[0].DisplayNameField == nil || *objects[0].DisplayNameField != "ParentId" {
		t.Fatalf("TestObject__c DisplayNameField = %v, want %q", objects[0].DisplayNameField, "ParentId")
	}
	if objects[1].DisplayNameField != nil {
		t.Fatalf("NoDisplay__c DisplayNameField = %v, want nil", objects[1].DisplayNameField)
	}
}

func TestBuildObjectMetadataPropagatesCustomSetting(t *testing.T) {
	specs := []ObjectSpec{{
		APIName:       "Countries_Region__c",
		Label:         "Countries Region",
		TableType:     "custom_object",
		Category:      "crm",
		CustomSetting: true,
		SharingModel:  "Private",
		Fields:        []FieldSpec{},
	}}

	objects := buildObjectMetadata(specs)
	if len(objects) != 1 {
		t.Fatalf("buildObjectMetadata() returned %d objects, want 1", len(objects))
	}
	if !objects[0].CustomSetting {
		t.Fatal("CustomSetting = false, want true")
	}
}

func TestBuildObjectMetadataPropagatesListFieldsPresence(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Issue__c",
			Label:        "Issue",
			TableType:    "custom_object",
			Category:     "test",
			SharingModel: "Private",
			ListFields:   []string{"Subject__c", "Status__c"},
			Fields:       []FieldSpec{},
		},
		{
			APIName:      "EmptyList__c",
			Label:        "Empty List",
			TableType:    "custom_object",
			Category:     "test",
			SharingModel: "Private",
			ListFields:   []string{},
			Fields:       []FieldSpec{},
		},
		{
			APIName:      "OmittedList__c",
			Label:        "Omitted List",
			TableType:    "custom_object",
			Category:     "test",
			SharingModel: "Private",
			Fields:       []FieldSpec{},
		},
	}

	objects := buildObjectMetadata(specs)
	issue := findObjectMetadataForTest(t, objects, "Issue__c")
	if !reflect.DeepEqual(issue.ListFields, []string{"Subject__c", "Status__c"}) {
		t.Fatalf("Issue__c ListFields = %v, want Subject__c/Status__c", issue.ListFields)
	}

	empty := findObjectMetadataForTest(t, objects, "EmptyList__c")
	if empty.ListFields == nil || len(empty.ListFields) != 0 {
		t.Fatalf("EmptyList__c ListFields = %#v, want explicit empty slice", empty.ListFields)
	}

	omitted := findObjectMetadataForTest(t, objects, "OmittedList__c")
	if omitted.ListFields != nil {
		t.Fatalf("OmittedList__c ListFields = %#v, want nil", omitted.ListFields)
	}
}

func TestObjectSpecMarshalPreservesListFieldsPresence(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "EmptyList__c",
			Label:        "Empty List",
			TableType:    "custom_object",
			Category:     "test",
			SharingModel: "Private",
			ListFields:   []string{},
			Fields:       []FieldSpec{},
		},
		{
			APIName:      "OmittedList__c",
			Label:        "Omitted List",
			TableType:    "custom_object",
			Category:     "test",
			SharingModel: "Private",
			Fields:       []FieldSpec{},
		},
	}

	raw, err := json.Marshal(specs)
	if err != nil {
		t.Fatalf("Marshal(ObjectSpec): %v", err)
	}
	var docs []map[string]json.RawMessage
	if err := json.Unmarshal(raw, &docs); err != nil {
		t.Fatalf("Unmarshal marshaled ObjectSpec: %v", err)
	}
	if got, ok := docs[0]["listFields"]; !ok || string(got) != `[]` {
		t.Fatalf("EmptyList__c listFields = %s present=%v, want explicit []", got, ok)
	}
	if _, ok := docs[1]["listFields"]; ok {
		t.Fatalf("OmittedList__c marshaled listFields unexpectedly: %s", docs[1]["listFields"])
	}
}

func TestBuildObjectMetadataPropagatesExclusiveSiblingFields(t *testing.T) {
	specs := exclusiveSiblingFieldObjectSpecsForTest()

	objects := buildObjectMetadata(specs)
	status := findObjectMetadataForTest(t, objects, "Status__c")
	if len(status.ExclusiveSiblingFields) != 1 {
		t.Fatalf("ExclusiveSiblingFields len = %d, want 1", len(status.ExclusiveSiblingFields))
	}
	got := status.ExclusiveSiblingFields[0]
	if got.FieldAPIName != "IsDefault__c" || got.PartitionFieldAPIName != "Parent__c" {
		t.Fatalf("ExclusiveSiblingFields[0] = %+v, want IsDefault__c partitioned by Parent__c", got)
	}
}

func TestValidateExclusiveSiblingFieldsForSpecsAcceptsBooleanFieldPartitionedByReference(t *testing.T) {
	if err := ValidateExclusiveSiblingFieldsForSpecs(t.Context(), exclusiveSiblingFieldObjectSpecsForTest(), nil, false); err != nil {
		t.Fatalf("ValidateExclusiveSiblingFieldsForSpecs() = %v, want nil", err)
	}
}

func TestValidateExclusiveSiblingFieldsForSpecsRejectsSplitObjectSpecAuthoring(t *testing.T) {
	specs := exclusiveSiblingFieldObjectSpecsForTest()
	declarationSpec := specs[1]
	fieldOnlySpec := declarationSpec
	fieldOnlySpec.ExclusiveSiblingFields = nil
	fieldOnlySpec.Fields = []FieldSpec{fieldOnlySpec.Fields[1], fieldOnlySpec.Fields[2]}
	snapshotSpecs := []ObjectSpec{specs[0], declarationSpec, fieldOnlySpec}

	err := ValidateExclusiveSiblingFieldsForSpecsWithSnapshot(t.Context(), []ObjectSpec{declarationSpec}, snapshotSpecs, nil, true)
	if err == nil {
		t.Fatal("ValidateExclusiveSiblingFieldsForSpecsWithSnapshot() = nil, want split object_spec authoring error")
	}
	for _, want := range []string{"Status__c", "single object_spec authoring surface"} {
		if !strings.Contains(err.Error(), want) {
			t.Fatalf("ValidateExclusiveSiblingFieldsForSpecsWithSnapshot() error = %v, want %q", err, want)
		}
	}
}

func TestValidateListFieldsForSpecsRejectsSplitObjectSpecAuthoring(t *testing.T) {
	declarationSpec := ObjectSpec{
		APIName:      "Issue",
		Label:        "Issue",
		TableType:    "custom_object",
		Category:     "test",
		SharingModel: "Private",
		ListFields:   []string{"Subject"},
		Fields: []FieldSpec{
			{APIName: "Subject", Label: "Subject", Type: "string", Required: true, IsNameField: true},
		},
	}
	fieldOnlySpec := declarationSpec
	fieldOnlySpec.ListFields = nil
	fieldOnlySpec.Fields = []FieldSpec{
		{APIName: "Status", Label: "Status", Type: "picklist"},
	}

	err := ValidateListFieldsForSpecsWithSnapshot([]ObjectSpec{declarationSpec}, []ObjectSpec{declarationSpec, fieldOnlySpec})
	if err == nil {
		t.Fatal("ValidateListFieldsForSpecsWithSnapshot() = nil, want split object_spec authoring error")
	}
	for _, want := range []string{"Issue", "single object_spec authoring surface"} {
		if !strings.Contains(err.Error(), want) {
			t.Fatalf("ValidateListFieldsForSpecsWithSnapshot() error = %v, want %q", err, want)
		}
	}
}

func TestValidateExclusiveSiblingFieldsForSpecsRejectsInvalidContracts(t *testing.T) {
	tests := []struct {
		name    string
		mutate  func([]ObjectSpec) []ObjectSpec
		wantErr string
	}{
		{
			name: "exclusive field must be boolean",
			mutate: func(specs []ObjectSpec) []ObjectSpec {
				specs[1].Fields[2].Type = "string"
				return specs
			},
			wantErr: "must be boolean",
		},
		{
			name: "exclusive field must be writable",
			mutate: func(specs []ObjectSpec) []ObjectSpec {
				specs[1].Fields[2].Createable = boolPtr(false)
				specs[1].Fields[2].Updateable = boolPtr(false)
				return specs
			},
			wantErr: "must be writable on create or update",
		},
		{
			name: "partition must be reference",
			mutate: func(specs []ObjectSpec) []ObjectSpec {
				specs[1].Fields[1].Type = "string"
				specs[1].Fields[1].ReferenceTo = nil
				return specs
			},
			wantErr: "must reference exactly one parent object",
		},
		{
			name: "duplicate declaration",
			mutate: func(specs []ObjectSpec) []ObjectSpec {
				specs[1].ExclusiveSiblingFields = append(specs[1].ExclusiveSiblingFields, specs[1].ExclusiveSiblingFields[0])
				return specs
			},
			wantErr: "duplicates",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			err := ValidateExclusiveSiblingFieldsForSpecs(t.Context(), tt.mutate(exclusiveSiblingFieldObjectSpecsForTest()), nil, false)
			if err == nil {
				t.Fatal("ValidateExclusiveSiblingFieldsForSpecs() = nil, want error")
			}
			if !strings.Contains(err.Error(), tt.wantErr) {
				t.Fatalf("ValidateExclusiveSiblingFieldsForSpecs() error = %v, want %q", err, tt.wantErr)
			}
		})
	}
}

// TestBuildObjectMetadataAppliesCanonicalPluralizeForEmptyPluralLabel
// pins the contract that buildObjectMetadata, when an ObjectSpec carries
// `Label != ""` and `PluralLabel == ""`, fills the result with
// `bootstrap.Pluralize(spec.Label)` instead of the naive `label + "s"`.
//
// This is the structural fix for NX#492 (option 1 from IANTHEREAL's AC
// note on PR #494): the source-backed pack reapply path used to default
// to naive `+s`, producing "Opportunitys" / "Price Book Entrys" on every
// reapply for the two CRM objects whose Label ends in consonant-y. Now
// that buildObjectMetadata routes through the canonical Pluralize
// algorithm, every Label in the algorithm's domain (consonant-y,
// sibilants, x, z) gets the right plural at install/reapply time —
// pack authors no longer need to hand-curate pluralLabel for those
// shapes, and `_System_Object.PluralLabel` no longer requires
// per-object spec lock-in tests.
//
// Cases pinned:
//
//   - "Opportunity" → "Opportunities" (consonant-y, regression case
//     from the bug report).
//   - "Price Book Entry" → "Price Book Entries" (consonant-y, the
//     second regression case).
//   - "Account" → "Accounts" (default-fall-through guard: the
//     canonical change must not break the common path).
//   - "Address" → "Addresses" (sibilant-ss guard: future-proofs
//     against sibilant Labels future pack authors might omit
//     pluralLabel for).
//   - "Order"/"Orders" (declared) → "Orders" (declared-value-wins
//     guard: function does not over-eagerly recompute when spec
//     already carries an explicit plural).
//
// Drop here = silent regression for whichever case is dropped on every
// install-pack write through buildObjectMetadata.
func TestBuildObjectMetadataAppliesCanonicalPluralizeForEmptyPluralLabel(t *testing.T) {
	cases := []struct {
		name        string
		label       string
		pluralLabel string
		want        string
	}{
		{"consonant_y_opportunity", "Opportunity", "", "Opportunities"},
		{"consonant_y_price_book_entry", "Price Book Entry", "", "Price Book Entries"},
		{"default_fall_through_account", "Account", "", "Accounts"},
		{"sibilant_ss_address", "Address", "", "Addresses"},
		{"declared_value_wins", "Order", "Orders", "Orders"},
	}

	for i, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			apiName := "TestObj_" + tc.name
			specs := []ObjectSpec{
				{
					APIName:      apiName,
					Label:        tc.label,
					PluralLabel:  tc.pluralLabel,
					TableType:    "standard_object",
					Category:     "test",
					SharingModel: "Private",
				},
			}
			objects := buildObjectMetadata(specs)
			if len(objects) != 1 {
				t.Fatalf("case[%d] buildObjectMetadata returned %d objects, want 1", i, len(objects))
			}
			if objects[0].PluralLabel != tc.want {
				t.Fatalf("case[%d] %q (PluralLabel=%q) → %q, want %q",
					i, tc.label, tc.pluralLabel, objects[0].PluralLabel, tc.want)
			}
		})
	}
}

// TestCRMPackV1PayloadHashStableAfterSpecRevert is a frozen-golden
// lock-in for the salesforce-crm-standard source-backed pack's v1
// payload hash. It exists as the runtime oracle for NX#492 PR #494
// option 1's central claim: "this fix is hash-neutral".
//
// Background: PR #494 commit 63538bf added explicit `pluralLabel`
// declarations to two CRM object spec entries. That mutated pack
// component payload, drifted the v1 hash, and tripped
// verifyInstalledPackHashState (`source_pack_ops.go` →
// `verify.go::verifyInstalledPackHashState`) on warm-upgraded orgs
// before any reapply path could converge. Option 1 reverts those
// declarations and instead routes `buildObjectMetadata` through
// canonical bootstrap.Pluralize — fixing the bug class without
// changing pack content.
//
// This test loads the CRM source pack via the same loader path the
// installed-pack reconciler uses and asserts the v1 payload hash
// equals a frozen golden hex string captured during this PR. Any
// future PR that mutates the external `salesforce-crm-standard` official-pack
// content
// (object specs, fields, validation rules, flows, manifest
// dependencies, etc.) will trip this assertion.
//
// If you are a future author seeing this test fail: STOP. Read
// REVIEWER_GUIDE cross-stage rule "改 manifest-declared pack
// component 内容 = 触发 pack hash drift, warm-upgrade 会在 reapply
// 之前 fail closed". Warm-upgraded orgs that installed the pack
// before your change carry the previous hash in
// `_System_InstalledPack.AppliedPayloadHash`.
//
// As of PR #559, the substrate primitive for "source-identity match +
// content-hash drift → reapply-before-strict-verify" exists: see
// reconcileActiveInstalledPackSourceState handling of *HashDriftError
// in source_pack_ops.go. Bumping the golden alongside an intentional
// pack content change is path (a): warm-upgraded orgs will reapply on
// next ApplyAll via the HashDriftError branch.
//
// Path (b) — routing through a hash-neutral fix — is still preferable
// when the fix permits it, because reapply has a small but non-zero
// blast radius. Bumping the golden without either (a) or (b) is the
// "land now, fix the shape later" antipattern the rule guards against.
func TestCRMPackV1PayloadHashStableAfterSpecRevert(t *testing.T) {
	registerCRMHostPrimitivesForTest(t)
	opts := officialCRMPackSourceOptionsForTest(t)
	// This oracle freezes the exact M2 source tuple named beside goldenV1Hash.
	// Product composition no longer mutates the process-global source registry,
	// so carry the historical source identity explicitly in the test operation.
	opts.OfficialRepoSourceRevisions["salesforce-crm-standard"] = "2425c00c77e968b0769d6bd247b59831a17de894"

	source, diagnostics := ResolvePackSourceWithOptions("official_repo:salesforce-crm-standard", opts)
	if len(diagnostics) > 0 {
		t.Fatalf("resolve CRM source diagnostics: %v", diagnostics)
	}
	pack, err := NewSourceBackedPack(source, officialCRMSourceBackedPackOptionsForTest(t, opts)...)
	if err != nil {
		t.Fatalf("construct CRM source-backed pack: %v", err)
	}
	hash, err := PackPayloadHash(pack)
	if err != nil {
		t.Fatalf("compute CRM v1 payload hash: %v", err)
	}

	// Frozen golden hash captured during NX#492 PR #494 round-3
	// revert (commit 63538bf's pluralLabel additions removed from
	// standard_objects_spec.json; canonical Pluralize routed via
	// buildObjectMetadata). Update only with explicit justification
	// per REVIEWER_GUIDE pack-hash-drift rule (see function doc).
	//
	// Updated for NX#551: replaced vrule_lead_post_convert_read_only with
	// per-anchor ISCHANGED rules + a permission-gated umbrella rule using SF
	// v63's canonical AllowViewEditConvertedLeads user permission. Warm-upgrade
	// path covered by the hash-drift reapply substrate added in this same PR.
	//
	// Re-aligned post-#559 merge: the golden captured during #559 (0x74a8b553…)
	// did not match the live payload at #559's merge tip, leaving the test red
	// on origin/main. Recaptured from PackPayloadHash against the current
	// external official `salesforce-crm-standard` pack tree; warm-upgrade remains covered by the
	// #559 hash-drift reapply substrate (no pack content change in this commit).
	//
	// Re-aligned post-#610 merge (2026-05-12): PR #610 "[Bug] Add Opportunity
	// Add Products flow" added an `AddProducts` action entry to
	// salesforce-crm-standard/actions/ui_actions.json without bumping
	// this golden. That left the test red on origin/main from #610's merge
	// onward (verified against b488120: 3 consecutive `go test` runs all give
	// 0xbe9dc3e7…). Recapturing from PackPayloadHash now; warm-upgraded orgs
	// reapply via the #559 hash-drift substrate (no fix-only pack content
	// change in this commit — the ui_actions.json mutation already landed
	// in #610).
	//
	// Re-aligned for issue #651 PR-1 (2026-05-12): this slice intentionally
	// adds the authoritative `PricebookEntry.Name` L1 metadata declaration as an
	// active lookupValue projection from Product2.Name. This changes bundled CRM
	// pack content by design. Warm-upgraded orgs are covered by the current-tree
	// hash-drift reapply substrate in verify.go/source_pack_ops.go, so bumping
	// the golden is the repository-approved path (a), not an unguarded drift.
	//
	// Re-aligned for issues #828/#870/#888 (2026-05-15/16): see prior comments.
	//
	// Re-aligned for issue #870 (2026-05-15): the official CRM pack now models
	// the standard Salesforce `Opportunity.LastStageChangeDate` field so
	// downstream active Opportunity formulas compile against an authoritative L1
	// declaration instead of failing on a missing metadata/physical column. This
	// is an intentional manifest-declared pack payload change. Recaptured from
	// the merged official app-packs mainline at d139c80, not from the pre-merge
	// feature-branch snapshot. Warm-upgraded orgs remain covered by the
	// source-hash-drift reapply substrate, so bumping the golden is the approved
	// path (a).
	//
	// Re-aligned for issue #888 (2026-05-16): external app-packs commit
	// 8932df7 intentionally adds the Opportunity stage-derived forecast flow,
	// declares Opportunity.ForecastCategory/ForecastCategoryName, adds the
	// forecast field to the Opportunity layout, and declares the flow in the
	// salesforce-crm-standard manifest. The preceding external commit e197c53
	// remains hash-neutral; the drift starts at 8932df7. This is an intentional
	// manifest-declared pack payload change, and warm-upgraded orgs remain
	// covered by the source-hash-drift reapply substrate.
	//
	// Re-aligned for issue #823 / nexuscrm-app-packs#10 (2026-05-16):
	// Task.IsClosed is now declared in the official CRM pack as an active
	// formula OR(ISPICKVAL(Status,"Completed"),ISPICKVAL(Status,"Deferred")).
	// Warm-upgraded orgs covered by the existing hash-drift reapply substrate.
	//
	// Re-aligned for issue #847 (2026-05-16): adds a `child_clone_rules`
	// component to the salesforce-crm-standard manifest that seeds
	// OpportunityLineItem to QuoteLineItem cloning on Quote create. This is an
	// intentional manifest-declared pack payload change. Warm-upgraded orgs
	// reapply via the hash-drift substrate.
	// Re-aligned for NexusCRM#1100 + nexuscrm-app-packs#29 (2026-05-19): platform
	// version bumped 0.1.0 → 0.2.0 and salesforce-crm-standard version bumped
	// 0.1.1 → 0.2.0 (removing Lead lockRule, updating platformCompatibility).
	// Intentional manifest-declared pack content change. Warm-upgraded orgs remain
	// covered by the source-hash-drift reapply substrate.
	//
	// Re-aligned for nexuscrm-app-packs#30 + subsequent flow/layout additions
	// (2026-05-19): version bump and post-#29 flow/layout entries compounded the
	// drift. Recaptured from PackPayloadHash against the current official pack
	// mainline. Warm-upgraded orgs covered by the source-hash-drift reapply substrate.
	//
	// Re-aligned for nexuscrm-app-packs commit f0b6f39 (2026-05-20): adds
	// Opportunity.Net_Fees__c as a physical currency field. Intentional pack
	// content change; warm-upgraded orgs reapply via the hash-drift substrate.
	//
	// Re-aligned for issue #1110 / PR #1133 (2026-05-21): TableDefinition now
	// carries displayNameField so verify-pack can compare live
	// _System_Object.DisplayNameField drift against the manifest-authored
	// value. This intentionally makes displayNameField part of the pack payload
	// hash; warm-upgraded orgs reapply via the existing hash-drift substrate.
	//
	// Re-aligned for L1 PR #36 canonical formula types (2026-05-22): the
	// official CRM pack now uses the canonical `type:"formula"` +
	// `returnType:<materialized type>` shape for active formula fields instead
	// of the legacy active-scalar declaration. Intentional manifest-declared pack
	// content change; warm-upgraded orgs reapply via the hash-drift substrate.
	//
	// Re-aligned for nexuscrm-pingcap-pack#1674 (2026-05-23): adds
	// search_scope_order component to salesforce-crm-standard manifest for
	// pack-driven global search scope ordering. Intentional manifest-declared
	// pack content change; warm-upgraded orgs reapply via the hash-drift substrate.
	//
	// Re-aligned for issue #1974 (2026-06-06): _System_RecycleBin now carries
	// delete-operation lineage columns so restore/purge can replay provenance
	// rather than exclusive-sibling lifecycle hooks. Intentional system metadata
	// payload change; warm-upgraded orgs reapply via the existing hash-drift
	// substrate.
	//
	// Re-aligned for NexusCRM#2137 plus nexuscrm-app-packs companion payload:
	// CRM record-mutation denials for RecordType/Flow/FlowDefinition/
	// ProcessDefinition/ProcessAction now ship as locked object_record_mutation_policy
	// components in the external salesforce-crm-standard pack instead of L0
	// platform bootstrap metadata. Intentional manifest-declared pack content
	// change; warm-upgraded orgs reapply via the existing source-hash-drift
	// substrate.
	//
	// Re-aligned for #2015 write-spine prerequisite (2026-06-10): ProcessDefinition
	// approval child materialization is now authored as a locked
	// object_lifecycle_policy component in the external salesforce-crm-standard
	// pack. The generic records lifecycle seam dispatches the CRM-owned
	// crm.approval-process-child-sync capability; the legacy REST/deploy child
	// sync bridge is removed. Intentional manifest-declared pack content change;
	// warm-upgraded orgs reapply via the existing source-hash-drift substrate.
	//
	// Re-aligned for #2229/#2258 successor plus nexuscrm-app-packs#118
	// (2026-06-13): ContentDistribution href rewrite field pairs now ship as
	// locked href_rewrite_fields metadata in the external CRM pack instead of
	// platform/dialect Go code. Re-aligned again for the same successor after
	// the pack also declared the owning Contract.LegacyLink__c field required by
	// field-level href rewrite ownership validation. Intentional
	// manifest-declared pack content change; warm-upgraded orgs reapply via the
	// existing source-hash-drift substrate.
	//
	// Re-aligned for #2362 plus nexuscrm-app-packs#118/#122 (2026-06-13): the
	// CRM distribution lock now points at the app-packs source-mode revision
	// that owns both the href rewrite pack payload and the external CRM dialect
	// source. Bumped for #2257: app-packs commit adds CASESAFEID formula_expr_v1
	// bundle to CRM formula functions. Bumped for #2257: app-packs commit adds
	// IMAGE formula_expr_v1 bundle to CRM formula functions.
	//
	// Re-aligned for #2383 frontend Phase 2B (2026-06-17): the CRM distribution
	// frontend app-packs source revision now points at the external source tree
	// that owns the bulk CRM frontend pages/features being consumed from Nexus.
	// Re-aligned for nexuscrm-app-packs#ADDMONTHS (2026-06-17): ADDMONTHS
	// bundle entry gained args spec (dateLikeLiteral + number). Intentional
	// pack content change; warm-upgraded orgs reapply via the existing
	// source-hash-drift substrate. Pinned to 2f6c45d (2026-06-17): advancing
	// CRM pack source revision catches up with upstream app-packs commits
	// (frontend policies, etc.) while preserving the same CRM formula bundle
	// payload (ISPICKVAL, HYPERLINK, CASESAFEID, IMAGE, ADDMONTHS).
	// Re-aligned for #2257: INCLUDES now ships as CRM formula_expr_v1 bundle
	// metadata after the L0 default catalog stopped owning its call shape and
	// SQL lowering handles the self-delegating bundle body without recursion.
	//
	// Re-aligned for #2383 frontend Phase 2B companion app-packs#194
	// (2026-06-17): CRM frontend runtime services and the record-detail
	// ChangePasswordModal moved to app-owned source; the CRM frontend
	// source-manifest component hash changed intentionally. Warm-upgraded orgs
	// reapply via the existing source-hash-drift substrate.
	// Re-aligned for #760 plus pingcap-inc/nexuscrm-app-packs#218: CRM now
	// ships Report/Dashboard folder_type metadata as app-pack-owned payload.
	// Re-aligned for issue 2229 Tier 2 Item 4 plus
	// pingcap-inc/nexuscrm-app-packs#218: CRM now ships SharingRule shape
	// constraints as source-backed ValidationRule metadata.
	// Re-aligned for #2847: reverted #2842's connector ingress receipt
	// substrate after review found vendor/header admission policy hardcoded
	// into L0. Intentional pack content change; warm-upgraded orgs reapply
	// through the existing source-hash-drift substrate.
	// Re-aligned for nexuscrm-app-packs#245 (2026-06-23): CRM app-pack
	// commit ce893e7 adds authored default layouts for Pricebook2, Product2,
	// and Task plus related catalog/activity object spec metadata. Intentional
	// manifest-declared pack content change; warm-upgraded orgs reapply via the
	// existing source-hash-drift substrate.
	//
	// Re-aligned for NCRM#3102 Group A plus nexuscrm-app-packs#256
	// (2026-06-25): CRM now owns the five connector flow_action_call_handler
	// descriptors and Std_ConvertLead depends on transferConnectorRemoteIdentity.
	// The descriptor payload belongs to the CRM app pack because
	// flow_action_call_handler ownership is global and connector packs cannot
	// declare that component kind. Intentional manifest-declared pack content
	// change; warm-upgraded orgs reapply via the existing source-hash-drift
	// substrate.
	//
	// Re-aligned for B2b S2 (nexuscrm-app-packs S2a): the external.records.read
	// OAuth custom-scope seed now declares route_family_prefix so the control
	// plane validates its route_patterns against a pack-supplied prefix
	// (oauthroutes.NewFamily) instead of a kernel-hardcoded "/services/data/".
	// Intentional manifest-declared pack content change; warm-upgraded orgs
	// reapply via the existing source-hash-drift substrate. NOTE: this golden
	// matches the S2a-updated pack — control-plane CI must run against an
	// app-packs checkout that includes S2a.
	//
	// Re-aligned for the M1 stopped-upgrade evidence tuple: NexusCRM core
	// 0c60aae305df and nexuscrm-app-packs b82eaa0ddeb5. The current-tree
	// registration closure removes the stale synthesized changeowner handler,
	// preserves Std_ConvertLead's declared transferConnectorRemoteIdentity
	// reference, and re-canonicalizes the affected derived projections. The
	// complete component delta is frozen by TestGoldenOracle.
	//
	// Re-aligned for the M1 Core release tuple: NexusCRM core
	// 28417e455d9a and nexuscrm-app-packs d4b83bd65680. The app-pack source
	// revision is the exact OfficialAppPacksSourceRevision published by that
	// Core commit; TestGoldenOracle freezes the component-level delta.
	//
	// Re-aligned for the M2 command-authority producer tuple: NexusCRM core
	// fe2d3d992732 and nexuscrm-app-packs 2425c00c77e9. The app-pack revision
	// removes the stale standard_action_protected declaration from the generic
	// connector handoff descriptor so it matches the Runtime registration.
	const goldenV1Hash = "nexus-pack-sha256-v1:2c30373ad4213f543e8fffb898f00accac348d9b200e052ea91352fa36d4bfae"

	if !IsPackPayloadHashV1(hash) {
		t.Fatalf("CRM pack hash %q is not v1 format (prefix %q)",
			hash, PackPayloadHashV1Prefix)
	}
	if hash != goldenV1Hash {
		t.Fatalf("CRM pack v1 payload hash drifted: got %q, golden %q.\n"+
			"Read REVIEWER_GUIDE cross-stage rule \"改 manifest-declared pack component 内容 = 触发 pack hash drift\" before bumping the golden — see function doc for the bumping criteria.",
			hash, goldenV1Hash)
	}
}

func findObjectSpec(t *testing.T, specs []ObjectSpec, apiName string) ObjectSpec {
	t.Helper()
	for _, spec := range specs {
		if spec.APIName == apiName {
			return spec
		}
	}
	t.Fatalf("object spec %s not found", apiName)
	return ObjectSpec{}
}

func exclusiveSiblingFieldObjectSpecsForTest() []ObjectSpec {
	return []ObjectSpec{
		{
			APIName:      "Parent__c",
			Label:        "Parent",
			TableType:    string(constants.TableTypeCustomObject),
			Category:     "test",
			SharingModel: string(constants.SharingModelPublicReadWrite),
			Fields: []FieldSpec{
				{
					APIName:     constants.FieldName,
					Label:       "Parent Name",
					Type:        string(constants.FieldTypeString),
					IsNameField: true,
				},
			},
		},
		{
			APIName:      "Status__c",
			Label:        "Status",
			TableType:    string(constants.TableTypeCustomObject),
			Category:     "test",
			SharingModel: string(constants.SharingModelPublicReadWrite),
			ExclusiveSiblingFields: []models.ExclusiveSiblingField{{
				FieldAPIName:          "IsDefault__c",
				PartitionFieldAPIName: "Parent__c",
			}},
			Fields: []FieldSpec{
				{
					APIName:     constants.FieldName,
					Label:       "Status Name",
					Type:        string(constants.FieldTypeString),
					IsNameField: true,
				},
				{
					APIName:     "Parent__c",
					Label:       "Parent",
					Type:        string(constants.FieldTypeReference),
					ReferenceTo: []string{"Parent__c"},
				},
				{
					APIName: "IsDefault__c",
					Label:   "Default",
					Type:    string(constants.FieldTypeBoolean),
				},
			},
		},
	}
}

func findObjectMetadataForTest(t *testing.T, objects []*models.ObjectMetadata, apiName string) *models.ObjectMetadata {
	t.Helper()
	for _, object := range objects {
		if object != nil && object.APIName == apiName {
			return object
		}
	}
	t.Fatalf("object metadata %s not found", apiName)
	return nil
}

func findFieldSpec(t *testing.T, spec ObjectSpec, apiName string) FieldSpec {
	t.Helper()
	for _, field := range spec.Fields {
		if field.APIName == apiName {
			return field
		}
	}
	t.Fatalf("field spec %s.%s not found", spec.APIName, apiName)
	return FieldSpec{}
}

func findTableDefinition(t *testing.T, defs []domainschema.TableDefinition, tableName string) domainschema.TableDefinition {
	t.Helper()
	for _, def := range defs {
		if def.TableName == tableName {
			return def
		}
	}
	t.Fatalf("table definition %s not found", tableName)
	return domainschema.TableDefinition{}
}

func findColumnDefinition(t *testing.T, columns []domainschema.ColumnDefinition, name string) domainschema.ColumnDefinition {
	t.Helper()
	for _, column := range columns {
		if column.Name == name {
			return column
		}
	}
	t.Fatalf("column %s not found", name)
	return domainschema.ColumnDefinition{}
}

func findFieldWithContext(t *testing.T, fields []domainschema.FieldWithContext, tableName, fieldName string) domainschema.FieldWithContext {
	t.Helper()
	for _, field := range fields {
		if field.TableName == tableName && field.Field != nil && field.Field.APIName == fieldName {
			return field
		}
	}
	t.Fatalf("field metadata %s.%s not found", tableName, fieldName)
	return domainschema.FieldWithContext{}
}

func hasColumnDefinition(columns []domainschema.ColumnDefinition, name string) bool {
	for _, column := range columns {
		if column.Name == name {
			return true
		}
	}
	return false
}

func hasFieldWithContext(fields []domainschema.FieldWithContext, tableName, fieldName string) bool {
	for _, field := range fields {
		if field.TableName == tableName && field.Field != nil && field.Field.APIName == fieldName {
			return true
		}
	}
	return false
}

// TestConvertSpecToTableDefinitionsRejectsMissingSharingModel locks the
// install-pack apply contract: pack ObjectSpecs must declare sharingModel
// explicitly. L0 does not infer SharingModel for L1 objects — Salesforce's
// Metadata API CustomObject deploy treats <sharingModel> as mandatory; this
// mirrors that behavior so authorization policy is always L1-authored.
func TestConvertSpecToTableDefinitionsRejectsMissingSharingModel(t *testing.T) {
	specs := []ObjectSpec{{
		APIName:   "Issue",
		Label:     "Issue",
		TableType: "standard_object",
		Category:  "jira",
		// SharingModel intentionally omitted
	}}
	_, err := tableDefinitionsForObjectSpecs(specs)
	if err == nil {
		t.Fatal("expected error when ObjectSpec omits sharingModel; got nil")
	}
	if !strings.Contains(err.Error(), "sharingModel") {
		t.Fatalf("error should mention sharingModel; got %v", err)
	}
	if !strings.Contains(err.Error(), "Issue") {
		t.Fatalf("error should name the offending object; got %v", err)
	}
}

func TestConvertSpecToTableDefinitionsAcceptsDeclaredSharingModel(t *testing.T) {
	cases := []string{"PublicReadWrite", "PublicRead", "Private", "ControlledByParent"}
	for _, sm := range cases {
		t.Run(sm, func(t *testing.T) {
			specs := []ObjectSpec{{
				APIName:      "Issue",
				Label:        "Issue",
				TableType:    "standard_object",
				Category:     "jira",
				SharingModel: sm,
			}}
			defs, err := tableDefinitionsForObjectSpecs(specs)
			if err != nil {
				t.Fatalf("unexpected error: %v", err)
			}
			if len(defs) != 1 {
				t.Fatalf("expected 1 def, got %d", len(defs))
			}
			if defs[0].SharingModel != sm {
				t.Fatalf("SharingModel = %q, want %q", defs[0].SharingModel, sm)
			}
		})
	}
}

// Regression for issue #153 codex review: greenfield public-installer path
// (tableDefinitionsForObjectSpecs → ColumnDefinition → DDL/FieldMetadata)
// must propagate FieldSpec.ChildRelationshipName. The persistence-side
// chain (ColumnDefinition → FieldMetadata via buildFieldMetadataFromColumn)
// is covered by TestChildRelationshipNamePersistencePath. This test
// covers the missing upstream hop.
func TestTableDefinitionsForObjectSpecsPropagatesChildRelationshipName(t *testing.T) {
	specs := []ObjectSpec{{
		APIName:      "Contact",
		Label:        "Contact",
		TableType:    "standard_object",
		Category:     "crm",
		SharingModel: "Private",
		Fields: []FieldSpec{{
			APIName:               "AccountId",
			Label:                 "Account ID",
			Type:                  "reference",
			ReferenceTo:           []string{"Account"},
			RelationshipName:      "Account",
			ChildRelationshipName: "Contacts",
		}},
	}}
	defs, err := tableDefinitionsForObjectSpecs(specs)
	if err != nil {
		t.Fatalf("tableDefinitionsForObjectSpecs: %v", err)
	}
	if len(defs) != 1 {
		t.Fatalf("expected 1 def, got %d", len(defs))
	}
	col := findColumnDefinition(t, defs[0].Columns, "AccountId")
	if col.ChildRelationshipName != "Contacts" {
		t.Fatalf("ColumnDefinition.ChildRelationshipName = %q, want %q", col.ChildRelationshipName, "Contacts")
	}
	if col.RelationshipName != "Account" {
		t.Fatalf("ColumnDefinition.RelationshipName = %q, want %q", col.RelationshipName, "Account")
	}
}

func TestTableDefinitionsForObjectSpecsKeepStringLengthMetadataOnly(t *testing.T) {
	specs := []ObjectSpec{{
		APIName:      "Contact",
		Label:        "Contact",
		TableType:    "standard_object",
		Category:     "crm",
		SharingModel: "Private",
		Fields: []FieldSpec{{
			APIName: "Salesforce_Identity_Key__c",
			Label:   "Salesforce Identity Key",
			Type:    "string",
			Length:  80,
		}},
	}}
	defs, err := tableDefinitionsForObjectSpecs(specs)
	if err != nil {
		t.Fatalf("tableDefinitionsForObjectSpecs: %v", err)
	}
	col := findColumnDefinition(t, defs[0].Columns, "Salesforce_Identity_Key__c")
	if col.Length != 0 {
		t.Fatalf("ColumnDefinition.Length = %d, want 0 for platform-default VARCHAR storage", col.Length)
	}
	physical := bootstrapimpl.PhysicalColumnShape{ColumnType: "varchar(255)", Nullable: true}
	if !packObjectSpecColumnShapeMatches("Contact", physical, col, nil) {
		t.Fatal("declared metadata length changed the platform-default VARCHAR(255) physical contract")
	}
	narrowPhysical := bootstrapimpl.PhysicalColumnShape{ColumnType: "varchar(80)", Nullable: true}
	compatible, err := packObjectSpecColumnShapeRetryCompatible(context.Background(), nil, "Contact", narrowPhysical, col, nil)
	if err != nil {
		t.Fatalf("narrow physical VARCHAR retry compatibility: %v", err)
	}
	if compatible {
		t.Fatal("declared metadata length authorized a physical VARCHAR(80) contract")
	}
	fieldConstraints := newObjectSpecRequiredFieldIndex(specs)
	if !packObjectSpecColumnShapeMatches("Contact", narrowPhysical, col, fieldConstraints) {
		t.Fatal("Catalog MaxLength did not authorize the existing sufficient VARCHAR(80) capacity")
	}
	insufficientPhysical := bootstrapimpl.PhysicalColumnShape{ColumnType: "varchar(79)", Nullable: true}
	if packObjectSpecColumnShapeMatches("Contact", insufficientPhysical, col, fieldConstraints) {
		t.Fatal("Catalog MaxLength authorized an insufficient VARCHAR(79) capacity")
	}
	field := buildPackFieldSpecMetadata(specs[0].Fields[0], "", objectSpecFieldResolver{})
	if field.MaxLength == nil || *field.MaxLength != 80 {
		t.Fatalf("FieldMetadata.MaxLength = %v, want 80", field.MaxLength)
	}
}

func TestTableDefinitionsForObjectSpecsRejectMaxLengthBeyondVarcharCapacity(t *testing.T) {
	specs := []ObjectSpec{{
		APIName:      "Contact",
		Label:        "Contact",
		TableType:    "standard_object",
		Category:     "crm",
		SharingModel: "Private",
		Fields: []FieldSpec{{
			APIName: "External_Key__c",
			Type:    "string",
			Length:  300,
		}},
	}}
	if _, err := tableDefinitionsForObjectSpecs(specs); err == nil || !strings.Contains(err.Error(), "length 300 exceeds physical VARCHAR(255) capacity") {
		t.Fatalf("tableDefinitionsForObjectSpecs error = %v, want physical-capacity rejection", err)
	}

	specs[0].Fields[0].Type = "textarea"
	if _, err := tableDefinitionsForObjectSpecs(specs); err != nil {
		t.Fatalf("textarea length metadata should not be constrained by VARCHAR(255): %v", err)
	}

	specs[0].Fields[0] = FieldSpec{
		APIName:     "Computed_Key__c",
		Type:        "formula",
		Formula:     "Account.External_Key__c",
		ReturnType:  "string",
		FormulaMode: "active",
		Length:      1300,
	}
	if _, err := tableDefinitionsForObjectSpecs(specs); err != nil {
		t.Fatalf("view-only formula length metadata should not be constrained by VARCHAR(255): %v", err)
	}

	specs[0].Fields[0].FormulaStorageMode = formulastorage.ModeWriteMaterialized
	if _, err := tableDefinitionsForObjectSpecs(specs); err == nil || !strings.Contains(err.Error(), "length 1300 exceeds physical VARCHAR(255) capacity") {
		t.Fatalf("tableDefinitionsForObjectSpecs error = %v, want materialized-formula physical-capacity rejection", err)
	}
}

func TestTableDefinitionsForObjectSpecsKeepTextareaLengthMetadataOnly(t *testing.T) {
	specs := []ObjectSpec{{
		APIName:      "Task",
		Label:        "Task",
		TableType:    "standard_object",
		Category:     "crm",
		SharingModel: "Private",
		Fields: []FieldSpec{{
			APIName: "Risk_and_Next_Step__c",
			Label:   "Risk and Next Step",
			Type:    "textarea",
			Length:  255,
		}},
	}}
	defs, err := tableDefinitionsForObjectSpecs(specs)
	if err != nil {
		t.Fatalf("tableDefinitionsForObjectSpecs: %v", err)
	}
	col := findColumnDefinition(t, defs[0].Columns, "Risk_and_Next_Step__c")
	if col.Length != 0 {
		t.Fatalf("ColumnDefinition.Length = %d, want 0 for TEXT storage", col.Length)
	}
	if got := bootstrapimpl.DesiredColumnType(col); got != "TEXT" {
		t.Fatalf("physical type = %q, want TEXT", got)
	}
	field := buildPackFieldSpecMetadata(specs[0].Fields[0], "", objectSpecFieldResolver{})
	if field.MaxLength == nil || *field.MaxLength != 255 {
		t.Fatalf("FieldMetadata.MaxLength = %v, want 255", field.MaxLength)
	}
}

func TestPackFieldSpecWriterMatchesObjectSpecConverters(t *testing.T) {
	createable := false
	updateable := false
	specs := []ObjectSpec{{
		APIName:      "Case",
		Label:        "Case",
		TableType:    "standard_object",
		Category:     "crm",
		SharingModel: "Private",
		Fields: []FieldSpec{{
			APIName:               "EscalationTarget__c",
			Label:                 "Escalation Target",
			Type:                  "reference",
			ReferenceTo:           []string{"User", "Queue"},
			WritableReferenceTo:   []string{"User"},
			RelationshipName:      "EscalationTarget",
			ChildRelationshipName: "EscalatedCases",
			Createable:            &createable,
			Updateable:            &updateable,
			Validator:             "runtime_scoped_lookup",
			ValidatorConfig:       `{"scope":"OwnerId"}`,
			ControllingField:      "OwnerId",
			WriteProtectionPolicy: "trusted_context",
		}},
	}}
	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))

	defs, err := convertSpecToTableDefinitions(specs, sm)
	if err != nil {
		t.Fatalf("convertSpecToTableDefinitions: %v", err)
	}
	fields, err := buildFieldMetadata("crm-extension-pack", specs, sm, false)
	if err != nil {
		t.Fatalf("buildFieldMetadata: %v", err)
	}
	expected, err := buildPackFieldSpecWrite("crm-extension-pack", "Case", domainschema.GenerateObjectID("Case"), specs[0].Fields[0], sm, "")
	if err != nil {
		t.Fatalf("buildPackFieldSpecWrite: %v", err)
	}

	actualCol := findColumnDefinition(t, defs[0].Columns, "EscalationTarget__c")
	if !reflect.DeepEqual(actualCol, expected.ColumnDefinition) {
		t.Fatalf("ColumnDefinition mismatch:\nactual=%#v\nexpected=%#v", actualCol, expected.ColumnDefinition)
	}
	var actualField metadata.FieldWithContext
	var found bool
	for _, field := range fields {
		if field.TableName == "Case" && field.Field != nil && field.Field.APIName == "EscalationTarget__c" {
			actualField = field
			found = true
			break
		}
	}
	if !found {
		t.Fatal("EscalationTarget__c field metadata not found")
	}
	if !reflect.DeepEqual(actualField.Field, expected.Field) {
		t.Fatalf("FieldMetadata mismatch:\nactual=%#v\nexpected=%#v", actualField.Field, expected.Field)
	}
	if !reflect.DeepEqual(actualField.ColumnDefinition, expected.ColumnDefinition) {
		t.Fatalf("FieldWithContext ColumnDefinition mismatch:\nactual=%#v\nexpected=%#v", actualField.ColumnDefinition, expected.ColumnDefinition)
	}
}

func TestPackFieldSpecWriterKeepsRequiredBusinessFieldPhysicallyNullable(t *testing.T) {
	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))

	field, err := buildPackFieldSpecWrite("crm-extension-pack", "Case", domainschema.GenerateObjectID("Case"), FieldSpec{
		APIName:  "Resolution__c",
		Label:    "Resolution",
		Type:     "string",
		Required: true,
	}, sm, "")
	if err != nil {
		t.Fatalf("buildPackFieldSpecWrite: %v", err)
	}

	if field.Field == nil || !field.Field.Required {
		t.Fatalf("Field.Required = false, want true")
	}
	if !field.ColumnDefinition.Nullable {
		t.Fatalf("ColumnDefinition.Nullable = false, want true; required is save-time validation, not physical NOT NULL")
	}
}

func TestObjectSpecUniqueFieldPropagatesToColumnAndMetadata(t *testing.T) {
	specs, err := parseObjectSpecsJSON([]byte(`[
		{
			"apiName": "Lead",
			"label": "Lead",
			"tableType": "standard_object",
			"category": "crm",
			"sharingModel": "Private",
			"fields": [
				{
					"apiName": "ExternalRemoteId__c",
					"label": "External Remote ID",
					"type": "string",
					"unique": true
				}
			]
		}
	]`), "unique-field-test")
	if err != nil {
		t.Fatalf("parseObjectSpecsJSON: %v", err)
	}
	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))

	defs, err := convertSpecToTableDefinitions(specs, sm)
	if err != nil {
		t.Fatalf("convertSpecToTableDefinitions: %v", err)
	}
	leadDef := findTableDefinition(t, defs, "Lead")
	col := findColumnDefinition(t, leadDef.Columns, "ExternalRemoteId__c")
	if !col.Unique {
		t.Fatal("ColumnDefinition.Unique = false, want true from object_spec field unique")
	}

	fields, err := buildFieldMetadata("crm-extension-pack", specs, sm, false)
	if err != nil {
		t.Fatalf("buildFieldMetadata: %v", err)
	}
	field := findFieldWithContext(t, fields, "Lead", "ExternalRemoteId__c")
	if field.Field == nil || !field.Field.IsUnique {
		t.Fatalf("FieldMetadata.IsUnique = false, want true from object_spec field unique")
	}
	if !field.ColumnDefinition.Unique {
		t.Fatalf("FieldWithContext.ColumnDefinition.Unique = false, want true from object_spec field unique")
	}
}

func TestApplyObjectSpecsPublishesUniqueMetadataAfterPhysicalConstraintVerification(t *testing.T) {
	raw, err := os.ReadFile("platform_core.go")
	if err != nil {
		t.Fatalf("read platform_core.go: %v", err)
	}
	text := string(raw)
	fnStart := strings.Index(text, "func applyObjectSpecsWithResolverAndValidation(")
	if fnStart < 0 {
		t.Fatal("applyObjectSpecsWithResolverAndValidation not found")
	}
	fnEnd := strings.Index(text[fnStart:], "\nfunc ")
	if fnEnd < 0 {
		t.Fatal("could not find end of applyObjectSpecsWithResolverAndValidation")
	}
	body := text[fnStart : fnStart+fnEnd]
	resolveUniqueIdx := strings.Index(body, "objectSpecUniquePhysicalConstraintDefinitionsForApply")
	preflightIdx := strings.Index(body, "preflightOrRepairObjectSpecUniqueLegacyDuplicates")
	createTableIdx := strings.Index(body, "deps.Schema.BatchCreatePhysicalTables")
	reconcileIdx := strings.Index(body, "schemaProvisioner.ReconcileTableColumns")
	reconcileIndicesIdx := strings.Index(body, "reconcilePackTableIndices")
	dropIdx := strings.Index(body, "dropOwnedObjectSpecUniqueDowngrades")
	assertIdx := strings.Index(body, "assertObjectSpecUniquePhysicalConstraints")
	saveFieldIdx := strings.Index(body, "BatchSaveFieldMetadataWithCustomizationPolicy")
	if resolveUniqueIdx < 0 || preflightIdx < 0 || createTableIdx < 0 || reconcileIdx < 0 || reconcileIndicesIdx < 0 || dropIdx < 0 || assertIdx < 0 || saveFieldIdx < 0 {
		t.Fatalf("expected ownership-aware unique resolution plus repair-aware preflight/create/reconcile/drop/index/assert/save calls in applyObjectSpecsWithResolverAndValidation; resolve=%d preflight=%d create=%d reconcile=%d drop=%d reconcileIndices=%d assert=%d save=%d", resolveUniqueIdx, preflightIdx, createTableIdx, reconcileIdx, dropIdx, reconcileIndicesIdx, assertIdx, saveFieldIdx)
	}
	if !(resolveUniqueIdx < preflightIdx && preflightIdx < createTableIdx) {
		t.Fatalf("effective unique ownership must be resolved before legacy duplicate preflight and schema mutation; resolve=%d preflight=%d create=%d", resolveUniqueIdx, preflightIdx, createTableIdx)
	}
	if !(reconcileIdx < dropIdx && dropIdx < reconcileIndicesIdx && reconcileIndicesIdx < assertIdx && assertIdx < saveFieldIdx) {
		t.Fatalf("unique physical constraints must be reconciled, stale generated unique indexes dropped, explicit indexes reconciled, and verified before field metadata publish; reconcile=%d drop=%d reconcileIndices=%d assert=%d save=%d", reconcileIdx, dropIdx, reconcileIndicesIdx, assertIdx, saveFieldIdx)
	}
}

func TestRequiredPackFieldPreflightAcceptsExistingNullablePhysicalColumn(t *testing.T) {
	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))
	specs := []ObjectSpec{{
		APIName:      "Case",
		Label:        "Case",
		TableType:    "standard_object",
		Category:     "crm",
		SharingModel: "Private",
		Fields: []FieldSpec{{
			APIName:  "Resolution__c",
			Label:    "Resolution",
			Type:     "string",
			Required: true,
		}},
	}}

	defs, err := convertSpecToTableDefinitions(specs, sm)
	if err != nil {
		t.Fatalf("convertSpecToTableDefinitions: %v", err)
	}
	col := findColumnDefinition(t, defs[0].Columns, "Resolution__c")
	physical := bootstrapimpl.PhysicalColumnShape{
		ColumnType: "varchar(255)",
		Nullable:   true,
		DefaultVal: sql.NullString{},
	}

	if !bootstrapimpl.ColumnShapeMatches(physical, col) {
		t.Fatalf("ColumnShapeMatches(nullable physical, required field) = false; required should not force desired physical NOT NULL")
	}
}

func TestPackObjectSpecColumnShapeMatchesOnlyExactLegacySessionTokenRepresentation(t *testing.T) {
	desired := domainschema.ColumnDefinition{
		Name:     constants.FieldSysSession_Token,
		Type:     "VARBINARY(3000)",
		Nullable: false,
	}
	legacy := bootstrapimpl.PhysicalColumnShape{
		ColumnType: "varchar(750)",
		Nullable:   false,
	}
	if !packObjectSpecColumnShapeMatches(constants.TableSession, legacy, desired, nil) {
		t.Fatal("pack verifier rejected exact _System_Session.Token VARCHAR(750) compatibility")
	}

	tests := []struct {
		name      string
		tableName string
		actual    bootstrapimpl.PhysicalColumnShape
		desired   domainschema.ColumnDefinition
	}{
		{
			name:      "unrelated table",
			tableName: "_System_Other",
			actual:    legacy,
			desired:   desired,
		},
		{
			name:      "different source width",
			tableName: constants.TableSession,
			actual: bootstrapimpl.PhysicalColumnShape{
				ColumnType: "varchar(749)",
				Nullable:   false,
			},
			desired: desired,
		},
		{
			name:      "different target width",
			tableName: constants.TableSession,
			actual:    legacy,
			desired: domainschema.ColumnDefinition{
				Name:     constants.FieldSysSession_Token,
				Type:     "VARBINARY(3001)",
				Nullable: false,
			},
		},
		{
			name:      "non-type shape drift",
			tableName: constants.TableSession,
			actual: bootstrapimpl.PhysicalColumnShape{
				ColumnType: "varchar(750)",
				Nullable:   true,
			},
			desired: desired,
		},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			if packObjectSpecColumnShapeMatches(tt.tableName, tt.actual, tt.desired, nil) {
				t.Fatal("pack verifier accepted an out-of-contract physical representation")
			}
		})
	}
}

func TestBuildPackFieldSpecWriteRejectsAggregateFormulaAuthoring(t *testing.T) {
	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))

	_, err := buildPackFieldSpecWrite("crm-extension-pack", "Case", domainschema.GenerateObjectID("Case"), FieldSpec{
		APIName:     "Total__c",
		Label:       "Total",
		Type:        "formula",
		Formula:     "SUM(Child__c.Amount__c)",
		FormulaMode: "active",
		ReturnType:  "number",
	}, sm, "")
	if err == nil {
		t.Fatal("buildPackFieldSpecWrite = nil, want aggregate authoring rejection")
	}
	if !strings.Contains(err.Error(), analytics.AggregateAuthoringRequiresRollupMessage) {
		t.Fatalf("buildPackFieldSpecWrite error = %q, want aggregate authoring guidance", err.Error())
	}
}

func TestTableDefinitionsForObjectSpecsDeriveRollupSummaryStorageTypes(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Story__c",
			Label:        "Story",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{APIName: "Points__c", Label: "Points", Type: "double"},
				{APIName: "StartedAt__c", Label: "Started At", Type: "datetime"},
				{APIName: "EpicId__c", Label: "Epic", Type: "masterdetail", ReferenceTo: []string{"Epic__c"}},
			},
		},
		{
			APIName:      "Epic__c",
			Label:        "Epic",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName: "TotalPoints__c",
					Label:   "Total Points",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "Story__c",
						SummaryField:      "Points__c",
						RelationshipField: "EpicId__c",
						CalcType:          "SUM",
					},
				},
				{
					APIName: "FirstStartedAt__c",
					Label:   "First Started At",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "Story__c",
						SummaryField:      "StartedAt__c",
						RelationshipField: "EpicId__c",
						CalcType:          "MIN",
					},
				},
			},
		},
	}

	defs, err := tableDefinitionsForObjectSpecs(specs)
	if err != nil {
		t.Fatalf("tableDefinitionsForObjectSpecs: %v", err)
	}

	epicDef := defs[1]
	totalPoints := findColumnDefinition(t, epicDef.Columns, "TotalPoints__c")
	if totalPoints.LogicalType != "rollupsummary" {
		t.Fatalf("TotalPoints__c logical type = %q, want rollupsummary", totalPoints.LogicalType)
	}
	if totalPoints.Type != "DECIMAL(65,30)" {
		t.Fatalf("TotalPoints__c SQL type = %q, want DECIMAL(65,30)", totalPoints.Type)
	}

	firstStartedAt := findColumnDefinition(t, epicDef.Columns, "FirstStartedAt__c")
	if firstStartedAt.LogicalType != "rollupsummary" {
		t.Fatalf("FirstStartedAt__c logical type = %q, want rollupsummary", firstStartedAt.LogicalType)
	}
	if firstStartedAt.Type != "DATETIME" {
		t.Fatalf("FirstStartedAt__c SQL type = %q, want DATETIME", firstStartedAt.Type)
	}
}

func TestTableDefinitionsForObjectSpecsRejectRollupSummaryWithoutResolvableSummaryField(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Story__c",
			Label:        "Story",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{APIName: "EpicId__c", Label: "Epic", Type: "masterdetail", ReferenceTo: []string{"Epic__c"}},
			},
		},
		{
			APIName:      "Epic__c",
			Label:        "Epic",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName: "TotalPoints__c",
					Label:   "Total Points",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "Story__c",
						SummaryField:      "MissingField__c",
						RelationshipField: "EpicId__c",
						CalcType:          "SUM",
					},
				},
			},
		},
	}

	_, err := tableDefinitionsForObjectSpecs(specs)
	if err == nil {
		t.Fatal("expected unresolved rollup summary field to fail closed")
	}
	if !strings.Contains(err.Error(), "MissingField__c") || !strings.Contains(err.Error(), "rollupsummary") {
		t.Fatalf("error = %v, want rollupsummary missing summary field diagnostic", err)
	}
}

func TestTableDefinitionsForObjectSpecsRejectRollupSummaryCountWithoutSummaryObject(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Epic__c",
			Label:        "Epic",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName: "StoryCount__c",
					Label:   "Story Count",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "",
						RelationshipField: "EpicId__c",
						CalcType:          "COUNT",
					},
				},
			},
		},
	}

	_, err := tableDefinitionsForObjectSpecs(specs)
	if err == nil {
		t.Fatal("expected COUNT rollup without summaryObject to fail closed")
	}
	if !strings.Contains(err.Error(), "summaryObject") || !strings.Contains(err.Error(), "COUNT") {
		t.Fatalf("error = %v, want COUNT summaryObject diagnostic", err)
	}
}

func TestTableDefinitionsForObjectSpecsRejectRollupSummaryWithoutRelationshipField(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Story__c",
			Label:        "Story",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{APIName: "Points__c", Label: "Points", Type: "double"},
			},
		},
		{
			APIName:      "Epic__c",
			Label:        "Epic",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName: "TotalPoints__c",
					Label:   "Total Points",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "Story__c",
						SummaryField:      "Points__c",
						RelationshipField: "",
						CalcType:          "SUM",
					},
				},
			},
		},
	}

	_, err := tableDefinitionsForObjectSpecs(specs)
	if err == nil {
		t.Fatal("expected rollup without relationshipField to fail closed")
	}
	if !strings.Contains(err.Error(), "relationshipField") || !strings.Contains(err.Error(), "SUM") {
		t.Fatalf("error = %v, want relationshipField diagnostic", err)
	}
}

func TestTableDefinitionsForObjectSpecsRejectRollupSummaryWhenRelationshipFieldDoesNotExist(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Story__c",
			Label:        "Story",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{APIName: "Points__c", Label: "Points", Type: "double"},
			},
		},
		{
			APIName:      "Epic__c",
			Label:        "Epic",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName: "TotalPoints__c",
					Label:   "Total Points",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "Story__c",
						SummaryField:      "Points__c",
						RelationshipField: "EpicId__c",
						CalcType:          "SUM",
					},
				},
			},
		},
	}

	_, err := tableDefinitionsForObjectSpecs(specs)
	if err == nil {
		t.Fatal("expected rollup with missing relationship field to fail closed")
	}
	if !strings.Contains(err.Error(), "relationshipField") || !strings.Contains(err.Error(), "EpicId__c") {
		t.Fatalf("error = %v, want missing relationshipField diagnostic", err)
	}
}

func TestTableDefinitionsForObjectSpecsRejectRollupSummaryWhenRelationshipFieldDoesNotPointToParent(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Story__c",
			Label:        "Story",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{APIName: "Points__c", Label: "Points", Type: "double"},
				{APIName: "ProjectId__c", Label: "Project", Type: "masterdetail", ReferenceTo: []string{"Project__c"}},
			},
		},
		{
			APIName:      "Epic__c",
			Label:        "Epic",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName: "TotalPoints__c",
					Label:   "Total Points",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "Story__c",
						SummaryField:      "Points__c",
						RelationshipField: "ProjectId__c",
						CalcType:          "SUM",
					},
				},
			},
		},
	}

	_, err := tableDefinitionsForObjectSpecs(specs)
	if err == nil {
		t.Fatal("expected rollup with wrong relationship target to fail closed")
	}
	if !strings.Contains(err.Error(), "relationshipField") || !strings.Contains(err.Error(), "Epic__c") {
		t.Fatalf("error = %v, want relationship target diagnostic", err)
	}
}

func TestTableDefinitionsForObjectSpecsRejectRollupSummaryWhenRelationshipFieldIsPolymorphic(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Story__c",
			Label:        "Story",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{APIName: "Points__c", Label: "Points", Type: "double"},
				{APIName: "ParentId__c", Label: "Parent", Type: "reference", ReferenceTo: []string{"Epic__c", "Project__c"}, IsPolymorphic: true},
			},
		},
		{
			APIName:      "Epic__c",
			Label:        "Epic",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName: "TotalPoints__c",
					Label:   "Total Points",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "Story__c",
						SummaryField:      "Points__c",
						RelationshipField: "ParentId__c",
						CalcType:          "SUM",
					},
				},
			},
		},
	}

	_, err := tableDefinitionsForObjectSpecs(specs)
	if err == nil {
		t.Fatal("expected rollup with polymorphic relationship field to fail closed")
	}
	if !strings.Contains(err.Error(), "relationshipField") || !strings.Contains(err.Error(), "Epic__c") {
		t.Fatalf("error = %v, want polymorphic relationship target diagnostic", err)
	}
}

func TestBuildFieldMetadataCarriesRollupSummaryColumnDefinition(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Story__c",
			Label:        "Story",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{APIName: "Points__c", Label: "Points", Type: "double"},
				{APIName: "EpicId__c", Label: "Epic", Type: "masterdetail", ReferenceTo: []string{"Epic__c"}},
			},
		},
		{
			APIName:      "Epic__c",
			Label:        "Epic",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName: "TotalPoints__c",
					Label:   "Total Points",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "Story__c",
						SummaryField:      "Points__c",
						RelationshipField: "EpicId__c",
						CalcType:          "SUM",
					},
				},
			},
		},
	}

	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))
	fields, err := buildFieldMetadata("jira-like-sample", specs, sm, false)
	if err != nil {
		t.Fatalf("buildFieldMetadata: %v", err)
	}

	var rollupField metadata.FieldWithContext
	var found bool
	for _, field := range fields {
		if field.TableName == "Epic__c" && field.Field != nil && field.Field.APIName == "TotalPoints__c" {
			rollupField = field
			found = true
			break
		}
	}
	if !found {
		t.Fatal("TotalPoints__c field metadata not found")
	}
	if rollupField.Field.Type != constants.FieldTypeRollupSummary {
		t.Fatalf("field type = %q, want rollupsummary", rollupField.Field.Type)
	}
	if rollupField.ColumnDefinition.LogicalType != "rollupsummary" {
		t.Fatalf("column logical type = %q, want rollupsummary", rollupField.ColumnDefinition.LogicalType)
	}
	if rollupField.ColumnDefinition.Type != "DECIMAL(65,30)" {
		t.Fatalf("column SQL type = %q, want DECIMAL(65,30)", rollupField.ColumnDefinition.Type)
	}
}

func TestBuildFieldMetadataCanonicalizesRollupSummaryCalcType(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Story__c",
			Label:        "Story",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{APIName: "Points__c", Label: "Points", Type: "double"},
				{APIName: "EpicId__c", Label: "Epic", Type: "masterdetail", ReferenceTo: []string{"Epic__c"}},
			},
		},
		{
			APIName:      "Epic__c",
			Label:        "Epic",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName: "TotalPoints__c",
					Label:   "Total Points",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "Story__c",
						SummaryField:      "Points__c",
						RelationshipField: "EpicId__c",
						CalcType:          "sum",
					},
				},
			},
		},
	}

	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))
	fields, err := buildFieldMetadata("jira-like-sample", specs, sm, false)
	if err != nil {
		t.Fatalf("buildFieldMetadata: %v", err)
	}

	var rollupField metadata.FieldWithContext
	var found bool
	for _, field := range fields {
		if field.TableName == "Epic__c" && field.Field != nil && field.Field.APIName == "TotalPoints__c" {
			rollupField = field
			found = true
			break
		}
	}
	if !found {
		t.Fatal("TotalPoints__c field metadata not found")
	}
	if rollupField.Field.RollupConfig == nil {
		t.Fatal("rollup config was not preserved")
	}
	if rollupField.Field.RollupConfig.CalcType != "SUM" {
		t.Fatalf("field rollup calcType = %q, want SUM", rollupField.Field.RollupConfig.CalcType)
	}
	if specs[1].Fields[0].RollupConfig.CalcType != "sum" {
		t.Fatalf("input spec calcType mutated to %q, want original lowercase", specs[1].Fields[0].RollupConfig.CalcType)
	}
}

func TestBuildFieldMetadataCanonicalizesRollupSummaryLinkageNames(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Story__c",
			Label:        "Story",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{APIName: "Points__c", Label: "Points", Type: "double"},
				{APIName: "EpicId__c", Label: "Epic", Type: "masterdetail", ReferenceTo: []string{"Epic__c"}},
			},
		},
		{
			APIName:      "Epic__c",
			Label:        "Epic",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName: "TotalPoints__c",
					Label:   "Total Points",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "story__c",
						SummaryField:      "points__c",
						RelationshipField: "epicid__c",
						CalcType:          "sum",
					},
				},
			},
		},
	}

	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))
	fields, err := buildFieldMetadata("jira-like-sample", specs, sm, false)
	if err != nil {
		t.Fatalf("buildFieldMetadata: %v", err)
	}

	var rollupField metadata.FieldWithContext
	var found bool
	for _, field := range fields {
		if field.TableName == "Epic__c" && field.Field != nil && field.Field.APIName == "TotalPoints__c" {
			rollupField = field
			found = true
			break
		}
	}
	if !found {
		t.Fatal("TotalPoints__c field metadata not found")
	}
	if rollupField.Field.RollupConfig == nil {
		t.Fatal("rollup config was not preserved")
	}
	if rollupField.Field.RollupConfig.SummaryObject != "Story__c" {
		t.Fatalf("field rollup summaryObject = %q, want Story__c", rollupField.Field.RollupConfig.SummaryObject)
	}
	if rollupField.Field.RollupConfig.SummaryField != "Points__c" {
		t.Fatalf("field rollup summaryField = %q, want Points__c", rollupField.Field.RollupConfig.SummaryField)
	}
	if rollupField.Field.RollupConfig.RelationshipField != "EpicId__c" {
		t.Fatalf("field rollup relationshipField = %q, want EpicId__c", rollupField.Field.RollupConfig.RelationshipField)
	}
	if specs[1].Fields[0].RollupConfig.SummaryObject != "story__c" {
		t.Fatalf("input spec summaryObject mutated to %q, want original lowercase", specs[1].Fields[0].RollupConfig.SummaryObject)
	}
}

func TestBuildObjectMetadataCanonicalizesRollupSummaryCalcType(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Epic__c",
			Label:        "Epic",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName: "TotalPoints__c",
					Label:   "Total Points",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "Story__c",
						SummaryField:      "Points__c",
						RelationshipField: "EpicId__c",
						CalcType:          "sum",
					},
				},
			},
		},
	}

	objects := buildObjectMetadata(specs)
	if len(objects) != 1 {
		t.Fatalf("buildObjectMetadata() returned %d objects, want 1", len(objects))
	}
	if objects[0].Fields[0].RollupConfig == nil {
		t.Fatal("rollup config was not preserved")
	}
	if objects[0].Fields[0].RollupConfig.CalcType != "SUM" {
		t.Fatalf("object metadata rollup calcType = %q, want SUM", objects[0].Fields[0].RollupConfig.CalcType)
	}
	if specs[0].Fields[0].RollupConfig.CalcType != "sum" {
		t.Fatalf("input spec calcType mutated to %q, want original lowercase", specs[0].Fields[0].RollupConfig.CalcType)
	}
}

func TestBuildObjectMetadataCanonicalizesRollupSummaryLinkageNames(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Story__c",
			Label:        "Story",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{APIName: "Points__c", Label: "Points", Type: "double"},
				{APIName: "EpicId__c", Label: "Epic", Type: "masterdetail", ReferenceTo: []string{"Epic__c"}},
			},
		},
		{
			APIName:      "Epic__c",
			Label:        "Epic",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName: "TotalPoints__c",
					Label:   "Total Points",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "story__c",
						SummaryField:      "points__c",
						RelationshipField: "epicid__c",
						CalcType:          "sum",
					},
				},
			},
		},
	}

	objects := buildObjectMetadata(specs)
	if len(objects) != 2 {
		t.Fatalf("buildObjectMetadata() returned %d objects, want 2", len(objects))
	}
	epic := objects[1]
	if epic.Fields[0].RollupConfig == nil {
		t.Fatal("rollup config was not preserved")
	}
	if epic.Fields[0].RollupConfig.SummaryObject != "Story__c" {
		t.Fatalf("object metadata rollup summaryObject = %q, want Story__c", epic.Fields[0].RollupConfig.SummaryObject)
	}
	if epic.Fields[0].RollupConfig.SummaryField != "Points__c" {
		t.Fatalf("object metadata rollup summaryField = %q, want Points__c", epic.Fields[0].RollupConfig.SummaryField)
	}
	if epic.Fields[0].RollupConfig.RelationshipField != "EpicId__c" {
		t.Fatalf("object metadata rollup relationshipField = %q, want EpicId__c", epic.Fields[0].RollupConfig.RelationshipField)
	}
	if specs[1].Fields[0].RollupConfig.RelationshipField != "epicid__c" {
		t.Fatalf("input spec relationshipField mutated to %q, want original lowercase", specs[1].Fields[0].RollupConfig.RelationshipField)
	}
}

func TestBuildFieldMetadataCanonicalizesCountRollupSummaryField(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Story__c",
			Label:        "Story",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName:             "EpicId__c",
					Label:               "Epic",
					Type:                "masterdetail",
					ReferenceTo:         []string{"Epic__c"},
					WritableReferenceTo: []string{"Epic__c"},
				},
			},
		},
		{
			APIName:      "Epic__c",
			Label:        "Epic",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName: "StoryCount__c",
					Label:   "Story Count",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "Story__c",
						SummaryField:      "",
						RelationshipField: "EpicId__c",
						CalcType:          "count",
					},
				},
			},
		},
	}

	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))
	fields, err := buildFieldMetadata("jira-like-sample", specs, sm, false)
	if err != nil {
		t.Fatalf("buildFieldMetadata: %v", err)
	}

	var rollupField metadata.FieldWithContext
	var found bool
	for _, field := range fields {
		if field.TableName == "Epic__c" && field.Field != nil && field.Field.APIName == "StoryCount__c" {
			rollupField = field
			found = true
			break
		}
	}
	if !found {
		t.Fatal("StoryCount__c field metadata not found")
	}
	if rollupField.Field.RollupConfig == nil {
		t.Fatal("rollup config was not preserved")
	}
	if rollupField.Field.RollupConfig.CalcType != "COUNT" {
		t.Fatalf("field rollup calcType = %q, want COUNT", rollupField.Field.RollupConfig.CalcType)
	}
	if rollupField.Field.RollupConfig.SummaryField != "*" {
		t.Fatalf("field rollup summaryField = %q, want *", rollupField.Field.RollupConfig.SummaryField)
	}
	if specs[1].Fields[0].RollupConfig.SummaryField != "" {
		t.Fatalf("input spec summaryField mutated to %q, want empty string", specs[1].Fields[0].RollupConfig.SummaryField)
	}
}

func TestBuildFieldMetadataCanonicalizesInvalidCountRollupSummaryField(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Story__c",
			Label:        "Story",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName:             "EpicId__c",
					Label:               "Epic",
					Type:                "masterdetail",
					ReferenceTo:         []string{"Epic__c"},
					WritableReferenceTo: []string{"Epic__c"},
				},
			},
		},
		{
			APIName:      "Epic__c",
			Label:        "Epic",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName: "StoryCount__c",
					Label:   "Story Count",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "Story__c",
						SummaryField:      "bad-field",
						RelationshipField: "EpicId__c",
						CalcType:          "count",
					},
				},
			},
		},
	}

	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))
	fields, err := buildFieldMetadata("jira-like-sample", specs, sm, false)
	if err != nil {
		t.Fatalf("buildFieldMetadata: %v", err)
	}

	var rollupField metadata.FieldWithContext
	var found bool
	for _, field := range fields {
		if field.TableName == "Epic__c" && field.Field != nil && field.Field.APIName == "StoryCount__c" {
			rollupField = field
			found = true
			break
		}
	}
	if !found {
		t.Fatal("StoryCount__c field metadata not found")
	}
	if rollupField.Field.RollupConfig == nil {
		t.Fatal("rollup config was not preserved")
	}
	if rollupField.Field.RollupConfig.SummaryField != "*" {
		t.Fatalf("field rollup summaryField = %q, want *", rollupField.Field.RollupConfig.SummaryField)
	}
	if specs[1].Fields[0].RollupConfig.SummaryField != "bad-field" {
		t.Fatalf("input spec summaryField mutated to %q, want original bad-field", specs[1].Fields[0].RollupConfig.SummaryField)
	}
}

func TestBuildObjectMetadataCanonicalizesCountRollupSummaryField(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Epic__c",
			Label:        "Epic",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName: "StoryCount__c",
					Label:   "Story Count",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "Story__c",
						SummaryField:      "",
						RelationshipField: "EpicId__c",
						CalcType:          "count",
					},
				},
			},
		},
	}

	objects := buildObjectMetadata(specs)
	if len(objects) != 1 {
		t.Fatalf("buildObjectMetadata() returned %d objects, want 1", len(objects))
	}
	if objects[0].Fields[0].RollupConfig == nil {
		t.Fatal("rollup config was not preserved")
	}
	if objects[0].Fields[0].RollupConfig.CalcType != "COUNT" {
		t.Fatalf("object metadata rollup calcType = %q, want COUNT", objects[0].Fields[0].RollupConfig.CalcType)
	}
	if objects[0].Fields[0].RollupConfig.SummaryField != "*" {
		t.Fatalf("object metadata rollup summaryField = %q, want *", objects[0].Fields[0].RollupConfig.SummaryField)
	}
	if specs[0].Fields[0].RollupConfig.SummaryField != "" {
		t.Fatalf("input spec summaryField mutated to %q, want empty string", specs[0].Fields[0].RollupConfig.SummaryField)
	}
}

func TestBuildObjectMetadataCanonicalizesInvalidCountRollupSummaryField(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Epic__c",
			Label:        "Epic",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName: "StoryCount__c",
					Label:   "Story Count",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "Story__c",
						SummaryField:      "bad-field",
						RelationshipField: "EpicId__c",
						CalcType:          "count",
					},
				},
			},
		},
	}

	objects := buildObjectMetadata(specs)
	if len(objects) != 1 {
		t.Fatalf("buildObjectMetadata() returned %d objects, want 1", len(objects))
	}
	if objects[0].Fields[0].RollupConfig == nil {
		t.Fatal("rollup config was not preserved")
	}
	if objects[0].Fields[0].RollupConfig.SummaryField != "*" {
		t.Fatalf("object metadata rollup summaryField = %q, want *", objects[0].Fields[0].RollupConfig.SummaryField)
	}
	if specs[0].Fields[0].RollupConfig.SummaryField != "bad-field" {
		t.Fatalf("input spec summaryField mutated to %q, want original bad-field", specs[0].Fields[0].RollupConfig.SummaryField)
	}
}

func TestTableDefinitionsForObjectSpecsDeriveRollupSummaryStorageTypesFromFormulaReturnType(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Story__c",
			Label:        "Story",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName:     "ComputedPoints__c",
					Label:       "Computed Points",
					Type:        "formula",
					ReturnType:  "double",
					FormulaMode: "frozen",
					Formula:     "8",
				},
				{
					APIName:     "ComputedDueDate__c",
					Label:       "Computed Due Date",
					Type:        "formula",
					ReturnType:  "date",
					FormulaMode: "frozen",
					Formula:     "TODAY()",
				},
				{APIName: "EpicId__c", Label: "Epic", Type: "masterdetail", ReferenceTo: []string{"Epic__c"}},
			},
		},
		{
			APIName:      "Epic__c",
			Label:        "Epic",
			TableType:    "custom_object",
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName: "TotalComputedPoints__c",
					Label:   "Total Computed Points",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "Story__c",
						SummaryField:      "ComputedPoints__c",
						RelationshipField: "EpicId__c",
						CalcType:          "SUM",
					},
				},
				{
					APIName: "FirstComputedDueDate__c",
					Label:   "First Computed Due Date",
					Type:    "rollupsummary",
					RollupConfig: &models.RollupConfig{
						SummaryObject:     "Story__c",
						SummaryField:      "ComputedDueDate__c",
						RelationshipField: "EpicId__c",
						CalcType:          "MIN",
					},
				},
			},
		},
	}

	defs, err := tableDefinitionsForObjectSpecs(specs)
	if err != nil {
		t.Fatalf("tableDefinitionsForObjectSpecs: %v", err)
	}

	epicDef := defs[1]
	totalComputedPoints := findColumnDefinition(t, epicDef.Columns, "TotalComputedPoints__c")
	if totalComputedPoints.Type != "DECIMAL(65,30)" {
		t.Fatalf("TotalComputedPoints__c SQL type = %q, want DECIMAL(65,30)", totalComputedPoints.Type)
	}

	firstComputedDueDate := findColumnDefinition(t, epicDef.Columns, "FirstComputedDueDate__c")
	if firstComputedDueDate.Type != "DATE" {
		t.Fatalf("FirstComputedDueDate__c SQL type = %q, want DATE", firstComputedDueDate.Type)
	}
}

func TestTableDefinitionsForObjectSpecsGeneratePolymorphicTypeColumns(t *testing.T) {
	specs := []ObjectSpec{{
		APIName:      "Task",
		Label:        "Task",
		TableType:    "standard_object",
		Category:     "crm",
		SharingModel: "Private",
		Fields: []FieldSpec{{
			APIName:       "WhatId",
			Label:         "Related To",
			Type:          "reference",
			ReferenceTo:   []string{"Account", "Opportunity"},
			IsPolymorphic: true,
		}},
	}}
	defs, err := tableDefinitionsForObjectSpecs(specs)
	if err != nil {
		t.Fatalf("tableDefinitionsForObjectSpecs: %v", err)
	}
	whatType := findColumnDefinition(t, defs[0].Columns, "WhatId_type")
	if whatType.Type != "VARCHAR(255)" || !whatType.Nullable {
		t.Fatalf("WhatId_type = %#v, want nullable VARCHAR(255)", whatType)
	}
}

// Issue #1407 / PR #1416 review follow-up: the pack apply path must surface its
// relationship FK columns to the shared EnsureRelationshipFKIndexes step. This
// asserts the conversion produces columns the shared rule (schema.
// RelationshipFKColumns) selects — see schema.TestRelationshipFKColumns for the
// rule itself.
func TestTableDefinitionsForObjectSpecsSurfaceRelationshipFKColumns(t *testing.T) {
	specs := []ObjectSpec{{
		APIName:      "OrderLike",
		Label:        "OrderLike",
		TableType:    "standard_object",
		Category:     "crm",
		SharingModel: "Private",
		Fields: []FieldSpec{
			{APIName: "AccountId", Label: "Account", Type: "reference", ReferenceTo: []string{"Account"}},
			{APIName: "ContractId", Label: "Contract", Type: "masterdetail", ReferenceTo: []string{"Contract"}},
			{APIName: "Description", Label: "Description", Type: "string"},
			{APIName: "WhatId", Label: "Related To", Type: "reference", ReferenceTo: []string{"Account", "Opportunity"}, IsPolymorphic: true},
		},
	}}
	defs, err := tableDefinitionsForObjectSpecs(specs)
	if err != nil {
		t.Fatalf("tableDefinitionsForObjectSpecs: %v", err)
	}

	got := map[string]bool{}
	for _, fk := range domainschema.RelationshipFKColumns(defs) {
		got[fk.Column] = true
	}
	for _, want := range []string{"AccountId", "ContractId", "WhatId"} {
		if !got[want] {
			t.Errorf("relationship FK column %s not surfaced for indexing", want)
		}
	}
	// Scope guards: non-relationship column, polymorphic discriminator, and
	// injected system columns must never be selected.
	for _, notWant := range []string{"Description", "WhatId_type", "OwnerId", "Id"} {
		if got[notWant] {
			t.Errorf("column %s must not be selected as a relationship FK", notWant)
		}
	}
}

func TestBuildFieldMetadataRegistersGeneratedPolymorphicDiscriminatorReadOnly(t *testing.T) {
	specs := []ObjectSpec{{
		APIName:      "Task",
		Label:        "Task",
		TableType:    "standard_object",
		Category:     "crm",
		SharingModel: "Private",
		Fields: []FieldSpec{{
			APIName:       "WhatId",
			Label:         "Related To",
			Type:          "reference",
			ReferenceTo:   []string{"Account", "Opportunity"},
			IsPolymorphic: true,
		}},
	}}
	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))
	fields, err := buildFieldMetadata("salesforce-crm-standard", specs, sm, false)
	if err != nil {
		t.Fatalf("buildFieldMetadata: %v", err)
	}
	var found bool
	for _, field := range fields {
		if field.TableName != "Task" || field.Field == nil || field.Field.APIName != "WhatId_type" {
			continue
		}
		found = true
		if field.Field.Createable || field.Field.Updateable {
			t.Fatalf("WhatId_type Createable/Updateable = %v/%v, want false/false", field.Field.Createable, field.Field.Updateable)
		}
		if field.OwnerKind != "pack" || field.OwnerPack != "salesforce-crm-standard" {
			t.Fatalf("WhatId_type owner = %s/%s, want pack/salesforce-crm-standard", field.OwnerKind, field.OwnerPack)
		}
	}
	if !found {
		t.Fatal("generated WhatId_type field metadata not found")
	}
}

func TestConvertSpecToTableDefinitionsInjectsRecordTypeIDOnlyForCustomObjects(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Issue",
			Label:        "Issue",
			TableType:    string(constants.TableTypeStandardObject),
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{{
				APIName:     "Subject",
				Label:       "Subject",
				Type:        "string",
				Required:    true,
				IsNameField: true,
			}},
		},
		{
			APIName:      "Issue__c",
			Label:        "Issue",
			TableType:    string(constants.TableTypeCustomObject),
			Category:     "tenant",
			SharingModel: "Private",
			Fields: []FieldSpec{{
				APIName:     "Subject__c",
				Label:       "Subject",
				Type:        "string",
				Required:    true,
				IsNameField: true,
			}},
		},
	}

	defs, err := tableDefinitionsForObjectSpecs(specs)
	if err != nil {
		t.Fatalf("tableDefinitionsForObjectSpecs: %v", err)
	}

	standardDef := findTableDefinition(t, defs, "Issue")
	if hasColumnDefinition(standardDef.Columns, constants.FieldRecordTypeID) {
		t.Fatalf("standard object columns unexpectedly include %s: %#v", constants.FieldRecordTypeID, standardDef.Columns)
	}

	customDef := findTableDefinition(t, defs, "Issue__c")
	if !hasColumnDefinition(customDef.Columns, constants.FieldRecordTypeID) {
		t.Fatalf("custom object columns missing %s: %#v", constants.FieldRecordTypeID, customDef.Columns)
	}
}

func TestBuildFieldMetadataInjectsRecordTypeIDOnlyForCustomObjects(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Issue",
			Label:        "Issue",
			TableType:    string(constants.TableTypeStandardObject),
			Category:     "jira",
			SharingModel: "Private",
			Fields: []FieldSpec{{
				APIName:     "Subject",
				Label:       "Subject",
				Type:        "string",
				Required:    true,
				IsNameField: true,
			}},
		},
		{
			APIName:      "Issue__c",
			Label:        "Issue",
			TableType:    string(constants.TableTypeCustomObject),
			Category:     "tenant",
			SharingModel: "Private",
			Fields: []FieldSpec{{
				APIName:     "Subject__c",
				Label:       "Subject",
				Type:        "string",
				Required:    true,
				IsNameField: true,
			}},
		},
	}

	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))
	fields, err := buildFieldMetadata("jira-like-sample", specs, sm, false)
	if err != nil {
		t.Fatalf("buildFieldMetadata: %v", err)
	}

	if hasFieldWithContext(fields, "Issue", constants.FieldRecordTypeID) {
		t.Fatalf("standard object field metadata unexpectedly includes %s", constants.FieldRecordTypeID)
	}
	if !hasFieldWithContext(fields, "Issue__c", constants.FieldRecordTypeID) {
		t.Fatalf("custom object field metadata missing %s", constants.FieldRecordTypeID)
	}
}

func TestPackDeclaredLastActivityDateStaysPackOwnedWithoutImplicitInjection(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Account",
			Label:        "Account",
			TableType:    string(constants.TableTypeStandardObject),
			Category:     "crm",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName:     "Name",
					Label:       "Account Name",
					Type:        "string",
					Required:    true,
					IsNameField: true,
				},
				{
					APIName:    "LastActivityDate",
					Label:      "Last Activity Date",
					Type:       "date",
					Createable: boolPtr(false),
					Updateable: boolPtr(false),
				},
			},
		},
		{
			APIName:      "Contact",
			Label:        "Contact",
			TableType:    string(constants.TableTypeStandardObject),
			Category:     "crm",
			SharingModel: "Private",
			Fields: []FieldSpec{
				{
					APIName:     "LastName",
					Label:       "Last Name",
					Type:        "string",
					Required:    true,
					IsNameField: true,
				},
				{
					APIName:    "LastActivityDate",
					Label:      "Last Activity Date",
					Type:       "date",
					Createable: boolPtr(false),
					Updateable: boolPtr(false),
				},
			},
		},
		{
			APIName:      "Case",
			Label:        "Case",
			TableType:    string(constants.TableTypeStandardObject),
			Category:     "crm",
			SharingModel: "Private",
			Fields: []FieldSpec{{
				APIName:     "Subject",
				Label:       "Subject",
				Type:        "string",
				Required:    true,
				IsNameField: true,
			}},
		},
	}

	defs, err := tableDefinitionsForObjectSpecs(specs)
	if err != nil {
		t.Fatalf("tableDefinitionsForObjectSpecs: %v", err)
	}

	accountDef := findTableDefinition(t, defs, "Account")
	accountLastActivity := findColumnDefinition(t, accountDef.Columns, "LastActivityDate")
	if accountLastActivity.Type != "DATE" {
		t.Fatalf("Account.LastActivityDate SQL type = %q, want DATE", accountLastActivity.Type)
	}
	if got := countColumnDefinitions(accountDef.Columns, "LastActivityDate"); got != 1 {
		t.Fatalf("Account.LastActivityDate column count = %d, want 1", got)
	}

	contactDef := findTableDefinition(t, defs, "Contact")
	contactLastActivity := findColumnDefinition(t, contactDef.Columns, "LastActivityDate")
	if contactLastActivity.Type != "DATE" {
		t.Fatalf("Contact.LastActivityDate SQL type = %q, want DATE", contactLastActivity.Type)
	}
	if got := countColumnDefinitions(contactDef.Columns, "LastActivityDate"); got != 1 {
		t.Fatalf("Contact.LastActivityDate column count = %d, want 1", got)
	}

	caseDef := findTableDefinition(t, defs, "Case")
	if hasColumnDefinition(caseDef.Columns, "LastActivityDate") {
		t.Fatalf("Case columns unexpectedly include LastActivityDate: %#v", caseDef.Columns)
	}

	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))
	fields, err := buildFieldMetadata("salesforce-crm-standard", specs, sm, false)
	if err != nil {
		t.Fatalf("buildFieldMetadata: %v", err)
	}

	accountField := findFieldWithContext(t, fields, "Account", "LastActivityDate")
	if accountField.Field == nil {
		t.Fatal("Account.LastActivityDate field metadata = nil")
	}
	if accountField.Field.Type != constants.FieldTypeDate {
		t.Fatalf("Account.LastActivityDate type = %q, want %q", accountField.Field.Type, constants.FieldTypeDate)
	}
	if accountField.Field.IsSystem {
		t.Fatal("Account.LastActivityDate IsSystem = true, want false")
	}
	if accountField.Field.Createable || accountField.Field.Updateable {
		t.Fatalf("Account.LastActivityDate Createable/Updateable = %v/%v, want false/false",
			accountField.Field.Createable, accountField.Field.Updateable)
	}
	if got := countFieldWithContext(fields, "Account", "LastActivityDate"); got != 1 {
		t.Fatalf("Account.LastActivityDate field metadata count = %d, want 1", got)
	}

	contactField := findFieldWithContext(t, fields, "Contact", "LastActivityDate")
	if contactField.Field == nil {
		t.Fatal("Contact.LastActivityDate field metadata = nil")
	}
	if contactField.Field.Type != constants.FieldTypeDate {
		t.Fatalf("Contact.LastActivityDate type = %q, want %q", contactField.Field.Type, constants.FieldTypeDate)
	}
	if contactField.Field.IsSystem {
		t.Fatal("Contact.LastActivityDate IsSystem = true, want false")
	}
	if contactField.Field.Createable || contactField.Field.Updateable {
		t.Fatalf("Contact.LastActivityDate Createable/Updateable = %v/%v, want false/false",
			contactField.Field.Createable, contactField.Field.Updateable)
	}
	if got := countFieldWithContext(fields, "Contact", "LastActivityDate"); got != 1 {
		t.Fatalf("Contact.LastActivityDate field metadata count = %d, want 1", got)
	}

	if hasFieldWithContext(fields, "Case", "LastActivityDate") {
		t.Fatal("Case field metadata unexpectedly includes LastActivityDate")
	}

	accountMinimal := specToMinimalObjectMetadata(specs[0])
	if !metadataFieldExists(accountMinimal, "LastActivityDate") {
		t.Fatal("specToMinimalObjectMetadata(Account) missing LastActivityDate")
	}

	contactMinimal := specToMinimalObjectMetadata(specs[1])
	if !metadataFieldExists(contactMinimal, "LastActivityDate") {
		t.Fatal("specToMinimalObjectMetadata(Contact) missing LastActivityDate")
	}

	caseMinimal := specToMinimalObjectMetadata(specs[2])
	if metadataFieldExists(caseMinimal, "LastActivityDate") {
		t.Fatal("specToMinimalObjectMetadata(Case) unexpectedly includes LastActivityDate")
	}
}

func countColumnDefinitions(columns []domainschema.ColumnDefinition, columnName string) int {
	var count int
	for _, col := range columns {
		if strings.EqualFold(col.Name, columnName) {
			count++
		}
	}
	return count
}

func countFieldWithContext(fields []metadata.FieldWithContext, objectAPIName string, fieldAPIName string) int {
	var count int
	for _, field := range fields {
		if field.TableName == objectAPIName && field.Field != nil && strings.EqualFold(field.Field.APIName, fieldAPIName) {
			count++
		}
	}
	return count
}

func TestBuildFieldMetadataKeepsPackDeclaredCustomFieldWhenTenantManifestIsRegistered(t *testing.T) {
	t.Cleanup(func() {
		packspec.RegisterTenantFieldManifest(nil)
	})

	packspec.RegisterTenantFieldManifest([]byte(`[
		{
			"apiName": "Issue",
			"fields": [
				{"apiName": "EscalationCode__c"}
			]
		}
	]`))

	specs := []ObjectSpec{{
		APIName:      "Issue",
		Label:        "Issue",
		TableType:    "standard_object",
		Category:     "jira",
		SharingModel: "Private",
		Fields: []FieldSpec{
			{APIName: "Subject", Label: "Subject", Type: "string", IsNameField: true},
			{APIName: "EscalationCode__c", Label: "Escalation Code", Type: "string"},
		},
	}}

	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))
	fields, err := buildFieldMetadata("jira-like-sample", specs, sm, false)
	if err != nil {
		t.Fatalf("buildFieldMetadata: %v", err)
	}

	for _, field := range fields {
		if field.TableName == "Issue" && field.Field != nil && field.Field.APIName == "EscalationCode__c" {
			return
		}
	}

	t.Fatal("buildFieldMetadata dropped pack-declared Issue.EscalationCode__c when tenant field manifest was registered")
}

func TestBuildFieldMetadataLocksExplicitPolymorphicDiscriminatorDeclaration(t *testing.T) {
	specs := []ObjectSpec{{
		APIName:      "Task",
		Label:        "Task",
		TableType:    "standard_object",
		Category:     "crm",
		SharingModel: "Private",
		Fields: []FieldSpec{
			{
				APIName:       "WhatId",
				Label:         "Related To",
				Type:          "reference",
				ReferenceTo:   []string{"Account", "Opportunity"},
				IsPolymorphic: true,
			},
			{
				APIName: "WhatId_type",
				Label:   "Related To Type",
				Type:    "string",
			},
		},
	}}
	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))
	fields, err := buildFieldMetadata("salesforce-crm-standard", specs, sm, false)
	if err != nil {
		t.Fatalf("buildFieldMetadata: %v", err)
	}
	var matches int
	for _, field := range fields {
		if field.TableName != "Task" || field.Field == nil || field.Field.APIName != "WhatId_type" {
			continue
		}
		matches++
		if field.Field.GeneratedForPolymorphicReference != "WhatId" {
			t.Fatalf("WhatId_type GeneratedForPolymorphicReference = %q, want WhatId", field.Field.GeneratedForPolymorphicReference)
		}
		if field.Field.Createable || field.Field.Updateable {
			t.Fatalf("explicit WhatId_type Createable/Updateable = %v/%v, want false/false", field.Field.Createable, field.Field.Updateable)
		}
		if field.ColumnDefinition.GeneratedForPolymorphicReference != "WhatId" {
			t.Fatalf("WhatId_type column GeneratedForPolymorphicReference = %q, want WhatId", field.ColumnDefinition.GeneratedForPolymorphicReference)
		}
	}
	if matches != 1 {
		t.Fatalf("WhatId_type metadata rows = %d, want 1", matches)
	}
}

func TestTableDefinitionsForObjectSpecsPropagateObjectKeyPrefix(t *testing.T) {
	specs := []ObjectSpec{{
		APIName:      "OpportunityContactRole",
		KeyPrefix:    "00K",
		Label:        "Opportunity Contact Role",
		TableType:    "standard_object",
		Category:     "crm",
		SharingModel: "Private",
	}}
	defs, err := tableDefinitionsForObjectSpecs(specs)
	if err != nil {
		t.Fatalf("tableDefinitionsForObjectSpecs: %v", err)
	}
	if len(defs) != 1 {
		t.Fatalf("expected 1 def, got %d", len(defs))
	}
	if defs[0].KeyPrefix != "00K" {
		t.Fatalf("TableDefinition.KeyPrefix = %q, want %q", defs[0].KeyPrefix, "00K")
	}
}

// TestValidateSpecLockRulesAcceptsValidExpressions pins that validateSpecLockRules
// accepts boolean-returning expressions at pack apply / verify time.
// The runtime-probe strategy evaluates against an empty record with no user context;
// comparison expressions return bool false and are accepted. Non-boolean bare
// field references are rejected (see TestValidateSpecLockRulesRejectsNonBoolean).
func TestValidateSpecLockRulesAcceptsValidExpressions(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Widget",
			Label:        "Widget",
			TableType:    "standard_object",
			Category:     "crm",
			SharingModel: "Private",
			LockRule:     `Status == "Closed"`,
			Fields: []FieldSpec{
				{APIName: "Status", Label: "Status", Type: "string"},
			},
		},
		{
			APIName:      "Ticket",
			Label:        "Ticket",
			TableType:    "standard_object",
			Category:     "crm",
			SharingModel: "Private",
			LockRule:     `IsArchived || Status == "Archived"`,
			Fields: []FieldSpec{
				{APIName: "Status", Label: "Status", Type: "string"},
				{APIName: "IsArchived", Label: "Is Archived", Type: "boolean"},
			},
		},
		{
			APIName:      "Task",
			Label:        "Task",
			TableType:    "standard_object",
			Category:     "crm",
			SharingModel: "Private",
			// No LockRule — should be a no-op.
			Fields: []FieldSpec{
				{APIName: "Title", Label: "Title", Type: "string"},
			},
		},
	}
	if err := validateSpecLockRules(specs, newObjectSpecFieldResolver(specs)); err != nil {
		t.Fatalf("validateSpecLockRules(valid specs) = %v; want nil", err)
	}
}

// TestValidateSpecLockRulesAcceptsSessionGlobals pins that session global
// expressions ($Profile, $User, $UserRole) in LockRule are accepted at
// pack apply / verify time. The runtime lock gate supports these globals;
// rejecting them at authoring time blocks valid metadata from round-tripping.
func TestValidateSpecLockRulesAcceptsSessionGlobals(t *testing.T) {
	tests := []struct {
		name       string
		expression string
	}{
		{name: "profile name session global", expression: `$Profile.Name == "ReadOnly"`},
		{name: "user role session global", expression: `$UserRole.Name == "Manager"`},
		{name: "combined with record field", expression: `$Profile.Name == "ReadOnly" || Status == "Archived"`},
	}
	for _, tc := range tests {
		t.Run(tc.name, func(t *testing.T) {
			specs := []ObjectSpec{
				{
					APIName:      "Widget",
					Label:        "Widget",
					TableType:    "standard_object",
					Category:     "crm",
					SharingModel: "Private",
					LockRule:     tc.expression,
					Fields: []FieldSpec{
						{APIName: "Status", Label: "Status", Type: "string"},
					},
				},
			}
			if err := validateSpecLockRules(specs, newObjectSpecFieldResolver(specs)); err != nil {
				t.Fatalf("validateSpecLockRules(%q) = %v; want nil (session globals are runtime-valid)", tc.expression, err)
			}
		})
	}
}

// TestValidateSpecLockRulesRejectsNonBoolean pins that validateSpecLockRules
// rejects non-boolean expressions (bare field reference like `Status` evaluates
// to the field value, not a boolean) at pack apply / verify time.
// The runtime-probe approach catches this at authoring time rather than at
// runtime (where a non-bool result from the gate would cause fail-closed
// behaviour that denies all edits/deletes).
func TestValidateSpecLockRulesRejectsNonBoolean(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Widget",
			Label:        "Widget",
			TableType:    "standard_object",
			Category:     "crm",
			SharingModel: "Private",
			LockRule:     `Status`,
			Fields: []FieldSpec{
				{APIName: "Status", Label: "Status", Type: "string"},
			},
		},
	}
	if err := validateSpecLockRules(specs, newObjectSpecFieldResolver(specs)); err == nil {
		t.Fatal("validateSpecLockRules(non-boolean 'Status') = nil; want error (must reject non-boolean LockRule)")
	}
}

// TestValidateSpecLockRulesRejectsRelationshipTraversal pins that expressions
// using relationship traversal (__r paths) are rejected at authoring time.
// The lock gate's Evaluate call has no Fetcher, so cross-object lookups always
// fail at runtime. Rejecting at save time gives the admin a clear authoring-time
// error rather than a silent runtime fail-closed.
func TestValidateSpecLockRulesRejectsRelationshipTraversal(t *testing.T) {
	tests := []struct {
		name       string
		expression string
	}{
		{name: "relationship traversal", expression: `Parent__r.Status == "Active"`},
	}
	for _, tc := range tests {
		t.Run(tc.name, func(t *testing.T) {
			specs := []ObjectSpec{
				{
					APIName:      "Widget",
					Label:        "Widget",
					TableType:    "standard_object",
					Category:     "crm",
					SharingModel: "Private",
					LockRule:     tc.expression,
					Fields: []FieldSpec{
						{APIName: "Status", Label: "Status", Type: "string"},
					},
				},
			}
			if err := validateSpecLockRules(specs, newObjectSpecFieldResolver(specs)); err == nil {
				t.Fatalf("validateSpecLockRules(%q) = nil; want error (relationship traversal must be rejected)", tc.expression)
			}
		})
	}
}

// TestValidateSpecLockRulesAcceptsRecordTypeIDForCustomObjects pins that a
// custom-object lockRule referencing RecordTypeId is accepted at pack
// apply/verify time. RecordTypeId is injected by the runtime DDL path for
// custom objects; the lock-rule validator must see the same field surface to
// avoid a parity gap where valid metadata fails authoring-time validation.
func TestValidateSpecLockRulesAcceptsRecordTypeIDForCustomObjects(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Case__c",
			Label:        "Case",
			TableType:    string(constants.TableTypeCustomObject),
			Category:     "crm",
			SharingModel: "Private",
			LockRule:     `RecordTypeId != ""`,
		},
	}
	if err := validateSpecLockRules(specs, newObjectSpecFieldResolver(specs)); err != nil {
		t.Fatalf("validateSpecLockRules(custom object with RecordTypeId lockRule) = %v; want nil (RecordTypeId must be visible in custom-object lock-rule validator)", err)
	}
}

// TestValidateSpecLockRulesRejectsRecordTypeIDForStandardObjects pins that a
// standard-object lockRule referencing RecordTypeId is rejected: standard objects
// do not receive RecordTypeId injection, so the validator must not silently
// accept field references that have no runtime column.
func TestValidateSpecLockRulesRejectsRecordTypeIDForStandardObjects(t *testing.T) {
	specs := []ObjectSpec{
		{
			APIName:      "Account",
			Label:        "Account",
			TableType:    "standard_object",
			Category:     "crm",
			SharingModel: "Private",
			LockRule:     `RecordTypeId != ""`,
		},
	}
	if err := validateSpecLockRules(specs, newObjectSpecFieldResolver(specs)); err == nil {
		t.Fatalf("validateSpecLockRules(standard object with RecordTypeId lockRule) = nil; want error (RecordTypeId is not a standard-object system field)")
	}
}

// TestIsManagedSystemFieldSpecGenericNoObjectBranch proves that
// isManagedSystemFieldSpec classifies FieldSpec values by structural
// properties only (createable, updateable, required, formula storage)
// — no object-name or field-name hardcoding appears in the classifier.
func TestIsManagedSystemFieldSpecGenericNoObjectBranch(t *testing.T) {
	falseVal := false
	cases := []struct {
		name  string
		field FieldSpec
		want  bool
	}{
		{
			name: "readonly_nullable_date",
			field: FieldSpec{
				APIName:    "LastActivityDate",
				Type:       "date",
				Createable: &falseVal,
				Updateable: &falseVal,
				Required:   false,
			},
			want: true,
		},
		{
			name: "readonly_required_date",
			field: FieldSpec{
				APIName:    "SomeDate",
				Type:       "date",
				Createable: &falseVal,
				Updateable: &falseVal,
				Required:   true,
			},
			want: false,
		},
		{
			name: "createable_date",
			field: FieldSpec{
				APIName:    "CustomDate__c",
				Type:       "date",
				Createable: nil, // nil defaults to createable
				Updateable: nil,
				Required:   false,
			},
			want: false,
		},
		{
			// When the pack spec omits explicit capabilities for a
			// system-maintained date field, ComputeIntrinsic still clamps
			// them to false at persist time. isManagedSystemFieldSpec
			// must match the live row (Codex Review finding round 1).
			name: "intrinsic_readonly_date_nil_capabilities",
			field: FieldSpec{
				APIName:    "LastActivityDate",
				Type:       "date",
				Createable: nil, // spec omits — ComputeIntrinsic still makes it read-only
				Updateable: nil,
				Required:   false,
			},
			want: true,
		},
		{
			name: "formula_view_only",
			field: FieldSpec{
				APIName:     "FormulaField",
				Type:        "formula",
				ReturnType:  "date",
				Formula:     "TODAY()",
				FormulaMode: "active",
				Createable:  &falseVal,
				Updateable:  &falseVal,
				Required:    false,
			},
			want: false,
		},
		{
			name: "formula_write_materialized",
			field: FieldSpec{
				APIName:            "StoredFormula",
				Type:               "formula",
				ReturnType:         "date",
				Formula:            "TODAY()",
				FormulaMode:        "active",
				FormulaStorageMode: "write_materialized",
				Createable:         &falseVal,
				Updateable:         &falseVal,
				Required:           false,
			},
			want: true,
		},
		{
			name: "readonly_nullable_string",
			field: FieldSpec{
				APIName:    "SomeString",
				Type:       "string",
				Createable: &falseVal,
				Updateable: &falseVal,
				Required:   false,
			},
			want: true,
		},
	}

	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			got := isManagedSystemFieldSpec(tc.field)
			if got != tc.want {
				t.Fatalf("isManagedSystemFieldSpec(%+v) = %v, want %v", tc.field, got, tc.want)
			}
		})
	}
}

func TestParseObjectSpecsRejectsInvalidDeleteConstraint(t *testing.T) {
	_, err := ParseObjectSpecsPayloads([][]byte{[]byte(`[
		{"apiName":"Lead","fields":[
			{"apiName":"First_Campaign__c","type":"reference","referenceTo":["Campaign"],"deleteConstraint":"Detach"}
		]}
	]`)}, "invalid-delete-constraint fixture")
	if err == nil || !strings.Contains(err.Error(), `Lead.First_Campaign__c deleteConstraint "Detach" is invalid`) {
		t.Fatalf("ParseObjectSpecsPayloads error = %v", err)
	}
}

func TestBuildPackFieldSpecWritePersistsDeleteConstraint(t *testing.T) {
	installPackFieldProvenance(t)
	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))
	specs, err := ParseObjectSpecsPayloads([][]byte{[]byte(`[
		{"apiName":"Lead","fields":[
			{"apiName":"First_Campaign__c","label":"First Campaign","type":"reference","referenceTo":["Campaign"],"deleteConstraint":"SetNull"}
		]}
	]`)}, "delete-constraint fixture")
	if err != nil {
		t.Fatalf("ParseObjectSpecsPayloads: %v", err)
	}
	resolver := newObjectSpecFieldResolver(specs)

	fieldWithContext, err := buildPackFieldSpecWriteWithResolver(
		"app-pack",
		"Lead",
		domainschema.GenerateObjectID("Lead"),
		specs[0].Fields[0],
		sm,
		"",
		resolver,
		packFieldAuthoringCompat{},
	)
	if err != nil {
		t.Fatalf("buildPackFieldSpecWriteWithResolver: %v", err)
	}

	if got := fieldWithContext.Field.DeleteRule; got == nil || *got != constants.DeleteRuleSetNull {
		t.Fatalf("Field.DeleteRule = %v, want %q", got, constants.DeleteRuleSetNull)
	}
	if got := fieldWithContext.ColumnDefinition.OnDelete; got != "SET NULL" {
		t.Fatalf("ColumnDefinition.OnDelete = %q, want SET NULL", got)
	}
}

func TestBuildPackFieldSpecWritePersistsSiblingMaxParentField(t *testing.T) {
	installPackFieldProvenance(t)
	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))
	specs := []ObjectSpec{{
		APIName: "LineItem__c",
		Fields: []FieldSpec{
			{APIName: "ParentId", Label: "Parent", Type: "reference", ReferenceTo: []string{"Parent__c"}},
			{APIName: "SortOrder__c", Label: "Sort Order", Type: "integer", SiblingMaxParentField: "ParentId"},
		},
	}}
	resolver := newObjectSpecFieldResolver(specs)

	fieldWithContext, err := buildPackFieldSpecWriteWithResolver(
		"app-pack",
		"LineItem__c",
		domainschema.GenerateObjectID("LineItem__c"),
		specs[0].Fields[1],
		sm,
		"",
		resolver,
		packFieldAuthoringCompat{},
	)
	if err != nil {
		t.Fatalf("buildPackFieldSpecWriteWithResolver: %v", err)
	}

	if got := fieldWithContext.Field.SiblingMaxParentField; got == nil || *got != "ParentId" {
		t.Fatalf("Field.SiblingMaxParentField = %v, want ParentId", got)
	}
	if got := fieldWithContext.Field.Type; got != "integer" {
		t.Fatalf("Field.Type = %q, want integer", got)
	}
	if got := fieldWithContext.ColumnDefinition.SiblingMaxParentField; got != "ParentId" {
		t.Fatalf("ColumnDefinition.SiblingMaxParentField = %q, want ParentId", got)
	}
	if got := fieldWithContext.ColumnDefinition.Type; got != "INTEGER" {
		t.Fatalf("ColumnDefinition.Type = %q, want INTEGER", got)
	}
}

func TestBuildPackFieldSpecWriteDerivesRollupOutputMetadata(t *testing.T) {
	installPackFieldProvenance(t)
	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))
	specs := []ObjectSpec{
		{
			APIName: "Campaign__c",
			Fields: []FieldSpec{
				{APIName: "Amount__c", Label: "Amount", Type: "currency"},
				{APIName: "CampaignId", Label: "Campaign", Type: "reference", ReferenceTo: []string{"Campaign"}},
			},
		},
		{
			APIName: "Campaign",
			Fields: []FieldSpec{
				{APIName: "TotalAmount", Label: "Total Amount", Type: "rollupsummary", RollupConfig: &models.RollupConfig{
					CalcType:          "SUM",
					SummaryObject:     "Campaign__c",
					SummaryField:      "Amount__c",
					RelationshipField: "CampaignId",
				}},
				{APIName: "MemberCount", Label: "Member Count", Type: "rollupsummary", RollupConfig: &models.RollupConfig{
					CalcType:          "COUNT",
					SummaryObject:     "Campaign__c",
					RelationshipField: "CampaignId",
				}},
			},
		},
	}
	resolver := newObjectSpecFieldResolver(specs)

	for _, tc := range []struct {
		fieldAPIName string
		wantType     constants.SchemaFieldType
		wantScale    int
	}{
		{fieldAPIName: "TotalAmount", wantType: constants.FieldTypeCurrency, wantScale: 2},
		{fieldAPIName: "MemberCount", wantType: constants.FieldTypeInt, wantScale: 0},
	} {
		fieldSpec, ok := resolver.find("Campaign", tc.fieldAPIName)
		if !ok {
			t.Fatalf("%s spec not found", tc.fieldAPIName)
		}
		write, err := buildPackFieldSpecWriteWithResolver(
			"app-pack",
			"Campaign",
			domainschema.GenerateObjectID("Campaign"),
			fieldSpec,
			sm,
			"",
			resolver,
			packFieldAuthoringCompat{},
		)
		if err != nil {
			t.Fatalf("buildPackFieldSpecWriteWithResolver(%s): %v", tc.fieldAPIName, err)
		}
		if write.Field.ReturnType == nil || *write.Field.ReturnType != tc.wantType || write.Field.Precision == nil || *write.Field.Precision != 18 || write.Field.Scale == nil || *write.Field.Scale != tc.wantScale {
			t.Fatalf("%s output metadata = %+v, want %s(18,%d)", tc.fieldAPIName, write.Field, tc.wantType, tc.wantScale)
		}
		if write.Field.RollupConfig == nil || write.Field.RollupConfig.ResultType == nil || *write.Field.RollupConfig.ResultType != tc.wantType || write.Field.RollupConfig.ResultPrecision == nil || *write.Field.RollupConfig.ResultPrecision != 18 || write.Field.RollupConfig.ResultScale == nil || *write.Field.RollupConfig.ResultScale != tc.wantScale {
			t.Fatalf("%s result config = %+v, want %s(18,%d)", tc.fieldAPIName, write.Field.RollupConfig, tc.wantType, tc.wantScale)
		}
		if write.ColumnDefinition.RollupConfig == nil || write.ColumnDefinition.ReturnType != string(tc.wantType) {
			t.Fatalf("%s column output metadata = %+v, want rollup config and return type %s", tc.fieldAPIName, write.ColumnDefinition, tc.wantType)
		}
	}
}

func TestBuildPackFieldSpecWriteRejectsInvalidSiblingMaxParentField(t *testing.T) {
	installPackFieldProvenance(t)
	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))
	cases := []struct {
		name   string
		parent FieldSpec
		want   string
	}{
		{
			name:   "non_reference_parent",
			parent: FieldSpec{APIName: "NotAParent__c", Label: "Not Parent", Type: "string"},
			want:   "non-polymorphic reference",
		},
		{
			name:   "multi_target_parent",
			parent: FieldSpec{APIName: "Parent__c", Label: "Parent", Type: "masterdetail", ReferenceTo: []string{"ParentA__c", "ParentB__c"}},
			want:   "non-polymorphic reference",
		},
		{
			name:   "blank_target_parent",
			parent: FieldSpec{APIName: "Parent__c", Label: "Parent", Type: "reference", ReferenceTo: []string{" "}},
			want:   "non-polymorphic reference",
		},
		{
			name:   "formula_reference_parent",
			parent: FieldSpec{APIName: "Parent__c", Label: "Parent", Type: "reference", ReferenceTo: []string{"Parent__c"}, Formula: "OwnerId"},
			want:   "persisted non-polymorphic reference",
		},
		{
			name:   "projection_reference_parent",
			parent: FieldSpec{APIName: "Parent__c", Label: "Parent", Type: "reference", ReferenceTo: []string{"Parent__c"}, ProjectionConfig: &models.FieldProjectionConfig{Kind: "lookup"}},
			want:   "persisted non-polymorphic reference",
		},
	}
	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			specs := []ObjectSpec{{
				APIName: "LineItem__c",
				Fields: []FieldSpec{
					tc.parent,
					{APIName: "SortOrder__c", Label: "Sort Order", Type: "integer", SiblingMaxParentField: tc.parent.APIName},
				},
			}}
			resolver := newObjectSpecFieldResolver(specs)

			_, err := buildPackFieldSpecWriteWithResolver(
				"app-pack",
				"LineItem__c",
				domainschema.GenerateObjectID("LineItem__c"),
				specs[0].Fields[1],
				sm,
				"",
				resolver,
				packFieldAuthoringCompat{},
			)
			if err == nil {
				t.Fatal("buildPackFieldSpecWriteWithResolver error = nil, want rejection")
			}
			if !strings.Contains(err.Error(), tc.want) {
				t.Fatalf("error = %v, want %q", err, tc.want)
			}
		})
	}
}

func TestBuildPackFieldSpecWriteRejectsDecimalSiblingMaxTarget(t *testing.T) {
	installPackFieldProvenance(t)
	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))
	cases := []string{"double", "number", "currency", "percent"}
	for _, fieldType := range cases {
		t.Run(fieldType, func(t *testing.T) {
			specs := []ObjectSpec{{
				APIName: "LineItem__c",
				Fields: []FieldSpec{
					{APIName: "ParentId", Label: "Parent", Type: "reference", ReferenceTo: []string{"Parent__c"}},
					{APIName: "SortOrder__c", Label: "Sort Order", Type: fieldType, SiblingMaxParentField: "ParentId"},
				},
			}}
			resolver := newObjectSpecFieldResolver(specs)

			_, err := buildPackFieldSpecWriteWithResolver(
				"app-pack",
				"LineItem__c",
				domainschema.GenerateObjectID("LineItem__c"),
				specs[0].Fields[1],
				sm,
				"",
				resolver,
				packFieldAuthoringCompat{},
			)
			if err == nil {
				t.Fatal("buildPackFieldSpecWriteWithResolver error = nil, want decimal target rejection")
			}
			if !strings.Contains(err.Error(), "integer-compatible numeric target") {
				t.Fatalf("error = %v, want integer-compatible numeric target diagnostic", err)
			}
		})
	}
}

func TestBuildPackFieldSpecWriteRejectsNonWriteableSiblingMaxTarget(t *testing.T) {
	installPackFieldProvenance(t)
	sm := metadata.NewSchemaManager(persistence.NewSchemaRepository(nil, nil))
	createableFalse := false
	updateableFalse := false
	createableTrue := true
	updateableTrue := true
	cases := []struct {
		name   string
		target FieldSpec
		want   string
	}{
		{
			name:   "not_createable",
			target: FieldSpec{APIName: "SortOrder__c", Label: "Sort Order", Type: "integer", Createable: &createableFalse, Updateable: &updateableTrue, SiblingMaxParentField: "ParentId"},
			want:   "createable and updateable",
		},
		{
			name:   "not_updateable",
			target: FieldSpec{APIName: "SortOrder__c", Label: "Sort Order", Type: "integer", Createable: &createableTrue, Updateable: &updateableFalse, SiblingMaxParentField: "ParentId"},
			want:   "createable and updateable",
		},
		{
			name:   "intrinsic_write_protection",
			target: FieldSpec{APIName: "SortOrder__c", Label: "Sort Order", Type: "integer", Createable: &createableTrue, Updateable: &updateableTrue, WriteProtectionPolicy: constants.FieldWriteProtectionTrustedOnly, SiblingMaxParentField: "ParentId"},
			want:   "createable and updateable",
		},
		{
			name: "rollup_config",
			target: FieldSpec{
				APIName:               "SortOrder__c",
				Label:                 "Sort Order",
				Type:                  "integer",
				Createable:            &createableTrue,
				Updateable:            &updateableTrue,
				SiblingMaxParentField: "ParentId",
				RollupConfig: &models.RollupConfig{
					SummaryObject:     "LineItem__c",
					SummaryField:      "SortOrder__c",
					RelationshipField: "ParentId",
					CalcType:          "MAX",
				},
			},
			want: "writable numeric target field",
		},
	}
	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			specs := []ObjectSpec{{
				APIName: "LineItem__c",
				Fields: []FieldSpec{
					{APIName: "ParentId", Label: "Parent", Type: "reference", ReferenceTo: []string{"Parent__c"}},
					tc.target,
				},
			}}
			resolver := newObjectSpecFieldResolver(specs)

			_, err := buildPackFieldSpecWriteWithResolver(
				"app-pack",
				"LineItem__c",
				domainschema.GenerateObjectID("LineItem__c"),
				specs[0].Fields[1],
				sm,
				"",
				resolver,
				packFieldAuthoringCompat{},
			)
			if err == nil {
				t.Fatal("buildPackFieldSpecWriteWithResolver error = nil, want non-writeable target rejection")
			}
			if !strings.Contains(err.Error(), tc.want) {
				t.Fatalf("error = %v, want %q", err, tc.want)
			}
		})
	}
}