export const prerender = false;

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

interface Env { DB: D1Database }

const SORT_MAP: Record<string, string> = {
  email: 'u.email',
  display_name: 'u.display_name',
  created_at: 'u.created_at',
  last_login_at: 'u.last_login_at',
  status: 'u.status',
  title_alert_count: 'title_alert_count',
  city_waitlist_count: 'city_waitlist_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') ?? '').trim();
  const statusFilter = (url.searchParams.get('status') ?? '').trim();
  const limit = Math.min(parseInt(url.searchParams.get('limit') || '50', 10) || 50, 200);
  const offset = Math.max(parseInt(url.searchParams.get('offset') || '0', 10) || 0, 0);
  const sortExpr = SORT_MAP[url.searchParams.get('sort') || 'created_at'] ?? 'u.created_at';
  const sortDir = url.searchParams.get('dir') === 'asc' ? 'ASC' : 'DESC';

  const conditions: string[] = [];
  const params: (string | number)[] = [];
  if (q) {
    conditions.push('(u.email LIKE ? OR u.display_name LIKE ?)');
    const like = `%${q}%`;
    params.push(like, like);
  }
  if (statusFilter === 'active' || statusFilter === 'suspended') {
    conditions.push('u.status = ?');
    params.push(statusFilter);
  }
  const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';

  const countSql = `SELECT COUNT(*) AS total FROM cultroll_users u ${where}`;
  const dataSql = `
    SELECT
      u.id,
      u.email,
      u.display_name,
      u.avatar_url,
      u.google_sub,
      u.status,
      u.email_verified_at,
      u.created_at,
      u.updated_at,
      u.last_login_at,
      COALESCE(ta.title_alert_count, 0) AS title_alert_count,
      COALESCE(cw.city_waitlist_count, 0) AS city_waitlist_count,
      COALESCE(us.active_session_count, 0) AS active_session_count
    FROM cultroll_users u
    LEFT JOIN (
      SELECT email, COUNT(*) AS title_alert_count
      FROM title_alerts
      GROUP BY email
    ) ta ON ta.email = u.email
    LEFT JOIN (
      SELECT email, COUNT(*) AS city_waitlist_count
      FROM city_waitlist
      GROUP BY email
    ) cw ON cw.email = u.email
    LEFT JOIN (
      SELECT user_id, COUNT(*) AS active_session_count
      FROM cultroll_user_sessions
      WHERE revoked_at IS NULL AND expires_at > datetime('now')
      GROUP BY user_id
    ) us ON us.user_id = u.id
    ${where}
    ORDER BY ${sortExpr} ${sortDir}
    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({
    users: rows.results,
    total: (countRow.results[0] as { total?: number } | undefined)?.total ?? 0,
    limit,
    offset,
  }), {
    headers: { 'Content-Type': 'application/json' },
  });
};