← back to Norma

app/api/outreach-pipeline/route.ts

236 lines

import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { getOrgId } from '@/lib/orgId';

/**
 * GET /api/outreach-pipeline?stage=&level=&state=&target_type=&limit=&offset=
 * List pipeline entries with optional filters and pagination.
 */
export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  const { searchParams } = new URL(request.url);
  const stage = searchParams.get('stage');
  const level = searchParams.get('level');
  const state = searchParams.get('state');
  const targetType = searchParams.get('target_type');
  const orgId = auth.role === 'admin' ? getOrgId(request) : auth.orgId;

  let limit = parseInt(searchParams.get('limit') || '50', 10);
  if (isNaN(limit) || limit < 1) limit = 50;
  if (limit > 200) limit = 200;

  let offset = parseInt(searchParams.get('offset') || '0', 10);
  if (isNaN(offset) || offset < 0) offset = 0;

  const conditions: string[] = [];
  const params: unknown[] = [];
  let idx = 1;

  if (orgId) {
    conditions.push(`org_id = $${idx++}`);
    params.push(orgId);
  }
  if (stage) {
    conditions.push(`stage = $${idx++}`);
    params.push(stage);
  }
  if (level) {
    conditions.push(`level = $${idx++}`);
    params.push(level);
  }
  if (state) {
    conditions.push(`state = $${idx++}`);
    params.push(state);
  }
  if (targetType) {
    conditions.push(`target_type = $${idx++}`);
    params.push(targetType);
  }

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

  try {
    const countResult = await query(
      `SELECT COUNT(*)::int AS total FROM outreach_pipeline ${whereClause}`,
      params
    );
    const total = countResult.rows[0]?.total ?? 0;

    const { rows } = await query(
      `SELECT * FROM outreach_pipeline ${whereClause}
       ORDER BY next_followup_date ASC NULLS LAST, updated_at DESC
       LIMIT $${idx} OFFSET $${idx + 1}`,
      [...params, limit, offset]
    );

    // Compute stage summary (org-scoped)
    const stageRes = orgId
      ? await query(
          `SELECT stage, count(*)::int AS count FROM outreach_pipeline WHERE org_id = $1 GROUP BY stage`,
          [orgId]
        )
      : await query(
          `SELECT stage, count(*)::int AS count FROM outreach_pipeline GROUP BY stage`
        );
    const stageCounts: Record<string, number> = {};
    for (const r of stageRes.rows) {
      stageCounts[r.stage as string] = r.count as number;
    }

    return NextResponse.json({ entries: rows, stageCounts, total, limit, offset });
  } catch (err) {
    return NextResponse.json({ error: (err as Error).message }, { status: 500 });
  }
}

/**
 * POST /api/outreach-pipeline
 * Create new pipeline entry.
 */
export async function POST(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  try {
    const body = await request.json();
    const {
      target_type, office_name, official_name, staff_contacts,
      district, state, city, level, party, committees, issue_areas,
      stage, channel, warm_intro_source,
      org_type, is_issue_advocacy, is_lobbying, is_electoral, compliance_notes,
      meeting_date, meeting_format, meeting_notes,
      one_pager_sent, thank_you_sent,
      last_contact_date, next_followup_date, outreach_count, notes,
    } = body;

    if (!target_type || !office_name) {
      return NextResponse.json({ error: 'target_type and office_name are required' }, { status: 400 });
    }

    const orgId = auth.role === 'admin' ? getOrgId(request) : auth.orgId;
    const { rows } = await query(
      `INSERT INTO outreach_pipeline (
        target_type, office_name, official_name, staff_contacts,
        district, state, city, level, party, committees, issue_areas,
        stage, channel, warm_intro_source,
        org_type, is_issue_advocacy, is_lobbying, is_electoral, compliance_notes,
        meeting_date, meeting_format, meeting_notes,
        one_pager_sent, thank_you_sent,
        last_contact_date, next_followup_date, outreach_count, notes, org_id
      ) VALUES (
        $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29
      ) RETURNING *`,
      [
        target_type, office_name, official_name || null,
        JSON.stringify(staff_contacts || []),
        district || null, state || null, city || null, level || null,
        party || null, committees || null, issue_areas || null,
        stage || 'identified', channel || null, warm_intro_source || null,
        org_type || '501c3',
        is_issue_advocacy !== undefined ? is_issue_advocacy : true,
        is_lobbying || false,
        is_electoral || false,
        compliance_notes || null,
        meeting_date || null, meeting_format || null, meeting_notes || null,
        one_pager_sent || false, thank_you_sent || false,
        last_contact_date || null, next_followup_date || null,
        outreach_count || 0, notes || null, orgId,
      ]
    );

    return NextResponse.json({ success: true, entry: rows[0] }, { status: 201 });
  } catch (err) {
    return NextResponse.json({ error: (err as Error).message }, { status: 500 });
  }
}

/**
 * PATCH /api/outreach-pipeline
 * Update pipeline entry. Body must include { id, ...fields }.
 */
export async function PATCH(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

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

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

    // Build dynamic SET clause
    const allowed = [
      'target_type', 'office_name', 'official_name', 'staff_contacts',
      'district', 'state', 'city', 'level', 'party', 'committees', 'issue_areas',
      'stage', 'channel', 'warm_intro_source',
      'org_type', 'is_issue_advocacy', 'is_lobbying', 'is_electoral', 'compliance_notes',
      'meeting_date', 'meeting_format', 'meeting_notes',
      'one_pager_sent', 'thank_you_sent',
      'last_contact_date', 'next_followup_date', 'outreach_count', 'notes',
    ];

    const setClauses: string[] = [];
    const params: unknown[] = [];
    let idx = 1;

    for (const key of allowed) {
      if (key in fields) {
        setClauses.push(`${key} = $${idx++}`);
        const val = fields[key];
        // Stringify JSONB fields
        if (key === 'staff_contacts' && typeof val === 'object') {
          params.push(JSON.stringify(val));
        } else {
          params.push(val);
        }
      }
    }

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

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

    const { rows } = await query(sql, params);

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

    return NextResponse.json({ success: true, entry: rows[0] });
  } catch (err) {
    return NextResponse.json({ error: (err as Error).message }, { status: 500 });
  }
}

/**
 * DELETE /api/outreach-pipeline
 * Delete pipeline entry. Body: { id }
 */
export async function DELETE(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  try {
    const body = await request.json();
    const { id } = body;
    if (!id) {
      return NextResponse.json({ error: 'id is required' }, { status: 400 });
    }
    const { rowCount } = await query('DELETE FROM outreach_pipeline WHERE id = $1', [id]);
    if (rowCount === 0) {
      return NextResponse.json({ error: 'Entry not found' }, { status: 404 });
    }
    return NextResponse.json({ success: true });
  } catch (err) {
    return NextResponse.json({ error: (err as Error).message }, { status: 500 });
  }
}