/**
 * Software-offer catalog persisted in iDempiere:
 *   CUST_SoftwarePlan        (models/cust_softwareplan)        — one row per package
 *   CUST_SoftwareCatalogItem (models/cust_softwarecatalogitem) — features (ItemType 'feature',
 *                             FK CUST_SoftwarePlan_ID = minimum package) and add-ons ('addon')
 * Enum columns are AD_Reference lists (CUST_SoftwareSupportLevel, …ItemType, …FeatureGroup,
 * …FeatureMode, …PriceUnit): read as { id, identifier }, written as { id }.
 *
 * FAIL-SOFT: a missing/unreadable table or an empty one falls back to
 * DEFAULT_SOFTWARE_CATALOG (app/utils/softwareCatalogDefaults.ts) for that part and reports it
 * (`source`, `missingTables`, `empty`) so the modal/settings page can say so. 5-min cache per org,
 * invalidated by every write. Rows are scoped `(AD_Org_ID eq <cookie org> or AD_Org_ID eq 0)`.
 */
import fetchHelper from '../fetchHelper'
import {
  DEFAULT_SOFTWARE_CATALOG, normalizeSoftwareCatalog,
  type SoftwareCatalog, type CatalogPlan, type CatalogFeature, type CatalogAddon
} from '../../../app/utils/softwareCatalogDefaults'

export const PLAN_MODEL = 'cust_softwareplan'
export const ITEM_MODEL = 'cust_softwarecatalogitem'
export const PLAN_TABLE = 'CUST_SoftwarePlan'
export const ITEM_TABLE = 'CUST_SoftwareCatalogItem'

const CACHE_TTL_MS = 5 * 60 * 1000
const g = globalThis as any
const cache: Map<string, { at: number; catalog: SoftwareCatalog }> = g.__softwareCatalogCache ?? (g.__softwareCatalogCache = new Map())
export const invalidateSoftwareCatalogCache = () => cache.clear()

const num = (v: any): number => { const n = Number(v); return Number.isFinite(n) ? n : 0 }
const listId = (v: any): string => (v && typeof v === 'object' ? String(v.id ?? v.identifier ?? '') : String(v ?? ''))
const fkId = (v: any): number => num(v && typeof v === 'object' ? v.id : v)
const orgOf = (event: any): number => num(getCookie(event, 'logship_organization_id'))

const scopeFilter = (orgId: number) => (orgId > 0 ? `(AD_Org_ID eq ${orgId} or AD_Org_ID eq 0)` : 'AD_Org_ID eq 0')

const isMissingTableError = (err: any): boolean => {
  const s = num(err?.statusCode ?? err?.status ?? err?.response?.status)
  const msg = String(err?.data?.detail ?? err?.data?.title ?? err?.message ?? '').toLowerCase()
  return s === 404 || /not found|does not exist|no table|invalid table|access denied for table/.test(msg)
}

// ---- row mappers (tolerant casing: REST returns the ColumnName verbatim) ----
const mapPlan = (r: any): CatalogPlan => ({
  id: num(r?.id),
  key: String(r?.Value ?? r?.value ?? '').trim(),
  name: String(r?.Name ?? r?.name ?? '').trim(),
  nameEn: String(r?.NameEN ?? r?.nameEN ?? r?.nameen ?? '').trim(),
  seqNo: num(r?.SeqNo ?? r?.seqNo),
  popular: (r?.IsPopular ?? r?.isPopular) === true,
  priceMonthly: num(r?.PriceMonthly ?? r?.priceMonthly),
  priceYearly: num(r?.PriceYearly ?? r?.priceYearly),
  includedOrders: num(r?.IncludedOrders ?? r?.includedOrders),
  includedUsers: num(r?.IncludedUsers ?? r?.includedUsers),
  salesChannels: num(r?.SalesChannels ?? r?.salesChannels),
  extraOrderPrice: num(r?.ExtraOrderPrice ?? r?.extraOrderPrice),
  extraUserPrice: num(r?.ExtraUserPrice ?? r?.extraUserPrice),
  support: (listId(r?.SupportLevel ?? r?.supportLevel) || 'ticket') as any,
  description: String(r?.Description ?? r?.description ?? ''),
  active: (r?.IsActive ?? r?.isActive) !== false
})

const mapItem = (r: any, planKeyById: Map<number, string>) => {
  const type = listId(r?.ItemType ?? r?.itemType) === 'addon' ? 'addon' : 'feature'
  const base = {
    id: num(r?.id),
    key: String(r?.Value ?? r?.value ?? '').trim(),
    name: String(r?.Name ?? r?.name ?? '').trim(),
    nameEn: String(r?.NameEN ?? r?.nameEN ?? r?.nameen ?? '').trim(),
    seqNo: num(r?.SeqNo ?? r?.seqNo),
    active: (r?.IsActive ?? r?.isActive) !== false
  }
  if (type === 'addon') {
    const addon: CatalogAddon = {
      ...base,
      price: num(r?.Price ?? r?.price),
      unit: (listId(r?.PriceUnit ?? r?.priceUnit) || 'month') as any,
      note: String(r?.Description ?? r?.description ?? ''),
      noteEn: String(r?.DescriptionEN ?? r?.descriptionEN ?? r?.descriptionen ?? ''),
      defaultSelected: (r?.IsDefault ?? r?.isDefault) === true
    }
    return { type, row: addon }
  }
  const feature: CatalogFeature = {
    ...base,
    group: listId(r?.FeatureGroup ?? r?.featureGroup) || 'core',
    minPlanKey: planKeyById.get(fkId(r?.CUST_SoftwarePlan_ID ?? r?.cust_softwareplan_id)) || '',
    mode: (listId(r?.Mode ?? r?.mode) || 'standard') as any
  }
  return { type, row: feature }
}

const listAll = async (event: any, token: string, model: string, orgId: number) => {
  const res: any = await fetchHelper(event, `models/${model}?$filter=${encodeURIComponent(scopeFilter(orgId))}&$orderby=${encodeURIComponent('SeqNo asc')}&$top=500`, 'GET', token, null)
  return Array.isArray(res?.records) ? res.records : []
}

/** Read the catalog (cached 5 min per org). `force` bypasses the cache. */
export const loadSoftwareCatalog = async (event: any, token: string, force = false): Promise<SoftwareCatalog> => {
  const orgId = orgOf(event)
  const ck = String(orgId)
  const hit = cache.get(ck)
  if (!force && hit && Date.now() - hit.at < CACHE_TTL_MS) return hit.catalog

  const missing: string[] = []
  let empty = false
  let plans: CatalogPlan[] = []
  let planRows: any[] = []
  try {
    planRows = await listAll(event, token, PLAN_MODEL, orgId)
    plans = planRows.map(mapPlan).filter((p) => p.key)
  } catch (err: any) {
    if (!isMissingTableError(err)) {
      const s = num(err?.statusCode ?? err?.status ?? err?.response?.status)
      if (s === 401) throw err // let the route run its token-refresh path
    }
    missing.push(PLAN_TABLE)
  }
  let features: CatalogFeature[] = []
  let addons: CatalogAddon[] = []
  try {
    const planKeyById = new Map<number, string>(plans.map((p) => [Number(p.id), p.key]))
    const rows = await listAll(event, token, ITEM_MODEL, orgId)
    for (const r of rows) {
      const m = mapItem(r, planKeyById)
      if (!m.row.key) continue
      if (m.type === 'addon') addons.push(m.row as CatalogAddon)
      else features.push(m.row as CatalogFeature)
    }
  } catch (err: any) {
    const s = num(err?.statusCode ?? err?.status ?? err?.response?.status)
    if (s === 401) throw err
    missing.push(ITEM_TABLE)
  }

  // Fallbacks: a missing OR empty part comes from the defaults (the app must
  // behave until the tables are created + seeded).
  const useDefaultPlans = missing.includes(PLAN_TABLE) || plans.length === 0
  const useDefaultItems = missing.includes(ITEM_TABLE) || (features.length === 0 && addons.length === 0)
  if (!missing.length && (plans.length === 0 || (features.length === 0 && addons.length === 0))) empty = true
  const catalog = normalizeSoftwareCatalog({
    plans: useDefaultPlans ? DEFAULT_SOFTWARE_CATALOG.plans : plans,
    features: useDefaultItems ? DEFAULT_SOFTWARE_CATALOG.features : features,
    addons: useDefaultItems ? DEFAULT_SOFTWARE_CATALOG.addons : addons,
    source: useDefaultPlans || useDefaultItems ? 'defaults' : 'idempiere',
    missingTables: missing,
    empty,
    loadedAt: new Date().toISOString()
  })
  // Keep the real (possibly inactive) rows visible to the settings page when the
  // tables answered: expose them separately so "defaults" never look persisted.
  ;(catalog as any).persisted = {
    plans: missing.includes(PLAN_TABLE) ? null : plans,
    features: missing.includes(ITEM_TABLE) ? null : features,
    addons: missing.includes(ITEM_TABLE) ? null : addons
  }
  cache.set(ck, { at: Date.now(), catalog })
  return catalog
}

// ---- writes ----
const SUPPORT = ['ticket', 'phone', 'sla']
const GROUPS = ['core', 'warehouse', 'advanced', 'enterprise']
const MODES = ['standard', 'onRequest', 'quote']
const UNITS = ['month', 'once', 'hour', 'text']
const str = (v: any, max: number) => String(v ?? '').trim().slice(0, max)
const nonNeg = (v: any) => Math.max(0, num(v))
const keyOk = (k: string) => /^[A-Za-z0-9_-]{1,40}$/.test(k)

const planBody = (p: any, orgId: number, forUpdate: boolean) => {
  const body: any = {
    value: str(p.key, 40),
    name: str(p.name, 160),
    nameEN: str(p.nameEn, 160),
    seqNo: Math.round(nonNeg(p.seqNo)),
    isPopular: p.popular === true,
    priceMonthly: nonNeg(p.priceMonthly),
    priceYearly: nonNeg(p.priceYearly),
    includedOrders: Math.round(nonNeg(p.includedOrders)),
    includedUsers: Math.round(nonNeg(p.includedUsers)),
    salesChannels: Math.round(nonNeg(p.salesChannels)),
    extraOrderPrice: nonNeg(p.extraOrderPrice),
    extraUserPrice: nonNeg(p.extraUserPrice),
    SupportLevel: { id: SUPPORT.includes(p.support) ? p.support : 'ticket' },
    description: str(p.description, 500),
    isActive: p.active !== false,
    tableName: PLAN_TABLE
  }
  if (!forUpdate) body.AD_Org_ID = { id: orgId, tableName: 'AD_Org' }
  return body
}

const itemBody = (type: 'feature' | 'addon', it: any, planIdByKey: Map<string, number>, orgId: number, forUpdate: boolean) => {
  const body: any = {
    ItemType: { id: type },
    value: str(it.key, 60),
    name: str(it.name, 90),
    nameEN: str(it.nameEn, 160),
    seqNo: Math.round(nonNeg(it.seqNo)),
    isDefault: type === 'addon' && it.defaultSelected === true,
    isActive: it.active !== false,
    tableName: ITEM_TABLE
  }
  if (type === 'feature') {
    body.FeatureGroup = { id: GROUPS.includes(it.group) ? it.group : 'core' }
    body.Mode = { id: MODES.includes(it.mode) ? it.mode : 'standard' }
    const planId = planIdByKey.get(String(it.minPlanKey || ''))
    if (planId) body.CUST_SoftwarePlan_ID = { id: planId, tableName: PLAN_TABLE }
    else if (forUpdate) body.CUST_SoftwarePlan_ID = null
  } else {
    body.price = nonNeg(it.price)
    body.PriceUnit = { id: UNITS.includes(it.unit) ? it.unit : 'month' }
    body.description = str(it.note, 500)
    body.descriptionEN = str(it.noteEn, 500)
  }
  if (!forUpdate) body.AD_Org_ID = { id: orgId, tableName: 'AD_Org' }
  return body
}

/**
 * Persist a whole catalog edit (settings page "Speichern"): upsert plans, then
 * features + add-ons (FK by plan key), then delete removed rows. A plan that is
 * still referenced by a feature cannot be deleted (400). Returns the fresh catalog.
 */
export const saveSoftwareCatalog = async (event: any, token: string, input: any): Promise<{ catalog: SoftwareCatalog; warnings: string[] }> => {
  const orgId = orgOf(event)
  const warnings: string[] = []
  const plans: any[] = Array.isArray(input?.plans) ? input.plans : []
  const features: any[] = Array.isArray(input?.features) ? input.features : []
  const addons: any[] = Array.isArray(input?.addons) ? input.addons : []
  const deletedPlanIds: number[] = (Array.isArray(input?.deleted?.plans) ? input.deleted.plans : []).map(num).filter((n: number) => n > 0)
  const deletedItemIds: number[] = (Array.isArray(input?.deleted?.items) ? input.deleted.items : []).map(num).filter((n: number) => n > 0)

  // Validation
  const seenPlan = new Set<string>()
  for (const p of plans) {
    const k = str(p.key, 40)
    if (!keyOk(k)) throw createError({ statusCode: 400, statusMessage: `Ungültiger Paket-Schlüssel "${p.key}" (a-z, 0-9, _ -)` })
    if (seenPlan.has(k)) throw createError({ statusCode: 400, statusMessage: `Paket-Schlüssel doppelt: ${k}` })
    seenPlan.add(k)
    if (!str(p.name, 160)) throw createError({ statusCode: 400, statusMessage: `Paket ${k}: Name fehlt` })
  }
  const seenItem = new Set<string>()
  for (const it of [...features.map((f) => ({ ...f, _t: 'feature' })), ...addons.map((a) => ({ ...a, _t: 'addon' }))]) {
    const k = str(it.key, 60)
    if (!keyOk(k)) throw createError({ statusCode: 400, statusMessage: `Ungültiger Schlüssel "${it.key}" (a-z, 0-9, _ -)` })
    const kk = it._t + ':' + k
    if (seenItem.has(kk)) throw createError({ statusCode: 400, statusMessage: `Schlüssel doppelt: ${k}` })
    seenItem.add(kk)
    if (!str(it.name, 90)) throw createError({ statusCode: 400, statusMessage: `${k}: Name fehlt` })
  }
  for (const f of features) {
    if (f.minPlanKey && !seenPlan.has(str(f.minPlanKey, 40))) throw createError({ statusCode: 400, statusMessage: `Funktion ${f.key}: Mindestpaket "${f.minPlanKey}" existiert nicht` })
  }
  if (deletedPlanIds.length) {
    const referenced = features.filter((f) => plans.some((p) => deletedPlanIds.includes(num(p.id)) && p.key === f.minPlanKey))
    if (referenced.length) throw createError({ statusCode: 400, statusMessage: 'Paket wird noch von Funktionen verwendet — erst Funktionen umhängen oder Paket deaktivieren' })
  }

  // 1) plans
  const planIdByKey = new Map<string, number>()
  for (const p of plans) {
    const id = num(p.id)
    const body = planBody(p, orgId, id > 0)
    const res: any = id > 0
      ? await fetchHelper(event, `models/${PLAN_MODEL}/${id}`, 'PUT', token, body)
      : await fetchHelper(event, `models/${PLAN_MODEL}`, 'POST', token, body)
    const newId = num(res?.id) || id
    if (!(newId > 0)) throw createError({ statusCode: 500, statusMessage: `Paket ${p.key}: Speichern fehlgeschlagen` })
    planIdByKey.set(str(p.key, 40), newId)
  }
  // 2) items
  const upsertItem = async (type: 'feature' | 'addon', it: any) => {
    const id = num(it.id)
    const body = itemBody(type, it, planIdByKey, orgId, id > 0)
    const res: any = id > 0
      ? await fetchHelper(event, `models/${ITEM_MODEL}/${id}`, 'PUT', token, body)
      : await fetchHelper(event, `models/${ITEM_MODEL}`, 'POST', token, body)
    if (!(num(res?.id) > 0 || id > 0)) throw createError({ statusCode: 500, statusMessage: `${it.key}: Speichern fehlgeschlagen` })
  }
  for (const f of features) await upsertItem('feature', f)
  for (const a of addons) await upsertItem('addon', a)
  // 3) deletes (items before plans)
  for (const id of deletedItemIds) {
    try { await fetchHelper(event, `models/${ITEM_MODEL}/${id}`, 'DELETE', token, null) } catch (e: any) { warnings.push(`Eintrag ${id} konnte nicht gelöscht werden: ${e?.data?.detail || e?.message || e}`) }
  }
  for (const id of deletedPlanIds) {
    try { await fetchHelper(event, `models/${PLAN_MODEL}/${id}`, 'DELETE', token, null) } catch (e: any) { warnings.push(`Paket ${id} konnte nicht gelöscht werden: ${e?.data?.detail || e?.message || e}`) }
  }
  invalidateSoftwareCatalogCache()
  return { catalog: await loadSoftwareCatalog(event, token, true), warnings }
}

/**
 * "Standardwerte importieren": insert every default plan / feature / add-on whose
 * key does not exist yet (idempotent). Needs both tables to exist.
 */
export const seedSoftwareCatalog = async (event: any, token: string): Promise<{ created: { plans: number; features: number; addons: number }; catalog: SoftwareCatalog }> => {
  const orgId = orgOf(event)
  const current = await loadSoftwareCatalog(event, token, true)
  if (current.missingTables.length) throw createError({ statusCode: 400, statusMessage: `Tabelle(n) fehlen: ${current.missingTables.join(', ')}` })
  const persisted: any = (current as any).persisted || {}
  const havePlans = new Map<string, number>((persisted.plans || []).map((p: CatalogPlan) => [p.key, Number(p.id)]))
  const haveFeatures = new Set<string>((persisted.features || []).map((f: CatalogFeature) => f.key))
  const haveAddons = new Set<string>((persisted.addons || []).map((a: CatalogAddon) => a.key))
  const created = { plans: 0, features: 0, addons: 0 }
  for (const p of DEFAULT_SOFTWARE_CATALOG.plans) {
    if (havePlans.has(p.key)) continue
    const res: any = await fetchHelper(event, `models/${PLAN_MODEL}`, 'POST', token, planBody(p, orgId, false))
    if (num(res?.id) > 0) { havePlans.set(p.key, num(res.id)); created.plans++ }
  }
  for (const f of DEFAULT_SOFTWARE_CATALOG.features) {
    if (haveFeatures.has(f.key)) continue
    await fetchHelper(event, `models/${ITEM_MODEL}`, 'POST', token, itemBody('feature', f, havePlans, orgId, false))
    created.features++
  }
  for (const a of DEFAULT_SOFTWARE_CATALOG.addons) {
    if (haveAddons.has(a.key)) continue
    await fetchHelper(event, `models/${ITEM_MODEL}`, 'POST', token, itemBody('addon', a, havePlans, orgId, false))
    created.addons++
  }
  invalidateSoftwareCatalogCache()
  return { created, catalog: await loadSoftwareCatalog(event, token, true) }
}