import { defineAction, fail } from "@agent-native/core/action";
import type { DbExecStatement } from "@agent-native/core/db";
import {
  isMissingOrganizationTableError,
  orgMembers,
} from "@agent-native/core/org";
import {
  accessFilter,
  assertAccess,
  currentAccess,
} from "@agent-native/core/sharing";
import { and, eq, inArray, like, sql } from "drizzle-orm";
import { nanoid } from "nanoid";
import { z } from "zod";

import { getDb, schema } from "../server/db/index.js";
import { designChangeResource } from "../server/lib/design-change-resource.js";
import {
  cleanupPreparedDesignVersionCheckpoint,
  persistPreparedDesignVersionCheckpoint,
  prepareDesignVersionBeforeAgentEditInVersionLock,
  withPreparedDesignVersionSourceFiles,
  withDesignVersionLock,
} from "../server/lib/design-versions.js";
import { screenRestoreContentHashes } from "../server/lib/screen-restore-claims.js";
import {
  deleteVisualEditSnapshotBlobs,
  queueVisualEditSnapshotBlobCleanupInTransaction,
} from "../server/lib/visual-edit-snapshot-blobs.js";
import {
  affectedRowCount,
  designSourceMutationLockKey,
  lockDesignFilesTable,
  withDesignSourceMutationTransaction,
} from "../server/source-workspace.js";
import { designOutputWriteReceipt } from "../shared/generation-output-receipt.js";
import { countLockedLayers } from "../shared/locked-layers.js";

const DESIGN_VERSION_CHECKPOINT_RETRIES = 2;

function isRetryableDesignVersionCheckpointChange(error: unknown): boolean {
  if (!error || typeof error !== "object") return false;
  const candidate = error as Record<string, unknown>;
  return (
    candidate.errorCode === "design-version-checkpoint-changed" &&
    candidate.retryable === true &&
    candidate.statusCode === 409
  );
}

async function withDesignVersionCheckpointRetry<T>(
  operation: () => Promise<T>,
): Promise<T> {
  for (let attempt = 0; ; attempt += 1) {
    try {
      return await operation();
    } catch (error) {
      if (
        !isRetryableDesignVersionCheckpointChange(error) ||
        attempt >= DESIGN_VERSION_CHECKPOINT_RETRIES
      ) {
        throw error;
      }
      await new Promise((resolve) => setTimeout(resolve, 20 * 2 ** attempt));
    }
  }
}

function drizzleSqlForAccess(statement: DbExecStatement) {
  if (typeof statement === "string") return sql.raw(statement);
  const chunks: string[] = [];
  const params: unknown[] = [];
  const usesQuestionPlaceholders = statement.sql.includes("?");
  const placeholder = usesQuestionPlaceholders ? /\?/g : /\$(\d+)/g;
  let offset = 0;
  for (const match of statement.sql.matchAll(placeholder)) {
    const index = match.index ?? 0;
    chunks.push(statement.sql.slice(offset, index));
    const argumentIndex = usesQuestionPlaceholders
      ? params.length
      : Number(match[1]) - 1;
    if (argumentIndex < 0 || argumentIndex >= (statement.args?.length ?? 0)) {
      throw new Error("Transactional access query has mismatched parameters.");
    }
    params.push(statement.args?.[argumentIndex]);
    offset = index + match[0].length;
  }
  chunks.push(statement.sql.slice(offset));
  return sql(chunks as unknown as TemplateStringsArray, ...params);
}

function normalizeDrizzleExecResult(result: unknown): {
  rows: any[];
  rowsAffected: number;
} {
  const resultObject =
    result && typeof result === "object"
      ? (result as Record<string, unknown>)
      : undefined;
  const rows = Array.isArray(result)
    ? result
    : Array.isArray(resultObject?.rows)
      ? resultObject.rows
      : [];
  const rowsAffected = [
    resultObject?.rowCount,
    resultObject?.rowsAffected,
    resultObject?.affectedRows,
    resultObject?.count,
  ].find((value): value is number => typeof value === "number");
  return { rows, rowsAffected: rowsAffected ?? rows.length };
}

function isRecord(value: unknown): value is Record<string, unknown> {
  return Boolean(value && typeof value === "object" && !Array.isArray(value));
}

function pruneKeyedRecord(
  value: unknown,
  fileId: string,
): Record<string, unknown> | undefined {
  if (!isRecord(value)) return undefined;
  const next = { ...value };
  delete next[fileId];
  return next;
}

function variantScreenMatchesFile(screen: unknown, fileId: string): boolean {
  if (typeof screen === "string") return screen === fileId;
  return isRecord(screen) && screen.id === fileId;
}

function pruneDesignVariantSets(
  value: unknown,
  fileId: string,
): Record<string, unknown> | undefined {
  if (!isRecord(value)) return undefined;
  const next: Record<string, unknown> = {};
  for (const [key, rawSet] of Object.entries(value)) {
    if (!isRecord(rawSet) || !Array.isArray(rawSet.screens)) {
      next[key] = rawSet;
      continue;
    }
    const screens = rawSet.screens.filter(
      (screen) => !variantScreenMatchesFile(screen, fileId),
    );
    if (screens.length <= 1) continue;
    next[key] = { ...rawSet, screens };
  }
  return next;
}

export function pruneDeletedFileMetadata(
  data: Record<string, unknown>,
  fileId: string,
): Record<string, unknown> {
  return {
    ...data,
    canvasFrames: pruneKeyedRecord(data.canvasFrames, fileId) ?? {},
    screenMetadata: pruneKeyedRecord(data.screenMetadata, fileId) ?? {},
    localhostScreens: pruneKeyedRecord(data.localhostScreens, fileId) ?? {},
    designVariantSets:
      pruneDesignVariantSets(data.designVariantSets, fileId) ?? {},
  };
}

function nextUpdatedAt(current: string | null, now: Date): string {
  const currentMs = current ? Date.parse(current) : Number.NaN;
  return new Date(
    Math.max(now.getTime(), Number.isFinite(currentMs) ? currentMs + 1 : 0),
  ).toISOString();
}

function parseDesignData(
  designId: string,
  serialized: string | null,
): Record<string, unknown> {
  if (serialized === null) return {};
  try {
    const parsed: unknown = JSON.parse(serialized);
    if (isRecord(parsed)) return parsed;
  } catch {
    throw new Error(`Design "${designId}" has invalid data JSON.`);
  }
  throw new Error(`Design "${designId}" has invalid data JSON.`);
}

interface DeletedFileSnapshot {
  id: string;
  filename: string;
  content: string;
  fileType: string;
  createdAt: string | null;
  updatedAt: string;
  geometry?: Record<string, unknown>;
  screenMetadata?: Record<string, unknown>;
  localhostScreen?: Record<string, unknown>;
  restoreClaimId?: string;
  restoreSourceFileId?: string;
  variantMemberships?: {
    setId: string;
    set: Record<string, unknown>;
    screen: unknown;
    index: number;
    originalScreenIds: string[];
  }[];
}

function deletedFileMetadataSnapshot(
  data: Record<string, unknown>,
  fileId: string,
): Pick<
  DeletedFileSnapshot,
  "geometry" | "screenMetadata" | "localhostScreen" | "variantMemberships"
> {
  const canvasFrames = isRecord(data.canvasFrames)
    ? data.canvasFrames[fileId]
    : undefined;
  const screenMetadata = isRecord(data.screenMetadata)
    ? data.screenMetadata[fileId]
    : undefined;
  const localhostScreen = isRecord(data.localhostScreens)
    ? data.localhostScreens[fileId]
    : undefined;
  const variantMemberships: NonNullable<
    DeletedFileSnapshot["variantMemberships"]
  > = [];

  if (isRecord(data.designVariantSets)) {
    for (const [setId, rawSet] of Object.entries(data.designVariantSets)) {
      if (!isRecord(rawSet) || !Array.isArray(rawSet.screens)) continue;
      const screens: unknown[] = rawSet.screens;
      const screenIds = screens.map((screen) =>
        typeof screen === "string"
          ? screen
          : isRecord(screen) && typeof screen.id === "string"
            ? screen.id
            : null,
      );
      if (screenIds.some((id) => id === null)) continue;
      screens.forEach((screen, index) => {
        if (screenIds[index] !== fileId) return;
        variantMemberships.push({
          setId,
          set: {
            ...rawSet,
            screens: screens.map((member) =>
              isRecord(member) ? { ...member } : member,
            ),
          },
          screen,
          index,
          originalScreenIds: screenIds as string[],
        });
      });
    }
  }

  return {
    ...(isRecord(canvasFrames) ? { geometry: { ...canvasFrames } } : {}),
    ...(isRecord(screenMetadata)
      ? { screenMetadata: { ...screenMetadata } }
      : {}),
    ...(isRecord(localhostScreen)
      ? { localhostScreen: { ...localhostScreen } }
      : {}),
    ...(variantMemberships.length > 0 ? { variantMemberships } : {}),
  };
}

function snapshotDeletedFile(
  file: {
    id: string;
    filename: string;
    content: string;
    fileType: string;
    createdAt: string | null;
    updatedAt: string | null;
  },
  data: Record<string, unknown>,
  restoreSourceFileId = file.id,
): DeletedFileSnapshot {
  return {
    id: file.id,
    filename: file.filename,
    content: file.content,
    fileType: file.fileType,
    createdAt: file.createdAt,
    updatedAt: file.updatedAt ?? "",
    restoreSourceFileId,
    ...deletedFileMetadataSnapshot(data, file.id),
  };
}

function hasConnectionId(value: unknown): boolean {
  return (
    isRecord(value) &&
    typeof value.connectionId === "string" &&
    value.connectionId.length > 0
  );
}

function screenRestoreClaimSnapshot(
  snapshot: DeletedFileSnapshot,
  fileOrderKey: string = snapshot.id,
):
  | (Pick<
      DeletedFileSnapshot,
      "filename" | "fileType" | "screenMetadata" | "localhostScreen"
    > & {
      contentHashes: string[];
      createdAt: string | null;
      fileOrderKey: string;
    })
  | null {
  if (
    snapshot.fileType !== "html" &&
    snapshot.fileType !== "jsx" &&
    !hasConnectionId(snapshot.screenMetadata) &&
    !hasConnectionId(snapshot.localhostScreen)
  ) {
    return null;
  }

  return {
    filename: snapshot.filename,
    fileType: snapshot.fileType,
    createdAt: snapshot.createdAt,
    fileOrderKey,
    contentHashes: screenRestoreContentHashes(
      snapshot.content,
      snapshot.fileType,
    ),
    ...(snapshot.screenMetadata
      ? { screenMetadata: { ...snapshot.screenMetadata } }
      : {}),
    ...(snapshot.localhostScreen
      ? { localhostScreen: { ...snapshot.localhostScreen } }
      : {}),
  };
}

function restoreFileOrderKey(snapshot: string, sourceFileId: string): string {
  let parsed: unknown;
  try {
    parsed = JSON.parse(snapshot);
  } catch {
    fail("This Screen restore is no longer available.", {
      errorCode: "screen_restore_claim_invalid",
      statusCode: 409,
    });
  }
  if (!isRecord(parsed)) {
    fail("This Screen restore is no longer available.", {
      errorCode: "screen_restore_claim_invalid",
      statusCode: 409,
    });
  }
  if (parsed.fileOrderKey === undefined) return sourceFileId;
  if (typeof parsed.fileOrderKey !== "string" || !parsed.fileOrderKey) {
    fail("This Screen restore is no longer available.", {
      errorCode: "screen_restore_claim_invalid",
      statusCode: 409,
    });
  }
  return parsed.fileOrderKey;
}

export async function deleteDesignFilesByOperationSourcePrefix(
  designId: string,
  prefix: string,
): Promise<string[]> {
  await assertAccess("design", designId, "editor");
  return getDb().transaction(async (tx) => {
    await tx.execute(
      sql`SELECT pg_advisory_xact_lock(hashtextextended(${designSourceMutationLockKey(designId)}, 0::bigint))`,
    );
    const deleted = await tx
      .delete(schema.designFiles)
      .where(
        and(
          eq(schema.designFiles.designId, designId),
          like(
            schema.designFiles.contentOperationSource,
            `${prefix.replace(/[\\%_]/g, "\\$&")}%`,
          ),
        ),
      )
      .returning({ id: schema.designFiles.id });
    if (deleted.length === 0) return [];
    const [design] = await tx
      .select({
        data: schema.designs.data,
        updatedAt: schema.designs.updatedAt,
      })
      .from(schema.designs)
      .where(eq(schema.designs.id, designId))
      .for("update");
    if (!design) throw new Error("Design " + designId + " not found.");
    let data = parseDesignData(designId, design.data);
    for (const { id } of deleted) data = pruneDeletedFileMetadata(data, id);
    const updatedAt = nextUpdatedAt(design.updatedAt, new Date());
    data.updatedAt = updatedAt;
    await tx
      .update(schema.designs)
      .set({ data: JSON.stringify(data), updatedAt })
      .where(eq(schema.designs.id, designId));
    return deleted.map(({ id }) => id);
  });
}

export default defineAction({
  description:
    "Delete one or more files from a design project. Idempotent: if a file is already gone, returns deleted=false so cleanup retries can continue. Validates ownership via the parent design's access when the file exists.",
  schema: z.object({
    id: z.string().describe("File ID to delete"),
    designId: z
      .string()
      .optional()
      .describe(
        "Owning Design id; include it when the target may already be missing during a generation turn.",
      ),
    fileIds: z
      .array(z.string())
      .max(100)
      .optional()
      .describe("Additional file IDs to delete in the same transaction."),
    allowLockedLayers: z
      .boolean()
      .optional()
      .default(false)
      .describe(
        "Delete the screen even though it holds locked layers. Only set this when the user explicitly asked for that screen to go.",
      ),
    historyCheckpointId: z
      .string()
      .optional()
      .describe(
        "Legacy frontend checkpoint to validate before a delete; the live pre-delete version is always captured in the delete transaction.",
      ),
  }),
  needsApproval: ({ allowLockedLayers }) => allowLockedLayers,
  allowPersistentApproval: false,
  run: async (
    {
      id,
      designId: expectedDesignId,
      fileIds,
      allowLockedLayers,
      historyCheckpointId,
    },
    context,
  ) => {
    const db = getDb();
    const requestedIds = [...new Set([id, ...(fileIds ?? [])])];

    const scopedFiles = await db
      .select({
        id: schema.designFiles.id,
        designId: schema.designFiles.designId,
        filename: schema.designFiles.filename,
        fileType: schema.designFiles.fileType,
        content: schema.designFiles.content,
      })
      .from(schema.designFiles)
      .innerJoin(
        schema.designs,
        eq(schema.designFiles.designId, schema.designs.id),
      )
      .where(
        and(
          requestedIds.length === 1
            ? eq(schema.designFiles.id, requestedIds[0]!)
            : inArray(schema.designFiles.id, requestedIds),
          accessFilter(schema.designs, schema.designShares),
        ),
      )
      .limit(requestedIds.length);

    const file = scopedFiles.find((candidate) => candidate.id === id);

    if (!file) {
      if (requestedIds.length > 1) {
        throw new Error(
          "One or more selected screens are no longer available. Refresh and try again.",
        );
      }
      if (expectedDesignId === undefined) {
        return { id, deleted: false, alreadyMissing: true };
      }
      await assertAccess("design", expectedDesignId, "editor");
      return {
        id,
        deleted: false,
        alreadyMissing: true,
        _receipt: designOutputWriteReceipt(expectedDesignId, 0),
      };
    }

    if (expectedDesignId !== undefined && expectedDesignId !== file.designId) {
      fail("File does not belong to the requested design.", {
        errorCode: "design_mismatch",
        statusCode: 400,
      });
    }

    if (requestedIds.length > 1 && scopedFiles.length !== requestedIds.length) {
      throw new Error(
        "One or more selected screens are no longer available. Refresh and try again.",
      );
    }

    if (scopedFiles.some((candidate) => candidate.designId !== file.designId)) {
      throw new Error(
        "All files in one delete must belong to the same design.",
      );
    }

    await assertAccess("design", file.designId, "editor");
    if (!allowLockedLayers) {
      for (const candidate of scopedFiles) {
        if (countLockedLayers(candidate.content) > 0) {
          throw new Error(
            "This screen contains locked layers. Unlock them before deleting the screen, or pass allowLockedLayers when the user asked for the whole screen to go.",
          );
        }
      }
    }

    if (historyCheckpointId !== undefined) {
      if (context?.caller !== "frontend") {
        throw new Error(
          "A reusable editor history checkpoint is only valid for frontend deletes.",
        );
      }
      const [checkpoint] = await db
        .select({
          id: schema.designVersions.id,
          designId: schema.designVersions.designId,
        })
        .from(schema.designVersions)
        .where(
          and(
            eq(schema.designVersions.id, historyCheckpointId),
            eq(schema.designVersions.designId, file.designId),
          ),
        )
        .limit(1);
      if (!checkpoint) {
        throw new Error(
          "The editor history checkpoint is no longer available.",
        );
      }
    }

    const deletion: {
      deletedIds: string[];
      deletedFiles: DeletedFileSnapshot[];
      snapshotBlobHandles: (string | null)[];
    } = await withDesignVersionCheckpointRetry(() =>
      withDesignVersionLock(file.designId, async () => {
        const preparedCheckpoint =
          await prepareDesignVersionBeforeAgentEditInVersionLock(
            file.designId,
            context,
          );
        const transactionOutcome = {
          callbackCompleted: false,
          rollbackConfirmed: false,
        };
        try {
          const result = await withPreparedDesignVersionSourceFiles(
            file.designId,
            async (preparedFiles) =>
              withDesignSourceMutationTransaction(
                file.designId,
                async (tx) => {
                  await lockDesignFilesTable(tx);
                  const currentFiles = await tx
                    .select({
                      id: schema.designFiles.id,
                      filename: schema.designFiles.filename,
                      content: schema.designFiles.content,
                      fileType: schema.designFiles.fileType,
                      createdAt: schema.designFiles.createdAt,
                      updatedAt: schema.designFiles.updatedAt,
                    })
                    .from(schema.designFiles)
                    .where(eq(schema.designFiles.designId, file.designId))
                    .for("update");
                  const currentTargetFiles = currentFiles.filter((candidate) =>
                    requestedIds.includes(candidate.id),
                  );
                  if (currentTargetFiles.length !== requestedIds.length) {
                    if (requestedIds.length === 1) {
                      return {
                        deletedIds: [],
                        deletedFiles: [],
                        snapshotBlobHandles: [],
                      };
                    }
                    fail(
                      "A selected screen changed while it was being deleted. Refresh and try again.",
                      { errorCode: "design_file_changed", statusCode: 409 },
                    );
                  }
                  if (!currentTargetFiles.length) {
                    return {
                      deletedIds: [],
                      deletedFiles: [],
                      snapshotBlobHandles: [],
                    };
                  }
                  const targetIds = currentTargetFiles.map(
                    (candidate) => candidate.id,
                  );
                  const priorRestoreClaims = await tx
                    .select({
                      sourceFileId:
                        schema.designScreenRestoreClaims.sourceFileId,
                      restoredFileId:
                        schema.designScreenRestoreClaims.restoredFileId,
                      snapshot: schema.designScreenRestoreClaims.snapshot,
                    })
                    .from(schema.designScreenRestoreClaims)
                    .where(
                      and(
                        eq(
                          schema.designScreenRestoreClaims.designId,
                          file.designId,
                        ),
                        inArray(
                          schema.designScreenRestoreClaims.restoredFileId,
                          targetIds,
                        ),
                      ),
                    )
                    .for("update");
                  const fileOrderKeyByRestoredId = new Map<string, string>();
                  for (const claim of priorRestoreClaims) {
                    if (!claim.restoredFileId) continue;
                    const fileOrderKey = restoreFileOrderKey(
                      claim.snapshot,
                      claim.sourceFileId,
                    );
                    const previous = fileOrderKeyByRestoredId.get(
                      claim.restoredFileId,
                    );
                    if (previous && previous !== fileOrderKey) {
                      fail("This Screen restore is no longer available.", {
                        errorCode: "screen_restore_claim_invalid",
                        statusCode: 409,
                      });
                    }
                    fileOrderKeyByRestoredId.set(
                      claim.restoredFileId,
                      fileOrderKey,
                    );
                  }
                  const [design] = await tx
                    .select({
                      id: schema.designs.id,
                      data: schema.designs.data,
                      updatedAt: schema.designs.updatedAt,
                      ownerEmail: schema.designs.ownerEmail,
                      orgId: schema.designs.orgId,
                      visibility: schema.designs.visibility,
                    })
                    .from(schema.designs)
                    .where(eq(schema.designs.id, file.designId))
                    .for("update");
                  if (!design) {
                    fail("Design not found.", {
                      errorCode: "not_found",
                      statusCode: 404,
                    });
                  }
                  await tx
                    .select({ id: schema.designShares.id })
                    .from(schema.designShares)
                    .where(eq(schema.designShares.resourceId, file.designId))
                    .for("update");
                  const access = currentAccess();
                  const memberEmail = access.userEmail?.trim().toLowerCase();
                  const ownerEmail = design.ownerEmail?.trim().toLowerCase();
                  if (
                    design.visibility === "org" &&
                    design.orgId &&
                    memberEmail &&
                    memberEmail !== ownerEmail
                  ) {
                    try {
                      await tx
                        .select({ id: orgMembers.id })
                        .from(orgMembers)
                        .where(
                          and(
                            eq(orgMembers.orgId, design.orgId),
                            sql`lower(${orgMembers.email}) = ${memberEmail}`,
                          ),
                        )
                        .for("update");
                    } catch (error) {
                      if (!isMissingOrganizationTableError(error)) throw error;
                    }
                  }
                  const transactionAccess = {
                    ...access,
                    transaction: {
                      async execute(statement: DbExecStatement) {
                        return normalizeDrizzleExecResult(
                          await tx.execute(drizzleSqlForAccess(statement)),
                        );
                      },
                    },
                  };
                  await assertAccess(
                    "design",
                    file.designId,
                    "editor",
                    transactionAccess,
                  );
                  if (preparedCheckpoint) {
                    await persistPreparedDesignVersionCheckpoint(
                      preparedCheckpoint,
                      preparedFiles,
                      tx,
                    );
                  }
                  if (!allowLockedLayers) {
                    for (const candidate of currentTargetFiles) {
                      if (countLockedLayers(candidate.content) > 0) {
                        fail(
                          "This screen contains locked layers. Unlock them before deleting the screen, or pass allowLockedLayers when the user asked for the whole screen to go.",
                          {
                            errorCode: "design_file_locked_layers",
                            statusCode: 409,
                          },
                        );
                      }
                    }
                  }
                  let data = parseDesignData(file.designId, design.data);
                  const deletedFiles = currentTargetFiles.map((candidate) =>
                    snapshotDeletedFile(candidate, data),
                  );
                  for (const deletedFile of deletedFiles) {
                    const trustedSnapshot = screenRestoreClaimSnapshot(
                      deletedFile,
                      fileOrderKeyByRestoredId.get(deletedFile.id) ??
                        deletedFile.id,
                    );
                    if (!trustedSnapshot) continue;
                    const restoreClaimId = nanoid();
                    await tx.insert(schema.designScreenRestoreClaims).values({
                      id: restoreClaimId,
                      designId: file.designId,
                      sourceFileId: deletedFile.id,
                      snapshot: JSON.stringify(trustedSnapshot),
                    });
                    deletedFile.restoreClaimId = restoreClaimId;
                    deletedFile.restoreSourceFileId = deletedFile.id;
                  }
                  const snapshotRows = await tx
                    .select({
                      blobHandle: schema.designVisualEditSnapshots.blobHandle,
                    })
                    .from(schema.designVisualEditSnapshots)
                    .where(
                      and(
                        eq(
                          schema.designVisualEditSnapshots.designId,
                          file.designId,
                        ),
                        inArray(
                          schema.designVisualEditSnapshots.fileId,
                          targetIds,
                        ),
                      ),
                    )
                    .for("update");
                  await queueVisualEditSnapshotBlobCleanupInTransaction(
                    tx,
                    snapshotRows.map((row) => row.blobHandle),
                  );
                  const deleteResult = await tx
                    .delete(schema.designFiles)
                    .where(
                      and(
                        targetIds.length === 1
                          ? eq(schema.designFiles.id, targetIds[0]!)
                          : inArray(schema.designFiles.id, targetIds),
                        eq(schema.designFiles.designId, file.designId),
                      ),
                    );
                  const affected = affectedRowCount(deleteResult);
                  if (affected === 0) {
                    if (requestedIds.length === 1) {
                      return {
                        deletedIds: [],
                        deletedFiles: [],
                        snapshotBlobHandles: [],
                      };
                    }
                    fail(
                      "A selected screen changed while it was being deleted. Refresh and try again.",
                      { errorCode: "design_file_changed", statusCode: 409 },
                    );
                  }
                  if (affected === undefined) {
                    fail("Could not verify that the design file was deleted.", {
                      statusCode: 500,
                    });
                  }
                  if (affected !== targetIds.length) {
                    fail("Unexpected design file delete result.", {
                      statusCode: 500,
                    });
                  }

                  const updatedAt = nextUpdatedAt(design.updatedAt, new Date());
                  for (const targetId of targetIds) {
                    data = pruneDeletedFileMetadata(data, targetId);
                  }
                  data.updatedAt = updatedAt;
                  const designUpdateResult = await tx
                    .update(schema.designs)
                    .set({ data: JSON.stringify(data), updatedAt })
                    .where(eq(schema.designs.id, file.designId));
                  const designAffected = affectedRowCount(designUpdateResult);
                  if (designAffected === undefined) {
                    fail(
                      "Could not verify that the design metadata was updated.",
                      {
                        statusCode: 500,
                      },
                    );
                  }
                  if (designAffected !== 1) {
                    fail("Unexpected design metadata update result.", {
                      statusCode: 500,
                    });
                  }
                  return {
                    deletedIds: targetIds,
                    deletedFiles,
                    snapshotBlobHandles: snapshotRows.map(
                      (row) => row.blobHandle,
                    ),
                  };
                },
                transactionOutcome,
              ),
          );
          if (preparedCheckpoint) {
            await cleanupPreparedDesignVersionCheckpoint(
              preparedCheckpoint,
              true,
            );
          }
          return result;
        } catch (error) {
          if (preparedCheckpoint) {
            await cleanupPreparedDesignVersionCheckpoint(
              preparedCheckpoint,
              false,
              transactionOutcome.rollbackConfirmed,
            );
          }
          throw error;
        }
      }),
    );

    if (deletion.deletedIds.length > 0) {
      await deleteVisualEditSnapshotBlobs(deletion.snapshotBlobHandles);
    }

    if (requestedIds.length === 1) {
      return deletion.deletedIds.includes(id)
        ? {
            id,
            designId: file.designId,
            deleted: true,
            deletedFiles: deletion.deletedFiles,
            _receipt: designOutputWriteReceipt(
              file.designId,
              deletion.deletedIds.length,
            ),
          }
        : {
            id,
            deleted: false,
            alreadyMissing: true,
            _receipt: designOutputWriteReceipt(file.designId, 0),
          };
    }
    return {
      id,
      designId: file.designId,
      deleted: deletion.deletedIds.includes(id),
      deletedIds: deletion.deletedIds,
      ...(deletion.deletedIds.includes(id)
        ? { deletedFiles: deletion.deletedFiles }
        : { alreadyMissing: true }),
      _receipt: designOutputWriteReceipt(
        file.designId,
        deletion.deletedIds.length,
      ),
    };
  },
  changeResource: (_p, result) =>
    designChangeResource(result?.designId, result),
});