package controlplane

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"log"
	"sort"
	"strings"

	bootstate "github.com/nexuscrm/db/pkg/bootstate"
)

const migrationIDRetireRevertedSCIMSchema = "20260722_retire_reverted_scim_schema"

type revertedSCIMTableFingerprint struct {
	name             string
	objectID         string
	label            string
	description      string
	fieldNames       []string
	referenceTargets map[string]string
	requiredFields   map[string]struct{}
	uniqueFields     map[string]struct{}
	datetimeFields   map[string]struct{}
	numberFields     map[string]struct{}
	booleanFields    map[string]struct{}
}

// revertedSCIMSchemaFingerprints freezes the exact system-table metadata that
// NexusCRM #3707 introduced and #3882 later removed. These are migration input,
// not current managed definitions: putting them back in the live schema catalog
// would recreate the reverted L0 tables on every greenfield install.
var revertedSCIMSchemaFingerprints = []revertedSCIMTableFingerprint{
	{
		name:        "_System_ScimProvisioningLink",
		objectID:    "obj__system_scimprovisioninglink_cb5e13511744138241ada5049e3e8651",
		label:       "SCIM Provisioning Link",
		description: "Durable correlation between an external IdP identity (SCIM externalId) and a NexusCRM User",
		fieldNames: []string{
			"CreatedDate", "ExternalId", "Id", "IdpUserName", "LastModifiedDate", "LastSyncDate", "Source", "UserId",
		},
		referenceTargets: map[string]string{"UserId": "User"},
		requiredFields:   revertedSCIMStringSet("UserId"),
		uniqueFields:     revertedSCIMStringSet("UserId"),
		datetimeFields:   revertedSCIMStringSet("CreatedDate", "LastModifiedDate", "LastSyncDate"),
	},
	{
		name:        "_System_ScimQuarantine",
		objectID:    "obj__system_scimquarantine_11488e2e19d1d11dbb6fc2ecc74b1e6f",
		label:       "SCIM Quarantine",
		description: "Inbound SCIM provisioning request held for admin review (conflict, rehire collision, ambiguous match)",
		fieldNames: []string{
			"ConflictUserId", "CreatedDate", "Email", "ExternalId", "Id", "LastModifiedDate", "RawResource", "Reason", "ResolutionNote", "ResolvedById", "ResolvedDate", "Status", "UserName",
		},
		referenceTargets: map[string]string{"ConflictUserId": "User", "ResolvedById": "User"},
		requiredFields:   revertedSCIMStringSet("Reason", "Status"),
		datetimeFields:   revertedSCIMStringSet("CreatedDate", "LastModifiedDate", "ResolvedDate"),
	},
	{
		name:        "_System_ScimPendingManager",
		objectID:    "obj__system_scimpendingmanager_113ce49d53a9e37b0a5ddd47b8137976",
		label:       "SCIM Pending Manager",
		description: "Unresolved manager reference queued for the SCIM reconcile pass to drain",
		fieldNames: []string{
			"Attempts", "CreatedDate", "Id", "LastAttemptDate", "LastModifiedDate", "ManagerExternalId", "UserId",
		},
		referenceTargets: map[string]string{"UserId": "User"},
		requiredFields:   revertedSCIMStringSet("Attempts", "ManagerExternalId", "UserId"),
		datetimeFields:   revertedSCIMStringSet("CreatedDate", "LastAttemptDate", "LastModifiedDate"),
		numberFields:     revertedSCIMStringSet("Attempts"),
	},
	{
		name:        "_System_ScimJournal",
		objectID:    "obj__system_scimjournal_2c7697a7342c0bc74ec9fc1e177f1b98",
		label:       "SCIM Journal",
		description: "Journaled SCIM provisioning outcomes for the admin dashboard and audit reconstruction",
		fieldNames: []string{
			"ActorId", "CreatedDate", "Detail", "Email", "EventType", "ExternalId", "Id", "LastModifiedDate", "RunId", "UserId",
		},
		referenceTargets: map[string]string{"ActorId": "User", "UserId": "User"},
		requiredFields:   revertedSCIMStringSet("EventType"),
		datetimeFields:   revertedSCIMStringSet("CreatedDate", "LastModifiedDate"),
	},
	{
		name:        "_System_ScimBreaker",
		objectID:    "obj__system_scimbreaker_548662ae8066008409c38699d2b8ae84",
		label:       "SCIM Breaker",
		description: "Single-row persisted deactivation-rate circuit-breaker window for SCIM provisioning",
		fieldNames: []string{
			"CreatedDate", "DeactivateCount", "Id", "LastModifiedDate", "Tripped", "TrippedAt", "TrippedReason", "WindowStart",
		},
		referenceTargets: map[string]string{},
		requiredFields:   revertedSCIMStringSet("DeactivateCount", "Tripped"),
		datetimeFields:   revertedSCIMStringSet("CreatedDate", "LastModifiedDate", "TrippedAt", "WindowStart"),
		numberFields:     revertedSCIMStringSet("DeactivateCount"),
		booleanFields:    revertedSCIMStringSet("Tripped"),
	},
}

func revertedSCIMStringSet(values ...string) map[string]struct{} {
	out := make(map[string]struct{}, len(values))
	for _, value := range values {
		out[value] = struct{}{}
	}
	return out
}

type revertedSCIMObjectRow struct {
	id                      string
	apiName                 string
	tableType               string
	label                   string
	description             string
	requiredWritePermission string
	requiredReadPermission  string
	deleteMode              string
	isCustom                int
	searchable              int
	isCustomized            int
	isDeleted               int
}

type revertedSCIMTableRow struct {
	tableName     string
	tableType     string
	category      string
	description   string
	isManaged     int
	schemaVersion string
	createdBy     string
	isDeleted     int
}

type revertedSCIMFieldRow struct {
	apiName      string
	fieldType    string
	required     int
	unique       int
	referenceTo  []string
	isCustomized int
	isDeleted    int
	ownerKind    string
	ownerPack    string
}

type revertedSCIMRelationshipRow struct {
	childObject  string
	parentObject string
	fieldName    string
}

type revertedSCIMRegistryState struct {
	object        *revertedSCIMObjectRow
	table         *revertedSCIMTableRow
	fields        []revertedSCIMFieldRow
	relationships []revertedSCIMRelationshipRow
}

type revertedSCIMStateKind int

const (
	revertedSCIMStateAbsent revertedSCIMStateKind = iota
	revertedSCIMStateActive
	revertedSCIMStateRetired
)

// RetireRevertedSCIMSchema retires the exact metadata shape briefly shipped by
// NexusCRM #3707. It never creates, alters, renames, or drops a physical table;
// preserving those tables is intentional so historical SCIM data remains
// recoverable for a future product-owned migration.
//
// The migration receipt is not trusted as a skip marker. Every invocation
// rechecks closure so a downgrade that resurrected the exact legacy metadata is
// repaired again. Exact monotonic field-retirement residue is completed, while
// customized or structurally partial shapes still fail closed.
func RetireRevertedSCIMSchema(ctx context.Context, db *sql.DB) (int, error) {
	if db == nil {
		return 0, fmt.Errorf("RetireRevertedSCIMSchema requires a database")
	}

	applied, err := bootstate.MigrationApplied(ctx, db, migrationIDRetireRevertedSCIMSchema)
	if err != nil {
		return 0, err
	}
	if applied {
		if err := verifyRevertedSCIMSchemaRetirementClosure(ctx, db); err == nil {
			log.Println("   RetireRevertedSCIMSchema: receipt and retirement closure verified")
			return 0, nil
		}
		// A receipt is not sufficient evidence after a rollback/downgrade may
		// have resurrected metadata. Re-enter the exact-shape classifier and
		// either repair the known state or fail closed on a collision.
	}

	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return 0, fmt.Errorf("RetireRevertedSCIMSchema begin transaction: %w", err)
	}
	defer tx.Rollback() // no-op after Commit

	retired := 0
	for _, fingerprint := range revertedSCIMSchemaFingerprints {
		state, err := loadRevertedSCIMRegistryState(ctx, tx, fingerprint)
		if err != nil {
			return retired, err
		}
		kind, err := validateRevertedSCIMRegistryState(fingerprint, state)
		if err != nil {
			return retired, err
		}

		switch kind {
		case revertedSCIMStateAbsent:
			// Metadata may have been manually removed while derived permissions
			// survived. Keep going so the idempotent derived cleanup below can
			// close that partial state before the receipt is recorded.
		case revertedSCIMStateActive:
			if err := retireActiveRevertedSCIMRegistryState(ctx, tx, fingerprint, state); err != nil {
				return retired, err
			}
			retired++
		case revertedSCIMStateRetired:
			// A prior attempt may have committed the metadata transition before
			// its receipt write failed. Closure cleanup below is idempotent.
		default:
			return retired, fmt.Errorf("RetireRevertedSCIMSchema %s: unknown state %d", fingerprint.name, kind)
		}

		if err := retireRevertedSCIMDerivedMetadata(ctx, tx, fingerprint); err != nil {
			return retired, err
		}
	}

	if err := tx.Commit(); err != nil {
		return retired, fmt.Errorf("RetireRevertedSCIMSchema commit metadata retirement: %w", err)
	}
	if err := verifyRevertedSCIMSchemaRetirementClosure(ctx, db); err != nil {
		return retired, err
	}
	if err := bootstate.RecordMigrationApplied(
		ctx,
		db,
		migrationIDRetireRevertedSCIMSchema,
		1,
		"Retire exact metadata from the reverted SCIM L0 implementation while preserving its physical tables and historical data.",
	); err != nil {
		return retired, err
	}

	if retired > 0 {
		if applied {
			log.Printf("   RetireRevertedSCIMSchema: repaired stamped metadata resurrection for %d table(s)", retired)
		} else {
			log.Printf("   RetireRevertedSCIMSchema: retired metadata for %d reverted SCIM table(s)", retired)
		}
	} else if applied {
		log.Println("   RetireRevertedSCIMSchema: receipt and retirement closure verified")
	}
	return retired, nil
}

func loadRevertedSCIMRegistryState(ctx context.Context, tx *sql.Tx, fingerprint revertedSCIMTableFingerprint) (revertedSCIMRegistryState, error) {
	state := revertedSCIMRegistryState{}
	tableName := fingerprint.name
	object := &revertedSCIMObjectRow{}
	err := tx.QueryRowContext(ctx, `
SELECT Id, ApiName, TableType, Label, COALESCE(Description, ''),
       COALESCE(RequiredWritePermission, ''), COALESCE(RequiredReadPermission, ''),
       COALESCE(DeleteMode, ''), COALESCE(IsCustom, 0), COALESCE(Searchable, 0),
       COALESCE(IsCustomized, 0), COALESCE(IsDeleted, 0)
FROM _System_Object
WHERE LOWER(ApiName) = LOWER(?)
FOR UPDATE`, tableName).Scan(
		&object.id,
		&object.apiName,
		&object.tableType,
		&object.label,
		&object.description,
		&object.requiredWritePermission,
		&object.requiredReadPermission,
		&object.deleteMode,
		&object.isCustom,
		&object.searchable,
		&object.isCustomized,
		&object.isDeleted,
	)
	switch {
	case err == nil:
		state.object = object
	case errors.Is(err, sql.ErrNoRows):
	case err != nil:
		return state, fmt.Errorf("RetireRevertedSCIMSchema load object %s: %w", tableName, err)
	}

	table := &revertedSCIMTableRow{}
	err = tx.QueryRowContext(ctx, `
SELECT TableName, TableType, COALESCE(Category, ''), COALESCE(Description, ''),
       COALESCE(IsManaged, 0), COALESCE(SchemaVersion, ''), COALESCE(CreatedBy, ''),
       COALESCE(IsDeleted, 0)
FROM _System_Table
WHERE LOWER(TableName) = LOWER(?)
FOR UPDATE`, tableName).Scan(
		&table.tableName,
		&table.tableType,
		&table.category,
		&table.description,
		&table.isManaged,
		&table.schemaVersion,
		&table.createdBy,
		&table.isDeleted,
	)
	switch {
	case err == nil:
		state.table = table
	case errors.Is(err, sql.ErrNoRows):
	case err != nil:
		return state, fmt.Errorf("RetireRevertedSCIMSchema load table registry %s: %w", tableName, err)
	}

	rows, err := tx.QueryContext(ctx, `
SELECT ApiName, Type, COALESCE(Required, 0), COALESCE(IsUnique, 0),
       COALESCE(CAST(ReferenceTo AS CHAR), '[]'),
       COALESCE(IsCustomized, 0), COALESCE(IsDeleted, 0),
       COALESCE(OwnerKind, ''), COALESCE(OwnerPack, '')
FROM _System_Field
WHERE ObjectId = ?
ORDER BY ApiName
FOR UPDATE`, fingerprint.objectID)
	if err != nil {
		return state, fmt.Errorf("RetireRevertedSCIMSchema load fields for %s: %w", tableName, err)
	}
	for rows.Next() {
		var field revertedSCIMFieldRow
		var referenceJSON string
		if err := rows.Scan(
			&field.apiName,
			&field.fieldType,
			&field.required,
			&field.unique,
			&referenceJSON,
			&field.isCustomized,
			&field.isDeleted,
			&field.ownerKind,
			&field.ownerPack,
		); err != nil {
			rows.Close()
			return state, fmt.Errorf("RetireRevertedSCIMSchema scan field for %s: %w", tableName, err)
		}
		if err := json.Unmarshal([]byte(referenceJSON), &field.referenceTo); err != nil {
			rows.Close()
			return state, fmt.Errorf("RetireRevertedSCIMSchema parse ReferenceTo for %s.%s: %w", tableName, field.apiName, err)
		}
		sort.Strings(field.referenceTo)
		state.fields = append(state.fields, field)
	}
	if err := rows.Close(); err != nil {
		return state, fmt.Errorf("RetireRevertedSCIMSchema close fields for %s: %w", tableName, err)
	}
	if err := rows.Err(); err != nil {
		return state, fmt.Errorf("RetireRevertedSCIMSchema iterate fields for %s: %w", tableName, err)
	}

	relRows, err := tx.QueryContext(ctx, `
SELECT ChildObjectApiName, ParentObjectApiName, FieldApiName
FROM _System_Relationship
WHERE LOWER(ChildObjectApiName) = LOWER(?) OR LOWER(ParentObjectApiName) = LOWER(?)
ORDER BY ChildObjectApiName, ParentObjectApiName, FieldApiName
FOR UPDATE`, tableName, tableName)
	if err != nil {
		return state, fmt.Errorf("RetireRevertedSCIMSchema load relationships for %s: %w", tableName, err)
	}
	for relRows.Next() {
		var relationship revertedSCIMRelationshipRow
		if err := relRows.Scan(&relationship.childObject, &relationship.parentObject, &relationship.fieldName); err != nil {
			relRows.Close()
			return state, fmt.Errorf("RetireRevertedSCIMSchema scan relationship for %s: %w", tableName, err)
		}
		state.relationships = append(state.relationships, relationship)
	}
	if err := relRows.Close(); err != nil {
		return state, fmt.Errorf("RetireRevertedSCIMSchema close relationships for %s: %w", tableName, err)
	}
	if err := relRows.Err(); err != nil {
		return state, fmt.Errorf("RetireRevertedSCIMSchema iterate relationships for %s: %w", tableName, err)
	}
	return state, nil
}

func validateRevertedSCIMRegistryState(fingerprint revertedSCIMTableFingerprint, state revertedSCIMRegistryState) (revertedSCIMStateKind, error) {
	if state.object == nil {
		if state.table != nil {
			return 0, fmt.Errorf("RetireRevertedSCIMSchema %s: table registry exists without its object metadata", fingerprint.name)
		}
		if len(state.relationships) != 0 {
			return 0, fmt.Errorf("RetireRevertedSCIMSchema %s: relationship metadata exists without its object fingerprint", fingerprint.name)
		}
		if len(state.fields) != 0 {
			return 0, fmt.Errorf("RetireRevertedSCIMSchema %s: historical field metadata exists without its object fingerprint", fingerprint.name)
		}
		return revertedSCIMStateAbsent, nil
	}

	object := state.object
	if object.id != fingerprint.objectID || object.apiName != fingerprint.name || !isRevertedSCIMObjectTableType(object.tableType) || object.label != fingerprint.label ||
		object.description != fingerprint.description || object.requiredWritePermission != "BLOCKED" ||
		object.requiredReadPermission != "BLOCKED" || object.deleteMode != "hard" || object.isCustom != 0 ||
		object.searchable != 1 || object.isCustomized != 0 {
		return 0, fmt.Errorf(
			"RetireRevertedSCIMSchema %s: object metadata does not match the reverted #3707 fingerprint",
			fingerprint.name,
		)
	}

	expectedFields := append([]string(nil), fingerprint.fieldNames...)
	sort.Strings(expectedFields)
	if len(state.fields) != len(expectedFields) {
		return 0, fmt.Errorf(
			"RetireRevertedSCIMSchema %s: field count=%d, want exact reverted count=%d",
			fingerprint.name,
			len(state.fields),
			len(expectedFields),
		)
	}
	for index, expectedName := range expectedFields {
		field := state.fields[index]
		if field.apiName != expectedName || field.isCustomized != 0 || field.ownerKind != "runtime" || field.ownerPack != "" {
			return 0, fmt.Errorf(
				"RetireRevertedSCIMSchema %s.%s: field metadata does not match the reverted runtime-owned fingerprint",
				fingerprint.name,
				expectedName,
			)
		}
		expectedType, expectedRequired, expectedUnique, expectedReferences := expectedRevertedSCIMFieldShape(fingerprint, expectedName)
		if field.fieldType != expectedType || field.required != expectedRequired || field.unique != expectedUnique ||
			!equalStrings(field.referenceTo, expectedReferences) {
			return 0, fmt.Errorf(
				"RetireRevertedSCIMSchema %s.%s: field type/required/unique/reference shape does not match the reverted fingerprint",
				fingerprint.name,
				expectedName,
			)
		}
		if field.isDeleted != 0 && field.isDeleted != 1 {
			return 0, fmt.Errorf(
				"RetireRevertedSCIMSchema %s.%s: invalid field IsDeleted=%d",
				fingerprint.name,
				expectedName,
				field.isDeleted,
			)
		}
		// A metadata reconciler can reactivate the exact legacy object while
		// leaving already-retired field rows untouched. That state is monotonic
		// and safe to finish: never resurrect the retired fields, only retire the
		// remaining exact active rows. The inverse state is not monotonic and
		// remains fail-closed.
		if object.isDeleted == 1 && field.isDeleted == 0 {
			return 0, fmt.Errorf(
				"RetireRevertedSCIMSchema %s.%s: partial retirement state object IsDeleted=%d field IsDeleted=%d",
				fingerprint.name,
				expectedName,
				object.isDeleted,
				field.isDeleted,
			)
		}
	}

	if err := validateRevertedSCIMRelationships(fingerprint, state.relationships); err != nil {
		return 0, err
	}

	switch object.isDeleted {
	case 0:
		if state.table == nil {
			return 0, fmt.Errorf("RetireRevertedSCIMSchema %s: active object is missing its table registry fingerprint", fingerprint.name)
		}
		if err := validateRevertedSCIMTableRow(fingerprint, *state.table); err != nil {
			return 0, err
		}
		return revertedSCIMStateActive, nil
	case 1:
		if state.table != nil {
			if state.table.isDeleted == 1 {
				return 0, fmt.Errorf("RetireRevertedSCIMSchema %s: destructive drop-pending table tombstone is forbidden", fingerprint.name)
			}
			return 0, fmt.Errorf("RetireRevertedSCIMSchema %s: retired object still has a table registry row", fingerprint.name)
		}
		return revertedSCIMStateRetired, nil
	default:
		return 0, fmt.Errorf("RetireRevertedSCIMSchema %s: invalid object IsDeleted=%d", fingerprint.name, object.isDeleted)
	}
}

func isRevertedSCIMObjectTableType(tableType string) bool {
	// Batch object persistence initially classified every _System_* object as
	// system_metadata. ApplyMetadata then reconciled #3707's managed definitions
	// to system_core. Both are exact historical states from that rollout: the
	// former survives if an old bootstrap stopped before post-DDL reconciliation.
	return tableType == "system_metadata" || tableType == "system_core"
}

func expectedRevertedSCIMFieldShape(fingerprint revertedSCIMTableFingerprint, fieldName string) (string, int, int, []string) {
	fieldType := "string"
	if _, ok := fingerprint.datetimeFields[fieldName]; ok {
		fieldType = "datetime"
	}
	if _, ok := fingerprint.numberFields[fieldName]; ok {
		fieldType = "double"
	}
	if _, ok := fingerprint.booleanFields[fieldName]; ok {
		fieldType = "boolean"
	}
	var references []string
	if target, ok := fingerprint.referenceTargets[fieldName]; ok {
		fieldType = "reference"
		references = []string{target}
	}
	required := 0
	if _, ok := fingerprint.requiredFields[fieldName]; ok {
		required = 1
	}
	unique := 0
	if _, ok := fingerprint.uniqueFields[fieldName]; ok {
		unique = 1
	}
	return fieldType, required, unique, references
}

func equalStrings(left, right []string) bool {
	if len(left) != len(right) {
		return false
	}
	for index := range left {
		if left[index] != right[index] {
			return false
		}
	}
	return true
}

func validateRevertedSCIMTableRow(fingerprint revertedSCIMTableFingerprint, table revertedSCIMTableRow) error {
	if table.isDeleted == 1 {
		return fmt.Errorf("RetireRevertedSCIMSchema %s: destructive drop-pending table tombstone is forbidden", fingerprint.name)
	}
	if table.tableName != fingerprint.name || table.tableType != "system_core" || table.category != "auth" ||
		table.description != fingerprint.description || table.isManaged != 0 || table.schemaVersion != "" ||
		table.createdBy != "bootstrap" || table.isDeleted != 0 {
		return fmt.Errorf(
			"RetireRevertedSCIMSchema %s: table registry does not match the reverted #3707 fingerprint",
			fingerprint.name,
		)
	}
	return nil
}

func validateRevertedSCIMRelationships(fingerprint revertedSCIMTableFingerprint, relationships []revertedSCIMRelationshipRow) error {
	if len(relationships) == 0 {
		return nil
	}
	relationship := relationships[0]
	// #3707 stored ReferenceTo only on _System_Field. Relationship catalog
	// reconciliation had already moved to pack apply, so even a semantically
	// plausible SCIM-to-User row is not part of the reverted runtime fingerprint
	// and must not be deleted as historical residue.
	return fmt.Errorf(
		"RetireRevertedSCIMSchema %s: unexpected relationship %s.%s -> %s",
		fingerprint.name,
		relationship.childObject,
		relationship.fieldName,
		relationship.parentObject,
	)
}

func retireActiveRevertedSCIMRegistryState(ctx context.Context, tx *sql.Tx, fingerprint revertedSCIMTableFingerprint, state revertedSCIMRegistryState) error {
	activeFields := int64(0)
	for _, field := range state.fields {
		if field.isDeleted == 0 {
			activeFields++
		}
	}
	if err := execRevertedSCIMExpectedRows(
		ctx,
		tx,
		`UPDATE _System_Field SET IsDeleted = 1, LastModifiedDate = CURRENT_TIMESTAMP WHERE ObjectId = ? AND COALESCE(IsDeleted, 0) = 0`,
		activeFields,
		fingerprint.name+" fields",
		state.object.id,
	); err != nil {
		return err
	}
	if err := execRevertedSCIMExpectedRows(
		ctx,
		tx,
		`UPDATE _System_Object SET IsDeleted = 1, LastModifiedDate = CURRENT_TIMESTAMP WHERE Id = ? AND COALESCE(IsDeleted, 0) = 0`,
		1,
		fingerprint.name+" object",
		state.object.id,
	); err != nil {
		return err
	}
	// Hard-delete only the registry row. An _System_Table.IsDeleted=1 row is
	// DROP-PENDING authority, so creating one here could authorize a later
	// orphan reconciler to destroy the preserved physical table.
	return execRevertedSCIMExpectedRows(
		ctx,
		tx,
		`DELETE FROM _System_Table WHERE TableName = ? AND COALESCE(IsDeleted, 0) = 0`,
		1,
		fingerprint.name+" table registry",
		fingerprint.name,
	)
}

func retireRevertedSCIMDerivedMetadata(ctx context.Context, tx *sql.Tx, fingerprint revertedSCIMTableFingerprint) error {
	if _, err := tx.ExecContext(ctx, `
UPDATE ObjectPermissions
SET IsDeleted = 1, LastModifiedDate = CURRENT_TIMESTAMP
WHERE LOWER(SobjectType) = LOWER(?) AND COALESCE(IsDeleted, 0) = 0`, fingerprint.name); err != nil {
		return fmt.Errorf("RetireRevertedSCIMSchema retire ObjectPermissions for %s: %w", fingerprint.name, err)
	}
	if _, err := tx.ExecContext(ctx, `
UPDATE FieldPermissions
SET IsDeleted = 1, LastModifiedDate = CURRENT_TIMESTAMP
WHERE LOWER(SobjectType) = LOWER(?) AND COALESCE(IsDeleted, 0) = 0`, fingerprint.name); err != nil {
		return fmt.Errorf("RetireRevertedSCIMSchema retire FieldPermissions for %s: %w", fingerprint.name, err)
	}
	return nil
}

func execRevertedSCIMExpectedRows(ctx context.Context, tx *sql.Tx, query string, expected int64, label string, args ...any) error {
	result, err := tx.ExecContext(ctx, query, args...)
	if err != nil {
		return fmt.Errorf("RetireRevertedSCIMSchema retire %s: %w", label, err)
	}
	rows, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("RetireRevertedSCIMSchema retire %s rows affected: %w", label, err)
	}
	if rows != expected {
		return fmt.Errorf("RetireRevertedSCIMSchema retire %s affected %d row(s), want %d", label, rows, expected)
	}
	return nil
}

func verifyRevertedSCIMSchemaRetirementClosure(ctx context.Context, db *sql.DB) error {
	names, placeholders := revertedSCIMNamesAndPlaceholders()
	objectIDs, objectIDPlaceholders := revertedSCIMObjectIDsAndPlaceholders()
	checks := []struct {
		label string
		query string
		args  []any
	}{
		{
			label: "active object metadata",
			query: "SELECT COUNT(*) FROM _System_Object WHERE LOWER(ApiName) IN (" + placeholders + ") AND COALESCE(IsDeleted, 0) = 0",
			args:  names,
		},
		{
			label: "active field metadata",
			query: "SELECT COUNT(*) FROM _System_Field WHERE ObjectId IN (" + objectIDPlaceholders + ") AND COALESCE(IsDeleted, 0) = 0",
			args:  objectIDs,
		},
		{
			label: "table registry metadata",
			query: "SELECT COUNT(*) FROM _System_Table WHERE LOWER(TableName) IN (" + placeholders + ")",
			args:  names,
		},
		{
			label: "active object permissions",
			query: "SELECT COUNT(*) FROM ObjectPermissions WHERE LOWER(SobjectType) IN (" + placeholders + ") AND COALESCE(IsDeleted, 0) = 0",
			args:  names,
		},
		{
			label: "active field permissions",
			query: "SELECT COUNT(*) FROM FieldPermissions WHERE LOWER(SobjectType) IN (" + placeholders + ") AND COALESCE(IsDeleted, 0) = 0",
			args:  names,
		},
		{
			label: "relationship metadata",
			query: "SELECT COUNT(*) FROM _System_Relationship WHERE LOWER(ChildObjectApiName) IN (" + placeholders + ") OR LOWER(ParentObjectApiName) IN (" + placeholders + ")",
			args:  append(append([]any(nil), names...), names...),
		},
	}
	for _, check := range checks {
		var count int
		if err := db.QueryRowContext(ctx, check.query, check.args...).Scan(&count); err != nil {
			return fmt.Errorf("RetireRevertedSCIMSchema closure check %s: %w", check.label, err)
		}
		if count != 0 {
			return fmt.Errorf("RetireRevertedSCIMSchema closure check: %d %s row(s) remain", count, check.label)
		}
	}
	return nil
}

func revertedSCIMNamesAndPlaceholders() ([]any, string) {
	names := make([]any, 0, len(revertedSCIMSchemaFingerprints))
	placeholders := make([]string, 0, len(revertedSCIMSchemaFingerprints))
	for _, fingerprint := range revertedSCIMSchemaFingerprints {
		names = append(names, strings.ToLower(fingerprint.name))
		placeholders = append(placeholders, "?")
	}
	return names, strings.Join(placeholders, ",")
}

func revertedSCIMObjectIDsAndPlaceholders() ([]any, string) {
	objectIDs := make([]any, 0, len(revertedSCIMSchemaFingerprints))
	placeholders := make([]string, 0, len(revertedSCIMSchemaFingerprints))
	for _, fingerprint := range revertedSCIMSchemaFingerprints {
		objectIDs = append(objectIDs, fingerprint.objectID)
		placeholders = append(placeholders, "?")
	}
	return objectIDs, strings.Join(placeholders, ",")
}