/**
 * iDempiere AD_ChangeLog reader with value resolution.
 *
 * Mirrors what the ZK web UI shows in a record's "Change Log" / "Time Line" tab:
 * every column change (old → new, who, when) for the record itself plus,
 * optionally, its child lines. Raw AD_ChangeLog rows only hold the stored
 * values (`1000032`, `CO`, `Y`, `NULL`), so this helper resolves them the way
 * the ZK UI does:
 *   - Yes-No       → 'Y' / 'N' (client renders Yes/No badges)
 *   - List         → AD_Ref_List name for the reference (cached per reference)
 *   - FK columns   → the referenced record's Name / DocumentNo / Value
 *                    (TableDir / Search / Table / ID / Location / Locator …)
 *   - everything else is passed through verbatim
 *
 * Lookups are bounded (MAX_LOOKUPS distinct records, LOOKUP_CONCURRENCY at a
 * time) and cached per process (TTL) so re-opening the modal is cheap. Every
 * lookup is fail-soft — a failed resolution leaves the raw value in place.
 */
import fetchHelper from './fetchHelper'

const encode = (s: string) => encodeURI(s)

export interface ChangeLogEntry {
  id: number
  at: string
  by: string
  byId: number | null
  event: 'I' | 'U' | 'D'
  column: string
  label: string
  refType: string
  oldRaw: string | null
  newRaw: string | null
  oldValue: string | null
  newValue: string | null
  trx: string
  source: string          // 'header' | child TableName (e.g. 'C_OrderLine')
  sourceLabel: string     // '' for header, 'Line 10 · 1003278 Rabatt' for children
  recordId: number
}

export interface ChangeLogResult {
  table: string
  tableId: number
  recordId: number
  created: { at: string | null, by: string | null } | null
  entries: ChangeLogEntry[]
  truncated: boolean
  childrenIncluded: boolean
}

interface ChildDef { table: string, name: string, tableId: number, fk: string, select: string, label: (r: any) => string }
interface TableDef { tableId: number, name: string, children: ChildDef[] }

/**
 * Product label for line captions. An expanded FK only carries `identifier`, and
 * M_Product's identifier on prod is "<strapiDocId>_<Value>_<Name>" (or "-1_<Value>_<Name>"),
 * so strip that technical prefix down to "Value Name".
 */
const productLabel = (p: any) => {
  const direct = [p?.Value, p?.Name].filter(Boolean).join(' ')
  if (direct) return direct
  const ident = String(p?.identifier ?? '')
  const m = ident.match(/^(?:[a-z0-9]{16,}|-1)_(\d+)_(.+)$/i)
  return m ? `${m[1]} ${m[2]}` : ident
}

/**
 * Known tables → AD_Table_ID + child tables whose changes belong to the record.
 * Unknown tables are resolved via ad_table at runtime (no children).
 */
const TABLES: Record<string, TableDef> = {
  c_order: {
    tableId: 259, name: 'C_Order',
    children: [{
      table: 'c_orderline', name: 'C_OrderLine', tableId: 260, fk: 'C_Order_ID', select: 'Line,M_Product_ID,C_Charge_ID',
      label: (r) => `Line ${r?.Line ?? ''}${r?.M_Product_ID ? ' · ' + productLabel(r.M_Product_ID) : (r?.C_Charge_ID?.identifier ? ' · ' + r.C_Charge_ID.identifier : '')}`.trim()
    }]
  },
  m_inout: {
    tableId: 319, name: 'M_InOut',
    children: [{
      table: 'm_inoutline', name: 'M_InOutLine', tableId: 320, fk: 'M_InOut_ID', select: 'Line,M_Product_ID,C_Charge_ID',
      label: (r) => `Line ${r?.Line ?? ''}${r?.M_Product_ID ? ' · ' + productLabel(r.M_Product_ID) : (r?.C_Charge_ID?.identifier ? ' · ' + r.C_Charge_ID.identifier : '')}`.trim()
    }]
  },
  m_product: { tableId: 208, name: 'M_Product', children: [] },
  c_bpartner: {
    tableId: 291, name: 'C_BPartner',
    children: [
      { table: 'c_bpartner_location', name: 'C_BPartner_Location', tableId: 293, fk: 'C_BPartner_ID', select: 'Name', label: (r) => `Location · ${r?.Name ?? r?.id ?? ''}` },
      { table: 'ad_user', name: 'AD_User', tableId: 114, fk: 'C_BPartner_ID', select: 'Name', label: (r) => `Contact · ${r?.Name ?? r?.id ?? ''}` }
    ]
  },
  c_invoice: {
    tableId: 318, name: 'C_Invoice',
    children: [{
      table: 'c_invoiceline', name: 'C_InvoiceLine', tableId: 333, fk: 'C_Invoice_ID', select: 'Line,M_Product_ID,C_Charge_ID',
      label: (r) => `Line ${r?.Line ?? ''}${r?.M_Product_ID ? ' · ' + productLabel(r.M_Product_ID) : (r?.C_Charge_ID?.identifier ? ' · ' + r.C_Charge_ID.identifier : '')}`.trim()
    }]
  }
}

/** Columns whose `_ID` suffix does not name their table. */
const COLUMN_TABLE_OVERRIDES: Record<string, string> = {
  SalesRep_ID: 'ad_user',
  Bill_BPartner_ID: 'c_bpartner',
  Bill_Location_ID: 'c_bpartner_location',
  Bill_User_ID: 'ad_user',
  DropShip_BPartner_ID: 'c_bpartner',
  DropShip_Location_ID: 'c_bpartner_location',
  DropShip_User_ID: 'ad_user',
  Return_BPartner_ID: 'c_bpartner',
  Return_Location_ID: 'c_bpartner_location',
  Return_User_ID: 'ad_user',
  C_DocTypeTarget_ID: 'c_doctype',
  Ref_Order_ID: 'c_order',
  Ref_OrderLine_ID: 'c_orderline',
  Ref_InOut_ID: 'm_inout',
  Ref_InOutLine_ID: 'm_inoutline',
  Ref_Invoice_ID: 'c_invoice',
  Ref_InvoiceLine_ID: 'c_invoiceline',
  User1_ID: 'c_elementvalue',
  User2_ID: 'c_elementvalue',
  Link_OrgBP_ID: 'c_bpartner',
  Lead_User_ID: 'ad_user',
  Accounting_AD_Org_ID: 'ad_org',
  M_LocatorTo_ID: 'm_locator',
  M_WarehouseSource_ID: 'm_warehouse',
  M_AttributeSetInstanceTo_ID: 'm_attributesetinstance',
  Parent_Product_ID: 'm_product',
  Strapi_Product_ID: '',           // external id, not an iDempiere FK
  CreatedBy: 'ad_user',
  UpdatedBy: 'ad_user'
}

/** Preferred label columns per table (must all exist — a bad $select is a 400). */
const LABEL_SELECT: Record<string, string> = {
  ad_user: 'Name', c_bpartner: 'Name,Value', c_bpartner_location: 'Name', ad_org: 'Name', ad_client: 'Name',
  ad_role: 'Name', m_product: 'Value,Name', m_product_category: 'Name', c_doctype: 'Name', m_warehouse: 'Name',
  m_locator: 'Value', m_shipper: 'Name', c_paymentterm: 'Name', c_currency: 'ISO_Code', c_country: 'Name',
  c_region: 'Name', c_tax: 'Name', c_taxcategory: 'Name', c_uom: 'Name', c_charge: 'Name', c_campaign: 'Name',
  c_project: 'Name', m_pricelist: 'Name', m_pricelist_version: 'Name', c_ordersource: 'Name', c_bp_group: 'Name',
  c_order: 'DocumentNo', m_inout: 'DocumentNo', c_invoice: 'DocumentNo', c_payment: 'DocumentNo', m_rma: 'DocumentNo',
  c_orderline: 'Line', m_inoutline: 'Line', c_invoiceline: 'Line',
  c_location: 'Address1,Postal,City', c_elementvalue: 'Value,Name', m_attributeset: 'Name',
  m_attributesetinstance: 'Description', c_activity: 'Name', c_salesregion: 'Name', ad_printformat: 'Name',
  c_bankaccount: 'Name', c_bank: 'Name', r_requesttype: 'Name', r_status: 'Name', ad_image: 'Name',
  cust_commissiontable: 'Name', cust_fulfillmentproductpricingrules: 'Name', cust_printer: 'Name'
}

/** Reference types whose values are foreign keys we can resolve. */
const FK_REFS = new Set(['Table Direct', 'Search', 'Table', 'ID', 'Location (Address)', 'Locator (WH)', 'Account', 'Product Attribute', 'Image', 'Color', 'Assignment'])
const REF_FIXED_TABLE: Record<string, string> = {
  'Location (Address)': 'c_location', 'Locator (WH)': 'm_locator', 'Account': 'c_validcombination',
  'Product Attribute': 'm_attributesetinstance', 'Image': 'ad_image', 'Color': 'ad_color', 'Assignment': 's_resourceassignment'
}

const MAX_ENTRIES = 3000
const PAGE = 500
const MAX_CHILD_RECORDS = 500
const MAX_LOOKUPS = 150
const LOOKUP_CONCURRENCY = 4
const CACHE_TTL = 5 * 60 * 1000

// ---- process-wide caches (fail-soft, bounded) --------------------------------
const labelCache = new Map<string, { v: string, exp: number }>()
const refListCache = new Map<number, { v: Map<string, string>, exp: number }>()
const refTableCache = new Map<number, { v: string, exp: number }>()
const tableNameCache = new Map<string, { v: TableDef, exp: number }>()

const cacheGet = <T>(m: Map<any, { v: T, exp: number }>, k: any): T | undefined => {
  const hit = m.get(k)
  if (hit && hit.exp > Date.now()) return hit.v
  if (hit) m.delete(k)
  return undefined
}
const cacheSet = <T>(m: Map<any, { v: T, exp: number }>, k: any, v: T) => {
  if (m.size > 5000) m.clear()
  m.set(k, { v, exp: Date.now() + CACHE_TTL })
}

const isNull = (v: any) => v === null || v === undefined || v === 'NULL' || v === ''

const runPool = async <T>(items: T[], n: number, fn: (i: T) => Promise<void>) => {
  let idx = 0
  const worker = async () => {
    while (idx < items.length) {
      const item = items[idx++]
      try { await fn(item) } catch { /* fail-soft */ }
    }
  }
  await Promise.all(Array.from({ length: Math.min(n, items.length) }, worker))
}

// ---- table resolution ----------------------------------------------------------
export const resolveTableDef = async (event: any, token: string, table: string): Promise<TableDef | null> => {
  const key = String(table || '').toLowerCase()
  if (TABLES[key]) return TABLES[key]
  const cached = cacheGet(tableNameCache, key)
  if (cached) return cached
  try {
    const res: any = await fetchHelper(event, `models/ad_table?$filter=${encode(`TableName eq '${key.replace(/'/g, "''")}'`)}&$select=TableName&$top=1`, 'GET', token, null)
    const rec = res?.records?.[0]
    if (!rec?.id) return null
    const def: TableDef = { tableId: Number(rec.id), name: rec.TableName || table, children: [] }
    cacheSet(tableNameCache, key, def)
    return def
  } catch {
    return null
  }
}

// ---- raw changelog paging ------------------------------------------------------
const fetchChangeLogRows = async (event: any, token: string, filter: string, cap: number): Promise<{ rows: any[], truncated: boolean }> => {
  const rows: any[] = []
  let skip = 0
  while (rows.length < cap) {
    const top = Math.min(PAGE, cap - rows.length)
    const url = `models/ad_changelog?$filter=${encode(filter)}&$orderby=${encode('Updated desc')}&$top=${top}&$skip=${skip}` +
      `&$expand=${encode('AD_Column_ID($select=ColumnName,Name,AD_Reference_ID,AD_Reference_Value_ID)')}`
    const res: any = await fetchHelper(event, url, 'GET', token, null)
    const page: any[] = res?.records || []
    rows.push(...page)
    if (page.length < top) return { rows, truncated: false }
    skip += page.length
  }
  return { rows, truncated: true }
}

// ---- value resolution ----------------------------------------------------------
const tableForColumn = async (event: any, token: string, columnName: string, refType: string, refValueId: number | null): Promise<string> => {
  if (columnName in COLUMN_TABLE_OVERRIDES) return COLUMN_TABLE_OVERRIDES[columnName]
  if (REF_FIXED_TABLE[refType]) return REF_FIXED_TABLE[refType]
  // 'Table' (and Search with an explicit reference) → AD_Ref_Table → AD_Table
  if (refValueId && (refType === 'Table' || refType === 'Search')) {
    const cached = cacheGet(refTableCache, refValueId)
    if (cached !== undefined) return cached
    try {
      const rt: any = await fetchHelper(event, `models/ad_ref_table?$filter=${encode('AD_Reference_ID eq ' + refValueId)}&$top=1`, 'GET', token, null)
      const tableId = rt?.records?.[0]?.AD_Table_ID?.id
      let name = ''
      if (tableId) {
        const t: any = await fetchHelper(event, `models/ad_table/${tableId}?$select=TableName`, 'GET', token, null)
        name = String(t?.TableName || '').toLowerCase()
      }
      cacheSet(refTableCache, refValueId, name)
      if (name) return name
    } catch { /* fall through to the _ID heuristic */ }
  }
  if (/_ID$/i.test(columnName)) return columnName.replace(/_ID$/i, '').toLowerCase()
  return ''
}

const refListNames = async (event: any, token: string, refId: number): Promise<Map<string, string>> => {
  const cached = cacheGet(refListCache, refId)
  if (cached) return cached
  const map = new Map<string, string>()
  try {
    const res: any = await fetchHelper(event, `models/ad_ref_list?$filter=${encode('AD_Reference_ID eq ' + refId)}&$select=Value,Name&$top=500`, 'GET', token, null)
    for (const r of res?.records || []) if (r?.Value !== undefined) map.set(String(r.Value), String(r.Name ?? r.Value))
  } catch { /* fail-soft */ }
  cacheSet(refListCache, refId, map)
  return map
}

const recordLabel = async (event: any, token: string, table: string, id: string): Promise<string | null> => {
  const key = `${table}/${id}`
  const cached = cacheGet(labelCache, key)
  if (cached !== undefined) return cached
  const select = LABEL_SELECT[table]
  let rec: any = null
  try {
    rec = await fetchHelper(event, `models/${table}/${id}${select ? '?$select=' + select : ''}`, 'GET', token, null)
  } catch {
    if (select) {
      try { rec = await fetchHelper(event, `models/${table}/${id}`, 'GET', token, null) } catch { rec = null }
    }
  }
  if (!rec || typeof rec !== 'object') return null
  let label = ''
  if (table === 'm_product' || table === 'c_elementvalue') label = [rec.Value, rec.Name].filter(Boolean).join(' ')
  else if (table === 'c_location') label = [rec.Address1, [rec.Postal, rec.City].filter(Boolean).join(' ')].filter(Boolean).join(', ')
  else if (table === 'c_currency') label = rec.ISO_Code || ''
  else if (/line$/.test(table)) label = rec.Line !== undefined ? `Line ${rec.Line}` : ''
  if (!label) label = rec.Name || rec.DocumentNo || rec.Value || rec.Description || ''
  label = String(label || '').trim()
  if (label) cacheSet(labelCache, key, label)
  return label || null
}

// ---- main ----------------------------------------------------------------------
export const readChangeLog = async (event: any, token: string, table: string, recordId: number, includeChildren: boolean): Promise<ChangeLogResult | null> => {
  const def = await resolveTableDef(event, token, table)
  if (!def) return null
  const modelName = def.name.toLowerCase()

  // The record's own creation (ZK shows "Created <Table>" as the first timeline event)
  let created: ChangeLogResult['created'] = null
  try {
    const rec: any = await fetchHelper(event, `models/${modelName}/${recordId}?$select=Created,CreatedBy`, 'GET', token, null)
    if (rec?.Created) created = { at: rec.Created, by: rec.CreatedBy?.identifier ?? null }
  } catch { /* fail-soft */ }

  // Header rows
  const header = await fetchChangeLogRows(event, token, `AD_Table_ID eq ${def.tableId} AND Record_ID eq ${recordId}`, MAX_ENTRIES)
  let truncated = header.truncated
  const raw: Array<{ row: any, source: string, sourceLabel: string }> = header.rows.map(row => ({ row, source: 'header', sourceLabel: '' }))

  // Child rows (order/shipment lines, partner locations + contacts)
  let childrenIncluded = false
  if (includeChildren && def.children.length) {
    for (const child of def.children) {
      try {
        const list: any = await fetchHelper(event, `models/${child.table}?$filter=${encode(`${child.fk} eq ${recordId}`)}&$select=${child.select}&$top=${MAX_CHILD_RECORDS}`, 'GET', token, null)
        const recs: any[] = list?.records || []
        if (!recs.length) continue
        childrenIncluded = true
        const labels = new Map<number, string>()
        for (const r of recs) labels.set(Number(r.id), child.label(r))
        const ids = recs.map(r => Number(r.id)).filter(Boolean)
        for (let i = 0; i < ids.length; i += 100) {
          const chunk = ids.slice(i, i + 100)
          const budget = Math.max(0, MAX_ENTRIES - raw.length)
          if (!budget) { truncated = true; break }
          const res = await fetchChangeLogRows(event, token, `AD_Table_ID eq ${child.tableId} AND Record_ID in (${chunk.join(',')})`, budget)
          truncated = truncated || res.truncated
          for (const row of res.rows) raw.push({ row, source: child.name, sourceLabel: labels.get(Number(row.Record_ID)) || `${child.name} ${row.Record_ID}` })
        }
      } catch { /* fail-soft per child table */ }
    }
  }

  // Shape entries (refValueIds runs parallel to entries — needed for List/Table lookups)
  const refValueIds: Array<number | null> = []
  const entries: ChangeLogEntry[] = raw.map(({ row, source, sourceLabel }) => {
    const col = row.AD_Column_ID || {}
    const columnName = col.ColumnName || String(col.identifier || '').replace(/_.*$/, '') || ''
    refValueIds.push(col.AD_Reference_Value_ID?.id ? Number(col.AD_Reference_Value_ID.id) : null)
    return {
      id: Number(row.AD_ChangeLog_ID ?? row.id ?? 0),
      at: row.Updated || row.Created || '',
      by: row.UpdatedBy?.identifier ?? row.CreatedBy?.identifier ?? '',
      byId: row.UpdatedBy?.id ?? row.CreatedBy?.id ?? null,
      event: (row.EventChangeLog?.id || 'U') as any,
      column: columnName,
      label: col.Name || columnName,
      refType: col.AD_Reference_ID?.identifier || '',
      oldRaw: isNull(row.OldValue) ? null : String(row.OldValue),
      newRaw: isNull(row.NewValue) ? null : String(row.NewValue),
      oldValue: isNull(row.OldValue) ? null : String(row.OldValue),
      newValue: isNull(row.NewValue) ? null : String(row.NewValue),
      trx: row.TrxName || '',
      source,
      sourceLabel,
      recordId: Number(row.Record_ID)
    }
  })

  // Resolve list + FK values (bounded, cached, fail-soft)
  const listJobs = new Map<number, ChangeLogEntry[]>()
  const fkJobs = new Map<string, Array<{ e: ChangeLogEntry, side: 'old' | 'new' }>>()
  const colTable = new Map<string, string>()

  for (let i = 0; i < entries.length; i++) {
    const e = entries[i]
    const refValueId = refValueIds[i]
    if (e.refType === 'Yes-No') {
      const yn = (v: string | null) => v === null ? null : (/^(true|y)$/i.test(v) ? 'Y' : 'N')
      e.oldValue = yn(e.oldRaw); e.newValue = yn(e.newRaw)
    } else if (e.refType === 'List' && refValueId) {
      if (!listJobs.has(refValueId)) listJobs.set(refValueId, [])
      listJobs.get(refValueId)!.push(e)
    } else if (FK_REFS.has(e.refType)) {
      const ck = `${e.column}|${e.refType}|${refValueId ?? ''}`
      let tbl = colTable.get(ck)
      if (tbl === undefined) {
        tbl = await tableForColumn(event, token, e.column, e.refType, refValueId)
        colTable.set(ck, tbl)
      }
      if (!tbl) continue
      for (const side of ['old', 'new'] as const) {
        const v = side === 'old' ? e.oldRaw : e.newRaw
        if (v === null || !/^\d+$/.test(v)) continue
        if (Number(v) <= 0) {           // 0 = "no reference" on FK columns
          if (side === 'old') e.oldValue = null; else e.newValue = null
          continue
        }
        const key = `${tbl}/${v}`
        if (!fkJobs.has(key)) fkJobs.set(key, [])
        fkJobs.get(key)!.push({ e, side })
      }
    }
  }

  await runPool([...listJobs.entries()], LOOKUP_CONCURRENCY, async ([refId, list]) => {
    const names = await refListNames(event, token, refId)
    for (const e of list) {
      if (e.oldRaw !== null && names.has(e.oldRaw)) e.oldValue = names.get(e.oldRaw)!
      if (e.newRaw !== null && names.has(e.newRaw)) e.newValue = names.get(e.newRaw)!
    }
  })

  const fkKeys = [...fkJobs.keys()].slice(0, MAX_LOOKUPS)
  await runPool(fkKeys, LOOKUP_CONCURRENCY, async (key) => {
    const [tbl, id] = key.split('/')
    const label = await recordLabel(event, token, tbl, id)
    if (!label) return
    for (const { e, side } of fkJobs.get(key)!) {
      if (side === 'old') e.oldValue = label; else e.newValue = label
    }
  })

  entries.sort((a, b) => (b.at.localeCompare(a.at)) || (b.id - a.id))

  return { table: def.name, tableId: def.tableId, recordId, created, entries, truncated, childrenIncluded }
}

// ---- admin gate ----------------------------------------------------------------
const adminCache = new Map<number, { v: boolean, exp: number }>()

const decodeJwtPayload = (token: string): any => {
  const part = token.split('.')[1]
  if (!part) throw new Error('bad jwt')
  return JSON.parse(Buffer.from(part.replace(/-/g, '+').replace(/_/g, '/'), 'base64').toString('utf8'))
}

/**
 * True when the session's role is a tenant administrator (`AD_Role.IsClientAdministrator`).
 * Resolved against iDempiere (not the client-writable role cookie), cached 10 min, fail-closed.
 */
export const isAdminRole = async (event: any, token: string): Promise<boolean> => {
  let roleId = 0
  try { roleId = Number(decodeJwtPayload(token)?.AD_Role_ID) } catch { roleId = 0 }
  if (!roleId) roleId = Number(getCookie(event, 'logship_role_id')) || 0
  if (!roleId) return false
  const cached = cacheGet(adminCache, roleId)
  if (cached !== undefined) return cached
  try {
    const role: any = await fetchHelper(event, `models/ad_role/${roleId}?$select=IsClientAdministrator`, 'GET', token, null)
    const ok = role?.IsClientAdministrator === true || role?.IsClientAdministrator === 'Y'
    adminCache.set(roleId, { v: ok, exp: Date.now() + 10 * 60 * 1000 })
    return ok
  } catch {
    return false
  }
}