const Order = require('../models/orderModel');
const Transaction = require('../models/transactionModel');
const Provider = require('../models/providerModel');
const Product = require('../models/productModel');
const Expense = require('../models/expenseModel');
const Capital = require('../models/capitalModel');
const {
  CapitalAccount,
  CapitalTransfer,
} = require('../models/capitalAccountModel');
const User = require('../models/userModel');

// ─────────────────────────────────────────────
// HELPER: Get only external (non-platform) providers
// Excludes the platform owner (Yadawy) by checking:
//   1. Provider's linked user has role admin/superadmin
//   2. Provider has no linked user but storeName matches platform name
// ─────────────────────────────────────────────
const PLATFORM_STORE_NAMES = ['samoulla', 'سامويلا'];

const getExternalProviders = async () => {
  // Find user IDs that are admin or superadmin
  const adminUsers = await User.find(
    { role: { $in: ['admin', 'superadmin'] } },
    '_id',
  );
  const adminUserIds = adminUsers.map((u) => u._id.toString());

  // Fetch all providers with their user populated
  const allProviders = await Provider.find({}).populate('user', 'role');

  // Filter out platform-owned providers
  return allProviders.filter((provider) => {
    // Exclude if linked user is admin/superadmin
    if (provider.user && adminUserIds.includes(provider.user._id.toString())) {
      return false;
    }
    // Exclude if storeName matches platform names (case-insensitive)
    if (
      PLATFORM_STORE_NAMES.includes(
        (provider.storeName || '').toLowerCase().trim(),
      )
    ) {
      return false;
    }
    return true;
  });
};

// ─────────────────────────────────────────────
// HELPER: Calculate financials with optional date range
// ─────────────────────────────────────────────
const calculateFinancials = async (startDate, endDate) => {
  const query = { orderStatus: 'completed' };

  if (startDate || endDate) {
    query.createdAt = {};
    if (startDate) query.createdAt.$gte = new Date(startDate);
    if (endDate) query.createdAt.$lte = new Date(endDate);
  }

  const orders = await Order.find(query).populate({
    path: 'items.product',
    populate: {
      path: 'provider',
      populate: { path: 'user' },
    },
  });

  let totalWebsiteProfit = 0;
  let websiteOwnedProfit = 0;
  let vendorOwnedProfit = 0;
  let totalRevenue = 0;

  const vendorMap = {};

  for (const order of orders) {
    for (const item of order.items) {
      const product = item.product;
      if (!product || !product.provider) continue;

      const quantity = item.quantity;
      const sellingPrice = item.unitPrice;
      const costPrice = product.costPrice || 0;
      const purchasePrice = product.purchasePrice || 0;

      const itemRevenue = sellingPrice * quantity;
      const itemWebsiteProfit = (sellingPrice - costPrice) * quantity;

      totalRevenue += itemRevenue;

      const providerUser = product.provider.user;
      const providerStoreName = (product.provider.storeName || '')
        .toLowerCase()
        .trim();
      let isWebsiteOwned = false;

      if (
        (providerUser &&
          (providerUser.role === 'admin' ||
            providerUser.role === 'superadmin')) ||
        // If provider is Samoulla or provider ID matches platform IDs
        PLATFORM_STORE_NAMES.includes(providerStoreName)
      ) {
        isWebsiteOwned = true;
      }

      totalWebsiteProfit += itemWebsiteProfit;

      if (isWebsiteOwned) {
        websiteOwnedProfit += itemWebsiteProfit;
      } else {
        vendorOwnedProfit += itemWebsiteProfit;

        const pid = product.provider._id.toString();

        if (!vendorMap[pid]) {
          vendorMap[pid] = {
            totalDeliveredSales: 0,
            totalRevenueOwed: 0,
            totalPurchaseCost: 0,
          };
        }

        vendorMap[pid].totalDeliveredSales += sellingPrice * quantity;
        vendorMap[pid].totalRevenueOwed += costPrice * quantity;
        vendorMap[pid].totalPurchaseCost += purchasePrice * quantity;
      }
    }
  }

  return {
    totalWebsiteProfit,
    websiteOwnedProfit,
    vendorOwnedProfit,
    totalRevenue,
    vendorMap,
  };
};

// ═════════════════════════════════════════════
//  ADMIN ENDPOINTS
// ═════════════════════════════════════════════

// ─────────────────────────────────────────────
// GET /finance/admin/stats
// Admin Financial Overview + Vendors Table
// Supports ?startDate=...&endDate=...
// ─────────────────────────────────────────────
exports.getAdminFinancialStats = async (req, res) => {
  try {
    const { startDate, endDate } = req.query;

    const {
      totalWebsiteProfit,
      websiteOwnedProfit,
      vendorOwnedProfit,
      totalRevenue,
      vendorMap,
    } = await calculateFinancials(startDate, endDate);

    const allProviders = await getExternalProviders();

    // Get payouts (optionally filtered by date)
    const payoutQuery = { type: 'payout' };
    if (startDate || endDate) {
      payoutQuery.date = {};
      if (startDate) payoutQuery.date.$gte = new Date(startDate);
      if (endDate) payoutQuery.date.$lte = new Date(endDate);
    }
    const payouts = await Transaction.find(payoutQuery);
    const payoutMap = {};
    payouts.forEach((t) => {
      const pid = t.provider.toString();
      payoutMap[pid] = (payoutMap[pid] || 0) + t.amount;
    });

    const vendorSummary = [];

    for (const provider of allProviders) {
      const pid = provider._id.toString();
      const stats = vendorMap[pid] || {
        totalDeliveredSales: 0,
        totalRevenueOwed: 0,
        totalPurchaseCost: 0,
      };
      const paidAmount = payoutMap[pid] || 0;

      const vendorProfit = stats.totalRevenueOwed - stats.totalPurchaseCost;
      const platformCommission =
        stats.totalDeliveredSales - stats.totalRevenueOwed;

      vendorSummary.push({
        provider: {
          _id: provider._id,
          name: provider.name,
          storeName: provider.storeName,
        },
        totalSales: stats.totalDeliveredSales,
        vendorProfit,
        platformCommission,
        totalOwed: stats.totalRevenueOwed,
        paidAmount,
        remainingBalance: stats.totalRevenueOwed - paidAmount,
      });
    }

    // Get expenses total for the period
    const expenseQuery = {};
    if (startDate || endDate) {
      expenseQuery.date = {};
      if (startDate) expenseQuery.date.$gte = new Date(startDate);
      if (endDate) expenseQuery.date.$lte = new Date(endDate);
    }
    const expenses = await Expense.find(expenseQuery);
    const totalExpenses = expenses.reduce((sum, e) => sum + e.amount, 0);

    // Get capital info
    const capital = await Capital.getCapital();

    res.status(200).json({
      status: 'success',
      data: {
        totalRevenue,
        totalWebsiteProfit,
        profitSplit: {
          websiteOwned: websiteOwnedProfit,
          vendorOwned: vendorOwnedProfit,
        },
        totalExpenses,
        netProfit: totalWebsiteProfit - totalExpenses,
        capital: {
          initial: capital.initialCapital,
          current: capital.currentCapital,
        },
        vendorSummary,
      },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// ─────────────────────────────────────────────
// POST /finance/admin/payout
// Create a payout and log to capital
// ─────────────────────────────────────────────
exports.createPayout = async (req, res) => {
  try {
    const { providerId, amount, note, paymentMethod, referenceId } = req.body;

    if (!amount || amount <= 0) {
      return res
        .status(400)
        .json({ status: 'fail', message: 'Amount must be positive' });
    }

    const provider = await Provider.findById(providerId);
    if (!provider) {
      return res
        .status(404)
        .json({ status: 'fail', message: 'Provider not found' });
    }

    const transaction = await Transaction.create({
      provider: providerId,
      admin: req.user._id,
      amount,
      type: 'payout',
      note,
      paymentMethod,
      referenceId,
    });

    // Decrease capital
    const capital = await Capital.getCapital();
    capital.currentCapital -= amount;
    capital.logs.push({
      type: 'vendor_payout',
      amount: -amount,
      balanceAfter: capital.currentCapital,
      reference: transaction._id,
      referenceModel: 'Transaction',
      description:
        `Payout to ${provider.storeName || provider.name}: ${note || ''}`.trim(),
      date: new Date(),
      createdBy: req.user._id,
    });
    await capital.save();

    res.status(201).json({
      status: 'success',
      data: {
        transaction,
        capitalAfter: capital.currentCapital,
      },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// ═════════════════════════════════════════════
//  CAPITAL MANAGEMENT
// ═════════════════════════════════════════════

// GET /finance/admin/capital
exports.getCapital = async (req, res) => {
  try {
    const capital = await Capital.getCapital();
    res.status(200).json({
      status: 'success',
      data: {
        initialCapital: capital.initialCapital,
        currentCapital: capital.currentCapital,
        logsCount: capital.logs.length,
      },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// PUT /finance/admin/capital
// Set/update initial capital
exports.setCapital = async (req, res) => {
  try {
    const { initialCapital } = req.body;

    if (initialCapital === undefined || initialCapital < 0) {
      return res.status(400).json({
        status: 'fail',
        message: 'initialCapital must be a non-negative number',
      });
    }

    const capital = await Capital.getCapital();
    const diff = initialCapital - capital.initialCapital;

    capital.initialCapital = initialCapital;
    capital.currentCapital += diff;

    capital.logs.push({
      type: 'initial',
      amount: diff,
      balanceAfter: capital.currentCapital,
      description: `Initial capital set to ${initialCapital}`,
      date: new Date(),
      createdBy: req.user._id,
    });

    await capital.save();

    res.status(200).json({
      status: 'success',
      data: {
        initialCapital: capital.initialCapital,
        currentCapital: capital.currentCapital,
      },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// POST /finance/admin/capital/adjust
// Manual adjustment (add/subtract) to capital
exports.adjustCapital = async (req, res) => {
  try {
    const { amount, description } = req.body;

    if (amount === undefined || amount === 0) {
      return res
        .status(400)
        .json({ status: 'fail', message: 'Amount must be a non-zero number' });
    }

    const capital = await Capital.getCapital();
    capital.currentCapital += amount;

    capital.logs.push({
      type: 'adjustment',
      amount,
      balanceAfter: capital.currentCapital,
      description:
        description || `Manual adjustment: ${amount > 0 ? '+' : ''}${amount}`,
      date: new Date(),
      createdBy: req.user._id,
    });

    await capital.save();

    res.status(200).json({
      status: 'success',
      data: {
        currentCapital: capital.currentCapital,
      },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// GET /finance/admin/capital/timeline
// Returns capital log entries for chart display
exports.getCapitalTimeline = async (req, res) => {
  try {
    const { startDate, endDate, limit } = req.query;
    const capital = await Capital.getCapital();

    let logs = capital.logs || [];

    // Filter by date
    if (startDate) {
      const sd = new Date(startDate);
      logs = logs.filter((l) => l.date >= sd);
    }
    if (endDate) {
      const ed = new Date(endDate);
      logs = logs.filter((l) => l.date <= ed);
    }

    // Sort by date ascending for timeline
    logs.sort((a, b) => a.date - b.date);

    // Limit
    if (limit) {
      logs = logs.slice(-parseInt(limit, 10));
    }

    res.status(200).json({
      status: 'success',
      results: logs.length,
      data: { logs },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// ═════════════════════════════════════════════
//  EXPENSE MANAGEMENT
// ═════════════════════════════════════════════

// GET /finance/admin/expenses
exports.getExpenses = async (req, res) => {
  try {
    const { startDate, endDate, category, page = 1, limit = 50 } = req.query;
    const query = {};

    if (startDate || endDate) {
      query.date = {};
      if (startDate) query.date.$gte = new Date(startDate);
      if (endDate) query.date.$lte = new Date(endDate);
    }
    if (category) query.category = category;

    const skip = (parseInt(page, 10) - 1) * parseInt(limit, 10);

    const [expenses, total] = await Promise.all([
      Expense.find(query)
        .sort('-date')
        .skip(skip)
        .limit(parseInt(limit, 10))
        .populate('createdBy', 'name email'),
      Expense.countDocuments(query),
    ]);

    const totalAmount = await Expense.aggregate([
      { $match: query },
      { $group: { _id: null, total: { $sum: '$amount' } } },
    ]);

    // Category breakdown
    const categoryBreakdown = await Expense.aggregate([
      { $match: query },
      {
        $group: {
          _id: '$category',
          total: { $sum: '$amount' },
          count: { $sum: 1 },
        },
      },
      { $sort: { total: -1 } },
    ]);

    res.status(200).json({
      status: 'success',
      results: expenses.length,
      totalRecords: total,
      data: {
        expenses,
        totalExpenses: (totalAmount[0] && totalAmount[0].total) || 0,
        categoryBreakdown,
      },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// POST /finance/admin/expenses
exports.createExpense = async (req, res) => {
  try {
    const { category, amount, date, notes } = req.body;

    if (!category || !amount) {
      return res
        .status(400)
        .json({ status: 'fail', message: 'category and amount are required' });
    }

    const expense = await Expense.create({
      category,
      amount,
      date: date || new Date(),
      notes: notes || '',
      createdBy: req.user._id,
    });

    // Decrease capital
    const capital = await Capital.getCapital();
    capital.currentCapital -= amount;
    capital.logs.push({
      type: 'expense',
      amount: -amount,
      balanceAfter: capital.currentCapital,
      reference: expense._id,
      referenceModel: 'Expense',
      description: `Expense (${category}): ${notes || ''}`.trim(),
      date: expense.date,
      createdBy: req.user._id,
    });
    await capital.save();

    res.status(201).json({
      status: 'success',
      data: {
        expense,
        capitalAfter: capital.currentCapital,
      },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// PUT /finance/admin/expenses/:id
exports.updateExpense = async (req, res) => {
  try {
    const expense = await Expense.findById(req.params.id);
    if (!expense) {
      return res
        .status(404)
        .json({ status: 'fail', message: 'Expense not found' });
    }

    const oldAmount = expense.amount;

    // Update fields
    if (req.body.category) expense.category = req.body.category;
    if (req.body.amount !== undefined) expense.amount = req.body.amount;
    if (req.body.date) expense.date = req.body.date;
    if (req.body.notes !== undefined) expense.notes = req.body.notes;

    await expense.save();

    // Adjust capital for difference
    const diff = oldAmount - expense.amount; // positive if expense decreased
    if (diff !== 0) {
      const capital = await Capital.getCapital();
      capital.currentCapital += diff;
      capital.logs.push({
        type: 'adjustment',
        amount: diff,
        balanceAfter: capital.currentCapital,
        reference: expense._id,
        referenceModel: 'Expense',
        description: `Expense updated (${expense.category}): ${oldAmount} → ${expense.amount}`,
        date: new Date(),
        createdBy: req.user._id,
      });
      await capital.save();
    }

    res.status(200).json({
      status: 'success',
      data: { expense },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// DELETE /finance/admin/expenses/:id
exports.deleteExpense = async (req, res) => {
  try {
    const expense = await Expense.findById(req.params.id);
    if (!expense) {
      return res
        .status(404)
        .json({ status: 'fail', message: 'Expense not found' });
    }

    // Restore capital
    const capital = await Capital.getCapital();
    capital.currentCapital += expense.amount;
    capital.logs.push({
      type: 'adjustment',
      amount: expense.amount,
      balanceAfter: capital.currentCapital,
      description: `Expense deleted (${expense.category}): +${expense.amount} restored`,
      date: new Date(),
      createdBy: req.user._id,
    });
    await capital.save();

    await Expense.findByIdAndDelete(req.params.id);

    res.status(200).json({
      status: 'success',
      message: 'Expense deleted and capital restored',
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// ═════════════════════════════════════════════
//  ANALYTICS (Admin)
// ═════════════════════════════════════════════

// GET /finance/admin/analytics/monthly-profit
// Returns monthly profit data for charts
exports.getMonthlyProfit = async (req, res) => {
  try {
    const { year } = req.query;
    const targetYear = parseInt(year, 10) || new Date().getFullYear();

    const startOfYear = new Date(targetYear, 0, 1);
    const endOfYear = new Date(targetYear, 11, 31, 23, 59, 59);

    // Aggregate completed orders by month
    const monthlyData = await Order.aggregate([
      {
        $match: {
          orderStatus: 'completed',
          createdAt: { $gte: startOfYear, $lte: endOfYear },
        },
      },
      { $unwind: '$items' },
      {
        $lookup: {
          from: 'products',
          localField: 'items.product',
          foreignField: '_id',
          as: 'productInfo',
        },
      },
      { $unwind: { path: '$productInfo', preserveNullAndEmptyArrays: true } },
      {
        $group: {
          _id: { $month: '$createdAt' },
          totalRevenue: {
            $sum: { $multiply: ['$items.unitPrice', '$items.quantity'] },
          },
          totalCost: {
            $sum: {
              $multiply: [
                { $ifNull: ['$productInfo.costPrice', 0] },
                '$items.quantity',
              ],
            },
          },
          orderCount: { $sum: 1 },
        },
      },
      { $sort: { _id: 1 } },
    ]);

    // Get monthly expenses
    const monthlyExpenses = await Expense.aggregate([
      {
        $match: {
          date: { $gte: startOfYear, $lte: endOfYear },
        },
      },
      {
        $group: {
          _id: { $month: '$date' },
          totalExpenses: { $sum: '$amount' },
        },
      },
      { $sort: { _id: 1 } },
    ]);

    const expenseMap = {};
    monthlyExpenses.forEach((e) => {
      expenseMap[e._id] = e.totalExpenses;
    });

    // Build full 12-month response
    const months = [];
    for (let m = 1; m <= 12; m++) {
      const data = monthlyData.find((d) => d._id === m);
      const revenue = data ? data.totalRevenue : 0;
      const cost = data ? data.totalCost : 0;
      const profit = revenue - cost;
      const expenses = expenseMap[m] || 0;

      months.push({
        month: m,
        revenue,
        profit,
        expenses,
        netProfit: profit - expenses,
        orderCount: data ? data.orderCount : 0,
      });
    }

    res.status(200).json({
      status: 'success',
      data: { year: targetYear, months },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// GET /finance/admin/analytics/vendor-comparison
exports.getVendorComparison = async (req, res) => {
  try {
    const { startDate, endDate } = req.query;
    const { vendorMap } = await calculateFinancials(startDate, endDate);

    const allProviders = await getExternalProviders();

    const comparison = [];
    for (const provider of allProviders) {
      const pid = provider._id.toString();
      const stats = vendorMap[pid] || {
        totalDeliveredSales: 0,
        totalRevenueOwed: 0,
        totalPurchaseCost: 0,
      };
      const platformCommission =
        stats.totalDeliveredSales - stats.totalRevenueOwed;
      const vendorProfit = stats.totalRevenueOwed - stats.totalPurchaseCost;

      comparison.push({
        provider: {
          _id: provider._id,
          name: provider.name,
          storeName: provider.storeName,
        },
        totalSales: stats.totalDeliveredSales,
        vendorProfit,
        platformCommission,
      });
    }

    // Sort by total sales descending
    comparison.sort((a, b) => b.totalSales - a.totalSales);

    res.status(200).json({
      status: 'success',
      data: { comparison },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// GET /finance/admin/analytics/revenue-vs-expenses
exports.getRevenueVsExpenses = async (req, res) => {
  try {
    const { year } = req.query;
    const targetYear = parseInt(year, 10) || new Date().getFullYear();

    const startOfYear = new Date(targetYear, 0, 1);
    const endOfYear = new Date(targetYear, 11, 31, 23, 59, 59);

    // Monthly revenue from completed orders
    const monthlyRevenue = await Order.aggregate([
      {
        $match: {
          orderStatus: 'completed',
          createdAt: { $gte: startOfYear, $lte: endOfYear },
        },
      },
      {
        $group: {
          _id: { $month: '$createdAt' },
          revenue: { $sum: '$totalPrice' },
        },
      },
      { $sort: { _id: 1 } },
    ]);

    // Monthly expenses
    const monthlyExpenses = await Expense.aggregate([
      {
        $match: {
          date: { $gte: startOfYear, $lte: endOfYear },
        },
      },
      {
        $group: {
          _id: { $month: '$date' },
          expenses: { $sum: '$amount' },
        },
      },
      { $sort: { _id: 1 } },
    ]);

    // Monthly vendor payouts
    const monthlyPayouts = await Transaction.aggregate([
      {
        $match: {
          type: 'payout',
          date: { $gte: startOfYear, $lte: endOfYear },
        },
      },
      {
        $group: {
          _id: { $month: '$date' },
          payouts: { $sum: '$amount' },
        },
      },
      { $sort: { _id: 1 } },
    ]);

    const revenueMap = {};
    monthlyRevenue.forEach((r) => {
      revenueMap[r._id] = r.revenue;
    });
    const expenseMap = {};
    monthlyExpenses.forEach((e) => {
      expenseMap[e._id] = e.expenses;
    });
    const payoutMap = {};
    monthlyPayouts.forEach((p) => {
      payoutMap[p._id] = p.payouts;
    });

    const months = [];
    for (let m = 1; m <= 12; m++) {
      months.push({
        month: m,
        revenue: revenueMap[m] || 0,
        expenses: expenseMap[m] || 0,
        vendorPayouts: payoutMap[m] || 0,
        totalOutflow: (expenseMap[m] || 0) + (payoutMap[m] || 0),
      });
    }

    res.status(200).json({
      status: 'success',
      data: { year: targetYear, months },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// GET /finance/admin/summary
// Daily / Weekly / Monthly summary
exports.getFinancialSummary = async (req, res) => {
  try {
    const { period } = req.query; // 'daily', 'weekly', 'monthly'
    const now = new Date();
    let startDate;

    switch (period) {
      case 'daily':
        startDate = new Date(now.getFullYear(), now.getMonth(), now.getDate());
        break;
      case 'weekly':
        startDate = new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000);
        break;
      case 'monthly':
        startDate = new Date(now.getFullYear(), now.getMonth(), 1);
        break;
      default:
        startDate = new Date(now.getFullYear(), now.getMonth(), 1); // default monthly
    }

    const { totalWebsiteProfit, totalRevenue, vendorMap } =
      await calculateFinancials(startDate, now);

    // Expenses in period
    const expenses = await Expense.aggregate([
      { $match: { date: { $gte: startDate, $lte: now } } },
      { $group: { _id: null, total: { $sum: '$amount' } } },
    ]);
    const totalExpenses = (expenses[0] && expenses[0].total) || 0;

    // Payouts in period
    const payouts = await Transaction.aggregate([
      { $match: { type: 'payout', date: { $gte: startDate, $lte: now } } },
      { $group: { _id: null, total: { $sum: '$amount' } } },
    ]);
    const totalPayouts = (payouts[0] && payouts[0].total) || 0;

    // Order count
    const orderCount = await Order.countDocuments({
      orderStatus: 'completed',
      createdAt: { $gte: startDate, $lte: now },
    });

    res.status(200).json({
      status: 'success',
      data: {
        period: period || 'monthly',
        startDate,
        endDate: now,
        totalRevenue,
        totalProfit: totalWebsiteProfit,
        totalExpenses,
        totalPayouts,
        netProfit: totalWebsiteProfit - totalExpenses,
        orderCount,
      },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// GET /finance/admin/export
// Export financial report as Excel
exports.exportFinancialReport = async (req, res) => {
  try {
    const XLSX = require('xlsx');
    const { startDate, endDate } = req.query;

    const {
      totalWebsiteProfit,
      websiteOwnedProfit,
      vendorOwnedProfit,
      totalRevenue,
      vendorMap,
    } = await calculateFinancials(startDate, endDate);

    const allProviders = await getExternalProviders();

    // Build vendor summary data
    const payoutQuery = { type: 'payout' };
    if (startDate || endDate) {
      payoutQuery.date = {};
      if (startDate) payoutQuery.date.$gte = new Date(startDate);
      if (endDate) payoutQuery.date.$lte = new Date(endDate);
    }
    const payouts = await Transaction.find(payoutQuery);
    const payoutMap = {};
    payouts.forEach((t) => {
      const pid = t.provider.toString();
      payoutMap[pid] = (payoutMap[pid] || 0) + t.amount;
    });

    // Sheet 1: Overview
    const overviewData = [
      ['Financial Report'],
      ['Period', startDate || 'All Time', endDate || 'Present'],
      [],
      ['Total Revenue', totalRevenue],
      ['Total Platform Profit', totalWebsiteProfit],
      ['Website-Owned Profit', websiteOwnedProfit],
      ['Vendor-Owned Profit (Commission)', vendorOwnedProfit],
    ];

    // Sheet 2: Vendor Details
    const vendorHeaders = [
      'Vendor Name',
      'Store Name',
      'Total Sales',
      'Vendor Profit',
      'Platform Commission',
      'Total Owed',
      'Paid Amount',
      'Remaining Balance',
    ];
    const vendorRows = [vendorHeaders];

    for (const provider of allProviders) {
      const pid = provider._id.toString();
      const stats = vendorMap[pid] || {
        totalDeliveredSales: 0,
        totalRevenueOwed: 0,
        totalPurchaseCost: 0,
      };
      const paidAmount = payoutMap[pid] || 0;
      const vendorProfit = stats.totalRevenueOwed - stats.totalPurchaseCost;
      const platformCommission =
        stats.totalDeliveredSales - stats.totalRevenueOwed;

      vendorRows.push([
        provider.name,
        provider.storeName,
        stats.totalDeliveredSales,
        vendorProfit,
        platformCommission,
        stats.totalRevenueOwed,
        paidAmount,
        stats.totalRevenueOwed - paidAmount,
      ]);
    }

    // Sheet 3: Expenses
    const expenseQuery = {};
    if (startDate || endDate) {
      expenseQuery.date = {};
      if (startDate) expenseQuery.date.$gte = new Date(startDate);
      if (endDate) expenseQuery.date.$lte = new Date(endDate);
    }
    const expensesList = await Expense.find(expenseQuery)
      .sort('-date')
      .populate('createdBy', 'name');
    const expenseHeaders = [
      'Category',
      'Amount',
      'Date',
      'Notes',
      'Created By',
    ];
    const expenseRows = [expenseHeaders];
    for (const exp of expensesList) {
      expenseRows.push([
        exp.category,
        exp.amount,
        exp.date ? exp.date.toISOString().split('T')[0] : '',
        exp.notes,
        exp.createdBy ? exp.createdBy.name : '',
      ]);
    }

    // Build workbook
    const wb = XLSX.utils.book_new();
    const ws1 = XLSX.utils.aoa_to_sheet(overviewData);
    const ws2 = XLSX.utils.aoa_to_sheet(vendorRows);
    const ws3 = XLSX.utils.aoa_to_sheet(expenseRows);

    XLSX.utils.book_append_sheet(wb, ws1, 'Overview');
    XLSX.utils.book_append_sheet(wb, ws2, 'Vendors');
    XLSX.utils.book_append_sheet(wb, ws3, 'Expenses');

    const buffer = XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' });

    res.setHeader(
      'Content-Type',
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    );
    res.setHeader(
      'Content-Disposition',
      'attachment; filename=financial_report.xlsx',
    );
    res.send(buffer);
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// ═════════════════════════════════════════════
//  VENDOR ENDPOINTS
// ═════════════════════════════════════════════

// ─────────────────────────────────────────────
// GET /finance/vendor/stats
// Vendor Profit Overview
// ─────────────────────────────────────────────
exports.getVendorMyStats = async (req, res) => {
  try {
    if (!req.user.provider) {
      return res
        .status(400)
        .json({ status: 'fail', message: 'User is not linked to a provider' });
    }
    const myProviderId = req.user.provider.toString();
    const { startDate, endDate } = req.query;

    const { vendorMap } = await calculateFinancials(startDate, endDate);
    const myStats = vendorMap[myProviderId] || {
      totalDeliveredSales: 0,
      totalRevenueOwed: 0,
      totalPurchaseCost: 0,
    };

    const totalProfit = myStats.totalRevenueOwed - myStats.totalPurchaseCost;
    const platformCommission =
      myStats.totalDeliveredSales - myStats.totalRevenueOwed;

    // Get payouts
    const payoutQuery = { provider: myProviderId, type: 'payout' };
    if (startDate || endDate) {
      payoutQuery.date = {};
      if (startDate) payoutQuery.date.$gte = new Date(startDate);
      if (endDate) payoutQuery.date.$lte = new Date(endDate);
    }
    const payouts = await Transaction.find(payoutQuery).sort('-date');
    const paidAmount = payouts.reduce((sum, t) => sum + t.amount, 0);

    res.status(200).json({
      status: 'success',
      data: {
        totalSales: myStats.totalDeliveredSales,
        totalRevenueOwed: myStats.totalRevenueOwed,
        totalProfit,
        platformCommission,
        paidAmount,
        pendingAmount: myStats.totalRevenueOwed - paidAmount,
        remainingBalance: myStats.totalRevenueOwed - paidAmount,
        transactions: payouts,
      },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// ─────────────────────────────────────────────
// GET /finance/vendor/orders
// Vendor Orders Financial Breakdown
// ─────────────────────────────────────────────
exports.getVendorOrderBreakdown = async (req, res) => {
  try {
    if (!req.user.provider) {
      return res
        .status(400)
        .json({ status: 'fail', message: 'User is not linked to a provider' });
    }
    const myProviderId = req.user.provider.toString();
    const { startDate, endDate, page = 1, limit = 20 } = req.query;

    // Find completed orders that contain products from this vendor's provider
    const matchStage = { orderStatus: 'completed' };
    if (startDate || endDate) {
      matchStage.createdAt = {};
      if (startDate) matchStage.createdAt.$gte = new Date(startDate);
      if (endDate) matchStage.createdAt.$lte = new Date(endDate);
    }

    const orders = await Order.find(matchStage)
      .populate({
        path: 'items.product',
        populate: { path: 'provider' },
      })
      .sort('-createdAt');

    // Filter and compute per-order financials for THIS vendor
    const orderBreakdown = [];

    for (const order of orders) {
      let orderVendorSales = 0;
      let orderVendorCost = 0;
      let orderVendorProfit = 0;
      let orderCommission = 0;
      let hasVendorItems = false;

      const vendorItems = [];

      for (const item of order.items) {
        if (!item.product || !item.product.provider) continue;
        if (item.product.provider._id.toString() !== myProviderId) continue;

        hasVendorItems = true;
        const qty = item.quantity;
        const sellingPrice = item.unitPrice;
        const costPrice = item.product.costPrice || 0;
        const purchasePrice = item.product.purchasePrice || 0;

        const itemSales = sellingPrice * qty;
        const itemOwed = costPrice * qty;
        const itemProfit = (costPrice - purchasePrice) * qty;
        const itemCommission = (sellingPrice - costPrice) * qty;

        orderVendorSales += itemSales;
        orderVendorCost += itemOwed;
        orderVendorProfit += itemProfit;
        orderCommission += itemCommission;

        vendorItems.push({
          productId: item.product._id,
          productName: item.product.nameAr || item.product.nameEn,
          quantity: qty,
          sellingPrice,
          costPrice,
          profitPerUnit: costPrice - purchasePrice,
          totalProfit: itemProfit,
          commission: itemCommission,
        });
      }

      if (hasVendorItems) {
        orderBreakdown.push({
          orderId: order._id,
          orderDate: order.createdAt,
          totalSales: orderVendorSales,
          vendorRevenue: orderVendorCost,
          vendorProfit: orderVendorProfit,
          platformCommission: orderCommission,
          items: vendorItems,
        });
      }
    }

    // Paginate
    const skip = (parseInt(page, 10) - 1) * parseInt(limit, 10);
    const paginatedOrders = orderBreakdown.slice(
      skip,
      skip + parseInt(limit, 10),
    );

    res.status(200).json({
      status: 'success',
      results: paginatedOrders.length,
      totalRecords: orderBreakdown.length,
      data: { orders: paginatedOrders },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// ─────────────────────────────────────────────
// GET /finance/vendor/payments
// Vendor Payment History
// ─────────────────────────────────────────────
exports.getVendorPaymentHistory = async (req, res) => {
  try {
    if (!req.user.provider) {
      return res
        .status(400)
        .json({ status: 'fail', message: 'User is not linked to a provider' });
    }
    const myProviderId = req.user.provider.toString();
    const { startDate, endDate, page = 1, limit = 20 } = req.query;

    const query = { provider: myProviderId, type: 'payout' };
    if (startDate || endDate) {
      query.date = {};
      if (startDate) query.date.$gte = new Date(startDate);
      if (endDate) query.date.$lte = new Date(endDate);
    }

    const skip = (parseInt(page, 10) - 1) * parseInt(limit, 10);

    const [payments, total] = await Promise.all([
      Transaction.find(query)
        .sort('-date')
        .skip(skip)
        .limit(parseInt(limit, 10))
        .populate('admin', 'name email'),
      Transaction.countDocuments(query),
    ]);

    const totalPaid = await Transaction.aggregate([
      { $match: query },
      { $group: { _id: null, total: { $sum: '$amount' } } },
    ]);

    res.status(200).json({
      status: 'success',
      results: payments.length,
      totalRecords: total,
      data: {
        payments,
        totalPaid: (totalPaid[0] && totalPaid[0].total) || 0,
      },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// ─────────────────────────────────────────────
// GET /finance/vendor/analytics
// Vendor profit analytics (daily/monthly)
// ─────────────────────────────────────────────
exports.getVendorAnalytics = async (req, res) => {
  try {
    if (!req.user.provider) {
      return res
        .status(400)
        .json({ status: 'fail', message: 'User is not linked to a provider' });
    }
    const myProviderId = req.user.provider.toString();
    const { year, granularity } = req.query; // granularity: 'daily' or 'monthly'
    const targetYear = parseInt(year, 10) || new Date().getFullYear();

    const startOfYear = new Date(targetYear, 0, 1);
    const endOfYear = new Date(targetYear, 11, 31, 23, 59, 59);

    // Get completed orders within the year
    const orders = await Order.find({
      orderStatus: 'completed',
      createdAt: { $gte: startOfYear, $lte: endOfYear },
    }).populate({
      path: 'items.product',
      populate: { path: 'provider' },
    });

    // Group by time period
    const dataMap = {};

    for (const order of orders) {
      for (const item of order.items) {
        if (!item.product || !item.product.provider) continue;
        if (item.product.provider._id.toString() !== myProviderId) continue;

        const date = new Date(order.createdAt);
        let key;

        if (granularity === 'daily') {
          key = date.toISOString().split('T')[0]; // YYYY-MM-DD
        } else {
          key = `${date.getFullYear()}-${String(date.getMonth() + 1).padStart(2, '0')}`; // YYYY-MM
        }

        if (!dataMap[key]) {
          dataMap[key] = { sales: 0, revenue: 0, profit: 0, orders: new Set() };
        }

        const qty = item.quantity;
        const sellingPrice = item.unitPrice;
        const costPrice = item.product.costPrice || 0;
        const purchasePrice = item.product.purchasePrice || 0;

        dataMap[key].sales += sellingPrice * qty;
        dataMap[key].revenue += costPrice * qty;
        dataMap[key].profit += (costPrice - purchasePrice) * qty;
        dataMap[key].orders.add(order._id.toString());
      }
    }

    // Convert to array
    const analytics = Object.entries(dataMap)
      .map(([period, data]) => ({
        period,
        sales: data.sales,
        revenue: data.revenue,
        profit: data.profit,
        orderCount: data.orders.size,
      }))
      .sort((a, b) => a.period.localeCompare(b.period));

    res.status(200).json({
      status: 'success',
      data: {
        year: targetYear,
        granularity: granularity || 'monthly',
        analytics,
      },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// ═════════════════════════════════════════════
//  CAPITAL ACCOUNTS (Where is the money?)
// ═════════════════════════════════════════════

// GET /finance/admin/accounts
// List all capital accounts with balances
exports.getCapitalAccounts = async (req, res) => {
  try {
    const { includeInactive } = req.query;
    const query = includeInactive === 'true' ? {} : { isActive: true };

    const accounts = await CapitalAccount.find(query).sort('type name');

    // Totals by type
    const totals = {
      cash: 0,
      bank: 0,
      e_wallet: 0,
      other: 0,
      grand: 0,
    };

    for (const acc of accounts) {
      if (!acc.isActive) continue;
      totals[acc.type] = (totals[acc.type] || 0) + acc.balance;
      totals.grand += acc.balance;
    }

    res.status(200).json({
      status: 'success',
      results: accounts.length,
      data: {
        accounts,
        totals,
      },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// POST /finance/admin/accounts
// Create a new capital account
exports.createCapitalAccount = async (req, res) => {
  try {
    const { name, type, bankName, accountNumber, balance, notes } = req.body;

    if (!name || !type) {
      return res
        .status(400)
        .json({ status: 'fail', message: 'name and type are required' });
    }

    const account = await CapitalAccount.create({
      name,
      type,
      bankName: bankName || '',
      accountNumber: accountNumber || '',
      balance: balance || 0,
      notes: notes || '',
    });

    // If initial balance > 0, log it in capital
    if (balance && balance > 0) {
      const capital = await Capital.getCapital();
      capital.logs.push({
        type: 'adjustment',
        amount: balance,
        balanceAfter: capital.currentCapital,
        description: `Initial balance for new account "${name}" (${type})`,
        date: new Date(),
        createdBy: req.user._id,
      });
      await capital.save();
    }

    res.status(201).json({
      status: 'success',
      data: { account },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// PUT /finance/admin/accounts/:id
// Update a capital account (name, type, bankName, notes, isActive)
exports.updateCapitalAccount = async (req, res) => {
  try {
    const account = await CapitalAccount.findById(req.params.id);
    if (!account) {
      return res
        .status(404)
        .json({ status: 'fail', message: 'Account not found' });
    }

    const allowedFields = [
      'name',
      'type',
      'bankName',
      'accountNumber',
      'notes',
      'isActive',
    ];
    for (const field of allowedFields) {
      if (req.body[field] !== undefined) {
        account[field] = req.body[field];
      }
    }

    await account.save();

    res.status(200).json({
      status: 'success',
      data: { account },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// DELETE /finance/admin/accounts/:id
// Soft-delete (deactivate) an account — only if balance is 0
exports.deleteCapitalAccount = async (req, res) => {
  try {
    const account = await CapitalAccount.findById(req.params.id);
    if (!account) {
      return res
        .status(404)
        .json({ status: 'fail', message: 'Account not found' });
    }

    if (account.balance !== 0) {
      return res.status(400).json({
        status: 'fail',
        message: `Cannot delete account with non-zero balance (${account.balance}). Transfer the balance first.`,
      });
    }

    account.isActive = false;
    await account.save();

    res.status(200).json({
      status: 'success',
      message: 'Account deactivated successfully',
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// PUT /finance/admin/accounts/:id/balance
// Manually set/correct the balance of an account (e.g. after physical count)
exports.setAccountBalance = async (req, res) => {
  try {
    const { balance, note } = req.body;

    if (balance === undefined || balance < 0) {
      return res.status(400).json({
        status: 'fail',
        message: 'balance must be a non-negative number',
      });
    }

    const account = await CapitalAccount.findById(req.params.id);
    if (!account) {
      return res
        .status(404)
        .json({ status: 'fail', message: 'Account not found' });
    }

    const oldBalance = account.balance;
    const diff = balance - oldBalance;
    account.balance = balance;
    await account.save();

    // Log the adjustment in capital
    if (diff !== 0) {
      const capital = await Capital.getCapital();
      capital.currentCapital += diff;
      capital.logs.push({
        type: 'adjustment',
        amount: diff,
        balanceAfter: capital.currentCapital,
        description:
          `Account "${account.name}" balance corrected: ${oldBalance} → ${balance}. ${note || ''}`.trim(),
        date: new Date(),
        createdBy: req.user._id,
      });
      await capital.save();
    }

    res.status(200).json({
      status: 'success',
      data: {
        account,
        adjustment: diff,
      },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// ═════════════════════════════════════════════
//  CAPITAL TRANSFERS (Move money between accounts)
// ═════════════════════════════════════════════

// POST /finance/admin/transfers
// Transfer money from one account to another
exports.createTransfer = async (req, res) => {
  try {
    const { fromAccountId, toAccountId, amount, note } = req.body;

    if (!fromAccountId || !toAccountId) {
      return res.status(400).json({
        status: 'fail',
        message: 'fromAccountId and toAccountId are required',
      });
    }

    if (fromAccountId === toAccountId) {
      return res.status(400).json({
        status: 'fail',
        message: 'Cannot transfer to the same account',
      });
    }

    if (!amount || amount <= 0) {
      return res
        .status(400)
        .json({ status: 'fail', message: 'Amount must be positive' });
    }

    const fromAccount = await CapitalAccount.findById(fromAccountId);
    const toAccount = await CapitalAccount.findById(toAccountId);

    if (!fromAccount) {
      return res
        .status(404)
        .json({ status: 'fail', message: 'Source account not found' });
    }
    if (!toAccount) {
      return res
        .status(404)
        .json({ status: 'fail', message: 'Destination account not found' });
    }

    if (!fromAccount.isActive || !toAccount.isActive) {
      return res.status(400).json({
        status: 'fail',
        message: 'Cannot transfer to/from a deactivated account',
      });
    }

    if (fromAccount.balance < amount) {
      return res.status(400).json({
        status: 'fail',
        message: `Insufficient balance in "${fromAccount.name}". Available: ${fromAccount.balance}, Requested: ${amount}`,
      });
    }

    // Perform the transfer
    fromAccount.balance -= amount;
    toAccount.balance += amount;

    await fromAccount.save();
    await toAccount.save();

    // Record the transfer
    const transfer = await CapitalTransfer.create({
      fromAccount: fromAccountId,
      toAccount: toAccountId,
      amount,
      note: note || '',
      createdBy: req.user._id,
    });

    // Log in capital timeline
    const capital = await Capital.getCapital();
    capital.logs.push({
      type: 'adjustment',
      amount: 0, // net effect on total capital is zero
      balanceAfter: capital.currentCapital,
      description:
        `Transfer: ${amount} from "${fromAccount.name}" → "${toAccount.name}". ${note || ''}`.trim(),
      date: new Date(),
      createdBy: req.user._id,
    });
    await capital.save();

    res.status(201).json({
      status: 'success',
      data: {
        transfer,
        fromAccount: {
          _id: fromAccount._id,
          name: fromAccount.name,
          balance: fromAccount.balance,
        },
        toAccount: {
          _id: toAccount._id,
          name: toAccount.name,
          balance: toAccount.balance,
        },
      },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};

// GET /finance/admin/transfers
// Get transfer history
exports.getTransfers = async (req, res) => {
  try {
    const { startDate, endDate, accountId, page = 1, limit = 30 } = req.query;
    const query = {};

    if (startDate || endDate) {
      query.date = {};
      if (startDate) query.date.$gte = new Date(startDate);
      if (endDate) query.date.$lte = new Date(endDate);
    }

    // Filter by specific account (either source or destination)
    if (accountId) {
      query.$or = [{ fromAccount: accountId }, { toAccount: accountId }];
    }

    const skip = (parseInt(page, 10) - 1) * parseInt(limit, 10);

    const [transfers, total] = await Promise.all([
      CapitalTransfer.find(query)
        .sort('-date')
        .skip(skip)
        .limit(parseInt(limit, 10))
        .populate('fromAccount', 'name type bankName')
        .populate('toAccount', 'name type bankName')
        .populate('createdBy', 'name email'),
      CapitalTransfer.countDocuments(query),
    ]);

    res.status(200).json({
      status: 'success',
      results: transfers.length,
      totalRecords: total,
      data: { transfers },
    });
  } catch (err) {
    res.status(500).json({ status: 'error', message: err.message });
  }
};