// KAR-913/P4.3: G60 Excel export — Delta-Übersicht über alle Tabs, Produktion,
// Kalkulator-Treiber und Struktur-Plausibilität, aus synthetischen G60-Daten
// (Zellen-Assertions wie das bestehende summary-export.test.ts) + einem echten
// Persistenz→Export-Roundtrip (buildG60Rows → rehydrateG60File →
// analyzeG60Pair → buildG60ExportWorkbook), keine echten Dateien/Werte.

import { describe, it, expect } from 'vitest'
import ExcelJS from 'exceljs'
import { parseG60Workbook, extractFullTab, sheetFromCells, workbookFromSheets } from '../parser'
import { analyzeG60Pair } from '../analyze'
import { buildG60Rows } from '../persistence'
import { rehydrateG60File } from '../rehydrate'
import { buildG60ExportWorkbook, type G60ExportInput } from '../export'
import { makeWorkbook, tabCells, RATES_CELLS } from './fixtures'
import type { PlausibilityIssue } from '../../plausibility'

async function loadWorkbook(buf: ArrayBuffer): Promise<ExcelJS.Workbook> {
  const wb = new ExcelJS.Workbook()
  await wb.xlsx.load(buf as unknown as ArrayBuffer)
  return wb
}

function cellTexts(ws: ExcelJS.Worksheet): string[] {
  const out: string[] = []
  ws.eachRow((row) => {
    for (const v of row.values as unknown[]) if (v !== null && v !== undefined) out.push(String(v))
  })
  return out
}

const basisParse = parseG60Workbook(makeWorkbook(100, 200))
const reprParse = parseG60Workbook(makeWorkbook(110, 260, 22, 33)) // ratios 1.1 / 1.3 → 2_2 PART-SPECIFIC
const analysis = analyzeG60Pair(basisParse, reprParse)

const structureIssues: PlausibilityIssue[] = [
  {
    type: 'g60_tab_structure_soft_mismatch',
    severity: 'pruefen',
    step: '2_2',
    explanation: 'Spaltenkopf weicht ab, Wert bleibt aber lesbar.',
    explanationEn: 'Column header differs but the value is still readable.',
  },
]

function baseInput(overrides: Partial<G60ExportInput> = {}): G60ExportInput {
  return {
    generatedAtLabel: '2026-07-10 09:00',
    baselineFileName: 'ALT_G60.xlsx',
    comparisonFileName: 'NEU_G60.xlsx',
    analysis,
    baselineTabs: basisParse.tabs,
    comparisonTabs: reprParse.tabs,
    baselineRates: basisParse.rates,
    comparisonRates: reprParse.rates,
    volumes: reprParse.volumes,
    structureIssues,
    engineVersion: { parser: '1.3.0', baselineStatus: 'baseline_review' },
    ...overrides,
  }
}

const REQUIRED_SHEETS = ['README', 'Tab_Übersicht', 'Produktion', 'Kalkulator_Treiber', 'Struktur_Plausibilität']

describe('buildG60ExportWorkbook', () => {
  it('produces the 5 G60 export sheets', async () => {
    const wb = await loadWorkbook(await buildG60ExportWorkbook(baseInput()))
    const names = wb.worksheets.map((w) => w.name)
    for (const s of REQUIRED_SHEETS) expect(names).toContain(s)
  })

  it('README shows engine version, filenames and the rate-card premises', async () => {
    const wb = await loadWorkbook(await buildG60ExportWorkbook(baseInput()))
    const ws = wb.getWorksheet('README')!
    const texts = cellTexts(ws)
    expect(texts.some((t) => t.includes('1.3.0'))).toBe(true)
    expect(texts).toContain('ALT_G60.xlsx')
    expect(texts).toContain('NEU_G60.xlsx')
    // RATES_CELLS.C22 = 0.1 (ovFK_d) — both sides identical in this fixture.
    expect(texts.some((t) => t.includes('0.1') || t.includes('0,1'))).toBe(true)
  })

  it('README shows the Stückzahlen volumes (years + quantities)', async () => {
    const wb = await loadWorkbook(await buildG60ExportWorkbook(baseInput()))
    const ws = wb.getWorksheet('README')!
    const texts = cellTexts(ws)
    expect(texts).toContain('2026')
    expect(texts).toContain('2027')
  })

  it('Tab_Übersicht lists every metric row with delta and PART-SPECIFIC flag', async () => {
    const wb = await loadWorkbook(await buildG60ExportWorkbook(baseInput()))
    const ws = wb.getWorksheet('Tab_Übersicht')!
    let salesRowFound = false
    let flaggedFound = false
    ws.eachRow((row) => {
      const vals = (row.values as unknown[]).map((v) => String(v ?? ''))
      if (vals.includes('2_2') && vals.includes('Quotation Price')) {
        salesRowFound = true
        expect(vals.some((v) => v.includes('20'))).toBe(true)
        expect(vals.some((v) => v.includes('22'))).toBe(true)
      }
      if (vals.includes('PART-SPECIFIC')) flaggedFound = true
    })
    expect(salesRowFound).toBe(true)
    expect(flaggedFound).toBe(true)
  })

  it('Tab_Übersicht carries the source-cell provenance columns (KAR-894/P1.3 sourceCells)', async () => {
    const wb = await loadWorkbook(await buildG60ExportWorkbook(baseInput()))
    const ws = wb.getWorksheet('Tab_Übersicht')!
    const header = (ws.getRow(1).values as unknown[]).map((v) => String(v ?? ''))
    expect(header).toContain('Quellzelle ALT')
    expect(header).toContain('Quellzelle NEU')
    let found = false
    ws.eachRow((row) => {
      const vals = (row.values as unknown[]).map((v) => String(v ?? ''))
      if (vals.some((v) => v === '2_2!DJ41')) found = true
    })
    expect(found).toBe(true)
  })

  it('highlights an unfavorable (cost-increasing) delta on the Tab_Übersicht sheet', async () => {
    const wb = await loadWorkbook(await buildG60ExportWorkbook(baseInput()))
    const ws = wb.getWorksheet('Tab_Übersicht')!
    // Column-key metadata (ws.columns[].key) is a write-time-only ExcelJS
    // convenience — a reloaded worksheet (xlsx.load) has no key→column map,
    // so getCell must use the 1-based numeric index here (7 = "Delta abs",
    // per the addSheet(...) column order in export.ts).
    const DELTA_COL = 7
    let highlighted = false
    ws.eachRow((row, num) => {
      if (num === 1) return
      const vals = (row.values as unknown[]).map((v) => String(v ?? ''))
      if (vals.includes('2_2') && vals.includes('Quotation Price')) {
        const fill = row.getCell(DELTA_COL).fill
        if (fill && fill.type === 'pattern') highlighted = true
      }
    })
    expect(highlighted).toBe(true)
  })

  it('Produktion sheet lists per-tab cycle time / employees / scrap / machine rate', async () => {
    const wb = await loadWorkbook(await buildG60ExportWorkbook(baseInput()))
    const ws = wb.getWorksheet('Produktion')!
    let found = false
    ws.eachRow((row) => {
      const vals = (row.values as unknown[]).map((v) => String(v ?? ''))
      if (vals.includes('2_2') && vals.some((v) => v.includes('100')) && vals.some((v) => v.includes('110'))) {
        found = true
      }
    })
    expect(found).toBe(true)
  })

  it('Kalkulator_Treiber lists a rate-card driver change', async () => {
    const wbBasis = parseG60Workbook(makeWorkbook(100, 200))
    const wbRepr = workbookFromSheets({
      INPUT: sheetFromCells({ ...RATES_CELLS, C42: 45 }), // Lohnsatz DE geändert
      Stückzahlen: sheetFromCells({ C4: 2026, D4: 2027, C5: 10000, D5: 20000 }),
      '2_2': sheetFromCells(tabCells({ part: 'P-A', sales: 22, w: 4, at15: 110 })),
      '10_2': sheetFromCells(tabCells({ part: 'P-B', sales: 33, w: 4, at15: 260 })),
      SBM_Matrix: sheetFromCells({}),
      Notizen: sheetFromCells({}),
    })
    const reprChanged = parseG60Workbook(wbRepr)
    const a = analyzeG60Pair(wbBasis, reprChanged)
    const wb = await loadWorkbook(
      await buildG60ExportWorkbook(
        baseInput({ analysis: a, comparisonTabs: reprChanged.tabs, comparisonRates: reprChanged.rates }),
      ),
    )
    const ws = wb.getWorksheet('Kalkulator_Treiber')!
    let found = false
    ws.eachRow((row) => {
      const vals = (row.values as unknown[]).map((v) => String(v ?? ''))
      if (vals.includes('C42') && vals.some((v) => v.includes('38')) && vals.some((v) => v.includes('45'))) found = true
    })
    expect(found).toBe(true)
  })

  it('Struktur_Plausibilität lists structure findings bilingually (KAR-906 pattern)', async () => {
    const wb = await loadWorkbook(await buildG60ExportWorkbook(baseInput()))
    const ws = wb.getWorksheet('Struktur_Plausibilität')!
    const header = (ws.getRow(1).values as unknown[]).map((v) => String(v ?? ''))
    expect(header).toContain('Erklärung')
    expect(header).toContain('Explanation (EN)')
    let found = false
    ws.eachRow((row) => {
      const vals = (row.values as unknown[]).map((v) => String(v ?? ''))
      if (vals.some((v) => v.includes('Spaltenkopf weicht ab')) && vals.some((v) => v.includes('Column header differs')))
        found = true
    })
    expect(found).toBe(true)
  })

  it('has a frozen header row + autofilter on the Tab_Übersicht sheet', async () => {
    const wb = await loadWorkbook(await buildG60ExportWorkbook(baseInput()))
    const ws = wb.getWorksheet('Tab_Übersicht')!
    expect(ws.views?.[0]?.state).toBe('frozen')
    expect(ws.autoFilter).toBeTruthy()
  })
})

// Roundtrip: persisted #105 rows → rehydrate → analyze → export must reproduce
// the direct-parse export byte-for-byte on the data cells that matter (KAR-913
// acceptance criterion: "On-Screen-Ergebnis und Download sind deterministisch
// identisch"). No re-parse of an original xlsx — only DB-shaped rows in.
describe('buildG60ExportWorkbook — Persistenz→Export-Roundtrip', () => {
  it('reproduces the direct-parse export from persisted rows', async () => {
    const wbBasis = makeWorkbook(100, 200)
    const wbRepr = makeWorkbook(110, 260, 22, 33)
    const basis = parseG60Workbook(wbBasis)
    const repr = parseG60Workbook(wbRepr)

    const fullTabsBasis = Object.fromEntries(Object.keys(basis.tabs).map((n) => [n, extractFullTab(wbBasis.sheet(n)!)]))
    const fullTabsRepr = Object.fromEntries(Object.keys(repr.tabs).map((n) => [n, extractFullTab(wbRepr.sheet(n)!)]))
    const basisRows = buildG60Rows({ projectId: 'p', fileId: 'alt' }, basis, fullTabsBasis)
    const reprRows = buildG60Rows({ projectId: 'p', fileId: 'neu' }, repr, fullTabsRepr)

    const basisRehydrated = rehydrateG60File(
      basisRows.meta,
      basisRows.tabRows.map((t) => ({ tab_name: t.tab_name, tab_index: t.tab_index, aggregate: t.aggregate, component_rows: t.component_rows })),
      basisRows.cardRows.map((c) => ({ code: c.code, label: c.label, value_numeric: c.value_numeric, value_text: c.value_text })),
    )
    const reprRehydrated = rehydrateG60File(
      reprRows.meta,
      reprRows.tabRows.map((t) => ({ tab_name: t.tab_name, tab_index: t.tab_index, aggregate: t.aggregate, component_rows: t.component_rows })),
      reprRows.cardRows.map((c) => ({ code: c.code, label: c.label, value_numeric: c.value_numeric, value_text: c.value_text })),
    )

    const directAnalysis = analyzeG60Pair(basis, repr)
    const viaDbAnalysis = analyzeG60Pair(basisRehydrated, reprRehydrated)
    expect(viaDbAnalysis.rows).toEqual(directAnalysis.rows)

    const directBuf = await buildG60ExportWorkbook(
      baseInput({ analysis: directAnalysis, baselineTabs: basis.tabs, comparisonTabs: repr.tabs }),
    )
    const viaDbBuf = await buildG60ExportWorkbook(
      baseInput({ analysis: viaDbAnalysis, baselineTabs: basisRehydrated.tabs, comparisonTabs: reprRehydrated.tabs }),
    )

    const directWs = (await loadWorkbook(directBuf)).getWorksheet('Tab_Übersicht')!
    const viaDbWs = (await loadWorkbook(viaDbBuf)).getWorksheet('Tab_Übersicht')!
    const flat = (ws: ExcelJS.Worksheet) => {
      const rows: string[][] = []
      ws.eachRow((row) => rows.push((row.values as unknown[]).map((v) => String(v ?? ''))))
      return rows
    }
    expect(flat(viaDbWs)).toEqual(flat(directWs))
  })
})

// Performance smoke test: real G60 workbooks carry ~112 tabs (maxDuration=300
// concern noted in P4.3). Assert the builder completes and the sheets are
// fully populated at that scale — not a timing assertion (flaky in CI), a
// structural-completion + generous-bound smoke test.
describe('buildG60ExportWorkbook — Performance-Smoke (112 Tabs)', () => {
  it('builds a workbook for ~112 tabs without throwing, in reasonable time', async () => {
    const sheets: Record<string, ReturnType<typeof sheetFromCells>> = {
      INPUT: sheetFromCells(RATES_CELLS),
      Stückzahlen: sheetFromCells({ C4: 2026, D4: 2027, C5: 10000, D5: 20000 }),
    }
    const TAB_COUNT = 112
    for (let i = 1; i <= TAB_COUNT; i++) {
      sheets[`${i}_2`] = sheetFromCells(tabCells({ part: `P-${i}`, sales: 20 + i, w: 4, at15: 100 + i }))
    }
    const wb = workbookFromSheets(sheets)
    const basis = parseG60Workbook(wb)
    const repr = parseG60Workbook(wb)
    const analysisLarge = analyzeG60Pair(basis, repr)
    expect(analysisLarge.common.length).toBe(TAB_COUNT)

    const start = Date.now()
    const buf = await buildG60ExportWorkbook(
      baseInput({ analysis: analysisLarge, baselineTabs: basis.tabs, comparisonTabs: repr.tabs }),
    )
    const elapsedMs = Date.now() - start
    expect(elapsedMs).toBeLessThan(10_000)

    const loaded = await loadWorkbook(buf)
    const tabUebersicht = loaded.getWorksheet('Tab_Übersicht')!
    // header row + at least 1 metric row per tab.
    expect(tabUebersicht.rowCount).toBeGreaterThanOrEqual(TAB_COUNT + 1)
  })
})