// G60 Excel export (KAR-913/P4.3) — the G60-Detailvergleich counterpart to the
// existing 8-sheet summary export (../export.ts). Deliberately a SEPARATE
// builder, not a mode branch inside buildQafExportWorkbook: G60's data shape
// (per-tab aggregates, INPUT rate card, production KPIs) has no equivalent in
// the summary QafComparisonResult, so sharing one function would mean a large
// optional-everything input type instead of two focused ones.
//
// Sheets:
//   1. README            — Deckblatt: Engine-Version, ALT/NEU-Dateien,
//                           Rates-/Volumes-Prämissen (Master-Prompt §17/§18).
//   2. Tab_Übersicht      — Delta-Übersicht über alle Tabs: die 11 Row-41-
//                           Kennzahlen je gemeinsamem Kostenreiter (identisch
//                           zu G60Analysis.rows, dem On-Screen-Datensatz der
//                           Metrik-Tabelle), inkl. Quellzell-Provenance
//                           (KAR-894/P1.3 sourceCells, sofern persistiert).
//   3. Produktion         — Produktionssicht je Kostenreiter (Zykluszeit,
//                           Mitarbeiter, Scrap, MSS, Ineffizienz).
//   4. Kalkulator_Treiber — INPUT-Ratenkarte-Diff (G60Analysis.driverDiff) —
//                           die "Kalkulator-Ergebnisse" aus dem Backlog-Item.
//   5. Struktur_Plausibilität — G60-Struktur-Guard-Funde (P0.3/P1.4), bilingual
//                           (KAR-906-Muster).
//
// Deliberately NOT exported: live Szenario-Editor-Overrides. The scenario
// engine (scenario.ts) is session-only — no qaf_g60_scenario table exists,
// overrides never leave the browser (see qaf-g60-detail.tsx: "Eingaben gelten
// je Sitzung"). Exporting them would violate the "reproducible from persisted
// data, on-screen result == download" acceptance criterion, since there is no
// single canonical on-screen scenario state to reproduce. See PR body.
//
// Server-only (ExcelJS imported dynamically, same discipline as ../export.ts).

import type { G60Analysis } from './analyze'
import type { G60Rates, G60TabAggregate, G60Volumes } from './parser'
import type { PlausibilityIssue } from '../plausibility'
import { FILL_RISE, FILL_FALL, FILL_WARN, addSheet } from '../xlsx-style-helpers'

export interface G60ExportInput {
  /** Caller-supplied timestamp label (no Date in engine code). */
  generatedAtLabel: string
  baselineFileName: string | null
  comparisonFileName: string | null
  analysis: G60Analysis
  /** Raw per-tab aggregates (ALT/NEU) — carries `sourceCells` per metric key,
   * which G60Analysis.rows does not retain; used for the Tab_Übersicht
   * provenance columns. */
  baselineTabs: Record<string, G60TabAggregate>
  comparisonTabs: Record<string, G60TabAggregate>
  baselineRates: G60Rates
  comparisonRates: G60Rates
  /** Stückzahlen volumes — repricing (NEU) side, matches analyzeG60Pair's
   * annual-impact computation ("V11: Annual impact uses the REPRICING volumes"). */
  volumes: G60Volumes
  structureIssues: PlausibilityIssue[]
  /** Persisted qaf_comparison.engine_version JSONB, shown verbatim. */
  engineVersion: unknown
}

// FILL_CRIT is the SAME ARGB value as the shared FILL_RISE but under a
// distinct name ("Kritisch/Blockiert" vs "cost increase") — kept as a local
// alias rather than folded into the shared palette (KAR-949 review Finding
// 2: document divergent-looking-but-actually-same values instead of
// silently unifying the names).
const FILL_CRIT = FILL_RISE // Status-Red strong — Kritisch/Blockiert

/** G60 row-41 metrics are all cost-like (higher = more expensive) — unlike the
 * summary/Fertigungskosten export, no field is HIGHER_IS_BETTER here. */
function g60DeltaFill(delta: number | null): string | null {
  if (delta === null || delta === 0) return null
  return delta > 0 ? FILL_RISE : FILL_FALL
}

const RATE_LABELS: Record<keyof G60Rates, string> = {
  ovFK_d: 'Gemeinkosten Fertigung (directed)',
  ovMAT_d: 'Gemeinkosten Material (directed)',
  pfFK_d: 'Gewinn Fertigung (directed)',
  pfMAT_d: 'Gewinn Material (directed)',
  ovFK_s: 'Gemeinkosten Fertigung (sourced)',
  ovMAT_s: 'Gemeinkosten Material (sourced)',
  pfFK_s: 'Gewinn Fertigung (sourced)',
  pfMAT_s: 'Gewinn Material (sourced)',
}
const RATE_KEYS = Object.keys(RATE_LABELS) as Array<keyof G60Rates>

export async function buildG60ExportWorkbook(input: G60ExportInput): Promise<ArrayBuffer> {
  const ExcelJS = (await import('exceljs')).default
  const wb = new ExcelJS.Workbook()
  wb.creator = 'KADi SupplierPulse — QAF-Differences (G60)'

  // 1. README — Deckblatt.
  const readme = wb.addWorksheet('README')
  readme.columns = [{ width: 30 }, { width: 34 }, { width: 34 }]
  readme.addRow(['QAF G60-Detailvergleich Export', '', '']).font = { bold: true, size: 14 }
  readme.addRow(['Erstellt', input.generatedAtLabel])
  readme.addRow(['ALT-Datei', input.baselineFileName ?? '—'])
  readme.addRow(['NEU-Datei', input.comparisonFileName ?? '—'])
  readme.addRow(['Kostenreiter verglichen', String(input.analysis.common.length)])
  readme.addRow(['Engine-Version', JSON.stringify(input.engineVersion ?? {})])
  readme.addRow([])
  readme.addRow(['Rates-Prämissen (INPUT!C22–C29)', 'ALT', 'NEU']).font = { bold: true }
  for (const key of RATE_KEYS) {
    readme.addRow([RATE_LABELS[key], input.baselineRates[key], input.comparisonRates[key]])
  }
  readme.addRow([])
  readme.addRow(['Stückzahlen-Prämisse (NEU/Repricing)', 'Jahr', 'Menge']).font = { bold: true }
  input.volumes.years.forEach((year, i) => {
    readme.addRow(['', year, input.volumes.vol[i] ?? 0])
  })
  readme.addRow([])
  readme.addRow(['Legende', '']).font = { bold: true }
  readme.addRow(['Kostenanstieg (ungünstig)', 'rot']).getCell(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: FILL_RISE } }
  readme.addRow(['Kostensenkung (günstig)', 'grün']).getCell(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: FILL_FALL } }
  readme.addRow(['Prüfen', 'gelb']).getCell(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: FILL_WARN } }
  readme.addRow(['Kritisch / Blockiert', 'rot']).getCell(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: FILL_CRIT } }

  // 2. Tab_Übersicht — Delta-Übersicht über alle Tabs.
  const tabUebersicht = addSheet(wb, 'Tab_Übersicht', [
    { header: 'Kostenreiter', key: 'tab', width: 14 },
    { header: 'Teil', key: 'part', width: 16 },
    { header: 'Kennzahl', key: 'headline', width: 24 },
    { header: 'Bereich', key: 'bereich', width: 16 },
    { header: 'ALT', key: 'basis', width: 14, numFmt: '#,##0.00' },
    { header: 'NEU', key: 'repr', width: 14, numFmt: '#,##0.00' },
    { header: 'Delta abs', key: 'delta', width: 14, numFmt: '#,##0.00' },
    { header: 'Delta %', key: 'deltaPct', width: 12, numFmt: '0.0%' },
    { header: 'Flag', key: 'flag', width: 16 },
    { header: 'Quellzelle ALT', key: 'cellAlt', width: 18 },
    { header: 'Quellzelle NEU', key: 'cellNeu', width: 18 },
  ])
  for (const r of input.analysis.rows) {
    const cellAlt = input.baselineTabs[r.reiter]?.sourceCells?.[r.key] ?? ''
    const cellNeu = input.comparisonTabs[r.reiter]?.sourceCells?.[r.key] ?? ''
    const row = tabUebersicht.addRow({
      tab: r.reiter,
      part: r.part,
      headline: r.headline,
      bereich: r.bereich,
      basis: r.basis,
      repr: r.repr,
      delta: r.delta,
      deltaPct: r.deltaPct,
      flag: r.flag,
      cellAlt,
      cellNeu,
    })
    const fill = g60DeltaFill(r.delta)
    if (fill) row.getCell('delta').fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: fill } }
    if (r.flag === 'PART-SPECIFIC') row.getCell('flag').fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: FILL_WARN } }
  }

  // 3. Produktion — Produktionssicht je Kostenreiter.
  const produktion = addSheet(wb, 'Produktion', [
    { header: 'Kostenreiter', key: 'tab', width: 14 },
    { header: 'Teil', key: 'part', width: 16 },
    { header: 'Zykluszeit ALT (s)', key: 'cycB', width: 16, numFmt: '#,##0.00' },
    { header: 'Zykluszeit NEU (s)', key: 'cycR', width: 16, numFmt: '#,##0.00' },
    { header: '# MA ALT', key: 'empB', width: 12, numFmt: '#,##0.00' },
    { header: '# MA NEU', key: 'empR', width: 12, numFmt: '#,##0.00' },
    { header: 'Scrap ALT', key: 'scrB', width: 12, numFmt: '0.00%' },
    { header: 'Scrap NEU', key: 'scrR', width: 12, numFmt: '0.00%' },
    { header: 'MSS ALT', key: 'mrB', width: 12, numFmt: '#,##0.00' },
    { header: 'MSS NEU', key: 'mrR', width: 12, numFmt: '#,##0.00' },
    { header: 'Ineffizienz ALT', key: 'inefB', width: 14, numFmt: '0.00%' },
    { header: 'Ineffizienz NEU', key: 'inefR', width: 14, numFmt: '0.00%' },
  ])
  for (const p of input.analysis.prod) {
    produktion.addRow({
      tab: p.t,
      part: p.part,
      cycB: p.cyc_b,
      cycR: p.cyc_r,
      empB: p.emp_b,
      empR: p.emp_r,
      scrB: p.scr_b,
      scrR: p.scr_r,
      mrB: p.mr_b,
      mrR: p.mr_r,
      inefB: p.inef_b,
      inefR: p.inef_r,
    })
  }

  // 4. Kalkulator_Treiber — INPUT-Ratenkarte-Diff (die "Kalkulator-Ergebnisse").
  const kalkulator = addSheet(wb, 'Kalkulator_Treiber', [
    { header: 'Code', key: 'code', width: 10 },
    { header: 'Bezeichnung', key: 'label', width: 36 },
    { header: 'ALT', key: 'basis', width: 14, numFmt: '#,##0.00' },
    { header: 'NEU', key: 'repr', width: 14, numFmt: '#,##0.00' },
    { header: 'Δ %', key: 'chg', width: 12, numFmt: '0.0%' },
  ])
  for (const d of input.analysis.driverDiff) {
    const row = kalkulator.addRow({ code: d.code, label: d.label, basis: d.basis, repr: d.repr, chg: d.chg })
    const fill = d.chg === null ? null : d.chg > 0 ? FILL_RISE : d.chg < 0 ? FILL_FALL : null
    if (fill) row.getCell('chg').fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: fill } }
  }

  // 5. Struktur_Plausibilität — G60-Struktur-Guard-Funde, bilingual (KAR-906-Muster).
  const plausi = addSheet(wb, 'Struktur_Plausibilität', [
    { header: 'Typ', key: 'typ', width: 30 },
    { header: 'Severity', key: 'sev', width: 12 },
    { header: 'Tab/Step', key: 'step', width: 18 },
    { header: 'Feld', key: 'field', width: 18 },
    { header: 'Erklärung', key: 'expl', width: 70 },
    { header: 'Explanation (EN)', key: 'explEn', width: 70 },
  ])
  for (const i of input.structureIssues) {
    const row = plausi.addRow({
      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_CRIT } }
    else if (i.severity === 'pruefen') row.getCell('sev').fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: FILL_WARN } }
  }

  return wb.xlsx.writeBuffer()
}