// Multi-QAF material-matrix parser (KAR-931 / Multi-QAF-Programm P1.3, Epic
// KAR-925).
//
// Reads the shared material/BOM matrix of a Multi-QAF workbook — the
// "gemeinsame Materialliste + Mengenfaktor je Variante" pattern all 4
// analyzed real files independently confirm (10-analyse-clarwe-eu.md D,
// 10-analyse-mx.md F, 10-analyse-nafta.md D, 10-analyse-ncar.md E) — into
// VariantMatrixRow[] (types.ts, this PR's target shape). Pure — no ExcelJS
// import at the top level; the ExcelJS bridge at the bottom mirrors
// header-parser.ts's own "pure core / thin adapter" split. The top-level
// orchestration function (parseMaterialMatrix) is ASYNC — it resolves 3
// label synonyms against the canonical-fields.ts registry via a cached
// dynamic import (loadCoreLabelSynonyms, mirrors material-parser.ts's
// loadMaterialRegistry) instead of a hand-copied literal, so the alias list
// has one source of truth (KAR-931 adversarial-review fix F3). All other
// exported functions (locateMaterialMatrixHeader, etc.) stay synchronous.
//
// PRECONDITION (task instruction, not enforced here): a caller has already
// run header-parser.ts's parseVariantHeaderBlock on THIS SAME sheet (Material
// / BOM Detail EU / Vgl Material — whichever sheet this parser is pointed
// at) and passes its VariantDefinition[] result in as `variants`. This
// module never guesses which columns are variant-factor columns from the
// grid itself — variants[i].originalColumn/originalColumnIndex are the sole
// source of "which column is variant i's factor column ON THIS SHEET" (real
// evidence for why this must be resolved per-sheet, not copied verbatim from
// the Summary sheet's own column letters: 10-analyse-mx.md F — Material's
// slot columns are R..AC, NOT the Summary sheet's own Q..AJ letters, though
// slot INDEX order matches; formula-lineage.ts's own module header — Summary's
// Q..AP band maps onto BOM's N..AM band, offset by -3).
//
// ── Real-file evidence for the algorithm below ──────────────────────────
//
// Row identity (Master-Prompt §11.1, 10-analyse-clarwe-eu.md J.10: "Position
// number + part designation, not raw row index, since rows can be
// inserted/deleted/reordered between file versions"): canonicalComponentIdentity
// is built from (normalized) position number + normalized component label,
// mirroring material-parser.ts's own materialRowLogicalKey convention for the
// STANDARD MATERIAL sheet (`${pos}::${designation}`) — deliberately the SAME
// convention, not a diverging one, per this PR's task instruction to look at
// material-parser.ts's field-extraction/registry anchors for consistency.
//
// Padding-row filter (10-analyse-clarwe-eu.md J.6/I.4/D: "Materialzeilen: 125
// befüllte Zeilen ... Rest bis 159 = Summen-/Padding-Bereich" +
// "24x #DIV/0! in BOM Detail EU ... Ursache: L84=AVERAGE(N84:AM84) über eine
// komplett leere Materialzeile ... liegt in einer Template-Padding-Zeile ohne
// reale Materialposition"): a row with NEITHER a position number NOR a
// component label is not a material position at all — skipped outright, even
// when it carries cached #DIV/0!/#N/A error values in its row-control
// columns (harmless template artifact per that same finding).
//
// Total-/Summen-row filter (KAR-931 adversarial-review fix F1,
// 10-analyse-clarwe-eu.md D: row 159 = "Summe Materialkosten" — a LABELED
// row with NO position number): the padding filter above does NOT catch
// this real pattern, since it requires BOTH position AND label to be blank.
// A row is additionally excluded from material-row extraction (and
// classified as a total/aggregate row instead) when its label matches a
// DE/EN Summe/Gesamt/Total pattern, OR its cells in the VARIANT columns
// themselves carry a SUMIF/SUMPRODUCT/aggregate-shaped formula
// (classifyFormulaShape) — the same structural signature the SUMPRODUCT-
// Verifikation pass below searches for. Excluding it here (rather than
// silently absorbing its cached totals as bogus per-component quantity
// factors) is what lets locateVariantTotalCell below actually find the real
// total formula: lastMaterialRow now correctly stops BEFORE this row
// instead of advancing past it.
//
// Empty-cell semantics for the per-variant factor matrix (task instruction:
// "leere Zelle = 0 oder nicht-enthalten? EMPIRISCH klären... und
// dokumentieren") — EMPIRICALLY RESOLVED as "not applicable", NOT 0:
//   - 10-analyse-nafta.md D: one material row has a non-zero factor in the
//     column subset belonging to one platform family and is completely
//     BLANK in the sibling platform family's columns — only the first
//     family's variants use this component at all; the blankness in the
//     other family's columns is a structural "does not apply" signal, not
//     "applies with quantity 0" (real-file evidence, deliberately not
//     reproducing the file's own platform/component identifiers here).
//   - 10-analyse-mx.md F: row 51 has SIX columns holding an EXPLICIT LITERAL
//     `0` (R51=T51=U51=W51=X51=Z51=AA51=0) while its OTHER columns
//     (S51/V51/Y51/AB51/AC51) are non-zero — i.e. the same file uses BOTH a
//     literal `0` AND a blank cell as DIFFERENT, deliberately distinguishable
//     states elsewhere in its factor matrix; collapsing blank into 0 would
//     erase that distinction the source file itself maintains.
//   - types.ts's own VariantMatrixRow doc comment already specifies this
//     contract ("a variant with NO entry for a given row means 'not
//     applicable'... distinct from an entry with value `0`") — this module
//     implements exactly that: a blank/empty cell OMITS the variant's key
//     from quantityFactorByVariant/effectiveCostByVariant entirely; a cell
//     holding a parseable literal `0` INCLUDES the key with value `0`.
//   Note this differs from what SUMPRODUCT/SUMIF do at the AGGREGATE
//   (column-total) level — Excel's own SUM functions treat a blank operand as
//   0 for arithmetic purposes, which is why the reconciliation pass below
//   (SUMPRODUCT-Verifikation) reproduces the SAME arithmetic (a row without a
//   quantityFactorByVariant entry for this variant contributes nothing to the
//   recomputed sum) even though the STRUCTURAL matrix representation keeps
//   "not applicable" and "0" apart. Both are correct at their own level;
//   conflating them would be the actual bug.
//
// unitCost.value is populated ONLY from an explicit, header-labeled
// "AW" (Angebotswährung/offer-currency) unit-cost column — deliberately NO
// automatic BW/exchange-rate fallback derivation, even though
// 10-analyse-clarwe-eu.md D documents `I=G/H` (AW = BW / Wechselkurs) for
// that one family: 10-analyse-nafta.md D documents `K=IFERROR(I*J,"")` for
// its own AW-equivalent column — a MULTIPLICATION, not a division — meaning
// the two real families do not even agree on which arithmetic direction
// "Wechselkurs" represents. Guessing the operator for a file whose AW column
// header this module fails to recognize would silently produce a
// wrong-by-a-factor-of-(rate squared) unit cost roughly as often as a correct
// one — exactly the "nie raten" case Master-Prompt §2 forbids. A row without
// a located AW-cost column reports `validationStatus: 'missing_unit_cost'`
// instead (honest absence, never fabricated). See PR body for the follow-up
// this implies once real-file header text for every family is confirmed.
//
// SUMPRODUCT-Verifikation (task instruction #4): all 4 real files place a
// per-variant-column TOTAL formula in a row just below the material data
// (10-analyse-mx.md F: `R121='=SUMPRODUCT($O$19:$O$120,R19:R120)'`;
// 10-analyse-nafta.md D: `N56=SUMPRODUCT($K$20:$K$54,N20:N54)`;
// 10-analyse-ncar.md E: `Material!N81=SUMPRODUCT($K$19:$K$80,N19:N80)`;
// 10-analyse-clarwe-eu.md D: row 159 "Summe Materialkosten" per column) — the
// SAME structural shape (a SUMIF/SUMPRODUCT/aggregate-shaped formula cell in
// this variant's own column, within a bounded window below the data) across
// all 4 families, found here via `classifyFormulaShape`/`parseFormulaReferences`
// (formula-lineage.ts) rather than a new formula analyzer, per task
// instruction. Recomputed independently from this module's own extracted
// rows (never trusting the sheet's own total blindly) and compared with the
// SAME tolerance philosophy reconciliation.ts already uses (RECONCILIATION_CONFIG,
// imported, not re-derived).
//
// The documented ClarWE template defect (10-analyse-clarwe-eu.md C.3/I.4:
// the row-level SUMIF-Mengensumme spans N:AO — 28 columns, including the 2
// orphaned columns — while the row-level Mengenfaktor-AVERAGE spans only
// N:AM — 26 columns) is NEVER reproduced as this module's own ground truth
// (quantityFactorByVariant is always derived from `variants`-driven per-cell
// reads, never from the sheet's own average/sum row-control columns) — it is
// only DETECTED, when both control columns are present, and surfaced as a
// `formula_column_range_inconsistency` warning (the exact code name types.ts's
// own MultiQafWarning doc comment already anticipates for this finding).
//
// Performance guard (same discipline as formula-lineage.ts's own header):
// bounded row/column scan window (MATERIAL_MATRIX_MAX_ROWS/_COLS, reusing
// workbook-adapter.ts's SEMANTIC_ACTIVE_RANGE_MAX_ROWS/_COLS — never
// ws.dimensions) and a bounded total-row search window
// (MATERIAL_MATRIX_TOTAL_ROW_SCAN_WINDOW). Hitting the row/column scan
// window's own edge with plausible material content still present is
// FAIL-CLOSED, not silently truncated: `truncated: true` on the result, a
// `material_matrix_scan_truncated` warning, and every reconciliation result
// downgraded to `nicht_pruefbar` (a truncated scan cannot safely claim rows
// beyond its own edge do not exist).

import type { Worksheet } from 'exceljs'
import { worksheetToGrid, worksheetToFormulaGrid, semanticActiveRange, SEMANTIC_ACTIVE_RANGE_MAX_ROWS, SEMANTIC_ACTIVE_RANGE_MAX_COLS } from '../workbook-adapter'
import { a1, cellToString } from '../summary-parser'
import { parseLocaleNumber, normalizeProcessName, normalizePosition, normalizeCurrency } from '../normalizer'
import { SHARED_FORMULA_UNRESOLVED } from '../formula-engine'
import { classifyFormulaShape, parseFormulaReferences } from './formula-lineage'
import { RECONCILIATION_CONFIG, type ReconciliationConfig } from '../reconciliation'
import type {
  MultiQafCellRef,
  MultiQafFormulaValue,
  MultiQafMoneyAmount,
  MultiQafWarning,
  VariantDefinition,
  VariantMatrixRow,
  VariantMatrixRowValidationStatus,
} from './types'

type FormulaCell = string | null | typeof SHARED_FORMULA_UNRESOLVED

// ── Pure input/options ──────────────────────────────────────────────────────

export interface MaterialMatrixGridInput {
  /** Sheet this grid was read from — carried into every MultiQafCellRef this
   * module produces. Sheet-agnostic, same discipline as
   * HeaderParserGridInput — call once per detail sheet (Material/BOM Detail
   * EU/Vgl Material/...). */
  sheet: string
  /** 0-based row-major grid of already-resolved cell values — merged-cell
   * propagation MUST already have happened upstream (workbook-adapter.ts
   * resolveCell/worksheetToGrid). */
  grid: readonly unknown[][]
  /** Parallel formula-text grid (workbook-adapter.ts worksheetToFormulaGrid).
   * Optional — omitted entirely, formulaAndCachedValue.formula stays null for
   * every row and the SUMPRODUCT-verification pass reports every
   * reconciliation result as nicht_pruefbar (no formulas to locate a total
   * cell with). */
  formulaGrid?: readonly FormulaCell[][]
}

export interface MaterialMatrixOptions {
  /** 0-based inclusive column scan bounds for HEADER search + full-row
   * padding scans. Defaults to workbook-adapter.ts's shared
   * SEMANTIC_ACTIVE_RANGE_MAX_COLS (never ws.dimensions). */
  colFrom?: number
  colTo?: number
  /** 1-based inclusive row bounds. rowFrom is where the HEADER search starts;
   * rowTo bounds both the header search and the data-row scan (performance
   * guard — see module header). Defaults to workbook-adapter.ts's shared
   * SEMANTIC_ACTIVE_RANGE_MAX_ROWS. */
  rowFrom?: number
  rowTo?: number
  /** Override for MATERIAL_MATRIX_HEADER_SCAN_MAX_ROWS — tests only. */
  headerScanMaxRows?: number
  /** Override for MATERIAL_MATRIX_TOTAL_ROW_SCAN_WINDOW — tests only. */
  totalRowScanWindow?: number
  /** Override for the reconciliation tolerance config — defaults to
   * reconciliation.ts's RECONCILIATION_CONFIG (same fach-freigegebene
   * Toleranz, never re-derived). */
  reconciliationConfig?: ReconciliationConfig
}

// ── Constants ────────────────────────────────────────────────────────────

/** Row/column scan bounds — reused verbatim from workbook-adapter.ts's
 * shared semantic-active-range constants (never ws.dimensions, never a
 * second, possibly-diverging guess — see module header). */
export const MATERIAL_MATRIX_MAX_ROWS = SEMANTIC_ACTIVE_RANGE_MAX_ROWS
export const MATERIAL_MATRIX_MAX_COLS = SEMANTIC_ACTIVE_RANGE_MAX_COLS

/** Generous headroom past every documented real header-row position (all 4
 * real files locate their material-matrix header within the first ~20 rows
 * of their respective sheet). */
export const MATERIAL_MATRIX_HEADER_SCAN_MAX_ROWS = 30

/** Generous headroom past every documented real total-row offset below the
 * last material data row (10-analyse-mx.md F: 1 row; 10-analyse-nafta.md D:
 * ~2 rows; 10-analyse-ncar.md E: 1 row; 10-analyse-clarwe-eu.md D: within the
 * documented Summen-/Padding-Bereich). */
export const MATERIAL_MATRIX_TOTAL_ROW_SCAN_WINDOW = 15

// ── Label synonym table (DE/EN, same buildSynonymTable discipline as
// header-parser.ts's own table — not imported since header-parser.ts does
// not export it, duplicated here as the same small pattern) ───────────────

type MaterialMatrixFieldKey =
  | 'positionNumber'
  | 'componentLabel'
  | 'procurementCurrency'
  | 'offerCurrency'
  | 'unitCostBw'
  | 'exchangeRate'
  | 'unitCostAw'
  | 'logisticsOrDuty'
  | 'materialOverhead'
  /** Row-level "Mengenfaktor-Durchschnitt" control column (template pattern per 10-analyse-clarwe-eu.md,
   * 10-analyse-clarwe-eu.md D's `L`) — read ONLY for the
   * formula_column_range_inconsistency check, NEVER as a factor source (see
   * module header). */
  | 'factorAverageControl'
  /** Row-level "Stückzahl/Summenstückzahl" control column (template pattern per 10-analyse-clarwe-eu.md, column `M`)
   * — same read-only, verification-only role as factorAverageControl. */
  | 'factorSumControl'

function normalizeLabel(raw: string): string {
  return normalizeProcessName(raw)
}

function buildSynonymTable<T extends string>(entries: ReadonlyArray<readonly [string, T]>): Record<string, T> {
  const table: Record<string, T> = {}
  for (const [rawLabel, value] of entries) table[normalizeLabel(rawLabel)] = value
  return table
}

const FIELD_LABEL_SYNONYMS: Record<string, MaterialMatrixFieldKey> = buildSynonymTable([
  ['Position Number', 'positionNumber'],
  ['Positionsnummer', 'positionNumber'],
  ['Position', 'positionNumber'],
  // KAR-931 F5 real-file discovery: all 4 real Multi-QAF BOM-sheet files
  // independently use this EXACT combined label (embedded newline, folded
  // by normalizeProcessName's `\s+` collapse) instead of a bare "Position
  // Number" — none of the 3 short forms above match it standalone.
  ['Position Number reference to Exploded Drawing', 'positionNumber'],
  ['Teilebenennung', 'componentLabel'],
  ['Teilebezeichnung', 'componentLabel'],
  ['Parts Designation', 'componentLabel'],
  ['Materialbezeichnung', 'componentLabel'],
  ['Beschaffungswährung', 'procurementCurrency'],
  ['Purchasing currency', 'procurementCurrency'],
  ['Purchasing Currency', 'procurementCurrency'],
  // KAR-931 F5 real-file discovery: 3 of 4 real files suffix this column
  // with "(BW)" even though the column itself holds a currency CODE, not a
  // BW-denominated amount (a template mislabel, reproduced verbatim here —
  // never "corrected" to what it should logically say).
  ['Purchasing currency (BW)', 'procurementCurrency'],
  ['Angebotswährung', 'offerCurrency'],
  ['Quotation currency', 'offerCurrency'],
  ['Materialkosten pro Mengeneinheit (BW)', 'unitCostBw'],
  ['Imputed unit cost (BW)', 'unitCostBw'],
  ['Imputed unit cost', 'unitCostBw'],
  ['Kosten/Einheit (BW)', 'unitCostBw'],
  // KAR-931 F5 real-file discovery: the real "(AW)"/"(BW)" columns are
  // actually labeled "(in AW)"/"(in BW)" — an extra "in" this module's
  // pre-real-file synonym list never carried. DE side ("Materialkosten...")
  // confirmed on 1 of 4 files, EN side ("Imputed costs per quantity
  // unit...") on the other 3.
  ['Materialkosten pro Mengeneinheit (in BW)', 'unitCostBw'],
  ['Imputed costs per quantity unit (in BW)', 'unitCostBw'],
  ['Wechselkurs', 'exchangeRate'],
  ['WK', 'exchangeRate'],
  ['Exchange rate', 'exchangeRate'],
  ['FX-Rate', 'exchangeRate'],
  ['FX Rate', 'exchangeRate'],
  // KAR-931 F5 real-file discovery: 3 of 4 real files use this EXACT typo
  // ("rate OFF exchange", not "rate of exchange") — reproduced verbatim,
  // same discipline as canonical-fields.ts's own documented BMW-source // allow-customer-string
  // typos, never "corrected" to what it should logically say.
  ['rate off exchange', 'exchangeRate'],
  ['Materialkosten pro Mengeneinheit (AW)', 'unitCostAw'],
  ['Imputed cost per unit (AW)', 'unitCostAw'],
  ['Imputed cost per unit', 'unitCostAw'],
  ['Kosten in AW', 'unitCostAw'],
  ['Cost in AW', 'unitCostAw'],
  ['Stückkosten in Angebotswährung', 'unitCostAw'],
  ['Materialkosten pro Mengeneinheit (in AW)', 'unitCostAw'],
  ['Imputed costs per quantity unit (in AW)', 'unitCostAw'],
  ['Transport+Zoll', 'logisticsOrDuty'],
  ['Transport costs/unit', 'logisticsOrDuty'],
  ['Transportkosten', 'logisticsOrDuty'],
  ['Transport and duty costs', 'logisticsOrDuty'],
  // KAR-931 F5 real-file discovery: 3 of 4 real files' transport-cost
  // column carries this longer combined label (embedded newline folded by
  // normalizeProcessName).
  ['Transport costs per quantity unit [BW]', 'logisticsOrDuty'],
  ['MGK', 'materialOverhead'],
  ['Materialgemeinkosten', 'materialOverhead'],
  ['Overhead', 'materialOverhead'],
  ['Mengenfaktor-Durchschnitt', 'factorAverageControl'],
  ['Average factor', 'factorAverageControl'],
  ['Stückzahl pro Bauteil', 'factorSumControl'],
  ['Lifetime quantity per part', 'factorSumControl'],
])

// ── Canonical-registry-sourced label synonyms (positionNumber/componentLabel/
// unitCostAw — the 3 fields this module deliberately reuses from the
// standard MATERIAL sheet's registry entries, module header "plausibl[e]
// reuse") ───────────────────────────────────────────────────────────────
//
// KAR-931 adversarial-review fix: a PRIOR version of this file hand-copied
// the literal labelDe strings for these 3 fields out of canonical-fields.ts
// as a second, independent constant (mirroring material-parser.ts's own
// dead CORE_FIELD_LABELS_DE, which is defined but never actually used for
// real header matching there — material-parser.ts resolves columns via
// findByAlias against the full canonical-fields.ts alias list instead). That
// hand-copied constant here silently went stale: it carried
// mat_calc_material_cost's labelDe "Kalkulatorische Materialkosten [AW]",
// NOT the KAR-915-confirmed real V8.8 alias "Materialkosten [AW]" — the
// exact same DE-side mismatch that KAR-915 documents as the root cause of
// coreFieldsFound:false / rows:0 on both real MATERIAL-sheet files.
// Resolved here from the registry itself instead — labelDe/labelEn/aliases,
// live, so a future registry fix (like KAR-915) is picked up automatically
// with no second copy to remember to update.
const CORE_LABEL_SYNONYM_SOURCE: ReadonlyArray<readonly [string, MaterialMatrixFieldKey]> = [
  ['mat_position_number', 'positionNumber'],
  ['mat_raw_material_or_purchased_part_designation', 'componentLabel'],
  ['mat_calc_material_cost', 'unitCostAw'],
]

let coreLabelSynonymsPromise: Promise<Record<string, MaterialMatrixFieldKey>> | null = null

/**
 * Lazy + cached (module-level Promise, resolved once) canonical-registry
 * label synonyms for CORE_LABEL_SYNONYM_SOURCE — same dynamic-import
 * discipline as material-parser.ts's loadMaterialRegistry: canonical-
 * fields.ts (the ~115k-line, 13-module registry) must stay OUT of this
 * module's static import graph (KAR-893 "Bundle-Lehre aus #272" — this
 * module is re-exported through the qaf-differences barrel that 'use
 * client' components already import from).
 */
async function loadCoreLabelSynonyms(): Promise<Record<string, MaterialMatrixFieldKey>> {
  if (!coreLabelSynonymsPromise) {
    coreLabelSynonymsPromise = (async () => {
      const { byCanonicalId } = await import('../canonical-model')
      const entries: Array<readonly [string, MaterialMatrixFieldKey]> = []
      for (const [canonicalId, key] of CORE_LABEL_SYNONYM_SOURCE) {
        const field = byCanonicalId(canonicalId)
        if (!field) continue
        entries.push([field.labelDe, key])
        entries.push([field.labelEn, key])
        for (const alias of field.aliases) entries.push([alias, key])
      }
      return buildSynonymTable(entries)
    })()
  }
  return coreLabelSynonymsPromise
}

/** Located header columns for one detail sheet's material matrix. */
type HeaderColumnMap = Partial<Record<MaterialMatrixFieldKey, number>>

// ── Grid access helpers (same shape as header-parser.ts's own) ────────────

function cellAt(grid: readonly unknown[][], row1: number, col0: number): unknown {
  return grid[row1 - 1]?.[col0]
}

function formulaAt(formulaGrid: readonly FormulaCell[][] | undefined, row1: number, col0: number): FormulaCell {
  if (!formulaGrid) return null
  return formulaGrid[row1 - 1]?.[col0] ?? null
}

function cellRef(sheet: string, row1: number, col0: number): MultiQafCellRef {
  return { sheet, cell: a1(row1 - 1, col0), row: row1 - 1, column: col0 + 1 }
}

const EXCEL_ERROR_VALUE_RE = /^#(DIV\/0!|N\/A|REF!|VALUE!|NAME\?|NULL!|NUM!)$/i

/** True when a raw (already-resolved-by-resolveCell) grid value IS an Excel
 * cached error literal (e.g. "#DIV/0!") — the documented padding-row error
 * pattern (module header). No shared helper exists yet for this elsewhere in
 * the codebase; kept local/minimal rather than extracted, single-caller. */
function isExcelErrorValue(v: unknown): boolean {
  return typeof v === 'string' && EXCEL_ERROR_VALUE_RE.test(v.trim())
}

// ── Header location ─────────────────────────────────────────────────────

interface HeaderLocateResult {
  row: number
  columns: HeaderColumnMap
}

/**
 * Locate the material-matrix header row: the row in the scan window with the
 * MOST label matches against FIELD_LABEL_SYNONYMS, gated by a minimum
 * requirement (componentLabel located AND at least one of
 * unitCostAw/unitCostBw located — without a component-identity label and
 * SOME cost column, this is not a usable material-matrix header at all).
 * First-occurrence-wins for a duplicate field key within one row (no
 * collision-group machinery — unlike material-parser.ts's MATERIAL sheet,
 * no documented duplicate-label case exists for this matrix). Never throws —
 * returns null when no row clears the gate (an honestly "no matrix found"
 * outcome, same discipline as header-parser.ts's locateSlotIndexRow).
 *
 * `extraSynonyms` (optional, defaults to {}) is merged on top of
 * FIELD_LABEL_SYNONYMS — the caller-supplied slot for canonical-registry-
 * sourced synonyms (loadCoreLabelSynonyms) so this locator itself stays
 * synchronous/pure; parseMaterialMatrix (the async orchestration boundary)
 * resolves the registry once and passes the result in here.
 */
export function locateMaterialMatrixHeader(
  input: MaterialMatrixGridInput,
  colFrom: number,
  colTo: number,
  rowFrom: number,
  rowTo: number,
  extraSynonyms: Record<string, MaterialMatrixFieldKey> = {},
): HeaderLocateResult | null {
  let best: HeaderLocateResult | null = null
  let bestScore = 0
  for (let r = rowFrom; r <= rowTo; r++) {
    const columns: HeaderColumnMap = {}
    let score = 0
    for (let c = colFrom; c <= colTo; c++) {
      const s = cellToString(cellAt(input.grid, r, c))
      if (s === null) continue
      const normalized = normalizeLabel(s)
      const key = FIELD_LABEL_SYNONYMS[normalized] ?? extraSynonyms[normalized]
      if (key === undefined || columns[key] !== undefined) continue
      columns[key] = c
      score++
    }
    const hasCore = columns.componentLabel !== undefined && (columns.unitCostAw !== undefined || columns.unitCostBw !== undefined)
    if (hasCore && score > bestScore) {
      best = { row: r, columns }
      bestScore = score
    }
  }
  return best
}

// ── Row extraction ──────────────────────────────────────────────────────

function moneyAt(grid: readonly unknown[][], row1: number, col0: number | undefined, currency: string | null): MultiQafMoneyAmount | null {
  if (col0 === undefined) return null
  const raw = cellAt(grid, row1, col0)
  if (isExcelErrorValue(raw)) return { value: null, currency }
  return { value: parseLocaleNumber(raw), currency }
}

// Total-/Summen-row label pattern — KAR-931 adversarial-review fix F1:
// 10-analyse-clarwe-eu.md D documents row 159 as "Summe Materialkosten" — a
// row with a NON-blank label but NO position number, i.e. it does NOT clear
// the old "no position AND no label" padding filter and was silently
// mis-parsed as a material component row (its SUMPRODUCT-cached total
// values consumed as if they were per-component quantity factors). Matched
// against the RAW label text (leading token), not the normalized one, since
// normalizeProcessName may fold whitespace/case in ways that would still
// leave "summe"/"gesamt" recognizable at the front either way.
//
// DE-only (Summe/Gesamt), deliberately NOT "total"/"sum" (KAR-931 F5
// real-file discovery, real bug caught by the env-gated real-file test this
// PR adds): a real material line item in one of the 4 real files is
// genuinely labeled starting with the ordinary English word "sum" ("sum of
// <bucket> parts" — a small-parts catch-all bucket, not a total row) and
// was wrongly excluded by an earlier version of this pattern that included
// bare "sum"/"total" as EN equivalents. None of the 4 real files' actual
// total/summary rows use an English "Total"/"Sum" label at all — they all
// use "Summe ..."/"... Gesamt" (DE) — so the EN alternatives were pure
// speculation this real-file evidence directly contradicts. Formula-shape
// detection (rowHasAggregateFormulaInVariantColumns, below) remains the
// label-independent signal for a genuine EN-labeled or unlabeled total row.
const TOTAL_ROW_LABEL_RE = /^(summe|gesamt)\b/i

/** True when `r`'s cells in the VARIANT columns themselves (not the
 * row-control columns) carry a SUMIF/SUMPRODUCT/aggregate-shaped formula —
 * the same structural signature locateVariantTotalCell searches for below,
 * reused here (classifyFormulaShape, formula-lineage.ts) as a second,
 * label-independent signal that this is an aggregation row, not a
 * component row (module header "SUMPRODUCT-Verifikation": all 4 real
 * families place exactly this formula shape in the total row). */
function rowHasAggregateFormulaInVariantColumns(input: MaterialMatrixGridInput, r: number, variants: readonly VariantDefinition[]): boolean {
  if (!input.formulaGrid) return false
  for (const variant of variants) {
    const col0 = variant.originalColumnIndex - 1
    const formula = formulaAt(input.formulaGrid, r, col0)
    if (typeof formula !== 'string' || formula === '') continue
    const shape = classifyFormulaShape(formula).kind
    if (shape === 'sumif_sumproduct_band' || shape === 'aggregate') return true
  }
  return false
}

/** True when row `r` carries content plausibly belonging to a material
 * position at the SCAN WINDOW'S OWN EDGE — the fail-closed truncation
 * contract (module header "Performance guard": "plausible material content
 * still present"), i.e. position OR label is non-blank, or a cost cell
 * holds a genuinely parseable number (or a cached Excel error — the
 * documented padding-error pattern). Used ONLY for the edge-of-window
 * truncation flag, never for row classification itself.
 *
 * KAR-931 F5 real-file discovery: an EARLIER version of this check treated
 * ANY non-blank cost-cell content as "plausible" — a real file's own
 * post-material summary block (e.g. a "MGK"/overhead row) can leave a
 * stray UNIT-LABEL string (not a cost value) in the cost column of an
 * otherwise position/label-blank row; that string is non-null but not a
 * number, so it was wrongly counted as plausible material content and
 * forced a false truncation. A cell must actually PARSE as a number (or be
 * a cached error) to count now. */
function rowHasPlausibleContentAtEdge(grid: readonly unknown[][], r: number, posRaw: string | null, labelRaw: string | null, cols: HeaderColumnMap): boolean {
  if (posRaw !== null && posRaw !== '') return true
  if (labelRaw !== null && labelRaw !== '') return true
  for (const c of [cols.unitCostAw, cols.unitCostBw]) {
    if (c === undefined) continue
    const raw = cellAt(grid, r, c)
    if (isExcelErrorValue(raw)) return true
    if (parseLocaleNumber(raw) !== null) return true
  }
  return false
}

interface ExtractRowsResult {
  rows: VariantMatrixRow[]
  lastMaterialRow: number | null
  paddingErrorRowCount: number
  paddingErrorSample: MultiQafCellRef | null
  totalRowsExcluded: number
  totalRowSample: MultiQafCellRef | null
  scanReachedWindowEdge: boolean
  warnings: MultiQafWarning[]
}

function extractRows(
  input: MaterialMatrixGridInput,
  header: HeaderLocateResult,
  variants: readonly VariantDefinition[],
  rowTo: number,
): ExtractRowsResult {
  const { grid, formulaGrid, sheet } = input
  const cols = header.columns
  const rows: VariantMatrixRow[] = []
  const seenIdentity = new Map<string, number>()
  const identityOccurrenceCount = new Map<string, number>()
  const warnings: MultiQafWarning[] = []
  let lastMaterialRow: number | null = null
  let paddingErrorRowCount = 0
  let paddingErrorSample: MultiQafCellRef | null = null
  let totalRowsExcluded = 0
  let totalRowSample: MultiQafCellRef | null = null
  let scanReachedWindowEdge = false

  const dataStart = header.row + 1
  const scanEnd = Math.min(rowTo, grid.length)

  for (let r = dataStart; r <= scanEnd; r++) {
    const posRaw = cellToString(cellAt(grid, r, cols.positionNumber ?? -1))
    const labelRaw = cellToString(cellAt(grid, r, cols.componentLabel ?? -1))

    const isBlankPositionAndLabel = (posRaw === null || posRaw === '') && (labelRaw === null || labelRaw === '')
    const isTotalLabel = labelRaw !== null && TOTAL_ROW_LABEL_RE.test(labelRaw.trim())
    // Formula-shape signal is label-independent (module header "SUMPRODUCT-
    // Verifikation") — checked whenever a total-label match hasn't already
    // settled the question, so a genuine material row's plain-value variant
    // cells (never formulas) are never misclassified either way.
    const isAggregateRow = isTotalLabel || rowHasAggregateFormulaInVariantColumns(input, r, variants)

    if (isBlankPositionAndLabel || isAggregateRow) {
      // Padding/blank row OR a recognized total/summary row (module header
      // "Padding-row filter" + KAR-931 F1 fix above): neither is a material
      // position — check the row-control columns (where a cached #DIV/0! is
      // documented to land) purely for diagnostics, never for data.
      const controlCols = [cols.factorAverageControl, cols.factorSumControl, cols.unitCostAw, cols.unitCostBw]
      for (const c of controlCols) {
        if (c === undefined) continue
        if (isExcelErrorValue(cellAt(grid, r, c))) {
          paddingErrorRowCount++
          if (paddingErrorSample === null) paddingErrorSample = cellRef(sheet, r, c)
          break
        }
      }
      if (isAggregateRow) {
        totalRowsExcluded++
        if (totalRowSample === null) totalRowSample = cellRef(sheet, r, cols.componentLabel ?? cols.positionNumber ?? 0)
      }
      // Fail-closed edge flag (KAR-931 F2 fix): a recognized total/summary
      // row landing exactly at the window edge is NOT "plausible material
      // content that might continue beyond the window" — it is the
      // documented end-of-data marker, so it must NOT trigger a false
      // truncation. A genuinely blank row at the edge never triggers it
      // either (nothing plausible there). Only an as-yet-unclassified row
      // that still carries position/label/cost content at the exact edge
      // is ambiguous enough to fail closed.
      if (r === rowTo && !isAggregateRow && rowHasPlausibleContentAtEdge(grid, r, posRaw, labelRaw, cols)) scanReachedWindowEdge = true
      continue
    }

    const position = normalizePosition(posRaw ?? '')
    const normalizedLabel = normalizeProcessName(labelRaw ?? '')
    const baseIdentity = `${position}::${normalizedLabel}`
    let identity = baseIdentity
    const firstSeenRow = seenIdentity.get(baseIdentity)
    let identityCollided = false
    if (firstSeenRow !== undefined) {
      identityCollided = true
      // KAR-931 adversarial-review fix F4: the disambiguation suffix used
      // to be the RAW absolute row number (`#row${r}`) — this broke the
      // row-shift invariance canonicalComponentIdentity's own doc comment
      // promises (types.ts VariantMatrixRow: "not just the row number...
      // since rows can be inserted/deleted/reordered between file
      // versions"), because inserting/deleting any row ABOVE a collision
      // group shifts every absolute row number below it, changing the
      // identity of a component that itself did not move. The ordinal here
      // is the occurrence COUNT within this collision group in document
      // order (2nd occurrence -> #2, 3rd -> #3, ...) instead — this is
      // shift-invariant as long as the collision group's OWN internal
      // ordering is preserved (documented edge case: if a row is inserted
      // or reordered BETWEEN the group's own members, the ordinal each
      // member receives can itself shift — P2 cross-version matching must
      // still treat that as a "position moved" signal, same as any other
      // structural reorder within a duplicate group).
      const ordinal = (identityOccurrenceCount.get(baseIdentity) ?? 1) + 1
      identityOccurrenceCount.set(baseIdentity, ordinal)
      identity = `${baseIdentity}#${ordinal}`
      warnings.push({
        code: 'material_matrix_row_identity_collision',
        severity: 'warning',
        message: `Materialzeile ${r} (Sheet "${sheet}") teilt sich Positionsnummer+Bezeichnung mit Zeile ${firstSeenRow} — Identität deterministisch um eine Kollisions-Ordinalzahl (#${ordinal}, stabil bei Zeilen-Verschiebung) erweitert, damit beide Zeilen unterscheidbar bleiben.`,
        messageEn: `Material row ${r} (sheet "${sheet}") shares position number + designation with row ${firstSeenRow} — identity deterministically suffixed with a collision ordinal (#${ordinal}, stable under row shifts) so both rows stay distinguishable.`,
        sourceReferences: [cellRef(sheet, r, cols.positionNumber ?? cols.componentLabel!)],
      })
    } else {
      seenIdentity.set(baseIdentity, r)
      identityOccurrenceCount.set(baseIdentity, 1)
    }

    const procurementCurrencyRaw = cols.procurementCurrency !== undefined ? cellToString(cellAt(grid, r, cols.procurementCurrency)) : null
    const offerCurrencyRaw = cols.offerCurrency !== undefined ? cellToString(cellAt(grid, r, cols.offerCurrency)) : null
    const procurementCurrency = normalizeCurrency(procurementCurrencyRaw)
    const offerCurrency = normalizeCurrency(offerCurrencyRaw)
    const moneyCurrency = offerCurrency ?? procurementCurrency

    const unitCostRawCell = cols.unitCostAw !== undefined ? cellAt(grid, r, cols.unitCostAw) : undefined
    const isErrorCached = cols.unitCostAw !== undefined && isExcelErrorValue(unitCostRawCell)
    const unitCostValue = cols.unitCostAw !== undefined && !isErrorCached ? parseLocaleNumber(unitCostRawCell) : null
    const unitCost: MultiQafMoneyAmount = { value: unitCostValue, currency: moneyCurrency }

    const exchangeRate = cols.exchangeRate !== undefined ? parseLocaleNumber(cellAt(grid, r, cols.exchangeRate)) : null
    const logisticsOrDuty = moneyAt(grid, r, cols.logisticsOrDuty, moneyCurrency)
    const materialOverhead = moneyAt(grid, r, cols.materialOverhead, moneyCurrency)

    const sourceCells: MultiQafCellRef[] = []
    if (cols.positionNumber !== undefined && posRaw !== null) sourceCells.push(cellRef(sheet, r, cols.positionNumber))
    if (cols.componentLabel !== undefined && labelRaw !== null) sourceCells.push(cellRef(sheet, r, cols.componentLabel))
    if (cols.unitCostAw !== undefined) sourceCells.push(cellRef(sheet, r, cols.unitCostAw))
    if (cols.exchangeRate !== undefined && exchangeRate !== null) sourceCells.push(cellRef(sheet, r, cols.exchangeRate))
    if (cols.procurementCurrency !== undefined && procurementCurrency !== null) sourceCells.push(cellRef(sheet, r, cols.procurementCurrency))
    if (cols.offerCurrency !== undefined && offerCurrency !== null) sourceCells.push(cellRef(sheet, r, cols.offerCurrency))
    if (cols.logisticsOrDuty !== undefined && logisticsOrDuty?.value !== null) sourceCells.push(cellRef(sheet, r, cols.logisticsOrDuty))
    if (cols.materialOverhead !== undefined && materialOverhead?.value !== null) sourceCells.push(cellRef(sheet, r, cols.materialOverhead))

    const quantityFactorByVariant: Record<string, number> = {}
    const effectiveCostByVariant: Record<string, number | null> = {}
    for (const variant of variants) {
      const col0 = variant.originalColumnIndex - 1
      const raw = cellAt(grid, r, col0)
      // Empty-cell semantics (module header, empirically resolved): a truly
      // blank cell OMITS this variant's key entirely ("not applicable"); a
      // parseable literal (including an explicit 0) INCLUDES it.
      const s = cellToString(raw)
      if (s === null) continue
      const factor = parseLocaleNumber(raw)
      if (factor === null) continue // unparseable non-blank content — never fabricated as 0 or silently omitted; simply not counted as a factor.
      quantityFactorByVariant[variant.stableInternalId] = factor
      effectiveCostByVariant[variant.stableInternalId] = unitCost.value !== null ? unitCost.value * factor : null
      sourceCells.push(cellRef(sheet, r, col0))
    }

    const unitCostFormulaText = formulaAt(formulaGrid, r, cols.unitCostAw ?? -1)
    const formulaAndCachedValue: MultiQafFormulaValue | null =
      cols.unitCostAw !== undefined
        ? {
            formula: typeof unitCostFormulaText === 'string' ? unitCostFormulaText : null,
            cachedValue: typeof unitCostRawCell === 'number' || typeof unitCostRawCell === 'string' ? unitCostRawCell : null,
          }
        : null

    let validationStatus: VariantMatrixRowValidationStatus
    if (isErrorCached) {
      validationStatus = 'formula_error_cached'
    } else if (unitCost.value === null) {
      validationStatus = 'missing_unit_cost'
    } else if (Object.keys(quantityFactorByVariant).length === 0 && variants.length > 0) {
      validationStatus = 'missing_factor_for_all_variants'
    } else if (identityCollided) {
      validationStatus = 'needs_review'
    } else {
      validationStatus = 'ok'
    }

    rows.push({
      canonicalComponentIdentity: identity,
      sourceRow: r,
      unitCost,
      procurementCurrency,
      offerCurrency,
      exchangeRate,
      logisticsOrDuty,
      materialOverhead,
      quantityFactorByVariant,
      effectiveCostByVariant,
      formulaAndCachedValue,
      sourceCells,
      validationStatus,
    })
    lastMaterialRow = r
    if (r === rowTo) scanReachedWindowEdge = true
  }

  return { rows, lastMaterialRow, paddingErrorRowCount, paddingErrorSample, totalRowsExcluded, totalRowSample, scanReachedWindowEdge, warnings }
}

// ── Row-control-column range-inconsistency check (documented range-defect pattern, 10-analyse-clarwe-eu.md —
// module header: detected, never reproduced) ──────────────────────────────

function widestColumnSpan(formula: string): number | null {
  const refs = parseFormulaReferences(formula)
  let widest: number | null = null
  for (const ref of refs) {
    if (ref.toColumn === null) continue
    const span = Math.abs(ref.toColumn - ref.fromColumn) + 1
    if (widest === null || span > widest) widest = span
  }
  return widest
}

function detectFactorRangeInconsistency(input: MaterialMatrixGridInput, header: HeaderLocateResult, firstMaterialRow: number): MultiQafWarning | null {
  const { columns } = header
  if (columns.factorAverageControl === undefined || columns.factorSumControl === undefined) return null
  const avgFormula = formulaAt(input.formulaGrid, firstMaterialRow, columns.factorAverageControl)
  const sumFormula = formulaAt(input.formulaGrid, firstMaterialRow, columns.factorSumControl)
  if (typeof avgFormula !== 'string' || typeof sumFormula !== 'string') return null

  const avgSpan = widestColumnSpan(avgFormula)
  const sumSpan = widestColumnSpan(sumFormula)
  if (avgSpan === null || sumSpan === null || avgSpan === sumSpan) return null

  return {
    code: 'formula_column_range_inconsistency',
    severity: 'warning',
    message: `Zeilen-Kontrollspalten für den Mengenfaktor sind inkonsistent: Durchschnitts-Formel spannt ${avgSpan} Spalten, Summen-Formel ${sumSpan} Spalten (Zeile ${firstMaterialRow}, Sheet "${input.sheet}") — Template-Formel-Inkonsistenz, NICHT als Ziel-Mengenfaktor übernommen (dieses Modul liest Faktoren ausschließlich aus den Varianten-Spalten selbst).`,
    messageEn: `Row-level control columns for the quantity factor are inconsistent: the average formula spans ${avgSpan} columns, the sum formula spans ${sumSpan} columns (row ${firstMaterialRow}, sheet "${input.sheet}") — a template formula inconsistency, NOT adopted as the target quantity factor (this module reads factors exclusively from the variant columns themselves).`,
    sourceReferences: [cellRef(input.sheet, firstMaterialRow, columns.factorAverageControl), cellRef(input.sheet, firstMaterialRow, columns.factorSumControl)],
  }
}

// ── SUMPRODUCT-Verifikation ──────────────────────────────────────────────

export type MaterialMatrixReconciliationStatus = 'bestanden' | 'abweichung' | 'nicht_pruefbar'

export interface MaterialMatrixReconciliationResult {
  variantId: string
  status: MaterialMatrixReconciliationStatus
  /** Recomputed from this module's own extracted rows — Sigma(unitCost x
   * factor) over every row carrying a (non-omitted) factor for this variant. */
  expected: number | null
  /** Cached value at the located total cell (resolveCell-already-resolved). */
  actual: number | null
  deltaAbsolute: number | null
  deltaPercent: number | null
  /** The located SUMIF/SUMPRODUCT/aggregate-shaped total cell, or null when
   * none was found within the scan window. */
  totalCell: MultiQafCellRef | null
  /** Set only for status === 'nicht_pruefbar'. */
  reason?: string
}

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

/** Locate the first SUMIF/SUMPRODUCT/aggregate-shaped formula cell in
 * `variant`'s own column, scanning downward from `searchFrom` through
 * `searchTo` (module header: all 4 real families place this within a few
 * rows of the last material row). Reuses classifyFormulaShape
 * (formula-lineage.ts) — never a new formula analyzer, per task
 * instruction. */
function locateVariantTotalCell(input: MaterialMatrixGridInput, variant: VariantDefinition, searchFrom: number, searchTo: number): { row: number; col0: number } | null {
  if (!input.formulaGrid) return null
  const col0 = variant.originalColumnIndex - 1
  for (let r = searchFrom; r <= searchTo; r++) {
    const formula = formulaAt(input.formulaGrid, r, col0)
    if (typeof formula !== 'string' || formula === '') continue
    const shape = classifyFormulaShape(formula).kind
    if (shape === 'sumif_sumproduct_band' || shape === 'aggregate') return { row: r, col0 }
  }
  return null
}

function evaluateVariantReconciliation(
  input: MaterialMatrixGridInput,
  variant: VariantDefinition,
  rows: readonly VariantMatrixRow[],
  lastMaterialRow: number | null,
  scanTruncated: boolean,
  totalRowScanWindow: number,
  cfg: ReconciliationConfig,
): MaterialMatrixReconciliationResult {
  const base = { variantId: variant.stableInternalId, expected: null, actual: null, deltaAbsolute: null, deltaPercent: null, totalCell: null }

  if (scanTruncated) {
    return { ...base, status: 'nicht_pruefbar', reason: 'Material-Matrix-Scan wurde durch den Performance-Guard beendet (reale Daten evtl. jenseits des Scan-Fensters) — Rekonziliation nicht belastbar.' }
  }
  if (lastMaterialRow === null) {
    return { ...base, status: 'nicht_pruefbar', reason: 'Keine Materialzeilen im gescannten Bereich gefunden — keine Gegenrechnung möglich.' }
  }

  let expected = 0
  let blockingRows = 0
  for (const row of rows) {
    const factor = row.quantityFactorByVariant[variant.stableInternalId]
    if (factor === undefined) continue // not applicable to this variant — contributes nothing, matching Excel's own SUM-of-blank=0 arithmetic.
    const effective = row.effectiveCostByVariant[variant.stableInternalId]
    if (effective === null || effective === undefined) {
      blockingRows++
      continue
    }
    expected += effective
  }
  if (blockingRows > 0) {
    return {
      ...base,
      status: 'nicht_pruefbar',
      reason: `${blockingRows} Materialzeile(n) mit Mengenfaktor für diese Variante, aber ohne bekannte Einheitskosten — Summe nicht belastbar.`,
    }
  }

  const totalCellLoc = locateVariantTotalCell(input, variant, lastMaterialRow + 1, lastMaterialRow + totalRowScanWindow)
  if (!totalCellLoc) {
    return { ...base, expected, status: 'nicht_pruefbar', reason: 'Keine SUMIF/SUMPRODUCT/Summen-Formel in der Varianten-Spalte innerhalb des Scan-Fensters unterhalb der Materialzeilen gefunden.' }
  }

  const totalCell = cellRef(input.sheet, totalCellLoc.row, totalCellLoc.col0)
  const actualRaw = cellAt(input.grid, totalCellLoc.row, totalCellLoc.col0)
  const actual = isExcelErrorValue(actualRaw) ? null : parseLocaleNumber(actualRaw)
  if (actual === null) {
    return { ...base, expected, totalCell, status: 'nicht_pruefbar', reason: 'Summenzelle enthält keinen auswertbaren Zahlenwert (Fehlerwert oder leer).' }
  }

  const deltaAbsolute = Number((actual - expected).toFixed(4))
  const deltaPercent = expected !== 0 ? Number((deltaAbsolute / Math.abs(expected)).toFixed(6)) : null
  const status: MaterialMatrixReconciliationStatus = withinTolerance(expected, actual, cfg) ? 'bestanden' : 'abweichung'
  return { variantId: variant.stableInternalId, status, expected, actual, deltaAbsolute, deltaPercent, totalCell }
}

// ── Orchestration ────────────────────────────────────────────────────────

export interface MaterialMatrixParseResult {
  rows: readonly VariantMatrixRow[]
  reconciliation: readonly MaterialMatrixReconciliationResult[]
  warnings: readonly MultiQafWarning[]
  /** 1-based header row, or null when no usable material-matrix header was
   * located at all (rows/reconciliation are then both empty). */
  headerRow: number | null
  /** true when the row/column scan window's own edge was reached with
   * plausible material content still present — see module header
   * "Performance guard". */
  truncated: boolean
}

/**
 * Parse ONE sheet's material matrix into VariantMatrixRow[] + a per-variant
 * SUMPRODUCT reconciliation. Functionally pure (no side effects, no
 * mutation) but ASYNC — see module header for the full algorithm writeup
 * and its real-file evidence. Async because header location resolves 3
 * label synonyms (positionNumber/componentLabel/unitCostAw) against the
 * canonical-fields.ts registry via a cached dynamic import
 * (loadCoreLabelSynonyms) rather than a hand-copied literal, KAR-931
 * adversarial-review fix F3 — the import stays dynamic (not hoisted to this
 * module's static top) so canonical-fields.ts's ~115k-line registry stays
 * out of this barrel's client-bundle graph (KAR-893).
 */
export async function parseMaterialMatrix(input: MaterialMatrixGridInput, variants: readonly VariantDefinition[], options: MaterialMatrixOptions = {}): Promise<MaterialMatrixParseResult> {
  const colFrom = options.colFrom ?? 0
  const colTo = options.colTo ?? MATERIAL_MATRIX_MAX_COLS
  const rowFrom = options.rowFrom ?? 1
  const rowTo = options.rowTo ?? MATERIAL_MATRIX_MAX_ROWS
  const headerScanMaxRows = options.headerScanMaxRows ?? MATERIAL_MATRIX_HEADER_SCAN_MAX_ROWS
  const totalRowScanWindow = options.totalRowScanWindow ?? MATERIAL_MATRIX_TOTAL_ROW_SCAN_WINDOW
  const cfg = options.reconciliationConfig ?? RECONCILIATION_CONFIG

  const coreSynonyms = await loadCoreLabelSynonyms()
  const headerScanRowTo = Math.min(rowTo, rowFrom + headerScanMaxRows - 1)
  const header = locateMaterialMatrixHeader(input, colFrom, colTo, rowFrom, headerScanRowTo, coreSynonyms)

  if (!header) {
    return {
      rows: [],
      reconciliation: [],
      warnings: [
        {
          code: 'material_matrix_header_not_found',
          severity: 'warning',
          message: `Keine auswertbare Material-Matrix-Kopfzeile im Sheet "${input.sheet}" gefunden (Positionsnummer/Bezeichnung + Einheitskosten nicht identifizierbar) — Sheet wird nicht als Material-Matrix behandelt.`,
          messageEn: `No usable material-matrix header row found in sheet "${input.sheet}" (position number/designation + unit cost not identifiable) — sheet not treated as a material matrix.`,
          sourceReferences: [],
        },
      ],
      headerRow: null,
      truncated: false,
    }
  }

  const extraction = extractRows(input, header, variants, rowTo)
  const warnings: MultiQafWarning[] = [...extraction.warnings]

  if (extraction.paddingErrorRowCount > 0) {
    warnings.push({
      code: 'material_matrix_padding_rows_skipped',
      severity: 'info',
      message: `${extraction.paddingErrorRowCount} Padding-Zeile(n) ohne Positionsnummer/Bezeichnung mit gecachten Fehlerwerten (z.B. #DIV/0!) in den Kontrollspalten übersprungen — dokumentierter, harmloser Realfall (leere Materialzeile, AVERAGE/SUMIF über eine leere Range).`,
      messageEn: `${extraction.paddingErrorRowCount} padding row(s) without a position number/designation, carrying cached error values (e.g. #DIV/0!) in their control columns, were skipped — a documented, harmless real-file pattern (empty material row, AVERAGE/SUMIF over an empty range).`,
      sourceReferences: extraction.paddingErrorSample ? [extraction.paddingErrorSample] : [],
    })
  }

  if (extraction.totalRowsExcluded > 0) {
    warnings.push({
      code: 'material_matrix_total_row_excluded',
      severity: 'info',
      message: `${extraction.totalRowsExcluded} Summen-/Total-Zeile(n) (Label-Muster Summe/Gesamt/Total oder SUMIF/SUMPRODUCT-Formel in den Varianten-Spalten) erkannt und von der Materialzeilen-Extraktion ausgeschlossen — dokumentierter Realfall (10-analyse-clarwe-eu.md D: "Summe Materialkosten"-Zeile ohne Positionsnummer).`,
      messageEn: `${extraction.totalRowsExcluded} total/summary row(s) (Summe/Gesamt/Total label pattern or a SUMIF/SUMPRODUCT formula in the variant columns) were recognized and excluded from material-row extraction — a documented real-file pattern (a "Summe Materialkosten" row with no position number).`,
      sourceReferences: extraction.totalRowSample ? [extraction.totalRowSample] : [],
    })
  }

  if (extraction.scanReachedWindowEdge) {
    warnings.push({
      code: 'material_matrix_scan_truncated',
      severity: 'warning',
      message: `Material-Matrix-Scan hat das Scan-Fenster-Ende (Zeile ${rowTo}) mit noch plausiblem Materialzeilen-Inhalt erreicht — reale Daten könnten jenseits des Fensters liegen; Ergebnis fail-closed als unvollständig markiert.`,
      messageEn: `Material-matrix scan reached the scan window's own edge (row ${rowTo}) with plausible material-row content still present — real data may extend beyond the window; result marked fail-closed as incomplete.`,
      sourceReferences: [],
    })
  }

  if (extraction.rows.length > 0) {
    const rangeWarning = detectFactorRangeInconsistency(input, header, extraction.rows[0].sourceRow)
    if (rangeWarning) warnings.push(rangeWarning)
  }

  const reconciliation = variants.map((v) => evaluateVariantReconciliation(input, v, extraction.rows, extraction.lastMaterialRow, extraction.scanReachedWindowEdge, totalRowScanWindow, cfg))

  return {
    rows: extraction.rows,
    reconciliation,
    warnings,
    headerRow: header.row,
    truncated: extraction.scanReachedWindowEdge,
  }
}

// ── ExcelJS bridge ───────────────────────────────────────────────────────

/** Convert a worksheet to a MaterialMatrixGridInput — thin wrapper around
 * workbook-adapter.ts's worksheetToGrid/worksheetToFormulaGrid (same "never
 * reimplement the resolveCell/formula-text code path" discipline as
 * header-parser.ts's own headerParserInputFromWorksheet). */
export function materialMatrixInputFromWorksheet(ws: Worksheet): MaterialMatrixGridInput {
  return { sheet: ws.name, grid: worksheetToGrid(ws), formulaGrid: worksheetToFormulaGrid(ws) }
}

/** Semantic active-range-derived scan bounds for `ws` — a caller building
 * MaterialMatrixOptions from a real worksheet uses this instead of the
 * module's own generous MATERIAL_MATRIX_MAX_ROWS/_COLS defaults when it wants
 * a tighter, sheet-specific window (never ws.dimensions — see module
 * header). */
export function materialMatrixScanBoundsFromWorksheet(ws: Worksheet): { rowTo: number; colTo: number } {
  const range = semanticActiveRange(ws)
  return { rowTo: Math.min(MATERIAL_MATRIX_MAX_ROWS, Math.max(range.lastRow, 1)), colTo: Math.min(MATERIAL_MATRIX_MAX_COLS, Math.max(range.lastColumn, 1)) }
}