BillTracker/routes/bills.js

const express = require('express');
const router = express.Router();
const { getDb, ensureUserDefaultCategories } = require('../db/database');

const VALID_VISIBILITY = ['default', 'all', 'ranges', 'none'];

function parseDueDay(value) {
  const day = Number(value);
  if (!Number.isInteger(day) || day < 1 || day > 31) {
    return { error: 'due_day must be an integer between 1 and 31' };
  }
  return { value: day };
}

function parseInterestRate(value) {
  if (value === undefined) return { value: undefined };
  if (value === null) return { value: null };
  if (typeof value === 'string' && value.trim() === '') return { value: null };

  const rate = Number(value);
  if (!Number.isFinite(rate) || rate < 0 || rate > 100) {
    return { error: 'interest_rate must be a number between 0 and 100, or null' };
  }
  return { value: rate };
}

// ── GET /api/bills ────────────────────────────────────────────────────────────
router.get('/', (req, res) => {
  const db = getDb();
  ensureUserDefaultCategories(req.user.id);
  const includeInactive = req.query.inactive === 'true';
  const bills = db.prepare(`
    SELECT b.*, c.name AS category_name,
      CASE WHEN EXISTS(
        SELECT 1 FROM bill_history_ranges WHERE bill_id = b.id
      ) THEN 1 ELSE 0 END AS has_history_ranges
    FROM bills b
    LEFT JOIN categories c ON b.category_id = c.id
    WHERE b.user_id = ?
      ${includeInactive ? '' : 'AND b.active = 1'}
    ORDER BY b.due_day ASC, b.name ASC
  `).all(req.user.id);
  res.json(bills);
});

// ── GET /api/bills/:id/monthly-state?year=&month= ─────────────────────────────
router.get('/:id/monthly-state', (req, res) => {
  const db = getDb();
  const billId = parseInt(req.params.id, 10);
  if (!db.prepare('SELECT id FROM bills WHERE id = ? AND user_id = ?').get(billId, req.user.id))
    return res.status(404).json({ error: 'Bill not found' });

  const year  = parseInt(req.query.year,  10);
  const month = parseInt(req.query.month, 10);
  if (isNaN(year)  || year  < 2000 || year  > 2100)
    return res.status(400).json({ error: 'year must be a 4-digit integer between 2000 and 2100' });
  if (isNaN(month) || month < 1    || month > 12)
    return res.status(400).json({ error: 'month must be an integer between 1 and 12' });

  const mbs = db.prepare(
    'SELECT actual_amount, notes, is_skipped FROM monthly_bill_state WHERE bill_id=? AND year=? AND month=?'
  ).get(billId, year, month);

  res.json({
    bill_id:       billId,
    year,
    month,
    actual_amount: mbs?.actual_amount ?? null,
    notes:         mbs?.notes         ?? null,
    is_skipped:    !!(mbs?.is_skipped),
  });
});

// ── PUT /api/bills/:id/monthly-state ──────────────────────────────────────────
router.put('/:id/monthly-state', (req, res) => {
  const db = getDb();
  const billId = parseInt(req.params.id, 10);
  if (!db.prepare('SELECT id FROM bills WHERE id = ? AND user_id = ?').get(billId, req.user.id))
    return res.status(404).json({ error: 'Bill not found' });

  const { year, month, actual_amount, notes, is_skipped } = req.body;

  const y = parseInt(year,  10);
  const m = parseInt(month, 10);
  if (isNaN(y) || y < 2000 || y > 2100)
    return res.status(400).json({ error: 'year must be a 4-digit integer between 2000 and 2100' });
  if (isNaN(m) || m < 1 || m > 12)
    return res.status(400).json({ error: 'month must be an integer between 1 and 12' });

  if (actual_amount !== undefined && actual_amount !== null) {
    const amt = parseFloat(actual_amount);
    if (isNaN(amt) || amt < 0)
      return res.status(400).json({ error: 'actual_amount must be a non-negative number or null' });
  }

  const amt      = actual_amount !== undefined ? (actual_amount === null ? null : parseFloat(actual_amount)) : null;
  const noteVal  = notes      !== undefined ? (notes      || null) : null;
  const skipVal  = is_skipped !== undefined ? (is_skipped ? 1 : 0) : 0;

  db.prepare(`
    INSERT INTO monthly_bill_state (bill_id, year, month, actual_amount, notes, is_skipped, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, datetime('now'))
    ON CONFLICT(bill_id, year, month) DO UPDATE SET
      actual_amount = excluded.actual_amount,
      notes         = excluded.notes,
      is_skipped    = excluded.is_skipped,
      updated_at    = datetime('now')
  `).run(billId, y, m, amt, noteVal, skipVal);

  const saved = db.prepare(
    'SELECT * FROM monthly_bill_state WHERE bill_id=? AND year=? AND month=?'
  ).get(billId, y, m);

  res.json({
    bill_id:       saved.bill_id,
    year:          saved.year,
    month:         saved.month,
    actual_amount: saved.actual_amount,
    notes:         saved.notes,
    is_skipped:    !!saved.is_skipped,
    created_at:    saved.created_at,
    updated_at:    saved.updated_at,
  });
});

// ── GET /api/bills/:id ────────────────────────────────────────────────────────
router.get('/:id', (req, res) => {
  const db = getDb();
  const bill = db.prepare(`
    SELECT b.*, c.name AS category_name,
      CASE WHEN EXISTS(
        SELECT 1 FROM bill_history_ranges WHERE bill_id = b.id
      ) THEN 1 ELSE 0 END AS has_history_ranges
    FROM bills b
    LEFT JOIN categories c ON b.category_id = c.id
    WHERE b.id = ? AND b.user_id = ?
  `).get(req.params.id, req.user.id);
  if (!bill) return res.status(404).json({ error: 'Bill not found' });
  res.json(bill);
});

// ── POST /api/bills ───────────────────────────────────────────────────────────
router.post('/', (req, res) => {
  const db = getDb();
  const {
    name, category_id, due_day, override_due_date, expected_amount, interest_rate,
    billing_cycle, autopay_enabled, autodraft_status, website, username,
    account_info, has_2fa, notes, history_visibility,
  } = req.body;

  if (!name || due_day == null) {
    return res.status(400).json({ error: 'name and due_day are required' });
  }

  const due = parseDueDay(due_day);
  if (due.error) return res.status(400).json({ error: due.error });
  const day = due.value;

  const parsedInterest = parseInterestRate(interest_rate);
  if (parsedInterest.error) return res.status(400).json({ error: parsedInterest.error });

  const bucket = day <= 14 ? '1st' : '15th';
  const catId = category_id || null;
  if (catId && !db.prepare('SELECT id FROM categories WHERE id = ? AND user_id = ?').get(catId, req.user.id)) {
    return res.status(400).json({ error: 'category_id is invalid for this user' });
  }

  const visibility = history_visibility || 'default';
  if (!VALID_VISIBILITY.includes(visibility)) {
    return res.status(400).json({ error: `history_visibility must be one of: ${VALID_VISIBILITY.join(', ')}` });
  }

  const result = db.prepare(`
    INSERT INTO bills
      (user_id, name, category_id, due_day, override_due_date, bucket, expected_amount,
       interest_rate, billing_cycle, autopay_enabled, autodraft_status, website, username,
       account_info, has_2fa, notes, history_visibility, active)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)
  `).run(
    req.user.id,
    name,
    catId,
    day,
    override_due_date || null,
    bucket,
    parseFloat(expected_amount) || 0,
    parsedInterest.value ?? null,
    billing_cycle || 'monthly',
    autopay_enabled ? 1 : 0,
    autodraft_status || 'none',
    website || null,
    username || null,
    account_info || null,
    has_2fa ? 1 : 0,
    notes || null,
    visibility,
  );

  const created = db.prepare('SELECT * FROM bills WHERE id = ?').get(result.lastInsertRowid);
  res.status(201).json(created);
});

// ── PUT /api/bills/:id ────────────────────────────────────────────────────────
router.put('/:id', (req, res) => {
  const db = getDb();
  const existing = db.prepare('SELECT * FROM bills WHERE id = ? AND user_id = ?').get(req.params.id, req.user.id);
  if (!existing) return res.status(404).json({ error: 'Bill not found' });

  const {
    name, category_id, due_day, override_due_date, expected_amount, interest_rate,
    billing_cycle, autopay_enabled, autodraft_status, website, username,
    account_info, has_2fa, notes, active, history_visibility,
  } = req.body;

  const due = due_day !== undefined ? parseDueDay(due_day) : { value: existing.due_day };
  if (due.error) return res.status(400).json({ error: due.error });
  const day = due.value;

  const parsedInterest = parseInterestRate(interest_rate);
  if (parsedInterest.error) return res.status(400).json({ error: parsedInterest.error });

  const bucket = day <= 14 ? '1st' : '15th';
  const nextCategoryId = category_id !== undefined ? (category_id || null) : existing.category_id;
  if (nextCategoryId && !db.prepare('SELECT id FROM categories WHERE id = ? AND user_id = ?').get(nextCategoryId, req.user.id)) {
    return res.status(400).json({ error: 'category_id is invalid for this user' });
  }

  const nextVisibility = history_visibility !== undefined ? history_visibility : existing.history_visibility;
  if (!VALID_VISIBILITY.includes(nextVisibility)) {
    return res.status(400).json({ error: `history_visibility must be one of: ${VALID_VISIBILITY.join(', ')}` });
  }

  db.prepare(`
    UPDATE bills SET
      name = ?, category_id = ?, due_day = ?, override_due_date = ?, bucket = ?,
      expected_amount = ?, interest_rate = ?, billing_cycle = ?, autopay_enabled = ?, autodraft_status = ?,
      website = ?, username = ?, account_info = ?, has_2fa = ?, notes = ?, active = ?,
      history_visibility = ?,
      updated_at = datetime('now')
    WHERE id = ? AND user_id = ?
  `).run(
    name ?? existing.name,
    nextCategoryId,
    day,
    override_due_date !== undefined ? (override_due_date || null) : existing.override_due_date,
    bucket,
    expected_amount != null ? parseFloat(expected_amount) : existing.expected_amount,
    parsedInterest.value !== undefined ? parsedInterest.value : existing.interest_rate,
    billing_cycle ?? existing.billing_cycle,
    autopay_enabled != null ? (autopay_enabled ? 1 : 0) : existing.autopay_enabled,
    autodraft_status ?? existing.autodraft_status,
    website !== undefined ? (website || null) : existing.website,
    username !== undefined ? (username || null) : existing.username,
    account_info !== undefined ? (account_info || null) : existing.account_info,
    has_2fa != null ? (has_2fa ? 1 : 0) : existing.has_2fa,
    notes !== undefined ? (notes || null) : existing.notes,
    active != null ? (active ? 1 : 0) : existing.active,
    nextVisibility,
    req.params.id,
    req.user.id,
  );

  const updated = db.prepare('SELECT * FROM bills WHERE id = ? AND user_id = ?').get(req.params.id, req.user.id);
  res.json(updated);
});

// ── DELETE /api/bills/:id — destructive hard-delete ───────────────────────────
// Permanently removes the bill and all associated data (payments, monthly state,
// history ranges). Inactivation (PUT with active:0) is the safer alternative.
// WARNING: this action is irreversible.
router.delete('/:id', (req, res) => {
  const db = getDb();
  const bill = db.prepare('SELECT id, name FROM bills WHERE id = ? AND user_id = ?').get(req.params.id, req.user.id);
  if (!bill) return res.status(404).json({ error: 'Bill not found' });

  // ON DELETE CASCADE in the schema removes payments, monthly_bill_state, and
  // bill_history_ranges automatically. Verify foreign_keys pragma is ON.
  db.prepare('DELETE FROM bills WHERE id = ? AND user_id = ?').run(req.params.id, req.user.id);

  res.json({
    success: true,
    deleted_bill_id:   bill.id,
    deleted_bill_name: bill.name,
    warning: 'Bill and all associated payments, monthly state, and history ranges were permanently deleted.',
  });
});

// ── GET /api/bills/:id/payments?page=1&limit=20 ───────────────────────────────
router.get('/:id/payments', (req, res) => {
  const db = getDb();
  const bill = db.prepare('SELECT id, name FROM bills WHERE id = ? AND user_id = ?').get(req.params.id, req.user.id);
  if (!bill) return res.status(404).json({ error: 'Bill not found' });

  const limit  = Math.min(parseInt(req.query.limit || '20', 10), 100);
  const page   = Math.max(parseInt(req.query.page  || '1',  10), 1);
  const offset = (page - 1) * limit;

  const total = db.prepare(
    'SELECT COUNT(*) AS n FROM payments WHERE bill_id = ? AND deleted_at IS NULL'
  ).get(req.params.id).n;

  const items = db.prepare(
    'SELECT * FROM payments WHERE bill_id = ? AND deleted_at IS NULL ORDER BY paid_date DESC LIMIT ? OFFSET ?'
  ).all(req.params.id, limit, offset);

  res.json({
    bill_id:    parseInt(req.params.id, 10),
    bill_name:  bill.name,
    total,
    page,
    limit,
    pages:      Math.ceil(total / limit),
    payments:   items,
  });
});

// ── GET /api/bills/:id/history-ranges ────────────────────────────────────────
router.get('/:id/history-ranges', (req, res) => {
  const db = getDb();
  if (!db.prepare('SELECT id FROM bills WHERE id = ? AND user_id = ?').get(req.params.id, req.user.id))
    return res.status(404).json({ error: 'Bill not found' });

  const ranges = db.prepare(
    'SELECT * FROM bill_history_ranges WHERE bill_id = ? ORDER BY start_year ASC, start_month ASC'
  ).all(req.params.id);

  const bill = db.prepare('SELECT history_visibility FROM bills WHERE id = ?').get(req.params.id);

  res.json({ bill_id: parseInt(req.params.id, 10), history_visibility: bill.history_visibility, ranges });
});

// ── POST /api/bills/:id/history-ranges ───────────────────────────────────────
router.post('/:id/history-ranges', (req, res) => {
  const db = getDb();
  if (!db.prepare('SELECT id FROM bills WHERE id = ? AND user_id = ?').get(req.params.id, req.user.id))
    return res.status(404).json({ error: 'Bill not found' });

  const { start_year, start_month, end_year, end_month, label } = req.body;

  const sy = parseInt(start_year, 10);
  const sm = parseInt(start_month, 10);
  if (isNaN(sy) || sy < 2000 || sy > 2100)
    return res.status(400).json({ error: 'start_year must be between 2000 and 2100' });
  if (isNaN(sm) || sm < 1 || sm > 12)
    return res.status(400).json({ error: 'start_month must be between 1 and 12' });

  let ey = null, em = null;
  if (end_year != null) {
    ey = parseInt(end_year, 10);
    if (isNaN(ey) || ey < 2000 || ey > 2100)
      return res.status(400).json({ error: 'end_year must be between 2000 and 2100' });
  }
  if (end_month != null) {
    em = parseInt(end_month, 10);
    if (isNaN(em) || em < 1 || em > 12)
      return res.status(400).json({ error: 'end_month must be between 1 and 12' });
  }
  if ((ey == null) !== (em == null)) {
    return res.status(400).json({ error: 'end_year and end_month must both be provided or both omitted' });
  }
  if (ey != null) {
    const startVal = sy * 12 + sm;
    const endVal   = ey * 12 + em;
    if (endVal < startVal)
      return res.status(400).json({ error: 'end date must be on or after start date' });
  }

  const result = db.prepare(`
    INSERT INTO bill_history_ranges (bill_id, start_year, start_month, end_year, end_month, label)
    VALUES (?, ?, ?, ?, ?, ?)
  `).run(req.params.id, sy, sm, ey, em, label || null);

  const created = db.prepare('SELECT * FROM bill_history_ranges WHERE id = ?').get(result.lastInsertRowid);
  res.status(201).json(created);
});

// ── PUT /api/bills/:id/history-ranges/:rangeId ───────────────────────────────
router.put('/:id/history-ranges/:rangeId', (req, res) => {
  const db = getDb();
  if (!db.prepare('SELECT id FROM bills WHERE id = ? AND user_id = ?').get(req.params.id, req.user.id))
    return res.status(404).json({ error: 'Bill not found' });

  const range = db.prepare('SELECT * FROM bill_history_ranges WHERE id = ? AND bill_id = ?')
    .get(req.params.rangeId, req.params.id);
  if (!range) return res.status(404).json({ error: 'History range not found' });

  const { start_year, start_month, end_year, end_month, label } = req.body;

  const sy = start_year != null ? parseInt(start_year, 10) : range.start_year;
  const sm = start_month != null ? parseInt(start_month, 10) : range.start_month;
  if (isNaN(sy) || sy < 2000 || sy > 2100)
    return res.status(400).json({ error: 'start_year must be between 2000 and 2100' });
  if (isNaN(sm) || sm < 1 || sm > 12)
    return res.status(400).json({ error: 'start_month must be between 1 and 12' });

  let ey = range.end_year;
  let em = range.end_month;
  if (end_year !== undefined) ey = end_year != null ? parseInt(end_year, 10) : null;
  if (end_month !== undefined) em = end_month != null ? parseInt(end_month, 10) : null;

  if (ey != null && (isNaN(ey) || ey < 2000 || ey > 2100))
    return res.status(400).json({ error: 'end_year must be between 2000 and 2100' });
  if (em != null && (isNaN(em) || em < 1 || em > 12))
    return res.status(400).json({ error: 'end_month must be between 1 and 12' });
  if ((ey == null) !== (em == null))
    return res.status(400).json({ error: 'end_year and end_month must both be provided or both omitted' });
  if (ey != null && (ey * 12 + em) < (sy * 12 + sm))
    return res.status(400).json({ error: 'end date must be on or after start date' });

  db.prepare(`
    UPDATE bill_history_ranges
    SET start_year = ?, start_month = ?, end_year = ?, end_month = ?, label = ?,
        updated_at = datetime('now')
    WHERE id = ? AND bill_id = ?
  `).run(sy, sm, ey, em, label !== undefined ? (label || null) : range.label, req.params.rangeId, req.params.id);

  const updated = db.prepare('SELECT * FROM bill_history_ranges WHERE id = ?').get(req.params.rangeId);
  res.json(updated);
});

// ── DELETE /api/bills/:id/history-ranges/:rangeId ────────────────────────────
router.delete('/:id/history-ranges/:rangeId', (req, res) => {
  const db = getDb();
  if (!db.prepare('SELECT id FROM bills WHERE id = ? AND user_id = ?').get(req.params.id, req.user.id))
    return res.status(404).json({ error: 'Bill not found' });

  const range = db.prepare('SELECT id FROM bill_history_ranges WHERE id = ? AND bill_id = ?')
    .get(req.params.rangeId, req.params.id);
  if (!range) return res.status(404).json({ error: 'History range not found' });

  db.prepare('DELETE FROM bill_history_ranges WHERE id = ? AND bill_id = ?')
    .run(req.params.rangeId, req.params.id);

  res.json({ success: true });
});

module.exports = router;