/**
 * Store for the two marketplace-listing tables in iDempiere:
 *   CUST_MarketplaceListing        (models/cust_marketplacelisting)        — one row per order source × listing
 *   CUST_MarketplaceListingChange  (models/cust_marketplacelistingchange)  — outbox + audit (queued → sending → sent → confirmed | error)
 *
 * Two fetch flavours share one signature (`IdempiereFetch`): `eventFetch` = the
 * request's fetchHelper (caller token, UpdatedBy = user); `serviceFetch` = the
 * non-expiring service token for the background flush (no h3 event available).
 *
 * Write casing (iDempiere REST): scalars lowercase-first, FK objects `{ id,
 * tableName }`, list values `{ id: 'code' }`; the sync-status column is named
 * `CUST_ListingSyncStatus` (kept as created in the AD) and is written under that
 * name. Reads tolerate both casings; REST omits NULL columns. Verified against prod
 * 2026-10-26 (see .claude/rules/marketplaces/marketplace-listings.md).
 */
import { string } from 'alga-js'
import fetchHelper from '../fetchHelper'

export const LISTING_MODEL = 'cust_marketplacelisting'
export const LISTING_TABLE = 'CUST_MarketplaceListing'
export const CHANGE_MODEL = 'cust_marketplacelistingchange'
export const CHANGE_TABLE = 'CUST_MarketplaceListingChange'

export type IdempiereFetch = (path: string, method?: 'GET' | 'POST' | 'PUT' | 'DELETE', body?: any) => Promise<any>

export const eventFetch = (event: any, token: string): IdempiereFetch =>
  (path, method = 'GET', body = null) => fetchHelper(event, path, method, token, body)

export const serviceFetch = (): IdempiereFetch => {
  const config: any = useRuntimeConfig()
  const base = String(config.api?.url || '').replace(/\/$/, '')
  const token = config.api?.idempieretoken
  if (!base || !token) throw new Error('iDempiere service token / url not configured (api.url, api.idempieretoken)')
  return (path, method = 'GET', body = null) => $fetch<any>(`${base}/${path}`, {
    method,
    retry: 0,
    timeout: 90_000,
    headers: { 'Content-Type': 'application/json', Accept: 'application/json', Authorization: 'Bearer ' + token },
    ...(body ? { body } : {})
  } as any)
}

export const enc = (s: string) => string.urlEncode(s)
export const esc = (s: string) => String(s ?? '').replace(/'/g, "''")
const fk = (v: any): number | null => (v && typeof v === 'object') ? (Number(v.id) || null) : (v != null ? Number(v) || null : null)
const fkName = (v: any): string | null => (v && typeof v === 'object') ? (v.identifier ?? null) : null
const lst = (v: any): string | null => (v && typeof v === 'object') ? (v.id != null ? String(v.id) : null) : (v != null ? String(v) : null)
const num = (v: any): number | null => (v == null || v === '') ? null : (Number.isFinite(Number(v)) ? Number(v) : null)
const str = (v: any): string | null => (v == null) ? null : String(v)

/**
 * iDempiere REST emits Created/Updated (and every column IT fills) as the server's
 * LOCAL wall-clock time with a literal "Z" — "2026-10-27T12:45:01Z" means 12:45 in
 * Europe/Berlin, not UTC. Columns WE write (restDate → UTC string) come back unchanged.
 * Convert the server-written ones to real UTC ISO so age calculations (debounce,
 * stale claims) are not off by the UTC offset. Timezone: IDEMPIERE_TZ (Europe/Berlin).
 */
const SERVER_TZ = process.env.IDEMPIERE_TZ || 'Europe/Berlin'
const tzOffsetMs = (utcMs: number): number => {
  try {
    const dtf = new Intl.DateTimeFormat('en-US', { timeZone: SERVER_TZ, hourCycle: 'h23', year: 'numeric', month: '2-digit', day: '2-digit', hour: '2-digit', minute: '2-digit', second: '2-digit' })
    const p: Record<string, string> = Object.fromEntries(dtf.formatToParts(new Date(utcMs)).map(x => [x.type, x.value]))
    return Date.UTC(Number(p.year), Number(p.month) - 1, Number(p.day), Number(p.hour), Number(p.minute), Number(p.second)) - utcMs
  } catch { return 0 }
}
export const serverLocalToIso = (v: any): string | null => {
  if (v == null || v === '') return null
  const m = /^(\d{4})-(\d{2})-(\d{2})[T ](\d{2}):(\d{2}):(\d{2})/.exec(String(v))
  if (!m) return String(v)
  const [y, mo, d, h, mi, se] = m.slice(1, 7).map(Number)
  const guess = Date.UTC(y!, mo! - 1, d!, h!, mi!, se!)
  // offset at the guessed instant; re-evaluate once in case the guess straddles a DST switch
  const off1 = tzOffsetMs(guess)
  const off2 = tzOffsetMs(guess - off1)
  return new Date(guess - off2).toISOString()
}
const pick = (r: any, ...keys: string[]) => { for (const k of keys) if (r?.[k] !== undefined) return r[k]; return undefined }

/* ------------------------------ types ------------------------------ */
export type ListingStatus = 'pending' | 'active' | 'inactive' | 'delisted' | 'error'
export type SyncStatus = 'synced' | 'queued' | 'sending' | 'sent' | 'error' | 'drift'
export type ChangeType = 'create' | 'update' | 'delist' | 'relist' | 'delete'
export type ChangeStatus = 'queued' | 'sending' | 'sent' | 'confirmed' | 'error' | 'cancelled' | 'superseded'
export type DeliveryMode = 'patch' | 'feed'
export type ChangeField = 'price' | 'qty' | 'minprice' | 'maxprice' | 'sale'

export interface ListingRow {
  id: number; uid: string | null
  orgId: number | null; orderSourceId: number | null; orderSourceName: string | null
  productId: number | null; productIdentifier: string | null
  name: string; sellerSku: string; listingKey: string
  externalProductId: string | null; externalInventoryId: string | null; productType: string | null
  listingStatus: ListingStatus
  price: number | null; minPrice: number | null; maxPrice: number | null
  salePrice: number | null; saleFrom: string | null; saleTo: string | null
  currencyId: number | null; currency: string | null; isPriceTaxIncluded: boolean
  qty: number | null; fulfillmentChannel: string | null
  remotePrice: number | null; remoteQty: number | null; remoteStatus: string | null; remoteTitle: string | null; remoteCondition: string | null
  dateListed: string | null; dateDelisted: string | null; lastSyncedAt: string | null; lastChangeAt: string | null
  syncStatus: SyncStatus; syncError: string | null
  buyBoxStatus: 'won' | 'lost' | 'unknown' | null; buyBoxPrice: number | null; lowestOtherPrice: number | null; competitorCount: number | null
  feesEstimate: number | null; feesJson: any; competitionCheckedAt: string | null
  meta: any; description: string | null; isActive: boolean
  created: string | null; updated: string | null
}

export interface ChangeRow {
  id: number; uid: string | null
  orgId: number | null; listingId: number | null; orderSourceId: number | null
  changeType: ChangeType; fieldName: ChangeField | null; oldValue: string | null; newValue: string | null
  changeStatus: ChangeStatus; deliveryMode: DeliveryMode | null
  batchId: string | null; feedId: string | null; submissionId: string | null; messageId: number | null
  sentAt: string | null; confirmedAt: string | null; attemptCount: number; errorMsg: string | null; description: string | null
  createdBy: { id: number | null; name: string | null }; created: string | null; updated: string | null
}

const safeJson = (s: any) => { if (s == null || s === '') return null; if (typeof s === 'object') return s; try { return JSON.parse(s) } catch { return null } }

/* ------------------------------ mapping ------------------------------ */
export const mapListing = (r: any): ListingRow | null => {
  if (!r?.id) return null
  return {
    id: Number(r.id), uid: r.uid || null,
    orgId: fk(r.AD_Org_ID), orderSourceId: fk(r.C_OrderSource_ID), orderSourceName: fkName(r.C_OrderSource_ID),
    productId: fk(r.M_Product_ID), productIdentifier: fkName(r.M_Product_ID),
    name: str(pick(r, 'Name', 'name')) || '',
    sellerSku: str(pick(r, 'SellerSKU', 'sellerSKU', 'sellersku')) || '',
    listingKey: str(pick(r, 'ListingKey', 'listingKey')) || '',
    externalProductId: str(pick(r, 'ExternalProductId', 'externalProductId')),
    externalInventoryId: str(pick(r, 'ExternalInventoryId', 'externalInventoryId')),
    productType: str(pick(r, 'ProductType', 'productType')),
    listingStatus: (lst(pick(r, 'ListingStatus', 'listingStatus')) || 'pending') as ListingStatus,
    price: num(pick(r, 'Price', 'price')), minPrice: num(pick(r, 'MinPrice', 'minPrice')), maxPrice: num(pick(r, 'MaxPrice', 'maxPrice')),
    salePrice: num(pick(r, 'SalePrice', 'salePrice')), saleFrom: str(pick(r, 'SaleFrom', 'saleFrom')), saleTo: str(pick(r, 'SaleTo', 'saleTo')),
    currencyId: fk(r.C_Currency_ID), currency: fkName(r.C_Currency_ID),
    isPriceTaxIncluded: (pick(r, 'IsPriceTaxIncluded', 'isPriceTaxIncluded') ?? true) === true,
    qty: num(pick(r, 'Qty', 'qty')), fulfillmentChannel: str(pick(r, 'FulfillmentChannel', 'fulfillmentChannel')),
    remotePrice: num(pick(r, 'RemotePrice', 'remotePrice')), remoteQty: num(pick(r, 'RemoteQty', 'remoteQty')),
    remoteStatus: str(pick(r, 'RemoteStatus', 'remoteStatus')), remoteTitle: str(pick(r, 'RemoteTitle', 'remoteTitle')), remoteCondition: str(pick(r, 'RemoteCondition', 'remoteCondition')),
    dateListed: str(pick(r, 'DateListed', 'dateListed')), dateDelisted: str(pick(r, 'DateDelisted', 'dateDelisted')),
    lastSyncedAt: str(pick(r, 'LastSyncedAt', 'lastSyncedAt')), lastChangeAt: str(pick(r, 'LastChangeAt', 'lastChangeAt')),
    syncStatus: (lst(pick(r, 'CUST_ListingSyncStatus', 'cUST_ListingSyncStatus', 'SyncStatus')) || 'synced') as SyncStatus,
    syncError: str(pick(r, 'SyncError', 'syncError')),
    buyBoxStatus: (lst(pick(r, 'BuyBoxStatus', 'buyBoxStatus')) as any) || null,
    buyBoxPrice: num(pick(r, 'BuyBoxPrice', 'buyBoxPrice')), lowestOtherPrice: num(pick(r, 'LowestOtherPrice', 'lowestOtherPrice')),
    competitorCount: num(pick(r, 'CompetitorCount', 'competitorCount')),
    feesEstimate: num(pick(r, 'FeesEstimate', 'feesEstimate')), feesJson: safeJson(pick(r, 'FeesJson', 'feesJson')),
    competitionCheckedAt: str(pick(r, 'CompetitionCheckedAt', 'competitionCheckedAt')),
    meta: safeJson(pick(r, 'MetaJson', 'metaJson')), description: str(pick(r, 'Description', 'description')),
    isActive: (pick(r, 'IsActive', 'isActive') ?? true) === true,
    created: serverLocalToIso(r.Created), updated: serverLocalToIso(r.Updated)
  }
}

export const mapChange = (r: any): ChangeRow | null => {
  if (!r?.id) return null
  return {
    id: Number(r.id), uid: r.uid || null,
    orgId: fk(r.AD_Org_ID), listingId: fk(r.CUST_MarketplaceListing_ID), orderSourceId: fk(r.C_OrderSource_ID),
    changeType: (lst(pick(r, 'ChangeType', 'changeType')) || 'update') as ChangeType,
    fieldName: (str(pick(r, 'FieldName', 'fieldName')) as any) || null,
    oldValue: str(pick(r, 'OldValue', 'oldValue')), newValue: str(pick(r, 'NewValue', 'newValue')),
    changeStatus: (lst(pick(r, 'ChangeStatus', 'changeStatus')) || 'queued') as ChangeStatus,
    deliveryMode: (lst(pick(r, 'DeliveryMode', 'deliveryMode')) as any) || null,
    batchId: str(pick(r, 'BatchId', 'batchId')), feedId: str(pick(r, 'FeedId', 'feedId')), submissionId: str(pick(r, 'SubmissionId', 'submissionId')),
    messageId: num(pick(r, 'MessageID', 'messageID', 'MessageId')),
    sentAt: str(pick(r, 'SentAt', 'sentAt')), confirmedAt: str(pick(r, 'ConfirmedAt', 'confirmedAt')),
    attemptCount: num(pick(r, 'AttemptCount', 'attemptCount')) || 0,
    errorMsg: str(pick(r, 'ErrorMsg', 'errorMsg')), description: str(pick(r, 'Description', 'description')),
    createdBy: { id: fk(r.CreatedBy), name: fkName(r.CreatedBy) },
    created: serverLocalToIso(r.Created), updated: serverLocalToIso(r.Updated)
  }
}

/* ------------------------------ write bodies ------------------------------ */
const fkObj = (id: number | null | undefined, tableName: string) => (id ? { id: Number(id), tableName } : 0)
// iDempiere REST accepts exactly yyyy-MM-dd'T'HH:mm:ss'Z' (no milliseconds) or a plain yyyy-MM-dd.
export const restDate = (v: any): string => {
  if (v instanceof Date) return v.toISOString().replace(/\.\d{3}Z$/, 'Z')
  const s = String(v)
  if (/^\d{4}-\d{2}-\d{2}$/.test(s)) return s
  const d = new Date(s)
  return Number.isNaN(d.getTime()) ? s : d.toISOString().replace(/\.\d{3}Z$/, 'Z')
}
const dateOrNull = (v: any) => (v == null || v === '') ? null : restDate(v)

/** Partial listing → REST body (only keys that are present in `p` are written). */
export const listingWriteBody = (p: Partial<ListingRow>): any => {
  const b: any = {}
  if (p.orgId !== undefined) b.AD_Org_ID = { id: p.orgId, tableName: 'AD_Org' }
  if (p.orderSourceId !== undefined) b.C_OrderSource_ID = fkObj(p.orderSourceId, 'C_OrderSource')
  if (p.productId !== undefined) b.M_Product_ID = fkObj(p.productId, 'M_Product')
  if (p.currencyId !== undefined) b.C_Currency_ID = fkObj(p.currencyId, 'C_Currency')
  if (p.name !== undefined) b.name = String(p.name || '').slice(0, 500)
  if (p.sellerSku !== undefined) b.sellerSKU = String(p.sellerSku || '').slice(0, 160)
  if (p.listingKey !== undefined) b.listingKey = String(p.listingKey || '').slice(0, 160)
  if (p.externalProductId !== undefined) b.externalProductId = p.externalProductId ? String(p.externalProductId).slice(0, 100) : null
  if (p.externalInventoryId !== undefined) b.externalInventoryId = p.externalInventoryId ? String(p.externalInventoryId).slice(0, 160) : null
  if (p.productType !== undefined) b.productType = p.productType ? String(p.productType).slice(0, 100) : null
  if (p.listingStatus !== undefined) b.listingStatus = { id: p.listingStatus }
  if (p.price !== undefined) b.price = p.price
  if (p.minPrice !== undefined) b.minPrice = p.minPrice
  if (p.maxPrice !== undefined) b.maxPrice = p.maxPrice
  if (p.salePrice !== undefined) b.salePrice = p.salePrice
  if (p.saleFrom !== undefined) b.saleFrom = dateOrNull(p.saleFrom)
  if (p.saleTo !== undefined) b.saleTo = dateOrNull(p.saleTo)
  if (p.isPriceTaxIncluded !== undefined) b.isPriceTaxIncluded = !!p.isPriceTaxIncluded
  if (p.qty !== undefined) b.qty = p.qty
  if (p.fulfillmentChannel !== undefined) b.fulfillmentChannel = p.fulfillmentChannel ? String(p.fulfillmentChannel).slice(0, 40) : null
  if (p.remotePrice !== undefined) b.remotePrice = p.remotePrice
  if (p.remoteQty !== undefined) b.remoteQty = p.remoteQty
  if (p.remoteStatus !== undefined) b.remoteStatus = p.remoteStatus ? String(p.remoteStatus).slice(0, 30) : null
  if (p.remoteTitle !== undefined) b.remoteTitle = p.remoteTitle ? String(p.remoteTitle).slice(0, 500) : null
  if (p.remoteCondition !== undefined) b.remoteCondition = p.remoteCondition ? String(p.remoteCondition).slice(0, 160) : null
  if (p.dateListed !== undefined) b.dateListed = dateOrNull(p.dateListed)
  if (p.dateDelisted !== undefined) b.dateDelisted = dateOrNull(p.dateDelisted)
  if (p.lastSyncedAt !== undefined) b.lastSyncedAt = dateOrNull(p.lastSyncedAt)
  if (p.lastChangeAt !== undefined) b.lastChangeAt = dateOrNull(p.lastChangeAt)
  if (p.syncStatus !== undefined) b.CUST_ListingSyncStatus = { id: p.syncStatus }
  if (p.syncError !== undefined) b.syncError = String(p.syncError || '').slice(0, 2000)
  if (p.buyBoxStatus !== undefined) b.buyBoxStatus = p.buyBoxStatus ? { id: p.buyBoxStatus } : null
  if (p.buyBoxPrice !== undefined) b.buyBoxPrice = p.buyBoxPrice
  if (p.lowestOtherPrice !== undefined) b.lowestOtherPrice = p.lowestOtherPrice
  if (p.competitorCount !== undefined) b.competitorCount = p.competitorCount
  if (p.feesEstimate !== undefined) b.feesEstimate = p.feesEstimate
  if (p.feesJson !== undefined) b.feesJson = p.feesJson == null ? null : (typeof p.feesJson === 'string' ? p.feesJson : JSON.stringify(p.feesJson))
  if (p.competitionCheckedAt !== undefined) b.competitionCheckedAt = dateOrNull(p.competitionCheckedAt)
  if (p.meta !== undefined) b.metaJson = p.meta == null ? null : (typeof p.meta === 'string' ? p.meta : JSON.stringify(p.meta))
  if (p.description !== undefined) b.description = p.description ? String(p.description).slice(0, 255) : null
  if (p.isActive !== undefined) b.isActive = !!p.isActive
  return b
}

export const changeWriteBody = (p: Partial<ChangeRow>): any => {
  const b: any = {}
  if (p.orgId !== undefined) b.AD_Org_ID = { id: p.orgId, tableName: 'AD_Org' }
  if (p.listingId !== undefined) b.CUST_MarketplaceListing_ID = fkObj(p.listingId, LISTING_TABLE)
  if (p.orderSourceId !== undefined) b.C_OrderSource_ID = fkObj(p.orderSourceId, 'C_OrderSource')
  if (p.changeType !== undefined) b.changeType = { id: p.changeType }
  if (p.fieldName !== undefined) b.fieldName = p.fieldName
  if (p.oldValue !== undefined) b.oldValue = p.oldValue == null ? null : String(p.oldValue).slice(0, 255)
  if (p.newValue !== undefined) b.newValue = p.newValue == null ? null : String(p.newValue).slice(0, 255)
  if (p.changeStatus !== undefined) b.changeStatus = { id: p.changeStatus }
  if (p.deliveryMode !== undefined) b.deliveryMode = p.deliveryMode ? { id: p.deliveryMode } : null
  if (p.batchId !== undefined) b.batchId = p.batchId ? String(p.batchId).slice(0, 80) : null
  if (p.feedId !== undefined) b.feedId = p.feedId ? String(p.feedId).slice(0, 80) : null
  if (p.submissionId !== undefined) b.submissionId = p.submissionId ? String(p.submissionId).slice(0, 80) : null
  if (p.messageId !== undefined) b.messageID = p.messageId
  if (p.sentAt !== undefined) b.sentAt = dateOrNull(p.sentAt)
  if (p.confirmedAt !== undefined) b.confirmedAt = dateOrNull(p.confirmedAt)
  if (p.attemptCount !== undefined) b.attemptCount = p.attemptCount
  if (p.errorMsg !== undefined) b.errorMsg = String(p.errorMsg || '').slice(0, 2000)
  if (p.description !== undefined) b.description = p.description ? String(p.description).slice(0, 255) : null
  return b
}

/* ------------------------------ probe ------------------------------ */
let probe: { ok: boolean; at: number; error?: string } | null = null
export const listingTablesAvailable = async (f: IdempiereFetch, force = false): Promise<boolean> => {
  if (!force && probe && Date.now() - probe.at < (probe.ok ? 10 * 60 * 1000 : 60 * 1000)) return probe.ok
  try {
    const a: any = await f(`models/${LISTING_MODEL}?$top=1&$select=ListingKey`)
    const b: any = await f(`models/${CHANGE_MODEL}?$top=1&$select=FieldName`)
    probe = { ok: Array.isArray(a?.records) && Array.isArray(b?.records), at: Date.now() }
  } catch (e: any) {
    const st = Number(e?.status || e?.statusCode || 0)
    if (st === 401 || st === 403) throw e
    probe = { ok: false, at: Date.now(), error: e?.data?.detail || e?.message || String(e) }
  }
  return probe.ok
}
export const listingBackendInfo = () => ({ available: !!probe?.ok, error: probe?.error || null, checkedAt: probe?.at || null })

/* ------------------------------ reads ------------------------------ */
const records = (res: any) => Array.isArray(res?.records) ? res.records : []

export const listListings = async (f: IdempiereFetch, orderSourceId: number, opts: { status?: ListingStatus; syncStatus?: SyncStatus | SyncStatus[]; activeOnly?: boolean; top?: number } = {}): Promise<ListingRow[]> => {
  if (!orderSourceId) return []
  const parts = [`C_OrderSource_ID eq ${Number(orderSourceId)}`]
  if (opts.activeOnly !== false) parts.push('IsActive eq true')
  if (opts.status) parts.push(`ListingStatus eq '${esc(opts.status)}'`)
  if (opts.syncStatus) {
    const list = Array.isArray(opts.syncStatus) ? opts.syncStatus : [opts.syncStatus]
    parts.push('(' + list.map(s => `CUST_ListingSyncStatus eq '${esc(s)}'`).join(' or ') + ')')
  }
  const res = await f(`models/${LISTING_MODEL}?$filter=${enc(parts.join(' and '))}&$orderby=${enc('Name asc')}&$top=${opts.top || 5000}`)
  return records(res).map(mapListing).filter(Boolean) as ListingRow[]
}

export const getListing = async (f: IdempiereFetch, id: number): Promise<ListingRow | null> => {
  if (!id) return null
  try { return mapListing(await f(`models/${LISTING_MODEL}/${Number(id)}`)) } catch (e: any) {
    if (Number(e?.status || e?.statusCode) === 404) return null
    throw e
  }
}

export const getListingsByIds = async (f: IdempiereFetch, ids: number[]): Promise<ListingRow[]> => {
  const out: ListingRow[] = []
  const uniq = [...new Set(ids.filter(Boolean))]
  for (let i = 0; i < uniq.length; i += 50) {
    const chunk = uniq.slice(i, i + 50)
    const res = await f(`models/${LISTING_MODEL}?$filter=${enc('(' + chunk.map(id => `CUST_MarketplaceListing_ID eq ${Number(id)}`).join(' or ') + ')')}&$top=${chunk.length}`)
    out.push(...(records(res).map(mapListing).filter(Boolean) as ListingRow[]))
  }
  return out
}

export const findListingByKey = async (f: IdempiereFetch, orderSourceId: number, listingKey: string): Promise<ListingRow | null> => {
  if (!orderSourceId || !listingKey) return null
  const res = await f(`models/${LISTING_MODEL}?$filter=${enc(`C_OrderSource_ID eq ${Number(orderSourceId)} and ListingKey eq '${esc(listingKey)}'`)}&$top=1`)
  return mapListing(records(res)[0])
}

export const listChanges = async (f: IdempiereFetch, filter: { orderSourceId?: number; listingId?: number; status?: ChangeStatus | ChangeStatus[]; feedId?: string; deliveryMode?: DeliveryMode }, top = 2000): Promise<ChangeRow[]> => {
  const parts: string[] = []
  if (filter.orderSourceId) parts.push(`C_OrderSource_ID eq ${Number(filter.orderSourceId)}`)
  if (filter.listingId) parts.push(`CUST_MarketplaceListing_ID eq ${Number(filter.listingId)}`)
  if (filter.status) {
    const list = Array.isArray(filter.status) ? filter.status : [filter.status]
    parts.push('(' + list.map(s => `ChangeStatus eq '${esc(s)}'`).join(' or ') + ')')
  }
  if (filter.feedId) parts.push(`FeedId eq '${esc(filter.feedId)}'`)
  if (filter.deliveryMode) parts.push(`DeliveryMode eq '${esc(filter.deliveryMode)}'`)
  const q = parts.length ? `$filter=${enc(parts.join(' and '))}&` : ''
  const res = await f(`models/${CHANGE_MODEL}?${q}$orderby=${enc('Created asc')}&$top=${top}`)
  return records(res).map(mapChange).filter(Boolean) as ChangeRow[]
}

/* ------------------------------ writes ------------------------------ */
export type NewListing = Omit<Partial<ListingRow>, 'orgId' | 'orderSourceId' | 'sellerSku' | 'listingKey' | 'name'> & { orgId: number; orderSourceId: number; sellerSku: string; listingKey: string; name: string }
export const insertListing = async (f: IdempiereFetch, data: NewListing): Promise<ListingRow> => {
  const body = { isActive: true, ...listingWriteBody(data), tableName: LISTING_TABLE }
  const created = await f(`models/${LISTING_MODEL}`, 'POST', body)
  const row = mapListing(created)
  if (!row) throw new Error('Listing could not be created')
  return row
}

export const updateListing = async (f: IdempiereFetch, id: number, patch: Partial<ListingRow>): Promise<ListingRow | null> => {
  const body = listingWriteBody(patch)
  if (!Object.keys(body).length) return null
  return mapListing(await f(`models/${LISTING_MODEL}/${Number(id)}`, 'PUT', body))
}

/** Listing of an order source by seller SKU (Shopify keys are variant GIDs, so the SKU is the human handle). */
export const findListingBySku = async (f: IdempiereFetch, orderSourceId: number, sku: string): Promise<ListingRow | null> => {
  if (!orderSourceId || !sku) return null
  const res: any = await f(`models/${LISTING_MODEL}?$filter=${enc(`C_OrderSource_ID eq ${Number(orderSourceId)} and SellerSKU eq '${esc(sku)}'`)}&$top=1`)
  return mapListing(res?.records?.[0])
}

export const deleteListing = async (f: IdempiereFetch, id: number): Promise<void> => { await f(`models/${LISTING_MODEL}/${Number(id)}`, 'DELETE') }

export type NewChange = Omit<Partial<ChangeRow>, 'orgId' | 'listingId' | 'orderSourceId' | 'changeType'> & { orgId: number; listingId: number; orderSourceId: number; changeType: ChangeType }
export const insertChange = async (f: IdempiereFetch, data: NewChange): Promise<ChangeRow> => {
  const body = { isActive: true, changeStatus: { id: 'queued' }, attemptCount: 0, ...changeWriteBody(data), tableName: CHANGE_TABLE }
  const row = mapChange(await f(`models/${CHANGE_MODEL}`, 'POST', body))
  if (!row) throw new Error('Change could not be created')
  return row
}

export const updateChange = async (f: IdempiereFetch, id: number, patch: Partial<ChangeRow>): Promise<ChangeRow | null> => {
  const body = changeWriteBody(patch)
  if (!Object.keys(body).length) return null
  return mapChange(await f(`models/${CHANGE_MODEL}/${Number(id)}`, 'PUT', body))
}

export const deleteChange = async (f: IdempiereFetch, id: number): Promise<void> => { await f(`models/${CHANGE_MODEL}/${Number(id)}`, 'DELETE') }

/**
 * Last write wins: queued rows of the same listing + field (or same type for
 * create/delist/relist/delete) are marked `superseded`, the new change is
 * inserted and the listing flips to SyncStatus 'queued'.
 */
export const queueChange = async (f: IdempiereFetch, listing: ListingRow, change: { changeType: ChangeType; fieldName?: ChangeField | null; oldValue?: any; newValue?: any; description?: string | null }): Promise<ChangeRow> => {
  const open = await listChanges(f, { listingId: listing.id, status: ['queued'] }, 200)
  for (const c of open) {
    const sameField = change.changeType === 'update' ? (c.changeType === 'update' && c.fieldName === (change.fieldName || null)) : (c.changeType === change.changeType)
    if (sameField) await updateChange(f, c.id, { changeStatus: 'superseded' })
  }
  const row = await insertChange(f, {
    orgId: listing.orgId!, listingId: listing.id, orderSourceId: listing.orderSourceId!,
    changeType: change.changeType, fieldName: change.fieldName ?? null,
    oldValue: change.oldValue == null ? null : String(change.oldValue), newValue: change.newValue == null ? null : String(change.newValue),
    description: change.description ?? null
  })
  await updateListing(f, listing.id, { syncStatus: 'queued', syncError: '', lastChangeAt: new Date().toISOString() })
  return row
}