// QAF-Differences Excel export (KAR-799, spec B9 — reference quality).
//
// Builds the 8 reference sheets (README, Import_Log, Zusammenfassung_Vergleich,
// Fertigungskosten_Vergleich, Neu_Entfallen, Delta_Highlights,
// Top_Treiber_Prozess, Plausibilitätscheck) with frozen headers, autofilter,
// number/percent formats and brand-CI status fills. Server-only (ExcelJS imported
// dynamically). ARGB color literals are exempt from check:hardcoded-colors.
//
// Net-new: the existing qaf-template route only writes a blank single-sheet
// template. Pure w.r.t. inputs — produces a workbook buffer.

import { exportProfileStampLineDe } from './export-profiles'
import type { QafComparisonResult } from './compare'
import { HIGHER_IS_BETTER_FIELDS } from '@/lib/qaf/comparison'
import { FILL_RISE, FILL_FALL, FILL_NEW, FILL_REMOVED, FILL_WARN, addSheet } from './xlsx-style-helpers'

export interface QafExportInput {
  /** Caller-supplied timestamp label (no Date in engine code). */
  generatedAtLabel: string
  files: Array<{ fileName: string; partNumber: string | null; status: string }>
  comparisons: QafComparisonResult[]
}

const DIRECTIONAL_STATUSES = new Set(['anstieg', 'senkung', 'auffaellig_10', 'auffaellig_25', 'kritisch_50'])

/**
 * Ampel-Füllung für eine Feld-Zeile (KAR-833).
 * Die Band-Status (auffaellig_10/25, kritisch_50) tragen keine Richtung — die
 * kommt aus dem Vorzeichen des Deltas. Rot heißt "ungünstig", nicht "gestiegen":
 * bei HIGHER_IS_BETTER-Feldern (z. B. Teile/Zyklus) ist ein Anstieg grün.
 */
export function statusFill(status: string, deltaAbsolute?: number | null, field?: string): string | null {
  if (status === 'neu') return FILL_NEW
  if (status === 'entfallen') return FILL_REMOVED
  if (!DIRECTIONAL_STATUSES.has(status)) return null

  const delta = deltaAbsolute ?? (status === 'senkung' ? -1 : status === 'anstieg' ? 1 : 0)
  if (delta === 0) return null
  const higherIsBetter = field !== undefined && HIGHER_IS_BETTER_FIELDS.has(field)
  const unfavorable = delta > 0 !== higherIsBetter
  return unfavorable ? FILL_RISE : FILL_FALL
}

export async function buildQafExportWorkbook(input: QafExportInput): Promise<ArrayBuffer> {
  const ExcelJS = (await import('exceljs')).default
  const wb = new ExcelJS.Workbook()
  wb.creator = 'KADi SupplierPulse — QAF-Differences'
  // Loop 12: der profilübergreifende Versions-Stempel — als Workbook-Property
  // UND als README-Zeile (Properties sieht in Excel kaum jemand an).
  wb.subject = exportProfileStampLineDe('xlsx_workbook')

  // 1. README
  const readme = wb.addWorksheet('README')
  readme.columns = [{ width: 28 }, { width: 80 }]
  readme.addRow(['QAF-Differences Export', '']).font = { bold: true, size: 14 }
  readme.addRow(['Exportprofil', exportProfileStampLineDe('xlsx_workbook')])
  readme.addRow(['Erstellt', input.generatedAtLabel])
  readme.addRow(['Dateien', String(input.files.length)])
  readme.addRow(['Vergleiche', String(input.comparisons.length)])
  readme.addRow([])
  readme.addRow(['Legende', ''])
  readme.addRow(['Anstieg / auffällig', 'rot']).getCell(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: FILL_RISE } }
  readme.addRow(['Senkung', 'grün']).getCell(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: FILL_FALL } }
  readme.addRow(['Neu', 'blau']).getCell(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: FILL_NEW } }
  readme.addRow(['Entfallen', 'grau']).getCell(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: FILL_REMOVED } }
  readme.addRow(['Plausibilität', 'gelb']).getCell(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: FILL_WARN } }

  // 2. Import_Log
  const log = addSheet(wb, 'Import_Log', [
    { header: 'Datei', key: 'file', width: 48 },
    { header: 'Sachnummer', key: 'pn', width: 18 },
    { header: 'Status', key: 'status', width: 16 },
  ])
  for (const f of input.files) log.addRow({ file: f.fileName, pn: f.partNumber ?? '—', status: f.status })

  // 3. Zusammenfassung_Vergleich
  const summary = addSheet(wb, 'Zusammenfassung_Vergleich', [
    { header: 'Sachnummer', key: 'pn', width: 18 },
    { header: 'ALT', key: 'alt', width: 28 },
    { header: 'NEU', key: 'neu', width: 28 },
    { header: 'Baseline-Status', key: 'bs', width: 18 },
    { header: 'Management-Fazit', key: 'fazit', width: 90 },
  ])
  for (const c of input.comparisons) {
    summary.addRow({
      pn: c.partNumber ?? '—',
      alt: c.altRef.fileName,
      neu: c.neuRef.fileName,
      bs: c.matchReviewCount > 0 ? 'review' : 'ok',
      fazit: c.rootCause.managementSummary,
    })
  }

  // 4. Fertigungskosten_Vergleich
  const fk = addSheet(wb, 'Fertigungskosten_Vergleich', [
    { header: 'Sachnummer', key: 'pn', width: 16 },
    { header: 'Prozessschritt', key: 'step', width: 28 },
    { header: 'Feld', key: 'field', width: 18 },
    { header: 'ALT', key: 'alt', width: 14, numFmt: '#,##0.00' },
    { header: 'NEU', key: 'neu', width: 14, numFmt: '#,##0.00' },
    { header: 'Delta abs', key: 'da', width: 14, numFmt: '#,##0.00' },
    { header: 'Delta %', key: 'dp', width: 12, numFmt: '0.0%' },
    { header: 'Delta %-Pkt', key: 'pp', width: 12, numFmt: '0.00' },
    { header: 'Status', key: 'status', width: 16 },
  ])
  for (const c of input.comparisons) {
    for (const sc of c.stepComparisons) {
      if (sc.altIndex === null || sc.neuIndex === null) continue
      for (const d of sc.fieldDiffs) {
        const row = fk.addRow({
          pn: c.partNumber ?? '—',
          step: sc.stepLabel,
          field: String(d.field),
          alt: d.altValue,
          neu: d.neuValue,
          da: d.deltaAbsolute,
          dp: d.deltaPercent,
          pp: d.deltaPercentagePoints,
          status: d.status,
        })
        const fill = statusFill(d.status, d.deltaAbsolute, String(d.field))
        if (fill) row.getCell('status').fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: fill } }
      }
    }
  }

  // 5. Neu_Entfallen
  const struct = addSheet(wb, 'Neu_Entfallen', [
    { header: 'Sachnummer', key: 'pn', width: 16 },
    { header: 'Art', key: 'art', width: 22 },
    { header: 'Prozessschritt', key: 'step', width: 36 },
  ])
  for (const c of input.comparisons) {
    const sc = c.structureChanges
    for (const s of sc.new)
      struct.addRow({ pn: c.partNumber ?? '—', art: 'neu', step: s }).getCell('art').fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: FILL_NEW } }
    for (const s of sc.removed)
      struct.addRow({ pn: c.partNumber ?? '—', art: 'entfallen', step: s }).getCell('art').fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: FILL_REMOVED } }
    for (const s of sc.possible) struct.addRow({ pn: c.partNumber ?? '—', art: 'mögliche Umstrukturierung', step: s })
  }

  // 6. Delta_Highlights
  const hl = addSheet(wb, 'Delta_Highlights', [
    { header: 'Sachnummer', key: 'pn', width: 16 },
    { header: 'Prozessschritt', key: 'step', width: 28 },
    { header: 'Feld', key: 'field', width: 16 },
    { header: 'Delta abs', key: 'da', width: 14, numFmt: '#,##0.00' },
    { header: 'Delta %', key: 'dp', width: 12, numFmt: '0.0%' },
  ])
  for (const c of input.comparisons) {
    for (const d of c.rootCause.topAbsoluteDrivers) {
      hl.addRow({ pn: c.partNumber ?? '—', step: d.stepLabel, field: String(d.field), da: d.deltaAbsolute, dp: d.deltaPercent })
    }
  }

  // 7. Top_Treiber_Prozess
  const drivers = addSheet(wb, 'Top_Treiber_Prozess', [
    { header: 'Sachnummer', key: 'pn', width: 16 },
    { header: 'Treiber-Typ', key: 'typ', width: 16 },
    { header: 'Prozessschritt', key: 'step', width: 28 },
    { header: 'Feld', key: 'field', width: 16 },
    { header: 'Delta abs', key: 'da', width: 14, numFmt: '#,##0.00' },
    { header: 'Delta %', key: 'dp', width: 12, numFmt: '0.0%' },
  ])
  for (const c of input.comparisons) {
    for (const d of c.rootCause.topAbsoluteDrivers)
      drivers.addRow({ pn: c.partNumber ?? '—', typ: 'absolut', step: d.stepLabel, field: String(d.field), da: d.deltaAbsolute, dp: d.deltaPercent })
    for (const d of c.rootCause.topRelativeDrivers)
      drivers.addRow({ pn: c.partNumber ?? '—', typ: 'relativ', step: d.stepLabel, field: String(d.field), da: d.deltaAbsolute, dp: d.deltaPercent })
  }

  // 8. Plausibilitätscheck
  // KAR-906/P3.2: this sheet is fed directly from the live
  // QafComparisonResult.plausibility (PlausibilityIssue[]) — NOT a persisted/
  // bilingual-encoded DB row (that encoding only happens at the
  // persistence-mapper.ts boundary) — so `i.explanationEn` is already the
  // plain EN string, no decodeBilingual needed here. No existing
  // export-language selection mechanism exists in this module (checked: no
  // locale param on QafExportInput/addSheet) — per task instruction
  // ("konservativ: zusätzliche EN-Spalte nur wenn es das Layout nicht
  // bricht"), this adds one additive trailing column rather than
  // reformatting the sheet; every pre-KAR-906 reader ignoring the new column
  // is unaffected.
  const plausi = addSheet(wb, 'Plausibilitätscheck', [
    { header: 'Sachnummer', key: 'pn', width: 16 },
    { header: 'Typ', key: 'typ', width: 24 },
    { header: 'Severity', key: 'sev', width: 12 },
    { header: 'Prozessschritt', key: 'step', width: 24 },
    { header: 'Feld', key: 'field', width: 18 },
    { header: 'Erklärung', key: 'expl', width: 70 },
    { header: 'Explanation (EN)', key: 'explEn', width: 70 },
  ])
  for (const c of input.comparisons) {
    for (const i of c.plausibility) {
      const row = plausi.addRow({
        pn: c.partNumber ?? '—',
        typ: i.type,
        sev: i.severity,
        step: i.step ?? '',
        field: i.field ?? '',
        expl: i.explanation,
        explEn: i.explanationEn ?? '',
      })
      if (i.severity === 'kritisch')
        row.getCell('sev').fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: FILL_RISE } }
      else if (i.severity === 'pruefen')
        row.getCell('sev').fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: FILL_WARN } }
    }
  }

  return wb.xlsx.writeBuffer()
}