// Fertigungskosten row parser (KAR-893 / P1.2): header→field mapping now goes
// through the canonical field model (canonical-model.ts/canonical-fields.ts,
// KAR-892/P1.1) instead of an exact-string-only dictionary, with a
// degradation path instead of a hard failure below HEADER_MATCH_MIN.
//
// HEADER_TO_KEY below is kept verbatim as the legacy/back-compat dictionary
// (tests import it, template-fingerprint.ts's evidence citations reference
// it) — it is NOT the runtime matching source anymore. All 44 of its DE+EN
// strings already exist verbatim as labelDe/labelEn on the 22 MANUFACTURING
// canonical fields (verified: canonical-fields.ts MANUFACTURING_FIELDS block,
// evidence `CODE("qaf-parser.ts:HEADER_TO_KEY")` on every entry) — so no new
// aliases needed to be added there for this PR; the model already "absorbed"
// this dictionary rather than needing a parallel alias migration.
//
// ── Matching tiers (matchHeaderColumn below) ────────────────────────────────
//   1.0 exact  — header cell (whitespace/newline-collapsed only, same
//                normalizeHeaderCell as before) equals a MANUFACTURING
//                field's labelDe or labelEn verbatim. Byte-identical to the
//                old HEADER_TO_KEY exact-match behavior for all 44 known
//                strings — this is why the 10 pre-existing tests in
//                lib/__tests__/qaf-parser.test.ts pass unchanged.
//   0.9 normalized — no exact hit, but canonical-model.ts's findByAlias()
//                (case-fold, umlaut-fold, whitespace-collapse, footnote/
//                numbering-strip via normalizeCanonicalLabel) finds exactly
//                one MANUFACTURING field whose label/alias set normalizes to
//                the same key. Deliberately NOT typo-correction or
//                substring/edit-distance fuzzing (task instruction: "KEIN
//                Raten über Fuzzy-Substring hinaus — konservativ bleiben") —
//                only whatever canonical-model.ts's established normalizer
//                already folds away. An ambiguous normalized match (>1
//                distinct field key) is treated as NO match, same
//                conservative principle.
//   unmapped  — neither tier hit; the column is dropped (field stays null
//                for every row, see the default-filled shape below) and its
//                header text is reported in QAFParseResult.unmappedHeaders.
//
// ── Client-bundle discipline (adversarial-review fix, same PR) ─────────────
// components/qaf/qaf-client.tsx ('use client') calls parseQAFTemplate/
// findHeaderRow/matchHeaderColumn directly — this file ships to the browser.
// canonical-model.ts + canonical-fields.ts (the full ~3.7k-line, 12-module
// registry, of which only the 22 MANUFACTURING fields are ever read here)
// must therefore stay OUT of the static import graph, exactly like exceljs
// a few lines below (`const ExcelJS = (await import('exceljs')).default`,
// "keep it out of client bundles"). loadManufacturingRegistry() is the same
// pattern: a dynamic import, cached after the first call (module-level
// Promise) so repeated parses/matches don't re-import. Only TYPE imports
// (`import type`, erased at compile time, zero runtime bundle cost) are
// static below.

import type { CanonicalField } from './qaf-differences/internal/canonical-fields.types'
import type { FormulaProvenance } from './qaf-differences/internal/formula-engine'

/**
 * DE/EN/typo substrings that identify the "manufacturing costs" worksheet
 * tab across BMW QAF locales (case-insensitive substring match against the // allow-customer-string
 * lowercased tab name) — see parseQAFTemplate's sheet-picker for the full
 * rationale. Exported so lib/__tests__/qaf-parser.test.ts can assert this
 * list stays in sync with qaf-differences/internal/module-sheet-names.ts's
 * MODULE_SHEET_NAME_ALIASES.MANUFACTURING (KAR-906/P3.2 #283-review fix —
 * a comment here previously claimed that drift test already existed; it did
 * not, see the sheet-picker comment below).
 */
export const MANUFACTURING_SHEET_NAME_SUBSTRINGS = ['fertigungskosten', 'manufacturing cost', 'manufactering cost'] as const

function matchesManufacturingSheetName(sheetName: string): boolean {
  const n = sheetName.toLowerCase()
  return MANUFACTURING_SHEET_NAME_SUBSTRINGS.some((s) => n.includes(s))
}

// Column headers — must match exactly what the template generates
export const TEMPLATE_HEADERS = [
  'Positionsnummer Fertigungsschritt',
  'Teilebenennung',
  'Prozessbezeichnung',
  'Bezeichnung Anlage/Maschine/Typ',
  'Standort',
  'Beschaffungswährung BW',
  'Zykluszeit [s]',
  'Teile pro Zyklus',
  'Anzahl direkte Mitarbeiter',
  'Kalkulatorisch angesetzte direkte Lohnkosten [BW/h]',
  'Kalkulatorisch angesetzte Lohn-zuschlagssätze SGK [%]',
  'Maschinenstundensatz MSS [BW/h]',
  'Rüstkosten pro Stück [BW]',
  'Fertigungseinzelkosten FEK [BW]',
  'Restfertigungsgemeinkosten (RFGK) Stundensatz [BW/h]',
  'Fertigungskosten FK [BW]',
  'Angebotswährung AW',
  'Wechselkurs [AW/BW]',
  'Anzahl pro Angebotsteil',
  'Fertigungskosten FK [AW]',
  'Ausschuss pro Prozessschritt [%]',
  'Ausschusskosten Fertigung [AW]',
] as const

export interface QAFRowValues {
  positionsnummer: string
  teilebenennung: string
  prozessbezeichnung: string
  bezeichnungAnlage: string
  standort: string
  beschaffungswaehrung: string
  zykluszeit: number | null
  teileProZyklus: number | null
  anzahlMA: number | null
  lohnkosten: number | null
  lohnzuschlagssaetze: number | null
  mss: number | null
  ruestkosten: number | null
  fek: number | null
  rfgk: number | null
  fk: number | null
  angebotswaehrung: string
  wechselkurs: number | null
  anzahlProAngebotsteil: number | null
  fkAW: number | null
  ausschuss: number | null
  ausschusskosten: number | null
}

/** Field key domain of QAFRowValues — used to type the sourceCells/normalized maps (KAR-886). */
export type QAFFieldKey = keyof QAFRowValues

/**
 * A parsed Fertigungskosten row. `sourceCells`/`normalized` are additive
 * (KAR-886): per-field cell provenance ("Sheet!A1") and the structured
 * normalized value, for the subset of fields actually located in the header
 * row. Both are optional — rows rehydrated from raw_values JSON persisted
 * before KAR-886 lack them entirely; readers must tolerate undefined.
 *
 * `rawText` is additive again (KAR-891, P0.6): the original cell text for a
 * numeric field that did NOT parse as a number (e.g. "n.a.", "entfällt"),
 * keyed the same way as sourceCells/normalized. It exists so the diff engine
 * can tell an explicit not-applicable marker apart from a genuinely empty
 * cell — both otherwise collapse to the same `null` in the typed field.
 * Optional for the same reason: rows persisted before KAR-891 lack it.
 *
 * `formulas` is additive again (KAR-900, P2.1): per-field Excel formula
 * provenance (raw text, normalized form, structural hash — see
 * formula-engine.ts), keyed the same way as sourceCells/normalized/rawText.
 * Only set for numeric fields whose cell actually carried a formula (ExcelJS
 * `cell.formula`) — a plain value-only cell gets no entry, same "no noise"
 * discipline as rawText. Lives inside raw_values JSONB for free on persist
 * (same mechanism sourceCells/normalized already rely on — see
 * persistence-mapper.ts buildManufacturingStepRow) — no schema change.
 *
 * `manualOverride` is additive again (KAR-912, P4.2): per-field manual
 * mapping-override provenance — set when a user corrected a field the
 * parser mapped wrongly or not at all (field-mapping-override.ts
 * applyFieldMappingOverrides), keyed the same way as sourceCells/normalized/
 * rawText/formulas. Distinct from the row-level ManualPin (matcher.ts,
 * KAR-845, which pairs an ALT step to a NEU step): this marks a single
 * FIELD's VALUE as user-corrected, independent of matching. Optional for the
 * same reason as the others: rows persisted before KAR-912 lack it.
 */
export type QAFRow = QAFRowValues & {
  sourceCells?: Partial<Record<QAFFieldKey, string>>
  normalized?: Partial<Record<QAFFieldKey, string | number | null>>
  rawText?: Partial<Record<QAFFieldKey, string>>
  formulas?: Partial<Record<QAFFieldKey, FormulaProvenance>>
  manualOverride?: Partial<Record<QAFFieldKey, { sourceDescription: string; setBy: string; setAt: string }>>
}

/**
 * Parse-level diagnostics (KAR-893 / P1.2), additive to the QAFRow[] parse
 * result — see QAFParseResult below for why these live as extra properties
 * on the array rather than a wrapper object (existing callers destructure/
 * iterate/`.length` the return value as a plain array; a wrapper object
 * would break every one of them, which the task explicitly rules out:
 * "Bestehende Rückgabe-Shape additiv erweitern, NICHT brechen").
 */
export interface QAFParseMeta {
  /** Arithmetic mean of per-column confidence (1.0 exact / 0.9 normalized)
   * over columns that were actually mapped to a QAFFieldKey. 1.0 for an
   * intact template (all columns exact), lower once normalized-only matches
   * or a genuinely degraded header enter the mix. 0 only if colMap ended up
   * empty, which cannot happen once the core-field gate has passed (the 3
   * core fields are always mapped by then). */
  parseConfidence: number
  /** Header cell text (already whitespace/newline-normalized) of every
   * non-empty header-row cell that matched no MANUFACTURING canonical field
   * — neither exact nor normalized. Empty when every non-blank header
   * column mapped. */
  unmappedHeaders: string[]
  /** Number of header columns that mapped to a QAFFieldKey (colMap.size). */
  mappedFieldCount: number
  /**
   * KAR-927 (Multi-QAF-Programm P0.2, "Kandidaten-Sichtbarkeit an .find()-
   * Kollaps-Stellen") — sheet names of every OTHER worksheet that also
   * matched `matchesManufacturingSheetName` but was NOT chosen (the first
   * match, exactly as `ejWb.worksheets.find(...)` already picked before this
   * field existed — selection itself is unchanged, this only reports what
   * else was there). `undefined` (key omitted, never `[]`) whenever at most
   * one worksheet matched — the overwhelming common case for a standard QAF,
   * so this never adds noise there. Set for a workbook where more than one
   * sheet name matches (e.g. a Multi-QAF file bundling several
   * Fertigungskosten/Manufacturing-costs tabs) — until Multi-QAF parsing
   * itself lands (backlog), the FIRST match keeps being the one actually
   * parsed; this field only makes the silently-dropped others visible.
   */
  ignoredCandidateSheets?: string[]
}

/** parseQAFTemplate's actual return value: a normal QAFRow[] (every existing
 * caller keeps working unchanged — `.length`, `[...]`, `for...of`, array
 * destructuring) with QAFParseMeta attached as extra properties, exactly the
 * same additive pattern already used for sourceCells/normalized/rawText on
 * QAFRow itself (KAR-886/891). */
export type QAFParseResult = QAFRow[] & QAFParseMeta

/** Exported (KAR-912, P4.2) so field-mapping-override.ts can classify a
 * manual override's raw text input the same way the parser itself does —
 * text fields stored verbatim, everything else parsed as a number. Single
 * source of truth for "which QAFFieldKeys are text, not numeric" — no
 * separate/divergent list in the override module. */
export const TEXT_FIELDS: Set<QAFFieldKey> = new Set([
  'positionsnummer',
  'teilebenennung',
  'prozessbezeichnung',
  'bezeichnungAnlage',
  'standort',
  'beschaffungswaehrung',
  'angebotswaehrung',
])

// Map exact header text → QAFRow field key
// Supports both DE (German template) and EN (v8 English) customer variants. // allow-customer-string
export const HEADER_TO_KEY: Record<string, QAFFieldKey> = {
  // ── German (template + DE customer QAFs) ──────────────────────────────────
  'Positionsnummer Fertigungsschritt':                        'positionsnummer',
  'Teilebenennung':                                           'teilebenennung',
  'Prozessbezeichnung':                                       'prozessbezeichnung',
  'Bezeichnung Anlage/Maschine/Typ':                          'bezeichnungAnlage',
  'Standort':                                                 'standort',
  'Beschaffungswährung BW':                                   'beschaffungswaehrung',
  'Zykluszeit [s]':                                           'zykluszeit',
  'Teile pro Zyklus':                                         'teileProZyklus',
  'Anzahl direkte Mitarbeiter':                               'anzahlMA',
  'Kalkulatorisch angesetzte direkte Lohnkosten [BW/h]':      'lohnkosten',
  'Kalkulatorisch angesetzte Lohn-zuschlagssätze SGK [%]':    'lohnzuschlagssaetze',
  'Maschinenstundensatz MSS [BW/h]':                          'mss',
  'Rüstkosten pro Stück [BW]':                                'ruestkosten',
  'Fertigungseinzelkosten FEK [BW]':                          'fek',
  'Restfertigungsgemeinkosten (RFGK) Stundensatz [BW/h]':     'rfgk',
  'Fertigungskosten FK [BW]':                                 'fk',
  'Angebotswährung AW':                                       'angebotswaehrung',
  'Wechselkurs [AW/BW]':                                      'wechselkurs',
  'Anzahl pro Angebotsteil':                                  'anzahlProAngebotsteil',
  'Fertigungskosten FK [AW]':                                 'fkAW',
  'Ausschuss pro Prozessschritt [%]':                         'ausschuss',
  'Ausschusskosten Fertigung [AW]':                           'ausschusskosten',
  // ── English (QAF v8 EN customer variant) — KAR-340 follow-up ──────────── // allow-customer-string
  'Item number Manufacturing step':                           'positionsnummer',
  'Parts designation':                                        'teilebenennung',
  'Process designation':                                      'prozessbezeichnung',
  'Designation Facility/machine/type':                        'bezeichnungAnlage',
  'Site':                                                     'standort',
  'Procurement currency [BW]':                                'beschaffungswaehrung',
  'Cycle time [s]':                                           'zykluszeit',
  'Parts per cycle':                                          'teileProZyklus',
  'Number of direct employees':                               'anzahlMA',
  'Imputed direct labor costs [BW/h]':                        'lohnkosten',
  'Imputed social overhead costs (SGK) [%]':                  'lohnzuschlagssaetze',
  'Machine-hour rate (MSS) [BW/h]':                           'mss',
  'Setup costs per unit [BW]':                                'ruestkosten',
  'Direct manufacturing costs (FEK) [BW]':                    'fek',
  'Remaining manufacturing overhead costs (RFGK) Hourly rate [BW/h]': 'rfgk',
  'Manufacturing costs (FK) [BW]':                            'fk',
  'Quotation currency [AW]':                                  'angebotswaehrung',
  'Exchange rate [AW/BW]':                                    'wechselkurs',
  'Number per quotation part':                                'anzahlProAngebotsteil',
  'Manufacturing costs FK [AW]':                              'fkAW',
  'Scrap per process step [%]':                               'ausschuss',
  'Scrap costs Manufacturing [AW]':                           'ausschusskosten',
}

/** All 22 canonical MANUFACTURING field keys, in TEMPLATE_HEADERS/
 * QAFRowValues declaration order — derived from HEADER_TO_KEY's DE entries
 * (a `Set` keeps first-insertion order, and the DE block above already lists
 * all 22 keys uniquely before the EN block repeats them) rather than a
 * separately hand-maintained list. Reused by field-mapping-override.ts
 * (KAR-912/P4.2) for override validation and by the override-setting UI for
 * its field picker. */
export const MANUFACTURING_FIELD_KEYS: readonly QAFFieldKey[] = [...new Set(Object.values(HEADER_TO_KEY))]

/** KAR-912 adversarial-review F2+F6 fix (PR #291, Sev 45 / Sev 62):
 * MANUFACTURING_FIELD_KEYS minus the two ROW-IDENTITY/ANCHOR fields:
 *
 *   - `positionsnummer` (F2, Sev 45): the row-identity key every override
 *     lookup (row matching, carry-forward-on-replace, the ALT/NEU field diff
 *     table) uses to find "this row". A manual override that overwrites
 *     `positionsnummer` itself would corrupt that lookup for every OTHER
 *     override/consumer of the row, and — worse — a user could no longer
 *     even find the row again to fix it (the override changes the very key
 *     used to address it).
 *
 *   - `prozessbezeichnung` (F6, Sev 62): the IDENTITY ANCHOR
 *     carryForwardFieldMappingOverrides compares between the OLD and NEW
 *     file on a replace (field-mapping-override.ts's identityAt). That
 *     anchor is read back off qaf_manufacturing_step.process_name — which
 *     persistence-mapper.ts's buildManufacturingStepRow populates from the
 *     OVERRIDE-APPLIED row (actions.ts's stepsForPersistence =
 *     applyFieldMappingOverrides(steps, …)), not the raw parser output. If
 *     `prozessbezeichnung` were overridable, a 2-hop replace chain could
 *     "bake" a user-typed value into process_name at hop 1, and hop 2's
 *     carry-forward guard would then compare the NEW file's genuine parser
 *     reading against that POISONED anchor instead of the OLD file's real
 *     one — with BMW's small, repetitive process vocabulary (Schweißen/ // allow-customer-string
 *     Montage/Lackieren/…), a co-located cost override could false-positive
 *     "identity confirmed" and land on an unrelated row — exactly the F1
 *     failure class, one hop later. Since `prozessbezeichnung` is itself a
 *     CORE/mandatory MANUFACTURING field (a missing/wrong value there
 *     already degrades the whole row's parseConfidence — coreFieldsFound:
 *     false — see canonical-fields.ts), the override editor is never the
 *     right tool to fix it anyway; a genuinely wrong Prozessbezeichnung
 *     needs a corrected source file, not a per-field patch that would also
 *     poison the very anchor future replaces rely on.
 *
 * Single source of truth for "which fields a field-mapping override may
 * target" — used by both the server-side validation
 * (field-mapping-override.ts's isOverridableFieldKey) and the
 * override-setting UI's field picker, so they cannot drift. */
export const OVERRIDABLE_MANUFACTURING_FIELD_KEYS: readonly QAFFieldKey[] = MANUFACTURING_FIELD_KEYS.filter(
  (k) => k !== 'positionsnummer' && k !== 'prozessbezeichnung',
)

const HEADER_MATCH_MIN = 5
const HEADER_SCAN_MAX_ROWS = 20

/**
 * The 3 header-level fields that MUST be located for a Fertigungskosten
 * header row to be usable at all (task instruction: "Position+Prozess+fk
 * müssen da sein"). Distinct from rule-engine.ts's row-VALUE mandatory set
 * (MANDATORY_STEP_TEXT_FIELDS/MANDATORY_STEP_NUMERIC_FIELD, which judges
 * whether a given row's cell is correctly filled) — this gate judges whether
 * the HEADER COLUMN for these fields exists at all. Below this minimum the
 * parse cannot produce a meaningful row set (in particular: the existing
 * row-push guard below only keeps a row when prozessbezeichnung or
 * teilebenennung is non-blank — losing the prozessbezeichnung column would
 * silently return zero rows without this gate) — parseQAFTemplate throws
 * instead of degrading further. At/above this minimum, everything else is
 * best-effort: missing fields stay null (existing default-filled shape),
 * QAFParseMeta reports what was lost.
 */
export const CORE_MANUFACTURING_FIELD_KEYS: readonly QAFFieldKey[] = ['positionsnummer', 'prozessbezeichnung', 'fk']

const CORE_FIELD_LABELS_DE: Partial<Record<QAFFieldKey, string>> = {
  positionsnummer: 'Positionsnummer Fertigungsschritt',
  prozessbezeichnung: 'Prozessbezeichnung',
  fk: 'Fertigungskosten FK [BW]',
}

function toNum(v: unknown): number | null {
  if (v === null || v === undefined || v === '') return null
  // Ein Date würde hier still zu einem Millisekunden-Epoch-Wert: `Number(new
  // Date('2026-07-27'))` ist 1785110400000, nicht NaN. Eine Zahlenspalte mit
  // versehentlich datums- oder zeitartigem Zellformat (eine Zykluszeit als
  // "mm:ss" genügt) käme damit als sinnlose Milliardenzahl in der Kalkulation
  // an — ohne Fehler, ohne Warnung. Beide Lesepfade erkennen Datumszellen rein
  // über das Zahlenformat (ExcelJS via utils.isDateFmt, SheetJS via cellDates),
  // treffen also dieselbe Fehleinschätzung.
  if (v instanceof Date) return null
  const n = Number(v)
  return isNaN(n) ? null : n
}

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

/** 0-based column index -> spreadsheet column letters (0 -> "A", 26 -> "AA"). */
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
}

interface ColumnMatch {
  key: QAFFieldKey
  /** 1.0 exact, 0.9 normalized-only — see module header. */
  confidence: number
}

/** Everything matchHeaderColumn needs from the (dynamically-loaded)
 * canonical registry — see loadManufacturingRegistry(). */
interface ManufacturingRegistryCtx {
  /** MANUFACTURING-only slice of the canonical registry. Scoping to this
   * module is required, not cosmetic: MATERIAL shares several labelDe
   * strings verbatim with MANUFACTURING (e.g. "Positionsnummer
   * Fertigungsschritt", "Teilebenennung") — matching against the full
   * registry would make every Fertigungskosten header ambiguous. */
  registry: readonly CanonicalField[]
  /** Inverse of QAF_FIELD_KEY_TO_CANONICAL (canonical-fields.ts), scoped to
   * MANUFACTURING ids only — every value in QAF_FIELD_KEY_TO_CANONICAL is
   * itself MANUFACTURING (see canonical-model.test.ts backward-mapping
   * assertions), so no filtering is needed beyond the mapping itself. */
  idToKey: Record<string, QAFFieldKey>
  /** canonical-model.ts's findByAlias, carried through the dynamic import
   * so callers don't need their own (client-bundle-leaking) static import. */
  findByAliasFn: (
    label: string,
    lang: 'de' | 'en' | undefined,
    registry: readonly CanonicalField[],
  ) => CanonicalField[]
}

/** Module-level cache: the dynamic import + registry derivation runs at most
 * once per process/bundle-load, every subsequent call awaits the already-
 * resolved promise (cheap microtask, not a re-import). See module header —
 * this is what keeps canonical-model.ts/canonical-fields.ts out of the
 * static import graph (and therefore out of the client bundle) while still
 * only paying the dynamic-import cost once. */
let manufacturingRegistryPromise: Promise<ManufacturingRegistryCtx> | null = null

async function loadManufacturingRegistry(): Promise<ManufacturingRegistryCtx> {
  if (!manufacturingRegistryPromise) {
    manufacturingRegistryPromise = (async () => {
      const [{ byModule, findByAlias }, { QAF_FIELD_KEY_TO_CANONICAL }] = await Promise.all([
        import('./qaf-differences/internal/canonical-model'),
        import('./qaf-differences/internal/canonical-fields'),
      ])
      return {
        registry: byModule('MANUFACTURING'),
        idToKey: Object.fromEntries(
          Object.entries(QAF_FIELD_KEY_TO_CANONICAL).map(([key, canonicalId]) => [canonicalId, key as QAFFieldKey]),
        ),
        findByAliasFn: findByAlias,
      }
    })()
  }
  return manufacturingRegistryPromise
}

/** Same dynamic-import discipline as loadManufacturingRegistry above (Bundle-
 * Lehre #272, KAR-900/P2.1) — formula-engine.ts uses node:crypto (sha256),
 * which must stay out of the client bundle. Cached after the first call.
 * Exposes both formula-provenance builders: buildFormulaProvenance for a
 * successfully resolved formula string, unresolvedFormulaProvenance for the
 * rare shared-formula-slave-could-not-be-translated edge case (see
 * UNRESOLVED_FORMULA sentinel below / formula-engine.ts module header
 * "Shared-Formula-Slaves", adversarial-review fix). */
interface FormulaProvenanceFns {
  buildFormulaProvenance: (raw: string) => FormulaProvenance
  unresolvedFormulaProvenance: () => FormulaProvenance
}

/** Sentinel distinguishing "cell.formulaType says this IS a shared formula,
 * but cell.formula could not resolve/translate it" from "this cell has no
 * formula at all" (null) in the raw formula grid below — internal to this
 * module only (never crosses a module boundary, never persisted). See
 * formula-engine.ts's FormulaProvenance.unresolved for how this becomes a
 * tri-state at the QAFRow.formulas level. */
const UNRESOLVED_FORMULA = Symbol('unresolved-shared-formula')

let formulaProvenanceFnsPromise: Promise<FormulaProvenanceFns> | null = null

async function loadFormulaProvenanceFns(): Promise<FormulaProvenanceFns> {
  if (!formulaProvenanceFnsPromise) {
    formulaProvenanceFnsPromise = import('./qaf-differences/internal/formula-engine').then((m) => ({
      buildFormulaProvenance: m.buildFormulaProvenance,
      unresolvedFormulaProvenance: m.unresolvedFormulaProvenance,
    }))
  }
  return formulaProvenanceFnsPromise
}

/** The actual matching logic, synchronous given an already-loaded registry
 * context — used by both the public matchHeaderColumn() below and the
 * per-cell scan loops in findHeaderRow/parseQAFTemplate, so the registry is
 * only awaited ONCE per call (not once per header cell). */
function matchHeaderColumnSync(headerCell: string, ctx: ManufacturingRegistryCtx): ColumnMatch | null {
  if (headerCell === '') return null

  // Tier 1 — exact (verbatim, mod the whitespace/newline collapse already
  // applied by the caller): byte-identical to the pre-KAR-893 HEADER_TO_KEY
  // exact-match behavior for all 44 known DE/EN strings.
  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 }
    }
  }

  // Tier 2 — normalized (findByAlias's case-fold/umlaut-fold/whitespace-
  // collapse/footnote-strip), scoped to MANUFACTURING. Ambiguous (>1
  // distinct field key) is treated the same as "no match".
  const hits = ctx.findByAliasFn(headerCell, undefined, ctx.registry)
  const distinctKeys = new Set(
    hits.map((f) => ctx.idToKey[f.id]).filter((k): k is QAFFieldKey => k !== undefined),
  )
  if (distinctKeys.size === 1) {
    const [key] = distinctKeys
    return { key, confidence: 0.9 }
  }

  return null
}

/**
 * Match one already-normalizeHeaderCell'd header string against the
 * MANUFACTURING canonical field registry. Pure aside from the lazy dynamic
 * import (loadManufacturingRegistry, cached after the first call — see
 * module header). See module header for the two-tier confidence rule;
 * returns null (unmapped) on no match or an ambiguous normalized match —
 * deliberately conservative, no fuzzy substring/edit-distance guessing
 * (task instruction). Async because the registry load is async; callers
 * that need to check many cells (findHeaderRow, parseQAFTemplate) load the
 * registry once themselves and call matchHeaderColumnSync directly instead
 * of awaiting this per cell.
 */
export async function matchHeaderColumn(headerCell: string): Promise<ColumnMatch | null> {
  const ctx = await loadManufacturingRegistry()
  return matchHeaderColumnSync(headerCell, ctx)
}

// Real BMW QAF files have headers at row 11 (index 10), while the Template // allow-customer-string
// has headers at row 1 (index 0). Auto-detect by scanning the first N rows
// and picking the row with the most matches against the canonical
// MANUFACTURING field registry (KAR-893/P1.2 — matchHeaderColumn, exact OR
// normalized tier both count towards the score; ambiguous/no-match don't).
// For every string previously in HEADER_TO_KEY this scores identically to
// the pre-KAR-893 exact-only scan (tier 1 always hits), so the existing
// regression tests are unaffected; a degraded/renamed template can now also
// reach the HEADER_MATCH_MIN threshold via tier-2 normalized hits, which was
// structurally impossible before.
//
// Multiple candidate rows resolve deterministically: most matches wins; on a
// tie, the LOWEST row index wins (the loop only replaces bestIdx on a STRICT
// `score > bestScore`, so an equal-scoring later row never overwrites an
// earlier one — this was already true before KAR-893, kept unchanged and
// now explicitly documented per task instruction).
//
// Async (KAR-893 client-bundle fix): loads the registry ONCE (cached after
// the first call, see loadManufacturingRegistry) then scores every cell
// synchronously via matchHeaderColumnSync — not one await per cell.
export async function findHeaderRow(raw: unknown[][]): Promise<number | null> {
  if (!raw || raw.length === 0) return null
  const ctx = await loadManufacturingRegistry()

  let bestIdx: number | null = null
  let bestScore = 0
  const scanLimit = Math.min(raw.length, HEADER_SCAN_MAX_ROWS)
  for (let i = 0; i < scanLimit; i++) {
    const row = raw[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
}

/** Attach QAFParseMeta onto a QAFRow[] array in place and return it typed as
 * QAFParseResult — the shared tail of every return path below. */
function withParseMeta(rows: QAFRow[], meta: QAFParseMeta): QAFParseResult {
  return Object.assign(rows, meta) as QAFParseResult
}

const EMPTY_PARSE_META: QAFParseMeta = { parseConfidence: 0, unmappedHeaders: [], mappedFieldCount: 0 }

export async function parseQAFTemplate(file: File): Promise<QAFParseResult> {
  const buffer = await file.arrayBuffer()
  // Verzweigung am Container wie in loadExcelWorkbook — hier NICHT über
  // workbook-adapter, weil der seinerseits parseQAFTemplate importiert
  // (Zyklus). Der Shim selbst hängt an nichts aus diesem Modul.
  const { isLegacyBiffBuffer, loadLegacyWorkbook } = await import('./qaf-differences/internal/legacy-workbook-shim')
  let ejWb
  if (isLegacyBiffBuffer(buffer)) {
    ejWb = await loadLegacyWorkbook(buffer)
  } else {
    const ExcelJS = (await import('exceljs')).default
    ejWb = new ExcelJS.Workbook()
    await ejWb.xlsx.load(buffer)
  }

  if (ejWb.worksheets.length === 0) throw new Error('Die Datei enthält keine Tabellenblätter.')

  // Pick the "manufacturing costs" sheet across BMW QAF locales: // allow-customer-string
  //   DE → "Fertigungskosten" // allow-customer-string
  //   EN → "Manufacturing costs" — BMW templates also use the misspelling "Manufactering costs" // allow-customer-string
  // Falls back to first sheet (lean Template upload).
  // KAR-905/P3.1: this DE/EN/typo alias list is documented as the
  // MANUFACTURING entry of qaf-differences/internal/module-sheet-names.ts's
  // MODULE_SHEET_NAME_ALIASES (the centralized source of truth every other
  // module's isXxxSheetName now reads from) — kept as an independent literal
  // list here (not importing that module) because this file is directly
  // imported by components/qaf/qaf-client.tsx ('use client') and must stay
  // maximally conservative about its static import surface (see module
  // header).
  //
  // KAR-906/P3.2 #283-review fix (confidence 80): a prior version of this
  // comment CLAIMED "a future edit to one without the other is a visible
  // test failure, not a silent drift" — that test did not actually exist
  // (module-sheet-names.test.ts only asserted matchesModuleSheetName against
  // its OWN hardcoded literals, never cross-referenced this array). The
  // constant below is now exported specifically so
  // lib/__tests__/qaf-parser.test.ts can assert MANUFACTURING_SHEET_NAME_
  // SUBSTRINGS and MODULE_SHEET_NAME_ALIASES.MANUFACTURING stay in sync for
  // real — see that test's "MANUFACTURING sheet-name alias drift guard"
  // describe block.
  //
  // KAR-927/P0.2 (Kandidaten-Sichtbarkeit): `.find()` only ever returns the
  // FIRST worksheet whose name matches — a workbook with more than one
  // matching sheet (a real capability-probe finding: a Multi-QAF file where
  // the intended Fertigungskosten sheet did not match at all, so this
  // fallback silently landed on an unrelated "Zusammenfassung" sheet) had no
  // visible trace of the other candidate(s) it discarded. Selection itself
  // is UNCHANGED — `manufacturingCandidates[0]` is the exact same worksheet
  // `.find()` would have returned (both scan `ejWb.worksheets` from index 0
  // forward and stop at the first match), so this is provably ergebnis-
  // neutral. `ejWb.worksheets[0]` fallback (no name match at all) is also
  // unchanged.
  const manufacturingCandidates = ejWb.worksheets.filter((w) => matchesManufacturingSheetName(w.name))
  const ws = manufacturingCandidates[0] ?? ejWb.worksheets[0]
  const ignoredManufacturingCandidateSheets =
    manufacturingCandidates.length > 1 ? manufacturingCandidates.slice(1).map((w) => w.name) : undefined

  // Build a 2-D array of raw cell values (mirroring sheet_to_json({header:1, raw:true})).
  // rowNumbers tracks each raw[] entry's real ExcelJS row.number: eachRow with
  // includeEmpty:false skips rows that were never touched at all, so a gap of
  // completely untouched rows would otherwise desync raw's array index from the
  // actual sheet row — and with it, every source-cell address (KAR-886).
  const raw: unknown[][] = []
  // Per-cell raw formula text (KAR-900/P2.1), parallel-indexed to `raw` —
  // populated in the SAME eachRow/eachCell pass (no extra sheet read).
  // null = no formula at all (plain value cell). UNRESOLVED_FORMULA = the
  // cell IS part of a shared-formula range but ExcelJS could not translate
  // it (missing/corrupt master — rare). A real string = the RESOLVED
  // formula text for this exact cell.
  //
  // Adversarial-review fix (KAR-900, 10.07.2026): reads cell.formula/
  // cell.formulaType (the Cell-level getters), NOT cell.value.formula.
  // cell.value only ever carries a literal `formula` key for the MASTER of a
  // shared/copy-down range (verified: exceljs FormulaValue._copyModel only
  // copies `formula` when model.formula is truthy) — every SLAVE cell in the
  // SAME range (routine in BMW Fertigungskosten copy-down columns) has // allow-customer-string
  // model.formula === undefined, so the old cell.value-based check silently
  // treated every slave as "no formula". cell.formula resolves a slave via
  // ExcelJS's own _getTranslatedFormula()/slideFormula(), which slides the
  // master's formula by the row/column offset to the slave's own position —
  // the returned text is already position-correct for THIS cell (see
  // formula-engine.ts module header "Shared-Formula-Slaves" for the full
  // writeup and the translation-normalization decision).
  const rawFormulas: (string | null | typeof UNRESOLVED_FORMULA)[][] = []
  const rowNumbers: number[] = []
  ws.eachRow({ includeEmpty: false }, (row) => {
    const cols: unknown[] = []
    const formulaCols: (string | null | typeof UNRESOLVED_FORMULA)[] = []
    row.eachCell({ includeEmpty: true }, (cell, colNum) => {
      // Prefer numeric value for formula results, fall back to text
      const v = cell.value
      cols[colNum - 1] =
        v !== null && typeof v === 'object' && 'result' in v ? (v as { result: unknown }).result : v ?? ''

      // cell.formulaType is only implemented on ExcelJS's FormulaValue class —
      // for every other value type (Number/String/Date/…) it comes back
      // `undefined`, NOT `FormulaType.None` (0). Both are falsy, so `!` covers
      // "this cell has no formula at all" correctly; comparing only against
      // `=== ExcelJS.FormulaType.None` missed every non-formula cell (bug
      // found in review — every plain-value cell was misread as an
      // unresolved shared formula).
      if (!cell.formulaType) {
        formulaCols[colNum - 1] = null
      } else {
        const resolved = cell.formula
        formulaCols[colNum - 1] = typeof resolved === 'string' && resolved !== '' ? resolved : UNRESOLVED_FORMULA
      }
    })
    raw.push(cols)
    rawFormulas.push(formulaCols)
    rowNumbers.push(row.number)
  })

  if (raw.length < 2) {
    return ignoredManufacturingCandidateSheets
      ? withParseMeta([], { ...EMPTY_PARSE_META, ignoredCandidateSheets: ignoredManufacturingCandidateSheets })
      : withParseMeta([], EMPTY_PARSE_META)
  }

  // Auto-detect header row (Template = row 0, BMW Real-QAF DE/EN = row 10) // allow-customer-string
  const headerIdx = await findHeaderRow(raw)
  if (headerIdx === null) {
    throw new Error(
      'Keine passenden Spalten gefunden. Bitte das mitgelieferte Template verwenden — Zeile 1 muss die Spaltenüberschriften enthalten, ODER eine BMW QAF V8 Datei (DE „Fertigungskosten" oder EN „Manufacturing/Manufactering costs"-Sheet).', // allow-customer-string
    )
  }

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

  // Map each column index to its QAFRow key via the canonical MANUFACTURING
  // registry (KAR-893/P1.2 — matchHeaderColumn, see module header for the
  // exact/normalized confidence tiers). Columns whose header is blank are
  // silently skipped (not "unmapped" in the reporting sense — there was
  // nothing there to map); every other non-matching column is recorded in
  // unmappedHeaders for the degradation report below. loadManufacturingRegistry()
  // was already resolved by findHeaderRow() above — this call just awaits
  // the cached promise (no re-import), see module header.
  const registryCtx = await loadManufacturingRegistry()
  const colMap = new Map<number, QAFFieldKey>()
  const colConfidence = new Map<number, number>()
  const unmappedHeaders: string[] = []
  for (let i = 0; i < headerRow.length; i++) {
    const cell = headerRow[i]
    if (cell === '') continue
    const match = matchHeaderColumnSync(cell, registryCtx)
    if (match) {
      colMap.set(i, match.key)
      colConfidence.set(i, match.confidence)
    } else {
      unmappedHeaders.push(cell)
    }
  }

  // Degradation gate (KAR-893/P1.2, task instruction): below this minimum
  // the parse cannot proceed at all — see CORE_MANUFACTURING_FIELD_KEYS doc
  // comment for why these 3 specifically. Everything else is best-effort.
  const mappedKeys = new Set(colMap.values())
  const missingCore = CORE_MANUFACTURING_FIELD_KEYS.filter((k) => !mappedKeys.has(k))
  if (missingCore.length > 0) {
    const missingLabels = missingCore.map((k) => CORE_FIELD_LABELS_DE[k] ?? k).join(', ')
    throw new Error(
      `Fertigungskosten-Header unvollständig — folgende Kernfelder fehlen: ${missingLabels}. Datei kann nicht geparst werden.`, // allow-customer-string
    )
  }

  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: QAFRow[] = []
  const { buildFormulaProvenance, unresolvedFormulaProvenance } = await loadFormulaProvenanceFns()

  for (let i = headerIdx + 1; i < raw.length; i++) {
    const row = (raw[i] as unknown[]) ?? []
    // Stop at completely empty row (BMW QAF has "Summen" footer after blanks) // allow-customer-string
    if (row.every((c) => c === '' || c === null || c === undefined)) break

    const r: Partial<QAFRow> = {
      positionsnummer: '',
      teilebenennung: '',
      prozessbezeichnung: '',
      bezeichnungAnlage: '',
      standort: '',
      beschaffungswaehrung: '',
      angebotswaehrung: '',
      zykluszeit: null,
      teileProZyklus: null,
      anzahlMA: null,
      lohnkosten: null,
      lohnzuschlagssaetze: null,
      mss: null,
      ruestkosten: null,
      fek: null,
      rfgk: null,
      fk: null,
      wechselkurs: null,
      anzahlProAngebotsteil: null,
      fkAW: null,
      ausschuss: null,
      ausschusskosten: null,
    }

    // Per-field provenance (KAR-886): cell address ("Sheet!A1") + the same
    // normalized value the row itself carries, keyed by field — only for
    // columns actually located in the header (colMap), not the full 21-key
    // default-filled shape above.
    const sourceCells: Partial<Record<QAFFieldKey, string>> = {}
    const normalized: Partial<Record<QAFFieldKey, string | number | null>> = {}
    // rawText (KAR-891, P0.6): only set for numeric fields whose cell did NOT
    // parse as a number but also wasn't blank — the candidate case for an
    // explicit "n.a."/"entfällt" marker. Genuinely empty cells and text
    // fields never get an entry (see normalizer.isNotApplicableValue, which
    // treats blank as "not n.a." too).
    const rawText: Partial<Record<QAFFieldKey, string>> = {}
    // Per-field formula provenance (KAR-900/P2.1) — only for numeric fields
    // whose cell actually carried a formula (rawFormulas), same "no noise for
    // value-only cells" discipline as rawText above.
    const formulas: Partial<Record<QAFFieldKey, FormulaProvenance>> = {}
    const sheetRowNumber = rowNumbers[i]
    const formulaRow = rawFormulas[i]

    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)
      }
      const formulaRaw = formulaRow?.[colIdx]
      if (!TEXT_FIELDS.has(key) && formulaRaw) {
        formulas[key] = formulaRaw === UNRESOLVED_FORMULA ? unresolvedFormulaProvenance() : buildFormulaProvenance(formulaRaw)
      }
    }

    if (r.prozessbezeichnung || r.teilebenennung) {
      r.sourceCells = sourceCells
      r.normalized = normalized
      if (Object.keys(rawText).length > 0) r.rawText = rawText
      if (Object.keys(formulas).length > 0) r.formulas = formulas
      rows.push(r as QAFRow)
    }
  }

  return withParseMeta(rows, {
    parseConfidence,
    unmappedHeaders,
    mappedFieldCount,
    ...(ignoredManufacturingCandidateSheets ? { ignoredCandidateSheets: ignoredManufacturingCandidateSheets } : {}),
  })
}