package app

import (
	"context"
	"net/http"
	"strings"
	"sync"
	"testing"
	"time"

	"github.com/stretchr/testify/require"

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

// afterNthOuterMembershipReadDB fires only after the selected non-locking
// membership read. It places the role change after every public preflight but
// before the mutation transaction's canonical membership lock.
type afterNthOuterMembershipReadDB struct {
	coredb.DB
	mu         sync.Mutex
	read       int
	afterRead  int
	onSelected func()
	triggered  bool
}

func (db *afterNthOuterMembershipReadDB) QueryRow(ctx context.Context, query string, args ...any) coredb.Row {
	row := db.DB.QueryRow(ctx, query, args...)
	if !strings.Contains(query, "SELECT id, display_name, user_id, workspace_role") || strings.Contains(query, "FOR UPDATE") {
		return row
	}
	return afterOuterMembershipReadRow{Row: row, afterRead: func() {
		db.mu.Lock()
		defer db.mu.Unlock()
		db.read++
		if db.read == db.afterRead && !db.triggered {
			db.triggered = true
			db.onSelected()
		}
	}}
}

func (db *afterNthOuterMembershipReadDB) wasTriggered() bool {
	db.mu.Lock()
	defer db.mu.Unlock()
	return db.triggered
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	appStore := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	now := time.Now().UTC()
	fixture := seedGitHubConfigSystemAdminFixture(t, pool, "github_config_store_admin", now)
	requestAndApproveAppInstallationForTests(t, ctx, appStore, fixture.UserID, fixture.UserID, fixture.WorkspaceID, "github", nil)

	view, err := appStore.Provider().SaveGitHubAppConfigForWorkspaceAdmin(ctx, fixture.UserID, fixture.WorkspaceID, provider.UpsertGitHubAppConfigParams{
		AppID:         12345,
		AppSlug:       "agentteam-test",
		AppName:       "AgentTeam Test",
		HTMLURL:       "https://github.com/apps/agentteam-test",
		ClientID:      "Iv1.test",
		ClientSecret:  "client-secret-value",
		PrivateKeyPEM: "-----BEGIN RSA PRIVATE KEY-----\nkey\n-----END RSA PRIVATE KEY-----",
		WebhookSecret: "whsec_test",
		OwnerLogin:    "acme",
		OwnerID:       67890,
	})
	require.NoError(t, err)
	require.True(t, view.Registered)
	require.Equal(t, int64(12345), view.AppID)
	require.Equal(t, "agentteam-test", view.AppSlug)
	require.Equal(t, "AgentTeam Test", view.AppName)
	require.Equal(t, "https://github.com/apps/agentteam-test", view.HTMLURL)
	require.Equal(t, "acme", view.OwnerLogin)

	var (
		storedClientSecret string
		storedPrivateKey   string
		storedWebhook      string
	)
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COALESCE(JSON_UNQUOTE(JSON_EXTRACT(credential_envelope, '$.clientSecret')), ''),
       COALESCE(JSON_UNQUOTE(JSON_EXTRACT(credential_envelope, '$.privateKeyPem')), ''),
       COALESCE(JSON_UNQUOTE(JSON_EXTRACT(credential_envelope, '$.webhookSecret')), '')
FROM service_provider_configs
LIMIT 1
`).Scan(&storedClientSecret, &storedPrivateKey, &storedWebhook))
	require.Contains(t, storedClientSecret, `"ciphertext"`)
	require.Contains(t, storedPrivateKey, `"ciphertext"`)
	require.Contains(t, storedWebhook, `"ciphertext"`)
	require.NotContains(t, storedClientSecret, "client-secret-value")
	require.NotContains(t, storedPrivateKey, "BEGIN RSA PRIVATE KEY")
	require.NotContains(t, storedWebhook, "whsec_test")

	loaded, found, err := appStore.Provider().LoadGitHubAppConfigForWorkspace(ctx, fixture.WorkspaceID)
	require.NoError(t, err)
	require.True(t, found)
	require.Equal(t, int64(12345), loaded.AppID)
	require.Equal(t, "agentteam-test", loaded.AppSlug)
	require.Equal(t, "AgentTeam Test", loaded.AppName)
	require.Equal(t, "https://github.com/apps/agentteam-test", loaded.HTMLURL)
	require.Equal(t, "Iv1.test", loaded.ClientID)
	require.Equal(t, "client-secret-value", loaded.ClientSecret)
	require.Equal(t, "-----BEGIN RSA PRIVATE KEY-----\nkey\n-----END RSA PRIVATE KEY-----", loaded.PrivateKeyPEM)
	require.Equal(t, "whsec_test", loaded.WebhookSecret)
	require.Equal(t, "acme", loaded.OwnerLogin)
	require.Equal(t, int64(67890), loaded.OwnerID)

	publicView, err := appStore.Provider().GetGitHubAppConfigForUser(ctx, fixture.UserID, fixture.WorkspaceID)
	require.NoError(t, err)
	require.True(t, publicView.Registered)
	require.Equal(t, "agentteam-test", publicView.AppSlug)

	wrongKeyStore := New(pool, Options{AppCredentialStorageKey: []byte("fedcba9876543210fedcba9876543210")})
	publicView, err = wrongKeyStore.Provider().GetGitHubAppConfigForUser(ctx, fixture.UserID, fixture.WorkspaceID)
	require.NoError(t, err)
	require.True(t, publicView.Registered)
	require.Equal(t, "agentteam-test", publicView.AppSlug)
	require.Equal(t, "AgentTeam Test", publicView.AppName)

	_, _, err = wrongKeyStore.Provider().LoadGitHubAppConfigForWorkspace(ctx, fixture.WorkspaceID)
	require.Error(t, err)
	require.Contains(t, err.Error(), "decrypt github app client secret")
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	appStore := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	now := time.Now().UTC()
	admin := seedAppAdminFixture(t, pool, "github_config_delete_admin", now)
	nonAdmin := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_config_delete_member", now)
	requestAndApproveAppInstallationForTests(t, ctx, appStore, admin.UserID, admin.UserID, admin.WorkspaceID, "github", nil)

	_, err := appStore.Provider().SaveGitHubAppConfigForWorkspaceAdmin(ctx, nonAdmin.UserID, nonAdmin.WorkspaceID, provider.UpsertGitHubAppConfigParams{
		AppID:         1,
		AppSlug:       "forbidden",
		AppName:       "Forbidden",
		HTMLURL:       "https://github.com/apps/forbidden",
		ClientID:      "Iv1.forbidden",
		ClientSecret:  "client-secret",
		PrivateKeyPEM: "private-key",
		WebhookSecret: "webhook-secret",
	})
	require.ErrorIs(t, err, ErrForbidden)

	_, err = appStore.Provider().SaveGitHubAppConfigForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, provider.UpsertGitHubAppConfigParams{
		AppID:         54321,
		AppSlug:       "agentteam-delete",
		AppName:       "AgentTeam Delete",
		HTMLURL:       "https://github.com/apps/agentteam-delete",
		ClientID:      "Iv1.delete",
		ClientSecret:  "client-secret",
		PrivateKeyPEM: "private-key",
		WebhookSecret: "webhook-secret",
		OwnerLogin:    "octo",
		OwnerID:       9,
	})
	require.NoError(t, err)

	publicView, err := appStore.Provider().GetGitHubAppConfigForUser(ctx, nonAdmin.UserID, nonAdmin.WorkspaceID)
	require.NoError(t, err)
	require.True(t, publicView.Registered)
	require.Equal(t, "agentteam-delete", publicView.AppSlug)

	_, err = appStore.Provider().DeleteGitHubAppConfigForUser(ctx, nonAdmin.UserID, nonAdmin.WorkspaceID)
	require.ErrorIs(t, err, ErrForbidden)

	view, err := appStore.Provider().DeleteGitHubAppConfigForUser(ctx, admin.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.False(t, view.Registered)

	loaded, found, err := appStore.Provider().LoadGitHubAppConfigForWorkspace(ctx, admin.WorkspaceID)
	require.NoError(t, err)
	require.False(t, found)
	require.Equal(t, provider.GitHubAppConfig{}, loaded)
}

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

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

	_, err := appStore.FinalizeGitHubAppSetupForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, validGitHubAppConfigParams())
	require.NoError(t, err)
	_, connectionBase, err := appStore.Provider().PrepareGitHubConnectionForUser(ctx, admin.UserID, admin.WorkspaceID, "987654321")
	require.NoError(t, err)
	installed, err := appStore.Provider().ApplyGitHubAppSetupForUser(ctx, admin.UserID, admin.WorkspaceID, "987654321", "acme", connectionBase)
	require.NoError(t, err)
	require.Len(t, installed.Connections, 1)
	connectionID := installed.Connections[0].ID

	require.Equal(t, int64(1), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*)
FROM service_connections
WHERE id = $1
  AND status = 'active'
  AND disabled_at IS NULL
`, connectionID))
	require.Equal(t, int64(1), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*)
FROM service_webhook_endpoints
WHERE service_connection_id = $1
  AND status = 'active'
  AND disabled_at IS NULL
  AND active_endpoint_key IS NOT NULL
`, connectionID))
	require.Equal(t, int64(1), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*)
FROM service_connections
WHERE id = $1
  AND status = 'active'
`, connectionID))

	view, err := appStore.Provider().DeleteGitHubAppConfigForUser(ctx, admin.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.False(t, view.Registered)

	require.Equal(t, int64(1), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*)
FROM service_connections
WHERE id = $1
  AND status = 'disabled'
  AND disabled_at IS NOT NULL
`, connectionID))
	require.Equal(t, int64(1), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*)
FROM service_webhook_endpoints
WHERE service_connection_id = $1
  AND status = 'disabled'
  AND disabled_at IS NOT NULL
  AND active_endpoint_key IS NULL
`, connectionID))
	require.Equal(t, int64(1), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*)
FROM service_connections
WHERE id = $1
  AND status = 'disabled'
`, connectionID))

	connections, err := appStore.Provider().ListGitHubConnectionsForUser(ctx, admin.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.Empty(t, connections, "deleted provider credentials must not leave a routable GitHub connection projection")

	payload := []byte(`{"installation":{"id":987654321}}`)
	headers := http.Header{}
	headers.Set("X-GitHub-Event", "issue_comment")
	headers.Set("X-GitHub-Delivery", "delivery-after-config-delete")
	headers.Set("X-Hub-Signature-256", githubWebhookSignature(payload, validGitHubAppConfigParams().WebhookSecret))
	webhookResult, err := appStore.HandleAppWebhook(ctx, "github", headers, payload)
	require.NoError(t, err)
	require.Equal(t, AppWebhookResult{}, webhookResult, "an old signed webhook must not route after its config is deleted")
	require.Equal(t, int64(0), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*)
FROM service_webhook_receipts
WHERE service_connection_id = $1
`, connectionID))
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	appStore := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	now := time.Now().UTC()
	fixture := seedGitHubConfigSystemAdminFixture(t, pool, "github_config_empty_client_secret", now)
	requestAndApproveAppInstallationForTests(t, ctx, appStore, fixture.UserID, fixture.UserID, fixture.WorkspaceID, "github", nil)

	_, err := appStore.Provider().SaveGitHubAppConfigForWorkspaceAdmin(ctx, fixture.UserID, fixture.WorkspaceID, provider.UpsertGitHubAppConfigParams{
		AppID:         67890,
		AppSlug:       "agentteam-empty-secret",
		AppName:       "AgentTeam Empty Secret",
		HTMLURL:       "https://github.com/apps/agentteam-empty-secret",
		ClientID:      "Iv1.empty",
		ClientSecret:  "",
		PrivateKeyPEM: "-----BEGIN RSA PRIVATE KEY-----\nkey\n-----END RSA PRIVATE KEY-----",
		WebhookSecret: "whsec_empty",
		OwnerLogin:    "acme",
		OwnerID:       7,
	})
	require.NoError(t, err)

	var storedClientSecret string
	require.NoError(t, pool.QueryRow(ctx, `
SELECT COALESCE(JSON_UNQUOTE(JSON_EXTRACT(credential_envelope, '$.clientSecret')), '')
FROM service_provider_configs
LIMIT 1
`).Scan(&storedClientSecret))
	require.Equal(t, "", storedClientSecret)

	loaded, found, err := appStore.Provider().LoadGitHubAppConfigForWorkspace(ctx, fixture.WorkspaceID)
	require.NoError(t, err)
	require.True(t, found)
	require.Equal(t, "", loaded.ClientSecret)
}

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

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

	_, err := appStore.FinalizeGitHubAppSetupForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, activeParams)
	require.NoError(t, err)
	_, connectionBase, err := appStore.Provider().PrepareGitHubConnectionForUser(ctx, admin.UserID, admin.WorkspaceID, "112233")
	require.NoError(t, err)
	installed, err := appStore.Provider().ApplyGitHubAppSetupForUser(ctx, admin.UserID, admin.WorkspaceID, "112233", "existing-installation", connectionBase)
	require.NoError(t, err)
	require.Len(t, installed.Connections, 1)
	connectionID := installed.Connections[0].ID

	replacement := activeParams
	replacement.AppID++
	replacement.AppSlug = "different-github-app"
	replacement.AppName = "Different GitHub App"
	replacement.PrivateKeyPEM = "replacement-private-key"
	replacement.WebhookSecret = "replacement-webhook-secret"
	_, err = appStore.Provider().SaveGitHubAppConfigForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, replacement)
	require.ErrorIs(t, err, ErrConflict)

	unchanged, found, err := appStore.Provider().LoadGitHubAppConfigForWorkspace(ctx, admin.WorkspaceID)
	require.NoError(t, err)
	require.True(t, found)
	require.Equal(t, activeParams.AppID, unchanged.AppID)
	require.Equal(t, activeParams.AppSlug, unchanged.AppSlug)
	require.Equal(t, activeParams.PrivateKeyPEM, unchanged.PrivateKeyPEM)
	require.Equal(t, activeParams.WebhookSecret, unchanged.WebhookSecret)

	connections, err := appStore.Provider().ListGitHubConnectionsForUser(ctx, admin.UserID, admin.WorkspaceID)
	require.NoError(t, err)
	require.Len(t, connections, 1)
	require.Equal(t, connectionID, connections[0].ID)
	require.Equal(t, "112233", connections[0].ExternalSubjectRef)
	require.Equal(t, int64(1), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*)
FROM service_connections
WHERE id = $1
  AND status = 'active'
`, connectionID))
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	now := time.Now().UTC()
	admin := seedAppAdminFixture(t, pool, "github_config_role_downgrade_save", now)
	baseStore := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	requestAndApproveAppInstallationForTests(t, ctx, baseStore, admin.UserID, admin.UserID, admin.WorkspaceID, "github", nil)

	guardedDB := &afterNthOuterMembershipReadDB{
		DB:        pool,
		afterRead: 4,
		onSelected: func() {
			mustExec(t, pool, `UPDATE members SET workspace_role = 'member', updated_at = $2 WHERE id = $1`, admin.MemberID, time.Now().UTC())
		},
	}
	guardedStore := New(guardedDB, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	_, err := guardedStore.Provider().SaveGitHubAppConfigForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, provider.UpsertGitHubAppConfigParams{
		AppID: 42, AppSlug: "must-not-save", AppName: "Must Not Save",
		PrivateKeyPEM: "private-key", WebhookSecret: "webhook-secret",
	})
	require.ErrorIs(t, err, ErrForbidden)
	require.True(t, guardedDB.wasTriggered())
	require.Equal(t, int64(0), githubConfigTestRowCount(t, ctx, pool, `SELECT COUNT(*) FROM service_provider_configs WHERE workspace_id = $1 AND provider_kind = 'github'`, admin.WorkspaceID))
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	now := time.Now().UTC()
	admin := seedAppAdminFixture(t, pool, "github_config_role_downgrade_delete", now)
	baseStore := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	requestAndApproveAppInstallationForTests(t, ctx, baseStore, admin.UserID, admin.UserID, admin.WorkspaceID, "github", nil)
	_, err := baseStore.Provider().SaveGitHubAppConfigForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, provider.UpsertGitHubAppConfigParams{
		AppID: 43, AppSlug: "keep-config", AppName: "Keep Config",
		PrivateKeyPEM: "private-key", WebhookSecret: "webhook-secret",
	})
	require.NoError(t, err)

	guardedDB := &afterNthOuterMembershipReadDB{
		DB:        pool,
		afterRead: 2,
		onSelected: func() {
			mustExec(t, pool, `UPDATE members SET workspace_role = 'member', updated_at = $2 WHERE id = $1`, admin.MemberID, time.Now().UTC())
		},
	}
	guardedStore := New(guardedDB, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	_, err = guardedStore.Provider().DeleteGitHubAppConfigForUser(ctx, admin.UserID, admin.WorkspaceID)
	require.ErrorIs(t, err, ErrForbidden)
	require.True(t, guardedDB.wasTriggered())
	require.Equal(t, int64(1), githubConfigTestRowCount(t, ctx, pool, `SELECT COUNT(*) FROM service_provider_configs WHERE workspace_id = $1 AND provider_kind = 'github'`, admin.WorkspaceID))
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	now := time.Now().UTC()
	admin := seedAppAdminFixture(t, pool, "github_setup_role_downgrade", now)
	baseStore := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	requestAndApproveAppInstallationForTests(t, ctx, baseStore, admin.UserID, admin.UserID, admin.WorkspaceID, "github", nil)
	_, err := baseStore.Provider().SaveGitHubAppConfigForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, provider.UpsertGitHubAppConfigParams{
		AppID: 44, AppSlug: "no-stale-setup", AppName: "No Stale Setup",
		PrivateKeyPEM: "private-key", WebhookSecret: "webhook-secret",
	})
	require.NoError(t, err)
	_, connectionBase, err := baseStore.Provider().PrepareGitHubConnectionForUser(ctx, admin.UserID, admin.WorkspaceID, "12345")
	require.NoError(t, err)

	guardedDB := &afterNthOuterMembershipReadDB{
		DB:        pool,
		afterRead: 2,
		onSelected: func() {
			mustExec(t, pool, `UPDATE members SET workspace_role = 'member', updated_at = $2 WHERE id = $1`, admin.MemberID, time.Now().UTC())
		},
	}
	guardedStore := New(guardedDB, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	_, err = guardedStore.Provider().ApplyGitHubAppSetupForUser(ctx, admin.UserID, admin.WorkspaceID, "12345", "must-not-connect", connectionBase)
	require.ErrorIs(t, err, ErrForbidden)
	require.True(t, guardedDB.wasTriggered())
	require.Equal(t, int64(0), githubConfigTestRowCount(t, ctx, pool, `SELECT COUNT(*) FROM service_connections WHERE workspace_id = $1`, admin.WorkspaceID))
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	now := time.Now().UTC()
	admin := seedAppAdminFixture(t, pool, "github_direct_config_cas", now)
	appStore := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	requestAndApproveAppInstallationForTests(t, ctx, appStore, admin.UserID, admin.UserID, admin.WorkspaceID, "github", nil)
	_, err := appStore.Provider().SaveGitHubAppConfigForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, provider.UpsertGitHubAppConfigParams{
		AppID: 46, AppSlug: "config-cas", AppName: "Config CAS",
		PrivateKeyPEM: "private-key", WebhookSecret: "old-webhook-secret",
	})
	require.NoError(t, err)

	_, base, err := appStore.Provider().PrepareGitHubConnectionForUser(ctx, admin.UserID, admin.WorkspaceID, "24680")
	require.NoError(t, err)
	// Simulate a config rotation committing while the handler is verifying the
	// GitHub installation with the captured credentials.
	mustExec(t, pool, `UPDATE service_provider_configs SET display_name = $1, updated_at = DATE_ADD(updated_at, INTERVAL 1 SECOND) WHERE workspace_id = $2 AND provider_kind = 'github'`, "Rotated Config", admin.WorkspaceID)

	_, err = appStore.Provider().ApplyGitHubAppSetupForUser(
		ctx, admin.UserID, admin.WorkspaceID, "24680", "must-not-connect", base,
	)
	require.ErrorIs(t, err, ErrConflict)
	require.ErrorContains(t, err, "configuration changed")
	require.Equal(t, int64(0), githubConfigTestRowCount(t, ctx, pool, `SELECT COUNT(*) FROM service_connections WHERE workspace_id = $1`, admin.WorkspaceID))
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	now := time.Now().UTC()
	admin := seedAppAdminFixture(t, pool, "github_direct_installation_cas", now)
	appStore := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	installation := requestAndApproveAppInstallationForTests(
		t, ctx, appStore, admin.UserID, admin.UserID, admin.WorkspaceID, "github", nil,
	)
	_, err := appStore.Provider().SaveGitHubAppConfigForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, provider.UpsertGitHubAppConfigParams{
		AppID: 47, AppSlug: "installation-cas", AppName: "Installation CAS",
		PrivateKeyPEM: "private-key", WebhookSecret: "webhook-secret",
	})
	require.NoError(t, err)

	_, base, err := appStore.Provider().PrepareGitHubConnectionForUser(ctx, admin.UserID, admin.WorkspaceID, "13579")
	require.NoError(t, err)
	// The durable installation id is reused across disable/re-enable and upgrade
	// operations, so id equality alone is not a sufficient first-writer fence.
	mustExec(t, pool, `UPDATE service_installations SET metadata = $1, updated_at = DATE_ADD(updated_at, INTERVAL 1 SECOND) WHERE id = $2`, `{"changed":true}`, installation.ID)

	_, err = appStore.Provider().ApplyGitHubAppSetupForUser(
		ctx, admin.UserID, admin.WorkspaceID, "13579", "must-not-connect", base,
	)
	require.ErrorIs(t, err, ErrConflict)
	require.ErrorContains(t, err, "installation changed")
	require.Equal(t, int64(0), githubConfigTestRowCount(t, ctx, pool, `SELECT COUNT(*) FROM service_connections WHERE workspace_id = $1`, admin.WorkspaceID))
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	now := time.Now().UTC()
	admin := seedAppAdminFixture(t, pool, "github_disconnect_role_downgrade", now)
	baseStore := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	requestAndApproveAppInstallationForTests(t, ctx, baseStore, admin.UserID, admin.UserID, admin.WorkspaceID, "github", nil)
	_, err := baseStore.Provider().SaveGitHubAppConfigForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, provider.UpsertGitHubAppConfigParams{
		AppID: 45, AppSlug: "keep-connection", AppName: "Keep Connection",
		PrivateKeyPEM: "private-key", WebhookSecret: "webhook-secret",
	})
	require.NoError(t, err)
	_, connectionBase, err := baseStore.Provider().PrepareGitHubConnectionForUser(ctx, admin.UserID, admin.WorkspaceID, "67890")
	require.NoError(t, err)
	installed, err := baseStore.Provider().ApplyGitHubAppSetupForUser(ctx, admin.UserID, admin.WorkspaceID, "67890", "keep-connected", connectionBase)
	require.NoError(t, err)
	require.Len(t, installed.Connections, 1)
	connectionID := installed.Connections[0].ID
	disableBase, err := baseStore.Provider().PrepareGitHubConnectionDisableForUser(ctx, admin.UserID, admin.WorkspaceID, connectionID)
	require.NoError(t, err)

	guardedDB := &afterNthOuterMembershipReadDB{
		DB:        pool,
		afterRead: 2,
		onSelected: func() {
			mustExec(t, pool, `UPDATE members SET workspace_role = 'member', updated_at = $2 WHERE id = $1`, admin.MemberID, time.Now().UTC())
		},
	}
	guardedStore := New(guardedDB, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	err = guardedStore.Provider().DisableGitHubConnectionForUser(ctx, admin.UserID, admin.WorkspaceID, connectionID, disableBase)
	require.ErrorIs(t, err, ErrForbidden)
	require.True(t, guardedDB.wasTriggered())
	var status string
	require.NoError(t, pool.QueryRow(ctx, `SELECT status FROM service_connections WHERE id = $1`, connectionID).Scan(&status))
	require.Equal(t, "active", status)
}

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

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

	_, err := appStore.FinalizeGitHubAppSetupForWorkspaceAdmin(ctx, admin.UserID, admin.WorkspaceID, params)
	require.NoError(t, err)
	_, connectionBase, err := appStore.Provider().PrepareGitHubConnectionForUser(ctx, admin.UserID, admin.WorkspaceID, "445566")
	require.NoError(t, err)
	installed, err := appStore.Provider().ApplyGitHubAppSetupForUser(ctx, admin.UserID, admin.WorkspaceID, "445566", "must-stay-connected", connectionBase)
	require.NoError(t, err)
	require.Len(t, installed.Connections, 1)
	connectionID := installed.Connections[0].ID
	disableBase, err := appStore.Provider().PrepareGitHubConnectionDisableForUser(ctx, admin.UserID, admin.WorkspaceID, connectionID)
	require.NoError(t, err)
	activeConfig, found, err := appStore.Provider().LoadGitHubAppConfigForWorkspace(ctx, admin.WorkspaceID)
	require.NoError(t, err)
	require.True(t, found)

	lease, err := appStore.BeginGitHubAppSetupVerificationForUser(ctx, admin.UserID, admin.WorkspaceID, GitHubSetupVerificationInput{
		Mode:                    GitHubAppSetupModeAdmin,
		Config:                  params,
		ExpectedConfigID:        activeConfig.ID,
		ExpectedConfigUpdatedAt: activeConfig.UpdatedAt,
	})
	require.NoError(t, err)
	t.Cleanup(func() {
		_ = appStore.AbortGitHubSetupVerificationForUser(context.Background(), admin.UserID, admin.WorkspaceID, lease.ID)
	})

	err = appStore.Provider().DisableGitHubConnectionForUser(ctx, admin.UserID, admin.WorkspaceID, connectionID, disableBase)
	require.ErrorIs(t, err, ErrConflict)
	require.Equal(t, int64(1), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*)
FROM service_connections
WHERE id = $1
  AND status = 'active'
  AND disabled_at IS NULL
`, connectionID))
	require.Equal(t, int64(1), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*)
FROM service_webhook_endpoints
WHERE service_connection_id = $1
  AND status = 'active'
  AND active_endpoint_key IS NOT NULL
`, connectionID))
	require.Equal(t, int64(1), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*)
FROM service_connections
WHERE id = $1
  AND status = 'active'
`, connectionID))
}

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

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

	_, err := appStore.FinalizeGitHubAppSetupForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, validGitHubAppConfigParams(),
	)
	require.NoError(t, err)
	config, expectedIDs, base, err := appStore.Provider().PrepareGitHubConnectionsForUser(
		ctx, member.UserID, admin.WorkspaceID, []string{"100"},
	)
	require.NoError(t, err)
	require.Equal(t, validGitHubAppConfigParams().AppID, config.AppID)
	require.Equal(t, []string{"100"}, expectedIDs)

	installed, err := appStore.Provider().ApplyVerifiedGitHubConnectionsForUser(
		ctx, member.UserID, admin.WorkspaceID,
		[]provider.GitHubVerifiedConnectionTarget{{
			InstallationID: "100", DisplayLabel: "acme", AccountID: 10,
		}},
		base,
	)
	require.NoError(t, err)
	require.Len(t, installed.Connections, 1)
	actual := make(map[string]string, len(installed.Connections))
	for _, connection := range installed.Connections {
		actual[connection.ExternalSubjectRef] = connection.DisplayLabel
	}
	require.Equal(t, map[string]string{"100": "acme"}, actual)
	require.Equal(t, int64(1), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*)
FROM service_connections
WHERE workspace_id = $1
  AND status = 'active'
  AND disabled_at IS NULL
`, admin.WorkspaceID))
}

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

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

	_, err := appStore.FinalizeGitHubAppSetupForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, validGitHubAppConfigParams(),
	)
	require.NoError(t, err)
	_, expectedIDs, connectBase, err := appStore.Provider().PrepareGitHubConnectionsForUser(
		ctx, member.UserID, admin.WorkspaceID, []string{"101"},
	)
	require.NoError(t, err)
	connected, err := appStore.Provider().ApplyVerifiedGitHubConnectionsForUser(
		ctx, member.UserID, admin.WorkspaceID,
		[]provider.GitHubVerifiedConnectionTarget{{
			InstallationID: expectedIDs[0], DisplayLabel: "acme-existing", AccountID: 11,
		}},
		connectBase,
	)
	require.NoError(t, err)
	require.Len(t, connected.Connections, 1)
	connectionID := connected.Connections[0].ID

	disableBase, err := appStore.Provider().PrepareGitHubConnectionDisableForUser(
		ctx, admin.UserID, admin.WorkspaceID, connectionID,
	)
	require.NoError(t, err)
	require.NoError(t, appStore.Provider().DisableGitHubConnectionForUser(
		ctx, admin.UserID, admin.WorkspaceID, connectionID, disableBase,
	))
	require.Equal(t, int64(1), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*)
FROM service_connections
WHERE id = $1
  AND status = 'disabled'
  AND disabled_at IS NOT NULL
`, connectionID))

	// Disconnecting AgentTeam does not uninstall the provider installation. When
	// discovery returns that installation again, any active member can choose it
	// and create a new active connection generation without a GitHub install
	// flow; the disabled generation remains durable audit.
	_, expectedIDs, reconnectBase, err := appStore.Provider().PrepareGitHubConnectionsForUser(
		ctx, member.UserID, admin.WorkspaceID, []string{"101"},
	)
	require.NoError(t, err)
	reconnected, err := appStore.Provider().ApplyVerifiedGitHubConnectionsForUser(
		ctx, member.UserID, admin.WorkspaceID,
		[]provider.GitHubVerifiedConnectionTarget{{
			InstallationID: expectedIDs[0], DisplayLabel: "acme-existing", AccountID: 11,
		}},
		reconnectBase,
	)
	require.NoError(t, err)
	require.Len(t, reconnected.Connections, 1)
	require.NotEqual(t, connectionID, reconnected.Connections[0].ID, "reconnect creates a new active generation and preserves the disabled row as audit")
	require.Equal(t, int64(1), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*)
FROM service_connections
WHERE service_installation_id = $1
  AND external_subject_ref = '101'
  AND status = 'active'
  AND disabled_at IS NULL
`, connected.ID))
	require.Equal(t, int64(1), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*)
FROM service_connections
WHERE service_installation_id = $1
  AND external_subject_ref = '101'
  AND status = 'active'
`, connected.ID))
	require.Equal(t, int64(1), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*)
FROM service_connections
WHERE id = $1
  AND status = 'disabled'
  AND disabled_at IS NOT NULL
`, connectionID))
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	appStore := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "github_direct_overlap_admin", time.Now().UTC())
	member := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_direct_overlap_member", time.Now().UTC())
	_, err := appStore.FinalizeGitHubAppSetupForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, validGitHubAppConfigParams(),
	)
	require.NoError(t, err)

	_, firstIDs, firstBase, err := appStore.Provider().PrepareGitHubConnectionsForUser(
		ctx, member.UserID, admin.WorkspaceID, []string{"200"},
	)
	require.NoError(t, err)
	_, secondIDs, secondBase, err := appStore.Provider().PrepareGitHubConnectionsForUser(
		ctx, member.UserID, admin.WorkspaceID, []string{"200"},
	)
	require.NoError(t, err)
	require.Equal(t, []string{"200"}, firstIDs)
	require.Equal(t, []string{"200"}, secondIDs)

	_, err = appStore.Provider().ApplyVerifiedGitHubConnectionsForUser(
		ctx, member.UserID, admin.WorkspaceID,
		[]provider.GitHubVerifiedConnectionTarget{{
			InstallationID: "200", DisplayLabel: "winner-200", AccountID: 20,
		}},
		secondBase,
	)
	require.NoError(t, err)

	replayed, err := appStore.Provider().ApplyVerifiedGitHubConnectionsForUser(
		ctx, member.UserID, admin.WorkspaceID,
		[]provider.GitHubVerifiedConnectionTarget{{
			InstallationID: "200", DisplayLabel: "must-not-overwrite", AccountID: 20,
		}},
		firstBase,
	)
	require.NoError(t, err)
	require.Len(t, replayed.Connections, 1)

	// A brand-new HTTP retry after the first commit also sees the complete
	// desired state at Prepare and returns success without overwriting it.
	_, _, retryBase, err := appStore.Provider().PrepareGitHubConnectionsForUser(
		ctx, member.UserID, admin.WorkspaceID, []string{"200"},
	)
	require.NoError(t, err)
	_, err = appStore.Provider().ApplyVerifiedGitHubConnectionsForUser(
		ctx, member.UserID, admin.WorkspaceID,
		[]provider.GitHubVerifiedConnectionTarget{{
			InstallationID: "200", DisplayLabel: "current-provider-label", AccountID: 20,
		}},
		retryBase,
	)
	require.NoError(t, err)
	var label string
	require.NoError(t, pool.QueryRow(ctx, `
SELECT display_label FROM service_connections
WHERE workspace_id = $1 AND external_subject_ref = '200' AND status = 'active'
`, admin.WorkspaceID).Scan(&label))
	require.Equal(t, "winner-200", label, "the losing upsert must not overwrite the concurrent winner")
}

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

	ctx := context.Background()
	pool := startAppRollbackDB(t, ctx)
	appStore := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
	admin := seedAppAdminFixture(t, pool, "github_direct_membership_admin", time.Now().UTC())
	member := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_direct_membership_member", time.Now().UTC())
	_, err := appStore.FinalizeGitHubAppSetupForWorkspaceAdmin(
		ctx, admin.UserID, admin.WorkspaceID, validGitHubAppConfigParams(),
	)
	require.NoError(t, err)
	_, _, base, err := appStore.Provider().PrepareGitHubConnectionsForUser(
		ctx, member.UserID, admin.WorkspaceID, []string{"100"},
	)
	require.NoError(t, err)
	mustExec(t, pool, `UPDATE members SET status = 'disabled', updated_at = $2 WHERE id = $1`, member.MemberID, time.Now().UTC())

	_, err = appStore.Provider().ApplyVerifiedGitHubConnectionsForUser(
		ctx, member.UserID, admin.WorkspaceID,
		[]provider.GitHubVerifiedConnectionTarget{{
			InstallationID: "100", DisplayLabel: "acme", AccountID: 10,
		}},
		base,
	)
	require.ErrorIs(t, err, ErrForbidden)
	require.Equal(t, int64(0), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*) FROM service_connections WHERE workspace_id = $1 AND status = 'active'
`, admin.WorkspaceID))
}

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

	for _, testCase := range []struct {
		name   string
		mutate func(*testing.T, context.Context, coredb.QueryExecer, appAdminFixture)
	}{
		{
			name: "config changed after exact prepare",
			mutate: func(t *testing.T, ctx context.Context, pool coredb.QueryExecer, admin appAdminFixture) {
				t.Helper()
				mustExec(t, pool, `
UPDATE service_provider_configs
SET updated_at = DATE_ADD(updated_at, INTERVAL 1 SECOND)
WHERE workspace_id = $1
`, admin.WorkspaceID)
			},
		},
		{
			name: "active ownership claim changed after exact prepare",
			mutate: func(t *testing.T, ctx context.Context, pool coredb.QueryExecer, admin appAdminFixture) {
				t.Helper()
				mustExec(t, pool, `
UPDATE service_provider_workspace_claims
SET claim_state = 'patching', claim_token = 'other-operation', updated_at = NOW()
WHERE workspace_id = $1
`, admin.WorkspaceID)
			},
		},
	} {
		t.Run(testCase.name, func(t *testing.T) {
			ctx := context.Background()
			pool := startAppRollbackDB(t, ctx)
			appStore := New(pool, Options{AppCredentialStorageKey: testAppCredentialStorageKey()})
			admin := seedAppAdminFixture(t, pool, "github_direct_drift_admin_"+strings.ReplaceAll(testCase.name, " ", "_"), time.Now().UTC())
			member := seedWorkspaceMemberFixture(t, pool, admin.WorkspaceID, "github_direct_drift_member_"+strings.ReplaceAll(testCase.name, " ", "_"), time.Now().UTC())
			_, err := appStore.FinalizeGitHubAppSetupForWorkspaceAdmin(
				ctx, admin.UserID, admin.WorkspaceID, validGitHubAppConfigParams(),
			)
			require.NoError(t, err)
			_, _, base, err := appStore.Provider().PrepareGitHubConnectionsForUser(
				ctx, member.UserID, admin.WorkspaceID, []string{"300"},
			)
			require.NoError(t, err)
			testCase.mutate(t, ctx, pool, admin)

			_, err = appStore.Provider().ApplyVerifiedGitHubConnectionsForUser(
				ctx, member.UserID, admin.WorkspaceID,
				[]provider.GitHubVerifiedConnectionTarget{{
					InstallationID: "300", DisplayLabel: "acme", AccountID: 30,
				}},
				base,
			)
			require.ErrorIs(t, err, ErrConflict)
			require.Equal(t, int64(0), githubConfigTestRowCount(t, ctx, pool, `
SELECT COUNT(*) FROM service_connections WHERE workspace_id = $1 AND status = 'active'
`, admin.WorkspaceID))
		})
	}
}

func githubConfigTestRowCount(t *testing.T, ctx context.Context, queryer coredb.QueryRower, query string, args ...any) int64 {
	t.Helper()
	var count int64
	require.NoError(t, queryer.QueryRow(ctx, query, args...).Scan(&count))
	return count
}