multica/server/internal/handler/github.go

package handler

import (
	"context"
	"crypto/hmac"
	"crypto/rand"
	"crypto/sha256"
	"encoding/hex"
	"encoding/json"
	"errors"
	"fmt"
	"io"
	"log/slog"
	"net/http"
	"net/url"
	"os"
	"regexp"
	"slices"
	"sort"
	"strconv"
	"strings"
	"time"

	"github.com/go-chi/chi/v5"
	"github.com/golang-jwt/jwt/v5"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgtype"
	"github.com/multica-ai/multica/server/internal/issuestatus"
	"github.com/multica-ai/multica/server/internal/middleware"
	db "github.com/multica-ai/multica/server/pkg/db/generated"
	"github.com/multica-ai/multica/server/pkg/protocol"
)

// githubAPIBase is the base URL for GitHub's REST API. Mutable so tests can
// point App-authenticated calls at an httptest server without touching GitHub.
var githubAPIBase = "https://api.github.com"

const (
	githubReturnToGitHub       = "github"
	githubReturnToRepositories = "repositories"
	githubAPIResponseLimit     = 4 << 20
)

// ── Response shapes ─────────────────────────────────────────────────────────

// GitHubInstallationResponse is the JSON shape returned by the installation
// list endpoint and broadcast on installation-related WS events.
//
// InstallationID is admin-only: the numeric GitHub installation_id is the
// management handle used by the Connect/Disconnect flows, so non-admin
// members receive responses with the field omitted. The list handler gates
// it by role; realtime broadcasts always omit it because the WS fanout has
// no per-recipient view (admins re-query the list endpoint on invalidation
// to recover the management handle).
type GitHubInstallationResponse struct {
	ID               string  `json:"id"`
	WorkspaceID      string  `json:"workspace_id"`
	InstallationID   *int64  `json:"installation_id,omitempty"`
	AccountLogin     string  `json:"account_login"`
	AccountType      string  `json:"account_type"`
	AccountAvatarURL *string `json:"account_avatar_url"`
	CreatedAt        string  `json:"created_at"`
}

type GitHubPullRequestResponse struct {
	ID string `json:"id"`
	// Provider is the Git provider this PR was mirrored from: "github", "forgejo",
	// "gitea", or "gitlab". The frontend uses it to pick the host icon and
	// label (e.g. GitLab "merge request").
	Provider        string  `json:"provider"`
	WorkspaceID     string  `json:"workspace_id"`
	RepoOwner       string  `json:"repo_owner"`
	RepoName        string  `json:"repo_name"`
	Number          int32   `json:"number"`
	Title           string  `json:"title"`
	State           string  `json:"state"`
	HtmlURL         string  `json:"html_url"`
	Branch          *string `json:"branch"`
	AuthorLogin     *string `json:"author_login"`
	AuthorAvatarURL *string `json:"author_avatar_url"`
	MergedAt        *string `json:"merged_at"`
	ClosedAt        *string `json:"closed_at"`
	PRCreatedAt     string  `json:"pr_created_at"`
	PRUpdatedAt     string  `json:"pr_updated_at"`
	// LinkSource explains why the PR is on the issue: "manual", "title",
	// "branch", or "auto" (an older rule). Only set on the issue PR list.
	LinkSource string `json:"link_source,omitempty"`
	// Mergeable state mirrors GitHub's REST `mergeable_state` field, retained
	// for compatibility. The card now reads the richer GraphQL fields below.
	MergeableState *string `json:"mergeable_state"`
	// ── GitHub API snapshot (MUL-5265, Plan C) ──────────────────────────────
	// These come from an authenticated GraphQL query, the single source of
	// truth. All are null / empty / 0 when no snapshot has landed (or the
	// GitHub App private key is unconfigured), so the card hides the CI / merge
	// region and degrades cleanly.
	//
	// Mergeable answers ONLY "is there a conflict": "mergeable" | "conflicting"
	// | "unknown" | null. A false "conflicting" must never be reported as
	// "not mergeable" — that verdict is MergeStateStatus's job.
	Mergeable *string `json:"mergeable"`
	// MergeStateStatus is GitHub's merge-state verdict, lowercased: "clean" |
	// "dirty" | "blocked" | "behind" | "unstable" | "draft" | "has_hooks" |
	// "unknown" | null. "Ready to merge" is derived ONLY from "clean".
	MergeStateStatus *string `json:"merge_state_status"`
	// SnapshotAvailable distinguishes a current API snapshot from both
	// "feature disabled / not fetched yet" and "a current snapshot whose
	// statusCheckRollup is null". Only the last case may render "no checks".
	// It is omitted for non-GitHub providers, which keep their webhook-derived
	// checks_conclusion compatibility path.
	SnapshotAvailable *bool `json:"snapshot_available,omitempty"`
	// ChecksRollup is GitHub's overall CI verdict, lowercased: "success" |
	// "failure" | "pending" | "error" | "expected" | null. null means
	// statusCheckRollup was null (no checks yet) and must NEVER render as
	// passed.
	ChecksRollup *string `json:"checks_rollup"`
	// ChecksConclusion is a coarse compat alias derived from the snapshot:
	// "passed" | "failed" | "pending" | null.
	ChecksConclusion *string `json:"checks_conclusion"`
	// Run-level counts for the PR's snapshot head. ChecksPending mirrors
	// ChecksRunning for older clients that still read the old key.
	ChecksTotal   int64 `json:"checks_total"`
	ChecksPassed  int64 `json:"checks_passed"`
	ChecksFailed  int64 `json:"checks_failed"`
	ChecksRunning int64 `json:"checks_running"`
	ChecksPending int64 `json:"checks_pending"`
	// FailedCheckNames names the failing checks so the card can point at them
	// (e.g. "✗ 2/7 · backend, e2e").
	FailedCheckNames []string `json:"failed_check_names"`
	// SnapshotStale is true when an open PR's last successful fetch is older
	// than the stale threshold (GitHub outage / revoked key): the card shows
	// last-known data greyed out rather than blank.
	SnapshotStale bool `json:"snapshot_stale"`
	// SnapshotFetchedAt is when the snapshot was last fetched (RFC3339), or null.
	SnapshotFetchedAt *string `json:"snapshot_fetched_at"`
	// Diff stats (lines added/removed and file count) sourced from the
	// `pull_request` webhook payload. Legacy rows that pre-date this
	// field default to 0; the frontend treats total == 0 as "unknown"
	// and hides the stats row.
	Additions    int32 `json:"additions"`
	Deletions    int32 `json:"deletions"`
	ChangedFiles int32 `json:"changed_files"`
}

type GitHubConnectResponse struct {
	URL        string `json:"url"`
	Configured bool   `json:"configured"`
}

type GitHubRepositoryResponse struct {
	ID            int64   `json:"id"`
	FullName      string  `json:"full_name"`
	HTMLURL       string  `json:"html_url"`
	CloneURL      string  `json:"clone_url"`
	Description   *string `json:"description"`
	Private       bool    `json:"private"`
	Archived      bool    `json:"archived"`
	DefaultBranch string  `json:"default_branch"`
}

type GitHubRepositoriesResponse struct {
	Repositories []GitHubRepositoryResponse `json:"repositories"`
	TotalCount   int64                      `json:"total_count"`
	NextPage     *int                       `json:"next_page"`
}

func githubInstallationToResponse(i db.GithubInstallation) GitHubInstallationResponse {
	instID := i.InstallationID
	return GitHubInstallationResponse{
		ID:               uuidToString(i.ID),
		WorkspaceID:      uuidToString(i.WorkspaceID),
		InstallationID:   &instID,
		AccountLogin:     i.AccountLogin,
		AccountType:      i.AccountType,
		AccountAvatarURL: textToPtr(i.AccountAvatarUrl),
		CreatedAt:        timestampToString(i.CreatedAt),
	}
}

// githubInstallationToBroadcast returns the same shape as the list endpoint's
// per-role response with the numeric `installation_id` stripped. Realtime
// events fan out to every WS client subscribed to the workspace, so the
// payload must match the weakest-role view — admin/owner clients re-query
// the list endpoint to recover the management handle. The frontend uses
// these events only to invalidate the installations query, so it does not
// read `installation_id` off the broadcast.
func githubInstallationToBroadcast(i db.GithubInstallation) GitHubInstallationResponse {
	resp := githubInstallationToResponse(i)
	resp.InstallationID = nil
	return resp
}

func githubPullRequestToResponse(p db.GithubPullRequest, snapshotEnabled bool) GitHubPullRequestResponse {
	snapshotAvailable := currentGitHubSnapshotAvailable(
		snapshotEnabled, p.HeadSha, p.SnapshotHeadSha, p.SnapshotFetchedAt,
	)
	return GitHubPullRequestResponse{
		ID:                uuidToString(p.ID),
		Provider:          "github",
		WorkspaceID:       uuidToString(p.WorkspaceID),
		RepoOwner:         p.RepoOwner,
		RepoName:          p.RepoName,
		Number:            p.PrNumber,
		Title:             p.Title,
		State:             p.State,
		HtmlURL:           p.HtmlUrl,
		Branch:            textToPtr(p.Branch),
		AuthorLogin:       textToPtr(p.AuthorLogin),
		AuthorAvatarURL:   textToPtr(p.AuthorAvatarUrl),
		MergedAt:          timestampToPtr(p.MergedAt),
		ClosedAt:          timestampToPtr(p.ClosedAt),
		PRCreatedAt:       timestampToString(p.PrCreatedAt),
		PRUpdatedAt:       timestampToString(p.PrUpdatedAt),
		MergeableState:    textToPtr(p.MergeableState),
		SnapshotAvailable: &snapshotAvailable,
		// A bare PR row has no aggregated check counts — webhook broadcasts of a
		// single PR fall through here and the frontend re-queries the list for
		// the full snapshot (mergeable / rollup / counts).
		ChecksConclusion: nil,
		FailedCheckNames: []string{},
		Additions:        p.Additions,
		Deletions:        p.Deletions,
		ChangedFiles:     p.ChangedFiles,
	}
}

// prSnapshotStaleThreshold is how old an open PR's last successful fetch may be
// before the card greys it out as stale. Healthy pipelines refresh open PRs at
// least every sweep interval (~10m), so crossing 30m means refreshes are not
// landing (GitHub outage, revoked key) and the shown data is last-known.
const prSnapshotStaleThreshold = 30 * time.Minute

func issuePullRequestRowToResponse(p db.ListPullRequestsByIssueRow, snapshotEnabled bool) GitHubPullRequestResponse {
	snapshotAvailable := currentGitHubSnapshotAvailable(
		snapshotEnabled, p.HeadSha, p.SnapshotHeadSha, p.SnapshotFetchedAt,
	)
	stale := false
	if snapshotAvailable && (p.State == "open" || p.State == "draft") {
		stale = time.Since(p.SnapshotFetchedAt.Time) > prSnapshotStaleThreshold
	}
	failedNames := p.FailedCheckNames
	if failedNames == nil {
		failedNames = []string{}
	}
	resp := GitHubPullRequestResponse{
		ID:                uuidToString(p.ID),
		Provider:          "github",
		WorkspaceID:       uuidToString(p.WorkspaceID),
		RepoOwner:         p.RepoOwner,
		RepoName:          p.RepoName,
		Number:            p.PrNumber,
		Title:             p.Title,
		State:             p.State,
		HtmlURL:           p.HtmlUrl,
		Branch:            textToPtr(p.Branch),
		AuthorLogin:       textToPtr(p.AuthorLogin),
		AuthorAvatarURL:   textToPtr(p.AuthorAvatarUrl),
		MergedAt:          timestampToPtr(p.MergedAt),
		ClosedAt:          timestampToPtr(p.ClosedAt),
		PRCreatedAt:       timestampToString(p.PrCreatedAt),
		PRUpdatedAt:       timestampToString(p.PrUpdatedAt),
		MergeableState:    textToPtr(p.MergeableState),
		SnapshotAvailable: &snapshotAvailable,
		FailedCheckNames:  []string{},
		SnapshotStale:     stale,
		Additions:         p.Additions,
		Deletions:         p.Deletions,
		ChangedFiles:      p.ChangedFiles,
	}
	if snapshotAvailable {
		resp.Mergeable = lowerTextPtr(p.ApiMergeable)
		resp.MergeStateStatus = lowerTextPtr(p.ApiMergeStateStatus)
		resp.ChecksRollup = lowerTextPtr(p.ChecksRollupState)
		resp.ChecksConclusion = rollupToConclusion(p.ChecksRollupState, p.ChecksFailed, p.ChecksRunning, p.ChecksPassed)
		resp.ChecksTotal = p.ChecksTotal
		resp.ChecksPassed = p.ChecksPassed
		resp.ChecksFailed = p.ChecksFailed
		resp.ChecksRunning = p.ChecksRunning
		resp.ChecksPending = p.ChecksRunning
		resp.FailedCheckNames = failedNames
		resp.SnapshotFetchedAt = timestampToPtr(p.SnapshotFetchedAt)
	}
	return resp
}

func currentGitHubSnapshotAvailable(
	enabled bool,
	headSHA string,
	snapshotHeadSHA string,
	fetchedAt pgtype.Timestamptz,
) bool {
	return enabled && fetchedAt.Valid && snapshotHeadSHA != "" && snapshotHeadSHA == headSHA
}

// aggregateChecksConclusion collapses per-PR commit-status counts into a
// single coarse status. Still used by the self-hosted VCS provider path
// (Forgejo / Gitea / GitLab), which mirrors commit statuses via webhook rather
// than fetching a GitHub-style API snapshot:
//   - any failed status wins ("failed");
//   - any not-yet-completed status makes the PR "pending";
//   - all completed and passed is "passed";
//   - no observed status at all is nil (rendered as "no checks" / hidden).
func aggregateChecksConclusion(failed, passed, pending, total int64) *string {
	if total == 0 {
		return nil
	}
	var v string
	switch {
	case failed > 0:
		v = "failed"
	case pending > 0:
		v = "pending"
	case passed > 0:
		v = "passed"
	default:
		return nil
	}
	return &v
}

// lowerTextPtr returns a lowercased *string for a non-empty pgtype.Text, else
// nil. Used to expose GraphQL enums (MERGEABLE / CLEAN / SUCCESS …) to the API
// in the project's lowercase convention.
func lowerTextPtr(t pgtype.Text) *string {
	if !t.Valid || t.String == "" {
		return nil
	}
	v := strings.ToLower(t.String)
	return &v
}

// rollupToConclusion derives the coarse compat "checks_conclusion" from the
// GraphQL rollup, falling back to the run counts when the rollup enum is
// unfamiliar. A null/empty rollup means "no checks yet" → nil (never "passed").
func rollupToConclusion(rollup pgtype.Text, failed, running, passed int64) *string {
	if !rollup.Valid || rollup.String == "" {
		return nil
	}
	var v string
	switch strings.ToUpper(rollup.String) {
	case "FAILURE", "ERROR":
		v = "failed"
	case "PENDING", "EXPECTED":
		v = "pending"
	case "SUCCESS":
		v = "passed"
	default:
		switch {
		case failed > 0:
			v = "failed"
		case running > 0:
			v = "pending"
		case passed > 0:
			v = "passed"
		default:
			return nil
		}
	}
	return &v
}

// ── Connect / state token ───────────────────────────────────────────────────

// githubAppSlug returns the GitHub App slug used to build the install URL.
// Empty when the integration is not configured for this deployment.
func githubAppSlug() string { return strings.TrimSpace(os.Getenv("GITHUB_APP_SLUG")) }

// githubWebhookSecret is shared by webhook verification and state-token signing.
// We reuse the webhook secret as the state HMAC key so operators only need to
// configure one value.
func githubWebhookSecret() string { return strings.TrimSpace(os.Getenv("GITHUB_WEBHOOK_SECRET")) }

// isGitHubConfigured returns true only when BOTH the install slug and the
// webhook secret are set. The Connect button uses this single flag, so the
// frontend never offers a flow that the backend would reject.
func isGitHubConfigured() bool { return githubAppSlug() != "" && githubWebhookSecret() != "" }

// isGitHubRepositoryBrowseConfigured is deliberately separate from the
// install-flow flag. The App slug + webhook secret are enough to connect an
// installation, but browsing its repositories also requires App JWT
// credentials so the server can mint a short-lived installation token.
func isGitHubRepositoryBrowseConfigured() bool {
	return strings.TrimSpace(os.Getenv("GITHUB_APP_ID")) != "" &&
		strings.TrimSpace(os.Getenv("GITHUB_APP_PRIVATE_KEY")) != ""
}

// signState produces an opaque token that binds a workspace ID to the
// install flow so the setup callback can recover the workspace without
// trusting query params alone. Format: "<workspaceID>.<nonce>.<sigHex>".
func signState(workspaceID string) (string, error) {
	return signStateForReturn(workspaceID, githubReturnToGitHub)
}

func signStateForReturn(workspaceID, returnTo string) (string, error) {
	secret := githubWebhookSecret()
	if secret == "" {
		return "", errors.New("github integration is not configured")
	}
	if !isAllowedGitHubReturnTo(returnTo) {
		return "", errors.New("invalid github return target")
	}
	nonceBytes := make([]byte, 12)
	if _, err := rand.Read(nonceBytes); err != nil {
		return "", err
	}
	nonce := hex.EncodeToString(nonceBytes)
	payload := workspaceID + "." + nonce
	if returnTo != githubReturnToGitHub {
		payload = workspaceID + "." + returnTo + "." + nonce
	}
	mac := hmac.New(sha256.New, []byte(secret))
	mac.Write([]byte(payload))
	sig := hex.EncodeToString(mac.Sum(nil))
	return payload + "." + sig, nil
}

func verifyState(token string) (string, bool) {
	workspaceID, _, ok := verifyStateWithReturn(token)
	return workspaceID, ok
}

func verifyStateWithReturn(token string) (workspaceID, returnTo string, ok bool) {
	secret := githubWebhookSecret()
	if secret == "" {
		return "", "", false
	}
	parts := strings.Split(token, ".")
	if len(parts) != 3 && len(parts) != 4 {
		return "", "", false
	}
	workspaceID = parts[0]
	returnTo = githubReturnToGitHub
	nonceIndex := 1
	if len(parts) == 4 {
		returnTo = parts[1]
		nonceIndex = 2
		if !isAllowedGitHubReturnTo(returnTo) {
			return "", "", false
		}
	}
	sig := parts[nonceIndex+1]
	payload := strings.Join(parts[:nonceIndex+1], ".")
	mac := hmac.New(sha256.New, []byte(secret))
	mac.Write([]byte(payload))
	expected := hex.EncodeToString(mac.Sum(nil))
	if !hmac.Equal([]byte(expected), []byte(sig)) {
		return "", "", false
	}
	return workspaceID, returnTo, true
}

func isAllowedGitHubReturnTo(returnTo string) bool {
	return returnTo == githubReturnToGitHub || returnTo == githubReturnToRepositories
}

func githubSettingsURL(frontend, returnTo string) string {
	if !isAllowedGitHubReturnTo(returnTo) {
		returnTo = githubReturnToGitHub
	}
	return strings.TrimRight(frontend, "/") + "/settings?tab=" + url.QueryEscape(returnTo)
}

// GitHubConnect (GET /api/workspaces/{id}/github/connect) returns the URL the
// browser should open to install the Multica GitHub App against the caller's
// repos. The state token binds the resulting setup callback to this workspace.
func (h *Handler) GitHubConnect(w http.ResponseWriter, r *http.Request) {
	workspaceID := chi.URLParam(r, "id")
	if _, ok := parseUUIDOrBadRequest(w, workspaceID, "workspace id"); !ok {
		return
	}
	if !isGitHubConfigured() {
		writeJSON(w, http.StatusOK, GitHubConnectResponse{Configured: false})
		return
	}
	returnTo := strings.TrimSpace(r.URL.Query().Get("return_to"))
	if returnTo == "" {
		returnTo = githubReturnToGitHub
	}
	if !isAllowedGitHubReturnTo(returnTo) {
		writeError(w, http.StatusBadRequest, "invalid return target")
		return
	}
	slug := githubAppSlug()
	state, err := signStateForReturn(workspaceID, returnTo)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "failed to sign state")
		return
	}
	installURL := fmt.Sprintf(
		"https://github.com/apps/%s/installations/new?state=%s",
		url.PathEscape(slug),
		url.QueryEscape(state),
	)
	writeJSON(w, http.StatusOK, GitHubConnectResponse{URL: installURL, Configured: true})
}

// GitHubSetupCallback (GET /api/github/setup) handles the redirect GitHub
// sends after a user installs (or re-authorizes) the App. We expect
// ?installation_id=<id>&state=<signed token>. We persist the installation
// row (workspace ↔ installation_id mapping), then bounce the user back to
// the new Settings → GitHub tab in the web app (RFC MUL-2414 §4.1). The
// previous destination was the catch-all Settings page, which after the
// GitHub-tab split would land users on the default profile tab instead of
// the place that shows the connection they just completed.
func (h *Handler) GitHubSetupCallback(w http.ResponseWriter, r *http.Request) {
	q := r.URL.Query()
	installationIDStr := q.Get("installation_id")
	state := q.Get("state")
	frontend := strings.TrimSpace(os.Getenv("FRONTEND_ORIGIN"))
	if frontend == "" {
		frontend = "http://localhost:3000"
	}
	settingsURL := githubSettingsURL(frontend, githubReturnToGitHub)

	if state == "" {
		http.Redirect(w, r, settingsURL+"&github_error=missing_params", http.StatusFound)
		return
	}
	workspaceID, returnTo, ok := verifyStateWithReturn(state)
	if !ok {
		http.Redirect(w, r, settingsURL+"&github_error=invalid_state", http.StatusFound)
		return
	}
	settingsURL = githubSettingsURL(frontend, returnTo)
	if installationIDStr == "" {
		http.Redirect(w, r, settingsURL+"&github_error=missing_params", http.StatusFound)
		return
	}
	installationID, err := strconv.ParseInt(installationIDStr, 10, 64)
	if err != nil {
		http.Redirect(w, r, settingsURL+"&github_error=bad_installation_id", http.StatusFound)
		return
	}
	wsUUID, err := parseStrictUUID(workspaceID)
	if err != nil {
		http.Redirect(w, r, settingsURL+"&github_error=bad_workspace", http.StatusFound)
		return
	}
	// Resolve the installation against GitHub's API to capture display info.
	// If the App auth is not configured we still create the row with the
	// minimum we know; webhook events will refresh it as soon as one fires.
	login, accountType, avatar := fetchInstallationAccount(r.Context(), installationID)

	// Best-effort capture of the connecting user (may be nil if the public
	// callback was hit without a session — e.g. user wasn't logged in to
	// Multica when they finished the GitHub install). Either way we save
	// the row so the workspace owner sees the connection on next reload.
	connectedBy := pgtype.UUID{}
	if userID := requestUserID(r); userID != "" {
		if u, err := parseStrictUUID(userID); err == nil {
			connectedBy = u
		}
	}

	inst, err := h.Queries.CreateGitHubInstallation(r.Context(), db.CreateGitHubInstallationParams{
		WorkspaceID:      wsUUID,
		InstallationID:   installationID,
		AccountLogin:     login,
		AccountType:      accountType,
		AccountAvatarUrl: ptrToText(avatar),
		ConnectedByID:    connectedBy,
	})
	if err != nil {
		slog.Error("github: failed to persist installation", "err", err, "installation_id", installationID)
		http.Redirect(w, r, settingsURL+"&github_error=persist_failed", http.StatusFound)
		return
	}
	inst, err = h.consumePendingGitHubInstallation(r.Context(), inst)
	if err != nil {
		slog.Error("github: failed to apply pending installation metadata", "err", err, "installation_id", installationID)
		http.Redirect(w, r, settingsURL+"&github_error=persist_failed", http.StatusFound)
		return
	}
	h.publish(protocol.EventGitHubInstallationCreated, workspaceID, "system", "", map[string]any{
		"installation": githubInstallationToBroadcast(inst),
	})
	http.Redirect(w, r, settingsURL+"&github_connected=1", http.StatusFound)
}

func (h *Handler) consumePendingGitHubInstallation(ctx context.Context, inst db.GithubInstallation) (db.GithubInstallation, error) {
	pending, err := h.Queries.GetPendingGitHubInstallation(ctx, inst.InstallationID)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return inst, nil
		}
		return inst, err
	}
	refreshed, err := h.Queries.CreateGitHubInstallation(ctx, db.CreateGitHubInstallationParams{
		WorkspaceID:      inst.WorkspaceID,
		InstallationID:   inst.InstallationID,
		AccountLogin:     pending.AccountLogin,
		AccountType:      coalesce(pending.AccountType, "User"),
		AccountAvatarUrl: pending.AccountAvatarUrl,
		ConnectedByID:    inst.ConnectedByID,
	})
	if err != nil {
		return inst, err
	}
	if err := h.Queries.DeletePendingGitHubInstallation(ctx, inst.InstallationID); err != nil {
		return inst, err
	}
	return refreshed, nil
}

// fetchInstallationAccount tries to enrich the installation row with the
// account name + avatar from GitHub.
//
// GitHub's `GET /app/installations/{id}` endpoint requires GitHub App
// authentication (a JWT signed with the App's RSA private key). When the
// operator has configured GITHUB_APP_ID and GITHUB_APP_PRIVATE_KEY, we
// sign a short-lived JWT and use it; on any failure (env not set, key
// malformed, GitHub returns non-200) we fall back to the "unknown"
// placeholder. The next `installation` webhook delivery from GitHub will
// upsert the row with the real account info — see handleInstallationEvent.
//
// The HTTP call is synchronous (no independent timeout — that's a pre-
// existing wart of the install path), but we deliberately do NOT let a
// failure abort the setup callback: a network blip here just leaves the
// "unknown" placeholder in place, and the frontend re-queries on the
// realtime broadcast emitted by the webhook handler, so the UI converges
// without a manual refresh.
func fetchInstallationAccount(ctx context.Context, installationID int64) (login, accountType string, avatar *string) {
	login = "unknown"
	accountType = "User"
	avatar = nil
	endpoint := fmt.Sprintf("%s/app/installations/%d", strings.TrimRight(githubAPIBase, "/"), installationID)
	req, err := http.NewRequestWithContext(ctx, http.MethodGet, endpoint, nil)
	if err != nil {
		return
	}
	req.Header.Set("Accept", "application/vnd.github+json")
	if token, err := signGitHubAppJWT(time.Now()); err != nil {
		// Misconfigured private key is operator-actionable — log so the
		// install path doesn't silently fall back to "unknown" forever
		// without leaving a breadcrumb.
		slog.Warn("github: sign App JWT failed", "err", err)
	} else if token != "" {
		req.Header.Set("Authorization", "Bearer "+token)
	}
	resp, err := http.DefaultClient.Do(req)
	if err != nil {
		return
	}
	defer resp.Body.Close()
	if resp.StatusCode != http.StatusOK {
		return
	}
	var body struct {
		Account struct {
			Login     string `json:"login"`
			Type      string `json:"type"`
			AvatarURL string `json:"avatar_url"`
		} `json:"account"`
	}
	if err := json.NewDecoder(resp.Body).Decode(&body); err != nil {
		return
	}
	if body.Account.Login != "" {
		login = body.Account.Login
	}
	if body.Account.Type != "" {
		accountType = body.Account.Type
	}
	if body.Account.AvatarURL != "" {
		v := body.Account.AvatarURL
		avatar = &v
	}
	return
}

// signGitHubAppJWT mints the short-lived RS256 JWT GitHub requires for
// App-authenticated REST calls (see fetchInstallationAccount). Returns
// ("", nil) when the operator hasn't configured the App identity — that's
// a soft "App auth not available" signal, not an error, so callers can
// fall through to their unauthenticated path. A malformed
// GITHUB_APP_PRIVATE_KEY surfaces as an error so the operator notices.
//
// `now` is injected for deterministic tests; production callers pass
// time.Now().
func signGitHubAppJWT(now time.Time) (string, error) {
	appID := strings.TrimSpace(os.Getenv("GITHUB_APP_ID"))
	pemKey := strings.TrimSpace(os.Getenv("GITHUB_APP_PRIVATE_KEY"))
	if appID == "" || pemKey == "" {
		return "", nil
	}
	key, err := jwt.ParseRSAPrivateKeyFromPEM([]byte(pemKey))
	if err != nil {
		return "", fmt.Errorf("parse GITHUB_APP_PRIVATE_KEY: %w", err)
	}
	// GitHub allows JWTs valid for up to 10 minutes. We back-date `iat`
	// by 60 seconds to absorb modest clock skew between us and GitHub
	// (otherwise an "iat in the future" verdict from GitHub fails the
	// request) and cap `exp` at 9 minutes ahead to stay inside the cap
	// even with the same skew applied.
	claims := jwt.MapClaims{
		"iat": now.Add(-60 * time.Second).Unix(),
		"exp": now.Add(9 * time.Minute).Unix(),
		"iss": appID,
	}
	token := jwt.NewWithClaims(jwt.SigningMethodRS256, claims)
	signed, err := token.SignedString(key)
	if err != nil {
		return "", fmt.Errorf("sign App JWT: %w", err)
	}
	return signed, nil
}

// ── Listing / disconnect ────────────────────────────────────────────────────

// ListGitHubInstallations returns the workspace's connected GitHub
// installations to any workspace member. Connect/disconnect remain
// admin-only at the router level, so the response carries a `can_manage`
// hint and strips the numeric `installation_id` for non-admin callers —
// they get visibility into "is GitHub wired up, and by whom?" without the
// management handle.
func (h *Handler) ListGitHubInstallations(w http.ResponseWriter, r *http.Request) {
	workspaceID := chi.URLParam(r, "id")
	wsUUID, ok := parseUUIDOrBadRequest(w, workspaceID, "workspace id")
	if !ok {
		return
	}
	member, _ := middleware.MemberFromContext(r.Context())
	canManage := roleAllowed(member.Role, "owner", "admin")

	rows, err := h.Queries.ListGitHubInstallationsByWorkspace(r.Context(), wsUUID)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "failed to list installations")
		return
	}
	out := make([]GitHubInstallationResponse, 0, len(rows))
	for _, row := range rows {
		resp := githubInstallationToResponse(row)
		if !canManage {
			resp.InstallationID = nil
		}
		out = append(out, resp)
	}
	writeJSON(w, http.StatusOK, map[string]any{
		"installations":                out,
		"configured":                   isGitHubConfigured(),
		"repository_browse_configured": isGitHubRepositoryBrowseConfigured(),
		"can_manage":                   canManage,
	})
}

// ListGitHubInstallationRepositories returns the repositories accessible to a
// workspace-bound GitHub App installation. The route is admin-only because
// private repository names are sensitive. The path takes our installation row
// UUID (not GitHub's numeric installation id), and the workspace ownership
// check happens before any GitHub API call.
func (h *Handler) ListGitHubInstallationRepositories(w http.ResponseWriter, r *http.Request) {
	workspaceID := chi.URLParam(r, "id")
	if _, ok := parseUUIDOrBadRequest(w, workspaceID, "workspace id"); !ok {
		return
	}
	installationRowID := chi.URLParam(r, "installationId")
	rowUUID, ok := parseUUIDOrBadRequest(w, installationRowID, "installation id")
	if !ok {
		return
	}
	row, err := h.Queries.GetGitHubInstallationByID(r.Context(), rowUUID)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			writeError(w, http.StatusNotFound, "github installation not found")
			return
		}
		writeError(w, http.StatusInternalServerError, "failed to load github installation")
		return
	}
	if uuidToString(row.WorkspaceID) != workspaceID {
		writeError(w, http.StatusNotFound, "github installation not found")
		return
	}
	if !isGitHubRepositoryBrowseConfigured() {
		writeFeatureDisabled(w, "github_repository_browsing_not_configured", "github repository browsing is not configured")
		return
	}
	page, ok := parseGitHubPageParam(w, r, "page", 1, 1, 100000)
	if !ok {
		return
	}
	perPage, ok := parseGitHubPageParam(w, r, "per_page", 100, 1, 100)
	if !ok {
		return
	}

	repositories, err := fetchGitHubInstallationRepositories(
		r.Context(),
		row.InstallationID,
		page,
		perPage,
	)
	if err != nil {
		slog.Warn("github: list installation repositories failed", "err", err)
		writeError(w, http.StatusBadGateway, "failed to list github repositories")
		return
	}
	writeJSON(w, http.StatusOK, repositories)
}

func parseGitHubPageParam(
	w http.ResponseWriter,
	r *http.Request,
	name string,
	defaultValue, minValue, maxValue int,
) (int, bool) {
	raw := strings.TrimSpace(r.URL.Query().Get(name))
	if raw == "" {
		return defaultValue, true
	}
	value, err := strconv.Atoi(raw)
	if err != nil || value < minValue || value > maxValue {
		writeError(w, http.StatusBadRequest, "invalid "+name)
		return 0, false
	}
	return value, true
}

func fetchGitHubInstallationRepositories(
	ctx context.Context,
	installationID int64,
	page, perPage int,
) (GitHubRepositoriesResponse, error) {
	appJWT, err := signGitHubAppJWT(time.Now())
	if err != nil {
		return GitHubRepositoriesResponse{}, err
	}
	if appJWT == "" {
		return GitHubRepositoriesResponse{}, errors.New("github App JWT credentials unavailable")
	}

	client := &http.Client{Timeout: 15 * time.Second}
	tokenEndpoint := fmt.Sprintf(
		"%s/app/installations/%d/access_tokens",
		strings.TrimRight(githubAPIBase, "/"),
		installationID,
	)
	tokenReq, err := http.NewRequestWithContext(
		ctx,
		http.MethodPost,
		tokenEndpoint,
		strings.NewReader(`{"permissions":{"metadata":"read"}}`),
	)
	if err != nil {
		return GitHubRepositoriesResponse{}, err
	}
	setGitHubAPIHeaders(tokenReq, appJWT)
	tokenReq.Header.Set("Content-Type", "application/json")
	tokenResp, err := client.Do(tokenReq)
	if err != nil {
		return GitHubRepositoriesResponse{}, fmt.Errorf("create installation token: %w", err)
	}
	defer tokenResp.Body.Close()
	if tokenResp.StatusCode != http.StatusCreated {
		_, _ = io.Copy(io.Discard, io.LimitReader(tokenResp.Body, githubAPIResponseLimit))
		return GitHubRepositoriesResponse{}, fmt.Errorf("create installation token: github status %d", tokenResp.StatusCode)
	}
	var tokenBody struct {
		Token string `json:"token"`
	}
	if err := json.NewDecoder(io.LimitReader(tokenResp.Body, githubAPIResponseLimit)).Decode(&tokenBody); err != nil {
		return GitHubRepositoriesResponse{}, fmt.Errorf("decode installation token: %w", err)
	}
	if tokenBody.Token == "" {
		return GitHubRepositoriesResponse{}, errors.New("github returned an empty installation token")
	}
	defer revokeGitHubInstallationToken(client, tokenBody.Token)

	repositoriesEndpoint := fmt.Sprintf(
		"%s/installation/repositories?page=%d&per_page=%d",
		strings.TrimRight(githubAPIBase, "/"),
		page,
		perPage,
	)
	repositoriesReq, err := http.NewRequestWithContext(ctx, http.MethodGet, repositoriesEndpoint, nil)
	if err != nil {
		return GitHubRepositoriesResponse{}, err
	}
	setGitHubAPIHeaders(repositoriesReq, tokenBody.Token)
	repositoriesResp, err := client.Do(repositoriesReq)
	if err != nil {
		return GitHubRepositoriesResponse{}, fmt.Errorf("list installation repositories: %w", err)
	}
	defer repositoriesResp.Body.Close()
	if repositoriesResp.StatusCode != http.StatusOK {
		_, _ = io.Copy(io.Discard, io.LimitReader(repositoriesResp.Body, githubAPIResponseLimit))
		return GitHubRepositoriesResponse{}, fmt.Errorf("list installation repositories: github status %d", repositoriesResp.StatusCode)
	}
	var body struct {
		TotalCount   int64 `json:"total_count"`
		Repositories []struct {
			ID            int64   `json:"id"`
			FullName      string  `json:"full_name"`
			HTMLURL       string  `json:"html_url"`
			CloneURL      string  `json:"clone_url"`
			Description   *string `json:"description"`
			Private       bool    `json:"private"`
			Archived      bool    `json:"archived"`
			DefaultBranch string  `json:"default_branch"`
		} `json:"repositories"`
	}
	if err := json.NewDecoder(io.LimitReader(repositoriesResp.Body, githubAPIResponseLimit)).Decode(&body); err != nil {
		return GitHubRepositoriesResponse{}, fmt.Errorf("decode installation repositories: %w", err)
	}
	out := GitHubRepositoriesResponse{
		Repositories: make([]GitHubRepositoryResponse, 0, len(body.Repositories)),
		TotalCount:   body.TotalCount,
	}
	for _, repository := range body.Repositories {
		out.Repositories = append(out.Repositories, GitHubRepositoryResponse(repository))
	}
	if int64(page*perPage) < body.TotalCount {
		nextPage := page + 1
		out.NextPage = &nextPage
	}
	return out, nil
}

func setGitHubAPIHeaders(req *http.Request, token string) {
	req.Header.Set("Accept", "application/vnd.github+json")
	req.Header.Set("Authorization", "Bearer "+token)
	req.Header.Set("X-GitHub-Api-Version", "2022-11-28")
}

func revokeGitHubInstallationToken(client *http.Client, token string) {
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()
	endpoint := strings.TrimRight(githubAPIBase, "/") + "/installation/token"
	req, err := http.NewRequestWithContext(ctx, http.MethodDelete, endpoint, nil)
	if err != nil {
		return
	}
	setGitHubAPIHeaders(req, token)
	resp, err := client.Do(req)
	if err != nil {
		return
	}
	defer resp.Body.Close()
	_, _ = io.Copy(io.Discard, io.LimitReader(resp.Body, githubAPIResponseLimit))
}

func (h *Handler) DeleteGitHubInstallation(w http.ResponseWriter, r *http.Request) {
	workspaceID := chi.URLParam(r, "id")
	wsUUID, ok := parseUUIDOrBadRequest(w, workspaceID, "workspace id")
	if !ok {
		return
	}
	id := chi.URLParam(r, "installationId")
	idUUID, ok := parseUUIDOrBadRequest(w, id, "installation id")
	if !ok {
		return
	}
	if err := h.Queries.DeleteGitHubInstallation(r.Context(), db.DeleteGitHubInstallationParams{
		ID:          idUUID,
		WorkspaceID: wsUUID,
	}); err != nil {
		writeError(w, http.StatusInternalServerError, "failed to remove installation")
		return
	}
	h.publish(protocol.EventGitHubInstallationDeleted, workspaceID, "system", "", map[string]any{
		"id": id,
	})
	w.WriteHeader(http.StatusNoContent)
}

// ── List PRs for an issue ───────────────────────────────────────────────────

func (h *Handler) ListPullRequestsForIssue(w http.ResponseWriter, r *http.Request) {
	issue, ok := h.loadIssueForUser(w, r, chi.URLParam(r, "id"))
	if !ok {
		return
	}
	ws, err := h.Queries.GetWorkspace(r.Context(), issue.WorkspaceID)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "failed to list pull requests")
		return
	}
	identifier := fmt.Sprintf("%s-%d", issuePrefixForWorkspace(ws), issue.Number)
	rows, err := h.Queries.ListPullRequestsByIssue(r.Context(), issue.ID)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "failed to list pull requests")
		return
	}
	out := make([]GitHubPullRequestResponse, 0, len(rows))
	for _, row := range rows {
		resp := issuePullRequestRowToResponse(row, h.PRRefresh.Enabled())
		resp.LinkSource = prLinkSource(row.LinkedByType, identifier, row.Title, row.Branch.String)
		out = append(out, resp)
		// Page-visit trigger (MUL-5265): if this card's snapshot is missing or
		// older than the view TTL, kick an async refresh. Non-blocking — the
		// current (possibly stale) response is returned immediately and the
		// fresh snapshot arrives via the pull_request:updated realtime event.
		h.PRRefresh.MaybeEnqueueOnView(
			row.InstallationID, row.RepoOwner, row.RepoName, row.PrNumber,
			row.SnapshotFetchedAt.Time,
			row.SnapshotFetchedAt.Valid &&
				row.SnapshotHeadSha != "" &&
				row.SnapshotHeadSha == row.HeadSha,
		)
	}
	// PRs from token-based providers (Forgejo / Gitea / GitLab) share the same
	// card list. They live in their own provider-tagged tables, so they merge
	// in here mapped to the same response shape; the combined list is re-sorted
	// newest-first.
	vcsRows, err := h.Queries.ListVCSPullRequestsByIssue(r.Context(), issue.ID)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "failed to list pull requests")
		return
	}
	for _, row := range vcsRows {
		resp := vcsPullRequestRowToResponse(row)
		resp.LinkSource = prLinkSource(row.LinkedByType, identifier, row.Title, row.Branch.String)
		out = append(out, resp)
	}
	sort.SliceStable(out, func(i, j int) bool {
		return out[i].PRCreatedAt > out[j].PRCreatedAt
	})
	// The one auto-complete decision the issue page renders, so the UI never
	// re-derives the rule on its own.
	decision, err := h.decidePRAutoComplete(r.Context(), ws, issue, nil)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "failed to list pull requests")
		return
	}
	writeJSON(w, http.StatusOK, map[string]any{
		"pull_requests": out,
		"auto_complete": prAutoCompleteToResponse(decision),
	})
}

// broadcastPRSnapshotApplied is the ghsnapshot pipeline's onApplied callback:
// once an API snapshot is written to a PR row, re-broadcast the PR so every
// open issue detail page re-queries its PR list and picks up the fresh CI /
// mergeability state. Runs on a background pipeline goroutine.
func (h *Handler) broadcastPRSnapshotApplied(ctx context.Context, prID pgtype.UUID) {
	pr, err := h.Queries.GetGitHubPullRequestByID(ctx, prID)
	if err != nil {
		return
	}
	issueIDs, err := h.Queries.ListIssueIDsForPullRequest(ctx, prID)
	if err != nil {
		return
	}
	linked := make([]string, 0, len(issueIDs))
	for _, id := range issueIDs {
		linked = append(linked, uuidToString(id))
	}
	h.publish(protocol.EventPullRequestUpdated, uuidToString(pr.WorkspaceID), "system", "", map[string]any{
		"pull_request":     githubPullRequestToResponse(pr, h.PRRefresh.Enabled()),
		"linked_issue_ids": linked,
	})
}

// ── Webhook ─────────────────────────────────────────────────────────────────

// identifierRe extracts identifiers like "MUL-1510" from text. Case-insensitive
// because branch names are conventionally lowercase but issue prefixes are
// uppercase. Word boundary on the left prevents matching inside email-style
// strings (e.g. "abc@MUL-1") and the digit anchor on the right rules out
// version numbers like "v1.2-3".
var identifierRe = regexp.MustCompile(`(?i)\b([a-z][a-z0-9]{0,9})-(\d+)\b`)

// closingIdentifierRe extracts identifiers that appear immediately after a
// GitHub-style closing keyword ("close[sd]?", "fix(e[sd])?", "resolve[sd]?"),
// optionally separated by a colon and whitespace. Matching is intentionally
// strict on adjacency — "Fix MUL-1" names MUL-1, but "Fix login MUL-1" does
// not. It is the one way a PR body links an issue; a bare body mention links
// nothing. Since MUL-7726 the keyword only links: what a merge does is the
// workspace's choice, not the PR text's.
var closingIdentifierRe = regexp.MustCompile(
	`(?i)\b(?:close[sd]?|fix(?:e[sd])?|resolve[sd]?)[:\s]+([a-z][a-z0-9]{0,9})-(\d+)\b`,
)

// HandleGitHubWebhook (POST /api/webhooks/github) is GitHub's destination for
// every event from a connected installation. We verify HMAC signature, route
// on X-GitHub-Event, and either upsert PR rows + auto-link to issues or
// remove the installation on uninstall.
func (h *Handler) HandleGitHubWebhook(w http.ResponseWriter, r *http.Request) {
	body, err := io.ReadAll(io.LimitReader(r.Body, 10<<20)) // 10 MiB cap
	if err != nil {
		writeError(w, http.StatusBadRequest, "read body failed")
		return
	}
	secret := githubWebhookSecret()
	if secret == "" {
		// Refusing to process webhooks at all is safer than treating an
		// unconfigured deployment as "all signatures valid".
		writeError(w, http.StatusNotFound, "not found")
		return
	}
	sigHeader := r.Header.Get("X-Hub-Signature-256")
	if !verifyWebhookSignature(secret, sigHeader, body) {
		writeError(w, http.StatusUnauthorized, "invalid signature")
		return
	}
	event := r.Header.Get("X-GitHub-Event")
	ctx := r.Context()
	switch event {
	case "ping":
		writeJSON(w, http.StatusOK, map[string]string{"ok": "pong"})
		return
	case "installation":
		h.handleInstallationEvent(ctx, body)
	case "pull_request":
		h.handlePullRequestEvent(ctx, body)
	case "check_suite", "check_run", "status":
		// CI events are pure triggers under Plan C (MUL-5265): their payload is
		// never read for display. Each just asks the API pipeline to re-fetch
		// the authoritative snapshot for the PR(s) it concerns.
		h.triggerPRRefreshFromCIEvent(ctx, body)
	default:
		// Acknowledge every event so GitHub doesn't mark the endpoint failing,
		// but ignore types we don't model.
	}
	w.WriteHeader(http.StatusAccepted)
}

func verifyWebhookSignature(secret, header string, body []byte) bool {
	const prefix = "sha256="
	if !strings.HasPrefix(header, prefix) {
		return false
	}
	want, err := hex.DecodeString(strings.TrimPrefix(header, prefix))
	if err != nil {
		return false
	}
	mac := hmac.New(sha256.New, []byte(secret))
	mac.Write(body)
	return hmac.Equal(mac.Sum(nil), want)
}

type ghInstallationPayload struct {
	Action       string `json:"action"`
	Installation struct {
		ID      int64 `json:"id"`
		Account struct {
			Login     string `json:"login"`
			Type      string `json:"type"`
			AvatarURL string `json:"avatar_url"`
		} `json:"account"`
	} `json:"installation"`
}

func githubInstallationAccountFromPayload(p ghInstallationPayload) (login, accountType string, avatar *string, ok bool) {
	login = strings.TrimSpace(p.Installation.Account.Login)
	if login == "" {
		return "", "", nil, false
	}
	accountType = coalesce(p.Installation.Account.Type, "User")
	avatar = strPtrOrNil(p.Installation.Account.AvatarURL)
	return login, accountType, avatar, true
}

func (h *Handler) handleInstallationEvent(ctx context.Context, body []byte) {
	var p ghInstallationPayload
	if err := json.Unmarshal(body, &p); err != nil {
		slog.Warn("github: bad installation payload", "err", err)
		return
	}
	switch p.Action {
	case "deleted", "suspend":
		// User removed/suspended the App on GitHub — trust in this
		// installation_id is gone entirely, so drop every workspace binding.
		// We DELETE … RETURNING so each broadcast can be scoped to its
		// workspace; events without WorkspaceID are dropped by the realtime
		// listener and would leave already-open Settings tabs stale.
		deleted, err := h.Queries.DeleteGitHubInstallationByInstallationID(ctx, p.Installation.ID)
		if err != nil {
			slog.Warn("github: delete installation failed", "err", err, "installation_id", p.Installation.ID)
			return
		}
		if err := h.Queries.DeletePendingGitHubInstallation(ctx, p.Installation.ID); err != nil {
			slog.Warn("github: delete pending installation failed", "err", err, "installation_id", p.Installation.ID)
		}
		// Broadcast the internal row id only — the numeric installation_id is
		// a management handle that non-admin members are not allowed to see.
		// The frontend invalidates the installations query on this event and
		// does not read the broadcast payload directly. One broadcast per
		// deleted binding so every affected workspace's Settings tab refreshes.
		for _, row := range deleted {
			h.publish(protocol.EventGitHubInstallationDeleted, uuidToString(row.WorkspaceID), "system", "", map[string]any{
				"id": uuidToString(row.ID),
			})
		}
	case "created", "new_permissions_accepted", "unsuspend":
		login, accountType, avatar, ok := githubInstallationAccountFromPayload(p)
		if !ok {
			slog.Warn("github: installation payload missing account login", "installation_id", p.Installation.ID)
			return
		}

		// We don't know which workspace(s) this maps to from the webhook
		// alone. If no setup callback has created a workspace binding yet,
		// keep the account metadata and let the callback consume it after it
		// creates github_installation.
		existing, err := h.Queries.ListGitHubInstallationsByInstallationID(ctx, p.Installation.ID)
		if err != nil {
			slog.Warn("github: lookup installation failed", "err", err, "installation_id", p.Installation.ID)
			return
		}
		if len(existing) == 0 {
			if _, err := h.Queries.UpsertPendingGitHubInstallation(ctx, db.UpsertPendingGitHubInstallationParams{
				InstallationID:   p.Installation.ID,
				AccountLogin:     login,
				AccountType:      accountType,
				AccountAvatarUrl: ptrToText(avatar),
			}); err != nil {
				slog.Warn("github: store pending installation failed", "err", err, "installation_id", p.Installation.ID)
			}
			return
		}
		// Refresh the account display metadata across every workspace binding;
		// workspace_id and connected_by_id are left untouched.
		refreshed, err := h.Queries.UpdateGitHubInstallationAccountByInstallationID(ctx, db.UpdateGitHubInstallationAccountByInstallationIDParams{
			InstallationID:   p.Installation.ID,
			AccountLogin:     login,
			AccountType:      accountType,
			AccountAvatarUrl: ptrToText(avatar),
		})
		if err != nil {
			slog.Warn("github: refresh installation failed", "err", err)
			return
		}
		if err := h.Queries.DeletePendingGitHubInstallation(ctx, p.Installation.ID); err != nil {
			slog.Warn("github: delete pending installation failed", "err", err, "installation_id", p.Installation.ID)
		}
		// Broadcast so any open Settings → GitHub tab re-queries the
		// installations list. Without this, a row created by the setup
		// callback with the "unknown" placeholder (e.g. because GitHub
		// App JWT auth wasn't configured, or this webhook arrived after
		// the user already loaded the page) would stay visibly stale
		// until the user manually refreshes. One broadcast per bound workspace.
		for _, inst := range refreshed {
			h.publish(protocol.EventGitHubInstallationCreated, uuidToString(inst.WorkspaceID), "system", "", map[string]any{
				"installation": githubInstallationToBroadcast(inst),
			})
		}
	}
}

type ghPullRequestPayload struct {
	Action      string `json:"action"`
	PullRequest struct {
		Number         int32  `json:"number"`
		HTMLURL        string `json:"html_url"`
		Title          string `json:"title"`
		Body           string `json:"body"`
		State          string `json:"state"`
		Draft          bool   `json:"draft"`
		Merged         bool   `json:"merged"`
		MergedAt       string `json:"merged_at"`
		ClosedAt       string `json:"closed_at"`
		CreatedAt      string `json:"created_at"`
		UpdatedAt      string `json:"updated_at"`
		MergeableState string `json:"mergeable_state"`
		Additions      int32  `json:"additions"`
		Deletions      int32  `json:"deletions"`
		ChangedFiles   int32  `json:"changed_files"`
		Head           struct {
			Ref string `json:"ref"`
			SHA string `json:"sha"`
		} `json:"head"`
		User struct {
			Login     string `json:"login"`
			AvatarURL string `json:"avatar_url"`
		} `json:"user"`
	} `json:"pull_request"`
	Changes    *ghPRChanges `json:"changes"`
	Repository struct {
		Name  string `json:"name"`
		Owner struct {
			Login string `json:"login"`
		} `json:"owner"`
	} `json:"repository"`
	Installation struct {
		ID int64 `json:"id"`
	} `json:"installation"`
}

func (h *Handler) handlePullRequestEvent(ctx context.Context, body []byte) {
	var p ghPullRequestPayload
	if err := json.Unmarshal(body, &p); err != nil {
		slog.Warn("github: bad pull_request payload", "err", err)
		return
	}
	if p.Installation.ID == 0 {
		return
	}
	insts, err := h.Queries.ListGitHubInstallationsByInstallationID(ctx, p.Installation.ID)
	if err != nil {
		slog.Warn("github: lookup installation failed", "err", err)
		return
	}
	if len(insts) == 0 {
		// Webhook from an installation we never wired up — nothing we
		// can attribute to a workspace, so drop it silently.
		return
	}
	// #4855 lets one GitHub App installation bind to several workspaces. A
	// repo's events belong to every bound workspace, so fan the delivery out:
	// each workspace independently mirrors the PR and auto-links it against its
	// own issues (its own prefix + github toggle). Repo scope is whatever GitHub
	// authorized the installation for; we deliberately don't gate on the
	// workspace.repos registry — that list is "code the agent clones", not a
	// webhook subscription (MUL-4343).
	//
	// Fanning out means an identifier can resolve in more than one workspace at
	// once (issue prefixes are not globally unique and issue numbers restart at
	// 1 per workspace, so two bound workspaces can both own a real "ABC-100").
	// Settle who — if anyone — may link each identifier BEFORE any workspace
	// writes, and treat that verdict as authoritative for the whole delivery.
	// See prLinkPolicy.
	linkPolicy := h.resolvePRLinkPolicy(ctx, insts, &p)
	for _, inst := range insts {
		h.mirrorPullRequestForWorkspace(ctx, inst.WorkspaceID, inst.InstallationID, &p, linkPolicy)
	}
	// The PR row(s) now carry the new head; ask the API pipeline for the
	// authoritative CI + mergeability snapshot for that head. The webhook is
	// only the doorbell — its own mergeable/checks payload is not used for
	// display anymore (MUL-5265).
	h.PRRefresh.Enqueue(p.Installation.ID, p.Repository.Owner.Login, p.Repository.Name, p.PullRequest.Number)
}

// prLinkPolicy decides which (identifier, workspace) pairs this delivery may
// link, and is the single authority for that decision: the per-workspace mirror
// pass consults it instead of re-deriving the answer from its own reads.
//
// A link is a claim that the PR delivers the issue, and a merged link set with
// a closing keyword completes the issue. So when one installation is bound to
// several workspaces, an identifier that resolves in more than one of them must
// not link anywhere — otherwise merging the PR could complete an issue in a
// workspace that has nothing to do with it (#6804). A member can still link the
// PR by hand.
type prLinkPolicy struct {
	// unrestricted marks the single-binding case: cross-workspace ambiguity is
	// impossible with one bound workspace, so the scan does no reads at all.
	unrestricted bool
	// indeterminate means a read failed, so we cannot tell ambiguous from
	// unique. The mirror pass then leaves links and statuses untouched for this
	// delivery rather than guessing either way.
	indeterminate bool
	// owner maps an identifier to the one auto-linking workspace proven to
	// resolve it.
	owner map[string]string
	// ambiguous lists identifiers that resolved in more than one auto-linking
	// workspace.
	ambiguous map[string]bool
}

// permits reports whether workspaceID may link identifier.
func (c prLinkPolicy) permits(identifier, workspaceID string) bool {
	if c.unrestricted {
		return true
	}
	owner, ok := c.owner[identifier]
	return ok && owner == workspaceID
}

// resolvePRLinkPolicy determines, before any workspace writes, which claimed
// identifiers on this PR may link, and in which workspace. An identifier is
// allowed only when exactly one auto-linking bound workspace was proven to
// resolve it; misjudging "unique" as "ambiguous" costs a link a person can add
// by hand, while the reverse would move someone else's issue on merge.
func (h *Handler) resolvePRLinkPolicy(ctx context.Context, insts []db.GithubInstallation, p *ghPullRequestPayload) prLinkPolicy {
	if len(insts) < 2 {
		return prLinkPolicy{unrestricted: true}
	}
	idents := prClaimedIdentifiers(p.PullRequest.Title, p.PullRequest.Body, p.PullRequest.Head.Ref)
	policy := prLinkPolicy{owner: map[string]string{}, ambiguous: map[string]bool{}}
	if len(idents) == 0 {
		return policy
	}
	prNumber := p.PullRequest.Number
	repo := p.Repository.Owner.Login + "/" + p.Repository.Name
	indeterminate := func(msg string, args ...any) prLinkPolicy {
		slog.Warn(msg, append(args, "installation_id", p.Installation.ID, "repo", repo, "pr_number", prNumber)...)
		return prLinkPolicy{indeterminate: true}
	}

	type resolver struct {
		workspaceID string
		autoLink    bool
	}
	resolvers := make(map[string][]resolver, len(idents))
	for _, inst := range insts {
		ws, err := h.Queries.GetWorkspace(ctx, inst.WorkspaceID)
		if err != nil {
			return indeterminate("github: cannot load bound workspace, leaving links unchanged for this delivery", "err", err)
		}
		// A settings blob we cannot parse is not evidence either way, so it
		// fails closed.
		autoLink, err := autoLinkPRsEnabledForWorkspace(ws)
		if err != nil {
			return indeterminate("github: cannot read workspace auto-link setting, leaving links unchanged for this delivery",
				"err", err, "workspace_id", uuidToString(inst.WorkspaceID))
		}
		// With GitHub off a workspace acts on nothing, so it claims nothing.
		if !githubFeaturesEnabled(ws) {
			continue
		}
		prefix := issuePrefixForWorkspace(ws)
		for _, id := range idents {
			number, ok := issueNumberForPrefix(id, prefix)
			if !ok {
				continue
			}
			if _, err := h.Queries.GetIssueByNumber(ctx, db.GetIssueByNumberParams{
				WorkspaceID: inst.WorkspaceID,
				Number:      number,
			}); err != nil {
				if errors.Is(err, pgx.ErrNoRows) {
					continue
				}
				return indeterminate("github: cannot resolve identifier in bound workspace, leaving links unchanged for this delivery",
					"err", err, "identifier", id, "workspace_id", uuidToString(inst.WorkspaceID))
			}
			resolvers[id] = append(resolvers[id], resolver{workspaceID: uuidToString(inst.WorkspaceID), autoLink: autoLink})
		}
	}
	for id, all := range resolvers {
		// A workspace with auto-link off never writes a link row, so it is not
		// a competing claimant for the link.
		var wss []string
		for _, r := range all {
			if r.autoLink {
				wss = append(wss, r.workspaceID)
			}
		}
		if len(wss) == 0 {
			continue
		}
		if len(wss) == 1 {
			policy.owner[id] = wss[0]
			continue
		}
		policy.ambiguous[id] = true
		// An issue that never auto-links is otherwise untraceable back to
		// GitHub, so leave a breadcrumb naming the collision.
		slog.Warn("github: identifier resolves in several bound workspaces, not auto-linking it",
			"identifier", id, "workspaces", len(wss),
			"installation_id", p.Installation.ID, "repo", repo, "pr_number", prNumber)
	}
	return policy
}

// ghCIEventPayload captures the shared shape of the check_suite / check_run /
// status webhooks — enough to resolve which PR to refresh. These events are
// pure triggers under Plan C: their payload data is never read for display.
type ghCIEventPayload struct {
	Installation struct {
		ID int64 `json:"id"`
	} `json:"installation"`
	Repository struct {
		Name  string `json:"name"`
		Owner struct {
			Login string `json:"login"`
		} `json:"owner"`
	} `json:"repository"`
	// status events: top-level commit SHA, no PR number.
	SHA        string `json:"sha"`
	CheckSuite struct {
		HeadSHA      string `json:"head_sha"`
		PullRequests []struct {
			Number int32 `json:"number"`
		} `json:"pull_requests"`
	} `json:"check_suite"`
	CheckRun struct {
		PullRequests []struct {
			Number int32 `json:"number"`
		} `json:"pull_requests"`
		CheckSuite struct {
			HeadSHA string `json:"head_sha"`
		} `json:"check_suite"`
	} `json:"check_run"`
}

// triggerPRRefreshFromCIEvent enqueues an API refresh for the PR(s) a
// check_suite / check_run / status webhook concerns. check_suite/check_run
// carry the PR numbers directly; status events carry only a commit SHA, so we
// map it back to the mirrored head_sha to find the PR(s).
func (h *Handler) triggerPRRefreshFromCIEvent(ctx context.Context, body []byte) {
	if !h.PRRefresh.Enabled() {
		return
	}
	var p ghCIEventPayload
	if err := json.Unmarshal(body, &p); err != nil {
		return
	}
	if p.Installation.ID == 0 || p.Repository.Name == "" {
		return
	}
	owner, repo := p.Repository.Owner.Login, p.Repository.Name

	seen := map[int32]struct{}{}
	enqueue := func(number int32) {
		if number == 0 {
			return
		}
		if _, ok := seen[number]; ok {
			return
		}
		seen[number] = struct{}{}
		h.PRRefresh.Enqueue(p.Installation.ID, owner, repo, number)
	}
	for _, pr := range p.CheckSuite.PullRequests {
		enqueue(pr.Number)
	}
	for _, pr := range p.CheckRun.PullRequests {
		enqueue(pr.Number)
	}
	if len(seen) > 0 {
		return
	}
	// No PR number in the payload (status event, or a check event whose
	// pull_requests array was empty) — resolve by head SHA.
	sha := p.SHA
	if sha == "" {
		sha = coalesce(p.CheckSuite.HeadSHA, p.CheckRun.CheckSuite.HeadSHA)
	}
	if sha == "" {
		return
	}
	numbers, err := h.Queries.ListGitHubPRNumbersByHeadSHA(ctx, db.ListGitHubPRNumbersByHeadSHAParams{
		InstallationID: p.Installation.ID,
		RepoOwner:      owner,
		RepoName:       repo,
		HeadSha:        sha,
	})
	if err != nil {
		return
	}
	for _, number := range numbers {
		enqueue(number)
	}
}

// mirrorPullRequestForWorkspace mirrors a pull_request webhook into a single
// workspace: it upserts the PR row, replays any check_suite events that
// arrived before the PR was mirrored, auto-links referenced issues (gated by
// the workspace's github toggles), completes issues on a merge, and broadcasts
// the change. Invoked once per workspace bound to the delivering installation.
//
// linkPolicy is the delivery-wide verdict on which identifiers this workspace
// may link; this function only ever narrows it.
func (h *Handler) mirrorPullRequestForWorkspace(ctx context.Context, wsID pgtype.UUID, installationID int64, p *ghPullRequestPayload, linkPolicy prLinkPolicy) {
	state := derivePRState(p.PullRequest.State, p.PullRequest.Draft, p.PullRequest.Merged)
	mergeable, clearMergeable := derivePRMergeableState(p.Action, p.PullRequest.MergeableState, baseRefChanged(p.Changes))
	// The stored state before this event, so a merge is acted on once — when it
	// happens — and not again on a later edit/label event of a merged PR (that
	// would re-complete an issue someone reopened on purpose).
	prevState := ""
	if prev, err := h.Queries.GetGitHubPullRequest(ctx, db.GetGitHubPullRequestParams{
		WorkspaceID: wsID,
		RepoOwner:   p.Repository.Owner.Login,
		RepoName:    p.Repository.Name,
		PrNumber:    p.PullRequest.Number,
	}); err == nil {
		prevState = prev.State
	}
	pr, err := h.Queries.UpsertGitHubPullRequest(ctx, db.UpsertGitHubPullRequestParams{
		WorkspaceID:         wsID,
		InstallationID:      installationID,
		RepoOwner:           p.Repository.Owner.Login,
		RepoName:            p.Repository.Name,
		PrNumber:            p.PullRequest.Number,
		Title:               p.PullRequest.Title,
		State:               state,
		HtmlUrl:             p.PullRequest.HTMLURL,
		Branch:              ptrToText(strPtrOrNil(p.PullRequest.Head.Ref)),
		AuthorLogin:         ptrToText(strPtrOrNil(p.PullRequest.User.Login)),
		AuthorAvatarUrl:     ptrToText(strPtrOrNil(p.PullRequest.User.AvatarURL)),
		MergedAt:            parseGHTime(p.PullRequest.MergedAt),
		ClosedAt:            parseGHTime(p.PullRequest.ClosedAt),
		PrCreatedAt:         parseGHTimeRequired(p.PullRequest.CreatedAt),
		PrUpdatedAt:         parseGHTimeRequired(p.PullRequest.UpdatedAt),
		HeadSha:             p.PullRequest.Head.SHA,
		MergeableState:      mergeable,
		ClearMergeableState: pgtype.Bool{Bool: clearMergeable, Valid: true},
		Additions:           p.PullRequest.Additions,
		Deletions:           p.PullRequest.Deletions,
		ChangedFiles:        p.PullRequest.ChangedFiles,
	})
	if errors.Is(err, pgx.ErrNoRows) {
		// A stale redelivery: the stored row is newer, and the newer event
		// already linked and published. Acting on this one would roll links
		// and merge detection back.
		return
	}
	if err != nil {
		slog.Warn("github: upsert pr failed", "err", err)
		return
	}

	workspaceID := uuidToString(wsID)
	resp := githubPullRequestToResponse(pr, h.PRRefresh.Enabled())
	linkedIssueIDs := make([]string, 0)

	// RFC MUL-2414 §4.8: the PR mirror upsert above always runs (so re-enabling
	// GitHub features restores history without backfill), but links and status
	// changes are side-effects gated by the master `github_enabled` switch.
	ws, err := h.Queries.GetWorkspace(ctx, wsID)
	if err == nil && githubFeaturesEnabled(ws) && !linkPolicy.indeterminate {
		touched := map[pgtype.UUID]struct{}{}
		idents := prClaimedIdentifiers(p.PullRequest.Title, p.PullRequest.Body, p.PullRequest.Head.Ref)
		if autoLink, _ := autoLinkPRsEnabledForWorkspace(ws); autoLink {
			linkedIssueIDs, touched = h.reconcileAutoLinks(ctx, ws, pr.ID, state, prAutoLinkInput{
				idents:    idents,
				permits:   func(id string) bool { return linkPolicy.permits(id, workspaceID) },
				ambiguous: func(id string) bool { return linkPolicy.ambiguous[id] },
				link: func(issueID pgtype.UUID) (int64, error) {
					return h.Queries.LinkIssueToPullRequest(ctx, db.LinkIssueToPullRequestParams{IssueID: issueID, PullRequestID: pr.ID})
				},
				unlink: func(issueID pgtype.UUID) (int64, error) {
					return h.Queries.UnlinkIssueFromPullRequest(ctx, db.UnlinkIssueFromPullRequestParams{IssueID: issueID, PullRequestID: pr.ID})
				},
				listAuto: func() ([]pgtype.UUID, error) {
					return h.Queries.ListAutoLinkedIssueIDsForPullRequest(ctx, pr.ID)
				},
			})
		}
		// A merge is a PR event for every issue this PR is linked to, manual
		// links included.
		if state == "merged" && prevState != "merged" {
			issueIDs, err := h.Queries.ListIssueIDsForPullRequest(ctx, pr.ID)
			if err != nil {
				slog.Warn("github: list linked issues failed", "err", err)
			}
			for _, id := range issueIDs {
				touched[id] = struct{}{}
			}
		}
		// One status-catalog read per workspace per delivery, however many
		// issues the PR touches.
		resolver := issuestatus.NewResolver(wsID)
		for issueID := range touched {
			h.maybeAutoCompleteIssue(ctx, wsID, issueID, resolver)
		}
	}

	// Broadcast PR change to the workspace so any open issue detail page
	// re-queries its PR list.
	h.publish(protocol.EventPullRequestUpdated, workspaceID, "system", "", map[string]any{
		"pull_request":     resp,
		"linked_issue_ids": linkedIssueIDs,
	})
}

// prAutoLinkInput is what reconcileAutoLinks needs from one provider.
type prAutoLinkInput struct {
	idents    []string                         // identifiers the PR claims (see prClaimedIdentifiers)
	permits   func(identifier string) bool     // cross-workspace verdict (always true for VCS)
	ambiguous func(identifier string) bool     // resolved in several workspaces
	link      func(pgtype.UUID) (int64, error) // automatic link; 1 when new
	unlink    func(pgtype.UUID) (int64, error) // drop a link; 1 when removed
	listAuto  func() ([]pgtype.UUID, error)    // issues currently auto-linked to the PR
}

// reconcileAutoLinks makes the PR's automatic links match its claims. It
// returns the issue ids the PR is linked to after the pass, and the issues
// whose link set changed (a PR event for auto-complete).
//
//   - A claimed identifier that resolves here links, unless a person removed
//     that PR from that issue before.
//   - An automatic link the PR no longer claims is dropped while the PR is still
//     open. After merge/close it is kept: a later title edit must not take the
//     delivered work off the issue.
//   - An automatic link for an identifier that turned out to be ambiguous
//     across bound workspaces is always dropped (see prLinkPolicy).
//
// Manual links are never touched here.
func (h *Handler) reconcileAutoLinks(ctx context.Context, ws db.Workspace, prID pgtype.UUID, state string, in prAutoLinkInput) ([]string, map[pgtype.UUID]struct{}) {
	touched := map[pgtype.UUID]struct{}{}
	linked := make([]string, 0)
	claimed := map[pgtype.UUID]struct{}{}
	ambiguousIssues := map[pgtype.UUID]struct{}{}
	prefix := issuePrefixForWorkspace(ws)
	for _, id := range in.idents {
		issue, ok := h.lookupIssueByIdentifier(ctx, ws.ID, prefix, id)
		if !ok {
			continue
		}
		if !in.permits(id) {
			if in.ambiguous(id) {
				ambiguousIssues[issue.ID] = struct{}{}
			}
			continue
		}
		excluded, err := h.Queries.IsPullRequestExcludedFromIssue(ctx, db.IsPullRequestExcludedFromIssueParams{IssueID: issue.ID, PullRequestID: prID})
		if err != nil {
			slog.Warn("pr link: exclusion lookup failed", "err", err)
			continue
		}
		if excluded {
			continue
		}
		claimed[issue.ID] = struct{}{}
		rows, err := in.link(issue.ID)
		if err != nil {
			slog.Warn("pr link: link failed", "err", err)
			continue
		}
		linked = append(linked, uuidToString(issue.ID))
		if rows > 0 {
			touched[issue.ID] = struct{}{}
		}
	}

	terminal := state == "merged" || state == "closed"
	current, err := in.listAuto()
	if err != nil {
		slog.Warn("pr link: list auto links failed", "err", err)
		return linked, touched
	}
	for _, issueID := range current {
		if _, ok := claimed[issueID]; ok {
			continue
		}
		_, ambiguous := ambiguousIssues[issueID]
		if terminal && !ambiguous {
			continue
		}
		rows, err := in.unlink(issueID)
		if err != nil {
			slog.Warn("pr link: unlink failed", "err", err)
			continue
		}
		if rows > 0 {
			// Dropping an unmerged PR can leave only merged ones behind.
			touched[issueID] = struct{}{}
		}
	}
	return linked, touched
}

// derivePRMergeableState resolves the upsert behaviour for the PR row's
// mergeable_state column on a `pull_request` webhook. It returns three
// states encoded as (value, clear):
//
//   - clear=true → force the column to NULL. State-changing actions (`opened`,
//     `synchronize`, `reopened`, or a base-branch swap) must blank the value
//     because GitHub re-computes mergeability asynchronously; the payload may
//     still carry the previous head's clean/dirty answer, and trusting it
//     would surface a stale verdict against the new head.
//   - clear=false, value valid → write the value. The event carried a
//     concrete verdict we should persist.
//   - clear=false, value invalid → preserve the existing column. Metadata
//     events (labeled/assigned/edited-without-base-swap) ship pull_request
//     payloads with mergeable_state empty even when the previous verdict is
//     still accurate, and silently overwriting clean/dirty with NULL would
//     drop information GitHub only refreshes lazily.
func derivePRMergeableState(action, payload string, baseRefChanged bool) (pgtype.Text, bool) {
	if action == "opened" || action == "synchronize" || action == "reopened" {
		return pgtype.Text{}, true
	}
	if action == "edited" && baseRefChanged {
		return pgtype.Text{}, true
	}
	if payload == "" {
		return pgtype.Text{}, false
	}
	return pgtype.Text{String: payload, Valid: true}, false
}

// ghPRChanges captures the only field of `pull_request.edited`'s `changes`
// payload we care about: a base-branch swap. Everything else (title, body)
// leaves mergeability intact.
type ghPRChanges struct {
	Base *struct {
		Ref *struct {
			From string `json:"from"`
		} `json:"ref"`
	} `json:"base"`
}

// baseRefChanged returns true when a pull_request.edited event indicates the
// PR's base branch was swapped. Only this kind of edit invalidates the
// existing mergeable_state.
func baseRefChanged(c *ghPRChanges) bool {
	return c != nil && c.Base != nil && c.Base.Ref != nil && c.Base.Ref.From != ""
}

func derivePRState(state string, draft, merged bool) string {
	if merged {
		return "merged"
	}
	if state == "closed" {
		return "closed"
	}
	if draft {
		return "draft"
	}
	return "open"
}

func parseGHTime(s string) pgtype.Timestamptz {
	if s == "" {
		return pgtype.Timestamptz{}
	}
	t, err := time.Parse(time.RFC3339, s)
	if err != nil {
		return pgtype.Timestamptz{}
	}
	return pgtype.Timestamptz{Time: t, Valid: true}
}

func parseGHTimeRequired(s string) pgtype.Timestamptz {
	t := parseGHTime(s)
	if !t.Valid {
		return pgtype.Timestamptz{Time: time.Now().UTC(), Valid: true}
	}
	return t
}

// extractIdentifiers pulls every "PREFIX-NUMBER" match across the supplied
// fields, deduplicating in input order.
func extractIdentifiers(parts ...string) []string {
	return extractMatchedIdentifiers(identifierRe, parts...)
}

// extractClosingIdentifiers pulls every identifier that appears immediately
// after a closing keyword. Callers pass the title and body only: in a branch
// name any identifier links already.
func extractClosingIdentifiers(parts ...string) []string {
	return extractMatchedIdentifiers(closingIdentifierRe, parts...)
}

func extractMatchedIdentifiers(re *regexp.Regexp, parts ...string) []string {
	seen := map[string]struct{}{}
	out := []string{}
	for _, src := range parts {
		for _, m := range re.FindAllStringSubmatch(src, -1) {
			ident := strings.ToUpper(m[1]) + "-" + m[2]
			if _, dup := seen[ident]; dup {
				continue
			}
			seen[ident] = struct{}{}
			out = append(out, ident)
		}
	}
	return out
}

// prClaimedIdentifiers returns the identifiers a PR claims. The title and
// branch name link an issue, and so does a closing keyword in the body
// ("Closes MUL-1"). A bare mention in the body claims nothing.
func prClaimedIdentifiers(title, body, branch string) []string {
	idents := extractIdentifiers(title, branch)
	for _, id := range extractClosingIdentifiers(title, body) {
		if !slices.Contains(idents, id) {
			idents = append(idents, id)
		}
	}
	return idents
}

// autoLinkPRsEnabledForWorkspace reports whether the workspace allows the
// GitHub webhook to create issue ↔ PR link rows. Defaults to true so that
// workspaces predating RFC MUL-2414 keep the historical "auto-link on"
// behavior, and short-circuits to false whenever the master GitHub switch
// is explicitly off — mirroring the precedence used on the client side.
//
// An unparseable settings blob is surfaced as an error instead of being folded
// into the permissive default. Callers deciding whether to write a link row
// keep taking the default, but the cross-workspace scan has to tell "auto-link
// is on" apart from "we could not find out" — see prLinkPolicy.
func autoLinkPRsEnabledForWorkspace(ws db.Workspace) (bool, error) {
	if len(ws.Settings) == 0 {
		return true, nil
	}
	var s struct {
		GitHubEnabled            *bool `json:"github_enabled"`
		GitHubAutoLinkPRsEnabled *bool `json:"github_auto_link_prs_enabled"`
	}
	if err := json.Unmarshal(ws.Settings, &s); err != nil {
		return true, err
	}
	if s.GitHubEnabled != nil && !*s.GitHubEnabled {
		return false, nil
	}
	if s.GitHubAutoLinkPRsEnabled == nil {
		return true, nil
	}
	return *s.GitHubAutoLinkPRsEnabled, nil
}

// githubFeaturesEnabled reads the GitHub master switch. Absent means on.
func githubFeaturesEnabled(ws db.Workspace) bool {
	if len(ws.Settings) == 0 {
		return true
	}
	var s struct {
		GitHubEnabled *bool `json:"github_enabled"`
	}
	if err := json.Unmarshal(ws.Settings, &s); err != nil {
		return true
	}
	return s.GitHubEnabled == nil || *s.GitHubEnabled
}

// issueNumberForPrefix returns the issue number encoded in a "PREFIX-NUMBER"
// identifier, and false when the identifier is malformed or carries a prefix
// that is not the workspace's. Case-insensitive: branch names are
// conventionally lowercase while issue prefixes are uppercase.
func issueNumberForPrefix(identifier, prefix string) (int32, bool) {
	idx := strings.LastIndex(identifier, "-")
	if idx < 0 {
		return 0, false
	}
	gotPrefix, numStr := identifier[:idx], identifier[idx+1:]
	if !strings.EqualFold(gotPrefix, prefix) {
		return 0, false
	}
	n, err := strconv.Atoi(numStr)
	if err != nil {
		return 0, false
	}
	return int32(n), true
}

// lookupIssueByIdentifier looks up an issue in the given workspace by its
// "PREFIX-NUMBER" identifier. Returns the row + true if the prefix matches
// the workspace's configured prefix and the number resolves to a real issue.
func (h *Handler) lookupIssueByIdentifier(ctx context.Context, workspaceID pgtype.UUID, prefix, identifier string) (db.Issue, bool) {
	number, ok := issueNumberForPrefix(identifier, prefix)
	if !ok {
		return db.Issue{}, false
	}
	issue, err := h.Queries.GetIssueByNumber(ctx, db.GetIssueByNumberParams{
		WorkspaceID: workspaceID,
		Number:      number,
	})
	if err != nil {
		return db.Issue{}, false
	}
	return issue, true
}

// ── Helpers ─────────────────────────────────────────────────────────────────

func parseStrictUUID(s string) (pgtype.UUID, error) {
	var u pgtype.UUID
	if err := u.Scan(s); err != nil {
		return pgtype.UUID{}, err
	}
	return u, nil
}

func coalesce(a, fallback string) string {
	if strings.TrimSpace(a) == "" {
		return fallback
	}
	return a
}

func strPtrOrNil(s string) *string {
	if s == "" {
		return nil
	}
	v := s
	return &v
}