package packs

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

	"github.com/nexuscrm/files"
	"github.com/nexuscrm/kernel/pkg/formula"
	"github.com/nexuscrm/metadata"
	"github.com/nexuscrm/persistence"
	"github.com/nexuscrm/shared/pkg/searchmetadata"
	"github.com/pingcap-inc/nexuscrm-controlplane/internal/offlinecandidateauthority"
	"github.com/pingcap-inc/nexuscrm-controlplane/packspec"
)

type runtimeMetadataProvider interface {
	RuntimeObjectSpecPayloads() [][]byte
}

type runtimeSearchScopeOrderProvider interface {
	RuntimeSearchScopeOrder() []metadata.SearchScopeOrderItem
	OwnedObjectAPINames() []string
}

type runtimeMetadataRegistration struct {
	pack        Pack
	declaration runtimeDeclaration
}

// RegisterInstalledRuntimeMetadata rebuilds process-local caches from the
// installed pack registry and pack payloads. The caches are not SSoT; they are
// deterministic runtime accelerators for metadata that still lacks a persisted
// lookup table.
func RegisterInstalledRuntimeMetadata(ctx context.Context, db *sql.DB) error {
	packList, err := LoadActiveInstalledRuntimeDeclarations(ctx, db)
	if err != nil {
		return err
	}
	return RegisterRuntimeMetadataForPacks(ctx, db, packList)
}

// RegisterInstalledRuntimeMetadataWithComponentKindRegistrationSet rebuilds
// source-verified runtime metadata with the distribution-owned registrations.
func RegisterInstalledRuntimeMetadataWithComponentKindRegistrationSet(ctx context.Context, db *sql.DB, registrations ComponentKindRegistrationSet) error {
	packList, err := LoadActiveInstalledRuntimeDeclarationsWithComponentKindRegistrationSet(ctx, db, registrations)
	if err != nil {
		return err
	}
	return RegisterRuntimeMetadataForPacks(ctx, db, packList)
}

// RegisterPersistedInstalledRuntimeMetadata rebuilds process-local caches from
// persisted installed-pack runtime declarations only. It intentionally avoids
// source/final-pack resolution and is suitable for startup paths that must skip
// bootstrap-time source verification while still honoring installed metadata.
func RegisterPersistedInstalledRuntimeMetadata(ctx context.Context, db *sql.DB) error {
	return RegisterPersistedInstalledRuntimeMetadataWithExecutor(ctx, db)
}

// RegisterPersistedInstalledRuntimeMetadataWithExecutor reconstructs the same
// process-local declarations through the caller's database snapshot. Catalog
// publication uses this form so installed-pack declarations and the metadata
// rows they classify are read from one transaction.
func RegisterPersistedInstalledRuntimeMetadataWithExecutor(ctx context.Context, executor persistence.Executor) error {
	packList, err := loadActiveInstalledPersistedRuntimeDeclarations(ctx, executor)
	if err != nil {
		return err
	}
	return RegisterRuntimeMetadataForPacks(ctx, nil, packList)
}

// RegisterRuntimeMetadataForInstall rebuilds process-local declarations from
// the full install-time effective pack set: every active installed pack except
// rows being replaced, plus the installing pack payloads. This keeps
// install-pack operation-local context from clearing declarations for another
// active app pack while also allowing same-pack reapply before the new
// AppliedPayloadHash has been finalized.
func RegisterRuntimeMetadataForInstall(ctx context.Context, db *sql.DB, installing Pack) error {
	return RegisterRuntimeMetadataForInstallWithOverrides(ctx, db, installing, nil)
}

func RegisterRuntimeMetadataForInstallingPacks(ctx context.Context, db *sql.DB, installing []Pack) error {
	return RegisterRuntimeMetadataForInstallingPacksWithOverrides(ctx, db, installing, nil)
}

func RegisterRuntimeMetadataForInstallingPacksWithOverrides(ctx context.Context, db *sql.DB, installing []Pack, activeOverrides map[string]Pack) error {
	return registerRuntimeMetadataForInstallingPacksWithOverridesAndComponentKindRegistrationSet(ctx, db, installing, activeOverrides, nil)
}

func RegisterRuntimeMetadataForInstallWithOverrides(ctx context.Context, db *sql.DB, installing Pack, activeOverrides map[string]Pack) error {
	return registerRuntimeMetadataForInstallingPacksWithOverridesAndComponentKindRegistrationSet(ctx, db, []Pack{installing}, activeOverrides, nil)
}

// RegisterRuntimeMetadataForInstallingPacksWithOverridesAndComponentKindRegistrationSet
// rebuilds install-time runtime metadata while reconstructing active
// source-backed peers with the distribution-owned component registrations.
func RegisterRuntimeMetadataForInstallingPacksWithOverridesAndComponentKindRegistrationSet(ctx context.Context, db *sql.DB, installing []Pack, activeOverrides map[string]Pack, registrations ComponentKindRegistrationSet) error {
	return registerRuntimeMetadataForInstallingPacksWithOverridesAndComponentKindRegistrationSet(ctx, db, installing, activeOverrides, &registrations)
}

// RegisterRuntimeMetadataForInstallWithOverridesAndComponentKindRegistrationSet
// is the singular install-pack carrier for distribution-owned component
// registrations.
func RegisterRuntimeMetadataForInstallWithOverridesAndComponentKindRegistrationSet(ctx context.Context, db *sql.DB, installing Pack, activeOverrides map[string]Pack, registrations ComponentKindRegistrationSet) error {
	return registerRuntimeMetadataForInstallingPacksWithOverridesAndComponentKindRegistrationSet(ctx, db, []Pack{installing}, activeOverrides, &registrations)
}

// RegisterPersistedRuntimeMetadataForInstallingPacksWithOverrides rebuilds the
// setup process's runtime declarations without reopening the sources of
// unrelated active packs. Requested overrides and the pack being applied are
// used directly; other active rows contribute only their persisted runtime
// declarations.
func RegisterPersistedRuntimeMetadataForInstallingPacksWithOverrides(
	ctx context.Context,
	authority offlinecandidateauthority.StoppedSourceSetAuthority,
	db *sql.DB,
	installing []Pack,
	activeOverrides map[string]Pack,
) error {
	if !authority.Valid() {
		return fmt.Errorf("stopped source-set runtime metadata authority is required")
	}
	if db == nil {
		return fmt.Errorf("database connection is unavailable")
	}
	installingByName, err := runtimeDeclarationInstallingByName(installing)
	if err != nil {
		return err
	}
	activeRows, err := activeInstalledPackRows(ctx, db)
	if err != nil {
		return err
	}
	packList := make([]Pack, 0, len(activeRows)+len(installingByName))
	for _, row := range activeRows {
		name := strings.ToLower(strings.TrimSpace(row.name))
		if _, replacing := installingByName[name]; replacing {
			continue
		}
		if override, ok := lookupRuntimeDeclarationOverride(activeOverrides, row.name); ok {
			packList = append(packList, override)
			continue
		}
		pack, err := loadPersistedRuntimePackFromRowWithoutSourceVerification(row)
		if err != nil {
			return fmt.Errorf("load persisted runtime declarations for active pack %s: %w", strings.TrimSpace(row.name), err)
		}
		packList = append(packList, pack)
	}
	installingNames := make([]string, 0, len(installingByName))
	for name := range installingByName {
		installingNames = append(installingNames, name)
	}
	sort.Strings(installingNames)
	for _, name := range installingNames {
		packList = append(packList, installingByName[name])
	}
	return RegisterRuntimeMetadataForPacks(ctx, db, packList)
}

func registerRuntimeMetadataForInstallingPacksWithOverridesAndComponentKindRegistrationSet(ctx context.Context, db *sql.DB, installing []Pack, activeOverrides map[string]Pack, registrations *ComponentKindRegistrationSet) error {
	packList, err := runtimeDeclarationContextForInstallWithComponentKindRegistrationSet(ctx, db, installing, activeOverrides, registrations)
	if err != nil {
		return err
	}
	return RegisterRuntimeMetadataForPacks(ctx, db, packList)
}

func RuntimeDeclarationContextForInstall(ctx context.Context, db *sql.DB, installing []Pack) ([]Pack, error) {
	return runtimeDeclarationContextForInstall(ctx, db, installing, nil)
}

func RuntimeFormulaFunctionBundlesForInstall(ctx context.Context, db *sql.DB, installing []Pack) ([]formula.FunctionBundle, error) {
	return RuntimeFormulaFunctionBundlesForInstallWithOverrides(ctx, db, installing, nil)
}

func RuntimeFormulaFunctionBundlesForInstallWithOverrides(ctx context.Context, db *sql.DB, installing []Pack, activeOverrides map[string]Pack) ([]formula.FunctionBundle, error) {
	return runtimeFormulaFunctionBundlesForInstallWithOverridesAndComponentKindRegistrationSet(ctx, db, installing, activeOverrides, nil)
}

func RuntimeFormulaFunctionBundlesForInstallWithOverridesAndComponentKindRegistrationSet(ctx context.Context, db *sql.DB, installing []Pack, activeOverrides map[string]Pack, registrations ComponentKindRegistrationSet) ([]formula.FunctionBundle, error) {
	installing, activeOverrides = normalizedRuntimeFormulaInstallInputs(installing, activeOverrides)
	return runtimeFormulaFunctionBundlesForInstallWithOverridesAndComponentKindRegistrationSet(ctx, db, installing, activeOverrides, &registrations)
}

// ActiveInstalledRuntimeSnapshot is one opaque observation of every active
// installed pack row used to assemble runtime declaration context. Its zero
// value is invalid; callers must mint it with ReadActiveInstalledRuntimeSnapshot.
type ActiveInstalledRuntimeSnapshot struct {
	rows []activeInstalledPackRow
	db   *sql.DB
}

func ReadActiveInstalledRuntimeSnapshot(
	ctx context.Context,
	db *sql.DB,
) (*ActiveInstalledRuntimeSnapshot, error) {
	if db == nil {
		return nil, fmt.Errorf("database connection is unavailable")
	}
	rows, err := activeInstalledPackRows(ctx, db)
	if err != nil {
		return nil, err
	}
	frozen := make([]activeInstalledPackRow, len(rows))
	copy(frozen, rows)
	return &ActiveInstalledRuntimeSnapshot{rows: frozen, db: db}, nil
}

// FrozenPersistedRuntimeDeclarationOverrides reconstructs every active peer
// from the declarations captured in this snapshot. It never reopens a pack
// source, so a mutable checkout that has already moved to a candidate payload
// cannot change the rollback baseline.
func (s *ActiveInstalledRuntimeSnapshot) FrozenPersistedRuntimeDeclarationOverrides() (map[string]Pack, error) {
	if s == nil || s.rows == nil || s.db == nil {
		return nil, fmt.Errorf("active installed runtime snapshot was not minted by its reader")
	}
	overrides := make(map[string]Pack, len(s.rows))
	for _, row := range s.rows {
		pack, err := loadPersistedRuntimePackFromRowWithoutSourceVerification(row)
		if err != nil {
			return nil, fmt.Errorf("freeze persisted runtime declarations for active pack %s: %w", row.name, err)
		}
		name := strings.ToLower(strings.TrimSpace(row.name))
		if name == "" {
			return nil, fmt.Errorf("frozen active runtime pack has an empty name")
		}
		if _, duplicate := overrides[name]; duplicate {
			return nil, fmt.Errorf("frozen active runtime pack %s is duplicated", row.name)
		}
		overrides[name] = pack
	}
	return overrides, nil
}

// RuntimeFormulaFunctionBundlesForInstall assembles one install context from
// this snapshot without re-reading the installed-pack runtime registry.
func (s *ActiveInstalledRuntimeSnapshot) RuntimeFormulaFunctionBundlesForInstall(
	ctx context.Context,
	installing []Pack,
	activeOverrides map[string]Pack,
	registrations ComponentKindRegistrationSet,
) ([]formula.FunctionBundle, error) {
	if s == nil || s.rows == nil || s.db == nil {
		return nil, fmt.Errorf("active installed runtime snapshot was not minted by its reader")
	}
	installing, activeOverrides = normalizedRuntimeFormulaInstallInputs(installing, activeOverrides)
	return s.runtimeFormulaFunctionBundlesForInstall(
		ctx, installing, activeOverrides, &registrations,
	)
}

// RegisterRuntimeMetadataForInstallingPacks rebuilds process-local runtime
// metadata from this frozen active-row observation plus one exact installing
// tuple. Passing an empty tuple restores the runtime metadata observed before
// the installation began. Neither path re-reads the installed-pack registry.
func (s *ActiveInstalledRuntimeSnapshot) RegisterRuntimeMetadataForInstallingPacks(
	ctx context.Context,
	installing []Pack,
	registrations ComponentKindRegistrationSet,
) error {
	return s.RegisterRuntimeMetadataForInstallingPacksWithOverrides(
		ctx, installing, nil, registrations,
	)
}

// RegisterRuntimeMetadataForInstallingPacksWithOverrides rebuilds runtime
// metadata from frozen active rows while allowing the proof owner to provide
// already source-verified versions of those exact rows. This is required for
// rollback: a checkout at the candidate revision cannot truthfully reopen an
// older frozen revision from the same release repository root.
func (s *ActiveInstalledRuntimeSnapshot) RegisterRuntimeMetadataForInstallingPacksWithOverrides(
	ctx context.Context,
	installing []Pack,
	activeOverrides map[string]Pack,
	registrations ComponentKindRegistrationSet,
) error {
	packList, err := s.runtimeDeclarationContextForInstall(
		ctx, installing, activeOverrides, &registrations,
	)
	if err != nil {
		return err
	}
	return RegisterRuntimeMetadataForPacks(ctx, s.db, packList)
}

// withFrozenReleaseRepoAuthorities keeps candidate and rollback source
// authority separate. A candidate operation owns one desired revision per URI,
// while a frozen active snapshot may contain an older revision of another pack
// from that same repository. The snapshot may authorize only the exact stored
// revisions it observed, using the already-authorized repository root. Payload
// hash validation in ResolveInstalledPack still binds every loaded pack to the
// active installed row; no mutable or arbitrary revision fallback is admitted.
func (s *ActiveInstalledRuntimeSnapshot) withFrozenReleaseRepoAuthorities(ctx context.Context) (context.Context, error) {
	options, ok := OperationPackSourceOptions(ctx)
	if !ok || len(options.ReleaseRepoSources) == 0 {
		return ctx, nil
	}
	if options.ReleaseRepoStoredRevisionSources == nil {
		options.ReleaseRepoStoredRevisionSources = map[string]ReleaseRepoSource{}
	}
	for _, row := range s.rows {
		if !row.sourceKind.Valid || strings.TrimSpace(row.sourceKind.String) != PackSourceKindReleaseRepo ||
			!row.sourceURI.Valid || !row.sourceRevision.Valid {
			continue
		}
		uri := strings.TrimSpace(row.sourceURI.String)
		revision := strings.TrimSpace(row.sourceRevision.String)
		if err := ValidateReleaseRepoRevision(revision); err != nil {
			return nil, fmt.Errorf("frozen active runtime pack %s release revision: %w", row.name, err)
		}
		primary, exists := options.ReleaseRepoSources[uri]
		if !exists || strings.TrimSpace(primary.Root) == "" {
			continue
		}
		key := releaseRepoStoredRevisionAuthorityKey(uri, revision)
		frozen := ReleaseRepoSource{Root: primary.Root, Revision: revision}
		if existing, duplicate := options.ReleaseRepoStoredRevisionSources[key]; duplicate && existing != frozen {
			return nil, fmt.Errorf("frozen active runtime release authority %q revision %q conflicts", uri, revision)
		}
		options.ReleaseRepoStoredRevisionSources[key] = frozen
	}
	return WithOperationPackSourceOptions(ctx, options), nil
}

func (s *ActiveInstalledRuntimeSnapshot) runtimeFormulaFunctionBundlesForInstall(
	ctx context.Context,
	installing []Pack,
	activeOverrides map[string]Pack,
	registrations *ComponentKindRegistrationSet,
) ([]formula.FunctionBundle, error) {
	packList, err := s.runtimeDeclarationContextForInstall(
		ctx, installing, activeOverrides, registrations,
	)
	if err != nil {
		return nil, err
	}
	return runtimeFormulaFunctionBundlesFromPacks(packList), nil
}

// runtimeDeclarationContextForInstall is the single snapshot-owned pack-list
// builder. Every formula, candidate-registration, and rollback path therefore
// carries the exact historical release_repo authorities frozen with the active
// rows before it resolves any peer source.
func (s *ActiveInstalledRuntimeSnapshot) runtimeDeclarationContextForInstall(
	ctx context.Context,
	installing []Pack,
	activeOverrides map[string]Pack,
	registrations *ComponentKindRegistrationSet,
) ([]Pack, error) {
	if s == nil || s.rows == nil || s.db == nil {
		return nil, fmt.Errorf("active installed runtime snapshot was not minted by its reader")
	}
	ctx, err := s.withFrozenReleaseRepoAuthorities(ctx)
	if err != nil {
		return nil, err
	}
	installingByName, err := runtimeDeclarationInstallingByName(installing)
	if err != nil {
		return nil, err
	}
	return runtimeDeclarationContextForInstallFromActiveRowsWithComponentKindRegistrationSet(
		ctx, s.db, installing, installingByName, activeOverrides, registrations, s.rows,
	)
}

func runtimeFormulaFunctionBundlesForInstallWithOverridesAndComponentKindRegistrationSet(ctx context.Context, db *sql.DB, installing []Pack, activeOverrides map[string]Pack, registrations *ComponentKindRegistrationSet) ([]formula.FunctionBundle, error) {
	if db == nil {
		return runtimeFormulaFunctionBundlesFromPacks(installing), nil
	}
	snapshot, err := ReadActiveInstalledRuntimeSnapshot(ctx, db)
	if err != nil {
		return nil, err
	}
	return snapshot.runtimeFormulaFunctionBundlesForInstall(ctx, installing, activeOverrides, registrations)
}

func normalizedRuntimeFormulaInstallInputs(installing []Pack, activeOverrides map[string]Pack) ([]Pack, map[string]Pack) {
	seen := make(map[string]struct{}, len(installing))
	normalizedInstalling := make([]Pack, 0, len(installing))
	for _, pack := range installing {
		if pack == nil {
			continue
		}
		name := strings.ToLower(strings.TrimSpace(pack.Name()))
		if _, duplicate := seen[name]; name == "" || duplicate {
			continue
		}
		seen[name] = struct{}{}
		normalizedInstalling = append(normalizedInstalling, pack)
	}
	overrideKeys := make([]string, 0, len(activeOverrides))
	for key := range activeOverrides {
		overrideKeys = append(overrideKeys, key)
	}
	sort.Strings(overrideKeys)
	normalizedOverrides := make(map[string]Pack, len(activeOverrides))
	for _, key := range overrideKeys {
		pack := activeOverrides[key]
		if pack == nil {
			continue
		}
		name := strings.ToLower(strings.TrimSpace(pack.Name()))
		if name == "" {
			continue
		}
		if _, duplicate := seen[name]; duplicate {
			continue
		}
		seen[name] = struct{}{}
		normalizedOverrides[name] = pack
	}
	return normalizedInstalling, normalizedOverrides
}

func RuntimeFormulaFunctionBundlesForActiveInstalledValidation(ctx context.Context, db *sql.DB) ([]formula.FunctionBundle, bool, error) {
	return RuntimeFormulaFunctionBundlesForActiveInstalledValidationWithOverrides(ctx, db, nil)
}

func RuntimeFormulaFunctionBundlesForActiveInstalledValidationWithOverrides(ctx context.Context, db *sql.DB, activeOverrides []Pack) ([]formula.FunctionBundle, bool, error) {
	if db == nil {
		return nil, false, nil
	}
	activePacks, err := activeInstalledPackRows(ctx, db)
	if err != nil {
		return nil, false, err
	}
	overrideByName := make(map[string]struct{}, len(activeOverrides))
	for _, pack := range activeOverrides {
		if pack == nil {
			continue
		}
		name := strings.ToLower(strings.TrimSpace(pack.Name()))
		if name != "" {
			overrideByName[name] = struct{}{}
		}
	}
	activePacksForValidation := activePacks
	if len(overrideByName) > 0 {
		activePacksForValidation = make([]activeInstalledPackRow, 0, len(activePacks))
		for _, row := range activePacks {
			if _, replacing := overrideByName[strings.ToLower(strings.TrimSpace(row.name))]; replacing {
				continue
			}
			activePacksForValidation = append(activePacksForValidation, row)
		}
	}
	activeBundles, hasActiveBundles, err := runtimeFormulaFunctionBundlesFromActiveInstalledRows(activePacksForValidation)
	if err != nil {
		return nil, false, err
	}
	overrideBundles := runtimeFormulaFunctionBundlesFromPacks(activeOverrides)
	bundles := append(activeBundles, overrideBundles...)
	return formula.CloneFunctionBundles(bundles), hasActiveBundles || len(overrideBundles) > 0, nil
}

func runtimeFormulaFunctionBundlesFromPacks(packList []Pack) []formula.FunctionBundle {
	var out []formula.FunctionBundle
	for _, pack := range packList {
		provider, ok := pack.(runtimeFormulaFunctionBundleProvider)
		if !ok || provider == nil {
			continue
		}
		out = append(out, provider.RuntimeFormulaFunctionBundles()...)
	}
	return formula.CloneFunctionBundles(out)
}

func runtimeFormulaFunctionBundlesFromActiveInstalledRows(rows []activeInstalledPackRow) ([]formula.FunctionBundle, bool, error) {
	var out []formula.FunctionBundle
	hasContext := false
	for _, row := range rows {
		if row.hasRuntimeFormulaBundles || row.runtimeFormulaFunctionBundles.Valid {
			hasContext = true
		}
		if !row.runtimeFormulaFunctionBundles.Valid {
			continue
		}
		var bundles []formula.FunctionBundle
		if err := unmarshalJSONRequiredArray(row.runtimeFormulaFunctionBundles.String, &bundles); err != nil {
			return nil, false, fmt.Errorf("parse persisted runtime formula function bundles for installed pack %s: %w", strings.TrimSpace(row.name), err)
		}
		if err := formula.ValidateFunctionBundles(bundles); err != nil {
			return nil, false, fmt.Errorf("validate persisted runtime formula function bundles for installed pack %s: %w", strings.TrimSpace(row.name), err)
		}
		out = append(out, bundles...)
	}
	return formula.CloneFunctionBundles(out), hasContext, nil
}

func RuntimeFormulaFunctionBundlesForInstallSource(ctx context.Context, db *sql.DB, source *ResolvedPackSource) ([]formula.FunctionBundle, error) {
	return RuntimeFormulaFunctionBundlesForInstallSourceWithOverrides(ctx, db, source, nil)
}

func RuntimeFormulaFunctionBundlesForInstallSourceWithOverrides(ctx context.Context, db *sql.DB, source *ResolvedPackSource, activeOverrides map[string]Pack) ([]formula.FunctionBundle, error) {
	if source == nil || source.Manifest == nil {
		return RuntimeFormulaFunctionBundlesForInstallWithOverrides(ctx, db, nil, activeOverrides)
	}
	bundles, err := RuntimeFormulaFunctionBundlesFromResolvedSource(source)
	if err != nil {
		return nil, err
	}
	installing := runtimeFormulaFunctionBundleInstallPack{
		name:     source.Manifest.Name,
		category: source.Manifest.Category,
		bundles:  bundles,
	}
	return RuntimeFormulaFunctionBundlesForInstallWithOverrides(ctx, db, []Pack{installing}, activeOverrides)
}

func RuntimeFormulaFunctionBundlesForInstallSourceWithOverridesAndComponentKindRegistrationSet(ctx context.Context, db *sql.DB, source *ResolvedPackSource, activeOverrides map[string]Pack, registrations ComponentKindRegistrationSet) ([]formula.FunctionBundle, error) {
	if source == nil || source.Manifest == nil {
		return RuntimeFormulaFunctionBundlesForInstallWithOverridesAndComponentKindRegistrationSet(ctx, db, nil, activeOverrides, registrations)
	}
	bundles, err := RuntimeFormulaFunctionBundlesFromResolvedSource(source)
	if err != nil {
		return nil, err
	}
	installing := runtimeFormulaFunctionBundleInstallPack{
		name:     source.Manifest.Name,
		category: source.Manifest.Category,
		bundles:  bundles,
	}
	return RuntimeFormulaFunctionBundlesForInstallWithOverridesAndComponentKindRegistrationSet(ctx, db, []Pack{installing}, activeOverrides, registrations)
}

func runtimeDeclarationContextForInstall(ctx context.Context, db *sql.DB, installing []Pack, activeOverrides map[string]Pack) ([]Pack, error) {
	return runtimeDeclarationContextForInstallWithComponentKindRegistrationSet(ctx, db, installing, activeOverrides, nil)
}

func runtimeDeclarationContextForInstallWithComponentKindRegistrationSet(ctx context.Context, db *sql.DB, installing []Pack, activeOverrides map[string]Pack, registrations *ComponentKindRegistrationSet) ([]Pack, error) {
	installingByName, err := runtimeDeclarationInstallingByName(installing)
	if err != nil {
		return nil, err
	}
	activePacks, err := activeInstalledPackRows(ctx, db)
	if err != nil {
		return nil, err
	}
	return runtimeDeclarationContextForInstallFromActiveRowsWithComponentKindRegistrationSet(
		ctx, db, installing, installingByName, activeOverrides, registrations, activePacks,
	)
}

func runtimeDeclarationInstallingByName(installing []Pack) (map[string]Pack, error) {
	installingByName := map[string]Pack{}
	for _, pack := range installing {
		if pack == nil {
			continue
		}
		name := strings.ToLower(strings.TrimSpace(pack.Name()))
		if name == "" {
			return nil, fmt.Errorf("install declaration context includes pack with empty name")
		}
		installingByName[name] = pack
	}
	return installingByName, nil
}

func runtimeDeclarationContextForInstallFromActiveRowsWithComponentKindRegistrationSet(
	ctx context.Context,
	db *sql.DB,
	installing []Pack,
	installingByName map[string]Pack,
	activeOverrides map[string]Pack,
	registrations *ComponentKindRegistrationSet,
	activePacks []activeInstalledPackRow,
) ([]Pack, error) {
	activePacksForValidation := make([]activeInstalledPackRow, 0, len(activePacks))
	var overrideFormulaFunctionBundles []formula.FunctionBundle
	for _, row := range activePacks {
		if _, replacing := installingByName[strings.ToLower(strings.TrimSpace(row.name))]; replacing {
			continue
		}
		if override, ok := lookupRuntimeDeclarationOverride(activeOverrides, row.name); ok {
			overrideFormulaFunctionBundles = append(overrideFormulaFunctionBundles, runtimeFormulaFunctionBundlesFromPacks([]Pack{override})...)
			continue
		}
		activePacksForValidation = append(activePacksForValidation, row)
	}
	activeFormulaFunctionBundles, hasActiveFormulaFunctionBundles, err := runtimeFormulaFunctionBundlesFromActiveInstalledRows(activePacksForValidation)
	if err != nil {
		return nil, err
	}
	installingFormulaFunctionBundles := runtimeFormulaFunctionBundlesFromPacks(installing)
	formulaFunctionBundlesForValidation := append(activeFormulaFunctionBundles, overrideFormulaFunctionBundles...)
	formulaFunctionBundlesForValidation = append(formulaFunctionBundlesForValidation, installingFormulaFunctionBundles...)
	hasFormulaFunctionBundlesForValidation := hasActiveFormulaFunctionBundles || len(overrideFormulaFunctionBundles) > 0 || len(installingFormulaFunctionBundles) > 0
	packList := make([]Pack, 0, len(activePacks)+len(installingByName))
	for _, row := range activePacks {
		if _, replacing := installingByName[strings.ToLower(strings.TrimSpace(row.name))]; replacing {
			continue
		}
		if override, ok := lookupRuntimeDeclarationOverride(activeOverrides, row.name); ok {
			packList = append(packList, override)
			continue
		}
		pack, err := loadRuntimePackFromActiveRowForInstall(ctx, db, row, formulaFunctionBundlesForValidation, hasFormulaFunctionBundlesForValidation, registrations)
		if err != nil {
			return nil, err
		}
		packList = append(packList, pack)
	}
	installingNames := make([]string, 0, len(installingByName))
	for name := range installingByName {
		installingNames = append(installingNames, name)
	}
	sort.Strings(installingNames)
	for _, name := range installingNames {
		packList = append(packList, installingByName[name])
	}
	return packList, nil
}

func lookupRuntimeDeclarationOverride(activeOverrides map[string]Pack, packName string) (Pack, bool) {
	if len(activeOverrides) == 0 {
		return nil, false
	}
	pack, ok := activeOverrides[strings.ToLower(strings.TrimSpace(packName))]
	return pack, ok
}

type runtimeFormulaFunctionBundleInstallPack struct {
	name     string
	category Category
	bundles  []formula.FunctionBundle
}

func (p runtimeFormulaFunctionBundleInstallPack) Name() string {
	return strings.TrimSpace(p.name)
}

func (p runtimeFormulaFunctionBundleInstallPack) Category() Category {
	if p.category == "" {
		return AppPack
	}
	return p.category
}

func (p runtimeFormulaFunctionBundleInstallPack) Apply(context.Context, ApplyDeps) error {
	return nil
}

func (p runtimeFormulaFunctionBundleInstallPack) Verify(context.Context, VerifyDeps) error {
	return nil
}

func (p runtimeFormulaFunctionBundleInstallPack) RuntimeFormulaFunctionBundles() []formula.FunctionBundle {
	return formula.CloneFunctionBundles(p.bundles)
}

// ResolveActiveInstalledRuntimePacks resolves the authoritative runtime pack
// set for active installed rows. App packs must carry source identity; the
// platform binary does not provide CRM/app-pack fallback constructors.
func ResolveActiveInstalledRuntimePacks(ctx context.Context, db *sql.DB) ([]Pack, error) {
	return resolveActiveInstalledRuntimePacks(ctx, db, nil)
}

// ResolveActiveInstalledRuntimePacksWithComponentKindRegistrationSet resolves
// active installed rows with the distribution-owned immutable registrations.
func ResolveActiveInstalledRuntimePacksWithComponentKindRegistrationSet(ctx context.Context, db *sql.DB, registrations ComponentKindRegistrationSet) ([]Pack, error) {
	return resolveActiveInstalledRuntimePacks(ctx, db, &registrations)
}

func resolveActiveInstalledRuntimePacks(ctx context.Context, db *sql.DB, registrations *ComponentKindRegistrationSet) ([]Pack, error) {
	activePacks, err := activeInstalledPackRows(ctx, db)
	if err != nil {
		return nil, err
	}
	formulaFunctionBundles, hasFormulaFunctionBundles, err := runtimeFormulaFunctionBundlesFromActiveInstalledRows(activePacks)
	if err != nil {
		return nil, err
	}
	packList := make([]Pack, 0, len(activePacks))
	for _, row := range activePacks {
		pack, err := resolveActiveInstalledRuntimePack(ctx, db, row, formulaFunctionBundles, hasFormulaFunctionBundles, registrations)
		if err != nil {
			return nil, err
		}
		packList = append(packList, pack)
	}
	return packList, nil
}

// ActiveInstalledPackNames returns the authoritative active installed pack
// name set for runtime gates that need pack presence without resolving payloads.
func ActiveInstalledPackNames(ctx context.Context, db *sql.DB) (map[string]bool, error) {
	activePacks, err := activeInstalledPackRows(ctx, db)
	if err != nil {
		return nil, err
	}
	names := make(map[string]bool, len(activePacks))
	for _, row := range activePacks {
		name := strings.TrimSpace(row.name)
		if name != "" {
			names[name] = true
		}
	}
	return names, nil
}

// KnownInstalledPackCatalog returns the host's full 3-state known-pack catalog,
// the single boot-time map both dialect doors resolve every OwningPack against:
// present+true = known & active; present+false = known & inactive (a DB row in
// installing/removing/failed); absent = unknown / not-yet-installed.
//
// It reads ONLY external authorities, never a consuming dialect's own OwningPack:
// (a) every non-deleted _System_InstalledPack row (NO Status filter — installing,
// removing, and failed rows MUST appear so a known-but-inactive pack is present
// with false and SKIPS, distinct from an absent/typo'd reference), with the row's
// raw Status column compared to "active"; UNIONED with (b) the binary's
// compile-time platform-pack list allKnownPacks() (always known & active). The
// active-only ActiveInstalledPackNames projection is the value==true subset of
// this catalog (their consistency is the single SSoT, not two read paths).
//
// Status is compared as a raw column read rather than against a status-enum set:
// the canonical domain set in controlplane/installed_packs.go and the smaller set
// in installed_pack_helpers.go each risk silently dropping a row (e.g. 'failed'),
// which would collapse the known-but-inactive state into absent.
func KnownInstalledPackCatalog(ctx context.Context, db *sql.DB) (map[string]bool, error) {
	rows, err := db.QueryContext(ctx, `
		SELECT Name, Status
		FROM _System_InstalledPack
		WHERE COALESCE(IsDeleted, 0) = 0
		ORDER BY Name`)
	if err != nil {
		return nil, fmt.Errorf("query known installed packs: %w", err)
	}
	defer rows.Close()

	catalog := map[string]bool{}
	for rows.Next() {
		var name, status string
		if err := rows.Scan(&name, &status); err != nil {
			return nil, fmt.Errorf("scan known installed pack: %w", err)
		}
		name = strings.TrimSpace(name)
		if name != "" {
			catalog[name] = status == "active"
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate known installed packs: %w", err)
	}
	for _, p := range allKnownPacks() {
		catalog[p.Name()] = true
	}
	return catalog, nil
}

// ActiveInstalledAppPack is the neutral installed-pack projection consumed by
// distribution composition roots. L0 owns only the installed-pack/source/hash
// facts; product distributions decide which pack identities they require.
type ActiveInstalledAppPack struct {
	Name               string
	SourceKind         string
	SourceURI          string
	SourceRevision     string
	AppliedPayloadHash string
	DesiredPayloadHash string
}

// ActiveInstalledAppPacks returns the active L1 app/distribution packs with
// enough source identity for composition roots to fail closed on a mismatched
// binary+metadata pairing.
func ActiveInstalledAppPacks(ctx context.Context, db *sql.DB) ([]ActiveInstalledAppPack, error) {
	rows, err := db.QueryContext(ctx, `
		SELECT Name, SourceKind, SourceURI, SourceRevision, AppliedPayloadHash, DesiredPayloadHash
		FROM _System_InstalledPack
		WHERE Status = 'active' AND Category = ? AND COALESCE(IsDeleted, 0) = 0
		ORDER BY Name`, string(AppPack))
	if err != nil {
		return nil, fmt.Errorf("query active installed app packs: %w", err)
	}
	defer rows.Close()

	packRows := make([]ActiveInstalledAppPack, 0)
	for rows.Next() {
		var row ActiveInstalledAppPack
		var sourceKind, sourceURI, sourceRevision, appliedHash, desiredHash sql.NullString
		if err := rows.Scan(&row.Name, &sourceKind, &sourceURI, &sourceRevision, &appliedHash, &desiredHash); err != nil {
			return nil, fmt.Errorf("scan active installed app pack: %w", err)
		}
		row.Name = strings.TrimSpace(row.Name)
		if row.Name == "" {
			continue
		}
		row.SourceKind = trimNullString(sourceKind)
		row.SourceURI = trimNullString(sourceURI)
		row.SourceRevision = trimNullString(sourceRevision)
		row.AppliedPayloadHash = trimNullString(appliedHash)
		row.DesiredPayloadHash = trimNullString(desiredHash)
		packRows = append(packRows, row)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate active installed app packs: %w", err)
	}
	return packRows, nil
}

// ActiveInstalledAppPackNames returns the active L1 app/distribution pack names.
// Prefer ActiveInstalledAppPacks for composition guards that need source identity.
func ActiveInstalledAppPackNames(ctx context.Context, db *sql.DB) ([]string, error) {
	rows, err := ActiveInstalledAppPacks(ctx, db)
	if err != nil {
		return nil, err
	}
	names := make([]string, 0, len(rows))
	for _, row := range rows {
		names = append(names, row.Name)
	}
	sort.Strings(names)
	return names, nil
}

func trimNullString(value sql.NullString) string {
	if !value.Valid {
		return ""
	}
	return strings.TrimSpace(value.String)
}

// RegisterRuntimeMetadataForPacks rebuilds process-local runtime metadata from
// a known pack set. Apply flows use this before marking packs active so final
// materialization can run without prematurely rewriting AppliedPayloadHash.
func RegisterRuntimeMetadataForPacks(ctx context.Context, db *sql.DB, packList []Pack) error {
	systemSpec, err := packspec.StandardObjectsSpecByCategoryJSON("system")
	if err != nil {
		return fmt.Errorf("load L0 runtime object spec: %w", err)
	}
	payloads := [][]byte{systemSpec}
	declarationSets, registrations, err := validateRuntimeMetadataDeclarations(packList)
	if err != nil {
		return err
	}
	if _, err := mergeCompatibilityDeclarationSets(declarationSets...); err != nil {
		return fmt.Errorf("validate runtime metadata declarations: %w", err)
	}
	if err := validateRuntimeDeclarationRegistrations(registrations); err != nil {
		return err
	}
	for _, registration := range registrations {
		payloads = append(payloads, registration.declaration.objectSpecPayloads...)
	}

	metadata.ClearPackRelatedListRules()
	metadata.ClearPackSearchScopeOrders()
	files.ClearHrefFields()
	searchmetadata.ClearPackSearchAllDefaultFields()
	for _, registration := range registrations {
		if err := publishRuntimeDeclaration(registration.pack.Name(), registration.declaration); err != nil {
			return fmt.Errorf("register runtime metadata for pack %s: %w", registration.pack.Name(), err)
		}
	}
	if err := formula.RegisterRuntimeFunctionBundles(runtimeFormulaFunctionBundlesFromRegistrations(registrations)); err != nil {
		return fmt.Errorf("register runtime formula function bundles: %w", err)
	}

	if err := fieldProvenanceInitializer.InitFieldProvenanceFromStandardObjectSpecs(
		fieldProvenanceInitializer.DefaultPlatformStandardFields(),
		payloads...,
	); err != nil {
		return fmt.Errorf("initialize installed field provenance: %w", err)
	}
	return nil
}

func validateRuntimeMetadataDeclarations(packList []Pack) ([]compatibilityDeclarationSet, []runtimeMetadataRegistration, error) {
	platformDecls, err := loadPlatformCompatibilityDeclarations()
	if err != nil {
		return nil, nil, fmt.Errorf("validate platform compatibility declarations: %w", err)
	}

	declarationSets := []compatibilityDeclarationSet{platformDecls}
	registrations := make([]runtimeMetadataRegistration, 0, len(packList))
	for _, pack := range packList {
		if pack == nil {
			continue
		}
		decl := buildRuntimeDeclaration(pack)
		registrations = append(registrations, runtimeMetadataRegistration{
			pack:        pack,
			declaration: decl,
		})
		packPayloads := decl.objectSpecPayloads
		if len(packPayloads) == 0 {
			continue
		}
		packDecls, err := compatibilityDeclarationsFromObjectSpecPayloads(packPayloads, pack.Name()+"-runtime-object-spec")
		if err != nil {
			return nil, nil, fmt.Errorf("validate runtime metadata declarations for pack %s: %w", pack.Name(), err)
		}
		declarationSets = append(declarationSets, packDecls)
	}
	return declarationSets, registrations, nil
}

func validateRuntimeDeclarationRegistrations(registrations []runtimeMetadataRegistration) error {
	ownerByObject := map[string]string{}
	searchAllOwnerByObject := map[string]string{}
	formulaBundles := make([]formula.FunctionBundle, 0)
	hrefOwnerByField := map[string]string{}
	for _, registration := range registrations {
		pack := registration.pack
		decl := registration.declaration
		if len(decl.relatedListRules) > 0 {
			if err := metadata.ValidatePackRelatedListRules(decl.relatedListRules); err != nil {
				return fmt.Errorf("validate related-list rules for pack %s: %w", pack.Name(), err)
			}
		}
		formulaBundles = append(formulaBundles, decl.formulaFunctionBundles...)
		ownedObjectAPINames, err := ownedObjectAPINamesFromRuntimeDeclaration(pack.Name(), decl)
		if err != nil {
			return err
		}

		items := decl.searchScopeOrder
		if len(items) > 0 {
			if err := metadata.ValidatePackSearchScopeOrder(items); err != nil {
				return fmt.Errorf("validate search scope order for pack %s: %w", pack.Name(), err)
			}
			if err := metadata.ValidatePackSearchScopeOrderOwnership(items, ownedObjectAPINames); err != nil {
				return fmt.Errorf("validate search scope order for pack %s: %w", pack.Name(), err)
			}
			for _, item := range items {
				name := strings.TrimSpace(item.Name)
				if name == "" {
					continue
				}
				key := strings.ToLower(name)
				if existingOwner, ok := ownerByObject[key]; ok && !strings.EqualFold(existingOwner, pack.Name()) {
					return fmt.Errorf("search scope object %s declared by both %s and %s", name, existingOwner, pack.Name())
				}
				ownerByObject[key] = pack.Name()
			}
		}

		searchAllDefaults := cloneSearchAllDefaultFieldsDeclarations(decl.searchAllDefaultFields)
		if len(searchAllDefaults) > 0 {
			if err := searchmetadata.ValidatePackSearchAllDefaultFields(searchAllDefaults); err != nil {
				return fmt.Errorf("validate search_all_default_fields for pack %s: %w", pack.Name(), err)
			}
			if err := searchmetadata.ValidatePackSearchAllDefaultFieldsOwnership(searchAllDefaults, ownedObjectAPINames); err != nil {
				return fmt.Errorf("validate search_all_default_fields for pack %s: %w", pack.Name(), err)
			}
			specs, err := ParseObjectSpecsPayloads(decl.objectSpecPayloads, pack.Name()+"-runtime-search-all-default-fields")
			if err != nil {
				return fmt.Errorf("validate search_all_default_fields for pack %s: %w", pack.Name(), err)
			}
			if err := validateSearchAllDefaultFieldsForObjectSpecs(searchAllDefaults, specs); err != nil {
				return fmt.Errorf("validate search_all_default_fields for pack %s: %w", pack.Name(), err)
			}
			for _, decl := range searchAllDefaults {
				name := strings.TrimSpace(decl.ObjectAPIName)
				if name == "" {
					continue
				}
				key := strings.ToLower(name)
				if existingOwner, ok := searchAllOwnerByObject[key]; ok && !strings.EqualFold(existingOwner, pack.Name()) {
					return fmt.Errorf("search_all_default_fields object %s declared by both %s and %s", name, existingOwner, pack.Name())
				}
				searchAllOwnerByObject[key] = pack.Name()
			}
		}

		hrefFields := files.NormalizeHrefRewriteFields(decl.hrefRewriteFields)
		if len(hrefFields) > 0 {
			if len(hrefFields) != len(decl.hrefRewriteFields) {
				return fmt.Errorf("validate href_rewrite_fields for pack %s: blank or duplicate field declarations", pack.Name())
			}
			specs, err := ParseObjectSpecsPayloads(decl.objectSpecPayloads, pack.Name()+"-runtime-href-rewrite-fields")
			if err != nil {
				return fmt.Errorf("validate href_rewrite_fields for pack %s: %w", pack.Name(), err)
			}
			if err := validateHrefRewriteFieldsForObjectSpecs(hrefFields, specs); err != nil {
				return fmt.Errorf("validate href_rewrite_fields for pack %s: %w", pack.Name(), err)
			}
			for _, field := range hrefFields {
				key := strings.ToLower(strings.TrimSpace(field.ParentApiName)) + "\x00" + strings.ToLower(strings.TrimSpace(field.FieldApiName))
				if existingOwner, ok := hrefOwnerByField[key]; ok && !strings.EqualFold(existingOwner, pack.Name()) {
					return fmt.Errorf("href_rewrite_fields %s/%s declared by both %s and %s", field.ParentApiName, field.FieldApiName, existingOwner, pack.Name())
				}
				hrefOwnerByField[key] = pack.Name()
			}
		}
	}
	if err := formula.ValidateFunctionBundles(formulaBundles); err != nil {
		return fmt.Errorf("validate formula function bundles: %w", err)
	}
	return nil
}

func runtimeFormulaFunctionBundlesFromRegistrations(registrations []runtimeMetadataRegistration) []formula.FunctionBundle {
	out := make([]formula.FunctionBundle, 0)
	for _, registration := range registrations {
		out = append(out, registration.declaration.formulaFunctionBundles...)
	}
	return out
}

func publishRuntimeDeclaration(owner string, decl runtimeDeclaration) error {
	if err := metadata.RegisterPackRelatedListRules(owner, decl.relatedListRules); err != nil {
		return err
	}
	if err := metadata.RegisterPackSearchScopeOrder(owner, decl.searchScopeOrder); err != nil {
		return err
	}
	files.RegisterHrefFields(decl.hrefRewriteFields...)
	return searchmetadata.RegisterPackSearchAllDefaultFields(owner, decl.searchAllDefaultFields)
}

func ownedObjectAPINamesFromRuntimeDeclaration(packName string, decl runtimeDeclaration) ([]string, error) {
	if len(decl.objectSpecPayloads) == 0 {
		return nil, nil
	}
	specs, err := ParseObjectSpecsPayloads(decl.objectSpecPayloads, "runtime-declaration")
	if err != nil {
		return nil, fmt.Errorf("parse runtime object specs for pack %s: %w", packName, err)
	}
	return objectSpecAPINames(specs), nil
}

// RegisterL0RuntimeMetadata rebuilds process-local runtime metadata for the
// clean L0 path only. It must not read _System_InstalledPack or resolve app
// packs; app-pack payload registration belongs exclusively to install/apply
// flows after clean-L0 verification has passed.
func RegisterL0RuntimeMetadata(_ context.Context, _ *sql.DB) error {
	if _, _, err := validateRuntimeMetadataDeclarations(nil); err != nil {
		return err
	}

	metadata.ClearPackRelatedListRules()
	metadata.ClearPackSearchScopeOrders()
	files.ClearHrefFields()
	searchmetadata.ClearPackSearchAllDefaultFields()
	metadata.ClearTenantRelatedListRules()
	if err := formula.RegisterRuntimeFunctionBundles(nil); err != nil {
		return fmt.Errorf("clear L0 formula function bundles: %w", err)
	}

	systemSpec, err := packspec.StandardObjectsSpecByCategoryJSON("system")
	if err != nil {
		return fmt.Errorf("load L0 runtime object spec: %w", err)
	}
	if err := fieldProvenanceInitializer.InitFieldProvenanceFromStandardObjectSpecs(
		fieldProvenanceInitializer.DefaultPlatformStandardFields(),
		systemSpec,
	); err != nil {
		return fmt.Errorf("initialize L0 field provenance: %w", err)
	}
	return nil
}

type activeInstalledPackRow struct {
	name                          string
	category                      string
	appliedPayloadHash            sql.NullString
	manifestFormatVersion         sql.NullString
	sourceKind                    sql.NullString
	sourceURI                     sql.NullString
	sourceRevision                sql.NullString
	runtimeObjectSpecDeclarations sql.NullString
	runtimeRelatedListRules       sql.NullString
	runtimeSearchScopeOrder       sql.NullString
	runtimeSearchAllDefaultFields sql.NullString
	runtimeFormulaFunctionBundles sql.NullString
	runtimeStartupShallow         sql.NullString
	hasRuntimeFormulaBundles      bool
	hasRuntimeStartupShallow      bool
}

func activeInstalledPackRows(ctx context.Context, executor persistence.Executor) ([]activeInstalledPackRow, error) {
	hasRuntimeDeclarationColumns, err := installedPackHasRuntimeDeclarationColumns(ctx, executor)
	if err != nil {
		return nil, err
	}
	hasRuntimeStartupShallowColumn := false
	hasRuntimeFormulaBundlesColumn := false
	query := `
		SELECT Name, Category, AppliedPayloadHash, ManifestFormatVersion, SourceKind, SourceURI, SourceRevision,
		       RuntimeObjectSpecDeclarations, RuntimeRelatedListRules, RuntimeSearchScopeOrder, RuntimeSearchAllDefaultFields
		FROM _System_InstalledPack
		WHERE Status = 'active' AND COALESCE(IsDeleted, 0) = 0
		ORDER BY Name`
	if hasRuntimeDeclarationColumns {
		hasRuntimeStartupShallowColumn, err = installedPackHasRuntimeStartupShallowDeclarationColumn(ctx, executor)
		if err != nil {
			return nil, err
		}
		hasRuntimeFormulaBundlesColumn, err = installedPackHasRuntimeFormulaFunctionBundlesColumn(ctx, executor)
		if err != nil {
			return nil, err
		}
		switch {
		case hasRuntimeStartupShallowColumn && hasRuntimeFormulaBundlesColumn:
			query = `
		SELECT Name, Category, AppliedPayloadHash, ManifestFormatVersion, SourceKind, SourceURI, SourceRevision,
		       RuntimeObjectSpecDeclarations, RuntimeRelatedListRules, RuntimeSearchScopeOrder, RuntimeSearchAllDefaultFields, RuntimeFormulaFunctionBundles, RuntimeStartupShallowDeclarations
		FROM _System_InstalledPack
		WHERE Status = 'active' AND COALESCE(IsDeleted, 0) = 0
		ORDER BY Name`
		case hasRuntimeStartupShallowColumn:
			query = `
		SELECT Name, Category, AppliedPayloadHash, ManifestFormatVersion, SourceKind, SourceURI, SourceRevision,
		       RuntimeObjectSpecDeclarations, RuntimeRelatedListRules, RuntimeSearchScopeOrder, RuntimeSearchAllDefaultFields, RuntimeStartupShallowDeclarations
		FROM _System_InstalledPack
		WHERE Status = 'active' AND COALESCE(IsDeleted, 0) = 0
		ORDER BY Name`
		case hasRuntimeFormulaBundlesColumn:
			query = `
		SELECT Name, Category, AppliedPayloadHash, ManifestFormatVersion, SourceKind, SourceURI, SourceRevision,
		       RuntimeObjectSpecDeclarations, RuntimeRelatedListRules, RuntimeSearchScopeOrder, RuntimeSearchAllDefaultFields, RuntimeFormulaFunctionBundles
		FROM _System_InstalledPack
		WHERE Status = 'active' AND COALESCE(IsDeleted, 0) = 0
		ORDER BY Name`
		}
	} else {
		query = `
		SELECT Name, Category, AppliedPayloadHash, ManifestFormatVersion, SourceKind, SourceURI, SourceRevision
		FROM _System_InstalledPack
		WHERE Status = 'active' AND COALESCE(IsDeleted, 0) = 0
		ORDER BY Name`
	}
	rows, err := executor.QueryContext(ctx, query)
	if err != nil {
		return nil, fmt.Errorf("query active installed packs: %w", err)
	}
	defer rows.Close()

	var out []activeInstalledPackRow
	for rows.Next() {
		var row activeInstalledPackRow
		if hasRuntimeDeclarationColumns {
			switch {
			case hasRuntimeStartupShallowColumn && hasRuntimeFormulaBundlesColumn:
				row.hasRuntimeStartupShallow = true
				row.hasRuntimeFormulaBundles = true
				err = rows.Scan(
					&row.name,
					&row.category,
					&row.appliedPayloadHash,
					&row.manifestFormatVersion,
					&row.sourceKind,
					&row.sourceURI,
					&row.sourceRevision,
					&row.runtimeObjectSpecDeclarations,
					&row.runtimeRelatedListRules,
					&row.runtimeSearchScopeOrder,
					&row.runtimeSearchAllDefaultFields,
					&row.runtimeFormulaFunctionBundles,
					&row.runtimeStartupShallow,
				)
			case hasRuntimeStartupShallowColumn:
				row.hasRuntimeStartupShallow = true
				err = rows.Scan(
					&row.name,
					&row.category,
					&row.appliedPayloadHash,
					&row.manifestFormatVersion,
					&row.sourceKind,
					&row.sourceURI,
					&row.sourceRevision,
					&row.runtimeObjectSpecDeclarations,
					&row.runtimeRelatedListRules,
					&row.runtimeSearchScopeOrder,
					&row.runtimeSearchAllDefaultFields,
					&row.runtimeStartupShallow,
				)
			case hasRuntimeFormulaBundlesColumn:
				row.hasRuntimeFormulaBundles = true
				err = rows.Scan(
					&row.name,
					&row.category,
					&row.appliedPayloadHash,
					&row.manifestFormatVersion,
					&row.sourceKind,
					&row.sourceURI,
					&row.sourceRevision,
					&row.runtimeObjectSpecDeclarations,
					&row.runtimeRelatedListRules,
					&row.runtimeSearchScopeOrder,
					&row.runtimeSearchAllDefaultFields,
					&row.runtimeFormulaFunctionBundles,
				)
			default:
				err = rows.Scan(
					&row.name,
					&row.category,
					&row.appliedPayloadHash,
					&row.manifestFormatVersion,
					&row.sourceKind,
					&row.sourceURI,
					&row.sourceRevision,
					&row.runtimeObjectSpecDeclarations,
					&row.runtimeRelatedListRules,
					&row.runtimeSearchScopeOrder,
					&row.runtimeSearchAllDefaultFields,
				)
			}
		} else {
			err = rows.Scan(
				&row.name,
				&row.category,
				&row.appliedPayloadHash,
				&row.manifestFormatVersion,
				&row.sourceKind,
				&row.sourceURI,
				&row.sourceRevision,
			)
		}
		if err != nil {
			return nil, fmt.Errorf("scan active installed pack: %w", err)
		}
		if hasRuntimeDeclarationColumns && !row.runtimeFormulaFunctionBundles.Valid {
			row.runtimeFormulaFunctionBundles = sql.NullString{String: "[]", Valid: true}
		}
		row.name = strings.TrimSpace(row.name)
		if row.name != "" {
			out = append(out, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate active installed packs: %w", err)
	}
	return out, nil
}

func InstalledPackHasRuntimeDeclarationColumns(ctx context.Context, db *sql.DB) (bool, error) {
	return installedPackHasRuntimeDeclarationColumns(ctx, db)
}

func installedPackHasRuntimeDeclarationColumns(ctx context.Context, executor persistence.Executor) (bool, error) {
	rows, err := executor.QueryContext(ctx, `
		SELECT COLUMN_NAME
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_SCHEMA = DATABASE()
		  AND TABLE_NAME = ?
		  AND COLUMN_NAME IN (?, ?, ?, ?)`,
		"_System_InstalledPack",
		"RuntimeObjectSpecDeclarations",
		"RuntimeRelatedListRules",
		"RuntimeSearchScopeOrder",
		"RuntimeSearchAllDefaultFields",
	)
	if err != nil {
		return false, fmt.Errorf("query installed-pack runtime declaration columns: %w", err)
	}
	defer rows.Close()

	found := map[string]bool{}
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			return false, fmt.Errorf("scan installed-pack runtime declaration column: %w", err)
		}
		found[strings.TrimSpace(name)] = true
	}
	if err := rows.Err(); err != nil {
		return false, fmt.Errorf("iterate installed-pack runtime declaration columns: %w", err)
	}
	return found["RuntimeObjectSpecDeclarations"] &&
		found["RuntimeRelatedListRules"] &&
		found["RuntimeSearchScopeOrder"] &&
		found["RuntimeSearchAllDefaultFields"], nil
}

func InstalledPackHasRuntimeStartupShallowDeclarationColumn(ctx context.Context, db *sql.DB) (bool, error) {
	return installedPackHasRuntimeStartupShallowDeclarationColumn(ctx, db)
}

func installedPackHasRuntimeStartupShallowDeclarationColumn(ctx context.Context, executor persistence.Executor) (bool, error) {
	var count int
	if err := executor.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_SCHEMA = DATABASE()
		  AND TABLE_NAME = ?
		  AND COLUMN_NAME = ?`,
		"_System_InstalledPack",
		"RuntimeStartupShallowDeclarations",
	).Scan(&count); err != nil {
		return false, fmt.Errorf("query installed-pack runtime startup-shallow declaration column: %w", err)
	}
	return count > 0, nil
}

func InstalledPackHasRuntimeFormulaFunctionBundlesColumn(ctx context.Context, db *sql.DB) (bool, error) {
	return installedPackHasRuntimeFormulaFunctionBundlesColumn(ctx, db)
}

func installedPackHasRuntimeFormulaFunctionBundlesColumn(ctx context.Context, executor persistence.Executor) (bool, error) {
	var count int
	if err := executor.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_SCHEMA = DATABASE()
		  AND TABLE_NAME = ?
		  AND COLUMN_NAME = ?`,
		"_System_InstalledPack",
		"RuntimeFormulaFunctionBundles",
	).Scan(&count); err != nil {
		return false, fmt.Errorf("query installed-pack runtime formula function bundles column: %w", err)
	}
	return count > 0, nil
}

func resolveActiveInstalledRuntimePack(ctx context.Context, db *sql.DB, row activeInstalledPackRow, formulaFunctionBundlesForValidation []formula.FunctionBundle, hasFormulaFunctionBundlesForValidation bool, registrations *ComponentKindRegistrationSet) (Pack, error) {
	var operationSourceOptions *PackSourceOptions
	if sourceOptions, ok := OperationPackSourceOptions(ctx); ok {
		operationSourceOptions = &sourceOptions
	}
	return ResolveInstalledPack(
		InstalledPackResolverInput{
			Name:                  row.name,
			Category:              Category(strings.TrimSpace(row.category)),
			AppliedPayloadHash:    row.appliedPayloadHash.String,
			ManifestFormatVersion: row.manifestFormatVersion.String,
			SourceKind:            row.sourceKind.String,
			SourceURI:             row.sourceURI.String,
			SourceRevision:        row.sourceRevision.String,
		},
		InstalledPackResolverOptions{
			AllowBundledFallback:                          false,
			PackSourceOptions:                             operationSourceOptions,
			RuntimeFormulaFunctionBundlesForValidation:    formulaFunctionBundlesForValidation,
			HasRuntimeFormulaFunctionBundlesForValidation: hasFormulaFunctionBundlesForValidation,
			ObjectSchemaResolver:                          sourceBackedVerifySchemaFallback(ctx, VerifyDeps{DB: db}),
			ComponentKindRegistrationSet:                  registrations,
		},
	)
}