← back to Norma

app/api/contacts/related/route.ts

147 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/related
 * Find contacts related by skill, school, or group.
 *
 * Query params:
 *   type       — "skill" | "school" | "group" (required)
 *   value      — the skill name / school name / group name (required)
 *   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 type = searchParams.get('type');
    const value = searchParams.get('value');
    const excludeId = searchParams.get('exclude_id');

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

    if (!['skill', 'school', 'group'].includes(type)) {
      return NextResponse.json({ error: 'type must be skill, school, or group' }, { status: 400 });
    }

    let result;
    let countResult;

    if (type === 'skill') {
      // Skills are stored as a TEXT[] array on the contacts table
      const params: unknown[] = [value];
      let nextIdx = 2;
      let extraConds = '';
      if (excludeId) { extraConds += ` AND c.id != $${nextIdx}::uuid`; params.push(excludeId); nextIdx++; }
      if (orgId) { extraConds += ` AND c.org_id = $${nextIdx}`; params.push(orgId); nextIdx++; }

      [countResult, result] = await Promise.all([
        query(
          `SELECT COUNT(*) AS count FROM contacts c
           WHERE LOWER($1) = ANY(SELECT LOWER(s) FROM unnest(c.skills) s)${extraConds}`,
          params,
        ),
        query(
          `SELECT c.id, c.first_name, c.last_name, c.headline, c.company, c.location, c.linkedin_url,
                  c.skills
           FROM contacts c
           WHERE LOWER($1) = ANY(SELECT LOWER(s) FROM unnest(c.skills) s)${extraConds}
           ORDER BY c.first_name
           LIMIT 50`,
          params,
        ),
      ]);

      return NextResponse.json({
        type,
        value,
        contacts: result.rows,
        total: parseInt(countResult.rows[0].count),
      });
    }

    if (type === 'school') {
      const params: unknown[] = [value];
      let nextIdx = 2;
      let extraConds = '';
      if (excludeId) { extraConds += ` AND c.id != $${nextIdx}::uuid`; params.push(excludeId); nextIdx++; }
      if (orgId) { extraConds += ` AND c.org_id = $${nextIdx}`; params.push(orgId); nextIdx++; }

      [countResult, result] = await Promise.all([
        query(
          `SELECT COUNT(DISTINCT c.id) AS count
           FROM contact_education edu
           JOIN contacts c ON c.id = edu.contact_id
           WHERE LOWER(edu.school) = LOWER($1)${extraConds}`,
          params,
        ),
        query(
          `SELECT DISTINCT ON (c.id)
             c.id, c.first_name, c.last_name, c.headline, c.company, c.location, c.linkedin_url,
             edu.degree, edu.field_of_study, edu.start_year, edu.end_year
           FROM contact_education edu
           JOIN contacts c ON c.id = edu.contact_id
           WHERE LOWER(edu.school) = LOWER($1)${extraConds}
           ORDER BY c.id, edu.end_year DESC NULLS LAST
           LIMIT 50`,
          params,
        ),
      ]);

      return NextResponse.json({
        type,
        value,
        contacts: result.rows,
        total: parseInt(countResult.rows[0].count),
      });
    }

    if (type === 'group') {
      const params: unknown[] = [value];
      let nextIdx = 2;
      let extraConds = '';
      if (excludeId) { extraConds += ` AND c.id != $${nextIdx}::uuid`; params.push(excludeId); nextIdx++; }
      if (orgId) { extraConds += ` AND c.org_id = $${nextIdx}`; params.push(orgId); nextIdx++; }

      [countResult, result] = await Promise.all([
        query(
          `SELECT COUNT(DISTINCT c.id) AS count
           FROM contact_groups cg
           JOIN contacts c ON c.id = cg.contact_id
           WHERE LOWER(cg.group_name) = LOWER($1)${extraConds}`,
          params,
        ),
        query(
          `SELECT DISTINCT ON (c.id)
             c.id, c.first_name, c.last_name, c.headline, c.company, c.location, c.linkedin_url
           FROM contact_groups cg
           JOIN contacts c ON c.id = cg.contact_id
           WHERE LOWER(cg.group_name) = LOWER($1)${extraConds}
           ORDER BY c.id
           LIMIT 50`,
          params,
        ),
      ]);

      return NextResponse.json({
        type,
        value,
        contacts: result.rows,
        total: parseInt(countResult.rows[0].count),
      });
    }

    return NextResponse.json({ error: 'Invalid type' }, { status: 400 });
  } catch (err) {
    console.error('[api/contacts/related] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch related contacts' }, { status: 500 });
  }
}