import { NextResponse } from 'next/server';
import { prisma } from '@/lib/prisma';
import { calculateShipDate, formatDateToYYYYMMDD } from '@/lib/dateUtils';

export async function GET(request: Request) {
  try {
    const { searchParams } = new URL(request.url);
    const masterCustomer = searchParams.get('masterCustomer')?.toLowerCase() || '';
    const shipToCustomer = searchParams.get('shipToCustomer')?.toLowerCase() || '';
    const customer = searchParams.get('customer')?.toLowerCase() || '';
    const po = searchParams.get('po')?.toLowerCase() || '';
    const order = searchParams.get('order')?.toLowerCase() || '';
    const item = searchParams.get('item')?.toLowerCase() || '';
    const prodDesc = searchParams.get('prodDesc')?.toLowerCase() || '';
    const shipdate = searchParams.get('shipdate') || '';
    const shipdateFrom = searchParams.get('shipdateFrom') || '';
    const shipdateTo = searchParams.get('shipdateTo') || '';
    const deliver = searchParams.get('deliver') || '';
    const deliverFrom = searchParams.get('deliverFrom') || '';
    const deliverTo = searchParams.get('deliverTo') || '';
    const farm = searchParams.get('farm')?.toLowerCase() || '';
    const farmId = searchParams.get('farmId') || '';
    const awb = searchParams.get('awb')?.toLowerCase() || '';
    const agency = searchParams.get('agency')?.toLowerCase() || '';

    const orderLines = await prisma.orderLine.findMany({
      include: {
        order: {
          include: {
            shippingCustomer: true,
            billingCustomer: true,
            masterCustomer: true
          }
        },
        product: true,
        farm: true
      },
      orderBy: [
        { order: { deliveryDate: 'asc' } },
        { order: { creationDate: 'asc' } } // FIFO default
      ]
    });

    // Query airway bills to map awb -> agency accurately
    const guias = await prisma.airwayBill.findMany();
    const guiaMap = new Map<string, string>();
    guias.forEach(g => {
      if (g.awb) {
        guiaMap.set(g.awb.trim().toLowerCase(), g.agency);
      }
    });

    const cancelledLabels = await prisma.cancelledLabel.findMany({
      orderBy: { cancelledAt: 'desc' }
    });

    // Flatten and filter lines
    const flatLines = orderLines.map(line => {
      const isAssigned = !!line.farmId || ['FARM ASIGNED', 'RECEIVED', 'SHIPPED'].includes(line.status);
      const isReceived = ['RECEIVED', 'SHIPPED'].includes(line.status);
      const qty = line.quantity || 1;
      const farmAssignedQty = isAssigned ? qty : 0;
      const receivedQty = isReceived ? qty : 0;
      const faltanteQty = qty - farmAssignedQty;

      const masterName = line.order.masterCustomer?.businessName || '';
      const shippingName = line.order.shippingCustomer?.businessName || '';
      const billingName = line.order.billingCustomer?.businessName || '';
      const allCustomerText = `${masterName} ${shippingName} ${billingName}`.toLowerCase();
      const customerName = masterName || shippingName || billingName || '—';

      const orderAwb = (line.airwayBill || line.order.awb || '').trim();
      const matchedAgency = orderAwb ? guiaMap.get(orderAwb.toLowerCase()) : '';
      const orderAgency = matchedAgency || line.order.service || '—';

      return {
        id: line.id,
        orderId: line.order.id,
        po: line.order.po,
        orderNumber: line.order.orderNumber,
        item: line.product.code,
        prodDesc: line.product.name,
        orddate: formatDateToYYYYMMDD(line.order.creationDate),
        shipdt: line.order.shipdt || (line.order.deliveryDate ? calculateShipDate(line.order.deliveryDate, orderAgency) : ''),
        deliver: formatDateToYYYYMMDD(line.order.deliveryDate),
        qty,
        farmAssigned: farmAssignedQty,
        received: receivedQty,
        faltante: faltanteQty,
        farmId: line.farmId,
        farmName: line.farm?.name || '',
        farmRuc: line.farm?.ruc || '',
        awb: orderAwb,
        agency: orderAgency,
        hawb: line.hawb || '',
        boxNumber: line.boxNumber || '',
        uniqueCode: line.uniqueCode || '',
        boxType: line.boxType || line.product.boxType || 'QB',
        farmPrice: line.farmPrice || line.price || 0,
        farmOrderDate: formatDateToYYYYMMDD(line.farmOrderDate) || formatDateToYYYYMMDD(line.createdAt),
        farmDepartureDate: formatDateToYYYYMMDD(line.farmDepartureDate),
        status: line.status,
        customerName,
        masterName,
        shippingName,
        billingName,
        allCustomerText
      };
    }).filter(row => {
      if (masterCustomer && !row.masterName.toLowerCase().includes(masterCustomer)) return false;
      if (shipToCustomer && !`${row.shippingName} ${row.billingName}`.toLowerCase().includes(shipToCustomer)) return false;
      if (customer && !row.allCustomerText.includes(customer)) return false;
      if (po && !row.po.toLowerCase().includes(po)) return false;
      if (order && !row.orderNumber.toLowerCase().includes(order)) return false;
      if (item && !row.item.toLowerCase().includes(item)) return false;
      if (prodDesc && !row.prodDesc.toLowerCase().includes(prodDesc)) return false;
      
      // Shipdate filter: single date or date range
      if (shipdate && row.shipdt !== shipdate) return false;
      if (shipdateFrom && row.shipdt < shipdateFrom) return false;
      if (shipdateTo && row.shipdt > shipdateTo) return false;

      // Delivery date filter: single date or date range
      if (deliver && row.deliver !== deliver) return false;
      if (deliverFrom && row.deliver < deliverFrom) return false;
      if (deliverTo && row.deliver > deliverTo) return false;

      if (farm && !row.farmName.toLowerCase().includes(farm)) return false;
      if (farmId && row.farmId !== farmId) return false;
      if (awb && !row.awb.toLowerCase().includes(awb)) return false;
      if (agency && !row.agency.toLowerCase().includes(agency)) return false;
      return true;
    });

    // Group by Item + Deliver Date (as shown in Sheet MODULO PEDIDO FINCAS)
    const groupsMap = new Map<string, {
      groupKey: string;
      item: string;
      prodDesc: string;
      shipdt: string;
      deliver: string;
      totalQty: number;
      totalFarmAssigned: number;
      totalReceived: number;
      totalFaltante: number;
      lines: typeof flatLines;
    }>();

    flatLines.forEach(row => {
      const key = `${row.item}___${row.deliver}`;
      if (!groupsMap.has(key)) {
        groupsMap.set(key, {
          groupKey: key,
          item: row.item,
          prodDesc: row.prodDesc,
          shipdt: row.shipdt,
          deliver: row.deliver,
          totalQty: 0,
          totalFarmAssigned: 0,
          totalReceived: 0,
          totalFaltante: 0,
          lines: []
        });
      }
      const g = groupsMap.get(key)!;
      g.totalQty += row.qty;
      g.totalFarmAssigned += row.farmAssigned;
      g.totalReceived += row.received;
      g.totalFaltante += row.faltante;
      g.lines.push(row);
    });

    // Exclude groups where totalFaltante <= 0 from the pending summary,
    // and filter group lines to only include those that are still pending (faltante > 0)
    const grouped = Array.from(groupsMap.values())
      .filter(g => g.totalFaltante > 0)
      .map(g => ({
        ...g,
        lines: g.lines.filter(l => l.faltante > 0)
      }));

    // Global totals
    const grandTotals = {
      qty: flatLines.reduce((acc, l) => acc + l.qty, 0),
      farmAssigned: flatLines.reduce((acc, l) => acc + l.farmAssigned, 0),
      received: flatLines.reduce((acc, l) => acc + l.received, 0),
      faltante: flatLines.reduce((acc, l) => acc + l.faltante, 0),
      totalLines: flatLines.length
    };

    return NextResponse.json({
      grouped,
      flatLines,
      cancelledLabels,
      grandTotals
    });
  } catch (error: any) {
    return NextResponse.json({ error: error.message || 'Error generating summary' }, { status: 500 });
  }
}