import "server-only";

import type { PoolConnection, ResultSetHeader, RowDataPacket } from "mysql2/promise";
import { ensureOfficialMailSchema, getDbPool } from "@/lib/db";
import { plainTextToHtml } from "@/lib/mail-html";
import { publishMailboxAutoSendCompleted } from "@/lib/mailbox-notifications";
import { createMailboxMessage } from "@/lib/official-mail";
import { hasActiveGrowthPlanByOwnerEmail } from "@/lib/toss-pay";

const EMAIL_PATTERN = /^[^\s@]+@[^\s@]+\.[^\s@]+$/i;
const DEFAULT_AUTO_SEND_SUBJECT = "안녕하세요";
const AUTO_SEND_INTERVAL_PRESETS = ["daily", "weekly", "monthly", "custom"] as const;
const DEFAULT_AUTO_SEND_INTERVAL_MINUTES = 1440;
const MIN_AUTO_SEND_INTERVAL_MINUTES = 1;
const MAX_AUTO_SEND_INTERVAL_MINUTES = 43_200;
const DEFAULT_AUTO_SEND_DAILY_SEND_LIMIT = 30;
const DEFAULT_AUTO_SEND_BATCH_SIZE = 1;
const MIN_AUTO_SEND_SEND_COUNT = 1;
const MAX_AUTO_SEND_SEND_COUNT = 30;
const AUTO_SEND_RECIPIENT_DUPLICATE_SCOPES = ["pending", "all"] as const;

export type AutoSendIntervalPreset = (typeof AUTO_SEND_INTERVAL_PRESETS)[number];
export type AutoSendRecipientDuplicateScope =
  (typeof AUTO_SEND_RECIPIENT_DUPLICATE_SCOPES)[number];

export type MailAutoSendRecipient = {
  active: boolean;
  displayName: string | null;
  email: string;
  id: number;
};

export type MailAutoSendPhrase = {
  active: boolean;
  bodyText: string;
  id: number;
  selected: boolean;
  subjectText: string;
};

export type MailAutoSendDeliveryLog = {
  createdAt: string | null;
  errorMessage: string | null;
  id: number;
  recipientEmail: string;
  scheduledAt: string | null;
  sentAt: string | null;
  status: "sending" | "sent" | "failed";
  subject: string;
};

export type MailAutoSendOverview = {
  deliveryLogs: MailAutoSendDeliveryLog[];
  enabled: boolean;
  failedCount: number;
  lastSentAt: string | null;
  nextRunAt: string | null;
  phrases: MailAutoSendPhrase[];
  randomEnabled: boolean;
  recipientDuplicateScope: AutoSendRecipientDuplicateScope;
  recipients: MailAutoSendRecipient[];
  representativeMailbox: string | null;
  sendIntervalPreset: AutoSendIntervalPreset;
  sendIntervalMinutes: number;
  dailySendLimit: number;
  dailySentCount: number;
  sendBatchSize: number;
  subject: string;
};

export type MailAutoSendRunSummary = {
  attempted: number;
  failed: number;
  owners: number;
  sent: number;
  skipped: number;
};

type OwnerContext = {
  mailboxEmail: string;
  mailboxId: number;
  ownerEmail: string;
  ownerUserId: number;
};

type OwnerContextRow = RowDataPacket & {
  mailbox_email: string;
  mailbox_id: number;
  owner_email: string;
  owner_user_id: number;
};

type AutoSendSettingRow = RowDataPacket & {
  enabled: number;
  failed_count: number | null;
  id: number;
  last_sent_at: Date | null;
  mailbox_id: number;
  max_send_count: number;
  send_batch_size: number;
  next_run_at: Date | null;
  owner_email: string;
  owner_user_id: number;
  random_enabled: number;
  recipient_duplicate_scope: string | null;
  representative_mailbox: string;
  send_interval_preset: string | null;
  send_interval_minutes: number;
  daily_sent_count: number | null;
  subject: string;
};

type AutoSendRecipientRow = RowDataPacket & {
  active: number;
  display_name: string | null;
  email: string;
  id: number;
};

type AutoSendPhraseRow = RowDataPacket & {
  active: number;
  body_text: string;
  id: number;
  selected: number;
  subject_text: string | null;
};

type AutoSendDeliveryLogRow = RowDataPacket & {
  created_at: Date | null;
  error_message: string | null;
  id: number;
  recipient_email: string;
  scheduled_at: Date | null;
  sent_at: Date | null;
  status: "sending" | "sent" | "failed";
  subject: string;
};

type AutoSendSaveRecipient = {
  active?: boolean;
  displayName?: string | null;
  email: string;
};

type AutoSendSavePhrase = {
  active?: boolean;
  bodyText: string;
  selected?: boolean;
  subjectText?: string;
};

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

function assertEmail(value: string, errorCode = "auto-send-invalid-email") {
  if (!EMAIL_PATTERN.test(value)) {
    throw new Error(errorCode);
  }
}

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

function normalizeBodyText(value: string) {
  return value.replace(/\r/g, "").trim();
}

function normalizeIntervalMinutes(value: number) {
  if (!Number.isFinite(value)) {
    return DEFAULT_AUTO_SEND_INTERVAL_MINUTES;
  }

  return Math.min(
    MAX_AUTO_SEND_INTERVAL_MINUTES,
    Math.max(MIN_AUTO_SEND_INTERVAL_MINUTES, Math.round(value)),
  );
}

function normalizeIntervalPreset(value: string | null | undefined): AutoSendIntervalPreset {
  return AUTO_SEND_INTERVAL_PRESETS.includes(value as AutoSendIntervalPreset)
    ? (value as AutoSendIntervalPreset)
    : "daily";
}

function normalizeRecipientDuplicateScope(
  value: string | null | undefined,
): AutoSendRecipientDuplicateScope {
  return AUTO_SEND_RECIPIENT_DUPLICATE_SCOPES.includes(
    value as AutoSendRecipientDuplicateScope,
  )
    ? (value as AutoSendRecipientDuplicateScope)
    : "pending";
}

function intervalMinutesForPreset(preset: AutoSendIntervalPreset, customMinutes: number) {
  if (preset === "daily") {
    return 1440;
  }

  if (preset === "weekly") {
    return 10_080;
  }

  if (preset === "monthly") {
    return 43_200;
  }

  return normalizeIntervalMinutes(customMinutes);
}

function normalizeSendCount(value: number, fallback: number) {
  if (!Number.isFinite(value) || value <= 0) {
    return fallback;
  }

  return Math.min(
    MAX_AUTO_SEND_SEND_COUNT,
    Math.max(MIN_AUTO_SEND_SEND_COUNT, Math.round(value)),
  );
}

function normalizeNextRunAtSql(value: string | null | undefined) {
  const normalized = String(value ?? "").trim();

  if (!normalized) {
    return null;
  }

  const match = normalized.match(
    /^(\d{4})-(\d{2})-(\d{2})[T ](\d{2}):(\d{2})(?::(\d{2}))?$/,
  );

  if (!match) {
    throw new Error("auto-send-invalid-date");
  }

  return `${match[1]}-${match[2]}-${match[3]} ${match[4]}:${match[5]}:${match[6] ?? "00"}`;
}

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

function uniqueRecipients(input: AutoSendSaveRecipient[]) {
  const recipientsByEmail = new Map<string, AutoSendSaveRecipient>();

  for (const item of input) {
    const email = normalizeEmail(item.email ?? "");

    if (!email) {
      continue;
    }

    assertEmail(email);
    const displayName = String(item.displayName ?? "").trim().slice(0, 191) || null;
    const existing = recipientsByEmail.get(email);

    if (existing) {
      recipientsByEmail.set(email, {
        active: existing.active !== false || item.active !== false,
        displayName: existing.displayName || displayName,
        email,
      });
      continue;
    }

    recipientsByEmail.set(email, {
      active: item.active !== false,
      displayName,
      email,
    });
  }

  return Array.from(recipientsByEmail.values());
}

function uniquePhrases(input: AutoSendSavePhrase[]) {
  const phrases: AutoSendSavePhrase[] = [];

  for (const item of input) {
    const bodyText = normalizeBodyText(item.bodyText ?? "");
    const subjectText = normalizeSubject(item.subjectText || DEFAULT_AUTO_SEND_SUBJECT);

    if (!bodyText) {
      continue;
    }

    phrases.push({
      active: item.active !== false,
      bodyText,
      selected: item.selected !== false,
      subjectText,
    });
  }

  return phrases;
}

async function withTransaction<T>(callback: (connection: PoolConnection) => Promise<T>) {
  const connection = await getDbPool().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) {
  await ensureOfficialMailSchema();
  const [rows] = await getDbPool().query<OwnerContextRow[]>(
    `
      SELECT
        u.id AS owner_user_id,
        u.email AS owner_email,
        m.id AS mailbox_id,
        m.email AS mailbox_email
      FROM users u
      INNER JOIN mailboxes m ON m.user_id = u.id
      WHERE u.email = ?
      ORDER BY CASE WHEN m.email = ? THEN 0 ELSE 1 END, m.id DESC
      LIMIT 1
    `,
    [normalizeEmail(ownerEmail), normalizeEmail(ownerEmail)],
  );
  const row = rows[0];

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

  return {
    mailboxEmail: row.mailbox_email,
    mailboxId: row.mailbox_id,
    ownerEmail: row.owner_email,
    ownerUserId: row.owner_user_id,
  } satisfies OwnerContext;
}

async function getAutoSendSettingByOwnerUserId(ownerUserId: number) {
  const [rows] = await getDbPool().query<AutoSendSettingRow[]>(
    `
      SELECT
        s.id,
        s.owner_user_id,
        s.mailbox_id,
        s.enabled,
        s.subject,
        s.random_enabled,
        s.recipient_duplicate_scope,
        s.send_interval_preset,
        s.send_interval_minutes,
        s.max_send_count,
        s.send_batch_size,
        s.next_run_at,
        s.last_sent_at,
        u.email AS owner_email,
        m.email AS representative_mailbox,
        (
          SELECT COUNT(*)
          FROM mailbox_auto_send_deliveries d
          WHERE d.setting_id = s.id
            AND d.status = 'sent'
            AND d.sent_at >= CURDATE()
            AND d.sent_at < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
        ) AS daily_sent_count,
        (
          SELECT COUNT(*)
          FROM mailbox_auto_send_deliveries d
          WHERE d.setting_id = s.id
            AND d.status = 'failed'
        ) AS failed_count
      FROM mailbox_auto_send_settings s
      INNER JOIN users u ON u.id = s.owner_user_id
      INNER JOIN mailboxes m ON m.id = s.mailbox_id
      WHERE s.owner_user_id = ?
      LIMIT 1
    `,
    [ownerUserId],
  );

  return rows[0] ?? null;
}

async function getAutoSendRecipients(settingId: number) {
  const [rows] = await getDbPool().query<AutoSendRecipientRow[]>(
    `
      SELECT
        r.id,
        r.email,
        r.display_name,
        r.active
      FROM mailbox_auto_send_recipients r
      WHERE r.setting_id = ?
      ORDER BY id ASC
    `,
    [settingId],
  );

  return rows.map((row) => ({
    active: row.active === 1,
    displayName: row.display_name,
    email: row.email,
    id: row.id,
  })) satisfies MailAutoSendRecipient[];
}

async function getAutoSendPhrases(settingId: number) {
  const [rows] = await getDbPool().query<AutoSendPhraseRow[]>(
    `
      SELECT id, subject_text, body_text, active, selected
      FROM mailbox_auto_send_phrases
      WHERE setting_id = ?
      ORDER BY id ASC
    `,
    [settingId],
  );

  return rows.map((row) => ({
    active: row.active === 1,
    bodyText: row.body_text,
    id: row.id,
    selected: row.selected === 1,
    subjectText: row.subject_text || DEFAULT_AUTO_SEND_SUBJECT,
  })) satisfies MailAutoSendPhrase[];
}

async function getAutoSendDeliveryLogs(settingId: number) {
  const [rows] = await getDbPool().query<AutoSendDeliveryLogRow[]>(
    `
      SELECT
        id,
        recipient_email,
        subject,
        status,
        error_message,
        scheduled_at,
        sent_at,
        created_at
      FROM mailbox_auto_send_deliveries
      WHERE setting_id = ?
      ORDER BY COALESCE(sent_at, created_at) DESC, id DESC
      LIMIT 500
    `,
    [settingId],
  );

  return rows.map((row) => ({
    createdAt: normalizeDate(row.created_at),
    errorMessage: row.error_message,
    id: row.id,
    recipientEmail: row.recipient_email,
    scheduledAt: normalizeDate(row.scheduled_at),
    sentAt: normalizeDate(row.sent_at),
    status: row.status,
    subject: row.subject,
  })) satisfies MailAutoSendDeliveryLog[];
}

function emptyOverview(owner: OwnerContext): MailAutoSendOverview {
  return {
    deliveryLogs: [],
    enabled: false,
    failedCount: 0,
    lastSentAt: null,
    nextRunAt: null,
    phrases: [],
    randomEnabled: true,
    recipientDuplicateScope: "pending",
    recipients: [],
    representativeMailbox: owner.mailboxEmail,
    sendIntervalPreset: "daily",
    sendIntervalMinutes: DEFAULT_AUTO_SEND_INTERVAL_MINUTES,
    dailySendLimit: DEFAULT_AUTO_SEND_DAILY_SEND_LIMIT,
    dailySentCount: 0,
    sendBatchSize: DEFAULT_AUTO_SEND_BATCH_SIZE,
    subject: DEFAULT_AUTO_SEND_SUBJECT,
  };
}

export async function getAutoSendOverviewByOwnerEmail(ownerEmail: string) {
  const owner = await getOwnerContextByEmail(ownerEmail);
  const setting = await getAutoSendSettingByOwnerUserId(owner.ownerUserId);

  if (!setting) {
    return emptyOverview(owner);
  }

  const [recipients, phrases, deliveryLogs] = await Promise.all([
    getAutoSendRecipients(setting.id),
    getAutoSendPhrases(setting.id),
    getAutoSendDeliveryLogs(setting.id),
  ]);

  return {
    deliveryLogs,
    enabled: setting.enabled === 1,
    failedCount: Number(setting.failed_count ?? 0),
    lastSentAt: normalizeDate(setting.last_sent_at),
    nextRunAt: normalizeDate(setting.next_run_at),
    phrases,
    randomEnabled: setting.random_enabled === 1,
    recipientDuplicateScope: normalizeRecipientDuplicateScope(
      setting.recipient_duplicate_scope,
    ),
    recipients,
    representativeMailbox: setting.representative_mailbox,
    sendIntervalPreset: normalizeIntervalPreset(setting.send_interval_preset),
    sendIntervalMinutes: Number(setting.send_interval_minutes),
    dailySendLimit: normalizeSendCount(
      Number(setting.max_send_count),
      DEFAULT_AUTO_SEND_DAILY_SEND_LIMIT,
    ),
    dailySentCount: Number(setting.daily_sent_count ?? 0),
    sendBatchSize: normalizeSendCount(
      Number(setting.send_batch_size),
      DEFAULT_AUTO_SEND_BATCH_SIZE,
    ),
    subject: setting.subject || DEFAULT_AUTO_SEND_SUBJECT,
  } satisfies MailAutoSendOverview;
}

export async function saveAutoSendSettingsByOwnerEmail(
  ownerEmail: string,
  input: {
    enabled: boolean;
    nextRunAt?: string | null;
    phrases: AutoSendSavePhrase[];
    randomEnabled: boolean;
    recipientDuplicateScope?: string;
    recipients: AutoSendSaveRecipient[];
    sendIntervalPreset?: string;
    sendIntervalMinutes: number;
    dailySendLimit?: number;
    sendBatchSize?: number;
    // Older callers use this field. It remains the persisted daily limit.
    maxSendCount?: number;
    subject?: string;
  },
) {
  const owner = await getOwnerContextByEmail(ownerEmail);

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

  const enabled = Boolean(input.enabled);
  const randomEnabled = Boolean(input.randomEnabled);
  const recipientDuplicateScope = normalizeRecipientDuplicateScope(
    input.recipientDuplicateScope,
  );
  const sendIntervalPreset = normalizeIntervalPreset(input.sendIntervalPreset);
  const sendIntervalMinutes = intervalMinutesForPreset(
    sendIntervalPreset,
    input.sendIntervalMinutes,
  );
  const dailySendLimit = normalizeSendCount(
    Number(input.dailySendLimit ?? input.maxSendCount),
    DEFAULT_AUTO_SEND_DAILY_SEND_LIMIT,
  );
  const sendBatchSize = normalizeSendCount(
    Number(input.sendBatchSize),
    DEFAULT_AUTO_SEND_BATCH_SIZE,
  );
  const nextRunAtSql = normalizeNextRunAtSql(input.nextRunAt);
  const recipients = uniqueRecipients(input.recipients);
  const phrases = uniquePhrases(input.phrases);
  const subject = normalizeSubject(
    phrases[0]?.subjectText || input.subject || DEFAULT_AUTO_SEND_SUBJECT,
  );
  const activeRecipients = recipients.filter((recipient) => recipient.active !== false);
  const activePhrases = phrases.filter((phrase) => phrase.active !== false);
  const selectedPhrases = activePhrases.filter((phrase) => phrase.selected !== false);

  if (enabled) {
    if (!subject) {
      throw new Error("auto-send-subject-required");
    }

    if (recipients.length === 0) {
      throw new Error("auto-send-recipient-required");
    }

    if (activePhrases.length === 0) {
      throw new Error("auto-send-phrase-required");
    }

    if (randomEnabled && selectedPhrases.length === 0) {
      throw new Error("auto-send-random-phrase-required");
    }
  }

  return withTransaction(async (connection) => {
    const [existingRows] = await connection.query<
      (RowDataPacket & {
        id: number;
        max_send_count: number;
      })[]
    >(
      `
        SELECT id, max_send_count
        FROM mailbox_auto_send_settings
        WHERE owner_user_id = ?
        LIMIT 1
      `,
      [owner.ownerUserId],
    );
    const existingSetting = existingRows[0] ?? null;
    const existingId = existingSetting?.id ?? null;
    let settingId = existingId;
    let dailySentCount = 0;

    if (existingId) {
      const [dailySentRows] = await connection.query<
        (RowDataPacket & { total: number })[]
      >(
        `
          SELECT COUNT(*) AS total
          FROM mailbox_auto_send_deliveries
          WHERE setting_id = ?
            AND status = 'sent'
            AND sent_at >= CURDATE()
            AND sent_at < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
        `,
        [existingId],
      );
      dailySentCount = Number(dailySentRows[0]?.total ?? 0);
    }

    const shouldSchedule = enabled && activeRecipients.length > 0;
    const dailyLimitReached = dailySentCount >= dailySendLimit;
    const wasBlockedByDailyLimit = Boolean(
      existingSetting &&
        dailySentCount >=
          normalizeSendCount(
            Number(existingSetting.max_send_count),
            DEFAULT_AUTO_SEND_DAILY_SEND_LIMIT,
          ),
    );
    const shouldResumeSoon =
      shouldSchedule && !dailyLimitReached && wasBlockedByDailyLimit;

    if (existingId) {
      await connection.query(
        `
          UPDATE mailbox_auto_send_settings
          SET
            mailbox_id = ?,
            enabled = ?,
            subject = ?,
            random_enabled = ?,
            recipient_duplicate_scope = ?,
            send_interval_preset = ?,
            send_interval_minutes = ?,
            max_send_count = ?,
            send_batch_size = ?,
            next_run_at = CASE
              WHEN ? = 0 THEN NULL
              WHEN ? = 1 THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY)
              WHEN ? = 1 THEN DATE_ADD(NOW(), INTERVAL 1 MINUTE)
              ELSE COALESCE(?, DATE_ADD(NOW(), INTERVAL ? MINUTE))
            END,
            updated_at = NOW()
          WHERE id = ?
        `,
        [
          owner.mailboxId,
          enabled ? 1 : 0,
          subject,
          randomEnabled ? 1 : 0,
          recipientDuplicateScope,
          sendIntervalPreset,
          sendIntervalMinutes,
          dailySendLimit,
          sendBatchSize,
          shouldSchedule ? 1 : 0,
          shouldSchedule && dailyLimitReached ? 1 : 0,
          shouldResumeSoon ? 1 : 0,
          nextRunAtSql,
          sendIntervalMinutes,
          existingId,
        ],
      );
    } else {
      const [result] = await connection.query<ResultSetHeader>(
        `
          INSERT INTO mailbox_auto_send_settings (
            owner_user_id,
            mailbox_id,
            enabled,
            subject,
            random_enabled,
            recipient_duplicate_scope,
            send_interval_preset,
            send_interval_minutes,
            max_send_count,
            send_batch_size,
            next_run_at
          ) VALUES (
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            CASE
              WHEN ? = 0 THEN NULL
              WHEN ? = 1 THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY)
              ELSE COALESCE(?, DATE_ADD(NOW(), INTERVAL ? MINUTE))
            END
          )
        `,
        [
          owner.ownerUserId,
          owner.mailboxId,
          enabled ? 1 : 0,
          subject,
          randomEnabled ? 1 : 0,
          recipientDuplicateScope,
          sendIntervalPreset,
          sendIntervalMinutes,
          dailySendLimit,
          sendBatchSize,
          shouldSchedule ? 1 : 0,
          shouldSchedule && dailyLimitReached ? 1 : 0,
          nextRunAtSql,
          sendIntervalMinutes,
        ],
      );

      settingId = result.insertId;
    }

    if (!settingId) {
      throw new Error("auto-send-save-failed");
    }

    await connection.query(
      "DELETE FROM mailbox_auto_send_recipients WHERE setting_id = ?",
      [settingId],
    );
    await connection.query("DELETE FROM mailbox_auto_send_phrases WHERE setting_id = ?", [
      settingId,
    ]);

    for (const recipient of recipients) {
      await connection.query(
        `
          INSERT INTO mailbox_auto_send_recipients (
            setting_id,
            email,
            display_name,
            active
          ) VALUES (?, ?, ?, ?)
        `,
        [
          settingId,
          recipient.email,
          recipient.displayName || null,
          recipient.active !== false ? 1 : 0,
        ],
      );
    }

    for (const phrase of phrases) {
      await connection.query(
        `
          INSERT INTO mailbox_auto_send_phrases (
            setting_id,
            subject_text,
            body_text,
            active,
            selected
          ) VALUES (?, ?, ?, ?, ?)
        `,
        [
          settingId,
          phrase.subjectText || DEFAULT_AUTO_SEND_SUBJECT,
          phrase.bodyText,
          phrase.active !== false ? 1 : 0,
          phrase.selected !== false ? 1 : 0,
        ],
      );
    }

    const [savedSettings] = await connection.query<
      (RowDataPacket & { next_run_at: Date | null })[]
    >(
      "SELECT next_run_at FROM mailbox_auto_send_settings WHERE id = ? LIMIT 1",
      [settingId],
    );

    return {
      nextRunAt: normalizeDate(savedSettings[0]?.next_run_at ?? null),
    };
  });
}

async function getDueAutoSendSettings() {
  await ensureOfficialMailSchema();
  const [rows] = await getDbPool().query<AutoSendSettingRow[]>(
    `
      SELECT
        s.id,
        s.owner_user_id,
        s.mailbox_id,
        s.enabled,
        s.subject,
        s.random_enabled,
        s.send_interval_preset,
        s.send_interval_minutes,
        s.max_send_count,
        s.send_batch_size,
        s.next_run_at,
        s.last_sent_at,
        u.email AS owner_email,
        m.email AS representative_mailbox,
        0 AS daily_sent_count,
        0 AS failed_count
      FROM mailbox_auto_send_settings s
      INNER JOIN users u ON u.id = s.owner_user_id
      INNER JOIN mailboxes m ON m.id = s.mailbox_id
      WHERE s.enabled = 1
        AND s.next_run_at IS NOT NULL
        AND s.next_run_at <= NOW()
      ORDER BY s.next_run_at ASC, s.id ASC
      LIMIT 50
    `,
  );

  return rows;
}

async function claimAutoSendSetting(settingId: number) {
  const [result] = await getDbPool().query<ResultSetHeader>(
    `
      UPDATE mailbox_auto_send_settings
      SET next_run_at = DATE_ADD(NOW(), INTERVAL 2 MINUTE), updated_at = NOW()
      WHERE id = ?
        AND enabled = 1
        AND next_run_at IS NOT NULL
        AND next_run_at <= NOW()
    `,
    [settingId],
  );

  return result.affectedRows === 1;
}

async function scheduleNextRun(
  settingId: number,
  sendIntervalMinutes: number,
  sent: boolean,
  hasPendingRecipients: boolean,
) {
  await getDbPool().query(
    `
      UPDATE mailbox_auto_send_settings
      SET
        last_sent_at = CASE WHEN ? = 1 THEN NOW() ELSE last_sent_at END,
        next_run_at = CASE
          WHEN ? = 1 THEN DATE_ADD(NOW(), INTERVAL ? MINUTE)
          ELSE NULL
        END,
        updated_at = NOW()
      WHERE id = ?
    `,
    [
      sent ? 1 : 0,
      hasPendingRecipients ? 1 : 0,
      sendIntervalMinutes,
      settingId,
    ],
  );
}

async function scheduleNextRunAfterDailyLimit(
  settingId: number,
  hasPendingRecipients: boolean,
  sent: boolean,
) {
  await getDbPool().query(
    `
      UPDATE mailbox_auto_send_settings
      SET
        last_sent_at = CASE WHEN ? = 1 THEN NOW() ELSE last_sent_at END,
        next_run_at = CASE
          WHEN ? = 1 THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY)
          ELSE NULL
        END,
        updated_at = NOW()
      WHERE id = ?
    `,
    [sent ? 1 : 0, hasPendingRecipients ? 1 : 0, settingId],
  );
}

async function getTodaySentCount(settingId: number) {
  const [rows] = await getDbPool().query<(RowDataPacket & { total: number })[]>(
    `
      SELECT COUNT(*) AS total
      FROM mailbox_auto_send_deliveries
      WHERE setting_id = ?
        AND status = 'sent'
        AND sent_at >= CURDATE()
        AND sent_at < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
    `,
    [settingId],
  );

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

async function countPendingAutoSendRecipients(settingId: number) {
  const [rows] = await getDbPool().query<(RowDataPacket & { total: number })[]>(
    `
      SELECT COUNT(*) AS total
      FROM mailbox_auto_send_recipients r
      WHERE r.setting_id = ?
        AND r.active = 1
    `,
    [settingId],
  );

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

function selectPhrase(phrases: MailAutoSendPhrase[], randomEnabled: boolean) {
  const active = phrases.filter((phrase) => phrase.active);
  const pool = randomEnabled ? active.filter((phrase) => phrase.selected) : active;

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

  if (!randomEnabled || pool.length === 1) {
    return pool[0] ?? null;
  }

  return pool[Math.floor(Math.random() * pool.length)] ?? null;
}

function errorMessage(error: unknown) {
  return error instanceof Error && error.message
    ? error.message.slice(0, 2000)
    : "자동발송 처리 중 오류가 발생했습니다.";
}

async function insertDelivery(input: {
  bodyText: string;
  phraseId: number | null;
  recipientEmail: string;
  scheduledAt: Date | null;
  settingId: number;
  subject: string;
}) {
  const [result] = await getDbPool().query<ResultSetHeader>(
    `
      INSERT INTO mailbox_auto_send_deliveries (
        setting_id,
        recipient_email,
        phrase_id,
        subject,
        body_text,
        status,
        scheduled_at
      ) VALUES (?, ?, ?, ?, ?, 'sending', ?)
    `,
    [
      input.settingId,
      input.recipientEmail,
      input.phraseId,
      input.subject,
      input.bodyText,
      input.scheduledAt ?? new Date(),
    ],
  );

  return result.insertId;
}

async function updateDelivery(
  deliveryId: number,
  input: {
    errorMessage?: string | null;
    messageId?: number | null;
    status: "failed" | "sent";
  },
) {
  await getDbPool().query(
    `
      UPDATE mailbox_auto_send_deliveries
      SET
        status = ?,
        error_message = ?,
        sent_message_id = ?,
        sent_at = CASE WHEN ? = 'sent' THEN NOW() ELSE sent_at END,
        updated_at = NOW()
      WHERE id = ?
    `,
    [
      input.status,
      input.errorMessage ?? null,
      input.messageId ?? null,
      input.status,
      deliveryId,
    ],
  );
}

async function markRecipientSent(settingId: number, recipientId: number) {
  await getDbPool().query(
    `
      UPDATE mailbox_auto_send_recipients
      SET active = 0, updated_at = NOW()
      WHERE id = ?
        AND setting_id = ?
    `,
    [recipientId, settingId],
  );
}

export async function processDueAutoSendReservations() {
  const dueSettings = await getDueAutoSendSettings();
  const summary: MailAutoSendRunSummary = {
    attempted: 0,
    failed: 0,
    owners: 0,
    sent: 0,
    skipped: 0,
  };

  for (const setting of dueSettings) {
    if (!(await claimAutoSendSetting(setting.id))) {
      summary.skipped += 1;
      continue;
    }

    summary.owners += 1;

    if (!(await hasActiveGrowthPlanByOwnerEmail(setting.owner_email))) {
      await getDbPool().query(
        "UPDATE mailbox_auto_send_settings SET enabled = 0, next_run_at = NULL, updated_at = NOW() WHERE id = ?",
        [setting.id],
      );
      summary.skipped += 1;
      continue;
    }

    const [recipients, phrases] = await Promise.all([
      getAutoSendRecipients(setting.id),
      getAutoSendPhrases(setting.id),
    ]);
    const activeRecipients = recipients.filter((recipient) => recipient.active);
    const dailySendLimit = normalizeSendCount(
      Number(setting.max_send_count),
      DEFAULT_AUTO_SEND_DAILY_SEND_LIMIT,
    );
    const sendBatchSize = normalizeSendCount(
      Number(setting.send_batch_size),
      DEFAULT_AUTO_SEND_BATCH_SIZE,
    );
    const dailySentCount = await getTodaySentCount(setting.id);
    const dailyRemaining = Math.max(0, dailySendLimit - dailySentCount);

    if (dailyRemaining === 0) {
      await scheduleNextRunAfterDailyLimit(setting.id, activeRecipients.length > 0, false);
      summary.skipped += 1;
      continue;
    }

    const limitedRecipients = activeRecipients.slice(
      0,
      Math.min(sendBatchSize, dailyRemaining),
    );
    const phrase = selectPhrase(phrases, setting.random_enabled === 1);

    if (limitedRecipients.length === 0 || !phrase) {
      await scheduleNextRun(
        setting.id,
        normalizeIntervalMinutes(Number(setting.send_interval_minutes)),
        false,
        false,
      );
      summary.skipped += 1;
      continue;
    }

    let sentForSetting = false;
    const sentRecipients: string[] = [];

    for (const recipient of limitedRecipients) {
      summary.attempted += 1;
      const deliveryId = await insertDelivery({
        bodyText: phrase.bodyText,
        phraseId: phrase.id,
        recipientEmail: recipient.email,
        scheduledAt: setting.next_run_at,
        settingId: setting.id,
        subject: phrase.subjectText,
      });

      try {
        const result = await createMailboxMessage(setting.owner_email, {
          body: phrase.bodyText,
          bodyHtml: plainTextToHtml(phrase.bodyText),
          intent: "send",
          senderDisplayName: "오피셜메일",
          sendIndividually: false,
          subject: phrase.subjectText,
          to: recipient.email,
          waitForDelivery: true,
        });
        await updateDelivery(deliveryId, {
          messageId: result.messageId ?? null,
          status: "sent",
        });
        await markRecipientSent(setting.id, recipient.id);
        summary.sent += 1;
        sentForSetting = true;
        sentRecipients.push(recipient.email);
      } catch (error) {
        await updateDelivery(deliveryId, {
          errorMessage: errorMessage(error),
          status: "failed",
        });
        summary.failed += 1;
      }
    }

    if (sentRecipients.length > 0) {
      publishMailboxAutoSendCompleted({
        ownerEmail: setting.owner_email,
        mailbox: setting.representative_mailbox,
        recipientEmail: sentRecipients[0],
        deliveryCount: sentRecipients.length,
        subject: phrase.subjectText,
      });
    }

    const pendingRecipientsLeft = await countPendingAutoSendRecipients(setting.id);

    const dailyLimitReached =
      pendingRecipientsLeft > 0 &&
      (await getTodaySentCount(setting.id)) >= dailySendLimit;

    if (dailyLimitReached) {
      await scheduleNextRunAfterDailyLimit(setting.id, true, sentForSetting);
      continue;
    }

    await scheduleNextRun(
      setting.id,
      normalizeIntervalMinutes(Number(setting.send_interval_minutes)),
      sentForSetting,
      pendingRecipientsLeft > 0,
    );
  }

  return summary;
}