// MATERIAL sheet row parser (KAR-897 / P1.6): standalone parser for the QAF
// MATERIAL detail sheet (Leitfaden 02-leitfaden-teil1.md [15]-[28]), built
// from day one on the canonical field model (canonical-model.ts/
// canonical-fields.ts, KAR-892/P1.1) instead of a hand-written exact-string
// dictionary — mirroring the label-anchor + confidence + degradation-path
// pattern qaf-parser.ts established for MANUFACTURING in KAR-893/P1.2 (see
// that file's module header for the tier-1/tier-2 matching rationale, which
// this module reuses verbatim).
//
// ── Scope discipline (backlog 05-backlog-phasenplan.md [P1.6]) ─────────────
//   - Parses + persists the row shape only. Formula validation (Kalkulatorische
//     Materialkosten = Kosten x Wechselkurs + Verpackung + Transport + Zoll +
//     MGK) is a P2.2 business-rule item, not this PR — this module returns the
//     raw + already-computed values from the sheet, it does not recompute or
//     verify them.
//   - RAW MATERIAL RISKS (the separate "RAW MATERIAL RISK" register sheet,
//     P2.3) is explicitly NOT parsed here — see isMaterialSheetName below.
//     MATERIAL rows reference a Rohstoffbezeichnung (rawMaterialDesignation)
//     but do not resolve it against that sheet.
//   - Cross-file matching/diffing of MATERIAL rows (ALT vs NEU) is explicitly
//     OUT of scope for this PR — a follow-up needs a matcher.ts extension
//     that understands the RMR split-row grouping this module exposes
//     (groupMaterialRowsByPosition) as a single logical unit rather than two
//     candidate rows. This module only parses + groups within ONE file.
//
// ── Rohstoffrisikobeteiligung / RMR split rows (Leitfaden [17], Master-Prompt
// §11) ───────────────────────────────────────────────────────────────────
// A material with raw-material-risk participation is deliberately split
// across MULTIPLE rows that share the SAME Positionsnummer: one row carries
// the fixed material-cost share (left QAF area), one or more further rows
// carry the variable/raw-material-risk share (Mengeneinheit =
// "Raw_material_risk_(kg)", right "ROHSTOFFMATERIALRISIKO" area — logistics/
// MGK/scrap columns are blank there, moved to the fixed row instead). This is
// NOT a duplicate/parse error — groupMaterialRowsByPosition below models the
// split explicitly so downstream code (reconciliation, a future matcher)
// treats it as one logical material position, not N independent rows.
//
// ── Client-bundle discipline (KAR-893 lesson, "Bundle-Lehre aus #272") ─────
// This module is exported through the qaf-differences barrel (index.ts),
// which several 'use client' components already import from (e.g.
// qaf-g60-scenario.tsx) — so canonical-model.ts/canonical-fields.ts (the
// ~115k-line, 13-module registry, of which only the 28 MATERIAL fields are
// ever read here) must stay OUT of the static import graph, exactly like
// qaf-parser.ts's loadManufacturingRegistry(). loadMaterialRegistry() is the
// same pattern: a dynamic import, cached after the first call (module-level
// Promise) so repeated parses don't re-import. Only TYPE imports (erased at
// compile time) are static below.

import type { Worksheet } from 'exceljs'
import type { CanonicalField } from './canonical-fields.types'
import { worksheetToGrid } from './workbook-adapter'
import { normalizeProcessName, normalizePosition } from './normalizer'
import { matchesModuleSheetName } from './module-sheet-names'
import type { ComparisonSide } from './rule-engine'
import type { PlausibilityIssue, PlausibilitySeverity } from './plausibility'
import type { FacetDegradation } from './types'
import { MIN_SIGNAL_MAPPED_COLUMNS } from './types'
import {
  normalizeIgnoredCandidateSheets,
  worksheetHasHeaderLabelInRegion,
  CANDIDATE_SCAN_MAX_COLS,
  type IgnoredCandidateSheetEntry,
  type IgnoredCandidateSheetsMeta,
} from './candidate-sheet-plausibility'

// ── Row shape ────────────────────────────────────────────────────────────

/**
 * The 28 MATERIAL fields the Leitfaden documents completely (S.18-20,
 * canonical-fields.ts MATERIAL_FIELDS) — camelCase mirror of the canonical
 * ids' `mat_*` suffix, same relationship as qaf-parser.ts's QAFFieldKey to
 * MANUFACTURING's `mfg_*` ids.
 */
export interface MaterialRowValues {
  positionNumber: string
  partDesignation: string
  materialDesignation: string
  supplier: string
  technicalFunction: string
  countryOfOrigin: string
  htsCode: string
  unitOfMeasure: string
  procurementCurrency: string
  costPerUnitBw: number | null
  quotationCurrency: string
  exchangeRate: number | null
  packagingCostPerUnit: number | null
  transportCostPerUnit: number | null
  customsCbamCostPerUnit: number | null
  overheadCost: number | null
  costPerUnitAw: number | null
  referenceQuantity: number | null
  netQuantity: number | null
  rebate: number | null
  quantityPerQuotedPart: number | null
  materialCost: number | null
  scrapRate: number | null
  scrapCost: number | null
  rawMaterialDesignation: string
  referenceWeight: number | null
  rawMaterialQuotation: number | null
  rawMaterialSurcharge: number | null
  /** "Verpackungskosten (Angebot)" (KAR-910/fehlerreport-analyse.md §5 Fall
   * #42) — the aggregated per-position packaging cost total, distinct from
   * packagingCostPerUnit's per-unit value. No Leitfaden coverage (see
   * canonical-fields.ts mat_packaging_cost_offer), Fehlerreport-only field. */
  packagingCostOffer: number | null
}

export type MaterialFieldKey = keyof MaterialRowValues

/**
 * A parsed MATERIAL row. sourceCells/normalized/rawText are present from day
 * one (task instruction: "kein Nachrüsten wie bei KAR-886") — every row
 * carries per-field cell provenance for whichever columns were actually
 * located in the header, exactly like qaf-parser.ts's QAFRow (KAR-886/891),
 * just never added later as an afterthought.
 */
export type MaterialRow = MaterialRowValues & {
  sourceCells: Partial<Record<MaterialFieldKey, string>>
  normalized: Partial<Record<MaterialFieldKey, string | number | null>>
  rawText: Partial<Record<MaterialFieldKey, string>>
}

const TEXT_FIELDS: Set<MaterialFieldKey> = new Set([
  'positionNumber',
  'partDesignation',
  'materialDesignation',
  'supplier',
  'technicalFunction',
  'countryOfOrigin',
  'htsCode',
  'unitOfMeasure',
  'procurementCurrency',
  'quotationCurrency',
  'rawMaterialDesignation',
])

/** Every MaterialFieldKey — used to build the default-filled row shape. */
const ALL_FIELD_KEYS: readonly MaterialFieldKey[] = [
  'positionNumber',
  'partDesignation',
  'materialDesignation',
  'supplier',
  'technicalFunction',
  'countryOfOrigin',
  'htsCode',
  'unitOfMeasure',
  'procurementCurrency',
  'costPerUnitBw',
  'quotationCurrency',
  'exchangeRate',
  'packagingCostPerUnit',
  'transportCostPerUnit',
  'customsCbamCostPerUnit',
  'overheadCost',
  'costPerUnitAw',
  'referenceQuantity',
  'netQuantity',
  'rebate',
  'quantityPerQuotedPart',
  'materialCost',
  'scrapRate',
  'scrapCost',
  'rawMaterialDesignation',
  'referenceWeight',
  'rawMaterialQuotation',
  'rawMaterialSurcharge',
  'packagingCostOffer',
]

/**
 * The 3 header-level fields that MUST be located for a MATERIAL header row to
 * be usable at all — Positionsnummer (row identity, needed for the RMR split
 * grouping), Benennung Rohmaterial/Kaufteil ("Materialbezeichnung" — the
 * actual material identity, distinct from Teilebenennung which just repeats
 * the parent part), and Kalkulatorische Materialkosten [AW] (the per-row cost
 * total in offer currency — the field reconciliation.ts needs to sum against
 * summary.materialCosts). Mirrors qaf-parser.ts's
 * CORE_MANUFACTURING_FIELD_KEYS 3-field pattern (Position+Prozess+Kosten).
 *
 * Deliberately does NOT throw when missing (unlike qaf-parser.ts's
 * parseQAFTemplate): MATERIAL is an OPTIONAL, additive sheet — a malformed
 * MATERIAL sheet must not fail the whole file ingest (which still needs
 * SUMMARY + MANUFACTURING to succeed). See parseMaterialWorksheet below:
 * below this minimum, coreFieldsFound is false and rows stays empty, the
 * caller decides what to do with that (a plausibility issue, not a thrown
 * error).
 */
export const CORE_MATERIAL_FIELD_KEYS: readonly MaterialFieldKey[] = [
  'positionNumber',
  'materialDesignation',
  'materialCost',
]

const CORE_FIELD_LABELS_DE: Partial<Record<MaterialFieldKey, string>> = {
  positionNumber: 'Positionsnummer Fertigungsschritt',
  materialDesignation: 'Benennung Rohmaterial / Kaufteil',
  materialCost: 'Kalkulatorische Materialkosten [AW]',
}

// KAR-958/P2 (gate-audit.md B5, coreFieldsFound-Resilienz) — the shared
// MIN_SIGNAL_MAPPED_COLUMNS floor from types.ts (PR #325 review fix #5, was
// the "canonical" duplicated-6x local literal every other parser's doc
// comment referenced by name): minimum number of RECOGNIZED header columns
// required before a degraded (missing-core) header is still worth
// extracting from. Below this, the sheet is treated exactly as before this
// PR (genuinely empty/foreign sheet, `rows: []`) — this floor exists
// specifically so a sheet that merely matches `isMaterialSheetName`'s
// substring but carries no real MATERIAL columns at all (e.g. a
// reference/lookup tab) is never mistaken for a degraded-but-real MATERIAL
// sheet. See __tests__/material-parser.test.ts for the anchor cases (both
// directions).

const HEADER_MATCH_MIN = 5
const HEADER_SCAN_MAX_ROWS = 20

// ── Sheet detection (task instruction: "Name enthält 'material', NICHT das
// Fertigungskosten- oder Summary-Sheet") ────────────────────────────────────

/**
 * True when a worksheet name identifies the MATERIAL detail sheet. The
 * Leitfaden's own screenshots (Abbildung 11/12) show the tab literally named
 * "MATERIAL" for both DE and EN templates — unlike Fertigungskosten/
 * Manufacturing costs, there is no separate translated sheet-name variant
 * documented (Leitfaden [15]-[28] never shows an English MATERIAL tab name
 * different from "MATERIAL"). Excludes the separate "RAW MATERIAL RISK(S)"
 * register sheet (Leitfaden [17]/[21] footnote: "Komplette Beschreibung siehe
 * Registerblatt RAW MATERIAL RISK") — that is a different module (RMR, P2.3),
 * not parsed by this function despite also containing the substring
 * "material".
 *
 * Sheet-name alias source centralized in module-sheet-names.ts (KAR-905/P3.1)
 * — the RMR exclusion below stays local since it is MATERIAL-specific
 * disambiguation, not a shared alias.
 */
export function isMaterialSheetName(name: string): boolean {
  const n = name.toLowerCase()
  if (n.includes('raw material')) return false
  return matchesModuleSheetName(name, 'MATERIAL')
}

/** First worksheet whose name matches isMaterialSheetName, or null when the
 * workbook has none (the additive gate — files without a MATERIAL sheet are
 * simply not parsed by this module at all). */
export function findMaterialWorksheet(wb: { worksheets: Worksheet[] }): Worksheet | null {
  return wb.worksheets.find((w) => isMaterialSheetName(w.name)) ?? null
}

// ── Canonical registry bridge (dynamic import — see module header) ─────────

interface ColumnMatch {
  key: MaterialFieldKey
  /** 1.0 exact, 0.9 normalized-only — see qaf-parser.ts module header for the
   * two-tier rationale, reused verbatim here. */
  confidence: number
  /** Set when the matched canonical field declares a collisionGroup — used
   * to resolve the documented "Mengeneinheit" duplicate label below. */
  collisionGroup?: string
}

interface MaterialRegistryCtx {
  /** MATERIAL-only slice of the canonical registry — scoping is required,
   * not cosmetic: several MATERIAL labels are shared verbatim with
   * MANUFACTURING (e.g. "Positionsnummer Fertigungsschritt",
   * "Teilebenennung") — matching against the full registry would make every
   * MATERIAL header ambiguous, same reasoning as qaf-parser.ts. */
  registry: readonly CanonicalField[]
  idToKey: Record<string, MaterialFieldKey>
  findByAliasFn: (
    label: string,
    lang: 'de' | 'en' | undefined,
    registry: readonly CanonicalField[],
  ) => CanonicalField[]
  /** Members of each declared collisionGroup, in registry declaration order —
   * built once here so the header-mapping loop can resolve the documented
   * "Mengeneinheit" duplicate (canonical-fields.ts
   * material_mengeneinheit_label_duplicate: mat_unit_of_measure's genuine
   * label vs. mat_overhead_cost's BMW source-data mislabel) deterministically // allow-customer-string
   * by column order rather than silently letting the second column overwrite
   * the first column's mapped key. */
  collisionGroupMembers: Map<string, MaterialFieldKey[]>
}

let materialRegistryPromise: Promise<MaterialRegistryCtx> | null = null

async function loadMaterialRegistry(): Promise<MaterialRegistryCtx> {
  if (!materialRegistryPromise) {
    materialRegistryPromise = (async () => {
      const [{ byModule, findByAlias }, { MATERIAL_FIELD_KEY_TO_CANONICAL }] = await Promise.all([
        import('./canonical-model'),
        import('./canonical-fields'),
      ])
      const registry = byModule('MATERIAL')
      const idToKey = Object.fromEntries(
        Object.entries(MATERIAL_FIELD_KEY_TO_CANONICAL).map(([key, canonicalId]) => [canonicalId, key as MaterialFieldKey]),
      ) as Record<string, MaterialFieldKey>

      const collisionGroupMembers = new Map<string, MaterialFieldKey[]>()
      for (const field of registry) {
        if (!field.collisionGroup) continue
        const key = idToKey[field.id]
        if (!key) continue
        const list = collisionGroupMembers.get(field.collisionGroup) ?? []
        list.push(key)
        collisionGroupMembers.set(field.collisionGroup, list)
      }

      return { registry, idToKey, findByAliasFn: findByAlias, collisionGroupMembers }
    })()
  }
  return materialRegistryPromise
}

function matchHeaderColumnSync(headerCell: string, ctx: MaterialRegistryCtx): ColumnMatch | null {
  if (headerCell === '') return null

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

  const hits = ctx.findByAliasFn(headerCell, undefined, ctx.registry)
  const distinctKeys = new Set(
    hits.map((f) => ctx.idToKey[f.id]).filter((k): k is MaterialFieldKey => k !== undefined),
  )
  if (distinctKeys.size === 1) {
    const [key] = distinctKeys
    const field = hits.find((f) => ctx.idToKey[f.id] === key)
    return { key, confidence: 0.9, collisionGroup: field?.collisionGroup }
  }

  return null
}

/** Match one already-whitespace-normalized header string against the
 * MATERIAL canonical field registry. Exported for the same reason
 * qaf-parser.ts exports matchHeaderColumn: single-cell callers (tests, a
 * future UI preview) that don't want to manage the registry cache
 * themselves. */
export async function matchMaterialHeaderColumn(headerCell: string): Promise<ColumnMatch | null> {
  const ctx = await loadMaterialRegistry()
  return matchHeaderColumnSync(headerCell, ctx)
}

function normalizeHeaderCell(v: unknown): string {
  return String(v ?? '')
    .replace(/[\r\n]+/g, ' ')
    .replace(/\s+/g, ' ')
    .trim()
}

function colLetter(col0: number): string {
  let n = col0 + 1
  let out = ''
  while (n > 0) {
    const rem = (n - 1) % 26
    out = String.fromCharCode(65 + rem) + out
    n = Math.floor((n - 1) / 26)
  }
  return out
}

function toNum(v: unknown): number | null {
  if (v === null || v === undefined || v === '') return null
  // `Number(new Date(...))` liefert den Millisekunden-Epoch, nicht NaN — eine
  // Zahlenspalte mit datums-/zeitartigem Zellformat käme sonst als
  // Milliardenwert in der Kalkulation an, ohne Fehler und ohne Warnung. Seit
  // der BIFF-Lesepfad `cellDates: true` setzt, liefern auch .xls-Dateien echte
  // Date-Objekte; 964 von 1076 Realdateien tragen Datumszellen, ein Großteil
  // davon in genau den Blättern, die dieser Parser liest.
  if (v instanceof Date) return null
  const n = Number(v)
  return isNaN(n) ? null : n
}

/** Best matching row for the MATERIAL header, scanning the first
 * HEADER_SCAN_MAX_ROWS rows exactly like qaf-parser.ts's findHeaderRow —
 * template files have the header at row 1, real BMW files deeper. // allow-customer-string
 */
export async function findMaterialHeaderRow(grid: unknown[][]): Promise<number | null> {
  if (!grid || grid.length === 0) return null
  const ctx = await loadMaterialRegistry()

  let bestIdx: number | null = null
  let bestScore = 0
  const scanLimit = Math.min(grid.length, HEADER_SCAN_MAX_ROWS)
  for (let i = 0; i < scanLimit; i++) {
    const row = grid[i] ?? []
    let score = 0
    for (const cell of row) {
      if (matchHeaderColumnSync(normalizeHeaderCell(cell), ctx)) score += 1
    }
    if (score > bestScore) {
      bestScore = score
      bestIdx = i
    }
  }
  return bestScore >= HEADER_MATCH_MIN ? bestIdx : null
}

// ── Parse-level diagnostics ─────────────────────────────────────────────────

export interface MaterialParseMeta {
  /** Arithmetic mean of per-column confidence over mapped columns — 1.0 for
   * an intact sheet, lower once normalized-only matches enter the mix. */
  parseConfidence: number
  /** Header cell text of every non-empty header-row cell that matched no
   * MATERIAL canonical field. */
  unmappedHeaders: string[]
  /** Number of header columns mapped to a MaterialFieldKey. */
  mappedFieldCount: number
  /** True once every CORE_MATERIAL_FIELD_KEYS was located. False means the
   * header is missing at least one core field — since KAR-958/P2, rows is
   * NOT unconditionally empty in that case anymore (see
   * MIN_SIGNAL_MAPPED_COLUMNS / `degradation` below and the module header of
   * parseMaterialWorksheet). */
  coreFieldsFound: boolean
  /** KAR-958/P2 — set only when `coreFieldsFound` is false AND at least
   * MIN_SIGNAL_MAPPED_COLUMNS columns mapped (partial extraction attempted).
   * `undefined` on every intact parse and on a genuinely empty/foreign sheet
   * (below the minimum signal — rows stays `[]` exactly as before this PR). */
  degradation?: FacetDegradation
  /**
   * KAR-927 (Multi-QAF-Programm P0.2, "Kandidaten-Sichtbarkeit an .find()-
   * Kollaps-Stellen") — an entry for EVERY OTHER worksheet that also matched
   * `isMaterialSheetName` but was NOT chosen (findMaterialWorksheet always
   * picks the FIRST match, exactly as before — this only reports what else
   * was there, selection itself is unchanged). `undefined` (key omitted,
   * never `[]`) whenever at most one MATERIAL-named sheet matched — the
   * overwhelming common case for a standard QAF.
   *
   * Adversarial-review fix (KAR-927 F1/F2, 12.07.2026, mirrors sbm-parser.ts's
   * identical fix): a PRIOR version of this field only listed a candidate
   * when a full `parseMaterialWorksheet` call on it ALSO located every
   * CORE_MATERIAL_FIELD_KEYS (`coreFieldsFound`) — an all-or-nothing gate
   * that (F2) fired a full worksheet parse (worksheetToGrid + header mapping
   * + data-row extraction) on every MATERIAL-named candidate purely to read
   * one throwaway boolean, and (F1) swallowed exactly the genuinely-broken
   * second sheets this field exists to surface (e.g. a duplicate MATERIAL
   * tab whose Positionsnummer header was renamed/corrupted).
   *
   * Fixed: EVERY other name-matching sheet is now listed unconditionally,
   * each tagged `plausibleData` by a CHEAP, BOUNDED header-region scan (see
   * candidate-sheet-plausibility.ts module header) for the single MATERIAL
   * identity label — materialDesignation ("Benennung Rohmaterial /
   * Kaufteil"), reusing the exact same registry-driven matchHeaderColumnSync
   * a real header-row scan uses, not a newly hardcoded label string.
   * Deliberately weaker than the old `coreFieldsFound` gate: a sheet with a
   * broken/renamed Positionsnummer header but an intact Materialbezeichnung
   * column still reports `plausibleData: true` (fixes F1). A non-data
   * reference/helper tab that merely matches the bare "material" substring
   * (module-sheet-names.ts MODULE_SHEET_NAME_ALIASES.MATERIAL) but carries
   * no such column still reports `plausibleData: false` — the anti-spam
   * property is preserved via materialParseMetaToPlausibilityIssue below,
   * which only turns `plausibleData: true` entries into a user-facing
   * message. */
  ignoredCandidateSheets?: IgnoredCandidateSheetEntry[]
}

export type MaterialParseResult = MaterialRow[] & MaterialParseMeta

function withParseMeta(rows: MaterialRow[], meta: MaterialParseMeta): MaterialParseResult {
  return Object.assign(rows, meta) as MaterialParseResult
}

const EMPTY_PARSE_META: MaterialParseMeta = {
  parseConfidence: 0,
  unmappedHeaders: [],
  mappedFieldCount: 0,
  coreFieldsFound: false,
}

/**
 * Parse an already-located MATERIAL worksheet. Pure aside from the lazy
 * registry import. Never throws on a degraded/unusable header (see
 * CORE_MATERIAL_FIELD_KEYS doc comment) — returns an empty, flagged result
 * instead, so a malformed MATERIAL sheet cannot fail the whole file ingest.
 */
export async function parseMaterialWorksheet(ws: Worksheet): Promise<MaterialParseResult> {
  const grid = worksheetToGrid(ws)
  if (grid.length < 2) return withParseMeta([], EMPTY_PARSE_META)

  const headerIdx = await findMaterialHeaderRow(grid)
  if (headerIdx === null) return withParseMeta([], EMPTY_PARSE_META)

  const headerRow = (grid[headerIdx] as unknown[]).map(normalizeHeaderCell)
  const sheetName = ws.name

  const ctx = await loadMaterialRegistry()
  const colMap = new Map<number, MaterialFieldKey>()
  const colConfidence = new Map<number, number>()
  const unmappedHeaders: string[] = []
  const usedKeys = new Set<MaterialFieldKey>()

  for (let i = 0; i < headerRow.length; i++) {
    const cell = headerRow[i]
    if (cell === '') continue
    const match = matchHeaderColumnSync(cell, ctx)
    if (!match) {
      unmappedHeaders.push(cell)
      continue
    }

    let key = match.key
    if (usedKeys.has(key)) {
      // Documented BMW source-data label duplicate (canonical-fields.ts // allow-customer-string
      // material_mengeneinheit_label_duplicate — "Mengeneinheit" appears
      // verbatim twice: once genuinely, once as a mislabeled MGK column).
      // Resolve left-to-right: the first occurrence already claimed its
      // field key, so this (later) column must be the OTHER member of the
      // same collisionGroup that hasn't been used yet. No silent overwrite.
      const group = match.collisionGroup
      const altKey = group ? ctx.collisionGroupMembers.get(group)?.find((k) => !usedKeys.has(k)) : undefined
      if (!altKey) {
        unmappedHeaders.push(cell)
        continue
      }
      key = altKey
    }

    colMap.set(i, key)
    colConfidence.set(i, match.confidence)
    usedKeys.add(key)
  }

  const mappedKeys = new Set(colMap.values())
  const coreFieldsFound = CORE_MATERIAL_FIELD_KEYS.every((k) => mappedKeys.has(k))
  // KAR-958/P2 (gate-audit.md B5): below the minimum-signal floor, this is
  // treated exactly as before this PR — a genuinely empty/foreign sheet, no
  // extraction attempted. At/above it, a missing core field no longer
  // discards every already-mapped column (see the row-push guard below,
  // which independently still requires materialDesignation — the row
  // identity field, itself possibly one of the missing core fields — so a
  // sheet without ANY identifiable rows still yields `rows: []` here too,
  // just now via that guard instead of an unconditional early return).
  if (colMap.size < MIN_SIGNAL_MAPPED_COLUMNS) {
    return withParseMeta([], {
      parseConfidence: 0,
      unmappedHeaders,
      mappedFieldCount: colMap.size,
      coreFieldsFound: false,
    })
  }

  const mappedFieldCount = colMap.size
  const confidences = [...colConfidence.values()]
  const parseConfidence = confidences.length > 0 ? confidences.reduce((a, b) => a + b, 0) / confidences.length : 0

  const rows: MaterialRow[] = []

  for (let i = headerIdx + 1; i < grid.length; i++) {
    const row = (grid[i] as unknown[]) ?? []
    if (row.every((c) => c === '' || c === null || c === undefined)) break

    const r: Partial<MaterialRowValues> = {}
    for (const key of ALL_FIELD_KEYS) {
      ;(r as Record<string, unknown>)[key] = TEXT_FIELDS.has(key) ? '' : null
    }

    const sourceCells: Partial<Record<MaterialFieldKey, string>> = {}
    const normalized: Partial<Record<MaterialFieldKey, string | number | null>> = {}
    const rawText: Partial<Record<MaterialFieldKey, string>> = {}
    const sheetRowNumber = i + 1 // worksheetToGrid is 0-based, ExcelJS rows are 1-based

    for (const [colIdx, key] of colMap.entries()) {
      const val = row[colIdx]
      const normVal: string | number | null = TEXT_FIELDS.has(key) ? String(val ?? '').trim() : toNum(val)
      ;(r as Record<string, unknown>)[key] = normVal
      sourceCells[key] = `${sheetName}!${colLetter(colIdx)}${sheetRowNumber}`
      normalized[key] = normVal
      if (!TEXT_FIELDS.has(key) && normVal === null && val !== null && val !== undefined && String(val).trim() !== '') {
        rawText[key] = String(val)
      }
    }

    // Row-push guard mirrors qaf-parser.ts's pattern (keep only rows that
    // actually carry a core text identity) — materialDesignation is the
    // MATERIAL sheet's row-identity field (BMW footer/subtotal rows never // allow-customer-string
    // carry a Rohmaterial/Kaufteil designation).
    if (r.materialDesignation) {
      rows.push({ ...(r as MaterialRowValues), sourceCells, normalized, rawText })
    }
  }

  const degradation: FacetDegradation | undefined = coreFieldsFound
    ? undefined
    : {
        facet: 'material',
        reason: 'PARSE_FAILED',
        sheet: sheetName,
        message: `MATERIAL-Kernfelder fehlen: ${CORE_MATERIAL_FIELD_KEYS.filter((k) => !mappedKeys.has(k))
          .map((k) => CORE_FIELD_LABELS_DE[k] ?? k)
          .join(', ')}.`,
      }

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

/**
 * Locate + parse the MATERIAL sheet from an already-loaded workbook. Returns
 * null when the workbook has no MATERIAL sheet at all (the additive gate —
 * distinct from "sheet present but header unusable", which returns an empty
 * MaterialParseResult with coreFieldsFound:false instead, see
 * parseMaterialWorksheet doc comment).
 */
export async function parseMaterialSheet(wb: { worksheets: Worksheet[] }): Promise<MaterialParseResult | null> {
  const ws = findMaterialWorksheet(wb)
  if (!ws) return null
  const result = await parseMaterialWorksheet(ws)
  // KAR-927/P0.2: findMaterialWorksheet's `.find()` only ever returns the
  // FIRST matching worksheet — this reports the OTHER worksheet name(s) that
  // also matched `isMaterialSheetName` but were silently discarded.
  // `nameCandidates[0]` is provably the same worksheet `ws` already is (both
  // scan `wb.worksheets` from index 0 forward, stopping at the first
  // match), so selection stays byte-identical — this is purely additive.
  //
  // Adversarial-review fix (KAR-927 F1/F2, 12.07.2026, mirrors sbm-parser.ts's
  // identical fix) — see MaterialParseMeta.ignoredCandidateSheets doc comment
  // above for the full rationale: EVERY other name-matching sheet is listed
  // unconditionally, no full parse, no all-or-nothing `coreFieldsFound` gate.
  // Each entry's `plausibleData` comes from a cheap, BOUNDED header-region
  // scan (candidate-sheet-plausibility.ts) for the single MATERIAL identity
  // label — materialDesignation ("Benennung Rohmaterial / Kaufteil"), the
  // field CORE_MATERIAL_FIELD_KEYS[1] already names as the row identity
  // field — reusing the exact same registry-driven matchHeaderColumnSync a
  // real header-row scan uses, not a newly hardcoded label string.
  const nameCandidates = wb.worksheets.filter((w) => isMaterialSheetName(w.name))
  if (nameCandidates.length > 1) {
    const ctx = await loadMaterialRegistry()
    const isMaterialDesignationLabel = (cell: string): boolean =>
      matchHeaderColumnSync(cell, ctx)?.key === 'materialDesignation'
    result.ignoredCandidateSheets = nameCandidates.slice(1).map((w) => ({
      name: w.name,
      plausibleData: worksheetHasHeaderLabelInRegion(w, HEADER_SCAN_MAX_ROWS, CANDIDATE_SCAN_MAX_COLS, isMaterialDesignationLabel),
    }))
  }
  return result
}

// ── Parse-meta -> PlausibilityIssue bridge (KAR-927/P0.2) ──────────────────
//
// Reuses the qaf_plausibility_issue path (no schema change), same pattern as
// plausibility.ts's manufacturingIgnoredCandidatesToPlausibilityIssue /
// rmr-parser.ts's rmrParseMetaToPlausibilityIssue — takes the meta as a
// plain object rather than growing compare.ts's import surface.
// Threaded through actions.ts (ingest -> g60_meta.material.parseMeta ->
// KAR-899-style rehydration) and compare.ts (QafFileParsed.materialParseMeta),
// same three-hop wiring manufacturingParseMeta/rmrParseMeta already use.

/**
 * Pure. Returns null when there is nothing to REPORT — no candidates at all,
 * OR every candidate is `plausibleData: false` (adversarial-review fix,
 * KAR-927 F1/F2 — mirrors sbm-parser.ts's sbmParseMetaToPlausibilityIssue,
 * same rationale). Accepts both the current `IgnoredCandidateSheetEntry[]`
 * shape and a pre-redesign persisted `string[]`
 * (normalizeIgnoredCandidateSheets — a legacy entry is treated as
 * `plausibleData: true`, since it only ever existed under the old strict
 * `coreFieldsFound` gate).
 */
export function materialParseMetaToPlausibilityIssue(
  meta: IgnoredCandidateSheetsMeta,
  side: ComparisonSide,
): PlausibilityIssue | null {
  const plausible = normalizeIgnoredCandidateSheets(meta.ignoredCandidateSheets).filter((e) => e.plausibleData)
  if (plausible.length === 0) return null
  const names = plausible.map((e) => e.name)
  return {
    type: 'parser_ignored_material_candidate_sheets',
    severity: 'pruefen' as PlausibilitySeverity,
    step: side,
    explanation: `Mehrere MATERIAL-Kandidaten-Sheets im Workbook gefunden (${side}) — nur das erste passende Sheet wird ausgewertet, ignoriert: ${names.join(', ')}.`,
    explanationEn: `Multiple MATERIAL candidate sheets found in the workbook (${side}) — only the first matching sheet is evaluated, ignored: ${names.join(', ')}.`,
  }
}

/**
 * Derive the tri-state `MaterialRow[] | null` value callers (actions.ts's
 * ingestQafUpload, and via it QafFileParsed.materialRows / ReconciliationInput.
 * materialRows) must persist/thread from a `parseMaterialSheet` result — the
 * single source of truth for that derivation, mirroring sbm-parser.ts's
 * sbmRowsForReconciliation (adversarial-review finding on the original
 * KAR-898 PR, confidence 82, point 2: "dasselbe Muster spiegelnd für
 * materialRows anwenden").
 *
 * A naive `parsed ? [...parsed] : null` collapses two different situations
 * into the same truthy empty array: the sheet exists but its header is too
 * degraded to trust (`coreFieldsFound: false`), and the sheet exists with an
 * intact header but legitimately has zero data rows. For material_detail_sum
 * this distinction does not currently change the resulting STATUS (both
 * `null` and a confirmed-empty array already resolve to `nicht_pruefbar`
 * there — see reconciliation.ts evaluateMaterialDetailSumCheck), but it did
 * produce a misleading REASON text ("Keine MATERIAL-Zeilen vorhanden" for a
 * sheet whose header was actually never readable) and left the same latent
 * null/[] conflation risk that turned into a real false-`bestanden` bug for
 * sbm_detail_sum. Fixed here proactively rather than waiting for a
 * material-side incident (KAR-899 tracks the reconciliation staleness gap
 * separately — this is not that).
 */
export function materialRowsForReconciliation(parsed: MaterialParseResult | null): MaterialRow[] | null {
  if (parsed === null) return null
  if (!parsed.coreFieldsFound) return null
  return [...parsed]
}

/**
 * The shape actions.ts's ingestQafUpload persists on `qaf_file.g60_meta.material`
 * (see that file's `materialMeta` local) — `null` when no MATERIAL sheet was
 * found at all, otherwise the parsed rows plus the same MaterialParseMeta a
 * live MaterialParseResult carries.
 */
export interface PersistedMaterialMeta {
  rows: MaterialRow[]
  parseMeta: MaterialParseMeta
}

/**
 * Persisted-JSONB counterpart to materialRowsForReconciliation above (KAR-899
 * reconciliation-staleness fix): derive the same tri-state `MaterialRow[] |
 * null | undefined` value from `qaf_file.g60_meta.material` for callers that
 * rehydrate a comparison from the database instead of holding a live
 * MaterialParseResult from the current request (recompareComparison's
 * sideOf() — see actions.ts). `undefined` when the persisted g60_meta carries
 * no `material` key at all: the file was ingested before KAR-897/P1.6 (or is
 * a G60 file, which never sets it), so no MATERIAL parse was ever attempted
 * for it — this is the tri-state's "no parse attempted, check skipped
 * entirely" case, exactly like a fresh ingest that found no MATERIAL sheet
 * would NOT produce (that case persists an explicit `material: null`).
 *
 * Reconstructs a MaterialParseResult (rows + parseMeta merged the same way
 * `withParseMeta` does at parse time) and delegates the null/degraded-vs-
 * confirmed decision to materialRowsForReconciliation itself, so the two
 * entry points (live parse vs. rehydrated JSONB) cannot drift on the
 * coreFieldsFound semantics introduced in 4d158f6.
 */
export function materialRowsFromPersistedMeta(
  meta: PersistedMaterialMeta | null | undefined,
): MaterialRow[] | null | undefined {
  if (meta === undefined) return undefined
  if (meta === null) return null
  return materialRowsForReconciliation(withParseMeta([...meta.rows], meta.parseMeta))
}

// ── Rohstoffrisikobeteiligung / RMR split-row grouping (Leitfaden [17]) ────

/** Mengeneinheit marker for the variable/raw-material-risk cost share
 * (Leitfaden [17]: "Bei variablen Materialkostenanteil wird in der Spalte
 * 'Mengeneinheit' die Auswahl 'Raw_material_risk_(kg)' selektiert"). */
export const MATERIAL_UNIT_RAW_MATERIAL_RISK = 'Raw_material_risk_(kg)'

/** Mengeneinheit marker used for Kaufteil rows and the collector
 * "Processcosts, scrap, Overhead" position on magnet/sinter-part materials
 * (Leitfaden [16]/[26]-[28]). Not used for grouping logic here (that only // allow-customer-string
 * needs the RMR marker), kept as a named constant since both markers are
 * BMW-specific dropdown option strings documented by the same Leitfaden // allow-customer-string
 * pages and a future reader should not have to re-derive it from the raw
 * example rows.
 */
export const MATERIAL_UNIT_PURCHASED_PART = 'Unit_(purchased_part)'

function isRawMaterialRiskUnit(unitOfMeasure: string): boolean {
  return normalizeProcessName(unitOfMeasure) === normalizeProcessName(MATERIAL_UNIT_RAW_MATERIAL_RISK)
}

/**
 * A logical material position: one or more parsed rows sharing the same
 * Positionsnummer. `isRawMaterialRiskSplit` is true when the group has more
 * than one row AND at least one of them carries the RMR Mengeneinheit marker
 * — i.e. this is a fixed/variable raw-material-risk split (Leitfaden [17]),
 * not a genuine duplicate-position parse anomaly. Cross-file matching of
 * these groups against an ALT/NEU counterpart is explicitly out of scope for
 * this PR (see module header) — this only groups rows WITHIN one parsed file.
 */
export interface MaterialPositionGroup {
  positionNumber: string
  rows: MaterialRow[]
  isRawMaterialRiskSplit: boolean
}

/**
 * Group parsed MATERIAL rows by (normalized) Positionsnummer, preserving
 * first-seen order. Pure. A group of size 1 is the common case (no raw-
 * material-risk participation); size >1 is either an RMR split (flagged) or
 * — for materials with multiple raw-stock cost components, e.g. the
 * Alu_Coil/Steel_Coil/Alu_Profile examples on Leitfaden [22] (LME/ECDP/Value
 * added/scrap-consumption rows, all sharing one Positionsnummer without any
 * of them using the RMR Mengeneinheit) or the magnet/sinter-part multi-
 * rohstoff compositions on [26]-[28] — a legitimately multi-row material
 * position that is intentionally NOT flagged as an RMR split (isRawMaterial-
 * RiskSplit stays false), since none of the Leitfaden's documented business
 * rules treat those as a fixed/variable pair the way [17] does.
 */
export function groupMaterialRowsByPosition(rows: readonly MaterialRow[]): MaterialPositionGroup[] {
  const order: string[] = []
  const byPosition = new Map<string, MaterialRow[]>()
  for (const row of rows) {
    const pos = normalizePosition(row.positionNumber)
    if (!byPosition.has(pos)) {
      byPosition.set(pos, [])
      order.push(pos)
    }
    byPosition.get(pos)!.push(row)
  }
  return order.map((pos) => {
    const group = byPosition.get(pos)!
    return {
      positionNumber: pos,
      rows: group,
      isRawMaterialRiskSplit: group.length > 1 && group.some((r) => isRawMaterialRiskUnit(r.unitOfMeasure)),
    }
  })
}

/**
 * Logical row key (Master-Prompt §11): Positionsnummer + Materialbezeichnung
 * + (if present) Rohstoffbezeichnung — the identity a future cross-file
 * matcher would key on. Two RMR split rows of the SAME position deliberately
 * produce DIFFERENT keys when their Rohstoffbezeichnung differs (the fixed
 * share often has none, the variable share names the raw material) — callers
 * that need "these belong together" use groupMaterialRowsByPosition instead,
 * which groups by Positionsnummer alone. Pure.
 */
export function materialRowLogicalKey(row: MaterialRow): string {
  const pos = normalizePosition(row.positionNumber)
  const designation = normalizeProcessName(row.materialDesignation)
  const rawMaterial = normalizeProcessName(row.rawMaterialDesignation)
  return rawMaterial ? `${pos}::${designation}::${rawMaterial}` : `${pos}::${designation}`
}