/**
 * Shared types + pure helpers for carrier contract rates (CUST_CarrierRate), the
 * DHL fixture and the shipping price sheet. Imported by RELATIVE path from the
 * server (routes/utils) and the client (pages/components) — no Nuxt auto-imports.
 *
 * Three tables:
 *   CUST_CarrierRate              contract / purchase prices per carrier service (cost)
 *   CUST_ShippingCost_Default     Tier 1 sell prices (global), billed by calculate.py
 *   CUST_ShippingCost_BPOverride  Tier 2 sell prices (per merchant org), billed first
 * The price sheet reads all three; its edits write Tier 1 / Tier 2 rows only.
 */

export type RateType = 'base' | 'surcharge' | 'service'
export type ServicePricing = 'tier' | 'perkg'
export type ServiceScope = 'national' | 'international'
export type SectionKey = 'national' | 'eu' | 'nonEu' | 'warenpost' | 'surcharges'
export type CellSource = 'tier2' | 'tier1' | 'derived' | 'none'

export const DHL_SHIPPER_ID = 1000000
export const DPD_SHIPPER_ID = 1000003
/** Carrier = M_Shipper.M_ShipperCfg_ID identifier ('DHL', 'DPD'); every shipper of a config shares the contract. */
export type CarrierCode = 'DHL' | 'DPD' | string
/** Logo of a carrier (public asset) — '' when unknown; used by the page, the matrix and (as PNG) the PDF. */
export const CARRIER_LOGO_PATHS: Record<string, { web: string; png: string }> = {
  DHL: { web: '/assets/shipper/dhl_logo_small.svg', png: 'dhl_logo_small.png' },
  DPD: { web: '/assets/shipper/dpd_logo_small.png', png: 'dpd_logo_small.png' }
}
export const carrierLogoPath = (name: string | null | undefined): string => CARRIER_LOGO_PATHS[carrierCodeOf(name, name || '')]?.web || ''

/** Countries pinned at the top of the price-sheet country picker (quick picks, always visible). */
export const PINNED_COUNTRY_CODES = ['DE', 'AT', 'FR', 'NL', 'CH']

/** Rows of a merged sheet grouped per carrier (in `sheet.carriers` order; single-carrier sheets → one group). */
export const groupRowsByCarrier = <T extends { shipperId?: number; shipperName?: string }>(rows: T[], carriers: { shipperId: number; shipperName: string }[] | undefined): { shipperId: number; shipperName: string; rows: T[] }[] => {
  const order = (carriers || []).map((c) => Number(c.shipperId))
  const map = new Map<number, { shipperId: number; shipperName: string; rows: T[] }>()
  for (const r of rows) {
    const id = Number(r.shipperId || 0)
    if (!map.has(id)) map.set(id, { shipperId: id, shipperName: String(r.shipperName || (carriers || []).find((c) => Number(c.shipperId) === id)?.shipperName || ''), rows: [] })
    map.get(id)!.rows.push(r)
  }
  return [...map.values()].sort((a, b) => { const ia = order.indexOf(a.shipperId), ib = order.indexOf(b.shipperId); return (ia < 0 ? 9999 : ia) - (ib < 0 ? 9999 : ib) })
}

export const carrierCodeOf = (cfgName: string | null | undefined, shipperName = ''): CarrierCode => {
  const c = String(cfgName || '').trim().toUpperCase()
  if (c) return c
  const n = String(shipperName || '').toUpperCase()
  return n.startsWith('DPD') ? 'DPD' : n.startsWith('DHL') ? 'DHL' : n || 'OTHER'
}
export const GERMANY_COUNTRY_ID = 101

/** Weight columns of the customer price sheet (kg). */
export const DEFAULT_TIER_COLUMNS: number[] = [1, 2, 3, 5, 10, 15, 20, 31.5]

/** Tier 1/2 convention on prod: the next flat tier starts 0.01 kg above the previous bound (2.01–5). */
export const TIER_RANGE_STEP = 0.01

/** No margin by default — derived prices equal the contract cost until a markup is entered. */
export const DEFAULT_MARKUP_PCT: Record<SectionKey, number> = {
  national: 0, eu: 0, nonEu: 0, warenpost: 0, surcharges: 0
}

// ===== Fixture (parsed contract) =====

export interface FixtureService {
  code: string
  name: string
  nameEn: string
  scope: ServiceScope
  pricing: ServicePricing
  maxWeightKg: number
  billingNo?: string
  deliveryDays?: { de: string; en: string }
  note?: { de: string; en: string }
  /** set on merged multi-carrier sheets: the carrier this service belongs to */
  shipperId?: number
  shipperName?: string
}

export interface FixtureRate {
  serviceCode: string
  rateType: RateType
  /** ISO2 (C_Country.CountryCode); null = national / all countries of the service */
  countryCode?: string | null
  countryNameDe?: string
  zone?: string
  rangeFrom?: number
  rangeTo?: number
  basePrice: number
  pricePerKg?: number
  isPercentage?: boolean
  description?: string
  sourceRef: string
}

export interface CarrierRateFixture {
  fixture: string
  shipperId: number
  shipperName: string
  contractNo: string
  validFrom: string
  generatedAt: string
  source: string[]
  services: FixtureService[]
  rates: FixtureRate[]
  unresolvedCountries: string[]
  /** optional: ISO2 → carrier zone label (for zone-priced services such as DPD Express) */
  countryZones?: Record<string, string>
}

// ===== CUST_CarrierRate row (mapped) =====

export interface CarrierRate {
  id: number
  uid?: string
  shipperId: number
  shipper: string
  serviceCode: string
  serviceName: string
  rateType: RateType
  countryId: number | null
  country: string
  countryCode: string
  zone: string
  rangeFrom: number
  rangeTo: number
  basePrice: number
  pricePerKg: number
  isPercentage: boolean
  contractNo: string
  validFrom: string
  validTo: string
  description: string
  sourceRef: string
  isActive: boolean
}

/** Identity of a contract row for the import diff. */
export const rateKey = (r: { shipperId: number; serviceCode: string; countryId?: number | null; countryCode?: string | null; rateType: string; rangeFrom?: number; validFrom?: string; description?: string; zone?: string }) =>
  [
    r.shipperId,
    r.serviceCode,
    r.countryId ?? (r.countryCode || 0),
    r.rateType,
    Number(r.rangeFrom ?? 0).toFixed(3),
    String(r.validFrom || '').slice(0, 10),
    r.rateType === 'base' ? '' : String(r.description || '').toLowerCase(),
    // zone-level rows (no country, e.g. DPD Express zones) differ only by zone
    r.countryId || r.countryCode ? '' : String(r.zone || '')
  ].join('|')

// ===== Tier rows (CUST_ShippingCost_Default / _BPOverride, mapped) =====

export interface TierRow {
  id: number
  table: 'default' | 'override'
  organizationId: number
  bpartnerId: number | null
  shipperId: number | null
  countryId: number | null
  basePrice: number
  pricePerKg: number
  rangeFrom: number
  rangeTo: number
  validFrom: string
  validTo: string
  isKleinpaket: boolean
  isActive: boolean
}

// ===== Price sheet =====

export interface SheetOptions {
  /** 0 = standard sheet (Tier 1 only) */
  organizationId: number
  bpartnerId?: number | null
  /** primary carrier (= shipperIds[0]); single-carrier routes (cell / row-persist) key on it */
  shipperId: number
  /** every selected carrier (config-group primary ids); > 1 → the resolve route merges one sheet per carrier */
  shipperIds?: number[]
  language: 'de' | 'en'
  columns: number[]
  markupPct: Record<SectionKey, number>
  countryScope: 'all' | 'eu' | 'list'
  countryCodes?: string[]
  /** service code → enabled */
  services: Record<string, boolean>
  validOn: string
  includeSurcharges: boolean
  includeHaustuer: boolean
  notes: string
  title?: string
  customerName?: string
}

export interface SheetCell {
  weight: number
  price: number | null
  source: CellSource
  tierRowId?: number
  tierTable?: 'default' | 'override'
  carrierPrice?: number | null
  note?: string
}

export interface SheetPriceField {
  value: number | null
  source: CellSource
  rowId?: number
  table?: 'default' | 'override'
  carrier?: number | null
}

export interface SheetRow {
  key: string
  serviceCode: string
  serviceName: string
  section: SectionKey
  countryId: number | null
  countryCode: string
  countryName: string
  zone: string
  isEu: boolean
  pricing: ServicePricing
  maxWeight: number
  /** perkg rows: the effective base / per-kg pair (Tier row or contract) */
  base?: SheetPriceField
  perKg?: SheetPriceField
  cells: SheetCell[]
  surcharges?: { label: string; price: number }[]
  /** Kleinpaket etc.: a note shown under the row */
  note?: string
  /** carrier of this row (set on merged multi-carrier sheets; Tier writes must use it, not options.shipperId) */
  shipperId?: number
  shipperName?: string
}

export interface SurchargeRow {
  serviceCode: string
  serviceName: string
  rateType: RateType
  label: string
  countryCode?: string
  carrierPrice: number
  price: number
  isPercentage: boolean
  description?: string
  shipperId?: number
  shipperName?: string
}

export interface PriceSheet {
  options: SheetOptions
  generatedAt: string
  merchant: { name: string; orgId: number; bpartnerId: number | null } | null
  /** display name — "DHL" or, merged, "DHL + DPD" */
  shipperName: string
  /** > 1 entry = merged multi-carrier sheet (rows / services / surcharges carry shipperId + shipperName) */
  carriers?: { shipperId: number; shipperName: string }[]
  services: FixtureService[]
  sections: Record<SectionKey, SheetRow[]>
  surcharges: SurchargeRow[]
  warnings: string[]
  tierCounts: { tier1: number; tier2: number; carrier: number }
}

export const round2 = (n: number): number => Math.round((Number(n) + Number.EPSILON) * 100) / 100

/** DHL bills weight-based prices rounded UP to 100 g: base + ceil(w, 0.1) × perKg. */
export const priceAt = (base: number, perKg: number, weightKg: number): number => {
  const w = Math.ceil(Number(weightKg) * 10 - 1e-9) / 10
  return round2(Number(base || 0) + w * Number(perKg || 0))
}

/** Billing's tier formula (full weight × per-kg, no rounding of the weight). */
export const tierPriceAt = (base: number, perKg: number, weightKg: number): number =>
  round2(Number(base || 0) + Number(weightKg) * Number(perKg || 0))

export const applyMarkup = (price: number, pct: number): number => round2(Number(price) * (1 + Number(pct || 0) / 100))

/** Billing weight match: 0/0 = any weight, else inclusive range. */
export const weightMatches = (w: number, from: number, to: number): boolean => {
  const f = Number(from || 0), t = Number(to || 0)
  if (f === 0 && t === 0) return true
  return w >= f && w <= t
}

/** Billing date match: only when BOTH bounds are set (or both empty). One-sided rows never match. */
export const validityMatches = (dayIso: string, validFrom: string, validTo: string): boolean => {
  const vf = String(validFrom || '').slice(0, 10), vt = String(validTo || '').slice(0, 10)
  if (!vf && !vt) return true
  if (!vf || !vt) return false
  const d = String(dayIso || '').slice(0, 10)
  return d >= vf && d <= vt
}

export const isOneSidedValidity = (validFrom: string, validTo: string): boolean => {
  const vf = String(validFrom || '').slice(0, 10), vt = String(validTo || '').slice(0, 10)
  return (!!vf) !== (!!vt)
}

const safeFilePart = (s: string) => String(s || '').normalize('NFKD').replace(/[^\w\- ]+/g, '').trim().replace(/\s+/g, '_').slice(0, 60)

/** Never `Angebot_` — the contract package picks those files up as Anlage 1. */
export const priceSheetFilename = (language: 'de' | 'en', merchantName: string, dateIso: string): string => {
  const who = safeFilePart(merchantName) || (language === 'en' ? 'Standard' : 'Standard')
  const date = String(dateIso || new Date().toISOString()).slice(0, 10)
  return language === 'en' ? `Shipping_Price_Sheet_${who}_${date}.pdf` : `Preisliste_Versand_${who}_${date}.pdf`
}

export const defaultSheetOptions = (over: Partial<SheetOptions> = {}): SheetOptions => ({
  organizationId: 0,
  bpartnerId: null,
  shipperId: DHL_SHIPPER_ID,
  language: 'de',
  columns: [...DEFAULT_TIER_COLUMNS],
  markupPct: { ...DEFAULT_MARKUP_PCT },
  countryScope: 'list',
  countryCodes: ['DE'],
  services: {},
  validOn: new Date().toISOString().slice(0, 10),
  includeSurcharges: true,
  includeHaustuer: true,
  notes: '',
  ...over
})

export const SECTION_ORDER: SectionKey[] = ['national', 'eu', 'nonEu', 'warenpost', 'surcharges']

/** Selected carrier ids of an options object (always ≥ 1 entry, primary first, de-duplicated). */
export const sheetShipperIds = (o: Partial<SheetOptions> | null | undefined): number[] => {
  const ids = [...(Array.isArray(o?.shipperIds) ? o!.shipperIds! : []), Number(o?.shipperId)].map(Number).filter((n) => Number.isFinite(n) && n > 0)
  const unique = [...new Set(ids)]
  return unique.length ? unique : [DHL_SHIPPER_ID]
}

/**
 * One sheet per carrier → one merged sheet (multi-carrier export). Rows, surcharges and services are tagged with
 * their carrier, row keys are prefixed with the shipper id (unique across carriers), sections keep each carrier's
 * own order (carrier A's rows, then carrier B's — the PDF / matrix group by service anyway), counts are summed.
 */
export const mergePriceSheets = (sheets: PriceSheet[]): PriceSheet => {
  if (sheets.length === 1) return sheets[0]
  const first = sheets[0]
  const carriers = sheets.map((s) => ({ shipperId: Number(s.options?.shipperId), shipperName: String(s.shipperName || '') }))
  const sections = {} as Record<SectionKey, SheetRow[]>
  for (const k of SECTION_ORDER) sections[k] = []
  const services: FixtureService[] = []
  const seen = new Set<string>()
  const surcharges: SurchargeRow[] = []
  const warnings: string[] = []
  const tierCounts = { tier1: 0, tier2: 0, carrier: 0 }
  for (const s of sheets) {
    const shipperId = Number(s.options?.shipperId)
    const shipperName = String(s.shipperName || '')
    for (const k of SECTION_ORDER) {
      for (const r of s.sections?.[k] || []) sections[k].push({ ...r, key: `${shipperId}:${r.key}`, shipperId, shipperName })
    }
    for (const svc of s.services || []) {
      if (seen.has(svc.code)) continue
      seen.add(svc.code)
      services.push({ ...svc, shipperId, shipperName })
    }
    for (const sr of s.surcharges || []) surcharges.push({ ...sr, shipperId, shipperName })
    for (const w of s.warnings || []) warnings.push(`${shipperName}: ${w}`)
    tierCounts.tier1 += Number(s.tierCounts?.tier1) || 0
    tierCounts.tier2 += Number(s.tierCounts?.tier2) || 0
    tierCounts.carrier += Number(s.tierCounts?.carrier) || 0
  }
  return {
    ...first,
    options: { ...first.options, shipperIds: carriers.map((c) => c.shipperId) },
    shipperName: carriers.map((c) => c.shipperName).filter(Boolean).join(' + '),
    carriers,
    services,
    sections,
    surcharges,
    warnings,
    tierCounts
  }
}

/** Which section a contract service belongs to. */
export const sectionOfService = (serviceCode: string, countryCode: string | null, isEu: boolean): SectionKey | null => {
  const c = String(serviceCode || '').toUpperCase()
  if (c.includes('WARENPOST')) return 'warenpost'
  if (c.endsWith('_GENERAL')) return 'surcharges'
  if (!countryCode || countryCode === 'DE') return 'national'
  return isEu ? 'eu' : 'nonEu'
}