BillTracker/routes/snowball.js

const express = require('express');
const router = express.Router();
const { getDb } = require('../db/database');
const { standardizeError } = require('../middleware/errorFormatter');
const { calculateSnowball, calculateAvalanche } = require('../services/snowballService');
const { calculateMinimumOnly, debtAprSnapshot } = require('../services/aprService');
const { serializeBill } = require('../services/billsService');
const { toCents, fromCents } = require('../utils/money');

const DEBT_LIKE_CLAUSES = `(
  b.snowball_include = 1
  OR (
    COALESCE(b.snowball_exempt, 0) = 0
    AND (
      LOWER(c.name) LIKE '%credit%'
      OR LOWER(c.name) LIKE '%loan%'
      OR LOWER(c.name) LIKE '%debt%'
      OR LOWER(c.name) LIKE '%mortgage%'
      OR LOWER(c.name) LIKE '%housing%'
    )
  )
)`;

function isRamseyMode(userId) {
  const db = getDb();
  const row = db.prepare(`
    SELECT value
    FROM user_settings
    WHERE user_id = ? AND key = 'snowball_ramsey_mode'
  `).get(userId);
  return row ? row.value !== 'false' && row.value !== '0' : true;
}

function getUserBoolSetting(userId, key, fallback = false) {
  const db = getDb();
  const row = db.prepare(`
    SELECT value
    FROM user_settings
    WHERE user_id = ? AND key = ?
  `).get(userId, key);
  if (!row) return fallback;
  return row.value === 'true' || row.value === '1';
}

function upsertUserSetting(db, userId, key, value) {
  db.prepare(`
    INSERT INTO user_settings (user_id, key, value, updated_at)
    VALUES (?, ?, ?, datetime('now'))
    ON CONFLICT(user_id, key) DO UPDATE SET
      value = excluded.value,
      updated_at = datetime('now')
  `).run(userId, key, String(value));
}

function getDebtQuery(ramseyMode) {
  const orderBy = ramseyMode
    ? `
    CASE WHEN b.current_balance IS NULL THEN 1 ELSE 0 END ASC,
    b.current_balance ASC,
    LOWER(b.name) ASC,
    b.id ASC`
    : `
    CASE WHEN b.snowball_order IS NULL THEN 1 ELSE 0 END ASC,
    b.snowball_order ASC,
    CASE WHEN b.current_balance IS NULL THEN 1 ELSE 0 END ASC,
    b.current_balance ASC`;

  return `
  SELECT b.*, c.name AS category_name
  FROM bills b
  LEFT JOIN categories c ON b.category_id = c.id AND c.user_id = b.user_id AND c.deleted_at IS NULL
  WHERE b.user_id = ?
    AND b.active = 1
    AND b.deleted_at IS NULL
    AND ${DEBT_LIKE_CLAUSES}
  ORDER BY${orderBy}
  `;
}

function getDebtBills(userId, ramseyMode) {
  const db = getDb();
  const mode = ramseyMode !== undefined ? ramseyMode : isRamseyMode(userId);
  return db.prepare(getDebtQuery(mode)).all(userId);
}

// GET /api/snowball — server-filtered debt bills, pre-sorted by snowball_order
router.get('/', (req, res) => {
  const ramseyMode = isRamseyMode(req.user.id);
  res.json(getDebtBills(req.user.id, ramseyMode).map(serializeBill));
});

// GET /api/snowball/settings — extra monthly payment for this user
router.get('/settings', (req, res) => {
  const db = getDb();
  const user = db.prepare('SELECT snowball_extra_payment FROM users WHERE id = ?').get(req.user.id);
  res.json({
    extra_payment: fromCents(user?.snowball_extra_payment ?? 0),
    ramsey_mode: isRamseyMode(req.user.id),
    ready_current_on_bills: getUserBoolSetting(req.user.id, 'snowball_ready_current_on_bills'),
    ready_emergency_fund: getUserBoolSetting(req.user.id, 'snowball_ready_emergency_fund'),
  });
});

// PATCH /api/snowball/settings — save extra monthly payment
router.patch('/settings', (req, res) => {
  const { extra_payment, ramsey_mode, ready_current_on_bills, ready_emergency_fund } = req.body;
  let val = 0;

  if (extra_payment !== undefined && extra_payment !== null && extra_payment !== '') {
    val = parseFloat(extra_payment);
    if (!Number.isFinite(val) || val < 0) {
      return res.status(400).json(standardizeError(
        'extra_payment must be a non-negative number',
        'VALIDATION_ERROR',
        'extra_payment',
      ));
    }
  }

  const db = getDb();
  const save = db.transaction(() => {
    if (extra_payment !== undefined) {
      db.prepare('UPDATE users SET snowball_extra_payment = ? WHERE id = ?').run(toCents(val), req.user.id);
    }

    if (ramsey_mode !== undefined) {
      upsertUserSetting(db, req.user.id, 'snowball_ramsey_mode', ramsey_mode ? 'true' : 'false');
    }

    if (ready_current_on_bills !== undefined) {
      upsertUserSetting(db, req.user.id, 'snowball_ready_current_on_bills', ready_current_on_bills ? 'true' : 'false');
    }

    if (ready_emergency_fund !== undefined) {
      upsertUserSetting(db, req.user.id, 'snowball_ready_emergency_fund', ready_emergency_fund ? 'true' : 'false');
    }
  });
  save();

  const user = db.prepare('SELECT snowball_extra_payment FROM users WHERE id = ?').get(req.user.id);
  res.json({
    extra_payment: fromCents(user?.snowball_extra_payment ?? 0),
    // Use body value when ramsey_mode was just saved; fall back to DB read if not in request
    ramsey_mode: ramsey_mode !== undefined ? !!ramsey_mode : isRamseyMode(req.user.id),
    ready_current_on_bills: getUserBoolSetting(req.user.id, 'snowball_ready_current_on_bills'),
    ready_emergency_fund: getUserBoolSetting(req.user.id, 'snowball_ready_emergency_fund'),
  });
});

// GET /api/snowball/projection — snowball, avalanche, minimum-only projections
// Each debt result is enriched with current APR metrics (monthly interest, etc.)
router.get('/projection', (req, res) => {
  const db = getDb();

  const ramseyMode = isRamseyMode(req.user.id);
  const bills = getDebtBills(req.user.id, ramseyMode);
  const user  = db.prepare('SELECT snowball_extra_payment FROM users WHERE id = ?').get(req.user.id);

  // Money fields on `bills` are stored as integer cents; the snowball/APR math
  // and the API response are dollar-denominated, so convert before computing.
  const billsForMath = bills.map(b => ({
    ...b,
    current_balance: fromCents(b.current_balance),
    minimum_payment: fromCents(b.minimum_payment),
  }));

  // Allow an optional ?extra=N override so the client can preview an unsaved
  // extra payment without a round-trip save. Falls back to the stored value.
  const queryExtra = req.query.extra !== undefined ? parseFloat(req.query.extra) : NaN;
  const extra = Number.isFinite(queryExtra) && queryExtra >= 0
    ? queryExtra
    : fromCents(user?.snowball_extra_payment ?? 0);

  // Build a lookup of APR snapshots keyed by bill id (computed once from current balances)
  const aprByBill = {};
  for (const b of billsForMath) {
    const snap = debtAprSnapshot(b);
    if (snap) aprByBill[b.id] = snap;
  }

  // Enrich each debt result with its APR snapshot
  function enrich(projection) {
    return {
      ...projection,
      debts: projection.debts.map(d => ({
        ...d,
        apr_snapshot: aprByBill[d.id] ?? null,
      })),
    };
  }

  const now          = new Date();
  const snowball     = enrich(calculateSnowball(billsForMath, extra, now));
  const avalanche    = enrich(calculateAvalanche(billsForMath, extra, now));
  const minimum_only = enrich(calculateMinimumOnly(billsForMath, now));

  // Comparison: what does the snowball save vs just paying minimums?
  const comparison = buildComparison(snowball, minimum_only);

  res.json({ snowball, avalanche, minimum_only, comparison });
});

// Build a summary comparing snowball to the minimum-only baseline
function buildComparison(snowball, minimum_only) {
  const sbMonths = snowball.months_to_freedom;
  const moMonths = minimum_only.months_to_freedom;
  const sbInterest = snowball.total_interest_paid;
  const moInterest = minimum_only.total_interest_paid;

  if (!sbMonths || !moMonths) return null;

  const months_saved    = moMonths - sbMonths;
  const interest_saved  = Math.round((moInterest - sbInterest) * 100) / 100;
  const years_saved     = +(months_saved / 12).toFixed(1);

  return {
    months_saved,
    years_saved,
    interest_saved,
    minimum_only_months:   moMonths,
    minimum_only_interest: moInterest,
    minimum_only_payoff:   minimum_only.payoff_display,
    snowball_months:       sbMonths,
    snowball_interest:     sbInterest,
    snowball_payoff:       snowball.payoff_display,
  };
}

// PATCH /api/snowball/order — batch-save snowball_order positions
router.patch('/order', (req, res) => {
  const items = req.body;
  if (!Array.isArray(items)) {
    return res.status(400).json(standardizeError('Request body must be an array', 'VALIDATION_ERROR'));
  }
  if (items.length === 0) {
    return res.json({ success: true, updated: 0 });
  }

  // Validate every row before touching the DB — no silent skips
  const parsed = [];
  for (let i = 0; i < items.length; i++) {
    const row   = items[i];
    const id    = parseInt(row?.id, 10);
    const order = parseInt(row?.snowball_order, 10);
    if (!Number.isInteger(id) || id <= 0) {
      return res.status(400).json(standardizeError(
        `Item at index ${i} has an invalid id: ${JSON.stringify(row?.id)}`,
        'VALIDATION_ERROR',
      ));
    }
    if (!Number.isInteger(order) || order < 0) {
      return res.status(400).json(standardizeError(
        `Item at index ${i} has an invalid snowball_order: ${JSON.stringify(row?.snowball_order)}`,
        'VALIDATION_ERROR',
      ));
    }
    parsed.push({ id, order });
  }

  const db     = getDb();
  const userId = req.user.id;
  const update = db.prepare(
    'UPDATE bills SET snowball_order = ? WHERE id = ? AND user_id = ? AND deleted_at IS NULL'
  );

  db.transaction(() => {
    for (const { id, order } of parsed) {
      update.run(order, id, userId);
    }
  })();

  res.json({ success: true, updated: parsed.length });
});

// ── Snowball Plan helpers ─────────────────────────────────────────────────────

function enrichPlanWithProgress(db, plan) {
  let snapshot;
  try { snapshot = JSON.parse(plan.plan_snapshot); } catch { snapshot = null; }

  const currentDebts = (snapshot?.debts ?? []).map(d => {
    const bill = db.prepare('SELECT current_balance, name, deleted_at FROM bills WHERE id = ?').get(d.bill_id);
    const currentBalance = bill && !bill.deleted_at ? fromCents(bill.current_balance) : null;
    const startingBalance = d.starting_balance ?? 0;
    const progressPct = startingBalance > 0 && currentBalance !== null
      ? Math.min(100, Math.max(0, Math.round((startingBalance - currentBalance) / startingBalance * 100)))
      : null;
    return { bill_id: d.bill_id, name: d.name, current_balance: currentBalance, starting_balance: startingBalance, progress_pct: progressPct, deleted: !!(bill?.deleted_at) };
  });

  const startedMs = plan.started_at ? new Date(plan.started_at).getTime() : Date.now();
  const monthsElapsed = Math.floor((Date.now() - startedMs) / (1000 * 60 * 60 * 24 * 30));

  return { ...plan, extra_payment: fromCents(plan.extra_payment), plan_snapshot: snapshot, months_elapsed: monthsElapsed, current_debts: currentDebts };
}

// POST /api/snowball/plans — start a new snowball plan
router.post('/plans', (req, res) => {
  try {
    const db = getDb();
    const userId = req.user.id;
    const { name, method, notes } = req.body;

    const planName   = (typeof name === 'string' && name.trim()) ? name.trim().slice(0, 100) : 'Snowball Plan';
    const planMethod = ['snowball', 'avalanche', 'custom'].includes(method) ? method : 'snowball';

    const ramseyMode = isRamseyMode(userId);
    const debts = getDebtBills(userId, ramseyMode);
    const activeDebts = debts.filter(b => (b.current_balance ?? 0) > 0);
    if (activeDebts.length === 0) {
      return res.status(400).json({ error: 'No debts with a balance found. Add a balance to at least one bill.' });
    }

    // Money fields on `debts` are stored as integer cents; the snowball/APR
    // math and plan_snapshot are dollar-denominated, so convert before computing.
    const debtsForMath = debts.map(b => ({
      ...b,
      current_balance: fromCents(b.current_balance),
      minimum_payment: fromCents(b.minimum_payment),
    }));

    const user       = db.prepare('SELECT snowball_extra_payment FROM users WHERE id = ?').get(userId);
    const extraCents = user?.snowball_extra_payment ?? 0;
    const extra      = fromCents(extraCents);
    const now        = new Date();

    const snowball    = planMethod === 'avalanche' ? calculateAvalanche(debtsForMath, extra, now) : calculateSnowball(debtsForMath, extra, now);
    const minOnly     = calculateMinimumOnly(debtsForMath, now);
    const interestSaved = Math.max(0, Math.round(((minOnly.total_interest_paid ?? 0) - (snowball.total_interest_paid ?? 0)) * 100) / 100);

    const debtSnaps = debtsForMath.map((b, i) => {
      const proj = snowball.debts?.find(d => d.id === b.id);
      return {
        bill_id:                b.id,
        name:                   b.name,
        starting_balance:       b.current_balance ?? 0,
        minimum_payment:        b.minimum_payment ?? 0,
        interest_rate:          b.interest_rate ?? 0,
        projected_payoff_month: proj?.payoff_month ?? null,
        projected_payoff_date:  proj?.payoff_date ?? null,
        projected_total_interest: proj?.total_interest ?? null,
        order: i,
      };
    });

    const planSnapshot = JSON.stringify({
      projected_payoff_date:    snowball.payoff_date ?? null,
      projected_months:         snowball.months_to_freedom ?? null,
      projected_total_interest: snowball.total_interest_paid ?? null,
      minimum_only_months:      minOnly.months_to_freedom ?? null,
      interest_saved:           interestSaved,
      debts:                    debtSnaps,
    });

    // Abandon any existing active/paused plan first
    db.prepare(`
      UPDATE snowball_plans SET status = 'abandoned', updated_at = datetime('now')
      WHERE user_id = ? AND status IN ('active', 'paused')
    `).run(userId);

    const result = db.prepare(`
      INSERT INTO snowball_plans (user_id, name, method, status, extra_payment, plan_snapshot, notes, started_at, created_at, updated_at)
      VALUES (?, ?, ?, 'active', ?, ?, ?, datetime('now'), datetime('now'), datetime('now'))
    `).run(userId, planName, planMethod, extraCents, planSnapshot, notes || null);

    const plan = db.prepare('SELECT * FROM snowball_plans WHERE id = ?').get(result.lastInsertRowid);
    res.status(201).json(enrichPlanWithProgress(db, plan));
  } catch (err) {
    console.error('[snowball plans] POST error:', err.message);
    res.status(500).json({ error: 'Failed to start plan' });
  }
});

// GET /api/snowball/plans — list all plans for user
router.get('/plans', (req, res) => {
  try {
    const db = getDb();
    const plans = db.prepare(`
      SELECT * FROM snowball_plans WHERE user_id = ? ORDER BY created_at DESC
    `).all(req.user.id);
    res.json({ plans: plans.map(p => enrichPlanWithProgress(db, p)) });
  } catch (err) {
    console.error('[snowball plans] GET /plans error:', err.message);
    res.status(500).json({ error: 'Failed to load plans' });
  }
});

// GET /api/snowball/plans/active — return the active or paused plan (or null)
router.get('/plans/active', (req, res) => {
  try {
    const db = getDb();
    const plan = db.prepare(`
      SELECT * FROM snowball_plans
      WHERE user_id = ? AND status IN ('active', 'paused')
      ORDER BY created_at DESC LIMIT 1
    `).get(req.user.id);
    res.json(plan ? enrichPlanWithProgress(db, plan) : null);
  } catch (err) {
    console.error('[snowball plans] GET /plans/active error:', err.message);
    res.status(500).json({ error: 'Failed to load active plan' });
  }
});

// PATCH /api/snowball/plans/:id — update name or notes
router.patch('/plans/:id', (req, res) => {
  try {
    const db = getDb();
    const id = parseInt(req.params.id, 10);
    if (!Number.isInteger(id) || id <= 0) return res.status(400).json({ error: 'Invalid id' });
    const plan = db.prepare('SELECT * FROM snowball_plans WHERE id = ? AND user_id = ?').get(id, req.user.id);
    if (!plan) return res.status(404).json({ error: 'Plan not found' });

    const { name, notes } = req.body;
    const newName  = (typeof name === 'string' && name.trim()) ? name.trim().slice(0, 100) : plan.name;
    const newNotes = notes !== undefined ? (notes || null) : plan.notes;

    db.prepare(`
      UPDATE snowball_plans SET name = ?, notes = ?, updated_at = datetime('now') WHERE id = ?
    `).run(newName, newNotes, id);

    const updated = db.prepare('SELECT * FROM snowball_plans WHERE id = ?').get(id);
    res.json(enrichPlanWithProgress(db, updated));
  } catch (err) {
    console.error('[snowball plans] PATCH error:', err.message);
    res.status(500).json({ error: 'Failed to update plan' });
  }
});

// POST /api/snowball/plans/:id/pause
router.post('/plans/:id/pause', (req, res) => {
  try {
    const db = getDb();
    const id = parseInt(req.params.id, 10);
    if (!Number.isInteger(id) || id <= 0) return res.status(400).json({ error: 'Invalid id' });
    const plan = db.prepare('SELECT * FROM snowball_plans WHERE id = ? AND user_id = ?').get(id, req.user.id);
    if (!plan) return res.status(404).json({ error: 'Plan not found' });
    if (plan.status !== 'active') return res.status(400).json({ error: 'Only active plans can be paused' });

    db.prepare(`
      UPDATE snowball_plans SET status = 'paused', paused_at = datetime('now'), updated_at = datetime('now') WHERE id = ?
    `).run(id);

    const updated = db.prepare('SELECT * FROM snowball_plans WHERE id = ?').get(id);
    res.json(enrichPlanWithProgress(db, updated));
  } catch (err) {
    console.error('[snowball plans] pause error:', err.message);
    res.status(500).json({ error: 'Failed to pause plan' });
  }
});

// POST /api/snowball/plans/:id/resume
router.post('/plans/:id/resume', (req, res) => {
  try {
    const db = getDb();
    const id = parseInt(req.params.id, 10);
    if (!Number.isInteger(id) || id <= 0) return res.status(400).json({ error: 'Invalid id' });
    const plan = db.prepare('SELECT * FROM snowball_plans WHERE id = ? AND user_id = ?').get(id, req.user.id);
    if (!plan) return res.status(404).json({ error: 'Plan not found' });
    if (plan.status !== 'paused') return res.status(400).json({ error: 'Only paused plans can be resumed' });

    db.prepare(`
      UPDATE snowball_plans SET status = 'active', paused_at = NULL, updated_at = datetime('now') WHERE id = ?
    `).run(id);

    const updated = db.prepare('SELECT * FROM snowball_plans WHERE id = ?').get(id);
    res.json(enrichPlanWithProgress(db, updated));
  } catch (err) {
    console.error('[snowball plans] resume error:', err.message);
    res.status(500).json({ error: 'Failed to resume plan' });
  }
});

// POST /api/snowball/plans/:id/complete
router.post('/plans/:id/complete', (req, res) => {
  try {
    const db = getDb();
    const id = parseInt(req.params.id, 10);
    if (!Number.isInteger(id) || id <= 0) return res.status(400).json({ error: 'Invalid id' });
    const plan = db.prepare('SELECT * FROM snowball_plans WHERE id = ? AND user_id = ?').get(id, req.user.id);
    if (!plan) return res.status(404).json({ error: 'Plan not found' });
    if (!['active', 'paused'].includes(plan.status)) return res.status(400).json({ error: 'Only active or paused plans can be completed' });

    db.prepare(`
      UPDATE snowball_plans SET status = 'completed', completed_at = datetime('now'), updated_at = datetime('now') WHERE id = ?
    `).run(id);

    const updated = db.prepare('SELECT * FROM snowball_plans WHERE id = ?').get(id);
    res.json(enrichPlanWithProgress(db, updated));
  } catch (err) {
    console.error('[snowball plans] complete error:', err.message);
    res.status(500).json({ error: 'Failed to complete plan' });
  }
});

// POST /api/snowball/plans/:id/abandon
router.post('/plans/:id/abandon', (req, res) => {
  try {
    const db = getDb();
    const id = parseInt(req.params.id, 10);
    if (!Number.isInteger(id) || id <= 0) return res.status(400).json({ error: 'Invalid id' });
    const plan = db.prepare('SELECT * FROM snowball_plans WHERE id = ? AND user_id = ?').get(id, req.user.id);
    if (!plan) return res.status(404).json({ error: 'Plan not found' });
    if (!['active', 'paused'].includes(plan.status)) return res.status(400).json({ error: 'Only active or paused plans can be abandoned' });

    db.prepare(`
      UPDATE snowball_plans SET status = 'abandoned', updated_at = datetime('now') WHERE id = ?
    `).run(id);

    const updated = db.prepare('SELECT * FROM snowball_plans WHERE id = ?').get(id);
    res.json(enrichPlanWithProgress(db, updated));
  } catch (err) {
    console.error('[snowball plans] abandon error:', err.message);
    res.status(500).json({ error: 'Failed to abandon plan' });
  }
});

module.exports = router;