import "server-only";

import { createHash } from "crypto";
import type { ResultSetHeader, RowDataPacket } from "mysql2/promise";
import { ensureMarketingAnalyticsSchema, getDbPool } from "@/lib/db";
import { sendGoogleAnalyticsLifecycleEvent } from "@/lib/google-analytics-server";
import {
  normalizeGoogleAnalyticsClientId,
  normalizeGoogleAnalyticsSessionId,
} from "@/lib/google-analytics-client-id";
import {
  detectMarketingApp,
  getMarketingChannel,
  isMarketingAppPath,
  INTERNAL_MARKETING_CHANNEL,
  MARKETING_PLATFORMS,
  normalizeMarketingAttribution,
  normalizeMarketingLandingPath,
  UNKNOWN_MARKETING_CHANNEL,
} from "@/lib/marketing-attribution";
import { getPublicClientNetwork, getStoredClientIp, normalizePublicClientIp, publicClientIpSql } from "@/lib/request-client-ip";
import { getMarketingPagination, type MarketingPagination } from "@/lib/marketing-pagination";

const MARKETING_LIFECYCLE_EVENT_TYPES = [
  "signup_completed",
  "domain_verified",
  "growth_plan_started",
  "payment_method_added",
  "purchase_completed",
] as const;
const MARKETING_INTERACTION_EVENT_TYPES = [
  "signup_form_viewed",
  "signup_recovery_sent",
  "signup_recovery_verified",
  "signup_submitted",
  "signup_failed",
  "dns_setup_viewed",
  "dns_wizard_step_viewed",
  "dns_record_confirmed",
  "dns_verification_attempted",
  "dns_verification_failed",
] as const;
const MARKETING_PRODUCT_FUNNEL_EVENT_TYPES = [
  ...MARKETING_INTERACTION_EVENT_TYPES,
  ...MARKETING_LIFECYCLE_EVENT_TYPES,
] as const;
const KAVENIX_MARKETING_RANGE_DAYS = [7, 30, 90] as const;
const KAVENIX_MARKETING_FUNNEL_STEPS = ["landing", "signup", "domain", "growth"] as const;
const MAX_VISITOR_ID_LENGTH = 96;

export const MARKETING_VISITOR_COOKIE = "official_mail_marketing_visitor";

export type MarketingEventType =
  | "landing_view"
  | "app_view"
  | "signup_completed"
  | "domain_verified"
  | "growth_plan_started"
  | "payment_method_added"
  | "purchase_completed";
export type MarketingLifecycleEventType =
  (typeof MARKETING_LIFECYCLE_EVENT_TYPES)[number];
export type MarketingInteractionEventType =
  (typeof MARKETING_INTERACTION_EVENT_TYPES)[number];
export type MarketingProductFunnelEventType =
  (typeof MARKETING_PRODUCT_FUNNEL_EVENT_TYPES)[number];
export type KavenixMarketingRangeDays =
  (typeof KAVENIX_MARKETING_RANGE_DAYS)[number];
export type KavenixMarketingFunnelStep =
  (typeof KAVENIX_MARKETING_FUNNEL_STEPS)[number];
export type KavenixMarketingDrilldownDimension = "channel" | "funnel" | "page";
export type KavenixMarketingDrilldown = {
  dimension: KavenixMarketingDrilldownDimension;
  value: string;
};

const MARKETING_FUNNEL_STEP_DETAILS = {
  domain: {
    audienceDescription: "기간 안에 도메인 연결 검증을 완료한 계정입니다.",
    audienceTitle: "도메인 연결 완료 계정",
    eventType: "domain_verified" as const,
    label: "도메인 연결",
  },
  growth: {
    audienceDescription: "기간 안에 성장플랜 전환이 시작된 대표 계정입니다.",
    audienceTitle: "성장플랜 전환 계정",
    eventType: "growth_plan_started" as const,
    label: "성장플랜 전환",
  },
  landing: {
    audienceDescription: "기간 안에 공개 페이지를 방문한 익명 방문자입니다.",
    audienceTitle: "최근 유입 방문자",
    eventType: "landing_view" as const,
    label: "유입 방문자",
  },
  signup: {
    audienceDescription: "기간 안에 회원가입을 완료한 대표 계정입니다.",
    audienceTitle: "가입 완료 계정",
    eventType: "signup_completed" as const,
    label: "가입 완료",
  },
} satisfies Record<KavenixMarketingFunnelStep, {
  audienceDescription: string;
  audienceTitle: string;
  eventType: MarketingEventType;
  label: string;
}>;

export type KavenixMarketingAnalytics = {
  appPages: Array<{ path: string; visitors: number }>;
  channels: Array<{
    label: string;
    visitors: number;
  }>;
  dailyVisitors: Array<{
    day: string;
    visitors: number;
  }>;
  landingPages: Array<{
    path: string;
    visitors: number;
  }>;
  metrics: {
    domainVerified: number;
    growthPlanStarted: number;
    landingVisitors: number;
    signups: number;
  };
  productFunnel: Array<{
    actors: number;
    detail: string | null;
    eventType: string;
    events: number;
  }>;
  drilldown: {
    pagination: MarketingPagination;
    breakdown: Array<{
      label: string;
      visitors: number;
    }>;
    devices: Array<{
      label: string;
      visitors: number;
    }>;
    daily: Array<{
      day: string;
      domainVerified: number;
      growthPlanStarted: number;
      signups: number;
      visitors: number;
    }>;
    dimension: KavenixMarketingDrilldownDimension;
    label: string;
    audience: Array<{
      channel: string;
      companyName: string | null;
      country: string | null;
      domain: string | null;
      email: string | null;
      ipAddress: string | null;
      ipAddressApproximate: boolean;
      landingPath: string | null;
      occurredAt: string | null;
      referrer: string | null;
      visitorId: string | null;
    }>;
    audienceDescription: string;
    audienceTitle: string;
    metrics: {
      domainVerified: number;
      growthPlanStarted: number;
      landingVisitors: number;
      networks: number;
      signups: number;
    };
    referrers: Array<{
      label: string;
      visitors: number;
    }>;
    recentVisits: Array<{
      browser: string | null;
      channel: string;
      country: string | null;
      device: string | null;
      ipAddress: string | null;
      ipAddressApproximate: boolean;
      landingPath: string;
      language: string | null;
      network: string | null;
      occurredAt: string | null;
      operatingSystem: string | null;
      referrer: string | null;
      timezone: string | null;
      viewport: string | null;
    }>;
    value: string;
  } | null;
  rangeDays: KavenixMarketingRangeDays;
  trackingStartedAt: string | null;
};

export type KavenixMarketingProductFunnelDetail = {
  actorCount: number;
  actors: Array<{
    channel: string;
    companyName: string | null;
    country: string | null;
    domain: string | null;
    email: string | null;
    eventCount: number;
    firstOccurredAt: string | null;
    id: string;
    landingPath: string | null;
    lastOccurredAt: string | null;
    referrer: string | null;
    visitorId: string | null;
  }>;
  detail: string | null;
  eventCount: number;
  eventType: MarketingProductFunnelEventType;
  rangeDays: KavenixMarketingRangeDays;
  truncated: boolean;
};

type MarketingEventSummaryRow = RowDataPacket & {
  domain_count: number;
  event_type: MarketingEventType;
  user_count: number;
  visitor_count: number;
};

type MarketingTrafficRow = RowDataPacket & {
  label: string | null;
  visitors: number;
};

type MarketingDailyVisitorRow = RowDataPacket & {
  day: string;
  visitors: number;
};

type MarketingFirstEventRow = RowDataPacket & {
  occurred_at: Date | null;
};

type MarketingVisitorCountRow = RowDataPacket & {
  visitors: number;
};

type MarketingProductFunnelRow = RowDataPacket & {
  actors: number;
  event_detail: string | null;
  event_type: string;
  events: number;
};

type MarketingProductFunnelSummaryRow = RowDataPacket & {
  actors: number;
  events: number;
};

type MarketingProductFunnelActorRow = MarketingAudienceRow & {
  actor_key: string;
  event_count: number;
  first_occurred_at: Date | null;
  last_occurred_at: Date | null;
};

type MarketingDrilldownMetricRow = RowDataPacket & {
  domain_verified: number;
  growth_plan_started: number;
  signups: number;
  networks: number;
  visitors: number;
};

type MarketingDrilldownDailyRow = RowDataPacket & {
  day: string;
  domain_verified: number;
  growth_plan_started: number;
  signups: number;
  visitors: number;
};

type MarketingRecentVisitRow = RowDataPacket & {
  user_agent: string | null;
  utm_source: string | null;
  utm_medium: string | null;
  browser_name: string | null;
  client_ip: string | null;
  client_ip_prefix: string | null;
  country_code: string | null;
  device_type: string | null;
  landing_path: string | null;
  language: string | null;
  occurred_at: Date | null;
  os_name: string | null;
  referrer_host: string | null;
  referrer_path: string | null;
  timezone: string | null;
  viewport_height: number | null;
  viewport_width: number | null;
};

type MarketingAudienceRow = RowDataPacket & {
  user_agent: string | null;
  browser_name: string | null;
  utm_source: string | null;
  utm_medium: string | null;
  client_ip: string | null;
  client_ip_prefix: string | null;
  company_name: string | null;
  country_code: string | null;
  domain: string | null;
  landing_path: string | null;
  occurred_at: Date | null;
  referrer_host: string | null;
  referrer_path: string | null;
  user_email: string | null;
  visitor_id: string | null;
};

function cleanText(value: unknown, maxLength: number) {
  const normalized = String(value ?? "")
    .trim()
    .replace(/[\u0000-\u001f\u007f]/g, "");

  return normalized.slice(0, maxLength) || null;
}

function normalizeVisitorId(value: unknown) {
  const visitorId = cleanText(value, MAX_VISITOR_ID_LENGTH);

  if (!visitorId || !/^[a-z0-9_-]{16,96}$/i.test(visitorId)) {
    return null;
  }

  return visitorId;
}

function normalizeLandingPath(value: unknown) {
  const path = cleanText(value, 191);

  if (!path || !path.startsWith("/") || path.includes("://")) {
    return null;
  }

  return path;
}

function normalizePositiveId(value: unknown) {
  const id = Number(value);
  return Number.isInteger(id) && id > 0 ? id : null;
}

function normalizeBoundedNumber(value: unknown, max: number) {
  const numberValue = Number(value);
  return Number.isInteger(numberValue) && numberValue > 0 && numberValue <= max
    ? numberValue
    : null;
}

function normalizeCountryCode(value: unknown) {
  const countryCode = cleanText(value, 2)?.toUpperCase() ?? null;
  return countryCode && /^[A-Z]{2}$/.test(countryCode) ? countryCode : null;
}

function getClientIpHash(value: string | null | undefined) {
  const ip = normalizePublicClientIp(value);
  const secret = process.env.MARKETING_IP_HASH_SECRET?.trim() || process.env.OFFICIAL_MAIL_CRON_SECRET?.trim();

  if (!ip || !secret) {
    return null;
  }

  return createHash("sha256").update(`${secret}:${ip}`).digest("hex");
}

function detectDeviceType(userAgent: string | null) {
  const value = userAgent?.toLowerCase() ?? "";

  if (/ipad|tablet/.test(value)) {
    return "tablet";
  }

  if (/mobi|android/.test(value)) {
    return "mobile";
  }

  return value ? "desktop" : null;
}

function detectBrowserName(userAgent: string | null) {
  const value = userAgent?.toLowerCase() ?? "";

  if (value.includes("edg/")) return "Edge";
  if (value.includes("opr/") || value.includes("opera")) return "Opera";
  if (value.includes("firefox/")) return "Firefox";
  if (value.includes("chrome/") || value.includes("crios/")) return "Chrome";
  if (value.includes("safari/") && !value.includes("chrome/")) return "Safari";
  return value ? "Other" : null;
}

function detectOsName(userAgent: string | null) {
  const value = userAgent?.toLowerCase() ?? "";

  if (value.includes("windows")) return "Windows";
  if (value.includes("android")) return "Android";
  if (/iphone|ipad|ipod/.test(value)) return "iOS";
  if (value.includes("mac os")) return "macOS";
  if (value.includes("linux")) return "Linux";
  return value ? "Other" : null;
}

function isLifecycleEventType(value: string): value is MarketingLifecycleEventType {
  return MARKETING_LIFECYCLE_EVENT_TYPES.includes(
    value as MarketingLifecycleEventType,
  );
}

export function isMarketingInteractionEventType(
  value: string,
): value is MarketingInteractionEventType {
  return MARKETING_INTERACTION_EVENT_TYPES.includes(
    value as MarketingInteractionEventType,
  );
}

export function normalizeMarketingProductFunnelEventType(
  value: unknown,
): MarketingProductFunnelEventType | null {
  const eventType = cleanText(value, 64) ?? "";

  return MARKETING_PRODUCT_FUNNEL_EVENT_TYPES.includes(
    eventType as MarketingProductFunnelEventType,
  )
    ? (eventType as MarketingProductFunnelEventType)
    : null;
}

function normalizeInteractionDetail(value: unknown) {
  const detail = (cleanText(value, 96) ?? "").toLowerCase();
  return /^[a-z0-9][a-z0-9:_-]{0,95}$/.test(detail) ? detail : null;
}

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

export function normalizeKavenixMarketingRangeDays(
  value: string | number | null | undefined,
): KavenixMarketingRangeDays {
  const days = Number(value);

  return KAVENIX_MARKETING_RANGE_DAYS.includes(days as KavenixMarketingRangeDays)
    ? (days as KavenixMarketingRangeDays)
    : 30;
}

export function normalizeMarketingProductFunnelDetail(value: unknown) {
  return normalizeInteractionDetail(value);
}

export function normalizeKavenixMarketingDrilldown(input: {
  dimension?: string | null;
  value?: string | null;
}): KavenixMarketingDrilldown | null {
  const value = cleanText(input.value, 191);

  if (!value) {
    return null;
  }

  if (input.dimension === "page" && normalizeLandingPath(value)) {
    return { dimension: "page", value };
  }

  if (input.dimension === "channel") {
    return { dimension: "channel", value: value === "직접 방문" ? UNKNOWN_MARKETING_CHANNEL : value };
  }

  if (
    input.dimension === "funnel" &&
    KAVENIX_MARKETING_FUNNEL_STEPS.includes(value as KavenixMarketingFunnelStep)
  ) {
    return { dimension: "funnel", value };
  }

  return null;
}

export async function getKavenixMarketingVisitorCount(input?: {
  rangeDays?: string | number | null;
}) {
  await ensureMarketingAnalyticsSchema();

  const rangeDays = normalizeKavenixMarketingRangeDays(input?.rangeDays);
  const from = new Date(Date.now() - rangeDays * 24 * 60 * 60 * 1000);
  const [rows] = await getDbPool().query<MarketingVisitorCountRow[]>(
    `
      SELECT COUNT(DISTINCT visitor_id) AS visitors
      FROM marketing_funnel_events
      WHERE event_type = 'landing_view'
        AND visitor_id IS NOT NULL
        AND visitor_id <> ''
        AND occurred_at >= ?
    `,
    [from],
  );

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

export async function getKavenixMarketingProductFunnelDetail(input: {
  detail?: string | null;
  eventType: string;
  rangeDays?: string | number | null;
}): Promise<KavenixMarketingProductFunnelDetail> {
  await ensureMarketingAnalyticsSchema();

  const eventType = normalizeMarketingProductFunnelEventType(input.eventType);
  if (!eventType) {
    throw new Error("Unsupported marketing product funnel event type");
  }

  const detail = input.detail ? normalizeMarketingProductFunnelDetail(input.detail) : null;
  if (input.detail && !detail) {
    throw new Error("Unsupported marketing product funnel detail");
  }

  const rangeDays = normalizeKavenixMarketingRangeDays(input.rangeDays);
  const from = new Date(Date.now() - rangeDays * 24 * 60 * 60 * 1000);
  const detailCondition = detail
    ? "event_detail = ?"
    : "(event_detail IS NULL OR event_detail = '')";
  const params = detail ? [from, eventType, detail] : [from, eventType];
  const [summaryResult, actorResult] = await Promise.all([
    getDbPool().query<MarketingProductFunnelSummaryRow[]>(
      `
        SELECT
          COUNT(*) AS events,
          COUNT(DISTINCT CASE
            WHEN user_id IS NOT NULL THEN CONCAT('user:', user_id)
            WHEN visitor_id IS NOT NULL THEN CONCAT('visitor:', visitor_id)
            ELSE NULL
          END) AS actors
        FROM marketing_funnel_events
        WHERE occurred_at >= ?
          AND event_type = ?
          AND ${detailCondition}
      `,
      params,
    ),
    getDbPool().query<MarketingProductFunnelActorRow[]>(
      `
        SELECT
          actor.actor_key,
          actor.event_count,
          actor.first_occurred_at,
          actor.last_occurred_at,
          COALESCE(event.client_ip, landing_event.client_ip) AS client_ip,
          COALESCE(event.client_ip_prefix, landing_event.client_ip_prefix) AS client_ip_prefix,
          COALESCE(event.country_code, landing_event.country_code) AS country_code,
          COALESCE(event.landing_path, landing_event.landing_path) AS landing_path,
          event.occurred_at,
          COALESCE(event.referrer_host, landing_event.referrer_host) AS referrer_host,
          COALESCE(event.referrer_path, landing_event.referrer_path) AS referrer_path,
          COALESCE(event.utm_source, landing_event.utm_source) AS utm_source,
          COALESCE(event.utm_medium, landing_event.utm_medium) AS utm_medium,
          COALESCE(event.user_agent, landing_event.user_agent) AS user_agent,
          COALESCE(event.browser_name, landing_event.browser_name) AS browser_name,
          event.visitor_id,
          account.email AS user_email,
          account.company_name,
          COALESCE(domain.domain, owner_domain.domain) AS domain
        FROM (
          SELECT
            CASE
              WHEN user_id IS NOT NULL THEN CONCAT('user:', user_id)
              WHEN visitor_id IS NOT NULL THEN CONCAT('visitor:', visitor_id)
              ELSE NULL
            END AS actor_key,
            COUNT(*) AS event_count,
            MIN(occurred_at) AS first_occurred_at,
            MAX(occurred_at) AS last_occurred_at,
            MAX(id) AS latest_event_id
          FROM marketing_funnel_events
          WHERE occurred_at >= ?
            AND event_type = ?
            AND ${detailCondition}
          GROUP BY actor_key
          HAVING actor_key IS NOT NULL
          ORDER BY last_occurred_at DESC
          LIMIT 201
        ) actor
        INNER JOIN marketing_funnel_events event ON event.id = actor.latest_event_id
        LEFT JOIN marketing_funnel_events identified_event ON identified_event.id = (
          SELECT source.id
          FROM marketing_funnel_events source
          WHERE source.visitor_id = event.visitor_id
            AND source.user_id IS NOT NULL
          ORDER BY source.occurred_at DESC, source.id DESC
          LIMIT 1
        )
        LEFT JOIN users account ON account.id = COALESCE(event.user_id, identified_event.user_id)
        LEFT JOIN domains domain ON domain.id = event.domain_id
        LEFT JOIN (
          SELECT user_id, MAX(id) AS domain_id
          FROM domains
          GROUP BY user_id
        ) latest_domain ON latest_domain.user_id = account.id
        LEFT JOIN domains owner_domain ON owner_domain.id = latest_domain.domain_id
        LEFT JOIN marketing_funnel_events landing_event ON landing_event.id = (
          SELECT source.id
          FROM marketing_funnel_events source
          WHERE source.event_type = 'landing_view'
            AND source.visitor_id = event.visitor_id
            AND source.occurred_at <= event.occurred_at
          ORDER BY source.occurred_at DESC, source.id DESC
          LIMIT 1
        )
        ORDER BY actor.last_occurred_at DESC, actor.latest_event_id DESC
      `,
      params,
    ),
  ]);
  const summary = summaryResult[0][0];
  const actorRows = actorResult[0];

  return {
    actorCount: Number(summary?.actors ?? 0),
    actors: actorRows.slice(0, 200).map((row) => ({
      channel: getMarketingChannel({
        referrerHost: row.referrer_host ?? "",
        utmSource: row.utm_source ?? "",
        utmMedium: row.utm_medium ?? "",
        browserName: row.browser_name,
        userAgent: row.user_agent,
      }),
      companyName: row.company_name,
      country: row.country_code,
      domain: row.domain,
      email: row.user_email,
      eventCount: Number(row.event_count ?? 0),
      firstOccurredAt: toIsoString(row.first_occurred_at),
      id: row.actor_key,
      landingPath: row.landing_path,
      lastOccurredAt: toIsoString(row.last_occurred_at),
      referrer: [row.referrer_host, row.referrer_path].filter(Boolean).join("") || null,
      visitorId: row.visitor_id,
    })),
    detail,
    eventCount: Number(summary?.events ?? 0),
    eventType,
    rangeDays,
    truncated: actorRows.length > 200,
  };
}

export async function recordMarketingLandingView(input: {
  sourceApp?: string | null;
  acceptLanguage?: string | null;
  clientIp?: string | null;
  countryCode?: string | null;
  gaClientId?: unknown;
  gaSessionId?: unknown;
  landingPath: string;
  referrerHost?: string | null;
  referrerPath?: string | null;
  screenHeight?: number | null;
  screenWidth?: number | null;
  timezone?: string | null;
  utmCampaign?: string | null;
  utmContent?: string | null;
  utmMedium?: string | null;
  utmSource?: string | null;
  utmTerm?: string | null;
  userAgent?: string | null;
  requestedWith?: string | null;
  visitorId: string;
  viewportHeight?: number | null;
  viewportWidth?: number | null;
}) {
  const visitorId = normalizeVisitorId(input.visitorId);
  const landingPath = normalizeMarketingLandingPath(input.landingPath);

  if (!visitorId || !landingPath) {
    return false;
  }

  try {
    await ensureMarketingAnalyticsSchema();
    const userAgent = cleanText(input.userAgent, 512);
    const attribution = normalizeMarketingAttribution({
      sourceApp: input.sourceApp ?? "",
      referrerHost: input.referrerHost ?? "",
      referrerPath: input.referrerPath ?? "",
      utmSource: input.utmSource ?? "",
      utmMedium: input.utmMedium ?? "",
      utmCampaign: input.utmCampaign ?? "",
      utmContent: input.utmContent ?? "",
      utmTerm: input.utmTerm ?? "",
    });
    await getDbPool().query(
      `
        INSERT INTO marketing_funnel_events (
          event_type,
          visitor_id,
          ga_client_id,
          ga_session_id,
          landing_path,
          referrer_host,
          referrer_path,
          utm_source,
          utm_medium,
          utm_campaign,
          utm_content,
          utm_term,
          client_ip_hash,
          client_ip,
          client_ip_prefix,
          country_code,
          user_agent,
          device_type,
          browser_name,
          os_name,
          language,
          timezone,
          screen_width,
          screen_height,
          viewport_width,
          viewport_height
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `,
      [
        isMarketingAppPath(landingPath) ? "app_view" : "landing_view",
        visitorId,
        normalizeGoogleAnalyticsClientId(input.gaClientId),
        normalizeGoogleAnalyticsSessionId(input.gaSessionId),
        landingPath,
        attribution.referrerHost || null,
        attribution.referrerPath || null,
        attribution.utmSource || null,
        attribution.utmMedium || null,
        attribution.utmCampaign || null,
        attribution.utmContent || null,
        attribution.utmTerm || null,
        getClientIpHash(input.clientIp),
        normalizePublicClientIp(input.clientIp),
        getPublicClientNetwork(input.clientIp),
        normalizeCountryCode(input.countryCode),
        userAgent,
        detectDeviceType(userAgent),
        detectMarketingApp(null, input.requestedWith) || attribution.sourceApp || detectMarketingApp(userAgent) || detectBrowserName(userAgent),
        detectOsName(userAgent),
        cleanText(input.acceptLanguage, 32),
        cleanText(input.timezone, 64),
        normalizeBoundedNumber(input.screenWidth, 32767),
        normalizeBoundedNumber(input.screenHeight, 32767),
        normalizeBoundedNumber(input.viewportWidth, 32767),
        normalizeBoundedNumber(input.viewportHeight, 32767),
      ],
    );
    return true;
  } catch (error) {
    console.error("Marketing landing tracking failed", error);
    return false;
  }
}

export async function pruneExpiredMarketingAnalytics() {
  await ensureMarketingAnalyticsSchema();
  const [result] = await getDbPool().query<ResultSetHeader>(
    `
      DELETE FROM marketing_funnel_events
      WHERE occurred_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 14 MONTH)
    `,
  );

  return result.affectedRows;
}

export async function recordMarketingLifecycleEvent(input: {
  currency?: string | null;
  domainId?: number | null;
  eventReference?: string | null;
  eventType: MarketingLifecycleEventType;
  ownerEmail?: string | null;
  userId?: number | null;
  value?: number | null;
  visitorId?: string | null;
}) {
  if (!isLifecycleEventType(input.eventType)) {
    return false;
  }

  try {
    await ensureMarketingAnalyticsSchema();
    let userId = normalizePositiveId(input.userId);

    if (!userId && input.ownerEmail?.trim()) {
      const [users] = await getDbPool().query<
        (RowDataPacket & { id: number })[]
      >(
        "SELECT id FROM users WHERE email = ? LIMIT 1",
        [input.ownerEmail.trim().toLowerCase()],
      );
      userId = normalizePositiveId(users[0]?.id);
    }

    if (!userId) {
      return false;
    }

    let visitorId = normalizeVisitorId(input.visitorId);

    if (!visitorId && input.eventType !== "signup_completed") {
      const [attributionRows] = await getDbPool().query<
        (RowDataPacket & { visitor_id: string | null })[]
      >(
        `
          SELECT visitor_id
          FROM marketing_funnel_events
          WHERE event_type = 'signup_completed'
            AND user_id = ?
            AND visitor_id IS NOT NULL
            AND visitor_id <> ''
          ORDER BY occurred_at DESC, id DESC
          LIMIT 1
        `,
        [userId],
      );
      visitorId = normalizeVisitorId(attributionRows[0]?.visitor_id);
    }

    let gaClientId: string | null = null;
    let gaSessionId: string | null = null;
    if (visitorId) {
      const [analyticsRows] = await getDbPool().query<
        (RowDataPacket & { ga_client_id: string | null; ga_session_id: string | null })[]
      >(
        `
          SELECT ga_client_id, ga_session_id
          FROM marketing_funnel_events
          WHERE visitor_id = ?
            AND ga_client_id IS NOT NULL
            AND ga_session_id IS NOT NULL
          ORDER BY occurred_at DESC, id DESC
          LIMIT 1
        `,
        [visitorId],
      );
      gaClientId = normalizeGoogleAnalyticsClientId(analyticsRows[0]?.ga_client_id);
      gaSessionId = normalizeGoogleAnalyticsSessionId(analyticsRows[0]?.ga_session_id);
    }

    const domainId = normalizePositiveId(input.domainId);
    const eventReference = cleanText(input.eventReference, 191) || null;
    const currency = /^[A-Z]{3}$/.test(input.currency?.trim().toUpperCase() ?? "")
      ? input.currency!.trim().toUpperCase()
      : null;
    const value = Number.isFinite(input.value) && Number(input.value) >= 0
      ? Number(input.value)
      : null;
    let inserted = true;

    if (input.eventType === "signup_completed") {
      const [result] = await getDbPool().query<ResultSetHeader>(
        `
          INSERT INTO marketing_funnel_events (
            event_type,
            visitor_id,
            ga_client_id,
            ga_session_id,
            user_id,
            domain_id,
            event_reference,
            event_value,
            event_currency
          )
          SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?
          WHERE NOT EXISTS (
            SELECT 1
            FROM marketing_funnel_events
            WHERE event_type = 'signup_completed'
              AND user_id = ?
          )
        `,
        [input.eventType, visitorId, gaClientId, gaSessionId, userId, domainId, eventReference, value, currency, userId],
      );
      inserted = result.affectedRows > 0;
    } else if (eventReference) {
      const [result] = await getDbPool().query<ResultSetHeader>(
        `
          INSERT IGNORE INTO marketing_funnel_events (
            event_type, visitor_id, ga_client_id, ga_session_id, user_id, domain_id,
            event_reference, event_value, event_currency
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        `,
        [input.eventType, visitorId, gaClientId, gaSessionId, userId, domainId, eventReference, value, currency],
      );
      inserted = result.affectedRows > 0;
    } else {
      await getDbPool().query(
        `
          INSERT INTO marketing_funnel_events (
            event_type,
            visitor_id,
            ga_client_id,
            ga_session_id,
            user_id,
            domain_id,
            event_value,
            event_currency
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        `,
        [input.eventType, visitorId, gaClientId, gaSessionId, userId, domainId, value, currency],
      );
    }

    if (inserted) {
      void sendGoogleAnalyticsLifecycleEvent({
        eventType: input.eventType,
        eventReference,
        gaClientId,
        gaSessionId,
        currency,
        userId,
        value,
        visitorId,
      });
    }
    return true;
  } catch (error) {
    console.error("Marketing lifecycle tracking failed", error);
    return false;
  }
}

export async function recordMarketingInteractionEvent(input: {
  eventDetail?: string | null;
  eventType: MarketingInteractionEventType;
  ownerEmail?: string | null;
  userId?: number | null;
  visitorId?: string | null;
}) {
  if (!isMarketingInteractionEventType(input.eventType)) return false;

  try {
    await ensureMarketingAnalyticsSchema();
    let userId = normalizePositiveId(input.userId);
    if (!userId && input.ownerEmail?.trim()) {
      const [users] = await getDbPool().query<
        (RowDataPacket & { id: number })[]
      >("SELECT id FROM users WHERE email = ? LIMIT 1", [
        input.ownerEmail.trim().toLowerCase(),
      ]);
      userId = normalizePositiveId(users[0]?.id);
    }
    let visitorId = normalizeVisitorId(input.visitorId);

    if (!visitorId && userId) {
      const [signupRows] = await getDbPool().query<
        (RowDataPacket & { visitor_id: string | null })[]
      >(
        `
          SELECT visitor_id
          FROM marketing_funnel_events
          WHERE event_type = 'signup_completed' AND user_id = ?
          ORDER BY occurred_at DESC, id DESC
          LIMIT 1
        `,
        [userId],
      );
      visitorId = normalizeVisitorId(signupRows[0]?.visitor_id);
    }

    if (!visitorId && !userId) return false;

    let gaClientId: string | null = null;
    let gaSessionId: string | null = null;
    if (visitorId) {
      const [analyticsRows] = await getDbPool().query<
        (RowDataPacket & { ga_client_id: string | null; ga_session_id: string | null })[]
      >(
        `
          SELECT ga_client_id, ga_session_id
          FROM marketing_funnel_events
          WHERE visitor_id = ?
            AND ga_client_id IS NOT NULL
            AND ga_session_id IS NOT NULL
          ORDER BY occurred_at DESC, id DESC
          LIMIT 1
        `,
        [visitorId],
      );
      gaClientId = normalizeGoogleAnalyticsClientId(analyticsRows[0]?.ga_client_id);
      gaSessionId = normalizeGoogleAnalyticsSessionId(analyticsRows[0]?.ga_session_id);
    }

    const eventDetail = normalizeInteractionDetail(input.eventDetail);
    const shouldDedupe = [
      "signup_form_viewed",
      "dns_setup_viewed",
      "dns_wizard_step_viewed",
    ].includes(input.eventType);
    const [result] = await getDbPool().query<ResultSetHeader>(
      shouldDedupe
        ? `
            INSERT INTO marketing_funnel_events (
              event_type, visitor_id, ga_client_id, ga_session_id, user_id, event_detail
            )
            SELECT ?, ?, ?, ?, ?, ?
            WHERE NOT EXISTS (
              SELECT 1
              FROM marketing_funnel_events
              WHERE event_type = ?
                AND event_detail <=> ?
                AND occurred_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 MINUTE)
                AND (
                  (? IS NOT NULL AND user_id = ?)
                  OR (? IS NOT NULL AND visitor_id = ?)
                )
            )
          `
        : `
            INSERT INTO marketing_funnel_events (
              event_type, visitor_id, ga_client_id, ga_session_id, user_id, event_detail
            ) VALUES (?, ?, ?, ?, ?, ?)
          `,
      shouldDedupe
        ? [
            input.eventType,
            visitorId,
            gaClientId,
            gaSessionId,
            userId,
            eventDetail,
            input.eventType,
            eventDetail,
            userId,
            userId,
            visitorId,
            visitorId,
          ]
        : [input.eventType, visitorId, gaClientId, gaSessionId, userId, eventDetail],
    );
    if (shouldDedupe && result.affectedRows === 0) return true;
    void sendGoogleAnalyticsLifecycleEvent({
      eventDetail,
      eventType: input.eventType,
      gaClientId,
      gaSessionId,
      userId,
      visitorId,
    });
    return true;
  } catch (error) {
    console.error("Marketing interaction tracking failed", error);
    return false;
  }
}

function marketingChannelSql() {
  const platforms = MARKETING_PLATFORMS.filter((item) => item.hosts.length).map((item) =>
    `WHEN ${item.hosts.map((host) => `(LOWER(referrer_host) = '${host}' OR LOWER(referrer_host) LIKE '%.${host}')`).join(" OR ")} THEN '${item.label}'`).join("\n");
  const apps = MARKETING_PLATFORMS.map((item) =>
    `WHEN browser_name = '${item.browser}' THEN '${item.label} (인앱 추정)'`).join("\n");
  const agents = MARKETING_PLATFORMS.map((item) =>
    `WHEN LOWER(user_agent) REGEXP '${item.agent}' THEN '${item.label} (인앱 추정)'`).join("\n");
  const internal = `LOWER(SUBSTRING_INDEX(referrer_host, ':', 1)) IN ('officialsite.kr', 'localhost', '127.0.0.1')
    OR LOWER(SUBSTRING_INDEX(referrer_host, ':', 1)) LIKE '%.officialsite.kr'`;
  return `
    COALESCE(
      NULLIF(CONCAT_WS(' / ', NULLIF(utm_source, ''), NULLIF(utm_medium, '')), ''),
      CASE ${platforms} ELSE NULL END,
      CASE WHEN ${internal} THEN NULL ELSE NULLIF(referrer_host, '') END,
      CASE ${apps} ELSE NULL END,
      CASE ${agents} ELSE NULL END,
      CASE WHEN ${internal} THEN '${INTERNAL_MARKETING_CHANNEL}' ELSE NULL END,
      '${UNKNOWN_MARKETING_CHANNEL}'
    )
  `;
}

function buildMarketingDrilldownFilter(drilldown: KavenixMarketingDrilldown) {
  if (drilldown.dimension === "page") {
    return {
      params: [drilldown.value],
      sql: "landing_path = ?",
    };
  }

  return {
    params: [drilldown.value],
    sql: `${marketingChannelSql()} = ?`,
  };
}

async function getKavenixMarketingFunnelDrilldown(input: {
  from: Date;
  step: KavenixMarketingFunnelStep;
  page?: unknown;
  pageSize?: unknown;
}): Promise<NonNullable<KavenixMarketingAnalytics["drilldown"]>> {
  const step = MARKETING_FUNNEL_STEP_DETAILS[input.step];
  const [countRows] = await getDbPool().query<(RowDataPacket & { total: number })[]>(
    "SELECT COUNT(*) AS total FROM marketing_funnel_events WHERE event_type = ? AND occurred_at >= ?", [step.eventType, input.from],
  );
  const pagination = getMarketingPagination(Number(countRows[0]?.total ?? 0), input.page, input.pageSize);

  const [metricRows, dailyRows, audienceRows] = await Promise.all([
    getDbPool().query<MarketingDrilldownMetricRow[]>(
      `
        SELECT
          COUNT(DISTINCT CASE WHEN event_type = 'landing_view' THEN visitor_id END) AS visitors,
          COUNT(DISTINCT CASE WHEN event_type = 'landing_view' AND ${publicClientIpSql("client_ip")} THEN client_ip_hash END) AS networks,
          COUNT(DISTINCT CASE WHEN event_type = 'signup_completed' THEN user_id END) AS signups,
          COUNT(DISTINCT CASE WHEN event_type = 'domain_verified' THEN user_id END) AS domain_verified,
          COUNT(DISTINCT CASE WHEN event_type = 'growth_plan_started' THEN user_id END) AS growth_plan_started
        FROM marketing_funnel_events
        WHERE occurred_at >= ?
      `,
      [input.from],
    ),
    getDbPool().query<MarketingDrilldownDailyRow[]>(
      `
        SELECT
          DATE_FORMAT(occurred_at, '%m/%d') AS day,
          COUNT(DISTINCT CASE WHEN event_type = 'landing_view' THEN visitor_id END) AS visitors,
          COUNT(DISTINCT CASE WHEN event_type = 'signup_completed' THEN user_id END) AS signups,
          COUNT(DISTINCT CASE WHEN event_type = 'domain_verified' THEN user_id END) AS domain_verified,
          COUNT(DISTINCT CASE WHEN event_type = 'growth_plan_started' THEN user_id END) AS growth_plan_started
        FROM marketing_funnel_events
        WHERE occurred_at >= ?
        GROUP BY DATE(occurred_at), day
        ORDER BY DATE(occurred_at) ASC
      `,
      [input.from],
    ),
    getDbPool().query<MarketingAudienceRow[]>(
      `
        SELECT
          COALESCE(event.client_ip, landing_event.client_ip) AS client_ip,
          COALESCE(event.client_ip_prefix, landing_event.client_ip_prefix) AS client_ip_prefix,
          COALESCE(event.country_code, landing_event.country_code) AS country_code,
          COALESCE(event.landing_path, landing_event.landing_path) AS landing_path,
          event.occurred_at,
          COALESCE(event.referrer_host, landing_event.referrer_host) AS referrer_host,
          COALESCE(event.referrer_path, landing_event.referrer_path) AS referrer_path,
          COALESCE(event.utm_source, landing_event.utm_source) AS utm_source,
          COALESCE(event.utm_medium, landing_event.utm_medium) AS utm_medium,
          COALESCE(event.user_agent, landing_event.user_agent) AS user_agent,
          COALESCE(event.browser_name, landing_event.browser_name) AS browser_name,
          event.visitor_id,
          account.email AS user_email,
          account.company_name,
          COALESCE(domain.domain, owner_domain.domain) AS domain
        FROM (
          SELECT * FROM marketing_funnel_events
          WHERE event_type = ? AND occurred_at >= ?
          ORDER BY occurred_at DESC, id DESC
          LIMIT ? OFFSET ?
        ) event
        LEFT JOIN users account ON account.id = event.user_id
        LEFT JOIN domains domain ON domain.id = event.domain_id
        LEFT JOIN (
          SELECT user_id, MAX(id) AS domain_id
          FROM domains
          GROUP BY user_id
        ) latest_domain ON latest_domain.user_id = event.user_id
        LEFT JOIN domains owner_domain ON owner_domain.id = latest_domain.domain_id
        LEFT JOIN marketing_funnel_events landing_event ON landing_event.id = (
          SELECT source.id
          FROM marketing_funnel_events source
          WHERE source.event_type = 'landing_view'
            AND source.visitor_id = event.visitor_id
            AND source.occurred_at <= event.occurred_at
          ORDER BY source.occurred_at DESC, source.id DESC
          LIMIT 1
        )
        ORDER BY event.occurred_at DESC, event.id DESC
      `,
      [step.eventType, input.from, pagination.pageSize, (pagination.page - 1) * pagination.pageSize],
    ),
  ]);
  const metric = metricRows[0][0];

  return {
    pagination,
    audience: audienceRows[0].map((row) => ({
      channel: getMarketingChannel({ referrerHost: row.referrer_host ?? "", utmSource: row.utm_source ?? "", utmMedium: row.utm_medium ?? "", browserName: row.browser_name, userAgent: row.user_agent }),
      companyName: row.company_name,
      country: row.country_code,
      domain: row.domain,
      email: row.user_email,
      ...getStoredClientIp(row.client_ip, row.client_ip_prefix),
      landingPath: row.landing_path,
      occurredAt: toIsoString(row.occurred_at),
      referrer: [row.referrer_host, row.referrer_path].filter(Boolean).join("") || null,
      visitorId: row.visitor_id,
    })),
    audienceDescription: step.audienceDescription,
    audienceTitle: step.audienceTitle,
    breakdown: [],
    devices: [],
    daily: dailyRows[0].map((row) => ({
      day: row.day,
      domainVerified: Number(row.domain_verified ?? 0),
      growthPlanStarted: Number(row.growth_plan_started ?? 0),
      signups: Number(row.signups ?? 0),
      visitors: Number(row.visitors ?? 0),
    })),
    dimension: "funnel",
    label: step.label,
    metrics: {
      domainVerified: Number(metric?.domain_verified ?? 0),
      growthPlanStarted: Number(metric?.growth_plan_started ?? 0),
      landingVisitors: Number(metric?.visitors ?? 0),
      networks: Number(metric?.networks ?? 0),
      signups: Number(metric?.signups ?? 0),
    },
    recentVisits: [],
    referrers: [],
    value: input.step,
  };
}

async function getKavenixMarketingDrilldown(input: {
  drilldown: KavenixMarketingDrilldown;
  from: Date;
  page?: unknown;
  pageSize?: unknown;
}) {
  if (input.drilldown.dimension === "funnel") {
    return getKavenixMarketingFunnelDrilldown({
      from: input.from,
      step: input.drilldown.value as KavenixMarketingFunnelStep,
      page: input.page,
      pageSize: input.pageSize,
    });
  }

  const filter = buildMarketingDrilldownFilter(input.drilldown);
  const viewEvent = input.drilldown.dimension === "page" && isMarketingAppPath(input.drilldown.value) ? "app_view" : "landing_view";
  const [countRows] = await getDbPool().query<(RowDataPacket & { total: number })[]>(
    `SELECT COUNT(*) AS total FROM marketing_funnel_events WHERE event_type = ?
      AND visitor_id IS NOT NULL AND visitor_id <> '' AND occurred_at >= ? AND ${filter.sql}`,
    [viewEvent, input.from, ...filter.params],
  );
  const pagination = getMarketingPagination(Number(countRows[0]?.total ?? 0), input.page, input.pageSize);
  const breakdownLabelSql =
    input.drilldown.dimension === "page" ? marketingChannelSql() : "landing_path";
  const [metricRows, dailyRows, breakdownRows, deviceRows, referrerRows, recentVisitRows] = await Promise.all([
    getDbPool().query<MarketingDrilldownMetricRow[]>(
      `
        SELECT
          COUNT(DISTINCT cohort.visitor_id) AS visitors,
          COUNT(DISTINCT CASE WHEN ${publicClientIpSql("cohort.client_ip")} THEN cohort.client_ip_hash END) AS networks,
          COUNT(DISTINCT CASE WHEN conversion_event.event_type = 'signup_completed' THEN conversion_event.user_id END) AS signups,
          COUNT(DISTINCT CASE WHEN conversion_event.event_type = 'domain_verified' THEN conversion_event.user_id END) AS domain_verified,
          COUNT(DISTINCT CASE WHEN conversion_event.event_type = 'growth_plan_started' THEN conversion_event.user_id END) AS growth_plan_started
        FROM (
          SELECT DISTINCT visitor_id, client_ip_hash, client_ip
          FROM marketing_funnel_events
          WHERE event_type = '${viewEvent}'
            AND visitor_id IS NOT NULL
            AND visitor_id <> ''
            AND occurred_at >= ?
            AND ${filter.sql}
        ) cohort
        LEFT JOIN marketing_funnel_events conversion_event
          ON conversion_event.visitor_id = cohort.visitor_id
         AND conversion_event.occurred_at >= ?
      `,
      [input.from, ...filter.params, input.from],
    ),
    getDbPool().query<MarketingDrilldownDailyRow[]>(
      `
        SELECT
          DATE_FORMAT(landing_event.occurred_at, '%m/%d') AS day,
          COUNT(DISTINCT landing_event.visitor_id) AS visitors,
          COUNT(DISTINCT CASE WHEN conversion_event.event_type = 'signup_completed' THEN conversion_event.user_id END) AS signups,
          COUNT(DISTINCT CASE WHEN conversion_event.event_type = 'domain_verified' THEN conversion_event.user_id END) AS domain_verified,
          COUNT(DISTINCT CASE WHEN conversion_event.event_type = 'growth_plan_started' THEN conversion_event.user_id END) AS growth_plan_started
        FROM marketing_funnel_events landing_event
        LEFT JOIN marketing_funnel_events conversion_event
          ON conversion_event.visitor_id = landing_event.visitor_id
         AND conversion_event.occurred_at >= ?
        WHERE landing_event.event_type = '${viewEvent}'
          AND landing_event.visitor_id IS NOT NULL
          AND landing_event.visitor_id <> ''
          AND landing_event.occurred_at >= ?
          AND ${filter.sql.replaceAll("landing_path", "landing_event.landing_path").replaceAll("utm_", "landing_event.utm_").replaceAll("referrer_host", "landing_event.referrer_host").replaceAll("browser_name", "landing_event.browser_name").replaceAll("user_agent", "landing_event.user_agent")}
        GROUP BY DATE(landing_event.occurred_at), day
        ORDER BY DATE(landing_event.occurred_at) ASC
      `,
      [input.from, input.from, ...filter.params],
    ),
    getDbPool().query<MarketingTrafficRow[]>(
      `
        SELECT ${breakdownLabelSql} AS label, COUNT(DISTINCT visitor_id) AS visitors
        FROM marketing_funnel_events
        WHERE event_type = '${viewEvent}'
          AND visitor_id IS NOT NULL
          AND visitor_id <> ''
          AND occurred_at >= ?
          AND ${filter.sql}
        GROUP BY label
        ORDER BY visitors DESC, label ASC
        LIMIT 8
      `,
      [input.from, ...filter.params],
    ),
    getDbPool().query<MarketingTrafficRow[]>(
      `
        SELECT COALESCE(NULLIF(device_type, ''), '알 수 없음') AS label, COUNT(DISTINCT visitor_id) AS visitors
        FROM marketing_funnel_events
        WHERE event_type = '${viewEvent}'
          AND visitor_id IS NOT NULL
          AND visitor_id <> ''
          AND occurred_at >= ?
          AND ${filter.sql}
        GROUP BY label
        ORDER BY visitors DESC, label ASC
        LIMIT 6
      `,
      [input.from, ...filter.params],
    ),
    getDbPool().query<MarketingTrafficRow[]>(
      `
        SELECT
          COALESCE(
            NULLIF(CONCAT(COALESCE(referrer_host, ''), COALESCE(referrer_path, '')), ''),
            '리퍼러 미전달'
          ) AS label,
          COUNT(DISTINCT visitor_id) AS visitors
        FROM marketing_funnel_events
        WHERE event_type = '${viewEvent}'
          AND visitor_id IS NOT NULL
          AND visitor_id <> ''
          AND occurred_at >= ?
          AND ${filter.sql}
        GROUP BY label
        ORDER BY visitors DESC, label ASC
        LIMIT 6
      `,
      [input.from, ...filter.params],
    ),
    getDbPool().query<MarketingRecentVisitRow[]>(
      `
        SELECT
          landing_path,
          referrer_host,
          referrer_path,
          utm_source,
          utm_medium,
          client_ip,
          client_ip_prefix,
          country_code,
          device_type,
          browser_name,
          user_agent,
          os_name,
          language,
          timezone,
          viewport_width,
          viewport_height,
          occurred_at
        FROM marketing_funnel_events
        WHERE event_type = '${viewEvent}'
          AND visitor_id IS NOT NULL
          AND visitor_id <> ''
          AND occurred_at >= ?
          AND ${filter.sql}
        ORDER BY occurred_at DESC, id DESC
        LIMIT ? OFFSET ?
      `,
      [input.from, ...filter.params, pagination.pageSize, (pagination.page - 1) * pagination.pageSize],
    ),
  ]);
  const metric = metricRows[0][0];

  return {
    pagination,
    audience: [],
    audienceDescription: "",
    audienceTitle: "",
    breakdown: breakdownRows[0].map((row) => ({
      label: row.label || "직접 방문",
      visitors: Number(row.visitors ?? 0),
    })),
    devices: deviceRows[0].map((row) => ({
      label: row.label || "알 수 없음",
      visitors: Number(row.visitors ?? 0),
    })),
    daily: dailyRows[0].map((row) => ({
      day: row.day,
      domainVerified: Number(row.domain_verified ?? 0),
      growthPlanStarted: Number(row.growth_plan_started ?? 0),
      signups: Number(row.signups ?? 0),
      visitors: Number(row.visitors ?? 0),
    })),
    dimension: input.drilldown.dimension,
    label: input.drilldown.dimension === "page" ? `랜딩 페이지 ${input.drilldown.value}` : `유입 경로 ${input.drilldown.value}`,
    metrics: {
      domainVerified: Number(metric?.domain_verified ?? 0),
      growthPlanStarted: Number(metric?.growth_plan_started ?? 0),
      landingVisitors: Number(metric?.visitors ?? 0),
      networks: Number(metric?.networks ?? 0),
      signups: Number(metric?.signups ?? 0),
    },
    referrers: referrerRows[0].map((row) => ({
      label: row.label || "직접 방문",
      visitors: Number(row.visitors ?? 0),
    })),
    recentVisits: recentVisitRows[0].map((row) => ({
      channel: getMarketingChannel({ referrerHost: row.referrer_host ?? "", utmSource: row.utm_source ?? "", utmMedium: row.utm_medium ?? "", browserName: row.browser_name, userAgent: row.user_agent }),
      browser: row.browser_name,
      country: row.country_code,
      device: row.device_type,
      ...getStoredClientIp(row.client_ip, row.client_ip_prefix),
      landingPath: row.landing_path || "/",
      language: row.language,
      network: getPublicClientNetwork(row.client_ip),
      occurredAt: toIsoString(row.occurred_at),
      operatingSystem: row.os_name,
      referrer: [row.referrer_host, row.referrer_path].filter(Boolean).join("") || null,
      timezone: row.timezone,
      viewport:
        row.viewport_width && row.viewport_height
          ? `${row.viewport_width} × ${row.viewport_height}`
          : null,
    })),
    value: input.drilldown.value,
  } satisfies NonNullable<KavenixMarketingAnalytics["drilldown"]>;
}

export async function getKavenixMarketingAnalytics(input?: {
  drilldown?: KavenixMarketingDrilldown | null;
  rangeDays?: string | number | null;
  page?: unknown;
  pageSize?: unknown;
}): Promise<KavenixMarketingAnalytics> {
  await ensureMarketingAnalyticsSchema();

  const rangeDays = normalizeKavenixMarketingRangeDays(input?.rangeDays);
  const from = new Date(Date.now() - rangeDays * 24 * 60 * 60 * 1000);
  const [
    summaryRows,
    channelRows,
    landingRows,
    dailyRows,
    firstEventRows,
    appRows,
    productFunnelRows,
  ] =
    await Promise.all([
      getDbPool().query<MarketingEventSummaryRow[]>(
        `
          SELECT
            event_type,
            COUNT(DISTINCT visitor_id) AS visitor_count,
            COUNT(DISTINCT user_id) AS user_count,
            COUNT(DISTINCT domain_id) AS domain_count
          FROM marketing_funnel_events
          WHERE occurred_at >= ?
          GROUP BY event_type
        `,
        [from],
      ),
      getDbPool().query<MarketingTrafficRow[]>(
        `
          SELECT
            ${marketingChannelSql()} AS label,
            COUNT(DISTINCT visitor_id) AS visitors
          FROM marketing_funnel_events
          WHERE event_type = 'landing_view'
            AND occurred_at >= ?
          GROUP BY label
          ORDER BY visitors DESC, label ASC
          LIMIT 8
        `,
        [from],
      ),
      getDbPool().query<MarketingTrafficRow[]>(
        `
          SELECT landing_path AS label, COUNT(DISTINCT visitor_id) AS visitors
          FROM marketing_funnel_events
          WHERE event_type = 'landing_view'
            AND occurred_at >= ?
          GROUP BY landing_path
          ORDER BY visitors DESC, landing_path ASC
          LIMIT 8
        `,
        [from],
      ),
      getDbPool().query<MarketingDailyVisitorRow[]>(
        `
          SELECT DATE_FORMAT(occurred_at, '%m/%d') AS day, COUNT(DISTINCT visitor_id) AS visitors
          FROM marketing_funnel_events
          WHERE event_type = 'landing_view'
            AND occurred_at >= ?
          GROUP BY DATE(occurred_at), day
          ORDER BY DATE(occurred_at) ASC
        `,
        [from],
      ),
      getDbPool().query<MarketingFirstEventRow[]>(
        "SELECT MIN(occurred_at) AS occurred_at FROM marketing_funnel_events",
      ),
      getDbPool().query<MarketingTrafficRow[]>(
        `SELECT landing_path AS label, COUNT(DISTINCT visitor_id) AS visitors
         FROM marketing_funnel_events WHERE event_type = 'app_view' AND occurred_at >= ?
         GROUP BY landing_path ORDER BY visitors DESC, landing_path ASC`, [from],
      ),
      getDbPool().query<MarketingProductFunnelRow[]>(
        `
          SELECT
            event_type,
            event_detail,
            COUNT(*) AS events,
            COUNT(DISTINCT CASE
              WHEN user_id IS NOT NULL THEN CONCAT('user:', user_id)
              WHEN visitor_id IS NOT NULL THEN CONCAT('visitor:', visitor_id)
              ELSE NULL
            END) AS actors
          FROM marketing_funnel_events
          WHERE occurred_at >= ?
            AND event_type IN (
              'signup_form_viewed',
              'signup_recovery_sent',
              'signup_recovery_verified',
              'signup_submitted',
              'signup_failed',
              'signup_completed',
              'dns_setup_viewed',
              'dns_wizard_step_viewed',
              'dns_record_confirmed',
              'dns_verification_attempted',
              'dns_verification_failed',
              'domain_verified',
              'payment_method_added',
              'growth_plan_started',
              'purchase_completed'
            )
          GROUP BY event_type, event_detail
        `,
        [from],
      ),
    ]);
  const summary = new Map(
    summaryRows[0].map((row) => [row.event_type, row]),
  );
  const drilldown = input?.drilldown ?? null;
  const drilldownData = drilldown
    ? await getKavenixMarketingDrilldown({ drilldown, from, page: input?.page, pageSize: input?.pageSize })
    : null;

  return {
    appPages: appRows[0].map((row) => ({ path: row.label || "/mail", visitors: Number(row.visitors ?? 0) })),
    channels: channelRows[0].map((row) => ({
      label: row.label || "직접 방문",
      visitors: Number(row.visitors ?? 0),
    })),
    dailyVisitors: dailyRows[0].map((row) => ({
      day: row.day,
      visitors: Number(row.visitors ?? 0),
    })),
    landingPages: landingRows[0].map((row) => ({
      path: row.label || "/",
      visitors: Number(row.visitors ?? 0),
    })),
    metrics: {
      domainVerified: Number(summary.get("domain_verified")?.domain_count ?? 0),
      growthPlanStarted: Number(
        summary.get("growth_plan_started")?.user_count ?? 0,
      ),
      landingVisitors: Number(summary.get("landing_view")?.visitor_count ?? 0),
      signups: Number(summary.get("signup_completed")?.user_count ?? 0),
    },
    productFunnel: productFunnelRows[0].map((row) => ({
      actors: Number(row.actors ?? 0),
      detail: row.event_detail || null,
      eventType: row.event_type,
      events: Number(row.events ?? 0),
    })),
    drilldown: drilldownData,
    rangeDays,
    trackingStartedAt: toIsoString(firstEventRows[0][0]?.occurred_at),
  };
}