package controlplane

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

	"github.com/nexuscrm/kernel/pkg/formula"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/pingcap-inc/nexuscrm-controlplane/layoutownership"
	"github.com/pingcap-inc/nexuscrm-controlplane/packs"
)

func validateLivePackDependencies(ctx context.Context, db *sql.DB, pack *packs.SourceBackedPack) error {
	return validateLivePackDependenciesWithOverrides(ctx, db, pack, nil)
}

func validateLivePackDependenciesForInstallWithPreflightOverrides(ctx context.Context, db *sql.DB, pack *packs.SourceBackedPack, preflightOverrides *installPackPreflightOverrides) error {
	desiredOverrides := desiredPackOverridesFromPreflight(preflightOverrides)
	if len(desiredOverrides) > 0 {
		return validateLivePackDependenciesWithPreflightOverrides(ctx, db, pack, preflightOverrides)
	}
	return validateLivePackDependenciesForInstall(ctx, db, pack)
}

func validateLivePackDependenciesWithOverrides(ctx context.Context, db *sql.DB, pack *packs.SourceBackedPack, activeOverrides map[string]*packs.SourceBackedPack) error {
	return validateLivePackDependenciesWithPreparedCandidate(ctx, db, pack, activeOverrides, nil)
}

func validateLivePackDependenciesWithPreflightOverrides(
	ctx context.Context,
	db *sql.DB,
	pack *packs.SourceBackedPack,
	preflightOverrides *installPackPreflightOverrides,
) error {
	return validateLivePackDependenciesWithPreparedCandidate(
		ctx,
		db,
		pack,
		desiredPackOverridesFromPreflight(preflightOverrides),
		preflightOverrides,
	)
}

func validateLivePackDependenciesWithPreparedCandidate(
	ctx context.Context,
	db *sql.DB,
	pack *packs.SourceBackedPack,
	activeOverrides map[string]*packs.SourceBackedPack,
	preflightOverrides *installPackPreflightOverrides,
) error {
	dependencies := pack.PackDependencies()
	if len(dependencies) == 0 {
		return nil
	}
	for _, dep := range dependencies {
		name := strings.TrimSpace(dep.Name)
		if name == "" {
			return fmt.Errorf("pack %s dependency has empty name", pack.Name())
		}
		row, ok, err := getInstalledPackRowByName(ctx, db, name)
		if err != nil {
			return err
		}
		active, err := packs.IsPackActive(ctx, db, name)
		if err != nil {
			return err
		}
		prepared := ok && offlineCandidatePreparedPeerMatchesRow(preflightOverrides, row)
		if !ok || (!active && !prepared) {
			return fmt.Errorf("pack %s dependency %s is not active", pack.Name(), name)
		}
		version := ""
		if override, ok := activeOverrides[strings.ToLower(name)]; ok && override != nil {
			version = strings.TrimSpace(packs.PackHashMetadataForPack(override).PackVersion)
		} else if row.PackVersion.Valid {
			version = row.PackVersion.String
		}
		if !packs.VersionSatisfiesConstraint(version, dep.VersionConstraint) {
			return fmt.Errorf("pack %s dependency %s version %q does not satisfy %q", pack.Name(), name, version, dep.VersionConstraint)
		}
	}
	return nil
}

func offlineCandidatePreparedPeerMatchesRow(
	preflightOverrides *installPackPreflightOverrides,
	row installedPackRow,
) bool {
	_, ok := offlineCandidatePreparedPeerTarget(preflightOverrides, row)
	return ok
}

func offlineCandidatePreparedPeerTarget(
	preflightOverrides *installPackPreflightOverrides,
	row installedPackRow,
) (*packs.SourceBackedPack, bool) {
	if preflightOverrides == nil || preflightOverrides.offlineCandidatePrepared == nil {
		return nil, false
	}
	execution := preflightOverrides.offlineCandidatePrepared
	if execution.preflight == nil || execution.preflight.offlineCandidatePrepared != execution {
		return nil, false
	}
	name := strings.ToLower(strings.TrimSpace(row.Name))
	target, ok := execution.candidate.byName[name]
	if !ok || !offlineCandidateInstallingRowMatchesTarget(row, target) {
		return nil, false
	}
	for _, prepared := range execution.prepared {
		if strings.EqualFold(strings.TrimSpace(prepared.applied.pack.Name()), name) &&
			prepared.applied.payloadHash == target.payloadHash {
			return target.pack, true
		}
	}
	return nil, false
}

func validateInstallPackAttributionPreflight(ctx context.Context, db *sql.DB, requestedPack *packs.SourceBackedPack, preflightOverrides *installPackPreflightOverrides) error {
	return validateInstallPackAttributionPreflightWithComponentKindRegistrationSet(ctx, db, requestedPack, preflightOverrides, packs.ComponentKindRegistrationSet{})
}

func validateInstallPackAttributionPreflightWithComponentKindRegistrationSet(ctx context.Context, db *sql.DB, requestedPack *packs.SourceBackedPack, preflightOverrides *installPackPreflightOverrides, registrations packs.ComponentKindRegistrationSet) error {
	if requestedPack == nil {
		return fmt.Errorf("install-pack attribution preflight: missing requested pack")
	}
	retryPeers, err := bindApplyAllSourceRevisionAdvanceAttributionRetryPeers(
		preflightOverrides, requestedPack, db, registrations,
	)
	if err != nil {
		return fmt.Errorf("install-pack attribution preflight: %w", err)
	}
	if err := validateApplyAllSourceRevisionAdvanceAttributionRetryPeerRows(
		ctx, db, retryPeers,
	); err != nil {
		return fmt.Errorf("install-pack attribution preflight: %w", err)
	}
	allowedObjects, err := AllowedL0ObjectAPINames(ctx, db)
	if err != nil {
		return fmt.Errorf("install-pack attribution preflight: load L0 object oracle: %w", err)
	}
	allowedPacks := map[string]struct{}{}
	for _, pack := range packs.RuntimeRequiredPacks() {
		allowedPacks[strings.ToLower(pack.Name())] = struct{}{}
	}
	allowedPacks[strings.ToLower(requestedPack.Name())] = struct{}{}
	for _, objectAPIName := range requestedPack.OwnedObjectAPINames() {
		allowedObjects[strings.ToLower(objectAPIName)] = struct{}{}
	}

	requestedObjects := lowerSet(requestedPack.OwnedObjectAPINames())
	for name, retryPeer := range retryPeers {
		allowedPacks[name] = struct{}{}
		for _, objectAPIName := range retryPeer.target.OwnedObjectAPINames() {
			if requestedObjects[strings.ToLower(objectAPIName)] {
				return fmt.Errorf("object %s is already owned by proof-bound retry peer %s", objectAPIName, name)
			}
			allowedObjects[strings.ToLower(objectAPIName)] = struct{}{}
		}
	}
	removingPacks := map[string]struct{}{}
	rows, err := listInstalledSourceBackedPacks(ctx, db)
	if err != nil {
		return err
	}
	var formulaFunctionBundlesForValidation []formula.FunctionBundle
	hasFormulaFunctionBundlesForValidation := false
	loadedFormulaFunctionBundlesForValidation := false
	loadFormulaFunctionBundlesForValidation := func() error {
		if loadedFormulaFunctionBundlesForValidation {
			return nil
		}
		bundles, err := packs.RuntimeFormulaFunctionBundlesForInstallWithOverridesAndComponentKindRegistrationSet(
			ctx,
			db,
			[]packs.Pack{requestedPack},
			runtimeDeclarationOverridesFromPreflightExcludingPack(preflightOverrides, requestedPack.Name()),
			registrations,
		)
		if err != nil {
			return fmt.Errorf("install-pack attribution preflight formula function bundles: %w", err)
		}
		formulaFunctionBundlesForValidation = bundles
		hasFormulaFunctionBundlesForValidation = len(bundles) > 0
		loadedFormulaFunctionBundlesForValidation = true
		return nil
	}
	for _, row := range rows {
		rowName := strings.TrimSpace(row.Name)
		if rowName == "" {
			return fmt.Errorf("installed source-backed pack row has empty name")
		}
		if rowName == requestedPack.Name() {
			continue
		}
		if _, proofBoundRetry := retryPeers[strings.ToLower(rowName)]; proofBoundRetry {
			continue
		}
		active, err := packs.IsPackActive(ctx, db, rowName)
		if err != nil {
			return err
		}
		preparedCandidateTarget, preparedCandidatePeer := offlineCandidatePreparedPeerTarget(preflightOverrides, row)
		preparedCandidatePeer = !active && preparedCandidatePeer
		if !active {
			removing, err := packs.IsPackInRemovalWindow(ctx, db, rowName)
			if err != nil {
				return err
			}
			if removing {
				removingPacks[strings.ToLower(rowName)] = struct{}{}
				continue
			}
		}
		if !active && !preparedCandidatePeer {
			return fmt.Errorf("installed source-backed pack %s status is %q; install-pack requires other source-backed packs to be active", rowName, row.Status)
		}
		activePack, ok := lookupDesiredPackOverride(preflightOverrides, rowName)
		if !ok && preparedCandidatePeer {
			activePack, ok = preparedCandidateTarget, true
		}
		var activeOwnedObjects []string
		if ok {
			activeOwnedObjects = activePack.OwnedObjectAPINames()
		}
		if !ok {
			if peers := activeGenerationAttributionPeersFromPreflight(preflightOverrides); peers != nil {
				var exists bool
				activeOwnedObjects, exists = peers.lookup(rowName)
				if !exists {
					return fmt.Errorf(
						"active source-backed pack %s has no exact active-generation attribution snapshot",
						rowName,
					)
				}
			} else {
				if !installedPackHasSourceIdentity(row) {
					return fmt.Errorf("installed source-backed pack %s has no source identity; run source-backed adoption before installing another pack", rowName)
				}
				activePack, err = sourceBackedPackFromInstalledRowWithComponentKindRegistrationSet(ctx, db, row, registrations)
				if err != nil && isMissingRuntimeFormulaFunctionError(err) {
					if loadErr := loadFormulaFunctionBundlesForValidation(); loadErr != nil {
						return loadErr
					}
					if hasFormulaFunctionBundlesForValidation {
						activePack, err = sourceBackedPackFromInstalledRowWithFormulaBundlesAndComponentKindRegistrationSet(ctx, db, row, formulaFunctionBundlesForValidation, true, registrations)
					}
				}
				if err != nil {
					return fmt.Errorf("active source-backed pack %s: %w", rowName, err)
				}
				if err := verifyInstalledPackHashState(row, activePack); err != nil {
					return fmt.Errorf("active source-backed pack %s: %w", rowName, err)
				}
				activeOwnedObjects = activePack.OwnedObjectAPINames()
			}
		}
		allowedPacks[strings.ToLower(rowName)] = struct{}{}
		for _, objectAPIName := range activeOwnedObjects {
			if requestedObjects[strings.ToLower(objectAPIName)] {
				return fmt.Errorf("object %s is already owned by active source-backed pack %s", objectAPIName, rowName)
			}
			allowedObjects[strings.ToLower(objectAPIName)] = struct{}{}
		}
	}
	if err := validateInstallPackAllowedObjects(ctx, db, sortedKeys(allowedObjects)); err != nil {
		return err
	}
	if err := validateRequestedPackBusinessKeyOwnership(
		ctx,
		db,
		requestedPack.Name(),
		requestedPack.StandardFlowDeveloperNames(),
		requestedPack.ValidationRuleBusinessKeys(),
		requestedPageLayoutOwnershipKeys(requestedPack.Components()),
		requestedObjectRecordMutationPolicyOwnershipKeys(requestedPack.Components()),
		requestedObjectLifecyclePolicyOwnershipKeys(requestedPack.Components()),
	); err != nil {
		return err
	}
	if err := validateRequestedConnectedAppOwnership(ctx, db, requestedPack.Name(), requestedPack.ConnectedAppOwnershipKeys()); err != nil {
		return err
	}
	return validateInstallPackOwnerPackSurfaces(ctx, db, sortedKeys(allowedPacks), sortedKeys(removingPacks), requestedPack.StandardFlowDeveloperNames())
}

// validatePackManagedApprovalOwnership is a non-bypassable transition and
// readiness guard. Without exact OwnerPack-scoped ProcessDefinition coverage,
// a zero-declaration app pack has no component that can reconcile prior rows.
func validatePackManagedApprovalOwnership(ctx context.Context, db *sql.DB, packName string, category packs.Category, registrations packs.ComponentKindRegistrationSet) error {
	if category != packs.AppPack || registrations.HasManagedOwnerPackTable(packs.ComponentKindApprovalProcess, "ProcessDefinition") {
		return nil
	}
	var id string
	err := db.QueryRowContext(ctx, `
SELECT Id
FROM ProcessDefinition
WHERE COALESCE(OwnerPack, '') = ?
ORDER BY Id
LIMIT 1`, packName).Scan(&id)
	if err == sql.ErrNoRows {
		return nil
	}
	if err != nil {
		return fmt.Errorf("query existing approval process ownership for %s: %w", packName, err)
	}
	return fmt.Errorf("pack %s owns ProcessDefinition row %s but no OwnerPack-scoped managed approval_process registration covers ProcessDefinition; dropped approval processes cannot be reconciled", packName, id)
}

func validateActiveInstalledPackManagedApprovalOwnership(ctx context.Context, db *sql.DB, rows []installedPackRow, registrations packs.ComponentKindRegistrationSet) error {
	for _, row := range rows {
		if row.Status != installedPackStatusActive {
			continue
		}
		if err := validatePackManagedApprovalOwnership(ctx, db, row.Name, packs.Category(strings.TrimSpace(row.Category)), registrations); err != nil {
			return fmt.Errorf("active source-backed pack %s managed approval coverage: %w", row.Name, err)
		}
	}
	return nil
}

func validateInstallPackChildRelationshipNamespacePreflight(ctx context.Context, db *sql.DB, pack *packs.SourceBackedPack) error {
	if pack == nil {
		return nil
	}
	proposed, err := packs.ChildRelFKDeclsFromObjectSpecPayloads(pack.RuntimeObjectSpecPayloads(), fmt.Sprintf("install-pack %s runtime object_spec", pack.Name()))
	if err != nil {
		return fmt.Errorf("install-pack attribution preflight: parse %s childRelationshipName declarations: %w", pack.Name(), err)
	}
	if len(proposed) == 0 {
		return nil
	}
	live, err := loadLiveChildRelationshipRows(ctx, db)
	if err != nil {
		return fmt.Errorf("install-pack attribution preflight: load live childRelationshipName namespace: %w", err)
	}
	if err := packs.CheckInstallPackChildRelationshipCollisions(pack.Name(), proposed, live); err != nil {
		return fmt.Errorf("install-pack attribution preflight: childRelationshipName live namespace collision: %w", err)
	}
	liveParentFields, err := loadLiveParentFieldsForChildRelationshipPreflight(ctx, db, proposed)
	if err != nil {
		return fmt.Errorf("install-pack attribution preflight: load live parent fields for childRelationshipName namespace: %w", err)
	}
	if err := packs.CheckInstallPackChildRelationshipParentFieldCollisions(proposed, liveParentFields); err != nil {
		return fmt.Errorf("install-pack attribution preflight: childRelationshipName parent field collision: %w", err)
	}
	return nil
}

func loadLiveChildRelationshipRows(ctx context.Context, db *sql.DB) ([]packs.LiveChildRelFKRow, error) {
	rows, err := db.QueryContext(ctx, `
SELECT COALESCE(f.OwnerKind, ''), COALESCE(f.OwnerPack, ''), COALESCE(o.ApiName, ''), COALESCE(f.ApiName, ''), COALESCE(f.ReferenceTo, ''), COALESCE(f.ChildRelationshipName, '')
FROM _System_Field f
JOIN _System_Object o ON o.Id = f.ObjectId
WHERE COALESCE(f.IsDeleted, 0) = 0
  AND COALESCE(o.IsDeleted, 0) = 0
  AND TRIM(COALESCE(f.ChildRelationshipName, '')) <> ''
  AND (LOWER(COALESCE(f.Type, '')) IN (?, ?, ?) OR COALESCE(f.IsMasterDetail, 0) = 1)
ORDER BY LOWER(o.ApiName), LOWER(f.ApiName)`,
		string(constants.FieldTypeReference),
		string(constants.FieldTypeMasterDetail),
		"lookup",
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []packs.LiveChildRelFKRow
	for rows.Next() {
		var ownerKind, ownerPack, objectAPIName, fieldAPIName, referenceToRaw, childRelationshipName string
		if err := rows.Scan(&ownerKind, &ownerPack, &objectAPIName, &fieldAPIName, &referenceToRaw, &childRelationshipName); err != nil {
			return nil, err
		}
		sourcePack := ""
		if childRelationshipOwnerKindCarriesPack(ownerKind) {
			sourcePack = strings.TrimSpace(ownerPack)
		}
		targets, err := decodeLiveReferenceTargets(referenceToRaw)
		if err != nil {
			return nil, fmt.Errorf("%s.%s ReferenceTo %q: %w", strings.TrimSpace(objectAPIName), strings.TrimSpace(fieldAPIName), referenceToRaw, err)
		}
		for _, parent := range targets {
			parent = strings.TrimSpace(parent)
			if parent == "" {
				continue
			}
			out = append(out, packs.LiveChildRelFKRow{
				SourcePack:            sourcePack,
				ObjectAPIName:         objectAPIName,
				FieldAPIName:          fieldAPIName,
				ParentAPIName:         parent,
				ChildRelationshipName: childRelationshipName,
			})
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return out, nil
}

func childRelationshipOwnerKindCarriesPack(ownerKind string) bool {
	switch strings.ToLower(strings.TrimSpace(ownerKind)) {
	case constants.FieldOwnerKindPack, constants.FieldOwnerKindPackRetained, constants.FieldOwnerKindPackRemoving:
		return true
	default:
		return false
	}
}

func decodeLiveReferenceTargets(raw string) ([]string, error) {
	raw = strings.TrimSpace(raw)
	if raw == "" || strings.EqualFold(raw, "null") {
		return nil, nil
	}
	var targets []string
	if err := json.Unmarshal([]byte(raw), &targets); err == nil {
		return trimNonEmptyStrings(targets), nil
	} else if strings.HasPrefix(raw, "[") {
		return nil, err
	}
	// Legacy defensive fallback for pre-JSON rows. New writes persist JSON.
	return trimNonEmptyStrings(strings.Split(raw, ",")), nil
}

func trimNonEmptyStrings(values []string) []string {
	out := make([]string, 0, len(values))
	for _, value := range values {
		value = strings.TrimSpace(value)
		if value != "" {
			out = append(out, value)
		}
	}
	return out
}

func loadLiveParentFieldsForChildRelationshipPreflight(ctx context.Context, db *sql.DB, proposed []packs.ChildRelFKDecl) ([]packs.LiveParentFieldRow, error) {
	parentSet := map[string]struct{}{}
	for _, decl := range proposed {
		parent := strings.ToLower(strings.TrimSpace(decl.ParentAPIName))
		name := strings.TrimSpace(decl.ChildRelationshipName)
		if parent == "" || name == "" {
			continue
		}
		parentSet[parent] = struct{}{}
	}
	parents := sortedKeys(parentSet)
	if len(parents) == 0 {
		return nil, nil
	}
	parentPredicate, args := inPredicate("o.ApiName", parents)
	rows, err := db.QueryContext(ctx, `
SELECT COALESCE(o.ApiName, ''), COALESCE(f.ApiName, '')
FROM _System_Field f
JOIN _System_Object o ON o.Id = f.ObjectId
WHERE COALESCE(f.IsDeleted, 0) = 0
  AND COALESCE(o.IsDeleted, 0) = 0
  AND `+parentPredicate+`
ORDER BY LOWER(o.ApiName), LOWER(f.ApiName)`, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []packs.LiveParentFieldRow
	for rows.Next() {
		var objectAPIName, fieldAPIName string
		if err := rows.Scan(&objectAPIName, &fieldAPIName); err != nil {
			return nil, err
		}
		out = append(out, packs.LiveParentFieldRow{ObjectAPIName: objectAPIName, FieldAPIName: fieldAPIName})
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return out, nil
}

func lookupDesiredPackOverride(preflightOverrides *installPackPreflightOverrides, packName string) (*packs.SourceBackedPack, bool) {
	desired := desiredPackOverridesFromPreflight(preflightOverrides)
	if len(desired) == 0 {
		return nil, false
	}
	pack, ok := desired[strings.ToLower(strings.TrimSpace(packName))]
	return pack, ok && pack != nil
}

// installPackPreflightPackContext is an immutable, canonical name-to-pack
// snapshot. Its pointer distinguishes an absent context from an explicit empty
// context that suppresses the legacy active-drift fallback.
type installPackPreflightPackContext struct {
	byName map[string]*packs.SourceBackedPack
}

func (c *installPackPreflightPackContext) clone() map[string]*packs.SourceBackedPack {
	if c == nil {
		return nil
	}
	cloned := make(map[string]*packs.SourceBackedPack, len(c.byName))
	for name, pack := range c.byName {
		cloned[name] = pack
	}
	return cloned
}

func newInstallPackPreflightPackContext(raw map[string]*packs.SourceBackedPack) (*installPackPreflightPackContext, error) {
	context := &installPackPreflightPackContext{byName: make(map[string]*packs.SourceBackedPack, len(raw))}
	for rawName, pack := range raw {
		name := strings.ToLower(strings.TrimSpace(rawName))
		if name == "" {
			return nil, fmt.Errorf("install-pack preflight pack context has an empty key")
		}
		if pack == nil {
			return nil, fmt.Errorf("install-pack preflight pack context %q is nil", name)
		}
		packName := strings.ToLower(strings.TrimSpace(pack.Name()))
		if packName == "" || packName != name {
			return nil, fmt.Errorf("install-pack preflight pack context key %q does not match pack name %q", name, pack.Name())
		}
		if _, duplicate := context.byName[name]; duplicate {
			return nil, fmt.Errorf("install-pack preflight pack context duplicates canonical name %q", name)
		}
		context.byName[name] = pack
	}
	return context, nil
}

type installPackPreflightRuntimeDeclarationContext struct {
	byName map[string]packs.Pack
}

// installPackPreflightAttributionPeerContext is an exact immutable-generation
// object-ownership snapshot for active source-backed peers. It lets stopped
// source-set attribution protect live ownership without reopening an omitted
// peer's release checkout.
type installPackPreflightAttributionPeerContext struct {
	ownedObjectsByName map[string][]string
}

func newInstallPackPreflightAttributionPeerContext(raw map[string][]string) (*installPackPreflightAttributionPeerContext, error) {
	context := &installPackPreflightAttributionPeerContext{
		ownedObjectsByName: make(map[string][]string, len(raw)),
	}
	for rawName, rawObjects := range raw {
		name := strings.ToLower(strings.TrimSpace(rawName))
		if name == "" {
			return nil, fmt.Errorf("install-pack attribution peer context has an empty pack name")
		}
		if _, duplicate := context.ownedObjectsByName[name]; duplicate {
			return nil, fmt.Errorf("install-pack attribution peer context duplicates canonical name %q", name)
		}
		objects := make([]string, 0, len(rawObjects))
		for _, rawObject := range rawObjects {
			objectAPIName := strings.TrimSpace(rawObject)
			if objectAPIName == "" {
				return nil, fmt.Errorf("install-pack attribution peer context %q has an empty object name", name)
			}
			objects = append(objects, objectAPIName)
		}
		context.ownedObjectsByName[name] = objects
	}
	return context, nil
}

// loadActiveGenerationAttributionPeerContext reads object ownership from the
// immutable active generation for source-backed peers that are not part of the
// requested replacement set. This is the authoritative stopped-apply read
// path: attribution preflight must not require an omitted peer's release
// checkout or infer ownership from mutable serving metadata.
func loadActiveGenerationAttributionPeerContext(
	ctx context.Context,
	db *sql.DB,
	excludedPackNames map[string]struct{},
) (*installPackPreflightAttributionPeerContext, error) {
	rows, err := listInstalledSourceBackedPacks(ctx, db)
	if err != nil {
		return nil, fmt.Errorf("load active-generation attribution peers: %w", err)
	}
	ownedObjectsByName := make(map[string][]string)
	for _, row := range rows {
		name := strings.ToLower(strings.TrimSpace(row.Name))
		if name == "" {
			return nil, fmt.Errorf("active-generation attribution peer has an empty pack name")
		}
		if _, excluded := excludedPackNames[name]; excluded || row.Status != installedPackStatusActive {
			continue
		}
		ownedObjects, err := loadActivePackGenerationObjectOwnershipForInstalledRow(ctx, db, row.Name)
		if err != nil {
			return nil, fmt.Errorf("load active-generation attribution peer %s: %w", row.Name, err)
		}
		ownedObjectsByName[name] = ownedObjects
	}
	context, err := newInstallPackPreflightAttributionPeerContext(ownedObjectsByName)
	if err != nil {
		return nil, fmt.Errorf("construct active-generation attribution peer context: %w", err)
	}
	return context, nil
}

func (c *installPackPreflightAttributionPeerContext) lookup(packName string) ([]string, bool) {
	if c == nil {
		return nil, false
	}
	objects, ok := c.ownedObjectsByName[strings.ToLower(strings.TrimSpace(packName))]
	return append([]string(nil), objects...), ok
}

func activeGenerationAttributionPeersFromPreflight(preflightOverrides *installPackPreflightOverrides) *installPackPreflightAttributionPeerContext {
	if preflightOverrides == nil {
		return nil
	}
	return preflightOverrides.activeGenerationAttributionPeers
}

func newInstallPackPreflightRuntimeDeclarationContext(raw map[string]packs.Pack) (*installPackPreflightRuntimeDeclarationContext, error) {
	context := &installPackPreflightRuntimeDeclarationContext{byName: make(map[string]packs.Pack, len(raw))}
	for rawName, pack := range raw {
		name := strings.ToLower(strings.TrimSpace(rawName))
		if name == "" || pack == nil || strings.ToLower(strings.TrimSpace(pack.Name())) != name {
			return nil, fmt.Errorf("install-pack runtime declaration context %q does not match a pack", name)
		}
		if _, duplicate := context.byName[name]; duplicate {
			return nil, fmt.Errorf("install-pack runtime declaration context duplicates canonical name %q", name)
		}
		context.byName[name] = pack
	}
	return context, nil
}

func (c *installPackPreflightRuntimeDeclarationContext) clone() map[string]packs.Pack {
	if c == nil {
		return nil
	}
	cloned := make(map[string]packs.Pack, len(c.byName))
	for name, pack := range c.byName {
		cloned[name] = pack
	}
	return cloned
}

func desiredPackOverridesFromPreflight(preflightOverrides *installPackPreflightOverrides) map[string]*packs.SourceBackedPack {
	if preflightOverrides == nil {
		return nil
	}
	if preflightOverrides.plannedDesiredPacks != nil {
		desired := preflightOverrides.plannedDesiredPacks.clone()
		execution := preflightOverrides.offlineCandidatePrepared
		if execution != nil && execution.plan.isMixedTransition() {
			for _, transition := range execution.plan.materialize {
				if transition.target.pack == nil {
					continue
				}
				desired[strings.ToLower(strings.TrimSpace(transition.target.pack.Name()))] = transition.target.pack
			}
		}
		return desired
	}
	return preflightOverrides.activeDriftedPacks
}

func runtimeEffectivePackOverridesFromPreflight(preflightOverrides *installPackPreflightOverrides) map[string]*packs.SourceBackedPack {
	if preflightOverrides == nil {
		return nil
	}
	if preflightOverrides.runtimeEffectivePacks != nil {
		return preflightOverrides.runtimeEffectivePacks.clone()
	}
	return preflightOverrides.activeDriftedPacks
}

func runtimeDeclarationPackOverridesFromPreflight(preflightOverrides *installPackPreflightOverrides) map[string]packs.Pack {
	runtimeEffective := runtimeEffectivePackOverridesFromPreflight(preflightOverrides)
	var overrides map[string]packs.Pack
	if runtimeEffective != nil {
		overrides = make(map[string]packs.Pack, len(runtimeEffective))
		for name, pack := range runtimeEffective {
			overrides[name] = pack
		}
	}
	if preflightOverrides == nil || preflightOverrides.runtimeProjectionCompatibilityPacks == nil {
		return overrides
	}
	if overrides == nil {
		overrides = make(map[string]packs.Pack)
	}
	for name, pack := range preflightOverrides.runtimeProjectionCompatibilityPacks.clone() {
		overrides[name] = pack
	}
	return overrides
}

func withRuntimeEffectivePeerDeclarationOverrides(ctx context.Context, preflightOverrides *installPackPreflightOverrides) context.Context {
	overrides := runtimeEffectivePackOverridesFromPreflight(preflightOverrides)
	if preflightOverrides != nil && preflightOverrides.runtimeEffectivePacks != nil {
		return packs.WithExactPeerDeclarationOverrides(ctx, overrides)
	}
	return packs.WithPeerDeclarationOverrides(ctx, overrides)
}

func runtimeEffectiveDeclarationPacksFromPreflight(preflightOverrides *installPackPreflightOverrides) []packs.Pack {
	runtimeDeclarations := runtimeDeclarationPackOverridesFromPreflight(preflightOverrides)
	if len(runtimeDeclarations) == 0 {
		return nil
	}
	names := make([]string, 0, len(runtimeDeclarations))
	for name := range runtimeDeclarations {
		names = append(names, name)
	}
	sort.Strings(names)
	out := make([]packs.Pack, 0, len(names))
	for _, name := range names {
		if pack := runtimeDeclarations[name]; pack != nil {
			out = append(out, pack)
		}
	}
	return out
}

func runtimeDeclarationOverridesFromPreflight(preflightOverrides *installPackPreflightOverrides) map[string]packs.Pack {
	runtimeDeclarations := runtimeDeclarationPackOverridesFromPreflight(preflightOverrides)
	if len(runtimeDeclarations) == 0 {
		return nil
	}
	out := make(map[string]packs.Pack, len(runtimeDeclarations))
	for name, pack := range runtimeDeclarations {
		if pack == nil {
			continue
		}
		out[name] = pack
	}
	return out
}

func runtimeDeclarationOverridesFromPreflightExcludingPack(preflightOverrides *installPackPreflightOverrides, excludedPackName string) map[string]packs.Pack {
	runtimeDeclarations := runtimeDeclarationPackOverridesFromPreflight(preflightOverrides)
	if len(runtimeDeclarations) == 0 {
		return nil
	}
	excludedPackName = strings.ToLower(strings.TrimSpace(excludedPackName))
	out := make(map[string]packs.Pack, len(runtimeDeclarations))
	for _, pack := range runtimeDeclarations {
		if pack == nil {
			continue
		}
		name := strings.ToLower(strings.TrimSpace(pack.Name()))
		if name == "" || name == excludedPackName {
			continue
		}
		out[name] = pack
	}
	return out
}

func validateInstallPackAllowedObjects(ctx context.Context, db *sql.DB, allowedObjects []string) error {
	predicate, args := notInPredicate("o.ApiName", allowedObjects)
	where := "COALESCE(o.IsDeleted, 0) = 0 AND COALESCE(o.TableType, '') <> 'custom_object' AND " + predicate
	if err := installPreflightCount(ctx, db,
		"unattributed non-custom objects outside L0/installed packs",
		"SELECT COUNT(*) FROM _System_Object o WHERE "+where,
		"SELECT o.ApiName FROM _System_Object o WHERE "+where+" ORDER BY o.ApiName LIMIT 5",
		args...); err != nil {
		return err
	}

	predicate, args = notInPredicate("t.TableName", allowedObjects)
	where = "COALESCE(t.IsDeleted, 0) = 0 AND COALESCE(t.IsManaged, 0) <> 0 AND COALESCE(t.TableType, '') <> 'custom_object' AND " + predicate
	if err := installPreflightCount(ctx, db,
		"unattributed non-custom tables outside L0/installed packs",
		"SELECT COUNT(*) FROM _System_Table t WHERE "+where,
		"SELECT t.TableName FROM _System_Table t WHERE "+where+" ORDER BY t.TableName LIMIT 5",
		args...); err != nil {
		return err
	}

	predicate, args = notInPredicate("o.ApiName", allowedObjects)
	where = "COALESCE(f.IsDeleted, 0) = 0 AND COALESCE(o.IsDeleted, 0) = 0 AND COALESCE(o.TableType, '') <> 'custom_object' AND " + predicate
	return installPreflightCount(ctx, db,
		"field rows under unattributed non-custom objects outside L0/installed packs",
		"SELECT COUNT(*) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId WHERE "+where,
		"SELECT CONCAT(o.ApiName, '.', f.ApiName) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...)
}

func validateInstallPackOwnerPackSurfaces(ctx context.Context, db *sql.DB, allowedPacks []string, removingPacks []string, legacyAdoptableFlowNames []string) error {
	checks := []struct {
		table      string
		ownerExpr  string
		sampleExpr string
		where      string
	}{
		{"_System_RecordType", "rt.OwnerPack", "CONCAT(COALESCE(rt.OwnerPack, ''), ':', COALESCE(o.ApiName, '<missing-object>'), '/', rt.Name)", "COALESCE(rt.IsDeleted, 0) = 0"},
		{"_System_ValidationRule", "v.OwnerPack", "CONCAT(COALESCE(v.OwnerPack, ''), ':', v.ObjectApiName, '/', v.Name)", "1 = 1"},
		{"_System_ReportType", "r.OwnerPack", "CONCAT(COALESCE(r.OwnerPack, ''), ':', r.FullName)", "COALESCE(r.IsDeleted, 0) = 0"},
		{"_System_Action", "a.OwnerPack", "CONCAT(COALESCE(a.OwnerPack, ''), ':', a.ObjectApiName, '/', a.Name)", "COALESCE(a.IsDeleted, 0) = 0"},
		{"_System_StandardAction", "a.OwnerPack", "CONCAT(COALESCE(a.OwnerPack, ''), ':', a.Name)", "COALESCE(a.IsDeleted, 0) = 0"},
		{"_System_Permission", "p.OwnerPack", "CONCAT(COALESCE(p.OwnerPack, ''), ':', p.ApiName)", "1 = 1"},
		{"_System_CustomPermission", "cp.OwnerPack", "CONCAT(COALESCE(cp.OwnerPack, ''), ':', cp.ApiName)", "COALESCE(cp.IsDeleted, 0) = 0"},
		{"_System_RecordTypePicklistValue", "rtpv.OwnerPack", "CONCAT(COALESCE(rtpv.OwnerPack, ''), ':', rtpv.RecordTypeId, '/', rtpv.FieldId, '/', rtpv.`Value`)", "COALESCE(rtpv.IsDeleted, 0) = 0"},
		{"ProcessDefinition", "pd.OwnerPack", "CONCAT(COALESCE(pd.OwnerPack, ''), ':', pd.TableEnumOrId, '/', COALESCE(pd.DeveloperName, pd.Name))", "(pd.State = 'Active' OR pd.IsActive = 1)"},
		{"_System_ConnectorRateLimit", "crl.OwnerPack", "CONCAT(COALESCE(crl.OwnerPack, ''), ':', crl.EndpointClass)", "COALESCE(crl.IsDeleted, 0) = 0"},
		{"_System_ConnectorInboundHandlerConfig", "cihc.Namespace", "CONCAT(COALESCE(cihc.Namespace, ''), ':', cihc.CapabilityKind)", "COALESCE(cihc.IsDeleted, 0) = 0"},
		{"_System_ConnectorCalloutProfile", "ccp.OwnerPack", "CONCAT(COALESCE(ccp.OwnerPack, ''), ':', ccp.ProfileName)", "COALESCE(ccp.IsDeleted, 0) = 0"},
		{"_System_EmailTemplate", "et.OwnerPack", "CONCAT(COALESCE(et.OwnerPack, ''), ':', et.DeveloperName)", "COALESCE(et.IsDeleted, 0) = 0"},
		{"_System_Object.AccessPolicyRecordMutation", "o.AccessPolicyRecordMutationPackName", "CONCAT(COALESCE(o.AccessPolicyRecordMutationPackName, ''), ':', o.ApiName)", "COALESCE(o.IsDeleted, 0) = 0"},
		{"_System_Object.AccessPolicyLifecycle", "o.AccessPolicyLifecyclePackName", "CONCAT(COALESCE(o.AccessPolicyLifecyclePackName, ''), ':', o.ApiName)", "COALESCE(o.IsDeleted, 0) = 0"},
	}
	fromByTable := map[string]string{
		"_System_RecordType":                        "_System_RecordType rt LEFT JOIN _System_Object o ON o.Id = rt.ObjectId",
		"_System_ValidationRule":                    "_System_ValidationRule v",
		"_System_ReportType":                        "_System_ReportType r",
		"_System_Action":                            "_System_Action a",
		"_System_StandardAction":                    "_System_StandardAction a",
		"_System_Permission":                        "_System_Permission p",
		"_System_CustomPermission":                  "_System_CustomPermission cp",
		"_System_RecordTypePicklistValue":           "_System_RecordTypePicklistValue rtpv",
		"ProcessDefinition":                         "ProcessDefinition pd",
		"_System_ConnectorRateLimit":                "_System_ConnectorRateLimit crl",
		"_System_ConnectorInboundHandlerConfig":     "_System_ConnectorInboundHandlerConfig cihc",
		"_System_ConnectorCalloutProfile":           "_System_ConnectorCalloutProfile ccp",
		"_System_EmailTemplate":                     "_System_EmailTemplate et",
		"_System_Object.AccessPolicyRecordMutation": "_System_Object o",
		"_System_Object.AccessPolicyLifecycle":      "_System_Object o",
	}
	for _, check := range checks {
		predicate, args := ownerOutsideRuntimePredicate(check.ownerExpr, allowedPacks)
		where := check.where + " AND " + predicate
		if err := installPreflightCount(ctx, db,
			fmt.Sprintf("%s rows owned by non-active pack", check.table),
			fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s", fromByTable[check.table], where),
			fmt.Sprintf("SELECT %s FROM %s WHERE %s ORDER BY 1 LIMIT 5", check.sampleExpr, fromByTable[check.table], where),
			args...); err != nil {
			return err
		}
	}
	if err := validateInstallPackFieldOwnerPackSurfaces(ctx, db, allowedPacks, removingPacks); err != nil {
		return err
	}
	return validateInstallPackFlowDefinitionOwners(ctx, db, allowedPacks, legacyAdoptableFlowNames)
}

func validateInstallPackFieldOwnerPackSurfaces(ctx context.Context, db *sql.DB, allowedPacks []string, removingPacks []string) error {
	activePredicate, activeArgs := inPredicate("f.OwnerPack", allowedPacks)
	activeWhere := "COALESCE(f.IsDeleted, 0) = 0 AND LOWER(COALESCE(f.OwnerKind, '')) = 'pack' AND (TRIM(COALESCE(f.OwnerPack, '')) = '' OR NOT (" + activePredicate + "))"
	if err := installPreflightCount(ctx, db,
		"_System_Field rows owned by non-active pack",
		"SELECT COUNT(*) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId WHERE "+activeWhere,
		"SELECT CONCAT(COALESCE(f.OwnerPack, ''), ':', COALESCE(o.ApiName, '<missing-object>'), '/', f.ApiName) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId WHERE "+activeWhere+" ORDER BY 1 LIMIT 5",
		activeArgs...); err != nil {
		return err
	}

	removingPredicate, removingArgs := inPredicate("f.OwnerPack", removingPacks)
	removingWhere := "COALESCE(f.IsDeleted, 0) = 0 AND LOWER(COALESCE(f.OwnerKind, '')) = 'pack_removing' AND (TRIM(COALESCE(f.OwnerPack, '')) = '' OR NOT (" + removingPredicate + "))"
	return installPreflightCount(ctx, db,
		"_System_Field pack_removing rows owned by non-removing pack",
		"SELECT COUNT(*) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId WHERE "+removingWhere,
		"SELECT CONCAT(COALESCE(f.OwnerPack, ''), ':', COALESCE(o.ApiName, '<missing-object>'), '/', f.ApiName) FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId WHERE "+removingWhere+" ORDER BY 1 LIMIT 5",
		removingArgs...)
}

func validateInstallPackFlowDefinitionOwners(ctx context.Context, db *sql.DB, allowedPacks []string, legacyAdoptableFlowNames []string) error {
	predicate, args := ownerOutsideRuntimePredicate("fd.OwnerPack", allowedPacks)
	flowNames := sortedLowerStrings(legacyAdoptableFlowNames)
	if len(flowNames) > 0 {
		legacyPredicate, legacyArgs := inPredicate("fd.DeveloperName", flowNames)
		predicate = fmt.Sprintf("(%s) AND NOT (LOWER(fd.OwnerPack) = ? AND %s)", predicate, legacyPredicate)
		args = append(args, strings.ToLower(flowSourceStandard))
		args = append(args, legacyArgs...)
	}
	where := "COALESCE(fd.IsDeleted, 0) = 0 AND " + predicate
	return installPreflightCount(ctx, db,
		"FlowDefinition rows owned by non-active pack",
		"SELECT COUNT(*) FROM FlowDefinition fd WHERE "+where,
		"SELECT CONCAT(COALESCE(fd.OwnerPack, ''), ':', fd.DeveloperName) FROM FlowDefinition fd WHERE "+where+" ORDER BY 1 LIMIT 5",
		args...)
}

type requestedPageLayoutOwnershipKey struct {
	ObjectAPIName string
	LayoutName    string
	IsDefault     bool
}

type requestedAccessPolicySidecarOwnershipKey struct {
	ObjectAPIName string
	BusinessKey   string
}

func requestedPageLayoutOwnershipKeys(components []packs.PackComponent) []requestedPageLayoutOwnershipKey {
	if len(components) == 0 {
		return nil
	}
	keys := make([]requestedPageLayoutOwnershipKey, 0)
	for _, component := range components {
		if component.Kind() != packs.ComponentKindPageLayout {
			continue
		}
		payload, err := component.ExpectedPayload()
		if err != nil {
			continue
		}
		key, err := packs.ParseSourceBackedPageLayoutPreflightKey(
			component.BusinessKey(),
			payload,
			fmt.Sprintf("page_layout %s payload", strings.TrimSpace(component.BusinessKey())),
		)
		if err != nil {
			continue
		}
		if key.ObjectAPIName == "" || strings.HasPrefix(key.ObjectAPIName, "_pack_sweep_") {
			continue
		}
		keys = append(keys, requestedPageLayoutOwnershipKey{
			ObjectAPIName: key.ObjectAPIName,
			LayoutName:    key.LayoutName,
			IsDefault:     key.IsDefault,
		})
	}
	return keys
}

func requestedObjectRecordMutationPolicyOwnershipKeys(components []packs.PackComponent) []requestedAccessPolicySidecarOwnershipKey {
	if len(components) == 0 {
		return nil
	}
	keys := make([]requestedAccessPolicySidecarOwnershipKey, 0)
	for _, component := range components {
		if component.Kind() != packs.ComponentKindObjectRecordMutationPolicy {
			continue
		}
		businessKey := strings.TrimSpace(component.BusinessKey())
		if businessKey == "" {
			continue
		}
		keys = append(keys, requestedAccessPolicySidecarOwnershipKey{
			ObjectAPIName: businessKey,
			BusinessKey:   businessKey,
		})
	}
	return keys
}

func requestedObjectLifecyclePolicyOwnershipKeys(components []packs.PackComponent) []requestedAccessPolicySidecarOwnershipKey {
	if len(components) == 0 {
		return nil
	}
	keys := make([]requestedAccessPolicySidecarOwnershipKey, 0)
	for _, component := range components {
		if component.Kind() != packs.ComponentKindObjectLifecyclePolicy {
			continue
		}
		businessKey := strings.TrimSpace(component.BusinessKey())
		if businessKey == "" {
			continue
		}
		keys = append(keys, requestedAccessPolicySidecarOwnershipKey{
			ObjectAPIName: businessKey,
			BusinessKey:   businessKey,
		})
	}
	return keys
}

type connectedAppUniqueRow struct {
	id          string
	name        string
	consumerKey string
}

func validateRequestedConnectedAppOwnership(ctx context.Context, db *sql.DB, packName string, connectedApps []packs.ConnectedAppOwnershipKey) error {
	declaredIDs := requestedConnectedAppIDSet(connectedApps)
	for _, connectedApp := range connectedApps {
		connectedApp.ID = strings.TrimSpace(connectedApp.ID)
		connectedApp.Name = strings.TrimSpace(connectedApp.Name)
		connectedApp.ConsumerKey = strings.TrimSpace(connectedApp.ConsumerKey)
		if connectedApp.ID == "" {
			continue
		}
		var declarationID string
		var declarationPack sql.NullString
		var declarationDeleted int
		hasSamePackDeclaration := false
		hasSamePackTombstone := false
		err := db.QueryRowContext(ctx, `
SELECT Id, InstalledPackName, COALESCE(IsDeleted, 0)
FROM _System_ConnectedAppDeclaration
WHERE ConnectedAppId = ?
LIMIT 1`, connectedApp.ID).Scan(&declarationID, &declarationPack, &declarationDeleted)
		if err == nil {
			gotPack := ""
			if declarationPack.Valid {
				gotPack = strings.TrimSpace(declarationPack.String)
			}
			if gotPack != packName {
				return fmt.Errorf("install-pack attribution preflight: connected_app %s declaration InstalledPackName = %q (row %s), refusing to install for %q", connectedApp.ID, gotPack, declarationID, packName)
			}
			if declarationDeleted == 0 {
				hasSamePackDeclaration = true
			} else {
				hasSamePackTombstone = true
			}
		} else if err != sql.ErrNoRows {
			return fmt.Errorf("install-pack attribution preflight: query connected_app declaration ownership %s: %w", connectedApp.ID, err)
		}

		rows, err := db.QueryContext(ctx, `
SELECT Id, Name, ConsumerKey, COALESCE(IsActive, 0)
FROM `+constants.TableConnectedApp+`
WHERE Id = ? OR Name = ? OR ConsumerKey = ?
ORDER BY Id`, connectedApp.ID, connectedApp.Name, connectedApp.ConsumerKey)
		if err != nil {
			return fmt.Errorf("install-pack attribution preflight: query connected_app unique key ownership %s: %w", connectedApp.ID, err)
		}
		sameIDRowExists := false
		sameIDRowActive := false
		var conflicts []connectedAppUniqueRow
		for rows.Next() {
			var rowID, rowName, rowConsumerKey string
			var rowActive bool
			if err := rows.Scan(&rowID, &rowName, &rowConsumerKey, &rowActive); err != nil {
				_ = rows.Close()
				return fmt.Errorf("install-pack attribution preflight: scan connected_app unique key ownership %s: %w", connectedApp.ID, err)
			}
			if strings.TrimSpace(rowID) == connectedApp.ID {
				sameIDRowExists = true
				sameIDRowActive = rowActive
				continue
			}
			conflicts = append(conflicts, connectedAppUniqueRow{id: rowID, name: rowName, consumerKey: rowConsumerKey})
		}
		if err := rows.Err(); err != nil {
			_ = rows.Close()
			return fmt.Errorf("install-pack attribution preflight: iterate connected_app unique key ownership %s: %w", connectedApp.ID, err)
		}
		if err := rows.Close(); err != nil {
			return fmt.Errorf("install-pack attribution preflight: close connected_app unique key ownership %s: %w", connectedApp.ID, err)
		}
		for _, conflict := range conflicts {
			rowID := strings.TrimSpace(conflict.id)
			ownership, err := requestedConnectedAppReplacementOwnership(ctx, db, packName, rowID, declaredIDs)
			if err != nil {
				return err
			}
			if ownership.samePackReplacement {
				continue
			}
			if ownership.ownerless {
				adoptable, err := requestedConnectedAppOwnerlessAdoptionCandidate(ctx, db, connectedApp, conflict)
				if err != nil {
					return err
				}
				if adoptable {
					continue
				}
			}
			switch {
			case strings.TrimSpace(conflict.name) == connectedApp.Name:
				return fmt.Errorf("install-pack attribution preflight: connected_app %s Name %q collides with existing _System_ConnectedApp row %s, refusing to install for %q", connectedApp.ID, connectedApp.Name, rowID, packName)
			case strings.TrimSpace(conflict.consumerKey) == connectedApp.ConsumerKey:
				return fmt.Errorf("install-pack attribution preflight: connected_app %s ConsumerKey %q collides with existing _System_ConnectedApp row %s, refusing to install for %q", connectedApp.ID, connectedApp.ConsumerKey, rowID, packName)
			default:
				return fmt.Errorf("install-pack attribution preflight: connected_app %s collides with existing _System_ConnectedApp row %s, refusing to install for %q", connectedApp.ID, rowID, packName)
			}
		}
		if sameIDRowExists && !hasSamePackDeclaration {
			if hasSamePackTombstone && !sameIDRowActive {
				continue
			}
			if hasSamePackTombstone {
				return fmt.Errorf("install-pack attribution preflight: connected_app %s has a soft-deleted declaration for %q but the _System_ConnectedApp row is active, refusing to install", connectedApp.ID, packName)
			}
			return fmt.Errorf("install-pack attribution preflight: connected_app %s already exists without source-backed declaration, refusing to install for %q", connectedApp.ID, packName)
		}
	}
	return nil
}

func requestedConnectedAppIDSet(connectedApps []packs.ConnectedAppOwnershipKey) map[string]struct{} {
	out := map[string]struct{}{}
	for _, connectedApp := range connectedApps {
		if key := strings.ToLower(strings.TrimSpace(connectedApp.ID)); key != "" {
			out[key] = struct{}{}
		}
	}
	return out
}

type requestedConnectedAppReplacementOwnershipResult struct {
	samePackReplacement bool
	ownerless           bool
}

func requestedConnectedAppReplacementOwnership(ctx context.Context, db *sql.DB, packName, existingAppID string, declaredIDs map[string]struct{}) (requestedConnectedAppReplacementOwnershipResult, error) {
	var result requestedConnectedAppReplacementOwnershipResult
	existingAppID = strings.TrimSpace(existingAppID)
	if existingAppID == "" {
		return result, nil
	}
	if _, ok := declaredIDs[strings.ToLower(existingAppID)]; ok {
		return result, nil
	}
	var owner sql.NullString
	var deleted int
	err := db.QueryRowContext(ctx, `
SELECT InstalledPackName, COALESCE(IsDeleted, 0)
FROM _System_ConnectedAppDeclaration
WHERE ConnectedAppId = ?
LIMIT 1`, existingAppID).Scan(&owner, &deleted)
	if err == sql.ErrNoRows {
		result.ownerless = true
		return result, nil
	}
	if err != nil {
		return result, fmt.Errorf("install-pack attribution preflight: query connected_app replacement ownership %s: %w", existingAppID, err)
	}
	if deleted != 0 || !owner.Valid {
		return result, nil
	}
	result.samePackReplacement = strings.TrimSpace(owner.String) == strings.TrimSpace(packName)
	return result, nil
}

func requestedConnectedAppOwnerlessAdoptionCandidate(ctx context.Context, db *sql.DB, expected packs.ConnectedAppOwnershipKey, conflict connectedAppUniqueRow) (bool, error) {
	conflictID := strings.TrimSpace(conflict.id)
	if conflictID == "" || !expected.OwnerlessLegacyAdoptionEligible() {
		return false, nil
	}
	if strings.TrimSpace(conflict.consumerKey) != strings.TrimSpace(expected.ConsumerKey) {
		return false, nil
	}
	candidate, ok, err := loadRequestedConnectedAppOwnershipKey(ctx, db, conflictID)
	if err != nil {
		return false, err
	}
	if !ok {
		return false, fmt.Errorf("install-pack attribution preflight: connected_app %s ConsumerKey %q matched _System_ConnectedApp row %s but the row disappeared before adoption validation", expected.ID, expected.ConsumerKey, conflictID)
	}
	if mismatch := expected.OwnerlessLegacyAdoptionMismatch(candidate); mismatch != "" {
		return false, fmt.Errorf("install-pack attribution preflight: connected_app %s ConsumerKey %q matches ownerless _System_ConnectedApp row %s but row is not safe to adopt: %s", expected.ID, expected.ConsumerKey, conflictID, mismatch)
	}
	return true, nil
}

func loadRequestedConnectedAppOwnershipKey(ctx context.Context, db *sql.DB, appID string) (packs.ConnectedAppOwnershipKey, bool, error) {
	var key packs.ConnectedAppOwnershipKey
	var callbackURL, redirectURIs, scopes, tokenAuth, allowedGrants, consentPolicy, permittedUsers, consumerSecret, consumerSecretHash sql.NullString
	err := db.QueryRowContext(ctx, `
SELECT Id, Name, ConsumerKey, CallbackUrl, RedirectUris, Scopes, IsActive, ClientType, RequirePkce,
       TokenEndpointAuthMethod, AllowedGrantTypes, ConsentPolicy, ConsentVersion, IssueRefreshTokens,
       PermittedUsers, ConsumerSecret, ConsumerSecretHash
FROM `+constants.TableConnectedApp+`
WHERE Id = ?`, strings.TrimSpace(appID)).Scan(
		&key.ID,
		&key.Name,
		&key.ConsumerKey,
		&callbackURL,
		&redirectURIs,
		&scopes,
		&key.IsActive,
		&key.ClientType,
		&key.RequirePKCE,
		&tokenAuth,
		&allowedGrants,
		&consentPolicy,
		&key.ConsentVersion,
		&key.IssueRefreshTokens,
		&permittedUsers,
		&consumerSecret,
		&consumerSecretHash,
	)
	if err == sql.ErrNoRows {
		return key, false, nil
	}
	if err != nil {
		return key, false, fmt.Errorf("install-pack attribution preflight: query connected_app ownerless adoption row %s: %w", strings.TrimSpace(appID), err)
	}
	key.CallbackURL = callbackURL.String
	key.RedirectURIs = splitRequestedConnectedAppRedirects(redirectURIs.String)
	key.Scopes = splitRequestedConnectedAppWords(scopes.String)
	key.TokenEndpointAuthMethod = tokenAuth.String
	key.AllowedGrantTypes = splitRequestedConnectedAppWords(allowedGrants.String)
	key.ConsentPolicy = consentPolicy.String
	key.PermittedUsers = permittedUsers.String
	key.ConsumerSecretPresent = strings.TrimSpace(consumerSecret.String) != "" || strings.TrimSpace(consumerSecretHash.String) != ""
	return key, true, nil
}

func splitRequestedConnectedAppRedirects(raw string) []string {
	raw = strings.TrimSpace(raw)
	if raw == "" {
		return nil
	}
	if strings.HasPrefix(raw, "[") {
		var values []string
		if err := json.Unmarshal([]byte(raw), &values); err == nil {
			return compactRequestedConnectedAppStrings(values)
		}
	}
	return compactRequestedConnectedAppStrings(strings.FieldsFunc(raw, func(r rune) bool {
		return r == ',' || r == '\n' || r == '\r' || r == '\t'
	}))
}

func splitRequestedConnectedAppWords(raw string) []string {
	return compactRequestedConnectedAppStrings(strings.FieldsFunc(raw, func(r rune) bool {
		return r == ',' || r == '\n' || r == '\r' || r == '\t' || r == ' '
	}))
}

func compactRequestedConnectedAppStrings(values []string) []string {
	out := make([]string, 0, len(values))
	for _, value := range values {
		if trimmed := strings.TrimSpace(value); trimmed != "" {
			out = append(out, trimmed)
		}
	}
	return out
}

func validateRequestedPackBusinessKeyOwnership(ctx context.Context, db *sql.DB, packName string, flowNames []string, validationRuleKeys []packs.ValidationRuleBusinessKey, pageLayoutKeys []requestedPageLayoutOwnershipKey, accessPolicySidecarKeySets ...[]requestedAccessPolicySidecarOwnershipKey) error {
	for _, flowName := range flowNames {
		flowName = strings.TrimSpace(flowName)
		if flowName == "" {
			continue
		}
		var id string
		var owner sql.NullString
		err := db.QueryRowContext(ctx, `
SELECT Id, OwnerPack
FROM FlowDefinition
WHERE DeveloperName = ? AND COALESCE(IsDeleted, 0) = 0
LIMIT 1`, flowName).Scan(&id, &owner)
		if err == sql.ErrNoRows {
			continue
		}
		if err != nil {
			return fmt.Errorf("install-pack attribution preflight: query FlowDefinition ownership %s: %w", flowName, err)
		}
		gotOwner := ""
		if owner.Valid {
			gotOwner = strings.TrimSpace(owner.String)
		}
		if gotOwner != packName {
			return fmt.Errorf("install-pack attribution preflight: FlowDefinition %s OwnerPack = %q (row %s), refusing to install for %q", flowName, gotOwner, id, packName)
		}
	}

	for _, key := range validationRuleKeys {
		objectAPIName := strings.TrimSpace(key.ObjectAPIName)
		name := strings.TrimSpace(key.Name)
		if objectAPIName == "" || name == "" {
			continue
		}
		var id string
		var owner sql.NullString
		err := db.QueryRowContext(ctx, `
SELECT Id, OwnerPack
FROM _System_ValidationRule
WHERE ObjectApiName = ? AND Name = ?
LIMIT 1`, objectAPIName, name).Scan(&id, &owner)
		if err == sql.ErrNoRows {
			continue
		}
		if err != nil {
			return fmt.Errorf("install-pack attribution preflight: query validation rule ownership %s/%s: %w", objectAPIName, name, err)
		}
		gotOwner := ""
		if owner.Valid {
			gotOwner = strings.TrimSpace(owner.String)
		}
		if gotOwner != packName {
			return fmt.Errorf("install-pack attribution preflight: validation rule %s/%s OwnerPack = %q (row %s), refusing to install for %q", objectAPIName, name, gotOwner, id, packName)
		}
	}

	for _, key := range pageLayoutKeys {
		objectAPIName := strings.TrimSpace(key.ObjectAPIName)
		layoutName := strings.TrimSpace(key.LayoutName)
		if objectAPIName == "" {
			continue
		}
		label := objectAPIName + "/default"
		if !key.IsDefault {
			if layoutName == "" {
				continue
			}
			label = objectAPIName + "/" + layoutName
		}
		if err := layoutownership.ValidateRequestedSourceBackedPageLayoutOwnership(ctx, db, objectAPIName, layoutName, key.IsDefault, packName); err != nil {
			return fmt.Errorf("install-pack attribution preflight: page_layout %s: %w", label, err)
		}
	}

	var recordMutationPolicyKeys []requestedAccessPolicySidecarOwnershipKey
	if len(accessPolicySidecarKeySets) > 0 {
		recordMutationPolicyKeys = accessPolicySidecarKeySets[0]
	}
	for _, key := range recordMutationPolicyKeys {
		if err := validateRequestedAccessPolicySidecarOwnership(ctx, db, packName, "object_record_mutation_policy", "AccessPolicy.recordMutation", constants.FieldSysObject_AccessPolicyRecordMutationPackName, constants.FieldSysObject_AccessPolicyRecordMutationPackBusinessKey, key); err != nil {
			return err
		}
	}
	var lifecyclePolicyKeys []requestedAccessPolicySidecarOwnershipKey
	if len(accessPolicySidecarKeySets) > 1 {
		lifecyclePolicyKeys = accessPolicySidecarKeySets[1]
	}
	for _, key := range lifecyclePolicyKeys {
		if err := validateRequestedAccessPolicySidecarOwnership(ctx, db, packName, "object_lifecycle_policy", "AccessPolicy.lifecycle", constants.FieldSysObject_AccessPolicyLifecyclePackName, constants.FieldSysObject_AccessPolicyLifecyclePackBusinessKey, key); err != nil {
			return err
		}
	}
	return nil
}

func validateRequestedAccessPolicySidecarOwnership(ctx context.Context, db *sql.DB, packName, componentKind, fact, ownerField, ownerBusinessKeyField string, key requestedAccessPolicySidecarOwnershipKey) error {
	objectAPIName := strings.TrimSpace(key.ObjectAPIName)
	businessKey := strings.TrimSpace(key.BusinessKey)
	if objectAPIName == "" || businessKey == "" {
		return nil
	}
	var id string
	var owner, ownerBusinessKey sql.NullString
	query := fmt.Sprintf(`
SELECT Id, %s, %s
FROM _System_Object
WHERE LOWER(ApiName) = LOWER(?) AND COALESCE(IsDeleted, 0) = 0
LIMIT 1`, ownerField, ownerBusinessKeyField)
	err := db.QueryRowContext(ctx, query, objectAPIName).Scan(&id, &owner, &ownerBusinessKey)
	if err == sql.ErrNoRows {
		return nil
	}
	if err != nil {
		return fmt.Errorf("install-pack attribution preflight: query %s ownership %s: %w", componentKind, objectAPIName, err)
	}
	gotOwner := ""
	if owner.Valid {
		gotOwner = strings.TrimSpace(owner.String)
	}
	gotBusinessKey := ""
	if ownerBusinessKey.Valid {
		gotBusinessKey = strings.TrimSpace(ownerBusinessKey.String)
	}
	if gotOwner == "" && gotBusinessKey == "" {
		return nil
	}
	if gotOwner != packName || gotBusinessKey != businessKey {
		return fmt.Errorf("install-pack attribution preflight: %s %s %s owner = %q/%q (row %s), refusing to install for %q/%q",
			componentKind, objectAPIName, fact, gotOwner, gotBusinessKey, id, packName, businessKey)
	}
	return nil
}

func installPreflightCount(ctx context.Context, db *sql.DB, label, countSQL, sampleSQL string, args ...any) error {
	var count int
	if err := db.QueryRowContext(ctx, countSQL, args...).Scan(&count); err != nil {
		return fmt.Errorf("install-pack attribution preflight: query %s count: %w", label, err)
	}
	if count == 0 {
		return nil
	}
	samples, err := queryStringSamples(ctx, db, sampleSQL, args...)
	if err != nil {
		return fmt.Errorf("install-pack attribution preflight: query %s samples: %w", label, err)
	}
	return fmt.Errorf("install-pack attribution preflight: %s: count=%d sample=%s", label, count, strings.Join(samples, ", "))
}

func sortedKeys(values map[string]struct{}) []string {
	out := make([]string, 0, len(values))
	for value := range values {
		value = strings.TrimSpace(strings.ToLower(value))
		if value != "" {
			out = append(out, value)
		}
	}
	sort.Strings(out)
	return out
}

func sortedLowerStrings(values []string) []string {
	seen := map[string]struct{}{}
	for _, value := range values {
		value = strings.TrimSpace(strings.ToLower(value))
		if value != "" {
			seen[value] = struct{}{}
		}
	}
	return sortedKeys(seen)
}