`src/app/api/drops/admin/route.ts`

42 lines
import { NextRequest, NextResponse } from "next/server";
import { query } from "@/lib/db";

/**
 * Admin GET — snapshot of subs + drops for the dashboard.
 * Dev-only — protect in production with a real admin auth layer.
 */
export async function GET(_req: NextRequest) {
  const [subs, drops, freshCounts, backendCounts] = await Promise.all([
    query<{ tier: string; status: string; count: string }>(
      `SELECT tier, status, COUNT(*)::text AS count FROM subscribers GROUP BY tier, status ORDER BY tier, status`
    ),
    query<{ id: number; drop_date: string; subject: string; status: string; items: string; deliveries: string; opens: string; bounces: string }>(
      `SELECT d.id, d.drop_date, d.subject, d.status,
              (SELECT COUNT(*)::text FROM drop_items WHERE drop_id = d.id) AS items,
              (SELECT COUNT(*)::text FROM deliveries WHERE drop_id = d.id) AS deliveries,
              (SELECT COUNT(*)::text FROM deliveries WHERE drop_id = d.id AND opened_at IS NOT NULL) AS opens,
              (SELECT COUNT(*)::text FROM deliveries WHERE drop_id = d.id AND bounced = TRUE) AS bounces
       FROM drops d
       ORDER BY drop_date DESC LIMIT 30`
    ),
    query<{ source_kind: string; count: string }>(
      `SELECT 'item' AS source_kind, COUNT(*)::text AS count FROM items
       WHERE NOT EXISTS (SELECT 1 FROM drop_items di WHERE di.source_kind='item' AND di.source_id=items.id)
       UNION ALL
       SELECT 'brand', COUNT(*)::text FROM brand_candidates
       WHERE opportunity_label IN ('domain_snipe','abandonment_watch')
         AND NOT EXISTS (SELECT 1 FROM drop_items di WHERE di.source_kind='brand' AND di.source_id=brand_candidates.id)`
    ),
    query<{ delivered_via: string; count: string }>(
      `SELECT delivered_via, COUNT(*)::text AS count FROM deliveries GROUP BY delivered_via`
    ),
  ]);

  return NextResponse.json({
    subs: subs.rows,
    drops: drops.rows,
    freshCounts: freshCounts.rows,
    backendCounts: backendCounts.rows,
  });
}