import "server-only";

import { randomBytes } from "crypto";
import type { PoolConnection, ResultSetHeader, RowDataPacket } from "mysql2/promise";
import { ensureOfficialMailSchema, getDbPool } from "@/lib/db";
import { recordMarketingLifecycleEvent } from "@/lib/marketing-analytics";
import {
  countMailBillingSeatsFromManagedMembers,
  createMailBillingChargeSummary,
  createMailBillingPreview,
  hasComplimentaryGrowthPlanByEmail,
  MAIL_GROWTH_PLAN_PRICE_PER_MEMBER,
} from "@/lib/mail-billing";
import {
  MAIL_BILLING_HISTORY_PAGE_SIZE,
  resolveMailBillingHistoryState,
  type MailBillingHistoryRangePreset,
} from "@/lib/mail-billing-history";
import { getMailAbsoluteUrl, getMailAppUrl, isLocalOrigin } from "@/lib/mail-urls";

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_REMOVE_URL = "https://pay.toss.im/api/v1/billing-key/remove";
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_PRICE_PER_MEMBER;

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";

export type TossPayBillingProfileOverview = {
  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;
};

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;
  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";
  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;
};

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

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 TossPayBillingRemoveResponse = {
  code: number;
  status?: number | 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";
};

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 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 getTossPayApiKeyMode() {
  return getTossPayEnvironment() === "test" ? ("TEST" as const) : ("LIVE" as const);
}

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) {
    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 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'
    `,
    [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,
        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],
  );

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

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 requestTossPayBillingRemoval(billingKey: string) {
  return await requestJson<TossPayBillingRemoveResponse>(TOSS_PAY_BILLING_REMOVE_URL, {
    apiKey: getTossPayApiKey(),
    billingKey,
  });
}

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): TossPayBillingProfileOverview | null {
  if (!row) {
    return null;
  }

  return {
    status: row.status,
    billingKey: 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: row.last_error_code,
    lastErrorMessage: 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,
  };
}

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 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 chargeSummary = createMailBillingChargeSummary(billingSeatCount);
  const manualPreview = createMailBillingPreview(billingSeatCount);

  return {
    apiKeyMode: getTossPayApiKeyMode(),
    apiKeySource: getTossPayApiKeySource(),
    billingSeatCount: chargeSummary.billableSeats,
    estimatedMonthlyCost: chargeSummary.estimatedMonthlyCost,
    manualTestAmount: manualPreview.chargeAmount || DEFAULT_MANUAL_TEST_AMOUNT,
    profile: mapBillingProfileOverview(recoveredProfile),
    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 (hasComplimentaryGrowthPlanByEmail(ownership.ownerEmail)) {
    return true;
  }

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

export async function createTossPayBillingRegistrationForOwnerEmail(input: {
  email: string;
  origin: string;
}): Promise<TossPayBillingRegistrationResult> {
  await ensureBillingSchemaReady();

  const user = await getOwnerUserByEmail(input.email);

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

  const publicBaseUrl = resolveTossPayPublicBaseUrl(input.origin);
  const profile = await recoverBillingProfileFromTossStatus(
    await getBillingProfileByOwnerUserId(user.id),
  );
  const userToken = profile?.user_token || createTossPayUserToken(user.id);
  const displayId = profile?.display_id?.trim() || BILLING_PROFILE_DISPLAY_ID;
  const currentBillingKey = normalizeBillingKey(profile?.billing_key);
  const requestedAt = new Date();
  const resultCallback = getMailAbsoluteUrl("/api/payments/toss-pay/billing/callback", publicBaseUrl);
  const returnSuccessUrl = getMailAbsoluteUrl(
    "/mail?panel=setup&section=billing&success=billing-registration-returned",
    publicBaseUrl,
  );
  const returnFailureUrl = getMailAbsoluteUrl(
    "/mail?panel=setup&section=billing&error=billing-registration-failed",
    publicBaseUrl,
  );

  const payload = {
    apiKey: getTossPayApiKey(),
    userId: userToken,
    displayId,
    productDesc: BILLING_PRODUCT_NAME,
    resultCallback,
    returnSuccessUrl,
    returnFailureUrl,
  };

  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) => {
    const updatedProfile = await upsertBillingProfileForRegistration(connection, {
      ownerUserId: user.id,
      userToken,
      displayId,
      billingKey: currentBillingKey,
      pendingBillingKey: isSuccess ? normalizeBillingKey(data?.billingKey) : null,
      replacementBillingKey: currentBillingKey ?? normalizeBillingKey(profile?.replacement_billing_key),
      status: isSuccess
        ? (profile?.status ?? (currentBillingKey ? "active" : "pending"))
        : currentBillingKey
          ? (profile?.status ?? "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: 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 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");
  }

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

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

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 (!profile || profile.status !== "active" || !profile.billing_key) {
      throw new Error("billing-profile-inactive");
    }

    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) {
    return {
      chargedAmount: 0,
      initialPaymentComplete: true,
      nextChargeAt: normalizeDate(prepared.subscription.next_charge_at),
      status: "active" as const,
    };
  }

  try {
    const billingSeatCount = await getGrowthPlanSeatCountByOwnerUserId(user.id);
    const chargeSummary = createMailBillingChargeSummary(billingSeatCount);
    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',
            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 = ?
        `,
        [nextChargeAt, chargedAt, prepared.subscription.id],
      );
    });

    await recordMarketingLifecycleEvent({
      eventType: "growth_plan_started",
      userId: 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");
  }

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

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

    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,
          updated_at = NOW()
        WHERE id = ?
      `,
      [subscription.id],
    );

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

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

async function executeTossPayBillingCharge(input: {
  ownerUserId: number;
  ownerEmail: string;
  billingProfileId: number;
  billingKey: string;
  subscriptionId: null | number;
  subscriptionNextChargeAt?: Date | null;
  chargeKind: TossPayChargeKind;
  amount: number;
  amountTaxFree: number;
  productDesc: string;
  spreadOut: number;
  sendFailPush: boolean;
  cashReceipt: boolean;
  cashReceiptTradeOption: string;
  metadataText: string | null;
}) {
  const orderNo = 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) => {
    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,
      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
              next_charge_at = ?,
              retry_after_at = NULL,
              last_charged_at = ?,
              last_charge_status = 'success',
              consecutive_failures = 0,
              updated_at = NOW()
            WHERE id = ?
          `,
          [nextChargeAt, approvedAt ?? requestedAt, input.subscriptionId],
        );
      } else {
        await connection.query<ResultSetHeader>(
          `
            UPDATE mailbox_toss_pay_subscriptions
            SET
              retry_after_at = DATE_ADD(NOW(), INTERVAL 1 DAY),
              last_charge_status = 'failed',
              consecutive_failures = consecutive_failures + 1,
              updated_at = NOW()
            WHERE id = ?
          `,
          [input.subscriptionId],
        );
      }
    }

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

async function skipCycleCharge(input: {
  ownerUserId: number;
  billingProfileId: number;
  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,
      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,
          updated_at = NOW()
        WHERE id = ?
      `,
      [nextChargeAt, input.subscriptionId],
    );

    return {
      amount: 0,
      approvedAt: null,
      chargeId,
      errorCode: null,
      errorMessage: "과금 대상 멤버가 없어 이번 회차 청구를 건너뜁니다.",
      orderNo,
      payMethod: null,
      productDesc: "성장플랜 정기결제 건너뜀",
      status: "skipped",
    } 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 (!profile || profile.status !== "active" || !profile.billing_key) {
    throw new Error("billing-profile-inactive");
  }

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

  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 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 recoverBillingProfileFromTossStatus(
    await getBillingProfileByOwnerUserId(user.id),
  );

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

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

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

  return 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',
          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 = ?
      `,
      [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;
  });
}

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

  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;
  const refundedAt = parseTossPayProcessedAt(data?.approvalTime);
  const isSuccess = response.ok && data?.code === 0;
  const errorCode = isSuccess ? null : data?.errorCode ?? `HTTP_${response.status}`;
  const errorMessage =
    isSuccess
      ? null
      : data?.msg ?? (response.rawText.slice(0, 1000) || "토스페이 환불 요청에 실패했습니다.");

  await withTransaction(async (connection) => {
    await updateRefundLogResult(connection, {
      errorCode,
      errorMessage,
      rawResponse: response.rawText,
      refundedAt,
      refundId: prepared.refundId,
      responseCode: data?.code ?? response.status,
      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();

  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,
        s.next_charge_at,
        s.send_fail_push,
        s.cash_receipt,
        s.cash_receipt_trade_option,
        s.spread_out,
        s.metadata_text,
        s.consecutive_failures
      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 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 (hasComplimentaryGrowthPlanByEmail(row.owner_email)) {
        await skipCycleCharge({
          ownerUserId: row.owner_user_id,
          billingProfileId: row.billing_profile_id,
          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);

      if (!chargeSummary.isChargeable) {
        await skipCycleCharge({
          ownerUserId: row.owner_user_id,
          billingProfileId: row.billing_profile_id,
          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,
        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") {
        summary.success += 1;
      } else {
        summary.failed += 1;
      }
    } finally {
      await withTransaction(async (connection) => {
        await releaseSubscriptionProcessingLock(connection, row.subscription_id);
      }).catch(() => {});
    }
  }

  return summary;
}