package controlplane

import (
	"context"
	"database/sql"
	"database/sql/driver"
	"errors"
	"fmt"
	"os"
	"os/exec"
	"path/filepath"
	"reflect"
	"regexp"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/go-sql-driver/mysql"
	"github.com/nexuscrm/analytics"
	"github.com/nexuscrm/automation"
	"github.com/nexuscrm/db"
	"github.com/nexuscrm/kernel/pkg/formula"
	"github.com/nexuscrm/kernel/pkg/logging"
	"github.com/nexuscrm/kernel/pkg/ports"
	"github.com/nexuscrm/metadata"
	"github.com/nexuscrm/persistence"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/formulastorage"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/pingcap-inc/nexuscrm-controlplane/packs"
)

type testRequestedPageLayoutComponent struct {
	kind        packs.ComponentKind
	businessKey string
	payload     []byte
}

type fakeConnectorInboundReconciler struct {
	reconcileCalls        int
	reconcileChangedCalls int
	reconcileErr          error
}

func (f *fakeConnectorInboundReconciler) Start() error { return nil }

func (f *fakeConnectorInboundReconciler) Reconcile(context.Context) error {
	f.reconcileCalls++
	return f.reconcileErr
}

func (f *fakeConnectorInboundReconciler) ReconcileChanged(ctx context.Context) error {
	f.reconcileChangedCalls++
	return f.reconcileErr
}

func (f *fakeConnectorInboundReconciler) Stop() error { return nil }

func TestReconcileConnectorInboundWorkersAfterPackLifecycle(t *testing.T) {
	fake := &fakeConnectorInboundReconciler{}
	runner := &Runner{
		SvcMgr: newTestServiceGraph(testServiceGraphConfig{ConnectorInbound: fake}),
	}

	if err := reconcileConnectorInboundWorkersAfterPackLifecycle(context.Background(), runner); err != nil {
		t.Fatalf("reconcileConnectorInboundWorkersAfterPackLifecycle: %v", err)
	}
	if fake.reconcileChangedCalls != 1 {
		t.Fatalf("reconcileChangedCalls = %d, want 1", fake.reconcileChangedCalls)
	}
	if fake.reconcileCalls != 0 {
		t.Fatalf("reconcileCalls = %d, want 0 full reconciles", fake.reconcileCalls)
	}

	fake.reconcileErr = errors.New("reconcile failed")
	err := reconcileConnectorInboundWorkersAfterPackLifecycle(context.Background(), runner)
	if err == nil || !strings.Contains(err.Error(), "reconcile failed") {
		t.Fatalf("error = %v, want reconcile failed", err)
	}
}

func TestSourceBackedInstallAutomationBoundarySkipsFlowMaintenanceWithoutFlowOwnership(t *testing.T) {
	rawDB, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer rawDB.Close()
	mock.ExpectQuery("(?s)SELECT 1.*FROM FlowDefinition.*OwnerPack").
		WithArgs("jira-like-sample").
		WillReturnRows(sqlmock.NewRows([]string{"marker"}))

	pack := testControlplaneSourceBackedPack(t)
	err = runSourceBackedInstallPhaseBoundaryWithRuntime(
		context.Background(), database.NewConnectionFromDB(rawDB), newTestServiceGraph(testServiceGraphConfig{}), pack,
		packs.InstallPlanPhaseAutomation, "test",
	)
	if err != nil {
		t.Fatalf("automation phase boundary without flow ownership: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet SQL expectations: %v", err)
	}
}

func TestSourceBackedInstallAutomationBoundaryMaintainsPreviouslyOwnedFlows(t *testing.T) {
	rawDB, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer rawDB.Close()
	mock.ExpectQuery("(?s)SELECT 1.*FROM FlowDefinition.*OwnerPack").
		WithArgs("jira-like-sample").
		WillReturnRows(sqlmock.NewRows([]string{"marker"}).AddRow(1))

	pack := testControlplaneSourceBackedPack(t)
	err = runSourceBackedInstallPhaseBoundaryWithRuntime(
		context.Background(), database.NewConnectionFromDB(rawDB), newTestServiceGraph(testServiceGraphConfig{}), pack,
		packs.InstallPlanPhaseAutomation, "test",
	)
	if err == nil || !strings.Contains(err.Error(), "metadata service is required for flow runtime maintenance") {
		t.Fatalf("automation phase boundary error = %v, want previously owned flows to require runtime maintenance", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet SQL expectations: %v", err)
	}
}

func (c testRequestedPageLayoutComponent) Kind() packs.ComponentKind { return c.kind }
func (c testRequestedPageLayoutComponent) PackName() string          { return "test-pack" }
func (c testRequestedPageLayoutComponent) BusinessKey() string       { return c.businessKey }
func (c testRequestedPageLayoutComponent) Manageability() packs.Manageability {
	return packs.ManageabilityDefaultsOnly
}
func (c testRequestedPageLayoutComponent) ExpectedPayload() ([]byte, error) {
	return append([]byte(nil), c.payload...), nil
}
func (c testRequestedPageLayoutComponent) ExpectedProjection() (any, error) { return nil, nil }
func (c testRequestedPageLayoutComponent) LiveProjection(context.Context, packs.VerifyDeps) (any, error) {
	return nil, nil
}
func (c testRequestedPageLayoutComponent) Apply(context.Context, packs.ApplyDeps) error { return nil }

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

	providerDir := writeFormulaFunctionProviderPackSource(t, "formula-provider-pack")
	providerPack, err := packs.ResolveInstalledSourceBackedPack(
		"formula-provider-pack",
		packs.AppPack,
		string(packs.PackSourceKindLocalPath),
		providerDir,
	)
	if err != nil {
		t.Fatalf("resolve provider pack: %v", err)
	}
	providerHash, err := packs.PackPayloadHash(providerPack)
	if err != nil {
		t.Fatalf("provider payload hash: %v", err)
	}
	providerDecl, err := packs.BuildPersistedRuntimeDeclaration(providerPack)
	if err != nil {
		t.Fatalf("provider runtime declaration: %v", err)
	}
	expectActiveInstalledPackRuntimeDeclarationWithFormulaBundles(mock, providerPack, providerDir, providerHash, providerDecl)

	consumerDir := writeFormulaFunctionConsumerPackSource(t, "formula-consumer-pack")
	report, _, err := validateInstallablePackSourceForInstall(context.Background(), db, "local_path:"+consumerDir)
	if err != nil {
		t.Fatalf("validateInstallablePackSourceForInstall: %v", err)
	}
	if report.HasErrors() {
		t.Fatalf("validateInstallablePackSourceForInstall diagnostics:\n%s", report.Error())
	}
	if report.Pack == nil {
		t.Fatal("validateInstallablePackSourceForInstall did not construct an installable pack")
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	sourceDir := writeFormulaFunctionSelfConsumingPackSource(t, "formula-self-pack")

	report, _, err := validateInstallablePackSourceForInstall(context.Background(), db, "local_path:"+sourceDir)
	if err != nil {
		t.Fatalf("validateInstallablePackSourceForInstall: %v", err)
	}
	if report.HasErrors() {
		t.Fatalf("validateInstallablePackSourceForInstall diagnostics:\n%s", report.Error())
	}
	if report.Pack == nil {
		t.Fatal("validateInstallablePackSourceForInstall did not construct an installable pack")
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestRestoreInstalledRuntimeMetadataAfterInstallFailureClearsCandidateFormulaFunctionBundles(t *testing.T) {
	if err := formula.RegisterRuntimeFunctionBundles(nil); err != nil {
		t.Fatalf("clear runtime formula bundles before test: %v", err)
	}
	t.Cleanup(func() {
		_ = formula.RegisterRuntimeFunctionBundles(nil)
	})

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

	candidateBundle, err := formula.ParseFunctionBundlePayload([]byte(testFormulaFunctionBundlePayload("CANDIDATE_PREFIX")))
	if err != nil {
		t.Fatalf("parse candidate bundle: %v", err)
	}
	if err := formula.RegisterRuntimeFunctionBundles([]formula.FunctionBundle{candidateBundle}); err != nil {
		t.Fatalf("publish candidate runtime bundle: %v", err)
	}
	if got, err := formula.NewEngine().Evaluate(`CANDIDATE_PREFIX(Name)`, &formula.Context{Record: map[string]interface{}{"Name": "alpha"}}); err != nil || got != "alpha-probe" {
		t.Fatalf("candidate function before restore = (%v, %v), want alpha-probe", got, err)
	}

	mode := "active"
	returnType := models.FieldType(constants.FieldTypeString)
	formulaExpr := "CANDIDATE_PREFIX(Name)"
	schema := &models.ObjectMetadata{
		APIName: "CandidateProbe__c",
		Fields: []models.FieldMetadata{
			{APIName: "Id", Type: constants.FieldTypeString},
			{APIName: "Name", Type: constants.FieldTypeString, IsNameField: true},
			{
				APIName:     "CandidateEcho__c",
				Type:        constants.FieldTypeFormula,
				Formula:     &formulaExpr,
				FormulaMode: &mode,
				ReturnType:  &returnType,
			},
		},
	}
	metaSvc := metadata.NewMetadataService(&activeFormulaSmokeMetadataRepo{schemas: []*models.ObjectMetadata{schema}}, nil, nil)
	fvSvc := analytics.NewFormulaViewService(db, metaSvc, logging.Default)
	runner := &Runner{
		SvcMgr: newTestServiceGraph(testServiceGraphConfig{
			FormulaView: fvSvc,
		}),
	}
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT COLUMN_NAME FROM information_schema.COLUMNS
			 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?
			 ORDER BY ORDINAL_POSITION`)).
		WithArgs("candidateprobe__c").
		WillReturnRows(sqlmock.NewRows([]string{"COLUMN_NAME"}).
			AddRow("Id").
			AddRow("Name"))
	if ddl, err := fvSvc.PreviewDDL(context.Background(), "CandidateProbe__c"); err != nil || !strings.Contains(ddl, "'-probe'") {
		t.Fatalf("candidate formula view compile before restore = (%q, %v), want bundle-backed DDL", ddl, err)
	}

	expectNoActiveInstalledPackRuntimeDeclarationRowsWithFormulaBundles(mock)
	if err := restoreInstalledRuntimeMetadataAfterInstallFailure(context.Background(), runner, db); err != nil {
		t.Fatalf("restoreInstalledRuntimeMetadataAfterInstallFailure: %v", err)
	}
	if got, err := formula.NewEngine().Evaluate(`CANDIDATE_PREFIX(Name)`, &formula.Context{Record: map[string]interface{}{"Name": "alpha"}}); err == nil {
		t.Fatalf("candidate function after restore = %v, nil error; want unknown function", got)
	}
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT COLUMN_NAME FROM information_schema.COLUMNS
			 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?
			 ORDER BY ORDINAL_POSITION`)).
		WithArgs("candidateprobe__c").
		WillReturnRows(sqlmock.NewRows([]string{"COLUMN_NAME"}).
			AddRow("Id").
			AddRow("Name"))
	if ddl, err := fvSvc.PreviewDDL(context.Background(), "CandidateProbe__c"); err == nil {
		t.Fatalf("candidate formula view compile after restore = %q, nil error; want unknown function", ddl)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	providerDir := writeFormulaFunctionProviderPackSource(t, "formula-provider-pack")
	providerPack, err := packs.ResolveInstalledSourceBackedPack(
		"formula-provider-pack",
		packs.AppPack,
		string(packs.PackSourceKindLocalPath),
		providerDir,
	)
	if err != nil {
		t.Fatalf("resolve provider pack: %v", err)
	}
	providerHash, err := packs.PackPayloadHash(providerPack)
	if err != nil {
		t.Fatalf("provider payload hash: %v", err)
	}
	providerDecl, err := packs.BuildPersistedRuntimeDeclaration(providerPack)
	if err != nil {
		t.Fatalf("provider runtime declaration: %v", err)
	}
	expectActiveInstalledPackRuntimeDeclarationWithFormulaBundles(mock, providerPack, providerDir, providerHash, providerDecl)

	consumerDir := writeFormulaFunctionConsumerPackSource(t, "formula-consumer-pack")
	if err := validateSourceDiagnosticsForOperationWithInstallContext(context.Background(), db, "reapply-pack", "local_path:"+consumerDir); err != nil {
		t.Fatalf("validateSourceDiagnosticsForOperationWithInstallContext: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	providerDir := writeFormulaFunctionProviderPackSource(t, "formula-provider-pack")
	providerPack, err := packs.ResolveInstalledSourceBackedPack(
		"formula-provider-pack",
		packs.AppPack,
		string(packs.PackSourceKindLocalPath),
		providerDir,
	)
	if err != nil {
		t.Fatalf("resolve provider pack: %v", err)
	}
	providerHash, err := packs.PackPayloadHash(providerPack)
	if err != nil {
		t.Fatalf("provider payload hash: %v", err)
	}
	providerDecl, err := packs.BuildPersistedRuntimeDeclaration(providerPack)
	if err != nil {
		t.Fatalf("provider runtime declaration: %v", err)
	}
	staleProviderDecl := staleFormulaBundleRuntimeDeclaration{persistedRuntimeDeclarationJSONView: providerDecl}

	consumerDir := writeFormulaFunctionConsumerPackSource(t, "formula-consumer-pack")
	consumerReport := packs.ValidateInstallablePackSourceWithRuntimeFormulaFunctionBundles(
		"local_path:"+consumerDir,
		packs.DefaultPackSourceOptions(),
		providerPack.RuntimeFormulaFunctionBundles(),
	)
	if consumerReport.HasErrors() {
		t.Fatalf("consumer source diagnostics:\n%s", consumerReport.Error())
	}
	consumerPack := consumerReport.Pack
	if consumerPack == nil {
		t.Fatal("consumer source did not produce an installable pack")
	}

	runner := &Runner{DB: database.NewConnectionFromDB(db)}
	consumerRow := installedPackRow{
		Name:       "formula-consumer-pack",
		Category:   string(packs.AppPack),
		Status:     installedPackStatusActive,
		SourceKind: sql.NullString{Valid: true, String: string(packs.PackSourceKindLocalPath)},
		SourceURI:  sql.NullString{Valid: true, String: consumerDir},
	}
	providerRow := installedPackRow{
		Name:       "formula-provider-pack",
		Category:   string(packs.AppPack),
		Status:     installedPackStatusActive,
		SourceKind: sql.NullString{Valid: true, String: string(packs.PackSourceKindLocalPath)},
		SourceURI:  sql.NullString{Valid: true, String: providerDir},
	}

	expectReapplyInstalledPackSourceRow(mock, consumerRow)
	expectReapplyInstalledPackSourceRow(mock, consumerRow)
	expectActiveInstalledPackRuntimeDeclarationWithFormulaBundles(mock, providerPack, providerDir, providerHash, staleProviderDecl)
	expectActiveInstalledPackRuntimeDeclarationWithFormulaBundles(mock, providerPack, providerDir, providerHash, staleProviderDecl)
	expectReapplyInstalledPackSourceRow(mock, providerRow)
	expectInstalledPackStateLookup(mock, providerRow.Name, installedPackStatusActive, 0)
	mock.ExpectQuery(regexp.QuoteMeta("SELECT GET_LOCK('nexuscrm_controlplane_apply', 600)")).
		WillReturnRows(sqlmock.NewRows([]string{"GET_LOCK"}).AddRow(1))
	expectApplyAllMarkerWrite(mock, "invalidated", "stale")
	mock.ExpectQuery(regexp.QuoteMeta("SELECT RELEASE_LOCK('nexuscrm_controlplane_apply')")).
		WillReturnRows(sqlmock.NewRows([]string{"RELEASE_LOCK"}).AddRow(1))

	origEnsure := ensureInstallPackSchemaReadinessForInstall
	origValidate := validateLivePackDependenciesForInstall
	origOwnerless := samePackAllowedOwnerlessObjectsForInstall
	origApply := applyResolvedSourceBackedPackForInstall
	origList := listInstalledAppPacksForReconcile
	origResolve := sourceBackedPackFromInstalledRowForReconcile
	origVerifyHash := verifyInstalledPackHashStateForReconcile
	defer func() {
		ensureInstallPackSchemaReadinessForInstall = origEnsure
		validateLivePackDependenciesForInstall = origValidate
		samePackAllowedOwnerlessObjectsForInstall = origOwnerless
		applyResolvedSourceBackedPackForInstall = origApply
		listInstalledAppPacksForReconcile = origList
		sourceBackedPackFromInstalledRowForReconcile = origResolve
		verifyInstalledPackHashStateForReconcile = origVerifyHash
	}()

	ensureInstallPackSchemaReadinessForInstall = func(context.Context, *Runner) error { return nil }
	validateLivePackDependenciesForInstall = func(context.Context, *sql.DB, *packs.SourceBackedPack) error { return nil }
	samePackAllowedOwnerlessObjectsForInstall = func(context.Context, *sql.DB, *packs.SourceBackedPack, string, InstallPackOptions) ([]string, error) {
		return nil, nil
	}
	listInstalledAppPacksForReconcile = func(context.Context, *sql.DB) ([]installedPackRow, error) {
		return []installedPackRow{consumerRow, providerRow}, nil
	}
	sourceBackedPackFromInstalledRowForReconcile = func(_ context.Context, _ *sql.DB, row installedPackRow, _ ...packs.SourceBackedPackOption) (*packs.SourceBackedPack, error) {
		switch row.Name {
		case consumerRow.Name:
			return consumerPack, nil
		case providerRow.Name:
			return providerPack, nil
		default:
			t.Fatalf("unexpected row resolved for reapply drift scan: %s", row.Name)
			return nil, nil
		}
	}
	verifyInstalledPackHashStateForReconcile = func(row installedPackRow, _ packs.Pack) error {
		if row.Name == providerRow.Name {
			return &HashDriftError{PackName: row.Name, StoredHash: "old", CurrentHash: "new"}
		}
		return nil
	}

	var applied string
	applyResolvedSourceBackedPackForInstall = func(_ context.Context, _ *Runner, pack *packs.SourceBackedPack, preflightOverrides *installPackPreflightOverrides, _ InstallPackOptions) error {
		if pack == nil {
			t.Fatal("apply pack = nil")
		}
		applied = pack.Name()
		if _, ok := preflightOverrides.activeDriftedPacks["formula-provider-pack"]; !ok {
			t.Fatalf("activeDriftedPacks = %#v, want formula-provider-pack override", preflightOverrides.activeDriftedPacks)
		}
		return nil
	}

	if err := ReapplyPackFromSource(context.Background(), runner, "local_path:"+consumerDir, InstallPackOptions{}); err != nil {
		t.Fatalf("ReapplyPackFromSource: %v", err)
	}
	if applied != "formula-consumer-pack" {
		t.Fatalf("applied pack = %q, want formula-consumer-pack", applied)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestRequestedPageLayoutOwnershipKeysPreserveDefaultPayloadLayoutName(t *testing.T) {
	keys := requestedPageLayoutOwnershipKeys([]packs.PackComponent{
		testRequestedPageLayoutComponent{
			kind:        packs.ComponentKindPageLayout,
			businessKey: "Quote/default",
			payload:     []byte(`{"objectApiName":"Quote","layoutName":"Default Layout","isDefault":true,"sections":[]}`),
		},
	})
	if len(keys) != 1 {
		t.Fatalf("len(keys) = %d, want 1", len(keys))
	}
	if got, want := keys[0].ObjectAPIName, "Quote"; got != want {
		t.Fatalf("ObjectAPIName = %q, want %q", got, want)
	}
	if got, want := keys[0].LayoutName, "Default Layout"; got != want {
		t.Fatalf("LayoutName = %q, want %q", got, want)
	}
	if !keys[0].IsDefault {
		t.Fatal("IsDefault = false, want true")
	}
}

func TestReapplyPackFromSourceUnlockedThreadsOperatorOverridesAndVerify(t *testing.T) {
	sourceDir, _ := writeFieldDeclarationSource(t, "Subject")

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

	runner := &Runner{DB: database.NewConnectionFromDB(db)}

	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision, HostRequirementsDigest FROM _System_InstalledPack").
		WithArgs("jira-like-sample").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility", "DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			"jira-like-sample",
			string(packs.AppPack),
			installedPackStatusActive,
			nil,
			nil,
			nil,
			nil,
			nil,
			nil,
			string(packs.PackSourceKindLocalPath),
			sourceDir,
			nil,
			nil,
		))

	origEnsure := ensureInstallPackSchemaReadinessForInstall
	origValidate := validateLivePackDependenciesForInstall
	origOwnerless := samePackAllowedOwnerlessObjectsForInstall
	origApply := applyResolvedSourceBackedPackForInstall
	origVerify := verifyInstalledPackByNameForInstall
	origList := listInstalledAppPacksForReconcile
	defer func() {
		ensureInstallPackSchemaReadinessForInstall = origEnsure
		validateLivePackDependenciesForInstall = origValidate
		samePackAllowedOwnerlessObjectsForInstall = origOwnerless
		applyResolvedSourceBackedPackForInstall = origApply
		verifyInstalledPackByNameForInstall = origVerify
		listInstalledAppPacksForReconcile = origList
	}()

	ensureInstallPackSchemaReadinessForInstall = func(context.Context, *Runner) error { return nil }
	validateLivePackDependenciesForInstall = func(context.Context, *sql.DB, *packs.SourceBackedPack) error { return nil }
	samePackAllowedOwnerlessObjectsForInstall = func(context.Context, *sql.DB, *packs.SourceBackedPack, string, InstallPackOptions) ([]string, error) {
		return nil, nil
	}
	listInstalledAppPacksForReconcile = func(context.Context, *sql.DB) ([]installedPackRow, error) {
		return nil, nil
	}

	var sawSkipAttribution bool
	var sawSkipPolymorphic bool
	var sawRepairLegacyUnique bool
	applyResolvedSourceBackedPackForInstall = func(_ context.Context, _ *Runner, pack *packs.SourceBackedPack, preflightOverrides *installPackPreflightOverrides, options InstallPackOptions) error {
		if pack == nil || pack.Name() != "jira-like-sample" {
			t.Fatalf("apply pack = %#v", pack)
		}
		sawSkipAttribution = preflightOverrides != nil && preflightOverrides.skipAttributionPreflight
		sawSkipPolymorphic = options.SkipPolymorphicDiscriminatorBackfill
		sawRepairLegacyUnique = options.RepairLegacyUniqueDuplicates
		return nil
	}

	var verifiedName string
	var verifiedDeep bool
	var verifiedRunner *Runner
	verifyInstalledPackByNameForInstall = func(_ context.Context, got *Runner, name string, deep bool) error {
		verifiedRunner = got
		verifiedName = name
		verifiedDeep = deep
		return nil
	}

	err = reapplyPackFromSourceUnlocked(context.Background(), runner, "local_path:"+sourceDir, InstallPackOptions{
		SkipAttributionPreflight:             true,
		SkipPolymorphicDiscriminatorBackfill: true,
		RepairLegacyUniqueDuplicates:         true,
		VerifyAfterApply:                     true,
	})
	if err != nil {
		t.Fatalf("reapplyPackFromSourceUnlocked: %v", err)
	}
	if !sawSkipAttribution {
		t.Fatal("skip attribution override was not forwarded to apply path")
	}
	if !sawSkipPolymorphic {
		t.Fatal("skip polymorphic backfill override was not forwarded to apply path")
	}
	if !sawRepairLegacyUnique {
		t.Fatal("repair legacy unique override was not forwarded to apply path")
	}
	if verifiedName != "jira-like-sample" || !verifiedDeep {
		t.Fatalf("verify call = (%q, %v), want (jira-like-sample, true)", verifiedName, verifiedDeep)
	}
	if verifiedRunner != runner {
		t.Fatalf("verify runner = %p, want %p", verifiedRunner, runner)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	pack := testControlplaneSourceBackedPackWithPermission(t)
	mock.ExpectQuery(regexp.QuoteMeta(`
SELECT Id
FROM ProcessDefinition
WHERE COALESCE(OwnerPack, '') = ?
ORDER BY Id
LIMIT 1`)).
		WithArgs(pack.Name()).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))

	mock.ExpectQuery(regexp.QuoteMeta("SELECT `ApiName`, COALESCE(`OwnerPack`, ''), `IsActive` FROM `_System_Permission` WHERE `ApiName` IN (?) ORDER BY `ApiName`")).
		WithArgs("RunReports").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "OwnerPack", "IsActive"}).
			AddRow("RunReports", "other-pack", 1))

	err = validateSourceBackedPackInstallPreflights(
		context.Background(), packs.ApplyDeps{DB: db}, packs.ComponentKindRegistrationSet{},
		pack, &installPackPreflightOverrides{skipAttributionPreflight: true},
	)
	if err == nil {
		t.Fatal("shared install preflight error = nil, want permission owner refusal")
	}
	for _, want := range []string{`source-backed permission owner preflight`, `permission RunReports OwnerPack = "other-pack"`} {
		if !strings.Contains(err.Error(), want) {
			t.Fatalf("error = %v, want substring %q", err, want)
		}
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestReapplyPackFromSourceUnlockedToleratesDriftedActivePeerWithoutApplyingPeer(t *testing.T) {
	sourceDir, requestedPack := writeNamedFieldDeclarationSource(t, "aaa-pack", "TargetObject", "TargetField__c")
	peerDir, peerPack := writeNamedFieldDeclarationSource(t, "zzz-peer", "PeerObject", "PeerField__c")

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

	runner := &Runner{DB: database.NewConnectionFromDB(db)}

	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision, HostRequirementsDigest FROM _System_InstalledPack").
		WithArgs("aaa-pack").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility", "DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			"aaa-pack",
			string(packs.AppPack),
			installedPackStatusActive,
			nil,
			nil,
			nil,
			nil,
			nil,
			nil,
			string(packs.PackSourceKindLocalPath),
			sourceDir,
			nil,
			nil,
		))

	requestedRow := installedPackRow{
		Name:       "aaa-pack",
		Category:   string(packs.AppPack),
		Status:     installedPackStatusActive,
		SourceKind: sql.NullString{Valid: true, String: string(packs.PackSourceKindLocalPath)},
		SourceURI:  sql.NullString{Valid: true, String: sourceDir},
	}
	peerRow := installedPackRow{
		Name:       "zzz-peer",
		Category:   string(packs.AppPack),
		Status:     installedPackStatusActive,
		SourceKind: sql.NullString{Valid: true, String: string(packs.PackSourceKindLocalPath)},
		SourceURI:  sql.NullString{Valid: true, String: peerDir},
	}

	origEnsure := ensureInstallPackSchemaReadinessForInstall
	origValidate := validateLivePackDependenciesForInstall
	origOwnerless := samePackAllowedOwnerlessObjectsForInstall
	origApply := applyResolvedSourceBackedPackForInstall
	origList := listInstalledAppPacksForReconcile
	origResolve := sourceBackedPackFromInstalledRowForReconcile
	origVerifyHash := verifyInstalledPackHashStateForReconcile
	defer func() {
		ensureInstallPackSchemaReadinessForInstall = origEnsure
		validateLivePackDependenciesForInstall = origValidate
		samePackAllowedOwnerlessObjectsForInstall = origOwnerless
		applyResolvedSourceBackedPackForInstall = origApply
		listInstalledAppPacksForReconcile = origList
		sourceBackedPackFromInstalledRowForReconcile = origResolve
		verifyInstalledPackHashStateForReconcile = origVerifyHash
	}()

	ensureInstallPackSchemaReadinessForInstall = func(context.Context, *Runner) error { return nil }
	validateLivePackDependenciesForInstall = func(context.Context, *sql.DB, *packs.SourceBackedPack) error { return nil }
	samePackAllowedOwnerlessObjectsForInstall = func(context.Context, *sql.DB, *packs.SourceBackedPack, string, InstallPackOptions) ([]string, error) {
		return nil, nil
	}
	listInstalledAppPacksForReconcile = func(context.Context, *sql.DB) ([]installedPackRow, error) {
		return []installedPackRow{requestedRow, peerRow}, nil
	}
	sourceBackedPackFromInstalledRowForReconcile = func(_ context.Context, _ *sql.DB, row installedPackRow, _ ...packs.SourceBackedPackOption) (*packs.SourceBackedPack, error) {
		switch row.Name {
		case "aaa-pack":
			return requestedPack, nil
		case "zzz-peer":
			return peerPack, nil
		default:
			t.Fatalf("unexpected row resolved for reapply drift scan: %s", row.Name)
			return nil, nil
		}
	}
	verifyInstalledPackHashStateForReconcile = func(row installedPackRow, _ packs.Pack) error {
		if row.Name == "zzz-peer" {
			return &HashDriftError{PackName: row.Name, StoredHash: "old", CurrentHash: "new"}
		}
		return nil
	}

	applyCalls := []string{}
	applyResolvedSourceBackedPackForInstall = func(_ context.Context, _ *Runner, pack *packs.SourceBackedPack, preflightOverrides *installPackPreflightOverrides, _ InstallPackOptions) error {
		if pack == nil {
			t.Fatal("apply pack = nil")
		}
		applyCalls = append(applyCalls, pack.Name())
		if pack.Name() != "aaa-pack" {
			t.Fatalf("apply pack = %q, want only aaa-pack", pack.Name())
		}
		if preflightOverrides == nil {
			t.Fatal("preflight overrides = nil, want drifted peer override")
		}
		if _, ok := preflightOverrides.activeDriftedPacks["zzz-peer"]; !ok {
			t.Fatalf("activeDriftedPacks = %#v, want zzz-peer override", preflightOverrides.activeDriftedPacks)
		}
		return nil
	}

	if err := reapplyPackFromSourceUnlocked(context.Background(), runner, "local_path:"+sourceDir, InstallPackOptions{}); err != nil {
		t.Fatalf("reapplyPackFromSourceUnlocked: %v", err)
	}
	if got := strings.Join(applyCalls, ","); got != "aaa-pack" {
		t.Fatalf("apply calls = %q, want aaa-pack only", got)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestReapplyPackFromSourceInvalidatesApplyAllMarkerAndRunsApplyPath(t *testing.T) {
	sourceDir, _ := writeFieldDeclarationSource(t, "Subject")

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

	runner := &Runner{DB: database.NewConnectionFromDB(db)}

	expectReapplyInstalledPackSourceRow(mock, installedPackRow{
		Name:       "jira-like-sample",
		Category:   string(packs.AppPack),
		Status:     installedPackStatusActive,
		SourceKind: sql.NullString{Valid: true, String: string(packs.PackSourceKindLocalPath)},
		SourceURI:  sql.NullString{Valid: true, String: sourceDir},
	})
	mock.ExpectQuery(regexp.QuoteMeta("SELECT GET_LOCK('nexuscrm_controlplane_apply', 600)")).
		WillReturnRows(sqlmock.NewRows([]string{"GET_LOCK"}).AddRow(1))
	expectApplyAllMarkerWrite(mock, "invalidated", "stale")
	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision, HostRequirementsDigest FROM _System_InstalledPack").
		WithArgs("jira-like-sample").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility", "DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			"jira-like-sample",
			string(packs.AppPack),
			installedPackStatusActive,
			nil,
			nil,
			nil,
			nil,
			nil,
			nil,
			string(packs.PackSourceKindLocalPath),
			sourceDir,
			nil,
			nil,
		))
	mock.ExpectQuery(regexp.QuoteMeta("SELECT RELEASE_LOCK('nexuscrm_controlplane_apply')")).
		WillReturnRows(sqlmock.NewRows([]string{"RELEASE_LOCK"}).AddRow(1))

	origEnsure := ensureInstallPackSchemaReadinessForInstall
	origValidate := validateLivePackDependenciesForInstall
	origOwnerless := samePackAllowedOwnerlessObjectsForInstall
	origApply := applyResolvedSourceBackedPackForInstall
	origList := listInstalledAppPacksForReconcile
	defer func() {
		ensureInstallPackSchemaReadinessForInstall = origEnsure
		validateLivePackDependenciesForInstall = origValidate
		samePackAllowedOwnerlessObjectsForInstall = origOwnerless
		applyResolvedSourceBackedPackForInstall = origApply
		listInstalledAppPacksForReconcile = origList
	}()

	ensureInstallPackSchemaReadinessForInstall = func(context.Context, *Runner) error { return nil }
	validateLivePackDependenciesForInstall = func(context.Context, *sql.DB, *packs.SourceBackedPack) error { return nil }
	samePackAllowedOwnerlessObjectsForInstall = func(context.Context, *sql.DB, *packs.SourceBackedPack, string, InstallPackOptions) ([]string, error) {
		return nil, nil
	}
	listInstalledAppPacksForReconcile = func(context.Context, *sql.DB) ([]installedPackRow, error) {
		return nil, nil
	}

	var sawApply bool
	applyResolvedSourceBackedPackForInstall = func(_ context.Context, _ *Runner, pack *packs.SourceBackedPack, _ *installPackPreflightOverrides, _ InstallPackOptions) error {
		if pack == nil || pack.Name() != "jira-like-sample" {
			t.Fatalf("apply pack = %#v", pack)
		}
		sawApply = true
		return nil
	}

	if err := ReapplyPackFromSource(context.Background(), runner, "local_path:"+sourceDir, InstallPackOptions{}); err != nil {
		t.Fatalf("ReapplyPackFromSource: %v", err)
	}
	if !sawApply {
		t.Fatal("reapply-pack did not reach the pack-scoped apply/finalize path")
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestReapplyPackFromSourceFailureMarksApplyAllFailed(t *testing.T) {
	sourceDir, _ := writeFieldDeclarationSource(t, "Subject")

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

	runner := &Runner{DB: database.NewConnectionFromDB(db)}

	expectReapplyInstalledPackSourceRow(mock, installedPackRow{
		Name:       "jira-like-sample",
		Category:   string(packs.AppPack),
		Status:     installedPackStatusActive,
		SourceKind: sql.NullString{Valid: true, String: string(packs.PackSourceKindLocalPath)},
		SourceURI:  sql.NullString{Valid: true, String: sourceDir},
	})
	mock.ExpectQuery(regexp.QuoteMeta("SELECT GET_LOCK('nexuscrm_controlplane_apply', 600)")).
		WillReturnRows(sqlmock.NewRows([]string{"GET_LOCK"}).AddRow(1))
	expectApplyAllMarkerWrite(mock, "invalidated", "stale")
	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision, HostRequirementsDigest FROM _System_InstalledPack").
		WithArgs("jira-like-sample").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility", "DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			"jira-like-sample",
			string(packs.AppPack),
			installedPackStatusActive,
			nil,
			nil,
			nil,
			nil,
			nil,
			nil,
			string(packs.PackSourceKindLocalPath),
			sourceDir,
			nil,
			nil,
		))
	expectApplyAllMarkerWrite(mock, "failed", "failed")
	mock.ExpectQuery(regexp.QuoteMeta("SELECT RELEASE_LOCK('nexuscrm_controlplane_apply')")).
		WillReturnRows(sqlmock.NewRows([]string{"RELEASE_LOCK"}).AddRow(1))

	origEnsure := ensureInstallPackSchemaReadinessForInstall
	origValidate := validateLivePackDependenciesForInstall
	origOwnerless := samePackAllowedOwnerlessObjectsForInstall
	origApply := applyResolvedSourceBackedPackForInstall
	origList := listInstalledAppPacksForReconcile
	defer func() {
		ensureInstallPackSchemaReadinessForInstall = origEnsure
		validateLivePackDependenciesForInstall = origValidate
		samePackAllowedOwnerlessObjectsForInstall = origOwnerless
		applyResolvedSourceBackedPackForInstall = origApply
		listInstalledAppPacksForReconcile = origList
	}()

	ensureInstallPackSchemaReadinessForInstall = func(context.Context, *Runner) error { return nil }
	validateLivePackDependenciesForInstall = func(context.Context, *sql.DB, *packs.SourceBackedPack) error { return nil }
	samePackAllowedOwnerlessObjectsForInstall = func(context.Context, *sql.DB, *packs.SourceBackedPack, string, InstallPackOptions) ([]string, error) {
		return nil, nil
	}
	listInstalledAppPacksForReconcile = func(context.Context, *sql.DB) ([]installedPackRow, error) {
		return nil, nil
	}
	applyResolvedSourceBackedPackForInstall = func(context.Context, *Runner, *packs.SourceBackedPack, *installPackPreflightOverrides, InstallPackOptions) error {
		return errors.New("partial reapply failed")
	}

	err = ReapplyPackFromSource(context.Background(), runner, "local_path:"+sourceDir, InstallPackOptions{})
	if err == nil {
		t.Fatal("ReapplyPackFromSource error = nil, want apply failure")
	}
	if !strings.Contains(err.Error(), "partial reapply failed") {
		t.Fatalf("error = %v, want original apply failure", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestReapplyPackLockMarksFailureOnSelectedLiveDB(t *testing.T) {
	lockDB, lockMock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("lock sqlmock: %v", err)
	}
	defer lockDB.Close()

	markerDB, markerMock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("marker sqlmock: %v", err)
	}
	defer markerDB.Close()

	lockMock.ExpectQuery(regexp.QuoteMeta("SELECT GET_LOCK('nexuscrm_controlplane_apply', 600)")).
		WillReturnRows(sqlmock.NewRows([]string{"GET_LOCK"}).AddRow(1))
	expectApplyAllMarkerWrite(lockMock, "invalidated", "stale")
	lockMock.ExpectQuery(regexp.QuoteMeta("SELECT RELEASE_LOCK('nexuscrm_controlplane_apply')")).
		WillReturnRows(sqlmock.NewRows([]string{"RELEASE_LOCK"}).AddRow(1))

	expectApplyAllMarkerWrite(markerMock, "failed", "failed")

	err = withReapplyPackLock(context.Background(), lockDB, func() *sql.DB {
		return markerDB
	}, func() error {
		return errors.New("reapply failed after reconnect")
	})
	if err == nil {
		t.Fatal("withReapplyPackLock error = nil, want reapply failure")
	}
	if !strings.Contains(err.Error(), "reapply failed after reconnect") {
		t.Fatalf("error = %v, want original reapply failure", err)
	}
	if err := lockMock.ExpectationsWereMet(); err != nil {
		t.Fatalf("lock expectations: %v", err)
	}
	if err := markerMock.ExpectationsWereMet(); err != nil {
		t.Fatalf("marker expectations: %v", err)
	}
}

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

	mock.ExpectQuery(regexp.QuoteMeta("SELECT GET_LOCK('nexuscrm_controlplane_apply', 600)")).
		WillReturnRows(sqlmock.NewRows([]string{"GET_LOCK"}).AddRow(1))
	expectApplyAllMarkerWrite(mock, "invalidated", "stale")
	mock.ExpectQuery(regexp.QuoteMeta("SELECT RELEASE_LOCK('nexuscrm_controlplane_apply')")).
		WillReturnRows(sqlmock.NewRows([]string{"RELEASE_LOCK"}).AddRow(1))

	err = withReapplyPackLock(context.Background(), lockDB, nil, func() error {
		return nil
	})
	if err != nil {
		t.Fatalf("withReapplyPackLock: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func expectApplyAllMarkerWrite(mock sqlmock.Sqlmock, phase, status string) {
	mock.ExpectExec(regexp.QuoteMeta("CREATE TABLE IF NOT EXISTS _System_BootstrapState")).
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(regexp.QuoteMeta("INSERT INTO `_System_BootstrapState` (`StateKey`,`RunID`,`Phase`,`Status`,`LastError`,`CreatedDate`,`LastModifiedDate`) VALUES (?,?,?,?,?,NOW(),NOW())")).
		WithArgs("control_plane_apply_all", sqlmock.AnyArg(), phase, status, sqlmock.AnyArg()).
		WillReturnResult(sqlmock.NewResult(0, 1))
}

func TestInstallPackFromSourceRejectsLegacyActiveScalarFormula(t *testing.T) {
	root := writeFormulaTypeValidationSourceRoot(t, "string", "active", "Subject", "string")
	if err := validateSourceDiagnosticsForOperation("install-pack", "local_path:"+root); err == nil {
		t.Fatal("install-pack accepted legacy active-scalar formula shape; want fail-closed error")
	}
}

func TestReapplyPackFromSourceRejectsLegacyActiveScalarFormula(t *testing.T) {
	root := writeFormulaTypeValidationSourceRoot(t, "string", "active", "Subject", "string")
	if err := validateSourceDiagnosticsForOperation("reapply-pack", "local_path:"+root); err == nil {
		t.Fatal("reapply-pack accepted legacy active-scalar formula shape; want fail-closed error")
	}
}

// writeFormulaTypeValidationSourceRoot writes a minimal local pack source with a single
// formula field whose type/formulaMode shape is controlled by the caller.
func writeFormulaTypeValidationSourceRoot(t *testing.T, fieldType string, formulaMode string, formulaExpr string, returnType string) string {
	t.Helper()
	root := filepath.Join(t.TempDir(), "jira-like-sample")
	if err := os.MkdirAll(root, 0o755); err != nil {
		t.Fatalf("mkdir source root: %v", err)
	}
	manifest := `{
  "manifestFormatVersion": "1",
  "name": "jira-like-sample",
  "displayName": "Jira Like Sample",
  "category": "app-pack",
  "version": "0.1.0",
  "platformCompatibility": {"min": "0.2.0", "maxExclusive": "0.3.0"},
  "components": [{
    "kind": "object_spec",
    "businessKey": "issue",
    "manageability": "locked",
    "payload": [{
      "apiName": "Issue",
      "label": "Issue",
      "tableType": "standard_object",
      "category": "jira",
      "sharingModel": "Private",
      "enableHierarchySharing": false,
      "description": "Issue work item",
      "fields": [
        {"apiName":"Subject","label":"Subject","type":"string","required":true,"isNameField":true},
        {"apiName":"SubjectEcho","label":"Subject Echo","type":"` + fieldType + `","formulaMode":"` + formulaMode + `","formula":"` + formulaExpr + `","returnType":"` + returnType + `"}
      ]
    }]
  }]
}`
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write source manifest: %v", err)
	}
	return root
}

func TestRemovePackOwnedFieldUnlockedRejectsActiveDeclaredFieldBeforeDrop(t *testing.T) {
	sourceDir, pack := writeFieldDeclarationSource(t, "Subject")
	payloadHash := mustPackPayloadHash(t, pack)
	staleHash := packs.PackPayloadHashV1Prefix + "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
	if staleHash == payloadHash {
		t.Fatal("test setup expected stale hash to differ from current payload hash")
	}

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

	runner := &Runner{
		DB: database.NewConnectionFromDB(db),
		SvcMgr: newTestServiceGraph(testServiceGraphConfig{
			Schema: metadata.NewSchemaManager(persistence.NewSchemaRepository(db, nil)),
		}),
	}

	mock.ExpectQuery("SELECT COALESCE\\(f.OwnerKind, ''\\), COALESCE\\(f.OwnerPack, ''\\) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId").
		WithArgs("Issue", "Subject").
		WillReturnRows(sqlmock.NewRows([]string{"OwnerKind", "OwnerPack"}).AddRow("pack", "jira-like-sample"))
	expectInstalledPackStateLookup(mock, "jira-like-sample", installedPackStatusActive, 0)
	mock.ExpectQuery("SELECT Name, Category, Status, COALESCE\\(IsDeleted, 0\\), ManifestFormatVersion, SourceKind, SourceURI, SourceRevision, AppliedPayloadHash, DesiredPayloadHash FROM _System_InstalledPack").
		WithArgs("jira-like-sample").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "IsDeleted", "ManifestFormatVersion", "SourceKind", "SourceURI", "SourceRevision", "AppliedPayloadHash", "DesiredPayloadHash",
		}).AddRow(
			"jira-like-sample",
			string(packs.AppPack),
			installedPackStatusActive,
			0,
			packs.SupportedManifestFormatVersion,
			string(packs.PackSourceKindLocalPath),
			sourceDir,
			"",
			staleHash,
			staleHash,
		))

	err = removePackOwnedFieldUnlocked(context.Background(), runner, "jira-like-sample", "Issue", "Subject")
	if err == nil {
		t.Fatal("removePackOwnedFieldUnlocked = nil, want declared-field rejection")
	}
	if !strings.Contains(err.Error(), `still declares field Issue.Subject`) {
		t.Fatalf("error = %v, want declared-field rejection", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestRemovePackOwnedFieldUnlockedDropsStaleFieldWithoutMarkingPackRemoving(t *testing.T) {
	sourceDir, pack := writeFieldDeclarationSource(t, "Subject")
	payloadHash := mustPackPayloadHash(t, pack)
	staleHash := packs.PackPayloadHashV1Prefix + "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"
	if staleHash == payloadHash {
		t.Fatal("test setup expected stale hash to differ from current payload hash")
	}

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

	runner := &Runner{
		DB: database.NewConnectionFromDB(db),
		SvcMgr: newTestServiceGraph(testServiceGraphConfig{
			Schema: metadata.NewSchemaManager(persistence.NewSchemaRepository(db, nil)),
		}),
	}

	mock.ExpectQuery("SELECT COALESCE\\(f.OwnerKind, ''\\), COALESCE\\(f.OwnerPack, ''\\) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId").
		WithArgs("Issue", "LegacyField__c").
		WillReturnRows(sqlmock.NewRows([]string{"OwnerKind", "OwnerPack"}).AddRow("pack", "jira-like-sample"))
	expectInstalledPackStateLookup(mock, "jira-like-sample", installedPackStatusActive, 0)
	mock.ExpectQuery("SELECT Name, Category, Status, COALESCE\\(IsDeleted, 0\\), ManifestFormatVersion, SourceKind, SourceURI, SourceRevision, AppliedPayloadHash, DesiredPayloadHash FROM _System_InstalledPack").
		WithArgs("jira-like-sample").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "IsDeleted", "ManifestFormatVersion", "SourceKind", "SourceURI", "SourceRevision", "AppliedPayloadHash", "DesiredPayloadHash",
		}).AddRow(
			"jira-like-sample",
			string(packs.AppPack),
			installedPackStatusActive,
			0,
			packs.SupportedManifestFormatVersion,
			string(packs.PackSourceKindLocalPath),
			sourceDir,
			"",
			staleHash,
			staleHash,
		))
	mock.ExpectQuery("SELECT f.Id, COALESCE\\(f.OwnerKind, ''\\), COALESCE\\(f.OwnerPack, ''\\) FROM `_System_Field` f JOIN `_System_Object` o ON o.Id = f.ObjectId").
		WithArgs("Issue", "LegacyField__c").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "OwnerKind", "OwnerPack"}).AddRow("fld_issue_legacy_field", "pack", "jira-like-sample"))
	mock.ExpectExec(regexp.QuoteMeta("UPDATE `_System_Field` SET OwnerKind = ?, OwnerPack = OwnerPack, IsDeleted = 1, LastModifiedDate = CURRENT_TIMESTAMP WHERE Id = ?")).
		WithArgs("pack_removing", "fld_issue_legacy_field").
		WillReturnResult(sqlmock.NewResult(0, 1))
	expectInstalledFieldColumnExistsLookup(mock, "Issue", "LegacyField__c", 1)
	mock.ExpectQuery("INFORMATION_SCHEMA.KEY_COLUMN_USAGE").
		WithArgs("Issue", "LegacyField__c").
		WillReturnRows(sqlmock.NewRows([]string{"CONSTRAINT_NAME"}))
	mock.ExpectExec("ALTER TABLE `Issue` DROP COLUMN `LegacyField__c`").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectQuery(regexp.QuoteMeta("SELECT TrackHistory, IsDeleted FROM `_System_Field` WHERE Id = ?")).
		WithArgs("fld_issue_legacy_field").
		WillReturnRows(sqlmock.NewRows([]string{"TrackHistory", "IsDeleted"}).AddRow(false, true))
	expectInstalledFieldColumnExistsLookup(mock, "Issue", "LegacyField__c", 0)
	mock.ExpectBegin()
	mock.ExpectQuery(regexp.QuoteMeta("SELECT f.Id, f.TrackHistory FROM `_System_Field` f JOIN `_System_Object` o ON o.Id = f.ObjectId WHERE o.ApiName = ? AND f.ApiName = ? AND COALESCE(f.OwnerKind, '') = ? AND COALESCE(f.OwnerPack, '') = ? LIMIT 1 FOR UPDATE")).
		WithArgs("Issue", "LegacyField__c", "pack_removing", "jira-like-sample").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "TrackHistory"}).AddRow("fld_issue_legacy_field", false))
	mock.ExpectExec(regexp.QuoteMeta("DELETE FROM `_System_Field` WHERE Id = ?")).
		WithArgs("fld_issue_legacy_field").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectCommit()

	if err := removePackOwnedFieldUnlocked(context.Background(), runner, "jira-like-sample", "Issue", "LegacyField__c"); err != nil {
		t.Fatalf("removePackOwnedFieldUnlocked: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestRemovePackOwnedFieldUnlockedRejectsIncoherentActiveHashState(t *testing.T) {
	sourceDir, _ := writeFieldDeclarationSource(t, "Subject")

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

	runner := &Runner{
		DB: database.NewConnectionFromDB(db),
		SvcMgr: newTestServiceGraph(testServiceGraphConfig{
			Schema: metadata.NewSchemaManager(persistence.NewSchemaRepository(db, nil)),
		}),
	}

	mock.ExpectQuery("SELECT COALESCE\\(f.OwnerKind, ''\\), COALESCE\\(f.OwnerPack, ''\\) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId").
		WithArgs("Issue", "LegacyField__c").
		WillReturnRows(sqlmock.NewRows([]string{"OwnerKind", "OwnerPack"}).AddRow("pack", "jira-like-sample"))
	expectInstalledPackStateLookup(mock, "jira-like-sample", installedPackStatusActive, 0)
	mock.ExpectQuery("SELECT Name, Category, Status, COALESCE\\(IsDeleted, 0\\), ManifestFormatVersion, SourceKind, SourceURI, SourceRevision, AppliedPayloadHash, DesiredPayloadHash FROM _System_InstalledPack").
		WithArgs("jira-like-sample").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "IsDeleted", "ManifestFormatVersion", "SourceKind", "SourceURI", "SourceRevision", "AppliedPayloadHash", "DesiredPayloadHash",
		}).AddRow(
			"jira-like-sample",
			string(packs.AppPack),
			installedPackStatusActive,
			0,
			packs.SupportedManifestFormatVersion,
			string(packs.PackSourceKindLocalPath),
			sourceDir,
			"",
			packs.PackPayloadHashV1Prefix+"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
			packs.PackPayloadHashV1Prefix+"bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb",
		))

	err = removePackOwnedFieldUnlocked(context.Background(), runner, "jira-like-sample", "Issue", "LegacyField__c")
	if err == nil {
		t.Fatal("removePackOwnedFieldUnlocked = nil, want incoherent-hash rejection")
	}
	if !strings.Contains(err.Error(), "refusing explicit removal check") {
		t.Fatalf("error = %v, want explicit removal hash-state rejection", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestRemovePackOwnedFieldUnlockedRejectsInvalidExplicitRemovalHashShape(t *testing.T) {
	sourceDir, _ := writeFieldDeclarationSource(t, "Subject")

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

	runner := &Runner{
		DB: database.NewConnectionFromDB(db),
		SvcMgr: newTestServiceGraph(testServiceGraphConfig{
			Schema: metadata.NewSchemaManager(persistence.NewSchemaRepository(db, nil)),
		}),
	}

	mock.ExpectQuery("SELECT COALESCE\\(f.OwnerKind, ''\\), COALESCE\\(f.OwnerPack, ''\\) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId").
		WithArgs("Issue", "LegacyField__c").
		WillReturnRows(sqlmock.NewRows([]string{"OwnerKind", "OwnerPack"}).AddRow("pack", "jira-like-sample"))
	expectInstalledPackStateLookup(mock, "jira-like-sample", installedPackStatusActive, 0)
	mock.ExpectQuery("SELECT Name, Category, Status, COALESCE\\(IsDeleted, 0\\), ManifestFormatVersion, SourceKind, SourceURI, SourceRevision, AppliedPayloadHash, DesiredPayloadHash FROM _System_InstalledPack").
		WithArgs("jira-like-sample").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "IsDeleted", "ManifestFormatVersion", "SourceKind", "SourceURI", "SourceRevision", "AppliedPayloadHash", "DesiredPayloadHash",
		}).AddRow(
			"jira-like-sample",
			string(packs.AppPack),
			installedPackStatusActive,
			0,
			packs.SupportedManifestFormatVersion,
			string(packs.PackSourceKindLocalPath),
			sourceDir,
			"",
			"not-a-v1-hash",
			"not-a-v1-hash",
		))

	err = removePackOwnedFieldUnlocked(context.Background(), runner, "jira-like-sample", "Issue", "LegacyField__c")
	if err == nil {
		t.Fatal("removePackOwnedFieldUnlocked = nil, want invalid-hash-shape rejection")
	}
	if !strings.Contains(err.Error(), "refusing explicit removal check") {
		t.Fatalf("error = %v, want explicit removal invalid-hash rejection", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	runner := &Runner{
		DB: database.NewConnectionFromDB(db),
		SvcMgr: newTestServiceGraph(testServiceGraphConfig{
			Schema: metadata.NewSchemaManager(persistence.NewSchemaRepository(db, nil)),
		}),
	}

	mock.ExpectQuery("SELECT COALESCE\\(f.OwnerKind, ''\\), COALESCE\\(f.OwnerPack, ''\\) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId").
		WithArgs("Issue", "Subject").
		WillReturnRows(sqlmock.NewRows([]string{"OwnerKind", "OwnerPack"}).AddRow("pack", "other-pack"))

	err = removePackOwnedFieldUnlocked(context.Background(), runner, "jira-like-sample", "Issue", "Subject")
	if err == nil {
		t.Fatal("removePackOwnedFieldUnlocked = nil, want owner-pack preflight error")
	}
	if !strings.Contains(err.Error(), `OwnerPack = "other-pack"`) {
		t.Fatalf("error = %v, want owner-pack mismatch", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func expectZeroInstallOwnerSurfaceCounts(mock sqlmock.Sqlmock, expectedAllowedPacks ...[]string) {
	if len(expectedAllowedPacks) > 1 {
		panic("expectZeroInstallOwnerSurfaceCounts accepts at most one allowed-pack list")
	}
	var args []driver.Value
	if len(expectedAllowedPacks) == 1 {
		args = make([]driver.Value, len(expectedAllowedPacks[0]))
		for index, packName := range expectedAllowedPacks[0] {
			args[index] = packName
		}
	}
	for _, tableName := range []string{
		"_System_RecordType",
		"_System_ValidationRule",
		"_System_ReportType",
		"_System_Action",
		"_System_StandardAction",
		"_System_Permission",
		"_System_CustomPermission",
		"_System_RecordTypePicklistValue",
		"ProcessDefinition",
		"_System_ConnectorRateLimit",
		"_System_ConnectorInboundHandlerConfig",
		"_System_ConnectorCalloutProfile",
		"_System_EmailTemplate",
		"_System_Object",
		"_System_Object",
	} {
		expectation := mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM " + tableName)
		if len(expectedAllowedPacks) == 1 {
			expectation.WithArgs(args...)
		}
		expectation.WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	}
}

func expectInstalledFieldColumnExistsLookup(mock sqlmock.Sqlmock, tableName string, columnName string, count int) {
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs(tableName, columnName).
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(count))
}

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

	pack := testControlplaneSourceBackedPack(t)
	currentHash, err := packs.PackPayloadHash(pack)
	if err != nil {
		t.Fatalf("current source hash: %v", err)
	}
	oldHash := packs.PackPayloadHashV1Prefix + "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
	if oldHash == currentHash {
		t.Fatal("test setup expected old hash to differ from current hash")
	}

	mock.ExpectQuery("SELECT Name, Category, Status, COALESCE\\(IsDeleted, 0\\), PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
		WithArgs(pack.Name()).
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "IsDeleted", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			pack.Name(),
			string(packs.AppPack),
			installedPackStatusActive,
			0,
			"0.1.0",
			packs.SupportedManifestFormatVersion,
			`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
			oldHash,
			oldHash,
			packs.PackPayloadHashAlgorithmV1,
			packs.PackSourceKindLocalPath,
			"/tmp/source-pack",
			nil,
			nil,
		))

	allowed, err := samePackAllowedOwnerlessObjects(context.Background(), db, pack, currentHash)
	if err != nil {
		t.Fatalf("samePackAllowedOwnerlessObjects active hash-changed reapply: %v", err)
	}
	if len(allowed) != 1 || allowed[0] != "Issue" {
		t.Fatalf("allowed objects = %#v, want Issue", allowed)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestSamePackAllowedOwnerlessObjectsRequiresAuthorizationForOfficialRepoRevisionAdvance(t *testing.T) {
	pack := testControlplaneOfficialRepoSourceBackedPack(t, "new-revision")
	currentHash, err := packs.PackPayloadHash(pack)
	if err != nil {
		t.Fatalf("current source hash: %v", err)
	}

	t.Run("no authorization fails closed", func(t *testing.T) {
		db, mock, err := sqlmock.New()
		if err != nil {
			t.Fatalf("sqlmock: %v", err)
		}
		defer db.Close()
		expectOfficialRepoInstalledPackRetryAllowanceRow(mock, pack.Name(), "old-revision")

		_, err = samePackAllowedOwnerlessObjectsWithOptions(context.Background(), db, pack, currentHash, InstallPackOptions{})
		if err == nil {
			t.Fatal("samePackAllowedOwnerlessObjectsWithOptions without authorization = nil, want source identity mismatch")
		}
		if !strings.Contains(err.Error(), "source identity differs from requested source") {
			t.Fatalf("error = %v, want source identity mismatch", err)
		}
		if !strings.Contains(err.Error(), "--allow-source-revision-advance") || !strings.Contains(err.Error(), "--from-revision old-revision") || !strings.Contains(err.Error(), "--to-revision new-revision") || !strings.Contains(err.Error(), "--verify-pack") {
			t.Fatalf("error = %v, want supported source-revision advance operator diagnostic", err)
		}
		if err := mock.ExpectationsWereMet(); err != nil {
			t.Fatalf("sql expectations: %v", err)
		}
	})

	t.Run("exact authorization allows advance", func(t *testing.T) {
		db, mock, err := sqlmock.New()
		if err != nil {
			t.Fatalf("sqlmock: %v", err)
		}
		defer db.Close()
		oldPack := testControlplaneOfficialRepoSourceBackedPack(t, "old-revision")
		oldHash, err := packs.PackPayloadHash(oldPack)
		if err != nil {
			t.Fatalf("old source hash: %v", err)
		}
		expectOfficialRepoInstalledPackRetryAllowanceRowWithAppliedHash(mock, pack.Name(), "old-revision", oldHash)
		restore := stubSourceRevisionAdvancePayloadValidation(t, oldHash)
		defer restore()

		allowed, err := samePackAllowedOwnerlessObjectsWithOptions(context.Background(), db, pack, currentHash, InstallPackOptions{
			SourceRevisionAdvance: &SourceRevisionAdvanceAuthorization{
				Name:         pack.Name(),
				Kind:         string(packs.PackSourceKindOfficialRepo),
				URI:          pack.Name(),
				FromRevision: "old-revision",
				ToRevision:   "new-revision",
			},
		})
		if err != nil {
			t.Fatalf("samePackAllowedOwnerlessObjectsWithOptions exact authorization: %v", err)
		}
		if len(allowed) != 1 || allowed[0] != "Issue" {
			t.Fatalf("allowed objects = %#v, want Issue", allowed)
		}
		if err := mock.ExpectationsWereMet(); err != nil {
			t.Fatalf("sql expectations: %v", err)
		}
	})

	t.Run("authorization fails closed when installed hash is not from revision hash", func(t *testing.T) {
		db, mock, err := sqlmock.New()
		if err != nil {
			t.Fatalf("sqlmock: %v", err)
		}
		defer db.Close()
		fromRevisionHash := packs.PackPayloadHashV1Prefix + strings.Repeat("a", 64)
		currentInstalledHash := packs.PackPayloadHashV1Prefix + strings.Repeat("b", 64)
		expectOfficialRepoInstalledPackRetryAllowanceRowWithAppliedHash(mock, pack.Name(), "old-revision", currentInstalledHash)
		restore := stubSourceRevisionAdvancePayloadValidation(t, fromRevisionHash)
		defer restore()

		_, err = samePackAllowedOwnerlessObjectsWithOptions(context.Background(), db, pack, currentHash, InstallPackOptions{
			SourceRevisionAdvance: &SourceRevisionAdvanceAuthorization{
				Name:         pack.Name(),
				Kind:         string(packs.PackSourceKindOfficialRepo),
				URI:          pack.Name(),
				FromRevision: "old-revision",
				ToRevision:   "new-revision",
			},
		})
		if err == nil {
			t.Fatal("samePackAllowedOwnerlessObjectsWithOptions mismatched from hash = nil, want fail closed")
		}
		if !strings.Contains(err.Error(), "AppliedPayloadHash") || !strings.Contains(err.Error(), "old-revision") {
			t.Fatalf("error = %v, want AppliedPayloadHash source revision precondition detail", err)
		}
		if err := mock.ExpectationsWereMet(); err != nil {
			t.Fatalf("sql expectations: %v", err)
		}
	})
}

func TestValidateDefaultChildRowsInstallPreflightRejectsUnsatisfiedRequiredField(t *testing.T) {
	pack := testControlplaneDefaultChildRowsPack(t)
	err := validateDefaultChildRowsInstallPreflight(context.Background(), nil, pack)
	if err == nil {
		t.Fatal("validateDefaultChildRowsInstallPreflight = nil, want required-field failure")
	}
	if !strings.Contains(err.Error(), "required target field") {
		t.Fatalf("validateDefaultChildRowsInstallPreflight error = %v, want required-target-field detail", err)
	}
}

func TestValidateDefaultChildRowsInstallPreflightRejectsSameInstallEngineOwnedField(t *testing.T) {
	pack := testControlplaneDefaultChildRowsAutoNumberPack(t)
	err := validateDefaultChildRowsInstallPreflight(context.Background(), nil, pack)
	if err == nil {
		t.Fatal("validateDefaultChildRowsInstallPreflight = nil, want engine-owned field failure")
	}
	if !strings.Contains(err.Error(), "EngineOwned__c") {
		t.Fatalf("validateDefaultChildRowsInstallPreflight error = %v, want engine-owned field detail", err)
	}
	if !strings.Contains(err.Error(), "engine-owned") {
		t.Fatalf("validateDefaultChildRowsInstallPreflight error = %v, want engine-owned rejection", err)
	}
}

func TestValidateChildCloneRulesInstallPreflightRejectsBadSameInstallReference(t *testing.T) {
	pack := testControlplaneChildCloneRulesPack(t)
	err := validateChildCloneRulesInstallPreflight(context.Background(), nil, pack)
	if err == nil {
		t.Fatal("validateChildCloneRulesInstallPreflight = nil, want target lookup failure")
	}
	if !strings.Contains(err.Error(), "install-pack childCloneRules preflight") {
		t.Fatalf("validateChildCloneRulesInstallPreflight error = %v, want install preflight context", err)
	}
	if !strings.Contains(err.Error(), "targetLookupField") {
		t.Fatalf("validateChildCloneRulesInstallPreflight error = %v, want targetLookupField detail", err)
	}
}

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

	pack := testControlplaneSourceBackedPack(t)
	currentHash, err := packs.PackPayloadHash(pack)
	if err != nil {
		t.Fatalf("current source hash: %v", err)
	}
	legacyHash := packs.PackPayloadHashV1Prefix + "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"
	if legacyHash == currentHash {
		t.Fatal("test setup expected legacy hash to differ from current hash")
	}

	mock.ExpectQuery("SELECT Name, Category, Status, COALESCE\\(IsDeleted, 0\\), PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
		WithArgs(pack.Name()).
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "IsDeleted", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			pack.Name(),
			string(packs.AppPack),
			installedPackStatusActive,
			0,
			"0.1.0",
			packs.SupportedManifestFormatVersion,
			`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
			legacyHash,
			legacyHash,
			packs.PackPayloadHashAlgorithmV1,
			nil,
			nil,
			nil,
			nil,
		))

	allowed, err := samePackAllowedOwnerlessObjects(context.Background(), db, pack, currentHash)
	if err != nil {
		t.Fatalf("samePackAllowedOwnerlessObjects legacy adoption: %v", err)
	}
	if len(allowed) != 1 || allowed[0] != "Issue" {
		t.Fatalf("allowed objects = %#v, want Issue", allowed)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	pack := testControlplaneSourceBackedPack(t)
	currentHash, err := packs.PackPayloadHash(pack)
	if err != nil {
		t.Fatalf("current source hash: %v", err)
	}
	legacyHash := packs.PackPayloadHashV1Prefix + "cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc"

	mock.ExpectQuery("SELECT Name, Category, Status, COALESCE\\(IsDeleted, 0\\), PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
		WithArgs(pack.Name()).
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "IsDeleted", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			pack.Name(),
			string(packs.AppPack),
			installedPackStatusActive,
			0,
			"0.1.0",
			packs.SupportedManifestFormatVersion,
			`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
			legacyHash,
			legacyHash,
			packs.PackPayloadHashAlgorithmV1,
			packs.PackSourceKindOfficialRepo,
			nil,
			"legacy-revision-without-uri",
			nil,
		))
	allowed, err := samePackAllowedOwnerlessObjects(context.Background(), db, pack, currentHash)
	if err != nil {
		t.Fatalf("samePackAllowedOwnerlessObjects incomplete identity adoption: %v", err)
	}
	if len(allowed) != 1 || allowed[0] != "Issue" {
		t.Fatalf("allowed objects = %#v, want Issue", allowed)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestSourceIdentityMatchesIgnoresLocalPathRevisionDrift(t *testing.T) {
	row := installedPackRow{
		SourceKind:     sql.NullString{Valid: true, String: string(packs.PackSourceKindLocalPath)},
		SourceURI:      sql.NullString{Valid: true, String: "/tmp/source-pack"},
		SourceRevision: sql.NullString{Valid: true, String: "old-revision"},
	}
	meta := packs.PackHashMetadata{
		SourceKind:          string(packs.PackSourceKindLocalPath),
		SourceURI:           "/tmp/source-pack",
		SourceRevision:      "new-revision",
		SourceRevisionValid: true,
	}

	if !sourceIdentityMatches(row, meta) {
		t.Fatal("sourceIdentityMatches(local_path) = false, want true when only revision drifted")
	}
}

func TestSourceIdentityMatchesKeepsOfficialRepoRevisionStrict(t *testing.T) {
	row := installedPackRow{
		SourceKind:     sql.NullString{Valid: true, String: string(packs.PackSourceKindOfficialRepo)},
		SourceURI:      sql.NullString{Valid: true, String: "official_repo:salesforce-crm-standard"},
		SourceRevision: sql.NullString{Valid: true, String: "old-revision"},
	}
	meta := packs.PackHashMetadata{
		SourceKind:          string(packs.PackSourceKindOfficialRepo),
		SourceURI:           "official_repo:salesforce-crm-standard",
		SourceRevision:      "new-revision",
		SourceRevisionValid: true,
	}

	if sourceIdentityMatches(row, meta) {
		t.Fatal("sourceIdentityMatches(official_repo) = true, want false when revision drifted")
	}
}

func TestSourceRevisionAdvanceAuthorizationRequiresExactPackAndRevision(t *testing.T) {
	row := installedPackRow{
		Name:               "jira-like-sample",
		AppliedPayloadHash: sql.NullString{Valid: true, String: "from-payload-hash"},
		SourceKind:         sql.NullString{Valid: true, String: string(packs.PackSourceKindOfficialRepo)},
		SourceURI:          sql.NullString{Valid: true, String: "jira-like-sample"},
		SourceRevision:     sql.NullString{Valid: true, String: "old-revision"},
	}
	meta := packs.PackHashMetadata{
		SourceKind:          string(packs.PackSourceKindOfficialRepo),
		SourceURI:           "jira-like-sample",
		SourceRevision:      "new-revision",
		SourceRevisionValid: true,
	}
	auth := SourceRevisionAdvanceAuthorization{
		Name:         "jira-like-sample",
		Kind:         string(packs.PackSourceKindOfficialRepo),
		URI:          "jira-like-sample",
		FromRevision: "old-revision",
		ToRevision:   "new-revision",
	}
	restore := stubSourceRevisionAdvancePayloadValidation(t, "from-payload-hash")
	defer restore()

	if err := sourceRevisionAdvanceAuthorizationError(context.Background(), nil, row, "jira-like-sample", meta, &auth); err != nil {
		t.Fatalf("sourceRevisionAdvanceAuthorizationError exact authorization = %v, want nil", err)
	}
	if err := sourceRevisionAdvanceAuthorizationError(context.Background(), nil, row, "jira-like-sample", meta, nil); err == nil {
		t.Fatal("sourceRevisionAdvanceAuthorizationError nil authorization = nil, want error")
	}

	for _, tc := range []struct {
		name   string
		mutate func(*SourceRevisionAdvanceAuthorization, *installedPackRow, *packs.PackHashMetadata)
	}{
		{
			name: "resolved pack name mismatch",
			mutate: func(auth *SourceRevisionAdvanceAuthorization, _ *installedPackRow, _ *packs.PackHashMetadata) {
				auth.Name = "jira-like-sample"
			},
		},
		{
			name: "wrong name",
			mutate: func(auth *SourceRevisionAdvanceAuthorization, _ *installedPackRow, _ *packs.PackHashMetadata) {
				auth.Name = "other-pack"
			},
		},
		{
			name: "wrong kind",
			mutate: func(auth *SourceRevisionAdvanceAuthorization, _ *installedPackRow, _ *packs.PackHashMetadata) {
				auth.Kind = string(packs.PackSourceKindLocalPath)
			},
		},
		{
			name: "wrong uri",
			mutate: func(auth *SourceRevisionAdvanceAuthorization, _ *installedPackRow, _ *packs.PackHashMetadata) {
				auth.URI = "other-pack"
			},
		},
		{
			name: "wrong from revision",
			mutate: func(auth *SourceRevisionAdvanceAuthorization, _ *installedPackRow, _ *packs.PackHashMetadata) {
				auth.FromRevision = "unexpected-old"
			},
		},
		{
			name: "wrong to revision",
			mutate: func(auth *SourceRevisionAdvanceAuthorization, _ *installedPackRow, _ *packs.PackHashMetadata) {
				auth.ToRevision = "unexpected-new"
			},
		},
		{
			name: "empty from revision",
			mutate: func(auth *SourceRevisionAdvanceAuthorization, _ *installedPackRow, _ *packs.PackHashMetadata) {
				auth.FromRevision = ""
			},
		},
		{
			name: "empty to revision",
			mutate: func(auth *SourceRevisionAdvanceAuthorization, _ *installedPackRow, _ *packs.PackHashMetadata) {
				auth.ToRevision = ""
			},
		},
		{
			name: "same from and to",
			mutate: func(auth *SourceRevisionAdvanceAuthorization, _ *installedPackRow, _ *packs.PackHashMetadata) {
				auth.ToRevision = auth.FromRevision
			},
		},
		{
			name: "installed row source kind mismatch",
			mutate: func(_ *SourceRevisionAdvanceAuthorization, row *installedPackRow, _ *packs.PackHashMetadata) {
				row.SourceKind = sql.NullString{Valid: true, String: string(packs.PackSourceKindLocalPath)}
			},
		},
		{
			name: "resolved source uri mismatch",
			mutate: func(_ *SourceRevisionAdvanceAuthorization, _ *installedPackRow, meta *packs.PackHashMetadata) {
				meta.SourceURI = "other-pack"
			},
		},
	} {
		t.Run(tc.name, func(t *testing.T) {
			candidateAuth := auth
			candidateRow := row
			candidateMeta := meta
			packName := "jira-like-sample"
			if tc.name == "resolved pack name mismatch" {
				packName = "other-pack"
			}
			tc.mutate(&candidateAuth, &candidateRow, &candidateMeta)
			if err := sourceRevisionAdvanceAuthorizationError(context.Background(), nil, candidateRow, packName, candidateMeta, &candidateAuth); err == nil {
				t.Fatalf("sourceRevisionAdvanceAuthorizationError(%s) = nil, want error", tc.name)
			}
		})
	}
}

func TestSourceRevisionAdvanceAuthorizationAcceptsLegacyAppliedHashForFromRevision(t *testing.T) {
	const packName = "jira-like-sample"
	root, _ := writeNamedFieldDeclarationSource(t, packName, "Issue", "Priority")
	t.Setenv(packs.OfficialAppPacksRootEnvVar, filepath.Dir(root))
	initGitTestRepoForControlplane(t, root)
	commitOne := gitTestCommitForControlplane(t, root, "initial")

	packAtCommitOne, err := packs.ResolveInstalledSourceBackedPackForInstalledRowAtSourceRevision(
		packName,
		packs.AppPack,
		packs.SupportedManifestFormatVersion,
		packs.PackSourceKindOfficialRepo,
		packName,
		commitOne,
	)
	if err != nil {
		t.Fatalf("ResolveInstalledSourceBackedPackForInstalledRowAtSourceRevision(commitOne): %v", err)
	}
	legacyHash, err := packs.LegacyPackPayloadHash(packAtCommitOne)
	if err != nil {
		t.Fatalf("legacy payload hash: %v", err)
	}

	mutateControlplaneGitTestPackSource(t, root)
	commitTwo := gitTestCommitForControlplane(t, root, "mutated")

	row := installedPackRow{
		Name:                  packName,
		Category:              string(packs.AppPack),
		ManifestFormatVersion: sql.NullString{Valid: true, String: packs.SupportedManifestFormatVersion},
		AppliedPayloadHash:    sql.NullString{Valid: true, String: legacyHash},
		SourceKind:            sql.NullString{Valid: true, String: string(packs.PackSourceKindOfficialRepo)},
		SourceURI:             sql.NullString{Valid: true, String: packName},
		SourceRevision:        sql.NullString{Valid: true, String: commitOne},
	}
	meta := packs.PackHashMetadata{
		SourceKind:          string(packs.PackSourceKindOfficialRepo),
		SourceURI:           packName,
		SourceRevision:      commitTwo,
		SourceRevisionValid: true,
	}
	auth := SourceRevisionAdvanceAuthorization{
		Name:         packName,
		Kind:         string(packs.PackSourceKindOfficialRepo),
		URI:          packName,
		FromRevision: commitOne,
		ToRevision:   commitTwo,
	}

	if err := sourceRevisionAdvanceAuthorizationError(context.Background(), nil, row, packName, meta, &auth); err != nil {
		t.Fatalf("sourceRevisionAdvanceAuthorizationError legacy applied hash = %v, want nil", err)
	}
}

func TestValidatePayloadHashForInstalledPackSourceRevisionUsesDBSchemaResolver(t *testing.T) {
	root := writeConnectorAssociationSchemaResolverPackSourceRoot(t)
	opts := packs.DefaultPackSourceOptions()
	opts.ObjectSchemaResolver = testSourcePackSchemaResolver()
	expectedPack, err := packs.ResolveInstalledSourceBackedPackForInstalledRowAtSourceRevisionWithPackSourceOptions(
		"schema-resolver-app",
		packs.AppPack,
		packs.SupportedManifestFormatVersion,
		string(packs.PackSourceKindLocalPath),
		root,
		"",
		opts,
	)
	if err != nil {
		t.Fatalf("resolve expected schema-backed source pack: %v", err)
	}
	expectedHash, err := packs.PackPayloadHash(expectedPack)
	if err != nil {
		t.Fatalf("expected payload hash: %v", err)
	}

	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()
	expectWorkItemAssociationLookupSchemaLoad(mock)

	row := installedPackRow{
		Name:                  "schema-resolver-app",
		Category:              string(packs.AppPack),
		ManifestFormatVersion: sql.NullString{Valid: true, String: packs.SupportedManifestFormatVersion},
		SourceKind:            sql.NullString{Valid: true, String: string(packs.PackSourceKindLocalPath)},
		SourceURI:             sql.NullString{Valid: true, String: root},
	}
	if err := validatePayloadHashForInstalledPackSourceRevision(context.Background(), db, row, "", expectedHash); err != nil {
		t.Fatalf("validatePayloadHashForInstalledPackSourceRevision: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestValidatePayloadHashForInstalledPackSourceRevisionUsesActiveFormulaBundles(t *testing.T) {
	providerRoot := writeFormulaFunctionProviderPackSource(t, "formula-provider-pack")
	providerPack, err := packs.ResolveInstalledSourceBackedPack(
		"formula-provider-pack",
		packs.AppPack,
		string(packs.PackSourceKindLocalPath),
		providerRoot,
	)
	if err != nil {
		t.Fatalf("resolve provider pack: %v", err)
	}
	providerHash, err := packs.PackPayloadHash(providerPack)
	if err != nil {
		t.Fatalf("provider payload hash: %v", err)
	}
	providerDecl, err := packs.BuildPersistedRuntimeDeclaration(providerPack)
	if err != nil {
		t.Fatalf("provider runtime declaration: %v", err)
	}

	consumerRoot := writeFormulaFunctionConsumerPackSource(t, "formula-consumer-pack")
	consumerReport := packs.ValidateInstallablePackSourceWithRuntimeFormulaFunctionBundles(
		"local_path:"+consumerRoot,
		packs.DefaultPackSourceOptions(),
		providerPack.RuntimeFormulaFunctionBundles(),
	)
	if consumerReport.HasErrors() {
		t.Fatalf("consumer source diagnostics:\n%s", consumerReport.Error())
	}
	if consumerReport.Pack == nil {
		t.Fatal("consumer source did not produce an installable pack")
	}
	expectedHash, err := packs.PackPayloadHash(consumerReport.Pack)
	if err != nil {
		t.Fatalf("expected consumer payload hash: %v", err)
	}

	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()
	expectActiveInstalledPackRuntimeDeclarationWithFormulaBundles(mock, providerPack, providerRoot, providerHash, providerDecl)

	row := installedPackRow{
		Name:                  "formula-consumer-pack",
		Category:              string(packs.AppPack),
		ManifestFormatVersion: sql.NullString{Valid: true, String: packs.SupportedManifestFormatVersion},
		SourceKind:            sql.NullString{Valid: true, String: string(packs.PackSourceKindLocalPath)},
		SourceURI:             sql.NullString{Valid: true, String: consumerRoot},
	}
	if err := validatePayloadHashForInstalledPackSourceRevision(context.Background(), db, row, "", expectedHash); err != nil {
		t.Fatalf("validatePayloadHashForInstalledPackSourceRevision: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestPrepareReapplySourceRevisionAdvanceRoutesExactOfficialTargetBeforeInstalledStatusCheck(t *testing.T) {
	source := testControlplaneOfficialRepoResolvedSource("target")
	options, authorized, err := prepareReapplySourceRevisionAdvance("official_repo:"+source.URI, source, InstallPackOptions{
		SourceRevisionAdvance: &SourceRevisionAdvanceAuthorization{
			FromRevision: "from",
			ToRevision:   source.Revision,
		},
	})
	if err != nil {
		t.Fatalf("prepare exact official target: %v", err)
	}
	if !authorized || options.SourceRevisionAdvance == nil ||
		options.SourceRevisionAdvance.Name != source.Manifest.Name ||
		options.SourceRevisionAdvance.Kind != source.Kind ||
		options.SourceRevisionAdvance.URI != source.URI {
		t.Fatalf("prepared authorization = (%+v, %v), want exact official source route", options.SourceRevisionAdvance, authorized)
	}

	for _, test := range []struct {
		name      string
		rawSource string
		from, to  string
	}{
		{name: "requested source mismatch", rawSource: "official_repo:other", from: "from", to: source.Revision},
		{name: "non-official source", rawSource: "local_path:/tmp/jira-like-sample", from: "from", to: source.Revision},
		{name: "missing from revision", rawSource: "official_repo:" + source.URI, to: source.Revision},
		{name: "missing to revision", rawSource: "official_repo:" + source.URI, from: "from"},
		{name: "same revision", rawSource: "official_repo:" + source.URI, from: source.Revision, to: source.Revision},
	} {
		t.Run(test.name, func(t *testing.T) {
			_, authorized, err := prepareReapplySourceRevisionAdvance(test.rawSource, source, InstallPackOptions{
				SourceRevisionAdvance: &SourceRevisionAdvanceAuthorization{
					FromRevision: test.from,
					ToRevision:   test.to,
				},
			})
			if err == nil || authorized {
				t.Fatalf("prepare invalid edge = authorized %v, error %v", authorized, err)
			}
		})
	}
}

func TestVerifyReapplySourceRevisionAdvanceResolvedSource(t *testing.T) {
	source := testControlplaneOfficialRepoResolvedSource("target")
	authorization := SourceRevisionAdvanceAuthorization{
		Name:         source.Manifest.Name,
		Kind:         source.Kind,
		URI:          source.URI,
		FromRevision: "from",
		ToRevision:   source.Revision,
	}
	if err := verifyReapplySourceRevisionAdvanceResolvedSource(source, authorization); err != nil {
		t.Fatalf("exact resolved source: %v", err)
	}

	for _, test := range []struct {
		name   string
		mutate func(*packs.ResolvedPackSource, *SourceRevisionAdvanceAuthorization)
	}{
		{name: "missing source", mutate: func(source *packs.ResolvedPackSource, _ *SourceRevisionAdvanceAuthorization) {
			*source = packs.ResolvedPackSource{}
		}},
		{name: "pack name", mutate: func(source *packs.ResolvedPackSource, _ *SourceRevisionAdvanceAuthorization) {
			source.Manifest.Name = "other"
		}},
		{name: "source kind", mutate: func(source *packs.ResolvedPackSource, _ *SourceRevisionAdvanceAuthorization) {
			source.Kind = packs.PackSourceKindLocalPath
		}},
		{name: "source URI", mutate: func(source *packs.ResolvedPackSource, _ *SourceRevisionAdvanceAuthorization) { source.URI = "other" }},
		{name: "target revision", mutate: func(source *packs.ResolvedPackSource, _ *SourceRevisionAdvanceAuthorization) {
			source.Revision = "other"
		}},
	} {
		t.Run(test.name, func(t *testing.T) {
			candidateSource := *source
			candidateManifest := *source.Manifest
			candidateSource.Manifest = &candidateManifest
			candidateAuthorization := authorization
			test.mutate(&candidateSource, &candidateAuthorization)
			if err := verifyReapplySourceRevisionAdvanceResolvedSource(&candidateSource, candidateAuthorization); err == nil {
				t.Fatal("mismatched resolved source accepted")
			}
		})
	}
}

func TestLegacyInstalledPackAdoptionSourceRejectsAmbiguousPartialLocalPath(t *testing.T) {
	_, err := legacyInstalledPackAdoptionSource(installedPackRow{
		Name:       "jira-like-sample",
		SourceKind: sql.NullString{String: string(packs.PackSourceKindLocalPath), Valid: true},
	})
	if err == nil {
		t.Fatal("legacyInstalledPackAdoptionSource = nil, want incomplete local_path source identity error")
	}
	if !strings.Contains(err.Error(), "incomplete source identity") {
		t.Fatalf("error = %v, want incomplete source identity", err)
	}
}

func TestLegacyInstalledPackAdoptionSourcePrefersOfficialRepoForLegacyRows(t *testing.T) {
	got, err := legacyInstalledPackAdoptionSource(installedPackRow{
		Name: "salesforce-crm-standard",
	})
	if err != nil {
		t.Fatalf("legacyInstalledPackAdoptionSource empty identity: %v", err)
	}
	if want := "official_repo:salesforce-crm-standard"; got != want {
		t.Fatalf("adoption source = %q, want %q", got, want)
	}

	got, err = legacyInstalledPackAdoptionSource(installedPackRow{
		Name:       "salesforce-crm-standard",
		SourceKind: sql.NullString{String: string(packs.PackSourceKindOfficialRepo), Valid: true},
	})
	if err != nil {
		t.Fatalf("legacyInstalledPackAdoptionSource partial official_repo identity: %v", err)
	}
	if want := "official_repo:salesforce-crm-standard"; got != want {
		t.Fatalf("adoption source = %q, want %q", got, want)
	}
}

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

	mock.ExpectQuery("(?s)SELECT Id, Name, COALESCE\\(PackInstallId, ''\\).*FROM _System_InstalledPack").
		WithArgs(string(packs.AppPack), installedPackStatusActive).
		WillReturnRows(sqlmock.NewRows([]string{
			"Id", "Name", "PackInstallId", "Category", "Status", "PackVersion", "ManifestFormatVersion",
			"PlatformCompatibility", "DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm",
			"SourceKind", "SourceURI", "SourceRevision",
		}).AddRow(
			"installed-pack-id", "salesforce-crm-standard", "pack-install-id", string(packs.AppPack), installedPackStatusActive,
			nil, nil, nil, "legacy-hash", "legacy-hash", nil, nil, nil, nil,
		))

	runner := &Runner{DB: database.NewConnectionFromDB(rawDB)}
	execution := &offlineCandidateExecution{plan: offlineCandidateTransitionPlan{
		legacySourceAdoptions: map[string]struct{}{"salesforce-crm-standard": {}},
	}}
	options := InstallPackOptions{offlineCandidate: execution}
	if err := adoptLegacyInstalledPackSourceIdentities(context.Background(), runner, options); err != nil {
		t.Fatalf("defer offline candidate legacy adoption: %v", err)
	}
	if got := execution.legacySourceAdoptionPackInstallIDs["salesforce-crm-standard"]; got != "pack-install-id" {
		t.Fatalf("deferred legacy adoption PackInstallID = %q, want pack-install-id", got)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("SQL expectations: %v", err)
	}
}

func TestSamePackAllowedOwnerlessObjectsAllowsFailedHashChangedRetry(t *testing.T) {
	// A failed install row with a stale desired hash must not block retry of the
	// same pack, whether the row is live or already soft-deleted.
	tests := []struct {
		name      string
		isDeleted int
	}{
		{name: "HashChangedRow", isDeleted: 0},
		{name: "SoftDeletedRow", isDeleted: 1},
	}
	for _, tc := range tests {
		t.Run(tc.name, func(t *testing.T) {
			db, mock, err := sqlmock.New()
			if err != nil {
				t.Fatalf("sqlmock: %v", err)
			}
			defer db.Close()

			pack := testControlplaneSourceBackedPack(t)
			currentHash, err := packs.PackPayloadHash(pack)
			if err != nil {
				t.Fatalf("current source hash: %v", err)
			}
			oldDesiredHash := packs.PackPayloadHashV1Prefix + "eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"
			if oldDesiredHash == currentHash {
				t.Fatal("test setup expected old desired hash to differ from current hash")
			}

			mock.ExpectQuery("SELECT Name, Category, Status, COALESCE\\(IsDeleted, 0\\), PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
				WithArgs(pack.Name()).
				WillReturnRows(sqlmock.NewRows([]string{
					"Name", "Category", "Status", "IsDeleted", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
					"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
				}).AddRow(
					pack.Name(),
					string(packs.AppPack),
					installedPackStatusFailed,
					tc.isDeleted,
					"0.1.0",
					packs.SupportedManifestFormatVersion,
					`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
					oldDesiredHash,
					nil,
					packs.PackPayloadHashAlgorithmV1,
					packs.PackSourceKindLocalPath,
					"/tmp/source-pack",
					nil,
					nil,
				))

			allowed, err := samePackAllowedOwnerlessObjects(context.Background(), db, pack, currentHash)
			if err != nil {
				t.Fatalf("samePackAllowedOwnerlessObjects retry: %v", err)
			}
			if len(allowed) != 1 || allowed[0] != "Issue" {
				t.Fatalf("allowed objects = %#v, want Issue", allowed)
			}
			if err := mock.ExpectationsWereMet(); err != nil {
				t.Fatalf("sql expectations: %v", err)
			}
		})
	}
}

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

	pack := testControlplaneSourceBackedPack(t)
	currentHash, err := packs.PackPayloadHash(pack)
	if err != nil {
		t.Fatalf("current source hash: %v", err)
	}

	mock.ExpectQuery("SELECT Name, Category, Status, COALESCE\\(IsDeleted, 0\\), PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
		WithArgs(pack.Name()).
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "IsDeleted", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			pack.Name(),
			string(packs.AppPack),
			installedPackStatusActive,
			1,
			"0.1.0",
			packs.SupportedManifestFormatVersion,
			`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
			currentHash,
			currentHash,
			packs.PackPayloadHashAlgorithmV1,
			packs.PackSourceKindLocalPath,
			"/tmp/source-pack",
			nil,
			nil,
		))

	allowed, err := samePackAllowedOwnerlessObjects(context.Background(), db, pack, currentHash)
	if err != nil {
		t.Fatalf("samePackAllowedOwnerlessObjects soft-deleted active row: %v", err)
	}
	if len(allowed) != 0 {
		t.Fatalf("allowed objects = %#v, want nil for soft-deleted active row", allowed)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision, HostRequirementsDigest\\s+FROM _System_InstalledPack\\s+WHERE COALESCE\\(IsDeleted, 0\\) = 0 AND Name = \\?\\s+LIMIT 1").
		WithArgs("jira-like-sample").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}))

	_, ok, err := getInstalledPackRowByName(context.Background(), db, "jira-like-sample")
	if err != nil {
		t.Fatalf("getInstalledPackRowByName soft-deleted row: %v", err)
	}
	if ok {
		t.Fatal("getInstalledPackRowByName ok = true, want false for soft-deleted row")
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	mock.ExpectQuery("SELECT Name, Category, Status[\\s\\S]*HostRequirementsDigest[\\s\\S]*FROM _System_InstalledPack").
		WithArgs("jira-like-sample").
		WillReturnError(&mysql.MySQLError{Number: 1054, Message: "Unknown column 'HostRequirementsDigest' in 'field list'"})
	mock.ExpectQuery("SELECT Name, Category, Status[\\s\\S]*SourceRevision\\s+FROM _System_InstalledPack").
		WithArgs("jira-like-sample").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision",
		}).AddRow(
			"jira-like-sample",
			string(packs.AppPack),
			installedPackStatusActive,
			nil,
			nil,
			nil,
			nil,
			nil,
			nil,
			string(packs.PackSourceKindLocalPath),
			"/tmp/jira-like-sample",
			nil,
		))

	row, ok, err := getInstalledPackRowByName(context.Background(), db, "jira-like-sample")
	if err != nil {
		t.Fatalf("getInstalledPackRowByName: %v", err)
	}
	if !ok {
		t.Fatal("getInstalledPackRowByName ok = false, want true")
	}
	if row.Name != "jira-like-sample" {
		t.Fatalf("row.Name = %q, want jira-like-sample", row.Name)
	}
	if row.HostRequirementsDigest.Valid {
		t.Fatal("HostRequirementsDigest.Valid = true, want false for legacy schema fallback")
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	pack := testControlplaneSourceBackedPack(t)
	expectInstalledPackRuntimeDeclarationColumns(mock, false)
	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
		WithArgs(string(packs.AppPack), string(packs.ConnectorPack)).
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			"other-pack",
			string(packs.AppPack),
			installedPackStatusFailed,
			"0.1.0",
			packs.SupportedManifestFormatVersion,
			`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
			packs.PackPayloadHashV1Prefix+"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
			nil,
			packs.PackPayloadHashAlgorithmV1,
			packs.PackSourceKindLocalPath,
			"/tmp/other-pack",
			nil,
			nil,
		))
	expectInstalledPackStateLookup(mock, "other-pack", installedPackStatusFailed, 0)
	expectInstalledPackStateLookup(mock, "other-pack", installedPackStatusFailed, 0)

	err = validateInstallPackAttributionPreflight(context.Background(), db, pack, nil)
	if err == nil {
		t.Fatal("validateInstallPackAttributionPreflight = nil, want failed foreign pack error")
	}
	if !strings.Contains(err.Error(), `status is "failed"`) {
		t.Fatalf("error = %v, want failed status", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	pack := testControlplaneSourceBackedPack(t)
	expectInstalledPackRuntimeDeclarationColumns(mock, false)
	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
		WithArgs(string(packs.AppPack), string(packs.ConnectorPack)).
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			"other-pack",
			string(packs.AppPack),
			installedPackStatusActive,
			"0.1.0",
			packs.SupportedManifestFormatVersion,
			`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
			packs.PackPayloadHashV1Prefix+"bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb",
			packs.PackPayloadHashV1Prefix+"bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb",
			packs.PackPayloadHashAlgorithmV1,
			nil,
			nil,
			nil,
			nil,
		))
	expectInstalledPackStateLookup(mock, "other-pack", installedPackStatusActive, 0)

	err = validateInstallPackAttributionPreflight(context.Background(), db, pack, nil)
	if err == nil {
		t.Fatal("validateInstallPackAttributionPreflight = nil, want missing source identity error")
	}
	if !strings.Contains(err.Error(), "has no source identity") {
		t.Fatalf("error = %v, want missing source identity", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	requestedPack := testControlplaneSourceBackedPack(t)
	peerRoot, peerPack := writeNamedFieldDeclarationSource(t, "peer-pack", "PeerObject", "PeerField__c")

	expectInstalledPackRuntimeDeclarationColumns(mock, false)
	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
		WithArgs(string(packs.AppPack), string(packs.ConnectorPack)).
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			"peer-pack",
			string(packs.AppPack),
			installedPackStatusActive,
			"0.1.0",
			packs.SupportedManifestFormatVersion,
			`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
			packs.PackPayloadHashV1Prefix+"dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd",
			packs.PackPayloadHashV1Prefix+"dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd",
			packs.PackPayloadHashAlgorithmV1,
			packs.PackSourceKindLocalPath,
			peerRoot,
			nil,
			nil,
		))
	expectInstalledPackStateLookup(mock, "peer-pack", installedPackStatusActive, 0)
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Object o WHERE").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Table t WHERE").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId WHERE").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	expectZeroInstallOwnerSurfaceCounts(mock)
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM FlowDefinition fd").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))

	err = validateInstallPackAttributionPreflight(context.Background(), db, requestedPack, &installPackPreflightOverrides{
		activeDriftedPacks: map[string]*packs.SourceBackedPack{
			"peer-pack": peerPack,
		},
	})
	if err != nil {
		t.Fatalf("validateInstallPackAttributionPreflight reconcile drift override: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestRuntimeDeclarationOverridesFromPreflightExcludingPackCanonicalizesAndExcludesRequested(t *testing.T) {
	_, requestedPack := writeNamedFieldDeclarationSource(t, "requested-pack", "RequestedObject", "RequestedField__c")
	_, peerPack := writeNamedFieldDeclarationSource(t, "peer-pack", "PeerObject", "PeerField__c")

	overrides := runtimeDeclarationOverridesFromPreflightExcludingPack(&installPackPreflightOverrides{
		activeDriftedPacks: map[string]*packs.SourceBackedPack{
			" REQUESTED-PACK ": requestedPack,
			" PEER-PACK ":      peerPack,
		},
	}, " Requested-Pack ")
	if len(overrides) != 1 || overrides["peer-pack"] != peerPack {
		t.Fatalf("overrides = %#v, want only canonical peer-pack", overrides)
	}
}

func TestValidateInstallPackAttributionPreflightResolvesActivePeersWithRuntimeFormulaBundles(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()
	mock.MatchExpectationsInOrder(false)
	_, runnerOptions := runnerManagedVerifyFixture(t)

	providerRoot := writeFormulaFunctionProviderPackSource(t, "formula-provider-pack")
	providerPack, err := packs.ResolveInstalledSourceBackedPack(
		"formula-provider-pack",
		packs.AppPack,
		string(packs.PackSourceKindLocalPath),
		providerRoot,
	)
	if err != nil {
		t.Fatalf("resolve provider pack: %v", err)
	}
	providerHash, err := packs.PackPayloadHash(providerPack)
	if err != nil {
		t.Fatalf("provider payload hash: %v", err)
	}
	providerDecl, err := packs.BuildPersistedRuntimeDeclaration(providerPack)
	if err != nil {
		t.Fatalf("provider runtime declaration: %v", err)
	}

	consumerRoot := writeFormulaFunctionConsumerPackSource(t, "formula-consumer-pack")
	addRunnerManagedTestComponentToPackSource(t, consumerRoot)
	consumerReport := packs.ValidateInstallablePackSourceWithRuntimeFormulaFunctionBundlesAndComponentKindRegistrationSet(
		"local_path:"+consumerRoot,
		packs.DefaultPackSourceOptions(),
		providerPack.RuntimeFormulaFunctionBundles(),
		runnerOptions.ComponentKindRegistrationSet,
	)
	if consumerReport.HasErrors() {
		t.Fatalf("consumer source diagnostics:\n%s", consumerReport.Error())
	}
	consumerPack := consumerReport.Pack
	if consumerPack == nil {
		t.Fatal("consumer source did not produce an installable pack")
	}
	consumerHash, err := packs.PackPayloadHash(consumerPack)
	if err != nil {
		t.Fatalf("consumer payload hash: %v", err)
	}
	consumerMetadata := packs.PackHashMetadataForPack(consumerPack)
	consumerDecl, err := packs.BuildPersistedRuntimeDeclaration(consumerPack)
	if err != nil {
		t.Fatalf("consumer runtime declaration: %v", err)
	}

	_, requestedPack := writeNamedFieldDeclarationSource(t, "unrelated-pack", "UnrelatedIssue", "ExternalRef")

	expectInstalledPackRuntimeDeclarationColumns(mock, true)
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("_System_InstalledPack", "RuntimeStartupShallowDeclarations").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("_System_InstalledPack", "RuntimeFormulaFunctionBundles").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))
	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision, HostRequirementsDigest, RuntimeObjectSpecDeclarations, RuntimeRelatedListRules, RuntimeSearchScopeOrder, RuntimeSearchAllDefaultFields, RuntimeFormulaFunctionBundles, RuntimeStartupShallowDeclarations").
		WithArgs(string(packs.AppPack), string(packs.ConnectorPack)).
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
			"RuntimeObjectSpecDeclarations", "RuntimeRelatedListRules", "RuntimeSearchScopeOrder", "RuntimeSearchAllDefaultFields", "RuntimeFormulaFunctionBundles", "RuntimeStartupShallowDeclarations",
		}).AddRow(
			consumerPack.Name(),
			string(packs.AppPack),
			installedPackStatusActive,
			"0.1.0",
			consumerMetadata.ManifestFormatVersion,
			`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
			consumerHash,
			consumerHash,
			packs.PackPayloadHashAlgorithmV1,
			packs.PackSourceKindLocalPath,
			consumerRoot,
			nil,
			nil,
			consumerDecl.ObjectPayloadsJSON(),
			consumerDecl.RelatedListRulesJSON(),
			consumerDecl.SearchScopeOrderJSON(),
			consumerDecl.SearchAllDefaultFieldsJSON(),
			consumerDecl.FormulaFunctionBundlesJSON(),
			consumerDecl.StartupShallowDeclaredRowsJSON(),
		).AddRow(
			providerPack.Name(),
			string(packs.AppPack),
			installedPackStatusActive,
			"0.1.0",
			packs.SupportedManifestFormatVersion,
			`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
			providerHash,
			providerHash,
			packs.PackPayloadHashAlgorithmV1,
			packs.PackSourceKindLocalPath,
			providerRoot,
			nil,
			nil,
			providerDecl.ObjectPayloadsJSON(),
			providerDecl.RelatedListRulesJSON(),
			providerDecl.SearchScopeOrderJSON(),
			providerDecl.SearchAllDefaultFieldsJSON(),
			providerDecl.FormulaFunctionBundlesJSON(),
			providerDecl.StartupShallowDeclaredRowsJSON(),
		))
	expectActiveInstalledPackRuntimeDeclarationsWithFormulaBundles(mock, []activeRuntimeFormulaBundleRow{
		{pack: consumerPack, sourceDir: consumerRoot, payloadHash: consumerHash, decl: consumerDecl},
		{pack: providerPack, sourceDir: providerRoot, payloadHash: providerHash, decl: providerDecl},
	})
	expectInstalledPackStateLookup(mock, consumerPack.Name(), installedPackStatusActive, 0)
	expectInstalledPackStateLookup(mock, providerPack.Name(), installedPackStatusActive, 0)
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Object o WHERE").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Table t WHERE").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId WHERE").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	expectZeroInstallOwnerSurfaceCounts(mock)
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM FlowDefinition fd").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))

	if err := validateInstallPackAttributionPreflightWithComponentKindRegistrationSet(context.Background(), db, requestedPack, nil, runnerOptions.ComponentKindRegistrationSet); err != nil {
		t.Fatalf("validateInstallPackAttributionPreflight: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	oldProviderRoot := writeFormulaFunctionProviderPackSourceWithFunctionName(t, "formula-provider-pack", "OLD_PREFIX")
	oldProviderPack, err := packs.ResolveInstalledSourceBackedPack(
		"formula-provider-pack",
		packs.AppPack,
		string(packs.PackSourceKindLocalPath),
		oldProviderRoot,
	)
	if err != nil {
		t.Fatalf("resolve old provider pack: %v", err)
	}
	oldProviderHash, err := packs.PackPayloadHash(oldProviderPack)
	if err != nil {
		t.Fatalf("old provider payload hash: %v", err)
	}
	oldProviderDecl, err := packs.BuildPersistedRuntimeDeclaration(oldProviderPack)
	if err != nil {
		t.Fatalf("old provider runtime declaration: %v", err)
	}

	providerRoot := writeFormulaFunctionProviderPackSource(t, "formula-provider-pack")
	providerPack, err := packs.ResolveInstalledSourceBackedPack(
		"formula-provider-pack",
		packs.AppPack,
		string(packs.PackSourceKindLocalPath),
		providerRoot,
	)
	if err != nil {
		t.Fatalf("resolve provider pack: %v", err)
	}

	consumerRoot := writeFormulaFunctionConsumerPackSource(t, "formula-consumer-pack")
	consumerReport := packs.ValidateInstallablePackSourceWithRuntimeFormulaFunctionBundles(
		"local_path:"+consumerRoot,
		packs.DefaultPackSourceOptions(),
		providerPack.RuntimeFormulaFunctionBundles(),
	)
	if consumerReport.HasErrors() {
		t.Fatalf("consumer source diagnostics:\n%s", consumerReport.Error())
	}
	consumerPack := consumerReport.Pack
	if consumerPack == nil {
		t.Fatal("consumer source did not produce an installable pack")
	}
	consumerHash, err := packs.PackPayloadHash(consumerPack)
	if err != nil {
		t.Fatalf("consumer payload hash: %v", err)
	}
	consumerDecl, err := packs.BuildPersistedRuntimeDeclaration(consumerPack)
	if err != nil {
		t.Fatalf("consumer runtime declaration: %v", err)
	}

	_, requestedPack := writeNamedFieldDeclarationSource(t, "unrelated-pack", "UnrelatedIssue", "ExternalRef")

	expectInstalledPackRuntimeDeclarationColumns(mock, true)
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("_System_InstalledPack", "RuntimeStartupShallowDeclarations").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("_System_InstalledPack", "RuntimeFormulaFunctionBundles").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))
	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision, HostRequirementsDigest, RuntimeObjectSpecDeclarations, RuntimeRelatedListRules, RuntimeSearchScopeOrder, RuntimeSearchAllDefaultFields, RuntimeFormulaFunctionBundles, RuntimeStartupShallowDeclarations").
		WithArgs(string(packs.AppPack), string(packs.ConnectorPack)).
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
			"RuntimeObjectSpecDeclarations", "RuntimeRelatedListRules", "RuntimeSearchScopeOrder", "RuntimeSearchAllDefaultFields", "RuntimeFormulaFunctionBundles", "RuntimeStartupShallowDeclarations",
		}).AddRow(
			consumerPack.Name(),
			string(packs.AppPack),
			installedPackStatusActive,
			"0.1.0",
			packs.SupportedManifestFormatVersion,
			`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
			consumerHash,
			consumerHash,
			packs.PackPayloadHashAlgorithmV1,
			packs.PackSourceKindLocalPath,
			consumerRoot,
			nil,
			nil,
			consumerDecl.ObjectPayloadsJSON(),
			consumerDecl.RelatedListRulesJSON(),
			consumerDecl.SearchScopeOrderJSON(),
			consumerDecl.SearchAllDefaultFieldsJSON(),
			consumerDecl.FormulaFunctionBundlesJSON(),
			consumerDecl.StartupShallowDeclaredRowsJSON(),
		).AddRow(
			oldProviderPack.Name(),
			string(packs.AppPack),
			installedPackStatusActive,
			"0.1.0",
			packs.SupportedManifestFormatVersion,
			`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
			oldProviderHash,
			oldProviderHash,
			packs.PackPayloadHashAlgorithmV1,
			packs.PackSourceKindLocalPath,
			oldProviderRoot,
			nil,
			nil,
			oldProviderDecl.ObjectPayloadsJSON(),
			oldProviderDecl.RelatedListRulesJSON(),
			oldProviderDecl.SearchScopeOrderJSON(),
			oldProviderDecl.SearchAllDefaultFieldsJSON(),
			oldProviderDecl.FormulaFunctionBundlesJSON(),
			oldProviderDecl.StartupShallowDeclaredRowsJSON(),
		))
	expectActiveInstalledPackRuntimeDeclarationsWithFormulaBundles(mock, []activeRuntimeFormulaBundleRow{
		{
			pack:        consumerPack,
			sourceDir:   consumerRoot,
			payloadHash: consumerHash,
			decl:        consumerDecl,
		},
		{
			pack:        oldProviderPack,
			sourceDir:   oldProviderRoot,
			payloadHash: oldProviderHash,
			decl:        oldProviderDecl,
		},
	})
	expectInstalledPackStateLookup(mock, consumerPack.Name(), installedPackStatusActive, 0)
	expectInstalledPackStateLookup(mock, oldProviderPack.Name(), installedPackStatusActive, 0)
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Object o WHERE").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Table t WHERE").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId WHERE").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	expectZeroInstallOwnerSurfaceCounts(mock)
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM FlowDefinition fd").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))

	err = validateInstallPackAttributionPreflight(context.Background(), db, requestedPack, &installPackPreflightOverrides{
		activeDriftedPacks: map[string]*packs.SourceBackedPack{
			"formula-provider-pack": providerPack,
		},
	})
	if err != nil {
		t.Fatalf("validateInstallPackAttributionPreflight with drift override: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	origVerifyResolved := verifyResolvedSourceBackedPackForReconcile
	origPublishGeneration := publishFirstPackGenerationForReconcile
	defer func() {
		verifyResolvedSourceBackedPackForReconcile = origVerifyResolved
		publishFirstPackGenerationForReconcile = origPublishGeneration
	}()
	verifyResolvedSourceBackedPackForReconcile = func(context.Context, installedPackRow, *sql.DB, *packs.SourceBackedPack) error {
		return nil
	}
	publishFirstPackGenerationForReconcile = func(context.Context, *sql.DB, appliedAppPack) error {
		return nil
	}

	providerRoot := writeFormulaFunctionProviderPackSource(t, "formula-provider-pack")
	providerPack, err := packs.ResolveInstalledSourceBackedPack(
		"formula-provider-pack",
		packs.AppPack,
		string(packs.PackSourceKindLocalPath),
		providerRoot,
	)
	if err != nil {
		t.Fatalf("resolve provider pack: %v", err)
	}
	providerHash, err := packs.PackPayloadHash(providerPack)
	if err != nil {
		t.Fatalf("provider payload hash: %v", err)
	}
	providerDecl, err := packs.BuildPersistedRuntimeDeclaration(providerPack)
	if err != nil {
		t.Fatalf("provider runtime declaration: %v", err)
	}

	consumerRoot := writeFormulaFunctionConsumerPackSource(t, "formula-consumer-pack")
	consumerReport := packs.ValidateInstallablePackSourceWithRuntimeFormulaFunctionBundles(
		"local_path:"+consumerRoot,
		packs.DefaultPackSourceOptions(),
		providerPack.RuntimeFormulaFunctionBundles(),
	)
	if consumerReport.HasErrors() {
		t.Fatalf("consumer source diagnostics:\n%s", consumerReport.Error())
	}
	consumerPack := consumerReport.Pack
	if consumerPack == nil {
		t.Fatal("consumer source did not produce an installable pack")
	}
	consumerHash, err := packs.PackPayloadHash(consumerPack)
	if err != nil {
		t.Fatalf("consumer payload hash: %v", err)
	}

	expectActiveInstalledPackRuntimeDeclarationWithFormulaBundles(mock, providerPack, providerRoot, providerHash, providerDecl)

	rows := []installedPackRow{
		activeFormulaBundleSourceBackedRow(consumerPack.Name(), consumerRoot, consumerHash),
		activeFormulaBundleSourceBackedRow(providerPack.Name(), providerRoot, providerHash),
	}
	runner := &Runner{DB: database.NewConnectionFromDB(rawDB)}
	if err := verifyActiveInstalledPackSourceState(context.Background(), runner, rows, InstallPackOptions{}); err != nil {
		t.Fatalf("verifyActiveInstalledPackSourceState: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	root := filepath.Join(t.TempDir(), "standard-action-pack")
	if err := os.MkdirAll(root, 0o755); err != nil {
		t.Fatalf("mkdir standard-action pack: %v", err)
	}
	manifest := `{
  "manifestFormatVersion":"1",
  "name":"standard-action-pack",
  "displayName":"Standard Action Pack",
  "category":"app-pack",
  "version":"0.1.0",
  "platformCompatibility":{"min":"0.2.0","maxExclusive":"0.3.0"},
  "components":[
    {"kind":"standard_flow","businessKey":"Test_Action_Flow","manageability":"defaults_only","payload":{"label":"Test Action Flow","status":"Active"}},
    {"kind":"invocable_action","businessKey":"test_actions","manageability":"defaults_only","dependsOn":[{"kind":"standard_flow","businessKey":"Test_Action_Flow"}],"payload":[{"name":"testAction","label":"Test Action","type":"flow","objectApiName":"WorkItem","config":{"flowDeveloperName":"Test_Action_Flow"}}]}
  ]
}`
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write standard-action pack: %v", err)
	}
	pack, err := packs.ResolveInstalledSourceBackedPack(
		"standard-action-pack", packs.AppPack, string(packs.PackSourceKindLocalPath), root,
	)
	if err != nil {
		t.Fatalf("resolve standard-action pack: %v", err)
	}
	payloadHash, err := packs.PackPayloadHash(pack)
	if err != nil {
		t.Fatalf("standard-action pack payload hash: %v", err)
	}

	origRebuild := rebuildRepairPackWithAllowedOwnerlessObjectsAndRegistrationsForReconcile
	origApply := applyResolvedSourceBackedPackUnlockedForReconcile
	origVerify := verifyResolvedSourceBackedPackForReconcile
	defer func() {
		rebuildRepairPackWithAllowedOwnerlessObjectsAndRegistrationsForReconcile = origRebuild
		applyResolvedSourceBackedPackUnlockedForReconcile = origApply
		verifyResolvedSourceBackedPackForReconcile = origVerify
	}()
	rebuildRepairPackWithAllowedOwnerlessObjectsAndRegistrationsForReconcile = func(
		_ context.Context, _ *sql.DB, _ installedPackRow, got *packs.SourceBackedPack,
		_ InstallPackOptions, _ *installPackPreflightOverrides, _ packs.ComponentKindRegistrationSet,
	) (*packs.SourceBackedPack, error) {
		return got, nil
	}
	applied := 0
	applyResolvedSourceBackedPackUnlockedForReconcile = func(
		_ context.Context, _ *Runner, got *packs.SourceBackedPack,
		_ *installPackPreflightOverrides, options InstallPackOptions,
	) error {
		applied++
		if got.Name() != pack.Name() || !options.replayStandardActionPersistenceSemantics {
			t.Fatalf("replay pack/options = %s/%v", got.Name(), options.replayStandardActionPersistenceSemantics)
		}
		return nil
	}
	verifyResolvedSourceBackedPackForReconcile = func(context.Context, installedPackRow, *sql.DB, *packs.SourceBackedPack) error {
		return &packs.ComponentVerifyError{
			Kind: packs.ComponentKindInvocableAction, BusinessKey: "test_actions",
			Err: &packs.StandardActionPersistenceDriftError{Details: []string{
				"standard action testAction schema input/attachments IsCollection = false, want true",
			}},
		}
	}

	row := activeFormulaBundleSourceBackedRow(pack.Name(), root, payloadHash)
	runner := &Runner{DB: database.NewConnectionFromDB(rawDB)}
	if err := verifyActiveInstalledPackSourceState(context.Background(), runner, []installedPackRow{row}, InstallPackOptions{
		replayStandardActionPersistenceSemantics: true,
	}); err != nil {
		t.Fatalf("verify active standard-action pack: %v", err)
	}
	if applied != 1 {
		t.Fatalf("standard-action persistence replay count = %d, want 1", applied)
	}
}

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

	oldProviderRoot := writeFormulaFunctionProviderPackSourceWithFunctionName(t, "formula-provider-pack", "OLD_PREFIX")
	oldProviderPack, err := packs.ResolveInstalledSourceBackedPack(
		"formula-provider-pack",
		packs.AppPack,
		string(packs.PackSourceKindLocalPath),
		oldProviderRoot,
	)
	if err != nil {
		t.Fatalf("resolve old provider pack: %v", err)
	}
	oldProviderHash, err := packs.PackPayloadHash(oldProviderPack)
	if err != nil {
		t.Fatalf("old provider payload hash: %v", err)
	}
	oldProviderDecl, err := packs.BuildPersistedRuntimeDeclaration(oldProviderPack)
	if err != nil {
		t.Fatalf("old provider runtime declaration: %v", err)
	}

	providerRoot := writeFormulaFunctionProviderPackSource(t, "formula-provider-pack")
	providerPack, err := packs.ResolveInstalledSourceBackedPack(
		"formula-provider-pack",
		packs.AppPack,
		string(packs.PackSourceKindLocalPath),
		providerRoot,
	)
	if err != nil {
		t.Fatalf("resolve provider pack: %v", err)
	}

	consumerRoot := writeFormulaFunctionConsumerPackSource(t, "formula-consumer-pack")
	consumerReport := packs.ValidateInstallablePackSourceWithRuntimeFormulaFunctionBundles(
		"local_path:"+consumerRoot,
		packs.DefaultPackSourceOptions(),
		providerPack.RuntimeFormulaFunctionBundles(),
	)
	if consumerReport.HasErrors() {
		t.Fatalf("consumer source diagnostics:\n%s", consumerReport.Error())
	}
	consumerPack := consumerReport.Pack
	if consumerPack == nil {
		t.Fatal("consumer source did not produce an installable pack")
	}
	consumerHash, err := packs.PackPayloadHash(consumerPack)
	if err != nil {
		t.Fatalf("consumer payload hash: %v", err)
	}
	consumerDecl, err := packs.BuildPersistedRuntimeDeclaration(consumerPack)
	if err != nil {
		t.Fatalf("consumer runtime declaration: %v", err)
	}
	staleConsumerHash := packs.PackPayloadHashV1Prefix + strings.Repeat("1", 64)
	if staleConsumerHash == consumerHash {
		t.Fatal("test setup: stale consumer hash matches current consumer hash")
	}

	expectActiveInstalledPackRuntimeDeclarationsWithFormulaBundles(mock, []activeRuntimeFormulaBundleRow{
		{
			pack:        consumerPack,
			sourceDir:   consumerRoot,
			payloadHash: staleConsumerHash,
			decl:        consumerDecl,
		},
		{
			pack:        oldProviderPack,
			sourceDir:   providerRoot,
			payloadHash: oldProviderHash,
			decl:        oldProviderDecl,
		},
	})

	origInspect := inspectLiveInstalledPackMissingPhysicalColumnsForReconcile
	origRebuild := rebuildRepairPackWithAllowedOwnerlessObjectsForReconcile
	origApply := applyResolvedSourceBackedPackUnlockedForReconcile
	defer func() {
		inspectLiveInstalledPackMissingPhysicalColumnsForReconcile = origInspect
		rebuildRepairPackWithAllowedOwnerlessObjectsForReconcile = origRebuild
		applyResolvedSourceBackedPackUnlockedForReconcile = origApply
	}()
	inspectLiveInstalledPackMissingPhysicalColumnsForReconcile = func(context.Context, *sql.DB, string) ([]string, error) {
		return nil, nil
	}
	rebuildRepairPackWithAllowedOwnerlessObjectsForReconcile = func(_ context.Context, _ *sql.DB, _ installedPackRow, pack *packs.SourceBackedPack, _ InstallPackOptions, _ *installPackPreflightOverrides) (*packs.SourceBackedPack, error) {
		return pack, nil
	}
	applied := map[string]bool{}
	applyResolvedSourceBackedPackUnlockedForReconcile = func(_ context.Context, _ *Runner, pack *packs.SourceBackedPack, overrides *installPackPreflightOverrides, _ InstallPackOptions) error {
		if overrides == nil {
			t.Fatal("apply overrides = nil, want drift batch overrides")
		}
		for _, want := range []string{"formula-consumer-pack", "formula-provider-pack"} {
			if _, ok := overrides.activeDriftedPacks[want]; !ok {
				t.Fatalf("activeDriftedPacks missing %s", want)
			}
		}
		applied[pack.Name()] = true
		return nil
	}

	rows := []installedPackRow{
		activeFormulaBundleSourceBackedRow(consumerPack.Name(), consumerRoot, staleConsumerHash),
		activeFormulaBundleSourceBackedRow(providerPack.Name(), providerRoot, oldProviderHash),
	}
	runner := &Runner{DB: database.NewConnectionFromDB(rawDB)}
	if err := repairActiveInstalledPackSourceHashDrift(context.Background(), runner, rows, InstallPackOptions{}); err != nil {
		t.Fatalf("repairActiveInstalledPackSourceHashDrift: %v", err)
	}
	for _, want := range []string{"formula-consumer-pack", "formula-provider-pack"} {
		if !applied[want] {
			t.Fatalf("pack %s was not reapplied", want)
		}
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	oldProviderRoot := writeFormulaFunctionProviderPackSourceWithFunctionName(t, "formula-provider-pack", "OLD_PREFIX")
	oldProviderPack, err := packs.ResolveInstalledSourceBackedPack(
		"formula-provider-pack",
		packs.AppPack,
		string(packs.PackSourceKindLocalPath),
		oldProviderRoot,
	)
	if err != nil {
		t.Fatalf("resolve old provider pack: %v", err)
	}
	oldProviderHash, err := packs.PackPayloadHash(oldProviderPack)
	if err != nil {
		t.Fatalf("old provider payload hash: %v", err)
	}
	oldProviderDecl, err := packs.BuildPersistedRuntimeDeclaration(oldProviderPack)
	if err != nil {
		t.Fatalf("old provider runtime declaration: %v", err)
	}

	providerRoot := writeFormulaFunctionProviderPackSource(t, "formula-provider-pack")
	providerPack, err := packs.ResolveInstalledSourceBackedPack(
		"formula-provider-pack",
		packs.AppPack,
		string(packs.PackSourceKindLocalPath),
		providerRoot,
	)
	if err != nil {
		t.Fatalf("resolve provider pack: %v", err)
	}

	consumerRoot := writeFormulaFunctionConsumerPackSource(t, "formula-consumer-pack")
	consumerReport := packs.ValidateInstallablePackSourceWithRuntimeFormulaFunctionBundles(
		"local_path:"+consumerRoot,
		packs.DefaultPackSourceOptions(),
		providerPack.RuntimeFormulaFunctionBundles(),
	)
	if consumerReport.HasErrors() {
		t.Fatalf("consumer source diagnostics:\n%s", consumerReport.Error())
	}
	consumerPack := consumerReport.Pack
	if consumerPack == nil {
		t.Fatal("consumer source did not produce an installable pack")
	}
	consumerHash, err := packs.PackPayloadHash(consumerPack)
	if err != nil {
		t.Fatalf("consumer payload hash: %v", err)
	}
	consumerDecl, err := packs.BuildPersistedRuntimeDeclaration(consumerPack)
	if err != nil {
		t.Fatalf("consumer runtime declaration: %v", err)
	}
	staleConsumerHash := packs.PackPayloadHashV1Prefix + strings.Repeat("3", 64)
	if staleConsumerHash == consumerHash {
		t.Fatal("test setup: stale consumer hash matches current consumer hash")
	}

	expectActiveInstalledPackRuntimeDeclarationsWithFormulaBundles(mock, []activeRuntimeFormulaBundleRow{
		{
			pack:        consumerPack,
			sourceDir:   consumerRoot,
			payloadHash: staleConsumerHash,
			decl:        consumerDecl,
		},
		{
			pack:        oldProviderPack,
			sourceDir:   providerRoot,
			payloadHash: oldProviderHash,
			decl:        oldProviderDecl,
		},
	})
	consumerRow := activeFormulaBundleSourceBackedRow(consumerPack.Name(), consumerRoot, staleConsumerHash)
	consumerMeta := packs.PackHashMetadataForPack(consumerPack)
	consumerRow.SourceKind = sql.NullString{String: consumerMeta.SourceKind, Valid: true}
	consumerRow.SourceURI = sql.NullString{String: consumerMeta.SourceURI, Valid: true}
	providerRow := activeFormulaBundleSourceBackedRow(providerPack.Name(), providerRoot, oldProviderHash)
	providerMeta := packs.PackHashMetadataForPack(providerPack)
	providerRow.SourceKind = sql.NullString{String: providerMeta.SourceKind, Valid: true}
	providerRow.SourceURI = sql.NullString{String: providerMeta.SourceURI, Valid: true}
	expectInstalledPackRetryAllowanceRow(mock, providerRow)
	expectInstalledPackRetryAllowanceRow(mock, consumerRow)
	expectActiveInstalledPackRuntimeDeclarationsWithFormulaBundles(mock, []activeRuntimeFormulaBundleRow{
		{
			pack:        consumerPack,
			sourceDir:   consumerRoot,
			payloadHash: staleConsumerHash,
			decl:        consumerDecl,
		},
		{
			pack:        oldProviderPack,
			sourceDir:   providerRoot,
			payloadHash: oldProviderHash,
			decl:        oldProviderDecl,
		},
	})

	origInspect := inspectLiveInstalledPackMissingPhysicalColumnsForReconcile
	origApply := applyResolvedSourceBackedPackUnlockedForReconcile
	defer func() {
		inspectLiveInstalledPackMissingPhysicalColumnsForReconcile = origInspect
		applyResolvedSourceBackedPackUnlockedForReconcile = origApply
	}()
	inspectLiveInstalledPackMissingPhysicalColumnsForReconcile = func(context.Context, *sql.DB, string) ([]string, error) {
		return nil, nil
	}
	applied := map[string]bool{}
	applyResolvedSourceBackedPackUnlockedForReconcile = func(_ context.Context, _ *Runner, pack *packs.SourceBackedPack, overrides *installPackPreflightOverrides, _ InstallPackOptions) error {
		if overrides == nil {
			t.Fatal("apply overrides = nil, want drift batch overrides")
		}
		for _, want := range []string{"formula-consumer-pack", "formula-provider-pack"} {
			if _, ok := overrides.activeDriftedPacks[want]; !ok {
				t.Fatalf("activeDriftedPacks missing %s", want)
			}
		}
		applied[pack.Name()] = true
		return nil
	}

	rows := []installedPackRow{
		activeFormulaBundleSourceBackedRow(consumerPack.Name(), consumerRoot, staleConsumerHash),
		activeFormulaBundleSourceBackedRow(providerPack.Name(), providerRoot, oldProviderHash),
	}
	runner := &Runner{DB: database.NewConnectionFromDB(rawDB)}
	if err := repairActiveInstalledPackSourceHashDrift(context.Background(), runner, rows, InstallPackOptions{}); err != nil {
		t.Fatalf("repairActiveInstalledPackSourceHashDrift: %v", err)
	}
	if !applied["formula-consumer-pack"] {
		t.Fatal("formula-consumer-pack was not reapplied")
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	oldProviderRoot := writeFormulaFunctionProviderPackSourceWithFunctionName(t, "formula-provider-pack", "OLD_A_PREFIX")
	oldProviderPack, err := packs.ResolveInstalledSourceBackedPack(
		"formula-provider-pack",
		packs.AppPack,
		string(packs.PackSourceKindLocalPath),
		oldProviderRoot,
	)
	if err != nil {
		t.Fatalf("resolve old provider pack: %v", err)
	}
	oldProviderHash, err := packs.PackPayloadHash(oldProviderPack)
	if err != nil {
		t.Fatalf("old provider payload hash: %v", err)
	}
	oldProviderDecl, err := packs.BuildPersistedRuntimeDeclaration(oldProviderPack)
	if err != nil {
		t.Fatalf("old provider runtime declaration: %v", err)
	}

	providerRoot := writeFormulaFunctionProviderPackSourceWithFunctionName(t, "formula-provider-pack", "A_PREFIX")
	providerPack, err := packs.ResolveInstalledSourceBackedPack(
		"formula-provider-pack",
		packs.AppPack,
		string(packs.PackSourceKindLocalPath),
		providerRoot,
	)
	if err != nil {
		t.Fatalf("resolve provider pack: %v", err)
	}

	oldIntermediateRoot := writeFormulaFunctionTransitivePackSource(t, "formula-intermediate-pack", "formula-provider-pack", "OLD_A_PREFIX", "OLD_B_PREFIX")
	oldIntermediateReport := packs.ValidateInstallablePackSourceWithRuntimeFormulaFunctionBundles(
		"local_path:"+oldIntermediateRoot,
		packs.DefaultPackSourceOptions(),
		oldProviderPack.RuntimeFormulaFunctionBundles(),
	)
	if oldIntermediateReport.HasErrors() {
		t.Fatalf("old intermediate source diagnostics:\n%s", oldIntermediateReport.Error())
	}
	oldIntermediatePack := oldIntermediateReport.Pack
	if oldIntermediatePack == nil {
		t.Fatal("old intermediate source did not produce an installable pack")
	}
	oldIntermediateHash, err := packs.PackPayloadHash(oldIntermediatePack)
	if err != nil {
		t.Fatalf("old intermediate payload hash: %v", err)
	}
	oldIntermediateDecl, err := packs.BuildPersistedRuntimeDeclaration(oldIntermediatePack)
	if err != nil {
		t.Fatalf("old intermediate runtime declaration: %v", err)
	}

	intermediateRoot := writeFormulaFunctionTransitivePackSource(t, "formula-intermediate-pack", "formula-provider-pack", "A_PREFIX", "B_PREFIX")
	intermediateReport := packs.ValidateInstallablePackSourceWithRuntimeFormulaFunctionBundles(
		"local_path:"+intermediateRoot,
		packs.DefaultPackSourceOptions(),
		providerPack.RuntimeFormulaFunctionBundles(),
	)
	if intermediateReport.HasErrors() {
		t.Fatalf("intermediate source diagnostics:\n%s", intermediateReport.Error())
	}
	intermediatePack := intermediateReport.Pack
	if intermediatePack == nil {
		t.Fatal("intermediate source did not produce an installable pack")
	}
	consumerRoot := writeFormulaFunctionConsumerPackSourceWithDependencyFunction(t, "formula-consumer-pack", "formula-intermediate-pack", "B_PREFIX")
	consumerReport := packs.ValidateInstallablePackSourceWithRuntimeFormulaFunctionBundles(
		"local_path:"+consumerRoot,
		packs.DefaultPackSourceOptions(),
		intermediatePack.RuntimeFormulaFunctionBundles(),
	)
	if consumerReport.HasErrors() {
		t.Fatalf("consumer source diagnostics:\n%s", consumerReport.Error())
	}
	consumerPack := consumerReport.Pack
	if consumerPack == nil {
		t.Fatal("consumer source did not produce an installable pack")
	}
	consumerHash, err := packs.PackPayloadHash(consumerPack)
	if err != nil {
		t.Fatalf("consumer payload hash: %v", err)
	}
	staleConsumerHash := packs.PackPayloadHashV1Prefix + strings.Repeat("2", 64)
	if staleConsumerHash == consumerHash {
		t.Fatal("test setup: stale consumer hash matches current consumer hash")
	}

	expectActiveInstalledPackRuntimeDeclarationsWithFormulaBundles(mock, []activeRuntimeFormulaBundleRow{
		{
			pack:        oldProviderPack,
			sourceDir:   providerRoot,
			payloadHash: oldProviderHash,
			decl:        oldProviderDecl,
		},
		{
			pack:        oldIntermediatePack,
			sourceDir:   intermediateRoot,
			payloadHash: oldIntermediateHash,
			decl:        oldIntermediateDecl,
		},
	})
	expectActiveInstalledPackRuntimeDeclarationsWithFormulaBundles(mock, []activeRuntimeFormulaBundleRow{
		{
			pack:        oldProviderPack,
			sourceDir:   providerRoot,
			payloadHash: oldProviderHash,
			decl:        oldProviderDecl,
		},
		{
			pack:        oldIntermediatePack,
			sourceDir:   intermediateRoot,
			payloadHash: oldIntermediateHash,
			decl:        oldIntermediateDecl,
		},
	})

	origInspect := inspectLiveInstalledPackMissingPhysicalColumnsForReconcile
	origRebuild := rebuildRepairPackWithAllowedOwnerlessObjectsForReconcile
	origApply := applyResolvedSourceBackedPackUnlockedForReconcile
	defer func() {
		inspectLiveInstalledPackMissingPhysicalColumnsForReconcile = origInspect
		rebuildRepairPackWithAllowedOwnerlessObjectsForReconcile = origRebuild
		applyResolvedSourceBackedPackUnlockedForReconcile = origApply
	}()
	inspectLiveInstalledPackMissingPhysicalColumnsForReconcile = func(context.Context, *sql.DB, string) ([]string, error) {
		return nil, nil
	}
	rebuildRepairPackWithAllowedOwnerlessObjectsForReconcile = func(_ context.Context, _ *sql.DB, _ installedPackRow, pack *packs.SourceBackedPack, _ InstallPackOptions, _ *installPackPreflightOverrides) (*packs.SourceBackedPack, error) {
		return pack, nil
	}
	applied := map[string]bool{}
	applyResolvedSourceBackedPackUnlockedForReconcile = func(_ context.Context, _ *Runner, pack *packs.SourceBackedPack, overrides *installPackPreflightOverrides, _ InstallPackOptions) error {
		if overrides == nil {
			t.Fatal("apply overrides = nil, want drift batch overrides")
		}
		for _, want := range []string{"formula-provider-pack", "formula-intermediate-pack", "formula-consumer-pack"} {
			if _, ok := overrides.activeDriftedPacks[want]; !ok {
				t.Fatalf("activeDriftedPacks missing %s", want)
			}
		}
		applied[pack.Name()] = true
		return nil
	}

	rows := []installedPackRow{
		activeFormulaBundleSourceBackedRow(intermediatePack.Name(), intermediateRoot, oldIntermediateHash),
		activeFormulaBundleSourceBackedRow(consumerPack.Name(), consumerRoot, staleConsumerHash),
		activeFormulaBundleSourceBackedRow(providerPack.Name(), providerRoot, oldProviderHash),
	}
	runner := &Runner{DB: database.NewConnectionFromDB(rawDB)}
	if err := repairActiveInstalledPackSourceHashDrift(context.Background(), runner, rows, InstallPackOptions{}); err != nil {
		t.Fatalf("repairActiveInstalledPackSourceHashDrift: %v", err)
	}
	for _, want := range []string{"formula-provider-pack", "formula-intermediate-pack", "formula-consumer-pack"} {
		if !applied[want] {
			t.Fatalf("pack %s was not reapplied", want)
		}
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestSourceBackedPackFromInstalledRowForReconcileWithFormulaBundlesKeepsCandidateSourceBundlesWithPeerValidationBundles(t *testing.T) {
	providerRoot := writeFormulaFunctionProviderPackSourceWithFunctionName(t, "formula-provider-pack", "A_PREFIX")
	providerPack, err := packs.ResolveInstalledSourceBackedPack(
		"formula-provider-pack",
		packs.AppPack,
		string(packs.PackSourceKindLocalPath),
		providerRoot,
	)
	if err != nil {
		t.Fatalf("resolve provider pack: %v", err)
	}

	candidateRoot := writeFormulaFunctionMixedPackSource(t, "formula-candidate-pack", "formula-provider-pack", "A_PREFIX", "B_PREFIX")
	candidateRow := installedPackRow{
		Name:                  "formula-candidate-pack",
		Category:              string(packs.AppPack),
		ManifestFormatVersion: sql.NullString{String: packs.SupportedManifestFormatVersion, Valid: true},
		SourceKind:            sql.NullString{String: string(packs.PackSourceKindLocalPath), Valid: true},
		SourceURI:             sql.NullString{String: candidateRoot, Valid: true},
	}
	formulaFunctionBundlesForValidation := &activeFormulaFunctionBundlesValidationContext{
		loaded:  true,
		bundles: providerPack.RuntimeFormulaFunctionBundles(),
		has:     true,
	}

	pack, err := sourceBackedPackFromInstalledRowForReconcileWithFormulaBundles(context.Background(), nil, candidateRow, formulaFunctionBundlesForValidation)
	if err != nil {
		t.Fatalf("sourceBackedPackFromInstalledRowForReconcileWithFormulaBundles: %v", err)
	}
	if err := packs.VerifySourceBackedPackWriteMaterializedFormulaPayload(pack); err != nil {
		t.Fatalf("resolved pack write-materialized formula verification: %v", err)
	}
}

func TestSourceBackedPackFromInstalledRowForReconcileWithFormulaBundlesDedupesCandidateBundleFromActiveContext(t *testing.T) {
	providerRoot := writeFormulaFunctionProviderPackSourceWithFunctionName(t, "formula-provider-pack", "A_PREFIX")
	providerPack, err := packs.ResolveInstalledSourceBackedPack(
		"formula-provider-pack",
		packs.AppPack,
		string(packs.PackSourceKindLocalPath),
		providerRoot,
	)
	if err != nil {
		t.Fatalf("resolve provider pack: %v", err)
	}

	candidateRoot := writeFormulaFunctionMixedPackSource(t, "formula-candidate-pack", "formula-provider-pack", "A_PREFIX", "B_PREFIX")
	candidateSourceReport := packs.ValidatePackSourceWithOptions("local_path:"+candidateRoot, packs.DefaultPackSourceOptions())
	if candidateSourceReport.HasErrors() {
		t.Fatalf("candidate source diagnostics:\n%s", candidateSourceReport.Error())
	}
	candidateBundles, err := packs.RuntimeFormulaFunctionBundlesFromResolvedSource(candidateSourceReport.Source)
	if err != nil {
		t.Fatalf("candidate source formula bundles: %v", err)
	}
	candidateRow := installedPackRow{
		Name:                  "formula-candidate-pack",
		Category:              string(packs.AppPack),
		ManifestFormatVersion: sql.NullString{String: packs.SupportedManifestFormatVersion, Valid: true},
		SourceKind:            sql.NullString{String: string(packs.PackSourceKindLocalPath), Valid: true},
		SourceURI:             sql.NullString{String: candidateRoot, Valid: true},
	}
	validationBundles := append(providerPack.RuntimeFormulaFunctionBundles(), candidateBundles...)
	formulaFunctionBundlesForValidation := &activeFormulaFunctionBundlesValidationContext{
		loaded:  true,
		bundles: validationBundles,
		has:     true,
	}

	pack, err := sourceBackedPackFromInstalledRowForReconcileWithFormulaBundles(context.Background(), nil, candidateRow, formulaFunctionBundlesForValidation)
	if err != nil {
		t.Fatalf("sourceBackedPackFromInstalledRowForReconcileWithFormulaBundles: %v", err)
	}
	if err := packs.VerifySourceBackedPackWriteMaterializedFormulaPayload(pack); err != nil {
		t.Fatalf("resolved pack write-materialized formula verification: %v", err)
	}
}

func activeFormulaBundleSourceBackedRow(name, sourceRoot, payloadHash string) installedPackRow {
	return installedPackRow{
		Name:                  name,
		Category:              string(packs.AppPack),
		Status:                installedPackStatusActive,
		PackVersion:           sql.NullString{String: "0.1.0", Valid: true},
		ManifestFormatVersion: sql.NullString{String: packs.SupportedManifestFormatVersion, Valid: true},
		PlatformCompatibility: sql.NullString{String: `{"min":"0.2.0","maxExclusive":"0.3.0"}`, Valid: true},
		DesiredPayloadHash:    sql.NullString{String: payloadHash, Valid: true},
		AppliedPayloadHash:    sql.NullString{String: payloadHash, Valid: true},
		PayloadHashAlgorithm:  sql.NullString{String: packs.PackPayloadHashAlgorithmV1, Valid: true},
		SourceKind:            sql.NullString{String: string(packs.PackSourceKindLocalPath), Valid: true},
		SourceURI:             sql.NullString{String: sourceRoot, Valid: true},
	}
}

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

	pack := testControlplaneSourceBackedFormulaPack(t)
	mock.ExpectQuery("SELECT o\\.ApiName,\\s*COALESCE\\(f\\.FormulaMode, ''\\),\\s*COALESCE\\(f\\.FormulaStorageMode, ''\\),\\s*COALESCE\\(f\\.Formula, ''\\),\\s*COALESCE\\(f\\.ProjectionConfig, ''\\) FROM _System_Field f JOIN _System_Object o ON o\\.Id = f\\.ObjectId").
		WithArgs("issue").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "FormulaMode", "FormulaStorageMode", "Formula", "ProjectionConfig"}).
			AddRow("Issue", "active", "", "TEXT(Subject)", ""))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)[\\s\\S]*information_schema\\.VIEWS").
		WithArgs("v_issue").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))

	err = verifySourceBackedDerivedArtifacts(context.Background(), db, pack)
	if err == nil {
		t.Fatal("verify derived artifacts = nil, want missing formula view error")
	}
	if !strings.Contains(err.Error(), "formula view v_issue") {
		t.Fatalf("error = %v, want missing formula view", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	pack := testControlplaneSourceBackedFormulaPack(t)
	mock.ExpectQuery("SELECT o\\.ApiName,\\s*COALESCE\\(f\\.FormulaMode, ''\\),\\s*COALESCE\\(f\\.FormulaStorageMode, ''\\),\\s*COALESCE\\(f\\.Formula, ''\\),\\s*COALESCE\\(f\\.ProjectionConfig, ''\\) FROM _System_Field f JOIN _System_Object o ON o\\.Id = f\\.ObjectId").
		WithArgs("issue").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "FormulaMode", "FormulaStorageMode", "Formula", "ProjectionConfig"}).
			AddRow("Issue", "active", "", "TEXT(Subject)", ""))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)[\\s\\S]*information_schema\\.VIEWS").
		WithArgs("v_issue").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))
	// View-usability probe (issue #1592): the stale view exists and is readable,
	// so verification proceeds to the compile proof, which is the rejection path
	// under test.
	mock.ExpectQuery("SELECT 1 FROM .v_issue. LIMIT 1").
		WillReturnRows(sqlmock.NewRows([]string{"1"}))

	err = verifySourceBackedDerivedArtifactsWithCompileProof(
		context.Background(),
		db,
		pack,
		func(context.Context, string) error {
			return errors.New("compile blew up")
		},
	)
	if err == nil {
		t.Fatal("verify derived artifacts = nil, want compile failure")
	}
	if !strings.Contains(err.Error(), "Issue") {
		t.Fatalf("error = %v, want object-scoped compile failure", err)
	}
	if !strings.Contains(err.Error(), "compile blew up") {
		t.Fatalf("error = %v, want compile proof failure", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	pack := testControlplaneSourceBackedStoredFormulaPack(t, "TEXT(")
	err = verifySourceBackedDerivedArtifactsWithCompileProof(context.Background(), db, pack, nil)
	if err == nil {
		t.Fatal("verify derived artifacts = nil, want write-materialized formula validation failure")
	}
	for _, want := range []string{
		"verify write-materialized formulas",
		"SubjectStored",
		"pack payload write-materialized-formula verification failed",
	} {
		if !strings.Contains(err.Error(), want) {
			t.Fatalf("error = %v, want %q", err, want)
		}
	}
}

func TestActiveFormulaSmokeFixtureIsPublicSafeAndInstallable(t *testing.T) {
	root := controlplaneTestPackFixtureDir(t, "active-formula-smoke")
	report := packs.ValidateInstallablePackSource("local_path:" + root)
	if report.HasErrors() {
		t.Fatalf("active-formula-smoke fixture is not installable:\n%s", report.Error())
	}
	if report.Pack == nil {
		t.Fatal("active-formula-smoke fixture did not construct source-backed pack")
	}
	if got := report.Pack.Name(); got != "active-formula-smoke" {
		t.Fatalf("pack name = %q, want active-formula-smoke", got)
	}

	objects := report.Pack.OwnedObjectAPINames()
	if len(objects) != 1 || objects[0] != "WorkItem" {
		t.Fatalf("owned objects = %v, want [WorkItem]", objects)
	}

	content, err := os.ReadFile(filepath.Join(root, "objects", "work_item.json"))
	if err != nil {
		t.Fatalf("read active formula smoke object fixture: %v", err)
	}
	if strings.Contains(string(content), "__c") {
		t.Fatalf("active formula smoke fixture contains tenant/custom suffix payload: %s", root)
	}
	for _, want := range []string{`"formulaMode": "active"`, `"formula": "Name"`, `"formula": "BLANKVALUE(Name, \"Untitled\")"`} {
		if !strings.Contains(string(content), want) {
			t.Fatalf("active formula smoke fixture missing %s", want)
		}
	}
}

func TestActiveFormulaSmokeFixtureMaterializesDerivedViewWithRealRebuild(t *testing.T) {
	root := controlplaneTestPackFixtureDir(t, "active-formula-smoke")
	report := packs.ValidateInstallablePackSource("local_path:" + root)
	if report.HasErrors() || report.Pack == nil {
		t.Fatalf("active-formula-smoke fixture is not installable:\n%s", report.Error())
	}

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

	schema := activeFormulaSmokeWorkItemSchemaForTest(t, report.Pack)
	metaRepo := &activeFormulaSmokeMetadataRepo{schemas: []*models.ObjectMetadata{schema}}
	metaSvc := metadata.NewMetadataService(metaRepo, nil, nil)
	runner := &Runner{
		DB: database.NewConnectionFromDB(db),
		SvcMgr: newTestServiceGraph(testServiceGraphConfig{
			Metadata:    metaSvc,
			FormulaView: analytics.NewFormulaViewService(db, metaSvc, logging.Default),
		}),
	}

	expectTouchedActiveFormulaObjects(mock, "workitem", "WorkItem")
	expectFormulaViewPhysicalColumnsForSchema(mock, schema)
	mock.ExpectExec("CREATE OR REPLACE SQL SECURITY INVOKER VIEW `v_workitem`").
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectQuery("SELECT TABLE_NAME FROM information_schema\\.VIEWS WHERE TABLE_SCHEMA = DATABASE\\(\\) AND TABLE_NAME LIKE 'v\\\\_%'").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME"}).AddRow("v_workitem"))
	expectTouchedActiveFormulaObjects(mock, "workitem", "WorkItem")
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)[\\s\\S]*information_schema\\.VIEWS").
		WithArgs("v_workitem").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))
	// View-usability probe (issue #1592): verify reads the freshly-rebuilt view.
	mock.ExpectQuery("SELECT 1 FROM .v_workitem. LIMIT 1").
		WillReturnRows(sqlmock.NewRows([]string{"1"}))

	if err := materializeSourceBackedPackDerived(context.Background(), runner, report.Pack, false); err != nil {
		t.Fatalf("materialize active-formula-smoke derived artifacts: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	pack := testControlplaneSourceBackedPack(t)
	predicate, args := inPredicate("o.ApiName", pack.OwnedObjectAPINames())
	mock.ExpectQuery("SELECT o\\.ApiName,\\s*COALESCE\\(f\\.FormulaMode, ''\\),\\s*COALESCE\\(f\\.FormulaStorageMode, ''\\),\\s*COALESCE\\(f\\.Formula, ''\\),\\s*COALESCE\\(f\\.ProjectionConfig, ''\\) FROM _System_Field f JOIN _System_Object o ON o\\.Id = f\\.ObjectId").
		WithArgs("issue").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "FormulaMode", "FormulaStorageMode", "Formula", "ProjectionConfig"}).
			AddRow("Issue", "active", "", "TEXT(Subject)", ""))

	rows, err := queryFormulaStorageRows(context.Background(), db, predicate, args...)
	if err != nil {
		t.Fatalf("queryFormulaStorageRows: %v", err)
	}
	if len(rows) != 1 || rows[0].objectAPIName != "Issue" {
		t.Fatalf("queryFormulaStorageRows = %v, want one Issue row", rows)
	}
	resolved, err := formulastorage.Resolve(formulastorage.Spec{
		FormulaMode:         rows[0].formulaMode,
		FormulaStorageMode:  rows[0].formulaStorageMode,
		Formula:             rows[0].formulaExpr,
		HasProjectionConfig: strings.TrimSpace(rows[0].projectionConfig) != "",
	})
	if err != nil {
		t.Fatalf("Resolve(Issue row): %v", err)
	}
	if !resolved.RequiresFormulaView() {
		t.Fatal("Resolve(Issue row).RequiresFormulaView = false, want true")
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	pack := testControlplaneSourceBackedPack(t)
	predicate, args := inPredicate("o.ApiName", pack.OwnedObjectAPINames())
	mock.ExpectQuery("SELECT o\\.ApiName,\\s*COALESCE\\(f\\.FormulaMode, ''\\),\\s*COALESCE\\(f\\.FormulaStorageMode, ''\\),\\s*COALESCE\\(f\\.Formula, ''\\),\\s*COALESCE\\(f\\.ProjectionConfig, ''\\) FROM _System_Field f JOIN _System_Object o ON o\\.Id = f\\.ObjectId").
		WithArgs("issue").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "FormulaMode", "FormulaStorageMode", "Formula", "ProjectionConfig"}))

	rows, err := queryFormulaStorageRows(context.Background(), db, predicate, args...)
	if err != nil {
		t.Fatalf("queryFormulaStorageRows: %v", err)
	}
	if len(rows) != 0 {
		t.Fatalf("queryFormulaStorageRows = %v, want no touched rows", rows)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestActiveFormulaSmokeWorkItemSchemaForTestPreservesFormulaStorageMode(t *testing.T) {
	root := controlplaneTestPackFixtureDir(t, "active-formula-smoke")
	report := packs.ValidateInstallablePackSource("local_path:" + root)
	if report.HasErrors() || report.Pack == nil {
		t.Fatalf("active-formula-smoke fixture is not installable:\n%s", report.Error())
	}

	schema := activeFormulaSmokeWorkItemSchemaForTest(t, report.Pack)
	var nameStored *models.FieldMetadata
	for i := range schema.Fields {
		if schema.Fields[i].APIName == "NameStored" {
			nameStored = &schema.Fields[i]
			break
		}
	}
	if nameStored == nil {
		t.Fatal("NameStored field missing from active formula smoke helper schema")
	}
	if nameStored.FormulaStorageMode == nil || *nameStored.FormulaStorageMode != "write_materialized" {
		t.Fatalf("NameStored.FormulaStorageMode = %#v, want write_materialized", nameStored.FormulaStorageMode)
	}
}

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

	pack := testControlplaneSourceBackedPack(t)
	predicate, args := inPredicate("o.ApiName", pack.OwnedObjectAPINames())
	mock.ExpectQuery("SELECT o\\.ApiName,\\s*COALESCE\\(f\\.FormulaMode, ''\\),\\s*COALESCE\\(f\\.FormulaStorageMode, ''\\),\\s*COALESCE\\(f\\.Formula, ''\\),\\s*COALESCE\\(f\\.ProjectionConfig, ''\\) FROM _System_Field f JOIN _System_Object o ON o\\.Id = f\\.ObjectId").
		WithArgs("issue").
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "FormulaMode", "FormulaStorageMode", "Formula", "ProjectionConfig"}).
			AddRow("Issue", "active", "write_materialized", "TEXT(\"stored\")", ""))

	rows, err := queryFormulaStorageRows(context.Background(), db, predicate, args...)
	if err != nil {
		t.Fatalf("queryFormulaStorageRows: %v", err)
	}
	if len(rows) != 1 || rows[0].objectAPIName != "Issue" {
		t.Fatalf("queryFormulaStorageRows = %v, want one Issue row", rows)
	}
	resolved, err := formulastorage.Resolve(formulastorage.Spec{
		FormulaMode:         rows[0].formulaMode,
		FormulaStorageMode:  rows[0].formulaStorageMode,
		Formula:             rows[0].formulaExpr,
		HasProjectionConfig: strings.TrimSpace(rows[0].projectionConfig) != "",
	})
	if err != nil {
		t.Fatalf("Resolve(write_materialized row): %v", err)
	}
	if resolved.RequiresFormulaView() {
		t.Fatal("Resolve(write_materialized row).RequiresFormulaView = true, want false")
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
		WithArgs("salesforce-crm-standard").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			"salesforce-crm-standard",
			string(packs.AppPack),
			installedPackStatusActive,
			"0.0.0",
			packs.BundledManifestFormatVersion,
			"null",
			packs.PackPayloadHashV1Prefix+"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
			packs.PackPayloadHashV1Prefix+"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
			packs.PackPayloadHashAlgorithmV1,
			nil,
			nil,
			nil,
			nil,
		))
	expectInstalledPackStateLookup(mock, "salesforce-crm-standard", installedPackStatusActive, 0)
	expectNoPackOwnedApprovalProcesses(mock, "salesforce-crm-standard")

	err = VerifyInstalledPackByName(context.Background(), db, "salesforce-crm-standard", false)
	if err == nil {
		t.Fatal("VerifyInstalledPackByName = nil, want missing source identity error")
	}
	if !strings.Contains(err.Error(), "has no source identity") {
		t.Fatalf("error = %v, want missing source identity", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	origRegisterInstalledRuntimeMetadata := registerInstalledRuntimeMetadataForCompileProof
	origNewRunner := newRunnerForFormulaCompileProof
	origRefreshRuntimeFunctions := refreshRuntimeFunctionsForCompileProof
	origRefreshRunnerCache := refreshRunnerCacheForCompileProof
	t.Cleanup(func() {
		registerInstalledRuntimeMetadataForCompileProof = origRegisterInstalledRuntimeMetadata
		newRunnerForFormulaCompileProof = origNewRunner
		refreshRuntimeFunctionsForCompileProof = origRefreshRuntimeFunctions
		refreshRunnerCacheForCompileProof = origRefreshRunnerCache
	})

	_, options := runnerManagedVerifyFixture(t)
	sentinel := errors.New("stop after cache refresh")
	var calls []string
	registerInstalledRuntimeMetadataForCompileProof = func(_ context.Context, got *sql.DB, registrations packs.ComponentKindRegistrationSet) error {
		if got != db {
			t.Fatalf("RegisterInstalledRuntimeMetadata db = %p, want %p", got, db)
		}
		gotRegistrations := registrations.Registrations()
		wantRegistrations := options.ComponentKindRegistrationSet.Registrations()
		if len(gotRegistrations) != 1 || len(wantRegistrations) != 1 || !reflect.DeepEqual(gotRegistrations[0].Contract, wantRegistrations[0].Contract) {
			t.Fatalf("RegisterInstalledRuntimeMetadata registrations = %#v, want %#v", gotRegistrations, wantRegistrations)
		}
		calls = append(calls, "register")
		return nil
	}
	newRunnerForFormulaCompileProof = func(_ *database.TiDBConnection, got RunnerOptions) (*Runner, error) {
		calls = append(calls, "runner")
		gotRegistrations := got.ComponentKindRegistrationSet.Registrations()
		wantRegistrations := options.ComponentKindRegistrationSet.Registrations()
		if len(gotRegistrations) != 1 || len(wantRegistrations) != 1 || !reflect.DeepEqual(gotRegistrations[0].Contract, wantRegistrations[0].Contract) {
			t.Fatalf("NewRunnerWithOptions registrations = %#v, want %#v", gotRegistrations, wantRegistrations)
		}
		return &Runner{}, nil
	}
	refreshRuntimeFunctionsForCompileProof = func(*Runner) error {
		calls = append(calls, "runtime-functions")
		return nil
	}
	refreshRunnerCacheForCompileProof = func(context.Context, *Runner) error {
		calls = append(calls, "cache")
		return sentinel
	}

	err = formulaViewCompileProofForDBWithRunnerOptions(db, options)(context.Background(), "Task")
	if !errors.Is(err, sentinel) {
		t.Fatalf("formulaViewCompileProofForDBWithRunnerOptions error = %v, want sentinel cache error", err)
	}
	wantCalls := []string{"register", "runner", "runtime-functions", "cache"}
	if !reflect.DeepEqual(calls, wantCalls) {
		t.Fatalf("compile proof init calls = %v, want %v", calls, wantCalls)
	}
}

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

	sourceDir := writeDependencyPackSource(t)
	pack, err := packs.ResolveInstalledSourceBackedPack(
		"jira-like-sample",
		packs.AppPack,
		string(packs.PackSourceKindLocalPath),
		sourceDir,
	)
	if err != nil {
		t.Fatalf("resolve dependency test pack: %v", err)
	}
	payloadHash := mustPackPayloadHash(t, pack)
	meta := pack.PackHashMetadata()

	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
		WithArgs("jira-like-sample").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			"jira-like-sample",
			string(packs.AppPack),
			installedPackStatusActive,
			meta.PackVersion,
			meta.ManifestFormatVersion,
			meta.PlatformCompatibility,
			payloadHash,
			payloadHash,
			packs.PackPayloadHashAlgorithmV1,
			packs.PackSourceKindLocalPath,
			meta.SourceURI,
			nil,
			nil,
		))
	expectInstalledPackStateLookup(mock, "jira-like-sample", installedPackStatusActive, 0)
	expectNoPackOwnedApprovalProcesses(mock, "jira-like-sample")
	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
		WithArgs("platform-base").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}))
	expectInstalledPackStateLookupMissing(mock, "platform-base")

	err = VerifyInstalledPackByName(context.Background(), db, "jira-like-sample", false)
	if err == nil {
		t.Fatal("VerifyInstalledPackByName = nil, want missing dependency error")
	}
	if !strings.Contains(err.Error(), "dependency platform-base is not active") {
		t.Fatalf("error = %v, want missing dependency", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestInstalledAppPackForRowRejectsCurrentSourceHashDriftForActiveRow(t *testing.T) {
	root, _ := writeNamedFieldDeclarationSource(t, "jira-like-sample", "Issue", "Priority")
	initGitTestRepoForControlplane(t, root)
	commitOne := gitTestCommitForControlplane(t, root, "initial")

	packAtCommitOne, err := packs.ResolveInstalledSourceBackedPackForInstalledRowAtSourceRevision(
		"jira-like-sample",
		packs.AppPack,
		packs.SupportedManifestFormatVersion,
		packs.PackSourceKindLocalPath,
		root,
		commitOne,
	)
	if err != nil {
		t.Fatalf("ResolveInstalledSourceBackedPackForInstalledRowAtSourceRevision(commitOne): %v", err)
	}
	hashOne := mustPackPayloadHash(t, packAtCommitOne)
	meta := packAtCommitOne.PackHashMetadata()

	mutateControlplaneGitTestPackSource(t, root)
	_ = gitTestCommitForControlplane(t, root, "mutated")

	row := installedPackRow{
		Name:                  "jira-like-sample",
		Category:              string(packs.AppPack),
		Status:                installedPackStatusActive,
		PackVersion:           sql.NullString{Valid: true, String: meta.PackVersion},
		ManifestFormatVersion: sql.NullString{Valid: true, String: packs.SupportedManifestFormatVersion},
		PlatformCompatibility: sql.NullString{Valid: true, String: meta.PlatformCompatibility},
		DesiredPayloadHash:    sql.NullString{Valid: true, String: hashOne},
		AppliedPayloadHash:    sql.NullString{Valid: true, String: hashOne},
		PayloadHashAlgorithm:  sql.NullString{Valid: true, String: packs.PackPayloadHashAlgorithmV1},
		SourceKind:            sql.NullString{Valid: true, String: string(packs.PackSourceKindLocalPath)},
		SourceURI:             sql.NullString{Valid: true, String: root},
		SourceRevision:        sql.NullString{Valid: true, String: commitOne},
	}

	_, err = installedAppPackForRow(context.Background(), nil, row, nil, false)
	if err == nil {
		t.Fatal("installedAppPackForRow = nil error, want hash drift failure")
	}
	if !strings.Contains(err.Error(), "payload hash") {
		t.Fatalf("installedAppPackForRow error = %v, want payload hash drift", err)
	}
}

func TestInstalledAppPackForRowUsesOfficialRepoStoredRevisionForHostRequirements(t *testing.T) {
	const packName = "jira-like-sample"
	root, _ := writeNamedFieldDeclarationSource(t, packName, "Issue", "Priority")
	t.Setenv(packs.OfficialAppPacksRootEnvVar, filepath.Dir(root))
	packs.RegisterDefaultHostPrimitives(packs.HostPrimitive{
		Kind: packs.HostPrimitiveKindProductDialect,
		Name: "salesforce-v63",
	})
	initGitTestRepoForControlplane(t, root)
	commitOne := gitTestCommitForControlplane(t, root, "initial")

	packAtCommitOne, err := packs.ResolveInstalledSourceBackedPackForInstalledRowAtSourceRevision(
		packName,
		packs.AppPack,
		packs.SupportedManifestFormatVersion,
		packs.PackSourceKindOfficialRepo,
		packName,
		commitOne,
	)
	if err != nil {
		t.Fatalf("ResolveInstalledSourceBackedPackForInstalledRowAtSourceRevision(commitOne): %v", err)
	}
	hashOne := mustPackPayloadHash(t, packAtCommitOne)
	hostDigestOne, err := packs.PackHostRequirementsDigest(packAtCommitOne)
	if err != nil {
		t.Fatalf("PackHostRequirementsDigest(commitOne): %v", err)
	}

	addHostRequirementToControlplaneGitTestPackSource(t, root)
	commitTwo := gitTestCommitForControlplane(t, root, "host requirements only")
	currentPack, err := packs.ResolveInstalledSourceBackedPackForInstalledRow(
		packName,
		packs.AppPack,
		packs.SupportedManifestFormatVersion,
		packs.PackSourceKindOfficialRepo,
		packName,
	)
	if err != nil {
		t.Fatalf("ResolveInstalledSourceBackedPackForInstalledRow(current): %v", err)
	}
	hashTwo := mustPackPayloadHash(t, currentPack)
	if hashTwo != hashOne {
		t.Fatalf("current payload hash = %q, want unchanged hash %q for hostRequirements-only edit", hashTwo, hashOne)
	}
	hostDigestTwo, err := packs.PackHostRequirementsDigest(currentPack)
	if err != nil {
		t.Fatalf("PackHostRequirementsDigest(current): %v", err)
	}
	if hostDigestTwo == hostDigestOne {
		t.Fatal("host requirements digest did not change after adding hostRequirements")
	}
	if got := packs.PackHashMetadataForPack(currentPack).SourceRevision; got != commitTwo {
		t.Fatalf("current SourceRevision = %q, want %q", got, commitTwo)
	}

	row := installedPackRow{
		Name:                   packName,
		Category:               string(packs.AppPack),
		Status:                 installedPackStatusActive,
		PackVersion:            sql.NullString{Valid: true, String: packs.PackHashMetadataForPack(packAtCommitOne).PackVersion},
		ManifestFormatVersion:  sql.NullString{Valid: true, String: packs.SupportedManifestFormatVersion},
		DesiredPayloadHash:     sql.NullString{Valid: true, String: hashOne},
		AppliedPayloadHash:     sql.NullString{Valid: true, String: hashOne},
		PayloadHashAlgorithm:   sql.NullString{Valid: true, String: packs.PackPayloadHashAlgorithmV1},
		SourceKind:             sql.NullString{Valid: true, String: string(packs.PackSourceKindOfficialRepo)},
		SourceURI:              sql.NullString{Valid: true, String: packName},
		SourceRevision:         sql.NullString{Valid: true, String: commitOne},
		HostRequirementsDigest: sql.NullString{Valid: true, String: hostDigestOne},
	}

	pack, err := installedAppPackForRow(context.Background(), nil, row, nil, false)
	if err != nil {
		t.Fatalf("installedAppPackForRow: %v", err)
	}
	if got := packs.PackHashMetadataForPack(pack).SourceRevision; got != commitOne {
		t.Fatalf("resolved SourceRevision = %q, want installed row revision %q", got, commitOne)
	}
	if err := verifyInstalledPackHostRequirementsState(row, pack); err != nil {
		t.Fatalf("verifyInstalledPackHostRequirementsState = %v, want nil for stored revision", err)
	}

	reconcilePack, err := sourceBackedPackFromInstalledRow(context.Background(), nil, row)
	if err != nil {
		t.Fatalf("sourceBackedPackFromInstalledRow: %v", err)
	}
	if got := packs.PackHashMetadataForPack(reconcilePack).SourceRevision; got != commitOne {
		t.Fatalf("reconcile resolved SourceRevision = %q, want installed row revision %q", got, commitOne)
	}
	if err := verifyInstalledPackHostRequirementsState(row, reconcilePack); err != nil {
		t.Fatalf("reconcile host requirements state = %v, want nil for stored revision", err)
	}

	formulaPack, err := sourceBackedPackFromInstalledRowWithFormulaBundles(context.Background(), nil, row, nil, false)
	if err != nil {
		t.Fatalf("sourceBackedPackFromInstalledRowWithFormulaBundles: %v", err)
	}
	if got := packs.PackHashMetadataForPack(formulaPack).SourceRevision; got != commitOne {
		t.Fatalf("formula-bundle resolved SourceRevision = %q, want installed row revision %q", got, commitOne)
	}
}

func TestValidateLivePackDependenciesUsesDriftBatchOverrideVersion(t *testing.T) {
	t.Run("strict path fails on stale installed dependency version", func(t *testing.T) {
		consumerDir := writePackWithDependencySource(t, "consumer-pack", "0.1.0", "peer-pack", ">=0.2.0")
		consumerPack, err := packs.ResolveInstalledSourceBackedPack("consumer-pack", packs.AppPack, packs.PackSourceKindLocalPath, consumerDir)
		if err != nil {
			t.Fatalf("resolve consumer pack: %v", err)
		}

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

		mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
			WithArgs("peer-pack").
			WillReturnRows(sqlmock.NewRows([]string{
				"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
				"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
			}).AddRow(
				"peer-pack",
				string(packs.AppPack),
				installedPackStatusActive,
				"0.1.0",
				packs.SupportedManifestFormatVersion,
				`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
				nil, nil, nil, nil, nil, nil, nil,
			))
		expectInstalledPackStateLookup(mock, "peer-pack", installedPackStatusActive, 0)

		err = validateLivePackDependencies(context.Background(), db, consumerPack)
		if err == nil {
			t.Fatal("validateLivePackDependencies = nil, want version constraint failure")
		}
		if !strings.Contains(err.Error(), `does not satisfy ">=0.2.0"`) {
			t.Fatalf("error = %v, want dependency version constraint failure", err)
		}
		if err := mock.ExpectationsWereMet(); err != nil {
			t.Fatalf("sql expectations: %v", err)
		}
	})

	t.Run("reconcile path uses current source version from drift batch override", func(t *testing.T) {
		consumerDir := writePackWithDependencySource(t, "consumer-pack", "0.1.0", "peer-pack", ">=0.2.0")
		consumerPack, err := packs.ResolveInstalledSourceBackedPack("consumer-pack", packs.AppPack, packs.PackSourceKindLocalPath, consumerDir)
		if err != nil {
			t.Fatalf("resolve consumer pack: %v", err)
		}
		peerDir := writePackWithDependencySource(t, "peer-pack", "0.2.0", "platform-base", ">=0.0.0")
		peerPack, err := packs.ResolveInstalledSourceBackedPack("peer-pack", packs.AppPack, packs.PackSourceKindLocalPath, peerDir)
		if err != nil {
			t.Fatalf("resolve peer pack: %v", err)
		}

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

		mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
			WithArgs("peer-pack").
			WillReturnRows(sqlmock.NewRows([]string{
				"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
				"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
			}).AddRow(
				"peer-pack",
				string(packs.AppPack),
				installedPackStatusActive,
				"0.1.0",
				packs.SupportedManifestFormatVersion,
				`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
				nil, nil, nil, nil, nil, nil, nil,
			))
		expectInstalledPackStateLookup(mock, "peer-pack", installedPackStatusActive, 0)

		err = validateLivePackDependenciesWithOverrides(context.Background(), db, consumerPack, map[string]*packs.SourceBackedPack{
			"peer-pack": peerPack,
		})
		if err != nil {
			t.Fatalf("validateLivePackDependenciesWithOverrides = %v, want nil", err)
		}
		if err := mock.ExpectationsWereMet(); err != nil {
			t.Fatalf("sql expectations: %v", err)
		}
	})
}

func TestInstallPackFromSourceUnlockedUsesDriftedPeerOverridesDuringDependencyPreflight(t *testing.T) {
	consumerDir := writePackWithDependencySource(t, "consumer-pack", "0.1.0", "peer-pack", ">=0.2.0")
	peerDir := writePackWithDependencySource(t, "peer-pack", "0.2.0", "platform-base", ">=0.0.0")
	peerPack, err := packs.ResolveInstalledSourceBackedPack("peer-pack", packs.AppPack, packs.PackSourceKindLocalPath, peerDir)
	if err != nil {
		t.Fatalf("resolve peer pack: %v", err)
	}

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

	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
		WithArgs("peer-pack").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			"peer-pack",
			string(packs.AppPack),
			installedPackStatusActive,
			"0.1.0",
			packs.SupportedManifestFormatVersion,
			`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
			nil, nil, nil, nil, nil, nil, nil,
		))
	expectInstalledPackStateLookup(mock, "peer-pack", installedPackStatusActive, 0)

	origEnsure := ensureInstallPackSchemaReadinessForInstall
	origOwnerless := samePackAllowedOwnerlessObjectsForInstall
	origApply := applyResolvedSourceBackedPackForInstall
	defer func() {
		ensureInstallPackSchemaReadinessForInstall = origEnsure
		samePackAllowedOwnerlessObjectsForInstall = origOwnerless
		applyResolvedSourceBackedPackForInstall = origApply
	}()

	ensureInstallPackSchemaReadinessForInstall = func(context.Context, *Runner) error { return nil }
	samePackAllowedOwnerlessObjectsForInstall = func(context.Context, *sql.DB, *packs.SourceBackedPack, string, InstallPackOptions) ([]string, error) {
		return nil, nil
	}
	var applied string
	applyResolvedSourceBackedPackForInstall = func(_ context.Context, _ *Runner, pack *packs.SourceBackedPack, _ *installPackPreflightOverrides, _ InstallPackOptions) error {
		if pack == nil {
			t.Fatal("apply pack = nil")
		}
		applied = pack.Name()
		return nil
	}

	runner := &Runner{DB: database.NewConnectionFromDB(db)}
	err = installPackFromSourceUnlockedWithPreflightOverrides(context.Background(), runner, "local_path:"+consumerDir, InstallPackOptions{}, &installPackPreflightOverrides{
		activeDriftedPacks: map[string]*packs.SourceBackedPack{
			"peer-pack": peerPack,
		},
	})
	if err != nil {
		t.Fatalf("installPackFromSourceUnlockedWithPreflightOverrides: %v", err)
	}
	if applied != "consumer-pack" {
		t.Fatalf("applied pack = %q, want consumer-pack", applied)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestInstallPackFromSourceUnlockedPreservesFlowActionRuntimeWhenRebuildingWithOwnerlessPolicy(t *testing.T) {
	sourceDir := writeFlowActionOwnerlessRetryPackSource(t)

	restoreRegistrations := automation.ResetDefaultAppFlowActionCallRegistrationsForTest()
	t.Cleanup(restoreRegistrations)
	restoreHandlers := automation.ResetDefaultAppFlowActionCallHandlerImplementationsForTest()
	t.Cleanup(restoreHandlers)
	automation.RegisterDefaultAppFlowActionCallHandlerImplementations(automation.FlowActionCallHandlerImplementation{
		HandlerRef: "crm:standard-action/stamp-converted-anchors",
		Build: func(descriptor automation.FlowActionCallRegistrationDescriptor) (automation.FlowActionCallRegistration, error) {
			return automation.FlowActionCallRegistration{
				ActionType:      descriptor.ActionType,
				HandlerRef:      descriptor.HandlerRef,
				AtomicPolicy:    automation.FlowActionCallAtomicPolicySafe,
				RequiresContext: automation.FlowActionCallContextStandardActionProtected,
				Handler: func(context.Context, *automation.FlowExecutor, *models.FlowInterview, *models.FlowMetadata, *models.FlowActionCall, *models.UserSession) (string, error) {
					return "", nil
				},
			}, nil
		},
	})

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

	origEnsure := ensureInstallPackSchemaReadinessForInstall
	origValidate := validateLivePackDependenciesForInstall
	origOwnerless := samePackAllowedOwnerlessObjectsForInstall
	origApply := applyResolvedSourceBackedPackForInstall
	defer func() {
		ensureInstallPackSchemaReadinessForInstall = origEnsure
		validateLivePackDependenciesForInstall = origValidate
		samePackAllowedOwnerlessObjectsForInstall = origOwnerless
		applyResolvedSourceBackedPackForInstall = origApply
	}()

	ensureInstallPackSchemaReadinessForInstall = func(context.Context, *Runner) error { return nil }
	validateLivePackDependenciesForInstall = func(context.Context, *sql.DB, *packs.SourceBackedPack) error { return nil }
	samePackAllowedOwnerlessObjectsForInstall = func(context.Context, *sql.DB, *packs.SourceBackedPack, string, InstallPackOptions) ([]string, error) {
		return []string{"Lead"}, nil
	}

	var applied string
	applyResolvedSourceBackedPackForInstall = func(_ context.Context, _ *Runner, pack *packs.SourceBackedPack, _ *installPackPreflightOverrides, _ InstallPackOptions) error {
		if pack == nil {
			t.Fatal("apply pack = nil")
		}
		applied = pack.Name()
		return nil
	}

	runner := &Runner{DB: database.NewConnectionFromDB(db)}
	err = installPackFromSourceUnlockedWithPreflightOverrides(context.Background(), runner, "local_path:"+sourceDir, InstallPackOptions{}, nil)
	if err != nil {
		t.Fatalf("installPackFromSourceUnlockedWithPreflightOverrides: %v", err)
	}
	if applied != "crm-flow-retry-pack" {
		t.Fatalf("applied pack = %q, want crm-flow-retry-pack", applied)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyInstalledPackByNameRejectsInvalidFrozenFormulaPayload(t *testing.T) {
	root := writeFrozenFormulaSourceRoot(t, `BLANKVALUE(Subject, "n/a")`)

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

	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
		WithArgs("jira-like-sample").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			"jira-like-sample",
			string(packs.AppPack),
			installedPackStatusActive,
			"0.1.0",
			packs.SupportedManifestFormatVersion,
			`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
			"nexus-pack-sha256-v1:0000000000000000000000000000000000000000000000000000000000000000",
			"nexus-pack-sha256-v1:0000000000000000000000000000000000000000000000000000000000000000",
			packs.PackPayloadHashAlgorithmV1,
			packs.PackSourceKindLocalPath,
			root,
			nil,
			nil,
		))
	expectInstalledPackStateLookup(mock, "jira-like-sample", installedPackStatusActive, 0)
	expectNoPackOwnedApprovalProcesses(mock, "jira-like-sample")

	err = VerifyInstalledPackByName(context.Background(), db, "jira-like-sample", false)
	if err == nil {
		t.Fatal("VerifyInstalledPackByName = nil, want invalid frozen formula error")
	}
	for _, want := range []string{
		"resolve installed source-backed pack jira-like-sample",
		"field.formula.expression_on_non_formula_type",
		`Issue.FrozenEcho`,
	} {
		if !strings.Contains(err.Error(), want) {
			t.Fatalf("VerifyInstalledPackByName error = %v, want %q in fail-closed resolver diagnostic", err, want)
		}
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyInstalledPackByNameFailsClosedWhenFormulaValidationDisabled(t *testing.T) {
	root, pack := writeFrozenFormulaSource(t, "")
	hash := mustPackPayloadHash(t, pack)
	t.Setenv("DISABLE_FORMULA_VALIDATION", "true")

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

	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
		WithArgs("jira-like-sample").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			"jira-like-sample",
			string(packs.AppPack),
			installedPackStatusActive,
			"0.1.0",
			packs.SupportedManifestFormatVersion,
			`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
			hash,
			hash,
			packs.PackPayloadHashAlgorithmV1,
			packs.PackSourceKindLocalPath,
			root,
			nil,
			nil,
		))
	expectInstalledPackStateLookup(mock, "jira-like-sample", installedPackStatusActive, 0)
	expectNoPackOwnedApprovalProcesses(mock, "jira-like-sample")

	err = VerifyInstalledPackByName(context.Background(), db, "jira-like-sample", false)
	if err == nil {
		t.Fatal("VerifyInstalledPackByName = nil under DISABLE_FORMULA_VALIDATION=true, want fail-closed error")
	}
	if !strings.Contains(err.Error(), "DISABLE_FORMULA_VALIDATION=true") {
		t.Fatalf("VerifyInstalledPackByName error = %v, want disabled validation wording", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyInstalledPackByNameRejectsInvalidConnectorHandlerSemanticConfig(t *testing.T) {
	root := writeConnectorPackSourceRoot(t)
	pack, err := packs.ResolveInstalledSourceBackedPack(
		"test-connector",
		packs.ConnectorPack,
		string(packs.PackSourceKindLocalPath),
		root,
	)
	if err != nil {
		t.Fatalf("ResolveInstalledSourceBackedPack: %v", err)
	}
	payloadHash := mustPackPayloadHash(t, pack)
	meta := pack.PackHashMetadata()

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

	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
		WithArgs("test-connector").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			"test-connector",
			string(packs.ConnectorPack),
			installedPackStatusActive,
			meta.PackVersion,
			meta.ManifestFormatVersion,
			meta.PlatformCompatibility,
			payloadHash,
			payloadHash,
			packs.PackPayloadHashAlgorithmV1,
			packs.PackSourceKindLocalPath,
			meta.SourceURI,
			nil,
			nil,
		))
	expectInstalledPackStateLookup(mock, "test-connector", installedPackStatusActive, 0)
	mock.ExpectQuery("SELECT p.Name, d.Namespace, d.Kind").
		WithArgs("test-connector").
		WillReturnRows(sqlmock.NewRows([]string{"Name", "Namespace", "Kind", "PackInstallId"}))
	mock.ExpectQuery("SELECT c.Namespace, c.CapabilityKind, c.ConfigJSON").
		WithArgs("test-connector").
		WillReturnRows(sqlmock.NewRows([]string{"Namespace", "CapabilityKind", "ConfigJSON", "InstalledPackId"}).
			AddRow("test-connector", "signature_verifier", `{"scheme":"hmac-sha256-template","signingTemplate":"{Timestamp}.{RawBody}","timestampHeader":"X-Test-Signature","signatureHeader":"X-Test-Signature","signatureEncoding":"hex","skewWindow":"5m","secretName":"default"}`, "pack-install-1"))

	err = VerifyInstalledPackByName(context.Background(), db, "test-connector", false)
	if err == nil {
		t.Fatal("VerifyInstalledPackByName = nil, want invalid connector handler semantic config")
	}
	for _, want := range []string{
		"installed pack test-connector live connector inbound handlers",
		"TimestampHeader and SignatureHeader must differ",
	} {
		if !strings.Contains(err.Error(), want) {
			t.Fatalf("VerifyInstalledPackByName error = %v, want %q", err, want)
		}
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func testControlplaneSourceBackedPack(t *testing.T) *packs.SourceBackedPack {
	return testControlplaneSourceBackedPackWithHostRequirements(t, nil)
}

func testControlplaneSourceBackedPackWithPermission(t *testing.T) *packs.SourceBackedPack {
	t.Helper()
	source := &packs.ResolvedPackSource{
		Kind: packs.PackSourceKindLocalPath,
		URI:  "/tmp/permission-owner-preflight",
		Manifest: &packs.PackManifest{
			ManifestFormatVersion: packs.SupportedManifestFormatVersion,
			Name:                  "permission-owner-preflight",
			DisplayName:           "Permission Owner Preflight",
			Category:              packs.AppPack,
			Version:               "0.1.0",
			PlatformCompatibility: packs.PlatformCompatibility{Min: "0.2.0", MaxExclusive: "0.3.0"},
			Components: []packs.ManifestComponent{
				{
					Kind:          packs.ComponentKindObjectSpec,
					BusinessKey:   "issue",
					Manageability: packs.ManageabilityLocked,
					Payload: []byte(`[
  {
    "apiName": "Issue",
    "label": "Issue",
    "tableType": "standard_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Issue work item",
    "fields": [
      {"apiName": "Subject", "label": "Subject", "type": "string", "required": true, "isNameField": true}
    ]
  }
]`),
				},
				{
					Kind:          packs.ComponentKindPermission,
					BusinessKey:   "feature-permissions",
					Manageability: packs.ManageabilityDefaultsOnly,
					Payload:       []byte(`[{"apiName":"RunReports","label":"Run Reports","category":"System","description":"Run Reports permission"}]`),
				},
			},
		},
	}
	pack, err := packs.NewSourceBackedPack(source)
	if err != nil {
		t.Fatalf("construct source-backed permission pack: %v", err)
	}
	return pack
}

func testControlplaneOfficialRepoSourceBackedPack(t *testing.T, revision string) *packs.SourceBackedPack {
	t.Helper()
	pack, err := packs.NewSourceBackedPack(
		testControlplaneOfficialRepoResolvedSource(revision),
	)
	if err != nil {
		t.Fatalf("construct official repo source-backed pack: %v", err)
	}
	return pack
}

func testControlplaneOfficialRepoResolvedSource(revision string) *packs.ResolvedPackSource {
	return &packs.ResolvedPackSource{
		Kind:     packs.PackSourceKindOfficialRepo,
		URI:      "jira-like-sample",
		Revision: revision,
		Manifest: &packs.PackManifest{
			ManifestFormatVersion: packs.SupportedManifestFormatVersion,
			Name:                  "jira-like-sample",
			DisplayName:           "Jira Like Sample",
			Category:              packs.AppPack,
			Version:               "0.1.0",
			PlatformCompatibility: packs.PlatformCompatibility{Min: "0.1.0", MaxExclusive: "0.2.0"},
			Components: []packs.ManifestComponent{{
				Kind:          packs.ComponentKindObjectSpec,
				BusinessKey:   "issue",
				Manageability: packs.ManageabilityLocked,
				Payload: []byte(`[
  {
    "apiName": "Issue",
    "label": "Issue",
    "tableType": "standard_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Issue work item",
    "fields": [
      {"apiName": "Subject", "label": "Subject", "type": "string", "required": true, "isNameField": true}
    ]
  }
]`),
			}},
		},
	}
}

func testControlplaneChildRelationshipPack(t *testing.T, packName, childRelationshipName string) *packs.SourceBackedPack {
	t.Helper()
	source := &packs.ResolvedPackSource{
		Kind: packs.PackSourceKindLocalPath,
		URI:  "/tmp/source-pack",
		Manifest: &packs.PackManifest{
			ManifestFormatVersion: packs.SupportedManifestFormatVersion,
			Name:                  packName,
			DisplayName:           "Child Relationship Sample",
			Category:              packs.AppPack,
			Version:               "0.1.0",
			PlatformCompatibility: packs.PlatformCompatibility{Min: "0.1.0", MaxExclusive: "0.2.0"},
			Components: []packs.ManifestComponent{{
				Kind:          packs.ComponentKindObjectSpec,
				BusinessKey:   "objects",
				Manageability: packs.ManageabilityLocked,
				Payload: []byte(fmt.Sprintf(`[
  {
    "apiName": "Parent__c",
    "label": "Parent",
    "tableType": "custom_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Parent object",
    "fields": [
      {"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true}
    ]
  },
  {
    "apiName": "ChildA__c",
    "label": "Child A",
    "tableType": "custom_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Child object",
    "fields": [
      {"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true},
      {"apiName": "Parent__c", "label": "Parent", "type": "reference", "referenceTo": ["Parent__c"], "relationshipName": "Parent", "childRelationshipName": %q}
    ]
  }
]`, childRelationshipName)),
			}},
		},
	}
	pack, err := packs.NewSourceBackedPack(source)
	if err != nil {
		t.Fatalf("construct source-backed pack: %v", err)
	}
	return pack
}

func testControlplaneSourceBackedPackWithHostRequirements(t *testing.T, requirements []packs.HostPrimitiveRequirement) *packs.SourceBackedPack {
	t.Helper()
	source := &packs.ResolvedPackSource{
		Kind: packs.PackSourceKindLocalPath,
		URI:  "/tmp/source-pack",
		Manifest: &packs.PackManifest{
			ManifestFormatVersion: packs.SupportedManifestFormatVersion,
			Name:                  "jira-like-sample",
			DisplayName:           "Jira Like Sample",
			Category:              packs.AppPack,
			Version:               "0.1.0",
			PlatformCompatibility: packs.PlatformCompatibility{Min: "0.1.0", MaxExclusive: "0.2.0"},
			HostRequirements:      requirements,
			Components: []packs.ManifestComponent{{
				Kind:          packs.ComponentKindObjectSpec,
				BusinessKey:   "issue",
				Manageability: packs.ManageabilityLocked,
				Payload: []byte(`[
  {
    "apiName": "Issue",
    "label": "Issue",
    "tableType": "standard_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Issue work item",
    "fields": [
      {"apiName": "Subject", "label": "Subject", "type": "string", "required": true, "isNameField": true}
    ]
  }
]`),
			}},
		},
	}
	pack, err := packs.NewSourceBackedPack(source)
	if err != nil {
		t.Fatalf("construct source-backed pack: %v", err)
	}
	return pack
}

func expectOfficialRepoInstalledPackRetryAllowanceRow(mock sqlmock.Sqlmock, packName string, sourceRevision string) {
	oldHash := packs.PackPayloadHashV1Prefix + strings.Repeat("d", 64)
	expectOfficialRepoInstalledPackRetryAllowanceRowWithAppliedHash(mock, packName, sourceRevision, oldHash)
}

func expectOfficialRepoInstalledPackRetryAllowanceRowWithAppliedHash(mock sqlmock.Sqlmock, packName string, sourceRevision string, appliedHash string) {
	mock.ExpectQuery("SELECT Name, Category, Status, COALESCE\\(IsDeleted, 0\\), PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision").
		WithArgs(packName).
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "IsDeleted", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			packName,
			string(packs.AppPack),
			installedPackStatusActive,
			0,
			"0.1.0",
			packs.SupportedManifestFormatVersion,
			`{"min":"0.1.0","maxExclusive":"0.2.0"}`,
			appliedHash,
			appliedHash,
			packs.PackPayloadHashAlgorithmV1,
			string(packs.PackSourceKindOfficialRepo),
			packName,
			sourceRevision,
			nil,
		))
}

func stubSourceRevisionAdvancePayloadValidation(t *testing.T, hash string) func() {
	t.Helper()
	previous := sourceRevisionAdvanceValidatePayloadHashForInstalledRow
	sourceRevisionAdvanceValidatePayloadHashForInstalledRow = func(_ context.Context, _ *sql.DB, _ installedPackRow, _ string, storedHash string, _ packs.ComponentKindRegistrationSet) error {
		if strings.TrimSpace(storedHash) != strings.TrimSpace(hash) {
			return fmt.Errorf("payload hash mismatch")
		}
		return nil
	}
	return func() {
		sourceRevisionAdvanceValidatePayloadHashForInstalledRow = previous
	}
}

func expectInstalledPackStateLookup(mock sqlmock.Sqlmock, name string, status string, isDeleted int) {
	mock.ExpectQuery("SELECT Status, COALESCE\\(IsDeleted, 0\\)").
		WithArgs(name).
		WillReturnRows(sqlmock.NewRows([]string{"Status", "IsDeleted"}).AddRow(status, isDeleted))
}

func expectInstalledPackStateLookupMissing(mock sqlmock.Sqlmock, name string) {
	mock.ExpectQuery("SELECT Status, COALESCE\\(IsDeleted, 0\\)").
		WithArgs(name).
		WillReturnRows(sqlmock.NewRows([]string{"Status", "IsDeleted"}))
}

func expectReapplyInstalledPackSourceRow(mock sqlmock.Sqlmock, row installedPackRow) {
	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision, HostRequirementsDigest FROM _System_InstalledPack").
		WithArgs(row.Name).
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility", "DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			row.Name,
			row.Category,
			row.Status,
			row.PackVersion,
			row.ManifestFormatVersion,
			row.PlatformCompatibility,
			row.DesiredPayloadHash,
			row.AppliedPayloadHash,
			row.PayloadHashAlgorithm,
			row.SourceKind,
			row.SourceURI,
			row.SourceRevision,
			row.HostRequirementsDigest,
		))
}

func expectInstalledPackRetryAllowanceRow(mock sqlmock.Sqlmock, row installedPackRow) {
	mock.ExpectQuery("SELECT Name, Category, Status, COALESCE\\(IsDeleted, 0\\), PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision, HostRequirementsDigest FROM _System_InstalledPack").
		WithArgs(row.Name).
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "IsDeleted", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility", "DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			row.Name,
			row.Category,
			row.Status,
			row.IsDeleted,
			row.PackVersion,
			row.ManifestFormatVersion,
			row.PlatformCompatibility,
			row.DesiredPayloadHash,
			row.AppliedPayloadHash,
			row.PayloadHashAlgorithm,
			row.SourceKind,
			row.SourceURI,
			row.SourceRevision,
			row.HostRequirementsDigest,
		))
}

func testControlplaneDefaultChildRowsPack(t *testing.T) *packs.SourceBackedPack {
	t.Helper()
	source := &packs.ResolvedPackSource{
		Kind: packs.PackSourceKindLocalPath,
		URI:  "/tmp/source-pack",
		Manifest: &packs.PackManifest{
			ManifestFormatVersion: packs.SupportedManifestFormatVersion,
			Name:                  "jira-like-sample",
			DisplayName:           "Jira Like Sample",
			Category:              packs.AppPack,
			Version:               "0.1.0",
			PlatformCompatibility: packs.PlatformCompatibility{Min: "0.1.0", MaxExclusive: "0.2.0"},
			Components: []packs.ManifestComponent{{
				Kind:          packs.ComponentKindObjectSpec,
				BusinessKey:   "objects",
				Manageability: packs.ManageabilityLocked,
				Payload: []byte(`[
  {
    "apiName": "Parent__c",
    "label": "Parent",
    "tableType": "custom_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Parent object",
    "defaultChildRows": [
      {
        "targetObject": "Child__c",
        "targetLookupField": "Parent__c",
        "fieldValues": {
          "Optional__c": "seeded"
        }
      }
    ],
    "fields": [
      {"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true}
    ]
  },
  {
    "apiName": "Child__c",
    "label": "Child",
    "tableType": "custom_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Child object",
    "fields": [
      {"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true},
      {"apiName": "Parent__c", "label": "Parent", "type": "reference", "referenceTo": ["Parent__c"], "createable": true, "updateable": true},
      {"apiName": "RequiredText__c", "label": "Required Text", "type": "string", "required": true, "createable": true, "updateable": true},
      {"apiName": "Optional__c", "label": "Optional", "type": "string", "createable": true, "updateable": true}
    ]
  }
]`),
			}},
		},
	}
	pack, err := packs.NewSourceBackedPack(source)
	if err != nil {
		t.Fatalf("construct source-backed pack with default child rows: %v", err)
	}
	return pack
}

func testControlplaneDefaultChildRowsAutoNumberPack(t *testing.T) *packs.SourceBackedPack {
	t.Helper()
	source := &packs.ResolvedPackSource{
		Kind: packs.PackSourceKindLocalPath,
		URI:  "/tmp/source-pack",
		Manifest: &packs.PackManifest{
			ManifestFormatVersion: packs.SupportedManifestFormatVersion,
			Name:                  "jira-like-sample",
			DisplayName:           "Jira Like Sample",
			Category:              packs.AppPack,
			Version:               "0.1.0",
			PlatformCompatibility: packs.PlatformCompatibility{Min: "0.1.0", MaxExclusive: "0.2.0"},
			Components: []packs.ManifestComponent{
				{
					Kind:          packs.ComponentKindObjectSpec,
					BusinessKey:   "objects",
					Manageability: packs.ManageabilityLocked,
					Payload: []byte(`[
  {
    "apiName": "Parent__c",
    "label": "Parent",
    "tableType": "custom_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Parent object",
    "defaultChildRows": [
      {
        "targetObject": "Child__c",
        "targetLookupField": "Parent__c",
        "fieldValues": {
          "Name": "Seeded child",
          "EngineOwned__c": "seeded"
        }
      }
    ],
    "fields": [
      {"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true}
    ]
  },
  {
    "apiName": "Child__c",
    "label": "Child",
    "tableType": "custom_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Child object",
    "fields": [
      {"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true},
      {"apiName": "Parent__c", "label": "Parent", "type": "reference", "referenceTo": ["Parent__c"], "createable": true, "updateable": true},
      {"apiName": "EngineOwned__c", "label": "Engine Owned", "type": "string", "createable": true, "updateable": true}
    ]
  }
]`),
				},
				{
					Kind:          packs.ComponentKindAutoNumberFormat,
					BusinessKey:   "autonumber_formats",
					Manageability: packs.ManageabilityLocked,
					DependsOn: []packs.ComponentReference{{
						Kind:        packs.ComponentKindObjectSpec,
						BusinessKey: "objects",
					}},
					Payload: []byte(`[
  {
    "objectName": "Child__c",
    "fieldApiName": "EngineOwned__c",
    "format": "ENG-{0000}",
    "scope": "global",
    "start": 1
  }
]`),
				},
			},
		},
	}
	pack, err := packs.NewSourceBackedPack(source)
	if err != nil {
		t.Fatalf("construct source-backed pack with default child rows and autonumber: %v", err)
	}
	return pack
}

func testControlplaneChildCloneRulesPack(t *testing.T) *packs.SourceBackedPack {
	t.Helper()
	source := &packs.ResolvedPackSource{
		Kind: packs.PackSourceKindLocalPath,
		URI:  "/tmp/source-pack",
		Manifest: &packs.PackManifest{
			ManifestFormatVersion: packs.SupportedManifestFormatVersion,
			Name:                  "jira-like-sample",
			DisplayName:           "Jira Like Sample",
			Category:              packs.AppPack,
			Version:               "0.1.0",
			PlatformCompatibility: packs.PlatformCompatibility{Min: "0.1.0", MaxExclusive: "0.2.0"},
			Components: []packs.ManifestComponent{{
				Kind:          packs.ComponentKindObjectSpec,
				BusinessKey:   "objects",
				Manageability: packs.ManageabilityLocked,
				Payload: []byte(`[
  {
    "apiName": "Parent__c",
    "label": "Parent",
    "tableType": "custom_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Parent object",
    "childCloneRules": [
      {
        "parentLookupField": "SourceParent__c",
        "sourceObject": "Source__c",
        "sourceLookupField": "SourceParent__c",
        "targetObject": "Target__c",
        "targetLookupField": "MissingParent__c",
        "fieldMappings": {
          "Name": "Name"
        }
      }
    ],
    "fields": [
      {"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true},
      {"apiName": "SourceParent__c", "label": "Source Parent", "type": "reference", "referenceTo": ["Account"], "createable": true, "updateable": true}
    ]
  },
  {
    "apiName": "Source__c",
    "label": "Source",
    "tableType": "custom_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Source object",
    "fields": [
      {"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true},
      {"apiName": "SourceParent__c", "label": "Source Parent", "type": "reference", "referenceTo": ["Account"], "createable": true, "updateable": true}
    ]
  },
  {
    "apiName": "Target__c",
    "label": "Target",
    "tableType": "custom_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Target object",
    "fields": [
      {"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true}
    ]
  }
]`),
			}},
		},
	}
	pack, err := packs.NewSourceBackedPack(source)
	if err != nil {
		t.Fatalf("construct source-backed pack with child clone rules: %v", err)
	}
	return pack
}

func mustPackPayloadHash(t *testing.T, pack packs.Pack) string {
	t.Helper()
	hash, err := packs.PackPayloadHash(pack)
	if err != nil {
		t.Fatalf("pack payload hash: %v", err)
	}
	return hash
}

func writeDependencyPackSource(t *testing.T) string {
	t.Helper()
	root := filepath.Join(t.TempDir(), "jira-like-sample")
	if err := os.MkdirAll(root, 0o755); err != nil {
		t.Fatalf("mkdir source root: %v", err)
	}
	manifest := `{
  "manifestFormatVersion": "1",
  "name": "jira-like-sample",
  "displayName": "Jira Like Sample",
  "category": "app-pack",
  "version": "0.1.0",
  "platformCompatibility": {"min": "0.2.0", "maxExclusive": "0.3.0"},
  "dependencies": [{"name": "platform-base", "versionConstraint": ">=0.1.0"}],
  "components": [{
    "kind": "object_spec",
    "businessKey": "issue",
    "manageability": "locked",
    "payload": [{
      "apiName": "Issue",
      "label": "Issue",
      "tableType": "standard_object",
      "category": "jira",
      "sharingModel": "Private",
      "enableHierarchySharing": false,
      "description": "Issue work item",
      "fields": [
        {"apiName": "Subject", "label": "Subject", "type": "string", "required": true, "isNameField": true}
      ]
    }]
  }]
}`
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write source manifest: %v", err)
	}
	return root
}

func testControlplaneSourceBackedFormulaPack(t *testing.T) *packs.SourceBackedPack {
	t.Helper()
	source := &packs.ResolvedPackSource{
		Kind: packs.PackSourceKindLocalPath,
		URI:  "/tmp/source-pack",
		Manifest: &packs.PackManifest{
			ManifestFormatVersion: packs.SupportedManifestFormatVersion,
			Name:                  "jira-like-sample",
			DisplayName:           "Jira Like Sample",
			Category:              packs.AppPack,
			Version:               "0.1.0",
			PlatformCompatibility: packs.PlatformCompatibility{Min: "0.1.0", MaxExclusive: "0.2.0"},
			Components: []packs.ManifestComponent{{
				Kind:          packs.ComponentKindObjectSpec,
				BusinessKey:   "issue",
				Manageability: packs.ManageabilityLocked,
				Payload: []byte(`[
  {
    "apiName": "Issue",
    "label": "Issue",
    "tableType": "standard_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Issue work item",
    "fields": [
      {"apiName": "Subject", "label": "Subject", "type": "string", "required": true, "isNameField": true},
      {"apiName": "SubjectEcho", "label": "Subject Echo", "type": "formula", "formulaMode": "active", "formula": "Subject", "returnType": "string"}
    ]
  }
]`),
			}},
		},
	}
	pack, err := packs.NewSourceBackedPack(source)
	if err != nil {
		t.Fatalf("construct formula source-backed pack: %v", err)
	}
	return pack
}

func testControlplaneSourceBackedStoredFormulaPack(t *testing.T, expr string) *packs.SourceBackedPack {
	t.Helper()
	escapedExpr := strings.ReplaceAll(expr, `"`, `\"`)
	source := &packs.ResolvedPackSource{
		Kind: packs.PackSourceKindLocalPath,
		URI:  "/tmp/source-pack",
		Manifest: &packs.PackManifest{
			ManifestFormatVersion: packs.SupportedManifestFormatVersion,
			Name:                  "jira-like-sample",
			DisplayName:           "Jira Like Sample",
			Category:              packs.AppPack,
			Version:               "0.1.0",
			PlatformCompatibility: packs.PlatformCompatibility{Min: "0.1.0", MaxExclusive: "0.2.0"},
			Components: []packs.ManifestComponent{{
				Kind:          packs.ComponentKindObjectSpec,
				BusinessKey:   "issue",
				Manageability: packs.ManageabilityLocked,
				Payload: []byte(`[
  {
    "apiName": "Issue",
    "label": "Issue",
    "tableType": "standard_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Issue work item",
    "fields": [
      {"apiName": "Subject", "label": "Subject", "type": "string", "required": true, "isNameField": true},
      {"apiName": "SubjectStored", "label": "Subject Stored", "type": "formula", "formulaMode": "active", "formulaStorageMode": "write_materialized", "formula": "` + escapedExpr + `", "returnType": "string"}
    ]
  }
]`),
			}},
		},
	}
	pack, err := packs.NewSourceBackedPack(source)
	if err != nil {
		t.Fatalf("construct stored-formula source-backed pack: %v", err)
	}
	return pack
}

func writeFrozenFormulaSourceRoot(t *testing.T, frozenFormula string) string {
	t.Helper()
	root := filepath.Join(t.TempDir(), "jira-like-sample")
	if err := os.MkdirAll(root, 0o755); err != nil {
		t.Fatalf("mkdir source root: %v", err)
	}
	fieldSpec := `{"apiName":"Subject","label":"Subject","type":"string","required":true,"isNameField":true}`
	if frozenFormula != "" {
		escapedFormula := strings.ReplaceAll(frozenFormula, `"`, `\"`)
		fieldSpec += `,
        {"apiName":"FrozenEcho","label":"Frozen Echo","type":"string","formulaMode":"frozen","formula":"` + escapedFormula + `","returnType":"string"}`
	}
	manifest := `{
  "manifestFormatVersion": "1",
  "name": "jira-like-sample",
  "displayName": "Jira Like Sample",
  "category": "app-pack",
  "version": "0.1.0",
  "platformCompatibility": {"min": "0.2.0", "maxExclusive": "0.3.0"},
  "components": [{
    "kind": "object_spec",
    "businessKey": "issue",
    "manageability": "defaults_only",
    "payload": [{
      "apiName": "Issue",
      "label": "Issue",
      "tableType": "standard_object",
      "category": "jira",
      "sharingModel": "Private",
      "enableHierarchySharing": false,
      "description": "Issue work item",
      "fields": [
        ` + fieldSpec + `
      ]
    }]
  }]
	}`
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write source manifest: %v", err)
	}
	return root
}

func writeFrozenFormulaSource(t *testing.T, frozenFormula string) (string, *packs.SourceBackedPack) {
	t.Helper()
	root := writeFrozenFormulaSourceRoot(t, frozenFormula)
	pack, err := packs.ResolveInstalledSourceBackedPack("jira-like-sample", packs.AppPack, packs.PackSourceKindLocalPath, root)
	if err != nil {
		t.Fatalf("ResolveInstalledSourceBackedPack: %v", err)
	}
	return root, pack
}

func writeFieldDeclarationSource(t *testing.T, fieldAPI string) (string, *packs.SourceBackedPack) {
	return writeNamedFieldDeclarationSource(t, "jira-like-sample", "Issue", fieldAPI)
}

func TestSourceBackedPackFromInstalledRowPropagatesOwnerlessAllowlistAtBuildTime(t *testing.T) {
	root := writeFormulaTypeValidationSourceRoot(t, "string", "active", "Subject", "string")
	row := installedPackRow{
		Name:                  "jira-like-sample",
		Category:              string(packs.AppPack),
		ManifestFormatVersion: sql.NullString{Valid: true, String: packs.SupportedManifestFormatVersion},
		SourceKind:            sql.NullString{Valid: true, String: string(packs.PackSourceKindLocalPath)},
		SourceURI:             sql.NullString{Valid: true, String: root},
	}

	pack, err := sourceBackedPackFromInstalledRow(context.Background(), nil, row, packs.WithAllowedOwnerlessObjectAPIs([]string{"Issue"}))
	if err != nil {
		t.Fatalf("sourceBackedPackFromInstalledRow: %v", err)
	}
	components := pack.Components()
	if len(components) == 0 {
		t.Fatal("sourceBackedPackFromInstalledRow returned no components")
	}

	componentValue := reflect.ValueOf(components[0])
	if componentValue.Kind() != reflect.Ptr {
		t.Fatalf("first component kind = %s, want pointer", componentValue.Kind())
	}
	componentValue = componentValue.Elem()
	allowField := componentValue.FieldByName("allowOwnerlessObjectAPI")
	if !allowField.IsValid() {
		t.Fatal("component does not expose allowOwnerlessObjectAPI field")
	}
	if allowField.Len() != 1 {
		t.Fatalf("allowOwnerlessObjectAPI len = %d, want 1", allowField.Len())
	}
	if !allowField.MapIndex(reflect.ValueOf("issue")).IsValid() {
		t.Fatal(`allowOwnerlessObjectAPI missing lowercased "issue" key`)
	}
}

func writeNamedFieldDeclarationSource(t *testing.T, packName string, objectAPI string, fieldAPI string) (string, *packs.SourceBackedPack) {
	t.Helper()
	root := filepath.Join(t.TempDir(), packName)
	if err := os.MkdirAll(root, 0o755); err != nil {
		t.Fatalf("mkdir source root: %v", err)
	}
	fields := []string{
		`{"apiName":"Subject","label":"Subject","type":"string","required":true,"isNameField":true}`,
	}
	if strings.TrimSpace(fieldAPI) != "" && fieldAPI != "Subject" {
		fields = append(fields, `{"apiName":"`+fieldAPI+`","label":"`+fieldAPI+`","type":"string"}`)
	}
	manifest := `{
  "manifestFormatVersion": "1",
  "name": "` + packName + `",
  "displayName": "Jira Like Sample",
  "category": "app-pack",
  "version": "0.1.0",
  "platformCompatibility": {"min": "0.2.0", "maxExclusive": "0.3.0"},
  "components": [{
    "kind": "object_spec",
    "businessKey": "issue",
    "manageability": "locked",
    "payload": [{
      "apiName": "` + objectAPI + `",
      "label": "` + objectAPI + `",
      "tableType": "standard_object",
      "category": "jira",
      "sharingModel": "Private",
      "enableHierarchySharing": false,
      "description": "Issue work item",
      "fields": [` + strings.Join(fields, ",") + `]
    }]
  }]
}`
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write source manifest: %v", err)
	}
	pack, err := packs.ResolveInstalledSourceBackedPack(packName, packs.AppPack, packs.PackSourceKindLocalPath, root)
	if err != nil {
		t.Fatalf("ResolveInstalledSourceBackedPack: %v", err)
	}
	return root, pack
}

func writeFlowActionOwnerlessRetryPackSource(t *testing.T) string {
	t.Helper()
	root := filepath.Join(t.TempDir(), "crm-flow-retry-pack")
	if err := os.MkdirAll(root, 0o755); err != nil {
		t.Fatalf("mkdir source root: %v", err)
	}
	manifest := `{
  "manifestFormatVersion": "1",
  "name": "crm-flow-retry-pack",
  "displayName": "CRM Flow Retry Pack",
  "category": "app-pack",
  "version": "0.1.0",
  "platformCompatibility": {"min": "0.2.0", "maxExclusive": "0.3.0"},
  "components": [
    {
      "kind": "object_spec",
      "businessKey": "lead",
      "manageability": "locked",
      "payload": [{
        "apiName": "Lead",
        "label": "Lead",
        "tableType": "standard_object",
        "category": "crm",
        "sharingModel": "Private",
        "enableHierarchySharing": false,
        "fields": [
          {"apiName":"Name","label":"Name","type":"string","required":true,"isNameField":true}
        ]
      }]
    },
    {
      "kind": "flow_action_call_handler",
      "businessKey": "stampconvertedanchors",
      "manageability": "locked",
      "payload": {
        "actionType": "stampConvertedAnchors",
        "handlerRef": "crm:standard-action/stamp-converted-anchors",
        "atomicPolicy": "safe",
        "previewPolicy": "reject",
        "requiresContext": "standard_action_protected"
      }
    }
  ]
}`
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write source manifest: %v", err)
	}
	return root
}

func initGitTestRepoForControlplane(t *testing.T, dir string) {
	t.Helper()
	if _, err := exec.LookPath("git"); err != nil {
		t.Skip("git not available")
	}
	runGitTestCommandForControlplane(t, dir, "init")
	runGitTestCommandForControlplane(t, dir, "config", "user.email", "codex@example.com")
	runGitTestCommandForControlplane(t, dir, "config", "user.name", "Codex")
}

func gitTestCommitForControlplane(t *testing.T, dir, message string) string {
	t.Helper()
	runGitTestCommandForControlplane(t, dir, "add", ".")
	runGitTestCommandForControlplane(t, dir, "commit", "-m", message)
	return strings.TrimSpace(runGitTestCommandForControlplane(t, dir, "rev-parse", "HEAD"))
}

func mutateControlplaneGitTestPackSource(t *testing.T, root string) {
	t.Helper()
	packPath := filepath.Join(root, "pack.json")
	body, err := os.ReadFile(packPath)
	if err != nil {
		t.Fatalf("read pack manifest: %v", err)
	}
	mutated := strings.Replace(string(body), `"Issue work item"`, `"Issue work item v2"`, 1)
	if mutated == string(body) {
		t.Fatal("expected mutation to change pack manifest payload")
	}
	if err := os.WriteFile(packPath, []byte(mutated), 0o644); err != nil {
		t.Fatalf("write mutated pack manifest: %v", err)
	}
}

func addHostRequirementToControlplaneGitTestPackSource(t *testing.T, root string) {
	t.Helper()
	packPath := filepath.Join(root, "pack.json")
	body, err := os.ReadFile(packPath)
	if err != nil {
		t.Fatalf("read pack manifest: %v", err)
	}
	mutated := strings.Replace(string(body),
		`"components": [{`,
		`"hostRequirements": [{"kind":"product-dialect","name":"salesforce-v63"}],
  "components": [{`,
		1,
	)
	if mutated == string(body) {
		t.Fatal("expected mutation to add hostRequirements")
	}
	if err := os.WriteFile(packPath, []byte(mutated), 0o644); err != nil {
		t.Fatalf("write host requirements pack manifest: %v", err)
	}
}

func runGitTestCommandForControlplane(t *testing.T, dir string, args ...string) string {
	t.Helper()
	cmd := exec.Command("git", append([]string{"-C", dir}, args...)...)
	out, err := cmd.CombinedOutput()
	if err != nil {
		t.Fatalf("git %v: %v\n%s", args, err, string(out))
	}
	return string(out)
}

type activeFormulaSmokeMetadataRepo struct {
	ports.MetadataRepository
	schemas []*models.ObjectMetadata
}

func (r *activeFormulaSmokeMetadataRepo) GetAllSchemas(context.Context) ([]*models.ObjectMetadata, error) {
	return r.schemas, nil
}

func (r *activeFormulaSmokeMetadataRepo) GetSchemaByAPIName(_ context.Context, apiName string) (*models.ObjectMetadata, error) {
	for _, schema := range r.schemas {
		if strings.EqualFold(schema.APIName, apiName) {
			return schema, nil
		}
	}
	return nil, nil
}

func (r *activeFormulaSmokeMetadataRepo) GetAllRelationships(context.Context) ([]*models.Relationship, error) {
	return nil, nil
}

func (r *activeFormulaSmokeMetadataRepo) GetAllFlowDefinitions(context.Context) ([]*models.FlowDefinition, error) {
	return nil, nil
}

func (r *activeFormulaSmokeMetadataRepo) GetValidationRules(context.Context, string) ([]*models.ValidationRule, error) {
	return nil, nil
}

func (r *activeFormulaSmokeMetadataRepo) GetAllValidationRules(context.Context) ([]*models.ValidationRule, error) {
	return nil, nil
}

func (r *activeFormulaSmokeMetadataRepo) GetAllAutoNumbers(context.Context) ([]*models.AutoNumber, error) {
	return nil, nil
}

func (r *activeFormulaSmokeMetadataRepo) GetAllIdentifierRules(context.Context) ([]*models.IdentifierRule, error) {
	return nil, nil
}

func activeFormulaSmokeWorkItemSchemaForTest(t *testing.T, pack *packs.SourceBackedPack) *models.ObjectMetadata {
	t.Helper()

	specs, err := packs.ParseObjectSpecsPayloads(pack.RuntimeObjectSpecPayloads(), "active-formula-smoke")
	if err != nil {
		t.Fatalf("parse active formula smoke object specs: %v", err)
	}
	if len(specs) != 1 || specs[0].APIName != "WorkItem" {
		t.Fatalf("active formula smoke specs = %#v, want one WorkItem spec", specs)
	}
	spec := specs[0]
	schema := &models.ObjectMetadata{
		ID:                     "obj_workitem",
		APIName:                spec.APIName,
		Label:                  spec.Label,
		PluralLabel:            spec.PluralLabel,
		TableType:              constants.TableType(spec.TableType),
		SharingModel:           constants.SharingModel(spec.SharingModel),
		EnableHierarchySharing: spec.EnableHierarchySharing,
	}
	for idx, field := range spec.Fields {
		meta := models.FieldMetadata{
			ID:          fmt.Sprintf("fld_workitem_%d", idx),
			APIName:     field.APIName,
			Label:       field.Label,
			Type:        constants.NormalizeFieldType(field.Type),
			Required:    field.Required,
			IsNameField: field.IsNameField,
		}
		if strings.TrimSpace(field.FormulaMode) != "" {
			formulaMode := field.FormulaMode
			meta.FormulaMode = &formulaMode
		}
		if strings.TrimSpace(field.FormulaStorageMode) != "" {
			formulaStorageMode := field.FormulaStorageMode
			meta.FormulaStorageMode = &formulaStorageMode
		}
		if strings.TrimSpace(field.Formula) != "" {
			formula := field.Formula
			meta.Formula = &formula
		}
		if strings.TrimSpace(field.ReturnType) != "" {
			returnType := constants.NormalizeFieldType(field.ReturnType)
			meta.ReturnType = &returnType
		}
		schema.Fields = append(schema.Fields, meta)
	}
	schema.FieldsMap = map[string]*models.FieldMetadata{}
	for i := range schema.Fields {
		field := &schema.Fields[i]
		schema.FieldsMap[strings.ToLower(field.APIName)] = field
	}
	return schema
}

func expectTouchedActiveFormulaObjects(mock sqlmock.Sqlmock, objectAPIName string, touched string) {
	mock.ExpectQuery("SELECT o\\.ApiName,\\s*COALESCE\\(f\\.FormulaMode, ''\\),\\s*COALESCE\\(f\\.FormulaStorageMode, ''\\),\\s*COALESCE\\(f\\.Formula, ''\\),\\s*COALESCE\\(f\\.ProjectionConfig, ''\\) FROM _System_Field f JOIN _System_Object o ON o\\.Id = f\\.ObjectId").
		WithArgs(objectAPIName).
		WillReturnRows(sqlmock.NewRows([]string{"ApiName", "FormulaMode", "FormulaStorageMode", "Formula", "ProjectionConfig"}).
			AddRow(touched, "active", "", "TEXT(\"derived\")", ""))
}

func expectFormulaViewPhysicalColumnsForSchema(mock sqlmock.Sqlmock, schema *models.ObjectMetadata) {
	rows := sqlmock.NewRows([]string{"COLUMN_NAME"})
	tableName := ""
	if schema != nil {
		tableName = strings.ToLower(schema.APIName)
		for _, field := range schema.Fields {
			if field.FormulaMode != nil && strings.EqualFold(strings.TrimSpace(*field.FormulaMode), "active") {
				continue
			}
			rows.AddRow(field.APIName)
		}
	}
	mock.ExpectQuery("SELECT COLUMN_NAME FROM information_schema\\.COLUMNS").
		WithArgs(tableName).
		WillReturnRows(rows)
}

func writePackWithDependencySource(t *testing.T, packName string, version string, depName string, depConstraint string) string {
	t.Helper()
	root := filepath.Join(t.TempDir(), packName)
	if err := os.MkdirAll(root, 0o755); err != nil {
		t.Fatalf("mkdir source root: %v", err)
	}
	manifest := `{
  "manifestFormatVersion": "1",
  "name": "` + packName + `",
  "displayName": "` + packName + `",
  "category": "app-pack",
  "version": "` + version + `",
  "platformCompatibility": {"min": "0.2.0", "maxExclusive": "0.3.0"},
  "dependencies": [{"name": "` + depName + `", "versionConstraint": "` + depConstraint + `"}],
  "components": [{
    "kind": "object_spec",
    "businessKey": "issue",
    "manageability": "locked",
    "payload": [{
      "apiName": "Issue",
      "label": "Issue",
      "tableType": "standard_object",
      "category": "jira",
      "sharingModel": "Private",
      "enableHierarchySharing": false,
      "description": "Issue work item",
      "fields": [
        {"apiName":"Subject","label":"Subject","type":"string","required":true,"isNameField":true}
      ]
    }]
  }]
}`
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write source manifest: %v", err)
	}
	return root
}

// writeConnectorPackSourceRoot writes a minimal local connector-pack source for
// pipeline tests. Originally added by PR #1509 with an `object_spec` component
// as a placeholder; updated by NCRM-INT-04A Wave 1 to use `connector_capability`
// instead, then updated by #1636 to include the matching handler config required
// by installable connector-pack manifests.
func writeConnectorPackSourceRoot(t *testing.T) string {
	t.Helper()
	return writeConnectorPackSourceRootWithHandlerConfig(t, `{
  "scheme":"hmac-sha256-template",
  "signingTemplate":"{Timestamp}.{RawBody}",
  "timestampHeader":"X-Test-Timestamp",
  "signatureHeader":"X-Test-Signature",
  "signatureEncoding":"hex",
  "skewWindow":"5m",
  "secretName":"default"
}`)
}

func writeConnectorPackSourceRootWithHandlerConfig(t *testing.T, config string) string {
	t.Helper()
	root := filepath.Join(t.TempDir(), "test-connector")
	if err := os.MkdirAll(root, 0o755); err != nil {
		t.Fatalf("mkdir connector pack root: %v", err)
	}
	manifest := `{
  "manifestFormatVersion": "1",
  "name": "test-connector",
  "displayName": "Test Connector",
  "category": "connector-pack",
  "version": "0.1.0",
  "platformCompatibility": {"min": "0.2.0", "maxExclusive": "0.3.0"},
  "dependencies": [],
  "components": [
    {
      "kind": "connector_capability",
      "businessKey": "test-connector:signature_verifier",
      "manageability": "locked",
      "payload": {"namespace":"test-connector","kind":"signature_verifier"}
    },
    {
      "kind": "connector_inbound_handler_config",
      "businessKey": "test-connector:signature_verifier",
      "manageability": "locked",
      "payload": {
        "namespace":"test-connector",
        "capabilityKind":"signature_verifier",
        "config": ` + config + `
      }
    }
  ]
}`
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write connector pack manifest: %v", err)
	}
	return root
}

func writeFormulaFunctionProviderPackSource(t *testing.T, packName string) string {
	t.Helper()
	return writeFormulaFunctionProviderPackSourceWithFunctionName(t, packName, "PACK_PREFIX")
}

func writeFormulaFunctionProviderPackSourceWithFunctionName(t *testing.T, packName, functionName string) string {
	t.Helper()
	root := filepath.Join(t.TempDir(), packName)
	if err := os.MkdirAll(root, 0o755); err != nil {
		t.Fatalf("mkdir provider pack root: %v", err)
	}
	manifest := fmt.Sprintf(`{
  "manifestFormatVersion": "1",
  "name": %q,
  "displayName": "Formula Provider Pack",
  "category": "app-pack",
  "version": "0.1.0",
  "platformCompatibility": {"min": "0.2.0", "maxExclusive": "0.3.0"},
  "dependencies": [],
  "components": [
    {
      "kind": "formula_function_bundle",
      "businessKey": "probe-functions",
      "manageability": "locked",
      "payload": {
	        "functions": [
	          {
	            "name": %q,
	            "minArgs": 1,
	            "maxArgs": 1,
	            "return": { "kind": "fixed", "type": "string" },
            "args": [{ "type": "string", "nullable": true }],
            "evaluator": {
              "kind": "formula_expr_v1",
              "params": ["value"],
              "body": "value + \"-probe\""
            }
          }
        ]
      }
	    }
	  ]
	}`, packName, functionName)
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write provider pack manifest: %v", err)
	}
	return root
}

func testFormulaFunctionBundlePayload(functionName string) string {
	return fmt.Sprintf(`{
		"functions": [
			{
				"name": %q,
				"minArgs": 1,
				"maxArgs": 1,
				"return": { "kind": "fixed", "type": "string" },
				"args": [{ "type": "string", "nullable": true }],
				"evaluator": {
					"kind": "formula_expr_v1",
					"params": ["value"],
					"body": "value + \"-probe\""
				}
			}
		]
	}`, functionName)
}

func writeFormulaFunctionConsumerPackSource(t *testing.T, packName string) string {
	t.Helper()
	return writeFormulaFunctionConsumerPackSourceWithDependencyFunction(t, packName, "formula-provider-pack", "PACK_PREFIX")
}

func addRunnerManagedTestComponentToPackSource(t *testing.T, root string) {
	t.Helper()
	manifestPath := filepath.Join(root, "pack.json")
	manifestBytes, err := os.ReadFile(manifestPath)
	if err != nil {
		t.Fatalf("read formula consumer manifest: %v", err)
	}
	manifest := strings.Replace(string(manifestBytes), `"manifestFormatVersion": "1"`, `"manifestFormatVersion": "2"`, 1)
	const componentsEnd = "\n  ]\n}"
	const registeredComponent = `,
    {
      "kind": "runner_managed_test",
      "businessKey": "Test/One",
      "manageability": "locked",
      "path": "components/one.json"
    }
  ]
}`
	updated := strings.Replace(manifest, componentsEnd, registeredComponent, 1)
	if updated == manifest {
		t.Fatal("formula consumer manifest component boundary not found")
	}
	if err := os.MkdirAll(filepath.Join(root, "components"), 0o755); err != nil {
		t.Fatalf("mkdir formula consumer components: %v", err)
	}
	if err := os.WriteFile(filepath.Join(root, "components", "one.json"), []byte(`{}`), 0o644); err != nil {
		t.Fatalf("write formula consumer registered component: %v", err)
	}
	if err := os.WriteFile(manifestPath, []byte(updated), 0o644); err != nil {
		t.Fatalf("write formula consumer manifest: %v", err)
	}
}

func writeFormulaFunctionConsumerPackSourceWithDependencyFunction(t *testing.T, packName, dependencyName, functionName string) string {
	t.Helper()
	root := filepath.Join(t.TempDir(), packName)
	if err := os.MkdirAll(root, 0o755); err != nil {
		t.Fatalf("mkdir consumer pack root: %v", err)
	}
	manifest := fmt.Sprintf(`{
  "manifestFormatVersion": "1",
  "name": %q,
  "displayName": "Formula Consumer Pack",
  "category": "app-pack",
  "version": "0.1.0",
  "platformCompatibility": {"min": "0.2.0", "maxExclusive": "0.3.0"},
  "dependencies": [{"name": %q, "versionConstraint": ">=0.1.0"}],
  "components": [
    {
      "kind": "standard_flow",
      "businessKey": "IssueFlow",
      "manageability": "defaults_only",
      "payload": {
        "label": "Probe Flow",
        "processType": "AutoLaunchedFlow",
        "start": {"object": "Issue", "filterFormula": %q}
      }
    },
    {
      "kind": "object_spec",
      "businessKey": "issue",
      "manageability": "locked",
      "payload": [
        {
          "apiName": "Issue",
          "label": "Issue",
          "tableType": "standard_object",
          "category": "jira",
          "sharingModel": "Private",
          "enableHierarchySharing": false,
          "description": "Issue work item",
          "lockRule": %q,
          "fields": [
            {"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true},
            {"apiName": "ProbeStored", "label": "ProbeStored", "type": "formula", "formulaMode": "active", "formulaStorageMode": "write_materialized", "formula": %q, "returnType": "string"}
          ]
        }
      ]
    }
  ]
}`, packName, dependencyName, functionName+`(Name) != ""`, functionName+`(Name) != ""`, functionName+`(Name)`)
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write consumer pack manifest: %v", err)
	}
	return root
}

func writeFormulaFunctionSelfConsumingPackSource(t *testing.T, packName string) string {
	t.Helper()
	root := filepath.Join(t.TempDir(), packName)
	if err := os.MkdirAll(root, 0o755); err != nil {
		t.Fatalf("mkdir self-consuming pack root: %v", err)
	}
	manifest := fmt.Sprintf(`{
  "manifestFormatVersion": "1",
  "name": %q,
  "displayName": "Formula Self Pack",
  "category": "app-pack",
  "version": "0.1.0",
  "platformCompatibility": {"min": "0.2.0", "maxExclusive": "0.3.0"},
  "dependencies": [],
  "components": [
    {
      "kind": "formula_function_bundle",
      "businessKey": "self-functions",
      "manageability": "locked",
      "payload": {
        "functions": [
          {
            "name": "PACK_PREFIX",
            "minArgs": 1,
            "maxArgs": 1,
            "return": { "kind": "fixed", "type": "string" },
            "args": [{ "type": "string", "nullable": true }],
            "evaluator": {
              "kind": "formula_expr_v1",
              "params": ["value"],
              "body": "value + \"-probe\""
            }
          }
        ]
      }
    },
    {
      "kind": "standard_flow",
      "businessKey": "IssueFlow",
      "manageability": "defaults_only",
      "payload": {
        "label": "Probe Flow",
        "processType": "AutoLaunchedFlow",
        "start": {"object": "Issue", "filterFormula": "PACK_PREFIX(Name) != \"\""}
      }
    },
    {
      "kind": "object_spec",
      "businessKey": "issue",
      "manageability": "locked",
      "payload": [
        {
          "apiName": "Issue",
          "label": "Issue",
          "tableType": "standard_object",
          "category": "jira",
          "sharingModel": "Private",
          "enableHierarchySharing": false,
          "description": "Issue work item",
          "lockRule": "PACK_PREFIX(Name) != \"\"",
          "fields": [
            {"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true},
            {"apiName": "ProbeStored", "label": "ProbeStored", "type": "formula", "formulaMode": "active", "formulaStorageMode": "write_materialized", "formula": "PACK_PREFIX(Name)", "returnType": "string"}
          ]
        }
      ]
    }
  ]
}`, packName)
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write self-consuming pack manifest: %v", err)
	}
	return root
}

func writeFormulaFunctionTransitivePackSource(t *testing.T, packName, dependencyName, dependencyFunctionName, publishedFunctionName string) string {
	t.Helper()
	root := filepath.Join(t.TempDir(), packName)
	if err := os.MkdirAll(root, 0o755); err != nil {
		t.Fatalf("mkdir transitive pack root: %v", err)
	}
	manifest := fmt.Sprintf(`{
  "manifestFormatVersion": "1",
  "name": %q,
  "displayName": "Formula Transitive Pack",
  "category": "app-pack",
  "version": "0.1.0",
  "platformCompatibility": {"min": "0.2.0", "maxExclusive": "0.3.0"},
  "dependencies": [{"name": %q, "versionConstraint": ">=0.1.0"}],
  "components": [
    {
      "kind": "formula_function_bundle",
      "businessKey": "transitive-functions",
      "manageability": "locked",
      "payload": {
        "functions": [
          {
            "name": %q,
            "minArgs": 1,
            "maxArgs": 1,
            "return": { "kind": "fixed", "type": "string" },
            "args": [{ "type": "string", "nullable": true }],
            "evaluator": {
              "kind": "formula_expr_v1",
              "params": ["value"],
              "body": "value + \"-transitive\""
            }
          }
        ]
      }
    },
    {
      "kind": "standard_flow",
      "businessKey": "IntermediateFlow",
      "manageability": "defaults_only",
      "payload": {
        "label": "Intermediate Flow",
        "processType": "AutoLaunchedFlow",
        "start": {"object": "IntermediateIssue", "filterFormula": %q}
      }
    },
    {
      "kind": "object_spec",
      "businessKey": "intermediate_issue",
      "manageability": "locked",
      "payload": [
        {
          "apiName": "IntermediateIssue",
          "label": "Intermediate Issue",
          "tableType": "standard_object",
          "category": "jira",
          "sharingModel": "Private",
          "enableHierarchySharing": false,
          "description": "Intermediate work item",
          "lockRule": %q,
          "fields": [
            {"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true},
            {"apiName": "ProbeStored", "label": "ProbeStored", "type": "formula", "formulaMode": "active", "formulaStorageMode": "write_materialized", "formula": %q, "returnType": "string"}
          ]
        }
      ]
    }
  ]
}`, packName, dependencyName, publishedFunctionName, dependencyFunctionName+`(Name) != ""`, dependencyFunctionName+`(Name) != ""`, dependencyFunctionName+`(Name)`)
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write transitive pack manifest: %v", err)
	}
	return root
}

func writeFormulaFunctionMixedPackSource(t *testing.T, packName, dependencyName, dependencyFunctionName, publishedFunctionName string) string {
	t.Helper()
	root := filepath.Join(t.TempDir(), packName)
	if err := os.MkdirAll(root, 0o755); err != nil {
		t.Fatalf("mkdir mixed pack root: %v", err)
	}
	manifest := fmt.Sprintf(`{
  "manifestFormatVersion": "1",
  "name": %q,
  "displayName": "Formula Mixed Pack",
  "category": "app-pack",
  "version": "0.1.0",
  "platformCompatibility": {"min": "0.2.0", "maxExclusive": "0.3.0"},
  "dependencies": [{"name": %q, "versionConstraint": ">=0.1.0"}],
  "components": [
    {
      "kind": "formula_function_bundle",
      "businessKey": "mixed-functions",
      "manageability": "locked",
      "payload": {
        "functions": [
          {
            "name": %q,
            "minArgs": 1,
            "maxArgs": 1,
            "return": { "kind": "fixed", "type": "string" },
            "args": [{ "type": "string", "nullable": true }],
            "evaluator": {
              "kind": "formula_expr_v1",
              "params": ["value"],
              "body": "value + \"-mixed\""
            }
          }
        ]
      }
    },
    {
      "kind": "standard_flow",
      "businessKey": "MixedFlow",
      "manageability": "defaults_only",
      "payload": {
        "label": "Mixed Flow",
        "processType": "AutoLaunchedFlow",
        "start": {"object": "IntermediateIssue", "filterFormula": %q}
      }
    },
    {
      "kind": "object_spec",
      "businessKey": "mixed_issue",
      "manageability": "locked",
      "payload": [
        {
          "apiName": "IntermediateIssue",
          "label": "Intermediate Issue",
          "tableType": "standard_object",
          "category": "jira",
          "sharingModel": "Private",
          "enableHierarchySharing": false,
          "description": "Mixed work item",
          "lockRule": %q,
          "fields": [
            {"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true},
            {"apiName": "ProbeStored", "label": "ProbeStored", "type": "formula", "formulaMode": "active", "formulaStorageMode": "write_materialized", "formula": %q, "returnType": "string"}
          ]
        }
      ]
    }
  ]
}`, packName, dependencyName, publishedFunctionName, dependencyFunctionName+`(Name) != ""`, dependencyFunctionName+`(Name) != ""`, publishedFunctionName+`(Name)`)
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write mixed pack manifest: %v", err)
	}
	return root
}

type persistedRuntimeDeclarationJSONView interface {
	ObjectPayloadsJSON() string
	RelatedListRulesJSON() string
	SearchScopeOrderJSON() string
	SearchAllDefaultFieldsJSON() string
	FormulaFunctionBundlesJSON() string
	StartupShallowDeclaredRowsJSON() string
}

type staleFormulaBundleRuntimeDeclaration struct {
	persistedRuntimeDeclarationJSONView
}

func (d staleFormulaBundleRuntimeDeclaration) FormulaFunctionBundlesJSON() string {
	return "[]"
}

func expectActiveInstalledPackRuntimeDeclarationWithFormulaBundles(mock sqlmock.Sqlmock, pack packs.Pack, sourceDir string, payloadHash string, decl persistedRuntimeDeclarationJSONView) {
	expectActiveInstalledPackRuntimeDeclarationsWithFormulaBundles(mock, []activeRuntimeFormulaBundleRow{{
		pack:        pack,
		sourceDir:   sourceDir,
		payloadHash: payloadHash,
		decl:        decl,
	}})
}

type activeRuntimeFormulaBundleRow struct {
	pack        packs.Pack
	sourceDir   string
	payloadHash string
	decl        persistedRuntimeDeclarationJSONView
}

func expectActiveInstalledPackRuntimeDeclarationsWithFormulaBundles(mock sqlmock.Sqlmock, activeRows []activeRuntimeFormulaBundleRow) {
	mock.ExpectQuery("SELECT COLUMN_NAME\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("_System_InstalledPack", "RuntimeObjectSpecDeclarations", "RuntimeRelatedListRules", "RuntimeSearchScopeOrder", "RuntimeSearchAllDefaultFields").
		WillReturnRows(sqlmock.NewRows([]string{"COLUMN_NAME"}).
			AddRow("RuntimeObjectSpecDeclarations").
			AddRow("RuntimeRelatedListRules").
			AddRow("RuntimeSearchScopeOrder").
			AddRow("RuntimeSearchAllDefaultFields"))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("_System_InstalledPack", "RuntimeStartupShallowDeclarations").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("_System_InstalledPack", "RuntimeFormulaFunctionBundles").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))
	rows := sqlmock.NewRows([]string{
		"Name", "Category", "AppliedPayloadHash", "ManifestFormatVersion", "SourceKind", "SourceURI", "SourceRevision",
		"RuntimeObjectSpecDeclarations", "RuntimeRelatedListRules", "RuntimeSearchScopeOrder", "RuntimeSearchAllDefaultFields", "RuntimeFormulaFunctionBundles", "RuntimeStartupShallowDeclarations",
	})
	for _, row := range activeRows {
		rows.AddRow(
			row.pack.Name(),
			string(row.pack.Category()),
			row.payloadHash,
			packs.SupportedManifestFormatVersion,
			string(packs.PackSourceKindLocalPath),
			row.sourceDir,
			nil,
			row.decl.ObjectPayloadsJSON(),
			row.decl.RelatedListRulesJSON(),
			row.decl.SearchScopeOrderJSON(),
			row.decl.SearchAllDefaultFieldsJSON(),
			row.decl.FormulaFunctionBundlesJSON(),
			row.decl.StartupShallowDeclaredRowsJSON(),
		)
	}
	mock.ExpectQuery("SELECT Name, Category, AppliedPayloadHash, ManifestFormatVersion, SourceKind, SourceURI, SourceRevision,\\s+RuntimeObjectSpecDeclarations, RuntimeRelatedListRules, RuntimeSearchScopeOrder, RuntimeSearchAllDefaultFields, RuntimeFormulaFunctionBundles, RuntimeStartupShallowDeclarations\\s+FROM _System_InstalledPack").
		WillReturnRows(rows)
}

func expectNoActiveInstalledPackRuntimeDeclarationRowsWithFormulaBundles(mock sqlmock.Sqlmock) {
	mock.ExpectQuery("SELECT COLUMN_NAME\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("_System_InstalledPack", "RuntimeObjectSpecDeclarations", "RuntimeRelatedListRules", "RuntimeSearchScopeOrder", "RuntimeSearchAllDefaultFields").
		WillReturnRows(sqlmock.NewRows([]string{"COLUMN_NAME"}).
			AddRow("RuntimeObjectSpecDeclarations").
			AddRow("RuntimeRelatedListRules").
			AddRow("RuntimeSearchScopeOrder").
			AddRow("RuntimeSearchAllDefaultFields"))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("_System_InstalledPack", "RuntimeStartupShallowDeclarations").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("_System_InstalledPack", "RuntimeFormulaFunctionBundles").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))
	mock.ExpectQuery("SELECT Name, Category, AppliedPayloadHash, ManifestFormatVersion, SourceKind, SourceURI, SourceRevision,\\s+RuntimeObjectSpecDeclarations, RuntimeRelatedListRules, RuntimeSearchScopeOrder, RuntimeSearchAllDefaultFields, RuntimeFormulaFunctionBundles, RuntimeStartupShallowDeclarations\\s+FROM _System_InstalledPack").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "AppliedPayloadHash", "ManifestFormatVersion", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
			"RuntimeObjectSpecDeclarations", "RuntimeRelatedListRules", "RuntimeSearchScopeOrder", "RuntimeSearchAllDefaultFields", "RuntimeFormulaFunctionBundles", "RuntimeStartupShallowDeclarations",
		}))
}

func writeConnectorAssociationSchemaResolverPackSourceRoot(t *testing.T) string {
	t.Helper()
	root := filepath.Join(t.TempDir(), "schema-resolver-app")
	if err := os.MkdirAll(filepath.Join(root, "objects"), 0o755); err != nil {
		t.Fatalf("mkdir schema resolver pack root: %v", err)
	}
	manifest := `{
  "manifestFormatVersion": "1",
  "name": "schema-resolver-app",
  "displayName": "Schema Resolver App",
  "category": "app-pack",
  "version": "0.1.0",
  "platformCompatibility": {"min": "0.2.0", "maxExclusive": "0.3.0"},
  "dependencies": [],
  "components": [
    {"kind":"object_spec","businessKey":"crm","manageability":"locked","path":"objects/crm.json"},
    {"kind":"standard_flow","businessKey":"WorkItem_To_Remote","manageability":"defaults_only","payload":{"start":{"object":"WorkItem__c"},"actionCalls":[{"name":"PushWorkItem","actionType":"PushToConnector"}]}},
    {"kind":"standard_flow","businessKey":"Account_To_Remote","manageability":"defaults_only","payload":{"start":{"object":"Account"},"actionCalls":[{"name":"PushAccount","actionType":"PushToConnector"}]}},
    {"kind":"connector_field_mapping","businessKey":"schema-resolver-app/push-mappings","manageability":"locked","payload":[
      {"sourceObject":"WorkItem__c","targetObject":"remoteWork","actionType":"PushToConnector","direction":"outbound","mappings":[{"sourceField":"Name","targetField":"name"}]},
      {"sourceObject":"Account","targetObject":"remoteAccount","actionType":"PushToConnector","direction":"outbound","mappings":[{"sourceField":"Name","targetField":"name"}]}
    ]},
    {"kind":"connector_push_contract","businessKey":"schema-resolver-app/WorkItem_To_Remote/PushWorkItem/WorkItem__c/remote-work","manageability":"locked","payload":{
      "flowDeveloperName":"WorkItem_To_Remote",
      "flowActionName":"PushWorkItem",
      "slotLineageId":"workitem-remote-sync",
      "sourceObject":"WorkItem__c",
      "destinationSlotId":"remote-work",
      "profileOwnerPack":"remote-connector",
      "profileName":"default",
      "targetObject":"remoteWork",
      "createPathTemplate":"/objects/work",
      "updatePathTemplate":"/objects/work/{remoteId}",
      "responseIdPath":"id",
      "remoteIdField":"RemoteKey__c",
      "associations":[{
        "sourceLookupField":"Account__c",
        "sourceObject":"Account",
        "remoteIdField":"RemoteKey__c",
        "targetActionSlotId":"account-remote-sync",
        "targetDestinationSlotId":"remote-account",
        "targetObject":"remoteAccount",
        "payloadField":"links",
        "payloadMergeMode":"append",
        "inlinePhases":["create"],
        "payloadTemplate":{"to":{"id":"{toId}"}}
      }]
    }},
    {"kind":"connector_push_contract","businessKey":"schema-resolver-app/Account_To_Remote/PushAccount/Account/remote-account","manageability":"locked","payload":{
      "flowDeveloperName":"Account_To_Remote",
      "flowActionName":"PushAccount",
      "slotLineageId":"account-remote-sync",
      "sourceObject":"Account",
      "destinationSlotId":"remote-account",
      "profileOwnerPack":"remote-connector",
      "profileName":"default",
      "targetObject":"remoteAccount",
      "createPathTemplate":"/objects/account",
      "updatePathTemplate":"/objects/account/{remoteId}",
      "responseIdPath":"id",
      "remoteIdField":"RemoteKey__c"
    }}
  ]
}`
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write schema resolver pack manifest: %v", err)
	}
	objects := `[
  {
    "apiName": "WorkItem__c",
    "label": "Work Item",
    "tableType": "standard_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Work item",
    "fields": [
      {"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true},
      {"apiName": "RemoteKey__c", "label": "Remote Key", "type": "string"}
    ]
  },
  {
    "apiName": "Account",
    "label": "Account",
    "tableType": "standard_object",
    "category": "crm",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Account",
    "fields": [
      {"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true},
      {"apiName": "RemoteKey__c", "label": "Remote Key", "type": "string"}
    ]
  }
]`
	if err := os.WriteFile(filepath.Join(root, "objects", "crm.json"), []byte(objects), 0o644); err != nil {
		t.Fatalf("write schema resolver object spec: %v", err)
	}
	return root
}

func writeDisplayNameLiveSchemaOnlyPackSourceRoot(t *testing.T) string {
	t.Helper()
	root := filepath.Join(t.TempDir(), "display-name-live-schema-app")
	if err := os.MkdirAll(filepath.Join(root, "objects"), 0o755); err != nil {
		t.Fatalf("mkdir display name live schema pack root: %v", err)
	}
	manifest := `{
  "manifestFormatVersion": "1",
  "name": "display-name-live-schema-app",
  "displayName": "Display Name Live Schema App",
  "category": "app-pack",
  "version": "0.1.0",
  "platformCompatibility": {"min": "0.2.0", "maxExclusive": "0.3.0"},
  "dependencies": [],
  "components": [
    {"kind":"object_spec","businessKey":"work-item","manageability":"locked","path":"objects/work_item.json"}
  ]
}`
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write display name live schema manifest: %v", err)
	}
	objects := `[
  {
    "apiName": "WorkItem__c",
    "label": "Work Item",
    "tableType": "standard_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Work item",
    "displayNameField": "Account.ExternalId__c",
    "fields": [
      {"apiName": "Name", "label": "Name", "type": "string", "required": true, "isNameField": true},
      {"apiName": "Account__c", "label": "Account", "type": "reference", "referenceTo": ["Account"], "relationshipName": "Account"}
    ]
  }
]`
	if err := os.WriteFile(filepath.Join(root, "objects", "work_item.json"), []byte(objects), 0o644); err != nil {
		t.Fatalf("write display name live schema object spec: %v", err)
	}
	return root
}

func testSourcePackSchemaResolver() func(string) *models.ObjectMetadata {
	return func(apiName string) *models.ObjectMetadata {
		if strings.TrimSpace(apiName) != "WorkItem__c" {
			return nil
		}
		return &models.ObjectMetadata{
			APIName: "WorkItem__c",
			Fields: []models.FieldMetadata{{
				APIName:     "Account__c",
				Type:        constants.FieldTypeReference,
				ReferenceTo: []string{"Account"},
				Createable:  true,
				Updateable:  true,
			}},
		}
	}
}

func controlplaneSystemObjectColumns() []string {
	cols := append([]string(nil), constants.ColsSystemObject...)
	return append(cols,
		constants.FieldSysObject_KeyPrefix,
		constants.FieldSysObject_DefaultChildRows,
		constants.FieldSysObject_DefaultChildRowsCustomized,
		constants.FieldSysObject_ChildCloneRulesCustomized,
		constants.FieldSysObject_ExclusiveSiblingFields,
		constants.FieldSysObject_ExclusiveSiblingFieldsCustomized,
		constants.FieldSysObject_ReadSource,
	)
}

func controlplaneSystemObjectRows() *sqlmock.Rows {
	return sqlmock.NewRows(controlplaneSystemObjectColumns())
}

func controlplaneSystemObjectRowValues(id, apiName string) []driver.Value {
	values := []driver.Value{
		id,
		apiName,
		string(constants.TableTypeStandardObject),
		apiName,
		apiName + "s",
		nil,
		nil,
		nil,
		nil,
		false,
		false,
		true,
		string(constants.SharingModelPrivate),
		false,
		nil,
		nil,
		nil,
		nil,
		nil,
		nil,
		nil,
		nil,
		false,
		nil,
		false,
		nil,
		false,
		nil,
		nil,
		nil,
		nil,
		nil,
		nil,
		nil,
		false,
		false,
		nil,
		false,
		nil,
	}
	if len(values) != len(controlplaneSystemObjectColumns()) {
		panic("controlplaneSystemObjectRowValues out of sync with _System_Object columns")
	}
	return values
}

func controlplaneSystemFieldRowValues(objectID, apiName string, fieldType constants.SchemaFieldType, referenceTo interface{}) []driver.Value {
	values := make([]driver.Value, len(constants.ColsSystemField))
	for i, col := range constants.ColsSystemField {
		switch col {
		case constants.FieldID:
			values[i] = "fld_" + strings.ToLower(apiName)
		case constants.FieldSysField_ObjectID:
			values[i] = objectID
		case constants.FieldSysField_APIName:
			values[i] = apiName
		case constants.FieldSysField_Label:
			values[i] = apiName
		case constants.FieldSysField_Type:
			values[i] = string(fieldType)
		case constants.FieldSysField_Createable, constants.FieldSysField_Updateable:
			values[i] = true
		case constants.FieldSysField_ReferenceTo:
			values[i] = referenceTo
		case constants.FieldSysField_OwnerKind:
			values[i] = "runtime"
		case constants.FieldSysField_Required,
			constants.FieldSysField_IsUnique,
			constants.FieldSysField_IsSystem,
			constants.FieldSysField_IsNameField,
			constants.FieldSysField_Indexed,
			constants.FieldSysField_TrackHistory,
			constants.FieldSysField_IsMasterDetail,
			constants.FieldSysField_IsPolymorphic,
			constants.FieldIsDeleted,
			constants.FieldSysField_IsCustomized:
			values[i] = false
		default:
			values[i] = nil
		}
	}
	return values
}

func expectWorkItemAssociationLookupSchemaLoad(mock sqlmock.Sqlmock) {
	mock.ExpectQuery("SELECT .*FROM `_System_Object`.*`IsDeleted` = \\?").
		WithArgs(false).
		WillReturnRows(controlplaneSystemObjectRows().
			AddRow(controlplaneSystemObjectRowValues("obj_workitem", "WorkItem__c")...).
			AddRow(controlplaneSystemObjectRowValues("obj_account", "Account")...))
	mock.ExpectQuery("SELECT .*FROM `_System_Field`.*`IsDeleted` = \\?").
		WithArgs(false).
		WillReturnRows(sqlmock.NewRows(constants.ColsSystemField).
			AddRow(controlplaneSystemFieldRowValues("obj_workitem", "Account__c", constants.FieldTypeReference, `["Account"]`)...).
			AddRow(controlplaneSystemFieldRowValues("obj_account", "RemoteKey__c", constants.FieldTypeString, nil)...))
	mock.ExpectQuery("SELECT .*FROM `_System_Relationship`").
		WillReturnRows(sqlmock.NewRows([]string{
			constants.FieldID,
			constants.FieldSysRelationship_ChildObjectAPIName,
			constants.FieldSysRelationship_ParentObjectAPIName,
			constants.FieldSysRelationship_FieldAPIName,
			constants.FieldSysRelationship_RelationshipName,
			constants.FieldSysRelationship_RelationshipType,
			constants.FieldSysRelationship_IsSharingParent,
			constants.FieldSysRelationship_CascadeDelete,
			constants.FieldSysRelationship_RestrictedDelete,
			constants.FieldSysRelationship_RelatedListLabel,
			constants.FieldSysRelationship_RelatedListFields,
			constants.FieldCreatedDate,
			constants.FieldLastModifiedDate,
		}))
}

func TestInstallPackSourceDiagnosticsRejectsLiveOnlyConnectorAssociationLookup(t *testing.T) {
	root := writeConnectorAssociationSchemaResolverPackSourceRoot(t)
	source := "local_path:" + root
	if err := validateSourceDiagnostics(source); err == nil {
		t.Fatal("validateSourceDiagnostics without schema resolver accepted missing connector association lookup field; want fail-closed")
	}

	opts := packs.DefaultPackSourceOptions()
	opts.ObjectSchemaResolver = testSourcePackSchemaResolver()
	if err := validateSourceDiagnosticsWithOptions(source, opts); err == nil {
		t.Fatal("validateSourceDiagnosticsWithOptions accepted live-schema-only connector association lookup; want fail-closed")
	} else if !strings.Contains(err.Error(), "sourceLookupField") {
		t.Fatalf("expected sourceLookupField schema diagnostic, got: %v", err)
	}
}

func TestValidateInstallablePackSourceForInstallRejectsLiveOnlyConnectorAssociationLookup(t *testing.T) {
	root := writeConnectorAssociationSchemaResolverPackSourceRoot(t)
	opts := packs.DefaultPackSourceOptions()
	opts.ObjectSchemaResolver = testSourcePackSchemaResolver()
	report, _, err := validateInstallablePackSourceForInstallWithOptions(context.Background(), nil, "local_path:"+root, opts)
	if err != nil {
		t.Fatalf("validate install source: %v", err)
	}
	if !report.HasErrors() {
		t.Fatal("install-pack validation accepted live-schema-only connector association lookup; want fail-closed")
	}
	if !strings.Contains(report.Error(), "sourceLookupField") {
		t.Fatalf("expected sourceLookupField schema diagnostic, got:\n%s", report.Error())
	}
}

func TestValidateInstallablePackSourceForInstallRejectsLiveOnlyDisplayNameFieldChildPath(t *testing.T) {
	root := writeDisplayNameLiveSchemaOnlyPackSourceRoot(t)
	opts := packs.DefaultPackSourceOptions()
	opts.ObjectSchemaResolver = func(apiName string) *models.ObjectMetadata {
		if strings.TrimSpace(apiName) != "Account" {
			return nil
		}
		return &models.ObjectMetadata{
			APIName: "Account",
			Fields: []models.FieldMetadata{{
				APIName:    "ExternalId__c",
				Type:       constants.FieldTypeString,
				Createable: true,
				Updateable: true,
			}},
		}
	}

	report, _, err := validateInstallablePackSourceForInstallWithOptions(context.Background(), nil, "local_path:"+root, opts)
	if err != nil {
		t.Fatalf("validate install source: %v", err)
	}
	if !report.HasErrors() {
		t.Fatal("install-pack validation accepted live-schema-only displayNameField child path; want fail-closed")
	}
	if !strings.Contains(report.Error(), "displayNameField") {
		t.Fatalf("expected displayNameField schema diagnostic, got:\n%s", report.Error())
	}
}

func TestValidateInstallPackAttributionPreflightUsesDBSchemaResolverForActiveConnectorAssociationLookup(t *testing.T) {
	root := writeConnectorAssociationSchemaResolverPackSourceRoot(t)
	opts := packs.DefaultPackSourceOptions()
	opts.ObjectSchemaResolver = testSourcePackSchemaResolver()
	activePack, err := packs.ResolveInstalledSourceBackedPackForInstalledRowAtSourceRevisionWithPackSourceOptions(
		"schema-resolver-app",
		packs.AppPack,
		packs.SupportedManifestFormatVersion,
		string(packs.PackSourceKindLocalPath),
		root,
		"",
		opts,
	)
	if err != nil {
		t.Fatalf("resolve active schema resolver pack: %v", err)
	}
	activeHash, err := packs.PackPayloadHash(activePack)
	if err != nil {
		t.Fatalf("active payload hash: %v", err)
	}
	activeDecl, err := packs.BuildPersistedRuntimeDeclaration(activePack)
	if err != nil {
		t.Fatalf("active runtime declaration: %v", err)
	}
	_, requestedPack := writeNamedFieldDeclarationSource(t, "unrelated-pack", "UnrelatedIssue", "ExternalRef")

	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()
	mock.MatchExpectationsInOrder(false)

	expectInstalledPackRuntimeDeclarationColumns(mock, true)
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("_System_InstalledPack", "RuntimeStartupShallowDeclarations").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("_System_InstalledPack", "RuntimeFormulaFunctionBundles").
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(1))
	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision, HostRequirementsDigest, RuntimeObjectSpecDeclarations, RuntimeRelatedListRules, RuntimeSearchScopeOrder, RuntimeSearchAllDefaultFields, RuntimeFormulaFunctionBundles, RuntimeStartupShallowDeclarations").
		WithArgs(string(packs.AppPack), string(packs.ConnectorPack)).
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
			"RuntimeObjectSpecDeclarations", "RuntimeRelatedListRules", "RuntimeSearchScopeOrder", "RuntimeSearchAllDefaultFields", "RuntimeFormulaFunctionBundles", "RuntimeStartupShallowDeclarations",
		}).AddRow(
			activePack.Name(),
			string(activePack.Category()),
			installedPackStatusActive,
			"0.1.0",
			packs.SupportedManifestFormatVersion,
			`{"min":"0.2.0","maxExclusive":"0.3.0"}`,
			activeHash,
			activeHash,
			packs.PackPayloadHashAlgorithmV1,
			packs.PackSourceKindLocalPath,
			root,
			nil,
			nil,
			activeDecl.ObjectPayloadsJSON(),
			activeDecl.RelatedListRulesJSON(),
			activeDecl.SearchScopeOrderJSON(),
			activeDecl.SearchAllDefaultFieldsJSON(),
			activeDecl.FormulaFunctionBundlesJSON(),
			activeDecl.StartupShallowDeclaredRowsJSON(),
		))
	expectInstalledPackStateLookup(mock, activePack.Name(), installedPackStatusActive, 0)
	expectWorkItemAssociationLookupSchemaLoad(mock)
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Object o WHERE").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Table t WHERE").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId WHERE").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	expectZeroInstallOwnerSurfaceCounts(mock)
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectQuery("SELECT COUNT\\(\\*\\) FROM FlowDefinition fd").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))

	if err := validateInstallPackAttributionPreflight(context.Background(), db, requestedPack, nil); err != nil {
		t.Fatalf("validateInstallPackAttributionPreflight rejected resolver-backed active connector association lookup: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestInstallPackFromSourceAcceptsConnectorPackCategory(t *testing.T) {
	root := writeConnectorPackSourceRoot(t)

	origEnsure := ensureInstallPackSchemaReadinessForInstall
	origValidate := validateLivePackDependenciesForInstall
	origOwnerless := samePackAllowedOwnerlessObjectsForInstall
	origApply := applyResolvedSourceBackedPackForInstall
	origList := listInstalledAppPacksForReconcile
	defer func() {
		ensureInstallPackSchemaReadinessForInstall = origEnsure
		validateLivePackDependenciesForInstall = origValidate
		samePackAllowedOwnerlessObjectsForInstall = origOwnerless
		applyResolvedSourceBackedPackForInstall = origApply
		listInstalledAppPacksForReconcile = origList
	}()

	ensureInstallPackSchemaReadinessForInstall = func(context.Context, *Runner) error { return nil }
	validateLivePackDependenciesForInstall = func(context.Context, *sql.DB, *packs.SourceBackedPack) error { return nil }
	samePackAllowedOwnerlessObjectsForInstall = func(context.Context, *sql.DB, *packs.SourceBackedPack, string, InstallPackOptions) ([]string, error) {
		return nil, nil
	}
	applyResolvedSourceBackedPackForInstall = func(_ context.Context, _ *Runner, pack *packs.SourceBackedPack, _ *installPackPreflightOverrides, _ InstallPackOptions) error {
		if pack == nil || pack.Name() != "test-connector" {
			t.Fatalf("apply: unexpected pack %#v", pack)
		}
		return nil
	}

	runner := &Runner{DB: database.NewConnectionFromDB(nil)}
	err := installPackFromSourceUnlockedWithOptions(context.Background(), runner, "local_path:"+root, InstallPackOptions{})
	if err != nil {
		t.Fatalf("installPackFromSourceUnlockedWithOptions rejected connector-pack category: %v", err)
	}
}

func TestReapplyPackFromSourceAcceptsConnectorPackCategory(t *testing.T) {
	root := writeConnectorPackSourceRoot(t)

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

	mock.ExpectQuery("SELECT Name, Category, Status, PackVersion, ManifestFormatVersion, PlatformCompatibility, DesiredPayloadHash, AppliedPayloadHash, PayloadHashAlgorithm, SourceKind, SourceURI, SourceRevision, HostRequirementsDigest FROM _System_InstalledPack").
		WithArgs("test-connector").
		WillReturnRows(sqlmock.NewRows([]string{
			"Name", "Category", "Status", "PackVersion", "ManifestFormatVersion", "PlatformCompatibility",
			"DesiredPayloadHash", "AppliedPayloadHash", "PayloadHashAlgorithm", "SourceKind", "SourceURI", "SourceRevision", "HostRequirementsDigest",
		}).AddRow(
			"test-connector",
			string(packs.ConnectorPack),
			installedPackStatusActive,
			nil, nil, nil, nil, nil, nil,
			string(packs.PackSourceKindLocalPath),
			root,
			nil,
			nil,
		))

	origEnsure := ensureInstallPackSchemaReadinessForInstall
	origValidate := validateLivePackDependenciesForInstall
	origOwnerless := samePackAllowedOwnerlessObjectsForInstall
	origApply := applyResolvedSourceBackedPackForInstall
	origList := listInstalledAppPacksForReconcile
	defer func() {
		ensureInstallPackSchemaReadinessForInstall = origEnsure
		validateLivePackDependenciesForInstall = origValidate
		samePackAllowedOwnerlessObjectsForInstall = origOwnerless
		applyResolvedSourceBackedPackForInstall = origApply
		listInstalledAppPacksForReconcile = origList
	}()

	ensureInstallPackSchemaReadinessForInstall = func(context.Context, *Runner) error { return nil }
	validateLivePackDependenciesForInstall = func(context.Context, *sql.DB, *packs.SourceBackedPack) error { return nil }
	samePackAllowedOwnerlessObjectsForInstall = func(context.Context, *sql.DB, *packs.SourceBackedPack, string, InstallPackOptions) ([]string, error) {
		return nil, nil
	}
	listInstalledAppPacksForReconcile = func(context.Context, *sql.DB) ([]installedPackRow, error) {
		return nil, nil
	}
	applyResolvedSourceBackedPackForInstall = func(_ context.Context, _ *Runner, pack *packs.SourceBackedPack, _ *installPackPreflightOverrides, _ InstallPackOptions) error {
		if pack == nil || pack.Name() != "test-connector" {
			t.Fatalf("apply: unexpected pack %#v", pack)
		}
		return nil
	}

	runner := &Runner{DB: database.NewConnectionFromDB(db)}
	if err := reapplyPackFromSourceUnlocked(context.Background(), runner, "local_path:"+root, InstallPackOptions{}); err != nil {
		t.Fatalf("reapplyPackFromSourceUnlocked rejected connector-pack category: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyActiveFormulaViewsUsableCoversNonPackObjects(t *testing.T) {
	objectsQuery := "SELECT o\\.ApiName,\\s*COALESCE\\(f\\.FormulaMode, ''\\),\\s*COALESCE\\(f\\.FormulaStorageMode, ''\\),\\s*COALESCE\\(f\\.Formula, ''\\),\\s*COALESCE\\(f\\.ProjectionConfig, ''\\) FROM _System_Field f JOIN _System_Object o ON o\\.Id = f\\.ObjectId"

	t.Run("unreadable non-pack view fails closed", func(t *testing.T) {
		db, mock, err := sqlmock.New()
		if err != nil {
			t.Fatalf("sqlmock: %v", err)
		}
		defer db.Close()
		mock.ExpectQuery(objectsQuery).
			WillReturnRows(sqlmock.NewRows([]string{"ApiName", "FormulaMode", "FormulaStorageMode", "Formula", "ProjectionConfig"}).
				AddRow("Widget__c", "active", "", "TEXT(Name)", ""))
		mock.ExpectQuery("SELECT COUNT\\(\\*\\)[\\s\\S]*information_schema\\.VIEWS").
			WithArgs("v_widget__c").
			WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))
		mock.ExpectQuery("SELECT 1 FROM .v_widget__c. LIMIT 1").
			WillReturnError(errors.New("Error 1356: View references invalid table(s) or definer"))

		err = verifyActiveFormulaViewsUsable(context.Background(), db)
		if err == nil || !strings.Contains(err.Error(), "v_widget__c") || !strings.Contains(err.Error(), "not readable") {
			t.Fatalf("verifyActiveFormulaViewsUsable = %v, want unreadable v_widget__c", err)
		}
		if err := mock.ExpectationsWereMet(); err != nil {
			t.Fatalf("sql expectations: %v", err)
		}
	})

	t.Run("readable view passes; non-formula object skipped", func(t *testing.T) {
		db, mock, err := sqlmock.New()
		if err != nil {
			t.Fatalf("sqlmock: %v", err)
		}
		defer db.Close()
		mock.ExpectQuery(objectsQuery).
			WillReturnRows(sqlmock.NewRows([]string{"ApiName", "FormulaMode", "FormulaStorageMode", "Formula", "ProjectionConfig"}).
				AddRow("Widget__c", "active", "", "TEXT(Name)", "").
				AddRow("Plain__c", "", "", "", ""))
		mock.ExpectQuery("SELECT COUNT\\(\\*\\)[\\s\\S]*information_schema\\.VIEWS").
			WithArgs("v_widget__c").
			WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))
		mock.ExpectQuery("SELECT 1 FROM .v_widget__c. LIMIT 1").
			WillReturnRows(sqlmock.NewRows([]string{"1"}).AddRow(1))

		if err := verifyActiveFormulaViewsUsable(context.Background(), db); err != nil {
			t.Fatalf("verifyActiveFormulaViewsUsable = %v, want nil", err)
		}
		if err := mock.ExpectationsWereMet(); err != nil {
			t.Fatalf("sql expectations: %v", err)
		}
	})
}

func publicConnectedAppOwnershipKey(id, name, consumerKey string) packs.ConnectedAppOwnershipKey {
	return packs.ConnectedAppOwnershipKey{
		ID:                      id,
		Name:                    name,
		ConsumerKey:             consumerKey,
		CallbackURL:             "http://127.0.0.1:1717/callback",
		RedirectURIs:            []string{"http://127.0.0.1:1717/callback"},
		Scopes:                  []string{"external.records.read"},
		IsActive:                true,
		ClientType:              "public",
		RequirePKCE:             true,
		TokenEndpointAuthMethod: "none",
		AllowedGrantTypes:       []string{"authorization_code"},
		ConsentPolicy:           "trusted",
		ConsentVersion:          1,
		IssueRefreshTokens:      false,
		PermittedUsers:          "AllUsers",
		ConsumerSecretPresent:   false,
	}
}