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 { plainTextToHtml, sanitizeMailboxHtml } from "@/lib/mail-html";
import {
  deleteObjectFromObjectStorage,
  readObjectFromObjectStorage,
  uploadBufferToObjectStorage,
} 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;
  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;
  mime_type: string;
  original_name: string;
  owner_user_id: number;
  public_url: string | null;
  size_bytes: number;
  storage_bucket: string | null;
  storage_key: string | null;
  storage_status: ComposeUploadStorageStatus;
  temp_path: string;
  temp_token: string;
  upload_kind: ComposeUploadKind;
};

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

type FinalizedComposeUpload = {
  contentType: string;
  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;
  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,
  public_url,
  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 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 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,
    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;
    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");
  }

  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;

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

    if (storageKey) {
      try {
        await deleteObjectFromObjectStorage({ key: storageKey });
        clearObjectStorage = true;
      } catch {
        clearObjectStorage = false;
      }
    }

    if (clearTempPath || clearObjectStorage) {
      await updateComposeUploadStorageCleanupState(queryable, target.id, {
        clearObjectStorage,
        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;
}

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[];
  }

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

  return rows.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 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,
      input.contentType,
      input.sizeBytes,
      tempPath,
      expiresAt,
    ],
  );

  return {
    contentType: input.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 buildAttachmentSectionText(uploads: FinalizedComposeUpload[]) {
  if (uploads.length === 0) {
    return "";
  }

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

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 = file.contentType.trim() || "application/octet-stream";
    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.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 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);

  if (inlineImageUploadIds.length > 0) {
    const rows = await getComposeUploadsByIdsForUser(userId, inlineImageUploadIds);
    const rowById = new Map(rows.map((row) => [row.id, row]));

    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 finalized = await finalizeComposeUpload(row);
      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) {
  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 = 'finalized'
        AND public_url IS NOT NULL
      ORDER BY id ASC
    `,
    [messageId],
  );

  return rows
    .filter((row) => 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.public_url as string,
      filename: row.original_name,
      index: -(index + 1),
      isInline: false,
      isPreviewable: isPreviewableAttachment(row.mime_type, row.original_name),
      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 = 'finalized'
      ORDER BY id ASC
    `,
    [messageId],
  );

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

  if (!row) {
    return null;
  }

  const storedContent = 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.public_url?.trim() || null,
    filename: row.original_name,
    index: attachmentIndex,
    isInline: false,
    isPreviewable: 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 = 'finalized'
          AND mailbox_message_id IS NULL
          AND COALESCE(finalized_at, created_at) < (NOW() - INTERVAL ? HOUR)
        )
        OR (storage_status = 'finalized' AND temp_path <> '')
        OR (storage_status = 'deleted' AND (temp_path <> '' OR storage_key IS NOT NULL))
    `,
    [ttlHours, ttlHours],
  );

  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) {
      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,
  };
}