export const prerender = false;
import type { APIContext } from 'astro';
import { isAuthenticated, ADMIN_COOKIE } from '@admin/lib/admin-auth';
import { syncGeneratedTitleSlugs } from '@/lib/title-slugs';
import { readFileSync } from 'fs';
import { join } from 'path';
interface Env { DB: D1Database }

// POST /api/admin/schema/apply
// Applies the full schema.sql against D1 (all CREATE TABLE IF NOT EXISTS + INSERT OR IGNORE are idempotent).
export async function POST({ locals, cookies }: APIContext) {
  const runtime = locals.runtime as { env: Env } | undefined;
  const db = runtime?.env?.DB;
  if (!db) return new Response(JSON.stringify({ error: 'no db' }), { status: 503 });
  if (!await isAuthenticated(cookies.get(ADMIN_COOKIE)?.value, db)) {
    return new Response(JSON.stringify({ error: 'unauthorized' }), { status: 401 });
  }

  // Read schema.sql from the filesystem (available at SSR time via Node)
  let schema: string;
  try {
    schema = readFileSync(join(process.cwd(), 'src/sites/cultroll/lib/schema.sql'), 'utf-8');
  } catch {
    return new Response(JSON.stringify({ error: 'schema file not found' }), { status: 500 });
  }

  // Split into individual statements (split on semicolons, skip blank/comment-only lines)
  const statements = schema
    .split(';')
    .map(s => s.trim())
    .filter(s => s.length > 0 && !s.startsWith('--'));

  const results: { ok: number; failed: number; errors: string[] } = { ok: 0, failed: 0, errors: [] };

  for (const stmt of statements) {
    try {
      await db.prepare(stmt).run();
      results.ok++;
    } catch (e: unknown) {
      const msg = e instanceof Error ? e.message : String(e);
      // Ignore "already exists" / "duplicate" errors from INSERT OR IGNORE
      if (!msg.includes('already exists') && !msg.includes('UNIQUE constraint')) {
        results.failed++;
        results.errors.push(msg.slice(0, 200));
      } else {
        results.ok++;
      }
    }
  }

  if (results.failed === 0) {
    try {
      const titles = await db.prepare('SELECT id, title_en, release_date FROM titles').all<{
        id: string;
        title_en: string | null;
        release_date: string | null;
      }>();
      for (const title of titles.results ?? []) {
        await syncGeneratedTitleSlugs(db, title.id, title.title_en, title.id, title.release_date);
      }
    } catch (e: unknown) {
      const msg = e instanceof Error ? e.message : String(e);
      results.failed++;
      results.errors.push(`slug backfill: ${msg.slice(0, 180)}`);
    }
  }

  return new Response(JSON.stringify({ success: results.failed === 0, ...results }), {
    headers: { 'Content-Type': 'application/json' },
  });
}