package app

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

	"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 githubAdminSetupCompletionReasonPrefix = "admin_complete:"

var errGitHubAdminSetupDraftExpired = errors.New("github admin setup draft expired")

// GetGitHubSetupDraftForRequester returns the authenticated ordinary member's
// unsubmitted credential-reuse draft and decrypts its config only for
// server-side provider discovery. The caller, workspace, mode, TTL, active /
// patching winner, and mandatory-initial-connection-set marker are revalidated
// under the canonical GitHub definition lock before any credential leaves the
// store boundary. Drafts themselves hold no ownership claim.
func (store *Store) GetGitHubSetupDraftForRequester(
	ctx context.Context,
	userID string,
	workspaceID string,
	draftID string,
) (GitHubSetupProposalView, provider.GitHubAppConfig, error) {
	expected, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return GitHubSetupProposalView{}, provider.GitHubAppConfig{}, err
	}
	if err := requireGitHubSetupMode(expected, GitHubAppSetupModeRequester); err != nil {
		return GitHubSetupProposalView{}, provider.GitHubAppConfig{}, err
	}
	trimmedDraftID := strings.TrimSpace(draftID)
	if trimmedDraftID == "" {
		return GitHubSetupProposalView{}, provider.GitHubAppConfig{}, badRequest("GitHub setup proposal id is required.")
	}

	var (
		draft   AppInstallationRequestView
		params  provider.UpsertGitHubAppConfigParams
		expired bool
	)
	err = coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := installation.LockAppInstallationMutationScope(ctx, tx, registry.GitHubAppID); err != nil {
			return err
		}
		draft, err = loadGitHubRequesterReuseDraftForUpdate(
			ctx, tx, workspaceID, trimmedDraftID, expected.Member.ID,
		)
		if err != nil {
			return err
		}
		current, elevated, err := store.revalidateGitHubSetupRequesterForUpdate(
			ctx, tx, userID, workspaceID, expected,
		)
		if err != nil {
			return err
		}
		if elevated {
			return ErrForbidden
		}
		if strings.TrimSpace(current.Member.ID) != strings.TrimSpace(expected.Member.ID) {
			return conflict("GitHub setup requester membership changed.")
		}
		if draft.CreatedAt.Before(time.Now().UTC().Add(-githubSetupProposalTTL)) {
			expired = true
			return deleteGitHubSetupProposalByIDInTx(
				ctx, tx, workspaceID, trimmedDraftID, expected.Member.ID,
			)
		}
		if !githubSetupProposalRequiresFirstConnection(draft) {
			return conflict("GitHub setup proposal does not support installation discovery.")
		}
		params, _, err = store.verifiedGitHubAppConfigFromStoredMap(
			appInstallationRequestStoredRequestedConfig(draft),
		)
		if err != nil {
			return err
		}
		if params.AppID <= 0 {
			return conflict("GitHub setup proposal is missing. Restart setup.")
		}
		blocked, err := githubSetupBlockedForMember(ctx, tx, workspaceID, true)
		if err != nil {
			return err
		}
		if blocked {
			return conflict("GitHub is already active or another setup has started provider mutation for this workspace.")
		}
		return nil
	})
	if err != nil {
		return GitHubSetupProposalView{}, provider.GitHubAppConfig{}, err
	}
	if expired {
		return GitHubSetupProposalView{}, provider.GitHubAppConfig{}, gone("GitHub setup proposal expired. Restart setup.")
	}
	return githubSetupProposalView(draft), githubAppConfigFromSetupParams(workspaceID, params), nil
}

func loadGitHubRequesterReuseDraftForUpdate(
	ctx context.Context,
	queryer coredb.QueryRower,
	workspaceID string,
	draftID 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 = ''
FOR UPDATE`
	draft, err := scanAppInstallationRequest(queryer.QueryRow(
		ctx, query, strings.TrimSpace(workspaceID), strings.TrimSpace(draftID),
		registry.GitHubAppID, serviceactor.KindApp, AppInstallationRequestKindInstall,
		strings.TrimSpace(memberID), AppInstallationRequestStatusSetupReady,
	))
	if err != nil {
		if coredb.IsNotFound(err) {
			return AppInstallationRequestView{}, ErrNotFound
		}
		return AppInstallationRequestView{}, fmt.Errorf("load GitHub requester setup draft: %w", err)
	}
	return draft, nil
}

// GetGitHubSetupDraftForWorkspaceAdmin returns an administrator-owned fresh
// setup draft and its decrypted config for server-side provider discovery.
// The config must never be serialized to the browser.
func (store *Store) GetGitHubSetupDraftForWorkspaceAdmin(
	ctx context.Context,
	userID string,
	workspaceID string,
	draftID string,
) (GitHubSetupProposalView, provider.GitHubAppConfig, error) {
	expected, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return GitHubSetupProposalView{}, provider.GitHubAppConfig{}, err
	}
	if err := requireGitHubSetupMode(expected, GitHubAppSetupModeAdmin); err != nil {
		return GitHubSetupProposalView{}, provider.GitHubAppConfig{}, err
	}
	if strings.TrimSpace(draftID) == "" {
		return GitHubSetupProposalView{}, provider.GitHubAppConfig{}, badRequest("GitHub setup draft id is required.")
	}

	var (
		draft   AppInstallationRequestView
		params  provider.UpsertGitHubAppConfigParams
		expired bool
	)
	err = coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := installation.LockAppInstallationMutationScope(ctx, tx, registry.GitHubAppID); err != nil {
			return err
		}
		draft, params, err = store.loadFreshAdminGitHubSetupDraftForUpdate(
			ctx, tx, userID, workspaceID, strings.TrimSpace(draftID), expected,
		)
		if errors.Is(err, errGitHubAdminSetupDraftExpired) {
			expired = true
			return deleteGitHubSetupProposalByIDInTx(
				ctx, tx, workspaceID, strings.TrimSpace(draftID), expected.Member.ID,
			)
		}
		return err
	})
	if err != nil {
		return GitHubSetupProposalView{}, provider.GitHubAppConfig{}, err
	}
	if expired {
		return GitHubSetupProposalView{}, provider.GitHubAppConfig{}, gone("GitHub setup draft expired. Restart setup.")
	}
	return githubSetupProposalView(draft), githubAppConfigFromSetupParams(workspaceID, params), nil
}

// BeginGitHubSetupCompletionForWorkspaceAdmin atomically binds one fresh
// administrator draft to a canonical set of selected installations and creates the
// short-lived workspace mutation fence used during exact provider validation
// and webhook PATCH. Repeating the same selection reuses the same lease and
// webhook secret; attempting to switch selections is rejected.
func (store *Store) BeginGitHubSetupCompletionForWorkspaceAdmin(
	ctx context.Context,
	userID string,
	workspaceID string,
	draftID string,
	installationIDs []string,
) (provider.GitHubAppConfig, GitHubSetupCompletionLease, error) {
	trimmedDraftID := strings.TrimSpace(draftID)
	canonicalInstallationIDs, err := normalizedGitHubInstallationIDs(installationIDs)
	if err != nil {
		return provider.GitHubAppConfig{}, GitHubSetupCompletionLease{}, err
	}
	if trimmedDraftID == "" {
		return provider.GitHubAppConfig{}, GitHubSetupCompletionLease{}, badRequest("GitHub setup draft id is required.")
	}
	expected, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return provider.GitHubAppConfig{}, GitHubSetupCompletionLease{}, err
	}
	leaseID, err := identifiers.NewEntityID("ghverify")
	if err != nil {
		return provider.GitHubAppConfig{}, GitHubSetupCompletionLease{}, fmt.Errorf("generate GitHub setup completion lease id: %w", err)
	}

	var (
		params  provider.UpsertGitHubAppConfigParams
		lease   GitHubSetupCompletionLease
		expired bool
	)
	err = coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := installation.LockAppInstallationMutationScope(ctx, tx, registry.GitHubAppID); err != nil {
			return err
		}
		now := time.Now().UTC()
		if err := deleteExpiredGitHubSetupVerificationsInTx(
			ctx, tx, workspaceID, now.Add(-githubSetupVerificationTTL),
		); err != nil {
			return err
		}
		reason := githubAdminSetupCompletionReason(trimmedDraftID, canonicalInstallationIDs)
		existing, found, err := loadGitHubSetupCompletionLeaseForUpdate(
			ctx, tx, workspaceID, expected.Member.ID, reason,
		)
		if err != nil {
			return err
		}
		if found {
			if _, err := store.revalidateGitHubSetupMemberForUpdate(
				ctx, tx, userID, workspaceID, expected,
			); err != nil {
				return err
			}
			params, err = store.validateAdminGitHubSetupCompletionSnapshotForUpdate(
				ctx, tx, workspaceID, existing,
			)
			if err != nil {
				return err
			}
			switch strings.TrimSpace(existing.Status) {
			case AppInstallationRequestStatusSetupVerifying:
				// A same-secret/same-selection pre-PATCH retry gets a fresh window.
				createdAt := now.Truncate(time.Second)
				if _, err := tx.Exec(ctx, `
UPDATE service_installation_requests
SET created_at = $1,
    updated_at = $1
WHERE id = $2
  AND workspace_id = $3
  AND status = $4
  AND request_reason = $5
`, createdAt, existing.ID, strings.TrimSpace(workspaceID),
					AppInstallationRequestStatusSetupVerifying, reason); err != nil {
					return fmt.Errorf("renew GitHub setup completion lease: %w", err)
				}
			case AppInstallationRequestStatusSetupPatching:
				if err := requireGitHubAppPatchingClaimForUpdate(
					ctx, tx, params.AppID, workspaceID, existing.ID,
				); err != nil {
					return err
				}
			default:
				return conflict("GitHub setup completion state changed. Restart setup.")
			}
			lease = GitHubSetupCompletionLease{
				ID:              existing.ID,
				DraftID:         trimmedDraftID,
				InstallationID:  canonicalInstallationIDs[0],
				InstallationIDs: append([]string(nil), canonicalInstallationIDs...),
			}
			return nil
		}
		current, err := store.revalidateGitHubSetupMemberForUpdate(ctx, tx, userID, workspaceID, expected)
		if err != nil {
			return err
		}
		if err := requireGitHubSetupMode(current, GitHubAppSetupModeAdmin); err != nil {
			return err
		}

		draft, draftParams, err := store.loadFreshAdminGitHubSetupDraftForUpdate(
			ctx, tx, userID, workspaceID, trimmedDraftID, current,
		)
		if errors.Is(err, errGitHubAdminSetupDraftExpired) {
			expired = true
			return deleteGitHubSetupProposalByIDInTx(
				ctx, tx, workspaceID, trimmedDraftID, expected.Member.ID,
			)
		}
		if err != nil {
			return err
		}
		params = draftParams
		if err := requireNoGitHubSetupVerificationInTx(ctx, tx, workspaceID); err != nil {
			return err
		}
		requestedConfigJSON, err := model.MarshalJSONObject(
			"GitHub setup completion config", appInstallationRequestStoredRequestedConfig(draft),
		)
		if err != nil {
			return fmt.Errorf("encode GitHub setup completion config: %w", err)
		}
		baseConfigJSON, err := model.MarshalJSONObject(
			"GitHub setup completion base config", appInstallationRequestStoredBaseConfig(draft),
		)
		if err != nil {
			return fmt.Errorf("encode GitHub setup completion base config: %w", err)
		}
		requestedScopesJSON, err := model.MarshalJSONObject(
			"GitHub setup completion scopes", registry.SortedUniqueScopes(draft.RequestedScopes),
		)
		if err != nil {
			return fmt.Errorf("encode GitHub setup completion scopes: %w", err)
		}
		createdAt := now.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
)
`, leaseID, strings.TrimSpace(workspaceID), registry.GitHubAppID, serviceactor.KindApp,
			AppInstallationRequestKindInstall, strings.TrimSpace(draft.RequestedVersion), requestedConfigJSON,
			baseConfigJSON, requestedScopesJSON, AppInstallationRequestStatusSetupVerifying, reason,
			expected.Member.ID, createdAt); err != nil {
			return fmt.Errorf("create GitHub setup completion lease: %w", err)
		}
		lease = GitHubSetupCompletionLease{
			ID:              leaseID,
			DraftID:         trimmedDraftID,
			InstallationID:  canonicalInstallationIDs[0],
			InstallationIDs: append([]string(nil), canonicalInstallationIDs...),
		}
		return nil
	})
	if err != nil {
		return provider.GitHubAppConfig{}, GitHubSetupCompletionLease{}, err
	}
	if expired {
		return provider.GitHubAppConfig{}, GitHubSetupCompletionLease{}, gone("GitHub setup draft expired. Restart setup.")
	}
	return githubAppConfigFromSetupParams(workspaceID, params), lease, nil
}

// MarkGitHubSetupCompletionProviderMutationStartedForWorkspaceAdmin enters the
// durable point-of-no-return state immediately before PATCH. The caller/member
// binding is retained, but administrator authorization was already linearized
// by Begin and is intentionally not re-evaluated here or during Finalize.
func (store *Store) MarkGitHubSetupCompletionProviderMutationStartedForWorkspaceAdmin(
	ctx context.Context,
	userID string,
	workspaceID string,
	lease GitHubSetupCompletionLease,
	targets []GitHubSetupCompletionTarget,
) error {
	canonicalInstallationIDs, err := githubSetupCompletionLeaseInstallationIDs(lease)
	if err != nil {
		return err
	}
	verifiedTargets, err := canonicalGitHubSetupCompletionTargets(targets)
	if err != nil {
		return err
	}
	if !slices.Equal(canonicalInstallationIDs, githubConnectionTargetIDs(verifiedTargets)) {
		return conflict("GitHub setup completion selection changed. Restart setup.")
	}
	expected, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return err
	}
	expired := 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 := store.revalidateGitHubSetupMemberForUpdate(
			ctx, tx, userID, workspaceID, expected,
		); err != nil {
			return err
		}
		completion, err := loadGitHubSetupCompletionLeaseByIDForUpdate(
			ctx, tx, workspaceID, expected.Member.ID, strings.TrimSpace(lease.ID),
			githubAdminSetupCompletionReason(lease.DraftID, canonicalInstallationIDs),
		)
		if err != nil {
			return err
		}
		params, err := store.validateAdminGitHubSetupCompletionSnapshotForUpdate(
			ctx, tx, workspaceID, completion,
		)
		if err != nil {
			return err
		}
		now := time.Now().UTC()
		switch strings.TrimSpace(completion.Status) {
		case AppInstallationRequestStatusSetupPatching:
			storedTargets, err := parseGitHubConnectionTargetSnapshots(
				appInstallationRequestStoredRequestedConfig(completion),
			)
			if err != nil || !sameGitHubConnectionTargetIdentities(storedTargets, verifiedTargets) {
				return conflict("GitHub installation identity changed after provider mutation. Operator recovery is required.")
			}
			if err := requireGitHubAppPatchingClaimForUpdate(
				ctx, tx, params.AppID, workspaceID, completion.ID,
			); err != nil {
				return err
			}
			if slices.Equal(storedTargets, verifiedTargets) {
				return nil
			}
			// Organization login is mutable. Once PATCH may already have
			// succeeded, immutable installation/account identity remains the
			// authority and a rename refreshes only the safe label snapshot.
			storedConfig := cloneAppInstallationRequestConfig(
				appInstallationRequestStoredRequestedConfig(completion),
			)
			storedConfig[githubConnectionTargetsSnapshotKey] = githubConnectionTargetSnapshotMap(verifiedTargets)
			requestedConfigJSON, err := model.MarshalJSONObject(
				"renamed GitHub setup completion targets", storedConfig,
			)
			if err != nil {
				return fmt.Errorf("encode renamed GitHub setup completion targets: %w", err)
			}
			result, err := tx.Exec(ctx, `
UPDATE service_installation_requests
SET requested_config = $1,
    updated_at = $2
WHERE id = $3
  AND workspace_id = $4
  AND requested_by_member_id = $5
  AND status = $6
`, requestedConfigJSON, now, completion.ID, strings.TrimSpace(workspaceID),
				expected.Member.ID, AppInstallationRequestStatusSetupPatching)
			if err != nil {
				return fmt.Errorf("refresh renamed GitHub setup completion target: %w", err)
			}
			if result.RowsAffected() != 1 {
				return conflict("GitHub setup completion state changed. Retry setup.")
			}
			return nil
		case AppInstallationRequestStatusSetupVerifying:
			if completion.CreatedAt.Before(now.Add(-githubSetupVerificationTTL)) {
				expired = true
				return deleteGitHubSetupVerificationByIDInTx(
					ctx, tx, workspaceID, completion.ID, expected.Member.ID,
				)
			}
		default:
			return conflict("GitHub setup completion state changed. Restart setup.")
		}
		if err := markGitHubAppClaimPatchingInTx(
			ctx, tx, params.AppID, workspaceID, completion.ID, false, now,
		); err != nil {
			return err
		}
		storedConfig := cloneAppInstallationRequestConfig(
			appInstallationRequestStoredRequestedConfig(completion),
		)
		if existingTargets, err := parseGitHubConnectionTargetSnapshots(storedConfig); err != nil {
			return err
		} else if len(existingTargets) > 0 {
			return conflict("GitHub setup completion target snapshot changed. Restart setup.")
		}
		storedConfig[githubConnectionTargetsSnapshotKey] = githubConnectionTargetSnapshotMap(verifiedTargets)
		requestedConfigJSON, err := model.MarshalJSONObject(
			"GitHub setup completion verified targets", storedConfig,
		)
		if err != nil {
			return fmt.Errorf("encode GitHub setup completion verified targets: %w", err)
		}
		result, err := tx.Exec(ctx, `
UPDATE service_installation_requests
SET status = $1,
    requested_config = $2,
    updated_at = $3
WHERE id = $4
  AND workspace_id = $5
  AND requested_by_member_id = $6
  AND status = $7
`, AppInstallationRequestStatusSetupPatching, requestedConfigJSON, now, completion.ID,
			strings.TrimSpace(workspaceID), expected.Member.ID,
			AppInstallationRequestStatusSetupVerifying)
		if err != nil {
			return fmt.Errorf("mark GitHub setup completion provider mutation started: %w", err)
		}
		if result.RowsAffected() != 1 {
			return conflict("GitHub setup completion state changed. Restart setup.")
		}
		return nil
	}); err != nil {
		return err
	}
	if expired {
		return gone("GitHub setup completion expired. Retry the setup draft.")
	}
	return nil
}

// FinalizeGitHubSetupCompletionForWorkspaceAdmin atomically materializes the
// AgentTeam App, active config, the complete selected connection set, and matching
// subscription unblocks, then consumes both the draft and short lease.
func (store *Store) FinalizeGitHubSetupCompletionForWorkspaceAdmin(
	ctx context.Context,
	userID string,
	workspaceID string,
	lease GitHubSetupCompletionLease,
) (provider.AppInstallationView, error) {
	canonicalInstallationIDs, err := githubSetupCompletionLeaseInstallationIDs(lease)
	if err != nil {
		return provider.AppInstallationView{}, err
	}
	expected, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return provider.AppInstallationView{}, err
	}

	var installationID string
	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 := store.revalidateGitHubSetupMemberForUpdate(
			ctx, tx, userID, workspaceID, expected,
		); err != nil {
			return err
		}
		completion, err := loadGitHubSetupCompletionLeaseByIDForUpdate(
			ctx, tx, workspaceID, expected.Member.ID, strings.TrimSpace(lease.ID),
			githubAdminSetupCompletionReason(lease.DraftID, canonicalInstallationIDs),
		)
		if err != nil {
			return err
		}
		if strings.TrimSpace(completion.Status) != AppInstallationRequestStatusSetupPatching {
			return conflict("GitHub setup completion has not entered provider mutation. Retry setup.")
		}
		now := time.Now().UTC()
		completionParams, err := store.validateAdminGitHubSetupCompletionSnapshotForUpdate(
			ctx, tx, workspaceID, completion,
		)
		if err != nil {
			return err
		}
		if err := requireGitHubAppPatchingClaimForUpdate(
			ctx, tx, completionParams.AppID, workspaceID, completion.ID,
		); err != nil {
			return err
		}
		verifiedTargets, err := parseGitHubConnectionTargetSnapshots(
			appInstallationRequestStoredRequestedConfig(completion),
		)
		if err != nil || !slices.Equal(
			githubConnectionTargetIDs(verifiedTargets), canonicalInstallationIDs,
		) {
			return conflict("GitHub setup completion targets changed. Operator recovery is required.")
		}

		installationID, err = store.finalizeGitHubAppSetupForWorkspaceAdminInTx(
			ctx, tx, userID, workspaceID, expected.Member.ID, completionParams,
			completion.RequestedVersion, completion.ID, now,
		)
		if err != nil {
			return err
		}
		for _, target := range verifiedTargets {
			if _, err := store.upsertAppConnectionInTx(ctx, tx, UpsertAppConnectionParams{
				InstallationID:     installationID,
				ExternalSubjectRef: target.InstallationID,
				DisplayLabel:       target.AccountLogin,
				Credentials: map[string]any{
					"webhook_secret": completionParams.WebhookSecret,
				},
				CreatedByUserID: strings.TrimSpace(userID),
			}, "", now); err != nil {
				return err
			}
			if err := store.setGitHubServiceEventSubscriptionsBlockedInTx(
				ctx, tx, workspaceID, target.InstallationID, false, now,
			); err != nil {
				return err
			}
		}
		connections, err := store.serviceActorStore().InTx(tx).ListActiveConnections(ctx, installationID)
		if err != nil {
			return err
		}
		actualConnectionIDs := make([]string, 0, len(connections))
		for _, connection := range connections {
			actualConnectionIDs = append(actualConnectionIDs, strings.TrimSpace(connection.ExternalSubjectRef))
		}
		actualConnectionIDs, err = normalizedGitHubInstallationIDs(actualConnectionIDs)
		if err != nil || !slices.Equal(actualConnectionIDs, canonicalInstallationIDs) {
			return conflict("GitHub connection state is inconsistent. Retry setup.")
		}
		return deleteGitHubSetupMutationByIDInTx(
			ctx, tx, workspaceID, completion.ID, expected.Member.ID,
			AppInstallationRequestStatusSetupPatching,
		)
	})
	if err != nil {
		return provider.AppInstallationView{}, err
	}
	// The commit above is authoritative. Do not let an exhausted provider-phase
	// deadline turn a successful local commit into a spurious response failure.
	readCtx, cancel := context.WithTimeout(context.WithoutCancel(ctx), 5*time.Second)
	defer cancel()
	installed, err := store.appInstallationViewByID(readCtx, workspaceID, installationID)
	if err != nil {
		return provider.AppInstallationView{}, err
	}
	return providerAppInstallationFromStore(installed), nil
}

// validateAdminGitHubSetupCompletionSnapshotForUpdate treats the completion
// row as a self-contained encrypted operation snapshot. Authorization is
// linearized at Begin; role or draft changes cannot destroy post-PATCH recovery.
func (store *Store) validateAdminGitHubSetupCompletionSnapshotForUpdate(
	ctx context.Context,
	tx coredb.Tx,
	workspaceID string,
	completion AppInstallationRequestView,
) (provider.UpsertGitHubAppConfigParams, error) {
	if strings.TrimSpace(completion.Status) != AppInstallationRequestStatusSetupPatching {
		blocked, err := githubSetupBlockedForMember(ctx, tx, workspaceID, true)
		if err != nil {
			return provider.UpsertGitHubAppConfigParams{}, err
		}
		if blocked {
			return provider.UpsertGitHubAppConfigParams{}, conflict("GitHub setup state changed. Restart setup.")
		}
	}
	if err := validateGitHubSetupVerificationConfigSnapshot(ctx, tx, workspaceID, completion); err != nil {
		return provider.UpsertGitHubAppConfigParams{}, err
	}
	prepared, err := prepareAppInstallationAtVersion(
		ctx, tx, registry.GitHubAppID, registry.GitHubAppSlug, completion.RequestedVersion, nil,
	)
	if err != nil {
		return provider.UpsertGitHubAppConfigParams{}, err
	}
	if strings.TrimSpace(prepared.version.Version) != strings.TrimSpace(completion.RequestedVersion) ||
		!equalSortedScopes(prepared.version.Scopes, completion.RequestedScopes) {
		return provider.UpsertGitHubAppConfigParams{}, conflict("GitHub App definition changed. Restart setup.")
	}
	params, found, err := store.verifiedGitHubAppConfigFromStoredMap(
		appInstallationRequestStoredRequestedConfig(completion),
	)
	if err != nil {
		return provider.UpsertGitHubAppConfigParams{}, err
	}
	if !found {
		return provider.UpsertGitHubAppConfigParams{}, conflict("GitHub setup completion is missing. Restart setup.")
	}
	return params, nil
}

func (store *Store) loadFreshAdminGitHubSetupDraftForUpdate(
	ctx context.Context,
	tx coredb.Tx,
	userID string,
	workspaceID string,
	draftID string,
	expected access.HumanWorkspaceMember,
) (AppInstallationRequestView, provider.UpsertGitHubAppConfigParams, error) {
	draft, err := loadGitHubAdminSetupDraftForUpdate(
		ctx, tx, workspaceID, draftID, expected.Member.ID,
	)
	if err != nil {
		return AppInstallationRequestView{}, provider.UpsertGitHubAppConfigParams{}, err
	}
	current, err := store.revalidateGitHubSetupMemberForUpdate(ctx, tx, userID, workspaceID, expected)
	if err != nil {
		return AppInstallationRequestView{}, provider.UpsertGitHubAppConfigParams{}, err
	}
	if err := requireGitHubSetupMode(current, GitHubAppSetupModeAdmin); err != nil {
		return AppInstallationRequestView{}, provider.UpsertGitHubAppConfigParams{}, err
	}
	if draft.CreatedAt.Before(time.Now().UTC().Add(-githubSetupProposalTTL)) {
		return AppInstallationRequestView{}, provider.UpsertGitHubAppConfigParams{}, errGitHubAdminSetupDraftExpired
	}
	blocked, err := githubSetupBlockedForMember(ctx, tx, workspaceID, true)
	if err != nil {
		return AppInstallationRequestView{}, provider.UpsertGitHubAppConfigParams{}, err
	}
	if blocked {
		return AppInstallationRequestView{}, provider.UpsertGitHubAppConfigParams{}, conflict("GitHub setup state changed. Restart setup.")
	}
	if err := validateGitHubSetupVerificationConfigSnapshot(ctx, tx, workspaceID, draft); err != nil {
		return AppInstallationRequestView{}, provider.UpsertGitHubAppConfigParams{}, err
	}
	prepared, err := prepareAppInstallationAtVersion(
		ctx, tx, registry.GitHubAppID, registry.GitHubAppSlug, draft.RequestedVersion, nil,
	)
	if err != nil {
		return AppInstallationRequestView{}, provider.UpsertGitHubAppConfigParams{}, err
	}
	if strings.TrimSpace(prepared.version.Version) != strings.TrimSpace(draft.RequestedVersion) ||
		!equalSortedScopes(prepared.version.Scopes, draft.RequestedScopes) {
		return AppInstallationRequestView{}, provider.UpsertGitHubAppConfigParams{}, conflict("GitHub App definition changed. Restart setup.")
	}
	params, found, err := store.verifiedGitHubAppConfigFromStoredMap(
		appInstallationRequestStoredRequestedConfig(draft),
	)
	if err != nil {
		return AppInstallationRequestView{}, provider.UpsertGitHubAppConfigParams{}, err
	}
	if !found {
		return AppInstallationRequestView{}, provider.UpsertGitHubAppConfigParams{}, conflict("GitHub setup draft is missing. Restart setup.")
	}
	return draft, params, nil
}

func loadGitHubAdminSetupDraftForUpdate(
	ctx context.Context,
	queryer coredb.QueryRower,
	workspaceID string,
	draftID 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 = $8
FOR UPDATE`
	draft, err := scanAppInstallationRequest(queryer.QueryRow(
		ctx, query, strings.TrimSpace(workspaceID), strings.TrimSpace(draftID),
		registry.GitHubAppID, serviceactor.KindApp, AppInstallationRequestKindInstall,
		strings.TrimSpace(memberID), AppInstallationRequestStatusSetupReady,
		GitHubAppSetupModeAdmin,
	))
	if err != nil {
		if coredb.IsNotFound(err) {
			return AppInstallationRequestView{}, ErrNotFound
		}
		return AppInstallationRequestView{}, fmt.Errorf("load GitHub administrator setup draft: %w", err)
	}
	return draft, nil
}

func loadGitHubSetupCompletionLeaseForUpdate(
	ctx context.Context,
	queryer coredb.QueryRower,
	workspaceID string,
	memberID string,
	reason string,
) (AppInstallationRequestView, bool, error) {
	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 IN ($6, $8)
  AND req.request_reason = $7
ORDER BY req.created_at DESC, req.id DESC
LIMIT 1
FOR UPDATE`
	lease, err := scanAppInstallationRequest(queryer.QueryRow(
		ctx, query, strings.TrimSpace(workspaceID), registry.GitHubAppID,
		serviceactor.KindApp, AppInstallationRequestKindInstall, strings.TrimSpace(memberID),
		AppInstallationRequestStatusSetupVerifying, strings.TrimSpace(reason),
		AppInstallationRequestStatusSetupPatching,
	))
	if err != nil {
		if coredb.IsNotFound(err) {
			return AppInstallationRequestView{}, false, nil
		}
		return AppInstallationRequestView{}, false, fmt.Errorf("load GitHub setup completion lease: %w", err)
	}
	return lease, true, nil
}

func loadGitHubSetupCompletionLeaseByIDForUpdate(
	ctx context.Context,
	queryer coredb.QueryRower,
	workspaceID string,
	memberID string,
	leaseID string,
	reason 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 IN ($7, $9)
  AND req.request_reason = $8
FOR UPDATE`
	lease, err := scanAppInstallationRequest(queryer.QueryRow(
		ctx, query, strings.TrimSpace(workspaceID), strings.TrimSpace(leaseID),
		registry.GitHubAppID, serviceactor.KindApp, AppInstallationRequestKindInstall,
		strings.TrimSpace(memberID), AppInstallationRequestStatusSetupVerifying,
		strings.TrimSpace(reason), AppInstallationRequestStatusSetupPatching,
	))
	if err != nil {
		if coredb.IsNotFound(err) {
			return AppInstallationRequestView{}, ErrNotFound
		}
		return AppInstallationRequestView{}, fmt.Errorf("load GitHub setup completion lease: %w", err)
	}
	return lease, nil
}

func githubAdminSetupCompletionReason(draftID string, installationIDs []string) string {
	return githubAdminSetupCompletionReasonPrefix + strings.TrimSpace(draftID) + ":" + strings.Join(installationIDs, ",")
}

func githubSetupCompletionLeaseInstallationIDs(lease GitHubSetupCompletionLease) ([]string, error) {
	values := append([]string(nil), lease.InstallationIDs...)
	if len(values) == 0 && strings.TrimSpace(lease.InstallationID) != "" {
		values = []string{lease.InstallationID}
	}
	canonical, err := normalizedGitHubInstallationIDs(values)
	if err != nil {
		return nil, err
	}
	if len(lease.InstallationIDs) > 0 && !slices.Equal(lease.InstallationIDs, canonical) {
		return nil, conflict("GitHub setup completion selection is invalid. Restart setup.")
	}
	if singular := strings.TrimSpace(lease.InstallationID); singular != "" && singular != canonical[0] {
		return nil, conflict("GitHub setup completion selection is invalid. Restart setup.")
	}
	return canonical, nil
}

func canonicalGitHubSetupCompletionTargets(
	targets []GitHubSetupCompletionTarget,
) ([]githubConnectionTargetSnapshot, error) {
	ids := make([]string, 0, len(targets))
	byID := make(map[string]githubConnectionTargetSnapshot, len(targets))
	for _, target := range targets {
		id, err := canonicalGitHubInstallationID(target.InstallationID)
		if err != nil {
			return nil, err
		}
		login := strings.TrimSpace(target.AccountLogin)
		if login == "" || target.AccountID <= 0 {
			return nil, conflict("GitHub installation identity is incomplete. Restart setup.")
		}
		ids = append(ids, id)
		byID[id] = githubConnectionTargetSnapshot{
			InstallationID: id,
			AccountLogin:   login,
			AccountID:      strconv.FormatInt(target.AccountID, 10),
		}
	}
	canonicalIDs, err := normalizedGitHubInstallationIDs(ids)
	if err != nil {
		return nil, err
	}
	result := make([]githubConnectionTargetSnapshot, 0, len(canonicalIDs))
	for _, id := range canonicalIDs {
		result = append(result, byID[id])
	}
	return result, nil
}

func canonicalGitHubInstallationID(value string) (string, error) {
	trimmed := strings.TrimSpace(value)
	parsed, err := strconv.ParseInt(trimmed, 10, 64)
	if value != trimmed || err != nil || parsed <= 0 || strconv.FormatInt(parsed, 10) != trimmed {
		return "", badRequest("GitHub installation id is invalid.")
	}
	return trimmed, nil
}

func githubAppConfigFromSetupParams(workspaceID string, params provider.UpsertGitHubAppConfigParams) provider.GitHubAppConfig {
	return provider.GitHubAppConfig{
		WorkspaceID:   strings.TrimSpace(workspaceID),
		AppID:         params.AppID,
		AppSlug:       strings.TrimSpace(params.AppSlug),
		AppName:       strings.TrimSpace(params.AppName),
		HTMLURL:       strings.TrimSpace(params.HTMLURL),
		ClientID:      strings.TrimSpace(params.ClientID),
		ClientSecret:  strings.TrimSpace(params.ClientSecret),
		PrivateKeyPEM: strings.TrimSpace(params.PrivateKeyPEM),
		WebhookSecret: strings.TrimSpace(params.WebhookSecret),
		OwnerLogin:    strings.TrimSpace(params.OwnerLogin),
		OwnerID:       params.OwnerID,
	}
}