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

type EventRow = {
  id?: number
  SessionUID?: string
  ActionType?: string
  ActionTime?: string
  DurationMs?: number | string
  Qty?: number | string
  ShippingService?: string
  IsActive?: boolean | string
  AD_User_ID?: any
  AD_Org_ID?: any
  Foreign_AD_Org_ID?: any
  M_InOut_ID?: any
  C_Order_ID?: any
  M_Product_ID?: any
  C_BPartner_ID?: any
  Created?: string
  Description?: string
}

const ISO_DATE_RE = /^\d{4}-\d{2}-\d{2}$/

function getDateRange(period: string, dateFrom?: string, dateTo?: string) {
  // Custom range overrides the period preset when both ends are valid yyyy-mm-dd.
  if (dateFrom && dateTo && ISO_DATE_RE.test(dateFrom) && ISO_DATE_RE.test(dateTo)) {
    const from = dateFrom <= dateTo ? dateFrom : dateTo
    const to = dateFrom <= dateTo ? dateTo : dateFrom
    return { startDate: from + ' 00:00:00', endDate: to + ' 23:59:59' }
  }

  const now = new Date()
  const fmt = (d: Date) => d.toISOString().split('T')[0]

  if (period === 'today') {
    const today = fmt(now)
    return { startDate: today + ' 00:00:00', endDate: today + ' 23:59:59' }
  }
  if (period === 'yesterday') {
    const y = new Date(now); y.setDate(y.getDate() - 1)
    return { startDate: fmt(y) + ' 00:00:00', endDate: fmt(y) + ' 23:59:59' }
  }
  if (period === 'last_month') {
    const s = new Date(now); s.setDate(s.getDate() - 30)
    return { startDate: fmt(s) + ' 00:00:00', endDate: fmt(now) + ' 23:59:59' }
  }
  if (period === 'last_quarter') {
    const s = new Date(now); s.setDate(s.getDate() - 90)
    return { startDate: fmt(s) + ' 00:00:00', endDate: fmt(now) + ' 23:59:59' }
  }
  if (period === 'last_year') {
    const s = new Date(now); s.setFullYear(s.getFullYear() - 1)
    return { startDate: fmt(s) + ' 00:00:00', endDate: fmt(now) + ' 23:59:59' }
  }
  // default last_week (7 days)
  const s = new Date(now); s.setDate(s.getDate() - 7)
  return { startDate: fmt(s) + ' 00:00:00', endDate: fmt(now) + ' 23:59:59' }
}

const fkId = (v: any): number | null => {
  if (v == null) return null
  if (typeof v === 'number') return Number.isFinite(v) ? v : null
  if (typeof v === 'object' && v.id != null) return Number(v.id) || null
  const n = Number(v)
  return Number.isFinite(n) ? n : null
}
const fkName = (v: any, fallback = ''): string => {
  if (v && typeof v === 'object' && v.identifier) return String(v.identifier)
  return fallback
}
const isActiveY = (v: any): boolean => {
  if (typeof v === 'boolean') return v
  if (typeof v === 'string') return v === 'Y' || v.toLowerCase() === 'true'
  return false
}
const n = (v: any): number => {
  const x = Number(v)
  return Number.isFinite(x) ? x : 0
}

async function fetchRows(event: any, token: string, filter: string, top = 5000): Promise<EventRow[]> {
  const encoded = string.urlEncode(filter)
  // Order by Created (iDempiere's auto-stamped row creation time, present on
  // every row) instead of ActionTime — see filter clauses below for context.
  const url = `models/cust_commissionactivity?$filter=${encoded}&$orderby=Created asc&$top=${top}`
  const res: any = await fetchHelper(event, url, 'GET', token, null)
  return Array.isArray(res?.records) ? res.records as EventRow[] : []
}

function median(values: number[]): number {
  if (!values.length) return 0
  const s = [...values].sort((a, b) => a - b)
  const m = Math.floor(s.length / 2)
  return s.length % 2 ? s[m] : (s[m - 1] + s[m]) / 2
}

function aggregate(active: EventRow[], abandoned: EventRow[]) {
  // Completed sessions: rows with ActionType='session_complete'
  const complete = active.filter((r) => r.ActionType === 'session_complete')

  // Shipment/order/customer FKs are only attached to the session_start row
  // (see composables/useCommissionActivity.ts → start()). Index them so the
  // recentSessions panel can backfill the completion row.
  const startBySession = new Map<string, EventRow>()
  for (const r of active) {
    if (r.ActionType === 'session_start' && r.SessionUID) startBySession.set(r.SessionUID, r)
  }

  // summary
  const durations = complete.map((r) => n(r.DurationMs)).filter((x) => x > 0)
  const totalMs = durations.reduce((a, b) => a + b, 0)
  const abandonedSessionUIDs = new Set<string>()
  for (const r of abandoned) {
    if (r.ActionType === 'session_start' && r.SessionUID) abandonedSessionUIDs.add(r.SessionUID)
  }
  const abandonedCount = abandonedSessionUIDs.size
  const completedCount = complete.length
  const totalSessions = completedCount + abandonedCount
  const abandonRate = totalSessions ? abandonedCount / totalSessions : 0

  // Label failures
  const labelFailures = active.filter((r) => r.ActionType === 'label_failed').length

  // shipmentsByDay
  const byDay = new Map<string, { shipments: number; totalMs: number }>()
  for (const r of complete) {
    const day = String(r.ActionTime || r.Created || '').slice(0, 10)
    if (!day) continue
    const cur = byDay.get(day) ?? { shipments: 0, totalMs: 0 }
    cur.shipments += 1
    cur.totalMs += n(r.DurationMs)
    byDay.set(day, cur)
  }
  const shipmentsByDay = Array.from(byDay.entries())
    .sort(([a], [b]) => (a < b ? -1 : a > b ? 1 : 0))
    .map(([day, v]) => ({
      day,
      shipments: v.shipments,
      avgMinutes: v.shipments ? +(v.totalMs / v.shipments / 60000).toFixed(2) : 0,
    }))

  // shipmentsByDayByUser — counts of completed shipments per (day, user)
  // Returned as a flat row-per-day shape ready for a stacked column chart:
  //   rows: [{ day: '2026-04-20', 'Alice': 5, 'Bob': 3 }, ...]
  //   users: ['Alice', 'Bob']  (stable column order, sorted by total desc)
  const dayUserCounts = new Map<string, Map<string, number>>()
  const userTotals = new Map<string, number>()
  for (const r of complete) {
    const day = String(r.ActionTime || r.Created || '').slice(0, 10)
    if (!day) continue
    const uid = fkId(r.AD_User_ID)
    const userLabel = fkName(r.AD_User_ID, uid != null ? `User ${uid}` : 'Unknown')
    const dayMap = dayUserCounts.get(day) ?? new Map<string, number>()
    dayMap.set(userLabel, (dayMap.get(userLabel) ?? 0) + 1)
    dayUserCounts.set(day, dayMap)
    userTotals.set(userLabel, (userTotals.get(userLabel) ?? 0) + 1)
  }
  const usersOrdered = Array.from(userTotals.entries())
    .sort((a, b) => b[1] - a[1])
    .map(([name]) => name)
  const shipmentsByDayByUser = {
    users: usersOrdered,
    rows: Array.from(dayUserCounts.entries())
      .sort(([a], [b]) => (a < b ? -1 : a > b ? 1 : 0))
      .map(([day, dayMap]) => {
        const row: Record<string, any> = { day }
        for (const u of usersOrdered) row[u] = dayMap.get(u) ?? 0
        return row
      }),
  }

  // shipmentsByUser
  const byUser = new Map<number, { name: string; durations: number[] }>()
  for (const r of complete) {
    const uid = fkId(r.AD_User_ID)
    if (uid == null) continue
    const entry = byUser.get(uid) ?? { name: fkName(r.AD_User_ID, `User ${uid}`), durations: [] }
    if (!entry.name && fkName(r.AD_User_ID)) entry.name = fkName(r.AD_User_ID)
    entry.durations.push(n(r.DurationMs))
    byUser.set(uid, entry)
  }
  const shipmentsByUser = Array.from(byUser.entries())
    .map(([adUserId, v]) => {
      const sum = v.durations.reduce((a, b) => a + b, 0)
      const avgMs = v.durations.length ? Math.round(sum / v.durations.length) : 0
      return {
        adUserId,
        userName: v.name,
        shipments: v.durations.length,
        avgMs,
        avgMinutes: v.durations.length ? +(sum / v.durations.length / 60000).toFixed(2) : 0,
        medianMinutes: +((median(v.durations) / 60000).toFixed(2)),
      }
    })
    .sort((a, b) => b.shipments - a.shipments)

  // slowestProducts: avg (time between previous event and this item_scanned), grouped by product
  const bySession = new Map<string, EventRow[]>()
  for (const r of active) {
    if (!r.SessionUID) continue
    const arr = bySession.get(r.SessionUID) ?? []
    arr.push(r)
    bySession.set(r.SessionUID, arr)
  }
  const productPickMs = new Map<number, { name: string; deltas: number[] }>()
  for (const rows of bySession.values()) {
    rows.sort((a, b) => String(a.ActionTime ?? a.Created ?? '').localeCompare(String(b.ActionTime ?? b.Created ?? '')))
    for (let i = 0; i < rows.length; i += 1) {
      const r = rows[i]
      if (r.ActionType !== 'item_scanned') continue
      const prev = rows[i - 1]
      if (!prev) continue
      const delta = n(r.DurationMs) - n(prev.DurationMs)
      if (delta <= 0 || delta > 10 * 60 * 1000) continue // clamp: >0 and <10min
      const pid = fkId(r.M_Product_ID)
      if (pid == null) continue
      const entry = productPickMs.get(pid) ?? { name: fkName(r.M_Product_ID, `#${pid}`), deltas: [] }
      if (!entry.name && fkName(r.M_Product_ID)) entry.name = fkName(r.M_Product_ID)
      entry.deltas.push(delta)
      productPickMs.set(pid, entry)
    }
  }
  const slowestProducts = Array.from(productPickMs.entries())
    .map(([mProductId, v]) => ({
      mProductId,
      productName: v.name,
      picks: v.deltas.length,
      avgSeconds: v.deltas.length ? +((v.deltas.reduce((a, b) => a + b, 0) / v.deltas.length) / 1000).toFixed(2) : 0,
    }))
    .filter((p) => p.picks >= 2) // ignore products with only 1 sample
    .sort((a, b) => b.avgSeconds - a.avgSeconds)
    .slice(0, 10)

  // carrierStats
  const byCarrier = new Map<string, { requested: number; succeeded: number; failed: number }>()
  for (const r of active) {
    if (r.ActionType !== 'label_requested' && r.ActionType !== 'label_succeeded' && r.ActionType !== 'label_failed') continue
    const svc = String(r.ShippingService || 'unknown')
    const cur = byCarrier.get(svc) ?? { requested: 0, succeeded: 0, failed: 0 }
    if (r.ActionType === 'label_requested') cur.requested += 1
    else if (r.ActionType === 'label_succeeded') cur.succeeded += 1
    else if (r.ActionType === 'label_failed') cur.failed += 1
    byCarrier.set(svc, cur)
  }
  const carrierStats = Array.from(byCarrier.entries())
    .map(([service, v]) => ({
      service,
      requested: v.requested,
      succeeded: v.succeeded,
      failed: v.failed,
      failureRate: v.requested ? +(v.failed / v.requested).toFixed(3) : 0,
    }))
    .sort((a, b) => b.requested - a.requested)

  // Client-org (Foreign_AD_Org_ID) segmentation — who the shipment is for,
  // not who the user is logged in as. Pulled from session_start rows since
  // label/completion rows don't carry the FK.
  type ClientOrgAgg = {
    id: number
    name: string
    shipments: number
    durations: number[]
    labelRequested: number
    labelSucceeded: number
    labelFailed: number
  }
  const byClientOrg = new Map<number, ClientOrgAgg>()
  const ensureOrgAgg = (org: any): ClientOrgAgg | null => {
    const id = fkId(org)
    if (id == null) return null
    let a = byClientOrg.get(id)
    if (!a) {
      a = { id, name: fkName(org, `Org ${id}`), shipments: 0, durations: [], labelRequested: 0, labelSucceeded: 0, labelFailed: 0 }
      byClientOrg.set(id, a)
    } else if (!a.name && fkName(org)) {
      a.name = fkName(org)
    }
    return a
  }
  // completed shipments per client org
  for (const r of complete) {
    const start = r.SessionUID ? startBySession.get(r.SessionUID) : undefined
    const a = ensureOrgAgg(r.Foreign_AD_Org_ID ?? start?.Foreign_AD_Org_ID)
    if (!a) continue
    a.shipments += 1
    a.durations.push(n(r.DurationMs))
  }
  // label events per client org
  for (const r of active) {
    if (r.ActionType !== 'label_requested' && r.ActionType !== 'label_succeeded' && r.ActionType !== 'label_failed') continue
    const start = r.SessionUID ? startBySession.get(r.SessionUID) : undefined
    const a = ensureOrgAgg(r.Foreign_AD_Org_ID ?? start?.Foreign_AD_Org_ID)
    if (!a) continue
    if (r.ActionType === 'label_requested') a.labelRequested += 1
    else if (r.ActionType === 'label_succeeded') a.labelSucceeded += 1
    else if (r.ActionType === 'label_failed') a.labelFailed += 1
  }
  const clientOrgStats = Array.from(byClientOrg.values())
    .map((a) => {
      const sum = a.durations.reduce((x, y) => x + y, 0)
      const avgMs = a.durations.length ? Math.round(sum / a.durations.length) : 0
      return {
        foreignAdOrgId: a.id,
        orgName: a.name,
        shipments: a.shipments,
        avgMs,
        avgMinutes: a.durations.length ? +(sum / a.durations.length / 60000).toFixed(2) : 0,
        labelRequested: a.labelRequested,
        labelSucceeded: a.labelSucceeded,
        labelFailed: a.labelFailed,
        failureRate: a.labelRequested ? +(a.labelFailed / a.labelRequested).toFixed(3) : 0,
      }
    })
    .sort((a, b) => b.shipments - a.shipments || b.labelRequested - a.labelRequested)

  // recentSessions (latest 50). Backfill FKs from the session_start row
  // because session_complete itself is tracked with no payload.
  const recentSessions = complete
    .slice()
    .sort((a, b) => String(b.ActionTime ?? b.Created ?? '').localeCompare(String(a.ActionTime ?? a.Created ?? '')))
    .slice(0, 50)
    .map((r) => {
      const start = r.SessionUID ? startBySession.get(r.SessionUID) : undefined
      const pick = <K extends keyof EventRow>(field: K) => (r[field] ?? start?.[field]) as EventRow[K]
      return {
        sessionUID: r.SessionUID,
        adUserId: fkId(pick('AD_User_ID')),
        userName: fkName(pick('AD_User_ID')),
        mInoutId: fkId(pick('M_InOut_ID')),
        shipmentNo: fkName(pick('M_InOut_ID')),
        cOrderId: fkId(pick('C_Order_ID')),
        orderNo: fkName(pick('C_Order_ID')),
        cBPartnerId: fkId(pick('C_BPartner_ID')),
        partnerName: fkName(pick('C_BPartner_ID')),
        foreignAdOrgId: fkId(pick('Foreign_AD_Org_ID')),
        foreignOrgName: fkName(pick('Foreign_AD_Org_ID')),
        durationMs: n(r.DurationMs),
        completedAt: r.ActionTime || r.Created || null,
      }
    })

  return {
    summary: {
      shipments: completedCount,
      avgMs: completedCount ? Math.round(totalMs / completedCount) : 0,
      avgMinutes: completedCount ? +(totalMs / completedCount / 60000).toFixed(2) : 0,
      totalMs,
      totalMinutes: +(totalMs / 60000).toFixed(2),
      abandonedSessions: abandonedCount,
      abandonRate: +abandonRate.toFixed(3),
      labelFailures,
    },
    shipmentsByDay,
    shipmentsByDayByUser,
    shipmentsByUser,
    slowestProducts,
    carrierStats,
    clientOrgStats,
    recentSessions,
  }
}

const handleFunc = async (event: any, authToken: any = null) => {
  const token = authToken ?? await getTokenHelper(event)
  const q = getQuery(event)
  const role = getCookie(event, 'logship_role')
  const cookieOrgId = getCookie(event, 'logship_organization_id')
  const newUserRole = JSON.parse(role ?? '{}')

  const period = String(q?.period || 'last_week')
  const dateFromParam = q?.date_from ? String(q.date_from) : ''
  const dateToParam = q?.date_to ? String(q.date_to) : ''
  const orgIdParam = q?.org_id ? String(q.org_id) : ''
  const userIdParam = q?.user_id ? Number(q.user_id) : null
  const service = q?.shipping_service ? String(q.shipping_service) : null
  const foreignOrgIdParam = q?.foreign_org_id ? Number(q.foreign_org_id) : null

  // Only clients admin can cross-org; others are forced to their cookie org
  let effectiveOrgId = cookieOrgId
  if (newUserRole?.IsClientAdministrator && orgIdParam) effectiveOrgId = orgIdParam

  const { startDate, endDate } = getDateRange(period, dateFromParam, dateToParam)

  const clauses: string[] = []
  if (effectiveOrgId) clauses.push(`AD_Org_ID eq ${Number(effectiveOrgId)}`)
  // Filter on Created (iDempiere auto-stamp on every row) rather than
  // ActionTime — the latter is currently NULL on every row due to a write
  // issue in server/api/commission/activity.post.ts (see toIDempiereTs).
  // Created lags real event time by ≤4s (the client-side flush interval),
  // which is well within tolerance for day-bucketed analytics.
  clauses.push(`Created ge '${startDate}'`)
  clauses.push(`Created le '${endDate}'`)
  if (userIdParam) clauses.push(`AD_User_ID eq ${userIdParam}`)
  if (service) clauses.push(`ShippingService eq '${service}'`)
  if (foreignOrgIdParam) clauses.push(`Foreign_AD_Org_ID eq ${foreignOrgIdParam}`)

  const activeFilter = [...clauses, `IsActive eq true`].join(' AND ')
  const abandonedFilter = [...clauses, `IsActive eq false`].join(' AND ')

  const [active, abandoned] = await Promise.all([
    fetchRows(event, token, activeFilter).catch(() => []),
    fetchRows(event, token, abandonedFilter).catch(() => []),
  ])

  const result = aggregate(active, abandoned)
  await enrichProductNames(event, token, result.slowestProducts)
  return result
}

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
})