package app

import (
	"context"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/app/installation"
	"github.com/tidbcloud/AgentTeam/internal/store/app/registry"
	"github.com/tidbcloud/AgentTeam/internal/store/model"
	"github.com/tidbcloud/AgentTeam/internal/store/provider"
	"github.com/tidbcloud/AgentTeam/internal/store/serviceactor"
)

const githubSetupVerificationTTL = 5 * time.Minute

const (
	githubVerificationExpectedConfigIDKey        = "_agentteam_expected_config_id"
	githubVerificationExpectedConfigUpdatedAtKey = "_agentteam_expected_config_updated_at"
	githubVerificationRequireInstallationKey     = "_agentteam_require_active_installation"
)

type githubActiveConfigSnapshot struct {
	ID        string
	AppID     int64
	AppSlug   string
	UpdatedAt time.Time
}

// BeginGitHubAppSetupVerificationForUser fences every GitHub activation path
// before recovery changes the provider webhook. The encrypted credential
// snapshot is never approval-ready while the external mutation is in flight.
func (store *Store) BeginGitHubAppSetupVerificationForUser(
	ctx context.Context,
	userID string,
	workspaceID string,
	input GitHubSetupVerificationInput,
) (GitHubSetupVerificationLease, error) {
	expected, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return GitHubSetupVerificationLease{}, err
	}
	mode := strings.TrimSpace(input.Mode)
	switch mode {
	case GitHubAppSetupModeAdmin:
	case GitHubAppSetupModeRequester:
		return GitHubSetupVerificationLease{}, conflict("Workspace members must submit a GitHub setup proposal before any webhook update.")
	default:
		return GitHubSetupVerificationLease{}, badRequest("GitHub setup mode is invalid.")
	}
	// A downgraded caller may retry an operation whose authorization was already
	// linearized at Begin. Only run privileged settlement for a currently
	// authorized new admin operation; the transaction below remains authoritative.
	if mode == GitHubAppSetupModeAdmin && requireGitHubSetupMode(expected, mode) == nil {
		if err := store.settlePrivilegedGitHubPendingRequests(ctx, workspaceID); err != nil {
			return GitHubSetupVerificationLease{}, err
		}
	}
	leaseID, err := identifiers.NewEntityID("ghverify")
	if err != nil {
		return GitHubSetupVerificationLease{}, fmt.Errorf("generate GitHub setup verification id: %w", err)
	}

	params := input.Config
	var result GitHubSetupVerificationLease
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := installation.LockAppInstallationMutationScope(ctx, tx, registry.GitHubAppID); err != nil {
			return err
		}
		now := time.Now().UTC()
		if err := deleteExpiredGitHubSetupVerificationsInTx(ctx, tx, workspaceID, now.Add(-githubSetupVerificationTTL)); err != nil {
			return err
		}
		existing, found, err := loadReusableGitHubSetupVerificationForUpdate(
			ctx, tx, workspaceID, expected.Member.ID, mode,
		)
		if err != nil {
			return err
		}
		if found {
			if _, err := store.revalidateGitHubSetupMemberForUpdate(
				ctx, tx, userID, workspaceID, expected,
			); err != nil {
				return err
			}
			storedParams, err := store.validateGitHubSetupVerificationLeaseSnapshot(
				ctx, tx, workspaceID, existing,
			)
			if err != nil {
				return err
			}
			if storedParams.AppID != params.AppID {
				return conflict("GitHub App setup identity changed. Restart setup.")
			}
			switch strings.TrimSpace(existing.Status) {
			case AppInstallationRequestStatusSetupVerifying:
				createdAt := now.Truncate(time.Second)
				if _, err := tx.Exec(ctx, `
UPDATE service_installation_requests
SET created_at = $1,
    updated_at = $1
WHERE id = $2
  AND workspace_id = $3
  AND status = $4
`, createdAt, existing.ID, strings.TrimSpace(workspaceID),
					AppInstallationRequestStatusSetupVerifying); err != nil {
					return fmt.Errorf("renew GitHub setup verification: %w", err)
				}
			case AppInstallationRequestStatusSetupPatching:
				if err := requireGitHubAppPatchingClaimForUpdate(
					ctx, tx, storedParams.AppID, workspaceID, existing.ID,
				); err != nil {
					return err
				}
			default:
				return conflict("GitHub setup verification state changed. Restart setup.")
			}
			result = GitHubSetupVerificationLease{
				ID:            existing.ID,
				Mode:          mode,
				WebhookSecret: strings.TrimSpace(storedParams.WebhookSecret),
			}
			return nil
		}

		current, err := store.revalidateGitHubSetupMemberForUpdate(ctx, tx, userID, workspaceID, expected)
		if err != nil {
			return err
		}
		if err := requireGitHubSetupMode(current, mode); err != nil {
			return err
		}
		if err := requireNoGitHubSetupVerificationInTx(ctx, tx, workspaceID); err != nil {
			return err
		}
		if mode == GitHubAppSetupModeRequester {
			// A ready administrator draft is only a one-hour reservation. Garbage
			// collect it before using it as a requester admission fence so an
			// abandoned draft cannot strand ordinary-member setup indefinitely.
			if err := deleteExpiredGitHubSetupProposalsInTx(
				ctx, tx, workspaceID, now.Add(-githubSetupProposalTTL),
			); err != nil {
				return err
			}
			blocked, err := githubSetupBlockedForMember(ctx, tx, workspaceID, true)
			if err != nil {
				return err
			}
			if blocked {
				return conflict("GitHub is already active for this workspace.")
			}
			if err := requireNoGitHubAdminSetupDraftInTx(ctx, tx, workspaceID); err != nil {
				return err
			}
		} else if input.RequireActiveInstallation {
			if err := requireActiveGitHubInstallationForSetupUpdateInTx(ctx, tx, workspaceID); err != nil {
				return err
			}
		}

		active, found, err := loadGitHubActiveConfigSnapshotForUpdate(ctx, tx, workspaceID)
		if err != nil {
			return err
		}
		expectedConfigID := strings.TrimSpace(input.ExpectedConfigID)
		if found != (expectedConfigID != "") || found &&
			(strings.TrimSpace(active.ID) != expectedConfigID || !active.UpdatedAt.Equal(input.ExpectedConfigUpdatedAt.UTC())) {
			return conflict("GitHub configuration changed. Restart setup.")
		}
		if found && active.AppID != params.AppID {
			return conflict("A different GitHub App is already configured for this workspace. Uninstall it before configuring another App.")
		}

		storedConfig, err := store.prepareStoredGitHubAppConfigProposal(params)
		if err != nil {
			return err
		}
		prepared, err := prepareAppInstallationAtVersion(ctx, tx, registry.GitHubAppID, registry.GitHubAppSlug, "", nil)
		if err != nil {
			return err
		}
		definition, err := store.syncAppServiceDefinition(ctx, tx, prepared.app.ID, now)
		if err != nil {
			return err
		}
		if strings.TrimSpace(definition.ID) != registry.GitHubAppID {
			return conflict("GitHub App definition changed. Restart setup.")
		}
		requestedConfigJSON, err := model.MarshalJSONObject("GitHub setup verification config", storedConfig)
		if err != nil {
			return fmt.Errorf("encode GitHub setup verification config: %w", err)
		}
		requestedScopesJSON, err := model.MarshalJSONObject("GitHub setup verification scopes", registry.SortedUniqueScopes(prepared.version.Scopes))
		if err != nil {
			return fmt.Errorf("encode GitHub setup verification scopes: %w", err)
		}
		expectedConfig := map[string]any{
			githubVerificationExpectedConfigIDKey:        strings.TrimSpace(input.ExpectedConfigID),
			githubVerificationExpectedConfigUpdatedAtKey: "",
			githubVerificationRequireInstallationKey:     input.RequireActiveInstallation,
		}
		if strings.TrimSpace(input.ExpectedConfigID) != "" {
			expectedConfig[githubVerificationExpectedConfigUpdatedAtKey] = input.ExpectedConfigUpdatedAt.UTC().Format(time.RFC3339Nano)
		}
		expectedConfigJSON, err := model.MarshalJSONObject("GitHub setup verification base config", expectedConfig)
		if err != nil {
			return fmt.Errorf("encode GitHub setup verification base config: %w", err)
		}

		// Verification stays read-only until Mark. Ready drafts and pending
		// requests remain independent contenders; only a durable patching winner
		// invalidates them.
		createdAt := now.Truncate(time.Second)
		if _, err := tx.Exec(ctx, `
INSERT INTO service_installation_requests (
    id, workspace_id, service_definition_id, service_kind, request_kind,
    requested_version, requested_config, base_config, requested_scopes,
    status, request_reason, requested_by_member_id, decision_reason,
    service_installation_id, created_at, updated_at
) VALUES (
    $1, $2, $3, $4, $5,
    $6, $7, $8, $9,
    $10, $11, $12, '',
    NULL, $13, $13
)
`, leaseID, strings.TrimSpace(workspaceID), registry.GitHubAppID, serviceactor.KindApp,
			AppInstallationRequestKindInstall, prepared.version.Version, requestedConfigJSON, expectedConfigJSON,
			requestedScopesJSON, AppInstallationRequestStatusSetupVerifying, mode, current.Member.ID, createdAt); err != nil {
			return fmt.Errorf("create GitHub setup verification lease: %w", err)
		}
		result = GitHubSetupVerificationLease{
			ID:            leaseID,
			Mode:          mode,
			WebhookSecret: strings.TrimSpace(params.WebhookSecret),
		}
		return nil
	}); err != nil {
		return GitHubSetupVerificationLease{}, err
	}
	return result, nil
}

// ResumeGitHubSetupVerificationForUser returns an already-authorized durable
// verification owned by the same member. It intentionally runs before current
// role gates in HTTP handlers so a role change cannot strand a provider PATCH
// whose only matching secret lives in this snapshot. It never creates a lease.
func (store *Store) ResumeGitHubSetupVerificationForUser(
	ctx context.Context,
	userID string,
	workspaceID string,
	mode string,
) (provider.GitHubAppConfig, GitHubSetupVerificationLease, bool, error) {
	expected, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return provider.GitHubAppConfig{}, GitHubSetupVerificationLease{}, false, err
	}
	trimmedMode := strings.TrimSpace(mode)
	if trimmedMode == GitHubAppSetupModeRequester {
		return provider.GitHubAppConfig{}, GitHubSetupVerificationLease{}, false,
			conflict("Workspace members must submit a GitHub setup proposal before any webhook update.")
	}
	if trimmedMode != GitHubAppSetupModeAdmin {
		return provider.GitHubAppConfig{}, GitHubSetupVerificationLease{}, false, badRequest("GitHub setup mode is invalid.")
	}
	var (
		config provider.GitHubAppConfig
		lease  GitHubSetupVerificationLease
		found  bool
	)
	err = coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := installation.LockAppInstallationMutationScope(ctx, tx, registry.GitHubAppID); err != nil {
			return err
		}
		now := time.Now().UTC()
		if err := deleteExpiredGitHubSetupVerificationsInTx(
			ctx, tx, workspaceID, now.Add(-githubSetupVerificationTTL),
		); err != nil {
			return err
		}
		existing, reusable, err := loadReusableGitHubSetupVerificationForUpdate(
			ctx, tx, workspaceID, expected.Member.ID, trimmedMode,
		)
		if err != nil || !reusable {
			return err
		}
		if _, err := store.revalidateGitHubSetupMemberForUpdate(
			ctx, tx, userID, workspaceID, expected,
		); err != nil {
			return err
		}
		params, err := store.validateGitHubSetupVerificationLeaseSnapshot(ctx, tx, workspaceID, existing)
		if err != nil {
			return err
		}
		switch strings.TrimSpace(existing.Status) {
		case AppInstallationRequestStatusSetupVerifying:
			createdAt := now.Truncate(time.Second)
			if _, err := tx.Exec(ctx, `
UPDATE service_installation_requests
SET created_at = $1,
    updated_at = $1
WHERE id = $2
  AND workspace_id = $3
  AND status = $4
`, createdAt, existing.ID, strings.TrimSpace(workspaceID),
				AppInstallationRequestStatusSetupVerifying); err != nil {
				return fmt.Errorf("renew GitHub setup verification: %w", err)
			}
		case AppInstallationRequestStatusSetupPatching:
			if err := requireGitHubAppPatchingClaimForUpdate(
				ctx, tx, params.AppID, workspaceID, existing.ID,
			); err != nil {
				return err
			}
		default:
			return conflict("GitHub setup verification state changed. Restart setup.")
		}
		config = githubAppConfigFromSetupParams(workspaceID, params)
		lease = GitHubSetupVerificationLease{
			ID:            existing.ID,
			Mode:          trimmedMode,
			WebhookSecret: strings.TrimSpace(params.WebhookSecret),
		}
		found = true
		return nil
	})
	if err != nil {
		return provider.GitHubAppConfig{}, GitHubSetupVerificationLease{}, false, err
	}
	return config, lease, found, nil
}

func loadReusableGitHubSetupVerificationForUpdate(
	ctx context.Context,
	queryer coredb.QueryRower,
	workspaceID string,
	memberID string,
	mode string,
) (AppInstallationRequestView, bool, error) {
	query := appInstallationRequestSelectSQL() + `
WHERE req.workspace_id = $1
  AND req.service_definition_id = $2
  AND req.service_kind = $3
  AND req.request_kind = $4
  AND req.requested_by_member_id = $5
  AND req.request_reason = $6
  AND req.status IN ($7, $8)
ORDER BY CASE WHEN req.status = $8 THEN 0 ELSE 1 END,
         req.created_at ASC,
         req.id ASC
LIMIT 1
FOR UPDATE`
	lease, err := scanAppInstallationRequest(queryer.QueryRow(
		ctx, query, strings.TrimSpace(workspaceID), registry.GitHubAppID,
		serviceactor.KindApp, AppInstallationRequestKindInstall, strings.TrimSpace(memberID),
		strings.TrimSpace(mode), AppInstallationRequestStatusSetupVerifying,
		AppInstallationRequestStatusSetupPatching,
	))
	if err != nil {
		if coredb.IsNotFound(err) {
			return AppInstallationRequestView{}, false, nil
		}
		return AppInstallationRequestView{}, false, fmt.Errorf("load reusable GitHub setup verification: %w", err)
	}
	return lease, true, nil
}

func (store *Store) validateGitHubSetupVerificationLeaseSnapshot(
	ctx context.Context,
	tx coredb.Tx,
	workspaceID string,
	lease AppInstallationRequestView,
) (provider.UpsertGitHubAppConfigParams, error) {
	if err := validateGitHubSetupVerificationConfigSnapshot(ctx, tx, workspaceID, lease); err != nil {
		return provider.UpsertGitHubAppConfigParams{}, err
	}
	if githubSetupVerificationRequiresActiveInstallation(lease) {
		if err := requireActiveGitHubInstallationForSetupUpdateInTx(ctx, tx, workspaceID); err != nil {
			return provider.UpsertGitHubAppConfigParams{}, err
		}
	}
	prepared, err := prepareAppInstallationAtVersion(
		ctx, tx, registry.GitHubAppID, registry.GitHubAppSlug, lease.RequestedVersion, nil,
	)
	if err != nil {
		return provider.UpsertGitHubAppConfigParams{}, err
	}
	if strings.TrimSpace(prepared.version.Version) != strings.TrimSpace(lease.RequestedVersion) ||
		!equalSortedScopes(prepared.version.Scopes, lease.RequestedScopes) {
		return provider.UpsertGitHubAppConfigParams{}, conflict("GitHub App definition changed. Restart setup.")
	}
	params, found, err := store.verifiedGitHubAppConfigFromStoredMap(
		appInstallationRequestStoredRequestedConfig(lease),
	)
	if err != nil {
		return provider.UpsertGitHubAppConfigParams{}, err
	}
	if !found {
		return provider.UpsertGitHubAppConfigParams{}, conflict("GitHub setup verification is missing. Restart setup.")
	}
	return params, nil
}

func requireActiveGitHubInstallationForSetupUpdateInTx(
	ctx context.Context,
	queryer coredb.QueryRower,
	workspaceID string,
) error {
	var installationID string
	err := queryer.QueryRow(ctx, `
SELECT id
FROM service_installations
WHERE workspace_id = $1
  AND service_definition_id = $2
  AND service_kind = $3
	  AND instance_key = $4
	  AND status = $5
LIMIT 1
FOR UPDATE
`, strings.TrimSpace(workspaceID), registry.GitHubAppID, serviceactor.KindApp,
		serviceactor.DefaultInstanceKey, serviceactor.StatusActive).Scan(&installationID)
	if err != nil {
		if coredb.IsNotFound(err) {
			return conflict("Fresh GitHub setup must select an organization installation before activation.")
		}
		return fmt.Errorf("load active GitHub installation for config recovery: %w", err)
	}
	return nil
}

// MarkGitHubSetupVerificationProviderMutationStartedForUser atomically enters
// the durable, non-expiring point-of-no-return state immediately before the
// GitHub webhook PATCH. Authorization was linearized by Begin; this method
// binds the same active member/lease but deliberately does not re-check role.
func (store *Store) MarkGitHubSetupVerificationProviderMutationStartedForUser(
	ctx context.Context,
	userID string,
	workspaceID string,
	leaseID string,
) error {
	expected, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return err
	}
	expired := false
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := installation.LockAppInstallationMutationScope(ctx, tx, registry.GitHubAppID); err != nil {
			return err
		}
		if _, err := store.revalidateGitHubSetupMemberForUpdate(
			ctx, tx, userID, workspaceID, expected,
		); err != nil {
			return err
		}
		lease, err := loadGitHubSetupVerificationForMutationForUpdate(
			ctx, tx, workspaceID, leaseID, expected.Member.ID,
		)
		if err != nil {
			return err
		}
		mode := strings.TrimSpace(lease.RequestReason)
		if mode != GitHubAppSetupModeAdmin {
			return conflict("GitHub setup verification mode changed. Restart setup.")
		}
		params, err := store.validateGitHubSetupVerificationLeaseSnapshot(ctx, tx, workspaceID, lease)
		if err != nil {
			return err
		}
		now := time.Now().UTC()
		switch strings.TrimSpace(lease.Status) {
		case AppInstallationRequestStatusSetupPatching:
			return requireGitHubAppPatchingClaimForUpdate(
				ctx, tx, params.AppID, workspaceID, lease.ID,
			)
		case AppInstallationRequestStatusSetupVerifying:
			if lease.CreatedAt.Before(now.Add(-githubSetupVerificationTTL)) {
				expired = true
				return deleteGitHubSetupVerificationByIDInTx(
					ctx, tx, workspaceID, lease.ID, expected.Member.ID,
				)
			}
		default:
			return conflict("GitHub setup verification state changed. Restart setup.")
		}
		if err := markGitHubAppClaimPatchingInTx(
			ctx, tx, params.AppID, workspaceID, lease.ID,
			true, now,
		); err != nil {
			return err
		}
		result, err := tx.Exec(ctx, `
UPDATE service_installation_requests
SET status = $1,
    updated_at = $2
WHERE id = $3
  AND workspace_id = $4
  AND requested_by_member_id = $5
  AND status = $6
`, AppInstallationRequestStatusSetupPatching, now, lease.ID,
			strings.TrimSpace(workspaceID), expected.Member.ID,
			AppInstallationRequestStatusSetupVerifying)
		if err != nil {
			return fmt.Errorf("mark GitHub setup provider mutation started: %w", err)
		}
		if result.RowsAffected() != 1 {
			return conflict("GitHub setup verification state changed. Restart setup.")
		}
		return nil
	}); err != nil {
		return err
	}
	if expired {
		return gone("GitHub setup verification expired. Restart setup.")
	}
	return nil
}

func (store *Store) FinalizeGitHubSetupVerificationForRequester(
	ctx context.Context,
	userID string,
	workspaceID string,
	leaseID string,
) (GitHubSetupProposalView, error) {
	_ = ctx
	_ = userID
	_ = workspaceID
	_ = leaseID
	return GitHubSetupProposalView{}, conflict("Workspace members must submit a GitHub setup proposal before any webhook update.")
}

func (store *Store) FinalizeGitHubSetupVerificationForWorkspaceAdmin(
	ctx context.Context,
	userID string,
	workspaceID string,
	leaseID string,
) (provider.GitHubAppConfigView, error) {
	expected, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return provider.GitHubAppConfigView{}, err
	}
	var params provider.UpsertGitHubAppConfigParams
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := installation.LockAppInstallationMutationScope(ctx, tx, registry.GitHubAppID); err != nil {
			return err
		}
		if _, err := store.revalidateGitHubSetupMemberForUpdate(
			ctx, tx, userID, workspaceID, expected,
		); err != nil {
			return err
		}
		lease, err := loadGitHubSetupVerificationForMutationForUpdate(ctx, tx, workspaceID, leaseID, expected.Member.ID)
		if err != nil {
			return err
		}
		if strings.TrimSpace(lease.RequestReason) != GitHubAppSetupModeAdmin ||
			strings.TrimSpace(lease.Status) != AppInstallationRequestStatusSetupPatching {
			return conflict("GitHub setup verification mode changed. Restart setup.")
		}
		now := time.Now().UTC()
		params, err = store.validateGitHubSetupVerificationLeaseSnapshot(ctx, tx, workspaceID, lease)
		if err != nil {
			return err
		}
		if err := requireGitHubAppPatchingClaimForUpdate(
			ctx, tx, params.AppID, workspaceID, lease.ID,
		); err != nil {
			return err
		}
		if _, err := store.finalizeGitHubAppSetupForWorkspaceAdminInTx(
			ctx, tx, userID, workspaceID, expected.Member.ID, params, "", lease.ID, now,
		); err != nil {
			return err
		}
		return deleteGitHubSetupMutationByIDInTx(
			ctx, tx, workspaceID, lease.ID, expected.Member.ID,
			AppInstallationRequestStatusSetupPatching,
		)
	}); err != nil {
		return provider.GitHubAppConfigView{}, err
	}
	return githubConfigViewFromParams(params), nil
}

func (store *Store) AbortGitHubSetupVerificationForUser(ctx context.Context, userID string, workspaceID string, leaseID string) error {
	expected, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return err
	}
	return coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := installation.LockAppInstallationMutationScope(ctx, tx, registry.GitHubAppID); err != nil {
			return err
		}
		_, err := tx.Exec(ctx, `
DELETE FROM service_installation_requests
WHERE id = $1
  AND workspace_id = $2
  AND service_definition_id = $3
  AND service_kind = $4
  AND request_kind = $5
  AND requested_by_member_id = $6
  AND status = $7
`, strings.TrimSpace(leaseID), strings.TrimSpace(workspaceID), registry.GitHubAppID,
			serviceactor.KindApp, AppInstallationRequestKindInstall, expected.Member.ID,
			AppInstallationRequestStatusSetupVerifying)
		if err != nil {
			return fmt.Errorf("abort GitHub setup verification: %w", err)
		}
		return releaseGitHubAppSetupClaimByTokenInTx(ctx, tx, workspaceID, leaseID)
	})
}

func loadGitHubActiveConfigSnapshotForUpdate(ctx context.Context, queryer coredb.QueryRower, workspaceID string) (githubActiveConfigSnapshot, bool, error) {
	var snapshot githubActiveConfigSnapshot
	err := queryer.QueryRow(ctx, `
SELECT id, COALESCE(CAST(JSON_EXTRACT(COALESCE(config, JSON_OBJECT()), '$.githubAppId') AS SIGNED), 0), provider_key, updated_at
FROM service_provider_configs
WHERE workspace_id = $1
  AND provider_kind = 'github'
LIMIT 1
FOR UPDATE
`, strings.TrimSpace(workspaceID)).Scan(&snapshot.ID, &snapshot.AppID, &snapshot.AppSlug, &snapshot.UpdatedAt)
	if err != nil {
		if coredb.IsNotFound(err) {
			return githubActiveConfigSnapshot{}, false, nil
		}
		return githubActiveConfigSnapshot{}, false, fmt.Errorf("load active GitHub config snapshot: %w", err)
	}
	return snapshot, true, nil
}

func validateGitHubSetupVerificationConfigSnapshot(
	ctx context.Context,
	queryer coredb.QueryRower,
	workspaceID string,
	lease AppInstallationRequestView,
) error {
	base := appInstallationRequestStoredBaseConfig(lease)
	if len(base) != 2 && len(base) != 3 {
		return conflict("GitHub setup verification state is invalid. Restart setup.")
	}
	expectedID, idOK := base[githubVerificationExpectedConfigIDKey].(string)
	expectedUpdatedAt, timeOK := base[githubVerificationExpectedConfigUpdatedAtKey].(string)
	if !idOK || !timeOK {
		return conflict("GitHub setup verification state is invalid. Restart setup.")
	}
	if raw, exists := base[githubVerificationRequireInstallationKey]; exists {
		if _, ok := raw.(bool); !ok {
			return conflict("GitHub setup verification state is invalid. Restart setup.")
		}
	}
	expectedID = strings.TrimSpace(expectedID)
	expectedUpdatedAt = strings.TrimSpace(expectedUpdatedAt)
	active, found, err := loadGitHubActiveConfigSnapshotForUpdate(ctx, queryer, workspaceID)
	if err != nil {
		return err
	}
	if expectedID == "" {
		if expectedUpdatedAt != "" || found {
			return conflict("GitHub configuration changed. Restart setup.")
		}
		return nil
	}
	parsedUpdatedAt, err := time.Parse(time.RFC3339Nano, expectedUpdatedAt)
	if err != nil || !found || strings.TrimSpace(active.ID) != expectedID || !active.UpdatedAt.Equal(parsedUpdatedAt) {
		return conflict("GitHub configuration changed. Restart setup.")
	}
	return nil
}

func githubSetupVerificationRequiresActiveInstallation(lease AppInstallationRequestView) bool {
	required, _ := appInstallationRequestStoredBaseConfig(lease)[githubVerificationRequireInstallationKey].(bool)
	return required
}

func loadGitHubSetupVerificationForMutationForUpdate(ctx context.Context, queryer coredb.QueryRower, workspaceID string, leaseID string, memberID string) (AppInstallationRequestView, error) {
	query := appInstallationRequestSelectSQL() + `
WHERE req.workspace_id = $1
  AND req.id = $2
  AND req.service_definition_id = $3
  AND req.service_kind = $4
  AND req.request_kind = $5
  AND req.requested_by_member_id = $6
	  AND req.status IN ($7, $8)
FOR UPDATE`
	view, err := scanAppInstallationRequest(queryer.QueryRow(ctx, query,
		strings.TrimSpace(workspaceID), strings.TrimSpace(leaseID), registry.GitHubAppID,
		serviceactor.KindApp, AppInstallationRequestKindInstall, strings.TrimSpace(memberID),
		AppInstallationRequestStatusSetupVerifying, AppInstallationRequestStatusSetupPatching))
	if err != nil {
		if coredb.IsNotFound(err) {
			return AppInstallationRequestView{}, ErrNotFound
		}
		return AppInstallationRequestView{}, fmt.Errorf("load GitHub setup verification: %w", err)
	}
	return view, nil
}

func requireNoGitHubSetupVerificationInTx(ctx context.Context, queryer coredb.QueryRower, workspaceID string) error {
	var leaseID string
	err := queryer.QueryRow(ctx, `
SELECT id
FROM service_installation_requests
WHERE workspace_id = $1
  AND service_definition_id = $2
  AND service_kind = $3
  AND request_kind = $4
	  AND status = $5
ORDER BY created_at ASC, id ASC
LIMIT 1
FOR UPDATE
	`, strings.TrimSpace(workspaceID), registry.GitHubAppID, serviceactor.KindApp,
		AppInstallationRequestKindInstall,
		AppInstallationRequestStatusSetupPatching).Scan(&leaseID)
	if err != nil {
		if coredb.IsNotFound(err) {
			return nil
		}
		return fmt.Errorf("load GitHub setup verification lease: %w", err)
	}
	return conflict("GitHub setup provider mutation is already in progress for this workspace.")
}

func requireNoGitHubAdminSetupDraftInTx(ctx context.Context, queryer coredb.QueryRower, workspaceID string) error {
	var draftID string
	err := queryer.QueryRow(ctx, `
SELECT id
FROM service_installation_requests
WHERE workspace_id = $1
  AND service_definition_id = $2
  AND service_kind = $3
  AND request_kind = $4
  AND status = $5
  AND request_reason = $6
ORDER BY created_at ASC, id ASC
LIMIT 1
FOR UPDATE
`, strings.TrimSpace(workspaceID), registry.GitHubAppID, serviceactor.KindApp,
		AppInstallationRequestKindInstall, AppInstallationRequestStatusSetupReady,
		GitHubAppSetupModeAdmin).Scan(&draftID)
	if err != nil {
		if coredb.IsNotFound(err) {
			return nil
		}
		return fmt.Errorf("load GitHub administrator setup draft: %w", err)
	}
	return conflict("A workspace administrator is already preparing GitHub setup.")
}

func deleteExpiredGitHubSetupVerificationsInTx(ctx context.Context, tx coredb.Tx, workspaceID string, cutoff time.Time) error {
	if _, err := tx.Exec(ctx, `
DELETE claim
FROM service_provider_workspace_claims claim
JOIN service_installation_requests req
  ON req.id = claim.claim_token
 AND req.workspace_id = claim.workspace_id
WHERE claim.workspace_id = $1
  AND claim.claim_state = $2
  AND req.service_definition_id = $3
  AND req.service_kind = $4
  AND req.request_kind = $5
  AND req.status = $6
  AND req.created_at < $7
`, strings.TrimSpace(workspaceID), githubAppClaimStateSetup, registry.GitHubAppID,
		serviceactor.KindApp, AppInstallationRequestKindInstall,
		AppInstallationRequestStatusSetupVerifying, cutoff.UTC()); err != nil {
		return fmt.Errorf("release expired GitHub setup verification claim: %w", err)
	}
	if _, err := tx.Exec(ctx, `
DELETE FROM service_installation_requests
WHERE workspace_id = $1
  AND service_definition_id = $2
  AND service_kind = $3
  AND request_kind = $4
  AND status = $5
  AND created_at < $6
`, strings.TrimSpace(workspaceID), registry.GitHubAppID, serviceactor.KindApp,
		AppInstallationRequestKindInstall, AppInstallationRequestStatusSetupVerifying, cutoff.UTC()); err != nil {
		return fmt.Errorf("delete expired GitHub setup verification: %w", err)
	}
	return nil
}

func deleteGitHubSetupVerificationByIDInTx(ctx context.Context, tx coredb.Tx, workspaceID string, leaseID string, memberID string) error {
	result, err := tx.Exec(ctx, `
DELETE FROM service_installation_requests
WHERE id = $1
  AND workspace_id = $2
  AND service_definition_id = $3
  AND service_kind = $4
  AND request_kind = $5
  AND requested_by_member_id = $6
  AND status = $7
`, strings.TrimSpace(leaseID), strings.TrimSpace(workspaceID), registry.GitHubAppID,
		serviceactor.KindApp, AppInstallationRequestKindInstall, strings.TrimSpace(memberID),
		AppInstallationRequestStatusSetupVerifying)
	if err != nil {
		return fmt.Errorf("delete GitHub setup verification: %w", err)
	}
	if result.RowsAffected() != 1 {
		return conflict("GitHub setup verification changed. Restart setup.")
	}
	return releaseGitHubAppSetupClaimByTokenInTx(ctx, tx, workspaceID, leaseID)
}

func deleteGitHubSetupMutationByIDInTx(
	ctx context.Context,
	tx coredb.Tx,
	workspaceID string,
	leaseID string,
	memberID string,
	expectedStatus string,
) error {
	result, err := tx.Exec(ctx, `
DELETE FROM service_installation_requests
WHERE id = $1
  AND workspace_id = $2
  AND service_definition_id = $3
  AND service_kind = $4
  AND request_kind = $5
  AND requested_by_member_id = $6
  AND status = $7
`, strings.TrimSpace(leaseID), strings.TrimSpace(workspaceID), registry.GitHubAppID,
		serviceactor.KindApp, AppInstallationRequestKindInstall, strings.TrimSpace(memberID),
		strings.TrimSpace(expectedStatus))
	if err != nil {
		return fmt.Errorf("delete GitHub setup mutation: %w", err)
	}
	if result.RowsAffected() != 1 {
		return conflict("GitHub setup mutation changed. Restart setup.")
	}
	return nil
}

func githubConfigViewFromParams(params provider.UpsertGitHubAppConfigParams) provider.GitHubAppConfigView {
	return provider.GitHubAppConfigView{
		Registered: true,
		AppID:      params.AppID,
		AppName:    strings.TrimSpace(params.AppName),
		AppSlug:    strings.TrimSpace(params.AppSlug),
		HTMLURL:    strings.TrimSpace(params.HTMLURL),
		OwnerLogin: strings.TrimSpace(params.OwnerLogin),
	}
}