`routes/upload.js`

76 lines
// Manual CSV upload route — drag-drop a bank-statement CSV → purchase rows.

const express = require('express');
const multer = require('multer');
const db = require('../lib/db');
const audit = require('../lib/audit');
const { id } = require('../lib/ids');
const csvParser = require('../lib/csv-parser');

const router = express.Router();
const DEV_USER_ID = 'user_steve';

// In-memory storage; file size cap 10 MB; keep CSV in RAM not disk.
const upload = multer({
  storage: multer.memoryStorage(),
  limits: { fileSize: 10 * 1024 * 1024 },
});

router.post('/api/upload/csv', upload.single('file'), async (req, res) => {
  if (!req.file) return res.status(400).json({ error: 'no file uploaded; use multipart/form-data with field name "file"' });
  const csvText = req.file.buffer.toString('utf-8');

  const parsed = csvParser.parseStatement(csvText);
  if (parsed.errors.length && parsed.rows.length === 0) {
    return res.status(400).json({ error: 'csv parse failed', details: parsed.errors });
  }

  let inserted = 0;
  for (const r of parsed.rows) {
    const purchaseId = id('purchase');
    // Stable dedup key so re-uploading overlapping statements doesn't double rows.
    const dedupKey = `csv:${new Date(r.date).toISOString().slice(0, 10)}:${r.amount}:${r.merchant}`.slice(0, 200);
    try {
      const ins = await db.query(
        `INSERT INTO purchase
           (id, user_id, source_message_id, merchant_name, merchant_domain, order_number, purchase_date, total_amount, currency, confidence, raw_extract)
         VALUES ($1, $2, NULL, $3, NULL, $4, $5, $6, 'USD', $7, $8)
         ON CONFLICT (user_id, order_number) WHERE order_number IS NOT NULL DO NOTHING
         RETURNING id`,
        [purchaseId, DEV_USER_ID, r.merchant, dedupKey, r.date, r.amount, 0.85, r]
      );
      if (!ins.rows.length) continue;  // duplicate of an already-imported transaction
      await audit.log({
        actorType: 'user',
        actorId: DEV_USER_ID,
        objectType: 'purchase',
        objectId: purchaseId,
        eventType: 'purchase_extracted',
        metadata: { source: 'csv_upload', merchant: r.merchant, total: r.amount, line: r.raw.line, filename: req.file.originalname },
      });
      inserted += 1;
    } catch (err) {
      console.warn('[csv-upload] insert', err.message);
    }
  }

  await audit.log({
    actorType: 'user',
    actorId: DEV_USER_ID,
    objectType: 'user_account',
    objectId: DEV_USER_ID,
    eventType: 'csv_upload',
    metadata: { filename: req.file.originalname, size: req.file.size, rows: parsed.rows.length, inserted, skipped: parsed.skipped },
  });

  res.json({
    ok: true,
    filename: req.file.originalname,
    inserted,
    skipped: parsed.skipped,
    parse_errors: parsed.errors.slice(0, 5),
  });
});

module.exports = router;