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"
import {
  SELECT_COLUMNS, buildFullFilter, buildSortClause, sessionCacheKey, fetchSummaryTotals,
} from "../../../utils/orders/summaryQuery"

// Rows of the /sales/orders grid (AgGridServer blocks) from the iDempiere view
// v_orders_summary (DDL: reference/sql/v_orders_summary.sql).
//
// Two-step paging: (1) ids + row-count with $select=c_order_id — Postgres drops
// the view's unused LATERAL joins, so this stays cheap at any $skip; (2) the
// full columns for exactly those ids. A single-step page would evaluate the
// per-order subqueries for every row skipped by OFFSET (6 s at skip 20000).
// Totals live in summary-totals.post.ts and are never awaited here unless a
// caller explicitly asks (withAggregates: true — card view / export compat).

// 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

// 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 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 fetchPageIds = async (event: any, token: string, fullFilter: string, sortClause: string, top: number, skip: number) => {
  const res: any = await event.context.fetch(
    `models/v_orders_summary?$filter=${string.urlEncode(fullFilter)}&$orderby=${string.urlEncode(sortClause)}&$select=c_order_id&$top=${top}&$skip=${skip}`,
    'GET', token, null
  )
  const ids: number[] = (res?.records || []).map((r: any) => Number(r.id)).filter((n: number) => Number.isFinite(n) && n > 0)
  return { ids, rowCount: Number(res?.['row-count'] || 0) }
}

const fetchRowsByIds = async (event: any, token: string, ids: number[]) => {
  if (ids.length === 0) return [] as any[]
  const res: any = await event.context.fetch(
    `models/v_orders_summary?$filter=${string.urlEncode(`c_order_id in (${ids.join(',')})`)}&$select=${SELECT_COLUMNS}&$top=${ids.length}`,
    'GET', token, null
  )
  const byId: Record<number, any> = {}
  for (const rec of res?.records || []) byId[rec.id] = rec
  // keep the order of step 1 — the by-ids query is unordered
  return ids.map(id => byId[id]).filter(Boolean)
}

const loadBlock = async (event: any, token: string, body: any) => {
  const data: any = { rows: [], lastRow: 0 }
  const startRow = body.startRow || 0
  const endRow = body.endRow || 100
  const pageSize = Math.max(1, endRow - startRow)

  const fullFilter = buildFullFilter(body)
  const sortClause = buildSortClause(body.sortModel)

  const { ids, rowCount } = await fetchPageIds(event, token, fullFilter, sortClause, pageSize, startRow)
  data.lastRow = rowCount
  if (ids.length === 0) return data

  const records = await fetchRowsByIds(event, token, ids)

  const config = useRuntimeConfig()
  const serviceToken = config.api.idempieretoken
  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 missingIds = ordersMissingDocStatus.map((item: any) => item.id)
      const idsFilter = missingIds.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=${missingIds.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)
  ))
  return data
}

// Identical concurrent block requests (purge storms when toggles are clicked
// quickly, keepalive re-mounts) share one in-flight promise. No TTL cache on
// rows: tracking / status / fee edits must show on the next refresh.
const blockInflight = new Map<string, Promise<any>>()

const handleFunc = async (event: any, authToken: any = null) => {
  const token = authToken ?? await getTokenHelper(event)
  const body = await readBody(event) || {}

  const key = `${sessionCacheKey(event)}|${JSON.stringify({
    s: body.startRow || 0, e: body.endRow || 100, f: buildFullFilter(body), o: buildSortClause(body.sortModel),
  })}`
  let p = blockInflight.get(key)
  if (!p) {
    p = loadBlock(event, token, body).finally(() => blockInflight.delete(key))
    blockInflight.set(key, p)
  }
  const data = { ...(await p) }

  // Compat: callers that explicitly ask for totals (card view / export) get the
  // cached whole-set sums; the grid itself calls summary-totals separately.
  if (body.withAggregates === true) {
    try {
      const totals = await fetchSummaryTotals(event, token, buildFullFilter(body))
      data.totalFees = totals.totalFees
      data.totalGrandTotal = totals.totalGrandTotal
    } catch (e: any) {
      console.warn('[orders/summary] totals failed:', e?.message || e)
    }
  }
  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
})