`routes/bills.js`

128 lines
// Bills to Pay — recurring & one-off money owed, incl. tax/government (CRA) payments.
const express = require('express');
const db = require('../lib/db');
const audit = require('../lib/audit');
const { id } = require('../lib/ids');
const { nextDue, daysUntil, toISODate } = require('../lib/recurrence');

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

const CATEGORIES = ['utility', 'rent', 'mortgage', 'insurance', 'subscription', 'loan', 'card', 'phone', 'internet', 'tax', 'government', 'other'];
const CADENCES = ['once', 'weekly', 'biweekly', 'monthly', 'quarterly', 'semiannual', 'annual'];

// Decorate a row with derived fields the UI needs (days until due, overdue flag).
function decorate(b) {
  const d = b.due_date ? daysUntil(b.due_date) : null;
  return {
    ...b,
    days_until: d,
    is_overdue: b.status === 'active' && d != null && d < 0,
    is_due_soon: b.status === 'active' && d != null && d >= 0 && d <= 7,
  };
}

async function listBills(userId) {
  const r = await db.query(
    `SELECT * FROM bill WHERE user_id = $1 ORDER BY (due_date IS NULL), due_date ASC, created_at DESC`,
    [userId]
  );
  return r.rows.map(decorate);
}

// ── page ──────────────────────────────────────────────
router.get('/bills', async (_req, res) => {
  const bills = await listBills(DEV_USER_ID);
  const monthlyBurden = bills
    .filter((b) => b.status === 'active' && b.cadence === 'monthly' && b.amount)
    .reduce((s, b) => s + Number(b.amount), 0);
  res.render('bills', { bills, categories: CATEGORIES, cadences: CADENCES, monthlyBurden });
});

// ── api ───────────────────────────────────────────────
router.get('/api/bills', async (_req, res) => {
  res.json(await listBills(DEV_USER_ID));
});

router.post('/api/bills', async (req, res) => {
  try {
    const b = req.body || {};
    if (!b.name) return res.status(400).json({ error: 'name required' });
    const billId = id('bill');
    await db.query(
      `INSERT INTO bill (id, user_id, name, payee, category, amount, currency, cadence, due_date, autopay, account_hint, url, status, notes)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)`,
      [
        billId, DEV_USER_ID, b.name, b.payee || null,
        CATEGORIES.includes(b.category) ? b.category : 'other',
        b.amount || null, b.currency || 'USD',
        CADENCES.includes(b.cadence) ? b.cadence : 'monthly',
        b.due_date || null, b.autopay === true || b.autopay === 'on',
        b.account_hint || null, b.url || null, 'active', b.notes || null,
      ]
    );
    await audit.log({ actorType: 'user', actorId: DEV_USER_ID, objectType: 'bill', objectId: billId, eventType: 'bill_created', metadata: { name: b.name, category: b.category } });
    const r = await db.query(`SELECT * FROM bill WHERE id = $1`, [billId]);
    res.status(201).json(decorate(r.rows[0]));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.post('/api/bills/:id', async (req, res) => {
  try {
    const b = req.body || {};
    const fields = ['name', 'payee', 'category', 'amount', 'currency', 'cadence', 'due_date', 'autopay', 'account_hint', 'url', 'status', 'notes'];
    const sets = [];
    const vals = [];
    let i = 1;
    for (const f of fields) {
      if (b[f] === undefined) continue;
      sets.push(`${f} = $${i++}`);
      vals.push(f === 'autopay' ? (b[f] === true || b[f] === 'on') : (b[f] === '' ? null : b[f]));
    }
    if (!sets.length) return res.status(400).json({ error: 'no fields to update' });
    sets.push(`updated_at = now()`);
    vals.push(req.params.id, DEV_USER_ID);
    const r = await db.query(
      `UPDATE bill SET ${sets.join(', ')} WHERE id = $${i++} AND user_id = $${i} RETURNING *`,
      vals
    );
    if (!r.rows.length) return res.status(404).json({ error: 'not found' });
    await audit.log({ actorType: 'user', actorId: DEV_USER_ID, objectType: 'bill', objectId: req.params.id, eventType: 'bill_updated' });
    res.json(decorate(r.rows[0]));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Mark a bill paid → stamp last_paid_at; roll due_date forward for recurring bills, else close it.
router.post('/api/bills/:id/paid', async (req, res) => {
  try {
    const cur = await db.query(`SELECT * FROM bill WHERE id = $1 AND user_id = $2`, [req.params.id, DEV_USER_ID]);
    if (!cur.rows.length) return res.status(404).json({ error: 'not found' });
    const bill = cur.rows[0];
    const today = toISODate(new Date());
    const rolled = nextDue(bill.due_date || today, bill.cadence);
    const r = await db.query(
      `UPDATE bill SET last_paid_at = $1, due_date = $2, status = $3, updated_at = now()
       WHERE id = $4 AND user_id = $5 RETURNING *`,
      [today, rolled || bill.due_date, rolled ? 'active' : 'closed', req.params.id, DEV_USER_ID]
    );
    // Clear the old deadline so the engine regenerates against the new due_date.
    await db.query(`DELETE FROM calendar_reminder WHERE user_id = $1 AND owner_table = 'bill' AND owner_id = $2`, [DEV_USER_ID, req.params.id]);
    await audit.log({ actorType: 'user', actorId: DEV_USER_ID, objectType: 'bill', objectId: req.params.id, eventType: 'bill_paid', metadata: { paid_on: today, next_due: rolled } });
    res.json(decorate(r.rows[0]));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.post('/api/bills/:id/delete', async (req, res) => {
  await db.query(`DELETE FROM bill WHERE id = $1 AND user_id = $2`, [req.params.id, DEV_USER_ID]);
  await audit.log({ actorType: 'user', actorId: DEV_USER_ID, objectType: 'bill', objectId: req.params.id, eventType: 'bill_deleted' });
  res.json({ ok: true });
});

module.exports = router;