Skip to content

A desktop coding agent with free access to frontier models.

TypeScript
313 KB
Raw Blame
import { and, asc, count, desc, eq, inArray, isNull, or } from "@omnirush-ee/den-db/drizzle"
import {
  AuthUserTable,
  ConfigObjectAccessGrantTable,
  ConfigObjectTable,
  ConfigObjectVersionTable,
  ConnectorAccountTable,
  ConnectorInstanceAccessGrantTable,
  ConnectorInstanceTable,
  ConnectorMappingTable,
  ConnectorSourceBindingTable,
  ConnectorSourceTombstoneTable,
  ConnectorSyncEventTable,
  ConnectorTargetTable,
  ExternalMcpConnectionAccessGrantTable,
  ExternalMcpConnectionTable,
  MarketplaceAccessGrantTable,
  MarketplacePluginTable,
  MarketplaceTable,
  MemberTable,
  OrganizationTable,
  PluginAccessGrantTable,
  PluginConfigObjectTable,
  PluginMcpRequirementBindingTable,
  PluginTable,
  RemoteMcpAppTable,
  TeamTable,
} from "@omnirush-ee/den-db/schema"
import { createDenTypeId, normalizeDenTypeId } from "@omnirush-ee/utils/typeid"
import { hasSkillFrontmatterName, parseSkillMarkdown } from "@omnirush-ee/utils"
import type { PluginArchActorContext, PluginArchResourceKind, PluginArchRole } from "./access.js"
import { isPluginArchOrgAdmin, PluginArchAuthorizationError, pluginArchResourceHasExpandedAudience, requirePluginArchResourceRole, resolvePluginArchGrantRole, resolvePluginArchResourceRole } from "./access.js"
import { CONTENT_EDIT_SESSION_MAX_AGE_MS } from "../shared.js"
import { clampCodePoints, clampUtf8Bytes, PROJECTION_TEXT_MAX_BYTES, PROJECTION_TITLE_MAX_CHARS } from "./projection-text.js"
import {
  AGENT_PLUGIN_V1_VERSION,
  parseAgentPluginV1McpText,
} from "./agent-plugin-v1.js"
import {
  buildGithubAppInstallUrl,
  createGithubInstallStateToken,
  fetchGithubImportFilesWithRevisionGuard,
  GithubConnectorConfigError,
  GithubConnectorRequestError,
  getGithubAppSummary,
  getGithubConnectorAppConfig,
  getGithubInstallationAccessToken,
  getGithubRepositoryHeadSha,
  getGithubRepositoryTextFile,
  getGithubRepositoryTree,
  getGithubInstallationSummary,
  listGithubInstallationRepositories,
  validateGithubInstallationTarget,
  verifyGithubInstallStateToken,
} from "./github-app.js"
import {
  buildGithubRepoDiscovery,
  type GithubDiscoveredPlugin,
  type GithubDiscoveryClassification,
  type GithubMarketplaceInfo,
  type GithubDiscoveryTreeEntry,
} from "./github-discovery.js"
import { planConnectorImportedResourceCleanup, uniqueIds } from "./connector-cleanup.js"
import {
  DEFAULT_ANTHROPIC_MARKETPLACE_DESCRIPTION,
  DEFAULT_ANTHROPIC_MARKETPLACE_LOGO_URL,
  DEFAULT_ANTHROPIC_MARKETPLACE_NAME,
  DEFAULT_ANTHROPIC_STARTER_PLUGINS,
  DEFAULT_OMNIRUSH_MARKETPLACE_DESCRIPTION,
  DEFAULT_OMNIRUSH_MARKETPLACE_LOGO_URL,
  DEFAULT_OMNIRUSH_MARKETPLACE_NAME,
  type DefaultMarketplacePluginEntry,
} from "./default-marketplaces.js"
import { db } from "../../../db.js"
import { resolveOrganizationMemberAuthority } from "../../../organization-team-roles.js"
import { env } from "../../../env.js"
import { appLogger } from "../../../observability/logger.js"
import { roleIncludesOwner } from "../../../orgs.js"
import { redactWorkflowNormalizedPayloadAuthoringDetails } from "../../../workflow-projections.js"
import { memberFacingMcpConnectionsEnabled } from "../../../capability-sources/external-mcp-rollout.js"
import { comparablePluginMcpRequirementUrl, marketplaceMcpServerEntries, resolveMarketplacePluginCloudReadiness } from "../../../mcp/marketplace-capabilities.js"
import { assertPublicUrl } from "../../../capability-sources/url-guard.js"
import {
  createExternalMcpConnection,
  deleteExternalMcpConnection,
  deleteExternalMcpConnectionIfUnreferenced,
  getExternalMcpConnection,
  listExternalMcpConnections,
  replaceExternalMcpConnectionAccessForPluginBinding,
} from "../../../capability-sources/external-mcp-connections.js"
import { connectExternalMcp } from "../../../capability-sources/external-mcp-client-runtime.js"
import {
  externalMcpDiagnosticForLog,
  externalMcpDiagnosticForResponse,
  safeExternalMcpEndpointForLog,
} from "../../../capability-sources/external-mcp-diagnostics.js"
import { getOrgOAuthClient, upsertOrgOAuthClient } from "../../../capability-sources/oauth-credentials.js"
import {
  deletePluginMcpRequirementBindingsByIds,
  deletePluginMcpRequirementBindingsForPluginConfigObject,
  deletePluginMcpRequirementBindingsForPlugin,
  listPluginMcpRequirementBindings,
  upsertPluginMcpRequirementBinding,
  type PluginMcpRequirementBindingRow,
} from "../../../mcp/plugin-mcp-requirement-bindings.js"
import { omnirushYourConnectionsUrl } from "../../../mcp/connection-navigation.js"
import {
  declaredPluginMcpAuthType,
  requiredPluginMcpAuthType,
  resolveGithubPluginMcpImportAuthType,
  type PluginMcpAuthType,
} from "../../../capability-sources/external-mcp-auth-policy.js"
import type { MemberUsableConnectionFacts } from "../mcp-connections.js"

type OrganizationId = PluginArchActorContext["organizationContext"]["organization"]["id"]
const logger = appLogger.child({ component: "plugin_system_store" })
type MemberId = PluginArchActorContext["organizationContext"]["currentMember"]["id"]
type TeamId = PluginArchActorContext["memberTeams"][number]["id"]
type ConfigObjectRow = typeof ConfigObjectTable.$inferSelect
type ConfigObjectVersionRow = typeof ConfigObjectVersionTable.$inferSelect
type MarketplaceRow = typeof MarketplaceTable.$inferSelect
type MarketplaceMembershipRow = typeof MarketplacePluginTable.$inferSelect
type PluginRow = typeof PluginTable.$inferSelect
type PluginMembershipRow = typeof PluginConfigObjectTable.$inferSelect
type ConfigObjectId = ConfigObjectRow["id"]
type ConfigObjectVersionId = ConfigObjectVersionRow["id"]
type MarketplaceId = MarketplaceRow["id"]
type MarketplaceMembershipId = MarketplaceMembershipRow["id"]
type PluginId = PluginRow["id"]
type PluginMembershipId = PluginMembershipRow["id"]
type AccessGrantRow =
  | typeof ConfigObjectAccessGrantTable.$inferSelect
  | typeof MarketplaceAccessGrantTable.$inferSelect
  | typeof PluginAccessGrantTable.$inferSelect
  | typeof ConnectorInstanceAccessGrantTable.$inferSelect
type ConfigObjectAccessGrantId = typeof ConfigObjectAccessGrantTable.$inferSelect.id
type MarketplaceAccessGrantId = typeof MarketplaceAccessGrantTable.$inferSelect.id
type PluginAccessGrantId = typeof PluginAccessGrantTable.$inferSelect.id
type ConnectorInstanceAccessGrantId = typeof ConnectorInstanceAccessGrantTable.$inferSelect.id
type ConnectorAccountRow = typeof ConnectorAccountTable.$inferSelect
type ConnectorInstanceRow = typeof ConnectorInstanceTable.$inferSelect
type ConnectorTargetRow = typeof ConnectorTargetTable.$inferSelect
type ConnectorMappingRow = typeof ConnectorMappingTable.$inferSelect
type ConnectorSyncEventRow = typeof ConnectorSyncEventTable.$inferSelect
type ConnectorAccountId = ConnectorAccountRow["id"]
type ConnectorInstanceId = ConnectorInstanceRow["id"]
type ConnectorTargetId = ConnectorTargetRow["id"]
type ConnectorMappingId = ConnectorMappingRow["id"]
type ConnectorSyncEventId = ConnectorSyncEventRow["id"]
type MemberRow = typeof MemberTable.$inferSelect
type OrganizationRow = typeof OrganizationTable.$inferSelect
type ExternalMcpConnectionRow = typeof ExternalMcpConnectionTable.$inferSelect
type PluginMcpRequirementBindingId = PluginMcpRequirementBindingRow["id"]
type DbTransaction = Parameters<Parameters<typeof db.transaction>[0]>[0]

type CursorPage<TItem extends { id: string }> = {
  items: TItem[]
  nextCursor: string | null
}

type GithubConnectorDiscoveryStep = {
  id: "read_repository_structure" | "check_marketplace_manifest" | "check_plugin_manifests" | "prepare_discovered_plugins"
  label: string
  status: "completed" | "running" | "warning"
}

type GithubConnectorDiscoveryTreeSummary = {
  scannedEntryCount: number
  strategy: "git-tree-recursive"
  truncated: boolean
}

type GithubDiscoveryImportPlan = {
  fileShaByPath?: Record<string, string>
  objectType: ConnectorMappingRow["objectType"]
  paths: string[]
  selector: string
}

type GithubDiscoveryCacheEntry = {
  branch: string
  classification: GithubDiscoveryClassification
  discoveredPlugins: GithubDiscoveredPlugin[]
  importPlansByPluginKey: Record<string, GithubDiscoveryImportPlan[]>
  marketplace: GithubMarketplaceInfo | null
  ref: string
  repositoryFullName: string
  sourceRevisionRef: string
  treeSummary: GithubConnectorDiscoveryTreeSummary
  warnings: string[]
}

type GithubConnectorDiscoveryComputation = GithubDiscoveryCacheEntry & {
  connectorInstance: ReturnType<typeof serializeConnectorInstance>
  connectorTarget: ReturnType<typeof serializeConnectorTarget>
  treeEntries: GithubDiscoveryTreeEntry[]
}

type GithubDiscoverySnapshot = GithubDiscoveryCacheEntry & {
  treeEntries: GithubDiscoveryTreeEntry[]
}

type PublicGithubPluginTarget = {
  branch: string | null
  repositoryFullName: string
  rootPath: string
}

type PublicGithubTreeSnapshot = {
  branch: string
  fullPathByDiscoveryPath: Map<string, string>
  headSha: string
  repositoryFullName: string
  rootPath: string
  treeEntries: GithubDiscoveryTreeEntry[]
  truncated: boolean
}

type GithubPluginMcpImportAccess = {
  memberIds: MemberId[]
  orgWide: boolean
  teamIds: TeamId[]
}

type PluginMcpRequirementAccess = GithubPluginMcpImportAccess

type PluginMcpRequirementAuthType = "apikey" | "none" | "oauth"

type PluginMcpRequirementCredentialMode = "per_member" | "shared"

type PluginMcpRequirementServer = {
  config: Record<string, unknown>
  name: string
  url: string
}

type PluginMcpConnectionSetup = {
  apiKey?: string
  authType: PluginMcpRequirementAuthType
  credentialMode?: PluginMcpRequirementCredentialMode
  oauthClient?: { clientId: string; clientSecret?: string }
}

type GithubPluginMcpImportServer = {
  authType: "oauth" | null
  connectionId: string | null
  name: string
  pluginKey: string
  pluginName: string
  serverKey: string
  skippedReason: "headers_unsupported" | "invalid_config" | "invalid_url" | "local_unsupported" | "missing_url" | "unsupported_auth" | null
  sourceSchemaVersion: string | null
  sourcePath: string
  supported: boolean
  url: string | null
}

type GithubPluginMcpImportPlugin = {
  description: string | null
  key: string
  mcpCount: number
  name: string
  skillCount: number
}

type GithubPluginSkillImportSkill = {
  description: string | null
  name: string
  pluginKey: string
  pluginName: string
  rawSourceText?: string
  skillKey: string
  skippedReason: "invalid_skill" | null
  sourceSchemaVersion: string | null
  sourcePath: string
  supported: boolean
}

type GithubPluginMcpImportPlan = {
  branch: string
  classification: GithubDiscoveryClassification
  marketplace: GithubMarketplaceInfo | null
  plugins: GithubPluginMcpImportPlugin[]
  repositoryFullName: string
  rootPath: string
  servers: GithubPluginMcpImportServer[]
  skills: GithubPluginSkillImportSkill[]
  sourceSchemaVersion: string | null
  sourceRevisionRef: string
  warnings: string[]
}

type ConfigObjectInput = {
  metadata?: Record<string, unknown>
  normalizedPayloadJson?: Record<string, unknown>
  parserMode?: string
  rawSourceText?: string
  schemaVersion?: string
}

function isRecord(value: unknown): value is Record<string, unknown> {
  return typeof value === "object" && value !== null
}

type AccessGrantWrite = {
  orgMembershipId?: MemberId
  orgWide?: boolean
  role: PluginArchRole
  teamId?: TeamId
}

type RepositorySummary = {
  defaultBranch: string | null
  fullName: string
  hasPluginManifest?: boolean
  id: number
  manifestKind?: "agent-plugin" | "marketplace" | "plugin" | null
  marketplacePluginCount?: number | null
  private: boolean
}

type ConfigObjectResourceTarget = {
  resourceId: ConfigObjectId
  resourceKind: "config_object"
}

type PluginResourceTarget = {
  resourceId: PluginId
  resourceKind: "plugin"
}

type MarketplaceResourceTarget = {
  resourceId: MarketplaceId
  resourceKind: "marketplace"
}

type ConnectorInstanceResourceTarget = {
  resourceId: ConnectorInstanceId
  resourceKind: "connector_instance"
}

type ResourceTarget =
  | ConfigObjectResourceTarget
  | MarketplaceResourceTarget
  | PluginResourceTarget
  | ConnectorInstanceResourceTarget

type ConfigObjectGrantTarget = ConfigObjectResourceTarget & { grantId: ConfigObjectAccessGrantId }
type MarketplaceGrantTarget = MarketplaceResourceTarget & { grantId: MarketplaceAccessGrantId }
type PluginGrantTarget = PluginResourceTarget & { grantId: PluginAccessGrantId }
type ConnectorInstanceGrantTarget = ConnectorInstanceResourceTarget & { grantId: ConnectorInstanceAccessGrantId }
type GrantTarget = ConfigObjectGrantTarget | MarketplaceGrantTarget | PluginGrantTarget | ConnectorInstanceGrantTarget

export class PluginArchRouteFailure extends Error {
  constructor(
    readonly status: 400 | 404 | 409 | 502,
    readonly error: string,
    message: string,
    options?: ErrorOptions,
  ) {
    super(message, options)
    this.name = "PluginArchRouteFailure"
  }
}

function normalizeOptionalString(value: string | null | undefined) {
  const trimmed = value?.trim()
  return trimmed ? trimmed : null
}

function firstTextLine(value: string) {
  return value
    .split(/\r?\n/g)
    .map((line) => line.trim())
    .filter(Boolean)[0] ?? ""
}

function stripLineDecorators(value: string) {
  return value
    .replace(/^#{1,6}\s+/, "")
    .replace(/^[-*+]\s+/, "")
    .replace(/^title\s*:\s*/i, "")
    .replace(/^description\s*:\s*/i, "")
    .trim()
}

function normalizeGithubPath(value: string) {
  return value.trim().replace(/^\.\//, "").replace(/^\/+/, "").replace(/\/+$/, "")
}

function parsePublicGithubPluginUrl(rawUrl: string): PublicGithubPluginTarget {
  let url: URL
  try {
    url = new URL(rawUrl)
  } catch {
    throw new PluginArchRouteFailure(400, "invalid_github_url", "Enter a valid GitHub URL.")
  }

  if (url.hostname !== "github.com" && url.hostname !== "www.github.com") {
    throw new PluginArchRouteFailure(400, "invalid_github_url", "Only github.com plugin URLs are supported.")
  }

  const segments = url.pathname.split("/").filter(Boolean)
  const [owner, rawRepo] = segments
  if (!owner || !rawRepo) {
    throw new PluginArchRouteFailure(400, "invalid_github_url", "GitHub URL must include an owner and repository.")
  }

  const repo = rawRepo.replace(/\.git$/i, "")
  if (!repo) {
    throw new PluginArchRouteFailure(400, "invalid_github_url", "GitHub URL must include a repository.")
  }

  if (segments.length === 2) {
    return {
      branch: null,
      repositoryFullName: `${owner}/${repo}`,
      rootPath: "",
    }
  }

  if (segments[2] !== "tree") {
    throw new PluginArchRouteFailure(400, "invalid_github_url", "Use a GitHub repository or tree URL, for example /tree/main/sales.")
  }

  const branch = segments[3]
  if (!branch) {
    throw new PluginArchRouteFailure(400, "invalid_github_url", "GitHub tree URL must include a branch.")
  }

  return {
    branch,
    repositoryFullName: `${owner}/${repo}`,
    rootPath: normalizeGithubPath(segments.slice(4).join("/")),
  }
}

async function requestPublicGithubJson(input: { path: string; allowStatuses?: number[] }) {
  const response = await fetch(`https://api.github.com${input.path}`, {
    headers: {
      Accept: "application/vnd.github+json",
      "User-Agent": "omnirush-den-api",
      "X-GitHub-Api-Version": "2022-11-28",
    },
  })
  const text = await response.text()
  const body: unknown = text ? JSON.parse(text) : null
  if (!response.ok && !(input.allowStatuses ?? []).includes(response.status)) {
    const message = isRecord(body) && typeof body.message === "string"
      ? body.message
      : `GitHub request failed with status ${response.status}.`
    throw new PluginArchRouteFailure(response.status === 404 ? 404 : 502, "github_request_failed", message)
  }
  return { body, ok: response.ok, status: response.status }
}

function publicGithubRepoParts(repositoryFullName: string) {
  const [owner, repo, ...rest] = repositoryFullName.split("/")
  if (!owner || !repo || rest.length > 0) {
    throw new PluginArchRouteFailure(400, "invalid_github_url", "GitHub repository name is invalid.")
  }
  return { owner, repo }
}

async function getPublicGithubDefaultBranch(repositoryFullName: string) {
  const { owner, repo } = publicGithubRepoParts(repositoryFullName)
  const response = await requestPublicGithubJson({
    path: `/repos/${encodeURIComponent(owner)}/${encodeURIComponent(repo)}`,
  })
  if (!isRecord(response.body) || typeof response.body.default_branch !== "string" || !response.body.default_branch.trim()) {
    throw new PluginArchRouteFailure(502, "github_response_incomplete", "GitHub repository response was missing the default branch.")
  }
  if (response.body.private === true) {
    throw new PluginArchRouteFailure(400, "private_github_repo", "Private GitHub repositories must be imported through the GitHub connector.")
  }
  return response.body.default_branch.trim()
}

async function getPublicGithubRepositoryTree(target: PublicGithubPluginTarget): Promise<PublicGithubTreeSnapshot> {
  const { owner, repo } = publicGithubRepoParts(target.repositoryFullName)
  const branch = target.branch ?? await getPublicGithubDefaultBranch(target.repositoryFullName)
  const commitResponse = await requestPublicGithubJson({
    path: `/repos/${encodeURIComponent(owner)}/${encodeURIComponent(repo)}/commits/${encodeURIComponent(branch)}`,
  })
  if (!isRecord(commitResponse.body)) {
    throw new PluginArchRouteFailure(502, "github_response_incomplete", "GitHub commit response was invalid.")
  }
  const headSha = typeof commitResponse.body.sha === "string" ? commitResponse.body.sha : ""
  const commit = isRecord(commitResponse.body.commit) ? commitResponse.body.commit : null
  const tree = commit && isRecord(commit.tree) ? commit.tree : null
  const treeSha = tree && typeof tree.sha === "string" ? tree.sha : ""
  if (!headSha || !treeSha) {
    throw new PluginArchRouteFailure(502, "github_response_incomplete", "GitHub commit response was missing the head or tree sha.")
  }

  const treeResponse = await requestPublicGithubJson({
    path: `/repos/${encodeURIComponent(owner)}/${encodeURIComponent(repo)}/git/trees/${encodeURIComponent(treeSha)}?recursive=1`,
  })
  if (!isRecord(treeResponse.body) || !Array.isArray(treeResponse.body.tree)) {
    throw new PluginArchRouteFailure(502, "github_response_incomplete", "GitHub tree response was invalid.")
  }

  const rootPath = normalizeGithubPath(target.rootPath)
  const fullPathByDiscoveryPath = new Map<string, string>()
  const treeEntries = treeResponse.body.tree.flatMap((entry): GithubDiscoveryTreeEntry[] => {
    if (!isRecord(entry)) return []
    const fullPath = typeof entry.path === "string" ? normalizeGithubPath(entry.path) : ""
    const kind = entry.type === "blob" || entry.type === "tree" ? entry.type : null
    if (!fullPath || !kind) return []
    if (rootPath && fullPath !== rootPath && !fullPath.startsWith(`${rootPath}/`)) return []
    const discoveryPath = rootPath
      ? (fullPath === rootPath ? "" : fullPath.slice(rootPath.length + 1))
      : fullPath
    if (!discoveryPath) return []
    fullPathByDiscoveryPath.set(discoveryPath, fullPath)
    return [{
      id: entry.sha === null || typeof entry.sha === "string" ? entry.sha ?? discoveryPath : discoveryPath,
      kind,
      path: discoveryPath,
      sha: entry.sha === null || typeof entry.sha === "string" ? entry.sha : null,
      size: typeof entry.size === "number" ? entry.size : null,
    }]
  })

  if (treeEntries.length === 0) {
    throw new PluginArchRouteFailure(404, "github_plugin_root_not_found", "No files were found at that GitHub plugin path.")
  }

  return {
    branch,
    fullPathByDiscoveryPath,
    headSha,
    repositoryFullName: target.repositoryFullName,
    rootPath,
    treeEntries,
    truncated: isRecord(treeResponse.body) && treeResponse.body.truncated === true,
  }
}

async function getPublicGithubTextFile(input: { branch: string; discoveryPath: string; snapshot: PublicGithubTreeSnapshot }) {
  const fullPath = input.snapshot.fullPathByDiscoveryPath.get(input.discoveryPath) ?? input.discoveryPath
  const { owner, repo } = publicGithubRepoParts(input.snapshot.repositoryFullName)
  const response = await requestPublicGithubJson({
    allowStatuses: [404],
    path: `/repos/${encodeURIComponent(owner)}/${encodeURIComponent(repo)}/contents/${fullPath.split("/").map(encodeURIComponent).join("/")}?ref=${encodeURIComponent(input.branch)}`,
  })
  if (!response.ok) return null
  if (!isRecord(response.body) || response.body.encoding !== "base64" || typeof response.body.content !== "string") {
    throw new PluginArchRouteFailure(502, "github_response_incomplete", "GitHub file response was incomplete.")
  }
  return Buffer.from(response.body.content.replace(/\n/g, ""), "base64").toString("utf8")
}

async function getPublicGithubDiscoveryFileTexts(snapshot: PublicGithubTreeSnapshot) {
  const interestingPaths = new Set<string>()
  const knownPaths = new Set(snapshot.treeEntries.map((entry) => entry.path))
  if (knownPaths.has(".claude-plugin/marketplace.json")) {
    interestingPaths.add(".claude-plugin/marketplace.json")
  }
  for (const entry of snapshot.treeEntries) {
    if (entry.path.endsWith(".claude-plugin/plugin.json") || entry.path.endsWith("/plugin.json") || entry.path === "plugin.json") {
      interestingPaths.add(entry.path)
    }
  }

  const fileTextByPath: Record<string, string | null> = {}
  for (const path of interestingPaths) {
    fileTextByPath[path] = await getPublicGithubTextFile({
      branch: snapshot.branch,
      discoveryPath: path,
      snapshot,
    })
  }
  return fileTextByPath
}

const STANDARD_SKILL_NAME_PATTERN = /^[a-z0-9]+(?:-[a-z0-9]+)*$/

function deriveSkillProjection(value: ConfigObjectInput) {
  const rawSourceText = normalizeOptionalString(value.rawSourceText)
  if (!rawSourceText) {
    throw new PluginArchRouteFailure(
      400,
      "invalid_skill_source",
      "Skill components require rawSourceText containing the complete SKILL.md.",
    )
  }

  const parsed = parseSkillMarkdown(rawSourceText)
  if (!parsed.hasFrontmatter) {
    throw new PluginArchRouteFailure(
      400,
      "invalid_skill_frontmatter",
      "SKILL.md must start with YAML frontmatter delimited by --- lines.",
    )
  }

  const name = parsed.name.trim()
  if (!name) {
    throw new PluginArchRouteFailure(
      400,
      "invalid_skill_name",
      "SKILL.md frontmatter requires a non-empty name.",
    )
  }
  if (name.length > 64 || !STANDARD_SKILL_NAME_PATTERN.test(name)) {
    throw new PluginArchRouteFailure(
      400,
      "invalid_skill_name",
      "SKILL.md frontmatter name must be 1-64 characters, contain only lowercase letters, numbers, and hyphens, and cannot start, end, or use consecutive hyphens.",
    )
  }

  const description = parsed.description.trim()
  if (!description) {
    throw new PluginArchRouteFailure(
      400,
      "invalid_skill_description",
      "SKILL.md frontmatter requires a non-empty description.",
    )
  }
  if (description.length > 1_024) {
    throw new PluginArchRouteFailure(
      400,
      "invalid_skill_description",
      "SKILL.md frontmatter description must be 1024 characters or fewer.",
    )
  }

  const body = parsed.body.trim()
  if (!body) {
    throw new PluginArchRouteFailure(
      400,
      "invalid_skill_body",
      "SKILL.md requires a non-empty Markdown instruction body after the frontmatter.",
    )
  }

  return {
    description,
    searchText: clampUtf8Bytes([name, description, body].join("\n"), PROJECTION_TEXT_MAX_BYTES),
    title: name,
  }
}

function deriveProjection(input: { objectType: ConfigObjectRow["objectType"]; value: ConfigObjectInput }) {
  if (input.objectType === "skill") {
    return deriveSkillProjection(input.value)
  }

  const metadata = input.value.metadata ?? {}
  const payload = input.value.normalizedPayloadJson ?? {}
  const rawSourceText = normalizeOptionalString(input.value.rawSourceText)
  const titleCandidate = [
    typeof metadata.title === "string" ? metadata.title : null,
    typeof metadata.name === "string" ? metadata.name : null,
    typeof payload.title === "string" ? payload.title : null,
    typeof payload.name === "string" ? payload.name : null,
    rawSourceText ? stripLineDecorators(firstTextLine(rawSourceText)) : null,
  ].find((value) => Boolean(normalizeOptionalString(value ?? undefined)))

  const descriptionCandidate = [
    typeof metadata.description === "string" ? metadata.description : null,
    typeof payload.description === "string" ? payload.description : null,
    rawSourceText
      ? rawSourceText
        .split(/\r?\n/g)
        .map((line) => stripLineDecorators(line.trim()))
        .filter(Boolean)
        .slice(1)
        .find(Boolean) ?? null
      : null,
  ].find((value) => Boolean(normalizeOptionalString(value ?? undefined)))

  const title = clampCodePoints(
    normalizeOptionalString(titleCandidate ?? undefined)
      ?? `${input.objectType.charAt(0).toUpperCase()}${input.objectType.slice(1)} ${new Date().toISOString()}`,
    PROJECTION_TITLE_MAX_CHARS,
  )

  const description = normalizeOptionalString(descriptionCandidate ?? undefined)
  const searchText = [title, description, rawSourceText].filter(Boolean).join("\n")

  return {
    description: description ? clampUtf8Bytes(description, PROJECTION_TEXT_MAX_BYTES) : null,
    searchText: searchText ? clampUtf8Bytes(searchText, PROJECTION_TEXT_MAX_BYTES) : null,
    title,
  }
}

function pageItems<TItem extends { id: string }>(items: TItem[], cursor: string | undefined, limit: number | undefined): CursorPage<TItem> {
  const ordered = [...items]
  const pageSize = limit ?? 50
  const startIndex = cursor ? Math.max(ordered.findIndex((item) => item.id === cursor) + 1, 0) : 0
  const sliced = ordered.slice(startIndex, startIndex + pageSize)
  const nextCursor = ordered.length > startIndex + pageSize ? sliced[sliced.length - 1]?.id ?? null : null
  return { items: sliced, nextCursor }
}

async function getLatestVersions(configObjectIds: ConfigObjectId[]) {
  if (configObjectIds.length === 0) {
    return new Map<string, ConfigObjectVersionRow>()
  }

  const rows = await db
    .select()
    .from(ConfigObjectVersionTable)
    .where(inArray(ConfigObjectVersionTable.configObjectId, configObjectIds))
    .orderBy(desc(ConfigObjectVersionTable.createdAt), desc(ConfigObjectVersionTable.id))

  const latestByObjectId = new Map<string, ConfigObjectVersionRow>()
  for (const row of rows) {
    if (!latestByObjectId.has(row.configObjectId)) {
      latestByObjectId.set(row.configObjectId, row)
    }
  }

  return latestByObjectId
}

function serializeVersion(row: ConfigObjectVersionRow) {
  // Workflow authoring data belongs to the role-aware Workflow management API.
  // Generic config-object reads must not bypass that boundary for viewers.
  const isCodemodeWorkflowVersion = row.schemaVersion === "codemode-script-v1"
  return {
    configObjectId: row.configObjectId,
    connectorSyncEventId: row.connectorSyncEventId,
    createdAt: row.createdAt.toISOString(),
    createdByOrgMembershipId: row.createdByOrgMembershipId,
    createdVia: row.createdVia,
    id: row.id,
    isDeletedVersion: row.isDeletedVersion,
    normalizedPayloadJson: isCodemodeWorkflowVersion
      ? redactWorkflowNormalizedPayloadAuthoringDetails(row.normalizedPayloadJson)
      : row.normalizedPayloadJson,
    rawSourceText: isCodemodeWorkflowVersion ? null : row.rawSourceText,
    schemaVersion: row.schemaVersion,
    sourceRevisionRef: row.sourceRevisionRef,
  }
}

function serializeConfigObject(row: ConfigObjectRow, latestVersion: ConfigObjectVersionRow | null) {
  return {
    connectorInstanceId: row.connectorInstanceId,
    createdAt: row.createdAt.toISOString(),
    createdByOrgMembershipId: row.createdByOrgMembershipId,
    currentFileExtension: row.currentFileExtension,
    currentFileName: row.currentFileName,
    currentRelativePath: row.currentRelativePath,
    deletedAt: row.deletedAt ? row.deletedAt.toISOString() : null,
    description: row.description,
    id: row.id,
    latestVersion: latestVersion ? serializeVersion(latestVersion) : null,
    objectType: row.objectType,
    organizationId: row.organizationId,
    searchText: row.searchText,
    sourceMode: row.sourceMode,
    status: row.status,
    title: row.title,
    updatedAt: row.updatedAt.toISOString(),
  }
}

type PluginMarketplaceSummary = {
  id: string
  name: string
}

const DEFAULT_OMNIRUSH_EXTENSION_MANIFESTS = [
  {
    schemaVersion: 1,
    id: "omnirush-browser",
    name: "OmniRush.ai Browser",
    description: "Automate the built-in browser panel that stays visible inside OmniRush.ai.",
    source: { format: "omnirush-builtin", origin: "builtin", trusted: true },
    icon: { src: "/omnirush-mark.svg" },
    composer: { prompt: "Use the OmniRush.ai Browser extension to " },
    setup: { instructions: "OmniRush.ai Browser is ready by default in desktop workspaces." },
    resources: [{ type: "opencode-plugin", id: "opencode-chrome-devtools", packageName: "opencode-chrome-devtools", required: true }],
    contributions: [
      { type: "settings-panel", ref: "omnirush.browser.settings", location: "settings-detail" },
      { type: "session-side-panel", ref: "omnirush.browser.panel", location: "session-right-pane" },
      { type: "composer-prompt", prompt: "Use the OmniRush.ai Browser extension to ", location: "composer" },
    ],
    enablement: [{ type: "toggle-enabled", ref: "omnirush-browser", label: "Enabled" }],
    lifecycle: { reload: ["plugins", "agents"], detection: ["plugin:opencode-chrome-devtools"] },
    defaultEnabled: true,
  },
  {
    schemaVersion: 1,
    id: "computer-use",
    name: "Computer Use",
    description: "Mac only: control Mac apps through semantic accessibility refs, screenshots, background-safe clicks, keyboard input, and strict mode.",
    source: { format: "omnirush-builtin", origin: "builtin", trusted: true },
    icon: { src: "/omnirush-mark.svg" },
    composer: { prompt: "Use Computer Use to " },
    setup: { instructions: "Computer Use is Mac only. Grant Accessibility and Screen Recording permissions, then connect the local MCP server in this workspace." },
    resources: [
      { type: "mcp", id: "computer-use-mcp", label: "Computer Use MCP", mcpServerName: "computer-use", command: ["npx", "-y", "@omnirush/handsfree", "mcp"], localCommandRef: "omnirush.computerUseMcp", required: true },
      { type: "native-binary", id: "computer-use-native", label: "macOS accessibility runtime", packageName: "@omnirush/handsfree", required: true },
    ],
    contributions: [
      { type: "setup-instructions", ref: "omnirush.computerUse.setup", location: "settings-detail" },
      { type: "composer-prompt", prompt: "Use Computer Use to ", location: "composer" },
    ],
    enablement: [
      { type: "mcp-connected", ref: "computer-use", label: "MCP server connected" },
      { type: "permission-granted", ref: "accessibility", label: "Accessibility permission" },
      { type: "permission-granted", ref: "screenRecording", label: "Screen Recording permission" },
    ],
    lifecycle: { reload: ["mcp"], detection: ["mcp:computer-use"] },
    platform: ["darwin"],
  },
  {
    schemaVersion: 1,
    id: "openai-image-gen",
    name: "OpenAI Image Gen",
    description: "Generate image artifacts with gpt-image-2.",
    source: { format: "omnirush-builtin", origin: "builtin", trusted: true },
    icon: { src: "/ext-openai.svg" },
    composer: { prompt: "Use the OpenAI Image Gen extension to " },
    setup: { instructions: "Add an OpenAI API key, then agents can generate image artifacts through OmniRush.ai extension actions." },
    resources: [
      { type: "secret", id: "openai-api-key", envKey: "OPENAI_API_KEY", required: true },
      { type: "local-service", id: "openai-image-generation-service", label: "OpenAI image generation", required: true },
      { type: "tool", id: "openai-image-generate", label: "Image generation", required: true },
    ],
    contributions: [
      { type: "settings-panel", ref: "omnirush.imageGen.settings", location: "settings-detail" },
      { type: "composer-prompt", prompt: "Use the OpenAI Image Gen extension to ", location: "composer" },
    ],
    enablement: [{ type: "env-set", ref: "OPENAI_API_KEY", label: "OpenAI API key" }],
    lifecycle: { reload: ["config"], detection: ["env:OPENAI_API_KEY"] },
  },
  {
    schemaVersion: 1,
    id: "google-workspace",
    name: "Google Workspace",
    description: "Let OmniRush.ai help with meetings, selected Drive files, and Gmail drafts.",
    source: { format: "omnirush-builtin", origin: "builtin", trusted: true },
    icon: { simpleIconSlug: "google" },
    composer: { prompt: "Use Google Workspace to " },
    setup: { instructions: "Connect your Google account to use Calendar, Drive, and Gmail drafts in OmniRush.ai." },
    resources: [
      { type: "provider", id: "google-oauth", label: "Google account", providerId: "google-workspace", required: true },
      { type: "local-service", id: "google-workspace-connector", label: "Secure local connection", required: true },
      { type: "tool", id: "google-calendar-read", label: "Calendar", required: true },
      { type: "tool", id: "google-gmail-drafts", label: "Gmail drafts", required: true },
      { type: "tool", id: "google-drive-selected-files", label: "Selected Drive files", required: true },
      { type: "tool", id: "google-gmail-read", label: "Gmail read (opt-in)", required: false },
      { type: "tool", id: "google-drive-full", label: "Full Drive access (opt-in)", required: false },
      { type: "tool", id: "google-calendar-events", label: "Calendar events (opt-in)", required: false },
      { type: "tool", id: "google-chat", label: "Google Chat (opt-in)", required: false },
    ],
    contributions: [
      { type: "settings-panel", ref: "omnirush.googleWorkspace.settings", location: "settings-detail" },
      { type: "composer-prompt", prompt: "Use Google Workspace to ", location: "composer" },
    ],
    lifecycle: { reload: ["config"], detection: ["provider:google-workspace"] },
  },
  {
    schemaVersion: 1,
    id: "ollama",
    name: "Ollama",
    description: "Local model provider at http://localhost:11434.",
    source: { format: "omnirush-builtin", origin: "builtin", trusted: true },
    icon: { src: "/ext-ollama.svg" },
    composer: { prompt: "Use the Ollama extension to " },
    setup: { instructions: "Run Ollama locally, choose or pull a model, then add it as an OpenCode provider." },
    resources: [
      { type: "local-service", id: "ollama-api", label: "Ollama API", description: "http://localhost:11434", required: true },
      { type: "provider", id: "ollama", providerId: "ollama", packageName: "@ai-sdk/openai-compatible", required: true },
    ],
    contributions: [
      { type: "settings-panel", ref: "omnirush.ollama.settings", location: "settings-detail" },
      { type: "composer-prompt", prompt: "Use the Ollama extension to ", location: "composer" },
    ],
    enablement: [{ type: "provider-connected", ref: "ollama", label: "Ollama provider" }],
    lifecycle: { reload: ["config"], detection: ["provider:ollama"] },
  },
] as const

function defaultOmniRushManifestForPlugin(row: PluginRow) {
  return DEFAULT_OMNIRUSH_EXTENSION_MANIFESTS.find((manifest) => manifest.name === row.name && manifest.description === row.description) ?? null
}

function extensionResourceTypeForConfigObject(objectType: string) {
  switch (objectType) {
    case "skill":
    case "agent":
    case "command":
    case "tool":
    case "mcp":
    case "hook":
    case "context":
      return objectType
    default:
      return "file"
  }
}

function serializedPluginSourceFormat(row: PluginRow) {
  switch (row.sourceFormat) {
    case "agent-plugin":
    case "claude-plugin":
    case "manual":
    case "mcp-directory":
    case "opencode-plugin":
    case "omnirush-extension-manifest":
      return row.sourceFormat
    default:
      return "claude-plugin" as const
  }
}

function serializePluginExtension(row: PluginRow, componentCounts: Record<string, number>) {
  const builtInManifest = defaultOmniRushManifestForPlugin(row)
  if (builtInManifest) {
    return {
      description: builtInManifest.description,
      id: builtInManifest.id,
      manifest: builtInManifest,
      name: builtInManifest.name,
      sourceFormat: "omnirush-builtin",
    }
  }

  const sourceFormat = serializedPluginSourceFormat(row)
  const agentPlugin = sourceFormat === "agent-plugin"
  const description = row.description?.trim() || `${row.name} extension`
  const resources = Object.entries(componentCounts).flatMap(([objectType, count]) => {
    if (count <= 0) return []
    const resourceType = extensionResourceTypeForConfigObject(objectType)
    return [{
      type: resourceType,
      id: `${row.id}:${objectType}`,
      label: `${count} ${objectType}${count === 1 ? "" : "s"}`,
      required: true,
    }]
  })
  return {
    description: row.description,
    id: row.id,
    manifest: {
      schemaVersion: 1,
      id: row.id,
      name: row.name,
      description,
      source: {
        format: sourceFormat,
        origin: "den" as const,
        reference: row.id,
        trusted: false,
      },
      resources,
      contributions: [{
        type: "setup-instructions",
        ref: agentPlugin ? "den.agentPlugin.setup" : "den.claudePlugin.setup",
        label: agentPlugin ? "Agent Plugin import" : "Claude-compatible plugin import",
        location: "settings-detail",
      }],
      setup: {
        instructions: agentPlugin
          ? `Imported from Agent Plugins ${row.sourceSchemaVersion ?? AGENT_PLUGIN_V1_VERSION}. OmniRush.ai installs supported skills and remote MCP resources into this workspace.`
          : "Imported from a Claude-compatible plugin. OmniRush.ai installs its resources into this workspace as extension components.",
      },
      lifecycle: {
        detection: Object.keys(componentCounts).map((objectType) => `${objectType}:${row.id}`),
      },
    },
    name: row.name,
    sourceFormat,
  }
}

function serializePlugin(row: PluginRow, memberCount?: number, marketplaces: PluginMarketplaceSummary[] = [], componentCounts: Record<string, number> = {}) {
  return {
    createdAt: row.createdAt.toISOString(),
    createdByOrgMembershipId: row.createdByOrgMembershipId,
    deletedAt: row.deletedAt ? row.deletedAt.toISOString() : null,
    description: row.description,
    id: row.id,
    extension: serializePluginExtension(row, componentCounts),
    marketplaces,
    memberCount,
    name: row.name,
    organizationId: row.organizationId,
    sourceRepositoryUrl: row.sourceRepositoryUrl,
    sourceFormat: row.sourceFormat ?? null,
    sourceSchemaVersion: row.sourceSchemaVersion ?? null,
    status: row.status,
    updatedAt: row.updatedAt.toISOString(),
  }
}

function serializeMarketplace(row: MarketplaceRow, pluginCount?: number) {
  return {
    createdAt: row.createdAt.toISOString(),
    createdByOrgMembershipId: row.createdByOrgMembershipId,
    deletedAt: row.deletedAt ? row.deletedAt.toISOString() : null,
    description: row.description,
    id: row.id,
    externalKey: row.externalKey,
    logoUrl: row.logoUrl,
    name: row.name,
    organizationId: row.organizationId,
    pluginCount,
    status: row.status,
    updatedAt: row.updatedAt.toISOString(),
  }
}

function serializeMembership(row: PluginMembershipRow, configObject?: ReturnType<typeof serializeConfigObject>) {
  return {
    configObject,
    configObjectId: row.configObjectId,
    connectorMappingId: row.connectorMappingId,
    createdAt: row.createdAt.toISOString(),
    createdByOrgMembershipId: row.createdByOrgMembershipId,
    id: row.id,
    membershipSource: row.membershipSource,
    pluginId: row.pluginId,
    removedAt: row.removedAt ? row.removedAt.toISOString() : null,
  }
}

function serializeMarketplaceMembership(row: MarketplaceMembershipRow, plugin?: ReturnType<typeof serializePlugin>) {
  return {
    createdAt: row.createdAt.toISOString(),
    createdByOrgMembershipId: row.createdByOrgMembershipId,
    id: row.id,
    marketplaceId: row.marketplaceId,
    membershipSource: row.membershipSource,
    plugin,
    pluginId: row.pluginId,
    removedAt: row.removedAt ? row.removedAt.toISOString() : null,
  }
}

function serializeAccessGrant(row: AccessGrantRow) {
  return {
    createdAt: row.createdAt.toISOString(),
    createdByOrgMembershipId: row.createdByOrgMembershipId,
    id: row.id,
    orgMembershipId: row.orgMembershipId,
    orgWide: row.orgWide,
    removedAt: row.removedAt ? row.removedAt.toISOString() : null,
    role: row.role,
    teamId: row.teamId,
  }
}

function serializeConnectorAccount(row: ConnectorAccountRow, creatorName: string | null = null) {
  return {
    connectorType: row.connectorType,
    createdAt: row.createdAt.toISOString(),
    createdByName: creatorName,
    createdByOrgMembershipId: row.createdByOrgMembershipId,
    displayName: row.displayName,
    externalAccountRef: row.externalAccountRef,
    id: row.id,
    metadata: row.metadataJson ?? undefined,
    organizationId: row.organizationId,
    remoteId: row.remoteId,
    status: row.status,
    updatedAt: row.updatedAt.toISOString(),
  }
}

function resolveCreatorName(context: PluginArchActorContext, memberId: string) {
  const member = context.organizationContext.members.find((entry) => entry.id === memberId)
  if (!member) return null
  return member.user.name?.trim() || member.user.email || null
}

function serializeConnectorInstance(row: ConnectorInstanceRow) {
  return {
    connectorAccountId: row.connectorAccountId,
    connectorType: row.connectorType,
    createdAt: row.createdAt.toISOString(),
    createdByOrgMembershipId: row.createdByOrgMembershipId,
    id: row.id,
    instanceConfigJson: row.instanceConfigJson,
    lastSyncCursor: row.lastSyncCursor,
    lastSyncStatus: row.lastSyncStatus,
    lastSyncedAt: row.lastSyncedAt ? row.lastSyncedAt.toISOString() : null,
    name: row.name,
    organizationId: row.organizationId,
    remoteId: row.remoteId,
    status: row.status,
    updatedAt: row.updatedAt.toISOString(),
  }
}

function serializeConnectorTarget(row: ConnectorTargetRow) {
  return {
    connectorInstanceId: row.connectorInstanceId,
    connectorType: row.connectorType,
    createdAt: row.createdAt.toISOString(),
    externalTargetRef: row.externalTargetRef,
    id: row.id,
    remoteId: row.remoteId,
    targetConfigJson: row.targetConfigJson,
    targetKind: row.targetKind,
    updatedAt: row.updatedAt.toISOString(),
  }
}

function serializeConnectorMapping(row: ConnectorMappingRow) {
  return {
    autoAddToPlugin: row.autoAddToPlugin,
    connectorInstanceId: row.connectorInstanceId,
    connectorTargetId: row.connectorTargetId,
    connectorType: row.connectorType,
    createdAt: row.createdAt.toISOString(),
    id: row.id,
    mappingConfigJson: row.mappingConfigJson,
    mappingKind: row.mappingKind,
    objectType: row.objectType,
    pluginId: row.pluginId,
    remoteId: row.remoteId,
    selector: row.selector,
    updatedAt: row.updatedAt.toISOString(),
  }
}

function serializeConnectorSyncEvent(row: ConnectorSyncEventRow) {
  return {
    attemptCount: row.attemptCount,
    completedAt: row.completedAt ? row.completedAt.toISOString() : null,
    connectorInstanceId: row.connectorInstanceId,
    connectorTargetId: row.connectorTargetId,
    connectorType: row.connectorType,
    eventType: row.eventType,
    externalEventRef: row.externalEventRef,
    id: row.id,
    nextAttemptAt: row.nextAttemptAt ? row.nextAttemptAt.toISOString() : null,
    remoteId: row.remoteId,
    sourceRevisionRef: row.sourceRevisionRef,
    startedAt: row.startedAt.toISOString(),
    status: row.status,
    summaryJson: row.summaryJson,
  }
}

async function getConfigObjectRow(organizationId: OrganizationId, configObjectId: ConfigObjectId) {
  const rows = await db
    .select()
    .from(ConfigObjectTable)
    .where(and(eq(ConfigObjectTable.organizationId, organizationId), eq(ConfigObjectTable.id, configObjectId)))
    .limit(1)

  return rows[0] ?? null
}

async function getPluginRow(organizationId: OrganizationId, pluginId: PluginId) {
  const rows = await db
    .select()
    .from(PluginTable)
    .where(and(eq(PluginTable.organizationId, organizationId), eq(PluginTable.id, pluginId)))
    .limit(1)

  return rows[0] ?? null
}

export async function findMarketplaceByExternalKey(context: PluginArchActorContext, externalKey: string) {
  const [row] = await db.select().from(MarketplaceTable).where(and(
    eq(MarketplaceTable.organizationId, context.organizationContext.organization.id),
    eq(MarketplaceTable.externalKey, externalKey),
  )).limit(1)
  return row
}

async function getMarketplaceRow(organizationId: OrganizationId, marketplaceId: MarketplaceId) {
  const rows = await db
    .select()
    .from(MarketplaceTable)
    .where(and(eq(MarketplaceTable.organizationId, organizationId), eq(MarketplaceTable.id, marketplaceId)))
    .limit(1)

  return rows[0] ?? null
}

async function getConnectorAccountRow(organizationId: OrganizationId, connectorAccountId: ConnectorAccountId) {
  const rows = await db
    .select()
    .from(ConnectorAccountTable)
    .where(and(eq(ConnectorAccountTable.organizationId, organizationId), eq(ConnectorAccountTable.id, connectorAccountId)))
    .limit(1)

  return rows[0] ?? null
}

async function getConnectorInstanceRow(organizationId: OrganizationId, connectorInstanceId: ConnectorInstanceId) {
  const rows = await db
    .select()
    .from(ConnectorInstanceTable)
    .where(and(eq(ConnectorInstanceTable.organizationId, organizationId), eq(ConnectorInstanceTable.id, connectorInstanceId)))
    .limit(1)

  return rows[0] ?? null
}

async function getConnectorTargetRow(organizationId: OrganizationId, connectorTargetId: ConnectorTargetId) {
  const rows = await db
    .select({ target: ConnectorTargetTable, instance: ConnectorInstanceTable })
    .from(ConnectorTargetTable)
    .innerJoin(ConnectorInstanceTable, eq(ConnectorTargetTable.connectorInstanceId, ConnectorInstanceTable.id))
    .where(and(eq(ConnectorTargetTable.id, connectorTargetId), eq(ConnectorInstanceTable.organizationId, organizationId)))
    .limit(1)

  return rows[0]?.target ?? null
}

async function getConnectorMappingRow(organizationId: OrganizationId, connectorMappingId: ConnectorMappingId) {
  const rows = await db
    .select({ mapping: ConnectorMappingTable, instance: ConnectorInstanceTable })
    .from(ConnectorMappingTable)
    .innerJoin(ConnectorInstanceTable, eq(ConnectorMappingTable.connectorInstanceId, ConnectorInstanceTable.id))
    .where(and(eq(ConnectorMappingTable.id, connectorMappingId), eq(ConnectorInstanceTable.organizationId, organizationId)))
    .limit(1)

  return rows[0]?.mapping ?? null
}

async function getConnectorSyncEventRow(organizationId: OrganizationId, connectorSyncEventId: ConnectorSyncEventId) {
  const rows = await db
    .select({ event: ConnectorSyncEventTable, instance: ConnectorInstanceTable })
    .from(ConnectorSyncEventTable)
    .innerJoin(ConnectorInstanceTable, eq(ConnectorSyncEventTable.connectorInstanceId, ConnectorInstanceTable.id))
    .where(and(eq(ConnectorSyncEventTable.id, connectorSyncEventId), eq(ConnectorInstanceTable.organizationId, organizationId)))
    .limit(1)

  return rows[0]?.event ?? null
}

// Verifies the target resource exists AND belongs to the caller's active
// organization, returning 404 otherwise. This must run before any role check on
// access-grant endpoints: resolvePluginArchResourceRole short-circuits to
// "manager" for org admins without binding the resource to the org, so without
// this guard an admin in org A could read/add/revoke grants on org B resources
// by supplying a foreign resourceId.
async function ensureResourceInOrganization(context: PluginArchActorContext, target: ResourceTarget) {
  const organizationId = context.organizationContext.organization.id
  if (target.resourceKind === "config_object") {
    if (!(await getConfigObjectRow(organizationId, target.resourceId))) {
      throw new PluginArchRouteFailure(404, "config_object_not_found", "Config object not found.")
    }
    return
  }
  if (target.resourceKind === "plugin") {
    if (!(await getPluginRow(organizationId, target.resourceId))) {
      throw new PluginArchRouteFailure(404, "plugin_not_found", "Plugin not found.")
    }
    return
  }
  if (target.resourceKind === "marketplace") {
    if (!(await getMarketplaceRow(organizationId, target.resourceId))) {
      throw new PluginArchRouteFailure(404, "marketplace_not_found", "Marketplace not found.")
    }
    return
  }
  if (!(await getConnectorInstanceRow(organizationId, target.resourceId))) {
    throw new PluginArchRouteFailure(404, "connector_instance_not_found", "Connector instance not found.")
  }
}

// Validates that a grant's target member/team belong to the caller's active
// organization, so a manager cannot grant access to a foreign org's member or
// team id by smuggling it through the request body.
async function ensureGrantTargetsInOrganization(context: PluginArchActorContext, value: AccessGrantWrite) {
  const organizationId = context.organizationContext.organization.id

  if (value.orgMembershipId) {
    const member = await db
      .select({ id: MemberTable.id })
      .from(MemberTable)
      .where(and(eq(MemberTable.organizationId, organizationId), eq(MemberTable.id, value.orgMembershipId)))
      .limit(1)
    if (!member[0]) {
      throw new PluginArchRouteFailure(404, "member_not_found", "Member not found.")
    }
  }

  if (value.teamId) {
    const team = await db
      .select({ id: TeamTable.id })
      .from(TeamTable)
      .where(and(eq(TeamTable.organizationId, organizationId), eq(TeamTable.id, value.teamId)))
      .limit(1)
    if (!team[0]) {
      throw new PluginArchRouteFailure(404, "team_not_found", "Team not found.")
    }
  }
}

async function ensureVisibleConfigObject(context: PluginArchActorContext, configObjectId: ConfigObjectId) {
  const row = await getConfigObjectRow(context.organizationContext.organization.id, configObjectId)
  if (!row) {
    throw new PluginArchRouteFailure(404, "config_object_not_found", "Config object not found.")
  }
  await requirePluginArchResourceRole({ context, resourceId: row.id, resourceKind: "config_object", role: "viewer" })
  return row
}

async function ensureEditablePlugin(
  context: PluginArchActorContext,
  pluginId: PluginId,
  requireFreshSession?: boolean,
  sessionMaxAgeMs?: number,
) {
  const row = await getPluginRow(context.organizationContext.organization.id, pluginId)
  if (!row) {
    throw new PluginArchRouteFailure(404, "plugin_not_found", "Plugin not found.")
  }
  await requirePluginArchResourceRole({
    context,
    requireFreshSession,
    sessionMaxAgeMs,
    resourceId: row.id,
    resourceKind: "plugin",
    role: "editor",
  })
  return row
}

async function ensureEditableMarketplace(context: PluginArchActorContext, marketplaceId: MarketplaceId) {
  const row = await getMarketplaceRow(context.organizationContext.organization.id, marketplaceId)
  if (!row) {
    throw new PluginArchRouteFailure(404, "marketplace_not_found", "Marketplace not found.")
  }
  await requirePluginArchResourceRole({ context, resourceId: row.id, resourceKind: "marketplace", role: "editor" })
  return row
}

async function ensureVisibleMarketplace(context: PluginArchActorContext, marketplaceId: MarketplaceId) {
  const row = await getMarketplaceRow(context.organizationContext.organization.id, marketplaceId)
  if (!row) {
    throw new PluginArchRouteFailure(404, "marketplace_not_found", "Marketplace not found.")
  }
  await requirePluginArchResourceRole({ context, resourceId: row.id, resourceKind: "marketplace", role: "viewer" })
  return row
}

async function ensureVisiblePlugin(context: PluginArchActorContext, pluginId: PluginId) {
  const row = await getPluginRow(context.organizationContext.organization.id, pluginId)
  if (!row) {
    throw new PluginArchRouteFailure(404, "plugin_not_found", "Plugin not found.")
  }
  await requirePluginArchResourceRole({ context, resourceId: row.id, resourceKind: "plugin", role: "viewer" })
  return row
}

async function ensureVisibleConnectorInstance(context: PluginArchActorContext, connectorInstanceId: ConnectorInstanceId) {
  const row = await getConnectorInstanceRow(context.organizationContext.organization.id, connectorInstanceId)
  if (!row) {
    throw new PluginArchRouteFailure(404, "connector_instance_not_found", "Connector instance not found.")
  }
  await requirePluginArchResourceRole({ context, resourceId: row.id, resourceKind: "connector_instance", role: "viewer" })
  return row
}

async function ensureEditableConnectorInstance(context: PluginArchActorContext, connectorInstanceId: ConnectorInstanceId) {
  const row = await getConnectorInstanceRow(context.organizationContext.organization.id, connectorInstanceId)
  if (!row) {
    throw new PluginArchRouteFailure(404, "connector_instance_not_found", "Connector instance not found.")
  }
  await requirePluginArchResourceRole({ context, resourceId: row.id, resourceKind: "connector_instance", role: "editor" })
  return row
}

async function upsertGrant(input: ResourceTarget & {
  context: PluginArchActorContext
  value: AccessGrantWrite
}) {
  const createdAt = new Date()
  const createdByOrgMembershipId = input.context.organizationContext.currentMember.id
  const organizationId = input.context.organizationContext.organization.id

  if (input.resourceKind === "config_object") {
    const existing = await db
      .select()
      .from(ConfigObjectAccessGrantTable)
      .where(and(
        eq(ConfigObjectAccessGrantTable.configObjectId, input.resourceId),
        input.value.orgMembershipId
          ? eq(ConfigObjectAccessGrantTable.orgMembershipId, input.value.orgMembershipId)
          : input.value.teamId
            ? eq(ConfigObjectAccessGrantTable.teamId, input.value.teamId)
            : eq(ConfigObjectAccessGrantTable.orgWide, true),
      ))
      .limit(1)

    if (existing[0]) {
      await db
        .update(ConfigObjectAccessGrantTable)
        .set({
          createdByOrgMembershipId,
          orgMembershipId: input.value.orgMembershipId ?? null,
          orgWide: input.value.orgWide ?? false,
          removedAt: null,
          role: input.value.role,
          teamId: input.value.teamId ?? null,
        })
        .where(eq(ConfigObjectAccessGrantTable.id, existing[0].id))
      return serializeAccessGrant({ ...existing[0], createdByOrgMembershipId, orgMembershipId: input.value.orgMembershipId ?? null, orgWide: input.value.orgWide ?? false, removedAt: null, role: input.value.role, teamId: input.value.teamId ?? null })
    }

    const row = {
      configObjectId: input.resourceId,
      createdAt,
      createdByOrgMembershipId,
      id: createDenTypeId("configObjectAccessGrant"),
      organizationId,
      orgMembershipId: input.value.orgMembershipId ?? null,
      orgWide: input.value.orgWide ?? false,
      role: input.value.role,
      teamId: input.value.teamId ?? null,
    }
    await db.insert(ConfigObjectAccessGrantTable).values(row)
    return serializeAccessGrant({ ...row, removedAt: null })
  }

  if (input.resourceKind === "marketplace") {
    const existing = await db
      .select()
      .from(MarketplaceAccessGrantTable)
      .where(and(
        eq(MarketplaceAccessGrantTable.marketplaceId, input.resourceId),
        input.value.orgMembershipId
          ? eq(MarketplaceAccessGrantTable.orgMembershipId, input.value.orgMembershipId)
          : input.value.teamId
            ? eq(MarketplaceAccessGrantTable.teamId, input.value.teamId)
            : eq(MarketplaceAccessGrantTable.orgWide, true),
      ))
      .limit(1)

    if (existing[0]) {
      await db
        .update(MarketplaceAccessGrantTable)
        .set({
          createdByOrgMembershipId,
          orgMembershipId: input.value.orgMembershipId ?? null,
          orgWide: input.value.orgWide ?? false,
          removedAt: null,
          role: input.value.role,
          teamId: input.value.teamId ?? null,
        })
        .where(eq(MarketplaceAccessGrantTable.id, existing[0].id))
      return serializeAccessGrant({ ...existing[0], createdByOrgMembershipId, orgMembershipId: input.value.orgMembershipId ?? null, orgWide: input.value.orgWide ?? false, removedAt: null, role: input.value.role, teamId: input.value.teamId ?? null })
    }

    const row = {
      createdAt,
      createdByOrgMembershipId,
      id: createDenTypeId("marketplaceAccessGrant"),
      marketplaceId: input.resourceId,
      organizationId,
      orgMembershipId: input.value.orgMembershipId ?? null,
      orgWide: input.value.orgWide ?? false,
      role: input.value.role,
      teamId: input.value.teamId ?? null,
    }
    await db.insert(MarketplaceAccessGrantTable).values(row)
    return serializeAccessGrant({ ...row, removedAt: null })
  }

  if (input.resourceKind === "plugin") {
    const existing = await db
      .select()
      .from(PluginAccessGrantTable)
      .where(and(
        eq(PluginAccessGrantTable.pluginId, input.resourceId),
        input.value.orgMembershipId
          ? eq(PluginAccessGrantTable.orgMembershipId, input.value.orgMembershipId)
          : input.value.teamId
            ? eq(PluginAccessGrantTable.teamId, input.value.teamId)
            : eq(PluginAccessGrantTable.orgWide, true),
      ))
      .limit(1)

    if (existing[0]) {
      await db
        .update(PluginAccessGrantTable)
        .set({
          createdByOrgMembershipId,
          orgMembershipId: input.value.orgMembershipId ?? null,
          orgWide: input.value.orgWide ?? false,
          removedAt: null,
          role: input.value.role,
          teamId: input.value.teamId ?? null,
        })
        .where(eq(PluginAccessGrantTable.id, existing[0].id))
      return serializeAccessGrant({ ...existing[0], createdByOrgMembershipId, orgMembershipId: input.value.orgMembershipId ?? null, orgWide: input.value.orgWide ?? false, removedAt: null, role: input.value.role, teamId: input.value.teamId ?? null })
    }

    const row = {
      createdAt,
      createdByOrgMembershipId,
      id: createDenTypeId("pluginAccessGrant"),
      organizationId,
      orgMembershipId: input.value.orgMembershipId ?? null,
      orgWide: input.value.orgWide ?? false,
      pluginId: input.resourceId,
      role: input.value.role,
      teamId: input.value.teamId ?? null,
    }
    await db.insert(PluginAccessGrantTable).values(row)
    return serializeAccessGrant({ ...row, removedAt: null })
  }

  const existing = await db
    .select()
    .from(ConnectorInstanceAccessGrantTable)
    .where(and(
      eq(ConnectorInstanceAccessGrantTable.connectorInstanceId, input.resourceId),
      input.value.orgMembershipId
        ? eq(ConnectorInstanceAccessGrantTable.orgMembershipId, input.value.orgMembershipId)
        : input.value.teamId
          ? eq(ConnectorInstanceAccessGrantTable.teamId, input.value.teamId)
          : eq(ConnectorInstanceAccessGrantTable.orgWide, true),
    ))
    .limit(1)

  if (existing[0]) {
    await db
      .update(ConnectorInstanceAccessGrantTable)
      .set({
        createdByOrgMembershipId,
        orgMembershipId: input.value.orgMembershipId ?? null,
        orgWide: input.value.orgWide ?? false,
        removedAt: null,
        role: input.value.role,
        teamId: input.value.teamId ?? null,
      })
      .where(eq(ConnectorInstanceAccessGrantTable.id, existing[0].id))
    return serializeAccessGrant({ ...existing[0], createdByOrgMembershipId, orgMembershipId: input.value.orgMembershipId ?? null, orgWide: input.value.orgWide ?? false, removedAt: null, role: input.value.role, teamId: input.value.teamId ?? null })
  }

  const row = {
    connectorInstanceId: input.resourceId,
    createdAt,
    createdByOrgMembershipId,
    id: createDenTypeId("connectorInstanceAccessGrant"),
    organizationId,
    orgMembershipId: input.value.orgMembershipId ?? null,
    orgWide: input.value.orgWide ?? false,
    role: input.value.role,
    teamId: input.value.teamId ?? null,
  }
  await db.insert(ConnectorInstanceAccessGrantTable).values(row)
  return serializeAccessGrant({ ...row, removedAt: null })
}

async function removeGrant(input: GrantTarget & { context: PluginArchActorContext }) {
  const removedAt = new Date()
  if (input.resourceKind === "config_object") {
    const rows = await db
      .select()
      .from(ConfigObjectAccessGrantTable)
      .where(and(eq(ConfigObjectAccessGrantTable.id, input.grantId), eq(ConfigObjectAccessGrantTable.configObjectId, input.resourceId)))
      .limit(1)
    if (!rows[0]) throw new PluginArchRouteFailure(404, "access_grant_not_found", "Access grant not found.")
    await db.update(ConfigObjectAccessGrantTable).set({ removedAt }).where(eq(ConfigObjectAccessGrantTable.id, input.grantId))
    return
  }
  if (input.resourceKind === "marketplace") {
    const rows = await db
      .select()
      .from(MarketplaceAccessGrantTable)
      .where(and(eq(MarketplaceAccessGrantTable.id, input.grantId), eq(MarketplaceAccessGrantTable.marketplaceId, input.resourceId)))
      .limit(1)
    if (!rows[0]) throw new PluginArchRouteFailure(404, "access_grant_not_found", "Access grant not found.")
    await db.update(MarketplaceAccessGrantTable).set({ removedAt }).where(eq(MarketplaceAccessGrantTable.id, input.grantId))
    return
  }
  if (input.resourceKind === "plugin") {
    const rows = await db
      .select()
      .from(PluginAccessGrantTable)
      .where(and(eq(PluginAccessGrantTable.id, input.grantId), eq(PluginAccessGrantTable.pluginId, input.resourceId)))
      .limit(1)
    if (!rows[0]) throw new PluginArchRouteFailure(404, "access_grant_not_found", "Access grant not found.")
    await db.update(PluginAccessGrantTable).set({ removedAt }).where(eq(PluginAccessGrantTable.id, input.grantId))
    return
  }
  const rows = await db
    .select()
    .from(ConnectorInstanceAccessGrantTable)
    .where(and(eq(ConnectorInstanceAccessGrantTable.id, input.grantId), eq(ConnectorInstanceAccessGrantTable.connectorInstanceId, input.resourceId)))
    .limit(1)
  if (!rows[0]) throw new PluginArchRouteFailure(404, "access_grant_not_found", "Access grant not found.")
  await db.update(ConnectorInstanceAccessGrantTable).set({ removedAt }).where(eq(ConnectorInstanceAccessGrantTable.id, input.grantId))
}

export async function listConfigObjects(input: {
  connectorInstanceId?: ConnectorInstanceId
  context: PluginArchActorContext
  cursor?: string
  includeDeleted?: boolean
  limit?: number
  pluginId?: PluginId
  q?: string
  sourceMode?: ConfigObjectRow["sourceMode"]
  status?: ConfigObjectRow["status"]
  type?: ConfigObjectRow["objectType"]
}) {
  const organizationId = input.context.organizationContext.organization.id
  if (input.connectorInstanceId) {
    await ensureVisibleConnectorInstance(input.context, input.connectorInstanceId)
  }
  if (input.pluginId) {
    await ensureVisiblePlugin(input.context, input.pluginId)
  }

  const rows = await db
    .select()
    .from(ConfigObjectTable)
    .where(eq(ConfigObjectTable.organizationId, organizationId))
    .orderBy(desc(ConfigObjectTable.updatedAt), desc(ConfigObjectTable.id))

  const latestVersions = await getLatestVersions(rows.map((row) => row.id))
  const filtered: ReturnType<typeof serializeConfigObject>[] = []

  for (const row of rows) {
    const role = await resolvePluginArchResourceRole({ context: input.context, resourceId: row.id, resourceKind: "config_object" })
    if (!role) continue
    if (input.type && row.objectType !== input.type) continue
    if (input.status && row.status !== input.status) continue
    if (input.sourceMode && row.sourceMode !== input.sourceMode) continue
    if (!input.includeDeleted && row.status === "deleted") continue
    if (input.connectorInstanceId && row.connectorInstanceId !== input.connectorInstanceId) continue
    if (input.q) {
      const haystack = `${row.title}\n${row.description ?? ""}\n${row.searchText ?? ""}`.toLowerCase()
      if (!haystack.includes(input.q.toLowerCase())) continue
    }
    if (input.pluginId) {
      const memberships = await db
        .select({ id: PluginConfigObjectTable.id })
        .from(PluginConfigObjectTable)
        .where(and(
          eq(PluginConfigObjectTable.organizationId, organizationId),
          eq(PluginConfigObjectTable.pluginId, input.pluginId),
          eq(PluginConfigObjectTable.configObjectId, row.id),
          isNull(PluginConfigObjectTable.removedAt),
        ))
        .limit(1)
      if (!memberships[0]) continue
    }
    filtered.push(serializeConfigObject(row, latestVersions.get(row.id) ?? null))
  }

  return pageItems(filtered, input.cursor, input.limit)
}

export async function getConfigObjectDetail(context: PluginArchActorContext, configObjectId: ConfigObjectId) {
  const row = await ensureVisibleConfigObject(context, configObjectId)
  const latest = await getLatestVersions([row.id])
  return serializeConfigObject(row, latest.get(row.id) ?? null)
}

export async function createConfigObject(input: {
  context: PluginArchActorContext
  objectType: ConfigObjectRow["objectType"]
  pluginIds?: PluginId[]
  requireFreshSession?: boolean
  sourceMode: ConfigObjectRow["sourceMode"]
  value: ConfigObjectInput
}) {
  if (input.sourceMode === "connector") {
    throw new PluginArchRouteFailure(400, "invalid_request", "Connector-managed config objects must be created through connector sync.")
  }

  const targetExposure = await Promise.all((input.pluginIds ?? []).map((pluginId) =>
    pluginArchResourceHasExpandedAudience({ context: input.context, resourceId: pluginId, resourceKind: "plugin" })))
  const requireFreshSession = input.requireFreshSession ?? targetExposure.some(Boolean)
  for (const pluginId of input.pluginIds ?? []) {
    await ensureEditablePlugin(input.context, pluginId, requireFreshSession,
      input.objectType === "skill" ? CONTENT_EDIT_SESSION_MAX_AGE_MS : undefined)
  }

  const now = new Date()
  const projection = deriveProjection({ objectType: input.objectType, value: input.value })
  const organizationId = input.context.organizationContext.organization.id
  const createdByOrgMembershipId = input.context.organizationContext.currentMember.id
  const configObjectId = createDenTypeId("configObject")
  const versionId = createDenTypeId("configObjectVersion")

  await db.transaction(async (tx) => {
    await tx.insert(ConfigObjectTable).values({
      createdAt: now,
      createdByOrgMembershipId,
      currentFileExtension: null,
      currentFileName: null,
      currentRelativePath: null,
      deletedAt: null,
      description: projection.description,
      id: configObjectId,
      objectType: input.objectType,
      organizationId,
      searchText: projection.searchText,
      sourceMode: input.sourceMode,
      status: "active",
      title: projection.title,
      updatedAt: now,
      connectorInstanceId: null,
    })

      await tx.insert(ConfigObjectVersionTable).values({
        configObjectId,
        connectorSyncEventId: null,
        createdAt: now,
        createdByOrgMembershipId,
        createdVia: input.sourceMode,
        id: versionId,
        isDeletedVersion: false,
        normalizedPayloadJson: input.value.normalizedPayloadJson ?? null,
        organizationId,
        rawSourceText: normalizeOptionalString(input.value.rawSourceText),
      schemaVersion: normalizeOptionalString(input.value.schemaVersion),
      sourceRevisionRef: null,
    })

      await tx.insert(ConfigObjectAccessGrantTable).values({
        configObjectId,
        createdAt: now,
        createdByOrgMembershipId,
        id: createDenTypeId("configObjectAccessGrant"),
        organizationId,
        orgMembershipId: createdByOrgMembershipId,
      orgWide: false,
      role: "manager",
      teamId: null,
    })

    for (const pluginId of input.pluginIds ?? []) {
      const existing = await tx
        .select({ id: PluginConfigObjectTable.id })
        .from(PluginConfigObjectTable)
        .where(and(eq(PluginConfigObjectTable.pluginId, pluginId), eq(PluginConfigObjectTable.configObjectId, configObjectId)))
        .limit(1)

      if (existing[0]) {
        await tx.update(PluginConfigObjectTable).set({ removedAt: null }).where(eq(PluginConfigObjectTable.id, existing[0].id))
      } else {
        await tx.insert(PluginConfigObjectTable).values({
          configObjectId,
          connectorMappingId: null,
          createdAt: now,
          createdByOrgMembershipId,
          id: createDenTypeId("pluginConfigObject"),
          membershipSource: "manual",
          organizationId,
          pluginId,
        })
      }
    }
  })

  return getConfigObjectDetail(input.context, configObjectId)
}

export async function listConfigObjectVersions(input: { context: PluginArchActorContext; configObjectId: ConfigObjectId; cursor?: string; includeDeleted?: boolean; limit?: number }) {
  const configObject = await ensureVisibleConfigObject(input.context, input.configObjectId)
  const rows = await db
    .select()
    .from(ConfigObjectVersionTable)
    .where(eq(ConfigObjectVersionTable.configObjectId, configObject.id))
    .orderBy(desc(ConfigObjectVersionTable.createdAt), desc(ConfigObjectVersionTable.id))

  const items = rows
    .filter((row) => input.includeDeleted || !row.isDeletedVersion)
    .map((row) => ({ ...serializeVersion(row), id: row.id }))

  return pageItems(items, input.cursor, input.limit)
}

export async function getConfigObjectVersion(input: { context: PluginArchActorContext; configObjectId: ConfigObjectId; versionId: ConfigObjectVersionId }) {
  await ensureVisibleConfigObject(input.context, input.configObjectId)
  const rows = await db
    .select()
    .from(ConfigObjectVersionTable)
    .where(and(eq(ConfigObjectVersionTable.id, input.versionId), eq(ConfigObjectVersionTable.configObjectId, input.configObjectId)))
    .limit(1)
  if (!rows[0]) {
    throw new PluginArchRouteFailure(404, "config_object_version_not_found", "Config object version not found.")
  }
  return serializeVersion(rows[0])
}

export async function getLatestConfigObjectVersion(input: { context: PluginArchActorContext; configObjectId: ConfigObjectId }) {
  await ensureVisibleConfigObject(input.context, input.configObjectId)
  const rows = await db
    .select()
    .from(ConfigObjectVersionTable)
    .where(eq(ConfigObjectVersionTable.configObjectId, input.configObjectId))
    .orderBy(desc(ConfigObjectVersionTable.createdAt), desc(ConfigObjectVersionTable.id))
    .limit(1)
  if (!rows[0]) {
    throw new PluginArchRouteFailure(404, "config_object_version_not_found", "Config object version not found.")
  }
  return serializeVersion(rows[0])
}

export async function createConfigObjectVersion(input: { context: PluginArchActorContext; configObjectId: ConfigObjectId; reason?: string; value: ConfigObjectInput }) {
  const row = await getConfigObjectRow(input.context.organizationContext.organization.id, input.configObjectId)
  if (!row) {
    throw new PluginArchRouteFailure(404, "config_object_not_found", "Config object not found.")
  }
  const requireFreshSession = await pluginArchResourceHasExpandedAudience({ context: input.context, resourceId: row.id, resourceKind: "config_object" })
  await requirePluginArchResourceRole({
    context: input.context, requireFreshSession, resourceId: row.id, resourceKind: "config_object", role: "editor",
    sessionMaxAgeMs: row.objectType === "skill" ? CONTENT_EDIT_SESSION_MAX_AGE_MS : undefined,
  })

  const now = new Date()
  const projection = deriveProjection({ objectType: row.objectType, value: input.value })
  await db.transaction(async (tx) => {
    await tx.insert(ConfigObjectVersionTable).values({
      configObjectId: row.id,
      connectorSyncEventId: null,
      createdAt: now,
      createdByOrgMembershipId: input.context.organizationContext.currentMember.id,
      createdVia: row.sourceMode === "connector" ? "connector" : row.sourceMode,
      id: createDenTypeId("configObjectVersion"),
      isDeletedVersion: false,
      normalizedPayloadJson: input.value.normalizedPayloadJson ?? null,
      organizationId: row.organizationId,
      rawSourceText: normalizeOptionalString(input.value.rawSourceText),
      schemaVersion: normalizeOptionalString(input.value.schemaVersion),
      sourceRevisionRef: normalizeOptionalString(input.reason),
    })

    await tx.update(ConfigObjectTable).set({
      description: projection.description,
      searchText: projection.searchText,
      title: projection.title,
      updatedAt: now,
    }).where(eq(ConfigObjectTable.id, row.id))
  })
  await deleteStalePluginMcpRequirementBindingsForConfigObject({
    configObject: row,
    spec: parseConfigObjectInputSpec(input.value),
  })

  return getConfigObjectDetail(input.context, row.id)
}

export async function setConfigObjectLifecycle(input: { context: PluginArchActorContext; configObjectId: ConfigObjectId; action: "archive" | "delete" | "restore" }) {
  const row = await getConfigObjectRow(input.context.organizationContext.organization.id, input.configObjectId)
  if (!row) {
    throw new PluginArchRouteFailure(404, "config_object_not_found", "Config object not found.")
  }
  const requireFreshSession = await pluginArchResourceHasExpandedAudience({ context: input.context, resourceId: row.id, resourceKind: "config_object" })
  await requirePluginArchResourceRole({ context: input.context, requireFreshSession, resourceId: row.id, resourceKind: "config_object", role: "manager" })
  const now = new Date()
  const patch = input.action === "archive"
    ? { deletedAt: null, status: "archived" as const, updatedAt: now }
    : input.action === "delete"
      ? { deletedAt: now, status: "deleted" as const, updatedAt: now }
      : { deletedAt: null, status: "active" as const, updatedAt: now }

  await db.update(ConfigObjectTable).set(patch).where(eq(ConfigObjectTable.id, row.id))
  await syncPluginMcpRequirementAccessForResource({
    context: input.context,
    resourceId: row.id,
    resourceKind: "config_object",
  })
  return getConfigObjectDetail(input.context, row.id)
}

export async function listConfigObjectPlugins(input: { context: PluginArchActorContext; configObjectId: ConfigObjectId }) {
  const configObject = await ensureVisibleConfigObject(input.context, input.configObjectId)
  const latest = await getLatestVersions([configObject.id])
  const memberships = await db
    .select()
    .from(PluginConfigObjectTable)
    .where(eq(PluginConfigObjectTable.configObjectId, configObject.id))
    .orderBy(desc(PluginConfigObjectTable.createdAt))

  const serializedConfigObject = serializeConfigObject(configObject, latest.get(configObject.id) ?? null)
  const visible: ReturnType<typeof serializeMembership>[] = []
  for (const membership of memberships) {
    const pluginRole = await resolvePluginArchResourceRole({ context: input.context, resourceId: membership.pluginId, resourceKind: "plugin" })
    if (!pluginRole) continue
    visible.push(serializeMembership(membership, serializedConfigObject))
  }
  return { items: visible, nextCursor: null }
}

export async function attachConfigObjectToPlugin(input: { context: PluginArchActorContext; configObjectId: ConfigObjectId; membershipSource?: PluginMembershipRow["membershipSource"]; pluginId: PluginId }) {
  const configObject = await ensureVisibleConfigObject(input.context, input.configObjectId)
  if (configObject.objectType === "workflow" || configObject.objectType === "script") {
    // Adding a Workflow to a Plugin can expand its audience through Plugin and
    // Marketplace grants, so only a Workflow manager may make that sharing
    // decision. Other config-object membership behavior stays compatible.
    await requirePluginArchResourceRole({
      context: input.context,
      resourceId: configObject.id,
      resourceKind: "config_object",
      role: "manager",
    })
  }
  await ensureEditablePlugin(input.context, input.pluginId)

  const existing = await db
    .select()
    .from(PluginConfigObjectTable)
    .where(and(eq(PluginConfigObjectTable.pluginId, input.pluginId), eq(PluginConfigObjectTable.configObjectId, input.configObjectId)))
    .limit(1)

  const now = new Date()
  let membershipId = existing[0]?.id ?? null
  if (existing[0]) {
    await db.update(PluginConfigObjectTable).set({ membershipSource: input.membershipSource ?? existing[0].membershipSource, removedAt: null }).where(eq(PluginConfigObjectTable.id, existing[0].id))
  } else {
    membershipId = createDenTypeId("pluginConfigObject")
    await db.insert(PluginConfigObjectTable).values({
      configObjectId: input.configObjectId,
      connectorMappingId: null,
      createdAt: now,
      createdByOrgMembershipId: input.context.organizationContext.currentMember.id,
      id: membershipId,
      membershipSource: input.membershipSource ?? "manual",
      organizationId: input.context.organizationContext.organization.id,
      pluginId: input.pluginId,
    })
  }

  const rows = await db.select().from(PluginConfigObjectTable).where(eq(PluginConfigObjectTable.id, membershipId!)).limit(1)
  return serializeMembership(rows[0])
}

export async function removeConfigObjectFromPlugin(input: { context: PluginArchActorContext; configObjectId: ConfigObjectId; pluginId: PluginId }) {
  const configObject = await ensureVisibleConfigObject(input.context, input.configObjectId)
  if (configObject.objectType === "workflow" || configObject.objectType === "script") {
    await requirePluginArchResourceRole({
      context: input.context,
      resourceId: configObject.id,
      resourceKind: "config_object",
      role: "manager",
    })
  }
  await ensureEditablePlugin(input.context, input.pluginId)
  const rows = await db
    .select()
    .from(PluginConfigObjectTable)
    .where(and(eq(PluginConfigObjectTable.pluginId, input.pluginId), eq(PluginConfigObjectTable.configObjectId, input.configObjectId), isNull(PluginConfigObjectTable.removedAt)))
    .limit(1)
  if (!rows[0]) {
    throw new PluginArchRouteFailure(404, "plugin_membership_not_found", "Plugin membership not found.")
  }
  await db.update(PluginConfigObjectTable).set({ removedAt: new Date() }).where(eq(PluginConfigObjectTable.id, rows[0].id))
  await deletePluginMcpRequirementBindingsForPluginConfigObject({
    configObjectId: input.configObjectId,
    organizationId: input.context.organizationContext.organization.id,
    pluginId: input.pluginId,
  })
}

export async function listResourceAccess(input: { context: PluginArchActorContext } & ResourceTarget) {
  await ensureResourceInOrganization(input.context, input)
  await requirePluginArchResourceRole({
    context: input.context,
    requireFreshSession: false,
    resourceId: input.resourceId,
    resourceKind: input.resourceKind,
    role: "manager",
  })

  if (input.resourceKind === "config_object") {
    const rows = await db.select().from(ConfigObjectAccessGrantTable).where(eq(ConfigObjectAccessGrantTable.configObjectId, input.resourceId)).orderBy(desc(ConfigObjectAccessGrantTable.createdAt))
    return { items: rows.map((row) => serializeAccessGrant(row)), nextCursor: null }
  }
  if (input.resourceKind === "marketplace") {
    const rows = await db.select().from(MarketplaceAccessGrantTable).where(eq(MarketplaceAccessGrantTable.marketplaceId, input.resourceId)).orderBy(desc(MarketplaceAccessGrantTable.createdAt))
    return { items: rows.map((row) => serializeAccessGrant(row)), nextCursor: null }
  }
  if (input.resourceKind === "plugin") {
    const rows = await db.select().from(PluginAccessGrantTable).where(eq(PluginAccessGrantTable.pluginId, input.resourceId)).orderBy(desc(PluginAccessGrantTable.createdAt))
    return { items: rows.map((row) => serializeAccessGrant(row)), nextCursor: null }
  }
  const rows = await db.select().from(ConnectorInstanceAccessGrantTable).where(eq(ConnectorInstanceAccessGrantTable.connectorInstanceId, input.resourceId)).orderBy(desc(ConnectorInstanceAccessGrantTable.createdAt))
  return { items: rows.map((row) => serializeAccessGrant(row)), nextCursor: null }
}

type TeamPluginAccessEdge = "direct_team" | "via_catalog" | "org_wide"

type TeamPluginAccessCandidate = {
  createdAt: Date
  createdByOrgMembershipId: MemberId
  edge: TeamPluginAccessEdge
  grantId: PluginAccessGrantId | null
  marketplace: { id: MarketplaceId; name: string } | null
  pluginId: PluginId
  pluginName: string
  role: PluginArchRole
}

const teamPluginAccessEdgeOrder: Record<TeamPluginAccessEdge, number> = {
  direct_team: 1,
  via_catalog: 2,
  org_wide: 3,
}

const pluginAccessRolePriority: Record<PluginArchRole, number> = {
  viewer: 1,
  editor: 2,
  manager: 3,
}

export async function listTeamEffectivePluginAccess(input: { context: PluginArchActorContext; teamId: TeamId }) {
  const organizationId = input.context.organizationContext.organization.id
  const teams = await db
    .select({ id: TeamTable.id })
    .from(TeamTable)
    .where(and(eq(TeamTable.id, input.teamId), eq(TeamTable.organizationId, organizationId)))
    .limit(1)

  if (!teams[0]) {
    throw new PluginArchRouteFailure(404, "team_not_found", "Team not found.")
  }
  if (!isPluginArchOrgAdmin(input.context) && !input.context.memberTeams.some((team) => team.id === input.teamId)) {
    throw new PluginArchAuthorizationError(403, "forbidden", "Only organization admins and team members can view this team's plugin access.")
  }

  const [directRows, marketplaceRows, orgWideRows] = await Promise.all([
    db
      .select({
        createdAt: PluginAccessGrantTable.createdAt,
        createdByOrgMembershipId: PluginAccessGrantTable.createdByOrgMembershipId,
        grantId: PluginAccessGrantTable.id,
        pluginId: PluginTable.id,
        pluginName: PluginTable.name,
        role: PluginAccessGrantTable.role,
      })
      .from(PluginAccessGrantTable)
      .innerJoin(PluginTable, eq(PluginAccessGrantTable.pluginId, PluginTable.id))
      .where(and(
        eq(PluginAccessGrantTable.organizationId, organizationId),
        eq(PluginAccessGrantTable.teamId, input.teamId),
        isNull(PluginAccessGrantTable.removedAt),
        eq(PluginTable.organizationId, organizationId),
        eq(PluginTable.status, "active"),
        isNull(PluginTable.deletedAt),
      ))
      .orderBy(asc(PluginAccessGrantTable.createdAt), asc(PluginAccessGrantTable.id)),
    db
      .select({
        createdAt: MarketplaceAccessGrantTable.createdAt,
        createdByOrgMembershipId: MarketplaceAccessGrantTable.createdByOrgMembershipId,
        marketplaceId: MarketplaceTable.id,
        marketplaceName: MarketplaceTable.name,
        pluginId: PluginTable.id,
        pluginName: PluginTable.name,
        role: MarketplaceAccessGrantTable.role,
      })
      .from(MarketplaceAccessGrantTable)
      .innerJoin(MarketplaceTable, eq(MarketplaceAccessGrantTable.marketplaceId, MarketplaceTable.id))
      .innerJoin(MarketplacePluginTable, eq(MarketplacePluginTable.marketplaceId, MarketplaceTable.id))
      .innerJoin(PluginTable, eq(MarketplacePluginTable.pluginId, PluginTable.id))
      .where(and(
        eq(MarketplaceAccessGrantTable.organizationId, organizationId),
        eq(MarketplaceAccessGrantTable.teamId, input.teamId),
        isNull(MarketplaceAccessGrantTable.removedAt),
        eq(MarketplaceTable.organizationId, organizationId),
        eq(MarketplaceTable.status, "active"),
        isNull(MarketplaceTable.deletedAt),
        eq(MarketplacePluginTable.organizationId, organizationId),
        isNull(MarketplacePluginTable.removedAt),
        eq(PluginTable.organizationId, organizationId),
        eq(PluginTable.status, "active"),
        isNull(PluginTable.deletedAt),
      ))
      .orderBy(asc(MarketplaceAccessGrantTable.createdAt), asc(MarketplaceAccessGrantTable.id), asc(MarketplaceTable.name)),
    db
      .select({
        createdAt: PluginAccessGrantTable.createdAt,
        createdByOrgMembershipId: PluginAccessGrantTable.createdByOrgMembershipId,
        pluginId: PluginTable.id,
        pluginName: PluginTable.name,
        role: PluginAccessGrantTable.role,
      })
      .from(PluginAccessGrantTable)
      .innerJoin(PluginTable, eq(PluginAccessGrantTable.pluginId, PluginTable.id))
      .where(and(
        eq(PluginAccessGrantTable.organizationId, organizationId),
        eq(PluginAccessGrantTable.orgWide, true),
        isNull(PluginAccessGrantTable.removedAt),
        eq(PluginTable.organizationId, organizationId),
        eq(PluginTable.status, "active"),
        isNull(PluginTable.deletedAt),
      ))
      .orderBy(asc(PluginAccessGrantTable.createdAt), asc(PluginAccessGrantTable.id)),
  ])

  const candidates: TeamPluginAccessCandidate[] = []
  for (const row of directRows) {
    candidates.push({
      ...row,
      edge: "direct_team",
      marketplace: null,
    })
  }
  for (const row of marketplaceRows) {
    candidates.push({
      createdAt: row.createdAt,
      createdByOrgMembershipId: row.createdByOrgMembershipId,
      edge: "via_catalog",
      grantId: null,
      marketplace: { id: row.marketplaceId, name: row.marketplaceName },
      pluginId: row.pluginId,
      pluginName: row.pluginName,
      role: row.role,
    })
  }
  for (const row of orgWideRows) {
    candidates.push({
      ...row,
      edge: "org_wide",
      grantId: null,
      marketplace: null,
    })
  }

  const effectiveByPluginEdge = new Map<string, TeamPluginAccessCandidate>()
  for (const candidate of candidates) {
    const key = `${candidate.pluginId}:${candidate.edge}`
    const current = effectiveByPluginEdge.get(key)
    if (!current || pluginAccessRolePriority[candidate.role] > pluginAccessRolePriority[current.role]) {
      effectiveByPluginEdge.set(key, candidate)
    }
  }

  const effective = [...effectiveByPluginEdge.values()]
  const pluginIds = uniqueIds(effective.map((candidate) => candidate.pluginId))
  const creatorIds = uniqueIds(effective.map((candidate) => candidate.createdByOrgMembershipId))
  const componentCountRows = pluginIds.length === 0
    ? []
    : await db
      .select({ pluginId: PluginConfigObjectTable.pluginId, componentCount: count() })
      .from(PluginConfigObjectTable)
      .where(and(
        eq(PluginConfigObjectTable.organizationId, organizationId),
        inArray(PluginConfigObjectTable.pluginId, pluginIds),
        isNull(PluginConfigObjectTable.removedAt),
      ))
      .groupBy(PluginConfigObjectTable.pluginId)
  const creatorRows = creatorIds.length === 0
    ? []
    : await db
      .select({ orgMembershipId: MemberTable.id, name: AuthUserTable.name })
      .from(MemberTable)
      .leftJoin(AuthUserTable, eq(MemberTable.userId, AuthUserTable.id))
      .where(and(eq(MemberTable.organizationId, organizationId), inArray(MemberTable.id, creatorIds)))

  const componentCounts = new Map(componentCountRows.map((row) => [row.pluginId, row.componentCount]))
  const creatorNames = new Map(creatorRows.flatMap((row) => row.name === null ? [] : [[row.orgMembershipId, row.name]]))

  effective.sort((left, right) => {
    const byName = left.pluginName.localeCompare(right.pluginName)
    if (byName !== 0) return byName
    const byEdge = teamPluginAccessEdgeOrder[left.edge] - teamPluginAccessEdgeOrder[right.edge]
    if (byEdge !== 0) return byEdge
    return left.pluginId.localeCompare(right.pluginId)
  })

  return {
    items: effective.map((candidate) => {
      const creatorName = creatorNames.get(candidate.createdByOrgMembershipId)
      return {
        plugin: {
          id: candidate.pluginId,
          name: candidate.pluginName,
          componentCount: componentCounts.get(candidate.pluginId) ?? 0,
        },
        edge: candidate.edge,
        marketplace: candidate.marketplace,
        role: candidate.role,
        grantedBy: creatorName
          ? { orgMembershipId: candidate.createdByOrgMembershipId, name: creatorName }
          : null,
        grantedAt: candidate.createdAt.toISOString(),
        grantId: candidate.grantId,
      }
    }),
  }
}

export type MePluginAccessEdge =
  | { kind: "mine" }
  | { kind: "person"; sharedBy: { orgMembershipId: MemberId; name: string } | null; grantedAt: string }
  | { kind: "team"; team: { id: TeamId; name: string } }
  | { kind: "org_wide" }
  | { kind: "catalog"; marketplace: { id: MarketplaceId; name: string } }

type MePluginAccessCandidate = {
  edge: MePluginAccessEdge
  edgeKey: string
  role: PluginArchRole
}

const mePluginAccessEdgeOrder: Record<MePluginAccessEdge["kind"], number> = {
  mine: 1,
  person: 2,
  team: 3,
  org_wide: 4,
  catalog: 5,
}

async function listMeEffectivePluginAccessWithComponentKinds(input: { context: PluginArchActorContext }) {
  const organizationId = input.context.organizationContext.organization.id
  const memberId = input.context.organizationContext.currentMember.id
  const teamIds = input.context.memberTeams.map((team) => team.id)
  const activePlugins = await db
    .select()
    .from(PluginTable)
    .where(and(
      eq(PluginTable.organizationId, organizationId),
      eq(PluginTable.status, "active"),
      isNull(PluginTable.deletedAt),
    ))

  if (activePlugins.length === 0) return { items: [] }

  const pluginIds = activePlugins.map((plugin) => plugin.id)
  const applicablePluginGrant = teamIds.length > 0
    ? or(
        eq(PluginAccessGrantTable.orgWide, true),
        eq(PluginAccessGrantTable.orgMembershipId, memberId),
        inArray(PluginAccessGrantTable.teamId, teamIds),
      )
    : or(
        eq(PluginAccessGrantTable.orgWide, true),
        eq(PluginAccessGrantTable.orgMembershipId, memberId),
      )
  const applicableMarketplaceGrant = teamIds.length > 0
    ? or(
        eq(MarketplaceAccessGrantTable.orgWide, true),
        eq(MarketplaceAccessGrantTable.orgMembershipId, memberId),
        inArray(MarketplaceAccessGrantTable.teamId, teamIds),
      )
    : or(
        eq(MarketplaceAccessGrantTable.orgWide, true),
        eq(MarketplaceAccessGrantTable.orgMembershipId, memberId),
      )

  const [pluginGrants, marketplaceMemberships, marketplaceGrants, componentRows] = await Promise.all([
    db
      .select()
      .from(PluginAccessGrantTable)
      .where(and(
        eq(PluginAccessGrantTable.organizationId, organizationId),
        inArray(PluginAccessGrantTable.pluginId, pluginIds),
        isNull(PluginAccessGrantTable.removedAt),
        applicablePluginGrant,
      ))
      .orderBy(asc(PluginAccessGrantTable.createdAt), asc(PluginAccessGrantTable.id)),
    db
      .select({
        marketplaceId: MarketplaceTable.id,
        marketplaceName: MarketplaceTable.name,
        pluginId: MarketplacePluginTable.pluginId,
      })
      .from(MarketplacePluginTable)
      .innerJoin(MarketplaceTable, eq(MarketplacePluginTable.marketplaceId, MarketplaceTable.id))
      .where(and(
        eq(MarketplacePluginTable.organizationId, organizationId),
        inArray(MarketplacePluginTable.pluginId, pluginIds),
        isNull(MarketplacePluginTable.removedAt),
        eq(MarketplaceTable.organizationId, organizationId),
        eq(MarketplaceTable.status, "active"),
        isNull(MarketplaceTable.deletedAt),
      )),
    db
      .select()
      .from(MarketplaceAccessGrantTable)
      .where(and(
        eq(MarketplaceAccessGrantTable.organizationId, organizationId),
        isNull(MarketplaceAccessGrantTable.removedAt),
        applicableMarketplaceGrant,
      )),
    db
      .select({
        pluginId: PluginConfigObjectTable.pluginId,
        objectType: ConfigObjectTable.objectType,
        componentCount: count(),
      })
      .from(PluginConfigObjectTable)
      .innerJoin(ConfigObjectTable, and(
        eq(PluginConfigObjectTable.organizationId, ConfigObjectTable.organizationId),
        eq(PluginConfigObjectTable.configObjectId, ConfigObjectTable.id),
      ))
      .where(and(
        eq(PluginConfigObjectTable.organizationId, organizationId),
        inArray(PluginConfigObjectTable.pluginId, pluginIds),
        isNull(PluginConfigObjectTable.removedAt),
      ))
      .groupBy(PluginConfigObjectTable.pluginId, ConfigObjectTable.objectType),
  ])

  const grantCreatorIds = uniqueIds(pluginGrants.flatMap((grant) =>
    grant.orgMembershipId === memberId ? [grant.createdByOrgMembershipId] : []))
  const grantCreatorRows = grantCreatorIds.length === 0
    ? []
    : await db
      .select({ orgMembershipId: MemberTable.id, name: AuthUserTable.name })
      .from(MemberTable)
      .leftJoin(AuthUserTable, eq(MemberTable.userId, AuthUserTable.id))
      .where(and(eq(MemberTable.organizationId, organizationId), inArray(MemberTable.id, grantCreatorIds)))
  const grantCreatorNames = new Map(grantCreatorRows.map((row) => [row.orgMembershipId, row.name]))
  const pluginsById = new Map(activePlugins.map((plugin) => [plugin.id, plugin]))
  const teamsById = new Map(input.context.memberTeams.map((team) => [team.id, team]))
  const marketplaceGrantsById = new Map<MarketplaceId, typeof marketplaceGrants>()
  const componentsByPlugin = new Map<PluginId, { count: number; kinds: Set<string> }>()
  for (const row of componentRows) {
    const components = componentsByPlugin.get(row.pluginId) ?? { count: 0, kinds: new Set<string>() }
    components.count += row.componentCount
    components.kinds.add(row.objectType)
    componentsByPlugin.set(row.pluginId, components)
  }
  const candidatesByPlugin = new Map<PluginId, Map<string, MePluginAccessCandidate>>()

  const addCandidate = (pluginId: PluginId, candidate: MePluginAccessCandidate) => {
    const existingCandidates = candidatesByPlugin.get(pluginId)
    const candidates = existingCandidates ?? new Map<string, MePluginAccessCandidate>()
    if (!existingCandidates) candidatesByPlugin.set(pluginId, candidates)
    const current = candidates.get(candidate.edgeKey)
    if (!current || pluginAccessRolePriority[candidate.role] > pluginAccessRolePriority[current.role]) {
      candidates.set(candidate.edgeKey, candidate)
    }
  }

  for (const plugin of activePlugins) {
    if (plugin.createdByOrgMembershipId === memberId) {
      addCandidate(plugin.id, { edge: { kind: "mine" }, edgeKey: "mine", role: "manager" })
    }
  }

  for (const grant of pluginGrants) {
    if (!pluginsById.has(grant.pluginId)) continue
    if (grant.orgMembershipId === memberId) {
      const creatorName = grantCreatorNames.get(grant.createdByOrgMembershipId)
      addCandidate(grant.pluginId, {
        edge: {
          kind: "person",
          sharedBy: creatorName ? { orgMembershipId: grant.createdByOrgMembershipId, name: creatorName } : null,
          grantedAt: grant.createdAt.toISOString(),
        },
        edgeKey: "person",
        role: grant.role,
      })
    }
    if (grant.teamId) {
      const team = teamsById.get(grant.teamId)
      if (team) {
        addCandidate(grant.pluginId, {
          edge: { kind: "team", team: { id: team.id, name: team.name } },
          edgeKey: `team:${team.id}`,
          role: grant.role,
        })
      }
    }
    if (grant.orgWide) {
      addCandidate(grant.pluginId, { edge: { kind: "org_wide" }, edgeKey: "org_wide", role: grant.role })
    }
  }

  for (const grant of marketplaceGrants) {
    const existing = marketplaceGrantsById.get(grant.marketplaceId) ?? []
    existing.push(grant)
    marketplaceGrantsById.set(grant.marketplaceId, existing)
  }
  for (const membership of marketplaceMemberships) {
    const role = resolvePluginArchGrantRole({
      grants: marketplaceGrantsById.get(membership.marketplaceId) ?? [],
      memberId,
      teamIds,
    })
    if (!role) continue
    addCandidate(membership.pluginId, {
      edge: {
        kind: "catalog",
        marketplace: { id: membership.marketplaceId, name: membership.marketplaceName },
      },
      edgeKey: `catalog:${membership.marketplaceId}`,
      role: "viewer",
    })
  }

  const items = activePlugins.flatMap((plugin) => {
    const candidates = [...(candidatesByPlugin.get(plugin.id)?.values() ?? [])]
    if (candidates.length === 0) return []
    candidates.sort((left, right) => {
      const byKind = mePluginAccessEdgeOrder[left.edge.kind] - mePluginAccessEdgeOrder[right.edge.kind]
      return byKind !== 0 ? byKind : left.edgeKey.localeCompare(right.edgeKey)
    })
    let role: PluginArchRole = "viewer"
    for (const candidate of candidates) {
      if (pluginAccessRolePriority[candidate.role] > pluginAccessRolePriority[role]) role = candidate.role
    }
    return [{
      plugin: {
        id: plugin.id,
        name: plugin.name,
        description: plugin.description,
        componentCount: componentsByPlugin.get(plugin.id)?.count ?? 0,
        componentKinds: [...(componentsByPlugin.get(plugin.id)?.kinds ?? [])].sort(),
        sourceRepositoryUrl: plugin.sourceRepositoryUrl,
      },
      edges: candidates.map((candidate) => candidate.edge),
      role,
    }]
  })
  items.sort((left, right) => {
    const byName = left.plugin.name.localeCompare(right.plugin.name)
    return byName !== 0 ? byName : left.plugin.id.localeCompare(right.plugin.id)
  })
  return { items }
}

export async function listMeEffectivePluginAccess(input: { context: PluginArchActorContext }) {
  const result = await listMeEffectivePluginAccessWithComponentKinds(input)
  return {
    items: result.items.map((item) => ({
      plugin: {
        id: item.plugin.id,
        name: item.plugin.name,
        description: item.plugin.description,
        componentCount: item.plugin.componentCount,
        sourceRepositoryUrl: item.plugin.sourceRepositoryUrl,
      },
      edges: item.edges,
      role: item.role,
    })),
  }
}

export async function listMeLibraryPluginItems(input: { context: PluginArchActorContext }) {
  const result = await listMeEffectivePluginAccessWithComponentKinds(input)
  return result.items.map((item) => ({
      type: "plugin" as const,
      id: item.plugin.id,
      name: item.plugin.name,
      description: item.plugin.description,
      componentCount: item.plugin.componentCount,
      componentKinds: item.plugin.componentKinds,
      sourceRepositoryUrl: item.plugin.sourceRepositoryUrl,
      edges: item.edges,
      role: item.role,
  }))
}

function memberConnectionState(connection: MemberUsableConnectionFacts) {
  if (
    connection.setupRequired
    || connection.issuerReviewRequired
    || connection.reconnectActionOwner === "organization_admin"
    || connection.authPolicyConfirmed === false
    || connection.authTypeMismatch
    || (connection.oauthClientRequired && !connection.oauthClientConfigured)
    || (connection.credentialMode === "shared" && !connection.connectedForMe)
  ) {
    return "needs_admin_setup"
  }
  if (connection.credentialMode === "per_member" && (!connection.connectedForMe || connection.needsReconnect)) {
    return "needs_signin"
  }
  if (connection.connectedForMe || (connection.credentialMode === "shared" && connection.connected)) {
    return "connected"
  }
  return "available"
}

export async function listMeLibraryConnectionItems(input: {
  connections: MemberUsableConnectionFacts[]
  context: PluginArchActorContext
}) {
  if (input.connections.length === 0) return []

  const organizationId = input.context.organizationContext.organization.id
  const memberId = input.context.organizationContext.currentMember.id
  const teamIds = input.context.memberTeams.map((team) => team.id)
  const connectionIds = new Set(input.connections.map((connection) => connection.id))
  const applicableGrant = teamIds.length > 0
    ? or(
        eq(ExternalMcpConnectionAccessGrantTable.orgWide, true),
        eq(ExternalMcpConnectionAccessGrantTable.orgMembershipId, memberId),
        inArray(ExternalMcpConnectionAccessGrantTable.teamId, teamIds),
      )
    : or(
        eq(ExternalMcpConnectionAccessGrantTable.orgWide, true),
        eq(ExternalMcpConnectionAccessGrantTable.orgMembershipId, memberId),
      )
  const grants = await db
    .select()
    .from(ExternalMcpConnectionAccessGrantTable)
    .where(and(
      eq(ExternalMcpConnectionAccessGrantTable.organizationId, organizationId),
      applicableGrant,
    ))
    .orderBy(asc(ExternalMcpConnectionAccessGrantTable.createdAt), asc(ExternalMcpConnectionAccessGrantTable.id))
  const creatorIds = uniqueIds(grants
    .filter((grant) => connectionIds.has(grant.externalMcpConnectionId) && grant.orgMembershipId === memberId)
    .map((grant) => grant.createdByOrgMembershipId))
  const creators = creatorIds.length === 0
    ? []
    : await db
      .select({ orgMembershipId: MemberTable.id, name: AuthUserTable.name })
      .from(MemberTable)
      .leftJoin(AuthUserTable, eq(MemberTable.userId, AuthUserTable.id))
      .where(and(eq(MemberTable.organizationId, organizationId), inArray(MemberTable.id, creatorIds)))
  const creatorNames = new Map(creators.map((creator) => [creator.orgMembershipId, creator.name]))
  const teamsById = new Map(input.context.memberTeams.map((team) => [team.id, team]))
  const edgesByConnection = new Map<string, Map<string, MePluginAccessEdge>>()
  const addEdge = (connectionId: string, key: string, edge: MePluginAccessEdge) => {
    if (!connectionIds.has(connectionId)) return
    const edges = edgesByConnection.get(connectionId) ?? new Map<string, MePluginAccessEdge>()
    if (!edgesByConnection.has(connectionId)) edgesByConnection.set(connectionId, edges)
    if (!edges.has(key)) edges.set(key, edge)
  }

  for (const grant of grants) {
    if (grant.orgWide) addEdge(grant.externalMcpConnectionId, "org_wide", { kind: "org_wide" })
    if (grant.orgMembershipId === memberId) {
      const creatorName = creatorNames.get(grant.createdByOrgMembershipId)
      addEdge(grant.externalMcpConnectionId, "person", {
        kind: "person",
        sharedBy: creatorName ? { orgMembershipId: grant.createdByOrgMembershipId, name: creatorName } : null,
        grantedAt: grant.createdAt.toISOString(),
      })
    }
    if (grant.teamId) {
      const team = teamsById.get(grant.teamId)
      if (team) addEdge(grant.externalMcpConnectionId, `team:${team.id}`, { kind: "team", team: { id: team.id, name: team.name } })
    }
  }

  return input.connections.map((connection) => {
    const edges = [...(edgesByConnection.get(connection.id)?.values() ?? [{ kind: "org_wide" } satisfies MePluginAccessEdge])]
    edges.sort((left, right) => mePluginAccessEdgeOrder[left.kind] - mePluginAccessEdgeOrder[right.kind])
    return {
      type: "connection",
      id: connection.id,
      name: connection.name,
      url: connection.url,
      description: null,
      transport: connection.nativeProviderKey !== null ? "native" : "mcp",
      provider: connection.nativeProviderKey,
      state: memberConnectionState(connection),
      connectedAt: connection.connectedAt,
      edges,
    }
  })
}

export async function createResourceAccessGrant(input: { context: PluginArchActorContext; value: AccessGrantWrite } & ResourceTarget) {
  await ensureResourceInOrganization(input.context, input)
  await requirePluginArchResourceRole({ context: input.context, resourceId: input.resourceId, resourceKind: input.resourceKind, role: "manager" })
  if (input.value.orgWide === true && !isPluginArchOrgAdmin(input.context)) {
    throw new PluginArchAuthorizationError(403, "forbidden", "Only organization owners and admins can grant org-wide access.")
  }
  await ensureGrantTargetsInOrganization(input.context, input.value)
  const grant = await upsertGrant(input)
  await syncPluginMcpRequirementAccessForResource(input)
  return grant
}

export async function deleteResourceAccessGrant(input: { context: PluginArchActorContext } & GrantTarget) {
  await ensureResourceInOrganization(input.context, input)
  await requirePluginArchResourceRole({ context: input.context, resourceId: input.resourceId, resourceKind: input.resourceKind, role: "manager" })
  await removeGrant(input)
  await syncPluginMcpRequirementAccessForResource(input)
}

async function collectPluginMarketplaces(organizationId: PluginRow["organizationId"], pluginIds: PluginId[]): Promise<Map<string, PluginMarketplaceSummary[]>> {
  const byPlugin = new Map<string, PluginMarketplaceSummary[]>()
  if (pluginIds.length === 0) {
    return byPlugin
  }

  const rows = await db
    .select({
      marketplaceId: MarketplaceTable.id,
      marketplaceName: MarketplaceTable.name,
      pluginId: MarketplacePluginTable.pluginId,
    })
    .from(MarketplacePluginTable)
    .innerJoin(MarketplaceTable, eq(MarketplacePluginTable.marketplaceId, MarketplaceTable.id))
    .where(and(
      eq(MarketplaceTable.organizationId, organizationId),
      isNull(MarketplacePluginTable.removedAt),
      isNull(MarketplaceTable.deletedAt),
      inArray(MarketplacePluginTable.pluginId, pluginIds),
    ))

  for (const row of rows) {
    const existing = byPlugin.get(row.pluginId) ?? []
    existing.push({ id: row.marketplaceId, name: row.marketplaceName })
    byPlugin.set(row.pluginId, existing)
  }
  return byPlugin
}

export async function listPlugins(input: { context: PluginArchActorContext; cursor?: string; limit?: number; q?: string; status?: PluginRow["status"] }) {
  const rows = await db
    .select()
    .from(PluginTable)
    .where(eq(PluginTable.organizationId, input.context.organizationContext.organization.id))
    .orderBy(desc(PluginTable.updatedAt), desc(PluginTable.id))

  const memberships = await db
    .select({ pluginId: PluginConfigObjectTable.pluginId, count: PluginConfigObjectTable.id })
    .from(PluginConfigObjectTable)
    .where(isNull(PluginConfigObjectTable.removedAt))

  const counts = memberships.reduce((accumulator, row) => {
    accumulator.set(row.pluginId, (accumulator.get(row.pluginId) ?? 0) + 1)
    return accumulator
  }, new Map<string, number>())

  const marketplaceMembers = await collectPluginMarketplaces(
    input.context.organizationContext.organization.id,
    rows.map((row) => row.id),
  )

  const visible: ReturnType<typeof serializePlugin>[] = []
  for (const row of rows) {
    const role = await resolvePluginArchResourceRole({ context: input.context, resourceId: row.id, resourceKind: "plugin" })
    if (!role) continue
    if (input.status && row.status !== input.status) continue
    if (input.q) {
      const haystack = `${row.name}\n${row.description ?? ""}`.toLowerCase()
      if (!haystack.includes(input.q.toLowerCase())) continue
    }
    visible.push(serializePlugin(row, counts.get(row.id) ?? 0, marketplaceMembers.get(row.id) ?? []))
  }

  return pageItems(visible, input.cursor, input.limit)
}

export async function getPluginDetail(context: PluginArchActorContext, pluginId: PluginId) {
  const row = await ensureVisiblePlugin(context, pluginId)
  const memberships = await db.select({ id: PluginConfigObjectTable.id }).from(PluginConfigObjectTable).where(and(eq(PluginConfigObjectTable.pluginId, row.id), isNull(PluginConfigObjectTable.removedAt)))
  const marketplaceMembers = await collectPluginMarketplaces(context.organizationContext.organization.id, [row.id])
  return serializePlugin(row, memberships.length, marketplaceMembers.get(row.id) ?? [])
}

export async function createPlugin(input: {
  context: PluginArchActorContext
  description?: string | null
  name: string
  sourceFormat?: string | null
  sourceRepositoryUrl?: string | null
  sourceSchemaVersion?: string | null
}) {
  const now = new Date()
  const name = input.name.trim()
  const existing = await db
    .select({ id: PluginTable.id })
    .from(PluginTable)
    .where(and(
      eq(PluginTable.organizationId, input.context.organizationContext.organization.id),
      eq(PluginTable.createdByOrgMembershipId, input.context.organizationContext.currentMember.id),
      eq(PluginTable.name, name),
      eq(PluginTable.status, "active"),
      isNull(PluginTable.deletedAt),
    ))
    .orderBy(asc(PluginTable.createdAt), asc(PluginTable.id))
    .limit(1)

  if (existing[0]) {
    throw new PluginArchRouteFailure(409, "duplicate_plugin", `You already have an active plugin named "${name}" (${existing[0].id}). Update it instead of creating a duplicate.`)
  }

  const row = {
    createdAt: now,
    createdByOrgMembershipId: input.context.organizationContext.currentMember.id,
    deletedAt: null,
    description: normalizeOptionalString(input.description ?? undefined),
    id: createDenTypeId("plugin"),
    name,
    organizationId: input.context.organizationContext.organization.id,
    sourceFormat: normalizeOptionalString(input.sourceFormat ?? undefined),
    sourceRepositoryUrl: normalizeOptionalString(input.sourceRepositoryUrl ?? undefined),
    sourceSchemaVersion: normalizeOptionalString(input.sourceSchemaVersion ?? undefined),
    status: "active" as const,
    updatedAt: now,
  }

  await db.transaction(async (tx) => {
    await tx.insert(PluginTable).values(row)
    await tx.insert(PluginAccessGrantTable).values({
      createdAt: now,
      createdByOrgMembershipId: input.context.organizationContext.currentMember.id,
      id: createDenTypeId("pluginAccessGrant"),
      organizationId: input.context.organizationContext.organization.id,
      orgMembershipId: input.context.organizationContext.currentMember.id,
      orgWide: false,
      pluginId: row.id,
      role: "manager",
      teamId: null,
    })
  })

  return serializePlugin(row, 0)
}

function pluginMcpConnectionSetupInput(setup: PluginMcpConnectionSetup) {
  return {
    apiKey: setup.apiKey,
    authType: setup.authType,
    credentialMode: setup.credentialMode ?? (setup.authType === "oauth" ? "per_member" : "shared"),
    oauthClient: setup.oauthClient,
  }
}

/**
 * A plugin whose inline MCP connection setup failed must not survive as an
 * active plugin with a half-configured server: the creator would see the
 * failure, fix the credentials, and then hit a duplicate-name conflict on
 * retry. Mirror the GitHub import path instead — drop the bindings and the
 * connections this attempt created, then archive the plugin.
 */
async function rollbackPluginMcpConnectionSetup(input: { context: PluginArchActorContext; pluginId: PluginId }) {
  const organizationId = input.context.organizationContext.organization.id
  const bindings = await pluginMcpRequirementBindingsForResource({ organizationId, resourceId: input.pluginId, resourceKind: "plugin" }).catch(() => [])
  await deletePluginMcpRequirementBindingsForPlugin({ organizationId, pluginId: input.pluginId }).catch(() => undefined)
  for (const binding of bindings) {
    if (!binding.connectionOwnedByPlugin) continue
    await deleteExternalMcpConnectionIfUnreferenced({ connectionId: binding.externalMcpConnectionId, organizationId }).catch(() => undefined)
  }
  await setPluginLifecycle({ action: "archive", context: input.context, pluginId: input.pluginId }).catch(() => undefined)
}

export async function createPluginBundle(input: {
  components?: { connection?: PluginMcpConnectionSetup; connectionId?: string; type: ConfigObjectRow["objectType"]; value?: ConfigObjectInput }[]
  context: PluginArchActorContext
  description?: string | null
  marketplaceId?: MarketplaceId
  name: string
  orgWide?: boolean
  sourceRepositoryUrl?: string | null
}) {
  if (input.orgWide === true && !isPluginArchOrgAdmin(input.context)) {
    throw new PluginArchAuthorizationError(403, "forbidden", "Only organization owners and admins can create org-wide plugins.")
  }

  const components: Array<{
    connection?: PluginMcpConnectionSetup
    connectionBinding?: { connection: ExternalMcpConnectionRow; serverName: string }
    type: ConfigObjectRow["objectType"]
    value: ConfigObjectInput
  }> = []
  for (const component of input.components ?? []) {
    if (component.connectionId !== undefined) {
      if (!isPluginArchOrgAdmin(input.context)) {
        throw new PluginArchAuthorizationError(403, "forbidden", "Only organization owners and admins can bind plugin MCP servers to organization connections.")
      }
      if (component.type !== "mcp") {
        throw new PluginArchRouteFailure(400, "invalid_request", "connectionId is only allowed on mcp components.")
      }
      if (component.connection) {
        throw new PluginArchRouteFailure(400, "invalid_request", "Provide either connection or connectionId, not both.")
      }
      let connectionId: ExternalMcpConnectionRow["id"]
      try {
        connectionId = normalizeDenTypeId("externalMcpConnection", component.connectionId)
      } catch {
        throw new PluginArchRouteFailure(404, "mcp_connection_not_found", "That connector was not found in this organization.")
      }
      const connection = await getExternalMcpConnection({
        connectionId,
        organizationId: input.context.organizationContext.organization.id,
      })
      if (!connection || connection.kind !== "external_mcp") {
        throw new PluginArchRouteFailure(404, "mcp_connection_not_found", "That connector was not found in this organization.")
      }
      const value: ConfigObjectInput = {
        normalizedPayloadJson: connectionBackedMcpPayload({
          authType: connection.authType,
          connectionId: connection.id,
          ownedByPlugin: false,
          server: { name: connection.name, url: connection.url },
        }),
        metadata: {
          name: component.value?.metadata?.name ?? connection.name,
          description: component.value?.metadata?.description,
        },
      }
      deriveProjection({ objectType: component.type, value })
      components.push({
        connectionBinding: { connection, serverName: slugifyPluginMcpName(connection.name) },
        type: component.type,
        value,
      })
      continue
    }
    if (!component.value) {
      throw new PluginArchRouteFailure(400, "invalid_request", "input is required unless connectionId is provided.")
    }
    deriveProjection({ objectType: component.type, value: component.value })
    if (component.connection) {
      if (!isPluginArchOrgAdmin(input.context)) {
        throw new PluginArchAuthorizationError(403, "forbidden", "Only organization owners and admins can configure plugin MCP connections.")
      }
      validatePluginMcpRequirementAuth(pluginMcpConnectionSetupInput(component.connection))
    }
    components.push({ connection: component.connection, type: component.type, value: component.value })
  }

  if (input.marketplaceId) {
    // Validate the publish target before creating anything so a bad marketplace cannot leave an orphan plugin.
    await ensureEditableMarketplace(input.context, input.marketplaceId)
  }

  const plugin = await createPlugin({ context: input.context, description: input.description, name: input.name, sourceRepositoryUrl: input.sourceRepositoryUrl })

  const pendingConnections: Array<{ configObjectId: ConfigObjectId; connection: PluginMcpConnectionSetup; serverNames: string[] }> = []
  const pendingConnectionBindings: Array<{ configObjectId: ConfigObjectId; connection: ExternalMcpConnectionRow; serverName: string }> = []
  for (const component of components) {
    const configObject = await createConfigObject({
      context: input.context,
      objectType: component.type,
      pluginIds: [plugin.id],
      requireFreshSession: false,
      sourceMode: "cloud",
      value: component.value,
    })
    if (input.orgWide) {
      await createResourceAccessGrant({
        context: input.context,
        resourceId: configObject.id,
        resourceKind: "config_object",
        value: { orgWide: true, role: "viewer" },
      })
    }
    if (component.connection) {
      pendingConnections.push({
        configObjectId: configObject.id,
        connection: component.connection,
        serverNames: marketplaceMcpServerEntries(parseConfigObjectInputSpec(component.value), configObject.title).map((entry) => entry.name),
      })
    }
    if (component.connectionBinding) {
      pendingConnectionBindings.push({ configObjectId: configObject.id, ...component.connectionBinding })
    }
  }

  if (input.orgWide) {
    await createResourceAccessGrant({
      context: input.context,
      resourceId: plugin.id,
      resourceKind: "plugin",
      value: { orgWide: true, role: "viewer" },
    })
  }

  if (input.marketplaceId) {
    await attachPluginToMarketplace({ context: input.context, marketplaceId: input.marketplaceId, pluginId: plugin.id })
  }

  // Grants and the collection are in place, so the derived connection access
  // is complete the moment each server is configured.
  try {
    for (const pending of pendingConnections) {
      for (const serverName of pending.serverNames) {
        await configureMarketplacePluginMcpRequirement({
          ...pluginMcpConnectionSetupInput(pending.connection),
          configObjectId: pending.configObjectId,
          context: input.context,
          pluginId: plugin.id,
          serverName,
        })
      }
    }
    for (const pending of pendingConnectionBindings) {
      const binding = await upsertPluginMcpRequirementBinding({
        configObjectId: pending.configObjectId,
        createdByOrgMembershipId: input.context.organizationContext.currentMember.id,
        externalMcpConnectionId: pending.connection.id,
        organizationId: input.context.organizationContext.organization.id,
        pluginId: plugin.id,
        serverName: pending.serverName,
        requiredAuthType: pending.connection.authType,
        connectionOwnedByPlugin: false,
      })
      await syncPluginMcpRequirementBindingAccess(binding)
    }
  } catch (error) {
    await rollbackPluginMcpConnectionSetup({ context: input.context, pluginId: plugin.id })
    throw error
  }

  return getPluginDetail(input.context, plugin.id)
}

export async function updatePlugin(input: { context: PluginArchActorContext; description?: string | null; name?: string; pluginId: PluginId }) {
  const requireFreshSession = await pluginArchResourceHasExpandedAudience({ context: input.context, resourceId: input.pluginId, resourceKind: "plugin" })
  const row = await ensureEditablePlugin(input.context, input.pluginId, requireFreshSession, CONTENT_EDIT_SESSION_MAX_AGE_MS)
  const updatedAt = new Date()
  await db.update(PluginTable).set({
    description: input.description === undefined ? row.description : normalizeOptionalString(input.description ?? undefined),
    name: input.name?.trim() || row.name,
    updatedAt,
  }).where(eq(PluginTable.id, row.id))
  return getPluginDetail(input.context, row.id)
}

export async function setPluginLifecycle(input: { action: "archive" | "restore"; context: PluginArchActorContext; pluginId: PluginId }) {
  const row = await ensureVisiblePlugin(input.context, input.pluginId)
  const requireFreshSession = await pluginArchResourceHasExpandedAudience({ context: input.context, resourceId: row.id, resourceKind: "plugin" })
  await requirePluginArchResourceRole({ context: input.context, requireFreshSession, resourceId: row.id, resourceKind: "plugin", role: "manager" })
  const updatedAt = new Date()
  await db.update(PluginTable).set({
    deletedAt: input.action === "archive" ? row.deletedAt : null,
    status: input.action === "archive" ? "archived" : "active",
    updatedAt,
  }).where(eq(PluginTable.id, row.id))
  await syncPluginMcpRequirementAccessForResource({
    context: input.context,
    resourceId: row.id,
    resourceKind: "plugin",
  })
  return getPluginDetail(input.context, row.id)
}

export async function listPluginMemberships(input: { context: PluginArchActorContext; pluginId: PluginId; includeConfigObjects?: boolean; legacyWorkflowObjectType?: boolean; onlyActive?: boolean }) {
  await ensureVisiblePlugin(input.context, input.pluginId)
  const memberships = await db
    .select()
    .from(PluginConfigObjectTable)
    .where(input.onlyActive ? and(eq(PluginConfigObjectTable.pluginId, input.pluginId), isNull(PluginConfigObjectTable.removedAt)) : eq(PluginConfigObjectTable.pluginId, input.pluginId))
    .orderBy(desc(PluginConfigObjectTable.createdAt))

  if (!input.includeConfigObjects) {
    return { items: memberships.map((membership) => serializeMembership(membership)), nextCursor: null }
  }

  const configObjects = await db.select().from(ConfigObjectTable).where(inArray(ConfigObjectTable.id, memberships.map((membership) => membership.configObjectId)))
  const resolvedConfigObjects = input.onlyActive
    ? configObjects.filter((row) => row.status === "active" && row.deletedAt === null)
    : configObjects
  const resolvedConfigObjectIds = new Set(resolvedConfigObjects.map((row) => row.id))
  const resolvedMemberships = input.onlyActive
    ? memberships.filter((membership) => resolvedConfigObjectIds.has(membership.configObjectId))
    : memberships
  const latestVersions = await getLatestVersions(resolvedConfigObjects.map((row) => row.id))
  const byId = new Map<string, ReturnType<typeof serializeConfigObject>>(resolvedConfigObjects.map((row) => {
    const serialized = serializeConfigObject(row, latestVersions.get(row.id) ?? null)
    return [row.id, input.legacyWorkflowObjectType && serialized.objectType === "workflow"
      ? { ...serialized, objectType: "script" }
      : serialized]
  }))
  return { items: resolvedMemberships.map((membership) => serializeMembership(membership, byId.get(membership.configObjectId))), nextCursor: null }
}

export async function addPluginMembership(input: { configObjectId: ConfigObjectId; context: PluginArchActorContext; membershipSource?: PluginMembershipRow["membershipSource"]; pluginId: PluginId }) {
  return attachConfigObjectToPlugin({ ...input })
}

export async function removePluginMembership(input: { configObjectId: ConfigObjectId; context: PluginArchActorContext; pluginId: PluginId }) {
  return removeConfigObjectFromPlugin(input)
}

export async function listMarketplaces(input: { context: PluginArchActorContext; cursor?: string; limit?: number; q?: string; status?: MarketplaceRow["status"] }) {
  await ensureDefaultOmniRushMarketplace(input.context)

  const rows = await db
    .select()
    .from(MarketplaceTable)
    .where(eq(MarketplaceTable.organizationId, input.context.organizationContext.organization.id))
    .orderBy(desc(MarketplaceTable.updatedAt), desc(MarketplaceTable.id))

  const marketplaceIds = rows.map((row) => row.id)
  const memberships = marketplaceIds.length === 0
    ? []
    : await db
      .select({ marketplaceId: MarketplacePluginTable.marketplaceId, count: count() })
      .from(MarketplacePluginTable)
      .where(and(
        eq(MarketplacePluginTable.organizationId, input.context.organizationContext.organization.id),
        inArray(MarketplacePluginTable.marketplaceId, marketplaceIds),
        isNull(MarketplacePluginTable.removedAt),
      ))
      .groupBy(MarketplacePluginTable.marketplaceId)

  const counts = new Map<string, number>(memberships.map((row) => [row.marketplaceId, row.count]))

  const visible: ReturnType<typeof serializeMarketplace>[] = []
  for (const row of rows) {
    const role = await resolvePluginArchResourceRole({ context: input.context, resourceId: row.id, resourceKind: "marketplace" })
    if (!role) continue
    if (input.status && row.status !== input.status) continue
    if (input.q) {
      const haystack = `${row.name}\n${row.description ?? ""}`.toLowerCase()
      if (!haystack.includes(input.q.toLowerCase())) continue
    }
    visible.push(serializeMarketplace(row, counts.get(row.id) ?? 0))
  }

  return pageItems(visible, input.cursor, input.limit)
}

async function ensureDefaultOmniRushMarketplace(context: PluginArchActorContext) {
  const organizationId = context.organizationContext.organization.id
  if (await defaultOmniRushMarketplaceSeedComplete(organizationId)) {
    return
  }

  await db.transaction(async (tx) => {
    const organization = (await tx
      .select({ id: OrganizationTable.id })
      .from(OrganizationTable)
      .where(eq(OrganizationTable.id, organizationId))
      .limit(1)
      .for("update"))[0]
    if (!organization) throw new Error("Organization not found while provisioning default marketplaces.")

    const now = new Date()
    const anthropicMarketplace = await ensureDefaultMarketplace({
      context,
      createdAt: now,
      database: tx,
      description: DEFAULT_ANTHROPIC_MARKETPLACE_DESCRIPTION,
      logoUrl: DEFAULT_ANTHROPIC_MARKETPLACE_LOGO_URL,
      name: DEFAULT_ANTHROPIC_MARKETPLACE_NAME,
    })
    await ensureDefaultMarketplacePlugins({
      context,
      createdAt: now,
      database: tx,
      entries: DEFAULT_ANTHROPIC_STARTER_PLUGINS,
      marketplaceId: anthropicMarketplace.id,
    })

    const marketplace = await ensureDefaultMarketplace({
      context,
      createdAt: now,
      database: tx,
      description: DEFAULT_OMNIRUSH_MARKETPLACE_DESCRIPTION,
      logoUrl: DEFAULT_OMNIRUSH_MARKETPLACE_LOGO_URL,
      name: DEFAULT_OMNIRUSH_MARKETPLACE_NAME,
    })
    await ensureDefaultMarketplacePlugins({
      context,
      createdAt: now,
      database: tx,
      entries: DEFAULT_OMNIRUSH_EXTENSION_MANIFESTS.map((manifest) => ({ description: manifest.description, name: manifest.name })),
      marketplaceId: marketplace.id,
    })
  })
}

async function defaultOmniRushMarketplaceSeedComplete(organizationId: OrganizationId) {
  const defaultMarketplaces = await db
    .select({ id: MarketplaceTable.id, logoUrl: MarketplaceTable.logoUrl, name: MarketplaceTable.name })
    .from(MarketplaceTable)
    .where(and(
      eq(MarketplaceTable.organizationId, organizationId),
      inArray(MarketplaceTable.name, [DEFAULT_ANTHROPIC_MARKETPLACE_NAME, DEFAULT_OMNIRUSH_MARKETPLACE_NAME]),
      eq(MarketplaceTable.status, "active"),
      isNull(MarketplaceTable.deletedAt),
    ))
  const marketplaceIdByName = new Map(defaultMarketplaces.map((marketplace) => [marketplace.name, marketplace.id]))
  const anthropicMarketplaceId = marketplaceIdByName.get(DEFAULT_ANTHROPIC_MARKETPLACE_NAME)
  const omniRushMarketplaceId = marketplaceIdByName.get(DEFAULT_OMNIRUSH_MARKETPLACE_NAME)
  if (!anthropicMarketplaceId || !omniRushMarketplaceId) {
    return false
  }
  if (!defaultMarketplaces.some((marketplace) => marketplace.name === DEFAULT_ANTHROPIC_MARKETPLACE_NAME && marketplace.logoUrl === DEFAULT_ANTHROPIC_MARKETPLACE_LOGO_URL)) {
    return false
  }
  if (!defaultMarketplaces.some((marketplace) => marketplace.name === DEFAULT_OMNIRUSH_MARKETPLACE_NAME && marketplace.logoUrl === DEFAULT_OMNIRUSH_MARKETPLACE_LOGO_URL)) {
    return false
  }

  const marketplaceIds = [anthropicMarketplaceId, omniRushMarketplaceId]
  const marketplaceGrantRows = await db
    .select({ marketplaceId: MarketplaceAccessGrantTable.marketplaceId, role: MarketplaceAccessGrantTable.role })
    .from(MarketplaceAccessGrantTable)
    .where(and(
      eq(MarketplaceAccessGrantTable.organizationId, organizationId),
      inArray(MarketplaceAccessGrantTable.marketplaceId, marketplaceIds),
      eq(MarketplaceAccessGrantTable.orgWide, true),
      eq(MarketplaceAccessGrantTable.role, "viewer"),
      isNull(MarketplaceAccessGrantTable.removedAt),
    ))
  const marketplaceGrants = new Set(marketplaceGrantRows.map((grant) => grant.marketplaceId))
  if (!marketplaceIds.every((marketplaceId) => marketplaceGrants.has(marketplaceId))) {
    return false
  }

  const anthropicPluginEntries = DEFAULT_ANTHROPIC_STARTER_PLUGINS
  const omniRushPluginEntries = DEFAULT_OMNIRUSH_EXTENSION_MANIFESTS.map((manifest) => ({ description: manifest.description, name: manifest.name }))
  const defaultPluginEntries = [...anthropicPluginEntries, ...omniRushPluginEntries]
  const defaultPluginRows = await db
    .select({ id: PluginTable.id, name: PluginTable.name, description: PluginTable.description })
    .from(PluginTable)
    .where(and(
      eq(PluginTable.organizationId, organizationId),
      inArray(PluginTable.name, defaultPluginEntries.map((entry) => entry.name)),
      eq(PluginTable.status, "active"),
      isNull(PluginTable.deletedAt),
    ))

  const pluginIdByEntry = new Map<string, PluginId>()
  for (const entry of defaultPluginEntries) {
    const plugin = defaultPluginRows.find((row) => row.name === entry.name && row.description === entry.description)
    if (!plugin) {
      return false
    }
    pluginIdByEntry.set(defaultMarketplacePluginEntryKey(entry), plugin.id)
  }

  const pluginIds = Array.from(pluginIdByEntry.values())
  const pluginGrantRows = await db
    .select({ pluginId: PluginAccessGrantTable.pluginId })
    .from(PluginAccessGrantTable)
    .where(and(
      eq(PluginAccessGrantTable.organizationId, organizationId),
      inArray(PluginAccessGrantTable.pluginId, pluginIds),
      eq(PluginAccessGrantTable.orgWide, true),
      eq(PluginAccessGrantTable.role, "viewer"),
      isNull(PluginAccessGrantTable.removedAt),
    ))
  const pluginGrants = new Set(pluginGrantRows.map((grant) => grant.pluginId))
  if (!pluginIds.every((pluginId) => pluginGrants.has(pluginId))) {
    return false
  }

  const expectedMemberships = new Set<string>()
  for (const entry of anthropicPluginEntries) {
    const pluginId = pluginIdByEntry.get(defaultMarketplacePluginEntryKey(entry))
    if (pluginId) expectedMemberships.add(defaultMarketplacePluginMembershipKey(anthropicMarketplaceId, pluginId))
  }
  for (const entry of omniRushPluginEntries) {
    const pluginId = pluginIdByEntry.get(defaultMarketplacePluginEntryKey(entry))
    if (pluginId) expectedMemberships.add(defaultMarketplacePluginMembershipKey(omniRushMarketplaceId, pluginId))
  }

  const membershipRows = await db
    .select({ marketplaceId: MarketplacePluginTable.marketplaceId, pluginId: MarketplacePluginTable.pluginId })
    .from(MarketplacePluginTable)
    .where(and(
      eq(MarketplacePluginTable.organizationId, organizationId),
      inArray(MarketplacePluginTable.marketplaceId, marketplaceIds),
      inArray(MarketplacePluginTable.pluginId, pluginIds),
      isNull(MarketplacePluginTable.removedAt),
    ))
  const memberships = new Set(membershipRows.map((membership) => defaultMarketplacePluginMembershipKey(membership.marketplaceId, membership.pluginId)))
  return Array.from(expectedMemberships).every((membership) => memberships.has(membership))
}

function defaultMarketplacePluginEntryKey(entry: DefaultMarketplacePluginEntry) {
  return `${entry.name}\n${entry.description}`
}

function defaultMarketplacePluginMembershipKey(marketplaceId: MarketplaceId, pluginId: PluginId) {
  return `${marketplaceId}:${pluginId}`
}

async function ensureDefaultMarketplacePlugins(input: {
  context: PluginArchActorContext
  createdAt: Date
  database: DbTransaction
  entries: DefaultMarketplacePluginEntry[]
  marketplaceId: MarketplaceId
}) {
  const organizationId = input.context.organizationContext.organization.id
  const createdByOrgMembershipId = input.context.organizationContext.currentMember.id

  for (const entry of input.entries) {
    let plugin = (await input.database
      .select()
      .from(PluginTable)
      .where(and(
        eq(PluginTable.organizationId, organizationId),
        eq(PluginTable.name, entry.name),
        eq(PluginTable.description, entry.description),
        isNull(PluginTable.deletedAt),
      ))
      .limit(1))[0]

    if (!plugin) {
      const pluginRow = {
        createdAt: input.createdAt,
        createdByOrgMembershipId,
        deletedAt: null,
        description: entry.description,
        id: createDenTypeId("plugin"),
        name: entry.name,
        organizationId,
        sourceFormat: null,
        sourceRepositoryUrl: null,
        sourceSchemaVersion: null,
        status: "active" as const,
        updatedAt: input.createdAt,
      }
      await input.database.insert(PluginTable).values(pluginRow)
      plugin = pluginRow
    }

    await ensureOrgWidePluginAccess({ context: input.context, database: input.database, pluginId: plugin.id, role: "viewer" })

    const existingMembership = (await input.database
      .select()
      .from(MarketplacePluginTable)
      .where(and(
        eq(MarketplacePluginTable.marketplaceId, input.marketplaceId),
        eq(MarketplacePluginTable.pluginId, plugin.id),
      ))
      .limit(1))[0]

    if (existingMembership) {
      if (existingMembership.removedAt) {
        await input.database.update(MarketplacePluginTable).set({ membershipSource: "system", removedAt: null }).where(eq(MarketplacePluginTable.id, existingMembership.id))
      }
      continue
    }

    await input.database.insert(MarketplacePluginTable).values({
      createdAt: input.createdAt,
      createdByOrgMembershipId,
      id: createDenTypeId("marketplacePlugin"),
      marketplaceId: input.marketplaceId,
      membershipSource: "system",
      organizationId,
      pluginId: plugin.id,
      removedAt: null,
    })
  }
}

async function ensureDefaultMarketplace(input: {
  context: PluginArchActorContext
  createdAt: Date
  database: DbTransaction
  description: string
  logoUrl: string
  name: string
}) {
  const organizationId = input.context.organizationContext.organization.id
  const createdByOrgMembershipId = input.context.organizationContext.currentMember.id

  let marketplace = (await input.database
    .select()
    .from(MarketplaceTable)
    .where(and(
      eq(MarketplaceTable.organizationId, organizationId),
      eq(MarketplaceTable.name, input.name),
      isNull(MarketplaceTable.deletedAt),
    ))
    .limit(1))[0]

  if (!marketplace) {
    const marketplaceRow = {
      externalKey: null,
      createdAt: input.createdAt,
      createdByOrgMembershipId,
      deletedAt: null,
      description: input.description,
      id: createDenTypeId("marketplace"),
      logoUrl: input.logoUrl,
      name: input.name,
      organizationId,
      status: "active" as const,
      updatedAt: input.createdAt,
    }
    await input.database.insert(MarketplaceTable).values(marketplaceRow)
    marketplace = marketplaceRow
  } else if (!marketplace.logoUrl) {
    await input.database.update(MarketplaceTable).set({ logoUrl: input.logoUrl }).where(eq(MarketplaceTable.id, marketplace.id))
    marketplace = { ...marketplace, logoUrl: input.logoUrl }
  }

  await ensureOrgWideMarketplaceAccess({ context: input.context, database: input.database, marketplaceId: marketplace.id, role: "viewer" })
  return marketplace
}

async function ensureOrgWideMarketplaceAccess(input: {
  context: PluginArchActorContext
  database: DbTransaction
  marketplaceId: MarketplaceId
  role: PluginArchRole
}) {
  const createdAt = new Date()
  const createdByOrgMembershipId = input.context.organizationContext.currentMember.id
  const organizationId = input.context.organizationContext.organization.id

  const existing = (await input.database
    .select()
    .from(MarketplaceAccessGrantTable)
    .where(and(eq(MarketplaceAccessGrantTable.marketplaceId, input.marketplaceId), eq(MarketplaceAccessGrantTable.orgWide, true)))
    .limit(1))[0]
  if (existing) {
    if (existing.removedAt || existing.role !== input.role) {
      await input.database.update(MarketplaceAccessGrantTable).set({ createdByOrgMembershipId, removedAt: null, role: input.role }).where(eq(MarketplaceAccessGrantTable.id, existing.id))
    }
    return
  }
  await input.database.insert(MarketplaceAccessGrantTable).values({
    createdAt,
    createdByOrgMembershipId,
    id: createDenTypeId("marketplaceAccessGrant"),
    marketplaceId: input.marketplaceId,
    organizationId,
    orgMembershipId: null,
    orgWide: true,
    role: input.role,
    teamId: null,
  })
}

async function ensureOrgWidePluginAccess(input: {
  context: PluginArchActorContext
  database: DbTransaction
  pluginId: PluginId
  role: PluginArchRole
}) {
  const createdAt = new Date()
  const createdByOrgMembershipId = input.context.organizationContext.currentMember.id
  const organizationId = input.context.organizationContext.organization.id

  const existing = (await input.database
    .select()
    .from(PluginAccessGrantTable)
    .where(and(eq(PluginAccessGrantTable.pluginId, input.pluginId), eq(PluginAccessGrantTable.orgWide, true)))
    .limit(1))[0]
  if (existing) {
    if (existing.removedAt || existing.role !== input.role) {
      await input.database.update(PluginAccessGrantTable).set({ createdByOrgMembershipId, removedAt: null, role: input.role }).where(eq(PluginAccessGrantTable.id, existing.id))
    }
    return
  }
  await input.database.insert(PluginAccessGrantTable).values({
    createdAt,
    createdByOrgMembershipId,
    id: createDenTypeId("pluginAccessGrant"),
    organizationId,
    orgMembershipId: null,
    orgWide: true,
    pluginId: input.pluginId,
    role: input.role,
    teamId: null,
  })
}

export async function getMarketplaceDetail(context: PluginArchActorContext, marketplaceId: MarketplaceId) {
  const row = await ensureVisibleMarketplace(context, marketplaceId)
  const memberships = await db
    .select({ id: MarketplacePluginTable.id })
    .from(MarketplacePluginTable)
    .where(and(eq(MarketplacePluginTable.marketplaceId, row.id), isNull(MarketplacePluginTable.removedAt)))
  return serializeMarketplace(row, memberships.length)
}

export async function createMarketplace(input: { context: PluginArchActorContext; description?: string | null; logoUrl?: string | null; name: string; externalKey?: string }) {
  const now = new Date()
  const row = {
    externalKey: input.externalKey ?? null,
    createdAt: now,
    createdByOrgMembershipId: input.context.organizationContext.currentMember.id,
    deletedAt: null,
    description: normalizeOptionalString(input.description ?? undefined),
    id: createDenTypeId("marketplace"),
    logoUrl: normalizeOptionalString(input.logoUrl ?? undefined),
    name: input.name.trim(),
    organizationId: input.context.organizationContext.organization.id,
    status: "active" as const,
    updatedAt: now,
  }

  await db.transaction(async (tx) => {
    await tx.insert(MarketplaceTable).values(row)
    await tx.insert(MarketplaceAccessGrantTable).values({
      createdAt: now,
      createdByOrgMembershipId: input.context.organizationContext.currentMember.id,
      id: createDenTypeId("marketplaceAccessGrant"),
      marketplaceId: row.id,
      organizationId: input.context.organizationContext.organization.id,
      orgMembershipId: input.context.organizationContext.currentMember.id,
      orgWide: false,
      role: "manager",
      teamId: null,
    })
  })

  return serializeMarketplace(row, 0)
}

export async function updateMarketplace(input: { context: PluginArchActorContext; description?: string | null; logoUrl?: string | null; marketplaceId: MarketplaceId; name?: string }) {
  const row = await ensureEditableMarketplace(input.context, input.marketplaceId)
  const updatedAt = new Date()
  await db.update(MarketplaceTable).set({
    description: input.description === undefined ? row.description : normalizeOptionalString(input.description ?? undefined),
    logoUrl: input.logoUrl === undefined ? row.logoUrl : normalizeOptionalString(input.logoUrl ?? undefined),
    name: input.name?.trim() || row.name,
    updatedAt,
  }).where(eq(MarketplaceTable.id, row.id))
  return getMarketplaceDetail(input.context, row.id)
}

export async function setMarketplaceLifecycle(input: { action: "archive" | "delete" | "restore"; context: PluginArchActorContext; marketplaceId: MarketplaceId }) {
  const row = await ensureVisibleMarketplace(input.context, input.marketplaceId)
  await requirePluginArchResourceRole({ context: input.context, resourceId: row.id, resourceKind: "marketplace", role: "manager" })
  const updatedAt = new Date()
  if (input.action === "delete") {
    const memberships = await db
      .select()
      .from(MarketplacePluginTable)
      .where(eq(MarketplacePluginTable.marketplaceId, row.id))
    if (memberships.some((membership) => membership.removedAt === null && (membership.membershipSource === "system" || membership.membershipSource === "connector"))) {
      throw new PluginArchRouteFailure(409, "managed_marketplace_cannot_be_deleted", "Built-in and connected marketplaces cannot be deleted here.")
    }
    await db.transaction(async (tx) => {
      await tx.delete(MarketplaceAccessGrantTable).where(eq(MarketplaceAccessGrantTable.marketplaceId, row.id))
      await tx.delete(MarketplacePluginTable).where(eq(MarketplacePluginTable.marketplaceId, row.id))
      await tx.delete(MarketplaceTable).where(eq(MarketplaceTable.id, row.id))
    })
    for (const pluginId of new Set(memberships.map((membership) => membership.pluginId))) {
      await syncPluginMcpRequirementAccessForResource({ context: input.context, resourceId: pluginId, resourceKind: "plugin" })
    }
    return serializeMarketplace({ ...row, deletedAt: updatedAt, status: "deleted", updatedAt }, memberships.filter((membership) => membership.removedAt === null).length)
  }
  await db.transaction(async (tx) => {
    await tx.update(MarketplaceTable).set({
      deletedAt: input.action === "restore" ? null : row.deletedAt,
      status: input.action === "archive" ? "archived" : "active",
      updatedAt,
    }).where(eq(MarketplaceTable.id, row.id))
  })
  await syncPluginMcpRequirementAccessForResource({
    context: input.context,
    resourceId: row.id,
    resourceKind: "marketplace",
  })
  return getMarketplaceDetail(input.context, row.id)
}

export async function listMarketplaceMemberships(input: { context: PluginArchActorContext; includePlugins?: boolean; marketplaceId: MarketplaceId; onlyActive?: boolean }) {
  await ensureVisibleMarketplace(input.context, input.marketplaceId)
  const memberships = await db
    .select()
    .from(MarketplacePluginTable)
    .where(input.onlyActive ? and(eq(MarketplacePluginTable.marketplaceId, input.marketplaceId), isNull(MarketplacePluginTable.removedAt)) : eq(MarketplacePluginTable.marketplaceId, input.marketplaceId))
    .orderBy(desc(MarketplacePluginTable.createdAt))

  if (!input.includePlugins) {
    return { items: memberships.map((membership) => serializeMarketplaceMembership(membership)), nextCursor: null }
  }

  const plugins = memberships.length === 0
    ? []
    : await db.select().from(PluginTable).where(inArray(PluginTable.id, memberships.map((membership) => membership.pluginId)))
  const byId = new Map<string, ReturnType<typeof serializePlugin>>(plugins.map((row) => [row.id, serializePlugin(row)]))
  return { items: memberships.map((membership) => serializeMarketplaceMembership(membership, byId.get(membership.pluginId))), nextCursor: null }
}

export type MarketplaceResolvedSource = {
  connectorAccountId: string
  connectorInstanceId: string
  accountLogin: string | null
  repositoryFullName: string
  branch: string | null
} | null

export async function getMarketplaceResolved(input: { context: PluginArchActorContext; marketplaceId: MarketplaceId }) {
  const marketplaceRow = await ensureVisibleMarketplace(input.context, input.marketplaceId)
  const organizationId = input.context.organizationContext.organization.id

  const memberships = await db
    .select()
    .from(MarketplacePluginTable)
    .where(and(eq(MarketplacePluginTable.marketplaceId, marketplaceRow.id), isNull(MarketplacePluginTable.removedAt)))
    .orderBy(desc(MarketplacePluginTable.createdAt))

  const pluginIds = memberships.map((membership) => membership.pluginId)
  const pluginRows = pluginIds.length === 0
    ? []
    : await db.select().from(PluginTable).where(inArray(PluginTable.id, pluginIds))

  const activePluginMemberships = pluginIds.length === 0
    ? []
    : await db
      .select({ pluginId: PluginConfigObjectTable.pluginId, configObjectId: PluginConfigObjectTable.configObjectId })
      .from(PluginConfigObjectTable)
      .where(and(inArray(PluginConfigObjectTable.pluginId, pluginIds), isNull(PluginConfigObjectTable.removedAt)))
  const memberCounts = new Map<string, number>()
  for (const entry of activePluginMemberships) {
    memberCounts.set(entry.pluginId, (memberCounts.get(entry.pluginId) ?? 0) + 1)
  }

  const configObjectIds = [...new Set(activePluginMemberships.map((entry) => entry.configObjectId))]
  const configObjectTypeById = new Map<string, string>()
  if (configObjectIds.length > 0) {
    const rows = await db
      .select({ id: ConfigObjectTable.id, objectType: ConfigObjectTable.objectType })
      .from(ConfigObjectTable)
      .where(inArray(ConfigObjectTable.id, configObjectIds))
    for (const row of rows) {
      configObjectTypeById.set(row.id, row.objectType)
    }
  }

  const componentCountsByPlugin = new Map<string, Map<string, number>>()
  for (const entry of activePluginMemberships) {
    const objectType = configObjectTypeById.get(entry.configObjectId)
    if (!objectType) continue
    let counts = componentCountsByPlugin.get(entry.pluginId)
    if (!counts) {
      counts = new Map<string, number>()
      componentCountsByPlugin.set(entry.pluginId, counts)
    }
    counts.set(objectType, (counts.get(objectType) ?? 0) + 1)
  }

  const cloudReadinessByPlugin = memberFacingMcpConnectionsEnabled(input.context.organizationContext.organization.metadata, { gatingEnabled: env.mcpConnectionsGatingEnabled })
    ? await resolveMarketplacePluginCloudReadiness({
        organizationId,
        member: {
          orgMembershipId: input.context.organizationContext.currentMember.id,
          teamIds: input.context.memberTeams.map((team) => team.id),
        },
        pluginIds,
        desktopManifestPluginIds: pluginRows.flatMap((row) => defaultOmniRushManifestForPlugin(row) ? [row.id] : []),
      })
    : new Map<string, never>()

  const plugins = pluginRows.map((row) => {
    const componentCounts = Object.fromEntries(componentCountsByPlugin.get(row.id) ?? new Map())
    const cloudReadiness = cloudReadinessByPlugin.get(row.id)
    return {
      ...serializePlugin(row, memberCounts.get(row.id) ?? 0, [], componentCounts),
      componentCounts,
      ...(cloudReadiness ? { cloudReadiness } : {}),
    }
  })

  let source: MarketplaceResolvedSource = null
  if (pluginIds.length > 0) {
    const mappingRows = await db
      .selectDistinct({ connectorInstanceId: ConnectorMappingTable.connectorInstanceId })
      .from(ConnectorMappingTable)
      .where(and(
        eq(ConnectorMappingTable.organizationId, organizationId),
        inArray(ConnectorMappingTable.pluginId, pluginIds),
      ))
    const connectorInstanceIds = mappingRows.map((entry) => entry.connectorInstanceId)
    if (connectorInstanceIds.length === 1) {
      const [instance] = await db
        .select()
        .from(ConnectorInstanceTable)
        .where(eq(ConnectorInstanceTable.id, connectorInstanceIds[0]))
        .limit(1)
      if (instance) {
        const [account] = await db
          .select()
          .from(ConnectorAccountTable)
          .where(eq(ConnectorAccountTable.id, instance.connectorAccountId))
          .limit(1)
        const [target] = await db
          .select()
          .from(ConnectorTargetTable)
          .where(eq(ConnectorTargetTable.connectorInstanceId, instance.id))
          .orderBy(asc(ConnectorTargetTable.createdAt), asc(ConnectorTargetTable.id))
          .limit(1)
        const targetConfig = target?.targetConfigJson && typeof target.targetConfigJson === "object"
          ? target.targetConfigJson as Record<string, unknown>
          : {}
        const repositoryFullName = typeof targetConfig.repositoryFullName === "string"
          ? targetConfig.repositoryFullName
          : instance.remoteId ?? ""
        source = {
          connectorAccountId: instance.connectorAccountId,
          connectorInstanceId: instance.id,
          accountLogin: account?.externalAccountRef ?? (account?.metadataJson && typeof account.metadataJson === "object" ? (account.metadataJson as Record<string, unknown>).accountLogin as string ?? null : null),
          repositoryFullName,
          branch: typeof targetConfig.branch === "string" ? targetConfig.branch : target?.externalTargetRef ?? null,
        }
      }
    }
  }

  return {
    marketplace: {
      ...serializeMarketplace(marketplaceRow, plugins.length),
      canDelete: memberships.every((membership) => membership.membershipSource === "manual"),
    },
    plugins,
    source,
  }
}

export async function attachPluginToMarketplace(input: { context: PluginArchActorContext; marketplaceId: MarketplaceId; membershipSource?: MarketplaceMembershipRow["membershipSource"]; pluginId: PluginId }) {
  await ensureVisiblePlugin(input.context, input.pluginId)
  if (input.marketplaceId) {
    await ensureEditableMarketplace(input.context, input.marketplaceId)
  }

  const existing = await db
    .select()
    .from(MarketplacePluginTable)
    .where(and(eq(MarketplacePluginTable.marketplaceId, input.marketplaceId), eq(MarketplacePluginTable.pluginId, input.pluginId)))
    .limit(1)

  const now = new Date()
  let membershipId: MarketplaceMembershipId | null = existing[0]?.id ?? null
  if (existing[0]) {
    await db.update(MarketplacePluginTable).set({ membershipSource: input.membershipSource ?? existing[0].membershipSource, removedAt: null }).where(eq(MarketplacePluginTable.id, existing[0].id))
  } else {
    membershipId = createDenTypeId("marketplacePlugin")
    await db.insert(MarketplacePluginTable).values({
      createdAt: now,
      createdByOrgMembershipId: input.context.organizationContext.currentMember.id,
      id: membershipId,
      marketplaceId: input.marketplaceId,
      membershipSource: input.membershipSource ?? "manual",
      organizationId: input.context.organizationContext.organization.id,
      pluginId: input.pluginId,
    })
  }

  const rows = await db.select().from(MarketplacePluginTable).where(eq(MarketplacePluginTable.id, membershipId!)).limit(1)
  await syncPluginMcpRequirementAccessForResource({ context: input.context, resourceId: input.pluginId, resourceKind: "plugin" })
  return serializeMarketplaceMembership(rows[0])
}

export async function removePluginFromMarketplace(input: { context: PluginArchActorContext; marketplaceId: MarketplaceId; pluginId: PluginId }) {
  await ensureVisiblePlugin(input.context, input.pluginId)
  await ensureEditableMarketplace(input.context, input.marketplaceId)
  const rows = await db
    .select()
    .from(MarketplacePluginTable)
    .where(and(eq(MarketplacePluginTable.marketplaceId, input.marketplaceId), eq(MarketplacePluginTable.pluginId, input.pluginId), isNull(MarketplacePluginTable.removedAt)))
    .limit(1)
  if (!rows[0]) {
    throw new PluginArchRouteFailure(404, "marketplace_membership_not_found", "Marketplace membership not found.")
  }
  await db.update(MarketplacePluginTable).set({ removedAt: new Date() }).where(eq(MarketplacePluginTable.id, rows[0].id))
  await syncPluginMcpRequirementAccessForResource({ context: input.context, resourceId: input.pluginId, resourceKind: "plugin" })
}

export async function listConnectorAccounts(input: { context: PluginArchActorContext; connectorType?: ConnectorAccountRow["connectorType"]; cursor?: string; limit?: number; q?: string; status?: ConnectorAccountRow["status"] }) {
  const rows = await db
    .select()
    .from(ConnectorAccountTable)
    .where(eq(ConnectorAccountTable.organizationId, input.context.organizationContext.organization.id))
    .orderBy(desc(ConnectorAccountTable.updatedAt), desc(ConnectorAccountTable.id))

  const filtered = rows
    .filter((row) => !input.connectorType || row.connectorType === input.connectorType)
    .filter((row) => !input.status || row.status === input.status)
    .filter((row) => !input.q || `${row.displayName}\n${row.remoteId}\n${row.externalAccountRef ?? ""}`.toLowerCase().includes(input.q.toLowerCase()))
    .map((row) => serializeConnectorAccount(row, resolveCreatorName(input.context, row.createdByOrgMembershipId)))

  return pageItems(filtered, input.cursor, input.limit)
}

export async function createConnectorAccount(input: { context: PluginArchActorContext; connectorType: ConnectorAccountRow["connectorType"]; displayName: string; externalAccountRef?: string | null; metadata?: Record<string, unknown>; remoteId: string }) {
  const now = new Date()
  const row = {
    connectorType: input.connectorType,
    createdAt: now,
    createdByOrgMembershipId: input.context.organizationContext.currentMember.id,
    displayName: input.displayName.trim(),
    externalAccountRef: normalizeOptionalString(input.externalAccountRef ?? undefined),
    id: createDenTypeId("connectorAccount"),
    metadataJson: input.metadata ?? null,
    organizationId: input.context.organizationContext.organization.id,
    remoteId: input.remoteId.trim(),
    status: "active" as const,
    updatedAt: now,
  }
  await db.insert(ConnectorAccountTable).values(row)
  return serializeConnectorAccount(row)
}

export async function getConnectorAccountDetail(context: PluginArchActorContext, connectorAccountId: ConnectorAccountId) {
  const row = await getConnectorAccountRow(context.organizationContext.organization.id, connectorAccountId)
  if (!row) {
    throw new PluginArchRouteFailure(404, "connector_account_not_found", "Connector account not found.")
  }
  return serializeConnectorAccount(row, resolveCreatorName(context, row.createdByOrgMembershipId))
}

export async function disconnectConnectorAccount(input: { connectorAccountId: ConnectorAccountId; context: PluginArchActorContext; reason?: string }) {
  const organizationId = input.context.organizationContext.organization.id
  const row = await getConnectorAccountRow(organizationId, input.connectorAccountId)
  if (!row) {
    throw new PluginArchRouteFailure(404, "connector_account_not_found", "Connector account not found.")
  }

  const instances = await db
    .select({ id: ConnectorInstanceTable.id })
    .from(ConnectorInstanceTable)
    .where(and(
      eq(ConnectorInstanceTable.organizationId, organizationId),
      eq(ConnectorInstanceTable.connectorAccountId, row.id),
    ))
  const instanceIds = instances.map((entry) => entry.id)

  const mappingRows = instanceIds.length === 0
    ? []
    : await db
      .select({ id: ConnectorMappingTable.id, pluginId: ConnectorMappingTable.pluginId })
      .from(ConnectorMappingTable)
      .where(inArray(ConnectorMappingTable.connectorInstanceId, instanceIds))
  const mappingIds = mappingRows.map((entry) => entry.id)
  const connectorPluginIds = [...new Set(mappingRows.map((entry) => entry.pluginId).filter((value): value is PluginId => Boolean(value)))]

  const configObjectRows = instanceIds.length === 0
    ? []
    : await db
      .select({ id: ConfigObjectTable.id })
      .from(ConfigObjectTable)
      .where(inArray(ConfigObjectTable.connectorInstanceId, instanceIds))
  const configObjectIds = configObjectRows.map((entry) => entry.id)

  // Resolve every imported marketplace/plugin id to delete up front so the
  // transaction below is a single pass of pure writes (no reads on the tx).
  const importedResourceCleanupPlan = await planConnectorImportedResourceCleanupIds({ organizationId, seedPluginIds: connectorPluginIds })
  const pluginMcpRequirementBindingIdsToDelete = await pluginMcpRequirementBindingIdsForHardDeletedResources({
    configObjectIds,
    organizationId,
    pluginIds: importedResourceCleanupPlan.pluginIdsToDelete,
  })
  importedResourceCleanupPlan.pluginMcpRequirementBindingIdsToDelete = uniqueIds([
    ...importedResourceCleanupPlan.pluginMcpRequirementBindingIdsToDelete,
    ...pluginMcpRequirementBindingIdsToDelete,
  ])

  await db.transaction(async (tx) => {
    await deletePluginMcpRequirementBindingsForHardDelete({
      bindingIds: importedResourceCleanupPlan.pluginMcpRequirementBindingIdsToDelete,
      tx,
    })

    if (instanceIds.length > 0) {
      await tx.delete(ConnectorSourceTombstoneTable).where(inArray(ConnectorSourceTombstoneTable.connectorInstanceId, instanceIds))
      await tx.delete(ConnectorSourceBindingTable).where(inArray(ConnectorSourceBindingTable.connectorInstanceId, instanceIds))
      await tx.delete(ConnectorSyncEventTable).where(inArray(ConnectorSyncEventTable.connectorInstanceId, instanceIds))
    }

    if (configObjectIds.length > 0) {
      await tx.delete(PluginConfigObjectTable).where(inArray(PluginConfigObjectTable.configObjectId, configObjectIds))
      await tx.delete(ConfigObjectAccessGrantTable).where(inArray(ConfigObjectAccessGrantTable.configObjectId, configObjectIds))
      await tx.delete(ConfigObjectVersionTable).where(inArray(ConfigObjectVersionTable.configObjectId, configObjectIds))
      await tx.delete(ConfigObjectTable).where(inArray(ConfigObjectTable.id, configObjectIds))
    }

    if (mappingIds.length > 0) {
      await tx.delete(PluginConfigObjectTable).where(inArray(PluginConfigObjectTable.connectorMappingId, mappingIds))
      await tx.delete(ConnectorMappingTable).where(inArray(ConnectorMappingTable.id, mappingIds))
    }

    if (instanceIds.length > 0) {
      await tx.delete(ConnectorTargetTable).where(inArray(ConnectorTargetTable.connectorInstanceId, instanceIds))
      await tx.delete(ConnectorInstanceAccessGrantTable).where(inArray(ConnectorInstanceAccessGrantTable.connectorInstanceId, instanceIds))
      await tx.delete(ConnectorInstanceTable).where(inArray(ConnectorInstanceTable.id, instanceIds))
    }

    await deleteConnectorImportedResources({ organizationId, plan: importedResourceCleanupPlan, tx })

    await tx.delete(ConnectorAccountTable).where(eq(ConnectorAccountTable.id, row.id))
  })

  return {
    deletedConfigObjectCount: configObjectIds.length,
    deletedConnectorInstanceCount: instanceIds.length,
    deletedConnectorMappingCount: mappingIds.length,
    disconnectedAccountId: row.id,
    reason: input.reason ?? null,
  }
}

export async function listConnectorInstances(input: { connectorAccountId?: ConnectorAccountId; context: PluginArchActorContext; cursor?: string; limit?: number; pluginId?: PluginId; q?: string; status?: ConnectorInstanceRow["status"] }) {
  const rows = await db
    .select()
    .from(ConnectorInstanceTable)
    .where(eq(ConnectorInstanceTable.organizationId, input.context.organizationContext.organization.id))
    .orderBy(desc(ConnectorInstanceTable.updatedAt), desc(ConnectorInstanceTable.id))

  const filtered: ReturnType<typeof serializeConnectorInstance>[] = []
  for (const row of rows) {
    const role = await resolvePluginArchResourceRole({ context: input.context, resourceId: row.id, resourceKind: "connector_instance" })
    if (!role) continue
    if (input.connectorAccountId && row.connectorAccountId !== input.connectorAccountId) continue
    if (input.status && row.status !== input.status) continue
    if (input.q && !`${row.name}\n${row.remoteId ?? ""}`.toLowerCase().includes(input.q.toLowerCase())) continue
    if (input.pluginId) {
      const mappings = await db
        .select({ id: ConnectorMappingTable.id })
        .from(ConnectorMappingTable)
        .where(and(eq(ConnectorMappingTable.connectorInstanceId, row.id), eq(ConnectorMappingTable.pluginId, input.pluginId)))
        .limit(1)
      if (!mappings[0]) continue
    }
    filtered.push(serializeConnectorInstance(row))
  }

  return pageItems(filtered, input.cursor, input.limit)
}

export async function createConnectorInstance(input: { connectorAccountId: ConnectorAccountId; connectorType: ConnectorInstanceRow["connectorType"]; config?: Record<string, unknown>; context: PluginArchActorContext; name: string; remoteId?: string | null }) {
  const account = await getConnectorAccountRow(input.context.organizationContext.organization.id, input.connectorAccountId)
  if (!account) {
    throw new PluginArchRouteFailure(404, "connector_account_not_found", "Connector account not found.")
  }
  const now = new Date()
  const row = {
    connectorAccountId: account.id,
    connectorType: input.connectorType,
    createdAt: now,
    createdByOrgMembershipId: input.context.organizationContext.currentMember.id,
    id: createDenTypeId("connectorInstance"),
    instanceConfigJson: input.config ?? null,
    lastSyncCursor: null,
    lastSyncStatus: null,
    lastSyncedAt: null,
    name: input.name.trim(),
    organizationId: input.context.organizationContext.organization.id,
    remoteId: normalizeOptionalString(input.remoteId ?? undefined),
    status: "active" as const,
    updatedAt: now,
  }
  await db.transaction(async (tx) => {
    await tx.insert(ConnectorInstanceTable).values(row)
    await tx.insert(ConnectorInstanceAccessGrantTable).values({
      connectorInstanceId: row.id,
      createdAt: now,
      createdByOrgMembershipId: input.context.organizationContext.currentMember.id,
      id: createDenTypeId("connectorInstanceAccessGrant"),
      organizationId: input.context.organizationContext.organization.id,
      orgMembershipId: input.context.organizationContext.currentMember.id,
      orgWide: false,
      role: "manager",
      teamId: null,
    })
  })
  return serializeConnectorInstance(row)
}

export async function getConnectorInstanceDetail(context: PluginArchActorContext, connectorInstanceId: ConnectorInstanceId) {
  const row = await ensureVisibleConnectorInstance(context, connectorInstanceId)
  return serializeConnectorInstance(row)
}

export async function updateConnectorInstance(input: { connectorInstanceId: ConnectorInstanceId; config?: Record<string, unknown>; context: PluginArchActorContext; name?: string; remoteId?: string | null; status?: ConnectorInstanceRow["status"] }) {
  const row = await ensureEditableConnectorInstance(input.context, input.connectorInstanceId)
  await db.update(ConnectorInstanceTable).set({
    instanceConfigJson: input.config === undefined ? row.instanceConfigJson : input.config,
    name: input.name?.trim() || row.name,
    remoteId: input.remoteId === undefined ? row.remoteId : normalizeOptionalString(input.remoteId ?? undefined),
    status: input.status ?? row.status,
    updatedAt: new Date(),
  }).where(eq(ConnectorInstanceTable.id, row.id))
  return getConnectorInstanceDetail(input.context, row.id)
}

export async function setConnectorInstanceLifecycle(input: { action: "archive" | "disable" | "enable"; connectorInstanceId: ConnectorInstanceId; context: PluginArchActorContext }) {
  const row = await ensureEditableConnectorInstance(input.context, input.connectorInstanceId)
  const status = input.action === "archive" ? "archived" : input.action === "disable" ? "disabled" : "active"
  await db.update(ConnectorInstanceTable).set({ status, updatedAt: new Date() }).where(eq(ConnectorInstanceTable.id, row.id))
  return getConnectorInstanceDetail(input.context, row.id)
}

function commonSelectorRootPath(selectors: string[]): string | null {
  const normalized = selectors
    .map((selector) => {
      let path = selector.trim().replace(/^\/+/, "").replace(/\/+$/, "")
      if (path.endsWith("/**")) {
        path = path.slice(0, -3)
      }
      const knownLeafSegments = ["skills", "commands", "agents", "hooks", "monitors", "mcp", ".mcp.json", ".lsp.json", "settings.json", "hooks.json"]
      for (const leaf of knownLeafSegments) {
        if (path === leaf) return ""
        if (path.endsWith(`/${leaf}`)) return path.slice(0, -(leaf.length + 1))
      }
      return path
    })
    .filter((path): path is string => path !== null)

  if (normalized.length === 0) return null
  if (normalized.every((path) => path === normalized[0])) {
    return normalized[0]
  }

  const parts = normalized[0].split("/")
  for (let index = parts.length; index > 0; index -= 1) {
    const candidate = parts.slice(0, index).join("/")
    if (normalized.every((path) => path === candidate || path.startsWith(`${candidate}/`))) {
      return candidate
    }
  }
  return ""
}

type ConnectorImportedResourceCleanupPlan = {
  marketplaceIdsToDelete: MarketplaceId[]
  pluginMcpRequirementBindingIdsToDelete: PluginMcpRequirementBindingId[]
  pluginIdsToDelete: PluginId[]
}

async function pluginMcpRequirementBindingIdsForHardDeletedResources(input: {
  configObjectIds: ConfigObjectId[]
  organizationId: OrganizationId
  pluginIds: PluginId[]
}) {
  const bindingIds = new Set<PluginMcpRequirementBindingId>()
  const configObjectIds = uniqueIds(input.configObjectIds)
  const pluginIds = uniqueIds(input.pluginIds)

  if (configObjectIds.length > 0) {
    const rows = await db
      .select({ id: PluginMcpRequirementBindingTable.id })
      .from(PluginMcpRequirementBindingTable)
      .where(and(
        eq(PluginMcpRequirementBindingTable.organizationId, input.organizationId),
        inArray(PluginMcpRequirementBindingTable.configObjectId, configObjectIds),
      ))
    for (const row of rows) bindingIds.add(row.id)
  }

  if (pluginIds.length > 0) {
    const rows = await db
      .select({ id: PluginMcpRequirementBindingTable.id })
      .from(PluginMcpRequirementBindingTable)
      .where(and(
        eq(PluginMcpRequirementBindingTable.organizationId, input.organizationId),
        inArray(PluginMcpRequirementBindingTable.pluginId, pluginIds),
      ))
    for (const row of rows) bindingIds.add(row.id)
  }

  return [...bindingIds]
}

async function pluginMcpRequirementBindingIdsForConnectorMapping(input: {
  connectorMappingId: ConnectorMappingId
  organizationId: OrganizationId
}) {
  const memberships = await db
    .select({
      configObjectId: PluginConfigObjectTable.configObjectId,
      pluginId: PluginConfigObjectTable.pluginId,
    })
    .from(PluginConfigObjectTable)
    .where(and(
      eq(PluginConfigObjectTable.organizationId, input.organizationId),
      eq(PluginConfigObjectTable.connectorMappingId, input.connectorMappingId),
    ))
  const configObjectIds = uniqueIds(memberships.map((membership) => membership.configObjectId))
  const pluginIds = uniqueIds(memberships.map((membership) => membership.pluginId))
  if (configObjectIds.length === 0 || pluginIds.length === 0) return []
  const membershipKeys = new Set(memberships.map((membership) => `${membership.pluginId}:${membership.configObjectId}`))
  const rows = await db
    .select({
      configObjectId: PluginMcpRequirementBindingTable.configObjectId,
      id: PluginMcpRequirementBindingTable.id,
      pluginId: PluginMcpRequirementBindingTable.pluginId,
    })
    .from(PluginMcpRequirementBindingTable)
    .where(and(
      eq(PluginMcpRequirementBindingTable.organizationId, input.organizationId),
      inArray(PluginMcpRequirementBindingTable.configObjectId, configObjectIds),
      inArray(PluginMcpRequirementBindingTable.pluginId, pluginIds),
    ))
  return rows
    .filter((row) => membershipKeys.has(`${row.pluginId}:${row.configObjectId}`))
    .map((row) => row.id)
}

async function deletePluginMcpRequirementBindingsForHardDelete(input: {
  bindingIds: PluginMcpRequirementBindingId[]
  tx: DbTransaction
}) {
  const bindingIds = uniqueIds(input.bindingIds)
  if (bindingIds.length === 0) return
  await input.tx.delete(ExternalMcpConnectionAccessGrantTable).where(inArray(ExternalMcpConnectionAccessGrantTable.pluginMcpRequirementBindingId, bindingIds))
  await input.tx.delete(PluginMcpRequirementBindingTable).where(inArray(PluginMcpRequirementBindingTable.id, bindingIds))
}

// Read-only planning pass. Runs outside of any transaction so that the
// subsequent delete pass can execute as a single transaction of pure writes.
async function planConnectorImportedResourceCleanupIds(input: { organizationId: OrganizationId; seedPluginIds: PluginId[] }): Promise<ConnectorImportedResourceCleanupPlan> {
  const uniqueSeedPluginIds = uniqueIds(input.seedPluginIds)
  if (uniqueSeedPluginIds.length === 0) {
    return { marketplaceIdsToDelete: [], pluginMcpRequirementBindingIdsToDelete: [], pluginIdsToDelete: [] }
  }

  const connectorMarketplaceRows = await db
    .select({ marketplaceId: MarketplacePluginTable.marketplaceId })
    .from(MarketplacePluginTable)
    .innerJoin(MarketplaceTable, eq(MarketplacePluginTable.marketplaceId, MarketplaceTable.id))
    .where(and(
      inArray(MarketplacePluginTable.pluginId, uniqueSeedPluginIds),
      eq(MarketplacePluginTable.organizationId, input.organizationId),
      eq(MarketplaceTable.organizationId, input.organizationId),
      eq(MarketplacePluginTable.membershipSource, "connector"),
      isNull(MarketplacePluginTable.removedAt),
    ))
  const candidateMarketplaceIds = uniqueIds(connectorMarketplaceRows.map((row) => row.marketplaceId))

  const activeMarketplaceMemberships = candidateMarketplaceIds.length === 0
    ? []
    : await db
      .select({
        marketplaceId: MarketplacePluginTable.marketplaceId,
        membershipSource: MarketplacePluginTable.membershipSource,
        pluginId: MarketplacePluginTable.pluginId,
      })
      .from(MarketplacePluginTable)
      .where(and(
        inArray(MarketplacePluginTable.marketplaceId, candidateMarketplaceIds),
        eq(MarketplacePluginTable.organizationId, input.organizationId),
        isNull(MarketplacePluginTable.removedAt),
      ))

  const candidatePluginIds = uniqueIds([
    ...uniqueSeedPluginIds,
    ...activeMarketplaceMemberships
      .filter((membership) => membership.membershipSource === "connector")
      .map((membership) => membership.pluginId),
  ])

  const activePluginMembershipRows = candidatePluginIds.length === 0
    ? []
    : await db
      .select({ pluginId: PluginConfigObjectTable.pluginId })
      .from(PluginConfigObjectTable)
      .where(and(
        inArray(PluginConfigObjectTable.pluginId, candidatePluginIds),
        eq(PluginConfigObjectTable.organizationId, input.organizationId),
        isNull(PluginConfigObjectTable.removedAt),
      ))

  const activeMappingRows = candidatePluginIds.length === 0
    ? []
    : await db
      .select({ pluginId: ConnectorMappingTable.pluginId })
      .from(ConnectorMappingTable)
      .where(and(
        inArray(ConnectorMappingTable.pluginId, candidatePluginIds),
        eq(ConnectorMappingTable.organizationId, input.organizationId),
      ))

  const plan = planConnectorImportedResourceCleanup({
    activeMarketplaceMemberships,
    activeMappingPluginIds: activeMappingRows
      .map((row) => row.pluginId)
      .filter((pluginId): pluginId is PluginId => Boolean(pluginId)),
    activePluginMembershipPluginIds: activePluginMembershipRows.map((row) => row.pluginId),
    candidateMarketplaceIds,
    candidatePluginIds,
  })

  return {
    ...plan,
    pluginMcpRequirementBindingIdsToDelete: await pluginMcpRequirementBindingIdsForHardDeletedResources({
      configObjectIds: [],
      organizationId: input.organizationId,
      pluginIds: plan.pluginIdsToDelete,
    }),
  }
}

// Write-only delete pass. Must run inside a transaction. Contains no reads so it
// is safe to run alongside the other deletes on the same Vitess connection.
async function deleteConnectorImportedResources(input: {
  organizationId: OrganizationId
  plan: ConnectorImportedResourceCleanupPlan
  tx: DbTransaction
}) {
  const { marketplaceIdsToDelete, pluginIdsToDelete } = input.plan

  if (pluginIdsToDelete.length > 0) {
    await input.tx.delete(PluginConfigObjectTable).where(and(inArray(PluginConfigObjectTable.pluginId, pluginIdsToDelete), eq(PluginConfigObjectTable.organizationId, input.organizationId)))
    await input.tx.delete(MarketplacePluginTable).where(and(inArray(MarketplacePluginTable.pluginId, pluginIdsToDelete), eq(MarketplacePluginTable.organizationId, input.organizationId)))
    await input.tx.delete(PluginAccessGrantTable).where(and(inArray(PluginAccessGrantTable.pluginId, pluginIdsToDelete), eq(PluginAccessGrantTable.organizationId, input.organizationId)))
    await input.tx.delete(PluginTable).where(and(inArray(PluginTable.id, pluginIdsToDelete), eq(PluginTable.organizationId, input.organizationId)))
  }

  if (marketplaceIdsToDelete.length > 0) {
    await input.tx.delete(MarketplacePluginTable).where(and(inArray(MarketplacePluginTable.marketplaceId, marketplaceIdsToDelete), eq(MarketplacePluginTable.organizationId, input.organizationId)))
    await input.tx.delete(MarketplaceAccessGrantTable).where(and(inArray(MarketplaceAccessGrantTable.marketplaceId, marketplaceIdsToDelete), eq(MarketplaceAccessGrantTable.organizationId, input.organizationId)))
    await input.tx.delete(MarketplaceTable).where(and(inArray(MarketplaceTable.id, marketplaceIdsToDelete), eq(MarketplaceTable.organizationId, input.organizationId)))
  }
}

export async function getConnectorInstanceConfiguration(input: { connectorInstanceId: ConnectorInstanceId; context: PluginArchActorContext }) {
  const instance = await ensureVisibleConnectorInstance(input.context, input.connectorInstanceId)
  const mappings = await db
    .select()
    .from(ConnectorMappingTable)
    .where(eq(ConnectorMappingTable.connectorInstanceId, instance.id))
    .orderBy(desc(ConnectorMappingTable.createdAt), desc(ConnectorMappingTable.id))

  const pluginIds = [...new Set(mappings.map((row) => row.pluginId).filter((value): value is PluginId => Boolean(value)))]
  const pluginRows = pluginIds.length === 0
    ? []
    : await db.select().from(PluginTable).where(inArray(PluginTable.id, pluginIds))
  const memberships = pluginIds.length === 0
    ? []
    : await db
      .select({ pluginId: PluginConfigObjectTable.pluginId, configObjectId: PluginConfigObjectTable.configObjectId })
      .from(PluginConfigObjectTable)
      .where(and(inArray(PluginConfigObjectTable.pluginId, pluginIds), isNull(PluginConfigObjectTable.removedAt)))
  const configObjectIds = [...new Set(memberships.map((entry) => entry.configObjectId))]
  const configObjectTypeById = new Map<string, string>()
  if (configObjectIds.length > 0) {
    const rows = await db
      .select({ id: ConfigObjectTable.id, objectType: ConfigObjectTable.objectType })
      .from(ConfigObjectTable)
      .where(inArray(ConfigObjectTable.id, configObjectIds))
    for (const row of rows) {
      configObjectTypeById.set(row.id, row.objectType)
    }
  }

  const pluginComponentCounts = new Map<string, Map<string, number>>()
  const membershipCounts = new Map<string, number>()
  for (const membership of memberships) {
    membershipCounts.set(membership.pluginId, (membershipCounts.get(membership.pluginId) ?? 0) + 1)
    const objectType = configObjectTypeById.get(membership.configObjectId)
    if (!objectType) continue
    let counts = pluginComponentCounts.get(membership.pluginId)
    if (!counts) {
      counts = new Map<string, number>()
      pluginComponentCounts.set(membership.pluginId, counts)
    }
    counts.set(objectType, (counts.get(objectType) ?? 0) + 1)
  }

  const pluginRootPaths = new Map<string, string | null>()
  for (const pluginId of pluginIds) {
    const selectors = mappings
      .filter((mapping) => mapping.pluginId === pluginId)
      .map((mapping) => mapping.selector)
    pluginRootPaths.set(pluginId, commonSelectorRootPath(selectors))
  }

  const configObjectRows = await db
    .select({ id: ConfigObjectTable.id })
    .from(ConfigObjectTable)
    .where(eq(ConfigObjectTable.connectorInstanceId, instance.id))

  const instanceConfig = instance.instanceConfigJson && typeof instance.instanceConfigJson === "object"
    ? instance.instanceConfigJson as Record<string, unknown>
    : {}
  const savedAutoImport = instanceConfig.autoImportNewPlugins

  return {
    autoImportNewPlugins: typeof savedAutoImport === "boolean" ? savedAutoImport : true,
    configuredPlugins: pluginRows.map((row) => {
      const componentCounts = Object.fromEntries(pluginComponentCounts.get(row.id) ?? new Map())
      return {
        ...serializePlugin(row, membershipCounts.get(row.id) ?? 0, [], componentCounts),
        componentCounts,
        rootPath: pluginRootPaths.get(row.id) ?? null,
      }
    }),
    connectorInstance: serializeConnectorInstance(instance),
    importedConfigObjectCount: configObjectRows.length,
    mappingCount: mappings.length,
  }
}

export async function setConnectorInstanceAutoImport(input: { autoImportNewPlugins: boolean; connectorInstanceId: ConnectorInstanceId; context: PluginArchActorContext }) {
  const instance = await ensureEditableConnectorInstance(input.context, input.connectorInstanceId)
  const currentConfig = instance.instanceConfigJson && typeof instance.instanceConfigJson === "object"
    ? instance.instanceConfigJson as Record<string, unknown>
    : {}
  await db.update(ConnectorInstanceTable).set({
    instanceConfigJson: {
      ...currentConfig,
      autoImportNewPlugins: input.autoImportNewPlugins,
    },
    updatedAt: new Date(),
  }).where(eq(ConnectorInstanceTable.id, instance.id))

  return getConnectorInstanceConfiguration({ connectorInstanceId: instance.id, context: input.context })
}

export async function removeConnectorInstance(input: { connectorInstanceId: ConnectorInstanceId; context: PluginArchActorContext }) {
  const instance = await ensureEditableConnectorInstance(input.context, input.connectorInstanceId)

  const mappingRows = await db
    .select({ id: ConnectorMappingTable.id, pluginId: ConnectorMappingTable.pluginId })
    .from(ConnectorMappingTable)
    .where(eq(ConnectorMappingTable.connectorInstanceId, instance.id))
  const mappingIds = mappingRows.map((entry) => entry.id)
  const pluginIds = [...new Set(mappingRows.map((entry) => entry.pluginId).filter((value): value is PluginId => Boolean(value)))]

  const configObjectRows = await db
    .select({ id: ConfigObjectTable.id })
    .from(ConfigObjectTable)
    .where(eq(ConfigObjectTable.connectorInstanceId, instance.id))
  const configObjectIds = configObjectRows.map((entry) => entry.id)

  // Resolve every imported marketplace/plugin id to delete up front so the
  // transaction below is a single pass of pure writes (no reads on the tx).
  const importedResourceCleanupPlan = await planConnectorImportedResourceCleanupIds({ organizationId: instance.organizationId, seedPluginIds: pluginIds })
  const pluginMcpRequirementBindingIdsToDelete = await pluginMcpRequirementBindingIdsForHardDeletedResources({
    configObjectIds,
    organizationId: instance.organizationId,
    pluginIds: importedResourceCleanupPlan.pluginIdsToDelete,
  })
  importedResourceCleanupPlan.pluginMcpRequirementBindingIdsToDelete = uniqueIds([
    ...importedResourceCleanupPlan.pluginMcpRequirementBindingIdsToDelete,
    ...pluginMcpRequirementBindingIdsToDelete,
  ])

  await db.transaction(async (tx) => {
    await deletePluginMcpRequirementBindingsForHardDelete({
      bindingIds: importedResourceCleanupPlan.pluginMcpRequirementBindingIdsToDelete,
      tx,
    })

    await tx.delete(ConnectorSourceTombstoneTable).where(eq(ConnectorSourceTombstoneTable.connectorInstanceId, instance.id))
    await tx.delete(ConnectorSourceBindingTable).where(eq(ConnectorSourceBindingTable.connectorInstanceId, instance.id))
    await tx.delete(ConnectorSyncEventTable).where(eq(ConnectorSyncEventTable.connectorInstanceId, instance.id))

    if (configObjectIds.length > 0) {
      await tx.delete(PluginConfigObjectTable).where(inArray(PluginConfigObjectTable.configObjectId, configObjectIds))
      await tx.delete(ConfigObjectAccessGrantTable).where(inArray(ConfigObjectAccessGrantTable.configObjectId, configObjectIds))
      await tx.delete(ConfigObjectVersionTable).where(inArray(ConfigObjectVersionTable.configObjectId, configObjectIds))
      await tx.delete(ConfigObjectTable).where(inArray(ConfigObjectTable.id, configObjectIds))
    }

    if (mappingIds.length > 0) {
      await tx.delete(PluginConfigObjectTable).where(inArray(PluginConfigObjectTable.connectorMappingId, mappingIds))
      await tx.delete(ConnectorMappingTable).where(inArray(ConnectorMappingTable.id, mappingIds))
    }

    await tx.delete(ConnectorTargetTable).where(eq(ConnectorTargetTable.connectorInstanceId, instance.id))
    await tx.delete(ConnectorInstanceAccessGrantTable).where(eq(ConnectorInstanceAccessGrantTable.connectorInstanceId, instance.id))
    await tx.delete(ConnectorInstanceTable).where(eq(ConnectorInstanceTable.id, instance.id))

    await deleteConnectorImportedResources({ organizationId: instance.organizationId, plan: importedResourceCleanupPlan, tx })
  })

  return {
    deletedConfigObjectCount: configObjectIds.length,
    deletedConnectorMappingCount: mappingIds.length,
    removedConnectorInstanceId: instance.id,
  }
}

export async function listConnectorTargets(input: { connectorInstanceId: ConnectorInstanceId; context: PluginArchActorContext; cursor?: string; limit?: number; q?: string; targetKind?: ConnectorTargetRow["targetKind"] }) {
  await ensureVisibleConnectorInstance(input.context, input.connectorInstanceId)
  const rows = await db
    .select()
    .from(ConnectorTargetTable)
    .where(eq(ConnectorTargetTable.connectorInstanceId, input.connectorInstanceId))
    .orderBy(desc(ConnectorTargetTable.updatedAt), desc(ConnectorTargetTable.id))

  const filtered = rows
    .filter((row) => !input.targetKind || row.targetKind === input.targetKind)
    .filter((row) => !input.q || `${row.remoteId}\n${row.externalTargetRef ?? ""}`.toLowerCase().includes(input.q.toLowerCase()))
    .map((row) => serializeConnectorTarget(row))

  return pageItems(filtered, input.cursor, input.limit)
}

export async function createConnectorTarget(input: { config: Record<string, unknown>; connectorInstanceId: ConnectorInstanceId; connectorType: ConnectorTargetRow["connectorType"]; context: PluginArchActorContext; externalTargetRef?: string | null; remoteId: string; targetKind: ConnectorTargetRow["targetKind"] }) {
  await ensureEditableConnectorInstance(input.context, input.connectorInstanceId)
  const row = {
    connectorInstanceId: input.connectorInstanceId,
    connectorType: input.connectorType,
    createdAt: new Date(),
    externalTargetRef: normalizeOptionalString(input.externalTargetRef ?? undefined),
    id: createDenTypeId("connectorTarget"),
    organizationId: input.context.organizationContext.organization.id,
    remoteId: input.remoteId.trim(),
    targetConfigJson: input.config,
    targetKind: input.targetKind,
    updatedAt: new Date(),
  }
  await db.insert(ConnectorTargetTable).values(row)
  return serializeConnectorTarget(row)
}

export async function getConnectorTargetDetail(context: PluginArchActorContext, connectorTargetId: ConnectorTargetId) {
  const target = await getConnectorTargetRow(context.organizationContext.organization.id, connectorTargetId)
  if (!target) throw new PluginArchRouteFailure(404, "connector_target_not_found", "Connector target not found.")
  await ensureVisibleConnectorInstance(context, target.connectorInstanceId)
  return serializeConnectorTarget(target)
}

export async function updateConnectorTarget(input: { config?: Record<string, unknown>; connectorTargetId: ConnectorTargetId; context: PluginArchActorContext; externalTargetRef?: string | null; remoteId?: string }) {
  const target = await getConnectorTargetRow(input.context.organizationContext.organization.id, input.connectorTargetId)
  if (!target) throw new PluginArchRouteFailure(404, "connector_target_not_found", "Connector target not found.")
  await ensureEditableConnectorInstance(input.context, target.connectorInstanceId)
  await db.update(ConnectorTargetTable).set({
    externalTargetRef: input.externalTargetRef === undefined ? target.externalTargetRef : normalizeOptionalString(input.externalTargetRef ?? undefined),
    remoteId: input.remoteId?.trim() || target.remoteId,
    targetConfigJson: input.config === undefined ? target.targetConfigJson : input.config,
    updatedAt: new Date(),
  }).where(eq(ConnectorTargetTable.id, target.id))
  return getConnectorTargetDetail(input.context, target.id)
}

export async function queueConnectorTargetResync(input: { connectorTargetId: ConnectorTargetId; context: PluginArchActorContext }) {
  const target = await getConnectorTargetRow(input.context.organizationContext.organization.id, input.connectorTargetId)
  if (!target) throw new PluginArchRouteFailure(404, "connector_target_not_found", "Connector target not found.")
  const instance = await ensureEditableConnectorInstance(input.context, target.connectorInstanceId)
  const eventId = createDenTypeId("connectorSyncEvent")
  await db.insert(ConnectorSyncEventTable).values({
    completedAt: null,
    connectorInstanceId: instance.id,
    connectorTargetId: target.id,
    connectorType: target.connectorType,
    eventType: "manual_resync",
    externalEventRef: null,
    id: eventId,
    organizationId: instance.organizationId,
    remoteId: target.remoteId,
    sourceRevisionRef: null,
    startedAt: new Date(),
    status: "queued",
    summaryJson: { queuedBy: input.context.organizationContext.currentMember.id },
  })
  return { id: eventId }
}

export async function listConnectorMappings(input: { connectorTargetId: ConnectorTargetId; context: PluginArchActorContext; cursor?: string; limit?: number; mappingKind?: ConnectorMappingRow["mappingKind"]; objectType?: ConnectorMappingRow["objectType"]; pluginId?: PluginId; q?: string }) {
  const target = await getConnectorTargetRow(input.context.organizationContext.organization.id, input.connectorTargetId)
  if (!target) throw new PluginArchRouteFailure(404, "connector_target_not_found", "Connector target not found.")
  await ensureVisibleConnectorInstance(input.context, target.connectorInstanceId)
  const rows = await db.select().from(ConnectorMappingTable).where(eq(ConnectorMappingTable.connectorTargetId, target.id)).orderBy(desc(ConnectorMappingTable.updatedAt), desc(ConnectorMappingTable.id))
  const filtered = rows
    .filter((row) => !input.mappingKind || row.mappingKind === input.mappingKind)
    .filter((row) => !input.objectType || row.objectType === input.objectType)
    .filter((row) => !input.pluginId || row.pluginId === input.pluginId)
    .filter((row) => !input.q || `${row.selector}\n${row.remoteId ?? ""}`.toLowerCase().includes(input.q.toLowerCase()))
    .map((row) => serializeConnectorMapping(row))
  return pageItems(filtered, input.cursor, input.limit)
}

export async function createConnectorMapping(input: { autoAddToPlugin: boolean; config?: Record<string, unknown>; connectorTargetId: ConnectorTargetId; context: PluginArchActorContext; mappingKind: ConnectorMappingRow["mappingKind"]; objectType: ConnectorMappingRow["objectType"]; pluginId?: PluginId | null; selector: string }) {
  const target = await getConnectorTargetRow(input.context.organizationContext.organization.id, input.connectorTargetId)
  if (!target) throw new PluginArchRouteFailure(404, "connector_target_not_found", "Connector target not found.")
  await ensureEditableConnectorInstance(input.context, target.connectorInstanceId)
  if (input.pluginId) {
    await ensureEditablePlugin(input.context, input.pluginId)
  }
  const row = {
    autoAddToPlugin: input.autoAddToPlugin,
    connectorInstanceId: target.connectorInstanceId,
    connectorTargetId: target.id,
    connectorType: target.connectorType,
    createdAt: new Date(),
    id: createDenTypeId("connectorMapping"),
    mappingConfigJson: input.config ?? null,
    mappingKind: input.mappingKind,
    objectType: input.objectType,
    organizationId: input.context.organizationContext.organization.id,
    pluginId: input.pluginId ?? null,
    remoteId: null,
    selector: input.selector.trim(),
    updatedAt: new Date(),
  }
  await db.insert(ConnectorMappingTable).values(row)
  return serializeConnectorMapping(row)
}

export async function updateConnectorMapping(input: { autoAddToPlugin?: boolean; config?: Record<string, unknown>; connectorMappingId: ConnectorMappingId; context: PluginArchActorContext; objectType?: ConnectorMappingRow["objectType"]; pluginId?: PluginId | null; selector?: string }) {
  const mapping = await getConnectorMappingRow(input.context.organizationContext.organization.id, input.connectorMappingId)
  if (!mapping) throw new PluginArchRouteFailure(404, "connector_mapping_not_found", "Connector mapping not found.")
  await ensureEditableConnectorInstance(input.context, mapping.connectorInstanceId)
  if (input.pluginId) {
    await ensureEditablePlugin(input.context, input.pluginId)
  }
  await db.update(ConnectorMappingTable).set({
    autoAddToPlugin: input.autoAddToPlugin ?? mapping.autoAddToPlugin,
    mappingConfigJson: input.config === undefined ? mapping.mappingConfigJson : input.config,
    objectType: input.objectType ?? mapping.objectType,
    pluginId: input.pluginId === undefined ? mapping.pluginId : input.pluginId,
    selector: input.selector?.trim() || mapping.selector,
    updatedAt: new Date(),
  }).where(eq(ConnectorMappingTable.id, mapping.id))
  return serializeConnectorMapping({ ...mapping, autoAddToPlugin: input.autoAddToPlugin ?? mapping.autoAddToPlugin, mappingConfigJson: input.config === undefined ? mapping.mappingConfigJson : input.config, objectType: input.objectType ?? mapping.objectType, pluginId: input.pluginId === undefined ? mapping.pluginId : input.pluginId, selector: input.selector?.trim() || mapping.selector, updatedAt: new Date() })
}

export async function deleteConnectorMapping(input: { connectorMappingId: ConnectorMappingId; context: PluginArchActorContext }) {
  const mapping = await getConnectorMappingRow(input.context.organizationContext.organization.id, input.connectorMappingId)
  if (!mapping) throw new PluginArchRouteFailure(404, "connector_mapping_not_found", "Connector mapping not found.")
  await ensureEditableConnectorInstance(input.context, mapping.connectorInstanceId)
  const bindingIds = await pluginMcpRequirementBindingIdsForConnectorMapping({
    connectorMappingId: mapping.id,
    organizationId: mapping.organizationId,
  })
  await db.transaction(async (tx) => {
    await deletePluginMcpRequirementBindingsForHardDelete({ bindingIds, tx })
    await tx.delete(PluginConfigObjectTable).where(eq(PluginConfigObjectTable.connectorMappingId, mapping.id))
    await tx.delete(ConnectorMappingTable).where(eq(ConnectorMappingTable.id, mapping.id))
  })
}

export async function listConnectorSyncEvents(input: { connectorInstanceId?: ConnectorInstanceId; connectorTargetId?: ConnectorTargetId; context: PluginArchActorContext; cursor?: string; eventType?: ConnectorSyncEventRow["eventType"]; limit?: number; q?: string; status?: ConnectorSyncEventRow["status"] }) {
  const rows = await db
    .select({ event: ConnectorSyncEventTable, instance: ConnectorInstanceTable })
    .from(ConnectorSyncEventTable)
    .innerJoin(ConnectorInstanceTable, eq(ConnectorSyncEventTable.connectorInstanceId, ConnectorInstanceTable.id))
    .where(eq(ConnectorInstanceTable.organizationId, input.context.organizationContext.organization.id))
    .orderBy(desc(ConnectorSyncEventTable.startedAt), desc(ConnectorSyncEventTable.id))

  const filtered: ReturnType<typeof serializeConnectorSyncEvent>[] = []
  for (const row of rows) {
    const role = await resolvePluginArchResourceRole({ context: input.context, resourceId: row.instance.id, resourceKind: "connector_instance" })
    if (!role) continue
    if (input.connectorInstanceId && row.event.connectorInstanceId !== input.connectorInstanceId) continue
    if (input.connectorTargetId && row.event.connectorTargetId !== input.connectorTargetId) continue
    if (input.eventType && row.event.eventType !== input.eventType) continue
    if (input.status && row.event.status !== input.status) continue
    if (input.q && !`${row.event.externalEventRef ?? ""}\n${row.event.sourceRevisionRef ?? ""}`.toLowerCase().includes(input.q.toLowerCase())) continue
    filtered.push(serializeConnectorSyncEvent(row.event))
  }
  return pageItems(filtered, input.cursor, input.limit)
}

export async function getConnectorSyncEventDetail(context: PluginArchActorContext, connectorSyncEventId: ConnectorSyncEventId) {
  const row = await getConnectorSyncEventRow(context.organizationContext.organization.id, connectorSyncEventId)
  if (!row) throw new PluginArchRouteFailure(404, "connector_sync_event_not_found", "Connector sync event not found.")
  await ensureVisibleConnectorInstance(context, row.connectorInstanceId)
  return serializeConnectorSyncEvent(row)
}

export async function retryConnectorSyncEvent(input: { connectorSyncEventId: ConnectorSyncEventId; context: PluginArchActorContext }) {
  const row = await getConnectorSyncEventRow(input.context.organizationContext.organization.id, input.connectorSyncEventId)
  if (!row) throw new PluginArchRouteFailure(404, "connector_sync_event_not_found", "Connector sync event not found.")
  await ensureEditableConnectorInstance(input.context, row.connectorInstanceId)
  await db.update(ConnectorSyncEventTable).set({
    attemptCount: 0,
    completedAt: null,
    nextAttemptAt: null,
    startedAt: new Date(),
    status: "queued",
  }).where(eq(ConnectorSyncEventTable.id, row.id))
  return { id: row.id }
}

export async function syncConnectorInstanceNow(input: { connectorInstanceId: ConnectorInstanceId; context: PluginArchActorContext }) {
  const instance = await ensureEditableConnectorInstance(input.context, input.connectorInstanceId)
  const targets = await db
    .select()
    .from(ConnectorTargetTable)
    .where(eq(ConnectorTargetTable.connectorInstanceId, instance.id))
    .orderBy(asc(ConnectorTargetTable.createdAt), asc(ConnectorTargetTable.id))

  let enqueuedCount = 0
  for (const target of targets) {
    const activeEvents = await db
      .select({ id: ConnectorSyncEventTable.id })
      .from(ConnectorSyncEventTable)
      .where(and(
        eq(ConnectorSyncEventTable.connectorTargetId, target.id),
        inArray(ConnectorSyncEventTable.status, ["queued", "running"]),
      ))
      .limit(1)
    if (activeEvents[0]) continue

    await db.insert(ConnectorSyncEventTable).values({
      connectorInstanceId: instance.id,
      connectorTargetId: target.id,
      connectorType: target.connectorType,
      eventType: "manual_resync",
      externalEventRef: null,
      id: createDenTypeId("connectorSyncEvent"),
      organizationId: instance.organizationId,
      remoteId: target.remoteId,
      sourceRevisionRef: null,
      startedAt: new Date(),
      status: "queued",
      summaryJson: { trigger: "manual" },
    })
    enqueuedCount += 1
  }

  return { enqueuedCount }
}

function githubConnectorAppConfig() {
  try {
    return getGithubConnectorAppConfig(env.githubConnectorApp)
  } catch (error) {
    if (error instanceof GithubConnectorConfigError) {
      throw new PluginArchRouteFailure(409, "github_connector_app_not_configured", error.message)
    }
    throw error
  }
}

export function consumeGithubInstallState(state: string) {
  const parsed = verifyGithubInstallStateToken({ secret: env.betterAuthSecret, token: state })
  if (!parsed) {
    throw new PluginArchRouteFailure(400, "invalid_github_install_state", "GitHub install state is invalid or expired.")
  }
  return parsed
}

function wrapGithubConnectorError(error: unknown): never {
  if (error instanceof PluginArchRouteFailure) {
    throw error
  }

  if (error instanceof GithubConnectorConfigError) {
    throw new PluginArchRouteFailure(409, "github_connector_app_not_configured", error.message)
  }

  if (error instanceof GithubConnectorRequestError) {
    throw new PluginArchRouteFailure(409, "github_connector_request_failed", error.message, { cause: error })
  }

  throw error
}

function normalizeDiscoveryCursor(value: string | undefined) {
  return value?.trim() || undefined
}

function discoveryStep(status: GithubConnectorDiscoveryStep["status"], id: GithubConnectorDiscoveryStep["id"], label: string): GithubConnectorDiscoveryStep {
  return { id, label, status }
}

function buildGithubConnectorDiscoverySteps(input: {
  classification: GithubDiscoveryClassification
  discoveredPlugins: GithubDiscoveredPlugin[]
}) {
  return [
    discoveryStep("completed", "read_repository_structure", "Read repository structure"),
    discoveryStep(input.classification === "claude_marketplace_repo" ? "completed" : "warning", "check_marketplace_manifest", "Check for Claude marketplace manifest"),
    discoveryStep(
      input.classification === "agent_plugin_repo"
        || input.classification === "claude_single_plugin_repo"
        || input.classification === "claude_multi_plugin_repo"
        ? "completed"
        : "warning",
      "check_plugin_manifests",
      "Check for Agent Plugins or Claude plugin manifests",
    ),
    discoveryStep(input.discoveredPlugins.length > 0 ? "completed" : "warning", "prepare_discovered_plugins", "Prepare discovered plugins"),
  ] satisfies GithubConnectorDiscoveryStep[]
}

function buildGithubDiscoveryImportPlans(input: { discoveredPlugins: GithubDiscoveredPlugin[]; treeEntries: GithubDiscoveryTreeEntry[] }) {
  return Object.fromEntries(input.discoveredPlugins.map((plugin) => [
    plugin.key,
    discoveryMappingsForPlugin(plugin).map((mapping) => {
      const entries = importableGithubPathsForMapping({ mapping, treeEntries: input.treeEntries })
      const fileShaByPath: Record<string, string> = {}
      for (const entry of entries) {
        if (entry.sha) {
          fileShaByPath[entry.path] = entry.sha
        }
      }
      return {
        fileShaByPath,
        objectType: mapping.objectType,
        paths: entries.map((entry) => entry.path),
        selector: mapping.selector,
      } satisfies GithubDiscoveryImportPlan
    }),
  ])) satisfies Record<string, GithubDiscoveryImportPlan[]>
}

function slugifyPluginMcpName(value: string) {
  return value
    .trim()
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/^-+|-+$/g, "")
    .slice(0, 80) || "mcp"
}

function externalMcpConnectionName(input: { pluginName: string; serverName: string }) {
  const serverName = input.serverName.trim()
  const pluginName = input.pluginName.trim()
  if (!pluginName) return serverName || "Imported MCP"
  if (!serverName) return pluginName
  return `${pluginName} / ${serverName}`
}

function githubPluginMcpServerKey(input: { name: string; pluginKey: string; sourcePath: string; url: string | null }) {
  return [input.pluginKey, input.sourcePath, input.name, input.url ?? ""].map(encodeURIComponent).join(":")
}

function githubPluginMcpImportServer(input: Omit<GithubPluginMcpImportServer, "serverKey">): GithubPluginMcpImportServer {
  return {
    ...input,
    serverKey: githubPluginMcpServerKey(input),
  }
}

function isLoopbackMcpHostname(hostname: string) {
  const normalized = hostname.toLowerCase().replace(/^\[|\]$/g, "")
  return normalized === "localhost"
    || normalized === "::1"
    || normalized.startsWith("127.")
}

export function mcpServerEntriesFromPayload(input: {
  plugin: GithubDiscoveredPlugin
  rawSourceText: string
  sourcePath: string
}): GithubPluginMcpImportServer[] {
  const isAgentPlugin = input.plugin.sourceKind === "agent_plugin_manifest"
  const sourceSchemaVersion = isAgentPlugin ? input.plugin.sourceSchemaVersion : null
  let invalidAgentEntries: GithubPluginMcpImportServer[] = []
  let fallbackEntries: Array<[string, unknown]>
  if (isAgentPlugin) {
    const parsed = parseAgentPluginV1McpText(input.rawSourceText, input.plugin.sourceSchemaVersion)
    if (!parsed.ok) {
      return [githubPluginMcpImportServer({
        authType: null,
        connectionId: null,
        name: input.sourcePath,
        pluginKey: input.plugin.key,
        pluginName: input.plugin.displayName,
        skippedReason: "invalid_config",
        sourceSchemaVersion,
        sourcePath: input.sourcePath,
        supported: false,
        url: null,
      })]
    }
    invalidAgentEntries = parsed.entries.filter((entry) => !entry.valid).map((entry) => githubPluginMcpImportServer({
      authType: null,
      connectionId: null,
      name: entry.name || input.plugin.displayName,
      pluginKey: input.plugin.key,
      pluginName: input.plugin.displayName,
      skippedReason: "invalid_config",
      sourceSchemaVersion,
      sourcePath: input.sourcePath,
      supported: false,
      url: typeof entry.config.url === "string" ? entry.config.url : null,
    }))
    const validEntries = parsed.entries.filter((entry) => entry.valid).map((entry) => [entry.name, entry.config] satisfies [string, unknown])
    if (validEntries.length === 0) return invalidAgentEntries
    fallbackEntries = validEntries
  } else {
    let parsed: unknown
    try {
      parsed = JSON.parse(input.rawSourceText)
    } catch {
      return [githubPluginMcpImportServer({
        authType: null,
        connectionId: null,
        name: input.sourcePath,
        pluginKey: input.plugin.key,
        pluginName: input.plugin.displayName,
        skippedReason: "invalid_url",
        sourceSchemaVersion,
        sourcePath: input.sourcePath,
        supported: false,
        url: null,
      })]
    }

    const root = isRecord(parsed) ? parsed : {}
    const containers = [
      isRecord(root.mcpServers) ? root.mcpServers : null,
      isRecord(root.mcp) ? root.mcp : null,
    ].filter((entry): entry is Record<string, unknown> => Boolean(entry))
    const entries = containers.flatMap((container) => Object.entries(container))
    fallbackEntries = entries.length > 0 ? entries : [[input.plugin.displayName, root]]
  }

  return [...invalidAgentEntries, ...fallbackEntries.map(([rawName, rawConfig]) => {
    const config = isRecord(rawConfig) ? rawConfig : {}
    const name = rawName.trim() || input.plugin.displayName
    const url = typeof config.url === "string" ? config.url.trim() : ""
    const type = typeof config.type === "string" ? config.type.trim().toLowerCase() : ""
    const command = typeof config.command === "string"
      ? config.command.trim()
      : Array.isArray(config.command) && config.command.some((part) => typeof part === "string" && part.trim())
        ? "local command"
        : ""

    if (isAgentPlugin && isRecord(config.headers) && Object.keys(config.headers).length > 0) {
      return githubPluginMcpImportServer({
        authType: null,
        connectionId: null,
        name,
        pluginKey: input.plugin.key,
        pluginName: input.plugin.displayName,
        skippedReason: "headers_unsupported",
        sourceSchemaVersion,
        sourcePath: input.sourcePath,
        supported: false,
        url: typeof config.url === "string" ? config.url : null,
      })
    }

    if (!url) {
      return githubPluginMcpImportServer({
        authType: null,
        connectionId: null,
        name,
        pluginKey: input.plugin.key,
        pluginName: input.plugin.displayName,
        skippedReason: command ? "local_unsupported" : "missing_url",
        sourceSchemaVersion,
        sourcePath: input.sourcePath,
        supported: false,
        url: null,
      })
    }

    let parsedUrl: URL
    try {
      parsedUrl = new URL(url)
    } catch {
      return githubPluginMcpImportServer({
        authType: null,
        connectionId: null,
        name,
        pluginKey: input.plugin.key,
        pluginName: input.plugin.displayName,
        skippedReason: "invalid_url",
        sourceSchemaVersion,
        sourcePath: input.sourcePath,
        supported: false,
        url,
      })
    }

    if (
      (parsedUrl.protocol !== "http:" && parsedUrl.protocol !== "https:")
      || (isAgentPlugin && parsedUrl.protocol === "http:" && !isLoopbackMcpHostname(parsedUrl.hostname))
      || (isAgentPlugin && Boolean(parsedUrl.username || parsedUrl.password))
      || (isAgentPlugin && Boolean(parsedUrl.hash))
    ) {
      return githubPluginMcpImportServer({
        authType: null,
        connectionId: null,
        name,
        pluginKey: input.plugin.key,
        pluginName: input.plugin.displayName,
        skippedReason: "invalid_url",
        sourceSchemaVersion,
        sourcePath: input.sourcePath,
        supported: false,
        url,
      })
    }

    if (type && type !== "http" && type !== "remote" && type !== "streamable-http" && type !== "sse") {
      return githubPluginMcpImportServer({
        authType: null,
        connectionId: null,
        name,
        pluginKey: input.plugin.key,
        pluginName: input.plugin.displayName,
        skippedReason: "local_unsupported",
        sourceSchemaVersion,
        sourcePath: input.sourcePath,
        supported: false,
        url,
      })
    }

    return githubPluginMcpImportServer({
      authType: declaredPluginMcpAuthType(config),
      connectionId: null,
      name,
      pluginKey: input.plugin.key,
      pluginName: input.plugin.displayName,
      skippedReason: null,
      sourceSchemaVersion,
      sourcePath: input.sourcePath,
      supported: true,
      url,
    })
  })]
}

function githubPluginSkillKey(input: { pluginKey: string; sourcePath: string }) {
  return [input.pluginKey, input.sourcePath].map(encodeURIComponent).join(":")
}

function skillMetadataFromText(skillText: string) {
  const parsed = parseSkillMarkdown(skillText)
  if (parsed.hasFrontmatter) {
    const title = parsed.name.trim() || "Untitled skill"
    const description = parsed.description.trim() || null
    return {
      description: description ? description.slice(0, 65535) : null,
      title: title.slice(0, 255),
    }
  }

  const lines = skillText
    .split(/\r?\n/g)
    .map((line) => line.trim())
    .filter(Boolean)

  const cleanup = (value: string) => value
    .replace(/^#{1,6}\s+/, "")
    .replace(/^[-*+]\s+/, "")
    .replace(/^title\s*:\s*/i, "")
    .replace(/^description\s*:\s*/i, "")
    .trim()

  const title = cleanup(lines[0] ?? "") || "Untitled skill"
  const description = lines.slice(1).map(cleanup).find(Boolean) ?? null

  return {
    description: description ? description.slice(0, 65535) : null,
    title: title.slice(0, 255),
  }
}

export function skillEntryFromSource(input: {
  includeRawSourceText: boolean
  plugin: GithubDiscoveredPlugin
  rawSourceText: string
  sourcePath: string
}): GithubPluginSkillImportSkill {
  const metadata = skillMetadataFromText(input.rawSourceText)
  const base = {
    description: metadata.description,
    name: metadata.title,
    pluginKey: input.plugin.key,
    pluginName: input.plugin.displayName,
    skillKey: githubPluginSkillKey({ pluginKey: input.plugin.key, sourcePath: input.sourcePath }),
    sourceSchemaVersion: input.plugin.sourceKind === "agent_plugin_manifest" ? input.plugin.sourceSchemaVersion : null,
    sourcePath: input.sourcePath,
  }
  if (input.plugin.sourceKind === "agent_plugin_manifest") {
    try {
      const projection = deriveSkillProjection({ rawSourceText: input.rawSourceText })
      const pathSegments = input.sourcePath.split("/").filter(Boolean)
      const skillDirectoryName = pathSegments.at(-2) ?? ""
      if (projection.title !== skillDirectoryName) {
        throw new Error("Agent Skill name must match its parent directory.")
      }
      return {
        ...base,
        description: projection.description,
        name: projection.title,
        rawSourceText: input.includeRawSourceText ? input.rawSourceText : undefined,
        skippedReason: null,
        supported: true,
      }
    } catch {
      return {
        ...base,
        skippedReason: "invalid_skill",
        supported: false,
      }
    }
  }
  if (!input.rawSourceText.trim() || !hasSkillFrontmatterName(input.rawSourceText)) {
    return {
      ...base,
      skippedReason: "invalid_skill",
      supported: false,
    }
  }
  return {
    ...base,
    rawSourceText: input.includeRawSourceText ? input.rawSourceText : undefined,
    skippedReason: null,
    supported: true,
  }
}

async function computeGithubPluginMcpImportPlan(input: { githubUrl: string; includeSkillText?: boolean }): Promise<GithubPluginMcpImportPlan> {
  const target = parsePublicGithubPluginUrl(input.githubUrl)
  const snapshot = await getPublicGithubRepositoryTree(target)
  const fileTextByPath = await getPublicGithubDiscoveryFileTexts(snapshot)
  const discovery = buildGithubRepoDiscovery({
    entries: snapshot.treeEntries,
    fileTextByPath,
  })
  const importPlansByPluginKey = buildGithubDiscoveryImportPlans({
    discoveredPlugins: discovery.discoveredPlugins,
    treeEntries: snapshot.treeEntries,
  })

  const servers: GithubPluginMcpImportServer[] = []
  const skills: GithubPluginSkillImportSkill[] = []
  for (const plugin of discovery.discoveredPlugins.filter((entry) => entry.supported)) {
    const componentPlans = importPlansByPluginKey[plugin.key] ?? []
    for (const plan of componentPlans.filter((entry) => entry.objectType === "mcp")) {
      for (const path of plan.paths) {
        const rawSourceText = await getPublicGithubTextFile({
          branch: snapshot.branch,
          discoveryPath: path,
          snapshot,
        })
        if (!rawSourceText) continue
        servers.push(...mcpServerEntriesFromPayload({
          plugin,
          rawSourceText,
          sourcePath: path,
        }))
      }
    }
    for (const plan of componentPlans.filter((entry) => entry.objectType === "skill")) {
      for (const path of plan.paths) {
        const rawSourceText = await getPublicGithubTextFile({
          branch: snapshot.branch,
          discoveryPath: path,
          snapshot,
        })
        if (!rawSourceText) continue
        skills.push(skillEntryFromSource({
          includeRawSourceText: input.includeSkillText === true,
          plugin,
          rawSourceText,
          sourcePath: path,
        }))
      }
    }
  }

  const plugins = discovery.discoveredPlugins
    .filter((plugin) => plugin.supported)
    .map((plugin) => ({
      description: plugin.description,
      key: plugin.key,
      mcpCount: servers.filter((server) => server.pluginKey === plugin.key && server.supported).length,
      name: plugin.displayName,
      skillCount: skills.filter((skill) => skill.pluginKey === plugin.key && skill.supported).length,
    } satisfies GithubPluginMcpImportPlugin))
    .filter((plugin) => plugin.mcpCount > 0 || plugin.skillCount > 0)
  const sourceSchemaVersions = new Set(discovery.discoveredPlugins
    .filter((plugin) => plugin.supported && plugin.sourceKind === "agent_plugin_manifest")
    .flatMap((plugin) => plugin.sourceSchemaVersion ? [plugin.sourceSchemaVersion] : []))

  return {
    branch: snapshot.branch,
    classification: discovery.classification,
    marketplace: discovery.marketplace,
    plugins,
    repositoryFullName: snapshot.repositoryFullName,
    rootPath: snapshot.rootPath,
    servers,
    skills,
    sourceSchemaVersion: sourceSchemaVersions.size === 1 ? [...sourceSchemaVersions][0] ?? null : null,
    sourceRevisionRef: snapshot.headSha,
    warnings: [
      ...discovery.warnings,
      ...(snapshot.truncated ? ["GitHub truncated the repository tree; some MCP files may be missing."] : []),
    ],
  }
}

function serializePluginMcpRequirementBinding(row: PluginMcpRequirementBindingRow) {
  return {
    configObjectId: row.configObjectId,
    externalMcpConnectionId: row.externalMcpConnectionId,
    id: row.id,
    pluginId: row.pluginId,
    serverName: row.serverName,
  }
}

function isExternalMcpConnectionReady(row: ExternalMcpConnectionRow) {
  if (row.credentialMode === "per_member") return true
  return Boolean(row.accessToken || row.apiKey || (row.authType === "none" && row.connectedAt))
}

function serializePluginMcpRequirementConnection(row: ExternalMcpConnectionRow) {
  return {
    authType: row.authType,
    connected: isExternalMcpConnectionReady(row),
    connectedAt: row.connectedAt ? row.connectedAt.toISOString() : null,
    credentialMode: row.credentialMode,
    id: row.id,
    name: row.name,
    url: row.url,
  }
}

function parseConfigObjectVersionSpec(row: ConfigObjectVersionRow): Record<string, unknown> {
  if (row.normalizedPayloadJson) return row.normalizedPayloadJson
  if (!row.rawSourceText) return {}
  try {
    const parsed: unknown = JSON.parse(row.rawSourceText)
    return isRecord(parsed) ? parsed : {}
  } catch {
    return {}
  }
}

function readRecordString(record: Record<string, unknown>, key: string) {
  const value = record[key]
  return typeof value === "string" ? value.trim() : ""
}

function parseConfigObjectInputSpec(input: ConfigObjectInput): Record<string, unknown> {
  if (input.normalizedPayloadJson) return input.normalizedPayloadJson
  if (!input.rawSourceText) return {}
  try {
    const parsed: unknown = JSON.parse(input.rawSourceText)
    return isRecord(parsed) ? parsed : {}
  } catch {
    return {}
  }
}

async function deleteStalePluginMcpRequirementBindingsForConfigObject(input: {
  configObject: ConfigObjectRow
  spec: Record<string, unknown>
}) {
  if (input.configObject.objectType !== "mcp") return
  const entries = new Map(marketplaceMcpServerEntries(input.spec, input.configObject.title).flatMap((entry) => {
    const url = readRecordString(entry.config, "url")
    return url ? [[entry.name, url]] : []
  }))
  const bindings = await db
    .select({ binding: PluginMcpRequirementBindingTable, connection: ExternalMcpConnectionTable })
    .from(PluginMcpRequirementBindingTable)
    .innerJoin(ExternalMcpConnectionTable, eq(ExternalMcpConnectionTable.id, PluginMcpRequirementBindingTable.externalMcpConnectionId))
    .where(and(
      eq(PluginMcpRequirementBindingTable.organizationId, input.configObject.organizationId),
      eq(PluginMcpRequirementBindingTable.configObjectId, input.configObject.id),
    ))
  const staleBindingIds = bindings.flatMap((row) => {
    const declaredUrl = entries.get(row.binding.serverName)
    if (!declaredUrl) return [row.binding.id]
    return comparablePluginMcpRequirementUrl(row.connection.url) === comparablePluginMcpRequirementUrl(declaredUrl)
      ? []
      : [row.binding.id]
  })
  await deletePluginMcpRequirementBindingsByIds({ bindingIds: staleBindingIds })
}

function mcpRequirementServerFromVersion(input: {
  configObject: ConfigObjectRow
  serverName: string
  version: ConfigObjectVersionRow
}): PluginMcpRequirementServer {
  const spec = parseConfigObjectVersionSpec(input.version)
  const serverName = input.serverName.trim()
  const entry = marketplaceMcpServerEntries(spec, input.configObject.title).find((candidate) => candidate.name === serverName)
  if (!entry) {
    throw new PluginArchRouteFailure(404, "mcp_server_not_found", "MCP server declaration not found on this config object.")
  }

  const url = readRecordString(entry.config, "url")
  if (!url) {
    throw new PluginArchRouteFailure(400, "mcp_server_not_remote", "Only declared remote MCP servers with a URL can be configured.")
  }

  return { config: entry.config, name: entry.name, url }
}

function configVersionOwnsImportedExternalMcpConnection(version: ConfigObjectVersionRow, connectionId: string) {
  const spec = parseConfigObjectVersionSpec(version)
  const metadata = isRecord(spec.metadata) ? spec.metadata : null
  const recordedConnectionId = readRecordString(spec, "externalMcpConnectionId")
    || (metadata ? readRecordString(metadata, "externalMcpConnectionId") : null)
  const owned = spec.externalMcpConnectionOwnedByPlugin === true
    || metadata?.externalMcpConnectionOwnedByPlugin === true
  return owned && recordedConnectionId === connectionId
}

async function assertRemotePluginMcpUrl(url: string) {
  let parsed: URL
  try {
    parsed = new URL(url)
  } catch {
    throw new PluginArchRouteFailure(400, "invalid_mcp_url", "MCP server URL is invalid.")
  }

  if (parsed.protocol !== "https:" && parsed.protocol !== "http:") {
    throw new PluginArchRouteFailure(400, "invalid_mcp_url", "MCP URLs must use HTTP or HTTPS.")
  }
  if (parsed.protocol === "http:" && !env.allowPrivateMcpUrls) {
    throw new PluginArchRouteFailure(400, "invalid_mcp_url", "Hosted MCP connections must use HTTPS.")
  }
  if (parsed.hash) {
    throw new PluginArchRouteFailure(400, "invalid_mcp_url", "MCP URLs must not contain a fragment.")
  }
  if (parsed.username || parsed.password) {
    throw new PluginArchRouteFailure(400, "invalid_mcp_url", "MCP URLs must not contain embedded credentials.")
  }

  const sensitiveParameters = new Set(["access_token", "api_key", "client_secret", "token", "refresh_token", "id_token", "code_verifier"])
  for (const parameter of parsed.searchParams.keys()) {
    if (sensitiveParameters.has(parameter.toLowerCase())) {
      throw new PluginArchRouteFailure(400, "invalid_mcp_url", `MCP URL query parameter "${parameter}" must not contain credentials.`)
    }
  }

  if (!env.allowPrivateMcpUrls) {
    try {
      await assertPublicUrl(url)
    } catch (error) {
      throw new PluginArchRouteFailure(400, "invalid_mcp_url", error instanceof Error ? error.message : "URL not allowed.")
    }
  }
}

async function activeMarketplaceIdsForPlugin(input: { organizationId: OrganizationId; pluginId: PluginId }) {
  const rows = await db
    .select({ marketplaceId: MarketplacePluginTable.marketplaceId })
    .from(MarketplacePluginTable)
    .innerJoin(MarketplaceTable, eq(MarketplacePluginTable.marketplaceId, MarketplaceTable.id))
    .where(and(
      eq(MarketplacePluginTable.organizationId, input.organizationId),
      eq(MarketplacePluginTable.pluginId, input.pluginId),
      isNull(MarketplacePluginTable.removedAt),
      eq(MarketplaceTable.organizationId, input.organizationId),
      eq(MarketplaceTable.status, "active"),
      isNull(MarketplaceTable.deletedAt),
    ))
  return rows.map((row) => row.marketplaceId)
}

async function derivePluginMcpRequirementAccess(input: {
  configObjectId: ConfigObjectId
  organizationId: OrganizationId
  pluginId: PluginId
}): Promise<PluginMcpRequirementAccess> {
  const activeRows = await db
    .select({ id: PluginConfigObjectTable.id, objectType: ConfigObjectTable.objectType })
    .from(PluginConfigObjectTable)
    .innerJoin(PluginTable, eq(PluginConfigObjectTable.pluginId, PluginTable.id))
    .innerJoin(ConfigObjectTable, eq(PluginConfigObjectTable.configObjectId, ConfigObjectTable.id))
    .where(and(
      eq(PluginConfigObjectTable.organizationId, input.organizationId),
      eq(PluginConfigObjectTable.pluginId, input.pluginId),
      eq(PluginConfigObjectTable.configObjectId, input.configObjectId),
      isNull(PluginConfigObjectTable.removedAt),
      eq(PluginTable.organizationId, input.organizationId),
      eq(PluginTable.status, "active"),
      isNull(PluginTable.deletedAt),
      eq(ConfigObjectTable.organizationId, input.organizationId),
      eq(ConfigObjectTable.status, "active"),
      isNull(ConfigObjectTable.deletedAt),
    ))
    .limit(1)
  if (!activeRows[0]) {
    return { memberIds: [], orgWide: false, teamIds: [] }
  }
  if (activeRows[0].objectType === "app") {
    const activeApp = await db.select({ configObjectId: RemoteMcpAppTable.configObjectId })
      .from(RemoteMcpAppTable)
      .where(and(
        eq(RemoteMcpAppTable.organizationId, input.organizationId),
        eq(RemoteMcpAppTable.configObjectId, input.configObjectId),
        eq(RemoteMcpAppTable.status, "active"),
      ))
      .limit(1)
    if (!activeApp[0]) return { memberIds: [], orgWide: false, teamIds: [] }
  }

  const marketplaceIds = await activeMarketplaceIdsForPlugin({ organizationId: input.organizationId, pluginId: input.pluginId })
  const configObjectGrants = await db
    .select({ orgMembershipId: ConfigObjectAccessGrantTable.orgMembershipId, orgWide: ConfigObjectAccessGrantTable.orgWide, teamId: ConfigObjectAccessGrantTable.teamId })
    .from(ConfigObjectAccessGrantTable)
    .where(and(
      eq(ConfigObjectAccessGrantTable.organizationId, input.organizationId),
      eq(ConfigObjectAccessGrantTable.configObjectId, input.configObjectId),
      isNull(ConfigObjectAccessGrantTable.removedAt),
    ))
  const pluginGrants = await db
    .select({ orgMembershipId: PluginAccessGrantTable.orgMembershipId, orgWide: PluginAccessGrantTable.orgWide, teamId: PluginAccessGrantTable.teamId })
    .from(PluginAccessGrantTable)
    .where(and(
      eq(PluginAccessGrantTable.organizationId, input.organizationId),
      eq(PluginAccessGrantTable.pluginId, input.pluginId),
      isNull(PluginAccessGrantTable.removedAt),
    ))
  const marketplaceGrants = marketplaceIds.length > 0
    ? await db
      .select({ orgMembershipId: MarketplaceAccessGrantTable.orgMembershipId, orgWide: MarketplaceAccessGrantTable.orgWide, teamId: MarketplaceAccessGrantTable.teamId })
      .from(MarketplaceAccessGrantTable)
      .where(and(
        eq(MarketplaceAccessGrantTable.organizationId, input.organizationId),
        inArray(MarketplaceAccessGrantTable.marketplaceId, marketplaceIds),
        isNull(MarketplaceAccessGrantTable.removedAt),
      ))
    : []
  const grants = [...configObjectGrants, ...pluginGrants, ...marketplaceGrants]
  return {
    orgWide: grants.some((grant) => grant.orgWide),
    memberIds: sortedUnique(grants.flatMap((grant) => grant.orgMembershipId ? [grant.orgMembershipId] : [])),
    teamIds: sortedUnique(grants.flatMap((grant) => grant.teamId ? [grant.teamId] : [])),
  }
}

async function syncPluginMcpRequirementBindingAccess(row: PluginMcpRequirementBindingRow) {
  const access = await derivePluginMcpRequirementAccess({
    configObjectId: row.configObjectId,
    organizationId: row.organizationId,
    pluginId: row.pluginId,
  })
  await replaceExternalMcpConnectionAccessForPluginBinding({
    access,
    bindingId: row.id,
    connectionId: row.externalMcpConnectionId,
    createdByOrgMembershipId: row.createdByOrgMembershipId,
    organizationId: row.organizationId,
  })
}

async function syncPluginMcpRequirementBindings(rows: PluginMcpRequirementBindingRow[]) {
  for (const row of rows) await syncPluginMcpRequirementBindingAccess(row)
}

async function pluginMcpRequirementBindingsForResource(input: ResourceTarget & { organizationId: OrganizationId }) {
  if (input.resourceKind === "config_object") {
    return db
      .select()
      .from(PluginMcpRequirementBindingTable)
      .where(and(
        eq(PluginMcpRequirementBindingTable.organizationId, input.organizationId),
        eq(PluginMcpRequirementBindingTable.configObjectId, input.resourceId),
      ))
  }
  if (input.resourceKind === "plugin") {
    return db
      .select()
      .from(PluginMcpRequirementBindingTable)
      .where(and(
        eq(PluginMcpRequirementBindingTable.organizationId, input.organizationId),
        eq(PluginMcpRequirementBindingTable.pluginId, input.resourceId),
      ))
  }
  if (input.resourceKind === "marketplace") {
    return db
      .select({ binding: PluginMcpRequirementBindingTable })
      .from(PluginMcpRequirementBindingTable)
      .innerJoin(MarketplacePluginTable, eq(MarketplacePluginTable.pluginId, PluginMcpRequirementBindingTable.pluginId))
      .where(and(
        eq(PluginMcpRequirementBindingTable.organizationId, input.organizationId),
        eq(MarketplacePluginTable.organizationId, input.organizationId),
        eq(MarketplacePluginTable.marketplaceId, input.resourceId),
        isNull(MarketplacePluginTable.removedAt),
      ))
      .then((rows) => rows.map((row) => row.binding))
  }
  return []
}

export async function syncPluginMcpRequirementAccessForResource(input: ResourceTarget & { context: PluginArchActorContext }) {
  const organizationId = input.context.organizationContext.organization.id
  const rows = input.resourceKind === "config_object"
    ? await pluginMcpRequirementBindingsForResource({ organizationId, resourceId: input.resourceId, resourceKind: "config_object" })
    : input.resourceKind === "plugin"
      ? await pluginMcpRequirementBindingsForResource({ organizationId, resourceId: input.resourceId, resourceKind: "plugin" })
      : input.resourceKind === "marketplace"
        ? await pluginMcpRequirementBindingsForResource({ organizationId, resourceId: input.resourceId, resourceKind: "marketplace" })
        : []
  await syncPluginMcpRequirementBindings(rows)
}

async function activePluginMcpRequirement(input: {
  configObjectId: ConfigObjectId
  organizationId: OrganizationId
  pluginId: PluginId
}) {
  const rows = await db
    .select({ configObject: ConfigObjectTable, plugin: PluginTable })
    .from(PluginConfigObjectTable)
    .innerJoin(PluginTable, eq(PluginConfigObjectTable.pluginId, PluginTable.id))
    .innerJoin(ConfigObjectTable, eq(PluginConfigObjectTable.configObjectId, ConfigObjectTable.id))
    .where(and(
      eq(PluginConfigObjectTable.organizationId, input.organizationId),
      eq(PluginConfigObjectTable.pluginId, input.pluginId),
      eq(PluginConfigObjectTable.configObjectId, input.configObjectId),
      isNull(PluginConfigObjectTable.removedAt),
      eq(PluginTable.organizationId, input.organizationId),
      eq(PluginTable.status, "active"),
      isNull(PluginTable.deletedAt),
      eq(ConfigObjectTable.organizationId, input.organizationId),
      eq(ConfigObjectTable.objectType, "mcp"),
      eq(ConfigObjectTable.status, "active"),
      isNull(ConfigObjectTable.deletedAt),
    ))
    .limit(1)

  const row = rows[0]
  if (!row) {
    throw new PluginArchRouteFailure(404, "mcp_requirement_not_found", "Active plugin MCP requirement not found.")
  }
  return row
}

function expectedMcpRequirementCredentialMode(input: { authType: PluginMcpRequirementAuthType; credentialMode: PluginMcpRequirementCredentialMode }) {
  if (input.authType === "apikey" || input.authType === "none") return "shared"
  return input.credentialMode
}

function normalizedPluginMcpApiKey(apiKey?: string) {
  const trimmed = apiKey?.trim()
  return trimmed ? trimmed : null
}

function validatePluginMcpRequirementAuth(input: {
  apiKey?: string
  authType: PluginMcpRequirementAuthType
  credentialMode: PluginMcpRequirementCredentialMode
  oauthClient?: { clientId: string; clientSecret?: string }
}) {
  const apiKey = normalizedPluginMcpApiKey(input.apiKey)
  if (input.oauthClient && input.authType !== "oauth") {
    throw new PluginArchRouteFailure(400, "invalid_mcp_auth", "oauthClient is only allowed when authType is oauth.")
  }
  if (apiKey && input.authType !== "apikey") {
    throw new PluginArchRouteFailure(400, "invalid_mcp_auth", "apiKey is only allowed when authType is apikey.")
  }
  if (input.authType === "apikey" && input.credentialMode !== "shared") {
    throw new PluginArchRouteFailure(400, "invalid_mcp_auth", "authType apikey requires credentialMode shared.")
  }
  if (input.authType === "apikey" && !apiKey) {
    throw new PluginArchRouteFailure(400, "invalid_mcp_auth", "authType apikey requires apiKey.")
  }
  if (input.credentialMode === "per_member" && input.authType !== "oauth") {
    throw new PluginArchRouteFailure(400, "invalid_mcp_auth", "credentialMode per_member requires authType oauth.")
  }
}

async function connectionCompatibleWithRequirement(input: {
  apiKey?: string | null
  authType: PluginMcpRequirementAuthType
  connection: ExternalMcpConnectionRow
  credentialMode: PluginMcpRequirementCredentialMode
  oauthClient?: { clientId: string; clientSecret?: string }
  organizationId: OrganizationId
  url: string
}) {
  if (input.connection.kind !== "external_mcp") return false
  const baseCompatible = comparablePluginMcpRequirementUrl(input.connection.url) === comparablePluginMcpRequirementUrl(input.url)
    && input.connection.authType === input.authType
    && input.connection.credentialMode === input.credentialMode
  if (!baseCompatible) return false
  if (input.authType === "apikey") {
    const apiKey = normalizedPluginMcpApiKey(input.apiKey ?? undefined)
    return Boolean(apiKey) && input.connection.apiKey === apiKey
  }
  if (!input.oauthClient || input.authType !== "oauth") return true
  const existingClient = await getOrgOAuthClient(input.organizationId, input.connection.id)
  return existingClient?.clientId === input.oauthClient.clientId
}

async function createOrReusePluginMcpRequirementConnection(input: {
  access: PluginMcpRequirementAccess
  apiKey?: string | null
  authType: PluginMcpRequirementAuthType
  context: PluginArchActorContext
  credentialMode: PluginMcpRequirementCredentialMode
  oauthClient?: { clientId: string; clientSecret?: string }
  plugin: PluginRow
  server: PluginMcpRequirementServer
}): Promise<{ connection: ExternalMcpConnectionRow; created: boolean }> {
  const organizationId = input.context.organizationContext.organization.id
  const connections = await listExternalMcpConnections(organizationId)
  let compatible: ExternalMcpConnectionRow | null = null
  for (const connection of connections) {
    if (await connectionCompatibleWithRequirement({
      apiKey: input.apiKey,
      authType: input.authType,
      connection,
      credentialMode: input.credentialMode,
      oauthClient: input.oauthClient,
      organizationId,
      url: input.server.url,
    })) {
      compatible = connection
      break
    }
  }

  if (compatible) {
    return { connection: compatible, created: false }
  }

  const created = await createExternalMcpConnection({
    access: { memberIds: [], orgWide: false, teamIds: [] },
    apiKey: input.authType === "apikey" ? input.apiKey : null,
    authType: input.authType,
    createdByOrgMembershipId: input.context.organizationContext.currentMember.id,
    credentialMode: input.credentialMode,
    name: externalMcpConnectionName({ pluginName: input.plugin.name, serverName: input.server.name }),
    organizationId,
    url: input.server.url,
  })
  return { connection: created, created: true }
}

function pluginMcpValidationRedirectUri(connectionId: string) {
  const baseUrl = env.apiPublicUrl ?? env.betterAuthUrl
  return new URL(`/v1/mcp-connections/${encodeURIComponent(connectionId)}/connect/callback`, baseUrl).toString()
}

async function validateConfiguredPluginMcpConnection(input: {
  authType: PluginMcpRequirementAuthType
  connection: ExternalMcpConnectionRow
}) {
  if (input.connection.kind !== "external_mcp") {
    throw new PluginArchRouteFailure(400, "invalid_mcp_connection", "Native provider connectors cannot satisfy plugin MCP requirements.")
  }
  if (input.authType === "oauth") return
  try {
    await connectExternalMcp(
      input.connection,
      pluginMcpValidationRedirectUri(input.connection.id),
      undefined,
      undefined,
      input.connection.id,
    )
  } catch (error) {
    const diagnostic = externalMcpDiagnosticForResponse(error, input.connection.id, "MCP_INITIALIZE")
    console.error("plugin_mcp_connection_validation_failed", {
      connectionId: input.connection.id,
      organizationId: input.connection.organizationId,
      connectionEndpoint: safeExternalMcpEndpointForLog(input.connection.url),
      ...externalMcpDiagnosticForLog(error, input.connection.id, "MCP_INITIALIZE"),
    })
    throw new PluginArchRouteFailure(
      502,
      "connection_validation_failed",
      `Could not validate "${input.connection.name}": ${diagnostic.message} Reference: ${diagnostic.referenceId}.`,
    )
  }

  if (input.authType === "none") {
    await markImportedExternalMcpConnectionConnected(input.connection.id)
  }
}

function sortedUnique<TValue extends string>(values: TValue[]): TValue[] {
  return [...new Set(values)].sort()
}

async function requireExistingExternalMcpConnectionMatchesImport(input: {
  authType: PluginMcpAuthType
  credentialMode: "per_member" | "shared"
  existingAuthType: "apikey" | "none" | "oauth"
  existingCredentialMode: "per_member" | "shared"
}) {
  const expectedCredentialMode = input.authType === "oauth" ? input.credentialMode : "shared"
  if (input.existingAuthType !== input.authType || input.existingCredentialMode !== expectedCredentialMode) {
    throw new PluginArchRouteFailure(
      409,
      "external_mcp_connection_config_mismatch",
      "An External MCP Connection already exists for this URL with different authentication or credential mode. Edit the existing connection or import with matching settings.",
    )
  }
}

async function ensureImportedExternalMcpConnection(input: {
  access: GithubPluginMcpImportAccess
  authType: PluginMcpAuthType
  context: PluginArchActorContext
  credentialMode: "per_member" | "shared"
  server: GithubPluginMcpImportServer
}): Promise<{ connection: Awaited<ReturnType<typeof createExternalMcpConnection>>; ownedByImportedPlugin: boolean }> {
  if (!input.server.url) {
    throw new PluginArchRouteFailure(400, "invalid_mcp_import", "MCP server URL is required.")
  }
  const serverUrl = input.server.url

  await assertPublicUrl(serverUrl)
  const organizationId = input.context.organizationContext.organization.id
  const existing = (await listExternalMcpConnections(organizationId))
    .find((connection) => connection.kind === "external_mcp" && comparablePluginMcpRequirementUrl(connection.url) === comparablePluginMcpRequirementUrl(serverUrl))

  if (existing) {
    await requireExistingExternalMcpConnectionMatchesImport({
      authType: input.authType,
      credentialMode: input.credentialMode,
      existingAuthType: existing.authType,
      existingCredentialMode: existing.credentialMode,
    })
    if (input.authType === "none") {
      try {
        await validateConfiguredPluginMcpConnection({ authType: input.authType, connection: existing })
      } catch (error) {
        await db.update(ExternalMcpConnectionTable).set({ connectedAt: null }).where(and(
          eq(ExternalMcpConnectionTable.organizationId, organizationId),
          eq(ExternalMcpConnectionTable.id, existing.id),
        ))
        throw error
      }
    }
    return { connection: existing, ownedByImportedPlugin: false }
  }

  const created = await createExternalMcpConnection({
    access: { memberIds: [], orgWide: false, teamIds: [] },
    authType: input.authType,
    createdByOrgMembershipId: input.context.organizationContext.currentMember.id,
    credentialMode: input.authType === "oauth" ? input.credentialMode : "shared",
    name: externalMcpConnectionName({ pluginName: input.server.pluginName, serverName: input.server.name }),
    organizationId,
    url: serverUrl,
  })
  if (input.authType === "none") {
    try {
      await validateConfiguredPluginMcpConnection({ authType: input.authType, connection: created })
    } catch (error) {
      await deleteExternalMcpConnection({ connectionId: created.id, organizationId })
      throw error
    }
  }
  return { connection: created, ownedByImportedPlugin: true }
}

async function markImportedExternalMcpConnectionConnected(connectionId: typeof ExternalMcpConnectionTable.$inferSelect.id) {
  await db
    .update(ExternalMcpConnectionTable)
    .set({ connectedAt: new Date() })
    .where(eq(ExternalMcpConnectionTable.id, connectionId))
}

function connectionBackedMcpPayload(input: {
  authType: PluginMcpAuthType
  connectionId: string
  ownedByPlugin: boolean
  server: { name: string; url: string | null }
}) {
  const serverName = slugifyPluginMcpName(input.server.name)
  return {
    mcpServers: {
      [serverName]: {
        type: "remote",
        url: input.server.url,
        omnirushManaged: "den_external_mcp",
        externalMcpConnectionId: input.connectionId,
        externalMcpConnectionOwnedByPlugin: input.ownedByPlugin,
        requiredAuthType: input.authType,
        ...(input.authType === "oauth" ? { oauth: true } : {}),
      },
    },
    omnirushManaged: "den_external_mcp",
    externalMcpConnectionId: input.connectionId,
    externalMcpConnectionOwnedByPlugin: input.ownedByPlugin,
    requiredAuthType: input.authType,
  }
}

function importedConnectionBackedMcpPayload(input: {
  authType: PluginMcpAuthType
  connectionId: string
  ownedByImportedPlugin: boolean
  server: GithubPluginMcpImportServer
}) {
  return connectionBackedMcpPayload({
    authType: input.authType,
    connectionId: input.connectionId,
    ownedByPlugin: input.ownedByImportedPlugin,
    server: input.server,
  })
}

function importedPluginName(plan: GithubPluginMcpImportPlan) {
  if (plan.plugins.length === 1) return plan.plugins[0].name
  return plan.marketplace?.name?.trim() || plan.rootPath.split("/").filter(Boolean).at(-1) || plan.repositoryFullName.split("/").at(-1) || "GitHub MCP Plugin"
}

export async function previewGithubPluginMcpImport(input: { githubUrl: string }) {
  return computeGithubPluginMcpImportPlan({ githubUrl: input.githubUrl })
}

export async function configureMarketplacePluginMcpRequirement(input: {
  apiKey?: string
  authType: PluginMcpRequirementAuthType
  configObjectId: ConfigObjectId
  context: PluginArchActorContext
  credentialMode: PluginMcpRequirementCredentialMode
  oauthClient?: { clientId: string; clientSecret?: string }
  pluginId: PluginId
  serverName: string
}) {
  validatePluginMcpRequirementAuth(input)
  const organizationId = input.context.organizationContext.organization.id
  const requirement = await activePluginMcpRequirement({
    configObjectId: input.configObjectId,
    organizationId,
    pluginId: input.pluginId,
  })
  const versions = await getLatestVersions([requirement.configObject.id])
  const version = versions.get(requirement.configObject.id)
  if (!version) {
    throw new PluginArchRouteFailure(409, "mcp_requirement_not_synced", "MCP config object has no active version to configure.")
  }

  const server = mcpRequirementServerFromVersion({
    configObject: requirement.configObject,
    serverName: input.serverName,
    version,
  })
  const declaredRequiredAuthType = requiredPluginMcpAuthType({
    declaredAuthType: declaredPluginMcpAuthType(server.config),
    url: server.url,
  })
  if (declaredRequiredAuthType && declaredRequiredAuthType !== input.authType) {
    throw new PluginArchRouteFailure(
      409,
      "mcp_auth_type_mismatch",
      `This MCP requirement must use ${declaredRequiredAuthType} authentication.`,
    )
  }
  const requiredAuthType = declaredRequiredAuthType ?? input.authType
  await assertRemotePluginMcpUrl(server.url)
  const credentialMode = expectedMcpRequirementCredentialMode(input)
  const apiKey = normalizedPluginMcpApiKey(input.apiKey)
  const access = await derivePluginMcpRequirementAccess({
    configObjectId: requirement.configObject.id,
    organizationId,
    pluginId: requirement.plugin.id,
  })
  const previousBinding = (await listPluginMcpRequirementBindings({
    configObjectIds: [requirement.configObject.id],
    organizationId,
  })).find((candidate) => candidate.pluginId === requirement.plugin.id && candidate.serverName === server.name)
  const connectionResult = await createOrReusePluginMcpRequirementConnection({
    access,
    apiKey,
    authType: input.authType,
    context: input.context,
    credentialMode,
    oauthClient: input.oauthClient,
    plugin: requirement.plugin,
    server,
  })
  const connection = connectionResult.connection

  try {
    await validateConfiguredPluginMcpConnection({ authType: input.authType, connection })
  } catch (error) {
    if (connectionResult.created) {
      await deleteExternalMcpConnection({ connectionId: connection.id, organizationId })
    }
    throw error
  }

  if (input.oauthClient) {
    const existingClient = await getOrgOAuthClient(organizationId, connection.id)
    if (!existingClient) {
      await upsertOrgOAuthClient({
        organizationId,
        providerId: connection.id,
        clientId: input.oauthClient.clientId,
        clientSecret: input.oauthClient.clientSecret ?? null,
        createdByOrgMembershipId: input.context.organizationContext.currentMember.id,
      })
    }
  }

  const binding = await upsertPluginMcpRequirementBinding({
    configObjectId: requirement.configObject.id,
    createdByOrgMembershipId: input.context.organizationContext.currentMember.id,
    externalMcpConnectionId: connection.id,
    organizationId,
    pluginId: requirement.plugin.id,
    serverName: server.name,
    requiredAuthType,
    connectionOwnedByPlugin: connectionResult.created || Boolean(
      previousBinding?.externalMcpConnectionId === connection.id && previousBinding.connectionOwnedByPlugin
    ),
  })
  await syncPluginMcpRequirementBindingAccess(binding)
  const replacedOwnedConnectionId = previousBinding
    && previousBinding.externalMcpConnectionId !== connection.id
    && (previousBinding.connectionOwnedByPlugin
      || configVersionOwnsImportedExternalMcpConnection(version, previousBinding.externalMcpConnectionId))
    ? previousBinding.externalMcpConnectionId
    : null
  if (replacedOwnedConnectionId) {
    await deleteExternalMcpConnectionIfUnreferenced({ connectionId: replacedOwnedConnectionId, organizationId })
  }
  const refreshedConnection = await getExternalMcpConnection({ connectionId: connection.id, organizationId })

  return {
    binding: serializePluginMcpRequirementBinding(binding),
    connection: serializePluginMcpRequirementConnection(refreshedConnection ?? connection),
    links: {
      yourConnections: omnirushYourConnectionsUrl(connection.id),
    },
  }
}

async function grantImportAccessToPluginArchResource(input: {
  access: GithubPluginMcpImportAccess
  context: PluginArchActorContext
} & (
  | { resourceId: ConfigObjectId; resourceKind: "config_object" }
  | { resourceId: PluginId; resourceKind: "plugin" }
)) {
  const grant = async (value: AccessGrantWrite) => {
    if (input.resourceKind === "plugin") {
      await createResourceAccessGrant({
        context: input.context,
        resourceId: input.resourceId,
        resourceKind: "plugin",
        value,
      })
      return
    }
    await createResourceAccessGrant({
      context: input.context,
      resourceId: input.resourceId,
      resourceKind: "config_object",
      value,
    })
  }

  if (input.access.orgWide) {
    await grant({ orgWide: true, role: "viewer" })
    return
  }

  for (const memberId of input.access.memberIds) {
    await grant({ orgMembershipId: memberId, role: "viewer" })
  }
  for (const teamId of input.access.teamIds) {
    await grant({ role: "viewer", teamId })
  }
}

export async function importGithubPluginMcps(input: {
  access?: GithubPluginMcpImportAccess
  authType: "none" | "oauth"
  context: PluginArchActorContext
  credentialMode: "per_member" | "shared"
  description?: string | null
  githubUrl: string
  marketplaceId?: MarketplaceId
  name?: string
  selectedSkillKeys?: string[]
  selectedServerKeys?: string[]
  selectedServerNames?: string[]
}) {
  if (!isPluginArchOrgAdmin(input.context)) {
    throw new PluginArchAuthorizationError(403, "forbidden", "Only organization owners and admins can import plugins from GitHub.")
  }

  if (input.marketplaceId) {
    await ensureEditableMarketplace(input.context, input.marketplaceId)
  }
  const plan = await computeGithubPluginMcpImportPlan({ githubUrl: input.githubUrl, includeSkillText: true })
  const selectedSkillKeys = new Set(input.selectedSkillKeys?.map((key) => key.trim()).filter(Boolean) ?? [])
  const selectedServerKeys = new Set(input.selectedServerKeys?.map((key) => key.trim()).filter(Boolean) ?? [])
  const selectedServerNames = new Set(input.selectedServerNames?.map((name) => name.trim()).filter(Boolean) ?? [])
  const consideredServers = selectedServerKeys.size > 0
    ? plan.servers.filter((server) => selectedServerKeys.has(server.serverKey))
    : selectedServerNames.size > 0
    ? plan.servers.filter((server) => selectedServerNames.has(server.name))
    : plan.servers
  const consideredSkills = selectedSkillKeys.size > 0
    ? plan.skills.filter((skill) => selectedSkillKeys.has(skill.skillKey))
    : []
  const supportedServers = consideredServers.filter((server) => server.supported && server.url)
  const supportedSkills = consideredSkills.filter((skill) => skill.supported && skill.rawSourceText)
  if (supportedServers.length === 0 && supportedSkills.length === 0) {
    throw new PluginArchRouteFailure(400, "no_supported_plugin_components", "No supported remote MCP servers or skills were selected from that plugin.")
  }

  const access = input.access ?? {
    memberIds: [],
    orgWide: true,
    teamIds: [],
  }
  if (!access.orgWide && access.memberIds.length === 0 && access.teamIds.length === 0) {
    throw new PluginArchRouteFailure(400, "missing_import_access", "Choose who can use the imported plugin.")
  }
  const plugin = await createPlugin({
    context: input.context,
    description: input.description === undefined
      ? `Plugin components imported from ${plan.repositoryFullName}${plan.rootPath ? `/${plan.rootPath}` : ""}.`
      : input.description,
    name: input.name ?? importedPluginName(plan),
    sourceFormat: plan.classification === "agent_plugin_repo" ? "agent-plugin" : "claude-plugin",
    sourceRepositoryUrl: `https://github.com/${plan.repositoryFullName}`,
    sourceSchemaVersion: plan.classification === "agent_plugin_repo" ? plan.sourceSchemaVersion : null,
  })

  const importedOwnedConnectionIds = new Set<ExternalMcpConnectionRow["id"]>()
  try {
    await grantImportAccessToPluginArchResource({
    access,
    context: input.context,
    resourceId: plugin.id,
    resourceKind: "plugin",
  })

  const imported: Array<{ connectionId: string; name: string; url: string }> = []
  const importedSkills: Array<{ configObjectId: ConfigObjectId; name: string; sourcePath: string }> = []
  for (const server of supportedServers) {
    const authType = resolveGithubPluginMcpImportAuthType({
      declaredAuthType: server.authType,
      requestedAuthType: input.authType,
      url: server.url ?? "",
    })
    const importedConnection = await ensureImportedExternalMcpConnection({
      access,
      authType,
      context: input.context,
      credentialMode: input.credentialMode,
      server,
    })
    const connection = importedConnection.connection
    if (importedConnection.ownedByImportedPlugin) importedOwnedConnectionIds.add(connection.id)
    const payload = importedConnectionBackedMcpPayload({
      authType,
      connectionId: connection.id,
      ownedByImportedPlugin: importedConnection.ownedByImportedPlugin,
      server,
    })
    const configObject = await createConfigObject({
      context: input.context,
      objectType: "mcp",
      pluginIds: [plugin.id],
      sourceMode: "import",
      value: {
        metadata: {
          description: `Den-hosted MCP connection imported from ${server.sourcePath}.`,
          externalMcpConnectionId: connection.id,
          externalMcpConnectionOwnedByPlugin: importedConnection.ownedByImportedPlugin,
          requiredAuthType: authType,
          githubUrl: input.githubUrl,
          name: externalMcpConnectionName({ pluginName: server.pluginName, serverName: server.name }),
          omnirushManaged: "den_external_mcp",
          repositoryFullName: plan.repositoryFullName,
          sourceFormat: plan.classification === "agent_plugin_repo" ? "agent-plugin" : "claude-plugin",
          sourceSchemaVersion: server.sourceSchemaVersion,
          sourcePath: server.sourcePath,
        },
        normalizedPayloadJson: payload,
        schemaVersion: "omnirush.den_external_mcp.v1",
      },
    })
    await upsertPluginMcpRequirementBinding({
      configObjectId: configObject.id,
      createdByOrgMembershipId: input.context.organizationContext.currentMember.id,
      externalMcpConnectionId: connection.id,
      organizationId: input.context.organizationContext.organization.id,
      pluginId: plugin.id,
      serverName: slugifyPluginMcpName(server.name),
      requiredAuthType: authType,
      connectionOwnedByPlugin: importedConnection.ownedByImportedPlugin,
    })
    await grantImportAccessToPluginArchResource({
      access,
      context: input.context,
      resourceId: configObject.id,
      resourceKind: "config_object",
    })
    imported.push({ connectionId: connection.id, name: server.name, url: server.url ?? "" })
  }

  for (const skill of supportedSkills) {
    const skillText = skill.rawSourceText
    if (!skillText) {
      throw new PluginArchRouteFailure(400, "invalid_skill_import", "Selected skill content was unavailable.")
    }
    const metadata = skillMetadataFromText(skillText)
    const configObject = await createConfigObject({
      context: input.context,
      objectType: "skill",
      pluginIds: [plugin.id],
      sourceMode: "import",
      value: {
        metadata: {
          description: metadata.description ?? `Skill imported from ${skill.sourcePath}.`,
          githubUrl: input.githubUrl,
          name: metadata.title,
          repositoryFullName: plan.repositoryFullName,
          sourceFormat: plan.classification === "agent_plugin_repo" ? "agent-plugin" : "claude-plugin",
          sourceSchemaVersion: skill.sourceSchemaVersion,
          sourcePath: skill.sourcePath,
        },
        rawSourceText: skillText,
      },
    })
    await grantImportAccessToPluginArchResource({
      access,
      context: input.context,
      resourceId: configObject.id,
      resourceKind: "config_object",
    })
    importedSkills.push({ configObjectId: configObject.id, name: metadata.title, sourcePath: skill.sourcePath })
  }

  if (input.marketplaceId) {
    await attachPluginToMarketplace({
      context: input.context,
      marketplaceId: input.marketplaceId,
      membershipSource: "api",
      pluginId: plugin.id,
    })
  }

  const skipped = consideredServers.flatMap((server) =>
    server.supported || !server.skippedReason ? [] : [{ name: server.name, reason: server.skippedReason }])
  const skippedSkills = consideredSkills.flatMap((skill) =>
    skill.supported || !skill.skippedReason ? [] : [{ name: skill.name, reason: skill.skippedReason, sourcePath: skill.sourcePath }])

  return {
    imported,
    importedSkills,
    marketplaceId: input.marketplaceId ?? null,
    plugin: await getPluginDetail(input.context, plugin.id),
    skipped,
    skippedSkills,
  }
  } catch (error) {
    await deletePluginMcpRequirementBindingsForPlugin({
      organizationId: input.context.organizationContext.organization.id,
      pluginId: plugin.id,
    }).catch(() => undefined)
    for (const connectionId of importedOwnedConnectionIds) {
      await deleteExternalMcpConnectionIfUnreferenced({
        organizationId: input.context.organizationContext.organization.id,
        connectionId,
      }).catch(() => undefined)
    }
    await setPluginLifecycle({ action: "archive", context: input.context, pluginId: plugin.id }).catch(() => undefined)
    throw error
  }
}

function readGithubDiscoveryCache(config: Record<string, unknown> | null) {
  const cache = config && isRecord(config.githubDiscoveryCache) ? config.githubDiscoveryCache : null
  if (!cache) {
    return null
  }

  const repositoryFullName = typeof cache.repositoryFullName === "string" ? cache.repositoryFullName : null
  const branch = typeof cache.branch === "string" ? cache.branch : null
  const ref = typeof cache.ref === "string" ? cache.ref : null
  const sourceRevisionRef = typeof cache.sourceRevisionRef === "string" ? cache.sourceRevisionRef : null
  const discoveredPlugins = Array.isArray(cache.discoveredPlugins) ? cache.discoveredPlugins as GithubDiscoveredPlugin[] : null
  const warnings = Array.isArray(cache.warnings) ? cache.warnings.filter((entry): entry is string => typeof entry === "string") : null
  const treeSummary = isRecord(cache.treeSummary) ? cache.treeSummary as GithubConnectorDiscoveryTreeSummary : null
  const importPlansByPluginKey = isRecord(cache.importPlansByPluginKey)
    ? cache.importPlansByPluginKey as Record<string, GithubDiscoveryImportPlan[]>
    : null
  const classification = typeof cache.classification === "string" ? cache.classification as GithubDiscoveryClassification : null

  if (!repositoryFullName || !branch || !ref || !sourceRevisionRef || !discoveredPlugins || !warnings || !treeSummary || !importPlansByPluginKey || !classification) {
    return null
  }

  return {
    branch,
    classification,
    discoveredPlugins,
    importPlansByPluginKey,
    marketplace: isRecord(cache.marketplace) || cache.marketplace === null ? cache.marketplace as GithubMarketplaceInfo | null : null,
    ref,
    repositoryFullName,
    sourceRevisionRef,
    treeSummary,
    warnings,
  } satisfies GithubDiscoveryCacheEntry
}

function withGithubDiscoveryCache(config: Record<string, unknown>, cache: GithubDiscoveryCacheEntry) {
  return {
    ...config,
    githubDiscoveryCache: cache,
  }
}

async function getGithubDiscoveryContext(input: { connectorInstanceId: ConnectorInstanceId; context: PluginArchActorContext }) {
  const connectorInstance = await ensureVisibleConnectorInstance(input.context, input.connectorInstanceId)
  if (connectorInstance.connectorType !== "github") {
    throw new PluginArchRouteFailure(409, "github_connector_instance_required", "Connector instance is not a GitHub connector.")
  }

  const connectorAccount = await getConnectorAccountRow(input.context.organizationContext.organization.id, connectorInstance.connectorAccountId)
  if (!connectorAccount || connectorAccount.connectorType !== "github") {
    throw new PluginArchRouteFailure(404, "connector_account_not_found", "GitHub connector account not found.")
  }

  const targetRows = await db
    .select()
    .from(ConnectorTargetTable)
    .where(eq(ConnectorTargetTable.connectorInstanceId, connectorInstance.id))
    .orderBy(asc(ConnectorTargetTable.createdAt), asc(ConnectorTargetTable.id))
    .limit(1)
  const connectorTarget = targetRows[0] ?? null
  if (!connectorTarget) {
    throw new PluginArchRouteFailure(404, "connector_target_not_found", "GitHub connector target not found.")
  }

  const targetConfig = connectorTarget.targetConfigJson && typeof connectorTarget.targetConfigJson === "object"
    ? connectorTarget.targetConfigJson as Record<string, unknown>
    : {}
  const repositoryFullName = typeof targetConfig.repositoryFullName === "string" ? targetConfig.repositoryFullName.trim() : connectorTarget.remoteId.trim()
  const branch = typeof targetConfig.branch === "string" ? targetConfig.branch.trim() : connectorTarget.externalTargetRef?.trim() ?? ""
  const ref = typeof targetConfig.ref === "string" ? targetConfig.ref.trim() : branch ? `refs/heads/${branch}` : ""
  const installationId = typeof connectorInstance.instanceConfigJson === "object" && connectorInstance.instanceConfigJson && typeof (connectorInstance.instanceConfigJson as Record<string, unknown>).installationId === "number"
    ? (connectorInstance.instanceConfigJson as Record<string, unknown>).installationId as number
    : Number(connectorAccount.remoteId)

  if (!repositoryFullName || !branch || !ref || !Number.isFinite(installationId) || installationId <= 0) {
    throw new PluginArchRouteFailure(409, "invalid_github_connector_target", "GitHub connector target is missing repository, branch, or installation metadata.")
  }

  const instanceConfigRecord = typeof connectorInstance.instanceConfigJson === "object" && connectorInstance.instanceConfigJson
    ? connectorInstance.instanceConfigJson as Record<string, unknown>
    : null
  const autoImportSaved = instanceConfigRecord ? instanceConfigRecord.autoImportNewPlugins : undefined
  return {
    autoImportNewPlugins: typeof autoImportSaved === "boolean" ? autoImportSaved : true,
    branch,
    connectorAccount,
    connectorInstance,
    connectorTarget,
    installationId,
    ref,
    repositoryFullName,
  }
}

async function buildConnectorAutomationContext(input: { connectorInstance: ConnectorInstanceRow }) {
  const organizationRows = await db
    .select()
    .from(OrganizationTable)
    .where(eq(OrganizationTable.id, input.connectorInstance.organizationId))
    .limit(1)
  const organization = organizationRows[0] as OrganizationRow | undefined
  if (!organization) {
    throw new PluginArchRouteFailure(404, "organization_not_found", "Organization not found for connector instance.")
  }

  const member = await resolveOrganizationMemberAuthority({
    organizationId: input.connectorInstance.organizationId,
    memberId: input.connectorInstance.createdByOrgMembershipId,
  })
  if (!member) {
    throw new PluginArchRouteFailure(404, "member_not_found", "Connector creator member not found.")
  }

  if (!member.userId) {
    throw new PluginArchRouteFailure(404, "member_not_joined", "Connector creator member has not joined the organization.")
  }

  return {
    automation: true,
    memberTeams: [],
    session: null,
    organizationContext: {
      currentMember: {
        createdAt: member.createdAt,
        id: member.id,
        isOwner: roleIncludesOwner(member.role),
        joinedAt: member.joinedAt,
        role: member.role,
        directRole: member.directRole,
        adminTeams: member.adminTeams,
        userId: member.userId,
      },
      invitations: [],
      members: [],
      organization: {
        allowedEmailDomains: organization.allowedEmailDomains ?? null,
        createdAt: organization.createdAt,
        id: organization.id,
        logo: organization.logo ?? null,
        metadata: organization.metadata ? JSON.stringify(organization.metadata) : null,
        name: organization.name,
        slug: organization.slug,
        updatedAt: organization.updatedAt,
      },
      roles: [],
      teams: [],
    },
  } satisfies PluginArchActorContext
}

async function maybeAutoImportGithubConnectorInstance(input: {
  connectorInstance: ConnectorInstanceRow
  connectorSyncEventId?: ConnectorSyncEventId
  connectorTarget: ConnectorTargetRow
}) {
  const instanceConfig = input.connectorInstance.instanceConfigJson && typeof input.connectorInstance.instanceConfigJson === "object"
    ? input.connectorInstance.instanceConfigJson as Record<string, unknown>
    : {}
  // Treat an unset flag as enabled to match getGithubDiscoveryContext defaults: a repo the
  // user has already configured should re-sync on push unless they explicitly opted out.
  const autoImportNewPlugins = instanceConfig.autoImportNewPlugins !== false
  if (!autoImportNewPlugins) {
    // User explicitly disabled auto-import: do not run discovery or materialize any objects.
    return {
      autoImported: false as const,
      autoImportNewPlugins,
      classification: null,
      createdMarketplace: null,
      createdPluginCount: 0,
      createdPlugins: [],
      discoveredPluginCount: 0,
      materializedConfigObjectCount: 0,
      materializedConfigObjects: [],
      sourceRevisionRef: null,
    }
  }

  const context = await buildConnectorAutomationContext({ connectorInstance: input.connectorInstance })
  // Force a fresh discovery so the latest head revision and file contents are fetched. Without
  // this, the cached discovery snapshot keeps the previous sourceRevisionRef and the version
  // guard in materializeGithubImportedObject would skip creating a new version on push.
  const discovery = await resolveGithubConnectorDiscovery({
    connectorInstanceId: input.connectorInstance.id,
    context,
    forceRefresh: true,
  })
  const selectedKeys = discovery.cache.discoveredPlugins
    .filter((plugin) => plugin.supported)
    .map((plugin) => plugin.key)

  const applied = await applyGithubConnectorDiscovery({
    autoImportNewPlugins,
    connectorInstanceId: input.connectorInstance.id,
    connectorSyncEventId: input.connectorSyncEventId,
    context,
    forceRefresh: true,
    selectedKeys,
  })

  return {
    autoImported: true as const,
    autoImportNewPlugins,
    classification: discovery.cache.classification,
    createdMarketplace: applied.createdMarketplace
      ? { id: applied.createdMarketplace.id, name: applied.createdMarketplace.name }
      : null,
    createdPluginCount: applied.createdPlugins.length,
    createdPlugins: applied.createdPlugins.map((plugin) => ({ id: plugin.id, name: plugin.name })),
    discoveredPluginCount: discovery.cache.discoveredPlugins.length,
    materializedConfigObjectCount: applied.materializedConfigObjects.length,
    materializedConfigObjects: applied.materializedConfigObjects.map((object) => ({
      id: object.id,
      objectType: object.objectType,
      path: object.currentRelativePath,
      title: object.title,
      versionId: object.latestVersion?.id ?? null,
    })),
    sourceRevisionRef: applied.sourceRevisionRef,
  }
}

export async function executeGithubConnectorSyncEvent(input: { connectorSyncEventId: ConnectorSyncEventId }) {
  const eventRows = await db
    .select()
    .from(ConnectorSyncEventTable)
    .where(eq(ConnectorSyncEventTable.id, input.connectorSyncEventId))
    .limit(1)
  const event = eventRows[0]
  if (!event || event.connectorType !== "github") {
    throw new Error("GitHub connector sync event not found.")
  }

  const connectorInstance = await getConnectorInstanceRow(event.organizationId, event.connectorInstanceId)
  if (!connectorInstance || connectorInstance.connectorType !== "github") {
    throw new Error("GitHub connector instance not found for sync event.")
  }
  if (!event.connectorTargetId) {
    throw new Error("GitHub connector target is missing from sync event.")
  }
  const connectorTarget = await getConnectorTargetRow(event.organizationId, event.connectorTargetId)
  if (!connectorTarget || connectorTarget.connectorType !== "github") {
    throw new Error("GitHub connector target not found for sync event.")
  }

  const startedAt = new Date()
  const autoImportSummary = await maybeAutoImportGithubConnectorInstance({
    connectorInstance,
    connectorSyncEventId: event.id,
    connectorTarget,
  })
  const completedAt = new Date()
  const eventStatus: ConnectorSyncEventRow["status"] = !autoImportSummary.autoImported
    ? "ignored"
    : autoImportSummary.materializedConfigObjectCount > 0
      ? "completed"
      : "partial"
  const summaryJson = {
    ...(event.summaryJson ?? {}),
    outcome: eventStatus,
    error: null,
    autoImportApplied: autoImportSummary.autoImported,
    autoImportNewPlugins: autoImportSummary.autoImportNewPlugins,
    classification: autoImportSummary.classification,
    resolvedSourceRevisionRef: autoImportSummary.sourceRevisionRef,
    discoveredPluginCount: autoImportSummary.discoveredPluginCount,
    createdMarketplace: autoImportSummary.createdMarketplace,
    createdPluginCount: autoImportSummary.createdPluginCount,
    createdPlugins: autoImportSummary.createdPlugins,
    materializedConfigObjectCount: autoImportSummary.materializedConfigObjectCount,
    materializedConfigObjects: autoImportSummary.materializedConfigObjects,
    startedAt: startedAt.toISOString(),
    completedAt: completedAt.toISOString(),
    durationMs: completedAt.getTime() - startedAt.getTime(),
  }

  await db.update(ConnectorSyncEventTable).set({
    completedAt,
    nextAttemptAt: null,
    status: eventStatus,
    summaryJson,
  }).where(and(
    eq(ConnectorSyncEventTable.id, event.id),
    eq(ConnectorSyncEventTable.status, "running"),
  ))

  return { status: eventStatus }
}

async function getGithubDiscoveryFileTexts(input: {
  branch: string
  config: ReturnType<typeof githubConnectorAppConfig>
  installationId: number
  repositoryFullName: string
  token?: string
  treeEntries: GithubDiscoveryTreeEntry[]
}) {
  const interestingPaths = new Set<string>()
  const knownPaths = new Set(input.treeEntries.map((entry) => entry.path))

  if (knownPaths.has(".claude-plugin/marketplace.json")) {
    interestingPaths.add(".claude-plugin/marketplace.json")
  }

  for (const entry of input.treeEntries) {
    if (entry.path.endsWith(".claude-plugin/plugin.json") || entry.path.endsWith("/plugin.json") || entry.path === "plugin.json") {
      interestingPaths.add(entry.path)
    }
  }

  const fileTextByPath: Record<string, string | null> = {}
  for (const path of interestingPaths) {
    try {
      fileTextByPath[path] = await getGithubRepositoryTextFile({
        config: input.config,
        installationId: input.installationId,
        path,
        ref: input.branch,
        repositoryFullName: input.repositoryFullName,
        token: input.token,
      })
    } catch (error) {
      wrapGithubConnectorError(error)
    }
  }

  return fileTextByPath
}

function pagedGithubDiscoveryTree(input: { cursor?: string; entries: GithubDiscoveryTreeEntry[]; limit?: number; prefix?: string }) {
  const normalizedPrefix = input.prefix?.trim().replace(/^\/+/, "").replace(/\/+$/, "")
  const filtered = input.entries
    .filter((entry) => !normalizedPrefix || entry.path === normalizedPrefix || entry.path.startsWith(`${normalizedPrefix}/`))
    .sort((left, right) => left.path.localeCompare(right.path))
  return pageItems(filtered, normalizeDiscoveryCursor(input.cursor), input.limit)
}

async function computeGithubDiscoverySnapshot(input: {
  branch: string
  installationId: number
  ref: string
  repositoryFullName: string
  token?: string
}) {
  const token = input.token ?? await getGithubInstallationAccessToken({
    config: githubConnectorAppConfig(),
    installationId: input.installationId,
  })
  let treeSnapshot: Awaited<ReturnType<typeof getGithubRepositoryTree>>
  try {
    treeSnapshot = await getGithubRepositoryTree({
      branch: input.branch,
      config: githubConnectorAppConfig(),
      installationId: input.installationId,
      repositoryFullName: input.repositoryFullName,
      token,
    })
  } catch (error) {
    wrapGithubConnectorError(error)
  }

  const fileTextByPath = await getGithubDiscoveryFileTexts({
    branch: input.branch,
    config: githubConnectorAppConfig(),
    installationId: input.installationId,
    repositoryFullName: input.repositoryFullName,
    token,
    treeEntries: treeSnapshot.treeEntries,
  })
  const discovery = buildGithubRepoDiscovery({
    entries: treeSnapshot.treeEntries,
    fileTextByPath,
  })

  return {
    branch: input.branch,
    classification: discovery.classification,
    discoveredPlugins: discovery.discoveredPlugins,
    importPlansByPluginKey: buildGithubDiscoveryImportPlans({
      discoveredPlugins: discovery.discoveredPlugins,
      treeEntries: treeSnapshot.treeEntries,
    }),
    marketplace: discovery.marketplace,
    ref: input.ref,
    repositoryFullName: input.repositoryFullName,
    sourceRevisionRef: treeSnapshot.headSha,
    treeEntries: treeSnapshot.treeEntries,
    treeSummary: {
      scannedEntryCount: treeSnapshot.treeEntries.length,
      strategy: "git-tree-recursive",
      truncated: treeSnapshot.truncated,
    } satisfies GithubConnectorDiscoveryTreeSummary,
    warnings: discovery.warnings,
  } satisfies GithubDiscoverySnapshot
}

async function computeGithubConnectorDiscovery(input: { connectorInstanceId: ConnectorInstanceId; context: PluginArchActorContext; token?: string }) {
  const discoveryContext = await getGithubDiscoveryContext(input)
  const snapshot = await computeGithubDiscoverySnapshot({
    branch: discoveryContext.branch,
    installationId: discoveryContext.installationId,
    ref: discoveryContext.ref,
    repositoryFullName: discoveryContext.repositoryFullName,
    token: input.token,
  })

  return {
    ...snapshot,
    connectorInstance: serializeConnectorInstance(discoveryContext.connectorInstance),
    connectorTarget: serializeConnectorTarget(discoveryContext.connectorTarget),
  } satisfies GithubConnectorDiscoveryComputation
}

async function persistGithubConnectorDiscoveryCache(input: {
  cache: GithubDiscoveryCacheEntry
  connectorTargetId: ConnectorTargetId
  context: PluginArchActorContext
}) {
  const target = await getConnectorTargetRow(input.context.organizationContext.organization.id, input.connectorTargetId)
  if (!target) {
    return
  }

  const targetConfig = target.targetConfigJson && typeof target.targetConfigJson === "object"
    ? target.targetConfigJson as Record<string, unknown>
    : {}
  await updateConnectorTarget({
    config: withGithubDiscoveryCache(targetConfig, input.cache),
    connectorTargetId: target.id,
    context: input.context,
    externalTargetRef: target.externalTargetRef,
    remoteId: target.remoteId,
  })
}

async function resolveGithubConnectorDiscovery(input: { connectorInstanceId: ConnectorInstanceId; context: PluginArchActorContext; forceRefresh?: boolean }) {
  const discoveryContext = await getGithubDiscoveryContext(input)
  const targetConfig = discoveryContext.connectorTarget.targetConfigJson && typeof discoveryContext.connectorTarget.targetConfigJson === "object"
    ? discoveryContext.connectorTarget.targetConfigJson as Record<string, unknown>
    : null
  const cached = readGithubDiscoveryCache(targetConfig)
  if (!input.forceRefresh
    && cached
    && cached.branch === discoveryContext.branch
    && cached.ref === discoveryContext.ref
    && cached.repositoryFullName === discoveryContext.repositoryFullName) {
    // A matching branch/ref says nothing about content: compare the cached
    // snapshot's commit SHA against the live head, otherwise the discovery
    // UI stays permanently stuck on the old repository structure after a
    // push (#1871). The probe is a single commits API call; if it fails
    // (rate limit, network), prefer availability and serve the cache.
    const liveHeadSha = await getGithubRepositoryHeadSha({
      branch: discoveryContext.branch,
      config: githubConnectorAppConfig(),
      installationId: discoveryContext.installationId,
      repositoryFullName: discoveryContext.repositoryFullName,
    }).catch(() => null)
    if (liveHeadSha === null || liveHeadSha === cached.sourceRevisionRef) {
      return {
        autoImportNewPlugins: discoveryContext.autoImportNewPlugins,
        cache: cached,
        connectorInstance: serializeConnectorInstance(discoveryContext.connectorInstance),
        connectorTarget: serializeConnectorTarget(discoveryContext.connectorTarget),
      }
    }
  }

  const computed = await computeGithubConnectorDiscovery(input)
  const cache = {
    branch: computed.branch,
    classification: computed.classification,
    discoveredPlugins: computed.discoveredPlugins,
    importPlansByPluginKey: computed.importPlansByPluginKey,
    marketplace: computed.marketplace,
    ref: computed.ref,
    repositoryFullName: computed.repositoryFullName,
    sourceRevisionRef: computed.sourceRevisionRef,
    treeSummary: computed.treeSummary,
    warnings: computed.warnings,
  } satisfies GithubDiscoveryCacheEntry
  await persistGithubConnectorDiscoveryCache({
    cache,
    connectorTargetId: computed.connectorTarget.id,
    context: input.context,
  })
  return {
    autoImportNewPlugins: discoveryContext.autoImportNewPlugins,
    cache,
    connectorInstance: computed.connectorInstance,
    connectorTarget: computed.connectorTarget,
  }
}

function discoveryMappingsForPlugin(plugin: GithubDiscoveredPlugin) {
  return [
    ...plugin.componentPaths.skills.map((selector) => ({
      objectType: "skill" as const,
      selector: plugin.sourceKind === "agent_plugin_manifest" ? selector : `${selector}/**`,
    })),
    ...plugin.componentPaths.commands.map((selector) => ({ objectType: "command" as const, selector: `${selector}/**` })),
    ...plugin.componentPaths.agents.map((selector) => ({ objectType: "agent" as const, selector: `${selector}/**` })),
    ...plugin.componentPaths.hooks.map((selector) => ({ objectType: "hook" as const, selector })),
    ...plugin.componentPaths.mcpServers.map((selector) => ({ objectType: "mcp" as const, selector })),
  ]
}

function mappingSelectorMatchesPath(selector: string, path: string) {
  const normalizedSelector = selector.trim().replace(/^\/+/, "")
  const normalizedPath = path.trim().replace(/^\/+/, "")
  if (normalizedSelector.endsWith("/**")) {
    const prefix = normalizedSelector.slice(0, -3)
    return normalizedPath.startsWith(`${prefix}/`)
  }
  return normalizedPath === normalizedSelector
}

function importableGithubPathsForMapping(input: {
  mapping: Pick<ReturnType<typeof serializeConnectorMapping>, "objectType" | "selector">
  treeEntries: GithubDiscoveryTreeEntry[]
}) {
  const matchingBlobs = input.treeEntries
    .filter((entry) => entry.kind === "blob")
    .filter((entry) => mappingSelectorMatchesPath(input.mapping.selector, entry.path))

  if (input.mapping.objectType === "skill") {
    const preferred = matchingBlobs.filter((entry) => entry.path.endsWith("/SKILL.md"))
    return preferred.length > 0 ? preferred : matchingBlobs.filter((entry) => entry.path.endsWith(".md"))
  }
  if (input.mapping.objectType === "agent") {
    const preferred = matchingBlobs.filter((entry) => entry.path.endsWith("/AGENT.md"))
    return preferred.length > 0 ? preferred : matchingBlobs.filter((entry) => entry.path.endsWith(".md"))
  }
  if (input.mapping.objectType === "command") {
    return matchingBlobs.filter((entry) => entry.path.endsWith(".md"))
  }
  return matchingBlobs
}

function parseMarkdownFrontmatter(rawSourceText: string): { body: string; data: Record<string, string> } {
  const match = rawSourceText.match(/^---\r?\n([\s\S]*?)\r?\n---\r?\n?([\s\S]*)$/)
  if (!match) {
    return { body: rawSourceText, data: {} }
  }

  const [, yaml, body] = match
  const data: Record<string, string> = {}
  for (const line of yaml.split(/\r?\n/)) {
    const trimmed = line.trim()
    if (!trimmed || trimmed.startsWith("#")) continue
    const colonIndex = trimmed.indexOf(":")
    if (colonIndex === -1) continue
    const key = trimmed.slice(0, colonIndex).trim()
    let value = trimmed.slice(colonIndex + 1).trim()
    if (value.length > 1) {
      const first = value[0]
      const last = value[value.length - 1]
      if ((first === '"' && last === '"') || (first === "'" && last === "'")) {
        value = value.slice(1, -1)
      }
    }
    if (!key || !value) continue
    data[key] = value
  }
  return { body: body ?? "", data }
}

function importedObjectMetadata(input: { objectType: ConnectorMappingRow["objectType"]; path: string; rawSourceText: string }) {
  const pathSegments = input.path.split("/")
  const fileName = pathSegments[pathSegments.length - 1] ?? input.path
  const parentName = pathSegments[pathSegments.length - 2] ?? pathSegments[pathSegments.length - 1] ?? "Imported"
  const nameFromFile = fileName.replace(/\.[^.]+$/, "")
  const preferredName = input.objectType === "skill" || input.objectType === "agent"
    ? (fileName.toUpperCase() === "SKILL.MD" || fileName.toUpperCase() === "AGENT.MD" ? parentName : nameFromFile)
    : nameFromFile

  const isMarkdown = fileName.toLowerCase().endsWith(".md") || fileName.toLowerCase().endsWith(".mdx")
  const frontmatter = isMarkdown ? parseMarkdownFrontmatter(input.rawSourceText) : null
  const frontmatterName = frontmatter?.data.name ?? frontmatter?.data.title
  const frontmatterDescription = frontmatter?.data.description ?? frontmatter?.data.summary

  const metadata: Record<string, unknown> = {
    name: frontmatterName?.trim() || preferredName,
    relativePath: input.path,
  }
  if (frontmatterDescription?.trim()) {
    metadata.description = frontmatterDescription.trim()
  }
  if (frontmatter && Object.keys(frontmatter.data).length > 0) {
    metadata.frontmatter = frontmatter.data
  }

  return {
    metadata,
    normalizedPayloadJson: (() => {
      if (!fileName.endsWith(".json")) {
        return undefined
      }
      try {
        const parsed = JSON.parse(input.rawSourceText) as unknown
        return typeof parsed === "object" && parsed !== null && !Array.isArray(parsed) ? parsed as Record<string, unknown> : undefined
      } catch {
        return undefined
      }
    })(),
  }
}

export function deriveGithubImportedObjectProjection(input: { objectType: ConnectorMappingRow["objectType"]; path: string; rawSourceText: string }) {
  const metadata = importedObjectMetadata({ objectType: input.objectType, path: input.path, rawSourceText: input.rawSourceText })
  const frontmatterRecord = metadata.metadata && typeof metadata.metadata.frontmatter === "object"
    ? metadata.metadata.frontmatter as Record<string, unknown>
    : null
  const hasFrontmatter = frontmatterRecord && Object.keys(frontmatterRecord).length > 0
  // Skill projections need the full SKILL.md: deriveSkillProjection parses and
  // validates the frontmatter itself, so stripping it here made every GitHub
  // connector skill import fail with invalid_skill_frontmatter.
  const projectionRawSource = input.objectType !== "skill" && hasFrontmatter
    ? parseMarkdownFrontmatter(input.rawSourceText).body
    : input.rawSourceText
  return {
    metadata,
    projection: deriveProjection({
      objectType: input.objectType,
      value: {
        metadata: metadata.metadata,
        normalizedPayloadJson: metadata.normalizedPayloadJson,
        rawSourceText: projectionRawSource,
      },
    }),
  }
}

async function findActiveConnectorSourceBinding(input: {
  connectorMappingId: ConnectorMappingId
  externalLocator: string
  organizationId: OrganizationId
}) {
  const rows = await db
    .select()
    .from(ConnectorSourceBindingTable)
    .where(and(
      eq(ConnectorSourceBindingTable.organizationId, input.organizationId),
      eq(ConnectorSourceBindingTable.connectorMappingId, input.connectorMappingId),
      eq(ConnectorSourceBindingTable.externalLocator, input.externalLocator),
      isNull(ConnectorSourceBindingTable.deletedAt),
    ))
    .limit(1)
  return rows[0] ?? null
}

async function materializeGithubImportedObject(input: {
  connectorInstance: ReturnType<typeof serializeConnectorInstance>
  connectorMapping: ReturnType<typeof serializeConnectorMapping>
  connectorSyncEventId?: ConnectorSyncEventId
  connectorTarget: ReturnType<typeof serializeConnectorTarget>
  context: PluginArchActorContext
  externalLocator: string
  rawSourceText: string
  sourceFileRevisionRef?: string | null
  sourceRevisionRef: string
}) {
  const organizationId = input.context.organizationContext.organization.id
  const createdByOrgMembershipId = input.context.organizationContext.currentMember.id
  const now = new Date()
  const { metadata, projection } = deriveGithubImportedObjectProjection({
    objectType: input.connectorMapping.objectType,
    path: input.externalLocator,
    rawSourceText: input.rawSourceText,
  })
  const fileName = input.externalLocator.split("/").filter(Boolean).at(-1) ?? input.externalLocator
  const fileExtension = fileName.includes(".") ? fileName.split(".").at(-1) ?? null : null

  // Prefer the per-file blob sha when the tree snapshot provides one so an unchanged file can be
  // skipped even when the head commit moved; fall back to the head revision otherwise.
  const bindingRevisionRef = input.sourceFileRevisionRef ?? input.sourceRevisionRef
  const existingBinding = await findActiveConnectorSourceBinding({
    connectorMappingId: input.connectorMapping.id,
    externalLocator: input.externalLocator,
    organizationId,
  })

  if (!existingBinding) {
    const configObjectId = createDenTypeId("configObject")
    const versionId = createDenTypeId("configObjectVersion")
    await db.transaction(async (tx) => {
      await tx.insert(ConfigObjectTable).values({
        connectorInstanceId: input.connectorInstance.id,
        createdAt: now,
        createdByOrgMembershipId,
        currentFileExtension: normalizeOptionalString(fileExtension ?? undefined),
        currentFileName: fileName,
        currentRelativePath: input.externalLocator,
        deletedAt: null,
        description: projection.description,
        id: configObjectId,
        objectType: input.connectorMapping.objectType,
        organizationId,
        searchText: projection.searchText,
        sourceMode: "connector",
        status: "active",
        title: projection.title,
        updatedAt: now,
      })

      await tx.insert(ConfigObjectVersionTable).values({
        configObjectId,
        connectorSyncEventId: input.connectorSyncEventId ?? null,
        createdAt: now,
        createdByOrgMembershipId,
        createdVia: "connector",
        id: versionId,
        isDeletedVersion: false,
        normalizedPayloadJson: metadata.normalizedPayloadJson ?? null,
        organizationId,
        rawSourceText: normalizeOptionalString(input.rawSourceText),
        schemaVersion: null,
        sourceRevisionRef: input.sourceRevisionRef,
      })

      await tx.insert(ConfigObjectAccessGrantTable).values({
        configObjectId,
        createdAt: now,
        createdByOrgMembershipId,
        id: createDenTypeId("configObjectAccessGrant"),
        organizationId,
        orgMembershipId: createdByOrgMembershipId,
        orgWide: false,
        role: "manager",
        teamId: null,
      })

      if (input.connectorMapping.pluginId) {
        await tx.insert(PluginConfigObjectTable).values({
          configObjectId,
          connectorMappingId: input.connectorMapping.id,
          createdAt: now,
          createdByOrgMembershipId,
          id: createDenTypeId("pluginConfigObject"),
          membershipSource: "connector",
          organizationId,
          pluginId: input.connectorMapping.pluginId,
          removedAt: null,
        })
      }

      await tx.insert(ConnectorSourceBindingTable).values({
        configObjectId,
        connectorInstanceId: input.connectorInstance.id,
        connectorMappingId: input.connectorMapping.id,
        connectorTargetId: input.connectorTarget.id,
        connectorType: input.connectorTarget.connectorType,
        createdAt: now,
        deletedAt: null,
        externalLocator: input.externalLocator,
        externalStableRef: input.externalLocator,
        id: createDenTypeId("connectorSourceBinding"),
        lastSeenSourceRevisionRef: bindingRevisionRef,
        organizationId,
        remoteId: input.connectorTarget.remoteId,
        status: "active",
        updatedAt: now,
      })
    })

    return getConfigObjectDetail(input.context, configObjectId)
  }

  const binding = existingBinding
  if (binding.lastSeenSourceRevisionRef !== bindingRevisionRef && binding.lastSeenSourceRevisionRef !== input.sourceRevisionRef) {
    const versionId = createDenTypeId("configObjectVersion")
    await db.transaction(async (tx) => {
      await tx.update(ConfigObjectTable).set({
        currentFileExtension: normalizeOptionalString(fileExtension ?? undefined),
        currentFileName: fileName,
        currentRelativePath: input.externalLocator,
        description: projection.description,
        searchText: projection.searchText,
        status: "active",
        title: projection.title,
        updatedAt: now,
      }).where(eq(ConfigObjectTable.id, binding.configObjectId))

      await tx.insert(ConfigObjectVersionTable).values({
        configObjectId: binding.configObjectId,
        connectorSyncEventId: input.connectorSyncEventId ?? null,
        createdAt: now,
        createdByOrgMembershipId,
        createdVia: "connector",
        id: versionId,
        isDeletedVersion: false,
        normalizedPayloadJson: metadata.normalizedPayloadJson ?? null,
        organizationId,
        rawSourceText: normalizeOptionalString(input.rawSourceText),
        schemaVersion: null,
        sourceRevisionRef: input.sourceRevisionRef,
      })

      if (input.connectorMapping.pluginId) {
        const membership = await tx
          .select({ id: PluginConfigObjectTable.id })
          .from(PluginConfigObjectTable)
          .where(and(
            eq(PluginConfigObjectTable.pluginId, input.connectorMapping.pluginId),
            eq(PluginConfigObjectTable.configObjectId, binding.configObjectId),
          ))
          .limit(1)
        if (membership[0]) {
          await tx.update(PluginConfigObjectTable).set({
            connectorMappingId: input.connectorMapping.id,
            membershipSource: "connector",
            removedAt: null,
          }).where(eq(PluginConfigObjectTable.id, membership[0].id))
        } else {
          await tx.insert(PluginConfigObjectTable).values({
            configObjectId: binding.configObjectId,
            connectorMappingId: input.connectorMapping.id,
            createdAt: now,
            createdByOrgMembershipId,
            id: createDenTypeId("pluginConfigObject"),
            membershipSource: "connector",
            organizationId,
            pluginId: input.connectorMapping.pluginId,
            removedAt: null,
          })
        }
      }

      await tx.update(ConnectorSourceBindingTable).set({
        deletedAt: null,
        lastSeenSourceRevisionRef: bindingRevisionRef,
        status: "active",
        updatedAt: now,
      }).where(eq(ConnectorSourceBindingTable.id, binding.id))
    })
  }

  return getConfigObjectDetail(input.context, binding.configObjectId)
}

async function materializeGithubImportPlans(input: {
  connectorInstance: ReturnType<typeof serializeConnectorInstance>
  connectorSyncEventId?: ConnectorSyncEventId
  connectorTarget: ReturnType<typeof serializeConnectorTarget>
  context: PluginArchActorContext
  importPlans: Array<{ fileShaByPath?: Record<string, string>; mapping: ReturnType<typeof serializeConnectorMapping>; paths: string[] }>
  sourceRevisionRef: string
}) {
  const config = githubConnectorAppConfig()
  const targetConfig = input.connectorTarget.targetConfigJson && typeof input.connectorTarget.targetConfigJson === "object"
    ? input.connectorTarget.targetConfigJson as Record<string, unknown>
    : {}
  const branch = typeof targetConfig.branch === "string" ? targetConfig.branch : input.connectorTarget.externalTargetRef ?? ""
  const installationId = typeof input.connectorInstance.instanceConfigJson === "object" && input.connectorInstance.instanceConfigJson && typeof (input.connectorInstance.instanceConfigJson as Record<string, unknown>).installationId === "number"
    ? (input.connectorInstance.instanceConfigJson as Record<string, unknown>).installationId as number
    : null
  const repositoryFullName = typeof targetConfig.repositoryFullName === "string" ? targetConfig.repositoryFullName : input.connectorTarget.remoteId
  if (!installationId || !branch || !repositoryFullName) {
    throw new PluginArchRouteFailure(409, "invalid_github_materialization_context", "GitHub connector target is missing required materialization context.")
  }

  const token = await getGithubInstallationAccessToken({
    config,
    installationId,
  })
  const organizationId = input.context.organizationContext.organization.id
  const plannedFiles = input.importPlans.flatMap((plan) => plan.paths.map((path) => ({
    mapping: plan.mapping,
    path,
    sourceFileRevisionRef: plan.fileShaByPath?.[path] ?? null,
  })))
  const existingBindings = await Promise.all(plannedFiles.map((file) => findActiveConnectorSourceBinding({
    connectorMappingId: file.mapping.id,
    externalLocator: file.path,
    organizationId,
  })))
  const fetchResults = await fetchGithubImportFilesWithRevisionGuard({
    fetchFile: (path) => getGithubRepositoryTextFile({
      config,
      installationId,
      path,
      ref: branch,
      repositoryFullName,
      token,
    }),
    files: plannedFiles.map((file, index) => ({
      lastSeenSourceRevisionRef: existingBindings[index]?.lastSeenSourceRevisionRef ?? null,
      path: file.path,
      sourceFileRevisionRef: file.sourceFileRevisionRef,
      sourceRevisionRef: input.sourceRevisionRef,
    })),
  })

  const materializedConfigObjects: ReturnType<typeof serializeConfigObject>[] = []
  let firstFetchFailure: { error: unknown } | null = null
  for (const [index, file] of plannedFiles.entries()) {
    const result = fetchResults[index]
    if (result.status === "failed") {
      firstFetchFailure = firstFetchFailure ?? { error: result.error }
      continue
    }
    if (result.status === "skipped_unchanged") {
      // The file content is already materialized at this revision: no fetch, no new version.
      const binding = existingBindings[index]
      if (binding) {
        materializedConfigObjects.push(await getConfigObjectDetail(input.context, binding.configObjectId))
      }
      continue
    }
    if (!result.rawSourceText) {
      continue
    }
    materializedConfigObjects.push(await materializeGithubImportedObject({
      connectorInstance: input.connectorInstance,
      connectorMapping: file.mapping,
      connectorSyncEventId: input.connectorSyncEventId,
      connectorTarget: input.connectorTarget,
      context: input.context,
      externalLocator: file.path,
      rawSourceText: result.rawSourceText,
      sourceFileRevisionRef: file.sourceFileRevisionRef,
      sourceRevisionRef: input.sourceRevisionRef,
    }))
  }

  if (firstFetchFailure) {
    wrapGithubConnectorError(firstFetchFailure.error)
  }

  return materializedConfigObjects
}

async function ensureDiscoveryPlugin(input: {
  context: PluginArchActorContext
  description: string | null
  name: string
  sourceFormat: "agent-plugin" | "claude-plugin"
  sourceRepositoryUrl: string
  sourceSchemaVersion: string | null
}) {
  const existing = await db
    .select()
    .from(PluginTable)
    .where(and(
      eq(PluginTable.organizationId, input.context.organizationContext.organization.id),
      eq(PluginTable.name, input.name.trim()),
      isNull(PluginTable.deletedAt),
    ))
    .orderBy(asc(PluginTable.createdAt), asc(PluginTable.id))
    .limit(1)

  if (existing[0]) {
    if (
      existing[0].sourceFormat !== input.sourceFormat
      || existing[0].sourceRepositoryUrl !== input.sourceRepositoryUrl
      || existing[0].sourceSchemaVersion !== input.sourceSchemaVersion
    ) {
      await db.update(PluginTable).set({
        sourceFormat: input.sourceFormat,
        sourceRepositoryUrl: input.sourceRepositoryUrl,
        sourceSchemaVersion: input.sourceSchemaVersion,
      }).where(eq(PluginTable.id, existing[0].id))
    }
    return serializePlugin({
      ...existing[0],
      sourceFormat: input.sourceFormat,
      sourceRepositoryUrl: input.sourceRepositoryUrl,
      sourceSchemaVersion: input.sourceSchemaVersion,
    }, 0)
  }

  return createPlugin({
    context: input.context,
    description: input.description,
    name: input.name,
    sourceFormat: input.sourceFormat,
    sourceRepositoryUrl: input.sourceRepositoryUrl,
    sourceSchemaVersion: input.sourceSchemaVersion,
  })
}

async function ensureDiscoveryMarketplace(input: { context: PluginArchActorContext; description: string | null; name: string }) {
  const existing = await db
    .select()
    .from(MarketplaceTable)
    .where(and(
      eq(MarketplaceTable.organizationId, input.context.organizationContext.organization.id),
      eq(MarketplaceTable.name, input.name.trim()),
      isNull(MarketplaceTable.deletedAt),
    ))
    .orderBy(asc(MarketplaceTable.createdAt), asc(MarketplaceTable.id))
    .limit(1)

  if (existing[0]) {
    return serializeMarketplace(existing[0], 0)
  }

  return createMarketplace({
    context: input.context,
    description: input.description,
    name: input.name,
  })
}

async function ensureDiscoveryMapping(input: {
  connectorTargetId: ConnectorTargetId
  context: PluginArchActorContext
  objectType: ConnectorMappingRow["objectType"]
  pluginId: PluginId
  selector: string
}) {
  const existing = await db
    .select()
    .from(ConnectorMappingTable)
    .where(and(
      eq(ConnectorMappingTable.connectorTargetId, input.connectorTargetId),
      eq(ConnectorMappingTable.mappingKind, "path"),
      eq(ConnectorMappingTable.objectType, input.objectType),
      eq(ConnectorMappingTable.pluginId, input.pluginId),
      eq(ConnectorMappingTable.selector, input.selector),
    ))
    .limit(1)

  if (existing[0]) {
    return serializeConnectorMapping(existing[0])
  }

  return createConnectorMapping({
    autoAddToPlugin: true,
    config: {
      discoverySourceKind: input.objectType,
    },
    connectorTargetId: input.connectorTargetId,
    context: input.context,
    mappingKind: "path",
    objectType: input.objectType,
    pluginId: input.pluginId,
    selector: input.selector,
  })
}

export async function createGithubConnectorAccount(input: { accountLogin: string; accountType: "Organization" | "User"; context: PluginArchActorContext; displayName: string; installationId: number }) {
  return createConnectorAccount({
    connectorType: "github",
    context: input.context,
    displayName: input.displayName,
    metadata: {
      accountLogin: input.accountLogin,
      accountType: input.accountType,
      repositories: [],
      repositorySelection: "all",
      settingsUrl: null,
    },
    remoteId: String(input.installationId),
  })
}

async function upsertGithubConnectorAccountFromInstallation(input: { context: PluginArchActorContext; installationId: number }) {
  let installation: Awaited<ReturnType<typeof getGithubInstallationSummary>>
  try {
    installation = await getGithubInstallationSummary({
      config: githubConnectorAppConfig(),
      installationId: input.installationId,
    })
  } catch (error) {
    wrapGithubConnectorError(error)
  }
  const organizationId = input.context.organizationContext.organization.id
  const existingRows = await db
    .select()
    .from(ConnectorAccountTable)
    .where(and(
      eq(ConnectorAccountTable.organizationId, organizationId),
      eq(ConnectorAccountTable.connectorType, "github"),
      eq(ConnectorAccountTable.remoteId, String(input.installationId)),
    ))
    .limit(1)

  const metadata = {
    accountLogin: installation.accountLogin,
    accountType: installation.accountType,
    repositories: [],
    repositorySelection: installation.repositorySelection,
    settingsUrl: installation.settingsUrl,
  }

  if (!existingRows[0]) {
    return createConnectorAccount({
      connectorType: "github",
      context: input.context,
      displayName: installation.displayName,
      externalAccountRef: installation.accountLogin,
      metadata,
      remoteId: String(input.installationId),
    })
  }

  await db.update(ConnectorAccountTable).set({
    displayName: installation.displayName,
    externalAccountRef: installation.accountLogin,
    metadataJson: {
      ...(existingRows[0].metadataJson ?? {}),
      ...metadata,
    },
    status: "active",
    updatedAt: new Date(),
  }).where(eq(ConnectorAccountTable.id, existingRows[0].id))

  return getConnectorAccountDetail(input.context, existingRows[0].id)
}

export async function startGithubConnectorInstall(input: { context: PluginArchActorContext; returnPath: string }) {
  const returnPath = input.returnPath.trim()
  if (!returnPath.startsWith("/") || returnPath.startsWith("//")) {
    throw new PluginArchRouteFailure(400, "invalid_return_path", "GitHub install return path must be a safe relative path.")
  }

  let app: Awaited<ReturnType<typeof getGithubAppSummary>>
  try {
    app = await getGithubAppSummary({ config: githubConnectorAppConfig() })
  } catch (error) {
    wrapGithubConnectorError(error)
  }
  const state = createGithubInstallStateToken({
    orgId: input.context.organizationContext.organization.id,
    returnPath,
    secret: env.betterAuthSecret,
    userId: input.context.organizationContext.currentMember.userId,
  })

  return {
    redirectUrl: buildGithubAppInstallUrl({ app, state }),
    state,
  }
}

export async function completeGithubConnectorInstall(input: { context: PluginArchActorContext; installationId: number; state: string }) {
  const parsedState = consumeGithubInstallState(input.state)
  if (parsedState.orgId !== input.context.organizationContext.organization.id) {
    throw new PluginArchRouteFailure(409, "github_install_org_mismatch", "GitHub install state does not match the current organization.")
  }
  if (parsedState.userId !== input.context.organizationContext.currentMember.userId) {
    throw new PluginArchRouteFailure(409, "github_install_user_mismatch", "GitHub install state does not match the current user.")
  }

  const connectorAccount = await upsertGithubConnectorAccountFromInstallation({
    context: input.context,
    installationId: input.installationId,
  })

  return {
    connectorAccount,
    // Keep install completion fast. The connected-account screen loads repositories next.
    repositories: [],
  }
}

export async function getGithubConnectorDiscovery(input: { connectorInstanceId: ConnectorInstanceId; context: PluginArchActorContext }) {
  const discovery = await resolveGithubConnectorDiscovery(input)
  return {
    autoImportNewPlugins: discovery.autoImportNewPlugins,
    classification: discovery.cache.classification,
    connectorInstance: discovery.connectorInstance,
    connectorTarget: discovery.connectorTarget,
    discoveredPlugins: discovery.cache.discoveredPlugins,
    repositoryFullName: discovery.cache.repositoryFullName,
    sourceRevisionRef: discovery.cache.sourceRevisionRef,
    steps: buildGithubConnectorDiscoverySteps({
      classification: discovery.cache.classification,
      discoveredPlugins: discovery.cache.discoveredPlugins,
    }),
    treeSummary: discovery.cache.treeSummary,
    warnings: discovery.cache.warnings,
  }
}

export async function getGithubConnectorDiscoveryTree(input: { connectorInstanceId: ConnectorInstanceId; context: PluginArchActorContext; cursor?: string; limit?: number; prefix?: string }) {
  const discovery = await computeGithubConnectorDiscovery({ connectorInstanceId: input.connectorInstanceId, context: input.context })
  return pagedGithubDiscoveryTree({
    cursor: input.cursor,
    entries: discovery.treeEntries,
    limit: input.limit,
    prefix: input.prefix,
  })
}

export async function applyGithubConnectorDiscovery(input: { autoImportNewPlugins: boolean; connectorInstanceId: ConnectorInstanceId; connectorSyncEventId?: ConnectorSyncEventId; context: PluginArchActorContext; forceRefresh?: boolean; selectedKeys: string[] }) {
  if (!isPluginArchOrgAdmin(input.context)) {
    throw new PluginArchAuthorizationError(403, "forbidden", "Only organization owners and admins can apply connector discovery.")
  }

  const discovery = await resolveGithubConnectorDiscovery({ connectorInstanceId: input.connectorInstanceId, context: input.context, forceRefresh: input.forceRefresh })
  const selectedKeySet = new Set(input.selectedKeys.map((key) => key.trim()).filter(Boolean))
  const selectedPlugins = discovery.cache.discoveredPlugins.filter((plugin) => plugin.supported && selectedKeySet.has(plugin.key))
  await db.update(ConnectorInstanceTable).set({
    instanceConfigJson: {
      ...((discovery.connectorInstance.instanceConfigJson && typeof discovery.connectorInstance.instanceConfigJson === "object")
        ? discovery.connectorInstance.instanceConfigJson as Record<string, unknown>
        : {}),
      autoImportNewPlugins: input.autoImportNewPlugins,
    },
    updatedAt: new Date(),
  }).where(eq(ConnectorInstanceTable.id, discovery.connectorInstance.id))

  const marketplaceInfo = discovery.cache.marketplace
  const marketplaceName = marketplaceInfo?.name?.trim() || discovery.cache.repositoryFullName
  const marketplaceDescription = marketplaceInfo?.description?.trim()
    ?? `Imported from GitHub marketplace repository ${discovery.cache.repositoryFullName}.`
  const createdMarketplace = discovery.cache.classification === "claude_marketplace_repo"
    ? await ensureDiscoveryMarketplace({
        context: input.context,
        description: marketplaceDescription,
        name: marketplaceName,
      })
    : null

  const plugins = [] as Array<ReturnType<typeof serializePlugin>>
  const mappings = [] as Array<ReturnType<typeof serializeConnectorMapping>>
  const importPlans = [] as Array<{ fileShaByPath?: Record<string, string>; mapping: ReturnType<typeof serializeConnectorMapping>; paths: string[] }>
  for (const discoveredPlugin of selectedPlugins) {
    const plugin = await ensureDiscoveryPlugin({
      context: input.context,
      description: discoveredPlugin.description,
      name: discoveredPlugin.displayName,
      sourceFormat: discoveredPlugin.sourceKind === "agent_plugin_manifest" ? "agent-plugin" : "claude-plugin",
      sourceRepositoryUrl: `https://github.com/${discovery.cache.repositoryFullName}`,
      sourceSchemaVersion: discoveredPlugin.sourceSchemaVersion,
    })
    plugins.push(plugin)

    if (createdMarketplace) {
      await attachPluginToMarketplace({
        context: input.context,
        marketplaceId: createdMarketplace.id,
        membershipSource: "connector",
        pluginId: plugin.id,
      })
    }

    for (const plan of discovery.cache.importPlansByPluginKey[discoveredPlugin.key] ?? []) {
      const mapping = await ensureDiscoveryMapping({
        connectorTargetId: discovery.connectorTarget.id,
        context: input.context,
        objectType: plan.objectType,
        pluginId: plugin.id,
        selector: plan.selector,
      })
      mappings.push(mapping)
      importPlans.push({ fileShaByPath: plan.fileShaByPath, mapping, paths: plan.paths })
    }
  }

  const materializedConfigObjects = await materializeGithubImportPlans({
    connectorInstance: discovery.connectorInstance,
    connectorSyncEventId: input.connectorSyncEventId,
    connectorTarget: discovery.connectorTarget,
    context: input.context,
    importPlans,
    sourceRevisionRef: discovery.cache.sourceRevisionRef,
  })

  return {
    autoImportNewPlugins: input.autoImportNewPlugins,
    createdMarketplace,
    connectorInstance: discovery.connectorInstance,
    connectorTarget: discovery.connectorTarget,
    createdPlugins: plugins,
    createdMappings: mappings,
    materializedConfigObjects,
    sourceRevisionRef: discovery.cache.sourceRevisionRef,
  }
}

export async function listGithubRepositories(input: { connectorAccountId: ConnectorAccountId; context: PluginArchActorContext; cursor?: string; limit?: number; q?: string }) {
  const account = await getConnectorAccountRow(input.context.organizationContext.organization.id, input.connectorAccountId)
  if (!account) {
    throw new PluginArchRouteFailure(404, "connector_account_not_found", "Connector account not found.")
  }
  if (account.connectorType !== "github") {
    throw new PluginArchRouteFailure(409, "github_connector_account_required", "Connector account is not a GitHub account.")
  }

  const installationId = Number(account.remoteId)
  if (!Number.isFinite(installationId) || installationId <= 0) {
    throw new PluginArchRouteFailure(409, "invalid_github_installation_id", "Connector account does not have a valid GitHub installation id.")
  }

  let repositories: RepositorySummary[]
  let installationSummary: Awaited<ReturnType<typeof getGithubInstallationSummary>>
  try {
    repositories = await listGithubInstallationRepositories({
      config: githubConnectorAppConfig(),
      installationId,
    })
    installationSummary = await getGithubInstallationSummary({
      config: githubConnectorAppConfig(),
      installationId,
    })
  } catch (error) {
    wrapGithubConnectorError(error)
  }

  const existingMetadata = account.metadataJson && typeof account.metadataJson === "object"
    ? account.metadataJson as Record<string, unknown>
    : {}
  await db.update(ConnectorAccountTable).set({
    metadataJson: {
      ...existingMetadata,
      repositories: repositories.map((repository) => ({
        defaultBranch: repository.defaultBranch,
        fullName: repository.fullName,
        hasPluginManifest: repository.hasPluginManifest ?? false,
        id: repository.id,
        manifestKind: repository.manifestKind ?? null,
        marketplacePluginCount: repository.marketplacePluginCount ?? null,
        private: repository.private,
      })),
      repositorySelection: installationSummary.repositorySelection,
      settingsUrl: installationSummary.settingsUrl,
    },
    updatedAt: new Date(),
  }).where(eq(ConnectorAccountTable.id, account.id))

  const filtered = repositories
    .filter((repository) => !input.q || `${repository.fullName}\n${repository.defaultBranch ?? ""}`.toLowerCase().includes(input.q.toLowerCase()))
    .map((repository) => ({ ...repository, id: String(repository.id) }))
  const page = pageItems(filtered, input.cursor, input.limit)
  return {
    items: page.items.map((repository) => ({
      defaultBranch: repository.defaultBranch,
      fullName: repository.fullName,
      hasPluginManifest: Boolean(repository.hasPluginManifest),
      id: Number(repository.id),
      manifestKind: repository.manifestKind ?? null,
      marketplacePluginCount: repository.marketplacePluginCount ?? null,
      private: repository.private,
    })),
    nextCursor: page.nextCursor,
  }
}

export async function validateGithubTarget(input: {
  branch: string
  config?: ReturnType<typeof githubConnectorAppConfig>
  installationId: number
  ref: string
  repositoryFullName: string
  repositoryId: number
  token?: string
}) {
  try {
    return await validateGithubInstallationTarget({
      branch: input.branch,
      config: input.config ?? githubConnectorAppConfig(),
      installationId: input.installationId,
      ref: input.ref,
      repositoryFullName: input.repositoryFullName,
      repositoryId: input.repositoryId,
      token: input.token,
    })
  } catch (error) {
    wrapGithubConnectorError(error)
  }
}

export async function githubSetup(input: {
  branch: string
  connectorAccountId?: ConnectorAccountId
  connectorInstanceName: string
  context: PluginArchActorContext
  installationId: number
  mappings: Array<{ autoAddToPlugin: boolean; config?: Record<string, unknown>; mappingKind: ConnectorMappingRow["mappingKind"]; objectType: ConnectorMappingRow["objectType"]; pluginId?: PluginId | null; selector: string }>
  ref: string
  repositoryFullName: string
  repositoryId: number
}) {
  const githubConfig = githubConnectorAppConfig()
  const installationToken = await getGithubInstallationAccessToken({
    config: githubConfig,
    installationId: input.installationId,
  })
  const validation = await validateGithubTarget({
    branch: input.branch,
    config: githubConfig,
    installationId: input.installationId,
    ref: input.ref,
    repositoryFullName: input.repositoryFullName,
    repositoryId: input.repositoryId,
    token: installationToken,
  })
  if (!validation.repositoryAccessible) {
    throw new PluginArchRouteFailure(409, "github_repository_not_accessible", "GitHub repository is not accessible for this installation.")
  }
  if (!validation.branchExists) {
    throw new PluginArchRouteFailure(409, "github_branch_not_found", "GitHub branch/ref could not be validated for this repository.")
  }

  const discovery = await computeGithubDiscoverySnapshot({
    branch: input.branch,
    installationId: input.installationId,
    ref: input.ref,
    repositoryFullName: input.repositoryFullName,
    token: installationToken,
  })

  let connectorAccountId = input.connectorAccountId as ConnectorAccountId | undefined
  let connectorAccountDetail = connectorAccountId ? await getConnectorAccountDetail(input.context, connectorAccountId) : null
  if (!connectorAccountId || !connectorAccountDetail) {
    connectorAccountDetail = await createGithubConnectorAccount({
      accountLogin: input.repositoryFullName.split("/")[0] ?? input.repositoryFullName,
      accountType: "Organization",
      context: input.context,
      displayName: input.repositoryFullName,
      installationId: input.installationId,
    })
    connectorAccountId = connectorAccountDetail.id
  }

  const connectorInstance = await createConnectorInstance({
    connectorAccountId,
    connectorType: "github",
    config: {
      autoImportNewPlugins: true,
      installationId: input.installationId,
    },
    context: input.context,
    name: input.connectorInstanceName,
    remoteId: input.repositoryFullName,
  })

  const connectorTarget = await createConnectorTarget({
    config: withGithubDiscoveryCache({
      branch: input.branch,
      defaultBranch: validation.defaultBranch,
      ref: input.ref,
      repositoryFullName: input.repositoryFullName,
      repositoryId: input.repositoryId,
    }, {
      branch: discovery.branch,
      classification: discovery.classification,
      discoveredPlugins: discovery.discoveredPlugins,
      importPlansByPluginKey: discovery.importPlansByPluginKey,
      marketplace: discovery.marketplace,
      ref: discovery.ref,
      repositoryFullName: discovery.repositoryFullName,
      sourceRevisionRef: discovery.sourceRevisionRef,
      treeSummary: discovery.treeSummary,
      warnings: discovery.warnings,
    }),
    connectorInstanceId: connectorInstance.id,
    connectorType: "github",
    context: input.context,
    externalTargetRef: input.branch,
    remoteId: input.repositoryFullName,
    targetKind: "repository_branch",
  })

  for (const mapping of input.mappings) {
    await createConnectorMapping({
      autoAddToPlugin: mapping.autoAddToPlugin,
      config: mapping.config,
      connectorTargetId: connectorTarget.id,
      context: input.context,
      mappingKind: mapping.mappingKind,
      objectType: mapping.objectType,
      pluginId: mapping.pluginId,
      selector: mapping.selector,
    })
  }

  return {
    connectorAccount: connectorAccountDetail,
    connectorInstance,
    connectorTarget,
  }
}

type GithubWebhookTargetRow = {
  instance: ConnectorInstanceRow
  target: ConnectorTargetRow
}

async function listActiveGithubWebhookTargets(installationId: number) {
  return db
    .select({ instance: ConnectorInstanceTable, target: ConnectorTargetTable })
    .from(ConnectorTargetTable)
    .innerJoin(ConnectorInstanceTable, eq(ConnectorTargetTable.connectorInstanceId, ConnectorInstanceTable.id))
    .innerJoin(ConnectorAccountTable, eq(ConnectorInstanceTable.connectorAccountId, ConnectorAccountTable.id))
    .where(and(
      eq(ConnectorTargetTable.connectorType, "github"),
      eq(ConnectorTargetTable.organizationId, ConnectorInstanceTable.organizationId),
      eq(ConnectorAccountTable.organizationId, ConnectorInstanceTable.organizationId),
      eq(ConnectorAccountTable.connectorType, "github"),
      eq(ConnectorAccountTable.remoteId, String(installationId)),
      eq(ConnectorAccountTable.status, "active"),
      eq(ConnectorInstanceTable.status, "active"),
    ))
}

function githubWebhookTargetMatchesRepository(input: {
  repositoryFullName: string
  repositoryId: number
  row: GithubWebhookTargetRow
}) {
  const targetConfig = input.row.target.targetConfigJson
  const storedRepositoryId = targetConfig.repositoryId
  return typeof storedRepositoryId === "number"
    ? storedRepositoryId === input.repositoryId
    : input.row.target.remoteId === input.repositoryFullName
}

function renamedRepositoryPreviousFullName(payload: Record<string, unknown>, repositoryFullName: string) {
  const changes = isRecord(payload.changes) ? payload.changes : null
  const repositoryChange = changes && isRecord(changes.repository) ? changes.repository : null
  const nameChange = repositoryChange && isRecord(repositoryChange.name) ? repositoryChange.name : null
  const previousName = nameChange && typeof nameChange.from === "string" ? nameChange.from.trim() : ""
  if (!previousName) return ""
  if (previousName.includes("/")) return previousName
  const owner = repositoryFullName.split("/")[0]?.trim() ?? ""
  return owner ? `${owner}/${previousName}` : previousName
}

async function handleGithubRepositoryRenamed(input: {
  deliveryId: string
  installationId: number
  payload: Record<string, unknown>
  repositoryFullName?: string
  repositoryId?: number
}) {
  const repositoryFullName = input.repositoryFullName
  const repositoryId = input.repositoryId
  if (!repositoryFullName || !repositoryId) {
    return 0
  }
  const previousFullName = renamedRepositoryPreviousFullName(input.payload, repositoryFullName)
  if (!previousFullName) {
    return 0
  }

  const rows = await listActiveGithubWebhookTargets(input.installationId)
  const matches = rows.filter((row) => githubWebhookTargetMatchesRepository({
    repositoryFullName: previousFullName,
    repositoryId,
    row,
  }))
  for (const row of matches) {
    const now = new Date()
    await db.update(ConnectorTargetTable).set({
      remoteId: repositoryFullName,
      targetConfigJson: {
        ...row.target.targetConfigJson,
        repositoryFullName,
      },
      updatedAt: now,
    }).where(eq(ConnectorTargetTable.id, row.target.id))
    await db.update(ConnectorInstanceTable).set({
      remoteId: repositoryFullName,
      updatedAt: now,
    }).where(eq(ConnectorInstanceTable.id, row.instance.id))

    await db.insert(ConnectorSyncEventTable).values({
      completedAt: now,
      connectorInstanceId: row.instance.id,
      connectorTargetId: row.target.id,
      connectorType: "github",
      eventType: "repository",
      externalEventRef: input.deliveryId,
      id: createDenTypeId("connectorSyncEvent"),
      organizationId: row.instance.organizationId,
      remoteId: repositoryFullName,
      sourceRevisionRef: null,
      startedAt: now,
      status: "completed",
      summaryJson: {
        action: "renamed",
        deliveryId: input.deliveryId,
        from: previousFullName,
        to: repositoryFullName,
        trigger: "webhook",
      },
    })

    const activeEvents = await db
      .select({ id: ConnectorSyncEventTable.id })
      .from(ConnectorSyncEventTable)
      .where(and(
        eq(ConnectorSyncEventTable.connectorTargetId, row.target.id),
        inArray(ConnectorSyncEventTable.status, ["queued", "running"]),
      ))
      .limit(1)
    if (!activeEvents[0]) {
      await db.insert(ConnectorSyncEventTable).values({
        connectorInstanceId: row.instance.id,
        connectorTargetId: row.target.id,
        connectorType: "github",
        eventType: "manual_resync",
        externalEventRef: input.deliveryId,
        id: createDenTypeId("connectorSyncEvent"),
        organizationId: row.instance.organizationId,
        remoteId: repositoryFullName,
        sourceRevisionRef: null,
        startedAt: now,
        status: "queued",
        summaryJson: { trigger: "webhook" },
      })
    }
  }
  return matches.length
}

function removedGithubRepositories(payload: Record<string, unknown>) {
  return Array.isArray(payload.repositories_removed)
    ? payload.repositories_removed.flatMap((entry) => {
        if (!isRecord(entry) || typeof entry.id !== "number" || typeof entry.full_name !== "string") return []
        const repositoryFullName = entry.full_name.trim()
        return repositoryFullName ? [{ repositoryFullName, repositoryId: entry.id }] : []
      })
    : []
}

async function handleGithubInstallationRepositoriesRemoved(input: {
  deliveryId: string
  installationId: number
  payload: Record<string, unknown>
}) {
  const removed = removedGithubRepositories(input.payload)
  if (removed.length === 0) return 0
  const rows = await listActiveGithubWebhookTargets(input.installationId)
  let matchedCount = 0
  for (const row of rows) {
    const repository = removed.find((entry) => githubWebhookTargetMatchesRepository({ ...entry, row }))
    if (!repository) continue
    matchedCount += 1
    const now = new Date()
    await db.update(ConnectorTargetTable).set({
      targetConfigJson: {
        ...row.target.targetConfigJson,
        status: "disabled",
      },
      updatedAt: now,
    }).where(eq(ConnectorTargetTable.id, row.target.id))
    await db.update(ConnectorInstanceTable).set({
      status: "disabled",
      updatedAt: now,
    }).where(eq(ConnectorInstanceTable.id, row.instance.id))
    await db.insert(ConnectorSyncEventTable).values({
      completedAt: now,
      connectorInstanceId: row.instance.id,
      connectorTargetId: row.target.id,
      connectorType: "github",
      eventType: "installation_repositories",
      externalEventRef: input.deliveryId,
      id: createDenTypeId("connectorSyncEvent"),
      organizationId: row.instance.organizationId,
      remoteId: row.target.remoteId,
      sourceRevisionRef: null,
      startedAt: now,
      status: "completed",
      summaryJson: {
        action: "removed",
        deliveryId: input.deliveryId,
        repositoryFullName: repository.repositoryFullName,
        repositoryId: repository.repositoryId,
        trigger: "webhook",
      },
    })
  }
  return matchedCount
}

export async function enqueueGithubWebhookSync(input: {
  deliveryId: string
  event: "installation" | "installation_repositories" | "push" | "repository"
  headSha?: string
  installationId?: number
  payload: Record<string, unknown>
  ref?: string
  repositoryFullName?: string
  repositoryId?: number
}) {
  if (!input.installationId) {
    return { accepted: false as const, reason: "missing installation id" }
  }

  const accounts = await db
    .select()
    .from(ConnectorAccountTable)
    .where(and(eq(ConnectorAccountTable.connectorType, "github"), eq(ConnectorAccountTable.remoteId, String(input.installationId))))

  if (input.event !== "push") {
    if (input.event === "installation") {
      const action = typeof input.payload.action === "string" ? input.payload.action : null
      if (action === "deleted") {
        for (const account of accounts) {
          await db.update(ConnectorAccountTable).set({ status: "disconnected", updatedAt: new Date() }).where(eq(ConnectorAccountTable.id, account.id))
        }
        return { accepted: true as const, queued: false as const }
      }
    }
    if (input.event === "repository" && input.payload.action === "renamed") {
      const matchedCount = await handleGithubRepositoryRenamed({
        deliveryId: input.deliveryId,
        installationId: input.installationId,
        payload: input.payload,
        repositoryFullName: input.repositoryFullName,
        repositoryId: input.repositoryId,
      })
      return matchedCount > 0
        ? { accepted: true as const, queued: true as const }
        : { accepted: false as const, reason: "event ignored" }
    }
    if (input.event === "installation_repositories" && input.payload.action === "removed") {
      const matchedCount = await handleGithubInstallationRepositoriesRemoved({
        deliveryId: input.deliveryId,
        installationId: input.installationId,
        payload: input.payload,
      })
      return matchedCount > 0
        ? { accepted: true as const, queued: false as const }
        : { accepted: false as const, reason: "event ignored" }
    }
    return { accepted: false as const, reason: "event ignored" }
  }

  if (!input.repositoryFullName || !input.ref || !input.headSha || !input.repositoryId) {
    return { accepted: false as const, reason: "missing push metadata" }
  }

  const instances = await db
    .select({ instance: ConnectorInstanceTable, target: ConnectorTargetTable })
    .from(ConnectorTargetTable)
    .innerJoin(ConnectorInstanceTable, eq(ConnectorTargetTable.connectorInstanceId, ConnectorInstanceTable.id))
    .innerJoin(ConnectorAccountTable, eq(ConnectorInstanceTable.connectorAccountId, ConnectorAccountTable.id))
    .where(and(
      eq(ConnectorTargetTable.connectorType, "github"),
      eq(ConnectorTargetTable.remoteId, input.repositoryFullName),
      eq(ConnectorTargetTable.organizationId, ConnectorInstanceTable.organizationId),
      eq(ConnectorAccountTable.organizationId, ConnectorInstanceTable.organizationId),
      eq(ConnectorAccountTable.connectorType, "github"),
      eq(ConnectorAccountTable.remoteId, String(input.installationId)),
      eq(ConnectorAccountTable.status, "active"),
      eq(ConnectorInstanceTable.status, "active"),
    ))

  const queuedIds: string[] = []
  for (const row of instances) {
    const targetConfig = row.target.targetConfigJson ?? {}
    const targetRef = typeof targetConfig.ref === "string" ? targetConfig.ref : null
    if (targetRef && targetRef !== input.ref) {
      continue
    }

    const existing = await db
      .select({ id: ConnectorSyncEventTable.id })
      .from(ConnectorSyncEventTable)
      .where(and(
        eq(ConnectorSyncEventTable.connectorTargetId, row.target.id),
        eq(ConnectorSyncEventTable.eventType, "push"),
        eq(ConnectorSyncEventTable.sourceRevisionRef, input.headSha),
      ))
      .limit(1)

    if (existing[0]) continue

    const id = createDenTypeId("connectorSyncEvent")
    await db.insert(ConnectorSyncEventTable).values({
      connectorInstanceId: row.instance.id,
      connectorTargetId: row.target.id,
      connectorType: "github",
      eventType: "push",
      externalEventRef: input.deliveryId,
      id,
      organizationId: row.instance.organizationId,
      remoteId: input.repositoryFullName,
      sourceRevisionRef: input.headSha,
      startedAt: new Date(),
      status: "queued",
      summaryJson: {
        deliveryId: input.deliveryId,
        headSha: input.headSha,
        installationId: input.installationId,
        ref: input.ref,
        repositoryFullName: input.repositoryFullName,
        repositoryId: input.repositoryId,
      },
    })
    queuedIds.push(id)
  }

  return queuedIds.length > 0
    ? { accepted: true as const, queued: true as const, syncEventIds: queuedIds }
    : { accepted: false as const, reason: "event ignored" }
}