const router = require('express').Router();
const pool = require('../config/db');
const auth = require('../middleware/auth');
const { requireRole } = require('../middleware/auth');
const ExcelJS = require('exceljs');

router.use(auth);
router.use(requireRole('master_admin'));

// Get report data
router.get('/', async (req, res) => {
  try {
    const { range, from, to } = req.query;
    const today = new Date().toISOString().slice(0, 10);

    let dateFilter = '';
    const params = [];

    if (range === 'today') {
      dateFilter = 'AND o.order_date = ?';
      params.push(today);
    } else if (range === 'week') {
      dateFilter = 'AND o.order_date >= DATE_SUB(?, INTERVAL 7 DAY)';
      params.push(today);
    } else if (range === 'month') {
      dateFilter = 'AND o.order_date >= DATE_SUB(?, INTERVAL 30 DAY)';
      params.push(today);
    } else if (range === 'custom' && from && to) {
      dateFilter = 'AND o.order_date >= ? AND o.order_date <= ?';
      params.push(from, to);
    }

    // Revenue & orders
    const [confirmed] = await pool.query(
      `SELECT o.*, oi.product_id, oi.qty, oi.unit_price, oi.line_total, p.cost_price, p.name as product_name
       FROM orders o
       JOIN order_items oi ON o.id = oi.order_id
       LEFT JOIN products p ON oi.product_id = p.id
       WHERE o.status = 'confirmed' ${dateFilter}`,
      params
    );

    const [cancelled] = await pool.query(
      `SELECT COUNT(*) as count FROM orders o WHERE o.status = 'cancelled' ${dateFilter}`,
      params
    );

    const [refunded] = await pool.query(
      `SELECT COUNT(*) as count FROM orders o WHERE o.status = 'refunded' ${dateFilter}`,
      params
    );

    // Aggregate
    const orderMap = {};
    let totalRevenue = 0, totalCogs = 0, totalDiscount = 0;
    const productStats = {};
    const clientStats = {};
    const paymentMethodStats = {};

    confirmed.forEach(row => {
      if (!orderMap[row.id]) {
        orderMap[row.id] = true;
        totalRevenue += parseFloat(row.total);
        totalDiscount += parseFloat(row.discount_amt);
        // Payment method
        paymentMethodStats[row.payment_method] = (paymentMethodStats[row.payment_method] || 0) + parseFloat(row.total);
        // Client
        if (!clientStats[row.client_id]) clientStats[row.client_id] = { spent: 0, orders: 0 };
        clientStats[row.client_id].spent += parseFloat(row.total);
        clientStats[row.client_id].orders += 1;
      }
      // COGS
      totalCogs += (parseFloat(row.cost_price) || 0) * row.qty;
      // Product stats
      if (!productStats[row.product_id]) productStats[row.product_id] = { name: row.product_name, qty: 0, revenue: 0 };
      productStats[row.product_id].qty += row.qty;
      productStats[row.product_id].revenue += parseFloat(row.line_total);
    });

    // Referral count
    const [refOrders] = await pool.query(
      `SELECT COUNT(*) as count FROM orders o WHERE o.referral_from IS NOT NULL AND o.status = 'confirmed' ${dateFilter}`,
      params
    );
    const referralBonus = refOrders[0].count * 75;

    // Top products
    const topProducts = Object.entries(productStats)
      .sort((a, b) => b[1].qty - a[1].qty)
      .slice(0, 6)
      .map(([id, d]) => ({ id, ...d }));

    // Top clients
    const clientIds = Object.keys(clientStats);
    let topClients = [];
    if (clientIds.length > 0) {
      const [clients] = await pool.query(
        `SELECT id, name, is_vip FROM clients WHERE id IN (${clientIds.map(() => '?').join(',')})`,
        clientIds
      );
      topClients = Object.entries(clientStats)
        .sort((a, b) => b[1].spent - a[1].spent)
        .slice(0, 8)
        .map(([id, d]) => {
          const c = clients.find(x => x.id === id);
          return { id, name: c?.name, is_vip: c?.is_vip, ...d };
        });
    }

    const uniqueOrders = Object.keys(orderMap).length;

    res.json({
      revenue: totalRevenue,
      cogs: totalCogs,
      grossProfit: totalRevenue - totalCogs,
      discounts: totalDiscount,
      referralBonus,
      netProfit: totalRevenue - totalCogs - referralBonus,
      totalOrders: uniqueOrders,
      cancelledCount: cancelled[0].count,
      refundedCount: refunded[0].count,
      topProducts,
      topClients,
      paymentMethods: paymentMethodStats,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Export Excel (.xlsx) with styled table
router.get('/export', async (req, res) => {
  try {
    const { format } = req.query;
    const [orders] = await pool.query(`
      SELECT o.*, c.name as client_name, c.phone as client_phone, c.email as client_email
      FROM orders o
      LEFT JOIN clients c ON o.client_id = c.id
      ORDER BY o.order_date DESC
    `);
    const [items] = await pool.query(`
      SELECT oi.*, p.name as product_name, p.mg, p.ml FROM order_items oi
      LEFT JOIN products p ON oi.product_id = p.id
    `);

    // JSON format for PDF generation on frontend
    if (format === 'json') {
      const result = orders.map(o => {
        const oi = items.filter(i => i.order_id === o.id);
        return {
          id: o.id,
          date: o.order_date ? new Date(o.order_date).toLocaleDateString('en-US', { month: 'short', day: 'numeric', year: 'numeric' }) : '',
          client: o.client_name || '',
          phone: o.client_phone || '',
          email: o.client_email || '',
          items: oi.map(i => `${i.product_name} (${i.mg}mg/${i.ml}ml) x${i.qty}`).join('; '),
          subtotal: parseFloat(o.subtotal),
          discount: parseFloat(o.discount_amt),
          discountPct: parseFloat(o.discount_pct),
          total: parseFloat(o.total),
          paymentMethod: o.payment_method || '',
          status: o.status,
          deliveryStatus: o.delivery_status,
          shippingAddress: o.shipping_address || '',
        };
      });
      return res.json(result);
    }

    // Excel export with ExcelJS
    const workbook = new ExcelJS.Workbook();
    workbook.creator = 'PeptideOS';
    workbook.created = new Date();

    const sheet = workbook.addWorksheet('Sales Report', {
      views: [{ state: 'frozen', ySplit: 1 }],
    });

    // Define columns
    sheet.columns = [
      { header: 'No', key: 'no', width: 5 },
      { header: 'Order ID', key: 'orderId', width: 12 },
      { header: 'Date', key: 'date', width: 16 },
      { header: 'Client', key: 'client', width: 22 },
      { header: 'Phone', key: 'phone', width: 16 },
      { header: 'Email', key: 'email', width: 24 },
      { header: 'Items', key: 'items', width: 40 },
      { header: 'Subtotal', key: 'subtotal', width: 14 },
      { header: 'Discount', key: 'discount', width: 14 },
      { header: 'Discount %', key: 'discountPct', width: 12 },
      { header: 'Total', key: 'total', width: 14 },
      { header: 'Payment', key: 'payment', width: 14 },
      { header: 'Status', key: 'status', width: 12 },
      { header: 'Delivery', key: 'delivery', width: 12 },
    ];

    // Style header row
    const headerRow = sheet.getRow(1);
    headerRow.font = { bold: true, color: { argb: 'FFFFFFFF' }, size: 11 };
    headerRow.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF3B82F6' } };
    headerRow.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true };
    headerRow.height = 28;
    headerRow.eachCell((cell) => {
      cell.border = {
        top: { style: 'thin', color: { argb: 'FF1E40AF' } },
        bottom: { style: 'thin', color: { argb: 'FF1E40AF' } },
        left: { style: 'thin', color: { argb: 'FF1E40AF' } },
        right: { style: 'thin', color: { argb: 'FF1E40AF' } },
      };
    });

    // Add data rows
    orders.forEach((o, idx) => {
      const oi = items.filter(i => i.order_id === o.id);
      const itemStr = oi.map(i => `${i.product_name} x${i.qty}`).join(' | ');
      const dateStr = o.order_date ? new Date(o.order_date).toLocaleDateString('en-US', { month: 'short', day: 'numeric', year: 'numeric' }) : '';

      const row = sheet.addRow({
        no: idx + 1,
        orderId: o.id,
        date: dateStr,
        client: o.client_name || '',
        phone: o.client_phone || '',
        email: o.client_email || '',
        items: itemStr,
        subtotal: parseFloat(o.subtotal),
        discount: parseFloat(o.discount_amt),
        discountPct: `${parseFloat(o.discount_pct).toFixed(0)}%`,
        total: parseFloat(o.total),
        payment: o.payment_method || '',
        status: o.status,
        delivery: o.delivery_status,
      });

      // Alternate row colors
      const fillColor = idx % 2 === 0 ? 'FFF8FAFC' : 'FFFFFFFF';
      row.eachCell((cell) => {
        cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: fillColor } };
        cell.border = {
          top: { style: 'thin', color: { argb: 'FFE2E8F0' } },
          bottom: { style: 'thin', color: { argb: 'FFE2E8F0' } },
          left: { style: 'thin', color: { argb: 'FFE2E8F0' } },
          right: { style: 'thin', color: { argb: 'FFE2E8F0' } },
        };
        cell.alignment = { vertical: 'middle', wrapText: true };
        cell.font = { size: 10 };
      });

      // Format currency columns
      row.getCell('subtotal').numFmt = '$#,##0.00';
      row.getCell('discount').numFmt = '$#,##0.00';
      row.getCell('total').numFmt = '$#,##0.00';
      row.getCell('total').font = { size: 10, bold: true };

      // Status color
      const statusCell = row.getCell('status');
      if (o.status === 'confirmed') {
        statusCell.font = { size: 10, color: { argb: 'FF16A34A' }, bold: true };
      } else if (o.status === 'cancelled') {
        statusCell.font = { size: 10, color: { argb: 'FFDC2626' }, bold: true };
      } else if (o.status === 'refunded') {
        statusCell.font = { size: 10, color: { argb: 'FFCA8A04' }, bold: true };
      }

      row.height = 22;
    });

    // Auto-filter
    sheet.autoFilter = { from: 'A1', to: `N${orders.length + 1}` };

    // Send file
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', 'attachment; filename=PeptideOS-Report.xlsx');
    await workbook.xlsx.write(res);
    res.end();
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
