backend/src/utils/csv.js

import { parse } from "csv-parse";

// Detect delimiter from header line (comma, semicolon, or tab)
function detectDelimiter(s) {
  const header = s.split(/\r?\n/)[0] || "";
  const counts = [
    [",", (header.match(/,/g) || []).length],
    [";", (header.match(/;/g) || []).length],
    ["\t", (header.match(/\t/g) || []).length],
  ];
  counts.sort((a, b) => b[1] - a[1]);
  return counts[0][1] > 0 ? counts[0][0] : ",";
}

// Normalize header keys to lower-case without BOM
function normalizeColumns(cols) {
  return cols.map((c) => c.replace(/^\uFEFF/, "").trim().toLowerCase());
}

export const parseCsvBuffer = (buf) =>
  new Promise((resolve, reject) => {
    const raw = buf.toString("utf8");
    const delimiter = detectDelimiter(raw);

    parse(
      raw,
      {
        columns: (cols) => normalizeColumns(cols),
        trim: true,
        skip_empty_lines: true,
        bom: true,
        delimiter,
        relax_column_count: true,
      },
      (err, rows) => {
        if (err) return reject(err);
        resolve(rows);
      }
    );
  });