// Shipping performance (cut-off SLA) per merchant organization.
//
// Rule: an order imported (c_order.Created, Berlin local) before the cut-off time on a working day
// must ship that day; everything else must ship by the cut-off of the NEXT working day
// (Mon–Fri, minus CUST_NonWorkingDays). "Shipped" = earliest customer shipment (m_inout C-) of the
// order with shipping_date (set at commissioning; true UTC) – fallback ProcessedOn when commissioned
// without a shipping_date. Day granularity: shipped on or before the deadline's date = on time.
// Cut-off = ?cutoff=HH:MM override → c_bpartner.CutOffTime of the org's partner → 14:00.
//
// Stock-blocked orders: an order that is not on time is checked against the historic stock of its
// item lines in the order's warehouse at the END of the deadline day (m_storage minus later
// m_transaction movements, direct Postgres – server/utils/bi/stockAtDeadline.ts). If at least one
// line needed more than was on hand the order gets status 'nostock' and is excluded from every rate
// (on-time, SLA, lateness, lead time). ?nostock=include keeps the original status (the row still
// carries stockBlocked/stockNote). Without PG the check is skipped (stockCheck: 'unavailable').
//
// Query: org_id (admins; limited roles are forced to their own org), from, to (YYYY-MM-DD, order
// Created window, default last 3 months), cutoff (HH:MM, optional), nostock (exclude|include).
import { string } from 'alga-js'
import refreshTokenHelper from '../../utils/refreshTokenHelper'
import errorHandlingHelper from '../../utils/errorHandlingHelper'
import forceLogoutHelper from '../../utils/forceLogoutHelper'
import fetchHelper from '../../utils/fetchHelper'
import {
  berlinParts, idempiereLocalToInstantMs, utcIsoToInstantMs, berlinLocalToInstantMs,
  dateKey, monthKey, addDays, weekdayOf, pad2
} from '../../utils/berlinTime'
import { findStockBlockedOrders, formatShortfall } from '../../utils/bi/stockAtDeadline'

const DEFAULT_CUTOFF = '14:00'
const PAGE = 1000
const MAX_ORDERS = 20000
const MAX_SHIPMENTS = 40000
const EXCLUDED_DOCSTATUS = new Set(['VO', 'RE', 'IN', 'NA'])

const isoDate = (d: Date) => d.toISOString().slice(0, 10)
const validDate = (v: any) => typeof v === 'string' && /^\d{4}-\d{2}-\d{2}$/.test(v) ? v : null
const parseCutoff = (v: any): { h: number; mi: number } | null => {
  if (!v) return null
  const m = String(v).match(/(\d{1,2}):(\d{2})/)
  if (!m) return null
  const h = Number(m[1]), mi = Number(m[2])
  if (h > 23 || mi > 59) return null
  return { h, mi }
}
const idOf = (v: any): number | null => (typeof v === 'number' ? v : (v?.id ?? null))

const pageAll = async (event: any, token: string, base: string, cap: number) => {
  const out: any[] = []
  for (let skip = 0; skip < cap; skip += PAGE) {
    const top = Math.min(PAGE, cap - skip)
    const res: any = await fetchHelper(event, `${base}&$top=${top}&$skip=${skip}`, 'GET', token, null)
    const page = res?.records || []
    out.push(...page)
    const total = Number(res?.['row-count']) || 0
    if (page.length < top || (total && out.length >= total)) break
  }
  return out
}

type Status = 'ontime' | 'late' | 'pending' | 'overdue' | 'nostock'

type Bucket = {
  key: string
  label: string
  orders: number
  shipped: number
  onTime: number
  late: number
  overdue: number
  pending: number
  noStock: number
  leadSum: number
  leadCount: number
  onTimeRate: number | null
  avgLeadHours: number | null
}
const newBucket = (key: string, label = key): Bucket => ({
  key, label, orders: 0, shipped: 0, onTime: 0, late: 0, overdue: 0, pending: 0, noStock: 0, leadSum: 0, leadCount: 0, onTimeRate: null, avgLeadHours: null
})
const finishBucket = (b: Bucket) => {
  const judged = b.onTime + b.late + b.overdue
  b.onTimeRate = judged > 0 ? Math.round((b.onTime / judged) * 1000) / 10 : null
  b.avgLeadHours = b.leadCount > 0 ? Math.round((b.leadSum / b.leadCount) * 10) / 10 : null
  return b
}

const handleFunc = async (event: any, authToken: any = null) => {
  const token = authToken ?? await getTokenHelper(event)
  const query = getQuery(event)

  // Org scope: client admins may pick any org, everybody else is pinned to the cookie org
  let role: any = {}
  try { role = JSON.parse(getCookie(event, 'logship_role') ?? '{}') } catch { role = {} }
  const cookieOrgId = Number(getCookie(event, 'logship_organization_id')) || 0
  const requestedOrgId = Number(query.org_id) || 0
  const orgId = role?.IsClientAdministrator ? (requestedOrgId || cookieOrgId) : cookieOrgId
  if (!orgId) {
    setResponseStatus(event, 400)
    return { status: 400, message: 'Organization is required' }
  }

  const today = new Date()
  const to = validDate(query.to) ?? isoDate(today)
  const from = validDate(query.from) ?? isoDate(new Date(today.getTime() - 90 * 86400000))
  const noStockMode: 'exclude' | 'include' = String(query.nostock || '').toLowerCase() === 'include' ? 'include' : 'exclude'

  // ---- cut-off time ------------------------------------------------------------------------
  const org: any = await fetchHelper(event, `models/ad_org/${orgId}?$select=Name,C_BPartner_ID`, 'GET', token, null)
  const orgName = org?.Name || org?.identifier || String(orgId)
  const orgBpId = idOf(org?.C_BPartner_ID)
  let partnerCutoff: string | null = null
  if (orgBpId) {
    try {
      const bp: any = await fetchHelper(event, `models/c_bpartner/${orgBpId}?$select=Name,CutOffTime`, 'GET', token, null)
      const parsed = parseCutoff(bp?.CutOffTime ?? bp?.cutOffTime)
      if (parsed) partnerCutoff = `${pad2(parsed.h)}:${pad2(parsed.mi)}`
    } catch (err) {
      console.error('shipping-performance: partner cut-off lookup failed', err)
    }
  }
  const override = parseCutoff(query.cutoff)
  const cutoff = override ?? parseCutoff(partnerCutoff) ?? parseCutoff(DEFAULT_CUTOFF)!
  const cutoffLabel = `${pad2(cutoff.h)}:${pad2(cutoff.mi)}`
  const cutoffSource = override ? 'override' : (partnerCutoff ? 'partner' : 'default')
  const cutoffMinutes = cutoff.h * 60 + cutoff.mi

  // ---- non-working days (tenant-wide org 0 + this org) ---------------------------------------
  const nonWorking = new Set<string>()
  try {
    const nwRes: any = await fetchHelper(
      event,
      `models/cust_nonworkingdays?$filter=${string.urlEncode(`IsActive eq true and (AD_Org_ID eq 0 or AD_Org_ID eq ${orgId})`)}&$select=Name,DateFrom,DateTo&$top=500`,
      'GET', token, null
    )
    for (const r of nwRes?.records || []) {
      const start = String(r.DateFrom || '').slice(0, 10)
      const end = String(r.DateTo || r.DateFrom || '').slice(0, 10)
      if (!/^\d{4}-\d{2}-\d{2}$/.test(start)) continue
      let cur = { y: Number(start.slice(0, 4)), m: Number(start.slice(5, 7)), d: Number(start.slice(8, 10)) }
      for (let i = 0; i < 60; i++) {
        const k = dateKey(cur)
        nonWorking.add(k)
        if (k >= end) break
        cur = addDays(cur, 1)
      }
    }
  } catch (err) {
    console.error('shipping-performance: non-working days lookup failed', err)
  }
  const isWorkingDay = (p: { y: number; m: number; d: number }) => {
    const wd = weekdayOf(p)
    return wd !== 0 && wd !== 6 && !nonWorking.has(dateKey(p))
  }
  /** Count working days strictly after `a` up to and including `b` (both YYYY-MM-DD keys). */
  const workingDaysBetween = (aKey: string, bKey: string) => {
    if (bKey <= aKey) return 0
    let cur = { y: Number(aKey.slice(0, 4)), m: Number(aKey.slice(5, 7)), d: Number(aKey.slice(8, 10)) }
    let n = 0
    for (let i = 0; i < 400; i++) {
      cur = addDays(cur, 1)
      if (isWorkingDay(cur)) n++
      if (dateKey(cur) >= bKey) break
    }
    return n
  }

  // ---- orders ---------------------------------------------------------------------------------
  const orderFilter = `AD_Org_ID eq ${orgId} and IsSOTrx eq true and IsFulfillmentOrder eq true and Created ge '${from} 00:00:00' and Created le '${to} 23:59:59'`
  const orderSelect = 'DocumentNo,Created,DateOrdered,DocStatus,ExternalOrderId,C_OrderSource_ID,C_BPartner_ID,ProcessedOn,GrandTotal'
  const ordersRaw = await pageAll(
    event, token,
    `models/c_order?$filter=${string.urlEncode(orderFilter)}&$select=${string.urlEncode(orderSelect)}&$orderby=${string.urlEncode('Created asc')}`,
    MAX_ORDERS
  )
  const orders = ordersRaw.filter(o => !EXCLUDED_DOCSTATUS.has(o.DocStatus?.id || o.DocStatus))

  // ---- shipments (customer shipments created from the window start; bounded 120 d past `to`) ----
  const shipToBound = isoDate(new Date(new Date(to + 'T12:00:00Z').getTime() + 120 * 86400000))
  const shipFilter = `AD_Org_ID eq ${orgId} and IsSOTrx eq true and MovementType eq 'C-' and Created ge '${from} 00:00:00' and Created le '${shipToBound} 23:59:59'`
  const shipSelect = 'DocumentNo,C_Order_ID,Created,ProcessedOn,shipping_date,IsCommissioned,DocStatus,MovementDate,M_Shipper_ID,TrackingNo'
  const shipmentsRaw = await pageAll(
    event, token,
    `models/m_inout?$filter=${string.urlEncode(shipFilter)}&$select=${string.urlEncode(shipSelect)}&$orderby=${string.urlEncode('Created asc')}`,
    MAX_SHIPMENTS
  )

  type ShipInfo = { inOutId: number; documentNo: string; shippedAt: number | null; source: string; shipper: string; trackingNo: string }
  const shipmentsByOrder: Record<number, ShipInfo[]> = {}
  for (const s of shipmentsRaw) {
    if (EXCLUDED_DOCSTATUS.has(s.DocStatus?.id || s.DocStatus)) continue
    const orderId = idOf(s.C_Order_ID)
    if (!orderId) continue
    let shippedAt: number | null = null
    let source = ''
    if (s.shipping_date) {
      shippedAt = utcIsoToInstantMs(s.shipping_date)
      source = 'shipping_date'
    } else if ((s.IsCommissioned === true || s.IsCommissioned === 'Y') && s.ProcessedOn) {
      shippedAt = Number(s.ProcessedOn) || null
      source = 'processed'
    }
    ;(shipmentsByOrder[orderId] ||= []).push({
      inOutId: s.id,
      documentNo: s.DocumentNo || '',
      shippedAt,
      source,
      shipper: s.M_Shipper_ID?.identifier || '',
      trackingNo: s.TrackingNo || ''
    })
  }

  // ---- pass 1: deadline + first shipment + preliminary status ---------------------------------
  const nowMs = Date.now()
  const WD = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat']

  type Prelim = {
    o: any
    createdMs: number
    created: ReturnType<typeof berlinParts>
    createdMinutes: number
    deadlineKey: string
    deadlineMs: number
    first: ShipInfo | null
    anyShipment: ShipInfo | null
    ships: ShipInfo[]
    status: Status
    shippedKey: string
    daysLate: number
    leadHours: number | null
  }
  const prelim: Prelim[] = orders.map((o: any) => {
    const createdMs = idempiereLocalToInstantMs(o.Created) ?? 0
    const created = berlinParts(createdMs)
    const createdMinutes = created.h * 60 + created.mi

    // Deadline: same day if before cut-off on a working day, else next working day's cut-off
    let dl: { y: number; m: number; d: number } = { y: created.y, m: created.m, d: created.d }
    if (!(isWorkingDay(dl) && createdMinutes < cutoffMinutes)) {
      do { dl = addDays(dl, 1) } while (!isWorkingDay(dl))
    }
    const deadlineKey = dateKey(dl)
    const deadlineMs = berlinLocalToInstantMs(dl.y, dl.m, dl.d, cutoff.h, cutoff.mi, 0)
    // Shipping any time on the deadline day counts as on time → overdue only after that day ends
    const deadlineEndMs = berlinLocalToInstantMs(dl.y, dl.m, dl.d, 23, 59, 59)

    const ships = (shipmentsByOrder[o.id] || [])
    const shippedOnes = ships.filter(s => s.shippedAt != null).sort((a, b) => (a.shippedAt! - b.shippedAt!))
    const first = shippedOnes[0] || null
    const anyShipment = ships[0] || null

    let status: Status
    let shippedKey = ''
    let daysLate = 0
    let leadHours: number | null = null
    if (first) {
      const sp = berlinParts(first.shippedAt!)
      shippedKey = dateKey(sp)
      daysLate = workingDaysBetween(deadlineKey, shippedKey)
      status = shippedKey <= deadlineKey ? 'ontime' : 'late'
      leadHours = Math.max(0, Math.round(((first.shippedAt! - createdMs) / 3600000) * 10) / 10)
    } else {
      status = nowMs > deadlineEndMs ? 'overdue' : 'pending'
    }
    return { o, createdMs, created, createdMinutes, deadlineKey, deadlineMs, first, anyShipment, ships, status, shippedKey, daysLate, leadHours }
  })

  // ---- stock check (only orders that are not on time can be stock-blocked) --------------------
  const candidates = prelim.filter(p => p.status !== 'ontime').map(p => ({ orderId: Number(p.o.id), deadlineDate: p.deadlineKey }))
  const stock = await findStockBlockedOrders(candidates)
  if (stock.available) {
    console.info(`shipping-performance: stock check ${stock.checked} orders → ${stock.blocked.size} blocked in ${stock.ms} ms (org ${orgId})`)
  } else if (stock.error !== 'PG not configured') {
    console.warn(`shipping-performance: stock check unavailable – ${stock.error}`)
  }

  // ---- pass 2: final status + aggregates ----------------------------------------------------
  const months: Record<string, Bucket> = {}
  const days: Record<string, Bucket> = {}
  const hours: Record<string, Bucket> = {}
  const weekdays: Record<string, Bucket> = {}
  const sources: Record<string, Bucket> = {}
  for (let h = 0; h < 24; h++) hours[pad2(h)] = newBucket(pad2(h), `${pad2(h)}:00`)
  for (const i of [1, 2, 3, 4, 5, 6, 0]) weekdays[String(i)] = newBucket(String(i), WD[i])

  const lateness: Record<string, number> = { '0': 0, '1': 0, '2': 0, '3': 0, '4+': 0 }
  const leadHoursAll: number[] = []
  let shipped = 0, onTime = 0, late = 0, overdue = 0, pending = 0, noStock = 0, generatedNotShipped = 0
  let lateDaysSum = 0
  let beforeCutoffMeasured = 0

  const rows = prelim.map((p) => {
    const { o, createdMs, created, createdMinutes, deadlineKey, deadlineMs, first, anyShipment, ships, shippedKey, daysLate, leadHours } = p
    const shortfall = stock.blocked.get(Number(o.id))
    const stockBlocked = !!shortfall
    const status: Status = stockBlocked && noStockMode === 'exclude' ? 'nostock' : p.status
    const beforeCutoff = createdMinutes < cutoffMinutes && isWorkingDay({ y: created.y, m: created.m, d: created.d })

    // Aggregates
    const mk = monthKey(created)
    const dk = dateKey(created)
    const hk = pad2(created.h)
    const wk = String(created.weekday)
    const srcName = o.C_OrderSource_ID?.identifier || '—'
    const buckets = [
      months[mk] ||= newBucket(mk),
      days[dk] ||= newBucket(dk),
      hours[hk],
      weekdays[wk],
      sources[srcName] ||= newBucket(srcName)
    ]
    for (const b of buckets) {
      b.orders++
      if (status === 'ontime') { b.shipped++; b.onTime++ }
      else if (status === 'late') { b.shipped++; b.late++ }
      else if (status === 'overdue') b.overdue++
      else if (status === 'nostock') b.noStock++
      else b.pending++
      if (status !== 'nostock' && leadHours != null) { b.leadSum += leadHours; b.leadCount++ }
    }
    if (status === 'ontime') { shipped++; onTime++; lateness['0']++ }
    else if (status === 'late') {
      shipped++; late++; lateDaysSum += daysLate
      lateness[daysLate >= 4 ? '4+' : String(Math.max(1, daysLate))]++
    }
    else if (status === 'overdue') overdue++
    else if (status === 'nostock') noStock++
    else {
      pending++
    }
    if (status !== 'nostock') {
      if (!first && anyShipment) generatedNotShipped++
      if (leadHours != null) leadHoursAll.push(leadHours)
      if (beforeCutoff) beforeCutoffMeasured++
    }

    return {
      orderId: o.id,
      documentNo: o.DocumentNo || '',
      externalOrderId: o.ExternalOrderId || '',
      orderSource: srcName,
      bpartnerName: o.C_BPartner_ID?.identifier || '',
      docStatus: o.DocStatus?.id || o.DocStatus || '',
      createdAt: createdMs,
      createdLocal: `${dk} ${pad2(created.h)}:${pad2(created.mi)}`,
      createdWeekday: WD[created.weekday],
      beforeCutoff,
      deadlineDate: deadlineKey,
      deadlineAt: deadlineMs,
      shippedAt: first?.shippedAt ?? null,
      shippedDate: shippedKey,
      shippedSource: first?.source || '',
      inOutId: first?.inOutId ?? anyShipment?.inOutId ?? null,
      shipmentDocumentNo: first?.documentNo || anyShipment?.documentNo || '',
      shipper: first?.shipper || anyShipment?.shipper || '',
      trackingNo: first?.trackingNo || anyShipment?.trackingNo || '',
      shipmentCount: ships.length,
      status,
      slaStatus: p.status,                 // status without the stock rule (what 'include' mode shows)
      stockBlocked,
      stockNote: formatShortfall(shortfall),
      daysLate,
      leadHours
    }
  })

  const sorted = [...leadHoursAll].sort((a, b) => a - b)
  const pct = (p: number) => sorted.length ? sorted[Math.min(sorted.length - 1, Math.floor(p * (sorted.length - 1)))] : null
  const judged = onTime + late + overdue
  const measured = rows.length - noStock
  const round1 = (v: number) => Math.round(v * 10) / 10

  return {
    orgId,
    orgName,
    from,
    to,
    cutoff: cutoffLabel,
    cutoffSource,
    partnerCutoff,
    timezone: 'Europe/Berlin',
    nonWorkingDays: Array.from(nonWorking).filter(k => k >= from).sort(),
    truncated: ordersRaw.length >= MAX_ORDERS,
    noStockMode,
    stockCheck: stock.available ? 'ok' : 'unavailable',
    stockCheckMs: stock.ms,
    stockChecked: stock.checked,
    summary: {
      orders: rows.length,
      measured,                                                                     // orders counted in the rates
      shipped,
      onTime,
      late,
      overdue,
      pending,
      noStock,
      stockBlocked: rows.filter(r => r.stockBlocked).length,                        // also in 'include' mode
      generatedNotShipped,
      onTimeRate: shipped > 0 ? round1((onTime / shipped) * 100) : null,          // of shipped orders
      slaRate: judged > 0 ? round1((onTime / judged) * 100) : null,               // incl. overdue unshipped
      avgLeadHours: sorted.length ? round1(sorted.reduce((a, b) => a + b, 0) / sorted.length) : null,
      medianLeadHours: pct(0.5),
      p90LeadHours: pct(0.9),
      avgDaysLate: late > 0 ? round1(lateDaysSum / late) : null,
      beforeCutoffShare: measured ? round1((beforeCutoffMeasured / measured) * 100) : null
    },
    months: Object.values(months).sort((a, b) => a.key.localeCompare(b.key)).map(finishBucket),
    days: Object.values(days).sort((a, b) => a.key.localeCompare(b.key)).map(finishBucket),
    hours: Object.values(hours).map(finishBucket),
    weekdays: [1, 2, 3, 4, 5, 6, 0].map(i => finishBucket(weekdays[String(i)])),
    sources: Object.values(sources).sort((a, b) => b.orders - a.orders).map(finishBucket),
    lateness: [
      { key: '0', label: 'On time', count: lateness['0'] },
      { key: '1', label: '1 day late', count: lateness['1'] },
      { key: '2', label: '2 days late', count: lateness['2'] },
      { key: '3', label: '3 days late', count: lateness['3'] },
      { key: '4+', label: '4+ days late', count: lateness['4+'] }
    ],
    rows
  }
}

export default defineEventHandler(async (event) => {
  let data: any = {}
  try {
    data = await handleFunc(event)
  } catch (err: any) {
    try {
      const authToken = await refreshTokenHelper(event)
      data = await handleFunc(event, authToken)
    } catch (error: any) {
      data = errorHandlingHelper(err?.data ?? err, error?.data ?? error)
      forceLogoutHelper(event, data)
    }
  }
  return data
})