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

const generateUniqueCode = () => 'UC' + Math.random().toString(36).substring(2, 8).toUpperCase();

export async function POST(req: Request) {
  try {
    const { data, rawData, format } = await req.json();

    if (!Array.isArray(data) || data.length === 0) {
      return NextResponse.json({ error: 'No data provided' }, { status: 400 });
    }

    let createdOrders = 0;
    let createdLines = 0;
    let skipped = 0;

    // ── PRE-VALIDACIÓN: Validar Cliente Master y Fechas ──
    const duplicates: Array<{
      row: number;
      master: string;
      po: string;
      orderNumber: string;
      orderDate: string;
      deliveryDate: string;
      item: string;
      reason: string;
    }> = [];

    // Pre-cargar todos los Master Customers para validación rápida O(1)
    const allMasters = await prisma.customer.findMany({
      where: { isMaster: true },
      select: { id: true, businessName: true }
    });
    const masterMap = new Map<string, string>(); // lowercase name -> id
    allMasters.forEach(m => masterMap.set(m.businessName.trim().toLowerCase(), m.id));

    // Pre-validar fechas obligatorias, SHIPDT vacío y Master Customers
    const now = new Date();
    const todayStr = `${now.getFullYear()}-${String(now.getMonth() + 1).padStart(2, '0')}-${String(now.getDate()).padStart(2, '0')}`;

    for (let i = 0; i < data.length; i++) {
      const row = data[i];
      const rowNum = i + 1;

      const po = (row['Ponumber']?.toString().trim() || row['PONumber']?.toString().trim() || '').trim();
      const orderNumber = (row['CUSTnbr']?.toString().trim() || '').trim();
      const tracking = (row['Tracking']?.toString().trim() || '').trim();

      if (!po && !orderNumber && !tracking) continue;

      const rawOrdDate = row['Orddate'] || row['orddate'] || row['OrderDate'] || row['orderdate'];
      const rawDeliver = row['Deliver'] || row['deliver'] || row['DeliveryDate'] || row['deliverydate'];

      if (!rawOrdDate || !rawDeliver) {
        return NextResponse.json({
          error: `Fila ${rowNum}: El archivo debe tener obligatoriamente fecha de orden (Orddate) y fecha de entrega (Deliver).`
        }, { status: 400 });
      }

      const rawShipdt = row['SHIPDT'] || row['shipdt'] || row['ShipDate'] || row['shipdate'];
      if (rawShipdt && rawShipdt.toString().trim() !== '') {
        return NextResponse.json({
          error: `Fila ${rowNum}: La columna SHIPDATE debe estar vacía en el archivo para que el sistema aplique el cálculo automático según el calendario de envíos.`
        }, { status: 400 });
      }

      const masterName = (row['Company'] || row['company'] || '').toString().trim();
      if (masterName && !masterMap.has(masterName.toLowerCase())) {
        return NextResponse.json({
          error: `Fila ${rowNum}: No se pudo subir la orden. El cliente Master (Company) "${masterName}" no existe en el sistema. Créalo primero en la sección de Clientes.`
        }, { status: 400 });
      }

      const creationDate = parseExcelDate(rawOrdDate);
      const orderDateStr = formatDateToYYYYMMDD(creationDate);
      if (orderDateStr > todayStr) {
        return NextResponse.json({
          error: `Fila ${rowNum}: La fecha de orden (Order date: ${orderDateStr}) no puede ser posterior a la fecha actual (${todayStr}). No se pueden subir órdenes con fechas futuras.`
        }, { status: 400 });
      }
    }

    // Pre-cargar órdenes existentes relevantes por PO + OrderNumber
    const posInFile = data
      .map((r: any) => (r['Ponumber']?.toString().trim() || r['PONumber']?.toString().trim() || '').trim())
      .filter(Boolean);
    const orderNumsInFile = data
      .map((r: any) => (r['CUSTnbr']?.toString().trim() || '').trim())
      .filter(Boolean);

    const existingOrders = await prisma.order.findMany({
      where: {
        po: { in: posInFile },
        orderNumber: { in: orderNumsInFile }
      },
      include: {
        shippingCustomer: true,
        masterCustomer: { select: { id: true, businessName: true } },
        lines: {
          include: {
            product: { select: { id: true, code: true } }
          }
        }
      }
    });

    // Map existing orders by 10-field signature
    const existingSignatures = new Set<string>();
    existingOrders.forEach(o => {
      const oDate = formatDateToYYYYMMDD(o.creationDate);
      const dDate = formatDateToYYYYMMDD(o.deliveryDate);
      const shipTo = (o.shippingCustomer?.businessName || '').toLowerCase().trim();
      const city = (o.shippingCustomer?.city || '').toLowerCase().trim();
      const state = (o.shippingCustomer?.state || '').toLowerCase().trim();
      const zip = (o.shippingCustomer?.zipCode || '').toLowerCase().trim();
      const phone = (o.shippingCustomer?.phone || '').toLowerCase().trim();

      o.lines.forEach(l => {
        const item = (l.product?.code || '').toLowerCase().trim();
        const sig = `${o.po.toLowerCase()}|${o.orderNumber.toLowerCase()}|${oDate}|${dDate}|${shipTo}|${city}|${state}|${zip}|${phone}|${item}`;
        existingSignatures.add(sig);
      });
    });

    const seenInBatchSignatures = new Map<string, number>();

    const headerRow = rawData && rawData.length > 0 ? rawData[0] : null;

    for (let i = 0; i < data.length; i++) {
      const row = data[i];
      const rowNum = i + 1;
      const rawRow = rawData && rawData.length > i + 1 ? rawData[i + 1] : null;
      // Embed _headers into rawRowData so export can reconstruct column order
      const rawRowData = rawRow ? JSON.stringify({ ...rawRow, _headers: headerRow }) : null;

      const po = (row['Ponumber']?.toString().trim() || row['PONumber']?.toString().trim() || '').trim();
      const orderNumber = (row['CUSTnbr']?.toString().trim() || '').trim();
      const tracking = (row['Tracking']?.toString().trim() || '').trim();

      if (!po && !orderNumber && !tracking) { skipped++; continue; }

      const rawOrdDate = row['Orddate'] || row['orddate'] || row['OrderDate'] || row['orderdate'];
      const rawDeliver = row['Deliver'] || row['deliver'] || row['DeliveryDate'] || row['deliverydate'];

      const creationDate = parseExcelDate(rawOrdDate);
      const deliveryDate = parseExcelDate(rawDeliver);
      const orderDateStr = formatDateToYYYYMMDD(creationDate);
      const deliveryDateStr = formatDateToYYYYMMDD(deliveryDate);
      const shipdt = calculateShipDate(deliveryDate, format || row['Service'] || row['agency'] || 'FEDEX');

      const masterName = (row['Company'] || row['company'] || '').toString().trim();
      let masterCustomerId: string | undefined = undefined;
      if (masterName) {
        masterCustomerId = masterMap.get(masterName.toLowerCase());
      }

      // ── Customer (Billing / Soldto) ──────────────────────────────
      const billingName = (row['Soldto'] || row['soldto'] || row['Company'] || row['company'] || 'Desconocido').toString().trim();
      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() || row['Phone']?.toString() || null,
            isBilling: true, 
            isShipping: false,
            parentId: masterCustomerId || null
          }
        });
      }

      // ── Customer (Shipping / Shipto) ─────────────────────────────
      const shippingName = (row['Shipto'] || row['shipto'] || 'Desconocido').toString().trim();
      let shippingCustomer = await prisma.customer.findFirst({
        where: { businessName: shippingName, isShipping: true }
      });
      if (!shippingCustomer) {
        shippingCustomer = await prisma.customer.create({
          data: {
            businessName: shippingName,
            address: row['Address']?.toString() || null,
            address2: row['Address2']?.toString() || null,
            city: row['City']?.toString() || null,
            state: row['State']?.toString() || null,
            zipCode: row['Zip']?.toString() || null,
            phone: row['Phone']?.toString() || null,
            isShipping: true,
            isBilling: false,
            parentId: masterCustomerId || null
          }
        });
      }

      // ── Product ──────────────────────────────────────────────────
      const productCode = (row['Item']?.toString().trim() || 'GENERIC').toUpperCase();
      const productName = (row['ProdDesc']?.toString().trim() || 'Producto Desconocido');

      let product = await prisma.product.findUnique({ where: { code: productCode } });
      if (!product) {
        product = await prisma.product.create({
          data: { code: productCode, name: productName }
        });
      }

      // ── Farm ─────────────────────────────────────────────────────
      let farmId: string | undefined = undefined;
      const farmName = row['Farm']?.toString().trim();
      if (farmName) {
        let farm = await prisma.farm.findFirst({ where: { name: farmName } });
        if (!farm) {
          farm = await prisma.farm.create({ data: { name: farmName } });
        }
        farmId = farm.id;
      }

      // ── 10-Field Duplicate Signature Validation ──────────────────
      const shipToClean = (shippingName || '').toLowerCase().trim();
      const cityClean = (row['City']?.toString() || '').toLowerCase().trim();
      const stateClean = (row['State']?.toString() || '').toLowerCase().trim();
      const zipClean = (row['Zip']?.toString() || '').toLowerCase().trim();
      const phoneClean = (row['Phone']?.toString() || '').toLowerCase().trim();

      const signature = `${po.toLowerCase()}|${orderNumber.toLowerCase()}|${orderDateStr}|${deliveryDateStr}|${shipToClean}|${cityClean}|${stateClean}|${zipClean}|${phoneClean}|${productCode.toLowerCase()}`;

      let isRowDuplicate = false;
      let duplicateReason = '';

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

      if (!isRowDuplicate && existingSignatures.has(signature)) {
        isRowDuplicate = true;
        duplicateReason = `Ya registrada previamente en el sistema`;
      }

      if (isRowDuplicate) {
        duplicates.push({
          row: rowNum,
          master: masterName || '—',
          po,
          orderNumber,
          orderDate: orderDateStr,
          deliveryDate: deliveryDateStr,
          item: productCode,
          reason: duplicateReason
        });
        skipped++;
        continue; // NO insertar órdenes ni líneas duplicadas en la base de datos
      }

      // ── Order (unique by PO + CUSTnbr + DeliveryDate) ────────────
      const orderKey = po || tracking || `NOPO-${Date.now()}`;
      const orderNumKey = orderNumber || tracking || `NONUM-${Date.now()}`;
      const awb = row['AWB']?.toString().trim() || undefined;

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

      if (!order) {
        order = await prisma.order.create({
          data: {
            po: orderKey,
            orderNumber: orderNumKey,
            creationDate,
            deliveryDate,
            awb,
            shipdt,
            master: tracking || undefined,
            masterCustomerId: masterCustomerId || null,
            billingCustomerId: billingCustomer.id,
            shippingCustomerId: shippingCustomer.id,
            isDuplicate: false,
            duplicateInfo: null
          }
        });
        createdOrders++;
      }

      // ── Order Lines ──────────────────────────────────────────────
      const quantity = parseInt(row['Quantity']?.toString()) || 1;
      const linesData = Array.from({ length: quantity }).map(() => ({
        orderId: order!.id,
        productId: product!.id,
        farmId: farmId || null,
        quantity: 1,
        price: row['DclValue'] ? parseFloat(row['DclValue']) : 0,
        trackingNumber: tracking || undefined,
        uniqueCode: generateUniqueCode(),
        rawRowData: rawRowData,
        isDuplicate: false,
        duplicateInfo: null
      }));

      await prisma.orderLine.createMany({ data: linesData as any });
      createdLines += quantity;
      existingSignatures.add(signature);
    }

    return NextResponse.json({
      message: duplicates.length > 0
        ? `Importación completada. Se importaron ${createdOrders} órdenes (${createdLines} cajas). Se omitieron ${duplicates.length} líneas del archivo de Excel por ser órdenes duplicadas.`
        : `Importación exitosa. Se importaron ${createdOrders} órdenes (${createdLines} cajas).`,
      format: format || 'auto',
      createdOrders,
      createdLines,
      duplicateCount: duplicates.length,
      duplicateList: duplicates,
      skipped
    });

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