import "server-only";

import { randomBytes } from "crypto";
import { access, mkdir, readFile, unlink, writeFile } from "fs/promises";
import mysql, { type Pool, type PoolConnection, type RowDataPacket } from "mysql2/promise";
import path from "path";
import { ensureOfficialMailSchema, getDbPool } from "@/lib/db";
import { hasActiveGrowthPlanByOwnerEmail } from "@/lib/mail-billing-access";
import { getFileDrawerQuotaBytesForTier } from "@/lib/mailbox-file-drawer";
import { getMailPlanQuotaTierForOwnerUserId } from "@/lib/mail-plan-quota";
import {
  GROWTH_LARGE_ATTACHMENT_LIMIT_BYTES,
  LARGE_ATTACHMENT_DOWNLOAD_LIMIT,
  LARGE_ATTACHMENT_RETENTION_DAYS,
} from "@/lib/mail-attachment-policy";
import { getMailAbsoluteUrl } from "@/lib/mail-urls";
import { plainTextToHtml, sanitizeMailboxHtml } from "@/lib/mail-html";
import {
  abortPrivateMultipartUpload,
  completePrivateMultipartUpload,
  createPrivateMultipartUpload,
  deleteObjectFromObjectStorage,
  DIRECT_MULTIPART_PART_BYTES,
  presignPrivateMultipartParts,
  readObjectFromObjectStorage,
  streamObjectFromObjectStorage,
  uploadBufferToObjectStorage,
  uploadPrivateBufferToObjectStorage,
  uploadPrivateStreamToObjectStorage,
} from "@/lib/object-storage";

export type ComposeUploadKind = "attachment" | "inline-image";
export type ComposeUploadStorageStatus = "deleted" | "finalized" | "temporary";

export type ComposeUploadClientItem = {
  contentType: string;
  filename: string;
  id: number;
  isImage: boolean;
  kind: ComposeUploadKind;
  previewUrl: string;
  sizeBytes: number;
  token: string;
};

export type ComposeRawAttachment = {
  content: Buffer;
  contentDisposition: "attachment" | "inline";
  contentId: string | null;
  contentType: string;
  filename: string;
};

export type LinkedComposeUploadAttachment = {
  contentDisposition: "attachment";
  contentId: null;
  contentType: string;
  extension: string;
  externalUrl: string;
  filename: string;
  index: number;
  isInline: false;
  isLargeAttachment: boolean;
  isPendingPublication?: boolean;
  isPreviewable: boolean;
  sizeBytes: number;
};

export type LinkedComposeUploadAttachmentPayload = Omit<LinkedComposeUploadAttachment, "externalUrl"> & {
  content: Buffer;
  externalUrl: string | null;
};

type ComposeUploadRow = RowDataPacket & {
  created_at: Date;
  id: number;
  mailbox_message_id: number | null;
  published_at: Date | null;
  mime_type: string;
  original_name: string;
  owner_user_id: number;
  public_url: string | null;
  large_attachment: number;
  download_count: number;
  expires_at: Date | null;
  size_bytes: number;
  storage_bucket: string | null;
  storage_key: string | null;
  multipart_upload_id: string | null;
  storage_status: ComposeUploadStorageStatus;
  temp_path: string;
  temp_token: string;
  upload_kind: ComposeUploadKind;
};

type UserLookupRow = RowDataPacket & {
  id: number;
};

type FinalizedComposeUpload = {
  contentType: string;
  expiresAt?: Date | null;
  filename: string;
  id: number;
  kind: ComposeUploadKind;
  publicUrl: string;
  sizeBytes: number;
  token: string;
};

export type ComposeUploadFinalizeStatus = "complete" | "uploading" | "waiting";

export type ComposeUploadFinalizeProgressItem = {
  filename: string;
  id: number;
  kind: ComposeUploadKind;
  sizeBytes: number;
  status: ComposeUploadFinalizeStatus;
  uploadedBytes: number;
};

export type ComposeUploadFinalizeProgressSnapshot = {
  completedBytes: number;
  items: ComposeUploadFinalizeProgressItem[];
  totalBytes: number;
  totalCount: number;
  uploadedCount: number;
};

type FinalizeComposeInlineImagesResult = {
  bodyHtml: string | null;
  finalizedUploadIds: number[];
};

export type ComposeUploadCleanupTarget = {
  id: number;
  multipartUploadId?: string | null;
  publicUrl: string | null;
  storageKey: string | null;
  tempPath: string;
};

type SqlQueryable = Pool | PoolConnection;

const DEFAULT_COMPOSE_UPLOAD_MAX_BYTES = 25 * 1024 * 1024;
const DEFAULT_COMPOSE_UPLOAD_TTL_HOURS = 24;
const COMPOSE_UPLOAD_SELECT_COLUMNS = `
  id,
  owner_user_id,
  mailbox_message_id,
  temp_token,
  upload_kind,
  storage_status,
  original_name,
  mime_type,
  size_bytes,
  temp_path,
  storage_bucket,
  storage_key,
  multipart_upload_id,
  public_url,
  large_attachment,
  download_count,
  expires_at,
  published_at,
  created_at
`;

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

function formatAttachmentSize(sizeBytes: number) {
  if (sizeBytes < 1024) {
    return `${sizeBytes} B`;
  }

  if (sizeBytes < 1024 * 1024) {
    return `${(sizeBytes / 1024).toFixed(sizeBytes < 10 * 1024 ? 1 : 0)} KB`;
  }

  if (sizeBytes < 1024 * 1024 * 1024) {
    return `${(sizeBytes / (1024 * 1024)).toFixed(sizeBytes < 10 * 1024 * 1024 ? 1 : 0)} MB`;
  }

  return `${(sizeBytes / (1024 * 1024 * 1024)).toFixed(1)} GB`;
}

function normalizeFilename(filename: string, fallbackBaseName: string) {
  const trimmed = filename.trim().replace(/[<>:"/\\|?*\u0000-\u001f]+/g, " ").replace(/\s+/g, " ");
  const withoutDots = trimmed.replace(/^\.+/, "").trim();

  if (withoutDots) {
    return withoutDots.slice(0, 255);
  }

  return fallbackBaseName;
}

function normalizeUploadContentType(contentType: string) {
  return (contentType.trim().toLowerCase() || "application/octet-stream").slice(0, 191);
}

function getFilenameExtension(filename: string) {
  const extension = path.extname(filename).trim().replace(/^\./, "").toLowerCase();
  return extension.slice(0, 16);
}

function normalizeAttachmentContentType(contentType: string | null | undefined, filename: string) {
  const normalized = contentType?.trim().toLowerCase() || "";

  if (normalized && normalized !== "application/octet-stream") {
    return normalized;
  }

  switch (getFilenameExtension(filename)) {
    case "csv":
      return "text/csv";
    case "gif":
      return "image/gif";
    case "htm":
    case "html":
      return "text/html";
    case "jpeg":
    case "jpg":
      return "image/jpeg";
    case "json":
      return "application/json";
    case "md":
      return "text/markdown";
    case "pdf":
      return "application/pdf";
    case "png":
      return "image/png";
    case "svg":
      return "image/svg+xml";
    case "txt":
      return "text/plain";
    case "webp":
      return "image/webp";
    default:
      return normalized || "application/octet-stream";
  }
}

function isPreviewableAttachment(contentType: string, filename: string) {
  const normalizedContentType = contentType.trim().toLowerCase();
  const extension = getFilenameExtension(filename);

  if (
    normalizedContentType.startsWith("image/") ||
    normalizedContentType.startsWith("text/") ||
    normalizedContentType === "application/pdf"
  ) {
    return true;
  }

  return ["csv", "gif", "jpeg", "jpg", "json", "md", "pdf", "png", "svg", "txt", "webp"].includes(
    extension,
  );
}

function isImageContentType(contentType: string) {
  return contentType.trim().toLowerCase().startsWith("image/");
}

function readMaxUploadBytes() {
  const value = Number(process.env.MAIL_COMPOSE_UPLOAD_MAX_BYTES ?? DEFAULT_COMPOSE_UPLOAD_MAX_BYTES);

  if (!Number.isFinite(value) || value < 1) {
    return DEFAULT_COMPOSE_UPLOAD_MAX_BYTES;
  }

  return Math.floor(value);
}

function readUploadTtlHours() {
  const value = Number(process.env.MAIL_COMPOSE_UPLOAD_TTL_HOURS ?? DEFAULT_COMPOSE_UPLOAD_TTL_HOURS);

  if (!Number.isFinite(value) || value < 1) {
    return DEFAULT_COMPOSE_UPLOAD_TTL_HOURS;
  }

  return Math.floor(value);
}

function getTempUploadDirectory() {
  return path.resolve(
    /* turbopackIgnore: true */ process.cwd(),
    process.env.MAIL_COMPOSE_TEMP_DIR ?? "tmp/mail-compose-uploads",
  );
}

function buildComposeUploadPreviewPath(token: string) {
  return `/api/mailbox/uploads/${token}`;
}

function extractComposeUploadPreviewReferences(value: string) {
  const references = Array.from(
    value.matchAll(
      /\bsrc\s*=\s*(['"])([^'"]*\/api\/mailbox\/uploads\/([a-f0-9]{48})(?:[?#][^'"]*)?)\1/gi,
    ),
  )
    .map((match) => ({
      source: match[2] ?? "",
      token: (match[3] ?? "").toLowerCase(),
    }))
    .filter((reference) => reference.source && reference.token);

  return references.filter(
    (reference, index) =>
      references.findIndex(
        (candidate) =>
          candidate.source === reference.source && candidate.token === reference.token,
      ) === index,
  );
}

function buildInlineComposeContentId(row: ComposeUploadRow) {
  return `${row.temp_token}@officialmail-inline`;
}

function containsEmbeddedDataImage(value: string | null | undefined) {
  return typeof value === "string" && /\bsrc=(['"])data:image\//i.test(value);
}

function parseEmbeddedDataImage(dataUrl: string) {
  const matched = dataUrl.match(/^data:(image\/[a-z0-9.+-]+);base64,([\s\S]+)$/i);

  if (!matched) {
    return null;
  }

  const contentType = matched[1].trim().toLowerCase();
  const encodedPayload = matched[2].replace(/\s+/g, "");

  if (!encodedPayload) {
    return null;
  }

  try {
    const buffer = Buffer.from(encodedPayload, "base64");

    if (buffer.length === 0) {
      return null;
    }

    return {
      buffer,
      contentType,
    };
  } catch {
    return null;
  }
}

function getEmbeddedImageExtension(contentType: string) {
  switch (contentType.trim().toLowerCase()) {
    case "image/apng":
      return "apng";
    case "image/avif":
      return "avif";
    case "image/gif":
      return "gif";
    case "image/jpeg":
      return "jpg";
    case "image/png":
      return "png";
    case "image/svg+xml":
      return "svg";
    case "image/webp":
      return "webp";
    default:
      return "png";
  }
}

function buildEmbeddedImageFilename(sequence: number, contentType: string) {
  return `embedded-image-${sequence}.${getEmbeddedImageExtension(contentType)}`;
}

async function ensureTempUploadDirectory() {
  await mkdir(getTempUploadDirectory(), { recursive: true });
}

async function getUserIdByEmail(email: string) {
  await ensureOfficialMailSchema();
  const pool = getDbPool();
  const [rows] = await pool.query<UserLookupRow[]>(
    `
      SELECT id
      FROM users
      WHERE email = ?
      LIMIT 1
    `,
    [email.trim().toLowerCase()],
  );

  return rows[0]?.id ?? null;
}

function toComposeUploadCleanupTarget(row: ComposeUploadRow): ComposeUploadCleanupTarget {
  return {
    id: row.id,
    multipartUploadId: row.multipart_upload_id,
    publicUrl: row.public_url?.trim() || null,
    storageKey: row.storage_key?.trim() || null,
    tempPath: row.temp_path.trim(),
  };
}

async function updateComposeUploadStorageCleanupState(
  queryable: SqlQueryable,
  rowId: number,
  input: {
    clearObjectStorage?: boolean;
    clearMultipart?: boolean;
    clearTempPath?: boolean;
  },
) {
  const updates = ["updated_at = NOW()"];

  if (input.clearTempPath) {
    updates.push("temp_path = ''");
  }

  if (input.clearObjectStorage) {
    updates.push("storage_bucket = NULL", "storage_key = NULL", "public_url = NULL");
  }
  if (input.clearMultipart) {
    updates.push("multipart_upload_id = NULL");
  }

  await queryable.query(
    `
      UPDATE mailbox_uploaded_assets
      SET ${updates.join(", ")}
      WHERE id = ?
    `,
    [rowId],
  );
}

export async function cleanupComposeUploadArtifacts(
  queryable: SqlQueryable,
  targets: ComposeUploadCleanupTarget[],
) {
  for (const target of targets) {
    const tempPath = target.tempPath.trim();
    const storageKey = target.storageKey?.trim() || null;

    let clearTempPath = false;
    let clearObjectStorage = false;
    let clearMultipart = false;

    if (tempPath) {
      await unlink(tempPath).catch(() => undefined);
      clearTempPath = true;
    }

    if (storageKey && target.multipartUploadId) {
      try {
        await abortPrivateMultipartUpload({ key: storageKey, uploadId: target.multipartUploadId });
        clearMultipart = true;
      } catch {
        clearMultipart = false;
      }
    }

    if (storageKey && (!target.multipartUploadId || clearMultipart)) {
      try {
        await deleteObjectFromObjectStorage({ key: storageKey });
        clearObjectStorage = true;
      } catch {
        clearObjectStorage = false;
      }
    }

    if (clearTempPath || clearObjectStorage || clearMultipart) {
      await updateComposeUploadStorageCleanupState(queryable, target.id, {
        clearObjectStorage,
        clearMultipart,
        clearTempPath,
      });
    }
  }
}

async function getComposeUploadsByIdsForUser(userId: number, uploadIds: number[]) {
  if (uploadIds.length === 0) {
    return [];
  }

  const pool = getDbPool();
  const [rows] = await pool.query<ComposeUploadRow[]>(
    `
      SELECT
        ${COMPOSE_UPLOAD_SELECT_COLUMNS}
      FROM mailbox_uploaded_assets
      WHERE owner_user_id = ?
        AND id IN (${uploadIds.map(() => "?").join(", ")})
        AND storage_status <> 'deleted'
    `,
    [userId, ...uploadIds],
  );

  return rows;
}

async function getComposeUploadByTokenForUser(userId: number, token: string) {
  const pool = getDbPool();
  const [rows] = await pool.query<ComposeUploadRow[]>(
    `
      SELECT
        ${COMPOSE_UPLOAD_SELECT_COLUMNS}
      FROM mailbox_uploaded_assets
      WHERE owner_user_id = ?
        AND temp_token = ?
      LIMIT 1
    `,
    [userId, token],
  );

  return rows[0] ?? null;
}

async function getComposeUploadsByTokensForUser(userId: number, tokens: string[]) {
  const normalizedTokens = [
    ...new Set(tokens.map((token) => token.trim().toLowerCase()).filter(Boolean)),
  ];

  if (normalizedTokens.length === 0) {
    return [];
  }

  const pool = getDbPool();
  const [rows] = await pool.query<ComposeUploadRow[]>(
    `
      SELECT
        ${COMPOSE_UPLOAD_SELECT_COLUMNS}
      FROM mailbox_uploaded_assets
      WHERE owner_user_id = ?
        AND temp_token IN (${normalizedTokens.map(() => "?").join(", ")})
        AND upload_kind = 'inline-image'
        AND storage_status <> 'deleted'
    `,
    [userId, ...normalizedTokens],
  );

  return rows;
}

export async function markComposeUploadsDeletedByMessageIds(
  queryable: SqlQueryable,
  messageIds: number[],
) {
  const normalizedMessageIds = [...new Set(messageIds.filter((value) => Number.isInteger(value) && value > 0))];

  if (normalizedMessageIds.length === 0) {
    return [] as ComposeUploadCleanupTarget[];
  }

  const [rows] = await queryable.query<ComposeUploadRow[]>(
    `
      SELECT
        ${COMPOSE_UPLOAD_SELECT_COLUMNS}
      FROM mailbox_uploaded_assets
      WHERE mailbox_message_id IN (${normalizedMessageIds.map(() => "?").join(", ")})
        AND storage_status <> 'deleted'
    `,
    normalizedMessageIds,
  );

  if (rows.length === 0) {
    return [] as ComposeUploadCleanupTarget[];
  }

  // A published large attachment is a File Drawer asset, not a disposable
  // copy of the Sent message. Keep its link alive until expiry, the download
  // limit, or an explicit File Drawer deletion.
  const retainedRows = rows.filter(
    (row) => row.large_attachment === 1 && row.published_at !== null,
  );
  const deletedRows = rows.filter(
    (row) => row.large_attachment !== 1 || row.published_at === null,
  );

  if (retainedRows.length > 0) {
    await queryable.query(
      `UPDATE mailbox_uploaded_assets
       SET mailbox_message_id = NULL, updated_at = NOW()
       WHERE id IN (${retainedRows.map(() => "?").join(", ")})`,
      retainedRows.map((row) => row.id),
    );
  }

  if (deletedRows.length === 0) {
    return [] as ComposeUploadCleanupTarget[];
  }

  await queryable.query(
    `
      UPDATE mailbox_uploaded_assets
      SET
        mailbox_message_id = NULL,
        storage_status = 'deleted',
        deleted_at = NOW(),
        updated_at = NOW()
      WHERE id IN (${deletedRows.map(() => "?").join(", ")})
    `,
    deletedRows.map((row) => row.id),
  );

  return deletedRows.map(toComposeUploadCleanupTarget);
}

export async function relinkComposeUploadsToMessageId(
  queryable: SqlQueryable,
  fromMessageIds: number[],
  keepMessageId: number,
) {
  const normalizedMessageIds = [...new Set(fromMessageIds.filter((value) => Number.isInteger(value) && value > 0))];

  if (normalizedMessageIds.length === 0 || !Number.isInteger(keepMessageId) || keepMessageId < 1) {
    return;
  }

  await queryable.query(
    `
      UPDATE mailbox_uploaded_assets
      SET
        mailbox_message_id = ?,
        updated_at = NOW()
      WHERE mailbox_message_id IN (${normalizedMessageIds.map(() => "?").join(", ")})
    `,
    [keepMessageId, ...normalizedMessageIds],
  );
}

async function persistTemporaryUpload(input: {
  buffer: Buffer;
  contentType: string;
  filename: string;
  kind: ComposeUploadKind;
  sizeBytes: number;
  userId: number;
}) {
  await ensureTempUploadDirectory();

  const contentType = normalizeUploadContentType(input.contentType);
  const token = randomBytes(24).toString("hex");
  const extension = getFilenameExtension(input.filename);
  const tempFileName = extension ? `${token}.${extension}` : token;
  const tempPath = path.join(getTempUploadDirectory(), tempFileName);

  await writeFile(tempPath, input.buffer);

  const expiresAt = new Date(Date.now() + readUploadTtlHours() * 60 * 60 * 1000);
  const pool = getDbPool();
  const [result] = await pool.query<mysql.ResultSetHeader>(
    `
      INSERT INTO mailbox_uploaded_assets (
        owner_user_id,
        temp_token,
        upload_kind,
        storage_status,
        original_name,
        mime_type,
        size_bytes,
        temp_path,
        expires_at
      ) VALUES (?, ?, ?, 'temporary', ?, ?, ?, ?, ?)
    `,
    [
      input.userId,
      token,
      input.kind,
      input.filename,
      contentType,
      input.sizeBytes,
      tempPath,
      expiresAt,
    ],
  );

  return {
    contentType,
    filename: input.filename,
    id: result.insertId,
    isImage: isImageContentType(input.contentType),
    kind: input.kind,
    previewUrl: buildComposeUploadPreviewPath(token),
    sizeBytes: input.sizeBytes,
    token,
  } satisfies ComposeUploadClientItem;
}

async function finalizeComposeUpload(row: ComposeUploadRow): Promise<FinalizedComposeUpload> {
  if (row.storage_status === "finalized" && row.public_url) {
    return {
      contentType: row.mime_type,
      filename: row.original_name,
      id: row.id,
      kind: row.upload_kind,
      publicUrl: row.public_url,
      sizeBytes: row.size_bytes,
      token: row.temp_token,
    };
  }

  const content = await readFile(row.temp_path);
  const date = row.created_at instanceof Date ? row.created_at : new Date();
  const extension = getFilenameExtension(row.original_name);
  const safeBaseName = normalizeFilename(path.basename(row.original_name, path.extname(row.original_name)), "file")
    .replace(/\s+/g, "-")
    .replace(/[^a-zA-Z0-9-_]+/g, "")
    .slice(0, 48) || "file";
  const key = [
    process.env.MAIL_COMPOSE_STORAGE_PREFIX?.trim() || "official-mail/compose",
    `${date.getUTCFullYear()}`,
    `${date.getUTCMonth() + 1}`.padStart(2, "0"),
    `${date.getUTCDate()}`.padStart(2, "0"),
    row.upload_kind,
    extension ? `${row.temp_token}-${safeBaseName}.${extension}` : `${row.temp_token}-${safeBaseName}`,
  ].join("/");
  const uploaded = await uploadBufferToObjectStorage({
    body: content,
    cacheControl: "public, max-age=31536000, immutable",
    contentType: row.mime_type || "application/octet-stream",
    key,
  });

  const pool = getDbPool();
  await pool.query(
    `
      UPDATE mailbox_uploaded_assets
      SET
        storage_status = 'finalized',
        storage_bucket = ?,
        storage_key = ?,
        public_url = ?,
        finalized_at = NOW(),
        updated_at = NOW()
      WHERE id = ?
    `,
    [uploaded.bucketName, uploaded.key, uploaded.publicUrl, row.id],
  );

  return {
    contentType: row.mime_type,
    filename: row.original_name,
    id: row.id,
    kind: row.upload_kind,
    publicUrl: uploaded.publicUrl,
    sizeBytes: row.size_bytes,
    token: row.temp_token,
  };
}

function buildAttachmentSectionHtml(uploads: FinalizedComposeUpload[]) {
  if (uploads.length === 0) {
    return "";
  }

  const items = uploads
    .map(
      (upload) =>
        `<li style="margin:0 0 10px;"><a href="${escapeHtml(upload.publicUrl)}" style="color:#2f6df6;text-decoration:none;font-weight:600;" target="_blank" rel="noopener noreferrer">${escapeHtml(upload.filename)}</a><span style="margin-left:8px;color:#7a8ba7;font-size:13px;">${formatAttachmentSize(upload.sizeBytes)}</span></li>`,
    )
    .join("");

  return `<div class="official-mail-attachment-links" style="margin-top:24px;padding-top:18px;border-top:1px solid #e4ebf8;"><p style="margin:0 0 12px;color:#30425f;font-size:14px;font-weight:700;">첨부 파일</p><ul style="margin:0;padding:0;list-style:none;">${items}</ul></div>`;
}

function buildLargeAttachmentSectionHtml(uploads: FinalizedComposeUpload[], bodyText: string) {
  if (uploads.length === 0) {
    return "";
  }

  const previewText = bodyText.replace(/\s+/g, " ").trim().slice(0, 140);
  const preview = previewText
    ? `<span style="display:none;max-height:0;overflow:hidden;opacity:0;color:transparent;font-size:0;line-height:0;">${escapeHtml(previewText)}</span>`
    : "";
  const totalSize = uploads.reduce((sum, upload) => sum + upload.sizeBytes, 0);
  const items = uploads.map((upload, index) => {
    const expiresAt = upload.expiresAt?.toLocaleDateString("sv-SE", { timeZone: "Asia/Seoul" }) ?? "";
    const rowBorder = index ? "border-top:1px solid #e9eef7;" : "";
    return `<tr><td style="width:68%;padding:10px 12px;vertical-align:middle;${rowBorder}"><span style="display:inline-block;margin-right:9px;border-radius:3px;background:#2f6df6;padding:5px 6px;color:#fff;font-size:10px;font-weight:700;">파일</span><a href="${escapeHtml(upload.publicUrl)}" target="_blank" rel="noopener noreferrer" style="color:#223450;font-size:12px;font-weight:600;text-decoration:none;word-break:break-all;">${escapeHtml(upload.filename)}</a><span style="margin-left:6px;color:#8290a6;font-size:11px;white-space:nowrap;">${formatAttachmentSize(upload.sizeBytes)}</span></td><td style="width:18%;padding:10px 4px;text-align:right;vertical-align:middle;${rowBorder}"><span style="color:#c45663;font-size:11px;white-space:nowrap;">${expiresAt ? `${escapeHtml(expiresAt)}까지` : ""}</span></td><td style="width:14%;padding:10px 12px 10px 6px;text-align:right;vertical-align:middle;${rowBorder}"><a href="${escapeHtml(upload.publicUrl)}" target="_blank" rel="noopener noreferrer" style="color:#2f6df6;font-size:11px;font-weight:700;text-decoration:none;white-space:nowrap;">다운로드</a></td></tr>`;
  }).join("");

  return `<div class="official-mail-attachment-links" style="margin:0 0 24px;max-width:760px;font-family:Arial,Malgun Gothic,sans-serif;">${preview}<p style="margin:0 0 8px;color:#30425f;font-size:13px;font-weight:700;">대용량 첨부 ${uploads.length}개 <span style="color:#8a97aa;font-weight:400;">${formatAttachmentSize(totalSize)}</span></p><table role="presentation" cellpadding="0" cellspacing="0" style="width:100%;table-layout:fixed;border:1px solid #dfe7f2;border-collapse:collapse;background:#fff;"><tbody>${items}<tr><td colspan="3" style="padding:9px 12px;border-top:1px solid #e9eef7;color:#8491a4;font-size:11px;">대용량 파일은 최대 ${LARGE_ATTACHMENT_RETENTION_DAYS}일 보관 · ${LARGE_ATTACHMENT_DOWNLOAD_LIMIT}회 다운로드할 수 있습니다.</td></tr></tbody></table></div>`;
}

function buildAttachmentSectionText(uploads: FinalizedComposeUpload[]) {
  if (uploads.length === 0) {
    return "";
  }

  return ["첨부 파일", ...uploads.map((upload) => `- ${upload.filename} (${formatAttachmentSize(upload.sizeBytes)}) ${upload.publicUrl}`)].join(
    "\n",
  );
}

export function appendLargeComposeAttachmentLinks(input: {
  bodyHtml: string | null;
  bodyText: string;
  uploads: FinalizedComposeUpload[];
}) {
  if (input.uploads.length === 0) return {
    bodyHtml: input.bodyHtml,
    bodyText: input.bodyText,
  };
  return {
    bodyHtml: `${buildLargeAttachmentSectionHtml(input.uploads, input.bodyText)}${input.bodyHtml ?? plainTextToHtml(input.bodyText) ?? ""}`,
    bodyText: [input.bodyText.trim(), buildAttachmentSectionText(input.uploads)]
      .filter(Boolean).join("\n\n"),
  };
}

export async function storeLargeComposeAttachmentsForUserEmail(input: {
  email: string;
  files: Array<{ buffer: Buffer; contentType: string; filename: string }>;
}) {
  const userId = await getUserIdByEmail(input.email);
  if (!userId) throw new Error("user-not-found");
  const [ownerRows] = await getDbPool().query<(RowDataPacket & { owner_user_id: number })[]>(
    `SELECT owner_user_id FROM managed_team_mailboxes
     WHERE email = ? AND status = 'active' LIMIT 1`,
    [input.email.trim().toLowerCase()],
  );
  const billingOwnerId = ownerRows[0]?.owner_user_id ?? userId;
  const tier = await getMailPlanQuotaTierForOwnerUserId(billingOwnerId);
  if (tier === "free" || !(await hasActiveGrowthPlanByOwnerEmail(input.email))) {
    throw new Error("attachment-growth-required");
  }
  const quotaBytes = getFileDrawerQuotaBytesForTier(tier);
  const uploads: FinalizedComposeUpload[] = [];

  for (const file of input.files) {
    if (file.buffer.length < 1 || file.buffer.length > GROWTH_LARGE_ATTACHMENT_LIMIT_BYTES) {
      throw new Error("attachment-large-limit");
    }
    const filename = normalizeFilename(file.filename, "attachment");
    const contentType = normalizeUploadContentType(file.contentType);
    const token = randomBytes(24).toString("hex");
    const key = `mailbox/compose-large/${userId}/${token}/${encodeURIComponent(filename)}`;
    const expiresAt = new Date(Date.now() + LARGE_ATTACHMENT_RETENTION_DAYS * 24 * 60 * 60 * 1000);
    const connection = await getDbPool().getConnection();
    let uploadId = 0;
    try {
      await connection.beginTransaction();
      // Both File Drawer and compose reservations lock this user row before
      // checking the shared quota, so concurrent uploads cannot overbook it.
      await connection.query("SELECT id FROM users WHERE id = ? FOR UPDATE", [userId]);
      const [usageRows] = await connection.query<(RowDataPacket & { size_bytes: number })[]>(
        `SELECT
          (SELECT COALESCE(SUM(size_bytes), 0) FROM mailbox_file_assets
           WHERE owner_user_id = ? AND storage_status IN ('uploading', 'active')) +
          (SELECT COALESCE(SUM(size_bytes), 0) FROM mailbox_uploaded_assets
           WHERE owner_user_id = ? AND large_attachment = 1
             AND storage_status IN ('temporary', 'finalized') AND expires_at > NOW()
             AND download_count < ?) AS size_bytes`,
        [userId, userId, LARGE_ATTACHMENT_DOWNLOAD_LIMIT],
      );
      if (Number(usageRows[0]?.size_bytes ?? 0) + file.buffer.length > quotaBytes) {
        throw new Error("attachment-drawer-quota");
      }
      const [result] = await connection.query<mysql.ResultSetHeader>(
        `INSERT INTO mailbox_uploaded_assets (
          owner_user_id, temp_token, upload_kind, storage_status,
          original_name, mime_type, size_bytes, temp_path,
          storage_key, large_attachment, expires_at
        ) VALUES (?, ?, 'attachment', 'temporary', ?, ?, ?, '', ?, 1, ?)`,
        [userId, token, filename, contentType, file.buffer.length, key, expiresAt],
      );
      uploadId = result.insertId;
      await connection.commit();
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }

    try {
      const stored = await uploadPrivateBufferToObjectStorage({ body: file.buffer, contentType, key });
      const [result] = await getDbPool().query<mysql.ResultSetHeader>(
        `UPDATE mailbox_uploaded_assets
         SET storage_status = 'finalized', storage_bucket = ?, finalized_at = NOW(), updated_at = NOW()
         WHERE id = ? AND storage_status = 'temporary'`,
        [stored.bucketName, uploadId],
      );
      if (result.affectedRows !== 1) throw new Error("attachment-reservation-lost");
      uploads.push({
        contentType,
        expiresAt,
        filename,
        id: uploadId,
        kind: "attachment",
        publicUrl: getMailAbsoluteUrl(`/api/mailbox/large-attachments/${token}`),
        sizeBytes: file.buffer.length,
        token,
      });
    } catch (error) {
      await getDbPool().query(
        `UPDATE mailbox_uploaded_assets
         SET storage_status = 'deleted', deleted_at = NOW(), updated_at = NOW()
         WHERE id = ? AND storage_status = 'temporary'`,
        [uploadId],
      ).catch(() => undefined);
      await deleteObjectFromObjectStorage({ key }).catch(() => undefined);
      throw error;
    }
  }
  return uploads;
}

export async function storeLargeComposeAttachmentStreamForUserEmail(input: {
  body: ReadableStream<Uint8Array>;
  contentType: string;
  email: string;
  filename: string;
  sizeBytes: number;
}) {
  if (!Number.isSafeInteger(input.sizeBytes) || input.sizeBytes < 1 ||
      input.sizeBytes > GROWTH_LARGE_ATTACHMENT_LIMIT_BYTES) {
    throw new Error("attachment-large-limit");
  }
  await ensureOfficialMailSchema();
  const userId = await getUserIdByEmail(input.email);
  if (!userId) throw new Error("user-not-found");
  const [ownerRows] = await getDbPool().query<(RowDataPacket & { owner_user_id: number })[]>(
    `SELECT owner_user_id FROM managed_team_mailboxes
     WHERE email = ? AND status = 'active' LIMIT 1`,
    [input.email.trim().toLowerCase()],
  );
  const billingOwnerId = ownerRows[0]?.owner_user_id ?? userId;
  const tier = await getMailPlanQuotaTierForOwnerUserId(billingOwnerId);
  if (tier === "free" || !(await hasActiveGrowthPlanByOwnerEmail(input.email))) {
    throw new Error("attachment-growth-required");
  }

  const filename = normalizeFilename(input.filename, "attachment");
  const contentType = normalizeUploadContentType(input.contentType);
  const token = randomBytes(24).toString("hex");
  const key = `mailbox/compose-large/${userId}/${token}/${encodeURIComponent(filename)}`;
  const expiresAt = new Date(Date.now() + LARGE_ATTACHMENT_RETENTION_DAYS * 24 * 60 * 60 * 1000);
  const connection = await getDbPool().getConnection();
  let uploadId = 0;
  try {
    await connection.beginTransaction();
    await connection.query("SELECT id FROM users WHERE id = ? FOR UPDATE", [userId]);
    const [usageRows] = await connection.query<(RowDataPacket & { size_bytes: number })[]>(
      `SELECT
        (SELECT COALESCE(SUM(size_bytes), 0) FROM mailbox_file_assets
         WHERE owner_user_id = ? AND storage_status IN ('uploading', 'active')) +
        (SELECT COALESCE(SUM(size_bytes), 0) FROM mailbox_uploaded_assets
         WHERE owner_user_id = ? AND large_attachment = 1
           AND storage_status IN ('temporary', 'finalized') AND expires_at > NOW()
           AND download_count < ?) AS size_bytes`,
      [userId, userId, LARGE_ATTACHMENT_DOWNLOAD_LIMIT],
    );
    if (Number(usageRows[0]?.size_bytes ?? 0) + input.sizeBytes > getFileDrawerQuotaBytesForTier(tier)) {
      throw new Error("attachment-drawer-quota");
    }
    const [result] = await connection.query<mysql.ResultSetHeader>(
      `INSERT INTO mailbox_uploaded_assets (
        owner_user_id, temp_token, upload_kind, storage_status,
        original_name, mime_type, size_bytes, temp_path,
        storage_key, large_attachment, expires_at
      ) VALUES (?, ?, 'attachment', 'temporary', ?, ?, ?, '', ?, 1, ?)`,
      [userId, token, filename, contentType, input.sizeBytes, key, expiresAt],
    );
    uploadId = result.insertId;
    await connection.commit();
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }

  try {
    const stored = await uploadPrivateStreamToObjectStorage({
      body: input.body,
      contentType,
      expectedBytes: input.sizeBytes,
      key,
    });
    const [result] = await getDbPool().query<mysql.ResultSetHeader>(
      `UPDATE mailbox_uploaded_assets
       SET storage_status = 'finalized', storage_bucket = ?, finalized_at = NOW(), updated_at = NOW()
       WHERE id = ? AND storage_status = 'temporary'`,
      [stored.bucketName, uploadId],
    );
    if (result.affectedRows !== 1) throw new Error("attachment-reservation-lost");
    return {
      contentType,
      expiresAt,
      filename,
      id: uploadId,
      kind: "attachment" as const,
      publicUrl: getMailAbsoluteUrl(`/api/mailbox/large-attachments/${token}`),
      sizeBytes: input.sizeBytes,
      token,
    };
  } catch (error) {
    await getDbPool().query(
      `UPDATE mailbox_uploaded_assets
       SET storage_status = 'deleted', deleted_at = NOW(), updated_at = NOW()
       WHERE id = ? AND storage_status = 'temporary'`,
      [uploadId],
    ).catch(() => undefined);
    await deleteObjectFromObjectStorage({ key }).catch(() => undefined);
    throw error;
  }
}

export async function startDirectLargeComposeAttachmentForUserEmail(input: {
  contentType: string;
  email: string;
  filename: string;
  sizeBytes: number;
}) {
  if (!Number.isSafeInteger(input.sizeBytes) || input.sizeBytes < 1 ||
      input.sizeBytes > GROWTH_LARGE_ATTACHMENT_LIMIT_BYTES) {
    throw new Error("attachment-large-limit");
  }
  await ensureOfficialMailSchema();
  const userId = await getUserIdByEmail(input.email);
  if (!userId) throw new Error("user-not-found");
  const [ownerRows] = await getDbPool().query<(RowDataPacket & { owner_user_id: number })[]>(
    `SELECT owner_user_id FROM managed_team_mailboxes
     WHERE email = ? AND status = 'active' LIMIT 1`,
    [input.email.trim().toLowerCase()],
  );
  const billingOwnerId = ownerRows[0]?.owner_user_id ?? userId;
  const tier = await getMailPlanQuotaTierForOwnerUserId(billingOwnerId);
  if (tier === "free" || !(await hasActiveGrowthPlanByOwnerEmail(input.email))) {
    throw new Error("attachment-growth-required");
  }
  const filename = normalizeFilename(input.filename, "attachment");
  const contentType = normalizeUploadContentType(input.contentType);
  const token = randomBytes(24).toString("hex");
  const key = `mailbox/compose-large/${userId}/${token}/${encodeURIComponent(filename)}`;
  const expiresAt = new Date(Date.now() + LARGE_ATTACHMENT_RETENTION_DAYS * 24 * 60 * 60 * 1000);
  const connection = await getDbPool().getConnection();
  let rowId = 0;
  try {
    await connection.beginTransaction();
    await connection.query("SELECT id FROM users WHERE id = ? FOR UPDATE", [userId]);
    const [usageRows] = await connection.query<(RowDataPacket & { size_bytes: number })[]>(
      `SELECT
        (SELECT COALESCE(SUM(size_bytes), 0) FROM mailbox_file_assets
         WHERE owner_user_id = ? AND storage_status IN ('uploading', 'active')) +
        (SELECT COALESCE(SUM(size_bytes), 0) FROM mailbox_uploaded_assets
         WHERE owner_user_id = ? AND large_attachment = 1
           AND storage_status IN ('temporary', 'finalized') AND expires_at > NOW()
           AND download_count < ?) AS size_bytes`,
      [userId, userId, LARGE_ATTACHMENT_DOWNLOAD_LIMIT],
    );
    if (Number(usageRows[0]?.size_bytes ?? 0) + input.sizeBytes > getFileDrawerQuotaBytesForTier(tier)) {
      throw new Error("attachment-drawer-quota");
    }
    const [result] = await connection.query<mysql.ResultSetHeader>(
      `INSERT INTO mailbox_uploaded_assets (
        owner_user_id, temp_token, upload_kind, storage_status,
        original_name, mime_type, size_bytes, temp_path,
        storage_key, large_attachment, expires_at
      ) VALUES (?, ?, 'attachment', 'temporary', ?, ?, ?, '', ?, 1, ?)`,
      [userId, token, filename, contentType, input.sizeBytes, key, expiresAt],
    );
    rowId = result.insertId;
    await connection.commit();
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
  let uploadId: string | null = null;
  try {
    const created = await createPrivateMultipartUpload({ contentType, key });
    uploadId = created.uploadId;
    const [result] = await getDbPool().query<mysql.ResultSetHeader>(
      `UPDATE mailbox_uploaded_assets
       SET storage_bucket = ?, multipart_upload_id = ?, updated_at = NOW()
       WHERE id = ? AND storage_status = 'temporary'`,
      [created.bucketName, uploadId, rowId],
    );
    if (result.affectedRows !== 1) throw new Error("attachment-reservation-lost");
    const parts = await presignPrivateMultipartParts({
      key,
      partCount: Math.ceil(input.sizeBytes / DIRECT_MULTIPART_PART_BYTES),
      uploadId,
    });
    return { id: rowId, partBytes: DIRECT_MULTIPART_PART_BYTES, parts, token };
  } catch (error) {
    await getDbPool().query(
      `UPDATE mailbox_uploaded_assets SET storage_status = 'deleted', deleted_at = NOW(),
       updated_at = NOW()
       WHERE id = ? AND storage_status = 'temporary'`,
      [rowId],
    ).catch(() => undefined);
    if (uploadId) {
      await abortPrivateMultipartUpload({ key, uploadId }).then(() =>
        getDbPool().query(
          `UPDATE mailbox_uploaded_assets SET multipart_upload_id = NULL WHERE id = ?`,
          [rowId],
        ),
      ).catch(() => undefined);
    }
    throw error;
  }
}

export async function completeDirectLargeComposeAttachmentForUserEmail(input: {
  email: string;
  token: string;
}) {
  await ensureOfficialMailSchema();
  const userId = await getUserIdByEmail(input.email);
  if (!userId) throw new Error("user-not-found");
  const connection = await getDbPool().getConnection();
  try {
    await connection.beginTransaction();
    const [rows] = await connection.query<ComposeUploadRow[]>(
      `SELECT * FROM mailbox_uploaded_assets
       WHERE owner_user_id = ? AND temp_token = ? AND large_attachment = 1
       FOR UPDATE`,
      [userId, input.token],
    );
    const row = rows[0];
    if (!row || row.storage_status !== "temporary" || !row.storage_key ||
        !row.multipart_upload_id || Date.now() - row.created_at.getTime() > 60 * 60 * 1000) {
      throw new Error("upload-session-unavailable");
    }
    const stored = await completePrivateMultipartUpload({
      expectedBytes: row.size_bytes,
      key: row.storage_key,
      uploadId: row.multipart_upload_id,
    });
    await connection.query(
      `UPDATE mailbox_uploaded_assets SET storage_status = 'finalized',
       storage_bucket = ?, multipart_upload_id = NULL, finalized_at = NOW(), updated_at = NOW()
       WHERE id = ?`,
      [stored.bucketName, row.id],
    );
    await connection.commit();
    return { id: row.id, name: row.original_name, sizeBytes: row.size_bytes };
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

export async function abortDirectLargeComposeAttachmentForUserEmail(input: {
  email: string;
  token: string;
}) {
  await ensureOfficialMailSchema();
  const userId = await getUserIdByEmail(input.email);
  if (!userId) return;
  const [rows] = await getDbPool().query<ComposeUploadRow[]>(
    `SELECT * FROM mailbox_uploaded_assets
     WHERE owner_user_id = ? AND temp_token = ? AND large_attachment = 1 LIMIT 1`,
    [userId, input.token],
  );
  const row = rows[0];
  if (!row || row.storage_status !== "temporary") return;
  await getDbPool().query(
    `UPDATE mailbox_uploaded_assets SET storage_status = 'deleted', deleted_at = NOW(),
     updated_at = NOW()
     WHERE id = ? AND storage_status = 'temporary'`,
    [row.id],
  );
  if (row.storage_key && row.multipart_upload_id) {
    await abortPrivateMultipartUpload({ key: row.storage_key, uploadId: row.multipart_upload_id })
      .then(() => getDbPool().query(
        `UPDATE mailbox_uploaded_assets SET multipart_upload_id = NULL WHERE id = ?`,
        [row.id],
      ))
      .catch(() => undefined);
  }
}

export async function getLargeComposeAttachmentsForUserEmail(email: string, uploadIds: number[]) {
  const ids = normalizeUploadIds(uploadIds);
  if (ids.length === 0) return [] as FinalizedComposeUpload[];
  if (ids.length > 100) throw new Error("attachment-count-limit");
  const userId = await getUserIdByEmail(email);
  if (!userId) throw new Error("user-not-found");
  const rows = await getComposeUploadsByIdsForUser(userId, ids);
  const byId = new Map(rows.map((row) => [row.id, row]));
  return ids.map((id) => {
    const row = byId.get(id);
    if (!row || row.large_attachment !== 1 || row.storage_status !== "finalized" ||
        row.size_bytes < 1 || row.size_bytes > GROWTH_LARGE_ATTACHMENT_LIMIT_BYTES ||
        !row.storage_key || !row.expires_at || row.expires_at.getTime() <= Date.now() ||
        row.download_count >= LARGE_ATTACHMENT_DOWNLOAD_LIMIT) {
      throw new Error("attachment-large-unavailable");
    }
    return {
      contentType: row.mime_type,
      expiresAt: row.expires_at,
      filename: row.original_name,
      id: row.id,
      kind: "attachment" as const,
      publicUrl: getMailAbsoluteUrl(`/api/mailbox/large-attachments/${row.temp_token}`),
      sizeBytes: row.size_bytes,
      token: row.temp_token,
    };
  });
}

export async function discardUnsentLargeComposeAttachmentsForUserEmail(email: string, uploadIds: number[]) {
  const ids = normalizeUploadIds(uploadIds);
  if (ids.length === 0) return 0;
  if (ids.length > 100) throw new Error("attachment-count-limit");
  await ensureOfficialMailSchema();
  const userId = await getUserIdByEmail(email);
  if (!userId) throw new Error("user-not-found");

  let discarded = 0;
  for (const id of ids) {
    const [rows] = await getDbPool().query<ComposeUploadRow[]>(
      `SELECT ${COMPOSE_UPLOAD_SELECT_COLUMNS}
       FROM mailbox_uploaded_assets
       WHERE id = ? AND owner_user_id = ? AND large_attachment = 1
         AND storage_status = 'finalized' AND published_at IS NULL
         AND mailbox_message_id IS NULL LIMIT 1`,
      [id, userId],
    );
    const row = rows[0];
    if (!row) continue;
    const [result] = await getDbPool().query<mysql.ResultSetHeader>(
      `UPDATE mailbox_uploaded_assets
       SET storage_status = 'deleted', deleted_at = NOW(), updated_at = NOW()
       WHERE id = ? AND owner_user_id = ? AND large_attachment = 1
         AND storage_status = 'finalized' AND published_at IS NULL
         AND mailbox_message_id IS NULL`,
      [id, userId],
    );
    if (result.affectedRows !== 1) continue;
    discarded += 1;
    if (row.storage_key) {
      // A cleanup worker retries if object deletion is temporarily unavailable.
      await deleteObjectFromObjectStorage({ key: row.storage_key }).catch(() => undefined);
    }
  }
  return discarded;
}

export async function openLargeComposeAttachmentByToken(token: string) {
  if (!/^[a-f0-9]{48}$/.test(token)) return null;
  await ensureOfficialMailSchema();
  const pool = getDbPool();
  const [result] = await pool.query<mysql.ResultSetHeader>(
    `UPDATE mailbox_uploaded_assets
     SET download_count = download_count + 1, updated_at = NOW()
     WHERE temp_token = ? AND large_attachment = 1
       AND storage_status = 'finalized' AND published_at IS NOT NULL
       AND expires_at > NOW() AND download_count < ?
       AND storage_key IS NOT NULL`,
    [token, LARGE_ATTACHMENT_DOWNLOAD_LIMIT],
  );
  if (result.affectedRows !== 1) {
    const [expiredRows] = await pool.query<ComposeUploadRow[]>(
      `SELECT ${COMPOSE_UPLOAD_SELECT_COLUMNS}
       FROM mailbox_uploaded_assets
       WHERE temp_token = ? AND large_attachment = 1
         AND storage_status = 'finalized' AND expires_at <= NOW()
       LIMIT 1`,
      [token],
    );
    if (expiredRows[0]) await reclaimExpiredLargeComposeAttachment(expiredRows[0]);
    return null;
  }
  const [rows] = await pool.query<ComposeUploadRow[]>(
    `SELECT ${COMPOSE_UPLOAD_SELECT_COLUMNS}
     FROM mailbox_uploaded_assets WHERE temp_token = ? LIMIT 1`,
    [token],
  );
  const row = rows[0];
  if (!row?.storage_key) return null;
  let object;
  try {
    object = await streamObjectFromObjectStorage({ key: row.storage_key });
  } catch (error) {
    if (row.download_count >= LARGE_ATTACHMENT_DOWNLOAD_LIMIT) {
      await reclaimExpiredLargeComposeAttachment(row);
    }
    throw error;
  }
  if (!object) {
    if (row.download_count >= LARGE_ATTACHMENT_DOWNLOAD_LIMIT) {
      await reclaimExpiredLargeComposeAttachment(row);
    }
    return null;
  }
  if (row.download_count < LARGE_ATTACHMENT_DOWNLOAD_LIMIT) {
    return { ...object, filename: row.original_name };
  }

  // Keep the object available while the 100th recipient is still streaming it.
  // Reclaim it as soon as that response completes or is cancelled.
  const reader = object.body.getReader();
  let reclaimed = false;
  const reclaim = async () => {
    if (reclaimed) return;
    reclaimed = true;
    await reclaimExpiredLargeComposeAttachment(row);
  };
  const body = new ReadableStream<Uint8Array>({
    async pull(controller) {
      try {
        const next = await reader.read();
        if (next.done) {
          await reclaim();
          controller.close();
        } else {
          controller.enqueue(next.value);
        }
      } catch (error) {
        await reclaim();
        controller.error(error);
      }
    },
    async cancel(reason) {
      try {
        await reader.cancel(reason);
      } finally {
        await reclaim();
      }
    },
  });
  return { ...object, body, filename: row.original_name };
}

async function reclaimExpiredLargeComposeAttachment(row: ComposeUploadRow) {
  const pool = getDbPool();
  const [result] = await pool.query<mysql.ResultSetHeader>(
    `UPDATE mailbox_uploaded_assets
     SET storage_status = 'deleted', deleted_at = NOW(), updated_at = NOW()
     WHERE id = ? AND large_attachment = 1 AND storage_status = 'finalized'
       AND (expires_at <= NOW() OR download_count >= ?)`,
    [row.id, LARGE_ATTACHMENT_DOWNLOAD_LIMIT],
  );
  if (result.affectedRows === 1) {
    await cleanupComposeUploadArtifacts(pool, [toComposeUploadCleanupTarget(row)]);
  }
}

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

function buildComposeUploadFinalizeProgressItem(
  row: ComposeUploadRow,
): ComposeUploadFinalizeProgressItem {
  return {
    filename: row.original_name,
    id: row.id,
    kind: row.upload_kind,
    sizeBytes: row.size_bytes,
    status: "waiting",
    uploadedBytes: 0,
  };
}

function buildComposeUploadFinalizeProgressSnapshot(
  items: ComposeUploadFinalizeProgressItem[],
): ComposeUploadFinalizeProgressSnapshot {
  return {
    completedBytes: items.reduce((sum, item) => sum + item.uploadedBytes, 0),
    items: items.map((item) => ({ ...item })),
    totalBytes: items.reduce((sum, item) => sum + item.sizeBytes, 0),
    totalCount: items.length,
    uploadedCount: items.filter((item) => item.status === "complete").length,
  };
}

export async function createTemporaryComposeUploadsForUserEmail(input: {
  email: string;
  files: Array<{
    buffer: Buffer;
    contentType: string;
    filename: string;
    sizeBytes: number;
  }>;
  kind: ComposeUploadKind;
}) {
  const userId = await getUserIdByEmail(input.email);

  if (!userId) {
    throw new Error("user-not-found");
  }

  const maxUploadBytes = readMaxUploadBytes();
  const createdUploads: ComposeUploadClientItem[] = [];

  for (const file of input.files) {
    const contentType = normalizeUploadContentType(file.contentType);
    const filename = normalizeFilename(
      file.filename,
      input.kind === "inline-image" ? "image" : "attachment",
    );

    if (file.sizeBytes < 1) {
      throw new Error("upload-empty");
    }

    if (file.sizeBytes > maxUploadBytes) {
      throw new Error("upload-too-large");
    }

    if (input.kind === "inline-image" && !isImageContentType(contentType)) {
      throw new Error("upload-image-only");
    }

    createdUploads.push(
      await persistTemporaryUpload({
        buffer: file.buffer,
        contentType,
        filename,
        kind: input.kind,
        sizeBytes: file.sizeBytes,
        userId,
      }),
    );
  }

  return createdUploads;
}

export async function deleteComposeUploadByTokenForUserEmail(email: string, token: string) {
  const userId = await getUserIdByEmail(email);

  if (!userId) {
    throw new Error("user-not-found");
  }

  const row = await getComposeUploadByTokenForUser(userId, token.trim());

  if (!row || row.storage_status === "deleted") {
    return false;
  }

  const pool = getDbPool();
  await pool.query(
    `
      UPDATE mailbox_uploaded_assets
      SET
        mailbox_message_id = NULL,
        storage_status = 'deleted',
        deleted_at = NOW(),
        updated_at = NOW()
      WHERE id = ?
    `,
    [row.id],
  );
  await cleanupComposeUploadArtifacts(pool, [toComposeUploadCleanupTarget(row)]);

  return true;
}

export async function getComposeUploadPayloadByTokenForUserEmail(email: string, token: string) {
  const userId = await getUserIdByEmail(email);

  if (!userId) {
    throw new Error("user-not-found");
  }

  const row = await getComposeUploadByTokenForUser(userId, token.trim());

  if (!row || row.storage_status === "deleted") {
    return null;
  }

  if (row.large_attachment === 1) return null;

  if (row.storage_status === "finalized" && row.public_url) {
    return {
      content: null,
      contentType: row.mime_type,
      filename: row.original_name,
      publicUrl: row.public_url,
    };
  }

  return {
    content: await readFile(row.temp_path),
    contentType: row.mime_type,
    filename: row.original_name,
    publicUrl: null,
  };
}

async function readComposeUploadContentFromRow(row: ComposeUploadRow) {
  if (row.storage_key?.trim()) {
    try {
      const storedObject = await readObjectFromObjectStorage({ key: row.storage_key.trim() });

      if (storedObject?.body) {
        return {
          content: storedObject.body,
          contentType: normalizeAttachmentContentType(storedObject.contentType ?? row.mime_type, row.original_name),
        };
      }
    } catch {
      // Fall through to public URL or temp file fallback when object storage read is unavailable.
    }
  }

  if (row.public_url?.trim()) {
    try {
      const response = await fetch(row.public_url.trim(), {
        cache: "no-store",
      });

      if (response.ok) {
        return {
          content: Buffer.from(await response.arrayBuffer()),
          contentType: normalizeAttachmentContentType(response.headers.get("content-type") ?? row.mime_type, row.original_name),
        };
      }
    } catch {
      // Fall through to temp file fallback when public URL fetch is unavailable.
    }
  }

  const tempPath = row.temp_path.trim();

  if (tempPath) {
    try {
      await access(tempPath);
      return {
        content: await readFile(tempPath),
        contentType: normalizeAttachmentContentType(row.mime_type, row.original_name),
      };
    } catch {
      // Ignore and return null below.
    }
  }

  return null;
}

export async function finalizeComposeUploadsForUserEmail(input: {
  attachmentUploadIds: number[];
  bodyHtml: string | null;
  bodyText: string;
  email: string;
  inlineImageUploadIds: number[];
  onProgress?: (snapshot: ComposeUploadFinalizeProgressSnapshot) => Promise<void> | void;
}) {
  const userId = await getUserIdByEmail(input.email);

  if (!userId) {
    throw new Error("user-not-found");
  }

  const attachmentUploadIds = normalizeUploadIds(input.attachmentUploadIds);
  const inlineImageUploadIds = normalizeUploadIds(input.inlineImageUploadIds);
  const requestedUploadIds = normalizeUploadIds([...attachmentUploadIds, ...inlineImageUploadIds]);

  if (requestedUploadIds.length === 0) {
    return {
      attachmentUploads: [] as FinalizedComposeUpload[],
      bodyHtml: input.bodyHtml,
      bodyText: input.bodyText,
      finalizedUploadIds: [] as number[],
    };
  }

  const rows = await getComposeUploadsByIdsForUser(userId, requestedUploadIds);
  const rowById = new Map(rows.map((row) => [row.id, row]));
  const finalizedUploadIds: number[] = [];
  const attachmentUploads: FinalizedComposeUpload[] = [];
  const sanitizedBaseHtml =
    sanitizeMailboxHtml(input.bodyHtml, input.bodyText) ?? plainTextToHtml(input.bodyText) ?? "";
  let nextBodyHtml = sanitizedBaseHtml;
  let nextBodyText = input.bodyText.trim();
  const inlineRowsToFinalize = inlineImageUploadIds
    .map((uploadId) => rowById.get(uploadId) ?? null)
    .filter((row): row is ComposeUploadRow => Boolean(row))
    .filter((row) => sanitizedBaseHtml.includes(buildComposeUploadPreviewPath(row.temp_token)));
  const attachmentRowsToFinalize = attachmentUploadIds
    .map((uploadId) => rowById.get(uploadId) ?? null)
    .filter((row): row is ComposeUploadRow => Boolean(row));
  const progressItems = [...inlineRowsToFinalize, ...attachmentRowsToFinalize].map(
    buildComposeUploadFinalizeProgressItem,
  );
  const progressItemById = new Map(progressItems.map((item) => [item.id, item]));
  const emitProgress = async () => {
    if (!input.onProgress || progressItems.length === 0) {
      return;
    }

    await input.onProgress(buildComposeUploadFinalizeProgressSnapshot(progressItems));
  };

  await emitProgress();

  for (const row of inlineRowsToFinalize) {
    const previewUrl = buildComposeUploadPreviewPath(row.temp_token);
    const progressItem = progressItemById.get(row.id);

    if (progressItem) {
      progressItem.status = "uploading";
      await emitProgress();
    }

    const finalized = await finalizeComposeUpload(row);
    nextBodyHtml = nextBodyHtml.replaceAll(previewUrl, finalized.publicUrl);
    finalizedUploadIds.push(finalized.id);

    if (progressItem) {
      progressItem.status = "complete";
      progressItem.uploadedBytes = progressItem.sizeBytes;
      await emitProgress();
    }
  }

  for (const row of attachmentRowsToFinalize) {
    const progressItem = progressItemById.get(row.id);

    if (progressItem) {
      progressItem.status = "uploading";
      await emitProgress();
    }

    const finalized = await finalizeComposeUpload(row);
    attachmentUploads.push(finalized);
    finalizedUploadIds.push(finalized.id);

    if (progressItem) {
      progressItem.status = "complete";
      progressItem.uploadedBytes = progressItem.sizeBytes;
      await emitProgress();
    }
  }

  if (attachmentUploads.length > 0) {
    nextBodyHtml = `${nextBodyHtml}${buildAttachmentSectionHtml(attachmentUploads)}`;
    const attachmentSectionText = buildAttachmentSectionText(attachmentUploads);
    nextBodyText = [nextBodyText, attachmentSectionText].filter(Boolean).join("\n\n").trim();
  }

  return {
    attachmentUploads,
    bodyHtml: nextBodyHtml || null,
    bodyText: nextBodyText,
    finalizedUploadIds: normalizeUploadIds(finalizedUploadIds),
  };
}

export async function buildComposeRawAttachmentsForUserEmail(input: {
  attachmentUploadIds: number[];
  bodyHtml: string | null;
  email: string;
  inlineImageUploadIds: number[];
}) {
  const userId = await getUserIdByEmail(input.email);

  if (!userId) {
    throw new Error("user-not-found");
  }

  const attachmentUploadIds = normalizeUploadIds(input.attachmentUploadIds);
  const inlineImageUploadIds = normalizeUploadIds(input.inlineImageUploadIds);
  const requestedUploadIds = normalizeUploadIds([...attachmentUploadIds, ...inlineImageUploadIds]);

  if (requestedUploadIds.length === 0) {
    return {
      attachments: [] as ComposeRawAttachment[],
      bodyHtml: input.bodyHtml,
    };
  }

  const rows = await getComposeUploadsByIdsForUser(userId, requestedUploadIds);
  const rowById = new Map(rows.map((row) => [row.id, row]));
  const attachments: ComposeRawAttachment[] = [];
  let nextBodyHtml = sanitizeMailboxHtml(input.bodyHtml) ?? input.bodyHtml?.trim() ?? "";

  for (const uploadId of inlineImageUploadIds) {
    const row = rowById.get(uploadId);

    if (!row) {
      continue;
    }

    const previewUrl = buildComposeUploadPreviewPath(row.temp_token);

    if (!nextBodyHtml.includes(previewUrl)) {
      continue;
    }

    const contentId = buildInlineComposeContentId(row);
    const content = await readFile(row.temp_path);
    nextBodyHtml = nextBodyHtml.replaceAll(previewUrl, `cid:${contentId}`);
    attachments.push({
      content,
      contentDisposition: "inline",
      contentId,
      contentType: row.mime_type || "application/octet-stream",
      filename: row.original_name,
    });
  }

  for (const uploadId of attachmentUploadIds) {
    const row = rowById.get(uploadId);

    if (!row) {
      continue;
    }

    attachments.push({
      content: await readFile(row.temp_path),
      contentDisposition: "attachment",
      contentId: null,
      contentType: row.mime_type || "application/octet-stream",
      filename: row.original_name,
    });
  }

  return {
    attachments,
    bodyHtml: nextBodyHtml || null,
  };
}

export async function getFirstComposeAttachmentFilenameForUserEmail(
  email: string,
  attachmentUploadIds: number[],
) {
  const firstId = normalizeUploadIds(attachmentUploadIds)[0];
  if (!firstId) return null;
  const userId = await getUserIdByEmail(email);
  if (!userId) return null;
  const rows = await getComposeUploadsByIdsForUser(userId, [firstId]);
  return rows[0]?.original_name ?? null;
}

export async function finalizeComposeInlineImagesForUserEmail(input: {
  bodyHtml: string | null;
  email: string;
  inlineImageUploadIds: number[];
}): Promise<FinalizeComposeInlineImagesResult> {
  const userId = await getUserIdByEmail(input.email);

  if (!userId) {
    throw new Error("user-not-found");
  }

  let nextBodyHtml = sanitizeMailboxHtml(input.bodyHtml) ?? input.bodyHtml?.trim() ?? "";

  if (!nextBodyHtml) {
    return {
      bodyHtml: null,
      finalizedUploadIds: [],
    };
  }

  const finalizedUploadIds: number[] = [];
  const inlineImageUploadIds = normalizeUploadIds(input.inlineImageUploadIds);
  const previewReferences = extractComposeUploadPreviewReferences(nextBodyHtml);
  const [rowsById, rowsByToken] = await Promise.all([
    getComposeUploadsByIdsForUser(userId, inlineImageUploadIds),
    getComposeUploadsByTokensForUser(
      userId,
      previewReferences.map((reference) => reference.token),
    ),
  ]);
  const rowsToFinalize = [
    ...new Map([...rowsById, ...rowsByToken].map((row) => [row.id, row])).values(),
  ];

  for (const row of rowsToFinalize) {
    if (row.upload_kind !== "inline-image") {
      continue;
    }

    const previewUrl = buildComposeUploadPreviewPath(row.temp_token);
    const matchingSources = previewReferences
      .filter((reference) => reference.token === row.temp_token.toLowerCase())
      .map((reference) => reference.source)
      .sort((left, right) => right.length - left.length);

    if (!nextBodyHtml.includes(previewUrl) && matchingSources.length === 0) {
      continue;
    }

    const finalized = await finalizeComposeUpload(row);
    for (const source of matchingSources) {
      nextBodyHtml = nextBodyHtml.replaceAll(source, finalized.publicUrl);
    }
    nextBodyHtml = nextBodyHtml.replaceAll(previewUrl, finalized.publicUrl);
    finalizedUploadIds.push(finalized.id);
  }

  if (containsEmbeddedDataImage(nextBodyHtml)) {
    const embeddedMatches = Array.from(
      nextBodyHtml.matchAll(/\bsrc=(['"])(data:image\/[^'"]+)\1/gi),
    );
    const finalizedDataImageBySource = new Map<string, { publicUrl: string; uploadId: number }>();
    let embeddedSequence = 0;

    for (const match of embeddedMatches) {
      const source = match[2];

      if (!source || finalizedDataImageBySource.has(source)) {
        continue;
      }

      const parsed = parseEmbeddedDataImage(source);

      if (!parsed) {
        continue;
      }

      embeddedSequence += 1;
      const temporaryUpload = await persistTemporaryUpload({
        buffer: parsed.buffer,
        contentType: parsed.contentType,
        filename: buildEmbeddedImageFilename(embeddedSequence, parsed.contentType),
        kind: "inline-image",
        sizeBytes: parsed.buffer.length,
        userId,
      });
      const [createdRow] = await getComposeUploadsByIdsForUser(userId, [temporaryUpload.id]);

      if (!createdRow) {
        continue;
      }

      const finalized = await finalizeComposeUpload(createdRow);
      finalizedDataImageBySource.set(source, {
        publicUrl: finalized.publicUrl,
        uploadId: finalized.id,
      });
      finalizedUploadIds.push(finalized.id);
    }

    for (const [source, finalized] of finalizedDataImageBySource.entries()) {
      nextBodyHtml = nextBodyHtml.replaceAll(source, finalized.publicUrl);
    }
  }

  return {
    bodyHtml: nextBodyHtml || null,
    finalizedUploadIds: normalizeUploadIds(finalizedUploadIds),
  };
}

export async function linkComposeUploadIdsToMessageId(
  queryable: SqlQueryable,
  uploadIds: number[],
  messageId: number,
) {
  const normalizedUploadIds = normalizeUploadIds(uploadIds);

  if (normalizedUploadIds.length === 0 || !Number.isInteger(messageId) || messageId < 1) {
    return;
  }

  await queryable.query(
    `
      UPDATE mailbox_uploaded_assets
      SET
        mailbox_message_id = ?,
        updated_at = NOW()
      WHERE id IN (${normalizedUploadIds.map(() => "?").join(", ")})
        AND storage_status <> 'deleted'
    `,
    [messageId, ...normalizedUploadIds],
  );
}

export async function getLinkedComposeUploadAttachmentsByMessageId(
  messageId: number,
  options: { includeUnpublishedOwnerCopy?: boolean } = {},
) {
  await ensureOfficialMailSchema();
  const pool = getDbPool();
  const [rows] = await pool.query<ComposeUploadRow[]>(
    `
      SELECT
        ${COMPOSE_UPLOAD_SELECT_COLUMNS}
      FROM mailbox_uploaded_assets
      WHERE mailbox_message_id = ?
        AND upload_kind = 'attachment'
        AND storage_status IN ('finalized', 'deleted')
      ORDER BY id ASC
    `,
    [messageId],
  );

  return rows
    .map((row, index) => ({ row, index }))
    .filter(({ row }) => row.large_attachment === 1
      ? row.published_at !== null || Boolean(
          options.includeUnpublishedOwnerCopy && row.storage_status === "finalized" &&
          row.storage_key && row.expires_at && row.expires_at.getTime() > Date.now() &&
          row.download_count < LARGE_ATTACHMENT_DOWNLOAD_LIMIT
        )
      : row.storage_status === "finalized" && Boolean(row.public_url))
    .map(({ row, index }) => ({
      contentDisposition: "attachment" as const,
      contentId: null,
      contentType: normalizeAttachmentContentType(row.mime_type, row.original_name),
      extension: getFilenameExtension(row.original_name),
      externalUrl: row.large_attachment === 1
        ? getMailAbsoluteUrl(`/api/mailbox/large-attachments/${row.temp_token}`)
        : row.public_url as string,
      filename: row.original_name,
      index: -(index + 1),
      isInline: false,
      isLargeAttachment: row.large_attachment === 1,
      isPendingPublication: row.large_attachment === 1 && row.published_at === null,
      isPreviewable: row.large_attachment !== 1 && isPreviewableAttachment(row.mime_type, row.original_name),
      sizeBytes: row.size_bytes,
    }));
}

export async function getPublishedLargeComposeAttachmentsByTokens(tokens: string[]) {
  const normalizedTokens = [...new Set(tokens.filter((token) => /^[a-f0-9]{48}$/.test(token)))];
  if (normalizedTokens.length === 0) return [] as LinkedComposeUploadAttachment[];
  await ensureOfficialMailSchema();
  const [rows] = await getDbPool().query<ComposeUploadRow[]>(
    `SELECT ${COMPOSE_UPLOAD_SELECT_COLUMNS}
     FROM mailbox_uploaded_assets
     WHERE temp_token IN (${normalizedTokens.map(() => "?").join(", ")})
       AND upload_kind = 'attachment' AND large_attachment = 1
       AND storage_status = 'finalized' AND published_at IS NOT NULL
       AND expires_at > NOW() AND download_count < ?
       AND storage_key IS NOT NULL`,
    [...normalizedTokens, LARGE_ATTACHMENT_DOWNLOAD_LIMIT],
  );
  const byToken = new Map(rows.map((row) => [row.temp_token, row]));
  return normalizedTokens.flatMap((token) => {
    const row = byToken.get(token);
    if (!row) return [];
    return [{
      contentDisposition: "attachment" as const,
      contentId: null,
      contentType: normalizeAttachmentContentType(row.mime_type, row.original_name),
      extension: getFilenameExtension(row.original_name),
      externalUrl: getMailAbsoluteUrl(`/api/mailbox/large-attachments/${token}`),
      filename: row.original_name,
      index: -(1_000_000_000 + row.id),
      isInline: false as const,
      isLargeAttachment: true,
      isPreviewable: false,
      sizeBytes: row.size_bytes,
    }];
  });
}

export async function getLinkedComposeUploadAttachmentPayloadByMessageId(
  messageId: number,
  attachmentIndex: number,
) {
  if (!Number.isInteger(messageId) || messageId < 1 || !Number.isInteger(attachmentIndex) || attachmentIndex >= 0) {
    return null;
  }

  await ensureOfficialMailSchema();
  const pool = getDbPool();
  const [rows] = await pool.query<ComposeUploadRow[]>(
    `
      SELECT
        ${COMPOSE_UPLOAD_SELECT_COLUMNS}
      FROM mailbox_uploaded_assets
      WHERE mailbox_message_id = ?
        AND upload_kind = 'attachment'
        AND storage_status IN ('finalized', 'deleted')
      ORDER BY id ASC
    `,
    [messageId],
  );

  const row = rows[Math.abs(attachmentIndex) - 1];

  if (!row || row.storage_status !== "finalized") {
    return null;
  }

  // The owner's mailbox attachment endpoint must consume the same download
  // allowance as the public link. Otherwise it can bypass the 100-download cap.
  const largeContent = row.large_attachment === 1
    ? await openLargeComposeAttachmentByToken(row.temp_token)
    : null;
  if (row.large_attachment === 1 && !largeContent) return null;
  const storedContent = largeContent
    ? {
        content: Buffer.from(await new Response(largeContent.body).arrayBuffer()),
        contentType: largeContent.contentType,
      }
    : await readComposeUploadContentFromRow(row);

  if (!storedContent) return null;

  const contentType = normalizeAttachmentContentType(storedContent.contentType, row.original_name);

  return {
    content: storedContent.content,
    contentDisposition: "attachment" as const,
    contentId: null,
    contentType,
    extension: getFilenameExtension(row.original_name),
    externalUrl: row.large_attachment === 1
      ? getMailAbsoluteUrl(`/api/mailbox/large-attachments/${row.temp_token}`)
      : row.public_url?.trim() || null,
    filename: row.original_name,
    index: attachmentIndex,
    isInline: false,
    isLargeAttachment: row.large_attachment === 1,
    isPreviewable: row.large_attachment !== 1 && isPreviewableAttachment(contentType, row.original_name),
    sizeBytes: row.size_bytes,
  } satisfies LinkedComposeUploadAttachmentPayload;
}

export async function cleanupExpiredComposeUploads() {
  await ensureOfficialMailSchema();
  const pool = getDbPool();
  const ttlHours = readUploadTtlHours();
  const [rows] = await pool.query<ComposeUploadRow[]>(
    `
      SELECT
        ${COMPOSE_UPLOAD_SELECT_COLUMNS}
      FROM mailbox_uploaded_assets
      WHERE
        (storage_status = 'temporary' AND created_at < (NOW() - INTERVAL ? HOUR))
        OR (storage_status = 'temporary' AND multipart_upload_id IS NOT NULL
            AND created_at < (NOW() - INTERVAL 2 HOUR))
        OR (
          storage_status = 'finalized'
          AND mailbox_message_id IS NULL
          AND published_at IS NULL
          AND COALESCE(finalized_at, created_at) < (NOW() - INTERVAL ? HOUR)
        )
        OR (storage_status = 'finalized' AND large_attachment = 1 AND expires_at <= NOW())
        OR (storage_status = 'finalized' AND large_attachment = 1
            AND download_count >= ? AND updated_at < (NOW() - INTERVAL 1 HOUR))
        OR (storage_status = 'finalized' AND temp_path <> '')
        OR (storage_status = 'deleted' AND
            (temp_path <> '' OR storage_key IS NOT NULL OR multipart_upload_id IS NOT NULL))
    `,
    [ttlHours, ttlHours, LARGE_ATTACHMENT_DOWNLOAD_LIMIT],
  );

  let deletedCount = 0;
  let tempFileCleanupCount = 0;

  for (const row of rows) {
    if (row.storage_status === "temporary") {
      await pool.query(
        `
          UPDATE mailbox_uploaded_assets
          SET
            mailbox_message_id = NULL,
            storage_status = 'deleted',
            deleted_at = NOW(),
            updated_at = NOW()
          WHERE id = ?
        `,
        [row.id],
      );
      await cleanupComposeUploadArtifacts(pool, [toComposeUploadCleanupTarget(row)]);
      deletedCount += 1;
      continue;
    }

    if (row.storage_status === "finalized" &&
        ((row.mailbox_message_id === null && row.published_at === null) ||
          (row.large_attachment === 1 && row.expires_at !== null && row.expires_at.getTime() <= Date.now()) ||
          (row.large_attachment === 1 && row.download_count >= LARGE_ATTACHMENT_DOWNLOAD_LIMIT))) {
      await pool.query(
        `
          UPDATE mailbox_uploaded_assets
          SET
            storage_status = 'deleted',
            deleted_at = NOW(),
            updated_at = NOW()
          WHERE id = ?
        `,
        [row.id],
      );
      await cleanupComposeUploadArtifacts(pool, [toComposeUploadCleanupTarget(row)]);
      deletedCount += 1;
      continue;
    }

    if (row.storage_status === "deleted") {
      await cleanupComposeUploadArtifacts(pool, [toComposeUploadCleanupTarget(row)]);
      continue;
    }

    if (row.temp_path.trim()) {
      await unlink(row.temp_path).catch(() => undefined);
      await updateComposeUploadStorageCleanupState(pool, row.id, { clearTempPath: true });
      tempFileCleanupCount += 1;
    }
  }

  return {
    deletedCount,
    tempFileCleanupCount,
    ttlHours,
  };
}