BillTracker/services/amountSuggestionService.cts

'use strict';

import type { Dollars } from '../utils/money.mts';
import type { Db } from '../types/db';

const { accountingActiveSql } = require('./paymentAccountingService.cts');
// require(esm) of the branded money helpers; cast so fromCents keeps its
// Cents→Dollars signature (paymentValidation.cts uses the same pattern).
const { fromCents } = require('../utils/money.mts') as typeof import('../utils/money.mts');

interface PriorMonth {
  y: number;
  m: number;
  key: string;
}
interface AmountSuggestion {
  suggestion: Dollars | null;
  months_used: number;
  confidence: 'high' | 'low';
}

// The 6 calendar months immediately preceding (year, month), newest first, each
// as { y, m, key: 'YYYY-MM' }.
function priorMonths(year: number, month: number, count = 6): PriorMonth[] {
  const list: PriorMonth[] = [];
  let y = year;
  let m = month;
  for (let i = 0; i < count; i++) {
    m -= 1;
    if (m === 0) {
      m = 12;
      y -= 1;
    }
    list.push({ y, m, key: `${y}-${String(m).padStart(2, '0')}` });
  }
  return list;
}

// Rolling median of a bill's monthly amounts → a suggestion object (or null).
function medianSuggestion(amounts: number[]): AmountSuggestion | null {
  if (amounts.length === 0) return null;
  const sorted = [...amounts].sort((a, b) => a - b);
  const mid = Math.floor(sorted.length / 2);
  const median = sorted.length % 2 === 0 ? (sorted[mid - 1] + sorted[mid]) / 2 : sorted[mid];
  return {
    suggestion: fromCents(Math.round(median)),
    months_used: amounts.length,
    confidence: amounts.length >= 3 ? 'high' : 'low',
  };
}

/**
 * Computes a suggested expected amount for a bill based on the rolling median
 * of the last 6 months of actual data. Prefers monthly_bill_state.actual_amount
 * (user-corrected values) over raw payment sums.
 */
function computeAmountSuggestion(
  db: Db,
  billId: number,
  year: number,
  month: number,
): AmountSuggestion | null {
  const amounts: number[] = [];

  for (const { y, m } of priorMonths(year, month, 6)) {
    const mbs = db
      .prepare(
        'SELECT actual_amount FROM monthly_bill_state WHERE bill_id = ? AND year = ? AND month = ?',
      )
      .get(billId, y, m);

    if (mbs?.actual_amount != null) {
      amounts.push(mbs.actual_amount);
      continue;
    }

    const result = db
      .prepare(
        `
      SELECT COALESCE(SUM(amount), 0) AS total
      FROM payments
      WHERE bill_id = ?
        AND deleted_at IS NULL
        AND ${accountingActiveSql()}
        AND strftime('%Y', paid_date) = ?
        AND strftime('%m', paid_date) = ?
    `,
      )
      .get(billId, String(y), String(m).padStart(2, '0'));

    if (result.total > 0) amounts.push(result.total);
  }

  return medianSuggestion(amounts);
}

/**
 * Batched form of computeAmountSuggestion for many bills at once: two queries
 * total (monthly_bill_state + grouped payment sums) instead of up to 12 per
 * bill. Returns a Map<billId, suggestion|null>. Behavior-identical to calling
 * computeAmountSuggestion per bill (guarded by amountSuggestionService.test.js).
 */
function computeAmountSuggestionsBatch(
  db: Db,
  billIds: number[],
  year: number,
  month: number,
): Map<number, AmountSuggestion | null> {
  const result = new Map<number, AmountSuggestion | null>();
  if (!billIds || billIds.length === 0) return result;

  const months = priorMonths(year, month, 6);
  const monthKeys = new Set(months.map((mm) => mm.key));
  const minKey = months[months.length - 1].key; // earliest (month − 6)
  const maxKey = months[0].key; // latest   (month − 1)
  // Broad window that fully spans the 6 months; the monthKeys filter below is
  // the exact gate, so a '-01'…'-31' string window is safe for ISO dates.
  const windowStart = `${minKey}-01`;
  const windowEnd = `${maxKey}-31`;

  const placeholders = billIds.map(() => '?').join(',');

  // User-corrected monthly amounts.
  const mbsMap = new Map<number, Record<string, number>>(); // billId → { 'YYYY-MM': actual_amount }
  const mbsRows = db
    .prepare(
      `
    SELECT bill_id, year, month, actual_amount
    FROM monthly_bill_state
    WHERE bill_id IN (${placeholders})
  `,
    )
    .all(...billIds);
  for (const r of mbsRows) {
    if (r.actual_amount == null) continue;
    const key = `${r.year}-${String(r.month).padStart(2, '0')}`;
    if (!monthKeys.has(key)) continue;
    let bucket = mbsMap.get(r.bill_id);
    if (!bucket) {
      bucket = {};
      mbsMap.set(r.bill_id, bucket);
    }
    bucket[key] = r.actual_amount;
  }

  // Payment sums grouped by bill + month.
  const payMap = new Map<number, Record<string, number>>(); // billId → { 'YYYY-MM': total }
  const payRows = db
    .prepare(
      `
    SELECT bill_id, strftime('%Y-%m', paid_date) AS ym, COALESCE(SUM(amount), 0) AS total
    FROM payments
    WHERE bill_id IN (${placeholders})
      AND deleted_at IS NULL
      AND ${accountingActiveSql()}
      AND paid_date BETWEEN ? AND ?
    GROUP BY bill_id, ym
  `,
    )
    .all(...billIds, windowStart, windowEnd);
  for (const r of payRows) {
    if (!monthKeys.has(r.ym)) continue;
    let bucket = payMap.get(r.bill_id);
    if (!bucket) {
      bucket = {};
      payMap.set(r.bill_id, bucket);
    }
    bucket[r.ym] = r.total;
  }

  for (const billId of billIds) {
    const amounts: number[] = [];
    const mbsFor = mbsMap.get(billId) || {};
    const payFor = payMap.get(billId) || {};
    for (const { key } of months) {
      if (mbsFor[key] != null) {
        amounts.push(mbsFor[key]);
        continue;
      }
      const total = payFor[key] || 0;
      if (total > 0) amounts.push(total);
    }
    result.set(billId, medianSuggestion(amounts));
  }

  return result;
}

module.exports = { computeAmountSuggestion, computeAmountSuggestionsBatch };