Blame for src/controllers/reportController.js

const Order = require('../models/Order');
const Expense = require('../models/Expense');
const PlatformSale = require('../models/PlatformSale');
const Setting = require('../models/Setting');
const {
  getBusinessDate,
  getBusinessDayRange,
  getBusinessDateRange,
  getCurrentBusinessDayRange,
} = require('../utils/businessDate');

// ── Helpers ──────────────────────────────────────────────────

const buildOrderFilter = (from, to, extra = {}) => ({
  status: 'completed',
  createdAt: { $gte: from, $lte: to },
  ...extra,
});

// Default financial report settings
const DEFAULT_FINANCIAL_SETTINGS = {
  chefSharePercent: 30,
  directorsSharePercent: 70,
  directorCount: 3,
  directorAdjustments: [],
};

// ── Settings Endpoints ────────────────────────────────────────

// GET /api/reports/settings
exports.getSettings = async (req, res) => {
  const doc = await Setting.findOne({ key: 'financial_settings' });
  res.json(doc ? doc.value : DEFAULT_FINANCIAL_SETTINGS);
};

// PUT /api/reports/settings
exports.updateSettings = async (req, res) => {
  const updatedValue = { ...DEFAULT_FINANCIAL_SETTINGS, ...req.body };
  const doc = await Setting.findOneAndUpdate(
    { key: 'financial_settings' },
    { value: updatedValue },
    { upsert: true, new: true, setDefaultsOnInsert: true }
  );
  res.json(doc.value);
};

// ── Daily Sales Summary ───────────────────────────────────────

// GET /api/reports/daily?date=2026-07-17
exports.dailySummary = async (req, res) => {
  const { date } = req.query;
  const { from, to, businessDate } = getBusinessDayRange(date);

  const orders = await Order.find(buildOrderFilter(from, to));

  const totalSales = orders.reduce((s, o) => s + o.netTotal, 0);
  const orderCount = orders.length;
  const avgOrderValue = orderCount ? totalSales / orderCount : 0;

  const byType = orders.reduce((acc, o) => {
    acc[o.orderType] = (acc[o.orderType] || 0) + o.netTotal;
    return acc;
  }, {});

  res.json({ date: businessDate, totalSales, orderCount, avgOrderValue, byType });
};

// ── Date-Range Report ─────────────────────────────────────────

// GET /api/reports/range?from=2026-07-01&to=2026-07-31
exports.rangeReport = async (req, res) => {
  const { from, to } = req.query;
  if (!from || !to) return res.status(400).json({ message: 'from and to dates required' });

  const { from: fromDate, to: toDate } = getBusinessDateRange(from, to);

  const orders = await Order.find(buildOrderFilter(fromDate, toDate));
  const totalSales = orders.reduce((s, o) => s + o.netTotal, 0);
  const orderCount = orders.length;

  // Group by business day (orders 00:00-05:00 belong to previous day)
  const byDay = {};
  for (const o of orders) {
    const day = getBusinessDate(o.createdAt);
    if (!byDay[day]) byDay[day] = { sales: 0, count: 0 };
    byDay[day].sales += o.netTotal;
    byDay[day].count += 1;
  }

  res.json({ from, to, totalSales, orderCount, byDay });
};

// ── Top-Selling Items ─────────────────────────────────────────

// GET /api/reports/top-items?from=...&to=...&limit=10
exports.topItems = async (req, res) => {
  const { from, to, limit = 10 } = req.query;
  const matchStage = { status: 'completed' };

  if (from || to) {
    const { from: fromDate, to: toDate } = getBusinessDateRange(from, to);
    matchStage.createdAt = { $gte: fromDate, $lte: toDate };
  }

  const results = await Order.aggregate([
    { $match: matchStage },
    { $unwind: '$items' },
    {
      $group: {
        _id: { menuItem: '$items.menuItem', variantLabel: '$items.variantLabel' },
        name: { $first: '$items.menuItemName' },
        variantLabel: { $first: '$items.variantLabel' },
        totalQty: { $sum: '$items.qty' },
        totalRevenue: { $sum: { $multiply: ['$items.qty', '$items.priceAtSale'] } },
      },
    },
    { $sort: { totalQty: -1 } },
    { $limit: Number(limit) },
  ]);

  res.json(results);
};

// ── Profit & Loss ─────────────────────────────────────────────

// GET /api/reports/profit-loss?from=...&to=...
exports.profitLoss = async (req, res) => {
  const { from, to } = req.query;
  if (!from || !to) return res.status(400).json({ message: 'from and to dates required' });

  const { from: fromDate, to: toDate } = getBusinessDateRange(from, to);

  // Calendar dates for expenses
  const [fy, fm, fd] = from.split('-').map(Number);
  const expenseFrom = new Date(fy, fm - 1, fd, 0, 0, 0, 0);
  const [ty, tm, td] = to.split('-').map(Number);
  const expenseTo = new Date(ty, tm - 1, td, 23, 59, 59, 999);

  const [orders, expenses, platformSales] = await Promise.all([
    Order.find(buildOrderFilter(fromDate, toDate)),
    Expense.find({ date: { $gte: expenseFrom, $lte: expenseTo } }),
    PlatformSale.find({ date: { $gte: expenseFrom, $lte: expenseTo } }),
  ]);

  const posSales = orders.reduce((s, o) => s + o.netTotal, 0);
  const platformTotal = platformSales.reduce((s, p) => s + p.amount, 0);
  const totalSales = posSales + platformTotal;
  const totalExpenses = expenses.reduce((s, e) => s + e.amount, 0);
  const netProfit = totalSales - totalExpenses;

  const expensesByCategory = expenses.reduce((acc, e) => {
    acc[e.category] = (acc[e.category] || 0) + e.amount;
    return acc;
  }, {});

  res.json({
    from,
    to,
    posSales,
    platformSales: platformTotal,
    totalSales,
    totalExpenses,
    netProfit,
    expensesByCategory,
  });
};

// ── Monthly Accounting Sheet (Exact Spreadsheet Format) ────────

// GET /api/reports/monthly-sheet?month=2026-07
exports.monthlySheet = async (req, res) => {
  const monthParam = req.query.month || getBusinessDate().substring(0, 7); // "YYYY-MM"
  const [yearStr, monthStr] = monthParam.split('-');
  const year = Number(yearStr);
  const monthNum = Number(monthStr);

  if (isNaN(year) || isNaN(monthNum) || monthNum < 1 || monthNum > 12) {
    return res.status(400).json({ message: 'Invalid month format, expected YYYY-MM' });
  }

  // Days in month
  const totalDays = new Date(year, monthNum, 0).getDate();

  // 1. Business date range for the whole month
  // Month starts on Day 1 at 05:00 AM
  const monthStart = new Date(year, monthNum - 1, 1, 5, 0, 0, 0);
  // Month ends on Day (totalDays + 1) at 04:59:59.999 AM
  const monthEnd = new Date(year, monthNum - 1, totalDays + 1, 4, 59, 59, 999);

  // Calendar date range for expenses and platform sales
  const calendarStart = new Date(year, monthNum - 1, 1, 0, 0, 0, 0);
  const calendarEnd = new Date(year, monthNum, 0, 23, 59, 59, 999);

  // 2. Fetch all data in parallel
  const [orders, expenses, platformSales, settingsDoc] = await Promise.all([
    Order.find(buildOrderFilter(monthStart, monthEnd)),
    Expense.find({ date: { $gte: calendarStart, $lte: calendarEnd } }).populate('createdBy', 'name'),
    PlatformSale.find({ date: { $gte: calendarStart, $lte: calendarEnd } }).populate('createdBy', 'name'),
    Setting.findOne({ key: 'financial_settings' }),
  ]);

  const settings = settingsDoc ? settingsDoc.value : DEFAULT_FINANCIAL_SETTINGS;

  // 3. Aggregate daily POS sales by business date
  const dailySalesMap = {};
  for (const o of orders) {
    const bDate = getBusinessDate(o.createdAt);
    dailySalesMap[bDate] = (dailySalesMap[bDate] || 0) + o.netTotal;
  }

  // 4. Aggregate daily expenses (excluding salaries) and separate salary expenses
  const dailyExpensesMap = {};
  const salaryExpenses = [];

  for (const e of expenses) {
    if (e.category === 'Salary') {
      salaryExpenses.push({
        _id: e._id,
        title: e.title,
        amount: e.amount,
        date: e.date,
        note: e.note,
        addedBy: e.createdBy?.name || '',
      });
    } else {
      const dayNum = new Date(e.date).getDate();
      const bDate = `${year}-${String(monthNum).padStart(2, '0')}-${String(dayNum).padStart(2, '0')}`;
      dailyExpensesMap[bDate] = (dailyExpensesMap[bDate] || 0) + e.amount;
    }
  }

  // 5. Build daily rows with running cumulative totals
  let runningSale = 0;
  let runningProfit = 0;

  const rows = [];
  for (let d = 1; d <= totalDays; d++) {
    const dayPadded = String(d).padStart(2, '0');
    const monthPadded = String(monthNum).padStart(2, '0');
    const dateKey = `${year}-${monthPadded}-${dayPadded}`;
    const displayDate = `${dayPadded}/${monthPadded}`;

    const exp = dailyExpensesMap[dateKey] || 0;
    const sale = dailySalesMap[dateKey] || 0;
    const profitLoss = sale - exp;

    runningSale += sale;
    runningProfit += profitLoss;

    rows.push({
      day: d,
      date: displayDate,
      fullDate: dateKey,
      expenses: exp,
      sale,
      profitLoss,
      totalSale: runningSale,
      totalProfit: runningProfit,
    });
  }

  // 6. Platform Sales (e.g. Foodpanda)
  const foodpandaTotal = platformSales.reduce((s, p) => s + p.amount, 0);

  // 7. Subtotal (S. Total)
  const totalDailyExpenses = rows.reduce((s, r) => s + r.expenses, 0);
  const totalDailySales = rows.reduce((s, r) => s + r.sale, 0);
  const subtotalSale = totalDailySales + foodpandaTotal;
  const subtotalExpenses = totalDailyExpenses;
  const subtotalProfit = subtotalSale - subtotalExpenses;

  // 8. Salaries Total
  const salariesTotal = salaryExpenses.reduce((s, e) => s + e.amount, 0);

  // 9. Grand Total (G. Total)
  const grandTotalExpenses = subtotalExpenses + salariesTotal;
  const grandTotalSale = subtotalSale;
  const netProfit = grandTotalSale - grandTotalExpenses;

  // 10. Profit Sharing Calculations
  const chefSharePercent = Number(settings.chefSharePercent) || 30;
  const directorsSharePercent = Number(settings.directorsSharePercent) || 70;
  const directorCount = Number(settings.directorCount) || 3;

  const chefShare = Math.round((netProfit * chefSharePercent) / 100);
  const directorsShare = netProfit - chefShare;
  const eachDirectorShare = directorCount > 0 ? Math.round(directorsShare / directorCount) : 0;

  res.json({
    month: monthParam,
    year,
    monthNum,
    monthName: new Date(year, monthNum - 1, 1).toLocaleString('en-US', { month: 'long', year: 'numeric' }),
    rows,
    platformSales: {
      platform: 'Foodpanda',
      entries: platformSales,
      total: foodpandaTotal,
    },
    subtotal: {
      expenses: subtotalExpenses,
      sale: subtotalSale,
      profit: subtotalProfit,
    },
    salaries: {
      entries: salaryExpenses,
      total: salariesTotal,
    },
    grandTotal: {
      expenses: grandTotalExpenses,
      sale: grandTotalSale,
      netProfit,
    },
    profitDistribution: {
      chefSharePercent,
      directorsSharePercent,
      directorCount,
      chefShare,
      directorsShare,
      eachDirectorShare,
      directorAdjustments: settings.directorAdjustments || [],
    },
  });
};

// ── CSV Export ────────────────────────────────────────────────

// GET /api/reports/export/csv?type=orders|expenses|monthly-sheet&from=...&to=...&month=...
exports.exportCSV = async (req, res) => {
  const { type = 'orders', from, to, month } = req.query;

  if (type === 'monthly-sheet') {
    const monthParam = month || getBusinessDate().substring(0, 7);
    const [yStr, mStr] = monthParam.split('-');
    const year = Number(yStr);
    const monthNum = Number(mStr);
    const totalDays = new Date(year, monthNum, 0).getDate();

    const monthStart = new Date(year, monthNum - 1, 1, 5, 0, 0, 0);
    const monthEnd = new Date(year, monthNum - 1, totalDays + 1, 4, 59, 59, 999);
    const calendarStart = new Date(year, monthNum - 1, 1, 0, 0, 0, 0);
    const calendarEnd = new Date(year, monthNum, 0, 23, 59, 59, 999);

    const [orders, expenses, platformSales, settingsDoc] = await Promise.all([
      Order.find(buildOrderFilter(monthStart, monthEnd)),
      Expense.find({ date: { $gte: calendarStart, $lte: calendarEnd } }),
      PlatformSale.find({ date: { $gte: calendarStart, $lte: calendarEnd } }),
      Setting.findOne({ key: 'financial_settings' }),
    ]);

    const settings = settingsDoc ? settingsDoc.value : DEFAULT_FINANCIAL_SETTINGS;
    const dailySalesMap = {};
    for (const o of orders) {
      const bDate = getBusinessDate(o.createdAt);
      dailySalesMap[bDate] = (dailySalesMap[bDate] || 0) + o.netTotal;
    }

    const dailyExpensesMap = {};
    let salariesTotal = 0;
    for (const e of expenses) {
      if (e.category === 'Salary') {
        salariesTotal += e.amount;
      } else {
        const dayNum = new Date(e.date).getDate();
        const bDate = `${year}-${String(monthNum).padStart(2, '0')}-${String(dayNum).padStart(2, '0')}`;
        dailyExpensesMap[bDate] = (dailyExpensesMap[bDate] || 0) + e.amount;
      }
    }

    const fpTotal = platformSales.reduce((s, p) => s + p.amount, 0);

    const rows = [
      [`Monthly Report - ${monthParam}`],
      ['Date', 'Expenses', 'Sale', 'Profit/Loss', 'Total Sale', 'Total Profit'],
    ];

    let rSale = 0;
    let rProf = 0;
    let totExp = 0;
    let totSale = 0;

    for (let d = 1; d <= totalDays; d++) {
      const dayP = String(d).padStart(2, '0');
      const monP = String(monthNum).padStart(2, '0');
      const dKey = `${year}-${monP}-${dayP}`;
      const exp = dailyExpensesMap[dKey] || 0;
      const sale = dailySalesMap[dKey] || 0;
      const pl = sale - exp;
      rSale += sale;
      rProf += pl;
      totExp += exp;
      totSale += sale;
      rows.push([`${dayP}/${monP}`, exp, sale, pl, rSale, rProf]);
    }

    // Platform Sales
    rows.push(['Foodpanda', '', fpTotal, fpTotal, totSale + fpTotal, rProf + fpTotal]);
    // Subtotal
    rows.push(['S. Total', totExp, totSale + fpTotal, (totSale + fpTotal) - totExp, '', '']);
    // Salaries
    rows.push(['Salaries', salariesTotal, '', '', '', '']);
    // Grand Total
    const gNet = (totSale + fpTotal) - (totExp + salariesTotal);
    rows.push(['G. Total', totExp + salariesTotal, totSale + fpTotal, gNet, '', '']);

    const chefShare = Math.round((gNet * (settings.chefSharePercent || 30)) / 100);
    const dirShare = gNet - chefShare;
    const eachDir = Math.round(dirShare / (settings.directorCount || 3));

    rows.push([]);
    rows.push(['Share of Chef (30%)', chefShare]);
    rows.push(['Directors Share (70%)', dirShare]);
    rows.push(['Each Director Share', eachDir]);

    res.setHeader('Content-Type', 'text/csv');
    res.setHeader('Content-Disposition', `attachment; filename="monthly_sheet_${monthParam}.csv"`);
    return res.send(rows.map((r) => r.join(',')).join('\n'));
  }

  const { from: fromDate, to: toDate } = getBusinessDateRange(from, to);

  if (type === 'orders') {
    const orders = await Order.find(buildOrderFilter(fromDate, toDate)).populate('cashier', 'name');
    const rows = [
      ['Business Date', 'Created At', 'Order Type', 'Items', 'Subtotal', 'Discount', 'Delivery Fee', 'Net Total', 'Cash Received', 'Change', 'Status', 'Cashier'],
      ...orders.map((o) => [
        getBusinessDate(o.createdAt),
        o.createdAt.toISOString(),
        o.orderType,
        o.items.map((i) => `${i.menuItemName} (${i.variantLabel}) x${i.qty}`).join(' | '),
        o.subtotal,
        o.discount.amount,
        o.deliveryFee || 0,
        o.netTotal,
        o.cashReceived,
        o.change,
        o.status,
        o.cashier?.name || '',
      ]),
    ];
    res.setHeader('Content-Type', 'text/csv');
    res.setHeader('Content-Disposition', `attachment; filename="orders_${from}_${to}.csv"`);
    return res.send(rows.map((r) => r.join(',')).join('\n'));
  }

  if (type === 'expenses') {
    const [fy, fm, fd] = from ? from.split('-').map(Number) : [1970, 1, 1];
    const [ty, tm, td] = to ? to.split('-').map(Number) : [new Date().getFullYear(), new Date().getMonth() + 1, new Date().getDate()];
    const expStart = new Date(fy, fm - 1, fd, 0, 0, 0, 0);
    const expEnd = new Date(ty, tm - 1, td, 23, 59, 59, 999);

    const expenses = await Expense.find({ date: { $gte: expStart, $lte: expEnd } }).populate('createdBy', 'name');
    const rows = [
      ['Date', 'Title', 'Category', 'Amount', 'Note', 'Created By'],
      ...expenses.map((e) => [e.date.toISOString().split('T')[0], e.title, e.category, e.amount, e.note, e.createdBy?.name || '']),
    ];
    res.setHeader('Content-Type', 'text/csv');
    res.setHeader('Content-Disposition', `attachment; filename="expenses_${from}_${to}.csv"`);
    return res.send(rows.map((r) => r.join(',')).join('\n'));
  }

  res.status(400).json({ message: 'type must be orders, expenses, or monthly-sheet' });
};