package app

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"reflect"
	"slices"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/domain"
	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/core/secrets"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/access"
	"github.com/tidbcloud/AgentTeam/internal/store/app/installation"
	"github.com/tidbcloud/AgentTeam/internal/store/app/registry"
	"github.com/tidbcloud/AgentTeam/internal/store/model"
	"github.com/tidbcloud/AgentTeam/internal/store/serviceactor"
)

const (
	AppInstallationRequestKindInstall    = "install"
	AppInstallationRequestKindConfigure  = "configure"
	AppInstallationRequestKindConnect    = "connect"
	AppInstallationRequestKindDisconnect = "disconnect"
	AppInstallationRequestKindUninstall  = "uninstall"

	AppInstallationRequestStatusPending = "pending"
	// AppInstallationRequestStatusSetupReady is a verified, owner-bound GitHub
	// provider draft. Requester-mode drafts become approval requests only after
	// explicit promotion; administrator-mode drafts are consumed only by the
	// direct setup completion flow.
	AppInstallationRequestStatusSetupReady = "setup_ready"
	// AppInstallationRequestStatusSetupVerifying is an internal, short-lived
	// workspace lease held while recovery mutates the external GitHub webhook.
	// It is neither browser-visible nor an approval request.
	AppInstallationRequestStatusSetupVerifying = "setup_verifying"
	// AppInstallationRequestStatusSetupPatching is the durable point-of-no-return
	// snapshot entered immediately before provider PATCH. It has no TTL cleanup:
	// retries must retain the only encrypted copy of the webhook secret.
	AppInstallationRequestStatusSetupPatching = "setup_patching"
	AppInstallationRequestStatusApproved      = "approved"
	AppInstallationRequestStatusRejected      = "rejected"

	appInstallationAppReviewSnapshotKey = "_agentteam_app_review"
)

const AppInstallationRequestReviewStateCannotBeApplied = "cannot_be_applied"

type appInstallationRequestScanner interface {
	Scan(...any) error
}

func (store *Store) RequestAppInstallationForUser(ctx context.Context, userID string, workspaceID string, params RequestAppInstallationParams) (AppInstallationRequestView, error) {
	return store.requestAppInstallationForUser(ctx, userID, workspaceID, params)
}

// ListPendingAppInstallationRequestsForUser returns only the authenticated
// workspace member's own pending or provider-patching requests. This is the browser-safe source of
// truth used to keep install/configure CTAs blocked after their request modal
// closes; another member's proposal must never be discoverable through it.
func (store *Store) ListPendingAppInstallationRequestsForUser(ctx context.Context, userID string, workspaceID string) ([]AppInstallationRequestView, error) {
	workspaceMember, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return nil, err
	}

	// Ordinary members keep the requester-privacy invariant (own rows
	// only); workspace admins additionally review every pending share
	// submission. Legacy install/configure proposals stay member-private.
	query := appInstallationRequestSelectSQL() + `
WHERE req.workspace_id = $1
  AND req.service_kind = $2
  AND req.status IN ($4, $5)
  AND req.approval_required = TRUE
  AND (
    req.requested_by_member_id = $3
    OR ($6 AND req.request_kind = 'share')
  )
ORDER BY req.created_at DESC, req.id DESC`
	rows, err := store.db.Query(
		ctx,
		query,
		strings.TrimSpace(workspaceID),
		serviceactor.KindApp,
		strings.TrimSpace(workspaceMember.Member.ID),
		AppInstallationRequestStatusPending,
		AppInstallationRequestStatusSetupPatching,
		access.HasWorkspaceAdminRole(domain.WorkspaceRole(workspaceMember.WorkspaceRole)),
	)
	if err != nil {
		return nil, fmt.Errorf("list pending App installation requests: %w", err)
	}
	defer rows.Close()

	requests := make([]AppInstallationRequestView, 0)
	for rows.Next() {
		request, scanErr := scanAppInstallationRequest(rows)
		if scanErr != nil {
			return nil, fmt.Errorf("scan pending App installation request: %w", scanErr)
		}
		requests = append(requests, request)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate pending App installation requests: %w", err)
	}
	return requests, nil
}

func (store *Store) requestAppInstallationForUser(
	ctx context.Context,
	userID string,
	workspaceID string,
	params RequestAppInstallationParams,
) (AppInstallationRequestView, error) {
	workspaceMember, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return AppInstallationRequestView{}, err
	}
	if strings.TrimSpace(params.GitHubSetupProposalID) != "" {
		// Promotion performs its own role-aware revalidation after locking the
		// proposal. That lets an elevated requester delete a stale private draft
		// and restart through the administrator-direct path instead of leaving an
		// encrypted setup proposal behind.
		return store.promoteVerifiedGitHubSetupProposalForUser(ctx, userID, workspaceID, workspaceMember, params)
	}
	if err := requireAppApprovalRequester(workspaceMember); err != nil {
		return AppInstallationRequestView{}, err
	}
	return store.requestAppInstallationForWorkspaceMember(ctx, userID, workspaceID, workspaceMember, params)
}

// requestAppInstallationForWorkspaceMember accepts the endpoint's early
// membership snapshot only as an expected identity. Every durable request
// transaction locks and reloads that canonical membership before using it.
// Keeping this boundary separate also lets race regression tests exercise a
// stale member snapshot without introducing production-only timing hooks.
func (store *Store) requestAppInstallationForWorkspaceMember(
	ctx context.Context,
	userID string,
	workspaceID string,
	workspaceMember access.HumanWorkspaceMember,
	params RequestAppInstallationParams,
) (AppInstallationRequestView, error) {
	explicitRequestKind := strings.TrimSpace(params.RequestKind)
	if strings.TrimSpace(params.GitHubSetupProposalID) != "" ||
		(trimmedInstallRequestKind(explicitRequestKind) && requestTargetsGitHubApp(params.AppID, params.AppSlug)) {
		return store.promoteVerifiedGitHubSetupProposalForUser(ctx, userID, workspaceID, workspaceMember, params)
	}
	if isProviderConnectionRequestKind(explicitRequestKind) {
		if requestTargetsGitHubApp(params.AppID, params.AppSlug) {
			if explicitRequestKind == AppInstallationRequestKindConnect {
				return AppInstallationRequestView{}, badRequest("GitHub connections must be requested through the GitHub installation flow.")
			}
			return store.requestGitHubDisconnectForUser(ctx, userID, workspaceID, workspaceMember, params)
		}
		return store.requestGiteaConnectionChangeForUser(ctx, userID, workspaceID, workspaceMember, params)
	}
	if explicitRequestKind != "" && explicitRequestKind != AppInstallationRequestKindInstall && explicitRequestKind != AppInstallationRequestKindConfigure && explicitRequestKind != AppInstallationRequestKindUninstall {
		return AppInstallationRequestView{}, badRequest("requestKind must be install, configure, connect, disconnect, or uninstall")
	}
	trimmedInstallationID := strings.TrimSpace(params.InstallationID)
	if explicitRequestKind == AppInstallationRequestKindInstall && trimmedInstallationID != "" {
		return AppInstallationRequestView{}, badRequest("installationId must be empty for install requests")
	}
	if explicitRequestKind == AppInstallationRequestKindConfigure && trimmedInstallationID == "" {
		return AppInstallationRequestView{}, badRequest("installationId is required for configure requests")
	}
	if explicitRequestKind == AppInstallationRequestKindUninstall {
		if trimmedInstallationID == "" {
			return AppInstallationRequestView{}, badRequest("installationId is required for uninstall requests")
		}
	}
	var installPrepared preparedAppInstallation

	var requestID string
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		now := time.Now().UTC()
		txStore := store.serviceActorStore().InTx(tx)
		var definition serviceactor.Definition
		var currentRequester access.HumanWorkspaceMember
		definitionIDHint := ""
		if trimmedInstallationID == "" {
			installableApp, err := installation.LoadWorkspaceInstallableApp(
				ctx,
				tx,
				workspaceID,
				params.AppID,
				params.AppSlug,
			)
			if err != nil {
				return err
			}
			installPrepared.app = installableApp
			installPrepared.version.Version = installableApp.LatestVersion
			definitionIDHint = strings.TrimSpace(installableApp.ID)
		} else {
			if err := tx.QueryRow(ctx, `
SELECT service_definition_id
FROM service_installations
WHERE id = $1
  AND workspace_id = $2
  AND service_kind = $3
  AND status = $4
LIMIT 1
`, trimmedInstallationID, strings.TrimSpace(workspaceID), serviceactor.KindApp, serviceactor.StatusActive).Scan(&definitionIDHint); err != nil {
				if coredb.IsNotFound(err) {
					return ErrNotFound
				}
				return fmt.Errorf("load App definition hint for installation request: %w", err)
			}
		}
		definitionIDHint = strings.TrimSpace(definitionIDHint)
		if definitionIDHint == "" {
			return conflict("App installation definition changed.")
		}
		// A canonical App-definition lock serializes request admission with direct
		// mutations and decisions without treating an optional pending row as a
		// singleton. Multiple members may submit independent proposals for the same
		// App while every proposal retains its own immutable base snapshot.
		definitionLocked, err := installation.LockExistingAppInstallationMutationScope(ctx, tx, definitionIDHint)
		if err != nil {
			return err
		}
		// Existing definitions follow the global definition -> membership lock
		// order used by direct mutations and request decisions. A genuinely first
		// definition has no canonical definition row yet, so membership is locked
		// first and no definition write can occur until the requester is proven to
		// still be an ordinary member.
		currentRequester, err = store.revalidateAppApprovalRequesterForUpdate(
			ctx,
			tx,
			userID,
			workspaceID,
			workspaceMember,
		)
		if err != nil {
			return err
		}
		if trimmedInstallationID == "" {
			// Enforce App-manager authority using the target's current definition,
			// before validating caller-supplied configuration. A peer who retained
			// a hidden App id must observe the same NotFound for every payload.
			if err := authorizeAppInstallationRequestForMember(
				ctx,
				tx,
				workspaceID,
				installPrepared,
				currentRequester.Member.ID,
			); err != nil {
				return err
			}
			if !registry.IsProductApp(installPrepared.app.ID, installPrepared.app.Slug) {
				return badRequest("Custom Apps must be created through the App setup flow.")
			}
			if err := requireGenericMemberAppInstallAllowed(
				ctx,
				tx,
				installPrepared.app.ID,
				AppInstallationRequestKindInstall,
			); err != nil {
				return err
			}
			manifestConfig := params.Config
			if requestTargetsGiteaApp(params.AppID, params.AppSlug) {
				manifestConfig = nil
			}
			installPrepared, err = prepareAppRecordAtVersion(
				ctx,
				tx,
				installPrepared.app,
				installPrepared.app.LatestVersion,
				manifestConfig,
			)
			if err != nil {
				return err
			}
		}
		if trimmedInstallationID == "" {
			definition, err = store.syncAppServiceDefinition(ctx, tx, installPrepared.app.ID, now)
			if err != nil {
				return err
			}
			definitionIDHint = definition.ID
		}
		if !definitionLocked {
			if err := installation.LockAppInstallationMutationScope(ctx, tx, definitionIDHint); err != nil {
				return err
			}
		}
		if isGitHubApp(definitionIDHint) {
			if err := deleteExpiredGitHubSetupVerificationsInTx(
				ctx, tx, workspaceID, time.Now().UTC().Add(-githubSetupVerificationTTL),
			); err != nil {
				return err
			}
			if err := requireNoGitHubSetupVerificationInTx(ctx, tx, workspaceID); err != nil {
				return err
			}
		}

		requestKind := AppInstallationRequestKindInstall
		targetInstallationID := ""
		prepared := installPrepared
		baseConfig := map[string]any{}
		requestedConfig := map[string]any{}
		if trimmedInstallationID != "" {
			installation, err := txStore.LoadInstallationForUpdate(ctx, trimmedInstallationID)
			if err != nil {
				return err
			}
			if strings.TrimSpace(installation.WorkspaceID) != strings.TrimSpace(workspaceID) ||
				strings.TrimSpace(installation.ServiceKind) != serviceactor.KindApp ||
				strings.TrimSpace(installation.Status) != serviceactor.StatusActive {
				return ErrNotFound
			}
			if strings.TrimSpace(installation.OwnershipKind) == serviceactor.OwnershipKindPersonal {
				// Personal installations leave no surface for the legacy
				// workspace request flow: their lifecycle is owner-managed.
				return badRequest("personal installations are managed by their owner.")
			}
			var admissionPrepared preparedAppInstallation
			if err := tx.QueryRow(ctx, `
SELECT id, slug, name
FROM service_definitions
WHERE id = $1
LIMIT 1
`, strings.TrimSpace(installation.ServiceDefinitionID)).Scan(
				&admissionPrepared.app.ID,
				&admissionPrepared.app.Slug,
				&admissionPrepared.app.Name,
			); err != nil {
				if coredb.IsNotFound(err) {
					return ErrNotFound
				}
				return fmt.Errorf("load App request admission target: %w", err)
			}
			admissionPrepared.version.Version = strings.TrimSpace(installation.InstalledVersion)
			if err := authorizeAppInstallationRequestForMember(
				ctx,
				tx,
				workspaceID,
				admissionPrepared,
				currentRequester.Member.ID,
			); err != nil {
				return err
			}
			if !registry.IsProductApp(admissionPrepared.app.ID, admissionPrepared.app.Slug) {
				return badRequest("Custom Apps must be managed directly by an App administrator or workspace administrator.")
			}
			if isGitHubApp(installation.ServiceDefinitionID) && explicitRequestKind != AppInstallationRequestKindUninstall {
				return badRequest("GitHub configuration must be changed through the GitHub setup flow.")
			}
			if explicitRequestKind == AppInstallationRequestKindUninstall &&
				(len(params.Config) > 0 || params.ExpectedBaseConfig != nil || strings.TrimSpace(params.ConnectionTarget) != "" || strings.TrimSpace(params.ConnectionID) != "") {
				return badRequest("uninstall requests must not include configuration or connection fields")
			}
			targetInstallationID = installation.ID
			if explicitRequestKind == AppInstallationRequestKindUninstall {
				requestKind = AppInstallationRequestKindUninstall
				prepared, err = prepareInstalledAppConfigurationAtVersion(
					ctx,
					tx,
					installation.ServiceDefinitionID,
					installation.InstalledVersion,
					decodeJSONObjectMap(installation.Config),
				)
				if err != nil {
					return err
				}
				definition, err = store.syncAppServiceDefinition(ctx, tx, prepared.app.ID, now)
				if err != nil {
					return err
				}
				if strings.TrimSpace(definition.ID) != strings.TrimSpace(installation.ServiceDefinitionID) {
					return conflict("App installation definition changed.")
				}
				if appID := strings.TrimSpace(params.AppID); appID != "" && appID != strings.TrimSpace(prepared.app.ID) {
					return conflict("App installation target changed.")
				}
				if appSlug := strings.TrimSpace(params.AppSlug); appSlug != "" && !strings.EqualFold(appSlug, strings.TrimSpace(prepared.app.Slug)) {
					return conflict("App installation target changed.")
				}
				baseConfig, err = store.appUninstallRequestBaseInTx(ctx, tx, installation)
				if err != nil {
					return err
				}
			} else {
				requestKind = AppInstallationRequestKindConfigure
				manifestConfig := params.Config
				if isGiteaApp(installation.ServiceDefinitionID) {
					manifestConfig = nil
				} else {
					if err := validateProviderAppManifestConfig(installation.ServiceDefinitionID, params.Config); err != nil {
						return err
					}
					if params.ExpectedBaseConfig == nil {
						return badRequest("expectedBaseConfig is required for App configuration requests")
					}
					if !reflect.DeepEqual(
						registry.JSONObjectOrEmpty(decodeJSONObjectMap(installation.Config)),
						registry.JSONObjectOrEmpty(params.ExpectedBaseConfig),
					) {
						return conflict("App configuration changed after it was opened. Reload and submit a new configuration request.")
					}
				}
				prepared, err = prepareInstalledAppConfigurationAtVersion(
					ctx,
					tx,
					installation.ServiceDefinitionID,
					installation.InstalledVersion,
					manifestConfig,
				)
				if err != nil {
					return err
				}
				definition, err = store.syncAppServiceDefinition(ctx, tx, prepared.app.ID, now)
				if err != nil {
					return err
				}
				if strings.TrimSpace(definition.ID) != strings.TrimSpace(installation.ServiceDefinitionID) {
					return conflict("App installation definition changed.")
				}
				if isGiteaApp(definition.ID) {
					baseConfig, requestedConfig, err = store.prepareGiteaAppConfigRequestInTx(ctx, tx, workspaceID, requestKind, params.Config)
					if err != nil {
						return err
					}
					if err := validateGiteaAppConfigExpectedBase(params.ExpectedBaseConfig, baseConfig); err != nil {
						return err
					}
				} else {
					baseConfig = decodeJSONObjectMap(installation.Config)
					requestedConfig = decodeJSONObjectMap(prepared.configJSON)
				}
			}
		} else {
			existingInstallation, found, err := txStore.LoadInstallationByInstanceForUpdate(ctx, workspaceID, definition.ID, serviceactor.DefaultInstanceKey)
			if err != nil {
				return err
			}
			if found && strings.TrimSpace(existingInstallation.Status) == serviceactor.StatusActive {
				return conflict("App is already installed. Submit a configuration request instead.")
			}
			if isGiteaApp(definition.ID) {
				baseConfig, requestedConfig, err = store.prepareGiteaAppConfigRequestInTx(ctx, tx, workspaceID, requestKind, params.Config)
				if err != nil {
					return err
				}
			} else {
				var appReview *AppInstallationReviewSnapshot
				if !registry.IsProductApp(prepared.app.ID, prepared.app.Slug) {
					appReview = appInstallationReviewSnapshotFromVersion(prepared.version)
					if appReview != nil {
						attribution, attributed, loadErr := loadAppManagementAttribution(
							ctx, tx, workspaceID, prepared.app.ID, false,
						)
						if loadErr != nil {
							return loadErr
						}
						if !attributed {
							return ErrNotFound
						}
						appReview.CatalogVisibility = attribution.CatalogVisibility
					}
				}
				baseConfig = appInstallationReviewBaseConfig(appReview)
				requestedConfig = decodeJSONObjectMap(prepared.configJSON)
			}
		}

		if err := authorizeAppInstallationRequestForMember(
			ctx,
			tx,
			workspaceID,
			prepared,
			currentRequester.Member.ID,
		); err != nil {
			return err
		}
		if strings.TrimSpace(definition.ID) != definitionIDHint {
			return conflict("App installation definition changed.")
		}
		requestID, err = identifiers.NewEntityID("svcinstreq")
		if err != nil {
			return fmt.Errorf("generate service installation request id: %w", err)
		}
		requestedScopesJSON, err := model.MarshalJSONObject("service installation request scopes", registry.SortedUniqueScopes(prepared.version.Scopes))
		if err != nil {
			return fmt.Errorf("encode service installation request scopes: %w", err)
		}
		baseConfigJSON, err := model.MarshalJSONObject("service installation request base config", baseConfig)
		if err != nil {
			return fmt.Errorf("encode service installation request base config: %w", err)
		}
		requestedConfigJSON, err := model.MarshalJSONObject("service installation request config", requestedConfig)
		if err != nil {
			return fmt.Errorf("encode service installation request config: %w", err)
		}
		if _, err := tx.Exec(ctx, `
INSERT INTO service_installation_requests (
    id, workspace_id, service_definition_id, service_kind, request_kind,
    requested_version, requested_config, base_config, requested_scopes,
    status, approval_required, request_reason, requested_by_member_id, decision_reason,
    service_installation_id, created_at, updated_at
) VALUES (
    $1, $2, $3, $4, $5,
    $6, $7, $8, $9,
    $10, TRUE, $11, $12, '',
    $13, $14, $14
)
`, requestID, strings.TrimSpace(workspaceID), definition.ID, serviceactor.KindApp, requestKind, prepared.version.Version, requestedConfigJSON, baseConfigJSON, requestedScopesJSON, AppInstallationRequestStatusPending, strings.TrimSpace(params.Reason), currentRequester.Member.ID, nullIfBlankString(targetInstallationID), now); err != nil {
			return fmt.Errorf("create service installation request: %w", err)
		}
		return nil
	}); err != nil {
		return AppInstallationRequestView{}, err
	}
	return store.appInstallationRequestByID(ctx, workspaceID, requestID)
}

func trimmedInstallRequestKind(kind string) bool {
	trimmed := strings.TrimSpace(kind)
	return trimmed == "" || trimmed == AppInstallationRequestKindInstall
}

// requireGenericMemberAppInstallAllowed keeps feature-owned setup out of the
// generic default-instance approval path. Non-install operations retain their
// existing routing and lifecycle.
func requireGenericMemberAppInstallAllowed(
	ctx context.Context,
	queryer coredb.QueryRower,
	appID string,
	requestKind string,
) error {
	if trimmedInstallRequestKind(requestKind) {
		return requireGenericAppSetupAllowed(ctx, queryer, appID)
	}
	return nil
}

// authorizeAppInstallationRequest is the closed catalog/install boundary.
// Public installation is restricted to exact product identities. Every other
// request must target a supported App attributed to this workspace.
func authorizeAppInstallationRequest(
	ctx context.Context,
	queryer coredb.QueryRower,
	workspaceID string,
	prepared preparedAppInstallation,
) error {
	appID := strings.TrimSpace(prepared.app.ID)
	appSlug := strings.TrimSpace(prepared.app.Slug)
	var kind string
	var visibility string
	var manifestJSON []byte
	if err := queryer.QueryRow(ctx, `
SELECT COALESCE(JSON_UNQUOTE(JSON_EXTRACT(COALESCE(app.config, JSON_OBJECT()), '$.appKind')), ''), app.visibility, version.manifest
FROM service_definitions app
JOIN extension_definition_versions version
  ON version.service_definition_id = app.id
 AND version.version = $2
WHERE app.id = $1
LIMIT 1
`, appID, strings.TrimSpace(prepared.version.Version)).Scan(&kind, &visibility, &manifestJSON); err != nil {
		if coredb.IsNotFound(err) {
			return ErrNotFound
		}
		return fmt.Errorf("load App installation visibility: %w", err)
	}
	if strings.TrimSpace(visibility) == "public" {
		if registry.IsProductApp(appID, appSlug) {
			return nil
		}
	}
	if strings.TrimSpace(kind) != "third_party" {
		return ErrNotFound
	}
	if _, supported := registry.SupportedExternalMentionRuntimeCapability(appSlug, kind, manifestJSON); !supported {
		return ErrNotFound
	}
	associated, err := appDefinitionAssociatedWithWorkspace(ctx, queryer, workspaceID, appID)
	if err != nil {
		return err
	}
	if associated {
		return nil
	}
	// Do not reveal whether another workspace owns this external definition.
	return ErrNotFound
}

// authorizeAppInstallationRequestForMember adds the manager-scoped admission
// boundary for workspace Apps. Catalog filtering is not an authorization
// boundary: a peer can retain an App or installation id after it disappears
// from their list. Product Apps (including Gitea) remain workspace-wide, while
// owner/admin direct commands intentionally call the base authorization above
// and bypass this member-only manager check.
func authorizeAppInstallationRequestForMember(
	ctx context.Context,
	queryer coredb.Queryer,
	workspaceID string,
	prepared preparedAppInstallation,
	requesterMemberID string,
) error {
	if err := authorizeAppInstallationRequest(ctx, queryer, workspaceID, prepared); err != nil {
		return err
	}
	if registry.IsProductApp(prepared.app.ID, prepared.app.Slug) {
		return nil
	}
	var kind string
	var manifestJSON []byte
	if err := queryer.QueryRow(ctx, `
SELECT COALESCE(JSON_UNQUOTE(JSON_EXTRACT(COALESCE(app.config, JSON_OBJECT()), '$.appKind')), ''), version.manifest
FROM service_definitions app
JOIN extension_definition_versions version
  ON version.service_definition_id = app.id
 AND version.version = $2
WHERE app.id = $1
LIMIT 1
`, strings.TrimSpace(prepared.app.ID), strings.TrimSpace(prepared.version.Version)).Scan(&kind, &manifestJSON); err != nil {
		if coredb.IsNotFound(err) {
			return ErrNotFound
		}
		return fmt.Errorf("load App manager admission capability: %w", err)
	}
	if _, supported := registry.SupportedExternalMentionRuntimeCapability(prepared.app.Slug, kind, manifestJSON); !supported {
		return ErrNotFound
	}
	managed, err := hasActiveAppManager(
		ctx,
		queryer,
		workspaceID,
		prepared.app.ID,
		requesterMemberID,
		false,
	)
	if err != nil {
		return err
	}
	if !managed {
		// Do not reveal whether the App belongs to another member or has no
		// active manager assignment.
		return ErrNotFound
	}
	return nil
}

func (store *Store) ApproveAppInstallationRequestForUser(ctx context.Context, userID string, workspaceID string, requestID string, params DecideAppInstallationRequestParams) (AppInstallationRequestView, error) {
	approver, err := store.loadAuthorizedHumanWorkspaceMember(ctx, store.db, userID, workspaceID, serviceInstallationApproveAction)
	if err != nil {
		return AppInstallationRequestView{}, err
	}
	trimmedRequestID := strings.TrimSpace(requestID)
	if trimmedRequestID == "" {
		return AppInstallationRequestView{}, badRequest("requestId is required")
	}
	request, err := store.appInstallationRequestByID(ctx, workspaceID, trimmedRequestID)
	if err != nil {
		return AppInstallationRequestView{}, err
	}
	if !request.ApprovalRequired {
		return AppInstallationRequestView{}, conflict("This App operation is not an approval request.")
	}
	if strings.TrimSpace(request.Status) == AppInstallationRequestStatusPending {
		if err := requireGenericMemberAppInstallAllowed(
			ctx,
			store.db,
			request.AppID,
			request.RequestKind,
		); err != nil {
			return AppInstallationRequestView{}, err
		}
	}
	if isGitHubInitialSetupApprovalRequest(request) {
		return store.approveGitHubInitialSetupRequestForUser(
			ctx, userID, workspaceID, trimmedRequestID, params,
		)
	}
	if strings.TrimSpace(request.Status) == AppInstallationRequestStatusPending {
		settledRequest, settled, err := store.SettlePrivilegedAppInstallationRequest(ctx, workspaceID, trimmedRequestID)
		if err != nil {
			return AppInstallationRequestView{}, err
		}
		request = settledRequest
		if settled {
			// A promoted request is withdrawn, never auto-executed. Returning its
			// terminal truth lets API/Approval callers converge without applying
			// the stale proposal.
			return request, nil
		}
	}
	switch strings.TrimSpace(request.RequestKind) {
	case AppInstallationRequestKindConnect:
		if isGitHubApp(request.AppID) {
			return store.approveGitHubConnectRequestForUser(ctx, userID, workspaceID, trimmedRequestID, approver.Member.ID, params)
		}
		return store.approveGiteaConnectRequestForUser(ctx, userID, workspaceID, trimmedRequestID, approver.Member.ID, params)
	case AppInstallationRequestKindDisconnect:
		if isGitHubApp(request.AppID) {
			return store.approveGitHubDisconnectRequestForUser(ctx, userID, workspaceID, trimmedRequestID, approver.Member.ID, params)
		}
		return store.approveGiteaDisconnectRequestForUser(ctx, userID, workspaceID, trimmedRequestID, approver.Member.ID, params)
	}

	shouldUnblockGiteaSubscriptions := false
	var approvedUninstall *appInstallationUninstallResult
	privilegedRequestSettled := false
	cleanupCtx, avatarCleanup := withAppAvatarMediaCleanup(ctx)
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		req, err := loadAppInstallationRequestForUpdate(ctx, tx, workspaceID, trimmedRequestID)
		if err != nil {
			return err
		}
		if isGitHubApp(req.AppID) {
			if err := deleteExpiredGitHubSetupVerificationsInTx(
				ctx, tx, workspaceID, time.Now().UTC().Add(-githubSetupVerificationTTL),
			); err != nil {
				return err
			}
			if err := requireNoGitHubSetupVerificationInTx(ctx, tx, workspaceID); err != nil {
				return err
			}
		}
		if !req.ApprovalRequired {
			return conflict("This App operation is not an approval request.")
		}
		if strings.TrimSpace(req.Status) == AppInstallationRequestStatusPending {
			if err := requireGenericMemberAppInstallAllowed(
				ctx,
				tx,
				req.AppID,
				req.RequestKind,
			); err != nil {
				return err
			}
		}
		privilegedRequestSettled, err = settlePrivilegedAppInstallationRequestInTx(cleanupCtx, tx, req, time.Now().UTC())
		if err != nil || privilegedRequestSettled {
			return err
		}
		currentApprover, err := store.loadAuthorizedHumanWorkspaceMemberForUpdate(ctx, tx, userID, workspaceID, serviceInstallationApproveAction)
		if err != nil {
			return err
		}
		if strings.TrimSpace(currentApprover.Member.ID) != strings.TrimSpace(approver.Member.ID) {
			return conflict("App change approver membership changed.")
		}
		switch req.Status {
		case AppInstallationRequestStatusApproved:
			return nil
		case AppInstallationRequestStatusRejected:
			return conflict("App installation request was already rejected.")
		case AppInstallationRequestStatusPending:
		default:
			return conflict("App change request has an invalid state.")
		}
		if err := requireAppInstallationRequestApplicable(req); err != nil {
			return err
		}
		requester, err := store.loadAuthorizedHumanWorkspaceMemberForUpdate(ctx, tx, req.RequestedByUserID, workspaceID, workspaceAccessAction)
		if err != nil {
			// The proposal is bound to the requester's current workspace
			// membership. Once that membership is no longer eligible, retrying
			// the same approval cannot make the immutable proposal valid again.
			return markAppInstallationApprovalStaleIfDeterministic(err)
		}
		if strings.TrimSpace(requester.Member.ID) != strings.TrimSpace(req.RequestedByMemberID) {
			return markAppInstallationApprovalStale(conflict("App change requester membership changed. Submit a new request."))
		}

		requestKind := strings.TrimSpace(req.RequestKind)
		giteaRequest := isGiteaApp(req.AppID)
		githubRequest := isGitHubApp(req.AppID)
		providerConfigRequest := giteaRequest || githubRequest
		var prepared preparedAppInstallation
		var installationID string
		uninstallRequested := false
		switch requestKind {
		case AppInstallationRequestKindInstall:
			// Request admission already validated that the requester could discover
			// this App. Approval revalidates the immutable App/version/config snapshot
			// by ID without borrowing creator- or requester-specific management
			// authority.
			manifestConfig := req.RequestedConfig
			if providerConfigRequest {
				// Provider credentials are an installation-request concern,
				// not App manifest config. The App installation itself therefore
				// remains at the canonical empty config.
				manifestConfig = nil
			}
			prepared, err = prepareInstalledAppConfigurationAtVersion(
				ctx,
				tx,
				req.AppID,
				req.RequestedVersion,
				manifestConfig,
			)
			if err == nil {
				// Revalidate current catalog admission in the decision transaction
				// before materializing the installation.
				err = authorizeAppInstallationRequest(ctx, tx, workspaceID, prepared)
			}
			err = markAppInstallationApprovalStaleIfDeterministic(err)
		case AppInstallationRequestKindConfigure:
			approvalRequest := req
			if giteaRequest {
				// Provider snapshots are intentionally stored outside the generic
				// manifest config. Revalidate the App installation against {} while
				// the provider helper performs the raw provider CAS below.
				approvalRequest.BaseConfig = map[string]any{}
				approvalRequest.RequestedConfig = map[string]any{}
			}
			installationID, prepared, err = store.prepareAppConfigurationApproval(ctx, tx, workspaceID, approvalRequest)
		case AppInstallationRequestKindUninstall:
			installationID = strings.TrimSpace(req.InstallationID)
			if installationID == "" {
				// The installation FK is SET NULL when another approved uninstall
				// wins. Preserve the immutable target in the base snapshot so this
				// proposal can still deterministically report a stale conflict.
				installationID = requestSnapshotString(appInstallationRequestStoredBaseConfig(req), appUninstallBaseInstallationIDKey)
			}
			manifestConfig, configFound := appUninstallRequestInstallationConfig(req)
			if installationID == "" || !configFound {
				return markAppInstallationApprovalStale(conflict("App installation changed. Submit a new uninstall request."))
			}
			prepared, err = prepareInstalledAppConfigurationAtVersion(
				ctx,
				tx,
				req.AppID,
				req.RequestedVersion,
				manifestConfig,
			)
			uninstallRequested = true
		default:
			return markAppInstallationApprovalStale(conflict("App change request has an invalid operation."))
		}
		if err != nil {
			return markAppInstallationApprovalStaleIfDeterministic(err)
		}
		if !slices.Equal(registry.SortedUniqueScopes(prepared.version.Scopes), registry.SortedUniqueScopes(req.RequestedScopes)) {
			return markAppInstallationApprovalStale(conflict("App change request scopes changed. Submit a new request."))
		}
		initialCredentialsEligible := false
		approvedAppCatalogVisibility := ""
		if requestKind == AppInstallationRequestKindInstall {
			initialCredentialsEligible, err = workspaceAppInitialCredentialEligible(ctx, tx, req.WorkspaceID, req.AppID)
			if err != nil {
				return err
			}
			if initialCredentialsEligible {
				approvedAppCatalogVisibility, err = registry.NormalizeCatalogVisibility(req.AppCatalogVisibility, true)
				if err != nil {
					return markAppInstallationApprovalStale(conflict("App visibility changed. Submit a new App creation request."))
				}
				if _, attributed, loadErr := loadAppManagementAttribution(
					ctx, tx, workspaceID, req.AppID, true,
				); loadErr != nil {
					return loadErr
				} else if !attributed {
					return markAppInstallationApprovalStale(conflict("App definition changed. Submit a new App creation request."))
				}
				pinnedReview := appInstallationReviewSnapshotFromVersion(prepared.version)
				if pinnedReview == nil ||
					!slices.Equal(normalizedAppReviewTransports(req.AppRuntimeTransports), pinnedReview.RuntimeTransports) ||
					strings.TrimSpace(req.AppCallbackURL) != strings.TrimSpace(pinnedReview.CallbackURL) {
					return markAppInstallationApprovalStale(conflict("App connection details changed. Submit a new App creation request."))
				}
			} else if len(normalizedAppReviewTransports(req.AppRuntimeTransports)) > 0 || strings.TrimSpace(req.AppCallbackURL) != "" {
				return markAppInstallationApprovalStale(conflict("App definition changed. Submit a new App creation request."))
			}
		}
		now := time.Now().UTC()
		if requestKind == AppInstallationRequestKindInstall {
			// The requester remains the installation initiator for audit attribution;
			// only workspace admins receive installation-management authority.
			installationID, err = store.installPreparedAppInTx(ctx, tx, req.RequestedByUserID, workspaceID, req.RequestedByMemberID, prepared, now)
			if err != nil {
				return markAppInstallationApprovalStaleIfDeterministic(err)
			}
			if initialCredentialsEligible {
				if err := updateWorkspaceAppCatalogVisibilityInTx(
					ctx, tx, workspaceID, req.AppID, approvedAppCatalogVisibility, now,
				); err != nil {
					return err
				}
			}
			// Callback Apps become externally reachable as soon as this
			// transaction commits. Keep the active installation off the known
			// empty HMAC key by installing an invisible random hold secret now;
			// the initial credential claim rotates it before returning plaintext.
			if initialCredentialsEligible && containsTrimmedString(req.AppRuntimeTransports, serviceactor.RuntimeTransportKindCallback) {
				holdSecret, err := secrets.NewToken(32)
				if err != nil {
					return fmt.Errorf("generate approved App callback hold secret: %w", err)
				}
				if err := store.setAppCallbackSigningSecretInTx(ctx, tx, req.AppID, holdSecret, now); err != nil {
					return err
				}
			}
		} else if uninstallRequested {
			uninstallResult, uninstallErr := store.uninstallAppInstallationInTx(
				cleanupCtx,
				tx,
				workspaceID,
				installationID,
				req.AppID,
				userID,
				currentApprover.Member.ID,
				&req,
			)
			if uninstallErr != nil {
				return markAppInstallationApprovalStaleIfDeterministic(uninstallErr)
			}
			approvedUninstall = &uninstallResult
			installationID = ""
		} else if !providerConfigRequest {
			if err := applyPreparedAppConfigurationInTx(ctx, tx, workspaceID, installationID, prepared, now); err != nil {
				return markAppInstallationApprovalStaleIfDeterministic(err)
			}
		}
		if giteaRequest && !uninstallRequested {
			if err := store.applyApprovedGiteaAppConfigInTx(ctx, tx, workspaceID, userID, req, now); err != nil {
				return markAppInstallationApprovalStaleIfDeterministic(err)
			}
			shouldUnblockGiteaSubscriptions = true
		}
		if githubRequest && !uninstallRequested {
			if requestKind != AppInstallationRequestKindInstall {
				return markAppInstallationApprovalStale(conflict("GitHub provider configuration can only be activated by an install request."))
			}
			if err := store.applyApprovedGitHubAppConfigInTx(ctx, tx, workspaceID, userID, req, now); err != nil {
				return markAppInstallationApprovalStaleIfDeterministic(err)
			}
		}
		resolvedBaseConfigJSON, err := model.MarshalJSONObject("resolved service installation request base config", appInstallationRequestResolvedBaseConfig(req))
		if err != nil {
			return fmt.Errorf("redact approved service installation request base config: %w", err)
		}
		resolvedRequestedConfig, err := appInstallationRequestTerminalRequestedConfig(req)
		if err != nil {
			return fmt.Errorf("resolve approved service installation request config: %w", err)
		}
		resolvedRequestedConfigJSON, err := model.MarshalJSONObject("resolved service installation request config", resolvedRequestedConfig)
		if err != nil {
			return fmt.Errorf("redact approved service installation request config: %w", err)
		}
		result, err := tx.Exec(ctx, `
UPDATE service_installation_requests
SET status = $1,
    decided_by_member_id = $2,
    decision_reason = $3,
    service_installation_id = $4,
    base_config = $5,
    requested_config = $6,
    updated_at = $7,
    decided_at = $7
WHERE id = $8
  AND workspace_id = $9
  AND status = $10
`, AppInstallationRequestStatusApproved, currentApprover.Member.ID, strings.TrimSpace(params.Reason), nullIfBlankString(installationID), resolvedBaseConfigJSON, resolvedRequestedConfigJSON, now, trimmedRequestID, strings.TrimSpace(workspaceID), AppInstallationRequestStatusPending)
		if err != nil {
			return fmt.Errorf("approve service installation request: %w", err)
		}
		if result.RowsAffected() != 1 {
			return conflict("App installation request decision changed.")
		}
		return nil
	}); err != nil {
		return AppInstallationRequestView{}, err
	}
	store.finalizeAppAvatarMediaCleanup(ctx, avatarCleanup)
	if privilegedRequestSettled {
		return store.appInstallationRequestByID(ctx, workspaceID, trimmedRequestID)
	}
	if shouldUnblockGiteaSubscriptions {
		// Connection lifecycle reconciliation is best-effort, matching the
		// provider connection handlers. The approved configuration remains the
		// durable truth even if one stale subscription cannot be reconciled.
		_, _ = store.UnblockServiceEventSubscriptions(ctx, workspaceID, giteaAppSlug, "")
	}
	if approvedUninstall != nil {
		store.finalizeAppInstallationUninstall(ctx, *approvedUninstall)
	}
	return store.appInstallationRequestByID(ctx, workspaceID, trimmedRequestID)
}

// prepareAppConfigurationApproval locks and revalidates the exact installation
// targeted by a configuration proposal. The base-config comparison prevents a
// stale approval from overwriting a newer, already-approved configuration.
func (store *Store) prepareAppConfigurationApproval(
	ctx context.Context,
	tx coredb.Tx,
	workspaceID string,
	req AppInstallationRequestView,
) (string, preparedAppInstallation, error) {
	installationID := strings.TrimSpace(req.InstallationID)
	if installationID == "" {
		return "", preparedAppInstallation{}, markAppInstallationApprovalStale(conflict("App configuration target no longer exists. Submit a new request."))
	}
	installation, err := store.serviceActorStore().InTx(tx).LoadInstallationForUpdate(ctx, installationID)
	if err != nil {
		if errors.Is(err, ErrNotFound) {
			return "", preparedAppInstallation{}, markAppInstallationApprovalStale(conflict("App configuration target no longer exists. Submit a new request."))
		}
		return "", preparedAppInstallation{}, err
	}
	if strings.TrimSpace(installation.WorkspaceID) != strings.TrimSpace(workspaceID) ||
		strings.TrimSpace(installation.ServiceKind) != serviceactor.KindApp ||
		strings.TrimSpace(installation.Status) != serviceactor.StatusActive ||
		strings.TrimSpace(installation.ServiceDefinitionID) != strings.TrimSpace(req.AppID) ||
		strings.TrimSpace(installation.InstalledVersion) != strings.TrimSpace(req.RequestedVersion) ||
		strings.TrimSpace(installation.OwnershipKind) == serviceactor.OwnershipKindPersonal {
		return "", preparedAppInstallation{}, markAppInstallationApprovalStale(conflict("App installation changed. Submit a new configuration request."))
	}
	if !reflect.DeepEqual(
		registry.JSONObjectOrEmpty(decodeJSONObjectMap(installation.Config)),
		registry.JSONObjectOrEmpty(req.BaseConfig),
	) {
		return "", preparedAppInstallation{}, markAppInstallationApprovalStale(conflict("App configuration changed. Submit a new configuration request."))
	}
	prepared, err := prepareInstalledAppConfigurationAtVersion(
		ctx,
		tx,
		installation.ServiceDefinitionID,
		installation.InstalledVersion,
		req.RequestedConfig,
	)
	if err != nil {
		return "", preparedAppInstallation{}, err
	}
	return installation.ID, prepared, nil
}

func applyPreparedAppConfigurationInTx(
	ctx context.Context,
	tx coredb.Tx,
	workspaceID string,
	installationID string,
	prepared preparedAppInstallation,
	now time.Time,
) error {
	result, err := tx.Exec(ctx, `
UPDATE service_installations
SET config = $1,
    updated_at = $2
WHERE id = $3
  AND workspace_id = $4
  AND service_definition_id = $5
  AND service_kind = $6
  AND status = $7
  AND installed_version = $8
`, prepared.configJSON, now, strings.TrimSpace(installationID), strings.TrimSpace(workspaceID), prepared.app.ID, serviceactor.KindApp, serviceactor.StatusActive, prepared.version.Version)
	if err != nil {
		return fmt.Errorf("apply approved App configuration: %w", err)
	}
	if result.RowsAffected() != 1 {
		return conflict("App installation changed. Submit a new configuration request.")
	}
	return nil
}

func (store *Store) RejectAppInstallationRequestForUser(ctx context.Context, userID string, workspaceID string, requestID string, params DecideAppInstallationRequestParams) (AppInstallationRequestView, error) {
	decider, err := store.loadAuthorizedHumanWorkspaceMember(ctx, store.db, userID, workspaceID, serviceInstallationApproveAction)
	if err != nil {
		return AppInstallationRequestView{}, err
	}
	trimmedRequestID := strings.TrimSpace(requestID)
	if trimmedRequestID == "" {
		return AppInstallationRequestView{}, badRequest("requestId is required")
	}
	privilegedRequestSettled := false
	cleanupCtx, avatarCleanup := withAppAvatarMediaCleanup(ctx)
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		req, err := loadAppInstallationRequestForUpdate(ctx, tx, workspaceID, trimmedRequestID)
		if err != nil {
			return err
		}
		if !req.ApprovalRequired {
			return conflict("This App operation is not an approval request.")
		}
		privilegedRequestSettled, err = settlePrivilegedAppInstallationRequestInTx(cleanupCtx, tx, req, time.Now().UTC())
		if err != nil || privilegedRequestSettled {
			return err
		}
		currentDecider, err := store.loadAuthorizedHumanWorkspaceMemberForUpdate(ctx, tx, userID, workspaceID, serviceInstallationApproveAction)
		if err != nil {
			return err
		}
		if strings.TrimSpace(currentDecider.Member.ID) != strings.TrimSpace(decider.Member.ID) {
			return conflict("App change approver membership changed.")
		}
		switch req.Status {
		case AppInstallationRequestStatusRejected:
			return nil
		case AppInstallationRequestStatusApproved:
			return conflict("App installation request was already approved.")
		case AppInstallationRequestStatusPending:
		default:
			return conflict("App installation request has an invalid state.")
		}
		now := time.Now().UTC()
		resolvedBaseConfigJSON, err := model.MarshalJSONObject("resolved service installation request base config", appInstallationRequestResolvedBaseConfig(req))
		if err != nil {
			return fmt.Errorf("redact rejected service installation request base config: %w", err)
		}
		resolvedRequestedConfig, err := appInstallationRequestTerminalRequestedConfig(req)
		if err != nil {
			return fmt.Errorf("resolve rejected service installation request config: %w", err)
		}
		resolvedRequestedConfigJSON, err := model.MarshalJSONObject("resolved service installation request config", resolvedRequestedConfig)
		if err != nil {
			return fmt.Errorf("redact rejected service installation request config: %w", err)
		}
		result, err := tx.Exec(ctx, `
UPDATE service_installation_requests
SET status = $2,
    decided_by_member_id = $3,
    decision_reason = $4,
    base_config = $5,
    requested_config = $6,
    updated_at = $7,
    decided_at = $7
WHERE id = $1
  AND workspace_id = $8
  AND status = 'pending'
`, trimmedRequestID, AppInstallationRequestStatusRejected, currentDecider.Member.ID, strings.TrimSpace(params.Reason), resolvedBaseConfigJSON, resolvedRequestedConfigJSON, now, strings.TrimSpace(workspaceID))
		if err != nil {
			return fmt.Errorf("reject service installation request: %w", err)
		}
		if result.RowsAffected() != 1 {
			return conflict("App installation request decision changed.")
		}
		if isGitHubApp(req.AppID) && strings.TrimSpace(req.RequestKind) == AppInstallationRequestKindInstall {
			if err := releaseGitHubAppSetupClaimByTokenInTx(ctx, tx, workspaceID, req.ID); err != nil {
				return err
			}
		}
		return deleteTerminalWorkspaceAppCatalogStateInTx(cleanupCtx, tx, req)
	}); err != nil {
		return AppInstallationRequestView{}, err
	}
	store.finalizeAppAvatarMediaCleanup(ctx, avatarCleanup)
	return store.appInstallationRequestByID(ctx, workspaceID, trimmedRequestID)
}

// deleteTerminalWorkspaceAppCatalogStateInTx removes a rejected or withdrawn
// App from the catalog. The request and its service-definition snapshot remain
// so Approval can project and retain the terminal audit message.
func deleteTerminalWorkspaceAppCatalogStateInTx(
	ctx context.Context,
	tx coredb.Tx,
	request AppInstallationRequestView,
) error {
	if strings.TrimSpace(request.RequestKind) != AppInstallationRequestKindInstall ||
		strings.TrimSpace(request.InstallationID) != "" {
		return nil
	}
	// Only custom App catalog state is removed on a terminal request, never a
	// product App. Product Apps (first_party, e.g. GitHub/Gitea) keep their
	// definition, versions, and manager/attribution projections so a subsequent
	// install can proceed. The appKind is read from the canonical definition.
	var appKind string
	if err := tx.QueryRow(ctx, `
SELECT COALESCE(JSON_UNQUOTE(JSON_EXTRACT(COALESCE(config, JSON_OBJECT()), '$.appKind')), '')
FROM service_definitions
WHERE id = $1
  AND service_kind = 'app'
LIMIT 1
`, strings.TrimSpace(request.AppID)).Scan(&appKind); err != nil {
		if coredb.IsNotFound(err) {
			return nil
		}
		return fmt.Errorf("load terminal workspace App kind: %w", err)
	}
	if strings.TrimSpace(appKind) == "third_party" {
		if err := deleteWorkspaceAppAvatarAssetsInTx(
			ctx, tx, request.WorkspaceID, request.AppID,
		); err != nil {
			return err
		}
		if _, err := tx.Exec(ctx, `
DELETE FROM extension_definition_versions
WHERE service_definition_id = $1
`, strings.TrimSpace(request.AppID)); err != nil {
			return fmt.Errorf("delete terminal workspace App versions: %w", err)
		}
		if _, err := tx.Exec(ctx, `
DELETE FROM extension_catalog_attributions
WHERE workspace_id = $1
  AND service_definition_id = $2
`, strings.TrimSpace(request.WorkspaceID), strings.TrimSpace(request.AppID)); err != nil {
			return fmt.Errorf("delete terminal workspace App attribution: %w", err)
		}
		if _, err := tx.Exec(ctx, `
DELETE FROM extension_definition_managers
WHERE workspace_id = $1
  AND service_definition_id = $2
`, strings.TrimSpace(request.WorkspaceID), strings.TrimSpace(request.AppID)); err != nil {
			return fmt.Errorf("delete terminal workspace App managers: %w", err)
		}
	}
	return nil
}

func (store *Store) appInstallationRequestByID(ctx context.Context, workspaceID string, requestID string) (AppInstallationRequestView, error) {
	return loadAppInstallationRequest(ctx, store.db, workspaceID, requestID)
}

// GetAppInstallationRequest is the trusted internal read surface used by
// Approval delivery retries to converge on the existing request truth.
func (store *Store) GetAppInstallationRequest(ctx context.Context, workspaceID string, requestID string) (AppInstallationRequestView, error) {
	return store.appInstallationRequestByID(ctx, workspaceID, requestID)
}

func requireAppInstallationRequestApplicable(request AppInstallationRequestView) error {
	if !request.ApprovalRequired {
		return conflict("This App operation is not an approval request.")
	}
	if request.Status == AppInstallationRequestStatusPending &&
		request.ReviewState == AppInstallationRequestReviewStateCannotBeApplied {
		return markAppInstallationApprovalStale(conflict("App change request can no longer be applied. Reject it and submit a new request."))
	}
	return nil
}

// MarkAppInstallationRequestCannotBeApplied persists the review action fence
// before Approval rewrites any cards. Request-row locking serializes it
// with approve/reject decisions: if a decision already won, its terminal truth
// is returned unchanged; otherwise every current and future reviewer must see
// the pending request as Reject-only.
func (store *Store) MarkAppInstallationRequestCannotBeApplied(
	ctx context.Context,
	workspaceID string,
	requestID string,
) (AppInstallationRequestView, error) {
	var request AppInstallationRequestView
	err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var err error
		request, err = loadAppInstallationRequestForUpdate(ctx, tx, workspaceID, requestID)
		if err != nil {
			return err
		}
		if !request.ApprovalRequired {
			return conflict("This App operation is not an approval request.")
		}
		if request.Status != AppInstallationRequestStatusPending ||
			request.ReviewState == AppInstallationRequestReviewStateCannotBeApplied {
			return nil
		}
		now := time.Now().UTC()
		if _, err := tx.Exec(ctx, `
UPDATE service_installation_requests
SET review_state = $1,
    updated_at = $2
WHERE workspace_id = $3
  AND id = $4
  AND status = $5
  AND approval_required = TRUE
`, AppInstallationRequestReviewStateCannotBeApplied, now, strings.TrimSpace(workspaceID), strings.TrimSpace(requestID), AppInstallationRequestStatusPending); err != nil {
			return fmt.Errorf("mark App installation request review state: %w", err)
		}
		request.ReviewState = AppInstallationRequestReviewStateCannotBeApplied
		request.UpdatedAt = now
		return nil
	})
	if err != nil {
		return AppInstallationRequestView{}, err
	}
	return request, nil
}

func loadAppInstallationRequestForUpdate(ctx context.Context, queryer coredb.QueryRower, workspaceID string, requestID string) (AppInstallationRequestView, error) {
	var serviceDefinitionID string
	if err := queryer.QueryRow(ctx, `
SELECT service_definition_id
FROM service_installation_requests
WHERE workspace_id = $1
  AND id = $2
LIMIT 1
`, strings.TrimSpace(workspaceID), strings.TrimSpace(requestID)).Scan(&serviceDefinitionID); err != nil {
		if coredb.IsNotFound(err) {
			return AppInstallationRequestView{}, ErrNotFound
		}
		return AppInstallationRequestView{}, fmt.Errorf("load App definition for installation request decision: %w", err)
	}
	if err := installation.LockAppInstallationMutationScope(ctx, queryer, serviceDefinitionID); err != nil {
		return AppInstallationRequestView{}, err
	}
	query := appInstallationRequestSelectSQL() + `
WHERE req.workspace_id = $1
  AND req.id = $2
FOR UPDATE`
	view, err := scanAppInstallationRequest(queryer.QueryRow(ctx, query, strings.TrimSpace(workspaceID), strings.TrimSpace(requestID)))
	if err != nil {
		if coredb.IsNotFound(err) {
			return AppInstallationRequestView{}, ErrNotFound
		}
		return AppInstallationRequestView{}, fmt.Errorf("load service installation request for update: %w", err)
	}
	return view, nil
}

func loadAppInstallationRequest(ctx context.Context, queryer coredb.QueryRower, workspaceID string, requestID string) (AppInstallationRequestView, error) {
	query := appInstallationRequestSelectSQL() + `
WHERE req.workspace_id = $1
  AND req.id = $2`
	view, err := scanAppInstallationRequest(queryer.QueryRow(ctx, query, strings.TrimSpace(workspaceID), strings.TrimSpace(requestID)))
	if err != nil {
		if coredb.IsNotFound(err) {
			return AppInstallationRequestView{}, ErrNotFound
		}
		return AppInstallationRequestView{}, fmt.Errorf("load service installation request: %w", err)
	}
	return view, nil
}

func appInstallationRequestSelectSQL() string {
	return `
SELECT
    req.id,
    req.workspace_id,
    req.service_definition_id,
    definition.slug,
    definition.name,
    definition.description,
    definition.avatar_url,
    req.request_kind,
    req.requested_version,
    req.requested_config,
    req.base_config,
    req.requested_scopes,
    req.status,
    req.review_state,
    req.approval_required,
    req.request_reason,
    req.requested_by_member_id,
    COALESCE(requested_member.user_id, ''),
    COALESCE(
        NULLIF(TRIM(requested_user.display_name), ''),
        NULLIF(TRIM(requested_user.name), ''),
        requested_member.display_name
    ),
    req.decided_by_member_id,
    req.decision_reason,
    req.service_installation_id,
    req.created_at,
    req.updated_at,
    req.decided_at
FROM service_installation_requests req
JOIN service_definitions definition ON definition.id = req.service_definition_id
JOIN members requested_member ON requested_member.id = req.requested_by_member_id
LEFT JOIN users requested_user ON requested_user.id = requested_member.user_id
`
}

func scanAppInstallationRequest(row appInstallationRequestScanner) (AppInstallationRequestView, error) {
	var view AppInstallationRequestView
	var requestedConfig []byte
	var baseConfig []byte
	var requestedScopes []byte
	var decidedByMemberID sql.NullString
	var installationID sql.NullString
	var decidedAt sql.NullTime
	if err := row.Scan(
		&view.ID,
		&view.WorkspaceID,
		&view.AppID,
		&view.AppSlug,
		&view.AppName,
		&view.AppDescription,
		&view.AvatarURL,
		&view.RequestKind,
		&view.RequestedVersion,
		&requestedConfig,
		&baseConfig,
		&requestedScopes,
		&view.Status,
		&view.ReviewState,
		&view.ApprovalRequired,
		&view.RequestReason,
		&view.RequestedByMemberID,
		&view.RequestedByUserID,
		&view.RequestedByDisplayName,
		&decidedByMemberID,
		&view.DecisionReason,
		&installationID,
		&view.CreatedAt,
		&view.UpdatedAt,
		&decidedAt,
	); err != nil {
		return AppInstallationRequestView{}, err
	}
	var storedRequestedConfig map[string]any
	if err := model.UnmarshalJSONObject("service installation request config", requestedConfig, &storedRequestedConfig); err != nil {
		return AppInstallationRequestView{}, err
	}
	if storedRequestedConfig == nil {
		storedRequestedConfig = map[string]any{}
	}
	var storedBaseConfig map[string]any
	if err := model.UnmarshalJSONObject("service installation request base config", baseConfig, &storedBaseConfig); err != nil {
		return AppInstallationRequestView{}, err
	}
	if storedBaseConfig == nil {
		storedBaseConfig = map[string]any{}
	}
	view.storedRequestedConfig = storedRequestedConfig
	view.storedBaseConfig = storedBaseConfig
	view.RequestedConfig = appInstallationRequestPublicConfig(view.AppID, storedRequestedConfig)
	view.BaseConfig = appInstallationRequestPublicConfig(view.AppID, storedBaseConfig)
	view.AppRuntimeTransports, view.AppCallbackURL, view.AppCatalogVisibility = appInstallationReviewSnapshotFromBaseConfig(storedBaseConfig)
	delete(view.BaseConfig, appInstallationAppReviewSnapshotKey)
	if err := model.UnmarshalJSONObject("service installation request scopes", requestedScopes, &view.RequestedScopes); err != nil {
		return AppInstallationRequestView{}, err
	}
	view.RequestedScopes = registry.SortedUniqueScopes(view.RequestedScopes)
	view.ID = strings.TrimSpace(view.ID)
	view.WorkspaceID = strings.TrimSpace(view.WorkspaceID)
	view.AppID = strings.TrimSpace(view.AppID)
	view.AppSlug = strings.TrimSpace(view.AppSlug)
	view.AppName = strings.TrimSpace(view.AppName)
	view.AppDescription = strings.TrimSpace(view.AppDescription)
	view.AvatarURL = strings.TrimSpace(view.AvatarURL)
	view.RequestKind = strings.TrimSpace(view.RequestKind)
	if isGitHubApp(view.AppID) &&
		(view.RequestKind == AppInstallationRequestKindInstall || view.RequestKind == AppInstallationRequestKindConnect) {
		targets, err := parseGitHubConnectionTargetSnapshots(storedRequestedConfig)
		if err != nil {
			return AppInstallationRequestView{}, err
		}
		view.ConnectionTargets = githubConnectionTargetIDs(targets)
		view.GitHubConnectionTargets = make([]GitHubConnectionTargetView, 0, len(targets))
		for _, target := range targets {
			view.GitHubConnectionTargets = append(view.GitHubConnectionTargets, GitHubConnectionTargetView{
				InstallationID: target.InstallationID,
				AccountLogin:   target.AccountLogin,
			})
		}
		if len(targets) > 0 {
			view.ConnectionTarget = targets[0].InstallationID
			view.ConnectionLabel = targets[0].AccountLogin
		}
	}
	switch view.RequestKind {
	case AppInstallationRequestKindInstall:
		if isGitHubApp(view.AppID) && len(view.ConnectionTargets) == 0 {
			view.ConnectionTarget = requestSnapshotString(storedRequestedConfig, connectionRequestTargetKey)
			view.ConnectionLabel = requestSnapshotString(storedRequestedConfig, connectionRequestDisplayLabelKey)
		}
	case AppInstallationRequestKindUninstall:
		// The uninstall base is internal CAS material (including installation
		// timestamps and active connection identities), not proposal content.
		// Create/admin/card responses only need the explicit operation and target.
		view.BaseConfig = map[string]any{}
		view.RequestedConfig = map[string]any{}
	case AppInstallationRequestKindConnect:
		if !isGitHubApp(view.AppID) || len(view.ConnectionTargets) == 0 {
			view.ConnectionTarget = requestSnapshotString(storedRequestedConfig, connectionRequestTargetKey)
			view.ConnectionLabel = requestSnapshotString(storedRequestedConfig, connectionRequestDisplayLabelKey)
		}
	case AppInstallationRequestKindDisconnect:
		view.ConnectionID = requestSnapshotString(storedRequestedConfig, connectionRequestIDKey)
		if view.ConnectionID == "" {
			view.ConnectionID = requestSnapshotString(storedBaseConfig, connectionRequestIDKey)
		}
		view.ConnectionLabel = requestSnapshotString(storedBaseConfig, connectionRequestDisplayLabelKey)
		if view.ConnectionLabel == "" {
			view.ConnectionLabel = requestSnapshotString(storedBaseConfig, connectionRequestExternalRefKey)
		}
		view.ConnectionTarget = requestSnapshotString(storedBaseConfig, connectionRequestExternalRefKey)
	}
	view.RequestedVersion = strings.TrimSpace(view.RequestedVersion)
	view.Status = strings.TrimSpace(view.Status)
	view.RequestReason = strings.TrimSpace(view.RequestReason)
	view.RequestedByMemberID = strings.TrimSpace(view.RequestedByMemberID)
	view.RequestedByUserID = strings.TrimSpace(view.RequestedByUserID)
	view.RequestedByDisplayName = strings.TrimSpace(view.RequestedByDisplayName)
	if decidedByMemberID.Valid {
		view.DecidedByMemberID = strings.TrimSpace(decidedByMemberID.String)
	}
	view.DecisionReason = strings.TrimSpace(view.DecisionReason)
	if installationID.Valid {
		view.InstallationID = strings.TrimSpace(installationID.String)
	}
	if decidedAt.Valid {
		at := decidedAt.Time.UTC()
		view.DecidedAt = &at
	}
	return view, nil
}