← back to Trademarks Copyright

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

17 lines

import { NextResponse } from "next/server";
import { query } from "@/lib/db";

export async function GET() {
  const { rows } = await query(
    `SELECT s.id, s.email, s.name, s.tier, s.status, s.trial_drops_left,
            s.signed_up_at, s.last_delivered_at, s.cancelled_at,
            (SELECT COUNT(*) FROM deliveries d WHERE d.subscriber_id = s.id) AS delivered_count,
            (SELECT COUNT(*) FROM deliveries d WHERE d.subscriber_id = s.id AND d.opened_at IS NOT NULL) AS open_count,
            (SELECT COUNT(*) FROM referrals r WHERE r.referrer_token = s.token) AS ref_count
     FROM subscribers s
     ORDER BY s.signed_up_at DESC
     LIMIT 500`
  );
  return NextResponse.json({ subscribers: rows });
}