`routes/reorders.js`

125 lines
// Reorders — "what we order a lot" + best-price ("save money") tracking.
const express = require('express');
const db = require('../lib/db');
const audit = require('../lib/audit');
const { id } = require('../lib/ids');
const { nextDueDays, daysUntil, toISODate } = require('../lib/recurrence');

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

const CATEGORIES = ['grocery', 'household', 'pet', 'office', 'health', 'other'];

function decorate(r) {
  const d = r.next_due_date ? daysUntil(r.next_due_date) : null;
  // savings = how much cheaper the best price is vs. what we typically pay
  const savings = r.typical_price != null && r.best_price != null
    ? Number(r.typical_price) - Number(r.best_price)
    : null;
  return {
    ...r,
    days_until: d,
    is_due: r.status === 'active' && d != null && d <= 0,
    savings: savings != null && savings > 0 ? Number(savings.toFixed(2)) : 0,
  };
}

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

router.get('/reorders', async (_req, res) => {
  const items = await listItems(DEV_USER_ID);
  const totalSavings = items.reduce((s, x) => s + (x.savings || 0), 0);
  res.render('reorders', { items, categories: CATEGORIES, totalSavings });
});

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

router.post('/api/reorders', async (req, res) => {
  try {
    const b = req.body || {};
    if (!b.name) return res.status(400).json({ error: 'name required' });
    const cadence = b.reorder_cadence_days ? parseInt(b.reorder_cadence_days, 10) : null;
    const nextDue = b.last_ordered_at && cadence ? nextDueDays(b.last_ordered_at, cadence) : (b.next_due_date || null);
    const itemId = id('reorder');
    await db.query(
      `INSERT INTO reorder_item (id, user_id, name, merchant, category, unit, typical_price, best_price, best_price_source, currency, reorder_cadence_days, last_ordered_at, next_due_date, url, status, notes)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16)`,
      [
        itemId, DEV_USER_ID, b.name, b.merchant || null,
        CATEGORIES.includes(b.category) ? b.category : (b.category || null),
        b.unit || null, b.typical_price || null, b.best_price || null, b.best_price_source || null,
        b.currency || 'USD', cadence, b.last_ordered_at || null, nextDue, b.url || null, 'active', b.notes || null,
      ]
    );
    await audit.log({ actorType: 'user', actorId: DEV_USER_ID, objectType: 'reorder_item', objectId: itemId, eventType: 'reorder_created', metadata: { name: b.name } });
    const r = await db.query(`SELECT * FROM reorder_item WHERE id = $1`, [itemId]);
    res.status(201).json(decorate(r.rows[0]));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.post('/api/reorders/:id', async (req, res) => {
  try {
    const b = req.body || {};
    const fields = ['name', 'merchant', 'category', 'unit', 'typical_price', 'best_price', 'best_price_source', 'currency', 'reorder_cadence_days', 'last_ordered_at', 'next_due_date', '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(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 reorder_item 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: 'reorder_item', objectId: req.params.id, eventType: 'reorder_updated' });
    res.json(decorate(r.rows[0]));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Mark ordered → stamp last_ordered_at=today and roll next_due_date forward by cadence.
router.post('/api/reorders/:id/ordered', async (req, res) => {
  try {
    const cur = await db.query(`SELECT * FROM reorder_item 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 item = cur.rows[0];
    const today = toISODate(new Date());
    const nextDue = item.reorder_cadence_days ? nextDueDays(today, item.reorder_cadence_days) : null;
    const r = await db.query(
      `UPDATE reorder_item SET last_ordered_at = $1, next_due_date = $2, updated_at = now()
       WHERE id = $3 AND user_id = $4 RETURNING *`,
      [today, nextDue, req.params.id, DEV_USER_ID]
    );
    await db.query(`DELETE FROM calendar_reminder WHERE user_id = $1 AND owner_table = 'reorder_item' AND owner_id = $2`, [DEV_USER_ID, req.params.id]);
    await audit.log({ actorType: 'user', actorId: DEV_USER_ID, objectType: 'reorder_item', objectId: req.params.id, eventType: 'reorder_ordered', metadata: { ordered_on: today, next_due: nextDue } });
    res.json(decorate(r.rows[0]));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

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

module.exports = router;