import { NextResponse } from 'next/server';
import { prisma } from '@/lib/prisma';
import * as XLSX from 'xlsx';

export async function GET() {
  try {
    const orders = await prisma.order.findMany({
      where: {
        status: { in: ['CONFIRMED', 'SHIPPED', 'DELIVERED', 'IN_TRANSIT'] }
      },
      include: {
        shippingCustomer: true,
        lines: { include: { product: true } }
      }
    });

    const exportData = orders.map(order => {
      const totalBoxes = order.lines.reduce((sum, line) => sum + line.quantity, 0);
      return {
        'Order Number': order.orderNumber,
        'PO #': order.po,
        'Carrier': 'FedEx/UPS', // Usually mapped from carrier table, hardcoded for export format
        'Service Type': 'Next Day Air',
        'Recipient Name': order.shippingCustomer?.businessName || '',
        'Recipient Company': order.shippingCustomer?.businessName || '',
        'Recipient Address': '123 Floral St', // Mock since no address field exists yet
        'Recipient City': 'Miami',
        'Recipient State': 'FL',
        'Recipient Zip': '33122',
        'Recipient Country': 'US',
        'Recipient Phone': order.shippingCustomer?.phone || '',
        'Weight (lbs)': totalBoxes * 15, // 15 lbs per box estimate
        'Length (in)': 40,
        'Width (in)': 10,
        'Height (in)': 10,
        'Package Type': 'Custom Box',
        'Total Packages': totalBoxes,
        'Reference 1': order.po,
      };
    });

    const wb = XLSX.utils.book_new();
    const ws = XLSX.utils.json_to_sheet(exportData);
    XLSX.utils.book_append_sheet(wb, ws, 'FedEx_UPS_Import');

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

    return new NextResponse(buf, {
      status: 200,
      headers: {
        'Content-Disposition': 'attachment; filename="Carrier_Export_USFloral.xlsx"',
        'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      }
    });
  } catch (error) {
    console.error(error);
    return NextResponse.json({ error: 'Failed to generate export' }, { status: 500 });
  }
}