import { string } from 'alga-js'
import refreshTokenHelper from "../../../utils/refreshTokenHelper"
import errorHandlingHelper from "../../../utils/errorHandlingHelper"
import fetchHelper from "../../../utils/fetchHelper"
import strapiHelper from "../../../utils/strapiHelper"

// AD_Table_ID of C_Order in Strapi's ad-attachments (see KNOWN_TABLE_IDS in
// server/api/attachments/[table]/[id]/index.get.ts).
const C_ORDER_TABLE_ID = 259

// Columns of v_orders_summary that mapRow() actually reads. Without $select
// iDempiere serialises every view column (~257 KB per 50-row block vs ~150 KB).
const SELECT_COLUMNS = [
  'DocumentNo', 'DateOrdered', 'Created', 'GrandTotal', 'TrackingNo', 'IsSOTrx', 'IsActive',
  'DocStatus', 'C_DocType_ID', 'C_DocTypeTarget_ID', 'C_Currency_ID', 'PriorityRule', 'M_Warehouse_ID',
  'C_BPartner_ID', 'C_BPartner_Location_ID', 'Bill_BPartner_ID', 'Bill_Location_ID', 'AD_Org_ID', 'AD_Client_ID',
  'C_OrderSource_ID', 'order_source_name', 'order_source_description', 'marketplace_name', 'ExternalOrderId',
  'plentyone_order_id', 'ship_location_id', 'ship_name', 'ship_address1', 'ship_city', 'ship_postal',
  'ship_country', 'ship_countrycode', 'shipment_id', 'shipment_docstatus', 'shipments_summary', 'IsCommissioned',
  'IsCommissionedConfirmed', 'shipping_date', 'shipper_id', 'shipper_name', 'shipper_cfg', 'is_attachment_required',
  'is_print_dina4', 'TotalQty', 'fulfillment_fee_total', 'fee_processed', 'has_shipment', 'has_invoice',
  'has_description', 'billing_differs_from_shipping',
].join(',')

// One m_inout lookup for the whole block (newest shipment per order) — replaces
// the per-row GET /api/inouts/{id}/order the TrackingNo + Action renderers used to fire.
const fetchShipmentsForOrders = async (event: any, token: string, orderIds: number[]) => {
  const map: Record<number, any> = {}
  if (orderIds.length === 0) return map
  const filter = `C_Order_ID in (${orderIds.join(',')})`
  const res: any = await event.context.fetch(
    `models/m_inout?$filter=${string.urlEncode(filter)}&$select=C_Order_ID,TrackingNo,DocStatus,IsCommissioned,IsCommissionedConfirmed,marketplace_confirm_log&$orderby=${string.urlEncode('M_InOut_ID desc')}&$top=${orderIds.length * 4}`,
    'GET', token, null
  )
  for (const rec of res?.records || []) {
    const orderId = rec.C_Order_ID?.id ?? rec.C_Order_ID
    if (orderId && !map[orderId]) map[orderId] = rec   // first hit = newest (desc)
  }
  return map
}

// One Strapi lookup for the whole block — replaces the per-row
// GET /api/attachments/C_Order/{id} (+ /files) the attachment renderer used to fire.
const fetchAttachmentsForOrders = async (event: any, orderIds: number[]) => {
  const map: Record<number, { documentId: string, count: number }> = {}
  if (orderIds.length === 0) return map
  const inFilter = orderIds.map((id, i) => `filters[Record_ID][$in][${i}]=${id}`).join('&')
  const res: any = await strapiHelper(
    event,
    `ad-attachments?filters[AD_Table_ID][$eq]=${C_ORDER_TABLE_ID}&${inFilter}&fields[0]=Record_ID&populate[attachment][fields][0]=id&pagination[pageSize]=${orderIds.length * 2}`,
    'GET', null
  )
  for (const entry of res?.data || []) {
    const orderId = Number(entry.Record_ID)
    const count = Array.isArray(entry.attachment) ? entry.attachment.length : 0
    if (!orderId) continue
    // several ad-attachment entries per record are possible — keep the one with files
    if (!map[orderId] || count > map[orderId].count) {
      map[orderId] = { documentId: entry.documentId || '', count }
    }
  }
  return map
}

const idempiereFilterName = (key: string) => {
  const map: Record<string, string> = {
    documentNo: 'DocumentNo',
    externalorderId: 'ExternalOrderId',
    partnerLocationName: 'ship_name',
    partnerLocationName2: 'ship_name2',
    locationZipCode: 'ship_postal',
    locationStreet: 'ship_address1',
    locationCity: 'ship_city',
    shipperName: 'shipper_name',
    grandTotal: 'GrandTotal',
    totalQty: 'TotalQty',
    fulfillmentFeeTotal: 'fulfillment_fee_total',
    trackingNo: 'TrackingNo',
    created: 'Created',
    // real view key column — bare 'id' is not queryable/orderable in iDempiere
    id: 'c_order_id',
  }
  return map[key] || ''
}

const sortFieldName = (key: string) => {
  const map: Record<string, string> = {
    documentNo: 'DocumentNo',
    organization: 'AD_Org_ID',
    externalorderId: 'ExternalOrderId',
    partnerLocationName: 'ship_name',
    partnerLocationName2: 'ship_name2',
    locationZipCode: 'ship_postal',
    locationStreet: 'ship_address1',
    locationCity: 'ship_city',
    shipperName: 'shipper_name',
    grandTotal: 'GrandTotal',
    fulfillmentFeeTotal: 'fulfillment_fee_total',
    trackingNo: 'TrackingNo',
    created: 'Created',
    id: 'c_order_id',
    totalQty: 'TotalQty',
    shippingDate: 'shipping_date',
    shipmentStatus: 'DocStatus',
  }
  return map[key] || ''
}

const filterHandler = (filterModel: any) => {
  if (!filterModel || Object.keys(filterModel).length === 0) return ''

  let parts: string[] = []
  for (const key of Object.keys(filterModel)) {
    const mapped = idempiereFilterName(key)
    if (!mapped) continue

    const item = filterModel[key]
    if (item.filterType === 'text') {
      const text = textFilterHandler(mapped, item)
      if (text) parts.push(text)
    } else if (item.filterType === 'number') {
      const num = numberFilterHandler(mapped, item)
      if (num) parts.push(num)
    } else if (item.filterType === 'date') {
      parts.push(`${mapped} eq ${item.dateFrom}`)
    }
  }

  if (parts.length === 0) return ''
  return ' AND (' + parts.join(' AND ') + ')'
}

const numberFilterHandler = (key: string, item: any) => {
  if (item.type === 'equals') return `${key} eq ${item.filter}`
  if (item.type === 'notEqual') return `${key} neq ${item.filter}`
  if (item.type === 'greaterThan') return `${key} gt ${item.filter}`
  if (item.type === 'greaterThanOrEqual') return `${key} ge ${item.filter}`
  if (item.type === 'lessThan') return `${key} lt ${item.filter}`
  if (item.type === 'lessThanOrEqual') return `${key} le ${item.filter}`
  if (item.type === 'inRange') return `${key} ge ${item.filter} AND ${key} le ${item.filterTo}`
  return ''
}

const textFilterHandler = (key: string, item: any) => {
  const val = item.filter?.toString() || ''
  const isFkField = key.endsWith('_ID')

  if (isFkField) {
    // FK fields: contains() matches against identifier text, upper() not supported
    const cleanVal = val.replaceAll('%', '')
    if (item.type === 'equals') return `${key} eq '${cleanVal}'`
    if (item.type === 'notEqual') return `${key} neq '${cleanVal}'`
    if (item.type === 'contains') return `contains(${key},'${cleanVal}')`
    if (item.type === 'notContains') return `not contains(${key},'${cleanVal}')`
    if (item.type === 'startsWith') return `startswith(${key},'${cleanVal}')`
    if (item.type === 'endsWith') return `endswith(${key},'${cleanVal}')`
    return ''
  }

  // Case-insensitive via tolower() (OData standard)
  const lowerKey = `tolower(${key})`
  const lowerVal = val.toLowerCase().replaceAll('%', '')
  if (item.type === 'equals') return `${lowerKey} eq '${lowerVal}'`
  if (item.type === 'notEqual') return `${lowerKey} neq '${lowerVal}'`
  if (item.type === 'contains') return `contains(${lowerKey},'${lowerVal}')`
  if (item.type === 'notContains') return `not contains(${lowerKey},'${lowerVal}')`
  if (item.type === 'startsWith') return `startswith(${lowerKey},'${lowerVal}')`
  if (item.type === 'endsWith') return `endswith(${lowerKey},'${lowerVal}')`
  return ''
}

const buildSortClause = (sortModel: any[]) => {
  if (!sortModel || sortModel.length === 0) return 'Created desc'

  const parts = sortModel
    .map(s => {
      const col = sortFieldName(s.colId)
      if (!col) return ''
      return `${col} ${s.sort}`
    })
    .filter(Boolean)

  return parts.length > 0 ? parts.join(',') : 'Created desc'
}


const calculateAge = (fromDate: string, toDate: string) => {
  if (!fromDate || !toDate) return ''
  const diffSeconds = Math.abs((new Date(toDate).getTime() - new Date(fromDate).getTime()) / 1000)
  if (diffSeconds < 3600) return Math.round(diffSeconds / 60) + ' minutes'
  if (diffSeconds < 86400) return Math.round(diffSeconds / 3600) + ' hours'
  return Math.round(diffSeconds / 86400) + ' days'
}

const mapRow = (item: any, serviceDocStatus?: any, inout?: any, attachment?: { documentId: string, count: number } | null) => {
  // Build minout array from shipments_summary
  const minout: any[] = []
  if (item.shipments_summary) {
    item.shipments_summary.split(' | ').forEach((s: string) => {
      const [inoutId, docNo] = s.split(':')
      if (inoutId) {
        minout.push({
          id: parseInt(inoutId),
          DocumentNo: docNo,
          TrackingNo: item.TrackingNo || '',
          IsCommissioned: item.IsCommissioned,
          IsCommissionedConfirmed: item.IsCommissionedConfirmed,
          DocStatus: item.shipment_docstatus?.id || '',
          shipping_date: item.shipping_date,
        })
      }
    })
  }

  // Use service token DocStatus as fallback if user's token didn't return it
  const docStatus = item.DocStatus || serviceDocStatus || null
  const docStatusId = docStatus?.id || ''

  // Compute shipment status server-side
  let shipmentStatus = ''
  const shipmentDocStatusId = item.shipment_docstatus?.id || ''
  if (docStatusId === 'VO') {
    shipmentStatus = 'voided'
  } else if (docStatusId === 'DR') {
    shipmentStatus = 'drafted'
  } else if (docStatusId === 'IP') {
    shipmentStatus = 'in_progress'
  } else if (docStatusId === 'CO') {
    if (shipmentDocStatusId === 'CO' && item.TrackingNo) {
      shipmentStatus = 'shipped'
    } else if (shipmentDocStatusId === 'CO' && !item.TrackingNo) {
      shipmentStatus = 'packing'
    } else {
      shipmentStatus = 'incoming'
    }
  }

  // Build orderSource object
  const orderSource = {
    ...(item.C_OrderSource_ID || {}),
    Name: item.order_source_name || item.C_OrderSource_ID?.identifier || '',
    Description: item.order_source_description || '',
    Marketplace: { identifier: item.marketplace_name || '' },
  }

  return {
    marketplaceKey: item.marketplace_name || '',
    id: item.id,
    isActive: item.IsActive,
    documentNo: item.DocumentNo,
    description: item.Description || '',
    dateOrdered: item.DateOrdered,
    created: item.Created,
    grandTotal: item.GrandTotal,
    trackingNo: item.TrackingNo || '',
    isSOTrx: item.IsSOTrx,
    shipmentStatus: shipmentStatus,
    docStatus: docStatus?.identifier || '',
    docStatusId: docStatusId,
    docType: item.C_DocType_ID?.identifier || '',
    docTypeId: item.C_DocType_ID?.id || '',
    docTypeTarget: item.C_DocTypeTarget_ID?.identifier || '',
    docTypeTargetId: item.C_DocTypeTarget_ID?.id || '',
    currency: item.C_Currency_ID?.identifier || '',
    currencyId: item.C_Currency_ID?.id || '',
    priorityRule: item.PriorityRule?.identifier || '',
    priorityRuleId: item.PriorityRule?.id || '',
    warehouse: item.M_Warehouse_ID?.identifier || '',
    warehouseId: item.M_Warehouse_ID?.id || '',
    partner: item.C_BPartner_ID?.identifier || '',
    partnerId: item.C_BPartner_ID?.id || '',
    partnerLocation: item.C_BPartner_Location_ID || {},
    partnerLocationId: item.C_BPartner_Location_ID?.id || '',
    partnerLocationName: item.ship_name || '',
    partnerLocationName2: item.ship_name2 || '',
    billPartner: item.Bill_BPartner_ID?.identifier || '',
    billPartnerId: item.Bill_BPartner_ID?.id || '',
    billPartnerLocation: item.Bill_Location_ID || {},
    billPartnerLocationId: item.Bill_Location_ID?.id || '',
    organization: item.AD_Org_ID?.identifier || '',
    organizationId: item.AD_Org_ID?.id || '',
    client: item.AD_Client_ID?.identifier || '',
    clientId: item.AD_Client_ID?.id || '',
    orderSource: orderSource,
    orderSourceId: item.C_OrderSource_ID?.id || '',
    orderSourceName: item.order_source_name || item.C_OrderSource_ID?.identifier || '',
    externalorderId: item.ExternalOrderId || '',
    shopware6_order_id: item.shopware6_order_id || '',
    shopify_order_id: item.shopify_order_id || '',
    amazon_order_id: item.amazon_order_id || '',
    plentyone_order_id: item.plentyone_order_id || '',
    jtl_order_id: item.jtl_order_id || '',
    country: item.ship_country || '',
    countryCode: item.ship_countrycode?.id || '',
    countryId: '',
    locationStreet: item.ship_address1 || '',
    locationStreet2: item.ship_address2 || '',
    locationZipCode: item.ship_postal || '',
    locationCity: item.ship_city || '',
    name2: item.ship_name2 || '',
    location: { id: item.ship_location_id?.id || item.ship_location_id || null },
    minout: minout,
    shipperId: item.shipper_id?.id ?? item.shipper_id ?? '',
    shipper: item.shipper_name || '',
    shipperName: item.shipper_name || '',
    shipperCfgIdentifier: item.shipper_cfg || '',
    isAttachmentRequired: item.is_attachment_required === true,
    isPrintDINA4: item.is_print_dina4 === true,
    paketTypeName: item.paket_type_name || '',
    shipmentWeight: item.shipment_weight || null,
    shippingDate: item.shipping_date || '',
    totalQty: item.TotalQty || 0,
    orderLines: [{ QtyEntered: item.TotalQty || 0 }],
    fulfillmentFeeTotal: item.fulfillment_fee_total || 0,
    feeProcessed: item.fee_processed === true || item.fee_processed === 'Y',
    feeEditable: (item.fulfillment_fee_total || 0) > 0 && !(item.fee_processed === true || item.fee_processed === 'Y'),
    daysSinceCreated: item.Created ? Math.floor((Date.now() - new Date(item.Created).getTime()) / 86400000) : 0,
    createdToOrderedAge: calculateAge(item.Created, item.DateOrdered),
    hasShipment: item.has_shipment === 'Y',
    hasInvoice: item.has_invoice === 'Y',
    hasDescription: item.has_description === 'Y',
    billingDiffersFromShipping: item.billing_differs_from_shipping === 'Y',
    // Block-level enrichment (no per-row fetches in cell renderers):
    shipmentId: inout?.id ?? item.shipment_id?.id ?? item.shipment_id ?? null,
    shipmentDocStatusId: inout?.DocStatus?.id ?? shipmentDocStatusId,
    shipmentTrackingNo: inout?.TrackingNo ?? item.TrackingNo ?? '',
    marketplaceConfirmLog: inout?.marketplace_confirm_log ?? '',
    hasMarketplaceConfirmLog: !!(inout?.marketplace_confirm_log && String(inout.marketplace_confirm_log).trim()),
    // null = lookup unavailable (Strapi down) → renderer falls back to its own fetch
    attachmentCount: attachment === undefined ? null : (attachment?.count ?? 0),
    attachmentDocumentId: attachment?.documentId ?? '',
  }
}

const handleFunc = async (event: any, authToken: any = null) => {
  let data: any = { rows: [], lastRow: 0 }
  const token = authToken ?? await getTokenHelper(event)
  const body = await readBody(event)

  const startRow = body.startRow || 0
  const endRow = body.endRow || 100
  const pageSize = endRow - startRow

  // Base filter
  let baseFilter = 'IsSOTrx eq true'

  // Custom switch filters
  if (body.orgId) {
    baseFilter += ` AND AD_Org_ID eq ${body.orgId}`
  }

  if (body.openOnly === true) {
    baseFilter += ` AND has_shipment eq 'N' AND (DocStatus eq 'CO' or DocStatus eq 'DR' or DocStatus eq 'IP')`
  }

  if (body.openShipments === true) {
    baseFilter += ` AND has_shipment eq 'Y' AND IsCommissioned eq false AND (DocStatus eq 'CO' or DocStatus eq 'DR' or DocStatus eq 'IP')`
  }

  if (body.shipped === true) {
    baseFilter += ` AND has_shipment eq 'Y' AND TrackingNo gt '' AND (DocStatus eq 'CO' or DocStatus eq 'IP')`
  }

  if (body.noFees === true) {
    baseFilter += ` AND fulfillment_fee_total eq 0`
  }

  // Shipper filter
  if (body.shipperId) {
    baseFilter += ` AND shipper_id eq ${body.shipperId}`
  }

  // Order source filter
  if (body.orderSourceId) {
    baseFilter += ` AND C_OrderSource_ID eq ${body.orderSourceId}`
  }

  // Marketplace filter (TODO: fix filtering)
  // if (body.marketplaceName) {
  //   baseFilter += ` AND marketplace_name eq '${body.marketplaceName}'`
  // }

  // Created date filter — filters on c_order.Created only
  if (body.dateFrom || body.dateTo) {
    if (body.dateFrom) {
      baseFilter += ` AND Created ge ${body.dateFrom}`
    }
    if (body.dateTo) {
      const nextDay = new Date(body.dateTo + 'T00:00:00')
      nextDay.setDate(nextDay.getDate() + 1)
      const nextDayStr = `${nextDay.getFullYear()}-${String(nextDay.getMonth() + 1).padStart(2, '0')}-${String(nextDay.getDate()).padStart(2, '0')}`
      baseFilter += ` AND Created lt ${nextDayStr}`
    }
  }

  // Shipped date filter — filters on shipping_date only
  if (body.shippedDateFrom || body.shippedDateTo) {
    if (body.shippedDateFrom) {
      baseFilter += ` AND shipping_date ge ${body.shippedDateFrom}`
    }
    if (body.shippedDateTo) {
      const nextDay = new Date(body.shippedDateTo + 'T00:00:00')
      nextDay.setDate(nextDay.getDate() + 1)
      const nextDayStr = `${nextDay.getFullYear()}-${String(nextDay.getMonth() + 1).padStart(2, '0')}-${String(nextDay.getDate()).padStart(2, '0')}`
      baseFilter += ` AND shipping_date lt ${nextDayStr}`
    }
  }

  // AG Grid column filters
  const agFilter = filterHandler(body.filterModel || {})

  const fullFilter = baseFilter + agFilter
  const sortClause = buildSortClause(body.sortModel)

  // Totals (fees / revenue over the WHOLE filtered set) are only needed once per
  // filter set — AgGridServer sends withAggregates:false for every block after
  // the first. Other callers (card view, exports) omit the flag and still get them.
  const withAggregates = body.withAggregates !== false

  const [res, sumRes]: any[] = await Promise.all([
    event.context.fetch(
      `models/v_orders_summary?$filter=${string.urlEncode(fullFilter)}&$orderby=${string.urlEncode(sortClause)}&$select=${SELECT_COLUMNS}&$top=${pageSize}&$skip=${startRow}`,
      'GET', token, null
    ),
    withAggregates
      ? event.context.fetch(
          `models/v_orders_summary?$filter=${string.urlEncode(fullFilter)}&$select=fulfillment_fee_total,GrandTotal&$top=999999`,
          'GET', token, null
        )
      : Promise.resolve(null),
  ])

  if (res) {
    const config = useRuntimeConfig()
    const serviceToken = config.api.idempieretoken
    const records = res.records || []
    const orderIds: number[] = records.map((item: any) => item.id).filter(Boolean)
    const ordersMissingDocStatus = records.filter((item: any) => !item.DocStatus)

    // Block-level enrichment in parallel: newest shipment per order, attachment
    // counts, and (limited roles only) DocStatus via the service token.
    const [shipmentMap, attachmentMap, serviceDocStatusMap] = await Promise.all([
      fetchShipmentsForOrders(event, token, orderIds).catch((e: any) => {
        console.warn('[orders/summary] shipment lookup failed:', e?.message || e)
        return {} as Record<number, any>
      }),
      fetchAttachmentsForOrders(event, orderIds).catch((e: any) => {
        console.warn('[orders/summary] attachment lookup failed:', e?.message || e)
        return null
      }),
      (async () => {
        const map: Record<number, any> = {}
        if (ordersMissingDocStatus.length === 0 || !serviceToken) return map
        const ids = ordersMissingDocStatus.map((item: any) => item.id)
        const idsFilter = ids.map((id: number) => `C_Order_ID eq ${id}`).join(' OR ')
        try {
          const serviceRes: any = await fetchHelper(event, `models/c_order?$filter=${string.urlEncode(idsFilter)}&$select=DocStatus&$top=${ids.length}`, 'GET', serviceToken, null)
          for (const rec of serviceRes?.records || []) map[rec.id] = rec.DocStatus
        } catch (e) {
          // Silently continue — status tags will be missing but grid still works
        }
        return map
      })(),
    ])

    data.rows = records.map((item: any) => mapRow(
      item,
      serviceDocStatusMap[item.id],
      shipmentMap[item.id],
      attachmentMap === null ? undefined : (attachmentMap[item.id] ?? null)
    ))
    data.lastRow = res['row-count'] || 0
  }

  if (sumRes?.records) {
    data.totalFees = sumRes.records.reduce((sum: number, r: any) => sum + (r.fulfillment_fee_total || 0), 0)
    data.totalGrandTotal = sumRes.records.reduce((sum: number, r: any) => sum + (r.GrandTotal || 0), 0)
  }

  return data
}

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

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

  return data
})