// Multi-QAF type detection + safe degradation (KAR-926 / Multi-QAF-Programm
// P0.1, Epic KAR-925).
//
// Problem this closes (20-capability-matrix-ist.md, Ist-Capability-Probe
// against 4 real Multi-QAF files, 12.07.2026): Kadi-v2's existing engine has
// NO concept of "Multi-QAF"/"Variante" at all — every parser assumes exactly
// one value column per metric (locateAwColumn, summary-metrics.ts:318) and
// exactly one candidate sheet per module (.find(), not .filter()). Fed a
// real Multi-QAF workbook (one file, N product variants side by side), the
// commercial-delta layer does not fail loudly — it silently reads a plausible
// -looking but WRONG cell (a location string, a piece count, an unresolved
// formula reference) with a reported confidence of 0.6, and the MATERIAL
// parser returns 0 rows from a 7000+-row sheet (coreFieldsFound:false) on 3
// of 4 real sample files. That is the single worst failure class Master-
// Prompt §7 names: "parsed but semantically incorrect" wrong numbers with no
// visible error. This module is the FIRST guard against it — detection only
// (P0.1); actually parsing Multi-QAF variant containers is P1+ (backlog
// 30-backlog-phasenplan.md), explicitly out of scope here.
//
// Design discipline (Master-Prompt §7, task instruction): NEVER classify from
// the file NAME. Every signal below reads workbook STRUCTURE — cell text,
// formula text, populated-cell counts in a bounded region — never
// `upload.name`. Four independently-weighted signals, each individually
// gated at strong/weak/none, combined into one of four classifications:
//   confirmed_multi_qaf — S1 hit, OR >=2 strong signals.
//   probable_multi_qaf  — exactly 1 strong signal, EXCEPT S3 alone (S3 has
//     only negative calibration against the real corpus, never proven as a
//     positive signal on its own — see F3 fix note at the classifier below;
//     S3 strong still counts toward confirmed_multi_qaf IN COMBINATION with
//     another strong signal).
//   ambiguous           — no strong signal but >=1 weak signal, OR S3 is the
//     ONLY strong signal (demoted, not promoted to probable — see above).
//   standard_qaf        — no signal at all (the overwhelming common case).
// Conservative by design: "im Zweifel standard_qaf NICHT antasten" — a false
// interception of a genuine standard QAF is the worst possible outcome of
// this PR (worse than under-detecting a real Multi-QAF file, which just
// falls through to today's already-known-unsafe behaviour, not a NEW
// regression). Every threshold below is deliberately calibrated against the
// real 4-Multi-QAF + real-standard-QAF-corpus regression fixture (see
// __tests__/qaf-type-detector.real-files.test.ts, env-gated) — not guessed.
//
// The single most important false-positive guard (task instruction, verified
// against summary-metrics.ts): a normal standard QAF has EXACTLY ONE
// quotation-currency ("AW") value column, selected out of a small M..R
// (columns 12-17, HEADER_ROW=9 0-based / row 10) candidate band by
// locateAwColumn — plus at most 3 populated header cells in that SAME row/
// column region (the AW1/AW2/AW3 currency-code header labels a clean
// template prints regardless of which one is actually selected). That is
// structurally identical in SHAPE to this module's own "populated cells in a
// row" signal (S2/S4) — so this module's variant-column-band scan
// deliberately starts at column S (index 18), one column PAST
// summary-metrics.ts's AW_SCAN_TO (index 17), and scans a DIFFERENT set of
// rows (1..HEADER_SCAN_ROWS, not just row 10) — a standard QAF's AW1-3
// header band can never itself trigger S2/S4 because it never enters the
// scanned column range at all. See real-file regression test for the actual
// proof (0 false positives across the real standard-QAF corpus).
//
// Evidence sources (20-capability-matrix-ist.md + 10-analyse-*.md, all
// server-only, real Multi-QAF files under
// /root/aria/work/qaf-compare-kar824/input/ — never committed; the 4 real
// files are referred to below only as File 1-4, never by name — see
// __tests__/qaf-type-detector.real-files.test.ts's own confidentiality-
// discipline note for why):
//   S1 (Zusammenfassung!B4 or B5, "M-QAF Version 1.0"/"M-QAF Version 2.0"):
//     confirmed on File 1 (B5, "M-QAF Version 2.0"), File 2 (B5, "M-QAF
//     Version 1.0"), File 3 (B4, "M-QAF Version 1.0 by tkS") — 3 of 4 files.
//     Cell position genuinely varies (B4 vs B5) between files, so this
//     module scans a bounded header region (rows 1..HEADER_SCAN_ROWS, all
//     columns) rather than a single hardcoded cell — same "cell-bound, not a
//     single fixed address" discipline foreign-form-detection.ts already
//     established for its own header-title signatures.
//   S2/S4 (variant-column-band population / multi-row header block): File 1
//     Zusammenfassung Row 2 carries a numbered slot index 1..25 (with gaps)
//     across columns Q-Z/AF-AT — 25 populated cells in one row. File 4 has
//     NO M-QAF marker at all but its Zusammenfassung header block spans rows
//     3-12 (vehicle/drive/ratio/FIT-rate/LL-RL/volume.../variant-code), each
//     row populated across 26 columns (Q-AP) — the same "many populated
//     cells in a row" shape as File 1's numeric index row, just with
//     text/formula content instead of a bare index number (this module
//     deliberately does not require the value to be numeric — see module
//     header "Design discipline" citing both examples together in the
//     original task brief). File 3's MATERIAL sheet header block is 9 rows
//     deep (4-12) — the canonical multi-row-header-block example this
//     module's S4 threshold is named after.
//   S3 (Mengenfaktor-Matrix / SUMPRODUCT-SUMIF-Spuren): File 1's MATERIAL
//     sheet sums each variant's BOM total via `R121=SUMPRODUCT($O$19:$O$120,
//     R19:R120)`, repeated per variant column (R, S, T, ... — 12+ distinct
//     columns). File 3's MATERIAL sheet: `N81=SUMPRODUCT($K$19:$K$80,
//     N19:N80)`, same per-variant-column repetition. File 4's material-
//     equivalent sheet uses `M19=SUMIF(N19:AO19,">0",$N$11:$AO$11)*L19` — a
//     SINGLE column (M) referencing a wide row-range, so S3 correctly stays
//     weak/none for File 4 (it reaches confirmed via S2+S4 instead, not S3 —
//     see 20-backlog-phasenplan.md P0.1's own "hat KEINEN M-QAF-Marker, muss
//     über S2/S3/S4 kommen" note for that file).
//
// Pure core (MultiQafDetectionInput, no ExcelJS) + a thin ExcelJS bridge at
// the bottom — same "pure detection logic / ExcelJS adapter at the edge"
// split as foreign-form-detection.ts / g60/parser.ts+bridge.ts.

import type { Worksheet } from 'exceljs'
import { resolveCell } from './workbook-adapter'
import { matchesModuleSheetName } from './module-sheet-names'
import type { PlausibilityIssue } from './plausibility'
import { AW_SCAN_TO } from './summary-metrics'

// ── Config (versioned via engine-config.ts) ─────────────────────────────────

/**
 * Multi-QAF detection thresholds — versioned via engine-config.ts's
 * `multiQafDetection` section (same "module-local DEFAULT constant,
 * EngineConfig references it" pattern as RULE_ENGINE_CONFIG/
 * RECONCILIATION_CONFIG/FORMULA_ENGINE_CONFIG). `enabled: true` since
 * 13.07.2026 — Kais' explicit go (TG 8475, Multi-QAF-Programm KAR-925) after
 * the P0–P3 pipeline (detection → container assembly → compare flow) went
 * live end-to-end. With enabled:false the detector does not run at ingest at
 * all; the 0-false-intercept guarantee for standard QAFs is pinned by the
 * CI calibration-boundary regression (qaf-type-detector.test.ts) plus the
 * env-gated real-corpus suite. Rollback = set this back to false (one-line
 * PR); persisted Multi-QAF containers/comparisons stay intact and readable.
 */
export interface MultiQafDetectionConfig {
  /** Master switch. false: the detector does not run at ingest at all — zero
   * behavioural or performance change on that path. true (default since
   * 13.07.2026): confirmed/probable ingests the file as a Multi-QAF container
   * (KAR-935 flow) with the pre-container rejection as fail-closed fallback;
   * ambiguous surfaces a non-blocking plausibility issue; standard_qaf
   * is a no-op either way. */
  enabled: boolean
  /** S2 — populated-cell count (within the variant column band) in a single
   * row at/above which that row counts as a strong signal on its own. */
  strongVariantColumnPopulation: number
  /** S2 — populated-cell count at/above which a row is a weak (not yet
   * strong) signal. */
  weakVariantColumnPopulation: number
  /** S4 — number of rows independently reaching
   * strongVariantColumnPopulation, AT/ABOVE which the header block itself
   * counts as a strong signal (task threshold: ">3 Header-Zeilen", i.e. >=4
   * — two real Multi-QAF files' 9-row and 10-row header blocks (see module
   * header evidence-sources note, Files 3/4) both clear this with wide
   * margin; a standard QAF's SUMMARY sheet has no such block at all — see
   * real-file regression). */
  strongHeaderBlockRowCount: number
  /** S4 — qualifying-row count at/above which the header block is a weak
   * signal. */
  weakHeaderBlockRowCount: number
  /** S3 — distinct MATERIAL-sheet columns carrying a SUMPRODUCT/SUMIF
   * formula within the scan region, AT/ABOVE which S3 is strong (task
   * threshold: ">2 parallele Spalten", i.e. >=3). */
  strongMaterialFormulaColumnCount: number
  /** S3 — distinct-column count at/above which S3 is weak. */
  weakMaterialFormulaColumnCount: number
}

// Calibrated against the real corpus (__tests__/qaf-type-detector.real-files
// .test.ts, env-gated) — NOT the task brief's initial "≥3 strong, 2 schwach"
// suggestion, which the real corpus disproved (see below). Real evidence
// (file identities intentionally omitted from this comment — see that test
// module's own confidentiality-discipline note; the manifest it reads from
// is server-only and never committed):
//   - variantBandRowPopulation: the real V9 "Brose family" standard-QAF // allow-customer-string
//     SUMMARY template (Autoliv, Creator_Basis, aktuell_Brose x2, // allow-customer-string
//     FT_links_aktuell_Brose — 5 of the real standard files) legitimately // allow-customer-string
//     populates UP TO 13 cells in a single row of the variant-column band —
//     a structural feature of that template's own SUMMARY layout, not a
//     variant signal. One real Multi-QAF file in the corpus (the one S2/S4
//     exist to catch, since it has no S1 marker) populates 20-24 cells
//     across 14 rows. 17/14 sit in the clean gap between these two real
//     clusters with margin on both sides.
//   - materialFormulaColumnCount: the same standard-QAF family legitimately
//     carries SUMPRODUCT/SUMIF formulas across up to 8 MATERIAL-sheet
//     columns (ordinary BOM aggregation, not a variant signal). None of the
//     4 real Multi-QAF files in the corpus actually need S3 to independently
//     confirm (3 of 4 already confirm via S1; the 4th's material-equivalent
//     sheet uses a different name entirely, which does not match the
//     MATERIAL sheet-name alias at all, so materialFormulaColumnCount is 0
//     for that file) — thresholds are set safely above the observed
//     standard ceiling (8) so S3 stays inert on every real standard file.
//     S3 strong is deliberately NEVER a sole trigger for probable/confirmed
//     (adversarial-review F3 fix, 12.07.2026 — see the classifier below):
//     unlike S1/S2/S4, S3 has only this negative calibration, never a real
//     corpus file that needed it alone to confirm, so a hypothetical wide
//     standard BOM clearing this threshold on its own must not intercept a
//     genuine standard QAF — S3 strong alone degrades to ambiguous, and only
//     counts toward confirmed in combination with another strong signal.
export const DEFAULT_MULTI_QAF_DETECTION_CONFIG: MultiQafDetectionConfig = {
  enabled: true,
  strongVariantColumnPopulation: 17,
  weakVariantColumnPopulation: 14,
  strongHeaderBlockRowCount: 4,
  weakHeaderBlockRowCount: 2,
  strongMaterialFormulaColumnCount: 12,
  weakMaterialFormulaColumnCount: 9,
}

// ── Pure input (assembled by the ExcelJS adapter at the bottom) ────────────

export interface MultiQafDetectionInput {
  /** Whether a SUMMARY/Zusammenfassung sheet was found at all — every other
   * field is derived from it and stays empty/0 when this is false. */
  hasSummarySheet: boolean
  /** Non-empty, trimmed string cell values from the SUMMARY sheet's header
   * region (rows 1..HEADER_SCAN_ROWS, every column) — S1 marker scan. */
  markerRegionCells: readonly string[]
  /** Per-row populated-cell count within the variant column band
   * (VARIANT_BAND_COL_FROM..VARIANT_BAND_COL_TO), one entry per scanned
   * SUMMARY row (index 0 = spreadsheet row 1, up to HEADER_SCAN_ROWS
   * entries) — shared basis for S2 (max single row) and S4 (row count). */
  variantBandRowPopulation: readonly number[]
  /** S3 — count of DISTINCT columns in a MATERIAL-like sheet (module-sheet-
   * names.ts 'MATERIAL' alias) carrying a SUMPRODUCT/SUMIF formula within
   * the bounded scan region (rows 1..MATERIAL_FORMULA_SCAN_ROWS). 0 when no
   * MATERIAL-like sheet exists. */
  materialFormulaColumnCount: number
}

// ── Signals ──────────────────────────────────────────────────────────────

export type MultiQafSignalId = 'versionMarker' | 'variantColumnBand' | 'materialFactorMatrix' | 'multiRowHeaderBlock'

export type MultiQafSignalStrength = 'strong' | 'weak' | 'none'

export interface MultiQafSignalFinding {
  id: MultiQafSignalId
  strength: MultiQafSignalStrength
  /** Plain-language (English, internal-diagnostic — NOT the user-facing
   * bilingual rejection/plausibility text, see multiQafRejectionMessage/
   * multiQafDetectionToPlausibilityIssue below) evidence summary. */
  evidence: string
}

export type MultiQafClassification = 'confirmed_multi_qaf' | 'probable_multi_qaf' | 'ambiguous' | 'standard_qaf'

export interface MultiQafDetectionResult {
  classification: MultiQafClassification
  /** Heuristic, deliberately coarse (same category of constant as
   * template-fingerprint.ts's knownCoverageThreshold/modifiedCoverageThreshold
   * or g60/structure-guard.ts's softConfidence) — NOT a measured/calibrated
   * probability. Documented here so a caller never mistakes it for one. */
  confidence: number
  signals: readonly MultiQafSignalFinding[]
  /** Raw matched M-QAF version marker text (S1), null when no marker cell
   * matched. */
  matchedVersionMarker: string | null
}

// Exported (KAR-934/P1.6) so multi-qaf/template-fingerprint.ts can extract
// the matched version DIGIT from an already-found `matchedVersionMarker`
// string without re-implementing this pattern (task instruction: "nutze die
// Signale/Konstanten aus qaf-type-detector.ts ... NICHT duplizieren") — no
// behavior change for the S1 scan itself, findVersionMarker below is
// untouched.
export const M_QAF_VERSION_MARKER_PATTERN = /M-QAF\s+Version\s+([12])(?:\.0)?/i

/** S1 — scans markerRegionCells for the literal "M-QAF Version 1.0"/"M-QAF
 * Version 2.0" marker (case-insensitive; the marker text itself is an
 * English abbreviation used verbatim regardless of the file's DE/EN content
 * language, same "locale-invariant literal" category as module-sheet-
 * names.ts's SBM-DEVICES-FWZ/LC-CN tab names). Returns the first matching
 * cell's raw text, or null. */
function findVersionMarker(cells: readonly string[]): string | null {
  for (const cell of cells) {
    if (M_QAF_VERSION_MARKER_PATTERN.test(cell)) return cell.trim()
  }
  return null
}

function evaluateS1(input: MultiQafDetectionInput): { finding: MultiQafSignalFinding; matchedVersionMarker: string | null } {
  const matched = findVersionMarker(input.markerRegionCells)
  return {
    matchedVersionMarker: matched,
    finding: {
      id: 'versionMarker',
      strength: matched ? 'strong' : 'none',
      evidence: matched
        ? `M-QAF version marker found: "${matched}"`
        : 'No M-QAF version marker found in the SUMMARY header region.',
    },
  }
}

function evaluateS2(input: MultiQafDetectionInput, config: MultiQafDetectionConfig): MultiQafSignalFinding {
  const maxPopulation = input.variantBandRowPopulation.reduce((max, n) => Math.max(max, n), 0)
  const strength: MultiQafSignalStrength =
    maxPopulation >= config.strongVariantColumnPopulation
      ? 'strong'
      : maxPopulation >= config.weakVariantColumnPopulation
        ? 'weak'
        : 'none'
  return {
    id: 'variantColumnBand',
    strength,
    evidence: `Widest single-row population in the variant column band: ${maxPopulation} cell(s).`,
  }
}

function evaluateS4(input: MultiQafDetectionInput, config: MultiQafDetectionConfig): MultiQafSignalFinding {
  const qualifyingRowCount = input.variantBandRowPopulation.filter(
    (n) => n >= config.strongVariantColumnPopulation,
  ).length
  const strength: MultiQafSignalStrength =
    qualifyingRowCount >= config.strongHeaderBlockRowCount
      ? 'strong'
      : qualifyingRowCount >= config.weakHeaderBlockRowCount
        ? 'weak'
        : 'none'
  return {
    id: 'multiRowHeaderBlock',
    strength,
    evidence: `${qualifyingRowCount} row(s) independently reach the variant-column-band population threshold.`,
  }
}

function evaluateS3(input: MultiQafDetectionInput, config: MultiQafDetectionConfig): MultiQafSignalFinding {
  const count = input.materialFormulaColumnCount
  const strength: MultiQafSignalStrength =
    count >= config.strongMaterialFormulaColumnCount
      ? 'strong'
      : count >= config.weakMaterialFormulaColumnCount
        ? 'weak'
        : 'none'
  return {
    id: 'materialFactorMatrix',
    strength,
    evidence: `${count} distinct MATERIAL-sheet column(s) carry a SUMPRODUCT/SUMIF formula in the scan region.`,
  }
}

// Heuristic confidence constants (KAR-926 adversarial-review F9 fix,
// 12.07.2026 — CLAUDE.md "no magic numbers, extract to named constants") —
// see MultiQafDetectionResult doc comment: deliberately coarse, not a
// calibrated model. No behavior change, same values as before, just named.
const CONFIDENCE_CONFIRMED_VIA_MARKER = 0.95 // S1 hit — the strongest evidence class
const CONFIDENCE_CONFIRMED_VIA_SIGNALS = 0.85 // confirmed via >=2 strong non-S1 signals
const CONFIDENCE_PROBABLE = 0.6
const CONFIDENCE_AMBIGUOUS = 0.35
const CONFIDENCE_STANDARD = 0.05

/** Heuristic confidence per classification — see MultiQafDetectionResult doc
 * comment. Deliberately coarse constants, not a calibrated model. */
function confidenceFor(classification: MultiQafClassification, s1Hit: boolean): number {
  switch (classification) {
    case 'confirmed_multi_qaf':
      return s1Hit ? CONFIDENCE_CONFIRMED_VIA_MARKER : CONFIDENCE_CONFIRMED_VIA_SIGNALS
    case 'probable_multi_qaf':
      return CONFIDENCE_PROBABLE
    case 'ambiguous':
      return CONFIDENCE_AMBIGUOUS
    case 'standard_qaf':
      return CONFIDENCE_STANDARD
  }
}

/**
 * Pure. Classifies a workbook as confirmed/probable/ambiguous/standard
 * Multi-QAF from four independently-weighted structural signals — see module
 * header for the full design rationale and per-signal evidence. Never reads
 * a file name. Deterministic: same input + same config always yields the
 * same result.
 */
export function detectMultiQaf(
  input: MultiQafDetectionInput,
  config: MultiQafDetectionConfig = DEFAULT_MULTI_QAF_DETECTION_CONFIG,
): MultiQafDetectionResult {
  const s1 = evaluateS1(input)
  const s2 = evaluateS2(input, config)
  const s3 = evaluateS3(input, config)
  const s4 = evaluateS4(input, config)
  const signals = [s1.finding, s2, s3, s4]

  const strongCount = signals.filter((s) => s.strength === 'strong').length
  const weakCount = signals.filter((s) => s.strength === 'weak').length

  // KAR-926 adversarial-review F3 fix (12.07.2026): S3 (materialFactorMatrix)
  // has ONLY negative calibration against the real standard-QAF corpus (see
  // DEFAULT_MULTI_QAF_DETECTION_CONFIG's calibration note — "None of the 4
  // real Multi-QAF files actually need S3 to independently confirm") — no
  // real Multi-QAF file in the corpus was ever observed reaching
  // confirmed/probable via S3 alone, so its strong threshold is unproven as a
  // POSITIVE signal. A legitimately wide standard BOM (many ordinary
  // SUMPRODUCT/SUMIF aggregation columns, no variant intent at all) could in
  // principle clear it on its own — S3 strong therefore NEVER by itself
  // reaches probable_multi_qaf; it only counts toward confirmed_multi_qaf IN
  // COMBINATION with another strong signal (S1/S2/S4), same as any other
  // strong pair. Isolated S3-strong degrades to, at most, ambiguous (still
  // surfaced — task instruction: "im Zweifel standard_qaf NICHT antasten",
  // not "silently drop a real structural signal").
  const isS3OnlyStrongSignal = s3.strength === 'strong' && strongCount === 1
  const nonS3StrongCount = signals.filter((s) => s.strength === 'strong' && s.id !== 'materialFactorMatrix').length

  let classification: MultiQafClassification
  if (s1.finding.strength === 'strong' || strongCount >= 2) {
    classification = 'confirmed_multi_qaf'
  } else if (nonS3StrongCount === 1) {
    // Exactly one strong signal, and it is NOT S3 (S1 already handled above).
    classification = 'probable_multi_qaf'
  } else if (isS3OnlyStrongSignal || weakCount >= 1) {
    classification = 'ambiguous'
  } else {
    classification = 'standard_qaf'
  }

  return {
    classification,
    confidence: confidenceFor(classification, s1.finding.strength === 'strong'),
    signals,
    matchedVersionMarker: s1.matchedVersionMarker,
  }
}

// ── Bilingual messages ───────────────────────────────────────────────────

const SIGNAL_LABEL_DE: Record<MultiQafSignalId, string> = {
  versionMarker: 'M-QAF-Versions-Marker',
  variantColumnBand: 'Varianten-Spaltenband',
  materialFactorMatrix: 'Mengenfaktor-Matrix',
  multiRowHeaderBlock: 'Mehrzeiliger Header-Block',
}
const SIGNAL_LABEL_EN: Record<MultiQafSignalId, string> = {
  versionMarker: 'M-QAF version marker',
  variantColumnBand: 'variant column band',
  materialFactorMatrix: 'material factor matrix',
  multiRowHeaderBlock: 'multi-row header block',
}
const STRENGTH_LABEL_DE: Record<MultiQafSignalStrength, string> = {
  strong: 'stark',
  weak: 'schwach',
  none: 'kein',
}

function signalSummaryDe(signals: readonly MultiQafSignalFinding[]): string {
  const hit = signals.filter((s) => s.strength !== 'none')
  if (hit.length === 0) return 'keine Signale.'
  return hit.map((s) => `${SIGNAL_LABEL_DE[s.id]} (${STRENGTH_LABEL_DE[s.strength]})`).join(', ') + '.'
}

function signalSummaryEn(signals: readonly MultiQafSignalFinding[]): string {
  const hit = signals.filter((s) => s.strength !== 'none')
  if (hit.length === 0) return 'no signals.'
  return hit.map((s) => `${SIGNAL_LABEL_EN[s.id]} (${s.strength})`).join(', ') + '.'
}

/**
 * Bilingual upload-rejection message (foreign-form-detection.ts's
 * toDetection pattern) — non-null ONLY for confirmed/probable, the two
 * classifications ingestQafUpload rejects when multiQafDetection.enabled is
 * true. Names the matched M-QAF version marker (when present) and every hit
 * signal, per task instruction ("nennt erkannte M-QAF-Version/Signale und
 * dass Multi-QAF-Vergleich noch nicht unterstützt wird").
 */
export function multiQafRejectionMessage(result: MultiQafDetectionResult): { reasonDe: string; reasonEn: string } | null {
  if (result.classification !== 'confirmed_multi_qaf' && result.classification !== 'probable_multi_qaf') return null
  const versionPart = result.matchedVersionMarker ? ` (Marker: „${result.matchedVersionMarker}")` : ''
  const versionPartEn = result.matchedVersionMarker ? ` (marker: "${result.matchedVersionMarker}")` : ''
  const certainty = result.classification === 'confirmed_multi_qaf' ? 'erkannt' : 'wahrscheinlich erkannt'
  const certaintyEn = result.classification === 'confirmed_multi_qaf' ? 'detected' : 'likely detected'
  return {
    reasonDe:
      `Diese Datei ist vermutlich ein Multi-QAF (mehrere Produktvarianten in einer Datei)${versionPart}, ${certainty} ` +
      `anhand: ${signalSummaryDe(result.signals)} Multi-QAF-Vergleich wird von Kadi-v2 aktuell noch nicht unterstützt ` +
      `— ein Upload würde stille Falschzahlen erzeugen, daher abgelehnt.`,
    reasonEn:
      `This file is likely a Multi-QAF (multiple product variants in one file)${versionPartEn}, ${certaintyEn} ` +
      `via: ${signalSummaryEn(result.signals)} Multi-QAF comparison is not yet supported by Kadi-v2 — uploading it ` +
      `would silently produce wrong numbers, so it was rejected.`,
  }
}

/**
 * PlausibilityIssue bridge (KAR-906 bilingual pattern, same shape as
 * template-fingerprint.ts's templateFingerprintToPlausibilityIssue/
 * workbook-safety.ts's workbookSafetyToPlausibilityIssues) — non-blocking
 * surfacing for the 'ambiguous' classification (task instruction: "NICHT
 * blockieren, aber Befund ... sichtbar machen ... nie still interpretieren").
 * Also covers probable/confirmed defensively (never actually reached via the
 * ingest wiring today, since those throw before a QafFileParsed/plausibility
 * array exists — kept for symmetry/testability, same as
 * templateFingerprintToPlausibilityIssue covering every non-'known' case).
 * Returns null for standard_qaf (never an issue) and for a `null` result
 * (detector did not run — multiQafDetection.enabled was false).
 */
export function multiQafDetectionToPlausibilityIssue(
  result: MultiQafDetectionResult | null,
  side: 'ALT' | 'NEU',
  fileLabel: string,
): PlausibilityIssue | null {
  if (!result || result.classification === 'standard_qaf') return null
  const versionPart = result.matchedVersionMarker ? ` (Marker: „${result.matchedVersionMarker}")` : ''
  const versionPartEn = result.matchedVersionMarker ? ` (marker: "${result.matchedVersionMarker}")` : ''
  return {
    type: 'multi_qaf_suspected',
    severity: 'pruefen',
    step: `${side} · ${fileLabel}`,
    explanation:
      `„${fileLabel}" zeigt Anzeichen einer Multi-QAF-Datei (mehrere Produktvarianten)${versionPart} — ` +
      `Einstufung: ${result.classification}. Signale: ${signalSummaryDe(result.signals)} Multi-QAF-Vergleich wird ` +
      `aktuell nicht unterstützt; Ergebniszahlen für diese Datei bitte manuell prüfen.`,
    explanationEn:
      `"${fileLabel}" shows signs of being a Multi-QAF file (multiple product variants)${versionPartEn} — ` +
      `classification: ${result.classification}. Signals: ${signalSummaryEn(result.signals)} Multi-QAF comparison is ` +
      `not currently supported; please manually review this file's result numbers.`,
  }
}

// ── Persistence shaping ──────────────────────────────────────────────────

/**
 * g60_meta JSONB fragment for the multiQafDetection facet (KAR-926
 * adversarial-review F8 fix, 12.07.2026, actions.ts's ingestQafUpload).
 * `multiQafDetection` is `null` only when the detector never ran for this
 * ingest (`multiQafDetection.enabled` was false — since KAR-925, 13.07.2026,
 * that is no longer the permanent default, see DEFAULT_MULTI_QAF_DETECTION_
 * CONFIG's doc above; it still happens per-comparison on a replace via
 * resolveReplaceMultiQafDetectionConfig, rehydrate.ts). Persisting an
 * explicit `multiQafDetection: null` key on every single ingest broke the
 * "byte-identical to pre-KAR-926" claim documented at that call site (the
 * default-OFF path DOES change the persisted JSONB, just not the observable
 * comparison behaviour) — this helper omits the key entirely in that case so
 * the additivity claim is actually true. rehydrateFile's `g60Meta?.
 * multiQafDetection` read already tolerates the key being absent (yields
 * `undefined`, same as any pre-KAR-926 historical file — see compare.ts's
 * QafFileParsed.multiQafDetection doc comment for the undefined/null/value
 * tri-state contract this preserves). Lives here (not actions.ts) because a
 * `'use server'` file may only export async functions — this is a plain pure
 * function, so it belongs in the module that owns MultiQafDetectionResult.
 *
 * KAR-925 adversarial-review F2 decision (13.07.2026): since the detector now
 * runs by default, a `standard_qaf` classification IS a real, non-null
 * result — this helper persists it like any other classification (it is
 * NOT specially omitted the way F8 above omits a genuine `null`). This is a
 * deliberate choice, not an oversight: `{classification: 'standard_qaf',
 * confidence, signals, matchedVersionMarker: null}` is a small, bounded,
 * single object (not a per-row cost) and is itself information — "the
 * detector ran and found nothing" is exactly what an operator debugging a
 * false negative near the S1-S4 thresholds needs to see per file, the same
 * diagnostic-value argument that already justifies persisting
 * `workbookSafety`/`templateFingerprint` unconditionally. Omitting it would
 * make that debugging impossible to distinguish from "detector never ran"
 * without cross-referencing the comparison's engine_version stamp.
 */
export function multiQafDetectionMetaFragment(
  multiQafDetection: MultiQafDetectionResult | null,
): { multiQafDetection: MultiQafDetectionResult } | Record<string, never> {
  return multiQafDetection === null ? {} : { multiQafDetection }
}

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

/** Rows scanned per sheet for both the S1 marker region and the S2/S4
 * variant-column-band — bounded, not a full-sheet read (same discipline as
 * foreign-form-detection.ts's HEADER_SCAN_ROWS). Comfortably covers every
 * documented real header block across the real Multi-QAF corpus (see module
 * header evidence-sources note). */
export const HEADER_SCAN_ROWS = 20

/** 0-based column bounds of the variant-column-band scan (S2/S4). Starts one
 * column PAST summary-metrics.ts's AW_SCAN_TO — DERIVED (KAR-926
 * adversarial-review F5 fix, 12.07.2026), not a same-value magic number kept
 * in sync only by a comment — see module header "Design discipline" for why
 * this placement structurally cannot collide with a standard QAF's AW1-3
 * currency-header band. No behavior change: AW_SCAN_TO is still 17 (column
 * R), so this still resolves to 18 (column S), exactly as before. */
export const VARIANT_BAND_COL_FROM = AW_SCAN_TO + 1 // column S
export const VARIANT_BAND_COL_TO = 80 // generous headroom past every real sample's last variant column in the corpus

/** Rows scanned in a MATERIAL-like sheet for SUMPRODUCT/SUMIF formula
 * traces (S3) — bounded (not the sheet's declared/bloated dimension, see
 * 20-capability-matrix-ist.md "ws.dimensions lügt IMMER"), but deep enough to
 * cover every documented real BOM-total row across the corpus (row 19, row
 * 81, row 121 — see module header evidence-sources note) plus headroom. */
export const MATERIAL_FORMULA_SCAN_ROWS = 200

const MATERIAL_FORMULA_PATTERN = /SUMPRODUCT|SUMIF/i

/** Count of DISTINCT columns carrying a SUMPRODUCT/SUMIF formula within the
 * bounded scan region of ONE sheet (S3, per-sheet). Extracted (KAR-926
 * adversarial-review F4 fix) so multiQafDetectionInputFromExcelJs can call it
 * once per MATERIAL-alias sheet and take the max across sheets. */
function materialFormulaColumnCountForSheet(ws: Worksheet): number {
  const rowCount = Math.min(MATERIAL_FORMULA_SCAN_ROWS, ws.rowCount ?? MATERIAL_FORMULA_SCAN_ROWS)
  const hitColumns = new Set<number>()
  for (let r = 1; r <= rowCount; r++) {
    ws.getRow(r).eachCell({ includeEmpty: false }, (cell, colNumber) => {
      if (!cell.formulaType) return
      const formula = cell.formula
      if (typeof formula === 'string' && MATERIAL_FORMULA_PATTERN.test(formula)) {
        hitColumns.add(colNumber)
      }
    })
  }
  return hitColumns.size
}

/** Build the pure MultiQafDetectionInput from an already-loaded ExcelJS
 * workbook — one bounded pass over the SUMMARY sheet (rows 1..
 * HEADER_SCAN_ROWS, every column) plus, if present, one bounded pass over a
 * MATERIAL-like sheet (rows 1..MATERIAL_FORMULA_SCAN_ROWS, formula cells
 * only). Never a full-sheet read. */
export function multiQafDetectionInputFromExcelJs(wb: { worksheets: Worksheet[] }): MultiQafDetectionInput {
  const summaryWs = wb.worksheets.find((w) => matchesModuleSheetName(w.name, 'SUMMARY'))
  const markerRegionCells: string[] = []
  const variantBandRowPopulation: number[] = []

  if (summaryWs) {
    const rowCount = Math.min(HEADER_SCAN_ROWS, summaryWs.rowCount ?? HEADER_SCAN_ROWS)
    for (let r = 1; r <= rowCount; r++) {
      let bandCount = 0
      summaryWs.getRow(r).eachCell({ includeEmpty: false }, (cell, colNumber) => {
        const v = resolveCell(cell)
        if (typeof v === 'string' && v.trim() !== '') markerRegionCells.push(v)
        const col0 = colNumber - 1
        if (col0 < VARIANT_BAND_COL_FROM || col0 > VARIANT_BAND_COL_TO) return
        const isPopulated = typeof v === 'number' ? true : typeof v === 'string' ? v.trim() !== '' : v !== null && v !== undefined
        if (isPopulated) bandCount++
      })
      variantBandRowPopulation.push(bandCount)
    }
  }

  // KAR-926 adversarial-review F4 fix (12.07.2026): iterate over EVERY sheet
  // matching the MATERIAL alias, not just the first (`.find()` was a
  // single-sheet assumption this module's own header explicitly names as one
  // of the two structural failure modes it exists to guard against —
  // "exactly one candidate sheet per module (.find(), not .filter())"). Takes
  // the MAXIMUM per-sheet count, not the sum: summing across sheets would
  // let two unrelated MATERIAL-like sheets with a handful of ordinary BOM
  // formulas each add up to a false S3 signal neither sheet earns on its
  // own — conservative against false positives, same design discipline as
  // every threshold in this module.
  const materialSheets = wb.worksheets.filter((w) => matchesModuleSheetName(w.name, 'MATERIAL'))
  let materialFormulaColumnCount = 0
  for (const materialWs of materialSheets) {
    materialFormulaColumnCount = Math.max(materialFormulaColumnCount, materialFormulaColumnCountForSheet(materialWs))
  }

  return {
    hasSummarySheet: !!summaryWs,
    markerRegionCells,
    variantBandRowPopulation,
    materialFormulaColumnCount,
  }
}