package app

import (
	"context"
	"strings"
	"testing"
	"time"

	"github.com/stretchr/testify/require"

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

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "admin_direct_app", time.Now().UTC())

	_, err := store.RequestAppInstallationForUser(ctx, admin.UserID, admin.WorkspaceID, RequestAppInstallationParams{AppSlug: "github"})
	require.ErrorIs(t, err, ErrForbidden)
	require.Equal(t, 0, appInstallationRequestCountForWorkspace(t, ctx, pool, admin.WorkspaceID))

	_, err = store.InstallAppForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, InstallAppParams{AppSlug: "github"})
	require.ErrorIs(t, err, ErrBadRequest)
	require.ErrorContains(t, err, "GitHub setup")
	require.Equal(t, 0, appInstallationRequestCountForWorkspace(t, ctx, pool, admin.WorkspaceID))
	var githubInstallationCount int
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM service_installations
WHERE workspace_id = $1
  AND service_definition_id = $2
  AND status = 'active'
`, admin.WorkspaceID, registry.GitHubAppID).Scan(&githubInstallationCount))
	require.Zero(t, githubInstallationCount)
	var githubConfigCount int
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_provider_configs WHERE workspace_id = $1`, admin.WorkspaceID).Scan(&githubConfigCount))
	require.Zero(t, githubConfigCount)

	installation, err := store.InstallAppForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, InstallAppParams{AppSlug: giteaAppSlug})
	require.NoError(t, err, "non-GitHub Apps retain the generic direct-install path")
	require.True(t, installation.Enabled)
	require.Equal(t, giteaAppSlug, installation.AppSlug)
	require.Equal(t, 0, appInstallationRequestCountForWorkspace(t, ctx, pool, admin.WorkspaceID))
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "github_verified_install", time.Now().UTC())

	installations, err := store.ListAppInstallationsForUser(ctx, admin.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.Empty(t, installations)

	view, err := store.FinalizeGitHubAppSetupForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, validGitHubAppConfigParams())
	require.NoError(t, err)
	require.True(t, view.Registered)
	view, err = store.FinalizeGitHubAppSetupForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, validGitHubAppConfigParams())
	require.NoError(t, err)
	require.True(t, view.Registered)

	installations, err = store.ListAppInstallationsForUser(ctx, admin.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.Len(t, installations, 1)
	require.Equal(t, registry.GitHubAppID, installations[0].AppID)
	require.True(t, installations[0].Enabled)
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "github_atomic_failure", time.Now().UTC())
	params := validGitHubAppConfigParams()
	params.WebhookSecret = ""

	_, err := store.FinalizeGitHubAppSetupForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, params)
	require.Error(t, err)

	installations, listErr := store.ListAppInstallationsForUser(ctx, admin.UserID, admin.WorkspaceID)
	require.NoError(t, listErr)
	require.Empty(t, installations)
	config, found, loadErr := store.Provider().LoadGitHubAppConfigForWorkspace(ctx, admin.WorkspaceID)
	require.NoError(t, loadErr)
	require.False(t, found)
	require.Equal(t, provider.GitHubAppConfig{}, config)
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	github := newGitHubConnectionVerificationServer(t, 4242)
	store := New(pool, Options{
		AppCredentialStorageKey: testAppCredentialStorageKey(),
		HTTPClient:              github.server.Client(),
		GitHubAPIBaseURL:        github.server.URL,
	})
	admin := seedAppAdminFixture(t, pool, "github_pending_finalizer", time.Now().UTC())
	member := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_pending_finalizer_member", time.Now().UTC())
	pending := requestGitHubInitialInstallationForAdminDirectTest(
		t, ctx, store, member.UserID, admin.WorkspaceID, "configure GitHub",
	)

	view, err := store.FinalizeGitHubAppSetupForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, validGitHubAppConfigParams())
	require.NoError(t, err)
	require.True(t, view.Registered)

	installations, listErr := store.ListAppInstallationsForUser(ctx, admin.UserID, admin.WorkspaceID)
	require.NoError(t, listErr)
	require.Len(t, installations, 1)
	_, found, loadErr := store.Provider().LoadGitHubAppConfigForWorkspace(ctx, admin.WorkspaceID)
	require.NoError(t, loadErr)
	require.True(t, found)
	stored, requestErr := store.GetAppInstallationRequest(ctx, admin.WorkspaceID, pending.ID)
	require.NoError(t, requestErr)
	require.Equal(t, AppInstallationRequestStatusPending, stored.Status)
	_, err = store.ApproveAppInstallationRequestForUser(ctx, admin.UserID, admin.WorkspaceID, pending.ID, DecideAppInstallationRequestParams{})
	require.ErrorIs(t, err, ErrConflict)
	require.ErrorIs(t, err, ErrAppInstallationApprovalStale)
}

func validGitHubAppConfigParams() provider.UpsertGitHubAppConfigParams {
	return provider.UpsertGitHubAppConfigParams{
		AppID:         42,
		AppSlug:       "agentteam-app",
		AppName:       "AgentTeam App",
		HTMLURL:       "https://github.com/apps/agentteam-app",
		ClientID:      "Iv1.client",
		ClientSecret:  "client-secret",
		PrivateKeyPEM: "private-key",
		WebhookSecret: "webhook-secret",
		OwnerLogin:    "acme",
		OwnerID:       7,
	}
}

func requestGitHubInitialInstallationForAdminDirectTest(
	t *testing.T,
	ctx context.Context,
	store *Store,
	requesterUserID string,
	workspaceID string,
	reason string,
) AppInstallationRequestView {
	t.Helper()
	params := provider.UpsertGitHubAppConfigParams{
		AppID:         4242,
		AppSlug:       "agentteam-http",
		AppName:       "AgentTeam HTTP",
		HTMLURL:       "https://github.com/apps/agentteam-http",
		ClientID:      "Iv1.admin-direct-race",
		ClientSecret:  "admin-direct-race-client-secret",
		PrivateKeyPEM: testGitHubConnectionPrivateKey(t),
		WebhookSecret: "admin-direct-race-webhook-secret",
		OwnerLogin:    "acme",
		OwnerID:       7,
	}
	draft, err := store.SaveVerifiedGitHubSetupDraftForRequester(
		ctx, requesterUserID, workspaceID, params,
	)
	require.NoError(t, err)
	pending, err := store.RequestAppInstallationForUser(
		ctx,
		requesterUserID,
		workspaceID,
		RequestAppInstallationParams{
			AppID:                 registry.GitHubAppID,
			RequestKind:           AppInstallationRequestKindInstall,
			GitHubSetupProposalID: draft.ID,
			ConnectionTargets:     []string{"789"},
			Reason:                reason,
		},
	)
	require.NoError(t, err)
	require.Equal(t, AppInstallationRequestStatusPending, pending.Status)
	require.Equal(t, []string{"789"}, pending.ConnectionTargets)
	return pending
}

func validGiteaAdminDirectRequestConfig() map[string]any {
	return map[string]any{
		giteaConfigInstanceURLKey:   "https://gitea.example.com",
		giteaConfigAPITokenKey:      "admin-direct-token",
		giteaConfigWebhookSecretKey: "admin-direct-webhook-secret",
		giteaConfigDisplayNameKey:   "Gitea",
	}
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "admin_direct_pending", time.Now().UTC())
	member := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "admin_direct_pending_member", time.Now().UTC())

	pending, err := store.RequestAppInstallationForUser(ctx, member.UserID, admin.WorkspaceID, RequestAppInstallationParams{
		AppSlug: giteaAppSlug,
		Config:  validGiteaAdminDirectRequestConfig(),
	})
	require.NoError(t, err)
	require.Equal(t, AppInstallationRequestStatusPending, pending.Status)

	installation, err := store.InstallAppForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, InstallAppParams{AppSlug: giteaAppSlug})
	require.NoError(t, err)
	require.True(t, installation.Enabled)
	require.Equal(t, 1, appInstallationRequestCountForWorkspace(t, ctx, pool, admin.WorkspaceID))
	_, err = store.ApproveAppInstallationRequestForUser(ctx, admin.UserID, admin.WorkspaceID, pending.ID, DecideAppInstallationRequestParams{})
	require.ErrorIs(t, err, ErrConflict)
	require.ErrorIs(t, err, ErrAppInstallationApprovalStale)
	stillPending, err := store.GetAppInstallationRequest(ctx, admin.WorkspaceID, pending.ID)
	require.NoError(t, err)
	require.Equal(t, AppInstallationRequestStatusPending, stillPending.Status)

	_, err = store.RejectAppInstallationRequestForUser(ctx, admin.UserID, admin.WorkspaceID, pending.ID, DecideAppInstallationRequestParams{Reason: "install directly instead"})
	require.NoError(t, err)
}

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

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

	pending, err := store.RequestAppInstallationForUser(ctx, requester.UserID, admin.WorkspaceID, RequestAppInstallationParams{
		AppSlug: giteaAppSlug,
		Config:  validGiteaAdminDirectRequestConfig(),
	})
	require.NoError(t, err)
	mustExec(t, pool, `UPDATE members SET workspace_role = 'admin', updated_at = NOW() WHERE id = $1`, requester.MemberID)

	installation, err := store.InstallAppForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, InstallAppParams{AppSlug: giteaAppSlug})
	require.NoError(t, err)
	require.True(t, installation.Enabled)
	settled, err := store.GetAppInstallationRequest(ctx, admin.WorkspaceID, pending.ID)
	require.NoError(t, err)
	require.Equal(t, AppInstallationRequestStatusRejected, settled.Status)
	require.Equal(t, requester.MemberID, settled.DecidedByMemberID)
	require.Equal(t, PrivilegedAppRequestDecisionReason, settled.DecisionReason)
	require.True(t, IsPrivilegedAppRequestWithdrawalDecision(settled))
}

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

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

	pending := requestVerifiedGitHubInstallationForTests(t, ctx, store, requester.UserID, admin.WorkspaceID, "configure GitHub")
	_, err := store.ApproveAppInstallationRequestForUser(ctx, admin.UserID, admin.WorkspaceID, pending.ID, DecideAppInstallationRequestParams{Reason: "must not execute"})
	require.ErrorIs(t, err, ErrConflict)
	require.ErrorIs(t, err, ErrAppInstallationApprovalStale)
	require.ErrorContains(t, err, "selected connections")
	stillPending, err := store.GetAppInstallationRequest(ctx, admin.WorkspaceID, pending.ID)
	require.NoError(t, err)
	require.Equal(t, AppInstallationRequestStatusPending, stillPending.Status)
	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'
`, admin.WorkspaceID, pending.AppID).Scan(&installationCount))
	require.Zero(t, installationCount)
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	github := newGitHubConnectionVerificationServer(t, 4242)
	store := New(pool, Options{
		AppCredentialStorageKey: testAppCredentialStorageKey(),
		HTTPClient:              github.server.Client(),
		GitHubAPIBaseURL:        github.server.URL,
	})
	admin := seedAppAdminFixture(t, pool, "admin_direct_mixed_pending", time.Now().UTC())
	ordinary := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "admin_direct_mixed_ordinary", time.Now().UTC())
	promoted := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "admin_direct_mixed_promoted", time.Now().UTC())

	ordinaryRequest := requestGitHubInitialInstallationForAdminDirectTest(
		t, ctx, store, ordinary.UserID, admin.WorkspaceID, "first request",
	)
	peerParams := provider.UpsertGitHubAppConfigParams{
		AppID:         4242,
		AppSlug:       "agentteam-http",
		AppName:       "AgentTeam HTTP",
		HTMLURL:       "https://github.com/apps/agentteam-http",
		ClientID:      "Iv1.admin-direct-peer",
		ClientSecret:  "admin-direct-peer-client-secret",
		PrivateKeyPEM: testGitHubConnectionPrivateKey(t),
		WebhookSecret: "admin-direct-peer-webhook-secret",
		OwnerLogin:    "acme",
		OwnerID:       7,
	}
	peerDraft, err := store.SaveVerifiedGitHubSetupDraftForRequester(
		ctx, promoted.UserID, admin.WorkspaceID, peerParams,
	)
	require.NoError(t, err)
	require.NotEmpty(t, peerDraft.ID)
	peerRequest, err := store.RequestAppInstallationForUser(
		ctx,
		promoted.UserID,
		admin.WorkspaceID,
		RequestAppInstallationParams{
			AppID:                 registry.GitHubAppID,
			RequestKind:           AppInstallationRequestKindInstall,
			GitHubSetupProposalID: peerDraft.ID,
			ConnectionTargets:     []string{"790"},
			Reason:                "peer request",
		},
	)
	require.NoError(t, err)
	require.Equal(t, AppInstallationRequestStatusPending, peerRequest.Status)

	_, err = store.InstallAppForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, InstallAppParams{AppSlug: "github"})
	require.ErrorIs(t, err, ErrBadRequest)
	require.ErrorContains(t, err, "GitHub setup")
	ordinaryView, err := store.GetAppInstallationRequest(ctx, admin.WorkspaceID, ordinaryRequest.ID)
	require.NoError(t, err)
	require.Equal(t, AppInstallationRequestStatusPending, ordinaryView.Status)
	peerView, err := store.GetAppInstallationRequest(ctx, admin.WorkspaceID, peerRequest.ID)
	require.NoError(t, err)
	require.Equal(t, AppInstallationRequestStatusPending, peerView.Status)
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "admin_direct_uninstall_promoted", time.Now().UTC())
	requester := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "admin_direct_uninstall_promoted_requester", time.Now().UTC())
	installation, err := store.InstallAppForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, InstallAppParams{AppSlug: giteaAppSlug})
	require.NoError(t, err)
	pending, err := store.RequestAppInstallationForUser(ctx, requester.UserID, admin.WorkspaceID, RequestAppInstallationParams{
		InstallationID: installation.ID,
		RequestKind:    AppInstallationRequestKindUninstall,
	})
	require.NoError(t, err)
	mustExec(t, pool, `UPDATE members SET workspace_role = 'admin', updated_at = NOW() WHERE id = $1`, requester.MemberID)

	uninstalled, err := store.UninstallAppInstallationForUser(ctx, admin.UserID, admin.WorkspaceID, installation.ID)
	require.NoError(t, err)
	require.False(t, uninstalled.Enabled)
	settled, err := store.GetAppInstallationRequest(ctx, admin.WorkspaceID, pending.ID)
	require.NoError(t, err)
	require.Equal(t, AppInstallationRequestStatusRejected, settled.Status)
	require.Equal(t, PrivilegedAppRequestDecisionReason, settled.DecisionReason)
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "admin_direct_bot", time.Now().UTC())

	invalid := directAppOnboardingParams("admin-direct-invalid-app")
	invalid.Config = map[string]any{"tone": "invalid"}
	_, err := store.InstallCustomAppForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, invalid)
	require.Error(t, err)
	var invalidDefinitionCount int
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_definitions WHERE slug = $1`, invalid.Slug).Scan(&invalidDefinitionCount))
	require.Zero(t, invalidDefinitionCount, "definition/catalog/installation must roll back with invalid initial config")

	params := directAppOnboardingParams("admin-direct-app")
	result, err := store.InstallCustomAppForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, params)
	require.NoError(t, err)
	require.True(t, result.Installation.Enabled)
	require.True(t, result.Installation.CanManageApp, "direct App creation must acknowledge the creator's canonical management capability")
	require.Equal(t, params.Slug, result.Installation.AppSlug)
	require.Equal(t, "brief", result.Installation.Config["tone"])
	require.NotEmpty(t, result.CallbackSigningSecret)
	require.NotEmpty(t, result.RunnerToken)
	require.Equal(t, 0, appInstallationRequestCountForWorkspace(t, ctx, pool, admin.WorkspaceID))
	_, err = store.InstallCustomAppForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, params)
	require.ErrorIs(t, err, ErrConflict)
	require.ErrorContains(t, err, "already exists")
	require.Equal(t, 0, appInstallationRequestCountForWorkspace(t, ctx, pool, admin.WorkspaceID), "replaying App onboarding must not create an installation or request without new one-time credentials")

	var attributedCreator string
	require.NoError(t, pool.QueryRow(ctx, `
SELECT created_by_member_id
FROM extension_catalog_attributions
WHERE workspace_id = $1 AND service_definition_id = $2
`, admin.WorkspaceID, result.Installation.AppID).Scan(&attributedCreator))
	require.Equal(t, admin.MemberID, attributedCreator)

	authenticated, err := newRunnerServiceForTest(store).AuthenticateRunner(ctx, result.RunnerToken)
	require.NoError(t, err)
	require.Equal(t, result.Installation.ID, authenticated.ServiceInstallationID)
	var credentialCount int
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM service_runtime_credentials
WHERE workspace_id = $1 AND service_installation_id = $2 AND status = 'active'
`, admin.WorkspaceID, result.Installation.ID).Scan(&credentialCount))
	require.Equal(t, 1, credentialCount)

	updated, err := store.ConfigureAppInstallationForWorkspaceAdmin(
		ctx,
		admin.UserID,
		admin.WorkspaceID,
		result.Installation.ID,
		ConfigureAppInstallationParams{
			ExpectedBaseConfig: map[string]any{"tone": "brief"},
			Config:             map[string]any{"tone": "detailed"},
		},
	)
	require.NoError(t, err)
	require.Equal(t, "detailed", updated.Config["tone"])
	require.Equal(t, 0, appInstallationRequestCountForWorkspace(t, ctx, pool, admin.WorkspaceID))

	uninstalled, err := store.UninstallAppInstallationForUser(ctx, admin.UserID, admin.WorkspaceID, result.Installation.ID)
	require.NoError(t, err)
	require.False(t, uninstalled.Enabled)
	require.Equal(t, 0, appInstallationRequestCountForWorkspace(t, ctx, pool, admin.WorkspaceID), "direct App delete removes its request ledger")

	apps, err := store.ListAppsForUser(ctx, admin.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	for _, listed := range apps {
		require.NotEqual(t, result.Installation.AppID, listed.ID, "deleted App must disappear from Apps")
	}
	installations, err := store.ListAppInstallationsForUser(ctx, admin.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.Empty(t, installations)

	for table, query := range map[string]string{
		"App definition":             `SELECT COUNT(*) FROM service_definitions WHERE id = $1`,
		"App version":                `SELECT COUNT(*) FROM extension_definition_versions WHERE service_definition_id = $1`,
		"catalog attribution":        `SELECT COUNT(*) FROM extension_catalog_attributions WHERE service_definition_id = $1`,
		"service definition":         `SELECT COUNT(*) FROM service_definitions WHERE id = $1`,
		"service installation":       `SELECT COUNT(*) FROM service_installations WHERE service_definition_id = $1`,
		"compat installation":        `SELECT COUNT(*) FROM service_installations WHERE service_definition_id = $1`,
		"service member":             `SELECT COUNT(*) FROM service_members WHERE service_definition_id = $1`,
		"runtime credential":         `SELECT COUNT(*) FROM service_runtime_credentials WHERE service_definition_id = $1`,
		"target binding":             `SELECT COUNT(*) FROM service_target_bindings WHERE service_definition_id = $1`,
		"event subscription":         `SELECT COUNT(*) FROM service_event_subscriptions WHERE service_definition_id = $1`,
		"installation request audit": `SELECT COUNT(*) FROM service_installation_requests WHERE service_definition_id = $1`,
	} {
		var count int
		require.NoError(t, pool.QueryRow(ctx, query, result.Installation.AppID).Scan(&count), table)
		require.Zero(t, count, "%s must not survive external App deletion", table)
	}
	_, err = newRunnerServiceForTest(store).AuthenticateRunner(ctx, result.RunnerToken)
	require.Error(t, err, "the deleted App runner token must be revoked with its installation")
}

func TestExternalAppCreatorAndAdminShareDirectManagementWhilePeerFailsClosed(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, "external_bot_shared_management", now)
	creator := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "external_bot_shared_management_creator", now)
	peer := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "external_bot_shared_management_peer", now)

	requested, err := store.RequestCustomAppInstallationForUser(
		ctx,
		creator.UserID,
		admin.WorkspaceID,
		directAppOnboardingParams("external-app-shared-management"),
	)
	require.NoError(t, err)
	approved, err := store.ApproveAppInstallationRequestForUser(
		ctx,
		admin.UserID,
		admin.WorkspaceID,
		requested.Request.ID,
		DecideAppInstallationRequestParams{Reason: "approved"},
	)
	require.NoError(t, err)
	adminInstallations, err := store.ListAppInstallationsForUser(ctx, admin.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	creatorInstallations, err := store.ListAppInstallationsForUser(ctx, creator.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.Len(t, adminInstallations, 1)
	require.Len(t, creatorInstallations, 1)
	require.True(t, adminInstallations[0].CanManageApp)
	require.True(t, creatorInstallations[0].CanManageApp)
	peerInstallations, err := store.ListAppInstallationsForUser(ctx, peer.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.Empty(t, peerInstallations)

	_, err = store.CreateAppRunnerCredentialForUser(ctx, peer.UserID, admin.WorkspaceID, approved.InstallationID, AppRunnerCredentialCreateParams{Name: "forbidden"})
	require.ErrorIs(t, err, ErrNotFound)
	created, err := store.CreateAppRunnerCredentialForUser(ctx, creator.UserID, admin.WorkspaceID, approved.InstallationID, AppRunnerCredentialCreateParams{Name: "creator token"})
	require.NoError(t, err)
	require.NotEmpty(t, created.Token)
	listed, err := store.ListAppRunnerCredentialsForUser(ctx, admin.UserID, admin.WorkspaceID, approved.InstallationID)
	require.NoError(t, err)
	require.Len(t, listed, 1)
	require.Equal(t, created.Credential.ID, listed[0].ID)
	require.NotContains(t, strings.TrimSpace(strings.Join([]string{listed[0].ID, listed[0].Name, listed[0].TokenHint}, " ")), created.Token)
	_, err = store.RotateAppCallbackSecretForUser(ctx, peer.UserID, admin.WorkspaceID, approved.InstallationID)
	require.ErrorIs(t, err, ErrNotFound)
	rotated, err := store.RotateAppCallbackSecretForUser(ctx, creator.UserID, admin.WorkspaceID, approved.InstallationID)
	require.NoError(t, err)
	require.NotEmpty(t, rotated.CallbackSigningSecret)

	_, err = store.ConfigureAppInstallationForWorkspaceAdmin(
		ctx,
		peer.UserID,
		admin.WorkspaceID,
		approved.InstallationID,
		ConfigureAppInstallationParams{ExpectedBaseConfig: map[string]any{"tone": "brief"}, Config: map[string]any{"tone": "detailed"}},
	)
	require.ErrorIs(t, err, ErrNotFound)
	requestCountBeforeDirectManagement := appInstallationRequestCountForWorkspace(t, ctx, pool, admin.WorkspaceID)
	_, err = store.RequestAppInstallationForUser(ctx, creator.UserID, admin.WorkspaceID, RequestAppInstallationParams{
		InstallationID:     approved.InstallationID,
		RequestKind:        AppInstallationRequestKindConfigure,
		ExpectedBaseConfig: map[string]any{"tone": "brief"},
		Config:             map[string]any{"tone": "detailed"},
	})
	require.ErrorIs(t, err, ErrBadRequest, "App managers configure directly instead of creating an approval request")
	require.Equal(t, requestCountBeforeDirectManagement, appInstallationRequestCountForWorkspace(t, ctx, pool, admin.WorkspaceID))
	configured, err := store.ConfigureAppInstallationForWorkspaceAdmin(
		ctx,
		creator.UserID,
		admin.WorkspaceID,
		approved.InstallationID,
		ConfigureAppInstallationParams{ExpectedBaseConfig: map[string]any{"tone": "brief"}, Config: map[string]any{"tone": "detailed"}},
	)
	require.NoError(t, err)
	require.Equal(t, "detailed", configured.Config["tone"])

	_, err = store.DisableAppRunnerCredentialForUser(ctx, creator.UserID, admin.WorkspaceID, created.Credential.ID)
	require.NoError(t, err)
	_, err = store.RequestAppInstallationForUser(ctx, creator.UserID, admin.WorkspaceID, RequestAppInstallationParams{
		InstallationID: approved.InstallationID,
		RequestKind:    AppInstallationRequestKindUninstall,
	})
	require.ErrorIs(t, err, ErrBadRequest, "App managers delete directly instead of creating an approval request")
	_, err = store.UninstallAppInstallationForUser(ctx, peer.UserID, admin.WorkspaceID, approved.InstallationID)
	require.ErrorIs(t, err, ErrNotFound)
	uninstalled, err := store.UninstallAppInstallationForUser(ctx, creator.UserID, admin.WorkspaceID, approved.InstallationID)
	require.NoError(t, err)
	require.False(t, uninstalled.Enabled)
	require.Zero(t, appInstallationRequestCountForWorkspace(t, ctx, pool, admin.WorkspaceID), "App deletion removes the original approval ledger instead of synthesizing history")
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "admin_direct_bot_pending", time.Now().UTC())
	member := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "admin_direct_bot_pending_member", time.Now().UTC())

	adminOnlyParams := directAppOnboardingParams("admin-request-must-fail")
	_, err := store.RequestCustomAppInstallationForUser(ctx, admin.UserID, admin.WorkspaceID, adminOnlyParams)
	require.ErrorIs(t, err, ErrForbidden)
	require.Equal(t, 0, appInstallationRequestCountForWorkspace(t, ctx, pool, admin.WorkspaceID))

	pendingParams := directAppOnboardingParams("member-pending-direct-conflict")
	pending, err := store.RequestCustomAppInstallationForUser(ctx, member.UserID, admin.WorkspaceID, pendingParams)
	require.NoError(t, err)
	require.Equal(t, AppInstallationRequestStatusPending, pending.Request.Status)

	_, err = store.InstallCustomAppForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, pendingParams)
	require.ErrorIs(t, err, ErrConflict)
	require.ErrorContains(t, err, "already exists")
	_, err = store.InstallAppForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, InstallAppParams{AppID: pending.Request.AppID, Config: pendingParams.Config})
	require.ErrorIs(t, err, ErrBadRequest)
	require.ErrorContains(t, err, "App setup")
	require.Equal(t, 1, appInstallationRequestCountForWorkspace(t, ctx, pool, admin.WorkspaceID))
	var activeInstallationCount int
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM service_installations
WHERE workspace_id = $1 AND service_definition_id = $2 AND status = 'active'
`, admin.WorkspaceID, pending.Request.AppID).Scan(&activeInstallationCount))
	require.Zero(t, activeInstallationCount)
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	store := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "request_role_toctou", time.Now().UTC())
	member := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "request_role_toctou_member", time.Now().UTC())
	_, err := store.SaveGiteaAppConfigForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, provider.UpsertGiteaAppConfigParams{
		InstanceURL:   "https://gitea.example.test",
		APIToken:      "request-role-toctou-token",
		WebhookSecret: "request-role-toctou-webhook-secret",
		DisplayName:   "Gitea",
	})
	require.NoError(t, err)
	staleMember, err := store.requireWorkspaceAccess(ctx, member.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.Equal(t, "member", staleMember.WorkspaceRole)
	mustExec(t, pool, `
UPDATE members
SET workspace_role = 'admin', updated_at = NOW()
WHERE id = $1 AND workspace_id = $2
`, member.MemberID, admin.WorkspaceID)

	_, err = store.requestAppInstallationForWorkspaceMember(
		ctx,
		member.UserID,
		admin.WorkspaceID,
		staleMember,
		RequestAppInstallationParams{AppSlug: "github"},
	)
	require.ErrorIs(t, err, ErrForbidden)

	_, err = store.requestAppInstallationForWorkspaceMember(
		ctx,
		member.UserID,
		admin.WorkspaceID,
		staleMember,
		RequestAppInstallationParams{
			AppSlug:          "gitea",
			RequestKind:      AppInstallationRequestKindConnect,
			ConnectionTarget: "acme/widgets",
		},
	)
	require.ErrorIs(t, err, ErrForbidden)

	appParams := directAppOnboardingParams("role-transition-app-must-rollback")
	preparedApp, err := prepareWorkspaceAppDefinition(appParams)
	require.NoError(t, err)
	_, err = store.requestPreparedAppInstallationForWorkspaceMember(
		ctx,
		member.UserID,
		admin.WorkspaceID,
		staleMember,
		appParams,
		preparedApp,
	)
	require.ErrorIs(t, err, ErrForbidden)
	require.Equal(t, 0, appInstallationRequestCountForWorkspace(t, ctx, pool, admin.WorkspaceID))
	var appDefinitionCount int
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_definitions WHERE slug = $1`, appParams.Slug).Scan(&appDefinitionCount))
	require.Zero(t, appDefinitionCount, "App definition/catalog/version state must not survive a stale member role")
}

func directAppOnboardingParams(slug string) RequestCustomAppInstallationParams {
	return RequestCustomAppInstallationParams{
		Slug:   slug,
		Name:   slug,
		Config: map[string]any{"tone": "brief"},
		Manifest: map[string]any{
			"schema_version": "1",
			"slug":           slug,
			"name":           slug,
			"version":        "1.0.0",
			"callback": map[string]any{
				"url": "https://example.com/app-callback",
			},
			"scopes": []string{AppMentionScopeRead, AppRuntimeScopeMessageWrite},
			"capabilities": map[string]any{
				"runtime": map[string]any{
					"runtime": map[string]any{
						"transports": []string{
							serviceactor.RuntimeTransportKindRunnerStream,
							serviceactor.RuntimeTransportKindCallback,
						},
						"default_transport": serviceactor.RuntimeTransportKindRunnerStream,
					},
					"events": []string{registry.AppEventMessageMention},
					"scopes": []string{AppMentionScopeRead, AppRuntimeScopeMessageWrite},
					"config_schema": map[string]any{
						"type":                 "object",
						"additionalProperties": false,
						"required":             []string{"tone"},
						"properties": map[string]any{
							"tone": map[string]any{
								"type": "string",
								"enum": []string{"brief", "detailed"},
							},
						},
					},
				},
			},
		},
	}
}

func appInstallationRequestCountForWorkspace(t *testing.T, ctx context.Context, pool coredb.QueryRower, workspaceID string) int {
	t.Helper()
	var count int
	require.NoError(t, pool.QueryRow(ctx, `SELECT COUNT(*) FROM service_installation_requests WHERE workspace_id = $1`, workspaceID).Scan(&count))
	return count
}