package app

import (
	"context"
	"errors"
	"fmt"
	"slices"
	"strconv"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/domain"
	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"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/provider"
	"github.com/tidbcloud/AgentTeam/internal/store/serviceactor"
)

const (
	GitHubAppSetupModeAdmin                       = "admin"
	GitHubAppSetupModeRequester                   = "requester"
	orphanedGitHubConnectionRequestDecisionReason = "Withdrawn by system: the GitHub App is no longer active in this workspace."

	githubSetupProposalTTL             = time.Hour
	githubSetupProposalProviderTimeout = 2 * time.Minute

	githubConfigAppIDKey         = "app_id"
	githubConfigAppSlugKey       = "app_slug"
	githubConfigAppNameKey       = "app_name"
	githubConfigHTMLURLKey       = "html_url"
	githubConfigClientIDKey      = "client_id"
	githubConfigClientSecretKey  = "client_secret"
	githubConfigPrivateKeyPEMKey = "private_key_pem"
	githubConfigWebhookSecretKey = "webhook_secret"
	githubConfigOwnerLoginKey    = "owner_login"
	githubConfigOwnerIDKey       = "owner_id"
	githubConfigMaskedSecret     = "********"

	// githubSetupRequireFirstConnectionKey marks a credential-reuse draft that
	// must bind the complete desired provider installation set (one to fifty)
	// before it can become a pending install request. It lives only in the
	// internal base snapshot and is never projected as provider config.
	githubSetupRequireFirstConnectionKey = "github_require_first_connection"
)

var githubConfigRequestKeys = map[string]struct{}{
	githubConfigAppIDKey:               {},
	githubConfigAppSlugKey:             {},
	githubConfigAppNameKey:             {},
	githubConfigHTMLURLKey:             {},
	githubConfigClientIDKey:            {},
	githubConfigClientSecretKey:        {},
	githubConfigPrivateKeyPEMKey:       {},
	githubConfigWebhookSecretKey:       {},
	githubConfigOwnerLoginKey:          {},
	githubConfigOwnerIDKey:             {},
	connectionRequestTargetKey:         {},
	connectionRequestDisplayLabelKey:   {},
	githubConnectionTargetsSnapshotKey: {},
}

func requestTargetsGitHubApp(appID string, appSlug string) bool {
	if trimmedAppID := strings.TrimSpace(appID); trimmedAppID != "" {
		return trimmedAppID == registry.GitHubAppID
	}
	return strings.EqualFold(strings.TrimSpace(appSlug), registry.GitHubAppSlug)
}

func isGitHubApp(appID string) bool {
	return strings.TrimSpace(appID) == registry.GitHubAppID
}

// GitHubAppSetupModeForUser classifies a registration/recovery flow before
// any GitHub credential is returned. Administrators keep the direct atomic
// path; ordinary members may prepare independent private drafts while GitHub
// is not installed or configured in the workspace. A pending install request
// is deliberately not an ownership fence: the first request that wins the
// pre-PATCH activation claim becomes authoritative.
func (store *Store) GitHubAppSetupModeForUser(ctx context.Context, userID string, workspaceID string) (string, error) {
	expected, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return "", err
	}
	mode := ""
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := installation.LockAppInstallationMutationScope(ctx, tx, registry.GitHubAppID); err != nil {
			return err
		}
		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
		}
		member, err := store.revalidateGitHubSetupMemberForUpdate(ctx, tx, userID, workspaceID, expected)
		if err != nil {
			return err
		}
		if access.HasWorkspaceAdminRole(domain.WorkspaceRole(member.WorkspaceRole)) {
			mode = GitHubAppSetupModeAdmin
			return nil
		}
		blocked, err := githubSetupBlockedForMember(ctx, tx, workspaceID, true)
		if err != nil {
			return err
		}
		if blocked {
			return conflict("GitHub is already active for this workspace.")
		}
		// Connection approvals created by older AgentTeam versions are scoped to the
		// removed App generation. They are retained as redacted terminal audit,
		// but must neither block a fresh App setup nor become actionable again if
		// the workspace later installs a different GitHub App.
		if err := settleOrphanedGitHubConnectionRequestsInTx(
			ctx, tx, workspaceID, time.Now().UTC(),
		); err != nil {
			return err
		}
		mode = GitHubAppSetupModeRequester
		return nil
	}); err != nil {
		return "", err
	}
	return mode, nil
}

// SaveVerifiedGitHubSetupProposalForUser persists one verified GitHub setup as
// an encrypted, requester-private draft. Repeating verification replaces the
// caller's previous unsubmitted draft; it never creates an active App,
// provider config, or pending approval request.
func (store *Store) SaveVerifiedGitHubSetupProposalForUser(
	ctx context.Context,
	userID string,
	workspaceID string,
	params provider.UpsertGitHubAppConfigParams,
) (GitHubSetupProposalView, error) {
	expectedRequester, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return GitHubSetupProposalView{}, err
	}
	if err := requireAppApprovalRequester(expectedRequester); err != nil {
		return GitHubSetupProposalView{}, err
	}
	return store.saveVerifiedGitHubSetupDraftForUser(
		ctx, userID, workspaceID, expectedRequester, GitHubAppSetupModeRequester, false, params,
	)
}

// SaveVerifiedGitHubSetupDraftForRequester persists an existing-App reuse
// draft without mutating the provider webhook. Unlike the registration
// callback proposal above, this draft must bind a first provider installation
// before it can be submitted for administrator approval.
func (store *Store) SaveVerifiedGitHubSetupDraftForRequester(
	ctx context.Context,
	userID string,
	workspaceID string,
	params provider.UpsertGitHubAppConfigParams,
) (GitHubSetupProposalView, error) {
	expectedRequester, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return GitHubSetupProposalView{}, err
	}
	if err := requireAppApprovalRequester(expectedRequester); err != nil {
		return GitHubSetupProposalView{}, err
	}
	return store.saveVerifiedGitHubSetupDraftForUser(
		ctx, userID, workspaceID, expectedRequester, GitHubAppSetupModeRequester, true, params,
	)
}

// SaveVerifiedGitHubSetupDraftForWorkspaceAdmin persists a fresh, encrypted
// administrator-owned draft after credential verification. Unlike the active
// configuration path, this method deliberately creates no AgentTeam App
// installation, provider config, connection, binding, grant, or subscription
// change. The draft is consumed only after the administrator explicitly
// selects the complete desired provider installation set (one to fifty).
func (store *Store) SaveVerifiedGitHubSetupDraftForWorkspaceAdmin(
	ctx context.Context,
	userID string,
	workspaceID string,
	params provider.UpsertGitHubAppConfigParams,
) (GitHubSetupProposalView, error) {
	expectedAdmin, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return GitHubSetupProposalView{}, err
	}
	if err := requireGitHubSetupMode(expectedAdmin, GitHubAppSetupModeAdmin); err != nil {
		return GitHubSetupProposalView{}, err
	}
	if err := store.settlePrivilegedGitHubPendingRequests(ctx, workspaceID); err != nil {
		return GitHubSetupProposalView{}, err
	}
	return store.saveVerifiedGitHubSetupDraftForUser(
		ctx, userID, workspaceID, expectedAdmin, GitHubAppSetupModeAdmin, true, params,
	)
}

func (store *Store) saveVerifiedGitHubSetupDraftForUser(
	ctx context.Context,
	userID string,
	workspaceID string,
	expected access.HumanWorkspaceMember,
	mode string,
	requireFirstConnection bool,
	params provider.UpsertGitHubAppConfigParams,
) (GitHubSetupProposalView, error) {
	storedConfig, err := store.prepareStoredGitHubAppConfigProposal(params)
	if err != nil {
		return GitHubSetupProposalView{}, err
	}

	proposalID, err := identifiers.NewEntityID("ghsetup")
	if err != nil {
		return GitHubSetupProposalView{}, fmt.Errorf("generate GitHub setup proposal id: %w", err)
	}
	publicConfig := appInstallationRequestPublicGitHubConfig(storedConfig)
	var proposal GitHubSetupProposalView
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := installation.LockAppInstallationMutationScope(ctx, tx, registry.GitHubAppID); err != nil {
			return err
		}
		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
		}
		prepared, err := prepareAppInstallationAtVersion(ctx, tx, registry.GitHubAppID, registry.GitHubAppSlug, "", nil)
		if err != nil {
			return err
		}
		current, err := store.revalidateGitHubSetupMemberForUpdate(ctx, tx, userID, workspaceID, expected)
		if err != nil {
			return err
		}
		if err := requireGitHubSetupMode(current, mode); err != nil {
			return err
		}
		cutoff := time.Now().UTC().Add(-githubSetupProposalTTL)
		if err := deleteExpiredGitHubSetupProposalsInTx(
			ctx, tx, workspaceID, cutoff,
		); err != nil {
			return err
		}
		blocked, err := githubSetupBlockedForMember(ctx, tx, workspaceID, true)
		if err != nil {
			return err
		}
		if blocked {
			return conflict("GitHub is already active for this workspace.")
		}

		definition, err := store.syncAppServiceDefinition(ctx, tx, prepared.app.ID, time.Now().UTC())
		if err != nil {
			return err
		}
		if strings.TrimSpace(definition.ID) != registry.GitHubAppID {
			return conflict("GitHub App definition changed. Restart setup.")
		}

		requestedConfigJSON, err := model.MarshalJSONObject("GitHub setup proposal config", storedConfig)
		if err != nil {
			return fmt.Errorf("encode GitHub setup proposal config: %w", err)
		}
		requestedScopesJSON, err := model.MarshalJSONObject("GitHub setup proposal scopes", registry.SortedUniqueScopes(prepared.version.Scopes))
		if err != nil {
			return fmt.Errorf("encode GitHub setup proposal scopes: %w", err)
		}
		baseConfig := map[string]any{}
		if requireFirstConnection {
			baseConfig[githubSetupRequireFirstConnectionKey] = true
		}
		requestReason := ""
		if strings.TrimSpace(mode) == GitHubAppSetupModeAdmin {
			baseConfig[githubVerificationExpectedConfigIDKey] = ""
			baseConfig[githubVerificationExpectedConfigUpdatedAtKey] = ""
			requestReason = GitHubAppSetupModeAdmin
		}
		baseConfigJSON, err := model.MarshalJSONObject("GitHub setup proposal base config", baseConfig)
		if err != nil {
			return fmt.Errorf("encode GitHub setup proposal base config: %w", err)
		}

		// A draft is not an audit decision. Reverification atomically replaces
		// the caller's previous unsubmitted secret envelope instead of retaining
		// stale credentials indefinitely.
		//
		// Every caller owns only its own unsubmitted draft. Peer requester/admin
		// drafts remain independent contenders until one operation wins the
		// durable pre-PATCH claim.
		if _, err := tx.Exec(ctx, `
DELETE claim
FROM service_provider_workspace_claims claim
JOIN service_installation_requests req
  ON req.id = claim.claim_token
 AND req.workspace_id = claim.workspace_id
WHERE claim.workspace_id = $1
  AND claim.claim_state = $2
  AND req.service_definition_id = $3
  AND req.service_kind = $4
  AND req.request_kind = $5
  AND req.requested_by_member_id = $6
  AND req.status = $7
`, strings.TrimSpace(workspaceID), githubAppClaimStateSetup, registry.GitHubAppID,
			serviceactor.KindApp, AppInstallationRequestKindInstall, current.Member.ID,
			AppInstallationRequestStatusSetupReady); err != nil {
			return fmt.Errorf("release replaced GitHub setup proposal claim: %w", err)
		}
		if _, err := tx.Exec(ctx, `
DELETE FROM service_installation_requests
WHERE workspace_id = $1
  AND service_definition_id = $2
  AND service_kind = $3
	AND request_kind = $4
	AND requested_by_member_id = $5
	AND status = $6
`, strings.TrimSpace(workspaceID), registry.GitHubAppID, serviceactor.KindApp, AppInstallationRequestKindInstall, current.Member.ID, AppInstallationRequestStatusSetupReady); err != nil {
			return fmt.Errorf("replace previous GitHub setup proposal: %w", err)
		}
		if strings.TrimSpace(mode) == GitHubAppSetupModeAdmin {
			if err := deleteRevokedAdminGitHubSetupProposalsInTx(ctx, tx, workspaceID); err != nil {
				return err
			}
		}

		// service_installation_requests timestamps have second precision. Use the
		// same precision for the returned expiry so a subsequent read returns the
		// identical proposal metadata instead of a database-rounded timestamp.
		// Drafts and pending requests are candidates, not ownership claims. The
		// durable App/workspace claim is acquired only at Mark immediately before
		// the first outbound webhook PATCH.
		now := time.Now().UTC().Truncate(time.Second)
		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, 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, $11, $12, '',
    NULL, $13, $13
)
`, proposalID, strings.TrimSpace(workspaceID), registry.GitHubAppID, serviceactor.KindApp, AppInstallationRequestKindInstall,
			prepared.version.Version, requestedConfigJSON, baseConfigJSON, requestedScopesJSON,
			AppInstallationRequestStatusSetupReady, requestReason, current.Member.ID, now); err != nil {
			return fmt.Errorf("create GitHub setup proposal: %w", err)
		}
		proposal = GitHubSetupProposalView{
			ID:         proposalID,
			Ready:      true,
			AppID:      requestSnapshotString(publicConfig, githubConfigAppIDKey),
			AppSlug:    requestSnapshotString(publicConfig, githubConfigAppSlugKey),
			AppName:    requestSnapshotString(publicConfig, githubConfigAppNameKey),
			OwnerLogin: requestSnapshotString(publicConfig, githubConfigOwnerLoginKey),
			ExpiresAt:  now.Add(githubSetupProposalTTL),
		}
		return nil
	}); err != nil {
		return GitHubSetupProposalView{}, err
	}
	return proposal, nil
}

// GetGitHubSetupProposalForUser returns only the caller's current, unexpired
// safe metadata. Peer members and administrators cannot enumerate drafts; the
// encrypted snapshot remains internal until explicit promotion and approval.
func (store *Store) GetGitHubSetupProposalForUser(ctx context.Context, userID string, workspaceID string) (GitHubSetupProposalView, bool, error) {
	expectedRequester, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return GitHubSetupProposalView{}, false, err
	}
	if err := requireAppApprovalRequester(expectedRequester); err != nil {
		return GitHubSetupProposalView{}, false, err
	}

	var request AppInstallationRequestView
	found := false
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := installation.LockAppInstallationMutationScope(ctx, tx, registry.GitHubAppID); err != nil {
			return err
		}
		currentRequester, err := store.revalidateAppApprovalRequesterForUpdate(
			ctx, tx, userID, workspaceID, expectedRequester,
		)
		if err != nil {
			return err
		}
		cutoff := time.Now().UTC().Add(-githubSetupProposalTTL)
		if err := deleteExpiredGitHubSetupProposalsInTx(
			ctx, tx, workspaceID, cutoff,
		); err != nil {
			return err
		}
		query := appInstallationRequestSelectSQL() + `
WHERE req.workspace_id = $1
  AND req.service_definition_id = $2
	AND req.service_kind = $3
	AND req.request_kind = $4
	AND req.requested_by_member_id = $5
	AND req.status = $6
	AND req.request_reason = ''
	AND req.created_at >= $7
ORDER BY req.created_at DESC, req.id DESC
LIMIT 1`
		request, err = scanAppInstallationRequest(tx.QueryRow(
			ctx, query, strings.TrimSpace(workspaceID), registry.GitHubAppID,
			serviceactor.KindApp, AppInstallationRequestKindInstall,
			strings.TrimSpace(currentRequester.Member.ID), AppInstallationRequestStatusSetupReady,
			cutoff,
		))
		if err != nil {
			if coredb.IsNotFound(err) {
				return nil
			}
			return fmt.Errorf("load GitHub setup proposal: %w", err)
		}
		found = true
		return nil
	}); err != nil {
		return GitHubSetupProposalView{}, false, err
	}
	if !found {
		return GitHubSetupProposalView{}, false, nil
	}
	view := githubSetupProposalView(request)
	return view, true, nil
}

func (store *Store) promoteVerifiedGitHubSetupProposalForUser(
	ctx context.Context,
	userID string,
	workspaceID string,
	expectedRequester access.HumanWorkspaceMember,
	params RequestAppInstallationParams,
) (AppInstallationRequestView, error) {
	// Proposal promotion is still pre-provider-mutation, so respect caller
	// cancellation while placing one overall ceiling around every exact GET and
	// the subsequent local transaction. This prevents a 50-installation request
	// from multiplying the HTTP client's per-request timeout into an unbounded
	// operation, and an expired operation cannot promote the local draft.
	operationCtx, cancel := context.WithTimeout(ctx, githubSetupProposalProviderTimeout)
	defer cancel()
	ctx = operationCtx

	proposalID := strings.TrimSpace(params.GitHubSetupProposalID)
	missingProposalID := proposalID == ""
	if strings.TrimSpace(params.InstallationID) != "" || len(params.Config) > 0 || params.ExpectedBaseConfig != nil ||
		strings.TrimSpace(params.ConnectionID) != "" {
		return AppInstallationRequestView{}, badRequest("GitHub install requests must contain githubSetupProposalId, one or more connectionTargets, and an optional reason.")
	}
	if explicit := strings.TrimSpace(params.RequestKind); explicit != "" && explicit != AppInstallationRequestKindInstall {
		return AppInstallationRequestView{}, badRequest("GitHub setup proposals can only be submitted as install requests.")
	}
	if appID := strings.TrimSpace(params.AppID); appID != "" && appID != registry.GitHubAppID {
		return AppInstallationRequestView{}, conflict("GitHub setup proposal target changed.")
	}
	if appSlug := strings.TrimSpace(params.AppSlug); appSlug != "" && !strings.EqualFold(appSlug, registry.GitHubAppSlug) {
		return AppInstallationRequestView{}, conflict("GitHub setup proposal target changed.")
	}
	selectedInstallationIDs := append([]string(nil), params.ConnectionTargets...)
	if strings.TrimSpace(params.ConnectionTarget) != "" {
		selectedInstallationIDs = append(selectedInstallationIDs, params.ConnectionTarget)
	}
	var verifiedSelections []githubConnectionTargetSnapshot
	if len(selectedInstallationIDs) > 0 {
		canonicalSelection, err := normalizedGitHubInstallationIDs(selectedInstallationIDs)
		if err != nil {
			return AppInstallationRequestView{}, err
		}
		selectedInstallationIDs = canonicalSelection
		// Product requester drafts are owner-bound and marked as requiring a
		// initial connection set. Exact provider verification happens before the
		// promotion transaction so a raw client cannot submit an undiscovered,
		// personal, suspended, or under-scoped installation.
		_, draftConfig, draftErr := store.GetGitHubSetupDraftForRequester(
			ctx, userID, workspaceID, proposalID,
		)
		if draftErr == nil {
			verifiedSelections = make([]githubConnectionTargetSnapshot, 0, len(selectedInstallationIDs))
			for _, installationID := range selectedInstallationIDs {
				verified, verifyErr := store.verifyGitHubConnectionInstallation(ctx, draftConfig, installationID)
				if verifyErr != nil {
					return AppInstallationRequestView{}, verifyErr
				}
				verifiedSelections = append(verifiedSelections, githubConnectionTargetSnapshot{
					InstallationID: verified.ExternalSubjectRef,
					AccountLogin:   verified.DisplayLabel,
					AccountID:      strconv.FormatInt(verified.AccountID, 10),
				})
			}
		} else if !errors.Is(draftErr, ErrNotFound) && !errors.Is(draftErr, ErrConflict) {
			return AppInstallationRequestView{}, draftErr
		}
	}

	expired := false
	elevated := false
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := installation.LockAppInstallationMutationScope(ctx, tx, registry.GitHubAppID); err != nil {
			return err
		}
		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 missingProposalID {
			// Even malformed direct calls must not bypass current-role
			// revalidation. This keeps the request ledger member-only when an
			// endpoint snapshot races an administrator promotion.
			_, err := store.revalidateAppApprovalRequesterForUpdate(
				ctx, tx, userID, workspaceID, expectedRequester,
			)
			return err
		}
		// Keep the canonical lock order shared with decisions and direct setup:
		// definition -> proposal -> requester membership -> provider state.
		request, err := loadGitHubSetupProposalForUpdate(
			ctx, tx, workspaceID, proposalID, expectedRequester.Member.ID,
		)
		if err != nil {
			return err
		}
		currentRequester, requesterElevated, err := store.revalidateGitHubSetupRequesterForUpdate(
			ctx, tx, userID, workspaceID, expectedRequester,
		)
		if err != nil {
			return err
		}
		if requesterElevated {
			if strings.TrimSpace(request.Status) == AppInstallationRequestStatusSetupReady {
				if err := deleteGitHubSetupProposalByIDInTx(
					ctx, tx, workspaceID, request.ID, currentRequester.Member.ID,
				); err != nil {
					return err
				}
				elevated = true
				return nil
			}
			return ErrForbidden
		}
		if strings.TrimSpace(request.Status) == AppInstallationRequestStatusPending {
			// A client can safely retry after losing the POST response. The same
			// opaque proposal id and immutable provider selection identify the
			// already-promoted request; do not create or mutate a second pending
			// request or allow a retry to switch organizations.
			if !slices.Equal(request.ConnectionTargets, selectedInstallationIDs) {
				return conflict("GitHub installation selection changed. Submit a new setup proposal.")
			}
			return nil
		}
		if strings.TrimSpace(request.Status) != AppInstallationRequestStatusSetupReady {
			return ErrNotFound
		}
		now := time.Now().UTC()
		if request.CreatedAt.Before(now.Add(-githubSetupProposalTTL)) {
			expired = true
			return deleteGitHubSetupProposalByIDInTx(
				ctx, tx, workspaceID, request.ID, currentRequester.Member.ID,
			)
		}
		blocked, err := githubSetupBlockedForMember(ctx, tx, workspaceID, true)
		if err != nil {
			return err
		}
		if blocked {
			return conflict("GitHub is already active for this workspace.")
		}
		if githubSetupProposalRequiresFirstConnection(request) {
			if len(selectedInstallationIDs) == 0 || len(verifiedSelections) != len(selectedInstallationIDs) {
				return badRequest("At least one verified GitHub installation must be selected.")
			}
		}
		prepared, err := prepareAppInstallationAtVersion(ctx, tx, registry.GitHubAppID, registry.GitHubAppSlug, request.RequestedVersion, nil)
		if err != nil {
			return err
		}
		if strings.TrimSpace(prepared.version.Version) != strings.TrimSpace(request.RequestedVersion) ||
			!equalSortedScopes(prepared.version.Scopes, request.RequestedScopes) {
			return conflict("GitHub App definition changed. Restart setup.")
		}
		_, found, err := store.verifiedGitHubAppConfigFromStoredMap(
			appInstallationRequestStoredRequestedConfig(request),
		)
		if err != nil {
			return err
		}
		if !found {
			return conflict("GitHub setup proposal is missing. Restart setup.")
		}
		storedRequestedConfig := cloneAppInstallationRequestConfig(
			appInstallationRequestStoredRequestedConfig(request),
		)
		if len(verifiedSelections) > 0 {
			storedRequestedConfig[githubConnectionTargetsSnapshotKey] = githubConnectionTargetSnapshotMap(verifiedSelections)
			delete(storedRequestedConfig, connectionRequestTargetKey)
			delete(storedRequestedConfig, connectionRequestDisplayLabelKey)
		}
		requestedConfigJSON, err := model.MarshalJSONObject(
			"GitHub setup request config", storedRequestedConfig,
		)
		if err != nil {
			return fmt.Errorf("encode GitHub setup request config: %w", err)
		}
		result, err := tx.Exec(ctx, `
UPDATE service_installation_requests
SET status = $1,
    request_reason = $2,
	requested_config = $3,
	approval_required = TRUE,
	updated_at = $4
WHERE id = $5
  AND workspace_id = $6
  AND requested_by_member_id = $7
  AND status = $8
	AND service_definition_id = $9
	AND service_kind = $10
	AND request_kind = $11
`, AppInstallationRequestStatusPending, strings.TrimSpace(params.Reason), requestedConfigJSON, now, request.ID,
			strings.TrimSpace(workspaceID), currentRequester.Member.ID, AppInstallationRequestStatusSetupReady,
			registry.GitHubAppID, serviceactor.KindApp, AppInstallationRequestKindInstall)
		if err != nil {
			return fmt.Errorf("submit GitHub setup proposal for approval: %w", err)
		}
		if result.RowsAffected() != 1 {
			return conflict("GitHub setup proposal was already submitted or replaced.")
		}
		return nil
	}); err != nil {
		return AppInstallationRequestView{}, err
	}
	if missingProposalID {
		return AppInstallationRequestView{}, badRequest("githubSetupProposalId is required after GitHub setup.")
	}
	if expired {
		return AppInstallationRequestView{}, gone("GitHub setup proposal expired. Restart setup.")
	}
	if elevated {
		return AppInstallationRequestView{}, conflict("Your workspace role changed. Continue GitHub setup directly as an administrator.")
	}
	return store.appInstallationRequestByID(ctx, workspaceID, proposalID)
}

func (store *Store) prepareStoredGitHubAppConfigProposal(params provider.UpsertGitHubAppConfigParams) (map[string]any, error) {
	params.AppSlug = strings.TrimSpace(params.AppSlug)
	params.AppName = strings.TrimSpace(params.AppName)
	params.HTMLURL = strings.TrimSpace(params.HTMLURL)
	params.ClientID = strings.TrimSpace(params.ClientID)
	params.ClientSecret = strings.TrimSpace(params.ClientSecret)
	params.PrivateKeyPEM = strings.TrimSpace(params.PrivateKeyPEM)
	params.WebhookSecret = strings.TrimSpace(params.WebhookSecret)
	params.OwnerLogin = strings.TrimSpace(params.OwnerLogin)
	if params.AppID <= 0 || params.OwnerID < 0 || params.AppSlug == "" || params.AppName == "" || params.PrivateKeyPEM == "" || params.WebhookSecret == "" {
		return nil, badRequest("Verified GitHub App configuration is incomplete.")
	}
	clientSecret, err := store.encryptOptionalGitHubAppConfigSecret(params.ClientSecret)
	if err != nil {
		return nil, err
	}
	privateKeyPEM, err := store.encryptGitHubAppConfigSecret(params.PrivateKeyPEM)
	if err != nil {
		return nil, err
	}
	webhookSecret, err := store.encryptGitHubAppConfigSecret(params.WebhookSecret)
	if err != nil {
		return nil, err
	}
	return map[string]any{
		// GitHub identifiers can exceed JavaScript's exact integer range. Keep
		// them as canonical decimal strings throughout the JSON snapshot.
		githubConfigAppIDKey:         strconv.FormatInt(params.AppID, 10),
		githubConfigAppSlugKey:       params.AppSlug,
		githubConfigAppNameKey:       params.AppName,
		githubConfigHTMLURLKey:       params.HTMLURL,
		githubConfigClientIDKey:      params.ClientID,
		githubConfigClientSecretKey:  clientSecret,
		githubConfigPrivateKeyPEMKey: privateKeyPEM,
		githubConfigWebhookSecretKey: webhookSecret,
		githubConfigOwnerLoginKey:    params.OwnerLogin,
		githubConfigOwnerIDKey:       strconv.FormatInt(params.OwnerID, 10),
	}, nil
}

func (store *Store) applyApprovedGitHubAppConfigInTx(
	ctx context.Context,
	tx coredb.Tx,
	workspaceID string,
	approvedByUserID string,
	request AppInstallationRequestView,
	now time.Time,
) error {
	_ = store
	_ = ctx
	_ = tx
	_ = workspaceID
	_ = approvedByUserID
	_ = request
	_ = now
	// GitHub initial activation must include an immutable first-connection set
	// and pass through the exact GET -> Mark -> PATCH saga. Legacy requests that
	// reach the generic approval path cannot safely activate provider config.
	return conflict("GitHub setup request is missing its selected connections. Submit a new request.")
}

func (store *Store) verifiedGitHubAppConfigFromStoredMap(config map[string]any) (provider.UpsertGitHubAppConfigParams, bool, error) {
	if len(config) == 0 {
		return provider.UpsertGitHubAppConfigParams{}, false, nil
	}
	for key := range config {
		if _, ok := githubConfigRequestKeys[key]; !ok {
			return provider.UpsertGitHubAppConfigParams{}, false, conflict("GitHub setup proposal is invalid. Restart setup.")
		}
	}
	readString := func(key string) (string, error) {
		value, ok := config[key]
		if !ok || value == nil {
			return "", nil
		}
		text, ok := value.(string)
		if !ok {
			return "", conflict("GitHub setup proposal is invalid. Restart setup.")
		}
		return strings.TrimSpace(text), nil
	}
	appSlug, err := readString(githubConfigAppSlugKey)
	if err != nil {
		return provider.UpsertGitHubAppConfigParams{}, false, err
	}
	appName, err := readString(githubConfigAppNameKey)
	if err != nil {
		return provider.UpsertGitHubAppConfigParams{}, false, err
	}
	htmlURL, err := readString(githubConfigHTMLURLKey)
	if err != nil {
		return provider.UpsertGitHubAppConfigParams{}, false, err
	}
	clientID, err := readString(githubConfigClientIDKey)
	if err != nil {
		return provider.UpsertGitHubAppConfigParams{}, false, err
	}
	storedClientSecret, err := readString(githubConfigClientSecretKey)
	if err != nil {
		return provider.UpsertGitHubAppConfigParams{}, false, err
	}
	storedPrivateKey, err := readString(githubConfigPrivateKeyPEMKey)
	if err != nil {
		return provider.UpsertGitHubAppConfigParams{}, false, err
	}
	storedWebhookSecret, err := readString(githubConfigWebhookSecretKey)
	if err != nil {
		return provider.UpsertGitHubAppConfigParams{}, false, err
	}
	ownerLogin, err := readString(githubConfigOwnerLoginKey)
	if err != nil {
		return provider.UpsertGitHubAppConfigParams{}, false, err
	}
	clientSecret, err := store.decryptGitHubAppConfigProposalSecret(storedClientSecret, true)
	if err != nil {
		return provider.UpsertGitHubAppConfigParams{}, false, err
	}
	privateKey, err := store.decryptGitHubAppConfigProposalSecret(storedPrivateKey, false)
	if err != nil {
		return provider.UpsertGitHubAppConfigParams{}, false, err
	}
	webhookSecret, err := store.decryptGitHubAppConfigProposalSecret(storedWebhookSecret, false)
	if err != nil {
		return provider.UpsertGitHubAppConfigParams{}, false, err
	}
	appIDText, err := readString(githubConfigAppIDKey)
	if err != nil {
		return provider.UpsertGitHubAppConfigParams{}, false, err
	}
	ownerIDText, err := readString(githubConfigOwnerIDKey)
	if err != nil {
		return provider.UpsertGitHubAppConfigParams{}, false, err
	}
	appID, err := strconv.ParseInt(appIDText, 10, 64)
	if err != nil || appID <= 0 || strconv.FormatInt(appID, 10) != appIDText {
		return provider.UpsertGitHubAppConfigParams{}, false, conflict("GitHub setup proposal is invalid. Restart setup.")
	}
	ownerID := int64(0)
	if ownerIDText != "" {
		ownerID, err = strconv.ParseInt(ownerIDText, 10, 64)
		if err != nil || ownerID < 0 || strconv.FormatInt(ownerID, 10) != ownerIDText {
			return provider.UpsertGitHubAppConfigParams{}, false, conflict("GitHub setup proposal is invalid. Restart setup.")
		}
	}
	if appID <= 0 || appSlug == "" || appName == "" || privateKey == "" || webhookSecret == "" {
		return provider.UpsertGitHubAppConfigParams{}, false, conflict("GitHub setup proposal is incomplete. Restart setup.")
	}
	return provider.UpsertGitHubAppConfigParams{
		AppID:         appID,
		AppSlug:       appSlug,
		AppName:       appName,
		HTMLURL:       htmlURL,
		ClientID:      clientID,
		ClientSecret:  clientSecret,
		PrivateKeyPEM: privateKey,
		WebhookSecret: webhookSecret,
		OwnerLogin:    ownerLogin,
		OwnerID:       ownerID,
	}, true, nil
}

func (store *Store) decryptGitHubAppConfigProposalSecret(stored string, optional bool) (string, error) {
	trimmed := strings.TrimSpace(stored)
	if trimmed == "" && optional {
		return "", nil
	}
	var envelope model.CredentialEnvelope
	if err := model.UnmarshalJSONString("GitHub setup proposal secret envelope", trimmed, &envelope); err != nil ||
		strings.TrimSpace(envelope.Storage) != appCredentialStorageScheme || strings.TrimSpace(envelope.Ciphertext) == "" {
		return "", conflict("GitHub setup proposal credentials are invalid. Restart setup.")
	}
	plaintext, err := store.decryptGitHubAppConfigSecret(trimmed)
	if err != nil || strings.TrimSpace(plaintext) == "" {
		return "", conflict("GitHub setup proposal credentials cannot be decrypted. Restart setup.")
	}
	return plaintext, nil
}

func appInstallationRequestPublicGitHubConfig(stored map[string]any) map[string]any {
	// This is a security boundary, not a redaction pass over an otherwise
	// public document. Build a new allowlisted projection so a future internal
	// execution field (or a corrupted row) cannot silently become browser- or
	// card-visible. Metadata is copied only when it has the expected scalar
	// shape; provider credentials are represented solely by an empty value or
	// the fixed mask.
	public := make(map[string]any, len(githubConfigRequestKeys))
	for _, key := range []string{
		githubConfigAppSlugKey,
		githubConfigAppNameKey,
		githubConfigHTMLURLKey,
		githubConfigClientIDKey,
		githubConfigOwnerLoginKey,
	} {
		value, ok := stored[key].(string)
		if !ok {
			continue
		}
		public[key] = strings.TrimSpace(value)
	}
	for _, key := range []string{githubConfigAppIDKey, githubConfigOwnerIDKey} {
		value, ok := stored[key].(string)
		if !ok {
			continue
		}
		value = strings.TrimSpace(value)
		parsed, err := strconv.ParseInt(value, 10, 64)
		if err == nil && strconv.FormatInt(parsed, 10) == value &&
			(parsed > 0 || key == githubConfigOwnerIDKey && parsed == 0) {
			public[key] = value
		}
	}
	for _, key := range []string{githubConfigClientSecretKey, githubConfigPrivateKeyPEMKey, githubConfigWebhookSecretKey} {
		raw, exists := stored[key]
		if !exists {
			continue
		}
		value, isString := raw.(string)
		if isString && strings.TrimSpace(value) == "" {
			public[key] = ""
			continue
		}
		public[key] = githubConfigMaskedSecret
	}
	return public
}

func githubSetupProposalRequiresFirstConnection(request AppInstallationRequestView) bool {
	required, ok := appInstallationRequestStoredBaseConfig(request)[githubSetupRequireFirstConnectionKey].(bool)
	return ok && required
}

func githubSetupProposalView(request AppInstallationRequestView) GitHubSetupProposalView {
	return GitHubSetupProposalView{
		ID:         strings.TrimSpace(request.ID),
		Ready:      strings.TrimSpace(request.Status) == AppInstallationRequestStatusSetupReady,
		AppID:      requestSnapshotString(request.RequestedConfig, githubConfigAppIDKey),
		AppSlug:    requestSnapshotString(request.RequestedConfig, githubConfigAppSlugKey),
		AppName:    requestSnapshotString(request.RequestedConfig, githubConfigAppNameKey),
		OwnerLogin: requestSnapshotString(request.RequestedConfig, githubConfigOwnerLoginKey),
		ExpiresAt:  request.CreatedAt.UTC().Add(githubSetupProposalTTL),
	}
}

func loadGitHubSetupProposalForUpdate(ctx context.Context, queryer coredb.QueryRower, workspaceID string, proposalID string, memberID string) (AppInstallationRequestView, error) {
	query := appInstallationRequestSelectSQL() + `
WHERE req.workspace_id = $1
  AND req.id = $2
  AND req.service_definition_id = $3
	AND req.service_kind = $4
	AND req.request_kind = $5
	AND req.requested_by_member_id = $6
	AND (
		(req.status = $7 AND req.request_reason = '')
		OR req.status = $8
	)
FOR UPDATE`
	request, err := scanAppInstallationRequest(queryer.QueryRow(
		ctx, query, strings.TrimSpace(workspaceID), strings.TrimSpace(proposalID), registry.GitHubAppID,
		serviceactor.KindApp, AppInstallationRequestKindInstall, strings.TrimSpace(memberID),
		AppInstallationRequestStatusSetupReady, AppInstallationRequestStatusPending,
	))
	if err != nil {
		if coredb.IsNotFound(err) {
			return AppInstallationRequestView{}, ErrNotFound
		}
		return AppInstallationRequestView{}, fmt.Errorf("load GitHub setup proposal for update: %w", err)
	}
	return request, nil
}

func (store *Store) revalidateGitHubSetupRequesterForUpdate(
	ctx context.Context,
	queryer coredb.QueryRower,
	userID string,
	workspaceID string,
	expected access.HumanWorkspaceMember,
) (access.HumanWorkspaceMember, bool, error) {
	current, err := store.loadAuthorizedHumanWorkspaceMemberForUpdate(
		ctx, queryer, userID, workspaceID, workspaceAccessAction,
	)
	if err != nil {
		return access.HumanWorkspaceMember{}, false, err
	}
	currentUserID := ""
	if current.Member.UserID != nil {
		currentUserID = strings.TrimSpace(*current.Member.UserID)
	}
	expectedUserID := ""
	if expected.Member.UserID != nil {
		expectedUserID = strings.TrimSpace(*expected.Member.UserID)
	}
	if strings.TrimSpace(current.Member.ID) != strings.TrimSpace(expected.Member.ID) ||
		strings.TrimSpace(current.Member.WorkspaceID) != strings.TrimSpace(workspaceID) ||
		currentUserID != strings.TrimSpace(userID) ||
		expectedUserID != strings.TrimSpace(userID) ||
		strings.TrimSpace(expected.Member.WorkspaceID) != strings.TrimSpace(workspaceID) {
		return access.HumanWorkspaceMember{}, false, conflict("App installation requester membership changed.")
	}
	elevated := access.HasWorkspaceAdminRole(domain.WorkspaceRole(current.WorkspaceRole))
	return current, elevated, nil
}

func githubSetupBlockedForMember(ctx context.Context, queryer coredb.QueryRower, workspaceID string, forUpdate bool) (bool, error) {
	installationQuery := `
SELECT id
FROM service_installations
WHERE workspace_id = $1
  AND service_definition_id = $2
  AND service_kind = $3
  AND instance_key = $4
  AND status = $5
LIMIT 1`
	if forUpdate {
		installationQuery += "\nFOR UPDATE"
	}
	var installationID string
	if err := queryer.QueryRow(ctx, installationQuery, strings.TrimSpace(workspaceID), registry.GitHubAppID,
		serviceactor.KindApp, serviceactor.DefaultInstanceKey, serviceactor.StatusActive).Scan(&installationID); err == nil {
		return true, nil
	} else if !coredb.IsNotFound(err) {
		return false, fmt.Errorf("load active GitHub installation: %w", err)
	}
	configured, err := githubAppConfigExistsForUpdate(ctx, queryer, workspaceID, forUpdate)
	if err != nil {
		return false, err
	}
	if configured {
		return true, nil
	}
	claimQuery := `
SELECT provider_app_id
FROM service_provider_workspace_claims
WHERE workspace_id = $1
  AND claim_state IN ($2, $3)
LIMIT 1`
	if forUpdate {
		claimQuery += "\nFOR UPDATE"
	}
	var claimedAppID int64
	if err := queryer.QueryRow(
		ctx, claimQuery, strings.TrimSpace(workspaceID),
		githubAppClaimStatePatching, githubAppClaimStateActive,
	).Scan(&claimedAppID); err == nil {
		return true, nil
	} else if !coredb.IsNotFound(err) {
		return false, fmt.Errorf("load GitHub activation claim: %w", err)
	}
	return false, nil
}

func settleOrphanedGitHubConnectionRequestsInTx(
	ctx context.Context,
	tx coredb.Tx,
	workspaceID string,
	now time.Time,
) error {
	rows, err := tx.Query(ctx, `
SELECT id
FROM service_installation_requests
WHERE workspace_id = $1
  AND service_definition_id = $2
  AND service_kind = $3
  AND request_kind IN ($4, $5)
  AND status = $6
  AND approval_required = TRUE
ORDER BY created_at ASC, id ASC
FOR UPDATE
`, strings.TrimSpace(workspaceID), registry.GitHubAppID, serviceactor.KindApp,
		AppInstallationRequestKindConnect, AppInstallationRequestKindDisconnect,
		AppInstallationRequestStatusPending)
	if err != nil {
		return fmt.Errorf("list orphaned GitHub connection requests: %w", err)
	}
	requestIDs := make([]string, 0)
	for rows.Next() {
		var requestID string
		if err := rows.Scan(&requestID); err != nil {
			rows.Close()
			return fmt.Errorf("scan orphaned GitHub connection request: %w", err)
		}
		requestIDs = append(requestIDs, strings.TrimSpace(requestID))
	}
	if err := rows.Err(); err != nil {
		rows.Close()
		return fmt.Errorf("iterate orphaned GitHub connection requests: %w", err)
	}
	rows.Close()

	for _, requestID := range requestIDs {
		request, err := loadAppInstallationRequest(ctx, tx, workspaceID, requestID)
		if err != nil {
			return err
		}
		if err := settleKnownAppInstallationRequestAsSystemRejectedInTx(
			ctx, tx, request, orphanedGitHubConnectionRequestDecisionReason, now,
		); err != nil {
			return err
		}
	}
	return nil
}

func githubAppConfigExistsForUpdate(ctx context.Context, queryer coredb.QueryRower, workspaceID string, forUpdate bool) (bool, error) {
	query := `
SELECT id
FROM service_provider_configs
WHERE workspace_id = $1
  AND provider_kind = 'github'
LIMIT 1`
	if forUpdate {
		query += "\nFOR UPDATE"
	}
	var configID string
	if err := queryer.QueryRow(ctx, query, strings.TrimSpace(workspaceID)).Scan(&configID); err != nil {
		if coredb.IsNotFound(err) {
			return false, nil
		}
		return false, fmt.Errorf("load GitHub App config state: %w", err)
	}
	return strings.TrimSpace(configID) != "", nil
}

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