import { string } from 'alga-js'
import refreshTokenHelper from "../../utils/refreshTokenHelper"
import forceLogoutHelper from "../../utils/forceLogoutHelper"
import errorHandlingHelper from "../../utils/errorHandlingHelper"
import fetchHelper from "../../utils/fetchHelper"
import sharedLang from "~/assets/langs/shared.js"

/**
 * Validates paket-to-shipment 1:1 relationship
 * Parses M_InOut_IDs from parcel line Description fields and cross-references with shipment records
 */
const performPaketShipmentValidation = async (
  event: any,
  token: string,
  organizationId: number,
  parcelLines: any,
  shipmentLines: any[]
) => {
  const validation: any = {
    totalParcels: 0,
    totalShipments: 0,
    matchedParcels: 0,
    matchedShipments: 0,
    unmatchedParcels: [],
    unmatchedShipments: [],
    parcelToShipmentMap: new Map(),
    shipmentToPaketMap: new Map()
  }

  try {
    // Step 1: Extract M_InOut_IDs from parcel Description fields
    const parcelInoutIds = new Set<number>()

    console.log('Processing parcel lines...')
    console.log('Parcel lines keys:', Object.keys(parcelLines))
    console.log('Total parcel products:', Object.keys(parcelLines).length)

    for (const productId in parcelLines) {
      const lineData = parcelLines[productId]
      console.log(`Processing parcel product ${productId}, details count:`, lineData.details?.length || 0)

      // Each detail represents a parcel fee line
      for (const detail of lineData.details) {
        if (detail.description) {
          console.log(`  Parcel fee line ${detail.id}: Description="${detail.description}"`)
          // Parse comma-separated M_InOut_IDs from Description
          const inoutIdsStr = detail.description.split(',').map((s: string) => s.trim())
          console.log(`    Parsed IDs:`, inoutIdsStr)
          for (const idStr of inoutIdsStr) {
            const inoutId = parseInt(idStr)
            if (!isNaN(inoutId) && inoutId > 0) {
              console.log(`    Adding parcel InOut ID: ${inoutId}`)
              parcelInoutIds.add(inoutId)

              // Track mapping from shipment to parcel
              if (!validation.shipmentToPaketMap.has(inoutId)) {
                validation.shipmentToPaketMap.set(inoutId, [])
              }
              validation.shipmentToPaketMap.get(inoutId).push({
                feeLineId: detail.id,
                productId: parseInt(productId),
                qty: detail.qty
              })
            }
          }
        } else {
          console.log(`  Parcel fee line ${detail.id}: No Description field`)
        }

        validation.totalParcels++
      }
    }

    // Step 2: Collect M_InOut_IDs from individual fulfillment sub-items
    // These are the detail lines with FulfillTypeAccounting === 'fulfillment' that get grouped into the final product
    const shipmentInoutIds = new Set<number>()

    for (const line of shipmentLines) {
      const inoutId = line.M_InOut_ID?.id
      const fulfillType = line.FulfillTypeAccounting

      // Only include fulfillment sub-items (the individual lines that get counted together)
      if (inoutId && fulfillType === 'fulfillment') {
        console.log(`  Fulfillment line ${line.id}: M_InOut_ID=${inoutId}, DocumentNo=${line.M_InOut_ID?.DocumentNo}`)
        shipmentInoutIds.add(inoutId)

        // Track mapping from shipment to parcel (only add unique shipment IDs)
        if (!validation.parcelToShipmentMap.has(inoutId)) {
          validation.parcelToShipmentMap.set(inoutId, {
            inoutId,
            documentNo: line.M_InOut_ID?.DocumentNo || inoutId.toString(),
            feeLineId: line.id,
            type: fulfillType,
            productId: line.M_Product_ID?.id
          })
        }
      }
    }

    // Count unique shipments
    validation.totalShipments = shipmentInoutIds.size

    console.log('═══════════════════════════════════════════════════════════')
    console.log('PAKET-SHIPMENT VALIDATION DEBUG')
    console.log('═══════════════════════════════════════════════════════════')
    console.log('Total fee lines checked:', shipmentLines.length)
    console.log('Fulfillment type lines found:', shipmentLines.filter(l => l.FulfillTypeAccounting === 'fulfillment').length)
    console.log('Parcel InOut IDs from Description:', Array.from(parcelInoutIds).sort((a, b) => a - b))
    console.log('Fulfillment InOut IDs (FulfillTypeAccounting=fulfillment):', Array.from(shipmentInoutIds).sort((a, b) => a - b))
    console.log('Total unique parcel references:', parcelInoutIds.size)
    console.log('Total unique fulfillment shipments:', shipmentInoutIds.size)
    console.log('═══════════════════════════════════════════════════════════')

    // Store detailed comparison info for frontend display
    validation.comparisonDebug = {
      parcelInoutIds: Array.from(parcelInoutIds).sort((a, b) => a - b),
      fulfillmentInoutIds: Array.from(shipmentInoutIds).sort((a, b) => a - b),
      totalFeeLines: shipmentLines.length,
      fulfillmentTypeLines: shipmentLines.filter(l => l.FulfillTypeAccounting === 'fulfillment').length
    }

    // Step 3: Find matches and mismatches
    // Check which parcel inouts have corresponding shipments
    for (const inoutId of parcelInoutIds) {
      if (shipmentInoutIds.has(inoutId)) {
        validation.matchedParcels++
      } else {
        validation.unmatchedParcels.push({
          inoutId,
          paketLines: validation.shipmentToPaketMap.get(inoutId) || [],
          reason: 'Parcel references this shipment but no fulfillment detail line found'
        })
      }
    }

    // Check which shipment inouts have corresponding parcels
    for (const inoutId of shipmentInoutIds) {
      if (parcelInoutIds.has(inoutId)) {
        validation.matchedShipments++
      } else {
        const shipmentInfo = validation.parcelToShipmentMap.get(inoutId)
        validation.unmatchedShipments.push({
          inoutId,
          documentNo: shipmentInfo?.documentNo || inoutId.toString(),
          feeLineId: shipmentInfo?.feeLineId,
          type: shipmentInfo?.type,
          reason: 'Fulfillment detail line exists but no parcel references this shipment'
        })
      }
    }

    console.log('Validation Results:', {
      totalParcels: validation.totalParcels,
      totalShipments: validation.totalShipments,
      matchedParcels: validation.matchedParcels,
      matchedShipments: validation.matchedShipments,
      unmatchedParcelCount: validation.unmatchedParcels.length,
      unmatchedShipmentCount: validation.unmatchedShipments.length
    })

  } catch (err: any) {
    console.error('Error performing paket-shipment validation:', err)
    validation.error = err.message || 'Validation failed'
  }

  return validation
}

const handleFunc = async (event: any, authToken: any = null) => {
  let data: any = { success: [], errors: [], preview: false }
  const token = authToken ?? await getTokenHelper(event)
  const organizationId = parseInt(getCookie(event, 'organizationId') || '1000000')
  const body = await readBody(event)

  // Get language preference from cookie (default to 'en-US')
  const lang = getCookie(event, 'language') || 'en-US'
  const t = sharedLang(lang)
  const isPreview = body.preview === true
  const shouldFetchCountryData = body.fetchCountryData === true
  const shouldCreateReport = body.createReport === true && !isPreview
  const shouldValidatePaketShipment = body.validatePaketShipment === true && isPreview
  const debugStorageUsage = body.debugStorageUsage === true && isPreview
  const selectedOrders = body.selectedOrders || null // Array of {bpartnerId, monthYear}

  console.log('Organization ID from cookie:', organizationId)
  console.log('Create Report:', shouldCreateReport)
  console.log('Selected Orders:', selectedOrders)

  // Set preview flag in response
  data.preview = isPreview

  // Helper function to round to 2 decimal places
  const roundToTwo = (num: number): number => {
    return Math.round((num + Number.EPSILON) * 100) / 100
  }

  // Cache for country data
  const countryCache: any = {}

  // Helper function to fetch country data from shipment or order
  const fetchCountryData = async (orderId?: number, inoutId?: number) => {
    // Only fetch country data if explicitly requested
    if (!shouldFetchCountryData) {
      return { countryCode: null, countryName: null }
    }

    // Try order first if available
    if (orderId) {
      const cacheKey = `order_${orderId}`
      if (countryCache[cacheKey]) {
        return countryCache[cacheKey]
      }
      if (countryCache[cacheKey] === false) {
        return { countryCode: null, countryName: null }
      }

      try {
        const order: any = await event.context.fetch(
          `models/c_order/${orderId}`,
          'GET',
          token,
          null
        )
        if (order?.C_BPartner_Location_ID?.id) {
          const bpLocation: any = await event.context.fetch(
            `models/c_bpartner_location/${order.C_BPartner_Location_ID.id}`,
            'GET',
            token,
            null
          )
          if (bpLocation?.C_Location_ID?.id) {
            const location: any = await event.context.fetch(
              `models/c_location/${bpLocation.C_Location_ID.id}`,
              'GET',
              token,
              null
            )
            const result = {
              countryCode: location?.C_Country_ID?.CountryCode,
              countryName: location?.C_Country_ID?.Name
            }
            countryCache[cacheKey] = result
            return result
          }
        }
        countryCache[cacheKey] = false
      } catch (err: any) {
        if (!err?.statusCode || err.statusCode !== 404) {
          console.error(`Failed to fetch country from order ${orderId}:`, err)
        }
        countryCache[cacheKey] = false
      }
    }

    // Try shipment if order failed or not available
    if (inoutId) {
      const cacheKey = `inout_${inoutId}`
      if (countryCache[cacheKey]) {
        return countryCache[cacheKey]
      }
      if (countryCache[cacheKey] === false) {
        return { countryCode: null, countryName: null }
      }

      try {
        // Optimized query to minimize traffic - only get location data
        const inout: any = await event.context.fetch(
          `models/m_inout/${inoutId}?$select=C_BPartner_Location_ID&$expand=C_BPartner_Location_ID($select=C_Location_ID)`,
          'GET',
          token,
          null
        )

        // The C_Location_ID is nested inside C_BPartner_Location_ID and contains C_Country_ID
        const locationData = inout?.C_BPartner_Location_ID?.C_Location_ID
        const countryId = locationData?.C_Country_ID?.id

        if (countryId) {
          // Fetch only the country code and name
          const country: any = await event.context.fetch(
            `models/c_country/${countryId}?$select=CountryCode,Name`,
            'GET',
            token,
            null
          )
          const result = {
            countryCode: country?.CountryCode || null,
            countryName: country?.Name || null
          }
          countryCache[cacheKey] = result
          return result
        }

        countryCache[cacheKey] = false
      } catch (err: any) {
        if (!err?.statusCode || err.statusCode !== 404) {
          console.error(`Failed to fetch country from shipment ${inoutId}:`, err)
        }
        countryCache[cacheKey] = false
      }
    }

    return { countryCode: null, countryName: null }
  }

  // Build filter for fulfillment fee lines
  let filter = 'A_Processed eq false'

  if (body.partnerId) {
    filter += ` and C_BPartner_ID eq ${body.partnerId}`
  }

  if (body.rangeMode === true && body.monthFrom && body.yearFrom && body.monthTo && body.yearTo) {
    // Filter by shipping_date across a span of months. Normalize so the lower
    // bound is never after the upper bound, then use a half-open window:
    // [first day of start month, first day of the month AFTER the end month).
    // Grouping further down still splits this into one order per partner/month.
    const fromIdx = parseInt(body.yearFrom) * 12 + (parseInt(body.monthFrom) - 1)
    const toIdx = parseInt(body.yearTo) * 12 + (parseInt(body.monthTo) - 1)
    const startIdx = Math.min(fromIdx, toIdx)
    const endIdx = Math.max(fromIdx, toIdx)
    const startDate = new Date(Date.UTC(Math.floor(startIdx / 12), startIdx % 12, 1)).toISOString().split('T')[0]
    const endExclusive = new Date(Date.UTC(Math.floor(endIdx / 12), (endIdx % 12) + 1, 1)).toISOString().split('T')[0]
    filter += ` and shipping_date ge '${startDate}' and shipping_date lt '${endExclusive}'`
  } else if (body.month && body.year) {
    // Filter by shipping_date month/year. Exclusive next-month-start bound,
    // date-only literal — the old full-ISO value carried milliseconds AND a
    // duplicated time suffix ('...T23:59:59.000ZT23:59:59'), which iDempiere
    // rejected as "Unparseable date".
    const startDate = new Date(Date.UTC(body.year, body.month - 1, 1)).toISOString().split('T')[0]
    const endExclusive = new Date(Date.UTC(body.year, body.month, 1)).toISOString().split('T')[0]
    filter += ` and shipping_date ge '${startDate}' and shipping_date lt '${endExclusive}'`
  }

  // Fetch unprocessed fulfillment fee lines with expanded relationships (only first level)
  // Description field should be included by default
  const feeLines: any = await event.context.fetch(
    `models/cust_fulfillmentfeeline?$filter=${string.urlEncode(filter)}&$expand=C_BPartner_ID,M_Product_ID,M_InOut_ID,C_Order_ID`,
    'GET',
    token,
    null
  )

  if (!feeLines?.records || feeLines.records.length === 0) {
    return {
      success: [],
      errors: [],
      message: 'No unprocessed fulfillment fee lines found'
    }
  }

  // Debug: Log fulfill types
  // const fulfillTypes: any = {}
  // feeLines.records.forEach((line: any) => {
  //   const type = line.FulfillTypeAccounting
  //   fulfillTypes[type] = (fulfillTypes[type] || 0) + 1
  // })
  // data.debug.fulfillTypesBreakdown = fulfillTypes
  // data.debug.totalRecordsFetched = feeLines.records.length
  // data.debug.filter = filter

  // console.log('╔════════════════════════════════════════════════════════╗')
  // console.log('║        FULFILL TYPES BREAKDOWN - INITIAL FETCH        ║')
  // console.log('╠════════════════════════════════════════════════════════╣')
  // console.log('  Total records fetched:', feeLines.records.length)
  // console.log('  Filter used:', filter)
  // console.log('  Types breakdown:')
  // Object.entries(fulfillTypes).forEach(([type, count]) => {
  //   console.log(`    - ${type}: ${count}`)
  // })
  // console.log('  ')
  // console.log('  Sample records (first 5):')
  // feeLines.records.slice(0, 5).forEach((record: any) => {
  //   console.log(`    ID: ${record.id}, Type: ${record.FulfillTypeAccounting}, Partner: ${record.C_BPartner_ID?.id || 'NONE'}, Product: ${record.M_Product_ID?.id || 'NONE'}`)
  // })
  // console.log('╚════════════════════════════════════════════════════════╝')

  // Group records by C_BPartner_ID and shipping_date (month/year)
  const grouped: any = {}

  for (const line of feeLines.records) {
    // Get C_BPartner_ID directly from the fee line
    const bpartnerId = line.C_BPartner_ID?.id

    if (!bpartnerId) {
      data.errors.push({
        lineId: line.id,
        error: 'No business partner found for line'
      })
      continue
    }

    const shippingDate = new Date(line.shipping_date)
    const monthYear = `${shippingDate.getFullYear()}-${String(shippingDate.getMonth() + 1).padStart(2, '0')}`
    const groupKey = `${bpartnerId}_${monthYear}`

    if (!grouped[groupKey]) {
      grouped[groupKey] = {
        bpartnerId,
        monthYear,
        month: shippingDate.getMonth() + 1,
        year: shippingDate.getFullYear(),
        lines: []
      }
    }

    grouped[groupKey].lines.push(line)
  }

  // Process groups in smaller batches to avoid connection pool exhaustion
  let groupKeys = Object.keys(grouped)

  // Filter groups if selectedOrders is provided
  if (selectedOrders && selectedOrders.length > 0) {
    const selectedSet = new Set(
      selectedOrders.map((sel: any) => `${sel.bpartnerId}_${sel.monthYear}`)
    )
    groupKeys = groupKeys.filter(key => selectedSet.has(key))
    console.log(`Filtered to ${groupKeys.length} selected orders from ${Object.keys(grouped).length} total`)
  }

  const batchSize = 3 // Process 3 business partners at a time to avoid pool exhaustion
  let processedCount = 0

  console.log(`Processing ${groupKeys.length} business partner groups in batches of ${batchSize}`)

  for (let batchStart = 0; batchStart < groupKeys.length; batchStart += batchSize) {
    const batchKeys = groupKeys.slice(batchStart, batchStart + batchSize)
    console.log(`Processing batch ${Math.floor(batchStart / batchSize) + 1}/${Math.ceil(groupKeys.length / batchSize)} (${batchKeys.length} groups)`)

    // Process batch sequentially with small delay between groups
    for (const groupKey of batchKeys) {
      const group = grouped[groupKey]
      let orderPayload: any = null // Declare outside try block so it's accessible in catch

      try {
      // Fetch business partner details with expanded relations
      const bpartner: any = await event.context.fetch(
        `models/c_bpartner/${group.bpartnerId}?$expand=C_BPartner_Location,AD_User&$filter=${string.urlEncode('isFulfillmentCustomer eq true and IsActive eq true')}`,
        'GET',
        token,
        null
      )

      if (!bpartner || !bpartner.isFulfillmentCustomer || !bpartner.IsActive) {
        data.errors.push({
          bpartnerId: group.bpartnerId,
          error: 'Partner is not an active fulfillment customer'
        })
        continue
      }

      // Check if partner wants detailed invoice descriptions (qty x price)
      const showInvoiceDetails = bpartner.ShowFulfillmentInvoiceDetails === true

      // Get IDs from partner or use defaults
      const warehouseId = bpartner.M_Warehouse_ID?.id || 1000000
      const priceListId = bpartner.M_PriceList_ID?.id || 1000002
      const paymentTermId = bpartner.C_PaymentTerm_ID?.id || 1000000
      // Use tax exempt rate if partner is tax exempt
      const taxId = bpartner.IsTaxExempt === true ? 1000001 : 1000000

      // Group lines by FulfillTypeAccounting and M_Product_ID
      const fulfillmentLines: any = {}
      const returnLines: any = {}
      const spaceRentLines: any = {}
      const spaceRentFlatLines: any = {}
      const parcelLines: any = {}
      const subscriptionLines: any = {}
      const shippingFeeLines: any = {}
      // Request lines bill 1:1 per cust_fulfillmentfeeline entry (no aggregation),
      // preserving each ticket's Description verbatim on the order line.
      const requestLines: any[] = []

      for (const line of group.lines) {
        const productId = line.M_Product_ID?.id
        const productName = line.M_Product_ID?.Name || line.M_Product_ID?.Value || `Product ${productId}`

        if (!productId) {
          // console.log('⚠️ Skipping line without product ID:', line.id, 'Type:', line.FulfillTypeAccounting)
          continue
        }

        // console.log(`Processing line ${line.id}: Type="${line.FulfillTypeAccounting}", Product=${productId}`)

        if (line.FulfillTypeAccounting === 'fulfillment') {
          if (!fulfillmentLines[productId]) {
            fulfillmentLines[productId] = {
              productId,
              productName,
              totalAmt: 0,
              inoutIds: new Set(),
              details: []
            }
          }
          fulfillmentLines[productId].totalAmt += parseFloat(line.LineTotalAmt) || 0
          if (line.M_InOut_ID?.id) {
            fulfillmentLines[productId].inoutIds.add(line.M_InOut_ID.id)
          }

          let country = { countryCode: null, countryName: null }
          try {
            country = await fetchCountryData(line.C_Order_ID?.id, line.M_InOut_ID?.id)
          } catch (countryErr) {
            console.error(`Failed to fetch country for line ${line.id}:`, countryErr)
          }

          fulfillmentLines[productId].details.push({
            id: line.id,
            inoutId: line.M_InOut_ID?.id,
            inoutDocumentNo: line.M_InOut_ID?.DocumentNo,
            orderId: line.C_Order_ID?.id,
            orderDocumentNo: line.C_Order_ID?.DocumentNo,
            externalOrderId: line.C_Order_ID?.ExternalOrderId || null,
            countryCode: country.countryCode,
            countryName: country.countryName,
            shippingDate: line.shipping_date,
            qty: parseFloat(line.QtyInvoiced) || 0,
            price: parseFloat(line.PriceEntered) || 0,
            amount: parseFloat(line.LineTotalAmt) || 0,
            sourceDescription: line.Description || ''
          })
        } else if (line.FulfillTypeAccounting === 'return') {
          if (!returnLines[productId]) {
            returnLines[productId] = {
              productId,
              productName,
              totalAmt: 0,
              inoutIds: new Set(),
              details: []
            }
          }
          returnLines[productId].totalAmt += parseFloat(line.LineTotalAmt) || 0
          if (line.M_InOut_ID?.id) {
            returnLines[productId].inoutIds.add(line.M_InOut_ID.id)
          }

          let country = { countryCode: null, countryName: null }
          try {
            country = await fetchCountryData(line.C_Order_ID?.id, line.M_InOut_ID?.id)
          } catch (countryErr) {
            console.error(`Failed to fetch country for line ${line.id}:`, countryErr)
          }

          returnLines[productId].details.push({
            id: line.id,
            inoutId: line.M_InOut_ID?.id,
            inoutDocumentNo: line.M_InOut_ID?.DocumentNo,
            orderId: line.C_Order_ID?.id,
            orderDocumentNo: line.C_Order_ID?.DocumentNo,
            externalOrderId: line.C_Order_ID?.ExternalOrderId || null,
            countryCode: country.countryCode,
            countryName: country.countryName,
            shippingDate: line.shipping_date,
            qty: parseFloat(line.QtyInvoiced) || 0,
            price: parseFloat(line.PriceEntered) || 0,
            amount: parseFloat(line.LineTotalAmt) || 0
          })
        } else if (line.FulfillTypeAccounting === 'spacerentqm3') {
          if (!spaceRentLines[productId]) {
            spaceRentLines[productId] = {
              productId,
              productName,
              totalAmt: 0,
              qtyInvoiced: 0,
              details: []
            }
          }
          spaceRentLines[productId].totalAmt += parseFloat(line.LineTotalAmt) || 0
          spaceRentLines[productId].qtyInvoiced += parseFloat(line.QtyInvoiced) || 0
          spaceRentLines[productId].details.push({
            id: line.id,
            shippingDate: line.shipping_date,
            qty: parseFloat(line.QtyInvoiced) || 0,
            price: parseFloat(line.PriceEntered) || 0,
            amount: parseFloat(line.LineTotalAmt) || 0
          })
        } else if (line.FulfillTypeAccounting === 'spacerentflat') {
          if (!spaceRentFlatLines[productId]) {
            spaceRentFlatLines[productId] = {
              productId,
              productName,
              totalAmt: 0,
              qtyInvoiced: 0,
              priceEntered: 0,
              details: []
            }
          }
          spaceRentFlatLines[productId].totalAmt += parseFloat(line.LineTotalAmt) || 0
          spaceRentFlatLines[productId].qtyInvoiced += parseFloat(line.QtyInvoiced) || 0
          spaceRentFlatLines[productId].details.push({
            id: line.id,
            shippingDate: line.shipping_date,
            qty: parseFloat(line.QtyInvoiced) || 0,
            price: parseFloat(line.PriceEntered) || 0,
            amount: parseFloat(line.LineTotalAmt) || 0
          })
        } else if (line.FulfillTypeAccounting === 'parcel') {
          if (!parcelLines[productId]) {
            parcelLines[productId] = {
              productId,
              productName,
              totalAmt: 0,
              qtyInvoiced: 0,
              priceEntered: 0,
              details: []
            }
          }
          parcelLines[productId].totalAmt += parseFloat(line.LineTotalAmt) || 0
          parcelLines[productId].qtyInvoiced += parseFloat(line.QtyInvoiced) || 0
          parcelLines[productId].details.push({
            id: line.id,
            shippingDate: line.shipping_date,
            qty: parseFloat(line.QtyInvoiced) || 0,
            price: parseFloat(line.PriceEntered) || 0,
            amount: parseFloat(line.LineTotalAmt) || 0,
            description: line.Description || null // Include Description for validation
          })
        } else if (line.FulfillTypeAccounting === 'subscription') {
          if (!subscriptionLines[productId]) {
            subscriptionLines[productId] = {
              productId,
              productName,
              totalAmt: 0,
              qtyInvoiced: 0,
              details: []
            }
          }
          subscriptionLines[productId].totalAmt += parseFloat(line.LineTotalAmt) || 0
          subscriptionLines[productId].qtyInvoiced += parseFloat(line.QtyInvoiced) || 0
          subscriptionLines[productId].details.push({
            id: line.id,
            shippingDate: line.shipping_date,
            qty: parseFloat(line.QtyInvoiced) || 0,
            price: parseFloat(line.PriceEntered) || 0,
            amount: parseFloat(line.LineTotalAmt) || 0
          })
        } else if (line.FulfillTypeAccounting === 'shippingfee') {
          if (!shippingFeeLines[productId]) {
            shippingFeeLines[productId] = {
              productId,
              productName,
              totalAmt: 0,
              qtyInvoiced: 0,
              details: []
            }
          }
          shippingFeeLines[productId].totalAmt += parseFloat(line.LineTotalAmt) || 0
          shippingFeeLines[productId].qtyInvoiced += parseFloat(line.QtyInvoiced) || 0

          // Only fetch country and shipping fee details in preview mode
          let country = { countryCode: null, countryName: null }
          let shippingFees = null

          if (isPreview && shouldFetchCountryData) {
            try {
              country = await fetchCountryData(line.C_Order_ID?.id, line.M_InOut_ID?.id)
            } catch (countryErr) {
              console.error(`Failed to fetch country for line ${line.id}:`, countryErr)
            }

            // Get shipping fee details from M_InOut if available
            if (line.M_InOut_ID?.id) {
              try {
                const inout: any = await event.context.fetch(
                  `models/m_inout/${line.M_InOut_ID.id}?$select=IsCommissioned,IsCommissionedConfirmed,ack_commissioned_laravel,shipping_date,M_Paket_Type_ID,ext_freight_co2,ext_freight_energy,ext_freight_cost,ext_freight_weight,shipping_service_name,isDHLKleinPaket,isplentyinprogress,isAccountingExported,isExportedPaketAccounting,ext_freight_total,int_freight_total,int_freight_co2,int_freight_energy,int_freight_cost,dhl_product_id,dhl_product_description,isExternalFeeAccounted,isShippingFeeAccounted&$expand=M_Paket_Type_ID($select=Name)`,
                  'GET',
                  token,
                  null
                )
                shippingFees = {
                  // External fees
                  extTotal: inout?.ext_freight_total,
                  extCo2: inout?.ext_freight_co2,
                  extEnergy: inout?.ext_freight_energy,
                  extCost: inout?.ext_freight_cost,
                  extWeight: inout?.ext_freight_weight,
                  // Internal fees
                  intTotal: inout?.int_freight_total,
                  intCo2: inout?.int_freight_co2,
                  intEnergy: inout?.int_freight_energy,
                  intCost: inout?.int_freight_cost,
                  // Shipment details
                  serviceName: inout?.shipping_service_name,
                  productDescription: inout?.dhl_product_description,
                  dhlProductId: inout?.dhl_product_id,
                  paketType: inout?.M_Paket_Type_ID?.Name,
                  shippingDate: inout?.shipping_date,
                  // Flags
                  isCommissioned: inout?.IsCommissioned,
                  isCommissionedConfirmed: inout?.IsCommissionedConfirmed,
                  ackCommissionedLaravel: inout?.ack_commissioned_laravel,
                  isDHLKleinPaket: inout?.isDHLKleinPaket,
                  isPlentyInProgress: inout?.isplentyinprogress,
                  isAccountingExported: inout?.isAccountingExported,
                  isExportedPaketAccounting: inout?.isExportedPaketAccounting,
                  isExternalFeeAccounted: inout?.isExternalFeeAccounted,
                  isShippingFeeAccounted: inout?.isShippingFeeAccounted
                }
              } catch (err: any) {
                if (!err?.statusCode || err.statusCode !== 404) {
                  console.error(`Failed to fetch shipping fees from shipment ${line.M_InOut_ID.id}:`, err)
                }
              }
            }
          }

          shippingFeeLines[productId].details.push({
            id: line.id,
            orderId: line.C_Order_ID?.id,
            orderDocumentNo: line.C_Order_ID?.DocumentNo,
            externalOrderId: line.C_Order_ID?.ExternalOrderId || null,
            inoutId: line.M_InOut_ID?.id,
            inoutDocumentNo: line.M_InOut_ID?.DocumentNo,
            countryCode: country.countryCode,
            countryName: country.countryName,
            productName: line.M_Product_ID?.Name || productName,
            shippingDate: line.shipping_date,
            qty: parseFloat(line.QtyInvoiced) || 0,
            price: parseFloat(line.PriceEntered) || 0,
            amount: parseFloat(line.LineTotalAmt) || 0,
            shippingFees: shippingFees
          })
        } else if (line.FulfillTypeAccounting === 'request') {
          const qty = parseFloat(line.QtyEntered) || parseFloat(line.QtyInvoiced) || 1
          const price = parseFloat(line.PriceEntered) || 0
          const amount = parseFloat(line.LineTotalAmt) || 0
          requestLines.push({
            id: line.id,
            productId,
            productName,
            description: line.Description || t.request,
            qty,
            price,
            amount,
            shippingDate: line.shipping_date
          })
        }
      }

      // Debug: Log what we collected
      // const groupedSummary = {
      //   fulfillmentProducts: Object.keys(fulfillmentLines).length,
      //   returnProducts: Object.keys(returnLines).length,
      //   spaceRentProducts: Object.keys(spaceRentLines).length,
      //   spaceRentFlatProducts: Object.keys(spaceRentFlatLines).length,
      //   parcelProducts: Object.keys(parcelLines).length,
      //   subscriptionProducts: Object.keys(subscriptionLines).length,
      //   shippingFeeProducts: Object.keys(shippingFeeLines).length,
      //   shippingFeeDetails: shippingFeeLines
      // }
      // if (!data.debug.groupedByPartner) data.debug.groupedByPartner = {}
      // data.debug.groupedByPartner[`${group.bpartnerId}_${group.monthYear}`] = groupedSummary

      // console.log('=== GROUPED LINES SUMMARY ===')
      // console.log(JSON.stringify(groupedSummary, null, 2))
      // console.log('=============================')

      // Build order lines array (store product IDs as integers for the payload)
      const orderLines: any[] = []
      let lineNo = 10

      // Add fulfillment positions
      for (const productId in fulfillmentLines) {
        const lineData = fulfillmentLines[productId]
        const orderCount = lineData.inoutIds.size
        const positionCount = lineData.details.length
        const qty = positionCount
        const unitPrice = roundToTwo(qty > 0 ? lineData.totalAmt / qty : lineData.totalAmt)

        orderLines.push({
          productId: parseInt(productId),
          QtyOrdered: 1,
          QtyEntered: 1,
          PriceEntered: roundToTwo(lineData.totalAmt),
          PriceActual: roundToTwo(lineData.totalAmt),
          LineNetAmt: roundToTwo(lineData.totalAmt),
          Description: `${t.fulfillment}: ${orderCount} ${orderCount > 1 ? t.orders : t.order} ${t.and} ${positionCount} ${positionCount > 1 ? t.positions : t.position}${showInvoiceDetails ? ` (${qty} x ${unitPrice})` : ''}`,
          Line: lineNo
        })
        lineNo += 10
      }

      // Add return positions
      for (const productId in returnLines) {
        const lineData = returnLines[productId]
        const orderCount = lineData.inoutIds.size
        const positionCount = lineData.details.length
        const qty = positionCount
        const unitPrice = roundToTwo(qty > 0 ? lineData.totalAmt / qty : lineData.totalAmt)

        orderLines.push({
          productId: parseInt(productId),
          QtyOrdered: 1,
          QtyEntered: 1,
          PriceEntered: roundToTwo(lineData.totalAmt),
          PriceActual: roundToTwo(lineData.totalAmt),
          LineNetAmt: roundToTwo(lineData.totalAmt),
          Description: `${t.return}: ${orderCount} ${orderCount > 1 ? t.orders : t.order} ${t.and} ${positionCount} ${positionCount > 1 ? t.positions : t.position}${showInvoiceDetails ? ` (${qty} x ${unitPrice})` : ''}`,
          Line: lineNo
        })
        lineNo += 10
      }

      // Add space rent positions
      for (const productId in spaceRentLines) {
        const lineData = spaceRentLines[productId]
        const qty = lineData.qtyInvoiced
        const unitPrice = roundToTwo(qty > 0 ? lineData.totalAmt / qty : lineData.totalAmt)

        orderLines.push({
          productId: parseInt(productId),
          QtyOrdered: 1,
          QtyEntered: 1,
          PriceEntered: roundToTwo(lineData.totalAmt),
          PriceActual: roundToTwo(lineData.totalAmt),
          LineNetAmt: roundToTwo(lineData.totalAmt),
          Description: `${t.warehouse_rent}: ${qty.toFixed(2)} qm³${showInvoiceDetails ? ` (${qty.toFixed(2)} x ${unitPrice})` : ''}`,
          Line: lineNo
        })
        lineNo += 10
      }

      // Add flat space rent positions
      for (const productId in spaceRentFlatLines) {
        const lineData = spaceRentFlatLines[productId]
        const qty = lineData.qtyInvoiced
        const unitPrice = roundToTwo(qty > 0 ? lineData.totalAmt / qty : lineData.totalAmt)

        orderLines.push({
          productId: parseInt(productId),
          QtyOrdered: 1,
          QtyEntered: 1,
          PriceEntered: roundToTwo(lineData.totalAmt),
          PriceActual: roundToTwo(lineData.totalAmt),
          LineNetAmt: roundToTwo(lineData.totalAmt),
          Description: `${t.warehouse_rent_flat}: ${qty.toFixed(2)} ${qty !== 1 ? t.units : t.unit}${showInvoiceDetails ? ` (${qty.toFixed(2)} x ${unitPrice})` : ''}`,
          Line: lineNo
        })
        lineNo += 10
      }

      // Add parcel positions
      for (const productId in parcelLines) {
        const lineData = parcelLines[productId]
        const qty = lineData.qtyInvoiced
        const unitPrice = roundToTwo(qty > 0 ? lineData.totalAmt / qty : lineData.totalAmt)

        orderLines.push({
          productId: parseInt(productId),
          QtyOrdered: 1,
          QtyEntered: 1,
          PriceEntered: roundToTwo(lineData.totalAmt),
          PriceActual: roundToTwo(lineData.totalAmt),
          LineNetAmt: roundToTwo(lineData.totalAmt),
          Description: `${t.parcel}: ${qty.toFixed(0)} ${qty !== 1 ? t.parcels : t.parcel}${showInvoiceDetails ? ` (${qty.toFixed(0)} x ${unitPrice})` : ''}`,
          Line: lineNo
        })
        lineNo += 10
      }

      // Add subscription positions
      for (const productId in subscriptionLines) {
        const lineData = subscriptionLines[productId]
        const qty = lineData.qtyInvoiced
        const unitPrice = roundToTwo(qty > 0 ? lineData.totalAmt / qty : lineData.totalAmt)

        orderLines.push({
          productId: parseInt(productId),
          QtyOrdered: 1,
          QtyEntered: 1,
          PriceEntered: roundToTwo(lineData.totalAmt),
          PriceActual: roundToTwo(lineData.totalAmt),
          LineNetAmt: roundToTwo(lineData.totalAmt),
          Description: `${t.subscription}: ${qty.toFixed(2)} ${qty !== 1 ? t.units : t.unit}${showInvoiceDetails ? ` (${qty.toFixed(2)} x ${unitPrice})` : ''}`,
          Line: lineNo
        })
        lineNo += 10
      }

      // Add shipping fee positions
      for (const productId in shippingFeeLines) {
        const lineData = shippingFeeLines[productId]
        const qty = lineData.qtyInvoiced
        const unitPrice = roundToTwo(qty > 0 ? lineData.totalAmt / qty : lineData.totalAmt)

        orderLines.push({
          productId: parseInt(productId),
          QtyOrdered: 1,
          QtyEntered: 1,
          PriceEntered: roundToTwo(lineData.totalAmt),
          PriceActual: roundToTwo(lineData.totalAmt),
          LineNetAmt: roundToTwo(lineData.totalAmt),
          Description: `${t.shipping_fee}: ${qty.toFixed(2)} ${qty !== 1 ? t.units : t.unit}${showInvoiceDetails ? ` (${qty.toFixed(2)} x ${unitPrice})` : ''}`,
          Line: lineNo
        })
        lineNo += 10
      }

      // Add request positions — one order line per cust_fulfillmentfeeline entry
      for (const reqLine of requestLines) {
        orderLines.push({
          productId: parseInt(reqLine.productId),
          QtyOrdered: reqLine.qty,
          QtyEntered: reqLine.qty,
          PriceEntered: roundToTwo(reqLine.price),
          PriceActual: roundToTwo(reqLine.price),
          LineNetAmt: roundToTwo(reqLine.amount),
          Description: reqLine.description,
          Line: lineNo
        })
        lineNo += 10
      }

      if (orderLines.length === 0) {
        data.errors.push({
          bpartnerId: group.bpartnerId,
          error: 'No valid order lines generated'
        })
        continue
      }

      // Calculate totals
      const totalLines = orderLines.reduce((sum, line) => sum + (line.LineNetAmt || 0), 0)

      // Get month name using translations
      const monthKeys = ['january', 'february', 'march', 'april', 'may', 'june',
        'july', 'august', 'september', 'october', 'november', 'december']
      const monthName = t[monthKeys[group.month - 1]]

      // Calculate last day of the month for PeriodPerformanceDate
      const lastDayOfMonth = new Date(group.year, group.month, 0).toISOString().split('T')[0]

      // Perform paket-shipment validation if requested
      let validationResults: any = null
      if (shouldValidatePaketShipment && isPreview) {
        validationResults = await performPaketShipmentValidation(
          event,
          token,
          organizationId,
          parcelLines,
          group.lines
        )
      }

      // In preview mode, skip order creation
      if (isPreview) {
        // Build preview order lines with product names
        const previewOrderLines: any[] = []

        for (const productId in fulfillmentLines) {
          const lineData = fulfillmentLines[productId]
          const orderCount = lineData.inoutIds.size
          const positionCount = lineData.details.length
          previewOrderLines.push({
            productId: parseInt(productId),
            productName: lineData.productName,
            description: `Fulfillment: ${orderCount} Order${orderCount > 1 ? 's' : ''} and ${positionCount} position${positionCount > 1 ? 's' : ''}`,
            qtyOrdered: 1,
            priceActual: roundToTwo(lineData.totalAmt),
            lineNetAmt: roundToTwo(lineData.totalAmt),
            type: 'fulfillment',
            details: lineData.details
          })
        }

        for (const productId in returnLines) {
          const lineData = returnLines[productId]
          const orderCount = lineData.inoutIds.size
          const positionCount = lineData.details.length
          previewOrderLines.push({
            productId: parseInt(productId),
            productName: lineData.productName,
            description: `Return: ${orderCount} Order${orderCount > 1 ? 's' : ''} and ${positionCount} position${positionCount > 1 ? 's' : ''}`,
            qtyOrdered: 1,
            priceActual: roundToTwo(lineData.totalAmt),
            lineNetAmt: roundToTwo(lineData.totalAmt),
            type: 'return',
            details: lineData.details
          })
        }

        for (const productId in spaceRentLines) {
          const lineData = spaceRentLines[productId]
          previewOrderLines.push({
            productId: parseInt(productId),
            productName: lineData.productName,
            description: `Warehouse Rent: ${lineData.qtyInvoiced.toFixed(2)} qm³`,
            qtyOrdered: 1,
            priceActual: roundToTwo(lineData.totalAmt),
            lineNetAmt: roundToTwo(lineData.totalAmt),
            type: 'spacerentqm3',
            details: lineData.details
          })
        }

        for (const productId in spaceRentFlatLines) {
          const lineData = spaceRentFlatLines[productId]
          const flatQty = lineData.qtyInvoiced
          const avgPrice = roundToTwo(flatQty > 0 ? lineData.totalAmt / flatQty : 0)
          previewOrderLines.push({
            productId: parseInt(productId),
            productName: lineData.productName,
            description: `Warehouse Rent (Flat): ${flatQty.toFixed(2)} units`,
            qtyOrdered: flatQty,
            priceActual: avgPrice,
            lineNetAmt: roundToTwo(lineData.totalAmt),
            type: 'spacerentflat',
            details: lineData.details
          })
        }

        for (const productId in parcelLines) {
          const lineData = parcelLines[productId]
          const parcelQty = lineData.qtyInvoiced
          const avgPrice = roundToTwo(parcelQty > 0 ? lineData.totalAmt / parcelQty : 0)
          previewOrderLines.push({
            productId: parseInt(productId),
            productName: lineData.productName,
            description: `Parcel: ${parcelQty.toFixed(0)} parcel${parcelQty !== 1 ? 's' : ''}`,
            qtyOrdered: parcelQty,
            priceActual: avgPrice,
            lineNetAmt: roundToTwo(lineData.totalAmt),
            type: 'parcel',
            details: lineData.details
          })
        }

        for (const productId in subscriptionLines) {
          const lineData = subscriptionLines[productId]
          const subscriptionQty = lineData.qtyInvoiced
          const avgPrice = roundToTwo(subscriptionQty > 0 ? lineData.totalAmt / subscriptionQty : 0)
          previewOrderLines.push({
            productId: parseInt(productId),
            productName: lineData.productName,
            description: `Subscription: ${subscriptionQty.toFixed(2)} unit${subscriptionQty !== 1 ? 's' : ''}`,
            qtyOrdered: subscriptionQty,
            priceActual: avgPrice,
            lineNetAmt: roundToTwo(lineData.totalAmt),
            type: 'subscription',
            details: lineData.details
          })
        }

        for (const productId in shippingFeeLines) {
          const lineData = shippingFeeLines[productId]
          const shippingFeeQty = lineData.qtyInvoiced
          const avgPrice = roundToTwo(shippingFeeQty > 0 ? lineData.totalAmt / shippingFeeQty : 0)
          previewOrderLines.push({
            productId: parseInt(productId),
            productName: lineData.productName,
            description: `Shipping Fee: ${shippingFeeQty.toFixed(2)} unit${shippingFeeQty !== 1 ? 's' : ''}`,
            qtyOrdered: shippingFeeQty,
            priceActual: avgPrice,
            lineNetAmt: roundToTwo(lineData.totalAmt),
            type: 'shippingfee',
            details: lineData.details
          })
        }

        for (const reqLine of requestLines) {
          previewOrderLines.push({
            productId: parseInt(reqLine.productId),
            productName: reqLine.productName,
            description: reqLine.description,
            qtyOrdered: reqLine.qty,
            priceActual: roundToTwo(reqLine.price),
            lineNetAmt: roundToTwo(reqLine.amount),
            type: 'request',
            details: [{
              id: reqLine.id,
              shippingDate: reqLine.shippingDate,
              qty: reqLine.qty,
              price: reqLine.price,
              amount: reqLine.amount
            }]
          })
        }

        data.success.push({
          bpartnerId: group.bpartnerId,
          bpartnerName: bpartner.Name,
          monthYear: group.monthYear,
          linesProcessed: group.lines.length,
          totalAmount: totalLines,
          orderLines: previewOrderLines,
          validationResults: validationResults
        })
        continue
      }

      // Get first location for billing and shipping
      const bpLocation = bpartner.C_BPartner_Location?.[0]
      const bpUser = bpartner.AD_User?.[0]

      // Validate that partner has a location
      if (!bpLocation?.id) {
        data.errors.push({
          bpartnerId: group.bpartnerId,
          error: `Business Partner has no Ship To Address - ${bpartner.Name || group.bpartnerId}`,
          details: { partnerId: group.bpartnerId, partnerName: bpartner.Name }
        })
        continue
      }

      // Create the sales order with all integer IDs (no object notation)
      orderPayload = {
        AD_Org_ID: organizationId,
        IsSOTrx: true,
        // Generated invoice-orders are bookkeeping, not fulfilment — flag accordingly so the
        // reminder popup triggers when staff open them.
        isFulfillmentOrder: false,
        C_DocTypeTarget_ID: 1000032, // Standard Order
        DateOrdered: new Date().toISOString().split('T')[0],
        DatePromised: new Date().toISOString().split('T')[0],
        DateAcct: new Date().toISOString().split('T')[0],
        PeriodPerformanceDate: lastDayOfMonth, // Set to last day of the month
        Description: `${t.fulfillment_invoice} ${monthName} ${group.year}`,
        SalesRep_ID: 1000004, // Default sales rep
        C_BPartner_ID: group.bpartnerId,
        C_PaymentTerm_ID: paymentTermId, // From partner or default "Immediate"
        C_Currency_ID: 102, // EUR
        M_PriceList_ID: priceListId, // From partner or default
        M_Warehouse_ID: warehouseId, // From partner or default
        PaymentRule: 'P', // On Credit
        DeliveryRule: 'F', // Force
        DeliveryViaRule: 'D', // Delivery
        FreightCostRule: 'I', // Freight included
        InvoiceRule: 'D', // After Delivery
        PriorityRule: '5', // Medium
        IsTaxIncluded: false,
        c_orderline: orderLines.map(line => ({
          Line: line.Line,
          AD_Org_ID: organizationId,
          M_Product_ID: line.productId,
          C_UOM_ID: 100, // Each
          QtyEntered: line.QtyEntered,
          QtyOrdered: line.QtyOrdered,
          PriceEntered: line.PriceEntered,
          PriceActual: line.PriceActual,
          C_Tax_ID: taxId, // Use tax exempt rate (1000001) if partner is tax exempt, otherwise 19% Mwst. (1000000)
          M_Warehouse_ID: warehouseId,
          ...(line.Description ? { Description: line.Description } : {})
        }))
      }

      // Add business partner location if available
      if (bpLocation?.id) {
        orderPayload.C_BPartner_Location_ID = bpLocation.id
        orderPayload.Bill_Location_ID = bpLocation.id
      }

      // Add business partner user if available
      if (bpUser?.id) {
        orderPayload.AD_User_ID = bpUser.id
        orderPayload.Bill_User_ID = bpUser.id
      }

      // Add bill partner (same as main partner)
      orderPayload.Bill_BPartner_ID = group.bpartnerId

      // Log the full request payload for debugging
      console.log('═══════════════════════════════════════════════════')
      console.log('CREATE ORDER REQUEST PAYLOAD')
      console.log('═══════════════════════════════════════════════════')
      console.log('Partner ID:', group.bpartnerId)
      console.log('Month/Year:', group.monthYear)
      console.log('Full Payload:', JSON.stringify(orderPayload, null, 2))
      console.log('═══════════════════════════════════════════════════')

      const order: any = await fetchHelper(event, 'models/c_order', 'POST', token, orderPayload)

      console.log('ORDER CREATION RESPONSE:', JSON.stringify(order, null, 2))

      if (order && order.id) {
        // Mark all lines as processed using SQL batch update (only in non-preview mode)
        if (!isPreview) {
          try {
            const lineIds = group.lines.map((line: any) => line.id)

            // Use batch SQL update endpoint - set a_processed='Y' and c_order_refinv_id
            await $fetch('/api/fulfillment/batch-process-lines', {
              method: 'POST',
              body: {
                lineIds,
                orderId: order.id
              }
            })

            console.log(`Batch updated ${group.lines.length} fee lines for order ${order.id}, linked to c_order_refinv_id`)
          } catch (updateError: any) {
            console.error('Batch update error:', updateError)
            data.errors.push({
              bpartnerId: group.bpartnerId,
              error: `Order created (ID: ${order.id}) but failed to mark lines as processed`,
              details: {
                orderId: order.id,
                orderDocumentNo: order.DocumentNo,
                totalLines: group.lines.length,
                error: updateError.message || String(updateError)
              }
            })
          }
        }

        // Build order lines with product names for response
        const responseOrderLines: any[] = []

        for (const productId in fulfillmentLines) {
          const lineData = fulfillmentLines[productId]
          const orderCount = lineData.inoutIds.size
          const positionCount = lineData.details.length
          responseOrderLines.push({
            productId: parseInt(productId),
            productName: lineData.productName,
            description: `Fulfillment: ${orderCount} Order${orderCount > 1 ? 's' : ''} and ${positionCount} position${positionCount > 1 ? 's' : ''}`,
            qtyOrdered: 1,
            priceActual: lineData.totalAmt,
            lineNetAmt: lineData.totalAmt
          })
        }

        for (const productId in returnLines) {
          const lineData = returnLines[productId]
          const orderCount = lineData.inoutIds.size
          const positionCount = lineData.details.length
          responseOrderLines.push({
            productId: parseInt(productId),
            productName: lineData.productName,
            description: `Return: ${orderCount} Order${orderCount > 1 ? 's' : ''} and ${positionCount} position${positionCount > 1 ? 's' : ''}`,
            qtyOrdered: 1,
            priceActual: lineData.totalAmt,
            lineNetAmt: lineData.totalAmt
          })
        }

        for (const productId in spaceRentLines) {
          const lineData = spaceRentLines[productId]
          responseOrderLines.push({
            productId: parseInt(productId),
            productName: lineData.productName,
            description: `Warehouse Rent: ${lineData.qtyInvoiced.toFixed(2)} qm³`,
            qtyOrdered: 1,
            priceActual: lineData.totalAmt,
            lineNetAmt: lineData.totalAmt
          })
        }

        for (const productId in spaceRentFlatLines) {
          const lineData = spaceRentFlatLines[productId]
          const flatQty = lineData.qtyInvoiced
          const avgPrice = flatQty > 0 ? lineData.totalAmt / flatQty : 0
          responseOrderLines.push({
            productId: parseInt(productId),
            productName: lineData.productName,
            description: `Warehouse Rent (Flat): ${flatQty.toFixed(2)} units`,
            qtyOrdered: flatQty,
            priceActual: avgPrice,
            lineNetAmt: lineData.totalAmt
          })
        }

        for (const productId in parcelLines) {
          const lineData = parcelLines[productId]
          const parcelQty = lineData.qtyInvoiced
          const avgPrice = parcelQty > 0 ? lineData.totalAmt / parcelQty : 0
          responseOrderLines.push({
            productId: parseInt(productId),
            productName: lineData.productName,
            description: `Parcel: ${parcelQty.toFixed(0)} parcel${parcelQty !== 1 ? 's' : ''}`,
            qtyOrdered: parcelQty,
            priceActual: avgPrice,
            lineNetAmt: lineData.totalAmt
          })
        }

        for (const productId in subscriptionLines) {
          const lineData = subscriptionLines[productId]
          const subscriptionQty = lineData.qtyInvoiced
          const avgPrice = subscriptionQty > 0 ? lineData.totalAmt / subscriptionQty : 0
          responseOrderLines.push({
            productId: parseInt(productId),
            productName: lineData.productName,
            description: `Subscription: ${subscriptionQty.toFixed(2)} unit${subscriptionQty !== 1 ? 's' : ''}`,
            qtyOrdered: subscriptionQty,
            priceActual: avgPrice,
            lineNetAmt: lineData.totalAmt
          })
        }

        for (const productId in shippingFeeLines) {
          const lineData = shippingFeeLines[productId]
          const shippingFeeQty = lineData.qtyInvoiced
          const avgPrice = shippingFeeQty > 0 ? lineData.totalAmt / shippingFeeQty : 0
          responseOrderLines.push({
            productId: parseInt(productId),
            productName: lineData.productName,
            description: `Shipping Fee: ${shippingFeeQty.toFixed(2)} unit${shippingFeeQty !== 1 ? 's' : ''}`,
            qtyOrdered: shippingFeeQty,
            priceActual: avgPrice,
            lineNetAmt: lineData.totalAmt
          })
        }

        for (const reqLine of requestLines) {
          responseOrderLines.push({
            productId: parseInt(reqLine.productId),
            productName: reqLine.productName,
            description: reqLine.description,
            qtyOrdered: reqLine.qty,
            priceActual: reqLine.price,
            lineNetAmt: reqLine.amount
          })
        }

        // Generate PDF report if requested
        if (shouldCreateReport && order.id && order.DocumentNo) {
          try {
            console.log(`Generating PDF report for order ${order.DocumentNo}...`)

            // Prepare report data with full details
            const reportOrderLines: any[] = []

            for (const productId in fulfillmentLines) {
              const lineData = fulfillmentLines[productId]
              reportOrderLines.push({
                productId: parseInt(productId),
                productName: lineData.productName,
                description: `Fulfillment: ${lineData.inoutIds.size} shipment${lineData.inoutIds.size > 1 ? 's' : ''}`,
                qtyOrdered: 1,
                priceActual: lineData.totalAmt,
                lineNetAmt: lineData.totalAmt,
                type: 'fulfillment',
                details: lineData.details
              })
            }

            for (const productId in returnLines) {
              const lineData = returnLines[productId]
              reportOrderLines.push({
                productId: parseInt(productId),
                productName: lineData.productName,
                description: `Return: ${lineData.inoutIds.size} return${lineData.inoutIds.size > 1 ? 's' : ''}`,
                qtyOrdered: 1,
                priceActual: lineData.totalAmt,
                lineNetAmt: lineData.totalAmt,
                type: 'return',
                details: lineData.details
              })
            }

            for (const productId in spaceRentLines) {
              const lineData = spaceRentLines[productId]
              reportOrderLines.push({
                productId: parseInt(productId),
                productName: lineData.productName,
                description: `Warehouse Rent: ${lineData.qtyInvoiced.toFixed(2)} qm³`,
                qtyOrdered: 1,
                priceActual: lineData.totalAmt,
                lineNetAmt: lineData.totalAmt,
                type: 'spacerentqm3',
                details: lineData.details
              })
            }

            for (const productId in spaceRentFlatLines) {
              const lineData = spaceRentFlatLines[productId]
              const flatQty = lineData.qtyInvoiced
              const avgPrice = flatQty > 0 ? lineData.totalAmt / flatQty : 0
              reportOrderLines.push({
                productId: parseInt(productId),
                productName: lineData.productName,
                description: `Warehouse Rent (Flat): ${flatQty.toFixed(2)} units`,
                qtyOrdered: flatQty,
                priceActual: avgPrice,
                lineNetAmt: lineData.totalAmt,
                type: 'spacerentflat',
                details: lineData.details
              })
            }

            for (const productId in parcelLines) {
              const lineData = parcelLines[productId]
              const parcelQty = lineData.qtyInvoiced
              const avgPrice = parcelQty > 0 ? lineData.totalAmt / parcelQty : 0
              reportOrderLines.push({
                productId: parseInt(productId),
                productName: lineData.productName,
                description: `Parcel: ${parcelQty.toFixed(0)} parcel${parcelQty !== 1 ? 's' : ''}`,
                qtyOrdered: parcelQty,
                priceActual: avgPrice,
                lineNetAmt: lineData.totalAmt,
                type: 'parcel',
                details: lineData.details
              })
            }

            for (const productId in subscriptionLines) {
              const lineData = subscriptionLines[productId]
              const subscriptionQty = lineData.qtyInvoiced
              const avgPrice = subscriptionQty > 0 ? lineData.totalAmt / subscriptionQty : 0
              reportOrderLines.push({
                productId: parseInt(productId),
                productName: lineData.productName,
                description: `Subscription: ${subscriptionQty.toFixed(2)} unit${subscriptionQty !== 1 ? 's' : ''}`,
                qtyOrdered: subscriptionQty,
                priceActual: avgPrice,
                lineNetAmt: lineData.totalAmt,
                type: 'subscription',
                details: lineData.details
              })
            }

            for (const productId in shippingFeeLines) {
              const lineData = shippingFeeLines[productId]
              const shippingFeeQty = lineData.qtyInvoiced
              const avgPrice = shippingFeeQty > 0 ? lineData.totalAmt / shippingFeeQty : 0
              reportOrderLines.push({
                productId: parseInt(productId),
                productName: lineData.productName,
                description: `Shipping Fee: ${shippingFeeQty.toFixed(2)} unit${shippingFeeQty !== 1 ? 's' : ''}`,
                qtyOrdered: shippingFeeQty,
                priceActual: avgPrice,
                lineNetAmt: lineData.totalAmt,
                type: 'shippingfee',
                details: lineData.details
              })
            }

            for (const reqLine of requestLines) {
              reportOrderLines.push({
                productId: parseInt(reqLine.productId),
                productName: reqLine.productName,
                description: reqLine.description,
                qtyOrdered: reqLine.qty,
                priceActual: reqLine.price,
                lineNetAmt: reqLine.amount,
                type: 'request',
                details: [{
                  id: reqLine.id,
                  shippingDate: reqLine.shippingDate,
                  qty: reqLine.qty,
                  price: reqLine.price,
                  amount: reqLine.amount
                }]
              })
            }

            // Query storage usage via API if customer has isAccountingWarehouseVolume = true
            let storageUsageData: any[] = []
            const hasSpaceRentQm3 = reportOrderLines.some(line => line.type === 'spacerentqm3')
            let storageUsageDebug: any = null

            if (hasSpaceRentQm3 && bpartner.isAccountingWarehouseVolume === true) {
              try {
                const [year, month] = group.monthYear.split('-')
                const startDate = `${year}-${month.padStart(2, '0')}-01`
                const endDate = new Date(parseInt(year), parseInt(month), 0).toISOString().split('T')[0]

                // IMPORTANT: Resolve BPartner strictly from the created Sales Order (no fallback)
                // - Non-preview (real order): use only order.C_BPartner_ID.id; if missing, SKIP fetching storage usage
                // - Preview: no real order exists, so use the group's BPartner ONLY for preview/debug purposes
                let usedBPartnerId: number | null = null
                let usedBpSource: 'order_bpartner' | 'preview_group_bpartner' | 'unknown' = 'unknown'

                if (!isPreview) {
                  usedBPartnerId = order?.C_BPartner_ID?.id ?? null
                  usedBpSource = 'order_bpartner'
                } else {
                  usedBPartnerId = group.bpartnerId ?? null
                  usedBpSource = 'preview_group_bpartner'
                }

                let customerOrgId: number | null = null

                try {
                  if (usedBPartnerId) {
                    // Query ad_org endpoint to get the organization ID for this business partner
                    const orgFilter = encodeURIComponent(`C_BPartner_ID eq ${usedBPartnerId}`)
                    const orgSelect = encodeURIComponent('ad_org_id')
                    const orgUrl = `models/ad_org?$filter=${orgFilter}&$select=${orgSelect}`

                    const orgResult: any = await event.context.fetch(
                      orgUrl,
                      'GET',
                      token,
                      null
                    )

                    const orgRecords: any[] = Array.isArray(orgResult?.records)
                      ? orgResult.records
                      : Array.isArray(orgResult)
                        ? orgResult
                        : []

                    if (orgRecords.length > 0) {
                      customerOrgId = orgRecords[0].id ?? orgRecords[0].ad_org_id ?? orgRecords[0].AD_Org_ID ?? null
                    }
                  }
                } catch (bpErr) {
                  console.error(`Failed to resolve ad_org_id for BPartner ${usedBPartnerId}:`, bpErr)
                }

                if (!customerOrgId) {
                  console.warn(`Skipping storage usage fetch: ${usedBpSource === 'order_bpartner' ? 'Order' : 'Preview group'} BPartner ${usedBPartnerId} has no AD_Org_ID`)
                  if (debugStorageUsage) {
                    storageUsageDebug = {
                      enabled: true,
                      gatedBy: 'missing_customer_org',
                      hasSpaceRentQm3,
                      isAccountingWarehouseVolume: bpartner.isAccountingWarehouseVolume === true,
                      usedBPartnerId,
                      customerOrgId: null,
                      customerOrgIdUsedSource: usedBpSource,
                      startDate,
                      endDate
                    }
                  }
                } else {
                  // Build OData query to match requested behavior
                  // Example: /models/cust_storage_usage?$filter=ad_org_id eq 1000005 AND DateAcct ge 2025-10-01 AND DateAcct le 2025-10-31
                  const filter = encodeURIComponent(`ad_org_id eq ${customerOrgId} AND DateAcct ge ${startDate} AND DateAcct le ${endDate}`)
                  const select = encodeURIComponent('CUST_Storage_Usage_ID,DateAcct,Volume,Qty,ProductValue,Product_Name,Volume_Single,Volume_Single_Price,Volume_Price')
                  const orderby = encodeURIComponent('DateAcct,Product_Name')

                  const url = `models/cust_storage_usage?$select=${select}&$filter=${filter}&$orderby=${orderby}`

                  const apiResult: any = await event.context.fetch(
                    url,
                    'GET',
                    token,
                    null
                  )

                  const records: any[] = Array.isArray(apiResult?.records)
                    ? apiResult.records
                    : Array.isArray(apiResult)
                      ? apiResult
                      : []

                  // Normalize field names expected by the PDF generator (lowercase, underscores)
                  storageUsageData = records.map((r: any) => ({
                    cust_storage_usage_id: r.CUST_Storage_Usage_ID ?? r.cust_storage_usage_id ?? r.id,
                    dateacct: r.DateAcct ?? r.dateacct,
                    volume: r.Volume ?? r.volume ?? 0,
                    qty: r.Qty ?? r.qty ?? 0,
                    productvalue: r.ProductValue ?? r.productvalue ?? '',
                    product_name: r.Product_Name ?? r.product_name ?? '',
                    volume_single: r.Volume_Single ?? r.volume_single ?? 0,
                    volume_single_price: r.Volume_Single_Price ?? r.volume_single_price ?? 0,
                    volume_price: r.Volume_Price ?? r.volume_price ?? 0
                  }))

                  console.log(`Fetched ${storageUsageData.length} storage usage records (API) for org ${customerOrgId} (used BP ${usedBPartnerId})`)

                  if (debugStorageUsage) {
                    storageUsageDebug = {
                      enabled: true,
                      hasSpaceRentQm3,
                      isAccountingWarehouseVolume: bpartner.isAccountingWarehouseVolume === true,
                      usedBPartnerId,
                      customerOrgId,
                      customerOrgIdUsedSource: usedBpSource,
                      startDate,
                      endDate,
                      requestUrl: url,
                      fetchedCount: storageUsageData.length,
                      sample: storageUsageData.slice(0, 3)
                    }
                  }
                }
              } catch (err) {
                console.error('Error fetching storage usage data via API:', err)
                if (debugStorageUsage) {
                  storageUsageDebug = {
                    enabled: true,
                    hasSpaceRentQm3,
                    isAccountingWarehouseVolume: bpartner.isAccountingWarehouseVolume === true,
                    // Keep any previously set info if available
                    error: err?.message || String(err)
                  }
                }
              }
            } else if (debugStorageUsage) {
              // Explain why we didn't fetch
              storageUsageDebug = {
                enabled: true,
                hasSpaceRentQm3,
                isAccountingWarehouseVolume: bpartner.isAccountingWarehouseVolume === true,
                skippedReason: !hasSpaceRentQm3
                  ? 'no_spacerentqm3_lines'
                  : 'customer_flag_isAccountingWarehouseVolume_is_false'
              }
            }

            const reportData = {
              orderId: order.id,
              documentNo: order.DocumentNo,
              bpartnerId: group.bpartnerId,
              bpartnerName: bpartner.Name,
              monthYear: group.monthYear,
              totalAmount: totalLines,
              orderLines: reportOrderLines,
              storageUsage: storageUsageData
            }

            // Call the report generation API
            const reportResult = await $fetch('/api/fulfillment/generate-fee-report', {
              method: 'POST',
              body: { reportData }
            })

            console.log('Report result:', reportResult)

            if (reportResult.success && reportResult.fileUrl) {
              console.log(`Report generated successfully: ${reportResult.fileUrl}`)

              // Update order's Report_Strapi_Reference with the Strapi file reference
              try {
                const updateResult = await event.context.fetch(
                  `models/c_order/${order.id}`,
                  'PUT',
                  token,
                  {
                    Report_Strapi_Reference: reportResult.fileUrl
                  }
                )
                console.log(`Updated order ${order.DocumentNo} Report_Strapi_Reference with report file: ${reportResult.fileUrl}`)
                console.log('Update result:', updateResult)
              } catch (updateErr: any) {
                console.error(`Failed to update Report_Strapi_Reference for order ${order.DocumentNo}:`, updateErr)
                data.errors.push({
                  bpartnerId: group.bpartnerId,
                  error: `Order created but failed to update Report_Strapi_Reference`,
                  details: {
                    orderId: order.id,
                    orderDocumentNo: order.DocumentNo,
                    reportUrl: reportResult.fileUrl,
                    updateError: updateErr.message || String(updateErr)
                  }
                })
              }
            } else {
              console.error(`Failed to generate report for order ${order.DocumentNo}`)
              data.errors.push({
                bpartnerId: group.bpartnerId,
                error: `Order created but failed to generate report`,
                details: {
                  orderId: order.id,
                  orderDocumentNo: order.DocumentNo,
                  reportError: reportResult.error || 'Unknown error'
                }
              })
            }
          } catch (reportErr: any) {
            console.error(`Error generating report for order ${order.DocumentNo}:`, reportErr)
            data.errors.push({
              bpartnerId: group.bpartnerId,
              error: `Order created but failed to generate report`,
              details: {
                orderId: order.id,
                orderDocumentNo: order.DocumentNo,
                reportError: reportErr.message || String(reportErr)
              }
            })
          }
        }

        const successPayload: any = {
          orderId: order.id,
          documentNo: order.DocumentNo,
          bpartnerId: group.bpartnerId,
          bpartnerName: bpartner.Name,
          monthYear: group.monthYear,
          linesProcessed: group.lines.length,
          totalAmount: totalLines,
          orderLines: responseOrderLines
        }

        if (debugStorageUsage) {
          successPayload.debug = successPayload.debug || {}
          successPayload.debug.storageUsage = storageUsageDebug
        }

        data.success.push(successPayload)
      } else {
        data.errors.push({
          bpartnerId: group.bpartnerId,
          error: 'Failed to create order',
          details: order,
          payload: orderPayload // Include payload in error response
        })
      }
    } catch (err: any) {
      console.error(`ORDER CREATION ERROR for partner ${group.bpartnerId}:`, err)
      data.errors.push({
        bpartnerId: group.bpartnerId,
        monthYear: group.monthYear,
        error: err.message || 'Unknown error occurred',
        errorType: err.name || 'Error',
        linesCount: group.lines.length,
        details: {
          message: err.message,
          statusCode: err.statusCode,
          stack: err.stack?.split('\n').slice(0, 3).join('\n') // First 3 lines of stack
        },
        payload: orderPayload // Include payload in error response
      })
      // Continue to next group instead of stopping
      continue
    }

      processedCount++

      // Add small delay between groups to prevent connection pool exhaustion
      if (processedCount < groupKeys.length) {
        await new Promise(resolve => setTimeout(resolve, 100))
      }
    }

    // Longer delay between batches
    if (batchStart + batchSize < groupKeys.length) {
      console.log('Pausing 500ms between batches...')
      await new Promise(resolve => setTimeout(resolve, 500))
    }
  }

  console.log(`Completed processing. Success: ${data.success.length}, Errors: ${data.errors.length}`)
  return data
}

export default defineEventHandler(async (event) => {
  let data: any = { success: [], errors: [], preview: false }

  try {
    data = await handleFunc(event)
  } catch(err: any) {
    try {
      let authToken: any = await refreshTokenHelper(event)
      data = await handleFunc(event, authToken)
    } catch(error: any) {
      console.error('Fatal error in generate-orders:', error)

      // Check if this is a local dev environment error (missing tables)
      const errorMsg = error.message || String(error)
      if (errorMsg.includes('cust_fulfillmentfeeline') || errorMsg.includes('No match found for table')) {
        console.log('Detected local dev environment - returning dummy data')
        const body = await readBody(event)
        return {
          success: [
            {
              bpartnerId: body.partnerId || 1014225,
              bpartnerName: 'Main Branch',
              monthYear: body.rangeMode
                ? `${body.monthFrom || 11}/${body.yearFrom || 2025} - ${body.monthTo || 11}/${body.yearTo || 2025}`
                : `${body.month || 11}/${body.year || 2025}`,
              orderPreview: {
                totalAmount: 1500.00,
                shippingFees: 150.00,
                parcelFees: 50.00,
                storageFees: 25.00,
                lineCount: 5,
                lines: [
                  {
                    productName: 'Shipping Fee - November',
                    quantity: 1,
                    price: 150.00,
                    lineTotal: 150.00
                  },
                  {
                    productName: 'Parcel Fee',
                    quantity: 5,
                    price: 10.00,
                    lineTotal: 50.00
                  },
                  {
                    productName: 'Storage Fee',
                    quantity: 1,
                    price: 25.00,
                    lineTotal: 25.00
                  }
                ]
              }
            }
          ],
          errors: [],
          preview: body.preview === true,
          localDevMode: true
        }
      }

      // Return partial results if any were generated
      if (data.success && data.success.length > 0) {
        data.errors.push({
          error: 'Request partially completed with errors',
          details: error.message || String(error)
        })
        return data
      }
      // Otherwise return error but don't throw
      data = {
        success: [],
        errors: [{
          error: 'Failed to generate orders',
          details: errorMsg
        }],
        preview: false
      }
    }
  }

  return data
})