// CARBON FOOTPRINT (CO2e) sheet parser + Emissionen-Material-Formel-
// Nachrechnung (KAR-904 / P2.5 — letztes Phase-2-Item). Built from the
// logistics-parser.ts/lccn-parser.ts pattern (KAR-903/P2.4, KAR-904/P2.5):
// dynamic import of the canonical registry, label-anchor + confidence
// matching, a controlled degradation path on an unusable sheet (never
// throws), sourceCells/normalized/rawText provenance from day one, and the
// coreFieldsFound tri-state + ForReconciliation/FromPersistedMeta helper pair
// built in from the START (the #274/KAR-898 lesson every P2.x module since
// has shipped on commit one instead of retrofitting).
//
// ── Struktur-Entscheidung: ZWEI Extraktions-Formen auf DEMSELBEN Blatt ─────
// [50]s eigene Reiter-Aufzaehlung ist explizit: "Kopfzeile mit Typauswahl
// Standard/LC-CN/CO2e (Checkbox), darunter Reiter SUMMARY/LC-CN/MATERIAL/
// MANUFACTURING COSTS" — KEIN eigenstaendiger "CO2e"-Tab wird hier genannt.
// Gleichzeitig traegt Abbildung 34s eigene Bildunterschrift den Namen
// "CO2e-Zusammenfassungsblatt in der QAF" (S.53) UND [53]s Beschreibung legt
// die PCF-Kernfelder als rechtes Panel ("CO2e CONTENT") NEBEN dem
// Standard-Zusammenfassungsblatt an, waehrend [50] separat sagt die
// CO2e-Gruppierung wird zusaetzlich auf MATERIAL/MANUFACTURING COSTS
// eingeblendet. Das ist ein echter, dokumentierter Struktur-Widerspruch im
// Leitfaden selbst (kein von uns erfundener) — ohne eine reale CO2e-Datei
// (keine der 9 verfuegbaren BMW-Summary-QAFs traegt Type=CO2e, siehe // allow-customer-string
// reconciliation.ts's LOGISTICS-Praezedenzfall fuer dieselbe Evidenzluecke)
// ist nicht entscheidbar, ob ein reales Template einen eigenstaendigen
// "CO2e"-Tab-Namen fuehrt oder die Panel-Spalten direkt in SUMMARY/MATERIAL
// einblendet. Diese Modul-Entscheidung (dokumentiert, nicht stillschweigend):
// findCo2eWorksheet() sucht EIN Worksheet per tolerantem Namens-Match
// ("co2e"/"carbon footprint") — dieselbe additive Namens-Gate-Konvention
// jedes anderen P2.x-Sheet-Parsers — und BEIDE Extraktions-Formen
// (Zusammenfassungs-Panel-Label-Scan + Material-Zeilen-Header-Scan) laufen
// GEGEN DASSELBE Worksheet. Traegt ein reales File die Panel-Spalten
// stattdessen direkt auf SUMMARY oder die Zeilen-Spalten direkt auf MATERIAL
// (ohne einen eigenen "CO2e"-benannten Tab), findet dieses Modul sie NICHT —
// dokumentierte Limitation (analog rmr-parser.ts's "Known layout
// limitation"), kein stillschweigend falsches Ergebnis: coreFieldsFound
// bleibt in diesem Fall korrekt false fuer beide Teile.
//
// ── Field-Scope: alle 14 registrierten CO2E-Canonical-Ids ───────────────────
// canonical-fields.ts's CO2E_FIELDS registriert 14 ids (KAR-892/P1.1): 4
// level:'summary' (PCF-Kernfelder + CBAM, [52]) + 10 level:'row'
// (CO2e-Materialblatt-Feldbeschreibungen inkl. Nachhaltigkeit/PrC-PoC-Rec-Q/
// CBAM-CN-Code, [55]-[56]). Beide Gruppen werden hier extrahiert — Co2eField
// Key = Co2eSummaryFieldKey | Co2eMaterialFieldKey deckt alle 14.
//
// ── Validierung (Kern-Umfang) — NUR wenn der Leitfaden-Extrakt die Formel
// EXPLIZIT nennt (task instruction, S.50-62 sorgfaeltig geprueft) ──────────
//   IMPLEMENTIERT: validateCo2eMaterialEmissionsFormula — Emissionen Material
//   = Emissionsfaktor + Upstream Emissionen Verpackung + Upstream Emissionen
//   Transport (Leitfaden [54]). Wortlaut Abbildung 35 "CO2e Berechnung fuer
//   Kaufteile" (verbatim): "Emissionen Kaufteil (kg CO2e pro Mengeneinheit) +
//   Emissionen Transport/Verpackung (kg CO2e pro Mengeneinheit) = Emissionen
//   Material (kg CO2e pro Mengeneinheit)"; [55] tabelliert "Upstream
//   Emissionen der Materialverpackung" und "Upstream Emissionen Transport"
//   als ZWEI separate Eingabefelder fuer den kombinierten "Emissionen
//   Transport/Verpackung"-Term. Abbildung 36 (Rohmaterialien) nennt dieselbe
//   Formel-FORM mit einer ANDEREN Bezeichnung fuer den ersten Summanden:
//   "Emissionsfaktor Rohmaterial (kg CO2e pro Mengeneinheit) + Emissionen
//   Transport/Verpackung (kg CO2e pro Mengeneinheit) = Emissionen Material
//   (kg CO2e pro Mengeneinheit)". Dies ist eine ECHTE, im Leitfaden selbst
//   vorhandene Terminologie-Inkonsistenz zwischen Abb.35 ("Emissionen
//   Kaufteil") und Abb.36 ("Emissionsfaktor Rohmaterial") — Abb.36s Wortlaut
//   deckt sich mit dem Feldtabellen-Namen "Emissionsfaktor [kg CO2e /
//   Mengeneinheit]" ([55], = co2e_emission_factor), Abb.35s Wortlaut nicht.
//   Wird hier NICHT stillschweigend geglaettet (analog zum "Vulkanisieren"-
//   Kleinbuchstaben-Fall in lccn-parser.ts) — beide Abbildungen beschreiben
//   dieselbe Formel-STRUKTUR (Summand1 + Transport/Verpackung = Emissionen
//   Material), nur mit abweichender Benennung des ersten Summanden je
//   Zeilentyp; validateCo2eMaterialEmissionsFormula liest in BEIDEN Faellen
//   das einzige dafuer registrierte Feld (co2e_emission_factor, [55]s
//   Feldtabellen-Name), da Abb.35/36 keine zwei separaten Eingabefelder
//   dokumentieren, nur zwei unterschiedliche Bezeichnungen fuer dasselbe.
//   Identische Formel-Form, gueltig fuer BEIDE Kaufteil- und Rohmaterial-
//   Zeilen, daher EIN Check statt zwei.
//
//   NICHT IMPLEMENTIERT (dokumentiert, nicht stillschweigend weggelassen):
//   - Gesamtemissionen Material ([54]: "Emissionen Material × Anzahl pro
//     Angebotsteil" bzw. Rohmaterial-Variante zusaetzlich "× Einsatzmenge
//     Rohmaterial × [1/(1−Ausschuss)]") — braucht Anzahl/Einsatzmenge/
//     Ausschuss, die KEINE registrierten co2e_*-Canonical-Felder sind
//     (Standard-MATERIAL-Sheet-Felder, material-parser.ts's Scope, nicht
//     dieses Moduls). Cross-Modul-Verknuepfung ist "bewusst Kern-Umfang"
//     explizit nicht Teil dieses PRs.
//   - Rezyklatgehalt (Rec-Q) = PrC + PoC ([56]) — PrC ("Abfall vor Gebrauch")
//     und PoC ("Abfall nach Gebrauch") sind laut canonical-fields.ts KEINE
//     eigenen registrierten co2e_*-Felder (nur Rec-Q selbst,
//     co2e_recycled_content_recq, ist registriert) — keine parsebare
//     Eingangsgroesse zur Nachrechnung vorhanden. Wuerde die Formel dennoch
//     "nachgebaut", muesste PrC/PoC fabriziert/geraten werden — verboten
//     (Master-Prompt §2, Disziplin-Erinnerung der Task-Instruktion).
//   - CO2e FERTIGUNG-Formeln ([58]-[59]: "Fertigung [kg CO2e] =
//     Σ(Einsatzmenge×Emissionsfaktor) + direkte Emissionen +
//     Betriebsstoffe") — deren Eingangsfelder (Energietraeger/Einsatzmenge/
//     Emissionsfaktor je Energietraeger, Betriebsstoffe) sind ebenfalls KEINE
//     registrierten co2e_*-Canonical-Ids (siehe canonical-fields.ts CO2E_
//     FIELDS — 14 Eintraege insgesamt, keiner davon Fertigungs-Energietraeger-
//     Felder) — nicht Teil dieses Moduls' 14-Feld-Scope.
//   - "Potential"-Reiter ([61]-[62]) ist laut Leitfaden explizit rein
//     informativ ("Dieser Reiter gilt rein fuer informative Zwecke") — wird
//     NICHT geparst (task instruction).

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 { CO2E_FIELD_KEY_TO_CANONICAL } from './canonical-fields'
import { byCanonicalId } from './canonical-model'
import { missingFieldReason, joinMissingReasons } from './bilingual-message'

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

// ── Record shapes ────────────────────────────────────────────────────────

export interface Co2eSummaryValues {
  /** PCF (Product Carbon Footprint), Eingabe nach Catena-X. */
  pcf: number | null
  /** PCF-Einheit — Dropdown "kg CO2e" / "t CO2e". */
  pcfUnit: string | null
  /** Referenzeinheit des Vertragsgegenstandes — "Stück"/"kg"/"t". */
  referenceUnit: string | null
  /** CBAM Specific Direct Embedded Emissions [t CO2e / t CBAM-Ware]. */
  cbamSpecificDirectEmissions: number | null
}

export type Co2eSummaryFieldKey = keyof Co2eSummaryValues

export interface Co2eMaterialRowValues {
  /** Emissionsfaktor [kg CO2e / Mengeneinheit]. */
  emissionFactor: number | null
  /** Quelle des Emissionsfaktors (Freitext). */
  emissionFactorSource: string | null
  /** Upstream Emissionen der Materialverpackung [kg CO2e / Mengeneinheit]. */
  upstreamPackagingEmissions: number | null
  /** Upstream Emissionen Transport [kg CO2e / Mengeneinheit]. */
  upstreamTransportEmissions: number | null
  /** Gesamtentfernung [km]. */
  totalDistance: number | null
  /** Transportmittel (Mehrfachnennung möglich, Freitext). */
  transportMode: string | null
  /** Emissionen Material [kg CO2e / Mengeneinheit] — berechnet. */
  materialEmissions: number | null
  /** Gesamtemissionen [kg CO2e] — berechnet (NICHT hier nachgerechnet, siehe
   * Modul-Header "NICHT IMPLEMENTIERT"). */
  totalMaterialEmissions: number | null
  /** Rezyklatgehalt (Rec-Q) [%] — berechnet (NICHT hier nachgerechnet, siehe
   * Modul-Header). */
  recycledContentRecq: number | null
  /** CN-Code (CBAM, 8-stellig). */
  cnCode: string | null
}

export type Co2eMaterialFieldKey = keyof Co2eMaterialRowValues

/** Union of both record shapes' keys — the module's full 14-field surface,
 * used for the canonical-fields.ts backward map (CO2E_FIELD_KEY_TO_CANONICAL). */
export type Co2eFieldKey = Co2eSummaryFieldKey | Co2eMaterialFieldKey

const ALL_SUMMARY_FIELD_KEYS: readonly Co2eSummaryFieldKey[] = ['pcf', 'pcfUnit', 'referenceUnit', 'cbamSpecificDirectEmissions']

const ALL_MATERIAL_FIELD_KEYS: readonly Co2eMaterialFieldKey[] = [
  'emissionFactor',
  'emissionFactorSource',
  'upstreamPackagingEmissions',
  'upstreamTransportEmissions',
  'totalDistance',
  'transportMode',
  'materialEmissions',
  'totalMaterialEmissions',
  'recycledContentRecq',
  'cnCode',
]

const TEXT_MATERIAL_FIELDS: Set<Co2eMaterialFieldKey> = new Set(['emissionFactorSource', 'transportMode', 'cnCode'])

/**
 * Summary-panel fields that MUST be located (as LABELS) for the "CO2e
 * CONTENT" panel to be usable — PCF + PCF-Einheit (value needs its unit to be
 * meaningful; the sheet's own defining pair, [52]). Deliberately does NOT
 * throw when missing: CO2e is an OPTIONAL, additive sheet/panel (Type-
 * Auswahl, [50]) — see parseCo2eSummaryPanel below.
 */
export const CORE_CO2E_SUMMARY_FIELD_KEYS: readonly Co2eSummaryFieldKey[] = ['pcf', 'pcfUnit']

// KAR-958/P2 (gate-audit.md B10/B11, coreFieldsFound-Resilienz) — the shared
// MIN_SIGNAL_MAPPED_COLUMNS floor from types.ts (PR #325 review fix #5, was
// a duplicated local literal here), used by both CO2e sub-parses below
// (summary panel: found LABELS; material rows: mapped header COLUMNS). Below
// it, a degraded CO2e sheet is still treated as a genuinely empty/foreign
// sheet.

/**
 * Material-row fields that MUST be located for the CO2e-Material row block to
 * be usable — emissionFactor + materialEmissions (the two fields
 * validateCo2eMaterialEmissionsFormula's third input, upstream packaging/
 * transport, is checked per-row at validation time, not gated at parse time
 * — a row missing only the upstream fields is still a genuine CO2e-Material
 * row, just not yet fully quotable).
 */
export const CORE_CO2E_MATERIAL_FIELD_KEYS: readonly Co2eMaterialFieldKey[] = ['emissionFactor', 'materialEmissions']

const MAX_SCAN_RIGHT = 6
const HEADER_MATCH_MIN = 3
const HEADER_SCAN_MAX_ROWS = 20

// ── Sheet detection (see module header "Struktur-Entscheidung") ────────────

/**
 * True when a worksheet name identifies a CO2e-relevant sheet — tolerant
 * substring match on "co2e" or "carbon footprint" (Abbildung 34's own
 * caption: "CO2e-Zusammenfassungsblatt", [53]). See module header for the
 * documented Leitfaden-internal tension this decision resolves. Sheet-name
 * alias source centralized in module-sheet-names.ts (KAR-905/P3.1).
 */
export function isCo2eSheetName(name: string): boolean {
  return matchesModuleSheetName(name, 'CO2E')
}

/** First worksheet whose name matches isCo2eSheetName, or null when the
 * workbook has none (the additive gate). */
export function findCo2eWorksheet(wb: { worksheets: Worksheet[] }): Worksheet | null {
  return wb.worksheets.find((w) => isCo2eSheetName(w.name)) ?? null
}

// ── Canonical registry bridge (dynamic import — Client-bundle discipline,
// KAR-893 lesson) ────────────────────────────────────────────────────────

interface LabelMatch<K extends string> {
  key: K
  /** 1.0 exact labelDe/labelEn match, 0.9 alias match. */
  confidence: number
}

interface Co2eRegistryCtx<K extends string> {
  registry: readonly CanonicalField[]
  idToKey: Record<string, K>
  findByAliasFn: (
    label: string,
    lang: 'de' | 'en' | undefined,
    registry: readonly CanonicalField[],
  ) => CanonicalField[]
}

let co2eSummaryRegistryPromise: Promise<Co2eRegistryCtx<Co2eSummaryFieldKey>> | null = null
let co2eMaterialRegistryPromise: Promise<Co2eRegistryCtx<Co2eMaterialFieldKey>> | null = null

async function loadCo2eSummaryRegistry(): Promise<Co2eRegistryCtx<Co2eSummaryFieldKey>> {
  if (!co2eSummaryRegistryPromise) {
    co2eSummaryRegistryPromise = (async () => {
      const [{ byModule, findByAlias }, { CO2E_FIELD_KEY_TO_CANONICAL }] = await Promise.all([
        import('./canonical-model'),
        import('./canonical-fields'),
      ])
      const registry = byModule('CO2E').filter((f) => f.level === 'summary')
      const idToKey = Object.fromEntries(
        ALL_SUMMARY_FIELD_KEYS.map((key) => [CO2E_FIELD_KEY_TO_CANONICAL[key], key]),
      ) as Record<string, Co2eSummaryFieldKey>
      return { registry, idToKey, findByAliasFn: findByAlias }
    })()
  }
  return co2eSummaryRegistryPromise
}

async function loadCo2eMaterialRegistry(): Promise<Co2eRegistryCtx<Co2eMaterialFieldKey>> {
  if (!co2eMaterialRegistryPromise) {
    co2eMaterialRegistryPromise = (async () => {
      const [{ byModule, findByAlias }, { CO2E_FIELD_KEY_TO_CANONICAL }] = await Promise.all([
        import('./canonical-model'),
        import('./canonical-fields'),
      ])
      const registry = byModule('CO2E').filter((f) => f.level === 'row')
      const idToKey = Object.fromEntries(
        ALL_MATERIAL_FIELD_KEYS.map((key) => [CO2E_FIELD_KEY_TO_CANONICAL[key], key]),
      ) as Record<string, Co2eMaterialFieldKey>
      return { registry, idToKey, findByAliasFn: findByAlias }
    })()
  }
  return co2eMaterialRegistryPromise
}

function matchCellSync<K extends string>(cellText: string, ctx: Co2eRegistryCtx<K>): LabelMatch<K> | null {
  if (cellText === '') return null

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

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

/** Match one already-whitespace-normalized cell string against the CO2E
 * summary-level canonical field registry. Exported for single-cell test
 * callers, same reason every other module's matchX* is. */
export async function matchCo2eSummaryLabelCell(cellText: string): Promise<LabelMatch<Co2eSummaryFieldKey> | null> {
  const ctx = await loadCo2eSummaryRegistry()
  return matchCellSync(cellText, ctx)
}

/** Match one already-whitespace-normalized header cell string against the
 * CO2E Material row-level canonical field registry. */
export async function matchCo2eMaterialHeaderColumn(cellText: string): Promise<LabelMatch<Co2eMaterialFieldKey> | null> {
  const ctx = await loadCo2eMaterialRegistry()
  return matchCellSync(cellText, ctx)
}

function normalizeCell(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 a1(row0: number, col0: number): string {
  return `${colLetter(col0)}${row0 + 1}`
}

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
}

// ── Summary panel (whole-grid label-scan, same shape as lccn-parser.ts) ────

export interface Co2eSummaryParseMeta {
  parseConfidence: number
  mappedFieldCount: number
  /** True once every CORE_CO2E_SUMMARY_FIELD_KEYS label was located. False
   * means at least one core label is missing — since KAR-958/P2, `values` is
   * NOT unconditionally all-null 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 labels were located. `undefined` on every
   * intact parse and on a genuinely empty/foreign sheet. */
  degradation?: FacetDegradation
}

export interface Co2eSummaryParseResult {
  values: Co2eSummaryValues
  sourceCells: Partial<Record<Co2eSummaryFieldKey, string>>
  normalized: Partial<Record<Co2eSummaryFieldKey, string | number>>
  meta: Co2eSummaryParseMeta
}

function emptySummaryValues(): Co2eSummaryValues {
  return { pcf: null, pcfUnit: null, referenceUnit: null, cbamSpecificDirectEmissions: null }
}

async function parseCo2eSummaryPanel(grid: unknown[][], sheetName: string): Promise<Co2eSummaryParseResult> {
  const ctx = await loadCo2eSummaryRegistry()
  const values = emptySummaryValues()
  const sourceCells: Partial<Record<Co2eSummaryFieldKey, string>> = {}
  const normalized: Partial<Record<Co2eSummaryFieldKey, string | number>> = {}
  const confidences: number[] = []
  const foundKeys = new Set<Co2eSummaryFieldKey>()

  for (let r = 0; r < grid.length; r++) {
    const row = (grid[r] as unknown[]) ?? []
    for (let c = 0; c < row.length; c++) {
      const text = normalizeCell(row[c])
      if (text === '') continue
      const match = matchCellSync(text, ctx)
      if (!match || foundKeys.has(match.key)) continue

      for (let k = c + 1; k <= c + MAX_SCAN_RIGHT && k < row.length; k++) {
        const cell = row[k]
        const cellText = normalizeCell(cell)
        // Stop only when the cell is itself a RECOGNIZED label (the next
        // field's label appearing before any value was found) — unlike
        // lccn-parser.ts's all-numeric field set, CO2e summary fields
        // legitimately hold free text (pcfUnit "kg CO2e", referenceUnit
        // "Stück"), so a generic "any non-numeric text = must be a label"
        // heuristic would wrongly reject those values.
        if (cellText !== '' && matchCellSync(cellText, ctx)) break
        if (cell === null || cell === undefined || String(cell).trim() === '') continue
        const num = toNum(cell)
        const value: string | number = num !== null ? num : String(cell).trim()
        ;(values as unknown as Record<string, unknown>)[match.key] = value
        sourceCells[match.key] = a1(r, k)
        normalized[match.key] = value
        break
      }

      foundKeys.add(match.key)
      confidences.push(match.confidence)
    }
  }

  const coreFieldsFound = CORE_CO2E_SUMMARY_FIELD_KEYS.every((k) => foundKeys.has(k))
  // KAR-958/P2 (gate-audit.md B10): below the minimum-signal floor, treated
  // exactly as before this PR. At/above it, a missing core label no longer
  // discards every already-located label — `values`/`sourceCells`/
  // `normalized` were already populated in the scan loop above.
  if (foundKeys.size < MIN_SIGNAL_MAPPED_COLUMNS) {
    return {
      values: emptySummaryValues(),
      sourceCells: {},
      normalized: {},
      meta: { parseConfidence: 0, mappedFieldCount: foundKeys.size, coreFieldsFound: false },
    }
  }

  const parseConfidence = confidences.length > 0 ? confidences.reduce((a, b) => a + b, 0) / confidences.length : 0
  const degradation: FacetDegradation | undefined = coreFieldsFound
    ? undefined
    : {
        facet: 'co2e_summary',
        reason: 'PARSE_FAILED',
        sheet: sheetName,
        message: `CO2e-Summary-Kernfelder fehlen: ${CORE_CO2E_SUMMARY_FIELD_KEYS.filter((k) => !foundKeys.has(k)).join(', ')}.`,
      }
  return {
    values,
    sourceCells,
    normalized,
    meta: {
      parseConfidence,
      mappedFieldCount: foundKeys.size,
      coreFieldsFound,
      ...(degradation ? { degradation } : {}),
    },
  }
}

// ── Material rows (header-row + data-block scan, same shape as
// logistics-parser.ts) ──────────────────────────────────────────────────────

export type Co2eMaterialRow = Co2eMaterialRowValues & {
  sourceCells: Partial<Record<Co2eMaterialFieldKey, string>>
  normalized: Partial<Record<Co2eMaterialFieldKey, string | number | null>>
  rawText: Partial<Record<Co2eMaterialFieldKey, string>>
}

export interface Co2eMaterialParseMeta {
  parseConfidence: number
  unmappedHeaders: string[]
  mappedFieldCount: number
  /** True once every CORE_CO2E_MATERIAL_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. `undefined` on every intact
   * parse and on a genuinely empty/foreign sheet. */
  degradation?: FacetDegradation
}

export type Co2eMaterialParseResult = Co2eMaterialRow[] & Co2eMaterialParseMeta

function withMaterialParseMeta(rows: Co2eMaterialRow[], meta: Co2eMaterialParseMeta): Co2eMaterialParseResult {
  return Object.assign(rows, meta) as Co2eMaterialParseResult
}

const EMPTY_MATERIAL_PARSE_META: Co2eMaterialParseMeta = {
  parseConfidence: 0,
  unmappedHeaders: [],
  mappedFieldCount: 0,
  coreFieldsFound: false,
}

async function parseCo2eMaterialRows(grid: unknown[][], sheetName: string): Promise<Co2eMaterialParseResult> {
  if (grid.length < 2) return withMaterialParseMeta([], EMPTY_MATERIAL_PARSE_META)

  const ctx = await loadCo2eMaterialRegistry()

  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] as unknown[]) ?? []
    let score = 0
    for (const cell of row) {
      if (matchCellSync(normalizeCell(cell), ctx)) score += 1
    }
    if (score > bestScore) {
      bestScore = score
      bestIdx = i
    }
  }
  if (bestIdx === null || bestScore < HEADER_MATCH_MIN) return withMaterialParseMeta([], EMPTY_MATERIAL_PARSE_META)

  const headerIdx = bestIdx
  const headerRow = (grid[headerIdx] as unknown[]).map(normalizeCell)

  const colMap = new Map<number, Co2eMaterialFieldKey>()
  const colConfidence = new Map<number, number>()
  const unmappedHeaders: string[] = []
  const usedKeys = new Set<Co2eMaterialFieldKey>()

  for (let i = 0; i < headerRow.length; i++) {
    const cell = headerRow[i]
    if (cell === '') continue
    const match = matchCellSync(cell, ctx)
    if (!match || usedKeys.has(match.key)) {
      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_CO2E_MATERIAL_FIELD_KEYS.every((k) => mappedKeys.has(k))
  // KAR-958/P2 (gate-audit.md B11): 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 at least one value in a mapped column.
  if (colMap.size < MIN_SIGNAL_MAPPED_COLUMNS) {
    return withMaterialParseMeta([], {
      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: Co2eMaterialRow[] = []
  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<Co2eMaterialRowValues> = {}
    for (const key of ALL_MATERIAL_FIELD_KEYS) {
      ;(r as Record<string, unknown>)[key] = TEXT_MATERIAL_FIELDS.has(key) ? '' : null
    }

    const sourceCells: Partial<Record<Co2eMaterialFieldKey, string>> = {}
    const normalized: Partial<Record<Co2eMaterialFieldKey, string | number | null>> = {}
    const rawText: Partial<Record<Co2eMaterialFieldKey, string>> = {}
    const sheetRowNumber = i + 1

    for (const [colIdx, key] of colMap.entries()) {
      const val = row[colIdx]
      const normVal: string | number | null = TEXT_MATERIAL_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_MATERIAL_FIELDS.has(key) && normVal === null && val !== null && val !== undefined && String(val).trim() !== '') {
        rawText[key] = String(val)
      }
    }

    // Row-push guard (KAR-958/P3 review fix — PR #325 finding #2): no
    // dedicated row-identity TEXT field exists among the 10 CO2e-specific
    // columns (Positionsnummer lives on the base MATERIAL sheet
    // material-parser.ts owns, not duplicated here), so this used to accept
    // ANY value in ANY single mapped column as "genuine CO2e-Material data" —
    // a foreign/degraded sheet that only header-matched 2 UNRELATED columns
    // (e.g. transportMode + cnCode, with emissionFactor/materialEmissions
    // never mapped at all) could still inject rows. CORE_CO2E_MATERIAL_
    // FIELD_KEYS (emissionFactor + materialEmissions — the sheet's own
    // defining "input, computed result" pair, [52]) now serves as the
    // identity anchor instead: a row counts as genuine CO2e-Material data
    // only when AT LEAST ONE of the two core fields is mapped AND carries an
    // actual value on THIS row — `some`, not `every`, deliberately: the
    // existing KAR-958/P2 partial-extraction contract (see
    // "extracts rows when only a NON-core-complete header is missing
    // (emissionFactor)" in co2e-parser.test.ts) legitimately extracts rows
    // whose header is missing exactly ONE of the two core fields but has the
    // OTHER one plus most of the remaining 8 columns genuinely mapped — an
    // `every` gate would wrongly reject that already-covered, safe case too.
    // `some` still closes the finding's actual gap: a foreign/degraded sheet
    // whose 2 recognized columns are BOTH non-core (e.g. transportMode +
    // cnCode) now yields zero rows instead of fabricating data, mirroring
    // every other module's "require the row-identity field(s), not just any
    // mapped column" invariant (material-parser.ts's materialDesignation,
    // rmr-parser.ts's rawMaterialDesignation+rawMaterialQuotation), adapted
    // to this module's identity-less column set via its core-field pair.
    const hasIdentity = CORE_CO2E_MATERIAL_FIELD_KEYS.some((k) => (r as Record<string, unknown>)[k] !== null)
    if (hasIdentity) {
      rows.push({ ...(r as Co2eMaterialRowValues), sourceCells, normalized, rawText })
    }
  }

  const degradation: FacetDegradation | undefined = coreFieldsFound
    ? undefined
    : {
        facet: 'co2e_material',
        reason: 'PARSE_FAILED',
        sheet: sheetName,
        message: `CO2e-Material-Kernfelder fehlen: ${CORE_CO2E_MATERIAL_FIELD_KEYS.filter((k) => !mappedKeys.has(k)).join(', ')}.`,
      }

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

// ── Combined parse ───────────────────────────────────────────────────────

export interface Co2eParseResult {
  summary: Co2eSummaryParseResult
  materialRows: Co2eMaterialParseResult
}

/**
 * Locate + parse the CO2e-relevant sheet from an already-loaded workbook —
 * BOTH extraction forms against the SAME worksheet (see module header
 * "Struktur-Entscheidung"). Returns null when the workbook has no CO2e-
 * relevant sheet at all (the additive gate — distinct from "sheet present
 * but one or both sub-parts too degraded", which returns a Co2eParseResult
 * with the relevant meta.coreFieldsFound:false instead). Never throws.
 */
export async function parseCo2eSheet(wb: { worksheets: Worksheet[] }): Promise<Co2eParseResult | null> {
  const ws = findCo2eWorksheet(wb)
  if (!ws) return null
  const grid = worksheetToGrid(ws) as unknown[][]
  const [summary, materialRows] = await Promise.all([
    parseCo2eSummaryPanel(grid ?? [], ws.name),
    parseCo2eMaterialRows(grid ?? [], ws.name),
  ])
  return { summary, materialRows }
}

/**
 * Derive the tri-state `Co2eSummaryValues | null` value from a Co2eParseResult
 * — mirrors lccnForReconciliation, scoped to the summary sub-part.
 */
export function co2eSummaryForReconciliation(parsed: Co2eParseResult | null): Co2eSummaryValues | null {
  if (parsed === null) return null
  if (!parsed.summary.meta.coreFieldsFound) return null
  return parsed.summary.values
}

/**
 * Derive the tri-state `Co2eMaterialRow[] | null` value from a Co2eParseResult
 * — mirrors rmrRowsForReconciliation/logisticsRowsForReconciliation, scoped
 * to the material-row sub-part.
 */
export function co2eMaterialRowsForReconciliation(parsed: Co2eParseResult | null): Co2eMaterialRow[] | null {
  if (parsed === null) return null
  if (!parsed.materialRows.coreFieldsFound) return null
  return [...parsed.materialRows]
}

/**
 * The shape actions.ts's ingestQafUpload persists on `qaf_file.g60_meta.co2e`
 * — `null` when no CO2e-relevant sheet was found at all, otherwise both
 * sub-parts' rows/values plus their own parse meta.
 */
export interface PersistedCo2eMeta {
  summary: { values: Co2eSummaryValues; sourceCells: Partial<Record<Co2eSummaryFieldKey, string>>; parseMeta: Co2eSummaryParseMeta }
  material: { rows: Co2eMaterialRow[]; parseMeta: Co2eMaterialParseMeta }
}

/**
 * Persisted-JSONB counterpart, KAR-899-style rehydration path threaded
 * through from the start — `undefined` when `qaf_file.g60_meta.co2e` carries
 * no key at all (pre-KAR-904/P2.5 file, or a file whose Type was never
 * CO2e), `null`/the reconstructed result otherwise.
 */
export function co2eFromPersistedMeta(meta: PersistedCo2eMeta | null | undefined): Co2eParseResult | null | undefined {
  if (meta === undefined) return undefined
  if (meta === null) return null
  return {
    summary: { values: meta.summary.values, sourceCells: meta.summary.sourceCells, normalized: {}, meta: meta.summary.parseMeta },
    materialRows: withMaterialParseMeta([...meta.material.rows], meta.material.parseMeta),
  }
}

// ── Validierung: Emissionen Material = Emissionsfaktor + Verpackung + Transport ─

export type Co2eValidationCheckId = 'co2e_material_emissions'
export type Co2eValidationStatus = 'bestanden' | 'abweichung' | 'nicht_pruefbar'

export interface Co2eValidationResult {
  checkId: Co2eValidationCheckId
  side: ComparisonSide
  /** Index in the materialRows array. -1 for a file-level result. */
  rowIndex: number
  status: Co2eValidationStatus
  expected: number | null
  actual: number | null
  deltaAbsolute: number | null
  deltaPercent: number | null
  reason?: string
  /** EN counterpart of `reason` (KAR-906/P3.2). Set whenever `reason` is. */
  reasonEn?: string
  messageDe?: string
  messageEn?: string
}

export interface Co2eValidationConfig {
  relativeTolerance: number
  absoluteToleranceMinor: number
}

export const CO2E_VALIDATION_CONFIG: Co2eValidationConfig = {
  relativeTolerance: 0.005,
  absoluteToleranceMinor: 0.01,
}

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

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

function notPruefbar(rowIndex: number, side: ComparisonSide, reason: string, reasonEn: string): Co2eValidationResult {
  return {
    checkId: 'co2e_material_emissions',
    side,
    rowIndex,
    status: 'nicht_pruefbar',
    expected: null,
    actual: null,
    deltaAbsolute: null,
    deltaPercent: null,
    reason,
    reasonEn,
  }
}

/**
 * Row-level check: Emissionen Material == Emissionsfaktor + Upstream
 * Emissionen der Materialverpackung + Upstream Emissionen Transport,
 * Leitfaden [54]/[55] (see module header — identical formula shape for
 * Kaufteile [Abb.35] and Rohmaterialien [Abb.36]). Gated: returns null — no
 * result at all, not even nicht_pruefbar — when materialEmissions itself is
 * empty (nothing to check yet). A missing component is nicht_pruefbar, not
 * silently treated as 0 (same "missing != zero" distinction every other
 * P2.x formula check in this file family makes).
 */
export function validateCo2eMaterialEmissionsFormula(
  row: Co2eMaterialRow,
  rowIndex: number,
  side: ComparisonSide,
  cfg: Co2eValidationConfig = CO2E_VALIDATION_CONFIG,
): Co2eValidationResult | null {
  const actual = row.materialEmissions
  if (actual === null) return null

  const missing: { de: string; en: string }[] = []
  const checkComponent = (value: number | null, rawTextKey: Co2eMaterialFieldKey, labelDe: string) => {
    if (value !== null) return
    const raw = row.rawText?.[rawTextKey]
    const naMarked = raw !== undefined && isNotApplicableValue(raw)
    missing.push(missingFieldReason(labelDe, co2eLabelEn(rawTextKey, labelDe), naMarked))
  }
  checkComponent(row.emissionFactor, 'emissionFactor', 'Emissionsfaktor')
  checkComponent(row.upstreamPackagingEmissions, 'upstreamPackagingEmissions', 'Upstream Emissionen der Materialverpackung')
  checkComponent(row.upstreamTransportEmissions, 'upstreamTransportEmissions', 'Upstream Emissionen Transport')

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

  const expected = row.emissionFactor! + row.upstreamPackagingEmissions! + row.upstreamTransportEmissions!
  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 messageDe = `Emissionen Material (${side}, Zeile ${rowIndex + 1}): Excel weist ${fmt(actual)} kg CO2e/ME aus, die unabhaengige Nachrechnung (Emissionsfaktor + Upstream Verpackung + Upstream Transport) ergibt ${fmt(expected)} (Delta ${fmt(deltaAbsolute)}) — Abweichung ausserhalb der Toleranz (${(cfg.relativeTolerance * 100).toFixed(1)} % / min. ${cfg.absoluteToleranceMinor}).`
  const messageEn = `Material emissions (${side}, row ${rowIndex + 1}): Excel states ${fmt(actual)} kg CO2e/unit, the independent recomputation (emission factor + upstream packaging + upstream transport) yields ${fmt(expected)} (delta ${fmt(deltaAbsolute)}) — deviation exceeds tolerance (${(cfg.relativeTolerance * 100).toFixed(1)}% / min. ${cfg.absoluteToleranceMinor}).`
  return {
    checkId: 'co2e_material_emissions',
    side,
    rowIndex,
    status: ok ? 'bestanden' : 'abweichung',
    expected,
    actual,
    deltaAbsolute,
    deltaPercent,
    ...(ok ? {} : { messageDe, messageEn }),
  }
}

export interface Co2eValidationInput {
  side: ComparisonSide
  /** Tri-state, identical contract to every other P2.x module: undefined =
   * no CO2e parse attempted (skipped entirely); null = a parse was attempted
   * but no usable CO2e-Material row block was found; Co2eMaterialRow[] =
   * parsed rows (can legitimately be empty). */
  materialRows?: Co2eMaterialRow[] | null
}

function fileLevelNotPruefbar(side: ComparisonSide): Co2eValidationResult {
  return notPruefbar(
    -1,
    side,
    'Kein CO2e-Material-Zeilenblock in dieser Datei erkannt (oder zu stark abweichend) — Nachrechnung nicht moeglich.',
    'No CO2e material row block detected in this file (or too degraded) — recomputation not possible.',
  )
}

/** Orchestrator over one side's materialRows tri-state — mirrors
 * evaluateRmrValidation/evaluateLogisticsValidation exactly. */
export function evaluateCo2eValidation(
  input: Co2eValidationInput,
  config: Co2eValidationConfig = CO2E_VALIDATION_CONFIG,
): Co2eValidationResult[] {
  const { side, materialRows } = input
  if (materialRows === undefined) return []
  if (materialRows === null) return [fileLevelNotPruefbar(side)]

  const results: Co2eValidationResult[] = []
  materialRows.forEach((row, idx) => {
    const r = validateCo2eMaterialEmissionsFormula(row, idx, side, config)
    if (r) results.push(r)
  })
  return results
}

export function co2eValidationResultToPlausibilityIssue(r: Co2eValidationResult): 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,
    }
  }

  return {
    type: r.checkId,
    severity: 'pruefen' as PlausibilitySeverity,
    step: r.side,
    explanation: r.messageDe ?? '',
    explanationEn: r.messageEn,
  }
}

export function checkCo2eValidation(
  input: Co2eValidationInput,
  config: Co2eValidationConfig = CO2E_VALIDATION_CONFIG,
): PlausibilityIssue[] {
  return evaluateCo2eValidation(input, config)
    .map(co2eValidationResultToPlausibilityIssue)
    .filter((x): x is PlausibilityIssue => x !== null)
}