import "server-only";

import { after } from "next/server";
import type { PoolConnection, ResultSetHeader, RowDataPacket } from "mysql2/promise";
import { cleanupComposeUploadArtifacts, type ComposeUploadCleanupTarget } from "@/lib/mail-compose-uploads";
import { deleteMailcowMailboxAccount, setMailcowMailboxActive } from "@/lib/mailcow";
import {
  decryptMailboxPassword,
  deleteRemoteMessages,
  type RemoteMailboxCredentials,
} from "@/lib/mailbox-remote";
import { ensureOfficialMailSchema, getDbPool } from "@/lib/db";
import {
  clearTossPayBillingProfileForOwnerUserId,
  refundTossPayChargeById,
  type TossPaySubscriptionStatus,
} from "@/lib/toss-pay";

const BILLING_WITHDRAWAL_WINDOW_DAYS = 7;
const BILLING_CANCELLATION_LOCK_TIMEOUT_MINUTES = 30;
const SYSTEM_WELCOME_SUBJECT = "오피셜메일에 오신 것을 환영합니다";
const SYSTEM_WELCOME_MAILBOX_EMAIL = (
  process.env.SYSTEM_WELCOME_MAILBOX_EMAIL ?? "admin@officialsite.kr"
)
  .trim()
  .toLowerCase();

export type MailBillingCancellationRequestKind = "cancel_only" | "withdrawal_refund";
export type MailBillingCancellationRequestStatus = "pending" | "processing" | "completed" | "failed";
export type MailBillingCancellationAdminReviewStatus =
  | "not_required"
  | "pending_review"
  | "approved_full"
  | "approved_partial"
  | "rejected"
  | "cancelled_by_user";
export type MailBillingWithdrawalEligibilityReason =
  | "eligible"
  | "no-successful-charge"
  | "outside-7-day-window"
  | "usage-history-exists"
  | "request-already-pending";

export type MailBillingCancellationOverview = {
  canCancelNow: boolean;
  canWithdrawWithRefund: boolean;
  hasUsageHistory: boolean;
  latestSuccessfulChargeAmount: number | null;
  latestSuccessfulChargeAt: string | null;
  latestSuccessfulChargeId: number | null;
  pendingRequest: {
    id: number;
    kind: MailBillingCancellationRequestKind;
    requestedAt: string;
    status: MailBillingCancellationRequestStatus;
  } | null;
  subscriptionStatus: TossPaySubscriptionStatus | null;
  usageDeliveryCount: number;
  usageMessageCount: number;
  withdrawalEligibilityReason: MailBillingWithdrawalEligibilityReason;
  withdrawalEligibleUntil: string | null;
};

export type MailBillingCancellationRequestResult = {
  id: number;
  kind: MailBillingCancellationRequestKind;
  message: string;
  requestedAt: string;
  status: "completed" | "queued";
  subscriptionStatus: TossPaySubscriptionStatus | "cancelled" | null;
};

export type MailBillingCancellationRevokeResult = {
  id: number;
  kind: MailBillingCancellationRequestKind;
  message: string;
  revokedAt: string;
  status: "revoked";
  subscriptionStatus: TossPaySubscriptionStatus | "active" | null;
};

export type MailBillingCancellationAdminReviewResult = {
  approvedRefundAmount: number | null;
  id: number;
  kind: MailBillingCancellationRequestKind;
  message: string;
  reviewedAt: string;
  reviewStatus: MailBillingCancellationAdminReviewStatus;
  status: MailBillingCancellationRequestStatus;
  subscriptionStatus: TossPaySubscriptionStatus | "active" | "cancelled" | null;
};

export type MailBillingCancellationQueueRunSummary = {
  attempted: number;
  cleanupAttempted: number;
  cleanupCompleted: number;
  cleanupFailed: number;
  completed: number;
  failed: number;
  memberAccessAttempted: number;
  memberAccessCompleted: number;
  memberAccessFailed: number;
};

type OwnerUserRow = RowDataPacket & {
  email: string;
  id: number;
};

type SubscriptionRow = RowDataPacket & {
  billing_profile_id: number | null;
  id: number;
  next_charge_at: Date | null;
  owner_user_id: number;
  status: TossPaySubscriptionStatus;
};

type CancelledMemberAccessSubscriptionRow = RowDataPacket & {
  id: number;
  member_access_disabled_at: Date | null;
  member_access_ends_at: Date;
  member_access_processing_started_at: Date | null;
  owner_user_id: number;
  status: TossPaySubscriptionStatus;
};

type ManagedMemberAccessRow = RowDataPacket & {
  email: string;
  id: number;
};

type BillingProfileStateRow = RowDataPacket & {
  billing_key: string | null;
  id: number;
  status: "failed" | "inactive" | "pending" | "active" | "removed";
};

type LatestChargeRow = RowDataPacket & {
  amount: number;
  approved_at: Date | null;
  id: number;
  requested_at: Date;
};

type CountRow = RowDataPacket & {
  total: number;
};

type CancellationRequestRow = RowDataPacket & {
  admin_review_status: MailBillingCancellationAdminReviewStatus;
  approved_refund_amount: number | null;
  billing_profile_id: number | null;
  id: number;
  latest_charge_amount: number;
  latest_charge_id: number | null;
  latest_charge_requested_at: Date | null;
  owner_user_id: number;
  processing_started_at: Date | null;
  request_kind: MailBillingCancellationRequestKind;
  review_note: string | null;
  reviewed_at: Date | null;
  reviewed_by_email: string | null;
  requested_at: Date;
  requested_by_email: string;
  status: MailBillingCancellationRequestStatus;
  subscription_id: number | null;
  usage_delivery_count: number;
  usage_message_count: number;
};

type OwnerMailboxRow = RowDataPacket & {
  email: string;
  id: number;
  password_ciphertext: string | null;
};

type ManagedMemberRow = RowDataPacket & {
  email: string;
  id: number;
};

type OwnerMessageRow = RowDataPacket & {
  folder_id: number;
  id: number;
  mailbox_id: number;
  remote_folder: string | null;
  remote_uid: number | null;
};

type UploadCleanupRow = RowDataPacket & {
  id: number;
  public_url: string | null;
  storage_key: string | null;
  temp_path: string;
};

type RefundBalanceRow = RowDataPacket & {
  amount: number;
  refunded_amount: number | null;
};

type UsageCounts = {
  deliveryCount: number;
  hasUsageHistory: boolean;
  messageCount: number;
};

type WithdrawalEligibility = {
  canWithdrawWithRefund: boolean;
  eligibleUntil: Date | null;
  reason: MailBillingWithdrawalEligibilityReason;
};

type PurgeSummary = {
  deletedAssetCount: number;
  deletedManagedMemberCount: number;
  deletedMessageCount: number;
};

type WithdrawalCleanupMailboxPayload = {
  email: string;
  mailboxId: number;
  remoteDeletions: Array<{
    remoteFolder: string;
    remoteUids: number[];
  }>;
};

type WithdrawalCleanupJobPayload = {
  managedMemberEmails: string[];
  mailboxes: WithdrawalCleanupMailboxPayload[];
  uploads: ComposeUploadCleanupTarget[];
};

type WithdrawalCleanupJobRow = RowDataPacket & {
  id: number;
  owner_user_id: number;
  cancellation_request_id: number;
  status: "pending" | "processing" | "completed" | "failed";
  payload_json: string;
  sync_cutoff_at: Date;
  attempt_count: number;
  process_after_at: Date;
  processing_started_at: Date | null;
  completed_at: Date | null;
  last_error: string | null;
};

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

function addDays(value: Date, days: number) {
  return new Date(value.getTime() + days * 24 * 60 * 60 * 1000);
}

function addMonth(value: Date) {
  const next = new Date(value);
  const day = next.getUTCDate();

  next.setUTCDate(1);
  next.setUTCMonth(next.getUTCMonth() + 1);

  const lastDay = new Date(Date.UTC(next.getUTCFullYear(), next.getUTCMonth() + 1, 0)).getUTCDate();
  next.setUTCDate(Math.min(day, lastDay));

  return next;
}

function getEffectiveChargeDate(charge: LatestChargeRow | null) {
  if (!charge) {
    return null;
  }

  return charge.approved_at ?? charge.requested_at;
}

function hasPendingWithdrawalCleanupPayload(payload: WithdrawalCleanupJobPayload) {
  return (
    payload.managedMemberEmails.length > 0 ||
    payload.mailboxes.some((mailbox) => mailbox.remoteDeletions.length > 0) ||
    payload.uploads.length > 0
  );
}

function groupRemoteDeletionPayload(
  mailboxRows: OwnerMailboxRow[],
  messageRows: OwnerMessageRow[],
) {
  const mailboxIdSet = new Set(mailboxRows.map((row) => row.id));
  const remoteMessagesByMailbox = new Map<number, Map<string, number[]>>();

  for (const row of messageRows) {
    if (!row.remote_folder || !row.remote_uid || !mailboxIdSet.has(row.mailbox_id)) {
      continue;
    }

    const mailboxGroups = remoteMessagesByMailbox.get(row.mailbox_id) ?? new Map<string, number[]>();
    const folderBucket = mailboxGroups.get(row.remote_folder) ?? [];
    folderBucket.push(row.remote_uid);
    mailboxGroups.set(row.remote_folder, folderBucket);
    remoteMessagesByMailbox.set(row.mailbox_id, mailboxGroups);
  }

  return mailboxRows
    .map((mailbox) => {
      const folderGroups = remoteMessagesByMailbox.get(mailbox.id) ?? new Map<string, number[]>();

      return {
        email: mailbox.email,
        mailboxId: mailbox.id,
        remoteDeletions: [...folderGroups.entries()].map(([remoteFolder, remoteUids]) => ({
          remoteFolder,
          remoteUids,
        })),
      } satisfies WithdrawalCleanupMailboxPayload;
    })
    .filter((mailbox) => mailbox.remoteDeletions.length > 0);
}

function buildWithdrawalCleanupPayload(
  mailboxRows: OwnerMailboxRow[],
  messageRows: OwnerMessageRow[],
  assetRows: UploadCleanupRow[],
  managedMemberRows: ManagedMemberRow[],
) {
  return {
    managedMemberEmails: managedMemberRows.map((row) => row.email),
    mailboxes: groupRemoteDeletionPayload(mailboxRows, messageRows),
    uploads: assetRows.map((row) => ({
      id: row.id,
      publicUrl: row.public_url?.trim() || null,
      storageKey: row.storage_key?.trim() || null,
      tempPath: row.temp_path.trim(),
    })),
  } satisfies WithdrawalCleanupJobPayload;
}

function triggerWithdrawalCleanupQueue() {
  try {
    after(async () => {
      await processMailBillingCancellationCleanupQueue(1);
    });
  } catch {
    void processMailBillingCancellationCleanupQueue(1);
  }
}

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 getOwnerUserByEmail(email: string, connection?: PoolConnection) {
  const executor = connection ?? getDbPool();
  const [rows] = await executor.query<OwnerUserRow[]>(
    `
      SELECT id, email
      FROM users
      WHERE email = ?
      LIMIT 1
    `,
    [email.trim().toLowerCase()],
  );

  return rows[0] ?? null;
}

async function getOwnerUserById(ownerUserId: number) {
  const [rows] = await getDbPool().query<OwnerUserRow[]>(
    `
      SELECT id, email
      FROM users
      WHERE id = ?
      LIMIT 1
    `,
    [ownerUserId],
  );

  return rows[0] ?? null;
}

async function getSubscriptionByOwnerUserId(ownerUserId: number, connection?: PoolConnection) {
  const executor = connection ?? getDbPool();
  const [rows] = await executor.query<SubscriptionRow[]>(
    `
      SELECT id, owner_user_id, billing_profile_id, status, next_charge_at
      FROM mailbox_toss_pay_subscriptions
      WHERE owner_user_id = ?
      LIMIT 1
    `,
    [ownerUserId],
  );

  return rows[0] ?? null;
}

async function getBillingProfileStateByOwnerUserId(
  ownerUserId: number,
  connection?: PoolConnection,
) {
  const executor = connection ?? getDbPool();
  const [rows] = await executor.query<BillingProfileStateRow[]>(
    `
      SELECT id, status, billing_key
      FROM mailbox_toss_pay_billing_profiles
      WHERE owner_user_id = ?
      LIMIT 1
    `,
    [ownerUserId],
  );

  return rows[0] ?? null;
}

async function getLatestSuccessfulChargeByOwnerUserId(ownerUserId: number, connection?: PoolConnection) {
  const executor = connection ?? getDbPool();
  const [rows] = await executor.query<LatestChargeRow[]>(
    `
      SELECT id, amount, requested_at, approved_at
      FROM mailbox_toss_pay_billing_charges
      WHERE owner_user_id = ?
        AND status = 'success'
      ORDER BY COALESCE(approved_at, requested_at) DESC, id DESC
      LIMIT 1
    `,
    [ownerUserId],
  );

  return rows[0] ?? null;
}

async function getPendingCancellationRequestByOwnerUserId(
  ownerUserId: number,
  connection?: PoolConnection,
) {
  const executor = connection ?? getDbPool();
  const [rows] = await executor.query<CancellationRequestRow[]>(
    `
      SELECT
        id,
        owner_user_id,
        subscription_id,
        billing_profile_id,
        latest_charge_id,
        latest_charge_amount,
        approved_refund_amount,
        latest_charge_requested_at,
        request_kind,
        status,
        admin_review_status,
        requested_by_email,
        reviewed_by_email,
        review_note,
        requested_at,
        processing_started_at,
        reviewed_at,
        usage_message_count,
        usage_delivery_count
      FROM mailbox_toss_pay_billing_cancellation_requests
      WHERE owner_user_id = ?
        AND status IN ('pending', 'processing')
      ORDER BY requested_at DESC, id DESC
      LIMIT 1
    `,
    [ownerUserId],
  );

  return rows[0] ?? null;
}

async function getOwnerMailboxIds(ownerUserId: number, connection?: PoolConnection) {
  const executor = connection ?? getDbPool();
  const [rows] = await executor.query<(RowDataPacket & { id: number })[]>(
    `
      SELECT id
      FROM (
        SELECT m.id
        FROM mailboxes m
        WHERE m.user_id = ?

        UNION

        SELECT m.id
        FROM managed_team_mailboxes mtm
        INNER JOIN mailboxes m
          ON LOWER(m.email) = LOWER(mtm.email)
        WHERE mtm.owner_user_id = ?
          AND mtm.status = 'active'
      ) owner_mailboxes
    `,
    [ownerUserId, ownerUserId],
  );

  return rows.map((row) => row.id);
}

async function getUsageCountsByOwnerUserId(ownerUserId: number, connection?: PoolConnection): Promise<UsageCounts> {
  const mailboxIds = await getOwnerMailboxIds(ownerUserId, connection);

  if (mailboxIds.length === 0) {
    return {
      deliveryCount: 0,
      hasUsageHistory: false,
      messageCount: 0,
    };
  }

  const executor = connection ?? getDbPool();
  const placeholders = mailboxIds.map(() => "?").join(", ");
  const [messageRows] = await executor.query<CountRow[]>(
    `
      SELECT COUNT(*) AS total
      FROM mailbox_messages
      WHERE mailbox_id IN (${placeholders})
        AND direction IN ('inbound', 'outbound')
        AND NOT (
          direction = 'inbound'
          AND remote_uid IS NULL
          AND LOWER(from_address) = ?
          AND subject = ?
        )
    `,
    [...mailboxIds, SYSTEM_WELCOME_MAILBOX_EMAIL, SYSTEM_WELCOME_SUBJECT],
  );
  const [deliveryRows] = await executor.query<CountRow[]>(
    `
      SELECT COUNT(*) AS total
      FROM mailbox_delivery_logs
      WHERE mailbox_id IN (${placeholders})
        AND action IN ('send', 'receive')
        AND transport NOT IN ('seed', 'system')
    `,
    mailboxIds,
  );

  const messageCount = Number(messageRows[0][0]?.total ?? 0);
  const deliveryCount = Number(deliveryRows[0][0]?.total ?? 0);

  return {
    deliveryCount,
    hasUsageHistory: messageCount > 0 || deliveryCount > 0,
    messageCount,
  };
}

function resolveWithdrawalEligibility(input: {
  hasUsageHistory: boolean;
  latestCharge: LatestChargeRow | null;
  pendingRequest: CancellationRequestRow | null;
}): WithdrawalEligibility {
  if (input.pendingRequest) {
    return {
      canWithdrawWithRefund: false,
      eligibleUntil: null,
      reason: "request-already-pending",
    };
  }

  const chargeDate = getEffectiveChargeDate(input.latestCharge);

  if (!chargeDate) {
    return {
      canWithdrawWithRefund: false,
      eligibleUntil: null,
      reason: "no-successful-charge",
    };
  }

  const eligibleUntil = addDays(chargeDate, BILLING_WITHDRAWAL_WINDOW_DAYS);

  if (Date.now() > eligibleUntil.getTime()) {
    return {
      canWithdrawWithRefund: false,
      eligibleUntil,
      reason: "outside-7-day-window",
    };
  }

  if (input.hasUsageHistory) {
    return {
      canWithdrawWithRefund: false,
      eligibleUntil,
      reason: "usage-history-exists",
    };
  }

  return {
    canWithdrawWithRefund: true,
    eligibleUntil,
    reason: "eligible",
  };
}

async function insertCancellationRequest(
  connection: PoolConnection,
  input: {
    adminReviewStatus: MailBillingCancellationAdminReviewStatus;
    approvedRefundAmount: number | null;
    billingProfileId: number | null;
    eligibleUntil: Date | null;
    errorMessage: string | null;
    latestChargeAmount: number;
    latestChargeDate: Date | null;
    latestChargeId: number | null;
    ownerUserId: number;
    processedAt: Date | null;
    requestedAt: Date;
    requestedByEmail: string;
    requestKind: MailBillingCancellationRequestKind;
    reviewNote: string | null;
    reviewedAt: Date | null;
    reviewedByEmail: string | null;
    resultText: string | null;
    status: MailBillingCancellationRequestStatus;
    subscriptionId: number | null;
    usageDeliveryCount: number;
    usageMessageCount: number;
  },
) {
  const [result] = await connection.query<ResultSetHeader>(
    `
      INSERT INTO mailbox_toss_pay_billing_cancellation_requests (
        owner_user_id,
        subscription_id,
        billing_profile_id,
        latest_charge_id,
        request_kind,
        status,
        admin_review_status,
        requested_by_email,
        reviewed_by_email,
        review_note,
        usage_message_count,
        usage_delivery_count,
        latest_charge_amount,
        approved_refund_amount,
        latest_charge_requested_at,
        eligible_until,
        requested_at,
        processing_started_at,
        reviewed_at,
        processed_at,
        result_text,
        error_message
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `,
    [
      input.ownerUserId,
      input.subscriptionId,
      input.billingProfileId,
      input.latestChargeId,
      input.requestKind,
      input.status,
      input.adminReviewStatus,
      input.requestedByEmail,
      input.reviewedByEmail,
      input.reviewNote,
      input.usageMessageCount,
      input.usageDeliveryCount,
      input.latestChargeAmount,
      input.approvedRefundAmount,
      input.latestChargeDate,
      input.eligibleUntil,
      input.requestedAt,
      null,
      input.reviewedAt,
      input.processedAt,
      input.resultText,
      input.errorMessage,
    ],
  );

  return result.insertId;
}

async function cancelSubscriptionNow(connection: PoolConnection, ownerUserId: number) {
  await connection.query<ResultSetHeader>(
    `
      UPDATE mailbox_toss_pay_subscriptions
      SET
        status = 'cancelled',
        next_charge_at = NULL,
        retry_after_at = NULL,
        processing_started_at = NULL,
        updated_at = NOW()
      WHERE owner_user_id = ?
    `,
    [ownerUserId],
  );
}

async function scheduleCancelledMemberAccessDeactivation(
  connection: PoolConnection,
  input: {
    latestChargeDate: Date | null;
    ownerUserId: number;
    requestedAt: Date;
    subscription: SubscriptionRow | null;
  },
) {
  const accessEndsAt =
    input.subscription?.next_charge_at ??
    (input.latestChargeDate ? addMonth(input.latestChargeDate) : input.requestedAt);

  await connection.query<ResultSetHeader>(
    `
      UPDATE mailbox_toss_pay_subscriptions
      SET
        member_access_ends_at = ?,
        member_access_processing_started_at = NULL,
        member_access_disabled_at = NULL,
        updated_at = NOW()
      WHERE owner_user_id = ?
        AND status = 'cancelled'
    `,
    [accessEndsAt, input.ownerUserId],
  );
}

export async function ensureCancelledMemberAccessExpiryByOwnerUserId(ownerUserId: number) {
  await ensureOfficialMailSchema();

  if (!Number.isInteger(ownerUserId) || ownerUserId <= 0) {
    return null;
  }

  return await withTransaction(async (connection) => {
    const subscription = await getSubscriptionByOwnerUserId(ownerUserId, connection);

    if (!subscription || subscription.status !== "cancelled") {
      return null;
    }

    const [rows] = await connection.query<
      (RowDataPacket & { member_access_ends_at: Date | null })[]
    >(
      `
        SELECT member_access_ends_at
        FROM mailbox_toss_pay_subscriptions
        WHERE id = ?
        LIMIT 1
        FOR UPDATE
      `,
      [subscription.id],
    );

    if (rows[0]?.member_access_ends_at) {
      return rows[0].member_access_ends_at;
    }

    const latestCharge = await getLatestSuccessfulChargeByOwnerUserId(ownerUserId, connection);
    const requestedAt = new Date();
    await scheduleCancelledMemberAccessDeactivation(connection, {
      latestChargeDate: getEffectiveChargeDate(latestCharge),
      ownerUserId,
      requestedAt,
      subscription,
    });

    return subscription.next_charge_at ??
      (latestCharge ? addMonth(getEffectiveChargeDate(latestCharge) ?? requestedAt) : requestedAt);
  });
}

async function reactivateSubscriptionNow(connection: PoolConnection, ownerUserId: number) {
  const profile = await getBillingProfileStateByOwnerUserId(ownerUserId, connection);

  if (!profile || profile.status !== "active" || !profile.billing_key) {
    throw new Error("billing-profile-inactive");
  }

  await connection.query<ResultSetHeader>(
    `
      INSERT INTO mailbox_toss_pay_subscriptions (
        owner_user_id,
        billing_profile_id,
        status,
        billing_cycle,
        next_charge_at,
        retry_after_at,
        last_charged_at,
        last_charge_status,
        consecutive_failures,
        send_fail_push,
        cash_receipt,
        cash_receipt_trade_option,
        spread_out,
        metadata_text
      )
      VALUES (?, ?, 'active', 'monthly', DATE_ADD(NOW(), INTERVAL 1 MONTH), NULL, NULL, 'idle', 0, 1, 0, 'GENERAL', 0, NULL)
      ON DUPLICATE KEY UPDATE
        billing_profile_id = VALUES(billing_profile_id),
        status = 'active',
        next_charge_at = COALESCE(mailbox_toss_pay_subscriptions.next_charge_at, DATE_ADD(NOW(), INTERVAL 1 MONTH)),
        retry_after_at = NULL,
        processing_started_at = NULL,
        updated_at = NOW()
    `,
    [ownerUserId, profile.id],
  );
}

async function getRefundBalanceByChargeId(chargeId: number, connection?: PoolConnection) {
  const executor = connection ?? getDbPool();
  const [rows] = await executor.query<RefundBalanceRow[]>(
    `
      SELECT
        c.amount,
        COALESCE(SUM(CASE WHEN r.status = 'success' THEN r.amount ELSE 0 END), 0) AS refunded_amount
      FROM mailbox_toss_pay_billing_charges c
      LEFT JOIN mailbox_toss_pay_billing_refunds r
        ON r.charge_id = c.id
      WHERE c.id = ?
      GROUP BY c.id, c.amount
    `,
    [chargeId],
  );

  return rows[0] ?? null;
}

function toRemoteCredentials(mailbox: OwnerMailboxRow): RemoteMailboxCredentials {
  if (!mailbox.password_ciphertext) {
    throw new Error("billing-cancellation-mailbox-auth-missing");
  }

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

async function scheduleOwnerResourceCleanup(
  ownerUserId: number,
  requestId: number,
  syncCutoffAt: Date,
): Promise<PurgeSummary> {
  return await withTransaction(async (connection) => {
    const [mailboxes, messages, assets, managedMembers] = await Promise.all([
      connection.query<OwnerMailboxRow[]>(
        `
          SELECT id, email, password_ciphertext
          FROM mailboxes
          WHERE user_id = ?
          ORDER BY id ASC
        `,
        [ownerUserId],
      ),
      connection.query<OwnerMessageRow[]>(
        `
          SELECT
            mm.id,
            mm.mailbox_id,
            mm.folder_id,
            mm.remote_folder,
            mm.remote_uid
          FROM mailbox_messages mm
          INNER JOIN mailboxes mb
            ON mb.id = mm.mailbox_id
          WHERE mb.user_id = ?
          ORDER BY mm.id ASC
        `,
        [ownerUserId],
      ),
      connection.query<UploadCleanupRow[]>(
        `
          SELECT
            id,
            temp_path,
            storage_key,
            public_url
          FROM mailbox_uploaded_assets
          WHERE owner_user_id = ?
            AND (
              storage_status <> 'deleted'
              OR temp_path <> ''
              OR storage_key IS NOT NULL
            )
          ORDER BY id ASC
        `,
        [ownerUserId],
      ),
      connection.query<ManagedMemberRow[]>(
        `
          SELECT id, email
          FROM managed_team_mailboxes
          WHERE owner_user_id = ?
          ORDER BY id ASC
        `,
        [ownerUserId],
      ),
    ]);

    const mailboxRows = mailboxes[0];
    const messageRows = messages[0];
    const assetRows = assets[0];
    const managedMemberRows = managedMembers[0];
    const mailboxIds = mailboxRows.map((row) => row.id);
    const payload = buildWithdrawalCleanupPayload(
      mailboxRows,
      messageRows,
      assetRows,
      managedMemberRows,
    );

    if (hasPendingWithdrawalCleanupPayload(payload)) {
      await connection.query(
        `
          INSERT INTO mailbox_withdrawal_cleanup_jobs (
            owner_user_id,
            cancellation_request_id,
            status,
            payload_json,
            sync_cutoff_at,
            process_after_at,
            processing_started_at,
            completed_at,
            last_error
          )
          VALUES (?, ?, 'pending', ?, ?, NOW(), NULL, NULL, NULL)
          ON DUPLICATE KEY UPDATE
            owner_user_id = VALUES(owner_user_id),
            status = 'pending',
            payload_json = VALUES(payload_json),
            sync_cutoff_at = VALUES(sync_cutoff_at),
            process_after_at = NOW(),
            processing_started_at = NULL,
            completed_at = NULL,
            last_error = NULL,
            updated_at = NOW()
        `,
        [ownerUserId, requestId, JSON.stringify(payload), syncCutoffAt],
      );
    }

    await connection.query(
      `
        UPDATE mailbox_uploaded_assets
        SET
          mailbox_message_id = NULL,
          storage_status = 'deleted',
          deleted_at = COALESCE(deleted_at, NOW()),
          updated_at = NOW()
        WHERE owner_user_id = ?
      `,
      [ownerUserId],
    );

    if (mailboxIds.length > 0) {
      await connection.query(
        `
          DELETE FROM mailbox_delivery_logs
          WHERE mailbox_id IN (${mailboxIds.map(() => "?").join(", ")})
        `,
        mailboxIds,
      );

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

      await connection.query(
        `
          UPDATE mailbox_folders
          SET
            remote_total = 0,
            remote_unseen = 0,
            last_synced_at = NULL,
            updated_at = NOW()
          WHERE mailbox_id IN (${mailboxIds.map(() => "?").join(", ")})
        `,
        mailboxIds,
      );
    }

    await connection.query(
      `
        UPDATE mailboxes
        SET
          last_sync_at = NULL,
          last_sync_error = NULL,
          message_visibility_cutoff_at = CASE
            WHEN message_visibility_cutoff_at IS NULL OR message_visibility_cutoff_at < ?
              THEN ?
            ELSE message_visibility_cutoff_at
          END,
          updated_at = NOW()
        WHERE user_id = ?
      `,
      [syncCutoffAt, syncCutoffAt, ownerUserId],
    );

    await connection.query(
      `
        UPDATE mailbox_ai_assist_settings
        SET
          enabled = 0,
          baseline_message_id = NULL,
          last_summarized_at = NULL,
          last_relayed_at = NULL,
          updated_at = NOW()
        WHERE owner_user_id = ?
      `,
      [ownerUserId],
    );

    await connection.query(
      `
        DELETE FROM managed_team_mailboxes
        WHERE owner_user_id = ?
      `,
      [ownerUserId],
    );

    return {
      deletedAssetCount: assetRows.length,
      deletedManagedMemberCount: managedMemberRows.length,
      deletedMessageCount: messageRows.length,
    } satisfies PurgeSummary;
  });
}

async function getPendingOrStaleWithdrawalCleanupJobIds(limit: number) {
  const [rows] = await getDbPool().query<(RowDataPacket & { id: number })[]>(
    `
      SELECT id
      FROM mailbox_withdrawal_cleanup_jobs
      WHERE status IN ('pending', 'failed')
         OR (
           status = 'processing'
           AND processing_started_at < DATE_SUB(NOW(), INTERVAL ? MINUTE)
         )
      ORDER BY process_after_at ASC, id ASC
      LIMIT ?
    `,
    [BILLING_CANCELLATION_LOCK_TIMEOUT_MINUTES, Math.max(1, Math.min(50, limit))],
  );

  return rows.map((row) => row.id);
}

async function acquireWithdrawalCleanupJobLock(connection: PoolConnection, jobId: number) {
  const [result] = await connection.query<ResultSetHeader>(
    `
      UPDATE mailbox_withdrawal_cleanup_jobs
      SET
        status = 'processing',
        processing_started_at = NOW(),
        attempt_count = attempt_count + 1,
        last_error = NULL,
        updated_at = NOW()
      WHERE id = ?
        AND (
          status IN ('pending', 'failed')
          OR (
            status = 'processing'
            AND processing_started_at < DATE_SUB(NOW(), INTERVAL ? MINUTE)
          )
        )
    `,
    [jobId, BILLING_CANCELLATION_LOCK_TIMEOUT_MINUTES],
  );

  return result.affectedRows === 1;
}

async function getWithdrawalCleanupJobById(jobId: number, connection?: PoolConnection) {
  const executor = connection ?? getDbPool();
  const [rows] = await executor.query<WithdrawalCleanupJobRow[]>(
    `
      SELECT
        id,
        owner_user_id,
        cancellation_request_id,
        status,
        payload_json,
        sync_cutoff_at,
        attempt_count,
        process_after_at,
        processing_started_at,
        completed_at,
        last_error
      FROM mailbox_withdrawal_cleanup_jobs
      WHERE id = ?
      LIMIT 1
    `,
    [jobId],
  );

  return rows[0] ?? null;
}

async function completeWithdrawalCleanupJob(jobId: number) {
  await getDbPool().query(
    `
      UPDATE mailbox_withdrawal_cleanup_jobs
      SET
        status = 'completed',
        completed_at = NOW(),
        last_error = NULL,
        updated_at = NOW()
      WHERE id = ?
    `,
    [jobId],
  );
}

async function failWithdrawalCleanupJob(jobId: number, errorMessage: string) {
  await getDbPool().query(
    `
      UPDATE mailbox_withdrawal_cleanup_jobs
      SET
        status = 'failed',
        last_error = ?,
        updated_at = NOW()
      WHERE id = ?
    `,
    [errorMessage.slice(0, 4000), jobId],
  );
}

async function processWithdrawalCleanupJob(job: WithdrawalCleanupJobRow) {
  const payload = JSON.parse(job.payload_json) as WithdrawalCleanupJobPayload;

  for (const mailboxPayload of payload.mailboxes) {
    const [mailboxRows] = await getDbPool().query<OwnerMailboxRow[]>(
      `
        SELECT id, email, password_ciphertext
        FROM mailboxes
        WHERE id = ?
        LIMIT 1
      `,
      [mailboxPayload.mailboxId],
    );
    const mailbox = mailboxRows[0];

    if (!mailbox) {
      continue;
    }

    const credentials = toRemoteCredentials(mailbox);

    for (const deletion of mailboxPayload.remoteDeletions) {
      if (deletion.remoteUids.length === 0) {
        continue;
      }

      const deleted = await deleteRemoteMessages(
        credentials,
        deletion.remoteFolder,
        deletion.remoteUids,
      );

      if (!deleted) {
        throw new Error("billing-cancellation-remote-delete-failed");
      }
    }
  }

  for (const memberEmail of payload.managedMemberEmails) {
    await deleteMailcowMailboxAccount(memberEmail);
  }

  await cleanupComposeUploadArtifacts(getDbPool(), payload.uploads);
}

async function getStaleOrPendingCancellationRequestIds(limit: number) {
  const [rows] = await getDbPool().query<(RowDataPacket & { id: number })[]>(
    `
      SELECT id
      FROM mailbox_toss_pay_billing_cancellation_requests
      WHERE (
          status = 'pending'
          AND (
            request_kind <> 'withdrawal_refund'
            OR admin_review_status IN ('approved_full', 'approved_partial')
          )
        )
         OR (
           status = 'processing'
           AND processing_started_at < DATE_SUB(NOW(), INTERVAL ? MINUTE)
           AND (
             request_kind <> 'withdrawal_refund'
             OR admin_review_status IN ('approved_full', 'approved_partial')
           )
         )
      ORDER BY requested_at ASC, id ASC
      LIMIT ?
    `,
    [BILLING_CANCELLATION_LOCK_TIMEOUT_MINUTES, Math.max(1, Math.min(50, limit))],
  );

  return rows.map((row) => row.id);
}

async function acquireCancellationRequestLock(connection: PoolConnection, requestId: number) {
  const [result] = await connection.query<ResultSetHeader>(
    `
      UPDATE mailbox_toss_pay_billing_cancellation_requests
      SET
        status = 'processing',
        processing_started_at = NOW(),
        error_message = NULL,
        updated_at = NOW()
      WHERE id = ?
        AND (
          (
            status = 'pending'
            AND (
              request_kind <> 'withdrawal_refund'
              OR admin_review_status IN ('approved_full', 'approved_partial')
            )
          )
          OR (
            status = 'processing'
            AND processing_started_at < DATE_SUB(NOW(), INTERVAL ? MINUTE)
            AND (
              request_kind <> 'withdrawal_refund'
              OR admin_review_status IN ('approved_full', 'approved_partial')
            )
          )
        )
    `,
    [requestId, BILLING_CANCELLATION_LOCK_TIMEOUT_MINUTES],
  );

  return result.affectedRows === 1;
}

async function getCancellationRequestById(requestId: number, connection?: PoolConnection) {
  const executor = connection ?? getDbPool();
  const [rows] = await executor.query<CancellationRequestRow[]>(
    `
      SELECT
        id,
        owner_user_id,
        subscription_id,
        billing_profile_id,
        latest_charge_id,
        latest_charge_amount,
        approved_refund_amount,
        latest_charge_requested_at,
        request_kind,
        status,
        admin_review_status,
        requested_by_email,
        reviewed_by_email,
        review_note,
        requested_at,
        processing_started_at,
        reviewed_at,
        usage_message_count,
        usage_delivery_count
      FROM mailbox_toss_pay_billing_cancellation_requests
      WHERE id = ?
      LIMIT 1
    `,
    [requestId],
  );

  return rows[0] ?? null;
}

async function completeCancellationRequest(requestId: number, resultText: string) {
  await getDbPool().query(
    `
      UPDATE mailbox_toss_pay_billing_cancellation_requests
      SET
        status = 'completed',
        processed_at = NOW(),
        result_text = ?,
        error_message = NULL,
        updated_at = NOW()
      WHERE id = ?
    `,
    [resultText.slice(0, 4000), requestId],
  );
}

async function failCancellationRequest(requestId: number, errorMessage: string) {
  await getDbPool().query(
    `
      UPDATE mailbox_toss_pay_billing_cancellation_requests
      SET
        status = 'failed',
        processed_at = NOW(),
        error_message = ?,
        updated_at = NOW()
      WHERE id = ?
    `,
    [errorMessage.slice(0, 4000), requestId],
  );
}

async function processApprovedWithdrawalCancellationRequest(request: CancellationRequestRow) {
  if (!request.latest_charge_id) {
    throw new Error("billing-cancellation-charge-not-found");
  }

  const refundBalance = await getRefundBalanceByChargeId(request.latest_charge_id);

  if (!refundBalance) {
    throw new Error("billing-cancellation-charge-not-found");
  }

  const remainingRefundableAmount = Math.max(
    0,
    refundBalance.amount - Number(refundBalance.refunded_amount ?? 0),
  );
  const approvedRefundAmount =
    request.admin_review_status === "approved_partial"
      ? Math.min(
          remainingRefundableAmount,
          Math.max(0, Number(request.approved_refund_amount ?? 0)),
        )
      : remainingRefundableAmount;

  await withTransaction(async (connection) => {
    await cancelSubscriptionNow(connection, request.owner_user_id);
  });

  if (approvedRefundAmount > 0) {
    await refundTossPayChargeById({
      amount: approvedRefundAmount,
      chargeId: request.latest_charge_id,
      reason:
        request.admin_review_status === "approved_partial"
          ? "최고관리자 승인 부분 환불 청약철회"
          : "7일 이내 미사용 청약철회",
      requestedByEmail: request.reviewed_by_email ?? request.requested_by_email,
    });
  }

  await withTransaction(async (connection) => {
    await clearTossPayBillingProfileForOwnerUserId(
      request.owner_user_id,
      connection,
    );
  });

  const syncCutoffAt = new Date();
  let purgeSummary: PurgeSummary = {
    deletedAssetCount: 0,
    deletedManagedMemberCount: 0,
    deletedMessageCount: 0,
  };
  let cleanupError: string | null = null;

  try {
    purgeSummary = await scheduleOwnerResourceCleanup(
      request.owner_user_id,
      request.id,
      syncCutoffAt,
    );
    triggerWithdrawalCleanupQueue();
  } catch (error) {
    cleanupError =
      error instanceof Error ? error.message : "billing-cancellation-cleanup-failed";
  }

  await completeCancellationRequest(
    request.id,
    JSON.stringify({
      cleanupError,
      deletedAssetCount: purgeSummary.deletedAssetCount,
      deletedManagedMemberCount: purgeSummary.deletedManagedMemberCount,
      deletedMessageCount: purgeSummary.deletedMessageCount,
      refundedAmount: approvedRefundAmount,
      syncCutoffAt: syncCutoffAt.toISOString(),
    }),
  );
}

export async function getMailBillingCancellationOverviewByOwnerEmail(
  email: string,
): Promise<MailBillingCancellationOverview | null> {
  await ensureOfficialMailSchema();

  const owner = await getOwnerUserByEmail(email);

  if (!owner) {
    return null;
  }

  const [subscription, pendingRequest, latestCharge, usageCounts] = await Promise.all([
    getSubscriptionByOwnerUserId(owner.id),
    getPendingCancellationRequestByOwnerUserId(owner.id),
    getLatestSuccessfulChargeByOwnerUserId(owner.id),
    getUsageCountsByOwnerUserId(owner.id),
  ]);
  const withdrawalEligibility = resolveWithdrawalEligibility({
    hasUsageHistory: usageCounts.hasUsageHistory,
    latestCharge,
    pendingRequest,
  });

  return {
    canCancelNow:
      !pendingRequest &&
      Boolean(subscription) &&
      subscription.status !== "cancelled",
    canWithdrawWithRefund: withdrawalEligibility.canWithdrawWithRefund,
    hasUsageHistory: usageCounts.hasUsageHistory,
    latestSuccessfulChargeAmount: latestCharge?.amount ?? null,
    latestSuccessfulChargeAt: normalizeDate(getEffectiveChargeDate(latestCharge)),
    latestSuccessfulChargeId: latestCharge?.id ?? null,
    pendingRequest: pendingRequest
      ? {
          id: pendingRequest.id,
          kind: pendingRequest.request_kind,
          requestedAt: pendingRequest.requested_at.toISOString(),
          status: pendingRequest.status,
        }
      : null,
    subscriptionStatus: subscription?.status ?? null,
    usageDeliveryCount: usageCounts.deliveryCount,
    usageMessageCount: usageCounts.messageCount,
    withdrawalEligibilityReason: withdrawalEligibility.reason,
    withdrawalEligibleUntil: normalizeDate(withdrawalEligibility.eligibleUntil),
  };
}

export async function requestMailBillingCancellationByOwnerEmail(input: {
  email: string;
  kind: MailBillingCancellationRequestKind;
  requestedByEmail: string;
}): Promise<MailBillingCancellationRequestResult> {
  await ensureOfficialMailSchema();

  const owner = await getOwnerUserByEmail(input.email);

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

  if (input.kind === "withdrawal_refund") {
    const prepared = await withTransaction(async (connection) => {
      const subscription = await getSubscriptionByOwnerUserId(owner.id, connection);
      const latestCharge = await getLatestSuccessfulChargeByOwnerUserId(owner.id, connection);
      const pendingRequest = await getPendingCancellationRequestByOwnerUserId(owner.id, connection);
      const usageCounts = await getUsageCountsByOwnerUserId(owner.id, connection);

      if (pendingRequest) {
        throw new Error("billing-cancellation-request-pending");
      }

      const requestedAt = new Date();
      const latestChargeDate = getEffectiveChargeDate(latestCharge);
      const withdrawalEligibility = resolveWithdrawalEligibility({
        hasUsageHistory: usageCounts.hasUsageHistory,
        latestCharge,
        pendingRequest,
      });

      if (!withdrawalEligibility.canWithdrawWithRefund) {
        throw new Error(`billing-cancellation-withdrawal-${withdrawalEligibility.reason}`);
      }

      await cancelSubscriptionNow(connection, owner.id);

      const requestId = await insertCancellationRequest(connection, {
        adminReviewStatus: "approved_full",
        approvedRefundAmount: latestCharge?.amount ?? 0,
        billingProfileId: subscription?.billing_profile_id ?? null,
        eligibleUntil: withdrawalEligibility.eligibleUntil,
        errorMessage: null,
        latestChargeAmount: latestCharge?.amount ?? 0,
        latestChargeDate,
        latestChargeId: latestCharge?.id ?? null,
        ownerUserId: owner.id,
        processedAt: null,
        requestedAt,
        requestedByEmail: input.requestedByEmail.trim().toLowerCase(),
        requestKind: "withdrawal_refund",
        reviewNote: "policy-auto-withdrawal",
        reviewedAt: requestedAt,
        reviewedByEmail: null,
        resultText: "auto-approved-withdrawal",
        status: "pending",
        subscriptionId: subscription?.id ?? null,
        usageDeliveryCount: usageCounts.deliveryCount,
        usageMessageCount: usageCounts.messageCount,
      });

      return {
        latestChargeId: latestCharge?.id ?? null,
        requestedAt,
        requestId,
      };
    });

    const locked = await withTransaction(async (connection) => {
      return await acquireCancellationRequestLock(connection, prepared.requestId);
    });

    if (!locked) {
      throw new Error("billing-cancellation-withdrawal-processing-failed");
    }

    try {
      const request = await getCancellationRequestById(prepared.requestId);

      if (!request) {
        throw new Error("billing-cancellation-request-not-found");
      }

      await processApprovedWithdrawalCancellationRequest(request);
    } catch (error) {
      await failCancellationRequest(
        prepared.requestId,
        error instanceof Error ? error.message : "billing-cancellation-processing-failed",
      );

      const refundBalance = prepared.latestChargeId
        ? await getRefundBalanceByChargeId(prepared.latestChargeId)
        : null;
      const refundedAmount = Number(refundBalance?.refunded_amount ?? 0);

      if (refundedAmount <= 0) {
        await withTransaction(async (connection) => {
          await reactivateSubscriptionNow(connection, owner.id);
        });
      }

      throw new Error("billing-cancellation-withdrawal-processing-failed");
    }

    return {
      id: prepared.requestId,
      kind: "withdrawal_refund",
      message:
        "전액 청약철회가 완료되었습니다. 결제 환불과 무료 플랜 복귀가 바로 반영되며, 데이터 정리는 순차 처리됩니다.",
      requestedAt: prepared.requestedAt.toISOString(),
      status: "completed",
      subscriptionStatus: "cancelled",
    };
  }

  return await withTransaction(async (connection) => {
    const subscription = await getSubscriptionByOwnerUserId(owner.id, connection);
    const latestCharge = await getLatestSuccessfulChargeByOwnerUserId(owner.id, connection);
    const pendingRequest = await getPendingCancellationRequestByOwnerUserId(owner.id, connection);
    const usageCounts = await getUsageCountsByOwnerUserId(owner.id, connection);

    if (pendingRequest) {
      throw new Error("billing-cancellation-request-pending");
    }

    const requestedAt = new Date();
    const latestChargeDate = getEffectiveChargeDate(latestCharge);
    const withdrawalEligibility = resolveWithdrawalEligibility({
      hasUsageHistory: usageCounts.hasUsageHistory,
      latestCharge,
      pendingRequest,
    });

    await cancelSubscriptionNow(connection, owner.id);
    await scheduleCancelledMemberAccessDeactivation(connection, {
      latestChargeDate,
      ownerUserId: owner.id,
      requestedAt,
      subscription,
    });
    await clearTossPayBillingProfileForOwnerUserId(owner.id, connection);

    const requestId = await insertCancellationRequest(connection, {
      adminReviewStatus: "not_required",
      approvedRefundAmount: null,
      billingProfileId: subscription?.billing_profile_id ?? null,
      eligibleUntil: withdrawalEligibility.eligibleUntil,
      errorMessage: null,
      latestChargeAmount: latestCharge?.amount ?? 0,
      latestChargeDate,
      latestChargeId: latestCharge?.id ?? null,
      ownerUserId: owner.id,
      processedAt: requestedAt,
      requestedAt,
      requestedByEmail: input.requestedByEmail.trim().toLowerCase(),
      requestKind: "cancel_only",
      reviewNote: null,
      reviewedAt: requestedAt,
      reviewedByEmail: null,
      resultText: "subscription-cancelled",
      status: "completed",
      subscriptionId: subscription?.id ?? null,
      usageDeliveryCount: usageCounts.deliveryCount,
      usageMessageCount: usageCounts.messageCount,
    });

    return {
      id: requestId,
      kind: "cancel_only",
      message: "정기결제를 해지했습니다. 다음 자동 청구는 중단됩니다.",
      requestedAt: requestedAt.toISOString(),
      status: "completed",
      subscriptionStatus: "cancelled",
    };
  });
}

export async function revokePendingMailBillingCancellationByOwnerEmail(input: {
  email: string;
  requestedByEmail: string;
}): Promise<MailBillingCancellationRevokeResult> {
  await ensureOfficialMailSchema();

  const owner = await getOwnerUserByEmail(input.email);

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

  return await withTransaction(async (connection) => {
    const pendingRequest = await getPendingCancellationRequestByOwnerUserId(owner.id, connection);

    if (!pendingRequest) {
      throw new Error("billing-cancellation-request-not-found");
    }

    if (pendingRequest.status !== "pending") {
      throw new Error("billing-cancellation-request-not-pending");
    }

    if (pendingRequest.admin_review_status !== "pending_review") {
      throw new Error("billing-cancellation-request-already-reviewed");
    }

    const revokedAt = new Date();

    await reactivateSubscriptionNow(connection, owner.id);
    await connection.query<ResultSetHeader>(
      `
        UPDATE mailbox_toss_pay_billing_cancellation_requests
        SET
          admin_review_status = 'cancelled_by_user',
          status = 'completed',
          reviewed_by_email = NULL,
          review_note = NULL,
          reviewed_at = ?,
          processed_at = ?,
          result_text = ?,
          error_message = NULL,
          updated_at = NOW()
        WHERE id = ?
      `,
      [
        revokedAt,
        revokedAt,
        `revoked-by-user:${input.requestedByEmail.trim().toLowerCase()}`,
        pendingRequest.id,
      ],
    );

    return {
      id: pendingRequest.id,
      kind: pendingRequest.request_kind,
      message: "취소 요청을 취소했습니다. 성장 플랜과 자동결제를 다시 유지합니다.",
      revokedAt: revokedAt.toISOString(),
      status: "revoked",
      subscriptionStatus: "active",
    };
  });
}

export async function reviewMailBillingCancellationRequestById(input: {
  action: "approve_full" | "approve_partial" | "reject";
  note?: string | null;
  refundAmount?: number | null;
  requestId: number;
  reviewedByEmail: string;
}): Promise<MailBillingCancellationAdminReviewResult> {
  await ensureOfficialMailSchema();

  const trimmedNote = input.note?.trim() ?? "";
  const normalizedNote = trimmedNote ? trimmedNote.slice(0, 255) : null;
  const normalizedReviewer = input.reviewedByEmail.trim().toLowerCase();

  return await withTransaction(async (connection) => {
    const request = await getCancellationRequestById(input.requestId, connection);

    if (!request) {
      throw new Error("billing-cancellation-request-not-found");
    }

    if (request.request_kind !== "withdrawal_refund") {
      throw new Error("billing-cancellation-review-not-supported");
    }

    if (request.status !== "pending") {
      throw new Error("billing-cancellation-request-not-pending");
    }

    if (request.admin_review_status !== "pending_review") {
      throw new Error("billing-cancellation-request-already-reviewed");
    }

    const reviewedAt = new Date();

    if (input.action === "reject") {
      await reactivateSubscriptionNow(connection, request.owner_user_id);
      await connection.query<ResultSetHeader>(
        `
          UPDATE mailbox_toss_pay_billing_cancellation_requests
          SET
            admin_review_status = 'rejected',
            status = 'completed',
            reviewed_by_email = ?,
            review_note = ?,
            reviewed_at = ?,
            processed_at = ?,
            approved_refund_amount = NULL,
            result_text = ?,
            error_message = NULL,
            updated_at = NOW()
          WHERE id = ?
        `,
        [
          normalizedReviewer,
          normalizedNote,
          reviewedAt,
          reviewedAt,
          `rejected-by-admin:${normalizedReviewer}`,
          request.id,
        ],
      );

      return {
        approvedRefundAmount: null,
        id: request.id,
        kind: request.request_kind,
        message: "청약철회 요청을 반려했습니다. 구독과 자동결제는 다시 유지됩니다.",
        reviewedAt: reviewedAt.toISOString(),
        reviewStatus: "rejected",
        status: "completed",
        subscriptionStatus: "active",
      };
    }

    const refundBalance = request.latest_charge_id
      ? await getRefundBalanceByChargeId(request.latest_charge_id, connection)
      : null;
    const remainingRefundableAmount = Math.max(
      0,
      Number(refundBalance?.amount ?? request.latest_charge_amount ?? 0) -
        Number(refundBalance?.refunded_amount ?? 0),
    );

    let approvedRefundAmount = remainingRefundableAmount;
    let reviewStatus: MailBillingCancellationAdminReviewStatus = "approved_full";

    if (input.action === "approve_partial") {
      const requestedAmount = Math.floor(Number(input.refundAmount ?? 0));

      if (!Number.isFinite(requestedAmount) || requestedAmount <= 0) {
        throw new Error("billing-cancellation-partial-refund-amount-invalid");
      }

      if (requestedAmount > remainingRefundableAmount) {
        throw new Error("billing-cancellation-partial-refund-amount-exceeds-remaining");
      }

      approvedRefundAmount = requestedAmount;
      reviewStatus = "approved_partial";
    }

    await connection.query<ResultSetHeader>(
      `
        UPDATE mailbox_toss_pay_billing_cancellation_requests
        SET
          admin_review_status = ?,
          reviewed_by_email = ?,
          review_note = ?,
          reviewed_at = ?,
          approved_refund_amount = ?,
          result_text = ?,
          error_message = NULL,
          updated_at = NOW()
        WHERE id = ?
      `,
      [
        reviewStatus,
        normalizedReviewer,
        normalizedNote,
        reviewedAt,
        approvedRefundAmount,
        `${reviewStatus === "approved_partial" ? "approved-partial" : "approved-full"}:${normalizedReviewer}`,
        request.id,
      ],
    );

    return {
      approvedRefundAmount,
      id: request.id,
      kind: request.request_kind,
      message:
        reviewStatus === "approved_partial"
          ? `${approvedRefundAmount.toLocaleString("ko-KR")}원 부분 환불 청약철회를 승인했습니다. 환불과 데이터 정리는 취소 큐에서 순차 처리됩니다.`
          : "전액 청약철회를 승인했습니다. 환불과 데이터 정리는 취소 큐에서 순차 처리됩니다.",
      reviewedAt: reviewedAt.toISOString(),
      reviewStatus,
      status: "pending",
      subscriptionStatus: "cancelled",
    };
  });
}

export async function processMailBillingCancellationCleanupQueue(limit = 10) {
  await ensureOfficialMailSchema();

  const jobIds = await getPendingOrStaleWithdrawalCleanupJobIds(limit);
  const summary = {
    attempted: 0,
    completed: 0,
    failed: 0,
  };

  for (const jobId of jobIds) {
    const locked = await withTransaction(async (connection) => {
      return await acquireWithdrawalCleanupJobLock(connection, jobId);
    });

    if (!locked) {
      continue;
    }

    summary.attempted += 1;

    try {
      const job = await getWithdrawalCleanupJobById(jobId);

      if (!job) {
        throw new Error("billing-cancellation-cleanup-job-not-found");
      }

      await processWithdrawalCleanupJob(job);
      await completeWithdrawalCleanupJob(job.id);
      summary.completed += 1;
    } catch (error) {
      summary.failed += 1;
      await failWithdrawalCleanupJob(
        jobId,
        error instanceof Error ? error.message : "billing-cancellation-cleanup-failed",
      );
    }
  }

  return summary;
}

async function getDueCancelledMemberAccessSubscriptionIds(limit: number) {
  const [rows] = await getDbPool().query<(RowDataPacket & { id: number })[]>(
    `
      SELECT id
      FROM mailbox_toss_pay_subscriptions
      WHERE status = 'cancelled'
        AND member_access_ends_at IS NOT NULL
        AND member_access_ends_at <= NOW()
        AND member_access_disabled_at IS NULL
        AND (
          member_access_processing_started_at IS NULL
          OR member_access_processing_started_at < DATE_SUB(NOW(), INTERVAL ? MINUTE)
        )
      ORDER BY member_access_ends_at ASC, id ASC
      LIMIT ?
    `,
    [BILLING_CANCELLATION_LOCK_TIMEOUT_MINUTES, Math.max(1, Math.min(50, limit))],
  );

  return rows.map((row) => row.id);
}

async function acquireCancelledMemberAccessLock(connection: PoolConnection, subscriptionId: number) {
  const [result] = await connection.query<ResultSetHeader>(
    `
      UPDATE mailbox_toss_pay_subscriptions
      SET
        member_access_processing_started_at = NOW(),
        updated_at = NOW()
      WHERE id = ?
        AND status = 'cancelled'
        AND member_access_ends_at IS NOT NULL
        AND member_access_ends_at <= NOW()
        AND member_access_disabled_at IS NULL
        AND (
          member_access_processing_started_at IS NULL
          OR member_access_processing_started_at < DATE_SUB(NOW(), INTERVAL ? MINUTE)
        )
    `,
    [subscriptionId, BILLING_CANCELLATION_LOCK_TIMEOUT_MINUTES],
  );

  return result.affectedRows === 1;
}

async function getCancelledMemberAccessSubscriptionById(subscriptionId: number) {
  const [rows] = await getDbPool().query<CancelledMemberAccessSubscriptionRow[]>(
    `
      SELECT
        id,
        owner_user_id,
        status,
        member_access_ends_at,
        member_access_processing_started_at,
        member_access_disabled_at
      FROM mailbox_toss_pay_subscriptions
      WHERE id = ?
      LIMIT 1
    `,
    [subscriptionId],
  );

  return rows[0] ?? null;
}

async function clearCancelledMemberAccessSchedule(subscriptionId: number) {
  await getDbPool().query(
    `
      UPDATE mailbox_toss_pay_subscriptions
      SET
        member_access_ends_at = NULL,
        member_access_processing_started_at = NULL,
        member_access_disabled_at = NULL,
        updated_at = NOW()
      WHERE id = ?
    `,
    [subscriptionId],
  );
}

async function releaseCancelledMemberAccessLock(subscriptionId: number) {
  await getDbPool().query(
    `
      UPDATE mailbox_toss_pay_subscriptions
      SET
        member_access_processing_started_at = NULL,
        updated_at = NOW()
      WHERE id = ?
        AND member_access_disabled_at IS NULL
    `,
    [subscriptionId],
  );
}

async function disableCancelledPlanMembers(subscription: CancelledMemberAccessSubscriptionRow) {
  if (subscription.status !== "cancelled") {
    await clearCancelledMemberAccessSchedule(subscription.id);
    return;
  }

  const [members] = await getDbPool().query<ManagedMemberAccessRow[]>(
    `
      SELECT id, email
      FROM managed_team_mailboxes
      WHERE owner_user_id = ?
        AND status = 'active'
      ORDER BY id ASC
    `,
    [subscription.owner_user_id],
  );

  for (const member of members) {
    await setMailcowMailboxActive(member.email, false);
  }

  await withTransaction(async (connection) => {
    await connection.query(
      `
        UPDATE managed_team_mailboxes
        SET status = 'disabled', updated_at = NOW()
        WHERE owner_user_id = ?
          AND status = 'active'
      `,
      [subscription.owner_user_id],
    );
    await connection.query(
      `
        UPDATE mailboxes m
        INNER JOIN managed_team_mailboxes mtm
          ON LOWER(mtm.email) = LOWER(m.email)
        SET
          m.status = 'disabled',
          m.last_sync_error = '성장플랜 해지로 멤버 메일함 사용이 중지되었습니다.',
          m.updated_at = NOW()
        WHERE mtm.owner_user_id = ?
          AND mtm.status = 'disabled'
      `,
      [subscription.owner_user_id],
    );
    await connection.query(
      `
        UPDATE users u
        INNER JOIN managed_team_mailboxes mtm
          ON LOWER(mtm.email) = LOWER(u.email)
        SET u.mail_configured = 0, u.updated_at = NOW()
        WHERE mtm.owner_user_id = ?
          AND mtm.status = 'disabled'
      `,
      [subscription.owner_user_id],
    );
    await connection.query(
      `
        UPDATE mailbox_toss_pay_subscriptions
        SET
          member_access_processing_started_at = NULL,
          member_access_disabled_at = NOW(),
          updated_at = NOW()
        WHERE id = ?
          AND status = 'cancelled'
      `,
      [subscription.id],
    );
  });
}

async function processCancelledMemberAccessQueue(limit: number) {
  const subscriptionIds = await getDueCancelledMemberAccessSubscriptionIds(limit);
  const summary = { attempted: 0, completed: 0, failed: 0 };

  for (const subscriptionId of subscriptionIds) {
    const locked = await withTransaction(async (connection) => {
      return await acquireCancelledMemberAccessLock(connection, subscriptionId);
    });

    if (!locked) continue;

    summary.attempted += 1;

    try {
      const subscription = await getCancelledMemberAccessSubscriptionById(subscriptionId);

      if (!subscription) {
        throw new Error("billing-cancellation-subscription-not-found");
      }

      await disableCancelledPlanMembers(subscription);
      summary.completed += 1;
    } catch {
      summary.failed += 1;
      await releaseCancelledMemberAccessLock(subscriptionId);
    }
  }

  return summary;
}

export async function processMailBillingCancellationQueue(
  limit = 10,
): Promise<MailBillingCancellationQueueRunSummary> {
  await ensureOfficialMailSchema();

  const requestIds = await getStaleOrPendingCancellationRequestIds(limit);
  const summary: MailBillingCancellationQueueRunSummary = {
    attempted: 0,
    cleanupAttempted: 0,
    cleanupCompleted: 0,
    cleanupFailed: 0,
    completed: 0,
    failed: 0,
    memberAccessAttempted: 0,
    memberAccessCompleted: 0,
    memberAccessFailed: 0,
  };

  for (const requestId of requestIds) {
    const locked = await withTransaction(async (connection) => {
      return await acquireCancellationRequestLock(connection, requestId);
    });

    if (!locked) {
      continue;
    }

    summary.attempted += 1;

    try {
      const request = await getCancellationRequestById(requestId);

      if (!request) {
        throw new Error("billing-cancellation-request-not-found");
      }

      if (request.request_kind !== "withdrawal_refund") {
        await completeCancellationRequest(request.id, "subscription-cancelled");
        summary.completed += 1;
        continue;
      }

      const owner = await getOwnerUserById(request.owner_user_id);

      if (!owner) {
        throw new Error("billing-cancellation-owner-not-found");
      }

      if (!request.latest_charge_id) {
        throw new Error("billing-cancellation-charge-not-found");
      }
      await processApprovedWithdrawalCancellationRequest(request);
      summary.completed += 1;
    } catch (error) {
      summary.failed += 1;
      await failCancellationRequest(
        requestId,
        error instanceof Error ? error.message : "billing-cancellation-processing-failed",
      );
    }
  }

  const cleanupSummary = await processMailBillingCancellationCleanupQueue(limit);
  summary.cleanupAttempted = cleanupSummary.attempted;
  summary.cleanupCompleted = cleanupSummary.completed;
  summary.cleanupFailed = cleanupSummary.failed;

  const memberAccessSummary = await processCancelledMemberAccessQueue(limit);
  summary.memberAccessAttempted = memberAccessSummary.attempted;
  summary.memberAccessCompleted = memberAccessSummary.completed;
  summary.memberAccessFailed = memberAccessSummary.failed;

  return summary;
}