package app

import (
	"context"
	"encoding/json"
	"net/http"
	"strconv"
	"sync"
	"sync/atomic"
	"testing"
	"time"

	"github.com/stretchr/testify/require"

	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/coredb/testkit"
	"github.com/tidbcloud/AgentTeam/internal/store/app/registry"
)

func TestGitHubSetupProposalPromotionDeadlineLeavesDraftUnchanged(t *testing.T) {
	t.Parallel()

	var installationGets atomic.Int32
	var webhookPatches atomic.Int32
	providerClient := &http.Client{Transport: githubInitialSetupRoundTripFunc(func(request *http.Request) (*http.Response, error) {
		switch {
		case request.Method == http.MethodGet && request.URL.Path == "/app/installations/2":
			installationGets.Add(1)
			return nil, context.DeadlineExceeded
		case request.Method == http.MethodPatch && request.URL.Path == "/app/hook/config":
			webhookPatches.Add(1)
			return &http.Response{StatusCode: http.StatusNoContent, Body: http.NoBody, Header: make(http.Header)}, nil
		default:
			return &http.Response{StatusCode: http.StatusNotFound, Body: http.NoBody, Header: make(http.Header)}, nil
		}
	})}

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{
		AppCredentialStorageKey: testAppCredentialStorageKey(),
		HTTPClient:              providerClient,
		GitHubAPIBaseURL:        "https://github.example.test",
	})
	admin := seedAppAdminFixture(t, pool, "github_setup_promotion_deadline", time.Now().UTC())
	requester := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_setup_promotion_deadline_requester", time.Now().UTC())
	params := validGitHubAppConfigParams()
	params.PrivateKeyPEM = testGitHubConnectionPrivateKey(t)
	proposal, err := store.SaveVerifiedGitHubSetupDraftForRequester(
		ctx, requester.UserID, admin.WorkspaceID, params,
	)
	require.NoError(t, err)

	var beforeConfig string
	require.NoError(t, pool.QueryRow(ctx, `SELECT requested_config FROM service_installation_requests WHERE id = $1`, proposal.ID).Scan(&beforeConfig))
	_, err = store.RequestAppInstallationForUser(
		ctx, requester.UserID, admin.WorkspaceID, RequestAppInstallationParams{
			AppID:                 registry.GitHubAppID,
			RequestKind:           AppInstallationRequestKindInstall,
			GitHubSetupProposalID: proposal.ID,
			ConnectionTargets:     []string{"2", "10"},
		},
	)
	require.ErrorIs(t, err, context.DeadlineExceeded)
	require.Equal(t, int32(1), installationGets.Load())
	require.Zero(t, webhookPatches.Load())

	var status, afterConfig string
	var approvalRequired bool
	require.NoError(t, pool.QueryRow(ctx, `
SELECT status, requested_config, approval_required
FROM service_installation_requests
WHERE id = $1
`, proposal.ID).Scan(&status, &afterConfig, &approvalRequired))
	require.Equal(t, AppInstallationRequestStatusSetupReady, status)
	require.Equal(t, beforeConfig, afterConfig)
	require.False(t, approvalRequired)
	assertNoActiveGitHubSetupForTest(t, ctx, pool, admin.WorkspaceID)
}

func TestGitHubSetupProposalIsPrivateEncryptedAndPromotesIdempotently(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	now := time.Now().UTC()
	admin := seedAppAdminFixture(t, pool, "github_setup_private", now)
	requester := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_setup_private_requester", now)
	peer := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_setup_private_peer", now)

	mode, err := store.GitHubAppSetupModeForUser(ctx, requester.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.Equal(t, GitHubAppSetupModeRequester, mode)
	mode, err = store.GitHubAppSetupModeForUser(ctx, admin.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.Equal(t, GitHubAppSetupModeAdmin, mode)

	params := validGitHubAppConfigParams()
	params.AppID = 9_007_199_254_740_993
	params.OwnerID = 9_007_199_254_740_991
	first, err := store.SaveVerifiedGitHubSetupProposalForUser(ctx, requester.UserID, admin.WorkspaceID, params)
	require.NoError(t, err)
	require.True(t, first.Ready)
	require.NotEmpty(t, first.ID)
	require.Equal(t, params.AppName, first.AppName)
	require.Equal(t, params.OwnerLogin, first.OwnerLogin)
	require.WithinDuration(t, time.Now().UTC().Add(githubSetupProposalTTL), first.ExpiresAt, 5*time.Second)

	var raw string
	require.NoError(t, pool.QueryRow(ctx, `SELECT requested_config FROM service_installation_requests WHERE id = $1`, first.ID).Scan(&raw))
	var stored map[string]any
	require.NoError(t, json.Unmarshal([]byte(raw), &stored))
	require.Equal(t, "9007199254740993", stored[githubConfigAppIDKey])
	require.Equal(t, "9007199254740991", stored[githubConfigOwnerIDKey])
	require.Contains(t, raw, "ciphertext")
	require.NotContains(t, raw, params.ClientSecret)
	require.NotContains(t, raw, params.PrivateKeyPEM)
	require.NotContains(t, raw, params.WebhookSecret)
	assertNoActiveGitHubSetupForTest(t, ctx, pool, admin.WorkspaceID)

	pending, err := store.ListPendingAppInstallationRequestsForUser(ctx, requester.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.Empty(t, pending, "setup_ready is private draft state, not an approval request")
	loaded, found, err := store.GetGitHubSetupProposalForUser(ctx, requester.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.True(t, found)
	require.Equal(t, first, loaded)
	_, found, err = store.GetGitHubSetupProposalForUser(ctx, peer.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.False(t, found)
	_, _, err = store.GetGitHubSetupProposalForUser(ctx, admin.UserID, admin.WorkspaceID)
	require.ErrorIs(t, err, ErrForbidden)

	second, err := store.SaveVerifiedGitHubSetupProposalForUser(ctx, requester.UserID, admin.WorkspaceID, params)
	require.NoError(t, err)
	require.NotEqual(t, first.ID, second.ID)
	var replacedCount int
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_installation_requests WHERE id = $1`, first.ID).Scan(&replacedCount))
	require.Zero(t, replacedCount, "reverification deletes the old non-audit draft")

	_, err = store.RequestAppInstallationForUser(ctx, peer.UserID, admin.WorkspaceID, RequestAppInstallationParams{
		AppID:                 registry.GitHubAppID,
		RequestKind:           AppInstallationRequestKindInstall,
		GitHubSetupProposalID: second.ID,
	})
	require.ErrorIs(t, err, ErrNotFound)

	var draftCreatedAt time.Time
	require.NoError(t, pool.QueryRow(ctx, `SELECT created_at FROM service_installation_requests WHERE id = $1`, second.ID).Scan(&draftCreatedAt))
	request, err := store.RequestAppInstallationForUser(ctx, requester.UserID, admin.WorkspaceID, RequestAppInstallationParams{
		AppID:                 registry.GitHubAppID,
		RequestKind:           AppInstallationRequestKindInstall,
		GitHubSetupProposalID: second.ID,
		Reason:                "install verified GitHub App",
	})
	require.NoError(t, err)
	require.Equal(t, AppInstallationRequestStatusPending, request.Status)
	require.Equal(t, "install verified GitHub App", request.RequestReason)
	var promotedCreatedAt time.Time
	require.NoError(t, pool.QueryRow(ctx, `SELECT created_at FROM service_installation_requests WHERE id = $1`, second.ID).Scan(&promotedCreatedAt))
	require.Equal(t, draftCreatedAt, promotedCreatedAt, "promotion preserves the immutable proposal TTL anchor")

	retry, err := store.RequestAppInstallationForUser(ctx, requester.UserID, admin.WorkspaceID, RequestAppInstallationParams{
		AppID:                 registry.GitHubAppID,
		RequestKind:           AppInstallationRequestKindInstall,
		GitHubSetupProposalID: second.ID,
		Reason:                "lost response retry",
	})
	require.NoError(t, err)
	require.Equal(t, request.ID, retry.ID)
	require.Equal(t, request.RequestReason, retry.RequestReason, "idempotent retry does not mutate the pending audit record")

	peerDraft, err := store.SaveVerifiedGitHubSetupProposalForUser(ctx, peer.UserID, admin.WorkspaceID, params)
	require.NoError(t, err, "a peer draft must not be blocked by another member's pending request")
	require.NotEqual(t, request.ID, peerDraft.ID)
	unchanged, err := store.GetAppInstallationRequest(ctx, admin.WorkspaceID, request.ID)
	require.NoError(t, err)
	require.Equal(t, AppInstallationRequestStatusPending, unchanged.Status)
}

func TestGitHubPendingInstallDoesNotReserveWorkspace(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "github_setup_pending_non_blocking", time.Now().UTC())
	requester := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_setup_pending_requester", time.Now().UTC())
	peer := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_setup_pending_peer", time.Now().UTC())

	firstDraft, err := store.SaveVerifiedGitHubSetupProposalForUser(
		ctx, requester.UserID, admin.WorkspaceID, validGitHubAppConfigParams(),
	)
	require.NoError(t, err)
	first, err := store.RequestAppInstallationForUser(
		ctx, requester.UserID, admin.WorkspaceID, RequestAppInstallationParams{
			AppID: registry.GitHubAppID, GitHubSetupProposalID: firstDraft.ID,
		},
	)
	require.NoError(t, err)
	require.Equal(t, AppInstallationRequestStatusPending, first.Status)

	mode, err := store.GitHubAppSetupModeForUser(ctx, peer.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.Equal(t, GitHubAppSetupModeRequester, mode)
	peerParams := validGitHubAppConfigParams()
	peerParams.AppID++
	peerParams.AppSlug = "peer-github-app"
	peerParams.AppName = "Peer GitHub App"
	secondDraft, err := store.SaveVerifiedGitHubSetupProposalForUser(
		ctx, peer.UserID, admin.WorkspaceID, peerParams,
	)
	require.NoError(t, err)
	second, err := store.RequestAppInstallationForUser(
		ctx, peer.UserID, admin.WorkspaceID, RequestAppInstallationParams{
			AppID: registry.GitHubAppID, GitHubSetupProposalID: secondDraft.ID,
		},
	)
	require.NoError(t, err)
	require.NotEqual(t, first.ID, second.ID)
	require.Equal(t, AppInstallationRequestStatusPending, second.Status)

	var pendingCount, claimCount int
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*) FROM service_installation_requests
WHERE workspace_id = $1
  AND service_definition_id = $2
  AND request_kind = 'install'
  AND status = 'pending'
`, admin.WorkspaceID, registry.GitHubAppID).Scan(&pendingCount))
	require.Equal(t, 2, pendingCount)
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_provider_workspace_claims WHERE workspace_id = $1`, admin.WorkspaceID).Scan(&claimCount))
	require.Zero(t, claimCount, "pending requests are contenders, not workspace ownership claims")
	assertNoActiveGitHubSetupForTest(t, ctx, pool, admin.WorkspaceID)
}

func TestGitHubDraftsDoNotReserveAppAcrossWorkspaces(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	adminA := seedAppAdminFixture(t, pool, "github_candidate_workspace_a", time.Now().UTC())
	adminB := seedAppAdminFixture(t, pool, "github_candidate_workspace_b", time.Now().UTC())
	params := validGitHubAppConfigParams()

	draftA, err := store.SaveVerifiedGitHubSetupDraftForWorkspaceAdmin(ctx, adminA.UserID, adminA.WorkspaceID, params)
	require.NoError(t, err)
	draftB, err := store.SaveVerifiedGitHubSetupDraftForWorkspaceAdmin(ctx, adminB.UserID, adminB.WorkspaceID, params)
	require.NoError(t, err)
	require.NotEqual(t, draftA.ID, draftB.ID)

	var readyCount, claimCount int
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*) FROM service_installation_requests
WHERE service_definition_id = $1 AND status = 'setup_ready'
  AND workspace_id IN ($2, $3)
`, registry.GitHubAppID, adminA.WorkspaceID, adminB.WorkspaceID).Scan(&readyCount))
	require.Equal(t, 2, readyCount)
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_provider_workspace_claims WHERE provider_app_id = $1`, params.AppID).Scan(&claimCount))
	require.Zero(t, claimCount, "App-global ownership begins only at the pre-PATCH Mark")
}

func TestGitHubPeerDraftsRemainIndependentUntilActivation(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	adminA := seedAppAdminFixture(t, pool, "github_independent_admin_a", time.Now().UTC())
	adminB := seedWorkspaceMemberFixture(t, pool, adminA.WorkspaceID, "github_independent_admin_b", time.Now().UTC())
	mustExec(t, pool, `UPDATE members SET workspace_role = 'admin' WHERE id = $1`, adminB.MemberID)
	requester := seedWorkspaceMemberFixture(t, pool, adminA.WorkspaceID, "github_independent_requester", time.Now().UTC())

	requesterDraft, err := store.SaveVerifiedGitHubSetupProposalForUser(
		ctx, requester.UserID, adminA.WorkspaceID, validGitHubAppConfigParams(),
	)
	require.NoError(t, err)
	adminAParams := validGitHubAppConfigParams()
	adminAParams.AppID++
	adminAParams.AppSlug = "admin-a-app"
	adminAParams.AppName = "Admin A App"
	adminADraft, err := store.SaveVerifiedGitHubSetupDraftForWorkspaceAdmin(
		ctx, adminA.UserID, adminA.WorkspaceID, adminAParams,
	)
	require.NoError(t, err)
	adminBParams := adminAParams
	adminBParams.AppID++
	adminBParams.AppSlug = "admin-b-app"
	adminBParams.AppName = "Admin B App"
	adminBDraft, err := store.SaveVerifiedGitHubSetupDraftForWorkspaceAdmin(
		ctx, adminB.UserID, adminA.WorkspaceID, adminBParams,
	)
	require.NoError(t, err)

	for _, id := range []string{requesterDraft.ID, adminADraft.ID, adminBDraft.ID} {
		var rows int
		require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_installation_requests WHERE id = $1 AND status = 'setup_ready'`, id).Scan(&rows))
		require.Equal(t, 1, rows)
	}
	var claimCount int
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_provider_workspace_claims WHERE workspace_id = $1`, adminA.WorkspaceID).Scan(&claimCount))
	require.Zero(t, claimCount)

	replacement := adminAParams
	replacement.AppID += 10
	replacement.AppSlug = "admin-a-replacement"
	replacement.AppName = "Admin A Replacement"
	replacementDraft, err := store.SaveVerifiedGitHubSetupDraftForWorkspaceAdmin(
		ctx, adminA.UserID, adminA.WorkspaceID, replacement,
	)
	require.NoError(t, err)
	require.NotEqual(t, adminADraft.ID, replacementDraft.ID)
	var oldRows, peerRows, requesterRows int
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_installation_requests WHERE id = $1`, adminADraft.ID).Scan(&oldRows))
	require.Zero(t, oldRows, "a caller replaces only its own previous draft")
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_installation_requests WHERE id = $1`, adminBDraft.ID).Scan(&peerRows))
	require.Equal(t, 1, peerRows)
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_installation_requests WHERE id = $1`, requesterDraft.ID).Scan(&requesterRows))
	require.Equal(t, 1, requesterRows)
}

func TestGitHubRegistrationCallbackPreemptsAllUnsubmittedReadyDraftsBeforeActiveClaim(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "github_registration_override", time.Now().UTC())
	requester := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_registration_override_requester", time.Now().UTC())
	requesterDraft, err := store.SaveVerifiedGitHubSetupProposalForUser(
		ctx, requester.UserID, admin.WorkspaceID, validGitHubAppConfigParams(),
	)
	require.NoError(t, err)
	registered := validGitHubAppConfigParams()
	registered.AppID++
	registered.AppSlug = "newly-registered-agentteam-http"
	registered.AppName = "Newly Registered AgentTeam HTTP"
	view, err := store.FinalizeGitHubAppSetupForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, registered)
	require.NoError(t, err)
	require.True(t, view.Registered)

	var draftRows int
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_installation_requests WHERE id = $1`, requesterDraft.ID).Scan(&draftRows))
	require.Zero(t, draftRows)
	var claimState string
	var claimAppID int64
	require.NoError(t, pool.QueryRow(ctx, `
SELECT provider_app_id, claim_state FROM service_provider_workspace_claims WHERE workspace_id = $1
`, admin.WorkspaceID).Scan(&claimAppID, &claimState))
	require.Equal(t, registered.AppID, claimAppID)
	require.Equal(t, githubAppClaimStateActive, claimState)
}

func TestGitHubRegistrationCallbackPreservesPendingAuditButReleasesItsOldClaim(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "github_registration_pending_override", time.Now().UTC())
	requester := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_registration_pending_requester", time.Now().UTC())
	oldParams := validGitHubAppConfigParams()
	draft, err := store.SaveVerifiedGitHubSetupProposalForUser(ctx, requester.UserID, admin.WorkspaceID, oldParams)
	require.NoError(t, err)
	pending, err := store.RequestAppInstallationForUser(ctx, requester.UserID, admin.WorkspaceID, RequestAppInstallationParams{
		AppID: registry.GitHubAppID, GitHubSetupProposalID: draft.ID,
	})
	require.NoError(t, err)
	require.Equal(t, AppInstallationRequestStatusPending, pending.Status)

	registered := oldParams
	registered.AppID++
	registered.AppSlug = "registered-over-pending"
	registered.AppName = "Registered Over Pending"
	_, err = store.FinalizeGitHubAppSetupForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, registered)
	require.NoError(t, err)
	stillPending, err := store.GetAppInstallationRequest(ctx, admin.WorkspaceID, pending.ID)
	require.NoError(t, err)
	require.Equal(t, AppInstallationRequestStatusPending, stillPending.Status)

	var appID int64
	var state string
	require.NoError(t, pool.QueryRow(ctx, `SELECT provider_app_id, claim_state FROM service_provider_workspace_claims WHERE workspace_id = $1`, admin.WorkspaceID).Scan(&appID, &state))
	require.Equal(t, registered.AppID, appID)
	require.Equal(t, githubAppClaimStateActive, state)
	_, err = store.ApproveAppInstallationRequestForUser(ctx, admin.UserID, admin.WorkspaceID, pending.ID, DecideAppInstallationRequestParams{})
	require.ErrorIs(t, err, ErrConflict)
	require.ErrorIs(t, err, ErrAppInstallationApprovalStale)
}

func TestGitHubAdminSetupDraftCompletesAppConfigAndFirstConnectionAtomically(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "github_admin_setup_draft", time.Now().UTC())
	peer := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_admin_setup_draft_peer", time.Now().UTC())
	params := validGitHubAppConfigParams()

	draft, err := store.SaveVerifiedGitHubSetupDraftForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, params)
	require.NoError(t, err)
	require.True(t, draft.Ready)
	assertNoActiveGitHubSetupForTest(t, ctx, pool, admin.WorkspaceID)

	var rawConfig, rawBase, reason string
	require.NoError(t, pool.QueryRow(ctx, `
SELECT requested_config, base_config, request_reason
FROM service_installation_requests
WHERE id = $1
`, draft.ID).Scan(&rawConfig, &rawBase, &reason))
	require.Equal(t, GitHubAppSetupModeAdmin, reason)
	require.Contains(t, rawConfig, "ciphertext")
	require.NotContains(t, rawConfig, params.PrivateKeyPEM)
	require.NotContains(t, rawConfig, params.WebhookSecret)
	require.Contains(t, rawBase, githubVerificationExpectedConfigIDKey)
	require.Contains(t, rawBase, githubVerificationExpectedConfigUpdatedAtKey)

	// An ordinary member must not rotate the App-global webhook or delete an
	// administrator's ready draft while manual confirmation is in progress.
	_, err = store.BeginGitHubAppSetupVerificationForUser(ctx, peer.UserID, admin.WorkspaceID, GitHubSetupVerificationInput{
		Mode:   GitHubAppSetupModeRequester,
		Config: params,
	})
	require.ErrorIs(t, err, ErrConflict)
	var protectedDraftCount int
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_installation_requests WHERE id = $1`, draft.ID).Scan(&protectedDraftCount))
	require.Equal(t, 1, protectedDraftCount)

	// The ordinary-member projection must never reinterpret an administrator
	// draft, even if the creator later loses the admin role.
	mustExec(t, pool, `UPDATE members SET workspace_role = 'member', updated_at = NOW() WHERE id = $1`, admin.MemberID)
	_, found, err := store.GetGitHubSetupProposalForUser(ctx, admin.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.False(t, found)
	mustExec(t, pool, `UPDATE members SET workspace_role = 'admin', updated_at = NOW() WHERE id = $1`, admin.MemberID)

	mustExec(t, pool, `UPDATE members SET workspace_role = 'admin', updated_at = NOW() WHERE id = $1`, peer.MemberID)
	_, _, err = store.GetGitHubSetupDraftForWorkspaceAdmin(ctx, peer.UserID, admin.WorkspaceID, draft.ID)
	require.ErrorIs(t, err, ErrNotFound)
	loaded, config, err := store.GetGitHubSetupDraftForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, draft.ID)
	require.NoError(t, err)
	require.Equal(t, draft, loaded)
	require.Equal(t, params.AppID, config.AppID)
	require.Equal(t, params.PrivateKeyPEM, config.PrivateKeyPEM)
	require.Equal(t, params.WebhookSecret, config.WebhookSecret)

	preparedConfig, lease, err := store.BeginGitHubSetupCompletionForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, draft.ID, []string{"789"},
	)
	require.NoError(t, err)
	require.Equal(t, params.PrivateKeyPEM, preparedConfig.PrivateKeyPEM)
	require.Equal(t, draft.ID, lease.DraftID)
	require.Equal(t, "789", lease.InstallationID)
	require.NotEmpty(t, lease.ID)
	assertNoActiveGitHubSetupForTest(t, ctx, pool, admin.WorkspaceID)

	// Retrying the same selection reuses the one short read-only lease.
	// setup_verifying is a contender and must not reserve the workspace/App.
	var claimCount int
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM service_provider_workspace_claims
WHERE provider_app_id = $1
`, params.AppID).Scan(&claimCount))
	require.Zero(t, claimCount)
	_, retryLease, err := store.BeginGitHubSetupCompletionForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, draft.ID, []string{"789"},
	)
	require.NoError(t, err)
	require.Equal(t, lease, retryLease)
	_, alternateLease, err := store.BeginGitHubSetupCompletionForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, draft.ID, []string{"790"},
	)
	require.NoError(t, err)
	require.NotEqual(t, lease.ID, alternateLease.ID)
	require.Equal(t, "790", alternateLease.InstallationID)
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM service_provider_workspace_claims
WHERE provider_app_id = $1
`, params.AppID).Scan(&claimCount))
	require.Zero(t, claimCount, "independent read-only verification contenders do not reserve the App")

	// A same-selection retry renews the short lease without rotating the
	// encrypted webhook secret, so provider work cannot cross the TTL boundary
	// between PATCH and local finalize.
	mustExec(t, pool, `UPDATE service_installation_requests SET created_at = $1, updated_at = $1 WHERE id = $2`, time.Now().UTC().Add(-githubSetupVerificationTTL+time.Second), lease.ID)
	_, renewedLease, err := store.BeginGitHubSetupCompletionForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, draft.ID, []string{"789"},
	)
	require.NoError(t, err)
	require.Equal(t, lease, renewedLease)
	var renewedAt time.Time
	require.NoError(t, pool.QueryRow(ctx, `SELECT created_at FROM service_installation_requests WHERE id = $1`, lease.ID).Scan(&renewedAt))
	require.WithinDuration(t, time.Now().UTC(), renewedAt, 5*time.Second)

	// Once Begin has copied the encrypted operation snapshot, cleanup of the
	// original ready draft cannot make a same-secret completion retry unsafe.
	mustExec(t, pool, `DELETE FROM service_installation_requests WHERE id = $1`, draft.ID)
	retriedConfig, retriedLease, err := store.BeginGitHubSetupCompletionForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, draft.ID, []string{"789"},
	)
	require.NoError(t, err)
	require.Equal(t, lease, retriedLease)
	require.Equal(t, params.WebhookSecret, retriedConfig.WebhookSecret)
	require.NoError(t, store.MarkGitHubSetupCompletionProviderMutationStartedForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, lease, []GitHubSetupCompletionTarget{{
			InstallationID: "789", AccountLogin: "pingcap", AccountID: 7,
		}},
	))
	var claimState, claimToken string
	require.NoError(t, pool.QueryRow(ctx, `
SELECT claim_state, claim_token
FROM service_provider_workspace_claims
WHERE provider_app_id = $1
`, params.AppID).Scan(&claimState, &claimToken))
	require.Equal(t, githubAppClaimStatePatching, claimState)
	require.Equal(t, lease.ID, claimToken)
	require.ErrorIs(t, store.MarkGitHubSetupCompletionProviderMutationStartedForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, alternateLease, []GitHubSetupCompletionTarget{{
			InstallationID: "790", AccountLogin: "other-org", AccountID: 8,
		}},
	), ErrConflict, "the first durable pre-PATCH Mark wins over every read-only contender")
	require.NoError(t, store.AbortGitHubSetupVerificationForUser(
		ctx, admin.UserID, admin.WorkspaceID, alternateLease.ID,
	))
	// Provider mutation is the point of no return. Its lease/claim must survive
	// ordinary TTL cleanup and an administrator downgrade so the exact owner can
	// retry the same-secret local commit rather than strand GitHub's webhook.
	mustExec(t, pool, `UPDATE service_installation_requests SET created_at = $1, updated_at = $1 WHERE id = $2`,
		time.Now().UTC().Add(-githubSetupVerificationTTL-time.Minute), lease.ID)
	mustExec(t, pool, `UPDATE members SET workspace_role = 'member', updated_at = NOW() WHERE id = $1`, admin.MemberID)
	durableConfig, durableLease, err := store.BeginGitHubSetupCompletionForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, draft.ID, []string{"789"},
	)
	require.NoError(t, err)
	require.Equal(t, lease, durableLease)
	require.Equal(t, params.WebhookSecret, durableConfig.WebhookSecret)

	installed, err := store.FinalizeGitHubSetupCompletionForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, lease,
	)
	require.NoError(t, err)
	require.True(t, installed.Enabled)
	require.Len(t, installed.Connections, 1)
	require.Equal(t, "789", installed.Connections[0].ExternalSubjectRef)
	require.Equal(t, "pingcap", installed.Connections[0].DisplayLabel)
	require.True(t, installed.Connections[0].Enabled)

	activeConfig, found, err := store.Provider().LoadGitHubAppConfigForWorkspace(ctx, admin.WorkspaceID)
	require.NoError(t, err)
	require.True(t, found)
	require.Equal(t, params.AppID, activeConfig.AppID)
	require.Equal(t, params.WebhookSecret, activeConfig.WebhookSecret)
	var remaining int
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM service_installation_requests
WHERE id IN ($1, $2)
`, draft.ID, lease.ID).Scan(&remaining))
	require.Zero(t, remaining, "successful completion consumes both draft and lease")
}

func TestGitHubAdminSetupDraftFailureRollsBackAllActiveState(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "github_admin_setup_draft_rollback", time.Now().UTC())
	draft, err := store.SaveVerifiedGitHubSetupDraftForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, validGitHubAppConfigParams(),
	)
	require.NoError(t, err)
	_, lease, err := store.BeginGitHubSetupCompletionForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, draft.ID, []string{"789"},
	)
	require.NoError(t, err)
	require.NoError(t, store.MarkGitHubSetupCompletionProviderMutationStartedForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, lease, []GitHubSetupCompletionTarget{{
			InstallationID: "789", AccountLogin: "pingcap", AccountID: 7,
		}},
	))

	// Corrupt only the completion snapshot after prepare. Finalization must
	// detect the mismatch before materialization and leave the draft retryable.
	mustExec(t, pool, `
UPDATE service_installation_requests
SET requested_config = JSON_SET(requested_config, '$.private_key_pem', 'not-an-envelope')
WHERE id = $1
`, lease.ID)
	_, err = store.FinalizeGitHubSetupCompletionForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, lease,
	)
	require.ErrorIs(t, err, ErrConflict)
	assertNoActiveGitHubSetupForTest(t, ctx, pool, admin.WorkspaceID)
	var draftCount int
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_installation_requests WHERE id = $1`, draft.ID).Scan(&draftCount))
	require.Equal(t, 1, draftCount)
}

func TestGitHubAdminSetupCompletionExpiredPrePatchLeaseRebindsValidDraft(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "github_admin_completion_expiry", time.Now().UTC())
	params := validGitHubAppConfigParams()
	draft, err := store.SaveVerifiedGitHubSetupDraftForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, params)
	require.NoError(t, err)
	_, first, err := store.BeginGitHubSetupCompletionForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, draft.ID, []string{"789"})
	require.NoError(t, err)
	mustExec(t, pool, `UPDATE service_installation_requests SET created_at = $1, updated_at = $1 WHERE id = $2`,
		time.Now().UTC().Add(-githubSetupVerificationTTL-time.Minute), first.ID)

	config, rebound, err := store.BeginGitHubSetupCompletionForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, draft.ID, []string{"789"})
	require.NoError(t, err)
	require.NotEqual(t, first.ID, rebound.ID)
	require.Equal(t, params.WebhookSecret, config.WebhookSecret)
	var draftRows, oldLeaseRows int
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_installation_requests WHERE id = $1`, draft.ID).Scan(&draftRows))
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_installation_requests WHERE id = $1`, first.ID).Scan(&oldLeaseRows))
	require.Equal(t, 1, draftRows)
	require.Zero(t, oldLeaseRows)
}

func TestGitHubAdminSetupCompletionMembershipRemovalRetainsDurablePatchingSaga(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "github_completion_removed_owner", time.Now().UTC())
	params := validGitHubAppConfigParams()
	draft, err := store.SaveVerifiedGitHubSetupDraftForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, params)
	require.NoError(t, err)
	_, lease, err := store.BeginGitHubSetupCompletionForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, draft.ID, []string{"789"})
	require.NoError(t, err)
	require.NoError(t, store.MarkGitHubSetupCompletionProviderMutationStartedForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, lease, []GitHubSetupCompletionTarget{{
			InstallationID: "789", AccountLogin: "acme", AccountID: 7,
		}},
	))
	mustExec(t, pool, `UPDATE members SET status = 'inactive', updated_at = NOW() WHERE id = $1`, admin.MemberID)
	_, err = store.FinalizeGitHubSetupCompletionForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, lease)
	require.ErrorIs(t, err, ErrForbidden)

	var requestStatus, claimState, claimToken string
	require.NoError(t, pool.QueryRow(ctx, `SELECT status FROM service_installation_requests WHERE id = $1`, lease.ID).Scan(&requestStatus))
	require.NoError(t, pool.QueryRow(ctx, `SELECT claim_state, claim_token FROM service_provider_workspace_claims WHERE provider_app_id = $1`, params.AppID).Scan(&claimState, &claimToken))
	require.Equal(t, AppInstallationRequestStatusSetupPatching, requestStatus)
	require.Equal(t, githubAppClaimStatePatching, claimState)
	require.Equal(t, lease.ID, claimToken)
	assertNoActiveGitHubSetupForTest(t, ctx, pool, admin.WorkspaceID)
}

func TestGitHubAdminSetupCompletionBindsAndFinalizesTargetSet(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "github_admin_completion_batch", time.Now().UTC())
	draft, err := store.SaveVerifiedGitHubSetupDraftForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, validGitHubAppConfigParams(),
	)
	require.NoError(t, err)
	_, lease, err := store.BeginGitHubSetupCompletionForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, draft.ID, []string{"10", "2"},
	)
	require.NoError(t, err)
	require.Equal(t, []string{"2", "10"}, lease.InstallationIDs)
	require.Equal(t, "2", lease.InstallationID)

	initialTargets := []GitHubSetupCompletionTarget{
		{InstallationID: "10", AccountLogin: "beta-old", AccountID: 20},
		{InstallationID: "2", AccountLogin: "acme-old", AccountID: 10},
	}
	require.NoError(t, store.MarkGitHubSetupCompletionProviderMutationStartedForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, lease, initialTargets,
	))

	// An immutable account replacement after PATCH may have succeeded is never
	// accepted and must leave the durable patching saga recoverable.
	drifted := []GitHubSetupCompletionTarget{
		{InstallationID: "2", AccountLogin: "acme-new", AccountID: 999},
		{InstallationID: "10", AccountLogin: "beta-new", AccountID: 20},
	}
	err = store.MarkGitHubSetupCompletionProviderMutationStartedForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, lease, drifted,
	)
	require.ErrorIs(t, err, ErrConflict)
	var status string
	require.NoError(t, pool.QueryRow(ctx, `SELECT status FROM service_installation_requests WHERE id = $1`, lease.ID).Scan(&status))
	require.Equal(t, AppInstallationRequestStatusSetupPatching, status)

	// Organization login is mutable. Same installation/account identity accepts
	// the current labels and refreshes the short operation snapshot.
	renamed := []GitHubSetupCompletionTarget{
		{InstallationID: "10", AccountLogin: "beta-new", AccountID: 20},
		{InstallationID: "2", AccountLogin: "acme-new", AccountID: 10},
	}
	require.NoError(t, store.MarkGitHubSetupCompletionProviderMutationStartedForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, lease, renamed,
	))
	installed, err := store.FinalizeGitHubSetupCompletionForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, lease,
	)
	require.NoError(t, err)
	require.Len(t, installed.Connections, 2)
	actual := map[string]string{}
	for _, connection := range installed.Connections {
		actual[connection.ExternalSubjectRef] = connection.DisplayLabel
	}
	require.Equal(t, map[string]string{"2": "acme-new", "10": "beta-new"}, actual)
}

func TestGitHubAdminSetupPatchingLeaseIsNotAnApprovalRequest(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "github_admin_internal_patching", time.Now().UTC())
	draft, err := store.SaveVerifiedGitHubSetupDraftForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, validGitHubAppConfigParams(),
	)
	require.NoError(t, err)
	_, lease, err := store.BeginGitHubSetupCompletionForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, draft.ID, []string{"789"},
	)
	require.NoError(t, err)
	require.NoError(t, store.MarkGitHubSetupCompletionProviderMutationStartedForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, lease,
		[]GitHubSetupCompletionTarget{{InstallationID: "789", AccountLogin: "acme", AccountID: 7}},
	))

	var status string
	var approvalRequired bool
	require.NoError(t, pool.QueryRow(ctx, `
SELECT status, approval_required
FROM service_installation_requests
WHERE id = $1
`, lease.ID).Scan(&status, &approvalRequired))
	require.Equal(t, AppInstallationRequestStatusSetupPatching, status)
	require.False(t, approvalRequired)

	_, err = store.ApproveAppInstallationRequestForUser(
		ctx, admin.UserID, admin.WorkspaceID, lease.ID, DecideAppInstallationRequestParams{},
	)
	require.ErrorIs(t, err, ErrConflict)
	_, err = store.RejectAppInstallationRequestForUser(
		ctx, admin.UserID, admin.WorkspaceID, lease.ID, DecideAppInstallationRequestParams{},
	)
	require.ErrorIs(t, err, ErrConflict)
	pending, err := store.ListPendingAppInstallationRequestsForUser(ctx, admin.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.Empty(t, pending)

	require.NoError(t, pool.QueryRow(ctx, `
SELECT status, approval_required
FROM service_installation_requests
WHERE id = $1
`, lease.ID).Scan(&status, &approvalRequired))
	require.Equal(t, AppInstallationRequestStatusSetupPatching, status)
	require.False(t, approvalRequired)
}

func TestGitHubSetupProposalApprovalAndRejectionResolveSecretsSafely(t *testing.T) {
	t.Parallel()

	t.Run("legacy request without selected connections cannot bypass the provider saga", func(t *testing.T) {
		ctx := context.Background()
		pool := startAppRollbackDB(t, ctx)
		store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
		admin := seedAppAdminFixture(t, pool, "github_setup_approve", time.Now().UTC())
		requester := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_setup_approve_requester", time.Now().UTC())
		params := validGitHubAppConfigParams()

		proposal, err := store.SaveVerifiedGitHubSetupProposalForUser(ctx, requester.UserID, admin.WorkspaceID, params)
		require.NoError(t, err)
		request, err := store.RequestAppInstallationForUser(ctx, requester.UserID, admin.WorkspaceID, RequestAppInstallationParams{
			AppID:                 registry.GitHubAppID,
			GitHubSetupProposalID: proposal.ID,
			Reason:                "please approve",
		})
		require.NoError(t, err)
		_, err = store.ApproveAppInstallationRequestForUser(
			ctx, admin.UserID, admin.WorkspaceID, request.ID, DecideAppInstallationRequestParams{Reason: "approved"},
		)
		require.ErrorIs(t, err, ErrConflict)
		require.ErrorIs(t, err, ErrAppInstallationApprovalStale)
		assertNoActiveGitHubSetupForTest(t, ctx, pool, admin.WorkspaceID)
		rejected, err := store.RejectAppInstallationRequestForUser(
			ctx, admin.UserID, admin.WorkspaceID, request.ID, DecideAppInstallationRequestParams{Reason: "missing connection selection"},
		)
		require.NoError(t, err)
		require.Equal(t, AppInstallationRequestStatusRejected, rejected.Status)
		require.Equal(t, strconv.FormatInt(params.AppID, 10), rejected.RequestedConfig[githubConfigAppIDKey])

		var raw string
		require.NoError(t, pool.QueryRow(ctx, `SELECT requested_config FROM service_installation_requests WHERE id = $1`, request.ID).Scan(&raw))
		require.NotContains(t, raw, "ciphertext")
		require.NotContains(t, raw, params.ClientSecret)
		require.NotContains(t, raw, params.PrivateKeyPEM)
		require.NotContains(t, raw, params.WebhookSecret)
		require.NotContains(t, raw, githubConfigMaskedSecret)
		require.NotContains(t, raw, githubConfigClientSecretKey)
		require.NotContains(t, raw, githubConfigPrivateKeyPEMKey)
		require.NotContains(t, raw, githubConfigWebhookSecretKey)
	})

	t.Run("reject leaves no active local state", func(t *testing.T) {
		ctx := context.Background()
		pool := startAppRollbackDB(t, ctx)
		store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
		admin := seedAppAdminFixture(t, pool, "github_setup_reject", time.Now().UTC())
		requester := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_setup_reject_requester", time.Now().UTC())
		params := validGitHubAppConfigParams()

		proposal, err := store.SaveVerifiedGitHubSetupProposalForUser(ctx, requester.UserID, admin.WorkspaceID, params)
		require.NoError(t, err)
		request, err := store.RequestAppInstallationForUser(ctx, requester.UserID, admin.WorkspaceID, RequestAppInstallationParams{
			AppSlug:               registry.GitHubAppSlug,
			GitHubSetupProposalID: proposal.ID,
		})
		require.NoError(t, err)
		rejected, err := store.RejectAppInstallationRequestForUser(
			ctx, admin.UserID, admin.WorkspaceID, request.ID, DecideAppInstallationRequestParams{},
		)
		require.NoError(t, err)
		require.Equal(t, AppInstallationRequestStatusRejected, rejected.Status)
		assertNoActiveGitHubSetupForTest(t, ctx, pool, admin.WorkspaceID)

		var raw string
		require.NoError(t, pool.QueryRow(ctx, `SELECT requested_config FROM service_installation_requests WHERE id = $1`, request.ID).Scan(&raw))
		require.NotContains(t, raw, "ciphertext")
		require.NotContains(t, raw, params.ClientSecret)
		require.NotContains(t, raw, params.PrivateKeyPEM)
		require.NotContains(t, raw, params.WebhookSecret)
		require.NotContains(t, raw, githubConfigMaskedSecret)
		require.NotContains(t, raw, githubConfigClientSecretKey)
		require.NotContains(t, raw, githubConfigPrivateKeyPEMKey)
		require.NotContains(t, raw, githubConfigWebhookSecretKey)
	})
}

func TestGitHubSetupProposalExpiryElevationAndAdminDirectCleanupDeleteDrafts(t *testing.T) {
	t.Parallel()

	t.Run("expiry is anchored to created_at and deletes the draft", func(t *testing.T) {
		ctx := context.Background()
		pool := startAppRollbackDB(t, ctx)
		store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
		admin := seedAppAdminFixture(t, pool, "github_setup_expired", time.Now().UTC())
		requester := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_setup_expired_requester", time.Now().UTC())
		proposal, err := store.SaveVerifiedGitHubSetupProposalForUser(ctx, requester.UserID, admin.WorkspaceID, validGitHubAppConfigParams())
		require.NoError(t, err)
		mustExec(t, pool, `UPDATE service_installation_requests SET created_at = $1, updated_at = $2 WHERE id = $3`,
			time.Now().UTC().Add(-githubSetupProposalTTL-time.Minute), time.Now().UTC(), proposal.ID)

		_, err = store.RequestAppInstallationForUser(ctx, requester.UserID, admin.WorkspaceID, RequestAppInstallationParams{
			AppID: registry.GitHubAppID, GitHubSetupProposalID: proposal.ID,
		})
		require.ErrorIs(t, err, ErrGone)
		var count int
		require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_installation_requests WHERE id = $1`, proposal.ID).Scan(&count))
		require.Zero(t, count)
	})

	t.Run("opportunistic cleanup removes expired drafts across requesters", func(t *testing.T) {
		ctx := context.Background()
		pool := startAppRollbackDB(t, ctx)
		store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
		admin := seedAppAdminFixture(t, pool, "github_setup_cleanup", time.Now().UTC())
		requester := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_setup_cleanup_requester", time.Now().UTC())
		peer := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_setup_cleanup_peer", time.Now().UTC())
		expiredPeer, err := store.SaveVerifiedGitHubSetupProposalForUser(ctx, peer.UserID, admin.WorkspaceID, validGitHubAppConfigParams())
		require.NoError(t, err)
		mustExec(t, pool, `UPDATE service_installation_requests SET created_at = $1 WHERE id = $2`,
			time.Now().UTC().Add(-githubSetupProposalTTL-time.Minute), expiredPeer.ID)
		// A live App claim intentionally permits only one owner-bound draft.
		// Creating the requester's replacement after the peer draft expires
		// exercises the workspace-wide cleanup before the new claim is acquired.
		current, err := store.SaveVerifiedGitHubSetupProposalForUser(ctx, requester.UserID, admin.WorkspaceID, validGitHubAppConfigParams())
		require.NoError(t, err)

		loaded, found, err := store.GetGitHubSetupProposalForUser(ctx, requester.UserID, admin.WorkspaceID)
		require.NoError(t, err)
		require.True(t, found)
		require.Equal(t, current.ID, loaded.ID)
		var peerCount int
		require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_installation_requests WHERE id = $1`, expiredPeer.ID).Scan(&peerCount))
		require.Zero(t, peerCount, "cleanup is workspace-wide under the GitHub definition lock")
	})

	t.Run("elevated requester loses ready draft and uses direct setup", func(t *testing.T) {
		ctx := context.Background()
		pool := startAppRollbackDB(t, ctx)
		store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
		admin := seedAppAdminFixture(t, pool, "github_setup_elevated", time.Now().UTC())
		requester := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_setup_elevated_requester", time.Now().UTC())
		proposal, err := store.SaveVerifiedGitHubSetupProposalForUser(ctx, requester.UserID, admin.WorkspaceID, validGitHubAppConfigParams())
		require.NoError(t, err)
		mustExec(t, pool, `UPDATE members SET workspace_role = 'admin', updated_at = NOW() WHERE id = $1`, requester.MemberID)

		_, err = store.RequestAppInstallationForUser(ctx, requester.UserID, admin.WorkspaceID, RequestAppInstallationParams{
			AppID: registry.GitHubAppID, GitHubSetupProposalID: proposal.ID,
		})
		require.ErrorIs(t, err, ErrConflict)
		require.ErrorContains(t, err, "administrator")
		var count int
		require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_installation_requests WHERE id = $1`, proposal.ID).Scan(&count))
		require.Zero(t, count)
	})

	t.Run("admin direct success deletes all ready drafts", func(t *testing.T) {
		ctx := context.Background()
		pool := startAppRollbackDB(t, ctx)
		store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
		admin := seedAppAdminFixture(t, pool, "github_setup_admin_direct", time.Now().UTC())
		member := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_setup_admin_direct_member", time.Now().UTC())
		proposal, err := store.SaveVerifiedGitHubSetupProposalForUser(ctx, member.UserID, admin.WorkspaceID, validGitHubAppConfigParams())
		require.NoError(t, err)

		_, err = store.FinalizeGitHubAppSetupForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, validGitHubAppConfigParams())
		require.NoError(t, err)
		var count int
		require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_installation_requests WHERE id = $1`, proposal.ID).Scan(&count))
		require.Zero(t, count)
	})
}

func TestGitHubSetupProposalTamperingRollsBackApproval(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "github_setup_tamper", time.Now().UTC())
	requester := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_setup_tamper_requester", time.Now().UTC())
	proposal, err := store.SaveVerifiedGitHubSetupProposalForUser(ctx, requester.UserID, admin.WorkspaceID, validGitHubAppConfigParams())
	require.NoError(t, err)
	request, err := store.RequestAppInstallationForUser(ctx, requester.UserID, admin.WorkspaceID, RequestAppInstallationParams{
		AppID: registry.GitHubAppID, GitHubSetupProposalID: proposal.ID,
	})
	require.NoError(t, err)
	mustExec(t, pool, `UPDATE service_installation_requests SET requested_config = JSON_SET(requested_config, '$.private_key_pem', 'not-an-envelope') WHERE id = $1`, request.ID)

	_, err = store.ApproveAppInstallationRequestForUser(ctx, admin.UserID, admin.WorkspaceID, request.ID, DecideAppInstallationRequestParams{})
	require.ErrorIs(t, err, ErrConflict)
	assertNoActiveGitHubSetupForTest(t, ctx, pool, admin.WorkspaceID)
	persisted, err := store.GetAppInstallationRequest(ctx, admin.WorkspaceID, request.ID)
	require.NoError(t, err)
	require.Equal(t, AppInstallationRequestStatusPending, persisted.Status)
}

func TestGitHubSetupProposalConcurrentSameOwnerPromotionCreatesOnePendingRequest(t *testing.T) {
	ctx := context.Background()
	dsn := testkit.StartInitializedTiDBWithRootDSN(t, appTiDB.RootDSN(t))
	sqlDB, err := testkit.OpenTiDBContext(ctx, dsn)
	require.NoError(t, err)
	sqlDB.SetMaxOpenConns(8)
	sqlDB.SetMaxIdleConns(8)
	t.Cleanup(func() { require.NoError(t, sqlDB.Close()) })
	pool := coredb.WrapSQLDB(sqlDB)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "github_setup_concurrent", time.Now().UTC())
	member := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_setup_concurrent_member", time.Now().UTC())
	proposal, err := store.SaveVerifiedGitHubSetupProposalForUser(ctx, member.UserID, admin.WorkspaceID, validGitHubAppConfigParams())
	require.NoError(t, err)

	type outcome struct {
		request AppInstallationRequestView
		err     error
	}
	start := make(chan struct{})
	results := make(chan outcome, 2)
	var wait sync.WaitGroup
	for range 2 {
		wait.Add(1)
		go func() {
			defer wait.Done()
			<-start
			request, requestErr := store.RequestAppInstallationForUser(ctx, member.UserID, admin.WorkspaceID, RequestAppInstallationParams{
				AppID: registry.GitHubAppID, GitHubSetupProposalID: proposal.ID,
			})
			results <- outcome{request: request, err: requestErr}
		}()
	}
	close(start)
	wait.Wait()
	close(results)

	successes := 0
	requestIDs := map[string]struct{}{}
	for result := range results {
		require.NoError(t, result.err)
		successes++
		require.Equal(t, AppInstallationRequestStatusPending, result.request.Status)
		requestIDs[result.request.ID] = struct{}{}
	}
	require.Equal(t, 2, successes)
	require.Len(t, requestIDs, 1)
	var pendingCount int
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM service_installation_requests
WHERE workspace_id = $1
	AND service_definition_id = $2
	AND service_kind = 'app'
	AND request_kind = 'install'
	AND status = 'pending'
`, admin.WorkspaceID, registry.GitHubAppID).Scan(&pendingCount))
	require.Equal(t, 1, pendingCount)
}

func assertNoActiveGitHubSetupForTest(t *testing.T, ctx context.Context, pool coredb.QueryExecer, workspaceID string) {
	t.Helper()
	var installationCount int
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM service_installations
WHERE workspace_id = $1
  AND service_definition_id = $2
  AND status = 'active'
`, workspaceID, registry.GitHubAppID).Scan(&installationCount))
	require.Zero(t, installationCount)
	var configCount int
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_provider_configs WHERE workspace_id = $1 AND provider_kind = 'github'`, workspaceID).Scan(&configCount))
	require.Zero(t, configCount)
	var connectionCount int
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_connections WHERE workspace_id = $1`, workspaceID).Scan(&connectionCount))
	require.Zero(t, connectionCount)
}