import { NextRequest, NextResponse } from 'next/server';
import { getDb } from '@/lib/db';
import { getAuthenticatedAdmin } from '@/lib/admin-auth';
import { CUISINE_OPTIONS, expandCuisineTags } from '@/lib/cuisine';
import { ensureRestaurantStats } from '@/lib/restaurant-stats';
import { ensureRetentionTracking, getRetentionStats } from '@/lib/retention';
import {
  REPORTING_TZ,
  startOfDayInTz,
  formatDateIsoInTz,
  formatMonthIsoInTz,
  getHourInTz,
  toSqliteUtc,
  parseSqliteUtc,
} from '@/lib/timezone';

function ensureCounters(db: ReturnType<typeof getDb>) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS app_counters (
      key   TEXT PRIMARY KEY,
      value INTEGER NOT NULL DEFAULT 0
    );

    CREATE TRIGGER IF NOT EXISTS trg_cnt_sessions
    AFTER INSERT ON sessions
    BEGIN
      INSERT INTO app_counters(key, value) VALUES ('sessions', 1)
      ON CONFLICT(key) DO UPDATE SET value = value + 1;
    END;

    CREATE TRIGGER IF NOT EXISTS trg_cnt_matches
    AFTER INSERT ON matches
    BEGIN
      INSERT INTO app_counters(key, value) VALUES ('matches', 1)
      ON CONFLICT(key) DO UPDATE SET value = value + 1;
    END;

    CREATE TRIGGER IF NOT EXISTS trg_cnt_swipes
    AFTER INSERT ON swipes
    BEGIN
      INSERT INTO app_counters(key, value) VALUES ('swipes', 1)
      ON CONFLICT(key) DO UPDATE SET value = value + 1;
    END;

    CREATE TRIGGER IF NOT EXISTS trg_cnt_swipes_yes
    AFTER INSERT ON swipes WHEN NEW.direction = 'yes'
    BEGIN
      INSERT INTO app_counters(key, value) VALUES ('swipes_yes', 1)
      ON CONFLICT(key) DO UPDATE SET value = value + 1;
    END;

    CREATE TRIGGER IF NOT EXISTS trg_cnt_participants
    AFTER INSERT ON participants
    BEGIN
      INSERT INTO app_counters(key, value) VALUES ('participants', 1)
      ON CONFLICT(key) DO UPDATE SET value = value + 1;
    END;

    CREATE TRIGGER IF NOT EXISTS trg_cnt_joker
    AFTER INSERT ON joker_draws
    BEGIN
      INSERT INTO app_counters(key, value) VALUES ('joker_draws', 1)
      ON CONFLICT(key) DO UPDATE SET value = value + 1;
    END;

    CREATE TRIGGER IF NOT EXISTS trg_cnt_sessions_with_match
    AFTER INSERT ON matches
    WHEN NOT EXISTS (
      SELECT 1 FROM matches WHERE session_id = NEW.session_id AND id != NEW.id
    )
    BEGIN
      INSERT INTO app_counters(key, value) VALUES ('sessions_with_match', 1)
      ON CONFLICT(key) DO UPDATE SET value = value + 1;
    END;

    CREATE TRIGGER IF NOT EXISTS trg_cnt_outbound_clicks_total
    AFTER INSERT ON outbound_clicks
    BEGIN
      INSERT INTO app_counters(key, value) VALUES ('outbound_clicks_total', 1)
      ON CONFLICT(key) DO UPDATE SET value = value + 1;
    END;

    CREATE TRIGGER IF NOT EXISTS trg_cnt_sessions_with_forward
    AFTER INSERT ON outbound_clicks
    WHEN NOT EXISTS (
      SELECT 1 FROM outbound_clicks WHERE session_id = NEW.session_id AND id != NEW.id
    )
    BEGIN
      INSERT INTO app_counters(key, value) VALUES ('sessions_with_forward', 1)
      ON CONFLICT(key) DO UPDATE SET value = value + 1;
    END;

    CREATE TRIGGER IF NOT EXISTS trg_cnt_outbound_clicks_maps
    AFTER INSERT ON outbound_clicks WHEN NEW.click_type = 'maps'
    BEGIN
      INSERT INTO app_counters(key, value) VALUES ('outbound_clicks_maps', 1)
      ON CONFLICT(key) DO UPDATE SET value = value + 1;
    END;

    CREATE TRIGGER IF NOT EXISTS trg_cnt_outbound_clicks_call
    AFTER INSERT ON outbound_clicks WHEN NEW.click_type = 'call'
    BEGIN
      INSERT INTO app_counters(key, value) VALUES ('outbound_clicks_call', 1)
      ON CONFLICT(key) DO UPDATE SET value = value + 1;
    END;

    CREATE TRIGGER IF NOT EXISTS trg_cnt_outbound_clicks_website
    AFTER INSERT ON outbound_clicks WHEN NEW.click_type = 'website'
    BEGIN
      INSERT INTO app_counters(key, value) VALUES ('outbound_clicks_website', 1)
      ON CONFLICT(key) DO UPDATE SET value = value + 1;
    END;

    CREATE TRIGGER IF NOT EXISTS trg_cnt_outbound_clicks_reservation
    AFTER INSERT ON outbound_clicks WHEN NEW.click_type = 'reservation'
    BEGIN
      INSERT INTO app_counters(key, value) VALUES ('outbound_clicks_reservation', 1)
      ON CONFLICT(key) DO UPDATE SET value = value + 1;
    END;

    CREATE TRIGGER IF NOT EXISTS trg_cnt_outbound_clicks_order
    AFTER INSERT ON outbound_clicks WHEN NEW.click_type = 'order'
    BEGIN
      INSERT INTO app_counters(key, value) VALUES ('outbound_clicks_order', 1)
      ON CONFLICT(key) DO UPDATE SET value = value + 1;
    END;

    CREATE TRIGGER IF NOT EXISTS trg_cnt_outbound_clicks_share
    AFTER INSERT ON outbound_clicks WHEN NEW.click_type = 'share'
    BEGIN
      INSERT INTO app_counters(key, value) VALUES ('outbound_clicks_share', 1)
      ON CONFLICT(key) DO UPDATE SET value = value + 1;
    END;

    CREATE INDEX IF NOT EXISTS idx_sessions_created ON sessions(created_at);
    CREATE INDEX IF NOT EXISTS idx_matches_created ON matches(created_at);
    CREATE INDEX IF NOT EXISTS idx_swipes_created ON swipes(created_at);
  `);

}

function counterMap(db: ReturnType<typeof getDb>): Record<string, number> {
  const rows = db.prepare('SELECT key, value FROM app_counters').all() as {
    key: string;
    value: number;
  }[];
  const map: Record<string, number> = {};
  for (const r of rows) map[r.key] = r.value;
  return map;
}

function topCuisineMatches(db: ReturnType<typeof getDb>, since: Date) {
  const rows = db
    .prepare(
      `SELECT r.cuisine AS cuisine
       FROM matches m
       JOIN restaurants r ON r.id = m.restaurant_id
       WHERE m.created_at >= ? AND r.cuisine IS NOT NULL AND r.cuisine != ''`,
    )
    .all(toSqliteUtc(since)) as { cuisine: string }[];

  const counts: Record<string, number> = {};
  for (const opt of CUISINE_OPTIONS) {
    const aliases = expandCuisineTags([opt.value]);
    let count = 0;
    for (const row of rows) {
      const haystack = row.cuisine.toLowerCase();
      if (aliases.some((a) => haystack.includes(a))) count++;
    }
    if (count > 0) counts[opt.value] = count;
  }

  return Object.entries(counts)
    .map(([value, cnt]) => ({
      value,
      label: CUISINE_OPTIONS.find((o) => o.value === value)?.label ?? value,
      cnt,
    }))
    .sort((a, b) => b.cnt - a.cnt)
    .slice(0, 8);
}

function topCityMatches(db: ReturnType<typeof getDb>, since: Date) {
  return db
    .prepare(
      `SELECT s.city AS city, COUNT(*) AS cnt
       FROM matches m
       JOIN sessions s ON s.id = m.session_id
       WHERE m.created_at >= ? AND s.city IS NOT NULL AND s.city != ''
       GROUP BY s.city
       ORDER BY cnt DESC
       LIMIT 8`,
    )
    .all(toSqliteUtc(since)) as { city: string; cnt: number }[];
}

function topCitySessions(db: ReturnType<typeof getDb>, since: Date) {
  return db
    .prepare(
      `SELECT city, COUNT(*) AS cnt
       FROM sessions
       WHERE created_at >= ? AND city IS NOT NULL AND city != ''
       GROUP BY city
       ORDER BY cnt DESC
       LIMIT 8`,
    )
    .all(toSqliteUtc(since)) as { city: string; cnt: number }[];
}

function funnelByRange(db: ReturnType<typeof getDb>, since: Date) {
  const sinceStr = toSqliteUtc(since);
  const started = (
    db.prepare(`SELECT COUNT(*) AS c FROM sessions WHERE created_at >= ?`).get(sinceStr) as { c: number }
  ).c;
  const withMatch = (
    db
      .prepare(`SELECT COUNT(DISTINCT session_id) AS c FROM matches WHERE created_at >= ?`)
      .get(sinceStr) as { c: number }
  ).c;
  const forwards = (
    db
      .prepare(`SELECT COUNT(DISTINCT session_id) AS c FROM outbound_clicks WHERE created_at >= ?`)
      .get(sinceStr) as { c: number }
  ).c;

  return {
    started,
    withMatch,
    forwards,
    withMatchPct: started > 0 ? Math.round((withMatch / started) * 1000) / 10 : 0,
    forwardsPct: started > 0 ? Math.round((forwards / started) * 1000) / 10 : 0,
  };
}

function outboundClicksByRange(db: ReturnType<typeof getDb>, since: Date) {
  const clickTypeLabels: Record<string, string> = {
    maps: 'Route/Maps',
    call: 'Anrufen',
    website: 'Webseite',
    reservation: 'Reservieren',
    order: 'Bestellen',
    share: 'Teilen',
  };

  const rows = db
    .prepare(
      `SELECT click_type, COUNT(*) AS cnt
       FROM outbound_clicks
       WHERE created_at >= ?
       GROUP BY click_type
       ORDER BY cnt DESC`,
    )
    .all(toSqliteUtc(since)) as { click_type: string; cnt: number }[];

  return {
    total: rows.reduce((sum, r) => sum + r.cnt, 0),
    byType: rows
      .map((r) => ({ type: r.click_type, label: clickTypeLabels[r.click_type] ?? r.click_type, cnt: r.cnt }))
      .filter((t) => t.cnt > 0)
      .sort((a, b) => b.cnt - a.cnt),
  };
}

function getMonthLabelsForLastYear(anchor: Date) {
  const labels: string[] = [];
  for (let i = 11; i >= 0; i--) {
    const d = new Date(anchor.getFullYear(), anchor.getMonth() - i, 1);
    labels.push(formatMonthIsoInTz(d, REPORTING_TZ));
  }
  return labels;
}

function buildDateRangeLabels(days: number, anchor: Date): string[] {
  const labels: string[] = [];
  for (let i = days - 1; i >= 0; i--) {
    const d = new Date(anchor.getTime() - i * 24 * 60 * 60 * 1000);
    labels.push(formatDateIsoInTz(d, REPORTING_TZ));
  }
  return labels;
}

function dailyTrend(db: ReturnType<typeof getDb>, days: number, anchor: Date) {
  if (days >= 365) {
    const labels = getMonthLabelsForLastYear(anchor);
    const since = startOfDayInTz(new Date(anchor.getTime() - 365 * 24 * 60 * 60 * 1000), REPORTING_TZ);

    const sessionRows = db
      .prepare(`SELECT created_at FROM sessions WHERE created_at >= ?`)
      .all(toSqliteUtc(since)) as { created_at: string }[];

    const matchRows = db
      .prepare(`SELECT created_at FROM matches WHERE created_at >= ?`)
      .all(toSqliteUtc(since)) as { created_at: string }[];

    const sessionMap = new Map<string, number>();
    const matchMap = new Map<string, number>();
    for (const r of sessionRows) {
      const bucket = formatMonthIsoInTz(parseSqliteUtc(r.created_at), REPORTING_TZ);
      sessionMap.set(bucket, (sessionMap.get(bucket) ?? 0) + 1);
    }
    for (const r of matchRows) {
      const bucket = formatMonthIsoInTz(parseSqliteUtc(r.created_at), REPORTING_TZ);
      matchMap.set(bucket, (matchMap.get(bucket) ?? 0) + 1);
    }

    return {
      labels,
      sessions: labels.map((label) => sessionMap.get(label) ?? 0),
      matches: labels.map((label) => matchMap.get(label) ?? 0),
    };
  }

  const since = startOfDayInTz(new Date(anchor.getTime() - (days - 1) * 24 * 60 * 60 * 1000), REPORTING_TZ);

  const sessionRows = db
    .prepare(`SELECT created_at FROM sessions WHERE created_at >= ?`)
    .all(toSqliteUtc(since)) as { created_at: string }[];

  const matchRows = db
    .prepare(`SELECT created_at FROM matches WHERE created_at >= ?`)
    .all(toSqliteUtc(since)) as { created_at: string }[];

  const sessionMap = new Map<string, number>();
  const matchMap = new Map<string, number>();
  for (const r of sessionRows) {
    const bucket = formatDateIsoInTz(parseSqliteUtc(r.created_at), REPORTING_TZ);
    sessionMap.set(bucket, (sessionMap.get(bucket) ?? 0) + 1);
  }
  for (const r of matchRows) {
    const bucket = formatDateIsoInTz(parseSqliteUtc(r.created_at), REPORTING_TZ);
    matchMap.set(bucket, (matchMap.get(bucket) ?? 0) + 1);
  }

  const labels = buildDateRangeLabels(days, anchor);
  return {
    labels,
    sessions: labels.map((label) => sessionMap.get(label) ?? 0),
    matches: labels.map((label) => matchMap.get(label) ?? 0),
  };
}

function hourlyTrend(db: ReturnType<typeof getDb>, anchor: Date) {
  const since = startOfDayInTz(anchor, REPORTING_TZ);

  const sessionRows = db
    .prepare(`SELECT created_at FROM sessions WHERE created_at >= ?`)
    .all(toSqliteUtc(since)) as { created_at: string }[];

  const matchRows = db
    .prepare(`SELECT created_at FROM matches WHERE created_at >= ?`)
    .all(toSqliteUtc(since)) as { created_at: string }[];

  const sessionMap = new Map<number, number>();
  const matchMap = new Map<number, number>();
  for (const r of sessionRows) {
    const hour = getHourInTz(parseSqliteUtc(r.created_at), REPORTING_TZ);
    sessionMap.set(hour, (sessionMap.get(hour) ?? 0) + 1);
  }
  for (const r of matchRows) {
    const hour = getHourInTz(parseSqliteUtc(r.created_at), REPORTING_TZ);
    matchMap.set(hour, (matchMap.get(hour) ?? 0) + 1);
  }

  const labels: string[] = [];
  const sessionsSeries: number[] = [];
  const matchesSeries: number[] = [];

  for (let hour = 0; hour < 24; hour++) {
    labels.push(`${String(hour).padStart(2, '0')}:00`);
    sessionsSeries.push(sessionMap.get(hour) ?? 0);
    matchesSeries.push(matchMap.get(hour) ?? 0);
  }

  return { labels, sessions: sessionsSeries, matches: matchesSeries };
}

function swipeVolumeByDay(db: ReturnType<typeof getDb>, days: number, anchor: Date) {
  if (days >= 365) {
    const labels = getMonthLabelsForLastYear(anchor);
    const since = startOfDayInTz(new Date(anchor.getTime() - 365 * 24 * 60 * 60 * 1000), REPORTING_TZ);

    const rows = db
      .prepare(`SELECT created_at FROM swipes WHERE created_at >= ?`)
      .all(toSqliteUtc(since)) as { created_at: string }[];

    const buffer = new Map<string, number>();
    for (const r of rows) {
      const bucket = formatMonthIsoInTz(parseSqliteUtc(r.created_at), REPORTING_TZ);
      buffer.set(bucket, (buffer.get(bucket) ?? 0) + 1);
    }

    return {
      labels,
      data: labels.map((label) => buffer.get(label) ?? 0),
    };
  }

  const since = startOfDayInTz(new Date(anchor.getTime() - (days - 1) * 24 * 60 * 60 * 1000), REPORTING_TZ);

  const rows = db
    .prepare(`SELECT created_at FROM swipes WHERE created_at >= ?`)
    .all(toSqliteUtc(since)) as { created_at: string }[];

  const buffer = new Map<string, number>();
  for (const r of rows) {
    const bucket = formatDateIsoInTz(parseSqliteUtc(r.created_at), REPORTING_TZ);
    buffer.set(bucket, (buffer.get(bucket) ?? 0) + 1);
  }

  const labels = buildDateRangeLabels(days, anchor);
  return {
    labels,
    data: labels.map((label) => buffer.get(label) ?? 0),
  };
}

function swipeVolumeByHourToday(db: ReturnType<typeof getDb>, anchor: Date) {
  const since = startOfDayInTz(anchor, REPORTING_TZ);

  const rows = db
    .prepare(`SELECT created_at FROM swipes WHERE created_at >= ?`)
    .all(toSqliteUtc(since)) as { created_at: string }[];

  const byHour = new Map<number, number>();
  for (const r of rows) {
    const hour = getHourInTz(parseSqliteUtc(r.created_at), REPORTING_TZ);
    byHour.set(hour, (byHour.get(hour) ?? 0) + 1);
  }

  const labels: string[] = [];
  const data: number[] = [];

  for (let hour = 0; hour < 24; hour++) {
    labels.push(`${String(hour).padStart(2, '0')}:00`);
    data.push(byHour.get(hour) ?? 0);
  }

  return { labels, data };
}

function swipeRatioByCuisine(db: ReturnType<typeof getDb>) {
  const rows = db
    .prepare(
      `SELECT r.cuisine AS cuisine, s.direction AS direction
       FROM swipes s
       JOIN restaurants r ON r.id = s.restaurant_id
       WHERE r.cuisine IS NOT NULL AND r.cuisine != ''`,
    )
    .all() as { cuisine: string; direction: string }[];

  const perCuisine: Record<string, { yes: number; total: number }> = {};
  for (const opt of CUISINE_OPTIONS) {
    const aliases = expandCuisineTags([opt.value]);
    let yes = 0;
    let total = 0;
    for (const row of rows) {
      const haystack = row.cuisine.toLowerCase();
      if (aliases.some((a) => haystack.includes(a))) {
        total++;
        if (row.direction === 'yes') yes++;
      }
    }
    if (total >= 3) perCuisine[opt.value] = { yes, total };
  }

  return Object.entries(perCuisine)
    .map(([value, { yes, total }]) => ({
      value,
      label: CUISINE_OPTIONS.find((o) => o.value === value)?.label ?? value,
      yesPct: Math.round((yes / total) * 100),
      total,
    }))
    .sort((a, b) => b.total - a.total)
    .slice(0, 6);
}

function topRestaurants(db: ReturnType<typeof getDb>, since?: Date) {
  const params: (string | number)[] = [];
  const whereClause = since ? 'WHERE m.created_at >= ?' : '';
  if (since) params.push(toSqliteUtc(since));

  return db
    .prepare(
      `SELECT r.id, r.name, r.cuisine, s.city AS city, COUNT(*) AS cnt,
              EXISTS(SELECT 1 FROM sponsored_restaurants sp WHERE sp.restaurant_id = r.id) AS is_sponsored
       FROM matches m
       JOIN restaurants r ON r.id = m.restaurant_id
       JOIN sessions s ON s.id = m.session_id
       ${whereClause}
       GROUP BY r.id
       ORDER BY cnt DESC
       LIMIT 8`,
    )
    .all(...params) as { id: number; name: string; cuisine: string | null; city: string | null; cnt: number; is_sponsored: number }[];
}

function topCityCuisineCombos(db: ReturnType<typeof getDb>, since?: Date) {
  const params: (string | number)[] = [];
  const wherePrefix = since ? 'WHERE m.created_at >= ? AND' : 'WHERE';
  if (since) params.push(toSqliteUtc(since));

  const rows = db
    .prepare(
      `SELECT s.city AS city, r.cuisine AS cuisine
       FROM matches m
       JOIN sessions s ON s.id = m.session_id
       JOIN restaurants r ON r.id = m.restaurant_id
       ${wherePrefix} s.city IS NOT NULL AND s.city != '' AND r.cuisine IS NOT NULL AND r.cuisine != ''`,
    )
    .all(...params) as { city: string; cuisine: string }[];

  const counts: Record<string, { city: string; cuisineLabel: string; cnt: number }> = {};
  for (const row of rows) {
    for (const opt of CUISINE_OPTIONS) {
      const aliases = expandCuisineTags([opt.value]);
      if (aliases.some((a) => row.cuisine.toLowerCase().includes(a))) {
        const key = `${row.city}__${opt.value}`;
        if (!counts[key]) counts[key] = { city: row.city, cuisineLabel: opt.label, cnt: 0 };
        counts[key].cnt++;
        break;
      }
    }
  }

  return Object.values(counts)
    .sort((a, b) => b.cnt - a.cnt)
    .slice(0, 5);
}

let statsCache: { data: object; expiresAt: number } | null = null;
const STATS_CACHE_TTL_MS = 60_000;

function sponsorVsOrganic(db: ReturnType<typeof getDb>) {
  const total = (db.prepare('SELECT COUNT(*) AS c FROM matches').get() as { c: number }).c;
  const sponsored = (
    db
      .prepare(
        `SELECT COUNT(*) AS c FROM matches m
         WHERE EXISTS(SELECT 1 FROM sponsored_restaurants sp WHERE sp.restaurant_id = m.restaurant_id)`,
      )
      .get() as { c: number }
  ).c;
  return {
    sponsored,
    total,
    pct: total > 0 ? Math.round((sponsored / total) * 100) : 0,
  };
}

function sponsorCampaignPerformance(db: ReturnType<typeof getDb>) {
  const sponsors = db
    .prepare(`SELECT * FROM sponsored_restaurants ORDER BY priority DESC, created_at DESC`)
    .all() as any[];

  return sponsors.map((s) => {
    if (!s.restaurant_id) {
      return { ...s, impressions: 0, yesSwipes: 0, matches: 0 };
    }
    const row = db
      .prepare(`SELECT impressions, yes_swipes, matches FROM restaurant_stats WHERE restaurant_id = ?`)
      .get(s.restaurant_id) as { impressions: number; yes_swipes: number; matches: number } | undefined;
    return {
      ...s,
      impressions: row?.impressions ?? 0,
      yesSwipes: row?.yes_swipes ?? 0,
      matches: row?.matches ?? 0,
    };
  });
}

export async function GET(req: NextRequest) {
  const admin = await getAuthenticatedAdmin();
  if (!admin) return NextResponse.json({ error: 'Nicht autorisiert' }, { status: 401 });

  // "refresh=1" erzwingt eine frische Berechnung und umgeht den 60s-Cache.
  // Wird vom "Aktualisieren"-Button im Admin genutzt; das Auto-Polling nutzt
  // bewusst den Cache.
  const force = req.nextUrl.searchParams.get('refresh') === '1';

  const cacheNow = Date.now();
  if (!force && statsCache && statsCache.expiresAt > cacheNow) {
    return NextResponse.json(statsCache.data, {
      headers: { 'Cache-Control': 'private, max-age=60' },
    });
  }

  const db = getDb();
  ensureCounters(db);
  ensureRestaurantStats(db);
  ensureRetentionTracking(db);

  const now = new Date();
  const todayStart = startOfDayInTz(now, REPORTING_TZ);
  const sevenDaysStart = startOfDayInTz(new Date(now.getTime() - 6 * 24 * 60 * 60 * 1000), REPORTING_TZ);
  const thirtyDaysStart = startOfDayInTz(new Date(now.getTime() - 29 * 24 * 60 * 60 * 1000), REPORTING_TZ);
  const oneYearStart = startOfDayInTz(new Date(now.getTime() - 364 * 24 * 60 * 60 * 1000), REPORTING_TZ);

  const lifetime = counterMap(db);

  const live = {
    sessions: (db.prepare('SELECT COUNT(*) AS c FROM sessions').get() as { c: number }).c,
    sessionsActive: (
      db
        .prepare(
          `SELECT COUNT(*) AS c FROM sessions
           WHERE status = 'active' AND expires_at > datetime('now')`,
        )
        .get() as { c: number }
    ).c,
    sessionsLive: (
      db
        .prepare(
          `SELECT COUNT(*) AS c FROM sessions
           WHERE status = 'active' AND expires_at > datetime('now')
             AND last_activity_at IS NOT NULL
             AND last_activity_at >= datetime('now', '-5 minutes')`,
        )
        .get() as { c: number }
    ).c,
    usersLive: (
      db
        .prepare(
          `SELECT COUNT(*) AS c FROM participants p
           JOIN sessions s ON s.id = p.session_id
           WHERE p.is_active = 1
             AND p.last_seen_at IS NOT NULL
             AND p.last_seen_at >= datetime('now', '-5 minutes')
             AND s.status = 'active' AND s.expires_at > datetime('now')`,
        )
        .get() as { c: number }
    ).c,
    sessionsToday: (
      db
        .prepare(`SELECT COUNT(*) AS c FROM sessions WHERE created_at >= ?`)
        .get(toSqliteUtc(todayStart)) as { c: number }
    ).c,
    sessions7d: (
      db
        .prepare(`SELECT COUNT(*) AS c FROM sessions WHERE created_at >= ?`)
        .get(toSqliteUtc(sevenDaysStart)) as { c: number }
    ).c,
    matches: (db.prepare('SELECT COUNT(*) AS c FROM matches').get() as { c: number }).c,
    matchesToday: (
      db
        .prepare(`SELECT COUNT(*) AS c FROM matches WHERE created_at >= ?`)
        .get(toSqliteUtc(todayStart)) as { c: number }
    ).c,
    matches7d: (
      db
        .prepare(`SELECT COUNT(*) AS c FROM matches WHERE created_at >= ?`)
        .get(toSqliteUtc(sevenDaysStart)) as { c: number }
    ).c,
    sessionsWithMatch: (
      db.prepare(`SELECT COUNT(DISTINCT session_id) AS c FROM matches`).get() as { c: number }
    ).c,
    participants: (db.prepare('SELECT COUNT(*) AS c FROM participants').get() as { c: number }).c,
    swipes: (db.prepare('SELECT COUNT(*) AS c FROM swipes').get() as { c: number }).c,
    swipesYes: (
      db.prepare(`SELECT COUNT(*) AS c FROM swipes WHERE direction = 'yes'`).get() as { c: number }
    ).c,
    jokerDraws: (db.prepare('SELECT COUNT(*) AS c FROM joker_draws').get() as { c: number }).c,
    restaurantsCached: (
      db.prepare('SELECT COUNT(*) AS c FROM restaurants').get() as { c: number }
    ).c,
    sponsorsActive: (
      db
        .prepare(
          `SELECT COUNT(*) AS c FROM sponsored_restaurants
           WHERE active = 1
             AND (active_from IS NULL OR active_from <= datetime('now'))
             AND (active_until IS NULL OR active_until >= datetime('now'))`,
        )
        .get() as { c: number }
    ).c,
  };

  const topCities = db
    .prepare(
      `SELECT city, COUNT(*) AS cnt
       FROM sessions
       WHERE city IS NOT NULL AND city != ''
       GROUP BY city
       ORDER BY cnt DESC
       LIMIT 8`,
    )
    .all() as { city: string; cnt: number }[];

  const lifetimeSessions = lifetime.sessions ?? 0;
  const lifetimeMatches = lifetime.matches ?? 0;
  const matchRateLifetime =
    lifetimeSessions > 0 ? Math.round((lifetimeMatches / lifetimeSessions) * 1000) / 10 : 0;

  const liveMatchSessionRate =
    live.sessions > 0
      ? Math.round((live.sessionsWithMatch / live.sessions) * 1000) / 10
      : 0;

  const topCitiesToday = topCitySessions(db, todayStart);
  const topCities7d = topCitySessions(db, sevenDaysStart);
  const topCities30d = topCitySessions(db, thirtyDaysStart);
  const topCities365d = topCitySessions(db, oneYearStart);

  const topCitiesMatchesToday = topCityMatches(db, todayStart);
  const topCitiesMatches7d = topCityMatches(db, sevenDaysStart);
  const topCitiesMatches30d = topCityMatches(db, thirtyDaysStart);
  const topCitiesMatches365d = topCityMatches(db, oneYearStart);

  const topCuisineMatchesToday = topCuisineMatches(db, todayStart);
  const topCuisineMatches7d = topCuisineMatches(db, sevenDaysStart);
  const topCuisineMatches30d = topCuisineMatches(db, thirtyDaysStart);
  const topCuisineMatches365d = topCuisineMatches(db, oneYearStart);

  const swipeVolumeToday = swipeVolumeByHourToday(db, now);
  const swipeVolume7d = swipeVolumeByDay(db, 7, now);
  const swipeVolume30d = swipeVolumeByDay(db, 30, now);
  const swipeVolume365d = swipeVolumeByDay(db, 365, now);

  const sessionsWithMatchLifetime = lifetime.sessions_with_match ?? 0;
  const sessionsWithForwardLifetime = lifetime.sessions_with_forward ?? 0;

  const funnel = {
    started: lifetimeSessions,
    withMatch: sessionsWithMatchLifetime,
    forwards: sessionsWithForwardLifetime,
    withMatchPct: lifetimeSessions > 0 ? Math.round((sessionsWithMatchLifetime / lifetimeSessions) * 1000) / 10 : 0,
    forwardsPct: lifetimeSessions > 0 ? Math.round((sessionsWithForwardLifetime / lifetimeSessions) * 1000) / 10 : 0,
  };

  const funnelToday = funnelByRange(db, todayStart);
  const funnel7d = funnelByRange(db, sevenDaysStart);
  const funnel30d = funnelByRange(db, thirtyDaysStart);
  const funnel365d = funnelByRange(db, oneYearStart);

  const clickTypeLabels: Record<string, string> = {
    maps: 'Route/Maps',
    call: 'Anrufen',
    website: 'Webseite',
    reservation: 'Reservieren',
    order: 'Bestellen',
    share: 'Teilen',
  };
  const outboundClicks = {
    total: lifetime.outbound_clicks_total ?? 0,
    byType: (['maps', 'call', 'website', 'reservation', 'order', 'share'] as const)
      .map((type) => ({ type, label: clickTypeLabels[type], cnt: lifetime[`outbound_clicks_${type}`] ?? 0 }))
      .filter((t) => t.cnt > 0)
      .sort((a, b) => b.cnt - a.cnt),
  };

  const outboundClicksToday = outboundClicksByRange(db, todayStart);
  const outboundClicks7d = outboundClicksByRange(db, sevenDaysStart);
  const outboundClicks30d = outboundClicksByRange(db, thirtyDaysStart);
  const outboundClicks365d = outboundClicksByRange(db, oneYearStart);

  const liveSessions = db
    .prepare(
      `SELECT
         s.id, s.code, s.city, s.expires_at, s.last_activity_at,
         (SELECT COUNT(*) FROM participants p WHERE p.session_id = s.id AND p.is_active = 1) AS participant_count,
         EXISTS(SELECT 1 FROM matches m WHERE m.session_id = s.id) AS has_match
       FROM sessions s
       WHERE s.status = 'active' AND s.expires_at > datetime('now')
         AND s.last_activity_at IS NOT NULL
         AND s.last_activity_at >= datetime('now', '-5 minutes')
       ORDER BY s.last_activity_at DESC
       LIMIT 20`
    )
    .all() as {
      id: number;
      code: string;
      city: string;
      expires_at: string;
      last_activity_at: string;
      participant_count: number;
      has_match: number;
    }[];

  // Coverage / Datenqualitaet: Restaurants haben keine eigene city-Spalte
  // (Stadt haengt an Sessions), daher wird hier die Bild-/Datenqualitaet des
  // Restaurants-Cache plus die Nachfrage pro Stadt ausgewertet.
  const imageSourceCounts = db
    .prepare('SELECT image_source_used, COUNT(*) AS c FROM restaurants GROUP BY image_source_used')
    .all() as { image_source_used: string | null; c: number }[];
  const imageCount = (source: string | null) =>
    imageSourceCounts.find((r) => r.image_source_used === source)?.c ?? 0;

  const coverage = {
    totalRestaurants: (
      db.prepare('SELECT COUNT(*) AS c FROM restaurants').get() as { c: number }
    ).c,
    withOsmImage: imageCount('osm'),
    withAdminImage: imageCount('admin'),
    withFallbackImage: imageCount('fallback'),
    noImage: imageCount('none'),
    unresolved: imageCount(null),
    pexelsKeyConfigured: Boolean(process.env.PEXELS_API_KEY),
    demandByCity: db
      .prepare(
        `SELECT city, COUNT(*) AS sessions FROM sessions
         WHERE city IS NOT NULL AND city != ''
         GROUP BY city
         ORDER BY sessions DESC
         LIMIT 30`
      )
      .all() as { city: string; sessions: number }[],
  };

  const responseData = {
    serverTime: new Date().toISOString(),
    reportingTz: REPORTING_TZ,
    lifetime: {
      sessions: lifetimeSessions,
      matches: lifetimeMatches,
      swipes: lifetime.swipes ?? 0,
      swipesYes: lifetime.swipes_yes ?? 0,
      participants: lifetime.participants ?? 0,
      jokerDraws: lifetime.joker_draws ?? 0,
      matchesPerSession: matchRateLifetime,
      sessionsWithMatch: sessionsWithMatchLifetime,
    },
    live: {
      ...live,
      matchSessionRatePct: liveMatchSessionRate,
    },
    topCities,
    topCitiesToday,
    topCities7d,
    topCities30d,
    topCities365d,
    sponsorInsights: {
      topCitiesMatchesToday,
      topCitiesMatches7d,
      topCitiesMatches30d,
      topCitiesMatches365d,
      topCuisineMatchesToday,
      topCuisineMatches7d,
      topCuisineMatches30d,
      topCuisineMatches365d,
    },
    trend7d: dailyTrend(db, 7, now),
    trend30d: dailyTrend(db, 30, now),
    trend365d: dailyTrend(db, 365, now),
    trendToday: hourlyTrend(db, now),
    funnel,
    funnelToday,
    funnel7d,
    funnel30d,
    funnel365d,
    outboundClicks,
    outboundClicksToday,
    outboundClicks7d,
    outboundClicks30d,
    outboundClicks365d,
    swipeVolumeToday,
    swipeVolume7d,
    swipeVolume30d,
    swipeVolume365d,
    swipeRatioByCuisine: swipeRatioByCuisine(db),
    topRestaurants: topRestaurants(db),
    topRestaurantsToday: topRestaurants(db, todayStart),
    topRestaurants7d: topRestaurants(db, sevenDaysStart),
    topRestaurants30d: topRestaurants(db, thirtyDaysStart),
    topRestaurants365d: topRestaurants(db, oneYearStart),
    topCityCuisineCombos: topCityCuisineCombos(db),
    topCityCuisineCombosToday: topCityCuisineCombos(db, todayStart),
    topCityCuisineCombos7d: topCityCuisineCombos(db, sevenDaysStart),
    topCityCuisineCombos30d: topCityCuisineCombos(db, thirtyDaysStart),
    topCityCuisineCombos365d: topCityCuisineCombos(db, oneYearStart),
    sponsorVsOrganic: sponsorVsOrganic(db),
    sponsorCampaigns: sponsorCampaignPerformance(db),
    retention: getRetentionStats(db),
    liveSessions,
    coverage,
  };

  statsCache = { data: responseData, expiresAt: Date.now() + STATS_CACHE_TTL_MS };

  return NextResponse.json(responseData, {
    headers: { 'Cache-Control': 'private, max-age=60' },
  });
}