/**
 * Shipping price sheet — resolver + Tier write path.
 *
 * Reads CUST_CarrierRate (contract cost), CUST_ShippingCost_Default (Tier 1 sell)
 * and CUST_ShippingCost_BPOverride (Tier 2 sell, merchant org) and produces one
 * row per service/country with a price per weight column:
 *   Tier 2 match → Tier 1 match → derived (contract cost × (1 + markup %)).
 * The Tier match mirrors ../logship-scripts calculate.py exactly: country +
 * inclusive weight range (or 0/0), validity only when both bounds are set,
 * `base + weight × perkg`; the Kleinpaket row is global (country ignored).
 *
 * Billing can only bill ONE price per country, so Tier rows are applied to the
 * primary services only (TIER_BILLED_SERVICES); Economy / Warenpost rows are
 * always derived and marked as not billable through the Tier tables.
 */
import fetchHelper from '../fetchHelper'
import { loadCarrierRates, loadCountryMaps, loadShipperGroups } from './carrierRates'
import { listFixtures, loadFixture } from './carrierRateImport'
import { isEuCountryCode } from '../../../app/utils/shippingCountryMap'
import {
  applyMarkup, priceAt, tierPriceAt, round2, weightMatches, validityMatches, isOneSidedValidity, sectionOfService,
  GERMANY_COUNTRY_ID, TIER_RANGE_STEP, DEFAULT_MARKUP_PCT, defaultSheetOptions, carrierCodeOf
} from '../../../app/utils/shippingPriceSheet'
import type { CarrierRate, FixtureService, PriceSheet, SheetCell, SheetOptions, SheetRow, SurchargeRow, TierRow, SectionKey, CellSource } from '../../../app/utils/shippingPriceSheet'

const TIER1_MODEL = 'models/cust_shippingcost_default'
const TIER2_MODEL = 'models/cust_shippingcost_bpoverride'

/** Services whose sell price comes from the Tier tables (billing bills one price per country). */
export const TIER_BILLED_SERVICES = ['DHL_PAKET', 'DHL_PAKET_INT_PREMIUM']
export const KLEINPAKET_SERVICES = ['DHL_KLEINPAKET']

const n = (v: any, d = 0): number => (Number.isFinite(Number(v)) ? Number(v) : d)
const dateOnly = (v: any): string => (v ? String(v).slice(0, 10) : '')

export const mapTierRow = (r: any, table: 'default' | 'override'): TierRow => ({
  id: n(r?.id),
  table,
  organizationId: n(r?.AD_Org_ID?.id),
  bpartnerId: r?.C_BPartner_ID?.id ? n(r.C_BPartner_ID.id) : null,
  shipperId: r?.M_Shipper_ID?.id ? n(r.M_Shipper_ID.id) : null,
  countryId: r?.C_Country_ID?.id ? n(r.C_Country_ID.id) : null,
  basePrice: n(r?.ShippingBasePrice ?? r?.shippingBasePrice),
  pricePerKg: n(r?.ShippingPricePerKG ?? r?.shippingPricePerKG),
  rangeFrom: n(r?.RangeWeightFrom ?? r?.rangeWeightFrom),
  rangeTo: n(r?.RangeWeightTo ?? r?.rangeWeightTo),
  validFrom: dateOnly(r?.ValidFrom ?? r?.validFrom),
  validTo: dateOnly(r?.ValidTo ?? r?.validTo),
  isKleinpaket: (r?.isDHLKleinPaket ?? r?.IsDHLKleinPaket) === true,
  isActive: (r?.IsActive ?? r?.isActive) !== false
})

export const loadTier1 = async (event: any, token: string): Promise<TierRow[]> => {
  const res: any = await fetchHelper(event, `${TIER1_MODEL}?$filter=${encodeURIComponent('IsActive eq true')}&$top=1000`, 'GET', token, null)
  return (res?.records || []).map((r: any) => mapTierRow(r, 'default'))
}

export const loadTier2 = async (event: any, token: string, organizationId: number): Promise<TierRow[]> => {
  if (!organizationId) return []
  const res: any = await fetchHelper(event, `${TIER2_MODEL}?$filter=${encodeURIComponent(`AD_Org_ID eq ${n(organizationId)} and IsActive eq true`)}&$top=1000`, 'GET', token, null)
  return (res?.records || []).map((r: any) => mapTierRow(r, 'override'))
}

/** Priority of calculate.py (lower = more specific). */
const tierPriority = (r: TierRow): number => {
  const bp = r.table === 'override' && !!r.bpartnerId
  const w = r.rangeFrom !== 0 || r.rangeTo !== 0
  const d = !!r.validFrom
  if (r.table === 'override') {
    if (bp && w && d) return 1
    if (bp && w) return 2
    if (bp && d) return 3
    if (w && d) return 4
    if (bp) return 5
    if (w) return 6
    if (d) return 7
    return 8
  }
  if (w && d) return 1
  if (w) return 2
  if (d) return 3
  return 4
}

export interface TierMatch { row: TierRow; ties: number }

export const findTierMatch = (rows: TierRow[], countryId: number, weight: number, validOn: string): TierMatch | null => {
  const hits = rows
    .filter((r) => r.isActive && !r.isKleinpaket && r.countryId === countryId && weightMatches(weight, r.rangeFrom, r.rangeTo) && validityMatches(validOn, r.validFrom, r.validTo))
    .sort((a, b) => tierPriority(a) - tierPriority(b) || a.id - b.id)
  if (!hits.length) return null
  const best = hits[0]
  const ties = hits.filter((h) => tierPriority(h) === tierPriority(best)).length - 1
  return { row: best, ties }
}

/** Kleinpaket: newest active Tier 1 row flagged isDHLKleinPaket, country ignored (calculate.py). */
export const findKleinpaketRow = (tier1: TierRow[]): TierRow | null => {
  const hits = tier1.filter((r) => r.isActive && r.isKleinpaket).sort((a, b) => b.id - a.id)
  return hits[0] || null
}

const serviceMetaFromRates = (rates: CarrierRate[]): FixtureService[] => {
  const map = new Map<string, FixtureService>()
  for (const r of rates) {
    if (r.rateType !== 'base') continue
    const cur = map.get(r.serviceCode) || { code: r.serviceCode, name: r.serviceName || r.serviceCode, nameEn: r.serviceName || r.serviceCode, scope: r.countryId && r.countryCode !== 'DE' ? 'international' : 'national', pricing: 'tier', maxWeightKg: 0 } as FixtureService
    if (r.pricePerKg > 0) cur.pricing = 'perkg'
    if (r.countryId && r.countryCode !== 'DE') cur.scope = 'international'
    cur.maxWeightKg = Math.max(cur.maxWeightKg, r.rangeTo)
    map.set(r.serviceCode, cur)
  }
  return [...map.values()]
}

const loadServiceMeta = async (shipperIds: number[], rates: CarrierRate[]): Promise<{ services: FixtureService[]; countryZones: Record<string, string> }> => {
  const derived = serviceMetaFromRates(rates)
  try {
    const fixtures = await listFixtures()
    const fx = fixtures.filter((f) => shipperIds.includes(f.shipperId)).sort((a, b) => String(b.validFrom).localeCompare(String(a.validFrom)))[0]
    if (!fx) return { services: derived, countryZones: {} }
    const f = await loadFixture(fx.fixture)
    if (!f) return { services: derived, countryZones: {} }
    const byCode = new Map(f.services.map((s) => [s.code, s]))
    return { services: derived.map((d) => ({ ...d, ...(byCode.get(d.code) || {}) })), countryZones: f.countryZones || {} }
  } catch { return { services: derived, countryZones: {} } }
}

const loadMerchant = async (event: any, token: string, organizationId: number) => {
  if (!organizationId) return null
  const res: any = await fetchHelper(event, `models/ad_org/${n(organizationId)}`, 'GET', token, null)
  return { name: String(res?.Name || ''), orgId: n(organizationId), bpartnerId: res?.C_BPartner_ID?.id ? n(res.C_BPartner_ID.id) : null }
}

export const resolvePriceSheet = async (event: any, token: string, input: Partial<SheetOptions>): Promise<PriceSheet> => {
  const options: SheetOptions = defaultSheetOptions({ ...input, markupPct: { ...DEFAULT_MARKUP_PCT, ...(input?.markupPct || {}) } })
  options.columns = [...new Set((options.columns || []).map(Number).filter((w) => w > 0))].sort((a, b) => a - b)
  const validOn = String(options.validOn || new Date().toISOString()).slice(0, 10)
  const orgId = n(options.organizationId)

  const [countries, rates, tier1, tier2, merchant, shipperGroups] = await Promise.all([
    loadCountryMaps(event, token),
    loadCarrierRates(event, token, { shipperId: options.shipperId, validOn }),
    loadTier1(event, token),
    loadTier2(event, token, orgId),
    loadMerchant(event, token, orgId),
    loadShipperGroups(event, token)
  ])
  if (merchant && !options.bpartnerId) options.bpartnerId = merchant.bpartnerId
  // Carrier identity = M_Shipper.M_ShipperCfg_ID ('DHL' | 'DPD'): every shipper of the config shares one contract.
  const shipperGroup = shipperGroups.get(n(options.shipperId))
  const carrier = shipperGroup?.carrier || carrierCodeOf('', rates[0]?.shipper)
  const { services, countryZones } = await loadServiceMeta(shipperGroup?.shipperIds || [options.shipperId], rates)
  const serviceByCode = new Map(services.map((s) => [s.code, s]))
  const enabled = (code: string) => options.services?.[code] !== false
  const warnings: string[] = []
  const shipperName = carrier || rates[0]?.shipper || ''
  if (carrier !== 'DHL' && rates.length) warnings.push(`${carrier}: the Tier tables are carrier-agnostic — billing applies the same Tier 1 / Tier 2 rows (DHL-based) to ${carrier} shipments; all ${carrier} prices here are derived from the contract.`)

  // Tier rows of OTHER shippers are still billed (billing ignores the shipper) — keep all.
  const tierRows = [...tier2, ...tier1]
  for (const r of tierRows) if (isOneSidedValidity(r.validFrom, r.validTo)) warnings.push(`Tier ${r.table === 'override' ? '2' : '1'} row ${r.id} (${countries.names.get(r.countryId || 0) || 'no country'}) has only one validity bound and is never matched by billing.`)

  const inScope = (code: string): boolean => {
    if (options.countryScope === 'eu') return code === 'DE' || isEuCountryCode(code)
    if (options.countryScope === 'list') return (options.countryCodes || []).map((c) => c.toUpperCase()).includes(code.toUpperCase())
    return true
  }

  const kleinpaket = findKleinpaketRow(tier1)
  const haustuerByCountry = new Map<number, number>()
  for (const r of rates) if (r.rateType === 'surcharge' && r.countryId && /haust/i.test(r.description)) haustuerByCountry.set(r.countryId, r.basePrice)

  const sections: Record<SectionKey, SheetRow[]> = { national: [], eu: [], nonEu: [], warenpost: [], surcharges: [] }
  const tieWarned = new Set<string>()

  const sellFor = (serviceCode: string, countryId: number, w: number, carrierPrice: number | null, section: SectionKey): SheetCell => {
    const cell: SheetCell = { weight: w, price: null, source: 'none', carrierPrice }
    if (carrierPrice === null) return cell
    if (KLEINPAKET_SERVICES.includes(serviceCode)) {
      if (kleinpaket) { cell.price = round2(kleinpaket.basePrice); cell.source = 'tier1'; cell.tierRowId = kleinpaket.id; cell.tierTable = 'default'; return cell }
      cell.price = applyMarkup(carrierPrice, options.markupPct[section]); cell.source = 'derived'; return cell
    }
    if (TIER_BILLED_SERVICES.includes(serviceCode)) {
      const m2 = findTierMatch(tier2, countryId, w, validOn)
      const m = m2 || findTierMatch(tier1, countryId, w, validOn)
      if (m) {
        cell.price = tierPriceAt(m.row.basePrice, m.row.pricePerKg, w)
        cell.source = m.row.table === 'override' ? 'tier2' : 'tier1'
        cell.tierRowId = m.row.id
        cell.tierTable = m.row.table
        if (m.ties > 0) {
          const k = `${m.row.table}|${countryId}|${w}`
          if (!tieWarned.has(k)) { tieWarned.add(k); warnings.push(`${countries.names.get(countryId)} ${w} kg: ${m.ties + 1} Tier ${m.row.table === 'override' ? '2' : '1'} rows match equally — billing picks an arbitrary one (row ${m.row.id} shown).`) }
        }
        return cell
      }
    }
    cell.price = applyMarkup(carrierPrice, options.markupPct[section])
    cell.source = 'derived'
    return cell
  }

  // ---- rows ----
  const baseRates = rates.filter((r) => r.rateType === 'base' && enabled(r.serviceCode))
  const isZoneRow = (r: CarrierRate) => !r.countryId && !!r.zone
  const nationalCodes = new Set<string>()
  for (const r of baseRates) {
    if (isZoneRow(r)) continue
    const code = r.countryCode || (r.countryId ? countries.byId.get(r.countryId) || '' : '')
    if (!r.countryId || code === 'DE') nationalCodes.add(r.serviceCode)
  }
  /** Contract cost for weight w from a sorted list of rows of one service+destination (flat tiers or base+perkg). */
  const costAt = (rows: CarrierRate[], w: number): number | null => {
    const t = rows.find((x) => weightMatches(w, x.rangeFrom, x.rangeTo)) || rows.find((x) => w <= x.rangeTo + 1e-9)
    if (!t) return null
    return t.pricePerKg > 0 ? priceAt(t.basePrice, t.pricePerKg, w) : round2(t.basePrice)
  }
  const groupMax = (rows: CarrierRate[], svc?: FixtureService) => Math.max(...rows.map((t) => t.rangeTo), 0) || svc?.maxWeightKg || 31.5

  // National services: one row per service, cells from the service's tier rows.
  for (const code of nationalCodes) {
    const svc = serviceByCode.get(code)
    const tiers = baseRates.filter((r) => r.serviceCode === code && !isZoneRow(r) && (!r.countryId || r.countryCode === 'DE')).sort((a, b) => a.rangeTo - b.rangeTo)
    const maxWeight = groupMax(tiers, svc)
    const cells: SheetCell[] = options.columns.map((w) => {
      if (w > maxWeight + 1e-9) return { weight: w, price: null, source: 'none' as CellSource, carrierPrice: null }
      return sellFor(code, GERMANY_COUNTRY_ID, w, costAt(tiers, w), 'national')
    })
    const row: SheetRow = {
      key: `${code}|DE`, serviceCode: code, serviceName: svc?.name || tiers[0]?.serviceName || code, section: 'national',
      countryId: GERMANY_COUNTRY_ID, countryCode: 'DE', countryName: countries.names.get(GERMANY_COUNTRY_ID) || 'Germany', zone: '', isEu: true,
      pricing: tiers.some((t) => t.pricePerKg > 0) ? 'perkg' : 'tier', maxWeight, cells
    }
    if (KLEINPAKET_SERVICES.includes(code)) row.note = kleinpaket ? `Tier 1 Kleinpaket row ${kleinpaket.id} (global, all merchants)` : 'No Tier 1 Kleinpaket row — derived from contract'
    else if (!TIER_BILLED_SERVICES.includes(code)) row.note = 'Not billed through the Tier tables (derived from contract)'
    if (inScope('DE')) sections.national.push(row)
  }

  // International per-country services — grouped by service + country (DHL: one base+perkg row; DPD: flat weight tiers).
  const intlGroups = new Map<string, CarrierRate[]>()
  for (const r of baseRates) {
    if (isZoneRow(r) || !r.countryId || !r.countryCode || r.countryCode === 'DE') continue
    const k = `${r.serviceCode}|${r.countryCode}`
    if (!intlGroups.has(k)) intlGroups.set(k, [])
    intlGroups.get(k)!.push(r)
  }
  for (const [, group] of intlGroups) {
    group.sort((a, b) => a.rangeTo - b.rangeTo)
    const r = group[0]
    const code = r.countryCode
    if (!inScope(code)) continue
    const isEu = isEuCountryCode(code)
    const section = sectionOfService(r.serviceCode, code, isEu)
    if (!section || section === 'surcharges') continue
    const svc = serviceByCode.get(r.serviceCode)
    const maxWeight = groupMax(group, svc)
    const pricing: SheetRow['pricing'] = group.length === 1 && r.pricePerKg > 0 ? 'perkg' : 'tier'
    const haustuer = options.includeHaustuer && TIER_BILLED_SERVICES.includes(r.serviceCode) ? (haustuerByCountry.get(r.countryId!) || 0) : 0
    const cells = options.columns.map((w) => {
      if (w > maxWeight + 1e-9) return { weight: w, price: null, source: 'none' as CellSource, carrierPrice: null }
      const c = costAt(group, w)
      const cost = c === null ? null : round2(c + haustuer)
      const cell = sellFor(r.serviceCode, r.countryId!, w, cost, section)
      if (haustuer && cell.source === 'derived') cell.note = 'incl. Haustürzuschlag'
      return cell
    })
    const row: SheetRow = {
      key: `${r.serviceCode}|${code}`, serviceCode: r.serviceCode, serviceName: svc?.name || r.serviceName || r.serviceCode, section,
      countryId: r.countryId, countryCode: code, countryName: countries.names.get(r.countryId!) || r.country || code, zone: r.zone, isEu,
      pricing, maxWeight, cells
    }
    if (pricing === 'perkg') {
      // base / per-kg fields: a 0/0 Tier row for the country (primary services only) else contract × markup
      row.base = { value: applyMarkup(r.basePrice, options.markupPct[section]), source: 'derived', carrier: r.basePrice }
      row.perKg = { value: applyMarkup(r.pricePerKg, options.markupPct[section]), source: 'derived', carrier: r.pricePerKg }
      if (TIER_BILLED_SERVICES.includes(r.serviceCode)) {
        const open = [...tier2, ...tier1].filter((t) => t.isActive && !t.isKleinpaket && t.countryId === r.countryId && t.rangeFrom === 0 && t.rangeTo === 0 && validityMatches(validOn, t.validFrom, t.validTo)).sort((a, b) => (a.table === 'override' ? 0 : 1) - (b.table === 'override' ? 0 : 1) || a.id - b.id)[0]
        if (open) {
          const src: CellSource = open.table === 'override' ? 'tier2' : 'tier1'
          row.base = { value: open.basePrice, source: src, rowId: open.id, table: open.table, carrier: r.basePrice }
          row.perKg = { value: open.pricePerKg, source: src, rowId: open.id, table: open.table, carrier: r.pricePerKg }
        }
      }
    }
    if (haustuer) row.surcharges = [{ label: 'Haustürzuschlag', price: haustuer }]
    if (!TIER_BILLED_SERVICES.includes(r.serviceCode)) row.note = 'Not billed through the Tier tables (derived from contract)'
    sections[section].push(row)
  }

  // Zone-level services (no country, e.g. DPD Express zones): one row per service + zone, always derived.
  const zoneGroups = new Map<string, CarrierRate[]>()
  for (const r of baseRates) {
    if (!isZoneRow(r)) continue
    const k = `${r.serviceCode}|${r.zone}`
    if (!zoneGroups.has(k)) zoneGroups.set(k, [])
    zoneGroups.get(k)!.push(r)
  }
  const zoneMembers = (zone: string): string[] => Object.entries(countryZones).filter(([, z]) => z === zone).map(([iso]) => iso).sort()
  for (const [, group] of zoneGroups) {
    group.sort((a, b) => a.rangeTo - b.rangeTo)
    const r = group[0]
    const svc = serviceByCode.get(r.serviceCode)
    const members = zoneMembers(r.zone)
    if (options.countryScope !== 'all' && members.length && !members.some((m) => inScope(m))) continue
    const section: SectionKey = /^1-/.test(r.zone) ? 'eu' : 'nonEu'
    const maxWeight = groupMax(group, svc)
    const cells = options.columns.map((w) => {
      if (w > maxWeight + 1e-9) return { weight: w, price: null, source: 'none' as CellSource, carrierPrice: null }
      const c = costAt(group, w)
      const cell: SheetCell = { weight: w, price: c === null ? null : applyMarkup(c, options.markupPct[section]), source: c === null ? 'none' : 'derived', carrierPrice: c }
      return cell
    })
    const label = `Zone ${r.zone}` + (members.length ? ` (${members.join(', ')})` : '')
    sections[section].push({
      key: `${r.serviceCode}|zone:${r.zone}`, serviceCode: r.serviceCode, serviceName: svc?.name || r.serviceName || r.serviceCode, section,
      countryId: null, countryCode: '', countryName: label, zone: r.zone, isEu: section === 'eu', pricing: 'tier', maxWeight, cells,
      note: 'Zone price (derived from contract, not billed through the Tier tables)'
    })
  }
  // Tier-billed (primary) services first, then the rest; countries alphabetically within a service.
  const svcRank = (code: string) => (TIER_BILLED_SERVICES.includes(code) ? 0 : /PREMIUM/.test(code) ? 1 : 2)
  for (const k of ['eu', 'nonEu', 'warenpost'] as SectionKey[]) sections[k].sort((a, b) => svcRank(a.serviceCode) - svcRank(b.serviceCode) || a.serviceCode.localeCompare(b.serviceCode) || (a.zone && !a.countryId ? 1 : 0) - (b.zone && !b.countryId ? 1 : 0) || a.countryName.localeCompare(b.countryName, 'de'))

  // Surcharges / services
  const surcharges: SurchargeRow[] = []
  if (options.includeSurcharges) {
    for (const r of rates) {
      if (r.rateType === 'base' || !enabled(r.serviceCode)) continue
      // country-specific surcharges (Haustür SE/FI, Brexit GB, Währungszuschlag CH/LI …) carry their countryCode
      const svc = serviceByCode.get(r.serviceCode)
      surcharges.push({
        serviceCode: r.serviceCode, serviceName: svc?.name || r.serviceName || r.serviceCode, rateType: r.rateType, label: r.description || r.rateType,
        countryCode: r.countryCode || undefined, carrierPrice: r.basePrice, isPercentage: r.isPercentage, description: r.sourceRef,
        price: r.isPercentage ? r.basePrice : applyMarkup(r.basePrice, options.markupPct.surcharges)
      })
    }
    surcharges.sort((a, b) => a.serviceCode.localeCompare(b.serviceCode) || (a.rateType === b.rateType ? a.label.localeCompare(b.label, 'de') : a.rateType === 'surcharge' ? -1 : 1))
  }

  // Fallback warning: Tier-billed countries without any Tier row → billing charges €25 fallback.
  const derivedBilled = carrier !== 'DHL' ? [] : [...sections.eu, ...sections.nonEu].filter((row) => TIER_BILLED_SERVICES.includes(row.serviceCode) && row.cells.every((c) => c.source !== 'tier1' && c.source !== 'tier2'))
  if (derivedBilled.length) warnings.unshift(`${derivedBilled.length} countries of ${TIER_BILLED_SERVICES[1]} have no Tier price yet — shipments there are billed at the €25.00 fallback until a Tier row exists (use "persist row" / "apply markup to all derived").`)
  const nationalDerived = sections.national.filter((row) => row.serviceCode === 'DHL_PAKET' && row.cells.some((c) => c.source === 'derived'))
  if (nationalDerived.length) warnings.unshift('DHL Paket national has weight columns without a Tier row — those weights are billed at the €25.00 fallback.')

  return {
    options, generatedAt: new Date().toISOString(), merchant, shipperName,
    // ALL services of the carrier, not only the enabled ones: the options panel renders its checkboxes from this
    // list, so a disabled service must stay listed (unchecked) — otherwise it vanishes and cannot be re-enabled
    // (the disabled state is persisted in localStorage, so a reload did not bring it back either). Rows / PDF
    // consult options.services themselves.
    services,
    sections, surcharges, warnings, tierCounts: { tier1: tier1.length, tier2: tier2.length, carrier: rates.length }
  }
}

// ===== Write path =====

export interface TierUpsertInput {
  scope: 'tier1' | 'tier2'
  organizationId: number
  bpartnerId?: number | null
  shipperId: number
  countryId: number
  mode: 'tier' | 'perkg' | 'kleinpaket'
  weight?: number
  prevWeight?: number
  price?: number
  base?: number
  perKg?: number
  rowId?: number
  table?: 'default' | 'override'
  validFrom?: string
  validTo?: string
  deactivate?: boolean
}

const tierBody = (i: TierUpsertInput, orgId: number, bpartnerId: number | null, range: { from: number; to: number }, base: number, perKg: number, kleinpaket: boolean) => {
  const body: any = {
    AD_Org_ID: { id: orgId, tableName: 'AD_Org' },
    M_Shipper_ID: { id: n(i.shipperId), tableName: 'M_Shipper' },
    C_Country_ID: { id: n(i.countryId), tableName: 'C_Country' },
    isActive: true,
    ShippingBasePrice: round2(base),
    ShippingPricePerKG: round2(perKg),
    RangeWeightFrom: round2(range.from),
    RangeWeightTo: round2(range.to),
    isDHLKleinPaket: kleinpaket,
    tableName: i.scope === 'tier2' ? 'CUST_ShippingCost_BPOverride' : 'CUST_ShippingCost_Default'
  }
  if (i.scope === 'tier2' && bpartnerId) body.C_BPartner_ID = { id: bpartnerId, tableName: 'C_BPartner' }
  if (i.validFrom && i.validTo) { body.ValidFrom = String(i.validFrom).slice(0, 10); body.ValidTo = String(i.validTo).slice(0, 10) }
  return body
}

/** Create or update ONE Tier row for a cell edit. Returns the row id + table. */
export const upsertTierRow = async (event: any, token: string, i: TierUpsertInput): Promise<{ rowId: number; table: 'default' | 'override' }> => {
  if (!i.countryId && i.mode !== 'kleinpaket') throw createError({ statusCode: 400, statusMessage: 'countryId missing' })
  if (i.mode === 'kleinpaket') { i.scope = 'tier1'; i.countryId = i.countryId || GERMANY_COUNTRY_ID }
  if (i.scope === 'tier2' && !i.organizationId) throw createError({ statusCode: 400, statusMessage: 'Tier 2 needs a merchant organization' })
  if ((i.validFrom && !i.validTo) || (!i.validFrom && i.validTo)) throw createError({ statusCode: 400, statusMessage: 'Validity needs both From and To (one-sided rows are ignored by billing)' })
  const table: 'default' | 'override' = i.scope === 'tier2' ? 'override' : 'default'
  const model = table === 'override' ? TIER2_MODEL : TIER1_MODEL

  if (i.deactivate) {
    if (!i.rowId) throw createError({ statusCode: 400, statusMessage: 'rowId missing' })
    await fetchHelper(event, `${model}/${n(i.rowId)}`, 'PUT', token, { isActive: false, tableName: table === 'override' ? 'CUST_ShippingCost_BPOverride' : 'CUST_ShippingCost_Default' })
    return { rowId: n(i.rowId), table }
  }

  let bpartnerId = i.bpartnerId ? n(i.bpartnerId) : null
  if (i.scope === 'tier2' && !bpartnerId) {
    try { const org: any = await fetchHelper(event, `models/ad_org/${n(i.organizationId)}`, 'GET', token, null); bpartnerId = org?.C_BPartner_ID?.id ? n(org.C_BPartner_ID.id) : null } catch { /* optional */ }
  }
  const orgId = i.scope === 'tier2' ? n(i.organizationId) : 0

  let range = { from: 0, to: 0 }, base = 0, perKg = 0, kleinpaket = false
  if (i.mode === 'tier') {
    const w = n(i.weight)
    if (!(w > 0)) throw createError({ statusCode: 400, statusMessage: 'weight missing' })
    const prev = n(i.prevWeight)
    range = { from: prev > 0 ? round2(prev + TIER_RANGE_STEP) : 0, to: w }
    base = n(i.price); perKg = 0
  } else if (i.mode === 'perkg') {
    base = n(i.base); perKg = n(i.perKg)
  } else {
    range = { from: 0, to: n(i.weight) || 1 }
    base = n(i.price); perKg = 0; kleinpaket = true
  }

  // Reuse an existing row with the same identity (scope, country, range, kleinpaket) when no rowId was given.
  let rowId = n(i.rowId)
  if (!rowId) {
    const filter = table === 'override'
      ? `AD_Org_ID eq ${orgId} and C_Country_ID eq ${n(i.countryId)} and IsActive eq true`
      : `C_Country_ID eq ${n(i.countryId)} and IsActive eq true`
    const res: any = await fetchHelper(event, `${model}?$filter=${encodeURIComponent(filter)}&$top=200`, 'GET', token, null)
    const rows = (res?.records || []).map((r: any) => mapTierRow(r, table))
    const hit = rows.find((r: TierRow) => r.isKleinpaket === kleinpaket && round2(r.rangeFrom) === round2(range.from) && round2(r.rangeTo) === round2(range.to) && (r.validFrom || '') === String(i.validFrom || '').slice(0, 10) && (table === 'default' || (r.bpartnerId || null) === (bpartnerId || null)))
    if (hit) rowId = hit.id
  }
  const body = tierBody(i, orgId, bpartnerId, range, base, perKg, kleinpaket)
  const res: any = rowId
    ? await fetchHelper(event, `${model}/${rowId}`, 'PUT', token, body)
    : await fetchHelper(event, model, 'POST', token, body)
  return { rowId: n(res?.id) || rowId, table }
}