package records

// persistence_upsert_test.go
//
// Regression coverage for issue #394 — PATCH/upsert record-existence leak.
// Tests that UpsertByExternalID returns 404 (not 403) when the caller lacks
// access to an existing record, making inaccessible and non-existent records
// indistinguishable per Salesforce v63 upsert behavior.

import (
	"context"
	"errors"
	"fmt"
	"strings"
	"testing"

	eventFeature "github.com/nexuscrm/events"
	appErrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/kernel/pkg/formula"
	"github.com/nexuscrm/kernel/pkg/ports"
	"github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/kernel/pkg/validation"
	"github.com/nexuscrm/metadata"
	"github.com/nexuscrm/permissions"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/contract"
	sharedEvents "github.com/nexuscrm/shared/pkg/events"
	"github.com/nexuscrm/shared/pkg/models"
)

type uniqueRaceRetryTxManager struct {
	inlineTxManager
	attempts int
}

func (m *uniqueRaceRetryTxManager) WithPessimisticRetry(
	ctx context.Context,
	fn func(context.Context) error,
	maxRetries int,
) error {
	for m.attempts < maxRetries {
		m.attempts++
		err := m.WithPessimisticTransaction(ctx, fn)
		if !ports.IsUniqueFieldUpsertRace(err) {
			return err
		}
	}
	return fmt.Errorf("unique-field retry budget exhausted: %w", ports.ErrUniqueFieldUpsertRace)
}

// ---------------------------------------------------------------------------
// Stub RecordRepository for UpsertByExternalID tests.
// Only the methods exercised by the tests are wired; the embedded interface
// traps any other call with a nil panic, so unexpected callers fail loudly.
// ---------------------------------------------------------------------------

type stubUpsertRecordRepo struct {
	ports.RecordRepository

	findOneByFieldFn func(ctx context.Context, t string, f string, v interface{}) (models.SObject, error)
	updateFn         func(ctx context.Context, t string, id string, u models.SObject) error
	insertFn         func(ctx context.Context, t string, rec models.SObject) error
	bulkInsertFn     func(ctx context.Context, t string, recs []models.SObject, cols []string, opts interface{}) error
	getLockFn        func(ctx context.Context, t string, id string) (models.SObject, error)
	findOneFn        func(ctx context.Context, t string, id string) (models.SObject, error)
	checkUniqueFn    func(ctx context.Context, schema *models.ObjectMetadata, f string, v interface{}, excl string) (bool, error)
}

func (r *stubUpsertRecordRepo) FindOneByField(ctx context.Context, t string, f string, v interface{}) (models.SObject, error) {
	if r.findOneByFieldFn != nil {
		return r.findOneByFieldFn(ctx, t, f, v)
	}
	return nil, nil
}
func (r *stubUpsertRecordRepo) Update(ctx context.Context, t string, id string, u models.SObject) error {
	if r.updateFn != nil {
		return r.updateFn(ctx, t, id, u)
	}
	return nil
}
func (r *stubUpsertRecordRepo) Insert(ctx context.Context, t string, rec models.SObject) error {
	if r.insertFn != nil {
		return r.insertFn(ctx, t, rec)
	}
	return nil
}

func (r *stubUpsertRecordRepo) GetLock(ctx context.Context, t string, id string) (models.SObject, error) {
	if r.getLockFn != nil {
		return r.getLockFn(ctx, t, id)
	}
	return nil, nil
}
func (r *stubUpsertRecordRepo) BulkInsert(ctx context.Context, t string, recs []models.SObject, cols []string, opts interface{}) error {
	if r.bulkInsertFn != nil {
		return r.bulkInsertFn(ctx, t, recs, cols, opts)
	}
	return nil
}
func (r *stubUpsertRecordRepo) FindOne(ctx context.Context, t string, id string) (models.SObject, error) {
	if r.findOneFn != nil {
		return r.findOneFn(ctx, t, id)
	}
	return nil, nil
}
func (r *stubUpsertRecordRepo) FindAny(ctx context.Context, t string, id string) (models.SObject, error) {
	return nil, nil
}
func (r *stubUpsertRecordRepo) Exists(ctx context.Context, t string, id string) (bool, error) {
	return false, nil
}
func (r *stubUpsertRecordRepo) CheckUniqueness(ctx context.Context, schema *models.ObjectMetadata, f string, v interface{}, excl string) (bool, error) {
	if r.checkUniqueFn != nil {
		return r.checkUniqueFn(ctx, schema, f, v, excl)
	}
	return false, nil
}

// ---------------------------------------------------------------------------
// Stub PermissionRepository for UpsertByExternalID tests.
// grantEdit controls whether the user gets Edit permission on the object.
// The embedded interface traps any unwired call with a nil panic.
// ---------------------------------------------------------------------------

type stubUpsertPermRepo struct {
	ports.PermissionRepository

	grantEdit            bool
	grantCreate          bool
	grantDelete          bool
	effectiveRecordTypes map[string]models.PermissionSetRecordTypeVisibility
	systemPermsByProfile map[string]map[string]bool
	roles                []*models.UserRole

	ensureProfilePermissionSetCalls int
	ensureProfilePermissionSetIDs   []string
	ensureProfilePermissionSetNames []string
	cleanupProfilePermissionSetIDs  []string
	ensureNavigationProfileIDs      []string
	insertMissingObjectPermsCalls   int
	insertMissingFieldPermsCalls    int

	ensureProfilePermissionSetErr  error
	cleanupProfilePermissionSetErr error
	ensureNavigationErr            error
	insertMissingObjectPermsErr    error
	insertMissingFieldPermsErr     error
}

func (r *stubUpsertPermRepo) GetAllRoles(ctx context.Context) ([]*models.UserRole, error) {
	return r.roles, nil
}
func (r *stubUpsertPermRepo) LoadEffectiveObjectPermission(ctx context.Context, user *models.UserSession, objectAPIName string) (*models.ObjectPermissions, error) {
	return &models.ObjectPermissions{
		PermissionsRead:   true,
		PermissionsEdit:   r.grantEdit,
		PermissionsCreate: r.grantCreate,
		PermissionsDelete: r.grantDelete,
	}, nil
}
func (r *stubUpsertPermRepo) GetEffectiveSystemPermissions(ctx context.Context, userID, profileID string) (map[string]bool, error) {
	if r.systemPermsByProfile != nil {
		if perms, ok := r.systemPermsByProfile[profileID]; ok {
			out := make(map[string]bool, len(perms))
			for k, v := range perms {
				out[k] = v
			}
			return out, nil
		}
	}
	return map[string]bool{}, nil
}
func (r *stubUpsertPermRepo) IsUserInGroup(ctx context.Context, groupID, userID string) (bool, error) {
	return false, nil
}
func (r *stubUpsertPermRepo) GetManualShareAccessLevels(ctx context.Context, objectAPIName, recordID, userID string) ([]string, error) {
	return []string{}, nil
}
func (r *stubUpsertPermRepo) GetTeamMemberAccessLevel(ctx context.Context, objectAPIName, recordID, userID string) (*string, error) {
	return nil, nil
}
func (r *stubUpsertPermRepo) GetEffectiveCustomPermissions(ctx context.Context, userID, profileID string) (map[string]bool, error) {
	return map[string]bool{}, nil
}
func (r *stubUpsertPermRepo) GetPendingApprovalLockInfo(ctx context.Context, targetObjectID string) (string, string, error) {
	return "", "", nil
}
func (r *stubUpsertPermRepo) HasPendingApprovalWorkitemForUser(ctx context.Context, processInstanceID, userID string) (bool, error) {
	return false, nil
}
func (r *stubUpsertPermRepo) GetRecordByID(ctx context.Context, tableName, recordID string) (models.SObject, error) {
	return nil, nil
}
func (r *stubUpsertPermRepo) LoadEffectiveFieldPermission(ctx context.Context, user *models.UserSession, objectAPIName, fieldAPIName string) (*models.FieldPermissions, error) {
	return nil, nil
}
func (r *stubUpsertPermRepo) InsertMissingObjectPermissions(ctx context.Context, perms []models.ObjectPermissions) error {
	r.insertMissingObjectPermsCalls++
	return r.insertMissingObjectPermsErr
}
func (r *stubUpsertPermRepo) InsertMissingFieldPermissions(ctx context.Context, perms []models.FieldPermissions) error {
	r.insertMissingFieldPermsCalls++
	return r.insertMissingFieldPermsErr
}
func (r *stubUpsertPermRepo) EnsureProfilePermissionSet(ctx context.Context, profileID, profileName string) (string, error) {
	r.ensureProfilePermissionSetCalls++
	r.ensureProfilePermissionSetIDs = append(r.ensureProfilePermissionSetIDs, profileID)
	r.ensureProfilePermissionSetNames = append(r.ensureProfilePermissionSetNames, profileName)
	if r.ensureProfilePermissionSetErr != nil {
		return "", r.ensureProfilePermissionSetErr
	}
	return "ps-" + profileID, nil
}
func (r *stubUpsertPermRepo) CleanupProfilePermissionSet(ctx context.Context, profileID string) error {
	r.cleanupProfilePermissionSetIDs = append(r.cleanupProfilePermissionSetIDs, profileID)
	return r.cleanupProfilePermissionSetErr
}
func (r *stubUpsertPermRepo) GetEffectiveRecordTypeVisibilities(ctx context.Context, userID, profileID string) (map[string]models.PermissionSetRecordTypeVisibility, error) {
	return r.effectiveRecordTypes, nil
}
func (r *stubUpsertPermRepo) EnsureNavigationVisibilityForProfile(ctx context.Context, profileID string) error {
	r.ensureNavigationProfileIDs = append(r.ensureNavigationProfileIDs, profileID)
	return r.ensureNavigationErr
}

// ---------------------------------------------------------------------------
// Stub MetadataRepository — the wired methods below serve the MetadataService
// cache init plus the rules/record-types/sharing reads these tests exercise;
// the embedded interface traps any other call with a nil panic.
// ---------------------------------------------------------------------------

type stubUpsertMetaRepo struct {
	ports.MetadataRepository

	schema           *models.ObjectMetadata
	schemas          []*models.ObjectMetadata // multi-schema support (overrides schema when set)
	recordTypes      []*models.RecordType
	validationRules  []*models.ValidationRule
	sharingRules     []*models.SharingRule
	sharingRuleCalls int
	// relationships, when non-nil, is returned by GetAllRelationships. Used by
	// parent-controlled write-guard tests to inject IsSharingParent rows so
	// MetadataService.GetParentControlledObject resolves correctly.
	relationships []*models.Relationship
}

func (r *stubUpsertMetaRepo) GetAllSchemas(ctx context.Context) ([]*models.ObjectMetadata, error) {
	if len(r.schemas) > 0 {
		return r.schemas, nil
	}
	if r.schema == nil {
		return nil, nil
	}
	return []*models.ObjectMetadata{r.schema}, nil
}
func (r *stubUpsertMetaRepo) GetAllRelationships(ctx context.Context) ([]*models.Relationship, error) {
	if r.relationships != nil {
		return r.relationships, nil
	}
	return []*models.Relationship{}, nil
}
func (r *stubUpsertMetaRepo) GetSchemaByAPIName(ctx context.Context, apiName string) (*models.ObjectMetadata, error) {
	if len(r.schemas) > 0 {
		for _, s := range r.schemas {
			if s.APIName == apiName {
				return s, nil
			}
		}
	}
	return r.schema, nil
}
func (r *stubUpsertMetaRepo) GetRecordTypes(ctx context.Context, objectAPIName string) ([]*models.RecordType, error) {
	return r.recordTypes, nil
}
func (r *stubUpsertMetaRepo) GetSharingRules(ctx context.Context, objectAPIName string) ([]*models.SharingRule, error) {
	r.sharingRuleCalls++
	return r.sharingRules, nil
}
func (r *stubUpsertMetaRepo) GetAllFlowDefinitions(ctx context.Context) ([]*models.FlowDefinition, error) {
	return nil, nil
}
func (r *stubUpsertMetaRepo) GetAllValidationRules(ctx context.Context) ([]*models.ValidationRule, error) {
	return r.validationRules, nil
}
func (r *stubUpsertMetaRepo) GetValidationRules(ctx context.Context, objectAPIName string) ([]*models.ValidationRule, error) {
	if len(r.validationRules) == 0 {
		return nil, nil
	}
	rules := make([]*models.ValidationRule, 0, len(r.validationRules))
	for _, rule := range r.validationRules {
		if rule.ObjectAPIName == "" || strings.EqualFold(rule.ObjectAPIName, objectAPIName) {
			rules = append(rules, rule)
		}
	}
	return rules, nil
}

// ---------------------------------------------------------------------------
// Helper: build a PersistenceService wired for UpsertByExternalID testing.
// ---------------------------------------------------------------------------

func newUpsertTestPersistenceService(
	t *testing.T,
	recRepo ports.RecordRepository,
	permRepo ports.PermissionRepository,
	metaRepo ports.MetadataRepository,
	testSchema *models.ObjectMetadata,
) *PersistenceService {
	t.Helper()

	metaSvc := metadata.NewMetadataService(metaRepo, nil, nil)
	permSvc := permissions.NewPermissionService(permRepo, nil, nil)

	return &PersistenceService{
		repo:        recRepo,
		metadata:    metaSvc,
		permissions: permSvc,
	}
}

// ---------------------------------------------------------------------------
// Tests
// ---------------------------------------------------------------------------

func TestValidateRecordTypeIdRejectsUnavailableEffectiveRecordType(t *testing.T) {
	const objectName = "Account"
	testSchema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString},
			{APIName: "RecordTypeId", Type: constants.FieldTypeReference},
		},
	}
	permRepo := &stubUpsertPermRepo{
		effectiveRecordTypes: map[string]models.PermissionSetRecordTypeVisibility{
			"rt-hidden": {RecordTypeID: "rt-hidden", IsVisible: false},
		},
	}
	ps := newUpsertTestPersistenceService(
		t,
		&stubUpsertRecordRepo{},
		permRepo,
		&stubUpsertMetaRepo{
			schema: testSchema,
			recordTypes: []*models.RecordType{
				{ID: "rt-hidden", ObjectAPIName: objectName, Name: "Hidden", IsActive: true},
			},
		},
		testSchema,
	)

	err := ps.validateRecordTypeId(context.Background(), objectName, models.SObject{"RecordTypeId": "rt-hidden"}, &models.UserSession{
		ID:        "user-standard",
		ProfileID: constants.ProfileStandardUser,
	})
	if err == nil || !appErrors.IsValidation(err) {
		t.Fatalf("validateRecordTypeId() error = %v, want validation error", err)
	}
}

func TestValidateRecordTypeIdRejectsUnmentionedRecordTypeWhenObjectIsManaged(t *testing.T) {
	const objectName = "Account"
	testSchema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString},
			{APIName: "RecordTypeId", Type: constants.FieldTypeReference},
		},
	}
	permRepo := &stubUpsertPermRepo{
		effectiveRecordTypes: map[string]models.PermissionSetRecordTypeVisibility{
			"rt-hidden":      {RecordTypeID: "rt-hidden", IsVisible: false},
			"rt-unmentioned": {RecordTypeID: "rt-unmentioned", IsVisible: false},
		},
	}
	ps := newUpsertTestPersistenceService(
		t,
		&stubUpsertRecordRepo{},
		permRepo,
		&stubUpsertMetaRepo{
			schema: testSchema,
			recordTypes: []*models.RecordType{
				{ID: "rt-hidden", ObjectAPIName: objectName, Name: "Hidden", IsActive: true},
				{ID: "rt-unmentioned", ObjectAPIName: objectName, Name: "Unmentioned", IsActive: true},
			},
		},
		testSchema,
	)

	err := ps.validateRecordTypeId(context.Background(), objectName, models.SObject{"RecordTypeId": "rt-unmentioned"}, &models.UserSession{
		ID:        "user-standard",
		ProfileID: constants.ProfileStandardUser,
	})
	if err == nil || !appErrors.IsValidation(err) {
		t.Fatalf("validateRecordTypeId() error = %v, want validation error for unmentioned managed record type", err)
	}
}

func TestValidateRecordTypeIdRejectsMasterWhenConcreteRecordTypesExist(t *testing.T) {
	const objectName = "Account"
	testSchema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString},
			{APIName: "RecordTypeId", Type: constants.FieldTypeReference},
		},
	}
	ps := newUpsertTestPersistenceService(
		t,
		&stubUpsertRecordRepo{},
		&stubUpsertPermRepo{},
		&stubUpsertMetaRepo{
			schema: testSchema,
			recordTypes: []*models.RecordType{
				{ID: "rt-visible", ObjectAPIName: objectName, Name: "Visible", IsActive: true},
			},
		},
		testSchema,
	)

	err := ps.validateRecordTypeId(context.Background(), objectName, models.SObject{"RecordTypeId": "012000000000000AAA"}, &models.UserSession{
		ID:        "user-standard",
		ProfileID: constants.ProfileStandardUser,
	})
	if err == nil || !appErrors.IsValidation(err) {
		t.Fatalf("validateRecordTypeId() error = %v, want validation error for Master on managed object", err)
	}
}

func TestValidateRecordTypeIdAllowsUnmentionedRecordTypeWhenOnlyAdditiveGrantExists(t *testing.T) {
	const objectName = "Account"
	testSchema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString},
			{APIName: "RecordTypeId", Type: constants.FieldTypeReference},
		},
	}
	permRepo := &stubUpsertPermRepo{
		effectiveRecordTypes: map[string]models.PermissionSetRecordTypeVisibility{
			"rt-addon": {RecordTypeID: "rt-addon", IsVisible: true},
		},
	}
	ps := newUpsertTestPersistenceService(
		t,
		&stubUpsertRecordRepo{},
		permRepo,
		&stubUpsertMetaRepo{
			schema: testSchema,
			recordTypes: []*models.RecordType{
				{ID: "rt-addon", ObjectAPIName: objectName, Name: "Additive", IsActive: true},
				{ID: "rt-unmentioned", ObjectAPIName: objectName, Name: "Unmentioned", IsActive: true},
			},
		},
		testSchema,
	)

	err := ps.validateRecordTypeId(context.Background(), objectName, models.SObject{"RecordTypeId": "rt-unmentioned"}, &models.UserSession{
		ID:        "user-standard",
		ProfileID: constants.ProfileStandardUser,
	})
	if err != nil {
		t.Fatalf("validateRecordTypeId() error = %v, want nil for additive-only unmentioned record type", err)
	}
}

// TestUpsertByExternalID_InaccessibleRecordReturns404 verifies that when
// UpsertByExternalID finds an existing record but the caller has no Edit
// permission on it, the method returns a *appErrors.NotFoundError (HTTP 404)
// rather than a PermissionError (HTTP 403). This is the core #394 fix:
// inaccessible records must be indistinguishable from non-existent ones per
// Salesforce v63 upsert behavior.
func TestUpsertByExternalID_InaccessibleRecordReturns404(t *testing.T) {
	const (
		objectName = "Account"
		recID      = "rec-existing-123"
		extField   = "ExternalId__c"
		extValue   = "EXT-001"
		userID     = "user-noedit"
	)

	testSchema := &models.ObjectMetadata{
		APIName:      objectName,
		SharingModel: constants.SharingModelPrivate,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString},
			{APIName: extField, Type: constants.FieldTypeString},
		},
	}

	repo := &stubUpsertRecordRepo{
		findOneByFieldFn: func(_ context.Context, _ string, _ string, _ interface{}) (models.SObject, error) {
			return models.SObject{constants.FieldID: recID, extField: extValue}, nil
		},
	}
	permRepo := &stubUpsertPermRepo{grantEdit: false} // no Edit → access denied
	metaRepo := &stubUpsertMetaRepo{schema: testSchema}

	ps := newUpsertTestPersistenceService(t, repo, permRepo, metaRepo, testSchema)

	user := &models.UserSession{
		ID:        userID,
		ProfileID: constants.ProfileStandardUser,
	}

	_, _, err := ps.UpsertByExternalID(context.Background(), objectName, extField, extValue, map[string]interface{}{}, user)
	if err == nil {
		t.Fatal("expected error for inaccessible record, got nil")
	}

	var notFound *appErrors.NotFoundError
	if !errors.As(err, &notFound) {
		t.Fatalf("expected *appErrors.NotFoundError (404), got %T: %v", err, err)
	}
}

func TestUpsertByExternalIDUniqueConflictRollsBackThenUpdatesWinner(t *testing.T) {
	const (
		objectName = "Widget__c"
		extField   = "ExternalId__c"
		extValue   = "provider:object:001"
		winnerID   = "winner-001"
	)

	testSchema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldName, Type: constants.FieldTypeString, Createable: true, Updateable: true},
			{APIName: extField, Type: constants.FieldTypeString, IsUnique: true, Createable: true, Updateable: true},
		},
	}

	lookupCalls := 0
	insertCalls := 0
	updateCalls := 0
	retryTx := &uniqueRaceRetryTxManager{}
	repo := &stubUpsertRecordRepo{
		findOneByFieldFn: func(lookupCtx context.Context, gotObject, gotField string, gotValue interface{}) (models.SObject, error) {
			lookupCalls++
			if gotObject != objectName || gotField != extField || gotValue != extValue {
				t.Fatalf("FindOneByField(%q, %q, %#v), want (%q, %q, %q)", gotObject, gotField, gotValue, objectName, extField, extValue)
			}
			if lookupCalls == 1 {
				return nil, nil
			}
			if lookupCalls == 2 && retryTx.IsTransactionActive(lookupCtx) {
				t.Fatal("duplicate-key live-winner proof reused the losing transaction snapshot")
			}
			return models.SObject{constants.FieldID: winnerID, extField: extValue, constants.FieldName: "winner"}, nil
		},
		findOneFn: func(_ context.Context, gotObject, gotID string) (models.SObject, error) {
			if gotObject != objectName || gotID != winnerID {
				t.Fatalf("FindOne(%q, %q), want (%q, %q)", gotObject, gotID, objectName, winnerID)
			}
			return models.SObject{constants.FieldID: winnerID, extField: extValue, constants.FieldName: "winner"}, nil
		},
		insertFn: func(_ context.Context, gotObject string, record models.SObject) error {
			if gotObject != objectName {
				return nil
			}
			insertCalls++
			if record[extField] != extValue {
				t.Fatalf("Insert(%q, %#v) did not carry the exact external identity", gotObject, record)
			}
			return fmt.Errorf(
				"%w: %w",
				ports.ErrDatabaseDuplicateKey,
				appErrors.NewConflictError(objectName, schema.GenerateUniqueIndexName(objectName, extField), ""),
			)
		},
		getLockFn: func(_ context.Context, gotObject, gotID string) (models.SObject, error) {
			if gotObject != objectName || gotID != winnerID {
				t.Fatalf("GetLock(%q, %q), want (%q, %q)", gotObject, gotID, objectName, winnerID)
			}
			return models.SObject{constants.FieldID: winnerID, extField: extValue, constants.FieldName: "winner"}, nil
		},
		updateFn: func(_ context.Context, gotObject, gotID string, updates models.SObject) error {
			updateCalls++
			if gotObject != objectName || gotID != winnerID {
				t.Fatalf("Update(%q, %q), want (%q, %q)", gotObject, gotID, objectName, winnerID)
			}
			if updates[constants.FieldName] != "incoming" {
				t.Fatalf("winner updates = %#v, want incoming business fields", updates)
			}
			return nil
		},
	}

	ps := newUpsertTestPersistenceService(
		t,
		repo,
		&stubUpsertPermRepo{grantCreate: true, grantEdit: true},
		&stubUpsertMetaRepo{schema: testSchema},
		testSchema,
	)
	ps.txManager = retryTx
	ps.eventBus = &eventFeature.NoOpEventBus{}
	ps.validator = metadata.NewValidationService(formula.NewEngine())
	ps.schemaValidator = validation.NewSchemaValidator()
	ps.rollup = noopRollupProcessor{}

	incoming := models.SObject{constants.FieldName: "incoming"}
	got, created, err := ps.UpsertByExternalID(
		constants.WithSecurityContext(context.Background(), constants.SystemContext, "unique-field upsert race test"),
		objectName,
		extField,
		extValue,
		incoming,
		recordEntrypointUser(),
	)
	if err != nil {
		t.Fatalf("UpsertByExternalID() error = %v", err)
	}
	if created {
		t.Fatal("created = true, want false after adopting the concurrent winner")
	}
	if extractRecordID(got) != winnerID {
		t.Fatalf("result Id = %q, want %q", extractRecordID(got), winnerID)
	}
	if retryTx.attempts != 2 || lookupCalls != 4 || insertCalls != 1 || updateCalls != 1 {
		t.Fatalf("attempts/lookups/inserts/updates = %d/%d/%d/%d, want 2/4/1/1", retryTx.attempts, lookupCalls, insertCalls, updateCalls)
	}
	if len(incoming) != 1 || incoming[constants.FieldName] != "incoming" {
		t.Fatalf("caller input mutated across retry: %#v", incoming)
	}
}

func TestExternalIDUpsertLiveWinnerReadDetachesAmbientPlainTransaction(t *testing.T) {
	const (
		objectName = "Widget__c"
		extField   = "ExternalId__c"
		extValue   = "provider:object:ambient-plain-tx"
		winnerID   = "winner-ambient-plain-tx"
	)

	txManager := inlineTxManager{}
	lookupCalls := 0
	repo := &stubUpsertRecordRepo{
		findOneByFieldFn: func(lookupCtx context.Context, gotObject, gotField string, gotValue interface{}) (models.SObject, error) {
			lookupCalls++
			if txManager.IsTransactionActive(lookupCtx) {
				t.Fatal("live-winner verification reused the ambient REPEATABLE READ transaction snapshot")
			}
			if gotObject != objectName || gotField != extField || gotValue != extValue {
				t.Fatalf("FindOneByField(%q, %q, %#v), want (%q, %q, %q)", gotObject, gotField, gotValue, objectName, extField, extValue)
			}
			return models.SObject{constants.FieldID: winnerID, extField: extValue}, nil
		},
	}
	ps := &PersistenceService{repo: repo, txManager: txManager}

	err := txManager.WithTransaction(context.Background(), func(txCtx context.Context) error {
		if !txManager.IsTransactionActive(txCtx) || txManager.IsPessimisticTx(txCtx) {
			t.Fatal("test precondition requires one active plain transaction")
		}
		winnerExists, winnerErr := ps.externalIDUpsertLiveWinnerExists(txCtx, objectName, extField, extValue)
		if winnerErr != nil {
			return winnerErr
		}
		if !winnerExists {
			t.Fatal("detached current read did not prove the committed live winner")
		}
		return nil
	})
	if err != nil {
		t.Fatalf("ambient plain transaction winner verification: %v", err)
	}
	if lookupCalls != 1 {
		t.Fatalf("live-winner lookups = %d, want 1", lookupCalls)
	}
}

func TestUpsertByUniqueExternalIDPrecheckRaceRollsBackThenAdoptsLiveWinner(t *testing.T) {
	const (
		objectName = "Widget__c"
		extField   = "ExternalId__c"
		extValue   = "provider:object:precheck-race"
		winnerID   = "winner-precheck"
	)
	testSchema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldName, Type: constants.FieldTypeString, Createable: true, Updateable: true},
			{APIName: extField, Type: constants.FieldTypeString, IsUnique: true, Createable: true, Updateable: true},
		},
	}
	winner := models.SObject{constants.FieldID: winnerID, extField: extValue, constants.FieldName: "winner"}
	lookupCalls := 0
	precheckCalls := 0
	repositoryInserts := 0
	repositoryUpdates := 0
	repo := &stubUpsertRecordRepo{
		findOneByFieldFn: func(_ context.Context, gotObject, gotField string, gotValue interface{}) (models.SObject, error) {
			lookupCalls++
			if gotObject != objectName || gotField != extField || gotValue != extValue {
				t.Fatalf("FindOneByField(%q, %q, %#v), want requested identity", gotObject, gotField, gotValue)
			}
			if lookupCalls == 1 {
				return nil, nil
			}
			return cloneSObject(winner), nil
		},
		findOneFn: func(context.Context, string, string) (models.SObject, error) {
			return cloneSObject(winner), nil
		},
		getLockFn: func(context.Context, string, string) (models.SObject, error) {
			return cloneSObject(winner), nil
		},
		checkUniqueFn: func(_ context.Context, _ *models.ObjectMetadata, field string, value interface{}, excludeID string) (bool, error) {
			if field != extField || value != extValue {
				t.Fatalf("CheckUniqueness(%q, %#v), want requested external identity", field, value)
			}
			if excludeID == "" {
				precheckCalls++
				return true, nil
			}
			return false, nil
		},
		insertFn: func(_ context.Context, gotObject string, _ models.SObject) error {
			if gotObject == objectName {
				repositoryInserts++
			}
			return nil
		},
		updateFn: func(context.Context, string, string, models.SObject) error {
			repositoryUpdates++
			return nil
		},
	}
	ps := newUpsertTestPersistenceService(
		t,
		repo,
		&stubUpsertPermRepo{grantCreate: true, grantEdit: true},
		&stubUpsertMetaRepo{schema: testSchema},
		testSchema,
	)
	retryTx := &uniqueRaceRetryTxManager{}
	ps.txManager = retryTx
	ps.eventBus = &eventFeature.NoOpEventBus{}
	ps.validator = metadata.NewValidationService(formula.NewEngine())
	ps.schemaValidator = validation.NewSchemaValidator()
	ps.rollup = noopRollupProcessor{}

	got, created, err := ps.UpsertByUniqueExternalID(
		constants.WithSecurityContext(context.Background(), constants.SystemContext, "unique precheck race test"),
		objectName,
		extField,
		extValue,
		models.SObject{constants.FieldName: "incoming"},
		recordEntrypointUser(),
	)
	if err != nil {
		t.Fatalf("UpsertByUniqueExternalID() error = %v", err)
	}
	if created || extractRecordID(got) != winnerID {
		t.Fatalf("result = (%#v, created=%t), want adopted winner %q", got, created, winnerID)
	}
	if retryTx.attempts != 2 || lookupCalls != 4 || precheckCalls != 1 || repositoryInserts != 0 || repositoryUpdates != 1 {
		t.Fatalf(
			"attempts/lookups/prechecks/inserts/updates = %d/%d/%d/%d/%d, want 2/4/1/0/1",
			retryTx.attempts,
			lookupCalls,
			precheckCalls,
			repositoryInserts,
			repositoryUpdates,
		)
	}
}

func TestUpsertByUniqueExternalIDDoesNotReplayAfterCreateHookConflict(t *testing.T) {
	const (
		objectName = "Widget__c"
		extField   = "ExternalId__c"
		extValue   = "provider:object:hook-conflict"
		capability = "test.upsert.after-create-conflict"
	)
	testSchema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldName, Type: constants.FieldTypeString, Createable: true, Updateable: true},
			{APIName: extField, Type: constants.FieldTypeString, IsUnique: true, Createable: true, Updateable: true},
		},
		AccessPolicy: &models.ObjectAccessPolicy{
			Lifecycle: &models.RecordLifecycleAccessPolicy{
				Hooks: []models.RecordLifecycleHookPolicy{{
					Phase:      string(contract.RecordLifecycleAfterCreateInTx),
					Capability: capability,
				}},
			},
		},
	}
	for _, test := range []struct {
		name    string
		hookErr error
	}{
		{name: "domain conflict", hookErr: appErrors.NewConflictError(objectName, extField, extValue)},
		{
			name: "database duplicate marker from hook-owned write",
			hookErr: fmt.Errorf(
				"%w: %w",
				ports.ErrDatabaseDuplicateKey,
				appErrors.NewConflictError(objectName, schema.GenerateUniqueIndexName(objectName, extField), ""),
			),
		},
	} {
		t.Run(test.name, func(t *testing.T) {
			lookupCalls := 0
			insertCalls := 0
			hookCalls := 0
			repo := &stubUpsertRecordRepo{
				findOneByFieldFn: func(context.Context, string, string, interface{}) (models.SObject, error) {
					lookupCalls++
					return nil, nil
				},
				insertFn: func(context.Context, string, models.SObject) error {
					insertCalls++
					return nil
				},
			}
			ps := newUpsertTestPersistenceService(
				t,
				repo,
				&stubUpsertPermRepo{grantCreate: true, grantEdit: true},
				&stubUpsertMetaRepo{schema: testSchema},
				testSchema,
			)
			retryTx := &uniqueRaceRetryTxManager{}
			ps.txManager = retryTx
			ps.eventBus = &eventFeature.NoOpEventBus{}
			ps.validator = metadata.NewValidationService(formula.NewEngine())
			ps.schemaValidator = validation.NewSchemaValidator()
			ps.rollup = noopRollupProcessor{}
			ps.lifecycleHooks = newRecordLifecycleHooks()
			if err := ps.RegisterRecordLifecycleHook(contract.RecordLifecycleRegistration{
				Capability: capability,
				Phase:      contract.RecordLifecycleAfterCreateInTx,
				Hook: func(context.Context, contract.RecordLifecycleEvent) error {
					hookCalls++
					return test.hookErr
				},
			}); err != nil {
				t.Fatalf("RegisterRecordLifecycleHook() error = %v", err)
			}

			_, _, err := ps.UpsertByUniqueExternalID(
				constants.WithSecurityContext(context.Background(), constants.SystemContext, "post-insert conflict provenance test"),
				objectName,
				extField,
				extValue,
				models.SObject{constants.FieldName: "incoming"},
				recordEntrypointUser(),
			)
			var conflict *appErrors.ConflictError
			if !errors.As(err, &conflict) {
				t.Fatalf("UpsertByUniqueExternalID() error = %v, want original hook ConflictError", err)
			}
			if ports.IsUniqueFieldUpsertRace(err) {
				t.Fatalf("hook conflict was misclassified as unique-field race: %v", err)
			}
			if retryTx.attempts != 1 || lookupCalls != 1 || insertCalls != 1 || hookCalls != 1 {
				t.Fatalf(
					"attempts/lookups/inserts/hooks = %d/%d/%d/%d, want 1/1/1/1",
					retryTx.attempts,
					lookupCalls,
					insertCalls,
					hookCalls,
				)
			}
		})
	}
}

func TestUpsertByUniqueExternalIDRejectsNonUniqueFieldBeforeMutation(t *testing.T) {
	const (
		objectName = "Widget__c"
		fieldName  = "LegacyExternalId__c"
	)
	lookups := 0
	schema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: fieldName, Type: constants.FieldTypeString, Createable: true, Updateable: true},
		},
	}
	ps := newUpsertTestPersistenceService(
		t,
		&stubUpsertRecordRepo{findOneByFieldFn: func(context.Context, string, string, interface{}) (models.SObject, error) {
			lookups++
			return nil, nil
		}},
		&stubUpsertPermRepo{},
		&stubUpsertMetaRepo{schema: schema},
		schema,
	)

	_, _, err := ps.UpsertByUniqueExternalID(
		context.Background(),
		objectName,
		fieldName,
		"legacy-1",
		models.SObject{},
		nil,
	)
	if err == nil || !strings.Contains(err.Error(), "requires unique field") {
		t.Fatalf("UpsertByUniqueExternalID() error = %v, want non-unique rejection", err)
	}
	if lookups != 0 {
		t.Fatalf("FindOneByField calls = %d after non-unique metadata, want 0", lookups)
	}
}

func TestUniqueExternalIDUpsertsRejectUnwritableIdentityBeforeMutation(t *testing.T) {
	const objectName = "Widget__c"
	formulaExpression := "Name"
	for _, test := range []struct {
		name  string
		field models.FieldMetadata
	}{
		{
			name:  "not createable",
			field: models.FieldMetadata{APIName: "ExternalId__c", IsUnique: true, Updateable: true},
		},
		{
			name:  "not updateable",
			field: models.FieldMetadata{APIName: "ExternalId__c", IsUnique: true, Createable: true},
		},
		{
			name: "virtual",
			field: models.FieldMetadata{
				APIName: "ExternalId__c", IsUnique: true, Createable: true, Updateable: true, IsVirtual: true,
			},
		},
		{
			name: "compound",
			field: models.FieldMetadata{
				APIName: "ExternalId__c", IsUnique: true, Createable: true, Updateable: true, IsCompound: true,
			},
		},
		{
			name: "formula",
			field: models.FieldMetadata{
				APIName: "ExternalId__c", IsUnique: true, Createable: true, Updateable: true, Formula: &formulaExpression,
			},
		},
	} {
		t.Run(test.name, func(t *testing.T) {
			for _, apiName := range []string{"UpsertByUniqueExternalID", "UpsertByExternalID"} {
				t.Run(apiName, func(t *testing.T) {
					lookups := 0
					schema := &models.ObjectMetadata{
						APIName: objectName,
						Fields: []models.FieldMetadata{
							{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
							test.field,
						},
					}
					ps := newUpsertTestPersistenceService(
						t,
						&stubUpsertRecordRepo{findOneByFieldFn: func(context.Context, string, string, interface{}) (models.SObject, error) {
							lookups++
							return nil, nil
						}},
						&stubUpsertPermRepo{},
						&stubUpsertMetaRepo{schema: schema},
						schema,
					)

					var err error
					if apiName == "UpsertByUniqueExternalID" {
						_, _, err = ps.UpsertByUniqueExternalID(
							context.Background(), objectName, test.field.APIName, "provider:object:1", models.SObject{}, nil,
						)
					} else {
						_, _, err = ps.UpsertByExternalID(
							context.Background(), objectName, test.field.APIName, "provider:object:1", models.SObject{}, nil,
						)
					}
					if err == nil || !strings.Contains(err.Error(), "createable, updateable, persistable non-formula") {
						t.Fatalf("%s() error = %v, want unwritable-field rejection", apiName, err)
					}
					if lookups != 0 {
						t.Fatalf("FindOneByField calls = %d after unwritable metadata, want 0", lookups)
					}
				})
			}
		})
	}
}

func TestAdoptionGuardProvenanceBelongsOnlyToDirectUpdate(t *testing.T) {
	const (
		objectName = "Widget__c"
		recordID   = "widget-001"
	)
	guardCtx, guard := withExternalIDUpsertAdoptionGuard(
		context.Background(),
		objectName,
		recordID,
		[]string{"OwnerIdentity__c"},
	)
	outerCtx := enterExternalIDUpsertAdoptionGuardUpdate(guardCtx, objectName, recordID)
	nestedCtx := enterExternalIDUpsertAdoptionGuardUpdate(outerCtx, objectName, recordID)

	if got := claimExternalIDUpsertAdoptionGuardMutation(
		nestedCtx,
		objectName,
		recordID,
		externalIDUpsertAdoptionGuardUniquenessPrecheck,
	); got != nil {
		t.Fatalf("nested same-record Update claimed outer guard provenance: %#v", got)
	}
	if got := claimExternalIDUpsertAdoptionGuardMutation(
		outerCtx,
		objectName,
		recordID,
		externalIDUpsertAdoptionGuardUniquenessPrecheck,
	); got != guard {
		t.Fatalf("direct Update guard owner = %#v, want %#v", got, guard)
	}
	if got := claimExternalIDUpsertAdoptionGuardMutation(
		outerCtx,
		objectName,
		recordID,
		externalIDUpsertAdoptionGuardRepositoryUpdate,
	); got != guard {
		t.Fatalf("direct repository mutation guard owner = %#v, want %#v", got, guard)
	}
	oldRecord := models.SObject{"OwnerIdentity__c": "outer-owner"}
	finalRecord := models.SObject{"OwnerIdentity__c": "nested-owner"}
	ps := &PersistenceService{repo: &stubUpsertRecordRepo{findOneByFieldFn: func(_ context.Context, _ string, field string, value interface{}) (models.SObject, error) {
		if field == "OwnerIdentity__c" && value == "outer-owner" {
			return models.SObject{constants.FieldID: recordID, field: value}, nil
		}
		return nil, nil
	}}}
	if err := ps.validateExternalIDUpsertAdoptionGuard(nestedCtx, objectName, recordID, oldRecord, finalRecord); err != nil {
		t.Fatalf("nested same-record Update borrowed outer guard validation: %v", err)
	}
	if err := ps.validateExternalIDUpsertAdoptionGuard(outerCtx, objectName, recordID, oldRecord, finalRecord); !ports.IsExternalIDUpsertAdoptionGuardConflict(err) {
		t.Fatalf("direct Update guard validation error = %v, want ownership conflict", err)
	}
}

func TestAdoptionConflictBoundaryDropsInternalDiagnosticFromUnwrapChain(t *testing.T) {
	diagnostic := appErrors.NewInternalError("repository update failed", errors.New("duplicate guard index"))
	err := newExternalIDUpsertAdoptionConflictError("guard ownership conflict", diagnostic)
	if !ports.IsExternalIDUpsertAdoptionGuardConflict(err) {
		t.Fatalf("error = %v, want adoption conflict sentinel", err)
	}
	if appErrors.IsInternal(err) {
		t.Fatalf("clean adoption conflict retained internal diagnostic in unwrap chain: %v", err)
	}
	if !strings.Contains(err.Error(), "duplicate guard index") {
		t.Fatalf("clean adoption conflict lost operator diagnostic text: %v", err)
	}
}

func TestGuardedUpsertResolvesOmittedExternalIDInsidePersistence(t *testing.T) {
	const (
		objectName = "Lead"
		extField   = "Salesforce_Identity_Key__c"
		guardField = "HubSpot_Id__c"
		resolved   = "00D-org:Lead:00Q-defaulted"
	)
	for _, test := range []struct {
		name       string
		useDefault bool
		beforeSave bool
		wantErr    bool
	}{
		{name: "schema default supplies identity", useDefault: true},
		{name: "before-save supplies identity", beforeSave: true},
		{name: "missing final identity fails before repository insert", wantErr: true},
	} {
		t.Run(test.name, func(t *testing.T) {
			extFieldMeta := models.FieldMetadata{
				APIName: extField, Type: constants.FieldTypeString, IsUnique: true, Createable: true, Updateable: true,
			}
			if test.useDefault {
				defaultValue := resolved
				extFieldMeta.DefaultValue = &defaultValue
			}
			testSchema := &models.ObjectMetadata{
				APIName: objectName,
				Fields: []models.FieldMetadata{
					{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
					{APIName: constants.FieldName, Type: constants.FieldTypeString, Createable: true, Updateable: true},
					extFieldMeta,
					{APIName: guardField, Type: constants.FieldTypeString, IsUnique: true, Createable: true, Updateable: true},
				},
			}
			insertCalls := 0
			var inserted models.SObject
			repo := &stubUpsertRecordRepo{insertFn: func(_ context.Context, gotObject string, record models.SObject) error {
				if gotObject != objectName {
					return nil
				}
				insertCalls++
				inserted = cloneSObject(record)
				return nil
			}}
			ps := newUpsertTestPersistenceService(
				t,
				repo,
				&stubUpsertPermRepo{grantCreate: true, grantEdit: true},
				&stubUpsertMetaRepo{schema: testSchema},
				testSchema,
			)
			ps.txManager = &uniqueRaceRetryTxManager{}
			ps.eventBus = &eventFeature.NoOpEventBus{}
			if test.beforeSave {
				ps.eventBus = mutatingRecordEventBus{mutate: func(eventType sharedEvents.EventType, record models.SObject) {
					if eventType == sharedEvents.RecordBeforeCreate {
						record[extField] = resolved
					}
				}}
			}
			ps.validator = metadata.NewValidationService(formula.NewEngine())
			ps.schemaValidator = validation.NewSchemaValidator()
			ps.rollup = noopRollupProcessor{}
			incoming := models.SObject{constants.FieldName: "Inbound", guardField: "hs-42"}

			got, created, err := ps.UpsertByExternalIDWithAdoptionGuards(
				constants.WithSecurityContext(context.Background(), constants.SystemContext, "deferred external identity test"),
				objectName,
				extField,
				"",
				incoming,
				[]string{guardField},
				recordEntrypointUser(),
			)
			if test.wantErr {
				if err == nil || !strings.Contains(err.Error(), "non-blank final string value") {
					t.Fatalf("error = %v, want final external-ID rejection", err)
				}
				if insertCalls != 0 {
					t.Fatalf("repository insert calls = %d after missing final identity, want 0", insertCalls)
				}
				return
			}
			if err != nil {
				t.Fatalf("UpsertByExternalIDWithAdoptionGuards() error = %v", err)
			}
			if !created || insertCalls != 1 || inserted[extField] != resolved || got[extField] != resolved {
				t.Fatalf("created/insert calls/insert/result = %v/%d/%#v/%#v, want one create with resolved identity", created, insertCalls, inserted, got)
			}
			if _, mutated := incoming[extField]; mutated {
				t.Fatalf("caller input gained persistence-resolved identity: %#v", incoming)
			}
		})
	}
}

func TestGuardedUpsertDefaultedExternalIDConflictRetriesAndAdoptsWinner(t *testing.T) {
	const (
		objectName = "Lead"
		extField   = "Salesforce_Identity_Key__c"
		guardField = "HubSpot_Id__c"
		resolved   = "00D-org:Lead:00Q-defaulted-winner"
		winnerID   = "00Q-local-winner"
	)
	defaultValue := resolved
	testSchema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldName, Type: constants.FieldTypeString, Createable: true, Updateable: true},
			{APIName: extField, Type: constants.FieldTypeString, IsUnique: true, Createable: true, Updateable: true, DefaultValue: &defaultValue},
			{APIName: guardField, Type: constants.FieldTypeString, IsUnique: true, Createable: true, Updateable: true},
		},
	}
	winner := models.SObject{
		constants.FieldID:   winnerID,
		constants.FieldName: "winner",
		extField:            resolved,
		guardField:          "",
	}
	lookupCalls := 0
	insertCalls := 0
	updateCalls := 0
	repo := &stubUpsertRecordRepo{
		findOneByFieldFn: func(_ context.Context, gotObject, gotField string, gotValue interface{}) (models.SObject, error) {
			lookupCalls++
			if gotObject != objectName || gotField != extField || gotValue != resolved {
				t.Fatalf("FindOneByField(%q, %q, %#v), want resolved default identity", gotObject, gotField, gotValue)
			}
			return cloneSObject(winner), nil
		},
		findOneFn: func(context.Context, string, string) (models.SObject, error) {
			return cloneSObject(winner), nil
		},
		getLockFn: func(context.Context, string, string) (models.SObject, error) {
			return cloneSObject(winner), nil
		},
		checkUniqueFn: func(_ context.Context, _ *models.ObjectMetadata, field string, value interface{}, excludeID string) (bool, error) {
			return excludeID == "" && strings.EqualFold(field, extField) && value == resolved, nil
		},
		insertFn: func(_ context.Context, gotObject string, _ models.SObject) error {
			if gotObject == objectName {
				insertCalls++
			}
			return nil
		},
		updateFn: func(_ context.Context, gotObject, gotID string, updates models.SObject) error {
			updateCalls++
			if gotObject != objectName || gotID != winnerID || updates[guardField] != "hs-42" {
				t.Fatalf("Update(%q, %q, %#v), want guarded winner adoption", gotObject, gotID, updates)
			}
			return nil
		},
	}
	ps := newUpsertTestPersistenceService(
		t,
		repo,
		&stubUpsertPermRepo{grantCreate: true, grantEdit: true},
		&stubUpsertMetaRepo{schema: testSchema},
		testSchema,
	)
	retryTx := &uniqueRaceRetryTxManager{}
	ps.txManager = retryTx
	ps.eventBus = &eventFeature.NoOpEventBus{}
	ps.validator = metadata.NewValidationService(formula.NewEngine())
	ps.schemaValidator = validation.NewSchemaValidator()
	ps.rollup = noopRollupProcessor{}

	got, created, err := ps.UpsertByExternalIDWithAdoptionGuards(
		constants.WithSecurityContext(context.Background(), constants.SystemContext, "defaulted external identity winner test"),
		objectName,
		extField,
		"",
		models.SObject{constants.FieldName: "Inbound", guardField: "hs-42"},
		[]string{guardField},
		recordEntrypointUser(),
	)
	if err != nil {
		t.Fatalf("UpsertByExternalIDWithAdoptionGuards() error = %v", err)
	}
	if created || extractRecordID(got) != winnerID {
		t.Fatalf("created/result = %v/%#v, want adopted winner %s", created, got, winnerID)
	}
	if retryTx.attempts != 2 || lookupCalls != 3 || insertCalls != 0 || updateCalls != 1 {
		t.Fatalf("attempts/lookups/inserts/updates = %d/%d/%d/%d, want 2/3/0/1", retryTx.attempts, lookupCalls, insertCalls, updateCalls)
	}
}

func TestUpsertByExternalIDWithAdoptionGuardsUsesLockedFinalOwner(t *testing.T) {
	const (
		objectName = "Lead"
		extField   = "Salesforce_Identity_Key__c"
		extValue   = "00D-org:Lead:00Q-source"
		guardField = "HubSpot_Id__c"
		recordID   = "00Q-local-owner"
	)
	testSchema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldName, Type: constants.FieldTypeString, Createable: true, Updateable: true},
			{APIName: extField, Type: constants.FieldTypeString, IsUnique: true, Createable: true, Updateable: true},
			{APIName: guardField, Type: constants.FieldTypeString, IsUnique: true, Createable: true, Updateable: true},
		},
	}

	tests := []struct {
		name                    string
		oldOwner                any
		beforeSaveOwner         string
		beforeSaveExternalID    string
		guardClaimAlreadyExists bool
		repositoryGuardConflict bool
		wantConflict            bool
		wantIdentityInvariant   bool
		wantRepositoryDML       int
	}{
		{name: "blank owner is claimable", oldOwner: "", wantRepositoryDML: 1},
		{name: "same owner is adoptable", oldOwner: "hs-42", wantRepositoryDML: 1},
		{name: "database-equivalent guard case variant is adoptable", oldOwner: "HS-42", wantRepositoryDML: 1},
		{name: "different owner is quarantinable conflict", oldOwner: "hs-other", wantConflict: true},
		{name: "before-save retarget is checked on final image", oldOwner: "hs-42", beforeSaveOwner: "hs-other", wantConflict: true},
		{name: "before-save external identity retarget is rejected before DML", oldOwner: "hs-42", beforeSaveExternalID: extValue + "-other", wantIdentityInvariant: true},
		{name: "guard identity owned by another row routes adoption conflict", oldOwner: "", guardClaimAlreadyExists: true, wantConflict: true},
		{name: "concurrent repository guard claim routes adoption conflict", oldOwner: "", repositoryGuardConflict: true, wantConflict: true, wantRepositoryDML: 1},
	}
	for _, test := range tests {
		t.Run(test.name, func(t *testing.T) {
			locked := models.SObject{
				constants.FieldID:   recordID,
				constants.FieldName: "existing",
				extField:            extValue,
				guardField:          test.oldOwner,
			}
			repositoryDML := 0
			auditInserts := 0
			var mutationOrder []string
			repo := &stubUpsertRecordRepo{
				findOneByFieldFn: func(_ context.Context, gotObject, gotField string, gotValue interface{}) (models.SObject, error) {
					if gotObject != objectName {
						return nil, nil
					}
					value, valueIsString := gotValue.(string)
					if !valueIsString {
						return nil, nil
					}
					switch {
					case strings.EqualFold(gotField, extField) && strings.EqualFold(value, extValue):
						return cloneSObject(locked), nil
					case strings.EqualFold(gotField, guardField):
						oldOwner, oldOwnerIsString := test.oldOwner.(string)
						if oldOwnerIsString && strings.EqualFold(value, oldOwner) {
							return cloneSObject(locked), nil
						}
					}
					return nil, nil
				},
				findOneFn: func(context.Context, string, string) (models.SObject, error) {
					return cloneSObject(locked), nil
				},
				getLockFn: func(context.Context, string, string) (models.SObject, error) {
					return cloneSObject(locked), nil
				},
				checkUniqueFn: func(_ context.Context, _ *models.ObjectMetadata, field string, _ interface{}, _ string) (bool, error) {
					return test.guardClaimAlreadyExists && strings.EqualFold(field, guardField), nil
				},
				updateFn: func(_ context.Context, gotObject, gotID string, updates models.SObject) error {
					repositoryDML++
					mutationOrder = append(mutationOrder, "update")
					if gotObject != objectName || gotID != recordID {
						t.Fatalf("Update(%q, %q, %#v) targeted the wrong guarded owner", gotObject, gotID, updates)
					}
					if test.repositoryGuardConflict {
						return fmt.Errorf(
							"%w: %w",
							ports.ErrDatabaseDuplicateKey,
							appErrors.NewConflictError(objectName, schema.GenerateUniqueIndexName(objectName, guardField), ""),
						)
					}
					return nil
				},
				insertFn: func(_ context.Context, gotObject string, _ models.SObject) error {
					if gotObject == constants.TableAuditLog {
						auditInserts++
						mutationOrder = append(mutationOrder, "audit")
					}
					return nil
				},
				bulkInsertFn: func(_ context.Context, gotObject string, records []models.SObject, _ []string, _ interface{}) error {
					if gotObject == constants.TableAuditLog {
						auditInserts += len(records)
						mutationOrder = append(mutationOrder, "audit")
					}
					return nil
				},
			}
			ps := newUpsertTestPersistenceService(
				t,
				repo,
				&stubUpsertPermRepo{grantCreate: true, grantEdit: true},
				&stubUpsertMetaRepo{schema: testSchema},
				testSchema,
			)
			ps.txManager = &uniqueRaceRetryTxManager{}
			ps.eventBus = &eventFeature.NoOpEventBus{}
			if test.beforeSaveOwner != "" || test.beforeSaveExternalID != "" {
				ps.eventBus = mutatingRecordEventBus{mutate: func(eventType sharedEvents.EventType, record models.SObject) {
					if eventType == sharedEvents.RecordBeforeUpdate {
						if test.beforeSaveOwner != "" {
							record[guardField] = test.beforeSaveOwner
						}
						if test.beforeSaveExternalID != "" {
							record[extField] = test.beforeSaveExternalID
						}
					}
				}}
			}
			ps.validator = metadata.NewValidationService(formula.NewEngine())
			ps.schemaValidator = validation.NewSchemaValidator()
			ps.rollup = noopRollupProcessor{}

			_, created, err := ps.UpsertByExternalIDWithAdoptionGuards(
				constants.WithSecurityContext(context.Background(), constants.SystemContext, "guarded external identity test"),
				objectName,
				extField,
				extValue,
				models.SObject{constants.FieldName: "incoming", guardField: "hs-42"},
				[]string{guardField},
				recordEntrypointUser(),
			)
			if test.wantConflict {
				if !ports.IsExternalIDUpsertAdoptionGuardConflict(err) {
					t.Fatalf("error = %v, want guarded-adoption conflict", err)
				}
				if test.repositoryGuardConflict && appErrors.IsInternal(err) {
					t.Fatalf("proved repository guard conflict retained an internal envelope: %v", err)
				}
			} else if test.wantIdentityInvariant {
				if err == nil || !strings.Contains(err.Error(), "final identity") {
					t.Fatalf("error = %v, want final external-ID invariant rejection", err)
				}
				if ports.IsExternalIDUpsertAdoptionGuardConflict(err) || ports.IsUniqueFieldUpsertRace(err) {
					t.Fatalf("external-ID retarget was misclassified as an ownership/race conflict: %v", err)
				}
			} else if err != nil {
				t.Fatalf("UpsertByExternalIDWithAdoptionGuards() error = %v", err)
			}
			if created {
				t.Fatal("created = true for an existing external identity")
			}
			if repositoryDML != test.wantRepositoryDML {
				t.Fatalf("repository update calls = %d, want %d", repositoryDML, test.wantRepositoryDML)
			}
			if test.name == "blank owner is claimable" {
				if auditInserts == 0 || len(mutationOrder) != 2 || mutationOrder[0] != "update" || mutationOrder[1] != "audit" {
					t.Fatalf("guarded success mutation order = %#v with %d audits, want [update audit] with audit rows", mutationOrder, auditInserts)
				}
			}
			if test.repositoryGuardConflict && auditInserts != 0 {
				t.Fatalf("failed guarded repository update emitted %d audit rows before ownership routing", auditInserts)
			}
		})
	}
}

func TestUniqueExternalIDUpsertExistingOwnerRejectsBeforeSaveRetarget(t *testing.T) {
	const (
		objectName = "Widget__c"
		extField   = "ExternalId__c"
		requested  = "provider:object:requested"
		retargeted = "provider:object:retargeted"
		recordID   = "widget-owner"
	)
	testSchema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldName, Type: constants.FieldTypeString, Createable: true, Updateable: true},
			{APIName: extField, Type: constants.FieldTypeString, IsUnique: true, Createable: true, Updateable: true},
		},
	}
	locked := models.SObject{
		constants.FieldID:   recordID,
		constants.FieldName: "existing",
		extField:            requested,
	}

	for _, apiName := range []string{"UpsertByExternalID", "UpsertByUniqueExternalID"} {
		t.Run(apiName, func(t *testing.T) {
			lookupCalls := 0
			repositoryDML := 0
			repo := &stubUpsertRecordRepo{
				findOneByFieldFn: func(_ context.Context, gotObject, gotField string, gotValue interface{}) (models.SObject, error) {
					lookupCalls++
					if gotObject != objectName || gotField != extField {
						t.Fatalf("FindOneByField(%q, %q, %#v), want %s.%s", gotObject, gotField, gotValue, objectName, extField)
					}
					if gotValue == requested {
						return cloneSObject(locked), nil
					}
					if gotValue == retargeted {
						return nil, nil
					}
					t.Fatalf("FindOneByField(%q, %q, %#v), want requested or retargeted identity", gotObject, gotField, gotValue)
					return nil, nil
				},
				findOneFn: func(context.Context, string, string) (models.SObject, error) {
					return cloneSObject(locked), nil
				},
				getLockFn: func(context.Context, string, string) (models.SObject, error) {
					return cloneSObject(locked), nil
				},
				updateFn: func(context.Context, string, string, models.SObject) error {
					repositoryDML++
					return nil
				},
			}
			ps := newUpsertTestPersistenceService(
				t,
				repo,
				&stubUpsertPermRepo{grantCreate: true, grantEdit: true},
				&stubUpsertMetaRepo{schema: testSchema},
				testSchema,
			)
			retryTx := &uniqueRaceRetryTxManager{}
			ps.txManager = retryTx
			ps.eventBus = mutatingRecordEventBus{mutate: func(eventType sharedEvents.EventType, record models.SObject) {
				if eventType == sharedEvents.RecordBeforeUpdate {
					record[extField] = retargeted
				}
			}}
			ps.validator = metadata.NewValidationService(formula.NewEngine())
			ps.schemaValidator = validation.NewSchemaValidator()
			ps.rollup = noopRollupProcessor{}

			ctx := constants.WithSecurityContext(context.Background(), constants.SystemContext, "existing external identity invariant test")
			var err error
			if apiName == "UpsertByUniqueExternalID" {
				_, _, err = ps.UpsertByUniqueExternalID(
					ctx,
					objectName,
					extField,
					requested,
					models.SObject{constants.FieldName: "incoming"},
					recordEntrypointUser(),
				)
			} else {
				_, _, err = ps.UpsertByExternalID(
					ctx,
					objectName,
					extField,
					requested,
					models.SObject{constants.FieldName: "incoming"},
					recordEntrypointUser(),
				)
			}
			if err == nil || !strings.Contains(err.Error(), "final identity") {
				t.Fatalf("%s() error = %v, want final external-ID invariant rejection", apiName, err)
			}
			if ports.IsExternalIDUpsertAdoptionGuardConflict(err) || ports.IsUniqueFieldUpsertRace(err) {
				t.Fatalf("%s() retarget was misclassified as an ownership/race conflict: %v", apiName, err)
			}
			if lookupCalls != 3 || repositoryDML != 0 || retryTx.attempts != 1 {
				t.Fatalf("%s() lookups/repository updates/attempts = %d/%d/%d, want 3/0/1", apiName, lookupCalls, repositoryDML, retryTx.attempts)
			}
		})
	}
}

func TestLegacyNonUniqueExternalIDUpsertPreservesBeforeSaveRetarget(t *testing.T) {
	const (
		objectName = "Widget__c"
		extField   = "LegacyExternalId__c"
		requested  = "legacy:requested"
		retargeted = "legacy:normalized"
		recordID   = "legacy-owner"
	)
	testSchema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldName, Type: constants.FieldTypeString, Createable: true, Updateable: true},
			{APIName: extField, Type: constants.FieldTypeString, Createable: true, Updateable: true},
		},
	}
	locked := models.SObject{
		constants.FieldID:   recordID,
		constants.FieldName: "existing",
		extField:            requested,
	}
	lookupCalls := 0
	updateCalls := 0
	repo := &stubUpsertRecordRepo{
		findOneByFieldFn: func(_ context.Context, gotObject, gotField string, gotValue interface{}) (models.SObject, error) {
			lookupCalls++
			if gotObject != objectName || gotField != extField || gotValue != requested {
				t.Fatalf("FindOneByField(%q, %q, %#v), want legacy requested identity", gotObject, gotField, gotValue)
			}
			return cloneSObject(locked), nil
		},
		findOneFn: func(context.Context, string, string) (models.SObject, error) {
			return cloneSObject(locked), nil
		},
		getLockFn: func(context.Context, string, string) (models.SObject, error) {
			return cloneSObject(locked), nil
		},
		updateFn: func(_ context.Context, gotObject, gotID string, updates models.SObject) error {
			updateCalls++
			if gotObject != objectName || gotID != recordID {
				t.Fatalf("Update(%q, %q) targeted the wrong legacy owner", gotObject, gotID)
			}
			if updates[extField] != retargeted {
				t.Fatalf("legacy repository identity = %#v, want before-save normalized %q", updates[extField], retargeted)
			}
			return nil
		},
	}
	ps := newUpsertTestPersistenceService(
		t,
		repo,
		&stubUpsertPermRepo{grantCreate: true, grantEdit: true},
		&stubUpsertMetaRepo{schema: testSchema},
		testSchema,
	)
	ps.txManager = inlineTxManager{}
	ps.eventBus = mutatingRecordEventBus{mutate: func(eventType sharedEvents.EventType, record models.SObject) {
		if eventType == sharedEvents.RecordBeforeUpdate {
			record[extField] = retargeted
		}
	}}
	ps.validator = metadata.NewValidationService(formula.NewEngine())
	ps.schemaValidator = validation.NewSchemaValidator()
	ps.rollup = noopRollupProcessor{}

	_, created, err := ps.UpsertByExternalID(
		constants.WithSecurityContext(context.Background(), constants.SystemContext, "legacy non-unique retarget compatibility test"),
		objectName,
		extField,
		requested,
		models.SObject{constants.FieldName: "incoming"},
		recordEntrypointUser(),
	)
	if err != nil {
		t.Fatalf("legacy UpsertByExternalID() error = %v", err)
	}
	if created {
		t.Fatal("created = true for existing legacy non-unique owner")
	}
	if lookupCalls != 1 || updateCalls != 1 {
		t.Fatalf("legacy lookups/updates = %d/%d, want 1/1", lookupCalls, updateCalls)
	}
}

func TestUniqueExternalIDUpsertExistingOwnerAcceptsDatabaseEquivalentCaseVariant(t *testing.T) {
	const (
		objectName = "Widget__c"
		extField   = "ExternalId__c"
		stored     = "provider:object:mixed-case"
		requested  = "PROVIDER:OBJECT:MIXED-CASE"
		recordID   = "widget-owner"
	)
	testSchema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldName, Type: constants.FieldTypeString, Createable: true, Updateable: true},
			{APIName: extField, Type: constants.FieldTypeString, IsUnique: true, Createable: true, Updateable: true},
		},
	}
	locked := models.SObject{
		constants.FieldID:   recordID,
		constants.FieldName: "existing",
		extField:            stored,
	}

	for _, apiName := range []string{"UpsertByExternalID", "UpsertByUniqueExternalID"} {
		t.Run(apiName, func(t *testing.T) {
			lookups := 0
			repositoryDML := 0
			repo := &stubUpsertRecordRepo{
				findOneByFieldFn: func(_ context.Context, gotObject, gotField string, gotValue interface{}) (models.SObject, error) {
					lookups++
					value, _ := gotValue.(string)
					if gotObject == objectName && strings.EqualFold(gotField, extField) && strings.EqualFold(value, stored) {
						return cloneSObject(locked), nil
					}
					return nil, nil
				},
				findOneFn: func(context.Context, string, string) (models.SObject, error) {
					return cloneSObject(locked), nil
				},
				getLockFn: func(context.Context, string, string) (models.SObject, error) {
					return cloneSObject(locked), nil
				},
				updateFn: func(_ context.Context, _ string, _ string, updates models.SObject) error {
					repositoryDML++
					if updates[extField] != requested {
						t.Fatalf("repository external identity = %#v, want requested case variant %q", updates[extField], requested)
					}
					return nil
				},
			}
			ps := newUpsertTestPersistenceService(
				t,
				repo,
				&stubUpsertPermRepo{grantCreate: true, grantEdit: true},
				&stubUpsertMetaRepo{schema: testSchema},
				testSchema,
			)
			ps.txManager = &uniqueRaceRetryTxManager{}
			ps.eventBus = &eventFeature.NoOpEventBus{}
			ps.validator = metadata.NewValidationService(formula.NewEngine())
			ps.schemaValidator = validation.NewSchemaValidator()
			ps.rollup = noopRollupProcessor{}

			ctx := constants.WithSecurityContext(context.Background(), constants.SystemContext, "database collation upsert test")
			var (
				got     models.SObject
				created bool
				err     error
			)
			if apiName == "UpsertByUniqueExternalID" {
				got, created, err = ps.UpsertByUniqueExternalID(
					ctx, objectName, extField, requested, models.SObject{constants.FieldName: "incoming"}, recordEntrypointUser(),
				)
			} else {
				got, created, err = ps.UpsertByExternalID(
					ctx, objectName, extField, requested, models.SObject{constants.FieldName: "incoming"}, recordEntrypointUser(),
				)
			}
			if err != nil {
				t.Fatalf("%s() error = %v, want database-equivalent adoption", apiName, err)
			}
			if created || extractRecordID(got) != recordID || repositoryDML != 1 || lookups != 2 {
				t.Fatalf("%s() created/id/updates/lookups = %v/%q/%d/%d, want false/%q/1/2", apiName, created, extractRecordID(got), repositoryDML, lookups, recordID)
			}
		})
	}
}

func TestCanonicalExternalIDUpsertAdoptionGuardsFailClosed(t *testing.T) {
	schema := &models.ObjectMetadata{
		APIName: "Lead",
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, IsUnique: true, IsSystem: true},
			{APIName: "Salesforce_Identity_Key__c", IsUnique: true, Createable: true, Updateable: true},
			{APIName: "HubSpot_Id__c", IsUnique: true, Createable: true, Updateable: true},
			{APIName: "Legacy_HubSpot_Id__c", Updateable: true},
			{APIName: "Immutable_HubSpot_Id__c", IsUnique: true},
			{APIName: "Create_Read_Only_HubSpot_Id__c", IsUnique: true, Updateable: true},
		},
	}
	for _, test := range []struct {
		name   string
		fields []string
		data   models.SObject
	}{
		{name: "missing value", fields: []string{"HubSpot_Id__c"}, data: models.SObject{}},
		{name: "blank value", fields: []string{"HubSpot_Id__c"}, data: models.SObject{"HubSpot_Id__c": " "}},
		{name: "duplicate authored guard", fields: []string{"HubSpot_Id__c", "hubspot_id__c"}, data: models.SObject{"HubSpot_Id__c": "hs-1"}},
		{name: "ambiguous input aliases", fields: []string{"HubSpot_Id__c"}, data: models.SObject{"HubSpot_Id__c": "hs-1", "hubspot_id__c": "hs-2"}},
		{name: "external identity reused as guard", fields: []string{"Salesforce_Identity_Key__c"}, data: models.SObject{"Salesforce_Identity_Key__c": "sf-1"}},
		{name: "non-unique guard", fields: []string{"Legacy_HubSpot_Id__c"}, data: models.SObject{"Legacy_HubSpot_Id__c": "hs-1"}},
		{name: "immutable guard", fields: []string{"Immutable_HubSpot_Id__c"}, data: models.SObject{"Immutable_HubSpot_Id__c": "hs-1"}},
		{name: "non-createable guard", fields: []string{"Create_Read_Only_HubSpot_Id__c"}, data: models.SObject{"Create_Read_Only_HubSpot_Id__c": "hs-1"}},
		{name: "system id guard", fields: []string{constants.FieldID}, data: models.SObject{constants.FieldID: "00Q-owner"}},
	} {
		t.Run(test.name, func(t *testing.T) {
			if _, _, err := canonicalExternalIDUpsertAdoptionGuards(schema, "Salesforce_Identity_Key__c", test.fields, test.data); err == nil {
				t.Fatal("canonicalExternalIDUpsertAdoptionGuards() error = nil, want fail-closed rejection")
			}
		})
	}
}

func TestCanonicalExternalIDUpsertAdoptionGuardsAcceptsUniqueMutableGuard(t *testing.T) {
	schema := &models.ObjectMetadata{
		APIName: "Lead",
		Fields: []models.FieldMetadata{
			{APIName: "Salesforce_Identity_Key__c", IsUnique: true, Createable: true, Updateable: true},
			{APIName: "HubSpot_Id__c", IsUnique: true, Createable: true, Updateable: true},
		},
	}
	fields, values, err := canonicalExternalIDUpsertAdoptionGuards(
		schema,
		"Salesforce_Identity_Key__c",
		[]string{"hubspot_id__c"},
		models.SObject{"HubSpot_Id__c": "hs-1"},
	)
	if err != nil {
		t.Fatalf("canonicalExternalIDUpsertAdoptionGuards() error = %v", err)
	}
	if len(fields) != 1 || fields[0] != "HubSpot_Id__c" || values["HubSpot_Id__c"] != "hs-1" {
		t.Fatalf("guards = (%#v, %#v), want canonical unique mutable guard", fields, values)
	}
}

func TestExactAdoptionGuardUniqueConflictClassifiesOnlyNamedUniqueGuard(t *testing.T) {
	const objectName = "Lead"
	objectSchema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: "Salesforce_Identity_Key__c", PhysicalName: "Salesforce_Identity_Key__c", IsUnique: true},
			{APIName: "HubSpot_Id__c", PhysicalName: "HubSpot_Id__c", IsUnique: true},
			{APIName: "Email", PhysicalName: "Email", IsUnique: false},
		},
	}
	guardConflict := appErrors.NewConflictError(
		objectName,
		schema.GenerateUniqueIndexName(objectName, "HubSpot_Id__c"),
		"",
	)
	if !exactAdoptionGuardUniqueConflict(
		guardConflict,
		objectName,
		objectSchema,
		[]string{"HubSpot_Id__c"},
	) {
		t.Fatal("exact guard unique conflict was not classified as an adoption conflict")
	}

	externalIdentityConflict := appErrors.NewConflictError(
		objectName,
		schema.GenerateUniqueIndexName(objectName, "Salesforce_Identity_Key__c"),
		"",
	)
	if exactAdoptionGuardUniqueConflict(
		externalIdentityConflict,
		objectName,
		objectSchema,
		[]string{"HubSpot_Id__c"},
	) {
		t.Fatal("external identity conflict must not be routed as a guard adoption conflict")
	}
	if exactAdoptionGuardUniqueConflict(
		appErrors.NewConflictError(objectName, "Email", ""),
		objectName,
		objectSchema,
		[]string{"Email"},
	) {
		t.Fatal("non-unique guard metadata must not classify an unrelated conflict")
	}
}

func TestUpsertByExternalIDBeforeSaveRetargetConflictIsNotReplayed(t *testing.T) {
	const (
		objectName   = "Widget__c"
		extField     = "ExternalId__c"
		requestedID  = "provider:object:requested"
		retargetedID = "provider:object:retargeted"
	)
	testSchema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldName, Type: constants.FieldTypeString, Createable: true, Updateable: true},
			{APIName: extField, Type: constants.FieldTypeString, IsUnique: true, Createable: true, Updateable: true},
		},
	}
	lookupCalls := 0
	insertCalls := 0
	repo := &stubUpsertRecordRepo{
		findOneByFieldFn: func(_ context.Context, gotObject, gotField string, gotValue interface{}) (models.SObject, error) {
			lookupCalls++
			if gotObject != objectName || gotField != extField || gotValue != requestedID {
				t.Fatalf("FindOneByField(%q, %q, %#v), want requested identity", gotObject, gotField, gotValue)
			}
			return nil, nil
		},
		insertFn: func(_ context.Context, gotObject string, record models.SObject) error {
			insertCalls++
			if gotObject != objectName || record[extField] != retargetedID {
				t.Fatalf("Insert(%q, %#v), want before-save retargeted identity", gotObject, record)
			}
			return fmt.Errorf(
				"%w: %w",
				ports.ErrDatabaseDuplicateKey,
				appErrors.NewConflictError(objectName, schema.GenerateUniqueIndexName(objectName, extField), ""),
			)
		},
	}
	ps := newUpsertTestPersistenceService(
		t,
		repo,
		&stubUpsertPermRepo{grantCreate: true, grantEdit: true},
		&stubUpsertMetaRepo{schema: testSchema},
		testSchema,
	)
	retryTx := &uniqueRaceRetryTxManager{}
	ps.txManager = retryTx
	ps.eventBus = mutatingRecordEventBus{mutate: func(eventType sharedEvents.EventType, record models.SObject) {
		if eventType == sharedEvents.RecordBeforeCreate {
			record[extField] = retargetedID
		}
	}}
	ps.validator = metadata.NewValidationService(formula.NewEngine())
	ps.schemaValidator = validation.NewSchemaValidator()
	ps.rollup = noopRollupProcessor{}

	_, _, err := ps.UpsertByExternalID(
		constants.WithSecurityContext(context.Background(), constants.SystemContext, "before-save retarget test"),
		objectName,
		extField,
		requestedID,
		models.SObject{constants.FieldName: "incoming"},
		recordEntrypointUser(),
	)
	if err == nil {
		t.Fatal("UpsertByExternalID() error = nil, want retargeted duplicate conflict")
	}
	if ports.IsUniqueFieldUpsertRace(err) {
		t.Fatalf("retargeted identity conflict was marked as requested identity race: %v", err)
	}
	if retryTx.attempts != 1 || lookupCalls != 1 || insertCalls != 1 {
		t.Fatalf("attempts/lookups/inserts = %d/%d/%d, want 1/1/1", retryTx.attempts, lookupCalls, insertCalls)
	}
}

func TestUpsertByExternalIDSoftDeletedUniqueOwnerIsNotRetried(t *testing.T) {
	const (
		objectName = "Widget__c"
		extField   = "ExternalId__c"
		extValue   = "provider:object:soft-deleted"
	)

	testSchema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldName, Type: constants.FieldTypeString, Createable: true, Updateable: true},
			{APIName: extField, Type: constants.FieldTypeString, IsUnique: true, Createable: true, Updateable: true},
		},
	}

	repositoryInsertCalls := 0
	repo := &stubUpsertRecordRepo{
		findOneByFieldFn: func(_ context.Context, _ string, _ string, _ interface{}) (models.SObject, error) {
			// Live lookup excludes the soft-deleted owner.
			return nil, nil
		},
		checkUniqueFn: func(_ context.Context, _ *models.ObjectMetadata, field string, value interface{}, _ string) (bool, error) {
			if field != extField || value != extValue {
				t.Fatalf("CheckUniqueness(%q, %#v), want (%q, %q)", field, value, extField, extValue)
			}
			// Uniqueness precheck still sees the soft-deleted owner.
			return true, nil
		},
		insertFn: func(context.Context, string, models.SObject) error {
			repositoryInsertCalls++
			return nil
		},
	}

	ps := newUpsertTestPersistenceService(
		t,
		repo,
		&stubUpsertPermRepo{grantCreate: true, grantEdit: true},
		&stubUpsertMetaRepo{schema: testSchema},
		testSchema,
	)
	retryTx := &uniqueRaceRetryTxManager{}
	ps.txManager = retryTx
	ps.eventBus = &eventFeature.NoOpEventBus{}
	ps.validator = metadata.NewValidationService(formula.NewEngine())
	ps.schemaValidator = validation.NewSchemaValidator()
	ps.rollup = noopRollupProcessor{}

	_, _, err := ps.UpsertByExternalID(
		constants.WithSecurityContext(context.Background(), constants.SystemContext, "soft-deleted unique owner test"),
		objectName,
		extField,
		extValue,
		models.SObject{constants.FieldName: "incoming"},
		recordEntrypointUser(),
	)
	if err == nil {
		t.Fatal("UpsertByExternalID() error = nil, want existing duplicate conflict")
	}
	var conflict *appErrors.ConflictError
	if !errors.As(err, &conflict) {
		t.Fatalf("UpsertByExternalID() error = %T %v, want ConflictError", err, err)
	}
	if ports.IsUniqueFieldUpsertRace(err) {
		t.Fatalf("soft-deleted duplicate was marked replay-safe: %v", err)
	}
	if retryTx.attempts != 1 || repositoryInsertCalls != 0 {
		t.Fatalf("attempts/repository inserts = %d/%d, want 1/0", retryTx.attempts, repositoryInsertCalls)
	}
}

func TestUpsertByExternalIDDatabaseDuplicateWithoutLiveWinnerIsNotRetried(t *testing.T) {
	const (
		objectName = "Widget__c"
		extField   = "ExternalId__c"
		extValue   = "provider:object:soft-deleted-after-race"
	)
	testSchema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldName, Type: constants.FieldTypeString, Createable: true, Updateable: true},
			{APIName: extField, Type: constants.FieldTypeString, IsUnique: true, Createable: true, Updateable: true},
		},
	}
	lookupCalls := 0
	insertCalls := 0
	repo := &stubUpsertRecordRepo{
		findOneByFieldFn: func(_ context.Context, _ string, _ string, _ interface{}) (models.SObject, error) {
			lookupCalls++
			// Both the initial lookup and the post-duplicate proof exclude the
			// concurrently soft-deleted owner.
			return nil, nil
		},
		checkUniqueFn: func(context.Context, *models.ObjectMetadata, string, interface{}, string) (bool, error) {
			return false, nil
		},
		insertFn: func(context.Context, string, models.SObject) error {
			insertCalls++
			return fmt.Errorf(
				"%w: %w",
				ports.ErrDatabaseDuplicateKey,
				appErrors.NewConflictError(objectName, schema.GenerateUniqueIndexName(objectName, extField), ""),
			)
		},
	}
	ps := newUpsertTestPersistenceService(
		t,
		repo,
		&stubUpsertPermRepo{grantCreate: true, grantEdit: true},
		&stubUpsertMetaRepo{schema: testSchema},
		testSchema,
	)
	retryTx := &uniqueRaceRetryTxManager{}
	ps.txManager = retryTx
	ps.eventBus = &eventFeature.NoOpEventBus{}
	ps.validator = metadata.NewValidationService(formula.NewEngine())
	ps.schemaValidator = validation.NewSchemaValidator()
	ps.rollup = noopRollupProcessor{}

	_, _, err := ps.UpsertByExternalID(
		constants.WithSecurityContext(context.Background(), constants.SystemContext, "database duplicate live-winner proof test"),
		objectName,
		extField,
		extValue,
		models.SObject{constants.FieldName: "incoming"},
		recordEntrypointUser(),
	)
	if err == nil {
		t.Fatal("UpsertByExternalID() error = nil, want duplicate conflict without a live winner")
	}
	if ports.IsUniqueFieldUpsertRace(err) {
		t.Fatalf("database duplicate without a live winner was marked replay-safe: %v", err)
	}
	if retryTx.attempts != 1 || lookupCalls != 2 || insertCalls != 1 {
		t.Fatalf("attempts/lookups/inserts = %d/%d/%d, want 1/2/1", retryTx.attempts, lookupCalls, insertCalls)
	}
}

func TestUpsertByExternalIDWithAdoptionGuardsInsertConflictRoutesOwnershipConflict(t *testing.T) {
	const (
		objectName = "Lead"
		extField   = "Salesforce_Identity_Key__c"
		extValue   = "salesforce:org:Lead:source"
		guardField = "HubSpot_Id__c"
	)
	testSchema := &models.ObjectMetadata{
		APIName: objectName,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: constants.FieldName, Type: constants.FieldTypeString, Createable: true, Updateable: true},
			{APIName: extField, Type: constants.FieldTypeString, IsUnique: true, Createable: true, Updateable: true},
			{APIName: guardField, Type: constants.FieldTypeString, IsUnique: true, Createable: true, Updateable: true},
		},
	}
	lookupCalls := 0
	repositoryInserts := 0
	repo := &stubUpsertRecordRepo{
		findOneByFieldFn: func(context.Context, string, string, interface{}) (models.SObject, error) {
			lookupCalls++
			return nil, nil
		},
		checkUniqueFn: func(_ context.Context, _ *models.ObjectMetadata, field string, _ interface{}, _ string) (bool, error) {
			return strings.EqualFold(field, guardField), nil
		},
		insertFn: func(context.Context, string, models.SObject) error {
			repositoryInserts++
			return nil
		},
	}
	ps := newUpsertTestPersistenceService(
		t,
		repo,
		&stubUpsertPermRepo{grantCreate: true, grantEdit: true},
		&stubUpsertMetaRepo{schema: testSchema},
		testSchema,
	)
	retryTx := &uniqueRaceRetryTxManager{}
	ps.txManager = retryTx
	ps.eventBus = &eventFeature.NoOpEventBus{}
	ps.validator = metadata.NewValidationService(formula.NewEngine())
	ps.schemaValidator = validation.NewSchemaValidator()
	ps.rollup = noopRollupProcessor{}

	_, _, err := ps.UpsertByExternalIDWithAdoptionGuards(
		constants.WithSecurityContext(context.Background(), constants.SystemContext, "insert guard ownership conflict test"),
		objectName,
		extField,
		extValue,
		models.SObject{constants.FieldName: "incoming", guardField: "hs-owned"},
		[]string{guardField},
		recordEntrypointUser(),
	)
	if !ports.IsExternalIDUpsertAdoptionGuardConflict(err) {
		t.Fatalf("error = %v, want typed adoption-guard ownership conflict", err)
	}
	if appErrors.IsInternal(err) {
		t.Fatalf("proved insert guard conflict retained an internal envelope: %v", err)
	}
	if retryTx.attempts != 1 || lookupCalls != 1 || repositoryInserts != 0 {
		t.Fatalf("attempts/lookups/repository inserts = %d/%d/%d, want 1/1/0", retryTx.attempts, lookupCalls, repositoryInserts)
	}
}

func TestExactUniqueFieldConflictRejectsOtherUniqueIndexes(t *testing.T) {
	field := models.FieldMetadata{
		APIName:      "ExternalId__c",
		PhysicalName: "external_id",
		IsUnique:     true,
	}
	objectName := "Widget__c"

	if !exactUniqueFieldConflict(
		appErrors.NewConflictError(objectName, schema.GenerateUniqueIndexName(objectName, field.PhysicalName), ""),
		objectName,
		field,
	) {
		t.Fatal("exact generated external-ID index was not classified as replay-safe")
	}
	for _, conflictField := range []string{"PRIMARY", "OtherUnique__c", schema.GenerateUniqueIndexName(objectName, "OtherUnique__c")} {
		if exactUniqueFieldConflict(appErrors.NewConflictError(objectName, conflictField, ""), objectName, field) {
			t.Fatalf("unrelated conflict %q was classified as replay-safe", conflictField)
		}
	}

	exactConflict := fmt.Errorf(
		"%w: %w",
		ports.ErrDatabaseDuplicateKey,
		appErrors.NewConflictError(objectName, schema.GenerateUniqueIndexName(objectName, field.PhysicalName), ""),
	)
	insertObservation := &insertFinalImageObservation{}
	directExactConflict := insertObservation.wrapDirectInsertError(externalIDUpsertInsertRepositoryWrite, exactConflict)
	if replaySafeUniqueFieldConflict(directExactConflict, insertObservation, objectName, field, "before-save-retargeted", "requested") {
		t.Fatal("before-save-retargeted identity was classified as the requested identity race")
	}
	if replaySafeUniqueFieldConflict(
		appErrors.NewConflictError(objectName, field.APIName, "requested"),
		insertObservation,
		objectName,
		field,
		"requested",
		"requested",
	) {
		t.Fatal("business-layer uniqueness conflict was classified as a database create race")
	}
	if replaySafeUniqueFieldConflict(exactConflict, insertObservation, objectName, field, "requested", "requested") {
		t.Fatal("database duplicate without direct outer-Insert provenance was classified as a create race")
	}
	if externalIDUpsertDirectInsertConflict(exactConflict, insertObservation) {
		t.Fatal("database duplicate without direct outer-Insert provenance could route an adoption-guard conflict")
	}
	foreignObservation := &insertFinalImageObservation{}
	foreignDirectConflict := foreignObservation.wrapDirectInsertError(externalIDUpsertInsertRepositoryWrite, exactConflict)
	if replaySafeUniqueFieldConflict(foreignDirectConflict, insertObservation, objectName, field, "requested", "requested") {
		t.Fatal("nested/foreign Insert provenance was accepted by the outer upsert")
	}
	if externalIDUpsertDirectInsertConflict(foreignDirectConflict, insertObservation) {
		t.Fatal("nested/foreign Insert provenance could route an outer adoption-guard conflict")
	}
	if !replaySafeUniqueFieldConflict(directExactConflict, insertObservation, objectName, field, "requested", "requested") {
		t.Fatal("owned direct repository duplicate was not classified as a create race")
	}
}

func TestPrepareInsertRequest_ObjectPermissionsAllowsBeforeSaveRepairCandidate(t *testing.T) {
	schema := &models.ObjectMetadata{
		APIName: constants.TableObjectPermissions,
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldObjectPermissions_ParentID, Type: constants.FieldTypeReference, Createable: true},
			{APIName: constants.FieldObjectPermissions_SobjectType, Type: constants.FieldTypeString, Createable: true},
			{APIName: constants.FieldObjectPermissions_PermissionsRead, Type: constants.FieldTypeBoolean, Createable: true},
			{APIName: constants.FieldObjectPermissions_PermissionsCreate, Type: constants.FieldTypeBoolean, Createable: true},
			{APIName: constants.FieldObjectPermissions_PermissionsEdit, Type: constants.FieldTypeBoolean, Createable: true},
			{APIName: constants.FieldObjectPermissions_PermissionsDelete, Type: constants.FieldTypeBoolean, Createable: true},
			{APIName: constants.FieldObjectPermissions_PermissionsViewAllRecords, Type: constants.FieldTypeBoolean, Createable: true},
			{APIName: constants.FieldObjectPermissions_PermissionsModifyAllRecords, Type: constants.FieldTypeBoolean, Createable: true},
		},
	}
	ps := newUpsertTestPersistenceService(
		t,
		&stubUpsertRecordRepo{},
		&stubUpsertPermRepo{grantCreate: true},
		&stubUpsertMetaRepo{schema: schema},
		schema,
	)

	_, err := ps.prepareInsertRequest(
		constants.WithSecurityContext(context.Background(), constants.SystemContext, "objectpermissions insert validation test"),
		constants.TableObjectPermissions,
		models.SObject{
			constants.FieldObjectPermissions_ParentID:          "0PS000000000001",
			constants.FieldObjectPermissions_SobjectType:       "Account",
			constants.FieldObjectPermissions_PermissionsRead:   false,
			constants.FieldObjectPermissions_PermissionsCreate: true,
		},
		nil,
	)
	if err != nil {
		t.Fatalf("prepareInsertRequest() error = %v, want nil before before-save repair", err)
	}
}

// Compile-time interface assertions.
var _ ports.RecordRepository = (*stubUpsertRecordRepo)(nil)
var _ ports.PermissionRepository = (*stubUpsertPermRepo)(nil)
var _ ports.MetadataRepository = (*stubUpsertMetaRepo)(nil)