← back to Patty

app/api/subscribers/route.ts

177 lines

import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { verifyAuth } from '@/lib/auth';

export async function GET(request: NextRequest) {
  const user = verifyAuth(request);
  if (!user) return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });

  const url = new URL(request.url);
  const search = url.searchParams.get('search');
  const source = url.searchParams.get('source');

  let sql = `SELECT * FROM email_subscribers`;
  const params: unknown[] = [];
  const conditions: string[] = [];

  if (search) {
    params.push(`%${search}%`);
    conditions.push(`(email ILIKE $${params.length} OR name ILIKE $${params.length})`);
  }

  if (source && source !== 'all') {
    params.push(source);
    conditions.push(`source = $${params.length}`);
  }

  if (conditions.length > 0) {
    sql += ` WHERE ` + conditions.join(' AND ');
  }

  sql += ` ORDER BY created_at DESC`;

  try {
    const result = await query(sql, params);

    // Get stats
    const statsResult = await query(`
      SELECT
        COUNT(*)::int as total,
        COUNT(*) FILTER (WHERE is_active = true)::int as active,
        COUNT(*) FILTER (WHERE is_active = false)::int as inactive
      FROM email_subscribers
    `);

    const sourceResult = await query(`
      SELECT source, COUNT(*)::int as count
      FROM email_subscribers
      GROUP BY source
      ORDER BY count DESC
    `);

    const bySource: Record<string, number> = {};
    for (const row of sourceResult.rows) {
      bySource[row.source || 'unknown'] = row.count;
    }

    const stats = {
      total: statsResult.rows[0]?.total || 0,
      active: statsResult.rows[0]?.active || 0,
      inactive: statsResult.rows[0]?.inactive || 0,
      by_source: bySource,
    };

    return NextResponse.json({ rows: result.rows, stats });
  } catch (err) {
    console.error('[api/subscribers] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch subscribers' }, { status: 500 });
  }
}

export async function POST(request: NextRequest) {
  const user = verifyAuth(request);
  if (!user) return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });

  try {
    const body = await request.json();
    const { email, name, zip_code, source, tags } = body;

    if (!email) {
      return NextResponse.json({ error: 'Email is required' }, { status: 400 });
    }

    // Get user_id
    const userResult = await query(`SELECT id FROM users LIMIT 1`);
    const userId = userResult.rows[0]?.id || null;

    const result = await query(
      `INSERT INTO email_subscribers (user_id, email, name, zip_code, source, tags)
       VALUES ($1, $2, $3, $4, $5, $6)
       RETURNING *`,
      [
        userId,
        email,
        name || null,
        zip_code || null,
        source || 'manual',
        tags || null,
      ]
    );

    return NextResponse.json({ row: result.rows[0] }, { status: 201 });
  } catch (err) {
    console.error('[api/subscribers] POST error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to add subscriber' }, { status: 500 });
  }
}

export async function PATCH(request: NextRequest) {
  const user = verifyAuth(request);
  if (!user) return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });

  try {
    const body = await request.json();
    const { id, ...updates } = body;

    if (!id) {
      return NextResponse.json({ error: 'Subscriber id is required' }, { status: 400 });
    }

    const allowedFields = ['name', 'zip_code', 'source', 'tags', 'is_active', 'petition_ids'];
    const setClauses: string[] = [];
    const params: unknown[] = [];

    for (const [key, value] of Object.entries(updates)) {
      if (allowedFields.includes(key)) {
        params.push(value);
        setClauses.push(`${key} = $${params.length}`);
      }
    }

    if (setClauses.length === 0) {
      return NextResponse.json({ error: 'No valid fields to update' }, { status: 400 });
    }

    params.push(id);
    const sql = `UPDATE email_subscribers SET ${setClauses.join(', ')} WHERE id = $${params.length} RETURNING *`;

    const result = await query(sql, params);
    if (result.rows.length === 0) {
      return NextResponse.json({ error: 'Subscriber not found' }, { status: 404 });
    }

    return NextResponse.json({ row: result.rows[0] });
  } catch (err) {
    console.error('[api/subscribers] PATCH error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to update subscriber' }, { status: 500 });
  }
}

export async function DELETE(request: NextRequest) {
  const user = verifyAuth(request);
  if (!user) return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });

  try {
    const url = new URL(request.url);
    const id = url.searchParams.get('id');

    if (!id) {
      return NextResponse.json({ error: 'Subscriber id is required' }, { status: 400 });
    }

    const result = await query(
      `UPDATE email_subscribers SET is_active = false, unsubscribed_at = NOW() WHERE id = $1 RETURNING *`,
      [id]
    );

    if (result.rows.length === 0) {
      return NextResponse.json({ error: 'Subscriber not found' }, { status: 404 });
    }

    return NextResponse.json({ row: result.rows[0] });
  } catch (err) {
    console.error('[api/subscribers] DELETE error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to unsubscribe' }, { status: 500 });
  }
}