/**
 * Warehouse Builder — server helpers shared by the warehouse-layouts routes.
 *
 *  - CUST_WarehouseLayout rows (one per warehouse + floor; the plan lives in LayoutJSON)
 *  - paged M_Locator loader for a warehouse incl. the layout columns, with a fallback
 *    when the columns do not exist yet (`columnsMissing`), 30 s cache + single-flight
 *  - capability probes (table / columns present?) so the UI can show a DDL checklist
 *
 * Writes follow idempiere/write-casing-and-flags.md: scalar keys lowercase-first
 * (`layoutJSON`, `layoutPosX`, `pickSeqNo`), FK objects PascalCase `{ id, tableName }`.
 */
import { string } from 'alga-js'
import fetchHelper from './fetchHelper'
import { parseLayout } from '../../app/utils/warehouseLayout/schema'
import type { WbLayout } from '../../app/utils/warehouseLayout/schema'
import { mapLocatorRow } from '../../app/utils/warehouseLayout/wiring'
import type { WbLocatorRow } from '../../app/utils/warehouseLayout/wiring'

export const LAYOUT_MODEL = 'models/cust_warehouselayout'
export const LAYOUT_TABLE = 'CUST_WarehouseLayout'
export const LOCATOR_LAYOUT_COLUMNS = ['LayoutPosX', 'LayoutPosY', 'LayoutSide', 'PickSeqNo', 'LayoutObjectKey', 'LayoutZone', 'CUST_WarehouseLayout_ID']
const LOCATOR_BASE_SELECT = 'Value,X,Y,Z,PriorityNo,IsDefault,IsActive,M_LocatorType_ID,M_Warehouse_ID,AD_Org_ID'
const LAYOUT_SELECT = 'Name,FloorNo,GridWidth,GridHeight,LayoutVersion,AppliedVersion,IsDefault,Description,Updated,M_Warehouse_ID,AD_Org_ID'
const PAGE = 1000
const MAX_PAGES = 30
const LOCATOR_TTL_MS = 30 * 1000
const CAP_TTL_MS = 60 * 1000

export const enc = (v: string) => string.urlEncode(v)

export const errStatus = (err: any): number => Number(err?.response?.status ?? err?.status ?? err?.statusCode ?? err?.data?.status ?? 0) || 0
export const errMessage = (err: any): string => String(err?.data?.detail ?? err?.data?.message ?? err?.data?.title ?? err?.message ?? err?.statusMessage ?? err ?? '')

/** iDempiere's way of saying "that column is not in the dictionary (yet)". */
export const isUnknownColumnError = (err: any): boolean => {
  const m = errMessage(err).toLowerCase()
  return /column|property|field|select/.test(m) && /not exist|does not|invalid|unknown|not found|no such/.test(m)
}
/** Table not in the dictionary / REST model not found. */
export const isMissingTableError = (err: any): boolean => {
  const m = errMessage(err).toLowerCase()
  const st = errStatus(err)
  return (st === 404 || st === 400 || st === 500) && /table|model|not found|does not exist|invalid|unknown/.test(m)
}

const isY = (v: any) => v === true || v === 'Y'
const idOf = (v: any): number | null => (v && typeof v === 'object' ? Number(v.id) : Number(v)) || null
const labelOf = (v: any): string => (v && typeof v === 'object' ? String(v.identifier ?? v.id ?? '') : String(v ?? ''))

export interface LayoutRow {
  id: number
  warehouseId: number | null
  warehouseName: string
  organizationId: number | null
  name: string
  floorNo: number
  isDefault: boolean
  gridWidth: number
  gridHeight: number
  layoutVersion: number
  appliedVersion: number
  description: string
  updated: string
  layout?: WbLayout | null
  parseError?: string
}

export const mapLayoutRow = (r: any, withJson = false): LayoutRow => {
  const row: LayoutRow = {
    id: Number(r.id),
    warehouseId: idOf(r.M_Warehouse_ID),
    warehouseName: labelOf(r.M_Warehouse_ID),
    organizationId: idOf(r.AD_Org_ID),
    name: String(r.Name ?? r.name ?? ''),
    floorNo: Number(r.FloorNo ?? r.floorNo ?? 0) || 0,
    isDefault: isY(r.IsDefault ?? r.isDefault),
    gridWidth: Number(r.GridWidth ?? r.gridWidth ?? 40) || 40,
    gridHeight: Number(r.GridHeight ?? r.gridHeight ?? 25) || 25,
    layoutVersion: Number(r.LayoutVersion ?? r.layoutVersion ?? 1) || 1,
    appliedVersion: Number(r.AppliedVersion ?? r.appliedVersion ?? 0) || 0,
    description: String(r.Description ?? r.description ?? ''),
    updated: String(r.Updated ?? r.updated ?? '')
  }
  if (withJson) {
    const p = parseLayout(r.LayoutJSON ?? r.layoutJSON ?? r.layoutjson ?? '')
    row.layout = p.layout
    if (p.error) row.parseError = p.error
  }
  return row
}

export const listLayouts = async (event: any, token: string, warehouseId: number): Promise<LayoutRow[]> => {
  const res: any = await fetchHelper(event, `${LAYOUT_MODEL}?$filter=${enc(`M_Warehouse_ID eq ${warehouseId} AND IsActive eq true`)}&$select=${LAYOUT_SELECT}&$orderby=${enc('FloorNo asc')}&$top=50`, 'GET', token, null)
  return (res?.records || []).map((r: any) => mapLayoutRow(r))
}

export const getLayout = async (event: any, token: string, id: number): Promise<LayoutRow | null> => {
  const res: any = await fetchHelper(event, `${LAYOUT_MODEL}/${id}`, 'GET', token, null)
  return res?.id ? mapLayoutRow(res, true) : null
}

export const warehouseOrg = async (event: any, token: string, warehouseId: number): Promise<{ orgId: number | null; name: string }> => {
  const res: any = await fetchHelper(event, `models/m_warehouse/${warehouseId}?$select=Name,AD_Org_ID`, 'GET', token, null)
  return { orgId: idOf(res?.AD_Org_ID), name: String(res?.Name || '') }
}

// ---- locators -----------------------------------------------------------------------------------
export interface LocatorLoad { records: WbLocatorRow[]; total: number; columnsMissing: boolean; missingColumns: string[] }
const locatorCache = new Map<number, { at: number; data: LocatorLoad }>()
const inflight = new Map<number, Promise<LocatorLoad>>()

export const bustLocatorCache = (warehouseId?: number) => { if (warehouseId) locatorCache.delete(Number(warehouseId)); else locatorCache.clear() }

const fetchPages = async (event: any, token: string, warehouseId: number, select: string): Promise<any[]> => {
  const out: any[] = []
  for (let page = 0; page < MAX_PAGES; page++) {
    const res: any = await fetchHelper(event, `models/m_locator?$filter=${enc(`M_Warehouse_ID eq ${warehouseId}`)}&$select=${select}&$orderby=${enc('M_Locator_ID asc')}&$top=${PAGE}&$skip=${page * PAGE}`, 'GET', token, null)
    const rows = res?.records || []
    out.push(...rows)
    if (rows.length < PAGE) break
  }
  return out
}

/** All locators of a warehouse (active + inactive) with the layout columns when they exist. */
export const loadWarehouseLocators = async (event: any, token: string, warehouseId: number, fresh = false): Promise<LocatorLoad> => {
  const wid = Number(warehouseId)
  if (!wid) return { records: [], total: 0, columnsMissing: false, missingColumns: [] }
  const hit = locatorCache.get(wid)
  if (!fresh && hit && Date.now() - hit.at < LOCATOR_TTL_MS) return hit.data
  if (inflight.has(wid)) return inflight.get(wid)!
  const p = (async () => {
    let columnsMissing = false
    let missingColumns: string[] = []
    let rows: any[]
    try {
      rows = await fetchPages(event, token, wid, `${LOCATOR_BASE_SELECT},${LOCATOR_LAYOUT_COLUMNS.join(',')}`)
    } catch (err: any) {
      if (!isUnknownColumnError(err)) throw err
      columnsMissing = true
      missingColumns = [...LOCATOR_LAYOUT_COLUMNS]
      rows = await fetchPages(event, token, wid, LOCATOR_BASE_SELECT)
    }
    const data: LocatorLoad = { records: rows.map(mapLocatorRow), total: rows.length, columnsMissing, missingColumns }
    locatorCache.set(wid, { at: Date.now(), data })
    return data
  })()
  inflight.set(wid, p)
  try { return await p } finally { inflight.delete(wid) }
}

/** Active locators in ALL warehouses whose Value is one of `values` (one physical hall mirrored per org). */
export const loadLocatorsByValues = async (event: any, token: string, values: string[]): Promise<WbLocatorRow[]> => {
  const { odataQuote } = await import('./odataHelper')
  const uniq = [...new Set(values.map(v => String(v || '').trim()).filter(Boolean))]
  const out: WbLocatorRow[] = []
  for (let i = 0; i < uniq.length; i += 40) {
    const chunk = uniq.slice(i, i + 40)
    const filter = '(' + chunk.map(v => `Value eq '${odataQuote(v)}'`).join(' OR ') + ') AND IsActive eq true'
    for (let page = 0; page < 10; page++) {
      const res: any = await fetchHelper(event, `models/m_locator?$filter=${enc(filter)}&$select=${LOCATOR_BASE_SELECT},${LOCATOR_LAYOUT_COLUMNS.join(',')}&$orderby=${enc('M_Locator_ID asc')}&$top=${PAGE}&$skip=${page * PAGE}`, 'GET', token, null)
      const rows = res?.records || []
      out.push(...rows.map(mapLocatorRow))
      if (rows.length < PAGE) break
    }
  }
  return out
}

/** Locators (any warehouse) currently linked to a layout row — the set a re-apply may have to clear. */
export const loadLocatorsOfLayout = async (event: any, token: string, layoutId: number): Promise<WbLocatorRow[]> => {
  const out: WbLocatorRow[] = []
  for (let page = 0; page < 20; page++) {
    const res: any = await fetchHelper(event, `models/m_locator?$filter=${enc(`CUST_WarehouseLayout_ID eq ${layoutId}`)}&$select=${LOCATOR_BASE_SELECT},${LOCATOR_LAYOUT_COLUMNS.join(',')}&$orderby=${enc('M_Locator_ID asc')}&$top=${PAGE}&$skip=${page * PAGE}`, 'GET', token, null)
    const rows = res?.records || []
    out.push(...rows.map(mapLocatorRow))
    if (rows.length < PAGE) break
  }
  return out
}

// ---- capabilities ---------------------------------------------------------------------------------
export interface Capabilities {
  layoutTable: boolean
  locatorColumns: boolean
  missingColumns: string[]
  commissionWarehouseColumn: boolean
  checkedAt: string
  messages: Record<string, string>
}
let capCache: { at: number; data: Capabilities } | null = null

export const probeCapabilities = async (event: any, token: string, fresh = false): Promise<Capabilities> => {
  if (!fresh && capCache && Date.now() - capCache.at < CAP_TTL_MS) return capCache.data
  const messages: Record<string, string> = {}
  const tryGet = async (url: string): Promise<boolean> => {
    try { await fetchHelper(event, url, 'GET', token, null); return true } catch (err: any) {
      const st = errStatus(err)
      if (st === 401 || st === 403) throw err
      messages[url] = errMessage(err).slice(0, 300)
      return false
    }
  }
  const layoutTable = await tryGet(`${LAYOUT_MODEL}?$select=Name&$top=1`)
  let locatorColumns = await tryGet(`models/m_locator?$select=${LOCATOR_LAYOUT_COLUMNS.join(',')}&$top=1`)
  const missingColumns: string[] = []
  if (!locatorColumns) {
    for (const c of LOCATOR_LAYOUT_COLUMNS) if (!(await tryGet(`models/m_locator?$select=${c}&$top=1`))) missingColumns.push(c)
    locatorColumns = missingColumns.length === 0
  }
  const commissionWarehouseColumn = await tryGet('models/cust_commissiontable?$select=M_Warehouse_ID&$top=1')
  const data: Capabilities = { layoutTable, locatorColumns, missingColumns, commissionWarehouseColumn, checkedAt: new Date().toISOString(), messages }
  capCache = { at: Date.now(), data }
  return data
}
export const bustCapabilities = () => { capCache = null }

export const userNameFromCookie = (event: any): string => {
  try { const u = JSON.parse(getCookie(event, 'logship_user') || 'null'); return String(u?.name || u?.Name || u?.userName || '') } catch { return '' }
}