// Unit tests for profile-parser.ts (KAR-932 / Multi-QAF-Programm P1.4).
//
// FIXTURE-DATEN-REGEL (same discipline as header-parser.test.ts/synthetic-
// fixtures.ts's own header comment, KAR-929 adversarial review F4 —
// merge-blocker class): every code, number, formula text, and cell reference
// below is FREE INVENTION. Before adding any new literal here: grep -inE
// "<code-or-number>" /root/aria/brain/01-Projekte/supplierpulse-multi-qaf/
// 10-analyse-*.md must be EMPTY. Sheet names ("Zusammenfassung",
// "Fertigungskosten", "Material", "LV Detail EU", "Rüstkosten EU") are the
// standard structural QAF template tab names already used non-confidentially
// throughout this codebase (module-sheet-names.ts, synthetic-fixtures.ts,
// formula-lineage.test.ts), not customer-specific. Invented numeric totals
// deliberately avoid the real per-file figures cited in the 10-analyse-*.md
// docs.

import { describe, it, expect } from 'vitest'
import ExcelJS from 'exceljs'
import { gridFromCells } from '../../summary-parser'
import { columnIndexToLetter } from '../types'
import { SHARED_FORMULA_UNRESOLVED } from '../../formula-engine'
import {
  parseSharedCostProfileBlocks,
  locateVolumeBandLookupTable,
  locateManufacturingSummaryRow,
  locateSummaryMoneyRow,
  resolveVariantProfileBindings,
  reconcileProfileToSummary,
  sharedCostProfileCandidateSheets,
  sharedCostProfilesFromWorkbook,
  DEFAULT_PROFILE_COL_FROM,
  MIN_PLAUSIBLE_VOLUME_BAND_THRESHOLD,
  type ProfileParserGridInput,
  type VariantColumnRef,
} from '../profile-parser'
import { DEFAULT_COL_FROM as SUMMARY_VARIANT_BAND_COL_FROM } from '../header-parser'
import type { SharedCostProfile } from '../types'

// ── Grid-building helpers (same per-file duplication discipline as
// header-parser.test.ts's own copy of this helper — no shared cross-file
// test-util module exists in this codebase). ────────────────────────────

function col(col0: number): string {
  return columnIndexToLetter(col0 + 1)
}

type FormulaCellValue = string | typeof SHARED_FORMULA_UNRESOLVED

function formulaGridFromCells(cells: Record<string, FormulaCellValue>): (string | null | typeof SHARED_FORMULA_UNRESOLVED)[][] {
  let maxRow = 0
  let maxCol = 0
  const parsed: Array<{ row: number; col: number; value: FormulaCellValue }> = []
  for (const [addr, value] of Object.entries(cells)) {
    const m = /^([A-Za-z]+)(\d+)$/.exec(addr)
    if (!m) throw new Error(`bad A1 address: ${addr}`)
    let col0 = 0
    for (const ch of m[1].toUpperCase()) col0 = col0 * 26 + (ch.charCodeAt(0) - 64)
    col0 -= 1
    const row0 = Number(m[2]) - 1
    parsed.push({ row: row0, col: col0, value })
    if (row0 > maxRow) maxRow = row0
    if (col0 > maxCol) maxCol = col0
  }
  const grid: (string | null | typeof SHARED_FORMULA_UNRESOLVED)[][] = Array.from({ length: maxRow + 1 }, () => new Array(maxCol + 1).fill(null))
  for (const { row, col: c, value } of parsed) grid[row][c] = value
  return grid
}

// ── Fixture: Fertigungskosten sheet — L-Shape (live SUMIF) + I-Shape
// (literal), both in the SAME Total column (10-analyse-mx.md G). ─────────
function buildLShapeIShapeGrid(): ProfileParserGridInput {
  const totalCol = 20 // column U
  const labelCol = 10 // column K
  const cells: Record<string, unknown> = {
    [`${col(labelCol)}54`]: 'PP l-shape',
    [`${col(totalCol)}54`]: 100,
    [`${col(labelCol)}55`]: 'PP i-shape',
    [`${col(totalCol)}55`]: 55.75,
  }
  const formulaGrid = formulaGridFromCells({
    [`${col(totalCol)}54`]: `SUMIF($R12:$R42,$${col(labelCol)}54,${col(totalCol)}12:${col(totalCol)}42)`,
  })
  return { sheet: 'Fertigungskosten', grid: gridFromCells(cells), formulaGrid }
}

// ── Fixture: 3-block volume-band pattern (10-analyse-nafta.md E,
// 10-analyse-ncar.md F/E-parallel). ────────────────────────────────────────
function buildVolumeBandBlocksGrid(): ProfileParserGridInput {
  const totalCol = 20 // U
  const labelCol = 9 // J
  const cells: Record<string, unknown> = {
    [`${col(labelCol)}32`]: '> 50.000 Stk/a',
    [`${col(totalCol)}32`]: 12.5,
    [`${col(labelCol)}33`]: '> 20.000 Stk/a',
    [`${col(totalCol)}33`]: 15.1,
    [`${col(labelCol)}34`]: '< 20.000 Stk/a',
    [`${col(totalCol)}34`]: 21.4,
  }
  const formulaGrid = formulaGridFromCells({
    [`${col(totalCol)}32`]: `SUMIF($R13:$R16,$${col(labelCol)}32,${col(totalCol)}13:${col(totalCol)}16)`,
    [`${col(totalCol)}33`]: `SUMIF($R18:$R21,$${col(labelCol)}33,${col(totalCol)}18:${col(totalCol)}21)`,
    [`${col(totalCol)}34`]: `SUMIF($R23:$R26,$${col(labelCol)}34,${col(totalCol)}23:${col(totalCol)}26)`,
  })
  return { sheet: 'Fertigungskosten', grid: gridFromCells(cells), formulaGrid }
}

// ── Fixture: Summary sheet with a "Fertigungskosten" row + variant columns
// pointing at cell references. ─────────────────────────────────────────────
function buildSummaryGrid(rowLabelCol0: number, manufacturingRow: number, variantFormulas: Record<number, string | null>, variantLiterals: Record<number, unknown> = {}): ProfileParserGridInput {
  const cells: Record<string, unknown> = {
    [`${col(rowLabelCol0)}${manufacturingRow}`]: 'Fertigungskosten',
  }
  for (const [colStr, literal] of Object.entries(variantLiterals)) {
    cells[`${col(Number(colStr))}${manufacturingRow}`] = literal
  }
  const formulaCells: Record<string, FormulaCellValue> = {}
  for (const [colStr, formula] of Object.entries(variantFormulas)) {
    if (formula !== null) formulaCells[`${col(Number(colStr))}${manufacturingRow}`] = formula
  }
  return { sheet: 'Zusammenfassung', grid: gridFromCells(cells), formulaGrid: formulaGridFromCells(formulaCells) }
}

// ── parseSharedCostProfileBlocks ─────────────────────────────────────────

describe('parseSharedCostProfileBlocks', () => {
  it('detects L-Shape as a live-formula profile and I-Shape as a same-column literal profile', () => {
    const profiles = parseSharedCostProfileBlocks(buildLShapeIShapeGrid())
    expect(profiles).toHaveLength(2)
    const lShape = profiles.find((p) => p.kind === 'lShape')
    const iShape = profiles.find((p) => p.kind === 'iShape')
    expect(lShape).toBeDefined()
    expect(iShape).toBeDefined()
    expect(lShape!.formulaAndCachedValue?.formula).not.toBeNull()
    expect(lShape!.values.totalPerUnit).toBe(100)
    expect(iShape!.formulaAndCachedValue?.formula).toBeNull()
    expect(iShape!.values.totalPerUnit).toBe(55.75)
    expect(lShape!.profileId).toBe(`Fertigungskosten!${col(20)}54`)
    expect(iShape!.profileId).toBe(`Fertigungskosten!${col(20)}55`)
  })

  it('detects a 3-block volume-band pattern, reading direction+threshold as metadata only', () => {
    const profiles = parseSharedCostProfileBlocks(buildVolumeBandBlocksGrid())
    const bandProfiles = profiles.filter((p) => p.kind === 'volumeBand')
    expect(bandProfiles).toHaveLength(3)
    const byRow = new Map(bandProfiles.map((p) => [p.sourceReferences[0]!.cell, p]))
    expect(byRow.get(`${col(20)}32`)?.values.threshold).toBe(50000)
    expect(byRow.get(`${col(20)}33`)?.values.threshold).toBe(20000)
    expect(byRow.get(`${col(20)}34`)?.values.threshold).toBe(0)
  })

  it('never invents a literal profile total for an unlabeled bare number in a known total column', () => {
    const totalCol = 20
    const cells: Record<string, unknown> = {
      [`${col(10)}54`]: 'PP l-shape',
      [`${col(totalCol)}54`]: 100,
      [`${col(totalCol)}56`]: 42, // literal, in the same total column, but NO row label at all.
    }
    const input: ProfileParserGridInput = {
      sheet: 'Fertigungskosten',
      grid: gridFromCells(cells),
      formulaGrid: formulaGridFromCells({ [`${col(totalCol)}54`]: `SUMIF($R12:$R42,$K54,${col(totalCol)}12:${col(totalCol)}42)` }),
    }
    const profiles = parseSharedCostProfileBlocks(input)
    expect(profiles).toHaveLength(1)
    expect(profiles[0]!.sourceReferences[0]!.cell).toBe(`${col(totalCol)}54`)
  })

  it('returns an empty array when there is no formula grid at all (never guesses a literal-only sheet)', () => {
    const profiles = parseSharedCostProfileBlocks({ sheet: 'Fertigungskosten', grid: gridFromCells({ [`${col(20)}54`]: 100 }) })
    expect(profiles).toEqual([])
  })

  // ── KAR-932 adversarial review F2: VOLUME_BAND_LABEL_RE used to have an
  // OPTIONAL unit suffix, so ANY "<N"/">N" annotation (e.g. a tolerance
  // note "< 5") matched as a volume band — findProfileLabel then preferred
  // it over the real label (specific-pattern priority), and
  // classifyProfileKind produced a confidently-wrong threshold. Fix:
  // matchVolumeBandLabel requires a unit/context anchor OR a plausible
  // magnitude (>= MIN_PLAUSIBLE_VOLUME_BAND_THRESHOLD). ───────────────────
  describe('volume-band label plausibility guard (KAR-932 adversarial review F2)', () => {
    it('does NOT classify a unit-less, small tolerance annotation ("< 5") as a volume band', () => {
      const totalCol = 20
      const cells: Record<string, unknown> = {
        [`${col(9)}54`]: '< 5', // tolerance-note annotation, NOT a volume-band label.
        [`${col(totalCol)}54`]: 42,
      }
      const input: ProfileParserGridInput = {
        sheet: 'Fertigungskosten',
        grid: gridFromCells(cells),
        formulaGrid: formulaGridFromCells({ [`${col(totalCol)}54`]: `SUMIF($R1:$R3,$${col(9)}54,${col(totalCol)}1:${col(totalCol)}3)` }),
      }
      const profiles = parseSharedCostProfileBlocks(input)
      expect(profiles).toHaveLength(1)
      expect(profiles[0]!.kind).toBe('manufacturing')
      expect(profiles[0]!.values.threshold).toBeUndefined()
    })

    it('prefers a genuine, FARTHER volume-band label ("> 20.000 Stk/a") over a nearer, implausible "< 5" annotation', () => {
      const totalCol = 20
      const cells: Record<string, unknown> = {
        [`${col(5)}50`]: '> 20.000 Stk/a', // real label, farther from the Total cell.
        [`${col(9)}50`]: '< 5', // irrelevant annotation, nearer to the Total cell.
        [`${col(totalCol)}50`]: 77,
      }
      const input: ProfileParserGridInput = {
        sheet: 'Fertigungskosten',
        grid: gridFromCells(cells),
        formulaGrid: formulaGridFromCells({ [`${col(totalCol)}50`]: `SUMIF($R1:$R3,$${col(5)}50,${col(totalCol)}1:${col(totalCol)}3)` }),
      }
      const profiles = parseSharedCostProfileBlocks(input)
      expect(profiles).toHaveLength(1)
      expect(profiles[0]!.kind).toBe('volumeBand')
      expect(profiles[0]!.label).toBe('> 20.000 Stk/a')
      expect(profiles[0]!.values.threshold).toBe(20000)
    })

    it('still recognizes a unit-less volume-band label via magnitude plausibility (Grenzfall: threshold exactly at MIN_PLAUSIBLE_VOLUME_BAND_THRESHOLD)', () => {
      const totalCol = 20
      const cells: Record<string, unknown> = {
        [`${col(9)}40`]: `>${MIN_PLAUSIBLE_VOLUME_BAND_THRESHOLD}`, // no unit — magnitude alone must qualify.
        [`${col(totalCol)}40`]: 9.9,
      }
      const input: ProfileParserGridInput = {
        sheet: 'Fertigungskosten',
        grid: gridFromCells(cells),
        formulaGrid: formulaGridFromCells({ [`${col(totalCol)}40`]: `SUMIF($R1:$R3,$${col(9)}40,${col(totalCol)}1:${col(totalCol)}3)` }),
      }
      const profiles = parseSharedCostProfileBlocks(input)
      expect(profiles).toHaveLength(1)
      expect(profiles[0]!.kind).toBe('volumeBand')
      expect(profiles[0]!.values.threshold).toBe(MIN_PLAUSIBLE_VOLUME_BAND_THRESHOLD)
    })

    it('Grenzfall: one below MIN_PLAUSIBLE_VOLUME_BAND_THRESHOLD, unit-less, is rejected as a volume band', () => {
      const totalCol = 20
      const cells: Record<string, unknown> = {
        [`${col(9)}41`]: `>${MIN_PLAUSIBLE_VOLUME_BAND_THRESHOLD - 1}`,
        [`${col(totalCol)}41`]: 9.9,
      }
      const input: ProfileParserGridInput = {
        sheet: 'Fertigungskosten',
        grid: gridFromCells(cells),
        formulaGrid: formulaGridFromCells({ [`${col(totalCol)}41`]: `SUMIF($R1:$R3,$${col(9)}41,${col(totalCol)}1:${col(totalCol)}3)` }),
      }
      const profiles = parseSharedCostProfileBlocks(input)
      expect(profiles).toHaveLength(1)
      expect(profiles[0]!.kind).toBe('manufacturing')
    })
  })
})

// ── locateVolumeBandLookupTable ──────────────────────────────────────────

describe('locateVolumeBandLookupTable', () => {
  it('reads a 2-column volume-threshold/lot-count lookup table, tolerating an open-ended last row', () => {
    const cells: Record<string, unknown> = {
      A20: 'Jahresvolumen',
      B20: 'Anzahl Lose',
      A21: 1000,
      B21: 12,
      A22: 5000,
      B22: 18,
      A23: 10000,
      B23: 24,
      A24: 50000,
      B24: 48,
      A25: 'sonst',
      B25: 96,
    }
    const input: ProfileParserGridInput = { sheet: 'Rüstkosten EU', grid: gridFromCells(cells) }
    const table = locateVolumeBandLookupTable(input)
    expect(table).not.toBeNull()
    expect(table!.kind).toBe('volumeBand')
    expect(table!.values.band_1_upperBound).toBe(1000)
    expect(table!.values.band_1_lotCount).toBe(12)
    expect(table!.values.band_5_upperBound).toBeNull()
    expect(table!.values.band_5_lotCount).toBe(96)
    expect(table!.label).toContain('Jahresvolumen')
  })

  it('returns null when no header pair matches (never a false positive from unrelated text)', () => {
    const input: ProfileParserGridInput = { sheet: 'Rüstkosten EU', grid: gridFromCells({ A1: 'Prozessschritt', B1: 'Zykluszeit' }) }
    expect(locateVolumeBandLookupTable(input)).toBeNull()
  })
})

// ── locateManufacturingSummaryRow ────────────────────────────────────────

describe('locateManufacturingSummaryRow', () => {
  it('finds the DE-labeled Fertigungskosten row regardless of its physical row number', () => {
    const input = buildSummaryGrid(1, 16, {})
    expect(locateManufacturingSummaryRow(input)).toBe(16)
  })

  it('finds the EN-labeled Manufacturing cost row via the same anchor', () => {
    const cells: Record<string, unknown> = { B14: 'Manufacturing cost' }
    const input: ProfileParserGridInput = { sheet: 'Zusammenfassung', grid: gridFromCells(cells) }
    expect(locateManufacturingSummaryRow(input)).toBe(14)
  })

  it('returns null when no row matches (never guesses a row number)', () => {
    const input: ProfileParserGridInput = { sheet: 'Zusammenfassung', grid: gridFromCells({ B14: 'Materialkosten' }) }
    expect(locateManufacturingSummaryRow(input)).toBeNull()
  })
})

// ── locateSummaryMoneyRow (KAR-951 + KAR-951 F1/F6 fixes) ──────────────────
//
// Also the regression proof for the F6 cleanup: locateManufacturingSummaryRow
// and locateSummaryMoneyRow now share one label-anchored row scanner
// (`locateLabelAnchoredRow`) instead of two duplicated loops — the two
// describe blocks above/below exercising both entry points with their own
// distinct label sets is exactly what would catch a scanner that silently
// broke one call site while "fixing" the other.

describe('locateSummaryMoneyRow', () => {
  it('finds the "ANGEBOTSBASISPREIS inkl. Umlage" row for the offerBasePriceInclAllocation kind (KAR-951 F1 fix) — a DIFFERENT row from plain offerBasePrice', () => {
    const input: ProfileParserGridInput = {
      sheet: 'Zusammenfassung',
      grid: gridFromCells({
        B23: 'ANGEBOTSBASISPREIS',
        B25: 'ANGEBOTSBASISPREIS inkl. Umlage',
      }),
    }
    expect(locateSummaryMoneyRow('offerBasePrice', input)).toBe(23)
    expect(locateSummaryMoneyRow('offerBasePriceInclAllocation', input)).toBe(25)
  })

  it('recognizes the EN synonym for offerBasePriceInclAllocation', () => {
    const input: ProfileParserGridInput = { sheet: 'Zusammenfassung', grid: gridFromCells({ B18: 'Offer base price incl. allocation' }) }
    expect(locateSummaryMoneyRow('offerBasePriceInclAllocation', input)).toBe(18)
  })

  it('finds the scrapMaterial/otherSurcharges/offerPrice rows via their own label anchors, regardless of physical row number', () => {
    const input: ProfileParserGridInput = {
      sheet: 'Zusammenfassung',
      grid: gridFromCells({
        B21: 'Ausschusskosten',
        B22: 'SUMME Overhead',
        B24: 'ANGEBOTSPREIS',
      }),
    }
    expect(locateSummaryMoneyRow('scrapMaterial', input)).toBe(21)
    expect(locateSummaryMoneyRow('otherSurcharges', input)).toBe(22)
    expect(locateSummaryMoneyRow('offerPrice', input)).toBe(24)
  })

  it('returns null when the kind has no matching row (never guesses)', () => {
    const input: ProfileParserGridInput = { sheet: 'Zusammenfassung', grid: gridFromCells({ B21: 'Materialkosten' }) }
    expect(locateSummaryMoneyRow('offerBasePriceInclAllocation', input)).toBeNull()
  })

  it('an intervening annotation cell further right can never mask the real label further left (same discipline locateManufacturingSummaryRow establishes)', () => {
    const input: ProfileParserGridInput = {
      sheet: 'Zusammenfassung',
      grid: gridFromCells({ G25: 'ANGEBOTSBASISPREIS inkl. Umlage', L25: 'Siehe Reiter LV Detail' }),
    }
    expect(locateSummaryMoneyRow('offerBasePriceInclAllocation', input, { colFrom: 20 })).toBe(25)
  })
})

// ── resolveVariantProfileBindings ────────────────────────────────────────

describe('resolveVariantProfileBindings — cellReference (10-analyse-ncar.md F/E-parallel)', () => {
  it('resolves each variant to its own hardcoded absolute cell reference', () => {
    const profiles = parseSharedCostProfileBlocks(buildVolumeBandBlocksGrid())
    const [bandA, bandB, bandC] = ['32', '33', '34'].map(
      (row) => profiles.find((p) => p.sourceReferences[0]!.cell === `${col(20)}${row}`)!,
    )
    const variantCols = [16, 17, 18] // Q, R, S
    const summary = buildSummaryGrid(1, 15, {
      [variantCols[0]!]: `Fertigungskosten!${col(20)}32`,
      [variantCols[1]!]: `Fertigungskosten!${col(20)}33`,
      [variantCols[2]!]: `Fertigungskosten!$${col(20)}$34`,
    })
    const variants: VariantColumnRef[] = [
      { variantId: 'v1', column: variantCols[0]! },
      { variantId: 'v2', column: variantCols[1]! },
      { variantId: 'v3', column: variantCols[2]! },
    ]
    const { bindings, warnings } = resolveVariantProfileBindings(summary, 15, variants, profiles)
    expect(bindings).toHaveLength(3)
    expect(bindings[0]).toMatchObject({ variantId: 'v1', profileId: bandA.profileId, bindingEvidence: { kind: 'cellReference' } })
    expect(bindings[1]).toMatchObject({ variantId: 'v2', profileId: bandB.profileId, bindingEvidence: { kind: 'cellReference' } })
    expect(bindings[2]).toMatchObject({ variantId: 'v3', profileId: bandC.profileId, bindingEvidence: { kind: 'cellReference' } })
    expect(warnings).toEqual([])
  })
})

describe('resolveVariantProfileBindings — all variants share one reference (10-analyse-nafta.md E)', () => {
  it('binds every variant to the SAME profile without treating that as an error', () => {
    const totalCol = 20
    const profileGrid: ProfileParserGridInput = {
      sheet: 'Fertigungskosten',
      grid: gridFromCells({ [`${col(totalCol)}29`]: 8.75 }),
      formulaGrid: formulaGridFromCells({ [`${col(totalCol)}29`]: `SUMIF($R13:$R15,$K29,${col(totalCol)}13:${col(totalCol)}15)` }),
    }
    const profiles = parseSharedCostProfileBlocks(profileGrid)
    expect(profiles).toHaveLength(1)

    const variantCols = [16, 17, 18, 19, 20]
    const formulas = Object.fromEntries(variantCols.map((c) => [c, `Fertigungskosten!$${col(totalCol)}$29`]))
    const summary = buildSummaryGrid(1, 16, formulas)
    const variants: VariantColumnRef[] = variantCols.map((c, i) => ({ variantId: `v${i + 1}`, column: c }))

    const { bindings, warnings } = resolveVariantProfileBindings(summary, 16, variants, profiles)
    expect(bindings).toHaveLength(5)
    expect(new Set(bindings.map((b) => b.profileId)).size).toBe(1)
    expect(bindings.every((b) => b.profileId === profiles[0]!.profileId)).toBe(true)
    expect(warnings).toEqual([])
  })
})

describe('resolveVariantProfileBindings — literal bindings', () => {
  const totalCol = 20
  function oneProfile(total: number, cell = `${col(totalCol)}55`): SharedCostProfile {
    return {
      profileId: `Fertigungskosten!${cell}`,
      kind: 'iShape',
      label: 'PP i-shape',
      sheet: 'Fertigungskosten',
      values: { totalPerUnit: total },
      formulaAndCachedValue: { formula: null, cachedValue: total },
      sourceReferences: [{ sheet: 'Fertigungskosten', cell, row: Number(cell.replace(/\D/g, '')) - 1, column: totalCol + 1 }],
      confidence: 0.65,
    }
  }

  it('matches an EXACT literal value to its profile total', () => {
    const profile = oneProfile(46.5)
    const summary = buildSummaryGrid(1, 16, {}, { 16: 46.5 })
    const { bindings, warnings } = resolveVariantProfileBindings(summary, 16, [{ variantId: 'v1', column: 16 }], [profile])
    expect(bindings).toHaveLength(1)
    expect(bindings[0]).toMatchObject({ variantId: 'v1', profileId: profile.profileId, bindingEvidence: { kind: 'literal', source: null } })
    expect(warnings).toEqual([])
  })

  it('matches a literal value WITHIN tolerance', () => {
    const profile = oneProfile(100)
    const summary = buildSummaryGrid(1, 16, {}, { 16: 100.005 })
    const { bindings, warnings } = resolveVariantProfileBindings(summary, 16, [{ variantId: 'v1', column: 16 }], [profile])
    expect(bindings).toHaveLength(1)
    expect(bindings[0]!.bindingEvidence.kind).toBe('literal')
    expect(warnings).toEqual([])
  })

  it('does NOT match a literal value OUTSIDE tolerance — unbound + warning, never guessed', () => {
    const profile = oneProfile(100)
    const summary = buildSummaryGrid(1, 16, {}, { 16: 100.02 })
    const { bindings, warnings } = resolveVariantProfileBindings(summary, 16, [{ variantId: 'v1', column: 16 }], [profile])
    expect(bindings).toEqual([])
    expect(warnings).toHaveLength(1)
    expect(warnings[0]!.code).toBe('variant_profile_binding_unresolved')
  })

  it('reports "kein Match" as unbound + warning (never guessed) when the literal matches NO profile at all', () => {
    const profile = oneProfile(46.5)
    const summary = buildSummaryGrid(1, 16, {}, { 16: 999 })
    const { bindings, warnings } = resolveVariantProfileBindings(summary, 16, [{ variantId: 'v1', column: 16 }], [profile])
    expect(bindings).toEqual([])
    expect(warnings).toHaveLength(1)
    expect(warnings[0]!.code).toBe('variant_profile_binding_unresolved')
    expect(warnings[0]!.message).toContain('v1')
  })

  it('treats two profiles sharing the same total value as ambiguous — never silently picks one', () => {
    const profileA = oneProfile(46.5, `${col(totalCol)}54`)
    const profileB = oneProfile(46.5, `${col(totalCol)}55`)
    const summary = buildSummaryGrid(1, 16, {}, { 16: 46.5 })
    const { bindings, warnings } = resolveVariantProfileBindings(summary, 16, [{ variantId: 'v1', column: 16 }], [profileA, profileB])
    expect(bindings).toEqual([])
    expect(warnings.some((w) => w.code === 'ambiguous_literal_profile_binding')).toBe(true)
  })

  // KAR-932 adversarial review Zusatzpunkt (b) — "Literal-Bindung Toleranz-
  // Kollision": two DIFFERENT profile totals both fall within tolerance of
  // the same literal value — pick the CLOSEST one deterministically instead
  // of flagging every within-tolerance collision as ambiguous.
  it('picks the CLOSEST profile total deterministically when two DIFFERENT totals both fall within tolerance', () => {
    const closer = oneProfile(100.0, `${col(totalCol)}54`) // |100.003 - 100.0|   = 0.003
    const farther = oneProfile(99.999, `${col(totalCol)}55`) // |100.003 - 99.999| = 0.004
    const summary = buildSummaryGrid(1, 16, {}, { 16: 100.003 })
    const { bindings, warnings } = resolveVariantProfileBindings(summary, 16, [{ variantId: 'v1', column: 16 }], [closer, farther])
    expect(bindings).toHaveLength(1)
    expect(bindings[0]).toMatchObject({ variantId: 'v1', profileId: closer.profileId, bindingEvidence: { kind: 'literal' } })
    expect(warnings).toEqual([])
  })

})

describe('resolveVariantProfileBindings — live formula (10-analyse-clarwe-eu.md E/F additive pattern)', () => {
  it('classifies an additive two-sheet formula as a live "formula" binding, matched against the referenced profile', () => {
    const lvDetail: ProfileParserGridInput = {
      sheet: 'LV Detail EU',
      grid: gridFromCells({ [`${col(20)}52`]: 31.15 }),
      formulaGrid: formulaGridFromCells({ [`${col(20)}52`]: `SUMIF($R12:$R50,"EUR",${col(20)}12:${col(20)}50)` }),
    }
    const profiles = parseSharedCostProfileBlocks(lvDetail)
    expect(profiles).toHaveLength(1)

    const summary = buildSummaryGrid(1, 15, { 16: `'LV Detail EU'!$${col(20)}$52+'Rüstkosten EU'!C15` })
    const { bindings, warnings } = resolveVariantProfileBindings(summary, 15, [{ variantId: 'v1', column: 16 }], profiles)
    expect(bindings).toHaveLength(1)
    expect(bindings[0]).toMatchObject({ variantId: 'v1', profileId: profiles[0]!.profileId, bindingEvidence: { kind: 'formula' } })
    expect(warnings).toEqual([])
  })

  it('reports unbound + warning when a live multi-reference formula matches NO known profile', () => {
    const summary = buildSummaryGrid(1, 15, { 16: `Zusammenfassung!A1+Zusammenfassung!A2` })
    const { bindings, warnings } = resolveVariantProfileBindings(summary, 15, [{ variantId: 'v1', column: 16 }], [])
    expect(bindings).toEqual([])
    expect(warnings.some((w) => w.code === 'variant_profile_binding_unresolved')).toBe(true)
  })
})

describe('resolveVariantProfileBindings — direct cell reference that matches no known profile', () => {
  it('is unbound + warned rather than fabricating a profileId', () => {
    const summary = buildSummaryGrid(1, 15, { 16: `Fertigungskosten!${col(20)}99` })
    const { bindings, warnings } = resolveVariantProfileBindings(summary, 15, [{ variantId: 'v1', column: 16 }], [])
    expect(bindings).toEqual([])
    expect(warnings).toHaveLength(1)
    expect(warnings[0]!.code).toBe('variant_profile_binding_unresolved')
  })
})

describe('resolveVariantProfileBindings — SHARED_FORMULA_UNRESOLVED', () => {
  it('is unbound + warned, never guessed as literal or reference', () => {
    const summary: ProfileParserGridInput = {
      sheet: 'Zusammenfassung',
      grid: gridFromCells({ B15: 'Fertigungskosten' }),
      formulaGrid: formulaGridFromCells({ [`${col(16)}15`]: SHARED_FORMULA_UNRESOLVED }),
    }
    const { bindings, warnings } = resolveVariantProfileBindings(summary, 15, [{ variantId: 'v1', column: 16 }], [])
    expect(bindings).toEqual([])
    expect(warnings).toHaveLength(1)
    expect(warnings[0]!.code).toBe('variant_profile_binding_unresolved')
    expect(warnings[0]!.message).toMatch(/shared-formula-slave/i)
  })
})

// ── reconcileProfileToSummary ────────────────────────────────────────────

describe('reconcileProfileToSummary', () => {
  const totalCol = 20
  const profile: SharedCostProfile = {
    profileId: `Fertigungskosten!${col(totalCol)}32`,
    kind: 'volumeBand',
    label: '> 50.000 Stk/a',
    sheet: 'Fertigungskosten',
    values: { threshold: 50000 },
    formulaAndCachedValue: { formula: '=SUMIF(...)', cachedValue: 12.5 },
    sourceReferences: [{ sheet: 'Fertigungskosten', cell: `${col(totalCol)}32`, row: 31, column: totalCol + 1 }],
    confidence: 0.85,
  }

  it('reports matches:true when the Summary value equals the bound profile total', () => {
    const summary = buildSummaryGrid(1, 15, {}, { 16: 12.5 })
    const bindings = [
      { variantId: 'v1', profileId: profile.profileId, bindingEvidence: { kind: 'cellReference' as const, source: null, detail: '' } },
    ]
    const result = reconcileProfileToSummary(summary, 15, [{ variantId: 'v1', column: 16 }], bindings, [profile])
    expect(result[0]).toMatchObject({ variantId: 'v1', matches: true })
  })

  it('reports matches:false when the Summary value diverges from the bound profile total (Rekonziliations-Abweichung)', () => {
    const summary = buildSummaryGrid(1, 15, {}, { 16: 999 })
    const bindings = [
      { variantId: 'v1', profileId: profile.profileId, bindingEvidence: { kind: 'cellReference' as const, source: null, detail: '' } },
    ]
    const result = reconcileProfileToSummary(summary, 15, [{ variantId: 'v1', column: 16 }], bindings, [profile])
    expect(result[0]).toMatchObject({ variantId: 'v1', matches: false })
  })

  it('reports matches:null for a "formula"-kind binding — an additive formula is not expected to equal the profile total 1:1', () => {
    const summary = buildSummaryGrid(1, 15, {}, { 16: 999 })
    const bindings = [
      { variantId: 'v1', profileId: profile.profileId, bindingEvidence: { kind: 'formula' as const, source: null, detail: '' } },
    ]
    const result = reconcileProfileToSummary(summary, 15, [{ variantId: 'v1', column: 16 }], bindings, [profile])
    expect(result[0]).toMatchObject({ variantId: 'v1', matches: null })
  })

  it('reports profileId:null/matches:null for an unbound variant', () => {
    const summary = buildSummaryGrid(1, 15, {}, { 16: 999 })
    const result = reconcileProfileToSummary(summary, 15, [{ variantId: 'v1', column: 16 }], [], [profile])
    expect(result[0]).toMatchObject({ variantId: 'v1', profileId: null, matches: null })
  })
})

// ── ExcelJS bridge: sharedCostProfileCandidateSheets / sharedCostProfilesFromWorkbook ──

function addSumifTotalCell(ws: ExcelJS.Worksheet, labelCell: string, label: string, totalCell: string, formula: string, result: number) {
  ws.getCell(labelCell).value = label
  ws.getCell(totalCell).value = { formula, result } as ExcelJS.CellValue
}

describe('sharedCostProfileCandidateSheets — candidate selection + orphan-hidden-sheet exclusion', () => {
  function buildWorkbook(): ExcelJS.Workbook {
    const wb = new ExcelJS.Workbook()
    const summary = wb.addWorksheet('Zusammenfassung')
    const manufacturing = wb.addWorksheet('Fertigungskosten')
    const material = wb.addWorksheet('Material')
    const lvDetail = wb.addWorksheet('LV Detail EU')
    const orphanSetup = wb.addWorksheet('Rüstkosten EU', { state: 'hidden' })

    // Summary references the name-matched Fertigungskosten sheet AND the
    // non-name-matched-but-lineage-reached "LV Detail EU" sheet, AND the
    // MATERIAL-module sheet (which must stay excluded despite being
    // lineage-reached, per module-sheet-names.ts's own MATERIAL alias).
    summary.getCell('B14').value = 'Materialkosten'
    summary.getCell('Q14').value = { formula: 'Material!N50', result: 10 } as ExcelJS.CellValue
    summary.getCell('B15').value = 'Fertigungskosten'
    summary.getCell('Q15').value = { formula: 'Fertigungskosten!U10', result: 12 } as ExcelJS.CellValue
    summary.getCell('R15').value = { formula: "'LV Detail EU'!C5", result: 8 } as ExcelJS.CellValue

    addSumifTotalCell(manufacturing, 'K10', 'PP l-shape', 'U10', 'SUMIF($R1:$R3,$K10,U1:U3)', 12)
    material.getCell('N50').value = 10
    lvDetail.getCell('C5').value = 8

    // Orphan: hidden, structurally profile-shaped, but NOTHING references it.
    addSumifTotalCell(orphanSetup, 'K5', 'PP l-shape', 'U5', 'SUMIF($R1:$R3,$K5,U1:U3)', 10)

    return wb
  }

  it('includes the name-matched Fertigungskosten sheet and the lineage-reached-but-unnamed LV Detail EU sheet', () => {
    const wb = buildWorkbook()
    const { included, orphanedHidden } = sharedCostProfileCandidateSheets(wb)
    expect(included).toContain('Fertigungskosten')
    expect(included).toContain('LV Detail EU')
    expect(orphanedHidden).toContain('Rüstkosten EU')
  })

  it('never includes the MATERIAL-module sheet even though it is lineage-reached', () => {
    const wb = buildWorkbook()
    const { included } = sharedCostProfileCandidateSheets(wb)
    expect(included).not.toContain('Material')
  })

  it('emits an orphaned_hidden_sheet warning for the excluded hidden sheet', () => {
    const wb = buildWorkbook()
    const { warnings } = sharedCostProfileCandidateSheets(wb)
    expect(warnings.some((w) => w.code === 'orphaned_hidden_sheet' && w.message.includes('Rüstkosten EU'))).toBe(true)
  })

  it('sharedCostProfilesFromWorkbook only extracts profiles from included sheets, never from the orphaned hidden one', () => {
    const wb = buildWorkbook()
    const result = sharedCostProfilesFromWorkbook(wb)
    expect(result.profiles.some((p) => p.sheet === 'Fertigungskosten')).toBe(true)
    expect(result.profiles.some((p) => p.sheet === 'Rüstkosten EU')).toBe(false)
    expect(result.orphanedHiddenSheets).toContain('Rüstkosten EU')
  })
})

describe('sharedCostProfileCandidateSheets — a referenced hidden sheet IS included', () => {
  it('includes a hidden sheet once formula-lineage shows it is transitively reached from Summary', () => {
    const wb = new ExcelJS.Workbook()
    const summary = wb.addWorksheet('Zusammenfassung')
    const hiddenButReferenced = wb.addWorksheet('Rüstkosten EU', { state: 'hidden' })

    summary.getCell('B15').value = 'Fertigungskosten'
    summary.getCell('Q15').value = { formula: "'Rüstkosten EU'!C15", result: 5 } as ExcelJS.CellValue
    addSumifTotalCell(hiddenButReferenced, 'K5', 'Rüstkosten', 'C15', 'SUMIF($R1:$R3,$K5,C1:C3)', 5)

    const { included, orphanedHidden } = sharedCostProfileCandidateSheets(wb)
    expect(included).toContain('Rüstkosten EU')
    expect(orphanedHidden).not.toContain('Rüstkosten EU')
  })
})

// ── KAR-932 adversarial review F3: a Multi-QAF workbook's shared MATERIAL/
// BOM sheet (10-analyse-clarwe-eu.md D/C.2: "BOM Detail EU" IS that file's
// material matrix) used to be admitted as a manufacturing-profile SOURCE
// sheet whenever it was lineage-reached but not name-matched — the
// NON_MANUFACTURING_MODULES MATERIAL exclusion only matches the substring
// "material" (module-sheet-names.ts), which "BOM Detail EU" does not
// contain. Every SUMPRODUCT-shaped row-total formula on its many material
// rows was then misread as an independent SharedCostProfile — the measured
// root cause of a large profile-count over-count on that real file. ───────
describe('sharedCostProfileCandidateSheets — Material-/BOM-Matrix domain exclusion (KAR-932 adversarial review F3)', () => {
  function buildBomWorkbook(): ExcelJS.Workbook {
    const wb = new ExcelJS.Workbook()
    const summary = wb.addWorksheet('Zusammenfassung')
    const bom = wb.addWorksheet('BOM Detail EU')

    summary.getCell('B14').value = 'Materialkosten'
    summary.getCell('Q14').value = { formula: "'BOM Detail EU'!U159", result: 100 } as ExcelJS.CellValue

    // Material-matrix header row (Positionsnummer + Teilebezeichnung), plus
    // several SUMPRODUCT-shaped per-row cost formulas — the exact shape
    // that used to be misread as independent manufacturing-profile Totals.
    bom.getCell('A1').value = 'Positionsnummer'
    bom.getCell('B1').value = 'Teilebezeichnung'
    for (let r = 2; r <= 5; r++) {
      bom.getCell(`A${r}`).value = `P${r}`
      bom.getCell(`B${r}`).value = `Bauteil ${r}`
      bom.getCell(`U${r}`).value = { formula: `SUMPRODUCT(C${r}:D${r},E${r}:F${r})`, result: r * 2 } as ExcelJS.CellValue
    }
    bom.getCell('U159').value = 100

    return wb
  }

  it('excludes a name-hinted BOM sheet from candidacy even though it is lineage-reached', () => {
    const wb = buildBomWorkbook()
    const { included } = sharedCostProfileCandidateSheets(wb)
    expect(included).not.toContain('BOM Detail EU')
  })

  it('sharedCostProfilesFromWorkbook extracts ZERO profiles from the excluded BOM sheet', () => {
    const wb = buildBomWorkbook()
    const result = sharedCostProfilesFromWorkbook(wb)
    expect(result.profiles.some((p) => p.sheet === 'BOM Detail EU')).toBe(false)
  })

  it('excludes a material-matrix sheet via STRUCTURAL header anchors alone, even with a tab name carrying no "bom"/"material" hint', () => {
    const wb = new ExcelJS.Workbook()
    const summary = wb.addWorksheet('Zusammenfassung')
    const matrix = wb.addWorksheet('Vergleich EU')
    summary.getCell('B14').value = 'Materialkosten'
    summary.getCell('Q14').value = { formula: "'Vergleich EU'!U50", result: 5 } as ExcelJS.CellValue
    matrix.getCell('A1').value = 'Position Number'
    matrix.getCell('B1').value = 'Parts Designation'
    matrix.getCell('C1').value = 'Imputed unit cost' // 3rd anchor — material-cost-specific, never present on a genuine manufacturing/process-step sheet.
    matrix.getCell('U50').value = { formula: 'SUMPRODUCT(C1:D1,E1:F1)', result: 5 } as ExcelJS.CellValue

    const { included } = sharedCostProfileCandidateSheets(wb)
    expect(included).not.toContain('Vergleich EU')
  })

  it('does NOT exclude a genuine manufacturing/process-step sheet that merely shares the position-number+component-label vocabulary (real-file correction: "LV Detail EU" carries "Positionsnummer Fertigungsschritt"+"Teilebenennung" columns but is NOT Material domain — the 3rd, material-cost-specific anchor is what disambiguates)', () => {
    const wb = new ExcelJS.Workbook()
    const summary = wb.addWorksheet('Zusammenfassung')
    const process = wb.addWorksheet('LV Detail EU')
    summary.getCell('B15').value = 'Fertigungskosten'
    summary.getCell('Q15').value = { formula: "'LV Detail EU'!U52", result: 31.15 } as ExcelJS.CellValue
    process.getCell('A1').value = 'Positionsnummer Fertigungsschritt'
    process.getCell('B1').value = 'Teilebenennung'
    process.getCell('C1').value = 'Maschinenstundensatz MSS [BW/h]' // manufacturing-specific, NOT a material-cost/quantity-factor anchor.
    addSumifTotalCell(process, 'K52', 'PP l-shape', 'U52', 'SUMIF($R1:$R3,$K52,U1:U3)', 31.15)

    const { included } = sharedCostProfileCandidateSheets(wb)
    expect(included).toContain('LV Detail EU')
  })

  it('a hidden Material-/BOM-domain sheet stays excluded even when it IS lineage-reached', () => {
    const wb = new ExcelJS.Workbook()
    const summary = wb.addWorksheet('Zusammenfassung')
    const hiddenBom = wb.addWorksheet('BOM Detail EU', { state: 'hidden' })
    summary.getCell('B14').value = 'Materialkosten'
    summary.getCell('Q14').value = { formula: "'BOM Detail EU'!U159", result: 100 } as ExcelJS.CellValue
    hiddenBom.getCell('A1').value = 'Positionsnummer'
    hiddenBom.getCell('B1').value = 'Teilebezeichnung'
    hiddenBom.getCell('U159').value = 100

    const { included, orphanedHidden } = sharedCostProfileCandidateSheets(wb)
    expect(included).not.toContain('BOM Detail EU')
    // Not a profile-shaped orphan either — it's material-domain, not a
    // manufacturing/setup-cost candidate at all (never reported as either).
    expect(orphanedHidden).not.toContain('BOM Detail EU')
  })
})

// ── KAR-932 adversarial review F1: sharedCostProfileCandidateSheets used to
// derive reachability SOLELY from graph.edges, ignoring buildColumnLineage's
// own fail-closed cap signals (scanIncompleteSheets/rangeTruncatedSheets/
// depthCappedSheets). A capped trace is NOT proof of "definitely no
// reference" — see profile-parser.ts's own `lineageIsCapped` doc comment.
// ───────────────────────────────────────────────────────────────────────
describe('sharedCostProfileCandidateSheets — fail-closed lineage-cap handling (KAR-932 adversarial review F1)', () => {
  it('reports a structurally profile-shaped, unreached HIDDEN sheet as "reachability unknown" — never a proven orphan — once a lineage guard/cap fired anywhere in the trace', () => {
    const wb = new ExcelJS.Workbook()
    const summary = wb.addWorksheet('Zusammenfassung')
    const filler = wb.addWorksheet('Fertigungskosten')
    const maybeOrphan = wb.addWorksheet('Rüstkosten EU', { state: 'hidden' })

    summary.getCell('B15').value = 'Fertigungskosten'
    summary.getCell('Q15').value = { formula: 'Fertigungskosten!U10', result: 12 } as ExcelJS.CellValue
    addSumifTotalCell(filler, 'K10', 'PP l-shape', 'U10', 'SUMIF($R1:$R3,$K10,U1:U3)', 12)
    // Filler formula cells (row 1) consume the overridden per-sheet cap of
    // 2 BEFORE the scan ever reaches row 10 — the guard trips on
    // "Fertigungskosten", a DIFFERENT sheet than the hidden one in
    // question (see `lineageIsCapped`'s doc comment: a whole-graph, not
    // per-sheet, signal is required for exactly this reason).
    filler.getCell('V1').value = { formula: '1+1', result: 2 } as ExcelJS.CellValue
    filler.getCell('W1').value = { formula: '2+2', result: 4 } as ExcelJS.CellValue
    filler.getCell('X1').value = { formula: '3+3', result: 6 } as ExcelJS.CellValue

    // Structurally profile-shaped hidden sheet with genuinely zero incoming
    // references — would be `orphaned_hidden_sheet` under a CLEAN trace
    // (see the sibling "a referenced hidden sheet IS included" / "candidate
    // selection + orphan-hidden-sheet exclusion" describe blocks above).
    addSumifTotalCell(maybeOrphan, 'K5', 'PP l-shape', 'U5', 'SUMIF($R1:$R3,$K5,U1:U3)', 10)

    const { included, orphanedHidden, warnings } = sharedCostProfileCandidateSheets(wb, { maxFormulaCellsPerSheet: 2 })

    expect(included).not.toContain('Rüstkosten EU')
    expect(orphanedHidden).not.toContain('Rüstkosten EU')
    expect(warnings.some((w) => w.code === 'hidden_sheet_reachability_unknown' && w.message.includes('Rüstkosten EU'))).toBe(true)
    expect(warnings.some((w) => w.code === 'orphaned_hidden_sheet')).toBe(false)
  })

  it('conservatively INCLUDES a structurally profile-shaped, unreached VISIBLE sheet (with a warning) once a lineage guard/cap fired — instead of silently dropping it', () => {
    const wb = new ExcelJS.Workbook()
    const summary = wb.addWorksheet('Zusammenfassung')
    const filler = wb.addWorksheet('Fertigungskosten')
    const maybeDropped = wb.addWorksheet('LV Detail EU') // visible, NOT name-matched, NOT referenced.

    summary.getCell('B15').value = 'Fertigungskosten'
    summary.getCell('Q15').value = { formula: 'Fertigungskosten!U10', result: 12 } as ExcelJS.CellValue
    addSumifTotalCell(filler, 'K10', 'PP l-shape', 'U10', 'SUMIF($R1:$R3,$K10,U1:U3)', 12)
    filler.getCell('V1').value = { formula: '1+1', result: 2 } as ExcelJS.CellValue
    filler.getCell('W1').value = { formula: '2+2', result: 4 } as ExcelJS.CellValue
    filler.getCell('X1').value = { formula: '3+3', result: 6 } as ExcelJS.CellValue

    addSumifTotalCell(maybeDropped, 'K5', 'PP l-shape', 'U5', 'SUMIF($R1:$R3,$K5,U1:U3)', 10)

    // Baseline (uncapped): confirms this sheet is normally EXCLUDED — the
    // conservative-inclusion behavior below is genuinely conditional on the
    // cap, not a blanket change.
    const uncapped = sharedCostProfileCandidateSheets(wb)
    expect(uncapped.included).not.toContain('LV Detail EU')

    const capped = sharedCostProfileCandidateSheets(wb, { maxFormulaCellsPerSheet: 2 })
    expect(capped.included).toContain('LV Detail EU')
    expect(
      capped.warnings.some((w) => w.code === 'lineage_reachability_unknown_conservative_include' && w.message.includes('LV Detail EU')),
    ).toBe(true)
  })
})

// Sanity: the default column-scan-from constant is distinct from the
// Summary-sheet variant-band constant this module reuses for row-label
// lookups — proves the two scan targets are intentionally different
// (process-row tables start at column A, not column Q).
describe('scan-bound constants', () => {
  it('DEFAULT_PROFILE_COL_FROM starts at column A, unlike the Summary variant band', () => {
    expect(DEFAULT_PROFILE_COL_FROM).toBe(0)
    expect(SUMMARY_VARIANT_BAND_COL_FROM).toBeGreaterThan(0)
  })
})