import { NextResponse } from 'next/server';
import { prisma } from '@/lib/prisma';
import * as XLSX from 'xlsx';
import { parseExcelDate, calculateShipDate, formatDateToYYYYMMDD } from '@/lib/dateUtils';

export async function POST(req: Request) {
  try {
    const formData = await req.formData();
    const file = formData.get('file') as File;
    if (!file) {
      return NextResponse.json({ error: 'No se encontró archivo' }, { status: 400 });
    }

    const buffer = Buffer.from(await file.arrayBuffer());
    const wb = XLSX.read(buffer, { type: 'buffer' });
    const ws = wb.Sheets[wb.SheetNames[0]];
    const data: any[] = XLSX.utils.sheet_to_json(ws);

    if (!data || data.length === 0) {
      return NextResponse.json({ error: 'El archivo está vacío' }, { status: 400 });
    }

    let createdOrders = 0;
    let createdLines = 0;
    let duplicateLinesCount = 0;
    const duplicateReport: any[] = [];

    // Map to track 10-field signatures within this Excel file:
    // (PO, OrderNumber, OrderDate, DeliveryDate, ShipTo, ShipTo2, City, State, Zip, Phone)
    const fileSignatures = new Map<string, number>(); // signature -> rowNumber (1-indexed)

    for (let i = 0; i < data.length; i++) {
      const row = data[i];
      const rowNum = i + 2; // Excel row number (1-based header is row 1)

      const po = row.Ponumber?.toString().trim();
      const orderNumber = row.CUSTnbr?.toString().trim();
      if (!po || !orderNumber) continue;

      const orderDateStr = formatDateToYYYYMMDD(row.Orddate);
      const deliveryDateStr = formatDateToYYYYMMDD(row.Deliver);
      const shipTo = (row.Shipto?.toString() || '').trim().toLowerCase();
      const shipTo2 = (row.Shipto2?.toString() || '').trim().toLowerCase();
      const city = (row.City?.toString() || '').trim().toLowerCase();
      const state = (row.State?.toString() || '').trim().toLowerCase();
      const zip = (row.Zip?.toString() || '').trim().toLowerCase();
      const phone = (row.Phone?.toString() || '').trim().toLowerCase();

      // Compute unique 10-field key
      const signature = `${po}|${orderNumber}|${orderDateStr}|${deliveryDateStr}|${shipTo}|${shipTo2}|${city}|${state}|${zip}|${phone}`;

      let isRowDuplicate = false;
      let duplicateReason = '';

      if (fileSignatures.has(signature)) {
        const prevRow = fileSignatures.get(signature);
        isRowDuplicate = true;
        duplicateReason = `Duplicada con Fila ${prevRow} del mismo archivo Excel`;
      } else {
        fileSignatures.set(signature, rowNum);
      }

      // 1. Manage Customers
      const billingName = row.Soldto || 'Desconocido';
      const shippingName = row.Shipto || 'Desconocido';

      let billingCustomer = await prisma.customer.findFirst({
        where: { businessName: billingName, isBilling: true }
      });
      if (!billingCustomer) {
        billingCustomer = await prisma.customer.create({
          data: {
            businessName: billingName,
            phone: row.STPhone?.toString(),
            isBilling: true
          }
        });
      }

      let shippingCustomer = await prisma.customer.findFirst({
        where: { businessName: shippingName, isShipping: true }
      });
      if (!shippingCustomer) {
        shippingCustomer = await prisma.customer.create({
          data: {
            businessName: shippingName,
            address: row.Address,
            address2: row.Address2,
            city: row.City,
            state: row.State,
            zipCode: row.Zip?.toString(),
            phone: row.Phone?.toString(),
            isShipping: true
          }
        });
      }

      // Check DB duplication if not already flagged in file
      if (!isRowDuplicate) {
        const existingInDb = await prisma.order.findFirst({
          where: {
            po,
            orderNumber,
            deliveryDate: parseExcelDate(row.Deliver),
            shippingCustomer: {
              city: row.City || undefined,
              state: row.State || undefined,
              zipCode: row.Zip?.toString() || undefined
            }
          }
        });
        if (existingInDb) {
          isRowDuplicate = true;
          duplicateReason = `Duplicada con orden ya existente en el sistema (PO ${po}, CUSTnbr ${orderNumber})`;
        }
      }

      if (isRowDuplicate) {
        duplicateLinesCount++;
        duplicateReport.push({
          rowNumber: rowNum,
          po,
          orderNumber,
          deliveryDate: deliveryDateStr,
          customer: row.Shipto || '—',
          reason: duplicateReason
        });
      }

      // 2. Manage Product
      const productCode = row.Item?.toString() || 'GENERIC';
      const productName = row.ProdDesc || 'Producto Desconocido';
      
      let product = await prisma.product.findUnique({ where: { code: productCode } });
      if (!product) {
        product = await prisma.product.create({
          data: { 
            code: productCode, 
            name: productName,
            length: row.Length ? parseFloat(row.Length) : null,
            width: row.Width ? parseFloat(row.Width) : null,
            height: row.Height ? parseFloat(row.Height) : null,
            realWeight: row.WeightKg ? parseFloat(row.WeightKg) : null,
            declaredValue: row.DclValue ? parseFloat(row.DclValue) : null,
          }
        });
      }

      // 3. Manage Order: Distinct by PO + orderNumber + deliveryDate
      const creationDate = parseExcelDate(row.Orddate);
      const deliveryDate = parseExcelDate(row.Deliver);
      const shipdt = (row.SHIPDT && row.SHIPDT.toString().trim() !== '')
        ? row.SHIPDT.toString().trim()
        : calculateShipDate(deliveryDate, row.Service?.toString() || 'FEDEX');

      let order = await prisma.order.findUnique({
        where: {
          po_orderNumber_deliveryDate: { po, orderNumber, deliveryDate }
        }
      });

      if (!order) {
        order = await prisma.order.create({
          data: {
            po,
            orderNumber,
            creationDate,
            deliveryDate,
            awb: row.AWB?.toString(),
            master: row.MASTER?.toString(),
            shipdt,
            service: row.Service?.toString() || 'FEDEX',
            pkgType: row.PkgType?.toString(),
            origin: row.Origin?.toString(),
            billingCustomerId: billingCustomer.id,
            shippingCustomerId: shippingCustomer.id,
            status: "CONFIRMED",
            isDuplicate: isRowDuplicate,
            duplicateInfo: isRowDuplicate ? duplicateReason : null
          }
        });
        createdOrders++;
      } else if (isRowDuplicate) {
        // If order existed and row is duplicate, ensure it's marked
        await prisma.order.update({
          where: { id: order.id },
          data: { isDuplicate: true, duplicateInfo: duplicateReason }
        });
      }

      // 4. Create Order Lines
      const quantity = parseInt(row.Quantity) || 1;
      const linesData = Array.from({ length: quantity }).map(() => ({
        orderId: order!.id,
        productId: product!.id,
        quantity: 1, // Divide into 1 box per line
        price: row.DclValue ? parseFloat(row.DclValue) : 0,
        message: row.Message?.toString(),
        observaciones: row.OBSERVACIONES?.toString(),
        isDuplicate: isRowDuplicate,
        duplicateInfo: isRowDuplicate ? duplicateReason : null
      }));

      await prisma.orderLine.createMany({
        data: linesData
      });

      createdLines += quantity;
    }

    return NextResponse.json({
      message: 'Success',
      createdOrders,
      createdLines,
      duplicateLinesCount,
      duplicateReport
    });

  } catch (error: any) {
    console.error("Error importing orders:", error);
    return NextResponse.json({ error: error.message }, { status: 500 });
  }
}