import { string } from 'alga-js'
import refreshTokenHelper from "../../../utils/refreshTokenHelper"
import forceLogoutHelper from "../../../utils/forceLogoutHelper"
import errorHandlingHelper from "../../../utils/errorHandlingHelper"
import fetchHelper from "../../../utils/fetchHelper"
import getTokenHelper from "../../../utils/getTokenHelper"
import { fetchProductsWithStorage } from '../../../utils/productStorageHelper'
import getReturnLocatorIds from '../../../utils/returnLocatorHelper'

/**
 * Point-in-time (PIT) Qty On Hand.
 *
 * Computes what each product's on-hand quantity was at the END of a given day,
 * using the backward method:
 *
 *   pitQty = currentQty (from M_Storage) - SUM(M_Transaction.MovementQty after date)
 *
 * MovementQty is signed (positive = inbound, negative = outbound) and the sum of ALL
 * transactions for a product exactly reconciles with the current M_Storage qty, so
 * subtracting only the movements that happened after the selected date rolls the stock
 * back to that date. For recent dates this fetches very few transaction rows.
 *
 * IMPORTANT: transactions are NOT filtered by organization. A product owned by org A
 * can be moved in/out of a warehouse owned by org B, and current storage counts those
 * movements, so the after-date sum must include them too (otherwise the rollback is wrong).
 */

const extractId = (value: any): number | null => {
  if (typeof value === 'number') return value
  if (value?.id) return value.id
  return null
}

// Sum all M_Transaction.MovementQty dated AFTER `date`, keyed by product (always) and
// by product+locator (only when needed for the by-locator view). Paginated on the PK
// for stable ordering across pages.
const fetchAfterDateSums = async (
  event: any,
  token: any,
  date: string,
  byLocator: boolean,
  returnLocatorIds: Set<number>
) => {
  const byProduct = new Map<number, number>()
  // Movements after `date` that touched a return (non-shippable) locator, per product —
  // lets the aggregated view roll the return portion back independently.
  const byProductReturn = new Map<number, number>()
  // key: `${productId}#${locatorId|'none'}` -> { qty, locatorId }
  const byProductLocator = new Map<string, { qty: number, locatorId: number | null }>()

  const pageSize = 5000
  const maxPages = 50 // safety backstop (250k rows); real volume is far lower
  const dateFilter = string.urlEncode(`MovementDate gt '${date}'`)
  const select = string.urlEncode('M_Product_ID,M_Locator_ID,MovementQty')
  const orderby = string.urlEncode('M_Transaction_ID asc')

  let skip = 0
  for (let page = 0; page < maxPages; page++) {
    const res: any = await fetchHelper(
      event,
      `models/m_transaction?$select=${select}&$filter=${dateFilter}&$orderby=${orderby}&$top=${pageSize}&$skip=${skip}`,
      'GET',
      token,
      null
    )

    const recs: any[] = res?.records || []
    if (recs.length === 0) break

    for (const tx of recs) {
      const productId = extractId(tx.M_Product_ID)
      if (!productId) continue
      const qty = Number(tx.MovementQty || 0)

      byProduct.set(productId, (byProduct.get(productId) || 0) + qty)

      const locatorId = extractId(tx.M_Locator_ID)
      if (locatorId !== null && returnLocatorIds.has(locatorId)) {
        byProductReturn.set(productId, (byProductReturn.get(productId) || 0) + qty)
      }

      if (byLocator) {
        const key = `${productId}#${locatorId ?? 'none'}`
        const existing = byProductLocator.get(key)
        if (existing) {
          existing.qty += qty
        } else {
          byProductLocator.set(key, { qty, locatorId })
        }
      }
    }

    if (recs.length < pageSize) break
    skip += pageSize
  }

  return { byProduct, byProductReturn, byProductLocator }
}

const handleFunc = async (event: any, authToken: any = null) => {
  let data: any = { records: [], 'row-count': 0 }
  const token = authToken ?? await getTokenHelper(event)
  const query = getQuery(event)
  const orgId = query?.org_id as string
  const date = (query?.date as string || '').trim()
  const byLocator = query?.by_locator === 'true' || query?.by_locator === true

  if (!orgId) {
    return { status: 400, message: 'Organization ID is required', records: [], 'row-count': 0 }
  }
  if (!date || !/^\d{4}-\d{2}-\d{2}$/.test(date)) {
    return { status: 400, message: 'A valid date (YYYY-MM-DD) is required', records: [], 'row-count': 0 }
  }

  // Build filter for products owned by organization (same scope as the live overview)
  const orgFilter = string.urlEncode(`AD_Org_ID eq ${orgId} and IsActive eq true`)

  // For the by-locator view we need locator coordinates / type / priority.
  const locatorMap = new Map<number, any>()
  if (byLocator) {
    try {
      const locatorOrgFilter = string.urlEncode(`AD_Org_ID eq ${orgId}`)
      const locatorRes: any = await fetchHelper(
        event,
        `models/m_locator?$filter=${locatorOrgFilter}&$expand=M_LocatorType_ID&$top=5000`,
        'GET',
        token,
        null
      )
      if (locatorRes?.records) {
        for (const locator of locatorRes.records) {
          locatorMap.set(locator.id, locator)
        }
      }
    } catch (err) {
      console.error('[qty-on-hand-pit] Error fetching locators:', err)
      // Continue without locator details — cross-org / unmapped locators fall back to blank coords
    }
  }

  // Fetch products with current storage (same query as the live overview).
  const res: any = await fetchProductsWithStorage(
    event,
    token,
    `models/m_product?$select=${string.urlEncode('M_Product_ID,Name,SKU,UPC,Value,mpn,AD_Org_ID')}&$expand=AD_Org_ID($select=Name)&$filter=${orgFilter}&$orderby=${string.urlEncode('Name asc')}&$top=5000`
  )

  if (!res?.records) {
    return data
  }

  // Return locators (all orgs — stock may sit in another org's warehouse). Cached; fail-soft.
  const returnLocatorIds = await getReturnLocatorIds(event, token, null)

  // Roll-back deltas: everything that moved AFTER the selected date.
  const { byProduct, byProductReturn, byProductLocator } = await fetchAfterDateSums(event, token, date, byLocator, returnLocatorIds)

  let transformedRecords: any[]

  if (byLocator) {
    // Group current storage by product + locator id.
    const groupedMap = new Map<string, any>()
    const productMeta = new Map<number, any>()

    for (const product of res.records) {
      const storages = product?.M_Storage || []
      const productId = product.M_Product_ID || product.id
      const orgName = product.AD_Org_ID?.Name || ''
      const orgIdVal = product.AD_Org_ID?.id || orgId

      productMeta.set(productId, {
        name: product.Name || '',
        sku: product.SKU || '',
        ean: product.UPC || '',
        value: product.Value || '',
        mpn: product.mpn || '',
        organization: orgName,
        organizationId: orgIdVal
      })

      const buildRow = (locatorId: number | null, currentQty: number) => {
        const locator = locatorId ? locatorMap.get(locatorId) : null
        return {
          id: `${productId}_${locatorId || 'no_locator'}`,
          productId,
          locatorId: locatorId || null,
          isReturn: !!locatorId && returnLocatorIds.has(locatorId),
          name: product.Name || '',
          sku: product.SKU || '',
          ean: product.UPC || '',
          value: product.Value || '',
          mpn: product.mpn || '',
          currentQtyOnHand: currentQty,
          qtyOnHand: currentQty, // adjusted below
          locatorX: locator?.X || '',
          locatorY: locator?.Y || '',
          locatorZ: locator?.Z || '',
          locatorValue: locator?.Value || '',
          locatorPriority: locator?.PriorityNo ?? null,
          locatorTypeId: locator?.M_LocatorType_ID?.id || null,
          locatorTypeName: locator?.M_LocatorType_ID?.Name || '',
          organization: orgName,
          organizationId: orgIdVal
        }
      }

      if (Array.isArray(storages) && storages.length > 0) {
        for (const storage of storages) {
          const locatorId = storage?.M_Locator_ID?.id || null
          const qtyOnHand = Number(storage?.QtyOnHand || 0)
          const groupKey = `${productId}#${locatorId ?? 'none'}`
          if (groupedMap.has(groupKey)) {
            const existing = groupedMap.get(groupKey)
            existing.currentQtyOnHand += qtyOnHand
            existing.qtyOnHand += qtyOnHand
          } else {
            groupedMap.set(groupKey, buildRow(locatorId, qtyOnHand))
          }
        }
      } else {
        const groupKey = `${productId}#none`
        if (!groupedMap.has(groupKey)) {
          groupedMap.set(groupKey, buildRow(null, 0))
        }
      }
    }

    // Apply the roll-back to each current product+locator group, and create synthetic
    // groups for product+locator pairs that had movement after the date but no current
    // storage row (stock fully left that locator since the selected date).
    for (const [key, after] of byProductLocator.entries()) {
      const [pidStr] = key.split('#')
      const productId = Number(pidStr)
      const meta = productMeta.get(productId)
      if (!meta) continue // product not in this org's active set — ignore

      if (groupedMap.has(key)) {
        groupedMap.get(key).qtyOnHand -= after.qty
      } else {
        const locatorId = after.locatorId
        const locator = locatorId ? locatorMap.get(locatorId) : null
        groupedMap.set(key, {
          id: `${productId}_${locatorId || 'no_locator'}`,
          productId,
          locatorId: locatorId || null,
          isReturn: !!locatorId && returnLocatorIds.has(locatorId),
          name: meta.name,
          sku: meta.sku,
          ean: meta.ean,
          value: meta.value,
          mpn: meta.mpn,
          currentQtyOnHand: 0,
          qtyOnHand: 0 - after.qty,
          locatorX: locator?.X || '',
          locatorY: locator?.Y || '',
          locatorZ: locator?.Z || '',
          locatorValue: locator?.Value || '',
          locatorPriority: locator?.PriorityNo ?? null,
          locatorTypeId: locator?.M_LocatorType_ID?.id || null,
          locatorTypeName: locator?.M_LocatorType_ID?.Name || '',
          organization: meta.organization,
          organizationId: meta.organizationId
        })
      }
    }

    transformedRecords = Array.from(groupedMap.values())
  } else {
    // Aggregated: one row per product, current qty summed across all locators.
    transformedRecords = res.records.map((product: any) => {
      const storages = product?.M_Storage || []
      const productId = product.M_Product_ID || product.id
      let currentQty = 0
      let currentReturnQty = 0
      if (Array.isArray(storages)) {
        for (const storage of storages) {
          const qty = Number(storage?.QtyOnHand || 0)
          currentQty += qty
          const locatorId = Number(storage?.M_Locator_ID?.id ?? storage?.M_Locator_ID)
          if (Number.isFinite(locatorId) && returnLocatorIds.has(locatorId)) currentReturnQty += qty
        }
      }
      const afterQty = byProduct.get(productId) || 0
      const afterReturnQty = byProductReturn.get(productId) || 0
      return {
        id: product.id || product.M_Product_ID,
        productId,
        name: product.Name || '',
        sku: product.SKU || '',
        ean: product.UPC || '',
        value: product.Value || '',
        mpn: product.mpn || '',
        currentQtyOnHand: currentQty,
        currentReturnQtyOnHand: currentReturnQty,
        qtyOnHand: currentQty - afterQty,
        returnQtyOnHand: currentReturnQty - afterReturnQty,
        organization: product.AD_Org_ID?.Name || '',
        organizationId: product.AD_Org_ID?.id || orgId
      }
    })
  }

  data = {
    status: 200,
    asOfDate: date,
    records: transformedRecords,
    'row-count': transformedRecords.length
  }

  return data
}

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

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

  return data
})