csv-core.js

export const CSV_LIMITS = Object.freeze({
  maxBytes: 1024 * 1024,
  maxRows: 10000,
  maxColumns: 100,
  maxCellBytes: 100 * 1024,
});

export const SYNTHETIC_CSV_SAMPLE = [
  "customer_id,name,email,phone,note",
  '0001," 김민수 ",minsu@example.test,010-1000-1000," first contact "',
  "0001,김민수,minsu@example.test,010-1000-1000,first contact",
  '0002,Ada Lovelace,ada@example.test,00123,"Asked, ""Please call""."',
  '0003,Grace Hopper,grace@example.test,00045,"Line one\nLine two"',
  "0004,박지영,shared@example.test,010-2222-3333,Primary record",
  "0005,Park Ji-young,shared@example.test,010-9999-8888,Changed phone",
  "0006,이서준,,010-3333-4444,Missing email key",
  "0007,Formula Cell,formula@example.test,=2+3,Formula-like phone",
  "0008,Plus Cell,plus@example.test,+82 10 5555 0000,International prefix",
  "0009,Minus Cell,minus@example.test,-42,Negative-looking text",
  "0010,At Cell,at@example.test,@handle,Handle-like text",
  '0011,"Lee, Hana",quote@example.test,010-7777-8888,"He said ""hello"""',
  "0012,Leading Zero,zero@example.test,0000123,Keep leading zero",
  '0013,Tab Risk,tab@example.test,010-8888-9999,"\t=SUM(A1:A2)"',
].join("\r\n") + "\r\n";

export const COUNT_DEFINITIONS = Object.freeze({
  inputRows: "Data rows parsed from the input, excluding the header.",
  cleanedRows: "Rows written to cleaned.csv, including exact duplicates when Keep all is selected.",
  quarantinedRows: "Rows withheld for a missing key or a conflicting key and written to review-needed.csv.",
  collapsedRows: "Non-representative exact duplicate rows omitted only by the Collapse policy.",
  exactDuplicateGroups: "Non-missing-key groups of two or more rows with identical processed cell values, including groups inside a conflict.",
  exactDuplicateRows: "All rows participating in those exact duplicate groups, not only the extra copies.",
  conflictGroups: "Non-missing key groups containing more than one distinct processed row.",
  conflictRows: "All rows in conflict groups, including any exact copies inside them.",
  missingKeyRows: "Rows where at least one selected processed key cell is empty.",
  trimmedCells: "Cells whose processed value changed when optional edge trimming was applied.",
});

export const REPORT_LIMITATIONS = Object.freeze([
  "Exact matching only: no fuzzy matching, case folding, phone normalization, or type inference.",
  "Internal whitespace, letter case, numeric-looking text, and leading zeroes are preserved.",
  "Collapsed rows remain documented in the JSON report but are not written to review-needed.csv.",
  "Spreadsheet-risk protection is conservative and export-only. It does not guarantee safety in every spreadsheet application.",
  "Values beginning with a minus sign, including negative numbers, receive an apostrophe prefix in CSV exports.",
]);

const encoder = new TextEncoder();

export function utf8ByteLength(value) {
  return encoder.encode(String(value)).length;
}

function csvError(message, recordNumber) {
  const suffix = recordNumber ? ` (record ${recordNumber})` : "";
  return new Error(`${message}${suffix}`);
}

export function decodeUtf8(bytes, limits = CSV_LIMITS) {
  const view = bytes instanceof Uint8Array ? bytes : new Uint8Array(bytes);
  if (view.byteLength > limits.maxBytes) {
    throw new Error("CSV exceeds the 1 MiB UTF-8 input limit.");
  }
  try {
    return new TextDecoder("utf-8", { fatal: true }).decode(view);
  } catch {
    throw new Error("The selected file is not valid UTF-8.");
  }
}

export function parseCsv(input, limits = CSV_LIMITS) {
  if (typeof input !== "string") throw new TypeError("CSV input must be text.");
  const inputBytes = utf8ByteLength(input);
  if (inputBytes > limits.maxBytes) throw new Error("CSV exceeds the 1 MiB UTF-8 input limit.");

  const text = input.startsWith("\uFEFF") ? input.slice(1) : input;
  if (text.length === 0) throw new Error("Enter CSV text or load the synthetic sample first.");

  const records = [];
  let record = [];
  let field = "";
  let state = "start";
  let justEndedRecord = false;

  const finishField = () => {
    if (utf8ByteLength(field) > limits.maxCellBytes) {
      throw csvError("A cell exceeds the 100 KiB limit", records.length + 1);
    }
    record.push(field);
    if (record.length > limits.maxColumns) {
      throw csvError("A row exceeds the 100-column limit", records.length + 1);
    }
    field = "";
    state = "start";
  };

  const finishRecord = () => {
    finishField();
    records.push(record);
    if (records.length - 1 > limits.maxRows) {
      throw new Error("CSV exceeds the 10,000 data-row limit.");
    }
    record = [];
    justEndedRecord = true;
  };

  for (let index = 0; index < text.length; index++) {
    const char = text[index];
    justEndedRecord = false;

    if (state === "quoted") {
      if (char === '"') {
        if (text[index + 1] === '"') {
          field += '"';
          index++;
        } else {
          state = "after-quote";
        }
      } else {
        field += char;
      }
      continue;
    }

    if (state === "after-quote") {
      if (char === ",") {
        finishField();
      } else if (char === "\n") {
        finishRecord();
      } else if (char === "\r" && text[index + 1] === "\n") {
        finishRecord();
        index++;
      } else {
        throw csvError("Unexpected character after a closing quote", records.length + 1);
      }
      continue;
    }

    if (state === "start" && char === '"') {
      state = "quoted";
    } else if (char === '"') {
      throw csvError("A quote inside an unquoted cell is not valid CSV", records.length + 1);
    } else if (char === ",") {
      finishField();
    } else if (char === "\n") {
      finishRecord();
    } else if (char === "\r" && text[index + 1] === "\n") {
      finishRecord();
      index++;
    } else if (char === "\r") {
      throw csvError("Bare carriage returns are not supported; use LF or CRLF", records.length + 1);
    } else {
      field += char;
      state = "unquoted";
    }
  }

  if (state === "quoted") throw csvError("Unclosed quoted cell", records.length + 1);
  if (!justEndedRecord) finishRecord();
  if (records.length === 0 || (records.length === 1 && records[0].length === 1 && records[0][0] === "")) {
    throw new Error("CSV must contain a non-empty header row.");
  }

  const headers = records[0];
  if (headers.some((header) => header === "")) throw new Error("CSV headers cannot be empty.");
  if (new Set(headers).size !== headers.length) throw new Error("CSV headers must be unique.");

  const rows = records.slice(1).map((values, index) => {
    if (values.length !== headers.length) {
      throw csvError(`Expected ${headers.length} columns but found ${values.length}`, index + 2);
    }
    return { sourceRow: index + 2, values };
  });

  return { headers, rows, inputBytes };
}

function validateOptions(parsed, options) {
  const keyColumns = options?.keyColumns;
  if (!Array.isArray(keyColumns) || keyColumns.length === 0) {
    throw new Error("Select at least one key column.");
  }
  if (new Set(keyColumns).size !== keyColumns.length || keyColumns.some((column) => !parsed.headers.includes(column))) {
    throw new Error("Selected key columns must be unique input headers.");
  }
  if (!["keep-all", "collapse"].includes(options.duplicatePolicy)) {
    throw new Error("Choose Keep all or Collapse for exact duplicates.");
  }
}

function fingerprint(values) {
  return JSON.stringify(values);
}

export function processCsv(parsed, options) {
  validateOptions(parsed, options);
  const keyIndexes = options.keyColumns.map((column) => parsed.headers.indexOf(column));
  const processed = parsed.rows.map((row) => {
    const values = row.values.map((value) => options.trimCells ? value.trim() : value);
    const changes = [];
    values.forEach((value, columnIndex) => {
      if (value !== row.values[columnIndex]) {
        changes.push({ type: "trim", column: parsed.headers[columnIndex], before: row.values[columnIndex], after: value });
      }
    });
    const keyValues = keyIndexes.map((index) => values[index]);
    return {
      sourceRow: row.sourceRow,
      originalValues: [...row.values],
      values,
      keyValues,
      keyFingerprint: fingerprint(keyValues),
      rowFingerprint: fingerprint(values),
      missingKey: keyValues.some((value) => value === ""),
      changes,
    };
  });

  const keyGroups = new Map();
  for (const row of processed) {
    if (row.missingKey) continue;
    if (!keyGroups.has(row.keyFingerprint)) keyGroups.set(row.keyFingerprint, []);
    keyGroups.get(row.keyFingerprint).push(row);
  }

  const conflictKeys = new Set();
  const exactGroupByRow = new Map();
  let exactDuplicateGroups = 0;
  let exactDuplicateRows = 0;
  let conflictGroups = 0;
  let conflictRows = 0;
  let duplicateGroupNumber = 0;

  for (const [key, rows] of keyGroups) {
    const byRow = new Map();
    for (const row of rows) {
      if (!byRow.has(row.rowFingerprint)) byRow.set(row.rowFingerprint, []);
      byRow.get(row.rowFingerprint).push(row);
    }
    if (byRow.size > 1) {
      conflictKeys.add(key);
      conflictGroups++;
      conflictRows += rows.length;
    }
    for (const duplicates of byRow.values()) {
      if (duplicates.length < 2) continue;
      exactDuplicateGroups++;
      exactDuplicateRows += duplicates.length;
      duplicateGroupNumber++;
      duplicates.forEach((row, index) => exactGroupByRow.set(row, {
        id: `exact-${duplicateGroupNumber}`,
        position: index + 1,
        size: duplicates.length,
      }));
    }
  }

  const cleaned = [];
  const quarantined = [];
  const collapsed = [];
  const rowReports = [];
  let missingKeyRows = 0;
  let trimmedCells = 0;

  for (const row of processed) {
    trimmedCells += row.changes.length;
    const exactDuplicate = exactGroupByRow.get(row) ?? null;
    let classification;
    let reason = null;

    if (row.missingKey) {
      classification = "quarantined";
      reason = `missing key: ${options.keyColumns.filter((_, index) => row.keyValues[index] === "").join(", ")}`;
      missingKeyRows++;
      quarantined.push({ ...row, reason });
    } else if (conflictKeys.has(row.keyFingerprint)) {
      classification = "quarantined";
      reason = `conflict: same ${options.keyColumns.join(" + ")} key has different processed rows`;
      quarantined.push({ ...row, reason });
    } else if (options.duplicatePolicy === "collapse" && exactDuplicate && exactDuplicate.position > 1) {
      classification = "collapsed";
      reason = `exact duplicate of source row ${keyGroups.get(row.keyFingerprint)[0].sourceRow}`;
      collapsed.push({ ...row, reason });
    } else {
      classification = "cleaned";
      cleaned.push(row);
    }

    rowReports.push({
      sourceRow: row.sourceRow,
      originalValues: row.originalValues,
      processedValues: row.values,
      keyValues: row.keyValues,
      classification,
      reason,
      exactDuplicate,
      changes: row.changes,
    });
  }

  const counts = {
    inputRows: processed.length,
    cleanedRows: cleaned.length,
    quarantinedRows: quarantined.length,
    collapsedRows: collapsed.length,
    exactDuplicateGroups,
    exactDuplicateRows,
    conflictGroups,
    conflictRows,
    missingKeyRows,
    trimmedCells,
  };
  const conserved = counts.inputRows === counts.cleanedRows + counts.quarantinedRows + counts.collapsedRows;
  if (!conserved) throw new Error("Internal row conservation check failed.");

  return { headers: parsed.headers, cleaned, quarantined, collapsed, rowReports, counts, conserved };
}

export function protectSpreadsheetCell(value) {
  const text = String(value);
  return (/^ *[=+\-@]/u.test(text) || /^[\t\r\n]/u.test(text)) ? `'${text}` : text;
}

function quoteCsvCell(value) {
  const text = String(value);
  return /[",\r\n]/u.test(text) ? `"${text.replaceAll('"', '""')}"` : text;
}

export function serializeCsv(headers, rows, rowSources = []) {
  const protections = [];
  const exportedHeaders = headers.map((value, columnIndex) => {
    const exported = protectSpreadsheetCell(value);
    if (exported !== value) protections.push({ location: "header", column: value, columnIndex, processedValue: value, exportedValue: exported });
    return exported;
  });
  const exportedRows = rows.map((values, rowIndex) => values.map((value, columnIndex) => {
    const exported = protectSpreadsheetCell(value);
    if (exported !== value) protections.push({
      location: "cell",
      sourceRow: rowSources[rowIndex] ?? null,
      column: headers[columnIndex],
      columnIndex,
      processedValue: value,
      exportedValue: exported,
    });
    return exported;
  }));
  const lines = [exportedHeaders, ...exportedRows].map((values) => values.map(quoteCsvCell).join(","));
  return { csv: `\uFEFF${lines.join("\r\n")}\r\n`, exportedHeaders, exportedRows, protections };
}

function uniqueMetadataHeader(base, headers) {
  let candidate = base;
  while (headers.includes(candidate)) candidate = `_${candidate}`;
  return candidate;
}

export function buildDeliverables(parsed, processed, options, metadata = {}) {
  const cleanedExport = serializeCsv(
    parsed.headers,
    processed.cleaned.map((row) => row.values),
    processed.cleaned.map((row) => row.sourceRow),
  );
  const sourceHeader = uniqueMetadataHeader("_fixproof_source_row", parsed.headers);
  const reasonHeader = uniqueMetadataHeader("_fixproof_reason", [...parsed.headers, sourceHeader]);
  const reviewHeaders = [sourceHeader, reasonHeader, ...parsed.headers];
  const reviewRows = processed.quarantined.map((row) => [String(row.sourceRow), row.reason, ...row.values]);
  const reviewExport = serializeCsv(
    reviewHeaders,
    reviewRows,
    processed.quarantined.map((row) => row.sourceRow),
  );

  const cleanedBySource = new Map(processed.cleaned.map((row, index) => [row.sourceRow, cleanedExport.exportedRows[index]]));
  const reviewBySource = new Map(processed.quarantined.map((row, index) => [row.sourceRow, reviewExport.exportedRows[index].slice(2)]));
  const report = {
    schemaVersion: "fixproof-csv-report/1",
    generatedAt: metadata.generatedAt ?? new Date().toISOString(),
    source: {
      name: metadata.sourceName ?? "pasted CSV",
      inputBytes: parsed.inputBytes,
      containsOriginalValues: true,
      sourceRowMeaning: "CSV record number with the header as record 1; quoted physical line breaks do not increment it.",
      note: "Original values may contain sensitive data. This report is generated locally and should be handled like the source CSV.",
    },
    headers: [...parsed.headers],
    policy: {
      keyColumns: [...options.keyColumns],
      trimCells: Boolean(options.trimCells),
      exactDuplicates: options.duplicatePolicy,
      matching: "exact processed values only",
    },
    limits: { ...CSV_LIMITS },
    counts: { ...processed.counts },
    countDefinitions: { ...COUNT_DEFINITIONS },
    conservation: {
      equation: "inputRows = cleanedRows + quarantinedRows + collapsedRows",
      valid: processed.conserved,
    },
    rows: processed.rowReports.map((row) => ({
      ...row,
      exportedValues: cleanedBySource.get(row.sourceRow) ?? reviewBySource.get(row.sourceRow) ?? null,
    })),
    exports: {
      cleaned: {
        fileName: "cleaned.csv",
        rowCount: processed.cleaned.length,
        headers: cleanedExport.exportedHeaders,
        protections: cleanedExport.protections,
      },
      reviewNeeded: {
        fileName: "review-needed.csv",
        rowCount: processed.quarantined.length,
        metadataHeaders: [sourceHeader, reasonHeader],
        headers: reviewExport.exportedHeaders,
        protections: reviewExport.protections,
      },
    },
    limitations: [...REPORT_LIMITATIONS],
  };

  return {
    cleanedCsv: cleanedExport.csv,
    reviewCsv: reviewExport.csv,
    report,
    reportJson: `${JSON.stringify(report, null, 2)}\n`,
  };
}

export function runCsvCleanup(input, options, metadata = {}) {
  const parsed = parseCsv(input);
  const processed = processCsv(parsed, options);
  const deliverables = buildDeliverables(parsed, processed, options, metadata);
  return { parsed, processed, deliverables };
}