import {
  createHash,
  randomBytes,
  randomUUID,
  scryptSync,
  timingSafeEqual,
} from "crypto";
import { Resolver } from "dns/promises";
import type { Pool, PoolConnection, RowDataPacket } from "mysql2/promise";
import { after } from "next/server";
import { ensureOfficialMailSchema, getDbPool } from "@/lib/db";
import { recordMarketingLifecycleEvent } from "@/lib/marketing-analytics";
import {
  getWwwDomainSuggestion,
  hasWwwDomainPrefix,
} from "@/lib/domain-input";
import {
  buildComposeRawAttachmentsForUserEmail,
  cleanupComposeUploadArtifacts,
  finalizeComposeInlineImagesForUserEmail,
  getLinkedComposeUploadAttachmentPayloadByMessageId,
  getLinkedComposeUploadAttachmentsByMessageId,
  linkComposeUploadIdsToMessageId,
  markComposeUploadsDeletedByMessageIds,
  relinkComposeUploadsToMessageId,
  type ComposeUploadCleanupTarget,
  type ComposeUploadFinalizeProgressSnapshot,
  type ComposeRawAttachment,
} from "@/lib/mail-compose-uploads";
import {
  normalizeStoredMailboxHtml,
  plainTextToHtml,
  sanitizeMailboxHtml,
} from "@/lib/mail-html";
import { triggerMailboxPostSyncByOwnerEmail } from "@/lib/mail-automation";
import {
  DEFAULT_MAIL_LAYOUT_PREFERENCES,
  normalizeMailLayoutPreferences,
  type MailListSortOrder,
  type MailLayoutPreferences,
} from "@/lib/mail-layout";
import { normalizeMailboxPageSize } from "@/lib/mailbox-page-size";
import {
  appendMessageToRemoteFolder,
  appendDraftMessage,
  appendSentMessage,
  createRemoteMailboxFolder,
  deleteRemoteMailboxFolder,
  decryptMailboxPassword,
  deleteRemoteMessages,
  encryptMailboxPassword,
  extractAttachmentsFromRawSource,
  getAttachmentPayloadFromRawSource,
  renameRemoteMailboxFolder,
  moveRemoteMessage,
  sendSmtpMessage,
  syncRemoteMailbox,
  updateRemoteMessageFlag,
  type RemoteMailboxAttachmentMeta,
  type RemoteMailboxAttachmentPayload,
  type RemoteMailboxMessage,
  type RemoteMailboxMoveResult,
} from "@/lib/mailbox-remote";
import {
  ensureMailcowDkimRecord,
  ensureMailcowDomain,
  ensureMailcowMailbox,
  ensureMailcowMailboxAccount,
  getMailcowMailboxQuota,
  ensureMailcowProvisioning,
  isMailcowProvisioned,
  regenerateMailcowDkim,
  type MailcowMailboxQuotaSnapshot,
} from "@/lib/mailcow";

const MX_HOSTNAME = process.env.MX_HOSTNAME ?? "mx1.officialsite.kr";
const DMARC_RUA = process.env.DMARC_RUA ?? "mailto:dmarc@officialsite.kr";
const SPF_RECORD_VALUE = `v=spf1 include:${MX_HOSTNAME} -all`;
const MAIL_SERVER_PUBLIC_IP =
  process.env.MAIL_SERVER_PUBLIC_IP?.trim() || "115.68.219.179";
const MX_HOST_SPF_RECORD_VALUE = `v=spf1 ip4:${MAIL_SERVER_PUBLIC_IP} -all`;
const DEFAULT_DOMAIN_VERIFICATION_DNS_SERVERS = ["1.1.1.1", "8.8.8.8"];
const configuredDomainVerificationDnsServers = (
  process.env.DOMAIN_VERIFICATION_DNS_SERVERS ??
  DEFAULT_DOMAIN_VERIFICATION_DNS_SERVERS.join(",")
)
  .split(",")
  .map((server) => server.trim())
  .filter(Boolean);
const DOMAIN_VERIFICATION_DNS_SERVERS =
  configuredDomainVerificationDnsServers.length > 0
    ? configuredDomainVerificationDnsServers
    : DEFAULT_DOMAIN_VERIFICATION_DNS_SERVERS;
const LOCAL_PART_PATTERN = /^(?=.{1,64}$)[a-z0-9](?:[a-z0-9._-]*[a-z0-9])?$/;
const MAILBOX_AUTO_REFRESH_MAX_AGE_MS = 30_000;
const MAILBOX_SYNC_WRITE_RETRY_DELAYS_MS = [120, 360, 900] as const;
const FREE_PLAN_DAILY_SEND_LIMIT = 30;
const GROWTH_PLAN_BURST_SEND_LIMIT = 10;
const GROWTH_PLAN_BURST_WINDOW_MS = 60 * 1000;
const GROWTH_PLAN_SEND_COOLDOWN_MS = 5 * 60 * 1000;
const SIGNUP_RECOVERY_EMAIL_CODE_TTL_MS = 3 * 60 * 1000;
const SIGNUP_RECOVERY_EMAIL_RESEND_COOLDOWN_MS = 10 * 1000;
const PASSWORD_RESET_TOKEN_TTL_MS = 5 * 60 * 1000;
const SIGNUP_RECOVERY_EMAIL_SUBJECT = "[오피셜메일] 보조 이메일 인증번호";
const PASSWORD_RESET_LINK_SUBJECT = "[오피셜메일] 비밀번호 재설정 링크";
const MAILCOW_QUOTA_FETCH_TIMEOUT_MS = Math.max(
  1000,
  Number(process.env.MAILCOW_QUOTA_FETCH_TIMEOUT_MS ?? "3000") || 3000,
);
const ATTACHMENT_MESSAGE_REGEXP = "pdf|첨부|제안서|계약서|정리";
const SYSTEM_WELCOME_SUBJECT = "오피셜메일에 오신 것을 환영합니다";
const SYSTEM_DELIVERY_FAILURE_PREFIX = "[전송 실패]";
const CUSTOM_FOLDER_PREFIX = "custom__";
const LOCAL_MAILBOX_FOLDERS: LocalFolderDefinition[] = [
  { systemName: "inbox", displayName: "받은메일" },
  { systemName: "sent", displayName: "보낸메일" },
  { systemName: "drafts", displayName: "임시보관함" },
  { systemName: "spam", displayName: "스팸함" },
  { systemName: "trash", displayName: "휴지통" },
];

// A browser refresh, the page-load refresh, and IMAP IDLE can all request the
// same cache write. Keep each mailbox's database sync ordered in this process.
const mailboxSyncQueues = new Map<number, Promise<void>>();

export type SessionSnapshot = {
  email: string;
  companyName: string;
  displayName: string;
  mailConfigured: boolean;
  domain: string;
  mailbox: string;
};

export type SignupProvisioningStep =
  | "account"
  | "domain"
  | "mailbox"
  | "dkim"
  | "welcome";

export type SignupProvisioningProgress = {
  description: string;
  status: "running" | "complete";
  step: SignupProvisioningStep;
  title: string;
};

export type PendingSignupRegistration = {
  companyName: string;
  displayName: string;
  domain: string;
  domainId: number;
  localPart: string;
  mailboxEmail: string;
  mailboxId: number;
  mailboxPassword: string;
  restoredFromCleanup: boolean;
  snapshot: SessionSnapshot;
  userId: number;
};

export type DnsRecordItem = {
  type: string;
  host: string;
  value: string;
  priority: number | null;
  status: "pending" | "verified" | "failed";
  lastCheckedAt: string | null;
};

export type MailSetupOverview = {
  domain: string;
  mailbox: string;
  localPart: string;
  status: "pending" | "verified" | "failed";
  mailConfigured: boolean;
  dkimEnabled: boolean;
  mxHostname: string;
  dkimSelector: string;
  dkimPublicKey: string;
  verifiedAt: string | null;
  dnsRecords: DnsRecordItem[];
};

export type MailFolderSummary = {
  id: number;
  kind: "custom" | "system";
  systemName: string;
  name: string;
  count: number;
  unreadCount: number;
};

export type MailMessageFilter = "all" | "unread" | "important" | "attachments";
export type MailSearchField = "all" | "from" | "to" | "subject" | "body";
export type MailAiSummaryStatus = "failed" | "sending" | "sent";

export type MailMessageFilterCounts = Record<MailMessageFilter, number>;

export type MailSearchSuggestion = {
  field: Exclude<MailSearchField, "all">;
  messageId: number;
  preview: string;
  receivedAt: string;
  value: string;
};

export type MailRecipientSuggestion = {
  displayName: string | null;
  email: string;
  kind: "history" | "member";
  label: string;
};

export type MailMessageSummary = {
  aiSummaryError: string | null;
  aiSummaryResponse: string | null;
  aiSummaryStatus: MailAiSummaryStatus | null;
  id: number;
  subject: string;
  fromName: string | null;
  fromAddress: string;
  toAddresses: string;
  snippet: string;
  isRead: boolean;
  isStarred: boolean;
  receivedAt: string;
  direction: "inbound" | "outbound" | "draft";
  folderSystemName: string;
  folderName: string;
};

export type MailMessageDetail = MailMessageSummary & {
  attachments: MailMessageAttachment[];
  bodyHtml: string | null;
  bodyHtmlDisplay: string | null;
  bodyText: string;
  messageIdHeader: string | null;
  rawSource: string | null;
  transportStatus: "saved" | "queued" | "sent" | "failed";
  transportResponse: string | null;
};

export type MailMessageAttachment = RemoteMailboxAttachmentMeta & {
  externalUrl?: string | null;
};

export type MailDeliveryLog = {
  id: number;
  action: "send" | "draft" | "receive";
  status: "saved" | "queued" | "sent" | "failed";
  transport: string;
  sourceAddress: string;
  targetAddress: string;
  subject: string;
  messageIdHeader: string | null;
  responseText: string | null;
  createdAt: string;
};

export type MailMessageBodyView = {
  bodyHtmlDisplay: string | null;
  bodyText: string;
  fromAddress: string;
  fromName: string | null;
  id: number;
  receivedAt: string;
  subject: string;
  toAddresses: string;
};

export type MailAppOverview = {
  mailbox: string;
  mailboxQuotaBytes: number | null;
  mailboxQuotaIsUnlimited: boolean;
  mailboxQuotaUsagePercent: number | null;
  mailboxQuotaUsedBytes: number | null;
  domain: string;
  lastSyncAt: string | null;
  status: "pending_dns" | "active" | "disabled";
  syncError: string | null;
  folders: MailFolderSummary[];
  currentFolder: string;
  messageFilter: MailMessageFilter;
  searchField: MailSearchField;
  searchQuery: string;
  sortOrder: MailListSortOrder;
  messageFilterCounts: MailMessageFilterCounts;
  currentPage: number;
  totalPages: number;
  totalMessages: number;
  pageSize: number;
  messages: MailMessageSummary[];
  selectedMessage: MailMessageDetail | null;
  selectedMessageLogs: MailDeliveryLog[];
};

type UserRow = RowDataPacket & {
  id: number;
  email: string;
  company_name: string;
  display_name: string;
  recovery_email: string | null;
  recovery_email_verified_at: Date | null;
  password_hash: string;
  mail_configured: number;
  mail_sidebar_width: number;
  mail_list_width: number;
  mail_list_height: number;
  mail_view_mode: string;
  mail_sort_order: string;
};

type SignupRecoveryEmailVerificationRow = RowDataPacket & {
  id: number;
  email: string;
  code_hash: string;
  verification_token_hash: string | null;
  expires_at: Date;
  resend_available_at: Date;
  verified_at: Date | null;
  consumed_at: Date | null;
};

type PasswordResetTokenRow = RowDataPacket & {
  id: number;
  user_id: number;
  recovery_email: string;
  token_hash: string;
  expires_at: Date;
  used_at: Date | null;
};

type MailSetupRow = RowDataPacket & {
  domain_id: number;
  domain: string;
  domain_status: "pending" | "verified" | "failed";
  dkim_enabled: number;
  dkim_selector: string;
  dkim_public_key: string;
  mailcow_cleanup_at: Date | null;
  verified_at: Date | null;
  mailbox_email: string;
  local_part: string;
  mailbox_status: "pending_dns" | "active" | "disabled";
};

type DomainVerificationTargetRow = MailSetupRow & {
  owner_user_id: number;
};

type CleanedDomainSignupRow = RowDataPacket & {
  domain: string;
  domain_id: number;
  mailbox_email: string;
  mailbox_id: number;
  owner_email: string;
  owner_recovery_email: string | null;
  owner_user_id: number;
};

type RestorableManagedMemberRow = RowDataPacket & {
  display_name: string;
  email: string;
  local_part: string;
  password_ciphertext: string;
};

type DnsRecordRow = RowDataPacket & {
  record_type: string;
  host_name: string;
  value_text: string;
  priority: number | null;
  status: "pending" | "verified" | "failed";
  last_checked_at: Date | null;
};

type MailboxRow = RowDataPacket & {
  id: number;
  email: string;
  last_sync_at: Date | null;
  last_sync_error: string | null;
  message_visibility_cutoff_at: Date | null;
  password_ciphertext: string | null;
  send_cooldown_until: Date | null;
  status: "pending_dns" | "active" | "disabled";
  domain: string;
};

type FolderSummaryRow = RowDataPacket & {
  id: number;
  last_synced_at: Date | null;
  system_name: string;
  name: string;
  remote_name: string | null;
  remote_total: number;
  remote_unseen: number;
};

type MailMessageCountRow = RowDataPacket & {
  all_count: number | null;
  unread_count: number | null;
  important_count: number | null;
  attachments_count: number | null;
};

type MailSearchSuggestionRow = RowDataPacket & {
  body_text: string;
  from_address: string;
  from_name: string | null;
  id: number;
  received_at: Date;
  snippet: string;
  subject: string;
  to_addresses: string;
};

type MailRecipientSuggestionRow = RowDataPacket & {
  from_address: string;
  from_name: string | null;
  received_at: Date;
  to_addresses: string;
};

type MailRecipientDirectoryRow = RowDataPacket & {
  display_name: string | null;
  email: string;
};

type MailMessageRow = RowDataPacket & {
  ai_summary_error: string | null;
  ai_summary_response: string | null;
  ai_summary_status: MailAiSummaryStatus | null;
  id: number;
  remote_folder: string | null;
  remote_uid: number | null;
  subject: string;
  from_name: string | null;
  from_address: string;
  to_addresses: string;
  snippet: string;
  body_text: string;
  body_html: string | null;
  message_id_header: string | null;
  raw_source: string | null;
  transport_status: "saved" | "queued" | "sent" | "failed";
  transport_response: string | null;
  remote_flags: string | null;
  is_read: number;
  is_starred: number;
  received_at: Date;
  direction: "inbound" | "outbound" | "draft";
  folder_system_name: string;
  folder_name: string;
};

type LocalFolderDefinition = {
  displayName: string;
  systemName: "inbox" | "sent" | "drafts" | "spam" | "trash";
};

type SupportedMailboxFolderSystemName = LocalFolderDefinition["systemName"];

type MailDeliveryLogRow = RowDataPacket & {
  id: number;
  action: "send" | "draft" | "receive";
  status: "saved" | "queued" | "sent" | "failed";
  transport: string;
  source_address: string;
  target_address: string;
  subject: string;
  message_id_header: string | null;
  response_text: string | null;
  created_at: Date;
};

type CountTotalRow = RowDataPacket & {
  total: number | null;
};

type MailboxSenderRuleRow = RowDataPacket & {
  id: number;
  mailbox_id: number;
  sender_address: string;
  rule_action: "spam";
};

type MailboxFolderRowBasic = RowDataPacket & {
  id: number;
  system_name: string;
  remote_name: string | null;
  name: string;
};

type MailboxMoveMessageRow = RowDataPacket & {
  id: number;
  folder_id: number;
  direction: "draft" | "inbound" | "outbound";
  from_address: string;
  remote_folder: string | null;
  remote_uid: number | null;
};

type MailboxRemoteDuplicateRow = RowDataPacket & {
  id: number;
  folder_id: number;
};

type MailboxFolderMessageRow = RowDataPacket & {
  id: number;
  folder_id: number;
  remote_uid: number | null;
  remote_folder: string | null;
  direction: "draft" | "inbound" | "outbound";
  subject: string;
  from_name: string | null;
  from_address: string;
  to_addresses: string;
  snippet: string;
  body_text: string;
  body_html: string | null;
  message_id_header: string | null;
  raw_source: string | null;
  transport_status: "saved" | "queued" | "sent" | "failed";
  transport_response: string | null;
  remote_flags: string | null;
  is_read: number;
  is_starred: number;
  received_at: Date;
};

type MailboxMessageDeleteResult = {
  cleanupTargets: ComposeUploadCleanupTarget[];
  deletedCount: number;
  deletedMessageIds: number[];
  invalidatedFolderIds: number[];
};

type MailboxMoveActionResult = {
  deletedCount: number;
  deletedMessageIds: number[];
  movedCount: number;
  movedMessageIds: number[];
  spamSenderCount: number;
};

function normalizeDate(value: Date | null) {
  return value ? value.toISOString() : null;
}

function isSystemMailboxFolderSystemName(
  value: string | undefined | null,
): value is SupportedMailboxFolderSystemName {
  return (
    value === "inbox" ||
    value === "sent" ||
    value === "drafts" ||
    value === "spam" ||
    value === "trash"
  );
}

function isCustomMailboxFolderSystemName(
  value: string | undefined | null,
): value is string {
  return typeof value === "string" && value.startsWith(CUSTOM_FOLDER_PREFIX);
}

function folderKindForSystemName(systemName: string) {
  return isCustomMailboxFolderSystemName(systemName)
    ? ("custom" as const)
    : ("system" as const);
}

function createCustomFolderSystemName() {
  return `${CUSTOM_FOLDER_PREFIX}${randomUUID()}`;
}

function deriveDisplayName(companyName: string, email: string) {
  return companyName.trim() || email.split("@")[0] || "대표";
}

function normalizeCustomFolderName(value: string) {
  return value.replace(/\s+/g, " ").trim().slice(0, 80);
}

type NormalizedSignupRegistrationInput = {
  companyName: string;
  domain: string;
  localPart: string;
  mailboxEmail: string;
  password: string;
  recoveryEmail: string;
  recoveryVerificationId: number;
  recoveryVerificationToken: string;
};

function normalizeSignupRegistrationInput(input: {
  companyName: string;
  domain: string;
  localPart: string;
  password: string;
  recoveryEmail: string;
  recoveryVerificationId: number;
  recoveryVerificationToken: string;
}): NormalizedSignupRegistrationInput {
  const companyName = input.companyName.trim();
  const domain = input.domain
    .trim()
    .toLowerCase()
    .replace(/^https?:\/\//, "")
    .replace(/\/$/, "");
  const localPart = input.localPart.trim().replace(/\s+/g, "").toLowerCase();
  const password = input.password.trim();
  const recoveryEmail = input.recoveryEmail.trim().toLowerCase();
  const recoveryVerificationId = Number(input.recoveryVerificationId);
  const recoveryVerificationToken = input.recoveryVerificationToken.trim();
  const mailboxEmail = `${localPart}@${domain}`;

  if (!companyName || !domain || !localPart || !password || !recoveryEmail) {
    throw new Error("required");
  }

  if (hasWwwDomainPrefix(input.domain)) {
    throw new Error(`domain-www-prefix:${getWwwDomainSuggestion(input.domain)}`);
  }

  if (!LOCAL_PART_PATTERN.test(localPart)) {
    throw new Error("invalid-local-part");
  }

  if (!RECIPIENT_ADDRESS_PATTERN.test(recoveryEmail)) {
    throw new Error("recovery-email-invalid");
  }

  if (
    !Number.isInteger(recoveryVerificationId) ||
    recoveryVerificationId <= 0 ||
    !recoveryVerificationToken
  ) {
    throw new Error("recovery-email-unverified");
  }

  return {
    companyName,
    domain,
    localPart,
    mailboxEmail,
    password,
    recoveryEmail,
    recoveryVerificationId,
    recoveryVerificationToken,
  };
}

function escapeHeaderValue(value: string) {
  return value.replace(/[\r\n]+/g, " ").trim();
}

function hasNonAscii(value: string) {
  return /[^\x20-\x7E]/.test(value);
}

function encodeMimeHeaderWord(value: string) {
  const safeValue = escapeHeaderValue(value);

  if (!safeValue) {
    return "";
  }

  if (!hasNonAscii(safeValue)) {
    return safeValue;
  }

  return `=?UTF-8?B?${Buffer.from(safeValue, "utf8").toString("base64")}?=`;
}

function formatMailboxHeader(name: string | null, address: string) {
  const safeAddress = escapeHeaderValue(address);

  if (!name?.trim()) {
    return `<${safeAddress}>`;
  }

  const safeName = escapeHeaderValue(name);

  if (hasNonAscii(safeName)) {
    return `${encodeMimeHeaderWord(safeName)} <${safeAddress}>`;
  }

  return `"${safeName.replace(/"/g, '\\"')}" <${safeAddress}>`;
}

function filterVisibleRemoteMailboxMessages(
  messages: RemoteMailboxMessage[],
  cutoffAt: Date | null,
) {
  if (!cutoffAt) {
    return messages;
  }

  const cutoffTime = cutoffAt.getTime();
  return messages.filter(
    (message) => message.receivedAt.getTime() > cutoffTime,
  );
}

function createMessageIdHeader(domain: string) {
  return `<${randomUUID()}@${domain}>`;
}

function formatMimeFilenameParameter(name: string, value: string) {
  const safeValue = escapeHeaderValue(value);

  if (!safeValue) {
    return null;
  }

  if (!hasNonAscii(safeValue)) {
    return `${name}="${safeValue.replace(/"/g, '\\"')}"`;
  }

  return `${name}*=UTF-8''${encodeURIComponent(safeValue)}`;
}

function encodeBase64Lines(buffer: Buffer) {
  return buffer.toString("base64").replace(/(.{76})/g, "$1\r\n");
}

function encodeBase64TextLines(value: string) {
  return encodeBase64Lines(Buffer.from(value, "utf8"));
}

function buildTextPlainPartLines(bodyText: string) {
  return [
    "Content-Type: text/plain; charset=UTF-8",
    "Content-Transfer-Encoding: base64",
    "",
    encodeBase64TextLines(bodyText),
  ];
}

function buildAlternativePartLines(bodyText: string, bodyHtml: string) {
  const boundary = `----=_OfficialMail_Alt_${randomUUID()}`;

  return [
    `Content-Type: multipart/alternative; boundary="${boundary}"`,
    "",
    `--${boundary}`,
    ...buildTextPlainPartLines(bodyText),
    "",
    `--${boundary}`,
    "Content-Type: text/html; charset=UTF-8",
    "Content-Transfer-Encoding: base64",
    "",
    encodeBase64TextLines(bodyHtml),
    "",
    `--${boundary}--`,
  ];
}

function buildMimeAttachmentPartLines(attachment: ComposeRawAttachment) {
  const contentTypeParts = [
    attachment.contentType.trim() || "application/octet-stream",
  ];
  const typeName = formatMimeFilenameParameter("name", attachment.filename);

  if (typeName) {
    contentTypeParts.push(typeName);
  }

  const dispositionParts: string[] = [attachment.contentDisposition];
  const fileName = formatMimeFilenameParameter("filename", attachment.filename);

  if (fileName) {
    dispositionParts.push(fileName);
  }

  return [
    `Content-Type: ${contentTypeParts.join("; ")}`,
    "Content-Transfer-Encoding: base64",
    `Content-Disposition: ${dispositionParts.join("; ")}`,
    attachment.contentId ? `Content-ID: <${attachment.contentId}>` : null,
    "",
    encodeBase64Lines(attachment.content),
  ].filter((line): line is string => line !== null);
}

function buildRawSource(input: {
  attachments?: ComposeRawAttachment[];
  bodyHtml?: string | null;
  bodyText: string;
  ccAddresses?: string;
  messageIdHeader: string;
  fromName: string | null;
  fromAddress: string;
  toAddresses: string;
  subject: string;
}) {
  const commonHeaders = [
    `Message-ID: ${input.messageIdHeader}`,
    `Date: ${new Date().toUTCString()}`,
    `From: ${formatMailboxHeader(input.fromName, input.fromAddress)}`,
    `To: ${escapeHeaderValue(input.toAddresses)}`,
    input.ccAddresses ? `Cc: ${escapeHeaderValue(input.ccAddresses)}` : null,
    `Subject: ${encodeMimeHeaderWord(input.subject)}`,
    "MIME-Version: 1.0",
  ].filter(Boolean);
  const normalizedText = input.bodyText.replace(/\r?\n/g, "\r\n");
  const normalizedHtml = input.bodyHtml?.trim()
    ? input.bodyHtml.replace(/\r?\n/g, "\r\n")
    : "";
  const attachments = input.attachments ?? [];
  const inlineAttachments = attachments.filter(
    (attachment) => attachment.contentDisposition === "inline",
  );
  const regularAttachments = attachments.filter(
    (attachment) => attachment.contentDisposition !== "inline",
  );
  const hasHtmlBody = normalizedHtml.length > 0;
  const bodyPartLines = hasHtmlBody
    ? buildAlternativePartLines(normalizedText, normalizedHtml)
    : buildTextPlainPartLines(normalizedText);

  if (attachments.length === 0) {
    if (!hasHtmlBody) {
      return [...commonHeaders, ...bodyPartLines].join("\r\n");
    }

    return [...commonHeaders, ...bodyPartLines].join("\r\n");
  }

  const mixedBoundary = `----=_OfficialMail_Mixed_${randomUUID()}`;
  const lines = [
    ...commonHeaders,
    `Content-Type: multipart/mixed; boundary="${mixedBoundary}"`,
    "",
  ];

  if (inlineAttachments.length > 0) {
    const relatedBoundary = `----=_OfficialMail_Related_${randomUUID()}`;
    lines.push(`--${mixedBoundary}`);
    lines.push(
      `Content-Type: multipart/related; boundary="${relatedBoundary}"`,
    );
    lines.push("");
    lines.push(`--${relatedBoundary}`);
    lines.push(...bodyPartLines);
    lines.push("");

    for (const attachment of inlineAttachments) {
      lines.push(`--${relatedBoundary}`);
      lines.push(...buildMimeAttachmentPartLines(attachment));
      lines.push("");
    }

    lines.push(`--${relatedBoundary}--`);
    lines.push("");
  } else {
    lines.push(`--${mixedBoundary}`);
    lines.push(...bodyPartLines);
    lines.push("");
  }

  for (const attachment of regularAttachments) {
    lines.push(`--${mixedBoundary}`);
    lines.push(...buildMimeAttachmentPartLines(attachment));
    lines.push("");
  }

  lines.push(`--${mixedBoundary}--`);
  return lines.join("\r\n");
}

type SystemWelcomeMailboxConfig = {
  displayName: string;
  email: string;
  password: string;
};

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

function formatRecipientSuggestionLabel(
  email: string,
  displayName?: string | null,
) {
  const normalizedName = displayName?.trim() ?? "";
  return normalizedName ? `${normalizedName} <${email}>` : email;
}

function normalizeMailSearchField(value: string | undefined): MailSearchField {
  if (
    value === "from" ||
    value === "to" ||
    value === "subject" ||
    value === "body"
  ) {
    return value;
  }

  return "all";
}

function normalizeMailSearchQuery(value: string | undefined) {
  return (value ?? "").trim().replace(/\s+/g, " ").slice(0, 120);
}

function escapeSqlLike(value: string) {
  return value.replace(/[\\%_]/g, "\\$&");
}

function buildMailboxSearchSql(input: {
  field: MailSearchField;
  query: string;
}) {
  if (!input.query) {
    return {
      params: [] as string[],
      sql: "",
    };
  }

  const likeQuery = `%${escapeSqlLike(input.query.toLowerCase())}%`;

  if (input.field === "from") {
    return {
      params: [likeQuery, likeQuery],
      sql: "AND (LOWER(COALESCE(mm.from_name, '')) LIKE ? ESCAPE '\\\\' OR LOWER(mm.from_address) LIKE ? ESCAPE '\\\\')",
    };
  }

  if (input.field === "to") {
    return {
      params: [likeQuery],
      sql: "AND LOWER(mm.to_addresses) LIKE ? ESCAPE '\\\\'",
    };
  }

  if (input.field === "subject") {
    return {
      params: [likeQuery],
      sql: "AND LOWER(mm.subject) LIKE ? ESCAPE '\\\\'",
    };
  }

  if (input.field === "body") {
    return {
      params: [likeQuery, likeQuery],
      sql: "AND (LOWER(mm.snippet) LIKE ? ESCAPE '\\\\' OR LOWER(mm.body_text) LIKE ? ESCAPE '\\\\')",
    };
  }

  return {
    params: [likeQuery, likeQuery, likeQuery, likeQuery, likeQuery],
    sql: `
      AND (
        LOWER(COALESCE(mm.from_name, '')) LIKE ? ESCAPE '\\\\'
        OR LOWER(mm.from_address) LIKE ? ESCAPE '\\\\'
        OR LOWER(mm.to_addresses) LIKE ? ESCAPE '\\\\'
        OR LOWER(mm.subject) LIKE ? ESCAPE '\\\\'
        OR LOWER(CONCAT_WS(' ', mm.snippet, mm.body_text)) LIKE ? ESCAPE '\\\\'
      )
    `,
  };
}

const RECIPIENT_ADDRESS_PATTERN = /^[^\s@]+@[^\s@]+\.[^\s@]+$/i;

function normalizeEmailAddress(value: string) {
  return value.trim().toLowerCase();
}

function isValidEmailAddress(value: string) {
  return RECIPIENT_ADDRESS_PATTERN.test(value);
}

function createSha256Hex(value: string) {
  return createHash("sha256").update(value).digest("hex");
}

function createSixDigitVerificationCode() {
  return String(Math.floor(Math.random() * 1_000_000)).padStart(6, "0");
}

function maskEmailAddress(email: string) {
  const normalizedEmail = normalizeEmailAddress(email);
  const [localPart, domain] = normalizedEmail.split("@");

  if (!localPart || !domain) {
    return normalizedEmail;
  }

  const maskedLocalPart =
    localPart.length <= 2
      ? `${localPart[0] ?? "*"}*`
      : `${localPart.slice(0, 2)}${"*".repeat(Math.max(2, localPart.length - 3))}${localPart.slice(-1)}`;

  return `${maskedLocalPart}@${domain}`;
}

function normalizeRecipientList(value: string) {
  const recipients = splitRecipientList(value);
  const seen = new Set<string>();

  return recipients.filter((recipient) => {
    if (!RECIPIENT_ADDRESS_PATTERN.test(recipient)) {
      throw new Error("invalid-recipient");
    }

    if (seen.has(recipient)) {
      return false;
    }

    seen.add(recipient);
    return true;
  });
}

function htmlToPlainText(value: string) {
  const withoutLineBreakTags = value
    .replace(/<(br|\/p|\/div|\/li|\/blockquote|\/h[1-6])\b[^>]*>/gi, "\n")
    .replace(/<(li)\b[^>]*>/gi, "• ");

  return withoutLineBreakTags
    .replace(/<style[\s\S]*?<\/style>/gi, "")
    .replace(/<script[\s\S]*?<\/script>/gi, "")
    .replace(/<[^>]+>/g, " ")
    .replace(/&nbsp;/gi, " ")
    .replace(/&amp;/gi, "&")
    .replace(/&lt;/gi, "<")
    .replace(/&gt;/gi, ">")
    .replace(/&quot;/gi, '"')
    .replace(/&#39;/gi, "'")
    .replace(/\r/g, "")
    .replace(/[ \t]+\n/g, "\n")
    .replace(/\n{3,}/g, "\n\n")
    .replace(/[ \t]{2,}/g, " ")
    .trim();
}

function getSystemWelcomeMailboxConfig(): SystemWelcomeMailboxConfig | null {
  const email = (
    process.env.SYSTEM_WELCOME_MAILBOX_EMAIL ?? "admin@officialsite.kr"
  )
    .trim()
    .toLowerCase();
  const password = (
    process.env.SYSTEM_WELCOME_MAILBOX_PASSWORD ??
    process.env.MAILBOX_CREDENTIAL_SECRET ??
    process.env.DB_PASSWORD ??
    ""
  ).trim();
  const displayName =
    (process.env.SYSTEM_WELCOME_MAILBOX_NAME ?? "오피셜메일").trim() ||
    "오피셜메일";

  if (!email || !password) {
    return null;
  }

  return { displayName, email, password };
}

function getSystemNoReplyMailboxConfig(): SystemWelcomeMailboxConfig | null {
  const email = (
    process.env.SYSTEM_NOREPLY_MAILBOX_EMAIL ?? "noreply@officialsite.kr"
  )
    .trim()
    .toLowerCase();
  const password = (
    process.env.SYSTEM_NOREPLY_MAILBOX_PASSWORD ??
    process.env.SYSTEM_WELCOME_MAILBOX_PASSWORD ??
    process.env.MAILBOX_CREDENTIAL_SECRET ??
    process.env.DB_PASSWORD ??
    ""
  ).trim();
  const displayName =
    (process.env.SYSTEM_NOREPLY_MAILBOX_NAME ?? "오피셜메일").trim() ||
    "오피셜메일";

  if (!email || !password) {
    return null;
  }

  return { displayName, email, password };
}

async function sendSystemMailboxMessage(
  mailbox: SystemWelcomeMailboxConfig,
  input: {
    bodyHtml?: string | null;
    bodyText: string;
    recipients: string[];
    subject: string;
  },
) {
  const [localPart, domain] = mailbox.email.split("@");

  if (!localPart || !domain) {
    throw new Error("system-mailbox-invalid");
  }

  await ensureMailcowMailboxAccount({
    displayName: mailbox.displayName,
    domain,
    localPart,
    mailboxPassword: mailbox.password,
  });

  const messageIdHeader = createMessageIdHeader(domain);
  const rawSource = buildRawSource({
    bodyHtml: input.bodyHtml ?? (plainTextToHtml(input.bodyText) || null),
    bodyText: input.bodyText,
    messageIdHeader,
    fromAddress: mailbox.email,
    fromName: mailbox.displayName,
    toAddresses: input.recipients.join(", "),
    subject: input.subject,
  });
  const credentials = {
    email: mailbox.email,
    password: mailbox.password,
  };

  await sendSmtpMessage(credentials, {
    rawSource,
    recipients: input.recipients,
  });

  try {
    await appendSentMessage(credentials, rawSource);
  } catch {
    // System mail sending should still succeed even if Sent append fails.
  }
}

function buildSystemWelcomeBody(input: {
  companyName: string;
  mailboxEmail: string;
}) {
  const companyName = input.companyName.trim() || "대표님";

  return [
    `${companyName}님, 오피셜메일에 오신 것을 환영합니다.`,
    "",
    `대표 메일 ${input.mailboxEmail} 준비가 완료되었습니다.`,
    "이제 받은메일함에서 실제 송수신을 바로 시작할 수 있습니다.",
    "",
    "처음 설정 순서",
    "1. 도메인 DNS 값 반영",
    "2. 연결 상태 확인",
    "3. 테스트 메일 1회 발송",
    "",
    "문의가 필요하면 이 메일에 답장해주세요.",
    "",
    "오피셜메일",
  ].join("\n");
}

function buildSignupRecoveryEmailBody(code: string) {
  return [
    "오피셜메일 회원가입 보조 이메일 인증번호입니다.",
    "",
    `인증번호: ${code}`,
    `유효시간: ${Math.floor(SIGNUP_RECOVERY_EMAIL_CODE_TTL_MS / 60_000)}분`,
    "",
    "직접 요청하지 않았다면 이 메일은 무시하셔도 됩니다.",
    "",
    "오피셜메일",
  ].join("\n");
}

function buildPasswordResetLinkBody(input: {
  companyName: string;
  resetUrl: string;
}) {
  const companyName = input.companyName.trim() || "회원";

  return [
    `${companyName} 계정의 비밀번호 재설정 링크입니다.`,
    "",
    input.resetUrl,
    "",
    `유효시간: ${Math.floor(PASSWORD_RESET_TOKEN_TTL_MS / 60_000)}분`,
    "링크에 접속하면 새 비밀번호를 바로 설정할 수 있습니다.",
    "",
    "직접 요청하지 않았다면 이 메일은 무시하셔도 됩니다.",
    "",
    "오피셜메일",
  ].join("\n");
}

function hashPassword(password: string) {
  const salt = randomBytes(16).toString("hex");
  const digest = scryptSync(password, salt, 64).toString("hex");
  return `${salt}:${digest}`;
}

function verifyPassword(password: string, storedHash: string) {
  const [salt, storedDigest] = storedHash.split(":");

  if (!salt || !storedDigest) {
    return false;
  }

  const computedDigest = scryptSync(password, salt, 64);
  const storedBuffer = Buffer.from(storedDigest, "hex");

  if (computedDigest.length !== storedBuffer.length) {
    return false;
  }

  return timingSafeEqual(computedDigest, storedBuffer);
}

async function withTransaction<T>(
  callback: (connection: PoolConnection) => Promise<T>,
) {
  const pool = getDbPool();
  const connection = await pool.getConnection();

  try {
    await connection.beginTransaction();
    const result = await callback(connection);
    await connection.commit();
    return result;
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

function isRetryableMailboxSyncWriteError(error: unknown) {
  if (!error || typeof error !== "object") {
    return false;
  }

  const code = "code" in error ? String(error.code) : "";
  const errno = "errno" in error ? Number(error.errno) : 0;

  return (
    code === "ER_LOCK_DEADLOCK" ||
    code === "ER_LOCK_WAIT_TIMEOUT" ||
    errno === 1213 ||
    errno === 1205
  );
}

function waitForMailboxSyncRetry(delayMs: number) {
  return new Promise<void>((resolve) => {
    setTimeout(resolve, delayMs);
  });
}

async function withMailboxSyncQueue<T>(
  mailboxId: number,
  callback: () => Promise<T>,
) {
  const previous = mailboxSyncQueues.get(mailboxId) ?? Promise.resolve();
  let releaseCurrent!: () => void;
  const current = new Promise<void>((resolve) => {
    releaseCurrent = resolve;
  });
  const queue = previous.catch(() => undefined).then(() => current);

  mailboxSyncQueues.set(mailboxId, queue);
  await previous.catch(() => undefined);

  try {
    return await callback();
  } finally {
    releaseCurrent();

    if (mailboxSyncQueues.get(mailboxId) === queue) {
      mailboxSyncQueues.delete(mailboxId);
    }
  }
}

async function retryMailboxSyncWrite<T>(callback: () => Promise<T>) {
  for (let attempt = 0; ; attempt += 1) {
    try {
      return await callback();
    } catch (error) {
      const retryDelay = MAILBOX_SYNC_WRITE_RETRY_DELAYS_MS[attempt];

      if (!isRetryableMailboxSyncWriteError(error) || retryDelay === undefined) {
        throw error;
      }

      await waitForMailboxSyncRetry(retryDelay);
    }
  }
}

async function emitSignupProvisioningProgress(
  callback:
    | ((progress: SignupProvisioningProgress) => void | Promise<void>)
    | undefined,
  progress: SignupProvisioningProgress,
) {
  await callback?.(progress);
}

async function getUserByEmail(email: string) {
  await ensureOfficialMailSchema();
  const pool = getDbPool();
  const [rows] = await pool.query<UserRow[]>(
    `
      SELECT
        id,
        email,
        company_name,
        display_name,
        recovery_email,
        recovery_email_verified_at,
        password_hash,
        mail_configured,
        mail_sidebar_width,
        mail_list_width,
        mail_list_height,
        mail_view_mode,
        mail_sort_order
      FROM users
      WHERE email = ?
      LIMIT 1
    `,
    [email],
  );

  return rows[0] ?? null;
}

async function getUserById(userId: number) {
  await ensureOfficialMailSchema();
  const pool = getDbPool();
  const [rows] = await pool.query<UserRow[]>(
    `
      SELECT
        id,
        email,
        company_name,
        display_name,
        recovery_email,
        recovery_email_verified_at,
        password_hash,
        mail_configured,
        mail_sidebar_width,
        mail_list_width,
        mail_list_height,
        mail_view_mode,
        mail_sort_order
      FROM users
      WHERE id = ?
      LIMIT 1
    `,
    [userId],
  );

  return rows[0] ?? null;
}

async function getSignupRecoveryEmailVerificationById(
  verificationId: number,
  queryable: Pool | PoolConnection = getDbPool(),
) {
  const [rows] = await queryable.query<SignupRecoveryEmailVerificationRow[]>(
    `
      SELECT
        id,
        email,
        code_hash,
        verification_token_hash,
        expires_at,
        resend_available_at,
        verified_at,
        consumed_at
      FROM signup_recovery_email_verifications
      WHERE id = ?
      LIMIT 1
    `,
    [verificationId],
  );

  return rows[0] ?? null;
}

async function getLatestSignupRecoveryEmailVerificationByEmail(email: string) {
  const [rows] = await getDbPool().query<SignupRecoveryEmailVerificationRow[]>(
    `
      SELECT
        id,
        email,
        code_hash,
        verification_token_hash,
        expires_at,
        resend_available_at,
        verified_at,
        consumed_at
      FROM signup_recovery_email_verifications
      WHERE email = ?
      ORDER BY id DESC
      LIMIT 1
    `,
    [email],
  );

  return rows[0] ?? null;
}

async function getPasswordResetTokenByHash(tokenHash: string) {
  const [rows] = await getDbPool().query<PasswordResetTokenRow[]>(
    `
      SELECT
        id,
        user_id,
        recovery_email,
        token_hash,
        expires_at,
        used_at
      FROM password_reset_tokens
      WHERE token_hash = ?
      LIMIT 1
    `,
    [tokenHash],
  );

  return rows[0] ?? null;
}

async function getLatestSetupByUserId(userId: number) {
  const pool = getDbPool();
  const [mailboxRows] = await pool.query<MailSetupRow[]>(
    `
      SELECT
        d.id AS domain_id,
        d.domain,
        d.status AS domain_status,
        d.dkim_enabled,
        d.dkim_selector,
        d.dkim_public_key,
        d.mailcow_cleanup_at,
        d.verified_at,
        m.email AS mailbox_email,
        m.local_part,
        m.status AS mailbox_status
      FROM mailboxes m
      INNER JOIN domains d ON d.id = m.domain_id
      WHERE m.user_id = ?
      ORDER BY m.id DESC
      LIMIT 1
    `,
    [userId],
  );

  if (mailboxRows[0]) {
    return mailboxRows[0];
  }

  const [rows] = await pool.query<MailSetupRow[]>(
    `
      SELECT
        d.id AS domain_id,
        d.domain,
        d.status AS domain_status,
        d.dkim_enabled,
        d.dkim_selector,
        d.dkim_public_key,
        d.mailcow_cleanup_at,
        d.verified_at,
        '' AS mailbox_email,
        '' AS local_part,
        'pending_dns' AS mailbox_status
      FROM domains d
      WHERE d.user_id = ?
      ORDER BY d.id DESC
      LIMIT 1
    `,
    [userId],
  );

  return rows[0] ?? null;
}

async function getMailSetupByDomainId(domainId: number) {
  const [rows] = await getDbPool().query<DomainVerificationTargetRow[]>(
    `
      SELECT
        d.id AS domain_id,
        d.domain,
        d.status AS domain_status,
        d.dkim_enabled,
        d.dkim_selector,
        d.dkim_public_key,
        d.mailcow_cleanup_at,
        d.verified_at,
        d.user_id AS owner_user_id,
        COALESCE(m.email, '') AS mailbox_email,
        COALESCE(m.local_part, '') AS local_part,
        COALESCE(m.status, 'pending_dns') AS mailbox_status
      FROM domains d
      LEFT JOIN mailboxes m
        ON m.domain_id = d.id
        AND m.user_id = d.user_id
      WHERE d.id = ?
      ORDER BY m.id DESC
      LIMIT 1
    `,
    [domainId],
  );

  return rows[0] ?? null;
}

async function getLatestMailboxByUserId(userId: number) {
  const pool = getDbPool();
  const [rows] = await pool.query<MailboxRow[]>(
    `
      SELECT
        m.id,
        m.email,
        m.password_ciphertext,
        m.last_sync_at,
        m.last_sync_error,
        m.message_visibility_cutoff_at,
        m.send_cooldown_until,
        m.status,
        d.domain
      FROM mailboxes m
      INNER JOIN domains d ON d.id = m.domain_id
      WHERE m.user_id = ?
      ORDER BY m.id DESC
      LIMIT 1
    `,
    [userId],
  );

  return rows[0] ?? null;
}

async function getDnsRecords(domainId: number) {
  const pool = getDbPool();
  const [rows] = await pool.query<DnsRecordRow[]>(
    `
      SELECT record_type, host_name, value_text, priority, status, last_checked_at
      FROM dns_records
      WHERE domain_id = ?
      ORDER BY FIELD(record_type, 'MX', 'TXT'), host_name
    `,
    [domainId],
  );

  return rows;
}

function buildSendCooldownDetail(until: Date) {
  const remainingMs = until.getTime() - Date.now();

  if (remainingMs <= 0) {
    return "잠시 후 다시 시도해주세요.";
  }

  const remainingMinutes = Math.max(1, Math.ceil(remainingMs / 60_000));
  return `약 ${remainingMinutes}분 후 다시 시도해주세요.`;
}

async function isGrowthPlanActiveByOwnerUserId(
  queryable: Pool | PoolConnection,
  ownerUserId: number,
) {
  const [ownerRows] = await queryable.query<
    (RowDataPacket & { effective_owner_user_id: number })[]
  >(
    `
      SELECT COALESCE(mtm.owner_user_id, u.id) AS effective_owner_user_id
      FROM users u
      LEFT JOIN managed_team_mailboxes mtm
        ON mtm.email = u.email
       AND mtm.status = 'active'
      WHERE u.id = ?
      LIMIT 1
    `,
    [ownerUserId],
  );
  const effectiveOwnerUserId = ownerRows[0]?.effective_owner_user_id ?? ownerUserId;
  const [rows] = await queryable.query<(RowDataPacket & { id: number })[]>(
    `
      SELECT id
      FROM mailbox_toss_pay_subscriptions
      WHERE owner_user_id = ?
        AND status = 'active'
      LIMIT 1
    `,
    [effectiveOwnerUserId],
  );

  return rows.length > 0;
}

async function countDistinctSendAttempts(
  queryable: Pool | PoolConnection,
  mailboxId: number,
  createdAfter: Date,
) {
  const [rows] = await queryable.query<CountTotalRow[]>(
    `
      SELECT COUNT(DISTINCT mailbox_message_id) AS total
      FROM mailbox_delivery_logs
      WHERE mailbox_id = ?
        AND action = 'send'
        AND created_at >= ?
    `,
    [mailboxId, createdAfter],
  );

  return Number(rows[0]?.total ?? 0);
}

async function assertMailboxSendAllowed(input: {
  activateCooldownOnBurst?: boolean;
  connection?: PoolConnection;
  mailboxId: number;
  ownerUserId: number;
  requestedSendCount?: number;
}) {
  const queryable = input.connection ?? getDbPool();
  const requestedSendCount = Math.max(
    1,
    Math.floor(input.requestedSendCount ?? 1),
  );
  const mailboxLockClause = input.connection ? "FOR UPDATE" : "";
  const [mailboxRows] = await queryable.query<
    (RowDataPacket & { send_cooldown_until: Date | null })[]
  >(
    `
      SELECT send_cooldown_until
      FROM mailboxes
      WHERE id = ?
      LIMIT 1
      ${mailboxLockClause}
    `,
    [input.mailboxId],
  );
  const mailboxRow = mailboxRows[0];

  if (!mailboxRow) {
    throw new Error("mailbox-not-found");
  }

  const cooldownUntil = mailboxRow.send_cooldown_until;

  if (cooldownUntil && cooldownUntil.getTime() > Date.now()) {
    throw new Error(
      `send-cooldown-active:${buildSendCooldownDetail(cooldownUntil)}`,
    );
  }

  if (cooldownUntil && cooldownUntil.getTime() <= Date.now()) {
    await queryable.query(
      `
        UPDATE mailboxes
        SET send_cooldown_until = NULL, updated_at = NOW()
        WHERE id = ?
      `,
      [input.mailboxId],
    );
  }

  const growthPlanActive = await isGrowthPlanActiveByOwnerUserId(
    queryable,
    input.ownerUserId,
  );

  if (!growthPlanActive) {
    const dayStart = new Date();
    dayStart.setHours(0, 0, 0, 0);
    const todaySendCount = await countDistinctSendAttempts(
      queryable,
      input.mailboxId,
      dayStart,
    );

    if (todaySendCount + requestedSendCount > FREE_PLAN_DAILY_SEND_LIMIT) {
      throw new Error("free-send-limit-daily");
    }

    return;
  }

  if (!input.activateCooldownOnBurst) {
    return;
  }

  const burstWindowStart = new Date(Date.now() - GROWTH_PLAN_BURST_WINDOW_MS);
  const burstSendCount = await countDistinctSendAttempts(
    queryable,
    input.mailboxId,
    burstWindowStart,
  );

  if (burstSendCount + requestedSendCount <= GROWTH_PLAN_BURST_SEND_LIMIT - 1) {
    return;
  }

  const nextCooldownUntil = new Date(Date.now() + GROWTH_PLAN_SEND_COOLDOWN_MS);

  await queryable.query(
    `
      UPDATE mailboxes
      SET send_cooldown_until = ?, updated_at = NOW()
      WHERE id = ?
    `,
    [nextCooldownUntil, input.mailboxId],
  );

  throw new Error(
    `send-cooldown-active:${buildSendCooldownDetail(nextCooldownUntil)}`,
  );
}

async function logMailboxDelivery(
  connection: PoolConnection,
  input: {
    mailboxMessageId: number;
    mailboxId: number;
    action: "send" | "draft" | "receive";
    status: "saved" | "queued" | "sent" | "failed";
    transport: string;
    sourceAddress: string;
    targetAddress: string;
    subject: string;
    messageIdHeader: string | null;
    responseText: string | null;
  },
) {
  await connection.query(
    `
      INSERT INTO mailbox_delivery_logs (
        mailbox_message_id,
        mailbox_id,
        action,
        status,
        transport,
        source_address,
        target_address,
        subject,
        message_id_header,
        response_text
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `,
    [
      input.mailboxMessageId,
      input.mailboxId,
      input.action,
      input.status,
      input.transport,
      input.sourceAddress,
      input.targetAddress,
      input.subject,
      input.messageIdHeader,
      input.responseText,
    ],
  );
}

function getSystemNoticeSender() {
  return {
    email: (
      process.env.SYSTEM_NOTICE_EMAIL ??
      process.env.SYSTEM_WELCOME_MAILBOX_EMAIL ??
      "admin@officialsite.kr"
    )
      .trim()
      .toLowerCase(),
    name:
      (process.env.SYSTEM_NOTICE_NAME ?? "오피셜메일 알림").trim() ||
      "오피셜메일 알림",
  };
}

type MailboxQueuedSendTarget = {
  envelopeRecipients: string[];
  logRecipients: string;
  messageIdHeader: string;
  rawSource: string;
  visibleRecipients: string;
};

function buildQueuedSendTarget(input: {
  attachments: ComposeRawAttachment[];
  bccRecipients: string[];
  body: string;
  bodyHtml: string | null;
  ccRecipients: string[];
  fromAddress: string;
  fromName: string | null;
  subject: string;
  toRecipients: string[];
}) {
  const envelopeRecipients = [
    ...input.toRecipients,
    ...input.ccRecipients,
    ...input.bccRecipients,
  ].filter((recipient, index, array) => array.indexOf(recipient) === index);
  const visibleRecipients = [
    input.toRecipients.join(", "),
    input.ccRecipients.length > 0
      ? `참조: ${input.ccRecipients.join(", ")}`
      : "",
  ]
    .filter(Boolean)
    .join(" / ");
  const messageIdHeader = createMessageIdHeader(
    input.fromAddress.split("@")[1] || "officialsite.kr",
  );

  return {
    envelopeRecipients,
    logRecipients: envelopeRecipients.join(", "),
    messageIdHeader,
    rawSource: buildRawSource({
      attachments: input.attachments,
      bodyHtml: input.bodyHtml,
      bodyText: input.body,
      ccAddresses: input.ccRecipients.join(", "),
      messageIdHeader,
      fromAddress: input.fromAddress,
      fromName: input.fromName,
      subject: input.subject,
      toAddresses: input.toRecipients.join(", "),
    }),
    visibleRecipients: visibleRecipients || input.toRecipients.join(", "),
  } satisfies MailboxQueuedSendTarget;
}

function buildQueuedSendTargets(input: {
  attachments: ComposeRawAttachment[];
  bccRecipients: string[];
  body: string;
  bodyHtml: string | null;
  ccRecipients: string[];
  fromAddress: string;
  fromName: string | null;
  sendIndividually: boolean;
  subject: string;
  toRecipients: string[];
}) {
  if (input.sendIndividually && input.toRecipients.length > 1) {
    return input.toRecipients.map((recipient) =>
      buildQueuedSendTarget({
        attachments: input.attachments,
        bccRecipients: input.bccRecipients,
        body: input.body,
        bodyHtml: input.bodyHtml,
        ccRecipients: input.ccRecipients,
        fromAddress: input.fromAddress,
        fromName: input.fromName,
        subject: input.subject,
        toRecipients: [recipient],
      }),
    );
  }

  return [
    buildQueuedSendTarget({
      attachments: input.attachments,
      bccRecipients: input.bccRecipients,
      body: input.body,
      bodyHtml: input.bodyHtml,
      ccRecipients: input.ccRecipients,
      fromAddress: input.fromAddress,
      fromName: input.fromName,
      subject: input.subject,
      toRecipients: input.toRecipients,
    }),
  ];
}

function normalizeDeliveryFailureReason(error: unknown) {
  const message =
    error instanceof Error ? error.message : "알 수 없는 전송 오류";

  return message
    .replace(/^mailbox-smtp-failed:/, "")
    .replace(/^mailbox-imap-failed:/, "")
    .replace(/\s+/g, " ")
    .trim()
    .slice(0, 500);
}

function buildDeliveryFailureNoticeBody(input: {
  mailboxAddress: string;
  reason: string;
  subject: string;
  targetAddress: string;
}) {
  return [
    "발송한 메일이 정상적으로 전달되지 않았습니다.",
    "",
    `제목: ${input.subject || "(제목 없음)"}`,
    `받는 사람: ${input.targetAddress}`,
    `보낸 계정: ${input.mailboxAddress}`,
    "",
    `사유: ${input.reason}`,
    "",
    "수신 주소와 메일 서버 설정을 다시 확인한 뒤 재전송해주세요.",
  ].join("\n");
}

async function insertLocalSystemNotice(
  connection: PoolConnection,
  input: {
    mailboxDomain: string;
    mailboxEmail: string;
    mailboxId: number;
    bodyText: string;
    subject: string;
  },
) {
  await ensureLocalMailboxFolders(connection, input.mailboxId);

  const [folderRows] = await connection.query<
    (RowDataPacket & { id: number })[]
  >(
    "SELECT id FROM mailbox_folders WHERE mailbox_id = ? AND system_name = 'inbox' LIMIT 1",
    [input.mailboxId],
  );
  const inboxFolderId = folderRows[0]?.id;

  if (!inboxFolderId) {
    return;
  }

  const sender = getSystemNoticeSender();
  const messageIdHeader = createMessageIdHeader(input.mailboxDomain);
  const bodyHtml = plainTextToHtml(input.bodyText) || null;
  const rawSource = buildRawSource({
    bodyHtml,
    bodyText: input.bodyText,
    messageIdHeader,
    fromAddress: sender.email,
    fromName: sender.name,
    toAddresses: input.mailboxEmail,
    subject: input.subject,
  });
  const mailboxMessageId = await upsertMailboxMessageRow(connection, {
    bodyHtml,
    bodyText: input.bodyText,
    direction: "inbound",
    folderId: inboxFolderId,
    fromAddress: sender.email,
    fromName: sender.name,
    isRead: false,
    isStarred: false,
    mailboxId: input.mailboxId,
    messageIdHeader,
    preserveExistingTransport: false,
    rawSource,
    receivedAt: new Date(),
    remoteFlags: null,
    remoteFolder: null,
    remoteUid: null,
    snippet: input.bodyText.replace(/\s+/g, " ").slice(0, 140),
    subject: input.subject,
    toAddresses: input.mailboxEmail,
    transportResponse: "오피셜메일 시스템 알림",
    transportStatus: "sent",
  });

  await logMailboxDelivery(connection, {
    mailboxMessageId,
    mailboxId: input.mailboxId,
    action: "receive",
    status: "sent",
    transport: "system",
    sourceAddress: sender.email,
    targetAddress: input.mailboxEmail,
    subject: input.subject,
    messageIdHeader,
    responseText: "오피셜메일 시스템 알림",
  });
}

async function hasStoredSystemWelcomeMail(input: {
  mailboxId: number;
  sourceAddress: string;
}) {
  const [rows] = await getDbPool().query<
    (RowDataPacket & { exists_flag: number })[]
  >(
    `
      SELECT 1 AS exists_flag
      FROM mailbox_messages
      WHERE mailbox_id = ?
        AND from_address = ?
        AND subject = ?
      LIMIT 1
    `,
    [input.mailboxId, input.sourceAddress, SYSTEM_WELCOME_SUBJECT],
  );

  return rows.length > 0;
}

async function sendSystemWelcomeMail(input: {
  companyName: string;
  mailboxId: number;
  mailboxEmail: string;
}) {
  const systemMailbox = getSystemWelcomeMailboxConfig();

  if (!systemMailbox) {
    return false;
  }

  if (
    await hasStoredSystemWelcomeMail({
      mailboxId: input.mailboxId,
      sourceAddress: systemMailbox.email,
    })
  ) {
    return false;
  }

  const [localPart, domain] = systemMailbox.email.split("@");

  if (!localPart || !domain) {
    return false;
  }

  await ensureMailcowMailboxAccount({
    displayName: systemMailbox.displayName,
    domain,
    localPart,
    mailboxPassword: systemMailbox.password,
  });

  const messageIdHeader = createMessageIdHeader(domain);
  const body = buildSystemWelcomeBody(input);
  const rawSource = buildRawSource({
    bodyText: body,
    messageIdHeader,
    fromAddress: systemMailbox.email,
    fromName: systemMailbox.displayName,
    toAddresses: input.mailboxEmail,
    subject: SYSTEM_WELCOME_SUBJECT,
  });
  const credentials = {
    email: systemMailbox.email,
    password: systemMailbox.password,
  };
  await sendSmtpMessage(credentials, {
    rawSource,
    recipients: [input.mailboxEmail],
  });

  try {
    await appendSentMessage(credentials, rawSource);
  } catch {
    // The welcome mail should still be considered sent even if the sender's Sent append fails.
  }
  return true;
}

async function finalizeQueuedMailboxSend(input: {
  logRecipients: string;
  mailbox: MailboxRow;
  mailboxMessageId: number;
  messageIdHeader: string;
  rawSource: string;
  recipients: string[];
  subject: string;
  visibleRecipients: string;
}) {
  let transport = "smtp";
  let transportResponse = "";
  let remoteFolderName = "Sent";
  let remoteUid: number | null = null;

  try {
    const credentials = toMailboxCredentials(input.mailbox);
    const sendResult = await sendSmtpMessage(credentials, {
      rawSource: input.rawSource,
      recipients: input.recipients,
    });

    transportResponse = sendResult.response;

    try {
      const appendResult = await appendSentMessage(
        credentials,
        input.rawSource,
      );
      remoteFolderName = appendResult?.destination ?? remoteFolderName;
      remoteUid = appendResult?.uid ?? null;

      if (!appendResult?.uid) {
        transportResponse = `${transportResponse} / Sent 폴더 UID 확인은 생략되었습니다.`;
      }
    } catch (error) {
      const appendError =
        error instanceof Error ? error.message : "sent-append-failed";
      transportResponse = `${transportResponse} / Sent 폴더 append 실패: ${appendError}`;
      transport = "smtp+imap";
    }

    await withTransaction(async (connection) => {
      const [folderRows] = await connection.query<
        (RowDataPacket & { id: number; remote_name: string | null })[]
      >(
        "SELECT id, remote_name FROM mailbox_folders WHERE mailbox_id = ? AND system_name = 'sent' LIMIT 1",
        [input.mailbox.id],
      );
      const sentFolderId = folderRows[0]?.id;

      if (sentFolderId && folderRows[0]?.remote_name !== remoteFolderName) {
        await connection.query(
          `
            UPDATE mailbox_folders
            SET remote_name = ?, updated_at = NOW()
            WHERE id = ?
          `,
          [remoteFolderName, sentFolderId],
        );
      }

      await connection.query(
        `
          UPDATE mailbox_messages
          SET
            remote_uid = ?,
            remote_folder = ?,
            transport_status = 'sent',
            transport_response = ?,
            remote_flags = '\\\\Seen',
            updated_at = NOW()
          WHERE id = ? AND mailbox_id = ?
        `,
        [
          remoteUid,
          remoteFolderName,
          transportResponse,
          input.mailboxMessageId,
          input.mailbox.id,
        ],
      );

      await logMailboxDelivery(connection, {
        mailboxMessageId: input.mailboxMessageId,
        mailboxId: input.mailbox.id,
        action: "send",
        status: "sent",
        transport,
        sourceAddress: input.mailbox.email,
        targetAddress: input.logRecipients,
        subject: input.subject,
        messageIdHeader: input.messageIdHeader,
        responseText: transportResponse,
      });
    });

    await updateMailboxSyncState(input.mailbox.id, {
      lastSyncAt: new Date(),
      lastSyncError: null,
    });

    return {
      status: "sent" as const,
    };
  } catch (error) {
    const reason = normalizeDeliveryFailureReason(error);
    const rawMessage = error instanceof Error ? error.message : "";
    const errorMessage =
      rawMessage.startsWith("mailbox-smtp-failed:") ||
      rawMessage.startsWith("mailbox-imap-failed:")
        ? rawMessage
        : `mailbox-smtp-failed:${reason}`;

    await withTransaction(async (connection) => {
      await connection.query(
        `
          UPDATE mailbox_messages
          SET
            transport_status = 'failed',
            transport_response = ?,
            updated_at = NOW()
          WHERE id = ? AND mailbox_id = ?
        `,
        [reason, input.mailboxMessageId, input.mailbox.id],
      );

      await logMailboxDelivery(connection, {
        mailboxMessageId: input.mailboxMessageId,
        mailboxId: input.mailbox.id,
        action: "send",
        status: "failed",
        transport,
        sourceAddress: input.mailbox.email,
        targetAddress: input.logRecipients,
        subject: input.subject,
        messageIdHeader: input.messageIdHeader,
        responseText: reason,
      });

      await insertLocalSystemNotice(connection, {
        bodyText: buildDeliveryFailureNoticeBody({
          mailboxAddress: input.mailbox.email,
          reason,
          subject: input.subject,
          targetAddress: input.visibleRecipients || input.logRecipients,
        }),
        mailboxDomain: input.mailbox.domain,
        mailboxEmail: input.mailbox.email,
        mailboxId: input.mailbox.id,
        subject: `${SYSTEM_DELIVERY_FAILURE_PREFIX} ${input.subject || "(제목 없음)"}`,
      });
    });

    await updateMailboxSyncState(input.mailbox.id, {
      lastSyncAt: new Date(),
      lastSyncError: reason,
    });

    return {
      errorMessage,
      reason,
      status: "failed" as const,
    };
  }
}

function buildSessionSnapshot(
  user: UserRow,
  setup: MailSetupRow | null,
): SessionSnapshot {
  const hasActiveMailbox =
    !setup?.mailcow_cleanup_at &&
    Boolean(setup?.mailbox_email) &&
    setup?.mailbox_status === "active";

  return {
    email: user.email,
    companyName: user.company_name,
    displayName: user.display_name,
    mailConfigured: !setup?.mailcow_cleanup_at && (Boolean(user.mail_configured) || hasActiveMailbox),
    domain: setup?.domain ?? "",
    mailbox: setup?.mailbox_email ?? "대표@도메인",
  };
}

function buildMailLayoutPreferences(user: UserRow): MailLayoutPreferences {
  return normalizeMailLayoutPreferences({
    sidebarWidth:
      user.mail_sidebar_width ?? DEFAULT_MAIL_LAYOUT_PREFERENCES.sidebarWidth,
    listWidth:
      user.mail_list_width ?? DEFAULT_MAIL_LAYOUT_PREFERENCES.listWidth,
    listHeight:
      user.mail_list_height ?? DEFAULT_MAIL_LAYOUT_PREFERENCES.listHeight,
    viewMode:
      (user.mail_view_mode as MailLayoutPreferences["viewMode"] | undefined) ??
      DEFAULT_MAIL_LAYOUT_PREFERENCES.viewMode,
    sortOrder:
      (user.mail_sort_order as
        | MailLayoutPreferences["sortOrder"]
        | undefined) ?? DEFAULT_MAIL_LAYOUT_PREFERENCES.sortOrder,
  });
}

function buildDnsTemplate(
  domain: string,
  dkimSelector: string,
  dkimPublicKey: string,
) {
  const mxSpfHost = getMxSpfRecordHost(domain);
  const records = [
    {
      type: "MX",
      host: "@",
      value: `${MX_HOSTNAME}.`,
      priority: 10,
    },
    {
      type: "TXT",
      host: "@",
      value: SPF_RECORD_VALUE,
      priority: null,
    },
    {
      type: "TXT",
      host: `${dkimSelector}._domainkey`,
      value: `v=DKIM1; k=rsa; p=${dkimPublicKey}`,
      priority: null,
    },
    {
      type: "TXT",
      host: "_dmarc",
      value: `v=DMARC1; p=none; rua=${DMARC_RUA}`,
      priority: null,
    },
  ] satisfies Array<{
    type: string;
    host: string;
    value: string;
    priority: number | null;
  }>;

  if (mxSpfHost) {
    records.splice(2, 0, {
      type: "TXT",
      host: mxSpfHost,
      value: MX_HOST_SPF_RECORD_VALUE,
      priority: null,
    });
  }

  return records;
}

function buildPendingDnsTemplate() {
  return [
    {
      type: "MX",
      host: "@",
      value: `${MX_HOSTNAME}.`,
      priority: 10,
    },
    {
      type: "TXT",
      host: "@",
      value: SPF_RECORD_VALUE,
      priority: null,
    },
    {
      type: "TXT",
      host: "_dmarc",
      value: `v=DMARC1; p=none; rua=${DMARC_RUA}`,
      priority: null,
    },
  ] satisfies Array<{
    type: string;
    host: string;
    value: string;
    priority: number | null;
  }>;
}

function getMxSpfRecordHost(domain: string) {
  const normalizedDomain = domain.trim().toLowerCase().replace(/\.$/, "");
  const normalizedMxHostname = MX_HOSTNAME.trim().toLowerCase().replace(/\.$/, "");
  const suffix = `.${normalizedDomain}`;

  if (!normalizedDomain || !normalizedMxHostname.endsWith(suffix)) {
    return null;
  }

  return normalizedMxHostname.slice(0, -suffix.length) || "@";
}

async function syncDnsRecords(
  connection: PoolConnection,
  domainId: number,
  records: ReturnType<typeof buildDnsTemplate>,
) {
  await connection.query("DELETE FROM dns_records WHERE domain_id = ?", [
    domainId,
  ]);

  for (const record of records) {
    await connection.query(
      `
        INSERT INTO dns_records (
          domain_id,
          record_type,
          host_name,
          value_text,
          priority,
          status
        ) VALUES (?, ?, ?, ?, ?, 'pending')
      `,
      [domainId, record.type, record.host, record.value, record.priority],
    );
  }
}

async function ensureLocalMailboxFolders(
  connection: PoolConnection,
  mailboxId: number,
) {
  for (const [index, folder] of LOCAL_MAILBOX_FOLDERS.entries()) {
    const [existingRows] = await connection.query<(RowDataPacket & { id: number })[]>(
      `
        SELECT id
        FROM mailbox_folders
        WHERE mailbox_id = ?
          AND system_name = ?
        LIMIT 1
      `,
      [mailboxId, folder.systemName],
    );
    const existingId = existingRows[0]?.id;

    if (existingId) {
      await connection.query(
        `
          UPDATE mailbox_folders
          SET name = ?
          WHERE id = ?
            AND name <> ?
        `,
        [folder.displayName, existingId, folder.displayName],
      );
      continue;
    }

    await connection.query(
      `
        INSERT INTO mailbox_folders (
          mailbox_id,
          system_name,
          remote_name,
          name,
          remote_total,
          remote_unseen,
          last_synced_at,
          sort_order
        )
        VALUES (?, ?, NULL, ?, 0, 0, NULL, ?)
      `,
      [mailboxId, folder.systemName, folder.displayName, index + 1],
    );
  }
}

function normalizeFolderSystemName(folder: string | undefined | null): string {
  if (
    isSystemMailboxFolderSystemName(folder) ||
    isCustomMailboxFolderSystemName(folder)
  ) {
    return folder;
  }

  return "inbox";
}

function normalizeTargetFolderSystemName(
  folder: string | undefined | null,
): string {
  if (
    isSystemMailboxFolderSystemName(folder) ||
    isCustomMailboxFolderSystemName(folder)
  ) {
    return folder;
  }

  throw new Error("invalid-folder");
}

function resolveMailboxRefreshFolder(folder: string | undefined | null) {
  if (folder === "all") {
    return "inbox";
  }

  return normalizeFolderSystemName(folder);
}

function remoteFolderFallbackName(
  systemName: SupportedMailboxFolderSystemName,
) {
  switch (systemName) {
    case "sent":
      return "Sent";
    case "drafts":
      return "Drafts";
    case "spam":
      return "Junk";
    case "trash":
      return "Trash";
    default:
      return "INBOX";
  }
}

function normalizeMessageIds(messageIds: Array<number | string>) {
  return [
    ...new Set(
      messageIds
        .map((value) => Number(value))
        .filter((value) => Number.isInteger(value) && value > 0),
    ),
  ];
}

function normalizeSenderAddress(value: string) {
  return value.trim().toLowerCase();
}

function normalizeRemoteFlags(flags: string[]) {
  return flags.length > 0 ? flags.sort().join(" ") : null;
}

function hasRemoteFlag(
  remoteFlags: string[] | string | null | undefined,
  flag: string,
) {
  if (!remoteFlags) {
    return false;
  }

  const normalizedFlag = flag.trim().toLowerCase();
  const values = Array.isArray(remoteFlags)
    ? remoteFlags
    : remoteFlags.split(/\s+/).filter(Boolean);

  return values.some((value) => value.trim().toLowerCase() === normalizedFlag);
}

function applyRemoteFlagString(
  remoteFlags: string | null | undefined,
  flag: string,
  enabled: boolean,
) {
  const nextFlags = new Set(
    (remoteFlags ?? "")
      .split(/\s+/)
      .map((value) => value.trim())
      .filter(Boolean),
  );

  if (enabled) {
    nextFlags.add(flag);
  } else {
    nextFlags.delete(flag);
  }

  return [...nextFlags].sort().join(" ") || null;
}

function remoteFlagList(remoteFlags: string | null | undefined) {
  return [
    ...new Set(
      (remoteFlags ?? "")
        .split(/\s+/)
        .map((value) => value.trim())
        .filter(Boolean),
    ),
  ];
}

function folderDisplayNameForAction(systemName: string, fallback: string) {
  if (systemName === "inbox") return "받은메일함";
  if (systemName === "sent") return "보낸메일함";
  if (systemName === "drafts") return "임시보관함";
  if (systemName === "spam") return "스팸메일함";
  if (systemName === "trash") return "휴지통";
  return fallback;
}

function padNumber(value: number) {
  return String(value).padStart(2, "0");
}

function formatFolderBackupName(sourceLabel: string) {
  const now = new Date();
  const dateStamp = `${now.getFullYear()}-${padNumber(now.getMonth() + 1)}-${padNumber(now.getDate())}`;
  const timeStamp = `${padNumber(now.getHours())}${padNumber(now.getMinutes())}`;

  return `${sourceLabel} 백업 ${dateStamp} ${timeStamp}`;
}

function normalizeRemoteTransportResponse(message: RemoteMailboxMessage) {
  return message.transportResponse?.trim() || "메일함과 동기화했습니다.";
}

function normalizeNullableText(value: string | null | undefined) {
  return value ?? null;
}

function normalizeNullableNumber(value: number | null | undefined) {
  return value === null || value === undefined ? null : Number(value);
}

function normalizeNullableDateTime(value: Date | string | null | undefined) {
  if (!value) {
    return null;
  }

  const date = value instanceof Date ? value : new Date(value);
  const time = date.getTime();
  return Number.isNaN(time) ? null : time;
}

function toMailboxCredentials(mailbox: MailboxRow) {
  if (!mailbox.password_ciphertext) {
    throw new Error("mailbox-auth-missing");
  }

  return {
    email: mailbox.email,
    password: decryptMailboxPassword(mailbox.password_ciphertext),
  };
}

async function updateMailboxSyncState(
  mailboxId: number,
  input: {
    lastSyncAt?: Date | null;
    lastSyncError?: string | null;
  },
) {
  await getDbPool().query(
    `
      UPDATE mailboxes
      SET
        last_sync_at = ?,
        last_sync_error = ?,
        updated_at = NOW()
      WHERE id = ?
    `,
    [input.lastSyncAt ?? null, input.lastSyncError ?? null, mailboxId],
  );
}

async function getFolderRowsByMailboxId(
  connection: PoolConnection,
  mailboxId: number,
) {
  const [rows] = await connection.query<
    (RowDataPacket & {
      id: number;
      system_name: string;
      remote_name: string | null;
    })[]
  >(
    `
      SELECT id, system_name, remote_name
      FROM mailbox_folders
      WHERE mailbox_id = ?
      ORDER BY sort_order ASC, id ASC
    `,
    [mailboxId],
  );

  return rows;
}

async function createCustomMailboxFolder(
  connection: PoolConnection,
  mailbox: MailboxRow,
  input: {
    name: string;
    insertAfterSystemName?: string | null;
    insertAtTop?: boolean;
  },
) {
  await ensureLocalMailboxFolders(connection, mailbox.id);

  const normalizedName = normalizeCustomFolderName(input.name);

  if (!normalizedName) {
    throw new Error("folder-name-required");
  }

  const [existingRows] = await connection.query<
    (RowDataPacket & { id: number; name: string; system_name: string })[]
  >(
    `
      SELECT id, name, system_name
      FROM mailbox_folders
      WHERE mailbox_id = ?
    `,
    [mailbox.id],
  );

  const duplicateFolder = existingRows.find(
    (row) => row.name.trim().toLowerCase() === normalizedName.toLowerCase(),
  );

  if (duplicateFolder) {
    return {
      id: Number(duplicateFolder.id),
      name: duplicateFolder.name,
      systemName: duplicateFolder.system_name,
    };
  }

  const credentials = mailbox.password_ciphertext
    ? toMailboxCredentials(mailbox)
    : null;

  if (credentials) {
    await createRemoteMailboxFolder(credentials, normalizedName);
  }

  let nextSortOrder = 0;

  if (input.insertAtTop) {
    const [targetRows] = await connection.query<
      (RowDataPacket & { sort_order: number | null })[]
    >(
      `
        SELECT sort_order
        FROM mailbox_folders
        WHERE mailbox_id = ?
          AND system_name LIKE 'custom\\_\\_%' ESCAPE '\\\\'
        ORDER BY sort_order ASC, id ASC
        LIMIT 1
      `,
      [mailbox.id],
    );

    nextSortOrder = Number(targetRows[0]?.sort_order ?? 6);
  } else if (input.insertAfterSystemName) {
    const [targetRows] = await connection.query<
      (RowDataPacket & { sort_order: number | null })[]
    >(
      `
        SELECT sort_order
        FROM mailbox_folders
        WHERE mailbox_id = ?
          AND system_name = ?
        LIMIT 1
      `,
      [mailbox.id, input.insertAfterSystemName],
    );

    const afterSortOrder = Number(targetRows[0]?.sort_order ?? 0);
    nextSortOrder = afterSortOrder > 0 ? afterSortOrder + 1 : 0;
  }

  if (nextSortOrder <= 0) {
    const [maxSortRows] = await connection.query<
      (RowDataPacket & { max_sort_order: number | null })[]
    >(
      `
        SELECT MAX(sort_order) AS max_sort_order
        FROM mailbox_folders
        WHERE mailbox_id = ?
      `,
      [mailbox.id],
    );
    nextSortOrder = Number(maxSortRows[0]?.max_sort_order ?? 0) + 1;
  } else {
    await connection.query(
      `
        UPDATE mailbox_folders
        SET sort_order = sort_order + 1
        WHERE mailbox_id = ?
          AND sort_order >= ?
      `,
      [mailbox.id, nextSortOrder],
    );
  }

  const systemName = createCustomFolderSystemName();
  const [result] = await connection.query(
    `
      INSERT INTO mailbox_folders (
        mailbox_id,
        system_name,
        remote_name,
        name,
        remote_total,
        remote_unseen,
        last_synced_at,
        sort_order
      ) VALUES (?, ?, ?, ?, 0, 0, NULL, ?)
    `,
    [mailbox.id, systemName, normalizedName, normalizedName, nextSortOrder],
  );

  return {
    id: Number((result as { insertId: number }).insertId),
    name: normalizedName,
    systemName,
  };
}

async function syncRemoteFolderStateToDatabase(
  connection: PoolConnection,
  mailboxId: number,
  folderState: Awaited<ReturnType<typeof syncRemoteMailbox>>["folders"],
) {
  await ensureLocalMailboxFolders(connection, mailboxId);
  const folderRows = await getFolderRowsByMailboxId(connection, mailboxId);
  const folderIdBySystemName = new Map(
    folderRows.map((row) => [row.system_name, row.id]),
  );

  for (const folder of folderState) {
    const folderId = folderIdBySystemName.get(folder.systemName);

    if (!folderId) {
      continue;
    }

    await connection.query(
      `
        UPDATE mailbox_folders
        SET
          remote_name = ?,
          remote_total = ?,
          remote_unseen = ?,
          last_synced_at = NOW(),
          updated_at = NOW()
        WHERE id = ?
          AND (
            NOT (remote_name <=> ?)
            OR remote_total <> ?
            OR remote_unseen <> ?
          )
      `,
      [
        folder.remoteName,
        folder.totalCount,
        folder.unreadCount,
        folderId,
        folder.remoteName,
        folder.totalCount,
        folder.unreadCount,
      ],
    );
  }

  return folderIdBySystemName;
}

async function upsertMailboxMessageRow(
  connection: PoolConnection,
  input: {
    bodyHtml: string | null;
    bodyText: string;
    direction: "inbound" | "outbound" | "draft";
    folderId: number;
    fromAddress: string;
    fromName: string | null;
    isRead: boolean;
    isStarred: boolean;
    mailboxId: number;
    messageIdHeader: string | null;
    preserveExistingTransport: boolean;
    rawSource: string | null;
    receivedAt: Date;
    remoteFlags: string | null;
    remoteFolder: string | null;
    remoteUid: number | null;
    snippet: string;
    subject: string;
    toAddresses: string;
    transportResponse: string | null;
    transportStatus: "saved" | "queued" | "sent" | "failed";
  },
) {
  if (input.remoteFolder !== null && input.remoteUid !== null) {
    const [existingRows] = await connection.query<
      (RowDataPacket & {
        body_html: string | null;
        body_text: string;
        direction: "inbound" | "outbound" | "draft";
        folder_id: number;
        from_address: string;
        from_name: string | null;
        id: number;
        is_read: number;
        is_starred: number;
        message_id_header: string | null;
        raw_source: string | null;
        received_at: Date | string;
        remote_flags: string | null;
        remote_folder: string | null;
        remote_internal_date: Date | string | null;
        remote_uid: number | null;
        snippet: string;
        subject: string;
        to_addresses: string;
        transport_response: string | null;
        transport_status: "saved" | "queued" | "sent" | "failed";
      })[]
    >(
      `
        SELECT
          id,
          folder_id,
          remote_uid,
          remote_folder,
          direction,
          subject,
          from_name,
          from_address,
          to_addresses,
          snippet,
          body_text,
          body_html,
          message_id_header,
          raw_source,
          transport_status,
          transport_response,
          remote_flags,
          is_read,
          is_starred,
          received_at,
          remote_internal_date
        FROM mailbox_messages
        WHERE mailbox_id = ?
          AND remote_folder = ?
          AND remote_uid = ?
        LIMIT 1
      `,
      [input.mailboxId, input.remoteFolder, input.remoteUid],
    );
    const existingId = existingRows[0]?.id;

    if (existingId) {
      const existingRow = existingRows[0];
      const nextTransportStatus = input.preserveExistingTransport
        ? existingRow.transport_status
        : input.transportStatus;
      const nextTransportResponse = input.preserveExistingTransport
        ? existingRow.transport_response ?? input.transportResponse
        : input.transportResponse;
      const hasChanges =
        Number(existingRow.folder_id) !== input.folderId ||
        normalizeNullableNumber(existingRow.remote_uid) !== input.remoteUid ||
        normalizeNullableText(existingRow.remote_folder) !== input.remoteFolder ||
        existingRow.direction !== input.direction ||
        existingRow.subject !== input.subject ||
        normalizeNullableText(existingRow.from_name) !== input.fromName ||
        existingRow.from_address !== input.fromAddress ||
        existingRow.to_addresses !== input.toAddresses ||
        existingRow.snippet !== input.snippet ||
        existingRow.body_text !== input.bodyText ||
        normalizeNullableText(existingRow.body_html) !== input.bodyHtml ||
        normalizeNullableText(existingRow.message_id_header) !== input.messageIdHeader ||
        normalizeNullableText(existingRow.raw_source) !== input.rawSource ||
        existingRow.transport_status !== nextTransportStatus ||
        normalizeNullableText(existingRow.transport_response) !== nextTransportResponse ||
        normalizeNullableText(existingRow.remote_flags) !== input.remoteFlags ||
        Number(existingRow.is_read) !== (input.isRead ? 1 : 0) ||
        Number(existingRow.is_starred) !== (input.isStarred ? 1 : 0) ||
        normalizeNullableDateTime(existingRow.received_at) !==
          normalizeNullableDateTime(input.receivedAt) ||
        normalizeNullableDateTime(existingRow.remote_internal_date) !==
          normalizeNullableDateTime(input.receivedAt);

      if (!hasChanges) {
        return Number(existingId);
      }

      const transportUpdate = input.preserveExistingTransport
        ? `
            transport_response = COALESCE(transport_response, ?),
        `
        : `
            transport_status = ?,
            transport_response = ?,
        `;
      const transportParams = input.preserveExistingTransport
        ? [input.transportResponse]
        : [input.transportStatus, input.transportResponse];

      await connection.query(
        `
          UPDATE mailbox_messages
          SET
            folder_id = ?,
            remote_uid = ?,
            remote_folder = ?,
            direction = ?,
            subject = ?,
            from_name = ?,
            from_address = ?,
            to_addresses = ?,
            snippet = ?,
            body_text = ?,
            body_html = ?,
            message_id_header = ?,
            raw_source = ?,
            ${transportUpdate}
            remote_flags = ?,
            is_read = ?,
            is_starred = ?,
            received_at = ?,
            remote_internal_date = ?,
            synced_at = NOW()
          WHERE id = ?
        `,
        [
          input.folderId,
          input.remoteUid,
          input.remoteFolder,
          input.direction,
          input.subject,
          input.fromName,
          input.fromAddress,
          input.toAddresses,
          input.snippet,
          input.bodyText,
          input.bodyHtml,
          input.messageIdHeader,
          input.rawSource,
          ...transportParams,
          input.remoteFlags,
          input.isRead ? 1 : 0,
          input.isStarred ? 1 : 0,
          input.receivedAt,
          input.receivedAt,
          existingId,
        ],
      );

      return Number(existingId);
    }
  }

  try {
    const [result] = await connection.query(
      `
        INSERT INTO mailbox_messages (
          mailbox_id,
          folder_id,
          remote_uid,
          remote_folder,
          direction,
          subject,
          from_name,
          from_address,
          to_addresses,
          snippet,
          body_text,
          body_html,
          message_id_header,
          raw_source,
          transport_status,
          transport_response,
          remote_flags,
          is_read,
          is_starred,
          received_at,
          remote_internal_date,
          synced_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
      `,
      [
        input.mailboxId,
        input.folderId,
        input.remoteUid,
        input.remoteFolder,
        input.direction,
        input.subject,
        input.fromName,
        input.fromAddress,
        input.toAddresses,
        input.snippet,
        input.bodyText,
        input.bodyHtml,
        input.messageIdHeader,
        input.rawSource,
        input.transportStatus,
        input.transportResponse,
        input.remoteFlags,
        input.isRead ? 1 : 0,
        input.isStarred ? 1 : 0,
        input.receivedAt,
        input.receivedAt,
      ],
    );

    return Number((result as { insertId: number }).insertId);
  } catch (error) {
    if (
      input.remoteFolder !== null &&
      input.remoteUid !== null &&
      typeof error === "object" &&
      error !== null &&
      "code" in error &&
      error.code === "ER_DUP_ENTRY"
    ) {
      return upsertMailboxMessageRow(connection, input);
    }

    throw error;
  }
}

async function permanentlyDeleteMailboxRows(
  connection: PoolConnection,
  mailbox: MailboxRow,
  rows: Array<
    Pick<
      MailboxMoveMessageRow,
      "folder_id" | "id" | "remote_folder" | "remote_uid"
    >
  >,
) {
  if (rows.length === 0) {
    return {
      cleanupTargets: [] as ComposeUploadCleanupTarget[],
      deletedCount: 0,
      deletedMessageIds: [] as number[],
      invalidatedFolderIds: [] as number[],
    } satisfies MailboxMessageDeleteResult;
  }

  const invalidatedFolderIds = new Set<number>();
  const credentials = mailbox.password_ciphertext
    ? toMailboxCredentials(mailbox)
    : null;

  if (credentials) {
    const remoteFolderMap = new Map<string, number[]>();

    for (const row of rows) {
      if (!row.remote_folder || !row.remote_uid) {
        continue;
      }

      invalidatedFolderIds.add(row.folder_id);
      const bucket = remoteFolderMap.get(row.remote_folder) ?? [];
      bucket.push(row.remote_uid);
      remoteFolderMap.set(row.remote_folder, bucket);
    }

    for (const [remoteFolder, remoteUids] of remoteFolderMap) {
      const deleted = await deleteRemoteMessages(
        credentials,
        remoteFolder,
        remoteUids,
      );

      if (!deleted) {
        throw new Error("mailbox-delete-failed");
      }
    }
  }

  const messageIds = rows.map((row) => row.id);
  const cleanupTargets = await markComposeUploadsDeletedByMessageIds(
    connection,
    messageIds,
  );

  await connection.query(
    `
      DELETE FROM mailbox_delivery_logs
      WHERE mailbox_message_id IN (${messageIds.map(() => "?").join(", ")})
    `,
    messageIds,
  );

  await connection.query(
    `
      DELETE FROM mailbox_messages
      WHERE mailbox_id = ?
        AND id IN (${messageIds.map(() => "?").join(", ")})
    `,
    [mailbox.id, ...messageIds],
  );

  rows.forEach((row) => invalidatedFolderIds.add(row.folder_id));

  return {
    cleanupTargets,
    deletedCount: messageIds.length,
    deletedMessageIds: messageIds,
    invalidatedFolderIds: [...invalidatedFolderIds],
  } satisfies MailboxMessageDeleteResult;
}

async function replaceCurrentFolderMessages(
  connection: PoolConnection,
  mailboxId: number,
  folderId: number,
  remoteFolder: string,
  messages: RemoteMailboxMessage[],
) {
  const remoteUids = messages.map((message) => message.remoteUid);
  const preserveLocalOutboundSql =
    "remote_uid IS NULL AND direction = 'outbound' AND transport_status IN ('queued', 'sent', 'failed')";
  let deletedAssetTargets: ComposeUploadCleanupTarget[] = [];

  if (remoteUids.length > 0) {
    const placeholders = remoteUids.map(() => "?").join(", ");

    const [rowsToDelete] = await connection.query<
      (RowDataPacket & { id: number })[]
    >(
      `
        SELECT id
        FROM mailbox_messages
        WHERE mailbox_id = ?
          AND folder_id = ?
          AND remote_folder = ?
          AND (
            remote_uid NOT IN (${placeholders})
            OR (remote_uid IS NULL AND NOT (${preserveLocalOutboundSql}))
          )
      `,
      [mailboxId, folderId, remoteFolder, ...remoteUids],
    );

    deletedAssetTargets = await markComposeUploadsDeletedByMessageIds(
      connection,
      rowsToDelete.map((row) => row.id),
    );

    await connection.query(
      `
        DELETE FROM mailbox_messages
        WHERE mailbox_id = ?
          AND folder_id = ?
          AND remote_folder = ?
          AND (
            remote_uid NOT IN (${placeholders})
            OR (remote_uid IS NULL AND NOT (${preserveLocalOutboundSql}))
          )
      `,
      [mailboxId, folderId, remoteFolder, ...remoteUids],
    );
  } else {
    const [rowsToDelete] = await connection.query<
      (RowDataPacket & { id: number })[]
    >(
      `
        SELECT id
        FROM mailbox_messages
        WHERE mailbox_id = ?
          AND folder_id = ?
          AND remote_folder = ?
          AND NOT (${preserveLocalOutboundSql})
      `,
      [mailboxId, folderId, remoteFolder],
    );

    deletedAssetTargets = await markComposeUploadsDeletedByMessageIds(
      connection,
      rowsToDelete.map((row) => row.id),
    );

    await connection.query(
      `
        DELETE FROM mailbox_messages
        WHERE mailbox_id = ?
          AND folder_id = ?
          AND remote_folder = ?
          AND NOT (${preserveLocalOutboundSql})
      `,
      [mailboxId, folderId, remoteFolder],
    );
  }

  for (const message of messages) {
    await upsertMailboxMessageRow(connection, {
      bodyHtml: message.bodyHtml,
      bodyText: message.bodyText,
      direction: message.direction,
      folderId,
      fromAddress: message.fromAddress,
      fromName: message.fromName,
      isRead: message.isRead,
      isStarred: hasRemoteFlag(message.remoteFlags, "\\Flagged"),
      mailboxId,
      messageIdHeader: message.messageIdHeader,
      preserveExistingTransport: true,
      rawSource: message.rawSource,
      receivedAt: message.receivedAt,
      remoteFlags: normalizeRemoteFlags(message.remoteFlags),
      remoteFolder: message.remoteFolder,
      remoteUid: message.remoteUid,
      snippet: message.snippet,
      subject: message.subject,
      toAddresses: message.toAddresses,
      transportResponse: normalizeRemoteTransportResponse(message),
      transportStatus: message.transportStatus,
    });
  }

  return deletedAssetTargets;
}

async function syncMailboxCacheByEmail(email: string, currentFolder?: string) {
  const user = await getUserByEmail(email);

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

  const mailbox = await getLatestMailboxByUserId(user.id);

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

  await withMailboxSyncQueue(mailbox.id, () =>
    syncMailboxCacheForMailbox(user, mailbox, currentFolder),
  );
}

async function syncMailboxCacheForMailbox(
  user: UserRow,
  mailbox: MailboxRow,
  currentFolder?: string,
) {
  const folderSystemName = normalizeFolderSystemName(currentFolder);
  await ensureOfficialMailSchema();
  const [folderRows] = await getDbPool().query<
    (RowDataPacket & {
      id: number;
      system_name: string;
      remote_name: string | null;
      name: string;
    })[]
  >(
    `
      SELECT id, system_name, remote_name, name
      FROM mailbox_folders
      WHERE mailbox_id = ?
    `,
    [mailbox.id],
  );
  const requestedFolderRow =
    folderRows.find((row) => row.system_name === folderSystemName) ??
    folderRows.find((row) => row.system_name === "inbox") ??
    null;

  try {
    const credentials = toMailboxCredentials(mailbox);
    const remoteSync = await syncRemoteMailbox(
      credentials,
      isSystemMailboxFolderSystemName(requestedFolderRow?.system_name)
        ? requestedFolderRow.system_name
        : requestedFolderRow
          ? {
              name: requestedFolderRow.name,
              remoteName:
                requestedFolderRow.remote_name || requestedFolderRow.name,
            }
          : "inbox",
    );
    const visibleCurrentFolderMessages = filterVisibleRemoteMailboxMessages(
      remoteSync.currentFolderMessages,
      mailbox.message_visibility_cutoff_at,
    );
    const deletedAssetTargets = await retryMailboxSyncWrite(() =>
      withTransaction(async (connection) => {
        const attemptDeletedAssetTargets: ComposeUploadCleanupTarget[] = [];

        await ensureLocalMailboxFolders(connection, mailbox.id);
        const syncedFolderRows = await getFolderRowsByMailboxId(
          connection,
          mailbox.id,
        );
        const activeFolderRow =
          syncedFolderRows.find(
            (row) =>
              row.system_name === (requestedFolderRow?.system_name ?? "inbox"),
          ) ??
          syncedFolderRows.find((row) => row.system_name === "inbox") ??
          null;
        const folderIdBySystemName = await syncRemoteFolderStateToDatabase(
          connection,
          mailbox.id,
          remoteSync.folders,
        );
        const syncedFolder = remoteSync.currentFolderState;

        if (syncedFolder && activeFolderRow) {
          const folderId =
            folderIdBySystemName.get(activeFolderRow.system_name) ??
            (activeFolderRow.system_name === requestedFolderRow?.system_name
              ? activeFolderRow.id
              : undefined);

          if (folderId) {
            attemptDeletedAssetTargets.push(
              ...(await replaceCurrentFolderMessages(
                connection,
                mailbox.id,
                folderId,
                syncedFolder.remoteName,
                visibleCurrentFolderMessages,
              )),
            );

            if (activeFolderRow.system_name === "inbox") {
              const moveFolderRows = await getMailboxFolderRows(
                connection,
                mailbox.id,
              );
              await applyMailboxSenderSpamRulesToInbox(
                connection,
                mailbox,
                moveFolderRows,
              );
            }

            await connection.query(
              `
                UPDATE mailbox_folders
                SET
                  remote_name = ?,
                  remote_total = ?,
                  remote_unseen = ?,
                  last_synced_at = NOW(),
                  updated_at = NOW()
                WHERE id = ?
                  AND (
                    NOT (remote_name <=> ?)
                    OR remote_total <> ?
                    OR remote_unseen <> ?
                  )
              `,
              [
                syncedFolder.remoteName,
                syncedFolder.totalCount,
                syncedFolder.unreadCount,
                folderId,
                syncedFolder.remoteName,
                syncedFolder.totalCount,
                syncedFolder.unreadCount,
              ],
            );
          }
        }

        return attemptDeletedAssetTargets;
      }),
    );
    await cleanupComposeUploadArtifacts(getDbPool(), deletedAssetTargets).catch(
      () => undefined,
    );

    await retryMailboxSyncWrite(() =>
      updateMailboxSyncState(mailbox.id, {
        lastSyncAt: new Date(),
        lastSyncError: null,
      }),
    );

    const syncedFolderKey = requestedFolderRow?.system_name ?? "inbox";

    await triggerMailboxPostSyncByOwnerEmail(user.email, syncedFolderKey);
  } catch (error) {
    const message =
      error instanceof Error ? error.message : "Mailbox sync failed";

    await retryMailboxSyncWrite(() =>
      updateMailboxSyncState(mailbox.id, {
        lastSyncAt: mailbox.last_sync_at,
        lastSyncError: message.slice(0, 1000),
      }),
    ).catch(() => undefined);

    throw error;
  }
}

async function setMailboxMessageReadState(
  email: string,
  messageId: number | null,
  isRead: boolean,
) {
  if (!messageId) {
    return false;
  }

  const user = await getUserByEmail(email);

  if (!user) {
    return false;
  }

  const mailbox = await getLatestMailboxByUserId(user.id);

  if (!mailbox) {
    return false;
  }

  const [rows] = await getDbPool().query<
    (RowDataPacket & {
      id: number;
      is_read: number;
      remote_flags: string | null;
      remote_folder: string | null;
      remote_uid: number | null;
    })[]
  >(
    `
      SELECT id, is_read, remote_flags, remote_folder, remote_uid
      FROM mailbox_messages
      WHERE id = ? AND mailbox_id = ?
      LIMIT 1
    `,
    [messageId, mailbox.id],
  );

  const message = rows[0];

  if (!message) {
    return false;
  }

  if (Boolean(message.is_read) === isRead) {
    return false;
  }

  const nextRemoteFlags = applyRemoteFlagString(
    message.remote_flags,
    "\\Seen",
    isRead,
  );

  await getDbPool().query(
    `
      UPDATE mailbox_messages
      SET is_read = ?, remote_flags = ?, updated_at = NOW()
      WHERE id = ?
    `,
    [isRead ? 1 : 0, nextRemoteFlags, message.id],
  );

  await updateMailboxSyncState(mailbox.id, {
    lastSyncAt: new Date(),
    lastSyncError: mailbox.last_sync_error,
  });

  if (
    mailbox.password_ciphertext &&
    message.remote_folder &&
    message.remote_uid
  ) {
    const credentials = toMailboxCredentials(mailbox);
    const syncErrorPrefix = isRead
      ? "mailbox-read-sync-failed"
      : "mailbox-unread-sync-failed";

    after(async () => {
      try {
        const updated = await updateRemoteMessageFlag(
          credentials,
          message.remote_folder!,
          message.remote_uid!,
          "\\Seen",
          isRead,
        );

        if (!updated) {
          await updateMailboxSyncState(mailbox.id, {
            lastSyncAt: mailbox.last_sync_at,
            lastSyncError: `${syncErrorPrefix}:${message.id}`.slice(0, 1000),
          });
          return;
        }

        if (mailbox.last_sync_error?.startsWith(syncErrorPrefix)) {
          await updateMailboxSyncState(mailbox.id, {
            lastSyncAt: mailbox.last_sync_at,
            lastSyncError: null,
          });
        }
      } catch (error) {
        const detail =
          error instanceof Error
            ? `${syncErrorPrefix}:${error.message}`
            : syncErrorPrefix;

        console.error("Mailbox read state sync failed", {
          email,
          isRead,
          messageId: message.id,
          error,
        });

        await updateMailboxSyncState(mailbox.id, {
          lastSyncAt: mailbox.last_sync_at,
          lastSyncError: detail.slice(0, 1000),
        });
      }
    });
  }

  return true;
}

async function markMailboxMessageAsRead(
  email: string,
  messageId: number | null,
) {
  return setMailboxMessageReadState(email, messageId, true);
}

async function getMailboxMessageStarContext(
  email: string,
  messageId: number | null,
) {
  if (!messageId) {
    throw new Error("message-not-found");
  }

  const user = await getUserByEmail(email);

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

  const mailbox = await getLatestMailboxByUserId(user.id);

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

  const [rows] = await getDbPool().query<
    (RowDataPacket & {
      id: number;
      is_starred: number;
      remote_flags: string | null;
      remote_folder: string | null;
      remote_uid: number | null;
    })[]
  >(
    `
      SELECT id, is_starred, remote_flags, remote_folder, remote_uid
      FROM mailbox_messages
      WHERE id = ? AND mailbox_id = ?
      LIMIT 1
    `,
    [messageId, mailbox.id],
  );

  const message = rows[0];

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

  return { mailbox, message };
}

async function persistMailboxMessageStarState(input: {
  mailbox: MailboxRow;
  message: {
    id: number;
    remote_flags: string | null;
    remote_folder: string | null;
    remote_uid: number | null;
  };
  nextStarred: boolean;
}) {
  const { mailbox, message, nextStarred } = input;

  if (
    mailbox.password_ciphertext &&
    message.remote_folder &&
    message.remote_uid
  ) {
    const updated = await updateRemoteMessageFlag(
      toMailboxCredentials(mailbox),
      message.remote_folder,
      message.remote_uid,
      "\\Flagged",
      nextStarred,
    );

    if (!updated) {
      throw new Error("mailbox-flag-failed");
    }
  }

  await getDbPool().query(
    `
      UPDATE mailbox_messages
      SET
        is_starred = ?,
        remote_flags = ?,
        updated_at = NOW()
      WHERE id = ?
    `,
    [
      nextStarred ? 1 : 0,
      applyRemoteFlagString(message.remote_flags, "\\Flagged", nextStarred),
      message.id,
    ],
  );

  return nextStarred;
}

export async function setMailboxMessageStarByEmail(
  email: string,
  messageId: number | null,
  nextStarred: boolean,
) {
  const { mailbox, message } = await getMailboxMessageStarContext(
    email,
    messageId,
  );

  if (Boolean(message.is_starred) === nextStarred) {
    return nextStarred;
  }

  return persistMailboxMessageStarState({ mailbox, message, nextStarred });
}

export async function toggleMailboxMessageStarByEmail(
  email: string,
  messageId: number | null,
) {
  const { mailbox, message } = await getMailboxMessageStarContext(
    email,
    messageId,
  );
  const nextStarred = !Boolean(message.is_starred);
  return persistMailboxMessageStarState({ mailbox, message, nextStarred });
}

export async function markMailboxMessagesAsReadByEmail(
  email: string,
  messageIds: Array<number | string>,
  isRead = true,
) {
  const normalizedIds = normalizeMessageIds(messageIds);

  if (normalizedIds.length === 0) {
    return 0;
  }

  let updatedCount = 0;

  for (const messageId of normalizedIds) {
    if (await setMailboxMessageReadState(email, messageId, isRead)) {
      updatedCount += 1;
    }
  }

  return updatedCount;
}

async function getMailboxFolderRows(
  connection: PoolConnection,
  mailboxId: number,
) {
  const [folderRows] = await connection.query<MailboxFolderRowBasic[]>(
    `
      SELECT id, system_name, remote_name, name
      FROM mailbox_folders
      WHERE mailbox_id = ?
    `,
    [mailboxId],
  );

  return folderRows;
}

async function getMailboxSpamRuleAddresses(
  connection: PoolConnection,
  mailboxId: number,
) {
  const [rows] = await connection.query<MailboxSenderRuleRow[]>(
    `
      SELECT id, mailbox_id, sender_address, rule_action
      FROM mailbox_sender_rules
      WHERE mailbox_id = ?
        AND rule_action = 'spam'
    `,
    [mailboxId],
  );

  return rows
    .map((row) => normalizeSenderAddress(row.sender_address))
    .filter(Boolean);
}

async function findInboundSenderRows(
  connection: PoolConnection,
  mailboxId: number,
  senderAddresses: string[],
  options?: {
    sourceFolder?: string;
  },
) {
  const normalizedSenderAddresses = [
    ...new Set(
      senderAddresses
        .map((senderAddress) => normalizeSenderAddress(senderAddress))
        .filter(Boolean),
    ),
  ];

  if (normalizedSenderAddresses.length === 0) {
    return [] as MailboxMoveMessageRow[];
  }

  if (options?.sourceFolder) {
    const [rows] = await connection.query<MailboxMoveMessageRow[]>(
      `
        SELECT
          mm.id,
          mm.folder_id,
          mm.direction,
          mm.from_address,
          mm.remote_folder,
          mm.remote_uid
        FROM mailbox_messages mm
        INNER JOIN mailbox_folders f ON f.id = mm.folder_id
        WHERE mm.mailbox_id = ?
          AND mm.direction = 'inbound'
          AND f.system_name = ?
          AND LOWER(mm.from_address) IN (${normalizedSenderAddresses.map(() => "?").join(", ")})
      `,
      [mailboxId, options.sourceFolder, ...normalizedSenderAddresses],
    );

    return rows;
  }

  const [rows] = await connection.query<MailboxMoveMessageRow[]>(
    `
      SELECT id, folder_id, direction, from_address, remote_folder, remote_uid
      FROM mailbox_messages
      WHERE mailbox_id = ?
        AND direction = 'inbound'
        AND LOWER(from_address) IN (${normalizedSenderAddresses.map(() => "?").join(", ")})
    `,
    [mailboxId, ...normalizedSenderAddresses],
  );

  return rows;
}

async function moveMailboxRowsToFolder(
  connection: PoolConnection,
  mailbox: MailboxRow,
  folderRows: MailboxFolderRowBasic[],
  rowsToMove: MailboxMoveMessageRow[],
  targetFolder: string,
) {
  const targetFolderRow = folderRows.find(
    (row) => row.system_name === targetFolder,
  );

  if (!targetFolderRow) {
    throw new Error("folder-not-found");
  }

  if (
    targetFolder === "sent" &&
    rowsToMove.some((message) => message.direction !== "outbound")
  ) {
    throw new Error("sent-folder-only-outbound");
  }

  const destinationRemoteFolder =
    targetFolderRow.remote_name ||
    (isSystemMailboxFolderSystemName(targetFolder)
      ? remoteFolderFallbackName(targetFolder)
      : targetFolderRow.name);
  const credentials = mailbox.password_ciphertext
    ? toMailboxCredentials(mailbox)
    : null;
  const invalidatedFolderIds = new Set<number>();
  const movedMessageIds: number[] = [];

  const mergeRemoteDuplicateRows = async (input: {
    keepMessageId: number;
    remoteFolder: string;
    remoteUid: number;
  }) => {
    const [duplicates] = await connection.query<MailboxRemoteDuplicateRow[]>(
      `
        SELECT id, folder_id
        FROM mailbox_messages
        WHERE mailbox_id = ?
          AND remote_folder = ?
          AND remote_uid = ?
          AND id <> ?
      `,
      [mailbox.id, input.remoteFolder, input.remoteUid, input.keepMessageId],
    );

    if (duplicates.length === 0) {
      return;
    }

    const duplicateIds = duplicates.map((row) => row.id);

    await connection.query(
      `
        UPDATE mailbox_delivery_logs
        SET mailbox_message_id = ?
        WHERE mailbox_message_id IN (${duplicateIds.map(() => "?").join(", ")})
      `,
      [input.keepMessageId, ...duplicateIds],
    );

    await connection.query(
      `
        UPDATE mailbox_ai_assist_threads
        SET source_message_id = ?
        WHERE source_message_id IN (${duplicateIds.map(() => "?").join(", ")})
          AND NOT EXISTS (
            SELECT 1
            FROM (
              SELECT source_message_id
              FROM mailbox_ai_assist_threads
              WHERE source_message_id = ?
            ) existing_thread
          )
      `,
      [input.keepMessageId, ...duplicateIds, input.keepMessageId],
    );

    await relinkComposeUploadsToMessageId(
      connection,
      duplicateIds,
      input.keepMessageId,
    );

    duplicates.forEach((row) => invalidatedFolderIds.add(row.folder_id));

    await connection.query(
      `
        DELETE FROM mailbox_messages
        WHERE id IN (${duplicateIds.map(() => "?").join(", ")})
      `,
      duplicateIds,
    );
  };

  for (const message of rowsToMove) {
    if (message.folder_id === targetFolderRow.id) {
      continue;
    }

    invalidatedFolderIds.add(message.folder_id);
    invalidatedFolderIds.add(targetFolderRow.id);

    let nextRemoteFolder = destinationRemoteFolder;
    let nextRemoteUid = message.remote_uid;

    if (credentials && message.remote_folder && message.remote_uid) {
      const moved: RemoteMailboxMoveResult | false = await moveRemoteMessage(
        credentials,
        message.remote_folder,
        message.remote_uid,
        destinationRemoteFolder,
      );

      if (!moved) {
        throw new Error("mailbox-move-failed");
      }

      nextRemoteFolder = moved.destination || destinationRemoteFolder;
      nextRemoteUid = moved.remoteUid;
    }

    if (nextRemoteFolder && nextRemoteUid) {
      await mergeRemoteDuplicateRows({
        keepMessageId: message.id,
        remoteFolder: nextRemoteFolder,
        remoteUid: nextRemoteUid,
      });
    }

    await connection.query(
      `
        UPDATE mailbox_messages
        SET
          folder_id = ?,
          remote_folder = ?,
          remote_uid = ?,
          updated_at = NOW()
        WHERE id = ?
      `,
      [targetFolderRow.id, nextRemoteFolder, nextRemoteUid, message.id],
    );

    movedMessageIds.push(message.id);
  }

  if (invalidatedFolderIds.size > 0) {
    await connection.query(
      `
        UPDATE mailbox_folders
        SET last_synced_at = NULL, updated_at = NOW()
        WHERE mailbox_id = ?
          AND id IN (${[...invalidatedFolderIds].map(() => "?").join(", ")})
      `,
      [mailbox.id, ...invalidatedFolderIds],
    );
  }

  if (isCustomMailboxFolderSystemName(targetFolder)) {
    await connection.query(
      `
        UPDATE mailbox_folders
        SET remote_name = COALESCE(remote_name, ?), updated_at = NOW()
        WHERE id = ?
      `,
      [destinationRemoteFolder, targetFolderRow.id],
    );
  }

  return {
    deletedCount: 0,
    deletedMessageIds: [] as number[],
    movedCount: movedMessageIds.length,
    movedMessageIds,
    spamSenderCount:
      targetFolder === "spam"
        ? new Set(
            rowsToMove
              .map((message) => normalizeSenderAddress(message.from_address))
              .filter(Boolean),
          ).size
        : 0,
  };
}

async function applyMailboxSenderSpamRulesToInbox(
  connection: PoolConnection,
  mailbox: MailboxRow,
  folderRows: MailboxFolderRowBasic[],
) {
  const blockedSenders = await getMailboxSpamRuleAddresses(
    connection,
    mailbox.id,
  );

  if (blockedSenders.length === 0) {
    return 0;
  }

  const inboxRows = await findInboundSenderRows(
    connection,
    mailbox.id,
    blockedSenders,
    {
      sourceFolder: "inbox",
    },
  );

  if (inboxRows.length === 0) {
    return 0;
  }

  const moveResult = await moveMailboxRowsToFolder(
    connection,
    mailbox,
    folderRows,
    inboxRows,
    "spam",
  );

  return moveResult.movedCount;
}

export async function moveMailboxMessagesByEmail(
  email: string,
  messageIds: Array<number | string>,
  targetFolderInput: string,
) {
  const normalizedIds = normalizeMessageIds(messageIds);

  if (normalizedIds.length === 0) {
    return {
      deletedCount: 0,
      deletedMessageIds: [] as number[],
      movedCount: 0,
      movedMessageIds: [] as number[],
      spamSenderCount: 0,
    } satisfies MailboxMoveActionResult;
  }

  const targetFolder = normalizeTargetFolderSystemName(targetFolderInput);
  const user = await getUserByEmail(email);

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

  const mailbox = await getLatestMailboxByUserId(user.id);

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

  const deletedAssetTargets: ComposeUploadCleanupTarget[] = [];
  const moveResult = await withTransaction(async (connection) => {
    await ensureLocalMailboxFolders(connection, mailbox.id);
    const folderRows = await getMailboxFolderRows(connection, mailbox.id);
    const [messageRows] = await connection.query<MailboxMoveMessageRow[]>(
      `
        SELECT id, folder_id, direction, from_address, remote_folder, remote_uid
        FROM mailbox_messages
        WHERE mailbox_id = ?
          AND id IN (${normalizedIds.map(() => "?").join(", ")})
      `,
      [mailbox.id, ...normalizedIds],
    );

    if (messageRows.length === 0) {
      return {
        deletedCount: 0,
        deletedMessageIds: [] as number[],
        movedCount: 0,
        movedMessageIds: [] as number[],
        spamSenderCount: 0,
      } satisfies MailboxMoveActionResult;
    }

    let rowsToMove = messageRows;

    if (targetFolder === "spam") {
      const senderRows = await findInboundSenderRows(
        connection,
        mailbox.id,
        messageRows.map((message) => message.from_address),
      );

      if (senderRows.length > 0) {
        rowsToMove = senderRows;
      }
    }

    if (targetFolder === "trash") {
      const trashFolderRow =
        folderRows.find((row) => row.system_name === "trash") ?? null;
      const rowsToDelete = trashFolderRow
        ? rowsToMove.filter(
            (message) => message.folder_id === trashFolderRow.id,
          )
        : [];
      const rowsToTrash = trashFolderRow
        ? rowsToMove.filter(
            (message) => message.folder_id !== trashFolderRow.id,
          )
        : rowsToMove;
      const deleteResult = await permanentlyDeleteMailboxRows(
        connection,
        mailbox,
        rowsToDelete,
      );

      deletedAssetTargets.push(...deleteResult.cleanupTargets);

      if (deleteResult.invalidatedFolderIds.length > 0) {
        await connection.query(
          `
            UPDATE mailbox_folders
            SET last_synced_at = NULL, updated_at = NOW()
            WHERE mailbox_id = ?
              AND id IN (${deleteResult.invalidatedFolderIds.map(() => "?").join(", ")})
          `,
          [mailbox.id, ...deleteResult.invalidatedFolderIds],
        );
      }

      if (rowsToTrash.length === 0) {
        return {
          deletedCount: deleteResult.deletedCount,
          deletedMessageIds: deleteResult.deletedMessageIds,
          movedCount: 0,
          movedMessageIds: [] as number[],
          spamSenderCount: 0,
        } satisfies MailboxMoveActionResult;
      }

      const movedResult = await moveMailboxRowsToFolder(
        connection,
        mailbox,
        folderRows,
        rowsToTrash,
        targetFolder,
      );

      return {
        ...movedResult,
        deletedCount: deleteResult.deletedCount,
        deletedMessageIds: deleteResult.deletedMessageIds,
      } satisfies MailboxMoveActionResult;
    }

    return moveMailboxRowsToFolder(
      connection,
      mailbox,
      folderRows,
      rowsToMove,
      targetFolder,
    );
  });
  await cleanupComposeUploadArtifacts(getDbPool(), deletedAssetTargets).catch(
    () => undefined,
  );

  return moveResult;
}

export async function markMailboxFolderReadByEmail(
  email: string,
  folderSystemNameInput: string,
) {
  const folderSystemName = normalizeTargetFolderSystemName(
    folderSystemNameInput,
  );
  const user = await getUserByEmail(email);

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

  const mailbox = await getLatestMailboxByUserId(user.id);

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

  const [rows] = await getDbPool().query<(RowDataPacket & { id: number })[]>(
    `
      SELECT mm.id
      FROM mailbox_messages mm
      INNER JOIN mailbox_folders f ON f.id = mm.folder_id
      WHERE mm.mailbox_id = ?
        AND f.system_name = ?
        AND mm.is_read = 0
    `,
    [mailbox.id, folderSystemName],
  );

  return markMailboxMessagesAsReadByEmail(
    email,
    rows.map((row) => row.id),
    true,
  );
}

export async function clearMailboxFolderByEmail(
  email: string,
  folderSystemNameInput: string,
) {
  const folderSystemName = normalizeTargetFolderSystemName(
    folderSystemNameInput,
  );

  if (folderSystemName === "inbox" || folderSystemName === "sent") {
    throw new Error("folder-clear-not-allowed");
  }

  const user = await getUserByEmail(email);

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

  const mailbox = await getLatestMailboxByUserId(user.id);

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

  let deletedAssetTargets: ComposeUploadCleanupTarget[] = [];
  const deletedCount = await withTransaction(async (connection) => {
    await ensureLocalMailboxFolders(connection, mailbox.id);
    const [folderRows] = await connection.query<
      (RowDataPacket & {
        id: number;
        name: string;
        remote_name: string | null;
        system_name: string;
      })[]
    >(
      `
        SELECT id, name, remote_name, system_name
        FROM mailbox_folders
        WHERE mailbox_id = ?
          AND system_name = ?
        LIMIT 1
      `,
      [mailbox.id, folderSystemName],
    );

    const folderRow = folderRows[0];

    if (!folderRow) {
      throw new Error("folder-not-found");
    }

    const [messageRows] = await connection.query<MailboxFolderMessageRow[]>(
      `
        SELECT
          id,
          folder_id,
          remote_uid,
          remote_folder,
          direction,
          subject,
          from_name,
          from_address,
          to_addresses,
          snippet,
          body_text,
          body_html,
          message_id_header,
          raw_source,
          transport_status,
          transport_response,
          remote_flags,
          is_read,
          is_starred,
          received_at
        FROM mailbox_messages
        WHERE mailbox_id = ?
          AND folder_id = ?
        ORDER BY id ASC
      `,
      [mailbox.id, folderRow.id],
    );

    if (messageRows.length === 0) {
      return 0;
    }
    const deleteResult = await permanentlyDeleteMailboxRows(
      connection,
      mailbox,
      messageRows,
    );
    deletedAssetTargets = deleteResult.cleanupTargets;

    await connection.query(
      `
        UPDATE mailbox_folders
        SET
          remote_total = 0,
          remote_unseen = 0,
          last_synced_at = NULL,
          updated_at = NOW()
        WHERE id = ?
      `,
      [folderRow.id],
    );

    return deleteResult.deletedCount;
  });
  await cleanupComposeUploadArtifacts(getDbPool(), deletedAssetTargets).catch(
    () => undefined,
  );

  await updateMailboxSyncState(mailbox.id, {
    lastSyncAt: new Date(),
    lastSyncError: null,
  });

  return deletedCount;
}

export async function backupMailboxFolderByEmail(
  email: string,
  folderSystemNameInput: string,
) {
  const folderSystemName = normalizeTargetFolderSystemName(
    folderSystemNameInput,
  );
  const user = await getUserByEmail(email);

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

  const mailbox = await getLatestMailboxByUserId(user.id);

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

  const result = await withTransaction(async (connection) => {
    await ensureLocalMailboxFolders(connection, mailbox.id);
    const [folderRows] = await connection.query<
      (RowDataPacket & {
        id: number;
        name: string;
        remote_name: string | null;
        system_name: string;
      })[]
    >(
      `
        SELECT id, name, remote_name, system_name
        FROM mailbox_folders
        WHERE mailbox_id = ?
          AND system_name = ?
        LIMIT 1
      `,
      [mailbox.id, folderSystemName],
    );

    const sourceFolder = folderRows[0];

    if (!sourceFolder) {
      throw new Error("folder-not-found");
    }

    const sourceLabel = folderDisplayNameForAction(
      sourceFolder.system_name,
      sourceFolder.name,
    );
    const backupFolder = await createCustomMailboxFolder(connection, mailbox, {
      name: formatFolderBackupName(sourceLabel),
    });
    const [messageRows] = await connection.query<MailboxFolderMessageRow[]>(
      `
        SELECT
          id,
          folder_id,
          remote_uid,
          remote_folder,
          direction,
          subject,
          from_name,
          from_address,
          to_addresses,
          snippet,
          body_text,
          body_html,
          message_id_header,
          raw_source,
          transport_status,
          transport_response,
          remote_flags,
          is_read,
          is_starred,
          received_at
        FROM mailbox_messages
        WHERE mailbox_id = ?
          AND folder_id = ?
        ORDER BY received_at ASC, id ASC
      `,
      [mailbox.id, sourceFolder.id],
    );

    if (messageRows.length === 0) {
      return {
        copiedCount: 0,
        folderName: backupFolder.name,
        folderSystemName: backupFolder.systemName,
      };
    }

    const credentials = mailbox.password_ciphertext
      ? toMailboxCredentials(mailbox)
      : null;
    let copiedCount = 0;

    for (const message of messageRows) {
      let remoteFolder = backupFolder.name;
      let remoteUid: number | null = null;

      if (credentials && message.raw_source) {
        const appendResult = await appendMessageToRemoteFolder(
          credentials,
          backupFolder.name,
          message.raw_source,
          remoteFlagList(message.remote_flags),
        );

        if (!appendResult) {
          throw new Error("mailbox-folder-backup-failed");
        }

        remoteFolder = appendResult.destination ?? backupFolder.name;
        remoteUid = appendResult.uid ?? null;
      }

      await upsertMailboxMessageRow(connection, {
        bodyHtml: message.body_html,
        bodyText: message.body_text,
        direction: message.direction,
        folderId: backupFolder.id,
        fromAddress: message.from_address,
        fromName: message.from_name,
        isRead: Boolean(message.is_read),
        isStarred: Boolean(message.is_starred),
        mailboxId: mailbox.id,
        messageIdHeader: message.message_id_header,
        preserveExistingTransport: false,
        rawSource: message.raw_source,
        receivedAt: message.received_at,
        remoteFlags: message.remote_flags,
        remoteFolder,
        remoteUid,
        snippet: message.snippet,
        subject: message.subject,
        toAddresses: message.to_addresses,
        transportResponse: message.transport_response,
        transportStatus: message.transport_status,
      });
      copiedCount += 1;
    }

    await connection.query(
      `
        UPDATE mailbox_folders
        SET last_synced_at = NULL, updated_at = NOW()
        WHERE id = ?
      `,
      [backupFolder.id],
    );

    return {
      copiedCount,
      folderName: backupFolder.name,
      folderSystemName: backupFolder.systemName,
    };
  });

  await updateMailboxSyncState(mailbox.id, {
    lastSyncAt: new Date(),
    lastSyncError: null,
  });

  return result;
}

export async function updateMailboxSenderSpamRuleByEmail(
  email: string,
  senderAddressInput: string,
  options: {
    enabled: boolean;
    moveExistingMessages?: boolean;
    targetFolder?: string;
  },
) {
  const senderAddress = normalizeSenderAddress(senderAddressInput);

  if (!senderAddress) {
    throw new Error("sender-address-required");
  }

  const user = await getUserByEmail(email);

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

  const mailbox = await getLatestMailboxByUserId(user.id);

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

  const targetFolder = options.targetFolder
    ? normalizeTargetFolderSystemName(options.targetFolder)
    : "inbox";

  const result = await withTransaction(async (connection) => {
    await ensureLocalMailboxFolders(connection, mailbox.id);
    const folderRows = await getMailboxFolderRows(connection, mailbox.id);

    if (options.enabled) {
      await connection.query(
        `
          INSERT INTO mailbox_sender_rules (mailbox_id, sender_address, rule_action)
          VALUES (?, ?, 'spam')
          ON DUPLICATE KEY UPDATE
            rule_action = VALUES(rule_action),
            updated_at = NOW()
        `,
        [mailbox.id, senderAddress],
      );

      const senderRows = await findInboundSenderRows(connection, mailbox.id, [
        senderAddress,
      ]);

      if (senderRows.length === 0) {
        return {
          movedCount: 0,
          senderAddress,
        };
      }

      const moveResult = await moveMailboxRowsToFolder(
        connection,
        mailbox,
        folderRows,
        senderRows,
        "spam",
      );

      return {
        movedCount: moveResult.movedCount,
        senderAddress,
      };
    }

    await connection.query(
      `
        DELETE FROM mailbox_sender_rules
        WHERE mailbox_id = ?
          AND sender_address = ?
      `,
      [mailbox.id, senderAddress],
    );

    if (!options.moveExistingMessages) {
      return {
        movedCount: 0,
        senderAddress,
      };
    }

    const senderRows = await findInboundSenderRows(
      connection,
      mailbox.id,
      [senderAddress],
      {
        sourceFolder: "spam",
      },
    );

    if (senderRows.length === 0) {
      return {
        movedCount: 0,
        senderAddress,
      };
    }

    const moveResult = await moveMailboxRowsToFolder(
      connection,
      mailbox,
      folderRows,
      senderRows,
      targetFolder,
    );

    return {
      movedCount: moveResult.movedCount,
      senderAddress,
    };
  });

  return result;
}

export async function createMailboxFolderByEmail(
  email: string,
  folderName: string,
  options?: {
    insertAfterSystemName?: string | null;
    insertAtTop?: boolean;
  },
) {
  const user = await getUserByEmail(email);

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

  const mailbox = await getLatestMailboxByUserId(user.id);

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

  return withTransaction(async (connection) =>
    createCustomMailboxFolder(connection, mailbox, {
      insertAfterSystemName: options?.insertAfterSystemName,
      insertAtTop: options?.insertAtTop,
      name: folderName,
    }),
  );
}

export async function renameMailboxFolderByEmail(
  email: string,
  folderSystemNameInput: string,
  nextFolderName: string,
) {
  const user = await getUserByEmail(email);

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

  const mailbox = await getLatestMailboxByUserId(user.id);

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

  const folderSystemName = normalizeTargetFolderSystemName(
    folderSystemNameInput,
  );
  const normalizedName = normalizeCustomFolderName(nextFolderName);

  if (!isCustomMailboxFolderSystemName(folderSystemName)) {
    throw new Error("custom-folder-only");
  }

  if (!normalizedName) {
    throw new Error("folder-name-required");
  }

  return withTransaction(async (connection) => {
    await ensureLocalMailboxFolders(connection, mailbox.id);
    const folderRows = await getMailboxFolderRows(connection, mailbox.id);
    const targetFolder = folderRows.find(
      (row) => row.system_name === folderSystemName,
    );

    if (!targetFolder) {
      throw new Error("folder-not-found");
    }

    const duplicateFolder = folderRows.find(
      (row) =>
        row.system_name !== folderSystemName &&
        row.name.trim().toLowerCase() === normalizedName.toLowerCase(),
    );

    if (duplicateFolder) {
      throw new Error("folder-name-exists");
    }

    const currentRemoteName = targetFolder.remote_name || targetFolder.name;
    const credentials = mailbox.password_ciphertext
      ? toMailboxCredentials(mailbox)
      : null;

    if (credentials && currentRemoteName !== normalizedName) {
      await renameRemoteMailboxFolder(
        credentials,
        currentRemoteName,
        normalizedName,
      );
    }

    await connection.query(
      `
        UPDATE mailbox_folders
        SET
          name = ?,
          remote_name = ?,
          last_synced_at = NULL,
          updated_at = NOW()
        WHERE mailbox_id = ? AND system_name = ?
      `,
      [normalizedName, normalizedName, mailbox.id, folderSystemName],
    );

    await connection.query(
      `
        UPDATE mailbox_messages
        SET
          remote_folder = ?,
          updated_at = NOW()
        WHERE mailbox_id = ?
          AND folder_id = ?
      `,
      [normalizedName, mailbox.id, targetFolder.id],
    );

    return {
      id: targetFolder.id,
      name: normalizedName,
      systemName: folderSystemName,
    };
  });
}

export async function deleteMailboxFolderByEmail(
  email: string,
  folderSystemNameInput: string,
) {
  const user = await getUserByEmail(email);

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

  const mailbox = await getLatestMailboxByUserId(user.id);

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

  const folderSystemName = normalizeTargetFolderSystemName(
    folderSystemNameInput,
  );

  if (!isCustomMailboxFolderSystemName(folderSystemName)) {
    throw new Error("custom-folder-only");
  }

  return withTransaction(async (connection) => {
    await ensureLocalMailboxFolders(connection, mailbox.id);
    const folderRows = await getMailboxFolderRows(connection, mailbox.id);
    const targetFolder = folderRows.find(
      (row) => row.system_name === folderSystemName,
    );

    if (!targetFolder) {
      throw new Error("folder-not-found");
    }

    const [folderMessages] = await connection.query<MailboxMoveMessageRow[]>(
      `
        SELECT
          id,
          folder_id,
          direction,
          from_address,
          remote_folder,
          remote_uid
        FROM mailbox_messages
        WHERE mailbox_id = ?
          AND folder_id = ?
      `,
      [mailbox.id, targetFolder.id],
    );

    const moveResult =
      folderMessages.length > 0
        ? await moveMailboxRowsToFolder(
            connection,
            mailbox,
            folderRows,
            folderMessages,
            "inbox",
          )
        : { movedCount: 0 };

    const credentials = mailbox.password_ciphertext
      ? toMailboxCredentials(mailbox)
      : null;
    const remoteFolderName = targetFolder.remote_name || targetFolder.name;

    if (credentials && remoteFolderName) {
      await deleteRemoteMailboxFolder(credentials, remoteFolderName);
    }

    await connection.query(
      `
        DELETE FROM mailbox_folders
        WHERE mailbox_id = ?
          AND system_name = ?
          AND system_name LIKE 'custom\\_\\_%' ESCAPE '\\\\'
      `,
      [mailbox.id, folderSystemName],
    );

    return {
      deletedFolderName: targetFolder.name,
      movedCount: moveResult.movedCount,
    };
  });
}

export async function getSessionSnapshotByEmail(email: string) {
  try {
    const user = await getUserByEmail(email);

    if (!user) {
      return null;
    }

    const setup = await getLatestSetupByUserId(user.id);
    return buildSessionSnapshot(user, setup);
  } catch {
    return null;
  }
}

async function getSessionSnapshotByUserId(userId: number) {
  try {
    const user = await getUserById(userId);

    if (!user) {
      return null;
    }

    const setup = await getLatestSetupByUserId(user.id);
    return buildSessionSnapshot(user, setup);
  } catch {
    return null;
  }
}

export async function getMailLayoutPreferencesByEmail(email: string) {
  const user = await getUserByEmail(email);

  if (!user) {
    return DEFAULT_MAIL_LAYOUT_PREFERENCES;
  }

  return buildMailLayoutPreferences(user);
}

export async function updateMailLayoutPreferencesByEmail(
  email: string,
  preferences: Partial<MailLayoutPreferences>,
) {
  await ensureOfficialMailSchema();
  const normalizedEmail = email.trim().toLowerCase();
  const user = await getUserByEmail(normalizedEmail);

  const normalizedPreferences = normalizeMailLayoutPreferences({
    sidebarWidth:
      preferences.sidebarWidth ??
      user?.mail_sidebar_width ??
      DEFAULT_MAIL_LAYOUT_PREFERENCES.sidebarWidth,
    listWidth:
      preferences.listWidth ??
      user?.mail_list_width ??
      DEFAULT_MAIL_LAYOUT_PREFERENCES.listWidth,
    listHeight:
      preferences.listHeight ??
      user?.mail_list_height ??
      DEFAULT_MAIL_LAYOUT_PREFERENCES.listHeight,
    viewMode:
      preferences.viewMode ??
      (user?.mail_view_mode as MailLayoutPreferences["viewMode"] | undefined) ??
      DEFAULT_MAIL_LAYOUT_PREFERENCES.viewMode,
    sortOrder:
      preferences.sortOrder ??
      (user?.mail_sort_order as
        | MailLayoutPreferences["sortOrder"]
        | undefined) ??
      DEFAULT_MAIL_LAYOUT_PREFERENCES.sortOrder,
  });

  await getDbPool().query(
    `
      UPDATE users
      SET
        mail_sidebar_width = ?,
        mail_list_width = ?,
        mail_list_height = ?,
        mail_view_mode = ?,
        mail_sort_order = ?,
        updated_at = NOW()
      WHERE email = ?
    `,
    [
      normalizedPreferences.sidebarWidth,
      normalizedPreferences.listWidth,
      normalizedPreferences.listHeight,
      normalizedPreferences.viewMode,
      normalizedPreferences.sortOrder,
      normalizedEmail,
    ],
  );

  return normalizedPreferences;
}

export async function updateMailboxFolderOrderByEmail(
  email: string,
  folderIds: number[],
) {
  await ensureOfficialMailSchema();
  const normalizedEmail = email.trim().toLowerCase();
  const user = await getUserByEmail(normalizedEmail);

  if (!user) {
    return [];
  }

  const mailbox = await getLatestMailboxByUserId(user.id);

  if (!mailbox) {
    return [];
  }

  const requestedIds = folderIds
    .map((value) => Number(value))
    .filter(
      (value, index, array) =>
        Number.isInteger(value) && value > 0 && array.indexOf(value) === index,
    );

  return withTransaction(async (connection) => {
    await ensureLocalMailboxFolders(connection, mailbox.id);

    const [folderRows] = await connection.query<
      (RowDataPacket & { id: number })[]
    >(
      `
        SELECT id
        FROM mailbox_folders
        WHERE mailbox_id = ?
        ORDER BY sort_order ASC, id ASC
      `,
      [mailbox.id],
    );

    const validIds = folderRows.map((row) => Number(row.id));
    const nextOrder = requestedIds.filter((id) => validIds.includes(id));

    for (const id of validIds) {
      if (!nextOrder.includes(id)) {
        nextOrder.push(id);
      }
    }

    for (const [index, id] of nextOrder.entries()) {
      await connection.query(
        `
          UPDATE mailbox_folders
          SET sort_order = ?
          WHERE mailbox_id = ? AND id = ?
        `,
        [index + 1, mailbox.id, id],
      );
    }

    return nextOrder;
  });
}

export async function loginOrCreateUser(input: {
  email: string;
  password: string;
}) {
  const email = input.email.trim().toLowerCase();
  const password = input.password.trim();

  if (!email || !password) {
    throw new Error("required");
  }

  const existingUser = await getUserByEmail(email);

  if (!existingUser) {
    throw new Error("invalid-password");
  }

  if (!verifyPassword(password, existingUser.password_hash)) {
    throw new Error("invalid-password");
  }

  const snapshot = await getSessionSnapshotByEmail(email);

  if (!snapshot) {
    throw new Error("user-load-failed");
  }

  return snapshot;
}

export async function registerUser(input: {
  email: string;
  companyName: string;
  password: string;
}) {
  const email = input.email.trim().toLowerCase();
  const companyName = input.companyName.trim();
  const password = input.password.trim();

  if (!email || !companyName || !password) {
    throw new Error("required");
  }

  const existingUser = await getUserByEmail(email);

  if (existingUser) {
    throw new Error("email-exists");
  }

  await withTransaction(async (connection) => {
    await connection.query(
      `
        INSERT INTO users (
          email,
          company_name,
          display_name,
          password_hash,
          mail_configured
        ) VALUES (?, ?, ?, ?, 0)
      `,
      [
        email,
        companyName,
        deriveDisplayName(companyName, email),
        hashPassword(password),
      ],
    );
  });

  const snapshot = await getSessionSnapshotByEmail(email);

  if (!snapshot) {
    throw new Error("user-load-failed");
  }

  return snapshot;
}

async function getCleanedDomainSignupRecord(domain: string) {
  const [rows] = await getDbPool().query<CleanedDomainSignupRow[]>(
    `
      SELECT
        d.id AS domain_id,
        d.domain,
        d.user_id AS owner_user_id,
        u.email AS owner_email,
        u.recovery_email AS owner_recovery_email,
        m.id AS mailbox_id,
        m.email AS mailbox_email
      FROM domains d
      INNER JOIN users u ON u.id = d.user_id
      INNER JOIN mailboxes m
        ON m.domain_id = d.id
       AND m.user_id = d.user_id
      WHERE d.domain = ?
        AND d.mailcow_cleanup_at IS NOT NULL
      ORDER BY m.id ASC
      LIMIT 1
    `,
    [domain],
  );

  return rows[0] ?? null;
}

async function restoreCleanedDomainMembers(pending: PendingSignupRegistration) {
  if (!pending.restoredFromCleanup) {
    return;
  }

  const [memberRows] = await getDbPool().query<RestorableManagedMemberRow[]>(
    `
      SELECT
        display_name,
        email,
        local_part,
        password_ciphertext
      FROM managed_team_mailboxes
      WHERE domain_id = ?
        AND status = 'active'
      ORDER BY created_at ASC, id ASC
    `,
    [pending.domainId],
  );

  for (const member of memberRows) {
    await ensureMailcowMailboxAccount({
      displayName: member.display_name,
      domain: pending.domain,
      localPart: member.local_part,
      mailboxPassword: decryptMailboxPassword(member.password_ciphertext),
    });
  }

  await withTransaction(async (connection) => {
    await connection.query(
      `
        UPDATE domains
        SET mailcow_cleanup_at = NULL, updated_at = NOW()
        WHERE id = ?
          AND mailcow_cleanup_at IS NOT NULL
      `,
      [pending.domainId],
    );
    await connection.query(
      `
        UPDATE mailboxes m
        INNER JOIN managed_team_mailboxes mtm
          ON LOWER(mtm.email) = LOWER(m.email)
        SET
          m.status = 'active',
          m.last_sync_error = NULL,
          m.updated_at = NOW()
        WHERE m.domain_id = ?
          AND mtm.domain_id = ?
          AND mtm.status = 'active'
      `,
      [pending.domainId, pending.domainId],
    );
    await connection.query(
      `
        UPDATE users u
        INNER JOIN mailboxes m ON m.user_id = u.id
        INNER JOIN managed_team_mailboxes mtm
          ON LOWER(mtm.email) = LOWER(m.email)
        SET u.mail_configured = 1, u.updated_at = NOW()
        WHERE m.domain_id = ?
          AND mtm.domain_id = ?
          AND mtm.status = 'active'
      `,
      [pending.domainId, pending.domainId],
    );
  });
}

export async function createPendingSignupRegistration(input: {
  companyName: string;
  domain: string;
  localPart: string;
  password: string;
  recoveryEmail: string;
  recoveryVerificationId: number;
  recoveryVerificationToken: string;
}) {
  const normalized = normalizeSignupRegistrationInput(input);
  const cleanedDomain = await getCleanedDomainSignupRecord(normalized.domain);
  const existingUser = await getUserByEmail(normalized.mailboxEmail);

  if (cleanedDomain && cleanedDomain.owner_email !== normalized.mailboxEmail) {
    throw new Error("domain-recovery-owner-mismatch");
  }

  if (existingUser && !cleanedDomain) {
    throw new Error("email-exists");
  }

  const pool = getDbPool();
  const [ownerRows] = await pool.query<
    (RowDataPacket & { user_id: number | null })[]
  >("SELECT user_id FROM domains WHERE domain = ? LIMIT 1", [
    normalized.domain,
  ]);

  if (ownerRows[0]?.user_id && !cleanedDomain) {
    throw new Error("domain-taken");
  }

  const displayName = deriveDisplayName(
    normalized.companyName,
    normalized.mailboxEmail,
  );
  let userId = 0;
  let domainId = 0;
  let mailboxId = 0;
  let restoredFromCleanup = false;

  await withTransaction(async (connection) => {
    const verification = await getSignupRecoveryEmailVerificationById(
      normalized.recoveryVerificationId,
      connection,
    );

    if (
      !verification ||
      verification.email !== normalized.recoveryEmail ||
      verification.consumed_at ||
      !verification.verified_at ||
      !verification.verification_token_hash
    ) {
      throw new Error("recovery-email-unverified");
    }

    if (verification.expires_at.getTime() <= Date.now()) {
      throw new Error("recovery-email-expired");
    }

    if (
      verification.verification_token_hash !==
      createSha256Hex(normalized.recoveryVerificationToken)
    ) {
      throw new Error("recovery-email-unverified");
    }

    const [lockedCleanedDomainRows] = await connection.query<CleanedDomainSignupRow[]>(
      `
        SELECT
          d.id AS domain_id,
          d.domain,
          d.user_id AS owner_user_id,
          u.email AS owner_email,
          u.recovery_email AS owner_recovery_email,
          m.id AS mailbox_id,
          m.email AS mailbox_email
        FROM domains d
        INNER JOIN users u ON u.id = d.user_id
        INNER JOIN mailboxes m
          ON m.domain_id = d.id
         AND m.user_id = d.user_id
        WHERE d.domain = ?
          AND d.mailcow_cleanup_at IS NOT NULL
        ORDER BY m.id ASC
        LIMIT 1
        FOR UPDATE
      `,
      [normalized.domain],
    );
    const lockedCleanedDomain = lockedCleanedDomainRows[0];

    if (lockedCleanedDomain) {
      if (lockedCleanedDomain.owner_email !== normalized.mailboxEmail) {
        throw new Error("domain-recovery-owner-mismatch");
      }

      if (lockedCleanedDomain.owner_recovery_email !== normalized.recoveryEmail) {
        throw new Error("domain-recovery-verification-required");
      }

      userId = lockedCleanedDomain.owner_user_id;
      domainId = lockedCleanedDomain.domain_id;
      mailboxId = lockedCleanedDomain.mailbox_id;
      restoredFromCleanup = true;

      await connection.query(
        `
          UPDATE users
          SET
            company_name = ?,
            display_name = ?,
            recovery_email = ?,
            recovery_email_verified_at = ?,
            password_hash = ?,
            mail_configured = 0,
            updated_at = NOW()
          WHERE id = ?
        `,
        [
          normalized.companyName,
          displayName,
          normalized.recoveryEmail,
          verification.verified_at,
          hashPassword(normalized.password),
          userId,
        ],
      );
      await connection.query(
        `
          UPDATE domains
          SET
            status = 'pending',
            verified_at = NULL,
            dkim_selector = ?,
            dkim_public_key = '',
            dkim_private_key = '',
            updated_at = NOW()
          WHERE id = ?
        `,
        [process.env.DKIM_SELECTOR?.trim() || "om1", domainId],
      );
      await connection.query(
        `
          UPDATE mailboxes
          SET
            local_part = ?,
            email = ?,
            password_ciphertext = ?,
            password_updated_at = NOW(),
            last_sync_at = NULL,
            last_sync_error = NULL,
            status = 'pending_dns',
            updated_at = NOW()
          WHERE id = ?
            AND user_id = ?
            AND domain_id = ?
        `,
        [
          normalized.localPart,
          normalized.mailboxEmail,
          encryptMailboxPassword(normalized.password),
          mailboxId,
          userId,
          domainId,
        ],
      );
      await syncDnsRecords(connection, domainId, buildPendingDnsTemplate());
      await ensureLocalMailboxFolders(connection, mailboxId);
    } else {
      const [duplicateUsers] = await connection.query<
        (RowDataPacket & { id: number })[]
      >("SELECT id FROM users WHERE email = ? LIMIT 1", [
        normalized.mailboxEmail,
      ]);

      if (duplicateUsers[0]?.id) {
        throw new Error("email-exists");
      }

      const [duplicateDomains] = await connection.query<
        (RowDataPacket & { user_id: number | null })[]
      >("SELECT user_id FROM domains WHERE domain = ? LIMIT 1", [
        normalized.domain,
      ]);

      if (duplicateDomains[0]?.user_id) {
        throw new Error("domain-taken");
      }

      const [userResult] = await connection.query(
      `
        INSERT INTO users (
          email,
          company_name,
          display_name,
          recovery_email,
          recovery_email_verified_at,
          password_hash,
          mail_configured
        ) VALUES (?, ?, ?, ?, ?, ?, 0)
      `,
      [
        normalized.mailboxEmail,
        normalized.companyName,
        displayName,
        normalized.recoveryEmail,
        verification.verified_at,
        hashPassword(normalized.password),
      ],
    );

      userId = Number((userResult as { insertId: number }).insertId);

      const [domainResult] = await connection.query(
      `
        INSERT INTO domains (
          user_id,
          domain,
          status,
          dkim_selector,
          dkim_public_key,
          dkim_private_key
        ) VALUES (?, ?, 'pending', ?, '', '')
      `,
      [userId, normalized.domain, process.env.DKIM_SELECTOR?.trim() || "om1"],
    );

      domainId = Number((domainResult as { insertId: number }).insertId);

      const [mailboxResult] = await connection.query(
      `
        INSERT INTO mailboxes (
          user_id,
          domain_id,
          local_part,
          email,
          password_ciphertext,
          password_updated_at,
          last_sync_at,
          last_sync_error,
          status
        ) VALUES (?, ?, ?, ?, ?, NOW(), NULL, NULL, 'pending_dns')
      `,
      [
        userId,
        domainId,
        normalized.localPart,
        normalized.mailboxEmail,
        encryptMailboxPassword(normalized.password),
      ],
    );

      mailboxId = Number((mailboxResult as { insertId: number }).insertId);

      await syncDnsRecords(
        connection,
        domainId,
        buildPendingDnsTemplate(),
      );
      await ensureLocalMailboxFolders(connection, mailboxId);
    }
    await connection.query(
      `
        UPDATE signup_recovery_email_verifications
        SET consumed_at = NOW(), updated_at = NOW()
        WHERE id = ?
      `,
      [verification.id],
    );
  });

  const snapshot = await getSessionSnapshotByEmail(normalized.mailboxEmail);

  if (!snapshot) {
    throw new Error("snapshot-load-failed");
  }

  return {
    companyName: normalized.companyName,
    displayName,
    domain: normalized.domain,
    domainId,
    localPart: normalized.localPart,
    mailboxEmail: normalized.mailboxEmail,
    mailboxId,
    mailboxPassword: normalized.password,
    restoredFromCleanup,
    snapshot,
    userId,
  } satisfies PendingSignupRegistration;
}

export async function finalizePendingSignupRegistration(
  pending: PendingSignupRegistration,
  options?: {
    onProgress?: (progress: SignupProvisioningProgress) => void | Promise<void>;
  },
) {
  const onProgress = options?.onProgress;

  await emitSignupProvisioningProgress(onProgress, {
    step: "domain",
    status: "running",
    title: "도메인 등록 중",
    description: `${pending.domain} 도메인을 메일 서버에 등록하고 있습니다.`,
  });
  await ensureMailcowDomain({
    allowExistingDomain: pending.restoredFromCleanup,
    domain: pending.domain,
    mailboxEmail: pending.mailboxEmail,
  });
  await emitSignupProvisioningProgress(onProgress, {
    step: "domain",
    status: "complete",
    title: "도메인 등록 완료",
    description: `${pending.domain} 도메인 등록이 완료되었습니다.`,
  });

  await emitSignupProvisioningProgress(onProgress, {
    step: "mailbox",
    status: "running",
    title: "대표 메일 생성 중",
    description: `${pending.mailboxEmail} 계정을 메일 서버에 생성하고 있습니다.`,
  });
  await ensureMailcowMailbox({
    displayName: pending.displayName,
    domain: pending.domain,
    localPart: pending.localPart,
    mailboxPassword: pending.mailboxPassword,
  });
  await emitSignupProvisioningProgress(onProgress, {
    step: "mailbox",
    status: "complete",
    title: "대표 메일 생성 완료",
    description: `${pending.mailboxEmail} 계정이 준비되었습니다.`,
  });

  await emitSignupProvisioningProgress(onProgress, {
    step: "dkim",
    status: "running",
    title: "DNS 보안값 준비 중",
    description: "DKIM 공개키와 연결용 DNS 값을 정리하고 있습니다.",
  });
  const dkimProvisioning = await ensureMailcowDkimRecord(pending.domain);
  await withTransaction(async (connection) => {
    await connection.query(
      `
        UPDATE domains
        SET
          dkim_selector = ?,
          dkim_public_key = ?,
          dkim_private_key = '',
          updated_at = NOW()
        WHERE id = ?
      `,
      [
        dkimProvisioning.dkimSelector,
        dkimProvisioning.dkimPublicKey,
        pending.domainId,
      ],
    );
    await syncDnsRecords(
      connection,
      pending.domainId,
      buildDnsTemplate(
        pending.domain,
        dkimProvisioning.dkimSelector,
        dkimProvisioning.dkimPublicKey,
      ),
    );
  });
  await restoreCleanedDomainMembers(pending);
  await emitSignupProvisioningProgress(onProgress, {
    step: "dkim",
    status: "complete",
    title: "DNS 보안값 준비 완료",
    description: "MX, SPF, DKIM, DMARC 표가 모두 준비되었습니다.",
  });

  await emitSignupProvisioningProgress(onProgress, {
    step: "welcome",
    status: "running",
    title: "초기 메일함 준비 중",
    description: "환영 메일과 기본 받은메일함을 마지막으로 정리하고 있습니다.",
  });
  try {
    const welcomeSent = await sendSystemWelcomeMail({
      companyName: pending.companyName,
      mailboxEmail: pending.mailboxEmail,
      mailboxId: pending.mailboxId,
    });

    if (welcomeSent) {
      await refreshMailboxByEmail(pending.mailboxEmail, { folder: "inbox" });
    }
  } catch (error) {
    console.error("System welcome mail send failed", error);
  }
  await emitSignupProvisioningProgress(onProgress, {
    step: "welcome",
    status: "complete",
    title: "초기 메일함 준비 완료",
    description: "가입 직후 확인할 수 있는 기본 메일함 상태까지 정리했습니다.",
  });

  const snapshot = await getSessionSnapshotByUserId(pending.userId);

  if (!snapshot) {
    throw new Error("snapshot-load-failed");
  }

  return snapshot;
}

export async function registerUserWithMailboxSetup(input: {
  companyName: string;
  domain: string;
  localPart: string;
  password: string;
  recoveryEmail: string;
  recoveryVerificationId: number;
  recoveryVerificationToken: string;
}) {
  const pending = await createPendingSignupRegistration(input);
  return finalizePendingSignupRegistration(pending);
}

export async function sendSignupRecoveryEmailVerification(input: {
  challengeId?: number | null;
  recoveryEmail: string;
}) {
  await ensureOfficialMailSchema();
  const recoveryEmail = normalizeEmailAddress(input.recoveryEmail);

  if (!recoveryEmail) {
    throw new Error("recovery-email-required");
  }

  if (!isValidEmailAddress(recoveryEmail)) {
    throw new Error("recovery-email-invalid");
  }

  const now = Date.now();
  const requestedChallengeId = Number(input.challengeId);
  let verification =
    Number.isInteger(requestedChallengeId) && requestedChallengeId > 0
      ? await getSignupRecoveryEmailVerificationById(requestedChallengeId)
      : null;

  if (verification && verification.email !== recoveryEmail) {
    verification = null;
  }

  if (!verification) {
    verification =
      await getLatestSignupRecoveryEmailVerificationByEmail(recoveryEmail);
  }

  if (verification && verification.resend_available_at.getTime() > now) {
    const secondsRemaining = Math.max(
      1,
      Math.ceil((verification.resend_available_at.getTime() - now) / 1000),
    );
    throw new Error(`recovery-email-resend-cooldown:${secondsRemaining}`);
  }

  const code = createSixDigitVerificationCode();
  const expiresAt = new Date(now + SIGNUP_RECOVERY_EMAIL_CODE_TTL_MS);
  const resendAvailableAt = new Date(
    now + SIGNUP_RECOVERY_EMAIL_RESEND_COOLDOWN_MS,
  );
  const codeHash = hashPassword(code);
  let verificationId = verification?.id ?? 0;

  if (verification && !verification.consumed_at) {
    await getDbPool().query(
      `
        UPDATE signup_recovery_email_verifications
        SET
          code_hash = ?,
          verification_token_hash = NULL,
          expires_at = ?,
          resend_available_at = ?,
          verified_at = NULL,
          updated_at = NOW()
        WHERE id = ?
      `,
      [codeHash, expiresAt, resendAvailableAt, verification.id],
    );
    verificationId = verification.id;
  } else {
    const [result] = await getDbPool().query(
      `
        INSERT INTO signup_recovery_email_verifications (
          email,
          code_hash,
          verification_token_hash,
          expires_at,
          resend_available_at,
          verified_at,
          consumed_at
        ) VALUES (?, ?, NULL, ?, ?, NULL, NULL)
      `,
      [recoveryEmail, codeHash, expiresAt, resendAvailableAt],
    );
    verificationId = Number((result as { insertId: number }).insertId);
  }

  const systemMailbox = getSystemNoReplyMailboxConfig();

  if (!systemMailbox) {
    throw new Error("system-mailbox-missing");
  }

  try {
    await sendSystemMailboxMessage(systemMailbox, {
      bodyText: buildSignupRecoveryEmailBody(code),
      recipients: [recoveryEmail],
      subject: SIGNUP_RECOVERY_EMAIL_SUBJECT,
    });
  } catch (error) {
    console.error("Signup recovery email send failed", error);
    throw new Error("recovery-email-send-failed");
  }

  return {
    challengeId: verificationId,
    expiresAt: expiresAt.toISOString(),
    resendAvailableAt: resendAvailableAt.toISOString(),
  };
}

export async function verifySignupRecoveryEmailCode(input: {
  challengeId: number;
  code: string;
  recoveryEmail: string;
}) {
  await ensureOfficialMailSchema();
  const challengeId = Number(input.challengeId);
  const code = input.code.trim();
  const recoveryEmail = normalizeEmailAddress(input.recoveryEmail);

  if (!challengeId || !code || !recoveryEmail) {
    throw new Error("required");
  }

  const verification =
    await getSignupRecoveryEmailVerificationById(challengeId);

  if (
    !verification ||
    verification.email !== recoveryEmail ||
    verification.consumed_at
  ) {
    throw new Error("not-found");
  }

  if (verification.expires_at.getTime() <= Date.now()) {
    throw new Error("expired");
  }

  if (!verifyPassword(code, verification.code_hash)) {
    throw new Error("invalid-code");
  }

  const verificationToken = randomBytes(24).toString("base64url");
  await getDbPool().query(
    `
      UPDATE signup_recovery_email_verifications
      SET
        verification_token_hash = ?,
        verified_at = NOW(),
        updated_at = NOW()
      WHERE id = ?
    `,
    [createSha256Hex(verificationToken), verification.id],
  );

  return {
    challengeId: verification.id,
    expiresAt: verification.expires_at.toISOString(),
    verificationToken,
  };
}

export async function requestPasswordResetLink(input: {
  companyName: string;
  email: string;
  mailAppUrl: string;
}) {
  await ensureOfficialMailSchema();
  const email = normalizeEmailAddress(input.email);
  const companyName = input.companyName.trim();
  const mailAppUrl = input.mailAppUrl.trim();

  if (!email || !companyName || !mailAppUrl) {
    throw new Error("required");
  }

  const user = await getUserByEmail(email);

  if (!user || user.company_name.trim() !== companyName) {
    throw new Error("not-found");
  }

  const recoveryEmail = normalizeEmailAddress(user.recovery_email ?? "");

  if (
    !recoveryEmail ||
    !user.recovery_email_verified_at ||
    !isValidEmailAddress(recoveryEmail)
  ) {
    throw new Error("recovery-email-missing");
  }

  const token = randomBytes(32).toString("base64url");
  const tokenHash = createSha256Hex(token);
  const expiresAt = new Date(Date.now() + PASSWORD_RESET_TOKEN_TTL_MS);

  await withTransaction(async (connection) => {
    await connection.query(
      `
        UPDATE password_reset_tokens
        SET used_at = COALESCE(used_at, NOW()), updated_at = NOW()
        WHERE user_id = ? AND used_at IS NULL
      `,
      [user.id],
    );
    await connection.query(
      `
        INSERT INTO password_reset_tokens (
          user_id,
          recovery_email,
          token_hash,
          expires_at
        ) VALUES (?, ?, ?, ?)
      `,
      [user.id, recoveryEmail, tokenHash, expiresAt],
    );
  });

  const systemMailbox = getSystemNoReplyMailboxConfig();

  if (!systemMailbox) {
    throw new Error("system-mailbox-missing");
  }

  const resetUrl = new URL(
    `/reset-password/${encodeURIComponent(token)}`,
    `${mailAppUrl.replace(/\/$/, "")}/`,
  ).toString();

  try {
    await sendSystemMailboxMessage(systemMailbox, {
      bodyText: buildPasswordResetLinkBody({
        companyName,
        resetUrl,
      }),
      recipients: [recoveryEmail],
      subject: PASSWORD_RESET_LINK_SUBJECT,
    });
  } catch (error) {
    console.error("Password reset email send failed", error);
    throw new Error("password-reset-send-failed");
  }

  return {
    maskedRecoveryEmail: maskEmailAddress(recoveryEmail),
  };
}

export type PasswordResetTokenStatus = "expired" | "invalid" | "used" | "valid";

export async function getPasswordResetTokenStatus(token: string) {
  await ensureOfficialMailSchema();
  const normalizedToken = token.trim();

  if (!normalizedToken) {
    return { status: "invalid" as const };
  }

  const resetToken = await getPasswordResetTokenByHash(
    createSha256Hex(normalizedToken),
  );

  if (!resetToken) {
    return { status: "invalid" as const };
  }

  if (resetToken.used_at) {
    return { status: "used" as const };
  }

  if (resetToken.expires_at.getTime() <= Date.now()) {
    return { status: "expired" as const };
  }

  return { status: "valid" as const };
}

export async function resetPasswordByToken(input: {
  password: string;
  token: string;
}) {
  await ensureOfficialMailSchema();
  const password = input.password.trim();
  const token = input.token.trim();

  if (!password || !token) {
    throw new Error("required");
  }

  const resetToken = await getPasswordResetTokenByHash(createSha256Hex(token));

  if (!resetToken) {
    throw new Error("invalid-token");
  }

  if (resetToken.used_at) {
    throw new Error("used-token");
  }

  if (resetToken.expires_at.getTime() <= Date.now()) {
    throw new Error("expired-token");
  }

  await setRepresentativePasswordByUserId(resetToken.user_id, password);
  await getDbPool().query(
    `
      UPDATE password_reset_tokens
      SET used_at = NOW(), updated_at = NOW()
      WHERE id = ?
    `,
    [resetToken.id],
  );
}

export async function findUserEmailsByCompanyName(companyName: string) {
  const normalizedCompanyName = companyName.trim();

  if (!normalizedCompanyName) {
    return [];
  }

  await ensureOfficialMailSchema();
  const [rows] = await getDbPool().query<UserRow[]>(
    `
      SELECT
        id,
        email,
        company_name,
        display_name,
        recovery_email,
        recovery_email_verified_at,
        password_hash,
        mail_configured,
        mail_sidebar_width,
        mail_list_width,
        mail_list_height,
        mail_view_mode,
        mail_sort_order
      FROM users
      WHERE company_name = ?
      ORDER BY id DESC
      LIMIT 10
    `,
    [normalizedCompanyName],
  );

  return rows.map((row) => ({
    maskedEmail: maskEmailAddress(row.email),
    companyName: row.company_name,
    displayName: row.display_name,
  }));
}

export async function resetUserPassword(input: {
  email: string;
  companyName: string;
  password: string;
}) {
  const email = input.email.trim().toLowerCase();
  const companyName = input.companyName.trim();
  const password = input.password.trim();

  if (!email || !companyName || !password) {
    throw new Error("required");
  }

  const existingUser = await getUserByEmail(email);

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

  if (existingUser.company_name.trim() !== companyName) {
    throw new Error("not-found");
  }

  return setRepresentativePasswordByUserId(existingUser.id, password);
}

async function setRepresentativePasswordByUserId(
  userId: number,
  nextPassword: string,
) {
  const normalizedPassword = nextPassword.trim();

  if (!Number.isInteger(userId) || userId <= 0 || !normalizedPassword) {
    throw new Error("required");
  }

  const user = await getUserById(userId);

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

  const [setup, mailbox] = await Promise.all([
    getLatestSetupByUserId(user.id),
    getLatestMailboxByUserId(user.id),
  ]);

  if (setup?.domain && setup.local_part) {
    await ensureMailcowMailboxAccount({
      displayName: user.display_name,
      domain: setup.domain,
      localPart: setup.local_part,
      mailboxPassword: normalizedPassword,
    });
  }

  await withTransaction(async (connection) => {
    await connection.query(
      `
        UPDATE users
        SET password_hash = ?, updated_at = NOW()
        WHERE id = ?
      `,
      [hashPassword(normalizedPassword), user.id],
    );

    if (mailbox) {
      await connection.query(
        `
          UPDATE mailboxes
          SET
            password_ciphertext = ?,
            password_updated_at = NOW(),
            last_sync_error = NULL,
            updated_at = NOW()
          WHERE id = ?
        `,
        [encryptMailboxPassword(normalizedPassword), mailbox.id],
      );
    }
  });

  const snapshot = await getSessionSnapshotByUserId(user.id);

  if (!snapshot) {
    throw new Error("snapshot-load-failed");
  }

  return snapshot;
}

export async function updateRepresentativePasswordByEmail(
  email: string,
  currentPassword: string,
  nextPassword: string,
) {
  const normalizedEmail = email.trim().toLowerCase();
  const normalizedCurrentPassword = currentPassword.trim();
  const normalizedPassword = nextPassword.trim();

  if (!normalizedEmail || !normalizedCurrentPassword || !normalizedPassword) {
    throw new Error("required");
  }

  const user = await getUserByEmail(normalizedEmail);

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

  if (!verifyPassword(normalizedCurrentPassword, user.password_hash)) {
    throw new Error("current-password-invalid");
  }

  return setRepresentativePasswordByUserId(user.id, normalizedPassword);
}

export async function updateMailboxDisplayNameByEmail(
  email: string,
  nextDisplayName: string,
) {
  const normalizedEmail = email.trim().toLowerCase();
  const displayName = nextDisplayName.replace(/\s+/g, " ").trim();

  if (!normalizedEmail || !displayName) {
    throw new Error("required");
  }

  if (displayName.length > 191) {
    throw new Error("display-name-too-long");
  }

  const user = await getUserByEmail(normalizedEmail);

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

  const mailbox = await getLatestMailboxByUserId(user.id);

  if (mailbox?.password_ciphertext) {
    const [localPart, domain] = mailbox.email.split("@");

    if (localPart && domain) {
      await ensureMailcowMailbox({
        displayName,
        domain,
        localPart,
        mailboxPassword: decryptMailboxPassword(mailbox.password_ciphertext),
      });
    }
  }

  await withTransaction(async (connection) => {
    await connection.query(
      `
        UPDATE users
        SET display_name = ?, updated_at = NOW()
        WHERE id = ?
      `,
      [displayName, user.id],
    );

    // Team members are re-synced from this table during domain operations.
    await connection.query(
      `
        UPDATE managed_team_mailboxes
        SET display_name = ?, updated_at = NOW()
        WHERE LOWER(email) = ?
      `,
      [displayName, normalizedEmail],
    );
  });

  const snapshot = await getSessionSnapshotByUserId(user.id);

  if (!snapshot) {
    throw new Error("snapshot-load-failed");
  }

  return snapshot;
}

export async function provisionMailSetup(
  email: string,
  domain: string,
  localPart: string,
  mailboxPassword?: string,
  dkimKeySize: 1024 | 2048 = 2048,
) {
  const normalizedEmail = email.trim().toLowerCase();
  const normalizedDomain = domain
    .trim()
    .toLowerCase()
    .replace(/^https?:\/\//, "")
    .replace(/\/$/, "");
  const normalizedLocalPart = localPart
    .trim()
    .replace(/\s+/g, "")
    .toLowerCase();
  const requestedMailboxPassword = mailboxPassword?.trim() ?? "";

  if (!normalizedEmail || !normalizedDomain || !normalizedLocalPart) {
    throw new Error("required");
  }

  if (!LOCAL_PART_PATTERN.test(normalizedLocalPart)) {
    throw new Error("invalid-local-part");
  }

  const user = await getUserByEmail(normalizedEmail);

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

  const existingSetup = await getLatestSetupByUserId(user.id);
  const existingMailbox = await getLatestMailboxByUserId(user.id);
  const mailboxEmail = `${normalizedLocalPart}@${normalizedDomain}`;
  const conflictingUser = await getUserByEmail(mailboxEmail);

  if (conflictingUser && conflictingUser.id !== user.id) {
    throw new Error("email-exists");
  }

  const resolvedMailboxPassword = requestedMailboxPassword
    ? requestedMailboxPassword
    : existingMailbox?.password_ciphertext
      ? decryptMailboxPassword(existingMailbox.password_ciphertext).trim()
      : "";

  if (!resolvedMailboxPassword) {
    throw new Error("mailbox-auth-missing");
  }

  const encryptedMailboxPassword = encryptMailboxPassword(
    resolvedMailboxPassword,
  );
  const pool = getDbPool();
  const [ownerRows] = await pool.query<
    (RowDataPacket & { user_id: number | null })[]
  >("SELECT user_id FROM domains WHERE domain = ? LIMIT 1", [normalizedDomain]);

  if (ownerRows[0]?.user_id && ownerRows[0].user_id !== user.id) {
    throw new Error("domain-taken");
  }

  const mailcowProvisioning = await ensureMailcowProvisioning({
    allowExistingDomain:
      existingSetup?.domain === normalizedDomain ||
      existingMailbox?.email === mailboxEmail,
    dkimKeySize,
    displayName: user.display_name,
    domain: normalizedDomain,
    localPart: normalizedLocalPart,
    mailboxPassword: resolvedMailboxPassword,
  });

  await withTransaction(async (connection) => {
    const [userRows] = await connection.query<UserRow[]>(
      `
        SELECT
          id,
          email,
          company_name,
          display_name,
          password_hash,
          mail_configured,
          mail_sidebar_width,
          mail_list_width,
          mail_list_height,
          mail_view_mode,
          mail_sort_order
        FROM users
        WHERE email = ?
        LIMIT 1
      `,
      [normalizedEmail],
    );

    const user = userRows[0];

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

    const [ownerRows] = await connection.query<
      (RowDataPacket & { user_id: number | null })[]
    >("SELECT user_id FROM domains WHERE domain = ? LIMIT 1", [
      normalizedDomain,
    ]);

    if (ownerRows[0]?.user_id && ownerRows[0].user_id !== user.id) {
      throw new Error("domain-taken");
    }

    await connection.query("DELETE FROM domains WHERE user_id = ?", [user.id]);

    const [domainResult] = await connection.query(
      `
        INSERT INTO domains (
          user_id,
          domain,
          status,
          dkim_selector,
          dkim_public_key,
          dkim_private_key
        ) VALUES (?, ?, 'pending', ?, ?, ?)
      `,
      [
        user.id,
        normalizedDomain,
        mailcowProvisioning.dkimSelector,
        mailcowProvisioning.dkimPublicKey,
        "",
      ],
    );

    const domainId = Number((domainResult as { insertId: number }).insertId);
    const [mailboxResult] = await connection.query(
      `
        INSERT INTO mailboxes (
          user_id,
          domain_id,
          local_part,
          email,
          password_ciphertext,
          password_updated_at,
          last_sync_at,
          last_sync_error,
          status
        ) VALUES (?, ?, ?, ?, ?, NOW(), NULL, NULL, 'pending_dns')
      `,
      [
        user.id,
        domainId,
        normalizedLocalPart,
        mailboxEmail,
        encryptedMailboxPassword,
      ],
    );

    const mailboxId = Number((mailboxResult as { insertId: number }).insertId);

    await syncDnsRecords(
      connection,
      domainId,
      buildDnsTemplate(
        normalizedDomain,
        mailcowProvisioning.dkimSelector,
        mailcowProvisioning.dkimPublicKey,
      ),
    );
    await ensureLocalMailboxFolders(connection, mailboxId);

    await connection.query(
      `
        UPDATE users
        SET email = ?, mail_configured = 0, updated_at = NOW()
        WHERE id = ?
      `,
      [mailboxEmail, user.id],
    );
  });

  const snapshot = await getSessionSnapshotByUserId(user.id);

  if (!snapshot) {
    throw new Error("snapshot-load-failed");
  }

  try {
    const latestUser = await getUserById(user.id);

    if (latestUser) {
      const latestMailbox = await getLatestMailboxByUserId(latestUser.id);

      if (latestMailbox) {
        const welcomeSent = await sendSystemWelcomeMail({
          companyName: user.company_name,
          mailboxEmail,
          mailboxId: latestMailbox.id,
        });

        if (welcomeSent) {
          await refreshMailboxByEmail(mailboxEmail, { folder: "inbox" });
        }
      }
    }
  } catch (error) {
    console.error("System welcome mail send failed", error);
  }

  return snapshot;
}

export async function regenerateMailSetupDkim(
  email: string,
  dkimKeySize: 1024 | 2048,
) {
  const normalizedEmail = email.trim().toLowerCase();
  const user = await getUserByEmail(normalizedEmail);

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

  const setup = await getLatestSetupByUserId(user.id);

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

  const dkim = await regenerateMailcowDkim(setup.domain, dkimKeySize);

  await withTransaction(async (connection) => {
    await connection.query(
      `
        UPDATE domains
        SET
          dkim_selector = ?,
          dkim_public_key = ?,
          dkim_private_key = '',
          status = 'pending',
          verified_at = NULL,
          updated_at = NOW()
        WHERE id = ? AND user_id = ?
      `,
      [dkim.dkimSelector, dkim.dkimPublicKey, setup.domain_id, user.id],
    );
    await connection.query(
      `
        DELETE FROM dns_records
        WHERE domain_id = ?
          AND record_type = 'TXT'
          AND host_name = ?
      `,
      [setup.domain_id, `${setup.dkim_selector}._domainkey`],
    );
    await connection.query(
      `
        INSERT INTO dns_records (
          domain_id,
          record_type,
          host_name,
          value_text,
          priority,
          status
        ) VALUES (?, 'TXT', ?, ?, NULL, 'pending')
      `,
      [
        setup.domain_id,
        `${dkim.dkimSelector}._domainkey`,
        `v=DKIM1; k=rsa; p=${dkim.dkimPublicKey}`,
      ],
    );
  });

  const snapshot = await getSessionSnapshotByUserId(user.id);

  if (!snapshot) {
    throw new Error("snapshot-load-failed");
  }

  return snapshot;
}

export async function regenerateMailSetupDkimByDomainId(
  domainId: number,
  dkimKeySize: 1024 | 2048,
) {
  if (!Number.isInteger(domainId) || domainId <= 0) {
    throw new Error("setup-not-found");
  }

  const setup = await getMailSetupByDomainId(domainId);

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

  const dkim = await regenerateMailcowDkim(setup.domain, dkimKeySize);

  await withTransaction(async (connection) => {
    await connection.query(
      `
        UPDATE domains
        SET
          dkim_selector = ?,
          dkim_public_key = ?,
          dkim_private_key = '',
          status = 'pending',
          verified_at = NULL,
          updated_at = NOW()
        WHERE id = ?
      `,
      [dkim.dkimSelector, dkim.dkimPublicKey, setup.domain_id],
    );
    await connection.query(
      `
        DELETE FROM dns_records
        WHERE domain_id = ?
          AND record_type = 'TXT'
          AND host_name = ?
      `,
      [setup.domain_id, `${setup.dkim_selector}._domainkey`],
    );
    await connection.query(
      `
        INSERT INTO dns_records (
          domain_id,
          record_type,
          host_name,
          value_text,
          priority,
          status
        ) VALUES (?, 'TXT', ?, ?, NULL, 'pending')
      `,
      [
        setup.domain_id,
        `${dkim.dkimSelector}._domainkey`,
        `v=DKIM1; k=rsa; p=${dkim.dkimPublicKey}`,
      ],
    );
  });

  return {
    dkimSelector: dkim.dkimSelector,
    domain: setup.domain,
    keySize: dkimKeySize,
  };
}

async function updateMailSetupDkimUsage(input: {
  dkimEnabled: boolean;
  domain: string;
  domainId: number;
  mailboxEmail: string;
  userId: number;
}) {
  await getDbPool().query(
    `
      UPDATE domains
      SET
        dkim_enabled = ?,
        status = 'pending',
        verified_at = NULL,
        updated_at = NOW()
      WHERE id = ?
    `,
    [input.dkimEnabled ? 1 : 0, input.domainId],
  );

  return verifyMailSetupTarget(input);
}

export async function updateMailSetupDkimUsageByEmail(
  email: string,
  dkimEnabled: boolean,
) {
  const user = await getUserByEmail(email.trim().toLowerCase());

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

  const setup = await getLatestSetupByUserId(user.id);

  if (!setup?.mailbox_email) {
    throw new Error("setup-not-found");
  }

  await updateMailSetupDkimUsage({
    dkimEnabled,
    domain: setup.domain,
    domainId: setup.domain_id,
    mailboxEmail: setup.mailbox_email,
    userId: user.id,
  });

  const snapshot = await getSessionSnapshotByUserId(user.id);

  if (!snapshot) {
    throw new Error("snapshot-load-failed");
  }

  return snapshot;
}

export async function updateMailSetupDkimUsageByDomainId(
  domainId: number,
  dkimEnabled: boolean,
) {
  if (!Number.isInteger(domainId) || domainId <= 0) {
    throw new Error("setup-not-found");
  }

  const setup = await getMailSetupByDomainId(domainId);

  if (!setup?.mailbox_email) {
    throw new Error("setup-not-found");
  }

  const result = await updateMailSetupDkimUsage({
    dkimEnabled,
    domain: setup.domain,
    domainId: setup.domain_id,
    mailboxEmail: setup.mailbox_email,
    userId: setup.owner_user_id,
  });

  return {
    domain: setup.domain,
    dkimEnabled,
    verified: result.verified,
  };
}

function toFqdn(domain: string, host: string) {
  if (host === "@") {
    return domain;
  }

  return `${host}.${domain}`;
}

function flattenTxt(rows: string[][]) {
  return rows.map((row) => row.join("")).join(" ");
}

function normalizeRecordValue(value: string) {
  return value.trim().replace(/\.$/, "").toLowerCase();
}

function normalizeTxtValue(value: string) {
  return value.replace(/\s+/g, "").trim();
}

async function resolveDomainDns<T>(
  resolve: (resolver: Resolver) => Promise<T>,
) {
  let lastError: unknown;

  for (const server of DOMAIN_VERIFICATION_DNS_SERVERS) {
    const resolver = new Resolver();
    resolver.setServers([server]);

    try {
      return await resolve(resolver);
    } catch (error) {
      lastError = error;
    }
  }

  throw lastError ?? new Error("domain-dns-resolver-unavailable");
}

async function verifyMx(domain: string, expectedValue: string) {
  const records = await resolveDomainDns((resolver) => resolver.resolveMx(domain));
  return records.some(
    (record) =>
      normalizeRecordValue(record.exchange) ===
      normalizeRecordValue(expectedValue),
  );
}

async function verifyTxt(fqdn: string, expectedValue: string) {
  const records = await resolveDomainDns((resolver) => resolver.resolveTxt(fqdn));

  return records.some((record) => {
    const value = flattenTxt([record]).replace(/\s+/g, " ").trim();

    if (expectedValue === SPF_RECORD_VALUE) {
      const mechanisms = value.toLowerCase().split(/\s+/);
      return (
        mechanisms[0] === "v=spf1" &&
        mechanisms.includes(`include:${normalizeRecordValue(MX_HOSTNAME)}`)
      );
    }

    return normalizeTxtValue(value).includes(normalizeTxtValue(expectedValue));
  });
}

async function verifyMailSetupTarget(input: {
  dkimEnabled: boolean;
  domain: string;
  domainId: number;
  mailboxEmail: string;
  userId: number;
}) {
  const dnsRecords = await getDnsRecords(input.domainId);
  const mailcowProvisioned = await isMailcowProvisioned(
    input.domain,
    input.mailboxEmail,
  );

  const verificationResults = await Promise.all(
    dnsRecords.map(async (record) => {
      const fqdn = toFqdn(input.domain, record.host_name);
      const isDkimRecord =
        record.record_type === "TXT" && record.host_name.endsWith("._domainkey");

      if (isDkimRecord && !input.dkimEnabled) {
        return {
          ...record,
          isRequired: false,
          isValid: false,
        };
      }

      try {
        if (record.record_type === "MX") {
          return {
            ...record,
            isRequired: true,
            isValid: await verifyMx(input.domain, record.value_text),
          };
        }

        return {
          ...record,
          isRequired: true,
          isValid: await verifyTxt(fqdn, record.value_text),
        };
      } catch {
        return {
          ...record,
          isRequired: true,
          isValid: false,
        };
      }
    }),
  );

  const verified =
    verificationResults.every(
      (record) => !record.isRequired || record.isValid,
    ) && mailcowProvisioned;

  const newlyVerified = await withTransaction(async (connection) => {
    const [domainRows] = await connection.query<
      (RowDataPacket & { verified_at: Date | null })[]
    >(
      "SELECT verified_at FROM domains WHERE id = ? FOR UPDATE",
      [input.domainId],
    );
    const wasPreviouslyVerified = Boolean(domainRows[0]?.verified_at);

    for (const record of verificationResults) {
      await connection.query(
        `
          UPDATE dns_records
          SET status = ?, last_checked_at = NOW(), updated_at = NOW()
          WHERE domain_id = ? AND record_type = ? AND host_name = ?
        `,
        [
          !record.isRequired
            ? "pending"
            : record.isValid
              ? "verified"
              : "failed",
          input.domainId,
          record.record_type,
          record.host_name,
        ],
      );
    }

    await connection.query(
      `
        UPDATE domains
        SET
          status = ?,
          verified_at = CASE
            WHEN ? = 1 AND verified_at IS NULL THEN NOW()
            ELSE verified_at
          END,
          updated_at = NOW()
        WHERE id = ?
      `,
      [verified ? "verified" : "failed", verified ? 1 : 0, input.domainId],
    );
    await connection.query(
      `
        UPDATE mailboxes
        SET status = ?, updated_at = NOW()
        WHERE domain_id = ? AND user_id = ?
      `,
      [verified ? "active" : "pending_dns", input.domainId, input.userId],
    );
    await connection.query(
      `
        UPDATE users
        SET mail_configured = ?, updated_at = NOW()
        WHERE id = ?
      `,
      [verified ? 1 : 0, input.userId],
    );

    return verified && !wasPreviouslyVerified;
  });

  if (newlyVerified) {
    await recordMarketingLifecycleEvent({
      domainId: input.domainId,
      eventType: "domain_verified",
      userId: input.userId,
    });
  }

  return { verified };
}

export async function verifyMailSetup(email: string) {
  const snapshot = await getSessionSnapshotByEmail(email);

  if (!snapshot || !snapshot.domain) {
    throw new Error("setup-not-found");
  }

  const user = await getUserByEmail(snapshot.email);

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

  const setup = await getLatestSetupByUserId(user.id);

  if (!setup?.mailbox_email) {
    throw new Error("setup-not-found");
  }

  await verifyMailSetupTarget({
    dkimEnabled: setup.dkim_enabled === 1,
    domain: setup.domain,
    domainId: setup.domain_id,
    mailboxEmail: setup.mailbox_email,
    userId: user.id,
  });

  const updatedSnapshot = await getSessionSnapshotByEmail(email);

  if (!updatedSnapshot) {
    throw new Error("snapshot-load-failed");
  }

  return updatedSnapshot;
}

export async function verifyMailSetupByDomainId(domainId: number) {
  if (!Number.isInteger(domainId) || domainId <= 0) {
    throw new Error("setup-not-found");
  }

  const setup = await getMailSetupByDomainId(domainId);

  if (!setup?.mailbox_email) {
    throw new Error("setup-not-found");
  }

  const result = await verifyMailSetupTarget({
    dkimEnabled: setup.dkim_enabled === 1,
    domain: setup.domain,
    domainId: setup.domain_id,
    mailboxEmail: setup.mailbox_email,
    userId: setup.owner_user_id,
  });

  return {
    domain: setup.domain,
    verified: result.verified,
  };
}

export async function getMailSetupOverviewByEmail(email: string) {
  const user = await getUserByEmail(email);

  if (!user) {
    return null;
  }

  const setup = await getLatestSetupByUserId(user.id);

  if (!setup) {
    return null;
  }

  const dnsRecords = await getDnsRecords(setup.domain_id);

  return {
    domain: setup.domain,
    mailbox: setup.mailbox_email,
    localPart: setup.local_part,
    status: setup.domain_status,
    mailConfigured:
      Boolean(user.mail_configured) ||
      (Boolean(setup.mailbox_email) && setup.mailbox_status === "active"),
    dkimEnabled: setup.dkim_enabled === 1,
    mxHostname: MX_HOSTNAME,
    dkimSelector: setup.dkim_selector,
    dkimPublicKey: setup.dkim_public_key,
    verifiedAt: normalizeDate(setup.verified_at),
    dnsRecords: dnsRecords.map((record) => ({
      type: record.record_type,
      host: record.host_name === "@" ? setup.domain : record.host_name,
      value: record.value_text,
      priority: record.priority,
      status: record.status,
      lastCheckedAt: normalizeDate(record.last_checked_at),
    })),
  } satisfies MailSetupOverview;
}

export async function refreshMailboxByEmail(
  email: string,
  options?: {
    folder?: string;
  },
) {
  await syncMailboxCacheByEmail(email, options?.folder);
}

export async function refreshMailboxIfStaleByEmail(
  email: string,
  options?: {
    folder?: string;
    maxAgeMs?: number;
  },
) {
  const user = await getUserByEmail(email);

  if (!user) {
    return false;
  }

  const mailbox = await getLatestMailboxByUserId(user.id);

  if (!mailbox) {
    return false;
  }

  await ensureOfficialMailSchema();
  const requestedFolderKey = resolveMailboxRefreshFolder(options?.folder);
  const [rows] = await getDbPool().query<
    (RowDataPacket & { last_synced_at: Date | null; system_name: string })[]
  >(
    `
      SELECT system_name, last_synced_at
      FROM mailbox_folders
      WHERE mailbox_id = ? AND system_name = ?
      LIMIT 1
    `,
    [mailbox.id, requestedFolderKey],
  );

  const resolvedFolderKey = rows[0]?.system_name ?? "inbox";
  const lastSyncedAt = rows[0]?.last_synced_at ?? null;
  const maxAgeMs = options?.maxAgeMs ?? MAILBOX_AUTO_REFRESH_MAX_AGE_MS;

  if (lastSyncedAt && Date.now() - lastSyncedAt.getTime() < maxAgeMs) {
    return false;
  }

  await syncMailboxCacheByEmail(email, resolvedFolderKey);
  return true;
}

export async function saveMailboxCredentialsByEmail(
  email: string,
  password: string,
  options?: {
    folder?: string;
  },
) {
  const normalizedPassword = password.trim();

  if (!normalizedPassword) {
    throw new Error("mailbox-password-required");
  }

  const user = await getUserByEmail(email);

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

  const mailbox = await getLatestMailboxByUserId(user.id);

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

  await getDbPool().query(
    `
      UPDATE mailboxes
      SET
        password_ciphertext = ?,
        password_updated_at = NOW(),
        last_sync_error = NULL,
        updated_at = NOW()
      WHERE id = ?
    `,
    [encryptMailboxPassword(normalizedPassword), mailbox.id],
  );

  await syncMailboxCacheByEmail(email, options?.folder);
}

async function getDeliveryLogsByMessageId(messageId: number) {
  const pool = getDbPool();
  const [rows] = await pool.query<MailDeliveryLogRow[]>(
    `
      SELECT
        id,
        action,
        status,
        transport,
        source_address,
        target_address,
        subject,
        message_id_header,
        response_text,
        created_at
      FROM mailbox_delivery_logs
      WHERE mailbox_message_id = ?
      ORDER BY created_at DESC, id DESC
    `,
    [messageId],
  );

  return rows.map((row) => ({
    id: row.id,
    action: row.action,
    status: row.status,
    transport: row.transport,
    sourceAddress: row.source_address,
    targetAddress: row.target_address,
    subject: row.subject,
    messageIdHeader: row.message_id_header,
    responseText: row.response_text,
    createdAt: row.created_at.toISOString(),
  })) satisfies MailDeliveryLog[];
}

export async function getMailboxMessageBodyViewByEmail(
  email: string,
  messageId: number,
) {
  if (!Number.isInteger(messageId) || messageId <= 0) {
    return null;
  }

  const user = await getUserByEmail(email);

  if (!user) {
    return null;
  }

  const mailbox = await getLatestMailboxByUserId(user.id);

  if (!mailbox) {
    return null;
  }

  const [rows] = await getDbPool().query<MailMessageRow[]>(
    `
      SELECT
        mm.id,
        mm.remote_uid,
        mm.remote_folder,
        mm.subject,
        mm.from_name,
        mm.from_address,
        mm.to_addresses,
        mm.snippet,
        mm.body_text,
        mm.body_html,
        mm.message_id_header,
        mm.raw_source,
        mm.transport_status,
        mm.transport_response,
        mm.remote_flags,
        mm.is_read,
        mm.is_starred,
        mm.received_at,
        mm.direction,
        f.system_name AS folder_system_name,
        f.name AS folder_name
      FROM mailbox_messages mm
      INNER JOIN mailbox_folders f ON f.id = mm.folder_id
      WHERE mm.mailbox_id = ?
        AND mm.id = ?
      LIMIT 1
    `,
    [mailbox.id, messageId],
  );
  const row = rows[0];

  if (!row) {
    return null;
  }

  return {
    bodyHtmlDisplay:
      normalizeStoredMailboxHtml(row.body_html) ??
      (row.body_text.trim() ? plainTextToHtml(row.body_text) : null),
    bodyText: row.body_text,
    fromAddress: row.from_address,
    fromName: row.from_name,
    id: row.id,
    receivedAt: row.received_at.toISOString(),
    subject: row.subject,
    toAddresses: row.to_addresses,
  } satisfies MailMessageBodyView;
}

export async function getMailboxDeliveryLogsByEmail(
  email: string,
  options?: {
    limit?: number;
  },
) {
  const user = await getUserByEmail(email);

  if (!user) {
    return [];
  }

  const mailbox = await getLatestMailboxByUserId(user.id);

  if (!mailbox) {
    return [];
  }

  const limit =
    options?.limit && Number.isFinite(options.limit)
      ? Math.max(1, Math.min(100, Math.floor(options.limit)))
      : 24;
  const pool = getDbPool();
  const [rows] = await pool.query<MailDeliveryLogRow[]>(
    `
      SELECT
        id,
        action,
        status,
        transport,
        source_address,
        target_address,
        subject,
        message_id_header,
        response_text,
        created_at
      FROM mailbox_delivery_logs
      WHERE mailbox_id = ?
      ORDER BY created_at DESC, id DESC
      LIMIT ?
    `,
    [mailbox.id, limit],
  );

  return rows.map((row) => ({
    id: row.id,
    action: row.action,
    status: row.status,
    transport: row.transport,
    sourceAddress: row.source_address,
    targetAddress: row.target_address,
    subject: row.subject,
    messageIdHeader: row.message_id_header,
    responseText: row.response_text,
    createdAt: row.created_at.toISOString(),
  })) satisfies MailDeliveryLog[];
}

export async function getMailboxAttachmentByEmail(
  email: string,
  messageId: number,
  attachmentIndex: number,
): Promise<RemoteMailboxAttachmentPayload | null> {
  if (!Number.isInteger(messageId) || messageId < 1) {
    return null;
  }

  if (!Number.isInteger(attachmentIndex)) {
    return null;
  }

  const user = await getUserByEmail(email.trim().toLowerCase());

  if (!user) {
    return null;
  }

  const mailbox = await getLatestMailboxByUserId(user.id);

  if (!mailbox) {
    return null;
  }

  const [rows] = await getDbPool().query<
    (RowDataPacket & { raw_source: string | null })[]
  >(
    `
      SELECT raw_source
      FROM mailbox_messages
      WHERE id = ?
        AND mailbox_id = ?
      LIMIT 1
    `,
    [messageId, mailbox.id],
  );

  const row = rows[0];

  if (!row?.raw_source) {
    if (attachmentIndex < 0) {
      const linkedAttachment =
        await getLinkedComposeUploadAttachmentPayloadByMessageId(
          messageId,
          attachmentIndex,
        );

      if (!linkedAttachment) {
        return null;
      }

      return {
        content: linkedAttachment.content,
        contentDisposition: linkedAttachment.contentDisposition,
        contentId: linkedAttachment.contentId,
        contentType: linkedAttachment.contentType,
        extension: linkedAttachment.extension,
        filename: linkedAttachment.filename,
        index: linkedAttachment.index,
        isInline: linkedAttachment.isInline,
        isPreviewable: linkedAttachment.isPreviewable,
        sizeBytes: linkedAttachment.sizeBytes,
      } satisfies RemoteMailboxAttachmentPayload;
    }

    return null;
  }

  if (attachmentIndex < 0) {
    const linkedAttachment =
      await getLinkedComposeUploadAttachmentPayloadByMessageId(
        messageId,
        attachmentIndex,
      );

    if (!linkedAttachment) {
      return null;
    }

    return {
      content: linkedAttachment.content,
      contentDisposition: linkedAttachment.contentDisposition,
      contentId: linkedAttachment.contentId,
      contentType: linkedAttachment.contentType,
      extension: linkedAttachment.extension,
      filename: linkedAttachment.filename,
      index: linkedAttachment.index,
      isInline: linkedAttachment.isInline,
      isPreviewable: linkedAttachment.isPreviewable,
      sizeBytes: linkedAttachment.sizeBytes,
    } satisfies RemoteMailboxAttachmentPayload;
  }

  return getAttachmentPayloadFromRawSource(row.raw_source, attachmentIndex);
}

export async function getMailAppOverviewByEmail(
  email: string,
  options?: {
    folder?: string;
    messageId?: number | null;
    messageFilter?: MailMessageFilter;
    page?: number;
    pageSize?: number;
    markMessageRead?: boolean;
    searchField?: MailSearchField;
    searchQuery?: string;
    sortOrder?: MailListSortOrder;
  },
) {
  const user = await getUserByEmail(email);

  if (!user) {
    return null;
  }

  const mailbox = await getLatestMailboxByUserId(user.id);

  if (!mailbox) {
    return null;
  }

  const quotaPromise = Promise.race<MailcowMailboxQuotaSnapshot | null>([
    getMailcowMailboxQuota(mailbox.email).catch(() => null),
    new Promise<null>((resolve) => {
      setTimeout(() => resolve(null), MAILCOW_QUOTA_FETCH_TIMEOUT_MS);
    }),
  ]);

  const pool = getDbPool();

  if (options?.markMessageRead && options?.messageId) {
    try {
      await markMailboxMessageAsRead(email, options.messageId);
    } catch {
      // Keep rendering the mailbox even if IMAP flag sync fails.
    }
  }

  const [folderRows] = await pool.query<FolderSummaryRow[]>(
    `
      SELECT
        f.id,
        f.system_name,
        f.name,
        f.remote_name,
        COALESCE(stats.total_count, 0) AS remote_total,
        COALESCE(stats.unread_count, 0) AS remote_unseen,
        f.last_synced_at
      FROM mailbox_folders f
      LEFT JOIN (
        SELECT
          folder_id,
          COUNT(*) AS total_count,
          SUM(CASE WHEN is_read = 0 THEN 1 ELSE 0 END) AS unread_count
        FROM mailbox_messages
        WHERE mailbox_id = ?
        GROUP BY folder_id
      ) stats ON stats.folder_id = f.id
      WHERE f.mailbox_id = ?
      ORDER BY f.sort_order ASC, f.id ASC
    `,
    [mailbox.id, mailbox.id],
  );

  const folders = folderRows.map((row) => ({
    id: row.id,
    kind: folderKindForSystemName(row.system_name),
    systemName: row.system_name,
    name: row.name,
    count: Number(row.remote_total ?? 0),
    unreadCount: Number(row.remote_unseen ?? 0),
  }));

  const defaultFolder =
    folders.find((folder) => folder.systemName === "inbox")?.systemName ??
    folders[0]?.systemName ??
    "inbox";
  const currentFolder =
    options?.folder === "all"
      ? "all"
      : (folders.find((folder) => folder.systemName === options?.folder)
          ?.systemName ?? defaultFolder);
  const messageFilter: MailMessageFilter =
    options?.messageFilter === "unread" ||
    options?.messageFilter === "important" ||
    options?.messageFilter === "attachments"
      ? options.messageFilter
      : "all";
  const sortOrder: MailListSortOrder =
    options?.sortOrder === "oldest" ? "oldest" : "latest";
  const searchField = normalizeMailSearchField(options?.searchField);
  const searchQuery = normalizeMailSearchQuery(options?.searchQuery);
  const messageQueryFolder = currentFolder === "all" ? "inbox" : currentFolder;
  const searchClause = buildMailboxSearchSql({
    field: searchField,
    query: searchQuery,
  });
  const pageSize = normalizeMailboxPageSize(options?.pageSize);

  const importantFilterSql = "mm.is_starred = 1";
  const attachmentFilterSql = `LOWER(CONCAT_WS(' ', mm.subject, mm.snippet)) REGEXP '${ATTACHMENT_MESSAGE_REGEXP}'`;
  const messageFilterSql =
    messageFilter === "unread"
      ? "AND mm.is_read = 0"
      : messageFilter === "important"
        ? `AND ${importantFilterSql}`
        : messageFilter === "attachments"
          ? `AND ${attachmentFilterSql}`
          : "";

  const [messageCountRows] = await pool.query<MailMessageCountRow[]>(
    `
      SELECT
        COUNT(*) AS all_count,
        SUM(CASE WHEN mm.is_read = 0 THEN 1 ELSE 0 END) AS unread_count,
        SUM(CASE WHEN ${importantFilterSql} THEN 1 ELSE 0 END) AS important_count,
        SUM(CASE WHEN ${attachmentFilterSql} THEN 1 ELSE 0 END) AS attachments_count
      FROM mailbox_messages mm
      INNER JOIN mailbox_folders f ON f.id = mm.folder_id
      WHERE mm.mailbox_id = ?
        AND f.system_name = ?
        ${searchClause.sql}
    `,
    [mailbox.id, messageQueryFolder, ...searchClause.params],
  );

  const countRow = messageCountRows[0];
  const messageFilterCounts = {
    all: Number(countRow?.all_count ?? 0),
    unread: Number(countRow?.unread_count ?? 0),
    important: Number(countRow?.important_count ?? 0),
    attachments: Number(countRow?.attachments_count ?? 0),
  } satisfies MailMessageFilterCounts;
  const totalMessages = messageFilterCounts[messageFilter];
  const totalPages = Math.max(1, Math.ceil(totalMessages / pageSize));
  const requestedPage =
    options?.page && Number.isFinite(options.page) && options.page > 0
      ? Math.floor(options.page)
      : 1;
  const currentPage = Math.max(1, Math.min(totalPages, requestedPage));
  const offset = (currentPage - 1) * pageSize;

  const [messageRows] = await pool.query<MailMessageRow[]>(
    `
      SELECT
        mm.id,
        mm.remote_uid,
        mm.remote_folder,
        mm.subject,
        mm.from_name,
        mm.from_address,
        mm.to_addresses,
        mm.snippet,
        mm.body_text,
        mm.body_html,
        mm.message_id_header,
        mm.raw_source,
        mm.transport_status,
        mm.transport_response,
        mm.remote_flags,
        mm.is_read,
        mm.is_starred,
        mm.received_at,
        mm.direction,
        mat.summary_status AS ai_summary_status,
        mat.summary_transport_response AS ai_summary_response,
        mat.last_error AS ai_summary_error,
        f.system_name AS folder_system_name,
        f.name AS folder_name
      FROM mailbox_messages mm
      INNER JOIN mailbox_folders f ON f.id = mm.folder_id
      LEFT JOIN mailbox_ai_assist_threads mat ON mat.source_message_id = mm.id
      WHERE mm.mailbox_id = ?
        AND f.system_name = ?
        ${searchClause.sql}
        ${messageFilterSql}
      ORDER BY mm.received_at ${sortOrder === "oldest" ? "ASC" : "DESC"}, mm.id ${sortOrder === "oldest" ? "ASC" : "DESC"}
      LIMIT ?
      OFFSET ?
    `,
    [
      mailbox.id,
      messageQueryFolder,
      ...searchClause.params,
      pageSize,
      offset,
    ],
  );

  const messages = messageRows.map((row) => ({
    aiSummaryError: row.ai_summary_error,
    aiSummaryResponse: row.ai_summary_response,
    aiSummaryStatus: row.ai_summary_status,
    id: row.id,
    subject: row.subject,
    fromName: row.from_name,
    fromAddress: row.from_address,
    toAddresses: row.to_addresses,
    snippet: row.snippet,
    isRead: Boolean(row.is_read),
    isStarred: Boolean(row.is_starred),
    receivedAt: row.received_at.toISOString(),
    direction: row.direction,
    folderSystemName: row.folder_system_name,
    folderName: row.folder_name,
  }));

  const selectedMessageId =
    options?.messageId &&
    messages.some((message) => message.id === options.messageId)
      ? options.messageId
      : null;

  const selectedMessageRow =
    selectedMessageId === null
      ? null
      : (messageRows.find((message) => message.id === selectedMessageId) ??
        null);

  const selectedMessageRawAttachments = selectedMessageRow?.raw_source
    ? await extractAttachmentsFromRawSource(
        selectedMessageRow.raw_source,
      ).catch(() => [])
    : [];
  const selectedMessageLinkedAttachments = selectedMessageRow
    ? await getLinkedComposeUploadAttachmentsByMessageId(
        selectedMessageRow.id,
      ).catch(() => [])
    : [];
  const selectedMessageAttachments: MailMessageAttachment[] = [
    ...selectedMessageRawAttachments.map((attachment) => ({
      ...attachment,
      externalUrl: null,
    })),
    ...selectedMessageLinkedAttachments,
  ];

  const selectedMessage = selectedMessageRow
    ? {
        attachments: selectedMessageAttachments,
        aiSummaryError: selectedMessageRow.ai_summary_error,
        aiSummaryResponse: selectedMessageRow.ai_summary_response,
        aiSummaryStatus: selectedMessageRow.ai_summary_status,
        id: selectedMessageRow.id,
        subject: selectedMessageRow.subject,
        fromName: selectedMessageRow.from_name,
        fromAddress: selectedMessageRow.from_address,
        toAddresses: selectedMessageRow.to_addresses,
        snippet: selectedMessageRow.snippet,
        isRead: Boolean(selectedMessageRow.is_read),
        isStarred: Boolean(selectedMessageRow.is_starred),
        receivedAt: selectedMessageRow.received_at.toISOString(),
        direction: selectedMessageRow.direction,
        folderSystemName: selectedMessageRow.folder_system_name,
        folderName: selectedMessageRow.folder_name,
        bodyHtml: sanitizeMailboxHtml(
          selectedMessageRow.body_html,
          selectedMessageRow.body_text,
        ),
        bodyHtmlDisplay:
          normalizeStoredMailboxHtml(selectedMessageRow.body_html) ??
          (selectedMessageRow.body_text.trim()
            ? plainTextToHtml(selectedMessageRow.body_text)
            : null),
        bodyText: selectedMessageRow.body_text,
        messageIdHeader: selectedMessageRow.message_id_header,
        rawSource: selectedMessageRow.raw_source,
        transportStatus: selectedMessageRow.transport_status,
        transportResponse: selectedMessageRow.transport_response,
      }
    : null;
  const selectedMessageLogs = selectedMessageId
    ? await getDeliveryLogsByMessageId(selectedMessageId)
    : [];
  const quota = await quotaPromise;

  return {
    mailbox: mailbox.email,
    mailboxQuotaBytes: quota?.limitBytes ?? null,
    mailboxQuotaIsUnlimited: quota?.isUnlimited ?? false,
    mailboxQuotaUsagePercent: quota?.usagePercent ?? null,
    mailboxQuotaUsedBytes: quota?.usedBytes ?? null,
    domain: mailbox.domain,
    lastSyncAt: normalizeDate(mailbox.last_sync_at),
    status: mailbox.status,
    syncError: mailbox.last_sync_error,
    folders,
    currentFolder,
    messageFilter,
    searchField,
    searchQuery,
    sortOrder,
    messageFilterCounts,
    currentPage,
    totalPages,
    totalMessages,
    pageSize,
    messages,
    selectedMessage,
    selectedMessageLogs,
  } satisfies MailAppOverview;
}

export async function createMailboxMessage(
  email: string,
  input: {
    attachmentUploadIds?: number[];
    bcc?: string;
    bodyHtml?: string;
    cc?: string;
    inlineImageUploadIds?: number[];
    onStageChange?: (stage: "sending" | "uploading") => Promise<void> | void;
    onUploadProgress?: (
      snapshot: ComposeUploadFinalizeProgressSnapshot,
    ) => Promise<void> | void;
    rawAttachmentFiles?: Array<{
      buffer: Buffer;
      contentType: string;
      filename: string;
      sizeBytes: number;
    }>;
    senderDisplayName?: string;
    sendIndividually?: boolean;
    to: string;
    subject: string;
    body: string;
    intent: "send" | "draft";
    waitForDelivery?: boolean;
  },
) {
  const user = await getUserByEmail(email);

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

  const senderDisplayName =
    input.senderDisplayName?.trim().slice(0, 191) || user.display_name;

  const to = input.to.trim().toLowerCase();
  const cc = (input.cc ?? "").trim().toLowerCase();
  const bcc = (input.bcc ?? "").trim().toLowerCase();
  const subject = input.subject.trim();
  const htmlBody = (input.bodyHtml ?? "").trim();
  const attachmentUploadIds = (input.attachmentUploadIds ?? []).filter(
    (value) => Number.isInteger(value) && value > 0,
  );
  const rawAttachmentFiles = (input.rawAttachmentFiles ?? []).filter(
    (file) => file.buffer.length > 0,
  );
  const inlineImageUploadIds = (input.inlineImageUploadIds ?? []).filter(
    (value) => Number.isInteger(value) && value > 0,
  );
  const toRecipients = normalizeRecipientList(to);
  const ccRecipients = cc ? normalizeRecipientList(cc) : [];
  const bccRecipients = bcc ? normalizeRecipientList(bcc) : [];
  const shouldSendIndividually =
    input.intent === "send" &&
    input.sendIndividually === true &&
    toRecipients.length > 1;
  const requestedSendCount = shouldSendIndividually ? toRecipients.length : 1;
  const recipients = [
    ...toRecipients,
    ...ccRecipients,
    ...bccRecipients,
  ].filter((recipient, index, array) => array.indexOf(recipient) === index);

  const mailbox = await getLatestMailboxByUserId(user.id);

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

  const body = input.body.trim() || htmlToPlainText(htmlBody);
  let storedBodyHtml =
    sanitizeMailboxHtml(htmlBody, body) ?? plainTextToHtml(body);
  let rawAttachments: ComposeRawAttachment[] = [];
  let finalizedInlineImageUploadIds: number[] = [];
  const hasPendingUploads =
    attachmentUploadIds.length > 0 ||
    inlineImageUploadIds.length > 0 ||
    rawAttachmentFiles.length > 0;
  const hasBodyContent = Boolean(body.trim() || storedBodyHtml?.trim());

  if (
    (input.intent === "send" && recipients.length === 0) ||
    (input.intent === "send" && !subject) ||
    (input.intent === "send" && !hasBodyContent && !hasPendingUploads)
  ) {
    throw new Error("compose-required");
  }

  if (input.intent === "send") {
    await assertMailboxSendAllowed({
      mailboxId: mailbox.id,
      ownerUserId: user.id,
      requestedSendCount,
    });
  }

  if (hasPendingUploads) {
    if (inlineImageUploadIds.length > 0) {
      const finalizedInlineImages =
        await finalizeComposeInlineImagesForUserEmail({
          bodyHtml: storedBodyHtml,
          email,
          inlineImageUploadIds,
        });
      storedBodyHtml = finalizedInlineImages.bodyHtml ?? storedBodyHtml;
      finalizedInlineImageUploadIds = finalizedInlineImages.finalizedUploadIds;
    }

    const rawUploadPayload = await buildComposeRawAttachmentsForUserEmail({
      attachmentUploadIds,
      bodyHtml: storedBodyHtml,
      email,
      inlineImageUploadIds: [],
    });

    rawAttachments = [
      ...rawUploadPayload.attachments,
      ...rawAttachmentFiles.map(
        (file) =>
          ({
            content: file.buffer,
            contentDisposition: "attachment",
            contentId: null,
            contentType: file.contentType || "application/octet-stream",
            filename: file.filename,
          }) satisfies ComposeRawAttachment,
      ),
    ];
    storedBodyHtml = rawUploadPayload.bodyHtml ?? storedBodyHtml;
  }

  if (storedBodyHtml && /\bsrc=(['"])data:image\//i.test(storedBodyHtml)) {
    const finalizedEmbeddedImages =
      await finalizeComposeInlineImagesForUserEmail({
        bodyHtml: storedBodyHtml,
        email,
        inlineImageUploadIds: [],
      });
    storedBodyHtml = finalizedEmbeddedImages.bodyHtml ?? storedBodyHtml;
    finalizedInlineImageUploadIds = [
      ...new Set([
        ...finalizedInlineImageUploadIds,
        ...finalizedEmbeddedImages.finalizedUploadIds,
      ]),
    ];
  }

  const targetFolder = input.intent === "draft" ? "drafts" : "sent";
  const snippet = body.replace(/\s+/g, " ").slice(0, 140);
  const messageIdHeader = createMessageIdHeader(mailbox.domain);
  const visibleRecipients = [
    toRecipients.join(", "),
    ccRecipients.length > 0 ? `참조: ${ccRecipients.join(", ")}` : "",
  ]
    .filter(Boolean)
    .join(" / ");
  const logRecipients = recipients.join(", ");
  const rawSource = buildRawSource({
    attachments: rawAttachments,
    bodyHtml: storedBodyHtml || null,
    bodyText: body,
    ccAddresses: ccRecipients.join(", "),
    messageIdHeader,
    fromName: senderDisplayName,
    fromAddress: mailbox.email,
    toAddresses: toRecipients.join(", "),
    subject,
  });
  const visibleRecipientsLabel = visibleRecipients || toRecipients.join(", ");

  if (input.intent === "send") {
    await input.onStageChange?.("sending");
  }

  if (input.intent === "draft") {
    const credentials = toMailboxCredentials(mailbox);
    let remoteFolderName = "Drafts";
    let remoteUid: number | null = null;
    let transportResponse = "";
    const appendResult = await appendDraftMessage(credentials, rawSource);
    remoteFolderName = appendResult?.destination ?? remoteFolderName;
    remoteUid = appendResult?.uid ?? null;
    transportResponse = appendResult?.uid
      ? "임시보관함에 임시저장을 완료했습니다."
      : "임시보관함에 임시저장을 요청했습니다.";

    const result = await withTransaction(async (connection) => {
      await ensureLocalMailboxFolders(connection, mailbox.id);

      const [folderRows] = await connection.query<
        (RowDataPacket & { id: number; remote_name: string | null })[]
      >(
        "SELECT id, remote_name FROM mailbox_folders WHERE mailbox_id = ? AND system_name = ? LIMIT 1",
        [mailbox.id, targetFolder],
      );

      const folder = folderRows[0];
      const folderId = folder?.id;

      if (!folderId) {
        throw new Error("folder-not-found");
      }

      if (!folder.remote_name || folder.remote_name !== remoteFolderName) {
        await connection.query(
          `
            UPDATE mailbox_folders
            SET remote_name = ?, updated_at = NOW()
            WHERE id = ?
          `,
          [remoteFolderName, folderId],
        );
      }

      const mailboxMessageId = await upsertMailboxMessageRow(connection, {
        bodyHtml: storedBodyHtml || null,
        bodyText: body,
        direction: "draft",
        folderId,
        fromAddress: mailbox.email,
        fromName: senderDisplayName,
        isRead: true,
        isStarred: false,
        mailboxId: mailbox.id,
        messageIdHeader,
        preserveExistingTransport: false,
        rawSource,
        receivedAt: new Date(),
        remoteFlags: "\\Draft",
        remoteFolder: remoteFolderName,
        remoteUid,
        snippet,
        subject,
        toAddresses: visibleRecipientsLabel,
        transportResponse,
        transportStatus: "saved",
      });

      await logMailboxDelivery(connection, {
        mailboxMessageId,
        mailboxId: mailbox.id,
        action: "draft",
        status: "saved",
        transport: "imap",
        sourceAddress: mailbox.email,
        targetAddress: logRecipients,
        subject,
        messageIdHeader,
        responseText: transportResponse,
      });

      await linkComposeUploadIdsToMessageId(
        connection,
        finalizedInlineImageUploadIds,
        mailboxMessageId,
      );

      return { mailboxMessageId };
    });

    await updateMailboxSyncState(mailbox.id, {
      lastSyncAt: new Date(),
      lastSyncError: null,
    });

    return {
      folder: targetFolder,
      messageId: result.mailboxMessageId,
      success: "draft" as const,
    };
  }

  // Validate stored credentials before queueing.
  toMailboxCredentials(mailbox);

  const queuedResult = await withTransaction(async (connection) => {
    await ensureLocalMailboxFolders(connection, mailbox.id);
    await assertMailboxSendAllowed({
      activateCooldownOnBurst: true,
      connection,
      mailboxId: mailbox.id,
      ownerUserId: user.id,
      requestedSendCount,
    });

    const [folderRows] = await connection.query<
      (RowDataPacket & { id: number; remote_name: string | null })[]
    >(
      "SELECT id, remote_name FROM mailbox_folders WHERE mailbox_id = ? AND system_name = 'sent' LIMIT 1",
      [mailbox.id],
    );

    const folderId = folderRows[0]?.id;
    const remoteFolderName = folderRows[0]?.remote_name || "Sent";

    if (!folderId) {
      throw new Error("folder-not-found");
    }

    const sendTargets = buildQueuedSendTargets({
      attachments: rawAttachments,
      bccRecipients,
      body,
      bodyHtml: storedBodyHtml || null,
      ccRecipients,
      fromAddress: mailbox.email,
      fromName: senderDisplayName,
      sendIndividually: shouldSendIndividually,
      subject,
      toRecipients,
    });
    const queuedEntries: Array<{
      envelopeRecipients: string[];
      logRecipients: string;
      mailboxMessageId: number;
      messageIdHeader: string;
      rawSource: string;
      visibleRecipients: string;
    }> = [];

    for (const sendTarget of sendTargets) {
      const mailboxMessageId = await upsertMailboxMessageRow(connection, {
        bodyHtml: storedBodyHtml || null,
        bodyText: body,
        direction: "outbound",
        folderId,
        fromAddress: mailbox.email,
        fromName: senderDisplayName,
        isRead: true,
        isStarred: false,
        mailboxId: mailbox.id,
        messageIdHeader: sendTarget.messageIdHeader,
        preserveExistingTransport: false,
        rawSource: sendTarget.rawSource,
        receivedAt: new Date(),
        remoteFlags: "\\Seen",
        remoteFolder: remoteFolderName,
        remoteUid: null,
        snippet,
        subject,
        toAddresses: sendTarget.visibleRecipients,
        transportResponse: "메일 전송 대기열에 등록되었습니다.",
        transportStatus: "queued",
      });

      await logMailboxDelivery(connection, {
        mailboxMessageId,
        mailboxId: mailbox.id,
        action: "send",
        status: "queued",
        transport: "smtp",
        sourceAddress: mailbox.email,
        targetAddress: sendTarget.logRecipients,
        subject,
        messageIdHeader: sendTarget.messageIdHeader,
        responseText: "메일 전송 대기열에 등록되었습니다.",
      });

      queuedEntries.push({
        envelopeRecipients: sendTarget.envelopeRecipients,
        logRecipients: sendTarget.logRecipients,
        mailboxMessageId,
        messageIdHeader: sendTarget.messageIdHeader,
        rawSource: sendTarget.rawSource,
        visibleRecipients: sendTarget.visibleRecipients,
      });
    }

    if (queuedEntries[0]) {
      await linkComposeUploadIdsToMessageId(
        connection,
        finalizedInlineImageUploadIds,
        queuedEntries[0].mailboxMessageId,
      );
    }

    return { queuedEntries };
  });

  if (input.waitForDelivery) {
    let firstFailure: { errorMessage: string } | null = null;

    for (const queuedEntry of queuedResult.queuedEntries) {
      const deliveryResult = await finalizeQueuedMailboxSend({
        logRecipients: queuedEntry.logRecipients,
        mailbox,
        mailboxMessageId: queuedEntry.mailboxMessageId,
        messageIdHeader: queuedEntry.messageIdHeader,
        rawSource: queuedEntry.rawSource,
        recipients: queuedEntry.envelopeRecipients,
        subject,
        visibleRecipients: queuedEntry.visibleRecipients,
      });

      if (deliveryResult.status === "failed" && !firstFailure) {
        firstFailure = {
          errorMessage: deliveryResult.errorMessage,
        };
      }
    }

    if (firstFailure) {
      throw new Error(firstFailure.errorMessage);
    }
  } else {
    after(async () => {
      for (const queuedEntry of queuedResult.queuedEntries) {
        await finalizeQueuedMailboxSend({
          logRecipients: queuedEntry.logRecipients,
          mailbox,
          mailboxMessageId: queuedEntry.mailboxMessageId,
          messageIdHeader: queuedEntry.messageIdHeader,
          rawSource: queuedEntry.rawSource,
          recipients: queuedEntry.envelopeRecipients,
          subject,
          visibleRecipients: queuedEntry.visibleRecipients,
        });
      }
    });
  }

  await updateMailboxSyncState(mailbox.id, {
    lastSyncAt: new Date(),
    lastSyncError: null,
  });

  return {
    folder: targetFolder,
    messageId:
      queuedResult.queuedEntries.length === 1
        ? (queuedResult.queuedEntries[0]?.mailboxMessageId ?? null)
        : null,
    success: shouldSendIndividually ? "send-individual" : "send",
  };
}

export async function getMailboxRecipientSuggestionsByEmail(
  email: string,
  input: {
    query: string;
    limit?: number;
  },
) {
  const user = await getUserByEmail(email);

  if (!user) {
    return [] satisfies MailRecipientSuggestion[];
  }

  const mailbox = await getLatestMailboxByUserId(user.id);

  if (!mailbox) {
    return [] satisfies MailRecipientSuggestion[];
  }

  const normalizedQuery = normalizeMailSearchQuery(input.query);

  if (!normalizedQuery) {
    return [] satisfies MailRecipientSuggestion[];
  }

  const limit = Math.max(1, Math.min(12, input.limit ?? 8));
  const queryLower = normalizedQuery.toLowerCase();
  const likeQuery = `%${escapeSqlLike(queryLower)}%`;
  const pool = getDbPool();
  const [memberRowsResult, historyRowsResult] = await Promise.all([
    pool.query<MailRecipientDirectoryRow[]>(
      `
        SELECT u.display_name, m.email
        FROM mailboxes m
        INNER JOIN domains d ON d.id = m.domain_id
        INNER JOIN users u ON u.id = m.user_id
        WHERE d.domain = ?

        UNION ALL

        SELECT mtm.display_name, mtm.email
        FROM managed_team_mailboxes mtm
        INNER JOIN domains d ON d.id = mtm.domain_id
        WHERE d.domain = ?
          AND mtm.status = 'active'
      `,
      [mailbox.domain, mailbox.domain],
    ),
    pool.query<MailRecipientSuggestionRow[]>(
      `
        SELECT
          mm.from_name,
          mm.from_address,
          mm.to_addresses,
          mm.received_at
        FROM mailbox_messages mm
        WHERE mm.mailbox_id = ?
          AND (
            LOWER(COALESCE(mm.from_name, '')) LIKE ? ESCAPE '\\\\'
            OR LOWER(mm.from_address) LIKE ? ESCAPE '\\\\'
            OR LOWER(mm.to_addresses) LIKE ? ESCAPE '\\\\'
          )
        ORDER BY mm.received_at DESC, mm.id DESC
        LIMIT 60
      `,
      [mailbox.id, likeQuery, likeQuery, likeQuery],
    ),
  ]);
  const [memberRows] = memberRowsResult;
  const [historyRows] = historyRowsResult;
  const suggestions: MailRecipientSuggestion[] = [];
  const seen = new Set<string>();

  const pushSuggestion = (suggestion: {
    displayName?: string | null;
    email: string;
    kind: MailRecipientSuggestion["kind"];
  }) => {
    const normalizedEmail = suggestion.email.trim().toLowerCase();

    if (!normalizedEmail || seen.has(normalizedEmail)) {
      return;
    }

    seen.add(normalizedEmail);
    suggestions.push({
      displayName: suggestion.displayName?.trim() ?? null,
      email: normalizedEmail,
      kind: suggestion.kind,
      label: formatRecipientSuggestionLabel(
        normalizedEmail,
        suggestion.displayName,
      ),
    });
  };

  for (const row of memberRows) {
    const displayName = row.display_name?.trim() ?? null;
    const memberEmail = row.email.trim().toLowerCase();

    if (!memberEmail) {
      continue;
    }

    if (
      !memberEmail.includes(queryLower) &&
      !(displayName ?? "").toLowerCase().includes(queryLower)
    ) {
      continue;
    }

    pushSuggestion({
      displayName,
      email: memberEmail,
      kind: "member",
    });

    if (suggestions.length >= limit) {
      return suggestions.slice(0, limit);
    }
  }

  for (const row of historyRows) {
    const senderName = row.from_name?.trim() ?? null;
    const senderAddress = row.from_address.trim().toLowerCase();

    if (
      senderAddress &&
      (senderAddress.includes(queryLower) ||
        (senderName ?? "").toLowerCase().includes(queryLower))
    ) {
      pushSuggestion({
        displayName: senderName,
        email: senderAddress,
        kind: "history",
      });
    }

    if (suggestions.length >= limit) {
      break;
    }

    for (const recipient of splitRecipientList(row.to_addresses)) {
      if (!recipient.includes(queryLower)) {
        continue;
      }

      pushSuggestion({
        displayName: null,
        email: recipient,
        kind: "history",
      });

      if (suggestions.length >= limit) {
        break;
      }
    }

    if (suggestions.length >= limit) {
      break;
    }
  }

  return suggestions.slice(0, limit);
}

export async function getMailboxSearchSuggestionsByEmail(
  email: string,
  input: {
    folder?: string;
    query: string;
    limit?: number;
  },
) {
  const user = await getUserByEmail(email);

  if (!user) {
    return [] satisfies MailSearchSuggestion[];
  }

  const mailbox = await getLatestMailboxByUserId(user.id);

  if (!mailbox) {
    return [] satisfies MailSearchSuggestion[];
  }

  const normalizedQuery = normalizeMailSearchQuery(input.query);

  if (!normalizedQuery) {
    return [] satisfies MailSearchSuggestion[];
  }

  const currentFolder = input.folder?.trim() || "inbox";
  const messageQueryFolder = currentFolder === "all" ? "inbox" : currentFolder;
  const searchClause = buildMailboxSearchSql({
    field: "all",
    query: normalizedQuery,
  });
  const [rows] = await getDbPool().query<MailSearchSuggestionRow[]>(
    `
      SELECT
        mm.id,
        mm.subject,
        mm.from_name,
        mm.from_address,
        mm.to_addresses,
        mm.snippet,
        mm.body_text,
        mm.received_at
      FROM mailbox_messages mm
      INNER JOIN mailbox_folders f ON f.id = mm.folder_id
      WHERE mm.mailbox_id = ?
        AND f.system_name = ?
        ${searchClause.sql}
      ORDER BY mm.received_at DESC, mm.id DESC
      LIMIT 40
    `,
    [mailbox.id, messageQueryFolder, ...searchClause.params],
  );

  const limit = Math.max(1, Math.min(10, input.limit ?? 8));
  const suggestions: MailSearchSuggestion[] = [];
  const seen = new Set<string>();
  const queryLower = normalizedQuery.toLowerCase();

  const pushSuggestion = (suggestion: MailSearchSuggestion | null) => {
    if (!suggestion) {
      return;
    }

    const key = `${suggestion.field}:${suggestion.value.toLowerCase()}`;

    if (seen.has(key)) {
      return;
    }

    seen.add(key);
    suggestions.push(suggestion);
  };

  for (const row of rows) {
    const senderName = row.from_name?.trim() ?? "";
    const senderAddress = row.from_address.trim();
    const senderPreview = senderName
      ? `${senderName} <${senderAddress}>`
      : senderAddress;

    if (senderName.toLowerCase().includes(queryLower)) {
      pushSuggestion({
        field: "from",
        messageId: row.id,
        preview: senderPreview,
        receivedAt: row.received_at.toISOString(),
        value: senderName,
      });
    }

    if (senderAddress.toLowerCase().includes(queryLower)) {
      pushSuggestion({
        field: "from",
        messageId: row.id,
        preview: senderPreview,
        receivedAt: row.received_at.toISOString(),
        value: senderAddress,
      });
    }

    for (const recipient of splitRecipientList(row.to_addresses)) {
      if (!recipient.includes(queryLower)) {
        continue;
      }

      pushSuggestion({
        field: "to",
        messageId: row.id,
        preview: row.to_addresses,
        receivedAt: row.received_at.toISOString(),
        value: recipient,
      });
    }

    const subject = row.subject.trim();

    if (subject.toLowerCase().includes(queryLower)) {
      pushSuggestion({
        field: "subject",
        messageId: row.id,
        preview: subject,
        receivedAt: row.received_at.toISOString(),
        value: subject,
      });
    }

    const bodySource = `${row.snippet} ${row.body_text}`.trim();

    if (bodySource.toLowerCase().includes(queryLower)) {
      const matchIndex = bodySource.toLowerCase().indexOf(queryLower);
      const preview = bodySource
        .slice(Math.max(0, matchIndex - 24), matchIndex + 56)
        .trim();

      pushSuggestion({
        field: "body",
        messageId: row.id,
        preview,
        receivedAt: row.received_at.toISOString(),
        value: normalizedQuery,
      });
    }

    if (suggestions.length >= limit) {
      break;
    }
  }

  return suggestions.slice(0, limit);
}