← back to Norma

app/api/contacts/import/route.ts

155 lines

import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { getOrgId } from '@/lib/orgId';
import { matchContactToOrgs } from '@/lib/contact-matcher';
import { createContactLinks } from '@/lib/contact-links';

/**
 * POST /api/contacts/import
 * Import contacts from LinkedIn CSV export.
 * Accepts multipart/form-data with a CSV file.
 *
 * LinkedIn CSV columns: First Name, Last Name, Email Address, Company, Position, Connected On
 */
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 formData = await request.formData();
    const file = formData.get('file') as File | null;

    if (!file) {
      return NextResponse.json({ error: 'No file uploaded. Send a CSV file as "file" field.' }, { status: 400 });
    }

    const text = await file.text();
    const lines = text.split('\n').map(l => l.trim()).filter(Boolean);

    if (lines.length < 2) {
      return NextResponse.json({ error: 'CSV file is empty or has no data rows' }, { status: 400 });
    }

    // Parse header to find column indices
    const header = parseCSVRow(lines[0]);
    const colMap: Record<string, number> = {};
    const aliases: Record<string, string[]> = {
      first_name: ['first name', 'firstname', 'first_name'],
      last_name: ['last name', 'lastname', 'last_name'],
      email: ['email address', 'email', 'e-mail'],
      company: ['company', 'organization', 'employer'],
      position: ['position', 'title', 'job title', 'role'],
      connected_on: ['connected on', 'connected_on', 'connection date', 'date'],
    };

    for (const [key, names] of Object.entries(aliases)) {
      const idx = header.findIndex(h => names.includes(h.toLowerCase().trim()));
      if (idx !== -1) colMap[key] = idx;
    }

    if (colMap.first_name === undefined || colMap.last_name === undefined) {
      return NextResponse.json({
        error: 'CSV must have "First Name" and "Last Name" columns. Found: ' + header.join(', '),
      }, { status: 400 });
    }

    let imported = 0;
    let skipped = 0;
    let orgMatches = 0;
    const errors: string[] = [];

    for (let i = 1; i < lines.length; i++) {
      const cols = parseCSVRow(lines[i]);
      const firstName = cols[colMap.first_name]?.trim();
      const lastName = cols[colMap.last_name]?.trim();

      if (!firstName || !lastName) { skipped++; continue; }

      const email = colMap.email !== undefined ? cols[colMap.email]?.trim() || null : null;
      const company = colMap.company !== undefined ? cols[colMap.company]?.trim() || null : null;
      const position = colMap.position !== undefined ? cols[colMap.position]?.trim() || null : null;
      const connectedOn = colMap.connected_on !== undefined ? parseDate(cols[colMap.connected_on]?.trim()) : null;

      try {
        const result = await query(
          `INSERT INTO contacts (first_name, last_name, email, company, position, connected_on, source${orgId ? ', org_id' : ''})
           VALUES ($1, $2, $3, $4, $5, $6, 'linkedin_csv'${orgId ? ', $7' : ''})
           ON CONFLICT (LOWER(first_name), LOWER(last_name), LOWER(COALESCE(email, '')))
           DO NOTHING
           RETURNING id`,
          orgId ? [firstName, lastName, email, company, position, connectedOn, orgId] : [firstName, lastName, email, company, position, connectedOn],
        );

        if (result.rowCount && result.rowCount > 0) {
          imported++;
          const contactId = result.rows[0].id;

          // Auto-match to organizations
          const matches = await matchContactToOrgs(contactId);
          if (matches.matched_org_id) orgMatches++;

          // Create mind map links
          await createContactLinks(contactId);
        } else {
          skipped++;
        }
      } catch (err) {
        errors.push(`Row ${i + 1}: ${(err as Error).message}`);
        skipped++;
      }
    }

    return NextResponse.json({
      imported,
      skipped,
      org_matches: orgMatches,
      total_rows: lines.length - 1,
      errors: errors.slice(0, 10),
    });
  } catch (err) {
    console.error('[api/contacts/import] Error:', (err as Error).message);
    return NextResponse.json({ error: 'Import failed: ' + (err as Error).message }, { status: 500 });
  }
}

/** Parse a CSV row handling quoted fields */
function parseCSVRow(line: string): string[] {
  const result: string[] = [];
  let current = '';
  let inQuotes = false;

  for (let i = 0; i < line.length; i++) {
    const ch = line[i];
    if (ch === '"') {
      if (inQuotes && line[i + 1] === '"') {
        current += '"';
        i++;
      } else {
        inQuotes = !inQuotes;
      }
    } else if (ch === ',' && !inQuotes) {
      result.push(current);
      current = '';
    } else {
      current += ch;
    }
  }
  result.push(current);
  return result;
}

/** Parse various date formats from LinkedIn CSV */
function parseDate(dateStr: string | undefined): string | null {
  if (!dateStr) return null;
  // LinkedIn uses formats like "15 Jan 2024", "01/15/2024", "2024-01-15"
  try {
    const d = new Date(dateStr);
    if (isNaN(d.getTime())) return null;
    return d.toISOString().split('T')[0];
  } catch {
    return null;
  }
}