// LOGISTICS&CUSTOM sheet row parser + Reconciliation (KAR-903 / P2.4):
// standalone parser for the QAF "LOGISTICS&CUSTOM" detail sheet (Leitfaden
// 02-leitfaden-teil2.md [39]-[41]). Built 1:1 from the material-parser.ts /
// sbm-parser.ts / rmr-parser.ts pattern (KAR-897/P1.6, KAR-898/P1.7,
// KAR-902/P2.3): dynamic import of the canonical registry, label-anchor +
// confidence matching, a controlled degradation path on an unusable header
// (never throws), sourceCells/normalized/rawText provenance from day one, and
// the coreFieldsFound tri-state + logisticsRowsForReconciliation/
// FromPersistedMeta helper pair built in from the START — the #274/KAR-898
// lesson rmr-parser.ts's own module header already documents (sbm-parser.ts
// RETROFITTED this contract after an adversarial-review finding; this module
// ships it from commit one, exactly like rmr-parser.ts did).
//
// ── Standort-Struktur-Entscheidung: FLAT ROWS, not RMR's multi-block (task
// instruction: "prüfe im Leitfaden-Extrakt, ob das Block- oder
// Spalten-Struktur ist") ─────────────────────────────────────────────────
// The Leitfaden is explicit about the sheet's actual layout (Abbildung 25,
// [41]): ONE table, ONE header row, 15 numbered rows ("Positionsnummer" 1-15),
// where "Anlieferstandort" is simply ONE COLUMN VALUE per row — exactly the
// same flat row/column shape MATERIAL and SBM already use (material-parser.ts/
// sbm-parser.ts), NOT RMR's genuinely different "two stacked header+data
// blocks on one tab" layout (rmr-parser.ts's own module header, "Rohstoff-
// preisanteile werden in 2 Bloecken dargestellt"). There is no textual or
// visual evidence anywhere in [39]-[41] of a second header row, a second
// title block, or per-Anlieferstandort sub-tables — every delivery site the
// Leitfaden's worked description names ("Spartanburg" etc., illustrative only,
// no real numbers given) is just one more row value like any other.
// Reusing RMR's multi-block scan loop here would therefore be a premature
// abstraction over a structure this sheet does not actually have (task
// instruction: "keine premature Abstraktion wenn die Strukturen zu verschieden
// sind: dann dokumentiert eigenständig") — this module intentionally mirrors
// material-parser.ts's SINGLE header/single contiguous data block shape
// instead, plus the day-1 tri-state contract RMR/SBM converged on. If a real
// file ever demonstrates a genuine second block on this tab, that would be a
// new, separately-evidenced follow-up (same "not solved here, flagged for a
// future PR" posture RMR's own module header takes for its own residual
// layout risk) — not retrofitted speculatively now.
//
// ── Scope discipline (backlog 05-backlog-phasenplan.md [P2.4] + explicit task
// override, see below) ──────────────────────────────────────────────────────
//   - Parses + persists the row shape, PLUS the one row-level formula check
//     the Leitfaden's own "Formeln/Berechnungslogik" section documents with
//     every input field this parser actually captures: Logistikkosten je
//     Anlieferstandort = Transportkosten + Verpackungskosten +
//     Vorverpackungskosten (je Bauteil) — see validateLogisticsCostFormula
//     below.
//   - The Leitfaden's SECOND documented formula ("Zollkosten pro Bauteil je
//     Anlieferstandort = Zollwert x Zollsatz, ggf. + CBAM-Kosten", [40]) is
//     explicitly NOT recomputed here: "Zollwert" and "Zollsatz" are never
//     their own documented columns in the [40] field table (only "Zollkosten
//     pro Bauteil je Anlieferstandort" and "Zollkosten je Anlieferstandort"
//     are, both already themselves calculated results) — there is no
//     independently-parseable input pair to multiply. Recomputing it would
//     require guessing at an unparsed Zollwert/Zollsatz split the Leitfaden
//     never tabulates as its own field, which the task instruction explicitly
//     forbids ("Automatik-Logik NICHT nachbauen — nur erfassen, dokumentiert").
//     customsCostPerPart/customsCostPerDeliverySite are captured as plain data
//     fields only, exactly like rmr-parser.ts's bmwParticipationRate/threshold
//     are captured without being evaluated.
//   - The [39]-vs-[40] prose/formula tension around a possible Volumen factor
//     is a documented, NOT silently resolved, source discrepancy — see
//     "Volumen-Diskrepanz" note below.
//   - VERP/LVVV surcharge-key AUTOMATION LOGIC (what triggers the auto-fill)
//     is explicitly NOT rebuilt — surchargeKeyPackaging/surchargeKeyPrePackaging
//     are captured as plain "automatic/protected" text fields (matching their
//     canonical-fields.ts classification: "protected"), never independently
//     derived or validated against the packaging-cost columns, per task
//     instruction ("VERP/LVVV-Zuschlagsschlüssel als Felder erfassen —
//     Automatik-Logik NICHT nachbauen — nur erfassen, dokumentiert").
//   - The Incoterm rechtslogik (what FCA/DAP/DDP actually mean contractually)
//     is explicitly OUT of scope per the backlog item itself ("NICHT rein: die
//     vollstaendige Incoterm-Rechtslogik ... wird als Kontext-Information
//     mitgefuehrt, nicht als eigene Validierungsregel geprueft") — this module
//     implements only a DOMAIN-LIST membership check (validateLogisticsIncoterm
//     Domain below), never a legal/business consequence of the chosen Incoterm.
//   - Cross-Sheet-Reconciliation against SUMMARY's customsSupplierToBMW/
//     transportSupplierToBMW metrics IS explicitly IN scope for this PR (the
//     backlog's own P2.2-Kaskade-Anschluss note, task instruction) — see
//     reconciliation.ts's logistics_transport_detail_sum/logistics_customs_
//     detail_sum checks, wired from actions.ts/compare.ts, NOT built in this
//     file (this module only exposes logisticsRowsForReconciliation, the same
//     "produces the tri-state value, does not itself sum against a Summary
//     metric" boundary material-parser.ts/sbm-parser.ts/rmr-parser.ts all
//     keep). Post-merge adversarial-review fix (confidence 82): which
//     LogisticsRow field the transport check sums (transportCostPerPart,
//     NOT the combined logisticsCostPerDeliverySite) is a deliberate,
//     evidence-limited choice — see reconciliation.ts's own module header
//     "Evidenzlage + Korrektur" for the full derivation and the empirical
//     check against real BMW Summary-QAFs (none of the 9 available files // allow-customer-string
//     carry a LOGISTICS&CUSTOM sheet, so the combined-vs-transport-only
//     question could not be settled from real data).
//   - Cross-file matching/diffing of LOGISTICS rows (ALT vs NEU) is explicitly
//     OUT of scope for this PR — same boundary every prior sheet parser in
//     this family documents for its own row domain.
//
// ── Volumen-Diskrepanz (Leitfaden [39] Prosa vs. [40] Formel — dokumentiert,
// nicht stillschweigend aufgeloest, Master-Prompt §2 "never silently guess") ─
// [39]'s free-text description says: "Logistikkosten je Anlieferstandort:
// Gesamtkosten automatisch aus Volumen x (Transportkosten + Zusatzverpackung +
// Einwegverpackung) berechnet" — implying a Gesamtvolumen multiplication.
// [40]'s own "Formeln/Berechnungslogik" section, however, states plainly:
// "Logistikkosten je Anlieferstandort = Transportkosten + Verpackungskosten +
// Vorverpackungskosten (je Bauteil)" — no volume factor at all, and the
// backlog item [P2.4] itself quotes EXACTLY this second (no-volume) formula
// as the one to validate ("Formel-Validierung 'Logistikkosten je
// Anlieferstandort = Transport+Verpackung+Vorverpackung' ... als
// Business-Rule"). validateLogisticsCostFormula below implements the
// backlog's own literal formula (no Gesamtvolumen multiplication) — the
// authoritative "Formeln/Berechnungslogik" table entry, not the looser prose
// paragraph one page earlier. This is a deliberate, documented choice: if a
// real file later demonstrates the sheet is genuinely volume-scaled, that is
// new evidence for a follow-up, not a reason to silently pick one reading now.

import type { Worksheet } from 'exceljs'
import type { CanonicalField } from './canonical-fields.types'
import { worksheetToGrid } from './workbook-adapter'
import { isNotApplicableValue } from './normalizer'
import { matchesModuleSheetName } from './module-sheet-names'
import type { ComparisonSide } from './rule-engine'
import type { PlausibilityIssue, PlausibilitySeverity } from './plausibility'
import type { FacetDegradation } from './types'
import { MIN_SIGNAL_MAPPED_COLUMNS } from './types'
import { LOG_FIELD_KEY_TO_CANONICAL } from './canonical-fields'
import { byCanonicalId } from './canonical-model'
import { missingFieldReason, joinMissingReasons } from './bilingual-message'

/** EN label for a LOGISTICS field key, sourced from the canonical field
 * registry (KAR-906/P3.2). */
function logisticsLabelEn(key: LogisticsFieldKey, fallbackDe: string): string {
  return byCanonicalId(LOG_FIELD_KEY_TO_CANONICAL[key])?.labelEn ?? fallbackDe
}

// ── Row shape ────────────────────────────────────────────────────────────

/**
 * The 14 LOGISTICS&CUSTOM fields the Leitfaden documents completely (S.40,
 * canonical-fields.ts LOGISTICS_FIELDS) — camelCase mirror of the canonical
 * ids' `log_*` suffix, same relationship as rmr-parser.ts's RmrFieldKey to
 * RMR_FIELDS.
 */
export interface LogisticsRowValues {
  positionNumber: string
  deliverySite: string
  totalVolume: number | null
  quotationCurrency: string
  transportCostPerPart: number | null
  deliveryTerms: string
  packagingCostPerPart: number | null
  surchargeKeyPackaging: string
  prePackagingCostPerPart: number | null
  surchargeKeyPrePackaging: string
  logisticsCostPerDeliverySite: number | null
  customsCostPerPart: number | null
  customsTariffNumber: string
  customsCostPerDeliverySite: number | null
}

export type LogisticsFieldKey = keyof LogisticsRowValues

/**
 * A parsed LOGISTICS&CUSTOM row. sourceCells/normalized/rawText are present
 * from day one (same "kein Nachruesten" instruction material-parser.ts
 * follows, and the explicit KAR-903 task instruction "coreFieldsFound-
 * Tri-State + ... ab Tag 1") — every row carries per-field cell provenance
 * for whichever columns were actually located in the header.
 */
export type LogisticsRow = LogisticsRowValues & {
  sourceCells: Partial<Record<LogisticsFieldKey, string>>
  normalized: Partial<Record<LogisticsFieldKey, string | number | null>>
  rawText: Partial<Record<LogisticsFieldKey, string>>
}

const TEXT_FIELDS: Set<LogisticsFieldKey> = new Set([
  'positionNumber',
  'deliverySite',
  'quotationCurrency',
  'deliveryTerms',
  'surchargeKeyPackaging',
  'surchargeKeyPrePackaging',
  'customsTariffNumber',
])

/** Every LogisticsFieldKey — used to build the default-filled row shape. */
const ALL_FIELD_KEYS: readonly LogisticsFieldKey[] = [
  'positionNumber',
  'deliverySite',
  'totalVolume',
  'quotationCurrency',
  'transportCostPerPart',
  'deliveryTerms',
  'packagingCostPerPart',
  'surchargeKeyPackaging',
  'prePackagingCostPerPart',
  'surchargeKeyPrePackaging',
  'logisticsCostPerDeliverySite',
  'customsCostPerPart',
  'customsTariffNumber',
  'customsCostPerDeliverySite',
]

/**
 * The 3 header-level fields that MUST be located for a LOGISTICS header row
 * to be usable at all — Positionsnummer (row identity), Anlieferstandort (the
 * entity identity the whole sheet is organized by — task instruction: "das
 * Sheet gliedert je ANLIEFERSTANDORT") and Logistikkosten je Anlieferstandort
 * (the sheet's own defining aggregate result field, the one
 * reconciliation.ts's logistics_transport_detail_sum needs to sum). Mirrors
 * material-parser.ts's CORE_MATERIAL_FIELD_KEYS / sbm-parser.ts's
 * CORE_SBM_FIELD_KEYS / rmr-parser.ts's CORE_RMR_FIELD_KEYS 3-field pattern
 * (row-identity + entity-name + the sheet's defining value field).
 *
 * Deliberately does NOT throw when missing (unlike qaf-parser.ts's
 * parseQAFTemplate): LOGISTICS&CUSTOM is an OPTIONAL, additive sheet — a
 * malformed LOGISTICS sheet must not fail the whole file ingest. See
 * parseLogisticsWorksheet below: below this minimum, coreFieldsFound is false
 * and rows stays empty.
 */
export const CORE_LOGISTICS_FIELD_KEYS: readonly LogisticsFieldKey[] = [
  'positionNumber',
  'deliverySite',
  'logisticsCostPerDeliverySite',
]

// KAR-958/P2 (gate-audit.md B8, coreFieldsFound-Resilienz) — the shared
// MIN_SIGNAL_MAPPED_COLUMNS floor from types.ts (PR #325 review fix #5, was
// a duplicated local literal here); below it, a degraded LOGISTICS header is
// still treated as a genuinely empty/foreign sheet.

const HEADER_MATCH_MIN = 3
const HEADER_SCAN_MAX_ROWS = 20

// ── Sheet detection ─────────────────────────────────────────────────────────

/**
 * True when a worksheet name identifies the LOGISTICS&CUSTOM sheet. The
 * Leitfaden's own tab title is "LOGISTICS&CUSTOM" ([39]/[41]) — matched on
 * the substring "logistics" (case-insensitive) so a real file's exact
 * ampersand/spacing rendering never matters, same tolerant substring-match
 * approach isMaterialSheetName/isRmrSheetName use for their own sheets. No
 * other documented QAF module name contains "logistics". Sheet-name alias
 * source centralized in module-sheet-names.ts (KAR-905/P3.1).
 */
export function isLogisticsSheetName(name: string): boolean {
  return matchesModuleSheetName(name, 'LOGISTICS')
}

/** First worksheet whose name matches isLogisticsSheetName, or null when the
 * workbook has none (the additive gate — files without a LOGISTICS sheet are
 * simply not parsed by this module at all). */
export function findLogisticsWorksheet(wb: { worksheets: Worksheet[] }): Worksheet | null {
  return wb.worksheets.find((w) => isLogisticsSheetName(w.name)) ?? null
}

// ── Canonical registry bridge (dynamic import — see module header) ─────────
//
// Client-bundle discipline (KAR-893 lesson, "Bundle-Lehre aus #272"): exported
// through the qaf-differences barrel (index.ts), which several 'use client'
// components already import from — canonical-model.ts/canonical-fields.ts
// must stay OUT of the static import graph, exactly like material-parser.ts's
// loadMaterialRegistry(). loadLogisticsRegistry() is the same pattern: a
// dynamic import, cached after the first call. Only TYPE imports (erased at
// compile time) are static above.

interface ColumnMatch {
  key: LogisticsFieldKey
  /** 1.0 exact, 0.9 normalized-only — see material-parser.ts module header
   * for the two-tier rationale, reused verbatim here. */
  confidence: number
}

interface LogisticsRegistryCtx {
  /** LOGISTICS-only slice of the canonical registry — scoping is required,
   * not cosmetic: "Positionsnummer" is shared verbatim with several other
   * modules — matching against the full registry would make every LOGISTICS
   * header ambiguous, same reasoning as material-parser.ts/sbm-parser.ts/
   * rmr-parser.ts. */
  registry: readonly CanonicalField[]
  idToKey: Record<string, LogisticsFieldKey>
  findByAliasFn: (
    label: string,
    lang: 'de' | 'en' | undefined,
    registry: readonly CanonicalField[],
  ) => CanonicalField[]
}

let logisticsRegistryPromise: Promise<LogisticsRegistryCtx> | null = null

async function loadLogisticsRegistry(): Promise<LogisticsRegistryCtx> {
  if (!logisticsRegistryPromise) {
    logisticsRegistryPromise = (async () => {
      const [{ byModule, findByAlias }, { LOG_FIELD_KEY_TO_CANONICAL }] = await Promise.all([
        import('./canonical-model'),
        import('./canonical-fields'),
      ])
      const registry = byModule('LOGISTICS')
      const idToKey = Object.fromEntries(
        Object.entries(LOG_FIELD_KEY_TO_CANONICAL).map(([key, canonicalId]) => [canonicalId, key as LogisticsFieldKey]),
      ) as Record<string, LogisticsFieldKey>

      return { registry, idToKey, findByAliasFn: findByAlias }
    })()
  }
  return logisticsRegistryPromise
}

function matchHeaderColumnSync(headerCell: string, ctx: LogisticsRegistryCtx): ColumnMatch | null {
  if (headerCell === '') return null

  for (const field of ctx.registry) {
    if (headerCell === field.labelDe || headerCell === field.labelEn) {
      const key = ctx.idToKey[field.id]
      if (key) return { key, confidence: 1 }
    }
  }

  const hits = ctx.findByAliasFn(headerCell, undefined, ctx.registry)
  const distinctKeys = new Set(
    hits.map((f) => ctx.idToKey[f.id]).filter((k): k is LogisticsFieldKey => k !== undefined),
  )
  if (distinctKeys.size === 1) {
    const [key] = distinctKeys
    return { key, confidence: 0.9 }
  }

  return null
}

/** Match one already-whitespace-normalized header string against the
 * LOGISTICS canonical field registry. Exported for the same reason
 * material-parser.ts exports matchMaterialHeaderColumn: single-cell callers
 * (tests, a future UI preview) that don't want to manage the registry cache
 * themselves. */
export async function matchLogisticsHeaderColumn(headerCell: string): Promise<ColumnMatch | null> {
  const ctx = await loadLogisticsRegistry()
  return matchHeaderColumnSync(headerCell, ctx)
}

function normalizeHeaderCell(v: unknown): string {
  return String(v ?? '')
    .replace(/[\r\n]+/g, ' ')
    .replace(/\s+/g, ' ')
    .trim()
}

function colLetter(col0: number): string {
  let n = col0 + 1
  let out = ''
  while (n > 0) {
    const rem = (n - 1) % 26
    out = String.fromCharCode(65 + rem) + out
    n = Math.floor((n - 1) / 26)
  }
  return out
}

function toNum(v: unknown): number | null {
  if (v === null || v === undefined || v === '') return null
  // `Number(new Date(...))` liefert den Millisekunden-Epoch, nicht NaN — eine
  // Zahlenspalte mit datums-/zeitartigem Zellformat käme sonst als
  // Milliardenwert in der Kalkulation an, ohne Fehler und ohne Warnung. Seit
  // der BIFF-Lesepfad `cellDates: true` setzt, liefern auch .xls-Dateien echte
  // Date-Objekte; 964 von 1076 Realdateien tragen Datumszellen, ein Großteil
  // davon in genau den Blättern, die dieser Parser liest.
  if (v instanceof Date) return null
  const n = Number(v)
  return isNaN(n) ? null : n
}

/** Best matching row for the LOGISTICS header, scanning the first
 * HEADER_SCAN_MAX_ROWS rows exactly like material-parser.ts's
 * findMaterialHeaderRow — template files have the header at row 1, real BMW // allow-customer-string
 * files deeper. */
export async function findLogisticsHeaderRow(grid: unknown[][]): Promise<number | null> {
  if (!grid || grid.length === 0) return null
  const ctx = await loadLogisticsRegistry()

  let bestIdx: number | null = null
  let bestScore = 0
  const scanLimit = Math.min(grid.length, HEADER_SCAN_MAX_ROWS)
  for (let i = 0; i < scanLimit; i++) {
    const row = grid[i] ?? []
    let score = 0
    for (const cell of row) {
      if (matchHeaderColumnSync(normalizeHeaderCell(cell), ctx)) score += 1
    }
    if (score > bestScore) {
      bestScore = score
      bestIdx = i
    }
  }
  return bestScore >= HEADER_MATCH_MIN ? bestIdx : null
}

// ── Parse-level diagnostics ─────────────────────────────────────────────────

export interface LogisticsParseMeta {
  /** Arithmetic mean of per-column confidence over mapped columns — 1.0 for
   * an intact sheet, lower once normalized-only matches enter the mix. */
  parseConfidence: number
  /** Header cell text of every non-empty header-row cell that matched no
   * LOGISTICS canonical field. */
  unmappedHeaders: string[]
  /** Number of header columns mapped to a LogisticsFieldKey. */
  mappedFieldCount: number
  /** True once every CORE_LOGISTICS_FIELD_KEYS was located. False means the
   * header is missing at least one core field — since KAR-958/P2, rows is
   * NOT unconditionally empty in that case anymore (see
   * MIN_SIGNAL_MAPPED_COLUMNS / `degradation` below). */
  coreFieldsFound: boolean
  /** KAR-958/P2 — set only when `coreFieldsFound` is false AND at least
   * MIN_SIGNAL_MAPPED_COLUMNS columns mapped (partial extraction attempted).
   * `undefined` on every intact parse and on a genuinely empty/foreign
   * sheet. */
  degradation?: FacetDegradation
}

export type LogisticsParseResult = LogisticsRow[] & LogisticsParseMeta

function withParseMeta(rows: LogisticsRow[], meta: LogisticsParseMeta): LogisticsParseResult {
  return Object.assign(rows, meta) as LogisticsParseResult
}

const EMPTY_PARSE_META: LogisticsParseMeta = {
  parseConfidence: 0,
  unmappedHeaders: [],
  mappedFieldCount: 0,
  coreFieldsFound: false,
}

/**
 * Parse an already-located LOGISTICS&CUSTOM worksheet — ONE header row + ONE
 * contiguous data block (see module header "Standort-Struktur-Entscheidung":
 * the Leitfaden documents a flat row/column table here, not RMR's genuinely
 * different two-block layout). Pure aside from the lazy registry import.
 * Never throws on a degraded/unusable header (see CORE_LOGISTICS_FIELD_KEYS
 * doc comment) — returns an empty, flagged result instead, so a malformed
 * LOGISTICS sheet cannot fail the whole file ingest.
 */
export async function parseLogisticsWorksheet(ws: Worksheet): Promise<LogisticsParseResult> {
  const grid = worksheetToGrid(ws)
  if (grid.length < 2) return withParseMeta([], EMPTY_PARSE_META)

  const headerIdx = await findLogisticsHeaderRow(grid)
  if (headerIdx === null) return withParseMeta([], EMPTY_PARSE_META)

  const headerRow = (grid[headerIdx] as unknown[]).map(normalizeHeaderCell)
  const sheetName = ws.name

  const ctx = await loadLogisticsRegistry()
  const colMap = new Map<number, LogisticsFieldKey>()
  const colConfidence = new Map<number, number>()
  const unmappedHeaders: string[] = []
  const usedKeys = new Set<LogisticsFieldKey>()

  for (let i = 0; i < headerRow.length; i++) {
    const cell = headerRow[i]
    if (cell === '') continue
    const match = matchHeaderColumnSync(cell, ctx)
    if (!match || usedKeys.has(match.key)) {
      // No documented LOGISTICS label duplicate (unlike MATERIAL's
      // Mengeneinheit collision) — a second column claiming an already-used
      // key is treated as unmapped rather than silently overwriting the
      // first column.
      unmappedHeaders.push(cell)
      continue
    }
    colMap.set(i, match.key)
    colConfidence.set(i, match.confidence)
    usedKeys.add(match.key)
  }

  const mappedKeys = new Set(colMap.values())
  const coreFieldsFound = CORE_LOGISTICS_FIELD_KEYS.every((k) => mappedKeys.has(k))
  // KAR-958/P2 (gate-audit.md B8): below the minimum-signal floor, treated
  // exactly as before this PR. At/above it, a missing core field no longer
  // discards every already-mapped column — the row-push guard below still
  // independently requires deliverySite (possibly itself a missing core
  // field), so a sheet without any identifiable rows still yields `rows: []`.
  if (colMap.size < MIN_SIGNAL_MAPPED_COLUMNS) {
    return withParseMeta([], {
      parseConfidence: 0,
      unmappedHeaders,
      mappedFieldCount: colMap.size,
      coreFieldsFound: false,
    })
  }

  const mappedFieldCount = colMap.size
  const confidences = [...colConfidence.values()]
  const parseConfidence = confidences.length > 0 ? confidences.reduce((a, b) => a + b, 0) / confidences.length : 0

  const rows: LogisticsRow[] = []

  for (let i = headerIdx + 1; i < grid.length; i++) {
    const row = (grid[i] as unknown[]) ?? []
    if (row.every((c) => c === '' || c === null || c === undefined)) break

    const r: Partial<LogisticsRowValues> = {}
    for (const key of ALL_FIELD_KEYS) {
      ;(r as Record<string, unknown>)[key] = TEXT_FIELDS.has(key) ? '' : null
    }

    const sourceCells: Partial<Record<LogisticsFieldKey, string>> = {}
    const normalized: Partial<Record<LogisticsFieldKey, string | number | null>> = {}
    const rawText: Partial<Record<LogisticsFieldKey, string>> = {}
    const sheetRowNumber = i + 1 // worksheetToGrid is 0-based, ExcelJS rows are 1-based

    for (const [colIdx, key] of colMap.entries()) {
      const val = row[colIdx]
      const normVal: string | number | null = TEXT_FIELDS.has(key) ? String(val ?? '').trim() : toNum(val)
      ;(r as Record<string, unknown>)[key] = normVal
      sourceCells[key] = `${sheetName}!${colLetter(colIdx)}${sheetRowNumber}`
      normalized[key] = normVal
      if (!TEXT_FIELDS.has(key) && normVal === null && val !== null && val !== undefined && String(val).trim() !== '') {
        rawText[key] = String(val)
      }
    }

    // Row-push guard mirrors material-parser.ts's pattern (keep only rows
    // that actually carry a core entity identity) — deliverySite (Anliefer-
    // standort) is this sheet's row-identity field, the value the whole
    // sheet is organized by (task instruction), so a BMW footer/subtotal row // allow-customer-string
    // (no Anlieferstandort) is never mistaken for a genuine LOGISTICS
    // position.
    if (r.deliverySite) {
      rows.push({ ...(r as LogisticsRowValues), sourceCells, normalized, rawText })
    }
  }

  const degradation: FacetDegradation | undefined = coreFieldsFound
    ? undefined
    : {
        facet: 'logistics',
        reason: 'PARSE_FAILED',
        sheet: sheetName,
        message: `LOGISTICS&CUSTOM-Kernfelder fehlen: ${CORE_LOGISTICS_FIELD_KEYS.filter((k) => !mappedKeys.has(k)).join(', ')}.`,
      }

  return withParseMeta(rows, {
    parseConfidence,
    unmappedHeaders,
    mappedFieldCount,
    coreFieldsFound,
    ...(degradation ? { degradation } : {}),
  })
}

/**
 * Locate + parse the LOGISTICS&CUSTOM sheet from an already-loaded workbook.
 * Returns null when the workbook has no LOGISTICS sheet at all (the additive
 * gate — distinct from "sheet present but header unusable", which returns an
 * empty LogisticsParseResult with coreFieldsFound:false instead, see
 * parseLogisticsWorksheet doc comment).
 */
export async function parseLogisticsSheet(wb: { worksheets: Worksheet[] }): Promise<LogisticsParseResult | null> {
  const ws = findLogisticsWorksheet(wb)
  if (!ws) return null
  return parseLogisticsWorksheet(ws)
}

/**
 * Derive the tri-state `LogisticsRow[] | null` value callers (actions.ts's
 * ingestQafUpload, and via it QafFileParsed.logisticsRows /
 * ReconciliationInput.logisticsRows) must persist/thread from a
 * `parseLogisticsSheet` result — the single source of truth for that
 * derivation, mirroring rmr-parser.ts's rmrRowsForReconciliation /
 * sbm-parser.ts's sbmRowsForReconciliation (the established pattern this
 * module builds in from day one instead of retrofitting, per the KAR-903 task
 * instruction "coreFieldsFound-Tri-State + logisticsRowsForReconciliation/
 * FromPersistedMeta ab Tag 1" — see rmr-parser.ts's own doc comment on this
 * function for the full false-positive scenario a naive `parsed ? [...parsed]
 * : null` would create).
 */
export function logisticsRowsForReconciliation(parsed: LogisticsParseResult | null): LogisticsRow[] | null {
  if (parsed === null) return null
  if (!parsed.coreFieldsFound) return null
  return [...parsed]
}

/**
 * The shape actions.ts's ingestQafUpload persists on
 * `qaf_file.g60_meta.logistics` (see that file's `logisticsMeta` local) —
 * `null` when no LOGISTICS sheet was found at all, otherwise the parsed rows
 * plus the same LogisticsParseMeta a live LogisticsParseResult carries.
 */
export interface PersistedLogisticsMeta {
  rows: LogisticsRow[]
  parseMeta: LogisticsParseMeta
}

/**
 * Persisted-JSONB counterpart to logisticsRowsForReconciliation above
 * (KAR-899-style rehydration path, threaded through from the start per task
 * instruction) — same rationale and tri-state contract as material-parser.ts's
 * materialRowsFromPersistedMeta / sbm-parser.ts's sbmRowsFromPersistedMeta /
 * rmr-parser.ts's rmrRowsFromPersistedMeta: `undefined` when
 * `qaf_file.g60_meta.logistics` carries no key at all (pre-KAR-903/P2.4 file,
 * or a G60 file — no LOGISTICS parse was ever attempted), `null`/rows
 * otherwise, delegated to logisticsRowsForReconciliation so the live-parse and
 * rehydrated-JSONB entry points cannot drift.
 */
export function logisticsRowsFromPersistedMeta(
  meta: PersistedLogisticsMeta | null | undefined,
): LogisticsRow[] | null | undefined {
  if (meta === undefined) return undefined
  if (meta === null) return null
  return logisticsRowsForReconciliation(withParseMeta([...meta.rows], meta.parseMeta))
}

// ── Formel-Validierung: Logistikkosten je Anlieferstandort (Leitfaden [40]) ─
//
// See module header "Volumen-Diskrepanz" for the evidence derivation and the
// explicit, documented choice of the [40] no-volume formula over the [39]
// prose. Modeled after rmr-parser.ts's validateRmrRawMaterialSurcharge shape
// (BusinessRuleResult-style judge()/notPruefbar()) but kept SELF-CONTAINED in
// this module rather than added to business-rules.ts — same reasoning
// rmr-parser.ts documents (business-rules.ts is P2.2's own scope, this PR
// does not touch it). Issue namespace is `log_*`, distinct from
// business-rules.ts's `rule_calc_*`, reconciliation.ts's `recon_*` and
// rmr-parser.ts's `rmr_*`.

export type LogisticsValidationCheckId = 'log_calc_cost_per_delivery_site' | 'log_incoterm_domain'
export type LogisticsValidationStatus = 'bestanden' | 'abweichung' | 'nicht_pruefbar' | 'pruefen'

export interface LogisticsValidationResult {
  checkId: LogisticsValidationCheckId
  side: ComparisonSide
  /** Index in the logisticsRows array this result belongs to. -1 for a
   * file-level result (no LOGISTICS sheet at all — there is no row to index
   * into). */
  rowIndex: number
  /** Anlieferstandort of the row (empty string for a file-level result). */
  deliverySite: string
  status: LogisticsValidationStatus
  /** Set for log_calc_cost_per_delivery_site only. */
  expected?: number | null
  actual?: number | null
  deltaAbsolute?: number | null
  deltaPercent?: number | null
  /** Set for log_incoterm_domain only — the raw Lieferbedingungen value. */
  value?: string
  /** Set only for status === 'nicht_pruefbar' — why no verdict could be reached. */
  reason?: string
  /** EN counterpart of `reason` (KAR-906/P3.2). Set whenever `reason` is. */
  reasonEn?: string
  messageDe?: string
  messageEn?: string
}

/**
 * Central tolerance config for this module — same STARTWERT-Fachentscheid
 * philosophy as reconciliation.ts's RECONCILIATION_CONFIG / rmr-parser.ts's
 * RMR_VALIDATION_CONFIG (0.5% relativ + 1 AW absolut, KAR-896-Muster), kept as
 * its own local constant for the same "single, narrowly-scoped check does not
 * need engine-config.ts wiring" reasoning rmr-parser.ts documents.
 */
export interface LogisticsValidationConfig {
  /** Fraction, e.g. 0.005 = 0.5%. */
  relativeTolerance: number
  /** Absolute floor in AW, applied via max(relative, absolute). */
  absoluteToleranceMinor: number
}

export const LOGISTICS_VALIDATION_CONFIG: LogisticsValidationConfig = {
  relativeTolerance: 0.005,
  absoluteToleranceMinor: 1,
}

function fmt(n: number): string {
  return n.toFixed(4)
}

function withinTolerance(expected: number, actual: number, cfg: LogisticsValidationConfig): boolean {
  const threshold = Math.max(Math.abs(expected) * cfg.relativeTolerance, cfg.absoluteToleranceMinor)
  return Math.abs(actual - expected) <= threshold
}

function judgeCostFormula(
  side: ComparisonSide,
  rowIndex: number,
  deliverySite: string,
  expected: number,
  actual: number,
  cfg: LogisticsValidationConfig,
): LogisticsValidationResult {
  const deltaAbsolute = Number((actual - expected).toFixed(6))
  const deltaPercent = expected !== 0 ? Number((deltaAbsolute / Math.abs(expected)).toFixed(6)) : null
  const ok = withinTolerance(expected, actual, cfg)
  const label = deliverySite || '(ohne Anlieferstandort)'
  const messageDe = `Logistikkosten je Anlieferstandort (${side}, ${label}): Excel weist ${fmt(actual)} aus, die unabhaengige Nachrechnung (Transportkosten + Verpackungskosten + Vorverpackungskosten) ergibt ${fmt(expected)} (Delta ${fmt(deltaAbsolute)}) — Abweichung ausserhalb der Toleranz (${(cfg.relativeTolerance * 100).toFixed(1)} % / min. ${cfg.absoluteToleranceMinor}).`
  const messageEn = `Logistics costs per delivery site (${side}, ${label}): Excel states ${fmt(actual)}, the independent recomputation (transport + packaging + pre-packaging costs) yields ${fmt(expected)} (delta ${fmt(deltaAbsolute)}) — deviation exceeds tolerance (${(cfg.relativeTolerance * 100).toFixed(1)}% / min. ${cfg.absoluteToleranceMinor}).`
  return {
    checkId: 'log_calc_cost_per_delivery_site',
    side,
    rowIndex,
    deliverySite,
    status: ok ? 'bestanden' : 'abweichung',
    expected,
    actual,
    deltaAbsolute,
    deltaPercent,
    ...(ok ? {} : { messageDe, messageEn }),
  }
}

function notPruefbarCostFormula(
  side: ComparisonSide,
  rowIndex: number,
  deliverySite: string,
  reason: string,
  reasonEn: string,
): LogisticsValidationResult {
  return {
    checkId: 'log_calc_cost_per_delivery_site',
    side,
    rowIndex,
    deliverySite,
    status: 'nicht_pruefbar',
    expected: null,
    actual: null,
    deltaAbsolute: null,
    deltaPercent: null,
    reason,
    reasonEn,
  }
}

/**
 * Row-level check: Logistikkosten je Anlieferstandort ==
 * Transportkosten + Verpackungskosten + Vorverpackungskosten (je Bauteil),
 * Leitfaden [40] Formeln/Berechnungslogik (see module header "Volumen-
 * Diskrepanz"). Gated (same pattern rmr-parser.ts's validateRmrRawMaterial
 * Surcharge uses): returns null — no result at all, not even
 * nicht_pruefbar — when logisticsCostPerDeliverySite itself is empty
 * (nothing to check yet, a normal intermediate state for a not-yet-fully-
 * quoted delivery route). Zero is treated as a valid, present input for
 * every component field (a delivery site legitimately carrying no additional
 * packaging cost is not "missing data").
 */
export function validateLogisticsCostFormula(
  row: LogisticsRow,
  rowIndex: number,
  side: ComparisonSide,
  cfg: LogisticsValidationConfig = LOGISTICS_VALIDATION_CONFIG,
): LogisticsValidationResult | null {
  const actual = row.logisticsCostPerDeliverySite
  if (actual === null) return null // Gating: nothing to check on this row yet.

  const missing: { de: string; en: string }[] = []
  const checkComponent = (value: number | null, rawTextKey: LogisticsFieldKey, labelDe: string) => {
    if (value !== null) return
    const raw = row.rawText?.[rawTextKey]
    const naMarked = raw !== undefined && isNotApplicableValue(raw)
    missing.push(missingFieldReason(labelDe, logisticsLabelEn(rawTextKey, labelDe), naMarked))
  }
  checkComponent(row.transportCostPerPart, 'transportCostPerPart', 'Transportkosten pro Bauteil Lieferant')
  checkComponent(row.packagingCostPerPart, 'packagingCostPerPart', 'Verpackungskosten pro Bauteil Lieferant')
  checkComponent(row.prePackagingCostPerPart, 'prePackagingCostPerPart', 'Vorverpackungskosten pro Bauteil Lieferant')

  if (missing.length > 0) {
    const joined = joinMissingReasons(missing)
    return notPruefbarCostFormula(side, rowIndex, row.deliverySite, joined.de, joined.en)
  }

  const expected = row.transportCostPerPart! + row.packagingCostPerPart! + row.prePackagingCostPerPart!
  return judgeCostFormula(side, rowIndex, row.deliverySite, expected, actual, cfg)
}

// ── Incoterm-Domänenliste (Leitfaden [39] "Erläuterung der Incoterms" / [40]
// Lieferbedingungen-Dropdown) ────────────────────────────────────────────────
//
// FCA/DAP/DDP are the ONLY three Incoterms the extracted Leitfaden pages
// document for this dropdown ([39]: "Lieferbedingung (Incoterm FCA/DAP/DDP)
// waehlen" + the three "Erlaeuterung der Incoterms" bullets; [40]: "Auswahl
// Incoterm (FCA/DAP/DDP)") — no fourth code (EXW/FOB/CIF/CPT/...) appears
// anywhere in 02-leitfaden-teil1.md or 02-leitfaden-teil2.md in connection
// with this sheet's Lieferbedingungen field. This is therefore the complete,
// evidenced domain list, not a partial one awaiting "weitere" entries —
// inventing additional codes without Leitfaden evidence would violate
// Master-Prompt §2's "never silently guess" the same way a wrong formula
// would. An unrecognized value is a `pruefen`-severity HINT, never an error
// (task instruction: "unbekannter Wert = pruefen-Hinweis, kein Fehler") —
// this module never rejects/blocks on it, and does NOT implement the
// Incoterm's legal/contractual consequences (backlog [P2.4] scope: "NICHT
// rein: die vollstaendige Incoterm-Rechtslogik").
export const LOGISTICS_INCOTERM_VALUES: readonly string[] = ['FCA', 'DAP', 'DDP']

function judgeIncotermDomain(
  side: ComparisonSide,
  rowIndex: number,
  deliverySite: string,
  value: string,
): LogisticsValidationResult {
  const known = LOGISTICS_INCOTERM_VALUES.includes(value.trim().toUpperCase())
  const label = deliverySite || '(ohne Anlieferstandort)'
  const messageDe = `Lieferbedingungen (${side}, ${label}): Wert "${value}" ist kein dokumentiertes Incoterm dieses Blattes (${LOGISTICS_INCOTERM_VALUES.join('/')}) — bitte pruefen, ob eine seltenere Lieferbedingung vorliegt oder ein Tippfehler.`
  const messageEn = `Delivery terms (${side}, ${label}): value "${value}" is not a documented Incoterm for this sheet (${LOGISTICS_INCOTERM_VALUES.join('/')}) — please review whether this is a rarer delivery term or a typo.`
  return {
    checkId: 'log_incoterm_domain',
    side,
    rowIndex,
    deliverySite,
    status: known ? 'bestanden' : 'pruefen',
    value,
    ...(known ? {} : { messageDe, messageEn }),
  }
}

/**
 * Row-level check: is Lieferbedingungen (Incoterm) one of the documented
 * domain values? Gated: returns null when deliveryTerms is empty (nothing to
 * check yet — a normal intermediate state before the supplier has picked an
 * Incoterm). Case-insensitive against LOGISTICS_INCOTERM_VALUES (a real
 * dropdown value could legitimately be typed/pasted in any case by an older
 * template revision) — the ORIGINAL value is kept verbatim in the result/
 * message, never silently normalized away.
 */
export function validateLogisticsIncotermDomain(
  row: LogisticsRow,
  rowIndex: number,
  side: ComparisonSide,
): LogisticsValidationResult | null {
  const value = row.deliveryTerms
  if (!value) return null // Gating: nothing to check on this row yet.
  return judgeIncotermDomain(side, rowIndex, row.deliverySite, value)
}

export interface LogisticsValidationInput {
  side: ComparisonSide
  /** Tri-state, identical contract to reconciliation.ts's
   * ReconciliationInput.materialRows / rmr-parser.ts's
   * RmrValidationInput.rmrRows: undefined = no LOGISTICS parse attempted (both
   * checks are omitted entirely, not even as nicht_pruefbar); null = a parse
   * was attempted but no usable LOGISTICS sheet was found (one file-level
   * nicht_pruefbar per check family); LogisticsRow[] = parsed rows (can
   * legitimately be empty). */
  logisticsRows?: LogisticsRow[] | null
}

function fileLevelNotPruefbar(checkId: LogisticsValidationCheckId, side: ComparisonSide): LogisticsValidationResult {
  return {
    checkId,
    side,
    rowIndex: -1,
    deliverySite: '',
    status: 'nicht_pruefbar',
    reason: 'Kein LOGISTICS&CUSTOM-Sheet in dieser Datei erkannt (oder Header zu stark abweichend) — Nachrechnung nicht moeglich.',
    reasonEn: 'No LOGISTICS&CUSTOM sheet detected in this file (or header too degraded) — recomputation not possible.',
  }
}

/** Orchestrator over one side's logisticsRows tri-state — mirrors
 * rmr-parser.ts's evaluateRmrValidation / business-rules.ts's
 * evaluateBusinessRules materialRows/sbmRows handling exactly, just across
 * TWO independent check families (cost formula + Incoterm domain) instead of
 * one. */
export function evaluateLogisticsValidation(
  input: LogisticsValidationInput,
  config: LogisticsValidationConfig = LOGISTICS_VALIDATION_CONFIG,
): LogisticsValidationResult[] {
  const { side, logisticsRows } = input
  if (logisticsRows === undefined) return [] // no parse attempted -> no results at all
  if (logisticsRows === null) {
    return [
      fileLevelNotPruefbar('log_calc_cost_per_delivery_site', side),
      fileLevelNotPruefbar('log_incoterm_domain', side),
    ]
  }

  const results: LogisticsValidationResult[] = []
  logisticsRows.forEach((row, idx) => {
    const costResult = validateLogisticsCostFormula(row, idx, side, config)
    if (costResult) results.push(costResult)
    const incotermResult = validateLogisticsIncotermDomain(row, idx, side)
    if (incotermResult) results.push(incotermResult)
  })
  return results
}

// ── Persistence bridge ───────────────────────────────────────────────────────
//
// Reuses the qaf_plausibility_issue path (no schema change), namespaced
// log_calc_cost_per_delivery_site (formula abweichung/nicht_pruefbar) and
// log_incoterm_domain (unknown-value hint), same pattern as reconciliation.ts's
// recon_<checkId>/rmr-parser.ts's rmr_<checkId>. 'bestanden' never produces an
// issue. Severity 'pruefen' for a formula abweichung (not 'kritisch') — same
// one-hop-removed reasoning rmr-parser.ts documents: the deviation itself is
// already independently surfaced via reconciliation.ts's logistics_transport_
// detail_sum (which DOES sum this field against the Summary sheet and carries
// its own severity), so this row-local check stays a review-level finding, not
// a duplicate critical alert. Severity 'pruefen' for an unknown Incoterm too —
// task instruction: "pruefen-Hinweis, kein Fehler", i.e. explicitly not
// 'kritisch'. 'nicht_pruefbar' stays 'hinweis', matching every other module's
// convention in this file family.
export function logisticsValidationResultToPlausibilityIssue(r: LogisticsValidationResult): PlausibilityIssue | null {
  if (r.status === 'bestanden') return null

  if (r.status === 'nicht_pruefbar') {
    return {
      type: `${r.checkId}_nicht_pruefbar`,
      severity: 'hinweis' as PlausibilitySeverity,
      step: r.side,
      explanation: r.reason ?? 'Nachrechnung nicht moeglich (unvollstaendige Datenbasis).',
      explanationEn: r.reasonEn,
    }
  }

  // 'abweichung' (cost formula) or 'pruefen' (Incoterm domain) — both surface
  // as a 'pruefen'-severity finding, see comment above.
  return {
    type: r.checkId,
    severity: 'pruefen' as PlausibilitySeverity,
    step: r.side,
    explanation: r.messageDe ?? '',
    explanationEn: r.messageEn,
  }
}

export function checkLogisticsValidation(
  input: LogisticsValidationInput,
  config: LogisticsValidationConfig = LOGISTICS_VALIDATION_CONFIG,
): PlausibilityIssue[] {
  return evaluateLogisticsValidation(input, config)
    .map(logisticsValidationResultToPlausibilityIssue)
    .filter((x): x is PlausibilityIssue => x !== null)
}