import "server-only";

import { randomBytes, randomUUID, scryptSync } from "crypto";
import type { PoolConnection, RowDataPacket } from "mysql2/promise";
import { ensureOfficialMailSchema, getDbPool } from "@/lib/db";
import { plainTextToHtml } from "@/lib/mail-html";
import {
  appendSentMessage,
  decryptMailboxPassword,
  encryptMailboxPassword,
  extractAttachmentsFromRawSource,
  getAttachmentPayloadFromRawSource,
  markRemoteMessageAsSeen,
  sendSmtpMessage,
  syncRemoteMailbox,
  type RemoteMailboxAttachmentPayload,
  type RemoteMailboxCredentials,
  type RemoteMailboxMessage,
} from "@/lib/mailbox-remote";
import {
  deleteMailcowMailboxAccount,
  ensureMailcowMailboxAccount,
  getMailcowPostfixLogs,
} from "@/lib/mailcow";
import { getMailWorkspaceUrl } from "@/lib/mail-urls";
import { hasActiveGrowthPlanByOwnerEmail } from "@/lib/toss-pay";

const LOCAL_PART_PATTERN = /^(?=.{1,64}$)[a-z0-9](?:[a-z0-9._-]*[a-z0-9])?$/;
const EMAIL_PATTERN = /^[^\s@]+@[^\s@]+\.[^\s@]+$/i;
const AI_BRIDGE_TOKEN_PATTERN = /\[AI-BRIDGE:([A-Z0-9-]{8,})\]/i;
const MESSAGE_ID_HEADER_PATTERN = /<[^<>\s]+@[^<>\s]+>/g;
const DEFAULT_MAIL_AI_LLM_MODEL = "qwen2.5-vl-32b-instruct";
const MAIL_AI_LLM_TIMEOUT_MS = 45_000;
const MAIL_AI_LLM_SOURCE_MAX_LENGTH = 7000;
const MAIL_AI_STALE_SENDING_RETRY_MS = 5 * 60 * 1000;
const MAIL_AI_DELIVERY_LOG_POLL_ATTEMPTS = 4;
const MAIL_AI_DELIVERY_LOG_POLL_DELAY_MS = 2000;
const MAIL_QUEUE_ID_PATTERN = /\bqueued as\s+([A-F0-9]+)\b/i;
const MAIL_AI_RELAY_LOGO_URL = process.env.MAIL_AI_RELAY_LOGO_URL?.trim() || null;
const LOCAL_MAILBOX_FOLDERS = [
  { systemName: "inbox", displayName: "받은메일" },
  { systemName: "sent", displayName: "보낸메일" },
  { systemName: "drafts", displayName: "임시보관함" },
  { systemName: "spam", displayName: "스팸함" },
  { systemName: "trash", displayName: "휴지통" },
] as const;

let cachedLlmAccessToken: { expiresAt: number; token: string } | null = null;

export type ManagedMailboxMember = {
  id: number;
  email: string;
  localPart: string;
  displayName: string;
  status: "active" | "disabled";
  createdAt: string;
  updatedAt: string;
};

export type MailAiAssistOverview = {
  assistantMailboxEmail: string;
  enabled: boolean;
  lastRelayedAt: string | null;
  lastSummarizedAt: string | null;
  notificationEmail: string | null;
  relayedCount: number;
  representativeMailbox: string | null;
  sentSummaryCount: number;
};

type OwnerContext = {
  companyName: string;
  displayName: string;
  domain: string;
  domainId: number;
  mailboxEmail: string;
  mailboxId: number;
  mailboxLocalPart: string;
  ownerUserId: number;
  passwordCiphertext: string | null;
};

type OwnerContextRow = RowDataPacket & {
  company_name: string;
  display_name: string;
  domain: string;
  domain_id: number;
  email: string;
  local_part: string;
  mailbox_id: number;
  owner_user_id: number;
  password_ciphertext: string | null;
};

type ManagedMemberRow = RowDataPacket & {
  created_at: Date;
  display_name: string;
  email: string;
  id: number;
  local_part: string;
  password_ciphertext: string;
  status: "active" | "disabled";
  updated_at: Date;
};

type AiMailboxAccess = {
  displayName: string;
  domain: string;
  email: string;
  localPart: string;
  password: string;
};

type StoredAiMailboxRow = RowDataPacket & {
  display_name: string | null;
  domain: string;
  email: string;
  local_part: string;
  password_ciphertext: string;
};

type AiAssistSettingsRow = RowDataPacket & {
  assistant_mailbox_email: string;
  baseline_message_id: number | null;
  enabled: number;
  id: number;
  last_relayed_at: Date | null;
  last_summarized_at: Date | null;
  mailbox_id: number;
  notification_email: string | null;
  owner_user_id: number;
};

type AiAssistCountsRow = RowDataPacket & {
  last_relayed_at: Date | null;
  last_summarized_at: Date | null;
  relayed_count: number | null;
  sent_summary_count: number | null;
};

type AiAssistEnabledOwnerRow = RowDataPacket & {
  owner_email: string;
};

export type MailAiReplyRelayRunSummary = {
  failed: number;
  owners: number;
  relayed: number;
  skipped: number;
};

export type MailAiAssistOwnerEmail = {
  ownerEmail: string;
};

type InboxCandidateMessageRow = RowDataPacket & {
  body_html: string | null;
  body_text: string;
  from_address: string;
  from_name: string | null;
  id: number;
  message_id_header: string | null;
  raw_source: string | null;
  received_at: Date;
  snippet: string;
  subject: string;
  thread_id: number | null;
  thread_status: "failed" | "sending" | "sent" | null;
};

type AiThreadRow = RowDataPacket & {
  bridge_token: string;
  id: number;
  mailbox_id: number;
  notification_email: string;
  original_sender_email: string;
  original_sender_name: string | null;
  original_subject: string;
  owner_user_id: number;
  source_message_id: number;
  summary_status: "failed" | "sending" | "sent";
  summary_transport_response?: string | null;
  updated_at: Date;
};

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

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

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

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

function delay(ms: number) {
  return new Promise((resolve) => setTimeout(resolve, ms));
}

function assertEmail(value: string, errorCode: string) {
  if (!EMAIL_PATTERN.test(value)) {
    throw new Error(errorCode);
  }
}

function assertLocalPart(value: string) {
  if (!LOCAL_PART_PATTERN.test(value)) {
    throw new Error("invalid-local-part");
  }
}

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 escapeHtml(value: string) {
  return value
    .replace(/&/g, "&amp;")
    .replace(/</g, "&lt;")
    .replace(/>/g, "&gt;")
    .replace(/"/g, "&quot;")
    .replace(/'/g, "&#39;");
}

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 || !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 encodeBase64Lines(value: string) {
  return Buffer.from(value, "utf8").toString("base64").replace(/(.{76})/g, "$1\r\n");
}

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 encodeBase64BufferLines(buffer: Buffer) {
  return buffer.toString("base64").replace(/(.{76})/g, "$1\r\n");
}

type AiRelayAttachment = RemoteMailboxAttachmentPayload & {
  relayContentId: string | null;
};

function isImageAttachment(attachment: Pick<RemoteMailboxAttachmentPayload, "contentType">) {
  return attachment.contentType.toLowerCase().startsWith("image/");
}

function normalizeContentId(value: string | null) {
  return value?.replace(/^<|>$/g, "").trim() || null;
}

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

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

  const dispositionParts: string[] = [disposition];
  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.relayContentId ? `Content-ID: <${attachment.relayContentId}>` : null,
    "",
    encodeBase64BufferLines(attachment.content),
  ].filter((line): line is string => line !== null);
}

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

function buildRawSource(input: {
  attachments?: AiRelayAttachment[];
  bodyHtml?: string | null;
  bodyText: string;
  fromAddress: string;
  fromName: string | null;
  messageIdHeader: string;
  subject: string;
  toAddresses: string;
}) {
  const commonHeaders = [
    `Message-ID: ${input.messageIdHeader}`,
    `Date: ${new Date().toUTCString()}`,
    `From: ${formatMailboxHeader(input.fromName, input.fromAddress)}`,
    `To: ${escapeHeaderValue(input.toAddresses)}`,
    `Subject: ${encodeMimeHeaderWord(input.subject)}`,
    "MIME-Version: 1.0",
  ];
  const normalizedText = input.bodyText.replace(/\r?\n/g, "\r\n");
  const attachments = input.attachments ?? [];

  if (!input.bodyHtml?.trim() && attachments.length === 0) {
    return [
      ...commonHeaders,
      "Content-Type: text/plain; charset=UTF-8",
      "Content-Transfer-Encoding: base64",
      "",
      encodeBase64Lines(normalizedText),
    ].join("\r\n");
  }

  const alternativeBoundary = `----=_OfficialMail_Alt_${randomUUID()}`;
  const normalizedHtml = input.bodyHtml?.trim()
    ? input.bodyHtml.replace(/\r?\n/g, "\r\n")
    : plainTextToHtml(input.bodyText).replace(/\r?\n/g, "\r\n");
  const bodyPartLines = [
    `Content-Type: multipart/alternative; boundary="${alternativeBoundary}"`,
    "",
    `--${alternativeBoundary}`,
    "Content-Type: text/plain; charset=UTF-8",
    "Content-Transfer-Encoding: base64",
    "",
    encodeBase64Lines(normalizedText),
    "",
    `--${alternativeBoundary}`,
    "Content-Type: text/html; charset=UTF-8",
    "Content-Transfer-Encoding: base64",
    "",
    encodeBase64Lines(normalizedHtml),
    "",
    `--${alternativeBoundary}--`,
  ];

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

  const mixedBoundary = `----=_OfficialMail_Mixed_${randomUUID()}`;
  const relatedBoundary = `----=_OfficialMail_Related_${randomUUID()}`;
  const inlineAttachments = attachments.filter((attachment) => isImageAttachment(attachment));
  const regularAttachments = attachments.filter((attachment) => !isImageAttachment(attachment));
  const lines = [
    ...commonHeaders,
    `Content-Type: multipart/mixed; boundary="${mixedBoundary}"`,
    "",
    `--${mixedBoundary}`,
    `Content-Type: multipart/related; boundary="${relatedBoundary}"`,
    "",
    `--${relatedBoundary}`,
    ...bodyPartLines,
    "",
  ];

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

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

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

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

function getAiMailboxConfig() {
  const email = normalizeEmail(
    process.env.SYSTEM_AI_MAILBOX_EMAIL ?? "ai@officialsite.kr",
  );
  const password = (
    process.env.SYSTEM_AI_MAILBOX_PASSWORD ??
    process.env.MAILBOX_CREDENTIAL_SECRET ??
    process.env.DB_PASSWORD ??
    ""
  ).trim();
  const displayName =
    (process.env.SYSTEM_AI_MAILBOX_NAME ?? "오피셜메일 AI 어시스트").trim() ||
    "오피셜메일 AI 어시스트";

  if (!email || !password || !email.includes("@")) {
    throw new Error("ai-assist-config-missing");
  }

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

  return {
    displayName,
    domain,
    email,
    localPart,
    password,
  } satisfies AiMailboxAccess;
}

async function findStoredAiMailboxAccess(email: string) {
  const [rows] = await getDbPool().query<StoredAiMailboxRow[]>(
    `
      SELECT
        u.display_name,
        d.domain,
        m.email,
        m.local_part,
        m.password_ciphertext
      FROM mailboxes m
      INNER JOIN domains d ON d.id = m.domain_id
      INNER JOIN users u ON u.id = m.user_id
      WHERE LOWER(m.email) = LOWER(?)
        AND m.password_ciphertext IS NOT NULL

      UNION ALL

      SELECT
        mtm.display_name,
        d.domain,
        mtm.email,
        mtm.local_part,
        mtm.password_ciphertext
      FROM managed_team_mailboxes mtm
      INNER JOIN domains d ON d.id = mtm.domain_id
      WHERE LOWER(mtm.email) = LOWER(?)
        AND mtm.status = 'active'
      LIMIT 1
    `,
    [email, email],
  );
  const row = rows[0];

  if (!row) {
    return null;
  }

  return {
    displayName: row.display_name?.trim() || getAiMailboxConfig().displayName,
    domain: row.domain,
    email: normalizeEmail(row.email),
    localPart: row.local_part,
    password: decryptMailboxPassword(row.password_ciphertext),
  } satisfies AiMailboxAccess;
}

function toRemoteMailboxCredentials(input: Pick<AiMailboxAccess, "email" | "password">) {
  return {
    email: input.email,
    password: input.password,
  } satisfies RemoteMailboxCredentials;
}

function createBridgeToken() {
  return randomUUID().replace(/-/g, "").slice(0, 12).toUpperCase();
}

function buildForwardSourceText(input: {
  bodyHtml: string | null;
  bodyText: string;
  snippet: string;
}) {
  const sourceText = input.bodyText.trim() || (input.bodyHtml ? htmlToPlainText(input.bodyHtml) : "") || input.snippet.trim();

  return sourceText
    .replace(/\r/g, "")
    .split(/\n+/)
    .map((line) => line.trim())
    .filter((line) => line.length >= 2)
    .join("\n")
    .trim();
}

function trimTrailingSlash(value: string) {
  return value.replace(/\/+$/, "");
}

function getMailAiLlmConfig() {
  const rawBaseUrl = (
    process.env.MAIL_AI_LLM_GATEWAY_BASE_URL ??
    process.env.EXTERNAL_LLM_GATEWAY_BASE_URL ??
    process.env.EXTERNAL_LLM_BASE_URL ??
    ""
  ).trim();
  const model = (
    process.env.MAIL_AI_LLM_MODEL ??
    process.env.EXTERNAL_LLM_UPSTREAM_MODEL ??
    DEFAULT_MAIL_AI_LLM_MODEL
  ).trim();
  const apiKey = (
    process.env.MAIL_AI_LLM_API_KEY ??
    process.env.EXTERNAL_LLM_API_KEY ??
    ""
  ).trim();
  const secretKey = (
    process.env.MAIL_AI_LLM_SECRET_KEY ??
    process.env.EXTERNAL_LLM_SECRET_KEY ??
    ""
  ).trim();
  const accessToken = (
    process.env.MAIL_AI_LLM_ACCESS_TOKEN ??
    process.env.EXTERNAL_LLM_ACCESS_TOKEN ??
    ""
  ).trim();

  if (!rawBaseUrl) {
    return null;
  }

  return {
    accessToken,
    apiKey,
    baseUrl: trimTrailingSlash(rawBaseUrl),
    model,
    secretKey,
  };
}

async function fetchJsonWithTimeout<T>(
  url: string,
  init: RequestInit,
  timeoutMs = MAIL_AI_LLM_TIMEOUT_MS,
) {
  const controller = new AbortController();
  const timeout = setTimeout(() => controller.abort(), timeoutMs);

  try {
    const response = await fetch(url, {
      ...init,
      signal: controller.signal,
    });
    const payload = (await response.json().catch(() => null)) as T | null;

    if (!response.ok) {
      const detail =
        payload && typeof payload === "object" && "detail" in payload
          ? String(payload.detail)
          : response.statusText;
      throw new Error(`llm-http-${response.status}:${detail}`);
    }

    if (!payload) {
      throw new Error("llm-empty-response");
    }

    return payload;
  } finally {
    clearTimeout(timeout);
  }
}

async function getMailAiLlmAccessToken(config: NonNullable<ReturnType<typeof getMailAiLlmConfig>>) {
  if (config.accessToken) {
    return config.accessToken;
  }

  if (cachedLlmAccessToken && cachedLlmAccessToken.expiresAt > Date.now() + 30_000) {
    return cachedLlmAccessToken.token;
  }

  if (!config.apiKey || !config.secretKey) {
    throw new Error("mail-ai-llm-credentials-missing");
  }

  const payload = await fetchJsonWithTimeout<{
    access_token?: string;
    expires_in?: number;
    token_type?: string;
  }>(`${config.baseUrl}/token`, {
    body: JSON.stringify({
      api_key: config.apiKey,
      secret_key: config.secretKey,
      user_label: "official-mail-ai-summary",
    }),
    headers: {
      "Content-Type": "application/json",
    },
    method: "POST",
  });
  const token = payload.access_token?.trim();

  if (!token) {
    throw new Error("mail-ai-llm-token-missing");
  }

  cachedLlmAccessToken = {
    expiresAt: Date.now() + Math.max(60, payload.expires_in ?? 3600) * 1000,
    token,
  };

  return token;
}

function normalizeLlmSummaryText(value: string) {
  return value
    .replace(/\r/g, "")
    .split("\n")
    .map((line) => line.trimEnd())
    .filter((line, index, lines) => !(line.trim() === "" && lines[index - 1]?.trim() === ""))
    .join("\n")
    .trim()
    .slice(0, 2200);
}

function stripMarkdownPrefix(value: string) {
  return value
    .replace(/^#{1,6}\s*/, "")
    .replace(/^[-*•]\s*/, "")
    .replace(/^\d+\.\s*/, "")
    .replace(/\*\*/g, "")
    .trim();
}

function isEmptySummarySectionTitle(value: string) {
  const normalized = stripMarkdownPrefix(value).replace(/\s+/g, "");

  return ["요약", "핵심요약", "확인할사항", "추천답장방향"].includes(normalized);
}

function removeEmptySummarySections(summaryText: string) {
  return summaryText
    .replace(/\r/g, "")
    .split(/\n{2,}/)
    .map((block) => block.trim())
    .filter(Boolean)
    .filter((block) => {
      const lines = block
        .split("\n")
        .map((line) => stripMarkdownPrefix(line.trim()))
        .filter(Boolean);

      if (lines.length !== 1) {
        return true;
      }

      return !isEmptySummarySectionTitle(lines[0] ?? "");
    })
    .join("\n\n")
    .trim();
}

function normalizeSummaryDisplayText(summaryText: string) {
  const normalized = summaryText
    .replace(/\r/g, "")
    .split("\n")
    .map((line) => stripMarkdownPrefix(line.trimEnd()))
    .join("\n")
    .replace(/\n{3,}/g, "\n\n")
    .trim();

  return removeEmptySummarySections(normalized);
}

async function generateMailAiSummary(input: {
  companyName: string;
  message: InboxCandidateMessageRow;
  representativeMailbox: string;
}) {
  const config = getMailAiLlmConfig();

  if (!config) {
    throw new Error("mail-ai-llm-config-missing");
  }

  const sourceText = buildForwardSourceText({
    bodyHtml: input.message.body_html,
    bodyText: input.message.body_text,
    snippet: input.message.snippet,
  }).slice(0, MAIL_AI_LLM_SOURCE_MAX_LENGTH);

  if (!sourceText.trim()) {
    throw new Error("mail-ai-summary-source-empty");
  }

  const accessToken = await getMailAiLlmAccessToken(config);
  const payload = await fetchJsonWithTimeout<{
    choices?: Array<{
      message?: {
        content?: string;
      };
    }>;
  }>(`${config.baseUrl}/v1/chat/completions`, {
    body: JSON.stringify({
      messages: [
        {
          role: "system",
          content:
            "너는 한국어 비즈니스 메일 요약 비서다. 과장하지 말고, 원문에 없는 내용을 만들지 말고, 업무자가 바로 처리할 수 있게 간결하게 정리한다.",
        },
        {
          role: "user",
          content: [
            `회사명: ${input.companyName}`,
            `대표 메일함: ${input.representativeMailbox}`,
            `보낸 사람: ${
              input.message.from_name
                ? `${input.message.from_name} <${input.message.from_address}>`
                : input.message.from_address
            }`,
            `제목: ${input.message.subject || "(제목 없음)"}`,
            "",
            "아래 메일을 한국어로 요약해줘.",
            "마크다운 코드블록이나 HTML 태그 없이 아래 섹션명과 짧은 문장만 사용해.",
            "출력 형식:",
            "핵심 요약",
            "- ...",
            "- ...",
            "",
            "확인할 사항",
            "- ...",
            "",
            "추천 답장 방향",
            "- ...",
            "",
            "메일 본문:",
            sourceText,
          ].join("\n"),
        },
      ],
      model: config.model,
      temperature: 0.2,
    }),
    headers: {
      Authorization: `Bearer ${accessToken}`,
      "Content-Type": "application/json",
    },
    method: "POST",
  });
  const content = payload.choices?.[0]?.message?.content;
  const summary = content ? normalizeLlmSummaryText(content) : "";

  if (!summary) {
    throw new Error("mail-ai-summary-empty");
  }

  return summary;
}

async function loadRelayAttachments(message: InboxCandidateMessageRow) {
  if (!message.raw_source) {
    return [] satisfies AiRelayAttachment[];
  }

  const metas = await extractAttachmentsFromRawSource(message.raw_source).catch(() => []);
  const attachments: AiRelayAttachment[] = [];

  for (const meta of metas) {
    const payload = await getAttachmentPayloadFromRawSource(message.raw_source, meta.index).catch(() => null);

    if (!payload) {
      continue;
    }

    attachments.push({
      ...payload,
      relayContentId: isImageAttachment(payload)
        ? normalizeContentId(payload.contentId) ?? `om-image-${message.id}-${payload.index}-${randomUUID()}@officialmail`
        : null,
    });
  }

  return attachments;
}

function formatSummaryHtml(summaryText: string) {
  const blocks = removeEmptySummarySections(summaryText)
    .replace(/\r/g, "")
    .split(/\n{2,}/)
    .map((block) => block.trim())
    .filter(Boolean);

  if (blocks.length === 0) {
    return "";
  }

  return blocks
    .map((block) => {
      const [rawTitle, ...rawLines] = block.split("\n").map((line) => line.trim()).filter(Boolean);
      const title = stripMarkdownPrefix(rawTitle ?? "");
      const lines = rawLines.map(stripMarkdownPrefix).filter(Boolean);
      const bodyLines = lines.length > 0 ? lines : [title];
      const heading = lines.length > 0 ? title : "요약";

      return [
        `<section style="margin-top:18px;">`,
        `<h2 style="margin:0 0 10px;font-size:15px;line-height:1.4;color:#16233c;">${escapeHtml(heading)}</h2>`,
        `<div style="margin:0;color:#34435c;font-size:14px;line-height:1.75;">`,
        bodyLines.map((line) => `<p style="margin:0 0 6px;">${escapeHtml(line)}</p>`).join(""),
        `</div>`,
        `</section>`,
      ].join("");
    })
    .join("");
}

function buildInlineImageHtml(attachments: AiRelayAttachment[]) {
  const images = attachments.filter((attachment) => isImageAttachment(attachment) && attachment.relayContentId);

  if (images.length === 0) {
    return "";
  }

  return [
    `<section style="margin-top:22px;">`,
    `<h2 style="margin:0 0 12px;font-size:15px;line-height:1.4;color:#16233c;">원본 이미지</h2>`,
    `<div style="display:block;">`,
    images
      .map(
        (image) =>
          `<div style="margin:0 0 14px;"><img src="cid:${escapeHtml(image.relayContentId ?? "")}" alt="${escapeHtml(image.filename)}" style="display:block;max-width:100%;height:auto;border-radius:14px;border:1px solid #e6edf8;" /></div>`,
      )
      .join(""),
    `</div>`,
    `</section>`,
  ].join("");
}

function buildAttachmentNoticeHtml(attachments: AiRelayAttachment[]) {
  const files = attachments.filter((attachment) => !isImageAttachment(attachment));

  if (files.length === 0) {
    return "";
  }

  return [
    `<section style="margin-top:22px;padding-top:16px;border-top:1px solid #edf2fb;">`,
    `<h2 style="margin:0 0 10px;font-size:15px;line-height:1.4;color:#16233c;">첨부파일</h2>`,
    `<p style="margin:0;color:#53647f;font-size:14px;line-height:1.7;">원본 메일의 첨부파일 ${files.length}개를 이 메일에 그대로 첨부했습니다.</p>`,
    `</section>`,
  ].join("");
}

function buildOriginalMessageText(message: InboxCandidateMessageRow) {
  return buildForwardSourceText({
    bodyHtml: message.body_html,
    bodyText: message.body_text,
    snippet: message.snippet,
  });
}

function buildOriginalMessageHtml(message: InboxCandidateMessageRow) {
  const sourceHtml = message.body_html?.trim();

  if (!sourceHtml) {
    const originalText = buildOriginalMessageText(message);
    return originalText ? plainTextToHtml(originalText) : "";
  }

  const styleHtml = Array.from(sourceHtml.matchAll(/<style\b[^>]*>[\s\S]*?<\/style>/gi))
    .map((match) => match[0])
    .join("");
  const bodyMatch = sourceHtml.match(/<body\b[^>]*>([\s\S]*?)<\/body>/i);
  const bodyHtml = bodyMatch?.[1]?.trim() || sourceHtml;

  return `${styleHtml}${bodyHtml}`
    .replace(/<script\b[^>]*>[\s\S]*?<\/script>/gi, "")
    .replace(/<iframe\b[^>]*>[\s\S]*?<\/iframe>/gi, "")
    .replace(/<object\b[^>]*>[\s\S]*?<\/object>/gi, "")
    .replace(/<embed\b[^>]*>[\s\S]*?<\/embed>/gi, "");
}

function buildOriginalMessageUrl(messageId: number) {
  const params = new URLSearchParams({
    detail: "body",
    folder: "all",
    message: String(messageId),
  });

  return `${getMailWorkspaceUrl()}?${params.toString()}`;
}

function buildAiSummaryTextBody(input: {
  attachmentCount: number;
  companyName: string;
  message: InboxCandidateMessageRow;
  notificationEmail: string;
  originalText?: string;
  representativeMailbox: string;
  summaryText: string;
}) {
  return [
    `${input.companyName} 대표 메일로 새 메일이 도착했습니다.`,
    "",
    `보낸 사람: ${input.message.from_name ? `${input.message.from_name} <${input.message.from_address}>` : input.message.from_address}`,
    `받는 사람: ${input.representativeMailbox}`,
    `알림 수신 주소: ${input.notificationEmail}`,
    `제목: ${input.message.subject}`,
    "",
    input.summaryText,
    input.originalText ? "" : "",
    input.originalText ? "원본 메일" : "",
    input.originalText ?? "",
    "",
    input.attachmentCount > 0 ? `원본 첨부 ${input.attachmentCount}개를 함께 전달했습니다.` : "",
    input.attachmentCount > 0 ? "" : "",
    `이 메일에 답장하면 ${input.message.from_address} 발신자에게 그대로 전달됩니다.`,
    `메일원본 확인: ${buildOriginalMessageUrl(input.message.id)}`,
  ].filter((line, index, lines) => !(line === "" && lines[index - 1] === "")).join("\n");
}

function buildAiSummaryHtmlBody(input: {
  attachments: AiRelayAttachment[];
  companyName: string;
  message: InboxCandidateMessageRow;
  notificationEmail: string;
  originalHtml?: string;
  representativeMailbox: string;
  summaryText: string;
}) {
  const sender = input.message.from_name
    ? `${input.message.from_name} <${input.message.from_address}>`
    : input.message.from_address;
  const originalMessageUrl = buildOriginalMessageUrl(input.message.id);
  const logoHtml = MAIL_AI_RELAY_LOGO_URL
    ? [
        `<div style="margin:0 0 18px;display:inline-flex;align-items:center;justify-content:flex-start;vertical-align:middle;color:#2f6df6;font-size:22px;font-weight:800;letter-spacing:-0.08em;line-height:28px;">`,
        `<img src="${escapeHtml(MAIL_AI_RELAY_LOGO_URL)}" width="28" height="28" alt="" style="display:inline-block;width:28px;height:28px;margin:0 10px 0 0;border:0;outline:none;text-decoration:none;vertical-align:middle;flex:0 0 28px;" />`,
        `<span style="display:inline-block;line-height:28px;vertical-align:middle;">오피셜메일</span>`,
        `</div>`,
      ].join("")
    : `<div style="margin:0 0 18px;display:inline-block;color:#2f6df6;font-size:22px;font-weight:800;letter-spacing:-0.08em;line-height:1;">✉ 오피셜메일</div>`;

  return [
    `<!doctype html>`,
    `<html><body style="margin:0;padding:0;background:#f5f8ff;font-family:Arial,'Apple SD Gothic Neo','Malgun Gothic',sans-serif;color:#16233c;">`,
    `<div style="max-width:720px;margin:0 auto;padding:28px 16px;">`,
    `<div style="border-radius:10px;background:#ffffff;padding:28px;border:1px solid #e4ecfb;">`,
    logoHtml,
    `<h1 style="margin:0 0 18px;font-size:22px;line-height:1.35;letter-spacing:-0.03em;color:#16233c;">${escapeHtml(input.companyName)} 대표 메일로 새 메일이 도착했습니다.</h1>`,
    `<div style="margin:0 0 20px;padding:16px;border-radius:10px;background:#f7faff;color:#43536f;font-size:14px;line-height:1.7;">`,
    `<div><strong style="color:#16233c;">보낸 사람</strong> ${escapeHtml(sender)}</div>`,
    `<div><strong style="color:#16233c;">받는 사람</strong> ${escapeHtml(input.representativeMailbox)}</div>`,
    `<div><strong style="color:#16233c;">제목</strong> ${escapeHtml(input.message.subject || "(제목 없음)")}</div>`,
    `</div>`,
    formatSummaryHtml(input.summaryText),
    input.originalHtml
      ? [
          `<section style="margin-top:22px;padding-top:18px;border-top:1px solid #edf2fb;">`,
          `<h2 style="margin:0 0 12px;font-size:15px;line-height:1.4;color:#16233c;">원본 메일</h2>`,
          `<div style="color:#34435c;font-size:14px;line-height:1.7;">${input.originalHtml}</div>`,
          `</section>`,
        ].join("")
      : "",
    buildInlineImageHtml(input.attachments),
    buildAttachmentNoticeHtml(input.attachments),
    `<div style="margin-top:24px;padding:16px;border-radius:10px;background:#eef4ff;color:#2f4c83;font-size:13px;line-height:1.7;">`,
    `이 메일에 답장하면 ${escapeHtml(input.message.from_address)} 발신자에게 그대로 전달됩니다.`,
    `<div style="margin-top:14px;">`,
    `<a href="${escapeHtml(originalMessageUrl)}" target="_blank" rel="noopener noreferrer" style="display:inline-block;border-radius:10px;background:#2f6df6;color:#ffffff;text-decoration:none;font-size:14px;font-weight:700;line-height:1;padding:13px 16px;">메일원본 확인</a>`,
    `</div>`,
    `</div>`,
    `</div>`,
    `</div>`,
    `</body></html>`,
  ].join("");
}

function extractReplyText(message: RemoteMailboxMessage) {
  const rawText = message.bodyText.trim() || (message.bodyHtml ? htmlToPlainText(message.bodyHtml) : "");

  if (!rawText) {
    return "";
  }

  const result: string[] = [];

  for (const line of rawText.replace(/\r/g, "").split("\n")) {
    const trimmed = line.trimEnd();
    const compact = trimmed.trim();

    if (
      /^>/.test(compact) ||
      /^On .+wrote:$/i.test(compact) ||
      /^\d{4}년 .*님이 작성:?$/i.test(compact) ||
      /^-+\s*원본 메일\s*-+$/i.test(compact) ||
      /^(From|To|Cc|Subject):/i.test(compact) ||
      /^(보낸사람|받는사람|참조|제목):/i.test(compact)
    ) {
      break;
    }

    result.push(trimmed);
  }

  return result.join("\n").trim();
}

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();
  }
}

async function getOwnerContextByEmail(
  ownerEmail: string,
  options?: {
    domainId?: number;
  },
) {
  await ensureOfficialMailSchema();
  const requestedDomainId = options?.domainId;

  if (requestedDomainId !== undefined && (!Number.isInteger(requestedDomainId) || requestedDomainId <= 0)) {
    throw new Error("owner-context-not-found");
  }

  const [rows] = await getDbPool().query<OwnerContextRow[]>(
    `
      SELECT
        u.id AS owner_user_id,
        u.company_name,
        u.display_name,
        d.id AS domain_id,
        d.domain,
        m.id AS mailbox_id,
        m.local_part,
        m.email,
        m.password_ciphertext
      FROM users u
      INNER JOIN mailboxes m ON m.user_id = u.id
      INNER JOIN domains d ON d.id = m.domain_id
      WHERE u.email = ?
        AND (? IS NULL OR d.id = ?)
      ORDER BY CASE WHEN m.email = ? THEN 0 ELSE 1 END, m.id DESC
      LIMIT 1
    `,
    [
      normalizeEmail(ownerEmail),
      requestedDomainId ?? null,
      requestedDomainId ?? null,
      normalizeEmail(ownerEmail),
    ],
  );

  const row = rows[0];

  if (!row) {
    throw new Error("owner-context-not-found");
  }

  return {
    companyName: row.company_name,
    displayName: row.display_name,
    domain: row.domain,
    domainId: row.domain_id,
    mailboxEmail: row.email,
    mailboxId: row.mailbox_id,
    mailboxLocalPart: row.local_part,
    ownerUserId: row.owner_user_id,
    passwordCiphertext: row.password_ciphertext,
  } satisfies OwnerContext;
}

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

async function upsertManagedMemberAppAccount(
  connection: PoolConnection,
  input: {
    displayName: string;
    email: string;
    localPart: string;
    owner: OwnerContext;
    password: string;
  },
) {
  const passwordHash = hashPassword(input.password);
  const passwordCiphertext = encryptMailboxPassword(input.password);

  await connection.query(
    `
      INSERT INTO users (
        email,
        company_name,
        display_name,
        password_hash,
        mail_configured
      ) VALUES (?, ?, ?, ?, 1)
      ON DUPLICATE KEY UPDATE
        company_name = VALUES(company_name),
        display_name = VALUES(display_name),
        password_hash = VALUES(password_hash),
        mail_configured = 1,
        updated_at = NOW()
    `,
    [
      input.email,
      input.owner.companyName,
      input.displayName,
      passwordHash,
    ],
  );

  const [userRows] = await connection.query<(RowDataPacket & { id: number })[]>(
    "SELECT id FROM users WHERE email = ? LIMIT 1",
    [input.email],
  );
  const memberUserId = userRows[0]?.id;

  if (!memberUserId) {
    throw new Error("member-user-create-failed");
  }

  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, 'active')
      ON DUPLICATE KEY UPDATE
        user_id = VALUES(user_id),
        domain_id = VALUES(domain_id),
        local_part = VALUES(local_part),
        password_ciphertext = VALUES(password_ciphertext),
        password_updated_at = NOW(),
        last_sync_error = NULL,
        status = 'active',
        updated_at = NOW()
    `,
    [
      memberUserId,
      input.owner.domainId,
      input.localPart,
      input.email,
      passwordCiphertext,
    ],
  );

  const [mailboxRows] = await connection.query<(RowDataPacket & { id: number })[]>(
    "SELECT id FROM mailboxes WHERE email = ? LIMIT 1",
    [input.email],
  );
  const mailboxId = mailboxRows[0]?.id;

  if (!mailboxId) {
    throw new Error("member-mailbox-create-failed");
  }

  await ensureLocalMailboxFolders(connection, mailboxId);
}

async function getAiAssistSettingsByOwnerUserId(ownerUserId: number) {
  const [rows] = await getDbPool().query<AiAssistSettingsRow[]>(
    `
      SELECT
        id,
        owner_user_id,
        mailbox_id,
        enabled,
        notification_email,
        assistant_mailbox_email,
        baseline_message_id,
        last_summarized_at,
        last_relayed_at
      FROM mailbox_ai_assist_settings
      WHERE owner_user_id = ?
      LIMIT 1
    `,
    [ownerUserId],
  );

  return rows[0] ?? null;
}

async function getCurrentInboxBaselineMessageId(mailboxId: number) {
  const [rows] = await getDbPool().query<(RowDataPacket & { max_message_id: number | null })[]>(
    `
      SELECT MAX(mm.id) AS max_message_id
      FROM mailbox_messages mm
      INNER JOIN mailbox_folders mf ON mf.id = mm.folder_id
      WHERE mm.mailbox_id = ?
        AND mf.system_name = 'inbox'
    `,
    [mailboxId],
  );

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

async function ensureAiMailboxReady() {
  const config = getAiMailboxConfig();
  const stored = await findStoredAiMailboxAccess(config.email);

  if (stored) {
    return stored;
  }

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

  return config;
}

function buildRepresentativeCredentials(owner: OwnerContext) {
  if (!owner.passwordCiphertext) {
    throw new Error("mailbox-auth-missing");
  }

  return {
    email: owner.mailboxEmail,
    password: decryptMailboxPassword(owner.passwordCiphertext),
  } satisfies RemoteMailboxCredentials;
}

async function reserveAiSummaryThread(input: {
  mailboxId: number;
  message: InboxCandidateMessageRow;
  notificationEmail: string;
  ownerUserId: number;
}) {
  return withTransaction(async (connection) => {
    const token = createBridgeToken();
    const [result] = await connection.query(
      `
        INSERT IGNORE INTO mailbox_ai_assist_threads (
          owner_user_id,
          mailbox_id,
          source_message_id,
          bridge_token,
          notification_email,
          original_sender_email,
          original_sender_name,
          original_subject,
          summary_status
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'sending')
      `,
      [
        input.ownerUserId,
        input.mailboxId,
        input.message.id,
        token,
        input.notificationEmail,
        input.message.from_address,
        input.message.from_name,
        input.message.subject,
      ],
    );
    const insertResult = result as { affectedRows?: number; insertId?: number };

    if (Number(insertResult.affectedRows ?? 0) === 1) {
      return {
        id: Number(insertResult.insertId),
        token,
      };
    }

    const [existingRows] = await connection.query<AiThreadRow[]>(
      `
        SELECT
          id,
          owner_user_id,
          mailbox_id,
          source_message_id,
          bridge_token,
          notification_email,
          original_sender_email,
          original_sender_name,
          original_subject,
          summary_status,
          updated_at
        FROM mailbox_ai_assist_threads
        WHERE source_message_id = ?
        LIMIT 1
        FOR UPDATE
      `,
      [input.message.id],
    );
    const existing = existingRows[0];

    if (!existing || existing.summary_status === "sent") {
      return null;
    }

    if (
      existing.summary_status === "sending" &&
      existing.updated_at.getTime() > Date.now() - MAIL_AI_STALE_SENDING_RETRY_MS
    ) {
      return null;
    }

    await connection.query(
      `
        UPDATE mailbox_ai_assist_threads
        SET
          notification_email = ?,
          original_sender_email = ?,
          original_sender_name = ?,
          original_subject = ?,
          summary_status = 'sending',
          last_error = NULL,
          updated_at = NOW()
        WHERE id = ?
      `,
      [
        input.notificationEmail,
        input.message.from_address,
        input.message.from_name,
        input.message.subject,
        existing.id,
      ],
    );

    return { id: existing.id, token: existing.bridge_token };
  });
}

async function updateAiSummaryThreadResult(input: {
  errorMessage?: string | null;
  messageIdHeader?: string | null;
  transportResponse?: string | null;
  status: "failed" | "sent";
  threadId: number;
}) {
  await getDbPool().query(
    `
      UPDATE mailbox_ai_assist_threads
      SET
        summary_message_id_header = ?,
        summary_transport_response = ?,
        summary_status = ?,
        summary_sent_at = CASE WHEN ? = 'sent' THEN NOW() ELSE summary_sent_at END,
        last_error = ?,
        updated_at = NOW()
      WHERE id = ?
    `,
    [
      input.messageIdHeader ?? null,
      input.transportResponse?.slice(0, 1000) ?? null,
      input.status,
      input.status,
      input.errorMessage?.slice(0, 1000) ?? null,
      input.threadId,
    ],
  );
}

function extractMailQueueId(response: string | null | undefined) {
  return response?.match(MAIL_QUEUE_ID_PATTERN)?.[1]?.toUpperCase() ?? null;
}

function extractMailcowDeliveryFailure(input: {
  queueId: string;
  recipientEmail: string;
}) {
  return async () => {
    try {
      const logs = await getMailcowPostfixLogs(800);
      const normalizedQueueId = input.queueId.toUpperCase();
      const normalizedRecipient = normalizeEmail(input.recipientEmail);
      const relevant = logs
        .map((log) => log.message)
        .filter((message) => {
          const lowerMessage = message.toLowerCase();
          return message.includes(normalizedQueueId) && lowerMessage.includes(normalizedRecipient);
        });
      const bounced = relevant.find((message) => /status=bounced|dsn=5\./i.test(message));

      return bounced ?? null;
    } catch {
      return null;
    }
  };
}

async function waitForMailcowDeliveryFailure(input: {
  queueId: string | null;
  recipientEmail: string;
}) {
  if (!input.queueId) {
    return null;
  }

  const readFailure = extractMailcowDeliveryFailure({
    queueId: input.queueId,
    recipientEmail: input.recipientEmail,
  });

  for (let attempt = 0; attempt < MAIL_AI_DELIVERY_LOG_POLL_ATTEMPTS; attempt += 1) {
    const failure = await readFailure();

    if (failure) {
      return failure;
    }

    if (attempt < MAIL_AI_DELIVERY_LOG_POLL_ATTEMPTS - 1) {
      await delay(MAIL_AI_DELIVERY_LOG_POLL_DELAY_MS);
    }
  }

  return null;
}

async function reserveAiReply(remoteMessageKey: string, threadId: number, messageIdHeader: string | null) {
  return withTransaction(async (connection) => {
    const [rows] = await connection.query<
      (RowDataPacket & { id: number; relay_status: "failed" | "relayed" | "sending" | "skipped" })[]
    >(
      `
        SELECT id, relay_status
        FROM mailbox_ai_assist_replies
        WHERE remote_message_key = ?
        LIMIT 1
      `,
      [remoteMessageKey],
    );

    const existing = rows[0];

    if (existing?.relay_status === "sending" || existing?.relay_status === "relayed" || existing?.relay_status === "skipped") {
      return null;
    }

    if (existing) {
      await connection.query(
        `
          UPDATE mailbox_ai_assist_replies
          SET
            relay_status = 'sending',
            relay_response = NULL,
            message_id_header = ?,
            updated_at = NOW()
          WHERE id = ?
        `,
        [messageIdHeader, existing.id],
      );

      return existing.id;
    }

    const [result] = await connection.query(
      `
        INSERT INTO mailbox_ai_assist_replies (
          thread_id,
          remote_message_key,
          message_id_header,
          relay_status
        ) VALUES (?, ?, ?, 'sending')
      `,
      [threadId, remoteMessageKey, messageIdHeader],
    );

    return Number((result as { insertId: number }).insertId);
  });
}

async function updateAiReplyResult(input: {
  relayId: number;
  response: string | null;
  status: "failed" | "relayed" | "skipped";
}) {
  await getDbPool().query(
    `
      UPDATE mailbox_ai_assist_replies
      SET
        relay_status = ?,
        relay_response = ?,
        relayed_at = CASE WHEN ? = 'relayed' THEN NOW() ELSE relayed_at END,
        updated_at = NOW()
      WHERE id = ?
    `,
    [input.status, input.response?.slice(0, 1000) ?? null, input.status, input.relayId],
  );
}

async function processPendingAiSummaries(owner: OwnerContext, settings: AiAssistSettingsRow) {
  if (!settings.enabled || !settings.notification_email) {
    return;
  }

  const [rows] = await getDbPool().query<InboxCandidateMessageRow[]>(
    `
      SELECT
        mm.id,
        mm.subject,
        mm.from_name,
        mm.from_address,
        mm.snippet,
        mm.body_text,
        mm.body_html,
        mm.raw_source,
        mm.message_id_header,
        mm.received_at,
        t.id AS thread_id,
        t.summary_status AS thread_status
      FROM mailbox_messages mm
      INNER JOIN mailbox_folders mf ON mf.id = mm.folder_id
      LEFT JOIN mailbox_ai_assist_threads t ON t.source_message_id = mm.id
      WHERE mm.mailbox_id = ?
        AND mm.direction = 'inbound'
        AND mf.system_name = 'inbox'
        AND mm.id > COALESCE(?, 0)
        AND LOWER(mm.from_address) <> LOWER(?)
        AND (
          t.id IS NULL
          OR t.summary_status = 'failed'
          OR (
            t.summary_status = 'sending'
            AND t.updated_at < DATE_SUB(NOW(), INTERVAL 5 MINUTE)
          )
        )
      ORDER BY mm.received_at ASC, mm.id ASC
      LIMIT 10
    `,
    [owner.mailboxId, settings.baseline_message_id, settings.assistant_mailbox_email],
  );

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

  const aiMailbox = await ensureAiMailboxReady();
  const aiCredentials = toRemoteMailboxCredentials(aiMailbox);

  for (const message of rows) {
    const reservedThread = await reserveAiSummaryThread({
      mailboxId: owner.mailboxId,
      message,
      notificationEmail: settings.notification_email,
      ownerUserId: owner.ownerUserId,
    });

    if (!reservedThread) {
      continue;
    }

    let summaryText: string;
    let summaryErrorMessage: string | null = null;
    let originalHtml: string | undefined;
    let originalText: string | undefined;

    try {
      summaryText = await generateMailAiSummary({
        companyName: owner.companyName,
        message,
        representativeMailbox: owner.mailboxEmail,
      });
    } catch (error) {
      summaryErrorMessage = error instanceof Error ? error.message : "ai-summary-generation-failed";
      summaryText = "요약에 실패했습니다.";
      originalHtml = buildOriginalMessageHtml(message);
      originalText = buildOriginalMessageText(message);
    }

    const subject = `Fwd: ${message.subject || "(제목 없음)"} [AI-BRIDGE:${reservedThread.token}]`;
    const attachments = await loadRelayAttachments(message);
    const displaySummaryText = normalizeSummaryDisplayText(summaryText);
    const bodyText = buildAiSummaryTextBody({
      attachmentCount: attachments.length,
      companyName: owner.companyName,
      message,
      notificationEmail: settings.notification_email,
      originalText,
      representativeMailbox: owner.mailboxEmail,
      summaryText: displaySummaryText,
    });
    const bodyHtml = buildAiSummaryHtmlBody({
      attachments,
      companyName: owner.companyName,
      message,
      notificationEmail: settings.notification_email,
      originalHtml,
      representativeMailbox: owner.mailboxEmail,
      summaryText: displaySummaryText,
    });
    const messageIdHeader = createMessageIdHeader(aiMailbox.domain);
    const rawSource = buildRawSource({
      attachments,
      bodyHtml,
      bodyText,
      fromAddress: aiMailbox.email,
      fromName: aiMailbox.displayName,
      messageIdHeader,
      subject,
      toAddresses: settings.notification_email,
    });

    try {
      const sendResult = await sendSmtpMessage(aiCredentials, {
        rawSource,
        recipients: [settings.notification_email],
      });
      await appendSentMessage(aiCredentials, rawSource).catch(() => null);
      const queueId = extractMailQueueId(sendResult.response);
      const deliveryFailure = await waitForMailcowDeliveryFailure({
        queueId,
        recipientEmail: settings.notification_email,
      });

      if (deliveryFailure) {
        await updateAiSummaryThreadResult({
          errorMessage: `delivery-bounce:${deliveryFailure}`,
          messageIdHeader,
          status: "failed",
          transportResponse: sendResult.response,
          threadId: reservedThread.id,
        });
        continue;
      }

      await updateAiSummaryThreadResult({
        errorMessage: summaryErrorMessage,
        messageIdHeader,
        status: "sent",
        transportResponse: sendResult.response,
        threadId: reservedThread.id,
      });
      await getDbPool().query(
        `
          UPDATE mailbox_ai_assist_settings
          SET last_summarized_at = NOW(), updated_at = NOW()
          WHERE id = ?
        `,
        [settings.id],
      );
    } catch (error) {
      const messageText = error instanceof Error ? error.message : "ai-summary-send-failed";
      await updateAiSummaryThreadResult({
        errorMessage: messageText,
        messageIdHeader,
        status: "failed",
        threadId: reservedThread.id,
      });
    }
  }
}

function looksLikeDeliveryFailure(message: RemoteMailboxMessage) {
  const fromAddress = normalizeEmail(message.fromAddress);
  const subject = message.subject.toLowerCase();

  return (
    fromAddress.includes("mailer-daemon") ||
    fromAddress.startsWith("postmaster@") ||
    subject.includes("undeliver") ||
    subject.includes("delivery status notification") ||
    subject.includes("delivery failure") ||
    subject.includes("mail delivery failed") ||
    subject.includes("returned mail")
  );
}

function extractReplyReferenceMessageIds(message: RemoteMailboxMessage) {
  const headerText = message.rawSource.split(/\r?\n\r?\n/, 1)[0] ?? "";
  const unfoldedHeaderText = headerText.replace(/\r?\n[ \t]+/g, " ");
  const referencedHeaders = unfoldedHeaderText
    .split(/\r?\n/)
    .filter((line) => /^(in-reply-to|references):/i.test(line))
    .join("\n");

  return Array.from(new Set(referencedHeaders.match(MESSAGE_ID_HEADER_PATTERN) ?? []));
}

async function markAiSummaryFailedFromBounce(owner: OwnerContext, message: RemoteMailboxMessage) {
  if (!looksLikeDeliveryFailure(message)) {
    return false;
  }

  const haystack = `${message.subject}\n${message.bodyText}\n${message.bodyHtml ?? ""}`;
  const bridgeToken = haystack.match(AI_BRIDGE_TOKEN_PATTERN)?.[1]?.toUpperCase() ?? null;
  const messageIdHeaders = Array.from(new Set(haystack.match(MESSAGE_ID_HEADER_PATTERN) ?? []));
  const conditions = ["owner_user_id = ?"];
  const params: Array<number | string> = [owner.ownerUserId];

  if (bridgeToken) {
    conditions.push("bridge_token = ?");
    params.push(bridgeToken);
  }

  for (const messageIdHeader of messageIdHeaders.slice(0, 5)) {
    conditions.push("summary_message_id_header = ?");
    params.push(messageIdHeader);
  }

  if (conditions.length === 1) {
    return false;
  }

  const [result] = await getDbPool().query(
    `
      UPDATE mailbox_ai_assist_threads
      SET
        summary_status = 'failed',
        last_error = ?,
        updated_at = NOW()
      WHERE owner_user_id = ?
        AND (${conditions.slice(1).join(" OR ")})
      LIMIT 1
    `,
    [
      `delivery-bounce:${message.subject || "AI summary delivery failed"}`.slice(0, 1000),
      owner.ownerUserId,
      ...params.slice(1),
    ],
  );

  return Number((result as { affectedRows?: number }).affectedRows ?? 0) > 0;
}

async function processAiReplyRelay(owner: OwnerContext, settings: AiAssistSettingsRow) {
  const summary: Omit<MailAiReplyRelayRunSummary, "owners"> = {
    failed: 0,
    relayed: 0,
    skipped: 0,
  };

  if (!settings.enabled) {
    return summary;
  }

  const aiMailboxConfig = await ensureAiMailboxReady();
  const aiCredentials = toRemoteMailboxCredentials(aiMailboxConfig);
  const representativeCredentials = buildRepresentativeCredentials(owner);
  const remoteSync = await syncRemoteMailbox(aiCredentials, "inbox");

  for (const message of remoteSync.currentFolderMessages) {
    if (await markAiSummaryFailedFromBounce(owner, message)) {
      await markRemoteMessageAsSeen(aiCredentials, message.remoteFolder, message.remoteUid).catch(() => null);
      continue;
    }

    if (normalizeEmail(message.fromAddress) === normalizeEmail(aiMailboxConfig.email)) {
      continue;
    }

    const bridgeToken = message.subject.match(AI_BRIDGE_TOKEN_PATTERN)?.[1]?.toUpperCase();
    const referenceMessageIds = bridgeToken ? [] : extractReplyReferenceMessageIds(message);

    if (!bridgeToken && referenceMessageIds.length === 0) {
      continue;
    }

    const matchConditions: string[] = [];
    const threadParams: Array<number | string> = [owner.ownerUserId];

    if (bridgeToken) {
      matchConditions.push("bridge_token = ?");
      threadParams.push(bridgeToken);
    }

    for (const messageIdHeader of referenceMessageIds.slice(0, 10)) {
      matchConditions.push("summary_message_id_header = ?");
      threadParams.push(messageIdHeader);
    }

    const [threadRows] = await getDbPool().query<AiThreadRow[]>(
      `
        SELECT
          id,
          owner_user_id,
          mailbox_id,
          source_message_id,
          bridge_token,
          notification_email,
          original_sender_email,
          original_sender_name,
          original_subject,
          summary_status
        FROM mailbox_ai_assist_threads
        WHERE owner_user_id = ?
          AND (${matchConditions.join(" OR ")})
        LIMIT 1
      `,
      threadParams,
    );
    const thread = threadRows[0];

    if (!thread) {
      continue;
    }

    const remoteMessageKey = `${message.remoteFolder}:${message.remoteUid}`;
    const relayId = await reserveAiReply(remoteMessageKey, thread.id, message.messageIdHeader);

    if (!relayId) {
      continue;
    }

    const replyText = extractReplyText(message);

    if (!replyText) {
      await updateAiReplyResult({
        relayId,
        response: "답장 본문을 추출하지 못했습니다.",
        status: "skipped",
      });
      summary.skipped += 1;
      await markRemoteMessageAsSeen(aiCredentials, message.remoteFolder, message.remoteUid).catch(() => null);
      continue;
    }

    const relaySubject = thread.original_subject.trim().toLowerCase().startsWith("re:")
      ? thread.original_subject
      : `Re: ${thread.original_subject}`;
    const relayBodyText = replyText;
    const relayMessageIdHeader = createMessageIdHeader(owner.domain);
    const relayRawSource = buildRawSource({
      bodyHtml: plainTextToHtml(relayBodyText),
      bodyText: relayBodyText,
      fromAddress: owner.mailboxEmail,
      fromName: owner.displayName,
      messageIdHeader: relayMessageIdHeader,
      subject: relaySubject,
      toAddresses: thread.original_sender_email,
    });

    try {
      await sendSmtpMessage(representativeCredentials, {
        rawSource: relayRawSource,
        recipients: [thread.original_sender_email],
      });
      await appendSentMessage(representativeCredentials, relayRawSource).catch(() => null);
      await updateAiReplyResult({
        relayId,
        response: `원본 발신자 ${thread.original_sender_email}에게 전달했습니다.`,
        status: "relayed",
      });
      summary.relayed += 1;
      await getDbPool().query(
        `
          UPDATE mailbox_ai_assist_settings
          SET last_relayed_at = NOW(), updated_at = NOW()
          WHERE id = ?
        `,
        [settings.id],
      );
      await markRemoteMessageAsSeen(aiCredentials, message.remoteFolder, message.remoteUid).catch(() => null);
    } catch (error) {
      const errorMessage = error instanceof Error ? error.message : "ai-reply-relay-failed";
      await updateAiReplyResult({
        relayId,
        response: errorMessage,
        status: "failed",
      });
      summary.failed += 1;
    }
  }

  return summary;
}

export async function getEnabledAiAssistOwnerEmails() {
  await ensureOfficialMailSchema();
  const [rows] = await getDbPool().query<AiAssistEnabledOwnerRow[]>(
    `
      SELECT u.email AS owner_email
      FROM mailbox_ai_assist_settings mas
      INNER JOIN users u ON u.id = mas.owner_user_id
      WHERE mas.enabled = 1
        AND mas.notification_email IS NOT NULL
      ORDER BY mas.updated_at DESC, mas.id DESC
    `,
  );

  return rows
    .map((row) => normalizeEmail(row.owner_email))
    .filter(Boolean)
    .map((ownerEmail) => ({ ownerEmail })) satisfies MailAiAssistOwnerEmail[];
}

export async function processAiReplyRelaySweep() {
  const rows = await getEnabledAiAssistOwnerEmails();
  const summary: MailAiReplyRelayRunSummary = {
    failed: 0,
    owners: 0,
    relayed: 0,
    skipped: 0,
  };

  for (const row of rows) {
    const ownerEmail = row.ownerEmail;

    if (!ownerEmail || !(await hasActiveGrowthPlanByOwnerEmail(ownerEmail))) {
      continue;
    }

    const owner = await getOwnerContextByEmail(ownerEmail);
    const settings = await getAiAssistSettingsByOwnerUserId(owner.ownerUserId);

    if (!settings?.enabled) {
      continue;
    }

    summary.owners += 1;
    const ownerSummary = await processAiReplyRelay(owner, settings);
    summary.failed += ownerSummary.failed;
    summary.relayed += ownerSummary.relayed;
    summary.skipped += ownerSummary.skipped;
  }

  return summary;
}

export async function getManagedMembersByOwnerEmail(ownerEmail: string) {
  const owner = await getOwnerContextByEmail(ownerEmail);
  const [rows] = await getDbPool().query<ManagedMemberRow[]>(
    `
      SELECT
        id,
        email,
        local_part,
        display_name,
        status,
        password_ciphertext,
        created_at,
        updated_at
      FROM managed_team_mailboxes
      WHERE owner_user_id = ?
      ORDER BY created_at ASC, id ASC
    `,
    [owner.ownerUserId],
  );

  return rows.map((row) => ({
    id: row.id,
    email: row.email,
    localPart: row.local_part,
    displayName: row.display_name,
    status: row.status,
    createdAt: row.created_at.toISOString(),
    updatedAt: row.updated_at.toISOString(),
  })) satisfies ManagedMailboxMember[];
}

async function prepareManagedMemberCreationByOwnerEmail(
  ownerEmail: string,
  input: {
    displayName: string;
    localPart: string;
    password: string;
  },
  options?: {
    domainId?: number;
  },
) {
  const owner = await getOwnerContextByEmail(ownerEmail, options);
  const displayName = input.displayName.trim();
  const localPart = normalizeLocalPart(input.localPart);
  const password = input.password.trim();
  const email = `${localPart}@${owner.domain}`;

  if (!displayName || !localPart || !password) {
    throw new Error("member-required");
  }

  assertLocalPart(localPart);

  const [conflictRows] = await getDbPool().query<(RowDataPacket & { email: string })[]>(
    `
      SELECT email FROM users WHERE email = ?
      UNION ALL
      SELECT email FROM mailboxes WHERE email = ?
      UNION ALL
      SELECT email FROM managed_team_mailboxes WHERE email = ?
      LIMIT 1
    `,
    [email, email, email],
  );

  if (conflictRows.length > 0) {
    throw new Error("member-mailbox-taken");
  }

  return {
    displayName,
    email,
    localPart,
    owner,
    password,
  };
}

export async function assertManagedMemberCreationAllowedByOwnerEmail(
  ownerEmail: string,
  input: {
    displayName: string;
    localPart: string;
    password: string;
  },
  options?: {
    domainId?: number;
  },
) {
  await prepareManagedMemberCreationByOwnerEmail(ownerEmail, input, options);
}

export async function createManagedMemberByOwnerEmail(
  ownerEmail: string,
  input: {
    displayName: string;
    localPart: string;
    password: string;
  },
  options?: {
    domainId?: number;
  },
) {
  const prepared = await prepareManagedMemberCreationByOwnerEmail(ownerEmail, input, options);

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

  await withTransaction(async (connection) => {
    await connection.query(
      `
        INSERT INTO managed_team_mailboxes (
          owner_user_id,
          domain_id,
          local_part,
          email,
          display_name,
          password_ciphertext,
          status
        ) VALUES (?, ?, ?, ?, ?, ?, 'active')
      `,
      [
        prepared.owner.ownerUserId,
        prepared.owner.domainId,
        prepared.localPart,
        prepared.email,
        prepared.displayName,
        encryptMailboxPassword(prepared.password),
      ],
    );

    await upsertManagedMemberAppAccount(connection, {
      displayName: prepared.displayName,
      email: prepared.email,
      localPart: prepared.localPart,
      owner: prepared.owner,
      password: prepared.password,
    });
  });
}

export async function updateManagedMemberByOwnerEmail(
  ownerEmail: string,
  input: {
    displayName: string;
    memberId: number;
    password?: string;
  },
) {
  const owner = await getOwnerContextByEmail(ownerEmail);
  const displayName = input.displayName.trim();

  if (!displayName || !Number.isInteger(input.memberId) || input.memberId <= 0) {
    throw new Error("member-required");
  }

  const [rows] = await getDbPool().query<ManagedMemberRow[]>(
    `
      SELECT
        id,
        email,
        local_part,
        display_name,
        status,
        password_ciphertext,
        created_at,
        updated_at
      FROM managed_team_mailboxes
      WHERE owner_user_id = ?
        AND id = ?
      LIMIT 1
    `,
    [owner.ownerUserId, input.memberId],
  );
  const member = rows[0];

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

  if (member.status !== "active") {
    throw new Error("member-disabled");
  }

  const nextPassword = input.password?.trim() || decryptMailboxPassword(member.password_ciphertext);

  await ensureMailcowMailboxAccount({
    displayName,
    domain: owner.domain,
    localPart: member.local_part,
    mailboxPassword: nextPassword,
  });

  await withTransaction(async (connection) => {
    await connection.query(
      `
        UPDATE managed_team_mailboxes
        SET
          display_name = ?,
          password_ciphertext = ?,
          updated_at = NOW()
        WHERE id = ?
      `,
      [displayName, encryptMailboxPassword(nextPassword), member.id],
    );

    await upsertManagedMemberAppAccount(connection, {
      displayName,
      email: member.email,
      localPart: member.local_part,
      owner,
      password: nextPassword,
    });
  });
}

export async function deleteManagedMemberByOwnerEmail(ownerEmail: string, memberId: number) {
  const owner = await getOwnerContextByEmail(ownerEmail);

  if (!Number.isInteger(memberId) || memberId <= 0) {
    throw new Error("member-not-found");
  }

  const [rows] = await getDbPool().query<ManagedMemberRow[]>(
    `
      SELECT
        id,
        email,
        local_part,
        display_name,
        status,
        password_ciphertext,
        created_at,
        updated_at
      FROM managed_team_mailboxes
      WHERE owner_user_id = ?
        AND id = ?
      LIMIT 1
    `,
    [owner.ownerUserId, memberId],
  );
  const member = rows[0];

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

  await deleteMailcowMailboxAccount(member.email);

  await withTransaction(async (connection) => {
    await connection.query("DELETE FROM managed_team_mailboxes WHERE id = ?", [member.id]);
    await connection.query("DELETE FROM users WHERE email = ? AND id <> ?", [
      member.email,
      owner.ownerUserId,
    ]);
    await connection.query("DELETE FROM mailboxes WHERE email = ?", [member.email]);
  });
}

export async function getAiAssistOverviewByOwnerEmail(ownerEmail: string) {
  const owner = await getOwnerContextByEmail(ownerEmail);
  const settings = await getAiAssistSettingsByOwnerUserId(owner.ownerUserId);
  const assistantMailboxEmail = settings?.assistant_mailbox_email ?? getAiMailboxConfig().email;
  const [countRows] = await getDbPool().query<AiAssistCountsRow[]>(
    `
      SELECT
        (
          SELECT COUNT(*)
          FROM mailbox_ai_assist_threads
          WHERE owner_user_id = ?
            AND summary_status = 'sent'
        ) AS sent_summary_count,
        (
          SELECT COUNT(*)
          FROM mailbox_ai_assist_replies mar
          INNER JOIN mailbox_ai_assist_threads mat ON mat.id = mar.thread_id
          WHERE mat.owner_user_id = ?
            AND mar.relay_status = 'relayed'
        ) AS relayed_count,
        (
          SELECT MAX(summary_sent_at)
          FROM mailbox_ai_assist_threads
          WHERE owner_user_id = ?
            AND summary_status = 'sent'
        ) AS last_summarized_at,
        (
          SELECT MAX(relayed_at)
          FROM mailbox_ai_assist_replies mar
          INNER JOIN mailbox_ai_assist_threads mat ON mat.id = mar.thread_id
          WHERE mat.owner_user_id = ?
            AND mar.relay_status = 'relayed'
        ) AS last_relayed_at
    `,
    [owner.ownerUserId, owner.ownerUserId, owner.ownerUserId, owner.ownerUserId],
  );
  const counts = countRows[0];

  return {
    assistantMailboxEmail,
    enabled: Boolean(settings?.enabled),
    lastRelayedAt: normalizeDate(counts?.last_relayed_at ?? settings?.last_relayed_at ?? null),
    lastSummarizedAt: normalizeDate(counts?.last_summarized_at ?? settings?.last_summarized_at ?? null),
    notificationEmail: settings?.notification_email ?? null,
    relayedCount: Number(counts?.relayed_count ?? 0),
    representativeMailbox: owner.mailboxEmail,
    sentSummaryCount: Number(counts?.sent_summary_count ?? 0),
  } satisfies MailAiAssistOverview;
}

export async function saveAiAssistSettingsByOwnerEmail(
  ownerEmail: string,
  input: {
    enabled: boolean;
    notificationEmail: string;
  },
) {
  const owner = await getOwnerContextByEmail(ownerEmail);
  const enabled = Boolean(input.enabled);
  const notificationEmail = normalizeEmail(input.notificationEmail);
  const aiMailbox = await ensureAiMailboxReady();
  const existing = await getAiAssistSettingsByOwnerUserId(owner.ownerUserId);

  if (enabled) {
    if (!notificationEmail) {
      throw new Error("ai-assist-notification-required");
    }

    assertEmail(notificationEmail, "ai-assist-invalid-email");

    if (!(await hasActiveGrowthPlanByOwnerEmail(ownerEmail))) {
      throw new Error("ai-assist-plan-required");
    }
  }

  const baselineMessageId =
    !existing && enabled
      ? await getCurrentInboxBaselineMessageId(owner.mailboxId)
      : existing && !existing.enabled && enabled
        ? await getCurrentInboxBaselineMessageId(owner.mailboxId)
        : existing?.baseline_message_id ?? null;

  if (existing) {
    await getDbPool().query(
      `
        UPDATE mailbox_ai_assist_settings
        SET
          mailbox_id = ?,
          enabled = ?,
          notification_email = ?,
          assistant_mailbox_email = ?,
          baseline_message_id = ?,
          updated_at = NOW()
        WHERE id = ?
      `,
      [
        owner.mailboxId,
        enabled ? 1 : 0,
        notificationEmail || null,
        aiMailbox.email,
        baselineMessageId,
        existing.id,
      ],
    );
  } else {
    await getDbPool().query(
      `
        INSERT INTO mailbox_ai_assist_settings (
          owner_user_id,
          mailbox_id,
          enabled,
          notification_email,
          assistant_mailbox_email,
          baseline_message_id
        ) VALUES (?, ?, ?, ?, ?, ?)
      `,
      [
        owner.ownerUserId,
        owner.mailboxId,
        enabled ? 1 : 0,
        notificationEmail || null,
        aiMailbox.email,
        baselineMessageId,
      ],
    );
  }
}

export async function triggerMailboxPostSyncByOwnerEmail(ownerEmail: string, syncedFolder?: string) {
  if (syncedFolder && syncedFolder !== "inbox") {
    return;
  }

  try {
    const owner = await getOwnerContextByEmail(ownerEmail);
    const settings = await getAiAssistSettingsByOwnerUserId(owner.ownerUserId);

    if (!settings?.enabled) {
      return;
    }

    if (!(await hasActiveGrowthPlanByOwnerEmail(ownerEmail))) {
      return;
    }

    await processPendingAiSummaries(owner, settings);
    await processAiReplyRelay(owner, settings);
  } catch (error) {
    console.error("AI assist bridge processing failed", error);
  }
}