Spaces:

aliroohan179
/

face

Running

face / src /controllers /reportController.js

feat: implement business date utility and financial reporting system including expense tracking, platform sales, and profit-loss analysis endpoints

ec62259 3 months ago

Raw History Blame Contribute Delete

17.1 kB

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' });
};

	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' });
	};