← back to Norma

app/api/contacts/coworkers/route.ts

116 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/contacts/coworkers
 * Find contacts who worked at the same organization during overlapping time periods.
 *
 * Query params:
 *   org        — organization/company name (required)
 *   start      — start year (optional, e.g. "2018")
 *   end        — end year (optional, e.g. "2022")
 *   exclude_id — contact id to exclude from results (optional)
 */
export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;
  const orgId = auth.role === 'admin' ? getOrgId(request) : auth.orgId;

  try {
    const { searchParams } = new URL(request.url);
    const org = searchParams.get('org');
    const startParam = searchParams.get('start');
    const endParam = searchParams.get('end');
    const excludeId = searchParams.get('exclude_id');

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

    const startYear = startParam ? parseInt(startParam) : null;
    const endYear = endParam ? parseInt(endParam) : null;

    // Build date overlap conditions
    // Overlap: start_a <= end_b AND end_a >= start_b (null end = "present")
    const conditions: string[] = [
      `LOWER(cwh.company_name) = LOWER($1)`,
    ];
    const params: unknown[] = [org];
    let idx = 2;

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

    if (excludeId) {
      conditions.push(`c.id != $${idx}::uuid`);
      params.push(excludeId);
      idx++;
    }

    // If we have a start year: their tenure must end AFTER our start (or is current)
    if (startYear !== null) {
      conditions.push(
        `(cwh.end_date IS NULL OR EXTRACT(YEAR FROM cwh.end_date) >= $${idx})`
      );
      params.push(startYear);
      idx++;
    }

    // If we have an end year: their tenure must start BEFORE our end
    if (endYear !== null) {
      conditions.push(
        `(cwh.start_date IS NULL OR EXTRACT(YEAR FROM cwh.start_date) <= $${idx})`
      );
      params.push(endYear);
      idx++;
    }

    const where = conditions.join(' AND ');

    // Count query
    const countResult = await query(
      `SELECT COUNT(DISTINCT c.id) AS count
       FROM contact_work_history cwh
       JOIN contacts c ON c.id = cwh.contact_id
       WHERE ${where}`,
      params,
    );

    // Main query — include overlap period info
    const result = await query(
      `SELECT DISTINCT ON (c.id)
         c.id,
         c.first_name,
         c.last_name,
         c.headline,
         c.company,
         c.location,
         c.linkedin_url,
         cwh.title AS role_at_org,
         cwh.start_date,
         cwh.end_date,
         cwh.is_current
       FROM contact_work_history cwh
       JOIN contacts c ON c.id = cwh.contact_id
       WHERE ${where}
       ORDER BY c.id, cwh.start_date DESC NULLS LAST
       LIMIT 50`,
      params,
    );

    return NextResponse.json({
      org,
      coworkers: result.rows,
      total: parseInt(countResult.rows[0].count),
    });
  } catch (err) {
    console.error('[api/contacts/coworkers] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch coworkers' }, { status: 500 });
  }
}