`app/api/contacts/counts/route.ts`

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

/**
 * POST /api/contacts/counts
 * Batch count related contacts for all clickable profile elements at once.
 * Returns counts grouped by type+value so the UI can show badges without
 * making individual requests for every skill/school/group/employer.
 *
 * Body:
 * {
 *   contact_id: string,
 *   skills:     string[],
 *   schools:    string[],
 *   groups:     string[],
 *   employers:  Array<{ company_name: string; start_date: string|null; end_date: string|null }>
 * }
 */
export async function POST(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 body = await request.json();
    const { contact_id, skills = [], schools = [], groups = [], employers = [] } = body;

    const results: Record<string, number> = {};

    await Promise.all([
      // Skills
      ...skills.map(async (skill: string) => {
        const r = await query(
          `SELECT COUNT(*) AS count FROM contacts c
           WHERE c.id != $1::uuid
             AND LOWER($2) = ANY(SELECT LOWER(s) FROM unnest(c.skills) s)${orgId ? ' AND c.org_id = $3' : ''}`,
          orgId ? [contact_id, skill, orgId] : [contact_id, skill],
        );
        results[`skill:${skill}`] = parseInt(r.rows[0].count);
      }),

      // Schools
      ...schools.map(async (school: string) => {
        const r = await query(
          `SELECT COUNT(DISTINCT c.id) AS count
           FROM contact_education edu
           JOIN contacts c ON c.id = edu.contact_id
           WHERE c.id != $1::uuid AND LOWER(edu.school) = LOWER($2)${orgId ? ' AND c.org_id = $3' : ''}`,
          orgId ? [contact_id, school, orgId] : [contact_id, school],
        );
        results[`school:${school}`] = parseInt(r.rows[0].count);
      }),

      // Groups
      ...groups.map(async (group: string) => {
        const r = await query(
          `SELECT COUNT(DISTINCT c.id) AS count
           FROM contact_groups cg
           JOIN contacts c ON c.id = cg.contact_id
           WHERE c.id != $1::uuid AND LOWER(cg.group_name) = LOWER($2)${orgId ? ' AND c.org_id = $3' : ''}`,
          orgId ? [contact_id, group, orgId] : [contact_id, group],
        );
        results[`group:${group}`] = parseInt(r.rows[0].count);
      }),

      // Employers (co-worker counts)
      ...employers.map(async (emp: { company_name: string; start_date: string | null; end_date: string | null }) => {
        const conditions: string[] = [
          `LOWER(cwh.company_name) = LOWER($1)`,
          `c.id != $2::uuid`,
        ];
        const params: unknown[] = [emp.company_name, contact_id];
        let idx = 3;

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

        if (emp.start_date) {
          const startYear = new Date(emp.start_date).getFullYear();
          conditions.push(`(cwh.end_date IS NULL OR EXTRACT(YEAR FROM cwh.end_date) >= $${idx})`);
          params.push(startYear);
          idx++;
        }
        if (emp.end_date) {
          const endYear = new Date(emp.end_date).getFullYear();
          conditions.push(`(cwh.start_date IS NULL OR EXTRACT(YEAR FROM cwh.start_date) <= $${idx})`);
          params.push(endYear);
          idx++;
        }

        const r = await query(
          `SELECT COUNT(DISTINCT c.id) AS count
           FROM contact_work_history cwh
           JOIN contacts c ON c.id = cwh.contact_id
           WHERE ${conditions.join(' AND ')}`,
          params,
        );
        results[`employer:${emp.company_name}`] = parseInt(r.rows[0].count);
      }),
    ]);

    return NextResponse.json({ counts: results });
  } catch (err) {
    console.error('[api/contacts/counts] POST error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch counts' }, { status: 500 });
  }
}