atbb / apps / appview / src / routes / admin.ts

import { Hono } from "hono";
import type { AppContext } from "../lib/app-context.js";
import type { Variables } from "../types.js";
import { requireAuth } from "../middleware/auth.js";
import { requirePermission, requireAnyPermission, getUserRole } from "../middleware/permissions.js";
import { memberships, roles, rolePermissions, users, forums, backfillProgress, backfillErrors, categories, boards, posts, modActions, themes, themePolicies } from "@atbb/db";
import { eq, and, sql, asc, desc, count, inArray, or } from "drizzle-orm";
import { isProgrammingError } from "../lib/errors.js";
import { BackfillStatus } from "../lib/backfill-manager.js";
import { CursorManager } from "../lib/cursor-manager.js";
import {
  handleRouteError,
  safeParseJsonBody,
  getForumAgentOrError,
} from "../lib/route-errors.js";
import { TID } from "@atproto/common-web";
import { parseBigIntParam, serializeBigInt, serializeDate } from "./helpers.js";
import { sanitizeCssOverrides } from "@atbb/css-sanitizer";

export function createAdminRoutes(ctx: AppContext) {
  const app = new Hono<{ Variables: Variables }>();

  /**
   * POST /api/admin/members/:did/role
   *
   * Assign a role to a forum member.
   */
  app.post(
    "/members/:did/role",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageRoles"),
    async (c) => {
      const targetDid = c.req.param("did");
      const user = c.get("user")!;

      // Parse and validate request body
      const { body, error: parseError } = await safeParseJsonBody(c);
      if (parseError) return parseError;

      const { roleUri } = body;

      if (typeof roleUri !== "string") {
        return c.json({ error: "roleUri is required and must be a string" }, 400);
      }

      // Validate roleUri format
      if (!roleUri.startsWith("at://") || !roleUri.includes("/space.atbb.forum.role/")) {
        return c.json({ error: "Invalid roleUri format" }, 400);
      }

      // Extract role rkey from roleUri
      const roleRkey = roleUri.split("/").pop();
      if (!roleRkey) {
        return c.json({ error: "Invalid roleUri format" }, 400);
      }

      try {
        // Validate role exists
        const [role] = await ctx.db
          .select()
          .from(roles)
          .where(
            and(
              eq(roles.did, ctx.config.forumDid),
              eq(roles.rkey, roleRkey)
            )
          )
          .limit(1);

        if (!role) {
          return c.json({ error: "Role not found" }, 404);
        }

        // Priority check: Can't assign role with equal or higher authority
        const assignerRole = await getUserRole(ctx, user.did);
        if (!assignerRole) {
          return c.json({ error: "You do not have a role assigned" }, 403);
        }

        if (role.priority <= assignerRole.priority) {
          return c.json({
            error: "Cannot assign role with equal or higher authority",
          }, 403);
        }

        // Get target user's membership
        const [membership] = await ctx.db
          .select()
          .from(memberships)
          .where(eq(memberships.did, targetDid))
          .limit(1);

        if (!membership) {
          return c.json({ error: "User is not a member of this forum" }, 404);
        }

        // Fetch forum CID for membership record
        const [forum] = await ctx.db
          .select({ cid: forums.cid })
          .from(forums)
          .where(eq(forums.did, ctx.config.forumDid))
          .limit(1);

        if (!forum) {
          return c.json({ error: "Forum record not found in database" }, 500);
        }

        // Get ForumAgent for PDS write operations
        const { agent, error: agentError } = getForumAgentOrError(ctx, c, "POST /api/admin/members/:did/role");
        if (agentError) return agentError;

        try {
          // Update membership record on user's PDS using ForumAgent
          await agent.com.atproto.repo.putRecord({
            repo: targetDid,
            collection: "space.atbb.membership",
            rkey: membership.rkey,
            record: {
              $type: "space.atbb.membership",
              forum: { forum: { uri: membership.forumUri, cid: forum.cid } },
              role: { role: { uri: roleUri, cid: role.cid } },
              joinedAt: membership.joinedAt?.toISOString(),
              createdAt: membership.createdAt.toISOString(),
            },
          });

          return c.json({
            success: true,
            roleAssigned: role.name,
            targetDid,
          });
        } catch (error) {
          return handleRouteError(c, error, "Failed to assign role", {
            operation: "POST /api/admin/members/:did/role",
            logger: ctx.logger,
            targetDid,
            roleUri,
          });
        }
      } catch (error) {
        return handleRouteError(c, error, "Failed to process role assignment", {
          operation: "POST /api/admin/members/:did/role",
          logger: ctx.logger,
          targetDid,
          roleUri,
        });
      }
    }
  );

  /**
   * GET /api/admin/roles
   *
   * List all available roles for the forum.
   */
  app.get(
    "/roles",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageRoles"),
    async (c) => {
      try {
        const rolesList = await ctx.db
          .select({
            id: roles.id,
            name: roles.name,
            description: roles.description,
            priority: roles.priority,
            rkey: roles.rkey,
            did: roles.did,
          })
          .from(roles)
          .where(eq(roles.did, ctx.config.forumDid))
          .orderBy(asc(roles.priority));

        const rolesWithPermissions = await Promise.all(
          rolesList.map(async (role) => {
            const perms = await ctx.db
              .select({ permission: rolePermissions.permission })
              .from(rolePermissions)
              .where(eq(rolePermissions.roleId, role.id));
            return {
              id: role.id.toString(),
              name: role.name,
              description: role.description,
              permissions: perms.map((p) => p.permission),
              priority: role.priority,
              uri: `at://${role.did}/space.atbb.forum.role/${role.rkey}`,
            };
          })
        );

        return c.json({ roles: rolesWithPermissions });
      } catch (error) {
        return handleRouteError(c, error, "Failed to retrieve roles", {
          operation: "GET /api/admin/roles",
          logger: ctx.logger,
        });
      }
    }
  );

  /**
   * GET /api/admin/members
   *
   * List all forum members with their assigned roles.
   */
  app.get(
    "/members",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageMembers"),
    async (c) => {
      try {
        const membersList = await ctx.db
          .select({
            did: memberships.did,
            handle: users.handle,
            role: roles.name,
            roleUri: memberships.roleUri,
            joinedAt: memberships.joinedAt,
          })
          .from(memberships)
          .leftJoin(users, eq(memberships.did, users.did))
          .leftJoin(
            roles,
            sql`${memberships.roleUri} LIKE 'at://' || ${roles.did} || '/space.atbb.forum.role/' || ${roles.rkey}`
          )
          .where(eq(memberships.forumUri, `at://${ctx.config.forumDid}/space.atbb.forum.forum/self`))
          .orderBy(asc(roles.priority), asc(users.handle))
          .limit(100);

        return c.json({
          members: membersList.map(member => ({
            did: member.did,
            handle: member.handle || member.did,
            role: member.role || "Guest",
            roleUri: member.roleUri,
            joinedAt: member.joinedAt?.toISOString(),
          })),
          isTruncated: membersList.length === 100,
        });
      } catch (error) {
        return handleRouteError(c, error, "Failed to retrieve members", {
          operation: "GET /api/admin/members",
          logger: ctx.logger,
        });
      }
    }
  );


  /**
   * GET /api/admin/members/me
   *
   * Returns the calling user's own membership, role name, and permissions.
   * Any authenticated user may call this — no special permission required.
   * Returns 404 if the user has no membership record for this forum.
   */
  app.get("/members/me", requireAuth(ctx), async (c) => {
    const user = c.get("user")!;

    try {
      const forumUri = `at://${ctx.config.forumDid}/space.atbb.forum.forum/self`;
      const [member] = await ctx.db
        .select({
          did: memberships.did,
          handle: users.handle,
          roleUri: memberships.roleUri,
          roleName: roles.name,
          roleId: roles.id,
        })
        .from(memberships)
        .leftJoin(users, eq(memberships.did, users.did))
        .leftJoin(
          roles,
          sql`${memberships.roleUri} LIKE 'at://' || ${roles.did} || '/space.atbb.forum.role/' || ${roles.rkey}`
        )
        .where(
          and(
            eq(memberships.did, user.did),
            eq(memberships.forumUri, forumUri)
          )
        )
        .limit(1);

      if (!member) {
        return c.json({ error: "Membership not found" }, 404);
      }

      let permissions: string[] = [];
      if (member.roleId) {
        const perms = await ctx.db
          .select({ permission: rolePermissions.permission })
          .from(rolePermissions)
          .where(eq(rolePermissions.roleId, member.roleId));
        permissions = perms.map((p) => p.permission);
      }

      return c.json({
        did: member.did,
        handle: member.handle || user.did,
        role: member.roleName || "Guest",
        roleUri: member.roleUri,
        permissions,
      });
    } catch (error) {
      return handleRouteError(c, error, "Failed to retrieve your membership", {
        operation: "GET /api/admin/members/me",
        logger: ctx.logger,
        did: user.did,
      });
    }
  });

  /**
   * POST /api/admin/backfill
   *
   * Trigger a backfill operation. Runs asynchronously.
   * Returns 202 Accepted immediately.
   * Use ?force=catch_up or ?force=full_sync to override gap detection.
   */
  app.post(
    "/backfill",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageForum"),
    async (c) => {
      const backfillManager = ctx.backfillManager;
      if (!backfillManager) {
        return c.json({ error: "Backfill manager not available" }, 503);
      }

      if (backfillManager.getIsRunning()) {
        return c.json({ error: "A backfill is already in progress" }, 409);
      }

      // Determine backfill type
      const force = c.req.query("force");
      let type: BackfillStatus;

      if (force === "catch_up" || force === "full_sync") {
        type = force === "catch_up" ? BackfillStatus.CatchUp : BackfillStatus.FullSync;
      } else {
        try {
          const cursor = await new CursorManager(ctx.db, ctx.logger).load();
          type = await backfillManager.checkIfNeeded(cursor);
        } catch (error) {
          if (isProgrammingError(error)) throw error;
          ctx.logger.error("Failed to check backfill status", {
            event: "backfill.admin_trigger.check_failed",
            error: error instanceof Error ? error.message : String(error),
          });
          return c.json({ error: "Failed to check backfill status. Please try again later." }, 500);
        }

        if (type === BackfillStatus.NotNeeded) {
          return c.json({
            message: "No backfill needed. Use ?force=catch_up or ?force=full_sync to override.",
          }, 200);
        }
      }

      // Create progress row first so we can return the ID immediately in the 202 response
      let progressId: bigint;
      try {
        progressId = await backfillManager.prepareBackfillRow(type);
      } catch (error) {
        if (isProgrammingError(error)) throw error;
        ctx.logger.error("Failed to create backfill row", {
          event: "backfill.admin_trigger.create_row_failed",
          error: error instanceof Error ? error.message : String(error),
        });
        return c.json({ error: "Failed to start backfill. Please try again later." }, 500);
      }

      // Fire and forget — don't await so response is immediate
      backfillManager.performBackfill(type, progressId).catch((err) => {
        ctx.logger.error("Background backfill failed", {
          event: "backfill.admin_trigger_failed",
          backfillId: progressId.toString(),
          error: err instanceof Error ? err.message : String(err),
        });
      });

      return c.json({
        message: "Backfill started",
        type,
        status: "in_progress",
        id: progressId.toString(),
      }, 202);
    }
  );

  /**
   * GET /api/admin/backfill/:id
   *
   * Get status and progress for a specific backfill by ID.
   */
  app.get(
    "/backfill/:id",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageForum"),
    async (c) => {
      const id = c.req.param("id");
      if (!/^\d+$/.test(id)) {
        return c.json({ error: "Invalid backfill ID" }, 400);
      }
      const parsedId = BigInt(id);

      try {
        const [row] = await ctx.db
          .select()
          .from(backfillProgress)
          .where(eq(backfillProgress.id, parsedId))
          .limit(1);

        if (!row) {
          return c.json({ error: "Backfill not found" }, 404);
        }

        const [errorCount] = await ctx.db
          .select({ count: count() })
          .from(backfillErrors)
          .where(eq(backfillErrors.backfillId, row.id));

        return c.json({
          id: row.id.toString(),
          status: row.status,
          type: row.backfillType,
          didsTotal: row.didsTotal,
          didsProcessed: row.didsProcessed,
          recordsIndexed: row.recordsIndexed,
          errorCount: errorCount?.count ?? 0,
          startedAt: row.startedAt.toISOString(),
          completedAt: row.completedAt?.toISOString() ?? null,
          errorMessage: row.errorMessage,
        });
      } catch (error) {
        return handleRouteError(c, error, "Failed to fetch backfill progress", {
          operation: "GET /api/admin/backfill/:id",
          logger: ctx.logger,
          id,
        });
      }
    }
  );

  /**
   * GET /api/admin/backfill/:id/errors
   *
   * List per-DID errors for a specific backfill.
   */
  app.get(
    "/backfill/:id/errors",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageForum"),
    async (c) => {
      const id = c.req.param("id");
      if (!/^\d+$/.test(id)) {
        return c.json({ error: "Invalid backfill ID" }, 400);
      }
      const parsedId = BigInt(id);

      try {
        const errors = await ctx.db
          .select()
          .from(backfillErrors)
          .where(eq(backfillErrors.backfillId, parsedId))
          .orderBy(asc(backfillErrors.createdAt))
          .limit(1000);

        return c.json({
          errors: errors.map((e) => ({
            id: e.id.toString(),
            did: e.did,
            collection: e.collection,
            errorMessage: e.errorMessage,
            createdAt: e.createdAt.toISOString(),
          })),
        });
      } catch (error) {
        return handleRouteError(c, error, "Failed to fetch backfill errors", {
          operation: "GET /api/admin/backfill/:id/errors",
          logger: ctx.logger,
          id,
        });
      }
    }
  );

  /**
   * POST /api/admin/categories
   *
   * Create a new forum category. Writes space.atbb.forum.category to Forum DID's PDS.
   * The firehose indexer creates the DB row asynchronously.
   */
  app.post(
    "/categories",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageCategories"),
    async (c) => {
      const { body, error: parseError } = await safeParseJsonBody(c);
      if (parseError) return parseError;

      const { name, description, sortOrder } = body;

      if (typeof name !== "string" || name.trim().length === 0) {
        return c.json({ error: "name is required and must be a non-empty string" }, 400);
      }

      const { agent, error: agentError } = getForumAgentOrError(ctx, c, "POST /api/admin/categories");
      if (agentError) return agentError;

      const rkey = TID.nextStr();
      const now = new Date().toISOString();

      try {
        const result = await agent.com.atproto.repo.putRecord({
          repo: ctx.config.forumDid,
          collection: "space.atbb.forum.category",
          rkey,
          record: {
            $type: "space.atbb.forum.category",
            name: name.trim(),
            ...(typeof description === "string" && { description: description.trim() }),
            ...(Number.isInteger(sortOrder) && sortOrder >= 0 && { sortOrder }),
            createdAt: now,
          },
        });

        return c.json({ uri: result.data.uri, cid: result.data.cid }, 201);
      } catch (error) {
        return handleRouteError(c, error, "Failed to create category", {
          operation: "POST /api/admin/categories",
          logger: ctx.logger,
        });
      }
    }
  );

  /**
   * PUT /api/admin/categories/:id
   *
   * Update an existing category. Fetches existing rkey from DB, calls putRecord
   * with updated fields preserving the original createdAt.
   * The firehose indexer updates the DB row asynchronously.
   */
  app.put(
    "/categories/:id",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageCategories"),
    async (c) => {
      const idParam = c.req.param("id");
      const id = parseBigIntParam(idParam);
      if (id === null) {
        return c.json({ error: "Invalid category ID" }, 400);
      }

      const { body, error: parseError } = await safeParseJsonBody(c);
      if (parseError) return parseError;

      const { name, description, sortOrder } = body;

      if (typeof name !== "string" || name.trim().length === 0) {
        return c.json({ error: "name is required and must be a non-empty string" }, 400);
      }

      let category: typeof categories.$inferSelect;
      try {
        const [row] = await ctx.db
          .select()
          .from(categories)
          .where(and(eq(categories.id, id), eq(categories.did, ctx.config.forumDid)))
          .limit(1);

        if (!row) {
          return c.json({ error: "Category not found" }, 404);
        }
        category = row;
      } catch (error) {
        return handleRouteError(c, error, "Failed to look up category", {
          operation: "PUT /api/admin/categories/:id",
          logger: ctx.logger,
          id: idParam,
        });
      }

      const { agent, error: agentError } = getForumAgentOrError(ctx, c, "PUT /api/admin/categories/:id");
      if (agentError) return agentError;

      // putRecord is a full replacement — fall back to existing values for
      // optional fields not provided in the request body, to avoid data loss.
      const resolvedDescription = typeof description === "string"
        ? description.trim()
        : category.description;
      const resolvedSortOrder = (Number.isInteger(sortOrder) && sortOrder >= 0)
        ? sortOrder
        : category.sortOrder;

      try {
        const result = await agent.com.atproto.repo.putRecord({
          repo: ctx.config.forumDid,
          collection: "space.atbb.forum.category",
          rkey: category.rkey,
          record: {
            $type: "space.atbb.forum.category",
            name: name.trim(),
            ...(resolvedDescription != null && { description: resolvedDescription }),
            ...(resolvedSortOrder != null && { sortOrder: resolvedSortOrder }),
            createdAt: category.createdAt.toISOString(),
          },
        });

        return c.json({ uri: result.data.uri, cid: result.data.cid });
      } catch (error) {
        return handleRouteError(c, error, "Failed to update category", {
          operation: "PUT /api/admin/categories/:id",
          logger: ctx.logger,
          id: idParam,
        });
      }
    }
  );

  /**
   * DELETE /api/admin/categories/:id
   *
   * Delete a category. Pre-flight: refuses with 409 if any boards reference this
   * category in the DB. If clear, calls deleteRecord on the Forum DID's PDS.
   * The firehose indexer removes the DB row asynchronously.
   */
  app.delete(
    "/categories/:id",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageCategories"),
    async (c) => {
      const idParam = c.req.param("id");
      const id = parseBigIntParam(idParam);
      if (id === null) {
        return c.json({ error: "Invalid category ID" }, 400);
      }

      let category: typeof categories.$inferSelect;
      try {
        const [row] = await ctx.db
          .select()
          .from(categories)
          .where(and(eq(categories.id, id), eq(categories.did, ctx.config.forumDid)))
          .limit(1);

        if (!row) {
          return c.json({ error: "Category not found" }, 404);
        }
        category = row;
      } catch (error) {
        return handleRouteError(c, error, "Failed to look up category", {
          operation: "DELETE /api/admin/categories/:id",
          logger: ctx.logger,
          id: idParam,
        });
      }

      // Pre-flight: refuse if any boards reference this category
      try {
        const [boardCount] = await ctx.db
          .select({ count: count() })
          .from(boards)
          .where(eq(boards.categoryId, id));

        if (boardCount && boardCount.count > 0) {
          return c.json(
            { error: "Cannot delete category with boards. Remove all boards first." },
            409
          );
        }
      } catch (error) {
        return handleRouteError(c, error, "Failed to check category boards", {
          operation: "DELETE /api/admin/categories/:id",
          logger: ctx.logger,
          id: idParam,
        });
      }

      const { agent, error: agentError } = getForumAgentOrError(ctx, c, "DELETE /api/admin/categories/:id");
      if (agentError) return agentError;

      try {
        await agent.com.atproto.repo.deleteRecord({
          repo: ctx.config.forumDid,
          collection: "space.atbb.forum.category",
          rkey: category.rkey,
        });

        return c.json({ success: true });
      } catch (error) {
        return handleRouteError(c, error, "Failed to delete category", {
          operation: "DELETE /api/admin/categories/:id",
          logger: ctx.logger,
          id: idParam,
        });
      }
    }
  );

  /**
   * POST /api/admin/boards
   *
   * Create a new forum board within a category. Fetches the category's CID from DB
   * to build the categoryRef strongRef required by the lexicon. Writes
   * space.atbb.forum.board to the Forum DID's PDS via putRecord.
   * The firehose indexer creates the DB row asynchronously.
   */
  app.post(
    "/boards",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageCategories"),
    async (c) => {
      const { body, error: parseError } = await safeParseJsonBody(c);
      if (parseError) return parseError;

      const { name, description, sortOrder, categoryUri } = body;

      if (typeof name !== "string" || name.trim().length === 0) {
        return c.json({ error: "name is required and must be a non-empty string" }, 400);
      }

      if (typeof categoryUri !== "string" || !categoryUri.startsWith("at://")) {
        return c.json({ error: "categoryUri is required and must be a valid AT URI" }, 400);
      }

      // Derive rkey from the categoryUri to look up the category in the DB
      const categoryRkey = categoryUri.split("/").pop();

      let category: typeof categories.$inferSelect;
      try {
        const [row] = await ctx.db
          .select()
          .from(categories)
          .where(
            and(
              eq(categories.did, ctx.config.forumDid),
              eq(categories.rkey, categoryRkey ?? "")
            )
          )
          .limit(1);

        if (!row) {
          return c.json({ error: "Category not found" }, 404);
        }
        category = row;
      } catch (error) {
        return handleRouteError(c, error, "Failed to look up category", {
          operation: "POST /api/admin/boards",
          logger: ctx.logger,
          categoryUri,
        });
      }

      const { agent, error: agentError } = getForumAgentOrError(ctx, c, "POST /api/admin/boards");
      if (agentError) return agentError;

      const rkey = TID.nextStr();
      const now = new Date().toISOString();

      try {
        const result = await agent.com.atproto.repo.putRecord({
          repo: ctx.config.forumDid,
          collection: "space.atbb.forum.board",
          rkey,
          record: {
            $type: "space.atbb.forum.board",
            name: name.trim(),
            ...(typeof description === "string" && { description: description.trim() }),
            ...(Number.isInteger(sortOrder) && sortOrder >= 0 && { sortOrder }),
            category: { category: { uri: categoryUri, cid: category.cid } },
            createdAt: now,
          },
        });

        return c.json({ uri: result.data.uri, cid: result.data.cid }, 201);
      } catch (error) {
        return handleRouteError(c, error, "Failed to create board", {
          operation: "POST /api/admin/boards",
          logger: ctx.logger,
          categoryUri,
        });
      }
    }
  );

  /**
   * PUT /api/admin/boards/:id
   *
   * Update an existing board's name, description, and sortOrder.
   * Fetches existing rkey + categoryUri from DB, then fetches category CID,
   * then putRecord with updated fields preserving the original categoryRef and createdAt.
   * Category cannot be changed on edit (no reparenting).
   * The firehose indexer updates the DB row asynchronously.
   */
  app.put(
    "/boards/:id",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageCategories"),
    async (c) => {
      const idParam = c.req.param("id");
      const id = parseBigIntParam(idParam);
      if (id === null) {
        return c.json({ error: "Invalid board ID" }, 400);
      }

      const { body, error: parseError } = await safeParseJsonBody(c);
      if (parseError) return parseError;

      const { name, description, sortOrder } = body;

      if (typeof name !== "string" || name.trim().length === 0) {
        return c.json({ error: "name is required and must be a non-empty string" }, 400);
      }

      let board: typeof boards.$inferSelect;
      try {
        const [row] = await ctx.db
          .select()
          .from(boards)
          .where(and(eq(boards.id, id), eq(boards.did, ctx.config.forumDid)))
          .limit(1);

        if (!row) {
          return c.json({ error: "Board not found" }, 404);
        }
        board = row;
      } catch (error) {
        return handleRouteError(c, error, "Failed to look up board", {
          operation: "PUT /api/admin/boards/:id",
          logger: ctx.logger,
          id: idParam,
        });
      }

      // Fetch category CID to rebuild the categoryRef strongRef.
      // Always fetch fresh — the category's CID can change after category edits.
      let categoryCid: string;
      try {
        const categoryRkey = board.categoryUri.split("/").pop() ?? "";
        const [cat] = await ctx.db
          .select({ cid: categories.cid })
          .from(categories)
          .where(
            and(
              eq(categories.did, ctx.config.forumDid),
              eq(categories.rkey, categoryRkey)
            )
          )
          .limit(1);

        if (!cat) {
          return c.json({ error: "Category not found" }, 404);
        }
        categoryCid = cat.cid;
      } catch (error) {
        return handleRouteError(c, error, "Failed to look up category", {
          operation: "PUT /api/admin/boards/:id",
          logger: ctx.logger,
          id: idParam,
        });
      }

      const { agent, error: agentError } = getForumAgentOrError(ctx, c, "PUT /api/admin/boards/:id");
      if (agentError) return agentError;

      // putRecord is a full replacement — fall back to existing values for
      // optional fields not provided in the request body, to avoid data loss.
      const resolvedDescription = typeof description === "string"
        ? description.trim()
        : board.description;
      const resolvedSortOrder = (Number.isInteger(sortOrder) && sortOrder >= 0)
        ? sortOrder
        : board.sortOrder;

      try {
        const result = await agent.com.atproto.repo.putRecord({
          repo: ctx.config.forumDid,
          collection: "space.atbb.forum.board",
          rkey: board.rkey,
          record: {
            $type: "space.atbb.forum.board",
            name: name.trim(),
            ...(resolvedDescription != null && { description: resolvedDescription }),
            ...(resolvedSortOrder != null && { sortOrder: resolvedSortOrder }),
            category: { category: { uri: board.categoryUri, cid: categoryCid } },
            createdAt: board.createdAt.toISOString(),
          },
        });

        return c.json({ uri: result.data.uri, cid: result.data.cid });
      } catch (error) {
        return handleRouteError(c, error, "Failed to update board", {
          operation: "PUT /api/admin/boards/:id",
          logger: ctx.logger,
          id: idParam,
        });
      }
    }
  );

  /**
   * DELETE /api/admin/boards/:id
   *
   * Delete a board. Pre-flight: refuses with 409 if any posts have boardId
   * pointing to this board. If clear, calls deleteRecord on the Forum DID's PDS.
   * The firehose indexer removes the DB row asynchronously.
   */
  app.delete(
    "/boards/:id",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageCategories"),
    async (c) => {
      const idParam = c.req.param("id");
      const id = parseBigIntParam(idParam);
      if (id === null) {
        return c.json({ error: "Invalid board ID" }, 400);
      }

      let board: typeof boards.$inferSelect;
      try {
        const [row] = await ctx.db
          .select()
          .from(boards)
          .where(and(eq(boards.id, id), eq(boards.did, ctx.config.forumDid)))
          .limit(1);

        if (!row) {
          return c.json({ error: "Board not found" }, 404);
        }
        board = row;
      } catch (error) {
        return handleRouteError(c, error, "Failed to look up board", {
          operation: "DELETE /api/admin/boards/:id",
          logger: ctx.logger,
          id: idParam,
        });
      }

      // Pre-flight: refuse if any posts reference this board
      try {
        const [postCount] = await ctx.db
          .select({ count: count() })
          .from(posts)
          .where(eq(posts.boardId, id));

        if (postCount && postCount.count > 0) {
          return c.json(
            { error: "Cannot delete board with posts. Remove all posts first." },
            409
          );
        }
      } catch (error) {
        return handleRouteError(c, error, "Failed to check board posts", {
          operation: "DELETE /api/admin/boards/:id",
          logger: ctx.logger,
          id: idParam,
        });
      }

      const { agent, error: agentError } = getForumAgentOrError(ctx, c, "DELETE /api/admin/boards/:id");
      if (agentError) return agentError;

      try {
        await agent.com.atproto.repo.deleteRecord({
          repo: ctx.config.forumDid,
          collection: "space.atbb.forum.board",
          rkey: board.rkey,
        });

        return c.json({ success: true });
      } catch (error) {
        return handleRouteError(c, error, "Failed to delete board", {
          operation: "DELETE /api/admin/boards/:id",
          logger: ctx.logger,
          id: idParam,
        });
      }
    }
  );

  /**
   * GET /api/admin/themes
   *
   * Returns all themes for this forum — no policy filtering.
   * Admins need to see all themes, including drafts not yet in the policy.
   */
  app.get(
    "/themes",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageThemes"),
    async (c) => {
      try {
        const themeList = await ctx.db
          .select()
          .from(themes)
          .where(eq(themes.did, ctx.config.forumDid))
          .limit(100);

        return c.json({
          themes: themeList.map((theme) => ({
            id: serializeBigInt(theme.id),
            uri: `at://${theme.did}/space.atbb.forum.theme/${theme.rkey}`,
            name: theme.name,
            colorScheme: theme.colorScheme,
            tokens: theme.tokens,
            cssOverrides: theme.cssOverrides ?? null,
            fontUrls: (theme.fontUrls as string[] | null) ?? null,
            createdAt: serializeDate(theme.createdAt),
            indexedAt: serializeDate(theme.indexedAt),
          })),
          isTruncated: themeList.length === 100,
        });
      } catch (error) {
        return handleRouteError(c, error, "Failed to retrieve themes", {
          operation: "GET /api/admin/themes",
          logger: ctx.logger,
        });
      }
    }
  );

  /**
   * POST /api/admin/themes
   *
   * Create a new theme record on Forum DID's PDS.
   * Writes space.atbb.forum.theme with a fresh TID rkey.
   * The firehose indexer creates the DB row asynchronously.
   */
  app.post(
    "/themes",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageThemes"),
    async (c) => {
      const { body, error: parseError } = await safeParseJsonBody(c);
      if (parseError) return parseError;

      const { name, colorScheme, tokens, cssOverrides, fontUrls } = body;

      if (typeof name !== "string" || name.trim().length === 0) {
        return c.json({ error: "name is required and must be a non-empty string" }, 400);
      }
      if (typeof colorScheme !== "string" || (colorScheme !== "light" && colorScheme !== "dark")) {
        return c.json({ error: 'colorScheme is required and must be "light" or "dark"' }, 400);
      }
      if (tokens === null || tokens === undefined || typeof tokens !== "object" || Array.isArray(tokens)) {
        return c.json({ error: "tokens is required and must be a plain object" }, 400);
      }
      for (const [key, val] of Object.entries(tokens as Record<string, unknown>)) {
        if (typeof val !== "string") {
          return c.json({ error: `tokens["${key}"] must be a string` }, 400);
        }
      }
      if (cssOverrides !== undefined && typeof cssOverrides !== "string") {
        return c.json({ error: "cssOverrides must be a string" }, 400);
      }
      if (fontUrls !== undefined) {
        if (!Array.isArray(fontUrls)) {
          return c.json({ error: "fontUrls must be an array of strings" }, 400);
        }
        for (const url of fontUrls as unknown[]) {
          if (typeof url !== "string" || !url.startsWith("https://")) {
            return c.json({ error: "fontUrls must contain only HTTPS URLs" }, 400);
          }
        }
      }

      // Sanitize cssOverrides before writing to PDS. In its own try-catch
      // because sanitization failure has different semantics than a PDS write failure.
      let sanitizedCssOverrides: string | undefined;
      if (typeof cssOverrides === "string") {
        try {
          const { css, warnings } = sanitizeCssOverrides(cssOverrides);
          if (warnings.length > 0) {
            ctx.logger.warn("Stripped dangerous CSS constructs from theme on create", {
              operation: "POST /api/admin/themes",
              warnings,
            });
          }
          sanitizedCssOverrides = css;
        } catch (error) {
          if (isProgrammingError(error)) throw error;
          ctx.logger.error("CSS sanitization failed unexpectedly on create", {
            operation: "POST /api/admin/themes",
            error: error instanceof Error ? error.message : String(error),
          });
          return c.json({ error: "Failed to process CSS overrides" }, 500);
        }
      }

      const { agent, error: agentError } = getForumAgentOrError(ctx, c, "POST /api/admin/themes");
      if (agentError) return agentError;

      const rkey = TID.nextStr();
      const now = new Date().toISOString();

      try {
        const result = await agent.com.atproto.repo.putRecord({
          repo: ctx.config.forumDid,
          collection: "space.atbb.forum.theme",
          rkey,
          record: {
            $type: "space.atbb.forum.theme",
            name: name.trim(),
            colorScheme,
            tokens,
            ...(typeof sanitizedCssOverrides === "string" && { cssOverrides: sanitizedCssOverrides }),
            ...(Array.isArray(fontUrls) && { fontUrls }),
            createdAt: now,
          },
        });

        return c.json({ uri: result.data.uri, cid: result.data.cid }, 201);
      } catch (error) {
        return handleRouteError(c, error, "Failed to create theme", {
          operation: "POST /api/admin/themes",
          logger: ctx.logger,
        });
      }
    }
  );

  /**
   * PUT /api/admin/themes/:rkey
   *
   * Update an existing theme. Fetches the existing row from DB to preserve
   * createdAt and fall back optional fields not in the request body.
   * The firehose indexer updates the DB row asynchronously.
   */
  app.put(
    "/themes/:rkey",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageThemes"),
    async (c) => {
      const themeRkey = c.req.param("rkey").trim();

      const { body, error: parseError } = await safeParseJsonBody(c);
      if (parseError) return parseError;

      const { name, colorScheme, tokens, cssOverrides, fontUrls } = body;

      if (typeof name !== "string" || name.trim().length === 0) {
        return c.json({ error: "name is required and must be a non-empty string" }, 400);
      }
      if (typeof colorScheme !== "string" || (colorScheme !== "light" && colorScheme !== "dark")) {
        return c.json({ error: 'colorScheme is required and must be "light" or "dark"' }, 400);
      }
      if (tokens === null || tokens === undefined || typeof tokens !== "object" || Array.isArray(tokens)) {
        return c.json({ error: "tokens is required and must be a plain object" }, 400);
      }
      for (const [key, val] of Object.entries(tokens as Record<string, unknown>)) {
        if (typeof val !== "string") {
          return c.json({ error: `tokens["${key}"] must be a string` }, 400);
        }
      }
      if (cssOverrides !== undefined && typeof cssOverrides !== "string") {
        return c.json({ error: "cssOverrides must be a string" }, 400);
      }
      if (fontUrls !== undefined) {
        if (!Array.isArray(fontUrls)) {
          return c.json({ error: "fontUrls must be an array of strings" }, 400);
        }
        for (const url of fontUrls as unknown[]) {
          if (typeof url !== "string" || !url.startsWith("https://")) {
            return c.json({ error: "fontUrls must contain only HTTPS URLs" }, 400);
          }
        }
      }

      let theme: typeof themes.$inferSelect;
      try {
        const [row] = await ctx.db
          .select()
          .from(themes)
          .where(and(eq(themes.did, ctx.config.forumDid), eq(themes.rkey, themeRkey)))
          .limit(1);

        if (!row) {
          return c.json({ error: "Theme not found" }, 404);
        }
        theme = row;
      } catch (error) {
        return handleRouteError(c, error, "Failed to look up theme", {
          operation: "PUT /api/admin/themes/:rkey",
          logger: ctx.logger,
          themeRkey,
        });
      }

      const { agent, error: agentError } = getForumAgentOrError(ctx, c, "PUT /api/admin/themes/:rkey");
      if (agentError) return agentError;

      // putRecord is a full replacement — fall back to existing values for
      // optional fields not provided in the request body, to avoid data loss.
      const rawCssOverrides =
        typeof cssOverrides === "string" ? cssOverrides : theme.cssOverrides;
      let resolvedCssOverrides: string | null | undefined = rawCssOverrides;
      if (rawCssOverrides != null) {
        try {
          const { css, warnings } = sanitizeCssOverrides(rawCssOverrides);
          if (warnings.length > 0) {
            ctx.logger.warn("Stripped dangerous CSS constructs from theme on update", {
              operation: "PUT /api/admin/themes/:rkey",
              themeRkey,
              warnings,
            });
          }
          resolvedCssOverrides = css;
        } catch (error) {
          if (isProgrammingError(error)) throw error;
          ctx.logger.error("CSS sanitization failed unexpectedly on update", {
            operation: "PUT /api/admin/themes/:rkey",
            themeRkey,
            error: error instanceof Error ? error.message : String(error),
          });
          return c.json({ error: "Failed to process CSS overrides" }, 500);
        }
      }
      const resolvedFontUrls = Array.isArray(fontUrls) ? fontUrls : (theme.fontUrls as string[] | null);

      try {
        const result = await agent.com.atproto.repo.putRecord({
          repo: ctx.config.forumDid,
          collection: "space.atbb.forum.theme",
          rkey: theme.rkey,
          record: {
            $type: "space.atbb.forum.theme",
            name: name.trim(),
            colorScheme,
            tokens,
            ...(resolvedCssOverrides != null && { cssOverrides: resolvedCssOverrides }),
            ...(resolvedFontUrls != null && { fontUrls: resolvedFontUrls }),
            createdAt: theme.createdAt.toISOString(),
            updatedAt: new Date().toISOString(),
          },
        });

        return c.json({ uri: result.data.uri, cid: result.data.cid });
      } catch (error) {
        return handleRouteError(c, error, "Failed to update theme", {
          operation: "PUT /api/admin/themes/:rkey",
          logger: ctx.logger,
          themeRkey,
        });
      }
    }
  );

  /**
   * DELETE /api/admin/themes/:rkey
   *
   * Delete a theme. Pre-flight: refuses with 409 if the theme is set as
   * defaultLightTheme or defaultDarkTheme in the theme policy.
   * The firehose indexer removes the DB row asynchronously.
   */
  app.delete(
    "/themes/:rkey",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageThemes"),
    async (c) => {
      const themeRkey = c.req.param("rkey").trim();

      let theme: typeof themes.$inferSelect;
      try {
        const [row] = await ctx.db
          .select()
          .from(themes)
          .where(and(eq(themes.did, ctx.config.forumDid), eq(themes.rkey, themeRkey)))
          .limit(1);

        if (!row) {
          return c.json({ error: "Theme not found" }, 404);
        }
        theme = row;
      } catch (error) {
        return handleRouteError(c, error, "Failed to look up theme", {
          operation: "DELETE /api/admin/themes/:rkey",
          logger: ctx.logger,
          themeRkey,
        });
      }

      // Pre-flight conflict check: refuse if this theme is a policy default
      const themeUri = `at://${theme.did}/space.atbb.forum.theme/${theme.rkey}`;
      try {
        const [conflictingPolicy] = await ctx.db
          .select({ id: themePolicies.id })
          .from(themePolicies)
          .where(
            and(
              eq(themePolicies.did, ctx.config.forumDid),
              or(
                eq(themePolicies.defaultLightThemeUri, themeUri),
                eq(themePolicies.defaultDarkThemeUri, themeUri)
              )
            )
          )
          .limit(1);

        if (conflictingPolicy) {
          return c.json(
            { error: "Cannot delete a theme that is currently set as a default. Update the theme policy first." },
            409
          );
        }
      } catch (error) {
        return handleRouteError(c, error, "Failed to check theme policy", {
          operation: "DELETE /api/admin/themes/:rkey",
          logger: ctx.logger,
          themeRkey,
        });
      }

      const { agent, error: agentError } = getForumAgentOrError(ctx, c, "DELETE /api/admin/themes/:rkey");
      if (agentError) return agentError;

      try {
        await agent.com.atproto.repo.deleteRecord({
          repo: ctx.config.forumDid,
          collection: "space.atbb.forum.theme",
          rkey: theme.rkey,
        });

        return c.json({ success: true });
      } catch (error) {
        return handleRouteError(c, error, "Failed to delete theme", {
          operation: "DELETE /api/admin/themes/:rkey",
          logger: ctx.logger,
          themeRkey,
        });
      }
    }
  );

  /**
   * POST /api/admin/themes/:rkey/duplicate
   *
   * Clones an existing theme record with " (Copy)" appended to the name.
   * Uses a fresh TID as the new record key.
   * The firehose indexer will create the DB row asynchronously.
   */
  app.post(
    "/themes/:rkey/duplicate",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageThemes"),
    async (c) => {
      const sourceRkey = c.req.param("rkey").trim();

      let source: typeof themes.$inferSelect;
      try {
        const [row] = await ctx.db
          .select()
          .from(themes)
          .where(and(eq(themes.did, ctx.config.forumDid), eq(themes.rkey, sourceRkey)))
          .limit(1);

        if (!row) {
          return c.json({ error: "Theme not found" }, 404);
        }
        source = row;
      } catch (error) {
        return handleRouteError(c, error, "Failed to look up source theme", {
          operation: "POST /api/admin/themes/:rkey/duplicate",
          logger: ctx.logger,
          sourceRkey,
        });
      }

      const { agent, error: agentError } = getForumAgentOrError(
        ctx,
        c,
        "POST /api/admin/themes/:rkey/duplicate"
      );
      if (agentError) return agentError;

      const newRkey = TID.nextStr();
      const newName = `${source.name} (Copy)`;
      const now = new Date().toISOString();

      // Sanitize cssOverrides from source before writing to PDS so any
      // pre-sanitization records don't propagate dangerous CSS via duplication.
      let duplicateCssOverrides: string | null = null;
      if (source.cssOverrides != null) {
        try {
          const { css, warnings } = sanitizeCssOverrides(source.cssOverrides);
          if (warnings.length > 0) {
            ctx.logger.warn("Stripped dangerous CSS constructs from theme on duplicate", {
              operation: "POST /api/admin/themes/:rkey/duplicate",
              sourceRkey,
              warnings,
            });
          }
          duplicateCssOverrides = css;
        } catch (error) {
          if (isProgrammingError(error)) throw error;
          ctx.logger.error("CSS sanitization failed unexpectedly on duplicate", {
            operation: "POST /api/admin/themes/:rkey/duplicate",
            sourceRkey,
            error: error instanceof Error ? error.message : String(error),
          });
          return c.json({ error: "Failed to process CSS overrides" }, 500);
        }
      }

      try {
        const result = await agent.com.atproto.repo.putRecord({
          repo: ctx.config.forumDid,
          collection: "space.atbb.forum.theme",
          rkey: newRkey,
          record: {
            $type: "space.atbb.forum.theme",
            name: newName,
            colorScheme: source.colorScheme,
            tokens: source.tokens,
            ...(duplicateCssOverrides != null && { cssOverrides: duplicateCssOverrides }),
            ...(source.fontUrls != null && { fontUrls: source.fontUrls }),
            createdAt: now,
          },
        });

        return c.json({ uri: result.data.uri, rkey: newRkey, name: newName }, 201);
      } catch (error) {
        return handleRouteError(c, error, "Failed to duplicate theme", {
          operation: "POST /api/admin/themes/:rkey/duplicate",
          logger: ctx.logger,
          sourceRkey,
          newRkey,
        });
      }
    }
  );

  /**
   * PUT /api/admin/theme-policy
   *
   * Create or update the themePolicy singleton (rkey: "self") on Forum DID's PDS.
   * Upsert semantics: works whether or not a policy record exists yet.
   * The firehose indexer creates/updates the DB row asynchronously.
   */
  app.put(
    "/theme-policy",
    requireAuth(ctx),
    requirePermission(ctx, "space.atbb.permission.manageThemes"),
    async (c) => {
      const { body, error: parseError } = await safeParseJsonBody(c);
      if (parseError) return parseError;

      const { availableThemes, defaultLightThemeUri, defaultDarkThemeUri, allowUserChoice } = body;

      if (!Array.isArray(availableThemes) || availableThemes.length === 0) {
        return c.json({ error: "availableThemes is required and must be a non-empty array" }, 400);
      }
      for (const t of availableThemes as unknown[]) {
        if (
          typeof t !== "object" ||
          t === null ||
          typeof (t as Record<string, unknown>).uri !== "string"
        ) {
          return c.json({ error: "Each availableThemes entry must have a uri string field" }, 400);
        }
      }

      if (typeof defaultLightThemeUri !== "string" || !defaultLightThemeUri.startsWith("at://")) {
        return c.json({ error: "defaultLightThemeUri is required and must be an AT-URI" }, 400);
      }
      if (typeof defaultDarkThemeUri !== "string" || !defaultDarkThemeUri.startsWith("at://")) {
        return c.json({ error: "defaultDarkThemeUri is required and must be an AT-URI" }, 400);
      }

      const typedAvailableThemes = availableThemes as Array<{ uri: string; cid?: string }>;
      const availableUris = typedAvailableThemes.map((t) => t.uri);
      if (!availableUris.includes(defaultLightThemeUri)) {
        return c.json({ error: "defaultLightThemeUri must be present in availableThemes" }, 400);
      }
      if (!availableUris.includes(defaultDarkThemeUri)) {
        return c.json({ error: "defaultDarkThemeUri must be present in availableThemes" }, 400);
      }

      const resolvedAllowUserChoice = typeof allowUserChoice === "boolean" ? allowUserChoice : true;

      // CID is optional — live refs (no cid) are valid for canonical atbb.space presets.
      // Pass cid through when provided; omit it when absent or empty string.
      const resolvedThemes = typedAvailableThemes.map((t) => ({
        uri: t.uri,
        cid: typeof t.cid === "string" && t.cid !== "" ? t.cid : undefined,
      }));

      const lightTheme = resolvedThemes.find((t) => t.uri === defaultLightThemeUri);
      const darkTheme  = resolvedThemes.find((t) => t.uri === defaultDarkThemeUri);
      if (!lightTheme || !darkTheme) {
        // Both URIs were validated as present in availableThemes above — this is unreachable.
        return c.json({ error: "Internal error: theme URIs not found in resolved themes" }, 500);
      }

      const { agent, error: agentError } = getForumAgentOrError(ctx, c, "PUT /api/admin/theme-policy");
      if (agentError) return agentError;

      try {
        const result = await agent.com.atproto.repo.putRecord({
          repo: ctx.config.forumDid,
          collection: "space.atbb.forum.themePolicy",
          rkey: "self",
          record: {
            $type: "space.atbb.forum.themePolicy",
            availableThemes: resolvedThemes.map((t) => ({
              uri: t.uri,
              ...(t.cid !== undefined ? { cid: t.cid } : {}),
            })),
            defaultLightTheme: { uri: lightTheme.uri, ...(lightTheme.cid !== undefined ? { cid: lightTheme.cid } : {}) },
            defaultDarkTheme:  { uri: darkTheme.uri,  ...(darkTheme.cid  !== undefined ? { cid: darkTheme.cid  } : {}) },
            allowUserChoice: resolvedAllowUserChoice,
            updatedAt: new Date().toISOString(),
          },
        });

        return c.json({ uri: result.data.uri, cid: result.data.cid });
      } catch (error) {
        return handleRouteError(c, error, "Failed to update theme policy", {
          operation: "PUT /api/admin/theme-policy",
          logger: ctx.logger,
        });
      }
    }
  );

  /**
   * GET /api/admin/modlog
   *
   * Paginated, reverse-chronological list of mod actions.
   * Joins users table twice: once for the moderator handle (via createdBy),
   * once for the subject handle (via subjectDid, nullable for post-targeting actions).
   *
   * Uses leftJoin for both users joins so actions are never dropped when a
   * moderator or subject DID has no indexed users row. moderatorHandle falls
   * back to moderatorDid in that case.
   *
   * Requires any of: moderatePosts, banUsers, lockTopics.
   */
  app.get(
    "/modlog",
    requireAuth(ctx),
    requireAnyPermission(ctx, [
      "space.atbb.permission.moderatePosts",
      "space.atbb.permission.banUsers",
      "space.atbb.permission.lockTopics",
    ]),
    async (c) => {
      const rawLimit = c.req.query("limit");
      const rawOffset = c.req.query("offset");

      if (rawLimit !== undefined && (!/^\d+$/.test(rawLimit))) {
        return c.json({ error: "limit must be a positive integer" }, 400);
      }
      if (rawOffset !== undefined && (!/^\d+$/.test(rawOffset))) {
        return c.json({ error: "offset must be a non-negative integer" }, 400);
      }

      const limitVal = rawLimit !== undefined ? parseInt(rawLimit, 10) : 50;
      const offsetVal = rawOffset !== undefined ? parseInt(rawOffset, 10) : 0;

      if (rawLimit !== undefined && limitVal < 1) {
        return c.json({ error: "limit must be a positive integer" }, 400);
      }
      if (rawOffset !== undefined && offsetVal < 0) {
        return c.json({ error: "offset must be a non-negative integer" }, 400);
      }

      const clampedLimit = Math.min(limitVal, 100);

      try {
        const [countResult, actions] = await Promise.all([
          ctx.db
            .select({ total: count() })
            .from(modActions)
            .where(eq(modActions.did, ctx.config.forumDid)),
          ctx.db
            .select()
            .from(modActions)
            .where(eq(modActions.did, ctx.config.forumDid))
            .orderBy(desc(modActions.createdAt))
            .limit(clampedLimit)
            .offset(offsetVal),
        ]);

        const total = Number(countResult[0]?.total ?? 0);

        // Resolve handles in a single batch query instead of aliased self-joins
        // (drizzle-orm's alias() generates invalid SQL for SQLite)
        const dids = new Set<string>();
        for (const a of actions) {
          if (a.createdBy) dids.add(a.createdBy);
          if (a.subjectDid) dids.add(a.subjectDid);
        }

        const handleMap = new Map<string, string>();
        if (dids.size > 0) {
          const userRows = await ctx.db
            .select({ did: users.did, handle: users.handle })
            .from(users)
            .where(inArray(users.did, [...dids]));
          for (const u of userRows) {
            if (u.handle) handleMap.set(u.did, u.handle);
          }
        }

        return c.json({
          actions: actions.map((a) => ({
            id: a.id.toString(),
            action: a.action,
            moderatorDid: a.createdBy,
            moderatorHandle: handleMap.get(a.createdBy) ?? a.createdBy,
            subjectDid: a.subjectDid ?? null,
            subjectHandle: a.subjectDid ? (handleMap.get(a.subjectDid) ?? null) : null,
            subjectPostUri: a.subjectPostUri ?? null,
            reason: a.reason ?? null,
            createdAt: a.createdAt.toISOString(),
          })),
          total,
          offset: offsetVal,
          limit: clampedLimit,
        });
      } catch (error) {
        return handleRouteError(c, error, "Failed to retrieve mod action log", {
          operation: "GET /api/admin/modlog",
          logger: ctx.logger,
        });
      }
    }
  );

  return app;
}