// Tests for the MATERIAL sheet parser (KAR-897 / P1.6). Fixtures are entirely
// synthetic (invented field values, invented material/rohstoff names) — no
// real Brose/Kiekert/Autoliv/BMW data or files, per task instruction. // allow-customer-string

import { describe, it, expect } from 'vitest'
import ExcelJS from 'exceljs'
import {
  isMaterialSheetName,
  findMaterialWorksheet,
  findMaterialHeaderRow,
  matchMaterialHeaderColumn,
  parseMaterialWorksheet,
  parseMaterialSheet,
  materialRowsForReconciliation,
  materialRowsFromPersistedMeta,
  materialParseMetaToPlausibilityIssue,
  groupMaterialRowsByPosition,
  materialRowLogicalKey,
  CORE_MATERIAL_FIELD_KEYS,
  MATERIAL_UNIT_RAW_MATERIAL_RISK,
  MATERIAL_UNIT_PURCHASED_PART,
  type MaterialRow,
  type MaterialRowValues,
} from '../material-parser'
import { evaluateReconciliation, type ReconciliationInput } from '../reconciliation'
import { metricsParse } from './summary-fixtures'

// Header labels in canonical-fields.ts MATERIAL_FIELDS declaration order
// (Leitfaden [18]-[20]). Column 7 (index 7) intentionally uses the genuine
// "Mengeneinheit" label; column 15 (index 15) intentionally ALSO uses the
// literal string "Mengeneinheit" instead of "Materialgemeinkosten MGK" — this
// reproduces the documented BMW source-data label-copy bug (canonical- // allow-customer-string
// fields.ts material_mengeneinheit_label_duplicate / fehlerreport-analyse
// §3.3 row #36) so the collision-disambiguation path is exercised for real,
// not just asserted in isolation.
const FULL_MATERIAL_HEADERS = [
  'Positionsnummer Fertigungsschritt', // 0 positionNumber
  'Teilebenennung', // 1 partDesignation
  'Benennung Rohmaterial / Kaufteil', // 2 materialDesignation
  'Lieferant', // 3 supplier
  'Technische Funktion des Bauteiles', // 4 technicalFunction
  'Ursprungsland', // 5 countryOfOrigin
  'HTS-Code', // 6 htsCode
  'Mengeneinheit', // 7 unitOfMeasure (genuine)
  'Beschaffungswährung BW', // 8 procurementCurrency
  'Kalkulatorisch angesetzte Materialkosten pro Mengeneinheit [BW]', // 9 costPerUnitBw
  'Angebotswährung AW', // 10 quotationCurrency
  'Wechselkurs [AW/BW]', // 11 exchangeRate
  'Verpackungskosten je Mengeneinheit [AW]', // 12 packagingCostPerUnit
  'Transportkosten je Mengeneinheit [AW]', // 13 transportCostPerUnit
  'Zollkosten, CBAM-Kosten je Mengeneinheit [AW]', // 14 customsCbamCostPerUnit
  'Mengeneinheit', // 15 overheadCost (BMW label-copy bug, should be "Materialgemeinkosten MGK") // allow-customer-string
  'Kalkulatorische Materialkosten pro Mengeneinheit [AW]', // 16 costPerUnitAw
  'Bezugsmenge [Einheiten] (nur Rohmaterial)', // 17 referenceQuantity
  'Nettomenge [Einheiten] (nur Rohmaterial)', // 18 netQuantity
  'Kalkulatorisch angesetzte Rückvergütung [AW] (nur Rohmaterial)', // 19 rebate
  'Anzahl pro Angebotsteil', // 20 quantityPerQuotedPart
  'Kalkulatorische Materialkosten [AW]', // 21 materialCost
  'Materialausschuss [%]', // 22 scrapRate
  'Ausschusskosten Material [AW]', // 23 scrapCost
  'Rohstoffbezeichnung', // 24 rawMaterialDesignation
  'Bezugsgewicht [kg]', // 25 referenceWeight
  'Rohstoffnotierung Ro [AW/kg]', // 26 rawMaterialQuotation
  'Rohstoffzuschlag RoZ0 [AW]', // 27 rawMaterialSurcharge
]

async function workbookWithSheets(sheets: Array<{ name: string; rows: unknown[][] }>): Promise<ExcelJS.Workbook> {
  const wb = new ExcelJS.Workbook()
  for (const { name, rows } of sheets) {
    const ws = wb.addWorksheet(name)
    rows.forEach((row, i) => {
      // ExcelJS: values[0] -> column A, so row[0] (positionNumber) lands on
      // column A directly (no leading placeholder, unlike the
      // qaf-parser.ts tests, which deliberately offset by one column).
      ws.getRow(i + 1).values = row as ExcelJS.CellValue[]
    })
  }
  return wb
}

/** A clean, fully-mapped MATERIAL sheet with 3 logical positions:
 *  - Pos 1: single-row Kaufteil (no raw-material-risk).
 *  - Pos 2: RMR fixed/variable split (Leitfaden [17]).
 *  - Pos 3: a legitimate multi-row raw-material breakdown WITHOUT the RMR
 *    Mengeneinheit marker (e.g. base price / value-added rows) — must NOT be
 *    flagged as an RMR split.
 */
function cleanMaterialRows(): unknown[][] {
  return [
    FULL_MATERIAL_HEADERS,
    // Pos 1 — Kaufteil, single row.
    [
      '1', 'Gehaeuse_Kunststoff', 'Kunststoffgehaeuse PA6', 'ACME Plastics', 'Gehaeuse', 'DE', '39269097',
      MATERIAL_UNIT_PURCHASED_PART, 'EUR', 0.8, 'EUR', 1, 0.02, 0.01, 0, 0.05, 0.88, '', '', '', 2, 1.76, 3, 0.05,
      '', '', '', '',
    ],
    // Pos 2 — RMR split, fixed share.
    [
      '2', 'Gussteil_Alu', 'AlSi10MgMn', 'Testlieferant Guss', 'Traeger', 'DE', '76169990',
      'kg', 'EUR', 1.2, 'EUR', 1, 0.03, 0.02, 0, 0.1, 1.35, 0.5, 0.48, 0, 1, 0.68, 4, 0.028,
      '', '', '', '',
    ],
    // Pos 2 — RMR split, variable share.
    [
      '2', 'Gussteil_Alu', 'AlSi10MgMn', '', '', '', '',
      MATERIAL_UNIT_RAW_MATERIAL_RISK, '', '', '', '', '', '', '', '', '', '', '', '', 1, '', '', '',
      'AL LME TEST - AMLI', 0.5, 2, 1,
    ],
    // Pos 3 — legitimate multi-row breakdown, no RMR marker on either row.
    [
      '3', 'Coil_Stahl', 'DX51D Base price', 'Testlieferant Coil', 'Blechteil', 'DE', '72091700',
      'kg', 'EUR', 0.6, 'EUR', 1, 0.01, 0.01, 0, 0.02, 0.64, 2, 1.9, 0, 1, 1.28, 2, 0.026,
      '', '', '', '',
    ],
    [
      '3', 'Coil_Stahl', 'DX51D Value added', '', '', '', '',
      'kg', 'EUR', 0.15, 'EUR', 1, '', '', 0, '', 0.15, 2, 1.9, 0, 1, 0.3, '', '',
      '', '', '', '',
    ],
  ]
}

async function cleanMaterialWorksheet(): Promise<ExcelJS.Worksheet> {
  const wb = await workbookWithSheets([{ name: 'MATERIAL', rows: cleanMaterialRows() }])
  return wb.worksheets[0]
}

function blankMaterialRow(overrides: Partial<MaterialRowValues>): MaterialRow {
  const base: MaterialRowValues = {
    positionNumber: '',
    partDesignation: '',
    materialDesignation: '',
    supplier: '',
    technicalFunction: '',
    countryOfOrigin: '',
    htsCode: '',
    unitOfMeasure: '',
    procurementCurrency: '',
    costPerUnitBw: null,
    quotationCurrency: '',
    exchangeRate: null,
    packagingCostPerUnit: null,
    transportCostPerUnit: null,
    customsCbamCostPerUnit: null,
    overheadCost: null,
    costPerUnitAw: null,
    referenceQuantity: null,
    netQuantity: null,
    rebate: null,
    quantityPerQuotedPart: null,
    materialCost: null,
    scrapRate: null,
    scrapCost: null,
    rawMaterialDesignation: '',
    referenceWeight: null,
    rawMaterialQuotation: null,
    rawMaterialSurcharge: null,
    packagingCostOffer: null, // KAR-910
  }
  return { ...base, ...overrides, sourceCells: {}, normalized: {}, rawText: {} }
}

describe('isMaterialSheetName', () => {
  it('matches the MATERIAL sheet name (DE/EN identical per Leitfaden)', () => {
    expect(isMaterialSheetName('MATERIAL')).toBe(true)
    expect(isMaterialSheetName('material')).toBe(true)
    expect(isMaterialSheetName('  Material  ')).toBe(true)
  })

  it('does not match the Fertigungskosten/Manufacturing sheet', () => {
    expect(isMaterialSheetName('Fertigungskosten')).toBe(false)
    expect(isMaterialSheetName('Manufacturing costs')).toBe(false)
  })

  it('does not match the Summary sheet', () => {
    expect(isMaterialSheetName('Zusammenfassung')).toBe(false)
    expect(isMaterialSheetName('SUMMARY')).toBe(false)
  })

  it('excludes the separate RAW MATERIAL RISK register sheet', () => {
    expect(isMaterialSheetName('RAW MATERIAL RISK')).toBe(false)
    expect(isMaterialSheetName('Raw Material Risks')).toBe(false)
  })
})

describe('findMaterialWorksheet', () => {
  it('finds the MATERIAL sheet among several', async () => {
    const wb = await workbookWithSheets([
      { name: 'SUMMARY', rows: [['x']] },
      { name: 'MATERIAL', rows: [['y']] },
      { name: 'MANUFACTURING COSTS', rows: [['z']] },
    ])
    const ws = findMaterialWorksheet(wb)
    expect(ws?.name).toBe('MATERIAL')
  })

  it('returns null when no MATERIAL sheet is present (additive gate)', async () => {
    const wb = await workbookWithSheets([{ name: 'SUMMARY', rows: [['x']] }])
    expect(findMaterialWorksheet(wb)).toBeNull()
  })
})

describe('matchMaterialHeaderColumn', () => {
  it('exact DE match', async () => {
    const m = await matchMaterialHeaderColumn('Positionsnummer Fertigungsschritt')
    expect(m?.key).toBe('positionNumber')
    expect(m?.confidence).toBe(1)
  })

  it('exact match for "Benennung Rohmaterial / Kaufteil"', async () => {
    const m = await matchMaterialHeaderColumn('Benennung Rohmaterial / Kaufteil')
    expect(m?.key).toBe('materialDesignation')
  })

  it('normalized (umlaut/whitespace) match', async () => {
    const m = await matchMaterialHeaderColumn('Ursprungsland  ')
    expect(m?.key).toBe('countryOfOrigin')
  })

  it('returns null for an unknown header', async () => {
    expect(await matchMaterialHeaderColumn('Voellig unbekannte Spalte')).toBeNull()
  })

  it('returns null for an empty header', async () => {
    expect(await matchMaterialHeaderColumn('')).toBeNull()
  })
})

describe('findMaterialHeaderRow', () => {
  it('finds the header row', async () => {
    const rows = cleanMaterialRows()
    expect(await findMaterialHeaderRow(rows)).toBe(0)
  })

  it('returns null when fewer than 5 headers match', async () => {
    const rows = [['Positionsnummer Fertigungsschritt', 'Teilebenennung', 'random', 'cells']]
    expect(await findMaterialHeaderRow(rows)).toBeNull()
  })

  it('returns null on empty input', async () => {
    expect(await findMaterialHeaderRow([])).toBeNull()
  })
})

describe('parseMaterialWorksheet — clean sheet', () => {
  it('parses all rows and resolves the "Mengeneinheit" label collision by column order', async () => {
    const ws = await cleanMaterialWorksheet()
    const result = await parseMaterialWorksheet(ws)

    expect(result.coreFieldsFound).toBe(true)
    expect(result).toHaveLength(5)
    // 28 headers, all resolved (including the disambiguated duplicate).
    expect(result.mappedFieldCount).toBe(28)
    expect(result.unmappedHeaders).toEqual([])
    expect(result.parseConfidence).toBe(1)

    const pos1 = result[0]
    expect(pos1.positionNumber).toBe('1')
    expect(pos1.materialDesignation).toBe('Kunststoffgehaeuse PA6')
    expect(pos1.unitOfMeasure).toBe(MATERIAL_UNIT_PURCHASED_PART)
    // Column 7 -> unitOfMeasure, column 15 -> overheadCost, despite BOTH // allow-customer-string
    // header cells reading "Mengeneinheit" verbatim.
    expect(pos1.overheadCost).toBe(0.05)
    expect(pos1.materialCost).toBe(1.76)
  })

  it('keeps provenance (sourceCells/normalized/rawText) from day one', async () => {
    const ws = await cleanMaterialWorksheet()
    const result = await parseMaterialWorksheet(ws)
    const pos1 = result[0]
    expect(pos1.sourceCells.positionNumber).toBe('MATERIAL!A2')
    expect(pos1.sourceCells.materialDesignation).toBe('MATERIAL!C2')
    expect(pos1.normalized.materialCost).toBe(1.76)
    expect(pos1.rawText).toEqual({})
  })

  it('parses the RMR split rows as two independent rows sharing one Positionsnummer', async () => {
    const ws = await cleanMaterialWorksheet()
    const result = await parseMaterialWorksheet(ws)
    const pos2Rows = result.filter((r) => r.positionNumber === '2')
    expect(pos2Rows).toHaveLength(2)
    const fixed = pos2Rows.find((r) => r.unitOfMeasure !== MATERIAL_UNIT_RAW_MATERIAL_RISK)
    const variable = pos2Rows.find((r) => r.unitOfMeasure === MATERIAL_UNIT_RAW_MATERIAL_RISK)
    expect(fixed?.overheadCost).toBe(0.1)
    expect(variable?.rawMaterialDesignation).toBe('AL LME TEST - AMLI')
    expect(variable?.rawMaterialSurcharge).toBe(1)
    // Fixed and variable share are NOT the same object / do not overwrite
    // each other — both preserved with their own field values.
    expect(fixed?.materialCost).not.toBe(variable?.materialCost)
  })

  it('captures rawText for an explicit not-applicable marker', async () => {
    const rows = cleanMaterialRows()
    rows[1] = [...rows[1]]
    rows[1][22] = 'n.a.' // Materialausschuss [%] column on Pos 1
    const wb = await workbookWithSheets([{ name: 'MATERIAL', rows }])
    const result = await parseMaterialWorksheet(wb.worksheets[0])
    expect(result[0].scrapRate).toBeNull()
    expect(result[0].rawText.scrapRate).toBe('n.a.')
  })
})

/** A real MATERIAL sheet whose header is too degraded to trust (Benennung
 * Rohmaterial/Kaufteil + Kalkulatorische Materialkosten [AW] columns
 * dropped) — coreFieldsFound stays false even though the sheet genuinely
 * exists and may carry real cost data the parser simply could not locate.
 * Shared by the parser-level and reconciliation-pipeline degradation tests
 * below (adversarial-review finding, KAR-898 follow-up, point 2). */
function degradedMaterialRows(): unknown[][] {
  const rows = cleanMaterialRows()
  const headers = [...rows[0]]
  const materialDesignationIdx = headers.indexOf('Benennung Rohmaterial / Kaufteil')
  const materialCostIdx = headers.indexOf('Kalkulatorische Materialkosten [AW]')
  // Drop both non-position core columns (drop higher index first to keep
  // the other index valid) — Positionsnummer stays intact.
  return rows.map((row) => {
    const r = [...row]
    r.splice(Math.max(materialDesignationIdx, materialCostIdx), 1)
    r.splice(Math.min(materialDesignationIdx, materialCostIdx), 1)
    return r
  })
}

describe('parseMaterialWorksheet — degradation path', () => {
  // KAR-958/P2 (gate-audit.md B5, coreFieldsFound-Resilienz): degradedMaterialRows()
  // drops BOTH materialDesignation (the row-identity field the row-push
  // guard requires) and materialCost — since the identity field itself is
  // gone, no row can be identified even though ~26 other columns still
  // mapped fine (well above MIN_SIGNAL_MAPPED_COLUMNS), so `rows` stays `[]`
  // via the SAME row-push guard an intact parse uses — not via an
  // unconditional early return anymore. parseConfidence/mappedFieldCount
  // now DO reflect the columns that mapped (this is the resilience fix:
  // real signal is no longer thrown away just because 2 of 3 core fields
  // are missing).
  it('does NOT throw when the identity field is missing; rows stay empty via the row-push guard, but the mapped-column signal survives', async () => {
    const wb = await workbookWithSheets([{ name: 'MATERIAL', rows: degradedMaterialRows() }])
    const result = await parseMaterialWorksheet(wb.worksheets[0])

    expect(result.coreFieldsFound).toBe(false)
    expect(result).toHaveLength(0)
    expect(result.mappedFieldCount).toBeGreaterThan(2)
    expect(result.parseConfidence).toBeGreaterThan(0)
    expect(result.degradation).toEqual({
      facet: 'material',
      reason: 'PARSE_FAILED',
      sheet: 'MATERIAL',
      message: expect.stringContaining('Benennung Rohmaterial / Kaufteil'),
    })
  })

  // The actual gate-audit B5 bug: previously ANY missing core field discarded
  // every already-mapped column, even when the row-identity field itself
  // (materialDesignation) was intact and rows were perfectly identifiable —
  // only materialCost is missing here.
  it('extracts rows when only a NON-identity core field is missing (materialCost), instead of discarding everything', async () => {
    const rows = cleanMaterialRows()
    const headers = [...rows[0]]
    const materialCostIdx = headers.indexOf('Kalkulatorische Materialkosten [AW]')
    const withoutMaterialCost = rows.map((row) => {
      const r = [...row]
      r.splice(materialCostIdx, 1)
      return r
    })
    const wb = await workbookWithSheets([{ name: 'MATERIAL', rows: withoutMaterialCost }])
    const result = await parseMaterialWorksheet(wb.worksheets[0])

    expect(result.coreFieldsFound).toBe(false)
    expect(result.length).toBeGreaterThan(0) // the fix: rows survive
    expect(result.every((r) => r.materialDesignation !== '')).toBe(true)
    expect(result.every((r) => r.materialCost === null)).toBe(true) // the missing field itself stays null
    expect(result.degradation?.reason).toBe('PARSE_FAILED')
    expect(result.degradation?.message).toContain('Kalkulatorische Materialkosten [AW]')
  })

  // MIN_SIGNAL_MAPPED_COLUMNS floor (task instruction: "Vorsicht bei WIRKLICH
  // leeren/fremden Sheets — Mindest-Signal definieren, z.B. >=2 erkannte
  // Spalten-Header, und im Test verankern"): a sheet that merely matches
  // isMaterialSheetName's substring but carries at most 1 recognizable
  // MATERIAL column must NOT be treated as a degraded-but-real MATERIAL
  // sheet — same empty/flagged result as before this PR.
  it('a genuinely foreign/near-empty sheet (fewer than MIN_SIGNAL_MAPPED_COLUMNS matches) stays empty, no degradation', async () => {
    const wb = await workbookWithSheets([
      { name: 'MATERIAL', rows: [['Positionsnummer Fertigungsschritt', 'Voellig', 'Andere', 'Spalten'], ['1', 'a', 'b', 'c']] },
    ])
    const result = await parseMaterialWorksheet(wb.worksheets[0])
    expect(result.coreFieldsFound).toBe(false)
    expect(result).toHaveLength(0)
    expect(result.parseConfidence).toBe(0)
    expect(result.degradation).toBeUndefined()
  })

  it('CORE_MATERIAL_FIELD_KEYS is exactly position + material designation + material cost', () => {
    expect(CORE_MATERIAL_FIELD_KEYS).toEqual(['positionNumber', 'materialDesignation', 'materialCost'])
  })

  it('returns an empty, non-throwing result for a sheet with only 1 row (mirrors qaf-parser.ts\'s grid.length<2 guard)', async () => {
    const wb = await workbookWithSheets([{ name: 'MATERIAL', rows: [FULL_MATERIAL_HEADERS] }])
    const result = await parseMaterialWorksheet(wb.worksheets[0])
    expect(result).toHaveLength(0)
    expect(result.coreFieldsFound).toBe(false)
  })
})

describe('parseMaterialSheet', () => {
  it('returns null when the workbook has no MATERIAL sheet (files without it stay unaffected)', async () => {
    const wb = await workbookWithSheets([{ name: 'SUMMARY', rows: [['x']] }])
    expect(await parseMaterialSheet(wb)).toBeNull()
  })

  it('locates and parses the MATERIAL sheet when present alongside other sheets', async () => {
    const wb = await workbookWithSheets([
      { name: 'SUMMARY', rows: [['x']] },
      { name: 'MATERIAL', rows: cleanMaterialRows() },
    ])
    const result = await parseMaterialSheet(wb)
    expect(result).not.toBeNull()
    expect(result).toHaveLength(5)
  })

  // KAR-927 (Multi-QAF-Programm P0.2): findMaterialWorksheet's `.find()`
  // silently dropped every candidate sheet after the first. Ergebnis-neutral
  // fix — the FIRST matching sheet is still the one parsed; only
  // ignoredCandidateSheets is new.
  it('a single MATERIAL sheet reports no ignored candidates (standard QAF — no spam)', async () => {
    const wb = await workbookWithSheets([{ name: 'MATERIAL', rows: cleanMaterialRows() }])
    const result = await parseMaterialSheet(wb)
    expect(result?.ignoredCandidateSheets).toBeUndefined()
  })

  // (b) two genuine MATERIAL data sheets — the ignored one is a full,
  // clean duplicate, so the cheap scan finds its materialDesignation header
  // and reports plausibleData:true (a message fires, see
  // materialParseMetaToPlausibilityIssue below).
  it('(b) two sheets whose names both match isMaterialSheetName: parses the first, reports the rest as ignored with plausibleData:true', async () => {
    const wb = new ExcelJS.Workbook()
    const s1 = wb.addWorksheet('MATERIAL')
    cleanMaterialRows().forEach((row, i) => {
      s1.getRow(i + 1).values = row as ExcelJS.CellValue[]
    })
    const s2 = wb.addWorksheet('Material ')
    cleanMaterialRows().forEach((row, i) => {
      s2.getRow(i + 1).values = row as ExcelJS.CellValue[]
    })
    const result = await parseMaterialSheet(wb)
    expect(result).not.toBeNull()
    expect(result).toHaveLength(5) // parsed from the FIRST sheet, unchanged
    expect(result?.ignoredCandidateSheets).toEqual([{ name: 'Material ', plausibleData: true }])
  })

  // (a) a name-matching REFERENCE tab (e.g. a Dropdown-Werteliste, mirroring
  // the real-corpus SBM_Matrix/SBM_Dropdown finding for MATERIAL) never
  // carries a materialDesignation header — visible in the meta, but
  // plausibleData:false, so no user-facing message (anti-spam preserved).
  it('(a) a name-matching reference/dropdown tab without the core label reports plausibleData:false and no message', async () => {
    const wb = new ExcelJS.Workbook()
    const s1 = wb.addWorksheet('MATERIAL')
    cleanMaterialRows().forEach((row, i) => {
      s1.getRow(i + 1).values = row as ExcelJS.CellValue[]
    })
    const s2 = wb.addWorksheet('Material_Dropdown')
    ;[['Auswahlliste'], [MATERIAL_UNIT_PURCHASED_PART], [MATERIAL_UNIT_RAW_MATERIAL_RISK]].forEach((row, i) => {
      s2.getRow(i + 1).values = row as ExcelJS.CellValue[]
    })
    const result = await parseMaterialSheet(wb)
    expect(result?.ignoredCandidateSheets).toEqual([{ name: 'Material_Dropdown', plausibleData: false }])
    expect(materialParseMetaToPlausibilityIssue({ ignoredCandidateSheets: result?.ignoredCandidateSheets }, 'ALT')).toBeNull()
  })

  // (c) F1 fix proof: a duplicate whose Positionsnummer header was
  // renamed/broken (the F1 motivating scenario) but whose materialDesignation
  // header is intact is now visible AND generates a message — the OLD
  // all-or-nothing coreFieldsFound gate would have swallowed this candidate
  // entirely (Positionsnummer is a CORE_MATERIAL_FIELD_KEYS member too).
  it('(c) a duplicate with a broken/renamed Positionsnummer header but an intact materialDesignation header is visible AND generates a message (F1)', async () => {
    const wb = new ExcelJS.Workbook()
    const s1 = wb.addWorksheet('MATERIAL')
    cleanMaterialRows().forEach((row, i) => {
      s1.getRow(i + 1).values = row as ExcelJS.CellValue[]
    })
    const brokenRows = cleanMaterialRows()
    const headers = [...(brokenRows[0] as string[])]
    const positionIdx = headers.indexOf('Positionsnummer Fertigungsschritt')
    headers[positionIdx] = 'Renamed Column XYZ'
    brokenRows[0] = headers
    const s2 = wb.addWorksheet('Material (EN)')
    brokenRows.forEach((row, i) => {
      s2.getRow(i + 1).values = row as ExcelJS.CellValue[]
    })
    const result = await parseMaterialSheet(wb)
    expect(result?.ignoredCandidateSheets).toEqual([{ name: 'Material (EN)', plausibleData: true }])
    const issue = materialParseMetaToPlausibilityIssue({ ignoredCandidateSheets: result?.ignoredCandidateSheets }, 'ALT')
    expect(issue).not.toBeNull()
    expect(issue?.explanation).toContain('Material (EN)')
  })

  // (d) F3 proof: a throwing/degenerate second tab must never abort the
  // whole ingest — the candidate still surfaces (plausibleData:false, same
  // as "label not found").
  it('(d) a throwing/degenerate second tab does not abort the ingest — still listed as plausibleData:false', async () => {
    const wb = new ExcelJS.Workbook()
    const s1 = wb.addWorksheet('MATERIAL')
    cleanMaterialRows().forEach((row, i) => {
      s1.getRow(i + 1).values = row as ExcelJS.CellValue[]
    })
    const s2 = wb.addWorksheet('Material_Broken')
    s2.getRow(1).values = ['x'] as ExcelJS.CellValue[]
    s2.getRow = () => {
      throw new Error('simulated parser-hostile worksheet')
    }
    const result = await parseMaterialSheet(wb)
    expect(result).not.toBeNull()
    expect(result).toHaveLength(5)
    expect(result?.ignoredCandidateSheets).toEqual([{ name: 'Material_Broken', plausibleData: false }])
  })
})

describe('materialRowsForReconciliation (adversarial-review finding, KAR-898 follow-up, point 2)', () => {
  it('no MATERIAL sheet at all -> null', () => {
    expect(materialRowsForReconciliation(null)).toBeNull()
  })

  it('a degraded header (sheet present, core fields unmapped) -> null, NOT an empty array', async () => {
    const wb = await workbookWithSheets([{ name: 'MATERIAL', rows: degradedMaterialRows() }])
    const parsed = await parseMaterialSheet(wb)
    expect(parsed?.coreFieldsFound).toBe(false)
    expect(materialRowsForReconciliation(parsed)).toBeNull()
  })

  it('a clean, fully-mapped sheet -> the parsed rows array', async () => {
    const wb = await workbookWithSheets([{ name: 'MATERIAL', rows: cleanMaterialRows() }])
    const parsed = await parseMaterialSheet(wb)
    expect(parsed?.coreFieldsFound).toBe(true)
    expect(materialRowsForReconciliation(parsed)).toHaveLength(5)
  })

  it('end-to-end: parseMaterialSheet -> materialRowsForReconciliation -> checkReconciliation reports nicht_pruefbar for a degraded header (correct reason text, not the misleading "keine Zeilen vorhanden")', async () => {
    const wb = await workbookWithSheets([{ name: 'MATERIAL', rows: degradedMaterialRows() }])
    const parsed = await parseMaterialSheet(wb)
    const materialRows = materialRowsForReconciliation(parsed)

    const input: ReconciliationInput = {
      side: 'ALT',
      steps: [],
      summaryMetrics: metricsParse({ materialCosts: 100 }),
      materialRows,
    }
    const r = evaluateReconciliation(input).find((x) => x.checkId === 'material_detail_sum')
    expect(r?.status).toBe('nicht_pruefbar')
    expect(r?.reason).toMatch(/MATERIAL-Sheet/)
  })
})

// KAR-899: rehydration counterpart of materialRowsForReconciliation, operating
// on the JSONB shape actions.ts persists on qaf_file.g60_meta.material instead
// of a live MaterialParseResult (recompareComparison's sideOf, see actions.ts).
describe('materialRowsFromPersistedMeta (KAR-899)', () => {
  const intactParseMeta = { parseConfidence: 1, unmappedHeaders: [], mappedFieldCount: 28, coreFieldsFound: true }
  const degradedParseMeta = { parseConfidence: 0, unmappedHeaders: ['??'], mappedFieldCount: 1, coreFieldsFound: false }

  it('g60_meta carries no material key at all (pre-KAR-897 file) -> undefined, not null', () => {
    expect(materialRowsFromPersistedMeta(undefined)).toBeUndefined()
  })

  it('g60_meta.material === null (attempted, no MATERIAL sheet found) -> null', () => {
    expect(materialRowsFromPersistedMeta(null)).toBeNull()
  })

  it('persisted rows with coreFieldsFound: true -> the rows array (Summen-Check can run)', () => {
    const rows = [blankMaterialRow({ positionNumber: '1', materialDesignation: 'Stahl', materialCost: 100 })]
    expect(materialRowsFromPersistedMeta({ rows, parseMeta: intactParseMeta })).toEqual(rows)
  })

  it('persisted rows with coreFieldsFound: false (degraded header, same as at parse time) -> null, NOT the empty/garbage rows array', () => {
    expect(materialRowsFromPersistedMeta({ rows: [], parseMeta: degradedParseMeta })).toBeNull()
  })

  it('end-to-end: a rehydrated intact side reconciles, a rehydrated degraded side reports nicht_pruefbar', () => {
    const intactRows = [blankMaterialRow({ positionNumber: '1', materialDesignation: 'Stahl', materialCost: 100 })]
    const intact = materialRowsFromPersistedMeta({ rows: intactRows, parseMeta: intactParseMeta })
    const degraded = materialRowsFromPersistedMeta({ rows: [], parseMeta: degradedParseMeta })

    const okResult = evaluateReconciliation({
      side: 'ALT',
      steps: [],
      summaryMetrics: metricsParse({ materialCosts: 100 }),
      materialRows: intact,
    }).find((x) => x.checkId === 'material_detail_sum')
    expect(okResult?.status).toBe('bestanden')

    const degradedResult = evaluateReconciliation({
      side: 'NEU',
      steps: [],
      summaryMetrics: metricsParse({ materialCosts: 100 }),
      materialRows: degraded,
    }).find((x) => x.checkId === 'material_detail_sum')
    expect(degradedResult?.status).toBe('nicht_pruefbar')
  })
})

describe('groupMaterialRowsByPosition', () => {
  it('flags a fixed/variable RMR split as one group', () => {
    const fixed = blankMaterialRow({ positionNumber: '2', materialDesignation: 'AlSi10MgMn', unitOfMeasure: 'kg' })
    const variable = blankMaterialRow({
      positionNumber: '2',
      materialDesignation: 'AlSi10MgMn',
      unitOfMeasure: MATERIAL_UNIT_RAW_MATERIAL_RISK,
      rawMaterialDesignation: 'AL LME TEST - AMLI',
    })
    const groups = groupMaterialRowsByPosition([fixed, variable])
    expect(groups).toHaveLength(1)
    expect(groups[0].positionNumber).toBe('2')
    expect(groups[0].rows).toHaveLength(2)
    expect(groups[0].isRawMaterialRiskSplit).toBe(true)
  })

  it('does NOT flag a multi-row position without the RMR marker as a split', () => {
    const a = blankMaterialRow({ positionNumber: '3', materialDesignation: 'DX51D Base price', unitOfMeasure: 'kg' })
    const b = blankMaterialRow({ positionNumber: '3', materialDesignation: 'DX51D Value added', unitOfMeasure: 'kg' })
    const groups = groupMaterialRowsByPosition([a, b])
    expect(groups).toHaveLength(1)
    expect(groups[0].isRawMaterialRiskSplit).toBe(false)
  })

  it('a single-row position is never flagged as a split', () => {
    const only = blankMaterialRow({ positionNumber: '1', materialDesignation: 'Kunststoffgehaeuse' })
    const groups = groupMaterialRowsByPosition([only])
    expect(groups).toHaveLength(1)
    expect(groups[0].isRawMaterialRiskSplit).toBe(false)
  })

  it('preserves first-seen position order and groups non-adjacent rows of the same position', () => {
    const p1 = blankMaterialRow({ positionNumber: '1', materialDesignation: 'A' })
    const p2 = blankMaterialRow({ positionNumber: '2', materialDesignation: 'B' })
    const p1again = blankMaterialRow({ positionNumber: '1', materialDesignation: 'A-2' })
    const groups = groupMaterialRowsByPosition([p1, p2, p1again])
    expect(groups.map((g) => g.positionNumber)).toEqual(['1', '2'])
    expect(groups[0].rows).toHaveLength(2)
  })
})

describe('materialRowLogicalKey', () => {
  it('combines position + material designation', () => {
    const row = blankMaterialRow({ positionNumber: '1', materialDesignation: 'Kunststoffgehaeuse PA6' })
    expect(materialRowLogicalKey(row)).toBe('1::kunststoffgehaeuse pa6')
  })

  it('includes the Rohstoffbezeichnung when present, distinguishing RMR split rows', () => {
    const fixed = blankMaterialRow({ positionNumber: '2', materialDesignation: 'AlSi10MgMn' })
    const variable = blankMaterialRow({
      positionNumber: '2',
      materialDesignation: 'AlSi10MgMn',
      rawMaterialDesignation: 'AL LME TEST - AMLI',
    })
    expect(materialRowLogicalKey(fixed)).not.toBe(materialRowLogicalKey(variable))
  })
})

// KAR-927 (Multi-QAF-Programm P0.2): MATERIAL candidate-sheet visibility
// bridge, same pattern as rmr-parser.ts's rmrParseMetaToPlausibilityIssue.
describe('materialParseMetaToPlausibilityIssue', () => {
  it('returns null when ignoredCandidateSheets is absent (standard QAF — no spam)', () => {
    expect(materialParseMetaToPlausibilityIssue({}, 'NEU')).toBeNull()
  })

  it('returns null when ignoredCandidateSheets is an empty array', () => {
    expect(materialParseMetaToPlausibilityIssue({ ignoredCandidateSheets: [] }, 'NEU')).toBeNull()
  })

  it('reports a pruefen issue naming the side and the ignored sheet names (structured entry, plausibleData:true)', () => {
    const issue = materialParseMetaToPlausibilityIssue({ ignoredCandidateSheets: [{ name: 'Material ', plausibleData: true }] }, 'ALT')
    expect(issue).not.toBeNull()
    expect(issue?.type).toBe('parser_ignored_material_candidate_sheets')
    expect(issue?.severity).toBe('pruefen')
    expect(issue?.step).toBe('ALT')
    expect(issue?.explanation).toContain('Material ')
    expect(issue?.explanationEn).toMatch(/candidate sheet/i)
  })

  it('reports a pruefen issue for a legacy string[] entry (altformat-tolerant — treated as plausibleData:true)', () => {
    const issue = materialParseMetaToPlausibilityIssue({ ignoredCandidateSheets: ['Material '] }, 'ALT')
    expect(issue).not.toBeNull()
    expect(issue?.explanation).toContain('Material ')
  })

  it('returns null when every entry is plausibleData:false (anti-spam — SBM_Matrix/SBM_Dropdown-style reference tabs)', () => {
    const issue = materialParseMetaToPlausibilityIssue(
      { ignoredCandidateSheets: [{ name: 'Material_Dropdown', plausibleData: false }] },
      'NEU',
    )
    expect(issue).toBeNull()
  })

  it('names only the plausibleData:true entries when a mix of plausible/implausible candidates exist', () => {
    const issue = materialParseMetaToPlausibilityIssue(
      {
        ignoredCandidateSheets: [
          { name: 'Material_Dropdown', plausibleData: false },
          { name: 'Material (EN)', plausibleData: true },
        ],
      },
      'ALT',
    )
    expect(issue).not.toBeNull()
    expect(issue?.explanation).toContain('Material (EN)')
    expect(issue?.explanation).not.toContain('Material_Dropdown')
  })
})