import { string } from 'alga-js'

/**
 * Query building shared by the sales-orders grid routes
 * (server/api/orders/ag-grid/summary.post.ts and summary-totals.post.ts).
 *
 * Both routes hit the iDempiere view `v_orders_summary` (DDL: reference/sql/v_orders_summary.sql).
 */

// 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).
export 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',
  // every per-marketplace id AgMarketplaceLink.vue links to (not only PlentyOne)
  'plentyone_order_id', 'shopware6_order_id', 'shopify_order_id', 'amazon_order_id', 'jtl_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(',')

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 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 filterHandler = (filterModel: any) => {
  if (!filterModel || Object.keys(filterModel).length === 0) return ''

  const 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 ') + ')'
}

export 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 nextDayStr = (isoDate: string) => {
  const nextDay = new Date(isoDate + 'T00:00:00')
  nextDay.setDate(nextDay.getDate() + 1)
  return `${nextDay.getFullYear()}-${String(nextDay.getMonth() + 1).padStart(2, '0')}-${String(nextDay.getDate()).padStart(2, '0')}`
}

/** Full OData $filter for the grid: switch toggles + dropdowns + date ranges + AG Grid column filters. */
export const buildFullFilter = (body: any) => {
  let baseFilter = 'IsSOTrx eq true'

  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`

  if (body.shipperId) baseFilter += ` AND shipper_id eq ${body.shipperId}`
  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) baseFilter += ` AND Created ge ${body.dateFrom}`
  if (body.dateTo) baseFilter += ` AND Created lt ${nextDayStr(body.dateTo)}`

  // Shipped date filter — filters on shipping_date only
  if (body.shippedDateFrom) baseFilter += ` AND shipping_date ge ${body.shippedDateFrom}`
  if (body.shippedDateTo) baseFilter += ` AND shipping_date lt ${nextDayStr(body.shippedDateTo)}`

  return baseFilter + filterHandler(body.filterModel || {})
}

/**
 * Row visibility of v_orders_summary depends on the iDempiere client/role/org of
 * the session (never on the user) — cache / dedupe per that triple.
 */
export const sessionCacheKey = (event: any) => {
  const c = getCookie(event, 'logship_client_id') || ''
  const r = getCookie(event, 'logship_role_id') || ''
  const o = getCookie(event, 'logship_organization_id') || ''
  return `${c}:${r}:${o}`
}

// ---------------------------------------------------------------------------
// Totals (fees / revenue over the WHOLE filtered set): one $top=999999 pull of
// two columns, summed in Node. 3.3k rows ≈ 0.5 s, the unfiltered 40k ≈ 3 s —
// never on the block path; cached 60 s per session triple + filter, single-flight.
// ---------------------------------------------------------------------------
const TOTALS_TTL_MS = 60_000
const totalsCache = new Map<string, { at: number; value: SummaryTotals }>()
const totalsInflight = new Map<string, Promise<SummaryTotals>>()

export interface SummaryTotals { totalFees: number; totalGrandTotal: number; rowCount: number }

export const fetchSummaryTotals = async (event: any, token: any, fullFilter: string): Promise<SummaryTotals> => {
  const key = `${sessionCacheKey(event)}|${fullFilter}`
  const hit = totalsCache.get(key)
  if (hit && Date.now() - hit.at < TOTALS_TTL_MS) return hit.value
  const running = totalsInflight.get(key)
  if (running) return running

  const p = (async () => {
    const res: any = await event.context.fetch(
      `models/v_orders_summary?$filter=${string.urlEncode(fullFilter)}&$select=fulfillment_fee_total,GrandTotal&$top=999999`,
      'GET', token, null
    )
    const records: any[] = res?.records || []
    const value: SummaryTotals = {
      totalFees: records.reduce((sum: number, r: any) => sum + (r.fulfillment_fee_total || 0), 0),
      totalGrandTotal: records.reduce((sum: number, r: any) => sum + (r.GrandTotal || 0), 0),
      rowCount: res?.['row-count'] ?? records.length,
    }
    totalsCache.set(key, { at: Date.now(), value })
    return value
  })().finally(() => totalsInflight.delete(key))
  totalsInflight.set(key, p)
  return p
}

/** Drop cached totals (e.g. after a fee edit) — all sessions, cheap to rebuild. */
export const invalidateSummaryTotals = () => totalsCache.clear()