BillTracker/routes/monthly-starting-amounts.js

const express = require('express');
const router = express.Router();
const { getDb } = require('../db/database');
const { getCycleRange } = require('../services/statusService');

function parseYearMonth(source) {
  const now = new Date();
  const year = parseInt(source.year || now.getFullYear(), 10);
  const month = parseInt(source.month || now.getMonth() + 1, 10);

  if (Number.isNaN(year) || year < 2000 || year > 2100) {
    return { error: 'year must be a 4-digit integer between 2000 and 2100' };
  }
  if (Number.isNaN(month) || month < 1 || month > 12) {
    return { error: 'month must be an integer between 1 and 12' };
  }

  return { year, month };
}

function money(value) {
  const n = Number(value);
  return Number.isFinite(n) ? n : 0;
}

function getStartingAmounts(db, userId, year, month) {
  const row = db.prepare(`
    SELECT first_amount, fifteenth_amount, other_amount
    FROM monthly_starting_amounts
    WHERE user_id = ? AND year = ? AND month = ?
  `).get(userId, year, month);

  return {
    first_amount: money(row?.first_amount || 0),
    fifteenth_amount: money(row?.fifteenth_amount || 0),
    other_amount: money(row?.other_amount || 0),
  };
}

function calculatePaidDeductions(db, userId, year, month) {
  const { start, end } = getCycleRange(year, month);

  // Paid from first bucket: bills with due_day 1-14
  const firstPaid = db.prepare(`
    SELECT COALESCE(SUM(p.amount), 0) AS paid
    FROM payments p
    JOIN bills b ON b.id = p.bill_id
    WHERE b.user_id = ?
      AND p.paid_date BETWEEN ? AND ?
      AND p.deleted_at IS NULL
      AND b.due_day BETWEEN 1 AND 14
  `).get(userId, start, end);

  // Paid from fifteenth bucket: bills with due_day 15-31
  const fifteenthPaid = db.prepare(`
    SELECT COALESCE(SUM(p.amount), 0) AS paid
    FROM payments p
    JOIN bills b ON b.id = p.bill_id
    WHERE b.user_id = ?
      AND p.paid_date BETWEEN ? AND ?
      AND p.deleted_at IS NULL
      AND b.due_day BETWEEN 15 AND 31
  `).get(userId, start, end);

  const totalPaid = db.prepare(`
    SELECT COALESCE(SUM(p.amount), 0) AS paid
    FROM payments p
    JOIN bills b ON b.id = p.bill_id
    WHERE b.user_id = ?
      AND p.paid_date BETWEEN ? AND ?
      AND p.deleted_at IS NULL
  `).get(userId, start, end);

  return {
    paid_from_first: money(firstPaid.paid),
    paid_from_fifteenth: money(fifteenthPaid.paid),
    paid_total: money(totalPaid.paid),
  };
}

function buildStartingAmountsResponse(db, userId, year, month) {
  const amounts = getStartingAmounts(db, userId, year, month);
  const paid = calculatePaidDeductions(db, userId, year, month);

  const combined_amount = amounts.first_amount + amounts.fifteenth_amount + amounts.other_amount;
  const paid_total = paid.paid_total;

  return {
    year,
    month,
    first_amount: amounts.first_amount,
    fifteenth_amount: amounts.fifteenth_amount,
    other_amount: amounts.other_amount,
    combined_amount,
    paid_from_first: paid.paid_from_first,
    paid_from_fifteenth: paid.paid_from_fifteenth,
    paid_total,
    first_remaining: amounts.first_amount - paid.paid_from_first,
    fifteenth_remaining: amounts.fifteenth_amount - paid.paid_from_fifteenth,
    other_remaining: amounts.other_amount,
    combined_remaining: combined_amount - paid_total,
  };
}

router.get('/', (req, res) => {
  const parsed = parseYearMonth(req.query);
  if (parsed.error) return res.status(400).json({ error: parsed.error });

  const db = getDb();
  res.json(buildStartingAmountsResponse(db, req.user.id, parsed.year, parsed.month));
});

router.put('/', (req, res) => {
  const parsed = parseYearMonth(req.body || {});
  if (parsed.error) return res.status(400).json({ error: parsed.error });

  const firstAmount = Number(req.body?.first_amount);
  if (!Number.isFinite(firstAmount) || firstAmount < 0 || firstAmount > 1000000000) {
    return res.status(400).json({ error: 'first_amount must be a number between 0 and 1000000000' });
  }

  const fifteenthAmount = Number(req.body?.fifteenth_amount);
  if (!Number.isFinite(fifteenthAmount) || fifteenthAmount < 0 || fifteenthAmount > 1000000000) {
    return res.status(400).json({ error: 'fifteenth_amount must be a number between 0 and 1000000000' });
  }

  const otherAmount = Number(req.body?.other_amount);
  if (!Number.isFinite(otherAmount) || otherAmount < 0 || otherAmount > 1000000000) {
    return res.status(400).json({ error: 'other_amount must be a number between 0 and 1000000000' });
  }

  const db = getDb();
  db.prepare(`
    INSERT INTO monthly_starting_amounts (user_id, year, month, first_amount, fifteenth_amount, other_amount, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, datetime('now'))
    ON CONFLICT(user_id, year, month) DO UPDATE SET
      first_amount = excluded.first_amount,
      fifteenth_amount = excluded.fifteenth_amount,
      other_amount = excluded.other_amount,
      updated_at = datetime('now')
  `).run(req.user.id, parsed.year, parsed.month, firstAmount, fifteenthAmount, otherAmount);

  res.json(buildStartingAmountsResponse(db, req.user.id, parsed.year, parsed.month));
});

module.exports = router;