export const prerender = false;

import type { APIRoute } from 'astro';
import { ADMIN_COOKIE, isAuthenticated } from '@admin/lib/admin-auth';
import { FILM_PAGE_EXPERIMENT_KEY, type FilmPageVariant } from '@/lib/experiments';

interface Env {
  DB: D1Database;
}

interface SummaryRow {
  variant: FilmPageVariant;
  exposures: number;
  booking_clicks: number;
  watchlist_saves: number;
  title_alerts: number;
  sign_ins: number;
}

const SCORE_WEIGHTS = {
  booking_clicks: 0.6,
  watchlist_saves: 0.2,
  title_alerts: 0.15,
  sign_ins: 0.05,
} as const;

function getDb(locals: App.Locals): D1Database | undefined {
  return ((locals as { runtime?: { env?: Env } }).runtime)?.env?.DB;
}

function clampWindowDays(value: string | null): number {
  const parsed = Number.parseInt(value ?? '', 10);
  if (!Number.isFinite(parsed)) return 30;
  return Math.min(90, Math.max(7, parsed));
}

function rate(count: number, exposures: number): number {
  if (exposures <= 0) return 0;
  return Number((count / exposures).toFixed(4));
}

function weightedScore(row: SummaryRow): number {
  if (row.exposures <= 0) return 0;
  const score =
    (SCORE_WEIGHTS.booking_clicks * row.booking_clicks)
    + (SCORE_WEIGHTS.watchlist_saves * row.watchlist_saves)
    + (SCORE_WEIGHTS.title_alerts * row.title_alerts)
    + (SCORE_WEIGHTS.sign_ins * row.sign_ins);
  return Number(((score / row.exposures) * 100).toFixed(2));
}

function escCsv(value: string | number): string {
  const text = String(value);
  return /[,"\n\r]/.test(text) ? `"${text.replace(/"/g, '""')}"` : text;
}

export const GET: APIRoute = async ({ request, cookies, locals }) => {
  const db = getDb(locals);
  if (!db || !await isAuthenticated(cookies.get(ADMIN_COOKIE)?.value, db)) {
    return new Response(JSON.stringify({ error: 'Unauthorized' }), {
      status: 401,
      headers: { 'Content-Type': 'application/json' },
    });
  }

  const url = new URL(request.url);
  const windowDays = clampWindowDays(url.searchParams.get('days'));
  const format = (url.searchParams.get('format') ?? 'json').toLowerCase();
  const generatedAt = new Date().toISOString();

  const result = await db.prepare(
    `WITH filtered AS (
       SELECT
         variant,
         event_name,
         title_id,
         session_key
       FROM cultroll_experiment_events
       WHERE experiment_key = ?
         AND created_at >= datetime('now', ?)
     )
     SELECT
       variant,
       COUNT(DISTINCT CASE WHEN event_name = 'exposure' THEN session_key || ':' || COALESCE(title_id, '') END) AS exposures,
       COUNT(DISTINCT CASE WHEN event_name = 'booking_click' THEN session_key || ':' || COALESCE(title_id, '') END) AS booking_clicks,
       COUNT(DISTINCT CASE WHEN event_name = 'watchlist_add' THEN session_key || ':' || COALESCE(title_id, '') END) AS watchlist_saves,
       COUNT(DISTINCT CASE WHEN event_name = 'title_alert' THEN session_key || ':' || COALESCE(title_id, '') END) AS title_alerts,
       COUNT(DISTINCT CASE WHEN event_name = 'sign_in' THEN session_key || ':' || COALESCE(title_id, '') END) AS sign_ins
     FROM filtered
     GROUP BY variant
     ORDER BY variant`,
  ).bind(
    FILM_PAGE_EXPERIMENT_KEY,
    `-${windowDays} days`,
  ).all<SummaryRow>();

  const rowsByVariant = new Map(
    (result.results ?? []).map((row) => [row.variant, row]),
  );
  const variants: SummaryRow[] = [
    rowsByVariant.get('editorial') ?? {
      variant: 'editorial',
      exposures: 0,
      booking_clicks: 0,
      watchlist_saves: 0,
      title_alerts: 0,
      sign_ins: 0,
    },
    rowsByVariant.get('conversion') ?? {
      variant: 'conversion',
      exposures: 0,
      booking_clicks: 0,
      watchlist_saves: 0,
      title_alerts: 0,
      sign_ins: 0,
    },
  ];

  const scoredVariants = variants.map((row) => ({
    ...row,
    booking_click_rate: rate(row.booking_clicks, row.exposures),
    watchlist_save_rate: rate(row.watchlist_saves, row.exposures),
    title_alert_rate: rate(row.title_alerts, row.exposures),
    sign_in_rate: rate(row.sign_ins, row.exposures),
    weighted_score: weightedScore(row),
  }));

  const winner = [...scoredVariants]
    .sort((left, right) => right.weighted_score - left.weighted_score)[0] ?? null;

  if (format === 'csv') {
    const lines = [
      '# FILM PAGE EXPERIMENT REPORT',
      `# Generated: ${generatedAt}`,
      `# Window (days): ${windowDays}`,
      '',
      [
        'variant',
        'exposures',
        'booking_clicks',
        'watchlist_saves',
        'title_alerts',
        'sign_ins',
        'booking_click_rate',
        'watchlist_save_rate',
        'title_alert_rate',
        'sign_in_rate',
        'weighted_score',
      ].join(','),
      ...scoredVariants.map((row) => [
        row.variant,
        row.exposures,
        row.booking_clicks,
        row.watchlist_saves,
        row.title_alerts,
        row.sign_ins,
        row.booking_click_rate,
        row.watchlist_save_rate,
        row.title_alert_rate,
        row.sign_in_rate,
        row.weighted_score,
      ].map(escCsv).join(',')),
    ];

    return new Response(lines.join('\r\n'), {
      headers: {
        'Content-Type': 'text/csv; charset=utf-8',
        'Content-Disposition': `attachment; filename="film-page-experiment-${generatedAt.slice(0, 10)}.csv"`,
      },
    });
  }

  return new Response(JSON.stringify({
    generated_at: generatedAt,
    experiment: FILM_PAGE_EXPERIMENT_KEY,
    window_days: windowDays,
    weights: SCORE_WEIGHTS,
    variants: scoredVariants,
    winner: winner
      ? { variant: winner.variant, weighted_score: winner.weighted_score }
      : null,
  }, null, 2), {
    headers: {
      'Content-Type': 'application/json',
      'Content-Disposition': `attachment; filename="film-page-experiment-${generatedAt.slice(0, 10)}.json"`,
    },
  });
};