import "server-only";

import type { RowDataPacket } from "mysql2/promise";
import type {
  MailBillingCancellationAdminReviewStatus,
  MailBillingCancellationRequestStatus,
} from "@/lib/billing-cancellation";
import { ensureCancelledMemberAccessExpiryByOwnerUserId } from "@/lib/billing-cancellation";
import { ensureOfficialMailSchema, getDbPool } from "@/lib/db";
import { createManagedMemberByOwnerEmail } from "@/lib/mail-automation";
import {
  hasComplimentaryGrowthPlanByEmail,
  MAIL_GROWTH_PLAN_PRICE_PER_MEMBER,
} from "@/lib/mail-billing";
import { deleteMailcowDomain } from "@/lib/mailcow";
import { 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 = {
  activeMemberCount: number;
  billingProfileStatus: "inactive" | "pending" | "active" | "removed" | "failed" | null;
  companyName: string;
  displayName: string;
  domainCount: number;
  email: string;
  estimatedMonthlyRevenue: number;
  id: number;
  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;
  subscriptionStatus: "pending" | "active" | "paused" | "cancelled" | null;
};

export type KavenixAdminDomainRow = {
  activeMemberCount: number;
  billingProfileStatus: "inactive" | "pending" | "active" | "removed" | "failed" | null;
  companyName: string;
  dkimEnabled: boolean;
  displayDomain: string;
  displayName: string;
  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;
  ownerEmail: string;
  ownerUserId: number;
  subscriptionStatus: "pending" | "active" | "paused" | "cancelled" | null;
  verifiedAt: string | null;
};

export type KavenixAdminMailboxRow = {
  accountDisplayName: string;
  accountEmail: string;
  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;
  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[];
  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 & {
  active_member_count: number | 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_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;
  subscription_status: "pending" | "active" | "paused" | "cancelled" | 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;
  owner_email: string;
  owner_user_id: number;
  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 AdminMailboxQueryRow = RowDataPacket & {
  account_display_name: string;
  account_email: string;
  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;
};

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

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

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

type KavenixMemberDomainRow = RowDataPacket & {
  company_name: string;
  domain: string;
  id: number;
  last_charged_at: Date | null;
  mailcow_cleanup_at: Date | null;
  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;
}

export async function cleanupKavenixDomainById(domainId: number) {
  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,
        d.mailcow_cleanup_at
      FROM domains d
      WHERE d.id = ?
      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 (domain.mailcow_cleanup_at) {
    return {
      alreadyCleaned: true,
      domain: domain.domain,
    };
  }

  await deleteMailcowDomain(domain.domain);

  await getDbPool().query(
    `
      UPDATE domains
      SET mailcow_cleanup_at = NOW(), updated_at = NOW()
      WHERE id = ?
    `,
    [domain.id],
  );
  await getDbPool().query(
    `
      UPDATE mailboxes
      SET
        status = 'pending_dns',
        last_sync_error = '최고관리자 도메인 정리로 Mailcow 리소스가 제거되었습니다.',
        updated_at = NOW()
      WHERE domain_id = ?
    `,
    [domain.id],
  );
  await getDbPool().query(
    `
      UPDATE users u
      INNER JOIN mailboxes m ON m.user_id = u.id
      SET u.mail_configured = 0, u.updated_at = NOW()
      WHERE m.domain_id = ?
    `,
    [domain.id],
  );
  await getDbPool().query(
    `
      UPDATE mailbox_toss_pay_subscriptions
      SET
        status = 'paused',
        next_charge_at = NULL,
        retry_after_at = NULL,
        processing_started_at = NULL,
        updated_at = NOW()
      WHERE owner_user_id = ?
        AND status IN ('active', 'pending')
    `,
    [domain.owner_user_id],
  );

  return {
    alreadyCleaned: false,
    domain: domain.domain,
  };
}

async function getKavenixAdminMemberDomainOptions() {
  await ensureOfficialMailSchema();

  const [rows] = await getDbPool().query<KavenixMemberDomainRow[]>(`
    SELECT
      d.id,
      d.domain,
      d.mailcow_cleanup_at,
      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.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,
        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);
  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 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 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[] = [externalOwnerSql("u")];

  if (selectedDomain) {
    whereClauses.push(`
      EXISTS (
        SELECT 1
        FROM domains fd
        WHERE fd.user_id = u.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 EXISTS (
          SELECT 1
          FROM domains qd
          WHERE qd.user_id = u.id
            AND qd.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);
  }

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

  const [rows] = await getDbPool().query<AdminUserQueryRow[]>(
    `
      SELECT
        u.id,
        u.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,
        latest_domain.domain AS latest_domain,
        latest_domain.status AS latest_domain_status,
        latest_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 = latest_domain.id
        ) AS latest_domain_last_checked_at,
        latest_mailbox.email AS latest_mailbox_email,
        latest_mailbox.status AS latest_mailbox_status,
        COALESCE(domain_counts.domain_count, 0) AS domain_count,
        CASE
          WHEN latest_domain.id IS NOT NULL
            AND latest_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
      FROM users u
      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 = u.id
      LEFT JOIN mailbox_toss_pay_billing_profiles billing_profile
        ON billing_profile.owner_user_id = u.id
      LEFT JOIN mailbox_toss_pay_subscriptions billing_subscription
        ON billing_subscription.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 = u.id
      ${whereSql}
      ORDER BY u.id DESC
      LIMIT 240
    `,
    params,
  );

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

    return {
      activeMemberCount,
      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"
          ? activeMemberCount * MAIL_GROWTH_PLAN_PRICE_PER_MEMBER
          : 0,
      id: row.id,
      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),
      subscriptionStatus: row.subscription_status,
    } satisfies KavenixAdminUserRow;
  });
}

async function getKavenixAdminDomains(input: { domain?: string; query?: string }) {
  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.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);
    }
  }

  return rows.map((row) => {
    const activeMemberCount = Number(row.active_member_count ?? 0);
    const dnsRecords = dnsRecordsByDomainId.get(row.id) ?? [];
    const lastDnsCheckedAt = dnsRecords.reduce<string | null>((latest, record) => {
      if (!record.lastCheckedAt) {
        return latest;
      }

      return !latest || record.lastCheckedAt > latest
        ? record.lastCheckedAt
        : latest;
    }, null);

    return {
      activeMemberCount,
      billingProfileStatus: row.billing_profile_status,
      companyName: row.company_name,
      dkimEnabled: row.dkim_enabled === 1,
      displayDomain: row.domain,
      displayName: row.display_name,
      dnsRecords,
      domainStatus: row.domain_status,
      mailcowCleanupAt: toIsoString(row.mailcow_cleanup_at),
      expectedMonthlyRevenue:
        row.subscription_status === "active"
          ? 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),
      ownerEmail: row.owner_email,
      ownerUserId: row.owner_user_id,
      subscriptionStatus: row.subscription_status,
      verifiedAt: toIsoString(row.verified_at),
    } satisfies KavenixAdminDomainRow;
  });
}

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

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

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

  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
      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 d.domain ASC, is_representative DESC, m.id DESC
      LIMIT 500
    `,
    params,
  );

  return rows.map((row) => ({
    accountDisplayName: row.account_display_name,
    accountEmail: row.account_email,
    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[];
}

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

  return 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),
      pendingRefundCount,
      productDesc: row.product_desc,
      refundState,
      refundedAmount,
      remainingRefundableAmount,
      requestedAt: row.requested_at.toISOString(),
      approvedAt: toIsoString(row.approved_at),
    } satisfies KavenixAdminChargeRow;
  });
}

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

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