export type CsvRecipient = {
  email?: string;
  linkedinProfileUrl?: string;
  displayName?: string;
  personalization?: Record<string, string>;
};

export type RecipientTable = {
  header: string[];
  rows: string[][];
  hasHeader: boolean;
  columnLabels: string[];
  columnSamples: string[];
  /** Where the file's quoting breaks (`scanCsv`); no one is read from it. */
  error?: string;
  /** Rows with filled cells past the header's last column. */
  raggedRows?: number;
};

export type RecipientField =
  | "firstName"
  | "lastName"
  | "fullName"
  | "email"
  | "linkedin";

export type DetectedRecipientColumns = {
  firstName?: number;
  lastName?: number;
  email: number[];
  linkedin: number[];
};

export type RecipientColumnMapping = Partial<
  Record<RecipientField, number | null>
>;

export type ColumnRole =
  | "first name"
  | "last name"
  | "full name"
  | "email"
  | "linkedin"
  | "merge value"
  | "ignored";

export type CsvColumn = {
  index: number;
  label: string;
  sample: string;
  role: ColumnRole;
  mergeKey?: string;
};

export type CsvRecipientsResult = {
  recipients: CsvRecipient[];
  truncated: boolean;
  rows: number;
  noChannel: number;
  duplicates: number;
  columns: CsvColumn[];
  hasHeader: boolean;
  error?: string;
};

export const MAX_CSV_RECIPIENTS = 500;

export const MAX_CSV_CHARS = 5_000_000;

const EMAIL_RE = /[A-Za-z0-9._%+'-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}/;
const NAMED_EMAIL_RE = /^(.*?)\s*<\s*([^<>\s]+@[^<>\s]+)\s*>$/;
// A slug may be in any script (linkedin.com/in/张伟): exports don't always
// percent-encode it, and `isLinkedinProfileUrl` accepts it.
const LINKEDIN_URL_RE =
  /(?<![A-Za-z0-9.-])(?:https?:\/\/)?(?:[a-z]{1,4}\.)?linkedin\.com\/in\/[\p{L}\p{N}\-_.%]+\/?/iu;

const FILE_EXTENSION_TLDS = new Set([
  "png",
  "jpg",
  "jpeg",
  "gif",
  "svg",
  "webp",
  "bmp",
  "ico",
  "pdf",
  "csv",
  "xls",
  "xlsx",
  "doc",
  "docx",
  "ppt",
  "pptx",
  "txt",
  "zip",
  "gz",
  "tar",
  "mp3",
  "mp4",
  "mov",
  "json",
  "xml",
  "html",
  "htm",
  "js",
  "jsx",
  "ts",
  "tsx",
  "css",
  "md",
  "exe",
  "dmg",
  "pkg",
]);

const UNREACHABLE_LOCAL_PARTS = new Set([
  "noreply",
  "no-reply",
  "no_reply",
  "donotreply",
  "do-not-reply",
  "do_not_reply",
  "mailer-daemon",
  "postmaster",
]);

const RESERVED_PROFILE_SLUGS = new Set([
  "me",
  "edit",
  "search",
  "unavailable",
  "login",
  "signup",
  "in",
  "pub",
  "company",
  "school",
]);

const NON_PERSON_SLUG_TOKENS = new Set([
  "agency",
  "associates",
  "capital",
  "cfa",
  "cfp",
  "co",
  "coach",
  "coaching",
  "company",
  "consulting",
  "corp",
  "corporation",
  "cpa",
  "dds",
  "enterprises",
  "esq",
  "global",
  "group",
  "hiring",
  "holdings",
  "inc",
  "international",
  "jd",
  "labs",
  "limited",
  "llc",
  "llp",
  "ltd",
  "marketing",
  "mba",
  "md",
  "media",
  "official",
  "partners",
  "phd",
  "pmp",
  "realtor",
  "realty",
  "recruiter",
  "recruiting",
  "rn",
  "sales",
  "services",
  "software",
  "solutions",
  "studio",
  "studios",
  "team",
  "tech",
  "technologies",
  "ventures",
]);

const HONORIFIC_PREFIX_TOKENS = new Set([
  "capt",
  "col",
  "dame",
  "dr",
  "hon",
  "lt",
  "miss",
  "mr",
  "mrs",
  "ms",
  "prof",
  "professor",
  "rev",
  "sgt",
  "sir",
]);

const SLUG_DEDUPE_SUFFIX = /^(?=.*\d)[0-9a-f]+$/i;

const LOCAL_PART_RE =
  /^[A-Za-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[A-Za-z0-9!#$%&'*+/=?^_`{|}~-]+)*$/;
const DOMAIN_LABEL_RE = /^[A-Za-z0-9](?:[A-Za-z0-9-]{0,61}[A-Za-z0-9])?$/;
const TLD_RE = /^[A-Za-z]{2,24}$/;

function isPlausibleEmailAddress(raw: string): boolean {
  const email = raw.trim();
  if (!email || email.length > 254) return false;
  const at = email.lastIndexOf("@");
  if (at <= 0 || at === email.length - 1) return false;
  const local = email.slice(0, at);
  const domain = email.slice(at + 1).replace(/\.$/, "");
  if (local.length > 64 || !LOCAL_PART_RE.test(local)) return false;
  const labels = domain.split(".");
  if (
    labels.length < 2 ||
    !labels.every((label) => DOMAIN_LABEL_RE.test(label))
  ) {
    return false;
  }
  const tld = labels[labels.length - 1].toLowerCase();
  if (!TLD_RE.test(tld) || FILE_EXTENSION_TLDS.has(tld)) return false;
  const bareLocal = local.toLowerCase().split("+")[0];
  return !UNREACHABLE_LOCAL_PARTS.has(bareLocal);
}

type NormalizedLinkedinProfile = {
  profileUrl: string;
  profileKey: string;
  publicIdentifier: string;
};

function normalizeLinkedinProfileUrl(
  raw: string,
): NormalizedLinkedinProfile | null {
  const trimmed = raw.trim();
  if (!trimmed) return null;
  const expanded = /^\/?in\/[^/?#\s]+(?:[/?#].*)?$/iu.test(trimmed)
    ? `https://www.linkedin.com/${trimmed.replace(/^\/+/, "")}`
    : trimmed;
  let url: URL;
  try {
    url = new URL(
      /^[a-z][a-z0-9+.-]*:\/\//i.test(expanded)
        ? expanded
        : `https://${expanded}`,
    );
  } catch {
    return null;
  }
  const hostname = url.hostname.toLowerCase().replace(/\.$/, "");
  if (hostname !== "linkedin.com" && !hostname.endsWith(".linkedin.com")) {
    return null;
  }
  const segments = url.pathname
    .split("/")
    .filter(Boolean)
    .map((segment) => {
      try {
        return decodeURIComponent(segment);
      } catch {
        return segment;
      }
    });
  const inIndex = segments.findIndex(
    (segment, index) => segment.toLowerCase() === "in" && index <= 1,
  );
  if (inIndex < 0 || inIndex + 1 >= segments.length) return null;
  const publicIdentifier = segments[inIndex + 1].trim();
  if (
    !publicIdentifier ||
    publicIdentifier.length > 200 ||
    /[/\\?#\s]/u.test(publicIdentifier)
  ) {
    return null;
  }
  const encoded = encodeURIComponent(publicIdentifier).replace(/%2D/gi, "-");
  return {
    profileUrl: `https://www.linkedin.com/in/${encoded}`,
    profileKey: `linkedin:${publicIdentifier.toLocaleLowerCase("en-US")}`,
    publicIdentifier,
  };
}

function isLinkedinProfileUrl(raw: string): boolean {
  const normalized = normalizeLinkedinProfileUrl(raw);
  if (!normalized) return false;
  const slug = normalized.publicIdentifier;
  if (slug.length < 2 || slug.length > 100) return false;
  if (RESERVED_PROFILE_SLUGS.has(slug.toLowerCase())) return false;
  return /^[\p{L}\p{N}\-_%.]+$/u.test(slug) && /[\p{L}\p{N}]/u.test(slug);
}

function displayNameFromLinkedinSlug(
  publicIdentifier: string,
): string | undefined {
  const tokens = publicIdentifier
    .trim()
    .split(/[-_.]+/)
    .filter(Boolean);
  while (
    tokens.length > 0 &&
    SLUG_DEDUPE_SUFFIX.test(tokens[tokens.length - 1])
  ) {
    tokens.pop();
  }
  while (
    tokens.length > 0 &&
    HONORIFIC_PREFIX_TOKENS.has(tokens[0].toLowerCase())
  ) {
    tokens.shift();
  }
  if (tokens.length < 2 || tokens.length > 4) return undefined;
  if (!tokens.every((token) => /^[a-zA-Z][a-zA-Z']+$/.test(token))) {
    return undefined;
  }
  if (tokens.some((token) => NON_PERSON_SLUG_TOKENS.has(token.toLowerCase()))) {
    return undefined;
  }
  return tokens
    .map((token) => token[0].toUpperCase() + token.slice(1).toLowerCase())
    .join(" ");
}

export function extractLinkedinUrl(raw: string): string | null {
  const match = raw.match(LINKEDIN_URL_RE);
  if (!match?.[0]) return null;
  const bare = match[0].replace(/\/$/, "");
  const url = /^https?:\/\//i.test(bare) ? bare : `https://${bare}`;
  return isLinkedinProfileUrl(url) ? url : null;
}

// One person per profile, whatever host prefix (uk., www.) or encoding.
function linkedinKey(url: string): string {
  const slug = url.match(/linkedin\.com\/in\/([^/?#]+)/i)?.[1] ?? url;
  let decoded = slug;
  try {
    decoded = decodeURIComponent(slug);
  } catch {
    decoded = slug;
  }
  return `li:${decoded.toLowerCase()}`;
}

function normalizeEmail(raw: string): string {
  return raw.trim().toLowerCase();
}

export function extractEmail(raw: string): string | null {
  const trimmed = raw.trim().replace(/^["']|["']$/g, "");
  if (!trimmed) return null;
  const named = trimmed.match(NAMED_EMAIL_RE);
  const candidate = named?.[2]?.trim() ?? trimmed.match(EMAIL_RE)?.[0] ?? null;
  return candidate && isPlausibleEmailAddress(candidate) ? candidate : null;
}

function extractNameFromNamedEmail(raw: string): string | undefined {
  const trimmed = raw.trim().replace(/^["']|["']$/g, "");
  const named = trimmed.match(NAMED_EMAIL_RE);
  const name = named?.[1]?.trim().replace(/^["']|["']$/g, "");
  return name || undefined;
}

function derivedNameFromLinkedinUrl(url: string): string | undefined {
  const slug = url.match(/\/in\/([^/?#]+)/i)?.[1];
  if (!slug) return undefined;
  let decoded = slug;
  try {
    decoded = decodeURIComponent(slug);
  } catch {
    decoded = slug;
  }
  return displayNameFromLinkedinSlug(decoded);
}

export type CsvDelimiter = "," | ";" | "\t";

export function detectDelimiter(text: string): CsvDelimiter {
  const line = text.replace(/^\uFEFF/, "").split(/\r?\n/, 1)[0] ?? "";
  const counts: Record<CsvDelimiter, number> = { ",": 0, ";": 0, "\t": 0 };
  let quoted = false;
  for (const char of line) {
    if (char === '"') quoted = !quoted;
    else if (!quoted && char in counts) counts[char as CsvDelimiter] += 1;
  }
  const best = (["\t", ";"] as const).find(
    (candidate) =>
      counts[candidate] > counts[","] &&
      counts[candidate] >= Math.max(counts[";"], counts["\t"]),
  );
  return best ?? ",";
}

// A quote opens a quoted value only at the start of a cell; a quoted value
// must close, with nothing but spaces after it. A broken quote would fold
// the rows after it into one person's cell, so the file is refused with the
// line. The same rules as the app's `scanCsv` (outreach-recipients.ts).
export function scanCsv(
  text: string,
  delimiter: CsvDelimiter = ",",
): { rows: string[][]; error?: string } {
  const rows: string[][] = [];
  let row: string[] = [];
  let cell = "";
  let inQuotes = false;
  let afterQuote = false;
  let line = 1;
  let quoteLine = 1;
  let error: string | undefined;
  const src = text.replace(/^\uFEFF/, "");
  const endCell = () => {
    row.push(cell);
    cell = "";
    afterQuote = false;
  };
  for (let i = 0; i < src.length; i++) {
    const ch = src[i];
    if (ch === "\n" || (ch === "\r" && src[i + 1] !== "\n")) line += 1;
    if (inQuotes) {
      if (ch === '"') {
        if (src[i + 1] === '"') {
          cell += '"';
          i += 1;
        } else {
          inQuotes = false;
          afterQuote = true;
        }
      } else {
        cell += ch;
      }
    } else if (ch === delimiter) {
      endCell();
    } else if (ch === "\n" || ch === "\r") {
      if (ch === "\r" && src[i + 1] === "\n") {
        i += 1;
        line += 1;
      }
      endCell();
      if (row.some((value) => value.trim() !== "")) rows.push(row);
      row = [];
    } else if (afterQuote) {
      if (ch.trim() && !error) {
        error = `Line ${line} has text after a closing quote. Put the whole value in quotes and try again.`;
      }
      if (ch.trim()) cell += ch;
    } else if (ch === '"' && !cell.trim()) {
      cell = "";
      inQuotes = true;
      quoteLine = line;
    } else {
      cell += ch;
    }
  }
  if (inQuotes && !error) {
    error = `Line ${quoteLine} opens a quote that never closes, so the rows after it can't be read. Fix the quote and try again.`;
  }
  endCell();
  if (row.some((value) => value.trim() !== "")) rows.push(row);
  return error ? { rows, error } : { rows };
}

export function splitCsvRows(
  text: string,
  delimiter: CsvDelimiter = ",",
): string[][] {
  return scanCsv(text, delimiter).rows;
}

function widestRow(rows: string[][], floor = 0): number {
  return rows.reduce((width, row) => Math.max(width, row.length), floor);
}

function cellIsExactly(cell: string, value: string): boolean {
  const bare = (text: string) =>
    text
      .trim()
      .replace(/^["']|["']$/g, "")
      .replace(/^https?:\/\//i, "")
      .toLowerCase();
  const trimmed = cell.trim().replace(/^["']|["']$/g, "");
  return bare(trimmed).startsWith(bare(value)) || NAMED_EMAIL_RE.test(trimmed);
}

type ColumnStats = {
  samples: number;
  emailHits: number;
  linkedinHits: number;
};

function columnStats(rows: string[][]): ColumnStats[] {
  const width = widestRow(rows);
  const stats: ColumnStats[] = Array.from({ length: width }, () => ({
    samples: 0,
    emailHits: 0,
    linkedinHits: 0,
  }));
  for (const row of rows) {
    row.forEach((cell, col) => {
      const value = cell.trim();
      if (!value) return;
      const stat = stats[col];
      stat.samples += 1;
      const email = extractEmail(value);
      if (email && cellIsExactly(value, email)) stat.emailHits += 1;
      const linkedin = extractLinkedinUrl(value);
      if (linkedin && cellIsExactly(value, linkedin)) stat.linkedinHits += 1;
    });
  }
  return stats;
}

function isChannelColumn(
  stat: ColumnStats | undefined,
  hits: (stat: ColumnStats) => number,
): boolean {
  return Boolean(stat && stat.samples > 0 && hits(stat) / stat.samples >= 0.5);
}

function pickChannelCell(
  row: string[],
  stats: ColumnStats[],
  extract: (value: string) => string | null,
  hits: (stat: ColumnStats) => number,
): { value: string; column: number } | null {
  let best: {
    value: string;
    column: number;
    exact: boolean;
    share: number;
  } | null = null;
  row.forEach((cell, column) => {
    const value = extract(cell);
    if (!value) return;
    const exact = cellIsExactly(cell, value);
    const stat = stats[column];
    const share = stat && stat.samples > 0 ? hits(stat) / stat.samples : 0;
    if (
      !best ||
      (exact && !best.exact) ||
      (exact === best.exact && share > best.share)
    ) {
      best = { value, column, exact, share };
    }
  });
  return best;
}

const emailHitsOf = (stat: ColumnStats) => stat.emailHits;
const linkedinHitsOf = (stat: ColumnStats) => stat.linkedinHits;

function firstRowIsHeader(row: string[]): boolean {
  return !row.some((cell) => extractEmail(cell) || extractLinkedinUrl(cell));
}

function rowPersonalization(
  row: string[],
  names: readonly string[],
  skip: (col: number) => boolean,
): Record<string, string> {
  const bag: Record<string, string> = {};
  names.forEach((name, col) => {
    if (skip(col)) return;
    const value = row[col]?.trim() ?? "";
    if (value) bag[`crm:${name}`] = value;
  });
  return bag;
}

// What each column is called as a merge value: its header, or "Column D".
// A stored variable name holds printable ASCII only (Convex refuses any
// other field name): accents drop ("Café" → "Cafe"), a header with nothing
// left ("職位") falls back to its letter, and names are unique ("Interest",
// "Interest 2"). The same rule as the app's `columnVariableNames`.
export function columnVariableNames(table: RecipientTable): string[] {
  const used = new Set<string>();
  return table.columnLabels.map((_, col) => {
    const base =
      (table.header[col] ?? "")
        .normalize("NFKD")
        .replace(/[̀-ͯ]/g, "")
        .replace(/[{}|]/g, " ")
        .replace(/[^\x20-\x7e]/g, " ")
        .replace(/\s+/g, " ")
        .trim()
        .slice(0, 100)
        .trim() || `Column ${columnLetter(col)}`;
    let name = base;
    for (let n = 2; used.has(name.toLowerCase()); n += 1) name = `${base} ${n}`;
    used.add(name.toLowerCase());
    return name;
  });
}

function findNameColumns(header: string[]): {
  first?: number;
  last?: number;
} {
  const indexOf = (pattern: RegExp) =>
    header.findIndex((cell) => pattern.test(cell.trim()));
  const first = indexOf(/^(first|given)([_\s-]*name)?$|^forename$|^fname$/i);
  const last = indexOf(/^(last|family)([_\s-]*name)?$|^surname$|^lname$/i);
  return {
    first: first >= 0 ? first : undefined,
    last: last >= 0 ? last : undefined,
  };
}

const FULL_NAME_HEADER = /^(full[_\s-]*name|name|contact([_\s-]*name)?)$/i;

export function detectFullNameColumn(
  table: RecipientTable,
): number | undefined {
  if (!table.hasHeader) return undefined;
  const stats = columnStats(table.rows);
  const index = table.header.findIndex(
    (cell, col) =>
      FULL_NAME_HEADER.test(cell.trim()) &&
      !isChannelColumn(stats[col], emailHitsOf) &&
      !isChannelColumn(stats[col], linkedinHitsOf),
  );
  return index >= 0 ? index : undefined;
}

export function withDetectedFullName(
  table: RecipientTable,
  mapping: RecipientColumnMapping,
): RecipientColumnMapping {
  if (mapping.fullName !== undefined) return mapping;
  const names = resolveNameColumns(detectRecipientColumns(table), mapping);
  if (names.first !== undefined || names.last !== undefined) return mapping;
  const full = detectFullNameColumn(table);
  return full === undefined ? mapping : { ...mapping, fullName: full };
}

function rowDisplayName(
  row: string[],
  columns: { first?: number; last?: number; full?: number },
): string | undefined {
  if (columns.full != null) return row[columns.full]?.trim() || undefined;
  const first = columns.first != null ? row[columns.first]?.trim() : "";
  const last =
    columns.last != null && columns.last !== columns.first
      ? row[columns.last]?.trim()
      : "";
  const combined = [first, last].filter(Boolean).join(" ");
  return combined || undefined;
}

export function columnLetter(col: number): string {
  let n = col;
  let out = "";
  do {
    out = String.fromCharCode(65 + (n % 26)) + out;
    n = Math.floor(n / 26) - 1;
  } while (n >= 0);
  return out;
}

export function parseRecipientTable(
  csvText: string,
  delimiter: CsvDelimiter = ",",
): RecipientTable {
  const { rows, error } = scanCsv(csvText, delimiter);
  const first = rows[0] ?? [];
  const hasHeader = rows.length > 0 && firstRowIsHeader(first);
  const header = hasHeader ? first.map((cell) => cell.trim()) : [];
  const dataRows = hasHeader ? rows.slice(1) : rows;
  const width = widestRow(dataRows, header.length);
  const raggedRows = hasHeader
    ? dataRows.filter((row) =>
        row.slice(header.length).some((cell) => cell.trim()),
      ).length
    : 0;
  const columnLabels = Array.from(
    { length: width },
    (_, col) => header[col] || columnLetter(col),
  );
  const columnSamples = Array.from({ length: width }, (_, col) => {
    const sample =
      dataRows
        .map((row) => row[col]?.trim() ?? "")
        .find((cell) => cell.length > 0) ?? "";
    return sample.length > 40 ? `${sample.slice(0, 39)}…` : sample;
  });
  return {
    header,
    rows: dataRows,
    hasHeader,
    columnLabels,
    columnSamples,
    ...(error ? { error } : {}),
    ...(raggedRows ? { raggedRows } : {}),
  };
}

const EMAIL_HEADER_RE =
  /^((work|business|primary|professional|personal)[\s_-]*)?e-?mail([\s_-]*address)?$/i;
const LINKEDIN_HEADER_RE =
  /^(linkedin([\s_-]*(profile|url|link|profile[\s_-]*(url|link)))?|(linkedin[\s_-]*)?profile[\s_-]*(url|link))$/i;

// Best first: a column whose header names the channel ("Email") before one
// that only holds more of it ("Backup contact"), then most-filled, leftmost.
export function detectRecipientColumns(
  table: RecipientTable,
): DetectedRecipientColumns {
  const stats = columnStats(table.rows);
  const rank = (hits: (stat: ColumnStats) => number, header: RegExp) =>
    stats
      .map((stat, col) => ({
        col,
        hits: hits(stat),
        named: header.test(table.header[col]?.trim() ?? "") ? 1 : 0,
      }))
      .filter((entry) => entry.hits > 0)
      .sort((a, b) => b.named - a.named || b.hits - a.hits || a.col - b.col)
      .map((entry) => entry.col);
  const names = table.hasHeader ? findNameColumns(table.header) : {};
  return {
    firstName: names.first,
    lastName: names.last,
    email: rank(emailHitsOf, EMAIL_HEADER_RE),
    linkedin: rank(linkedinHitsOf, LINKEDIN_HEADER_RE),
  };
}

function resolveNameColumns(
  detected: DetectedRecipientColumns,
  mapping: RecipientColumnMapping,
): { first?: number; last?: number; full?: number } {
  const pick = (field: "firstName" | "lastName") =>
    mapping[field] === null ? undefined : (mapping[field] ?? detected[field]);
  if (
    typeof mapping.fullName === "number" &&
    typeof mapping.firstName !== "number" &&
    typeof mapping.lastName !== "number"
  )
    return { full: mapping.fullName };
  return { first: pick("firstName"), last: pick("lastName") };
}

export function recipientsFromTable(
  table: RecipientTable,
  mapping: RecipientColumnMapping = {},
): CsvRecipientsResult {
  const empty = (error: string): CsvRecipientsResult => ({
    recipients: [],
    truncated: false,
    rows: table.rows.length,
    noChannel: 0,
    duplicates: 0,
    columns: [],
    hasHeader: table.hasHeader,
    error,
  });
  if (table.error) return empty(table.error);
  if (table.rows.length === 0 && !table.hasHeader) {
    return empty("This file is empty.");
  }
  const stats = columnStats(table.rows);
  const columnNames = columnVariableNames(table);
  const detected = detectRecipientColumns(table);
  const nameColumns = resolveNameColumns(detected, mapping);
  const preferred = (field: "email" | "linkedin") =>
    typeof mapping[field] === "number" ? (mapping[field] as number) : undefined;
  const pickChannel = (
    row: string[],
    field: "email" | "linkedin",
    extract: (value: string) => string | null,
    hits: (stat: ColumnStats) => number,
  ) => {
    if (mapping[field] === null) return null;
    const chosen = preferred(field);
    if (chosen !== undefined) {
      const value = extract(row[chosen] ?? "");
      if (value) return { value, column: chosen };
    }
    return pickChannelCell(row, stats, extract, hits);
  };
  const consumed = (col: number) =>
    isChannelColumn(stats[col], emailHitsOf) ||
    isChannelColumn(stats[col], linkedinHitsOf) ||
    preferred("email") === col ||
    preferred("linkedin") === col ||
    nameColumns.first === col ||
    nameColumns.last === col;
  const recipients: CsvRecipient[] = [];
  const seen = new Map<string, CsvRecipient>();
  let noChannel = 0;
  let duplicates = 0;
  for (const row of table.rows) {
    const email = pickChannel(row, "email", extractEmail, emailHitsOf);
    const linkedin = pickChannel(
      row,
      "linkedin",
      extractLinkedinUrl,
      linkedinHitsOf,
    );
    const emailRaw = email ? (row[email.column] ?? "") : "";
    const address = extractEmail(emailRaw);
    const linkedinUrl = linkedin?.value ?? null;
    if (!address && !linkedinUrl) {
      noChannel += 1;
      continue;
    }
    const emailKey = address ? normalizeEmail(address) : undefined;
    const profileKey = linkedinUrl ? linkedinKey(linkedinUrl) : undefined;
    const personalization = table.hasHeader
      ? rowPersonalization(row, columnNames, consumed)
      : undefined;
    const name =
      rowDisplayName(row, nameColumns)?.trim() ||
      extractNameFromNamedEmail(emailRaw) ||
      (linkedinUrl ? derivedNameFromLinkedinUrl(linkedinUrl) : undefined);
    const matches = new Set(
      [emailKey, profileKey].flatMap((key) => {
        const person = key ? seen.get(key) : undefined;
        return person ? [person] : [];
      }),
    );
    if (matches.size > 0) {
      // The same person again: keep what this row adds (their LinkedIn, a
      // name, a column that was blank), never what the first row said.
      duplicates += 1;
      const [existing] = matches;
      if (matches.size > 1 || !existing) continue;
      if (address && emailKey && !existing.email) {
        existing.email = address;
        seen.set(emailKey, existing);
      }
      if (linkedinUrl && profileKey && !existing.linkedinProfileUrl) {
        existing.linkedinProfileUrl = linkedinUrl;
        seen.set(profileKey, existing);
      }
      if (name && !existing.displayName) existing.displayName = name;
      if (personalization && Object.keys(personalization).length > 0) {
        existing.personalization = {
          ...personalization,
          ...existing.personalization,
        };
      }
      continue;
    }
    const person: CsvRecipient = {
      ...(address ? { email: address } : {}),
      ...(linkedinUrl ? { linkedinProfileUrl: linkedinUrl } : {}),
      ...(name ? { displayName: name } : {}),
      ...(personalization && Object.keys(personalization).length > 0
        ? { personalization }
        : {}),
    };
    if (emailKey) seen.set(emailKey, person);
    if (profileKey) seen.set(profileKey, person);
    recipients.push(person);
  }
  // A channel switched off with `--map email=none` reads nothing from its
  // column, so the column is reported ignored, not as that channel.
  const channelColumn = (
    field: "email" | "linkedin",
    index: number,
    hits: (stat: ColumnStats) => number,
  ) =>
    preferred(field) === index || isChannelColumn(stats[index], hits)
      ? mapping[field] === null
        ? ("ignored" as const)
        : field
      : undefined;
  const columns: CsvColumn[] = table.columnLabels.map((label, index) => {
    const role: ColumnRole =
      nameColumns.first === index
        ? "first name"
        : nameColumns.last === index
          ? "last name"
          : nameColumns.full === index
            ? "full name"
            : (channelColumn("email", index, emailHitsOf) ??
              channelColumn("linkedin", index, linkedinHitsOf) ??
              (table.hasHeader ? "merge value" : "ignored"));
    return {
      index,
      label,
      sample: table.columnSamples[index] ?? "",
      role,
      ...((role === "merge value" || role === "full name") && table.hasHeader
        ? { mergeKey: `crm:${columnNames[index]}` }
        : {}),
    };
  });
  if (recipients.length === 0) {
    return {
      ...empty(
        "No email addresses or LinkedIn profile links in this file. Each person needs at least one.",
      ),
      noChannel,
      duplicates,
      columns,
    };
  }
  return {
    recipients: recipients.slice(0, MAX_CSV_RECIPIENTS),
    truncated: recipients.length > MAX_CSV_RECIPIENTS,
    rows: table.rows.length,
    noChannel,
    duplicates,
    columns,
    hasHeader: table.hasHeader,
  };
}

const MAP_FIELDS: Record<string, RecipientField> = {
  email: "email",
  emailaddress: "email",
  workemail: "email",
  linkedin: "linkedin",
  linkedinurl: "linkedin",
  linkedinprofile: "linkedin",
  linkedinprofileurl: "linkedin",
  profileurl: "linkedin",
  first: "firstName",
  firstname: "firstName",
  given: "firstName",
  givenname: "firstName",
  last: "lastName",
  lastname: "lastName",
  surname: "lastName",
  family: "lastName",
  familyname: "lastName",
  name: "fullName",
  fullname: "fullName",
  full: "fullName",
  contact: "fullName",
  contactname: "fullName",
  displayname: "fullName",
};

export const COLUMN_MAP_USAGE =
  'e.g. --map "email=Work Email" --map "linkedin=Profile URL" --map name="Full Name" (or --map first="First Name" --map last=Surname). Fields: email, linkedin, name (the whole name in one column), first, last. A column is its header or its spreadsheet letter (A, B, ...); none switches a field off.';

function letterIndex(letters: string): number {
  let index = 0;
  for (const char of letters.toUpperCase())
    index = index * 26 + (char.charCodeAt(0) - 64);
  return index - 1;
}

function resolveColumn(
  table: RecipientTable,
  reference: string,
  spec: string,
): number | null {
  if (/^(none|off|skip)$/i.test(reference)) return null;
  const lowered = reference.toLowerCase();
  const byLabel = table.columnLabels.findIndex(
    (label) => label.trim().toLowerCase() === lowered,
  );
  if (byLabel >= 0) return byLabel;
  const width = table.columnLabels.length;
  if (/^[A-Za-z]{1,3}$/.test(reference)) {
    const index = letterIndex(reference);
    if (index < width) return index;
  }
  if (/^\d+$/.test(reference)) {
    const index = Number(reference) - 1;
    if (index >= 0 && index < width) return index;
  }
  throw new Error(
    `--map "${spec}": no column called "${reference}". Columns: ${table.columnLabels.join(", ")}.`,
  );
}

export function parseColumnMap(
  specs: readonly string[],
  table: RecipientTable,
): RecipientColumnMapping {
  const mapping: RecipientColumnMapping = {};
  for (const spec of specs) {
    const at = spec.indexOf("=");
    const name = at === -1 ? "" : spec.slice(0, at).trim();
    const reference = at === -1 ? "" : spec.slice(at + 1).trim();
    const field = MAP_FIELDS[name.toLowerCase().replace(/[^a-z0-9]/g, "")];
    if (!field || !reference) {
      throw new Error(
        `--map "${spec}" needs a field and a column, ${COLUMN_MAP_USAGE}`,
      );
    }
    if (field in mapping) {
      throw new Error(`--map sets ${name} twice; keep one column per field.`);
    }
    mapping[field] = resolveColumn(table, reference, spec);
  }
  if (
    typeof mapping.fullName === "number" &&
    (typeof mapping.firstName === "number" ||
      typeof mapping.lastName === "number")
  ) {
    throw new Error(
      "--map name is the whole name in one column; use it or --map first/last, not both.",
    );
  }
  return mapping;
}