import "server-only";

import { randomUUID } from "node:crypto";
import type { PoolConnection, ResultSetHeader, RowDataPacket } from "mysql2/promise";
import {
  authActivityPage,
  authActivityPageWindow,
  decodeAuthActivityCursor,
} from "@/lib/auth-activity-pagination";
import type {
  MailBillingCancellationAdminReviewStatus,
  MailBillingCancellationRequestStatus,
} from "@/lib/billing-cancellation";
import { ensureCancelledMemberAccessExpiryByOwnerUserId } from "@/lib/billing-cancellation";
import { ensureOfficialMailSchema, getDbPool } from "@/lib/db";
import { isValidDomainInput } from "@/lib/domain-input";
import { createManagedMemberByOwnerEmail } from "@/lib/mail-automation";
import {
  buildMailboxEmail,
  isValidMailboxLocalPart,
  normalizeMailboxLocalPart,
} from "@/lib/mailbox-address";
import {
  hasComplimentaryGrowthPlanByEmail,
  MAIL_GROWTH_PLAN_PRICE_PER_MEMBER,
} from "@/lib/mail-billing";
import { syncMailcowMailboxQuotasForOwnerUserId } from "@/lib/mail-plan-quota";
import {
  decryptMailboxPassword,
  encryptMailboxPassword,
} from "@/lib/mailbox-remote";
import {
  createMailcowRepairPassword,
  isMailcowPasswordComplexityError,
} from "@/lib/mailbox-password-repair";
import {
  DEFAULT_MAIL_SENDING_SERVER_KEY,
  getMailSendingServerOptions,
  normalizeMailSendingServerInput,
  requireConfiguredMailSendingServerKey,
  type MailSendingServerInput,
  type MailSendingServerOption,
} from "@/lib/mail-sending-servers";
import {
  deleteMailcowDomain,
  ensureMailcowMailbox,
  getMailcowProvisioningInventory,
  renameMailcowMailbox,
} from "@/lib/mailcow";
import {
  cleanupComposeUploadArtifacts,
  type ComposeUploadCleanupTarget,
} from "@/lib/mail-compose-uploads";
import { deleteObjectFromObjectStorage } from "@/lib/object-storage";
import { verifyMailSetupByDomainId } from "@/lib/official-mail";
import { revokePolarSubscriptionsForDeletedAccounts } from "@/lib/polar-billing";
import {
  clearTossPayBillingProfileForOwnerUserId,
  getMailBillingOverviewByOwnerEmail,
} from "@/lib/toss-pay";

const KAVENIX_ADMIN_EMAIL = "admin@officialsite.kr";
const KAVENIX_OPERATIONAL_DOMAIN = "officialsite.kr";
const KAVENIX_OPERATIONAL_EMAIL_SUFFIX = "@officialsite.kr";

export type KavenixAdminSummary = {
  activeMembers: number;
  activeSubscriptions: number;
  expectedMonthlyRevenue: number;
  successfulCharges30d: number;
  totalDomains: number;
  totalUsers: number;
  verifiedDomains: number;
};

export type KavenixAdminUserRow = {
  accountDeletedAt: string | null;
  activeMemberCount: number;
  billingOwnerEmail: string | null;
  billingProfileStatus: "inactive" | "pending" | "active" | "removed" | "failed" | null;
  companyName: string;
  displayName: string;
  domainCount: number;
  email: string;
  estimatedMonthlyRevenue: number;
  id: number;
  isManagedMember: boolean;
  isRepresentativeOwner: boolean;
  lastChargeAt: string | null;
  lastChargeStatus: "requested" | "success" | "failed" | "skipped" | null;
  latestDomain: string | null;
  latestDomainStatus: "pending" | "verified" | "failed" | null;
  latestDomainVerifiedAt: string | null;
  latestMailboxEmail: string | null;
  latestMailboxStatus: "pending_dns" | "active" | null;
  mailConfigured: boolean;
  nextChargeAt: string | null;
  recoveryEmail: string | null;
  subscriptionStatus: "pending" | "active" | "paused" | "cancelled" | null;
};

export type KavenixAdminUserAuthActivity = {
  eventType: "find_id" | "login";
  id: number;
  identityEmail: string;
  ipAddress: string | null;
  occurredAt: string;
  outcome: string;
  source: "app" | "web";
  userAgent: string | null;
};

export type KavenixAdminDomainRow = {
  activeMemberCount: number;
  billingProfileStatus: "inactive" | "pending" | "active" | "removed" | "failed" | null;
  companyName: string;
  dkimEnabled: boolean;
  displayDomain: string;
  displayName: string;
  domainInputValid: boolean;
  dnsReady: boolean;
  dnsRecords: KavenixAdminDnsRecord[];
  domainStatus: "pending" | "verified" | "failed";
  mailcowCleanupAt: string | null;
  expectedMonthlyRevenue: number;
  id: number;
  lastChargeAt: string | null;
  lastChargeStatus: "requested" | "success" | "failed" | "skipped" | null;
  lastDnsCheckedAt: string | null;
  mailboxEmail: string | null;
  mailboxStatus: "pending_dns" | "active" | null;
  nextChargeAt: string | null;
  operationalBenefitEnabled: boolean;
  ownerEmail: string;
  ownerUserId: number;
  provisioningStatus:
    | "ready"
    | "mailcow-domain-missing"
    | "mailcow-mailbox-missing"
    | "database-mailbox-missing"
    | "state-mismatch"
    | "unknown"
    | "cleaned";
  repairEligible: boolean;
  sendingServerKey: string;
  subscriptionStatus: "pending" | "active" | "paused" | "cancelled" | null;
  verifiedAt: string | null;
};

export type KavenixAdminMailboxRow = {
  accountDisplayName: string;
  accountEmail: string;
  contentSyncErrorCount: number;
  createdAt: string;
  displayName: string;
  domain: string;
  domainId: number;
  domainStatus: "pending" | "verified" | "failed";
  email: string;
  id: number;
  inboxTotal: number;
  inboxUnread: number;
  isRepresentative: boolean;
  lastSyncAt: string | null;
  lastSyncError: string | null;
  managedMemberStatus: "active" | "disabled" | null;
  ownerEmail: string;
  status: "pending_dns" | "active" | "disabled";
};

export type KavenixAdminDnsRecord = {
  host: string;
  lastCheckedAt: string | null;
  priority: number | null;
  status: "pending" | "verified" | "failed";
  type: string;
  value: string;
};

export type KavenixAdminMemberDomainOption = {
  companyName: string;
  domain: string;
  id: number;
  ownerEmail: string;
  planStatus: "cancelled" | "free" | "growth";
};

export type KavenixAdminChargeRefundState = "none" | "partial" | "processing" | "refunded";
export type KavenixAdminChargeFilter =
  | "all"
  | "refunded"
  | "withdrawal"
  | "withdrawal_approved"
  | "withdrawal_pending_review";

export type KavenixAdminChargeRow = {
  amount: number;
  amountTaxFree: number;
  canRefund: boolean;
  cancellationApprovedRefundAmount: number | null;
  cancellationRequestedAt: string | null;
  cancellationRequestedByEmail: string | null;
  cancellationRequestId: number | null;
  cancellationRequestStatus: MailBillingCancellationRequestStatus | null;
  cancellationResultText: string | null;
  cancellationReviewNote: string | null;
  cancellationReviewedAt: string | null;
  cancellationReviewedByEmail: string | null;
  cancellationReviewStatus: MailBillingCancellationAdminReviewStatus | null;
  cardCompanyName: string | null;
  cardNum4Print: string | null;
  chargeKind: "manual" | "cycle";
  chargeStatus: "requested" | "success" | "failed" | "skipped";
  companyName: string;
  displayDomain: string | null;
  displayName: string;
  errorCode: string | null;
  errorMessage: string | null;
  id: number;
  latestMailboxEmail: string | null;
  latestRefundErrorMessage: string | null;
  latestRefundReason: string | null;
  latestRefundRequestedAt: string | null;
  latestRefundRequestedByEmail: string | null;
  latestRefundStatus: "pending" | "success" | "failed" | null;
  latestRefundedAt: string | null;
  orderNo: string;
  ownerEmail: string;
  ownerUserId: number;
  payMethod: string | null;
  payTokenAvailable: boolean;
  pendingRefundCount: number;
  productDesc: string;
  provider: "polar" | "toss";
  currency: string;
  paymentTrigger: string | null;
  refundState: KavenixAdminChargeRefundState;
  refundedAmount: number;
  remainingRefundableAmount: number;
  requestedAt: string;
  approvedAt: string | null;
};

export type KavenixAdminDataset = {
  charges: KavenixAdminChargeRow[];
  chargeFilter: KavenixAdminChargeFilter;
  memberDomainOptions: KavenixAdminMemberDomainOption[];
  domainOptions: string[];
  domains: KavenixAdminDomainRow[];
  mailboxes: KavenixAdminMailboxRow[];
  sendingServers: MailSendingServerOption[];
  summary: KavenixAdminSummary;
  users: KavenixAdminUserRow[];
};

export function normalizeKavenixChargeFilter(value: string | null | undefined): KavenixAdminChargeFilter {
  if (
    value === "withdrawal" ||
    value === "withdrawal_pending_review" ||
    value === "withdrawal_approved" ||
    value === "refunded"
  ) {
    return value;
  }

  return "all";
}

type AdminSummaryRow = RowDataPacket & {
  active_members: number;
  active_subscriptions: number;
  billable_growth_seats: number;
  successful_charges_30d: number;
  total_domains: number;
  total_users: number;
  verified_domains: number;
};

type AdminUserQueryRow = RowDataPacket & {
  account_deleted_at: Date | null;
  active_member_count: number | null;
  billing_owner_email: string | null;
  billing_profile_status: "inactive" | "pending" | "active" | "removed" | "failed" | null;
  company_name: string;
  display_name: string;
  domain_count: number | null;
  email: string;
  id: number;
  is_managed_member: number;
  is_representative_owner: number;
  last_charge_at: Date | null;
  last_charge_status: "requested" | "success" | "failed" | "skipped" | null;
  latest_domain: string | null;
  latest_domain_status: "pending" | "verified" | "failed" | null;
  latest_domain_last_checked_at: Date | null;
  latest_domain_verified_at: Date | null;
  latest_mailbox_email: string | null;
  latest_mailbox_status: "pending_dns" | "active" | null;
  mail_configured: number;
  next_charge_at: Date | null;
  operational_benefit_enabled: number;
  recovery_email: string | null;
  subscription_status: "pending" | "active" | "paused" | "cancelled" | null;
};

type AdminUserAuthActivityQueryRow = RowDataPacket & {
  cursor_time: string;
  event_type: "find_id" | "login";
  id: number;
  identity_email: string;
  ip_address: string | null;
  occurred_at: Date;
  outcome: string;
  source: "app" | "web";
  user_agent: string | null;
};

type AdminDomainQueryRow = RowDataPacket & {
  active_member_count: number | null;
  billing_profile_status: "inactive" | "pending" | "active" | "removed" | "failed" | null;
  company_name: string;
  dkim_enabled: number;
  display_name: string;
  domain: string;
  domain_status: "pending" | "verified" | "failed";
  id: number;
  last_charge_at: Date | null;
  last_charge_status: "requested" | "success" | "failed" | "skipped" | null;
  mailcow_cleanup_at: Date | null;
  mailbox_email: string | null;
  mailbox_status: "pending_dns" | "active" | null;
  next_charge_at: Date | null;
  operational_benefit_enabled: number;
  owner_email: string;
  owner_user_id: number;
  sending_server_key: string | null;
  subscription_status: "pending" | "active" | "paused" | "cancelled" | null;
  verified_at: Date | null;
};

type AdminDomainDnsRecordRow = RowDataPacket & {
  domain_id: number;
  host_name: string;
  last_checked_at: Date | null;
  priority: number | null;
  record_type: string;
  status: "pending" | "verified" | "failed";
  value_text: string;
};

type KavenixDomainProvisioningRepairRow = RowDataPacket & {
  display_name: string;
  domain: string;
  domain_id: number;
  local_part: string | null;
  mailbox_email: string | null;
  mailbox_id: number | null;
  mailcow_cleanup_at: Date | null;
  password_ciphertext: string | null;
  user_id: number;
};

type AdminMailboxQueryRow = RowDataPacket & {
  account_display_name: string;
  account_email: string;
  content_sync_error_count: number;
  created_at: Date;
  domain: string;
  domain_id: number;
  domain_status: "pending" | "verified" | "failed";
  email: string;
  id: number;
  inbox_total: number | null;
  inbox_unseen: number | null;
  is_representative: number;
  last_sync_at: Date | null;
  last_sync_error: string | null;
  mailbox_status: "pending_dns" | "active" | "disabled";
  managed_member_display_name: string | null;
  managed_member_status: "active" | "disabled" | null;
  owner_email: string;
};

type AdminChargeQueryRow = RowDataPacket & {
  amount: number;
  amount_tax_free: number;
  approved_at: Date | null;
  cancellation_approved_refund_amount: number | null;
  cancellation_request_id: number | null;
  cancellation_request_status: MailBillingCancellationRequestStatus | null;
  cancellation_result_text: string | null;
  cancellation_review_note: string | null;
  cancellation_review_status: MailBillingCancellationAdminReviewStatus | null;
  cancellation_reviewed_at: Date | null;
  cancellation_reviewed_by_email: string | null;
  cancellation_requested_at: Date | null;
  cancellation_requested_by_email: string | null;
  card_company_name: string | null;
  card_num4_print: string | null;
  charge_kind: "manual" | "cycle";
  charge_status: "requested" | "success" | "failed" | "skipped";
  company_name: string;
  display_name: string;
  display_domain: string | null;
  error_code: string | null;
  error_message: string | null;
  id: number;
  latest_mailbox_email: string | null;
  latest_refund_error_message: string | null;
  latest_refund_reason: string | null;
  latest_refund_requested_at: Date | null;
  latest_refund_requested_by_email: string | null;
  latest_refund_status: "pending" | "success" | "failed" | null;
  latest_refunded_at: Date | null;
  order_no: string;
  owner_email: string;
  owner_user_id: number;
  pay_method: string | null;
  pay_token: string | null;
  pending_refund_count: number | null;
  product_desc: string;
  refunded_amount: number | null;
  requested_at: Date;
};

type AdminPolarPaymentQueryRow = RowDataPacket & {
  amount: number;
  card_brand: string | null;
  card_last_four: string | null;
  company_name: string;
  currency: string;
  decline_message: string | null;
  decline_reason: string | null;
  display_domain: string | null;
  display_name: string;
  id: number;
  latest_mailbox_email: string | null;
  owner_email: string;
  owner_user_id: number;
  payment_created_at: Date;
  payment_method: string;
  payment_trigger: string | null;
  polar_checkout_id: string | null;
  polar_order_id: string | null;
  polar_payment_id: string;
  status: string;
};

function normalizeFilterValue(value: string | null | undefined) {
  return value?.trim() ?? "";
}

function isKavenixOperationalEmail(email: string) {
  return email.trim().toLowerCase().endsWith(KAVENIX_OPERATIONAL_EMAIL_SUFFIX);
}

function isKavenixOperationalDomain(domain: string) {
  return domain.trim().toLowerCase() === KAVENIX_OPERATIONAL_DOMAIN;
}

function externalOwnerSql(alias: string) {
  return `LOWER(${alias}.email) NOT LIKE '%@officialsite.kr' AND ${alias}.account_deleted_at IS NULL`;
}

function adminUserSql(alias: string) {
  return `LOWER(${alias}.email) NOT LIKE '%@officialsite.kr'`;
}

function externalDomainSql(alias: string) {
  return `LOWER(${alias}.domain) <> 'officialsite.kr'`;
}

type KavenixDomainCleanupRow = RowDataPacket & {
  domain: string;
  id: number;
  owner_email: string;
  owner_user_id: number;
};

type KavenixOwnedDomainCleanupRow = RowDataPacket & {
  domain: string;
  id: number;
};

type KavenixCleanupMemberUserRow = RowDataPacket & {
  id: number;
};

type KavenixCleanupStorageKeyRow = RowDataPacket & {
  storage_key: string;
};

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

type KavenixPrimaryMailboxRow = RowDataPacket & {
  domain: string;
  domain_id: number;
  mailcow_cleanup_at: Date | null;
  mailbox_email: string;
  mailbox_id: number;
  mailbox_local_part: string;
  owner_email: string;
  owner_user_id: number;
};

type KavenixPrimaryEmailChangeRetryRow = RowDataPacket & {
  error_message: string | null;
  id: number;
  mailcow_completed_at: Date | null;
};

type KavenixDomainOperationalBenefitRow = RowDataPacket & {
  domain: string;
  id: number;
  mailcow_cleanup_at: Date | null;
  operational_benefit_enabled: number;
  owner_email: string;
  owner_user_id: number;
};

type KavenixMemberDomainRow = RowDataPacket & {
  company_name: string;
  domain: string;
  id: number;
  last_charged_at: Date | null;
  mailcow_cleanup_at: Date | null;
  operational_benefit_enabled: number;
  owner_email: string;
  owner_user_id: number;
  subscription_status: "pending" | "active" | "paused" | "cancelled" | null;
};

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

export function getKavenixAdminAllowlist() {
  return [KAVENIX_ADMIN_EMAIL];
}

export function isKavenixAdminEmail(email: string) {
  return email.trim().toLowerCase() === KAVENIX_ADMIN_EMAIL;
}

async function runKavenixCleanupTransaction<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 deleteKavenixCleanupStorageObjects(storageKeys: string[]) {
  const uniqueKeys = [...new Set(storageKeys.map((key) => key.trim()).filter(Boolean))];

  for (let offset = 0; offset < uniqueKeys.length; offset += 10) {
    await Promise.all(
      uniqueKeys
        .slice(offset, offset + 10)
        .map((key) => deleteObjectFromObjectStorage({ key })),
    );
  }

  return uniqueKeys.length;
}

export async function cleanupKavenixDomainById(
  domainId: number,
  confirmationDomain: string,
) {
  await ensureOfficialMailSchema();

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

  const [rows] = await getDbPool().query<KavenixDomainCleanupRow[]>(
    `
      SELECT
        d.id,
        d.domain,
        d.user_id AS owner_user_id,
        u.email AS owner_email
      FROM domains d
      INNER JOIN users u ON u.id = d.user_id
      WHERE d.id = ?
        AND u.account_deleted_at IS NULL
      LIMIT 1
    `,
    [domainId],
  );
  const domain = rows[0];

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

  if (isKavenixOperationalDomain(domain.domain)) {
    throw new Error("kavenix-operational-domain-hidden");
  }

  if (confirmationDomain.trim().toLowerCase() !== domain.domain.trim().toLowerCase()) {
    throw new Error("kavenix-domain-cleanup-confirmation-mismatch");
  }

  const [ownedDomains] = await getDbPool().query<KavenixOwnedDomainCleanupRow[]>(
    `
      SELECT id, domain
      FROM domains
      WHERE user_id = ?
      ORDER BY id ASC
    `,
    [domain.owner_user_id],
  );

  if (ownedDomains.some((row) => isKavenixOperationalDomain(row.domain))) {
    throw new Error("kavenix-operational-domain-hidden");
  }

  const [memberUsers] = await getDbPool().query<KavenixCleanupMemberUserRow[]>(
    `
      SELECT DISTINCT member_user.id
      FROM managed_team_mailboxes member
      INNER JOIN users member_user
        ON LOWER(member_user.email) = LOWER(member.email)
      WHERE member.owner_user_id = ?
        AND member_user.account_deleted_at IS NULL
        AND NOT EXISTS (
          SELECT 1
          FROM domains member_owned_domain
          WHERE member_owned_domain.user_id = member_user.id
        )
        AND NOT EXISTS (
          SELECT 1
          FROM managed_team_mailboxes other_team
          WHERE LOWER(other_team.email) = LOWER(member_user.email)
            AND other_team.owner_user_id <> ?
        )
    `,
    [domain.owner_user_id, domain.owner_user_id],
  );
  const affectedUserIds = [
    domain.owner_user_id,
    ...memberUsers.map((row) => Number(row.id)),
  ].filter((id, index, values) => Number.isSafeInteger(id) && values.indexOf(id) === index);
  const affectedUserPlaceholders = affectedUserIds.map(() => "?").join(", ");
  const ownedDomainIds = ownedDomains.map((row) => Number(row.id));
  const ownedDomainPlaceholders = ownedDomainIds.map(() => "?").join(", ");

  const [composeUploads] = await getDbPool().query<KavenixCleanupComposeUploadRow[]>(
    `
      SELECT id, temp_path, storage_key, public_url
      FROM mailbox_uploaded_assets
      WHERE owner_user_id IN (${affectedUserPlaceholders})
      ORDER BY id ASC
    `,
    affectedUserIds,
  );
  const cleanupTargets = composeUploads.map((row) => ({
    id: Number(row.id),
    publicUrl: row.public_url,
    storageKey: row.storage_key,
    tempPath: row.temp_path,
  } satisfies ComposeUploadCleanupTarget));

  const storageKeyQueries = await Promise.all([
    getDbPool().query<KavenixCleanupStorageKeyRow[]>(
      `SELECT storage_key FROM mailbox_storage_objects WHERE owner_user_id_snapshot IN (${affectedUserPlaceholders})`,
      affectedUserIds,
    ),
    getDbPool().query<KavenixCleanupStorageKeyRow[]>(
      `SELECT storage_key FROM mailbox_file_assets WHERE owner_user_id IN (${affectedUserPlaceholders}) AND storage_key <> ''`,
      affectedUserIds,
    ),
    getDbPool().query<KavenixCleanupStorageKeyRow[]>(
      `
        SELECT attachment.storage_key
        FROM mailbox_message_attachments attachment
        INNER JOIN mailbox_messages message ON message.id = attachment.mailbox_message_id
        INNER JOIN mailboxes mailbox ON mailbox.id = message.mailbox_id
        WHERE attachment.storage_key IS NOT NULL
          AND attachment.storage_key <> ''
          AND (
            mailbox.user_id IN (${affectedUserPlaceholders})
            ${ownedDomainIds.length > 0 ? `OR mailbox.domain_id IN (${ownedDomainPlaceholders})` : ""}
          )
      `,
      [...affectedUserIds, ...ownedDomainIds],
    ),
    getDbPool().query<KavenixCleanupStorageKeyRow[]>(
      `SELECT storage_key FROM mailbox_uploaded_assets WHERE owner_user_id IN (${affectedUserPlaceholders}) AND storage_key IS NOT NULL AND storage_key <> ''`,
      affectedUserIds,
    ),
    getDbPool().query<KavenixCleanupStorageKeyRow[]>(
      `SELECT mail_signature_image_key AS storage_key FROM users WHERE id IN (${affectedUserPlaceholders}) AND mail_signature_image_key IS NOT NULL AND mail_signature_image_key <> ''`,
      affectedUserIds,
    ),
  ]);
  const storageKeys = storageKeyQueries.flatMap(([rows]) =>
    rows.map((row) => row.storage_key),
  );

  await revokePolarSubscriptionsForDeletedAccounts(affectedUserIds);

  let deletedMailboxCount = 0;
  for (const ownedDomain of ownedDomains) {
    const cleanup = await deleteMailcowDomain(ownedDomain.domain);
    deletedMailboxCount += cleanup.deletedMailboxCount;
  }

  await cleanupComposeUploadArtifacts(getDbPool(), cleanupTargets);
  const deletedStorageObjectCount = await deleteKavenixCleanupStorageObjects(storageKeys);

  await runKavenixCleanupTransaction(async (connection) => {
    const [lockedRows] = await connection.query<(RowDataPacket & { id: number })[]>(
      `
        SELECT id
        FROM users
        WHERE id = ?
          AND account_deleted_at IS NULL
        FOR UPDATE
      `,
      [domain.owner_user_id],
    );

    if (!lockedRows[0]) {
      throw new Error("kavenix-domain-not-found");
    }

    for (const affectedUserId of affectedUserIds) {
      await clearTossPayBillingProfileForOwnerUserId(affectedUserId, connection);
    }
    await connection.query(
      `
        UPDATE mailbox_toss_pay_billing_profiles
        SET
          user_token = CONCAT('deleted-', owner_user_id),
          display_id = CONCAT('deleted-', owner_user_id),
          provider_customer_key = CONCAT('deleted-', owner_user_id),
          updated_at = NOW()
        WHERE owner_user_id IN (${affectedUserPlaceholders})
      `,
      affectedUserIds,
    );
    await connection.query(
      `
        UPDATE mailbox_toss_pay_subscriptions
        SET
          status = 'cancelled',
          next_charge_at = NULL,
          retry_after_at = NULL,
          processing_started_at = NULL,
          send_fail_push = 0,
          metadata_text = NULL,
          updated_at = NOW()
        WHERE owner_user_id IN (${affectedUserPlaceholders})
      `,
      affectedUserIds,
    );
    await connection.query(
      `
        UPDATE mailbox_toss_pay_billing_charges
        SET
          card_company_name = NULL,
          card_num4_print = NULL,
          card_method_type = NULL,
          account_bank_name = NULL,
          account_number_masked = NULL,
          pay_token = NULL,
          error_message = NULL,
          raw_response = NULL,
          updated_at = NOW()
        WHERE owner_user_id IN (${affectedUserPlaceholders})
      `,
      affectedUserIds,
    );
    await connection.query(
      `
        UPDATE mailbox_toss_pay_billing_refunds
        SET
          pay_token = CONCAT('deleted-', id),
          requested_by_email = CONCAT('deleted-', owner_user_id, '@deleted.invalid'),
          error_message = NULL,
          raw_response = NULL,
          updated_at = NOW()
        WHERE owner_user_id IN (${affectedUserPlaceholders})
      `,
      affectedUserIds,
    );
    await connection.query(
      `
        UPDATE mailbox_toss_pay_billing_cancellation_requests
        SET
          requested_by_email = CONCAT('deleted-', owner_user_id, '@deleted.invalid'),
          review_note = NULL,
          result_text = NULL,
          error_message = NULL,
          updated_at = NOW()
        WHERE owner_user_id IN (${affectedUserPlaceholders})
      `,
      affectedUserIds,
    );
    await connection.query(
      `
        UPDATE mailbox_polar_payments
        SET card_brand = NULL, card_last_four = NULL, decline_message = NULL, updated_at = NOW()
        WHERE owner_user_id IN (${affectedUserPlaceholders})
      `,
      affectedUserIds,
    );
    await connection.query(
      `DELETE FROM mailbox_toss_pay_billing_registrations WHERE owner_user_id IN (${affectedUserPlaceholders})`,
      affectedUserIds,
    );

    if (ownedDomainIds.length > 0) {
      await connection.query(
        `DELETE FROM domain_verification_results WHERE domain_id IN (${ownedDomainPlaceholders})`,
        ownedDomainIds,
      );
      await connection.query(
        `DELETE FROM marketing_funnel_events WHERE domain_id IN (${ownedDomainPlaceholders}) OR user_id IN (${affectedUserPlaceholders})`,
        [...ownedDomainIds, ...affectedUserIds],
      );
    } else {
      await connection.query(
        `DELETE FROM marketing_funnel_events WHERE user_id IN (${affectedUserPlaceholders})`,
        affectedUserIds,
      );
    }

    const directUserTables = [
      ["domain_setup_assistance_requests", "user_id"],
      ["domain_dkim_reminder_deliveries", "user_id"],
      ["domain_setup_promotion_redemptions", "user_id"],
      ["mailbox_ai_assist_settings", "owner_user_id"],
      ["mailbox_ai_assist_threads", "owner_user_id"],
      ["mailbox_app_sessions", "user_id"],
      ["mailbox_auto_send_settings", "owner_user_id"],
      ["mailbox_developer_api_keys", "owner_user_id"],
      ["mailbox_file_hidden_attachments", "owner_user_id"],
      ["mailbox_primary_email_changes", "owner_user_id"],
      ["mailbox_provisioning_recovery_jobs", "owner_user_id"],
      ["mailbox_push_devices", "user_id"],
      ["mailbox_uploaded_assets", "owner_user_id"],
      ["mailbox_withdrawal_cleanup_jobs", "owner_user_id"],
      ["password_reset_tokens", "user_id"],
      ["user_auth_activity_events", "user_id"],
      ["user_legal_consents", "user_id"],
      ["user_remember_devices", "user_id"],
    ] as const;

    for (const [tableName, columnName] of directUserTables) {
      await connection.query(
        `DELETE FROM ${tableName} WHERE ${columnName} IN (${affectedUserPlaceholders})`,
        affectedUserIds,
      );
    }

    await connection.query(
      `DELETE FROM domains WHERE user_id = ?`,
      [domain.owner_user_id],
    );
    await connection.query(
      `DELETE FROM managed_team_mailboxes WHERE owner_user_id = ?`,
      [domain.owner_user_id],
    );
    await connection.query(
      `DELETE FROM mailboxes WHERE user_id IN (${affectedUserPlaceholders})`,
      affectedUserIds,
    );
    await connection.query(
      `DELETE FROM mailbox_storage_delete_jobs WHERE owner_user_id IN (${affectedUserPlaceholders})`,
      affectedUserIds,
    );
    await connection.query(
      `DELETE FROM mailbox_storage_objects WHERE owner_user_id_snapshot IN (${affectedUserPlaceholders})`,
      affectedUserIds,
    );
    await connection.query(
      `
        UPDATE users
        SET
          email = CONCAT('deleted-', id, '-', REPLACE(UUID(), '-', ''), '@deleted.invalid'),
          company_name = '삭제됨',
          display_name = '삭제됨',
          recovery_email = NULL,
          recovery_email_verified_at = NULL,
          password_hash = CONCAT('deleted:', UUID()),
          mail_configured = 0,
          preferred_locale = NULL,
          mail_signature_enabled = 0,
          mail_signature_html = NULL,
          mail_signature_text = NULL,
          mail_signature_image_url = NULL,
          mail_signature_image_key = NULL,
          account_deleted_at = NOW(),
          updated_at = NOW()
        WHERE id IN (${affectedUserPlaceholders})
      `,
      affectedUserIds,
    );
  });

  return {
    domain: domain.domain,
    deletedDomainCount: ownedDomains.length,
    deletedMailboxCount,
    deletedStorageObjectCount,
    deletedUserCount: affectedUserIds.length,
  };
}

export async function updateKavenixDomainPrimaryEmailById(input: {
  domainId: number;
  localPart: string;
  requestedByEmail: string;
}) {
  await ensureOfficialMailSchema();

  if (!Number.isSafeInteger(input.domainId) || input.domainId <= 0) {
    throw new Error("kavenix-domain-not-found");
  }

  const newLocalPart = normalizeMailboxLocalPart(input.localPart);
  const requestedByEmail = input.requestedByEmail.trim().toLowerCase();

  if (!isValidMailboxLocalPart(newLocalPart)) {
    throw new Error("kavenix-primary-email-local-part-invalid");
  }

  const pool = getDbPool();
  const connection = await pool.getConnection();
  const lockName = `official-mail-primary-email-change-${input.domainId}`;
  let lockAcquired = false;
  let mailcowRenameCompleted = false;
  let transactionStarted = false;
  let operationId = 0;

  try {
    const [lockRows] = await connection.query<(RowDataPacket & { acquired: number })[]>(
      "SELECT GET_LOCK(?, 15) AS acquired",
      [lockName],
    );
    lockAcquired = Number(lockRows[0]?.acquired ?? 0) === 1;

    if (!lockAcquired) {
      throw new Error("kavenix-primary-email-change-busy");
    }

    const loadPrimaryMailbox = async (forUpdate = false) => {
      const [rows] = await connection.query<KavenixPrimaryMailboxRow[]>(
        `
          SELECT
            d.id AS domain_id,
            d.domain,
            d.mailcow_cleanup_at,
            u.id AS owner_user_id,
            u.email AS owner_email,
            m.id AS mailbox_id,
            m.email AS mailbox_email,
            m.local_part AS mailbox_local_part
          FROM domains d
          INNER JOIN users u ON u.id = d.user_id
          INNER JOIN mailboxes m
            ON m.domain_id = d.id
           AND m.user_id = d.user_id
          WHERE d.id = ?
          ORDER BY m.id ASC
          LIMIT 1
          ${forUpdate ? "FOR UPDATE" : ""}
        `,
        [input.domainId],
      );

      return rows[0] ?? null;
    };

    const primaryMailbox = await loadPrimaryMailbox();

    if (!primaryMailbox) {
      throw new Error("kavenix-primary-email-mailbox-not-found");
    }

    if (isKavenixOperationalDomain(primaryMailbox.domain)) {
      throw new Error("kavenix-operational-domain-hidden");
    }

    if (primaryMailbox.mailcow_cleanup_at) {
      throw new Error("kavenix-domain-cleaned");
    }

    const oldEmail = primaryMailbox.mailbox_email.trim().toLowerCase();
    const oldLocalPart = primaryMailbox.mailbox_local_part.trim().toLowerCase();
    const ownerEmail = primaryMailbox.owner_email.trim().toLowerCase();
    const newEmail = buildMailboxEmail(newLocalPart, primaryMailbox.domain);

    if (ownerEmail !== oldEmail || oldEmail !== buildMailboxEmail(oldLocalPart, primaryMailbox.domain)) {
      throw new Error("kavenix-primary-email-state-mismatch");
    }

    if (newEmail === oldEmail) {
      throw new Error("kavenix-primary-email-unchanged");
    }

    if (newEmail.length > 320) {
      throw new Error("kavenix-primary-email-local-part-invalid");
    }

    const [conflictRows] = await connection.query<(RowDataPacket & { source: string })[]>(
      `
        SELECT source
        FROM (
          SELECT 'user' AS source
          FROM users
          WHERE email = ? AND id <> ?
          UNION ALL
          SELECT 'mailbox' AS source
          FROM mailboxes
          WHERE email = ? AND id <> ?
          UNION ALL
          SELECT 'member' AS source
          FROM managed_team_mailboxes
          WHERE email = ?
        ) conflicts
        LIMIT 1
      `,
      [
        newEmail,
        primaryMailbox.owner_user_id,
        newEmail,
        primaryMailbox.mailbox_id,
        newEmail,
      ],
    );

    if (conflictRows[0]) {
      throw new Error("kavenix-primary-email-exists");
    }

    const [retryRows] = await connection.query<KavenixPrimaryEmailChangeRetryRow[]>(
      `
        SELECT id, mailcow_completed_at, error_message
        FROM mailbox_primary_email_changes
        WHERE domain_id = ?
          AND old_email = ?
          AND new_email = ?
          AND status <> 'completed'
        ORDER BY id DESC
        LIMIT 1
      `,
      [primaryMailbox.domain_id, oldEmail, newEmail],
    );
    const retryOperation = retryRows[0];
    const acceptAlreadyRenamed = Boolean(
      retryOperation?.mailcow_completed_at ||
        retryOperation?.error_message?.startsWith("mailcow-mailbox-rename-state-unknown:"),
    );

    const [operationResult] = await connection.query(
      `
        INSERT INTO mailbox_primary_email_changes (
          domain_id,
          owner_user_id,
          mailbox_id,
          old_email,
          new_email,
          old_local_part,
          new_local_part,
          requested_by_email,
          status
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'pending')
      `,
      [
        primaryMailbox.domain_id,
        primaryMailbox.owner_user_id,
        primaryMailbox.mailbox_id,
        oldEmail,
        newEmail,
        oldLocalPart,
        newLocalPart,
        requestedByEmail,
      ],
    );
    operationId = Number((operationResult as { insertId?: number }).insertId ?? 0);

    await connection.beginTransaction();
    transactionStarted = true;

    const lockedMailbox = await loadPrimaryMailbox(true);

    if (
      !lockedMailbox ||
      lockedMailbox.owner_user_id !== primaryMailbox.owner_user_id ||
      lockedMailbox.mailbox_id !== primaryMailbox.mailbox_id ||
      lockedMailbox.owner_email.trim().toLowerCase() !== oldEmail ||
      lockedMailbox.mailbox_email.trim().toLowerCase() !== oldEmail
    ) {
      throw new Error("kavenix-primary-email-state-changed");
    }

    const [userUpdate] = await connection.query(
      "UPDATE users SET email = ?, updated_at = NOW() WHERE id = ? AND email = ?",
      [newEmail, primaryMailbox.owner_user_id, oldEmail],
    );
    const [mailboxUpdate] = await connection.query(
      `
        UPDATE mailboxes
        SET local_part = ?, email = ?, last_sync_error = NULL, updated_at = NOW()
        WHERE id = ? AND email = ?
      `,
      [newLocalPart, newEmail, primaryMailbox.mailbox_id, oldEmail],
    );

    if (
      Number((userUpdate as { affectedRows?: number }).affectedRows ?? 0) !== 1 ||
      Number((mailboxUpdate as { affectedRows?: number }).affectedRows ?? 0) !== 1
    ) {
      throw new Error("kavenix-primary-email-state-changed");
    }

    await connection.query(
      `
        UPDATE mailbox_push_devices
        SET mailbox_email = ?, updated_at = NOW()
        WHERE user_id = ? AND mailbox_email = ?
      `,
      [newEmail, primaryMailbox.owner_user_id, oldEmail],
    );

    const mailcowResult = await renameMailcowMailbox({
      acceptAlreadyRenamed,
      domain: primaryMailbox.domain,
      newLocalPart,
      oldLocalPart,
    });
    mailcowRenameCompleted = true;

    await pool.query(
      `
        UPDATE mailbox_primary_email_changes
        SET mailcow_completed_at = COALESCE(mailcow_completed_at, NOW()), updated_at = NOW()
        WHERE id = ?
      `,
      [operationId],
    );

    await connection.query(
      `
        UPDATE mailbox_primary_email_changes
        SET
          status = 'completed',
          mailcow_completed_at = COALESCE(mailcow_completed_at, NOW()),
          completed_at = NOW(),
          error_message = NULL,
          updated_at = NOW()
        WHERE id = ?
      `,
      [operationId],
    );

    await connection.commit();
    transactionStarted = false;

    return {
      alreadyRenamedInMailcow: mailcowResult.alreadyRenamed,
      domain: primaryMailbox.domain,
      newEmail,
      oldEmail,
    };
  } catch (error) {
    if (transactionStarted) {
      await connection.rollback().catch(() => undefined);
      transactionStarted = false;
    }

    if (operationId > 0) {
      const rawErrorMessage = error instanceof Error ? error.message : "unknown-error";
      const errorMessage = (
        mailcowRenameCompleted &&
        !rawErrorMessage.startsWith("mailcow-mailbox-rename-state-unknown:")
          ? `mailcow-mailbox-rename-state-unknown:${rawErrorMessage}`
          : rawErrorMessage
      ).slice(0, 1000);
      await pool.query(
        `
          UPDATE mailbox_primary_email_changes
          SET status = 'failed', error_message = ?, updated_at = NOW()
          WHERE id = ? AND status = 'pending'
        `,
        [errorMessage, operationId],
      ).catch(() => undefined);
    }

    throw error;
  } finally {
    if (lockAcquired) {
      await connection.query("SELECT RELEASE_LOCK(?)", [lockName]).catch(() => undefined);
    }
    connection.release();
  }
}

async function getKavenixDomainDnsValueSnapshot(domainId: number) {
  const [rows] = await getDbPool().query<
    (RowDataPacket & {
      host_name: string;
      priority: number | null;
      record_type: string;
      value_text: string;
    })[]
  >(
    `
      SELECT record_type, host_name, value_text, priority
      FROM dns_records
      WHERE domain_id = ?
      ORDER BY record_type ASC, host_name ASC
    `,
    [domainId],
  );

  return JSON.stringify(
    rows.map((row) => [
      row.record_type,
      row.host_name,
      row.value_text,
      row.priority,
    ]),
  );
}

export async function repairKavenixDomainProvisioningById(domainId: number) {
  await ensureOfficialMailSchema();

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

  const [rows] = await getDbPool().query<KavenixDomainProvisioningRepairRow[]>(
    `
      SELECT
        d.id AS domain_id,
        d.domain,
        d.user_id,
        d.mailcow_cleanup_at,
        u.display_name,
        m.id AS mailbox_id,
        m.email AS mailbox_email,
        m.local_part,
        m.password_ciphertext
      FROM domains d
      INNER JOIN users u ON u.id = d.user_id
      LEFT JOIN mailboxes m
        ON m.domain_id = d.id
       AND m.user_id = d.user_id
      WHERE d.id = ?
      LIMIT 1
    `,
    [domainId],
  );
  const target = rows[0];

  if (!target || isKavenixOperationalDomain(target.domain)) {
    throw new Error("kavenix-domain-not-found");
  }

  if (target.mailcow_cleanup_at) {
    throw new Error("kavenix-domain-cleaned");
  }

  if (!isValidDomainInput(target.domain)) {
    throw new Error("kavenix-domain-input-invalid");
  }

  if (
    !target.mailbox_id ||
    !target.mailbox_email ||
    !target.local_part ||
    !target.password_ciphertext
  ) {
    throw new Error("kavenix-mailbox-data-missing");
  }

  const dnsValuesBefore = await getKavenixDomainDnsValueSnapshot(target.domain_id);
  const inventory = await getMailcowProvisioningInventory();
  const normalizedDomain = target.domain.trim().toLowerCase();
  const normalizedMailbox = target.mailbox_email.trim().toLowerCase();
  const domainPresent = inventory.domainNames.includes(normalizedDomain);
  const mailboxPresent = inventory.mailboxEmails.includes(normalizedMailbox);
  let mailboxPasswordRotated = false;

  if (!domainPresent) {
    throw new Error("kavenix-mailcow-domain-missing");
  }

  if (!mailboxPresent) {
    const savedMailboxPassword = decryptMailboxPassword(target.password_ciphertext);

    try {
      await ensureMailcowMailbox({
        displayName: target.display_name,
        domain: target.domain,
        localPart: target.local_part,
        mailboxPassword: savedMailboxPassword,
      });
    } catch (error) {
      if (!isMailcowPasswordComplexityError(error)) {
        throw error;
      }

      const repairPassword = createMailcowRepairPassword();

      await ensureMailcowMailbox({
        displayName: target.display_name,
        domain: target.domain,
        localPart: target.local_part,
        mailboxPassword: repairPassword,
      });
      await getDbPool().query(
        `
          UPDATE mailboxes
          SET
            password_ciphertext = ?,
            password_updated_at = NOW(),
            last_sync_error = NULL,
            updated_at = NOW()
          WHERE id = ?
        `,
        [encryptMailboxPassword(repairPassword), target.mailbox_id],
      );
      mailboxPasswordRotated = true;
    }
  }

  const finalVerification = await verifyMailSetupByDomainId(target.domain_id);
  const dnsValuesAfter = await getKavenixDomainDnsValueSnapshot(target.domain_id);

  if (dnsValuesBefore !== dnsValuesAfter) {
    throw new Error("kavenix-domain-dns-values-changed");
  }

  if (!finalVerification.mailcowProvisioned) {
    throw new Error("kavenix-domain-repair-incomplete");
  }

  return {
    dnsVerified: finalVerification.dnsVerified,
    dnsValuesUnchanged: true,
    domain: target.domain,
    mailboxCreated: !mailboxPresent,
    mailboxPasswordRotated,
    verified: finalVerification.verified,
  };
}

async function getMatureKavenixDomainIds(minAgeSeconds: number) {
  const [rows] = await getDbPool().query<(RowDataPacket & { id: number })[]>(
    `
      SELECT id
      FROM domains
      WHERE mailcow_cleanup_at IS NULL
        AND TIMESTAMPDIFF(SECOND, created_at, NOW()) >= ?
    `,
    [minAgeSeconds],
  );

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

export async function repairAllKavenixDomainProvisioningIssues(options?: {
  minAgeSeconds?: number;
  quietInventoryErrors?: boolean;
}) {
  const requestedMinAgeSeconds = Number(options?.minAgeSeconds ?? 0);
  const minAgeSeconds = Math.max(
    0,
    Math.min(
      24 * 60 * 60,
      Number.isFinite(requestedMinAgeSeconds)
        ? Math.floor(requestedMinAgeSeconds)
        : 0,
    ),
  );
  const domains = await getKavenixAdminDomains({
    quietInventoryErrors: options?.quietInventoryErrors,
  });

  if (domains.some((domain) => domain.provisioningStatus === "unknown")) {
    throw new Error("kavenix-mailcow-inventory-unavailable");
  }

  const matureDomainIds =
    minAgeSeconds > 0
      ? await getMatureKavenixDomainIds(minAgeSeconds)
      : null;
  const candidates = domains.filter(
    (domain) =>
      domain.repairEligible &&
      (!matureDomainIds || matureDomainIds.has(domain.id)),
  );
  const repaired: string[] = [];
  const failed: Array<{ domain: string; error: string }> = [];

  for (const candidate of candidates) {
    try {
      await repairKavenixDomainProvisioningById(candidate.id);
      repaired.push(candidate.displayDomain);
    } catch (error) {
      failed.push({
        domain: candidate.displayDomain,
        error: error instanceof Error ? error.message : "unknown",
      });
    }
  }

  return {
    candidateCount: candidates.length,
    checkedCount: domains.length,
    failed,
    repaired,
  };
}

export async function updateKavenixDomainOperationalBenefitById(
  domainId: number,
  enabled: boolean,
) {
  await ensureOfficialMailSchema();

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

  const [rows] = await getDbPool().query<KavenixDomainOperationalBenefitRow[]>(
    `
      SELECT
        d.id,
        d.domain,
        d.mailcow_cleanup_at,
        d.operational_benefit_enabled,
        u.id AS owner_user_id,
        u.email AS owner_email
      FROM domains d
      INNER JOIN users u ON u.id = d.user_id
      WHERE d.id = ?
      LIMIT 1
    `,
    [domainId],
  );
  const domain = rows[0];

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

  if (
    isKavenixOperationalDomain(domain.domain) ||
    isKavenixOperationalEmail(domain.owner_email)
  ) {
    throw new Error("kavenix-operational-domain-hidden");
  }

  if (domain.mailcow_cleanup_at) {
    throw new Error("kavenix-domain-cleaned");
  }

  await getDbPool().query(
    `
      UPDATE domains
      SET operational_benefit_enabled = ?, updated_at = NOW()
      WHERE id = ?
        AND mailcow_cleanup_at IS NULL
    `,
    [enabled ? 1 : 0, domain.id],
  );

  let quotaSyncPending = false;

  try {
    await syncMailcowMailboxQuotasForOwnerUserId(domain.owner_user_id);
  } catch (error) {
    quotaSyncPending = true;
    console.error(
      `[operational-benefit] domain=${domain.domain} quota sync failed`,
      error instanceof Error ? error.message : error,
    );
  }

  return {
    domain: domain.domain,
    operationalBenefitEnabled: enabled,
    quotaSyncPending,
  };
}

export async function updateKavenixDomainSendingServerById(
  domainId: number,
  requestedServerKey: unknown,
) {
  await ensureOfficialMailSchema();

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

  const serverKey = await requireConfiguredMailSendingServerKey(requestedServerKey);
  const [rows] = await getDbPool().query<
    (RowDataPacket & {
      domain: string;
      mailcow_cleanup_at: Date | null;
      owner_email: string;
    })[]
  >(
    `
      SELECT d.domain, d.mailcow_cleanup_at, u.email AS owner_email
      FROM domains d
      INNER JOIN users u ON u.id = d.user_id
      WHERE d.id = ?
      LIMIT 1
    `,
    [domainId],
  );
  const domain = rows[0];

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

  if (
    isKavenixOperationalDomain(domain.domain) ||
    isKavenixOperationalEmail(domain.owner_email)
  ) {
    throw new Error("kavenix-operational-domain-hidden");
  }

  if (domain.mailcow_cleanup_at) {
    throw new Error("kavenix-domain-cleaned");
  }

  await getDbPool().query(
    `
      UPDATE domains
      SET sending_server_key = ?, updated_at = NOW()
      WHERE id = ?
        AND mailcow_cleanup_at IS NULL
    `,
    [
      serverKey === DEFAULT_MAIL_SENDING_SERVER_KEY ? null : serverKey,
      domainId,
    ],
  );

  const server = (await getMailSendingServerOptions()).find(
    (candidate) => candidate.key === serverKey,
  );

  if (!server) {
    throw new Error("kavenix-sending-server-not-configured");
  }

  return {
    domain: domain.domain,
    sendingServer: server,
  };
}

type KavenixSendingServerRecordRow = RowDataPacket & {
  assigned_domain_count: number;
  id: number;
  label: string;
  server_key: string;
};

function requireKavenixSendingServerId(serverId: number) {
  if (!Number.isInteger(serverId) || serverId <= 0) {
    throw new Error("kavenix-sending-server-not-found");
  }

  return serverId;
}

async function getKavenixSendingServerRecord(serverId: number) {
  const [rows] = await getDbPool().query<KavenixSendingServerRecordRow[]>(
    `
      SELECT
        server.id,
        server.server_key,
        server.label,
        (
          SELECT COUNT(*)
          FROM domains domain_assignment
          WHERE domain_assignment.sending_server_key = server.server_key
            AND domain_assignment.mailcow_cleanup_at IS NULL
        ) AS assigned_domain_count
      FROM mail_sending_servers server
      WHERE server.id = ?
      LIMIT 1
    `,
    [serverId],
  );

  return rows[0] ?? null;
}

async function getKavenixSendingServerOptionById(serverId: number) {
  const server = (await getMailSendingServerOptions()).find(
    (candidate) => candidate.id === serverId,
  );

  if (!server) {
    throw new Error("kavenix-sending-server-not-found");
  }

  return server;
}

export async function createKavenixSendingServer(value: unknown) {
  await ensureOfficialMailSchema();
  const input = normalizeMailSendingServerInput(value);
  const serverKey = `server-${randomUUID().replaceAll("-", "").slice(0, 20)}`;
  const [result] = await getDbPool().query<ResultSetHeader>(
    `
      INSERT INTO mail_sending_servers (
        server_key,
        label,
        host,
        port,
        security_mode,
        tls_server_name,
        enabled
      ) VALUES (?, ?, ?, ?, ?, ?, ?)
    `,
    [
      serverKey,
      input.label,
      input.host,
      input.port,
      input.securityMode,
      input.tlsServerName,
      input.enabled ? 1 : 0,
    ],
  );

  return getKavenixSendingServerOptionById(Number(result.insertId));
}

export async function updateKavenixSendingServer(
  serverId: number,
  value: unknown,
) {
  await ensureOfficialMailSchema();
  requireKavenixSendingServerId(serverId);
  const input: MailSendingServerInput = normalizeMailSendingServerInput(value);
  const current = await getKavenixSendingServerRecord(serverId);

  if (!current) {
    throw new Error("kavenix-sending-server-not-found");
  }

  if (!input.enabled && Number(current.assigned_domain_count) > 0) {
    throw new Error("kavenix-sending-server-assigned");
  }

  await getDbPool().query(
    `
      UPDATE mail_sending_servers
      SET
        label = ?,
        host = ?,
        port = ?,
        security_mode = ?,
        tls_server_name = ?,
        enabled = ?
      WHERE id = ?
    `,
    [
      input.label,
      input.host,
      input.port,
      input.securityMode,
      input.tlsServerName,
      input.enabled ? 1 : 0,
      serverId,
    ],
  );

  return getKavenixSendingServerOptionById(serverId);
}

export async function deleteKavenixSendingServer(serverId: number) {
  await ensureOfficialMailSchema();
  requireKavenixSendingServerId(serverId);
  const current = await getKavenixSendingServerRecord(serverId);

  if (!current) {
    throw new Error("kavenix-sending-server-not-found");
  }

  if (Number(current.assigned_domain_count) > 0) {
    throw new Error("kavenix-sending-server-assigned");
  }

  const [result] = await getDbPool().query<ResultSetHeader>(
    `
      DELETE FROM mail_sending_servers
      WHERE id = ?
        AND NOT EXISTS (
          SELECT 1
          FROM domains domain_assignment
          WHERE domain_assignment.sending_server_key = ?
            AND domain_assignment.mailcow_cleanup_at IS NULL
        )
    `,
    [serverId, current.server_key],
  );

  if (Number(result.affectedRows) !== 1) {
    throw new Error("kavenix-sending-server-assigned");
  }

  return {
    id: serverId,
    label: current.label,
  };
}

async function getKavenixAdminMemberDomainOptions() {
  await ensureOfficialMailSchema();

  const [rows] = await getDbPool().query<KavenixMemberDomainRow[]>(`
    SELECT
      d.id,
      d.domain,
      d.mailcow_cleanup_at,
      d.operational_benefit_enabled,
      u.id AS owner_user_id,
      u.email AS owner_email,
      u.company_name,
      billing_subscription.status AS subscription_status,
      billing_subscription.last_charged_at
    FROM domains d
    INNER JOIN users u ON u.id = d.user_id
    LEFT JOIN mailbox_toss_pay_subscriptions billing_subscription
      ON billing_subscription.owner_user_id = d.user_id
    WHERE d.mailcow_cleanup_at IS NULL
      AND ${externalDomainSql("d")}
      AND ${externalOwnerSql("u")}
    ORDER BY d.domain ASC, d.id ASC
    LIMIT 500
  `);

  return rows.map((row) => ({
    companyName: row.company_name,
    domain: row.domain,
    id: row.id,
    ownerEmail: row.owner_email,
    planStatus:
      hasComplimentaryGrowthPlanByEmail(row.owner_email) ||
      row.operational_benefit_enabled === 1 ||
      (row.subscription_status === "active" && row.last_charged_at !== null)
        ? ("growth" as const)
        : row.subscription_status === "cancelled"
          ? ("cancelled" as const)
          : ("free" as const),
  })) satisfies KavenixAdminMemberDomainOption[];
}

export async function createKavenixManagedMember(input: {
  allowCancelledPlan: boolean;
  displayName: string;
  domainId: number;
  localPart: string;
  password: string;
}) {
  await ensureOfficialMailSchema();

  if (!Number.isInteger(input.domainId) || input.domainId <= 0) {
    throw new Error("kavenix-domain-not-found");
  }

  const [rows] = await getDbPool().query<KavenixMemberDomainRow[]>(
    `
      SELECT
        d.id,
        d.domain,
        d.mailcow_cleanup_at,
        d.operational_benefit_enabled,
        u.id AS owner_user_id,
        u.email AS owner_email,
        u.company_name,
        NULL AS subscription_status,
        NULL AS last_charged_at
      FROM domains d
      INNER JOIN users u ON u.id = d.user_id
      WHERE d.id = ?
      LIMIT 1
    `,
    [input.domainId],
  );
  const domain = rows[0];

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

  if (
    isKavenixOperationalDomain(domain.domain) ||
    isKavenixOperationalEmail(domain.owner_email)
  ) {
    throw new Error("kavenix-operational-domain-hidden");
  }

  if (domain.mailcow_cleanup_at) {
    throw new Error("kavenix-domain-cleaned");
  }

  const complimentary =
    hasComplimentaryGrowthPlanByEmail(domain.owner_email) ||
    domain.operational_benefit_enabled === 1;
  const overview = complimentary
    ? null
    : await getMailBillingOverviewByOwnerEmail(domain.owner_email, {
        recoverRemoteStatus: false,
      });
  const subscription = overview?.subscription;
  const growthPlanActive =
    complimentary || Boolean(subscription?.status === "active" && subscription.initialPaymentComplete);
  const cancelledPlan = subscription?.status === "cancelled";

  if (!growthPlanActive && !cancelledPlan) {
    throw new Error("growth-plan-required");
  }

  if (cancelledPlan && !input.allowCancelledPlan) {
    throw new Error("growth-plan-cancelled-confirmation-required");
  }

  if (cancelledPlan) {
    await ensureCancelledMemberAccessExpiryByOwnerUserId(domain.owner_user_id);
  }

  await createManagedMemberByOwnerEmail(
    domain.owner_email,
    {
      displayName: input.displayName,
      localPart: input.localPart,
      password: input.password,
    },
    { domainId: domain.id },
  );

  return {
    domain: domain.domain,
    email: `${input.localPart.trim().toLowerCase()}@${domain.domain}`,
    planStatus: cancelledPlan ? ("cancelled" as const) : ("growth" as const),
  };
}

async function getKavenixAdminSummary() {
  await ensureOfficialMailSchema();

  const [rows] = await getDbPool().query<AdminSummaryRow[]>(
    `
    SELECT
      (SELECT COUNT(*) FROM users u WHERE ${externalOwnerSql("u")}) AS total_users,
      (
        SELECT COUNT(*)
        FROM domains d
        INNER JOIN users owner_user ON owner_user.id = d.user_id
        WHERE d.mailcow_cleanup_at IS NULL
          AND ${externalDomainSql("d")}
          AND ${externalOwnerSql("owner_user")}
      ) AS total_domains,
      (
        SELECT COUNT(*)
        FROM domains d
        INNER JOIN users owner_user ON owner_user.id = d.user_id
        WHERE d.mailcow_cleanup_at IS NULL
          AND d.status = 'verified'
          AND ${externalDomainSql("d")}
          AND ${externalOwnerSql("owner_user")}
      ) AS verified_domains,
      (
        SELECT COUNT(*)
        FROM mailboxes m
        INNER JOIN domains d ON d.id = m.domain_id
        INNER JOIN users owner_user ON owner_user.id = d.user_id
        WHERE d.mailcow_cleanup_at IS NULL
          AND m.status = 'active'
          AND ${externalDomainSql("d")}
          AND ${externalOwnerSql("owner_user")}
      ) AS active_members,
      (
        SELECT COUNT(*)
        FROM mailbox_toss_pay_subscriptions s
        INNER JOIN users owner_user ON owner_user.id = s.owner_user_id
        WHERE s.status = 'active'
          AND ${externalOwnerSql("owner_user")}
          AND NOT EXISTS (
            SELECT 1
            FROM domains benefit_domain
            WHERE benefit_domain.user_id = s.owner_user_id
              AND benefit_domain.mailcow_cleanup_at IS NULL
              AND benefit_domain.operational_benefit_enabled = 1
          )
          AND EXISTS (
            SELECT 1
            FROM domains d
            WHERE d.user_id = s.owner_user_id
              AND d.mailcow_cleanup_at IS NULL
              AND ${externalDomainSql("d")}
          )
      ) AS active_subscriptions,
      (
        SELECT COALESCE(SUM(COALESCE(member_counts.seat_count, 1)), 0)
        FROM mailbox_toss_pay_subscriptions s
        INNER JOIN users owner_user ON owner_user.id = s.owner_user_id
        LEFT JOIN (
          SELECT owner_user_id, COUNT(*) + 1 AS seat_count
          FROM managed_team_mailboxes
          WHERE status = 'active'
          GROUP BY owner_user_id
        ) member_counts ON member_counts.owner_user_id = s.owner_user_id
        WHERE s.status = 'active'
          AND ${externalOwnerSql("owner_user")}
          AND NOT EXISTS (
            SELECT 1
            FROM domains benefit_domain
            WHERE benefit_domain.user_id = s.owner_user_id
              AND benefit_domain.mailcow_cleanup_at IS NULL
              AND benefit_domain.operational_benefit_enabled = 1
          )
          AND EXISTS (
            SELECT 1
            FROM domains d
            WHERE d.user_id = s.owner_user_id
              AND d.mailcow_cleanup_at IS NULL
              AND ${externalDomainSql("d")}
          )
      ) AS billable_growth_seats,
      (
        SELECT COUNT(*)
        FROM mailbox_toss_pay_billing_charges c
        INNER JOIN users owner_user ON owner_user.id = c.owner_user_id
        WHERE c.status = 'success'
          AND c.requested_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
          AND ${externalOwnerSql("owner_user")}
      ) AS successful_charges_30d
    `,
  );

  const row = rows[0];
  const activeMembers = Number(row?.active_members ?? 0);
  const billableGrowthSeats = Number(row?.billable_growth_seats ?? 0);

  return {
    activeMembers,
    activeSubscriptions: Number(row?.active_subscriptions ?? 0),
    expectedMonthlyRevenue: billableGrowthSeats * MAIL_GROWTH_PLAN_PRICE_PER_MEMBER,
    successfulCharges30d: Number(row?.successful_charges_30d ?? 0),
    totalDomains: Number(row?.total_domains ?? 0),
    totalUsers: Number(row?.total_users ?? 0),
    verifiedDomains: Number(row?.verified_domains ?? 0),
  } satisfies KavenixAdminSummary;
}

async function getKavenixDomainOptions() {
  await ensureOfficialMailSchema();

  const [rows] = await getDbPool().query<RowDataPacket[]>(`
    SELECT d.domain
    FROM domains d
    INNER JOIN users u ON u.id = d.user_id
    WHERE ${externalDomainSql("d")}
      AND ${externalOwnerSql("u")}
    GROUP BY d.domain
    ORDER BY d.domain ASC
    LIMIT 250
  `);

  return rows
    .map((row) => String(row.domain ?? "").trim())
    .filter(Boolean);
}

async function getKavenixAdminUsers(input: { domain?: string; query?: string }) {
  await ensureOfficialMailSchema();

  const query = normalizeFilterValue(input.query);
  const selectedDomain = normalizeFilterValue(input.domain);
  const params: string[] = [];
  const whereClauses: string[] = [adminUserSql("u")];

  if (selectedDomain) {
    whereClauses.push(`
      (
        EXISTS (
          SELECT 1
          FROM domains fd
          WHERE fd.user_id = u.id
            AND fd.domain = ?
        )
        OR EXISTS (
          SELECT 1
          FROM managed_team_mailboxes selected_member
          INNER JOIN domains selected_member_domain
            ON selected_member_domain.id = selected_member.domain_id
          WHERE selected_member.email = u.email
            AND selected_member_domain.domain = ?
        )
      )
    `);
    params.push(selectedDomain, selectedDomain);
  }

  if (query) {
    const searchLike = `%${query}%`;
    whereClauses.push(`
      (
        u.email LIKE ?
        OR u.recovery_email LIKE ?
        OR u.company_name LIKE ?
        OR u.display_name LIKE ?
        OR EXISTS (
          SELECT 1
          FROM domains qd
          WHERE qd.user_id = u.id
            AND qd.domain LIKE ?
        )
        OR EXISTS (
          SELECT 1
          FROM managed_team_mailboxes query_member
          INNER JOIN domains query_member_domain
            ON query_member_domain.id = query_member.domain_id
          WHERE query_member.email = u.email
            AND query_member_domain.domain LIKE ?
        )
        OR EXISTS (
          SELECT 1
          FROM mailboxes qm
          WHERE qm.user_id = u.id
            AND qm.email LIKE ?
        )
      )
    `);
    params.push(searchLike, searchLike, searchLike, searchLike, searchLike, searchLike, searchLike);
  }

  const whereSql = whereClauses.length > 0 ? `WHERE ${whereClauses.join(" AND ")}` : "";

  const [rows] = await getDbPool().query<AdminUserQueryRow[]>(
    `
      SELECT
        u.id,
        u.email,
        u.account_deleted_at,
        CASE
          WHEN u.recovery_email_verified_at IS NOT NULL THEN u.recovery_email
          ELSE NULL
        END AS recovery_email,
        u.company_name,
        u.display_name,
        u.mail_configured,
        CASE WHEN COALESCE(domain_counts.domain_count, 0) > 0 THEN 1 ELSE 0 END AS is_representative_owner,
        CASE WHEN managed_membership.id IS NOT NULL THEN 1 ELSE 0 END AS is_managed_member,
        COALESCE(billing_owner.email, u.email) AS billing_owner_email,
        COALESCE(latest_domain.domain, managed_domain.domain) AS latest_domain,
        COALESCE(latest_domain.status, managed_domain.status) AS latest_domain_status,
        COALESCE(latest_domain.verified_at, managed_domain.verified_at) AS latest_domain_verified_at,
        (
          SELECT MAX(dns_record.last_checked_at)
          FROM dns_records dns_record
          WHERE dns_record.domain_id = COALESCE(latest_domain.id, managed_domain.id)
        ) AS latest_domain_last_checked_at,
        latest_mailbox.email AS latest_mailbox_email,
        latest_mailbox.status AS latest_mailbox_status,
        CASE
          WHEN managed_membership.id IS NOT NULL THEN 1
          ELSE COALESCE(domain_counts.domain_count, 0)
        END AS domain_count,
        CASE
          WHEN COALESCE(latest_domain.id, managed_domain.id) IS NOT NULL
            AND COALESCE(latest_domain.mailcow_cleanup_at, managed_domain.mailcow_cleanup_at) IS NULL
          THEN COALESCE(member_counts.active_member_count, 0) + 1
          ELSE 0
        END AS active_member_count,
        billing_profile.status AS billing_profile_status,
        billing_subscription.status AS subscription_status,
        billing_subscription.next_charge_at,
        latest_charge.last_charge_at,
        latest_charge.last_charge_status,
        EXISTS (
          SELECT 1
          FROM domains benefit_domain
          WHERE benefit_domain.user_id = COALESCE(managed_membership.owner_user_id, u.id)
            AND benefit_domain.mailcow_cleanup_at IS NULL
            AND benefit_domain.operational_benefit_enabled = 1
        ) AS operational_benefit_enabled
      FROM users u
      LEFT JOIN managed_team_mailboxes managed_membership
        ON managed_membership.email = u.email
      LEFT JOIN users billing_owner
        ON billing_owner.id = managed_membership.owner_user_id
      LEFT JOIN domains managed_domain
        ON managed_domain.id = managed_membership.domain_id
      LEFT JOIN (
        SELECT user_id, MAX(id) AS latest_domain_id
        FROM domains
        WHERE ${externalDomainSql("domains")}
        GROUP BY user_id
      ) latest_domain_ref
        ON latest_domain_ref.user_id = u.id
      LEFT JOIN domains latest_domain
        ON latest_domain.id = latest_domain_ref.latest_domain_id
      LEFT JOIN (
        SELECT user_id, MAX(id) AS latest_mailbox_id
        FROM mailboxes
        GROUP BY user_id
      ) latest_mailbox_ref
        ON latest_mailbox_ref.user_id = u.id
      LEFT JOIN mailboxes latest_mailbox
        ON latest_mailbox.id = latest_mailbox_ref.latest_mailbox_id
      LEFT JOIN (
        SELECT user_id, COUNT(*) AS domain_count
        FROM domains
        WHERE ${externalDomainSql("domains")}
        GROUP BY user_id
      ) domain_counts
        ON domain_counts.user_id = u.id
      LEFT JOIN (
        SELECT owner_user_id, COUNT(*) AS active_member_count
        FROM managed_team_mailboxes
        WHERE status = 'active'
        GROUP BY owner_user_id
      ) member_counts
        ON member_counts.owner_user_id = COALESCE(managed_membership.owner_user_id, u.id)
      LEFT JOIN mailbox_toss_pay_billing_profiles billing_profile
        ON billing_profile.owner_user_id = COALESCE(managed_membership.owner_user_id, u.id)
      LEFT JOIN mailbox_toss_pay_subscriptions billing_subscription
        ON billing_subscription.owner_user_id = COALESCE(managed_membership.owner_user_id, u.id)
      LEFT JOIN (
        SELECT
          c.owner_user_id,
          MAX(c.requested_at) AS last_charge_at,
          SUBSTRING_INDEX(
            GROUP_CONCAT(c.status ORDER BY c.requested_at DESC, c.id DESC SEPARATOR ','),
            ',',
            1
          ) AS last_charge_status
        FROM mailbox_toss_pay_billing_charges c
        GROUP BY c.owner_user_id
      ) latest_charge
        ON latest_charge.owner_user_id = COALESCE(managed_membership.owner_user_id, u.id)
      ${whereSql}
      ORDER BY u.id DESC
      LIMIT 240
    `,
    params,
  );

  return rows.map((row) => {
    const activeMemberCount = Number(row.active_member_count ?? 0);

    return {
      accountDeletedAt: toIsoString(row.account_deleted_at),
      activeMemberCount,
      billingOwnerEmail: row.billing_owner_email,
      billingProfileStatus: row.billing_profile_status,
      companyName: row.company_name,
      displayName: row.display_name,
      domainCount: Number(row.domain_count ?? 0),
      email: row.email,
      estimatedMonthlyRevenue:
        row.subscription_status === "active" &&
        row.operational_benefit_enabled !== 1
          ? activeMemberCount * MAIL_GROWTH_PLAN_PRICE_PER_MEMBER
          : 0,
      id: row.id,
      isManagedMember: row.is_managed_member === 1,
      isRepresentativeOwner: row.is_representative_owner === 1,
      lastChargeAt: toIsoString(row.last_charge_at),
      lastChargeStatus: row.last_charge_status,
      latestDomain: row.latest_domain,
      latestDomainStatus: row.latest_domain_status,
      latestDomainVerifiedAt: toIsoString(
        row.latest_domain_last_checked_at ?? row.latest_domain_verified_at,
      ),
      latestMailboxEmail: row.latest_mailbox_email,
      latestMailboxStatus: row.latest_mailbox_status,
      mailConfigured: row.mail_configured === 1,
      nextChargeAt: toIsoString(row.next_charge_at),
      recoveryEmail: row.recovery_email,
      subscriptionStatus: row.subscription_status,
    } satisfies KavenixAdminUserRow;
  });
}

export async function getKavenixAdminUserAuthActivityById(
  userId: number,
  cursorValue: string | null = null,
) {
  if (!Number.isSafeInteger(userId) || userId <= 0) {
    throw new Error("kavenix-user-not-found");
  }

  const cursor = decodeAuthActivityCursor(cursorValue, userId);
  const pageWindow = authActivityPageWindow(userId, cursor);
  await ensureOfficialMailSchema();
  const [userRows] = await getDbPool().query<
    (RowDataPacket & { id: number })[]
  >("SELECT id FROM users WHERE id = ? AND account_deleted_at IS NULL LIMIT 1", [userId]);

  if (!userRows[0]) {
    throw new Error("kavenix-user-not-found");
  }

  const [rows] = await getDbPool().query<AdminUserAuthActivityQueryRow[]>(
    `
      SELECT
        id,
        event_type,
        outcome,
        source,
        identity_email,
        ip_address,
        user_agent,
        occurred_at,
        DATE_FORMAT(occurred_at, '%Y-%m-%d %H:%i:%s.%f') AS cursor_time
      FROM user_auth_activity_events
      WHERE ${pageWindow.where}
      ORDER BY occurred_at DESC, id DESC
      LIMIT ?
    `,
    pageWindow.params,
  );

  const page = authActivityPage(rows, userId);
  const activities = page.items.map((row) => ({
    eventType: row.event_type,
    id: Number(row.id),
    identityEmail: row.identity_email,
    ipAddress: row.ip_address,
    occurredAt: row.occurred_at.toISOString(),
    outcome: row.outcome,
    source: row.source,
    userAgent: row.user_agent,
  } satisfies KavenixAdminUserAuthActivity));

  return { activities, nextCursor: page.nextCursor };
}

async function getKavenixAdminDomains(input: {
  domain?: string;
  query?: string;
  quietInventoryErrors?: boolean;
}) {
  await ensureOfficialMailSchema();

  const query = normalizeFilterValue(input.query);
  const selectedDomain = normalizeFilterValue(input.domain);
  const params: string[] = [];
  const whereClauses: string[] = [
    externalDomainSql("d"),
    externalOwnerSql("u"),
  ];

  if (selectedDomain) {
    whereClauses.push("d.domain = ?");
    params.push(selectedDomain);
  }

  if (query) {
    const searchLike = `%${query}%`;
    whereClauses.push(`
      (
        d.domain LIKE ?
        OR u.email LIKE ?
        OR u.company_name LIKE ?
        OR u.display_name LIKE ?
        OR mailbox.email LIKE ?
      )
    `);
    params.push(searchLike, searchLike, searchLike, searchLike, searchLike);
  }

  const whereSql = whereClauses.length > 0 ? `WHERE ${whereClauses.join(" AND ")}` : "";

  const [rows] = await getDbPool().query<AdminDomainQueryRow[]>(
    `
      SELECT
        d.id,
        d.domain,
        d.status AS domain_status,
        d.dkim_enabled,
        d.operational_benefit_enabled,
        d.sending_server_key,
        d.mailcow_cleanup_at,
        d.verified_at,
        u.id AS owner_user_id,
        u.email AS owner_email,
        u.company_name,
        u.display_name,
        mailbox.email AS mailbox_email,
        mailbox.status AS mailbox_status,
        CASE
          WHEN d.mailcow_cleanup_at IS NULL
          THEN COALESCE(member_counts.active_member_count, 0) + 1
          ELSE 0
        END AS active_member_count,
        billing_profile.status AS billing_profile_status,
        billing_subscription.status AS subscription_status,
        billing_subscription.next_charge_at,
        latest_charge.last_charge_at,
        latest_charge.last_charge_status
      FROM domains d
      INNER JOIN users u
        ON u.id = d.user_id
      LEFT JOIN mailboxes mailbox
        ON mailbox.domain_id = d.id
       AND mailbox.user_id = d.user_id
      LEFT JOIN (
        SELECT domain_id, COUNT(*) AS active_member_count
        FROM managed_team_mailboxes
        WHERE status = 'active'
        GROUP BY domain_id
      ) member_counts
        ON member_counts.domain_id = d.id
      LEFT JOIN mailbox_toss_pay_billing_profiles billing_profile
        ON billing_profile.owner_user_id = d.user_id
      LEFT JOIN mailbox_toss_pay_subscriptions billing_subscription
        ON billing_subscription.owner_user_id = d.user_id
      LEFT JOIN (
        SELECT
          c.owner_user_id,
          MAX(c.requested_at) AS last_charge_at,
          SUBSTRING_INDEX(
            GROUP_CONCAT(c.status ORDER BY c.requested_at DESC, c.id DESC SEPARATOR ','),
            ',',
            1
          ) AS last_charge_status
        FROM mailbox_toss_pay_billing_charges c
        GROUP BY c.owner_user_id
      ) latest_charge
        ON latest_charge.owner_user_id = d.user_id
      ${whereSql}
      ORDER BY d.id DESC
      LIMIT 320
    `,
    params,
  );

  const domainIds = rows.map((row) => row.id);
  const dnsRecordsByDomainId = new Map<number, KavenixAdminDnsRecord[]>();

  if (domainIds.length > 0) {
    const placeholders = domainIds.map(() => "?").join(", ");
    const [dnsRows] = await getDbPool().query<AdminDomainDnsRecordRow[]>(
      `
        SELECT
          domain_id,
          record_type,
          host_name,
          value_text,
          priority,
          status,
          last_checked_at
        FROM dns_records
        WHERE domain_id IN (${placeholders})
        ORDER BY domain_id ASC, FIELD(record_type, 'MX', 'TXT'), host_name ASC
      `,
      domainIds,
    );

    for (const dnsRow of dnsRows) {
      const records = dnsRecordsByDomainId.get(dnsRow.domain_id) ?? [];
      records.push({
        host: dnsRow.host_name,
        lastCheckedAt: toIsoString(dnsRow.last_checked_at),
        priority: dnsRow.priority,
        status: dnsRow.status,
        type: dnsRow.record_type,
        value: dnsRow.value_text,
      });
      dnsRecordsByDomainId.set(dnsRow.domain_id, records);
    }
  }

  const mailcowInventory = await getMailcowProvisioningInventory().catch((error) => {
    if (!input.quietInventoryErrors) {
      console.error("Kavenix Mailcow provisioning inventory lookup failed", error);
    }

    return null;
  });
  const mailcowDomains = new Set(mailcowInventory?.domainNames ?? []);
  const mailcowMailboxes = new Set(mailcowInventory?.mailboxEmails ?? []);

  return rows.map((row) => {
    const activeMemberCount = Number(row.active_member_count ?? 0);
    const dnsRecords = dnsRecordsByDomainId.get(row.id) ?? [];
    const requiredDnsRecords = dnsRecords.filter(
      (record) =>
        row.dkim_enabled === 1 ||
        record.type !== "TXT" ||
        !record.host.endsWith("._domainkey"),
    );
    const dnsReady =
      requiredDnsRecords.length > 0 &&
      requiredDnsRecords.every((record) => record.status === "verified");
    const lastDnsCheckedAt = dnsRecords.reduce<string | null>((latest, record) => {
      if (!record.lastCheckedAt) {
        return latest;
      }

      return !latest || record.lastCheckedAt > latest
        ? record.lastCheckedAt
        : latest;
    }, null);
    const normalizedDomain = row.domain.trim().toLowerCase();
    const normalizedMailbox = row.mailbox_email?.trim().toLowerCase() ?? "";
    const domainInputValid = isValidDomainInput(row.domain);
    let provisioningStatus: KavenixAdminDomainRow["provisioningStatus"];

    if (row.mailcow_cleanup_at) {
      provisioningStatus = "cleaned";
    } else if (!mailcowInventory) {
      provisioningStatus = "unknown";
    } else if (!row.mailbox_email) {
      provisioningStatus = "database-mailbox-missing";
    } else if (!mailcowDomains.has(normalizedDomain)) {
      provisioningStatus = "mailcow-domain-missing";
    } else if (!mailcowMailboxes.has(normalizedMailbox)) {
      provisioningStatus = "mailcow-mailbox-missing";
    } else if (
      dnsReady &&
      (row.domain_status !== "verified" || row.mailbox_status !== "active")
    ) {
      provisioningStatus = "state-mismatch";
    } else {
      provisioningStatus = "ready";
    }

    const repairEligible =
      domainInputValid &&
      (provisioningStatus === "mailcow-mailbox-missing" ||
        (dnsReady && provisioningStatus === "state-mismatch"));

    return {
      activeMemberCount,
      billingProfileStatus: row.billing_profile_status,
      companyName: row.company_name,
      dkimEnabled: row.dkim_enabled === 1,
      displayDomain: row.domain,
      displayName: row.display_name,
      domainInputValid,
      dnsReady,
      dnsRecords,
      domainStatus: row.domain_status,
      mailcowCleanupAt: toIsoString(row.mailcow_cleanup_at),
      expectedMonthlyRevenue:
        row.subscription_status === "active" &&
        row.operational_benefit_enabled !== 1
          ? activeMemberCount * MAIL_GROWTH_PLAN_PRICE_PER_MEMBER
          : 0,
      id: row.id,
      lastChargeAt: toIsoString(row.last_charge_at),
      lastChargeStatus: row.last_charge_status,
      lastDnsCheckedAt,
      mailboxEmail: row.mailbox_email,
      mailboxStatus: row.mailbox_status,
      nextChargeAt: toIsoString(row.next_charge_at),
      operationalBenefitEnabled: row.operational_benefit_enabled === 1,
      ownerEmail: row.owner_email,
      ownerUserId: row.owner_user_id,
      provisioningStatus,
      repairEligible,
      sendingServerKey:
        row.sending_server_key?.trim().toLowerCase() ||
        DEFAULT_MAIL_SENDING_SERVER_KEY,
      subscriptionStatus: row.subscription_status,
      verifiedAt: toIsoString(row.verified_at),
    } satisfies KavenixAdminDomainRow;
  });
}

async function getKavenixAdminMailboxes(input: {
  domain?: string;
  mailboxId?: number;
  query?: string;
}) {
  await ensureOfficialMailSchema();

  const query = normalizeFilterValue(input.query);
  const selectedDomain = normalizeFilterValue(input.domain);
  const params: Array<number | string> = [];
  const whereClauses: string[] = [
    externalDomainSql("d"),
    externalOwnerSql("owner"),
  ];

  if (selectedDomain) {
    whereClauses.push("d.domain = ?");
    params.push(selectedDomain);
  }

  if (Number.isInteger(input.mailboxId) && Number(input.mailboxId) > 0) {
    whereClauses.push("m.id = ?");
    params.push(Number(input.mailboxId));
  }

  if (query) {
    const searchLike = `%${query}%`;
    whereClauses.push(`
      (
        m.email LIKE ?
        OR u.email LIKE ?
        OR u.display_name LIKE ?
        OR d.domain LIKE ?
        OR owner.email LIKE ?
      )
    `);
    params.push(searchLike, searchLike, searchLike, searchLike, searchLike);
  }

  const whereSql = whereClauses.length > 0 ? `WHERE ${whereClauses.join(" AND ")}` : "";
  const [rows] = await getDbPool().query<AdminMailboxQueryRow[]>(
    `
      SELECT
        m.id,
        m.email,
        m.status AS mailbox_status,
        m.last_sync_at,
        m.last_sync_error,
        m.created_at,
        d.id AS domain_id,
        d.domain,
        d.status AS domain_status,
        u.email AS account_email,
        u.display_name AS account_display_name,
        owner.email AS owner_email,
        CASE WHEN m.user_id = d.user_id THEN 1 ELSE 0 END AS is_representative,
        mtm.display_name AS managed_member_display_name,
        mtm.status AS managed_member_status,
        COALESCE(inbox.remote_total, 0) AS inbox_total,
        COALESCE(inbox.remote_unseen, 0) AS inbox_unseen,
        (
          SELECT COUNT(*)
          FROM mailbox_folders sync_error_folder
          WHERE sync_error_folder.mailbox_id = m.id
            AND NULLIF(TRIM(sync_error_folder.last_content_sync_error), '') IS NOT NULL
        ) AS content_sync_error_count
      FROM mailboxes m
      INNER JOIN domains d
        ON d.id = m.domain_id
      INNER JOIN users u
        ON u.id = m.user_id
      INNER JOIN users owner
        ON owner.id = d.user_id
      LEFT JOIN managed_team_mailboxes mtm
        ON mtm.domain_id = d.id
        AND LOWER(mtm.email) = LOWER(m.email)
      LEFT JOIN mailbox_folders inbox
        ON inbox.mailbox_id = m.id
        AND inbox.system_name = 'inbox'
      ${whereSql}
      ORDER BY m.created_at DESC, m.id DESC
      LIMIT 500
    `,
    params,
  );

  return rows.map((row) => ({
    accountDisplayName: row.account_display_name,
    accountEmail: row.account_email,
    contentSyncErrorCount: Number(row.content_sync_error_count ?? 0),
    createdAt: row.created_at.toISOString(),
    displayName: row.managed_member_display_name || row.account_display_name || row.email,
    domain: row.domain,
    domainId: row.domain_id,
    domainStatus: row.domain_status,
    email: row.email,
    id: row.id,
    inboxTotal: Number(row.inbox_total ?? 0),
    inboxUnread: Number(row.inbox_unseen ?? 0),
    isRepresentative: row.is_representative === 1,
    lastSyncAt: toIsoString(row.last_sync_at),
    lastSyncError: row.last_sync_error,
    managedMemberStatus: row.managed_member_status,
    ownerEmail: row.owner_email,
    status: row.mailbox_status,
  })) satisfies KavenixAdminMailboxRow[];
}

export async function getKavenixAdminMailboxById(mailboxId: number) {
  if (!Number.isInteger(mailboxId) || mailboxId <= 0) {
    return null;
  }

  const rows = await getKavenixAdminMailboxes({ mailboxId });
  return rows[0] ?? null;
}

async function getKavenixAdminCharges(input: {
  chargeFilter?: KavenixAdminChargeFilter;
  domain?: string;
  query?: string;
}) {
  await ensureOfficialMailSchema();

  const query = normalizeFilterValue(input.query);
  const selectedDomain = normalizeFilterValue(input.domain);
  const chargeFilter = normalizeKavenixChargeFilter(input.chargeFilter);
  const params: string[] = [];
  const whereClauses: string[] = [externalOwnerSql("u")];

  if (selectedDomain) {
    whereClauses.push(`
      EXISTS (
        SELECT 1
        FROM domains fd
        WHERE fd.user_id = c.owner_user_id
          AND fd.domain = ?
      )
    `);
    params.push(selectedDomain);
  }

  if (query) {
    const searchLike = `%${query}%`;
    whereClauses.push(`
      (
        u.email LIKE ?
        OR u.company_name LIKE ?
        OR u.display_name LIKE ?
        OR c.order_no LIKE ?
        OR c.product_desc LIKE ?
        OR EXISTS (
          SELECT 1
          FROM domains qd
          WHERE qd.user_id = c.owner_user_id
            AND qd.domain LIKE ?
        )
      )
    `);
    params.push(searchLike, searchLike, searchLike, searchLike, searchLike, searchLike);
  }

  if (chargeFilter === "withdrawal") {
    whereClauses.push("latest_cancellation.id IS NOT NULL");
  } else if (chargeFilter === "withdrawal_pending_review") {
    whereClauses.push(`
      latest_cancellation.id IS NOT NULL
      AND latest_cancellation.admin_review_status = 'pending_review'
      AND latest_cancellation.status = 'pending'
    `);
  } else if (chargeFilter === "withdrawal_approved") {
    whereClauses.push(`
      latest_cancellation.id IS NOT NULL
      AND latest_cancellation.admin_review_status IN ('approved_full', 'approved_partial')
      AND latest_cancellation.status IN ('pending', 'processing')
    `);
  } else if (chargeFilter === "refunded") {
    whereClauses.push(`
      (
        COALESCE(refund_totals.refunded_amount, 0) > 0
        OR COALESCE(refund_totals.pending_refund_count, 0) > 0
      )
    `);
  }

  const whereSql = whereClauses.length > 0 ? `WHERE ${whereClauses.join(" AND ")}` : "";

  const [rows] = await getDbPool().query<AdminChargeQueryRow[]>(
    `
      SELECT
        c.id,
        c.owner_user_id,
        u.email AS owner_email,
        u.company_name,
        u.display_name,
        latest_domain.domain AS display_domain,
        latest_mailbox.email AS latest_mailbox_email,
        c.charge_kind,
        c.status AS charge_status,
        c.order_no,
        c.amount,
        c.amount_tax_free,
        c.product_desc,
        c.requested_at,
        c.approved_at,
        c.pay_method,
        c.card_company_name,
        c.card_num4_print,
        c.pay_token,
        c.error_code,
        c.error_message,
        latest_cancellation.id AS cancellation_request_id,
        latest_cancellation.status AS cancellation_request_status,
        latest_cancellation.admin_review_status AS cancellation_review_status,
        latest_cancellation.requested_at AS cancellation_requested_at,
        latest_cancellation.requested_by_email AS cancellation_requested_by_email,
        latest_cancellation.reviewed_at AS cancellation_reviewed_at,
        latest_cancellation.reviewed_by_email AS cancellation_reviewed_by_email,
        latest_cancellation.review_note AS cancellation_review_note,
        latest_cancellation.approved_refund_amount AS cancellation_approved_refund_amount,
        latest_cancellation.result_text AS cancellation_result_text,
        COALESCE(refund_totals.refunded_amount, 0) AS refunded_amount,
        COALESCE(refund_totals.pending_refund_count, 0) AS pending_refund_count,
        latest_refund.status AS latest_refund_status,
        latest_refund.requested_by_email AS latest_refund_requested_by_email,
        latest_refund.reason AS latest_refund_reason,
        latest_refund.error_message AS latest_refund_error_message,
        latest_refund.requested_at AS latest_refund_requested_at,
        latest_refund.refunded_at AS latest_refunded_at
      FROM mailbox_toss_pay_billing_charges c
      INNER JOIN users u
        ON u.id = c.owner_user_id
      LEFT JOIN (
        SELECT user_id, MAX(id) AS latest_domain_id
        FROM domains
        GROUP BY user_id
      ) latest_domain_ref
        ON latest_domain_ref.user_id = c.owner_user_id
      LEFT JOIN domains latest_domain
        ON latest_domain.id = latest_domain_ref.latest_domain_id
      LEFT JOIN (
        SELECT user_id, MAX(id) AS latest_mailbox_id
        FROM mailboxes
        GROUP BY user_id
      ) latest_mailbox_ref
        ON latest_mailbox_ref.user_id = c.owner_user_id
      LEFT JOIN mailboxes latest_mailbox
        ON latest_mailbox.id = latest_mailbox_ref.latest_mailbox_id
      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
      LEFT JOIN (
        SELECT charge_id, MAX(id) AS latest_refund_id
        FROM mailbox_toss_pay_billing_refunds
        GROUP BY charge_id
      ) latest_refund_ref
        ON latest_refund_ref.charge_id = c.id
      LEFT JOIN mailbox_toss_pay_billing_refunds latest_refund
        ON latest_refund.id = latest_refund_ref.latest_refund_id
      LEFT JOIN (
        SELECT latest_request.*
        FROM mailbox_toss_pay_billing_cancellation_requests latest_request
        INNER JOIN (
          SELECT latest_charge_id, MAX(id) AS latest_request_id
          FROM mailbox_toss_pay_billing_cancellation_requests
          WHERE request_kind = 'withdrawal_refund'
            AND latest_charge_id IS NOT NULL
          GROUP BY latest_charge_id
        ) latest_request_ref
          ON latest_request_ref.latest_request_id = latest_request.id
      ) latest_cancellation
        ON latest_cancellation.latest_charge_id = c.id
      ${whereSql}
      ORDER BY c.requested_at DESC, c.id DESC
      LIMIT 320
    `,
    params,
  );

  const tossCharges = rows.map((row) => {
    const refundedAmount = Number(row.refunded_amount ?? 0);
    const pendingRefundCount = Number(row.pending_refund_count ?? 0);
    const remainingRefundableAmount = Math.max(0, row.amount - refundedAmount);
    const refundState: KavenixAdminChargeRefundState =
      pendingRefundCount > 0
        ? "processing"
        : refundedAmount <= 0
          ? "none"
          : refundedAmount >= row.amount
            ? "refunded"
            : "partial";

    return {
      amount: row.amount,
      amountTaxFree: row.amount_tax_free,
      canRefund:
        row.charge_status === "success" &&
        Boolean(row.pay_token) &&
        pendingRefundCount === 0 &&
        remainingRefundableAmount > 0,
      cancellationApprovedRefundAmount:
        row.cancellation_approved_refund_amount !== null
          ? Number(row.cancellation_approved_refund_amount)
          : null,
      cancellationRequestedAt: toIsoString(row.cancellation_requested_at),
      cancellationRequestedByEmail: row.cancellation_requested_by_email,
      cancellationRequestId: row.cancellation_request_id !== null ? Number(row.cancellation_request_id) : null,
      cancellationRequestStatus: row.cancellation_request_status,
      cancellationResultText: row.cancellation_result_text,
      cancellationReviewNote: row.cancellation_review_note,
      cancellationReviewedAt: toIsoString(row.cancellation_reviewed_at),
      cancellationReviewedByEmail: row.cancellation_reviewed_by_email,
      cancellationReviewStatus: row.cancellation_review_status,
      cardCompanyName: row.card_company_name,
      cardNum4Print: row.card_num4_print,
      chargeKind: row.charge_kind,
      chargeStatus: row.charge_status,
      companyName: row.company_name,
      displayDomain: row.display_domain,
      displayName: row.display_name,
      errorCode: row.error_code,
      errorMessage: row.error_message,
      id: row.id,
      latestMailboxEmail: row.latest_mailbox_email,
      latestRefundErrorMessage: row.latest_refund_error_message,
      latestRefundReason: row.latest_refund_reason,
      latestRefundRequestedAt: toIsoString(row.latest_refund_requested_at),
      latestRefundRequestedByEmail: row.latest_refund_requested_by_email,
      latestRefundStatus: row.latest_refund_status,
      latestRefundedAt: toIsoString(row.latest_refunded_at),
      orderNo: row.order_no,
      ownerEmail: row.owner_email,
      ownerUserId: row.owner_user_id,
      payMethod: row.pay_method,
      payTokenAvailable: Boolean(row.pay_token),
      paymentTrigger: null,
      pendingRefundCount,
      productDesc: row.product_desc,
      provider: "toss",
      currency: "krw",
      refundState,
      refundedAmount,
      remainingRefundableAmount,
      requestedAt: row.requested_at.toISOString(),
      approvedAt: toIsoString(row.approved_at),
    } satisfies KavenixAdminChargeRow;
  });

  if (chargeFilter !== "all") {
    return tossCharges;
  }

  const polarParams: string[] = [];
  const polarWhereClauses = [externalOwnerSql("u")];

  if (selectedDomain) {
    polarWhereClauses.push(`
      EXISTS (
        SELECT 1
        FROM domains fd
        WHERE fd.user_id = p.owner_user_id
          AND fd.domain = ?
      )
    `);
    polarParams.push(selectedDomain);
  }

  if (query) {
    const searchLike = `%${query}%`;
    polarWhereClauses.push(`
      (
        u.email LIKE ?
        OR u.company_name LIKE ?
        OR u.display_name LIKE ?
        OR p.polar_payment_id LIKE ?
        OR p.polar_checkout_id LIKE ?
        OR p.decline_message LIKE ?
        OR EXISTS (
          SELECT 1
          FROM domains qd
          WHERE qd.user_id = p.owner_user_id
            AND qd.domain LIKE ?
        )
      )
    `);
    polarParams.push(
      searchLike,
      searchLike,
      searchLike,
      searchLike,
      searchLike,
      searchLike,
      searchLike,
    );
  }

  const [polarRows] = await getDbPool().query<AdminPolarPaymentQueryRow[]>(
    `
      SELECT
        p.id,
        p.owner_user_id,
        u.email AS owner_email,
        u.company_name,
        u.display_name,
        latest_domain.domain AS display_domain,
        latest_mailbox.email AS latest_mailbox_email,
        p.polar_payment_id,
        p.polar_checkout_id,
        p.polar_order_id,
        p.status,
        p.payment_method,
        p.payment_trigger,
        p.currency,
        p.amount,
        p.card_brand,
        p.card_last_four,
        p.decline_reason,
        p.decline_message,
        p.payment_created_at
      FROM mailbox_polar_payments p
      INNER JOIN users u
        ON u.id = p.owner_user_id
      LEFT JOIN (
        SELECT user_id, MAX(id) AS latest_domain_id
        FROM domains
        GROUP BY user_id
      ) latest_domain_ref
        ON latest_domain_ref.user_id = p.owner_user_id
      LEFT JOIN domains latest_domain
        ON latest_domain.id = latest_domain_ref.latest_domain_id
      LEFT JOIN (
        SELECT user_id, MAX(id) AS latest_mailbox_id
        FROM mailboxes
        GROUP BY user_id
      ) latest_mailbox_ref
        ON latest_mailbox_ref.user_id = p.owner_user_id
      LEFT JOIN mailboxes latest_mailbox
        ON latest_mailbox.id = latest_mailbox_ref.latest_mailbox_id
      WHERE ${polarWhereClauses.join(" AND ")}
      ORDER BY p.payment_created_at DESC, p.id DESC
      LIMIT 320
    `,
    polarParams,
  );

  const polarCharges = polarRows.map((row) => ({
    amount: Number(row.amount),
    amountTaxFree: 0,
    approvedAt: row.status === "succeeded" ? row.payment_created_at.toISOString() : null,
    canRefund: false,
    cancellationApprovedRefundAmount: null,
    cancellationRequestedAt: null,
    cancellationRequestedByEmail: null,
    cancellationRequestId: null,
    cancellationRequestStatus: null,
    cancellationResultText: null,
    cancellationReviewNote: null,
    cancellationReviewedAt: null,
    cancellationReviewedByEmail: null,
    cancellationReviewStatus: null,
    cardCompanyName: row.card_brand,
    cardNum4Print: row.card_last_four,
    chargeKind: row.payment_trigger === "purchase" ? "manual" : "cycle",
    chargeStatus:
      row.status === "succeeded"
        ? "success"
        : row.status === "failed"
          ? "failed"
          : "requested",
    companyName: row.company_name,
    currency: row.currency,
    displayDomain: row.display_domain,
    displayName: row.display_name,
    errorCode: row.decline_reason,
    errorMessage: row.decline_message,
    id: Number(row.id),
    latestMailboxEmail: row.latest_mailbox_email,
    latestRefundErrorMessage: null,
    latestRefundReason: null,
    latestRefundRequestedAt: null,
    latestRefundRequestedByEmail: null,
    latestRefundStatus: null,
    latestRefundedAt: null,
    orderNo: row.polar_order_id ?? row.polar_checkout_id ?? row.polar_payment_id,
    ownerEmail: row.owner_email,
    ownerUserId: Number(row.owner_user_id),
    paymentTrigger: row.payment_trigger,
    payMethod: row.payment_method,
    payTokenAvailable: false,
    pendingRefundCount: 0,
    productDesc: "성장 플랜",
    provider: "polar",
    refundState: "none",
    refundedAmount: 0,
    remainingRefundableAmount: 0,
    requestedAt: row.payment_created_at.toISOString(),
  })) satisfies KavenixAdminChargeRow[];

  return [...tossCharges, ...polarCharges]
    .sort((a, b) => new Date(b.requestedAt).getTime() - new Date(a.requestedAt).getTime())
    .slice(0, 320);
}

export async function getKavenixAdminDataset(input: {
  chargeFilter?: KavenixAdminChargeFilter;
  domain?: string;
  query?: string;
}): Promise<KavenixAdminDataset> {
  const [summary, domainOptions, memberDomainOptions, users, domains, mailboxes, charges, sendingServers] = await Promise.all([
    getKavenixAdminSummary(),
    getKavenixDomainOptions(),
    getKavenixAdminMemberDomainOptions(),
    getKavenixAdminUsers(input),
    getKavenixAdminDomains(input),
    getKavenixAdminMailboxes(input),
    getKavenixAdminCharges(input),
    getMailSendingServerOptions(),
  ]);

  return {
    charges,
    chargeFilter: normalizeKavenixChargeFilter(input.chargeFilter),
    domainOptions,
    memberDomainOptions,
    domains,
    mailboxes,
    sendingServers,
    summary,
    users,
  };
}