// ExcelJS → engine adapter (KAR-799, spec B-Parser / A5.2).
//
// Bridges a real .xlsx workbook to the pure summary parser: resolves an ExcelJS
// worksheet to a 0-based row-major grid (formula results, hyperlink text), and
// loads the Zusammenfassung/Summary sheet from a buffer. Server-only — ExcelJS
// is imported dynamically (kept out of client bundles), matching qaf-parser.ts.
//
// Net-new. The Fertigungskosten step parser already exists (qaf-parser.ts); this
// adds the missing summary-sheet bridge. Read-only — never mutates the file.

import type { Workbook, Worksheet, Cell } from 'exceljs'
import { isLegacyBiffBuffer, loadLegacyWorkbook } from './legacy-workbook-shim'
import { parseQAFTemplate, type QAFRow } from '@/lib/qaf-parser'
import { parseSummary } from './summary-parser'
import { parseSummaryMetrics, type SummaryMetricsParse } from './summary-metrics'
import type { QafSummary, FacetDegradation } from './types'
import { SHARED_FORMULA_UNRESOLVED } from './formula-engine'
import { matchesModuleSheetName } from './module-sheet-names'
import { summaryFieldCandidates } from './capability/summary-field-candidates'
import { manufacturingStepFieldCandidates } from './capability/manufacturing-field-candidates'
import { premiseMasterRateFieldCandidates } from './capability/premise-field-candidates'
import { isPremiseSheetName } from './capability/sheet-resolver'
import type { FieldCandidate } from './capability/types'

/**
 * Resolve one ExcelJS cell to its plain read value (formula result, hyperlink/
 * rich-text display text, or the literal). Takes the `Cell` itself (not
 * `cell.value`) so formula cells can be special-cased — see below.
 *
 * KAR-928 fix (part 1): a formula cell whose cached RESULT is falsy (`0`,
 * `""`, `false`) previously came back as the raw `{formula, ref, shareType,
 * ...}` object instead of that falsy value. Root cause verified in
 * node_modules/exceljs/lib/doc/cell.js: `cell.value` for a formula cell is
 * `FormulaValue._copyModel(this.model)`, which only copies a key when
 * `if (value)` is truthy — `result: 0` or `result: ""` is silently dropped
 * from the returned object, so the old `'result' in o` check here never
 * fired and fell through to `return v` (the raw formula object). Every
 * downstream reader (cellToString, parseLocaleNumber) then either produced
 * `null` for a metric that is legitimately `0`, or — for parseLocaleNumber,
 * which stringifies non-null/non-number input — the nonsensical
 * `"[object Object]"` before also collapsing to `null`. Never a genuine
 * "formula reference text as value" leak downstream, but real, silent data
 * loss for any falsy formula result.
 *
 * Fix, same "read the Cell-level getter, not cell.value" pattern
 * formula-engine.ts already established for `cell.formula` vs
 * `cell.value.formula` (KAR-900 module header, "Shared-Formula-Slaves"):
 * `cell.result` is `FormulaValue.model.result` read directly, NOT through
 * `_copyModel`'s truthy filter, so it returns the real cached value
 * (including `0`/`""`/`false`) whenever ExcelJS resolved one — and `undefined`
 * (never the raw formula object) when it did not, which every downstream
 * reader already treats identically to `null`. No Excel evaluation happens
 * here — only the already-cached value is read, exactly as before.
 *
 * KAR-928 fix (part 2, adversarial-review follow-up): a cell that is itself
 * the non-anchor member of a merged range has `cell.formulaType ===
 * undefined` even when the merge ANCHOR is a formula — `MergeValue`
 * (node_modules/exceljs/lib/doc/cell.js) defines no `formulaType` getter at
 * all, so part 1's fix never fired for these cells and they fell back to the
 * `cell.value` path below, which for a merge cell is `MergeValue.value`
 * delegating to `anchor.value` — i.e. exactly the same truthy-filtered
 * `_copyModel` snapshot part 1 fixes for direct formula cells. Falsy anchor
 * results (`0`/`""`/`false`) were therefore STILL lost for every
 * merge-duplicated formula cell — a real, non-hypothetical case: BMW QAF // allow-customer-string
 * templates merge-duplicate formula-driven header cells across many columns
 * (found in real-corpus regression testing, KAR-928).
 *
 * Fix: `cell.master` (Cell.prototype getter, same file) is ExcelJS's own
 * public API for this — it returns `this` for a non-merged cell (a no-op)
 * and the merge ANCHOR Cell for any member of a merged range, anchor
 * included (`isMergedTo`/`merge()` machinery). Recursing into
 * `resolveCell(cell.master)` before the formulaType check routes a merged
 * formula cell through the SAME `cell.formulaType`/`cell.result` branch a
 * direct formula cell takes, fixing the falsy-result loss for both the
 * anchor and every non-anchor member identically. A non-formula anchor
 * (plain merged literal/text/hyperlink) is unaffected: the recursive call
 * lands right back on the literal/text/richText fallback below and produces
 * the identical value `cell.value`'s old direct delegation already returned
 * — proven in the regression tests (workbook-adapter.test.ts, merge-range
 * `describe` block).
 */
export function resolveCell(cell: Cell): unknown {
  if (cell.master !== cell) return resolveCell(cell.master)
  if (cell.formulaType) return cell.result
  const v = cell.value
  if (v !== null && typeof v === 'object') {
    const o = v as unknown as Record<string, unknown>
    if ('result' in o) return o.result
    if ('text' in o) return o.text
    if (Array.isArray(o.richText)) return (o.richText as Array<{ text?: string }>).map((t) => t.text ?? '').join('')
  }
  return v
}

/** Load a workbook buffer ONCE — callers fan out (G60 detection, summary parse).
 *
 * Verzweigt am CONTAINER, nicht an der Dateiendung: `.xls` trägt im Realkorpus
 * echtes BIFF, aber auch `.xlsx` kann ein OLE2-Container sein (umbenannt oder
 * verschlüsselt), und umgekehrt liegt hinter mancher `.xls` ein reguläres OOXML.
 * Die Endung ist eine Behauptung, die Signatur ein Befund. */
export async function loadExcelWorkbook(buffer: Buffer | ArrayBuffer): Promise<Workbook> {
  if (isLegacyBiffBuffer(buffer)) return loadLegacyWorkbook(buffer)
  const ExcelJS = (await import('exceljs')).default
  const wb = new ExcelJS.Workbook()
  await wb.xlsx.load(buffer as unknown as ArrayBuffer)
  return wb
}

/** Sheet-level structural summary — feeds the template fingerprint (KAR-895/
 * P1.4, template-fingerprint.ts). Uses ExcelJS's own dimension counters
 * (rowCount/columnCount), no additional read pass over the workbook. */
export interface WorkbookSheetSummary {
  name: string
  rowCount: number
  colCount: number
}

/** Summarize every worksheet in an already-loaded workbook — one entry per
 * sheet regardless of whether Kadi-v2 recognizes/parses that sheet today
 * (unrecognized sheets are part of the structural fingerprint too). */
export function summarizeWorkbookSheets(wb: { worksheets: Worksheet[] }): WorkbookSheetSummary[] {
  return wb.worksheets.map((ws) => ({
    name: ws.name,
    rowCount: ws.rowCount ?? 0,
    colCount: ws.columnCount ?? 0,
  }))
}

// ── Semantic active-range scan (KAR-933/P1.5, shared helper) ───────────────
//
// `ws.dimensions`/`ws.rowCount`/`ws.columnCount` lie routinely across the
// real Multi-QAF corpus — 30-backlog-phasenplan.md "Querschnitts-Wahrheiten":
// "ws.dimensions lügt IMMER (45×–83× Bloat)". Concretely: one real corpus
// file's BOM detail sheet declares 7124 rows, 159 are real; another
// declares a 7050-cell sheet, 85 cells are real (30-backlog-phasenplan.md,
// item 4).
// qaf-type-detector.ts already defends against this per-signal with its own
// fixed scan caps (HEADER_SCAN_ROWS/MATERIAL_FORMULA_SCAN_ROWS/
// VARIANT_BAND_COL_TO) rather than trusting the declared dimension as a loop
// bound. This helper generalizes that same discipline (bounded scan,
// Math.min against the declared counter only as an UPPER bound, never as
// ground truth) into a single reusable "how far does the real content
// actually go" primitive, for callers (formula-lineage.ts) that need to know
// the real scan window rather than just a fixed guess.
/** Rows/columns scanned per sheet when computing a semantic active range.
 * Generous headroom past every documented real BOM/material-sheet row count
 * in the Multi-QAF corpus (row159/row121/row81/row19 — 10-analyse-*.md) and
 * reuses qaf-type-detector.ts's own VARIANT_BAND_COL_TO=80 headroom for
 * columns (not re-derived as a second, possibly-diverging constant). */
export const SEMANTIC_ACTIVE_RANGE_MAX_ROWS = 300
export const SEMANTIC_ACTIVE_RANGE_MAX_COLS = 80

export interface SemanticActiveRange {
  /** Last 1-based row carrying a non-empty value/formula within the bounded
   * scan window — 0 when the sheet has no populated cell in that window. */
  lastRow: number
  /** Last 1-based column carrying a non-empty value/formula within the
   * bounded scan window. */
  lastColumn: number
  /** true when content reached all the way to the scan window's own edge
   * (maxRows/maxColumns) — the real content may extend further than
   * reported; callers should treat lastRow/lastColumn as a lower bound in
   * that case, but must never silently widen the scan past the guard (that
   * defeats the guard's entire purpose). */
  truncated: boolean
}

/** Bounded semantic active-range scan — NEVER reads ws.dimensions/
 * ws.rowCount/ws.columnCount as ground truth for the real data edge, only as
 * an already-Math.min-capped UPPER bound so a genuinely small sheet is not
 * scanned past its own real end. Same `getRow(r).eachCell({includeEmpty:
 * false})` bounded-loop shape as qaf-type-detector.ts's own scans (kept as-is
 * there — different bounded-scan shape per signal, not superseded by this
 * shared helper). Pure read, no mutation. */
export function semanticActiveRange(
  ws: Worksheet,
  opts?: { maxRows?: number; maxColumns?: number },
): SemanticActiveRange {
  const maxRows = opts?.maxRows ?? SEMANTIC_ACTIVE_RANGE_MAX_ROWS
  const maxColumns = opts?.maxColumns ?? SEMANTIC_ACTIVE_RANGE_MAX_COLS
  const rowScanLimit = Math.min(maxRows, ws.rowCount || maxRows)
  let lastRow = 0
  let lastColumn = 0
  for (let r = 1; r <= rowScanLimit; r++) {
    ws.getRow(r).eachCell({ includeEmpty: false }, (cell, colNumber) => {
      if (colNumber > maxColumns) return
      if (r > lastRow) lastRow = r
      if (colNumber > lastColumn) lastColumn = colNumber
    })
  }
  const truncated = lastRow >= rowScanLimit || lastColumn >= maxColumns
  return { lastRow, lastColumn, truncated }
}

/** Convert a worksheet to a 0-based row-major grid (grid[row][col]). */
export function worksheetToGrid(ws: Worksheet): unknown[][] {
  const grid: unknown[][] = []
  ws.eachRow({ includeEmpty: true }, (row, rowNumber) => {
    const cols: unknown[] = []
    row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
      cols[colNumber - 1] = resolveCell(cell)
    })
    grid[rowNumber - 1] = cols
  })
  return grid
}

/**
 * Parallel formula grid (KAR-900/P2.1) — same 0-based row-major indexing as
 * worksheetToGrid, but each cell holds the RESOLVED Excel formula text
 * (`cell.formula`, NOT `cell.value.formula` — see below), or null when the
 * cell has no formula at all, or the SHARED_FORMULA_UNRESOLVED sentinel for
 * the rare case where ExcelJS could not translate a shared-formula slave.
 * Callers zip this against worksheetToGrid's value grid by the same
 * [row][col] index (see summary-metrics.ts formulaAt).
 *
 * 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 — every SLAVE cell in the same range has
 * model.formula === undefined, so the old cell.value-based check silently
 * dropped every slave's 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, so no further
 * row-relative normalization is needed downstream (see formula-engine.ts
 * module header "Shared-Formula-Slaves" for the full writeup).
 */
export function worksheetToFormulaGrid(ws: Worksheet): (string | null | typeof SHARED_FORMULA_UNRESOLVED)[][] {
  const grid: (string | null | typeof SHARED_FORMULA_UNRESOLVED)[][] = []
  ws.eachRow({ includeEmpty: true }, (row, rowNumber) => {
    const cols: (string | null | typeof SHARED_FORMULA_UNRESOLVED)[] = []
    row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
      // 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 the literal `0` missed every non-formula cell (bug found in
      // review — every plain-value cell was misread as an unresolved shared
      // formula).
      if (!cell.formulaType) {
        cols[colNumber - 1] = null
      } else {
        const resolved = cell.formula
        cols[colNumber - 1] = typeof resolved === 'string' && resolved !== '' ? resolved : SHARED_FORMULA_UNRESOLVED
      }
    })
    grid[rowNumber - 1] = cols
  })
  return grid
}

/** Load the summary sheet from an .xlsx buffer and parse it. Returns an all-null
 *  summary when there is no Zusammenfassung/Summary sheet. */
export async function parseSummaryFromWorkbook(buffer: Buffer | ArrayBuffer): Promise<QafSummary> {
  return (await parseSummarySheet(buffer)).summary
}

/**
 * Load the summary sheet once and parse both the identity fields and the
 * canonical money metrics (KAR-840). `summaryMetrics` is null when the workbook
 * has no Zusammenfassung/Summary sheet.
 *
 * `sheetNames` (PR #325 review fix #6, cleanup): every worksheet name from
 * THIS load, additive to the original two-field shape — free to compute
 * (`wb.worksheets` is already fully in memory here) but lets parseQafFile's
 * manufacturing-facet failure branch below identify the failed sheet WITHOUT
 * a second `loadExcelWorkbook(buffer)` full re-parse of the same buffer
 * purely to read worksheet names that were already available from this call.
 *
 * `summaryFieldCandidates` (KAR-960/P3, Hebel B — additive): the Zusammenfassung/
 * Summary-sheet master-rate label scan + already-parsed Summen wrapped into
 * FieldCandidate[] (internal/capability/summary-field-candidates.ts) —
 * `[]` when there is no SUMMARY-like sheet, exactly like summaryMetrics: null.
 */
export async function parseSummarySheet(
  buffer: Buffer | ArrayBuffer,
): Promise<{
  summary: QafSummary
  summaryMetrics: SummaryMetricsParse | null
  sheetNames: string[]
  summaryFieldCandidates: FieldCandidate<number>[]
}> {
  const wb = await loadExcelWorkbook(buffer)
  return { ...parseSummarySheetFromWorkbook(wb), sheetNames: wb.worksheets.map((w) => w.name) }
}

/** Same as parseSummarySheet but on an already-loaded workbook (single load). */
export function parseSummarySheetFromWorkbook(wb: {
  worksheets: Worksheet[]
}): { summary: QafSummary; summaryMetrics: SummaryMetricsParse | null; summaryFieldCandidates: FieldCandidate<number>[] } {
  // Sheet-name alias source centralized in module-sheet-names.ts (KAR-905/
  // P3.1) — was an inline /zusammenfassung|summary/i regex, behaviorally
  // identical (both DE "Zusammenfassung"/legacy and EN/V9 "SUMMARY" tab names).
  const ws = wb.worksheets.find((w) => matchesModuleSheetName(w.name, 'SUMMARY'))
  // KAR-962/P5 §21 P3-follow-up: Prämissenblatt/Assumptions-sheet master-rate
  // scan — a SEPARATE source for the same 4 fields summaryFieldCandidates
  // already scans the Zusammenfassung/Summary sheet for (capability-matrix.md:
  // Master-Sätze found there in only 14-22% of the corpus; a Prämissenblatt/
  // Assumptions-sheet tab is present in 76%+20% of the 227-file dev split —
  // see sheet-resolver.ts's isPremiseSheetName/EXTRA_ROLE_ALIASES comment for
  // the exact counts). Scanned independently of whether a SUMMARY sheet was
  // found at all — a Prämissenblatt can exist on its own. A workbook may
  // carry more than one premise-role sheet (e.g. the RMR-specific
  // "Prämissenblatt_Rohstoff" alongside the main one); every one is scanned,
  // candidates concatenated — field-candidates.ts's resolver already handles
  // multiple candidates for the same field (SINGLE/AGREEMENT/INCONSISTENT),
  // so no special-casing is needed here for "which premise sheet wins".
  const premiseSheets = wb.worksheets.filter((w) => isPremiseSheetName(w.name))
  const premiseCandidates = premiseSheets.flatMap((pws) => premiseMasterRateFieldCandidates(worksheetToGrid(pws), pws.name))

  if (!ws) return { summary: parseSummary([]), summaryMetrics: null, summaryFieldCandidates: premiseCandidates }
  const grid = worksheetToGrid(ws)
  // KAR-900/P2.1: formula grid threaded through alongside the value grid so
  // parseSummaryMetrics can attach per-metric formula provenance for the
  // "berechnet" summary rows (SUMME HERSTELLKOSTEN, GESAMTKOSTEN, …).
  const formulaGrid = worksheetToFormulaGrid(ws)
  const summaryMetrics = parseSummaryMetrics(grid, ws.name, formulaGrid)
  return {
    summary: parseSummary(grid),
    summaryMetrics,
    // KAR-960/P3 — reuses the SAME grid already built above, no second sheet
    // read (see module header on summary-field-candidates.ts for the two
    // sub-producers this wraps). KAR-962/P5 — premiseCandidates appended
    // (its own, separately-loaded grid(s), see above).
    summaryFieldCandidates: [...summaryFieldCandidates(grid, summaryMetrics, ws.name), ...premiseCandidates],
  }
}

/**
 * Parse one uploaded QAF file into the summary identity, the canonical summary
 * metrics and the manufacturing steps. Reuses the existing Fertigungskosten
 * parser for steps (read-only; never mutates the file).
 *
 * KAR-958/P1 fix (gate-audit.md B14, "Parse-Entkopplung"): the summary parse
 * and the manufacturing-facet parse used to run as a single `Promise.all` —
 * `parseQAFTemplate` THROWS when the Fertigungskosten/Manufacturing-costs
 * sheet's header is unreadable (missing core columns, or the workbook has no
 * sheets at all; see qaf-parser.ts's CORE_MANUFACTURING_FIELD_KEYS gate),
 * which used to reject the WHOLE `Promise.all` — discarding the already-
 * successful `summary`/`summaryMetrics` parse right along with it, even
 * though the two sheets are structurally independent. `Promise.allSettled`
 * isolates the two facets: a manufacturing-facet failure now degrades to an
 * empty `steps` array plus a structured `manufacturingDegradation` finding
 * (types.ts's Empty-Field-Reason-Taxonomie, KAR-958/P3), while `summary`/
 * `summaryMetrics` come back exactly as they would have without the failure.
 */
export async function parseQafFile(file: File): Promise<{
  summary: QafSummary
  summaryMetrics: SummaryMetricsParse | null
  steps: QAFRow[]
  /** Fertigungskosten header-mapping diagnostics (KAR-893/P1.2), lifted off
   * parseQAFTemplate's QAFParseResult meta so callers that only care about
   * `steps: QAFRow[]` are unaffected. `ignoredCandidateSheets` (KAR-927/P0.2)
   * is additive — undefined for every parse where at most one
   * MANUFACTURING-named sheet existed. All-defaults (0/[]/0) when the
   * manufacturing facet failed — see `manufacturingDegradation` below. */
  manufacturingParseMeta: {
    parseConfidence: number
    unmappedHeaders: string[]
    mappedFieldCount: number
    ignoredCandidateSheets?: string[]
  }
  /** KAR-958/P1 — set only when the manufacturing-facet parse threw (see
   * module header above). `undefined` on every parse where it succeeded
   * (the overwhelming common case), so existing callers that only destructure
   * `summary`/`steps` are unaffected. */
  manufacturingDegradation?: FacetDegradation
  /** KAR-960/P3, Hebel A — additive: `steps` turned into process-scoped
   * FieldCandidate[] (internal/capability/manufacturing-field-candidates.ts).
   * `[]` when the manufacturing facet failed/found no rows — never a
   * fabricated candidate for a failed parse. */
  manufacturingFieldCandidates: FieldCandidate<number>[]
  /** KAR-960/P3, Hebel B — additive passthrough of parseSummarySheet's own
   * summaryFieldCandidates (see that function's doc comment). */
  summaryFieldCandidates: FieldCandidate<number>[]
}> {
  const buffer = await file.arrayBuffer()
  const [sheetResult, stepsResult] = await Promise.allSettled([parseSummarySheet(buffer), parseQAFTemplate(file)])

  if (sheetResult.status === 'rejected') {
    // parseSummarySheet degrades internally (an all-null QafSummary when
    // there's no SUMMARY sheet) rather than throwing, in every known
    // real-corpus case — but Promise.allSettled makes this branch symmetric
    // with the manufacturing one below rather than a silent assumption.
    // There is no meaningful partial parseQafFile result without ANY
    // summary (every downstream consumer of this shape assumes `summary` is
    // always present), so this one case still propagates.
    throw sheetResult.reason
  }
  const sheet = sheetResult.value

  if (stepsResult.status === 'fulfilled') {
    const steps = stepsResult.value
    // KAR-960/P3, Hebel A: the Fertigungskosten sheet name itself is not
    // carried on QAFParseMeta (qaf-parser.ts) — derived the SAME
    // ergebnis-neutral way the failure branch below already does (both loads
    // read the identical buffer, so `sheet.sheetNames` is authoritative for
    // this file regardless of which parse succeeded).
    const manufacturingSheetName = sheet.sheetNames.find((name) => matchesModuleSheetName(name, 'MANUFACTURING')) ?? null
    return {
      summary: sheet.summary,
      summaryMetrics: sheet.summaryMetrics,
      steps,
      manufacturingParseMeta: {
        parseConfidence: steps.parseConfidence,
        unmappedHeaders: steps.unmappedHeaders,
        mappedFieldCount: steps.mappedFieldCount,
        ignoredCandidateSheets: steps.ignoredCandidateSheets,
      },
      manufacturingFieldCandidates: manufacturingStepFieldCandidates(steps, manufacturingSheetName, steps.parseConfidence),
      summaryFieldCandidates: sheet.summaryFieldCandidates,
    }
  }

  // Manufacturing facet failed — degrade ONLY this facet (see module header).
  // Best-effort sheet-name diagnostic: PR #325 review fix #6 (cleanup) — this
  // used to pay for a SECOND full `loadExcelWorkbook(buffer)` re-parse of the
  // same buffer purely to read worksheet names, even though `parseSummarySheet`
  // (the OTHER half of the same Promise.allSettled call, a few lines above)
  // already loaded this exact buffer and its `sheetNames` field carries
  // every worksheet name from that load. Reusing it here is free (no I/O, no
  // ExcelJS parse) and ergebnis-neutral — same `matchesModuleSheetName`
  // lookup, same names, since both loads read the identical buffer.
  const failedSheet = sheet.sheetNames.find((name) => matchesModuleSheetName(name, 'MANUFACTURING')) ?? null

  return {
    summary: sheet.summary,
    summaryMetrics: sheet.summaryMetrics,
    steps: [],
    manufacturingParseMeta: { parseConfidence: 0, unmappedHeaders: [], mappedFieldCount: 0 },
    manufacturingDegradation: {
      facet: 'manufacturing',
      reason: 'PARSE_FAILED',
      sheet: failedSheet,
      message: stepsResult.reason instanceof Error ? stepsResult.reason.message : String(stepsResult.reason),
    },
    // KAR-960/P3 — no rows to derive candidates from when the facet failed.
    manufacturingFieldCandidates: [],
    summaryFieldCandidates: sheet.summaryFieldCandidates,
  }
}