// Multi-QAF container assembly + VirtualQafVariant generation (KAR-935 /
// Multi-QAF-Programm P2.1, Epic KAR-925).
//
// Problem this closes (30-backlog-phasenplan.md P2.1 + Master-Prompt §6):
// the 6 P1 modules (identity.ts/serialization.ts/bridge.ts KAR-929,
// header-parser.ts/column-classifier.ts KAR-930, material-matrix-parser.ts
// KAR-931, profile-parser.ts KAR-932, formula-lineage.ts KAR-933,
// template-fingerprint.ts KAR-934) each parse ONE facet of a Multi-QAF
// workbook in isolation — none of them produce a single, complete
// `MultiQafContainer`. This module is the orchestrator that calls them in
// the right order and assembles ONE `MultiQafContainer` (assembleMultiQafContainer),
// plus the first `VirtualQafVariant[]` generation pass over that finished
// container (generateVirtualVariants) — the two Master-Prompt §6 deliverables
// this PR closes. It is still NOT the ingest wiring itself (P2.1's own task
// brief: "erstes Ingest-Wiring hinter Flag") — the actions.ts call site is a
// thin try/catch around `resolveMultiQafContainerIngest` below, fail-closed
// to today's rejection when assembly throws.
//
// ── Orchestration order (Master-Prompt §5 architecture diagram) ────────────
//   1. multiQafTemplateFingerprintInputFromExcelJs (template-fingerprint.ts,
//      KAR-934) — reused VERBATIM for its own SUMMARY-then-MATERIAL
//      sheet-fallback cascade (module header there: "Datei 3/NAFTA... stores
//      its variant/dimension block [in MATERIAL] instead, at a different
//      column offset than SUMMARY's own default"). This is the container's
//      SOLE source of variant IDENTITY (activeVariants/inactiveVariants,
//      variantDimensions) — never re-derived independently here.
//   2. fingerprintMultiQafTemplate + toMultiQafContainerFingerprint
//      (template-fingerprint.ts) — templateFingerprint field, WITH
//      classification (KAR-934 adversarial review F2 lesson: `family` alone
//      is not sufficient).
//   3. Material-matrix parsing (material-matrix-parser.ts, KAR-931) — run
//      SEPARATELY on the MATERIAL/BOM sheet's own re-parsed variant header
//      block (material-matrix-parser.ts's own precondition: "resolved
//      per-sheet, not copied verbatim from the Summary sheet's own column
//      letters"), via the SAME widened-column-scan retry step 1 uses
//      internally (template-fingerprint.ts's locateVariantHeaderBlockOnSheet,
//      exported for this reuse — KAR-935 additive change, zero behavior
//      change for its own pre-existing caller).
//   4. Shared cost profiles + variant->profile bindings (profile-parser.ts,
//      KAR-932) — profile BLOCK detection is workbook-level and independent
//      of variant identity; binding RESOLUTION needs Summary-sheet-local
//      variant column positions, which are only available when step 1's
//      identity source WAS the Summary sheet (see "Known scope limits"
//      below for the Material-fallback case).
//   5. Formula lineage (formula-lineage.ts, KAR-933) — feeds
//      summaryAggregation.formulaLineageNotes + merges its own warnings.
//   6. generateVirtualVariants(container) — a PURE function over the
//      now-finished container's own fields (never re-reads the workbook),
//      per Master-Prompt §6's own VirtualQafVariant field list. Called once
//      internally to populate summaryAggregation.perVariant, and exported
//      for a caller (P2.3+ compare wiring) that wants the full array.
//
// ── Known scope limits (honest, not invented precision — Master-Prompt §7)
// ──────────────────────────────────────────────────────────────────────────
//   - Cross-sheet variant IDENTITY MATCHING (step 3,
//     matchMaterialVariantsToContainer) is a TWO-PASS match: an EXACT match
//     on the RAW (pre-disambiguation) compositeCanonicalKey first (with
//     originalVariantNumber/originalColumn ONLY as a within-collision-group
//     tiebreaker — KAR-935 adversarial-review F2 fix), THEN — KAR-936/P2.2 —
//     variant-matcher.ts's core-dimension-subset/fuzzy-label-similarity
//     cascade (matchVariants) as a FALLBACK over whatever the exact pass
//     left unmatched on both sides. KAR-935's own real-file regression
//     observed the exact-match's real-world limit directly: for 3 of the 4
//     real Multi-QAF files, the Material sheet's own re-parsed header block
//     picks up DIFFERENT "extra/unknown dimension" labels than the Summary
//     sheet's own re-parse (neighboring header rows differ per-sheet), so
//     the two sides' RAW keys never agree byte-for-byte even though the
//     semantically stable dimensions (steeringSide/motor/scu...) and the
//     workbook's own printed variant number DO agree. The KAR-936 fallback
//     closes exactly this gap: variant-matcher.ts's stage 2
//     (core_subset_exact) matches on the dimension keys BOTH sides actually
//     populated, ignoring the sheet-local "extra" ones — only a UNIQUE
//     fallback match (matchVariants' own 'matched' result) translates a
//     Material-side key; an 'ambiguous'/'split_suspected'/'merge_suspected'
//     fallback result is left unmatched (with the same
//     `material_variant_unmatched_to_summary` warning as before) exactly
//     like an unresolved exact-match miss always was — never silently forced
//     into an aggregation. Every unique fallback translation gets its own
//     `material_variant_fuzzy_matched_to_summary` (info, reviewRelevant)
//     warning so a human can still sanity-check it. The 4th file
//     (MATERIAL-fallback pattern, 10-analyse-nafta.md) is the one case where
//     the exact pass alone already matches cleanly, because step 1's own
//     identity source AND step 3's own re-parse read the exact SAME
//     physical sheet — the KAR-936 fallback pass is a no-op for it (nothing
//     left unmatched to hand to it).
//   - Variant->profile binding resolution (step 4) needs SUMMARY-sheet-local
//     variant column positions. When step 1's identity source was the
//     MATERIAL sheet (the Summary sheet itself materialized zero variants —
//     10-analyse-nafta.md's own documented shape), there is no reliable
//     Summary-column mapping for the container's own variant identities;
//     binding resolution is skipped for that file with an explicit
//     `variant_profile_binding_summary_columns_unavailable` warning rather
//     than guessed from column position.
//   - sharedMetadata (types.ts: "supplier, project, quotation date... with
//     its source cell") has no P1 parser producing it — stays `{}` (honest
//     absence, not fabricated). language stays 'unknown' for the same
//     reason (no Multi-QAF-specific language-signal parser exists yet;
//     ../language-detection.ts's own detector is shaped around a standard-
//     QAF summaryTemplate this container does not have).
//   - VirtualVariantSummaryTotals.scrap/otherSurcharges/offerBasePrice/
//     offerBasePriceInclAllocation/offerPrice (KAR-951, the last one added by
//     the KAR-951 F1 adversarial-review fix): now extracted via a
//     label-anchored Summary-row scan (profile-parser.ts
//     locateSummaryMoneyRow/extractSummaryMoneyRow, step 4b below) under the
//     SAME "Summary-local variant columns" precondition as
//     variantProfileBindings above — stay `null` only when that
//     precondition doesn't hold (identity sourced from the MATERIAL sheet)
//     or the row/cell genuinely has no value, never fabricated.
//     `offerBasePriceInclAllocation` is its OWN row/metric, never merged
//     onto `offerBasePrice`/`offerPrice` — see
//     VirtualVariantSummaryTotals.offerBasePriceInclAllocation's own doc
//     comment (types.ts) for why the KAR-951 livetest corpus needed a THIRD
//     distinct anchor here, not a fix to one of the first two.
//
// tdd-guard: covered by __tests__/container-assembly.test.ts (synthetic
// ExcelJS workbooks covering all 4 structural patterns) and
// __tests__/container-assembly.real-files.test.ts (env-gated, the 4 real
// Multi-QAF files, aggregate-only assertions consistent with the P1 modules'
// own already-measured real-file counts).

import type { Worksheet } from 'exceljs'
import { matchesModuleSheetName } from '../module-sheet-names'
import type { MultiQafDetectionResult } from '../qaf-type-detector'
import { detectCanonicalKeyCollisions, disambiguateCanonicalKeys } from './identity'
import { matchVariants, type VariantMatchStage, type VariantMatchedResult } from './variant-matcher'
import {
  multiQafTemplateFingerprintInputFromExcelJs,
  fingerprintMultiQafTemplate,
  toMultiQafContainerFingerprint,
  locateVariantHeaderBlockOnSheet,
} from './template-fingerprint'
import {
  parseMaterialMatrix,
  materialMatrixInputFromWorksheet,
  materialMatrixScanBoundsFromWorksheet,
  type MaterialMatrixReconciliationResult,
} from './material-matrix-parser'
import {
  sharedCostProfilesFromWorkbook,
  locateManufacturingSummaryRow,
  resolveVariantProfileBindings,
  profileParserInputFromWorksheet,
  locateSummaryMoneyRow,
  extractSummaryMoneyRow,
  SUMMARY_MONEY_ROW_KINDS,
  type VariantColumnRef,
  type SummaryMoneyRowKind,
} from './profile-parser'
import { buildColumnLineage, formulaLineageNotesFromGraph, type FormulaLineageWarning } from './formula-lineage'
import type {
  ColumnClassification,
  MultiQafCellRef,
  MultiQafContainer,
  MultiQafMoneyAmount,
  MultiQafWarning,
  SharedCostProfile,
  VariantDefinition,
  VariantMatrixRow,
  VariantProfileBinding,
  VirtualQafVariant,
  VirtualVariantMaterialRow,
  VirtualVariantSummaryMoneyRows,
  VirtualVariantSummaryTotals,
} from './types'

// ── Small local helpers (same "one small helper per module" discipline
// profile-parser.ts's own module header establishes rather than a shared
// cross-module utility) ─────────────────────────────────────────────────

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

/** Same MATERIAL-sheet discovery fallback material-matrix-parser.real-files.
 * test.ts's own `findMaterialMatrixSheet` established (MATERIAL-module-name
 * match, falling back to a "bom" substring match for the one real file whose
 * tab is literally named "BOM Detail EU" — module-sheet-names.ts's
 * MATERIAL alias doesn't match that tab name at all). */
function findMaterialWorksheet(wb: { worksheets: readonly Worksheet[] }): Worksheet | null {
  const byModule = wb.worksheets.find((w) => matchesModuleSheetName(w.name, 'MATERIAL'))
  if (byModule) return byModule
  return wb.worksheets.find((w) => /\bbom\b/i.test(w.name)) ?? null
}

function lineageWarningToMultiQafWarning(w: FormulaLineageWarning): MultiQafWarning {
  const sourceReferences: MultiQafCellRef[] =
    w.sheet !== undefined ? [{ sheet: w.sheet, cell: w.cell ?? null, row: null, column: null }] : []
  return { code: w.code, severity: 'warning', message: w.message, sourceReferences }
}

/** Turns a non-'bestanden' SUMPRODUCT-reconciliation result into a container-
 * level warning — material-matrix-parser.ts's own `parseMaterialMatrix`
 * returns the reconciliation array separately (never as warnings itself,
 * see that module's own MaterialMatrixParseResult doc). Follows the
 * "Variante ${variantId}: ..." DE message-prefix convention every other P1
 * warning producer in this package already establishes (profile-parser.ts's
 * own `unresolvedBindingWarning`) for human readability, AND sets
 * `variantIds` structurally (KAR-935 F5 fix) — `variantWarningsFor` below
 * reads the structural field first, the message-prefix convention is only a
 * legacy fallback for warnings that don't set it. */
function reconciliationWarnings(reconciliation: readonly MaterialMatrixReconciliationResult[]): MultiQafWarning[] {
  const warnings: MultiQafWarning[] = []
  for (const r of reconciliation) {
    if (r.status === 'bestanden') continue
    if (r.status === 'abweichung') {
      warnings.push({
        code: 'material_matrix_reconciliation_mismatch',
        severity: 'warning',
        message: `Variante ${r.variantId}: SUMPRODUCT-Rekonziliation weicht ab (erwartet ${r.expected ?? '—'}, gefunden ${r.actual ?? '—'}, Δ=${r.deltaAbsolute ?? '—'}).`,
        messageEn: `Variant ${r.variantId}: SUMPRODUCT reconciliation deviates (expected ${r.expected ?? '—'}, found ${r.actual ?? '—'}, Δ=${r.deltaAbsolute ?? '—'}).`,
        sourceReferences: r.totalCell ? [r.totalCell] : [],
        variantIds: [r.variantId],
      })
    } else {
      warnings.push({
        code: 'material_matrix_reconciliation_not_verifiable',
        severity: 'info',
        message: `Variante ${r.variantId}: SUMPRODUCT-Rekonziliation nicht prüfbar (${r.reason ?? 'kein Grund angegeben'}).`,
        messageEn: `Variant ${r.variantId}: SUMPRODUCT reconciliation not verifiable (${r.reason ?? 'no reason given'}).`,
        sourceReferences: r.totalCell ? [r.totalCell] : [],
        variantIds: [r.variantId],
      })
    }
  }
  return warnings
}

function collectCurrencies(materialRows: readonly VariantMatrixRow[]): readonly string[] {
  const set = new Set<string>()
  for (const row of materialRows) {
    if (row.procurementCurrency) set.add(row.procurementCurrency)
    if (row.offerCurrency) set.add(row.offerCurrency)
    if (row.unitCost.currency) set.add(row.unitCost.currency)
    if (row.logisticsOrDuty?.currency) set.add(row.logisticsOrDuty.currency)
    if (row.materialOverhead?.currency) set.add(row.materialOverhead.currency)
  }
  return [...set].sort()
}

/** Master-Prompt §10 bucketing of a flat SharedCostProfile[] by `.kind` into
 * the 3 container fields — explicitly deferred to this ingest-wiring layer
 * by profile-parser.ts's own module header ("bucketing... is a P2.1
 * ingest-wiring concern, deliberately left to that caller"). `setupCost` and
 * `commonTooling` get their own bucket; every other kind (manufacturing,
 * volumeBand, lShape, iShape, location, currency — the latter two never
 * actually produced by profile-parser.ts today, per its own module header)
 * is manufacturing-shaped or manufacturing-adjacent (a volume-band/L-Shape/
 * I-Shape block IS a manufacturing-cost total under a different selection
 * mechanism) and lands in sharedManufacturingProfiles. */
function bucketProfilesByKind(profiles: readonly SharedCostProfile[]): {
  manufacturing: SharedCostProfile[]
  tooling: SharedCostProfile[]
  setupCost: SharedCostProfile[]
} {
  const manufacturing: SharedCostProfile[] = []
  const tooling: SharedCostProfile[] = []
  const setupCost: SharedCostProfile[] = []
  for (const p of profiles) {
    if (p.kind === 'setupCost') setupCost.push(p)
    else if (p.kind === 'commonTooling') tooling.push(p)
    else manufacturing.push(p)
  }
  return { manufacturing, tooling, setupCost }
}

/**
 * Resolves a genuine compositeCanonicalKey collision (identity.ts's own
 * `disambiguateCanonicalKeys`) AND keeps `stableInternalId` in sync — that
 * field is what `sharedMaterialMaster`/`variantProfileBindings` are actually
 * keyed by (identity.ts's own module header names this as the "aggregation"
 * risk a caller must not leave undetected). header-parser.ts's own
 * `assembleVariant` sets `stableInternalId = compositeCanonicalKey` whenever
 * the key is non-empty (never the reserved-slot `slot-<column>` fallback in
 * that case) — this helper mirrors that exact rule so a variant whose key
 * gets disambiguated here also gets its stableInternalId updated, and a
 * variant whose stableInternalId was ALREADY the column-derived fallback
 * (no identity at all) is left untouched (already unique per column, no fix
 * needed).
 */
function disambiguateVariantIdentities(variants: readonly VariantDefinition[]): VariantDefinition[] {
  const disambiguated = disambiguateCanonicalKeys(variants)
  return disambiguated.map((v, i) => {
    const original = variants[i]!
    if (v.compositeCanonicalKey === original.compositeCanonicalKey) return v
    if (original.stableInternalId !== original.compositeCanonicalKey) return v
    return { ...v, stableInternalId: v.compositeCanonicalKey }
  })
}

/**
 * KAR-935 adversarial review F2 fix: matches the Material sheet's own
 * independently-parsed variant identities against the container's own
 * (Summary-sourced) variant identities, and returns Material-side
 * VariantDefinition objects whose `stableInternalId` has been TRANSLATED to
 * the matched container variant's own `stableInternalId`.
 *
 * The bug this closes: step 3 below used to call
 * `disambiguateVariantIdentities` on `matHeaderResult.variants` completely
 * independently of the container's own `variants` (from step 1) —
 * `disambiguateCanonicalKeys` only appends a `#dup-N` suffix when 2+ entries
 * in the SAME array share a key, so when the two sheets' own re-parses
 * disagree on which variants collide (a real occurrence — the Material
 * sheet's own header-block re-parse can find a different variant subset than
 * the Summary sheet's, module header "Known scope limits"), the SAME real
 * variant could end up with two DIFFERENT final stableInternalIds. Since
 * `parseMaterialMatrix` keys `quantityFactorByVariant`/`effectiveCostByVariant`
 * by the array it was CALLED with, and `generateVirtualVariants` looks rows
 * up by the CONTAINER's own `stableInternalId`, a mismatch made the row look
 * "not applicable" (the same blank-cell semantics a genuinely inapplicable
 * row uses) — material costs silently reported null/too-low, no warning.
 *
 * Matching key: `compositeCanonicalKey` computed BEFORE either side's own
 * disambiguation (`materialRawVariants`/`containerRawVariants` — the RAW,
 * un-suffixed key is what identity.ts's own determinism guarantee actually
 * promises to be stable across sheets: "depends only on DIMENSION VALUES,
 * never on sheet/column"). Within a collision group sharing one raw key,
 * `originalVariantNumber` (the workbook's own printed slot/variant number,
 * when present on both sides) is preferred as a tiebreaker; otherwise
 * members are consumed in document order — the same determinism
 * `disambiguateCanonicalKeys` itself relies on for its own `#dup-N`
 * ordinals, applied here to a CROSS-array pairing instead of a single-array
 * suffix.
 *
 * A Material-side variant that matches no container raw key at all is
 * reported via a `material_variant_unmatched_to_summary` warning and kept in
 * `forParsing` with its OWN (untranslated) stableInternalId — deliberately
 * NOT dropped from the array `parseMaterialMatrix` is called with. An
 * earlier version of this fix excluded unmatched columns entirely, which
 * regressed real-file row COUNTS (CLARWE 120 -> 121, one row's total/
 * aggregate-formula-shaped cell lived in exactly the excluded column, so
 * `parseMaterialMatrix`'s own `rowHasAggregateFormulaInVariantColumns` no
 * longer saw it and mis-classified a total row as an ordinary material row)
 * — row CLASSIFICATION scans every real variant column on the sheet
 * regardless of whether it can be attributed to a container identity, so
 * every real column must stay in the array `parseMaterialMatrix` sees. An
 * unmatched column's own (untranslated) id can never coincide with any
 * CONTAINER variant's own final id (that is the definition of "unmatched"),
 * so its quantityFactorByVariant entries are simply never looked up by
 * `generateVirtualVariants` — inert, not mis-attributed.
 *
 * KAR-936/P2.2 addition: whatever the exact-RAW-key pass above leaves
 * unmatched on BOTH sides is handed to variant-matcher.ts's matchVariants as
 * a second, fallback pass (see module header "Known scope limits" for the
 * real-file evidence this closes). Only a UNIQUE fallback `'matched'` result
 * translates a Material-side stableInternalId — an `'ambiguous'`/
 * `'split_suspected'`/`'merge_suspected'` fallback finding is left exactly
 * as unmatched as an unresolved exact-match miss always was (never silently
 * forced into the aggregation, per Master-Prompt §9's "never automatically
 * match ambiguous variants for commercial aggregation"), still surfacing the
 * SAME `material_variant_unmatched_to_summary` warning it would have gotten
 * without this fallback at all. Every unique fallback translation instead
 * gets its own `material_variant_fuzzy_matched_to_summary` (severity info,
 * reviewRelevant) warning — the totals now flow, but a human can still
 * sanity-check exactly which columns were matched by inference rather than
 * by an exact key.
 *
 * Adversarial-review-of-#308 F1 addendum: a UNIQUE `'matched'` fallback
 * result is STILL not sufficient on its own — see
 * `qualifiesForFuzzyAggregation`/`FUZZY_AGGREGATION_MIN_*` immediately below
 * for the additional financial-aggregation gate (stage + dimension-count +
 * confidence) every fallback match must also clear before its
 * stableInternalId is translated. A 'matched' result that fails the gate is
 * treated exactly like an unresolved exact-match miss (unmatched, no
 * translation), plus a `material_variant_fuzzy_match_below_aggregation_gate`
 * warning explaining the near-miss.
 */
// ── Fuzzy-fallback financial-aggregation gate (adversarial review of #308,
// F1 — the SINGLE MOST SEVERE finding: "wrong numbers instead of missing
// numbers") ──────────────────────────────────────────────────────────────
//
// matchVariants' own `candidateMinScore`/`coreSubsetBaseConfidence` floors
// (~0.45/~0.6, variant-matcher.ts DEFAULT_VARIANT_MATCH_CONFIG) are
// calibrated for "is this worth surfacing as a REVIEW candidate at all" —
// NOT for "is this safe to silently move real supplier cost/volume data
// between container variants". Before this fix, ANY 'matched' fallback
// result was translated unconditionally (see the loop below): a single
// low-information dimension match (e.g. only `steeringSide=links` shared,
// every other dimension unset on one side due to header-parsing noise)
// could reach `coreSubsetBaseConfidence` (0.6) with zero competing
// candidates to force `ambiguous` — and would then silently reassign a
// Material-sheet column's cost/volume totals to a container variant it may
// not actually belong to.
//
// This is a SEPARATE, stricter, financially-scoped gate applied on TOP of
// matchVariants' own candidate floor, modeled on variant-matcher.ts's own
// VariantMatchConfig pattern (named, documented constants — CLAUDE.md "No
// magic numbers"): only a `raw_exact`/`core_subset_exact` result (NEVER
// `fuzzy_label_similarity` — that stage tolerates per-dimension typos/
// synonyms, too soft a signal to move real cost data) backed by AT LEAST
// FUZZY_AGGREGATION_MIN_DIMENSIONS informative (non-empty, shared)
// dimensions AND a confidence at/above FUZZY_AGGREGATION_MIN_CONFIDENCE may
// translate a Material-sheet stableInternalId. A single-dimension match is
// NEVER sufficient on its own, regardless of confidence. Anything below
// either bar is left exactly as unmatched as an unresolved exact-match miss
// always was (`material_variant_unmatched_to_summary`), with an additional
// `material_variant_fuzzy_match_below_aggregation_gate` (info,
// reviewRelevant) warning explaining WHY a plausible-looking fuzzy
// candidate was rejected — Master-Prompt §9 "never automatically match...
// for commercial aggregation" applied to this fallback's own translation
// step, not just to matchVariants' own ambiguous/split/merge result kinds.
//
// FUZZY_AGGREGATION_MIN_CONFIDENCE calibration (real-file evidence, KAR-936
// real-file suite): a single-dimension match at FULL coverage (nothing else
// populated on either side — the exact "steeringSide=links alone" attack
// this gate exists for) already reaches confidence 0.9
// (coreSubsetBaseConfidence 0.6 + coreSubsetCoverageWeight 0.3 * coverage 1.0)
// — a confidence-only bar can NEVER exclude it on its own, which is exactly
// why FUZZY_AGGREGATION_MIN_DIMENSIONS is the primary, non-negotiable guard
// and confidence is the secondary one. Two real post-fix data points pin the
// confidence bar itself: Datei "4" (ncar, manifest index 3) has 8 genuine
// (verified via independent originalVariantNumber agreement) 4-shared-
// dimension core_subset_exact candidates at confidence 0.8 — these MUST
// clear the gate; Datei "2" (mx, manifest index 1) has 7 weaker 2-shared-
// dimension candidates at confidence 0.667 (steeringSide + one coincidental
// unrecognized-label overlap) — these correctly do NOT clear it, an honest
// regression vs. #308's original (ungated) 7-match claim, not a bug.
// 0.75 sits between the two, backed by both real data points.
const FUZZY_AGGREGATION_MIN_CONFIDENCE = 0.75
const FUZZY_AGGREGATION_MIN_DIMENSIONS = 2
const FUZZY_AGGREGATION_ELIGIBLE_STAGES: readonly VariantMatchStage[] = ['raw_exact', 'core_subset_exact']

function qualifiesForFuzzyAggregation(r: VariantMatchedResult): boolean {
  return (
    FUZZY_AGGREGATION_ELIGIBLE_STAGES.includes(r.stage) &&
    r.matchedDimensionCount >= FUZZY_AGGREGATION_MIN_DIMENSIONS &&
    r.confidence >= FUZZY_AGGREGATION_MIN_CONFIDENCE
  )
}

function matchMaterialVariantsToContainer(
  materialRawVariants: readonly VariantDefinition[],
  materialVariants: readonly VariantDefinition[],
  containerRawVariants: readonly VariantDefinition[],
  containerVariants: readonly VariantDefinition[],
): { forParsing: VariantDefinition[]; matchedCount: number; warnings: MultiQafWarning[] } {
  const groups = new Map<string, { raw: VariantDefinition; final: VariantDefinition }[]>()
  containerRawVariants.forEach((raw, i) => {
    const entry = { raw, final: containerVariants[i]! }
    const existing = groups.get(raw.compositeCanonicalKey)
    if (existing) existing.push(entry)
    else groups.set(raw.compositeCanonicalKey, [entry])
  })

  const forParsing: VariantDefinition[] = []
  const warnings: MultiQafWarning[] = []
  let matchedCount = 0
  // Indices (into materialRawVariants/materialVariants/forParsing — all
  // three stay index-aligned throughout, see the exact-pass loop below) that
  // the exact pass could not resolve — candidates for the KAR-936 fallback.
  const unmatchedMaterialIdx: number[] = []

  materialRawVariants.forEach((raw, i) => {
    const matSide = materialVariants[i]!
    const queue = groups.get(raw.compositeCanonicalKey)
    if (!queue || queue.length === 0) {
      // Placeholder (untranslated) — may be overwritten by the fallback pass
      // below; falls through to the standard unmatched warning otherwise.
      forParsing.push(matSide)
      unmatchedMaterialIdx.push(i)
      return
    }
    let idx = raw.originalVariantNumber !== null ? queue.findIndex((g) => g.raw.originalVariantNumber === raw.originalVariantNumber) : -1
    if (idx === -1) idx = 0
    const [target] = queue.splice(idx, 1)
    matchedCount += 1
    forParsing.push({ ...matSide, stableInternalId: target!.final.stableInternalId })
  })

  // Container variants left unconsumed in `groups` after the exact pass —
  // the LEFT side variant-matcher.ts's fallback matches against.
  const leftoverContainer: { raw: VariantDefinition; final: VariantDefinition }[] = []
  for (const queue of groups.values()) leftoverContainer.push(...queue)

  const fuzzyMatchedLocalIdx = new Set<number>()
  if (unmatchedMaterialIdx.length > 0 && leftoverContainer.length > 0) {
    const leftoverContainerRaw = leftoverContainer.map((e) => e.raw)
    const leftoverMaterialRaw = unmatchedMaterialIdx.map((i) => materialRawVariants[i]!)
    const fallbackResults = matchVariants(leftoverContainerRaw, leftoverMaterialRaw)

    for (const r of fallbackResults) {
      if (r.kind !== 'matched') continue
      const containerEntry = leftoverContainer[r.leftIndex]!
      const materialGlobalIdx = unmatchedMaterialIdx[r.rightIndex]!
      const matSide = materialVariants[materialGlobalIdx]!
      const label = matSide.originalLabels.filter((l) => l.trim() !== '').join(' / ') || matSide.originalColumn

      if (!qualifiesForFuzzyAggregation(r)) {
        // Below the financial-aggregation gate (F1 fix) — leave unmatched,
        // exactly like an unresolved exact-match miss always was, but surface
        // WHY a plausible-looking fuzzy candidate was rejected so a human
        // reviewer sees the near-miss instead of just silence.
        warnings.push({
          code: 'material_variant_fuzzy_match_below_aggregation_gate',
          severity: 'info',
          message: `Material-Sheet-Spalte "${matSide.originalColumn}" (${label}) hat einen Fuzzy-Match-Kandidaten (Stufe "${r.stage}", Konfidenz ${r.confidence}, ${r.matchedDimensionCount} gemeinsame Dimension(en)) zu einer Summary-Varianten-Identität, der unterhalb der Mindestanforderung für die automatische Kosten-Zuordnung liegt (Stufe raw_exact/core_subset_exact, ≥${FUZZY_AGGREGATION_MIN_DIMENSIONS} Dimensionen, Konfidenz ≥${FUZZY_AGGREGATION_MIN_CONFIDENCE}) — Materialdaten dieser Spalte werden NICHT übernommen, um keine Zahlen der falschen Variante zuzuordnen.`,
          messageEn: `Material-sheet column "${matSide.originalColumn}" (${label}) has a fuzzy-match candidate (stage "${r.stage}", confidence ${r.confidence}, ${r.matchedDimensionCount} shared dimension(s)) to a Summary variant identity that falls below the minimum bar for automatic cost aggregation (stage raw_exact/core_subset_exact, >=${FUZZY_AGGREGATION_MIN_DIMENSIONS} dimensions, confidence >=${FUZZY_AGGREGATION_MIN_CONFIDENCE}) — this column's material data is NOT carried forward, to avoid attributing numbers to the wrong variant.`,
          sourceReferences: matSide.sourceReferences,
          variantIds: [containerEntry.final.stableInternalId],
          reviewRelevant: true,
        })
        continue
      }

      forParsing[materialGlobalIdx] = { ...matSide, stableInternalId: containerEntry.final.stableInternalId }
      matchedCount += 1
      fuzzyMatchedLocalIdx.add(r.rightIndex)
      warnings.push({
        code: 'material_variant_fuzzy_matched_to_summary',
        severity: 'info',
        message: `Material-Sheet-Spalte "${matSide.originalColumn}" (${label}) wurde per Fuzzy-Matching (Stufe "${r.stage}", Konfidenz ${r.confidence}) einer Summary-Varianten-Identität zugeordnet — bitte prüfen: ${r.explanation}`,
        messageEn: `Material-sheet column "${matSide.originalColumn}" (${label}) was matched to a Summary variant identity via fuzzy matching (stage "${r.stage}", confidence ${r.confidence}) — please verify: ${r.explanation}`,
        sourceReferences: matSide.sourceReferences,
        variantIds: [containerEntry.final.stableInternalId],
        reviewRelevant: true,
      })
    }
  }

  unmatchedMaterialIdx.forEach((globalIdx, localIdx) => {
    if (fuzzyMatchedLocalIdx.has(localIdx)) return
    const matSide = materialVariants[globalIdx]!
    const label = matSide.originalLabels.filter((l) => l.trim() !== '').join(' / ') || matSide.originalColumn
    warnings.push({
      code: 'material_variant_unmatched_to_summary',
      severity: 'warning',
      message: `Material-Sheet-Spalte "${matSide.originalColumn}" (${label}) konnte keiner Summary-Varianten-Identität zugeordnet werden — Materialdaten dieser Spalte werden nicht in die Container-Aggregation übernommen.`,
      messageEn: `Material-sheet column "${matSide.originalColumn}" (${label}) could not be matched to any Summary variant identity — this column's material data is excluded from container aggregation.`,
      sourceReferences: matSide.sourceReferences,
      // matSide's OWN (material-side, pre-container-resolution) stableInternalId
      // — its dimension-VALUE-derived compositeCanonicalKey when the header
      // parse recognized any (position-independent), falling back to a
      // column-slot id only when it recognized none. KAR-937 adversarial
      // review F2 fix: container-differ.ts's detailWithoutSummarySetDiff
      // needs this to dedupe structurally instead of by rendered message
      // text, which two genuinely different columns can collide on after a
      // column shift (same recurring label text landing at the same
      // rendered column letter).
      variantIds: [matSide.stableInternalId],
      reviewRelevant: true,
    })
  })

  return { forParsing, matchedCount, warnings }
}

// ── Assembly ─────────────────────────────────────────────────────────────

export interface MultiQafContainerAssemblyOptions {
  /** Provenance-only (types.ts MultiQafSourceWorkbookMeta doc: "NEVER read
   * by any detection/classification logic"). */
  fileName?: string | null
  fileHash?: string | null
}

/**
 * Orchestrates the 6 merged P1 modules into ONE MultiQafContainer — see
 * module header for the full order/rationale. Async (parseMaterialMatrix is
 * async — canonical-registry label synonyms, KAR-931). Throws when NO
 * variant identity could be located at all (neither SUMMARY nor MATERIAL
 * sheet materialized a single VariantDefinition) — fail-closed by design
 * (module header task brief: "lieber abweisen als halben Container
 * persistieren"); `resolveMultiQafContainerIngest` below is the fail-closed
 * wrapper actions.ts actually calls.
 */
export async function assembleMultiQafContainer(
  wb: { worksheets: Worksheet[] },
  detection: MultiQafDetectionResult,
  options: MultiQafContainerAssemblyOptions = {},
): Promise<MultiQafContainer> {
  const warnings: MultiQafWarning[] = []

  // ── 1+2. Identity + template fingerprint (template-fingerprint.ts, reused
  // verbatim — SUMMARY-then-MATERIAL sheet-fallback cascade). ──────────────
  const fingerprintInput = multiQafTemplateFingerprintInputFromExcelJs(wb, { detection })
  const headerParser = fingerprintInput.headerParser
  if (headerParser === null || headerParser.variants.length === 0) {
    throw new Error(
      'Multi-QAF container assembly failed: no variant identity could be located (neither SUMMARY nor MATERIAL sheet materialized a variant header block).',
    )
  }
  const fingerprintResult = fingerprintMultiQafTemplate(fingerprintInput)
  const templateFingerprint = toMultiQafContainerFingerprint(fingerprintResult)

  // Collision detection runs on the RAW header-parser output first (never
  // silently pre-merged — identity.ts doctrine) so the warning always
  // reflects what the parser actually produced; disambiguation then
  // resolves it for THIS container's own internal Map keys (materialRows/
  // bindings below are keyed by `stableInternalId` — an undetected
  // collision there would make two genuinely different variants silently
  // share one VirtualQafVariant's material/profile data, identity.ts's own
  // "two genuinely different variants silently look like the same part"
  // risk applied to this module's own join key instead of
  // QafSummary.partNumber). `disambiguateVariantIdentities` below keeps
  // `stableInternalId` in sync with the disambiguated `compositeCanonicalKey`
  // — header-parser.ts's own assembleVariant does not call
  // disambiguateCanonicalKeys itself (P1.2 scope), so this container-
  // assembly layer is the first point that both HAS all sibling variants at
  // once and NEEDS their join keys to be unique.
  const rawCollisionWarnings = detectCanonicalKeyCollisions(headerParser.variants)
  const variants = disambiguateVariantIdentities(headerParser.variants)
  // KAR-935 F5 fix: detectCanonicalKeyCollisions necessarily reports RAW
  // (pre-disambiguation) ids — for a genuine non-empty-key collision,
  // header-parser.ts's own assembleVariant sets
  // `stableInternalId := compositeCanonicalKey`, so every member of the RAW
  // collision group carries the exact SAME (non-discriminating) raw id;
  // only the FINAL, disambiguated `variants[i].stableInternalId` values
  // actually distinguish the members. Translate here, by re-grouping
  // `headerParser.variants` BY THE SAME `compositeCanonicalKey` field in the
  // same insertion order `detectCanonicalKeyCollisions` itself groups by —
  // the two therefore produce warnings/groups in the same order 1:1, so
  // `collisionGroupsInOrder[i]` is safe to zip against `rawCollisionWarnings[i]`.
  const rawGroupsByKey = new Map<string, number[]>()
  headerParser.variants.forEach((v, i) => {
    const list = rawGroupsByKey.get(v.compositeCanonicalKey)
    if (list) list.push(i)
    else rawGroupsByKey.set(v.compositeCanonicalKey, [i])
  })
  const collisionGroupsInOrder = [...rawGroupsByKey.values()].filter((indices) => indices.length >= 2)
  warnings.push(
    ...rawCollisionWarnings.map((w, i) => ({
      ...w,
      variantIds: collisionGroupsInOrder[i]?.map((idx) => variants[idx]!.stableInternalId) ?? w.variantIds,
    })),
  )

  const activeVariants: VariantDefinition[] = variants.filter((v) => v.activeState === 'active')
  const inactiveVariants: VariantDefinition[] = variants.filter((v) => v.activeState !== 'active')

  const auxiliaryScenarios: ColumnClassification[] = headerParser.columnClassifications.filter(
    (c) => c.kind === 'benchmark_scenario' || c.kind === 'comparison_scenario',
  )
  const helperColumns: ColumnClassification[] = headerParser.columnClassifications.filter(
    (c) => c.kind === 'delta_column' || c.kind === 'percentage_delta_column' || c.kind === 'comment_column' || c.kind === 'helper_calculation',
  )

  const identitySourceSheet = variants.find((v) => v.sourceReferences.length > 0)?.sourceReferences[0]?.sheet ?? null
  const summaryWs = wb.worksheets.find((w) => matchesModuleSheetName(w.name, 'SUMMARY')) ?? null
  const identityIsSummarySourced = summaryWs !== null && identitySourceSheet === summaryWs.name

  // ── 3. Material matrix (material-matrix-parser.ts) — ALWAYS re-parsed
  // directly on the Material/BOM sheet's own local header block (see module
  // header "Known scope limits"), independent of where step 1's identity
  // came from. ───────────────────────────────────────────────────────────
  let sharedMaterialMaster: readonly VariantMatrixRow[] = []
  const materialWs = findMaterialWorksheet(wb)
  if (materialWs) {
    const matHeaderResult = locateVariantHeaderBlockOnSheet(materialWs)
    if (matHeaderResult.variants.length > 0) {
      // KAR-935 F2 fix: match the Material sheet's own independently-parsed
      // variants against the container's own (Summary-sourced) `variants`
      // FIRST — one shared identity resolution instead of disambiguating
      // `matHeaderResult.variants` in complete isolation (see
      // matchMaterialVariantsToContainer's own doc comment for the exact
      // failure this replaces). `matVariants` (disambiguated Material-side,
      // needed only as this function's own tiebreak/provenance input, e.g.
      // originalColumnIndex on THIS sheet) is intentionally still computed
      // the old way — the fix is in how its stableInternalId gets
      // TRANSLATED before being handed to parseMaterialMatrix, not in how
      // the Material-side collisions among themselves are resolved.
      const matVariants = disambiguateVariantIdentities(matHeaderResult.variants)
      const { forParsing, matchedCount, warnings: matchWarnings } = matchMaterialVariantsToContainer(
        matHeaderResult.variants,
        matVariants,
        headerParser.variants,
        variants,
      )
      warnings.push(...matchWarnings)
      if (matchedCount === 0) {
        // Row-level data (position/label/unit cost) is independent of
        // variant-column matching — locateMaterialMatrixHeader below never
        // consults the variants array at all — so this is NOT a reason to
        // skip material-row extraction entirely. `forParsing` is passed to
        // parseMaterialMatrix below regardless (still needed for accurate
        // total/aggregate-row CLASSIFICATION even when zero columns can be
        // attributed to a container identity) — only worth flagging
        // distinctly here: a matrix with zero matched variants extracts
        // rows but attributes costs to NO variant at all.
        warnings.push({
          code: 'material_matrix_no_matched_variant_identity',
          severity: 'warning',
          message: `Material-/BOM-Sheet "${materialWs.name}": keine seiner Varianten konnte einer Summary-Varianten-Identität zugeordnet werden — Materialzeilen werden extrahiert, aber keiner Variante zugeordnet.`,
          messageEn: `Material/BOM sheet "${materialWs.name}": none of its variants could be matched to a Summary variant identity — material rows are extracted but attributed to no variant.`,
          sourceReferences: [],
          reviewRelevant: true,
        })
      }
      const mmInput = materialMatrixInputFromWorksheet(materialWs)
      const bounds = materialMatrixScanBoundsFromWorksheet(materialWs)
      const mmResult = await parseMaterialMatrix(mmInput, forParsing, { rowTo: bounds.rowTo, colTo: bounds.colTo })
      sharedMaterialMaster = mmResult.rows
      warnings.push(...mmResult.warnings, ...reconciliationWarnings(mmResult.reconciliation))

      // KAR-935 F2 fix: distinguish a legitimate per-row "not applicable"
      // (this variant's id simply never appears as a key on THIS row, while
      // it DOES appear on others) from a SYSTEMATIC miss (this variant's id
      // never appears in the matrix's row-key-set AT ALL, despite the matrix
      // being non-trivially populated for other variants) — the latter is a
      // symptom of an identity mismatch this matching pass did not catch
      // (e.g. a wrong-but-non-empty match), not an honest absence. Reserved
      // slots are excluded — having zero material rows is their normal,
      // expected shape (no identity, no volume), not a mismatch signal.
      if (sharedMaterialMaster.length > 0) {
        const rowKeySet = new Set<string>()
        for (const row of sharedMaterialMaster) {
          for (const id of Object.keys(row.quantityFactorByVariant)) rowKeySet.add(id)
        }
        if (rowKeySet.size > 0) {
          for (const v of variants) {
            if (v.activeState === 'reserved') continue
            if (rowKeySet.has(v.stableInternalId)) continue
            warnings.push({
              code: 'material_matrix_variant_never_referenced',
              severity: 'warning',
              message: `Variante ${v.stableInternalId}: keine einzige Materialzeile referenziert diese Varianten-ID (bei sonst befüllter Material-Matrix) — Materialkosten werden als "nicht ermittelbar" statt "nicht anwendbar" behandelt; mögliches Identitäts-Mismatch zwischen Summary- und Material-Sheet.`,
              messageEn: `Variant ${v.stableInternalId}: not a single material row references this variant ID (while the material matrix is otherwise populated) — material costs are treated as "undeterminable" rather than "not applicable"; possible identity mismatch between the Summary and Material sheet.`,
              sourceReferences: [],
              variantIds: [v.stableInternalId],
              reviewRelevant: true,
            })
          }
        }
      }
    } else {
      warnings.push({
        code: 'material_matrix_no_variant_identity',
        severity: 'warning',
        message: `Material-/BOM-Sheet "${materialWs.name}" gefunden, aber kein eigener Varianten-Header-Block darauf identifizierbar — Material-Matrix wird nicht extrahiert.`,
        messageEn: `Material/BOM sheet "${materialWs.name}" found, but no variant header block of its own could be identified on it — material matrix not extracted.`,
        sourceReferences: [],
      })
    }
  } else {
    warnings.push({
      code: 'material_sheet_not_found',
      severity: 'warning',
      message: 'Kein MATERIAL-/BOM-Sheet im Workbook gefunden — geteilte Material-Matrix bleibt leer.',
      messageEn: 'No MATERIAL/BOM sheet found in the workbook — shared material matrix stays empty.',
      sourceReferences: [],
    })
  }

  // ── 4. Shared cost profiles + variant->profile bindings (profile-parser.ts).
  // Profile BLOCK detection is workbook-level (no variant refs needed);
  // binding RESOLUTION needs Summary-local variant columns — only available
  // when step 1's identity source WAS the Summary sheet (module header
  // "Known scope limits"). ─────────────────────────────────────────────────
  const profilesResult = sharedCostProfilesFromWorkbook(wb)
  warnings.push(...profilesResult.warnings)
  const {
    manufacturing: sharedManufacturingProfiles,
    tooling: sharedToolingData,
    setupCost: setupCostProfiles,
  } = bucketProfilesByKind(profilesResult.profiles)

  let variantProfileBindings: readonly VariantProfileBinding[] = []
  // KAR-951 — the 4 Summary-money-row extractions below share the exact same
  // "Summary-local variant columns, only available when identity itself came
  // from the Summary sheet" precondition the profile-binding resolution
  // above already established — computed alongside it, not as a separate
  // pass, so the two never disagree about which columns a variant occupies.
  let summaryMoneyRowsByVariant: Record<string, VirtualVariantSummaryMoneyRows> = {}
  if (identityIsSummarySourced && summaryWs) {
    const summaryInput = profileParserInputFromWorksheet(summaryWs)
    const variantColumnRefs: VariantColumnRef[] = variants.map((v) => ({
      variantId: v.stableInternalId,
      column: v.originalColumnIndex - 1,
    }))

    const manufacturingRow = locateManufacturingSummaryRow(summaryInput)
    if (manufacturingRow !== null) {
      const bindingResult = resolveVariantProfileBindings(summaryInput, manufacturingRow, variantColumnRefs, profilesResult.profiles)
      variantProfileBindings = bindingResult.bindings
      warnings.push(...bindingResult.warnings)
    } else {
      warnings.push({
        code: 'manufacturing_summary_row_not_found',
        severity: 'info',
        message: 'Keine Fertigungskosten-Zeile im Summary-Sheet gefunden — Varianten-Profil-Bindung übersprungen.',
        messageEn: 'No manufacturing-cost row found in the Summary sheet — variant/profile binding resolution skipped.',
        sourceReferences: [],
      })
    }

    const currencyByVariant = new Map(variants.map((v) => [v.stableInternalId, v.currency] as const))
    const extractionByKind: Partial<Record<SummaryMoneyRowKind, ReturnType<typeof extractSummaryMoneyRow>>> = {}
    for (const kind of SUMMARY_MONEY_ROW_KINDS) {
      const row = locateSummaryMoneyRow(kind, summaryInput)
      if (row === null) {
        warnings.push({
          code: `summary_money_row_not_found_${kind}`,
          severity: 'info',
          message: `Keine Zeile für "${kind}" im Summary-Sheet gefunden — dieser Summary-Kennzahl-Wert bleibt "nicht ermittelbar" für alle Varianten.`,
          messageEn: `No row for "${kind}" found in the Summary sheet — this summary metric stays "undeterminable" for every variant.`,
          sourceReferences: [],
        })
        continue
      }
      extractionByKind[kind] = extractSummaryMoneyRow(summaryInput, row, variantColumnRefs, currencyByVariant)
    }
    const fieldByKind: Record<SummaryMoneyRowKind, keyof VirtualVariantSummaryMoneyRows> = {
      scrapMaterial: 'scrap',
      otherSurcharges: 'otherSurcharges',
      offerBasePrice: 'offerBasePrice',
      offerBasePriceInclAllocation: 'offerBasePriceInclAllocation',
      offerPrice: 'offerPrice',
    }
    const byVariantId: Record<string, VirtualVariantSummaryMoneyRows> = {}
    for (const v of variants) {
      const row: VirtualVariantSummaryMoneyRows = {
        scrap: null,
        otherSurcharges: null,
        offerBasePrice: null,
        offerBasePriceInclAllocation: null,
        offerPrice: null,
      }
      for (const kind of SUMMARY_MONEY_ROW_KINDS) {
        const extraction = extractionByKind[kind]
        if (!extraction) continue
        const amount = extraction.byVariant[v.stableInternalId]
        const sourceCell = extraction.sourceCellByVariant[v.stableInternalId]
        if (amount === undefined || sourceCell === undefined) continue
        row[fieldByKind[kind]] = { amount, sourceCell }
      }
      byVariantId[v.stableInternalId] = row
    }
    summaryMoneyRowsByVariant = byVariantId
  } else {
    warnings.push({
      code: 'variant_profile_binding_summary_columns_unavailable',
      severity: 'info',
      message:
        'Varianten-Identität stammt vom MATERIAL-Sheet (Summary-Sheet lieferte keine Varianten) — Fertigungskosten-Profil-Bindung kann nicht sicher auf Summary-Spalten abgebildet werden und wurde übersprungen (Cross-Sheet-Varianten-Matching ist P2.2-Folgearbeit).',
      messageEn:
        'Variant identity comes from the MATERIAL sheet (the Summary sheet materialized no variants) — manufacturing/setup-cost profile binding cannot be safely mapped onto Summary columns and was skipped (cross-sheet variant matching is P2.2 follow-up work).',
      sourceReferences: [],
    })
  }

  // ── 5. Formula lineage (formula-lineage.ts) — feeds
  // summaryAggregation.formulaLineageNotes + merges its own warnings. ──────
  const lineageGraph = buildColumnLineage(wb)
  warnings.push(...lineageGraph.warnings.map(lineageWarningToMultiQafWarning))
  const formulaLineageNotes = formulaLineageNotesFromGraph(lineageGraph)

  const currencies = collectCurrencies(sharedMaterialMaster)
  const hiddenSheetNames = wb.worksheets.filter(isHiddenWorksheet).map((w) => w.name)

  const variantConfidences = variants.map((v) => v.confidence)
  const avgVariantConfidence = variantConfidences.length > 0 ? variantConfidences.reduce((a, b) => a + b, 0) / variantConfidences.length : 0
  // Deliberately coarse composite (same "not calibrated" category as every
  // other confidence field in this package) — averages the 3 independent
  // heuristic confidences this assembly pass draws on.
  const confidence = Number(((detection.confidence + fingerprintResult.confidence + avgVariantConfidence) / 3).toFixed(4))

  const container: MultiQafContainer = {
    sourceWorkbook: {
      fileName: options.fileName ?? null,
      fileHash: options.fileHash ?? null,
      sheetNames: wb.worksheets.map((w) => w.name),
      hiddenSheetNames,
    },
    detectedTemplateFamily: templateFingerprint.family,
    multiQafVersion: detection.matchedVersionMarker,
    // No P1 module extracts the underlying (non-Multi) QAF template version
    // for a Multi-QAF container — honest absence, not fabricated (see
    // ../template-fingerprint.ts's own standard-QAF-only version concept,
    // which does not apply to this container shape).
    underlyingQafVersion: null,
    // No Multi-QAF-specific language-signal parser exists yet (module header
    // "Known scope limits") — honest absence.
    language: 'unknown',
    currencies,
    // No P1 parser extracts container-level shared metadata (supplier/
    // project/quotation date) for a Multi-QAF container yet — honest
    // absence, not fabricated.
    sharedMetadata: {},
    variantDimensions: headerParser.dimensionDescriptors,
    activeVariants,
    inactiveVariants,
    auxiliaryScenarios,
    helperColumns,
    sharedMaterialMaster,
    sharedManufacturingProfiles,
    sharedToolingData,
    setupCostProfiles,
    variantProfileBindings,
    summaryAggregation: { perVariant: [], formulaLineageNotes },
    templateFingerprint,
    warnings,
    confidence,
    summaryMoneyRowsByVariant,
  }

  // ── 6. VirtualQafVariant generation (pure over the now-finished container)
  // — populates summaryAggregation.perVariant so it is never left empty when
  // this module HAS enough data to compute it (types.ts's own "always empty
  // from this module's own pure functions" doc comment predates this PR;
  // P2.1 is the caller that actually computes it now). ─────────────────────
  const virtualVariants = generateVirtualVariants(container)
  return {
    ...container,
    summaryAggregation: { perVariant: virtualVariants.map((v) => v.summaryTotals), formulaLineageNotes },
  }
}

// ── VirtualQafVariant generation ────────────────────────────────────────

interface MoneySumResult {
  /** The single-currency sum, or null when EITHER no amount had a value, OR
   * 2+ distinct known currencies were mixed (see `mixedCurrencies`). */
  amount: MultiQafMoneyAmount | null
  /** One bucket per distinct currency observed among the defined amounts
   * (`currency: null` bucket included when at least one defined amount had
   * an unknown currency) — always populated when `amounts` had at least one
   * defined value, independent of whether `amount`/`mixedCurrencies` ended
   * up set. This is the "per-Währung-Bucket" KAR-935 F1 fix exposes on
   * VirtualVariantSummaryTotals.materialCostsByCurrency instead of ever
   * fabricating a cross-currency total. */
  byCurrency: readonly MultiQafMoneyAmount[]
  /** Sorted distinct currency codes when 2+ known currencies were mixed
   * across `amounts`, else null. */
  mixedCurrencies: readonly string[] | null
}

/**
 * KAR-935 adversarial-review F1 fix: the original `sumMoney` added every
 * amount's raw `.value` together UNCONDITIONALLY and labeled the result with
 * whichever currency happened to be found first (`defined.find((a) =>
 * a.currency !== null)?.currency`) — never checking whether the amounts
 * actually shared one currency. `VariantMatrixRow` tracks
 * procurementCurrency/offerCurrency/unitCost.currency separately per row
 * specifically because a real Multi-QAF variant's material rows are NOT
 * guaranteed single-currency (types.ts MultiQafContainer.currencies' own doc:
 * "a Multi-QAF container is NOT guaranteed single-currency"), so a mixed-
 * currency variant used to get a materially wrong total, persisted and
 * surfaced to the user with no warning that currencies were mixed.
 *
 * Fix: sum only when every defined amount shares ONE known currency (or none
 * has a currency at all); when 2+ DISTINCT known currencies are present,
 * never fabricate a cross-currency total — return `amount: null` +
 * `mixedCurrencies` instead, so the caller can leave the corresponding
 * VirtualVariant field honestly absent and attach a warning (Master-Prompt
 * §7 "never force an interpretation").
 */
function sumMoneyByCurrency(amounts: readonly MultiQafMoneyAmount[]): MoneySumResult {
  const defined = amounts.filter((a) => a.value !== null)
  if (defined.length === 0) return { amount: null, byCurrency: [], mixedCurrencies: null }

  const totalsByCurrency = new Map<string, number>() // '' key == unknown/null currency bucket
  for (const a of defined) totalsByCurrency.set(a.currency ?? '', (totalsByCurrency.get(a.currency ?? '') ?? 0) + (a.value ?? 0))
  const byCurrency: MultiQafMoneyAmount[] = [...totalsByCurrency.entries()]
    .sort(([a], [b]) => a.localeCompare(b))
    .map(([currency, value]) => ({ value, currency: currency === '' ? null : currency }))

  const distinctKnownCurrencies = [...new Set(byCurrency.map((b) => b.currency).filter((c): c is string => c !== null))].sort()
  if (distinctKnownCurrencies.length > 1) {
    return { amount: null, byCurrency, mixedCurrencies: distinctKnownCurrencies }
  }

  const value = defined.reduce((sum, a) => sum + (a.value ?? 0), 0)
  const currency = distinctKnownCurrencies[0] ?? null
  return { amount: { value, currency }, byCurrency, mixedCurrencies: null }
}

function profileTotalAsMoney(profile: SharedCostProfile | null, currency: string | null): MultiQafMoneyAmount | null {
  if (!profile) return null
  const total = profile.values.totalPerUnit
  if (typeof total !== 'number') return null
  return { value: total, currency }
}

/**
 * Attachment of container-level warnings onto the VirtualQafVariant they
 * concern — MultiQafContainer carries no dedicated per-variant warning index
 * (types.ts), so this walks `container.warnings` on every call.
 *
 * KAR-935 adversarial-review F5 fix: reads the STRUCTURAL `w.variantIds`
 * field first (producers set it going forward — MultiQafWarning.variantIds's
 * own doc comment) and falls back to the legacy "Variante ${variantId}:" DE
 * message-prefix convention ONLY for warnings that don't set `variantIds` at
 * all. The prefix-only match used to be the sole mechanism, and it silently
 * missed identity.ts's own `detectCanonicalKeyCollisions` — this package's
 * most safety-critical warning class — because that message names MULTIPLE
 * colliding variants at once and was never phrased with the single-variant
 * prefix; a future warning producer that also never adopts the DE-prefix
 * convention but DOES set `variantIds` is covered by the structural branch
 * regardless of message wording.
 */
function variantWarningsFor(container: MultiQafContainer, variantId: string): MultiQafWarning[] {
  const structural = container.warnings.filter((w) => w.variantIds?.includes(variantId))
  const prefix = `Variante ${variantId}:`
  const legacyPrefixMatches = container.warnings.filter((w) => w.variantIds === undefined && w.message.startsWith(prefix))
  return [...structural, ...legacyPrefixMatches]
}

/**
 * Master-Prompt §6 VirtualQafVariant: one normalized single-variant view per
 * active AND inactive (marked) variant, built PURELY from the already-
 * assembled container's own fields (never re-reads the workbook). See module
 * header for the field-by-field mapping and its honest-absence gaps.
 */
export function generateVirtualVariants(container: MultiQafContainer): VirtualQafVariant[] {
  const allVariants = [...container.activeVariants, ...container.inactiveVariants]
  const allProfiles = [...container.sharedManufacturingProfiles, ...container.setupCostProfiles, ...container.sharedToolingData]
  const profileById = new Map(allProfiles.map((p) => [p.profileId, p] as const))
  const bindingByVariant = new Map(container.variantProfileBindings.map((b) => [b.variantId, b] as const))

  return allVariants.map((definition) => {
    const materialRows: VirtualVariantMaterialRow[] = []
    for (const row of container.sharedMaterialMaster) {
      const factor = row.quantityFactorByVariant[definition.stableInternalId]
      // Blank-cell semantics (material-matrix-parser.ts contract): an
      // OMITTED key means "not applicable to this variant" — skipped, never
      // fabricated as 0.
      if (factor === undefined) continue
      const effectiveValue = row.effectiveCostByVariant[definition.stableInternalId] ?? null
      materialRows.push({
        canonicalComponentIdentity: row.canonicalComponentIdentity,
        unitCost: row.unitCost,
        quantityFactor: factor,
        effectiveCost: { value: effectiveValue, currency: row.unitCost.currency },
        sourceCells: row.sourceCells,
      })
    }
    // KAR-935 F1 fix: local, per-variant warnings for THIS generation pass
    // only — deliberately NOT pushed into `container.warnings` (this
    // function is documented as pure over the container's own fields, and
    // is called a second time by `resolveMultiQafContainerIngest`; mutating
    // the shared container.warnings array here would double the warnings on
    // that second call). Deterministically recomputed from `materialRows`
    // every call, so recomputation is idempotent even though it isn't
    // cached in container.warnings.
    const localWarnings: MultiQafWarning[] = []
    const materialCostsResult = sumMoneyByCurrency(materialRows.map((r) => r.effectiveCost))
    const materialCosts = materialCostsResult.amount
    if (materialCostsResult.mixedCurrencies) {
      localWarnings.push({
        code: 'mixed_currency_material_costs',
        severity: 'warning',
        message: `Variante ${definition.stableInternalId}: Materialkosten mischen Währungen (${materialCostsResult.mixedCurrencies.join(', ')}) — kein Gesamt-Materialkosten-Total gebildet, siehe Werte je Währung.`,
        messageEn: `Variant ${definition.stableInternalId}: material costs mix currencies (${materialCostsResult.mixedCurrencies.join(', ')}) — no aggregate material-cost total was formed, see per-currency values.`,
        sourceReferences: [],
        variantIds: [definition.stableInternalId],
      })
    }

    const binding = bindingByVariant.get(definition.stableInternalId)
    const boundProfile = binding ? (profileById.get(binding.profileId) ?? null) : null
    let selectedManufacturingProfile: SharedCostProfile | null = null
    let selectedSetupCostProfile: SharedCostProfile | null = null
    let selectedToolingProfile: SharedCostProfile | null = null
    if (boundProfile) {
      if (boundProfile.kind === 'setupCost') selectedSetupCostProfile = boundProfile
      else if (boundProfile.kind === 'commonTooling') selectedToolingProfile = boundProfile
      else selectedManufacturingProfile = boundProfile
    }

    const variantCurrency = definition.currency ?? materialCosts?.currency ?? null
    const manufacturingCosts = profileTotalAsMoney(selectedManufacturingProfile, variantCurrency)
    const setupCostAllocation = profileTotalAsMoney(selectedSetupCostProfile, variantCurrency)
    const toolingAndFixtureCost = profileTotalAsMoney(selectedToolingProfile, variantCurrency)

    // Sum only when BOTH components are known AND share one currency — never
    // a partial/guessed sum (Master-Prompt §7 "never force an
    // interpretation"). KAR-935 F1 fix: the original version summed
    // materialCosts.value + manufacturingCosts.value unconditionally and
    // labeled the result `materialCosts.currency ?? manufacturingCosts.currency`
    // WITHOUT checking the two actually agree — e.g. a EUR material total
    // plus a USD manufacturing total used to silently become a "EUR" total
    // that is neither. A currency mismatch here now stays null + warned,
    // exactly like the mixed-currency-within-materialCosts case above.
    let totalProductionCosts: MultiQafMoneyAmount | null = null
    if (materialCosts?.value != null && manufacturingCosts?.value != null) {
      if (materialCosts.currency !== null && manufacturingCosts.currency !== null && materialCosts.currency !== manufacturingCosts.currency) {
        localWarnings.push({
          code: 'mixed_currency_total_production_costs',
          severity: 'warning',
          message: `Variante ${definition.stableInternalId}: Materialkosten (${materialCosts.currency}) und Fertigungskosten (${manufacturingCosts.currency}) verwenden unterschiedliche Währungen — kein Gesamt-Produktionskosten-Total gebildet.`,
          messageEn: `Variant ${definition.stableInternalId}: material costs (${materialCosts.currency}) and manufacturing costs (${manufacturingCosts.currency}) use different currencies — no total production cost was formed.`,
          sourceReferences: [],
          variantIds: [definition.stableInternalId],
        })
      } else {
        totalProductionCosts = { value: materialCosts.value + manufacturingCosts.value, currency: materialCosts.currency ?? manufacturingCosts.currency }
      }
    }

    // KAR-951 — read from container.summaryMoneyRowsByVariant (Summary-sheet
    // label-anchored row extraction, container-assembly.ts step 4b) rather
    // than the hardcoded `null` this module used to assign here (see that
    // field's own doc comment for why the amount+sourceCell pair lives there
    // instead of on VirtualVariantSummaryTotals itself). Absent from the
    // record, or a `null` per-metric entry, means the row could not be
    // located at all / this variant had no value on it — honest absence,
    // never fabricated, same as every other field this function computes.
    const summaryMoneyRows = container.summaryMoneyRowsByVariant[definition.stableInternalId]

    const summaryTotals: VirtualVariantSummaryTotals = {
      variantId: definition.stableInternalId,
      materialCosts,
      materialCostsByCurrency: materialCostsResult.byCurrency,
      manufacturingCosts,
      totalProductionCosts,
      toolingAndFixtureCost,
      setupCostAllocation,
      scrap: summaryMoneyRows?.scrap?.amount ?? null,
      otherSurcharges: summaryMoneyRows?.otherSurcharges?.amount ?? null,
      offerBasePrice: summaryMoneyRows?.offerBasePrice?.amount ?? null,
      offerBasePriceInclAllocation: summaryMoneyRows?.offerBasePriceInclAllocation?.amount ?? null,
      offerPrice: summaryMoneyRows?.offerPrice?.amount ?? null,
    }

    return {
      variantId: definition.stableInternalId,
      containerFingerprint: container.templateFingerprint.structuralHash,
      definition,
      materialRows,
      selectedManufacturingProfile,
      selectedSetupCostProfile,
      selectedToolingProfile,
      summaryTotals,
      warnings: [...variantWarningsFor(container, definition.stableInternalId), ...localWarnings],
    }
  })
}

// ── review_status gate (KAR-935 adversarial-review F4 fix) ─────────────────

export type MultiQafContainerReviewStatus = 'ok' | 'review_required'

/**
 * Same decision actions.ts persists as `qaf_file.review_status` for a
 * `kind: 'multi_qaf'` file — pulled out into its own pure, unit-testable
 * function (same "decision logic worth testing in isolation lives here, not
 * inline in actions.ts's DB-bound glue" discipline
 * `resolveMultiQafContainerIngest`'s own doc comment already establishes).
 *
 * Before this fix, actions.ts checked ONLY `severity === 'critical'` — no
 * producer in the entire multi-qaf module set ever constructs a warning with
 * that severity, so the branch was permanently dead and every Multi-QAF file
 * persisted as `'ok'` regardless of genuine identity ambiguity (a canonical-
 * key collision, an unmatched material variant, an ambiguous cost-profile
 * binding — exactly the findings a human SHOULD look at before the
 * container's aggregation is trusted). `reviewRelevant` (MultiQafWarning's
 * own doc comment) is the honest, separate axis that fix introduces —
 * `severity: 'critical'` is kept in the check too since a future producer
 * MAY legitimately use it.
 */
export function reviewStatusForMultiQafContainer(container: MultiQafContainer): MultiQafContainerReviewStatus {
  return container.warnings.some((w) => w.severity === 'critical' || w.reviewRelevant === true) ? 'review_required' : 'ok'
}

// ── Fail-closed ingest wrapper (KAR-935 task instruction: "lieber abweisen
// als halben Container persistieren") ──────────────────────────────────────

export type MultiQafContainerIngestOutcome =
  | { ok: true; container: MultiQafContainer; virtualVariants: readonly VirtualQafVariant[] }
  | { ok: false; reasonDe: string; reasonEn: string }

/**
 * Try assembly + virtual-variant generation; on ANY failure, fall back to
 * the caller-supplied rejection message (today's `multiQafRejectionMessage`
 * text — qaf-type-detector.ts) rather than persisting a half-built
 * container. This is the ONE function actions.ts's ingest wiring calls —
 * kept here (not inline in actions.ts) so it is unit-testable without any
 * Supabase/DB mocking (actions.ts itself is "DB-bound integration glue",
 * see that file's own module header — the decision logic worth testing in
 * isolation lives here instead).
 */
export async function resolveMultiQafContainerIngest(
  wb: { worksheets: Worksheet[] },
  detection: MultiQafDetectionResult,
  rejection: { reasonDe: string; reasonEn: string },
  options: MultiQafContainerAssemblyOptions = {},
): Promise<MultiQafContainerIngestOutcome> {
  try {
    const container = await assembleMultiQafContainer(wb, detection, options)
    const virtualVariants = generateVirtualVariants(container)
    return { ok: true, container, virtualVariants }
  } catch (err) {
    const detail = err instanceof Error ? err.message : String(err)
    return {
      ok: false,
      reasonDe: `${rejection.reasonDe} (Container-Assembly fehlgeschlagen: ${detail})`,
      reasonEn: `${rejection.reasonEn} (Container assembly failed: ${detail})`,
    }
  }
}