// Stock-at-deadline check for the shipping-performance SLA (direct Postgres).
//
// Question answered per order: "could the warehouse have shipped this order on its deadline day?"
// For every item line (m_product.ProductType = 'I', QtyOrdered > 0) we compute the on-hand quantity
// of the product in the ORDER'S warehouse at the END of the deadline day, restricted to locators
// whose type allows shipping / replenishment / reservation (same rule as the dashboard SameDay card).
//
// Historic stock = current m_storage.QtyOnHand minus every m_transaction.MovementQty dated AFTER
// the deadline day (backward method, identical to materials/products/qty-on-hand-pit.get.ts: the sum
// of all transactions of a product reconciles exactly with its current storage).
//
// Why end of the deadline day: the order was NOT shipped that day, so none of its own demand was
// consumed; whatever was left at the end of that day is exactly what was available to it (orders
// that did ship that day are already deducted). Deadlines in the future have no later movements →
// current stock.
//
// Rule (user decision 2026-10-27): an order is stock-blocked when AT LEAST ONE line needed more than
// was on hand (`needed > onHand`). QtyOrdered is used (not QtyOrdered - QtyDelivered) because the
// judgement point is before the first shipment.
//
// Fail-soft: without PG credentials or on any query error `available` is false and the caller keeps
// its unmodified classification.
import { Pool } from 'pg'

export type StockShortfallLine = { productId: number; sku: string; needed: number; onHand: number }
export type StockShortfall = { orderId: number; lines: StockShortfallLine[] }
export type StockCheckResult = {
  available: boolean
  blocked: Map<number, StockShortfall>
  ms: number
  checked: number
  error?: string
}

const CHUNK = 500
const STATEMENT_TIMEOUT_MS = 30000

// Shared pool (survives HMR via globalThis, like telephony/cdr.ts)
const g = globalThis as any
const getPool = (): Pool | null => {
  if (g.__biStockPool) return g.__biStockPool as Pool
  const config = useRuntimeConfig()
  if (!config.pgHost || !config.pgDatabase || !config.pgUser || !config.pgPassword) return null
  const pool = new Pool({
    host: config.pgHost,
    port: parseInt(config.pgPort || '5432'),
    database: config.pgDatabase,
    user: config.pgUser,
    password: config.pgPassword,
    max: 4,
    idleTimeoutMillis: 60000,
    statement_timeout: STATEMENT_TIMEOUT_MS
  })
  pool.on('error', (err) => console.error('stockAtDeadline: pool error', err))
  g.__biStockPool = pool
  return pool
}

const SHIPPABLE_LOCATOR = (lt: string) =>
  `(${lt}.m_locatortype_id IS NULL OR ${lt}.isavailableforshipping = 'Y' OR ${lt}.isavailableforreplenishment = 'Y' OR ${lt}.isavailableforreservation = 'Y')`

// $1 = int[] order ids, $2 = date[] deadlines (parallel arrays)
const SQL = `
  WITH req AS (
    SELECT c_order_id, deadline FROM unnest($1::int[], $2::date[]) AS t(c_order_id, deadline)
  ),
  lines AS (
    SELECT r.c_order_id, r.deadline, o.m_warehouse_id, ol.m_product_id,
           COALESCE(NULLIF(p.sku, ''), p.value) AS sku,
           SUM(ol.qtyordered) AS needed
    FROM req r
    JOIN c_order o      ON o.c_order_id = r.c_order_id
    JOIN c_orderline ol ON ol.c_order_id = o.c_order_id
    JOIN m_product p    ON p.m_product_id = ol.m_product_id
    WHERE p.producttype = 'I'
      AND ol.qtyordered > 0
    GROUP BY r.c_order_id, r.deadline, o.m_warehouse_id, ol.m_product_id, p.sku, p.value
  ),
  needed_products AS (
    SELECT DISTINCT m_product_id, m_warehouse_id FROM lines
  ),
  cur AS (
    SELECT s.m_product_id, l.m_warehouse_id, SUM(s.qtyonhand) AS qty
    FROM m_storage s
    JOIN needed_products np ON np.m_product_id = s.m_product_id
    JOIN m_locator l        ON l.m_locator_id = s.m_locator_id AND l.m_warehouse_id = np.m_warehouse_id
    LEFT JOIN m_locatortype lt ON lt.m_locatortype_id = l.m_locatortype_id
    WHERE ${SHIPPABLE_LOCATOR('lt')}
    GROUP BY s.m_product_id, l.m_warehouse_id
  )
  SELECT ln.c_order_id, ln.m_product_id, ln.sku, ln.needed,
         COALESCE(cur.qty, 0) - COALESCE((
           SELECT SUM(tx.movementqty)
           FROM m_transaction tx
           JOIN m_locator tl ON tl.m_locator_id = tx.m_locator_id
           LEFT JOIN m_locatortype tlt ON tlt.m_locatortype_id = tl.m_locatortype_id
           WHERE tx.m_product_id = ln.m_product_id
             AND tl.m_warehouse_id = ln.m_warehouse_id
             AND tx.movementdate > ln.deadline
             AND ${SHIPPABLE_LOCATOR('tlt')}
         ), 0) AS on_hand
  FROM lines ln
  LEFT JOIN cur ON cur.m_product_id = ln.m_product_id AND cur.m_warehouse_id = ln.m_warehouse_id
`

/**
 * Find orders that could not have been shipped on their deadline day because at least one item
 * line exceeded the on-hand stock in the order's warehouse at the end of that day.
 */
export const findStockBlockedOrders = async (
  pairs: { orderId: number; deadlineDate: string }[]
): Promise<StockCheckResult> => {
  const started = Date.now()
  const blocked = new Map<number, StockShortfall>()
  const valid = pairs.filter(p => Number.isFinite(p.orderId) && p.orderId > 0 && /^\d{4}-\d{2}-\d{2}$/.test(p.deadlineDate))
  if (!valid.length) return { available: true, blocked, ms: 0, checked: 0 }

  const pool = getPool()
  if (!pool) return { available: false, blocked, ms: 0, checked: 0, error: 'PG not configured' }

  try {
    for (let i = 0; i < valid.length; i += CHUNK) {
      const chunk = valid.slice(i, i + CHUNK)
      const res = await pool.query(SQL, [chunk.map(c => c.orderId), chunk.map(c => c.deadlineDate)])
      for (const r of res.rows) {
        const needed = Number(r.needed) || 0
        const onHand = Number(r.on_hand) || 0
        if (needed <= onHand) continue
        const orderId = Number(r.c_order_id)
        const entry = blocked.get(orderId) || { orderId, lines: [] }
        entry.lines.push({ productId: Number(r.m_product_id), sku: r.sku || String(r.m_product_id), needed, onHand })
        blocked.set(orderId, entry)
      }
    }
    return { available: true, blocked, ms: Date.now() - started, checked: valid.length }
  } catch (err: any) {
    console.error('stockAtDeadline: query failed', err?.message || err)
    return { available: false, blocked: new Map(), ms: Date.now() - started, checked: valid.length, error: err?.message || String(err) }
  }
}

/** Human-readable shortfall, e.g. "SKU-1: needed 3, had 0 · SKU-2: needed 1, had 0" */
export const formatShortfall = (s: StockShortfall | undefined, max = 4): string => {
  if (!s?.lines.length) return ''
  const fmt = (n: number) => (Number.isInteger(n) ? String(n) : n.toFixed(2))
  const parts = s.lines.slice(0, max).map(l => `${l.sku}: needed ${fmt(l.needed)}, had ${fmt(Math.max(0, l.onHand))}`)
  if (s.lines.length > max) parts.push(`+${s.lines.length - max} more`)
  return parts.join(' · ')
}