// Fertigungs-/Rüst-Profile-Parser + Bindungs-Auflösung (KAR-932 / Multi-QAF-
// Programm P1.4, Epic KAR-925).
//
// Problem this closes (30-backlog-phasenplan.md P1.4 + Master-Prompt §12):
// a Multi-QAF workbook's manufacturing/setup-cost logic is usually NOT one
// independent process list per variant — it is a small number of SHARED
// profile blocks (a manufacturing-cost total, an L-Shape/I-Shape family
// total, a volume-tranche total, a setup-cost total) that each variant's
// Summary row points AT, rather than computes independently. This module
// (a) finds those profile blocks and their Total cells in a Fertigungskosten
// -shaped sheet, and (b) resolves, per variant, WHICH profile its Summary
// Fertigungskosten cell is bound to and HOW (a hardcoded cell reference, a
// literal value that happens to equal a profile total, or a live formula).
//
// ── Core empirical findings this module is built from (10-analyse-*.md) ──
//
// (a) The binding mechanism is USUALLY a hardcoded absolute cell reference in
//     the variant's own Summary Fertigungskosten cell, e.g.
//     `Zusammenfassung!U15 = '=Fertigungskosten!$U$34'` (10-analyse-ncar.md
//     F/E-parallel) — REFERENCE RESOLUTION, never a volume-threshold
//     re-derivation. This module never re-implements the ">50k/>20k/<20k"
//     selection LOGIC (that selection was made once, by hand, when the
//     workbook was built) — it only reads which cell each variant's formula
//     already points at. `parseFormulaReferences`/`classifyFormulaShape`
//     (formula-lineage.ts, this module's core tool per task instruction) do
//     the reference parsing; this module adds NOTHING to that parser, it
//     only interprets the parsed references against the profile-block
//     inventory below.
// (b) Two independent binding SHAPES coexist even within one file
//     (10-analyse-mx.md G): L-Shape is a LIVE `SUMIF($R12:$R42,$K54,U12:U42)`
//     profile total; I-Shape is a HARDCODED LITERAL (`U55 = 41.219...`, no
//     formula at all) that bypasses the process-row chain entirely. Both
//     produce a `SharedCostProfile` here (kind `lShape`/`iShape`); the
//     literal one is found via the SAME-COLUMN heuristic documented on
//     `locateLiteralTotalCandidates` below.
// (c) A 3-block volume-band pattern recurs in 2/4 files (10-analyse-nafta.md
//     E, 10-analyse-ncar.md F/E-parallel): three process-row blocks labeled
//     ">50.000 Stk/a"/">20.000 Stk/a"/"<20.000 Stk/a", each aggregated via
//     its own `SUMIF` into its own Total cell. The label text is READ AS
//     METADATA (`values.threshold` on the resulting `volumeBand`-kind
//     profile — the volume floor above which this tier applies; the bottom
//     "<X" catch-all tier's own threshold is 0, matching the convention
//     synthetic-fixtures.ts's buildMultiRowHeaderFixture already
//     established) — never rebuilt as selection logic; 10-analyse-nafta.md
//     E proves only ONE of the three tranches may be populated in a given
//     file (the other two SUMIFs simply evaluate to an empty/zero range),
//     which this module handles for free since it only ever reports what a
//     SUMIF-shaped Total cell actually contains.
// (d) 10-analyse-nafta.md E: all 5 real variants reference the SAME absolute
//     cell (`Fertigungskosten!$U$29`) — manufacturing cost is, in that file,
//     a single variant-INDEPENDENT value; cost differences come from
//     Material alone. `resolveVariantProfileBindings` below does not assume
//     one profile per variant — multiple variants legitimately resolving to
//     the identical `profileId` is the expected, correctly-modeled outcome.
// (e) 10-analyse-clarwe-eu.md E/F: manufacturing cost is an ADDITIVE
//     two-source formula, `='LV Detail EU'!$U$52+'Rüstkosten EU'!<col>15` —
//     a CONSTANT part (LV Detail EU, same cell for all 26 variants — itself
//     a `SharedCostProfile`, since it has its own SUMIF Total) plus a
//     variant-specific part (Rüstkosten EU, itself a per-variant setup-cost
//     lookup driven by a volume-band lookup table, 10-analyse-clarwe-eu.md
//     E's "Anzahl Lose"-Tabelle — see `locateVolumeBandLookupTable`). This
//     is the real-file evidence for the `formula` bindingEvidence.kind
//     (types.ts VariantProfileBinding doc): the Summary cell's own formula
//     has MORE than one reference / is not a pure passthrough, so the
//     binding is recorded as a live formula rather than a single reference,
//     with a best-effort profileId when at least one referenced cell
//     matches a detected profile Total.
// (f) 10-analyse-nafta.md G / 10-analyse-ncar.md F: BOTH files carry a
//     HIDDEN "Rüstkosten EU" sheet referencing a completely foreign vehicle
//     programme set (unrelated to either file's own active variants) that
//     reads FROM the visible Fertigungskosten sheet but is never itself
//     referenced BY the Summary sheet ("Volltext-Grep …
//     nach 'Rüstkosten' ergab null Treffer") — a genuine orphan, evidence
//     for the "hidden sheets may contain profile logic but must be
//     excluded when nothing points AT them" rule (Master-Prompt §17 +
//     30-backlog-phasenplan.md "Hidden Sheets nur einbinden, wenn eingehende
//     Referenzen existieren"). `sharedCostProfileCandidateSheets` below
//     implements exactly this Incoming-Reference-Check, reusing
//     formula-lineage.ts's `buildColumnLineage` (never re-deriving its own
//     reference-following logic for this — task instruction: "Kern-
//     Werkzeug").
// (g) 10-analyse-clarwe-eu.md itself has NO sheet literally named
//     "Fertigungskosten"/"Manufacturing cost" (it splits into "LV Detail EU"
//     + "Rüstkosten EU") — Master-Prompt §8 ("do not build four
//     filename-specific parsers... a template profile must not be the sole
//     parsing mechanism") therefore rules out a sheet-NAME-only candidate
//     filter. `sharedCostProfileCandidateSheets` combines the
//     module-sheet-names.ts MANUFACTURING alias (fast path, catches
//     "Fertigungskosten"/"Manufacturing cost(s)") with a STRUCTURAL
//     fallback (any sheet formula-lineage shows is transitively referenced
//     from the Summary sheet's own cost formulas, minus sheets already
//     claimed by another known module) — this is what admits "LV Detail EU"
//     without inventing a "LV Detail EU"-specific name pattern.
//
// ── Scope discipline ────────────────────────────────────────────────────
// Pure functions over an already-extracted grid (the block parser) or an
// already-loaded workbook (the ExcelJS bridge functions, same tier as
// formula-lineage.ts's own `buildColumnLineage`) — no ingest wiring, no DB,
// no container assembly (bucketing the returned `SharedCostProfile[]` into
// MultiQafContainer.sharedManufacturingProfiles/setupCostProfiles/
// sharedToolingData by `.kind` is a P2.1 ingest-wiring concern, deliberately
// left to that caller rather than half-built here). `qaf-parser.ts`'s
// standard single-variant Fertigungskosten step parser is NOT imported or
// modified — this module solves a structurally different problem (shared
// profile BLOCKS across many variant columns, not one variant's own process
// list) and reuses only the DE/EN label-anchor vocabulary already
// centralized in module-sheet-names.ts (`MANUFACTURING` alias) and
// summary-metrics.ts (`METRIC_LABELS_DE.manufacturingCosts`), never the
// step-parser's row-walking logic itself.
//
// `location`/`currency`/`commonTooling` (types.ts SharedCostProfileKind) are
// never produced by this module — no real-file evidence for a DEDICATED
// profile block of those kinds was found across the 4 analyzed files (a
// location/currency signal, where present, lives on individual material/
// process rows, not as its own shared-profile block) — left for a future
// item with real evidence, never fabricated here.
//
// Fail-closed guard/cap philosophy (same discipline as formula-lineage.ts):
// a SHARED_FORMULA_UNRESOLVED cell, a missing formula grid, or a value with
// no resolvable binding NEVER become a guessed 'literal'/'cellReference'/
// 'formula' classification — they produce NO VariantProfileBinding entry at
// all (an "unbound" variant) plus an explicit MultiQafWarning, never a
// silently wrong profileId.
//
// tdd-guard: covered by __tests__/profile-parser.test.ts (synthetic
// fixtures) and __tests__/profile-parser.real-files.test.ts (env-gated
// aggregate-only real-file regression).

import type { Worksheet } from 'exceljs'
import { worksheetToGrid, worksheetToFormulaGrid } from '../workbook-adapter'
import { a1, cellToString } from '../summary-parser'
import { parseLocaleNumber, normalizeProcessName } from '../normalizer'
import { SHARED_FORMULA_UNRESOLVED } from '../formula-engine'
import { matchesModuleSheetName, type QafSheetModule } from '../module-sheet-names'
import { METRIC_LABELS_DE } from '../summary-metrics'
import { DEFAULT_COL_FROM as SUMMARY_VARIANT_BAND_COL_FROM } from './header-parser'
import { columnIndexToLetter } from './types'
import type { MultiQafCellRef, MultiQafMoneyAmount, MultiQafWarning, SharedCostProfile, SharedCostProfileKind, VariantProfileBinding } from './types'
import { parseFormulaReferences, classifyFormulaShape, buildColumnLineage, type BuildColumnLineageOptions } from './formula-lineage'
import {
  locateMaterialMatrixHeader,
  materialMatrixInputFromWorksheet,
  materialMatrixScanBoundsFromWorksheet,
  MATERIAL_MATRIX_HEADER_SCAN_MAX_ROWS,
} from './material-matrix-parser'

type FormulaCell = string | null | typeof SHARED_FORMULA_UNRESOLVED

// ── Pure grid input (mirrors header-parser.ts's HeaderParserGridInput) ─────

export interface ProfileParserGridInput {
  /** Sheet this grid was read from — carried into every MultiQafCellRef this
   * module produces. Sheet-agnostic by design (see module header (g)): call
   * once per candidate sheet, never assume a fixed "Fertigungskosten" name. */
  sheet: string
  grid: readonly unknown[][]
  formulaGrid?: readonly FormulaCell[][]
}

export interface ProfileBlockScanOptions {
  /** 0-based inclusive column scan bounds for the block detector. */
  colFrom?: number
  colTo?: number
  /** 1-based inclusive row scan bounds. */
  rowFrom?: number
  rowTo?: number
  /** How many columns to the left of a candidate Total cell to search for
   * its row label (process-family/volume-band text). */
  labelScanWindow?: number
}

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

/** Process-row tables in the real corpus start at column A/B (Site,
 * Zykluszeit, ...) — unlike header-parser.ts's variant-band scan (which
 * deliberately starts at column Q), this module's target is ROW-shaped
 * process/profile data to the LEFT of any variant band, so the default scan
 * starts at column 0. */
export const DEFAULT_PROFILE_COL_FROM = 0
/** Generous headroom past every documented real Fertigungskosten sheet width
 * (max observed: column AL, 10-analyse-mx.md B) — reuses the same 80-column
 * ceiling formula-lineage.ts/workbook-adapter.ts already standardize on
 * (SEMANTIC_ACTIVE_RANGE_MAX_COLS) rather than a second, possibly-diverging
 * constant. */
export const DEFAULT_PROFILE_COL_TO = 79
export const DEFAULT_PROFILE_ROW_FROM = 1
/** Generous headroom past every documented real Fertigungskosten process-row
 * table (max observed: row 81, 10-analyse-mx.md D helper zone). */
export const DEFAULT_PROFILE_ROW_TO = 120
export const DEFAULT_LABEL_SCAN_WINDOW = 20

export const MIN_VOLUME_BAND_LOOKUP_ROWS = 2
export const MAX_VOLUME_BAND_LOOKUP_ROWS = 12

/** Literal-binding match tolerance (task instruction "Literal-Wert der einem
 * Profil-Total ±Toleranz entspricht") — a relative tolerance (handles
 * cross-recompute rounding on many-decimal real values like MX's
 * `46.38818662968414`) with a small absolute floor for near-zero totals. */
export const LITERAL_BINDING_ABS_TOLERANCE = 1e-6
export const LITERAL_BINDING_REL_TOLERANCE = 1e-4

function valuesApproxEqual(a: number, b: number): boolean {
  return Math.abs(a - b) <= Math.max(LITERAL_BINDING_ABS_TOLERANCE, LITERAL_BINDING_REL_TOLERANCE * Math.abs(b))
}

// ── Grid access helpers (module-local — same small-helper-per-module
// discipline header-parser.ts/formula-lineage.ts already established rather
// than a shared cross-module grid-access utility) ──────────────────────────

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 textValue(raw: unknown): string | null {
  const s = cellToString(raw)
  if (s === null || s === '') return null
  if (parseLocaleNumber(raw) !== null && /^-?[\d.,\s]+$/.test(s)) return null
  return s
}

/**
 * Finds a Total-cell's row label within `window` columns to its left.
 * Prefers a cell matching one of the SPECIFIC classification patterns below
 * (shape/setup-cost/volume-band) over the nearest plain-text cell — real-
 * file proof this ordering matters (10-analyse-ncar.md F/E-parallel): a
 * volume-band Total row's OWN label ("> 50.000 Stk/a") sits further left
 * than an intervening currency-code annotation cell ("EUR") the process-row
 * table also carries in between — "nearest text wins" would find "EUR"
 * first and never even look further left at the real label (the SAME class
 * of bug `rowHasManufacturingLabel` above fixes for the Summary sheet's own
 * Fertigungskosten-row lookup, a currency/annotation cell sitting between
 * the real label and the scan target). Falls back to the nearest plain-text
 * cell when nothing in the window matches a specific pattern (the generic
 * `manufacturing`-kind case, e.g. 10-analyse-clarwe-eu.md E's LV Detail EU
 * total, which has no shape/setup/volume-band vocabulary at all).
 */
function findProfileLabel(grid: readonly unknown[][], row1: number, col0: number, window: number): string | null {
  const lo = Math.max(0, col0 - window)
  let nearestGeneric: string | null = null
  for (let c = col0 - 1; c >= lo; c--) {
    const t = textValue(cellAt(grid, row1, c))
    if (t === null) continue
    if (nearestGeneric === null) nearestGeneric = t
    if (SHAPE_LABEL_RE.lShape.test(t) || SHAPE_LABEL_RE.iShape.test(t) || SETUP_COST_LABEL_RE.test(t) || matchVolumeBandLabel(t) !== null) return t
  }
  return nearestGeneric
}

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

// ── Profile-kind classification from a Total cell's row label ──────────────

// Lookbehind/lookahead on LETTERS only (never \b) — real-file proof this
// matters (10-analyse-mx.md G): the actual label text is "PP_L-Shape" /
// "PP_I-Shape" (underscore before the shape letter). `\b` is a transition
// between a word character and a non-word character, and underscore IS a
// word character — `\bl` would never match right after `_`, silently
// missing the single most-cited real-file example this whole module exists
// for. A letter-only boundary treats '_'/'-' the same as any other
// non-letter separator, matching correctly regardless of which separator
// character a real template happens to use.
const SHAPE_LABEL_RE: Record<'lShape' | 'iShape', RegExp> = {
  lShape: /(?<![a-z])l[-_\s]?shape(?![a-z])/i,
  iShape: /(?<![a-z])i[-_\s]?shape(?![a-z])/i,
}
const SETUP_COST_LABEL_RE = /r(?:ü|ue)stkosten|set[-\s]?up[-\s]?cost/i
/** Matches labels like ">50.000 Stk/a" / "<20.000 Stk/a" / "> 20,000 pcs"
 * (10-analyse-nafta.md E, 10-analyse-ncar.md F/E-parallel) — captures
 * direction + threshold, read as METADATA only (module header (c): never
 * rebuilt as selection logic).
 *
 * KAR-932 adversarial review F2: the unit/context group used to be
 * OPTIONAL (`(?:...)?`), so this regex alone matched ANY "<N"/">N"
 * annotation — including an unrelated tolerance note like "< 5" (no unit,
 * no volume-scale magnitude, nothing about quantity at all) sitting near a
 * Total cell. `findProfileLabel` then preferred that tolerance note over
 * the real nearby label (its "specific pattern beats nearest text"
 * ordering, module header comment there), and `classifyProfileKind` turned
 * it into a confidently-wrong `volumeBand` profile with a fabricated
 * threshold. Fix: a raw match alone is no longer sufficient — see
 * `matchVolumeBandLabel` below, which additionally requires either a unit/
 * context anchor OR a plausible volume-scale magnitude before accepting the
 * match as a genuine volume-band label. */
const VOLUME_BAND_LABEL_RAW_RE = /([<>])\s*([\d.,]+)\s*(stk|st(?:ü|ue)ck|pcs?|units?|p\.?\s?a\.?|jahr|year)?/i

/** Below this magnitude, a "<N"/">N" match with NO unit/context anchor is
 * treated as implausible for a volume-band tier (real corpus: every genuine
 * volume-band threshold is in the tens-of-thousands range, e.g. 50000/20000
 * — never single digits like a tolerance annotation's "< 5"). Deliberately
 * generous (not tuned to the real corpus's own 20000/50000 floor) so a
 * smaller-but-still-plausible volume threshold in an unseen file is not
 * rejected just because it lacks a recognized unit token. */
export const MIN_PLAUSIBLE_VOLUME_BAND_THRESHOLD = 1000

interface VolumeBandLabelMatch {
  direction: '<' | '>'
  threshold: number
}

/**
 * Accepts a raw `VOLUME_BAND_LABEL_RAW_RE` match as a genuine volume-band
 * label only when at least one of two independent plausibility signals
 * holds (KAR-932 adversarial review F2 — task instruction: "Einheit ODER
 * Größenordnung ODER Kontext-Anker"):
 *   - a recognized unit/context anchor (Stk/pcs/units/Jahr/p.a./year) was
 *     captured, OR
 *   - the parsed number is at or above `MIN_PLAUSIBLE_VOLUME_BAND_THRESHOLD`
 *     (a bare, unit-less number is still plausible if its MAGNITUDE alone
 *     looks like an annual-volume threshold, not a small tolerance/count).
 * Neither holding (e.g. a bare "< 5" tolerance annotation) means this is
 * NOT read as a volume-band label — falls back to the generic
 * `manufacturing`-kind classification instead of a fabricated threshold.
 */
function matchVolumeBandLabel(label: string): VolumeBandLabelMatch | null {
  const m = VOLUME_BAND_LABEL_RAW_RE.exec(label)
  if (!m) return null
  const threshold = parseLocaleNumber(m[2])
  if (threshold === null) return null
  const hasContextAnchor = m[3] !== undefined
  const isPlausibleMagnitude = threshold >= MIN_PLAUSIBLE_VOLUME_BAND_THRESHOLD
  if (!hasContextAnchor && !isPlausibleMagnitude) return null
  return { direction: m[1] === '<' ? '<' : '>', threshold }
}

interface ProfileKindClassification {
  kind: SharedCostProfileKind
  /** Only set for kind==='volumeBand' — the volume FLOOR above which this
   * tier applies (matches synthetic-fixtures.ts's pre-established
   * buildMultiRowHeaderFixture convention: a ">50.000"-labeled tier's own
   * threshold is 50000; the bottom "<20.000" catch-all tier's threshold is
   * 0, i.e. "applies from 0 up to the next tier's floor" — read as
   * METADATA only, module header (c): never rebuilt as selection logic). */
  volumeBandThreshold?: number
}

function classifyProfileKind(label: string | null): ProfileKindClassification {
  if (label !== null) {
    if (SHAPE_LABEL_RE.lShape.test(label)) return { kind: 'lShape' }
    if (SHAPE_LABEL_RE.iShape.test(label)) return { kind: 'iShape' }
    if (SETUP_COST_LABEL_RE.test(label)) return { kind: 'setupCost' }
    const bandMatch = matchVolumeBandLabel(label)
    if (bandMatch) return { kind: 'volumeBand', volumeBandThreshold: bandMatch.direction === '<' ? 0 : bandMatch.threshold }
  }
  // Safe generic default — never a MORE specific kind than the evidence
  // supports (Master-Prompt §7). Callers only ever reach this classifier for
  // an already sheet-filtered candidate (sharedCostProfileCandidateSheets),
  // so "manufacturing" here is a defensible fallback, not a guess about
  // sheet relevance itself.
  return { kind: 'manufacturing' }
}

// ── Confidence constants (CLAUDE.md "no magic numbers") ─────────────────────

const CONFIDENCE_FORMULA_SPECIFIC_KIND = 0.85
const CONFIDENCE_FORMULA_GENERIC_LABELED = 0.65
const CONFIDENCE_FORMULA_GENERIC_UNLABELED = 0.5
const CONFIDENCE_LITERAL_LABELED = 0.65
const CONFIDENCE_VOLUME_BAND_TABLE = 0.8

// ── Step 1: SUMIF/SUMPRODUCT-shaped Total-cell candidates ──────────────────

interface TotalCellCandidate {
  row1: number
  col0: number
  formula: string | null
  cachedValue: number | string | null
  label: string | null
}

/** Best-effort "component values" on the SAME row as a Total-cell candidate
 * (task instruction "Werte (Total + wo lesbar Komponenten)") — every literal
 * numeric, non-formula cell on that row (excluding the Total cell's own
 * column), keyed by column letter. Deliberately NOT a role-labeled
 * extraction (cycle time/labor rate/machine-hour rate): no consistent
 * per-file column layout for those roles was found across the 4 real files
 * (10-analyse-mx.md G's own column set differs from 10-analyse-ncar.md
 * F/E-parallel's) — a role guess would be exactly the "invented precision"
 * this codebase's fail-closed discipline forbids. In practice this is empty
 * for a pure AGGREGATE total row (real corpus: the total row carries only
 * its label + formula, no sibling literals) and non-empty only for a
 * literal-total row that happens to carry other literal neighbors — both
 * outcomes are honest reflections of what is actually on the row. */
function componentValuesOnRow(input: ProfileParserGridInput, row1: number, ownCol0: number, colFrom: number, colTo: number): Record<string, number> {
  const out: Record<string, number> = {}
  for (let c = colFrom; c <= colTo; c++) {
    if (c === ownCol0) continue
    const formula = formulaAt(input.formulaGrid, row1, c)
    if (typeof formula === 'string' && formula !== '') continue
    if (formula === SHARED_FORMULA_UNRESOLVED) continue
    const n = parseLocaleNumber(cellAt(input.grid, row1, c))
    if (n === null) continue
    out[columnIndexToLetter(c + 1)] = n
  }
  return out
}

function resolvedScanBounds(input: ProfileParserGridInput, options: ProfileBlockScanOptions) {
  const colFrom = options.colFrom ?? DEFAULT_PROFILE_COL_FROM
  const colToRequested = options.colTo ?? DEFAULT_PROFILE_COL_TO
  const rowFrom = options.rowFrom ?? DEFAULT_PROFILE_ROW_FROM
  const rowToRequested = options.rowTo ?? DEFAULT_PROFILE_ROW_TO
  const labelScanWindow = options.labelScanWindow ?? DEFAULT_LABEL_SCAN_WINDOW
  // Clamp to the grid's own real size for scan efficiency — never widens the
  // requested window, only shrinks it when the grid is smaller (same
  // "min(configured, real)" discipline workbook-adapter.ts's
  // semanticActiveRange uses for rowScanLimit).
  const rowTo = Math.min(rowToRequested, input.grid.length || rowToRequested)
  const widestRow = input.grid.reduce((max, row) => Math.max(max, row?.length ?? 0), 0)
  const colTo = widestRow > 0 ? Math.min(colToRequested, widestRow - 1) : colToRequested
  return { colFrom, colTo, rowFrom, rowTo, labelScanWindow }
}

function locateFormulaTotalCandidates(
  input: ProfileParserGridInput,
  bounds: ReturnType<typeof resolvedScanBounds>,
): TotalCellCandidate[] {
  const out: TotalCellCandidate[] = []
  if (!input.formulaGrid) return out
  for (let r = bounds.rowFrom; r <= bounds.rowTo; r++) {
    for (let c = bounds.colFrom; c <= bounds.colTo; c++) {
      const formula = formulaAt(input.formulaGrid, r, c)
      if (typeof formula !== 'string' || formula === '') continue
      if (classifyFormulaShape(formula).kind !== 'sumif_sumproduct_band') continue
      const raw = cellAt(input.grid, r, c)
      const numeric = parseLocaleNumber(raw)
      const cachedValue: number | string | null = numeric !== null ? numeric : typeof raw === 'string' ? raw : null
      const label = findProfileLabel(input.grid, r, c, bounds.labelScanWindow)
      out.push({ row1: r, col0: c, formula, cachedValue, label })
    }
  }
  return out
}

/**
 * I-Shape-style literal Total cells (module header (b)): a literal numeric,
 * NO-formula cell sitting in a column that ALREADY hosted at least one
 * SUMIF/SUMPRODUCT-shaped Total cell elsewhere on this sheet (real-corpus
 * proof: MX's L-Shape Total `U54` (SUMIF) and I-Shape Total `U55` (literal)
 * share column U). A literal cell with NO resolvable row label is never
 * accepted (Master-Prompt §7 "never force an interpretation" — an unlabeled
 * bare number in a total-shaped column is just as likely a stray value as a
 * second profile total, and this module never guesses which).
 */
function locateLiteralTotalCandidates(
  input: ProfileParserGridInput,
  knownTotalColumns: ReadonlySet<number>,
  bounds: ReturnType<typeof resolvedScanBounds>,
): TotalCellCandidate[] {
  const out: TotalCellCandidate[] = []
  for (let r = bounds.rowFrom; r <= bounds.rowTo; r++) {
    for (const c of knownTotalColumns) {
      const formula = formulaAt(input.formulaGrid, r, c)
      if (typeof formula === 'string' && formula !== '') continue // has its own formula — already a formula candidate, or a differently-shaped formula we never guess about.
      if (formula === SHARED_FORMULA_UNRESOLVED) continue // fail-closed: cannot tell, never guess.
      const n = parseLocaleNumber(cellAt(input.grid, r, c))
      if (n === null) continue
      const label = findProfileLabel(input.grid, r, c, bounds.labelScanWindow)
      if (label === null) continue
      out.push({ row1: r, col0: c, formula: null, cachedValue: n, label })
    }
  }
  return out
}

function confidenceFor(candidate: TotalCellCandidate, kindResult: ProfileKindClassification): number {
  if (candidate.formula === null) return CONFIDENCE_LITERAL_LABELED
  if (kindResult.kind !== 'manufacturing') return CONFIDENCE_FORMULA_SPECIFIC_KIND
  return candidate.label !== null ? CONFIDENCE_FORMULA_GENERIC_LABELED : CONFIDENCE_FORMULA_GENERIC_UNLABELED
}

function buildProfileFromCandidate(input: ProfileParserGridInput, candidate: TotalCellCandidate, bounds: ReturnType<typeof resolvedScanBounds>): SharedCostProfile {
  const kindResult = classifyProfileKind(candidate.label)
  const sourceCell = cellRefAt(input.sheet, candidate.row1, candidate.col0)
  const values: Record<string, number | string | null> =
    kindResult.kind === 'volumeBand'
      ? { threshold: kindResult.volumeBandThreshold! }
      : { totalPerUnit: typeof candidate.cachedValue === 'number' ? candidate.cachedValue : null }
  Object.assign(values, componentValuesOnRow(input, candidate.row1, candidate.col0, bounds.colFrom, bounds.colTo))
  return {
    profileId: `${input.sheet}!${sourceCell.cell}`,
    kind: kindResult.kind,
    label: candidate.label,
    sheet: input.sheet,
    values,
    formulaAndCachedValue: { formula: candidate.formula, cachedValue: candidate.cachedValue },
    sourceReferences: [sourceCell],
    confidence: confidenceFor(candidate, kindResult),
  }
}

// ── Volume-band LOOKUP TABLE (distinct from a manufacturing block's own
// volume-band LABEL — module header (e), 10-analyse-clarwe-eu.md E's
// "Anzahl Lose"-Tabelle: a small two-column table mapping an annual-volume
// threshold to a lot count, itself a `SharedCostProfile` of kind
// 'volumeBand'). ──────────────────────────────────────────────────────────

const VOLUME_THRESHOLD_HEADER_RE = /jahresvolumen|annual volume/i
const LOT_COUNT_HEADER_RE = /anzahl.*los|lot|batch/i

export function locateVolumeBandLookupTable(input: ProfileParserGridInput, options: ProfileBlockScanOptions = {}): SharedCostProfile | null {
  const bounds = resolvedScanBounds(input, options)
  for (let r = bounds.rowFrom; r <= bounds.rowTo; r++) {
    for (let c = bounds.colFrom; c < bounds.colTo; c++) {
      const headerA = textValue(cellAt(input.grid, r, c))
      if (headerA === null || !VOLUME_THRESHOLD_HEADER_RE.test(headerA)) continue
      const headerB = textValue(cellAt(input.grid, r, c + 1))
      if (headerB === null || !LOT_COUNT_HEADER_RE.test(headerB)) continue

      const values: Record<string, number | string | null> = {}
      const sourceReferences: MultiQafCellRef[] = [cellRefAt(input.sheet, r, c), cellRefAt(input.sheet, r, c + 1)]
      let n = 0
      const scanTo = Math.min(bounds.rowTo, r + MAX_VOLUME_BAND_LOOKUP_ROWS)
      for (let rr = r + 1; rr <= scanTo; rr++) {
        const lot = parseLocaleNumber(cellAt(input.grid, rr, c + 1))
        if (lot === null) break // pattern ends — first row with no lot-count value.
        const threshold = parseLocaleNumber(cellAt(input.grid, rr, c)) // null tolerated: an open-ended "(sonst)"/"else" last row.
        n++
        values[`band_${n}_upperBound`] = threshold
        values[`band_${n}_lotCount`] = lot
        sourceReferences.push(cellRefAt(input.sheet, rr, c), cellRefAt(input.sheet, rr, c + 1))
      }
      if (n < MIN_VOLUME_BAND_LOOKUP_ROWS) continue // too few rows — keep scanning, might be a false header-text hit.
      return {
        profileId: `${input.sheet}!${a1(r - 1, c)}:${a1(r - 1, c + 1)}`,
        kind: 'volumeBand',
        label: `${headerA} / ${headerB}`,
        sheet: input.sheet,
        values,
        formulaAndCachedValue: null,
        sourceReferences,
        confidence: CONFIDENCE_VOLUME_BAND_TABLE,
      }
    }
  }
  return null
}

// ── Public: block detection over one already-extracted grid ────────────────

/**
 * Detect every shared manufacturing-/setup-cost-/volume-band-profile block
 * on ONE sheet's grid (call once per candidate sheet — see
 * `sharedCostProfileCandidateSheets` for which sheets qualify on a real
 * workbook). Pure — see module header for the full algorithm + evidence.
 */
export function parseSharedCostProfileBlocks(input: ProfileParserGridInput, options: ProfileBlockScanOptions = {}): SharedCostProfile[] {
  const bounds = resolvedScanBounds(input, options)
  const formulaCandidates = locateFormulaTotalCandidates(input, bounds)
  const knownTotalColumns = new Set(formulaCandidates.map((c) => c.col0))
  const literalCandidates = locateLiteralTotalCandidates(input, knownTotalColumns, bounds)

  const profiles: SharedCostProfile[] = [
    ...formulaCandidates.map((c) => buildProfileFromCandidate(input, c, bounds)),
    ...literalCandidates.map((c) => buildProfileFromCandidate(input, c, bounds)),
  ]

  const volumeBandTable = locateVolumeBandLookupTable(input, options)
  if (volumeBandTable) profiles.push(volumeBandTable)

  return profiles.sort((a, b) => a.profileId.localeCompare(b.profileId))
}

// ── Locating the Summary sheet's own Fertigungskosten row ──────────────────

const MANUFACTURING_ROW_LABEL_SYNONYMS = new Set(
  [METRIC_LABELS_DE.manufacturingCosts, 'Manufacturing costs', 'Manufacturing cost', 'Manufactering cost', 'Manufactering costs'].map(
    normalizeProcessName,
  ),
)

/** True when ANY cell strictly left of colTo on this row matches one of the
 * given label synonyms — an exact-match scan over the WHOLE left margin, not
 * a "nearest text cell wins" pick (same class of fix `findProfileLabel`
 * above applies to a Total-cell's own row label). Real-file proof this
 * matters (10-analyse-clarwe-eu.md F): the Summary sheet's own
 * Fertigungskosten row carries the real label at column G
 * ("2. Fertigungskosten") AND a closer, unrelated annotation cell at column L
 * ("Siehe Reiter LV Detail") between it and the variant band — "nearest
 * wins" would find L first and never even look at G. This scan checks every
 * left-margin cell for an EXACT synonym match instead, so an intervening
 * annotation column can never mask the real label. Shared by every
 * label-anchored Summary-row scan in this module (KAR-951 cleanup —
 * `locateManufacturingSummaryRow` and `locateSummaryMoneyRow` used to
 * duplicate this exact loop with only the label set varying). */
function rowHasLabelAmong(grid: readonly unknown[][], row1: number, colTo: number, synonyms: ReadonlySet<string>): boolean {
  for (let c = 0; c < colTo; c++) {
    const t = textValue(cellAt(grid, row1, c))
    if (t !== null && synonyms.has(normalizeProcessName(t))) return true
  }
  return false
}

/** Scans rows `rowFrom..rowTo` (1-based) for the first one whose left margin
 * (`rowHasLabelAmong`) matches `synonyms` — the shared row-scan loop behind
 * both `locateManufacturingSummaryRow` and `locateSummaryMoneyRow`. NOT a
 * fixed row number, since the real row varies by file (module header /
 * `locateSummaryMoneyRow`'s own doc for the per-metric real-file evidence). */
function locateLabelAnchoredRow(
  input: ProfileParserGridInput,
  synonyms: ReadonlySet<string>,
  options: { colFrom?: number; rowFrom?: number; rowTo?: number } = {},
): number | null {
  const colFrom = options.colFrom ?? SUMMARY_VARIANT_BAND_COL_FROM
  const rowFrom = options.rowFrom ?? 1
  const rowTo = options.rowTo ?? 60
  for (let r = rowFrom; r <= rowTo; r++) {
    if (rowHasLabelAmong(input.grid, r, colFrom, synonyms)) return r
  }
  return null
}

/**
 * Locates the 1-based row, within a Summary/Zusammenfassung grid, whose
 * left-margin carries the DE/EN "Fertigungskosten"/"Manufacturing cost(s)"
 * anchor (reusing summary-metrics.ts's own DE label — task instruction:
 * "Registry-Label-Anker ... wiederverwenden") — NOT a fixed row number,
 * since the real row varies by file (14/15/16 across the 4 analyzed files:
 * 10-analyse-clarwe-eu.md F row 15, 10-analyse-mx.md D row 16,
 * 10-analyse-nafta.md E row 16, 10-analyse-ncar.md F/E-parallel row 15).
 */
export function locateManufacturingSummaryRow(
  input: ProfileParserGridInput,
  options: { colFrom?: number; rowFrom?: number; rowTo?: number } = {},
): number | null {
  return locateLabelAnchoredRow(input, MANUFACTURING_ROW_LABEL_SYNONYMS, options)
}

// ── Locating the Summary sheet's Zuschläge/Ausschuss/Angebotspreis rows
// (KAR-951) ──────────────────────────────────────────────────────────────
//
// container-assembly.ts's own module header ("Known scope limits") used to
// document VirtualVariantSummaryTotals.scrap/otherSurcharges/offerBasePrice/
// offerPrice as staying `null` — "no Multi-QAF-side source exists yet". This
// closes that gap the SAME way locateManufacturingSummaryRow above already
// closes it for manufacturingCosts: a label-anchored row scan over the
// Summary sheet's left margin (never a fixed row number — task instruction
// + this module's own established precedent that the real row varies by
// file), reusing summary-metrics.ts's own METRIC_LABELS_DE anchors (task
// instruction: "Registry-Label-Anker wiederverwenden") plus real-file label
// variants this scan needs beyond that registry (see SUMMARY_MONEY_ROW_LABEL_
// SYNONYMS below) — the exact same "registry anchor + documented real-file
// extra variants" shape rowHasManufacturingLabel already established for
// 'Manufacturing costs'/'Manufacturing cost'/'Manufactering cost(s)'.
//
// `scrapMaterial` maps 1:1 onto bridge.ts's own "t.scrap maps to
// scrapMaterial ONLY" doc (materialCosts row's Ausschusskosten sub-line;
// bridge.ts explicitly does NOT read scrapManufacturing from this single
// field). `otherSurcharges` additionally recognizes 'SUMME Overhead'/'Summe
// Overhead'/'Sum overhead'/'Overhead total' — the real KAR-951 livetest
// corpus's own Zuschläge-section (§3./"Zuschläge") total row is labelled
// "SUMME Overhead", not the standard single-column QAF template's "Sonstige
// Zuschläge" (summary-metrics.ts TEMPLATE_CONFIG row 23) — the same class of
// real-file vocabulary drift MANUFACTURING_ROW_LABEL_SYNONYMS already
// tolerates for the Fertigungskosten row.

export type SummaryMoneyRowKind = 'scrapMaterial' | 'otherSurcharges' | 'offerBasePrice' | 'offerBasePriceInclAllocation' | 'offerPrice'

/** Iteration order for the KAR-951 kinds — also the order
 * container-assembly.ts scans them in, kept as one named constant so a
 * caller never has to re-enumerate the union by hand.
 *
 * `offerBasePriceInclAllocation` (KAR-951 F1 fix, review finding): the real
 * KAR-951 livetest ALT/NEU pair's own "ANGEBOTSPREIS" row (`offerPrice`
 * below) is EMPTY on all 26 variants, while the neighboring
 * "ANGEBOTSBASISPREIS inkl. Umlage" row — the live-formula final base price
 * INCLUDING the vorrichtungs-Umlage surcharge — genuinely changes on all 26.
 * That row was never anchored at all before this fix, so a supplier who only
 * edits the Umlage surcharge got zero findings for it. Deliberately its OWN
 * kind/metric, never folded onto `offerBasePrice`/`offerPrice` (see
 * VirtualVariantSummaryTotals.offerBasePriceInclAllocation's own doc
 * comment for why the three are semantically distinct rows). */
export const SUMMARY_MONEY_ROW_KINDS: readonly SummaryMoneyRowKind[] = [
  'scrapMaterial',
  'otherSurcharges',
  'offerBasePrice',
  'offerBasePriceInclAllocation',
  'offerPrice',
]

const SUMMARY_MONEY_ROW_LABEL_SYNONYMS: Record<SummaryMoneyRowKind, ReadonlySet<string>> = {
  scrapMaterial: new Set(
    [METRIC_LABELS_DE.scrapMaterial, 'Ausschusskosten', 'Ausschusskosten Material', 'Scrap costs', 'Scrap costs material', 'Scrap cost material'].map(
      normalizeProcessName,
    ),
  ),
  otherSurcharges: new Set(
    [
      METRIC_LABELS_DE.otherSurcharges,
      'Sonstige Zuschläge',
      'Other surcharges',
      'SUMME Overhead',
      'Summe Overhead',
      'Sum overhead',
      'Overhead total',
      'Total overhead',
    ].map(normalizeProcessName),
  ),
  offerBasePrice: new Set(
    [METRIC_LABELS_DE.quotationBasePrice, 'ANGEBOTSBASISPREIS', 'Angebotsbasispreis', 'Quotation base price', 'QUOTATION BASE PRICE'].map(
      normalizeProcessName,
    ),
  ),
  // KAR-951 F1 fix — the real-file "ANGEBOTSBASISPREIS inkl. Umlage" row
  // (generic QAF-template label text, not a confidential/customer-specific
  // string — see this PR's own confidentiality-grep gate). No
  // summary-metrics.ts METRIC_LABELS_DE registry entry exists for this row
  // (it is not one of that registry's 20 canonical keys), so this kind has
  // no registry anchor to reuse, unlike every other kind in this record.
  offerBasePriceInclAllocation: new Set(
    [
      'ANGEBOTSBASISPREIS inkl. Umlage',
      'Angebotsbasispreis inkl. Umlage',
      'Angebotsbasispreis inkl Umlage',
      'Offer base price incl. allocation',
      'Offer base price including allocation',
      'Quotation base price incl. allocation',
    ].map(normalizeProcessName),
  ),
  offerPrice: new Set(
    [METRIC_LABELS_DE.quotationPrice, 'ANGEBOTSPREIS', 'Angebotspreis', 'Quotation price', 'QUOTATION PRICE', 'Offer price'].map(normalizeProcessName),
  ),
}

/** Locates the 1-based row for one of the KAR-951 Summary money metrics —
 * same signature/shape/scan-window as locateManufacturingSummaryRow. */
export function locateSummaryMoneyRow(
  kind: SummaryMoneyRowKind,
  input: ProfileParserGridInput,
  options: { colFrom?: number; rowFrom?: number; rowTo?: number } = {},
): number | null {
  return locateLabelAnchoredRow(input, SUMMARY_MONEY_ROW_LABEL_SYNONYMS[kind], options)
}

export interface SummaryMoneyRowExtraction {
  /** Keyed by VariantDefinition.stableInternalId. Absent for a variant whose
   * column carried no numeric value on this row (honest absence, never a
   * fabricated 0 — same discipline material-matrix-parser.ts's
   * quantityFactorByVariant omitted-key convention already establishes). */
  byVariant: Readonly<Record<string, MultiQafMoneyAmount>>
  sourceCellByVariant: Readonly<Record<string, MultiQafCellRef>>
}

/** Reads one already-located Summary row's value for every given variant
 * column — same `cellAt`/`parseLocaleNumber` primitives
 * resolveVariantProfileBindings' own literal-binding path already uses.
 * `currencyByVariant` mirrors container-assembly.ts's own
 * `variantCurrency = definition.currency ?? materialCosts?.currency ?? null`
 * fallback (computed by the caller, once, from data this module has no
 * access to) — not re-derived here. */
export function extractSummaryMoneyRow(
  input: ProfileParserGridInput,
  row: number,
  variants: readonly VariantColumnRef[],
  currencyByVariant: ReadonlyMap<string, string | null>,
): SummaryMoneyRowExtraction {
  const byVariant: Record<string, MultiQafMoneyAmount> = {}
  const sourceCellByVariant: Record<string, MultiQafCellRef> = {}
  for (const variant of variants) {
    const value = parseLocaleNumber(cellAt(input.grid, row, variant.column))
    if (value === null) continue
    byVariant[variant.variantId] = { value, currency: currencyByVariant.get(variant.variantId) ?? null }
    sourceCellByVariant[variant.variantId] = cellRefAt(input.sheet, row, variant.column)
  }
  return { byVariant, sourceCellByVariant }
}

// ── Variant → profile binding resolution ────────────────────────────────────

export interface VariantColumnRef {
  variantId: string
  /** 0-based grid column — same indexing as ProfileParserGridInput.grid. */
  column: number
}

export interface ResolveVariantProfileBindingsResult {
  bindings: readonly VariantProfileBinding[]
  warnings: readonly MultiQafWarning[]
}

function unresolvedBindingWarning(variantId: string, sourceCell: MultiQafCellRef, detail: string): MultiQafWarning {
  return {
    code: 'variant_profile_binding_unresolved',
    severity: 'warning',
    message: `Variante ${variantId}: Fertigungs-/Rüstkosten-Profilbindung nicht auflösbar (${sourceCell.cell}) — ${detail}`,
    messageEn: `Variant ${variantId}: manufacturing/setup-cost profile binding could not be resolved (${sourceCell.cell}) — ${detail}`,
    sourceReferences: [sourceCell],
    // KAR-935 F5 fix: set structurally too, not just via the DE message
    // prefix — see MultiQafWarning.variantIds's own doc comment.
    variantIds: [variantId],
  }
}

/**
 * For one variant's literal (no cell-reference) Summary Fertigungskosten
 * value: match it, within tolerance, against every detected profile's Total
 * value (module header (b), 10-analyse-mx.md D's AB literal — note that
 * file's own literal is a non-variant benchmark column, kept here as the
 * general-purpose "literal happens to equal a real profile total" path any
 * genuine variant could also take).
 *
 * KAR-932 adversarial review Zusatzpunkt (b) — "Literal-Bindung Toleranz-
 * Kollision": when TWO OR MORE profile totals fall within tolerance of the
 * same literal value, the CLOSEST one (smallest |value-total| distance) is
 * picked deterministically — never "the first one" in array order, which
 * would silently depend on `profiles`' incoming sort order rather than
 * which match is actually the better fit. Only a genuine TIE (two or more
 * candidates sharing the exact same minimal distance) is treated as
 * ambiguous and reported as unbound + warning instead of guessed (same
 * "detect, do not silently merge" discipline identity.ts's
 * detectCanonicalKeyCollisions established).
 */
function resolveLiteralBinding(
  variant: VariantColumnRef,
  formulaText: string | null,
  rawValue: unknown,
  profiles: readonly SharedCostProfile[],
  sourceCell: MultiQafCellRef,
  bindings: VariantProfileBinding[],
  warnings: MultiQafWarning[],
): void {
  const value = parseLocaleNumber(rawValue)
  if (value === null) {
    warnings.push(unresolvedBindingWarning(variant.variantId, sourceCell, 'Zelle enthält weder Formel noch numerischen Literalwert.'))
    return
  }
  const candidates = profiles
    .map((p) => {
      const total = p.formulaAndCachedValue?.cachedValue
      return typeof total === 'number' ? { profile: p, total, distance: Math.abs(value - total) } : null
    })
    .filter((c): c is { profile: SharedCostProfile; total: number; distance: number } => c !== null && valuesApproxEqual(value, c.total))
  if (candidates.length === 0) {
    warnings.push(
      unresolvedBindingWarning(variant.variantId, sourceCell, `Literalwert ${value} entspricht keinem erkannten Profil-Total (nie geraten).`),
    )
    return
  }
  const minDistance = Math.min(...candidates.map((c) => c.distance))
  const closest = candidates.filter((c) => c.distance === minDistance)
  if (closest.length > 1) {
    warnings.push({
      code: 'ambiguous_literal_profile_binding',
      severity: 'warning',
      message: `Variante ${variant.variantId}: Literalwert ${value} passt innerhalb Toleranz zu ${closest.length} verschiedenen Profil-Totals mit IDENTISCHEM Abstand (${closest
        .map((c) => c.profile.profileId)
        .join(', ')}) — Bindung als unbound behandelt statt geraten (Gleichstand, kein deterministisch bestes Match).`,
      sourceReferences: [sourceCell],
      variantIds: [variant.variantId],
      // KAR-935 F4 fix: an ambiguous binding is a genuine "which profile is
      // this variant's own?" identity question, not just a missing-data gap
      // — worth a human's look before the container is trusted.
      reviewRelevant: true,
    })
    return
  }
  const matched = closest[0]!.profile
  const total = closest[0]!.total
  const delta = closest[0]!.distance
  bindings.push({
    variantId: variant.variantId,
    profileId: matched.profileId,
    bindingEvidence: {
      kind: 'literal',
      source: null,
      detail:
        `Literalwert ${value}${formulaText ? ` (Formel ohne Zellreferenz: "${formulaText}")` : ' (kein Formeltext)'} ` +
        `entspricht Profil-Total ${matched.profileId} (${total}) ${delta === 0 ? 'exakt' : `innerhalb Toleranz (Δ=${delta})`} — Match-Confidence ${matched.confidence}.`,
    },
  })
}

/**
 * Resolve, per variant, which SharedCostProfile its Summary
 * Fertigungskosten cell is bound to and via which mechanism
 * (bindingEvidence.kind — see module header (a)/(b)/(e)). `manufacturingRow`
 * is the 1-based Summary row located via `locateManufacturingSummaryRow`
 * (passed explicitly rather than re-located per variant — same cost/row for
 * every variant in a Summary sheet). Never fabricates a binding: a variant
 * whose cell cannot be resolved to any detected profile gets NO
 * VariantProfileBinding entry (VariantProfileBinding.profileId is
 * non-nullable in types.ts — an "unknown profile" is structurally
 * inexpressible there, correctly so) plus an explicit MultiQafWarning.
 */
export function resolveVariantProfileBindings(
  summary: ProfileParserGridInput,
  manufacturingRow: number,
  variants: readonly VariantColumnRef[],
  profiles: readonly SharedCostProfile[],
): ResolveVariantProfileBindingsResult {
  const bindings: VariantProfileBinding[] = []
  const warnings: MultiQafWarning[] = []

  const profileByCell = new Map<string, SharedCostProfile>()
  for (const p of profiles) {
    for (const ref of p.sourceReferences) {
      if (ref.row === null || ref.column === null) continue
      // ParsedCellReference (formula-lineage.ts) is 1-based row+column;
      // MultiQafCellRef.row is 0-based (header-parser.ts's own cellRef
      // convention, reused verbatim here — see cellRefAt) while .column is
      // already 1-based — normalize once to a shared 1-based lookup key.
      profileByCell.set(`${ref.sheet}!${ref.row + 1}:${ref.column}`, p)
    }
  }

  for (const variant of variants) {
    const formula = formulaAt(summary.formulaGrid, manufacturingRow, variant.column)
    const sourceCell = cellRefAt(summary.sheet, manufacturingRow, variant.column)

    if (formula === SHARED_FORMULA_UNRESOLVED) {
      warnings.push(unresolvedBindingWarning(variant.variantId, sourceCell, 'Zelle ist eine nicht auflösbare Shared-Formula-Slave-Referenz.'))
      continue
    }

    if (typeof formula === 'string' && formula !== '') {
      const refs = parseFormulaReferences(formula)
      if (refs.length === 0) {
        // A formula string with zero cell references ("=100") behaves like a
        // literal for binding-mechanism purposes (module header (b)).
        resolveLiteralBinding(variant, formula, cellAt(summary.grid, manufacturingRow, variant.column), profiles, sourceCell, bindings, warnings)
        continue
      }

      const shape = classifyFormulaShape(formula).kind
      if (refs.length === 1 && shape === 'direct_reference') {
        const ref = refs[0]!
        const targetSheet = ref.sheet ?? summary.sheet
        const matched = profileByCell.get(`${targetSheet}!${ref.fromRow}:${ref.fromColumn}`)
        if (matched) {
          bindings.push({
            variantId: variant.variantId,
            profileId: matched.profileId,
            bindingEvidence: {
              kind: 'cellReference',
              source: matched.sourceReferences[0] ?? null,
              detail: `Direkte, hartcodierte Zellreferenz auf Profil-Total ${matched.profileId} ("${formula}").`,
            },
          })
        } else {
          warnings.push(
            unresolvedBindingWarning(variant.variantId, sourceCell, `Direkte Zellreferenz "${formula}" trifft kein erkanntes Profil-Total.`),
          )
        }
        continue
      }

      // Multiple references and/or a non-pure-passthrough shape (module
      // header (e)'s additive `A+B` pattern, or a live in-cell aggregation)
      // — a live FORMULA binding, best-effort matched against any
      // referenced profile Total.
      const matchedProfile = refs
        .map((ref) => profileByCell.get(`${ref.sheet ?? summary.sheet}!${ref.fromRow}:${ref.fromColumn}`))
        .find((p): p is SharedCostProfile => p !== undefined)
      if (matchedProfile) {
        bindings.push({
          variantId: variant.variantId,
          profileId: matchedProfile.profileId,
          bindingEvidence: {
            kind: 'formula',
            source: matchedProfile.sourceReferences[0] ?? null,
            detail: `Live-Formel referenziert u.a. Profil-Total ${matchedProfile.profileId} ("${formula}").`,
          },
        })
      } else {
        warnings.push(
          unresolvedBindingWarning(
            variant.variantId,
            sourceCell,
            `Live-Formel "${formula}" referenziert kein erkanntes Profil-Total (nie geraten).`,
          ),
        )
      }
      continue
    }

    // No formula at all — pure literal value (or a genuinely empty cell,
    // handled by resolveLiteralBinding's own null-value branch).
    resolveLiteralBinding(variant, null, cellAt(summary.grid, manufacturingRow, variant.column), profiles, sourceCell, bindings, warnings)
  }

  return { bindings, warnings }
}

// ── Summary-Rekonziliation Profil ↔ Summary-Wert je Variante (task
// instruction point 4 — extraction/check only, not the comparison itself,
// which is P2.5/P2.6) ───────────────────────────────────────────────────────

export interface ProfileSummaryReconciliationResult {
  variantId: string
  profileId: string | null
  summaryValue: number | null
  profileTotalValue: number | null
  /** null when this variant is unbound, or when either side's value could
   * not be read at all — never a guessed true/false (fail-closed, same
   * discipline as formula-lineage.ts's DetailColumnLineageResult tri-state). */
  matches: boolean | null
}

/**
 * Reconciles each variant's OWN Summary Fertigungskosten value against its
 * bound profile's Total value. A `'formula'`-kind binding may legitimately
 * combine the matched profile with OTHER additive terms (module header
 * (e)'s constant-plus-variant-specific pattern) — an exact 1:1 match is not
 * expected there, so `matches` stays `null` (not a false mismatch) for that
 * evidence kind; only `'cellReference'`/`'literal'` bindings (where the
 * Summary value IS supposed to equal the profile Total exactly) get a hard
 * true/false verdict.
 */
export function reconcileProfileToSummary(
  summary: ProfileParserGridInput,
  manufacturingRow: number,
  variants: readonly VariantColumnRef[],
  bindings: readonly VariantProfileBinding[],
  profiles: readonly SharedCostProfile[],
): readonly ProfileSummaryReconciliationResult[] {
  const profileById = new Map(profiles.map((p) => [p.profileId, p] as const))
  const bindingByVariant = new Map(bindings.map((b) => [b.variantId, b] as const))

  return variants.map((variant) => {
    const summaryValue = parseLocaleNumber(cellAt(summary.grid, manufacturingRow, variant.column))
    const binding = bindingByVariant.get(variant.variantId)
    if (!binding) return { variantId: variant.variantId, profileId: null, summaryValue, profileTotalValue: null, matches: null }

    const profile = profileById.get(binding.profileId)
    const total = profile?.formulaAndCachedValue?.cachedValue
    const profileTotalValue = typeof total === 'number' ? total : null

    if (summaryValue === null || profileTotalValue === null || binding.bindingEvidence.kind === 'formula') {
      return { variantId: variant.variantId, profileId: binding.profileId, summaryValue, profileTotalValue, matches: null }
    }
    return {
      variantId: variant.variantId,
      profileId: binding.profileId,
      summaryValue,
      profileTotalValue,
      matches: valuesApproxEqual(summaryValue, profileTotalValue),
    }
  })
}

// ── Getrennte Befund-Typen (Master-Prompt §12 report list, task instruction
// point 4 — CLASSIFICATION VOCABULARY ONLY; the actual comparison across two
// containers is P2.5). Mapping onto §12's 8-item list: items 1 ("changed
// manufacturing process") and 2 ("changed profile total") both surface as
// `profile_value_changed` here (this module's own extraction granularity
// stops at the profile Total, not individual process rows — see
// componentValuesOnRow's doc comment); item 4 ("variant moved to another
// profile") is `profile_binding_changed`; item 3 is `volume_band_changed`;
// items 5/6 are `profile_added`/`profile_removed`; item 7 is
// `inconsistent_binding`; item 8 is `summary_reconciliation_mismatch`
// (this module's own `reconcileProfileToSummary` above is the extraction
// step that fact feeds). ─────────────────────────────────────────────────

export type ManufacturingProfileFindingKind =
  | 'profile_value_changed'
  | 'profile_binding_changed'
  | 'volume_band_changed'
  | 'profile_added'
  | 'profile_removed'
  | 'inconsistent_binding'
  | 'summary_reconciliation_mismatch'

// ── ExcelJS bridge (mirrors header-parser.ts's headerParserInputFromWorksheet
// / formula-lineage.ts's buildColumnLineage tier — pure over an already-
// loaded workbook, no file IO here) ─────────────────────────────────────────

export function profileParserInputFromWorksheet(ws: Worksheet): ProfileParserGridInput {
  return { sheet: ws.name, grid: worksheetToGrid(ws), formulaGrid: worksheetToFormulaGrid(ws) }
}

function isHiddenWorksheet(ws: Worksheet): boolean {
  return ws.state === 'hidden' || ws.state === 'veryHidden'
}

const NON_MANUFACTURING_MODULES: readonly QafSheetModule[] = ['SUMMARY', 'MATERIAL', 'SBM', 'LOGISTICS', 'RMR', 'LC_CN', 'CO2E']

/** Used ONLY to decide whether an unreached hidden sheet is worth reporting
 * as a candidate orphan at all — see sharedCostProfileCandidateSheets' own
 * doc comment for why this is needed alongside the structural check. */
const HIDDEN_SHEET_PROFILE_NAME_HINT_RE = /r(?:ü|ue)stkosten|set[-\s]?up[-\s]?cost|fertigung|manufactur/i

// ── Material-/BOM-Matrix domain exclusion (KAR-932 adversarial review F3) ──
//
// A Multi-QAF workbook's shared MATERIAL/BOM sheet (e.g. "Material", "BOM
// Detail EU" — 10-analyse-clarwe-eu.md D/C.2: BOM Detail EU IS that file's
// material matrix; its Summary-sheet Materialkosten row references it
// directly, `='BOM Detail EU'!<col>159`) must NEVER be admitted as a
// manufacturing-/setup-cost-profile SOURCE sheet — even though formula-
// lineage correctly shows it transitively reached from the Summary sheet
// (it legitimately is, just for MATERIAL cost, not manufacturing cost).
//
// Real-file evidence this was previously missed: NON_MANUFACTURING_MODULES'
// MATERIAL exclusion only matches worksheet tab names containing the
// substring "material" (module-sheet-names.ts) — "BOM Detail EU" doesn't
// contain that substring, so it fell through to the structural lineage-
// reached fallback below and got admitted; every SUMPRODUCT-shaped row-
// total formula on its ~159 material rows (weighted-cost aggregations, not
// manufacturing totals) was then misread as an independent
// SharedCostProfile — the measured root cause of a profile-count explosion
// for that file (the large majority of that file's over-counted profiles
// resolved to this one sheet).
//
// Two independent signals, either sufficient (same "name OR structural"
// discipline as MANUFACTURING's own candidacy check below):
//   - NAME FAMILY: the observed real BOM-sheet tab-name family ("bom").
//     Module header (g)'s "no filename-specific parser" instruction is
//     about PARSING logic, not about recognizing an already-known real
//     tab-name family for EXCLUSION — the opposite risk direction (false
//     inclusion, not false content parsing).
//   - STRUCTURAL: `material-matrix-parser.ts`'s own `locateMaterialMatrixHeader`
//     (KAR-931, merged) finds a material-matrix header row on this sheet —
//     reused DIRECTLY here (not duplicated) now that PR #303 has landed, per
//     this fix's own task instruction ("nutze die Anker-Logik konsistent mit
//     material-matrix-parser aus PR #303 falls schon gemerged"). That
//     locator's own gate (`componentLabel` located AND at least one of
//     `unitCostAw`/`unitCostBw` located) is the anchor that is actually
//     MATERIAL-domain-specific.
//
//     A two-anchor check (position-number + component-label alone, this
//     fix's own FIRST attempt) is NOT sufficient and was corrected during
//     this fix's real-file verification: the QAF template reuses that SAME
//     row-identity vocabulary on a genuine MANUFACTURING/process-step sheet
//     too (10-analyse-clarwe-eu.md's own "LV Detail EU" sheet header row:
//     "Positionsnummer Fertigungsschritt" + "Teilebenennung" columns,
//     confirmed on the real file — a two-anchor check misclassified that
//     genuine profile sheet as Material domain and silently dropped it). A
//     manufacturing/process-step sheet prices by cycle-time/labor-rate/
//     machine-hour-rate (Zykluszeit/MSS/Rüstkosten pro Stück), never by a
//     per-unit material cost — `locateMaterialMatrixHeader`'s own
//     `unitCostAw`/`unitCostBw` gate is what correctly separates "BOM Detail
//     EU" from "LV Detail EU" on the real file this fix was measured
//     against.
const MATERIAL_BOM_SHEET_NAME_HINT_RE = /\bbom\b/i

function isMaterialBomDomainSheet(ws: Worksheet): boolean {
  if (MATERIAL_BOM_SHEET_NAME_HINT_RE.test(ws.name)) return true
  const input = materialMatrixInputFromWorksheet(ws)
  const bounds = materialMatrixScanBoundsFromWorksheet(ws)
  const headerRowTo = Math.min(bounds.rowTo, MATERIAL_MATRIX_HEADER_SCAN_MAX_ROWS)
  return locateMaterialMatrixHeader(input, DEFAULT_PROFILE_COL_FROM, bounds.colTo, DEFAULT_PROFILE_ROW_FROM, headerRowTo) !== null
}

// ── Fail-closed lineage-cap handling (KAR-932 adversarial review F1) ───────
//
// `sharedCostProfileCandidateSheets` used to derive reachability SOLELY from
// `graph.edges`, ignoring `buildColumnLineage`'s own fail-closed cap signals
// (`scanIncompleteSheets`/`rangeTruncatedSheets`/`depthCappedSheets` —
// formula-lineage.ts's `isOrphanedDetailColumn` already treats these as
// "cannot prove a negative" at COLUMN granularity; this module never
// consulted them at all at SHEET granularity). A capped trace is NOT proof
// of "definitely no reference": the exact information a guard/cap cuts off
// (a formula cell past MAX_FORMULA_CELLS_PER_SHEET, content past the
// active-range scan window, or a chain beyond MAX_LINEAGE_DEPTH) is exactly
// what could have contained the missing reference. Two failure modes this
// caused: (a) a legitimate, non-name-matched profile sheet silently
// vanishing from `included` with NO warning at all (the "LV Detail EU"-
// style structural-fallback case, module header (g)); (b) a hidden sheet
// confidently reported as `orphaned_hidden_sheet` ("zero incoming
// references") when the trace that would have proven otherwise was simply
// never completed.
//
// Fix: `lineageCapped` below is a whole-graph flag, not a per-sheet one —
// `buildColumnLineage` runs a SINGLE connected trace from every Summary
// formula cell; a cap tripping ANYWHERE in that trace (e.g. on an
// intermediate sheet one hop before reaching the sheet in question) can
// hide a path to ANY not-yet-reached sheet, not only the sheet the cap was
// recorded against (see `ColumnLineageGraph`'s own doc comments — the
// capped set names the sheet where indexing/depth stopped, not necessarily
// the target sheet whose reachability that stoppage left unresolved). A
// per-sheet-only check (mirroring `unknownReasonForSheet`'s narrower,
// column-granularity precedent) would miss exactly this class of case, so
// this module deliberately uses the more conservative whole-graph signal.
function lineageIsCapped(graph: { scanIncompleteSheets: readonly string[]; rangeTruncatedSheets: readonly string[]; depthCappedSheets: readonly string[] }): boolean {
  return graph.scanIncompleteSheets.length > 0 || graph.rangeTruncatedSheets.length > 0 || graph.depthCappedSheets.length > 0
}

function describeLineageCapSignals(graph: {
  scanIncompleteSheets: readonly string[]
  rangeTruncatedSheets: readonly string[]
  depthCappedSheets: readonly string[]
}): string {
  const parts: string[] = []
  if (graph.scanIncompleteSheets.length > 0) parts.push(`scanIncomplete: ${graph.scanIncompleteSheets.join(', ')}`)
  if (graph.rangeTruncatedSheets.length > 0) parts.push(`rangeTruncated: ${graph.rangeTruncatedSheets.join(', ')}`)
  if (graph.depthCappedSheets.length > 0) parts.push(`depthCapped: ${graph.depthCappedSheets.join(', ')}`)
  return parts.join('; ')
}

export interface SharedCostProfileCandidateSheets {
  included: readonly string[]
  orphanedHidden: readonly string[]
  warnings: readonly MultiQafWarning[]
}

/**
 * Selects which sheets are candidates for manufacturing-/setup-cost-profile
 * extraction (module header (g): name-match OR formula-lineage-reached,
 * minus sheets already claimed by another known module OR the Material-/
 * BOM-matrix domain — F3 above), and separately identifies + EXCLUDES
 * orphaned hidden sheets (module header (f) — the Orphan-Hidden-Sheet-
 * Ausschluss / Incoming-Reference-Check task instruction), fail-closed
 * against the underlying lineage trace's own cap signals (F1 above).
 * Reuses formula-lineage.ts's `buildColumnLineage` as the SOLE reference-
 * following mechanism (task instruction: "Kern-Werkzeug") — this function
 * adds no reference-parsing logic of its own.
 *
 * For a HIDDEN sheet, name-matching alone is NEVER sufficient to include it
 * — a hidden "Rüstkosten EU" sheet name-matches perfectly in both real
 * orphan cases (10-analyse-nafta.md G, 10-analyse-ncar.md F) and must still
 * be excluded. Only `buildColumnLineage` actually showing the Summary
 * sheet's own formulas transitively reaching it admits a hidden sheet.
 *
 * `lineageOptions` is a passthrough to `buildColumnLineage` (tests only —
 * mirrors formula-lineage.test.ts's own `BuildColumnLineageOptions`
 * overrides, needed to exercise the F1 cap-signal fail-closed paths without
 * an 8000-formula-cell/300-row/40-hop real-sized fixture).
 */
export function sharedCostProfileCandidateSheets(
  wb: { worksheets: readonly Worksheet[] },
  lineageOptions?: BuildColumnLineageOptions,
): SharedCostProfileCandidateSheets {
  const graph = buildColumnLineage({ worksheets: wb.worksheets as Worksheet[] }, lineageOptions)
  const lineageReachedSheets = new Set(graph.edges.map((e) => e.detailSheet))
  const lineageCapped = lineageIsCapped(graph)

  const included: string[] = []
  const orphanedHidden: string[] = []
  const warnings: MultiQafWarning[] = []

  for (const ws of wb.worksheets) {
    const nameMatches = matchesModuleSheetName(ws.name, 'MANUFACTURING')
    const isOtherModule = NON_MANUFACTURING_MODULES.some((m) => matchesModuleSheetName(ws.name, m))
    const lineageReached = lineageReachedSheets.has(ws.name)

    if (isHiddenWorksheet(ws)) {
      if (lineageReached && !isOtherModule && !isMaterialBomDomainSheet(ws)) {
        included.push(ws.name)
        continue
      }
      // Not (confirmedly) referenced by anything the Summary sheet's own
      // cost formulas transitively touch — before reporting anything about
      // it, check whether this hidden sheet even LOOKS like a profile
      // source at all (never invent an "orphaned profile sheet" finding for
      // a hidden sheet that plainly contains nothing profile-shaped). Two
      // independent signals, either sufficient:
      //   - STRUCTURAL: a SUMIF/SUMPRODUCT-shaped Total cell is present
      //     (parseSharedCostProfileBlocks finds something) — catches a
      //     profile-shaped sheet regardless of its name.
      //   - NAME HINT: the real orphan case (10-analyse-nafta.md G,
      //     10-analyse-ncar.md F) is a hidden sheet literally named
      //     "Rüstkosten EU" whose OWN Total cell is a multi-term arithmetic
      //     chain (`=(Fertigungskosten!K12*Fertigungskosten!L12+...)`), NOT
      //     SUMIF-shaped — the structural signal alone misses exactly the
      //     two real files this check exists for. A name hint is only ever
      //     used here to decide whether to REPORT a candidacy (never as the
      //     sole mechanism for extracting content, Master-Prompt §8 — the
      //     actual include/exclude decision above is 100% formula-lineage-
      //     driven).
      const structuralHit = parseSharedCostProfileBlocks(profileParserInputFromWorksheet(ws)).length > 0
      const nameHint = HIDDEN_SHEET_PROFILE_NAME_HINT_RE.test(ws.name)
      if (structuralHit || nameHint) {
        if (lineageCapped) {
          // F1: cannot prove a NEGATIVE here — a guard/cap fired somewhere
          // in the trace, so "no edge found" is not the same as "confirmed
          // no reference". Excluded (safer default for a hidden sheet), but
          // reported HONESTLY as unknown, never as a proven orphan.
          warnings.push({
            code: 'hidden_sheet_reachability_unknown',
            severity: 'warning',
            message: `Verstecktes Sheet "${ws.name}" enthält Fertigungs-/Rüstkosten-Profil-Struktur; ob es von einer Zusammenfassung-Formel transitiv referenziert wird, ist NICHT prüfbar (Formel-Lineage-Guard/-Cap ausgelöst — ${describeLineageCapSignals(graph)}) — konservativ ausgeschlossen, aber NICHT als bewiesener Orphan gemeldet.`,
            messageEn: `Hidden sheet "${ws.name}" contains manufacturing/setup-cost profile structure; whether it is transitively referenced by any Summary formula could NOT be checked (formula-lineage guard/cap triggered) — conservatively excluded, but NOT reported as a proven orphan.`,
            sourceReferences: [],
          })
        } else {
          orphanedHidden.push(ws.name)
          warnings.push({
            // Same code name synthetic-fixtures.ts's buildMultiRowHeaderFixture
            // already establishes for this exact real-file pattern
            // (10-analyse-ncar.md F) — kept consistent so downstream
            // consumers can match on one stable code regardless of which
            // module produced it.
            code: 'orphaned_hidden_sheet',
            severity: 'warning',
            message: `Verstecktes Sheet "${ws.name}" enthält Fertigungs-/Rüstkosten-Profil-Struktur, wird aber von keiner Zusammenfassung-Formel transitiv referenziert (Incoming-Reference-Check negativ) — als Orphan ausgeschlossen.`,
            messageEn: `Hidden sheet "${ws.name}" contains manufacturing/setup-cost profile structure but is never transitively referenced by any Summary formula (incoming-reference check negative) — excluded as an orphan.`,
            sourceReferences: [],
          })
        }
      }
      continue
    }

    if (nameMatches) {
      included.push(ws.name)
      continue
    }
    if (lineageReached) {
      if (!isOtherModule && !isMaterialBomDomainSheet(ws)) included.push(ws.name)
      continue
    }
    // F1: not lineage-reached AND not name-matched. Under a clean
    // (uncapped) trace this is a confident exclusion (unchanged behavior).
    // Under a capped trace, conservatively include it anyway when it LOOKS
    // profile-shaped (same dual structural/name-hint signal the hidden-
    // sheet branch above uses) — never silently drop a possibly-legitimate
    // profile sheet just because a guard/cap hid the proof of its own
    // reachability (module header (g)'s "LV Detail EU" structural-fallback
    // case is exactly the shape of sheet this protects).
    if (lineageCapped && !isOtherModule && !isMaterialBomDomainSheet(ws)) {
      const structuralHit = parseSharedCostProfileBlocks(profileParserInputFromWorksheet(ws)).length > 0
      const nameHint = HIDDEN_SHEET_PROFILE_NAME_HINT_RE.test(ws.name)
      if (structuralHit || nameHint) {
        included.push(ws.name)
        warnings.push({
          code: 'lineage_reachability_unknown_conservative_include',
          severity: 'warning',
          message: `Sheet "${ws.name}" wurde nicht über Formel-Lineage als erreicht bestätigt, aber ein Formel-Lineage-Guard/-Cap wurde ausgelöst (${describeLineageCapSignals(graph)}) — Nicht-Erreichbarkeit ist NICHT beweisbar. Konservativ als Profil-Kandidat einbezogen statt still verworfen.`,
          messageEn: `Sheet "${ws.name}" was not confirmed reached via formula lineage, but a formula-lineage guard/cap was triggered — unreachability could not be proven. Conservatively included as a profile candidate instead of being silently dropped.`,
          sourceReferences: [],
        })
      }
    }
  }

  return { included, orphanedHidden, warnings }
}

export interface SharedCostProfilesFromWorkbookResult {
  profiles: readonly SharedCostProfile[]
  includedSheets: readonly string[]
  orphanedHiddenSheets: readonly string[]
  warnings: readonly MultiQafWarning[]
}

/**
 * Full orchestration: candidate-sheet selection (incl. orphan-hidden-sheet
 * exclusion) + block detection on every included sheet, merged into one
 * sorted `SharedCostProfile[]`. Still no ingest wiring/container assembly —
 * see module header "Scope discipline".
 */
export function sharedCostProfilesFromWorkbook(
  wb: { worksheets: readonly Worksheet[] },
  options?: ProfileBlockScanOptions,
  lineageOptions?: BuildColumnLineageOptions,
): SharedCostProfilesFromWorkbookResult {
  const { included, orphanedHidden, warnings } = sharedCostProfileCandidateSheets(wb, lineageOptions)
  const includedSet = new Set(included)
  const profiles: SharedCostProfile[] = []
  for (const ws of wb.worksheets) {
    if (!includedSet.has(ws.name)) continue
    profiles.push(...parseSharedCostProfileBlocks(profileParserInputFromWorksheet(ws), options))
  }
  profiles.sort((a, b) => a.profileId.localeCompare(b.profileId))
  return { profiles, includedSheets: included, orphanedHiddenSheets: orphanedHidden, warnings }
}