import { describe, it, expect } from 'vitest'
import ExcelJS from 'exceljs'
import {
  worksheetToGrid,
  worksheetToFormulaGrid,
  parseSummaryFromWorkbook,
  parseSummarySheetFromWorkbook,
  summarizeWorkbookSheets,
  semanticActiveRange,
} from '../workbook-adapter'

async function bufferWithSummary(): Promise<ArrayBuffer> {
  const wb = new ExcelJS.Workbook()
  const ws = wb.addWorksheet('Zusammenfassung')
  // mirror the verified real layout: labels in B/F/L, values in C/I/M
  ws.getCell('B8').value = 'Angebotsdatum:'
  ws.getCell('C8').value = new Date('2025-04-29T00:00:00.000Z')
  ws.getCell('F5').value = 'Anbieter/Lieferant:'
  ws.getCell('I5').value = 'Karl HESS '
  ws.getCell('F8').value = 'BMW Sachnummer:' // allow-customer-string
  ws.getCell('I8').value = 7490365
  ws.getCell('L5').value = 'Teilebenennung:'
  ws.getCell('M5').value = 'LI / RE Blende D Säule'
  ws.getCell('L8').value = 'Variante:'
  ws.getCell('M8').value = 'Final'
  return wb.xlsx.writeBuffer()
}

describe('worksheetToGrid', () => {
  it('converts a worksheet to a 0-based row-major grid', async () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('S')
    ws.getCell('A1').value = 'a1'
    ws.getCell('C2').value = 42
    const grid = worksheetToGrid(ws)
    expect(grid[0][0]).toBe('a1')
    expect(grid[1][2]).toBe(42)
  })

  it('resolves formula cells to their result', async () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('S')
    ws.getCell('A1').value = { formula: '1+1', result: 2 } as ExcelJS.CellValue
    const grid = worksheetToGrid(ws)
    expect(grid[0][0]).toBe(2)
  })

  // KAR-928 — a formula cell whose cached RESULT is falsy (0, "", false) used
  // to come back as the raw {formula, ref, shareType, ...} object instead of
  // that falsy value: ExcelJS's `cell.value` for a formula cell is
  // FormulaValue._copyModel(this.model), which only copies a model key when
  // `if (value)` is truthy (node_modules/exceljs/lib/doc/cell.js) — so
  // `result: 0` / `result: ""` was silently dropped from the object
  // `worksheetToGrid`'s old `resolveCell(cell.value)` inspected, and its
  // `'result' in o` check never fired. Every metric/label reader downstream
  // (parseLocaleNumber, cellToString) then either lost a legitimately-zero
  // money value to `null`, or — for a label — read the formula's structural
  // object instead of its display text. Fix: resolveCell now takes the Cell
  // itself and reads `cell.result` (the Cell-level getter, backed directly
  // by the un-filtered internal model) for any formula cell, the same
  // "Cell-level getter, not cell.value" pattern formula-engine.ts already
  // uses for cell.formula vs cell.value.formula (KAR-900).
  it('resolves a formula cell whose cached result is the number 0 (KAR-928)', () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('S')
    ws.getCell('A1').value = { formula: '1-1', result: 0 } as ExcelJS.CellValue
    const grid = worksheetToGrid(ws)
    expect(grid[0][0]).toBe(0)
  })

  // Adversarial-review fix (KAR-928, F4): the original version of this test
  // only proved resolveCell's own logic (an in-memory `.value =
  // {formula, result: ''}` assignment never goes through ExcelJS's XML
  // reader at all). It did NOT prove that a `result: ""` formula cell
  // survives a real .xlsx round trip — and empirically it does not.
  // node_modules/exceljs/lib/xlsx/xform/sheet/cell-xform.js `parseClose('c')`
  // only sets `model.result` `if (model.value)` (line ~351) — also a truthy
  // check, this time in the XML READER, not just the writer-side `_copyModel`
  // part 1 of this fix addresses. A `<v></v>` empty-string result node never
  // calls `parseText` (SAX parsers don't fire on zero-length text), so
  // `model.value` stays `undefined` and `model.result` is NEVER SET —
  // `cell.result` comes back `undefined` after a real round trip, identical
  // to "no cached result at all". Verified directly (see probe below): this
  // is an upstream ExcelJS reader limitation, not something resolveCell can
  // recover from — the information is already gone by the time our code
  // sees the Cell. Documented honestly rather than asserting a fix that
  // doesn't hold for real files: this test now asserts the REAL (degraded)
  // round-trip behavior, matching the "no cached result" case below.
  it('a formula cell whose cached result is an empty string does NOT survive a real .xlsx round trip (documented ExcelJS reader limitation, KAR-928 F4)', async () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('S')
    ws.getCell('A1').value = { formula: 'IF(1=2,"x","")', result: '' } as ExcelJS.CellValue
    const buf = await wb.xlsx.writeBuffer()
    const wb2 = new ExcelJS.Workbook()
    await wb2.xlsx.load(buf)
    const ws2 = wb2.getWorksheet('S')!
    // ExcelJS itself never delivers the "" result through a real file —
    // formulaType survives, result does not.
    expect(ws2.getCell('A1').formulaType).toBeTruthy()
    expect(ws2.getCell('A1').result).toBeUndefined()
    const grid = worksheetToGrid(ws2)
    expect(grid[0][0]).toBeUndefined()
  })

  // A formula cell ExcelJS genuinely never cached a result for (no `result`
  // key in the model at all, e.g. a file saved without a full recalc) must
  // degrade to "no value" (undefined — every downstream reader treats this
  // identically to null), never leak the raw formula object as if it were
  // the cell's value.
  it('resolves a formula cell with no cached result at all to undefined, never the raw formula object', () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('S')
    ws.getCell('A1').value = { formula: 'B1+C1' } as ExcelJS.CellValue
    const grid = worksheetToGrid(ws)
    expect(grid[0][0]).toBeUndefined()
  })
})

// Adversarial-review fix (KAR-928, F1-F3): a non-anchor cell in a merged
// range has `cell.formulaType === undefined` even when the merge ANCHOR is a
// formula (MergeValue defines no formulaType getter). Before this fix,
// resolveCell fell back to `cell.value` for such cells — MergeValue.value
// delegates to `anchor.value`, i.e. the SAME truthy-filtered `_copyModel`
// snapshot that drops falsy formula results (0/""/false). BMW QAF templates // allow-customer-string
// merge-duplicate formula-driven header cells across many columns (real-
// corpus finding, module docstring above), so this was a live bug for
// exactly the class of file this ticket targets, not a hypothetical.
// Fix: resolveCell now recurses into `cell.master` (ExcelJS's own public
// getter — `this` for a non-merged cell, the anchor Cell for any merged
// member including the anchor) BEFORE the formulaType check, so a merged
// formula cell is routed through the same cell.result branch a direct
// formula cell takes. Every test below round-trips through a real
// wb.xlsx.writeBuffer()/load() — not an in-memory `.value` assignment —
// because that's the only way to observe what ExcelJS's own XML writer/
// reader pair actually preserves for a merged formula cell (see F4 finding
// above: the "" case does not survive ANY real round trip, merged or not —
// asserted here too, for parity, not re-litigated).
describe('worksheetToGrid — merged formula ranges (KAR-928 F1-F3)', () => {
  async function mergedFormulaWorkbook(cellValue: ExcelJS.CellValue): Promise<ExcelJS.Worksheet> {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('S')
    ws.getCell('A1').value = cellValue
    ws.mergeCells('A1:C1')
    const buf = await wb.xlsx.writeBuffer()
    const wb2 = new ExcelJS.Workbook()
    await wb2.xlsx.load(buf)
    return wb2.getWorksheet('S')!
  }

  it('resolves the merge ANCHOR of a formula range with cached result 0 (not the raw formula object)', async () => {
    const ws = await mergedFormulaWorkbook({ formula: '1-1', result: 0 } as ExcelJS.CellValue)
    const grid = worksheetToGrid(ws)
    expect(grid[0][0]).toBe(0) // A1, the anchor
  })

  it('resolves a NON-ANCHOR member of a formula merge range with cached result 0 (not the raw formula object) — the core F1-F3 regression', async () => {
    const ws = await mergedFormulaWorkbook({ formula: '1-1', result: 0 } as ExcelJS.CellValue)
    const grid = worksheetToGrid(ws)
    expect(grid[0][1]).toBe(0) // B1, non-anchor
    expect(grid[0][2]).toBe(0) // C1, non-anchor
  })

  it('resolves the merge ANCHOR of a formula range with cached result false', async () => {
    const ws = await mergedFormulaWorkbook({ formula: '1=2', result: false } as ExcelJS.CellValue)
    const grid = worksheetToGrid(ws)
    expect(grid[0][0]).toBe(false)
  })

  it('resolves a NON-ANCHOR member of a formula merge range with cached result false', async () => {
    const ws = await mergedFormulaWorkbook({ formula: '1=2', result: false } as ExcelJS.CellValue)
    const grid = worksheetToGrid(ws)
    expect(grid[0][1]).toBe(false) // B1, non-anchor
    expect(grid[0][2]).toBe(false) // C1, non-anchor
  })

  // Parity with the standalone F4 finding: an empty-string formula result
  // does not survive ANY real .xlsx round trip (ExcelJS reader limitation,
  // cell-xform.js parseClose — see the dedicated test above), merged or not.
  // Documented here rather than silently skipped, so the merge fix's scope
  // is exactly and only "route through the correct branch", not "recover
  // information ExcelJS itself already lost".
  it('a merged formula range with cached result "" degrades to undefined on both anchor and non-anchor, same as the unmerged case (KAR-928 F4 parity)', async () => {
    const ws = await mergedFormulaWorkbook({ formula: 'IF(1=2,"x","")', result: '' } as ExcelJS.CellValue)
    const grid = worksheetToGrid(ws)
    expect(grid[0][0]).toBeUndefined() // A1, anchor
    expect(grid[0][1]).toBeUndefined() // B1, non-anchor
    expect(grid[0][2]).toBeUndefined() // C1, non-anchor
  })

  // Neutrality: a plain (non-formula) merged literal must resolve identically
  // before and after this fix — the recursion into cell.master must not
  // change behavior for the overwhelming majority of merge ranges in real
  // QAF templates, which merge plain labels/values, not formulas.
  it('does not change behavior for a merged PLAIN LITERAL (non-formula) range — neutrality', async () => {
    const ws = await mergedFormulaWorkbook('Teilebenennung:')
    const grid = worksheetToGrid(ws)
    expect(grid[0][0]).toBe('Teilebenennung:') // anchor
    expect(grid[0][1]).toBe('Teilebenennung:') // non-anchor delegates to anchor's literal
    expect(grid[0][2]).toBe('Teilebenennung:')
  })
})

// KAR-900/P2.1: parallel formula grid — worksheetToGrid resolves to the
// computed VALUE, this extracts the raw FORMULA text (null when the cell has
// no formula), same shape/indexing so callers can zip the two grids together.
describe('worksheetToFormulaGrid', () => {
  it('extracts the raw formula text for a formula cell', () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('S')
    ws.getCell('A1').value = { formula: '1+1', result: 2 } as ExcelJS.CellValue
    const grid = worksheetToFormulaGrid(ws)
    expect(grid[0][0]).toBe('1+1')
  })

  it('is null for a plain value cell', () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('S')
    ws.getCell('A1').value = 42
    const grid = worksheetToFormulaGrid(ws)
    expect(grid[0][0]).toBeNull()
  })

  it('is indexed the same way as worksheetToGrid (row/col alignment)', () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('S')
    ws.getCell('C2').value = { formula: 'A1+B1', result: 5 } as ExcelJS.CellValue
    const valueGrid = worksheetToGrid(ws)
    const formulaGrid = worksheetToFormulaGrid(ws)
    expect(valueGrid[1][2]).toBe(5)
    expect(formulaGrid[1][2]).toBe('A1+B1')
  })

  // Adversarial-review fix (KAR-900, 10.07.2026): a real shared-formula slave
  // cell (Worksheet.fillFormula) must resolve to its TRANSLATED formula text,
  // not null — the original cell.value-based check only ever saw a `formula`
  // key on the master.
  it('resolves a real shared-formula slave cell to its translated formula text (not null)', () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('S')
    ws.fillFormula('A1:A2', 'B1+C1', [10, 11])
    const grid = worksheetToFormulaGrid(ws)
    expect(grid[0][0]).toBe('B1+C1') // master
    expect(grid[1][0]).toBe('B2+C2') // slave, translated
  })
})

describe('summarizeWorkbookSheets', () => {
  it('returns one entry per worksheet with its dimension counters', () => {
    const wb = new ExcelJS.Workbook()
    const s1 = wb.addWorksheet('Zusammenfassung')
    s1.getCell('C10').value = 'x'
    const s2 = wb.addWorksheet('Fertigungskosten')
    s2.getCell('V30').value = 'y'
    const sheets = summarizeWorkbookSheets(wb)
    expect(sheets.map((s) => s.name)).toEqual(['Zusammenfassung', 'Fertigungskosten'])
    expect(sheets[0].rowCount).toBeGreaterThanOrEqual(10)
    expect(sheets[1].colCount).toBeGreaterThanOrEqual(22)
  })

  it('returns an empty array for a workbook with no sheets', () => {
    const wb = new ExcelJS.Workbook()
    expect(summarizeWorkbookSheets(wb)).toEqual([])
  })
})

// KAR-933/P1.5 shared helper — "ws.dimensions lügt IMMER" defense
// (30-backlog-phasenplan.md): the declared rowCount/columnCount must NEVER
// be trusted as the real content edge, only as an (already-capped) upper
// scan bound.
describe('semanticActiveRange', () => {
  it('finds the real last populated row/column, ignoring a wildly inflated ws.dimensions', () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('BOM Detail EU')
    ws.getCell('B3').value = 'x'
    ws.getCell('D5').value = 'y'
    // Simulate the real-corpus bloat pattern (declared 7124 rows, real 159 —
    // the real Multi-QAF corpus analysis) by styling a far-away cell without content:
    // ExcelJS's own rowCount/dimensions inflate from style-only touches.
    ws.getCell('A500').style = { font: { bold: true } }
    const range = semanticActiveRange(ws)
    expect(range.lastRow).toBe(5)
    expect(range.lastColumn).toBe(4)
  })

  it('returns lastRow/lastColumn 0 for a sheet with no populated cell', () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('S')
    const range = semanticActiveRange(ws)
    expect(range).toEqual({ lastRow: 0, lastColumn: 0, truncated: false })
  })

  it('bounds the scan to the configured maxRows/maxColumns caps and reports truncated', () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('S')
    ws.getCell('A200').value = 'past the cap'
    const range = semanticActiveRange(ws, { maxRows: 10, maxColumns: 5 })
    expect(range.lastRow).toBe(0) // row 200 is past the maxRows=10 cap, never seen.
    expect(range.truncated).toBe(false) // nothing populated even at the cap edge itself.
  })

  it('marks truncated=true when content reaches the scan window edge itself', () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('S')
    ws.getCell('A10').value = 'at the cap edge'
    const range = semanticActiveRange(ws, { maxRows: 10, maxColumns: 5 })
    expect(range.lastRow).toBe(10)
    expect(range.truncated).toBe(true)
  })
})

describe('parseSummaryFromWorkbook', () => {
  it('parses the summary sheet from a real .xlsx buffer', async () => {
    const buf = await bufferWithSummary()
    const summary = await parseSummaryFromWorkbook(buf)
    expect(summary.partNumber.value).toBe('7490365')
    expect(summary.partNumber.cell).toBe('I8')
    expect(summary.supplier.value).toBe('Karl HESS')
    expect(summary.partName.value).toBe('LI / RE Blende D Säule')
    expect(summary.variant.value).toBe('Final')
    expect(summary.quotationDate.value).toBe('2025-04-29T00:00:00.000Z')
  })

  it('returns null fields when there is no summary sheet', async () => {
    const wb = new ExcelJS.Workbook()
    wb.addWorksheet('Fertigungskosten')
    const buf = await wb.xlsx.writeBuffer()
    const summary = await parseSummaryFromWorkbook(buf)
    expect(summary.partNumber.value).toBeNull()
  })
})

// KAR-928 — end-to-end repro through the real ExcelJS pipeline (not the pure
// gridFromCells unit-test helper summary-metrics.test.ts uses): a summary
// sheet whose AW1 value cell is a FORMULA that legitimately computes to 0
// (a real, non-rare case — e.g. a supplier with zero scrap cost) used to
// come back as `value: null` (the metric read as "absent") because
// resolveCell's old `cell.value`-based check lost the falsy cached result.
// Also covers locateAwColumn's own currency-code read, over the same
// pipeline, to prove the fix helps the AW-column-location path this ticket
// targets, not just an isolated grid cell.
describe('parseSummarySheetFromWorkbook — formula-valued AW cells (KAR-928)', () => {
  it('reads a metric value of 0 from a formula cell instead of losing it to null', async () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('Zusammenfassung')
    ws.getCell('C26').value = 'EUR' // awCell (QAF_LEGACY_DE_SUMMARY)
    ws.getCell('M10').value = 'EUR' // AW1 header band match (column M)
    ws.getCell('G18').value = 'Ausschusskosten' // scrapMaterial label, expected row 18
    ws.getCell('M18').value = { formula: 'N18-N18', result: 0 } as ExcelJS.CellValue
    const buf = await wb.xlsx.writeBuffer()
    const wb2 = new ExcelJS.Workbook()
    await wb2.xlsx.load(buf)
    const { summaryMetrics } = parseSummarySheetFromWorkbook(wb2)
    expect(summaryMetrics?.currency).toBe('EUR')
    expect(summaryMetrics?.metrics.scrapMaterial.value).toBe(0)
    expect(summaryMetrics?.metrics.scrapMaterial.cell).toBe('M18')
    expect(summaryMetrics?.metrics.scrapMaterial.howLocated).toBe('labelMatch')
  })
})