`lib/spend-reviews/adapters/csv.js`

123 lines
'use strict';
/**
 * CSV ingestion adapter — parse a bank/credit-card statement export into spend
 * records. Merchant strings are messy ("SQ *BLUE BOTTLE OAKLAND CA", "AMZN
 * Mktp US*2X4YZ"); we capture the raw description as `merchant` and let
 * store.normalizeMerchant clean it downstream. Zero dependencies.
 *
 * interface: module.exports = { ingest };  ingest(opts) -> {ok, items, meta}
 *   opts.csv       = raw CSV text (from an upload), OR
 *   (absent)       = read every *.csv in data/csv-drop/
 */
const fs = require('fs');
const path = require('path');
const cfg = require('../config');

// --- tiny RFC-4180-ish CSV parser (quoted fields, embedded commas/newlines) ---
function parseCsv(text) {
  const rows = [];
  let row = [], field = '', i = 0, inQ = false;
  const s = text.replace(/\r\n/g, '\n').replace(/\r/g, '\n');
  while (i < s.length) {
    const c = s[i];
    if (inQ) {
      if (c === '"') { if (s[i + 1] === '"') { field += '"'; i += 2; continue; } inQ = false; i++; continue; }
      field += c; i++; continue;
    }
    if (c === '"') { inQ = true; i++; continue; }
    if (c === ',') { row.push(field); field = ''; i++; continue; }
    if (c === '\n') { row.push(field); rows.push(row); row = []; field = ''; i++; continue; }
    field += c; i++;
  }
  if (field.length || row.length) { row.push(field); rows.push(row); }
  return rows.filter((r) => r.some((x) => (x || '').trim() !== ''));
}

function findCol(header, names) {
  const lower = header.map((h) => (h || '').toLowerCase().trim());
  for (const n of names) { const idx = lower.indexOf(n.toLowerCase()); if (idx >= 0) return idx; }
  // loose contains-match fallback
  for (const n of names) { const idx = lower.findIndex((h) => h.includes(n.toLowerCase())); if (idx >= 0) return idx; }
  return -1;
}

function toISO(d) {
  if (!d) return null;
  const s = String(d).trim();
  let m = s.match(/^(\d{4})-(\d{2})-(\d{2})/); if (m) return `${m[1]}-${m[2]}-${m[3]}`;
  m = s.match(/^(\d{1,2})\/(\d{1,2})\/(\d{2,4})/);
  if (m) { let y = m[3]; if (y.length === 2) y = '20' + y; return `${y}-${String(m[1]).padStart(2, '0')}-${String(m[2]).padStart(2, '0')}`; }
  const dt = new Date(s); return isNaN(dt) ? null : dt.toISOString().slice(0, 10);
}

function parseAmount(raw) {
  if (raw == null) return NaN;
  let s = String(raw).trim().replace(/[$,]/g, '');
  let neg = false;
  if (/^\(.*\)$/.test(s)) { neg = true; s = s.replace(/[()]/g, ''); }
  const n = parseFloat(s);
  if (!isFinite(n)) return NaN;
  return neg ? -n : n;
}

function parseText(text, fname) {
  const rows = parseCsv(text);
  if (rows.length < 2) return { items: [], rows: rows.length, columns: null };
  const header = rows[0];
  const dateI = findCol(header, ['Transaction Date', 'Trans Date', 'Posted Date', 'Post Date', 'Date']);
  const descI = findCol(header, ['Description', 'Merchant', 'Payee', 'Name', 'Details', 'Memo']);
  const amtI = findCol(header, ['Amount', 'Transaction Amount', 'Debit', 'Charge']);
  const creditI = findCol(header, ['Credit']);
  const typeI = findCol(header, ['Type', 'Category', 'Transaction Type']);
  const items = [];
  for (let r = 1; r < rows.length; r++) {
    const row = rows[r];
    const desc = descI >= 0 ? (row[descI] || '').trim() : '';
    if (!desc) continue;
    let amt = amtI >= 0 ? parseAmount(row[amtI]) : NaN;
    // split debit/credit columns: a value in the Credit column = money back → skip
    if (creditI >= 0 && parseAmount(row[creditI]) > 0 && !(amt > 0)) continue;
    if (!isFinite(amt)) continue;
    // Convention normalization: many banks record purchases as NEGATIVE. Treat a
    // negative as a purchase (flip to positive); a positive in an Amount-only file
    // is also a purchase. A positive in a Debit column is a purchase. Skip clear
    // payments/refunds by type/keyword.
    const typ = typeI >= 0 ? (row[typeI] || '').toLowerCase() : '';
    if (/payment|autopay|refund|return|interest|credit adjustment|reversal/.test(typ + ' ' + desc.toLowerCase())) continue;
    let amount = amt < 0 ? -amt : amt;
    if (amount <= 0) continue;
    items.push({ merchant: desc, product: null, order_id: null, date: toISO(dateI >= 0 ? row[dateI] : null),
      amount, currency: 'USD', url: null, raw: { file: fname || null, row } });
  }
  return { items, rows: rows.length, columns: { date: header[dateI], merchant: header[descI], amount: header[amtI] || header[creditI] } };
}

async function ingest(opts) {
  try {
    let sources = [];
    if (opts && opts.csv && String(opts.csv).trim()) {
      sources.push({ text: String(opts.csv), name: opts.filename || 'upload.csv' });
    } else {
      let files = [];
      try { files = fs.readdirSync(cfg.CSV_DROP_DIR).filter((f) => /\.csv$/i.test(f)); } catch (e) {}
      if (!files.length) {
        return { ok: false, items: [], meta: { source: 'csv',
          note: 'no CSV provided and data/csv-drop/ is empty — drop a statement export there or upload one',
          dropDir: cfg.CSV_DROP_DIR } };
      }
      for (const f of files) sources.push({ text: fs.readFileSync(path.join(cfg.CSV_DROP_DIR, f), 'utf8'), name: f });
    }
    let items = [], rows = 0, columns = null, filesUsed = [];
    for (const src of sources) {
      const p = parseText(src.text, src.name);
      items = items.concat(p.items); rows += p.rows; columns = columns || p.columns; filesUsed.push(src.name);
    }
    return { ok: true, items, meta: { source: 'csv', note: `parsed ${items.length} purchases from ${rows} rows`,
      parsed: items.length, rows, columns, files: filesUsed } };
  } catch (e) {
    return { ok: false, items: [], meta: { source: 'csv', note: 'csv parse failed: ' + e.message } };
  }
}

module.exports = { ingest, parseCsv, parseText };