import { parse } from 'csv-parse/sync';
import { query } from '../db';

interface ContactRow {
  email: string;
  first_name?: string;
  last_name?: string;
  [key: string]: string | undefined;
}

interface ImportResult {
  imported: number;
  skipped: number;
  errors: string[];
}

export async function importContacts(csvBuffer: Buffer, listId?: string): Promise<ImportResult> {
  const records: ContactRow[] = parse(csvBuffer, {
    columns: true,
    skip_empty_lines: true,
    trim: true,
    bom: true,
  });

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

  for (const row of records) {
    const email = row.email?.toLowerCase()?.trim();
    if (!email || !email.includes('@')) {
      errors.push(`Invalid email: "${row.email}"`);
      skipped++;
      continue;
    }

    try {
      const { first_name, last_name, email: _email, ...rest } = row;

      const customFields = Object.keys(rest).length > 0 ? rest : {};

      const result = await query<{ id: string }>(
        `INSERT INTO contacts (email, first_name, last_name, custom_fields)
         VALUES ($1, $2, $3, $4)
         ON CONFLICT (email) DO UPDATE SET
           first_name = COALESCE(EXCLUDED.first_name, contacts.first_name),
           last_name  = COALESCE(EXCLUDED.last_name,  contacts.last_name),
           updated_at = now()
         RETURNING id`,
        [email, first_name || null, last_name || null, JSON.stringify(customFields)]
      );

      if (result[0] && listId) {
        await query(
          'INSERT INTO list_contacts (list_id, contact_id) VALUES ($1, $2) ON CONFLICT DO NOTHING',
          [listId, result[0].id]
        );
      }

      imported++;
    } catch (err) {
      errors.push(`Failed to import ${email}: ${(err as Error).message}`);
      skipped++;
    }
  }

  return { imported, skipped, errors };
}