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 getTokenHelper from "../../utils/getTokenHelper"

const handleFunc = async (event: any, authToken: any = null) => {
  let data: any = { records: [], 'row-count': 0 }
  const token = authToken ?? await getTokenHelper(event)
  const query = getQuery(event)

  const top = query['$top'] || 100
  const skip = query['$skip'] || 0
  const orgId = query['org_id'] as string
  const dateFrom = query['date_from'] as string
  const dateTo = query['date_to'] as string

  if (!orgId) {
    return { status: 400, message: 'Organization ID is required', records: [], 'row-count': 0 }
  }

  // Base filter: sales orders only, filtered by org
  let filterParts = ['isSOTrx eq true', `AD_Org_ID eq ${orgId}`]

  // Date range filter on DateOrdered
  if (dateFrom) {
    filterParts.push(`DateOrdered ge '${dateFrom}'`)
  }
  if (dateTo) {
    filterParts.push(`DateOrdered le '${dateTo}'`)
  }

  if (query.filter) {
    filterParts.push(query.filter as string)
  }

  const filterString = string.urlEncode(filterParts.join(' AND '))

  // Fetch orders with order lines (including tax), order source, and shipments (with freight costs)
  const orderRes: any = await fetchHelper(
    event,
    `models/c_order?$filter=${filterString}&$expand=${string.urlEncode(
      'c_orderline($expand=m_product_id($select=Name,Value,SKU,UPC),C_Tax_ID($select=Rate,Name)),' +
      'c_ordersource_id($select=Name,Marketplace),' +
      'm_inout($select=ext_freight_cost,ext_freight_total,int_freight_cost,int_freight_total,shipping_date,TrackingNo,DocStatus),' +
      'C_BPartner_ID($select=Name),' +
      'AD_Org_ID($select=Name),' +
      'C_Currency_ID($select=ISO_Code)'
    )}&$orderby=${string.urlEncode('c_order_id desc')}&$top=${top}&$skip=${skip}`,
    'GET',
    token,
    null
  )

  // Fetch purchase prices from pricelist version 1000004
  const priceRes: any = await fetchHelper(
    event,
    `models/m_productprice?$filter=${string.urlEncode('M_PriceList_Version_ID eq 1000004')}&$expand=M_Product_ID($select=Name,Value,SKU)&$top=10000`,
    'GET',
    token,
    null
  )

  // Build purchase price lookup map: product_id -> PriceStd
  const purchasePriceMap: Record<number, number> = {}
  if (priceRes?.records) {
    for (const pp of priceRes.records) {
      if (pp.M_Product_ID?.id) {
        purchasePriceMap[pp.M_Product_ID.id] = pp.PriceStd || pp.PriceList || 0
      }
    }
  }

  if (orderRes?.records) {
    // Flatten: one row per order line
    const rows: any[] = []
    for (const order of orderRes.records) {
      const orderLines = order.c_orderline || []
      const orderSource = order.c_ordersource_id || order.C_OrderSource_ID || null
      const inouts = order.m_inout || []
      const isTaxIncluded = order.IsTaxIncluded === true || order.IsTaxIncluded === 'Y'

      // Determine marketplace
      let marketplace = ''
      if (orderSource) {
        const mp = orderSource.Marketplace
        if (typeof mp === 'object' && mp?.identifier) marketplace = mp.identifier
        else if (typeof mp === 'string') marketplace = mp
        else if (orderSource.Name) marketplace = orderSource.Name
      }

      // Shipment freight: take the max of ext vs int freight total across all shipments
      let extFreightTotal = 0
      let intFreightTotal = 0
      for (const inout of inouts) {
        extFreightTotal += inout.ext_freight_total || 0
        intFreightTotal += inout.int_freight_total || 0
      }
      const shippingCost = Math.max(extFreightTotal, intFreightTotal)

      // Derive tax multiplier from order header: GrandTotal / TotalLines
      // This is the most reliable source since C_Tax_ID expansion may not return Rate
      const orderGrandTotal = order.GrandTotal || 0
      const orderTotalLines = order.TotalLines || orderLines.reduce((s: number, l: any) => s + (l.LineNetAmt || 0), 0) || 1
      // When IsTaxIncluded: TotalLines already contains tax, GrandTotal also contains tax
      // When NOT IsTaxIncluded: TotalLines is net, GrandTotal is gross
      // So orderTaxMultiplier = GrandTotal / TotalLines gives us gross/net ratio
      const orderTaxMultiplier = (!isTaxIncluded && orderTotalLines > 0 && orderGrandTotal > 0)
        ? (orderGrandTotal / orderTotalLines)
        : (isTaxIncluded && orderTotalLines > 0 && orderGrandTotal > 0 && orderGrandTotal !== orderTotalLines)
          ? (orderGrandTotal / orderTotalLines)  // some iDempiere configs: GrandTotal may still differ
          : 1.19 // fallback to 19% VAT

      let orderNetTotal = 0

      // First pass: compute net and gross amounts per line, skipping charge lines (no product)
      // and aggregating lines with the same product within the same order
      const productAggMap: Record<number, any> = {}
      for (const line of orderLines) {
        const productId = line.M_Product_ID?.id || line.m_product_id?.id
        // Skip charge lines (no product) — these are shipping/handling charges etc.
        if (!productId) continue

        const lineNetAmtRaw = line.LineNetAmt || 0
        const qty = line.QtyEntered || line.QtyOrdered || 1

        let lineNetAmt: number
        let lineGrossAmt: number

        if (isTaxIncluded) {
          lineGrossAmt = lineNetAmtRaw
          lineNetAmt = lineNetAmtRaw / orderTaxMultiplier
        } else {
          lineNetAmt = lineNetAmtRaw
          lineGrossAmt = lineNetAmtRaw * orderTaxMultiplier
        }

        // Aggregate by unique product within the same order
        if (productAggMap[productId]) {
          productAggMap[productId].qty += qty
          productAggMap[productId].lineNetAmt += lineNetAmt
          productAggMap[productId].lineGrossAmt += lineGrossAmt
        } else {
          productAggMap[productId] = {
            line,
            productId,
            qty,
            lineNetAmt,
            lineGrossAmt
          }
        }

        orderNetTotal += lineNetAmt
      }

      if (orderNetTotal === 0) orderNetTotal = 1 // avoid division by zero

      const taxRate = orderTaxMultiplier > 1 ? Math.round((orderTaxMultiplier - 1) * 10000) / 100 : 0

      // Marketplace info
      const marketplaceLower = marketplace.toLowerCase()
      const isAmazon = marketplaceLower.includes('amazon')
      const isEbay = marketplaceLower.includes('ebay')
      const isMarketplace = isAmazon || isEbay

      // Amazon referral rate determined by orderGrandTotal thresholds
      let amazonReferralPct = 0
      if (isAmazon) {
        if (orderGrandTotal <= 15) amazonReferralPct = 5
        else if (orderGrandTotal <= 20) amazonReferralPct = 10
        else amazonReferralPct = 15
      }

      // Shipping cost: use actual if available, default 4.50 per order if missing
      const hasShippingFee = shippingCost > 0
      const effectiveShippingCost = hasShippingFee ? shippingCost : 4.50

      // Second pass: build rows for unique products, shipping only on first position
      const uniqueProducts = Object.values(productAggMap)
      let isFirstLine = true
      for (const agg of uniqueProducts) {
        const { line, productId, qty, lineNetAmt, lineGrossAmt } = agg
        const productName = line.M_Product_ID?.identifier || line.m_product_id?.Name || line.M_Product_ID?.Name || ''
        const productSKU = line.M_Product_ID?.SKU || line.m_product_id?.SKU || ''
        const productValue = line.M_Product_ID?.Value || line.m_product_id?.Value || ''
        const purchasePrice = productId ? (purchasePriceMap[productId] || 0) : 0

        const salesPriceNet = qty > 0 ? (lineNetAmt / qty) : 0
        const salesPriceGross = qty > 0 ? (lineGrossAmt / qty) : 0

        // Shipping cost only on the first position of the order
        const lineShippingCost = isFirstLine ? effectiveShippingCost : 0

        // Referral fee calculated per position based on line gross amount (incl. tax)
        let referralFee = 0
        let referralPct = 0
        if (isAmazon) {
          referralPct = amazonReferralPct
          if (orderGrandTotal > 45) {
            // Blended rate for >€45 orders, applied proportionally to this line's gross
            const blendedRate = ((45 * 0.15) + ((orderGrandTotal - 45) * 0.07)) / orderGrandTotal
            referralFee = lineGrossAmt * blendedRate
          } else {
            referralFee = lineGrossAmt * (referralPct / 100)
          }
          if (referralFee < 0.30 && uniqueProducts.length === 1) referralFee = 0.30
        } else if (isEbay) {
          referralPct = lineGrossAmt <= 2000 ? 15 : 9
          referralFee = lineGrossAmt * (referralPct / 100)
        }

        // Margin = line net amount - purchase cost - shipping - referral fee
        const totalPurchaseCost = purchasePrice * qty
        const margin = lineNetAmt - totalPurchaseCost - lineShippingCost - referralFee
        const marginPct = lineNetAmt > 0 ? (margin / lineNetAmt) * 100 : 0

        isFirstLine = false

        rows.push({
          // Order info
          orderId: order.id,
          documentNo: order.DocumentNo,
          dateOrdered: order.DateOrdered,
          organization: order.AD_Org_ID?.identifier || order.AD_Org_ID?.Name || '',
          organizationId: order.AD_Org_ID?.id,
          partner: order.C_BPartner_ID?.identifier || order.C_BPartner_ID?.Name || '',
          partnerId: order.C_BPartner_ID?.id,
          currency: order.C_Currency_ID?.ISO_Code || order.C_Currency_ID?.identifier || 'EUR',
          grandTotal: order.GrandTotal || 0,
          isTaxIncluded: isTaxIncluded,

          // Order source
          marketplace: marketplace,
          orderSourceName: orderSource?.Name || '',
          isAmazon: isAmazon,
          isEbay: isEbay,

          // Product info
          orderLineId: line.id,
          productId: productId,
          product: productName,
          productSKU: productSKU,
          productValue: productValue,
          qty: qty,

          // Prices
          salesPriceNet: salesPriceNet,
          salesPriceGross: salesPriceGross,
          lineNetAmt: lineNetAmt,
          lineGrossAmt: lineGrossAmt,
          taxRate: taxRate,
          purchasePrice: purchasePrice,
          totalPurchaseCost: totalPurchaseCost,

          // Shipping
          extFreightTotal: extFreightTotal,
          intFreightTotal: intFreightTotal,
          shippingCost: effectiveShippingCost,
          lineShippingCost: lineShippingCost,
          hasShippingFee: hasShippingFee,

          // Fees
          referralFee: referralFee,
          referralPct: referralPct,

          // Margin completeness: warn if shipping is estimated or marketplace fee unknown
          missingFees: !hasShippingFee || (isMarketplace ? false : (marketplace ? true : false)),

          // Margin
          margin: margin,
          marginPct: marginPct
        })
      }
    }

    data = {
      status: 200,
      records: rows,
      'row-count': orderRes['row-count'] || orderRes.records.length,
      'line-count': rows.length
    }
  }

  return data
}

export default defineEventHandler(async (event) => {
  let data: any = {}

  try {
    data = await handleFunc(event)
  } catch (err: any) {
    try {
      let authToken: any = await refreshTokenHelper(event)
      data = await handleFunc(event, authToken)
    } catch (error: any) {
      data = errorHandlingHelper(err?.data ?? err, error?.data ?? error)
      forceLogoutHelper(event, data)
    }
  }

  return data
})