import { describe, it, expect } from 'vitest'
import ExcelJS from 'exceljs'
import type { QAFRow } from '@/lib/qaf-parser'
import type { QafSummary, QafSummaryKey } from '../types'
import { compareQafPair, type QafFileParsed } from '../compare'
import { buildQafExportWorkbook, statusFill, type QafExportInput } from '../export'

function summary(values: Partial<Record<QafSummaryKey, string | null>>): QafSummary {
  const keys: QafSummaryKey[] = [
    'partNumber', 'quotationDate', 'supplier', 'partName', 'variant',
    'project', 'requestVersion', 'changeIndex', 'supplierNo',
    'peakVolumeYear', 'productionStartSop', 'deliverySite', 'shiftsPerWeek', // KAR-910
    'plannedCapacity', 'lotSize', // QVS-P4
  ]
  const out = {} as QafSummary
  for (const k of keys) out[k] = { value: values[k] ?? null, cell: null }
  return out
}
function mkRow(o: Partial<QAFRow> = {}): QAFRow {
  return {
    positionsnummer: '1', teilebenennung: 'Teil', prozessbezeichnung: 'Montage',
    bezeichnungAnlage: 'Anlage', standort: 'Werk', beschaffungswaehrung: 'EUR', angebotswaehrung: 'EUR',
    zykluszeit: 10, teileProZyklus: 1, anzahlMA: 1, lohnkosten: 30, lohnzuschlagssaetze: 10, mss: 50,
    ruestkosten: 0, fek: 0, rfgk: 0, fk: 100, wechselkurs: 1, anzahlProAngebotsteil: 1, fkAW: 100,
    ausschuss: 2, ausschusskosten: 1, ...o,
  }
}
function file(id: string, date: string, steps: QAFRow[]): QafFileParsed {
  return { ref: { id, fileName: `${id}.xlsx`, quotationDate: date }, summary: summary({ partNumber: '7490365', quotationDate: date, partName: 'Blende' }), steps }
}

const alt = file('old', '2023-01-01', [mkRow({ positionsnummer: '2', prozessbezeichnung: 'Schweissen', bezeichnungAnlage: 'Zelle', fk: 200 })])
const neu = file('new', '2024-01-01', [
  mkRow({ positionsnummer: '2', prozessbezeichnung: 'Schweissen', bezeichnungAnlage: 'Zelle', fk: 500 }),
  mkRow({ positionsnummer: '3', prozessbezeichnung: 'Pruefen', bezeichnungAnlage: 'Pruefstand', fk: 40 }),
])

const input: QafExportInput = {
  generatedAtLabel: '2026-06-26 00:00',
  files: [
    { fileName: 'old.xlsx', partNumber: '7490365', status: 'parsed' },
    { fileName: 'new.xlsx', partNumber: '7490365', status: 'parsed' },
  ],
  comparisons: [compareQafPair(alt, neu)],
}

const REQUIRED_SHEETS = [
  'README', 'Import_Log', 'Zusammenfassung_Vergleich', 'Fertigungskosten_Vergleich',
  'Neu_Entfallen', 'Delta_Highlights', 'Top_Treiber_Prozess', 'Plausibilitätscheck',
]

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

describe('buildQafExportWorkbook', () => {
  it('produces all 8 reference sheets', async () => {
    const wb = await loadWorkbook(await buildQafExportWorkbook(input))
    const names = wb.worksheets.map((w) => w.name)
    for (const s of REQUIRED_SHEETS) expect(names).toContain(s)
  })

  it('writes the FK delta into Fertigungskosten_Vergleich', async () => {
    const wb = await loadWorkbook(await buildQafExportWorkbook(input))
    const ws = wb.getWorksheet('Fertigungskosten_Vergleich')!
    let found = false
    ws.eachRow((row) => {
      const vals = (row.values as unknown[]).map((v) => String(v ?? ''))
      if (vals.includes('fk') && vals.includes('300')) found = true
    })
    expect(found).toBe(true)
  })

  it('lists the new step in Neu_Entfallen', async () => {
    const wb = await loadWorkbook(await buildQafExportWorkbook(input))
    const ws = wb.getWorksheet('Neu_Entfallen')!
    let hasPruefen = false
    ws.eachRow((row) => {
      if (String(row.values).includes('Pruefen')) hasPruefen = true
    })
    expect(hasPruefen).toBe(true)
  })

  it('has a frozen header row + autofilter on a data sheet', async () => {
    const wb = await loadWorkbook(await buildQafExportWorkbook(input))
    const ws = wb.getWorksheet('Fertigungskosten_Vergleich')!
    expect(ws.views?.[0]?.state).toBe('frozen')
    expect(ws.autoFilter).toBeTruthy()
  })
})

// KAR-906/P3.2: Plausibilitätscheck gets an additive "Explanation (EN)"
// column fed directly from the live PlausibilityIssue.explanationEn (no
// bilingual-decode needed here — see export.ts module comment on that sheet).
describe('buildQafExportWorkbook — Plausibilitätscheck bilingual column (KAR-906)', () => {
  it('adds an "Explanation (EN)" column without displacing the existing German columns', async () => {
    const wb = await loadWorkbook(await buildQafExportWorkbook(input))
    const ws = wb.getWorksheet('Plausibilitätscheck')!
    const headerRow = (ws.getRow(1).values as unknown[]).map((v) => String(v ?? ''))
    expect(headerRow).toContain('Erklärung')
    expect(headerRow).toContain('Explanation (EN)')
    expect(headerRow.indexOf('Explanation (EN)')).toBeGreaterThan(headerRow.indexOf('Erklärung'))
  })

  it('fills the EN column with a distinct English explanation for at least one row', async () => {
    // The alt/neu fixture has a currency-consistent, new-step scenario that
    // reliably produces at least one plausibility issue with a translated
    // explanation (structural / rule-engine issues all carry explanationEn).
    const wb = await loadWorkbook(await buildQafExportWorkbook(input))
    const ws = wb.getWorksheet('Plausibilitätscheck')!
    const rows: Record<string, unknown>[] = []
    ws.eachRow((row, num) => {
      if (num === 1) return
      const vals = row.values as unknown[]
      rows.push({ expl: vals[6], explEn: vals[7] })
    })
    expect(rows.length).toBeGreaterThan(0)
    const withEn = rows.find((r) => typeof r.explEn === 'string' && (r.explEn as string).length > 0)
    expect(withEn).toBeDefined()
    expect(withEn?.explEn).not.toBe(withEn?.expl)
  })
})

describe('statusFill — Feld-Polarität (KAR-833)', () => {
  const RED = 'FFF8BBD0'
  const GREEN = 'FFC8E6C9'

  it('Kosten-Feld: Anstieg rot, Senkung grün (auch bei Band-Status)', () => {
    expect(statusFill('anstieg', 1.2, 'fk')).toBe(RED)
    expect(statusFill('senkung', -1.2, 'fk')).toBe(GREEN)
    expect(statusFill('kritisch_50', -80, 'fk')).toBe(GREEN) // große Senkung ≠ rot
    expect(statusFill('auffaellig_25', 30, 'lohnkosten')).toBe(RED)
  })

  it('HIGHER_IS_BETTER-Feld: Anstieg grün, Senkung rot', () => {
    expect(statusFill('anstieg', 0.5, 'teileProZyklus')).toBe(GREEN)
    expect(statusFill('senkung', -0.5, 'teileProZyklus')).toBe(RED)
    expect(statusFill('kritisch_50', 2, 'anzahlProAngebotsteil')).toBe(GREEN)
    expect(statusFill('auffaellig_10', -0.1, 'wechselkurs')).toBe(RED)
  })

  it('Struktur-Status bleiben unverändert', () => {
    expect(statusFill('neu', null, 'fk')).toBe('FFA8DFFF')
    expect(statusFill('entfallen', null, 'fk')).toBe('FFD7D8DA')
    expect(statusFill('konstant', 0, 'fk')).toBeNull()
  })

  it('fällt ohne Delta auf die Status-Richtung zurück', () => {
    expect(statusFill('anstieg', null, 'fk')).toBe(RED)
    expect(statusFill('senkung', null, 'teileProZyklus')).toBe(RED)
  })
})