import "server-only";

import type { RowDataPacket } from "mysql2/promise";

import { ensureOfficialMailSchema, getDbPool } from "@/lib/db";
import { MAIL_GROWTH_PLAN_PRICE_PER_MEMBER } from "@/lib/mail-billing";
import type {
  KavenixStatistics,
  KavenixStatisticsActionDetail,
  KavenixStatisticsActionDetailItem,
  KavenixStatisticsActionId,
  KavenixStatisticsActionItem,
  KavenixStatisticsAdoption,
  KavenixStatisticsDailyPoint,
  KavenixStatisticsDetail,
  KavenixStatisticsDetailItem,
  KavenixStatisticsFunnel,
  KavenixStatisticsMetricKey,
  KavenixStatisticsPeriodMetrics,
  KavenixStatisticsRange,
  KavenixStatisticsSnapshot,
  KavenixStatisticsTopDomain,
} from "@/lib/kavenix-statistics-types";

const STATISTICS_RANGES = [7, 30, 90, 365] as const;
const STATISTICS_ACTION_IDS: KavenixStatisticsActionId[] = [
  "queued-messages",
  "billing-grace",
  "sync-errors",
  "content-sync-errors",
  "pending-domains",
  "auto-send-failures",
  "relay-failures",
  "push-failures",
  "missing-recovery",
];
const INTERNAL_EMAIL_SUFFIX = "@officialsite.kr";
const INTERNAL_DOMAIN = "officialsite.kr";

type DailyMetricRow = RowDataPacket & {
  day_key: string;
  failed_charges: number | string | null;
  inbound_messages: number | string | null;
  outbound_messages: number | string | null;
  revenue: number | string | null;
  signups: number | string | null;
  successful_charges: number | string | null;
  successful_logins: number | string | null;
  verified_domains: number | string | null;
};

type ActivitySummaryRow = RowDataPacket & {
  current_active_mailboxes: number | string | null;
  previous_active_mailboxes: number | string | null;
};

type FunnelRow = RowDataPacket & {
  current_connected: number | string | null;
  current_paid: number | string | null;
  current_sent_mail: number | string | null;
  current_signups: number | string | null;
  previous_connected: number | string | null;
  previous_paid: number | string | null;
  previous_sent_mail: number | string | null;
  previous_signups: number | string | null;
};

type SnapshotRow = RowDataPacket & {
  active_api_owners: number | string | null;
  active_auto_send_owners: number | string | null;
  active_mailboxes: number | string | null;
  active_paid_owners: number | string | null;
  active_subscriptions: number | string | null;
  ai_relay_owners: number | string | null;
  billable_seats: number | string | null;
  missing_recovery_emails: number | string | null;
  push_enabled_owners: number | string | null;
  signature_owners: number | string | null;
  total_owners: number | string | null;
  verified_domains: number | string | null;
};

type HealthRow = RowDataPacket & {
  auto_send_failures: number | string | null;
  billing_grace: number | string | null;
  content_sync_errors: number | string | null;
  pending_domains: number | string | null;
  push_failures: number | string | null;
  queued_messages: number | string | null;
  relay_failures: number | string | null;
  sync_errors: number | string | null;
};

type TopDomainRow = RowDataPacket & {
  company_name: string;
  domain: string;
  inbound_messages: number | string | null;
  last_active_at: Date | null;
  outbound_messages: number | string | null;
  total_messages: number | string | null;
};

type StatisticsDetailRow = RowDataPacket & {
  detail_one: string | null;
  detail_two: string | null;
  href: string | null;
  id: number | string;
  occurred_at: Date;
  subtitle: string | null;
  title: string;
};

type StatisticsActionDetailRow = RowDataPacket & {
  detail_one: string | null;
  detail_three: string | null;
  detail_two: string | null;
  id: number | string;
  occurred_at: Date | null;
  subtitle: string | null;
  title: string;
};

function numberValue(value: number | string | null | undefined) {
  return Number(value ?? 0) || 0;
}

function ownerCondition(alias: string) {
  return `
    LOWER(${alias}.email) NOT LIKE '%${INTERNAL_EMAIL_SUFFIX}'
    AND ${alias}.account_deleted_at IS NULL
    AND NOT EXISTS (
      SELECT 1
      FROM managed_team_mailboxes owner_member
      WHERE LOWER(owner_member.email) = LOWER(${alias}.email)
    )
  `;
}

function externalDomainCondition(alias: string) {
  return `
    LOWER(${alias}.domain) <> '${INTERNAL_DOMAIN}'
    AND ${alias}.mailcow_cleanup_at IS NULL
  `;
}

function subtractDays(dateKey: string, days: number) {
  const [year, month, day] = dateKey.split("-").map(Number);
  const date = new Date(Date.UTC(year, month - 1, day));
  date.setUTCDate(date.getUTCDate() - days);
  return date.toISOString().slice(0, 10);
}

function buildDateKeys(todayKey: string, totalDays: number) {
  return Array.from({ length: totalDays }, (_, index) =>
    subtractDays(todayKey, totalDays - index - 1),
  );
}

function emptyDailyPoint(date: string): KavenixStatisticsDailyPoint {
  return {
    date,
    failedCharges: 0,
    inboundMessages: 0,
    outboundMessages: 0,
    revenue: 0,
    signups: 0,
    successfulCharges: 0,
    successfulLogins: 0,
    verifiedDomains: 0,
  };
}

function sumPeriod(points: KavenixStatisticsDailyPoint[]): KavenixStatisticsPeriodMetrics {
  const totals = points.reduce(
    (result, point) => ({
      failedCharges: result.failedCharges + point.failedCharges,
      inboundMessages: result.inboundMessages + point.inboundMessages,
      outboundMessages: result.outboundMessages + point.outboundMessages,
      revenue: result.revenue + point.revenue,
      signups: result.signups + point.signups,
      successfulCharges: result.successfulCharges + point.successfulCharges,
      successfulLogins: result.successfulLogins + point.successfulLogins,
      verifiedDomains: result.verifiedDomains + point.verifiedDomains,
    }),
    {
      failedCharges: 0,
      inboundMessages: 0,
      outboundMessages: 0,
      revenue: 0,
      signups: 0,
      successfulCharges: 0,
      successfulLogins: 0,
      verifiedDomains: 0,
    },
  );

  return {
    ...totals,
    activeMailboxes: 0,
    paymentAttempts: totals.successfulCharges + totals.failedCharges,
  };
}

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

export function normalizeKavenixStatisticsRange(
  value: string | number | null | undefined,
): KavenixStatisticsRange {
  const numeric = Number(value);
  return STATISTICS_RANGES.includes(numeric as KavenixStatisticsRange)
    ? (numeric as KavenixStatisticsRange)
    : 30;
}

export function normalizeKavenixStatisticsMetricKey(
  value: string | null | undefined,
): KavenixStatisticsMetricKey | null {
  return [
    "signups",
    "verifiedDomains",
    "inboundMessages",
    "outboundMessages",
    "revenue",
    "successfulLogins",
  ].includes(String(value))
    ? (value as KavenixStatisticsMetricKey)
    : null;
}

export function normalizeKavenixStatisticsActionId(
  value: string | null | undefined,
): KavenixStatisticsActionId | null {
  return STATISTICS_ACTION_IDS.includes(value as KavenixStatisticsActionId)
    ? (value as KavenixStatisticsActionId)
    : null;
}

export function normalizeKavenixStatisticsDate(
  value: string | null | undefined,
) {
  const candidate = String(value ?? "");

  if (!/^\d{4}-\d{2}-\d{2}$/.test(candidate)) {
    return null;
  }

  const [year, month, day] = candidate.split("-").map(Number);
  const date = new Date(Date.UTC(year, month - 1, day));

  return date.toISOString().slice(0, 10) === candidate ? candidate : null;
}

export async function getKavenixStatisticsDetail(
  date: string,
  metricKey: KavenixStatisticsMetricKey,
): Promise<KavenixStatisticsDetail> {
  await ensureOfficialMailSchema();

  const ownerSql = ownerCondition("u");
  const domainSql = externalDomainCondition("d");
  let query = "";

  if (metricKey === "signups") {
    query = `
      SELECT
        u.id,
        u.company_name AS title,
        u.email AS subtitle,
        NULLIF(u.display_name, u.company_name) AS detail_one,
        (
          SELECT signup_domain.domain
          FROM domains signup_domain
          WHERE signup_domain.user_id = u.id
          ORDER BY signup_domain.id ASC
          LIMIT 1
        ) AS detail_two,
        u.created_at AS occurred_at,
        '/kavenix?view=users' AS href
      FROM users u
      WHERE u.created_at >= ?
        AND u.created_at < DATE_ADD(?, INTERVAL 1 DAY)
        AND ${ownerSql}
      ORDER BY u.created_at DESC, u.id DESC
      LIMIT 201
    `;
  } else if (metricKey === "verifiedDomains") {
    query = `
      SELECT
        d.id,
        d.domain AS title,
        u.company_name AS subtitle,
        u.email AS detail_one,
        'DNS 검증 완료' AS detail_two,
        d.verified_at AS occurred_at,
        '/kavenix?view=domains' AS href
      FROM domains d
      INNER JOIN users u ON u.id = d.user_id
      WHERE d.status = 'verified'
        AND d.verified_at >= ?
        AND d.verified_at < DATE_ADD(?, INTERVAL 1 DAY)
        AND ${domainSql}
        AND ${ownerSql}
      ORDER BY d.verified_at DESC, d.id DESC
      LIMIT 201
    `;
  } else if (metricKey === "inboundMessages" || metricKey === "outboundMessages") {
    const direction = metricKey === "inboundMessages" ? "inbound" : "outbound";
    const transportCondition = direction === "outbound" ? "AND mm.transport_status = 'sent'" : "";

    query = `
      SELECT
        mm.id,
        COALESCE(NULLIF(mm.subject, ''), '(제목 없음)') AS title,
        CONCAT(
          COALESCE(NULLIF(mm.from_address, ''), '(발신자 없음)'),
          ' → ',
          COALESCE(NULLIF(mm.to_addresses, ''), m.email)
        ) AS subtitle,
        u.company_name AS detail_one,
        m.email AS detail_two,
        COALESCE(mm.received_at, mm.created_at) AS occurred_at,
        CONCAT('/kavenix?view=mailboxes&mailbox=', m.id) AS href
      FROM mailbox_messages mm
      INNER JOIN mailboxes m ON m.id = mm.mailbox_id
      INNER JOIN domains d ON d.id = m.domain_id
      INNER JOIN users u ON u.id = d.user_id
      WHERE mm.direction = '${direction}'
        ${transportCondition}
        AND COALESCE(mm.received_at, mm.created_at) >= ?
        AND COALESCE(mm.received_at, mm.created_at) < DATE_ADD(?, INTERVAL 1 DAY)
        AND ${domainSql}
        AND ${ownerSql}
      ORDER BY COALESCE(mm.received_at, mm.created_at) DESC, mm.id DESC
      LIMIT 201
    `;
  } else if (metricKey === "revenue") {
    query = `
      SELECT
        c.id,
        u.company_name AS title,
        u.email AS subtitle,
        CONCAT(FORMAT(c.amount, 0), '원') AS detail_one,
        CONCAT(c.product_desc, ' · ', COALESCE(NULLIF(c.pay_method, ''), '결제수단 미상')) AS detail_two,
        COALESCE(c.approved_at, c.requested_at) AS occurred_at,
        '/kavenix?view=charges' AS href
      FROM mailbox_toss_pay_billing_charges c
      INNER JOIN users u ON u.id = c.owner_user_id
      WHERE c.status = 'success'
        AND COALESCE(c.approved_at, c.requested_at) >= ?
        AND COALESCE(c.approved_at, c.requested_at) < DATE_ADD(?, INTERVAL 1 DAY)
        AND ${ownerSql}
      ORDER BY COALESCE(c.approved_at, c.requested_at) DESC, c.id DESC
      LIMIT 201
    `;
  } else {
    query = `
      SELECT
        u.id,
        COALESCE(login_owner.company_name, u.company_name) AS title,
        u.email AS subtitle,
        CASE
          WHEN SUM(login_activity.source = 'app') > 0
            AND SUM(login_activity.source IN ('web', 'signup')) > 0
            THEN '웹 · 앱 로그인'
          WHEN SUM(login_activity.source = 'app') > 0 THEN '앱 로그인'
          WHEN SUM(login_activity.source = 'signup') > 0
            AND SUM(login_activity.source = 'web') = 0
            THEN '가입 후 자동 로그인'
          ELSE '웹 로그인'
        END AS detail_one,
        CONCAT(
          CASE WHEN login_member.id IS NULL THEN '대표 계정' ELSE '팀 멤버' END,
          ' · 당일 ',
          COUNT(*),
          '회'
        ) AS detail_two,
        MAX(login_activity.occurred_at) AS occurred_at,
        '/kavenix?view=users' AS href
      FROM (
        SELECT CONCAT('event-', a.id) AS id, a.user_id, a.source, a.occurred_at
        FROM user_auth_activity_events a
        WHERE a.event_type = 'login'
          AND a.outcome = 'success'
          AND a.occurred_at >= ?
          AND a.occurred_at < DATE_ADD(?, INTERVAL 1 DAY)

        UNION ALL

        SELECT CONCAT('app-', app_session.id), app_session.user_id, 'app', COALESCE(app_session.last_used_at, app_session.created_at)
        FROM mailbox_app_sessions app_session
        WHERE COALESCE(app_session.last_used_at, app_session.created_at) >= ?
          AND COALESCE(app_session.last_used_at, app_session.created_at) < DATE_ADD(?, INTERVAL 1 DAY)
          AND NOT EXISTS (
            SELECT 1
            FROM user_auth_activity_events app_event
            WHERE app_event.user_id = app_session.user_id
              AND app_event.event_type = 'login'
              AND app_event.outcome = 'success'
              AND app_event.source = 'app'
              AND app_event.occurred_at BETWEEN
                DATE_SUB(COALESCE(app_session.last_used_at, app_session.created_at), INTERVAL 1 MINUTE)
                AND DATE_ADD(COALESCE(app_session.last_used_at, app_session.created_at), INTERVAL 1 MINUTE)
          )

        UNION ALL

        SELECT CONCAT('web-', remember_device.id), remember_device.user_id, 'web', COALESCE(remember_device.last_used_at, remember_device.created_at)
        FROM user_remember_devices remember_device
        WHERE COALESCE(remember_device.last_used_at, remember_device.created_at) >= ?
          AND COALESCE(remember_device.last_used_at, remember_device.created_at) < DATE_ADD(?, INTERVAL 1 DAY)
          AND NOT EXISTS (
            SELECT 1
            FROM user_auth_activity_events web_event
            WHERE web_event.user_id = remember_device.user_id
              AND web_event.event_type = 'login'
              AND web_event.outcome = 'success'
              AND web_event.source = 'web'
              AND web_event.occurred_at BETWEEN
                DATE_SUB(COALESCE(remember_device.last_used_at, remember_device.created_at), INTERVAL 1 MINUTE)
                AND DATE_ADD(COALESCE(remember_device.last_used_at, remember_device.created_at), INTERVAL 1 MINUTE)
          )

        UNION ALL

        SELECT CONCAT('signup-', signup_event.id), signup_event.user_id, 'signup', signup_event.occurred_at
        FROM marketing_funnel_events signup_event
        WHERE signup_event.event_type = 'signup_completed'
          AND signup_event.user_id IS NOT NULL
          AND signup_event.occurred_at >= ?
          AND signup_event.occurred_at < DATE_ADD(?, INTERVAL 1 DAY)
          AND NOT EXISTS (
            SELECT 1
            FROM user_auth_activity_events recorded_login
            WHERE recorded_login.user_id = signup_event.user_id
              AND recorded_login.event_type = 'login'
              AND recorded_login.outcome = 'success'
              AND recorded_login.occurred_at BETWEEN
                DATE_SUB(signup_event.occurred_at, INTERVAL 30 MINUTE)
                AND DATE_ADD(signup_event.occurred_at, INTERVAL 30 MINUTE)
          )
      ) login_activity
      INNER JOIN users u ON u.id = login_activity.user_id
      LEFT JOIN managed_team_mailboxes login_member
        ON LOWER(login_member.email) = LOWER(u.email)
      LEFT JOIN users login_owner ON login_owner.id = login_member.owner_user_id
      WHERE LOWER(COALESCE(login_owner.email, u.email)) NOT LIKE '%${INTERNAL_EMAIL_SUFFIX}'
      GROUP BY u.id, login_owner.company_name, u.company_name, u.email, login_member.id
      ORDER BY occurred_at DESC, u.id DESC
      LIMIT 201
    `;
  }

  const parameters = metricKey === "successfulLogins"
    ? [date, date, date, date, date, date, date, date]
    : [date, date];
  const [rows] = await getDbPool().query<StatisticsDetailRow[]>(query, parameters);
  const truncated = rows.length > 200;
  const items: KavenixStatisticsDetailItem[] = rows.slice(0, 200).map((row) => ({
    details: [row.detail_one, row.detail_two].filter((value): value is string => Boolean(value)),
    href: row.href,
    id: String(row.id),
    occurredAt: row.occurred_at.toISOString(),
    subtitle: row.subtitle,
    title: row.title,
  }));

  return { date, items, metricKey, truncated };
}

export async function getKavenixStatisticsActionDetail(
  actionId: KavenixStatisticsActionId,
): Promise<KavenixStatisticsActionDetail> {
  await ensureOfficialMailSchema();

  const ownerSql = ownerCondition("u");
  const domainSql = externalDomainCondition("d");
  let query = "";

  if (actionId === "queued-messages") {
    query = `
      SELECT
        mm.id,
        COALESCE(NULLIF(mm.subject, ''), '(제목 없음)') AS title,
        m.email AS subtitle,
        u.company_name AS detail_one,
        CONCAT(
          COALESCE(NULLIF(mm.from_address, ''), '(발신자 없음)'),
          ' → ',
          COALESCE(NULLIF(mm.to_addresses, ''), '(수신자 없음)')
        ) AS detail_two,
        CONCAT(TIMESTAMPDIFF(MINUTE, mm.created_at, NOW()), '분째 발송 대기') AS detail_three,
        mm.created_at AS occurred_at
      FROM mailbox_messages mm
      INNER JOIN mailboxes m ON m.id = mm.mailbox_id
      INNER JOIN domains d ON d.id = m.domain_id
      INNER JOIN users u ON u.id = d.user_id
      WHERE mm.direction = 'outbound'
        AND mm.transport_status = 'queued'
        AND mm.created_at < DATE_SUB(NOW(), INTERVAL 10 MINUTE)
        AND ${domainSql}
        AND ${ownerSql}
      ORDER BY mm.created_at ASC, mm.id ASC
      LIMIT 201
    `;
  } else if (actionId === "billing-grace") {
    query = `
      SELECT
        s.id,
        COALESCE(NULLIF(u.company_name, ''), u.email) AS title,
        u.email AS subtitle,
        CONCAT('최근 결제 ', s.last_charge_status, ' · 연속 실패 ', s.consecutive_failures, '회') AS detail_one,
        CASE
          WHEN s.retry_after_at IS NOT NULL
            THEN CONCAT('다음 재시도 ', DATE_FORMAT(s.retry_after_at, '%Y-%m-%d %H:%i'))
          ELSE '예약된 재시도 없음'
        END AS detail_two,
        CASE
          WHEN s.billing_grace_ends_at IS NOT NULL
            THEN CONCAT('유예 종료 ', DATE_FORMAT(s.billing_grace_ends_at, '%Y-%m-%d %H:%i'))
          ELSE '유예 종료일 미설정'
        END AS detail_three,
        s.updated_at AS occurred_at
      FROM mailbox_toss_pay_subscriptions s
      INNER JOIN users u ON u.id = s.owner_user_id
      WHERE s.status = 'active'
        AND (s.billing_grace_ends_at IS NOT NULL OR s.last_charge_status = 'failed')
        AND ${ownerSql}
      ORDER BY s.updated_at DESC, s.id DESC
      LIMIT 201
    `;
  } else if (actionId === "sync-errors") {
    query = `
      SELECT
        m.id,
        m.email AS title,
        COALESCE(NULLIF(u.company_name, ''), u.email) AS subtitle,
        d.domain AS detail_one,
        m.last_sync_error AS detail_two,
        '활성 메일함' AS detail_three,
        m.updated_at AS occurred_at
      FROM mailboxes m
      INNER JOIN domains d ON d.id = m.domain_id
      INNER JOIN users u ON u.id = d.user_id
      WHERE m.status = 'active'
        AND NULLIF(TRIM(m.last_sync_error), '') IS NOT NULL
        AND ${domainSql}
        AND ${ownerSql}
      ORDER BY m.updated_at DESC, m.id DESC
      LIMIT 201
    `;
  } else if (actionId === "content-sync-errors") {
    query = `
      SELECT
        m.id,
        m.email AS title,
        COALESCE(NULLIF(u.company_name, ''), u.email) AS subtitle,
        d.domain AS detail_one,
        GROUP_CONCAT(
          CONCAT(mf.name, ': ', mf.last_content_sync_error)
          ORDER BY mf.sort_order ASC, mf.id ASC
          SEPARATOR ' / '
        ) AS detail_two,
        CONCAT('오류 폴더 ', COUNT(*), '개') AS detail_three,
        MAX(mf.updated_at) AS occurred_at
      FROM mailboxes m
      INNER JOIN domains d ON d.id = m.domain_id
      INNER JOIN users u ON u.id = d.user_id
      INNER JOIN mailbox_folders mf ON mf.mailbox_id = m.id
      WHERE m.status = 'active'
        AND NULLIF(TRIM(mf.last_content_sync_error), '') IS NOT NULL
        AND ${domainSql}
        AND ${ownerSql}
      GROUP BY m.id, m.email, u.company_name, u.email, d.domain
      ORDER BY occurred_at DESC, m.id DESC
      LIMIT 201
    `;
  } else if (actionId === "pending-domains") {
    query = `
      SELECT
        d.id,
        d.domain AS title,
        COALESCE(NULLIF(u.company_name, ''), u.email) AS subtitle,
        u.email AS detail_one,
        CONCAT('현재 상태 ', d.status) AS detail_two,
        CONCAT('가입 후 ', TIMESTAMPDIFF(HOUR, d.created_at, NOW()), '시간 대기') AS detail_three,
        d.created_at AS occurred_at
      FROM domains d
      INNER JOIN users u ON u.id = d.user_id
      WHERE d.status <> 'verified'
        AND d.created_at < DATE_SUB(NOW(), INTERVAL 24 HOUR)
        AND ${domainSql}
        AND ${ownerSql}
      ORDER BY d.created_at ASC, d.id ASC
      LIMIT 201
    `;
  } else if (actionId === "auto-send-failures") {
    query = `
      SELECT
        delivery.id,
        COALESCE(NULLIF(delivery.subject, ''), '(제목 없음)') AS title,
        delivery.recipient_email AS subtitle,
        CONCAT(COALESCE(NULLIF(u.company_name, ''), u.email), ' · ', u.email) AS detail_one,
        delivery.error_message AS detail_two,
        CONCAT('예약 시각 ', DATE_FORMAT(delivery.scheduled_at, '%Y-%m-%d %H:%i')) AS detail_three,
        delivery.created_at AS occurred_at
      FROM mailbox_auto_send_deliveries delivery
      INNER JOIN mailbox_auto_send_settings setting ON setting.id = delivery.setting_id
      INNER JOIN users u ON u.id = setting.owner_user_id
      WHERE delivery.status = 'failed'
        AND delivery.created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
        AND ${ownerSql}
      ORDER BY delivery.created_at DESC, delivery.id DESC
      LIMIT 201
    `;
  } else if (actionId === "relay-failures") {
    query = `
      SELECT
        relay.id,
        COALESCE(NULLIF(relay.original_subject, ''), '(제목 없음)') AS title,
        CONCAT(relay.original_sender_email, ' → ', relay.notification_email) AS subtitle,
        CONCAT(COALESCE(NULLIF(u.company_name, ''), u.email), ' · ', m.email) AS detail_one,
        relay.last_error AS detail_two,
        'AI 릴레이 요약 발송 실패' AS detail_three,
        relay.updated_at AS occurred_at
      FROM mailbox_ai_assist_threads relay
      INNER JOIN users u ON u.id = relay.owner_user_id
      INNER JOIN mailboxes m ON m.id = relay.mailbox_id
      WHERE relay.summary_status = 'failed'
        AND relay.created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
        AND ${ownerSql}
      ORDER BY relay.updated_at DESC, relay.id DESC
      LIMIT 201
    `;
  } else if (actionId === "push-failures") {
    query = `
      SELECT
        push.id,
        push.mailbox_email AS title,
        COALESCE(NULLIF(u.company_name, ''), u.email) AS subtitle,
        CONCAT('플랫폼 ', UPPER(push.platform), ' · 오류 ', COALESCE(NULLIF(push.failure_code, ''), '알 수 없음')) AS detail_one,
        CASE
          WHEN push.last_success_at IS NOT NULL
            THEN CONCAT('마지막 성공 ', DATE_FORMAT(push.last_success_at, '%Y-%m-%d %H:%i'))
          ELSE '성공 기록 없음'
        END AS detail_two,
        CONCAT('알림 모드 ', push.notification_mode) AS detail_three,
        push.last_failure_at AS occurred_at
      FROM mailbox_push_devices push
      INNER JOIN users u ON u.id = push.user_id
      WHERE push.enabled = 1
        AND push.last_failure_at IS NOT NULL
        AND (push.last_success_at IS NULL OR push.last_failure_at > push.last_success_at)
        AND ${ownerSql}
      ORDER BY push.last_failure_at DESC, push.id DESC
      LIMIT 201
    `;
  } else {
    query = `
      SELECT
        u.id,
        COALESCE(NULLIF(u.company_name, ''), u.email) AS title,
        u.email AS subtitle,
        CASE
          WHEN NULLIF(TRIM(u.recovery_email), '') IS NULL THEN '보조 이메일 없음'
          ELSE CONCAT('미인증 보조 이메일 ', u.recovery_email)
        END AS detail_one,
        CASE
          WHEN u.recovery_email_verified_at IS NULL THEN '인증 기록 없음'
          ELSE CONCAT('인증 시각 ', DATE_FORMAT(u.recovery_email_verified_at, '%Y-%m-%d %H:%i'))
        END AS detail_two,
        CONCAT('가입일 ', DATE_FORMAT(u.created_at, '%Y-%m-%d')) AS detail_three,
        u.created_at AS occurred_at
      FROM users u
      WHERE ${ownerSql}
        AND (u.recovery_email IS NULL OR u.recovery_email_verified_at IS NULL)
      ORDER BY u.created_at DESC, u.id DESC
      LIMIT 201
    `;
  }

  const [rows] = await getDbPool().query<StatisticsActionDetailRow[]>(query);
  const truncated = rows.length > 200;
  const items: KavenixStatisticsActionDetailItem[] = rows.slice(0, 200).map((row) => ({
    details: [row.detail_one, row.detail_two, row.detail_three].filter(
      (value): value is string => Boolean(value),
    ),
    id: String(row.id),
    occurredAt: row.occurred_at?.toISOString() ?? null,
    subtitle: row.subtitle,
    title: row.title,
  }));

  return { actionId, items, truncated };
}

export async function getKavenixStatistics(
  requestedRange: KavenixStatisticsRange,
): Promise<KavenixStatistics> {
  const rangeDays = normalizeKavenixStatisticsRange(requestedRange);
  const currentStartOffset = rangeDays - 1;
  const previousStartOffset = rangeDays * 2 - 1;
  const ownerSql = ownerCondition("u");
  const domainSql = externalDomainCondition("d");

  await ensureOfficialMailSchema();

  const [
    [todayRows],
    [dailyRows],
    [activityRows],
    [funnelRows],
    [snapshotRows],
    [healthRows],
    [topDomainRows],
  ] = await Promise.all([
    getDbPool().query<(RowDataPacket & { today_key: string })[]>(
      "SELECT DATE_FORMAT(CURDATE(), '%Y-%m-%d') AS today_key",
    ),
    getDbPool().query<DailyMetricRow[]>(`
      SELECT
        metric_day.day_key,
        SUM(metric_day.signups) AS signups,
        SUM(metric_day.verified_domains) AS verified_domains,
        SUM(metric_day.inbound_messages) AS inbound_messages,
        SUM(metric_day.outbound_messages) AS outbound_messages,
        SUM(metric_day.revenue) AS revenue,
        SUM(metric_day.successful_charges) AS successful_charges,
        SUM(metric_day.failed_charges) AS failed_charges,
        SUM(metric_day.successful_logins) AS successful_logins
      FROM (
        SELECT DATE_FORMAT(u.created_at, '%Y-%m-%d') AS day_key,
          COUNT(*) AS signups, 0 AS verified_domains, 0 AS inbound_messages,
          0 AS outbound_messages, 0 AS revenue, 0 AS successful_charges,
          0 AS failed_charges, 0 AS successful_logins
        FROM users u
        WHERE u.created_at >= DATE_SUB(CURDATE(), INTERVAL ${previousStartOffset} DAY)
          AND ${ownerSql}
        GROUP BY DATE_FORMAT(u.created_at, '%Y-%m-%d')

        UNION ALL

        SELECT DATE_FORMAT(d.verified_at, '%Y-%m-%d') AS day_key,
          0, COUNT(*), 0, 0, 0, 0, 0, 0
        FROM domains d
        INNER JOIN users u ON u.id = d.user_id
        WHERE d.status = 'verified'
          AND d.verified_at >= DATE_SUB(CURDATE(), INTERVAL ${previousStartOffset} DAY)
          AND ${domainSql}
          AND ${ownerSql}
        GROUP BY DATE_FORMAT(d.verified_at, '%Y-%m-%d')

        UNION ALL

        SELECT DATE_FORMAT(COALESCE(mm.received_at, mm.created_at), '%Y-%m-%d') AS day_key,
          0, 0,
          SUM(CASE WHEN mm.direction = 'inbound' THEN 1 ELSE 0 END),
          SUM(CASE WHEN mm.direction = 'outbound' AND mm.transport_status = 'sent' THEN 1 ELSE 0 END),
          0, 0, 0, 0
        FROM mailbox_messages mm
        INNER JOIN mailboxes m ON m.id = mm.mailbox_id
        INNER JOIN domains d ON d.id = m.domain_id
        INNER JOIN users u ON u.id = d.user_id
        WHERE COALESCE(mm.received_at, mm.created_at) >= DATE_SUB(CURDATE(), INTERVAL ${previousStartOffset} DAY)
          AND ${domainSql}
          AND ${ownerSql}
        GROUP BY DATE_FORMAT(COALESCE(mm.received_at, mm.created_at), '%Y-%m-%d')

        UNION ALL

        SELECT DATE_FORMAT(COALESCE(c.approved_at, c.requested_at), '%Y-%m-%d') AS day_key,
          0, 0, 0, 0,
          SUM(CASE WHEN c.status = 'success' THEN c.amount ELSE 0 END),
          SUM(CASE WHEN c.status = 'success' THEN 1 ELSE 0 END),
          SUM(CASE WHEN c.status = 'failed' THEN 1 ELSE 0 END),
          0
        FROM mailbox_toss_pay_billing_charges c
        INNER JOIN users u ON u.id = c.owner_user_id
        WHERE c.requested_at >= DATE_SUB(CURDATE(), INTERVAL ${previousStartOffset} DAY)
          AND ${ownerSql}
        GROUP BY DATE_FORMAT(COALESCE(c.approved_at, c.requested_at), '%Y-%m-%d')

        UNION ALL

        SELECT DATE_FORMAT(login_activity.occurred_at, '%Y-%m-%d') AS day_key,
          0, 0, 0, 0, 0, 0, 0,
          COUNT(DISTINCT login_activity.user_id)
        FROM (
          SELECT a.user_id, a.occurred_at
          FROM user_auth_activity_events a
          WHERE a.event_type = 'login'
            AND a.outcome = 'success'
            AND a.occurred_at >= DATE_SUB(CURDATE(), INTERVAL ${previousStartOffset} DAY)

          UNION ALL

          SELECT app_session.user_id, COALESCE(app_session.last_used_at, app_session.created_at)
          FROM mailbox_app_sessions app_session
          WHERE COALESCE(app_session.last_used_at, app_session.created_at) >= DATE_SUB(CURDATE(), INTERVAL ${previousStartOffset} DAY)
            AND NOT EXISTS (
              SELECT 1
              FROM user_auth_activity_events app_event
              WHERE app_event.user_id = app_session.user_id
                AND app_event.event_type = 'login'
                AND app_event.outcome = 'success'
                AND app_event.source = 'app'
                AND app_event.occurred_at BETWEEN
                  DATE_SUB(COALESCE(app_session.last_used_at, app_session.created_at), INTERVAL 1 MINUTE)
                  AND DATE_ADD(COALESCE(app_session.last_used_at, app_session.created_at), INTERVAL 1 MINUTE)
            )

          UNION ALL

          SELECT remember_device.user_id, COALESCE(remember_device.last_used_at, remember_device.created_at)
          FROM user_remember_devices remember_device
          WHERE COALESCE(remember_device.last_used_at, remember_device.created_at) >= DATE_SUB(CURDATE(), INTERVAL ${previousStartOffset} DAY)
            AND NOT EXISTS (
              SELECT 1
              FROM user_auth_activity_events web_event
              WHERE web_event.user_id = remember_device.user_id
                AND web_event.event_type = 'login'
                AND web_event.outcome = 'success'
                AND web_event.source = 'web'
                AND web_event.occurred_at BETWEEN
                  DATE_SUB(COALESCE(remember_device.last_used_at, remember_device.created_at), INTERVAL 1 MINUTE)
                  AND DATE_ADD(COALESCE(remember_device.last_used_at, remember_device.created_at), INTERVAL 1 MINUTE)
            )

          UNION ALL

          SELECT signup_event.user_id, signup_event.occurred_at
          FROM marketing_funnel_events signup_event
          WHERE signup_event.event_type = 'signup_completed'
            AND signup_event.user_id IS NOT NULL
            AND signup_event.occurred_at >= DATE_SUB(CURDATE(), INTERVAL ${previousStartOffset} DAY)
            AND NOT EXISTS (
              SELECT 1
              FROM user_auth_activity_events recorded_login
              WHERE recorded_login.user_id = signup_event.user_id
                AND recorded_login.event_type = 'login'
                AND recorded_login.outcome = 'success'
                AND recorded_login.occurred_at BETWEEN
                  DATE_SUB(signup_event.occurred_at, INTERVAL 30 MINUTE)
                  AND DATE_ADD(signup_event.occurred_at, INTERVAL 30 MINUTE)
            )
        ) login_activity
        INNER JOIN users u ON u.id = login_activity.user_id
        LEFT JOIN managed_team_mailboxes login_member
          ON LOWER(login_member.email) = LOWER(u.email)
        LEFT JOIN users login_owner ON login_owner.id = login_member.owner_user_id
        WHERE LOWER(COALESCE(login_owner.email, u.email)) NOT LIKE '%${INTERNAL_EMAIL_SUFFIX}'
        GROUP BY DATE_FORMAT(login_activity.occurred_at, '%Y-%m-%d')
      ) metric_day
      GROUP BY metric_day.day_key
      ORDER BY metric_day.day_key ASC
    `),
    getDbPool().query<ActivitySummaryRow[]>(`
      SELECT
        COUNT(DISTINCT CASE
          WHEN COALESCE(mm.received_at, mm.created_at) >= DATE_SUB(CURDATE(), INTERVAL ${currentStartOffset} DAY)
          THEN mm.mailbox_id END
        ) AS current_active_mailboxes,
        COUNT(DISTINCT CASE
          WHEN COALESCE(mm.received_at, mm.created_at) >= DATE_SUB(CURDATE(), INTERVAL ${previousStartOffset} DAY)
            AND COALESCE(mm.received_at, mm.created_at) < DATE_SUB(CURDATE(), INTERVAL ${currentStartOffset} DAY)
          THEN mm.mailbox_id END
        ) AS previous_active_mailboxes
      FROM mailbox_messages mm
      INNER JOIN mailboxes m ON m.id = mm.mailbox_id
      INNER JOIN domains d ON d.id = m.domain_id
      INNER JOIN users u ON u.id = d.user_id
      WHERE COALESCE(mm.received_at, mm.created_at) >= DATE_SUB(CURDATE(), INTERVAL ${previousStartOffset} DAY)
        AND (
          mm.direction = 'inbound'
          OR (mm.direction = 'outbound' AND mm.transport_status = 'sent')
        )
        AND ${domainSql}
        AND ${ownerSql}
    `),
    getDbPool().query<FunnelRow[]>(`
      SELECT
        SUM(CASE WHEN cohort.period_key = 'current' THEN 1 ELSE 0 END) AS current_signups,
        SUM(CASE WHEN cohort.period_key = 'current' AND cohort.connected = 1 THEN 1 ELSE 0 END) AS current_connected,
        SUM(CASE WHEN cohort.period_key = 'current' AND cohort.sent_mail = 1 THEN 1 ELSE 0 END) AS current_sent_mail,
        SUM(CASE WHEN cohort.period_key = 'current' AND cohort.paid = 1 THEN 1 ELSE 0 END) AS current_paid,
        SUM(CASE WHEN cohort.period_key = 'previous' THEN 1 ELSE 0 END) AS previous_signups,
        SUM(CASE WHEN cohort.period_key = 'previous' AND cohort.connected = 1 THEN 1 ELSE 0 END) AS previous_connected,
        SUM(CASE WHEN cohort.period_key = 'previous' AND cohort.sent_mail = 1 THEN 1 ELSE 0 END) AS previous_sent_mail,
        SUM(CASE WHEN cohort.period_key = 'previous' AND cohort.paid = 1 THEN 1 ELSE 0 END) AS previous_paid
      FROM (
        SELECT
          u.id,
          CASE
            WHEN u.created_at >= DATE_SUB(CURDATE(), INTERVAL ${currentStartOffset} DAY) THEN 'current'
            ELSE 'previous'
          END AS period_key,
          EXISTS (
            SELECT 1 FROM domains connected_domain
            WHERE connected_domain.user_id = u.id
              AND connected_domain.status = 'verified'
              AND ${externalDomainCondition("connected_domain")}
          ) AS connected,
          EXISTS (
            SELECT 1
            FROM mailboxes sent_mailbox
            INNER JOIN mailbox_messages sent_message ON sent_message.mailbox_id = sent_mailbox.id
            WHERE sent_mailbox.user_id = u.id
              AND sent_message.direction = 'outbound'
              AND sent_message.transport_status = 'sent'
          ) AS sent_mail,
          EXISTS (
            SELECT 1 FROM mailbox_toss_pay_subscriptions paid_subscription
            WHERE paid_subscription.owner_user_id = u.id
              AND paid_subscription.status = 'active'
              AND NOT EXISTS (
                SELECT 1 FROM domains paid_benefit_domain
                WHERE paid_benefit_domain.user_id = paid_subscription.owner_user_id
                  AND paid_benefit_domain.operational_benefit_enabled = 1
                  AND paid_benefit_domain.mailcow_cleanup_at IS NULL
              )
          ) AS paid
        FROM users u
        WHERE u.created_at >= DATE_SUB(CURDATE(), INTERVAL ${previousStartOffset} DAY)
          AND ${ownerSql}
      ) cohort
    `),
    getDbPool().query<SnapshotRow[]>(`
      SELECT
        (SELECT COUNT(*) FROM users u WHERE ${ownerSql}) AS total_owners,
        (SELECT COUNT(*) FROM domains d INNER JOIN users u ON u.id = d.user_id
          WHERE d.status = 'verified' AND ${domainSql} AND ${ownerSql}) AS verified_domains,
        (SELECT COUNT(*) FROM mailboxes m INNER JOIN domains d ON d.id = m.domain_id
          INNER JOIN users u ON u.id = d.user_id
          WHERE m.status = 'active' AND ${domainSql} AND ${ownerSql}) AS active_mailboxes,
        (SELECT COUNT(*) FROM users u WHERE ${ownerSql}
          AND (u.recovery_email IS NULL OR u.recovery_email_verified_at IS NULL)) AS missing_recovery_emails,
        (SELECT COUNT(*) FROM mailbox_toss_pay_subscriptions s INNER JOIN users u ON u.id = s.owner_user_id
          WHERE s.status = 'active' AND ${ownerSql}
            AND NOT EXISTS (
              SELECT 1 FROM domains benefit_domain
              WHERE benefit_domain.user_id = s.owner_user_id
                AND benefit_domain.operational_benefit_enabled = 1
                AND benefit_domain.mailcow_cleanup_at IS NULL
            )) AS active_subscriptions,
        (SELECT COALESCE(SUM(COALESCE(member_count.seats, 1)), 0)
          FROM mailbox_toss_pay_subscriptions s
          INNER JOIN users u ON u.id = s.owner_user_id
          LEFT JOIN (
            SELECT owner_user_id, COUNT(*) + 1 AS seats
            FROM managed_team_mailboxes WHERE status = 'active' GROUP BY owner_user_id
          ) member_count ON member_count.owner_user_id = s.owner_user_id
          WHERE s.status = 'active' AND ${ownerSql}
            AND NOT EXISTS (
              SELECT 1 FROM domains benefit_domain
              WHERE benefit_domain.user_id = s.owner_user_id
                AND benefit_domain.operational_benefit_enabled = 1
                AND benefit_domain.mailcow_cleanup_at IS NULL
            )) AS billable_seats,
        (SELECT COUNT(DISTINCT s.owner_user_id) FROM mailbox_toss_pay_subscriptions s
          INNER JOIN users u ON u.id = s.owner_user_id WHERE s.status = 'active' AND ${ownerSql}
            AND NOT EXISTS (
              SELECT 1 FROM domains benefit_domain
              WHERE benefit_domain.user_id = s.owner_user_id
                AND benefit_domain.operational_benefit_enabled = 1
                AND benefit_domain.mailcow_cleanup_at IS NULL
            )) AS active_paid_owners,
        (SELECT COUNT(DISTINCT ai.owner_user_id) FROM mailbox_ai_assist_settings ai
          INNER JOIN users u ON u.id = ai.owner_user_id WHERE ai.enabled = 1 AND ${ownerSql}) AS ai_relay_owners,
        (SELECT COUNT(DISTINCT auto.owner_user_id) FROM mailbox_auto_send_settings auto
          INNER JOIN users u ON u.id = auto.owner_user_id WHERE auto.enabled = 1 AND ${ownerSql}) AS active_auto_send_owners,
        (SELECT COUNT(DISTINCT api.owner_user_id) FROM mailbox_developer_api_keys api
          INNER JOIN users u ON u.id = api.owner_user_id WHERE api.status = 'active' AND ${ownerSql}) AS active_api_owners,
        (SELECT COUNT(DISTINCT push.user_id) FROM mailbox_push_devices push
          INNER JOIN users u ON u.id = push.user_id WHERE push.enabled = 1 AND ${ownerSql}) AS push_enabled_owners,
        (SELECT COUNT(*) FROM users u WHERE u.mail_signature_enabled = 1 AND ${ownerSql}) AS signature_owners
    `),
    getDbPool().query<HealthRow[]>(`
      SELECT
        (SELECT COUNT(*) FROM domains d INNER JOIN users u ON u.id = d.user_id
          WHERE d.status <> 'verified' AND d.created_at < DATE_SUB(NOW(), INTERVAL 24 HOUR)
            AND ${domainSql} AND ${ownerSql}) AS pending_domains,
        (SELECT COUNT(*) FROM mailboxes m INNER JOIN domains d ON d.id = m.domain_id
          INNER JOIN users u ON u.id = d.user_id
          WHERE m.status = 'active' AND NULLIF(TRIM(m.last_sync_error), '') IS NOT NULL
            AND ${domainSql} AND ${ownerSql}) AS sync_errors,
        (SELECT COUNT(DISTINCT m.id) FROM mailboxes m
          INNER JOIN domains d ON d.id = m.domain_id
          INNER JOIN users u ON u.id = d.user_id
          INNER JOIN mailbox_folders mf ON mf.mailbox_id = m.id
          WHERE m.status = 'active' AND NULLIF(TRIM(mf.last_content_sync_error), '') IS NOT NULL
            AND ${domainSql} AND ${ownerSql}) AS content_sync_errors,
        (SELECT COUNT(*) FROM mailbox_toss_pay_subscriptions s INNER JOIN users u ON u.id = s.owner_user_id
          WHERE s.status = 'active'
            AND (s.billing_grace_ends_at IS NOT NULL OR s.last_charge_status = 'failed')
            AND ${ownerSql}) AS billing_grace,
        (SELECT COUNT(*) FROM mailbox_messages mm INNER JOIN mailboxes m ON m.id = mm.mailbox_id
          INNER JOIN domains d ON d.id = m.domain_id INNER JOIN users u ON u.id = d.user_id
          WHERE mm.direction = 'outbound' AND mm.transport_status = 'queued'
            AND mm.created_at < DATE_SUB(NOW(), INTERVAL 10 MINUTE)
            AND ${domainSql} AND ${ownerSql}) AS queued_messages,
        (SELECT COUNT(*) FROM mailbox_push_devices push INNER JOIN users u ON u.id = push.user_id
          WHERE push.enabled = 1 AND push.last_failure_at IS NOT NULL
            AND (push.last_success_at IS NULL OR push.last_failure_at > push.last_success_at)
            AND ${ownerSql}) AS push_failures,
        (SELECT COUNT(*) FROM mailbox_auto_send_deliveries delivery
          INNER JOIN mailbox_auto_send_settings setting ON setting.id = delivery.setting_id
          INNER JOIN users u ON u.id = setting.owner_user_id
          WHERE delivery.status = 'failed' AND delivery.created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
            AND ${ownerSql}) AS auto_send_failures,
        (SELECT COUNT(*) FROM mailbox_ai_assist_threads relay
          INNER JOIN users u ON u.id = relay.owner_user_id
          WHERE relay.summary_status = 'failed' AND relay.created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
            AND ${ownerSql}) AS relay_failures
    `),
    getDbPool().query<TopDomainRow[]>(`
      SELECT
        d.domain,
        u.company_name,
        SUM(CASE WHEN mm.direction = 'inbound' THEN 1 ELSE 0 END) AS inbound_messages,
        SUM(CASE WHEN mm.direction = 'outbound' AND mm.transport_status = 'sent' THEN 1 ELSE 0 END) AS outbound_messages,
        COUNT(*) AS total_messages,
        MAX(COALESCE(mm.received_at, mm.created_at)) AS last_active_at
      FROM mailbox_messages mm
      INNER JOIN mailboxes m ON m.id = mm.mailbox_id
      INNER JOIN domains d ON d.id = m.domain_id
      INNER JOIN users u ON u.id = d.user_id
      WHERE COALESCE(mm.received_at, mm.created_at) >= DATE_SUB(CURDATE(), INTERVAL ${currentStartOffset} DAY)
        AND (
          mm.direction = 'inbound'
          OR (mm.direction = 'outbound' AND mm.transport_status = 'sent')
        )
        AND ${domainSql}
        AND ${ownerSql}
      GROUP BY d.id, d.domain, u.company_name
      ORDER BY total_messages DESC, d.id DESC
      LIMIT 12
    `),
  ]);

  const todayKey = todayRows[0]?.today_key ?? new Date().toISOString().slice(0, 10);
  const allDateKeys = buildDateKeys(todayKey, rangeDays * 2);
  const dailyByDate = new Map(
    dailyRows.map((row) => [
      row.day_key,
      {
        date: row.day_key,
        failedCharges: numberValue(row.failed_charges),
        inboundMessages: numberValue(row.inbound_messages),
        outboundMessages: numberValue(row.outbound_messages),
        revenue: numberValue(row.revenue),
        signups: numberValue(row.signups),
        successfulCharges: numberValue(row.successful_charges),
        successfulLogins: numberValue(row.successful_logins),
        verifiedDomains: numberValue(row.verified_domains),
      } satisfies KavenixStatisticsDailyPoint,
    ]),
  );
  const allDaily = allDateKeys.map(
    (date) => dailyByDate.get(date) ?? emptyDailyPoint(date),
  );
  const previousDaily = allDaily.slice(0, rangeDays);
  const currentDaily = allDaily.slice(rangeDays);
  const current = sumPeriod(currentDaily);
  const previous = sumPeriod(previousDaily);
  current.activeMailboxes = numberValue(activityRows[0]?.current_active_mailboxes);
  previous.activeMailboxes = numberValue(activityRows[0]?.previous_active_mailboxes);

  const funnelRow = funnelRows[0];
  const currentFunnel: KavenixStatisticsFunnel = {
    connected: numberValue(funnelRow?.current_connected),
    paid: numberValue(funnelRow?.current_paid),
    sentMail: numberValue(funnelRow?.current_sent_mail),
    signups: numberValue(funnelRow?.current_signups),
  };
  const previousFunnel: KavenixStatisticsFunnel = {
    connected: numberValue(funnelRow?.previous_connected),
    paid: numberValue(funnelRow?.previous_paid),
    sentMail: numberValue(funnelRow?.previous_sent_mail),
    signups: numberValue(funnelRow?.previous_signups),
  };

  const snapshotRow = snapshotRows[0];
  const totalOwners = numberValue(snapshotRow?.total_owners);
  const snapshot: KavenixStatisticsSnapshot = {
    activeMailboxes: numberValue(snapshotRow?.active_mailboxes),
    activeSubscriptions: numberValue(snapshotRow?.active_subscriptions),
    expectedMonthlyRevenue:
      numberValue(snapshotRow?.billable_seats) * MAIL_GROWTH_PLAN_PRICE_PER_MEMBER,
    missingRecoveryEmails: numberValue(snapshotRow?.missing_recovery_emails),
    totalOwners,
    verifiedDomains: numberValue(snapshotRow?.verified_domains),
  };
  const adoption: KavenixStatisticsAdoption = {
    activeApiOwners: numberValue(snapshotRow?.active_api_owners),
    activeAutoSendOwners: numberValue(snapshotRow?.active_auto_send_owners),
    activePaidOwners: numberValue(snapshotRow?.active_paid_owners),
    aiRelayOwners: numberValue(snapshotRow?.ai_relay_owners),
    pushEnabledOwners: numberValue(snapshotRow?.push_enabled_owners),
    signatureOwners: numberValue(snapshotRow?.signature_owners),
    totalOwners,
  };

  const health = healthRows[0];
  const actionCandidates: KavenixStatisticsActionItem[] = [
    {
      count: numberValue(health?.queued_messages),
      description: "10분 넘게 발송 대기 중인 메일입니다. 큐와 워커 상태를 우선 확인하세요.",
      id: "queued-messages",
      severity: "critical",
      title: "발송 큐 적체",
    },
    {
      count: numberValue(health?.billing_grace),
      description: "결제 실패 또는 유예 기간에 들어간 성장 플랜입니다.",
      id: "billing-grace",
      severity: "critical",
      title: "결제 복구 필요",
    },
    {
      count: numberValue(health?.sync_errors),
      description: "최근 동기화 오류가 남아 있는 활성 메일함입니다.",
      id: "sync-errors",
      severity: "warning",
      title: "메일함 동기화 오류",
    },
    {
      count: numberValue(health?.content_sync_errors),
      description: "메일 목록은 동기화됐지만 본문 또는 첨부파일 캐시가 완료되지 않은 메일함입니다.",
      id: "content-sync-errors",
      severity: "warning",
      title: "메일 본문 동기화 오류",
    },
    {
      count: numberValue(health?.pending_domains),
      description: "가입 후 24시간이 지났지만 DNS 연결이 완료되지 않은 도메인입니다.",
      id: "pending-domains",
      severity: "warning",
      title: "DNS 연결 장기 대기",
    },
    {
      count: numberValue(health?.auto_send_failures),
      description: "최근 24시간 자동발송 실패 건입니다.",
      id: "auto-send-failures",
      severity: "warning",
      title: "자동발송 실패",
    },
    {
      count: numberValue(health?.relay_failures),
      description: "최근 24시간 AI 릴레이 요약 발송 실패 건입니다.",
      id: "relay-failures",
      severity: "warning",
      title: "AI 릴레이 실패",
    },
    {
      count: numberValue(health?.push_failures),
      description: "마지막 푸시 결과가 실패로 남아 있는 활성 기기입니다.",
      id: "push-failures",
      severity: "warning",
      title: "푸시 알림 전달 오류",
    },
    {
      count: snapshot.missingRecoveryEmails,
      description: "보조 이메일이 없거나 인증되지 않아 계정 복구가 어려운 고객입니다.",
      id: "missing-recovery",
      severity: "info",
      title: "보조 이메일 미인증",
    },
  ];
  const actionItems = actionCandidates.filter((item) => item.count > 0);
  const topDomains: KavenixStatisticsTopDomain[] = topDomainRows.map((row) => ({
    companyName: row.company_name,
    domain: row.domain,
    inboundMessages: numberValue(row.inbound_messages),
    lastActiveAt: toIsoString(row.last_active_at),
    outboundMessages: numberValue(row.outbound_messages),
    totalMessages: numberValue(row.total_messages),
  }));

  return {
    actionItems,
    adoption,
    current,
    currentDaily,
    currentFunnel,
    generatedAt: new Date().toISOString(),
    previous,
    previousFunnel,
    rangeDays,
    snapshot,
    topDomains,
  };
}