package provider

import (
	"bytes"
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/domain"
	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/access"
)

type GitHubAppConfigView struct {
	Registered bool
	AppID      int64
	AppName    string
	AppSlug    string
	HTMLURL    string
	OwnerLogin string
}

type GitHubAppConfig struct {
	ID              string
	WorkspaceID     string
	AppID           int64
	AppSlug         string
	AppName         string
	HTMLURL         string
	ClientID        string
	ClientSecret    string
	PrivateKeyPEM   string
	WebhookSecret   string
	OwnerLogin      string
	OwnerID         int64
	CreatedByUserID string
	CreatedAt       time.Time
	UpdatedAt       time.Time
}

const (
	githubConnectionMutationConnect    = "connect"
	githubConnectionMutationDisconnect = "disconnect"
)

// GitHubConnectionMutationBase is an opaque optimistic-concurrency token for
// one privileged GitHub connection mutation. Callers must capture it before
// provider I/O and return it unchanged to the matching apply method. Its
// fields intentionally remain private so HTTP callers cannot construct a
// weaker base than the Store captured under the canonical GitHub App lock.
type GitHubConnectionMutationBase struct {
	operation          string
	workspaceID        string
	externalSubjectRef string
	connectionID       string
	configID           string
	configAppID        int64
	configUpdatedAt    time.Time
	installation       githubInstallationMutationSnapshot
	connectionFound    bool
	connection         githubConnectionMutationSnapshot
}

type githubInstallationMutationSnapshot struct {
	id                  string
	workspaceID         string
	serviceDefinitionID string
	serviceKind         string
	instanceKey         string
	status              string
	installedVersion    string
	config              []byte
	metadata            []byte
	createdByMemberID   string
	createdByActorType  string
	createdByActorID    string
	createdAt           time.Time
	updatedAt           time.Time
	disabledAt          *time.Time
}

type githubConnectionMutationSnapshot struct {
	id                    string
	workspaceID           string
	serviceDefinitionID   string
	serviceInstallationID string
	serviceKind           string
	externalSubjectRef    string
	displayLabel          string
	credentials           []byte
	config                []byte
	status                string
	createdByMemberID     string
	createdAt             time.Time
	updatedAt             time.Time
	disabledAt            *time.Time
}

type UpsertGitHubAppConfigParams struct {
	AppID         int64
	AppSlug       string
	AppName       string
	HTMLURL       string
	ClientID      string
	ClientSecret  string
	PrivateKeyPEM string
	WebhookSecret string
	OwnerLogin    string
	OwnerID       int64
}

func (store *Store) AuthorizeGitHubAppRegistrationForUser(ctx context.Context, userID string, workspaceID string) error {
	return store.requireWorkspaceServiceInstall(ctx, userID, workspaceID)
}

func (store *Store) AuthorizeGitHubAppInstallForUser(ctx context.Context, userID string, workspaceID string) error {
	if err := store.requireWorkspaceServiceInstall(ctx, userID, workspaceID); err != nil {
		return err
	}
	installation, found, err := store.findAppInstallationByWorkspaceAndApp(ctx, workspaceID, "app_github")
	if err != nil {
		return err
	}
	if !found || !installation.Enabled {
		return conflict("GitHub App must be installed before provider setup.")
	}
	return nil
}

func (store *Store) GetGitHubAppConfigForUser(ctx context.Context, userID string, workspaceID string) (GitHubAppConfigView, error) {
	if err := store.requireWorkspaceAccess(ctx, userID, workspaceID); err != nil {
		return GitHubAppConfigView{}, err
	}

	row := store.db.QueryRow(ctx, `
SELECT COALESCE(CAST(JSON_EXTRACT(COALESCE(config, JSON_OBJECT()), '$.githubAppId') AS SIGNED), 0),
       provider_key,
       display_name,
       COALESCE(JSON_UNQUOTE(JSON_EXTRACT(COALESCE(config, JSON_OBJECT()), '$.htmlUrl')), ''),
       COALESCE(JSON_UNQUOTE(JSON_EXTRACT(COALESCE(config, JSON_OBJECT()), '$.ownerLogin')), '')
FROM service_provider_configs
WHERE workspace_id = $1
  AND provider_kind = 'github'
LIMIT 1
`, strings.TrimSpace(workspaceID))

	view := GitHubAppConfigView{Registered: true}
	if err := row.Scan(&view.AppID, &view.AppSlug, &view.AppName, &view.HTMLURL, &view.OwnerLogin); err != nil {
		if coredb.IsNotFound(err) {
			return GitHubAppConfigView{Registered: false}, nil
		}
		return GitHubAppConfigView{}, fmt.Errorf("load github app config view: %w", err)
	}
	view.AppSlug = strings.TrimSpace(view.AppSlug)
	view.AppName = strings.TrimSpace(view.AppName)
	view.HTMLURL = strings.TrimSpace(view.HTMLURL)
	view.OwnerLogin = strings.TrimSpace(view.OwnerLogin)
	return view, nil
}

func (store *Store) LoadGitHubAppConfigForWorkspace(ctx context.Context, workspaceID string) (GitHubAppConfig, bool, error) {
	return store.loadGitHubAppConfigForWorkspace(ctx, store.db, workspaceID, false)
}

func (store *Store) loadGitHubAppConfigForWorkspace(ctx context.Context, queryer coredb.QueryRower, workspaceID string, forUpdate bool) (GitHubAppConfig, bool, error) {
	query := `
SELECT
    service_provider_configs.id,
    service_provider_configs.workspace_id,
    COALESCE(CAST(JSON_EXTRACT(COALESCE(service_provider_configs.config, JSON_OBJECT()), '$.githubAppId') AS SIGNED), 0),
    service_provider_configs.provider_key,
    service_provider_configs.display_name,
    COALESCE(JSON_UNQUOTE(JSON_EXTRACT(COALESCE(service_provider_configs.config, JSON_OBJECT()), '$.htmlUrl')), ''),
    COALESCE(JSON_UNQUOTE(JSON_EXTRACT(COALESCE(service_provider_configs.credential_envelope, JSON_OBJECT()), '$.clientId')), ''),
    COALESCE(JSON_UNQUOTE(JSON_EXTRACT(COALESCE(service_provider_configs.credential_envelope, JSON_OBJECT()), '$.clientSecret')), ''),
    COALESCE(JSON_UNQUOTE(JSON_EXTRACT(COALESCE(service_provider_configs.credential_envelope, JSON_OBJECT()), '$.privateKeyPem')), ''),
    COALESCE(JSON_UNQUOTE(JSON_EXTRACT(COALESCE(service_provider_configs.credential_envelope, JSON_OBJECT()), '$.webhookSecret')), ''),
    COALESCE(JSON_UNQUOTE(JSON_EXTRACT(COALESCE(service_provider_configs.config, JSON_OBJECT()), '$.ownerLogin')), ''),
    COALESCE(CAST(JSON_EXTRACT(COALESCE(service_provider_configs.config, JSON_OBJECT()), '$.ownerId') AS SIGNED), 0),
    COALESCE(creator.user_id, service_provider_configs.created_by_member_id) AS created_by_user_id,
    service_provider_configs.created_at,
    service_provider_configs.updated_at
FROM service_provider_configs
LEFT JOIN members creator
  ON creator.id = service_provider_configs.created_by_member_id
WHERE service_provider_configs.workspace_id = $1
  AND service_provider_configs.provider_kind = 'github'
LIMIT 1
`
	if forUpdate {
		query += "FOR UPDATE\n"
	}
	row := queryer.QueryRow(ctx, query, strings.TrimSpace(workspaceID))

	config, err := store.scanGitHubAppConfig(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return GitHubAppConfig{}, false, nil
		}
		return GitHubAppConfig{}, false, fmt.Errorf("load github app config: %w", err)
	}
	return config, true, nil
}

func (store *Store) SaveGitHubAppConfigForWorkspaceAdmin(ctx context.Context, userID string, workspaceID string, params UpsertGitHubAppConfigParams) (GitHubAppConfigView, error) {
	if err := store.AuthorizeGitHubAppRegistrationForUser(ctx, userID, workspaceID); err != nil {
		return GitHubAppConfigView{}, err
	}
	if err := store.AuthorizeGitHubAppInstallForUser(ctx, userID, workspaceID); err != nil {
		return GitHubAppConfigView{}, err
	}

	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedUserID := strings.TrimSpace(userID)
	// Keep the inexpensive preflight before opening the transaction. The
	// transaction below remains authoritative for both caller role and enabled
	// installation state.
	if err := store.AuthorizeGitHubAppInstallForUser(ctx, userID, workspaceID); err != nil {
		return GitHubAppConfigView{}, err
	}
	expectedCaller, err := loadWorkspaceServiceInstaller(ctx, store.db, trimmedUserID, trimmedWorkspaceID, false, "")
	if err != nil {
		return GitHubAppConfigView{}, err
	}
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := fenceGitHubProviderMutationInTx(ctx, tx, trimmedWorkspaceID); err != nil {
			return err
		}
		if _, err := loadWorkspaceServiceInstaller(ctx, tx, trimmedUserID, trimmedWorkspaceID, true, expectedCaller.Member.ID); err != nil {
			return err
		}
		if _, err := loadEnabledGitHubInstallationForUpdate(ctx, tx, trimmedWorkspaceID); err != nil {
			return err
		}
		return store.SaveGitHubAppConfigInTx(ctx, tx, trimmedUserID, trimmedWorkspaceID, params, time.Now().UTC())
	}); err != nil {
		return GitHubAppConfigView{}, err
	}

	config, found, err := store.LoadGitHubAppConfigForWorkspace(ctx, workspaceID)
	if err != nil {
		return GitHubAppConfigView{}, err
	}
	if !found {
		return GitHubAppConfigView{}, ErrNotFound
	}
	return githubAppConfigViewFromRecord(config), nil
}

// SaveGitHubAppConfigInTx encrypts and upserts one GitHub App configuration.
// The caller owns workspace authorization and must lock or install the matching
// AgentTeam App in the same transaction before invoking this helper.
func (store *Store) SaveGitHubAppConfigInTx(
	ctx context.Context,
	tx coredb.Tx,
	userID string,
	workspaceID string,
	params UpsertGitHubAppConfigParams,
	now time.Time,
) error {
	return store.saveGitHubAppConfigWithClaimInTx(ctx, tx, userID, workspaceID, params, "", now)
}

// SaveGitHubAppConfigAfterSetupInTx consumes the exact durable setup claim
// that authorized a provider webhook mutation and atomically makes it active
// with the config write. Generic saves deliberately cannot consume setup or
// patching claims.
func (store *Store) SaveGitHubAppConfigAfterSetupInTx(
	ctx context.Context,
	tx coredb.Tx,
	userID string,
	workspaceID string,
	params UpsertGitHubAppConfigParams,
	claimToken string,
	now time.Time,
) error {
	if strings.TrimSpace(claimToken) == "" {
		return badRequest("GitHub App setup claim is required.")
	}
	return store.saveGitHubAppConfigWithClaimInTx(ctx, tx, userID, workspaceID, params, claimToken, now)
}

func (store *Store) saveGitHubAppConfigWithClaimInTx(
	ctx context.Context,
	tx coredb.Tx,
	userID string,
	workspaceID string,
	params UpsertGitHubAppConfigParams,
	claimToken string,
	now time.Time,
) error {
	encryptedClientSecret, err := store.encryptOptionalGitHubAppConfigSecret(params.ClientSecret)
	if err != nil {
		return err
	}
	encryptedPrivateKeyPEM, err := store.encryptGitHubAppConfigSecret(params.PrivateKeyPEM)
	if err != nil {
		return err
	}
	encryptedWebhookSecret, err := store.encryptGitHubAppConfigSecret(params.WebhookSecret)
	if err != nil {
		return err
	}

	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedUserID := strings.TrimSpace(userID)
	if now.IsZero() {
		now = time.Now().UTC()
	} else {
		now = now.UTC()
	}
	creatorMemberID, err := resolveProviderConfigCreatorMemberID(ctx, tx, trimmedWorkspaceID, trimmedUserID)
	if err != nil {
		return err
	}
	configJSON, err := githubProviderConfigJSON(params.AppID, strings.TrimSpace(params.HTMLURL), strings.TrimSpace(params.OwnerLogin), params.OwnerID)
	if err != nil {
		return err
	}
	envelopeJSON, err := githubProviderCredentialEnvelopeJSON(
		strings.TrimSpace(params.ClientID),
		encryptedClientSecret,
		encryptedPrivateKeyPEM,
		encryptedWebhookSecret,
	)
	if err != nil {
		return err
	}
	if err := prepareGitHubAppClaimForConfigSaveInTx(
		ctx, tx, params.AppID, trimmedWorkspaceID, claimToken, now,
	); err != nil {
		return err
	}
	updateExisting := func(existingID string) error {
		if _, err := tx.Exec(ctx, `
UPDATE service_provider_configs
SET workspace_id = $2,
    provider_key = $3,
    display_name = $4,
    config = $5,
    credential_envelope = $6,
    credential_envelope_version = credential_envelope_version + 1,
    created_by_member_id = $7,
    updated_at = $8
WHERE id = $1
`, existingID, trimmedWorkspaceID, strings.TrimSpace(params.AppSlug), strings.TrimSpace(params.AppName), configJSON, envelopeJSON, creatorMemberID, now); err != nil {
			return fmt.Errorf("update github app config: %w", err)
		}
		return nil
	}
	type existingConfigIdentity struct {
		id    string
		appID int64
	}
	loadExistingForUpdate := func() (existingConfigIdentity, bool, error) {
		var existing existingConfigIdentity
		err := tx.QueryRow(ctx, `
SELECT id, COALESCE(CAST(JSON_EXTRACT(COALESCE(config, JSON_OBJECT()), '$.githubAppId') AS SIGNED), 0)
FROM service_provider_configs
WHERE workspace_id = $1
  AND provider_kind = 'github'
LIMIT 1
FOR UPDATE
`, trimmedWorkspaceID).Scan(&existing.id, &existing.appID)
		if err == nil {
			return existing, true, nil
		}
		if coredb.IsNotFound(err) {
			return existingConfigIdentity{}, false, nil
		}
		return existingConfigIdentity{}, false, fmt.Errorf("load github app config for update: %w", err)
	}
	requireSameApp := func(existing existingConfigIdentity) error {
		if existing.appID != params.AppID {
			return conflict("A different GitHub App is already configured for this workspace. Uninstall it before configuring another App.")
		}
		return nil
	}

	existing, found, err := loadExistingForUpdate()
	if err != nil {
		return err
	}
	if found {
		if err := requireSameApp(existing); err != nil {
			return err
		}
		if err := updateExisting(existing.id); err != nil {
			return err
		}
		return activateGitHubAppClaimAfterConfigSaveInTx(
			ctx, tx, params.AppID, trimmedWorkspaceID, claimToken, now,
		)
	}

	configID, err := identifiers.NewEntityID("ghcfg")
	if err != nil {
		return fmt.Errorf("generate github app config id: %w", err)
	}
	if _, err := tx.Exec(ctx, `
INSERT INTO service_provider_configs (
    id, workspace_id, provider_kind, provider_key, display_name,
    config, credential_envelope, credential_envelope_version,
    created_by_member_id, created_at, updated_at
)
VALUES ($1, $2, 'github', $3, $4, $5, $6, 1, $7, $8, $8)
`, configID, trimmedWorkspaceID, strings.TrimSpace(params.AppSlug), strings.TrimSpace(params.AppName), configJSON, envelopeJSON, creatorMemberID, now); err != nil {
		if !coredb.IsUniqueViolation(err) {
			return fmt.Errorf("insert github app config: %w", err)
		}
		existing, found, loadErr := loadExistingForUpdate()
		if loadErr != nil {
			return loadErr
		}
		if !found {
			return fmt.Errorf("insert github app config: %w", err)
		}
		if sameAppErr := requireSameApp(existing); sameAppErr != nil {
			return sameAppErr
		}
		if err := updateExisting(existing.id); err != nil {
			return err
		}
		return activateGitHubAppClaimAfterConfigSaveInTx(
			ctx, tx, params.AppID, trimmedWorkspaceID, claimToken, now,
		)
	}
	return activateGitHubAppClaimAfterConfigSaveInTx(
		ctx, tx, params.AppID, trimmedWorkspaceID, claimToken, now,
	)
}

func (store *Store) DeleteGitHubAppConfigForUser(ctx context.Context, userID string, workspaceID string) (GitHubAppConfigView, error) {
	if err := store.AuthorizeGitHubAppRegistrationForUser(ctx, userID, workspaceID); err != nil {
		return GitHubAppConfigView{}, err
	}

	trimmedUserID := strings.TrimSpace(userID)
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	expectedCaller, err := loadWorkspaceServiceInstaller(ctx, store.db, trimmedUserID, trimmedWorkspaceID, false, "")
	if err != nil {
		return GitHubAppConfigView{}, err
	}
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := fenceGitHubProviderMutationInTx(ctx, tx, trimmedWorkspaceID); err != nil {
			return err
		}
		if _, err := loadWorkspaceServiceInstaller(ctx, tx, trimmedUserID, trimmedWorkspaceID, true, expectedCaller.Member.ID); err != nil {
			return err
		}
		installation, installationFound, err := loadActiveGitHubInstallationForUpdate(ctx, tx, trimmedWorkspaceID)
		if err != nil {
			return err
		}
		if installationFound {
			connectionIDs, err := loadActiveConnectionIDsForUpdate(ctx, tx, installation.ID)
			if err != nil {
				return err
			}
			for _, connectionID := range connectionIDs {
				if err := store.disableAppConnectionInTx(ctx, tx, installation.ID, connectionID); err != nil {
					return err
				}
			}
		}
		if err := store.setGitHubSubscriptionsBlockedInTx(
			ctx, tx, trimmedWorkspaceID, "", true, time.Now().UTC(),
		); err != nil {
			return err
		}
		if _, err := tx.Exec(ctx, `DELETE FROM service_provider_configs WHERE workspace_id = $1 AND provider_kind = 'github'`, trimmedWorkspaceID); err != nil {
			return fmt.Errorf("delete github app config: %w", err)
		}
		return releaseProviderActiveGitHubAppClaimInTx(ctx, tx, trimmedWorkspaceID)
	}); err != nil {
		return GitHubAppConfigView{}, err
	}
	return GitHubAppConfigView{Registered: false}, nil
}

func loadActiveGitHubInstallationForUpdate(ctx context.Context, tx coredb.Tx, workspaceID string) (StoredAppInstallation, bool, error) {
	var installation StoredAppInstallation
	err := tx.QueryRow(ctx, `
SELECT id, service_definition_id, workspace_id
FROM service_installations
WHERE workspace_id = $1
  AND service_definition_id = 'app_github'
  AND status = 'active'
LIMIT 1
FOR UPDATE
`, strings.TrimSpace(workspaceID)).Scan(&installation.ID, &installation.AppID, &installation.WorkspaceID)
	if err != nil {
		if coredb.IsNotFound(err) {
			return StoredAppInstallation{}, false, nil
		}
		return StoredAppInstallation{}, false, fmt.Errorf("load active GitHub App installation for update: %w", err)
	}
	installation.Enabled = true
	return installation, true, nil
}

func loadActiveConnectionIDsForUpdate(ctx context.Context, tx coredb.Tx, installationID string) ([]string, error) {
	rows, err := tx.Query(ctx, `
SELECT id
FROM service_connections
WHERE service_installation_id = $1
  AND status = 'active'
ORDER BY created_at ASC, id ASC
FOR UPDATE
`, strings.TrimSpace(installationID))
	if err != nil {
		return nil, fmt.Errorf("load active GitHub connections for update: %w", err)
	}
	defer rows.Close()

	connectionIDs := make([]string, 0)
	for rows.Next() {
		var connectionID string
		if err := rows.Scan(&connectionID); err != nil {
			return nil, fmt.Errorf("scan active GitHub connection for update: %w", err)
		}
		connectionIDs = append(connectionIDs, strings.TrimSpace(connectionID))
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate active GitHub connections for update: %w", err)
	}
	return connectionIDs, nil
}

// PrepareGitHubConnectionForUser captures the exact local state on which a
// privileged direct GitHub connection attempt is based. The API must call
// this before its GitHub installation lookup; ApplyGitHubAppSetupForUser then
// rejects the result if an approval or another direct command committed first.
func (store *Store) PrepareGitHubConnectionForUser(
	ctx context.Context,
	userID string,
	workspaceID string,
	externalSubjectRef string,
) (GitHubAppConfig, GitHubConnectionMutationBase, error) {
	if err := store.AuthorizeGitHubAppInstallForUser(ctx, userID, workspaceID); err != nil {
		return GitHubAppConfig{}, GitHubConnectionMutationBase{}, err
	}
	trimmedExternalSubjectRef := strings.TrimSpace(externalSubjectRef)
	if trimmedExternalSubjectRef == "" {
		return GitHubAppConfig{}, GitHubConnectionMutationBase{}, badRequest("GitHub installation id is required.")
	}
	trimmedUserID := strings.TrimSpace(userID)
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	expectedCaller, err := loadWorkspaceServiceInstaller(ctx, store.db, trimmedUserID, trimmedWorkspaceID, false, "")
	if err != nil {
		return GitHubAppConfig{}, GitHubConnectionMutationBase{}, err
	}
	var config GitHubAppConfig
	var base GitHubConnectionMutationBase
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := fenceGitHubProviderMutationInTx(ctx, tx, trimmedWorkspaceID); err != nil {
			return err
		}
		if _, err := loadWorkspaceServiceInstaller(ctx, tx, trimmedUserID, trimmedWorkspaceID, true, expectedCaller.Member.ID); err != nil {
			return err
		}
		installation, loadErr := loadEnabledGitHubInstallationForUpdate(ctx, tx, trimmedWorkspaceID)
		if loadErr != nil {
			return loadErr
		}
		var configFound bool
		config, configFound, err = store.loadGitHubAppConfigForWorkspace(ctx, tx, trimmedWorkspaceID, true)
		if err != nil {
			return err
		}
		if !configFound {
			return badRequest("GitHub App is not registered.")
		}
		connection, connectionFound, err := loadActiveGitHubConnectionByExternalRefForUpdate(
			ctx, tx, installation.id, trimmedExternalSubjectRef,
		)
		if err != nil {
			return err
		}
		base = GitHubConnectionMutationBase{
			operation:          githubConnectionMutationConnect,
			workspaceID:        trimmedWorkspaceID,
			externalSubjectRef: trimmedExternalSubjectRef,
			configID:           config.ID,
			configAppID:        config.AppID,
			configUpdatedAt:    config.UpdatedAt,
			installation:       installation,
			connectionFound:    connectionFound,
			connection:         connection,
		}
		return nil
	}); err != nil {
		return GitHubAppConfig{}, GitHubConnectionMutationBase{}, err
	}
	return config, base, nil
}

func (store *Store) ApplyGitHubAppSetupForUser(
	ctx context.Context,
	userID string,
	workspaceID string,
	externalSubjectRef string,
	displayLabel string,
	base GitHubConnectionMutationBase,
) (AppInstallationView, error) {
	if err := store.AuthorizeGitHubAppInstallForUser(ctx, userID, workspaceID); err != nil {
		return AppInstallationView{}, err
	}
	connectionDisplayLabel := strings.TrimSpace(displayLabel)
	trimmedExternalSubjectRef := strings.TrimSpace(externalSubjectRef)
	if trimmedExternalSubjectRef == "" {
		return AppInstallationView{}, badRequest("GitHub installation id is required.")
	}
	if connectionDisplayLabel == "" {
		connectionDisplayLabel = trimmedExternalSubjectRef
	}
	trimmedUserID := strings.TrimSpace(userID)
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	if !base.validForConnect(trimmedWorkspaceID, trimmedExternalSubjectRef) {
		return AppInstallationView{}, conflict("GitHub connection changed. Restart installation.")
	}
	expectedCaller, err := loadWorkspaceServiceInstaller(ctx, store.db, trimmedUserID, trimmedWorkspaceID, false, "")
	if err != nil {
		return AppInstallationView{}, err
	}
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := fenceGitHubProviderMutationInTx(ctx, tx, trimmedWorkspaceID); err != nil {
			return err
		}
		if _, err := loadWorkspaceServiceInstaller(ctx, tx, trimmedUserID, trimmedWorkspaceID, true, expectedCaller.Member.ID); err != nil {
			return err
		}
		installation, loadErr := loadEnabledGitHubInstallationForUpdate(ctx, tx, trimmedWorkspaceID)
		if loadErr != nil {
			return loadErr
		}
		if !githubInstallationMutationSnapshotsEqual(base.installation, installation) {
			return conflict("GitHub App installation changed. Restart installation.")
		}
		config, configFound, err := store.loadGitHubAppConfigForWorkspace(ctx, tx, trimmedWorkspaceID, true)
		if err != nil {
			return err
		}
		if !configFound || strings.TrimSpace(config.ID) != strings.TrimSpace(base.configID) ||
			config.AppID != base.configAppID || !config.UpdatedAt.Equal(base.configUpdatedAt) {
			return conflict("GitHub configuration changed. Restart installation.")
		}
		connection, connectionFound, err := loadActiveGitHubConnectionByExternalRefForUpdate(
			ctx, tx, installation.id, trimmedExternalSubjectRef,
		)
		if err != nil {
			return err
		}
		if base.connectionFound != connectionFound ||
			(connectionFound && !githubConnectionMutationSnapshotsEqual(base.connection, connection)) {
			return conflict("GitHub connection changed. Restart installation.")
		}
		if _, err := store.upsertAppConnectionInTx(ctx, tx, UpsertAppConnectionParams{
			InstallationID:     installation.id,
			ExternalSubjectRef: trimmedExternalSubjectRef,
			DisplayLabel:       connectionDisplayLabel,
			Credentials: map[string]any{
				"webhook_secret": config.WebhookSecret,
			},
			CreatedByUserID: trimmedUserID,
		}); err != nil {
			return err
		}
		return store.setGitHubSubscriptionsBlockedInTx(
			ctx, tx, trimmedWorkspaceID, trimmedExternalSubjectRef, false, time.Now().UTC(),
		)
	}); err != nil {
		return AppInstallationView{}, err
	}
	return store.appInstallationViewByID(ctx, trimmedWorkspaceID, base.installation.id)
}

func loadWorkspaceServiceInstaller(ctx context.Context, queryer coredb.QueryRower, userID string, workspaceID string, forUpdate bool, expectedMemberID string) (access.HumanWorkspaceMember, error) {
	trimmedUserID := strings.TrimSpace(userID)
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	var (
		membership access.HumanWorkspaceMember
		err        error
	)
	if forUpdate {
		membership, err = access.LoadHumanWorkspaceMemberForUpdate(ctx, queryer, trimmedUserID, trimmedWorkspaceID)
	} else {
		membership, err = access.LoadHumanWorkspaceMember(ctx, queryer, trimmedUserID, trimmedWorkspaceID)
	}
	if err != nil {
		if errors.Is(err, access.ErrForbidden) || errors.Is(err, access.ErrNotFound) {
			return access.HumanWorkspaceMember{}, ErrForbidden
		}
		return access.HumanWorkspaceMember{}, err
	}
	if strings.TrimSpace(membership.Member.ID) == "" ||
		strings.TrimSpace(membership.Member.WorkspaceID) != trimmedWorkspaceID ||
		membership.Member.UserID == nil || strings.TrimSpace(*membership.Member.UserID) != trimmedUserID ||
		!access.HasWorkspaceAdminRole(domain.WorkspaceRole(membership.WorkspaceRole)) {
		return access.HumanWorkspaceMember{}, ErrForbidden
	}
	if forUpdate && (strings.TrimSpace(expectedMemberID) == "" || membership.Member.ID != strings.TrimSpace(expectedMemberID)) {
		return access.HumanWorkspaceMember{}, ErrForbidden
	}
	return membership, nil
}

func loadEnabledGitHubInstallationForUpdate(ctx context.Context, tx coredb.Tx, workspaceID string) (githubInstallationMutationSnapshot, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	var installation githubInstallationMutationSnapshot
	var disabledAt sql.NullTime
	err := tx.QueryRow(ctx, `
SELECT id, workspace_id, service_definition_id, service_kind, instance_key,
       status, installed_version, config, metadata,
       COALESCE(created_by_member_id, ''), created_by_actor_type,
       created_by_actor_id, created_at, updated_at, disabled_at
FROM service_installations
WHERE workspace_id = $1
  AND service_definition_id = 'app_github'
LIMIT 1
FOR UPDATE
`, trimmedWorkspaceID).Scan(
		&installation.id,
		&installation.workspaceID,
		&installation.serviceDefinitionID,
		&installation.serviceKind,
		&installation.instanceKey,
		&installation.status,
		&installation.installedVersion,
		&installation.config,
		&installation.metadata,
		&installation.createdByMemberID,
		&installation.createdByActorType,
		&installation.createdByActorID,
		&installation.createdAt,
		&installation.updatedAt,
		&disabledAt,
	)
	if err != nil {
		if coredb.IsNotFound(err) {
			return githubInstallationMutationSnapshot{}, conflict("GitHub App must be installed before provider setup.")
		}
		return githubInstallationMutationSnapshot{}, fmt.Errorf("load GitHub App installation for update: %w", err)
	}
	if disabledAt.Valid {
		at := disabledAt.Time.UTC()
		installation.disabledAt = &at
	}
	if strings.TrimSpace(installation.id) == "" ||
		strings.TrimSpace(installation.serviceDefinitionID) != githubAppDefinitionID ||
		strings.TrimSpace(installation.serviceKind) != githubAppServiceKind ||
		strings.TrimSpace(installation.workspaceID) != trimmedWorkspaceID ||
		strings.TrimSpace(installation.status) != "active" {
		return githubInstallationMutationSnapshot{}, conflict("GitHub App must be installed before provider setup.")
	}
	return installation, nil
}

func (store *Store) scanGitHubAppConfig(row coredb.Row) (GitHubAppConfig, error) {
	var (
		config                 GitHubAppConfig
		encryptedClientSecret  string
		encryptedPrivateKeyPEM string
		encryptedWebhookSecret string
	)
	if err := row.Scan(
		&config.ID,
		&config.WorkspaceID,
		&config.AppID,
		&config.AppSlug,
		&config.AppName,
		&config.HTMLURL,
		&config.ClientID,
		&encryptedClientSecret,
		&encryptedPrivateKeyPEM,
		&encryptedWebhookSecret,
		&config.OwnerLogin,
		&config.OwnerID,
		&config.CreatedByUserID,
		&config.CreatedAt,
		&config.UpdatedAt,
	); err != nil {
		return GitHubAppConfig{}, err
	}

	var err error
	config.ClientSecret, err = store.decryptGitHubAppConfigSecret(encryptedClientSecret)
	if err != nil {
		return GitHubAppConfig{}, fmt.Errorf("decrypt github app client secret: %w", err)
	}
	config.PrivateKeyPEM, err = store.decryptGitHubAppConfigSecret(encryptedPrivateKeyPEM)
	if err != nil {
		return GitHubAppConfig{}, fmt.Errorf("decrypt github app private key: %w", err)
	}
	config.WebhookSecret, err = store.decryptGitHubAppConfigSecret(encryptedWebhookSecret)
	if err != nil {
		return GitHubAppConfig{}, fmt.Errorf("decrypt github app webhook secret: %w", err)
	}
	config.ID = strings.TrimSpace(config.ID)
	config.WorkspaceID = strings.TrimSpace(config.WorkspaceID)
	config.AppSlug = strings.TrimSpace(config.AppSlug)
	config.AppName = strings.TrimSpace(config.AppName)
	config.HTMLURL = strings.TrimSpace(config.HTMLURL)
	config.ClientID = strings.TrimSpace(config.ClientID)
	config.ClientSecret = strings.TrimSpace(config.ClientSecret)
	config.PrivateKeyPEM = strings.TrimSpace(config.PrivateKeyPEM)
	config.WebhookSecret = strings.TrimSpace(config.WebhookSecret)
	config.OwnerLogin = strings.TrimSpace(config.OwnerLogin)
	config.CreatedByUserID = strings.TrimSpace(config.CreatedByUserID)
	return config, nil
}

func githubAppConfigViewFromRecord(config GitHubAppConfig) GitHubAppConfigView {
	return GitHubAppConfigView{
		Registered: true,
		AppID:      config.AppID,
		AppName:    config.AppName,
		AppSlug:    config.AppSlug,
		HTMLURL:    config.HTMLURL,
		OwnerLogin: config.OwnerLogin,
	}
}

func (store *Store) ListGitHubConnectionsForUser(ctx context.Context, userID string, workspaceID string) ([]AppConnectionView, error) {
	if err := store.requireWorkspaceAccess(ctx, userID, workspaceID); err != nil {
		return nil, err
	}

	installation, found, err := store.findAppInstallationByWorkspaceAndApp(ctx, workspaceID, "app_github")
	if err != nil {
		return nil, err
	}
	if !found || !installation.Enabled {
		return []AppConnectionView{}, nil
	}
	return store.ListAppConnections(ctx, installation.ID)
}

// PrepareGitHubConnectionDisableForUser captures the exact active connection
// an administrator intends to disable. Returning this token to
// DisableGitHubConnectionForUser prevents the direct delete from disabling a
// connection which an approval updated after the command began.
func (store *Store) PrepareGitHubConnectionDisableForUser(
	ctx context.Context,
	userID string,
	workspaceID string,
	connectionID string,
) (GitHubConnectionMutationBase, error) {
	if err := store.AuthorizeGitHubAppInstallForUser(ctx, userID, workspaceID); err != nil {
		return GitHubConnectionMutationBase{}, err
	}
	trimmedConnectionID := strings.TrimSpace(connectionID)
	if trimmedConnectionID == "" {
		return GitHubConnectionMutationBase{}, badRequest("connectionId is required")
	}
	trimmedUserID := strings.TrimSpace(userID)
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	expectedCaller, err := loadWorkspaceServiceInstaller(ctx, store.db, trimmedUserID, trimmedWorkspaceID, false, "")
	if err != nil {
		return GitHubConnectionMutationBase{}, err
	}
	var base GitHubConnectionMutationBase
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := fenceGitHubProviderMutationInTx(ctx, tx, trimmedWorkspaceID); err != nil {
			return err
		}
		if _, err := loadWorkspaceServiceInstaller(ctx, tx, trimmedUserID, trimmedWorkspaceID, true, expectedCaller.Member.ID); err != nil {
			return err
		}
		installation, err := loadEnabledGitHubInstallationForUpdate(ctx, tx, trimmedWorkspaceID)
		if err != nil {
			return err
		}
		connection, found, err := loadActiveGitHubConnectionByIDForUpdate(ctx, tx, installation.id, trimmedConnectionID)
		if err != nil {
			return err
		}
		if !found {
			return ErrNotFound
		}
		base = GitHubConnectionMutationBase{
			operation:          githubConnectionMutationDisconnect,
			workspaceID:        trimmedWorkspaceID,
			externalSubjectRef: connection.externalSubjectRef,
			connectionID:       trimmedConnectionID,
			installation:       installation,
			connectionFound:    true,
			connection:         connection,
		}
		return nil
	}); err != nil {
		return GitHubConnectionMutationBase{}, err
	}
	return base, nil
}

func (store *Store) DisableGitHubConnectionForUser(
	ctx context.Context,
	userID string,
	workspaceID string,
	connectionID string,
	base GitHubConnectionMutationBase,
) error {
	if err := store.AuthorizeGitHubAppInstallForUser(ctx, userID, workspaceID); err != nil {
		return err
	}
	trimmedConnectionID := strings.TrimSpace(connectionID)
	if trimmedConnectionID == "" {
		return badRequest("connectionId is required")
	}
	trimmedUserID := strings.TrimSpace(userID)
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	if !base.validForDisconnect(trimmedWorkspaceID, trimmedConnectionID) {
		return conflict("GitHub connection changed. Retry the disconnect.")
	}
	expectedCaller, err := loadWorkspaceServiceInstaller(ctx, store.db, trimmedUserID, trimmedWorkspaceID, false, "")
	if err != nil {
		return err
	}
	return coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := fenceGitHubProviderMutationInTx(ctx, tx, trimmedWorkspaceID); err != nil {
			return err
		}
		if _, err := loadWorkspaceServiceInstaller(ctx, tx, trimmedUserID, trimmedWorkspaceID, true, expectedCaller.Member.ID); err != nil {
			return err
		}
		installation, err := loadEnabledGitHubInstallationForUpdate(ctx, tx, trimmedWorkspaceID)
		if err != nil {
			return err
		}
		connection, found, err := loadActiveGitHubConnectionByIDForUpdate(ctx, tx, base.installation.id, trimmedConnectionID)
		if err != nil {
			return err
		}
		if !found || !githubConnectionMutationSnapshotsEqual(base.connection, connection) {
			return conflict("GitHub connection changed. Retry the disconnect.")
		}
		if !githubInstallationMutationSnapshotsEqual(base.installation, installation) {
			return conflict("GitHub App installation changed. Retry the disconnect.")
		}
		now := time.Now().UTC()
		if err := store.disableAppConnectionInTx(ctx, tx, installation.id, trimmedConnectionID); err != nil {
			return err
		}
		return store.setGitHubSubscriptionsBlockedInTx(
			ctx, tx, trimmedWorkspaceID, connection.externalSubjectRef, true, now,
		)
	})
}

func (base GitHubConnectionMutationBase) validForConnect(workspaceID string, externalSubjectRef string) bool {
	return base.operation == githubConnectionMutationConnect &&
		strings.TrimSpace(base.workspaceID) == strings.TrimSpace(workspaceID) &&
		strings.TrimSpace(base.installation.id) != "" &&
		strings.TrimSpace(base.externalSubjectRef) == strings.TrimSpace(externalSubjectRef) &&
		strings.TrimSpace(base.configID) != "" &&
		base.configAppID > 0 &&
		!base.configUpdatedAt.IsZero()
}

func (base GitHubConnectionMutationBase) validForDisconnect(workspaceID string, connectionID string) bool {
	return base.operation == githubConnectionMutationDisconnect &&
		strings.TrimSpace(base.workspaceID) == strings.TrimSpace(workspaceID) &&
		strings.TrimSpace(base.installation.id) != "" &&
		strings.TrimSpace(base.connectionID) == strings.TrimSpace(connectionID) &&
		strings.TrimSpace(base.externalSubjectRef) != "" &&
		base.connectionFound
}

func githubInstallationMutationSnapshotsEqual(left githubInstallationMutationSnapshot, right githubInstallationMutationSnapshot) bool {
	return strings.TrimSpace(left.id) == strings.TrimSpace(right.id) &&
		strings.TrimSpace(left.workspaceID) == strings.TrimSpace(right.workspaceID) &&
		strings.TrimSpace(left.serviceDefinitionID) == strings.TrimSpace(right.serviceDefinitionID) &&
		strings.TrimSpace(left.serviceKind) == strings.TrimSpace(right.serviceKind) &&
		strings.TrimSpace(left.instanceKey) == strings.TrimSpace(right.instanceKey) &&
		strings.TrimSpace(left.status) == strings.TrimSpace(right.status) &&
		strings.TrimSpace(left.installedVersion) == strings.TrimSpace(right.installedVersion) &&
		bytes.Equal(left.config, right.config) &&
		bytes.Equal(left.metadata, right.metadata) &&
		strings.TrimSpace(left.createdByMemberID) == strings.TrimSpace(right.createdByMemberID) &&
		strings.TrimSpace(left.createdByActorType) == strings.TrimSpace(right.createdByActorType) &&
		strings.TrimSpace(left.createdByActorID) == strings.TrimSpace(right.createdByActorID) &&
		left.createdAt.Equal(right.createdAt) &&
		left.updatedAt.Equal(right.updatedAt) &&
		optionalTimesEqual(left.disabledAt, right.disabledAt)
}

func loadActiveGitHubConnectionByExternalRefForUpdate(
	ctx context.Context,
	queryer coredb.QueryRower,
	installationID string,
	externalSubjectRef string,
) (githubConnectionMutationSnapshot, bool, error) {
	return loadActiveGitHubConnectionMutationSnapshotForUpdate(
		ctx,
		queryer,
		`service_installation_id = $1 AND external_subject_ref = $2`,
		strings.TrimSpace(installationID),
		strings.TrimSpace(externalSubjectRef),
	)
}

func loadActiveGitHubConnectionByIDForUpdate(
	ctx context.Context,
	queryer coredb.QueryRower,
	installationID string,
	connectionID string,
) (githubConnectionMutationSnapshot, bool, error) {
	return loadActiveGitHubConnectionMutationSnapshotForUpdate(
		ctx,
		queryer,
		`service_installation_id = $1 AND id = $2`,
		strings.TrimSpace(installationID),
		strings.TrimSpace(connectionID),
	)
}

func loadActiveGitHubConnectionMutationSnapshotForUpdate(
	ctx context.Context,
	queryer coredb.QueryRower,
	predicate string,
	first string,
	second string,
) (githubConnectionMutationSnapshot, bool, error) {
	var snapshot githubConnectionMutationSnapshot
	var disabledAt sql.NullTime
	query := `
SELECT id, workspace_id, service_definition_id, service_installation_id,
       service_kind, external_subject_ref, display_label, credentials, config,
       status, created_by_member_id, created_at, updated_at, disabled_at
FROM service_connections
WHERE ` + predicate + `
  AND service_definition_id = $3
  AND service_kind = $4
  AND status = 'active'
LIMIT 1
FOR UPDATE
`
	err := queryer.QueryRow(ctx, query, first, second, githubAppDefinitionID, githubAppServiceKind).Scan(
		&snapshot.id,
		&snapshot.workspaceID,
		&snapshot.serviceDefinitionID,
		&snapshot.serviceInstallationID,
		&snapshot.serviceKind,
		&snapshot.externalSubjectRef,
		&snapshot.displayLabel,
		&snapshot.credentials,
		&snapshot.config,
		&snapshot.status,
		&snapshot.createdByMemberID,
		&snapshot.createdAt,
		&snapshot.updatedAt,
		&disabledAt,
	)
	if err != nil {
		if coredb.IsNotFound(err) {
			return githubConnectionMutationSnapshot{}, false, nil
		}
		return githubConnectionMutationSnapshot{}, false, fmt.Errorf("load active GitHub connection for update: %w", err)
	}
	if disabledAt.Valid {
		at := disabledAt.Time.UTC()
		snapshot.disabledAt = &at
	}
	return snapshot, true, nil
}

func githubConnectionMutationSnapshotsEqual(left githubConnectionMutationSnapshot, right githubConnectionMutationSnapshot) bool {
	return strings.TrimSpace(left.id) == strings.TrimSpace(right.id) &&
		strings.TrimSpace(left.workspaceID) == strings.TrimSpace(right.workspaceID) &&
		strings.TrimSpace(left.serviceDefinitionID) == strings.TrimSpace(right.serviceDefinitionID) &&
		strings.TrimSpace(left.serviceInstallationID) == strings.TrimSpace(right.serviceInstallationID) &&
		strings.TrimSpace(left.serviceKind) == strings.TrimSpace(right.serviceKind) &&
		strings.TrimSpace(left.externalSubjectRef) == strings.TrimSpace(right.externalSubjectRef) &&
		strings.TrimSpace(left.displayLabel) == strings.TrimSpace(right.displayLabel) &&
		strings.TrimSpace(left.status) == strings.TrimSpace(right.status) &&
		strings.TrimSpace(left.createdByMemberID) == strings.TrimSpace(right.createdByMemberID) &&
		left.createdAt.Equal(right.createdAt) &&
		left.updatedAt.Equal(right.updatedAt) &&
		optionalTimesEqual(left.disabledAt, right.disabledAt) &&
		bytes.Equal(left.credentials, right.credentials) &&
		bytes.Equal(left.config, right.config)
}

func optionalTimesEqual(left *time.Time, right *time.Time) bool {
	if left == nil || right == nil {
		return left == nil && right == nil
	}
	return left.Equal(*right)
}

func (store *Store) requireSystemAdminUser(ctx context.Context, userID string) error {
	row := store.db.QueryRow(ctx, `
SELECT is_admin
FROM users
WHERE id = $1
`, strings.TrimSpace(userID))

	var isAdmin bool
	if err := row.Scan(&isAdmin); err != nil {
		if coredb.IsNotFound(err) {
			return ErrUnauthorized
		}
		return fmt.Errorf("load system admin user: %w", err)
	}
	if !isAdmin {
		return ErrForbidden
	}
	return nil
}