`app/api/collaborations/route.ts`

169 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { verifyAuthWithOrg, resolveOrgId } from '@/lib/auth';

/* ─── GET /api/collaborations ─────────────────────────────────────────────── */
export async function GET(request: NextRequest) {
  const session = verifyAuthWithOrg(request);
  if (!session) return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });

  try {
    const url = new URL(request.url);
    const collabType = url.searchParams.get('collab_type');
    const status = url.searchParams.get('status');
    const search = url.searchParams.get('search');

    const orgId = await resolveOrgId(session);
    if (!orgId) return NextResponse.json({ rows: [], typeCounts: {} });

    let sql = 'SELECT id, org_id, collab_type, name, title, organization, website_url, email, phone, district, state, ai_reason, ai_relevance, ai_talking_points, status, notes, last_contacted, created_at, updated_at FROM collaborations WHERE org_id = $1';
    const params: unknown[] = [orgId];
    let paramIdx = 2;

    if (collabType && collabType !== 'all') {
      sql += ` AND collab_type = $${paramIdx}`;
      params.push(collabType);
      paramIdx++;
    }

    if (status && status !== 'all') {
      sql += ` AND status = $${paramIdx}`;
      params.push(status);
      paramIdx++;
    }

    if (search) {
      sql += ` AND (name ILIKE $${paramIdx} OR organization ILIKE $${paramIdx} OR ai_reason ILIKE $${paramIdx})`;
      params.push(`%${search}%`);
      paramIdx++;
    }

    sql += ' ORDER BY ai_relevance DESC NULLS LAST, created_at DESC';

    const result = await query(sql, params);

    // Type counts (always unfiltered)
    const countsRes = await query(
      `SELECT collab_type, COUNT(*)::int as count FROM collaborations WHERE org_id = $1 GROUP BY collab_type`,
      [orgId],
    );
    const typeCounts: Record<string, number> = {};
    let total = 0;
    for (const row of countsRes.rows) {
      typeCounts[row.collab_type] = row.count;
      total += row.count;
    }
    typeCounts['all'] = total;

    return NextResponse.json({ rows: result.rows, typeCounts });
  } catch (err) {
    console.error('[collaborations GET]', err);
    return NextResponse.json({ error: 'Failed to fetch collaborations' }, { status: 500 });
  }
}

/* ─── POST /api/collaborations ────────────────────────────────────────────── */
export async function POST(request: NextRequest) {
  const session = verifyAuthWithOrg(request);
  if (!session) return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });

  try {
    const body = await request.json();
    const orgId = await resolveOrgId(session);
    if (!orgId) return NextResponse.json({ error: 'No organization found' }, { status: 400 });

    const result = await query(
      `INSERT INTO collaborations (org_id, collab_type, name, title, organization, website_url, email, phone, district, state, ai_reason, ai_relevance, ai_talking_points, status, notes)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
       RETURNING id, org_id, collab_type, name, title, organization, website_url, email, phone, district, state, ai_reason, ai_relevance, ai_talking_points, status, notes, last_contacted, created_at, updated_at`,
      [
        orgId,
        body.collab_type || 'nonprofit',
        body.name,
        body.title || null,
        body.organization || null,
        body.website_url || null,
        body.email || null,
        body.phone || null,
        body.district || null,
        body.state || null,
        body.ai_reason || null,
        body.ai_relevance || null,
        body.ai_talking_points || [],
        body.status || 'suggested',
        body.notes || null,
      ],
    );

    return NextResponse.json(result.rows[0], { status: 201 });
  } catch (err) {
    console.error('[collaborations POST]', err);
    return NextResponse.json({ error: 'Failed to create collaboration' }, { status: 500 });
  }
}

/* ─── PATCH /api/collaborations ───────────────────────────────────────────── */
export async function PATCH(request: NextRequest) {
  const session = verifyAuthWithOrg(request);
  if (!session) return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });

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

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

    // 2026-05-30 (audit P1-7): map allowed field → literal SQL identifier
    // instead of interpolating the user-supplied key.
    const COLUMN_MAP: Record<string, string> = {
      status: '"status"', notes: '"notes"', last_contacted: '"last_contacted"',
      email: '"email"', phone: '"phone"', website_url: '"website_url"',
      title: '"title"', organization: '"organization"', district: '"district"',
      state: '"state"',
    };

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

    for (const key of Object.keys(fields)) {
      const col = COLUMN_MAP[key];
      if (col) {
        setClauses.push(`${col} = $${paramIdx}`);
        params.push(fields[key]);
        paramIdx++;
      }
    }

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

    // 2026-05-04 (code-reviewer P0-3): tenant isolation on PATCH.
    // 2026-05-05 (tick 19+20): cookie-orgId fast path with LIMIT 1 fallback.
    const patchOrgId = await resolveOrgId(session);
    if (!patchOrgId) {
      return NextResponse.json({ error: 'No organization found' }, { status: 400 });
    }

    params.push(id);
    const idIdx = paramIdx;
    paramIdx++;
    params.push(patchOrgId);
    const orgIdx = paramIdx;
    const sql = `UPDATE collaborations SET ${setClauses.join(', ')} WHERE id = $${idIdx} AND org_id = $${orgIdx} RETURNING id, org_id, collab_type, name, title, organization, website_url, email, phone, district, state, ai_reason, ai_relevance, ai_talking_points, status, notes, last_contacted, created_at, updated_at`;
    const result = await query(sql, params);

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

    return NextResponse.json(result.rows[0]);
  } catch (err) {
    console.error('[collaborations PATCH]', err);
    return NextResponse.json({ error: 'Failed to update collaboration' }, { status: 500 });
  }
}