// Tests for the SBM-DEVICES-FWZ sheet parser (KAR-898 / P1.7). Fixtures are
// entirely synthetic (invented field values, invented Werkzeugart/Bauteil
// names) — no real Brose/Kiekert/Autoliv/BMW data or files. // allow-customer-string
// (per task instruction)

import { describe, it, expect } from 'vitest'
import ExcelJS from 'exceljs'
import {
  isSbmSheetName,
  findSbmWorksheet,
  findSbmHeaderRow,
  matchSbmHeaderColumn,
  parseSbmWorksheet,
  parseSbmSheet,
  sbmRowsForReconciliation,
  sbmRowsFromPersistedMeta,
  sbmParseMetaToPlausibilityIssue,
  classifySbmToolDeviceType,
  CORE_SBM_FIELD_KEYS,
  SBM_VERRECHNUNGSFORM_VALUES,
  SBM_DEVICE_CATEGORY_TABLE,
  type SbmRow,
  type SbmRowValues,
} from '../sbm-parser'
import { evaluateReconciliation, type ReconciliationInput } from '../reconciliation'
import { metricsParse } from './summary-fixtures'

// Header labels in canonical-fields.ts SBM_FIELDS declaration order
// (Leitfaden Teil 2 [34]-[37]).
const FULL_SBM_HEADERS = [
  'Positionsnummer Fertigungsschritt', // 0 positionNumber
  'Verrechnungsform', // 1 verrechnungsform
  'Werkzeug-/ Vorrichtungsart', // 2 toolFixtureType
  'Bauteilbezeichnung', // 3 componentDesignation
  'Auslegung / Kavitäten / Teile pro Hub [x-fach]', // 4 cavityConfiguration
  'Anzahl Stufen / Takte / Komponenten', // 5 stageCount
  'Standzeit [Zyklen]', // 6 serviceLifeCycles
  'Einsatztermin Werkzeug [MM/JJJJ]', // 7 toolDeploymentDate
  'Auftragszeit [Wochen]', // 8 orderLeadTimeWeeks
  'Standort des Werkzeug-/Vorrichtungsherstellers', // 9 manufacturerLocation
  'Beschaffungswährung BW', // 10 procurementCurrency
  'Kalkulatorisch angesetzte Werkzeug- und Vorrichtungskosten [BW]', // 11 toolFixtureCostBw
  'BMW Sachnummer', // 12 bmwPartNumber // allow-customer-string
  'Index', // 13 componentIndex
  'Länge [mm]', // 14 length
  'Breite [mm]', // 15 width
  'Höhe [mm]', // 16 height
  'Blechdicke [mm]', // 17 sheetThickness
  'Bauteiloberfläche', // 18 componentSurface
  'Werkzeugabmessungen', // 19 toolDimensions
  'Werkzeuggewicht [kg]', // 20 toolWeight
  'Werkzeugkonzept', // 21 toolConcept
  'Anbindungskonzept / Sonderelemente', // 22 connectionConcept
  'Schieber allg. / Verfahr-Elemente', // 23 slidersGeneral
  'Schieber hydraulisch', // 24 slidersHydraulic
  'Schrägschieber / Halteelemente', // 25 slidersAngled
  'Backenschieber', // 26 slidersJaw
  'Schieber gesamt', // 27 slidersTotal
  'Bemerkungen / Details', // 28 remarks
  'Angebotswährung AW', // 29 quotationCurrency
  'Wechselkurs [AW/BW]', // 30 exchangeRate
  'Anzahl Werkzeuge / Vorrichtungen', // 31 toolFixtureCount
  'Summe Werkzeug-/Vorrichtungskosten [AW]', // 32 totalToolFixtureCostAw
  'Summe Werkzeugkosten SBM [AW]', // 33 totalSbmToolCostAw
  'Vorrichtungen + Folgewerkzeuge/-einsätze + Werkzeuginstandhaltung pro Stück [AW]', // 34 devicesFollowupMaintenancePerUnit
]

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) => {
      ws.getRow(i + 1).values = row as ExcelJS.CellValue[]
    })
  }
  return wb
}

/** A clean, fully-mapped SBM-DEVICES-FWZ sheet with 3 rows:
 *  - Pos 1: SBM + Folgewerkzeug Spritzgießwerkzeug (known Werkzeugart).
 *  - Pos 2: Vorrichtung Montagevorrichtung (known Werkzeugart).
 *  - Pos 3: an unrecognized Werkzeugart -> must degrade to a review flag, not
 *    an error.
 */
function cleanSbmRows(): unknown[][] {
  return [
    FULL_SBM_HEADERS,
    // Pos 1 — Kunststoffspritzgießen, SBM + Folgewerkzeug.
    [
      '1', 'SBM', 'Spritzgießwerkzeuge', 'Gehaeuse_Kunststoff', '1-fach', 1, 500000, '03/2027', 12,
      'DE', 'EUR', 45000, '', '', 200, 150, 80, '', 'Feinfarbe', '400x300x250', 850, 'Heisskanal',
      'Standard', 2, 0, 0, 0, 2, '', 'EUR', 1, 2, 45000, 45000, '',
    ],
    // Pos 2 — Montage, Vorrichtung.
    [
      '2', 'Vorrichtung', 'Montagevorrichtungen aller Art', 'Traeger_Halter', '', '', '', '02/2027', 8,
      'DE', 'EUR', 8000, '', '', 300, 200, 120, '', '', '', 40, '', '', 0, 0, 0, 0, 0, '', 'EUR', 1, 1, 8000, '', 5,
    ],
    // Pos 3 — unrecognized Werkzeugart (deliberately fictional string).
    [
      '3', 'SBM', 'Voellig_Unbekanntes_Sonderverfahren', 'Bauteil_X', '', '', '', '', '',
      'DE', 'EUR', 12000, '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, '', 'EUR', 1, 1, 12000, 12000, '',
    ],
  ]
}

async function cleanSbmWorksheet(): Promise<ExcelJS.Worksheet> {
  const wb = await workbookWithSheets([{ name: 'SBM-DEVICES-FWZ', rows: cleanSbmRows() }])
  return wb.worksheets[0]
}

/** A real SBM-DEVICES-FWZ sheet whose header is too degraded to trust
 * (Werkzeug-/Vorrichtungsart + Summe Werkzeug-/Vorrichtungskosten [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). */
function degradedSbmRows(): unknown[][] {
  const rows = cleanSbmRows()
  const headers = [...rows[0]]
  const toolTypeIdx = headers.indexOf('Werkzeug-/ Vorrichtungsart')
  const costIdx = headers.indexOf('Summe Werkzeug-/Vorrichtungskosten [AW]')
  return rows.map((row) => {
    const r = [...row]
    r.splice(Math.max(toolTypeIdx, costIdx), 1)
    r.splice(Math.min(toolTypeIdx, costIdx), 1)
    return r
  })
}

function blankSbmRow(overrides: Partial<SbmRowValues>): SbmRow {
  const base: SbmRowValues = {
    positionNumber: '',
    verrechnungsform: '',
    toolFixtureType: '',
    componentDesignation: '',
    cavityConfiguration: '',
    stageCount: null,
    stageCountActive: null, // KAR-910
    stageCountEmpty: null, // KAR-910
    serviceLifeCycles: null,
    toolDeploymentDate: '',
    orderLeadTimeWeeks: null,
    manufacturerLocation: '',
    procurementCurrency: '',
    toolFixtureCostBw: null,
    bmwPartNumber: '',
    componentIndex: '',
    length: null,
    width: null,
    height: null,
    sheetThickness: null,
    componentSurface: '',
    toolDimensions: '',
    toolWeight: null,
    toolConcept: '',
    connectionConcept: '',
    slidersGeneral: null,
    slidersHydraulic: null,
    slidersAngled: null,
    slidersJaw: null,
    slidersTotal: null,
    remarks: '',
    quotationCurrency: '',
    exchangeRate: null,
    toolFixtureCount: null,
    totalToolFixtureCostAw: null,
    totalSbmToolCostAw: null,
    devicesFollowupMaintenancePerUnit: null,
  }
  return { ...base, ...overrides, sourceCells: {}, normalized: {}, rawText: {}, deviceClassification: null }
}

describe('isSbmSheetName', () => {
  it('matches the SBM-DEVICES-FWZ sheet name (Abbildung 24)', () => {
    expect(isSbmSheetName('SBM-DEVICES-FWZ')).toBe(true)
    expect(isSbmSheetName('sbm-devices-fwz')).toBe(true)
    expect(isSbmSheetName('  SBM-DEVICES-FWZ  ')).toBe(true)
  })

  it('does not match the MATERIAL or Fertigungskosten sheets', () => {
    expect(isSbmSheetName('MATERIAL')).toBe(false)
    expect(isSbmSheetName('Fertigungskosten')).toBe(false)
    expect(isSbmSheetName('Manufacturing costs')).toBe(false)
  })

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

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

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

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

  it('exact match for "Werkzeug-/ Vorrichtungsart"', async () => {
    const m = await matchSbmHeaderColumn('Werkzeug-/ Vorrichtungsart')
    expect(m?.key).toBe('toolFixtureType')
  })

  it('matches the documented "Verrechungsform" typo alias', async () => {
    const m = await matchSbmHeaderColumn('Verrechungsform')
    expect(m?.key).toBe('verrechnungsform')
  })

  // KAR-905/P3.1: the 4th documented fehlerreport-analyse.md typo case
  // ("Diverse Tippfehler nur in DE: 'Teilebennung' (2x), 'Produdktionsstart',
  // 'Verrechungsform', 'Schieber hyrd.' (statt 'hydr.')", §3.3/§4.2 R6) —
  // registered as an alias on sbm_sliders_hydraulic since KAR-892/P1.1
  // (canonical-fields.ts) but, unlike the other three, had no dedicated
  // regression test until this PR's P3.1 typo-coverage audit.
  it('matches the documented "Schieber hyrd." typo alias (statt "hydr.")', async () => {
    const m = await matchSbmHeaderColumn('Schieber hyrd.')
    expect(m?.key).toBe('slidersHydraulic')
  })

  it('normalized (umlaut/whitespace) match', async () => {
    const m = await matchSbmHeaderColumn('Laenge [mm]')
    expect(m?.key).toBe('length')
  })

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

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

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

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

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

/**
 * Ein Blattaufbau, wie ihn die ausgelieferten Formblätter tatsächlich haben:
 * ein über mehrere verbundene Zeilen laufender Kopf, eine Abstandszeile
 * darunter, Trennzeilen zwischen den Baugruppen, keine Positionsnummern — und
 * unter der Tabelle ein Fussbereich mit Gesamtsumme und Vorlagenhinweis.
 *
 * Erfundene Bezeichnungen, keine Echtdaten.
 */
function realisticSbmRows(): unknown[][] {
  const row = (part: string, cost: number, cav = '1-fach', life: number | '' = 500000): unknown[] => {
    const r = new Array(35).fill('')
    r[0] = '' // Positionsnummer bleibt leer — so kommen die Blätter an
    r[1] = 'SBM'
    r[2] = 'Spritzgießwerkzeuge'
    r[3] = part
    r[4] = cav
    r[6] = life
    r[29] = 'EUR'
    r[32] = cost
    return r
  }
  const blank = new Array(35).fill('')
  const footer = (label: string): unknown[] => {
    const r = new Array(35).fill('')
    r[0] = label
    return r
  }

  return [
    FULL_SBM_HEADERS,
    FULL_SBM_HEADERS, // verbundene Kopfzelle, zweite Zeile
    blank, // Abstandszeile unter dem Kopf
    row('Traeger_A', 45000),
    row('Traeger_B', 8000),
    blank, // Trennzeile zwischen zwei Baugruppen
    row('Deckel_C', 12000),
    blank,
    footer('Summen'),
    [...new Array(32).fill(''), 65000, '', ''], // Gesamtsumme im Fuss
    footer('Zusätzliche Zeile: Leerzeile kopieren'),
  ]
}

describe('parseSbmWorksheet — Blattaufbau der ausgelieferten Formblätter', () => {
  const parse = async () => {
    const wb = await workbookWithSheets([{ name: 'SBM-DEVICES-FWZ', rows: realisticSbmRows() }])
    return parseSbmWorksheet(wb.worksheets[0])
  }

  it('liest alle Positionen trotz Trennzeilen und fehlender Positionsnummern', async () => {
    const result = await parse()
    expect(result.map((r) => r.componentDesignation)).toEqual(['Traeger_A', 'Traeger_B', 'Deckel_C'])
  })

  it('nimmt die Kopfwiederholung nicht als Position auf', async () => {
    // Verbundene Kopfzellen liefern in jeder überspannten Zeile denselben Text.
    // Ungefiltert stünde eine Position namens „Bauteilbezeichnung" im Ergebnis.
    const result = await parse()
    expect(result.map((r) => r.componentDesignation)).not.toContain('Bauteilbezeichnung')
  })

  it('hört beim Blattfuss auf, statt Summen und Vorlagenhinweise mitzulesen', async () => {
    // Die Gesamtsumme als Position mitzuzählen verdoppelt die Werkzeugkosten.
    const result = await parse()
    expect(result).toHaveLength(3)
    expect(result.reduce((s, r) => s + (r.totalToolFixtureCostAw ?? 0), 0)).toBe(65000)
  })

  it('behält die Zeilennummer des Blatts als Fundstelle', async () => {
    const result = await parse()
    expect(result[0].sourceCells.componentDesignation).toBe('SBM-DEVICES-FWZ!D4')
    expect(result[2].sourceCells.componentDesignation).toBe('SBM-DEVICES-FWZ!D7')
  })

  it('beendet die Tabelle nach einem längeren Leerlauf', async () => {
    const rows = realisticSbmRows().slice(0, 5)
    const blank = new Array(35).fill('')
    const spaeter = new Array(35).fill('')
    spaeter[3] = 'Legende_Text'
    const wb = await workbookWithSheets([
      { name: 'SBM-DEVICES-FWZ', rows: [...rows, blank, blank, blank, spaeter] },
    ])
    const result = await parseSbmWorksheet(wb.worksheets[0])
    expect(result.map((r) => r.componentDesignation)).toEqual(['Traeger_A', 'Traeger_B'])
  })
})

describe('parseSbmWorksheet — clean sheet', () => {
  it('parses all rows and maps every header', async () => {
    const ws = await cleanSbmWorksheet()
    const result = await parseSbmWorksheet(ws)

    expect(result.coreFieldsFound).toBe(true)
    expect(result).toHaveLength(3)
    expect(result.mappedFieldCount).toBe(35)
    expect(result.unmappedHeaders).toEqual([])
    expect(result.parseConfidence).toBe(1)

    const pos1 = result[0]
    expect(pos1.positionNumber).toBe('1')
    expect(pos1.verrechnungsform).toBe('SBM')
    expect(pos1.toolFixtureType).toBe('Spritzgießwerkzeuge')
    expect(pos1.totalToolFixtureCostAw).toBe(45000)
  })

  it('keeps provenance (sourceCells/normalized/rawText) from day one', async () => {
    const ws = await cleanSbmWorksheet()
    const result = await parseSbmWorksheet(ws)
    const pos1 = result[0]
    expect(pos1.sourceCells.positionNumber).toBe('SBM-DEVICES-FWZ!A2')
    expect(pos1.sourceCells.toolFixtureType).toBe('SBM-DEVICES-FWZ!C2')
    expect(pos1.normalized.totalToolFixtureCostAw).toBe(45000)
    expect(pos1.rawText).toEqual({})
  })

  it('captures rawText for an explicit not-applicable marker', async () => {
    const rows = cleanSbmRows()
    rows[1] = [...rows[1]]
    rows[1][30] = 'n.a.' // Wechselkurs [AW/BW] column on Pos 1
    const wb = await workbookWithSheets([{ name: 'SBM-DEVICES-FWZ', rows }])
    const result = await parseSbmWorksheet(wb.worksheets[0])
    expect(result[0].exchangeRate).toBeNull()
    expect(result[0].rawText.exchangeRate).toBe('n.a.')
  })

  it('classifies a known Werkzeugart via the Betriebsmittelkategorie table', async () => {
    const ws = await cleanSbmWorksheet()
    const result = await parseSbmWorksheet(ws)
    const pos1 = result[0]
    expect(pos1.deviceClassification?.needsReview).toBe(false)
    expect(pos1.deviceClassification?.entry?.classification).toContain('SBM')
    expect(pos1.deviceClassification?.entry?.classification).toContain('Folgewerkzeug')

    const pos2 = result[1]
    expect(pos2.deviceClassification?.needsReview).toBe(false)
    expect(pos2.deviceClassification?.entry?.classification).toEqual(['Vorrichtung'])
  })

  it('flags an unrecognized Werkzeugart for review instead of erroring', async () => {
    const ws = await cleanSbmWorksheet()
    const result = await parseSbmWorksheet(ws)
    const pos3 = result[2]
    expect(pos3.toolFixtureType).toBe('Voellig_Unbekanntes_Sonderverfahren')
    expect(pos3.deviceClassification?.needsReview).toBe(true)
    expect(pos3.deviceClassification?.entry).toBeNull()
  })
})

describe('parseSbmWorksheet — degradation path', () => {
  // KAR-958/P2 (gate-audit.md B6, coreFieldsFound-Resilienz): degradedSbmRows()
  // drops toolFixtureType + totalToolFixtureCostAw but keeps positionNumber
  // (SBM's row-identity/row-push-guard field) intact — so rows ARE now
  // extractable even though 2 of 3 core fields are missing. This is the
  // actual gate-audit B6 fix: previously every already-mapped column was
  // discarded just because those 2 (non-identity) core fields were gone.
  it('does NOT throw when core fields are missing; extracts rows via the surviving positionNumber identity, coreFieldsFound stays false', async () => {
    const wb = await workbookWithSheets([{ name: 'SBM-DEVICES-FWZ', rows: degradedSbmRows() }])
    const result = await parseSbmWorksheet(wb.worksheets[0])

    expect(result.coreFieldsFound).toBe(false)
    expect(result.length).toBeGreaterThan(0) // the fix: rows survive
    expect(result.every((r) => r.positionNumber !== '')).toBe(true)
    expect(result.every((r) => r.toolFixtureType === '')).toBe(true) // the missing field itself stays blank
    expect(result.parseConfidence).toBeGreaterThan(0)
    expect(result.degradation).toEqual({
      facet: 'sbm',
      reason: 'PARSE_FAILED',
      sheet: 'SBM-DEVICES-FWZ',
      message: expect.stringContaining('toolFixtureType'),
    })
  })

  // MIN_SIGNAL_MAPPED_COLUMNS floor (task instruction, same rationale as
  // material-parser.test.ts's anchor): a sheet with fewer recognizable
  // columns than findSbmHeaderRow's own HEADER_MATCH_MIN never even locates
  // a header row — stays empty/undegraded exactly as before this PR.
  it('a genuinely foreign/near-empty sheet stays empty, no degradation', async () => {
    const wb = await workbookWithSheets([
      { name: 'SBM-DEVICES-FWZ', rows: [['Voellig', 'Andere', 'Spalten'], ['a', 'b', 'c']] },
    ])
    const result = await parseSbmWorksheet(wb.worksheets[0])
    expect(result.coreFieldsFound).toBe(false)
    expect(result).toHaveLength(0)
    expect(result.parseConfidence).toBe(0)
    expect(result.degradation).toBeUndefined()
  })

  it('CORE_SBM_FIELD_KEYS is exactly position + Werkzeug-/Vorrichtungsart + AW-Verrechnungsbetrag', () => {
    expect(CORE_SBM_FIELD_KEYS).toEqual(['positionNumber', 'toolFixtureType', 'totalToolFixtureCostAw'])
  })

  it('returns an empty, non-throwing result for a sheet with only 1 row', async () => {
    const wb = await workbookWithSheets([{ name: 'SBM-DEVICES-FWZ', rows: [FULL_SBM_HEADERS] }])
    const result = await parseSbmWorksheet(wb.worksheets[0])
    expect(result).toHaveLength(0)
    expect(result.coreFieldsFound).toBe(false)
  })
})

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

  it('locates and parses the SBM sheet when present alongside other sheets', async () => {
    const wb = await workbookWithSheets([
      { name: 'SUMMARY', rows: [['x']] },
      { name: 'SBM-DEVICES-FWZ', rows: cleanSbmRows() },
    ])
    const result = await parseSbmSheet(wb)
    expect(result).not.toBeNull()
    expect(result).toHaveLength(3)
  })

  // KAR-927 (Multi-QAF-Programm P0.2): findSbmWorksheet's `.find()` silently
  // dropped every candidate sheet after the first — real motivation: a
  // Multi-QAF file bundling a DE and an EN SBM-DEVICES-FWZ tab, one broken.
  // Ergebnis-neutral fix — the FIRST matching sheet is still the one parsed;
  // only ignoredCandidateSheets is new.
  it('a single SBM sheet reports no ignored candidates (standard QAF — no spam)', async () => {
    const wb = await workbookWithSheets([{ name: 'SBM-DEVICES-FWZ', rows: cleanSbmRows() }])
    const result = await parseSbmSheet(wb)
    expect(result?.ignoredCandidateSheets).toBeUndefined()
  })

  // (b) two genuine SBM data sheets — the ignored one is a full, clean
  // duplicate, so the cheap scan finds its toolFixtureType header and
  // reports plausibleData:true (a message fires, see
  // sbmParseMetaToPlausibilityIssue below).
  it('(b) two sheets whose names both match isSbmSheetName: parses the first, reports the rest as ignored with plausibleData:true', async () => {
    const wb = new ExcelJS.Workbook()
    const s1 = wb.addWorksheet('SBM-DEVICES-FWZ')
    cleanSbmRows().forEach((row, i) => {
      s1.getRow(i + 1).values = row as ExcelJS.CellValue[]
    })
    const s2 = wb.addWorksheet('SBM-DEVICES-FWZ (EN)') // allow-customer-string — synthetic DE/EN collision fixture
    cleanSbmRows().forEach((row, i) => {
      s2.getRow(i + 1).values = row as ExcelJS.CellValue[]
    })
    const result = await parseSbmSheet(wb)
    expect(result).not.toBeNull()
    expect(result).toHaveLength(3) // parsed from the FIRST sheet, unchanged
    expect(result?.ignoredCandidateSheets).toEqual([{ name: 'SBM-DEVICES-FWZ (EN)', plausibleData: true }]) // allow-customer-string
  })

  // (a) a name-matching REFERENCE tab (real-corpus finding, 12.07.2026:
  // SBM_Matrix/SBM_Dropdown bundled alongside the real SBM-DEVICES-FWZ sheet // allow-customer-string
  // in nearly every real BMW template) never carries a toolFixtureType // allow-customer-string
  // 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('SBM-DEVICES-FWZ')
    cleanSbmRows().forEach((row, i) => {
      s1.getRow(i + 1).values = row as ExcelJS.CellValue[]
    })
    const s2 = wb.addWorksheet('SBM_Dropdown')
    ;[['Auswahlliste'], ['SBM'], ['Vorrichtung'], ['Folgewerkzeug']].forEach((row, i) => {
      s2.getRow(i + 1).values = row as ExcelJS.CellValue[]
    })
    const result = await parseSbmSheet(wb)
    expect(result?.ignoredCandidateSheets).toEqual([{ name: 'SBM_Dropdown', plausibleData: false }])
    expect(sbmParseMetaToPlausibilityIssue({ ignoredCandidateSheets: result?.ignoredCandidateSheets }, 'ALT')).toBeNull()
  })

  // (c) F1 fix proof: a duplicate whose Positionsnummer header was
  // renamed/broken (the F1 motivating scenario — "EN-Duplikat mit kaputtem/
  // umbenanntem Positionsnummer-Header") but whose toolFixtureType 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_SBM_FIELD_KEYS member too).
  it('(c) a duplicate with a broken/renamed Positionsnummer header but an intact toolFixtureType header is visible AND generates a message (F1)', async () => {
    const wb = new ExcelJS.Workbook()
    const s1 = wb.addWorksheet('SBM-DEVICES-FWZ')
    cleanSbmRows().forEach((row, i) => {
      s1.getRow(i + 1).values = row as ExcelJS.CellValue[]
    })
    const brokenRows = cleanSbmRows()
    const headers = [...(brokenRows[0] as string[])]
    const positionIdx = headers.indexOf('Positionsnummer Fertigungsschritt')
    headers[positionIdx] = 'Renamed Column XYZ'
    brokenRows[0] = headers
    const s2 = wb.addWorksheet('SBM-DEVICES-FWZ (EN)') // allow-customer-string
    brokenRows.forEach((row, i) => {
      s2.getRow(i + 1).values = row as ExcelJS.CellValue[]
    })
    const result = await parseSbmSheet(wb)
    expect(result?.ignoredCandidateSheets).toEqual([{ name: 'SBM-DEVICES-FWZ (EN)', plausibleData: true }]) // allow-customer-string
    const issue = sbmParseMetaToPlausibilityIssue({ ignoredCandidateSheets: result?.ignoredCandidateSheets }, 'ALT')
    expect(issue).not.toBeNull()
    expect(issue?.explanation).toContain('SBM-DEVICES-FWZ (EN)') // allow-customer-string
  })

  // (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('SBM-DEVICES-FWZ')
    cleanSbmRows().forEach((row, i) => {
      s1.getRow(i + 1).values = row as ExcelJS.CellValue[]
    })
    const s2 = wb.addWorksheet('SBM_Broken')
    s2.getRow(1).values = ['x'] as ExcelJS.CellValue[]
    s2.getRow = () => {
      throw new Error('simulated parser-hostile worksheet')
    }
    const result = await parseSbmSheet(wb)
    expect(result).not.toBeNull()
    expect(result).toHaveLength(3)
    expect(result?.ignoredCandidateSheets).toEqual([{ name: 'SBM_Broken', plausibleData: false }])
  })
})

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

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

  it('a clean, fully-mapped sheet -> the parsed rows array (possibly empty, but genuinely so)', async () => {
    const wb = await workbookWithSheets([{ name: 'SBM-DEVICES-FWZ', rows: cleanSbmRows() }])
    const parsed = await parseSbmSheet(wb)
    expect(parsed?.coreFieldsFound).toBe(true)
    expect(sbmRowsForReconciliation(parsed)).toHaveLength(3)
  })

  it('end-to-end: parseSbmSheet -> sbmRowsForReconciliation -> checkReconciliation reports nicht_pruefbar for a degraded header, NEVER bestanden, even when summary.devicesAndTools is also empty', async () => {
    const wb = await workbookWithSheets([{ name: 'SBM-DEVICES-FWZ', rows: degradedSbmRows() }])
    const parsed = await parseSbmSheet(wb)
    const sbmRows = sbmRowsForReconciliation(parsed)

    const input: ReconciliationInput = {
      side: 'ALT',
      steps: [],
      // devicesAndTools intentionally empty too — this is exactly the trap
      // the "both sides empty -> bestanden" shortcut could fall into if the
      // degraded header were misreported as an empty (rather than null)
      // sbmRows array.
      summaryMetrics: metricsParse({}),
      sbmRows,
    }
    const r = evaluateReconciliation(input).find((x) => x.checkId === 'sbm_detail_sum')
    expect(r?.status).toBe('nicht_pruefbar')
    expect(r?.status).not.toBe('bestanden')
    expect(r?.reason).toMatch(/SBM-Sheet/)
  })
})

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

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

  it('g60_meta.sbm === null (attempted, no SBM sheet found) -> null', () => {
    expect(sbmRowsFromPersistedMeta(null)).toBeNull()
  })

  it('persisted rows with coreFieldsFound: true -> the rows array (Summen-Check can run)', () => {
    const rows = [blankSbmRow({ positionNumber: '1', totalToolFixtureCostAw: 100 })]
    expect(sbmRowsFromPersistedMeta({ 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 — even when devicesAndTools is also empty', () => {
    expect(sbmRowsFromPersistedMeta({ rows: [], parseMeta: degradedParseMeta })).toBeNull()

    const r = evaluateReconciliation({
      side: 'ALT',
      steps: [],
      summaryMetrics: metricsParse({}),
      sbmRows: sbmRowsFromPersistedMeta({ rows: [], parseMeta: degradedParseMeta }),
    }).find((x) => x.checkId === 'sbm_detail_sum')
    expect(r?.status).toBe('nicht_pruefbar')
  })

  it('end-to-end: a rehydrated intact side reconciles', () => {
    const rows = [blankSbmRow({ positionNumber: '1', totalToolFixtureCostAw: 100 })]
    const intact = sbmRowsFromPersistedMeta({ rows, parseMeta: intactParseMeta })
    const r = evaluateReconciliation({
      side: 'NEU',
      steps: [],
      summaryMetrics: metricsParse({ devicesAndTools: 100 }),
      sbmRows: intact,
    }).find((x) => x.checkId === 'sbm_detail_sum')
    expect(r?.status).toBe('bestanden')
  })
})

describe('SBM_VERRECHNUNGSFORM_VALUES', () => {
  it('is the 5-value Leitfaden domain (S.34/S.37)', () => {
    expect(SBM_VERRECHNUNGSFORM_VALUES).toEqual(['SBM', 'Vorrichtung', 'Folgewerkzeug', 'Folgeeinsatz', 'Werkzeuginstandhaltung'])
  })
})

describe('classifySbmToolDeviceType', () => {
  it('returns null (nothing to classify) for a blank Werkzeugart', () => {
    expect(classifySbmToolDeviceType('')).toBeNull()
  })

  it('matches case/umlaut/whitespace-insensitively', () => {
    const r = classifySbmToolDeviceType('  spritzgiessWERKZEUGE  ')
    expect(r?.needsReview).toBe(false)
    expect(r?.entry?.werkzeugart).toBe('Spritzgießwerkzeuge')
  })

  it('flags an unknown Werkzeugart as needsReview without throwing', () => {
    const r = classifySbmToolDeviceType('Ganz neue Technologie 2027')
    expect(r?.needsReview).toBe(true)
    expect(r?.entry).toBeNull()
  })

  it('a Vorrichtung-only entry classifies as exactly Vorrichtung', () => {
    const r = classifySbmToolDeviceType('Schweißaufnahme/-gestelle')
    expect(r?.entry?.classification).toEqual(['Vorrichtung'])
  })

  it('an SBM+Folgewerkzeug entry carries both tags', () => {
    const r = classifySbmToolDeviceType('Druckgießwerkzeug')
    expect(r?.entry?.classification).toEqual(expect.arrayContaining(['SBM', 'Folgewerkzeug']))
  })
})

describe('SBM_DEVICE_CATEGORY_TABLE', () => {
  it('has close to 40+ documented Werkzeugart entries (Leitfaden Teil 3, S.85-90)', () => {
    expect(SBM_DEVICE_CATEGORY_TABLE.length).toBeGreaterThanOrEqual(40)
  })

  it('every entry declares at least one classification tag', () => {
    for (const entry of SBM_DEVICE_CATEGORY_TABLE) {
      expect(entry.classification.length).toBeGreaterThan(0)
    }
  })

  it('has no duplicate Werkzeugart text mapping to different classifications', () => {
    const seen = new Map<string, readonly string[]>()
    for (const entry of SBM_DEVICE_CATEGORY_TABLE) {
      const key = entry.werkzeugart.toLowerCase()
      const prior = seen.get(key)
      if (prior) {
        expect([...prior].sort()).toEqual([...entry.classification].sort())
      } else {
        seen.set(key, entry.classification)
      }
    }
  })
})

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

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

  it('reports a pruefen issue naming the side and the ignored sheet names (structured entry, plausibleData:true)', () => {
    const issue = sbmParseMetaToPlausibilityIssue(
      { ignoredCandidateSheets: [{ name: 'SBM-DEVICES-FWZ (EN)', plausibleData: true }] }, // allow-customer-string
      'ALT',
    )
    expect(issue).not.toBeNull()
    expect(issue?.type).toBe('parser_ignored_sbm_candidate_sheets')
    expect(issue?.severity).toBe('pruefen')
    expect(issue?.step).toBe('ALT')
    expect(issue?.explanation).toContain('SBM-DEVICES-FWZ (EN)') // allow-customer-string
    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 = sbmParseMetaToPlausibilityIssue({ ignoredCandidateSheets: ['SBM-DEVICES-FWZ (EN)'] }, 'ALT') // allow-customer-string
    expect(issue).not.toBeNull()
    expect(issue?.explanation).toContain('SBM-DEVICES-FWZ (EN)') // allow-customer-string
  })

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

  it('names only the plausibleData:true entries when a mix of plausible/implausible candidates exist', () => {
    const issue = sbmParseMetaToPlausibilityIssue(
      {
        ignoredCandidateSheets: [
          { name: 'SBM_Matrix', plausibleData: false },
          { name: 'SBM_Dropdown', plausibleData: false },
          { name: 'SBM-DEVICES-FWZ (EN)', plausibleData: true }, // allow-customer-string
        ],
      },
      'ALT',
    )
    expect(issue).not.toBeNull()
    expect(issue?.explanation).toContain('SBM-DEVICES-FWZ (EN)') // allow-customer-string
    expect(issue?.explanation).not.toContain('SBM_Matrix')
    expect(issue?.explanation).not.toContain('SBM_Dropdown')
  })
})