// QAF Zusammenfassung/Summary parser (KAR-799, spec A3 / B-Parser).
//
// Extracts identity + context fields from the summary sheet. Strategy (spec):
// known standard cell first, then a robust label-scan fallback (find the label
// text, take the first non-empty value to its right). Works across DE
// "Zusammenfassung" and EN "Summary" variants and shifted layouts.
//
// Net-new: lib/qaf-parser.ts parses only the Fertigungskosten sheet; no summary
// parser existed. Pure: operates on a 2-D grid (row-major, 0-based). The
// ExcelJS→grid adapter lives in the upload/parse pipeline; gridFromCells mirrors
// it for tests.
//
// Field names and label synonyms are customer-neutral (no customer token in
// code) — matching is done on the distinctive part of each label.

import { normalizeProcessName } from './normalizer'
import type { QafSummary, QafSummaryKey, SummaryField } from './types'

// ── A1 helpers ────────────────────────────────────────────────────────────────

function letterToCol(letters: string): number {
  let n = 0
  for (const ch of letters.toUpperCase()) n = n * 26 + (ch.charCodeAt(0) - 64)
  return n - 1
}

function colToLetter(col: number): string {
  let n = col + 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
}

export function parseA1(addr: string): { row: number; col: number } {
  const m = /^([A-Za-z]+)(\d+)$/.exec(addr)
  if (!m) throw new Error(`bad A1 address: ${addr}`)
  return { col: letterToCol(m[1]), row: Number(m[2]) - 1 }
}

export function a1(row: number, col: number): string {
  return `${colToLetter(col)}${row + 1}`
}

// ── Cell value coercion ───────────────────────────────────────────────────────

export function cellToString(v: unknown): string | null {
  if (v === null || v === undefined) return null
  // Ein ungültiges Date (`new Date(NaN)`) ist `instanceof Date`, aber
  // `toISOString()` wirft darauf `RangeError: Invalid time value`. Excel liefert
  // solche Werte für kaputte Datumszellen, und im Realkorpus gibt es sie —
  // gefunden über die env-gated Korpus-Tests, nachdem 2475 neue Realdateien
  // dazukamen. Ungeprüft reißt eine einzige solche Zelle den kompletten
  // Summary-Parse ab, statt nur dieses eine Feld leer zu lassen.
  if (v instanceof Date) return Number.isNaN(v.getTime()) ? null : v.toISOString()
  if (typeof v === 'object') {
    const o = v as Record<string, unknown>
    if ('text' in o) return cellToString(o.text)
    if ('result' in o) return cellToString(o.result)
    if (Array.isArray(o.richText)) return (o.richText as Array<{ text?: string }>).map((t) => t.text ?? '').join('')
    return null
  }
  const s = String(v).trim()
  return s === '' ? null : s
}

function isLabelCell(v: unknown): boolean {
  return typeof v === 'string' && v.trim().endsWith(':')
}

// ── Field definitions ─────────────────────────────────────────────────────────

interface FieldDef {
  /** Fixed A1 anchor, tried first. Omitted for fields with no verified fixed
   * cell (KAR-910: the 4 "Allgemeine Prämissen"/"Prämissen Lieferant" fields
   * below live further down the sheet than the 9-field identity header block
   * this module originally parsed — no Leitfaden/real-file cell coordinate
   * is documented for them, only the label text and page, see
   * canonical-fields.ts SUMMARY_PREMISES_FIELDS). Falls straight through to
   * labelScan when unset. */
  primary?: string
  /** Lowercased, umlaut-folded substrings; a label matches if it contains one. */
  labels: string[]
  /**
   * Review-Fix 5 (KAR-973 adversarial review, MAJOR — labelScan cross-field
   * collision): when true, labelScan additionally rejects a scanned
   * candidate that is implausible as THIS field's actual value — see
   * `isPlausibleScannedValue` below for the exact two checks. Real-corpus
   * evidence: `plannedCapacity`/`lotSize` (both `dataType: 'number'`, no
   * `primary` anchor) silently mis-extracted a NEIGHBORING SUMMARY_METRIC
   * label's own un-colon'd text (e.g. "Enthaltene Zölle", summary-metrics.ts
   * `customsIncluded`) in 13-14/227 real dev-split files, because that
   * label's row happened to leave the intended value cell blank and the
   * neighboring label sat inside the shared `MAX_SCAN_RIGHT=6` window.
   * Deliberately OPT-IN per field, not a blanket behavior change to
   * `labelScan` itself: the 4 already-shipped KAR-910 fields
   * (peakVolumeYear/productionStartSop/deliverySite/shiftsPerWeek) are known
   * to have the SAME latent collision (documented, not fixed here — a
   * silent behavior change to 4 already-tested, already-measured fields is
   * out of this fix's scope; see corpus-evidence.md for the follow-up this
   * finding raises) — flipping this on for them stays a deliberate,
   * separately-reviewed follow-up (own KAR), not bundled silently into this
   * fix. Only the 2 NEW QVS-P4 fields opt in. */
  strictValueCheck?: true
}

const FIELD_DEFS: Record<QafSummaryKey, FieldDef> = {
  partNumber: { primary: 'I8', labels: ['sachnummer', 'part number'] },
  quotationDate: { primary: 'C8', labels: ['angebotsdatum', 'quotation date'] },
  supplier: { primary: 'I5', labels: ['anbieter/lieferant', 'vendor/supplier'] },
  partName: { primary: 'M5', labels: ['teilebenennung', 'parts designation'] },
  variant: { primary: 'M8', labels: ['variante', 'variant'] },
  project: { primary: 'I7', labels: ['projekt', 'project'] },
  requestVersion: { primary: 'M7', labels: ['anfragenummer', 'request number'] },
  changeIndex: { primary: 'M6', labels: ['aenderungsindex', 'change index'] },
  supplierNo: { primary: 'I6', labels: ['lieferanten-nr', 'supplier no'] },
  // KAR-910 — labels verbatim from Leitfaden teil1.md [11] (DE) resp. the
  // real-file-sourced Fehlerreport EN column (03-fehlerreport-analyse.md §2
  // rows #10/#12/#14/#16, "Feld EN") — see canonical-fields.ts
  // sum_peak_volume_year/sum_production_start_sop/sum_delivery_site/
  // sum_shifts_per_week for the full evidence citations. No fixed A1 anchor
  // known (label-scan only, see FieldDef.primary doc above).
  peakVolumeYear: { labels: ['peakvolumen', 'peak volume'] },
  // 'produdktionsstart' is the documented BMW source-data typo (Fehlerreport // allow-customer-string
  // §3.3, also the DE label used verbatim in the real fehlerreport row #12).
  productionStartSop: { labels: ['produktionsstart', 'produdktionsstart', 'production start'] },
  deliverySite: { labels: ['auslieferungsstandort', 'delivery location', 'delivery site'] },
  shiftsPerWeek: { labels: ['schichten', 'shifts'] },
  // QVS-P4 (KAR-973) — labels verbatim from Leitfaden teil1.md [11] (DE) resp.
  // corpus-evidence.md's real-file EN wording (both DE/EN variants scanned at
  // 221/227 resp. 220/227 dev-split presence). 'plankapazitaet'/
  // 'fertigungslosgroesse' are the umlaut-folded forms normalizeProcessName
  // produces (ä->ae, ö->oe, ü->ue, ß->ss — see normalizer.ts), matching this
  // module's own substring-containment convention (labelScan's doc above).
  // No fixed A1 anchor known — label-scan only, same as the 4 KAR-910 fields.
  // Review-Fix 5: strictValueCheck opt-in (see FieldDef doc) — both new P4
  // fields are dataType 'number' with no fixed primary anchor, exactly the
  // shape the labelScan cross-field collision hits.
  plannedCapacity: { labels: ['plankapazitaet', 'planned capacity'], strictValueCheck: true },
  // "Manufacturing lot size" is the real-file EN wording corpus-evidence.md's
  // scan matched (distinct from the Leitfaden's own EN working-translation
  // "Production lot size" already used as labelEn in canonical-fields.ts) —
  // both are scanned, same "Leitfaden label stays primary, real-file wording
  // is an additional match" precedent as sum_peak_volume_year/sum_delivery_site.
  lotSize: { labels: ['fertigungslosgroesse', 'production lot size', 'manufacturing lot size'], strictValueCheck: true },
}

const MAX_SCAN_RIGHT = 6

function gridGet(grid: unknown[][], row: number, col: number): unknown {
  return grid[row]?.[col]
}

/**
 * Review-Fix 5 plausibility guard (see `FieldDef.strictValueCheck` doc) —
 * rejects a labelScan candidate that is itself another field's un-colon'd
 * label text having leaked through the scan window, via two independent
 * signals (either one disqualifies):
 *   (a) the candidate contains NO digit at all — both real-corpus collisions
 *       found (summary-metrics.ts's "Enthaltene Zölle" /
 *       "Enthaltene Verpackung und Transport" SUMMARY_METRIC labels) are pure
 *       label prose with zero digits, while a genuine plannedCapacity/
 *       lotSize value is always a number;
 *   (b) the candidate's normalized text contains another FIELD_DEFS entry's
 *       OWN label substring (defense-in-depth for a same-module collision,
 *       even one that happened to carry a digit).
 * Never applied to a field whose FieldDef doesn't opt in (strictValueCheck) —
 * see that field's doc for why the 4 KAR-910 fields are excluded.
 */
function isPlausibleScannedValue(value: string, ownKey: QafSummaryKey): boolean {
  if (!/\d/.test(value)) return false
  const normalized = normalizeProcessName(value)
  for (const key of Object.keys(FIELD_DEFS) as QafSummaryKey[]) {
    if (key === ownKey) continue
    if (FIELD_DEFS[key].labels.some((l) => normalized.includes(l))) return false
  }
  return true
}

/**
 * Find a value via label scan: locate a matching label cell, return the first
 * non-empty, non-label, PLAUSIBLE (see `isPlausibleScannedValue`, opt-in via
 * `def.strictValueCheck`) cell to its right in the same row.
 *
 * KAR-917 collision guard: labels match via SUBSTRING containment
 * (`norm.includes(l)`), not exact equality — deliberately, so shortened
 * real-file label variants still match (see FIELD_DEFS comments). That
 * substring match can ALSO fire on an unrelated free-text cell that happens
 * to contain the label text (e.g. a "Bemerkungsfeld"/remarks cell whose
 * content mentions "Peakvolumen" in prose). Before this fix, the scan
 * returned the value next to the FIRST matching cell it found, silently
 * pulling a neighboring free-text value if that first match happened to be
 * the wrong (non-label) cell. Mirrors summary-metrics.ts's
 * locateRowUnanchored collision guard (count hits first, only accept an
 * UNAMBIGUOUS single match) — labelScan counts every matching cell across
 * the whole grid; more than one match returns null/null (nicht auffindbar),
 * exactly like locateRowUnanchored's 'labelCollision' outcome, rather than
 * guessing which one is the real label.
 */
function labelScan(grid: unknown[][], def: FieldDef, ownKey: QafSummaryKey): SummaryField {
  const hits: Array<{ r: number; c: number }> = []
  for (let r = 0; r < grid.length; r++) {
    const rowArr = grid[r] ?? []
    for (let c = 0; c < rowArr.length; c++) {
      const norm = normalizeProcessName(rowArr[c])
      if (norm === '' || !def.labels.some((l) => norm.includes(l))) continue
      hits.push({ r, c })
    }
  }
  if (hits.length !== 1) return { value: null, cell: null }

  const { r, c } = hits[0]
  const rowArr = grid[r] ?? []
  for (let k = c + 1; k <= c + MAX_SCAN_RIGHT && k < rowArr.length; k++) {
    const cell = rowArr[k]
    if (isLabelCell(cell)) break
    const val = cellToString(cell)
    if (val === null) continue
    if (def.strictValueCheck && !isPlausibleScannedValue(val, ownKey)) continue
    return { value: val, cell: a1(r, k) }
  }
  return { value: null, cell: null }
}

function extractField(grid: unknown[][], key: QafSummaryKey, def: FieldDef): SummaryField {
  if (def.primary !== undefined) {
    const { row, col } = parseA1(def.primary)
    const primaryVal = cellToString(gridGet(grid, row, col))
    if (primaryVal !== null) return { value: primaryVal, cell: def.primary }
  }
  return labelScan(grid, def, key)
}

/**
 * Parse the summary sheet grid into a QafSummary with per-field source cells.
 * Missing values are null (e.g. EN files often omit the part number — the
 * caller then falls back to the filename per spec B6).
 */
export function parseSummary(grid: unknown[][]): QafSummary {
  const out = {} as QafSummary
  for (const key of Object.keys(FIELD_DEFS) as QafSummaryKey[]) {
    out[key] = extractField(grid, key, FIELD_DEFS[key])
  }
  return out
}

/** Test/adapter helper: build a row-major grid from an { A1: value } map. */
export function gridFromCells(cells: Record<string, unknown>): unknown[][] {
  let maxRow = 0
  let maxCol = 0
  const parsed: Array<{ row: number; col: number; value: unknown }> = []
  for (const [addr, value] of Object.entries(cells)) {
    const { row, col } = parseA1(addr)
    parsed.push({ row, col, value })
    if (row > maxRow) maxRow = row
    if (col > maxCol) maxCol = col
  }
  const grid: unknown[][] = Array.from({ length: maxRow + 1 }, () => new Array(maxCol + 1).fill(null))
  for (const { row, col, value } of parsed) grid[row][col] = value
  return grid
}