import "server-only";

import { createHash } from "crypto";
import type { ResultSetHeader, RowDataPacket } from "mysql2/promise";
import { ensureMarketingAnalyticsSchema, getDbPool } from "@/lib/db";

const MARKETING_LIFECYCLE_EVENT_TYPES = [
  "signup_completed",
  "domain_verified",
  "growth_plan_started",
] as const;
const KAVENIX_MARKETING_RANGE_DAYS = [7, 30, 90] as const;
const MAX_VISITOR_ID_LENGTH = 96;

export const MARKETING_VISITOR_COOKIE = "official_mail_marketing_visitor";

export type MarketingEventType =
  | "landing_view"
  | "signup_completed"
  | "domain_verified"
  | "growth_plan_started";
export type MarketingLifecycleEventType =
  (typeof MARKETING_LIFECYCLE_EVENT_TYPES)[number];
export type KavenixMarketingRangeDays =
  (typeof KAVENIX_MARKETING_RANGE_DAYS)[number];
export type KavenixMarketingDrilldownDimension = "channel" | "page";
export type KavenixMarketingDrilldown = {
  dimension: KavenixMarketingDrilldownDimension;
  value: string;
};

export type KavenixMarketingAnalytics = {
  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;
  };
  drilldown: {
    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;
    metrics: {
      domainVerified: number;
      growthPlanStarted: number;
      landingVisitors: number;
      networks: number;
      signups: number;
    };
    referrers: Array<{
      label: string;
      visitors: number;
    }>;
    recentVisits: Array<{
      browser: string | null;
      country: string | null;
      device: string | null;
      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;
};

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 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 & {
  browser_name: 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;
};

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 normalizeReferrerHost(value: unknown) {
  const host = cleanText(value, 191)?.toLowerCase() ?? null;

  if (!host || !/^[a-z0-9.-]+(?::\d{1,5})?$/i.test(host)) {
    return null;
  }

  return host;
}

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 = cleanText(value, 128);
  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 getClientIpPrefix(value: string | null | undefined) {
  const ip = cleanText(value, 128)?.replace(/^::ffff:/i, "");

  if (!ip) {
    return null;
  }

  const ipv4 = ip.match(/^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.\d{1,3}$/);

  if (ipv4) {
    return `${ipv4[1]}.${ipv4[2]}.${ipv4[3]}.0/24`;
  }

  if (ip.includes(":")) {
    return `${ip.split(":").slice(0, 4).join(":")}::/56`;
  }

  return null;
}

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

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

  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 recordMarketingLandingView(input: {
  acceptLanguage?: string | null;
  clientIp?: string | null;
  countryCode?: string | null;
  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;
  visitorId: string;
  viewportHeight?: number | null;
  viewportWidth?: number | null;
}) {
  const visitorId = normalizeVisitorId(input.visitorId);
  const landingPath = normalizeLandingPath(input.landingPath);

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

  try {
    await ensureMarketingAnalyticsSchema();
    const userAgent = cleanText(input.userAgent, 512);
    await getDbPool().query(
      `
        INSERT INTO marketing_funnel_events (
          event_type,
          visitor_id,
          landing_path,
          referrer_host,
          referrer_path,
          utm_source,
          utm_medium,
          utm_campaign,
          utm_content,
          utm_term,
          client_ip_hash,
          client_ip_prefix,
          country_code,
          user_agent,
          device_type,
          browser_name,
          os_name,
          language,
          timezone,
          screen_width,
          screen_height,
          viewport_width,
          viewport_height
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `,
      [
        "landing_view",
        visitorId,
        landingPath,
        normalizeReferrerHost(input.referrerHost),
        cleanText(input.referrerPath, 512),
        cleanText(input.utmSource, 120),
        cleanText(input.utmMedium, 120),
        cleanText(input.utmCampaign, 160),
        cleanText(input.utmContent, 160),
        cleanText(input.utmTerm, 160),
        getClientIpHash(input.clientIp),
        getClientIpPrefix(input.clientIp),
        normalizeCountryCode(input.countryCode),
        userAgent,
        detectDeviceType(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: {
  domainId?: number | null;
  eventType: MarketingLifecycleEventType;
  ownerEmail?: string | null;
  userId?: 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);
    }

    // Direct visits to signup do not pass through a public landing page. Keep
    // them in the funnel instead of leaving completed signups unattributed.
    if (input.eventType === "signup_completed" && visitorId) {
      await getDbPool().query(
        `
          INSERT INTO marketing_funnel_events (event_type, visitor_id, landing_path)
          SELECT 'landing_view', ?, '/signup'
          WHERE NOT EXISTS (
            SELECT 1
            FROM marketing_funnel_events
            WHERE event_type = 'landing_view'
              AND visitor_id = ?
          )
        `,
        [visitorId, visitorId],
      );
    }

    await getDbPool().query(
      `
        INSERT INTO marketing_funnel_events (
          event_type,
          visitor_id,
          user_id,
          domain_id
        ) VALUES (?, ?, ?, ?)
      `,
      [
        input.eventType,
        visitorId,
        userId,
        normalizePositiveId(input.domainId),
      ],
    );
    return true;
  } catch (error) {
    console.error("Marketing lifecycle tracking failed", error);
    return false;
  }
}

function marketingChannelSql() {
  return `
    COALESCE(
      NULLIF(CONCAT_WS(' / ', NULLIF(utm_source, ''), NULLIF(utm_medium, '')), ''),
      NULLIF(referrer_host, ''),
      '직접 방문'
    )
  `;
}

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

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

async function getKavenixMarketingDrilldown(input: {
  drilldown: KavenixMarketingDrilldown;
  from: Date;
}) {
  const filter = buildMarketingDrilldownFilter(input.drilldown);
  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 cohort.client_ip_hash) 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
          FROM marketing_funnel_events
          WHERE event_type = 'landing_view'
            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 = 'landing_view'
          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")}
        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 = 'landing_view'
          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 = 'landing_view'
          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 = 'landing_view'
          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,
          client_ip_prefix,
          country_code,
          device_type,
          browser_name,
          os_name,
          language,
          timezone,
          viewport_width,
          viewport_height,
          occurred_at
        FROM marketing_funnel_events
        WHERE event_type = 'landing_view'
          AND visitor_id IS NOT NULL
          AND visitor_id <> ''
          AND occurred_at >= ?
          AND ${filter.sql}
        ORDER BY occurred_at DESC, id DESC
        LIMIT 20
      `,
      [input.from, ...filter.params],
    ),
  ]);
  const metric = metricRows[0][0];

  return {
    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) => ({
      browser: row.browser_name,
      country: row.country_code,
      device: row.device_type,
      landingPath: row.landing_path || "/",
      language: row.language,
      network: row.client_ip_prefix,
      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;
}): 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] =
    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
            COALESCE(
              NULLIF(CONCAT_WS(' / ', NULLIF(utm_source, ''), NULLIF(utm_medium, '')), ''),
              NULLIF(referrer_host, ''),
              '직접 방문'
            ) 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",
      ),
    ]);
  const summary = new Map(
    summaryRows[0].map((row) => [row.event_type, row]),
  );
  const drilldown = input?.drilldown ?? null;
  const drilldownData = drilldown
    ? await getKavenixMarketingDrilldown({ drilldown, from })
    : null;

  return {
    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),
    },
    drilldown: drilldownData,
    rangeDays,
    trackingStartedAt: toIsoString(firstEventRows[0][0]?.occurred_at),
  };
}