import { describe, it, expect } from 'vitest'
import ExcelJS from 'exceljs'
import {
  findHeaderRow,
  HEADER_TO_KEY,
  parseQAFTemplate,
  TEMPLATE_HEADERS,
  matchHeaderColumn,
  CORE_MANUFACTURING_FIELD_KEYS,
  MANUFACTURING_SHEET_NAME_SUBSTRINGS,
} from '@/lib/qaf-parser'
import { compareFormulaPair } from '@/lib/qaf-differences/internal/formula-engine'
import { MODULE_SHEET_NAME_ALIASES, matchesModuleSheetName } from '@/lib/qaf-differences/internal/module-sheet-names'

const KNOWN_HEADER_TOKENS = Object.keys(HEADER_TO_KEY)

describe('findHeaderRow (auto-detect header row in QAF Excel)', () => {
  it('finds header in row 0 (template format)', async () => {
    const raw: unknown[][] = [
      KNOWN_HEADER_TOKENS,
      ['1', 'Teil A', 'Spritzguss', 'M-1', 'Tachov', 'EUR'],
    ]
    expect(await findHeaderRow(raw)).toBe(0)
  })

  it('finds header in row 10 (BMW Real-QAF format, header at row 11 = index 10)', async () => { // allow-customer-string
    const raw: unknown[][] = []
    // 10 leading rows of header/meta (rows 0-9)
    for (let i = 0; i < 10; i++) {
      raw.push(['BMW Group', 'VERTRAULICH', 'QAF Version 8.8']) // allow-customer-string
    }
    // Row 10 (index) = real headers
    raw.push(['', ...KNOWN_HEADER_TOKENS])
    // Data rows
    raw.push(['', '1', 'Teil A', 'Spritzguss'])
    expect(await findHeaderRow(raw)).toBe(10)
  })

  it('returns null when no header row matches', async () => {
    const raw: unknown[][] = [
      ['random', 'cells', 'no', 'header'],
      ['more', 'random', 'data'],
    ]
    expect(await findHeaderRow(raw)).toBe(null)
  })

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

  it('requires at least 5 matching headers (avoids false positives)', async () => {
    // Row with only 2 known headers — should NOT be detected
    const raw: unknown[][] = [
      ['Positionsnummer Fertigungsschritt', 'Teilebenennung', 'random', 'cells'],
    ]
    expect(await findHeaderRow(raw)).toBe(null)
  })

  it('picks the row with the highest match count when multiple candidates exist', async () => {
    const raw: unknown[][] = [
      // Row 0: 5 matches (minimum)
      KNOWN_HEADER_TOKENS.slice(0, 5),
      // Row 1: all matches
      KNOWN_HEADER_TOKENS,
    ]
    expect(await findHeaderRow(raw)).toBe(1)
  })

  it('handles header text with leading/trailing whitespace', async () => {
    const raw: unknown[][] = [
      KNOWN_HEADER_TOKENS.map((h) => `  ${h}  `),
    ]
    expect(await findHeaderRow(raw)).toBe(0)
  })

  it('handles header text with embedded newlines (Real-QAF uses \\n in header cells)', async () => {
    const raw: unknown[][] = [
      [
        'Positionsnummer\n Fertigungsschritt',
        'Teilebenennung',
        'Prozessbezeichnung',
        'Bezeichnung\n Anlage/Maschine/Typ',
        'Standort',
        'Beschaffungswährung BW',
      ],
    ]
    expect(await findHeaderRow(raw)).toBe(0)
  })

  it('detects English BMW QAF v8 headers (Item number, Parts designation, ...)', async () => { // allow-customer-string
    const raw: unknown[][] = [
      [
        'Item number Manufacturing step',
        'Parts designation',
        'Process designation',
        'Designation Facility/machine/type',
        'Site',
        'Procurement currency [BW]',
        'Cycle time [s]',
      ],
    ]
    expect(await findHeaderRow(raw)).toBe(0)
  })

  it('detects English headers with embedded newlines (real BMW EN file)', async () => { // allow-customer-string
    const raw: unknown[][] = [
      [
        ' Item number\n Manufacturing step',
        ' Parts designation',
        ' Process designation',
        ' Designation\n Facility/machine/type',
        ' Site',
        ' Procurement currency [BW]',
      ],
    ]
    expect(await findHeaderRow(raw)).toBe(0)
  })
})

// KAR-886: per-row cell provenance (source_cells) + structured normalized values.
describe('parseQAFTemplate — source_cells/normalized provenance (KAR-886)', () => {
  async function workbookFile(opts: {
    sheetName?: string
    headerRow?: number
    dataRows: Array<{ row: number; values: Array<string | number> }>
  }): Promise<File> {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet(opts.sheetName ?? 'Fertigungskosten')
    const headerRowNum = opts.headerRow ?? 1
    ws.getRow(headerRowNum).values = [null, ...TEMPLATE_HEADERS]
    for (const { row, values } of opts.dataRows) {
      ws.getRow(row).values = [null, ...values]
    }
    const buf = await wb.xlsx.writeBuffer()
    return new File([buf], 'qaf.xlsx', {
      type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    })
  }

  const rowValues = ['1', 'Blende', 'Montage', 'Anlage A', 'Werk', 'EUR', 12, 1, 1, 30, 10, 50, 0, 0, 0, 100, 'EUR', 1, 1, 100, 2, 1]

  it('attaches a Sheet!A1 source cell + normalized value per mapped field', async () => {
    const file = await workbookFile({ dataRows: [{ row: 2, values: rowValues }] })
    const [row] = await parseQAFTemplate(file)

    // Header row 1 (template position): positionsnummer is column B, data row 2.
    expect(row.sourceCells?.positionsnummer).toBe('Fertigungskosten!B2')
    // prozessbezeichnung is the 3rd header column -> D.
    expect(row.sourceCells?.prozessbezeichnung).toBe('Fertigungskosten!D2')
    // fk [BW] is the 16th header column -> Q.
    expect(row.sourceCells?.fk).toBe('Fertigungskosten!Q2')

    expect(row.normalized?.positionsnummer).toBe('1')
    expect(row.normalized?.prozessbezeichnung).toBe('Montage')
    expect(row.normalized?.fk).toBe(100)

    // Existing (pre-KAR-886) value shape must stay untouched.
    expect(row.fk).toBe(100)
    expect(row.prozessbezeichnung).toBe('Montage')
  })

  it('only records source cells / normalized for fields actually mapped from the header', async () => {
    const file = await workbookFile({ dataRows: [{ row: 2, values: rowValues }] })
    const [row] = await parseQAFTemplate(file)

    const mappedKeys = Object.keys(row.sourceCells ?? {})
    expect(mappedKeys.length).toBeGreaterThanOrEqual(5) // HEADER_MATCH_MIN
    expect(mappedKeys).toEqual(Object.keys(row.normalized ?? {}))
  })

  it('resolves the correct sheet row when the header is shifted (BMW Real-QAF, header row 11)', async () => { // allow-customer-string
    const file = await workbookFile({ headerRow: 11, dataRows: [{ row: 12, values: rowValues }] })
    const [row] = await parseQAFTemplate(file)

    expect(row.sourceCells?.fk).toBe('Fertigungskosten!Q12')
    expect(row.fk).toBe(100)
  })

  it('resolves the correct sheet row across a gap of completely untouched rows', async () => {
    // Row 3 is never touched at all (no cell ever assigned) — ExcelJS therefore
    // never visits it via eachRow({includeEmpty:false}), so the raw grid's array
    // index for the second data row does NOT equal its sheet row number. The
    // parser must track the real ExcelJS row.number, not assume headerIdx+i.
    const secondRowValues = ['2', 'Blende', 'Schweissen', 'Anlage B', 'Werk', 'EUR', 8, 1, 1, 30, 10, 50, 0, 0, 0, 200, 'EUR', 1, 1, 200, 2, 1] // allow-customer-string
    const file = await workbookFile({
      dataRows: [
        { row: 2, values: rowValues },
        { row: 4, values: secondRowValues },
      ],
    })
    const rows = await parseQAFTemplate(file)

    expect(rows).toHaveLength(2)
    expect(rows[0].sourceCells?.fk).toBe('Fertigungskosten!Q2')
    expect(rows[1].sourceCells?.fk).toBe('Fertigungskosten!Q4')
    expect(rows[1].fk).toBe(200)
  })
})

// P0.6 / KAR-891: raw cell text for numeric fields that failed to parse as a
// number (e.g. "n.a."), so the diff engine can tell an explicit not-applicable
// marker apart from a genuinely empty cell — both otherwise collapse to null.
describe('parseQAFTemplate — rawText provenance for non-numeric cells (KAR-891)', () => {
  async function workbookFile(opts: {
    dataRows: Array<{ row: number; values: Array<string | number> }>
  }): Promise<File> {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('Fertigungskosten')
    ws.getRow(1).values = [null, ...TEMPLATE_HEADERS]
    for (const { row, values } of opts.dataRows) {
      ws.getRow(row).values = [null, ...values]
    }
    const buf = await wb.xlsx.writeBuffer()
    return new File([buf], 'qaf.xlsx', {
      type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    })
  }

  it('records rawText for a numeric field holding an "n.a." marker, alongside the null parsed value', async () => {
    const rowValues = ['1', 'Blende', 'Montage', 'Anlage A', 'Werk', 'EUR', 12, 1, 1, 30, 10, 50, 0, 0, 0, 'n.a.', 'EUR', 1, 1, 100, 2, 1]
    const file = await workbookFile({ dataRows: [{ row: 2, values: rowValues }] })
    const [row] = await parseQAFTemplate(file)

    expect(row.fk).toBeNull()
    expect(row.rawText?.fk).toBe('n.a.')
  })

  it('does not record rawText for fields that parse as a real number', async () => {
    const rowValues = ['1', 'Blende', 'Montage', 'Anlage A', 'Werk', 'EUR', 12, 1, 1, 30, 10, 50, 0, 0, 0, 100, 'EUR', 1, 1, 100, 2, 1]
    const file = await workbookFile({ dataRows: [{ row: 2, values: rowValues }] })
    const [row] = await parseQAFTemplate(file)

    expect(row.fk).toBe(100)
    expect(row.rawText?.fk).toBeUndefined()
  })

  it('does not record rawText for a genuinely empty numeric cell', async () => {
    const rowValues = ['1', 'Blende', 'Montage', 'Anlage A', 'Werk', 'EUR', 12, 1, 1, 30, 10, 50, 0, 0, 0, '', 'EUR', 1, 1, 100, 2, 1]
    const file = await workbookFile({ dataRows: [{ row: 2, values: rowValues }] })
    const [row] = await parseQAFTemplate(file)

    expect(row.fk).toBeNull()
    expect(row.rawText?.fk).toBeUndefined()
  })

  it('does not record rawText for text fields (positionsnummer etc.)', async () => {
    const rowValues = ['n.a.', 'Blende', 'Montage', 'Anlage A', 'Werk', 'EUR', 12, 1, 1, 30, 10, 50, 0, 0, 0, 100, 'EUR', 1, 1, 100, 2, 1]
    const file = await workbookFile({ dataRows: [{ row: 2, values: rowValues }] })
    const [row] = await parseQAFTemplate(file)

    expect(row.positionsnummer).toBe('n.a.')
    expect(row.rawText?.positionsnummer).toBeUndefined()
  })
})

// KAR-900 / P2.1: per-field Excel formula extraction (cell.formula), alongside
// the existing computed-value/sourceCells/normalized/rawText provenance.
describe('parseQAFTemplate — formula provenance (KAR-900/P2.1)', () => {
  async function workbookFileWithFormula(): Promise<File> {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('Fertigungskosten')
    ws.getRow(1).values = [null, ...TEMPLATE_HEADERS]
    const rowValues = ['1', 'Blende', 'Montage', 'Anlage A', 'Werk', 'EUR', 12, 1, 1, 30, 10, 50, 0, 0, 0, 100, 'EUR', 1, 1, 100, 2, 1]
    ws.getRow(2).values = [null, ...rowValues]
    // fk [BW] is column Q (16th header column, +1 offset for the leading null).
    ws.getCell('Q2').value = { formula: 'B2+D2', result: 100 } as unknown as import('exceljs').CellValue
    const buf = await wb.xlsx.writeBuffer()
    return new File([buf], 'qaf.xlsx', { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })
  }

  it('attaches raw/normalized/hash provenance for a field whose cell carries a formula', async () => {
    const file = await workbookFileWithFormula()
    const [row] = await parseQAFTemplate(file)
    expect(row.fk).toBe(100) // computed value unaffected
    expect(row.formulas?.fk?.raw).toBe('B2+D2')
    expect(row.formulas?.fk?.normalized).toBe('B2+D2')
    expect(row.formulas?.fk?.hash).toMatch(/^[0-9a-f]{64}$/)
  })

  it('does not attach formula provenance for a plain value-only cell (no noise)', async () => {
    const file = await workbookFileWithFormula()
    const [row] = await parseQAFTemplate(file)
    expect(row.formulas?.ruestkosten).toBeUndefined()
  })

  it('a row with no formulas anywhere has no `formulas` key at all', async () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('Fertigungskosten')
    ws.getRow(1).values = [null, ...TEMPLATE_HEADERS]
    const rowValues = ['1', 'Blende', 'Montage', 'Anlage A', 'Werk', 'EUR', 12, 1, 1, 30, 10, 50, 0, 0, 0, 100, 'EUR', 1, 1, 100, 2, 1]
    ws.getRow(2).values = [null, ...rowValues]
    const buf = await wb.xlsx.writeBuffer()
    const file = new File([buf], 'qaf.xlsx', { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })
    const [row] = await parseQAFTemplate(file)
    expect(row.formulas).toBeUndefined()
  })
})

// Adversarial-review fix (KAR-900, 10.07.2026, Konfidenz 88): the original
// extraction read cell.value.formula, which ExcelJS only ever populates on
// the MASTER cell of a shared/copy-down formula range — every SLAVE cell has
// model.formula === undefined and was silently read as "no formula". BMW // allow-customer-string
// Fertigungskosten copy-down columns routinely use shared ranges, so a cell
// flipping master<->slave role between ALT/NEU (a plain re-save, a row
// insert, Excel choosing a different internal anchor) could misfire the
// flagship 'formel_zu_konstante'/kritisch finding on an unchanged file. The
// fix reads cell.formula/cell.formulaType (ExcelJS's own translated-formula
// getters) instead. These tests use REAL ExcelJS shared-formula ranges
// (Worksheet.fillFormula) — not synthetic {formula:...} literals — because
// that is the one construct ExcelJS treats specially (slave cells with only
// `sharedFormula`, resolved via _getTranslatedFormula/slideFormula).
describe('parseQAFTemplate — shared-formula slave resolution (KAR-900 adversarial-review fix)', () => {
  const baseRow = (pos: string) => [
    pos, 'Blende', 'Montage', 'Anlage A', 'Werk', 'EUR', 12, 1, 1, 30, 10, 50, 0, 0, 0,
    0 /* fk placeholder — overwritten by fillFormula below */, 'EUR', 1, 1, 100, 2, 1,
  ]

  it('resolves BOTH the master and a slave cell of a real shared-formula range to non-empty, position-correct formula text (not null)', async () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('Fertigungskosten')
    ws.getRow(1).values = [null, ...TEMPLATE_HEADERS]
    ws.getRow(2).values = [null, ...baseRow('1')]
    ws.getRow(3).values = [null, ...baseRow('2')]
    // fk [BW] is column Q (16th header column, +1 offset). Q2 becomes the
    // shared-formula MASTER (formula 'B2+D2'), Q3 becomes a SLAVE
    // (sharedFormula: 'Q2', no formula of its own in the raw XML).
    ws.fillFormula('Q2:Q3', 'B2+D2', [100, 105])

    const buf = await wb.xlsx.writeBuffer()
    const file = new File([buf], 'qaf.xlsx', { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })
    const rows = await parseQAFTemplate(file)

    expect(rows).toHaveLength(2)
    expect(rows[0].fk).toBe(100)
    expect(rows[1].fk).toBe(105)

    // Master (row 2): resolved via the literal model.formula.
    expect(rows[0].formulas?.fk?.raw).toBe('B2+D2')
    expect(rows[0].formulas?.fk?.unresolved).toBeUndefined()

    // Slave (row 3): resolved via ExcelJS's translated-formula getter — this
    // is exactly what was broken before the fix (would have been undefined/null).
    expect(rows[1].formulas?.fk?.raw).toBe('B3+D3')
    expect(rows[1].formulas?.fk?.unresolved).toBeUndefined()

    // Translation-normalization decision (documented per review request,
    // see formula-engine.ts module header "Shared-Formula-Slaves"): row 2's
    // and row 3's formulas reference DIFFERENT input cells (B2/D2 vs B3/D3)
    // — they are genuinely different formulas and MUST hash differently.
    // No additional row-relative collapsing is applied on top of ExcelJS's
    // own slideFormula translation; stripReferenceAnchors only unifies $
    // anchors, it does not (and must not) erase a real row-shift.
    expect(rows[0].formulas?.fk?.hash).not.toBe(rows[1].formulas?.fk?.hash)
  })

  it('Master-in-ALT vs Slave-in-NEU of the SAME logical cell (routine re-save/anchor shuffle) produces NO formula-changed finding', async () => {
    // ALT: row 3's fk is a plain literal formula (Master type, no shared range).
    const wbAlt = new ExcelJS.Workbook()
    const wsAlt = wbAlt.addWorksheet('Fertigungskosten')
    wsAlt.getRow(1).values = [null, ...TEMPLATE_HEADERS]
    wsAlt.getRow(2).values = [null, ...baseRow('1')]
    wsAlt.getRow(3).values = [null, ...baseRow('2')]
    wsAlt.getCell('Q3').value = { formula: 'B3+D3', result: 100 } as unknown as ExcelJS.CellValue
    wsAlt.getCell('Q2').value = { formula: 'B2+D2', result: 100 } as unknown as ExcelJS.CellValue
    const bufAlt = await wbAlt.xlsx.writeBuffer()
    const altRows = await parseQAFTemplate(
      new File([bufAlt], 'alt.xlsx', { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' }),
    )

    // NEU: same file, re-saved such that row 3's fk is now a SLAVE of a
    // shared range anchored at row 2 (Q2:Q3, master formula 'B2+D2') — a
    // routine internal-anchor change that leaves the CALCULATION LOGIC
    // identical: translated to Q3 this is 'B3+D3', byte-identical to ALT's
    // literal master formula there.
    const wbNeu = new ExcelJS.Workbook()
    const wsNeu = wbNeu.addWorksheet('Fertigungskosten')
    wsNeu.getRow(1).values = [null, ...TEMPLATE_HEADERS]
    wsNeu.getRow(2).values = [null, ...baseRow('1')]
    wsNeu.getRow(3).values = [null, ...baseRow('2')]
    wsNeu.fillFormula('Q2:Q3', 'B2+D2', [100, 100])
    const bufNeu = await wbNeu.xlsx.writeBuffer()
    const neuRows = await parseQAFTemplate(
      new File([bufNeu], 'neu.xlsx', { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' }),
    )

    // Sanity: NEU's row 3 really is the slave path (would have been
    // undefined pre-fix).
    expect(neuRows[1].formulas?.fk?.raw).toBe('B3+D3')

    const cmp = compareFormulaPair({
      altFormula: altRows[1].formulas?.fk,
      neuFormula: neuRows[1].formulas?.fk,
      altValue: altRows[1].fk,
      neuValue: neuRows[1].fk,
    })
    expect(cmp.kind).toBe('unauffaellig')
    expect(cmp.inputsChanged).toBe(false)
  })

  it('a formula genuinely removed (no shared range involved) and replaced by a hardcoded constant still reports formel_zu_konstante (regression guard)', async () => {
    const wbAlt = new ExcelJS.Workbook()
    const wsAlt = wbAlt.addWorksheet('Fertigungskosten')
    wsAlt.getRow(1).values = [null, ...TEMPLATE_HEADERS]
    wsAlt.getRow(2).values = [null, ...baseRow('1')]
    wsAlt.getCell('Q2').value = { formula: 'B2+D2', result: 100 } as unknown as ExcelJS.CellValue
    const bufAlt = await wbAlt.xlsx.writeBuffer()
    const altRows = await parseQAFTemplate(
      new File([bufAlt], 'alt.xlsx', { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' }),
    )

    const wbNeu = new ExcelJS.Workbook()
    const wsNeu = wbNeu.addWorksheet('Fertigungskosten')
    wsNeu.getRow(1).values = [null, ...TEMPLATE_HEADERS]
    const neuRow = baseRow('1')
    neuRow[15] = 999 // fk hardcoded, no formula at all
    wsNeu.getRow(2).values = [null, ...neuRow]
    const bufNeu = await wbNeu.xlsx.writeBuffer()
    const neuRows = await parseQAFTemplate(
      new File([bufNeu], 'neu.xlsx', { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' }),
    )

    expect(altRows[0].formulas?.fk?.raw).toBe('B2+D2')
    expect(neuRows[0].formulas?.fk).toBeUndefined()

    const cmp = compareFormulaPair({
      altFormula: altRows[0].formulas?.fk,
      neuFormula: neuRows[0].formulas?.fk,
      altValue: altRows[0].fk,
      neuValue: neuRows[0].fk,
    })
    expect(cmp.kind).toBe('formel_zu_konstante')
  })
})

// KAR-893 / P1.2: header→field mapping via the canonical MANUFACTURING
// registry (canonical-model.ts findByAlias) instead of HEADER_TO_KEY
// exact-match-only, with a confidence-scored degradation path instead of a
// hard failure below HEADER_MATCH_MIN.
describe('matchHeaderColumn — canonical label-anchor matching (KAR-893)', () => {
  it('exact DE label -> confidence 1', async () => {
    expect(await matchHeaderColumn('Prozessbezeichnung')).toEqual({ key: 'prozessbezeichnung', confidence: 1 })
  })

  it('exact EN label -> confidence 1', async () => {
    expect(await matchHeaderColumn('Process designation')).toEqual({ key: 'prozessbezeichnung', confidence: 1 })
  })

  it('case-folded variant (not an exact byte match) -> normalized tier, confidence 0.9', async () => {
    expect(await matchHeaderColumn('PROZESSBEZEICHNUNG')).toEqual({ key: 'prozessbezeichnung', confidence: 0.9 })
  })

  it('footnote-asterisk variant -> normalized tier, confidence 0.9', async () => {
    expect(await matchHeaderColumn('Prozessbezeichnung*')).toEqual({ key: 'prozessbezeichnung', confidence: 0.9 })
  })

  it('leading-numbering variant (Leitfaden-style "1. Label") -> normalized tier, confidence 0.9', async () => {
    expect(await matchHeaderColumn('3. Prozessbezeichnung')).toEqual({ key: 'prozessbezeichnung', confidence: 0.9 })
  })

  it('umlaut-ASCII variant -> normalized tier, confidence 0.9', async () => {
    expect(await matchHeaderColumn('Beschaffungswaehrung BW')).toEqual({
      key: 'beschaffungswaehrung',
      confidence: 0.9,
    })
  })

  it('unrelated free text -> unmapped (no fuzzy substring guessing)', async () => {
    expect(await matchHeaderColumn('Bemerkung')).toBeNull()
    expect(await matchHeaderColumn('random junk column')).toBeNull()
  })

  it('blank header cell -> unmapped', async () => {
    expect(await matchHeaderColumn('')).toBeNull()
  })

  it('MATERIAL sharing the same DE label does not leak in — MANUFACTURING scope only', async () => {
    // "Positionsnummer Fertigungsschritt"/"Teilebenennung" exist verbatim in
    // both the MANUFACTURING and MATERIAL canonical modules (canonical-fields.ts).
    // matchHeaderColumn must resolve to the MANUFACTURING field unambiguously,
    // not bail out as a cross-module collision.
    expect(await matchHeaderColumn('Positionsnummer Fertigungsschritt')).toEqual({
      key: 'positionsnummer',
      confidence: 1,
    })
    expect(await matchHeaderColumn('Teilebenennung')).toEqual({ key: 'teilebenennung', confidence: 1 })
  })
})

describe('CORE_MANUFACTURING_FIELD_KEYS (KAR-893)', () => {
  it('is exactly Position + Prozess + FK[BW] per task instruction', () => {
    expect([...CORE_MANUFACTURING_FIELD_KEYS].sort()).toEqual(['fk', 'positionsnummer', 'prozessbezeichnung'].sort())
  })
})

describe('findHeaderRow — degraded header row (KAR-893)', () => {
  it('finds a header row via normalized-only matches when it could not before (structurally impossible pre-KAR-893)', async () => {
    // 5 of the first 6 template headers, each perturbed just enough to fail
    // an EXACT match but still normalize to the same canonical field — case
    // folding + a footnote asterisk + leading numbering + umlaut-ASCII.
    const raw: unknown[][] = [
      [
        'POSITIONSNUMMER FERTIGUNGSSCHRITT',
        'Teilebenennung*',
        '3. Prozessbezeichnung',
        'bezeichnung anlage/maschine/typ',
        'STANDORT',
        'Beschaffungswaehrung BW',
      ],
    ]
    expect(await findHeaderRow(raw)).toBe(0)
  })

  it('a row with only unmapped junk columns still returns null (no false positive)', async () => {
    const raw: unknown[][] = [['foo', 'bar', 'baz', 'qux', 'quux'], ['x', 'y']]
    expect(await findHeaderRow(raw)).toBeNull()
  })
})

describe('parseQAFTemplate — degradation path (KAR-893 / P1.2)', () => {
  async function workbookFile(headers: string[], dataRow: Array<string | number>): Promise<File> {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('Fertigungskosten')
    ws.getRow(1).values = [null, ...headers]
    ws.getRow(2).values = [null, ...dataRow]
    const buf = await wb.xlsx.writeBuffer()
    return new File([buf], 'qaf.xlsx', {
      type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    })
  }

  const rowValues = ['1', 'Blende', 'Montage', 'Anlage A', 'Werk', 'EUR', 12, 1, 1, 30, 10, 50, 0, 0, 0, 100, 'EUR', 1, 1, 100, 2, 1]

  it('an intact template reports parseConfidence 1, mappedFieldCount 22, no unmapped headers', async () => {
    const file = await workbookFile([...TEMPLATE_HEADERS], rowValues)
    const rows = await parseQAFTemplate(file)
    expect(rows.parseConfidence).toBe(1)
    expect(rows.mappedFieldCount).toBe(22)
    expect(rows.unmappedHeaders).toEqual([])
  })

  it('a template with a renamed (normalized-only) header and one genuinely unknown extra column degrades instead of failing', async () => {
    const headers: string[] = [...TEMPLATE_HEADERS]
    // Rename "Standort" to a footnote-marked variant — still resolves via
    // the normalized tier, not exact.
    const standortIdx = headers.indexOf('Standort')
    headers[standortIdx] = 'Standort*'
    // Append one genuinely unrecognized column.
    headers.push('Interne Bemerkung')
    const dataRow = [...rowValues, 'nur intern']

    const file = await workbookFile(headers, dataRow)
    const rows = await parseQAFTemplate(file)

    expect(rows).toHaveLength(1)
    // Degraded column still parsed correctly — this is the whole point.
    expect(rows[0].standort).toBe('Werk')
    expect(rows.mappedFieldCount).toBe(22) // all 22 known fields still resolved (1 via normalized tier)
    expect(rows.unmappedHeaders).toEqual(['Interne Bemerkung'])
    // 21 exact (confidence 1) + 1 normalized (confidence 0.9) -> mean < 1.
    expect(rows.parseConfidence).toBeCloseTo((21 * 1 + 1 * 0.9) / 22, 6)
    expect(rows.parseConfidence).toBeLessThan(1)
    expect(rows.parseConfidence).toBeGreaterThan(0.9)
  })

  it('a header missing a core field (Prozessbezeichnung) throws a clear, named error instead of silently returning zero rows', async () => {
    const headers = TEMPLATE_HEADERS.filter((h) => h !== 'Prozessbezeichnung')
    const dataRow = [...rowValues]
    dataRow.splice(TEMPLATE_HEADERS.indexOf('Prozessbezeichnung'), 1)

    const file = await workbookFile(headers, dataRow)
    await expect(parseQAFTemplate(file)).rejects.toThrow(/Prozessbezeichnung/)
  })

  it('a header missing multiple core fields names all of them in the error', async () => {
    const headers = TEMPLATE_HEADERS.filter((h) => h !== 'Prozessbezeichnung' && h !== 'Fertigungskosten FK [BW]')
    // Still >= HEADER_MATCH_MIN (5) so findHeaderRow locates the row at all.
    const dataRow = rowValues.filter(
      (_, i) => TEMPLATE_HEADERS[i] !== 'Prozessbezeichnung' && TEMPLATE_HEADERS[i] !== 'Fertigungskosten FK [BW]',
    )
    const file = await workbookFile([...headers], dataRow)
    await expect(parseQAFTemplate(file)).rejects.toThrow(/Prozessbezeichnung.*Fertigungskosten FK \[BW\]|Fertigungskosten FK \[BW\].*Prozessbezeichnung/)
  })

  it('a degraded parse still keeps values for mapped fields null-safe for genuinely unmapped ones', async () => {
    const headers = TEMPLATE_HEADERS.filter((h) => h !== 'Ausschusskosten Fertigung [AW]')
    const dataRow = [...rowValues]
    dataRow.splice(TEMPLATE_HEADERS.indexOf('Ausschusskosten Fertigung [AW]'), 1)

    const file = await workbookFile(headers, dataRow)
    const rows = await parseQAFTemplate(file)
    expect(rows[0].ausschusskosten).toBeNull()
    expect(rows[0].fkAW).toBe(100) // unaffected field still parses
    expect(rows.mappedFieldCount).toBe(21)
  })
})

// KAR-906/P3.2 beifang (#283-review finding, confidence 80): a comment on
// qaf-parser.ts's sheet-picker used to claim a drift test between its inline
// DE/EN/typo substrings and module-sheet-names.ts's
// MODULE_SHEET_NAME_ALIASES.MANUFACTURING already existed. It did not —
// module-sheet-names.test.ts only ever asserted matchesModuleSheetName
// against its own hardcoded literal strings, never against this file's
// array. This describe block is that missing test: it fails immediately if
// either list changes without the other.
describe('MANUFACTURING sheet-name alias drift guard (KAR-906, #283-review fix)', () => {
  it('qaf-parser.ts substrings match exactly the MODULE_SHEET_NAME_ALIASES.MANUFACTURING alias set', () => {
    const canonicalNames = MODULE_SHEET_NAME_ALIASES.MANUFACTURING.map((a) => a.name).slice().sort()
    const parserNames = [...MANUFACTURING_SHEET_NAME_SUBSTRINGS].sort()
    expect(parserNames).toEqual(canonicalNames)
  })

  it('every canonical MANUFACTURING alias is recognized by BOTH the qaf-parser matcher and matchesModuleSheetName', () => {
    for (const alias of MODULE_SHEET_NAME_ALIASES.MANUFACTURING) {
      // Simulate a worksheet tab whose lowercase name literally is the alias
      // (the same case-insensitive-substring contract both matchers use).
      const sampleSheetName = alias.name
      const recognizedByParser = MANUFACTURING_SHEET_NAME_SUBSTRINGS.some((s) => sampleSheetName.toLowerCase().includes(s))
      expect(recognizedByParser).toBe(true)
      expect(matchesModuleSheetName(sampleSheetName, 'MANUFACTURING')).toBe(true)
    }
  })

  it('every qaf-parser substring is itself recognized as a MANUFACTURING alias by matchesModuleSheetName (reverse direction)', () => {
    for (const substring of MANUFACTURING_SHEET_NAME_SUBSTRINGS) {
      expect(matchesModuleSheetName(substring, 'MANUFACTURING')).toBe(true)
    }
  })

  it('a real BMW tab name variant ("2. FERTIGUNGSKOSTEN") is recognized by both matchers identically', () => { // allow-customer-string
    const tabName = '2. FERTIGUNGSKOSTEN'
    const recognizedByParser = MANUFACTURING_SHEET_NAME_SUBSTRINGS.some((s) => tabName.toLowerCase().includes(s))
    expect(recognizedByParser).toBe(matchesModuleSheetName(tabName, 'MANUFACTURING'))
    expect(recognizedByParser).toBe(true)
  })
})

// KAR-927 (Multi-QAF-Programm P0.2, "Kandidaten-Sichtbarkeit an .find()-
// Kollaps-Stellen") — parseQAFTemplate's MANUFACTURING sheet-picker used to
// silently drop every candidate sheet after the first via `.find()`. Fixed
// to report the rest via QAFParseResult.ignoredCandidateSheets WITHOUT
// changing which sheet is actually parsed (ergebnis-neutral) — every
// assertion below that a real value came from the FIRST matching sheet is
// the neutrality proof; ignoredCandidateSheets is purely additive.
describe('parseQAFTemplate — MANUFACTURING candidate visibility (KAR-927)', () => {
  const rowValues = ['1', 'Blende', 'Montage', 'Anlage A', 'Werk', 'EUR', 12, 1, 1, 30, 10, 50, 0, 0, 0, 100, 'EUR', 1, 1, 100, 2, 1]

  async function multiSheetWorkbookFile(sheetNames: string[]): Promise<File> {
    const wb = new ExcelJS.Workbook()
    for (const name of sheetNames) {
      const ws = wb.addWorksheet(name)
      ws.getRow(1).values = [null, ...TEMPLATE_HEADERS]
      ws.getRow(2).values = [null, ...rowValues]
    }
    const buf = await wb.xlsx.writeBuffer()
    return new File([buf], 'qaf.xlsx', { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })
  }

  it('a single MANUFACTURING-named sheet reports no ignored candidates (standard QAF — no spam)', async () => {
    const file = await multiSheetWorkbookFile(['Fertigungskosten'])
    const rows = await parseQAFTemplate(file)
    expect(rows.ignoredCandidateSheets).toBeUndefined()
  })

  it('two MANUFACTURING-named sheets: parses the FIRST (unchanged .find() semantics) and reports the second as ignored', async () => {
    const file = await multiSheetWorkbookFile(['Fertigungskosten', 'Manufacturing costs'])
    const rows = await parseQAFTemplate(file)
    expect(rows[0].sourceCells?.positionsnummer).toBe('Fertigungskosten!B2')
    expect(rows.ignoredCandidateSheets).toEqual(['Manufacturing costs'])
  })

  it('three MANUFACTURING-named sheets: reports both ignored candidates in sheet order', async () => {
    const file = await multiSheetWorkbookFile(['Fertigungskosten', 'Manufacturing costs', 'Manufactering costs'])
    const rows = await parseQAFTemplate(file)
    expect(rows.ignoredCandidateSheets).toEqual(['Manufacturing costs', 'Manufactering costs'])
  })

  it('a non-MANUFACTURING sheet interleaved between candidates does not appear in ignoredCandidateSheets', async () => {
    const wb = new ExcelJS.Workbook()
    const s1 = wb.addWorksheet('Zusammenfassung') // allow-customer-string
    s1.getRow(1).values = [null, 'irrelevant']
    const s2 = wb.addWorksheet('Fertigungskosten')
    s2.getRow(1).values = [null, ...TEMPLATE_HEADERS]
    s2.getRow(2).values = [null, ...rowValues]
    const s3 = wb.addWorksheet('Manufacturing costs')
    s3.getRow(1).values = [null, ...TEMPLATE_HEADERS]
    s3.getRow(2).values = [null, ...rowValues]
    const buf = await wb.xlsx.writeBuffer()
    const file = new File([buf], 'qaf.xlsx', { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })
    const rows = await parseQAFTemplate(file)
    expect(rows.ignoredCandidateSheets).toEqual(['Manufacturing costs'])
  })

  it('no MANUFACTURING-named sheet at all: falls back to the first sheet, no ignoredCandidateSheets (unchanged fallback behavior)', async () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('Sheet1')
    ws.getRow(1).values = [null, ...TEMPLATE_HEADERS]
    ws.getRow(2).values = [null, ...rowValues]
    const buf = await wb.xlsx.writeBuffer()
    const file = new File([buf], 'qaf.xlsx', { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })
    const rows = await parseQAFTemplate(file)
    expect(rows.ignoredCandidateSheets).toBeUndefined()
  })
})