`app/api/clients/route.ts`

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

/**
 * GET /api/clients
 * List clients with filtering, searching, sorting, and pagination.
 *
 * Query params:
 *   q             - Full-text search on name, email
 *   client_type   - borrower | member | volunteer | donor | beneficiary | general
 *   status        - active | inactive | archived
 *   priority      - high | medium | low
 *   state         - Two-letter state code
 *   assigned_to   - Staff member name (exact match)
 *   sort          - name | intake_date | last_contact_date | debt_amount (default: name)
 *   sort_dir      - asc | desc (default: asc)
 *   limit         - Max records returned (default: 50, max: 200)
 *   offset        - Pagination offset (default: 0)
 */
export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  try {
    const { searchParams } = new URL(request.url);
    const q = searchParams.get('q');
    const clientType = searchParams.get('client_type');
    const status = searchParams.get('status');
    const priority = searchParams.get('priority');
    const state = searchParams.get('state');
    const assignedTo = searchParams.get('assigned_to');
    const sort = searchParams.get('sort') || 'name';
    const sortDir = searchParams.get('sort_dir') === 'desc' ? 'DESC' : 'ASC';
    const limit = Math.min(parseInt(searchParams.get('limit') || '50', 10), 200);
    const offset = parseInt(searchParams.get('offset') || '0', 10);

    const orgId = auth.role === 'admin' ? getOrgId(request) : auth.orgId;

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

    if (orgId) {
      conditions.push(`org_id = $${idx++}`);
      params.push(orgId);
    }

    if (q) {
      conditions.push(
        `(to_tsvector('english', first_name || ' ' || last_name) @@ plainto_tsquery($${idx}) OR LOWER(email) LIKE '%' || LOWER($${idx}) || '%')`,
      );
      params.push(q);
      idx++;
    }
    if (clientType) {
      conditions.push(`client_type = $${idx++}`);
      params.push(clientType);
    }
    if (status) {
      conditions.push(`status = $${idx++}`);
      params.push(status);
    }
    if (priority) {
      conditions.push(`priority = $${idx++}`);
      params.push(priority);
    }
    if (state) {
      conditions.push(`state = $${idx++}`);
      params.push(state);
    }
    if (assignedTo) {
      conditions.push(`LOWER(assigned_to) = LOWER($${idx++})`);
      params.push(assignedTo);
    }

    const where = conditions.length > 0 ? 'WHERE ' + conditions.join(' AND ') : '';

    const sortMap: Record<string, string> = {
      name: 'last_name, first_name',
      intake_date: 'intake_date',
      last_contact_date: 'last_contact_date',
      debt_amount: 'debt_amount',
    };
    const orderCol = sortMap[sort] || 'last_name, first_name';

    const [dataResult, countResult] = await Promise.all([
      query(
        `SELECT * FROM clients ${where}
         ORDER BY ${orderCol} ${sortDir} NULLS LAST
         LIMIT $${idx} OFFSET $${idx + 1}`,
        [...params, limit, offset],
      ),
      query(`SELECT COUNT(*)::int AS total FROM clients ${where}`, params),
    ]);

    return NextResponse.json({
      clients: dataResult.rows,
      total: countResult.rows[0]?.total ?? 0,
      limit,
      offset,
    });
  } catch (err) {
    console.error('[api/clients] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch clients' }, { status: 500 });
  }
}

/**
 * POST /api/clients
 * Create a new client / constituent record.
 */
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 {
      first_name, last_name, email, phone, city, state, zip,
      client_type, status, priority,
      case_notes, intake_date, assigned_to, tags,
      debt_amount, debt_type, loan_servicer, repayment_plan,
      last_contact_date, total_interactions, referral_source,
      custom_fields,
    } = body;

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

    const orgId = auth.role === 'admin' ? getOrgId(request) : auth.orgId;

    const { rows } = await query(
      `INSERT INTO clients (
        org_id,
        first_name, last_name, email, phone, city, state, zip,
        client_type, status, priority,
        case_notes, intake_date, assigned_to, tags,
        debt_amount, debt_type, loan_servicer, repayment_plan,
        last_contact_date, total_interactions, referral_source,
        custom_fields
      ) VALUES (
        $1,
        $2, $3, $4, $5, $6, $7, $8,
        $9, $10, $11,
        $12, $13, $14, $15,
        $16, $17, $18, $19,
        $20, $21, $22,
        $23
      ) RETURNING *`,
      [
        orgId,
        first_name.trim(), last_name.trim(), email || null, phone || null,
        city || null, state || null, zip || null,
        client_type || 'general', status || 'active', priority || 'medium',
        case_notes || null, intake_date || null, assigned_to || null, tags || [],
        debt_amount ?? null, debt_type || null, loan_servicer || null, repayment_plan || null,
        last_contact_date || null, total_interactions ?? 0, referral_source || null,
        custom_fields ? JSON.stringify(custom_fields) : '{}',
      ],
    );

    const client = rows[0];

    await auditLog(
      'client.created',
      'client',
      client.id,
      { org_id: orgId, name: `${first_name} ${last_name}`, client_type: client.client_type },
      request.headers.get('x-forwarded-for') ?? undefined,
    );

    return NextResponse.json({ client }, { status: 201 });
  } catch (err) {
    console.error('[api/clients] POST error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to create client' }, { status: 500 });
  }
}