package packs

import (
	"context"
	"database/sql"
	"database/sql/driver"
	"encoding/json"
	"errors"
	"reflect"
	"strings"
	"testing"
	"time"

	"github.com/DATA-DOG/go-sqlmock"
	featureauth "github.com/nexuscrm/auth"
	pkgauth "github.com/nexuscrm/kernel/pkg/auth"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

func TestResolveAdminPassword_ExplicitPassword(t *testing.T) {
	cases := []struct {
		name, nexusEnv, envPassword string
	}{
		{"production explicit", "production", "MySecureP@ss"},
		{"staging explicit", "staging", "OverridePwd"},
		{"dev explicit", "dev", "DevPwd123"},
		{"prod explicit", "prod", "ProdPwd!"},
	}
	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			got, explicit, err := resolveAdminPassword("seed", tc.envPassword, tc.nexusEnv)
			if err != nil {
				t.Fatalf("unexpected error: %v", err)
			}
			if !explicit {
				t.Fatal("expected explicit=true")
			}
			if got != tc.envPassword {
				t.Fatalf("password = %q, want %q", got, tc.envPassword)
			}
		})
	}
}

func TestResolveAdminPassword_ProductionFail(t *testing.T) {
	cases := []struct {
		name, nexusEnv string
	}{
		{"production", "production"},
		{"prod", "prod"},
		{"staging", "staging"},
		{"PRODUCTION (case insensitive)", "PRODUCTION"},
		{"PROD (case insensitive)", "PROD"},
		{"STAGING (case insensitive)", "STAGING"},
		{"staging with spaces", " staging "},
	}
	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			got, explicit, err := resolveAdminPassword("seed", "", tc.nexusEnv)
			if err == nil {
				t.Fatal("expected error for production-equivalent env with empty password, got nil")
			}
			if !strings.Contains(err.Error(), "NEXUS_ADMIN_PASSWORD must be set") {
				t.Fatalf("error message = %q, want 'NEXUS_ADMIN_PASSWORD must be set'", err.Error())
			}
			if explicit {
				t.Fatal("expected explicit=false on error")
			}
			if got != "" {
				t.Fatalf("expected empty password on error, got %q", got)
			}
		})
	}
}

func TestResolveAdminPassword_DevRandomGenerate(t *testing.T) {
	cases := []struct {
		name, nexusEnv string
	}{
		{"dev", "dev"},
		{"test", "test"},
		{"ci", "ci"},
		{"empty env treated as non-production", ""},
		{"local", "local"},
	}
	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			got, explicit, err := resolveAdminPassword("seed", "", tc.nexusEnv)
			if err != nil {
				t.Fatalf("unexpected error: %v", err)
			}
			if explicit {
				t.Fatal("expected explicit=false")
			}
			if got == "" {
				t.Fatal("expected non-empty generated password")
			}
			// hex.EncodeToString of 12 bytes → 24 hex characters
			if len(got) != 24 {
				t.Fatalf("generated password length = %d, want 24 (12 bytes hex-encoded)", len(got))
			}
			// Verify it's valid hex
			for _, c := range got {
				if !((c >= '0' && c <= '9') || (c >= 'a' && c <= 'f')) {
					t.Fatalf("generated password %q contains non-hex character %q", got, c)
				}
			}
		})
	}
}

func TestResolveAdminPassword_DevWithWhitespace(t *testing.T) {
	// Whitespace in envPassword is trimmed — a spaces-only value should
	// fall through to the production-fail or dev-random branch.
	got, explicit, err := resolveAdminPassword("seed", "   ", "production")
	if err == nil {
		t.Fatalf("expected error for production env with whitespace-only password, got nil; password=%q explicit=%v", got, explicit)
	}

	got, explicit, err = resolveAdminPassword("seed", "   ", "dev")
	if err != nil {
		t.Fatalf("unexpected error for dev env with whitespace-only password: %v", err)
	}
	if explicit {
		t.Fatal("expected explicit=false for whitespace-only password")
	}
	if got == "" {
		t.Fatal("expected non-empty generated password for whitespace-only input")
	}
}

func TestResolveAdminPassword_ProductionErrorIsWrapped(t *testing.T) {
	// The returned error should be usable with errors.Is-style checks
	// (not a custom type, but a standard wrapped fmt.Errorf).
	_, _, err := resolveAdminPassword("seed", "", "production")
	if err == nil {
		t.Fatal("expected error, got nil")
	}
	// Verify the error message contains the NEXUS_ENV value for debugging.
	if !strings.Contains(err.Error(), "production") {
		t.Fatalf("error = %q, want to contain 'production' for debugging context", err.Error())
	}
}

func TestResolveAdminPassword_ExplicitOverridesSeed(t *testing.T) {
	// The seedPassword parameter is ignored when NEXUS_ADMIN_PASSWORD is set.
	got, explicit, err := resolveAdminPassword("ignored-seed", "real-pwd", "production")
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if got != "real-pwd" {
		t.Fatalf("password = %q, want %q (seed should be overridden)", got, "real-pwd")
	}
	if !explicit {
		t.Fatal("expected explicit=true")
	}
}

func TestResolveAdminPassword_DevExplicitOverridesSeed(t *testing.T) {
	// Same test for a dev environment: explicit password wins over seed.
	got, explicit, err := resolveAdminPassword("ignored-seed", "dev-explicit", "dev")
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if got != "dev-explicit" {
		t.Fatalf("password = %q, want %q", got, "dev-explicit")
	}
	if !explicit {
		t.Fatal("expected explicit=true")
	}
}

func TestPlatformPolicyDoesNotDeclareOAuthCustomScope(t *testing.T) {
	declared, err := (&PlatformPolicyPack{}).DeclaredComponents()
	if err != nil {
		t.Fatalf("DeclaredComponents: %v", err)
	}
	for _, component := range declared {
		if component.Kind == ComponentKindOAuthCustomScope {
			t.Fatalf("platform-policy must not own oauth_custom_scope components: %#v", component)
		}
	}
}

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

	app := PlatformBootstrapConnectedApp{
		ClientID:           "nexuscrm-ui",
		Name:               "NexusCRM UI",
		ConsumerKey:        "nexuscrm-ui",
		Scopes:             "api refresh_token offline_access",
		SessionIssuerClass: "platform_browser",
	}
	mock.ExpectQuery("SELECT `Id`, `Name`, `IsActive`, COALESCE\\(`Scopes`, ''\\), COALESCE\\(`SessionIssuerClass`, ''\\)\\s+FROM `_System_ConnectedApp`\\s+WHERE `ConsumerKey` = \\?\\s+LIMIT 1").
		WithArgs("nexuscrm-ui").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name", "IsActive", "Scopes", "SessionIssuerClass"}).
			AddRow("nexuscrm-ui", "NexusCRM UI", true, "api refresh_token offline_access", "platform_browser"))

	if err := verifyPlatformBootstrapConnectedApps(context.Background(), db, []PlatformBootstrapConnectedApp{app}); err != nil {
		t.Fatalf("verifyPlatformBootstrapConnectedApps: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	app := PlatformBootstrapConnectedApp{
		ClientID:    "nexuscrm-ui",
		Name:        "NexusCRM UI",
		ConsumerKey: "nexuscrm-ui",
	}
	mock.ExpectQuery("SELECT `Id`, `Name`, `IsActive`, COALESCE\\(`Scopes`, ''\\), COALESCE\\(`SessionIssuerClass`, ''\\)\\s+FROM `_System_ConnectedApp`\\s+WHERE `ConsumerKey` = \\?\\s+LIMIT 1").
		WithArgs("nexuscrm-ui").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name", "IsActive", "Scopes", "SessionIssuerClass"}))

	err = verifyPlatformBootstrapConnectedApps(context.Background(), db, []PlatformBootstrapConnectedApp{app})
	if err == nil {
		t.Fatal("verifyPlatformBootstrapConnectedApps accepted missing bootstrap connected app")
	}
	if !strings.Contains(err.Error(), `consumer key "nexuscrm-ui" is missing`) {
		t.Fatalf("error = %v, want missing consumer key", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	hook := &connectedAppInvalidationHookRecorder{}
	mock.ExpectBegin()
	mock.ExpectQuery("SELECT COALESCE\\(`Scopes`, ''\\) FROM `_System_ConnectedApp` WHERE `Id` = \\? AND `ConsumerKey` = \\? LIMIT 1 FOR UPDATE").
		WithArgs("nexuscrm-ui", "nexuscrm-ui").
		WillReturnRows(sqlmock.NewRows([]string{"Scopes"}).AddRow(""))
	mock.ExpectExec("UPDATE `_System_ConnectedApp` SET `Scopes` = \\?, `LastModifiedDate` = CURRENT_TIMESTAMP WHERE `Id` = \\? AND `ConsumerKey` = \\?").
		WithArgs("api refresh_token offline_access", "nexuscrm-ui", "nexuscrm-ui").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectQuery("SELECT COALESCE\\(`Scopes`, ''\\) FROM `_System_ConnectedApp` WHERE `Id` = \\? AND `ConsumerKey` = \\? LIMIT 1").
		WithArgs("nexuscrm-ui", "nexuscrm-ui").
		WillReturnRows(sqlmock.NewRows([]string{"Scopes"}).AddRow("api refresh_token offline_access"))
	mock.ExpectExec("UPDATE `_System_Session` SET `IsRevoked` = 1, `LastModifiedDate` = CURRENT_TIMESTAMP WHERE `ConnectedAppId` = \\? AND `IsRevoked` = 0").
		WithArgs("nexuscrm-ui").
		WillReturnResult(sqlmock.NewResult(0, 2))
	mock.ExpectCommit()
	if err := convergePlatformBootstrapConnectedAppScopes(context.Background(), ApplyDeps{DB: db, ConnectedAppHook: hook}, bootstrapConnectedApp{
		ClientID: "nexuscrm-ui", KeyID: "nexuscrm-ui", Scopes: " api   refresh_token offline_access ",
	}); err != nil {
		t.Fatalf("convergePlatformBootstrapConnectedAppScopes: %v", err)
	}
	if got, want := hook.appIDs, []string{"nexuscrm-ui"}; !reflect.DeepEqual(got, want) {
		t.Fatalf("ConnectedAppHook appIDs = %q, want %q", got, want)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	hook := &connectedAppInvalidationHookRecorder{}
	mock.ExpectBegin()
	mock.ExpectQuery("SELECT COALESCE\\(`Scopes`, ''\\) FROM `_System_ConnectedApp` WHERE `Id` = \\? AND `ConsumerKey` = \\? LIMIT 1 FOR UPDATE").
		WithArgs("nexuscrm-ui", "nexuscrm-ui").
		WillReturnRows(sqlmock.NewRows([]string{"Scopes"}).AddRow("api refresh_token offline_access"))
	mock.ExpectCommit()
	if err := convergePlatformBootstrapConnectedAppScopes(context.Background(), ApplyDeps{DB: db, ConnectedAppHook: hook}, bootstrapConnectedApp{
		ClientID: "nexuscrm-ui", KeyID: "nexuscrm-ui", Scopes: "api refresh_token offline_access",
	}); err != nil {
		t.Fatalf("convergePlatformBootstrapConnectedAppScopes: %v", err)
	}
	if len(hook.appIDs) != 0 {
		t.Fatalf("ConnectedAppHook appIDs = %q, want no invalidation for unchanged scopes", hook.appIDs)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	mock.ExpectBegin()
	mock.ExpectQuery("SELECT COALESCE\\(`Scopes`, ''\\) FROM `_System_ConnectedApp` WHERE `Id` = \\? AND `ConsumerKey` = \\? LIMIT 1 FOR UPDATE").
		WithArgs("nexuscrm-ui", "nexuscrm-ui").
		WillReturnError(sql.ErrNoRows)
	mock.ExpectRollback()
	err = convergePlatformBootstrapConnectedAppScopes(context.Background(), ApplyDeps{DB: db}, bootstrapConnectedApp{
		ClientID: "nexuscrm-ui", KeyID: "nexuscrm-ui", Scopes: "api",
	})
	if err == nil || !errors.Is(err, sql.ErrNoRows) {
		t.Fatalf("error = %v, want fail-closed missing-row error", err)
	}
}

// expectOAuthScopeAllowDynamicColumnProbe stubs the schema-presence probe the
// seed path runs before it references the AllowDynamicPublicClients column.
// present=true models a DB that has applied NexusCRM#3986; present=false models
// a warm-upgrade DB that has not.
func expectOAuthScopeAllowDynamicColumnProbe(mock sqlmock.Sqlmock, present bool) {
	count := 0
	if present {
		count = 1
	}
	mock.ExpectQuery("information_schema.columns").
		WithArgs("_System_OAuthCustomScope", "AllowDynamicPublicClients").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(count))
}

func TestSourceBackedOAuthCustomScopeComponentApplyAndVerify(t *testing.T) {
	payload := []byte(`{
		"id": "oauthscope_external_records_read",
		"name": "external.records.read",
		"description": "Read-only access to an app-pack records REST API for public OAuth clients.",
		"allowed_client_types": ["public"],
		"allowed_methods": ["GET", "HEAD"],
		"route_family_prefix": "/services/data/", "route_patterns": ["/services/data", "/services/data/:version/sobjects"],
		"resource_links": ["sobjects"],
		"active": true
	}`)
	component, err := newSourceBackedOAuthCustomScopeComponent("crm-pack", ManifestComponent{
		Kind:          ComponentKindOAuthCustomScope,
		BusinessKey:   "external.records.read",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload)
	if err != nil {
		t.Fatalf("newSourceBackedOAuthCustomScopeComponent: %v", err)
	}
	seed := component.(*sourceBackedManagedComponent)
	expected, err := seed.ExpectedProjection()
	if err != nil {
		t.Fatalf("ExpectedProjection: %v", err)
	}
	scope := expected.(bootstrapOAuthCustomScope)

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

	mock.ExpectQuery("SELECT OwnerPack").
		WithArgs(scope.Name).
		WillReturnRows(sqlmock.NewRows([]string{"OwnerPack"}))
	expectOAuthScopeAllowDynamicColumnProbe(mock, true)
	mock.ExpectExec("INSERT INTO _System_OAuthCustomScope").
		WithArgs(
			scope.ID,
			scope.Name,
			scope.Description,
			nil,
			strings.Join(scope.AllowedClientTypes, " "),
			strings.Join(scope.AllowedMethods, " "),
			strings.Join(scope.RoutePatterns, " "),
			strings.Join(scope.ResourceLinks, " "),
			scope.OwnerPack,
			scope.Active,
			scope.AllowDynamicPublicClients,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))

	if err := component.Apply(context.Background(), ApplyDeps{DB: db}); err != nil {
		t.Fatalf("Apply: %v", err)
	}

	expectOAuthScopeAllowDynamicColumnProbe(mock, true)
	mock.ExpectQuery("SELECT Id, Name, Description, CustomPermissionId, AllowedClientTypes, AllowedMethods, RoutePatterns, ResourceLinks, OwnerPack, IsActive").
		WithArgs(scope.Name).
		WillReturnRows(sqlmock.NewRows([]string{
			"Id",
			"Name",
			"Description",
			"CustomPermissionId",
			"AllowedClientTypes",
			"AllowedMethods",
			"RoutePatterns",
			"ResourceLinks",
			"OwnerPack",
			"IsActive",
			"AllowDynamicPublicClients",
		}).AddRow(
			scope.ID,
			scope.Name,
			scope.Description,
			nil,
			strings.Join(scope.AllowedClientTypes, " "),
			strings.Join(scope.AllowedMethods, " "),
			strings.Join(scope.RoutePatterns, " "),
			strings.Join(scope.ResourceLinks, " "),
			scope.OwnerPack,
			scope.Active,
			scope.AllowDynamicPublicClients,
		))

	verifier := component.(componentCustomVerifier)
	if err := verifier.VerifyComponent(context.Background(), VerifyDeps{DB: db}); err != nil {
		t.Fatalf("VerifyComponent: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

// TestSourceBackedOAuthCustomScopeAllowDynamicPublicClientsRoundTrips pins the
// pairing with NexusCRM#3986: a scope seed that opts in to dynamic public
// client registration must bind allow_dynamic_public_clients=1 on upsert and
// read it back on the select projection so Verify converges instead of drifting.
func TestSourceBackedOAuthCustomScopeAllowDynamicPublicClientsRoundTrips(t *testing.T) {
	payload := []byte(`{
		"id": "oauthscope_external_records_read",
		"name": "external.records.read",
		"description": "Read-only access to an app-pack records REST API for public OAuth clients.",
		"allowed_client_types": ["public"],
		"allowed_methods": ["GET", "HEAD"],
		"route_family_prefix": "/services/data/", "route_patterns": ["/services/data", "/services/data/:version/sobjects"],
		"resource_links": ["sobjects"],
		"active": true,
		"allow_dynamic_public_clients": true
	}`)
	component, err := newSourceBackedOAuthCustomScopeComponent("crm-pack", ManifestComponent{
		Kind:          ComponentKindOAuthCustomScope,
		BusinessKey:   "external.records.read",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload)
	if err != nil {
		t.Fatalf("newSourceBackedOAuthCustomScopeComponent: %v", err)
	}
	expected, err := component.(*sourceBackedManagedComponent).ExpectedProjection()
	if err != nil {
		t.Fatalf("ExpectedProjection: %v", err)
	}
	scope := expected.(bootstrapOAuthCustomScope)
	if !scope.AllowDynamicPublicClients {
		t.Fatal("ExpectedProjection dropped allow_dynamic_public_clients opt-in")
	}

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

	mock.ExpectQuery("SELECT OwnerPack").
		WithArgs(scope.Name).
		WillReturnRows(sqlmock.NewRows([]string{"OwnerPack"}))
	expectOAuthScopeAllowDynamicColumnProbe(mock, true)
	mock.ExpectExec("INSERT INTO _System_OAuthCustomScope").
		WithArgs(
			scope.ID,
			scope.Name,
			scope.Description,
			nil,
			strings.Join(scope.AllowedClientTypes, " "),
			strings.Join(scope.AllowedMethods, " "),
			strings.Join(scope.RoutePatterns, " "),
			strings.Join(scope.ResourceLinks, " "),
			scope.OwnerPack,
			scope.Active,
			true,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))

	if err := component.Apply(context.Background(), ApplyDeps{DB: db}); err != nil {
		t.Fatalf("Apply: %v", err)
	}

	expectOAuthScopeAllowDynamicColumnProbe(mock, true)
	mock.ExpectQuery("SELECT Id, Name, Description, CustomPermissionId, AllowedClientTypes, AllowedMethods, RoutePatterns, ResourceLinks, OwnerPack, IsActive").
		WithArgs(scope.Name).
		WillReturnRows(sqlmock.NewRows([]string{
			"Id",
			"Name",
			"Description",
			"CustomPermissionId",
			"AllowedClientTypes",
			"AllowedMethods",
			"RoutePatterns",
			"ResourceLinks",
			"OwnerPack",
			"IsActive",
			"AllowDynamicPublicClients",
		}).AddRow(
			scope.ID,
			scope.Name,
			scope.Description,
			nil,
			strings.Join(scope.AllowedClientTypes, " "),
			strings.Join(scope.AllowedMethods, " "),
			strings.Join(scope.RoutePatterns, " "),
			strings.Join(scope.ResourceLinks, " "),
			scope.OwnerPack,
			scope.Active,
			true,
		))

	live, err := loadOAuthCustomScopeSeed(context.Background(), db, scope.Name)
	if err != nil {
		t.Fatalf("loadOAuthCustomScopeSeed: %v", err)
	}
	if !live.AllowDynamicPublicClients {
		t.Fatal("loadOAuthCustomScopeSeed did not read back allow_dynamic_public_clients")
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

// TestSourceBackedOAuthCustomScopeWarmUpgradeMissingColumnFallsBackToLegacySQL
// pins restart-safe schema provisioning (the Codex blocker on PR #203): a
// warm-upgrade DB that has NOT applied NexusCRM#3986 lacks the
// AllowDynamicPublicClients column. An opt-in-false seed (every existing scope)
// must still upsert and verify against the legacy column set instead of failing
// with an unknown-column error.
func TestSourceBackedOAuthCustomScopeWarmUpgradeMissingColumnFallsBackToLegacySQL(t *testing.T) {
	payload := []byte(`{
		"id": "oauthscope_external_records_read",
		"name": "external.records.read",
		"description": "Read-only access to an app-pack records REST API for public OAuth clients.",
		"allowed_client_types": ["public"],
		"allowed_methods": ["GET", "HEAD"],
		"route_family_prefix": "/services/data/", "route_patterns": ["/services/data", "/services/data/:version/sobjects"],
		"resource_links": ["sobjects"],
		"active": true
	}`)
	component, err := newSourceBackedOAuthCustomScopeComponent("crm-pack", ManifestComponent{
		Kind:          ComponentKindOAuthCustomScope,
		BusinessKey:   "external.records.read",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload)
	if err != nil {
		t.Fatalf("newSourceBackedOAuthCustomScopeComponent: %v", err)
	}
	expected, err := component.(*sourceBackedManagedComponent).ExpectedProjection()
	if err != nil {
		t.Fatalf("ExpectedProjection: %v", err)
	}
	seed := expected.(bootstrapOAuthCustomScope)

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

	// Apply: owner claim, then a column probe that reports the column ABSENT,
	// then a legacy INSERT with no AllowDynamicPublicClients column/value.
	mock.ExpectQuery("SELECT OwnerPack").
		WithArgs(seed.Name).
		WillReturnRows(sqlmock.NewRows([]string{"OwnerPack"}))
	expectOAuthScopeAllowDynamicColumnProbe(mock, false)
	mock.ExpectExec("INSERT INTO _System_OAuthCustomScope").
		WithArgs(
			seed.ID,
			seed.Name,
			seed.Description,
			nil,
			strings.Join(seed.AllowedClientTypes, " "),
			strings.Join(seed.AllowedMethods, " "),
			strings.Join(seed.RoutePatterns, " "),
			strings.Join(seed.ResourceLinks, " "),
			seed.OwnerPack,
			seed.Active,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))

	if err := component.Apply(context.Background(), ApplyDeps{DB: db}); err != nil {
		t.Fatalf("Apply on warm-upgrade schema: %v", err)
	}

	// Verify: probe ABSENT again, then a legacy SELECT with the 10-column set;
	// the loader must default AllowDynamicPublicClients=false and converge.
	expectOAuthScopeAllowDynamicColumnProbe(mock, false)
	mock.ExpectQuery("SELECT Id, Name, Description, CustomPermissionId, AllowedClientTypes, AllowedMethods, RoutePatterns, ResourceLinks, OwnerPack, IsActive").
		WithArgs(seed.Name).
		WillReturnRows(sqlmock.NewRows([]string{
			"Id",
			"Name",
			"Description",
			"CustomPermissionId",
			"AllowedClientTypes",
			"AllowedMethods",
			"RoutePatterns",
			"ResourceLinks",
			"OwnerPack",
			"IsActive",
		}).AddRow(
			seed.ID,
			seed.Name,
			seed.Description,
			nil,
			strings.Join(seed.AllowedClientTypes, " "),
			strings.Join(seed.AllowedMethods, " "),
			strings.Join(seed.RoutePatterns, " "),
			strings.Join(seed.ResourceLinks, " "),
			seed.OwnerPack,
			seed.Active,
		))

	live, err := loadOAuthCustomScopeSeed(context.Background(), db, seed.Name)
	if err != nil {
		t.Fatalf("loadOAuthCustomScopeSeed on warm-upgrade schema: %v", err)
	}
	if live.AllowDynamicPublicClients {
		t.Fatal("warm-upgrade load must default AllowDynamicPublicClients to false")
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

// TestSourceBackedOAuthCustomScopeWarmUpgradeMissingColumnRejectsOptIn pins that
// a seed which explicitly opts in to dynamic public clients fails loudly on a
// warm-upgrade DB missing the column, rather than silently dropping the opt-in
// or emitting an unknown-column write.
func TestSourceBackedOAuthCustomScopeWarmUpgradeMissingColumnRejectsOptIn(t *testing.T) {
	payload := []byte(`{
		"id": "oauthscope_external_records_read",
		"name": "external.records.read",
		"description": "Read-only access to an app-pack records REST API for public OAuth clients.",
		"allowed_client_types": ["public"],
		"allowed_methods": ["GET", "HEAD"],
		"route_family_prefix": "/services/data/", "route_patterns": ["/services/data", "/services/data/:version/sobjects"],
		"resource_links": ["sobjects"],
		"active": true,
		"allow_dynamic_public_clients": true
	}`)
	component, err := newSourceBackedOAuthCustomScopeComponent("crm-pack", ManifestComponent{
		Kind:          ComponentKindOAuthCustomScope,
		BusinessKey:   "external.records.read",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload)
	if err != nil {
		t.Fatalf("newSourceBackedOAuthCustomScopeComponent: %v", err)
	}
	expected, err := component.(*sourceBackedManagedComponent).ExpectedProjection()
	if err != nil {
		t.Fatalf("ExpectedProjection: %v", err)
	}
	seed := expected.(bootstrapOAuthCustomScope)

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

	mock.ExpectQuery("SELECT OwnerPack").
		WithArgs(seed.Name).
		WillReturnRows(sqlmock.NewRows([]string{"OwnerPack"}))
	expectOAuthScopeAllowDynamicColumnProbe(mock, false)
	// No INSERT expectation: the guard must fire before any write.

	err = component.Apply(context.Background(), ApplyDeps{DB: db})
	if err == nil {
		t.Fatal("Apply accepted allow_dynamic_public_clients opt-in on warm-upgrade schema missing the column")
	}
	if !strings.Contains(err.Error(), "NexusCRM#3986") || !strings.Contains(err.Error(), "allow_dynamic_public_clients") {
		t.Fatalf("Apply error = %v, want a NexusCRM#3986 migration-required message", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedOAuthCustomScopeAdoptsMatchingOwnerlessLegacyRow(t *testing.T) {
	payload := []byte(`{
		"id": "oauthscope_external_records_read",
		"name": "external.records.read",
		"description": "Read-only access to an app-pack records REST API for public OAuth clients.",
		"allowed_client_types": ["public"],
		"allowed_methods": ["GET", "HEAD"],
		"route_family_prefix": "/services/data/", "route_patterns": ["/services/data", "/services/data/:version/sobjects"],
		"resource_links": ["sobjects"],
		"active": true
	}`)
	component, err := newSourceBackedOAuthCustomScopeComponent("crm-pack", ManifestComponent{
		Kind:          ComponentKindOAuthCustomScope,
		BusinessKey:   "external.records.read",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload)
	if err != nil {
		t.Fatalf("newSourceBackedOAuthCustomScopeComponent: %v", err)
	}
	expected, err := component.(*sourceBackedManagedComponent).ExpectedProjection()
	if err != nil {
		t.Fatalf("ExpectedProjection: %v", err)
	}
	scope := expected.(bootstrapOAuthCustomScope)

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

	mock.ExpectQuery("SELECT OwnerPack").
		WithArgs(scope.Name).
		WillReturnRows(sqlmock.NewRows([]string{"OwnerPack"}).AddRow(""))
	expectOAuthScopeAllowDynamicColumnProbe(mock, true)
	mock.ExpectQuery("SELECT Id, Name, Description, CustomPermissionId, AllowedClientTypes, AllowedMethods, RoutePatterns, ResourceLinks, OwnerPack, IsActive").
		WithArgs(scope.Name).
		WillReturnRows(sqlmock.NewRows([]string{
			"Id",
			"Name",
			"Description",
			"CustomPermissionId",
			"AllowedClientTypes",
			"AllowedMethods",
			"RoutePatterns",
			"ResourceLinks",
			"OwnerPack",
			"IsActive",
			"AllowDynamicPublicClients",
		}).AddRow(
			scope.ID,
			scope.Name,
			scope.Description,
			nil,
			strings.Join(scope.AllowedClientTypes, " "),
			strings.Join(scope.AllowedMethods, " "),
			strings.Join(scope.RoutePatterns, " "),
			strings.Join(scope.ResourceLinks, " "),
			"",
			scope.Active,
			scope.AllowDynamicPublicClients,
		))
	expectOAuthScopeAllowDynamicColumnProbe(mock, true)
	mock.ExpectExec("INSERT INTO _System_OAuthCustomScope").
		WithArgs(
			scope.ID,
			scope.Name,
			scope.Description,
			nil,
			strings.Join(scope.AllowedClientTypes, " "),
			strings.Join(scope.AllowedMethods, " "),
			strings.Join(scope.RoutePatterns, " "),
			strings.Join(scope.ResourceLinks, " "),
			scope.OwnerPack,
			scope.Active,
			scope.AllowDynamicPublicClients,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))

	if err := component.Apply(context.Background(), ApplyDeps{DB: db}); err != nil {
		t.Fatalf("Apply: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedOAuthCustomScopeRejectsDivergedOwnerlessRow(t *testing.T) {
	payload := []byte(`{
		"id": "oauthscope_external_records_read",
		"name": "external.records.read",
		"description": "Read-only access to an app-pack records REST API for public OAuth clients.",
		"allowed_client_types": ["public"],
		"allowed_methods": ["GET", "HEAD"],
		"route_family_prefix": "/services/data/", "route_patterns": ["/services/data", "/services/data/:version/sobjects"],
		"resource_links": ["sobjects"],
		"active": true
	}`)
	component, err := newSourceBackedOAuthCustomScopeComponent("crm-pack", ManifestComponent{
		Kind:          ComponentKindOAuthCustomScope,
		BusinessKey:   "external.records.read",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload)
	if err != nil {
		t.Fatalf("newSourceBackedOAuthCustomScopeComponent: %v", err)
	}
	expected, err := component.(*sourceBackedManagedComponent).ExpectedProjection()
	if err != nil {
		t.Fatalf("ExpectedProjection: %v", err)
	}
	scope := expected.(bootstrapOAuthCustomScope)

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

	mock.ExpectQuery("SELECT OwnerPack").
		WithArgs(scope.Name).
		WillReturnRows(sqlmock.NewRows([]string{"OwnerPack"}).AddRow(""))
	expectOAuthScopeAllowDynamicColumnProbe(mock, true)
	mock.ExpectQuery("SELECT Id, Name, Description, CustomPermissionId, AllowedClientTypes, AllowedMethods, RoutePatterns, ResourceLinks, OwnerPack, IsActive").
		WithArgs(scope.Name).
		WillReturnRows(sqlmock.NewRows([]string{
			"Id",
			"Name",
			"Description",
			"CustomPermissionId",
			"AllowedClientTypes",
			"AllowedMethods",
			"RoutePatterns",
			"ResourceLinks",
			"OwnerPack",
			"IsActive",
			"AllowDynamicPublicClients",
		}).AddRow(
			scope.ID,
			scope.Name,
			scope.Description,
			nil,
			strings.Join(scope.AllowedClientTypes, " "),
			strings.Join(scope.AllowedMethods, " "),
			"/services/data/not-the-pack-contract",
			strings.Join(scope.ResourceLinks, " "),
			"",
			scope.Active,
			scope.AllowDynamicPublicClients,
		))

	err = component.Apply(context.Background(), ApplyDeps{DB: db})
	if err == nil {
		t.Fatal("Apply accepted diverged ownerless OAuth custom scope")
	}
	if !strings.Contains(err.Error(), "ownerless legacy row does not match pack") {
		t.Fatalf("Apply error = %v, want ownerless legacy mismatch", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedOAuthCustomScopeCanonicalizesMixedCaseName(t *testing.T) {
	payload := []byte(`{
		"id": "oauthscope_external_records_read",
		"name": "External.Records.Read",
		"description": "Read-only access to an app-pack records REST API for public OAuth clients.",
		"allowed_client_types": ["public"],
		"allowed_methods": ["GET", "HEAD"],
		"route_family_prefix": "/services/data/", "route_patterns": ["/services/data", "/services/data/:version/sobjects"],
		"resource_links": ["sobjects"],
		"active": true
	}`)
	component, err := newSourceBackedOAuthCustomScopeComponent("crm-pack", ManifestComponent{
		Kind:          ComponentKindOAuthCustomScope,
		BusinessKey:   "external.records.read",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload)
	if err != nil {
		t.Fatalf("newSourceBackedOAuthCustomScopeComponent: %v", err)
	}
	expected, err := component.(*sourceBackedManagedComponent).ExpectedProjection()
	if err != nil {
		t.Fatalf("ExpectedProjection: %v", err)
	}
	scope := expected.(bootstrapOAuthCustomScope)
	if scope.Name != "external.records.read" {
		t.Fatalf("canonical scope name = %q, want external.records.read", scope.Name)
	}

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

	mock.ExpectQuery("SELECT OwnerPack").
		WithArgs("external.records.read").
		WillReturnRows(sqlmock.NewRows([]string{"OwnerPack"}))
	expectOAuthScopeAllowDynamicColumnProbe(mock, true)
	mock.ExpectExec("INSERT INTO _System_OAuthCustomScope").
		WithArgs(
			scope.ID,
			"external.records.read",
			scope.Description,
			nil,
			strings.Join(scope.AllowedClientTypes, " "),
			strings.Join(scope.AllowedMethods, " "),
			strings.Join(scope.RoutePatterns, " "),
			strings.Join(scope.ResourceLinks, " "),
			scope.OwnerPack,
			scope.Active,
			scope.AllowDynamicPublicClients,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))

	if err := component.Apply(context.Background(), ApplyDeps{DB: db}); err != nil {
		t.Fatalf("Apply: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedOAuthCustomScopeRejectsProductRouteScopeName(t *testing.T) {
	reset := featureauth.SetProductOAuthRouteScopeOwners([]featureauth.ProductOAuthRouteScopeOwner{{Scope: "vendor.builtin", OwningAppPacks: []string{"crm-pack"}}})
	t.Cleanup(reset)

	payload := []byte(`{
		"oauth_custom_scopes": [{
			"id": "oauthscope_vendor_builtin",
			"name": "Vendor.Builtin",
			"allowed_client_types": ["public"],
			"allowed_methods": ["GET"],
			"route_family_prefix": "/services/data/", "route_patterns": ["/services/data"],
			"active": true
		}]
	}`)
	if _, err := parseOAuthCustomScopeSeeds(payload, "test-source"); err == nil {
		t.Fatal("parseOAuthCustomScopeSeeds accepted product-owned OAuth route scope name")
	} else if !strings.Contains(err.Error(), `scope "vendor.builtin" must not shadow a reserved OAuth scope`) {
		t.Fatalf("error = %v, want reserved OAuth scope rejection", err)
	}
}

func TestSourceBackedOAuthCustomScopeRejectsNonCanonicalBusinessKey(t *testing.T) {
	payload := []byte(`{
		"id": "oauthscope_external_records_read",
		"name": "External.Records.Read",
		"allowed_client_types": ["public"],
		"allowed_methods": ["GET"],
		"route_family_prefix": "/services/data/", "route_patterns": ["/services/data"],
		"active": true
	}`)
	diagnostics := validateOAuthCustomScopeComponent(ManifestComponent{
		Kind:          ComponentKindOAuthCustomScope,
		BusinessKey:   "External.Records.Read",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload, "components[0]")
	if len(diagnostics) == 0 {
		t.Fatal("mixed-case businessKey was accepted for canonical source-backed oauth_custom_scope name")
	}
	if diagnostics[0].Code != "oauth_custom_scope.business_key.mismatch_name" {
		t.Fatalf("diagnostic code = %q, want oauth_custom_scope.business_key.mismatch_name", diagnostics[0].Code)
	}
}

func TestSourceBackedOAuthCustomScopeRejectsMalformedRoutePatternVersionSegment(t *testing.T) {
	payload := []byte(`{
		"id": "oauthscope_external_records_read",
		"name": "external.records.read",
		"allowed_client_types": ["public"],
		"allowed_methods": ["GET"],
		"route_family_prefix": "/services/data/", "route_patterns": ["/services/data/not-a-version/sobjects"],
		"active": true
	}`)
	diagnostics := validateOAuthCustomScopeComponent(ManifestComponent{
		Kind:          ComponentKindOAuthCustomScope,
		BusinessKey:   "external.records.read",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload, "components[0]")
	if len(diagnostics) == 0 {
		t.Fatal("malformed /services/data route-pattern version segment was accepted")
	}
	if diagnostics[0].Code != "component.payload.invalid_schema" {
		t.Fatalf("diagnostic code = %q, want component.payload.invalid_schema", diagnostics[0].Code)
	}
	if !strings.Contains(diagnostics[0].Message, "route pattern") {
		t.Fatalf("diagnostic message = %q, want route pattern validation error", diagnostics[0].Message)
	}
}

// TestOAuthCustomScopeRouteFamilyPrefixValidation pins the B2b S2 security
// boundary: the route-family prefix is taken from pack-declared seed metadata,
// NOT from the owning-pack identity (which is unset at validation time), and a
// seed that declares route_patterns without a prefix fails CLOSED rather than
// leaving the patterns unvalidated (the pre-B2b fail-open this replaces).
func TestOAuthCustomScopeRouteFamilyPrefixValidation(t *testing.T) {
	base := func() bootstrapOAuthCustomScope {
		return bootstrapOAuthCustomScope{
			ID:                 "oauthscope_external_records_read",
			Name:               "external.records.read",
			AllowedClientTypes: []string{"public"},
			AllowedMethods:     []string{"GET", "HEAD"},
			RoutePatterns:      []string{"/services/data", "/services/data/:version/sobjects"},
			RouteFamilyPrefix:  "/services/data/",
			Active:             true,
		}
	}

	// (1) route_patterns + empty prefix -> fail closed (NOT fail open).
	t.Run("empty prefix fails closed", func(t *testing.T) {
		seed := base()
		seed.RouteFamilyPrefix = ""
		if _, err := normalizeOAuthCustomScopeSeed(seed, false); err == nil {
			t.Fatal("route_patterns without route_family_prefix must fail closed")
		}
	})

	// (2) route_patterns + valid prefix -> valid, and the prefix is cleared from
	// the normalized (persisted) seed so it never enters the stored projection.
	t.Run("valid prefix accepted and not persisted", func(t *testing.T) {
		got, err := normalizeOAuthCustomScopeSeed(base(), false)
		if err != nil {
			t.Fatalf("valid seed rejected: %v", err)
		}
		if got.RouteFamilyPrefix != "" {
			t.Fatalf("RouteFamilyPrefix must be cleared post-validation, got %q", got.RouteFamilyPrefix)
		}
	})

	// (3) malformed family pattern under a valid prefix -> rejected.
	t.Run("malformed family pattern rejected", func(t *testing.T) {
		seed := base()
		seed.RoutePatterns = []string{"/services/data/not-a-version/sobjects"}
		if _, err := normalizeOAuthCustomScopeSeed(seed, false); err == nil {
			t.Fatal("malformed version segment must be rejected under the family prefix")
		}
	})

	// (4) boundary cases: bare "/services/data" and "/services/data/" both
	// validate under the family (non-versioned roots are not malformed).
	t.Run("boundary roots accepted", func(t *testing.T) {
		seed := base()
		seed.RoutePatterns = []string{"/services/data", "/services/data/"}
		if _, err := normalizeOAuthCustomScopeSeed(seed, false); err != nil {
			t.Fatalf("boundary roots must validate: %v", err)
		}
	})

	// (5) OwnerPack is unset at validation time and must not affect validation.
	t.Run("OwnerPack empty at validate is irrelevant", func(t *testing.T) {
		seed := base()
		seed.OwnerPack = "" // the source-backed builder sets this only AFTER normalize
		if _, err := normalizeOAuthCustomScopeSeed(seed, false); err != nil {
			t.Fatalf("validation must not depend on OwnerPack: %v", err)
		}
	})

	// (6) the pre-B2b fail-open cannot return: family-shaped malformed patterns
	// with an empty prefix must NOT be silently accepted as non-family.
	t.Run("no fail-open on empty prefix with family-shaped patterns", func(t *testing.T) {
		seed := base()
		seed.RouteFamilyPrefix = ""
		seed.RoutePatterns = []string{"/services/data/not-a-version/sobjects"}
		if _, err := normalizeOAuthCustomScopeSeed(seed, false); err == nil {
			t.Fatal("empty prefix + family-shaped patterns must fail closed, not pass through")
		}
	})
}

// TestOAuthCustomScopeInstalledRowRouteFamilyPrefixCompat pins the narrow
// installed-row-only warm-upgrade compat: historical payloads authored before
// route_family_prefix became pack metadata (route_patterns present, prefix
// absent) must FAIL CLOSED for fresh authoring but RESOLVE under the legacy
// Salesforce default during installed-row reconstruction. The legacy default
// still validates route patterns, so malformed patterns stay rejected even
// under compat.
func TestOAuthCustomScopeInstalledRowRouteFamilyPrefixCompat(t *testing.T) {
	oldShape := func() bootstrapOAuthCustomScope {
		return bootstrapOAuthCustomScope{
			ID:                 "oauthscope_external_records_read",
			Name:               "external.records.read",
			AllowedClientTypes: []string{"public"},
			AllowedMethods:     []string{"GET", "HEAD"},
			RoutePatterns:      []string{"/services/data", "/services/data/:version/sobjects"},
			// no RouteFamilyPrefix: this is the historical shape.
			Active: true,
		}
	}

	// (1) old-shape seed: fresh authoring (false) fails closed; installed-row
	// reconstruction (true) resolves it under the legacy default.
	t.Run("old shape fails closed fresh but resolves installed-row", func(t *testing.T) {
		if _, err := normalizeOAuthCustomScopeSeed(oldShape(), false); err == nil {
			t.Fatal("old-shape seed must fail closed for fresh authoring")
		}
		if _, err := normalizeOAuthCustomScopeSeed(oldShape(), true); err != nil {
			t.Fatalf("old-shape seed must resolve under installed-row compat: %v", err)
		}
	})

	// (2) under compat, a malformed family pattern is STILL rejected: the legacy
	// default validates the same version segments, so compat is not a bypass.
	t.Run("compat still rejects malformed family pattern", func(t *testing.T) {
		seed := oldShape()
		seed.RoutePatterns = []string{"/services/data/not-a-version/sobjects"}
		if _, err := normalizeOAuthCustomScopeSeed(seed, true); err == nil {
			t.Fatal("malformed family pattern must be rejected even under installed-row compat")
		}
	})

	// (3) new-shape seed (explicit prefix) validates under BOTH false and true:
	// compat never weakens an already-well-formed payload.
	t.Run("new shape validates under both flags", func(t *testing.T) {
		newShape := oldShape()
		newShape.RouteFamilyPrefix = "/services/data/"
		if _, err := normalizeOAuthCustomScopeSeed(newShape, false); err != nil {
			t.Fatalf("new-shape seed must validate fresh: %v", err)
		}
		if _, err := normalizeOAuthCustomScopeSeed(newShape, true); err != nil {
			t.Fatalf("new-shape seed must validate under compat: %v", err)
		}
	})

	// (4) component-level: the historical payload shape (external_records_read.json
	// WITHOUT the route_family_prefix key) is a diagnostic without compat and nil
	// with compat.
	t.Run("component-level compat resolves historical payload", func(t *testing.T) {
		oldShapePayloadJSON := []byte(`{
			"id": "oauthscope_external_records_read",
			"name": "external.records.read",
			"allowed_client_types": ["public"],
			"allowed_methods": ["GET", "HEAD"],
			"route_patterns": ["/services/data", "/services/data/:version/sobjects"],
			"active": true
		}`)
		component := ManifestComponent{
			Kind:          ComponentKindOAuthCustomScope,
			BusinessKey:   "external.records.read",
			Manageability: ManageabilityLocked,
			Payload:       oldShapePayloadJSON,
		}
		if diagnostics := validateOAuthCustomScopeComponentWithCompat(component, oldShapePayloadJSON, "components[0]", false); len(diagnostics) == 0 {
			t.Fatal("historical payload must produce a diagnostic without installed-row compat")
		}
		if diagnostics := validateOAuthCustomScopeComponentWithCompat(component, oldShapePayloadJSON, "components[0]", true); len(diagnostics) != 0 {
			t.Fatalf("historical payload must validate under installed-row compat, got %+v", diagnostics)
		}
	})
}

// writeAppPackWithLegacyOAuthScopeMissingRouteFamilyPrefix writes a temp app-pack
// whose oauth_custom_scope payload is the HISTORICAL shape: route_patterns present
// but NO route_family_prefix (the field postdates the prior CRM pack revisions the
// acceptance window still accepts).
func writeAppPackWithLegacyOAuthScopeMissingRouteFamilyPrefix(t *testing.T) string {
	t.Helper()
	components := `[{
    "kind":"oauth_custom_scope",
    "businessKey":"external.records.read",
    "manageability":"locked",
    "payload":{
      "id":"oauthscope_external_records_read",
      "name":"external.records.read",
      "allowed_client_types":["public"],
      "allowed_methods":["GET","HEAD"],
      "route_patterns":["/services/data","/services/data/:version/sobjects"],
      "resource_links":["sobjects"],
      "active":true
    }
  }]`
	return writePackFixture(t, "jira-like-sample", validPackManifest(components, `"dependencies": [],`))
}

// TestResolveInstalledOAuthScopeMissingRouteFamilyPrefix_HistoricalResolves is the
// RESOLVER-LEVEL regression for the installed-row compat. It drives the full
// resolveInstalledSourceBackedPackWithStoredIdentity path, which builds an
// InstallablePackSourceReport that INTERNALLY constructs the pack from reportOpts
// (NewSourceBackedPack) before the resolver's own packOpts are applied. So a
// historical oauth_custom_scope payload (route_patterns, no route_family_prefix)
// fails closed during report construction — and the resolver returns early at
// report.HasErrors() — unless the compat is wired into reportOpts, not only
// packOpts. Warm-upgrade orgs must still resolve their installed pack.
func TestResolveInstalledOAuthScopeMissingRouteFamilyPrefix_HistoricalResolves(t *testing.T) {
	sourceDir := writeAppPackWithLegacyOAuthScopeMissingRouteFamilyPrefix(t)
	pack, err := resolveInstalledSourceBackedPackWithStoredIdentity(
		"jira-like-sample",
		AppPack,
		PackSourceKindLocalPath,
		sourceDir,
		SupportedManifestFormatVersion,
		"",
	)
	if err != nil {
		t.Fatalf("installed-row resolution of a historical oauth_custom_scope (no route_family_prefix) must succeed under compat: %v", err)
	}
	if pack == nil {
		t.Fatal("expected a resolved pack")
	}
}

// TestValidateOAuthScopeMissingRouteFamilyPrefix_FreshRejects pins that FRESH
// validation of the same old-shape source still fails closed (the compat is
// installed-row-only; new authoring must declare route_family_prefix).
func TestValidateOAuthScopeMissingRouteFamilyPrefix_FreshRejects(t *testing.T) {
	sourceDir := writeAppPackWithLegacyOAuthScopeMissingRouteFamilyPrefix(t)
	report := ValidateInstallablePackSourceWithOptions("local_path:"+sourceDir, testPackSourceOptions(t))
	rejected := false
	for _, d := range report.Diagnostics {
		if strings.Contains(d.String(), "route_family_prefix") {
			rejected = true
		}
	}
	if !rejected {
		t.Fatalf("fresh installability validation of oauth_custom_scope without route_family_prefix must fail closed with a route_family_prefix diagnostic; got: %v", report.Diagnostics)
	}
}

func TestSourceBackedConnectedAppComponentApplyAndVerify(t *testing.T) {
	payload := []byte(connectedAppComponentManifestPayload())
	component, err := newSourceBackedConnectedAppComponent("oauth-client-pack", ManifestComponent{
		Kind:          ComponentKindConnectedApp,
		BusinessKey:   "synthetic-client",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload)
	if err != nil {
		t.Fatalf("newSourceBackedConnectedAppComponent: %v", err)
	}

	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })
	expectedAny, err := component.ExpectedProjection()
	if err != nil {
		t.Fatalf("ExpectedProjection: %v", err)
	}
	expected := expectedAny.(sourceBackedConnectedAppComponentProjection)

	mock.ExpectBegin()
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey, CallbackUrl, RedirectUris, Scopes, IsActive, ClientType, RequirePkce,").
		WithArgs("synthetic-client").
		WillReturnRows(connectedAppProjectionRows())
	mock.ExpectQuery("SELECT Id, InstalledPackName").
		WithArgs("synthetic-client").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "InstalledPackName", "IsDeleted"}))
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey").
		WithArgs("synthetic-client", "Synthetic Client", "synthetic-client-key").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name", "ConsumerKey", "IsActive"}))
	mock.ExpectExec("UPDATE _System_ConnectedAppDeclaration").
		WithArgs(
			expected.Declaration.PayloadHash,
			0,
			expected.Declaration.ID,
			"oauth-client-pack",
			"synthetic-client",
		).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectConnectedAppDeclarationExists(mock, expected.Declaration, false)
	mock.ExpectExec("INSERT INTO _System_ConnectedAppDeclaration").
		WithArgs(
			expected.Declaration.ID,
			"oauth-client-pack",
			"synthetic-client",
			expected.Declaration.PayloadHash,
			0,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))
	mock.ExpectExec("UPDATE _System_ConnectedAppStage").
		WithArgs(
			expected.Declaration.PayloadHash,
			sqlmock.AnyArg(),
			0,
			expected.Declaration.ID,
			"oauth-client-pack",
			"synthetic-client",
		).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectConnectedAppStageExists(mock, sourceBackedConnectedAppStageRow{
		ID:                expected.Declaration.ID,
		InstalledPackName: "oauth-client-pack",
		ConnectedAppID:    "synthetic-client",
	}, false)
	mock.ExpectExec("INSERT INTO _System_ConnectedAppStage").
		WithArgs(
			expected.Declaration.ID,
			"oauth-client-pack",
			"synthetic-client",
			expected.Declaration.PayloadHash,
			sqlmock.AnyArg(),
			0,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))
	mock.ExpectCommit()

	hook := &connectedAppInvalidationHookRecorder{}
	if err := component.Apply(context.Background(), ApplyDeps{DB: db, ConnectedAppHook: hook}); err != nil {
		t.Fatalf("Apply: %v", err)
	}
	if got, want := strings.Join(hook.appIDs, ","), ""; got != want {
		t.Fatalf("ConnectedAppHook appIDs = %q, want %q", got, want)
	}

	expectConnectedAppPackStatus(mock, "oauth-client-pack", "installing")
	expectConnectedAppStageQuery(mock, sourceBackedConnectedAppStageRow{
		ID:                expected.Declaration.ID,
		InstalledPackName: "oauth-client-pack",
		ConnectedAppID:    "synthetic-client",
		PayloadHash:       expected.Declaration.PayloadHash,
		RecordJSON:        sourceBackedConnectedAppStageRecordJSON(t),
		IsDeleted:         0,
	})
	expectConnectedAppDeclarationQuery(mock, expected.Declaration)

	verifier := component.(componentCustomVerifier)
	if err := verifier.VerifyComponent(context.Background(), VerifyDeps{DB: db}); err != nil {
		t.Fatalf("VerifyComponent: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedConnectedAppComponentVerifyAcceptsStagedNumericLimits(t *testing.T) {
	payload := connectedAppComponentManifestPayloadWithOverrides(t, map[string]any{
		"dailyApiLimit":  5000,
		"hourlyApiLimit": 600,
	})
	component, err := newSourceBackedConnectedAppComponent("oauth-client-pack", ManifestComponent{
		Kind:          ComponentKindConnectedApp,
		BusinessKey:   "synthetic-client",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload)
	if err != nil {
		t.Fatalf("newSourceBackedConnectedAppComponent: %v", err)
	}
	expectedAny, err := component.ExpectedProjection()
	if err != nil {
		t.Fatalf("ExpectedProjection: %v", err)
	}
	expected := expectedAny.(sourceBackedConnectedAppComponentProjection)
	if expected.App.DailyAPILimit == nil || *expected.App.DailyAPILimit != 5000 {
		t.Fatalf("expected DailyAPILimit = %#v, want 5000", expected.App.DailyAPILimit)
	}
	if expected.App.HourlyAPILimit == nil || *expected.App.HourlyAPILimit != 600 {
		t.Fatalf("expected HourlyAPILimit = %#v, want 600", expected.App.HourlyAPILimit)
	}

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

	expectConnectedAppPackStatus(mock, "oauth-client-pack", "installing")
	expectConnectedAppStageQuery(mock, sourceBackedConnectedAppStageRow{
		ID:                expected.Declaration.ID,
		InstalledPackName: "oauth-client-pack",
		ConnectedAppID:    "synthetic-client",
		PayloadHash:       expected.Declaration.PayloadHash,
		RecordJSON:        sourceBackedConnectedAppStageRecordJSONForPayload(t, "oauth-client-pack", "synthetic-client", payload),
		IsDeleted:         0,
	})
	expectConnectedAppDeclarationQuery(mock, expected.Declaration)

	verifier := component.(componentCustomVerifier)
	if err := verifier.VerifyComponent(context.Background(), VerifyDeps{DB: db}); err != nil {
		t.Fatalf("VerifyComponent: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedConnectedAppPublishWritesLiveRowsAtPackActivation(t *testing.T) {
	expected := sourceBackedConnectedAppExpectedProjection(t)
	stage := sourceBackedConnectedAppStageRow{
		ID:                expected.Declaration.ID,
		InstalledPackName: "oauth-client-pack",
		ConnectedAppID:    "synthetic-client",
		PayloadHash:       expected.Declaration.PayloadHash,
		RecordJSON:        sourceBackedConnectedAppStageRecordJSON(t),
		IsDeleted:         0,
	}
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })

	mock.ExpectBegin()
	mock.ExpectQuery("SELECT Id, InstalledPackName, ConnectedAppId, PayloadHash, RecordJson").
		WithArgs("oauth-client-pack").
		WillReturnRows(sqlmock.NewRows([]string{
			"Id",
			"InstalledPackName",
			"ConnectedAppId",
			"PayloadHash",
			"RecordJson",
			"IsDeleted",
		}).AddRow(stage.ID, stage.InstalledPackName, stage.ConnectedAppID, stage.PayloadHash, stage.RecordJSON, stage.IsDeleted))
	mock.ExpectQuery("SELECT Id FROM _System_ConnectedApp").
		WithArgs("synthetic-client").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectExec("INSERT INTO _System_ConnectedApp").
		WithArgs(
			"synthetic-client",
			"Synthetic Client",
			"synthetic-client-key",
			nil,
			sqlmock.AnyArg(),
			"https://client.example/callback",
			`["https://client.example/callback"]`,
			"api",
			true,
			"confidential",
			false,
			"client_secret_post",
			"authorization_code password",
			"trusted",
			1,
			false,
			nil,
			nil,
			nil,
			"AllUsers",
			nil,
			nil,
			nil,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))
	mock.ExpectExec("UPDATE `_System_Session` SET").
		WithArgs("synthetic-client").
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("DELETE FROM _System_ConnectedAppStage").
		WithArgs("oauth-client-pack").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectCommit()
	changed, err := func() ([]string, error) {
		tx, err := db.BeginTx(context.Background(), nil)
		if err != nil {
			return nil, err
		}
		committed := false
		defer func() {
			if !committed {
				_ = tx.Rollback()
			}
		}()
		changed, err := PublishSourceBackedConnectedAppsForPackInTx(context.Background(), tx, "oauth-client-pack")
		if err != nil {
			return nil, err
		}
		if err := tx.Commit(); err != nil {
			return nil, err
		}
		committed = true
		return changed, nil
	}()
	if err != nil {
		t.Fatalf("PublishSourceBackedConnectedAppsForPackInTx: %v", err)
	}
	if got, want := strings.Join(changed, ","), "synthetic-client"; got != want {
		t.Fatalf("changed app IDs = %q, want %q", got, want)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedConnectedAppPublishHashesPlaintextSecretFromHandWrittenStage(t *testing.T) {
	packName := "oauth-client-pack"
	appID := "synthetic-client"
	stage := sourceBackedConnectedAppStageRow{
		ID:                sourceBackedConnectedAppDeclarationID(packName, appID),
		InstalledPackName: packName,
		ConnectedAppID:    appID,
		PayloadHash:       "stage-hash",
		RecordJSON:        sourceBackedConnectedAppStageRecordJSONWithPlaintextSecret(t, appID, "Synthetic Client", "synthetic-client-key", "handwritten-secret"),
		IsDeleted:         0,
	}
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })

	mock.ExpectBegin()
	mock.ExpectQuery("SELECT Id, InstalledPackName, ConnectedAppId, PayloadHash, RecordJson").
		WithArgs(packName).
		WillReturnRows(sqlmock.NewRows([]string{
			"Id",
			"InstalledPackName",
			"ConnectedAppId",
			"PayloadHash",
			"RecordJson",
			"IsDeleted",
		}).AddRow(stage.ID, stage.InstalledPackName, stage.ConnectedAppID, stage.PayloadHash, stage.RecordJSON, stage.IsDeleted))
	mock.ExpectQuery("SELECT Id FROM _System_ConnectedApp").
		WithArgs(appID).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectExec("INSERT INTO _System_ConnectedApp").
		WithArgs(
			appID,
			"Synthetic Client",
			"synthetic-client-key",
			nil,
			bcryptHashArg{secret: "handwritten-secret"},
			"https://client.example/callback",
			`["https://client.example/callback"]`,
			"api",
			true,
			"confidential",
			false,
			"client_secret_post",
			"authorization_code password",
			"trusted",
			1,
			false,
			nil,
			nil,
			nil,
			"AllUsers",
			nil,
			nil,
			nil,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))
	mock.ExpectExec("UPDATE `_System_Session` SET").
		WithArgs(appID).
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("DELETE FROM _System_ConnectedAppStage").
		WithArgs(packName).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectCommit()

	changed, err := func() ([]string, error) {
		tx, err := db.BeginTx(context.Background(), nil)
		if err != nil {
			return nil, err
		}
		committed := false
		defer func() {
			if !committed {
				_ = tx.Rollback()
			}
		}()
		changed, err := PublishSourceBackedConnectedAppsForPackInTx(context.Background(), tx, packName)
		if err != nil {
			return nil, err
		}
		if err := tx.Commit(); err != nil {
			return nil, err
		}
		committed = true
		return changed, nil
	}()
	if err != nil {
		t.Fatalf("PublishSourceBackedConnectedAppsForPackInTx: %v", err)
	}
	if got, want := strings.Join(changed, ","), appID; got != want {
		t.Fatalf("changed app IDs = %q, want %q", got, want)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedConnectedAppPublishRenamesSamePackClientByTombstoningOldKeysFirst(t *testing.T) {
	packName := "oauth-client-pack"
	oldID := "old-client"
	newID := "new-client"
	oldStage := sourceBackedConnectedAppStageRow{
		ID:                sourceBackedConnectedAppDeclarationID(packName, oldID),
		InstalledPackName: packName,
		ConnectedAppID:    oldID,
		PayloadHash:       "old-hash",
		IsDeleted:         1,
	}
	newStage := sourceBackedConnectedAppStageRow{
		ID:                sourceBackedConnectedAppDeclarationID(packName, newID),
		InstalledPackName: packName,
		ConnectedAppID:    newID,
		PayloadHash:       "new-hash",
		RecordJSON:        sourceBackedConnectedAppStageRecordJSONFor(t, newID, "Shared Client", "shared-key"),
		IsDeleted:         0,
	}
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })

	mock.ExpectBegin()
	mock.ExpectQuery("SELECT Id, InstalledPackName, ConnectedAppId, PayloadHash, RecordJson").
		WithArgs(packName).
		WillReturnRows(sqlmock.NewRows([]string{
			"Id",
			"InstalledPackName",
			"ConnectedAppId",
			"PayloadHash",
			"RecordJson",
			"IsDeleted",
		}).
			AddRow(oldStage.ID, oldStage.InstalledPackName, oldStage.ConnectedAppID, oldStage.PayloadHash, nil, oldStage.IsDeleted).
			AddRow(newStage.ID, newStage.InstalledPackName, newStage.ConnectedAppID, newStage.PayloadHash, newStage.RecordJSON, newStage.IsDeleted))
	mock.ExpectExec("UPDATE _System_ConnectedApp").
		WithArgs(
			sourceBackedConnectedAppTombstoneValue("deleted connected_app", oldID),
			sourceBackedConnectedAppTombstoneValue("deleted_consumer_key", oldID),
			oldID,
		).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec("UPDATE _System_ConnectedAppDeclaration").
		WithArgs(oldStage.PayloadHash, 1, oldStage.ID, packName, oldID).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec("UPDATE `_System_Session` SET").
		WithArgs(oldID).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectQuery("SELECT Id FROM _System_ConnectedApp").
		WithArgs(newID).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectExec("INSERT INTO _System_ConnectedApp").
		WithArgs(
			newID,
			"Shared Client",
			"shared-key",
			nil,
			sqlmock.AnyArg(),
			"https://client.example/callback",
			`["https://client.example/callback"]`,
			"api",
			true,
			"confidential",
			false,
			"client_secret_post",
			"authorization_code password",
			"trusted",
			1,
			false,
			nil,
			nil,
			nil,
			"AllUsers",
			nil,
			nil,
			nil,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))
	mock.ExpectExec("UPDATE `_System_Session` SET").
		WithArgs(newID).
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("DELETE FROM _System_ConnectedAppStage").
		WithArgs(packName).
		WillReturnResult(sqlmock.NewResult(0, 2))
	mock.ExpectCommit()

	changed, err := func() ([]string, error) {
		tx, err := db.BeginTx(context.Background(), nil)
		if err != nil {
			return nil, err
		}
		committed := false
		defer func() {
			if !committed {
				_ = tx.Rollback()
			}
		}()
		changed, err := PublishSourceBackedConnectedAppsForPackInTx(context.Background(), tx, packName)
		if err != nil {
			return nil, err
		}
		if err := tx.Commit(); err != nil {
			return nil, err
		}
		committed = true
		return changed, nil
	}()
	if err != nil {
		t.Fatalf("PublishSourceBackedConnectedAppsForPackInTx: %v", err)
	}
	if got, want := strings.Join(changed, ","), "new-client,old-client"; got != want {
		t.Fatalf("changed app IDs = %q, want %q", got, want)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedConnectedAppComponentApplyAllowsOwnerlessPublicConsumerKeyAdoption(t *testing.T) {
	packName := "oauth-client-pack"
	legacyID := "legacy-public-bi-uuid"
	payload := publicConnectedAppComponentManifestPayload(t)
	component, err := newSourceBackedConnectedAppComponentWithPolicy(packName, ManifestComponent{
		Kind:          ComponentKindConnectedApp,
		BusinessKey:   "synthetic-client",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload, sourceBackedConnectedAppPublicScopePolicy{"external.records.read": {packName: {}}})
	if err != nil {
		t.Fatalf("newSourceBackedConnectedAppComponentWithPolicy: %v", err)
	}
	expectedAny, err := component.ExpectedProjection()
	if err != nil {
		t.Fatalf("ExpectedProjection: %v", err)
	}
	expected := expectedAny.(sourceBackedConnectedAppComponentProjection)

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

	mock.ExpectBegin()
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey, CallbackUrl, RedirectUris, Scopes, IsActive, ClientType, RequirePkce,").
		WithArgs("synthetic-client").
		WillReturnRows(connectedAppProjectionRows())
	mock.ExpectQuery("SELECT Id, InstalledPackName").
		WithArgs("synthetic-client").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "InstalledPackName", "IsDeleted"}))
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey").
		WithArgs("synthetic-client", "Synthetic Client", "synthetic-client-key").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name", "ConsumerKey", "IsActive"}).
			AddRow(legacyID, "Synthetic Client", "synthetic-client-key", true))
	mock.ExpectQuery("SELECT InstalledPackName").
		WithArgs(legacyID).
		WillReturnRows(sqlmock.NewRows([]string{"InstalledPackName", "IsDeleted"}))
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey, CallbackUrl, RedirectUris, Scopes, IsActive, ClientType, RequirePkce,").
		WithArgs(legacyID).
		WillReturnRows(connectedAppProjectionRows().AddRow(connectedAppProjectionRowValues("", map[string]driver.Value{
			"Id":                      legacyID,
			"Scopes":                  "external.records.read",
			"ClientType":              "public",
			"RequirePkce":             true,
			"TokenEndpointAuthMethod": "none",
			"AllowedGrantTypes":       "authorization_code",
			"ConsumerSecret":          nil,
			"ConsumerSecretHash":      nil,
		})...))
	mock.ExpectExec("UPDATE _System_ConnectedAppDeclaration").
		WithArgs(
			expected.Declaration.PayloadHash,
			0,
			expected.Declaration.ID,
			packName,
			"synthetic-client",
		).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectConnectedAppDeclarationExists(mock, expected.Declaration, false)
	mock.ExpectExec("INSERT INTO _System_ConnectedAppDeclaration").
		WithArgs(
			expected.Declaration.ID,
			packName,
			"synthetic-client",
			expected.Declaration.PayloadHash,
			0,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))
	mock.ExpectExec("UPDATE _System_ConnectedAppStage").
		WithArgs(
			expected.Declaration.PayloadHash,
			sqlmock.AnyArg(),
			0,
			expected.Declaration.ID,
			packName,
			"synthetic-client",
		).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectConnectedAppStageExists(mock, sourceBackedConnectedAppStageRow{
		ID:                expected.Declaration.ID,
		InstalledPackName: packName,
		ConnectedAppID:    "synthetic-client",
	}, false)
	mock.ExpectExec("INSERT INTO _System_ConnectedAppStage").
		WithArgs(
			expected.Declaration.ID,
			packName,
			"synthetic-client",
			expected.Declaration.PayloadHash,
			sqlmock.AnyArg(),
			0,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))
	mock.ExpectCommit()

	if err := component.Apply(context.Background(), ApplyDeps{DB: db}); err != nil {
		t.Fatalf("Apply ownerless public adoption: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedConnectedAppOwnershipRejectsDivergedOwnerlessPublicConsumerKeyAdoption(t *testing.T) {
	packName := "oauth-client-pack"
	legacyID := "legacy-public-bi-uuid"
	payload := publicConnectedAppComponentManifestPayload(t)
	component, err := newSourceBackedConnectedAppComponentWithPolicy(packName, ManifestComponent{
		Kind:          ComponentKindConnectedApp,
		BusinessKey:   "synthetic-client",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload, sourceBackedConnectedAppPublicScopePolicy{"external.records.read": {packName: {}}})
	if err != nil {
		t.Fatalf("newSourceBackedConnectedAppComponentWithPolicy: %v", err)
	}
	expectedAny, err := component.ExpectedProjection()
	if err != nil {
		t.Fatalf("ExpectedProjection: %v", err)
	}
	expected := expectedAny.(sourceBackedConnectedAppComponentProjection)

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

	mock.ExpectQuery("SELECT Id, Name, ConsumerKey").
		WithArgs("synthetic-client", "Synthetic Client", "synthetic-client-key").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name", "ConsumerKey", "IsActive"}).
			AddRow(legacyID, "Synthetic Client", "synthetic-client-key", true))
	mock.ExpectQuery("SELECT InstalledPackName").
		WithArgs(legacyID).
		WillReturnRows(sqlmock.NewRows([]string{"InstalledPackName", "IsDeleted"}))
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey, CallbackUrl, RedirectUris, Scopes, IsActive, ClientType, RequirePkce,").
		WithArgs(legacyID).
		WillReturnRows(connectedAppProjectionRows().AddRow(connectedAppProjectionRowValues("", map[string]driver.Value{
			"Id":                      legacyID,
			"CallbackUrl":             "https://evil.example/callback",
			"RedirectUris":            `["https://evil.example/callback"]`,
			"Scopes":                  "external.records.read",
			"ClientType":              "public",
			"RequirePkce":             true,
			"TokenEndpointAuthMethod": "none",
			"AllowedGrantTypes":       "authorization_code",
			"ConsumerSecret":          nil,
			"ConsumerSecretHash":      nil,
		})...))

	_, err = validateConnectedAppUniqueKeyClaim(context.Background(), db, packName, expected.App, nil)
	if err == nil {
		t.Fatal("validateConnectedAppUniqueKeyClaim accepted diverged ownerless public ConnectedApp")
	}
	if !strings.Contains(err.Error(), "not safe to adopt") || !strings.Contains(err.Error(), "CallbackURL") {
		t.Fatalf("error = %v, want unsafe adoption callback mismatch", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedConnectedAppPublishAdoptsOwnerlessPublicConsumerKeyByTombstoningLegacyRow(t *testing.T) {
	packName := "oauth-client-pack"
	legacyID := "legacy-public-bi-uuid"
	payload := publicConnectedAppComponentManifestPayload(t)
	_, _, expectedApp, err := sourceBackedConnectedAppComponentModel(packName, ManifestComponent{
		Kind:          ComponentKindConnectedApp,
		BusinessKey:   "synthetic-client",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload, sourceBackedConnectedAppPublicScopePolicy{"external.records.read": {packName: {}}})
	if err != nil {
		t.Fatalf("sourceBackedConnectedAppComponentModel: %v", err)
	}
	declaration, err := sourceBackedConnectedAppDeclarationForProjection(packName, expectedApp.ID, expectedApp)
	if err != nil {
		t.Fatalf("sourceBackedConnectedAppDeclarationForProjection: %v", err)
	}
	stage := sourceBackedConnectedAppStageRow{
		ID:                declaration.ID,
		InstalledPackName: packName,
		ConnectedAppID:    "synthetic-client",
		PayloadHash:       declaration.PayloadHash,
		RecordJSON:        sourceBackedPublicConnectedAppStageRecordJSONForPayload(t, packName, "synthetic-client", payload),
		IsDeleted:         0,
	}
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })

	mock.ExpectBegin()
	mock.ExpectQuery("SELECT Id, InstalledPackName, ConnectedAppId, PayloadHash, RecordJson").
		WithArgs(packName).
		WillReturnRows(sqlmock.NewRows([]string{
			"Id",
			"InstalledPackName",
			"ConnectedAppId",
			"PayloadHash",
			"RecordJson",
			"IsDeleted",
		}).AddRow(stage.ID, stage.InstalledPackName, stage.ConnectedAppID, stage.PayloadHash, stage.RecordJSON, stage.IsDeleted))
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey").
		WithArgs("synthetic-client", "Synthetic Client", "synthetic-client-key").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name", "ConsumerKey", "IsActive"}).
			AddRow(legacyID, "Synthetic Client", "synthetic-client-key", true))
	mock.ExpectQuery("SELECT InstalledPackName").
		WithArgs(legacyID).
		WillReturnRows(sqlmock.NewRows([]string{"InstalledPackName", "IsDeleted"}))
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey, CallbackUrl, RedirectUris, Scopes, IsActive, ClientType, RequirePkce,").
		WithArgs(legacyID).
		WillReturnRows(connectedAppProjectionRows().AddRow(connectedAppProjectionRowValues("", map[string]driver.Value{
			"Id":                      legacyID,
			"Scopes":                  "external.records.read",
			"ClientType":              "public",
			"RequirePkce":             true,
			"TokenEndpointAuthMethod": "none",
			"AllowedGrantTypes":       "authorization_code",
			"ConsumerSecret":          nil,
			"ConsumerSecretHash":      nil,
		})...))
	mock.ExpectExec("UPDATE _System_ConnectedApp").
		WithArgs(
			sourceBackedConnectedAppTombstoneValue("deleted connected_app", legacyID),
			sourceBackedConnectedAppTombstoneValue("deleted_consumer_key", legacyID),
			legacyID,
		).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec("UPDATE `_System_Session` SET").
		WithArgs(legacyID).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectQuery("SELECT Id FROM _System_ConnectedApp").
		WithArgs("synthetic-client").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectExec("INSERT INTO _System_ConnectedApp").
		WithArgs(
			"synthetic-client",
			"Synthetic Client",
			"synthetic-client-key",
			nil,
			nil,
			"https://client.example/callback",
			`["https://client.example/callback"]`,
			"external.records.read",
			true,
			"public",
			true,
			"none",
			"authorization_code",
			"trusted",
			1,
			false,
			nil,
			nil,
			nil,
			"AllUsers",
			nil,
			nil,
			nil,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))
	mock.ExpectExec("UPDATE `_System_Session` SET").
		WithArgs("synthetic-client").
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("DELETE FROM _System_ConnectedAppStage").
		WithArgs(packName).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectCommit()

	changed, err := func() ([]string, error) {
		tx, err := db.BeginTx(context.Background(), nil)
		if err != nil {
			return nil, err
		}
		committed := false
		defer func() {
			if !committed {
				_ = tx.Rollback()
			}
		}()
		changed, err := PublishSourceBackedConnectedAppsForPackInTx(context.Background(), tx, packName)
		if err != nil {
			return nil, err
		}
		if err := tx.Commit(); err != nil {
			return nil, err
		}
		committed = true
		return changed, nil
	}()
	if err != nil {
		t.Fatalf("PublishSourceBackedConnectedAppsForPackInTx: %v", err)
	}
	if got, want := strings.Join(changed, ","), "legacy-public-bi-uuid,synthetic-client"; got != want {
		t.Fatalf("changed app IDs = %q, want %q", got, want)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedConnectedAppComponentApplyClearsStageWhenLiveAlreadyConverged(t *testing.T) {
	payload := []byte(connectedAppComponentManifestPayload())
	component, err := newSourceBackedConnectedAppComponent("oauth-client-pack", ManifestComponent{
		Kind:          ComponentKindConnectedApp,
		BusinessKey:   "synthetic-client",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload)
	if err != nil {
		t.Fatalf("newSourceBackedConnectedAppComponent: %v", err)
	}
	expectedAny, err := component.ExpectedProjection()
	if err != nil {
		t.Fatalf("ExpectedProjection: %v", err)
	}
	expected := expectedAny.(sourceBackedConnectedAppComponentProjection)
	secretHash, err := pkgauth.HashPassword("synthetic-secret")
	if err != nil {
		t.Fatalf("HashPassword: %v", err)
	}
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })

	mock.ExpectBegin()
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey, CallbackUrl, RedirectUris, Scopes, IsActive, ClientType, RequirePkce,").
		WithArgs("synthetic-client").
		WillReturnRows(connectedAppProjectionRows().AddRow(connectedAppProjectionRowValues(secretHash, nil)...))
	mock.ExpectQuery("SELECT Id, InstalledPackName").
		WithArgs("synthetic-client").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "InstalledPackName", "IsDeleted"}).
			AddRow(expected.Declaration.ID, "oauth-client-pack", 0))
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey").
		WithArgs("synthetic-client", "Synthetic Client", "synthetic-client-key").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name", "ConsumerKey", "IsActive"}).
			AddRow("synthetic-client", "Synthetic Client", "synthetic-client-key", true))
	mock.ExpectExec("UPDATE _System_ConnectedAppDeclaration").
		WithArgs(
			expected.Declaration.PayloadHash,
			0,
			expected.Declaration.ID,
			"oauth-client-pack",
			"synthetic-client",
		).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectConnectedAppDeclarationExists(mock, expected.Declaration, true)
	mock.ExpectExec("DELETE FROM _System_ConnectedAppStage").
		WithArgs(expected.Declaration.ID, "oauth-client-pack", "synthetic-client").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectCommit()

	if err := component.Apply(context.Background(), ApplyDeps{DB: db}); err != nil {
		t.Fatalf("Apply: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedConnectedAppComponentApplyRejectsOwnerlessExistingConnectedApp(t *testing.T) {
	payload := []byte(connectedAppComponentManifestPayload())
	component, err := newSourceBackedConnectedAppComponent("oauth-client-pack", ManifestComponent{
		Kind:          ComponentKindConnectedApp,
		BusinessKey:   "synthetic-client",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload)
	if err != nil {
		t.Fatalf("newSourceBackedConnectedAppComponent: %v", err)
	}

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

	mock.ExpectBegin()
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey, CallbackUrl, RedirectUris, Scopes, IsActive, ClientType, RequirePkce,").
		WithArgs("synthetic-client").
		WillReturnRows(connectedAppProjectionRows().AddRow(connectedAppProjectionRowValues("", nil)...))
	mock.ExpectQuery("SELECT Id, InstalledPackName").
		WithArgs("synthetic-client").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "InstalledPackName", "IsDeleted"}))
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey").
		WithArgs("synthetic-client", "Synthetic Client", "synthetic-client-key").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name", "ConsumerKey", "IsActive"}).
			AddRow("synthetic-client", "Synthetic Client", "synthetic-client-key", true))
	mock.ExpectRollback()

	err = component.Apply(context.Background(), ApplyDeps{DB: db})
	if err == nil {
		t.Fatal("Apply accepted ownerless existing ConnectedApp row")
	}
	if !strings.Contains(err.Error(), "collides with existing _System_ConnectedApp row without source-backed declaration") {
		t.Fatalf("Apply error = %v, want ownerless ConnectedApp collision", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedConnectedAppOwnershipAllowsSamePackInactiveTombstone(t *testing.T) {
	expected := sourceBackedConnectedAppExpectedProjection(t)

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

	mock.ExpectQuery("SELECT Id, InstalledPackName").
		WithArgs("synthetic-client").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "InstalledPackName", "IsDeleted"}).
			AddRow(expected.Declaration.ID, "oauth-client-pack", 1))
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey").
		WithArgs("synthetic-client", "Synthetic Client", "synthetic-client-key").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name", "ConsumerKey", "IsActive"}).
			AddRow("synthetic-client", "Synthetic Client", "synthetic-client-key", false))

	if err := validateSourceBackedConnectedAppOwnershipClaim(context.Background(), db, expected.Declaration, expected.App, true, nil); err != nil {
		t.Fatalf("validateSourceBackedConnectedAppOwnershipClaim same-pack inactive tombstone: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedConnectedAppOwnershipAllowsSamePackRenameReclaim(t *testing.T) {
	expected := sourceBackedConnectedAppExpectedProjection(t)
	expected.App.ID = "new-client"
	expected.App.Name = "Shared Client"
	expected.App.ConsumerKey = "shared-key"

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

	mock.ExpectQuery("SELECT Id, Name, ConsumerKey").
		WithArgs("new-client", "Shared Client", "shared-key").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name", "ConsumerKey", "IsActive"}).
			AddRow("old-client", "Shared Client", "shared-key", true))
	mock.ExpectQuery("SELECT InstalledPackName").
		WithArgs("old-client").
		WillReturnRows(sqlmock.NewRows([]string{"InstalledPackName", "IsDeleted"}).
			AddRow("oauth-client-pack", 0))

	declaredIDs := map[string]struct{}{connectedAppDeclarationKey("new-client"): {}}
	if _, err := validateConnectedAppUniqueKeyClaim(context.Background(), db, "oauth-client-pack", expected.App, declaredIDs); err != nil {
		t.Fatalf("validateConnectedAppUniqueKeyClaim same-pack rename reclaim: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedConnectedAppOwnershipRejectsSamePackActiveTombstone(t *testing.T) {
	expected := sourceBackedConnectedAppExpectedProjection(t)

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

	mock.ExpectQuery("SELECT Id, InstalledPackName").
		WithArgs("synthetic-client").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "InstalledPackName", "IsDeleted"}).
			AddRow(expected.Declaration.ID, "oauth-client-pack", 1))
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey").
		WithArgs("synthetic-client", "Synthetic Client", "synthetic-client-key").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name", "ConsumerKey", "IsActive"}).
			AddRow("synthetic-client", "Synthetic Client", "synthetic-client-key", true))

	err = validateSourceBackedConnectedAppOwnershipClaim(context.Background(), db, expected.Declaration, expected.App, true, nil)
	if err == nil {
		t.Fatal("validateSourceBackedConnectedAppOwnershipClaim accepted active same-pack tombstone")
	}
	if !strings.Contains(err.Error(), "soft-deleted declaration") || !strings.Contains(err.Error(), "row is active") {
		t.Fatalf("error = %v, want active tombstone rejection", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedConnectedAppComponentApplyRejectsNameCollisionBeforeWrite(t *testing.T) {
	payload := []byte(connectedAppComponentManifestPayload())
	component, err := newSourceBackedConnectedAppComponent("oauth-client-pack", ManifestComponent{
		Kind:          ComponentKindConnectedApp,
		BusinessKey:   "synthetic-client",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload)
	if err != nil {
		t.Fatalf("newSourceBackedConnectedAppComponent: %v", err)
	}

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

	mock.ExpectBegin()
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey, CallbackUrl, RedirectUris, Scopes, IsActive, ClientType, RequirePkce,").
		WithArgs("synthetic-client").
		WillReturnRows(connectedAppProjectionRows())
	mock.ExpectQuery("SELECT Id, InstalledPackName").
		WithArgs("synthetic-client").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "InstalledPackName", "IsDeleted"}))
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey").
		WithArgs("synthetic-client", "Synthetic Client", "synthetic-client-key").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "Name", "ConsumerKey", "IsActive"}).
			AddRow("other-client", "Synthetic Client", "other-key", true))
	mock.ExpectQuery("SELECT InstalledPackName").
		WithArgs("other-client").
		WillReturnRows(sqlmock.NewRows([]string{"InstalledPackName", "IsDeleted"}))
	mock.ExpectRollback()

	err = component.Apply(context.Background(), ApplyDeps{DB: db})
	if err == nil {
		t.Fatal("Apply accepted ConnectedApp Name collision")
	}
	if !strings.Contains(err.Error(), `Name "Synthetic Client" collides`) {
		t.Fatalf("Apply error = %v, want Name collision", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedConnectedAppPostApplyUsesSessionRepository(t *testing.T) {
	repo := &connectedAppSessionRepositoryRecorder{}
	hook := &connectedAppInvalidationHookRecorder{}

	if err := postApplyConnectedAppSecurityChange(context.Background(), ApplyDeps{
		SessionRepo:      repo,
		ConnectedAppHook: hook,
	}, "synthetic-client"); err != nil {
		t.Fatalf("postApplyConnectedAppSecurityChange: %v", err)
	}
	if got, want := strings.Join(repo.appIDs, ","), "synthetic-client"; got != want {
		t.Fatalf("SessionRepo appIDs = %q, want %q", got, want)
	}
	if got, want := strings.Join(hook.appIDs, ","), "synthetic-client"; got != want {
		t.Fatalf("ConnectedAppHook appIDs = %q, want %q", got, want)
	}
}

func TestSourceBackedConnectedAppComponentVerifyDetectsOwnedFieldDrift(t *testing.T) {
	payload := []byte(connectedAppComponentManifestPayload())
	component, err := newSourceBackedConnectedAppComponent("oauth-client-pack", ManifestComponent{
		Kind:          ComponentKindConnectedApp,
		BusinessKey:   "synthetic-client",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload)
	if err != nil {
		t.Fatalf("newSourceBackedConnectedAppComponent: %v", err)
	}

	secretHash, err := pkgauth.HashPassword("synthetic-secret")
	if err != nil {
		t.Fatalf("HashPassword: %v", err)
	}

	verifier := component.(componentCustomVerifier)
	cases := []struct {
		name      string
		overrides map[string]driver.Value
	}{
		{name: "CallbackUrl", overrides: map[string]driver.Value{"CallbackUrl": "https://drift.example/callback"}},
		{name: "RedirectUris", overrides: map[string]driver.Value{"RedirectUris": `["https://drift.example/callback"]`}},
		{name: "Scopes", overrides: map[string]driver.Value{"Scopes": "api chatter_api"}},
		{name: "AllowedGrantTypes", overrides: map[string]driver.Value{"AllowedGrantTypes": "authorization_code"}},
		{name: "PermittedUsers", overrides: map[string]driver.Value{"PermittedUsers": "PowerUsers"}},
		{name: "TokenEndpointAuthMethod", overrides: map[string]driver.Value{"TokenEndpointAuthMethod": "none"}},
		{name: "ConsentPolicy", overrides: map[string]driver.Value{"ConsentPolicy": "prompt"}},
		{name: "ConsentVersion", overrides: map[string]driver.Value{"ConsentVersion": int64(2)}},
		{name: "RequirePkce", overrides: map[string]driver.Value{"RequirePkce": true}},
		{name: "IsActive", overrides: map[string]driver.Value{"IsActive": false}},
		{name: "SessionIssuerClass", overrides: map[string]driver.Value{"SessionIssuerClass": "platform_browser"}},
		{name: "PublisherName", overrides: map[string]driver.Value{"PublisherName": "Drift Publisher"}},
		{name: "SupportUrl", overrides: map[string]driver.Value{"SupportUrl": "https://support.example"}},
		{name: "DailyApiLimit", overrides: map[string]driver.Value{"DailyApiLimit": int64(100)}},
		{name: "HourlyApiLimit", overrides: map[string]driver.Value{"HourlyApiLimit": int64(10)}},
		{name: "IssueRefreshTokens", overrides: map[string]driver.Value{"IssueRefreshTokens": true}},
	}
	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			db, mock, err := sqlmock.New()
			if err != nil {
				t.Fatalf("sqlmock.New: %v", err)
			}
			t.Cleanup(func() { _ = db.Close() })

			expectConnectedAppPackStatus(mock, "oauth-client-pack", "active")
			mock.ExpectQuery("SELECT Id, Name, ConsumerKey, CallbackUrl, RedirectUris, Scopes, IsActive, ClientType, RequirePkce,").
				WithArgs("synthetic-client").
				WillReturnRows(connectedAppProjectionRows().AddRow(connectedAppProjectionRowValues(secretHash, tc.overrides)...))
			expectedAny, err := component.ExpectedProjection()
			if err != nil {
				t.Fatalf("ExpectedProjection: %v", err)
			}
			expectConnectedAppDeclarationQuery(mock, expectedAny.(sourceBackedConnectedAppComponentProjection).Declaration)

			err = verifier.VerifyComponent(context.Background(), VerifyDeps{DB: db})
			if err == nil {
				t.Fatalf("VerifyComponent accepted drifted %s", tc.name)
			}
			if !strings.Contains(err.Error(), "projection drift") {
				t.Fatalf("VerifyComponent error = %v, want projection drift", err)
			}
			if err := mock.ExpectationsWereMet(); err != nil {
				t.Fatalf("sqlmock expectations: %v", err)
			}
		})
	}
}

func TestSourceBackedConnectedAppComponentVerifyChecksSecretHash(t *testing.T) {
	payload := []byte(connectedAppComponentManifestPayload())
	component, err := newSourceBackedConnectedAppComponent("oauth-client-pack", ManifestComponent{
		Kind:          ComponentKindConnectedApp,
		BusinessKey:   "synthetic-client",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload)
	if err != nil {
		t.Fatalf("newSourceBackedConnectedAppComponent: %v", err)
	}
	wrongSecretHash, err := pkgauth.HashPassword("wrong-secret")
	if err != nil {
		t.Fatalf("HashPassword: %v", err)
	}
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })
	expectConnectedAppPackStatus(mock, "oauth-client-pack", "active")
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey, CallbackUrl, RedirectUris, Scopes, IsActive, ClientType, RequirePkce,").
		WithArgs("synthetic-client").
		WillReturnRows(connectedAppProjectionRows().AddRow(connectedAppProjectionRowValues(wrongSecretHash, nil)...))
	expectedAny, err := component.ExpectedProjection()
	if err != nil {
		t.Fatalf("ExpectedProjection: %v", err)
	}
	expectConnectedAppDeclarationQuery(mock, expectedAny.(sourceBackedConnectedAppComponentProjection).Declaration)

	verifier := component.(componentCustomVerifier)
	err = verifier.VerifyComponent(context.Background(), VerifyDeps{DB: db})
	if err == nil {
		t.Fatal("VerifyComponent accepted mismatched ConsumerSecretHash")
	}
	if !strings.Contains(err.Error(), "projection drift") {
		t.Fatalf("VerifyComponent error = %v, want projection drift", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedConnectedAppComponentVerifyRejectsPlaintextConsumerSecret(t *testing.T) {
	payload := []byte(connectedAppComponentManifestPayload())
	component, err := newSourceBackedConnectedAppComponent("oauth-client-pack", ManifestComponent{
		Kind:          ComponentKindConnectedApp,
		BusinessKey:   "synthetic-client",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload)
	if err != nil {
		t.Fatalf("newSourceBackedConnectedAppComponent: %v", err)
	}
	secretHash, err := pkgauth.HashPassword("synthetic-secret")
	if err != nil {
		t.Fatalf("HashPassword: %v", err)
	}
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })
	expectConnectedAppPackStatus(mock, "oauth-client-pack", "active")
	mock.ExpectQuery("SELECT Id, Name, ConsumerKey, CallbackUrl, RedirectUris, Scopes, IsActive, ClientType, RequirePkce,").
		WithArgs("synthetic-client").
		WillReturnRows(connectedAppProjectionRows().AddRow(connectedAppProjectionRowValues(secretHash, map[string]driver.Value{
			"ConsumerSecret": "synthetic-secret",
		})...))
	expectedAny, err := component.ExpectedProjection()
	if err != nil {
		t.Fatalf("ExpectedProjection: %v", err)
	}
	expectConnectedAppDeclarationQuery(mock, expectedAny.(sourceBackedConnectedAppComponentProjection).Declaration)

	verifier := component.(componentCustomVerifier)
	err = verifier.VerifyComponent(context.Background(), VerifyDeps{DB: db})
	if err == nil {
		t.Fatal("VerifyComponent accepted plaintext ConsumerSecret fallback")
	}
	if !strings.Contains(err.Error(), "projection drift") {
		t.Fatalf("VerifyComponent error = %v, want projection drift", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sqlmock expectations: %v", err)
	}
}

func TestSourceBackedConnectedAppDeclarationUpsertTreatsNoopUpdateAsExisting(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })
	row := sourceBackedConnectedAppDeclarationRow{
		ID:                sourceBackedConnectedAppDeclarationID("oauth-client-pack", "synthetic-client"),
		InstalledPackName: "oauth-client-pack",
		ConnectedAppID:    "synthetic-client",
		PayloadHash:       "payload-hash",
		IsDeleted:         0,
	}
	mock.ExpectExec("UPDATE _System_ConnectedAppDeclaration").
		WithArgs(row.PayloadHash, row.IsDeleted, row.ID, row.InstalledPackName, row.ConnectedAppID).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectConnectedAppDeclarationExists(mock, row, true)

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

func connectedAppProjectionRows() *sqlmock.Rows {
	return sqlmock.NewRows(connectedAppProjectionColumns())
}

func connectedAppProjectionColumns() []string {
	return []string{
		"Id",
		"Name",
		"ConsumerKey",
		"CallbackUrl",
		"RedirectUris",
		"Scopes",
		"IsActive",
		"ClientType",
		"RequirePkce",
		"TokenEndpointAuthMethod",
		"AllowedGrantTypes",
		"ConsentPolicy",
		"ConsentVersion",
		"IssueRefreshTokens",
		"PublisherName",
		"SupportUrl",
		"RunAsUserId",
		"PermittedUsers",
		"SessionIssuerClass",
		"DailyApiLimit",
		"HourlyApiLimit",
		"ConsumerSecret",
		"ConsumerSecretHash",
	}
}

func connectedAppProjectionRowValues(secretHash string, overrides map[string]driver.Value) []driver.Value {
	values := map[string]driver.Value{
		"Id":                      "synthetic-client",
		"Name":                    "Synthetic Client",
		"ConsumerKey":             "synthetic-client-key",
		"CallbackUrl":             "https://client.example/callback",
		"RedirectUris":            `["https://client.example/callback"]`,
		"Scopes":                  "api",
		"IsActive":                true,
		"ClientType":              "confidential",
		"RequirePkce":             false,
		"TokenEndpointAuthMethod": "client_secret_post",
		"AllowedGrantTypes":       "authorization_code password",
		"ConsentPolicy":           "trusted",
		"ConsentVersion":          int64(1),
		"IssueRefreshTokens":      false,
		"PublisherName":           nil,
		"SupportUrl":              nil,
		"RunAsUserId":             nil,
		"PermittedUsers":          "AllUsers",
		"SessionIssuerClass":      nil,
		"DailyApiLimit":           nil,
		"HourlyApiLimit":          nil,
		"ConsumerSecret":          nil,
		"ConsumerSecretHash":      secretHash,
	}
	for key, value := range overrides {
		values[key] = value
	}
	out := make([]driver.Value, 0, len(values))
	for _, column := range connectedAppProjectionColumns() {
		out = append(out, values[column])
	}
	return out
}

func sourceBackedConnectedAppExpectedProjection(t *testing.T) sourceBackedConnectedAppComponentProjection {
	t.Helper()
	payload := []byte(connectedAppComponentManifestPayload())
	component, err := newSourceBackedConnectedAppComponent("oauth-client-pack", ManifestComponent{
		Kind:          ComponentKindConnectedApp,
		BusinessKey:   "synthetic-client",
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload)
	if err != nil {
		t.Fatalf("newSourceBackedConnectedAppComponent: %v", err)
	}
	expectedAny, err := component.ExpectedProjection()
	if err != nil {
		t.Fatalf("ExpectedProjection: %v", err)
	}
	return expectedAny.(sourceBackedConnectedAppComponentProjection)
}

type connectedAppInvalidationHookRecorder struct {
	appIDs []string
}

func (h *connectedAppInvalidationHookRecorder) OnConnectedAppChanged(appID string) {
	h.appIDs = append(h.appIDs, appID)
}

type connectedAppSessionRepositoryRecorder struct {
	appIDs []string
}

func (r *connectedAppSessionRepositoryRecorder) InsertSession(context.Context, *models.SystemSession) error {
	return nil
}

func (r *connectedAppSessionRepositoryRecorder) GetSession(context.Context, string) (*models.SystemSession, error) {
	return nil, nil
}

func (r *connectedAppSessionRepositoryRecorder) AdvanceSessionAuthEpoch(context.Context, string, uint64) (uint64, error) {
	return 0, nil
}

func (r *connectedAppSessionRepositoryRecorder) RevokeSession(context.Context, string, string) (bool, error) {
	return false, nil
}

func (r *connectedAppSessionRepositoryRecorder) RevokeAllSessionsForUser(context.Context, string) error {
	return nil
}

func (r *connectedAppSessionRepositoryRecorder) RevokeAllSessionsByProfileID(context.Context, string) (int64, error) {
	return 0, nil
}

func (r *connectedAppSessionRepositoryRecorder) RevokeAllSessionsByUserRoleID(context.Context, string) (int64, error) {
	return 0, nil
}

func (r *connectedAppSessionRepositoryRecorder) RevokeAllSessionsForConnectedApp(_ context.Context, appID string) (int64, error) {
	r.appIDs = append(r.appIDs, appID)
	return 1, nil
}

func (r *connectedAppSessionRepositoryRecorder) RevokeSessionsForUserAndConnectedApp(context.Context, string, string) (int64, error) {
	return 0, nil
}

func (r *connectedAppSessionRepositoryRecorder) RenewSession(context.Context, string, string, string, time.Time, time.Time) (bool, error) {
	return false, nil
}

func (r *connectedAppSessionRepositoryRecorder) UpdateLastActivity(context.Context, string) error {
	return nil
}

func (r *connectedAppSessionRepositoryRecorder) CleanupExpiredSessions(context.Context, time.Duration) (int64, error) {
	return 0, nil
}

func (r *connectedAppSessionRepositoryRecorder) IsConnectedAppActive(context.Context, string) (bool, error) {
	return false, nil
}

type bcryptHashArg struct {
	secret string
}

func (a bcryptHashArg) Match(value driver.Value) bool {
	hash, ok := value.(string)
	return ok && hash != "" && hash != a.secret && pkgauth.VerifyPassword(a.secret, hash)
}

func expectConnectedAppDeclarationQuery(mock sqlmock.Sqlmock, row sourceBackedConnectedAppDeclarationRow) {
	mock.ExpectQuery("SELECT Id, InstalledPackName, ConnectedAppId, PayloadHash").
		WithArgs(row.InstalledPackName, row.ConnectedAppID).
		WillReturnRows(sqlmock.NewRows([]string{
			"Id",
			"InstalledPackName",
			"ConnectedAppId",
			"PayloadHash",
			"IsDeleted",
		}).AddRow(
			row.ID,
			row.InstalledPackName,
			row.ConnectedAppID,
			row.PayloadHash,
			row.IsDeleted,
		))
}

func expectConnectedAppDeclarationExists(mock sqlmock.Sqlmock, row sourceBackedConnectedAppDeclarationRow, exists bool) {
	rows := sqlmock.NewRows([]string{"1"})
	if exists {
		rows.AddRow(1)
	}
	mock.ExpectQuery("SELECT 1").
		WithArgs(row.ID, row.InstalledPackName, row.ConnectedAppID).
		WillReturnRows(rows)
}

func expectConnectedAppPackStatus(mock sqlmock.Sqlmock, packName, status string) {
	rows := sqlmock.NewRows([]string{"Status"})
	if status != "" {
		rows.AddRow(status)
	}
	mock.ExpectQuery("SELECT Status").
		WithArgs(packName).
		WillReturnRows(rows)
}

func expectConnectedAppStageQuery(mock sqlmock.Sqlmock, row sourceBackedConnectedAppStageRow) {
	mock.ExpectQuery("SELECT Id, InstalledPackName, ConnectedAppId, PayloadHash, RecordJson").
		WithArgs(row.InstalledPackName, row.ConnectedAppID).
		WillReturnRows(sqlmock.NewRows([]string{
			"Id",
			"InstalledPackName",
			"ConnectedAppId",
			"PayloadHash",
			"RecordJson",
			"IsDeleted",
		}).AddRow(
			row.ID,
			row.InstalledPackName,
			row.ConnectedAppID,
			row.PayloadHash,
			row.RecordJSON,
			row.IsDeleted,
		))
}

func expectConnectedAppStageExists(mock sqlmock.Sqlmock, row sourceBackedConnectedAppStageRow, exists bool) {
	rows := sqlmock.NewRows([]string{"1"})
	if exists {
		rows.AddRow(1)
	}
	mock.ExpectQuery("SELECT 1").
		WithArgs(row.ID, row.InstalledPackName, row.ConnectedAppID).
		WillReturnRows(rows)
}

func sourceBackedConnectedAppStageRecordJSON(t *testing.T) string {
	t.Helper()
	return sourceBackedConnectedAppStageRecordJSONFor(t, "synthetic-client", "Synthetic Client", "synthetic-client-key")
}

func sourceBackedConnectedAppStageRecordJSONFor(t *testing.T, id, name, consumerKey string) string {
	t.Helper()
	payload := connectedAppComponentManifestPayloadWithOverrides(t, map[string]any{
		"id":          id,
		"name":        name,
		"consumerKey": consumerKey,
	})
	return sourceBackedConnectedAppStageRecordJSONForPayload(t, "oauth-client-pack", id, payload)
}

func sourceBackedConnectedAppStageRecordJSONForPayload(t *testing.T, packName, id string, payload []byte) string {
	t.Helper()
	_, record, _, err := sourceBackedConnectedAppComponentModel(packName, ManifestComponent{
		Kind:          ComponentKindConnectedApp,
		BusinessKey:   id,
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload, nil)
	if err != nil {
		t.Fatalf("sourceBackedConnectedAppComponentModel: %v", err)
	}
	encoded, err := json.Marshal(record)
	if err != nil {
		t.Fatalf("json.Marshal staged ConnectedApp record: %v", err)
	}
	return string(encoded)
}

func sourceBackedPublicConnectedAppStageRecordJSONForPayload(t *testing.T, packName, id string, payload []byte) string {
	t.Helper()
	_, record, _, err := sourceBackedConnectedAppComponentModel(packName, ManifestComponent{
		Kind:          ComponentKindConnectedApp,
		BusinessKey:   id,
		Manageability: ManageabilityLocked,
		Payload:       payload,
	}, payload, sourceBackedConnectedAppPublicScopePolicy{"external.records.read": {packName: {}}})
	if err != nil {
		t.Fatalf("sourceBackedConnectedAppComponentModel: %v", err)
	}
	encoded, err := json.Marshal(record)
	if err != nil {
		t.Fatalf("json.Marshal staged public ConnectedApp record: %v", err)
	}
	return string(encoded)
}

func sourceBackedConnectedAppStageRecordJSONWithPlaintextSecret(t *testing.T, id, name, consumerKey, secret string) string {
	t.Helper()
	record := models.SObject{
		constants.ColSysConnectedApp_ID:                      id,
		constants.ColSysConnectedApp_Name:                    name,
		constants.ColSysConnectedApp_ConsumerKey:             consumerKey,
		constants.ColSysConnectedApp_ConsumerSecret:          secret,
		constants.ColSysConnectedApp_ConsumerSecretHash:      "",
		constants.ColSysConnectedApp_CallbackURL:             "https://client.example/callback",
		constants.ColSysConnectedApp_RedirectURIs:            `["https://client.example/callback"]`,
		constants.ColSysConnectedApp_Scopes:                  "api",
		constants.ColSysConnectedApp_IsActive:                true,
		constants.ColSysConnectedApp_ClientType:              "confidential",
		constants.ColSysConnectedApp_RequirePkce:             false,
		constants.ColSysConnectedApp_TokenEndpointAuthMethod: "client_secret_post",
		constants.ColSysConnectedApp_AllowedGrantTypes:       "authorization_code password",
		constants.ColSysConnectedApp_ConsentPolicy:           "trusted",
		constants.ColSysConnectedApp_ConsentVersion:          1,
		constants.ColSysConnectedApp_IssueRefreshTokens:      false,
		constants.ColSysConnectedApp_PermittedUsers:          "AllUsers",
	}
	encoded, err := json.Marshal(record)
	if err != nil {
		t.Fatalf("json.Marshal hand-written staged ConnectedApp record: %v", err)
	}
	return string(encoded)
}

func connectedAppComponentManifestPayloadWithOverrides(t *testing.T, overrides map[string]any) []byte {
	t.Helper()
	var raw map[string]any
	if err := json.Unmarshal([]byte(connectedAppComponentManifestPayload()), &raw); err != nil {
		t.Fatalf("json.Unmarshal connected_app payload: %v", err)
	}
	for key, value := range overrides {
		raw[key] = value
	}
	payload, err := json.Marshal(raw)
	if err != nil {
		t.Fatalf("json.Marshal connected_app payload: %v", err)
	}
	return payload
}

func publicConnectedAppComponentManifestPayload(t *testing.T) []byte {
	t.Helper()
	return connectedAppComponentManifestPayloadWithOverrides(t, map[string]any{
		"consumerSecret":          "",
		"clientType":              "public",
		"requirePkce":             true,
		"tokenEndpointAuthMethod": "none",
		"allowedGrantTypes":       []string{"authorization_code"},
		"scopes":                  []string{"external.records.read"},
	})
}