BillTracker/routes/tracker.js

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

// GET /api/tracker?year=2026&month=5
router.get('/', (req, res) => {
  const db = getDb();
  const now   = new Date();
  const year  = parseInt(req.query.year  || now.getFullYear(),    10);
  const month = parseInt(req.query.month || now.getMonth() + 1,   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 todayStr = now.toISOString().slice(0, 10);

  const { start, end } = getCycleRange(year, month);

  // Calculate previous month (with year wrapping)
  const prevMonth = month === 1 ? 12 : month - 1;
  const prevYear = month === 1 ? year - 1 : year;
  const prevMonthRange = getCycleRange(prevYear, prevMonth);

  // Calculate 3-month range for trend analysis
  const threeMonthsAgo = (() => {
    let y = year, m = month - 2;
    while (m <= 0) { m += 12; y -= 1; }
    return { year: y, month: m };
  })();

  const bills = db.prepare(`
    SELECT b.*, c.name AS category_name
    FROM bills b
    LEFT JOIN categories c ON b.category_id = c.id
    WHERE b.active = 1 AND b.user_id = ?
    ORDER BY b.due_day ASC, b.name ASC
  `).all(req.user.id);

  // Batch fetch all monthly bill states for current month
  const billIds = bills.map(bill => bill.id);
  const placeholders = billIds.map(() => '?').join(',');
  
  let monthlyStates = {};
  if (billIds.length > 0) {
    const monthlyStateQuery = `
      SELECT bill_id, actual_amount, notes, is_skipped 
      FROM monthly_bill_state 
      WHERE bill_id IN (${placeholders}) AND year = ? AND month = ?
    `;
    const monthlyStateRows = db.prepare(monthlyStateQuery).all(...billIds, year, month);
    monthlyStates = Object.fromEntries(monthlyStateRows.map(row => [row.bill_id, row]));
  }

  // Batch fetch all payments for current month
  let allPayments = {};
  if (billIds.length > 0) {
    const paymentsQuery = `
      SELECT bill_id, id, amount, paid_date, method, notes, created_at, updated_at
      FROM payments
      WHERE bill_id IN (${placeholders}) AND paid_date BETWEEN ? AND ?
        AND deleted_at IS NULL
      ORDER BY paid_date DESC
    `;
    const paymentRows = db.prepare(paymentsQuery).all(...billIds, start, end);
    
    // Group payments by bill_id
    allPayments = {};
    paymentRows.forEach(row => {
      if (!allPayments[row.bill_id]) {
        allPayments[row.bill_id] = [];
      }
      allPayments[row.bill_id].push(row);
    });
  }

  // Batch fetch all previous month payments
  let prevMonthPayments = {};
  if (billIds.length > 0) {
    const prevPaymentsQuery = `
      SELECT bill_id, SUM(amount) as total_paid
      FROM payments
      WHERE bill_id IN (${placeholders}) AND paid_date BETWEEN ? AND ?
        AND deleted_at IS NULL
      GROUP BY bill_id
    `;
    const prevPaymentRows = db.prepare(prevPaymentsQuery).all(...billIds, prevMonthRange.start, prevMonthRange.end);
    prevMonthPayments = Object.fromEntries(prevPaymentRows.map(row => [row.bill_id, row.total_paid]));
  }

  const rows = bills.map(bill => {
    // Get payments for this bill
    const payments = allPayments[bill.id] || [];

    const row = buildTrackerRow(bill, payments, year, month, todayStr);

    // Overlay monthly state overrides
    const mbs = monthlyStates[bill.id];
    row.actual_amount  = mbs?.actual_amount  ?? null;
    row.monthly_notes  = mbs?.notes          ?? null;
    row.is_skipped     = !!(mbs?.is_skipped);

    // Get previous month paid amount
    row.previous_month_paid = prevMonthPayments[bill.id] || 0;

    return row;
  });

  const totalOverdue  = rows
    .filter(r => !r.is_skipped && (r.status === 'late' || r.status === 'missed'))
    .reduce((s, r) => s + r.balance, 0);

  const activeRows = rows.filter(r => !r.is_skipped);

  // Get starting amounts for this month
  const startingAmounts = db.prepare(`
    SELECT COALESCE(first_amount, 0) + COALESCE(fifteenth_amount, 0) + COALESCE(other_amount, 0) AS combined_amount
    FROM monthly_starting_amounts
    WHERE user_id = ? AND year = ? AND month = ?
  `).get(req.user.id, year, month);

  const totalStarting = startingAmounts?.combined_amount || 0;
  const hasStartingAmounts = !!startingAmounts;
  const activeTotalPaid = activeRows.reduce((s, r) => s + r.total_paid, 0);
  const activeTotalExpected = activeRows.reduce((s, r) => s + r.expected_amount, 0);
  const activeOutstandingBalance = activeRows.reduce((s, r) => s + Math.max(r.balance || 0, 0), 0);
  
  // Calculate previous month total
  const previousMonthTotal = activeRows.reduce((s, r) => s + r.previous_month_paid, 0);

  // Calculate 3-month trend data
  const threeMonthStart = getCycleRange(threeMonthsAgo.year, threeMonthsAgo.month).start;
  const currentMonthEnd = end;
  
  // Get all payments for the last 3 months for this user
  // Join through bills to get user_id since payments table doesn't have user_id
  const threeMonthPayments = db.prepare(`
    SELECT SUM(p.amount) as total_paid, strftime('%Y-%m', p.paid_date) as month_key
    FROM payments p
    JOIN bills b ON p.bill_id = b.id
    WHERE b.user_id = ? AND p.paid_date BETWEEN ? AND ?
      AND p.deleted_at IS NULL
    GROUP BY strftime('%Y-%m', p.paid_date)
  `).all(req.user.id, threeMonthStart, currentMonthEnd);

  // Create a map of month payments for easier access
  const monthlyPaymentsMap = new Map();
  threeMonthPayments.forEach(payment => {
    monthlyPaymentsMap.set(payment.month_key, payment.total_paid);
  });

  // Calculate payments for each of the last 3 months
  const months = [];
  for (let i = 2; i >= 0; i--) {
    const date = new Date(year, month - 1 - i);
    const monthKey = `${date.getFullYear()}-${String(date.getMonth() + 1).padStart(2, '0')}`;
    months.push({
      year: date.getFullYear(),
      month: date.getMonth() + 1,
      key: monthKey,
      payment: parseFloat(monthlyPaymentsMap.get(monthKey) || 0)
    });
  }

  // Calculate 3-month average
  const threeMonthTotal = months.reduce((sum, m) => sum + m.payment, 0);
  const threeMonthAvg = threeMonthTotal / 3;

  // Calculate current month paid (sum of all bills)
  const currentMonthPaid = activeTotalPaid;

  // Calculate percentage change
  let percentChange = 0;
  let direction = 'flat';
  
  if (threeMonthAvg > 0) {
    percentChange = ((currentMonthPaid - threeMonthAvg) / threeMonthAvg) * 100;
    
    // Determine direction based on percentage change
    if (percentChange > 2) {
      direction = 'up';
    } else if (percentChange < -2) {
      direction = 'down';
    } else {
      direction = 'flat';
    }
  }

  // Ensure percentChange is a number with 1 decimal place
  percentChange = parseFloat(percentChange.toFixed(1));

  res.json({
    year, month, today: todayStr,
    summary: {
      total_expected:  activeTotalExpected,
      total_starting:  totalStarting,
      has_starting_amounts: hasStartingAmounts,
      total_paid:      activeTotalPaid,
      remaining:       hasStartingAmounts ? totalStarting - activeTotalPaid : activeOutstandingBalance,
      overdue:         totalOverdue,
      count_paid:      activeRows.filter(r => r.status === 'paid').length,
      count_upcoming:  activeRows.filter(r => r.status === 'upcoming' || r.status === 'due_soon').length,
      count_late:      activeRows.filter(r => r.status === 'late' || r.status === 'missed').length,
      count_autodraft: activeRows.filter(r => r.status === 'autodraft').length,
      previous_month_total: previousMonthTotal,
      trend: {
        three_month_avg: parseFloat(threeMonthAvg.toFixed(2)),
        current_month_paid: parseFloat(currentMonthPaid.toFixed(2)),
        percent_change: percentChange,
        direction: direction
      }
    },
    rows,
  });
});

// GET /api/tracker/upcoming?days=30
// Returns active bills with a due date in the next N days, sorted by due_date asc.
router.get('/upcoming', (req, res) => {
  const db   = getDb();
  const days = Math.max(1, Math.min(parseInt(req.query.days || '30', 10) || 30, 365));
  const now  = new Date();
  const todayStr = now.toISOString().slice(0, 10);

  const year  = now.getFullYear();
  const month = now.getMonth() + 1;
  const { start, end } = getCycleRange(year, month);

  const bills = db.prepare(`
    SELECT b.*, c.name AS category_name
    FROM bills b
    LEFT JOIN categories c ON b.category_id = c.id
    WHERE b.active = 1 AND b.user_id = ?
  `).all(req.user.id);

  const cutoff = new Date(now);
  cutoff.setDate(cutoff.getDate() + days);
  const cutoffStr = cutoff.toISOString().slice(0, 10);

  // Get all bill IDs for batch processing
  const billIds = bills.map(bill => bill.id);
  
  // Batch fetch all payments for all bills in the date range
  let allPayments = {};
  if (billIds.length > 0) {
    const placeholders = billIds.map(() => '?').join(',');
    const paymentsQuery = `
      SELECT bill_id, id, amount, paid_date, method, notes, created_at, updated_at
      FROM payments
      WHERE bill_id IN (${placeholders}) AND paid_date BETWEEN ? AND ?
        AND deleted_at IS NULL
      ORDER BY paid_date DESC
    `;
    const paymentRows = db.prepare(paymentsQuery).all(...billIds, start, end);
    
    // Group payments by bill_id
    allPayments = {};
    paymentRows.forEach(row => {
      if (!allPayments[row.bill_id]) {
        allPayments[row.bill_id] = [];
      }
      allPayments[row.bill_id].push(row);
    });
  }

  const upcoming = [];

  for (const bill of bills) {
    const dueDate = resolveDueDate(bill, year, month);
    if (dueDate < todayStr || dueDate > cutoffStr) continue;

    // Get payments for this bill from the batched results
    const payments = allPayments[bill.id] || [];

    const row = buildTrackerRow(bill, payments, year, month, todayStr);
    if (row.status === 'paid') continue; // skip already paid

    upcoming.push({
      id:              bill.id,
      name:            bill.name,
      category_name:   bill.category_name,
      due_date:        dueDate,
      expected_amount: bill.expected_amount,
      status:          row.status,
      days_until_due:  Math.floor((new Date(dueDate) - now) / 86400000),
    });
  }

  upcoming.sort((a, b) => a.due_date.localeCompare(b.due_date));
  res.json({ days, today: todayStr, upcoming });
});

module.exports = router;