import "server-only";

import { createHash, randomBytes } from "crypto";
import type { PoolConnection, ResultSetHeader, RowDataPacket } from "mysql2/promise";
import { ensureOfficialMailSchema, getDbPool } from "@/lib/db";
import { renderBillingFailureEmail } from "@/lib/billing-failure-email";
import {
  processPendingBillingMemberSuspensions,
  processPendingBillingMemberRestorations,
  restoreManagedMembersAfterBillingRecovery,
} from "@/lib/billing-member-access";
import {
  BILLING_GRACE_RETRY_LIMIT,
  createBillingGraceRetrySlots,
  formatKstDate,
  getBillingGraceRetrySlot,
  isLastBillingRetryOfKstDay,
} from "@/lib/billing-retry-schedule";
import { assertDomainAssistanceAllowsBillingChangesForOwnerUserId } from "@/lib/domain-assistance-billing-lock";
import { recordMarketingLifecycleEvent } from "@/lib/marketing-analytics";
import {
  countMailBillingSeatsFromManagedMembers,
  createMailBillingChargeSummary,
  createMailBillingPreview,
  resolveMailBillingSeatChargeAmount,
} from "@/lib/mail-billing";
import {
  MAIL_BILLING_HISTORY_PAGE_SIZE,
  resolveMailBillingHistoryState,
  type MailBillingHistoryRangePreset,
} from "@/lib/mail-billing-history";
import { syncMailcowMailboxQuotasForOwnerUserId } from "@/lib/mail-plan-quota";
import {
  hasOperationalBenefitByOwnerEmail,
  hasOperationalBenefitByOwnerUserId,
} from "@/lib/mail-operational-benefit";
import { getMailAbsoluteUrl, getMailAppUrl, isLocalOrigin } from "@/lib/mail-urls";
import {
  MAIL_GROWTH_PLAN_CHARGE_PER_MEMBER,
  MAIL_GROWTH_PLAN_NET_PRICE_PER_MEMBER,
} from "@/lib/pricing";
import { sendOfficialMailNotificationEmail } from "@/lib/official-mail";
import {
  cancelTossPaymentsPayment,
  chargeTossPaymentsBillingKey,
  createTossCustomerKey,
  deleteTossPaymentsBillingKey,
  getTossPaymentsClientKey,
  getTossPaymentsClientKeySource,
  getTossPaymentsEnvironment,
  issueTossPaymentsBillingKey,
  TossPaymentsRequestError,
} from "@/lib/toss-payments";

const TOSS_PAY_BILLING_CREATE_URL = "https://pay.toss.im/api/v1/billing-key";
const TOSS_PAY_BILLING_STATUS_URL = "https://pay.toss.im/api/v1/billing-key/status";
const TOSS_PAY_BILLING_BILL_URL = "https://pay.toss.im/api/v1/billing-key/bill";
const TOSS_PAY_REFUND_URL = "https://pay.toss.im/api/v2/refunds";
const TOSS_PAY_DOCS_TEST_API_KEY = "sk_test_w5lNQylNqa5lNQe013Nq";
const BILLING_PROFILE_DISPLAY_ID = "official-mail-growth";
const BILLING_PRODUCT_NAME = "성장플랜 자동결제";
const DEFAULT_MANUAL_TEST_AMOUNT = MAIL_GROWTH_PLAN_CHARGE_PER_MEMBER;
const DEFAULT_TOSS_PAYMENTS_REVIEW_EMAILS = [
  "review@toss-review.officialsite.kr",
];

const TOSS_PAYMENTS_REVIEW_EMAILS = new Set([
  ...DEFAULT_TOSS_PAYMENTS_REVIEW_EMAILS,
  ...(process.env.TOSS_PAYMENTS_REVIEW_EMAILS ?? "")
    .split(",")
    .map((email) => email.trim().toLowerCase())
    .filter(Boolean),
]);

export function isTossPaymentsBillingEnabledForEmail(
  email: string | null | undefined,
) {
  return TOSS_PAYMENTS_REVIEW_EMAILS.has(email?.trim().toLowerCase() ?? "");
}

async function syncGrowthPlanMailboxQuotas(ownerUserId: number) {
  await syncMailcowMailboxQuotasForOwnerUserId(ownerUserId, "growth").catch((error) => {
    console.error(
      `[mailbox-quota] owner=${ownerUserId} growth activation sync failed`,
      error instanceof Error ? error.message : error,
    );
  });
}

async function reconcileExternalMailAccessAfterBillingChange(ownerUserId: number) {
  try {
    const [rows] = await getDbPool().query<(RowDataPacket & { email: string })[]>(
      "SELECT email FROM users WHERE id = ? LIMIT 1",
      [ownerUserId],
    );
    if (!rows[0]?.email) return;
    // Keep this import dynamic: mail-external-access checks entitlement through
    // this billing module, and a static import would create a module cycle.
    const { synchronizeMailboxExternalAccessByOwnerEmail } = await import(
      "@/lib/mail-external-access"
    );
    await synchronizeMailboxExternalAccessByOwnerEmail(rows[0].email);
  } catch (error) {
    // Billing has already committed. The periodic reconciler will retry a
    // failed revocation without risking a duplicate payment attempt.
    console.error("[toss-pay-billing] external access reconciliation failed", {
      ownerUserId,
      message: error instanceof Error ? error.message : "unknown",
    });
  }
}

export type TossPayApiKeySource = "docs-test" | "env";
type TossPayEnvironment = "live" | "test";
export type TossPayBillingProfileStatus = "failed" | "inactive" | "pending" | "active" | "removed";
export type TossPaySubscriptionStatus = "pending" | "active" | "paused" | "cancelled";
export type TossPaySubscriptionChargeStatus = "idle" | "success" | "failed" | "skipped";
export type TossPayChargeKind = "cycle" | "manual";
type TossBillingProvider = "legacy_toss_pay" | "toss_payments";

export type TossPayBillingProfileOverview = {
  provider: TossBillingProvider;
  status: TossPayBillingProfileStatus;
  billingKey: string | null;
  displayId: string | null;
  payMethod: null | "CARD" | "TOSS_MONEY";
  cardCompanyName: string | null;
  cardMethodType: string | null;
  cardNum4Print: string | null;
  cardNumberMasked: string | null;
  accountBankName: string | null;
  accountNumberMasked: string | null;
  activatedAt: string | null;
  removedAt: string | null;
  requestedAt: string | null;
  lastProcessedAt: string | null;
  lastErrorCode: string | null;
  lastErrorMessage: string | null;
};

export type TossPaySubscriptionOverview = {
  status: TossPaySubscriptionStatus;
  initialPaymentComplete: boolean;
  billingCycle: "monthly";
  nextChargeAt: string | null;
  retryAfterAt: string | null;
  lastChargedAt: string | null;
  lastChargeStatus: TossPaySubscriptionChargeStatus;
  consecutiveFailures: number;
  sendFailPush: boolean;
  cashReceipt: boolean;
  spreadOut: number;
  seatChargeAmount: number;
  billingGraceStartedAt: string | null;
  billingGraceEndsAt: string | null;
  billingGraceRetryCount: number;
};

export type TossPayChargeOverview = {
  id: number;
  chargeKind: TossPayChargeKind;
  status: "failed" | "requested" | "skipped" | "success";
  orderNo: string;
  amount: number;
  productDesc: string;
  requestedAt: string;
  approvedAt: string | null;
  payMethod: string | null;
  cardCompanyName: string | null;
  cardNum4Print: string | null;
  errorCode: string | null;
  errorMessage: string | null;
};

export type MailBillingHistoryEntry = {
  accountBankName: string | null;
  accountNumberMasked: string | null;
  amount: number;
  approvedAt: string | null;
  cardCompanyName: string | null;
  cardNum4Print: string | null;
  chargeKind: TossPayChargeKind;
  errorCode: string | null;
  errorMessage: string | null;
  id: number;
  orderNo: string;
  payMethod: string | null;
  pendingRefundCount: number;
  productDesc: string;
  refundedAmount: number;
  refundState: "none" | "partial" | "processing" | "refunded";
  requestedAt: string;
  status: "failed" | "requested" | "skipped" | "success";
  transactionId: string | null;
};

export type MailBillingHistoryPage = {
  entries: MailBillingHistoryEntry[];
  from: string | null;
  page: number;
  pageSize: number;
  rangePreset: MailBillingHistoryRangePreset;
  to: string | null;
  totalCount: number;
  totalPages: number;
};

export type MailBillingOverview = {
  apiKeyMode: "LIVE" | "TEST";
  apiKeySource: TossPayApiKeySource;
  billingSeatCount: number;
  estimatedMonthlyCost: number;
  manualTestAmount: number;
  profile: TossPayBillingProfileOverview | null;
  subscription: TossPaySubscriptionOverview | null;
  recentCharges: TossPayChargeOverview[];
};

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

type BillingProfileRow = RowDataPacket & {
  id: number;
  owner_user_id: number;
  user_token: string;
  display_id: string | null;
  billing_key: string | null;
  pending_billing_key: string | null;
  replacement_billing_key: string | null;
  provider: TossBillingProvider;
  provider_customer_key: string | null;
  provider_mid: string | null;
  status: TossPayBillingProfileStatus;
  requested_at: Date | null;
  activated_at: Date | null;
  removed_at: Date | null;
  last_action: string | null;
  last_processed_at: Date | null;
  pay_method: null | "CARD" | "TOSS_MONEY";
  card_method_type: string | null;
  card_user_type: string | null;
  card_company_no: number | null;
  card_company_name: string | null;
  card_number_masked: string | null;
  card_num4_print: string | null;
  card_bin_number: string | null;
  account_bank_code: string | null;
  account_bank_name: string | null;
  account_number_masked: string | null;
  last_error_code: string | null;
  last_error_message: string | null;
};

type BillingSubscriptionRow = RowDataPacket & {
  id: number;
  owner_user_id: number;
  billing_profile_id: number;
  status: TossPaySubscriptionStatus;
  billing_cycle: "monthly";
  seat_charge_amount: number | null;
  next_charge_at: Date | null;
  retry_after_at: Date | null;
  last_charged_at: Date | null;
  last_charge_status: TossPaySubscriptionChargeStatus;
  consecutive_failures: number;
  send_fail_push: number;
  cash_receipt: number;
  cash_receipt_trade_option: string;
  spread_out: number;
  metadata_text: string | null;
  processing_started_at: Date | null;
  billing_grace_started_at: Date | null;
  billing_grace_ends_at: Date | null;
  billing_grace_retry_count: number;
  billing_failure_notice_sent_on: Date | string | null;
  billing_member_access_suspended_at: Date | null;
};

type BillingChargeRow = RowDataPacket & {
  id: number;
  charge_kind: TossPayChargeKind;
  status: "failed" | "requested" | "skipped" | "success";
  order_no: string;
  amount: number;
  product_desc: string;
  requested_at: Date;
  approved_at: Date | null;
  pay_method: string | null;
  card_company_name: string | null;
  card_num4_print: string | null;
  error_code: string | null;
  error_message: string | null;
};

type BillingChargeDetailRow = RowDataPacket & {
  id: number;
  owner_user_id: number;
  subscription_id: number | null;
  billing_profile_id: number;
  charge_kind: TossPayChargeKind;
  status: "failed" | "requested" | "skipped" | "success";
  order_no: string;
  amount: number;
  amount_tax_free: number;
  product_desc: string;
  requested_at: Date;
  approved_at: Date | null;
  pay_method: string | null;
  card_company_name: string | null;
  card_num4_print: string | null;
  card_method_type: string | null;
  account_bank_name: string | null;
  account_number_masked: string | null;
  provider: TossBillingProvider;
  pay_token: string | null;
  transaction_id: string | null;
  response_code: number | null;
  error_code: string | null;
  error_message: string | null;
};

type BillingChargeHistoryRow = RowDataPacket & {
  id: number;
  charge_kind: TossPayChargeKind;
  status: "failed" | "requested" | "skipped" | "success";
  order_no: string;
  amount: number;
  product_desc: string;
  requested_at: Date;
  approved_at: Date | null;
  pay_method: string | null;
  card_company_name: string | null;
  card_num4_print: string | null;
  account_bank_name: string | null;
  account_number_masked: string | null;
  transaction_id: string | null;
  error_code: string | null;
  error_message: string | null;
  refunded_amount: number | null;
  pending_refund_count: number | null;
};

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

type RefundStatsRow = RowDataPacket & {
  refunded_amount: number | null;
  pending_count: number | null;
};

type DueSubscriptionRow = RowDataPacket & {
  subscription_id: number;
  owner_user_id: number;
  owner_email: string;
  billing_profile_id: number;
  billing_key: string;
  provider: TossBillingProvider;
  provider_customer_key: string | null;
  next_charge_at: Date | null;
  send_fail_push: number;
  cash_receipt: number;
  cash_receipt_trade_option: string;
  spread_out: number;
  metadata_text: string | null;
  consecutive_failures: number;
  seat_charge_amount: number | null;
  retry_after_at: Date | null;
  billing_grace_started_at: Date | null;
  billing_grace_ends_at: Date | null;
  billing_grace_retry_count: number;
};

type BillingFailureRecipientRow = RowDataPacket & {
  company_name: string;
  recovery_email: string | null;
  recovery_email_verified_at: Date | null;
  notice_sent_on: string | null;
};

type LegacyFailedSubscriptionRow = RowDataPacket & {
  failed_at: Date;
  subscription_id: number;
};

type BillingRegistrationRow = RowDataPacket & {
  id: number;
  owner_user_id: number;
  token_hash: string;
  provider_customer_key: string;
  intent: "billing" | "domain-assistance";
  return_app_scheme: string | null;
  status: "pending" | "processing" | "completed" | "failed";
  expires_at: Date;
};

type TossPayBillingCreateResponse = {
  code: number;
  billingKey?: string;
  checkoutAndroidUri?: string;
  checkoutIosUri?: string;
  checkoutUri?: string;
  errorCode?: string;
  msg?: string;
};

type TossPayBillingStatusResponse = {
  code: number;
  billingKey?: string;
  status?: string;
  displayId?: string;
  payMethod?: "CARD" | "TOSS_MONEY";
  cardMethodType?: string;
  cardUserType?: string;
  cardCompanyNo?: number;
  cardCompanyName?: string;
  cardNumber?: string;
  cardNum4Print?: string;
  cardBinNumber?: string;
  accountBankCode?: string;
  accountBankName?: string;
  accountNumber?: string;
  errorCode?: string;
  msg?: string;
};

type TossPayBillingCallbackPayload = {
  action?: string;
  processedTs?: string;
  userId?: string;
  displayId?: string;
  billingKey?: string;
  payMethod?: "CARD" | "TOSS_MONEY";
  cardMethodType?: string;
  cardUserType?: string;
  cardCompanyNo?: number;
  cardCompanyName?: string;
  cardNumber?: string;
  cardNum4Print?: string;
  cardBinNumber?: string;
  accountBankCode?: string;
  accountBankName?: string;
  accountNumber?: string;
};

type TossPayBillResponse = {
  code: number;
  approvalTime?: string;
  amount?: number;
  cardCompanyName?: string;
  cardMethodType?: string;
  cardNum4Print?: string;
  errorCode?: string;
  msg?: string;
  orderNo?: string;
  payMethod?: string;
  payToken?: string;
  transactionId?: string;
};

type TossPayRefundResponse = {
  code: number;
  approvalTime?: string;
  errorCode?: string;
  msg?: string;
  payMethod?: string;
  payStatus?: string;
  payToken?: string;
  refundableAmount?: number;
  refundedAmount?: number;
  refundNo?: string;
  transactionId?: string;
};

type TossPayChargeExecutionResult = {
  amount: number;
  approvedAt: string | null;
  chargeId: number;
  errorCode: string | null;
  errorMessage: string | null;
  orderNo: string;
  payMethod: string | null;
  productDesc: string;
  status: "failed" | "skipped" | "success";
  cycleFailure: null | {
    downgraded: boolean;
    graceEndsAt: string;
    nextRetryAt: string | null;
    noticeDue: boolean;
    retryCount: number;
    scheduledRetryAt: string | null;
  };
};

export type TossPayChargeRefundResult = {
  amount: number;
  chargeId: number;
  refundedAt: string | null;
  refundId: number;
  refundNo: string;
  remainingRefundableAmount: number;
  status: "success";
};

export type TossPaymentsCheckoutChargeRecordResult = {
  alreadyRecorded: boolean;
  amount: number;
  approvedAt: string;
  chargeId: number;
  nextChargeAt: string;
  orderNo: string;
  status: "success";
  subscriptionStatus: "active";
};

export type TossPayBillingRegistrationResult = {
  billingKey: string | null;
  checkoutAndroidUri: string | null;
  checkoutIosUri: string | null;
  checkoutUri: string;
  publicBaseUrl: string;
  usesCanonicalPublicBaseUrl: boolean;
};

export type TossPaymentsBillingAuthSession = {
  clientKey: string;
  clientKeySource: "docs-test" | "env";
  customerEmail: string;
  customerKey: string;
  customerName: string;
  failUrl: string;
  successUrl: string;
};

export type TossPayDueChargeRunSummary = {
  attempted: number;
  failed: number;
  skipped: number;
  success: number;
};

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

function toBoolean(value: number) {
  return Boolean(value);
}

function isPrivateIpv4(hostname: string) {
  return (
    /^10\./.test(hostname) ||
    /^127\./.test(hostname) ||
    /^192\.168\./.test(hostname) ||
    /^172\.(1[6-9]|2\d|3[0-1])\./.test(hostname)
  );
}

function isExternallyReachableOrigin(origin: string) {
  try {
    const url = new URL(origin);
    return url.protocol === "https:" && !isLocalOrigin(origin) && !isPrivateIpv4(url.hostname);
  } catch {
    return false;
  }
}

function resolveTossPayPublicBaseUrl(origin: string) {
  const fallback = process.env.TOSS_PAY_PUBLIC_BASE_URL?.trim() || getMailAppUrl();
  return isExternallyReachableOrigin(origin) ? origin : fallback;
}

function getTossPayEnvironment(): TossPayEnvironment {
  return process.env.TOSS_PAY_MODE?.trim().toLowerCase() === "live" ? "live" : "test";
}

function getConfiguredTossPayApiKey(environment: TossPayEnvironment) {
  return (environment === "live"
    ? process.env.TOSS_PAY_API_KEY_LIVE
    : process.env.TOSS_PAY_API_KEY_TEST
  )?.trim();
}

function getLegacyTossPayApiKey(environment: TossPayEnvironment) {
  const value = process.env.TOSS_PAY_API_KEY?.trim();
  const expectedPrefix = environment === "live" ? "sk_live_" : "sk_test_";
  return value?.startsWith(expectedPrefix) ? value : undefined;
}

function getTossPayApiKey() {
  const environment = getTossPayEnvironment();
  const configured =
    getConfiguredTossPayApiKey(environment) || getLegacyTossPayApiKey(environment);

  if (configured) {
    const expectedPrefix = environment === "live" ? "sk_live_" : "sk_test_";

    if (!configured.startsWith(expectedPrefix)) {
      throw new Error(
        `TOSS_PAY_MODE=${environment} requires an API key starting with ${expectedPrefix}.`,
      );
    }

    return configured;
  }

  if (environment === "test") {
    return TOSS_PAY_DOCS_TEST_API_KEY;
  }

  throw new Error(
    "TOSS_PAY_MODE=live requires a configured TOSS_PAY_API_KEY_LIVE value.",
  );
}

export function getTossPayApiKeySource(): TossPayApiKeySource {
  const environment = getTossPayEnvironment();
  return getConfiguredTossPayApiKey(environment) || getLegacyTossPayApiKey(environment)
    ? "env"
    : "docs-test";
}

function createTossPayUserToken(ownerUserId: number) {
  return `om-user-${ownerUserId}`;
}

function normalizeBillingKey(value: string | null | undefined) {
  const normalized = value?.trim();
  return normalized ? normalized : null;
}

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

  const match = /^om-user-(\d+)$/.exec(value.trim());
  return match ? Number(match[1]) : null;
}

function createTossPayOrderNo(kind: TossPayChargeKind) {
  return `om-${kind}-${Date.now()}-${randomBytes(4).toString("hex")}`.slice(0, 50);
}

function createTossPayRefundNo() {
  return `om-refund-${Date.now()}-${randomBytes(4).toString("hex")}`.slice(0, 50);
}

function needsBillingProfileStatusRecovery(profile: BillingProfileRow | null) {
  if (!profile || profile.provider !== "legacy_toss_pay") {
    return false;
  }

  if (profile.pending_billing_key || profile.replacement_billing_key || profile.billing_key) {
    return false;
  }

  return (
    profile.status === "active" ||
    profile.last_action === "ACTIVATED" ||
    Boolean(profile.activated_at) ||
    profile.last_error_code === "BILLING_KEY_ALREADY_ACTIVATED"
  );
}

function hasUsableBillingProfile(
  profile: BillingProfileRow | null,
): profile is BillingProfileRow & { billing_key: string } {
  if (!profile || profile.status !== "active" || !profile.billing_key) {
    return false;
  }

  return (
    profile.provider === "legacy_toss_pay" ||
    (profile.provider === "toss_payments" && Boolean(profile.provider_customer_key))
  );
}

function mapTossPayStatusToProfileStatus(
  status: string | undefined,
  billingKey: string | null,
): TossPayBillingProfileStatus {
  switch (status?.trim().toUpperCase()) {
    case "ACTIVE":
      return billingKey ? "active" : "inactive";
    case "REMOVED":
      return "removed";
    case "FAILED":
      return "failed";
    case "PENDING":
      return "pending";
    default:
      return billingKey ? "active" : "inactive";
  }
}

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

  const normalized = value.trim().replace(" ", "T");
  const date = new Date(`${normalized}+09:00`);
  return Number.isNaN(date.getTime()) ? null : date;
}

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 createNextMonthlyChargeAt(baseDate = new Date()) {
  return addMonth(baseDate);
}

function buildManualChargeProductDescription(billableSeats: number, usesFallbackAmount: boolean) {
  if (usesFallbackAmount) {
    return "성장플랜 즉시 청구";
  }

  return `성장플랜 ${billableSeats}명`;
}

function buildCycleChargeProductDescription(billableSeats: number) {
  return `성장플랜 ${billableSeats}명`;
}

function buildManagedMemberChargeProductDescription() {
  return "성장플랜 멤버 추가 1명";
}

function serializeMetadata(value: Record<string, unknown>) {
  return JSON.stringify(value);
}

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, company_name, display_name
      FROM users
      WHERE email = ?
      LIMIT 1
    `,
    [email],
  );

  return rows[0] ?? null;
}

export async function canManageMailBillingByEmail(email: string) {
  const ownership = await getMailBillingOwnershipByEmail(email);

  return ownership.canManageBilling;
}

export async function getMailBillingOwnershipByEmail(email: string) {
  await ensureOfficialMailSchema();
  const normalizedEmail = email.trim().toLowerCase();
  const [rows] = await getDbPool().query<
    (RowDataPacket & {
      member_owner_email: string | null;
    })[]
  >(
    `
      SELECT owner.email AS member_owner_email
      FROM managed_team_mailboxes mtm
      INNER JOIN users owner
        ON owner.id = mtm.owner_user_id
      WHERE mtm.email = ?
        AND mtm.status = 'active'
      LIMIT 1
    `,
    [normalizedEmail],
  );
  const ownerEmail = rows[0]?.member_owner_email?.trim().toLowerCase() || normalizedEmail;

  return {
    canManageBilling: ownerEmail === normalizedEmail,
    ownerEmail,
  };
}

export async function assertMailBillingManagementAccessByEmail(email: string) {
  const allowed = await canManageMailBillingByEmail(email);

  if (!allowed) {
    throw new Error("billing-management-forbidden");
  }
}

async function getManagedActiveMemberCountByOwnerUserId(ownerUserId: number, connection?: PoolConnection) {
  const executor = connection ?? getDbPool();
  const [rows] = await executor.query<CountRow[]>(
    `
      SELECT COUNT(*) AS total
      FROM managed_team_mailboxes
      WHERE owner_user_id = ?
        AND (
          status = 'active'
          OR billing_suspended_at IS NOT NULL
        )
    `,
    [ownerUserId],
  );

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

async function getGrowthPlanSeatCountByOwnerUserId(
  ownerUserId: number,
  connection?: PoolConnection,
) {
  const managedMemberCount = await getManagedActiveMemberCountByOwnerUserId(
    ownerUserId,
    connection,
  );

  return countMailBillingSeatsFromManagedMembers(managedMemberCount);
}

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

  return rows[0] ?? null;
}

async function getBillingProfileByBillingKey(billingKey: string, connection?: PoolConnection) {
  const executor = connection ?? getDbPool();
  const [rows] = await executor.query<BillingProfileRow[]>(
    `
      SELECT *
      FROM mailbox_toss_pay_billing_profiles
      WHERE billing_key = ?
         OR pending_billing_key = ?
         OR replacement_billing_key = ?
      LIMIT 1
    `,
    [billingKey, billingKey, billingKey],
  );

  return rows[0] ?? null;
}

async function getBillingSubscriptionByOwnerUserId(ownerUserId: number, connection?: PoolConnection) {
  const executor = connection ?? getDbPool();
  const [rows] = await executor.query<BillingSubscriptionRow[]>(
    `
      SELECT *
      FROM mailbox_toss_pay_subscriptions
      WHERE owner_user_id = ?
      LIMIT 1
    `,
    [ownerUserId],
  );

  return rows[0] ?? null;
}

async function getBillingChargeById(chargeId: number, connection?: PoolConnection) {
  const executor = connection ?? getDbPool();
  const [rows] = await executor.query<BillingChargeDetailRow[]>(
    `
      SELECT *
      FROM mailbox_toss_pay_billing_charges
      WHERE id = ?
      LIMIT 1
      FOR UPDATE
    `,
    [chargeId],
  );

  return rows[0] ?? null;
}

async function getBillingChargeByOrderNo(orderNo: string, connection?: PoolConnection) {
  const executor = connection ?? getDbPool();
  const [rows] = await executor.query<BillingChargeDetailRow[]>(
    `
      SELECT *
      FROM mailbox_toss_pay_billing_charges
      WHERE order_no = ?
      LIMIT 1
    `,
    [orderNo],
  );

  return rows[0] ?? null;
}

async function getRefundStatsByChargeId(chargeId: number, connection?: PoolConnection) {
  const executor = connection ?? getDbPool();
  const [rows] = await executor.query<RefundStatsRow[]>(
    `
      SELECT
        SUM(CASE WHEN status = 'success' THEN amount ELSE 0 END) AS refunded_amount,
        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count
      FROM mailbox_toss_pay_billing_refunds
      WHERE charge_id = ?
    `,
    [chargeId],
  );

  return {
    pendingCount: Number(rows[0]?.pending_count ?? 0),
    refundedAmount: Number(rows[0]?.refunded_amount ?? 0),
  };
}

async function insertPendingRefundLog(
  connection: PoolConnection,
  input: {
    amount: number;
    amountTaxFree: number;
    chargeId: number;
    ownerUserId: number;
    payToken: string;
    reason: string | null;
    refundNo: string;
    requestedAt: Date;
    requestedByEmail: string;
    transactionId: string | null;
  },
) {
  const [result] = await connection.query<ResultSetHeader>(
    `
      INSERT INTO mailbox_toss_pay_billing_refunds (
        charge_id,
        owner_user_id,
        refund_no,
        status,
        amount,
        amount_tax_free,
        reason,
        pay_token,
        transaction_id,
        requested_by_email,
        requested_at
      )
      VALUES (?, ?, ?, 'pending', ?, ?, ?, ?, ?, ?, ?)
    `,
    [
      input.chargeId,
      input.ownerUserId,
      input.refundNo,
      input.amount,
      input.amountTaxFree,
      input.reason,
      input.payToken,
      input.transactionId,
      input.requestedByEmail,
      input.requestedAt,
    ],
  );

  return result.insertId;
}

async function updateRefundLogResult(
  connection: PoolConnection,
  input: {
    errorCode: string | null;
    errorMessage: string | null;
    rawResponse: string;
    refundedAt: Date | null;
    refundId: number;
    responseCode: number | null;
    status: "failed" | "success";
  },
) {
  await connection.query<ResultSetHeader>(
    `
      UPDATE mailbox_toss_pay_billing_refunds
      SET
        status = ?,
        refunded_at = ?,
        response_code = ?,
        error_code = ?,
        error_message = ?,
        raw_response = ?,
        updated_at = NOW()
      WHERE id = ?
    `,
    [
      input.status,
      input.refundedAt,
      input.responseCode,
      input.errorCode,
      input.errorMessage,
      input.rawResponse,
      input.refundId,
    ],
  );
}

async function ensureBillingSubscriptionRow(
  connection: PoolConnection,
  input: {
    billingProfileId: number;
    ownerUserId: number;
  },
) {
  await connection.query<ResultSetHeader>(
    `
      INSERT INTO mailbox_toss_pay_subscriptions (
        owner_user_id,
        billing_profile_id,
        status,
        billing_cycle,
        seat_charge_amount,
        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 (?, ?, 'pending', 'monthly', ?, NULL, NULL, NULL, 'idle', 0, 1, 0, 'GENERAL', 0, NULL)
      ON DUPLICATE KEY UPDATE
        billing_profile_id = VALUES(billing_profile_id),
        updated_at = NOW()
    `,
    [input.ownerUserId, input.billingProfileId, MAIL_GROWTH_PLAN_CHARGE_PER_MEMBER],
  );

  const subscription = await getBillingSubscriptionByOwnerUserId(input.ownerUserId, connection);

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

  return subscription;
}

async function upsertBillingProfileForRegistration(
  connection: PoolConnection,
  input: {
    ownerUserId: number;
    userToken: string;
    displayId: string;
    billingKey: string | null;
    pendingBillingKey: string | null;
    replacementBillingKey: string | null;
    provider?: TossBillingProvider;
    providerCustomerKey?: string | null;
    providerMid?: string | null;
    status: TossPayBillingProfileStatus;
    requestedAt: Date;
    lastErrorCode: string | null;
    lastErrorMessage: string | null;
  },
) {
  await connection.query<ResultSetHeader>(
    `
      INSERT INTO mailbox_toss_pay_billing_profiles (
        owner_user_id,
        user_token,
        display_id,
        billing_key,
        pending_billing_key,
        replacement_billing_key,
        provider,
        provider_customer_key,
        provider_mid,
        status,
        requested_at,
        last_error_code,
        last_error_message
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ON DUPLICATE KEY UPDATE
        user_token = VALUES(user_token),
        display_id = VALUES(display_id),
        billing_key = VALUES(billing_key),
        pending_billing_key = VALUES(pending_billing_key),
        replacement_billing_key = VALUES(replacement_billing_key),
        provider = VALUES(provider),
        provider_customer_key = VALUES(provider_customer_key),
        provider_mid = VALUES(provider_mid),
        status = VALUES(status),
        requested_at = VALUES(requested_at),
        last_error_code = VALUES(last_error_code),
        last_error_message = VALUES(last_error_message),
        updated_at = NOW()
    `,
    [
      input.ownerUserId,
      input.userToken,
      input.displayId,
      input.billingKey,
      input.pendingBillingKey,
      input.replacementBillingKey,
      input.provider ?? "legacy_toss_pay",
      input.providerCustomerKey ?? null,
      input.providerMid ?? null,
      input.status,
      input.requestedAt,
      input.lastErrorCode,
      input.lastErrorMessage,
    ],
  );

  const profile = await getBillingProfileByOwnerUserId(input.ownerUserId, connection);

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

  return profile;
}

async function ensureBillingSchemaReady() {
  await ensureOfficialMailSchema();
}

function isBillingFailureEmailEnabled() {
  return process.env.TOSS_PAY_BILLING_FAILURE_EMAIL_ENABLED?.trim().toLowerCase() === "true";
}

async function initializeLegacyFailedSubscriptionGracePeriods() {
  const [rows] = await getDbPool().query<LegacyFailedSubscriptionRow[]>(
    `
      SELECT
        s.id AS subscription_id,
        COALESCE(MAX(c.requested_at), s.updated_at) AS failed_at
      FROM mailbox_toss_pay_subscriptions s
      LEFT JOIN mailbox_toss_pay_billing_charges c
        ON c.subscription_id = s.id
       AND c.charge_kind = 'cycle'
       AND c.status = 'failed'
      WHERE s.status = 'active'
        AND s.last_charge_status = 'failed'
        AND s.billing_grace_started_at IS NULL
      GROUP BY s.id, s.updated_at
    `,
  );

  for (const row of rows) {
    const retrySlots = createBillingGraceRetrySlots(row.failed_at);

    await getDbPool().query<ResultSetHeader>(
      `
        UPDATE mailbox_toss_pay_subscriptions
        SET
          billing_grace_started_at = ?,
          billing_grace_ends_at = ?,
          billing_grace_retry_count = 0,
          billing_failure_notice_sent_on = NULL,
          retry_after_at = ?,
          updated_at = NOW()
        WHERE id = ?
          AND status = 'active'
          AND billing_grace_started_at IS NULL
      `,
      [row.failed_at, retrySlots.at(-1) ?? row.failed_at, retrySlots[0], row.subscription_id],
    );
  }
}

async function sendBillingFailureNoticeIfEnabled(input: {
  amount: number;
  billableSeats: number;
  errorMessage: string | null;
  graceEndsAt: Date;
  nextRetryAt: Date | null;
  ownerUserId: number;
  subscriptionId: number;
  noticeDate: Date;
}) {
  const [rows] = await getDbPool().query<BillingFailureRecipientRow[]>(
    `
      SELECT
        u.company_name,
        u.recovery_email,
        u.recovery_email_verified_at,
        DATE_FORMAT(s.billing_failure_notice_sent_on, '%Y-%m-%d') AS notice_sent_on
      FROM users u
      INNER JOIN mailbox_toss_pay_subscriptions s
        ON s.owner_user_id = u.id
      WHERE u.id = ?
        AND s.id = ?
      LIMIT 1
    `,
    [input.ownerUserId, input.subscriptionId],
  );
  const recipient = rows[0];
  const noticeDate = formatKstDate(input.noticeDate);

  if (
    !recipient?.recovery_email ||
    !recipient.recovery_email_verified_at ||
    recipient.notice_sent_on === noticeDate
  ) {
    return false;
  }

  if (!isBillingFailureEmailEnabled()) {
    console.info(
      `[toss-pay-billing] failure email held for review owner=${input.ownerUserId} date=${noticeDate}`,
    );
    return false;
  }

  const email = renderBillingFailureEmail({
    amount: input.amount,
    billableSeats: input.billableSeats,
    companyName: recipient.company_name,
    errorMessage: input.errorMessage || "등록된 결제수단으로 승인되지 않았습니다.",
    graceEndsAt: input.graceEndsAt,
    netPricePerMember: MAIL_GROWTH_PLAN_NET_PRICE_PER_MEMBER,
    nextRetryAt: input.nextRetryAt,
  });

  await sendOfficialMailNotificationEmail({
    bodyHtml: email.bodyHtml,
    bodyText: email.bodyText,
    recipientEmail: recipient.recovery_email,
    subject: email.subject,
  });
  await getDbPool().query<ResultSetHeader>(
    `
      UPDATE mailbox_toss_pay_subscriptions
      SET billing_failure_notice_sent_on = ?, updated_at = NOW()
      WHERE id = ?
        AND owner_user_id = ?
    `,
    [noticeDate, input.subscriptionId, input.ownerUserId],
  );

  return true;
}

async function requestJson<TResponse>(url: string, payload: Record<string, unknown>) {
  const response = await fetch(url, {
    method: "POST",
    headers: {
      Accept: "application/json",
      "Content-Type": "application/json; charset=utf-8",
    },
    body: JSON.stringify(payload),
    cache: "no-store",
  });

  const rawText = await response.text();
  let json: TResponse | null = null;

  try {
    json = rawText ? (JSON.parse(rawText) as TResponse) : null;
  } catch {
    json = null;
  }

  return {
    json,
    ok: response.ok,
    rawText,
    status: response.status,
  };
}

async function requestTossPayBillingStatus(input: { userToken: string; displayId: string }) {
  return await requestJson<TossPayBillingStatusResponse>(TOSS_PAY_BILLING_STATUS_URL, {
    apiKey: getTossPayApiKey(),
    userId: input.userToken,
    displayId: input.displayId,
  });
}

async function recoverBillingProfileFromTossStatus(profile: BillingProfileRow | null) {
  if (!needsBillingProfileStatusRecovery(profile) || !profile) {
    return profile;
  }

  const response = await requestTossPayBillingStatus({
    userToken: profile.user_token || createTossPayUserToken(profile.owner_user_id),
    displayId: profile.display_id?.trim() || BILLING_PROFILE_DISPLAY_ID,
  });
  const data = response.json;
  const remoteBillingKey = normalizeBillingKey(data?.billingKey);

  if (!response.ok || data?.code !== 0 || !remoteBillingKey) {
    return profile;
  }

  const nextStatus = mapTossPayStatusToProfileStatus(data?.status, remoteBillingKey);
  const nextPayMethod = nextStatus === "active" ? data?.payMethod ?? profile.pay_method : null;
  const nextCardMethodType =
    nextStatus === "active" ? data?.cardMethodType ?? profile.card_method_type : null;
  const nextCardUserType =
    nextStatus === "active" ? data?.cardUserType ?? profile.card_user_type : null;
  const nextCardCompanyNo =
    nextStatus === "active" ? data?.cardCompanyNo ?? profile.card_company_no : null;
  const nextCardCompanyName =
    nextStatus === "active" ? data?.cardCompanyName ?? profile.card_company_name : null;
  const nextCardNumberMasked =
    nextStatus === "active" ? data?.cardNumber ?? profile.card_number_masked : null;
  const nextCardNum4Print =
    nextStatus === "active" ? data?.cardNum4Print ?? profile.card_num4_print : null;
  const nextCardBinNumber =
    nextStatus === "active" ? data?.cardBinNumber ?? profile.card_bin_number : null;
  const nextAccountBankCode =
    nextStatus === "active" ? data?.accountBankCode ?? profile.account_bank_code : null;
  const nextAccountBankName =
    nextStatus === "active" ? data?.accountBankName ?? profile.account_bank_name : null;
  const nextAccountNumberMasked =
    nextStatus === "active" ? data?.accountNumber ?? profile.account_number_masked : null;

  await withTransaction(async (connection) => {
    await connection.query<ResultSetHeader>(
      `
        UPDATE mailbox_toss_pay_billing_profiles
        SET
          billing_key = ?,
          pending_billing_key = NULL,
          replacement_billing_key = NULL,
          display_id = ?,
          status = ?,
          activated_at = CASE
            WHEN ? = 'active' THEN COALESCE(activated_at, NOW())
            ELSE activated_at
          END,
          removed_at = CASE
            WHEN ? = 'removed' THEN COALESCE(removed_at, NOW())
            ELSE NULL
          END,
          last_action = CASE
            WHEN ? = 'active' THEN 'ACTIVATED'
            WHEN ? = 'removed' THEN 'REMOVED'
            ELSE last_action
          END,
          last_processed_at = NOW(),
          pay_method = ?,
          card_method_type = ?,
          card_user_type = ?,
          card_company_no = ?,
          card_company_name = ?,
          card_number_masked = ?,
          card_num4_print = ?,
          card_bin_number = ?,
          account_bank_code = ?,
          account_bank_name = ?,
          account_number_masked = ?,
          last_error_code = NULL,
          last_error_message = NULL,
          updated_at = NOW()
        WHERE id = ?
      `,
      [
        remoteBillingKey,
        data?.displayId?.trim() || profile.display_id || BILLING_PROFILE_DISPLAY_ID,
        nextStatus,
        nextStatus,
        nextStatus,
        nextStatus,
        nextStatus,
        nextPayMethod,
        nextCardMethodType,
        nextCardUserType,
        nextCardCompanyNo,
        nextCardCompanyName,
        nextCardNumberMasked,
        nextCardNum4Print,
        nextCardBinNumber,
        nextAccountBankCode,
        nextAccountBankName,
        nextAccountNumberMasked,
        profile.id,
      ],
    );
  });

  return await getBillingProfileByOwnerUserId(profile.owner_user_id);
}

function mapBillingProfileOverview(
  row: BillingProfileRow | null,
  usesTossPayments: boolean,
): TossPayBillingProfileOverview | null {
  if (!row) {
    return null;
  }

  const expectedProvider: TossBillingProvider = usesTossPayments
    ? "toss_payments"
    : "legacy_toss_pay";
  const requiresReregistration = row.provider !== expectedProvider;

  return {
    provider: row.provider,
    status: requiresReregistration ? "inactive" : row.status,
    billingKey: requiresReregistration ? null : row.billing_key ?? row.pending_billing_key,
    displayId: row.display_id,
    payMethod: row.pay_method,
    cardCompanyName: row.card_company_name,
    cardMethodType: row.card_method_type,
    cardNum4Print: row.card_num4_print,
    cardNumberMasked: row.card_number_masked,
    accountBankName: row.account_bank_name,
    accountNumberMasked: row.account_number_masked,
    activatedAt: normalizeDate(row.activated_at),
    removedAt: normalizeDate(row.removed_at),
    requestedAt: normalizeDate(row.requested_at),
    lastProcessedAt: normalizeDate(row.last_processed_at),
    lastErrorCode: requiresReregistration
      ? "billing-profile-reregistration-required"
      : row.last_error_code,
    lastErrorMessage: requiresReregistration
      ? "현재 결제 경로에 맞게 결제수단을 다시 등록해야 합니다."
      : row.last_error_message,
  };
}

function mapBillingSubscriptionOverview(
  row: BillingSubscriptionRow | null,
): TossPaySubscriptionOverview | null {
  if (!row) {
    return null;
  }

  return {
    status: row.status,
    initialPaymentComplete: row.status === "active" && row.last_charged_at !== null,
    billingCycle: row.billing_cycle,
    nextChargeAt: normalizeDate(row.next_charge_at),
    retryAfterAt: normalizeDate(row.retry_after_at),
    lastChargedAt: normalizeDate(row.last_charged_at),
    lastChargeStatus: row.last_charge_status,
    consecutiveFailures: row.consecutive_failures,
    sendFailPush: toBoolean(row.send_fail_push),
    cashReceipt: toBoolean(row.cash_receipt),
    spreadOut: row.spread_out,
    seatChargeAmount: resolveMailBillingSeatChargeAmount(row.seat_charge_amount),
    billingGraceStartedAt: normalizeDate(row.billing_grace_started_at),
    billingGraceEndsAt: normalizeDate(row.billing_grace_ends_at),
    billingGraceRetryCount: row.billing_grace_retry_count,
  };
}

function mapChargeOverview(row: BillingChargeRow): TossPayChargeOverview {
  return {
    id: row.id,
    chargeKind: row.charge_kind,
    status: row.status,
    orderNo: row.order_no,
    amount: row.amount,
    productDesc: row.product_desc,
    requestedAt: row.requested_at.toISOString(),
    approvedAt: normalizeDate(row.approved_at),
    payMethod: row.pay_method,
    cardCompanyName: row.card_company_name,
    cardNum4Print: row.card_num4_print,
    errorCode: row.error_code,
    errorMessage: row.error_message,
  };
}

function mapBillingHistoryEntry(row: BillingChargeHistoryRow): MailBillingHistoryEntry {
  const refundedAmount = Number(row.refunded_amount ?? 0);
  const pendingRefundCount = Number(row.pending_refund_count ?? 0);
  const refundState: MailBillingHistoryEntry["refundState"] =
    pendingRefundCount > 0
      ? "processing"
      : refundedAmount <= 0
        ? "none"
        : refundedAmount >= row.amount
          ? "refunded"
          : "partial";

  return {
    accountBankName: row.account_bank_name,
    accountNumberMasked: row.account_number_masked,
    amount: row.amount,
    approvedAt: normalizeDate(row.approved_at),
    cardCompanyName: row.card_company_name,
    cardNum4Print: row.card_num4_print,
    chargeKind: row.charge_kind,
    errorCode: row.error_code,
    errorMessage: row.error_message,
    id: row.id,
    orderNo: row.order_no,
    payMethod: row.pay_method,
    pendingRefundCount,
    productDesc: row.product_desc,
    refundedAmount,
    refundState,
    requestedAt: row.requested_at.toISOString(),
    status: row.status,
    transactionId: row.transaction_id,
  };
}

export async function getMailBillingOverviewByOwnerEmail(
  email: string,
  options?: {
    recoverRemoteStatus?: boolean;
  },
): Promise<MailBillingOverview | null> {
  await ensureBillingSchemaReady();

  const user = await getOwnerUserByEmail(email);

  if (!user) {
    return null;
  }

  const usesTossPayments = isTossPaymentsBillingEnabledForEmail(user.email);
  const billingProfile = await getBillingProfileByOwnerUserId(user.id);
  const recoveredProfile =
    options?.recoverRemoteStatus === false
      ? billingProfile
      : await recoverBillingProfileFromTossStatus(billingProfile);

  const [subscription, billingSeatCount, chargeRows] = await Promise.all([
    getBillingSubscriptionByOwnerUserId(user.id),
    getGrowthPlanSeatCountByOwnerUserId(user.id),
    getDbPool().query<BillingChargeRow[]>(
      `
        SELECT
          id,
          charge_kind,
          status,
          order_no,
          amount,
          product_desc,
          requested_at,
          approved_at,
          pay_method,
          card_company_name,
          card_num4_print,
          error_code,
          error_message
        FROM mailbox_toss_pay_billing_charges
        WHERE owner_user_id = ?
        ORDER BY requested_at DESC, id DESC
        LIMIT 8
      `,
      [user.id],
    ),
  ]);

  const seatChargeAmount = resolveMailBillingSeatChargeAmount(
    subscription?.seat_charge_amount,
  );
  const chargeSummary = createMailBillingChargeSummary(
    billingSeatCount,
    seatChargeAmount,
  );
  const manualPreview = createMailBillingPreview(billingSeatCount, seatChargeAmount);

  return {
    apiKeyMode: usesTossPayments
      ? getTossPaymentsEnvironment() === "live"
        ? "LIVE"
        : "TEST"
      : getTossPayEnvironment() === "live"
        ? "LIVE"
        : "TEST",
    apiKeySource: usesTossPayments
      ? getTossPaymentsClientKeySource()
      : getTossPayApiKeySource(),
    billingSeatCount: chargeSummary.billableSeats,
    estimatedMonthlyCost: chargeSummary.estimatedMonthlyCost,
    manualTestAmount: manualPreview.chargeAmount || DEFAULT_MANUAL_TEST_AMOUNT,
    profile: mapBillingProfileOverview(recoveredProfile, usesTossPayments),
    subscription: mapBillingSubscriptionOverview(subscription),
    recentCharges: chargeRows[0].map(mapChargeOverview),
  };
}

export async function getMailBillingHistoryByOwnerEmail(
  email: string,
  options?: {
    from?: string | null;
    page?: number;
    pageSize?: number;
    rangePreset?: MailBillingHistoryRangePreset;
    to?: string | null;
  },
): Promise<MailBillingHistoryPage | null> {
  await ensureBillingSchemaReady();

  const user = await getOwnerUserByEmail(email);

  if (!user) {
    return null;
  }

  const resolvedState = resolveMailBillingHistoryState({
    from: options?.from,
    page: options?.page,
    rangePreset: options?.rangePreset,
    to: options?.to,
  });
  const pageSize =
    typeof options?.pageSize === "number" &&
    Number.isFinite(options.pageSize) &&
    options.pageSize > 0
      ? Math.min(50, Math.max(1, Math.floor(options.pageSize)))
      : MAIL_BILLING_HISTORY_PAGE_SIZE;
  const whereConditions = ["c.owner_user_id = ?"];
  const params: Array<number | string> = [user.id];

  if (resolvedState.from) {
    whereConditions.push("c.requested_at >= ?");
    params.push(`${resolvedState.from} 00:00:00`);
  }

  if (resolvedState.to) {
    const exclusiveToDate = new Date(`${resolvedState.to}T00:00:00`);
    exclusiveToDate.setDate(exclusiveToDate.getDate() + 1);
    const year = exclusiveToDate.getFullYear();
    const month = String(exclusiveToDate.getMonth() + 1).padStart(2, "0");
    const date = String(exclusiveToDate.getDate()).padStart(2, "0");
    whereConditions.push("c.requested_at < ?");
    params.push(`${year}-${month}-${date} 00:00:00`);
  }

  const whereSql = `WHERE ${whereConditions.join(" AND ")}`;
  const [countRows] = await getDbPool().query<CountRow[]>(
    `
      SELECT COUNT(*) AS total
      FROM mailbox_toss_pay_billing_charges c
      ${whereSql}
    `,
    params,
  );
  const totalCount = Number(countRows[0]?.total ?? 0);
  const totalPages = Math.max(1, Math.ceil(totalCount / pageSize));
  const page = Math.min(resolvedState.page, totalPages);
  const offset = (page - 1) * pageSize;
  const [rows] = await getDbPool().query<BillingChargeHistoryRow[]>(
    `
      SELECT
        c.id,
        c.charge_kind,
        c.status,
        c.order_no,
        c.amount,
        c.product_desc,
        c.requested_at,
        c.approved_at,
        c.pay_method,
        c.card_company_name,
        c.card_num4_print,
        c.account_bank_name,
        c.account_number_masked,
        c.transaction_id,
        c.error_code,
        c.error_message,
        COALESCE(refund_totals.refunded_amount, 0) AS refunded_amount,
        COALESCE(refund_totals.pending_refund_count, 0) AS pending_refund_count
      FROM mailbox_toss_pay_billing_charges c
      LEFT JOIN (
        SELECT
          charge_id,
          SUM(CASE WHEN status = 'success' THEN amount ELSE 0 END) AS refunded_amount,
          SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_refund_count
        FROM mailbox_toss_pay_billing_refunds
        GROUP BY charge_id
      ) refund_totals
        ON refund_totals.charge_id = c.id
      ${whereSql}
      ORDER BY c.requested_at DESC, c.id DESC
      LIMIT ?
      OFFSET ?
    `,
    [...params, pageSize, offset],
  );

  return {
    entries: rows.map(mapBillingHistoryEntry),
    from: resolvedState.from,
    page,
    pageSize,
    rangePreset: resolvedState.rangePreset,
    to: resolvedState.to,
    totalCount,
    totalPages,
  };
}

export async function hasActiveGrowthPlanByOwnerEmail(email: string) {
  const ownership = await getMailBillingOwnershipByEmail(email);

  if (await hasOperationalBenefitByOwnerEmail(ownership.ownerEmail)) {
    return true;
  }

  const overview = await getMailBillingOverviewByOwnerEmail(ownership.ownerEmail);
  const subscription = overview?.subscription;
  return subscription?.status === "active" && subscription.initialPaymentComplete;
}

async function createLegacyTossPayBillingRegistration(input: {
  intent?: "billing" | "domain-assistance";
  origin: string;
  retAppScheme?: "officialmail://billing";
  user: OwnerUserRow;
}): Promise<TossPayBillingRegistrationResult> {
  const publicBaseUrl = resolveTossPayPublicBaseUrl(input.origin);
  const profile = await recoverBillingProfileFromTossStatus(
    await getBillingProfileByOwnerUserId(input.user.id),
  );
  const userToken = profile?.user_token || createTossPayUserToken(input.user.id);
  const displayId = profile?.display_id?.trim() || BILLING_PROFILE_DISPLAY_ID;
  const currentBillingKey =
    profile?.provider === "legacy_toss_pay"
      ? normalizeBillingKey(profile.billing_key)
      : null;
  const requestedAt = new Date();
  const resultCallback = getMailAbsoluteUrl(
    "/api/payments/toss-pay/billing/callback",
    publicBaseUrl,
  );
  const returnsToDomainAssistance = input.intent === "domain-assistance";
  const returnSuccessUrl = getMailAbsoluteUrl(
    returnsToDomainAssistance
      ? "/mail?panel=setup&section=domain&success=billing-registration-returned&billingIntent=domain-assistance"
      : "/mail?panel=setup&section=billing&success=billing-registration-returned",
    publicBaseUrl,
  );
  const returnFailureUrl = getMailAbsoluteUrl(
    returnsToDomainAssistance
      ? "/mail?panel=setup&section=domain&error=billing-registration-failed&billingIntent=domain-assistance"
      : "/mail?panel=setup&section=billing&error=billing-registration-failed",
    publicBaseUrl,
  );
  const payload = {
    apiKey: getTossPayApiKey(),
    userId: userToken,
    displayId,
    productDesc: BILLING_PRODUCT_NAME,
    resultCallback,
    returnSuccessUrl,
    returnFailureUrl,
    ...(input.retAppScheme ? { retAppScheme: input.retAppScheme } : {}),
  };
  const response = await requestJson<TossPayBillingCreateResponse>(
    TOSS_PAY_BILLING_CREATE_URL,
    payload,
  );
  const data = response.json;
  const isSuccess =
    response.ok && data?.code === 0 && typeof data.checkoutUri === "string";

  await withTransaction(async (connection) => {
    await assertDomainAssistanceAllowsBillingChangesForOwnerUserId(
      input.user.id,
      connection,
    );
    const updatedProfile = await upsertBillingProfileForRegistration(connection, {
      ownerUserId: input.user.id,
      userToken,
      displayId,
      billingKey: currentBillingKey,
      pendingBillingKey: isSuccess ? normalizeBillingKey(data?.billingKey) : null,
      replacementBillingKey:
        currentBillingKey ??
        (profile?.provider === "legacy_toss_pay"
          ? normalizeBillingKey(profile.replacement_billing_key)
          : null),
      provider: "legacy_toss_pay",
      providerCustomerKey: null,
      providerMid: null,
      status: isSuccess
        ? profile?.provider === "legacy_toss_pay"
          ? (profile.status ?? (currentBillingKey ? "active" : "pending"))
          : currentBillingKey
            ? "active"
            : "pending"
        : currentBillingKey
          ? "active"
          : "failed",
      requestedAt,
      lastErrorCode: isSuccess
        ? null
        : data?.errorCode ?? `HTTP_${response.status}`,
      lastErrorMessage: isSuccess
        ? null
        : data?.msg ??
          (response.rawText.slice(0, 500) || "빌링키 생성 요청에 실패했습니다."),
    });

    await ensureBillingSubscriptionRow(connection, {
      billingProfileId: updatedProfile.id,
      ownerUserId: input.user.id,
    });
  });

  if (!isSuccess || !data?.checkoutUri) {
    throw new Error(
      data?.msg || data?.errorCode || "billing-registration-request-failed",
    );
  }

  return {
    billingKey: data.billingKey ?? null,
    checkoutAndroidUri: data.checkoutAndroidUri ?? null,
    checkoutIosUri: data.checkoutIosUri ?? null,
    checkoutUri: data.checkoutUri,
    publicBaseUrl,
    usesCanonicalPublicBaseUrl: publicBaseUrl !== input.origin,
  };
}

export async function createTossPayBillingRegistrationForOwnerEmail(input: {
  email: string;
  intent?: "billing" | "domain-assistance";
  origin: string;
  retAppScheme?: "officialmail://billing";
}): Promise<TossPayBillingRegistrationResult> {
  await ensureBillingSchemaReady();

  const user = await getOwnerUserByEmail(input.email);

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

  if (!isTossPaymentsBillingEnabledForEmail(user.email)) {
    return await createLegacyTossPayBillingRegistration({
      intent: input.intent,
      origin: input.origin,
      retAppScheme: input.retAppScheme,
      user,
    });
  }

  const publicBaseUrl = resolveTossPayPublicBaseUrl(input.origin);
  const profile = await getBillingProfileByOwnerUserId(user.id);
  const userToken = profile?.user_token || createTossPayUserToken(user.id);
  const displayId = profile?.display_id?.trim() || BILLING_PROFILE_DISPLAY_ID;
  const isTossPaymentsProfile = profile?.provider === "toss_payments";
  const currentBillingKey = isTossPaymentsProfile
    ? normalizeBillingKey(profile?.billing_key)
    : null;
  const customerKey =
    (isTossPaymentsProfile ? profile?.provider_customer_key?.trim() : "") ||
    createTossCustomerKey();
  const requestedAt = new Date();
  const registrationToken = randomBytes(32).toString("base64url");
  const registrationTokenHash = createHash("sha256")
    .update(registrationToken)
    .digest("hex");
  const expiresAt = new Date(Date.now() + 15 * 60 * 1000);
  const intent = input.intent === "domain-assistance" ? input.intent : "billing";

  await withTransaction(async (connection) => {
    await assertDomainAssistanceAllowsBillingChangesForOwnerUserId(
      user.id,
      connection,
    );
    const updatedProfile = await upsertBillingProfileForRegistration(connection, {
      ownerUserId: user.id,
      userToken,
      displayId,
      billingKey: currentBillingKey,
      pendingBillingKey: null,
      replacementBillingKey: currentBillingKey,
      provider: "toss_payments",
      providerCustomerKey: customerKey,
      providerMid: isTossPaymentsProfile ? profile?.provider_mid ?? null : null,
      status: currentBillingKey ? "active" : "pending",
      requestedAt,
      lastErrorCode: null,
      lastErrorMessage: null,
    });

    await ensureBillingSubscriptionRow(connection, {
      billingProfileId: updatedProfile.id,
      ownerUserId: user.id,
    });

    await connection.query<ResultSetHeader>(
      `
        INSERT INTO mailbox_toss_pay_billing_registrations (
          owner_user_id,
          token_hash,
          provider_customer_key,
          intent,
          return_app_scheme,
          status,
          expires_at
        )
        VALUES (?, ?, ?, ?, ?, 'pending', ?)
      `,
      [
        user.id,
        registrationTokenHash,
        customerKey,
        intent,
        input.retAppScheme ?? null,
        expiresAt,
      ],
    );
  });

  const checkoutUri = getMailAbsoluteUrl(
    `/payments/toss/billing/register?registration=${encodeURIComponent(registrationToken)}`,
    publicBaseUrl,
  );

  return {
    billingKey: currentBillingKey,
    checkoutAndroidUri: null,
    checkoutIosUri: null,
    checkoutUri,
    publicBaseUrl,
    usesCanonicalPublicBaseUrl: publicBaseUrl !== input.origin,
  };
}

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

async function getBillingRegistrationByToken(
  token: string,
  connection?: PoolConnection,
  lock = false,
) {
  if (!/^[A-Za-z0-9_-]{40,100}$/.test(token)) {
    return null;
  }

  const executor = connection ?? getDbPool();
  const [rows] = await executor.query<BillingRegistrationRow[]>(
    `
      SELECT *
      FROM mailbox_toss_pay_billing_registrations
      WHERE token_hash = ?
      LIMIT 1
      ${lock ? "FOR UPDATE" : ""}
    `,
    [hashBillingRegistrationToken(token)],
  );

  return rows[0] ?? null;
}

function getBillingRegistrationReturnUrl(input: {
  intent: "billing" | "domain-assistance";
  origin: string;
  returnAppScheme: string | null;
  result: "failed" | "success";
}) {
  const resultQuery =
    input.result === "success"
      ? "success=billing-registration-returned"
      : "error=billing-registration-failed";

  if (input.returnAppScheme) {
    return `${input.returnAppScheme}?${resultQuery}`;
  }

  const path =
    input.intent === "domain-assistance"
      ? `/mail?panel=setup&section=domain&${resultQuery}&billingIntent=domain-assistance`
      : `/mail?panel=setup&section=billing&${resultQuery}`;

  return getMailAbsoluteUrl(path, input.origin);
}

export async function getTossPaymentsBillingAuthSession(
  registrationToken: string,
  origin: string,
): Promise<TossPaymentsBillingAuthSession> {
  await ensureBillingSchemaReady();
  const registration = await getBillingRegistrationByToken(registrationToken);

  if (
    !registration ||
    registration.status !== "pending" ||
    registration.expires_at.getTime() <= Date.now()
  ) {
    throw new Error("billing-registration-expired");
  }

  const [users] = await getDbPool().query<OwnerUserRow[]>(
    `SELECT id, email, company_name, display_name FROM users WHERE id = ? LIMIT 1`,
    [registration.owner_user_id],
  );
  const user = users[0];

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

  const publicBaseUrl = resolveTossPayPublicBaseUrl(origin);
  const encodedToken = encodeURIComponent(registrationToken);

  return {
    clientKey: getTossPaymentsClientKey(),
    clientKeySource: getTossPaymentsClientKeySource(),
    customerEmail: user.email,
    customerKey: registration.provider_customer_key,
    customerName: user.company_name || user.display_name || user.email,
    failUrl: getMailAbsoluteUrl(
      `/payments/toss/billing/fail?registration=${encodedToken}`,
      publicBaseUrl,
    ),
    successUrl: getMailAbsoluteUrl(
      `/payments/toss/billing/success?registration=${encodedToken}`,
      publicBaseUrl,
    ),
  };
}

export async function completeTossPaymentsBillingRegistration(input: {
  authKey: string;
  customerKey: string;
  origin: string;
  registrationToken: string;
}) {
  await ensureBillingSchemaReady();
  let issuedBillingKey: string | null = null;
  const prepared = await withTransaction(async (connection) => {
    const registration = await getBillingRegistrationByToken(
      input.registrationToken,
      connection,
      true,
    );

    if (
      !registration ||
      registration.status !== "pending" ||
      registration.expires_at.getTime() <= Date.now()
    ) {
      throw new Error("billing-registration-expired");
    }

    if (registration.provider_customer_key !== input.customerKey) {
      throw new Error("billing-customer-key-mismatch");
    }

    await connection.query<ResultSetHeader>(
      `
        UPDATE mailbox_toss_pay_billing_registrations
        SET status = 'processing', updated_at = NOW()
        WHERE id = ?
      `,
      [registration.id],
    );

    return registration;
  });

  try {
    const issued = await issueTossPaymentsBillingKey({
      authKey: input.authKey,
      customerKey: input.customerKey,
    });
    const billing = issued.data;
    const billingKey = normalizeBillingKey(billing.billingKey);

    if (!billingKey || billing.customerKey !== input.customerKey) {
      throw new Error("billing-key-response-invalid");
    }
    issuedBillingKey = billingKey;

    const cardNumber = billing.card?.number?.trim() || null;
    const cardLastFour = cardNumber
      ? cardNumber.replace(/\D/g, "").slice(-4) || null
      : null;
    const activatedAt = billing.authenticatedAt
      ? new Date(billing.authenticatedAt)
      : new Date();

    const replacedBillingKey = await withTransaction(async (connection) => {
      const profile = await getBillingProfileByOwnerUserId(
        prepared.owner_user_id,
        connection,
      );

      if (!profile || profile.provider_customer_key !== input.customerKey) {
        throw new Error("billing-profile-not-found");
      }

      await connection.query<ResultSetHeader>(
        `
          UPDATE mailbox_toss_pay_billing_profiles
          SET
            billing_key = ?,
            pending_billing_key = NULL,
            replacement_billing_key = NULL,
            provider = 'toss_payments',
            provider_mid = ?,
            status = 'active',
            activated_at = ?,
            removed_at = NULL,
            last_action = 'ACTIVATED',
            last_processed_at = NOW(),
            pay_method = 'CARD',
            card_method_type = ?,
            card_user_type = ?,
            card_company_name = ?,
            card_number_masked = ?,
            card_num4_print = ?,
            account_bank_code = NULL,
            account_bank_name = NULL,
            account_number_masked = NULL,
            last_error_code = NULL,
            last_error_message = NULL,
            updated_at = NOW()
          WHERE id = ?
        `,
        [
          billingKey,
          billing.mId ?? null,
          Number.isNaN(activatedAt.getTime()) ? new Date() : activatedAt,
          billing.card?.cardType ?? null,
          billing.card?.ownerType ?? null,
          billing.card?.issuerCode ?? "카드",
          cardNumber,
          cardLastFour,
          profile.id,
        ],
      );
      await ensureBillingSubscriptionRow(connection, {
        billingProfileId: profile.id,
        ownerUserId: profile.owner_user_id,
      });
      await connection.query<ResultSetHeader>(
        `
          UPDATE mailbox_toss_pay_billing_registrations
          SET status = 'completed', completed_at = NOW(), updated_at = NOW()
          WHERE id = ?
        `,
        [prepared.id],
      );

      return profile.billing_key;
    });

    if (replacedBillingKey && replacedBillingKey !== billingKey) {
      await deleteTossPaymentsBillingKey(replacedBillingKey).catch((error) => {
        console.error(
          `[toss-payments] stale billing key deletion failed owner=${prepared.owner_user_id}`,
          error instanceof Error ? error.message : error,
        );
      });
    }

    await recordMarketingLifecycleEvent({
      eventReference: `toss-payments-registration:${prepared.id}`,
      eventType: "payment_method_added",
      userId: prepared.owner_user_id,
    });

    return {
      redirectUrl: getBillingRegistrationReturnUrl({
        intent: prepared.intent,
        origin: resolveTossPayPublicBaseUrl(input.origin),
        result: "success",
        returnAppScheme: prepared.return_app_scheme,
      }),
    };
  } catch (error) {
    if (issuedBillingKey) {
      await deleteTossPaymentsBillingKey(issuedBillingKey).catch(() => undefined);
    }
    await getDbPool().query(
      `
        UPDATE mailbox_toss_pay_billing_registrations
        SET
          status = 'failed',
          last_error_code = ?,
          last_error_message = ?,
          updated_at = NOW()
        WHERE id = ?
      `,
      [
        error instanceof TossPaymentsRequestError ? error.code : "BILLING_REGISTRATION_FAILED",
        error instanceof Error ? error.message.slice(0, 1000) : String(error).slice(0, 1000),
        prepared.id,
      ],
    );
    await getDbPool().query(
      `
        UPDATE mailbox_toss_pay_billing_profiles
        SET
          status = CASE WHEN billing_key IS NULL THEN 'failed' ELSE 'active' END,
          last_error_code = ?,
          last_error_message = ?,
          updated_at = NOW()
        WHERE owner_user_id = ?
          AND provider = 'toss_payments'
          AND provider_customer_key = ?
      `,
      [
        error instanceof TossPaymentsRequestError ? error.code : "BILLING_REGISTRATION_FAILED",
        error instanceof Error ? error.message.slice(0, 1000) : String(error).slice(0, 1000),
        prepared.owner_user_id,
        prepared.provider_customer_key,
      ],
    );
    throw error;
  }
}

export async function failTossPaymentsBillingRegistration(input: {
  code?: string;
  message?: string;
  origin: string;
  registrationToken: string;
}) {
  await ensureBillingSchemaReady();
  const registration = await getBillingRegistrationByToken(input.registrationToken);

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

  await getDbPool().query(
    `
      UPDATE mailbox_toss_pay_billing_registrations
      SET
        status = 'failed',
        last_error_code = ?,
        last_error_message = ?,
        updated_at = NOW()
      WHERE id = ?
        AND status = 'pending'
    `,
    [input.code?.slice(0, 128) || null, input.message?.slice(0, 1000) || null, registration.id],
  );
  await getDbPool().query(
    `
      UPDATE mailbox_toss_pay_billing_profiles
      SET
        status = CASE WHEN billing_key IS NULL THEN 'failed' ELSE 'active' END,
        last_error_code = ?,
        last_error_message = ?,
        updated_at = NOW()
      WHERE owner_user_id = ?
        AND provider = 'toss_payments'
        AND provider_customer_key = ?
    `,
    [
      input.code?.slice(0, 128) || "BILLING_AUTH_FAILED",
      input.message?.slice(0, 1000) || "카드 인증이 완료되지 않았습니다.",
      registration.owner_user_id,
      registration.provider_customer_key,
    ],
  );

  return getBillingRegistrationReturnUrl({
    intent: registration.intent,
    origin: resolveTossPayPublicBaseUrl(input.origin),
    result: "failed",
    returnAppScheme: registration.return_app_scheme,
  });
}

export async function handleTossPayBillingCallback(payload: TossPayBillingCallbackPayload) {
  await ensureBillingSchemaReady();

  const ownerUserIdFromToken = parseTossPayUserToken(payload.userId);
  const billingKey = payload.billingKey?.trim() ?? "";
  const processedAt = parseTossPayProcessedAt(payload.processedTs) ?? new Date();
  const action = payload.action?.trim() ?? "";

  if (!billingKey) {
    throw new Error("billing-key-required");
  }

  if (action !== "ACTIVATED" && action !== "REMOVED") {
    throw new Error("unsupported-billing-action");
  }

  let affectedOwnerUserId: number | null = null;
  await withTransaction(async (connection) => {
    let profile =
      (await getBillingProfileByBillingKey(billingKey, connection)) ??
      (ownerUserIdFromToken !== null
        ? await getBillingProfileByOwnerUserId(ownerUserIdFromToken, connection)
        : null);

    if (!profile) {
      if (ownerUserIdFromToken === null) {
        throw new Error("billing-profile-not-found");
      }

      const ownerUser = await connection.query<OwnerUserRow[]>(
        `
          SELECT id, email, company_name, display_name
          FROM users
          WHERE id = ?
          LIMIT 1
        `,
        [ownerUserIdFromToken],
      );

      if (!ownerUser[0][0]) {
        throw new Error("billing-owner-not-found");
      }

      profile = await upsertBillingProfileForRegistration(connection, {
        ownerUserId: ownerUserIdFromToken,
        userToken: createTossPayUserToken(ownerUserIdFromToken),
        displayId: payload.displayId?.trim() || BILLING_PROFILE_DISPLAY_ID,
        billingKey: null,
        pendingBillingKey: action === "ACTIVATED" ? billingKey : null,
        replacementBillingKey: action === "REMOVED" ? billingKey : null,
        status: action === "ACTIVATED" ? "pending" : "inactive",
        requestedAt: processedAt,
        lastErrorCode: null,
        lastErrorMessage: null,
      });
    }

    affectedOwnerUserId = profile.owner_user_id;
    const currentBillingKey = normalizeBillingKey(profile.billing_key);
    const pendingBillingKey = normalizeBillingKey(profile.pending_billing_key);
    const replacementBillingKey = normalizeBillingKey(profile.replacement_billing_key);
    const resolvedDisplayId = payload.displayId?.trim() || profile.display_id || BILLING_PROFILE_DISPLAY_ID;

    if (action === "ACTIVATED") {
      await connection.query<ResultSetHeader>(
        `
          UPDATE mailbox_toss_pay_billing_profiles
          SET
            billing_key = ?,
            pending_billing_key = NULL,
            replacement_billing_key = NULL,
            display_id = ?,
            status = 'active',
            activated_at = ?,
            removed_at = NULL,
            last_action = 'ACTIVATED',
            last_processed_at = ?,
            pay_method = ?,
            card_method_type = ?,
            card_user_type = ?,
            card_company_no = ?,
            card_company_name = ?,
            card_number_masked = ?,
            card_num4_print = ?,
            card_bin_number = ?,
            account_bank_code = ?,
            account_bank_name = ?,
            account_number_masked = ?,
            last_error_code = NULL,
            last_error_message = NULL,
            updated_at = NOW()
          WHERE id = ?
        `,
        [
          billingKey,
          resolvedDisplayId,
          processedAt,
          processedAt,
          payload.payMethod ?? profile.pay_method,
          payload.cardMethodType ?? profile.card_method_type,
          payload.cardUserType ?? profile.card_user_type,
          payload.cardCompanyNo ?? profile.card_company_no,
          payload.cardCompanyName ?? profile.card_company_name,
          payload.cardNumber ?? profile.card_number_masked,
          payload.cardNum4Print ?? profile.card_num4_print,
          payload.cardBinNumber ?? profile.card_bin_number,
          payload.accountBankCode ?? profile.account_bank_code,
          payload.accountBankName ?? profile.account_bank_name,
          payload.accountNumber ?? profile.account_number_masked,
          profile.id,
        ],
      );
    } else {
      const isActiveRemoval = currentBillingKey === billingKey;
      const isPendingRemoval = pendingBillingKey === billingKey;
      const isReplacementRemoval = replacementBillingKey === billingKey;

      let nextBillingKey = currentBillingKey;
      let nextPendingBillingKey = pendingBillingKey;
      let nextReplacementBillingKey = replacementBillingKey;
      let nextStatus = profile.status;
      let shouldPauseSubscription = false;

      if (isReplacementRemoval) {
        nextReplacementBillingKey = null;

        if (isActiveRemoval) {
          nextBillingKey = null;
        }

        nextStatus = nextPendingBillingKey
          ? "pending"
          : nextBillingKey
            ? profile.status
            : "inactive";
      } else if (isActiveRemoval) {
        nextBillingKey = null;
        nextStatus = nextPendingBillingKey ? "pending" : "removed";
        shouldPauseSubscription = !nextPendingBillingKey;
      } else if (isPendingRemoval) {
        nextPendingBillingKey = null;
        nextStatus = nextBillingKey ? profile.status : "inactive";
      } else {
        nextStatus =
          profile.status === "active" && !nextBillingKey && !nextPendingBillingKey
            ? "removed"
            : profile.status;
        shouldPauseSubscription = nextStatus === "removed";
      }

      const nextRemovedAt =
        nextStatus === "removed" || (!nextBillingKey && !nextPendingBillingKey)
          ? processedAt
          : null;
      const nextPayMethod = nextStatus === "active" ? payload.payMethod ?? profile.pay_method : null;
      const nextCardMethodType =
        nextStatus === "active" ? payload.cardMethodType ?? profile.card_method_type : null;
      const nextCardUserType =
        nextStatus === "active" ? payload.cardUserType ?? profile.card_user_type : null;
      const nextCardCompanyNo =
        nextStatus === "active" ? payload.cardCompanyNo ?? profile.card_company_no : null;
      const nextCardCompanyName =
        nextStatus === "active" ? payload.cardCompanyName ?? profile.card_company_name : null;
      const nextCardNumberMasked =
        nextStatus === "active" ? payload.cardNumber ?? profile.card_number_masked : null;
      const nextCardNum4Print =
        nextStatus === "active" ? payload.cardNum4Print ?? profile.card_num4_print : null;
      const nextCardBinNumber =
        nextStatus === "active" ? payload.cardBinNumber ?? profile.card_bin_number : null;
      const nextAccountBankCode =
        nextStatus === "active" ? payload.accountBankCode ?? profile.account_bank_code : null;
      const nextAccountBankName =
        nextStatus === "active" ? payload.accountBankName ?? profile.account_bank_name : null;
      const nextAccountNumberMasked =
        nextStatus === "active" ? payload.accountNumber ?? profile.account_number_masked : null;

      await connection.query<ResultSetHeader>(
        `
          UPDATE mailbox_toss_pay_billing_profiles
          SET
            billing_key = ?,
            pending_billing_key = ?,
            replacement_billing_key = ?,
            display_id = ?,
            status = ?,
            activated_at = ?,
            removed_at = ?,
            last_action = 'REMOVED',
            last_processed_at = ?,
            pay_method = ?,
            card_method_type = ?,
            card_user_type = ?,
            card_company_no = ?,
            card_company_name = ?,
            card_number_masked = ?,
            card_num4_print = ?,
            card_bin_number = ?,
            account_bank_code = ?,
            account_bank_name = ?,
            account_number_masked = ?,
            last_error_code = NULL,
            last_error_message = NULL,
            updated_at = NOW()
          WHERE id = ?
        `,
        [
          nextBillingKey,
          nextPendingBillingKey,
          nextReplacementBillingKey,
          resolvedDisplayId,
          nextStatus,
          profile.activated_at,
          nextRemovedAt,
          processedAt,
          nextPayMethod,
          nextCardMethodType,
          nextCardUserType,
          nextCardCompanyNo,
          nextCardCompanyName,
          nextCardNumberMasked,
          nextCardNum4Print,
          nextCardBinNumber,
          nextAccountBankCode,
          nextAccountBankName,
          nextAccountNumberMasked,
          profile.id,
        ],
      );

      const subscription = await ensureBillingSubscriptionRow(connection, {
        billingProfileId: profile.id,
        ownerUserId: profile.owner_user_id,
      });

      if (shouldPauseSubscription) {
        await connection.query<ResultSetHeader>(
          `
            UPDATE mailbox_toss_pay_subscriptions
            SET
              status = CASE
                WHEN status = 'cancelled' THEN 'cancelled'
                ELSE 'paused'
              END,
              updated_at = NOW()
            WHERE id = ?
          `,
          [subscription.id],
        );
      }

      return;
    }

    await ensureBillingSubscriptionRow(connection, {
      billingProfileId: profile.id,
      ownerUserId: profile.owner_user_id,
    });
  });
  if (action === "ACTIVATED" && affectedOwnerUserId !== null) {
    await recordMarketingLifecycleEvent({
      eventReference: `toss-pay-registration:${affectedOwnerUserId}:${processedAt.getTime()}`,
      eventType: "payment_method_added",
      userId: affectedOwnerUserId,
    });
  }
  if (action === "REMOVED" && affectedOwnerUserId !== null) {
    await reconcileExternalMailAccessAfterBillingChange(affectedOwnerUserId);
  }
}

export async function activateTossPaySubscriptionForOwnerEmail(email: string) {
  await ensureBillingSchemaReady();

  const user = await getOwnerUserByEmail(email);

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

  const prepared = await withTransaction(async (connection) => {
    const profile = await getBillingProfileByOwnerUserId(user.id, connection);

    if (!hasUsableBillingProfile(profile)) {
      throw new Error("billing-profile-reregistration-required");
    }

    const subscription = await ensureBillingSubscriptionRow(connection, {
      billingProfileId: profile.id,
      ownerUserId: user.id,
    });

    if (subscription.status === "active" && subscription.last_charged_at) {
      return {
        alreadyActive: true,
        billingKey: profile.billing_key,
        billingProfileId: profile.id,
        subscription,
        status: "active" as const,
      };
    }

    if (!(await acquireSubscriptionProcessingLock(connection, subscription.id))) {
      throw new Error("subscription-start-in-progress");
    }

    return {
      alreadyActive: false,
      billingKey: profile.billing_key,
      billingProfileId: profile.id,
      subscription,
      status: "pending" as const,
    };
  });

  if (prepared.alreadyActive) {
    const restoredMembers = await restoreManagedMembersAfterBillingRecovery(user.id).catch(
      (error) => {
        console.error(
          `[toss-pay-billing] owner=${user.id} member restoration failed`,
          error instanceof Error ? error.message : error,
        );
        return 0;
      },
    );

    if (restoredMembers === 0) {
      await syncGrowthPlanMailboxQuotas(user.id);
    }

    await reconcileExternalMailAccessAfterBillingChange(user.id);

    return {
      chargedAmount: 0,
      initialPaymentComplete: true,
      nextChargeAt: normalizeDate(prepared.subscription.next_charge_at),
      status: "active" as const,
    };
  }

  try {
    const billingSeatCount = await getGrowthPlanSeatCountByOwnerUserId(user.id);
    const seatChargeAmount = resolveMailBillingSeatChargeAmount(
      prepared.subscription.seat_charge_amount,
    );
    const chargeSummary = createMailBillingChargeSummary(
      billingSeatCount,
      seatChargeAmount,
    );
    const charge = await executeTossPayBillingCharge({
      ownerUserId: user.id,
      ownerEmail: user.email,
      billingProfileId: prepared.billingProfileId,
      billingKey: prepared.billingKey,
      subscriptionId: prepared.subscription.id,
      chargeKind: "manual",
      amount: chargeSummary.amount,
      amountTaxFree: 0,
      productDesc: buildManualChargeProductDescription(
        chargeSummary.billableSeats,
        false,
      ),
      spreadOut: prepared.subscription.spread_out,
      sendFailPush: toBoolean(prepared.subscription.send_fail_push),
      cashReceipt: toBoolean(prepared.subscription.cash_receipt),
      cashReceiptTradeOption: prepared.subscription.cash_receipt_trade_option,
      metadataText: serializeMetadata({
        billingSeatCount: chargeSummary.billableSeats,
        chargeKind: "manual",
        reason: "growth-plan-start",
      }),
    });

    if (charge.status !== "success") {
      throw new Error(charge.errorMessage || "growth-plan-initial-charge-failed");
    }

    const chargedAt = charge.approvedAt ? new Date(charge.approvedAt) : new Date();
    const nextChargeAt = createNextMonthlyChargeAt(chargedAt);

    await withTransaction(async (connection) => {
      await connection.query<ResultSetHeader>(
        `
          UPDATE mailbox_toss_pay_subscriptions
          SET
            status = 'active',
            seat_charge_amount = ?,
            next_charge_at = ?,
            retry_after_at = NULL,
            last_charged_at = ?,
            last_charge_status = 'success',
            consecutive_failures = 0,
            billing_grace_started_at = NULL,
            billing_grace_ends_at = NULL,
            billing_grace_retry_count = 0,
            billing_failure_notice_sent_on = NULL,
            processing_started_at = NULL,
            updated_at = NOW()
          WHERE id = ?
        `,
        [seatChargeAmount, nextChargeAt, chargedAt, prepared.subscription.id],
      );
    });

    await recordMarketingLifecycleEvent({
      eventReference: `growth:${charge.orderNo}`,
      eventType: "growth_plan_started",
      userId: user.id,
    });
    const restoredMembers = await restoreManagedMembersAfterBillingRecovery(user.id).catch(
      (error) => {
        console.error(
          `[toss-pay-billing] owner=${user.id} member restoration failed`,
          error instanceof Error ? error.message : error,
        );
        return 0;
      },
    );

    if (restoredMembers === 0) {
      await syncGrowthPlanMailboxQuotas(user.id);
    }

    return {
      chargedAmount: charge.amount,
      initialPaymentComplete: true,
      nextChargeAt: nextChargeAt.toISOString(),
      status: "active" as const,
    };
  } catch (error) {
    await withTransaction(async (connection) => {
      await connection.query<ResultSetHeader>(
        `
          UPDATE mailbox_toss_pay_subscriptions
          SET
            status = 'pending',
            next_charge_at = NULL,
            retry_after_at = NULL,
            last_charge_status = 'failed',
            processing_started_at = NULL,
            updated_at = NOW()
          WHERE id = ?
            AND last_charged_at IS NULL
        `,
        [prepared.subscription.id],
      );
    }).catch(() => {});

    throw error;
  }
}

export async function pauseTossPaySubscriptionForOwnerEmail(email: string) {
  await ensureBillingSchemaReady();

  const user = await getOwnerUserByEmail(email);

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

  const paused = await withTransaction(async (connection) => {
    const profile = await getBillingProfileByOwnerUserId(user.id, connection);

    if (!hasUsableBillingProfile(profile)) {
      throw new Error("billing-profile-reregistration-required");
    }

    const subscription = await ensureBillingSubscriptionRow(connection, {
      billingProfileId: profile.id,
      ownerUserId: user.id,
    });

    await connection.query<ResultSetHeader>(
      `
        UPDATE mailbox_toss_pay_subscriptions
        SET
          status = 'paused',
          retry_after_at = NULL,
          billing_grace_started_at = NULL,
          billing_grace_ends_at = NULL,
          billing_grace_retry_count = 0,
          billing_failure_notice_sent_on = NULL,
          updated_at = NOW()
        WHERE id = ?
      `,
      [subscription.id],
    );

    return {
      nextChargeAt: normalizeDate(subscription.next_charge_at),
      status: "paused" as const,
    };
  });
  await reconcileExternalMailAccessAfterBillingChange(user.id);
  return paused;
}

export async function clearTossPayBillingProfileForOwnerUserId(
  ownerUserId: number,
  connection?: PoolConnection,
) {
  const executor = connection ?? getDbPool();
  const profile = await getBillingProfileByOwnerUserId(ownerUserId, connection);

  if (!profile) {
    return null;
  }

  await executor.query<ResultSetHeader>(
    `
      UPDATE mailbox_toss_pay_billing_profiles
      SET
        billing_key = NULL,
        pending_billing_key = NULL,
        replacement_billing_key = NULL,
        status = 'removed',
        removed_at = COALESCE(removed_at, NOW()),
        last_action = 'REMOVED',
        last_processed_at = NOW(),
        pay_method = NULL,
        card_method_type = NULL,
        card_user_type = NULL,
        card_company_no = NULL,
        card_company_name = NULL,
        card_number_masked = NULL,
        card_num4_print = NULL,
        card_bin_number = NULL,
        account_bank_code = NULL,
        account_bank_name = NULL,
        account_number_masked = NULL,
        last_error_code = NULL,
        last_error_message = NULL,
        updated_at = NOW()
      WHERE owner_user_id = ?
    `,
    [ownerUserId],
  );

  return await getBillingProfileByOwnerUserId(ownerUserId, connection);
}

async function insertChargeLog(
  connection: PoolConnection,
  input: {
    ownerUserId: number;
    subscriptionId: null | number;
    billingProfileId: number;
    chargeKind: TossPayChargeKind;
    orderNo: string;
    amount: number;
    amountTaxFree: number;
    productDesc: string;
    status: "failed" | "requested" | "skipped" | "success";
    requestedAt: Date;
    approvedAt: Date | null;
    payMethod: string | null;
    cardCompanyName: string | null;
    cardNum4Print: string | null;
    cardMethodType: string | null;
    accountBankName: string | null;
    accountNumberMasked: string | null;
    provider?: TossBillingProvider;
    payToken: string | null;
    transactionId: string | null;
    responseCode: number | null;
    errorCode: string | null;
    errorMessage: string | null;
    rawResponse: string;
  },
) {
  const [result] = await connection.query<ResultSetHeader>(
    `
      INSERT INTO mailbox_toss_pay_billing_charges (
        owner_user_id,
        subscription_id,
        billing_profile_id,
        charge_kind,
        status,
        order_no,
        amount,
        amount_tax_free,
        product_desc,
        requested_at,
        approved_at,
        pay_method,
        card_company_name,
        card_num4_print,
        card_method_type,
        account_bank_name,
        account_number_masked,
        provider,
        pay_token,
        transaction_id,
        response_code,
        error_code,
        error_message,
        raw_response
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `,
    [
      input.ownerUserId,
      input.subscriptionId,
      input.billingProfileId,
      input.chargeKind,
      input.status,
      input.orderNo,
      input.amount,
      input.amountTaxFree,
      input.productDesc,
      input.requestedAt,
      input.approvedAt,
      input.payMethod,
      input.cardCompanyName,
      input.cardNum4Print,
      input.cardMethodType,
      input.accountBankName,
      input.accountNumberMasked,
      input.provider ?? "toss_payments",
      input.payToken,
      input.transactionId,
      input.responseCode,
      input.errorCode,
      input.errorMessage,
      input.rawResponse,
    ],
  );

  return result.insertId;
}

async function acquireSubscriptionProcessingLock(connection: PoolConnection, subscriptionId: number) {
  const [result] = await connection.query<ResultSetHeader>(
    `
      UPDATE mailbox_toss_pay_subscriptions
      SET processing_started_at = NOW(), updated_at = NOW()
      WHERE id = ?
        AND (
          processing_started_at IS NULL
          OR processing_started_at < DATE_SUB(NOW(), INTERVAL 30 MINUTE)
        )
    `,
    [subscriptionId],
  );

  return result.affectedRows === 1;
}

async function releaseSubscriptionProcessingLock(connection: PoolConnection, subscriptionId: number) {
  await connection.query<ResultSetHeader>(
    `
      UPDATE mailbox_toss_pay_subscriptions
      SET processing_started_at = NULL, updated_at = NOW()
      WHERE id = ?
    `,
    [subscriptionId],
  );
}

type BillingChargeExecutionInput = {
  ownerUserId: number;
  ownerEmail: string;
  billingProfileId: number;
  billingKey: string;
  subscriptionId: null | number;
  subscriptionNextChargeAt?: Date | null;
  billingGraceStartedAt?: Date | null;
  billingGraceRetryCount?: number;
  scheduledRetryAt?: Date | null;
  chargeKind: TossPayChargeKind;
  amount: number;
  amountTaxFree: number;
  productDesc: string;
  spreadOut: number;
  sendFailPush: boolean;
  cashReceipt: boolean;
  cashReceiptTradeOption: string;
  metadataText: string | null;
  orderNo?: string;
};

async function executeLegacyTossPayBillingCharge(
  input: BillingChargeExecutionInput,
) {
  const orderNo =
    input.orderNo?.trim().slice(0, 64) ||
    createTossPayOrderNo(input.chargeKind);
  const requestedAt = new Date();
  const metadata = input.metadataText
    ? input.metadataText
    : serializeMetadata({
        chargeKind: input.chargeKind,
        ownerEmail: input.ownerEmail,
      });
  const payload = {
    apiKey: getTossPayApiKey(),
    billingKey: input.billingKey,
    orderNo,
    productDesc: input.productDesc,
    spreadOut: input.spreadOut,
    amount: input.amount,
    amountTaxFree: input.amountTaxFree,
    cashReceipt: input.cashReceipt,
    sendFailPush: input.sendFailPush,
    cashReceiptTradeOption: input.cashReceiptTradeOption || "GENERAL",
    metadata,
  };
  const response = await requestJson<TossPayBillResponse>(
    TOSS_PAY_BILLING_BILL_URL,
    payload,
  );
  const data = response.json;
  const approvedAt = parseTossPayProcessedAt(data?.approvalTime);
  const status =
    response.ok && data?.code === 0 ? "success" : ("failed" as const);
  const errorCode =
    status === "success" ? null : data?.errorCode ?? `HTTP_${response.status}`;
  const errorMessage =
    status === "success"
      ? null
      : data?.msg ??
        (response.rawText.slice(0, 1000) ||
          "토스페이 자동결제 승인 요청에 실패했습니다.");

  return await withTransaction(async (connection) => {
    let cycleFailure: TossPayChargeExecutionResult["cycleFailure"] = null;
    const chargeId = await insertChargeLog(connection, {
      ownerUserId: input.ownerUserId,
      subscriptionId: input.subscriptionId,
      billingProfileId: input.billingProfileId,
      chargeKind: input.chargeKind,
      orderNo,
      amount: input.amount,
      amountTaxFree: input.amountTaxFree,
      productDesc: input.productDesc,
      status,
      requestedAt,
      approvedAt,
      payMethod: data?.payMethod ?? null,
      cardCompanyName: data?.cardCompanyName ?? null,
      cardNum4Print: data?.cardNum4Print ?? null,
      cardMethodType: data?.cardMethodType ?? null,
      accountBankName: null,
      accountNumberMasked: null,
      provider: "legacy_toss_pay",
      payToken: data?.payToken ?? null,
      transactionId: data?.transactionId ?? null,
      responseCode: data?.code ?? response.status,
      errorCode,
      errorMessage,
      rawResponse: response.rawText,
    });

    if (input.subscriptionId !== null && input.chargeKind === "cycle") {
      if (status === "success") {
        const nextChargeBase = input.subscriptionNextChargeAt ?? requestedAt;
        const nextChargeAt = createNextMonthlyChargeAt(nextChargeBase);

        await connection.query<ResultSetHeader>(
          `
            UPDATE mailbox_toss_pay_subscriptions
            SET
              status = 'active',
              next_charge_at = ?,
              retry_after_at = NULL,
              last_charged_at = ?,
              last_charge_status = 'success',
              consecutive_failures = 0,
              billing_grace_started_at = NULL,
              billing_grace_ends_at = NULL,
              billing_grace_retry_count = 0,
              billing_failure_notice_sent_on = NULL,
              updated_at = NOW()
            WHERE id = ?
          `,
          [nextChargeAt, approvedAt ?? requestedAt, input.subscriptionId],
        );
      } else {
        const isGraceRetry = Boolean(input.billingGraceStartedAt);
        const graceStartedAt = input.billingGraceStartedAt ?? requestedAt;
        const retrySlots = createBillingGraceRetrySlots(graceStartedAt);
        const retryCount = isGraceRetry
          ? Math.max(0, input.billingGraceRetryCount ?? 0) + 1
          : 0;
        const downgraded = retryCount >= BILLING_GRACE_RETRY_LIMIT;
        const nextRetryAt = downgraded
          ? null
          : getBillingGraceRetrySlot(graceStartedAt, retryCount);
        const graceEndsAt = retrySlots.at(-1) ?? requestedAt;
        const noticeDue =
          isGraceRetry && isLastBillingRetryOfKstDay(input.scheduledRetryAt);

        await connection.query<ResultSetHeader>(
          `
            UPDATE mailbox_toss_pay_subscriptions
            SET
              status = ?,
              retry_after_at = ?,
              last_charge_status = 'failed',
              consecutive_failures = consecutive_failures + 1,
              billing_grace_started_at = ?,
              billing_grace_ends_at = ?,
              billing_grace_retry_count = ?,
              billing_failure_notice_sent_on = CASE
                WHEN ? = 0 THEN NULL
                ELSE billing_failure_notice_sent_on
              END,
              updated_at = NOW()
            WHERE id = ?
          `,
          [
            downgraded ? "paused" : "active",
            nextRetryAt,
            graceStartedAt,
            graceEndsAt,
            retryCount,
            isGraceRetry ? 1 : 0,
            input.subscriptionId,
          ],
        );
        cycleFailure = {
          downgraded,
          graceEndsAt: graceEndsAt.toISOString(),
          nextRetryAt: nextRetryAt?.toISOString() ?? null,
          noticeDue,
          retryCount,
          scheduledRetryAt: input.scheduledRetryAt?.toISOString() ?? null,
        };
      }
    }

    return {
      amount: input.amount,
      approvedAt: approvedAt ? approvedAt.toISOString() : null,
      chargeId,
      errorCode,
      errorMessage,
      orderNo,
      payMethod: data?.payMethod ?? null,
      productDesc: input.productDesc,
      status,
      cycleFailure,
    } satisfies TossPayChargeExecutionResult;
  });
}

async function executeTossPaymentsBillingCharge(
  input: BillingChargeExecutionInput,
) {
  const providerProfile = await getBillingProfileByOwnerUserId(input.ownerUserId);

  if (
    !providerProfile ||
    providerProfile.provider !== "toss_payments" ||
    providerProfile.billing_key !== input.billingKey ||
    !providerProfile.provider_customer_key
  ) {
    throw new Error("billing-profile-reregistration-required");
  }

  const orderNo = input.orderNo?.trim().slice(0, 64) || createTossPayOrderNo(input.chargeKind);
  const requestedAt = new Date();
  let data: Awaited<ReturnType<typeof chargeTossPaymentsBillingKey>>["data"] | null = null;
  let rawResponse = "";
  let responseCode = 200;
  let errorCode: string | null = null;
  let errorMessage: string | null = null;

  try {
    const response = await chargeTossPaymentsBillingKey({
      amount: input.amount,
      billingKey: input.billingKey,
      customerEmail: input.ownerEmail,
      customerKey: providerProfile.provider_customer_key,
      orderId: orderNo,
      orderName: input.productDesc,
      taxFreeAmount: input.amountTaxFree,
    });
    data = response.data;
    rawResponse = response.rawText;
    responseCode = response.status;

    if (data.status !== "DONE" || !data.paymentKey) {
      errorCode = data.status || "BILLING_PAYMENT_NOT_DONE";
      errorMessage = "토스페이먼츠 자동결제가 완료 상태로 승인되지 않았습니다.";
    }
  } catch (error) {
    responseCode = error instanceof TossPaymentsRequestError ? error.status : 500;
    errorCode =
      error instanceof TossPaymentsRequestError
        ? error.code
        : "TOSS_PAYMENTS_BILLING_REQUEST_FAILED";
    errorMessage =
      error instanceof Error
        ? error.message
        : "토스페이먼츠 자동결제 승인 요청에 실패했습니다.";
    rawResponse = JSON.stringify({ code: errorCode, message: errorMessage });
  }

  const approvedAt = data?.approvedAt ? new Date(data.approvedAt) : null;
  const normalizedApprovedAt =
    approvedAt && !Number.isNaN(approvedAt.getTime()) ? approvedAt : null;
  const status = errorCode === null ? ("success" as const) : ("failed" as const);
  const cardNumber = data?.card?.number?.trim() || null;
  const cardLastFour = cardNumber
    ? cardNumber.replace(/\D/g, "").slice(-4) || null
    : null;

  return await withTransaction(async (connection) => {
    let cycleFailure: TossPayChargeExecutionResult["cycleFailure"] = null;
    const chargeId = await insertChargeLog(connection, {
      ownerUserId: input.ownerUserId,
      subscriptionId: input.subscriptionId,
      billingProfileId: input.billingProfileId,
      chargeKind: input.chargeKind,
      orderNo,
      amount: input.amount,
      amountTaxFree: input.amountTaxFree,
      productDesc: input.productDesc,
      status,
      requestedAt,
      approvedAt: normalizedApprovedAt,
      payMethod: data?.method ?? "CARD",
      cardCompanyName: data?.card?.issuerCode ?? providerProfile.card_company_name,
      cardNum4Print: cardLastFour ?? providerProfile.card_num4_print,
      cardMethodType: data?.card?.cardType ?? null,
      accountBankName: null,
      accountNumberMasked: null,
      provider: "toss_payments",
      payToken: data?.paymentKey ?? null,
      transactionId: data?.paymentKey ?? null,
      responseCode,
      errorCode,
      errorMessage,
      rawResponse,
    });

    if (input.subscriptionId !== null && input.chargeKind === "cycle") {
      if (status === "success") {
        const nextChargeBase = input.subscriptionNextChargeAt ?? requestedAt;
        const nextChargeAt = createNextMonthlyChargeAt(nextChargeBase);

        await connection.query<ResultSetHeader>(
          `
            UPDATE mailbox_toss_pay_subscriptions
            SET
              status = 'active',
              next_charge_at = ?,
              retry_after_at = NULL,
              last_charged_at = ?,
              last_charge_status = 'success',
              consecutive_failures = 0,
              billing_grace_started_at = NULL,
              billing_grace_ends_at = NULL,
              billing_grace_retry_count = 0,
              billing_failure_notice_sent_on = NULL,
              updated_at = NOW()
            WHERE id = ?
          `,
          [nextChargeAt, normalizedApprovedAt ?? requestedAt, input.subscriptionId],
        );
      } else {
        const isGraceRetry = Boolean(input.billingGraceStartedAt);
        const graceStartedAt = input.billingGraceStartedAt ?? requestedAt;
        const retrySlots = createBillingGraceRetrySlots(graceStartedAt);
        const retryCount = isGraceRetry
          ? Math.max(0, input.billingGraceRetryCount ?? 0) + 1
          : 0;
        const downgraded = retryCount >= BILLING_GRACE_RETRY_LIMIT;
        const nextRetryAt = downgraded
          ? null
          : getBillingGraceRetrySlot(graceStartedAt, retryCount);
        const graceEndsAt = retrySlots.at(-1) ?? requestedAt;
        const noticeDue =
          isGraceRetry && isLastBillingRetryOfKstDay(input.scheduledRetryAt);

        await connection.query<ResultSetHeader>(
          `
            UPDATE mailbox_toss_pay_subscriptions
            SET
              status = ?,
              retry_after_at = ?,
              last_charge_status = 'failed',
              consecutive_failures = consecutive_failures + 1,
              billing_grace_started_at = ?,
              billing_grace_ends_at = ?,
              billing_grace_retry_count = ?,
              billing_failure_notice_sent_on = CASE
                WHEN ? = 0 THEN NULL
                ELSE billing_failure_notice_sent_on
              END,
              updated_at = NOW()
            WHERE id = ?
          `,
          [
            downgraded ? "paused" : "active",
            nextRetryAt,
            graceStartedAt,
            graceEndsAt,
            retryCount,
            isGraceRetry ? 1 : 0,
            input.subscriptionId,
          ],
        );
        cycleFailure = {
          downgraded,
          graceEndsAt: graceEndsAt.toISOString(),
          nextRetryAt: nextRetryAt?.toISOString() ?? null,
          noticeDue,
          retryCount,
          scheduledRetryAt: input.scheduledRetryAt?.toISOString() ?? null,
        };
      }
    }

    return {
      amount: input.amount,
      approvedAt: normalizedApprovedAt ? normalizedApprovedAt.toISOString() : null,
      chargeId,
      errorCode,
      errorMessage,
      orderNo,
      payMethod: data?.method ?? "CARD",
      productDesc: input.productDesc,
      status,
      cycleFailure,
    } satisfies TossPayChargeExecutionResult;
  });
}

async function executeTossPayBillingCharge(
  input: BillingChargeExecutionInput,
) {
  const profile = await getBillingProfileByOwnerUserId(input.ownerUserId);

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

  let result: TossPayChargeExecutionResult;
  if (profile.provider === "legacy_toss_pay") {
    result = await executeLegacyTossPayBillingCharge(input);
  } else if (
    profile.provider === "toss_payments" &&
    profile.provider_customer_key
  ) {
    result = await executeTossPaymentsBillingCharge(input);
  } else {
    throw new Error("billing-profile-reregistration-required");
  }

  if (result.status === "success" && result.amount > 0) {
    await recordMarketingLifecycleEvent({
      currency: "KRW",
      eventReference: `toss:${result.orderNo}`,
      eventType: "purchase_completed",
      userId: input.ownerUserId,
      value: result.amount,
    });
  }

  return result;
}

async function skipCycleCharge(input: {
  ownerUserId: number;
  billingProfileId: number;
  provider: TossBillingProvider;
  subscriptionId: number;
  subscriptionNextChargeAt: Date | null;
}) {
  const requestedAt = new Date();
  const orderNo = createTossPayOrderNo("cycle");

  return await withTransaction(async (connection) => {
    const chargeId = await insertChargeLog(connection, {
      ownerUserId: input.ownerUserId,
      subscriptionId: input.subscriptionId,
      billingProfileId: input.billingProfileId,
      chargeKind: "cycle",
      orderNo,
      amount: 0,
      amountTaxFree: 0,
      productDesc: "성장플랜 정기결제 건너뜀",
      status: "skipped",
      requestedAt,
      approvedAt: null,
      payMethod: null,
      cardCompanyName: null,
      cardNum4Print: null,
      cardMethodType: null,
      accountBankName: null,
      accountNumberMasked: null,
      provider: input.provider,
      payToken: null,
      transactionId: null,
      responseCode: 0,
      errorCode: null,
      errorMessage: "과금 대상 멤버가 없어 이번 회차 청구를 건너뜁니다.",
      rawResponse: JSON.stringify({
        code: 0,
        message: "No billable members. Charge skipped.",
      }),
    });
    const nextChargeAt = createNextMonthlyChargeAt(input.subscriptionNextChargeAt ?? requestedAt);

    await connection.query<ResultSetHeader>(
      `
        UPDATE mailbox_toss_pay_subscriptions
        SET
          next_charge_at = ?,
          retry_after_at = NULL,
          last_charge_status = 'skipped',
          consecutive_failures = 0,
          billing_grace_started_at = NULL,
          billing_grace_ends_at = NULL,
          billing_grace_retry_count = 0,
          billing_failure_notice_sent_on = NULL,
          updated_at = NOW()
        WHERE id = ?
      `,
      [nextChargeAt, input.subscriptionId],
    );

    return {
      amount: 0,
      approvedAt: null,
      chargeId,
      errorCode: null,
      errorMessage: "과금 대상 멤버가 없어 이번 회차 청구를 건너뜁니다.",
      orderNo,
      payMethod: null,
      productDesc: "성장플랜 정기결제 건너뜀",
      status: "skipped",
      cycleFailure: null,
    } satisfies TossPayChargeExecutionResult;
  });
}

export async function requestManualTossPayBillingChargeByOwnerEmail(email: string) {
  await ensureBillingSchemaReady();

  const user = await getOwnerUserByEmail(email);

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

  const profile = await getBillingProfileByOwnerUserId(user.id);

  if (!hasUsableBillingProfile(profile)) {
    throw new Error("billing-profile-reregistration-required");
  }

  const subscription = await getBillingSubscriptionByOwnerUserId(user.id);
  const billingSeatCount = await getGrowthPlanSeatCountByOwnerUserId(user.id);
  const preview = createMailBillingPreview(
    billingSeatCount,
    subscription?.seat_charge_amount,
  );

  return await executeTossPayBillingCharge({
    ownerUserId: user.id,
    ownerEmail: user.email,
    billingProfileId: profile.id,
    billingKey: profile.billing_key,
    subscriptionId: subscription?.id ?? null,
    chargeKind: "manual",
    amount: preview.chargeAmount || DEFAULT_MANUAL_TEST_AMOUNT,
    amountTaxFree: 0,
    productDesc: buildManualChargeProductDescription(
      preview.billableSeats,
      preview.usesFallbackAmount,
    ),
    spreadOut: subscription?.spread_out ?? 0,
    sendFailPush: subscription ? toBoolean(subscription.send_fail_push) : true,
    cashReceipt: subscription ? toBoolean(subscription.cash_receipt) : false,
    cashReceiptTradeOption: subscription?.cash_receipt_trade_option ?? "GENERAL",
    metadataText:
      subscription?.metadata_text ??
      serializeMetadata({
        billingSeatCount,
        chargeKind: "manual",
        previewMode: preview.usesFallbackAmount ? "test-fallback" : "live-members",
      }),
  });
}

export async function requestDomainSetupAssistanceCharge(input: {
  amount: number;
  email: string;
  requestId: number;
}) {
  await ensureBillingSchemaReady();

  const user = await getOwnerUserByEmail(input.email);

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

  const orderPrefix = `om-domain-${input.requestId}-`;
  const [previousRows] = await getDbPool().query<BillingChargeRow[]>(
    `
      SELECT
        id,
        charge_kind,
        status,
        order_no,
        amount,
        product_desc,
        requested_at,
        approved_at,
        pay_method,
        card_company_name,
        card_num4_print,
        error_code,
        error_message
      FROM mailbox_toss_pay_billing_charges
      WHERE owner_user_id = ?
        AND order_no LIKE ?
      ORDER BY id DESC
    `,
    [user.id, `${orderPrefix}%`],
  );
  const successfulCharge = previousRows.find((row) => row.status === "success");

  if (successfulCharge) {
    return {
      amount: successfulCharge.amount,
      approvedAt: normalizeDate(successfulCharge.approved_at),
      chargeId: successfulCharge.id,
      errorCode: successfulCharge.error_code,
      errorMessage: successfulCharge.error_message,
      orderNo: successfulCharge.order_no,
      payMethod: successfulCharge.pay_method,
      productDesc: successfulCharge.product_desc,
      status: "success",
      cycleFailure: null,
    } satisfies TossPayChargeExecutionResult;
  }

  const recoveredProfile = await getBillingProfileByOwnerUserId(user.id);

  if (
    !hasUsableBillingProfile(recoveredProfile) ||
    recoveredProfile.pay_method !== "CARD"
  ) {
    throw new Error("billing-card-required");
  }

  const subscription = await getBillingSubscriptionByOwnerUserId(user.id);
  const attempt = previousRows.length + 1;

  return await executeTossPayBillingCharge({
    ownerUserId: user.id,
    ownerEmail: user.email,
    billingProfileId: recoveredProfile.id,
    billingKey: recoveredProfile.billing_key,
    subscriptionId: subscription?.id ?? null,
    chargeKind: "manual",
    amount: input.amount,
    amountTaxFree: 0,
    productDesc: "도메인 연결 설정 대행",
    spreadOut: subscription?.spread_out ?? 0,
    sendFailPush: subscription ? toBoolean(subscription.send_fail_push) : true,
    cashReceipt: subscription ? toBoolean(subscription.cash_receipt) : false,
    cashReceiptTradeOption: subscription?.cash_receipt_trade_option ?? "GENERAL",
    metadataText: serializeMetadata({
      chargeKind: "manual",
      domainSetupAssistanceRequestId: input.requestId,
      reason: "domain-setup-assistance-completed",
    }),
    orderNo: `${orderPrefix}${attempt}`,
  });
}

export async function requestManagedMemberSeatChargeByOwnerEmail(input: {
  displayName?: string | null;
  email: string;
  localPart?: string | null;
}) {
  await ensureBillingSchemaReady();

  const user = await getOwnerUserByEmail(input.email);

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

  const recoveredProfile = await getBillingProfileByOwnerUserId(user.id);

  if (!hasUsableBillingProfile(recoveredProfile)) {
    throw new Error("billing-profile-reregistration-required");
  }

  const subscription = await getBillingSubscriptionByOwnerUserId(user.id);

  if (
    !subscription ||
    subscription.status !== "active" ||
    !subscription.last_charged_at
  ) {
    throw new Error("growth-plan-required");
  }

  return await executeTossPayBillingCharge({
    ownerUserId: user.id,
    ownerEmail: user.email,
    billingProfileId: recoveredProfile.id,
    billingKey: recoveredProfile.billing_key,
    subscriptionId: subscription.id,
    chargeKind: "manual",
    amount: resolveMailBillingSeatChargeAmount(subscription.seat_charge_amount),
    amountTaxFree: 0,
    productDesc: buildManagedMemberChargeProductDescription(),
    spreadOut: subscription.spread_out,
    sendFailPush: toBoolean(subscription.send_fail_push),
    cashReceipt: toBoolean(subscription.cash_receipt),
    cashReceiptTradeOption: subscription.cash_receipt_trade_option,
    metadataText: serializeMetadata({
      chargeKind: "manual",
      displayName: input.displayName?.trim() || null,
      localPart: input.localPart?.trim() || null,
      reason: "managed-member-create",
      seatAmount: resolveMailBillingSeatChargeAmount(subscription.seat_charge_amount),
    }),
  });
}

export async function recordTossPaymentsCheckoutChargeByOwnerEmail(input: {
  email: string;
  amount: number;
  approvedAt: string;
  method: string;
  orderId: string;
  orderName: string;
  paymentKey: string;
  provider?: string | null;
  simulated: boolean;
}): Promise<TossPaymentsCheckoutChargeRecordResult> {
  await ensureBillingSchemaReady();

  const user = await getOwnerUserByEmail(input.email);

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

  const normalizedOrderId = input.orderId.trim();
  const normalizedMethod = input.method.trim() || "토스페이";
  const normalizedOrderName =
    input.orderName.trim().slice(0, 255) || "성장플랜";
  const approvedAtDate = new Date(input.approvedAt);

  if (!normalizedOrderId) {
    throw new Error("charge-order-id-required");
  }

  if (!Number.isFinite(input.amount) || input.amount <= 0) {
    throw new Error("charge-amount-invalid");
  }

  if (Number.isNaN(approvedAtDate.getTime())) {
    throw new Error("charge-approved-at-invalid");
  }

  const result = await withTransaction(async (connection) => {
    let profile = await getBillingProfileByOwnerUserId(user.id, connection);

    if (!profile) {
      profile = await upsertBillingProfileForRegistration(connection, {
        ownerUserId: user.id,
        userToken: createTossPayUserToken(user.id),
        displayId: BILLING_PROFILE_DISPLAY_ID,
        billingKey: null,
        pendingBillingKey: null,
        replacementBillingKey: null,
        status: "inactive",
        requestedAt: approvedAtDate,
        lastErrorCode: null,
        lastErrorMessage: null,
      });
    }

    const subscription = await ensureBillingSubscriptionRow(connection, {
      billingProfileId: profile.id,
      ownerUserId: user.id,
    });

    let existingCharge = await getBillingChargeByOrderNo(normalizedOrderId, connection);
    let alreadyRecorded = Boolean(existingCharge);

    if (!existingCharge) {
      try {
        const chargeId = await insertChargeLog(connection, {
          ownerUserId: user.id,
          subscriptionId: subscription.id,
          billingProfileId: profile.id,
          chargeKind: "manual",
          orderNo: normalizedOrderId,
          amount: input.amount,
          amountTaxFree: 0,
          productDesc: normalizedOrderName,
          status: "success",
          requestedAt: approvedAtDate,
          approvedAt: approvedAtDate,
          payMethod: normalizedMethod,
          cardCompanyName: input.provider?.trim() || null,
          cardNum4Print: null,
          cardMethodType: null,
          accountBankName: null,
          accountNumberMasked: null,
          payToken: null,
          transactionId: input.paymentKey.trim() || null,
          responseCode: 0,
          errorCode: null,
          errorMessage: null,
          rawResponse: JSON.stringify({
            approvedAt: input.approvedAt,
            amount: input.amount,
            method: normalizedMethod,
            orderId: normalizedOrderId,
            orderName: normalizedOrderName,
            paymentKey: input.paymentKey,
            provider: input.provider ?? null,
            simulated: input.simulated,
          }),
        });

        existingCharge = await getBillingChargeById(chargeId, connection);
      } catch (error) {
        const duplicateEntry =
          typeof error === "object" &&
          error !== null &&
          "code" in error &&
          (error as { code?: string }).code === "ER_DUP_ENTRY";

        if (!duplicateEntry) {
          throw error;
        }

        existingCharge = await getBillingChargeByOrderNo(normalizedOrderId, connection);
        alreadyRecorded = true;
      }
    }

    if (!existingCharge) {
      throw new Error("charge-record-failed");
    }

    const nextChargeAt =
      subscription.next_charge_at && subscription.next_charge_at.getTime() > Date.now()
        ? subscription.next_charge_at
        : createNextMonthlyChargeAt(approvedAtDate);

    await connection.query<ResultSetHeader>(
      `
        UPDATE mailbox_toss_pay_subscriptions
        SET
          status = 'active',
          seat_charge_amount = COALESCE(seat_charge_amount, ?),
          next_charge_at = ?,
          retry_after_at = NULL,
          last_charged_at = ?,
          last_charge_status = 'success',
          consecutive_failures = 0,
          processing_started_at = NULL,
          updated_at = NOW()
        WHERE id = ?
      `,
      [MAIL_GROWTH_PLAN_CHARGE_PER_MEMBER, nextChargeAt, approvedAtDate, subscription.id],
    );

    return {
      alreadyRecorded,
      amount: existingCharge.amount,
      approvedAt: normalizeDate(existingCharge.approved_at) ?? approvedAtDate.toISOString(),
      chargeId: existingCharge.id,
      nextChargeAt: nextChargeAt.toISOString(),
      orderNo: existingCharge.order_no,
      status: "success",
      subscriptionStatus: "active",
    } satisfies TossPaymentsCheckoutChargeRecordResult;
  });

  await syncGrowthPlanMailboxQuotas(user.id);
  if (!result.alreadyRecorded) {
    await recordMarketingLifecycleEvent({
      currency: "KRW",
      eventReference: `toss-checkout:${result.orderNo}`,
      eventType: "purchase_completed",
      userId: user.id,
      value: result.amount,
    });
  }
  return result;
}

export async function refundTossPayChargeById(input: {
  amount?: number | null;
  chargeId: number;
  reason?: string | null;
  requestedByEmail: string;
}): Promise<TossPayChargeRefundResult> {
  await ensureBillingSchemaReady();

  const trimmedReason = input.reason?.trim() ?? "";
  const normalizedReason = trimmedReason ? trimmedReason.slice(0, 255) : "최고관리자 환불";

  const prepared = await withTransaction(async (connection) => {
    const charge = await getBillingChargeById(input.chargeId, connection);

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

    if (charge.status !== "success") {
      throw new Error("charge-not-refundable");
    }

    if (!charge.pay_token) {
      throw new Error("charge-pay-token-missing");
    }

    const refundStats = await getRefundStatsByChargeId(input.chargeId, connection);

    if (refundStats.pendingCount > 0) {
      throw new Error("refund-already-processing");
    }

    const remainingRefundableAmount = Math.max(0, charge.amount - refundStats.refundedAmount);

    if (remainingRefundableAmount <= 0) {
      throw new Error("charge-already-refunded");
    }

    const requestedAmount =
      input.amount === null || input.amount === undefined
        ? remainingRefundableAmount
        : Math.floor(Number(input.amount));

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

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

    const refundNo = createTossPayRefundNo();
    const requestedAt = new Date();
    const amountTaxFree = Math.min(charge.amount_tax_free, requestedAmount);
    const refundId = await insertPendingRefundLog(connection, {
      amount: requestedAmount,
      amountTaxFree,
      chargeId: charge.id,
      ownerUserId: charge.owner_user_id,
      payToken: charge.pay_token,
      reason: normalizedReason,
      refundNo,
      requestedAt,
      requestedByEmail: input.requestedByEmail,
      transactionId: charge.transaction_id,
    });

    return {
      amount: requestedAmount,
      amountTaxFree,
      charge,
      remainingRefundableAmount,
      refundId,
      refundNo,
    };
  });

  let refundedAt: Date | null = null;
  let isSuccess = false;
  let errorCode: string | null = null;
  let errorMessage: string | null = null;
  let rawResponse = "";
  let responseCode = 200;

  if (prepared.charge.provider === "toss_payments") {
    try {
      const response = await cancelTossPaymentsPayment({
        amount: prepared.amount,
        cancelReason: normalizedReason,
        idempotencyKey: `refund:${prepared.refundNo}`,
        paymentKey: prepared.charge.pay_token!,
      });
      const latestCancel = response.data.cancels?.at(-1);
      refundedAt = latestCancel?.canceledAt
        ? new Date(latestCancel.canceledAt)
        : new Date();
      if (Number.isNaN(refundedAt.getTime())) {
        refundedAt = new Date();
      }
      isSuccess =
        response.data.status === "CANCELED" ||
        response.data.status === "PARTIAL_CANCELED" ||
        latestCancel?.cancelStatus === "DONE";
      rawResponse = response.rawText;
      responseCode = response.status;

      if (!isSuccess) {
        errorCode = response.data.status || "PAYMENT_CANCEL_NOT_DONE";
        errorMessage = "토스페이먼츠 결제 취소가 완료 상태로 처리되지 않았습니다.";
      }
    } catch (error) {
      responseCode = error instanceof TossPaymentsRequestError ? error.status : 500;
      errorCode =
        error instanceof TossPaymentsRequestError
          ? error.code
          : "TOSS_PAYMENTS_CANCEL_FAILED";
      errorMessage =
        error instanceof Error ? error.message : "토스페이먼츠 결제 취소에 실패했습니다.";
      rawResponse = JSON.stringify({ code: errorCode, message: errorMessage });
    }
  } else {
    const payload = {
      apiKey: getTossPayApiKey(),
      payToken: prepared.charge.pay_token,
      orderNo: prepared.charge.order_no,
      refundNo: prepared.refundNo,
      reason: normalizedReason,
      amount: prepared.amount,
      amountTaxFree: prepared.amountTaxFree,
    };
    const response = await requestJson<TossPayRefundResponse>(TOSS_PAY_REFUND_URL, payload);
    const data = response.json;
    refundedAt = parseTossPayProcessedAt(data?.approvalTime);
    isSuccess = response.ok && data?.code === 0;
    errorCode = isSuccess ? null : data?.errorCode ?? `HTTP_${response.status}`;
    errorMessage =
      isSuccess
        ? null
        : data?.msg ??
          (response.rawText.slice(0, 1000) || "토스페이 환불 요청에 실패했습니다.");
    rawResponse = response.rawText;
    responseCode = data?.code ?? response.status;
  }

  await withTransaction(async (connection) => {
    await updateRefundLogResult(connection, {
      errorCode,
      errorMessage,
      rawResponse,
      refundedAt,
      refundId: prepared.refundId,
      responseCode,
      status: isSuccess ? "success" : "failed",
    });
  });

  if (!isSuccess) {
    throw new Error(errorMessage || "charge-refund-failed");
  }

  return {
    amount: prepared.amount,
    chargeId: prepared.charge.id,
    refundedAt: refundedAt ? refundedAt.toISOString() : null,
    refundId: prepared.refundId,
    refundNo: prepared.refundNo,
    remainingRefundableAmount: Math.max(0, prepared.remainingRefundableAmount - prepared.amount),
    status: "success",
  };
}

export async function processDueTossPaySubscriptionCharges(limit = 20): Promise<TossPayDueChargeRunSummary> {
  await ensureBillingSchemaReady();
  await initializeLegacyFailedSubscriptionGracePeriods();
  await processPendingBillingMemberRestorations(limit).catch((error) => {
    console.error(
      "[toss-pay-billing] pending member restoration failed",
      error instanceof Error ? error.message : error,
    );
  });
  await processPendingBillingMemberSuspensions(limit).catch((error) => {
    console.error(
      "[toss-pay-billing] pending member suspension failed",
      error instanceof Error ? error.message : error,
    );
  });

  const [rows] = await getDbPool().query<DueSubscriptionRow[]>(
    `
      SELECT
        s.id AS subscription_id,
        s.owner_user_id,
        u.email AS owner_email,
        s.billing_profile_id,
        p.billing_key,
        p.provider,
        p.provider_customer_key,
        s.next_charge_at,
        s.retry_after_at,
        s.send_fail_push,
        s.cash_receipt,
        s.cash_receipt_trade_option,
        s.spread_out,
        s.metadata_text,
        s.consecutive_failures,
        s.seat_charge_amount,
        s.billing_grace_started_at,
        s.billing_grace_ends_at,
        s.billing_grace_retry_count
      FROM mailbox_toss_pay_subscriptions s
      INNER JOIN mailbox_toss_pay_billing_profiles p
        ON p.id = s.billing_profile_id
      INNER JOIN users u
        ON u.id = s.owner_user_id
      WHERE s.status = 'active'
        AND s.last_charged_at IS NOT NULL
        AND p.status = 'active'
        AND p.billing_key IS NOT NULL
        AND (
          p.provider = 'legacy_toss_pay'
          OR (
            p.provider = 'toss_payments'
            AND p.provider_customer_key IS NOT NULL
          )
        )
        AND s.next_charge_at IS NOT NULL
        AND s.next_charge_at <= NOW()
        AND (
          s.retry_after_at IS NULL
          OR s.retry_after_at <= NOW()
        )
      ORDER BY s.next_charge_at ASC, s.id ASC
      LIMIT ?
    `,
    [Math.max(1, Math.min(100, limit))],
  );

  const summary: TossPayDueChargeRunSummary = {
    attempted: 0,
    failed: 0,
    skipped: 0,
    success: 0,
  };

  for (const row of rows) {
    const locked = await withTransaction(async (connection) => {
      return await acquireSubscriptionProcessingLock(connection, row.subscription_id);
    });

    if (!locked) {
      continue;
    }

    try {
      if (await hasOperationalBenefitByOwnerUserId(row.owner_user_id)) {
        await skipCycleCharge({
          ownerUserId: row.owner_user_id,
          billingProfileId: row.billing_profile_id,
          provider: row.provider,
          subscriptionId: row.subscription_id,
          subscriptionNextChargeAt: row.next_charge_at,
        });
        summary.skipped += 1;
        continue;
      }

      summary.attempted += 1;
      const billingSeatCount = await getGrowthPlanSeatCountByOwnerUserId(
        row.owner_user_id,
      );
      const chargeSummary = createMailBillingChargeSummary(
        billingSeatCount,
        row.seat_charge_amount,
      );

      if (!chargeSummary.isChargeable) {
        await skipCycleCharge({
          ownerUserId: row.owner_user_id,
          billingProfileId: row.billing_profile_id,
          provider: row.provider,
          subscriptionId: row.subscription_id,
          subscriptionNextChargeAt: row.next_charge_at,
        });
        summary.skipped += 1;
        continue;
      }

      const result = await executeTossPayBillingCharge({
        ownerUserId: row.owner_user_id,
        ownerEmail: row.owner_email,
        billingProfileId: row.billing_profile_id,
        billingKey: row.billing_key,
        subscriptionId: row.subscription_id,
        subscriptionNextChargeAt: row.next_charge_at,
        billingGraceStartedAt: row.billing_grace_started_at,
        billingGraceRetryCount: row.billing_grace_retry_count,
        scheduledRetryAt: row.retry_after_at,
        chargeKind: "cycle",
        amount: chargeSummary.amount,
        amountTaxFree: 0,
        productDesc: buildCycleChargeProductDescription(chargeSummary.billableSeats),
        spreadOut: row.spread_out,
        sendFailPush: toBoolean(row.send_fail_push),
        cashReceipt: toBoolean(row.cash_receipt),
        cashReceiptTradeOption: row.cash_receipt_trade_option,
        metadataText:
          row.metadata_text ??
          serializeMetadata({
            billingSeatCount: chargeSummary.billableSeats,
            chargeKind: "cycle",
          }),
      });

      if (result.status === "success") {
        await restoreManagedMembersAfterBillingRecovery(row.owner_user_id).catch((error) => {
          console.error(
            `[toss-pay-billing] owner=${row.owner_user_id} member restoration failed`,
            error instanceof Error ? error.message : error,
          );
        });
        summary.success += 1;
      } else {
        if (result.cycleFailure?.downgraded) {
          await reconcileExternalMailAccessAfterBillingChange(row.owner_user_id);
        }
        if (result.cycleFailure?.noticeDue) {
          await sendBillingFailureNoticeIfEnabled({
            amount: result.amount,
            billableSeats: chargeSummary.billableSeats,
            errorMessage: result.errorMessage,
            graceEndsAt: new Date(result.cycleFailure.graceEndsAt),
            nextRetryAt: result.cycleFailure.nextRetryAt
              ? new Date(result.cycleFailure.nextRetryAt)
              : null,
            noticeDate: result.cycleFailure.scheduledRetryAt
              ? new Date(result.cycleFailure.scheduledRetryAt)
              : new Date(),
            ownerUserId: row.owner_user_id,
            subscriptionId: row.subscription_id,
          }).catch((error) => {
            console.error(
              `[toss-pay-billing] failure notice owner=${row.owner_user_id} failed`,
              error instanceof Error ? error.message : error,
            );
          });
        }
        summary.failed += 1;
      }
    } finally {
      await withTransaction(async (connection) => {
        await releaseSubscriptionProcessingLock(connection, row.subscription_id);
      }).catch(() => {});
    }
  }

  await processPendingBillingMemberSuspensions(limit).catch((error) => {
    console.error(
      "[toss-pay-billing] member suspension failed",
      error instanceof Error ? error.message : error,
    );
  });
  await processPendingBillingMemberRestorations(limit).catch((error) => {
    console.error(
      "[toss-pay-billing] member restoration failed",
      error instanceof Error ? error.message : error,
    );
  });

  return summary;
}