// Untrusted-Excel hardening (KAR-914 / P4.4, Master-Prompt §20 "Security and
// file safety").
//
// Pure structural safety checks that run in ingestQafUpload (actions.ts):
//
//   (a) BEFORE loadExcelWorkbook — a zip-ratio guard reads the ZIP Central
//       Directory (entry names, declared sizes, compression method, local
//       header offset; XLSX/XLSM is a ZIP container) via a minimal
//       hand-rolled ZIP reader (node:buffer + node:zlib only, no new
//       dependency), and rejects a suspected zip bomb before the expensive
//       full unzip+XML-parse that ExcelJS's wb.xlsx.load() performs.
//
//       Adversarial-review fix (KAR-914 F1, 10.07.2026): the FIRST version of
//       this guard trusted the Central Directory's DECLARED
//       uncompressedSize field for the cap/ratio decision. That is exactly
//       as unsafe as doing no check at all — a crafted ZIP can declare any
//       uncompressedSize it likes while its DEFLATE stream really inflates
//       to gigabytes; ExcelJS's own unzip (JSZip → pako) only compares
//       data_length against the declared size in its "end" event, i.e.
//       AFTER the real inflate output is already fully materialized in
//       memory — by then the OOM has already happened, so a second
//       declared-size check ahead of it is no defense whatsoever.
//
//       The fix: `computeRealUncompressedSize` REALLY decompresses every
//       DEFLATE entry via `node:zlib`'s `inflateRawSync` with
//       `maxOutputLength` set to the REMAINING budget. Node's zlib caps the
//       decompressor's own internal output buffer and throws
//       ERR_BUFFER_TOO_LARGE the INSTANT more output would be produced than
//       the cap allows — verified empirically: a stream that would inflate
//       to 50 MB throws immediately when capped at 1 MB, the 50 MB is never
//       allocated. That is the actual OOM-safe primitive this module relies
//       on; nothing here ever allocates more than `maxUncompressedBytes` for
//       decompressed data, regardless of what any entry's metadata claims.
//       The budget is a single SHARED, DECREMENTING pool across every entry
//       (not a per-entry cap), so many medium entries that individually stay
//       under the cap but sum past it are still caught.
//
//   (b) AFTER the workbook load, BEFORE any cell iteration — a sheet-
//       dimension cap reads ExcelJS's own rowCount/columnCount counters (no
//       additional read pass) and rejects a workbook with an oversized
//       sheet, matching the same "reject before partial parse" contract as
//       the zip-ratio guard.
//   (c) External workbook links (xl/externalLinks/…) and (d) macro presence
//       (xl/vbaProject.bin) are detected from the same Central Directory
//       entry-name list the zip-ratio guard already reads — no separate
//       pass, no ExcelJS model inspection needed. These are NON-blocking:
//       Kadi-v2 never follows external links and ExcelJS never executes
//       macros (verified: ExcelJS's xlsx reader only parses
//       sheet/style/shared-string XML — it has no VBA interpreter and never
//       reads externalLinks targets), so both are surfaced as an advisory
//       qaf_plausibility_issue (KAR-906 bilingual pattern) for reviewer
//       awareness rather than a hard rejection.
//   (e) Resource-limit: parse wall-clock time is already bounded by
//       `export const maxDuration = 300` (app/qaf-differences/page.tsx) —
//       no separate timer here (avoids duplicating a limit Next.js/Vercel
//       already enforces at the route level).
//
// Relationship to KAR-801: KAR-801 tracks the export/download HTTP route
// context (see actions.ts:70-76's comment); this module hardens the
// UPLOAD/INGEST path only. The wire-size cap (QAF_MAX_FILE_BYTES, 20 MB
// compressed, qaf-upload-constants.ts) already existed before this PR and is
// unchanged — it bounds the wire size only, which is exactly the gap this
// module closes (decompressed size / ratio / dimensions).
//
// Pure, no I/O beyond in-memory Buffer/zlib operations, no ExcelJS import
// (works on the raw Buffer for (a)/(c)/(d), and on the caller-supplied
// WorkbookSheetSummary[] for (b) — see workbook-adapter.ts's
// summarizeWorkbookSheets).

import zlib from 'node:zlib'
import type { WorkbookSheetSummary } from './workbook-adapter'
import type { PlausibilityIssue } from './plausibility'

// ── ZIP Central Directory reader ────────────────────────────────────────────

export interface ZipEntryInfo {
  name: string
  /** DECLARED (Central Directory) compressed size — safe to trust only for
   * bounding WHICH SLICE of the already-in-memory, wire-capped buffer to
   * feed into inflate (see computeRealUncompressedSize); never used as the
   * security decision itself. */
  compressedSize: number
  /** DECLARED (Central Directory) uncompressed size — DIAGNOSTIC ONLY.
   * Never trusted for the zip-bomb decision (see F1 fix in the module
   * header) — a crafted ZIP can set this to anything regardless of what the
   * DEFLATE stream really inflates to. Kept on the entry purely for
   * logging/debugging (e.g. comparing declared vs. real in a smoke test). */
  declaredUncompressedSize: number
  /** Compression method from the Central Directory (0 = stored, 8 =
   * deflate — the only two methods Office/ExcelJS ever produce). */
  method: number
  /** Byte offset of this entry's LOCAL file header, from the Central
   * Directory's "relative offset of local header" field — used to locate
   * the entry's actual compressed data (see locateLocalFileHeaderDataOffset,
   * which reads the LOCAL header's own name/extra lengths, not the Central
   * Directory's copy — the two can legally differ). */
  localHeaderOffset: number
}

export interface ZipStructureInspection {
  entries: ZipEntryInfo[]
  /** Sum of DECLARED compressed sizes — safe (bounded by the already
   * wire-capped buffer), used as the ratio check's denominator. */
  totalCompressedSize: number
  /** Sum of DECLARED uncompressed sizes — DIAGNOSTIC ONLY, see
   * ZipEntryInfo.declaredUncompressedSize. Never used for the zip-bomb
   * decision (see computeRealUncompressedSize instead). */
  totalDeclaredUncompressedSize: number
  /** true when the archive declares Zip64 sizes (the 0xFFFFFFFF sentinel)
   * this reader does not resolve. Real QAF files stay far below the 4 GiB
   * Zip64 threshold and under the 20 MB wire cap — a Zip64 marker on an
   * uploaded "xlsx" is itself out of place, so callers should fail closed
   * (see evaluateZipBombRisk) rather than silently under-count. */
  zip64Unresolved: boolean
}

export class ZipStructureError extends Error {}

const EOCD_SIGNATURE = 0x06054b50
const CENTRAL_DIRECTORY_SIGNATURE = 0x02014b50
const LOCAL_FILE_HEADER_SIGNATURE = 0x04034b50
const EOCD_FIXED_SIZE = 22
const EOCD_MAX_COMMENT_LENGTH = 65535
const LOCAL_FILE_HEADER_FIXED_SIZE = 30
const ZIP64_SENTINEL_U32 = 0xffffffff
const ZIP64_SENTINEL_U16 = 0xffff

const METHOD_STORE = 0
const METHOD_DEFLATE = 8

function locateEndOfCentralDirectory(buffer: Buffer): number {
  if (buffer.length < EOCD_FIXED_SIZE) return -1
  // The EOCD record sits at the end, followed only by an optional comment
  // (max 65535 bytes) — search backwards from the tail, bounded. Same
  // technique every zip reader uses (unzip, 7z, Python's zipfile).
  const searchStart = Math.max(0, buffer.length - EOCD_FIXED_SIZE - EOCD_MAX_COMMENT_LENGTH)
  for (let i = buffer.length - EOCD_FIXED_SIZE; i >= searchStart; i--) {
    if (buffer.readUInt32LE(i) === EOCD_SIGNATURE) return i
  }
  return -1
}

/**
 * Read the ZIP Central Directory of an XLSX/XLSM buffer: entry names,
 * declared sizes (diagnostic only, see ZipEntryInfo doc), compression
 * method and local-header offset. Bilingual (DE-first message, EN suffix)
 * on every failure (KAR-906 pattern, KAR-914 F4 fix) so a rejection at this
 * layer is as legible to a reviewer as the qaf_plausibility_issue channel.
 */
export function inspectZipStructure(buffer: Buffer): ZipStructureInspection {
  const eocdOffset = locateEndOfCentralDirectory(buffer)
  if (eocdOffset === -1) {
    throw new ZipStructureError(
      'Keine gültige ZIP/XLSX-Containerstruktur (kein End-Of-Central-Directory-Eintrag gefunden). / ' +
        'Not a valid ZIP/XLSX container (no End-Of-Central-Directory record found).',
    )
  }
  const cdEntryCount = buffer.readUInt16LE(eocdOffset + 10)
  const cdSize = buffer.readUInt32LE(eocdOffset + 12)
  const cdOffset = buffer.readUInt32LE(eocdOffset + 16)
  let zip64Unresolved = cdEntryCount === ZIP64_SENTINEL_U16 || cdSize === ZIP64_SENTINEL_U32 || cdOffset === ZIP64_SENTINEL_U32

  const entries: ZipEntryInfo[] = []
  let totalCompressedSize = 0
  let totalDeclaredUncompressedSize = 0
  let cursor = cdOffset
  for (let i = 0; i < cdEntryCount && !zip64Unresolved; i++) {
    if (cursor + 46 > buffer.length) {
      throw new ZipStructureError(
        'ZIP-Central-Directory-Eintrag unvollständig (abgeschnitten). / ZIP central directory entry truncated.',
      )
    }
    const signature = buffer.readUInt32LE(cursor)
    if (signature !== CENTRAL_DIRECTORY_SIGNATURE) {
      throw new ZipStructureError(
        'Fehlerhafte ZIP-Central-Directory (unerwartete Eintrags-Signatur). / ' +
          'Malformed ZIP central directory (unexpected entry signature).',
      )
    }
    const method = buffer.readUInt16LE(cursor + 10)
    const compressedSize = buffer.readUInt32LE(cursor + 20)
    const declaredUncompressedSize = buffer.readUInt32LE(cursor + 24)
    const nameLength = buffer.readUInt16LE(cursor + 28)
    const extraLength = buffer.readUInt16LE(cursor + 30)
    const commentLength = buffer.readUInt16LE(cursor + 32)
    const localHeaderOffset = buffer.readUInt32LE(cursor + 42)
    const nameStart = cursor + 46
    if (nameStart + nameLength > buffer.length) {
      throw new ZipStructureError(
        'ZIP-Central-Directory-Eintragsname unvollständig (abgeschnitten). / ' +
          'ZIP central directory entry name truncated.',
      )
    }
    if (compressedSize === ZIP64_SENTINEL_U32 || declaredUncompressedSize === ZIP64_SENTINEL_U32 || localHeaderOffset === ZIP64_SENTINEL_U32) {
      zip64Unresolved = true
      break
    }
    const name = buffer.toString('utf8', nameStart, nameStart + nameLength)
    entries.push({ name, compressedSize, declaredUncompressedSize, method, localHeaderOffset })
    totalCompressedSize += compressedSize
    totalDeclaredUncompressedSize += declaredUncompressedSize
    cursor = nameStart + nameLength + extraLength + commentLength
  }

  return { entries, totalCompressedSize, totalDeclaredUncompressedSize, zip64Unresolved }
}

// ── Verdict shape shared by every check in this module ──────────────────────

export interface WorkbookSafetyVerdict {
  ok: boolean
  /** DE — set only when ok is false. */
  reasonDe?: string
  /** EN — set only when ok is false. */
  reasonEn?: string
}

function formatMb(bytes: number): string {
  return (bytes / 1024 / 1024).toFixed(1)
}

function isBufferTooLargeError(e: unknown): boolean {
  return e instanceof Error && (e as NodeJS.ErrnoException).code === 'ERR_BUFFER_TOO_LARGE'
}

/**
 * Locate an entry's compressed data within the buffer. Reads the name/extra
 * LENGTHS FROM THE LOCAL FILE HEADER ITSELF (localHeaderOffset + 26/28) —
 * deliberately NOT from the Central Directory's copy of these fields
 * (KAR-914 F1 review note): a crafted ZIP can legally declare different
 * name/extra lengths in the local header than in the Central Directory: a
 * naive reader that used the Central Directory's lengths to compute the
 * data offset would misalign the window and could read the wrong bytes as
 * compressed data. Every real zip reader (unzip, 7-Zip, Python's zipfile)
 * only trusts the LOCAL header's own lengths for this.
 */
function locateLocalFileHeaderDataOffset(buffer: Buffer, localHeaderOffset: number, entryNameForError: string): number {
  if (localHeaderOffset + LOCAL_FILE_HEADER_FIXED_SIZE > buffer.length) {
    throw new ZipStructureError(
      `ZIP-Lokal-Header für „${entryNameForError}" unvollständig (abgeschnitten). / ` +
        `Truncated ZIP local file header for entry "${entryNameForError}".`,
    )
  }
  const signature = buffer.readUInt32LE(localHeaderOffset)
  if (signature !== LOCAL_FILE_HEADER_SIGNATURE) {
    throw new ZipStructureError(
      `ZIP-Lokal-Header für „${entryNameForError}" fehlerhaft (unerwartete Signatur). / ` +
        `Malformed ZIP local file header for entry "${entryNameForError}" (unexpected signature).`,
    )
  }
  const nameLength = buffer.readUInt16LE(localHeaderOffset + 26)
  const extraLength = buffer.readUInt16LE(localHeaderOffset + 28)
  const dataOffset = localHeaderOffset + LOCAL_FILE_HEADER_FIXED_SIZE + nameLength + extraLength
  if (dataOffset > buffer.length) {
    throw new ZipStructureError(
      `ZIP-Lokal-Header für „${entryNameForError}" unvollständig (Name/Extra über Puffergrenze). / ` +
        `Truncated ZIP local file header for entry "${entryNameForError}" (name/extra beyond buffer).`,
    )
  }
  return dataOffset
}

// ── (a) Zip-ratio / oversized-workbook guard ────────────────────────────────

export interface ZipBombLimits {
  /** Absolute cap on the REAL (verified, not declared) total uncompressed
   * size, independent of ratio — catches a bomb built from many small,
   * individually-unremarkable entries. Also the `maxOutputLength` budget
   * `computeRealUncompressedSize` never allocates past — see module
   * header. */
  maxUncompressedBytes: number
  /** uncompressed:compressed ratio (computed on REAL uncompressed size,
   * post-F1) above which a file is rejected. */
  ratioThreshold: number
  /** The ratio check only engages once the DECLARED compressed size clears
   * this floor — small legitimately-compressible templates (mostly
   * boilerplate XML) can have a high ratio too; only worth rejecting once
   * the absolute compressed size makes the ratio meaningful.
   *
   * Adversarial-review fix (KAR-914 F2): MUST satisfy
   * `ratioMinCompressedBytes * ratioThreshold <= maxUncompressedBytes`, or
   * the ratio branch is mathematically unreachable — any uncompressed total
   * large enough to clear `ratioMinCompressedBytes * ratioThreshold` would
   * already have tripped the absolute cap first, and the ratio check would
   * never be the one that fires. At the previous defaults (floor 10 MB,
   * threshold 100) the branch required >1 GB uncompressed to ever fire,
   * which the 300 MB cap always caught first — meanwhile a 2 MB→290 MB
   * (145:1) file slipped through BOTH checks. 2 MB × 100 = 200 MB stays
   * below the 300 MB cap, keeping the ratio branch genuinely reachable for
   * files that stay under the absolute cap but are still suspiciously
   * over-compressible. */
  ratioMinCompressedBytes: number
}

export const DEFAULT_ZIP_BOMB_LIMITS: ZipBombLimits = {
  maxUncompressedBytes: 300 * 1024 * 1024, // 300 MB
  ratioThreshold: 100, // 100:1
  ratioMinCompressedBytes: 2 * 1024 * 1024, // 2 MB — see doc comment above (F2)
}

export interface RealSizeCheckResult {
  ok: boolean
  /** Sum of REAL (verified via bounded inflate, never declared-only) bytes
   * confirmed across every entry processed before either a rejection or
   * successful completion. */
  realUncompressedSize: number
  reasonDe?: string
  reasonEn?: string
}

/**
 * The F1 fix (see module header): computes the REAL total uncompressed size
 * by actually decompressing every DEFLATE entry with node:zlib's
 * `inflateRawSync({ maxOutputLength })`, never trusting the Central
 * Directory's declared uncompressedSize. The budget is a single shared,
 * DECREMENTING pool across every entry — each entry's `maxOutputLength` is
 * the REMAINING budget, not the full cap, so many medium entries that
 * individually stay under the cap but sum past it are still caught. Fails
 * closed (rejects) on any entry this reader cannot safely verify: an
 * unresolvable local-file-header, an inflate failure (corrupt/truncated
 * stream), or an unexpected compression method (Office/ExcelJS only ever
 * produce store or deflate).
 */
export function computeRealUncompressedSize(
  buffer: Buffer,
  inspection: ZipStructureInspection,
  maxUncompressedBytes: number,
): RealSizeCheckResult {
  let budgetRemaining = maxUncompressedBytes
  let realTotal = 0

  for (const entry of inspection.entries) {
    let dataOffset: number
    try {
      dataOffset = locateLocalFileHeaderDataOffset(buffer, entry.localHeaderOffset, entry.name)
    } catch (e) {
      const detail = e instanceof Error ? e.message : String(e)
      return {
        ok: false,
        realUncompressedSize: realTotal,
        reasonDe: `ZIP-Struktur bei „${entry.name}" nicht sicher auswertbar — Datei aus Vorsicht abgelehnt (${detail}).`,
        reasonEn: `ZIP structure at "${entry.name}" could not be safely verified — file rejected out of caution (${detail}).`,
      }
    }

    // The DECLARED compressedSize only bounds WHICH SLICE of the already
    // in-memory (≤20 MB, wire-capped) buffer to hand to inflate — trusting
    // it here carries no OOM risk even if lied about: too large just means
    // inflate receives extra trailing bytes it ignores once it reaches the
    // stream's own end marker (and the slice is still clamped to
    // buffer.length below); too small surfaces as an inflate failure,
    // itself handled as a fail-closed rejection.
    const sliceEnd = Math.min(dataOffset + Math.max(0, entry.compressedSize), buffer.length)
    const dataSlice = buffer.subarray(dataOffset, sliceEnd > dataOffset ? sliceEnd : buffer.length)

    let entryRealSize: number
    if (entry.method === METHOD_STORE) {
      // Stored (uncompressed) — the slice length IS the real size, no
      // decompression involved, no OOM risk (already buffer-bounded by the
      // 20 MB wire cap upstream of this check).
      entryRealSize = dataSlice.length
    } else if (entry.method === METHOD_DEFLATE) {
      try {
        // maxOutputLength caps zlib's OWN internal output buffer — it
        // throws ERR_BUFFER_TOO_LARGE the INSTANT more output would be
        // produced than allowed, never materializing a larger buffer first
        // (verified empirically, see module header).
        entryRealSize = zlib.inflateRawSync(dataSlice, { maxOutputLength: Math.max(0, budgetRemaining) }).length
      } catch (e) {
        if (isBufferTooLargeError(e)) {
          return {
            ok: false,
            realUncompressedSize: realTotal,
            reasonDe: `Entpackte Gesamtgröße zu groß (Budget ${formatMb(maxUncompressedBytes)} MB überschritten bei „${entry.name}") — Zip-Bomb-Verdacht.`,
            reasonEn: `Decompressed total size too large (budget ${formatMb(maxUncompressedBytes)} MB exceeded at "${entry.name}") — zip-bomb suspicion.`,
          }
        }
        // Any other inflate failure (corrupt/truncated DEFLATE stream, bad
        // window, ...) — cannot safely verify this entry's real size at
        // all; fail closed rather than silently trust the declared
        // metadata for the part we could not confirm.
        return {
          ok: false,
          realUncompressedSize: realTotal,
          reasonDe: `ZIP-Eintrag „${entry.name}" konnte nicht sicher entpackt werden — Datei aus Vorsicht abgelehnt.`,
          reasonEn: `ZIP entry "${entry.name}" could not be safely decompressed — file rejected out of caution.`,
        }
      }
    } else {
      // Any method other than store/deflate (shrink, implode, bzip2,
      // LZMA, ...) is not something Office/ExcelJS ever produces — fail
      // closed rather than silently skip verifying this entry's real size.
      return {
        ok: false,
        realUncompressedSize: realTotal,
        reasonDe: `ZIP-Eintrag „${entry.name}" nutzt eine unerwartete Komprimierungsmethode (${entry.method}) — Datei aus Vorsicht abgelehnt.`,
        reasonEn: `ZIP entry "${entry.name}" uses an unexpected compression method (${entry.method}) — file rejected out of caution.`,
      }
    }

    realTotal += entryRealSize
    budgetRemaining -= entryRealSize
    // Redundant with zlib's own maxOutputLength enforcement for DEFLATE
    // entries (defense in depth), but the ONLY guard for STORE entries
    // (never passed through zlib at all).
    if (realTotal > maxUncompressedBytes) {
      return {
        ok: false,
        realUncompressedSize: realTotal,
        reasonDe: `Entpackte Gesamtgröße zu groß (${formatMb(realTotal)} MB, Limit ${formatMb(maxUncompressedBytes)} MB).`,
        reasonEn: `Decompressed total size too large (${formatMb(realTotal)} MB, limit ${formatMb(maxUncompressedBytes)} MB).`,
      }
    }
  }

  return { ok: true, realUncompressedSize: realTotal }
}

/**
 * Real, output-capped zip-bomb check (KAR-914 F1 fix — see module header for
 * why the original declared-size-only version was unsafe). Requires the
 * full buffer (not just the declared metadata) because it genuinely
 * decompresses every DEFLATE entry, bounded by `limits.maxUncompressedBytes`
 * at every step — never allocates more than that budget for decompressed
 * data regardless of what any entry's Central-Directory metadata claims.
 */
export function evaluateZipBombRisk(
  buffer: Buffer,
  inspection: ZipStructureInspection,
  limits: ZipBombLimits = DEFAULT_ZIP_BOMB_LIMITS,
): WorkbookSafetyVerdict {
  if (inspection.zip64Unresolved) {
    return {
      ok: false,
      reasonDe:
        'ZIP64-Archivstruktur erkannt, die dieser Prüfer nicht sicher auswerten kann — Datei aus Vorsicht abgelehnt.',
      reasonEn: 'ZIP64 archive structure detected that this checker cannot safely evaluate — file rejected out of caution.',
    }
  }

  const realCheck = computeRealUncompressedSize(buffer, inspection, limits.maxUncompressedBytes)
  if (!realCheck.ok) {
    return { ok: false, reasonDe: realCheck.reasonDe, reasonEn: realCheck.reasonEn }
  }

  // Cheap early-exit re-derived from the SAME real numbers just computed
  // (no second decompression pass) — catches a file that stays under the
  // absolute cap but is still suspiciously over-compressible (KAR-914 F2:
  // e.g. 2 MB compressed → 290 MB real, well under the 300 MB cap but a
  // 145:1 ratio). Denominator is the DECLARED compressed size — safe to
  // trust for this (bounded by the wire-capped buffer, see ZipEntryInfo doc).
  if (inspection.totalCompressedSize >= limits.ratioMinCompressedBytes) {
    const ratio =
      inspection.totalCompressedSize === 0 ? Infinity : realCheck.realUncompressedSize / inspection.totalCompressedSize
    if (ratio > limits.ratioThreshold) {
      return {
        ok: false,
        reasonDe: `Verdächtiges Entpackungsverhältnis (${ratio.toFixed(1)}:1 bei ${formatMb(inspection.totalCompressedSize)} MB komprimiert, real ${formatMb(realCheck.realUncompressedSize)} MB entpackt, Limit ${limits.ratioThreshold}:1) — Zip-Bomb-Verdacht.`,
        reasonEn: `Suspicious decompression ratio (${ratio.toFixed(1)}:1 at ${formatMb(inspection.totalCompressedSize)} MB compressed, ${formatMb(realCheck.realUncompressedSize)} MB real decompressed, limit ${limits.ratioThreshold}:1) — zip-bomb suspicion.`,
      }
    }
  }

  return { ok: true }
}

// ── (b) Sheet-dimensions cap ─────────────────────────────────────────────────

export interface SheetDimensionLimits {
  maxRows: number
  maxColumns: number
}

export const DEFAULT_SHEET_DIMENSION_LIMITS: SheetDimensionLimits = {
  maxRows: 200_000,
  maxColumns: 500,
}

/** Pure. Runs AFTER the workbook is loaded (ExcelJS's own rowCount/
 * columnCount counters — no additional read pass) but BEFORE any caller
 * iterates cells (worksheetToGrid et al.) — a violating sheet is rejected
 * wholesale, never partially parsed. */
export function evaluateSheetDimensions(
  sheets: readonly WorkbookSheetSummary[],
  limits: SheetDimensionLimits = DEFAULT_SHEET_DIMENSION_LIMITS,
): WorkbookSafetyVerdict {
  const violations = sheets.filter((s) => s.rowCount > limits.maxRows || s.colCount > limits.maxColumns)
  if (violations.length === 0) return { ok: true }
  const describeDe = (s: WorkbookSheetSummary) => `„${s.name}" (${s.rowCount} Zeilen × ${s.colCount} Spalten)`
  const describeEn = (s: WorkbookSheetSummary) => `"${s.name}" (${s.rowCount} rows × ${s.colCount} columns)`
  return {
    ok: false,
    reasonDe: `Sheet-Dimensionen über dem Limit (max. ${limits.maxRows} Zeilen / ${limits.maxColumns} Spalten je Sheet): ${violations.map(describeDe).join(', ')}.`,
    reasonEn: `Sheet dimensions exceed the limit (max ${limits.maxRows} rows / ${limits.maxColumns} columns per sheet): ${violations.map(describeEn).join(', ')}.`,
  }
}

// ── (c) External workbook links / (d) macro presence ────────────────────────
//
// Both read only the Central Directory entry NAMES already collected by
// inspectZipStructure — no separate pass, no ExcelJS model inspection.

const VBA_PROJECT_ENTRY = 'xl/vbaproject.bin'
const EXTERNAL_LINKS_ENTRY_PREFIX = 'xl/externallinks/'

/** True when the archive contains a VBA project part (.xlsm with macros).
 * Never executed — ExcelJS's xlsx reader has no VBA interpreter and never
 * touches this entry; this is purely an advisory signal for reviewers (see
 * workbookSafetyToPlausibilityIssues). */
export function detectMacroPresence(inspection: ZipStructureInspection): boolean {
  return inspection.entries.some((e) => e.name.toLowerCase() === VBA_PROJECT_ENTRY)
}

/** True when the archive declares an external workbook link
 * (xl/externalLinks/externalLinkN.xml). Kadi-v2 never follows these —
 * ExcelJS resolves formula results from the cached values already stored in
 * the workbook XML and never fetches an external target; this is purely an
 * advisory signal for reviewers. */
export function detectExternalWorkbookLinks(inspection: ZipStructureInspection): boolean {
  return inspection.entries.some((e) => {
    const lower = e.name.toLowerCase()
    return lower.startsWith(EXTERNAL_LINKS_ENTRY_PREFIX) && lower.endsWith('.xml')
  })
}

// ── Orchestrator (actions.ts call site) ──────────────────────────────────────

export interface WorkbookSafetyResult {
  macroPresent: boolean
  externalLinksPresent: boolean
  /** false, wenn auf externe Verknüpfungen gar nicht geprüft werden KONNTE.
   * Nur dann darf `externalLinksPresent: false` nicht als "keine gefunden"
   * gelesen werden. Fehlt das Feld, wurde geprüft (ZIP-Pfad, Default-Verhalten
   * vor Einführung des BIFF-Pfads). */
  externalLinksChecked?: boolean
}

export interface WorkbookPreParseCheck {
  inspection: ZipStructureInspection
  zipBomb: WorkbookSafetyVerdict
  safety: WorkbookSafetyResult
}

// ── Legacy-BIFF-Pfad (.xls, OLE2) ────────────────────────────────────────────
//
// inspectZipStructure() wirft für jeden Nicht-ZIP-Puffer — eine .xls-Datei
// käme also nie bis zum Reader. Diese Variante ist das BIFF-Gegenstück.
//
// Kein Entpack-Cap wie im ZIP-Pfad, weil BIFF keine ZIP-Kompression kennt: es
// gibt keinen Schritt, an dem 1 MB Eingabe zu Gigabytes Ausgabe wird.
//
// Die frühere Fassung dieses Kommentars schloss daraus, der Aufwand sei
// "linear zur Dateigröße" — das war FALSCH und wurde im Review widerlegt. Eine
// BIFF-Datei deklariert ihren Zellbereich in einem eigenen Record (4-Byte-
// Zeilenfeld, also bis ~4,29 Mrd.), den SheetJS ungeprüft übernimmt. Wer daraus
// eine Schleifengrenze macht, baut einen Denial-of-Service aus einer 3-KB-Datei:
// nachgestellt mit 3584 Bytes und 200000x2000 deklariert — 265 Sekunden im
// Loader. Die Gegenmaßnahme sitzt deshalb dort, wo iteriert wird
// (legacy-workbook-shim.ts: Iteration ausschließlich über tatsächlich
// vorhandene Zellen, gekappte Blattmaße), nicht hier.
//
// Diese Prüfung deckt entsprechend nur ab, was VOR dem Parse entscheidbar ist:
// die Puffergröße.

/** OLE2-Verzeichnisnamen stehen als UTF-16LE im Container. Ein VBA-Projekt legt
 * einen Stream namens `_VBA_PROJECT` an — dieselbe Aussage wie `xl/vbaProject.bin`
 * im ZIP-Pfad. */
const OLE2_VBA_STREAM_UTF16 = Buffer.from('_VBA_PROJECT', 'utf16le')

export interface LegacyBiffLimits {
  /** Puffergröße-Obergrenze. Default entspricht dem Upload-Cap
   * (QAF_MAX_FILE_BYTES), damit dieser Pfad nie mehr durchlässt als der. */
  maxBytes: number
}

export const DEFAULT_BIFF_LIMITS: LegacyBiffLimits = {
  maxBytes: 20 * 1024 * 1024,
}

/** Vor-Parse-Prüfung für eine BIFF-Datei. Blockierend ist ausschließlich die
 * Größe; Makro-Erkennung ist wie im ZIP-Pfad nur ein Hinweis. Auf externe
 * Verknüpfungen wird NICHT geprüft — BIFF kodiert sie in SUPBOOK-Records, deren
 * Erkennung ohne vollen Parser zu unzuverlässig wäre, um daraus ein „sauber" zu
 * machen. Das Ergebnis sagt das ausdrücklich (`externalLinksChecked: false`),
 * statt ein ungeprüftes `false` wie einen Befund aussehen zu lassen. */
export function runPreParseLegacyBiffCheck(
  buffer: Buffer,
  limits: LegacyBiffLimits = DEFAULT_BIFF_LIMITS,
): WorkbookPreParseCheck {
  const tooLarge = buffer.byteLength > limits.maxBytes
  return {
    inspection: {
      entries: [],
      totalCompressedSize: buffer.byteLength,
      totalDeclaredUncompressedSize: buffer.byteLength,
      zip64Unresolved: false,
    },
    zipBomb: tooLarge
      ? {
          ok: false,
          reasonDe: `Datei zu groß für den Altformat-Pfad (${buffer.byteLength} Bytes, erlaubt sind ${limits.maxBytes}).`,
          reasonEn: `File too large for the legacy-format path (${buffer.byteLength} bytes, limit ${limits.maxBytes}).`,
        }
      : { ok: true },
    safety: {
      macroPresent: buffer.includes(OLE2_VBA_STREAM_UTF16),
      externalLinksPresent: false,
      externalLinksChecked: false,
    },
  }
}

/** One call, ONE Central-Directory read: the zip-ratio verdict (blocking,
 * real-inflate-verified per F1) plus the macro/external-link signals
 * (advisory) — mirrors the "one ExcelJS load per file" discipline
 * actions.ts already documents for the full parse. Call this BEFORE
 * loadExcelWorkbook. */
export function runPreParseWorkbookSafetyCheck(
  buffer: Buffer,
  limits: ZipBombLimits = DEFAULT_ZIP_BOMB_LIMITS,
): WorkbookPreParseCheck {
  const inspection = inspectZipStructure(buffer)
  return {
    inspection,
    zipBomb: evaluateZipBombRisk(buffer, inspection, limits),
    safety: {
      macroPresent: detectMacroPresence(inspection),
      externalLinksPresent: detectExternalWorkbookLinks(inspection),
    },
  }
}

// ── Persistence bridge (PlausibilityIssue, KAR-906 bilingual pattern) ──────
//
// Same shape/pattern as template-fingerprint.ts's
// templateFingerprintToPlausibilityIssue: pure, `side`/`fileLabel`-aware,
// never blocks (blocking already happened via evaluateZipBombRisk/
// evaluateSheetDimensions before this file's issues are ever computed).

export function workbookSafetyToPlausibilityIssues(
  result: WorkbookSafetyResult,
  side: 'ALT' | 'NEU',
  fileLabel: string,
): PlausibilityIssue[] {
  const issues: PlausibilityIssue[] = []
  if (result.macroPresent) {
    issues.push({
      type: 'security_macro_present',
      severity: 'pruefen',
      step: `${side} · ${fileLabel}`,
      explanation: `„${fileLabel}" enthält ein VBA-Makroprojekt (xl/vbaProject.bin). Kadi-v2 führt Makros nie aus — zur Reviewer-Awareness dokumentiert.`,
      explanationEn: `"${fileLabel}" contains a VBA macro project (xl/vbaProject.bin). Kadi-v2 never executes macros — documented for reviewer awareness.`,
    })
  }
  if (result.externalLinksChecked === false) {
    issues.push({
      type: 'security_external_links_unchecked',
      severity: 'pruefen',
      step: `${side} · ${fileLabel}`,
      explanation: `„${fileLabel}" liegt im alten Binärformat (.xls). Auf externe Arbeitsmappen-Verknüpfungen wurde NICHT geprüft — das ist ausdrücklich „nicht geprüft", nicht „keine gefunden".`,
      explanationEn: `"${fileLabel}" is in the legacy binary format (.xls). External workbook links were NOT checked — this means "not checked", not "none found".`,
    })
  }
  if (result.externalLinksPresent) {
    issues.push({
      type: 'security_external_links',
      severity: 'pruefen',
      step: `${side} · ${fileLabel}`,
      explanation: `„${fileLabel}" referenziert externe Arbeitsmappen-Verknüpfungen (xl/externalLinks/). Kadi-v2 folgt diesen Links nie automatisch — zur Prüfung markiert.`,
      explanationEn: `"${fileLabel}" references external workbook links (xl/externalLinks/). Kadi-v2 never follows these links automatically — flagged for review.`,
    })
  }
  return issues
}