accounting/server/routes/statistics.js

const express = require('express');
const { getDb } = require('../db');
const { roundCurrency } = require('../utils/accounting');
const { getWeekRange } = require('../utils/date');

const router = express.Router();

function getUsers(db) {
  return db.all('SELECT name FROM users ORDER BY sort_order, id');
}

function getTotalByType(db, whereClause, params, type) {
  const row = db.get(
    `SELECT COALESCE(SUM(amount), 0) AS total FROM records WHERE ${whereClause} AND type = ?`,
    ...params,
    type
  );

  return roundCurrency(row?.total);
}

function getUserStats(db, whereClause, params) {
  const users = getUsers(db);
  const result = Object.fromEntries(
    users.map((user) => [user.name, { income: 0, expense: 0 }])
  );

  const rows = db.all(`
    SELECT user, type, ROUND(SUM(amount), 2) AS total
    FROM records
    WHERE ${whereClause}
    GROUP BY user, type
  `, ...params);

  rows.forEach((row) => {
    if (!result[row.user] || !['income', 'expense'].includes(row.type)) {
      return;
    }

    result[row.user][row.type] = roundCurrency(row.total);
  });

  return result;
}

function getCategoriesWithUsers(db, whereClause, params) {
  const users = getUsers(db);
  const categories = db.all(`
    SELECT c.id, c.name, c.icon, ROUND(SUM(r.amount), 2) AS total
    FROM records r
    JOIN categories c ON r.category_id = c.id
    WHERE ${whereClause} AND r.type = 'expense'
    GROUP BY c.id
    ORDER BY total DESC, c.sort_order ASC, c.id ASC
  `, ...params);

  const userRows = db.all(`
    SELECT c.id AS category_id, r.user, ROUND(SUM(r.amount), 2) AS total
    FROM records r
    JOIN categories c ON r.category_id = c.id
    WHERE ${whereClause} AND r.type = 'expense'
    GROUP BY c.id, r.user
  `, ...params);

  const userTotals = new Map();
  userRows.forEach((row) => {
    if (!userTotals.has(row.category_id)) {
      userTotals.set(row.category_id, new Map());
    }
    userTotals.get(row.category_id).set(row.user, roundCurrency(row.total));
  });

  return categories.map((category) => ({
    name: category.name,
    icon: category.icon,
    total: roundCurrency(category.total),
    users: users.map((user) => ({
      user: user.name,
      total: userTotals.get(category.id)?.get(user.name) || 0,
    })),
  }));
}

function getGroupedSeries(db, whereClause, params, bucketExpr, bucketAlias) {
  return db.all(`
    SELECT ${bucketExpr} AS ${bucketAlias}, type, user, ROUND(SUM(amount), 2) AS total
    FROM records
    WHERE ${whereClause}
    GROUP BY ${bucketAlias}, type, user
    ORDER BY ${bucketAlias}, type, user
  `, ...params).map((row) => ({
    ...row,
    total: roundCurrency(row.total),
  }));
}

function buildStatsPayload(db, whereClause, params, periodPayload, seriesKey, seriesValue) {
  const income = getTotalByType(db, whereClause, params, 'income');
  const expense = getTotalByType(db, whereClause, params, 'expense');

  return {
    ...periodPayload,
    income,
    expense,
    balance: roundCurrency(income - expense),
    [seriesKey]: seriesValue,
    categories: getCategoriesWithUsers(db, whereClause, params),
    userStats: getUserStats(db, whereClause, params),
  };
}

router.get('/daily', (req, res) => {
  const db = getDb();
  const { date } = req.query;

  if (!date) {
    return res.status(400).json({ error: '请提供日期参数' });
  }

  const dailyData = getGroupedSeries(db, 'date = ?', [date], 'date', 'date');
  res.json(buildStatsPayload(db, 'date = ?', [date], { date }, 'dailyData', dailyData));
});

router.get('/weekly', (req, res) => {
  const db = getDb();
  const { date } = req.query;

  if (!date) {
    return res.status(400).json({ error: '请提供日期参数' });
  }

  const range = getWeekRange(date);
  if (!range) {
    return res.status(400).json({ error: '日期格式不正确' });
  }

  const { startDate, endDate } = range;
  const dailyData = getGroupedSeries(
    db,
    'date BETWEEN ? AND ?',
    [startDate, endDate],
    'date',
    'date'
  );

  res.json(
    buildStatsPayload(
      db,
      'date BETWEEN ? AND ?',
      [startDate, endDate],
      { startDate, endDate },
      'dailyData',
      dailyData
    )
  );
});

router.get('/monthly', (req, res) => {
  const db = getDb();
  const { month } = req.query;

  if (!/^\d{4}-\d{2}$/.test(String(month ?? ''))) {
    return res.status(400).json({ error: '请提供正确的月份参数' });
  }

  const pattern = `${month}%`;
  const dailyData = getGroupedSeries(db, 'date LIKE ?', [pattern], 'date', 'date');

  res.json(
    buildStatsPayload(db, 'date LIKE ?', [pattern], { month }, 'dailyData', dailyData)
  );
});

router.get('/yearly', (req, res) => {
  const db = getDb();
  const { year } = req.query;

  if (!/^\d{4}$/.test(String(year ?? ''))) {
    return res.status(400).json({ error: '请提供正确的年份参数' });
  }

  const pattern = `${year}%`;
  const monthlyData = getGroupedSeries(
    db,
    'date LIKE ?',
    [pattern],
    'substr(date, 1, 7)',
    'month'
  );

  res.json(
    buildStatsPayload(db, 'date LIKE ?', [pattern], { year }, 'monthlyData', monthlyData)
  );
});

router.get('/balance', (req, res) => {
  const db = getDb();
  const totalIncome = getTotalByType(db, '1 = 1', [], 'income');
  const totalExpense = getTotalByType(db, '1 = 1', [], 'expense');

  const userBalances = getUsers(db).map((user) => {
    const initialBalance = roundCurrency(
      db.get('SELECT initial_balance FROM user_balance_settings WHERE user_name = ?', user.name)
        ?.initial_balance
    );
    const income = getTotalByType(db, 'user = ?', [user.name], 'income');
    const expense = getTotalByType(db, 'user = ?', [user.name], 'expense');

    return {
      user: user.name,
      initial_balance: initialBalance,
      income,
      expense,
      balance: roundCurrency(initialBalance + income - expense),
    };
  });

  const totalInitialBalance = roundCurrency(
    userBalances.reduce((sum, item) => sum + item.initial_balance, 0)
  );

  res.json({
    initial_balance: totalInitialBalance,
    total_income: totalIncome,
    total_expense: totalExpense,
    current_balance: roundCurrency(totalInitialBalance + totalIncome - totalExpense),
    userBalances,
  });
});

router.get('/trend', (req, res) => {
  const db = getDb();
  const { start, end } = req.query;

  if (!start || !end) {
    return res.status(400).json({ error: '请提供开始和结束日期' });
  }

  res.json(
    getGroupedSeries(db, 'date BETWEEN ? AND ?', [start, end], 'date', 'date')
  );
});

module.exports = router;