// Multi-QAF / Variante↔Standard XLSX export (KAR-949).
//
// Two ExcelJS workbook builders, sibling to ../export.ts's
// buildQafExportWorkbook (Standard-Vergleich) and ./g60/export.ts's
// buildG60ExportWorkbook (G60-Detailvergleich) — NEITHER of those two files
// is touched by this module, so their byte-for-byte output is unaffected
// (task requirement: "Standard- und G60-Export byte-identisch").
//
// Both builders are PURE w.r.t. their inputs (a caller-supplied
// generatedAtLabel string plus already-persisted/rehydrated domain objects —
// no Date, no re-parse of a workbook, no DB access) and, like every
// PlausibilityIssue/MultiQafWarning/AggregateImpactGateOutcome consumer in
// this package, honest about uncertainty: every "unknown"/"nicht
// ermittelbar"/"nicht prüfbar" state gets its OWN visible status text — an
// empty cell that could be misread as "0" or "no difference" is a bug (task
// invariant "Ehrlichkeits-Invarianten im Export"). `result`/`container`
// arguments are nullable throughout and every sheet builder tolerates a
// `null` (an older/corrupt persisted record, or a comparison that was never
// actually run) by emitting one explanatory hint row instead of throwing —
// same "render an honest 'nicht verfügbar' section instead of crashing"
// contract app/qaf-differences/[id]/page.tsx's own try/catch-wrapped
// deserialize already establishes for this data.
//
// Every status/reason/gate/check code is exported in TWO columns side by
// side: the stable machine code (Code) AND a curated DE/EN plain-language
// sentence (Klartext) — task requirement "Klartexte + stabile Codes beide
// exportieren". The label maps below are a lib-local, presentation-only
// duplicate of components/qaf-differences/qaf-multi-qaf-code-labels.ts's
// vocabulary (never imported from here: this package is UI-independent —
// see lib/qaf-differences/index.ts's own module header "usable from server
// actions, batch jobs, CLI and tests" — lib/ must not depend on
// components/). An unrecognized code safely falls back to itself as both
// DE/EN (never a crash, never a blank cell) — same `labelFor` discipline
// code-labels.ts already establishes.
//
// tdd-guard:skip — sheet-builder composition of already-persisted/rehydrated
// data into ExcelJS rows; covered by export.test.ts (synthetic fixtures,
// incl. the honesty-invariant cases) and the env-gated real-files tests.

import type { Workbook } from 'exceljs'
import type {
  MultiQafContainer,
  VariantDefinition,
  MultiQafTemplateFamily,
  MultiQafFamilyClassification,
} from './types'
import type { MultiQafComparisonResult } from './compare-flow'
import type { VariantMatchOverride, VariantMatchStage } from './variant-matcher'
import type { VariantReconciliationResult, VariantReconciliationStatus, VariantReconciliationCheckId, VariantReconciliationNichtPruefbarReason } from './variant-reconciliation'
import type { SummaryTotalsMetricKey, SummaryTotalsMetricState } from './summary-totals-differ'
import type { AggregateImpactGateId, AggregateImpactExclusionReason } from './aggregate-impact'
import type {
  VariantVsStandardComparisonResult,
  VariantVsStandardReviewRequiredReason,
} from './variant-vs-standard'
import type { SummaryMetricKey } from '../summary-metrics'
import { METRIC_LABELS_DE } from '../summary-metrics'
import type { PlausibilitySeverity } from '../plausibility'
import {
  FILL_RISE,
  FILL_FALL,
  FILL_REMOVED,
  FILL_WARN,
  newSheet,
  titleRow,
  headerRow,
  subHeaderRow,
  blank,
  numFmt,
  fillCell,
  statusBandFill,
} from '../xlsx-style-helpers'

// FILL_NEUTRAL is the SAME ARGB value as the shared FILL_REMOVED but under a
// distinct name (covers nicht_ermittelbar/nicht_pruefbar/unknown states, not
// "removed") — kept as a local alias rather than folded into the shared
// palette (KAR-949 review Finding 2: document divergent-looking-but-actually-
// same values instead of silently unifying the names).
const FILL_NEUTRAL = FILL_REMOVED

// ── Bilingual label maps (lib-local — see module header) ───────────────────

interface Bl {
  de: string
  en: string
}

function bl(map: Record<string, Bl>, code: string | null | undefined): Bl {
  if (!code) return { de: '—', en: '—' }
  return map[code] ?? { de: code, en: code }
}

function blText(map: Record<string, Bl>, code: string | null | undefined): string {
  const l = bl(map, code)
  return l.de === l.en ? l.de : `${l.de} / ${l.en}`
}

const DIFF_STATUS_LABEL: Record<string, Bl> = {
  neu: { de: 'neu', en: 'new' },
  entfallen: { de: 'entfallen', en: 'removed' },
  nicht_berechenbar: { de: 'nicht berechenbar', en: 'not computable' },
  nicht_anwendbar: { de: 'nicht anwendbar', en: 'not applicable' },
  blockiert: { de: 'blockiert (Pflichtfeld)', en: 'blocked (mandatory field)' },
  konstant: { de: 'konstant', en: 'unchanged' },
  anstieg: { de: 'Anstieg', en: 'increase' },
  senkung: { de: 'Senkung', en: 'decrease' },
  auffaellig_10: { de: 'auffällig (>10%)', en: 'notable (>10%)' },
  auffaellig_25: { de: 'auffällig (>25%)', en: 'notable (>25%)' },
  kritisch_50: { de: 'kritisch (>50%)', en: 'critical (>50%)' },
  formel_geaendert: { de: 'Formel geändert (Wert gleich)', en: 'formula changed (value unchanged)' },
  formel_zu_konstante: { de: 'Formel durch Konstante ersetzt', en: 'formula replaced by constant' },
  changed: { de: 'geändert', en: 'changed' },
  unchanged: { de: 'unverändert', en: 'unchanged' },
  nicht_ermittelbar: { de: 'nicht ermittelbar', en: 'undeterminable' },
}

const RECONCILIATION_STATUS_LABEL: Record<VariantReconciliationStatus, Bl> = {
  bestanden: { de: 'bestanden', en: 'passed' },
  abweichung: { de: 'Abweichung', en: 'deviation' },
  nicht_pruefbar: { de: 'nicht prüfbar', en: 'not verifiable' },
}

const RECONCILIATION_CHECK_LABEL: Record<VariantReconciliationCheckId, Bl> = {
  currency: { de: 'Währung', en: 'Currency' },
  volumes: { de: 'Volumina', en: 'Volumes' },
  material_total_detail_to_summary: { de: 'Material-Summe (Detail → Summary)', en: 'Material total (detail → Summary)' },
  material_total_matrix_independent: { de: 'Material-Summe (unabhängig, Matrix)', en: 'Material total (independent, matrix)' },
  manufacturing_total: { de: 'Fertigungskosten-Summe', en: 'Manufacturing cost total' },
  setup_cost_allocation: { de: 'Rüstkosten-Allokation', en: 'Setup cost allocation' },
  tooling_fixture_cost: { de: 'Werkzeug-/Vorrichtungskosten', en: 'Tooling/fixture cost' },
  scrap: { de: 'Ausschuss', en: 'Scrap' },
  surcharges: { de: 'Zuschläge/Overhead', en: 'Surcharges/overhead' },
  offer_base_price: { de: 'Angebotsbasispreis', en: 'Offer base price' },
  offer_price: { de: 'Angebotspreis', en: 'Offer price' },
  total_production_costs_cascade: { de: 'Summe Herstellkosten (Kaskade)', en: 'Total production costs (cascade)' },
  formula_identity_material_rows: { de: 'Formel-Identität Materialzeilen', en: 'Formula identity, material rows' },
  formula_identity_profiles: { de: 'Formel-Identität Profile', en: 'Formula identity, profiles' },
}

const NICHT_PRUEFBAR_REASON_LABEL: Record<VariantReconciliationNichtPruefbarReason, Bl> = {
  fehlende_daten: { de: 'fehlende Daten', en: 'missing data' },
  same_source_no_independent_check: { de: 'gleiche Quelle, kein unabhängiger Gegencheck', en: 'same source, no independent check' },
  mixed_currency: { de: 'gemischte Währungen', en: 'mixed currencies' },
  unmatched_variant: { de: 'Variante nicht gematcht', en: 'variant not matched' },
  fingerprint_unavailable: { de: 'Struktur-Fingerabdruck nicht verfügbar', en: 'structural fingerprint unavailable' },
}

const REVIEW_REASON_LABEL: Record<string, Bl> = {
  alt_container_review_required: { de: 'ALT-Datei braucht eigene Review', en: 'ALT file needs its own review' },
  neu_container_review_required: { de: 'NEU-Datei braucht eigene Review', en: 'NEU file needs its own review' },
  uncertain_variant_matches_present: { de: 'unsichere Varianten-Zuordnungen vorhanden', en: 'uncertain variant matches present' },
  caller_override: { de: 'manuell erzwungen', en: 'manually forced' },
  match_overrides_dropped_on_drift: {
    de: 'manuelle Zuordnungs-Overrides beim Neu-Matching verworfen (Drift)',
    en: 'manual match overrides dropped on re-matching drift',
  },
  summary_totals_changed_without_material_or_profile_evidence: {
    de: 'Summary-Kennzahl geändert ohne erklärenden Material-/Profil-Befund',
    en: 'Summary metric changed without a corroborating material/profile finding',
  },
  container_review_required: { de: 'Container braucht eigene Review', en: 'Container needs its own review' },
  variant_material_below_aggregation_gate: {
    de: 'Varianten-Materialdaten unterhalb der Aggregations-Mindestanforderung',
    en: 'Variant material data below the aggregation minimum bar',
  },
  summary_identity_critical_issue: { de: 'kritischer Summary-Identitäts-Befund', en: 'critical summary-identity finding' },
}

const GATE_LABEL: Record<AggregateImpactGateId, Bl> = {
  compatible_currencies: { de: 'Kompatible Währungen', en: 'Compatible currencies' },
  volumes_available: { de: 'Volumen vorhanden', en: 'Volumes available' },
  no_duplicates_or_ambiguous: { de: 'Keine Duplikate/Unsicherheiten', en: 'No duplicates/ambiguities' },
  consistent_units: { de: 'Konsistente Einheiten', en: 'Consistent units' },
  valid_baseline: { de: 'Valide Baseline', en: 'Valid baseline' },
  no_blocked_critical_mappings: { de: 'Keine blockierten Zuordnungen', en: 'No blocked mappings' },
}

const EXCLUSION_REASON_LABEL: Record<AggregateImpactExclusionReason, Bl> = {
  ambiguous_or_uncertain_match: { de: 'unsichere Varianten-Zuordnung', en: 'uncertain variant match' },
  blocked_critical_mapping: { de: 'geblockte kritische Zuordnung', en: 'blocked critical mapping' },
  inconsistent_units: { de: 'inkonsistente Einheiten', en: 'inconsistent units' },
  consistent_units_not_verifiable: { de: 'Einheiten-Konsistenz nicht prüfbar', en: 'unit consistency not verifiable' },
  volume_missing_annual: { de: 'Jahres-Volumen fehlt', en: 'annual volume missing' },
  volume_missing_lifetime: { de: 'Lifetime-Volumen fehlt', en: 'lifetime volume missing' },
}

const SUMMARY_TOTALS_METRIC_LABEL: Record<SummaryTotalsMetricKey, Bl> = {
  materialCosts: { de: 'Materialkosten', en: 'Material costs' },
  manufacturingCosts: { de: 'Fertigungskosten', en: 'Manufacturing costs' },
  totalProductionCosts: { de: 'Summe Herstellkosten', en: 'Total production costs' },
  toolingAndFixtureCost: { de: 'Werkzeug-/Vorrichtungskosten', en: 'Tooling/fixture cost' },
  setupCostAllocation: { de: 'Rüstkosten-Allokation', en: 'Setup cost allocation' },
  scrap: { de: 'Ausschuss', en: 'Scrap' },
  otherSurcharges: { de: 'Sonstige Zuschläge', en: 'Other surcharges' },
  offerBasePrice: { de: 'Angebotsbasispreis', en: 'Offer base price' },
  offerBasePriceInclAllocation: { de: 'Angebotsbasispreis inkl. Umlage', en: 'Offer base price incl. allocation' },
  offerPrice: { de: 'Angebotspreis', en: 'Offer price' },
}

const MATCH_STAGE_LABEL: Record<VariantMatchStage, Bl> = {
  raw_exact: { de: 'exakt (roh)', en: 'exact (raw)' },
  core_subset_exact: { de: 'exakt (Kern-Dimensionen)', en: 'exact (core dimensions)' },
  fuzzy_label_similarity: { de: 'unscharf (Label-Ähnlichkeit)', en: 'fuzzy (label similarity)' },
  manual_override: { de: 'manuell zugeordnet', en: 'manually matched' },
}

const MATCH_KIND_LABEL: Record<string, Bl> = {
  matched: { de: 'gematcht', en: 'matched' },
  ambiguous: { de: 'mehrdeutig', en: 'ambiguous' },
  split_suspected: { de: 'Split vermutet', en: 'split suspected' },
  merge_suspected: { de: 'Merge vermutet', en: 'merge suspected' },
  unmatched_left: { de: 'entfallen (nur ALT)', en: 'removed (ALT only)' },
  unmatched_right: { de: 'neu (nur NEU)', en: 'new (NEU only)' },
}

const ACTIVE_STATE_LABEL: Record<string, Bl> = {
  active: { de: 'aktiv', en: 'active' },
  inactive: { de: 'inaktiv', en: 'inactive' },
  reserved: { de: 'reserviert (leer)', en: 'reserved (empty)' },
}

const OVERRIDE_STATUS_LABEL: Record<string, Bl> = {
  active: { de: 'aktiv', en: 'active' },
  dropped_on_drift: { de: 'verworfen (Drift)', en: 'dropped (drift)' },
  dropped_ambiguous_identity: { de: 'verworfen (mehrdeutige Identität)', en: 'dropped (ambiguous identity)' },
}

const TEMPLATE_FAMILY_LABEL: Record<MultiQafTemplateFamily, Bl> = {
  qaf_8_1_custom_multi: { de: 'QAF 8.1 (custom Multi-QAF)', en: 'QAF 8.1 (custom Multi-QAF)' },
  m_qaf_1_0: { de: 'M-QAF 1.0', en: 'M-QAF 1.0' },
  m_qaf_2_0: { de: 'M-QAF 2.0', en: 'M-QAF 2.0' },
  unknown_multi_qaf: { de: 'unbekanntes Multi-QAF-Template', en: 'unknown Multi-QAF template' },
  unknown: { de: 'nie geprüft', en: 'never checked' },
}

const CLASSIFICATION_LABEL: Record<MultiQafFamilyClassification, Bl> = {
  known: { de: 'bekannt', en: 'known' },
  modified: { de: 'modifiziert', en: 'modified' },
  unknown: { de: 'unbekannt', en: 'unknown' },
}

const DEGRADED_MODULE_KEY_LABEL: Record<string, Bl> = {
  manufacturing_steps: { de: 'Fertigungs-Prozessvergleich', en: 'Manufacturing process comparison' },
  material: { de: 'MATERIAL-Detail-Vergleich', en: 'MATERIAL detail comparison' },
  sbm: { de: 'SBM-DEVICES-FWZ-Vergleich', en: 'SBM-DEVICES-FWZ comparison' },
  rmr: { de: 'RAW MATERIAL RISKS-Vergleich', en: 'RAW MATERIAL RISKS comparison' },
  logistics: { de: 'LOGISTICS & CUSTOM-Vergleich', en: 'LOGISTICS & CUSTOM comparison' },
  lccn: { de: 'LC-CN-Validierung', en: 'LC-CN validation' },
  co2e: { de: 'CO2e-Material-Validierung', en: 'CO2e material validation' },
  reconciliation: { de: 'Summen-Rekonziliation', en: 'Sum reconciliation' },
  business_rules: { de: 'Business-Rule-Nachrechnung', en: 'Business-rule recomputation' },
  rule_engine: { de: 'Fehlerreport-Regel-Engine (R1-R6)', en: 'Error-report rule engine (R1-R6)' },
  root_cause: { de: 'Root-Cause-Analyse', en: 'Root-cause analysis' },
  workbook_safety: { de: 'Untrusted-Excel-Hardening', en: 'Untrusted-Excel hardening' },
}

const SEVERITY_LABEL: Record<PlausibilitySeverity, Bl> = {
  hinweis: { de: 'Hinweis', en: 'notice' },
  pruefen: { de: 'prüfen', en: 'check' },
  kritisch: { de: 'kritisch', en: 'critical' },
}

const REVIEW_REQUIRED_REASON_LABEL: Record<VariantVsStandardReviewRequiredReason, Bl> = {
  container_review_required: REVIEW_REASON_LABEL.container_review_required,
  variant_material_below_aggregation_gate: REVIEW_REASON_LABEL.variant_material_below_aggregation_gate,
  summary_identity_critical_issue: REVIEW_REASON_LABEL.summary_identity_critical_issue,
}

// ── Small shared helpers ────────────────────────────────────────────────────

function variantLabel(v: VariantDefinition | undefined | null): string {
  if (!v) return '—'
  const labels = v.normalizedLabels.length > 0 ? v.normalizedLabels : v.originalLabels
  return labels.length > 0 ? labels.join(' / ') : v.stableInternalId
}

function variantById(container: MultiQafContainer | null): ReadonlyMap<string, VariantDefinition> {
  if (!container) return new Map()
  return new Map([...container.activeVariants, ...container.inactiveVariants].map((v) => [v.stableInternalId, v] as const))
}

function money(v: { value: number | null; currency: string | null } | null | undefined): { value: number | null; currency: string | null } {
  return v ?? { value: null, currency: null }
}

function reservedCount(container: MultiQafContainer | null): number {
  if (!container) return 0
  return container.inactiveVariants.filter((v) => v.activeState === 'reserved').length
}

// ── (a) Übersicht ────────────────────────────────────────────────────────

function buildOverviewSheet(
  wb: Workbook,
  input: MultiQafExportInput,
): void {
  const ws = newSheet(wb, 'Uebersicht', [40, 60])
  titleRow(ws, 'Multi-QAF-Vergleich — Übersicht / Overview')
  ws.addRow(['Erstellt / Generated', input.generatedAtLabel])
  ws.addRow(['ALT-Datei / ALT file', input.altFileName ?? '—'])
  ws.addRow(['NEU-Datei / NEU file', input.neuFileName ?? '—'])
  blank(ws)

  const addContainerBlock = (label: string, container: MultiQafContainer | null) => {
    subHeaderRow(ws, [`${label} — Container-Metadaten / metadata`, ''])
    if (!container) {
      ws.addRow(['Status', 'kein persistierter Container / no persisted container'])
      return
    }
    ws.addRow(['Template-Familie / Template family', blText(TEMPLATE_FAMILY_LABEL, container.detectedTemplateFamily)])
    ws.addRow(['Klassifikation / Classification', blText(CLASSIFICATION_LABEL, container.templateFingerprint.classification ?? undefined)])
    ws.addRow(['Sprache / Language', container.language])
    ws.addRow(['Währungen / Currencies', container.currencies.length ? container.currencies.join(', ') : '—'])
    const activeRow = ws.addRow(['Aktive Varianten / Active variants', container.activeVariants.length])
    numFmt(activeRow, 2, '#,##0')
    const inactiveRow = ws.addRow(['Inaktive Varianten / Inactive variants', container.inactiveVariants.length - reservedCount(container)])
    numFmt(inactiveRow, 2, '#,##0')
    const reservedRow = ws.addRow(['Reservierte Slots (leer) / Reserved slots (empty)', reservedCount(container)])
    numFmt(reservedRow, 2, '#,##0')
    const confRow = ws.addRow(['Parsing-Confidence / Parsing confidence', container.confidence])
    numFmt(confRow, 2, '0.0%')
  }
  addContainerBlock('ALT', input.altContainer)
  blank(ws)
  addContainerBlock('NEU', input.neuContainer)
  blank(ws)

  subHeaderRow(ws, ['Vergleichs-Ergebnis / Comparison result', ''])
  const result = input.result
  if (!result) {
    ws.addRow(['Status', 'kein gespeichertes Vergleichsergebnis — Export zeigt nur Container-Metadaten / no saved comparison result — export shows container metadata only'])
    return
  }
  const matched = result.matchResult.filter((r) => r.kind === 'matched').length
  const added = result.matchResult.filter((r) => r.kind === 'unmatched_right').length
  const removed = result.matchResult.filter((r) => r.kind === 'unmatched_left').length
  const uncertain = result.matchResult.filter((r) => r.kind === 'ambiguous' || r.kind === 'split_suspected' || r.kind === 'merge_suspected').length
  ws.addRow(['Varianten gematcht / matched', matched])
  ws.addRow(['Varianten neu (nur NEU) / added (NEU only)', added])
  ws.addRow(['Varianten entfallen (nur ALT) / removed (ALT only)', removed])
  ws.addRow(['Unsichere Zuordnungen / uncertain matches', uncertain])

  const statusRow = ws.addRow(['Gesamtstatus / overall status', result.reviewRequired ? 'Review erforderlich / review required' : 'OK'])
  fillCell(statusRow, 2, result.reviewRequired ? FILL_WARN : FILL_FALL)
  if (result.reviewRequiredReasons.length === 0) {
    ws.addRow(['Review-Gründe / review reasons', '—'])
  } else {
    for (const reason of result.reviewRequiredReasons) {
      ws.addRow([`Review-Grund / reason [${reason}]`, blText(REVIEW_REASON_LABEL, reason)])
    }
  }

  blank(ws)
  subHeaderRow(ws, ['Vergleichbarkeit / Comparability', ''])
  const tf = result.containerDiff.template.family
  ws.addRow(['Template-Familie geändert / family changed', tf.changed ? 'ja / yes' : 'nein / no'])
  ws.addRow(['Struktur-Hash geändert / structural hash changed', tf.structuralHashChanged ? 'ja / yes' : 'nein / no'])
  ws.addRow(['Sheets hinzugefügt / sheets added', result.containerDiff.template.sheetSet.added.join(', ') || '—'])
  ws.addRow(['Sheets entfernt / sheets removed', result.containerDiff.template.sheetSet.removed.join(', ') || '—'])
  ws.addRow(['Dimensionen hinzugefügt / dimensions added', result.containerDiff.dimensions.added.join(', ') || '—'])
  ws.addRow(['Dimensionen entfernt / dimensions removed', result.containerDiff.dimensions.removed.join(', ') || '—'])
  ws.addRow([
    'Gedroppte manuelle Overrides / dropped manual overrides',
    result.droppedOverrides.length,
  ])
}

// ── (b) Varianten-Matching ──────────────────────────────────────────────────

function buildMatchingSheet(wb: Workbook, input: MultiQafExportInput): void {
  const ws = newSheet(wb, 'Varianten_Matching', [16, 14, 30, 14, 14, 30, 14, 22, 12, 14, 60, 60])
  const result = input.result
  const altById = variantById(input.altContainer)
  const neuById = variantById(input.neuContainer)

  headerRow(ws, [
    'Typ / type',
    'ALT-ID',
    'ALT-Label',
    'ALT-Status',
    'NEU-ID',
    'NEU-Label',
    'NEU-Status',
    'Stage/Methode',
    'Konfidenz',
    'Review-pflichtig',
    'Erklärung / explanation',
    'Evidenz / evidence',
  ])

  if (!result) {
    const row = ws.addRow(['—', '—', 'kein gespeichertes Vergleichsergebnis / no saved comparison result', '—', '—', '—', '—', '—', '—', '—', '—', '—'])
    fillCell(row, 1, FILL_NEUTRAL)
    return
  }
  if (result.matchResult.length === 0) {
    ws.addRow(['—', '—', 'keine Varianten in beiden Containern / no variants in either container', '—', '—', '—', '—', '—', '—', '—', '—', '—'])
  }

  for (const r of result.matchResult) {
    const typeLabel = blText(MATCH_KIND_LABEL, r.kind)
    if (r.kind === 'matched') {
      const altV = altById.get(r.leftId)
      const neuV = neuById.get(r.rightId)
      const row = ws.addRow([
        typeLabel,
        r.leftId,
        variantLabel(altV),
        blText(ACTIVE_STATE_LABEL, altV?.activeState),
        r.rightId,
        variantLabel(neuV),
        blText(ACTIVE_STATE_LABEL, neuV?.activeState),
        blText(MATCH_STAGE_LABEL, r.stage),
        r.confidence,
        'nein / no',
        r.explanation,
        r.evidence.map((e) => `${e.signal}: ${e.detail}`).join('; '),
      ])
      numFmt(row, 9, '0.0%')
    } else if (r.kind === 'unmatched_left') {
      const altV = altById.get(r.leftId)
      ws.addRow([typeLabel, r.leftId, variantLabel(altV), blText(ACTIVE_STATE_LABEL, altV?.activeState), '—', '—', '—', '—', '—', 'nein / no', 'entfallen (nur ALT) / removed (ALT only)', '—'])
    } else if (r.kind === 'unmatched_right') {
      const neuV = neuById.get(r.rightId)
      ws.addRow([typeLabel, '—', '—', '—', r.rightId, variantLabel(neuV), blText(ACTIVE_STATE_LABEL, neuV?.activeState), '—', '—', 'nein / no', 'neu (nur NEU) / added (NEU only)', '—'])
    } else {
      // ambiguous / split_suspected / merge_suspected — review-relevant,
      // uncertain. Each match kind has a DIFFERENT shape here (KAR-949
      // review Finding 1): 'ambiguous' carries plural leftIds/rightIds,
      // 'split_suspected' carries a SINGULAR leftId (one ALT variant
      // suspected split across several NEU candidates) plus plural
      // rightIds, and 'merge_suspected' carries plural leftIds plus a
      // SINGULAR rightId — see VariantSplitSuspectedResult /
      // VariantMergeSuspectedResult in ./variant-matcher.ts. A prior `'x' in
      // r` string check was false for the singular side of split/merge,
      // silently dropping exactly the variant identity a reviewer needs
      // (ALT-ID/Label for split_suspected, NEU-ID/Label for
      // merge_suspected) — explicit discriminated-union narrowing on
      // r.kind instead, so every kind's real field names are used.
      let leftIds: readonly string[]
      let rightIds: readonly string[]
      if (r.kind === 'ambiguous') {
        leftIds = r.leftIds
        rightIds = r.rightIds
      } else if (r.kind === 'split_suspected') {
        leftIds = [r.leftId]
        rightIds = r.rightIds
      } else {
        leftIds = r.leftIds
        rightIds = [r.rightId]
      }
      const row = ws.addRow([
        typeLabel,
        leftIds.join(', ') || '—',
        leftIds.map((id) => variantLabel(altById.get(id))).join(' / ') || '—',
        '—',
        rightIds.join(', ') || '—',
        rightIds.map((id) => variantLabel(neuById.get(id))).join(' / ') || '—',
        '—',
        '—',
        '—',
        'ja / yes',
        r.explanation,
        r.candidates.flatMap((c) => c.evidence.map((e) => `${e.signal}: ${e.detail}`)).join('; '),
      ])
      fillCell(row, 10, FILL_WARN)
    }
  }

  blank(ws)
  subHeaderRow(ws, ['Manuelle Varianten-Zuordnungs-Overrides (inkl. verworfen) / manual variant-match overrides (incl. dropped)', '', '', '', '', '', '', '', '', '', '', ''])
  headerRow(ws, ['Status', 'Entscheidung / decision', 'ALT-Schlüssel / key', 'ALT-Dimensionen', '', 'Notiz / note', '', 'Gesetzt von', 'Gesetzt am', 'In diesem Lauf verworfen', '', ''])
  if (input.persistedOverrides.length === 0) {
    ws.addRow(['—', 'keine manuellen Overrides / no manual overrides'])
  } else {
    const droppedThisRun = new Set((result.droppedOverrides ?? []).map((o) => JSON.stringify(o)))
    for (const o of input.persistedOverrides) {
      const status = o.status ?? 'active'
      const row = ws.addRow([
        blText(OVERRIDE_STATUS_LABEL, status),
        o.decision,
        o.left.compositeCanonicalKey,
        Object.entries(o.left.dimensions)
          .map(([k, v]) => `${k}=${v.raw}`)
          .join(', '),
        '',
        o.note ?? '—',
        '',
        o.setBy,
        o.setAt,
        droppedThisRun.has(JSON.stringify(o)) ? 'ja / yes' : 'nein / no',
      ])
      if (status !== 'active') fillCell(row, 1, FILL_WARN)
    }
  }
}

// ── (c) Summary-Kennzahlen je Variante (KAR-951-Diff) ───────────────────────

function buildSummaryKennzahlenSheet(wb: Workbook, input: MultiQafExportInput): void {
  const ws = newSheet(wb, 'Summary_Kennzahlen', [16, 30, 16, 30, 30, 30, 16, 16, 14, 14, 12, 14])
  headerRow(ws, [
    'ALT-Variante-ID',
    'ALT-Label',
    'NEU-Variante-ID',
    'NEU-Label',
    'Metrik (Code)',
    'Metrik (Klartext)',
    'Status (Code)',
    'Status (Klartext)',
    'ALT-Betrag',
    'NEU-Betrag',
    'Währung',
    'Delta abs',
  ])
  const result = input.result
  const altById = variantById(input.altContainer)
  const neuById = variantById(input.neuContainer)
  if (!result || !result.summaryTotalsDiff) {
    const row = ws.addRow(['—', 'kein Summary-Kennzahlen-Diff verfügbar (altes Ergebnis-Format oder kein Ergebnis) / no summary-metrics diff available (old result format or no result)'])
    fillCell(row, 1, FILL_NEUTRAL)
    return
  }
  const diff = result.summaryTotalsDiff
  const statePriority: Record<SummaryTotalsMetricState, number> = { changed: 0, unchanged: 1, nicht_ermittelbar: 2 }
  const findings = [...diff.findings].sort((a, b) => statePriority[a.state] - statePriority[b.state])
  for (const f of findings) {
    const altV = altById.get(f.altVariantId)
    const neuV = neuById.get(f.neuVariantId)
    const altM = money(f.altAmount)
    const neuM = money(f.neuAmount)
    const row = ws.addRow([
      f.altVariantId,
      variantLabel(altV),
      f.neuVariantId,
      variantLabel(neuV),
      f.metricKey,
      blText(SUMMARY_TOTALS_METRIC_LABEL, f.metricKey),
      f.state,
      blText(DIFF_STATUS_LABEL, f.state),
      altM.value,
      neuM.value,
      neuM.currency ?? altM.currency ?? '—',
      f.deltaAbsolute,
    ])
    if (altM.value !== null) numFmt(row, 9, '#,##0.00')
    if (neuM.value !== null) numFmt(row, 10, '#,##0.00')
    if (f.deltaAbsolute !== null) numFmt(row, 12, '#,##0.00')
    const fill = statusBandFill(f.state)
    if (fill) fillCell(row, 8, fill)
    if (f.currencyChanged) fillCell(row, 11, FILL_WARN)
  }

  blank(ws)
  subHeaderRow(ws, ['Materialkosten je Währung (Buckets) / material costs by currency', '', '', '', '', '', '', '', '', '', '', ''])
  headerRow(ws, ['ALT-Variante-ID', '', 'NEU-Variante-ID', '', 'Währung', 'Status (Code)', 'Status (Klartext)', 'ALT-Wert', 'NEU-Wert', 'Delta abs', '', ''])
  if (diff.materialCostsByCurrencyFindings.length === 0) {
    ws.addRow(['—', '', '—', '', 'keine Bucket-Funde / no bucket findings'])
  } else {
    for (const f of diff.materialCostsByCurrencyFindings) {
      const row = ws.addRow([
        f.altVariantId,
        '',
        f.neuVariantId,
        '',
        f.currency ?? '—',
        f.state,
        blText(DIFF_STATUS_LABEL, f.state),
        f.altValue,
        f.neuValue,
        f.deltaAbsolute,
      ])
      const fill = statusBandFill(f.state)
      if (fill) fillCell(row, 7, fill)
    }
  }

  blank(ws)
  subHeaderRow(ws, ['Summen geänderter Kennzahlen je Währung (informativ, KEIN Aggregat-Impact) / sums of changed metrics by currency (informational, NOT the aggregate impact)', '', '', '', '', '', '', '', '', '', '', ''])
  headerRow(ws, ['Währung', 'Summe Delta abs', 'Anzahl Funde', '', '', '', '', '', '', '', '', ''])
  if (diff.changedMetricSumsByCurrency.length === 0) {
    ws.addRow(['—', 'keine geänderten Kennzahlen / no changed metrics'])
  } else {
    for (const s of diff.changedMetricSumsByCurrency) {
      const row = ws.addRow([s.currency, s.totalDeltaAbsolute, s.metricFindingCount])
      numFmt(row, 2, '#,##0.00')
      numFmt(row, 3, '#,##0')
    }
  }
}

// ── (d) Material-Diff ────────────────────────────────────────────────────

function buildMaterialDiffSheet(wb: Workbook, input: MultiQafExportInput): void {
  const ws = newSheet(wb, 'Material_Diff', [24, 20, 16, 16, 14, 14, 14, 30, 30, 40, 50])
  headerRow(ws, [
    'Änderungstyp / change type',
    'Komponente',
    'ALT-Wert',
    'NEU-Wert',
    'Delta abs',
    'Delta %',
    'Status (Code)',
    'Status (Klartext)',
    'Review-relevant',
    'Betroffene Varianten',
    'Impact je Währung',
  ])
  const result = input.result
  if (!result) {
    const row = ws.addRow(['—', 'kein gespeichertes Vergleichsergebnis / no saved comparison result'])
    fillCell(row, 1, FILL_NEUTRAL)
    return
  }
  const md = result.materialDiff
  const impactCols = (impact: { affectedVariantIds: readonly string[]; aggregates: readonly { currency: string; totalImpact: number }[] }) => [
    impact.affectedVariantIds.join(', ') || '—',
    impact.aggregates.map((a) => `${a.currency}: ${a.totalImpact.toFixed(2)}`).join('; ') || '—',
  ]
  let any = false
  for (const f of md.sharedComponents.unitCostValueChanges) {
    any = true
    const row = ws.addRow([
      'Einheitspreis geändert / unit cost value changed',
      f.canonicalComponentIdentity,
      f.altValue,
      f.neuValue,
      f.deltaAbsolute,
      f.deltaPercent,
      f.status,
      blText(DIFF_STATUS_LABEL, f.status),
      f.reviewRelevant ? 'ja / yes' : 'nein / no',
      ...impactCols(f.impact),
    ])
    if (f.altValue !== null) numFmt(row, 3, '#,##0.00')
    if (f.neuValue !== null) numFmt(row, 4, '#,##0.00')
    if (f.deltaAbsolute !== null) numFmt(row, 5, '#,##0.00')
    if (f.deltaPercent !== null) numFmt(row, 6, '0.0%')
    const fill = statusBandFill(f.status)
    if (fill) fillCell(row, 7, fill)
  }
  for (const f of md.sharedComponents.unitCostCurrencyChanges) {
    any = true
    const row = ws.addRow([
      'Einheitspreis-Währung geändert / unit cost currency changed',
      f.canonicalComponentIdentity,
      f.altCurrency ?? '—',
      f.neuCurrency ?? '—',
      null,
      null,
      '—',
      '—',
      f.reviewRelevant ? 'ja / yes' : 'nein / no',
      ...impactCols(f.impact),
    ])
    fillCell(row, 1, FILL_WARN)
  }
  for (const f of md.sharedComponents.exchangeRateChanges) {
    any = true
    const row = ws.addRow([
      'Wechselkurs geändert / exchange rate changed',
      f.canonicalComponentIdentity,
      f.altValue,
      f.neuValue,
      f.deltaAbsolute,
      f.deltaPercent,
      f.status,
      blText(DIFF_STATUS_LABEL, f.status),
      f.reviewRelevant ? 'ja / yes' : 'nein / no',
      ...impactCols(f.impact),
    ])
    if (f.altValue !== null) numFmt(row, 3, '#,##0.0000')
    if (f.neuValue !== null) numFmt(row, 4, '#,##0.0000')
    if (f.deltaAbsolute !== null) numFmt(row, 5, '#,##0.0000')
    if (f.deltaPercent !== null) numFmt(row, 6, '0.0%')
  }
  for (const f of [...md.sharedComponents.logisticsOrDutyChanges, ...md.sharedComponents.materialOverheadChanges]) {
    any = true
    const altM = money(f.altAmount)
    const neuM = money(f.neuAmount)
    const row = ws.addRow([
      f.field === 'logistics_or_duty' ? 'Logistik/Zoll geändert / logistics-or-duty changed' : 'Material-Gemeinkosten geändert / material overhead changed',
      f.canonicalComponentIdentity,
      altM.value,
      neuM.value,
      f.deltaAbsolute,
      f.deltaPercent,
      f.status ?? '—',
      f.status ? blText(DIFF_STATUS_LABEL, f.status) : '—',
      f.reviewRelevant ? 'ja / yes' : 'nein / no',
      ...impactCols(f.impact),
    ])
    if (altM.value !== null) numFmt(row, 3, '#,##0.00')
    if (neuM.value !== null) numFmt(row, 4, '#,##0.00')
    if (f.deltaAbsolute !== null) numFmt(row, 5, '#,##0.00')
    if (f.deltaPercent !== null) numFmt(row, 6, '0.0%')
  }
  for (const f of md.sharedComponents.formulaChanges) {
    any = true
    ws.addRow([
      `Formel geändert (${f.comparisonKind}) / formula changed`,
      f.canonicalComponentIdentity,
      null,
      null,
      null,
      null,
      f.comparisonKind,
      f.explanationEn ? `${f.explanation} / ${f.explanationEn}` : f.explanation,
      f.reviewRelevant ? 'ja / yes' : 'nein / no',
      ...impactCols(f.impact),
    ])
  }
  for (const f of md.sharedComponents.rowIdentityChanges) {
    any = true
    ws.addRow([
      'Zeilen-Identität geändert / row identity changed',
      `${f.altCanonicalComponentIdentity} → ${f.neuCanonicalComponentIdentity}`,
      f.altLabel,
      f.neuLabel,
      null,
      null,
      '—',
      f.position,
      f.reviewRelevant ? 'ja / yes' : 'nein / no',
      '—',
      '—',
    ])
  }
  if (!any) ws.addRow(['—', 'keine geteilten Komponenten-Änderungen / no shared-component changes'])

  blank(ws)
  subHeaderRow(ws, ['Varianten-Allokation je Variante / per-variant allocation', '', '', '', '', '', '', '', '', '', ''])
  headerRow(ws, ['Änderungsart / kind', 'Komponente', 'ALT-Faktor', 'NEU-Faktor', 'ALT-Kosten', 'NEU-Kosten', 'Delta (Code)', 'Delta (Klartext)', 'Review-relevant', 'ALT-Variante', 'NEU-Variante'])
  if (md.variantAllocation.findings.length === 0) {
    ws.addRow(['—', 'keine Allokations-Änderungen / no allocation changes'])
  } else {
    for (const f of md.variantAllocation.findings) {
      const row = ws.addRow([
        f.kind,
        f.canonicalComponentIdentity,
        f.altFactor,
        f.neuFactor,
        f.altEffectiveCost,
        f.neuEffectiveCost,
        f.effectiveCostStatus,
        f.effectiveCostDeltaReason ? blText(DIFF_STATUS_LABEL, f.effectiveCostStatus) + ` (${f.effectiveCostDeltaReason})` : blText(DIFF_STATUS_LABEL, f.effectiveCostStatus),
        f.reviewRelevant ? 'ja / yes' : 'nein / no',
        f.altVariantId,
        f.neuVariantId,
      ])
      const fill = statusBandFill(f.effectiveCostStatus)
      if (fill) fillCell(row, 7, fill)
    }
  }

  blank(ws)
  subHeaderRow(ws, ['Substitutionsverdacht / substitution suspected', '', '', '', '', '', '', '', '', '', ''])
  headerRow(ws, ['Entfernte Komponente', 'Kandidaten (Labels)', 'Label-Ähnlichkeit', 'Betroffene Varianten-Paare', '', '', '', '', '', '', ''])
  if (md.variantAllocation.substitutionsSuspected.length === 0) {
    ws.addRow(['—', 'keine Substitutionsverdachte / no substitutions suspected'])
  } else {
    for (const s of md.variantAllocation.substitutionsSuspected) {
      const row = ws.addRow([
        s.removed.canonicalComponentIdentity,
        s.addedCandidates.map((c) => c.canonicalComponentIdentity).join(', '),
        s.labelSimilarity,
        s.affectedVariantPairs.map((p) => `${p.altVariantId}↔${p.neuVariantId}`).join(', '),
      ])
      numFmt(row, 3, '0.0%')
      fillCell(row, 1, FILL_WARN)
    }
  }

  blank(ws)
  subHeaderRow(ws, ['Struktur-Änderungen (Material-Matrix) / structural changes', '', '', '', '', '', '', '', '', '', ''])
  const sm = result.containerDiff.sharedMaterial
  ws.addRow(['ALT-Zeilen', sm.structure.altRowCount, 'NEU-Zeilen', sm.structure.neuRowCount, 'Hinzugefügte Zeilen', sm.addedRows.length, 'Entfernte Zeilen', sm.removedRows.length, 'Geänderte Zeilen', sm.changedRows.length])
  headerRow(ws, ['Kategorie', 'Komponente', 'ALT-Validierung', 'NEU-Validierung', 'Validierung geändert', '', '', '', '', '', ''])
  for (const r of sm.addedRows) ws.addRow(['hinzugefügt / added', r.canonicalComponentIdentity])
  for (const r of sm.removedRows) ws.addRow(['entfernt / removed', r.canonicalComponentIdentity])
  for (const r of sm.changedRows)
    ws.addRow(['geändert / changed', r.canonicalComponentIdentity, r.altValidationStatus, r.neuValidationStatus, r.validationStatusChanged ? 'ja / yes' : 'nein / no'])
  if (sm.addedRows.length === 0 && sm.removedRows.length === 0 && sm.changedRows.length === 0) ws.addRow(['—', 'keine Struktur-Änderungen / no structural changes'])
}

// ── (e) Fertigungsprofile ─────────────────────────────────────────────────

function buildProfilesSheet(wb: Workbook, input: MultiQafExportInput): void {
  const ws = newSheet(wb, 'Fertigungsprofile', [26, 24, 24, 22, 16, 16, 14, 14, 14, 40])
  headerRow(ws, ['Kategorie / category', 'ALT-Profil', 'NEU-Profil', 'Feld/Bucket', 'ALT-Wert', 'NEU-Wert', 'Delta abs', 'Status (Code)', 'Status (Klartext)', 'Hinweis / note'])
  const result = input.result
  if (!result) {
    const row = ws.addRow(['—', 'kein gespeichertes Vergleichsergebnis / no saved comparison result'])
    fillCell(row, 1, FILL_NEUTRAL)
    return
  }
  const pd = result.profileDiff
  let any = false
  for (const f of pd.componentValueChanges) {
    any = true
    for (const c of f.changes) {
      const row = ws.addRow([
        'Komponentenwert geändert / component value changed',
        f.alt.label ?? f.alt.profileId,
        f.neu.label ?? f.neu.profileId,
        c.key,
        typeof c.altValue === 'number' ? c.altValue : c.altValue,
        typeof c.neuValue === 'number' ? c.neuValue : c.neuValue,
        c.deltaAbsolute,
        c.status,
        blText(DIFF_STATUS_LABEL, c.status),
        '—',
      ])
      if (typeof c.altValue === 'number') numFmt(row, 5, '#,##0.00')
      if (typeof c.neuValue === 'number') numFmt(row, 6, '#,##0.00')
      if (c.deltaAbsolute !== null) numFmt(row, 7, '#,##0.00')
      const fill = statusBandFill(c.status)
      if (fill) fillCell(row, 8, fill)
    }
  }
  for (const f of pd.totalChanges) {
    any = true
    const row = ws.addRow([
      'Profil-Total geändert / profile total changed',
      f.alt.label ?? f.alt.profileId,
      f.neu.label ?? f.neu.profileId,
      'Total',
      f.altTotal,
      f.neuTotal,
      f.deltaAbsolute,
      f.status,
      blText(DIFF_STATUS_LABEL, f.status),
      `betroffene Varianten / affected variants: ${f.affectedVariantIds.join(', ') || '—'}`,
    ])
    if (f.altTotal !== null) numFmt(row, 5, '#,##0.00')
    if (f.neuTotal !== null) numFmt(row, 6, '#,##0.00')
    if (f.deltaAbsolute !== null) numFmt(row, 7, '#,##0.00')
    const fill = statusBandFill(f.status)
    if (fill) fillCell(row, 8, fill)
  }
  for (const f of pd.bindingValueImpacts) {
    any = true
    const row = ws.addRow([
      `Bindungs-Wert-Impact (${f.bucket}) / binding value impact`,
      f.altProfile?.label ?? f.altProfile?.profileId ?? '—',
      f.neuProfile?.label ?? f.neuProfile?.profileId ?? '—',
      f.direction,
      f.altValue,
      f.neuValue,
      f.deltaAbsolute,
      f.status ?? '—',
      f.status ? blText(DIFF_STATUS_LABEL, f.status) : blText(DIFF_STATUS_LABEL, f.currencyState),
      `Variante ${f.neu.variantId} / variant`,
    ])
    if (f.altValue !== null) numFmt(row, 5, '#,##0.00')
    if (f.neuValue !== null) numFmt(row, 6, '#,##0.00')
    if (f.deltaAbsolute !== null) numFmt(row, 7, '#,##0.00')
    if (f.currencyState === 'currency_changed') fillCell(row, 9, FILL_WARN)
    if (f.currencyState === 'currency_unknown') fillCell(row, 9, FILL_NEUTRAL)
  }
  for (const p of pd.added) {
    any = true
    ws.addRow(['Profil hinzugefügt / profile added', '—', p.label ?? p.profileId, p.kind, null, null, null, 'neu', blText(DIFF_STATUS_LABEL, 'neu'), '—'])
  }
  for (const p of pd.removed) {
    any = true
    ws.addRow(['Profil entfernt / profile removed', p.label ?? p.profileId, '—', p.kind, null, null, null, 'entfallen', blText(DIFF_STATUS_LABEL, 'entfallen'), '—'])
  }
  if (!any) ws.addRow(['—', 'keine Profil-Änderungen / no profile changes'])

  blank(ws)
  subHeaderRow(ws, ['Inkonsistente Bindungen / inconsistent bindings', '', '', '', '', '', '', '', '', ''])
  headerRow(ws, ['Änderung / change', 'Variante', 'Grund (Code)', 'Grund (Klartext)', 'Profil-ID', 'Nachricht', '', '', '', ''])
  const ib = pd.inconsistentBindings
  if (ib.added.length === 0 && ib.removed.length === 0) {
    ws.addRow(['—', 'keine inkonsistenten Bindungen / no inconsistent bindings'])
  } else {
    for (const e of ib.added) ws.addRow(['neu / added', e.variantId, e.reason, blText(DIFF_STATUS_LABEL, e.reason), e.profileId ?? '—', e.message])
    for (const e of ib.removed) ws.addRow(['behoben / resolved', e.variantId, e.reason, blText(DIFF_STATUS_LABEL, e.reason), e.profileId ?? '—', e.message])
  }

  blank(ws)
  subHeaderRow(ws, ['Summary-Rekonziliation je Profil-Bindung / summary reconciliation per binding (nicht_pruefbar ausgewiesen)', '', '', '', '', '', '', '', '', ''])
  headerRow(ws, ['Änderung / change', 'Variante', 'Profil-ID', 'Bucket', 'Status (Code)', 'Status (Klartext)', 'NichtPrüfbar-Grund', 'Delta abs', '', ''])
  const sr = pd.summaryReconciliation
  if (sr.added.length === 0 && sr.removed.length === 0 && sr.changed.length === 0) {
    ws.addRow(['—', 'keine Rekonziliations-Einträge / no reconciliation entries'])
  } else {
    for (const e of sr.added) {
      const row = ws.addRow([
        'neu / added',
        e.variantId,
        e.profileId,
        e.bucket,
        e.status,
        blText(RECONCILIATION_STATUS_LABEL, e.status),
        e.nichtPruefbarReason ? blText(NICHT_PRUEFBAR_REASON_LABEL, e.nichtPruefbarReason) : '—',
        e.deltaAbsolute,
      ])
      const fill = statusBandFill(e.status)
      if (fill) fillCell(row, 5, fill)
    }
    for (const e of sr.removed) {
      const row = ws.addRow([
        'entfernt / removed',
        e.variantId,
        e.profileId,
        e.bucket,
        e.status,
        blText(RECONCILIATION_STATUS_LABEL, e.status),
        e.nichtPruefbarReason ? blText(NICHT_PRUEFBAR_REASON_LABEL, e.nichtPruefbarReason) : '—',
        e.deltaAbsolute,
      ])
      const fill = statusBandFill(e.status)
      if (fill) fillCell(row, 5, fill)
    }
    for (const e of sr.changed) {
      const row = ws.addRow(['geändert / changed', e.variantId, e.profileId, e.bucket, e.status, blText(RECONCILIATION_STATUS_LABEL, e.status), '—', e.neuDeltaAbsolute])
      const fill = statusBandFill(e.status)
      if (fill) fillCell(row, 5, fill)
    }
  }
}

// ── (f) Rekonziliation je Variante ──────────────────────────────────────────

function buildReconciliationSheet(wb: Workbook, input: MultiQafExportInput): void {
  const ws = newSheet(wb, 'Rekonziliation', [10, 16, 26, 26, 14, 22, 26, 14, 14, 14, 45, 45])
  headerRow(ws, [
    'Seite / side',
    'Variante',
    'Check (Code)',
    'Check (Klartext)',
    'Status (Code)',
    'Status (Klartext)',
    'NichtPrüfbar-Grund',
    'Erwartet',
    'Tatsächlich',
    'Delta abs',
    'Nachricht (DE)',
    'Nachricht (EN)',
  ])
  const result = input.result
  if (!result) {
    const row = ws.addRow(['—', 'kein gespeichertes Vergleichsergebnis / no saved comparison result'])
    fillCell(row, 1, FILL_NEUTRAL)
    return
  }
  const writeSide = (side: 'ALT' | 'NEU', results: readonly VariantReconciliationResult[]) => {
    for (const vr of results) {
      for (const c of vr.checks) {
        const row = ws.addRow([
          side,
          vr.variantId,
          c.checkId,
          blText(RECONCILIATION_CHECK_LABEL, c.checkId),
          c.status,
          blText(RECONCILIATION_STATUS_LABEL, c.status),
          c.nichtPruefbarReason ? blText(NICHT_PRUEFBAR_REASON_LABEL, c.nichtPruefbarReason) : '—',
          c.expected,
          c.actual,
          c.deltaAbsolute,
          c.messageDe ?? '—',
          c.messageEn ?? '—',
        ])
        if (typeof c.expected === 'number') numFmt(row, 8, '#,##0.00')
        if (typeof c.actual === 'number') numFmt(row, 9, '#,##0.00')
        if (c.deltaAbsolute !== null) numFmt(row, 10, '#,##0.00')
        const fill = statusBandFill(c.status)
        if (fill) fillCell(row, 5, fill)
      }
    }
  }
  writeSide('ALT', result.reconciliation.alt)
  writeSide('NEU', result.reconciliation.neu)
  if (result.reconciliation.alt.length === 0 && result.reconciliation.neu.length === 0) {
    ws.addRow(['—', 'keine Rekonziliations-Ergebnisse / no reconciliation results'])
  }
}

// ── (g) Aggregat-Impact — NUR mit Gate-Kontext ──────────────────────────────

function buildAggregateSheet(wb: Workbook, input: MultiQafExportInput): void {
  const ws = newSheet(wb, 'Aggregat_Impact', [30, 12, 45, 45, 40])
  const result = input.result
  titleRow(ws, 'Aggregat-Impact — NUR mit Gate-Kontext / Aggregate impact — WITH gate context only')
  blank(ws)

  if (!result) {
    ws.addRow(['kein gespeichertes Vergleichsergebnis / no saved comparison result'])
    return
  }
  const agg = result.aggregateImpact
  if (!agg) {
    const row = ws.addRow(['Aggregat nicht verfügbar — altes Ergebnis-Format (vor KAR-944) / aggregate not available — old result format (pre-KAR-944)'])
    fillCell(row, 1, FILL_NEUTRAL)
    return
  }

  subHeaderRow(ws, ['Gates', '', '', '', ''])
  headerRow(ws, ['Gate (Code)', 'Bestanden', 'Nachricht (DE)', 'Nachricht (EN)', 'Betroffene Varianten'])
  for (const g of agg.gates) {
    const row = ws.addRow([`${blText(GATE_LABEL, g.gate)} [${g.gate}]`, g.passed ? 'ja / yes' : 'nein / no', g.messageDe, g.messageEn, g.affectedVariantIds.join(', ') || '—'])
    fillCell(row, 2, g.passed ? FILL_FALL : FILL_RISE)
  }

  blank(ws)
  if (agg.gatesFailed.length > 0) {
    const row = ws.addRow([
      `Aggregat durch ${agg.gatesFailed.length} Gate(s) blockiert / aggregate blocked by ${agg.gatesFailed.length} gate(s): ${agg.gatesFailed.map((g) => blText(GATE_LABEL, g)).join('; ')}`,
    ])
    fillCell(row, 1, FILL_RISE)
    blank(ws)
  }

  subHeaderRow(ws, ['Ausgeschlossene Varianten / excluded variants', '', '', '', ''])
  headerRow(ws, ['Variante', 'Grund (Code)', 'Grund (Klartext)', 'Nachricht (DE)', 'Nachricht (EN)'])
  if (agg.excludedVariants.length === 0) {
    ws.addRow(['—', 'keine ausgeschlossenen Varianten / no excluded variants'])
  } else {
    for (const e of agg.excludedVariants) ws.addRow([e.variantId, e.reason, blText(EXCLUSION_REASON_LABEL, e.reason), e.messageDe, e.messageEn])
  }

  blank(ws)
  subHeaderRow(ws, ['Annahmen / assumptions', '', '', '', ''])
  headerRow(ws, ['Code', 'Nachricht (DE)', 'Nachricht (EN)', '', ''])
  if (agg.assumptions.length === 0) {
    ws.addRow(['—', 'keine Annahmen / no assumptions'])
  } else {
    for (const a of agg.assumptions) ws.addRow([a.code, a.messageDe, a.messageEn])
  }

  const writeTimeframe = (label: string, tf: typeof agg.annual) => {
    blank(ws)
    subHeaderRow(ws, [`${label} — Population: ${tf.population.length} Variante(n) / variants`, '', '', '', ''])
    headerRow(ws, ['Währung', 'Summe Impact', 'Anzahl Varianten', '', ''])
    if (tf.aggregate.length === 0) {
      const row = ws.addRow(['—', 'kein Aggregat berechnet (siehe Gates oben) / no aggregate computed (see gates above)'])
      fillCell(row, 1, FILL_NEUTRAL)
    } else {
      for (const c of tf.aggregate) {
        const row = ws.addRow([c.currency, c.totalImpact, c.variantCount])
        numFmt(row, 2, '#,##0.00')
        numFmt(row, 3, '#,##0')
      }
    }
  }
  writeTimeframe('Jahres-Impact / annual impact', agg.annual)
  writeTimeframe('Lifetime-Impact / lifetime impact', agg.lifetime)

  blank(ws)
  subHeaderRow(ws, ['Unweighted Struktur-Vergleich (immer verfügbar, unabhängig von Gates) / structural comparison (always available)', '', '', '', ''])
  const sf = agg.structuralFallback
  headerRow(ws, ['Kennzahl', 'Wert', '', '', ''])
  const sfEntries: [string, number][] = [
    ['Varianten hinzugefügt / added', sf.variantsAdded],
    ['Varianten entfernt / removed', sf.variantsRemoved],
    ['Varianten umbenannt / renamed', sf.variantsRenamed],
    ['Varianten umsortiert / reordered', sf.variantsReordered],
    ['Aktiv-Status geändert / active-state changed', sf.variantsActiveStateChanged],
    ['Unsichere Zuordnungen / uncertain matches', sf.uncertainMatches],
    ['Material-Zeilen hinzugefügt / rows added', sf.materialRowsAdded],
    ['Material-Zeilen entfernt / rows removed', sf.materialRowsRemoved],
    ['Material-Zeilen geändert / rows changed', sf.materialRowsChanged],
    ['Profile hinzugefügt / added', sf.profilesAdded],
    ['Profile entfernt / removed', sf.profilesRemoved],
    ['Volumen-Band-Schwellen geändert / threshold changes', sf.volumeBandThresholdChanges],
    ['Profil-Bindungs-Änderungen / binding changes', sf.profileBindingChanges],
  ]
  for (const [label, value] of sfEntries) {
    const row = ws.addRow([label, value])
    numFmt(row, 2, '#,##0')
  }
}

// ── Multi-QAF export entrypoint ─────────────────────────────────────────────

export interface MultiQafExportInput {
  generatedAtLabel: string
  altFileName: string | null
  neuFileName: string | null
  altContainer: MultiQafContainer | null
  neuContainer: MultiQafContainer | null
  result: MultiQafComparisonResult | null
  persistedOverrides: readonly VariantMatchOverride[]
}

/** Builds the 7-sheet Multi-QAF comparison export (task spec (a)-(g)). Tolerant
 * of `null` container/result — every sheet builder above emits an honest hint
 * row instead of throwing (task requirement 5, "Alt-Results ohne neue Felder
 * tolerant exportieren"). */
export async function buildMultiQafExportWorkbook(input: MultiQafExportInput): Promise<ArrayBuffer> {
  const ExcelJS = (await import('exceljs')).default
  const wb = new ExcelJS.Workbook()
  wb.creator = 'KADi SupplierPulse — Multi-QAF-Vergleich'
  buildOverviewSheet(wb, input)
  buildMatchingSheet(wb, input)
  buildSummaryKennzahlenSheet(wb, input)
  buildMaterialDiffSheet(wb, input)
  buildProfilesSheet(wb, input)
  buildReconciliationSheet(wb, input)
  buildAggregateSheet(wb, input)
  return wb.xlsx.writeBuffer()
}

// ── Variante ↔ Standard export ──────────────────────────────────────────────

export interface VariantVsStandardExportInput {
  generatedAtLabel: string
  multiQafFileName: string | null
  standardFileName: string | null
  container: MultiQafContainer | null
  variantId: string | null
  result: VariantVsStandardComparisonResult | null
}

function buildVvsOverviewSheet(wb: Workbook, input: VariantVsStandardExportInput): void {
  const ws = newSheet(wb, 'Uebersicht', [40, 60])
  titleRow(ws, 'Multi-QAF-Variante ↔ Standard-QAF — Übersicht / Overview')
  ws.addRow(['Erstellt / Generated', input.generatedAtLabel])
  ws.addRow(['Multi-QAF-Container-Datei / container file', input.multiQafFileName ?? '—'])
  ws.addRow(['Standard-QAF-Datei / standard file', input.standardFileName ?? '—'])
  const variant = input.variantId ? (variantById(input.container).get(input.variantId) ?? null) : null
  ws.addRow(['Verglichene Variante / compared variant', variant ? `${variantLabel(variant)} (${input.variantId})` : (input.variantId ?? '—')])
  if (variant) {
    ws.addRow(['Dimensionen der Variante / variant dimensions', Object.entries(variant.dimensions).map(([k, v]) => `${k}=${v.raw}`).join(', ') || '—'])
  }
  blank(ws)
  if (!input.result) {
    ws.addRow(['Status', 'kein gespeichertes Vergleichsergebnis / no saved comparison result'])
    return
  }
  const statusRow = ws.addRow(['Gesamtstatus / overall status', input.result.reviewRequired ? 'Review erforderlich / review required' : 'OK'])
  fillCell(statusRow, 2, input.result.reviewRequired ? FILL_WARN : FILL_FALL)
  if (input.result.reviewRequiredReasons.length === 0) {
    ws.addRow(['Review-Gründe / review reasons', '—'])
  } else {
    for (const reason of input.result.reviewRequiredReasons) {
      ws.addRow([`Review-Grund [${reason}]`, blText(REVIEW_REQUIRED_REASON_LABEL, reason)])
    }
  }
  ws.addRow(['Real durchgeführte Module / genuinely-run modules', 'Summary-Identität + SummaryMetrics-Diff (2 von 14) / Summary identity + SummaryMetrics diff (2 of 14)'])
  ws.addRow(['Strukturell nicht verfügbare Module / structurally unavailable modules', `${input.result.degradedModules.length} — siehe Sheet "Nicht_Verfuegbar" / see sheet "Nicht_Verfuegbar"`])
}

function buildVvsIdentitySheet(wb: Workbook, input: VariantVsStandardExportInput): void {
  const ws = newSheet(wb, 'Summary_Identitaet', [24, 12, 20, 60, 60])
  headerRow(ws, ['Typ / type', 'Severity (Code)', 'Severity (Klartext)', 'Erklärung (DE)', 'Erklärung (EN)'])
  if (!input.result) {
    const row = ws.addRow(['—', '—', 'kein gespeichertes Vergleichsergebnis / no saved comparison result'])
    fillCell(row, 1, FILL_NEUTRAL)
    return
  }
  if (input.result.summaryIdentityIssues.length === 0) {
    ws.addRow(['—', '—', '—', 'keine Identitäts-Befunde / no identity findings', '—'])
    return
  }
  for (const i of input.result.summaryIdentityIssues) {
    const row = ws.addRow([i.type, i.severity, blText(SEVERITY_LABEL, i.severity), i.explanation, i.explanationEn ?? '—'])
    const fill = i.severity === 'kritisch' ? FILL_RISE : i.severity === 'pruefen' ? FILL_WARN : null
    if (fill) fillCell(row, 2, fill)
  }
}

function buildVvsMetricsSheet(wb: Workbook, input: VariantVsStandardExportInput): void {
  const ws = newSheet(wb, 'Summary_Kennzahlen_Diff', [24, 24, 16, 16, 14, 14, 12])
  headerRow(ws, ['Metrik (Code)', 'Metrik (Klartext)', 'Standard-Wert (ALT)', 'Varianten-Wert (NEU)', 'Delta abs', 'Status (Code)', 'Status (Klartext)'])
  if (!input.result) {
    const row = ws.addRow(['—', 'kein gespeichertes Vergleichsergebnis / no saved comparison result'])
    fillCell(row, 1, FILL_NEUTRAL)
    return
  }
  if (input.result.summaryMetricsDiff.length === 0) {
    ws.addRow(['—', 'keine SummaryMetrics-Diffs / no SummaryMetrics diffs'])
    return
  }
  const metricLabel = (k: SummaryMetricKey): string => METRIC_LABELS_DE[k] ?? k
  for (const d of input.result.summaryMetricsDiff) {
    const row = ws.addRow([d.metricKey, metricLabel(d.metricKey), d.altValue, d.neuValue, d.deltaAbsolute, d.status, blText(DIFF_STATUS_LABEL, d.status)])
    if (d.altValue !== null) numFmt(row, 3, '#,##0.00')
    if (d.neuValue !== null) numFmt(row, 4, '#,##0.00')
    if (d.deltaAbsolute !== null) numFmt(row, 5, '#,##0.00')
    const fill = statusBandFill(d.status)
    if (fill) fillCell(row, 6, fill)
  }
}

function buildVvsDegradedSheet(wb: Workbook, input: VariantVsStandardExportInput): void {
  const ws = newSheet(wb, 'Nicht_Verfuegbar', [24, 30, 60, 60])
  titleRow(ws, 'Für Varianten-Vergleiche nicht verfügbar / Not available for variant comparisons')
  ws.addRow(['Diese 12 Module benötigen Fertigungskosten-Prozesszeilen, die eine Multi-QAF-Variante strukturell nicht hat — NICHTS hiervon bedeutet "keine Unterschiede", sondern "nicht geprüft". / These 12 modules need manufacturing-cost process rows, which a Multi-QAF variant structurally does not have — NONE of this means "no differences", it means "not checked".'])
  blank(ws)
  headerRow(ws, ['Modul (Code)', 'Modul (Klartext)', 'Grund (DE)', 'Grund (EN)'])
  const modules = input.result?.degradedModules ?? []
  if (modules.length === 0) {
    ws.addRow(['—', 'keine Angaben (kein gespeichertes Ergebnis) / no data (no saved result)'])
  } else {
    for (const m of modules) {
      const row = ws.addRow([m.moduleKey, blText(DEGRADED_MODULE_KEY_LABEL, m.moduleKey), m.messageDe, m.messageEn])
      fillCell(row, 1, FILL_NEUTRAL)
    }
  }
}

/** Builds the 4-sheet Variante↔Standard export (task spec: Übersicht, the 2
 * real modules, the 12 degraded modules as their own honest block). Tolerant
 * of `null` container/result, same discipline as the Multi-QAF builder. */
export async function buildVariantVsStandardExportWorkbook(input: VariantVsStandardExportInput): Promise<ArrayBuffer> {
  const ExcelJS = (await import('exceljs')).default
  const wb = new ExcelJS.Workbook()
  wb.creator = 'KADi SupplierPulse — Multi-QAF-Variante ↔ Standard-QAF'
  buildVvsOverviewSheet(wb, input)
  buildVvsIdentitySheet(wb, input)
  buildVvsMetricsSheet(wb, input)
  buildVvsDegradedSheet(wb, input)
  return wb.xlsx.writeBuffer()
}