// Cross-Language E2E test (KAR-905 / P3.1, task-mandated deliverable):
// "synthetisches DE-Workbook vs. synthetisches EN-Workbook derselben
// logischen Daten -> Vergleich läuft, Felder matchen über canonical IDs,
// keine falschen Struktur-Befunde wegen Sprache."
//
// Drives the REAL ingest-adjacent pipeline (loadExcelWorkbook +
// parseQAFTemplate for MANUFACTURING steps, parseSummarySheetFromWorkbook for
// SUMMARY money metrics — the same two calls actions.ts's ingestQafUpload
// makes) rather than pre-built QAFRow/QafSummary fixtures, so this test
// actually exercises the DE/EN sheet-name detection (module-sheet-names.ts)
// and DE/EN header/label matching (canonical-model.ts findByAlias) verified
// individually elsewhere in this PR, end-to-end through compareQafPair.
//
// Synthetic data only — invented part number, process names, costs. No real
// BMW/supplier file content. // allow-customer-string

import { describe, it, expect, beforeAll } from 'vitest'
import ExcelJS from 'exceljs'
import { TEMPLATE_HEADERS, HEADER_TO_KEY, parseQAFTemplate, type QAFFieldKey } from '@/lib/qaf-parser'
import { loadExcelWorkbook, parseSummarySheetFromWorkbook } from '../workbook-adapter'
import { compareQafPair, type QafFileParsed } from '../compare'
import { detectQafFileLanguage } from '../language-detection'

// EN header row, derived from the SAME production HEADER_TO_KEY dictionary
// TEMPLATE_HEADERS' DE order is built from — guarantees this test can never
// drift out of sync with the real DE/EN alias list (Object.entries preserves
// insertion order; HEADER_TO_KEY lists all 22 DE labels first, then all 22 EN
// labels, so the second write per key below always ends up being the EN one).
const FIELD_KEY_ORDER: QAFFieldKey[] = TEMPLATE_HEADERS.map((h) => HEADER_TO_KEY[h])
const EN_LABEL_BY_KEY = new Map<QAFFieldKey, string>()
for (const [label, key] of Object.entries(HEADER_TO_KEY)) EN_LABEL_BY_KEY.set(key, label)
const TEMPLATE_HEADERS_EN: string[] = FIELD_KEY_ORDER.map((k) => EN_LABEL_BY_KEY.get(k)!)

async function buildQafWorkbookBuffer(opts: {
  manufacturingSheetName: string
  manufacturingHeaders: readonly string[]
  manufacturingRows: Array<Array<string | number>>
  summarySheetName: string
  summaryCells: Record<string, string | number>
}): Promise<Buffer> {
  const wb = new ExcelJS.Workbook()
  const mfg = wb.addWorksheet(opts.manufacturingSheetName)
  mfg.getRow(1).values = [null, ...opts.manufacturingHeaders]
  opts.manufacturingRows.forEach((row, i) => {
    mfg.getRow(i + 2).values = [null, ...row]
  })
  const sum = wb.addWorksheet(opts.summarySheetName)
  for (const [addr, value] of Object.entries(opts.summaryCells)) {
    sum.getCell(addr).value = value
  }
  const buf = await wb.xlsx.writeBuffer()
  return Buffer.from(buf)
}

// ── DE workbook (legacy "Zusammenfassung" SUMMARY template + "Fertigungskosten" tab) ──
const DE_ROWS: Array<Array<string | number>> = [
  // Pos 1: DE process/facility free text, genuinely untranslated vs. the EN
  // side below (a real supplier would never machine-translate this).
  ['1', 'Teil A', 'Schweissen', 'Schweisszelle 1', 'Werk Nord', 'EUR', 10, 1, 1, 30, 10, 50, 0, 0, 0, 100, 'EUR', 1, 1, 100, 0, 0],
  // Pos 2: coincidentally identical free text on ALL THREE matcher-relevant
  // name fields (teilebenennung/prozessbezeichnung/bezeichnungAnlage) on both
  // sides (plausible for a proper-noun part/machine designation the supplier
  // reuses verbatim in both file variants) — genuinely a safe_match, not just
  // a partial name overlap.
  ['2', 'Teil B', 'Montage', 'Montageband 3', 'Werk Nord', 'EUR', 8, 1, 1, 25, 10, 40, 0, 0, 0, 200, 'EUR', 1, 1, 200, 0, 0],
]

// ── EN workbook (V9 "SUMMARY" template + "Manufacturing costs" tab) ─────────
const EN_ROWS: Array<Array<string | number>> = [
  // Pos 1: same logical step, deliberately DIFFERENT free text (untranslated
  // supplier wording) AND a genuinely changed cost (100 -> 130).
  ['1', 'Part A', 'Welding Station Alpha', 'Weld Cell One', 'Plant North', 'EUR', 10, 1, 1, 30, 10, 50, 0, 0, 0, 130, 'EUR', 1, 1, 130, 0, 0],
  // Pos 2: same logical step, IDENTICAL teilebenennung/prozessbezeichnung/
  // bezeichnungAnlage text as the DE side (see comment above), same cost.
  ['2', 'Teil B', 'Montage', 'Montageband 3', 'Plant North', 'EUR', 8, 1, 1, 25, 10, 40, 0, 0, 0, 200, 'EUR', 1, 1, 200, 0, 0],
]

async function parseSide(buffer: Buffer, manufacturingSheetName: string): Promise<{
  steps: Awaited<ReturnType<typeof parseQAFTemplate>>
  summary: Awaited<ReturnType<typeof parseSummarySheetFromWorkbook>>['summary']
  summaryMetrics: Awaited<ReturnType<typeof parseSummarySheetFromWorkbook>>['summaryMetrics']
  sheetNames: string[]
}> {
  const wb = await loadExcelWorkbook(buffer)
  const file = new File([new Uint8Array(buffer)], `${manufacturingSheetName}.xlsx`, {
    type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
  })
  const steps = await parseQAFTemplate(file)
  const { summary, summaryMetrics } = parseSummarySheetFromWorkbook(wb)
  return { steps, summary, summaryMetrics, sheetNames: wb.worksheets.map((w) => w.name) }
}

describe('Cross-Language E2E — synthetic DE vs. synthetic EN workbook of the same logical data', () => {
  let alt: QafFileParsed
  let neu: QafFileParsed
  let altSheetNames: string[]
  let neuSheetNames: string[]
  // QAFParseResult (parseSide's `steps`) carries the additive QAFParseMeta
  // properties (parseConfidence/unmappedHeaders) that QafFileParsed.steps'
  // narrower `QAFRow[]` type does not — kept as separate references so the
  // "headers parsed at full confidence" assertion below can read them
  // without a cast.
  let altParseMeta: { parseConfidence: number; unmappedHeaders: string[] }
  let neuParseMeta: { parseConfidence: number; unmappedHeaders: string[] }

  beforeAll(async () => {
    const deBuf = await buildQafWorkbookBuffer({
      manufacturingSheetName: 'Fertigungskosten',
      manufacturingHeaders: TEMPLATE_HEADERS,
      manufacturingRows: DE_ROWS,
      summarySheetName: 'Zusammenfassung',
      summaryCells: {
        C26: 'EUR',
        N10: 'EUR',
        G11: 'Materialkosten',
        N11: 10,
        G12: 'Fertigungskosten',
        N12: 20,
        G13: 'SUMME HERSTELLKOSTEN',
        N13: 30,
        G29: 'ANGEBOTSPREIS',
        N29: 50,
      },
    })
    const enBuf = await buildQafWorkbookBuffer({
      manufacturingSheetName: 'Manufacturing costs',
      manufacturingHeaders: TEMPLATE_HEADERS_EN,
      manufacturingRows: EN_ROWS,
      summarySheetName: 'SUMMARY',
      summaryCells: {
        C25: 'EUR',
        N10: 'EUR',
        G11: 'Material costs',
        N11: 10,
        G12: 'Manufacturing costs',
        N12: 20,
        G13: 'Total production costs',
        N13: 30,
        G30: 'QUOTATION PRICE',
        N30: 60,
      },
    })

    const de = await parseSide(deBuf, 'Fertigungskosten')
    const en = await parseSide(enBuf, 'Manufacturing costs')
    altSheetNames = de.sheetNames
    neuSheetNames = en.sheetNames
    altParseMeta = { parseConfidence: de.steps.parseConfidence, unmappedHeaders: de.steps.unmappedHeaders }
    neuParseMeta = { parseConfidence: en.steps.parseConfidence, unmappedHeaders: en.steps.unmappedHeaders }

    alt = {
      ref: { id: 'alt-de', fileName: 'alt-de.xlsx', quotationDate: '2026-01-01' },
      summary: { ...de.summary, partNumber: { value: '1234567', cell: null } },
      steps: de.steps,
      summaryMetrics: de.summaryMetrics ?? undefined,
    }
    neu = {
      ref: { id: 'neu-en', fileName: 'neu-en.xlsx', quotationDate: '2026-02-01' },
      summary: { ...en.summary, partNumber: { value: '1234567', cell: null } },
      steps: en.steps,
      summaryMetrics: en.summaryMetrics ?? undefined,
    }
  })

  it('parses both sides through the real pipeline (2 steps each)', () => {
    expect(alt.steps.length).toBe(2)
    expect(neu.steps.length).toBe(2)
  })

  it('detects DE for the ALT sheet-name set and EN for the NEU sheet-name set', () => {
    expect(detectQafFileLanguage({ sheetNames: altSheetNames }).language).toBe('de')
    expect(detectQafFileLanguage({ sheetNames: neuSheetNames }).language).toBe('en')
  })

  it('MANUFACTURING headers parse at full confidence on BOTH the DE and the EN sheet', () => {
    expect(altParseMeta.parseConfidence).toBe(1)
    expect(neuParseMeta.parseConfidence).toBe(1)
    expect(altParseMeta.unmappedHeaders).toEqual([])
    expect(neuParseMeta.unmappedHeaders).toEqual([])
  })

  it('matches both process steps via Positionsnummer — no false "new"/"removed" structure findings from language alone', () => {
    const r = compareQafPair(alt, neu)
    expect(r.structureChanges.new).toEqual([])
    expect(r.structureChanges.removed).toEqual([])
    expect(r.stepComparisons).toHaveLength(2)
  })

  it('Pos 2 (coincidentally identical free text both sides) is a safe/high-confidence match', () => {
    const r = compareQafPair(alt, neu)
    // DE_ROWS/EN_ROWS both list Pos 2 as the second (index 1) row.
    const pos2 = r.stepComparisons.find((c) => c.altIndex === 1 && c.neuIndex === 1)
    expect(pos2).toBeDefined()
    expect(pos2?.match.matchStatus).toBe('safe_match')
    expect(pos2?.match.confidenceScore).toBe(1)
  })

  it('Pos 1 (genuinely untranslated DE vs. EN process/facility free text) still matches via Positionsnummer, with reduced confidence and requiresReview — the documented cross-language degradation, NOT a false structure change', () => {
    const r = compareQafPair(alt, neu)
    // DE_ROWS/EN_ROWS both list Pos 1 as the first (index 0) row.
    const pos1 = r.stepComparisons.find((c) => c.altIndex === 0 && c.neuIndex === 0)
    expect(pos1).toBeDefined()
    expect(pos1?.match.matchStatus).not.toBe('safe_match')
    expect(['possible_structure_change', 'probable_match', 'candidate_match']).toContain(pos1?.match.matchStatus)
    expect(pos1?.match.confidenceScore ?? 1).toBeLessThan(1)
    // Pos 1's cost genuinely changed (100 -> 130) — that delta must still be
    // visible despite the language-driven name mismatch.
    expect(pos1?.fieldDiffs.find((d) => d.field === 'fk')?.deltaAbsolute).toBe(30)
  })

  it('diffs summary money metrics across canonical metric keys regardless of DE vs. EN label text or legacy vs. V9 template', () => {
    const r = compareQafPair(alt, neu)
    const materialCosts = r.summaryDiffs.find((d) => d.metricKey === 'materialCosts')
    const manufacturingCosts = r.summaryDiffs.find((d) => d.metricKey === 'manufacturingCosts')
    const totalProductionCosts = r.summaryDiffs.find((d) => d.metricKey === 'totalProductionCosts')
    const quotationPrice = r.summaryDiffs.find((d) => d.metricKey === 'quotationPrice')

    // Same value on both sides (10/20/30) -> no delta, proving the DE label
    // "Materialkosten"/"Fertigungskosten"/"SUMME HERSTELLKOSTEN" and the EN
    // label "Material costs"/"Manufacturing costs"/"Total production costs"
    // resolved to the exact same canonical metricKey.
    expect(materialCosts?.altValue).toBe(10)
    expect(materialCosts?.neuValue).toBe(10)
    expect(materialCosts?.deltaAbsolute).toBe(0)
    expect(manufacturingCosts?.deltaAbsolute).toBe(0)
    expect(totalProductionCosts?.deltaAbsolute).toBe(0)

    // Deliberately different value (50 DE vs. 60 EN, ANGEBOTSPREIS/QUOTATION
    // PRICE, legacy row 29 vs. V9 row 30) -> a real, correctly computed
    // cross-language, cross-template-version delta.
    expect(quotationPrice?.altValue).toBe(50)
    expect(quotationPrice?.neuValue).toBe(60)
    expect(quotationPrice?.deltaAbsolute).toBe(10)
  })

  it('runs plausibility with no critical issue purely from the language/template-version difference', () => {
    const r = compareQafPair(alt, neu)
    // Non-critical 'pruefen'/'hinweis' findings are expected (this synthetic
    // fixture only fills 4 of 19 summary metrics, so several reconciliation
    // cascades correctly report "nicht pruefbar" — a genuine, honest finding,
    // not a bug). The assertion here is specifically that NOTHING escalates
    // to 'kritisch' purely because ALT is DE/legacy-template and NEU is
    // EN/V9-template.
    expect(r.plausibility.every((i) => i.severity !== 'kritisch')).toBe(true)
  })
})