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

export async function GET(request: Request) {
  try {
    const { searchParams } = new URL(request.url);
    const status = searchParams.get('status') || 'ALL';

    const whereClause: any = {};
    if (status !== 'ALL') {
      if (status === 'WITH_TRACKING') {
        whereClause.lines = { some: { trackingNumber: { not: null } } };
      } else if (status === 'NO_TRACKING') {
        whereClause.lines = { some: { trackingNumber: null } };
      } else {
        whereClause.status = status;
      }
    }

    const orders = await prisma.order.findMany({
      where: whereClause,
      include: {
        billingCustomer: true,
        shippingCustomer: true,
        lines: {
          include: { product: true, farm: true }
        }
      },
      orderBy: { creationDate: 'desc' }
    });

    const headers = [
      'TRACKING', 'Company', 'Orddate', 'Deliver', 'Shipto', 'Shipto2',
      'Address', 'Address2', 'City', 'State', 'Zip', 'Phone',
      'Soldto', 'STPhone', 'Ponumber', 'CUSTnbr', 'Quantity', 'Item',
      'ProdDesc', 'Message', 'Farm', 'AWB'
    ];

    const data: any[][] = [headers];

    const formatDate = (date: Date) => date.toISOString().split('T')[0];

    orders.forEach(order => {
      order.lines.forEach(line => {
        data.push([
          line.trackingNumber || '', // TRACKING
          'eblooms', // Company
          formatDate(order.creationDate), // Orddate
          formatDate(order.deliveryDate), // Deliver
          order.shippingCustomer?.businessName || '', // Shipto
          '', // Shipto2
          order.shippingCustomer?.address || '', // Address
          order.shippingCustomer?.address2 || '', // Address2
          order.shippingCustomer?.city || '', // City
          order.shippingCustomer?.state || '', // State
          order.shippingCustomer?.zipCode || '', // Zip
          order.shippingCustomer?.phone || '', // Phone
          order.billingCustomer?.businessName || '', // Soldto
          order.billingCustomer?.phone || '', // STPhone
          order.po, // Ponumber
          order.orderNumber, // CUSTnbr
          line.quantity, // Quantity
          line.product.code, // Item
          line.product.name, // ProdDesc
          line.message || '', // Message
          line.farm?.name || '', // Farm
          order.awb || '' // AWB
        ]);
      });
    });

    const ws = XLSX.utils.aoa_to_sheet(data);
    
    const widths = {
      'TRACKING': 18, 'Company': 12, 'Orddate': 12, 'Deliver': 12, 'Shipto': 25, 'Shipto2': 10,
      'Address': 30, 'Address2': 10, 'City': 15, 'State': 8, 'Zip': 10, 'Phone': 15,
      'Soldto': 25, 'STPhone': 15, 'Ponumber': 12, 'CUSTnbr': 12, 'Quantity': 10, 'Item': 12,
      'ProdDesc': 35, 'Message': 20, 'Farm': 20, 'AWB': 18
    };
    
    ws['!cols'] = headers.map(h => ({ wch: widths[h as keyof typeof widths] || 15 }));

    const wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, 'Orders_Export');
    const buffer = XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' });

    return new Response(buffer, {
      headers: {
        'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        'Content-Disposition': 'attachment; filename="export_ordenes.xlsx"',
      },
    });

  } catch (error: any) {
    console.error(error);
    return NextResponse.json({ error: 'Error generating export' }, { status: 500 });
  }
}