Toot-Worker/src/mastodon.ts

import {
  actorDocument,
  announceActivity,
  cacheRemoteNote,
  createActivity,
  deleteActivity,
  followActivity,
  likeActivity,
  undoActivity,
  updateNoteActivity,
  updatePersonActivity
} from "./activitypub";
import { hashPassword, verifyPassword } from "./crypto";
import {
  addBookmark,
  addPin,
  countFollowers,
  countFollowing,
  countStatuses,
  deleteOAuthToken,
  findBookmark,
  findFavourite,
  findOutgoingFollow,
  findPin,
  findReblog,
  getActorByLocalId,
  getActorFromCache,
  getAdminUser,
  getAppByClientId,
  getOAuthToken,
  getCachedStatusByObjectId,
  getStatus,
  getUserById,
  getUserByIdOrUsername,
  getUserByUsername,
  insertOAuthToken,
  listCachedStatusAttachments,
  listMarkers,
  listProfileFields,
  recordNotification,
  removeBookmark,
  removePin,
  replaceProfileFields,
  saveMarker,
  setUserAvatarKey,
  setUserHeaderKey,
  takeOAuthCode,
  touchOAuthToken
} from "./db";
import {
  deliverToInboxes,
  gatherFollowerInboxes,
  objectAsJson,
  resolveDeliveryInboxes,
  resolveRemoteActor
} from "./federation";
import {
  bodyArray,
  bodyString,
  cors,
  HttpError,
  html,
  json,
  readBody
} from "./http";
import type { ParsedBody } from "./http";
import type {
  ActorCache,
  AccountList,
  CachedStatus,
  CachedStatusMention,
  CachedStatusTag,
  Follow,
  Json,
  Media,
  Mention,
  Marker,
  Notification,
  Poll,
  PollOption,
  PushSubscription,
  ScheduledStatus,
  Session,
  Status,
  User
} from "./types";
import {
  actorUrl,
  activityUrl,
  baseUrl,
  clampLimit,
  escapeHtml,
  hostFromBaseUrl,
  htmlContent,
  id,
  isLocalActor,
  mediaUrl,
  normalizeArray,
  objectUrl,
  profileUrl,
  safeFileName,
  statusUrl,
  tokenString
} from "./util";

const TOKEN_TTL_SECONDS = 60 * 60 * 24 * 90;
const MAX_STATUS_CHARS = 5000;
const MAX_MEDIA_ATTACHMENTS = 20;
const MAX_MEDIA_BYTES = 10 * 1024 * 1024;
const AUTH_RATE_LIMIT_WINDOW_SECONDS = 15 * 60;
const AUTH_RATE_LIMIT_LOCK_SECONDS = 15 * 60;
const AUTH_RATE_LIMIT_MAX_IP_FAILURES = 20;
const AUTH_RATE_LIMIT_MAX_USERNAME_FAILURES = 8;

const SUPPORTED_MIME = ["image/jpeg", "image/png", "image/gif", "image/webp"];
const VALID_STATUS_VISIBILITIES = new Set(["public", "unlisted", "private", "direct"]);
const MAX_POLL_OPTIONS = 4;
const MAX_POLL_OPTION_CHARS = 50;
const MIN_POLL_EXPIRATION_SECONDS = 300;
const MAX_POLL_EXPIRATION_SECONDS = 2629746;
const SCHEDULED_STATUS_MIN_DELAY_SECONDS = 300;
const ACTIVITY_JSON_ACCEPT = "application/activity+json, application/ld+json; profile=\"https://www.w3.org/ns/activitystreams\", application/json";

type StatusVisibility = "public" | "unlisted" | "private" | "direct";
type StatusViewer = {
  user: User | null;
  actor: string | null;
  followsByOwnerId: Map<string, boolean>;
  remoteFollowsByActorId: Map<string, boolean>;
};

type StatusCreateInput = {
  statusText: string;
  summary: string;
  sensitive: boolean;
  visibility: StatusVisibility;
  inReplyTo: string;
  language: string;
  mediaIds: string[];
  pollOptions: string[];
  pollExpiresIn: number | null;
  pollMultiple: boolean;
  pollHideTotals: boolean;
};

type StatusEditInput = {
  statusText: string;
  summary: string;
  sensitive: boolean;
  visibility: StatusVisibility;
  language: string;
  mediaIds: string[] | null;
};

function parseRedirectUris(value: string): string[] {
  return value.split(/\s+/).map((item) => item.trim()).filter(Boolean);
}

function selectRedirectUri(app: { redirect_uri: string }, requested: string | null | undefined): string | null {
  const allowed = parseRedirectUris(app.redirect_uri);
  const fallback = allowed[0] ?? "urn:ietf:wg:oauth:2.0:oob";
  const candidate = (requested ?? "").trim() || fallback;
  return allowed.includes(candidate) ? candidate : null;
}

export async function instance(env: Env): Promise<Response> {
  const userCount = await env.DB.prepare("SELECT COUNT(*) AS count FROM users").first<{ count: number }>();
  const statusCount = await env.DB.prepare("SELECT COUNT(*) AS count FROM statuses").first<{ count: number }>();
  const admin = await getAdminUser(env);
  return json({
    uri: hostFromBaseUrl(env),
    title: env.INSTANCE_NAME,
    short_description: "A single-user ActivityPub server on Cloudflare Workers.",
    description: "A single-user ActivityPub server on Cloudflare Workers.",
    email: "",
    version: "4.2.0-compatible (toot-worker)",
    urls: { streaming_api: `wss://${hostFromBaseUrl(env)}` },
    stats: { user_count: userCount?.count ?? 0, status_count: statusCount?.count ?? 0, domain_count: 0 },
    languages: ["en"],
    registrations: false,
    approval_required: false,
    invites_enabled: false,
    configuration: {
      statuses: { max_characters: MAX_STATUS_CHARS, max_media_attachments: MAX_MEDIA_ATTACHMENTS, characters_reserved_per_url: 23 },
      media_attachments: { supported_mime_types: SUPPORTED_MIME, image_size_limit: MAX_MEDIA_BYTES, image_matrix_limit: 16777216 },
      polls: { max_options: MAX_POLL_OPTIONS, max_characters_per_option: MAX_POLL_OPTION_CHARS, min_expiration: MIN_POLL_EXPIRATION_SECONDS, max_expiration: MAX_POLL_EXPIRATION_SECONDS }
    },
    contact_account: await accountJson(env, admin),
    rules: []
  });
}

export async function instanceV2(env: Env): Promise<Response> {
  const admin = await getAdminUser(env);
  return json({
    domain: hostFromBaseUrl(env),
    title: env.INSTANCE_NAME,
    version: "4.2.0-compatible (toot-worker)",
    source_url: "https://example.com",
    description: "A single-user ActivityPub server on Cloudflare Workers.",
    usage: { users: { active_month: 1 } },
    thumbnail: { url: `${baseUrl(env)}/header.png` },
    languages: ["en"],
    configuration: {
      urls: { streaming: `wss://${hostFromBaseUrl(env)}` },
      accounts: { max_featured_tags: 0 },
      statuses: { max_characters: MAX_STATUS_CHARS, max_media_attachments: MAX_MEDIA_ATTACHMENTS, characters_reserved_per_url: 23 },
      media_attachments: { supported_mime_types: SUPPORTED_MIME, image_size_limit: MAX_MEDIA_BYTES, image_matrix_limit: 16777216 },
      polls: { max_options: MAX_POLL_OPTIONS, max_characters_per_option: MAX_POLL_OPTION_CHARS, min_expiration: MIN_POLL_EXPIRATION_SECONDS, max_expiration: MAX_POLL_EXPIRATION_SECONDS }
    },
    registrations: { enabled: false, approval_required: false, message: null },
    contact: { email: "", account: await accountJson(env, admin) },
    rules: []
  });
}

export async function createApp(request: Request, env: Env): Promise<Response> {
  const body = await readBody(request);
  const now = new Date().toISOString();
  const redirectUri = bodyString(body, "redirect_uris", bodyString(body, "redirect_uri", "urn:ietf:wg:oauth:2.0:oob"));
  const app = {
    id: id(),
    client_id: tokenString(32),
    client_secret: tokenString(48),
    name: bodyString(body, "client_name", bodyString(body, "name", "Mastodon App")),
    redirect_uri: redirectUri,
    scopes: bodyString(body, "scopes", "read write follow"),
    website: bodyString(body, "website", "") || null,
    created_at: now
  };
  await env.DB.prepare(
    "INSERT INTO oauth_apps (id, client_id, client_secret, name, redirect_uri, scopes, website, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
  )
    .bind(app.id, app.client_id, app.client_secret, app.name, app.redirect_uri, app.scopes, app.website, app.created_at)
    .run();
  return json({
    id: app.id,
    name: app.name,
    website: app.website,
    redirect_uri: app.redirect_uri,
    client_id: app.client_id,
    client_secret: app.client_secret,
    vapid_key: ""
  });
}

export async function verifyAppCredentials(request: Request, env: Env): Promise<Response> {
  const auth = request.headers.get("authorization") ?? "";
  const token = auth.match(/^Bearer\s+(.+)$/i)?.[1];
  if (!token) throw new HttpError(401, "The access token is invalid");
  const session = await loadSession(env, token);
  if (!session) throw new HttpError(401, "The access token is invalid");
  requireScopes(session, ["read"]);
  const app = await env.DB.prepare("SELECT * FROM oauth_apps WHERE id = ?").bind(session.appId).first<{ name: string; website: string | null }>();
  return json({ name: app?.name ?? "Mastodon App", website: app?.website ?? null, vapid_key: "" });
}

export async function authorizePage(request: Request, env: Env): Promise<Response> {
  const url = new URL(request.url);
  const clientId = url.searchParams.get("client_id");
  const app = clientId ? await getAppByClientId(env, clientId) : null;
  if (!app) return html("Unknown OAuth application", 400);
  const redirectUri = selectRedirectUri(app, url.searchParams.get("redirect_uri"));
  if (!redirectUri) return html("Invalid redirect URI", 400);

  return html(`<!doctype html>
<html><head><meta name="viewport" content="width=device-width,initial-scale=1"><title>Authorize</title></head>
<body style="font-family:system-ui;margin:2rem;max-width:34rem">
<h1>${escapeHtml(env.INSTANCE_NAME)}</h1>
<p>Authorize ${escapeHtml(app.name)} to access your account.</p>
<form method="post" action="/oauth/authorize">
<input type="hidden" name="client_id" value="${escapeHtml(clientId!)}">
<input type="hidden" name="redirect_uri" value="${escapeHtml(redirectUri)}">
<input type="hidden" name="scope" value="${escapeHtml(url.searchParams.get("scope") ?? app.scopes)}">
<input type="hidden" name="state" value="${escapeHtml(url.searchParams.get("state") ?? "")}">
<label>Username <input name="username" autocomplete="username" value="${escapeHtml(env.ADMIN_USERNAME)}"></label><br><br>
<label>Password <input name="password" type="password" autocomplete="current-password"></label><br><br>
<button>Authorize</button>
</form></body></html>`);
}

export async function authorize(request: Request, env: Env): Promise<Response> {
  const body = await readBody(request);
  const app = await getAppByClientId(env, bodyString(body, "client_id"));
  if (!app) return json({ error: "invalid_client" }, 400);
  const redirectUri = selectRedirectUri(app, bodyString(body, "redirect_uri"));
  if (!redirectUri) return json({ error: "invalid_request" }, 400);

  const username = bodyString(body, "username");
  await assertAuthNotRateLimited(request, env, username);
  const user = await getUserByUsername(env, username);
  if (!user || !(await verifyPassword(bodyString(body, "password"), user.password_hash))) {
    await recordAuthFailure(request, env, username);
    return html("Invalid username or password", 401);
  }
  await clearAuthFailures(request, env, username);

  const code = tokenString(32);
  const scope = requestedScopesWithinApp(bodyString(body, "scope", app.scopes), app.scopes);
  await env.DB.prepare("INSERT INTO oauth_codes (code, app_id, user_id, redirect_uri, scopes, expires_at) VALUES (?, ?, ?, ?, ?, ?)")
    .bind(code, app.id, user.id, redirectUri, scope, Math.floor(Date.now() / 1000) + 600)
    .run();

  if (redirectUri === "urn:ietf:wg:oauth:2.0:oob") return html(`<p>Authorization code:</p><code>${code}</code>`);
  const url = new URL(redirectUri);
  url.searchParams.set("code", code);
  const state = bodyString(body, "state");
  if (state) url.searchParams.set("state", state);
  return Response.redirect(url.toString(), 302);
}

export async function token(request: Request, env: Env): Promise<Response> {
  const body = await readBody(request);
  const app = await getAppByClientId(env, bodyString(body, "client_id"));
  if (!app || app.client_secret !== bodyString(body, "client_secret")) return json({ error: "invalid_client" }, 401);

  const grantType = bodyString(body, "grant_type", "authorization_code");
  let userId = "";
  let scopes = app.scopes;

  if (grantType === "password") {
    const username = bodyString(body, "username");
    await assertAuthNotRateLimited(request, env, username);
    const user = await getUserByUsername(env, username);
    if (!user || !(await verifyPassword(bodyString(body, "password"), user.password_hash))) {
      await recordAuthFailure(request, env, username);
      return json({ error: "invalid_grant" }, 400);
    }
    await clearAuthFailures(request, env, username);
    userId = user.id;
    scopes = requestedScopesWithinApp(bodyString(body, "scope", app.scopes), app.scopes);
  } else if (grantType === "client_credentials") {
    scopes = requestedScopesWithinApp(bodyString(body, "scope", "read"), app.scopes);
  } else {
    const row = await takeOAuthCode(env, bodyString(body, "code"));
    if (!row || row.app_id !== app.id) return json({ error: "invalid_grant" }, 400);
    const redirectUri = bodyString(body, "redirect_uri", row.redirect_uri);
    if (redirectUri !== row.redirect_uri || !parseRedirectUris(app.redirect_uri).includes(row.redirect_uri)) {
      return json({ error: "invalid_grant" }, 400);
    }
    userId = row.user_id;
    scopes = row.scopes;
  }

  const accessToken = tokenString(48);
  await env.KV.put(`token:${accessToken}`, JSON.stringify({ userId, appId: app.id, scopes } satisfies Session), { expirationTtl: TOKEN_TTL_SECONDS });
  if (userId) await insertOAuthToken(env, accessToken, userId, app.id, scopes);
  return json({ access_token: accessToken, token_type: "Bearer", scope: scopes, created_at: Math.floor(Date.now() / 1000) });
}

export async function revoke(request: Request, env: Env): Promise<Response> {
  const body = await readBody(request);
  const tokenValue = bodyString(body, "token");
  if (tokenValue) {
    await env.KV.delete(`token:${tokenValue}`);
    await deleteOAuthToken(env, tokenValue);
  }
  return json({});
}

type AuthFailureRecord = {
  count: number;
  firstFailureAt: number;
  lockedUntil?: number;
};

async function assertAuthNotRateLimited(request: Request, env: Env, username: string): Promise<void> {
  const keys = authRateLimitKeys(request, username);
  const now = Math.floor(Date.now() / 1000);
  const records = await Promise.all(keys.map(({ key }) => env.KV.get<AuthFailureRecord>(key, "json")));
  const lockedUntil = records.reduce((latest, record) => Math.max(latest, record?.lockedUntil ?? 0), 0);
  if (lockedUntil > now) {
    const retryAfter = String(Math.max(1, lockedUntil - now));
    throw new HttpError(429, "rate_limited", { "retry-after": retryAfter });
  }
}

async function recordAuthFailure(request: Request, env: Env, username: string): Promise<void> {
  const now = Math.floor(Date.now() / 1000);
  await Promise.all(authRateLimitKeys(request, username).map(async ({ key, limit }) => {
    const existing = await env.KV.get<AuthFailureRecord>(key, "json");
    const inWindow = existing && now - existing.firstFailureAt < AUTH_RATE_LIMIT_WINDOW_SECONDS;
    const next: AuthFailureRecord = {
      count: inWindow ? existing.count + 1 : 1,
      firstFailureAt: inWindow ? existing.firstFailureAt : now
    };
    if (next.count >= limit) next.lockedUntil = now + AUTH_RATE_LIMIT_LOCK_SECONDS;
    const ttl = Math.max(AUTH_RATE_LIMIT_WINDOW_SECONDS, AUTH_RATE_LIMIT_LOCK_SECONDS);
    await env.KV.put(key, JSON.stringify(next), { expirationTtl: ttl });
  }));
}

async function clearAuthFailures(request: Request, env: Env, username: string): Promise<void> {
  await Promise.all(authRateLimitKeys(request, username).map(({ key }) => env.KV.delete(key)));
}

function authRateLimitKeys(request: Request, username: string): { key: string; limit: number }[] {
  const ip = request.headers.get("cf-connecting-ip") ?? request.headers.get("x-forwarded-for")?.split(",")[0]?.trim() ?? "unknown";
  const normalizedUsername = username.trim().toLowerCase() || "empty";
  return [
    { key: `auth_fail:ip:${ip}`, limit: AUTH_RATE_LIMIT_MAX_IP_FAILURES },
    { key: `auth_fail:user:${normalizedUsername}`, limit: AUTH_RATE_LIMIT_MAX_USERNAME_FAILURES }
  ];
}

export async function verifyCredentials(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  const account = await accountJson(env, user) as Record<string, unknown>;
  const fields = await listProfileFields(env, user.id);
  account.source = {
    privacy: "public",
    sensitive: false,
    language: "en",
    note: user.note,
    fields: fields.map((field) => ({ name: field.name, value: field.value }))
  };
  return json(account);
}

export async function updateCredentials(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  const contentType = (request.headers.get("content-type") ?? "").toLowerCase();
  let form: FormData | null = null;
  let body: ParsedBody = {};
  if (contentType.includes("multipart/form-data") || contentType.startsWith("application/x-www-form-urlencoded")) {
    form = await request.formData();
    body = parsedBodyFromForm(form);
  } else {
    body = await readBody(request);
  }

  const displayName = bodyString(body, "display_name", user.display_name);
  const note = bodyString(body, "note", user.note);
  await env.DB.prepare("UPDATE users SET display_name = ?, note = ? WHERE id = ?").bind(displayName, note, user.id).run();

  const password = bodyString(body, "password");
  if (password) {
    const hash = await hashPassword(password);
    await env.DB.prepare("UPDATE users SET password_hash = ? WHERE id = ?").bind(hash, user.id).run();
  }

  const fields = extractFieldsAttributes(body);
  if (fields !== null) {
    await replaceProfileFields(env, user.id, fields);
  }

  if (form) {
    const avatar = form.get("avatar");
    if (avatar instanceof File && avatar.size > 0) {
      if (avatar.size > MAX_MEDIA_BYTES) return json({ error: "avatar too large" }, 413);
      const key = await storeProfileAsset(env, user.id, "avatar", avatar);
      await setUserAvatarKey(env, user.id, key);
    }
    const header = form.get("header");
    if (header instanceof File && header.size > 0) {
      if (header.size > MAX_MEDIA_BYTES) return json({ error: "header too large" }, 413);
      const key = await storeProfileAsset(env, user.id, "header", header);
      await setUserHeaderKey(env, user.id, key);
    }
  }

  const refreshed = await getUserById(env, user.id);
  if (!refreshed) throw new HttpError(500, "user_missing");
  const followerInboxes = await gatherFollowerInboxes(env, user.id);
  if (followerInboxes.length > 0) {
    await deliverToInboxes(env, refreshed, followerInboxes, updatePersonActivity(env, refreshed, await actorDocument(env, refreshed)));
  }
  return json(await accountJson(env, refreshed));
}

async function storeProfileAsset(env: Env, userId: string, kind: "avatar" | "header", file: File): Promise<string> {
  if (!isSupportedImageMime(file.type)) throw new HttpError(415, "unsupported media type");
  const ext = mimeExtension(file.type) ?? safeFileName(file.name).split(".").pop() ?? "bin";
  const key = `${userId}/${kind}-${id()}.${ext}`;
  await env.MEDIA.put(key, file.stream(), { httpMetadata: { contentType: file.type || "application/octet-stream" } });
  return key;
}

function isSupportedImageMime(mime: string): boolean {
  return SUPPORTED_MIME.includes(mime) || mime === "image/jpg";
}

function mimeExtension(mime: string): string | null {
  switch (mime) {
    case "image/jpeg": case "image/jpg": return "jpg";
    case "image/png": return "png";
    case "image/gif": return "gif";
    case "image/webp": return "webp";
    case "image/avif": return "avif";
    default: return null;
  }
}

function parsedBodyFromForm(form: FormData): ParsedBody {
  const data: ParsedBody = {};
  for (const [key, value] of form) {
    const cleanKey = key.endsWith("[]") ? key.slice(0, -2) : key;
    const normalized = value instanceof File ? value : String(value);
    const existing = data[cleanKey];
    if (existing === undefined) {
      data[cleanKey] = key.endsWith("[]") ? [normalized as string] : normalized;
    } else if (Array.isArray(existing)) {
      existing.push(normalized as string);
    } else {
      data[cleanKey] = [existing as string, normalized as string];
    }
  }
  return data;
}

function extractFieldsAttributes(body: ParsedBody): { name: string; value: string }[] | null {
  const flat = body["fields_attributes"];
  if (Array.isArray(flat)) {
    return flat.map((entry) => {
      if (typeof entry === "string") {
        try {
          const parsed = JSON.parse(entry) as { name?: string; value?: string };
          return { name: String(parsed.name ?? ""), value: String(parsed.value ?? "") };
        } catch {
          return { name: entry, value: "" };
        }
      }
      const obj = entry as unknown as { name?: string; value?: string };
      return { name: String(obj.name ?? ""), value: String(obj.value ?? "") };
    });
  }
  const indexed: { name: string; value: string }[] = [];
  let touched = false;
  for (const key of Object.keys(body)) {
    const match = key.match(/^fields_attributes\[(\d+)\]\[(name|value)\]$/);
    if (!match) continue;
    touched = true;
    const idx = Number(match[1]);
    indexed[idx] = indexed[idx] ?? { name: "", value: "" };
    const v = body[key];
    indexed[idx][match[2] as "name" | "value"] = typeof v === "string" ? v : Array.isArray(v) ? String(v[0] ?? "") : "";
  }
  if (!touched) return null;
  return indexed.filter(Boolean);
}

export async function getAccount(env: Env, accountId: string): Promise<Response> {
  const local = await getUserByIdOrUsername(env, accountId);
  if (local) return json(await accountJson(env, local));
  const byLocalId = await getActorByLocalId(env, accountId);
  if (byLocalId) return json(remoteAccountJson(byLocalId));
  if (accountId.startsWith("http://") || accountId.startsWith("https://")) {
    const cache = await resolveRemoteActor(env, accountId);
    if (cache) return json(remoteAccountJson(cache));
  }
  return json({ error: "Record not found" }, 404);
}

export async function lookupAccount(request: Request, env: Env): Promise<Response> {
  const acct = (new URL(request.url).searchParams.get("acct") ?? "").trim();
  if (!acct) return json({ error: "acct parameter is required" }, 422);
  const resolved = await resolveAcct(env, acct);
  if (!resolved) return json({ error: "Record not found" }, 404);

  if (resolved.actorId.startsWith(baseUrl(env))) {
    const match = resolved.actorId.match(/\/users\/([^/?#]+)$/);
    const user = match ? await getUserByUsername(env, match[1]) : null;
    if (!user) return json({ error: "Record not found" }, 404);
    return json(await accountJson(env, user));
  }
  const cache = await resolveRemoteActor(env, resolved.actorId);
  if (!cache) return json({ error: "Record not found" }, 404);
  return json(remoteAccountJson(cache));
}

export async function accountStatuses(request: Request, env: Env, accountId: string): Promise<Response> {
  const url = new URL(request.url);
  const limit = clampLimit(url.searchParams.get("limit"), 20, 40);

  const user = await getUserByIdOrUsername(env, accountId);
  if (user) {
    const excludeReplies = url.searchParams.get("exclude_replies") === "true";
    const where: string[] = ["user_id = ?"];
    const binds: unknown[] = [user.id];
    const viewer = await loadStatusViewer(request, env);
    const visibilityClause = await visibleStatusWhereForOwner(env, user.id, viewer);
    if (visibilityClause) where.push(visibilityClause);
    if (excludeReplies) where.push("in_reply_to_id IS NULL");
    pagedAppend(where, binds, url);
    const sql = `SELECT * FROM statuses WHERE ${where.join(" AND ")} ORDER BY created_at DESC LIMIT ?`;
    binds.push(limit);
    const rows = await env.DB.prepare(sql).bind(...binds).all<Status>();
    const items = await serializeStatuses(env, rows.results, request, new Map([[user.id, user]]));
    return withPagination(json(items), request, rows.results.map((row) => row.id));
  }

  const remote = await getActorByLocalId(env, accountId)
    ?? (accountId.startsWith("http://") || accountId.startsWith("https://") ? await resolveRemoteActor(env, accountId) : null);
  if (remote) {
    const viewer = await loadStatusViewer(request, env);
    const fetchLimit = Math.min(limit * 4, 160);
    const rows = await env.DB.prepare(
      "SELECT * FROM cached_statuses WHERE actor = ? ORDER BY published DESC LIMIT ?"
    ).bind(remote.id, fetchLimit).all<CachedStatus>();
    const visibleRows = await filterCachedStatusesForViewer(env, rows.results, viewer);
    const items = await Promise.all(visibleRows.slice(0, limit).map((row) => cachedStatusToMastodon(env, row)));
    return json(items);
  }

  return json({ error: "Record not found" }, 404);
}

export async function accountFollowers(request: Request, env: Env, accountId: string): Promise<Response> {
  const user = await getUserByIdOrUsername(env, accountId);
  if (!user) return remoteAccountListFallback(env, accountId);

  const url = new URL(request.url);
  const limit = clampLimit(url.searchParams.get("limit"), 20, 80);
  const where: string[] = ["local_user_id = ?", "accepted = 1"];
  const binds: unknown[] = [user.id];
  pagedAppendForTable(where, binds, url, "follows");
  const rows = await env.DB.prepare(
    `SELECT * FROM follows WHERE ${where.join(" AND ")} ORDER BY created_at DESC LIMIT ?`
  ).bind(...binds, limit).all<Follow>();

  const accounts = await actorIdsToAccounts(env, rows.results.map((row) => row.follower_actor));
  return withPagination(json(accounts), request, rows.results.map((row) => row.id));
}

export async function accountFollowing(request: Request, env: Env, accountId: string): Promise<Response> {
  const user = await getUserByIdOrUsername(env, accountId);
  if (!user) return remoteAccountListFallback(env, accountId);

  const url = new URL(request.url);
  const limit = clampLimit(url.searchParams.get("limit"), 20, 80);
  const where: string[] = ["local_user_id = ?", "accepted = 1"];
  const binds: unknown[] = [user.id];
  pagedAppendForTable(where, binds, url, "outgoing_follows");
  const rows = await env.DB.prepare(
    `SELECT * FROM outgoing_follows WHERE ${where.join(" AND ")} ORDER BY created_at DESC LIMIT ?`
  ).bind(...binds, limit).all<{ id: string; target_actor: string }>();

  const accounts = await actorIdsToAccounts(env, rows.results.map((row) => row.target_actor));
  return withPagination(json(accounts), request, rows.results.map((row) => row.id));
}

async function remoteAccountListFallback(env: Env, accountId: string): Promise<Response> {
  const remote = await getActorByLocalId(env, accountId)
    ?? (accountId.startsWith("http://") || accountId.startsWith("https://") ? await resolveRemoteActor(env, accountId) : null);
  if (remote) return json([]);
  return json({ error: "Record not found" }, 404);
}

async function actorIdsToAccounts(env: Env, actorIds: string[]): Promise<Record<string, unknown>[]> {
  const accounts = await Promise.all(actorIds.map((actorId) => accountFromActorId(env, actorId)));
  return accounts.filter((account): account is Record<string, unknown> => Boolean(account));
}

async function accountFromActorId(env: Env, actorId: string): Promise<Record<string, unknown> | null> {
  if (actorId.startsWith(baseUrl(env))) {
    const match = actorId.match(/\/users\/([^/?#]+)$/);
    const user = match ? await getUserByUsername(env, match[1]) : null;
    return user ? accountJson(env, user) : null;
  }
  const cache = await resolveRemoteActor(env, actorId) ?? await getActorFromCache(env, actorId);
  return cache ? remoteAccountJson(cache) : null;
}

export async function createStatus(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  const body = await readBody(request);
  const input = parseStatusCreateInput(body);
  const scheduledAt = bodyString(body, "scheduled_at");
  if (scheduledAt) return scheduleStatus(env, user, input, scheduledAt);

  const status = await publishStatus(env, user, input);
  return json(await statusJson(env, status, user, request));
}

async function publishStatus(env: Env, user: User, input: StatusCreateInput): Promise<Status> {
  const now = new Date().toISOString();
  const statusId = id();
  const objectId = objectUrl(env, statusId);
  const activityId = activityUrl(env, statusId);

  const mentionsAcct = extractMentions(input.statusText);
  const hashtags = extractHashtags(input.statusText);

  const resolvedMentions: { acct: string; actorId: string; url: string }[] = [];
  for (const acct of mentionsAcct) {
    const resolved = await resolveAcct(env, acct);
    if (resolved) resolvedMentions.push(resolved);
  }

  const renderedContent = htmlContent(input.statusText, resolvedMentions.map(({ acct, url }) => ({ acct, url })), hashtags);

  await env.DB.prepare(
    "INSERT INTO statuses (id, user_id, content, summary, sensitive, language, visibility, in_reply_to_id, activity_id, object_id, created_at, url, source_text, edited_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL)"
  )
    .bind(
      statusId,
      user.id,
      renderedContent,
      input.summary,
      input.sensitive ? 1 : 0,
      input.language,
      input.visibility,
      input.inReplyTo || null,
      activityId,
      objectId,
      now,
      statusUrl(env, user, statusId),
      input.statusText
    )
    .run();

  for (const mediaId of input.mediaIds) {
    await env.DB.prepare("UPDATE media SET status_id = ? WHERE id = ? AND user_id = ?").bind(statusId, mediaId, user.id).run();
  }

  if (input.pollOptions.length > 0) {
    const pollId = id();
    const expiresAt = input.pollExpiresIn ? new Date(Date.now() + input.pollExpiresIn * 1000).toISOString() : null;
    await env.DB.prepare(
      "INSERT INTO polls (id, status_id, user_id, expires_at, multiple, hide_totals, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)"
    ).bind(pollId, statusId, user.id, expiresAt, input.pollMultiple ? 1 : 0, input.pollHideTotals ? 1 : 0, now).run();
    for (let i = 0; i < input.pollOptions.length; i++) {
      await env.DB.prepare("INSERT INTO poll_options (poll_id, position, title) VALUES (?, ?, ?)")
        .bind(pollId, i, input.pollOptions[i]).run();
    }
  }

  for (const mention of resolvedMentions) {
    await env.DB.prepare("INSERT OR IGNORE INTO mentions (status_id, actor, acct, url) VALUES (?, ?, ?, ?)")
      .bind(statusId, mention.actorId, mention.acct, mention.url).run();
  }

  for (const tag of hashtags) {
    await env.DB.prepare("INSERT OR IGNORE INTO hashtags (status_id, tag) VALUES (?, ?)").bind(statusId, tag).run();
  }

  let replyParent: Status | null = null;
  if (input.inReplyTo) {
    replyParent = await getStatus(env, input.inReplyTo);
    if (replyParent) {
      const parentUser = await getUserById(env, replyParent.user_id);
      if (parentUser && parentUser.id !== user.id) {
        await recordNotification(env, parentUser.id, "mention", actorUrl(env, user), statusId);
      }
    }
  }

  for (const mention of resolvedMentions) {
    if (mention.actorId.startsWith(baseUrl(env))) {
      const mentionedUser = await getUserByUsername(env, mention.acct.split("@")[0]);
      if (mentionedUser && mentionedUser.id !== user.id) {
        await recordNotification(env, mentionedUser.id, "mention", actorUrl(env, user), statusId);
      }
    }
  }

  const status = await getStatus(env, statusId);
  if (!status) throw new HttpError(500, "status_not_found");

  if (input.visibility === "public" || input.visibility === "unlisted" || input.visibility === "private") {
    const inboxes = new Set<string>(await gatherFollowerInboxes(env, user.id));
    for (const mention of resolvedMentions) {
      if (!mention.actorId.startsWith(baseUrl(env))) {
        const cache = await resolveRemoteActor(env, mention.actorId);
        if (cache) inboxes.add(cache.shared_inbox ?? cache.inbox);
      }
    }
    const mentionActors = resolvedMentions.map((m) => m.actorId);
    const to = input.visibility === "public"
      ? ["https://www.w3.org/ns/activitystreams#Public"]
      : input.visibility === "unlisted"
        ? [`${actorUrl(env, user)}/followers`]
        : [`${actorUrl(env, user)}/followers`, ...mentionActors];
    const cc = input.visibility === "public"
      ? [`${actorUrl(env, user)}/followers`, ...mentionActors]
      : input.visibility === "unlisted"
        ? ["https://www.w3.org/ns/activitystreams#Public", ...mentionActors]
        : [];
    const activity = await createActivity(env, user, status, { to, cc });
    await deliverToInboxes(env, user, inboxes, activity);
  } else if (input.visibility === "direct") {
    const inboxes = new Set<string>();
    for (const mention of resolvedMentions) {
      if (!mention.actorId.startsWith(baseUrl(env))) {
        const cache = await resolveRemoteActor(env, mention.actorId);
        if (cache) inboxes.add(cache.shared_inbox ?? cache.inbox);
      }
    }
    const activity = await createActivity(env, user, status, { to: resolvedMentions.map((m) => m.actorId), cc: [] });
    await deliverToInboxes(env, user, inboxes, activity);
  }

  return status;
}

function parseStatusCreateInput(body: ParsedBody): StatusCreateInput {
  const statusText = bodyString(body, "status").trim();
  if (!statusText) throw new HttpError(422, "status can't be blank");
  if (statusText.length > MAX_STATUS_CHARS) throw new HttpError(422, "status too long");

  const visibility = bodyString(body, "visibility", "public");
  if (!isStatusVisibility(visibility)) throw new HttpError(422, "invalid_visibility");

  const pollOptions = bodyArray(body, "poll[options]").map((option) => option.trim()).filter(Boolean);
  if (pollOptions.length === 1) throw new HttpError(422, "poll needs at least two options");
  if (pollOptions.length > MAX_POLL_OPTIONS) throw new HttpError(422, "too_many_poll_options");
  if (pollOptions.some((option) => option.length > MAX_POLL_OPTION_CHARS)) throw new HttpError(422, "poll_option_too_long");

  const pollExpiresIn = pollOptions.length > 0 ? parsePollExpiresIn(bodyString(body, "poll[expires_in]", String(MIN_POLL_EXPIRATION_SECONDS))) : null;
  const mediaIds = bodyArray(body, "media_ids");
  if (mediaIds.length > MAX_MEDIA_ATTACHMENTS) throw new HttpError(422, "too_many_media_attachments");

  return {
    statusText,
    summary: bodyString(body, "spoiler_text"),
    sensitive: bodyString(body, "sensitive") === "true",
    visibility,
    inReplyTo: bodyString(body, "in_reply_to_id"),
    language: bodyString(body, "language", "en"),
    mediaIds,
    pollOptions,
    pollExpiresIn,
    pollMultiple: bodyString(body, "poll[multiple]") === "true",
    pollHideTotals: bodyString(body, "poll[hide_totals]") === "true"
  };
}

function parseStatusEditInput(body: ParsedBody, existing: Status): StatusEditInput {
  const existingText = statusSourceText(existing);
  const statusText = Object.prototype.hasOwnProperty.call(body, "status")
    ? bodyString(body, "status").trim()
    : existingText;
  if (!statusText) throw new HttpError(422, "status can't be blank");
  if (statusText.length > MAX_STATUS_CHARS) throw new HttpError(422, "status too long");

  const visibility = bodyString(body, "visibility", existing.visibility);
  if (!isStatusVisibility(visibility)) throw new HttpError(422, "invalid_visibility");
  const mediaIds = Object.prototype.hasOwnProperty.call(body, "media_ids") ? bodyArray(body, "media_ids") : null;
  if (mediaIds && mediaIds.length > MAX_MEDIA_ATTACHMENTS) throw new HttpError(422, "too_many_media_attachments");

  return {
    statusText,
    summary: bodyString(body, "spoiler_text", existing.summary),
    sensitive: Object.prototype.hasOwnProperty.call(body, "sensitive")
      ? bodyString(body, "sensitive") === "true"
      : Boolean(existing.sensitive),
    visibility,
    language: bodyString(body, "language", existing.language || "en"),
    mediaIds
  };
}

function parsePollExpiresIn(value: string): number {
  const seconds = Number(value);
  if (!Number.isFinite(seconds)) throw new HttpError(422, "invalid_poll_expiration");
  const wholeSeconds = Math.floor(seconds);
  if (wholeSeconds < MIN_POLL_EXPIRATION_SECONDS) throw new HttpError(422, "poll_expiration_too_short");
  if (wholeSeconds > MAX_POLL_EXPIRATION_SECONDS) throw new HttpError(422, "poll_expiration_too_long");
  return wholeSeconds;
}

async function scheduleStatus(env: Env, user: User, input: StatusCreateInput, scheduledAtValue: string): Promise<Response> {
  const scheduledAt = new Date(scheduledAtValue);
  if (!Number.isFinite(scheduledAt.getTime())) return json({ error: "invalid_scheduled_at" }, 422);
  if (scheduledAt.getTime() < Date.now() + SCHEDULED_STATUS_MIN_DELAY_SECONDS * 1000) {
    return json({ error: "scheduled_at_too_soon" }, 422);
  }
  const now = new Date().toISOString();
  const row: ScheduledStatus = {
    id: id(),
    user_id: user.id,
    params_json: JSON.stringify(input),
    media_ids_json: JSON.stringify(input.mediaIds),
    scheduled_at: scheduledAt.toISOString(),
    created_at: now
  };
  await env.DB.prepare(
    "INSERT INTO scheduled_statuses (id, user_id, params_json, media_ids_json, scheduled_at, created_at) VALUES (?, ?, ?, ?, ?, ?)"
  ).bind(row.id, row.user_id, row.params_json, row.media_ids_json, row.scheduled_at, row.created_at).run();
  return json(await scheduledStatusJson(env, row));
}

export async function listScheduledStatuses(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  const url = new URL(request.url);
  const limit = clampLimit(url.searchParams.get("limit"), 20, 80);
  const rows = await env.DB.prepare(
    "SELECT * FROM scheduled_statuses WHERE user_id = ? ORDER BY scheduled_at ASC LIMIT ?"
  ).bind(user.id, limit).all<ScheduledStatus>();
  return json(await Promise.all(rows.results.map((row) => scheduledStatusJson(env, row))));
}

export async function getScheduledStatus(request: Request, env: Env, scheduledId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const row = await env.DB.prepare("SELECT * FROM scheduled_statuses WHERE id = ? AND user_id = ?").bind(scheduledId, user.id).first<ScheduledStatus>();
  if (!row) return json({ error: "Record not found" }, 404);
  return json(await scheduledStatusJson(env, row));
}

export async function updateScheduledStatus(request: Request, env: Env, scheduledId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const row = await env.DB.prepare("SELECT * FROM scheduled_statuses WHERE id = ? AND user_id = ?").bind(scheduledId, user.id).first<ScheduledStatus>();
  if (!row) return json({ error: "Record not found" }, 404);
  const body = await readBody(request);
  const scheduledAtValue = bodyString(body, "scheduled_at");
  if (!scheduledAtValue) return json({ error: "scheduled_at is required" }, 422);
  const scheduledAt = new Date(scheduledAtValue);
  if (!Number.isFinite(scheduledAt.getTime())) return json({ error: "invalid_scheduled_at" }, 422);
  if (scheduledAt.getTime() < Date.now() + SCHEDULED_STATUS_MIN_DELAY_SECONDS * 1000) {
    return json({ error: "scheduled_at_too_soon" }, 422);
  }
  await env.DB.prepare("UPDATE scheduled_statuses SET scheduled_at = ? WHERE id = ? AND user_id = ?")
    .bind(scheduledAt.toISOString(), scheduledId, user.id).run();
  const updated = await env.DB.prepare("SELECT * FROM scheduled_statuses WHERE id = ?").bind(scheduledId).first<ScheduledStatus>();
  return json(await scheduledStatusJson(env, updated!));
}

export async function deleteScheduledStatus(request: Request, env: Env, scheduledId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const row = await env.DB.prepare("SELECT * FROM scheduled_statuses WHERE id = ? AND user_id = ?").bind(scheduledId, user.id).first<ScheduledStatus>();
  if (!row) return json({ error: "Record not found" }, 404);
  await env.DB.prepare("DELETE FROM scheduled_statuses WHERE id = ? AND user_id = ?").bind(scheduledId, user.id).run();
  return json({});
}

export async function publishDueScheduledStatuses(env: Env): Promise<void> {
  const rows = await env.DB.prepare(
    "SELECT * FROM scheduled_statuses WHERE scheduled_at <= ? ORDER BY scheduled_at ASC LIMIT 10"
  ).bind(new Date().toISOString()).all<ScheduledStatus>();
  for (const row of rows.results) {
    const user = await getUserById(env, row.user_id);
    if (!user) {
      await env.DB.prepare("DELETE FROM scheduled_statuses WHERE id = ?").bind(row.id).run();
      continue;
    }
    try {
      await publishStatus(env, user, parseScheduledStatusInput(row.params_json));
      await env.DB.prepare("DELETE FROM scheduled_statuses WHERE id = ?").bind(row.id).run();
    } catch (error) {
      console.warn("scheduled-status-publish-failed", row.id, String(error));
    }
  }
}

async function scheduledStatusJson(env: Env, row: ScheduledStatus): Promise<Record<string, unknown>> {
  const input = parseScheduledStatusInput(row.params_json);
  const mediaIds = parseCachedJson<string>(row.media_ids_json);
  const media = mediaIds.length > 0
    ? (await env.DB.prepare(`SELECT * FROM media WHERE id IN (${placeholders(mediaIds.length)})`).bind(...mediaIds).all<Media>()).results
    : [];
  return {
    id: row.id,
    scheduled_at: row.scheduled_at,
    params: {
      text: input.statusText,
      media_ids: input.mediaIds,
      sensitive: input.sensitive,
      spoiler_text: input.summary,
      visibility: input.visibility,
      scheduled_at: row.scheduled_at,
      poll: input.pollOptions.length > 0 ? {
        options: input.pollOptions,
        expires_in: input.pollExpiresIn,
        multiple: input.pollMultiple,
        hide_totals: input.pollHideTotals
      } : null,
      idempotency: null,
      in_reply_to_id: input.inReplyTo || null,
      application_id: null
    },
    media_attachments: media.map((item) => mediaJson(env, item))
  };
}

function parseScheduledStatusInput(value: string): StatusCreateInput {
  const parsed = JSON.parse(value) as StatusCreateInput;
  if (!isStatusVisibility(parsed.visibility)) throw new Error("invalid_scheduled_visibility");
  return {
    statusText: String(parsed.statusText ?? ""),
    summary: String(parsed.summary ?? ""),
    sensitive: Boolean(parsed.sensitive),
    visibility: parsed.visibility,
    inReplyTo: String(parsed.inReplyTo ?? ""),
    language: String(parsed.language ?? "en"),
    mediaIds: Array.isArray(parsed.mediaIds) ? parsed.mediaIds.map(String) : [],
    pollOptions: Array.isArray(parsed.pollOptions) ? parsed.pollOptions.map(String) : [],
    pollExpiresIn: typeof parsed.pollExpiresIn === "number" ? parsed.pollExpiresIn : null,
    pollMultiple: Boolean(parsed.pollMultiple),
    pollHideTotals: Boolean(parsed.pollHideTotals)
  };
}

export async function getStatusEndpoint(request: Request, env: Env, statusId: string): Promise<Response> {
  const status = await getStatus(env, statusId);
  if (!status) return json({ error: "Record not found" }, 404);
  const viewer = await loadStatusViewer(request, env);
  if (!await canViewerViewStatus(env, status, viewer)) return json({ error: "Record not found" }, 404);
  const user = await getUserById(env, status.user_id);
  if (!user) return json({ error: "Record not found" }, 404);
  return json(await statusJson(env, status, user, request));
}

export async function getStatusSource(request: Request, env: Env, statusId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const status = await getStatus(env, statusId);
  if (!status || status.user_id !== user.id) return json({ error: "Record not found" }, 404);
  return json({
    id: status.id,
    text: statusSourceText(status),
    spoiler_text: status.summary
  });
}

export async function updateStatusEndpoint(request: Request, env: Env, statusId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const status = await getStatus(env, statusId);
  if (!status || status.user_id !== user.id) return json({ error: "Record not found" }, 404);

  const body = await readBody(request);
  const input = parseStatusEditInput(body, status);
  const previousMentions = await listMentionsForStatus(env, status.id);
  const mentionsAcct = extractMentions(input.statusText);
  const hashtags = extractHashtags(input.statusText);

  const resolvedMentions: { acct: string; actorId: string; url: string }[] = [];
  for (const acct of mentionsAcct) {
    const resolved = await resolveAcct(env, acct);
    if (resolved) resolvedMentions.push(resolved);
  }
  const renderedContent = htmlContent(input.statusText, resolvedMentions.map(({ acct, url }) => ({ acct, url })), hashtags);
  const editedAt = new Date().toISOString();

  await env.DB.prepare(
    `UPDATE statuses
     SET content = ?, summary = ?, sensitive = ?, language = ?, visibility = ?, url = ?, source_text = ?, edited_at = ?
     WHERE id = ? AND user_id = ?`
  ).bind(
    renderedContent,
    input.summary,
    input.sensitive ? 1 : 0,
    input.language,
    input.visibility,
    statusUrl(env, user, status.id),
    input.statusText,
    editedAt,
    status.id,
    user.id
  ).run();

  if (input.mediaIds !== null) {
    await env.DB.prepare("UPDATE media SET status_id = NULL WHERE status_id = ? AND user_id = ?").bind(status.id, user.id).run();
    for (const mediaId of input.mediaIds) {
      await env.DB.prepare("UPDATE media SET status_id = ? WHERE id = ? AND user_id = ?").bind(status.id, mediaId, user.id).run();
    }
  }

  await env.DB.prepare("DELETE FROM mentions WHERE status_id = ?").bind(status.id).run();
  for (const mention of resolvedMentions) {
    await env.DB.prepare("INSERT OR IGNORE INTO mentions (status_id, actor, acct, url) VALUES (?, ?, ?, ?)")
      .bind(status.id, mention.actorId, mention.acct, mention.url).run();
  }
  await env.DB.prepare("DELETE FROM hashtags WHERE status_id = ?").bind(status.id).run();
  for (const tag of hashtags) {
    await env.DB.prepare("INSERT OR IGNORE INTO hashtags (status_id, tag) VALUES (?, ?)").bind(status.id, tag).run();
  }

  const updated = await getStatus(env, status.id);
  if (!updated) throw new HttpError(500, "status_not_found");

  if (updated.visibility === "public" || updated.visibility === "unlisted" || updated.visibility === "private" || updated.visibility === "direct") {
    const inboxes = new Set<string>();
    if (updated.visibility !== "direct") {
      for (const inbox of await gatherFollowerInboxes(env, user.id)) inboxes.add(inbox);
    }
    const remoteActors = new Set<string>([
      ...previousMentions.map((mention) => mention.actor),
      ...resolvedMentions.map((mention) => mention.actorId)
    ].filter((actorId) => !actorId.startsWith(baseUrl(env))));
    for (const actorId of remoteActors) {
      const cache = await resolveRemoteActor(env, actorId);
      if (cache) inboxes.add(cache.shared_inbox ?? cache.inbox);
    }

    const mentionActors = resolvedMentions.map((mention) => mention.actorId);
    const to = updated.visibility === "public"
      ? ["https://www.w3.org/ns/activitystreams#Public"]
      : updated.visibility === "unlisted"
        ? [`${actorUrl(env, user)}/followers`]
        : updated.visibility === "private"
          ? [`${actorUrl(env, user)}/followers`, ...mentionActors]
          : mentionActors;
    const cc = updated.visibility === "public"
      ? [`${actorUrl(env, user)}/followers`, ...mentionActors]
      : updated.visibility === "unlisted"
        ? ["https://www.w3.org/ns/activitystreams#Public", ...mentionActors]
        : [];
    await deliverToInboxes(env, user, inboxes, await updateNoteActivity(env, user, updated, { to, cc }));
  }

  return json(await statusJson(env, updated, user, request));
}

export async function getPoll(request: Request, env: Env, pollId: string): Promise<Response> {
  const poll = await env.DB.prepare("SELECT * FROM polls WHERE id = ?").bind(pollId).first<Poll>();
  if (!poll) return json({ error: "Record not found" }, 404);
  const status = await getStatus(env, poll.status_id);
  if (!status) return json({ error: "Record not found" }, 404);
  const viewer = await loadStatusViewer(request, env);
  if (!await canViewerViewStatus(env, status, viewer)) return json({ error: "Record not found" }, 404);
  return json(await pollJson(env, poll, viewer.actor));
}

export async function votePoll(request: Request, env: Env, pollId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const poll = await env.DB.prepare("SELECT * FROM polls WHERE id = ?").bind(pollId).first<Poll>();
  if (!poll) return json({ error: "Record not found" }, 404);
  const status = await getStatus(env, poll.status_id);
  if (!status) return json({ error: "Record not found" }, 404);
  const viewer = statusViewerForUser(env, user);
  if (!await canViewerViewStatus(env, status, viewer)) return json({ error: "Record not found" }, 404);
  if (poll.expires_at && Date.parse(poll.expires_at) <= Date.now()) return json({ error: "poll_expired" }, 422);

  const body = await readBody(request);
  const choices = uniqueNumbers(bodyArray(body, "choices").map((choice) => Number(choice)));
  if (choices.length === 0) return json({ error: "choices can't be blank" }, 422);
  if (!poll.multiple && choices.length > 1) return json({ error: "poll_is_single_choice" }, 422);
  const options = await env.DB.prepare("SELECT * FROM poll_options WHERE poll_id = ?").bind(poll.id).all<PollOption>();
  const validPositions = new Set(options.results.map((option) => option.position));
  if (choices.some((choice) => !validPositions.has(choice))) return json({ error: "invalid_choice" }, 422);

  const actor = actorUrl(env, user);
  const existing = await env.DB.prepare("SELECT 1 AS hit FROM poll_votes WHERE poll_id = ? AND voter_actor = ? LIMIT 1").bind(poll.id, actor).first<{ hit: number }>();
  if (existing) return json({ error: "already_voted" }, 422);
  const now = new Date().toISOString();
  for (const choice of choices) {
    await env.DB.prepare("INSERT INTO poll_votes (poll_id, position, voter_actor, created_at) VALUES (?, ?, ?, ?)")
      .bind(poll.id, choice, actor, now).run();
  }
  return json(await pollJson(env, poll, actor));
}

export async function deleteStatusEndpoint(request: Request, env: Env, statusId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const status = await getStatus(env, statusId);
  if (!status || status.user_id !== user.id) return json({ error: "Record not found" }, 404);

  const serialized = await statusJson(env, status, user, request);

  const inboxes = new Set<string>();
  if (status.visibility !== "direct") {
    for (const inbox of await gatherFollowerInboxes(env, user.id)) inboxes.add(inbox);
  }
  const mentions = await listMentionsForStatus(env, status.id);
  for (const mention of mentions) {
    if (!mention.actor.startsWith(baseUrl(env))) {
      const cache = await resolveRemoteActor(env, mention.actor);
      if (cache) inboxes.add(cache.shared_inbox ?? cache.inbox);
    }
  }
  const media = await env.DB.prepare("SELECT r2_key FROM media WHERE status_id = ?").bind(status.id).all<{ r2_key: string }>();
  const deleteResults = await Promise.allSettled(media.results.map((item) => env.MEDIA.delete(item.r2_key)));
  for (const result of deleteResults) {
    if (result.status === "rejected") console.warn("media-delete-failed", status.id, String(result.reason));
  }

  await env.DB.prepare(
    "INSERT INTO deleted_statuses (id, user_id, object_id, url, deleted_at) VALUES (?, ?, ?, ?, ?)"
  ).bind(status.id, user.id, status.object_id, statusUrl(env, user, status.id), new Date().toISOString()).run();
  await env.DB.prepare("DELETE FROM statuses WHERE id = ?").bind(status.id).run();
  await env.DB.prepare("DELETE FROM media WHERE status_id = ?").bind(status.id).run();
  await env.DB.prepare("DELETE FROM mentions WHERE status_id = ?").bind(status.id).run();
  await env.DB.prepare("DELETE FROM hashtags WHERE status_id = ?").bind(status.id).run();
  await env.DB.prepare("DELETE FROM favourites WHERE status_id = ?").bind(status.id).run();
  await env.DB.prepare("DELETE FROM reblogs WHERE status_id = ?").bind(status.id).run();
  await env.DB.prepare("DELETE FROM notifications WHERE status_id = ?").bind(status.id).run();
  const poll = await env.DB.prepare("SELECT id FROM polls WHERE status_id = ?").bind(status.id).first<{ id: string }>();
  if (poll) {
    await env.DB.prepare("DELETE FROM poll_votes WHERE poll_id = ?").bind(poll.id).run();
    await env.DB.prepare("DELETE FROM poll_options WHERE poll_id = ?").bind(poll.id).run();
    await env.DB.prepare("DELETE FROM polls WHERE id = ?").bind(poll.id).run();
  }

  const mentionActors = mentions.map((mention) => mention.actor);
  const deleteAudience = status.visibility === "direct"
    ? { to: mentionActors, cc: [] }
    : status.visibility === "private"
      ? { to: [`${actorUrl(env, user)}/followers`, ...mentionActors], cc: [] }
      : {};
  await deliverToInboxes(env, user, inboxes, deleteActivity(env, user, status, deleteAudience));
  return json(serialized);
}

export async function statusContext(env: Env, statusId: string, request: Request): Promise<Response> {
  const status = await getStatus(env, statusId);
  if (!status) return json({ error: "Record not found" }, 404);
  const viewer = await loadStatusViewer(request, env);
  if (!await canViewerViewStatus(env, status, viewer)) return json({ error: "Record not found" }, 404);

  const ancestors: Status[] = [];
  let cursor = status.in_reply_to_id;
  while (cursor) {
    const parent = await getStatus(env, cursor);
    if (!parent) break;
    ancestors.unshift(parent);
    cursor = parent.in_reply_to_id;
  }
  const descRows = await env.DB.prepare("SELECT * FROM statuses WHERE in_reply_to_id = ? ORDER BY created_at ASC LIMIT 40").bind(statusId).all<Status>();
  const visibleAncestors = await filterStatusesForViewer(env, ancestors, viewer);
  const visibleDescendants = await filterStatusesForViewer(env, descRows.results, viewer);
  const serialized = await serializeStatuses(env, [...visibleAncestors, ...visibleDescendants], request);
  const byId = new Map(serialized.map((item) => [String(item.id), item]));
  return json({
    ancestors: visibleAncestors.map((item) => byId.get(item.id)).filter(Boolean),
    descendants: visibleDescendants.map((item) => byId.get(item.id)).filter(Boolean)
  });
}

export async function favouriteStatus(request: Request, env: Env, statusId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const status = await visibleStatusOrNull(env, statusId, statusViewerForUser(env, user));
  if (!status) return json({ error: "Record not found" }, 404);

  const actor = actorUrl(env, user);
  const existing = await findFavourite(env, status.id, actor);
  if (!existing) {
    const activityId = activityUrl(env, id());
    await env.DB.prepare(
      "INSERT INTO favourites (id, status_id, actor, activity_id, created_at) VALUES (?, ?, ?, ?, ?)"
    ).bind(id(), status.id, actor, activityId, new Date().toISOString()).run();

    const owner = await getUserById(env, status.user_id);
    if (owner && owner.id !== user.id) {
      await recordNotification(env, owner.id, "favourite", actor, status.id);
    }
    if (!isLocalActor(env, status.object_id)) {
      const inboxes = await resolveDeliveryInboxes(env, [status.object_id]);
      await deliverToInboxes(env, user, inboxes, likeActivity(env, user, status.object_id, activityId));
    }
  }
  const owner = await getUserById(env, status.user_id);
  if (!owner) throw new HttpError(500, "owner_missing");
  return json(await statusJson(env, status, owner, request));
}

export async function unfavouriteStatus(request: Request, env: Env, statusId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const status = await visibleStatusOrNull(env, statusId, statusViewerForUser(env, user));
  if (!status) return json({ error: "Record not found" }, 404);

  const actor = actorUrl(env, user);
  const existing = await findFavourite(env, status.id, actor);
  if (existing) {
    await env.DB.prepare("DELETE FROM favourites WHERE id = ?").bind(existing.id).run();
    if (!isLocalActor(env, status.object_id)) {
      const inboxes = await resolveDeliveryInboxes(env, [status.object_id]);
      await deliverToInboxes(env, user, inboxes, undoActivity(env, user, likeActivity(env, user, status.object_id, existing.activity_id)));
    }
  }
  const owner = await getUserById(env, status.user_id);
  if (!owner) throw new HttpError(500, "owner_missing");
  return json(await statusJson(env, status, owner, request));
}

export async function reblogStatus(request: Request, env: Env, statusId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const status = await visibleStatusOrNull(env, statusId, statusViewerForUser(env, user));
  if (!status) return json({ error: "Record not found" }, 404);
  if (status.visibility !== "public" && status.visibility !== "unlisted") return json({ error: "status_not_rebloggable" }, 422);

  const actor = actorUrl(env, user);
  const existing = await findReblog(env, status.id, actor);
  if (!existing) {
    const activityId = activityUrl(env, id());
    await env.DB.prepare(
      "INSERT INTO reblogs (id, status_id, actor, activity_id, created_at) VALUES (?, ?, ?, ?, ?)"
    ).bind(id(), status.id, actor, activityId, new Date().toISOString()).run();
    const owner = await getUserById(env, status.user_id);
    if (owner && owner.id !== user.id) {
      await recordNotification(env, owner.id, "reblog", actor, status.id);
    }
    const inboxes = new Set<string>(await gatherFollowerInboxes(env, user.id));
    if (!isLocalActor(env, status.object_id)) {
      for (const inbox of await resolveDeliveryInboxes(env, [status.object_id])) inboxes.add(inbox);
    }
    await deliverToInboxes(env, user, inboxes, announceActivity(env, user, status.object_id, activityId));
  }
  const owner = await getUserById(env, status.user_id);
  if (!owner) throw new HttpError(500, "owner_missing");
  return json(await statusJson(env, status, owner, request));
}

export async function unreblogStatus(request: Request, env: Env, statusId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const status = await visibleStatusOrNull(env, statusId, statusViewerForUser(env, user));
  if (!status) return json({ error: "Record not found" }, 404);

  const actor = actorUrl(env, user);
  const existing = await findReblog(env, status.id, actor);
  if (existing) {
    await env.DB.prepare("DELETE FROM reblogs WHERE id = ?").bind(existing.id).run();
    const inboxes = new Set<string>(await gatherFollowerInboxes(env, user.id));
    if (!isLocalActor(env, status.object_id)) {
      for (const inbox of await resolveDeliveryInboxes(env, [status.object_id])) inboxes.add(inbox);
    }
    await deliverToInboxes(env, user, inboxes, undoActivity(env, user, announceActivity(env, user, status.object_id, existing.activity_id)));
  }
  const owner = await getUserById(env, status.user_id);
  if (!owner) throw new HttpError(500, "owner_missing");
  return json(await statusJson(env, status, owner, request));
}

export async function bookmarkStatus(request: Request, env: Env, statusId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const status = await visibleStatusOrNull(env, statusId, statusViewerForUser(env, user));
  if (!status) return json({ error: "Record not found" }, 404);
  await addBookmark(env, user.id, status.id);
  const owner = await getUserById(env, status.user_id);
  if (!owner) throw new HttpError(500, "owner_missing");
  return json(await statusJson(env, status, owner, request));
}

export async function unbookmarkStatus(request: Request, env: Env, statusId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const status = await visibleStatusOrNull(env, statusId, statusViewerForUser(env, user));
  if (!status) return json({ error: "Record not found" }, 404);
  await removeBookmark(env, user.id, status.id);
  const owner = await getUserById(env, status.user_id);
  if (!owner) throw new HttpError(500, "owner_missing");
  return json(await statusJson(env, status, owner, request));
}

export async function pinStatus(request: Request, env: Env, statusId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const status = await getStatus(env, statusId);
  if (!status || status.user_id !== user.id) return json({ error: "Record not found" }, 404);
  await addPin(env, user.id, status.id);
  return json(await statusJson(env, status, user, request));
}

export async function unpinStatus(request: Request, env: Env, statusId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const status = await getStatus(env, statusId);
  if (!status || status.user_id !== user.id) return json({ error: "Record not found" }, 404);
  await removePin(env, user.id, status.id);
  return json(await statusJson(env, status, user, request));
}

export async function bookmarksList(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  const url = new URL(request.url);
  const limit = clampLimit(url.searchParams.get("limit"), 20, 40);
  const rows = await env.DB.prepare(
    `SELECT s.* FROM statuses s INNER JOIN bookmarks b ON b.status_id = s.id
     WHERE b.user_id = ? ORDER BY b.created_at DESC LIMIT ?`
  ).bind(user.id, limit).all<Status>();
  const visibleRows = await filterStatusesForViewer(env, rows.results, statusViewerForUser(env, user));
  const items = await serializeStatuses(env, visibleRows, request);
  return withPagination(json(items), request, visibleRows.map((s) => s.id));
}

export async function favouritesList(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  const url = new URL(request.url);
  const limit = clampLimit(url.searchParams.get("limit"), 20, 40);
  const actor = actorUrl(env, user);
  const rows = await env.DB.prepare(
    `SELECT s.* FROM statuses s INNER JOIN favourites f ON f.status_id = s.id
     WHERE f.actor = ? ORDER BY f.created_at DESC LIMIT ?`
  ).bind(actor, limit).all<Status>();
  const visibleRows = await filterStatusesForViewer(env, rows.results, statusViewerForUser(env, user));
  const items = await serializeStatuses(env, visibleRows, request);
  return withPagination(json(items), request, visibleRows.map((s) => s.id));
}

export async function publicTimeline(request: Request, env: Env): Promise<Response> {
  const url = new URL(request.url);
  const limit = clampLimit(url.searchParams.get("limit"), 20, 40);
  const where: string[] = ["visibility = 'public'"];
  const binds: unknown[] = [];
  pagedAppend(where, binds, url);
  const sql = `SELECT * FROM statuses WHERE ${where.join(" AND ")} ORDER BY created_at DESC LIMIT ?`;
  binds.push(limit);
  const rows = await env.DB.prepare(sql).bind(...binds).all<Status>();
  const items = await serializeStatuses(env, rows.results, request);
  return withPagination(json(items), request, rows.results.map((s) => s.id));
}

export async function homeTimeline(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  const url = new URL(request.url);
  const limit = clampLimit(url.searchParams.get("limit"), 20, 40);
  const viewer = statusViewerForUser(env, user);
  const cachedFetchLimit = Math.min(limit * 4, 160);

  const localRows = await env.DB.prepare(
    "SELECT * FROM statuses WHERE user_id = ? ORDER BY created_at DESC LIMIT ?"
  ).bind(user.id, limit).all<Status>();

  const cachedRows = await env.DB.prepare(
    `SELECT cs.* FROM cached_statuses cs
     INNER JOIN outgoing_follows of ON of.target_actor = cs.actor
     WHERE of.local_user_id = ? AND of.accepted = 1
     ORDER BY cs.published DESC LIMIT ?`
  ).bind(user.id, cachedFetchLimit).all<CachedStatus>();

  const localItems = await serializeStatuses(env, localRows.results, request);
  const visibleCachedRows = await filterCachedStatusesForViewer(env, cachedRows.results, viewer);
  const cachedItems = await Promise.all(visibleCachedRows.slice(0, limit).map((row) => cachedStatusToMastodon(env, row)));

  const merged = [...localItems, ...cachedItems].sort((a, b) => {
    const at = String(a.created_at ?? "");
    const bt = String(b.created_at ?? "");
    return bt.localeCompare(at);
  }).slice(0, limit);

  return json(merged);
}

export async function hashtagTimeline(request: Request, env: Env, tag: string): Promise<Response> {
  const url = new URL(request.url);
  const limit = clampLimit(url.searchParams.get("limit"), 20, 40);
  const where: string[] = ["s.visibility = 'public'", "h.tag = ?"];
  const binds: unknown[] = [tag.toLowerCase()];
  const maxId = url.searchParams.get("max_id");
  if (maxId) { where.push("s.created_at < (SELECT created_at FROM statuses WHERE id = ?)"); binds.push(maxId); }
  const sinceId = url.searchParams.get("since_id");
  if (sinceId) { where.push("s.created_at > (SELECT created_at FROM statuses WHERE id = ?)"); binds.push(sinceId); }
  const minId = url.searchParams.get("min_id");
  if (minId) { where.push("s.created_at > (SELECT created_at FROM statuses WHERE id = ?)"); binds.push(minId); }
  const sql = `SELECT s.* FROM statuses s INNER JOIN hashtags h ON h.status_id = s.id WHERE ${where.join(" AND ")} ORDER BY s.created_at DESC LIMIT ?`;
  binds.push(limit);
  const rows = await env.DB.prepare(sql).bind(...binds).all<Status>();
  const items = await serializeStatuses(env, rows.results, request);
  return withPagination(json(items), request, rows.results.map((s) => s.id));
}

export async function hashtagInfo(env: Env, tag: string): Promise<Response> {
  const normalized = tag.toLowerCase();
  const row = await env.DB.prepare("SELECT COUNT(DISTINCT status_id) AS count FROM hashtags WHERE tag = ?").bind(normalized).first<{ count: number }>();
  return json({
    name: normalized,
    url: `${baseUrl(env)}/tags/${encodeURIComponent(normalized)}`,
    history: [],
    following: false,
    statuses_count: row?.count ?? 0
  });
}

export async function uploadMedia(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  const form = await request.formData();
  const file = form.get("file");
  if (!(file instanceof File)) return json({ error: "file is required" }, 422);
  if (file.size > MAX_MEDIA_BYTES) return json({ error: "file too large" }, 413);
  if (!isSupportedImageMime(file.type)) return json({ error: "unsupported media type" }, 415);

  const mediaId = id();
  const key = `${user.id}/${mediaId}/${safeFileName(file.name || "upload")}`;
  await env.MEDIA.put(key, file.stream(), { httpMetadata: { contentType: file.type || "application/octet-stream" } });
  await env.DB.prepare(
    "INSERT INTO media (id, user_id, status_id, r2_key, mime_type, description, size, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
  )
    .bind(mediaId, user.id, null, key, file.type || "application/octet-stream", form.get("description")?.toString() ?? null, file.size, new Date().toISOString())
    .run();

  const media = await env.DB.prepare("SELECT * FROM media WHERE id = ?").bind(mediaId).first<Media>();
  return json(mediaJson(env, media!), 200);
}

export async function updateMedia(request: Request, env: Env, mediaId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const body = await readBody(request);
  const description = bodyString(body, "description", "");
  await env.DB.prepare("UPDATE media SET description = ? WHERE id = ? AND user_id = ?").bind(description, mediaId, user.id).run();
  const media = await env.DB.prepare("SELECT * FROM media WHERE id = ?").bind(mediaId).first<Media>();
  if (!media) return json({ error: "Record not found" }, 404);
  return json(mediaJson(env, media));
}

export async function serveMedia(env: Env, key: string): Promise<Response> {
  const object = await env.MEDIA.get(decodeURIComponent(key));
  if (!object) return new Response("Not found", { status: 404 });
  return cors(new Response(object.body, {
    headers: {
      "content-type": object.httpMetadata?.contentType ?? "application/octet-stream",
      "cache-control": "public, max-age=86400"
    }
  }));
}

export async function notificationsList(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  const url = new URL(request.url);
  const limit = clampLimit(url.searchParams.get("limit"), 15, 80);
  const types = normalizeArray(url.searchParams.getAll("types[]"));
  const excludeTypes = normalizeArray(url.searchParams.getAll("exclude_types[]"));

  const where: string[] = ["user_id = ?"];
  const binds: unknown[] = [user.id];
  if (types.length > 0) {
    where.push(`type IN (${types.map(() => "?").join(",")})`);
    binds.push(...types);
  }
  if (excludeTypes.length > 0) {
    where.push(`type NOT IN (${excludeTypes.map(() => "?").join(",")})`);
    binds.push(...excludeTypes);
  }

  const maxId = url.searchParams.get("max_id");
  if (maxId) {
    where.push("created_at < (SELECT created_at FROM notifications WHERE id = ?)");
    binds.push(maxId);
  }
  const sinceId = url.searchParams.get("since_id");
  if (sinceId) {
    where.push("created_at > (SELECT created_at FROM notifications WHERE id = ?)");
    binds.push(sinceId);
  }

  const sql = `SELECT * FROM notifications WHERE ${where.join(" AND ")} ORDER BY created_at DESC LIMIT ?`;
  binds.push(limit);

  const rows = await env.DB.prepare(sql).bind(...binds).all<Notification>();
  const out = await serializeNotifications(env, rows.results, request);
  return withPagination(json(out), request, rows.results.map((n) => n.id));
}

export async function notificationClear(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  await env.DB.prepare("DELETE FROM notifications WHERE user_id = ?").bind(user.id).run();
  return json({});
}

export async function notificationDismiss(request: Request, env: Env, notificationId: string): Promise<Response> {
  const user = await requireUser(request, env);
  await env.DB.prepare("DELETE FROM notifications WHERE id = ? AND user_id = ?").bind(notificationId, user.id).run();
  return json({});
}

export async function getRelationships(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  const url = new URL(request.url);
  const ids = url.searchParams.getAll("id[]").concat(url.searchParams.getAll("id"));
  const out = [];
  for (const target of ids) {
    out.push(await relationshipFor(env, user, target));
  }
  return json(out);
}

export async function listsList(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  const rows = await env.DB.prepare("SELECT * FROM lists WHERE user_id = ? ORDER BY created_at DESC").bind(user.id).all<AccountList>();
  return json(rows.results.map(listJson));
}

export async function createList(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  const body = await readBody(request);
  const title = bodyString(body, "title").trim();
  if (!title) return json({ error: "title can't be blank" }, 422);
  const row: AccountList = {
    id: id(),
    user_id: user.id,
    title,
    replies_policy: bodyString(body, "replies_policy", "list") || "list",
    exclusive: bodyString(body, "exclusive") === "true" ? 1 : 0,
    created_at: new Date().toISOString()
  };
  await env.DB.prepare(
    "INSERT INTO lists (id, user_id, title, replies_policy, exclusive, created_at) VALUES (?, ?, ?, ?, ?, ?)"
  ).bind(row.id, row.user_id, row.title, row.replies_policy, row.exclusive, row.created_at).run();
  return json(listJson(row));
}

export async function getList(request: Request, env: Env, listId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const row = await getOwnedList(env, user.id, listId);
  if (!row) return json({ error: "Record not found" }, 404);
  return json(listJson(row));
}

export async function updateList(request: Request, env: Env, listId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const existing = await getOwnedList(env, user.id, listId);
  if (!existing) return json({ error: "Record not found" }, 404);
  const body = await readBody(request);
  const title = bodyString(body, "title", existing.title).trim();
  if (!title) return json({ error: "title can't be blank" }, 422);
  const repliesPolicy = bodyString(body, "replies_policy", existing.replies_policy) || existing.replies_policy;
  const exclusiveValue = bodyString(body, "exclusive");
  const exclusive = exclusiveValue ? (exclusiveValue === "true" ? 1 : 0) : existing.exclusive;
  await env.DB.prepare("UPDATE lists SET title = ?, replies_policy = ?, exclusive = ? WHERE id = ? AND user_id = ?")
    .bind(title, repliesPolicy, exclusive, listId, user.id).run();
  const updated = await getOwnedList(env, user.id, listId);
  return json(listJson(updated!));
}

export async function deleteList(request: Request, env: Env, listId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const existing = await getOwnedList(env, user.id, listId);
  if (!existing) return json({ error: "Record not found" }, 404);
  await env.DB.prepare("DELETE FROM list_accounts WHERE list_id = ?").bind(listId).run();
  await env.DB.prepare("DELETE FROM lists WHERE id = ? AND user_id = ?").bind(listId, user.id).run();
  return json({});
}

export async function listAccounts(request: Request, env: Env, listId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const existing = await getOwnedList(env, user.id, listId);
  if (!existing) return json({ error: "Record not found" }, 404);
  const rows = await env.DB.prepare("SELECT account_actor FROM list_accounts WHERE list_id = ? ORDER BY created_at DESC").bind(listId).all<{ account_actor: string }>();
  return json(await actorIdsToAccounts(env, rows.results.map((row) => row.account_actor)));
}

export async function addListAccounts(request: Request, env: Env, listId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const existing = await getOwnedList(env, user.id, listId);
  if (!existing) return json({ error: "Record not found" }, 404);
  const body = await readBody(request);
  const accountIds = bodyArray(body, "account_ids");
  if (accountIds.length === 0) return json({ error: "account_ids can't be blank" }, 422);
  const now = new Date().toISOString();
  for (const accountId of accountIds) {
    const target = await resolveAccountTarget(env, accountId);
    if (!target) continue;
    await env.DB.prepare("INSERT OR IGNORE INTO list_accounts (list_id, account_actor, created_at) VALUES (?, ?, ?)")
      .bind(listId, target.actorId, now).run();
  }
  return json({});
}

export async function removeListAccounts(request: Request, env: Env, listId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const existing = await getOwnedList(env, user.id, listId);
  if (!existing) return json({ error: "Record not found" }, 404);
  const body = await readBody(request);
  for (const accountId of bodyArray(body, "account_ids")) {
    const target = await resolveAccountTarget(env, accountId);
    const actorId = target?.actorId ?? accountId;
    await env.DB.prepare("DELETE FROM list_accounts WHERE list_id = ? AND account_actor = ?").bind(listId, actorId).run();
  }
  return json({});
}

export async function listTimeline(request: Request, env: Env, listId: string): Promise<Response> {
  const user = await requireUser(request, env);
  const existing = await getOwnedList(env, user.id, listId);
  if (!existing) return json({ error: "Record not found" }, 404);
  const url = new URL(request.url);
  const limit = clampLimit(url.searchParams.get("limit"), 20, 40);
  const accountRows = await env.DB.prepare("SELECT account_actor FROM list_accounts WHERE list_id = ?").bind(listId).all<{ account_actor: string }>();
  const actors = accountRows.results.map((row) => row.account_actor);
  const localUserIds: string[] = [];
  const remoteActors: string[] = [];
  for (const actor of actors) {
    if (actor.startsWith(baseUrl(env))) {
      const match = actor.match(/\/users\/([^/?#]+)$/);
      const local = match ? await getUserByUsername(env, match[1]) : null;
      if (local) localUserIds.push(local.id);
    } else {
      remoteActors.push(actor);
    }
  }

  const viewer = statusViewerForUser(env, user);
  const localRows = localUserIds.length > 0
    ? (await env.DB.prepare(`SELECT * FROM statuses WHERE user_id IN (${placeholders(localUserIds.length)}) ORDER BY created_at DESC LIMIT ?`).bind(...localUserIds, limit * 2).all<Status>()).results
    : [];
  const remoteRows = remoteActors.length > 0
    ? (await env.DB.prepare(`SELECT * FROM cached_statuses WHERE actor IN (${placeholders(remoteActors.length)}) ORDER BY published DESC LIMIT ?`).bind(...remoteActors, limit * 2).all<CachedStatus>()).results
    : [];
  const visibleLocalRows = await filterStatusesForViewer(env, localRows, viewer);
  const visibleRemoteRows = await filterCachedStatusesForViewer(env, remoteRows, viewer);
  const localItems = await serializeStatuses(env, visibleLocalRows.slice(0, limit), request);
  const remoteItems = await Promise.all(visibleRemoteRows.slice(0, limit).map((row) => cachedStatusToMastodon(env, row)));
  return json([...localItems, ...remoteItems].sort((a, b) => String(b.created_at ?? "").localeCompare(String(a.created_at ?? ""))).slice(0, limit));
}

export async function followAccount(request: Request, env: Env, accountId: string): Promise<Response> {
  const user = await requireUser(request, env, ["follow"]);
  const target = await resolveAccountTarget(env, accountId);
  if (!target) return json({ error: "Record not found" }, 404);

  if (target.kind === "local") {
    await env.DB.prepare(
      "INSERT OR REPLACE INTO outgoing_follows (id, local_user_id, target_actor, target_inbox, activity_id, accepted, created_at) VALUES (?, ?, ?, ?, ?, 1, ?)"
    ).bind(id(), user.id, target.actorId, `${target.actorId}/inbox`, "", new Date().toISOString()).run();
    await env.DB.prepare(
      "INSERT OR REPLACE INTO follows (id, follower_actor, local_user_id, inbox, accepted, created_at) VALUES (?, ?, ?, ?, 1, ?)"
    ).bind(id(), actorUrl(env, user), target.userId, `${actorUrl(env, user)}/inbox`, new Date().toISOString()).run();
  } else {
    const activityId = activityUrl(env, id());
    const cache = await resolveRemoteActor(env, target.actorId);
    if (!cache) return json({ error: "remote_actor_unreachable" }, 502);
    await env.DB.prepare(
      "INSERT OR REPLACE INTO outgoing_follows (id, local_user_id, target_actor, target_inbox, activity_id, accepted, created_at) VALUES (?, ?, ?, ?, ?, 0, ?)"
    ).bind(id(), user.id, cache.id, cache.inbox, activityId, new Date().toISOString()).run();
    await deliverToInboxes(env, user, [cache.inbox], followActivity(env, user, cache.id, activityId));
  }
  return json(await relationshipFor(env, user, accountId));
}

export async function unfollowAccount(request: Request, env: Env, accountId: string): Promise<Response> {
  const user = await requireUser(request, env, ["follow"]);
  const target = await resolveAccountTarget(env, accountId);
  if (!target) return json({ error: "Record not found" }, 404);

  const existing = await findOutgoingFollow(env, user.id, target.actorId);
  await env.DB.prepare("DELETE FROM outgoing_follows WHERE local_user_id = ? AND target_actor = ?").bind(user.id, target.actorId).run();

  if (target.kind === "local") {
    await env.DB.prepare("DELETE FROM follows WHERE follower_actor = ? AND local_user_id = ?").bind(actorUrl(env, user), target.userId).run();
  } else if (existing) {
    const cache = await resolveRemoteActor(env, target.actorId);
    if (cache) {
      await deliverToInboxes(env, user, [cache.inbox], undoActivity(env, user, followActivity(env, user, target.actorId, existing.activity_id)));
    }
  }
  return json(await relationshipFor(env, user, accountId));
}

export async function followRequestsList(request: Request, env: Env): Promise<Response> {
  await requireUser(request, env, ["follow"]);
  return json([]);
}

export async function authorizeFollowRequest(request: Request, env: Env, _accountId: string): Promise<Response> {
  await requireUser(request, env, ["follow"]);
  return json({ id: _accountId, following: true, requested: false });
}

export async function rejectFollowRequest(request: Request, env: Env, _accountId: string): Promise<Response> {
  await requireUser(request, env, ["follow"]);
  return json({ id: _accountId, following: false, requested: false });
}

export async function search(request: Request, env: Env): Promise<Response> {
  const url = new URL(request.url);
  const q = (url.searchParams.get("q") ?? "").trim().slice(0, 128);
  const type = url.searchParams.get("type");
  const accounts: unknown[] = [];
  const statuses: unknown[] = [];
  const hashtags: unknown[] = [];
  if (!q) return json({ accounts, statuses, hashtags });

  if (!type || type === "accounts") {
    if (q.startsWith("@") || q.includes("@")) {
      const acct = q.replace(/^@/, "");
      const resolved = await resolveAcct(env, acct);
      if (resolved) {
        if (resolved.actorId.startsWith(baseUrl(env))) {
          const local = await getUserByUsername(env, resolved.acct.split("@")[0]);
          if (local) accounts.push(await accountJson(env, local));
        } else {
          const cache = await resolveRemoteActor(env, resolved.actorId);
          if (cache) accounts.push(remoteAccountJson(cache));
        }
      }
    } else {
      const rows = await env.DB.prepare("SELECT * FROM users WHERE username LIKE ? ESCAPE '\\' LIMIT 20").bind(likeContains(q)).all<User>();
      for (const row of rows.results) accounts.push(await accountJson(env, row));
    }
  }

  if (!type || type === "statuses") {
    const viewer = await loadStatusViewer(request, env);
    const remoteStatus = await resolveRemoteStatusSearch(env, q);
    if (remoteStatus && await canViewerViewCachedStatus(env, remoteStatus, viewer)) {
      statuses.push(await cachedStatusToMastodon(env, remoteStatus));
    }
    const rows = await env.DB.prepare("SELECT * FROM statuses WHERE content LIKE ? ESCAPE '\\' ORDER BY created_at DESC LIMIT 100").bind(likeContains(q)).all<Status>();
    const visibleRows = await filterStatusesForViewer(env, rows.results, viewer);
    statuses.push(...await serializeStatuses(env, visibleRows.slice(0, 20), request));
  }

  if (!type || type === "hashtags") {
    const tag = q.replace(/^#/, "");
    const rows = await env.DB.prepare("SELECT tag, COUNT(*) AS count FROM hashtags WHERE tag LIKE ? ESCAPE '\\' GROUP BY tag LIMIT 20").bind(likeContains(tag)).all<{ tag: string; count: number }>();
    for (const row of rows.results) hashtags.push({ name: row.tag, url: `${baseUrl(env)}/tags/${encodeURIComponent(row.tag)}`, history: [] });
  }

  return json({ accounts, statuses, hashtags });
}

function likeContains(value: string): string {
  return `%${value.replace(/[\\%_]/g, (char) => `\\${char}`)}%`;
}

async function resolveRemoteStatusSearch(env: Env, q: string): Promise<CachedStatus | null> {
  const url = parseSearchUrl(q);
  if (!url || url.host.toLowerCase() === hostFromBaseUrl(env).toLowerCase()) return null;

  const cached = await env.DB.prepare("SELECT * FROM cached_statuses WHERE object_id = ? OR url = ? LIMIT 1")
    .bind(url.toString(), url.toString()).first<CachedStatus>();
  if (cached) return cached;

  let data: Json;
  try {
    const response = await fetch(url.toString(), {
      headers: { accept: ACTIVITY_JSON_ACCEPT },
      signal: AbortSignal.timeout(10_000),
      cf: { cacheTtl: 60 }
    });
    if (!response.ok) return null;
    data = await response.json() as Json;
  } catch {
    return null;
  }

  const resolved = remoteNoteFromFetchedObject(data);
  if (!resolved) return null;
  await resolveRemoteActor(env, resolved.actorId);
  const stored = await cacheRemoteNote(env, resolved.actorId, resolved.note, resolved.activity);
  return stored ?? getCachedStatusByObjectId(env, String(resolved.note.id));
}

function parseSearchUrl(value: string): URL | null {
  try {
    const url = new URL(value);
    return url.protocol === "https:" || url.protocol === "http:" ? url : null;
  } catch {
    return null;
  }
}

function remoteNoteFromFetchedObject(value: Json): { actorId: string; note: Json; activity: Json } | null {
  const type = String(value.type ?? "");
  if (type === "Note") {
    const actorId = actorIdFromField(value.attributedTo);
    return actorId && typeof value.id === "string" ? { actorId, note: value, activity: {} } : null;
  }

  const note = objectAsJson(value.object);
  if (type === "Create" && note && String(note.type ?? "") === "Note" && typeof note.id === "string") {
    const actorId = actorIdFromField(value.actor) ?? actorIdFromField(note.attributedTo);
    return actorId ? { actorId, note, activity: value } : null;
  }
  if (type === "Announce" && note && String(note.type ?? "") === "Note" && typeof note.id === "string") {
    const actorId = actorIdFromField(note.attributedTo) ?? actorIdFromField(value.actor);
    return actorId ? { actorId, note, activity: value } : null;
  }
  return null;
}

function actorIdFromField(value: unknown): string | null {
  if (typeof value === "string" && value) return value;
  if (Array.isArray(value)) {
    for (const item of value) {
      const actorId = actorIdFromField(item);
      if (actorId) return actorId;
    }
  }
  const obj = objectAsJson(value);
  return typeof obj?.id === "string" && obj.id ? obj.id : null;
}

export async function customEmojis(env: Env): Promise<Response> {
  void env;
  return json([]);
}

export async function filtersV1(_request: Request, env: Env): Promise<Response> {
  void env;
  return json([]);
}

export async function trendsTags(env: Env): Promise<Response> {
  void env;
  return json([]);
}

export async function getPushSubscription(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  const row = await env.DB.prepare("SELECT * FROM push_subscriptions WHERE user_id = ?").bind(user.id).first<PushSubscription>();
  if (!row) return json({ error: "Record not found" }, 404);
  return json(pushSubscriptionJson(row));
}

export async function createPushSubscription(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  const body = await readBody(request);
  const endpoint = bodyString(body, "subscription[endpoint]");
  const serverKey = bodyString(body, "subscription[keys][p256dh]");
  const auth = bodyString(body, "subscription[keys][auth]");
  if (!endpoint || !serverKey || !auth) return json({ error: "subscription is incomplete" }, 422);
  const now = new Date().toISOString();
  const existing = await env.DB.prepare("SELECT * FROM push_subscriptions WHERE user_id = ?").bind(user.id).first<PushSubscription>();
  const idValue = existing?.id ?? id();
  const alerts = pushAlertsFromBody(body, existing?.alerts_json);
  const policy = bodyString(body, "data[policy]", existing?.policy ?? "all") || "all";
  await env.DB.prepare(
    `INSERT INTO push_subscriptions (id, user_id, endpoint, server_key, auth, alerts_json, policy, created_at, updated_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
     ON CONFLICT(user_id) DO UPDATE SET
       endpoint = excluded.endpoint,
       server_key = excluded.server_key,
       auth = excluded.auth,
       alerts_json = excluded.alerts_json,
       policy = excluded.policy,
       updated_at = excluded.updated_at`
  ).bind(idValue, user.id, endpoint, serverKey, auth, JSON.stringify(alerts), policy, existing?.created_at ?? now, now).run();
  const row = await env.DB.prepare("SELECT * FROM push_subscriptions WHERE user_id = ?").bind(user.id).first<PushSubscription>();
  return json(pushSubscriptionJson(row!));
}

export async function updatePushSubscription(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  const row = await env.DB.prepare("SELECT * FROM push_subscriptions WHERE user_id = ?").bind(user.id).first<PushSubscription>();
  if (!row) return json({ error: "Record not found" }, 404);
  const body = await readBody(request);
  const alerts = pushAlertsFromBody(body, row.alerts_json);
  const policy = bodyString(body, "data[policy]", row.policy) || row.policy;
  await env.DB.prepare("UPDATE push_subscriptions SET alerts_json = ?, policy = ?, updated_at = ? WHERE user_id = ?")
    .bind(JSON.stringify(alerts), policy, new Date().toISOString(), user.id).run();
  const updated = await env.DB.prepare("SELECT * FROM push_subscriptions WHERE user_id = ?").bind(user.id).first<PushSubscription>();
  return json(pushSubscriptionJson(updated!));
}

export async function deletePushSubscription(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  await env.DB.prepare("DELETE FROM push_subscriptions WHERE user_id = ?").bind(user.id).run();
  return json({});
}

export async function markersList(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  const url = new URL(request.url);
  const timelines = uniqueStrings(url.searchParams.getAll("timeline[]").concat(url.searchParams.getAll("timeline")));
  if (timelines.length === 0) return json({});

  const rows = await listMarkers(env, user.id, timelines);
  return json(markersJson(rows));
}

export async function updateMarkers(request: Request, env: Env): Promise<Response> {
  const user = await requireUser(request, env);
  const body = await readBody(request);
  const out: Record<string, unknown> = {};

  for (const timeline of ["home", "notifications"]) {
    const lastReadId = bodyString(body, `${timeline}[last_read_id]`).trim();
    if (!lastReadId) continue;
    const result = await saveMarker(env, user.id, timeline, lastReadId);
    if (result.conflict) throw new HttpError(409, "Conflict during update, please try again");
    if (result.marker) out[timeline] = markerJson(result.marker);
  }

  return json(out);
}

function markersJson(rows: Marker[]): Record<string, unknown> {
  const out: Record<string, unknown> = {};
  for (const row of rows) out[row.timeline] = markerJson(row);
  return out;
}

function markerJson(row: Marker): Record<string, unknown> {
  return {
    last_read_id: row.last_read_id,
    version: row.version,
    updated_at: row.updated_at
  };
}

function pushSubscriptionJson(row: PushSubscription): Record<string, unknown> {
  return {
    id: row.id,
    endpoint: row.endpoint,
    server_key: row.server_key,
    alerts: parseObjectJson(row.alerts_json),
    policy: row.policy
  };
}

function pushAlertsFromBody(body: ParsedBody, fallback?: string): Record<string, boolean> {
  const alerts = parseObjectJson(fallback ?? "{}") as Record<string, boolean>;
  const keys = ["follow", "favourite", "reblog", "mention", "poll", "status", "update", "admin.sign_up", "admin.report"];
  for (const key of keys) {
    const value = bodyString(body, `data[alerts][${key}]`);
    if (value) alerts[key] = value === "true";
  }
  return alerts;
}

function parseObjectJson(value: string): Record<string, unknown> {
  try {
    const parsed = JSON.parse(value) as unknown;
    return parsed && typeof parsed === "object" && !Array.isArray(parsed) ? parsed as Record<string, unknown> : {};
  } catch {
    return {};
  }
}

type StatusSerializationContext = {
  usersById: Map<string, User>;
  accountByUserId: Map<string, Record<string, unknown>>;
  mediaByStatusId: Map<string, Media[]>;
  mentionsByStatusId: Map<string, Mention[]>;
  hashtagsByStatusId: Map<string, string[]>;
  favouriteCountByStatusId: Map<string, number>;
  favouritedStatusIds: Set<string>;
  reblogCountByStatusId: Map<string, number>;
  rebloggedStatusIds: Set<string>;
  replyCountByStatusId: Map<string, number>;
  bookmarkedStatusIds: Set<string>;
  pinnedStatusIds: Set<string>;
  pollByStatusId: Map<string, Poll>;
  pollOptionsByPollId: Map<string, PollOption[]>;
  pollVotesByPollId: Map<string, Map<number, number>>;
  pollVotersCountByPollId: Map<string, number>;
  pollOwnVotesByPollId: Map<string, number[]>;
};

async function cachedStatusToMastodon(env: Env, row: CachedStatus): Promise<Record<string, unknown>> {
  const cache = await resolveRemoteActor(env, row.actor);
  const account = cache ? remoteAccountJson(cache) : { id: row.actor, acct: row.actor, username: row.actor };
  const attachments = await listCachedStatusAttachments(env, row.id);
  const mentions = parseCachedJson<CachedStatusMention>(row.mentions_json);
  const tags = parseCachedJson<CachedStatusTag>(row.tags_json);
  return {
    id: row.object_id,
    uri: row.object_id,
    url: row.url,
    account,
    in_reply_to_id: row.in_reply_to,
    in_reply_to_account_id: null,
    content: row.content,
    text: row.content,
    created_at: row.published,
    edited_at: null,
    visibility: row.visibility,
    language: row.language,
    sensitive: Boolean(row.sensitive),
    spoiler_text: row.summary,
    media_attachments: attachments.map((att) => ({
      id: `${row.id}:${att.position}`,
      type: att.mime_type.startsWith("image/") ? "image"
        : att.mime_type.startsWith("video/") ? "video"
        : att.mime_type.startsWith("audio/") ? "audio" : "unknown",
      url: att.url,
      preview_url: att.preview_url ?? att.url,
      remote_url: att.url,
      text_url: null,
      meta: {},
      description: att.description,
      blurhash: null
    })),
    mentions: mentions.map((mention) => ({
      id: mention.actor,
      username: mention.acct.replace(/^@/, "").split("@")[0],
      acct: mention.acct.replace(/^@/, ""),
      url: mention.url
    })),
    tags: tags.map((tag) => ({ name: tag.name, url: tag.url })),
    emojis: [],
    reblogs_count: 0,
    favourites_count: 0,
    replies_count: 0,
    reblog: null,
    application: null,
    favourited: false,
    reblogged: false,
    muted: false,
    bookmarked: false,
    pinned: false,
    card: null,
    poll: null
  };
}

function parseCachedJson<T>(value: string): T[] {
  try {
    const parsed = JSON.parse(value) as unknown;
    return Array.isArray(parsed) ? parsed as T[] : [];
  } catch {
    return [];
  }
}

function statusSourceText(status: Status): string {
  return status.source_text || htmlToPlainText(status.content);
}

function htmlToPlainText(value: string): string {
  return decodeHtmlEntities(value
    .replace(/<br\s*\/?>/gi, "\n")
    .replace(/<\/p>\s*<p[^>]*>/gi, "\n\n")
    .replace(/<[^>]*>/g, "")
    .trim());
}

function decodeHtmlEntities(value: string): string {
  return value
    .replace(/&nbsp;/g, " ")
    .replace(/&amp;/g, "&")
    .replace(/&lt;/g, "<")
    .replace(/&gt;/g, ">")
    .replace(/&quot;/g, '"')
    .replace(/&#39;/g, "'");
}

async function statusJson(
  env: Env,
  status: Status,
  user: User,
  request: Request,
  context?: StatusSerializationContext
): Promise<Record<string, unknown>> {
  const resolvedContext = context ?? await buildStatusSerializationContext(env, [status], request, new Map([[user.id, user]]));
  return statusRecord(env, status, user, resolvedContext);
}

function statusRecord(env: Env, status: Status, user: User, context: StatusSerializationContext): Record<string, unknown> {
  const media = context.mediaByStatusId.get(status.id) ?? [];
  const mentions = context.mentionsByStatusId.get(status.id) ?? [];
  const tags = context.hashtagsByStatusId.get(status.id) ?? [];
  return {
    id: status.id,
    uri: status.object_id,
    url: statusUrl(env, user, status.id),
    account: context.accountByUserId.get(user.id) ?? {
      id: user.id,
      username: user.username,
      acct: user.username,
      display_name: user.display_name
    },
    in_reply_to_id: status.in_reply_to_id,
    in_reply_to_account_id: null,
    content: status.content,
    text: status.content,
    created_at: status.created_at,
    edited_at: status.edited_at,
    visibility: status.visibility,
    language: status.language,
    sensitive: Boolean(status.sensitive),
    spoiler_text: status.summary,
    media_attachments: media.map((item) => mediaJson(env, item)),
    mentions: mentions.map((mention) => ({
      id: mention.actor,
      username: mention.acct.split("@")[0],
      acct: mention.acct,
      url: mention.url
    })),
    tags: tags.map((tag) => ({ name: tag, url: `${baseUrl(env)}/tags/${encodeURIComponent(tag)}` })),
    emojis: [],
    reblogs_count: context.reblogCountByStatusId.get(status.id) ?? 0,
    favourites_count: context.favouriteCountByStatusId.get(status.id) ?? 0,
    replies_count: context.replyCountByStatusId.get(status.id) ?? 0,
    reblog: null,
    application: { name: "Toot Worker", website: null },
    favourited: context.favouritedStatusIds.has(status.id),
    reblogged: context.rebloggedStatusIds.has(status.id),
    muted: false,
    bookmarked: context.bookmarkedStatusIds.has(status.id),
    pinned: context.pinnedStatusIds.has(status.id),
    card: null,
    poll: pollRecord(status.id, context)
  };
}

function pollRecord(statusId: string, context: StatusSerializationContext): Record<string, unknown> | null {
  const poll = context.pollByStatusId.get(statusId);
  if (!poll) return null;
  const options = context.pollOptionsByPollId.get(poll.id) ?? [];
  const voteCounts = context.pollVotesByPollId.get(poll.id) ?? new Map<number, number>();
  const votersCount = context.pollVotersCountByPollId.get(poll.id) ?? 0;
  const ownVotes = context.pollOwnVotesByPollId.get(poll.id) ?? [];
  const now = Date.now();
  const expiresAt = poll.expires_at ? Date.parse(poll.expires_at) : NaN;
  const expired = Number.isFinite(expiresAt) ? expiresAt <= now : false;
  const showTotals = !poll.hide_totals || expired || ownVotes.length > 0;
  const votesCount = [...voteCounts.values()].reduce((total, count) => total + count, 0);
  return {
    id: poll.id,
    expires_at: poll.expires_at,
    expired,
    multiple: Boolean(poll.multiple),
    votes_count: showTotals ? votesCount : null,
    voters_count: showTotals ? votersCount : null,
    voted: ownVotes.length > 0,
    own_votes: ownVotes,
    options: options.map((option) => ({
      title: option.title,
      votes_count: showTotals ? voteCounts.get(option.position) ?? 0 : null
    })),
    emojis: []
  };
}

async function pollJson(env: Env, poll: Poll, viewer: string | null): Promise<Record<string, unknown>> {
  const [options, voteRows, votersRow] = await Promise.all([
    env.DB.prepare("SELECT * FROM poll_options WHERE poll_id = ? ORDER BY position ASC").bind(poll.id).all<PollOption>(),
    env.DB.prepare("SELECT position, COUNT(*) AS count FROM poll_votes WHERE poll_id = ? GROUP BY position").bind(poll.id).all<{ position: number; count: number }>(),
    env.DB.prepare("SELECT COUNT(DISTINCT voter_actor) AS count FROM poll_votes WHERE poll_id = ?").bind(poll.id).first<{ count: number }>()
  ]);
  const ownRows = viewer
    ? (await env.DB.prepare("SELECT position FROM poll_votes WHERE poll_id = ? AND voter_actor = ? ORDER BY position ASC").bind(poll.id, viewer).all<{ position: number }>()).results
    : [];
  const context: StatusSerializationContext = {
    usersById: new Map(),
    accountByUserId: new Map(),
    mediaByStatusId: new Map(),
    mentionsByStatusId: new Map(),
    hashtagsByStatusId: new Map(),
    favouriteCountByStatusId: new Map(),
    favouritedStatusIds: new Set(),
    reblogCountByStatusId: new Map(),
    rebloggedStatusIds: new Set(),
    replyCountByStatusId: new Map(),
    bookmarkedStatusIds: new Set(),
    pinnedStatusIds: new Set(),
    pollByStatusId: new Map([[poll.status_id, poll]]),
    pollOptionsByPollId: new Map([[poll.id, options.results]]),
    pollVotesByPollId: new Map([[poll.id, new Map(voteRows.results.map((row) => [row.position, row.count]))]]),
    pollVotersCountByPollId: new Map([[poll.id, votersRow?.count ?? 0]]),
    pollOwnVotesByPollId: new Map([[poll.id, ownRows.map((row) => row.position)]])
  };
  return pollRecord(poll.status_id, context)!;
}

async function serializeStatuses(
  env: Env,
  statuses: Status[],
  request: Request,
  usersById?: Map<string, User>
): Promise<Record<string, unknown>[]> {
  if (statuses.length === 0) return [];
  const context = await buildStatusSerializationContext(env, statuses, request, usersById);
  return statuses.flatMap((status) => {
    const user = context.usersById.get(status.user_id);
    return user ? [statusRecord(env, status, user, context)] : [];
  });
}

async function buildStatusSerializationContext(
  env: Env,
  statuses: Status[],
  request: Request,
  initialUsersById: Map<string, User> = new Map()
): Promise<StatusSerializationContext> {
  const statusIds = uniqueStrings(statuses.map((status) => status.id));
  const usersById = new Map(initialUsersById);
  const missingUserIds = uniqueStrings(statuses.map((status) => status.user_id).filter((userId) => !usersById.has(userId)));
  if (missingUserIds.length > 0) {
    for (const user of await loadUsersByIds(env, missingUserIds)) usersById.set(user.id, user);
  }

  const viewerUserForContext = await viewerUser(request, env);
  const viewer = viewerUserForContext ? actorUrl(env, viewerUserForContext) : null;
  const viewerId = viewerUserForContext?.id ?? null;
  const [mediaByStatusId, mentionsByStatusId, hashtagsByStatusId, favouriteSummary, reblogSummary, replyCountByStatusId, bookmarkedStatusIds, pinnedStatusIds, pollContext] = await Promise.all([
    loadMediaByStatusIds(env, statusIds),
    loadMentionsByStatusIds(env, statusIds),
    loadHashtagsByStatusIds(env, statusIds),
    loadStatusInteractionSummary(env, "favourites", statusIds, viewer),
    loadStatusInteractionSummary(env, "reblogs", statusIds, viewer),
    loadReplyCountByStatusIds(env, statusIds),
    viewerId ? loadBookmarkedStatusIds(env, viewerId, statusIds) : Promise.resolve(new Set<string>()),
    viewerId ? loadPinnedStatusIds(env, viewerId, statusIds) : Promise.resolve(new Set<string>()),
    loadPollSerializationContext(env, statusIds, viewer)
  ]);

  const accountByUserId = new Map<string, Record<string, unknown>>();
  for (const user of usersById.values()) {
    accountByUserId.set(user.id, await accountJson(env, user));
  }

  return {
    usersById,
    accountByUserId,
    mediaByStatusId,
    mentionsByStatusId,
    hashtagsByStatusId,
    favouriteCountByStatusId: favouriteSummary.countByStatusId,
    favouritedStatusIds: favouriteSummary.viewerMatchedStatusIds,
    reblogCountByStatusId: reblogSummary.countByStatusId,
    rebloggedStatusIds: reblogSummary.viewerMatchedStatusIds,
    replyCountByStatusId,
    bookmarkedStatusIds,
    pinnedStatusIds,
    pollByStatusId: pollContext.pollByStatusId,
    pollOptionsByPollId: pollContext.pollOptionsByPollId,
    pollVotesByPollId: pollContext.pollVotesByPollId,
    pollVotersCountByPollId: pollContext.pollVotersCountByPollId,
    pollOwnVotesByPollId: pollContext.pollOwnVotesByPollId
  };
}

async function accountJson(env: Env, user: User): Promise<Record<string, unknown>> {
  const [followersCount, followingCount, statusesCount, fields] = await Promise.all([
    countFollowers(env, user.id),
    countFollowing(env, user.id),
    countStatuses(env, user.id),
    listProfileFields(env, user.id)
  ]);
  const acct = `${user.username}`;
  const avatar = user.avatar_r2_key ? mediaUrl(env, user.avatar_r2_key) : `${baseUrl(env)}/avatar.png`;
  const header = user.header_r2_key ? mediaUrl(env, user.header_r2_key) : `${baseUrl(env)}/header.png`;
  return {
    id: user.id,
    username: user.username,
    acct,
    display_name: user.display_name,
    locked: false,
    bot: false,
    discoverable: true,
    group: false,
    created_at: user.created_at,
    note: user.note,
    url: profileUrl(env, user),
    avatar,
    avatar_static: avatar,
    header,
    header_static: header,
    followers_count: followersCount,
    following_count: followingCount,
    statuses_count: statusesCount,
    last_status_at: null,
    emojis: [],
    fields: fields.map((field) => ({ name: field.name, value: field.value, verified_at: null }))
  };
}

function remoteAccountJson(cache: ActorCache): Record<string, unknown> {
  const host = (() => { try { return new URL(cache.id).host; } catch { return "remote"; } })();
  const username = cache.preferred_username ?? cache.id.split("/").pop() ?? "user";
  return {
    id: cache.local_id ?? cache.id,
    username,
    acct: `${username}@${host}`,
    display_name: cache.name ?? username,
    locked: false,
    bot: false,
    discoverable: true,
    group: false,
    created_at: cache.fetched_at,
    note: cache.summary ?? "",
    url: cache.id,
    avatar: cache.icon_url ?? "",
    avatar_static: cache.icon_url ?? "",
    header: "",
    header_static: "",
    followers_count: 0,
    following_count: 0,
    statuses_count: 0,
    last_status_at: null,
    emojis: [],
    fields: []
  };
}

function mediaJson(env: Env, media: Media): Record<string, unknown> {
  const url = mediaUrl(env, media.r2_key);
  return {
    id: media.id,
    type: media.mime_type.startsWith("image/") ? "image" : media.mime_type.startsWith("video/") ? "video" : "unknown",
    url,
    preview_url: url,
    remote_url: null,
    text_url: null,
    meta: {},
    description: media.description,
    blurhash: null
  };
}

async function relationshipFor(env: Env, user: User, target: string): Promise<Record<string, unknown>> {
  const resolved = await resolveAccountTarget(env, target);
  const actorId = resolved?.actorId ?? target;
  const outgoing = await findOutgoingFollow(env, user.id, actorId);
  const incoming = await env.DB.prepare("SELECT * FROM follows WHERE follower_actor = ? AND local_user_id = ?").bind(actorId, user.id).first<Follow>();
  return {
    id: target,
    following: Boolean(outgoing && outgoing.accepted),
    showing_reblogs: true,
    notifying: false,
    languages: null,
    followed_by: Boolean(incoming),
    blocking: false,
    blocked_by: false,
    muting: false,
    muting_notifications: false,
    requested: Boolean(outgoing && !outgoing.accepted),
    domain_blocking: false,
    endorsed: false,
    note: ""
  };
}

function listJson(row: AccountList): Record<string, unknown> {
  return {
    id: row.id,
    title: row.title,
    replies_policy: row.replies_policy,
    exclusive: Boolean(row.exclusive)
  };
}

async function getOwnedList(env: Env, userId: string, listId: string): Promise<AccountList | null> {
  return env.DB.prepare("SELECT * FROM lists WHERE id = ? AND user_id = ?").bind(listId, userId).first<AccountList>();
}

type AccountTarget = { kind: "local"; userId: string; actorId: string } | { kind: "remote"; actorId: string };

async function resolveAccountTarget(env: Env, key: string): Promise<AccountTarget | null> {
  const local = await getUserByIdOrUsername(env, key);
  if (local) return { kind: "local", userId: local.id, actorId: actorUrl(env, local) };

  const byLocalId = await getActorByLocalId(env, key);
  if (byLocalId) return { kind: "remote", actorId: byLocalId.id };

  if (key.startsWith("http://") || key.startsWith("https://")) {
    if (key.startsWith(baseUrl(env))) {
      const match = key.match(/\/users\/([^/?#]+)$/);
      const u = match ? await getUserByUsername(env, match[1]) : null;
      if (u) return { kind: "local", userId: u.id, actorId: actorUrl(env, u) };
    }
    await resolveRemoteActor(env, key);
    return { kind: "remote", actorId: key };
  }
  if (key.includes("@")) {
    const resolved = await resolveAcct(env, key);
    if (!resolved) return null;
    if (resolved.actorId.startsWith(baseUrl(env))) {
      const match = resolved.actorId.match(/\/users\/([^/?#]+)$/);
      const localUser = match ? await getUserByUsername(env, match[1]) : null;
      if (localUser) return { kind: "local", userId: localUser.id, actorId: resolved.actorId };
    }
    await resolveRemoteActor(env, resolved.actorId);
    return { kind: "remote", actorId: resolved.actorId };
  }
  return null;
}

async function resolveAcct(env: Env, acct: string): Promise<{ acct: string; actorId: string; url: string } | null> {
  const trimmed = acct.replace(/^@/, "");
  const [name, host] = trimmed.split("@");
  if (!name) return null;
  const targetHost = host ?? hostFromBaseUrl(env);
  if (targetHost.toLowerCase() === hostFromBaseUrl(env).toLowerCase()) {
    const user = await getUserByUsername(env, name);
    if (!user) return null;
    return { acct: name, actorId: actorUrl(env, user), url: actorUrl(env, user) };
  }
  try {
    const wf = await fetch(`https://${targetHost}/.well-known/webfinger?resource=acct:${name}@${targetHost}`, {
      headers: { accept: "application/jrd+json, application/json" },
      signal: AbortSignal.timeout(10_000)
    });
    if (!wf.ok) return null;
    const doc = await wf.json() as { links?: { rel: string; type?: string; href: string }[] };
    const self = doc.links?.find((link) => link.rel === "self" && (link.type ?? "").includes("activity+json"));
    if (!self?.href) return null;
    return { acct: `${name}@${targetHost}`, actorId: self.href, url: self.href };
  } catch {
    return null;
  }
}

async function listMentionsForStatus(env: Env, statusId: string): Promise<Mention[]> {
  const rows = await env.DB.prepare("SELECT * FROM mentions WHERE status_id = ?").bind(statusId).all<Mention>();
  return rows.results;
}

async function listHashtagsForStatus(env: Env, statusId: string): Promise<string[]> {
  const rows = await env.DB.prepare("SELECT tag FROM hashtags WHERE status_id = ?").bind(statusId).all<{ tag: string }>();
  return rows.results.map((row) => row.tag);
}

function extractMentions(text: string): string[] {
  const re = /@([A-Za-z0-9._-]+(?:@[A-Za-z0-9.-]+\.[A-Za-z]{2,})?)/g;
  const out = new Set<string>();
  let match: RegExpExecArray | null;
  while ((match = re.exec(text)) !== null) out.add(match[1]);
  return [...out];
}

function extractHashtags(text: string): string[] {
  const re = /(?:^|\s)#([\p{L}\p{N}_]{1,64})/gu;
  const out = new Set<string>();
  let match: RegExpExecArray | null;
  while ((match = re.exec(text)) !== null) out.add(match[1].toLowerCase());
  return [...out];
}

function uniqueStrings(values: Array<string | null | undefined>): string[] {
  return [...new Set(values.filter((value): value is string => Boolean(value)))];
}

function uniqueNumbers(values: number[]): number[] {
  return [...new Set(values.filter((value) => Number.isInteger(value) && value >= 0))];
}

function placeholders(count: number): string {
  return Array.from({ length: count }, () => "?").join(",");
}

async function loadUsersByIds(env: Env, userIds: string[]): Promise<User[]> {
  if (userIds.length === 0) return [];
  const rows = await env.DB.prepare(`SELECT * FROM users WHERE id IN (${placeholders(userIds.length)})`).bind(...userIds).all<User>();
  return rows.results;
}

async function loadStatusesByIds(env: Env, statusIds: string[]): Promise<Status[]> {
  if (statusIds.length === 0) return [];
  const rows = await env.DB.prepare(`SELECT * FROM statuses WHERE id IN (${placeholders(statusIds.length)})`).bind(...statusIds).all<Status>();
  return rows.results;
}

async function loadCachedStatusesByObjectIds(env: Env, objectIds: string[]): Promise<CachedStatus[]> {
  if (objectIds.length === 0) return [];
  const rows = await env.DB.prepare(`SELECT * FROM cached_statuses WHERE object_id IN (${placeholders(objectIds.length)})`).bind(...objectIds).all<CachedStatus>();
  return rows.results;
}

async function loadMediaByStatusIds(env: Env, statusIds: string[]): Promise<Map<string, Media[]>> {
  const grouped = new Map<string, Media[]>();
  if (statusIds.length === 0) return grouped;
  const rows = await env.DB.prepare(
    `SELECT * FROM media WHERE status_id IN (${placeholders(statusIds.length)}) ORDER BY created_at ASC`
  ).bind(...statusIds).all<Media>();
  for (const row of rows.results) {
    if (!row.status_id) continue;
    const bucket = grouped.get(row.status_id);
    if (bucket) bucket.push(row);
    else grouped.set(row.status_id, [row]);
  }
  return grouped;
}

async function loadMentionsByStatusIds(env: Env, statusIds: string[]): Promise<Map<string, Mention[]>> {
  const grouped = new Map<string, Mention[]>();
  if (statusIds.length === 0) return grouped;
  const rows = await env.DB.prepare(
    `SELECT * FROM mentions WHERE status_id IN (${placeholders(statusIds.length)})`
  ).bind(...statusIds).all<Mention>();
  for (const row of rows.results) {
    const bucket = grouped.get(row.status_id);
    if (bucket) bucket.push(row);
    else grouped.set(row.status_id, [row]);
  }
  return grouped;
}

async function loadHashtagsByStatusIds(env: Env, statusIds: string[]): Promise<Map<string, string[]>> {
  const grouped = new Map<string, string[]>();
  if (statusIds.length === 0) return grouped;
  const rows = await env.DB.prepare(
    `SELECT status_id, tag FROM hashtags WHERE status_id IN (${placeholders(statusIds.length)})`
  ).bind(...statusIds).all<{ status_id: string; tag: string }>();
  for (const row of rows.results) {
    const bucket = grouped.get(row.status_id);
    if (bucket) bucket.push(row.tag);
    else grouped.set(row.status_id, [row.tag]);
  }
  return grouped;
}

async function loadStatusInteractionSummary(
  env: Env,
  table: "favourites" | "reblogs",
  statusIds: string[],
  viewer: string | null
): Promise<{ countByStatusId: Map<string, number>; viewerMatchedStatusIds: Set<string> }> {
  const countByStatusId = new Map<string, number>();
  const viewerMatchedStatusIds = new Set<string>();
  if (statusIds.length === 0) return { countByStatusId, viewerMatchedStatusIds };

  const viewerSql = viewer ? ", MAX(CASE WHEN actor = ? THEN 1 ELSE 0 END) AS viewer_match" : "";
  const sql = `SELECT status_id, COUNT(*) AS count${viewerSql} FROM ${table} WHERE status_id IN (${placeholders(statusIds.length)}) GROUP BY status_id`;
  const binds = viewer ? [viewer, ...statusIds] : statusIds;
  const rows = await env.DB.prepare(sql).bind(...binds).all<{ status_id: string; count: number; viewer_match?: number }>();
  for (const row of rows.results) {
    countByStatusId.set(row.status_id, row.count);
    if (row.viewer_match) viewerMatchedStatusIds.add(row.status_id);
  }
  return { countByStatusId, viewerMatchedStatusIds };
}

async function loadReplyCountByStatusIds(env: Env, statusIds: string[]): Promise<Map<string, number>> {
  const counts = new Map<string, number>();
  if (statusIds.length === 0) return counts;
  const rows = await env.DB.prepare(
    `SELECT in_reply_to_id AS status_id, COUNT(*) AS count FROM statuses WHERE in_reply_to_id IN (${placeholders(statusIds.length)}) GROUP BY in_reply_to_id`
  ).bind(...statusIds).all<{ status_id: string; count: number }>();
  for (const row of rows.results) counts.set(row.status_id, row.count);
  return counts;
}

async function loadPollSerializationContext(
  env: Env,
  statusIds: string[],
  viewer: string | null
): Promise<Pick<StatusSerializationContext, "pollByStatusId" | "pollOptionsByPollId" | "pollVotesByPollId" | "pollVotersCountByPollId" | "pollOwnVotesByPollId">> {
  const pollByStatusId = new Map<string, Poll>();
  const pollOptionsByPollId = new Map<string, PollOption[]>();
  const pollVotesByPollId = new Map<string, Map<number, number>>();
  const pollVotersCountByPollId = new Map<string, number>();
  const pollOwnVotesByPollId = new Map<string, number[]>();
  if (statusIds.length === 0) return { pollByStatusId, pollOptionsByPollId, pollVotesByPollId, pollVotersCountByPollId, pollOwnVotesByPollId };

  const polls = await env.DB.prepare(`SELECT * FROM polls WHERE status_id IN (${placeholders(statusIds.length)})`).bind(...statusIds).all<Poll>();
  const pollIds = polls.results.map((poll) => poll.id);
  for (const poll of polls.results) pollByStatusId.set(poll.status_id, poll);
  if (pollIds.length === 0) return { pollByStatusId, pollOptionsByPollId, pollVotesByPollId, pollVotersCountByPollId, pollOwnVotesByPollId };

  const optionRows = await env.DB.prepare(
    `SELECT * FROM poll_options WHERE poll_id IN (${placeholders(pollIds.length)}) ORDER BY position ASC`
  ).bind(...pollIds).all<PollOption>();
  for (const option of optionRows.results) {
    const bucket = pollOptionsByPollId.get(option.poll_id);
    if (bucket) bucket.push(option);
    else pollOptionsByPollId.set(option.poll_id, [option]);
  }

  const voteRows = await env.DB.prepare(
    `SELECT poll_id, position, COUNT(*) AS count FROM poll_votes WHERE poll_id IN (${placeholders(pollIds.length)}) GROUP BY poll_id, position`
  ).bind(...pollIds).all<{ poll_id: string; position: number; count: number }>();
  for (const row of voteRows.results) {
    let bucket = pollVotesByPollId.get(row.poll_id);
    if (!bucket) {
      bucket = new Map();
      pollVotesByPollId.set(row.poll_id, bucket);
    }
    bucket.set(row.position, row.count);
  }

  const voterRows = await env.DB.prepare(
    `SELECT poll_id, COUNT(DISTINCT voter_actor) AS count FROM poll_votes WHERE poll_id IN (${placeholders(pollIds.length)}) GROUP BY poll_id`
  ).bind(...pollIds).all<{ poll_id: string; count: number }>();
  for (const row of voterRows.results) pollVotersCountByPollId.set(row.poll_id, row.count);

  if (viewer) {
    const ownRows = await env.DB.prepare(
      `SELECT poll_id, position FROM poll_votes WHERE voter_actor = ? AND poll_id IN (${placeholders(pollIds.length)}) ORDER BY position ASC`
    ).bind(viewer, ...pollIds).all<{ poll_id: string; position: number }>();
    for (const row of ownRows.results) {
      const bucket = pollOwnVotesByPollId.get(row.poll_id);
      if (bucket) bucket.push(row.position);
      else pollOwnVotesByPollId.set(row.poll_id, [row.position]);
    }
  }

  return { pollByStatusId, pollOptionsByPollId, pollVotesByPollId, pollVotersCountByPollId, pollOwnVotesByPollId };
}

async function serializeNotifications(env: Env, notifications: Notification[], request: Request): Promise<Record<string, unknown>[]> {
  if (notifications.length === 0) return [];

  const notificationStatusIds = uniqueStrings(notifications.map((notification) => notification.status_id));
  const viewer = await loadStatusViewer(request, env);
  const statuses = await loadStatusesByIds(env, notificationStatusIds);
  const localStatusIds = new Set(statuses.map((status) => status.id));
  const cachedStatuses = await loadCachedStatusesByObjectIds(env, notificationStatusIds.filter((statusId) => !localStatusIds.has(statusId)));
  const visibleStatuses = await filterStatusesForViewer(env, statuses, viewer);
  const visibleCachedStatuses = await filterCachedStatusesForViewer(env, cachedStatuses, viewer);
  const serializedStatuses = await serializeStatuses(env, visibleStatuses, request);
  const serializedCachedStatuses = await Promise.all(visibleCachedStatuses.map((row) => cachedStatusToMastodon(env, row)));
  const serializedStatusById = new Map(
    [...serializedStatuses, ...serializedCachedStatuses].map((item) => [String(item.id), item])
  );

  const remoteActorIds = uniqueStrings(
    notifications.map((notification) => notification.actor).filter((actorId) => !actorId.startsWith(baseUrl(env)))
  );
  const remoteAccounts = new Map<string, Record<string, unknown>>();
  const remoteResults = await Promise.all(remoteActorIds.map(async (actorId) => [actorId, await resolveRemoteActor(env, actorId)] as const));
  for (const [actorId, actorCache] of remoteResults) {
    remoteAccounts.set(actorId, actorCache ? remoteAccountJson(actorCache) : { id: actorId, acct: actorId, username: actorId });
  }

  const localAccounts = new Map<string, Record<string, unknown>>();
  const out: Record<string, unknown>[] = [];
  for (const notification of notifications) {
    let account = localAccounts.get(notification.actor) ?? remoteAccounts.get(notification.actor);
    if (!account) {
      const match = notification.actor.match(/\/users\/([^/?#]+)$/);
      const localUser = match ? await getUserByUsername(env, match[1]) : null;
      account = localUser ? await accountJson(env, localUser) : { id: notification.actor, acct: notification.actor };
      localAccounts.set(notification.actor, account);
    }
    out.push({
      id: notification.id,
      type: notification.type,
      created_at: notification.created_at,
      account,
      status: notification.status_id ? serializedStatusById.get(notification.status_id) ?? null : null
    });
  }
  return out;
}

function pagedAppend(where: string[], binds: unknown[], url: URL): void {
  const maxId = url.searchParams.get("max_id");
  if (maxId) {
    where.push("created_at < (SELECT created_at FROM statuses WHERE id = ?)");
    binds.push(maxId);
  }
  const sinceId = url.searchParams.get("since_id");
  if (sinceId) {
    where.push("created_at > (SELECT created_at FROM statuses WHERE id = ?)");
    binds.push(sinceId);
  }
  const minId = url.searchParams.get("min_id");
  if (minId) {
    where.push("created_at > (SELECT created_at FROM statuses WHERE id = ?)");
    binds.push(minId);
  }
}

function pagedAppendForTable(where: string[], binds: unknown[], url: URL, table: "follows" | "outgoing_follows"): void {
  const maxId = url.searchParams.get("max_id");
  if (maxId) {
    where.push(`created_at < (SELECT created_at FROM ${table} WHERE id = ?)`);
    binds.push(maxId);
  }
  const sinceId = url.searchParams.get("since_id");
  if (sinceId) {
    where.push(`created_at > (SELECT created_at FROM ${table} WHERE id = ?)`);
    binds.push(sinceId);
  }
  const minId = url.searchParams.get("min_id");
  if (minId) {
    where.push(`created_at > (SELECT created_at FROM ${table} WHERE id = ?)`);
    binds.push(minId);
  }
}

function withPagination(response: Response, request: Request, ids: string[]): Response {
  if (ids.length === 0) return response;
  const url = new URL(request.url);
  const nextUrl = new URL(url);
  nextUrl.searchParams.set("max_id", ids[ids.length - 1]);
  const prevUrl = new URL(url);
  prevUrl.searchParams.set("since_id", ids[0]);
  const link = `<${nextUrl}>; rel="next", <${prevUrl}>; rel="prev"`;
  const headers = new Headers(response.headers);
  headers.set("link", link);
  return new Response(response.body, { status: response.status, statusText: response.statusText, headers });
}

function isStatusVisibility(value: string): value is StatusVisibility {
  return VALID_STATUS_VISIBILITIES.has(value);
}

async function loadStatusViewer(request: Request, env: Env): Promise<StatusViewer> {
  return statusViewerForUser(env, await viewerUser(request, env));
}

function statusViewerForUser(env: Env, user: User | null): StatusViewer {
  return {
    user,
    actor: user ? actorUrl(env, user) : null,
    followsByOwnerId: new Map(),
    remoteFollowsByActorId: new Map()
  };
}

async function visibleStatusWhereForOwner(env: Env, ownerUserId: string, viewer: StatusViewer, column = "visibility"): Promise<string | null> {
  if (viewer.user?.id === ownerUserId) return null;
  if (await viewerFollowsOwner(env, viewer, ownerUserId)) return `${column} IN ('public', 'unlisted', 'private')`;
  return `${column} IN ('public', 'unlisted')`;
}

async function visibleStatusOrNull(env: Env, statusId: string, viewer: StatusViewer): Promise<Status | null> {
  const status = await getStatus(env, statusId);
  if (!status) return null;
  return await canViewerViewStatus(env, status, viewer) ? status : null;
}

async function filterStatusesForViewer(env: Env, statuses: Status[], viewer: StatusViewer): Promise<Status[]> {
  const visible: Status[] = [];
  for (const status of statuses) {
    if (await canViewerViewStatus(env, status, viewer)) visible.push(status);
  }
  return visible;
}

async function filterCachedStatusesForViewer(env: Env, statuses: CachedStatus[], viewer: StatusViewer): Promise<CachedStatus[]> {
  const visible: CachedStatus[] = [];
  for (const status of statuses) {
    if (await canViewerViewCachedStatus(env, status, viewer)) visible.push(status);
  }
  return visible;
}

async function canViewerViewStatus(env: Env, status: Status, viewer: StatusViewer): Promise<boolean> {
  if (status.visibility === "public" || status.visibility === "unlisted") return true;
  if (viewer.user?.id === status.user_id) return true;
  if (status.visibility === "private") return viewerFollowsOwner(env, viewer, status.user_id);
  return false;
}

async function canViewerViewCachedStatus(env: Env, status: CachedStatus, viewer: StatusViewer): Promise<boolean> {
  if (status.visibility === "public" || status.visibility === "unlisted") return true;
  if (!viewer.actor) return false;
  if (status.visibility === "private") return viewerFollowsRemoteActor(env, viewer, status.actor);
  return parseCachedJson<string>(status.local_recipients_json).includes(viewer.actor);
}

async function viewerFollowsOwner(env: Env, viewer: StatusViewer, ownerUserId: string): Promise<boolean> {
  if (!viewer.actor) return false;
  const cached = viewer.followsByOwnerId.get(ownerUserId);
  if (cached !== undefined) return cached;
  const row = await env.DB.prepare(
    "SELECT 1 AS hit FROM follows WHERE local_user_id = ? AND follower_actor = ? AND accepted = 1 LIMIT 1"
  ).bind(ownerUserId, viewer.actor).first<{ hit: number }>();
  const follows = Boolean(row?.hit);
  viewer.followsByOwnerId.set(ownerUserId, follows);
  return follows;
}

async function viewerFollowsRemoteActor(env: Env, viewer: StatusViewer, actorId: string): Promise<boolean> {
  if (!viewer.user) return false;
  const cached = viewer.remoteFollowsByActorId.get(actorId);
  if (cached !== undefined) return cached;
  const row = await env.DB.prepare(
    "SELECT 1 AS hit FROM outgoing_follows WHERE local_user_id = ? AND target_actor = ? AND accepted = 1 LIMIT 1"
  ).bind(viewer.user.id, actorId).first<{ hit: number }>();
  const follows = Boolean(row?.hit);
  viewer.remoteFollowsByActorId.set(actorId, follows);
  return follows;
}

async function viewerUser(request: Request, env: Env): Promise<User | null> {
  const auth = request.headers.get("authorization") ?? "";
  const token = auth.match(/^Bearer\s+(.+)$/i)?.[1];
  if (!token) return null;
  const session = await loadSession(env, token);
  if (!session) return null;
  if (!hasAnyScope(session, ["read"])) return null;
  return getUserById(env, session.userId);
}

async function loadBookmarkedStatusIds(env: Env, userId: string, statusIds: string[]): Promise<Set<string>> {
  if (statusIds.length === 0) return new Set();
  const placeholders = statusIds.map(() => "?").join(",");
  const rows = await env.DB.prepare(
    `SELECT status_id FROM bookmarks WHERE user_id = ? AND status_id IN (${placeholders})`
  ).bind(userId, ...statusIds).all<{ status_id: string }>();
  return new Set(rows.results.map((row) => row.status_id));
}

async function loadPinnedStatusIds(env: Env, userId: string, statusIds: string[]): Promise<Set<string>> {
  if (statusIds.length === 0) return new Set();
  const placeholders = statusIds.map(() => "?").join(",");
  const rows = await env.DB.prepare(
    `SELECT status_id FROM pinned_statuses WHERE user_id = ? AND status_id IN (${placeholders})`
  ).bind(userId, ...statusIds).all<{ status_id: string }>();
  return new Set(rows.results.map((row) => row.status_id));
}

async function requireUser(request: Request, env: Env, scopes = defaultRequiredScopes(request)): Promise<User> {
  const auth = request.headers.get("authorization") ?? "";
  const token = auth.match(/^Bearer\s+(.+)$/i)?.[1];
  if (!token) throw new HttpError(401, "The access token is invalid");
  const session = await loadSession(env, token);
  if (!session) throw new HttpError(401, "The access token is invalid");
  requireScopes(session, scopes);
  const user = await getUserById(env, session.userId);
  if (!user) throw new HttpError(401, "The access token is invalid");
  return user;
}

async function loadSession(env: Env, token: string): Promise<Session | null> {
  const session = await env.KV.get<Session>(`token:${token}`, "json");
  if (session) return session;

  const row = await getOAuthToken(env, token);
  if (!row) return null;
  const createdAt = Date.parse(row.created_at);
  if (!Number.isFinite(createdAt) || Date.now() - createdAt > TOKEN_TTL_SECONDS * 1000) {
    await deleteOAuthToken(env, token).catch(() => undefined);
    return null;
  }
  const restored = { userId: row.user_id, appId: row.app_id, scopes: row.scopes } satisfies Session;
  await Promise.allSettled([
    env.KV.put(`token:${token}`, JSON.stringify(restored), { expirationTtl: TOKEN_TTL_SECONDS }),
    touchOAuthToken(env, token)
  ]);
  return restored;
}

function defaultRequiredScopes(request: Request): string[] {
  return request.method.toUpperCase() === "GET" ? ["read"] : ["write"];
}

function requestedScopesWithinApp(requested: string, appScopes: string): string {
  const requestedScopes = normalizeScopes(requested || appScopes);
  const allowed = normalizeScopes(appScopes);
  if (requestedScopes.length === 0) return appScopes;
  if (requestedScopes.every((scope) => scopeAllowed(scope, allowed))) return requestedScopes.join(" ");
  throw new HttpError(400, "invalid_scope");
}

function requireScopes(session: Session, required: string[]): void {
  if (!hasAllScopes(session, required)) throw new HttpError(403, "insufficient_scope");
}

function hasAllScopes(session: Session, required: string[]): boolean {
  return required.every((scope) => hasAnyScope(session, [scope]));
}

function hasAnyScope(session: Session, required: string[]): boolean {
  const granted = normalizeScopes(session.scopes);
  return required.some((scope) => scopeAllowed(scope, granted));
}

function scopeAllowed(required: string, granted: string[]): boolean {
  if (granted.includes(required)) return true;
  if (!required.includes(":") && granted.some((scope) => scope.startsWith(`${required}:`))) return true;
  const root = required.split(":")[0];
  return granted.includes(root);
}

function normalizeScopes(scopes: string): string[] {
  return [...new Set(scopes.split(/\s+/).map((scope) => scope.trim()).filter(Boolean))];
}

export { requireUser };