`app/api/onboard/route.ts`

102 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/onboard ─────────────────────────────────────────────────────────
// Query params: status=discovered|contacted|onboarded|rejected, page=1, limit=20, sort=match_score

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 status = searchParams.get('status') ?? '';
    const page = Math.max(1, parseInt(searchParams.get('page') ?? '1', 10) || 1);
    const limit = Math.min(100, Math.max(1, parseInt(searchParams.get('limit') ?? '20', 10) || 20));
    const offset = (page - 1) * limit;

    const orgId = auth.role === 'admin' ? getOrgId(request) : auth.orgId;
    const VALID_STATUSES = ['discovered', 'contacted', 'onboarded', 'rejected'];
    const useStatusFilter = status && VALID_STATUSES.includes(status);

    const conditions: string[] = ['TRUE'];
    const params: unknown[] = [];
    let pIdx = 1;

    if (orgId) {
      conditions.push(`od.org_id = $${pIdx}`);
      params.push(orgId);
      pIdx++;
    }
    if (useStatusFilter) {
      conditions.push(`od.status = $${pIdx}`);
      params.push(status);
      pIdx++;
    }

    const whereClause = `WHERE ${conditions.join(' AND ')}`;
    const limitIdx = pIdx;
    const offsetIdx = pIdx + 1;
    params.push(limit, offset);

    const { rows } = await query<{
      id: string;
      org_id: string | null;
      org_name: string;
      trigger_label: string | null;
      match_score: number | null;
      status: string;
      ntee_code: string | null;
      state: string | null;
      recommended_actions: unknown;
      notes: string | null;
      created_at: string;
      updated_at: string | null;
    }>(
      `SELECT
         od.id,
         od.org_id,
         COALESCE(o.name, 'Unknown Org') AS org_name,
         od.trigger_label,
         od.match_score,
         od.status,
         o.ntee_code,
         o.state,
         od.recommended_actions,
         od.notes,
         od.created_at,
         od.updated_at
       FROM onboard_discoveries od
       LEFT JOIN organizations o ON o.id = od.org_id
       ${whereClause}
       ORDER BY od.match_score DESC NULLS LAST, od.created_at DESC
       LIMIT $${limitIdx} OFFSET $${offsetIdx}`,
      params,
    );

    const countParams: unknown[] = [];
    const countConditions: string[] = ['TRUE'];
    if (orgId) { countConditions.push(`od.org_id = $${countParams.length + 1}`); countParams.push(orgId); }
    if (useStatusFilter) { countConditions.push(`od.status = $${countParams.length + 1}`); countParams.push(status); }
    const { rows: countRows } = await query<{ count: string }>(
      `SELECT COUNT(*) AS count
       FROM onboard_discoveries od
       WHERE ${countConditions.join(' AND ')}`,
      countParams,
    );

    return NextResponse.json({
      items: rows,
      total: parseInt(countRows[0]?.count ?? '0', 10),
      page,
      limit,
      status_filter: status || null,
    });
  } catch (err: unknown) {
    console.error('[api/onboard] Error:', (err as Error).message);
    return NextResponse.json({ error: (err as Error).message }, { status: 500 });
  }
}