// KAR-914 / P4.4 — Untrusted-Excel-Hardening. See workbook-safety.ts module
// header for the design, and the F1/F2 adversarial-review fix notes for why
// the security decision is based on REALLY decompressed sizes (bounded via
// node:zlib's maxOutputLength), never the ZIP Central Directory's DECLARED
// sizes alone. All malicious fixtures are generated IN THE TEST (via ExcelJS
// itself, or a minimal hand-built ZIP buffer) — no binary fixtures are
// committed (portability/gitleaks).

import { describe, it, expect } from 'vitest'
import zlib from 'node:zlib'
import ExcelJS from 'exceljs'
import {
  inspectZipStructure,
  ZipStructureError,
  computeRealUncompressedSize,
  evaluateZipBombRisk,
  DEFAULT_ZIP_BOMB_LIMITS,
  evaluateSheetDimensions,
  DEFAULT_SHEET_DIMENSION_LIMITS,
  detectMacroPresence,
  detectExternalWorkbookLinks,
  runPreParseWorkbookSafetyCheck,
  workbookSafetyToPlausibilityIssues,
  type ZipStructureInspection,
} from '../workbook-safety'

// ── Test helpers ────────────────────────────────────────────────────────────

async function tinyWorkbookBuffer(): Promise<Buffer> {
  const wb = new ExcelJS.Workbook()
  const ws = wb.addWorksheet('S')
  ws.getCell('A1').value = 'hello'
  return Buffer.from(await wb.xlsx.writeBuffer())
}

interface RawZipEntrySpec {
  name: string
  /** 0 = stored, 8 = deflate. */
  method: 0 | 8
  /** The ACTUAL bytes placed after the local header (already
   * deflate-compressed by the caller when method is 8). */
  data: Buffer
  /** DECLARED compressed size (Central Directory + local header) —
   * defaults to `data.length`. Override to simulate a mismatch. */
  declaredCompressedSize?: number
  /** DECLARED uncompressed size (Central Directory + local header) —
   * defaults to `data.length`. Override to simulate a LIE (KAR-914 F1's
   * exploit scenario: declared small, real large). */
  declaredUncompressedSize?: number
}

/** Hand-builds a structurally valid (but CRC-unchecked — this reader never
 * validates CRC-32) ZIP buffer from raw entry specs. Lets tests construct
 * exactly the "declared size lies, real payload doesn't" shape the F1 fix
 * defends against, without needing a full xlsx fixture. */
function buildZip(entries: RawZipEntrySpec[]): Buffer {
  const localParts: Buffer[] = []
  const localOffsets: number[] = []
  let cursor = 0
  for (const entry of entries) {
    const nameBuf = Buffer.from(entry.name, 'utf8')
    const declaredCompressedSize = entry.declaredCompressedSize ?? entry.data.length
    const declaredUncompressedSize = entry.declaredUncompressedSize ?? entry.data.length

    const localHeader = Buffer.alloc(30)
    localHeader.writeUInt32LE(0x04034b50, 0)
    localHeader.writeUInt16LE(20, 4)
    localHeader.writeUInt16LE(0, 6)
    localHeader.writeUInt16LE(entry.method, 8)
    localHeader.writeUInt16LE(0, 10)
    localHeader.writeUInt16LE(0, 12)
    localHeader.writeUInt32LE(0, 14) // crc — never checked by this reader
    localHeader.writeUInt32LE(declaredCompressedSize, 18)
    localHeader.writeUInt32LE(declaredUncompressedSize, 22)
    localHeader.writeUInt16LE(nameBuf.length, 26)
    localHeader.writeUInt16LE(0, 28)

    localOffsets.push(cursor)
    const part = Buffer.concat([localHeader, nameBuf, entry.data])
    localParts.push(part)
    cursor += part.length
  }

  const localSection = Buffer.concat(localParts)
  const cdParts: Buffer[] = []
  for (let i = 0; i < entries.length; i++) {
    const entry = entries[i]
    const nameBuf = Buffer.from(entry.name, 'utf8')
    const declaredCompressedSize = entry.declaredCompressedSize ?? entry.data.length
    const declaredUncompressedSize = entry.declaredUncompressedSize ?? entry.data.length

    const cdHeader = Buffer.alloc(46)
    cdHeader.writeUInt32LE(0x02014b50, 0)
    cdHeader.writeUInt16LE(20, 4)
    cdHeader.writeUInt16LE(20, 6)
    cdHeader.writeUInt16LE(0, 8)
    cdHeader.writeUInt16LE(entry.method, 10)
    cdHeader.writeUInt16LE(0, 12)
    cdHeader.writeUInt16LE(0, 14)
    cdHeader.writeUInt32LE(0, 16) // crc — never checked by this reader
    cdHeader.writeUInt32LE(declaredCompressedSize, 20)
    cdHeader.writeUInt32LE(declaredUncompressedSize, 24)
    cdHeader.writeUInt16LE(nameBuf.length, 28)
    cdHeader.writeUInt16LE(0, 30)
    cdHeader.writeUInt16LE(0, 32)
    cdHeader.writeUInt16LE(0, 34)
    cdHeader.writeUInt16LE(0, 36)
    cdHeader.writeUInt32LE(0, 38)
    cdHeader.writeUInt32LE(localOffsets[i], 42)
    cdParts.push(Buffer.concat([cdHeader, nameBuf]))
  }
  const cdSection = Buffer.concat(cdParts)

  const eocd = Buffer.alloc(22)
  eocd.writeUInt32LE(0x06054b50, 0)
  eocd.writeUInt16LE(0, 4)
  eocd.writeUInt16LE(0, 6)
  eocd.writeUInt16LE(entries.length, 8)
  eocd.writeUInt16LE(entries.length, 10)
  eocd.writeUInt32LE(cdSection.length, 12)
  eocd.writeUInt32LE(localSection.length, 16)
  eocd.writeUInt16LE(0, 20)

  return Buffer.concat([localSection, cdSection, eocd])
}

function storedEntry(name: string, content: Buffer = Buffer.from('placeholder')): RawZipEntrySpec {
  return { name, method: 0, data: content }
}

/** A genuinely deflate-compressed entry whose data is REAL (not fabricated)
 * — `declaredUncompressedSize` can still be overridden by the caller to
 * simulate a lie in the metadata, independent of what the real stream
 * actually inflates to. */
function deflatedEntry(name: string, realPayload: Buffer, declaredUncompressedSizeOverride?: number): RawZipEntrySpec {
  const compressed = zlib.deflateRawSync(realPayload, { level: 9 })
  return {
    name,
    method: 8,
    data: compressed,
    declaredCompressedSize: compressed.length, // accurate — the F1 exploit lies about the UNCOMPRESSED side, not this one
    declaredUncompressedSize: declaredUncompressedSizeOverride ?? realPayload.length,
  }
}

// ── inspectZipStructure ─────────────────────────────────────────────────────

describe('inspectZipStructure', () => {
  it('reads entry names + declared sizes + method + local header offset from a real ExcelJS-generated xlsx', async () => {
    const buf = await tinyWorkbookBuffer()
    const inspection = inspectZipStructure(buf)
    expect(inspection.entries.length).toBeGreaterThan(0)
    const contentTypes = inspection.entries.find((e) => e.name === '[Content_Types].xml')
    expect(contentTypes).toBeTruthy()
    expect(contentTypes!.localHeaderOffset).toBe(0) // first entry in a freshly-written zip
    expect(inspection.totalDeclaredUncompressedSize).toBeGreaterThan(0)
    expect(inspection.totalCompressedSize).toBeGreaterThan(0)
    expect(inspection.zip64Unresolved).toBe(false)
  })

  it('reads a hand-built minimal ZIP with one stored entry', () => {
    const buf = buildZip([storedEntry('hello.txt', Buffer.from('hi'))])
    const inspection = inspectZipStructure(buf)
    expect(inspection.entries).toEqual([
      { name: 'hello.txt', compressedSize: 2, declaredUncompressedSize: 2, method: 0, localHeaderOffset: 0 },
    ])
  })

  it('throws a bilingual ZipStructureError for a non-ZIP buffer', () => {
    const buf = Buffer.from('this is definitely not a zip file, just plain text padding to be long enough')
    expect(() => inspectZipStructure(buf)).toThrow(ZipStructureError)
    try {
      inspectZipStructure(buf)
      expect.unreachable()
    } catch (e) {
      expect((e as Error).message).toMatch(/gültige|valid/i)
      expect((e as Error).message).toContain(' / ') // DE / EN separator (KAR-906 pattern, F4 fix)
    }
  })

  it('throws ZipStructureError for a truncated/empty buffer', () => {
    expect(() => inspectZipStructure(Buffer.alloc(4))).toThrow(ZipStructureError)
  })
})

// ── computeRealUncompressedSize (KAR-914 F1 fix) ────────────────────────────

describe('computeRealUncompressedSize', () => {
  it('matches the real payload size for a genuine deflate entry (declared size is accurate here)', () => {
    const payload = Buffer.alloc(500_000, 65) // 500 KB of 'A'
    const buf = buildZip([deflatedEntry('xl/worksheets/sheet1.xml', payload)])
    const inspection = inspectZipStructure(buf)
    const result = computeRealUncompressedSize(buf, inspection, 10 * 1024 * 1024)
    expect(result.ok).toBe(true)
    expect(result.realUncompressedSize).toBe(payload.length)
  })

  it('computes the real size correctly for a stored (uncompressed) entry', () => {
    const content = Buffer.from('exact bytes, no compression')
    const buf = buildZip([storedEntry('xl/worksheets/sheet2.xml', content)])
    const inspection = inspectZipStructure(buf)
    const result = computeRealUncompressedSize(buf, inspection, 10 * 1024 * 1024)
    expect(result.ok).toBe(true)
    expect(result.realUncompressedSize).toBe(content.length)
  })

  it('THE F1 EXPLOIT: a declared-small-but-really-large entry is caught by the REAL bounded inflate, not the lie', () => {
    // 5 MB of a single repeated byte — deflate compresses this to a few
    // hundred bytes, but the entry LIES that it only inflates to 100 bytes
    // (exactly the shape the original vulnerable version trusted blindly).
    const realPayload = Buffer.alloc(5 * 1024 * 1024, 66)
    const buf = buildZip([deflatedEntry('xl/worksheets/bomb.xml', realPayload, 100 /* the lie */)])
    const inspection = inspectZipStructure(buf)

    // The lie is visible in the declared metadata...
    expect(inspection.entries[0].declaredUncompressedSize).toBe(100)
    expect(inspection.totalDeclaredUncompressedSize).toBe(100)

    // ...but the REAL check, budgeted well below the real 5 MB payload,
    // catches it regardless of what the Central Directory claims.
    const result = computeRealUncompressedSize(buf, inspection, 1 * 1024 * 1024)
    expect(result.ok).toBe(false)
    expect(result.reasonDe).toBeTruthy()
    expect(result.reasonEn).toBeTruthy()
  })

  it('SHARED BUDGET: many individually-small entries whose SUM exceeds the budget are still caught', () => {
    const perEntry = Buffer.alloc(400_000, 67) // 400 KB each, genuinely (not a lie)
    const entries = Array.from({ length: 5 }, (_, i) => deflatedEntry(`xl/part${i}.xml`, perEntry))
    const buf = buildZip(entries)
    const inspection = inspectZipStructure(buf)
    // 5 × 400 KB = 2 MB real total; budget only 1.5 MB — must reject even
    // though every INDIVIDUAL entry (400 KB) is far under the 1.5 MB cap.
    const result = computeRealUncompressedSize(buf, inspection, 1.5 * 1024 * 1024)
    expect(result.ok).toBe(false)
  })

  it('fails closed on a corrupted/non-deflate byte stream declared as method=deflate', () => {
    const garbage = Buffer.from([0xff, 0x00, 0xff, 0x00, 0x13, 0x37, 0x42, 0x99])
    const buf = buildZip([{ name: 'xl/bad.xml', method: 8, data: garbage }])
    const inspection = inspectZipStructure(buf)
    const result = computeRealUncompressedSize(buf, inspection, 10 * 1024 * 1024)
    expect(result.ok).toBe(false)
    expect(result.reasonDe).toBeTruthy()
    expect(result.reasonEn).toBeTruthy()
  })

  it('fails closed on an unexpected compression method', () => {
    const buf = buildZip([{ name: 'xl/weird.xml', method: 12 as 0 | 8, data: Buffer.from('whatever') }])
    const inspection = inspectZipStructure(buf)
    const result = computeRealUncompressedSize(buf, inspection, 10 * 1024 * 1024)
    expect(result.ok).toBe(false)
  })
})

// ── evaluateZipBombRisk (now buffer-aware, F1 fix) ──────────────────────────

describe('evaluateZipBombRisk', () => {
  it('accepts a real small ExcelJS workbook', async () => {
    const buf = await tinyWorkbookBuffer()
    const inspection = inspectZipStructure(buf)
    expect(evaluateZipBombRisk(buf, inspection).ok).toBe(true)
  })

  it('rejects the F1 exploit shape end-to-end (declared small, real large)', () => {
    const realPayload = Buffer.alloc(3 * 1024 * 1024, 68)
    const buf = buildZip([deflatedEntry('xl/worksheets/bomb.xml', realPayload, 50 /* lie */)])
    const inspection = inspectZipStructure(buf)
    const verdict = evaluateZipBombRisk(buf, inspection, { ...DEFAULT_ZIP_BOMB_LIMITS, maxUncompressedBytes: 1024 * 1024 })
    expect(verdict.ok).toBe(false)
  })

  it('rejects an unresolved Zip64 structure out of caution', async () => {
    const buf = await tinyWorkbookBuffer()
    const inspection: ZipStructureInspection = {
      entries: [],
      totalCompressedSize: 0,
      totalDeclaredUncompressedSize: 0,
      zip64Unresolved: true,
    }
    expect(evaluateZipBombRisk(buf, inspection).ok).toBe(false)
  })

  it('F2 REGRESSION — the default limits satisfy ratioMinCompressedBytes * ratioThreshold <= maxUncompressedBytes (the ratio branch is mathematically reachable below the cap)', () => {
    expect(DEFAULT_ZIP_BOMB_LIMITS.ratioMinCompressedBytes * DEFAULT_ZIP_BOMB_LIMITS.ratioThreshold).toBeLessThanOrEqual(
      DEFAULT_ZIP_BOMB_LIMITS.maxUncompressedBytes,
    )
  })

  it('F2 REGRESSION — ratio branch fires in isolation, on REAL sizes, for a file that stays under the absolute cap', () => {
    // 300 KB real payload, highly compressible (repeated byte) -> compresses
    // to well under 1 KB. Use a floor low enough that the compressed size
    // clears it, and a cap high enough that only the RATIO branch can fire
    // (isolates the ratio math from the absolute-cap math — the previous
    // version's test at workbook-safety.test.ts:157-166, per the review
    // finding, exercised the CAP branch while believing it tested ratio).
    const realPayload = Buffer.alloc(300_000, 69)
    const buf = buildZip([deflatedEntry('xl/worksheets/sheet1.xml', realPayload)])
    const inspection = inspectZipStructure(buf)
    expect(inspection.totalCompressedSize).toBeLessThan(2_000) // genuinely tiny compressed size
    const limits = { maxUncompressedBytes: 50 * 1024 * 1024, ratioThreshold: 50, ratioMinCompressedBytes: 100 }
    const verdict = evaluateZipBombRisk(buf, inspection, limits)
    // Real ratio here is >>50:1 (300 KB from well under 2 KB compressed) and
    // the real total (300 KB) is nowhere near the 50 MB cap — proves the
    // rejection came from the RATIO branch, not the cap.
    expect(verdict.ok).toBe(false)
  })

  it('does NOT reject a real, moderately-compressible small file below the ratio floor', () => {
    // A few KB of genuinely-varied (not hyper-repetitive) content — real
    // ratio stays modest, and the compressed size stays below the
    // ratioMinCompressedBytes floor at the default limits, so neither
    // branch should fire.
    const varied = Buffer.from(Array.from({ length: 20_000 }, (_, i) => (i * 37) % 256))
    const buf = buildZip([deflatedEntry('xl/worksheets/sheet1.xml', varied)])
    const inspection = inspectZipStructure(buf)
    expect(evaluateZipBombRisk(buf, inspection).ok).toBe(true)
  })

  it('detects a real highly-compressible large sheet built via ExcelJS (repeated-value zip bomb pattern)', async () => {
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('Bomb')
    const filler = 'A'.repeat(200)
    for (let row = 1; row <= 8000; row++) {
      for (let col = 1; col <= 10; col++) {
        ws.getCell(row, col).value = filler
      }
    }
    const buf = Buffer.from(await wb.xlsx.writeBuffer())
    const inspection = inspectZipStructure(buf)
    // Real, measured ratio for this fixture is ~17:1 (DEFLATE's 32 KB window
    // bounds how far a repeated-content xlsx can compress — a genuine 100:1+
    // xlsx bomb needs a fixture far too large to build in a fast unit test).
    // A normal small QAF workbook measures ~3:1 (see tinyWorkbookBuffer use
    // above) — a threshold of 12 cleanly separates "highly repetitive,
    // suspicious" from "normal" while staying entirely below the production
    // default (100:1). Numbers here are REAL (post-F1), computed by
    // actually inflating every entry, not read off the Central Directory.
    const testLimits = { ...DEFAULT_ZIP_BOMB_LIMITS, ratioThreshold: 12, ratioMinCompressedBytes: 50_000 }
    const verdict = evaluateZipBombRisk(buf, inspection, testLimits)
    expect(verdict.ok).toBe(false)
  })
})

// ── evaluateSheetDimensions ──────────────────────────────────────────────────

describe('evaluateSheetDimensions', () => {
  it('accepts a normal-sized sheet', () => {
    const verdict = evaluateSheetDimensions([{ name: 'S', rowCount: 100, colCount: 20 }])
    expect(verdict.ok).toBe(true)
  })

  it('rejects a sheet over the row limit', () => {
    const verdict = evaluateSheetDimensions([{ name: 'Huge', rowCount: DEFAULT_SHEET_DIMENSION_LIMITS.maxRows + 1, colCount: 5 }])
    expect(verdict.ok).toBe(false)
    expect(verdict.reasonDe).toContain('Huge')
    expect(verdict.reasonEn).toContain('Huge')
  })

  it('rejects a sheet over the column limit', () => {
    const verdict = evaluateSheetDimensions([{ name: 'Wide', rowCount: 5, colCount: DEFAULT_SHEET_DIMENSION_LIMITS.maxColumns + 1 }])
    expect(verdict.ok).toBe(false)
  })

  it('reports every offending sheet, not just the first', () => {
    const verdict = evaluateSheetDimensions([
      { name: 'A', rowCount: DEFAULT_SHEET_DIMENSION_LIMITS.maxRows + 1, colCount: 5 },
      { name: 'B', rowCount: 5, colCount: DEFAULT_SHEET_DIMENSION_LIMITS.maxColumns + 1 },
      { name: 'C', rowCount: 5, colCount: 5 },
    ])
    expect(verdict.ok).toBe(false)
    expect(verdict.reasonDe).toContain('A')
    expect(verdict.reasonDe).toContain('B')
    expect(verdict.reasonDe).not.toContain('"C"')
  })
})

// ── detectMacroPresence / detectExternalWorkbookLinks ────────────────────────

describe('detectMacroPresence', () => {
  it('is false for a workbook with no vbaProject.bin entry', async () => {
    const inspection = inspectZipStructure(await tinyWorkbookBuffer())
    expect(detectMacroPresence(inspection)).toBe(false)
  })

  it('is true when xl/vbaProject.bin is present', () => {
    const inspection = inspectZipStructure(buildZip([storedEntry('xl/vbaProject.bin', Buffer.from('fake vba'))]))
    expect(detectMacroPresence(inspection)).toBe(true)
  })
})

describe('detectExternalWorkbookLinks', () => {
  it('is false for a workbook with no externalLinks entries', async () => {
    const inspection = inspectZipStructure(await tinyWorkbookBuffer())
    expect(detectExternalWorkbookLinks(inspection)).toBe(false)
  })

  it('is true when xl/externalLinks/externalLink1.xml is present', () => {
    const inspection = inspectZipStructure(buildZip([storedEntry('xl/externalLinks/externalLink1.xml', Buffer.from('<x/>'))]))
    expect(detectExternalWorkbookLinks(inspection)).toBe(true)
  })
})

// ── runPreParseWorkbookSafetyCheck (orchestrator) ────────────────────────────

describe('runPreParseWorkbookSafetyCheck', () => {
  it('passes a legitimate small workbook clean', async () => {
    const result = runPreParseWorkbookSafetyCheck(await tinyWorkbookBuffer())
    expect(result.zipBomb.ok).toBe(true)
    expect(result.safety.macroPresent).toBe(false)
    expect(result.safety.externalLinksPresent).toBe(false)
  })

  it('flags macro presence without rejecting the file', () => {
    const buf = buildZip([storedEntry('xl/vbaProject.bin', Buffer.from('fake'))])
    const result = runPreParseWorkbookSafetyCheck(buf)
    expect(result.zipBomb.ok).toBe(true)
    expect(result.safety.macroPresent).toBe(true)
  })
})

// ── workbookSafetyToPlausibilityIssues (KAR-906 bilingual bridge) ───────────

describe('workbookSafetyToPlausibilityIssues', () => {
  it('returns no issues for a clean file', () => {
    expect(workbookSafetyToPlausibilityIssues({ macroPresent: false, externalLinksPresent: false }, 'ALT', 'a.xlsx')).toEqual([])
  })

  it('emits a bilingual security_macro_present issue', () => {
    const issues = workbookSafetyToPlausibilityIssues({ macroPresent: true, externalLinksPresent: false }, 'NEU', 'macro.xlsm')
    expect(issues).toHaveLength(1)
    expect(issues[0].type).toBe('security_macro_present')
    expect(issues[0].severity).toBe('pruefen')
    expect(issues[0].explanation).toContain('macro.xlsm')
    expect(issues[0].explanationEn).toContain('macro.xlsm')
    expect(issues[0].explanationEn).not.toBe(issues[0].explanation)
  })

  it('emits a bilingual security_external_links issue', () => {
    const issues = workbookSafetyToPlausibilityIssues({ macroPresent: false, externalLinksPresent: true }, 'ALT', 'links.xlsx')
    expect(issues).toHaveLength(1)
    expect(issues[0].type).toBe('security_external_links')
  })

  it('emits both issues when both are present', () => {
    const issues = workbookSafetyToPlausibilityIssues({ macroPresent: true, externalLinksPresent: true }, 'ALT', 'both.xlsm')
    expect(issues.map((i) => i.type).sort()).toEqual(['security_external_links', 'security_macro_present'])
  })
})