export const prerender = false;

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

interface Env { DB: D1Database }

// Whitelist of sortable columns → SQL expression
const SORT_MAP: Record<string, string> = {
  title_en:      'm.title_en',
  director:      'm.director',
  genre_raw:     'm.genre_raw',
  duration_min:  'm.duration_min',
  release_date:  'm.release_date',
  updated_at:    'm.updated_at',
  created_at:    'm.created_at',
  status:        'm.status',
  is_enabled:    'm.is_enabled',
  showtime_count:'showtime_count',
};

export const GET: APIRoute = async ({ request, locals, cookies }) => {
  const runtime = locals.runtime as { env: Env } | undefined;
  const db = runtime?.env?.DB;
  if (!db || !await isAuthenticated(cookies.get(ADMIN_COOKIE)?.value, db)) {
    return new Response(JSON.stringify({ error: 'Unauthorized' }), { status: 401 });
  }

  const url = new URL(request.url);
  const q      = url.searchParams.get('q') ?? '';
  const limit  = Math.min(parseInt(url.searchParams.get('limit')  || '50'),  200);
  const offset = parseInt(url.searchParams.get('offset') || '0');
  const sortKey = url.searchParams.get('sort') || 'updated_at';
  const dir     = url.searchParams.get('dir')  === 'asc' ? 'ASC' : 'DESC';
  const filter = url.searchParams.get('filter') ?? '';

  const sortExpr = SORT_MAP[sortKey] ?? buildTitleMetadataSortSql('m', sortKey) ?? 'm.updated_at';

  const conditions: string[] = [];
  const params: (string | number)[] = [];

  if (q) {
    conditions.push('(m.title_en LIKE ? OR m.title_ar LIKE ? OR m.title_original LIKE ? OR m.director LIKE ?)');
    const t = `%${q}%`;
    params.push(t, t, t, t);
  }

  if      (filter === 'indie')           conditions.push('m.is_independent = 1');
  else if (filter === 'featured')        conditions.push('m.is_featured = 1');
  else if (filter === 'metadata')        conditions.push(buildTitleMetadataAvailabilitySql('m'));
  else if (filter === 'no_metadata')     conditions.push(`NOT ${buildTitleMetadataAvailabilitySql('m')}`);
  else if (filter === 'no_poster')       conditions.push("(m.poster_url IS NULL OR m.poster_url = '')");
  else if (filter === 'now_playing')     conditions.push("m.status = 'now_playing'");
  else if (filter === 'coming_soon')     conditions.push("m.status = 'coming_soon'");
  else if (filter === 'prev_playing')    conditions.push("m.status = 'previously_playing'");
  else if (filter === 'visible')         conditions.push('m.is_enabled = 1');
  else if (filter === 'hidden')          conditions.push('m.is_enabled = 0');
  else if (filter === 'no_shows')        conditions.push('NOT EXISTS (SELECT 1 FROM showtimes WHERE title_id = m.id)');

  const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';

  const countSQL = `SELECT COUNT(*) as total FROM titles m ${where}`;
  const dataSQL  = `
    SELECT m.id, m.title_en, m.title_ar, m.title_original, m.poster_url,
           ${buildTitleMetadataProjectionSql('m')},
           m.imdb_id, m.elcinema_id, m.director,
           m.genre_raw, m.genre_ids, m.duration_min, m.release_date,
           m.status, m.is_independent, m.is_featured, m.is_enabled, m.updated_at,
           COUNT(s.id) as showtime_count
    FROM titles m
    LEFT JOIN showtimes s ON s.title_id = m.id
    ${where}
    GROUP BY m.id
    ORDER BY ${sortExpr} ${dir}
    LIMIT ? OFFSET ?
  `;

  const [countRow, rows] = await db.batch([
    db.prepare(countSQL).bind(...params),
    db.prepare(dataSQL).bind(...params, limit, offset),
  ]);

  return new Response(JSON.stringify({
    titles: rows.results,
    total: (countRow.results[0] as any)?.total ?? 0,
    limit,
    offset,
  }), { headers: { 'Content-Type': 'application/json' } });
};