BillTracker/routes/summary.js

const express = require('express');
const router = express.Router();
const { getDb } = require('../db/database');
const { getCycleRange, resolveDueDate } = require('../services/statusService');
const { getUserSettings } = require('../services/userSettings');
const { accountingActiveSql } = require('../services/paymentAccountingService');
const { toCents, fromCents } = require('../utils/money');

const DEFAULT_INCOME_LABEL = 'Salary';
const DEFAULT_PENDING_DAYS = 3;

// Build bank tracking summary when the user has enabled SimpleFIN bank tracking.
// Returns null when disabled, the account isn't found, or bank sync isn't set up.
function buildBankTrackingSummary(db, userId, year, month) {
  try {
  const settings = getUserSettings(userId);
  if (settings.bank_tracking_enabled !== 'true') return null;

  const accountId = parseInt(settings.bank_tracking_account_id, 10);
  if (!Number.isInteger(accountId) || accountId < 1) return null;

  const account = db.prepare(`
    SELECT id, name, org_name, account_type, balance, available_balance, updated_at
    FROM financial_accounts
    WHERE id = ? AND user_id = ?
  `).get(accountId, userId);

  if (!account || account.balance === null) return null;

  const pendingDays = parseInt(settings.bank_tracking_pending_days, 10);
  const effectivePendingDays = Number.isInteger(pendingDays) && pendingDays >= 0
    ? pendingDays : DEFAULT_PENDING_DAYS;

  // Only count manually-entered payments as pending — bank-synced payments
  // (provider_sync) are already reflected in the live bank balance, so
  // including them would double-deduct.
  const pendingRow = effectivePendingDays > 0
    ? db.prepare(`
        SELECT COALESCE(SUM(p.amount), 0) AS pending_total
        FROM payments p
        JOIN bills b ON b.id = p.bill_id
        WHERE b.user_id = ?
          AND p.paid_date >= date('now', '-' || ? || ' days')
          AND p.paid_date <= date('now')
          AND b.deleted_at IS NULL
          AND ${accountingActiveSql('p')}
          AND (p.payment_source IS NULL OR p.payment_source NOT IN ('provider_sync', 'transaction_match', 'auto_match'))
      `).get(userId, effectivePendingDays)
    : { pending_total: 0 };

  // Unpaid bills remaining this month (not skipped, not yet paid), occurrence-gated
  // in JS so annual / off-month quarterly bills don't inflate the total (QA-B5-02,
  // same root as QA-B5-01 — SQL can't call resolveDueDate).
  const { start, end } = getCycleRange(year, month);
  const unpaidCandidates = db.prepare(`
    SELECT b.due_day, b.billing_cycle, b.cycle_type, b.cycle_day,
      CASE
        WHEN m.actual_amount IS NOT NULL THEN m.actual_amount
        ELSE COALESCE(b.expected_amount, 0)
      END AS amount_cents
    FROM bills b
    LEFT JOIN monthly_bill_state m ON m.bill_id = b.id AND m.year = ? AND m.month = ?
    LEFT JOIN (
      SELECT bill_id, SUM(amount) AS paid_sum
      FROM payments
      WHERE paid_date BETWEEN ? AND ?
        AND deleted_at IS NULL
        AND ${accountingActiveSql()}
      GROUP BY bill_id
    ) pay ON pay.bill_id = b.id
    WHERE b.user_id = ?
      AND b.active = 1
      AND b.deleted_at IS NULL
      AND COALESCE(m.is_skipped, 0) = 0
      AND COALESCE(pay.paid_sum, 0) = 0
  `).all(year, month, start, end, userId);
  const unpaidTotalCents = unpaidCandidates
    .filter(row => resolveDueDate(row, year, month))
    .reduce((sum, row) => sum + (row.amount_cents || 0), 0);

  const balanceDollars   = money(account.balance / 100);
  const pendingDollars   = fromCents(pendingRow.pending_total);
  const effectiveDollars = money(balanceDollars - pendingDollars);
  const unpaidDollars    = fromCents(unpaidTotalCents);

  return {
    enabled:           true,
    account_id:        account.id,
    account_name:      account.name,
    org_name:          account.org_name,
    account_type:      account.account_type,
    balance:           balanceDollars,
    available_balance: account.available_balance !== null ? money(account.available_balance / 100) : null,
    last_updated:      account.updated_at,
    pending_payments:  pendingDollars,
    pending_days:      effectivePendingDays,
    effective_balance: effectiveDollars,
    unpaid_this_month: unpaidDollars,
    remaining:         money(effectiveDollars - unpaidDollars),
  };
  } catch (err) {
    console.error('[buildBankTrackingSummary] Error:', err.message);
    return null;
  }
}

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) ? Math.round(n * 100) / 100 : 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: fromCents(row?.first_amount || 0),
    fifteenth_amount: fromCents(row?.fifteenth_amount || 0),
    other_amount: fromCents(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 b.deleted_at IS NULL
      AND p.paid_date BETWEEN ? AND ?
      AND p.deleted_at IS NULL
      AND ${accountingActiveSql('p')}
      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 b.deleted_at IS NULL
      AND p.paid_date BETWEEN ? AND ?
      AND p.deleted_at IS NULL
      AND ${accountingActiveSql('p')}
      AND b.due_day BETWEEN 15 AND 31
  `).get(userId, start, end);

  // Paid from other bucket: bills with due_day outside 1-14 and 15-31 (shouldn't happen with current schema)
  const otherPaid = 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 b.deleted_at IS NULL
      AND p.paid_date BETWEEN ? AND ?
      AND p.deleted_at IS NULL
      AND ${accountingActiveSql('p')}
      AND (b.due_day < 1 OR b.due_day > 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 b.deleted_at IS NULL
      AND p.paid_date BETWEEN ? AND ?
      AND p.deleted_at IS NULL
      AND ${accountingActiveSql('p')}
  `).get(userId, start, end);

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

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

  const combined_amount = money(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_from_other: paid.paid_from_other,
    paid_total,
    first_remaining: money(amounts.first_amount - paid.paid_from_first),
    fifteenth_remaining: money(amounts.fifteenth_amount - paid.paid_from_fifteenth),
    other_remaining: money(amounts.other_amount - paid.paid_from_other),
    combined_remaining: money(combined_amount - paid_total),
  };
}

function getIncome(db, userId, year, month) {
  const row = db.prepare(`
    SELECT id, label, amount
    FROM monthly_income
    WHERE user_id = ? AND year = ? AND month = ?
  `).get(userId, year, month);

  return {
    id: row?.id || null,
    label: row?.label || DEFAULT_INCOME_LABEL,
    amount: fromCents(row?.amount ?? 0),
  };
}

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

  const billRows = db.prepare(`
    SELECT
      b.id AS bill_id,
      b.name,
      b.expected_amount,
      b.due_day,
      b.billing_cycle,
      b.cycle_type,
      b.cycle_day,
      c.name AS category_name,
      m.actual_amount,
      m.is_skipped,
      b.sort_order,
      b.autopay_enabled,
      b.is_subscription,
      CASE WHEN mr.bill_id IS NOT NULL THEN 1 ELSE 0 END AS has_merchant_rule,
      CASE WHEN lt.matched_bill_id IS NOT NULL THEN 1 ELSE 0 END AS has_linked_transactions
    FROM bills b
    LEFT JOIN categories c ON c.id = b.category_id AND c.user_id = b.user_id AND c.deleted_at IS NULL
    LEFT JOIN monthly_bill_state m ON m.bill_id = b.id AND m.year = ? AND m.month = ?
    LEFT JOIN (SELECT DISTINCT bill_id FROM bill_merchant_rules) mr ON mr.bill_id = b.id
    LEFT JOIN (SELECT DISTINCT matched_bill_id FROM transactions WHERE match_status = 'matched') lt ON lt.matched_bill_id = b.id
    WHERE b.user_id = ? AND b.active = 1 AND b.deleted_at IS NULL
    ORDER BY CASE WHEN b.sort_order IS NULL THEN 1 ELSE 0 END,
      b.sort_order ASC,
      b.due_day ASC,
      b.name ASC
  `).all(year, month, userId)
    // QA-B5-01: only bills that actually occur in this month (matches the Tracker's
    // resolveDueDate gating) — otherwise annual / off-month quarterly bills inflate
    // the monthly expense list and total, disagreeing with the Tracker.
    .filter(row => resolveDueDate(row, year, month));

  const billIds = billRows.map(row => row.bill_id);
  const paymentMap = new Map();

  if (billIds.length > 0) {
    const placeholders = billIds.map(() => '?').join(', ');
    const payments = db.prepare(`
      SELECT p.bill_id, COUNT(p.id) AS payment_count, SUM(p.amount) AS paid_amount
      FROM payments p
      JOIN bills b ON b.id = p.bill_id
      WHERE b.user_id = ?
        AND b.deleted_at IS NULL
        AND p.bill_id IN (${placeholders})
        AND p.paid_date BETWEEN ? AND ?
        AND p.deleted_at IS NULL
        AND ${accountingActiveSql('p')}
      GROUP BY p.bill_id
    `).all(userId, ...billIds, start, end);

    for (const row of payments) {
      paymentMap.set(row.bill_id, {
        payment_count: row.payment_count || 0,
        paid_amount: fromCents(row.paid_amount),
      });
    }
  }

  const expenses = billRows.map(row => {
    const payment = paymentMap.get(row.bill_id) || { payment_count: 0, paid_amount: 0 };
    const hasActual = row.actual_amount !== null && row.actual_amount !== undefined;
    const displayAmount = fromCents(hasActual ? row.actual_amount : row.expected_amount);
    const paidAmount = money(payment.paid_amount);

    return {
      bill_id: row.bill_id,
      name: row.name,
      expected_amount: fromCents(row.expected_amount),
      actual_amount: hasActual ? fromCents(row.actual_amount) : null,
      display_amount: displayAmount,
      is_paid: payment.payment_count > 0,
      paid_amount: paidAmount,
      payment_count: payment.payment_count,
      is_skipped: !!row.is_skipped,
      due_day: row.due_day,
      category_name: row.category_name || null,
      autopay_enabled: !!row.autopay_enabled,
      is_subscription: !!row.is_subscription,
      has_merchant_rule: !!row.has_merchant_rule,
      has_linked_transactions: !!row.has_linked_transactions,
    };
  });

  const countedExpenses = expenses.filter(expense => !expense.is_skipped);
  const incomeTotal = money(income.amount);
  const expenseTotal = money(countedExpenses.reduce((sum, expense) => sum + expense.display_amount, 0));
  const paidTotal = money(countedExpenses.reduce((sum, expense) => sum + expense.paid_amount, 0));
  const paidExpenseCount = countedExpenses.filter(expense => expense.is_paid).length;
  const starting_amounts = buildStartingAmountsSummary(db, userId, year, month);
  const bank_tracking = buildBankTrackingSummary(db, userId, year, month);
  // When bank tracking is on, drive the "plan base" from the effective bank balance
  const planBaseTotal = bank_tracking
    ? bank_tracking.effective_balance
    : money(starting_amounts.combined_amount);
  const result = money(planBaseTotal - expenseTotal);

  // Previous month context
  let previous_month = null;
  if (month > 1) {
    const prevMonth = month - 1;
    const prevYear = year;
    const prevStarting = buildStartingAmountsSummary(db, userId, prevYear, prevMonth);
    if (prevStarting.combined_amount > 0) {
      previous_month = {
        year: prevYear,
        month: prevMonth,
        combined_remaining: prevStarting.combined_remaining,
      };
    }
  } else if (year > 2000) {
    const prevMonth = 12;
    const prevYear = year - 1;
    const prevStarting = buildStartingAmountsSummary(db, userId, prevYear, prevMonth);
    if (prevStarting.combined_amount > 0) {
      previous_month = {
        year: prevYear,
        month: prevMonth,
        combined_remaining: prevStarting.combined_remaining,
      };
    }
  }

  return {
    year,
    month,
    income,
    expenses,
    starting_amounts,
    bank_tracking: bank_tracking ?? { enabled: false },
    previous_month,
    summary: {
      income_total: incomeTotal,
      starting_total: planBaseTotal,
      expense_total: expenseTotal,
      paid_expense_count: paidExpenseCount,
      expense_count: countedExpenses.length,
      paid_total: starting_amounts.paid_total,
      remaining_expense_total: money(Math.max(0, expenseTotal - paidTotal)),
      result,
    },
    chart: [
      { type: 'Starting', amount: planBaseTotal },
      { type: 'Expenses', amount: expenseTotal },
      { type: 'Remaining', amount: result },
    ],
    generated_at: new Date().toISOString(),
  };
}

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(buildSummary(db, req.user.id, parsed.year, parsed.month));
});

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

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

  const label = String(req.body?.label || DEFAULT_INCOME_LABEL).trim().slice(0, 80) || DEFAULT_INCOME_LABEL;
  const db = getDb();

  db.prepare(`
    INSERT INTO monthly_income (user_id, year, month, label, amount, updated_at)
    VALUES (?, ?, ?, ?, ?, datetime('now'))
    ON CONFLICT(user_id, year, month) DO UPDATE SET
      label = excluded.label,
      amount = excluded.amount,
      updated_at = datetime('now')
  `).run(req.user.id, parsed.year, parsed.month, label, toCents(amount));

  res.json({
    year: parsed.year,
    month: parsed.month,
    income: getIncome(db, req.user.id, parsed.year, parsed.month),
  });
});

module.exports = router;