export const prerender = false;

import type { APIRoute } from 'astro';
import { isAuthenticated, ADMIN_COOKIE } from '@admin/lib/admin-auth';
import { buildTitleMetadataProjectionSql } from '@/lib/catalog-metadata';

interface Env { DB: D1Database }

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

// ── Data types ──────────────────────────────────────────────────────────────

interface ComingSoonRow {
  id: string; title_en: string; title_ar: string | null;
  release_date: string | null; metadata_popularity: number | null; metadata_rating: number | null;
}
interface ShowtimeLeaderRow {
  id: string; title_en: string; cinema_count: number; showtime_count: number;
}
interface WaitlistCityRow { city_name: string; demand: number; }
interface BookingClickRow { title_en: string; clicks: number; }
interface TitleAlertRow   { title_en: string; signups: number; }

// ── Helpers ──────────────────────────────────────────────────────────────────

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

function rowsToCsv(headers: string[], rows: (string | number | null)[][]): string {
  const lines = [headers.map(escCsv).join(',')];
  for (const row of rows) lines.push(row.map(escCsv).join(','));
  return lines.join('\r\n');
}

// ── Route ────────────────────────────────────────────────────────────────────

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 format = (url.searchParams.get('format') ?? 'json').toLowerCase();
  const generatedAt = new Date().toISOString();

  // ── Core queries (always available) ────────────────────────────────────────
  let comingSoon: ComingSoonRow[]     = [];
  let showtimeLeaders: ShowtimeLeaderRow[] = [];
  let waitlistCities: WaitlistCityRow[] = [];

  try {
    const [csRes, slRes, wlRes] = await db.batch([
      db.prepare(`
        SELECT id, title_en, title_ar, release_date, ${buildTitleMetadataProjectionSql()}
        FROM titles WHERE status = 'coming_soon'
        ORDER BY COALESCE(release_date,'9999') ASC, COALESCE(metadata_popularity,0) DESC
        LIMIT 20
      `),
      db.prepare(`
        SELECT t.id, t.title_en,
               COUNT(DISTINCT s.cinema_id) AS cinema_count,
               COUNT(s.id)                AS showtime_count
        FROM titles t
        JOIN showtimes s ON s.title_id = t.id
        WHERE t.status = 'now_playing'
        GROUP BY t.id
        ORDER BY cinema_count DESC, showtime_count DESC
        LIMIT 20
      `),
      db.prepare(`
        SELECT city_name, COUNT(*) AS demand
        FROM city_waitlist
        GROUP BY city_name
        ORDER BY demand DESC
        LIMIT 20
      `),
    ]);
    comingSoon      = csRes.results as ComingSoonRow[];
    showtimeLeaders = slRes.results as ShowtimeLeaderRow[];
    waitlistCities  = wlRes.results as WaitlistCityRow[];
  } catch (e) {
    // DB structure issue — return error rather than silently empty
    return new Response(JSON.stringify({ error: 'Failed to query core tables', detail: String(e) }), {
      status: 500,
      headers: { 'Content-Type': 'application/json' },
    });
  }

  // ── Optional: booking_clicks ────────────────────────────────────────────────
  let bookingClicks: BookingClickRow[] = [];
  let bookingClicksAvailable = false;
  try {
    const bcRes = await db.prepare(`
      SELECT t.title_en, COUNT(bc.id) AS clicks
      FROM booking_clicks bc
      JOIN titles t ON t.id = bc.title_id
      WHERE bc.clicked_at >= datetime('now', '-7 days')
      GROUP BY bc.title_id
      ORDER BY clicks DESC
      LIMIT 20
    `).all();
    bookingClicks = bcRes.results as BookingClickRow[];
    bookingClicksAvailable = true;
  } catch (_) { /* table not yet created */ }

  // ── Optional: title_alerts ──────────────────────────────────────────────────
  let titleAlerts: TitleAlertRow[] = [];
  let titleAlertsAvailable = false;
  try {
    const taRes = await db.prepare(`
      SELECT t.title_en, COUNT(ta.id) AS signups
      FROM title_alerts ta
      JOIN titles t ON t.id = ta.title_id
      GROUP BY ta.title_id
      ORDER BY signups DESC
      LIMIT 20
    `).all();
    titleAlerts = taRes.results as TitleAlertRow[];
    titleAlertsAvailable = true;
  } catch (_) { /* table not yet created */ }

  // ── Format: JSON ─────────────────────────────────────────────────────────────
  if (format !== 'csv') {
    const payload = {
      generated_at: generatedAt,
      coming_soon: comingSoon,
      showtime_leaders: showtimeLeaders,
      city_waitlist_demand: waitlistCities,
      booking_clicks_7d: bookingClicksAvailable ? bookingClicks : null,
      title_alert_demand: titleAlertsAvailable ? titleAlerts : null,
      meta: {
        booking_clicks_available: bookingClicksAvailable,
        title_alerts_available: titleAlertsAvailable,
      },
    };
    return new Response(JSON.stringify(payload, null, 2), {
      headers: {
        'Content-Type': 'application/json',
        'Content-Disposition': `attachment; filename="release-pulse-${generatedAt.slice(0, 10)}.json"`,
      },
    });
  }

  // ── Format: CSV (multi-section, separated by blank lines) ────────────────────
  const sections: string[] = [];

  sections.push('# RELEASE PULSE REPORT');
  sections.push(`# Generated: ${generatedAt}`);
  sections.push('');

  // Coming Soon
  sections.push('## COMING SOON');
  sections.push(rowsToCsv(
    ['title_en', 'title_ar', 'release_date', 'metadata_popularity', 'metadata_rating'],
    comingSoon.map(r => [r.title_en, r.title_ar, r.release_date, r.metadata_popularity, r.metadata_rating]),
  ));
  sections.push('');

  // Showtime Leaders
  sections.push('## SHOWTIME LEADERS (NOW PLAYING)');
  sections.push(rowsToCsv(
    ['rank', 'title_en', 'cinema_count', 'showtime_count'],
    showtimeLeaders.map((r, i) => [i + 1, r.title_en, r.cinema_count, r.showtime_count]),
  ));
  sections.push('');

  // City Waitlist Demand
  sections.push('## CITY WAITLIST DEMAND');
  sections.push(rowsToCsv(
    ['city_name', 'signups'],
    waitlistCities.map(r => [r.city_name, r.demand]),
  ));
  sections.push('');

  // Booking Clicks
  sections.push('## BOOKING INTENT (LAST 7 DAYS)');
  if (bookingClicksAvailable) {
    sections.push(rowsToCsv(
      ['title_en', 'clicks_7d'],
      bookingClicks.map(r => [r.title_en, r.clicks]),
    ));
  } else {
    sections.push('# booking_clicks table not yet available');
  }
  sections.push('');

  // Title Alert Demand
  sections.push('## TITLE ALERT DEMAND');
  if (titleAlertsAvailable) {
    sections.push(rowsToCsv(
      ['title_en', 'alert_signups'],
      titleAlerts.map(r => [r.title_en, r.signups]),
    ));
  } else {
      sections.push('# title_alerts table not yet available');
  }

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