import type { APIContext } from 'astro';
import { isAuthenticated, ADMIN_COOKIE } from '@admin/lib/admin-auth';

export const prerender = false;

export async function POST({ cookies, locals }: APIContext) {
  const db: D1Database = (locals as any).runtime?.env?.DB;
  if (!db) return Response.json({ error: 'DB unavailable' }, { status: 503 });

  const cookie = cookies.get(ADMIN_COOKIE)?.value;
  if (!(await isAuthenticated(cookie, db))) {
    return Response.json({ error: 'Unauthorized' }, { status: 401 });
  }

  // Fetch all titles that have a raw genre string but no normalized genre_ids yet
  const { results: titles } = await db
    .prepare(`SELECT id, genre_raw FROM titles WHERE genre_raw IS NOT NULL AND (genre_ids IS NULL OR genre_ids = '')`)
    .all<{ id: string; genre_raw: string }>();

  if (!titles.length) {
    return Response.json({ updated: 0, message: 'Nothing to backfill' });
  }

  // Build a single alias lookup for all unique tokens
  const allTokens = new Set<string>();
  const titleTokens = new Map<string, string[]>();
  for (const t of titles) {
    const tokens = t.genre_raw.split(',').map((tok) => tok.trim().toLowerCase()).filter(Boolean);
    titleTokens.set(t.id, tokens);
    tokens.forEach((tok) => allTokens.add(tok));
  }

  // Fetch all relevant aliases in one query
  const tokenList = [...allTokens];
  const { results: aliases } = await db
    .prepare(`SELECT alias, genre_id FROM genre_aliases WHERE alias IN (${tokenList.map(() => '?').join(',')})`)
    .bind(...tokenList)
    .all<{ alias: string; genre_id: string }>();

  const aliasMap = new Map(aliases.map((a) => [a.alias, a.genre_id]));

  // Build batched updates
  let updated = 0;
  const batch: D1PreparedStatement[] = [];

  for (const [titleId, tokens] of titleTokens) {
    const seen = new Set<string>();
    const ids: string[] = [];
    for (const token of tokens) {
      const id = aliasMap.get(token);
      if (id && !seen.has(id)) {
        seen.add(id);
        ids.push(id);
      }
    }
    if (ids.length) {
      batch.push(
        db.prepare(`UPDATE titles SET genre_ids = ?, updated_at = datetime('now') WHERE id = ?`)
          .bind(ids.join(','), titleId)
      );
      updated++;
    }
  }

  // Execute in batches of 100
  for (let i = 0; i < batch.length; i += 100) {
    await db.batch(batch.slice(i, i + 100));
  }

  return Response.json({
    checked: titles.length,
    updated,
    skipped: titles.length - updated,
    message: `Backfill complete. ${updated} titles updated.`,
  });
}