// G60 detail-QAF parser core (KAR-840 G60 stage 1) — 1:1 port of the
// validated V11 b3_core.js extraction: INPUT rates/card, Stückzahlen volumes,
// cost tabs (^\d+_2) with row-41 aggregates, EUR-row SGA/Profit split by
// directed/sourced, process steps and the full component rows used by the
// scenario engine later.
//
// Pure: operates on a CellGetter interface (A1 → value), so the whole engine
// is unit-testable without ExcelJS; the workbook bridge lives in the upload
// pipeline (stage G2).

import { normalizePosition, parseLocaleNumber } from '../normalizer'

/** Read access to one sheet: A1 address → raw cell value. */
export type CellGetter = (addr: string) => unknown

export interface G60Workbook {
  sheetNames: string[]
  sheet(name: string): CellGetter | null
}

/** Test/adapter helper: sheet from an { A1: value } map. */
export function sheetFromCells(cells: Record<string, unknown>): CellGetter {
  return (addr) => cells[addr] ?? null
}

/** Test/adapter helper: workbook from named sheets. */
export function workbookFromSheets(sheets: Record<string, CellGetter>): G60Workbook {
  return {
    sheetNames: Object.keys(sheets),
    sheet: (name) => sheets[name] ?? null,
  }
}

// ── V11 constants ─────────────────────────────────────────────────────────────

/** Master sheets that match the tab pattern but are never cost tabs. */
const MASTERS = new Set(['SBM_Matrix', 'SBM_Dropdown', 'Preise 02', 'Serie 02'])

/** Row-41 aggregate columns (V11 MCOL). */
const MCOL = {
  W: 'W',
  Y: 'Y',
  Pers: 'AS',
  Mach: 'AT',
  AV: 'AV',
  MfgTot: 'BB',
  Scrap: 'BD',
  HK: 'BF',
  Sur: 'DI',
  Sales: 'DJ',
  TC: 'DG',
} as const

export type G60MetricKey = keyof typeof MCOL

/** Component/step rows of a cost tab (V11: fixed range 15–38). Exported so the
 * structure guard (structure-guard.ts, KAR-888) can derive the header-row
 * coordinate (TAB_ROW_FIRST − 1) from the same single source of truth. */
export const TAB_ROW_FIRST = 15
const TAB_ROW_LAST = 38
export const SECONDS_PER_HOUR = 3600

/**
 * Numeric cell read. Deliberately BETTER than V11's parseFloat, which
 * truncates German decimals ("38,50" → 38, silently losing the fraction):
 * locale-aware parse first, then — like parseFloat — a leading numeric
 * prefix for unit-suffixed text ("38 EUR/h" → 38, "38,50 €/h" → 38.5).
 */
function num(v: unknown): number | null {
  const parsed = parseLocaleNumber(v)
  if (parsed !== null) return parsed
  if (typeof v !== 'string') return null
  const prefix = v.trim().match(/^-?\d+(?:[.,]\d+)?/)
  return prefix ? parseLocaleNumber(prefix[0]) : null
}

const str = normalizePosition

function natKey(s: string): number {
  const m = s.match(/^(\d+)/)
  return m ? parseInt(m[1], 10) : 999
}

// ── Workbook-level readers ────────────────────────────────────────────────────

/** Cost tabs: ^\d+_2, masters excluded, natural sort (V11 detailTabs). */
export function detailTabs(wb: G60Workbook): string[] {
  return wb.sheetNames
    .filter((s) => /^\d+_2/.test(s.trim()) && !MASTERS.has(s))
    .sort((a, b) => {
      const na = natKey(a)
      const nb = natKey(b)
      return na !== nb ? na - nb : a.localeCompare(b)
    })
}

/** INPUT!B22-B29 rate-card label cells — mirrors the rows inputRates() reads
 * at C22-C29. Kept local to detectG60's hardening check (below) rather than
 * imported from structure-guard.ts to avoid a runtime circular import
 * (structure-guard.ts depends on parser.ts's types, never the reverse). */
const INPUT_RATE_LABEL_ROWS = [22, 23, 24, 25, 26, 27, 28, 29] as const

/**
 * KAR-961/P4 — detectG60's three signals (exact 'INPUT' sheet name, exact
 * `^\d+_2` detail-tab convention, exact B22-B29 rate-label rows) used to be
 * hardcoded literals (gate-audit.md B1/B2: "starrer INPUT-Name + N_2-
 * Konvention"). They are now a config object so a FUTURE calibration pass —
 * once a real drifted-G60 sample exists (task instruction: "Blanko-
 * Templates kommen evtl. später... baue so, dass Kalibrierung nachschärfbar
 * ist, Konfig-getriebene Profile, nicht Hardcode") — can widen them WITHOUT
 * touching detectG60's logic, only this config.
 *
 * The defaults below are DELIBERATELY functionally IDENTICAL to the
 * pre-KAR-961 hardcoded behavior: zero corpus evidence exists today for any
 * real file that needs a wider match — the two candidate "new template
 * generation" families this PR actually onboards (see
 * cost-allocation-family.ts) turned out, on inspection of their real sheet
 * names, to have NO rate-card sheet and NO numbered cost tabs at all (a
 * genuinely different structure, not a renamed/drifted G60) — so widening
 * detectG60 itself would not have helped them, and this PR does not widen
 * it beyond the identity defaults. This is verified empirically by the
 * mandatory KAR-961 dev-split sweep (see
 * qaf-corpus/reports/p4-classification-diff.md): detectG60's boolean result
 * is unchanged for all 227 dev-split files under these defaults. Widening
 * `inputSheetNames`/`detailTabPatterns`/`rateLabelRowWindow`, or explicitly
 * setting `inputSheetNameMatch: 'caseInsensitiveTrimmed'`, is safe, additive
 * calibration work for the day a real drifted G60 sample shows up — every
 * one of them is a strict superset/widening of the current defaults, never
 * a narrowing, so it can only ADD detections, never change an
 * already-positive or already-negative file's classification for a pattern
 * that doesn't apply to it. PR #328 review fix: this widening is available
 * ONLY via an explicitly non-default config object — no production call
 * site constructs one today (`detectG60(g60wb)` in actions.ts always uses
 * the implicit default), so it is documented future option, not active
 * behavior.
 */
export interface G60DetectionConfig {
  /** Rate-card sheet name candidates, matched per `inputSheetNameMatch`
   * below — checked in `wb.sheetNames` order; first match wins. This alone
   * is never sufficient: `rateLabelRowWindow` below still gates on it, so a
   * same-named sheet belonging to an unrelated template family is still
   * correctly rejected. Default: just `'INPUT'` (today's exact, single
   * hardcoded name). */
  inputSheetNames: readonly string[]
  /**
   * PR #328 review fix (finding [0]): match strategy for `inputSheetNames`
   * against `wb.sheetNames`. `'exact'` (the default) is a byte-identical
   * `wb.sheet(name)` lookup — case-sensitive, no trimming, exactly the
   * pre-KAR-961 hardcoded `wb.sheet('INPUT')` behavior (ExcelJS
   * `getWorksheet` is `worksheet.name === id`, strictly case-sensitive).
   * `'caseInsensitiveTrimmed'` additionally accepts a sheet whose name
   * differs from an `inputSheetNames` entry only in case/whitespace — an
   * explicit, future calibration OPT-IN for a real drifted-G60 sample (see
   * the class doc above), never active under DEFAULT_G60_DETECTION_CONFIG.
   * Before this fix the tolerant fallback ran unconditionally, even under
   * the untouched default config, silently widening detection for every
   * caller (the exact regression this field closes — see
   * detect-g60-corpus-sweep and parser.test.ts's dedicated regression
   * case).
   */
  inputSheetNameMatch: 'exact' | 'caseInsensitiveTrimmed'
  /** Detail/cost-tab name patterns — a sheet counts as a candidate if ANY
   * pattern matches its trimmed name (MASTERS still excluded). Default:
   * just `^\d+_2` (today's exact, single hardcoded convention). Purely a
   * DETECTION signal — the tab SET actually extracted still comes from the
   * unchanged, exported `detailTabs()` above (parser.ts/structure-guard.ts
   * scope discipline: this PR touches routing, not extraction). */
  detailTabPatterns: readonly RegExp[]
  /** Row window scanned (column B) for the rate-card labels — "Ratenkarten-
   * Region-Erkennung" instead of the fixed 8 rows: a small window around
   * the canonical INPUT!B22-B29 range lets a one/two-row template shift
   * still register as "this looks like a rate-card region" for the
   * DETECTION decision, without touching the exact-coordinate READ
   * (inputRates()/extractTab() below stay untouched — KAR-888 scope
   * discipline). Default: exactly rows 22-29 (today's fixed range, zero
   * widening). */
  rateLabelRowWindow: { readonly first: number; readonly last: number }
  /** How many non-blank labels within the window count as "has a rate
   * card". Default: 1 (today's `.some(...)` — any single label). */
  minRateLabelHits: number
}

export const DEFAULT_G60_DETECTION_CONFIG: G60DetectionConfig = {
  inputSheetNames: ['INPUT'],
  inputSheetNameMatch: 'exact',
  detailTabPatterns: [/^\d+_2/],
  rateLabelRowWindow: { first: INPUT_RATE_LABEL_ROWS[0], last: INPUT_RATE_LABEL_ROWS[INPUT_RATE_LABEL_ROWS.length - 1] },
  minRateLabelHits: 1,
}

/** Resolves the rate-card sheet against `config.inputSheetNames` — exact
 * name lookup first (today's `wb.sheet('INPUT')` behavior, byte-identical
 * for the default single-entry list, `config.inputSheetNameMatch` regardless
 * of its value: an exact match is tried unconditionally before anything
 * tolerant). Only when `inputSheetNameMatch === 'caseInsensitiveTrimmed'`
 * (never the case for DEFAULT_G60_DETECTION_CONFIG, see that field's doc
 * comment) does a second, tolerant pass run as a structural safety net for a
 * sheet name that differs only in case/whitespace. PR #328 review fix
 * (finding [0]): this second pass used to run unconditionally, silently
 * widening detectG60's DEFAULT config beyond the pre-KAR-961 hardcoded
 * behavior it claims to reproduce byte-identically. */
function resolveInputSheetCandidate(wb: G60Workbook, config: G60DetectionConfig): CellGetter | null {
  for (const name of config.inputSheetNames) {
    const exact = wb.sheet(name)
    if (exact) return exact
  }
  if (config.inputSheetNameMatch !== 'caseInsensitiveTrimmed') return null
  const synonyms = new Set(config.inputSheetNames.map((n) => n.trim().toLowerCase()))
  for (const sheetName of wb.sheetNames) {
    if (synonyms.has(sheetName.trim().toLowerCase())) {
      const sheet = wb.sheet(sheetName)
      if (sheet) return sheet
    }
  }
  return null
}

/** Detail/cost-tab candidates for the DETECTION decision only — see
 * `G60DetectionConfig.detailTabPatterns` doc for why this is a separate,
 * local helper rather than a change to the exported `detailTabs()` above. */
function detectionDetailTabs(wb: G60Workbook, patterns: readonly RegExp[]): string[] {
  return wb.sheetNames.filter((s) => {
    const trimmed = s.trim()
    return !MASTERS.has(s) && patterns.some((p) => p.test(trimmed))
  })
}

function hasRateCardRegionLabel(sheet: CellGetter, config: G60DetectionConfig): boolean {
  let hits = 0
  for (let row = config.rateLabelRowWindow.first; row <= config.rateLabelRowWindow.last; row++) {
    if (str(sheet(`B${row}`)).trim() !== '') hits++
  }
  return hits >= config.minRateLabelHits
}

/**
 * Conservative G60 detection: a rate-card sheet + at least one cost tab AND
 * no Zusammenfassung/Summary sheet — a Summary QAF that coincidentally
 * carries a rate-card-named tab must never be misrouted into the G60-only
 * path.
 *
 * KAR-888 hardening, now capability-signal-based (KAR-961/P4, gate-audit.md
 * B1/B2): a workbook that superficially matches (has a rate-card-named
 * sheet + cost-tab-shaped tab names) but carries NONE of the rate-card
 * labels the parser depends on (INPUT!B22-B29, or the configured window) is
 * not a G60 file that merely drifted — it does not carry the rate-card
 * structure at all, and routing it into the G60 path would silently read
 * unrelated cells as if they were the SG&A/profit rates (Master-Prompt §7,
 * "silent template modification without an updated version number"). A
 * single/partial label gap is deliberately NOT rejected here — that softer
 * case is exactly what the structure guard (structure-guard.ts) exists to
 * flag per-anchor once the file is already routed into the G60 path,
 * without losing the rest of a genuinely-G60 file to a hard reject.
 *
 * Master-Prompt §0 discipline: any template-fingerprint/family LABEL
 * (template-fingerprint.ts, multi-qaf/template-fingerprint.ts,
 * cost-allocation-family.ts) is a HINT only, never consulted here — this
 * function decides purely from the workbook's own structural signals
 * (sheet names + rate-card label presence), the same discipline the
 * pre-KAR-961 version already had, now made explicit via `config` instead
 * of inline literals.
 */
export function detectG60(wb: G60Workbook, config: G60DetectionConfig = DEFAULT_G60_DETECTION_CONFIG): boolean {
  const inputSheet = resolveInputSheetCandidate(wb, config)
  if (inputSheet === null) return false
  if (detectionDetailTabs(wb, config.detailTabPatterns).length === 0) return false
  if (wb.sheetNames.some((s) => /zusammenfassung|summary/i.test(s))) return false
  return hasRateCardRegionLabel(inputSheet, config)
}

/** SG&A/Profit master rates, INPUT!C22–C29 (V11 inputRates). */
export interface G60Rates {
  ovFK_d: number
  ovMAT_d: number
  pfFK_d: number
  pfMAT_d: number
  ovFK_s: number
  ovMAT_s: number
  pfFK_s: number
  pfMAT_s: number
}

/** KAR-894/P1.3: source-cell addresses for the 8 INPUT!C22-C29 rate fields.
 * A plain constant, not a function of the workbook — inputRates() always
 * reads these exact fixed coordinates (no header search), so the addresses
 * never vary by file content; nothing is gained by persisting them per
 * comparison (unlike G60TabAggregate/G60Step sourceCells, which DO vary by
 * tab), so rehydrate.ts references this constant directly instead of
 * round-tripping it through the DB. */
export type G60RatesSourceCells = Record<keyof G60Rates, string>

export const G60_RATES_SOURCE_CELLS: G60RatesSourceCells = {
  ovFK_d: 'INPUT!C22',
  ovMAT_d: 'INPUT!C23',
  pfFK_d: 'INPUT!C24',
  pfMAT_d: 'INPUT!C25',
  ovFK_s: 'INPUT!C26',
  ovMAT_s: 'INPUT!C27',
  pfFK_s: 'INPUT!C28',
  pfMAT_s: 'INPUT!C29',
}

export function inputRates(wb: G60Workbook): G60Rates {
  const input = wb.sheet('INPUT')
  const r = (n: number): number => (input ? (num(input(`C${n}`)) ?? 0) : 0)
  return {
    ovFK_d: r(22),
    ovMAT_d: r(23),
    pfFK_d: r(24),
    pfMAT_d: r(25),
    ovFK_s: r(26),
    ovMAT_s: r(27),
    pfFK_s: r(28),
    pfMAT_s: r(29),
  }
}

/** Rate card INPUT!B/C rows 20–59, keyed by C-code (V11 inputCard). */
export interface G60CardEntry {
  label: string
  value: unknown
}

export type G60InputCard = Record<string, G60CardEntry>

export function inputCard(wb: G60Workbook): G60InputCard {
  const input = wb.sheet('INPUT')
  const card: G60InputCard = {}
  if (!input) return card
  for (let row = 20; row < 60; row++) {
    const label = input(`B${row}`)
    if (label === null || label === undefined || label === '') continue
    card[`C${row}`] = { label: str(label), value: input(`C${row}`) }
  }
  return card
}

/** Years/volumes from the Stückzahlen sheet, row 4/5, columns C–K (V11 volumes). */
export interface G60Volumes {
  years: number[]
  vol: number[]
}

const COLS_C_TO_K = ['C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K']

export function volumes(wb: G60Workbook): G60Volumes {
  const sheet = wb.sheet('Stückzahlen')
  if (!sheet) return { years: [], vol: [] }
  const years: number[] = []
  const vol: number[] = []
  // Deliberately BETTER than V11: columns are filtered PAIRWISE here. V11
  // keeps raw arrays and later slices vol by the filtered years' length, so a
  // mid-sheet year gap shifts the following volumes onto the wrong years.
  for (const col of COLS_C_TO_K) {
    const y = num(sheet(`${col}4`))
    if (y === null) continue
    years.push(y)
    vol.push(num(sheet(`${col}5`)) ?? 0)
  }
  return { years, vol }
}

// ── Cost-tab extraction ───────────────────────────────────────────────────────

/** KAR-894/P1.3: source-cell addresses ("&lt;TabName&gt;!AO15") for the 3
 * G60Step fields that correspond to a structure-guard header anchor (AO/AQ/AT
 * — see structure-guard.ts TAB_HEADER_ANCHORS). ppc/ineff/scrapstep (AP/AS/BC)
 * are fix-coordinate-only columns with no verified anchor text (AS in
 * particular means a different thing at row 41 vs per-row — see
 * structure-guard.ts's TAB_HEADER_ANCHORS comment — so it deliberately never
 * got one) and are left out here rather than implying a confidence guarantee
 * that does not exist for them. Optional (KAR-886 pattern): rows persisted
 * before this PR round-trip through rehydrate.ts without a sourceCells entry
 * — readers must tolerate `undefined`. */
export interface G60StepSourceCells {
  cycle?: string
  emp?: string
  machrate?: string
}

export interface G60Step {
  row: number
  cycle: number
  ppc: number | null
  emp: number | null
  ineff: number | null
  machrate: number | null
  scrapstep: number | null
  sourceCells?: G60StepSourceCells
}

export interface G60TabAggregate extends Record<G60MetricKey, number> {
  part: string
  /**
   * PR #328 review fix (finding [3]/[5]/[6]): `null` — not `0` — when the
   * INPUT rate-card is hard-broken (structure-guard.ts's `rateCardBroken`)
   * and SGA could therefore not be verifiably computed. §23-principle:
   * "fehlend sichtbar fehlend" — a genuine SGA of 0 (rare but possible, e.g.
   * an all-directed tab with zero overhead rate) must stay distinguishable
   * from "we withheld this because the rate card was broken". Every other
   * caller (a normal, non-degraded parse) still always produces a `number`
   * here — only structure-guard.ts's guarded path ever writes `null`.
   */
  SGA: number | null
  /** Same `null`-on-degradation contract as `SGA` above. */
  Profit: number | null
  steps: G60Step[]
  /** Primary machine rate (row 15 — molding step), V11 at15. */
  at15: number | null
  Material: number
  Labor: number
  Manufacturing: number
  ScrapB: number
  /** KAR-894/P1.3: source-cell address ("&lt;TabName&gt;!W41" etc.) per
   * row-41 MCOL aggregate field — Master-Prompt §17 cell-to-result
   * traceability, additive, no change to the values themselves. Optional for
   * the same reason as G60Step.sourceCells (pre-P1.3 persisted rows lack
   * it). */
  sourceCells?: Partial<Record<G60MetricKey, string>>
}

/** KAR-894/P1.3: the 5 structure-guard-anchored columns (W/Y/AO/AQ/AT) that
 * label-based localization (structure-guard.ts locateG60ColumnAnchors) may
 * resolve to a neighbor column. Every other fix-coordinate column (AS, AP,
 * AR, BC, AX, BA, AZ, AD, DB, …) has no verified anchor text and stays
 * literal — see structure-guard.ts module header for why only these five
 * were verified against the two real files. */
export interface G60ColumnOverrides {
  W?: string
  Y?: string
  AO?: string
  AQ?: string
  AT?: string
}

/**
 * One cost tab → aggregates + buckets + steps (V11 extractTab).
 *
 * `tabName` (KAR-894/P1.3) feeds the per-value sourceCells addresses
 * ("<TabName>!W41" etc.) — required, not optional, so a caller cannot
 * silently produce an empty-prefix address by omission.
 *
 * `columnOverrides` (KAR-894/P1.3) lets structure-guard.ts's anchor-based
 * localization redirect exactly the 5 verified anchor columns (W/Y/AO/AQ/AT)
 * to a neighbor column when the header anchor was found shifted ±1/±2 —
 * every other fix-coordinate column (AP/AS/AR/BC/AX/BA/AZ/AD/DB) stays
 * literal, unaffected by this parameter. Defaults to `{}` (every literal
 * unchanged), so omitting it is byte-identical to the pre-P1.3 behaviour —
 * the acceptance criterion for this change (KAR-888 precedent).
 */
export function extractTab(
  sheet: CellGetter,
  rates: G60Rates,
  tabName: string,
  columnOverrides: G60ColumnOverrides = {},
): G60TabAggregate {
  const g = (col: string, row: number): number | null => num(sheet(`${col}${row}`))
  const addr = (col: string, row: number): string => `${tabName}!${col}${row}`

  const colW = columnOverrides.W ?? 'W'
  const colY = columnOverrides.Y ?? 'Y'
  const colAO = columnOverrides.AO ?? 'AO'
  const colAQ = columnOverrides.AQ ?? 'AQ'
  const colAT = columnOverrides.AT ?? 'AT'
  // MCOL.Mach ('AT') is the row-41 aggregate counterpart of the same AT
  // anchor that governs per-row machrate/at15 below — one resolved column
  // for both uses, so a relocation is applied consistently tab-wide.
  const rowColumnOverrideByKey: Partial<Record<G60MetricKey, string>> = { W: colW, Y: colY, Mach: colAT }

  // V11: cell('C15') || '' — any falsy part id (incl. a literal 0) reads as "".
  const rawPart = sheet('C15')
  const rec = { part: rawPart ? str(rawPart) : '' } as G60TabAggregate
  const sourceCells = {} as Record<G60MetricKey, string>
  for (const key of Object.keys(MCOL) as G60MetricKey[]) {
    const col = rowColumnOverrideByKey[key] ?? MCOL[key]
    rec[key] = g(col, 41) ?? 0
    sourceCells[key] = addr(col, 41)
  }
  rec.sourceCells = sourceCells

  let sga = 0
  let profit = 0
  const steps: G60Step[] = []
  for (let r = TAB_ROW_FIRST; r <= TAB_ROW_LAST; r++) {
    const unit = sheet(`L${r}`)
    const ax = g('AX', r) ?? 0
    const w = g(colW, r) ?? 0
    const sourcing = str(sheet(`E${r}`)).toLowerCase()
    if (unit === 'EUR') {
      if (sourcing === 'directed') {
        sga += ax * rates.ovFK_d + w * rates.ovMAT_d
        profit += ax * rates.pfFK_d + w * rates.pfMAT_d
      } else {
        sga += ax * rates.ovFK_s + w * rates.ovMAT_s
        profit += ax * rates.pfFK_s + w * rates.pfMAT_s
      }
    }
    const cycle = g(colAO, r)
    if (cycle && cycle > 0) {
      steps.push({
        row: r,
        cycle,
        ppc: g('AP', r),
        emp: g(colAQ, r),
        ineff: g('AS', r),
        machrate: g(colAT, r),
        scrapstep: g('BC', r),
        sourceCells: { cycle: addr(colAO, r), emp: addr(colAQ, r), machrate: addr(colAT, r) },
      })
    }
  }

  rec.SGA = sga
  rec.Profit = profit
  rec.steps = steps
  rec.at15 = g(colAT, 15)
  rec.Material = rec.W + rec.Y
  rec.Labor = rec.Pers
  rec.Manufacturing = rec.MfgTot - rec.Pers
  rec.ScrapB = rec.Scrap
  return rec
}

/** Primary process step = highest machine rate (V11 primaryStep). */
export function primaryStep(rec: G60TabAggregate): G60Step | null {
  const rated = rec.steps.filter((s) => s.machrate)
  if (rated.length) return rated.reduce((a, b) => ((b.machrate ?? 0) > (a.machrate ?? 0) ? b : a))
  return rec.steps[0] ?? null
}

/**
 * Primary process-step row selection (V11 rule, `calculatorParamsFrom` in
 * app/qaf-differences/actions.ts): gate on AO>0 (Zykluszeit — a real process
 * step, not a material/summary row), then tie-break on the highest AT
 * (Maschinensatz).
 *
 * Single source of truth for `calculatorParamsFrom` (which row's values the
 * Preis-Kalkulator chips load) and `g60TabLabel` below (which row's
 * desig/proc the dropdown shows) — PR #330 review fix (KAR-966 item 1): these
 * two used to implement the gate independently and had drifted (`g60TabLabel`
 * gated on AT>0 instead of AO>0), so a tab with one AO>0/AT=0 row and one
 * AO<=0/AT>0 row showed the label of one row while loading the calculator
 * values of a DIFFERENT row.
 */
export function primaryComponentRow<T extends { AO: number; AT: number }>(rows: readonly T[]): T | null {
  const candidates = rows.filter((r) => typeof r.AO === 'number' && r.AO > 0)
  if (candidates.length === 0) return null
  return candidates.reduce((a, b) => ((b.AT ?? 0) > (a.AT ?? 0) ? b : a))
}

/**
 * KAR-966 (Kais' UI feedback, item 1): speaking Preis-Kalkulator dropdown
 * label for one Kostenreiter tab — the dropdown used to show only the bare
 * `tab_name` ("1", "2", …), which carries no information about what that
 * cost tab actually IS.
 *
 * Two real fields feed this, both already captured for other purposes:
 * - `part` = `qaf_g60_tab.part` (cell C15, "Teil"/Sachnummer — the same
 *   value the Excel export's "Produktion" sheet already prints as `Teil`,
 *   export.ts).
 * - the process name = the PRIMARY row's `desig` ("Bezeichnung", column D)
 *   or `proc` (column AK) — via `primaryComponentRow` above, the SAME
 *   AO>0-gated/AT-tie-broken rule `calculatorParamsFrom` uses to pick the
 *   tab-representative row, so the label describes the exact row whose
 *   values the chips below adopt.
 *
 * Format: "<Name> (<Sachnummer>)" when both are present, else the best
 * available single value, "Schritt <tabName>" only as the last resort — the
 * bare numeric tab_name pre-KAR-966 behaviour, kept as a fallback so a tab
 * with genuinely no text in D/AK/C15 still gets a stable, addressable label
 * instead of an empty string. Never invents a value (Master-Prompt §23).
 */
export function g60TabLabel(
  tabName: string,
  part: string | null,
  rows: readonly Pick<G60ComponentRow, 'desig' | 'proc' | 'AO' | 'AT'>[],
): string {
  const primary = primaryComponentRow(rows)
  const name = primary?.desig?.trim() || primary?.proc?.trim() || null
  const partTrimmed = part?.trim() || null
  if (name && partTrimmed) return `${name} (${partTrimmed})`
  if (name) return name
  if (partTrimmed) return partTrimmed
  return `Schritt ${tabName}`
}

// ── Full component rows (scenario engine base, V11 extractFullTab) ───────────

export interface G60ComponentRow {
  r: number
  proc: string
  desig: string
  L: unknown
  AY: unknown
  E: string
  W: number
  X: number
  Y: number
  AO: number
  AP: number
  AQ: number
  AR: number
  AS: number
  AT: number
  BC: number
  AX: number
  BA: number
  AZ: number
  AD: number
  DB: number
  /** Setup/other manufacturing per unit, held constant in scenarios. */
  rem_pu: number
}

/**
 * Full component rows (scenario engine base, V11 extractFullTab).
 *
 * `columnOverrides` (KAR-894/P1.3 adversarial-review follow-up): the same
 * anchor-column redirection extractTab applies to the aggregate path — a
 * relocated W/Y/AO/AQ/AT column must be read from its resolved position HERE
 * too, or the scenario/what-if engine (recomputeTab/recomputeScenario, which
 * reads exclusively from these G60ComponentRow values, never from
 * G60TabAggregate) would silently compute off the stale fix-coordinate cell
 * while the detail view (fed by extractTab's already-relocated aggregate)
 * shows the correct number — exactly the "displayed value hides a stale
 * formula/source" failure Master-Prompt §12.4 warns about, just one layer
 * down. Every other fix-coordinate column (X/AP/AR/AS/BC/AX/BA/AZ/AD/DB)
 * stays literal — no verified anchor exists for them (see G60ColumnOverrides
 * doc comment). Defaults to `{}` (every literal unchanged) — byte-identical
 * to the pre-fix behaviour when omitted.
 */
export function extractFullTab(sheet: CellGetter, columnOverrides: G60ColumnOverrides = {}): G60ComponentRow[] {
  const g = (col: string, row: number): number => num(sheet(`${col}${row}`)) ?? 0
  const colW = columnOverrides.W ?? 'W'
  const colY = columnOverrides.Y ?? 'Y'
  const colAO = columnOverrides.AO ?? 'AO'
  const colAQ = columnOverrides.AQ ?? 'AQ'
  const colAT = columnOverrides.AT ?? 'AT'
  const rows: G60ComponentRow[] = []
  for (let r = TAB_ROW_FIRST; r <= TAB_ROW_LAST; r++) {
    const rec: G60ComponentRow = {
      r,
      proc: str(sheet(`AK${r}`)),
      desig: str(sheet(`D${r}`)),
      L: sheet(`L${r}`),
      AY: sheet(`AY${r}`),
      E: str(sheet(`E${r}`)).toLowerCase(),
      W: g(colW, r),
      X: g('X', r),
      Y: g(colY, r),
      AO: g(colAO, r),
      AP: g('AP', r),
      AQ: g(colAQ, r),
      AR: g('AR', r),
      AS: g('AS', r),
      AT: g(colAT, r),
      BC: g('BC', r),
      AX: g('AX', r),
      BA: g('BA', r),
      AZ: g('AZ', r),
      AD: g('AD', r),
      DB: g('DB', r),
      rem_pu: 0,
    }
    const labPu = rec.AP > 0 ? (rec.AQ * rec.AR * (rec.AS + 1) * rec.AO) / rec.AP / SECONDS_PER_HOUR : 0
    const macPu = rec.AP > 0 ? (rec.AT * rec.AO) / rec.AP / SECONDS_PER_HOUR : 0
    rec.rem_pu = rec.AX - labPu - macPu
    rows.push(rec)
  }
  return rows
}

// ── Whole-file parse ──────────────────────────────────────────────────────────

export interface G60ParseResult {
  rates: G60Rates
  /** KAR-894/P1.3: source-cell addresses for `rates` — see G60_RATES_SOURCE_CELLS. */
  ratesSourceCells: G60RatesSourceCells
  card: G60InputCard
  volumes: G60Volumes
  /** Aggregates per cost tab, in natural tab order. */
  tabs: Record<string, G60TabAggregate>
}

export function parseG60Workbook(wb: G60Workbook): G60ParseResult {
  const rates = inputRates(wb)
  const tabs: Record<string, G60TabAggregate> = {}
  for (const name of detailTabs(wb)) {
    const sheet = wb.sheet(name)
    if (sheet) tabs[name] = extractTab(sheet, rates, name)
  }
  return { rates, ratesSourceCells: G60_RATES_SOURCE_CELLS, card: inputCard(wb), volumes: volumes(wb), tabs }
}