`lib/csv-parser.js`

150 lines
// Minimal statement-style CSV parser.
// Supports: any CSV with a header row that includes (case-insensitive)
//   date column   — one of: date, transaction date, posted date, post date
//   amount column — one of: amount, debit, charge, total
//   merchant col  — one of: description, merchant, payee, memo, name
//
// Returns { rows: [{ date, merchant, amount, raw }], skipped: N, errors: [] }.
// Robust to:
//   - quoted fields with commas inside
//   - $ / commas in amounts
//   - negative amounts (refunds — caller can filter)
//   - blank lines

function parseLine(line) {
  // Tiny CSV tokenizer that respects double-quoted fields with embedded commas + ""
  const fields = [];
  let cur = '';
  let inQuotes = false;
  for (let i = 0; i < line.length; i++) {
    const c = line[i];
    if (inQuotes) {
      if (c === '"' && line[i + 1] === '"') { cur += '"'; i++; }
      else if (c === '"') { inQuotes = false; }
      else cur += c;
    } else {
      if (c === '"') inQuotes = true;
      else if (c === ',') { fields.push(cur); cur = ''; }
      else cur += c;
    }
  }
  fields.push(cur);
  return fields;
}

const DATE_HEADERS = ['date', 'transaction date', 'posted date', 'post date', 'trans date', 'transaction_date'];
const AMOUNT_HEADERS = ['amount', 'debit', 'charge', 'total', 'transaction amount', 'amt'];
const MERCHANT_HEADERS = ['description', 'merchant', 'payee', 'memo', 'name', 'details', 'particulars'];

function findColumn(header, candidates) {
  const lc = header.map(h => h.trim().toLowerCase());
  for (const cand of candidates) {
    const i = lc.indexOf(cand);
    if (i >= 0) return i;
  }
  return -1;
}

function parseAmount(s) {
  if (s == null) return null;
  const cleaned = String(s).replace(/[$,\s]/g, '').replace(/[()]/g, (c) => c === '(' ? '-' : '');
  if (!cleaned || cleaned === '-' || isNaN(Number(cleaned))) return null;
  return Number(cleaned);
}

function parseDate(s) {
  if (!s) return null;
  const t = Date.parse(s);
  if (!isNaN(t)) return new Date(t);
  // Common US "MM/DD/YYYY" / "MM-DD-YYYY"
  const m = String(s).match(/^(\d{1,2})[-\/](\d{1,2})[-\/](\d{2,4})$/);
  if (m) {
    let [, mo, d, y] = m;
    if (y.length === 2) y = (Number(y) > 70 ? '19' : '20') + y;
    const t2 = Date.parse(`${y}-${mo.padStart(2,'0')}-${d.padStart(2,'0')}`);
    if (!isNaN(t2)) return new Date(t2);
  }
  return null;
}

/**
 * @param csv  raw CSV string
 * @returns { rows, skipped, errors, columns }
 */
function parseStatement(csv) {
  const lines = String(csv || '')
    .replace(/\r\n/g, '\n')
    .split('\n')
    .filter(l => l.trim().length > 0);

  if (lines.length < 2) {
    return { rows: [], skipped: 0, errors: ['empty or header-only csv'], columns: null };
  }

  const header = parseLine(lines[0]);
  let dateIdx = findColumn(header, DATE_HEADERS);
  let amountIdx = findColumn(header, AMOUNT_HEADERS);
  let merchantIdx = findColumn(header, MERCHANT_HEADERS);
  let startRow = 1;
  // 'positive' = a debit/charge is a positive number (typical header CSVs).
  // 'negative' = money out is a negative number (Wells Fargo / Quicken headerless export).
  let signMode = 'positive';

  // Headerless fallback (Wells Fargo etc.): first line is already data — field 0 is a
  // date and some later field is numeric. Money-out is negative in these exports.
  if (dateIdx < 0 || amountIdx < 0) {
    const f0 = parseLine(lines[0]);
    if (parseDate(f0[0]) && f0.some((v, i) => i > 0 && parseAmount(v) != null)) {
      dateIdx = 0;
      amountIdx = f0.findIndex((v, i) => i > 0 && parseAmount(v) != null);
      // description = the longest mostly-alphabetic field
      let best = -1, bestLen = 0;
      f0.forEach((v, i) => { const t = String(v || '').trim(); if (/[a-z]/i.test(t) && t.length > bestLen) { best = i; bestLen = t.length; } });
      merchantIdx = best;
      startRow = 0;          // no header row to skip
      signMode = 'negative'; // WF/Quicken: purchases are negative
    } else {
      return {
        rows: [], skipped: 0,
        errors: [`missing required columns (date=${dateIdx}, amount=${amountIdx}); found: ${header.join(', ')}`],
        columns: { dateIdx, amountIdx, merchantIdx },
      };
    }
  }

  const rows = [];
  const errors = [];
  let skipped = 0;

  for (let i = startRow; i < lines.length; i++) {
    const f = parseLine(lines[i]);
    const date = parseDate(f[dateIdx]);
    let amount = parseAmount(f[amountIdx]);
    if (!date || amount == null) {
      skipped += 1;
      if (errors.length < 5) errors.push(`line ${i + 1}: bad row (date=${f[dateIdx]}, amount=${f[amountIdx]})`);
      continue;
    }
    // Normalize to positive spend; drop inflows (deposits/refunds/payments).
    if (signMode === 'negative') {
      if (amount >= 0) { skipped += 1; continue; }  // deposit/credit
      amount = Math.abs(amount);
    } else if (amount <= 0) {
      skipped += 1;
      continue;
    }
    const merchantRaw = merchantIdx >= 0 ? (f[merchantIdx] || '').trim() : '';
    const merchant = merchantRaw.replace(/\s+/g, ' ').slice(0, 200) || 'Unknown';
    rows.push({
      date,
      amount,
      merchant,
      raw: { line: i + 1, fields: f, header: startRow ? header : null },
    });
  }

  return { rows, skipped, errors, columns: { dateIdx, amountIdx, merchantIdx, signMode } };
}

module.exports = { parseStatement, parseLine, parseAmount, parseDate };