// KAR-915 — V8.8 Cross-Language E2E: synthetic V8.8-style DE workbook vs.
// synthetic V8.8-style EN workbook of the same logical data, driven through
// the real ingest-adjacent pipeline and compareQafPair end-to-end. Modeled
// directly on cross-language-e2e.test.ts (KAR-905/P3.1's own V9 version of
// this test) — same structure, extended to also parse+thread MATERIAL/SBM/
// RMR rows so the KAR-915 alias fixes are exercised through the full
// comparison engine, not just the individual parser unit tests in
// v88-aliases.test.ts.
//
// V8.8 header LABEL TEXT is verbatim real-file evidence (server-only NCAR-
// Korpus, never a value/name/price — see v88-aliases.test.ts's own header
// comment for the full extraction-methodology note). All ROW VALUES below
// are invented.

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 { parseMaterialSheet, materialRowsForReconciliation } from '../material-parser'
import { parseSbmSheet, sbmRowsForReconciliation } from '../sbm-parser'
import { parseRmrSheet, rmrRowsForReconciliation } from '../rmr-parser'

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)!)

const MFG_ROWS: Array<Array<string | number>> = [
  ['1', 'Teil V88', 'Schweissen', 'Zelle 1', 'Werk Nord', 'EUR', 10, 1, 1, 30, 10, 50, 0, 0, 0, 100, 'EUR', 1, 1, 100, 0, 0],
]

const V88_DE_MATERIAL_HEADER = [
  'Positionsnummer\n Fertigungsschritt', 'Teilebenennung', 'Benennung\n Rohmaterial / Kaufteil', 'Lieferant',
  'Technische Funktion\n des Bauteils', 'Ursprungsland (handelsrechtlich)', 'HTS-Code (6-stellig)', 'Mengeneinheit',
  'Beschaffungswährung [BW]', 'Kalkulatorisch angesetzte Materialkosten pro\nMengeneinheit [BW]', 'Angebotswährung [AW]',
  'Wechselkurs [AW/BW]', 'Verpackungskosten\n je Mengeneinheit [AW]', 'Transportkosten\n je Mengeneinheit [AW]',
  'Zollkosten \n je Mengeneinheit [AW]', 'Materialgemeinkosten\n MGK [%]', 'Materialkosten\n pro Mengeneinheit [AW]',
  'Einsatzmenge [Einheiten]\n (nur Rohmaterial)', 'Nettomenge [Einheiten]\n (nur Rohmaterial)',
  'Kalkulatorisch angesetzte Rückvergütung [AW]\n (nur Rohmaterial)', 'Anzahl pro Angebotsteil', 'Materialkosten [AW]',
  'Materialausschuss [%]', 'Ausschusskosten\n Material [AW]', 'Rohstoffbezeichnung', 'Bezugsgewicht [kg]',
  'Rohstoffnotierung\n Ro [AW/kg]', 'Rohstofffzuschlag\n RoZ0 [AW]',
]
const V88_EN_MATERIAL_HEADER = [
  'Item Number\n Manufacturing step', 'Parts designation', 'Designation\n Raw material / purchased part', 'Supplier',
  'Technical function \n of the component', 'Country of origin \n (commercial law)', 'HTS-Code \n (6 digits)', 'Unit',
  'Purchasing currency [BW]', 'Imputed costs per\n quantity unit [BW]', 'Quotation currency [AW]',
  'Exchange rate [AW/BW]', 'Packaging costs\n per unit [AW]', 'Transport costs\n per quantity unit [AW]',
  'Customs duties\n per quantity unit [AW]', 'Material overhead costs\n (MGK) [%]', 'Material costs\n per quantity unit [AW]',
  'Use amount [units]\n (only raw materials)', 'Net amount [units]\n (only raw materials)',
  'Imputed reimbursement [AW]\n(only raw materials)', 'Number per quotation part', 'Material costs [AW]',
  'Material scrap [%]', 'Material scrap\n costs [AW]', 'Raw material description', 'Base weight [kg]',
  'Raw material annotation \n Ro [AW/kg]', 'Raw material sucharge\n RoZ0 [AW]',
]
// materialCost=32 satisfies business-rules.ts's rule_calc_material_cost
// Rohmaterial formula (referenceQuantity is filled here, so
// evaluateMaterialCostCheck takes the "isRohmaterial" branch regardless of
// unitOfMeasure — see that function's own branch-order comment):
// (costPerUnitAw x referenceQuantity - rebate) x quantityPerQuotedPart
// = (1.6 x 10 - 0) x 2 = 32. This test exercises the real business-rules
// pipeline end-to-end, so the invented numbers must satisfy the same
// formula a real file's numbers would be checked against.
const MATERIAL_ROW: Array<string | number> = [
  '1', 'Test-Teil', 'Test-Rohmaterial-Alpha', 'Test-Zulieferer AG', 'Test-Funktion', 'DE', '39269097',
  'kg', 'EUR', 1.5, 'EUR', 1, 0.1, 0.05, 0, 5, 1.6, 10, 9, 0, 2, 32, 1, 0.03,
  'Test-Rohstoff-Beta', 0.5, 2, 1,
]

const V88_DE_SBM_HEADER = [
  'Positionsnummer\n Fertigungsschritt', 'Verrechnungsform', 'Werkzeug-/\n Vorrichtungsart', 'Bauteilbezeichnung',
  'Beschaffungswährung BW', 'Kalkulatorisch angesetzte Werkzeug- und Vorrichtungskosten [BW]',
  'Summe Werkzeug- /\n Vorrichtungskosten [AW]',
]
const V88_EN_SBM_HEADER = [
  'Item number \nManufactering step', 'Billing method', 'Tool /\ndevice type', 'Name', 'Procurement currency [BW]',
  'Inputed tool / \ndevice costs [BW]', 'Sum tools /\n device costs [AW]',
]
const SBM_ROW: Array<string | number> = ['1', 'SBM', 'Spritzguss-Werkzeug', 'Test-Vorrichtung', 'EUR', 12000, 12500]

const V88_DE_RMR_HEADER = [
  'Positionsnummer', 'Rohstoffbezeichnung', 'Bezugsgewicht [kg]', 'Rohstoffschlüssel',
  'Rohstoffnotierung R0 [AW/kg]', 'Rohstoff-Zuschlag [AW]\n RoZ0',
]
const V88_EN_RMR_HEADER = [
  'Item number', 'Raw material description', 'Referenced weight [kg]', 'Raw material key',
  'Raw material annotation\n Ro [AW/kg]', 'Raw material surcharge\n [AW] RoZ0',
]
const RMR_ROW: Array<string | number> = ['1', 'Test-Rohstoff-Beta', 0.5, 'RS-001', 2.0, 1.0]

async function buildV88WorkbookBuffer(opts: {
  mfgSheetName: string
  mfgHeaders: readonly string[]
  materialSheetName: string
  materialHeader: readonly string[]
  sbmSheetName: string
  sbmHeader: readonly string[]
  rmrSheetName: string
  rmrHeader: readonly string[]
  summarySheetName: string
  summaryCells: Record<string, string | number>
}): Promise<Buffer> {
  const wb = new ExcelJS.Workbook()
  const mfg = wb.addWorksheet(opts.mfgSheetName)
  mfg.getRow(1).values = [null, ...opts.mfgHeaders]
  MFG_ROWS.forEach((row, i) => {
    mfg.getRow(i + 2).values = [null, ...row]
  })

  const material = wb.addWorksheet(opts.materialSheetName)
  material.getRow(11).values = opts.materialHeader as ExcelJS.CellValue[]
  material.getRow(12).values = MATERIAL_ROW as ExcelJS.CellValue[]

  const sbm = wb.addWorksheet(opts.sbmSheetName)
  sbm.getRow(12).values = opts.sbmHeader as ExcelJS.CellValue[]
  sbm.getRow(13).values = SBM_ROW as ExcelJS.CellValue[]

  const rmr = wb.addWorksheet(opts.rmrSheetName)
  rmr.getRow(11).values = opts.rmrHeader as ExcelJS.CellValue[]
  rmr.getRow(12).values = RMR_ROW as ExcelJS.CellValue[]

  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)
}

async function parseSide(
  buffer: Buffer,
  mfgSheetName: string,
): Promise<{ file: QafFileParsed; sheetNames: string[] }> {
  const wb = await loadExcelWorkbook(buffer)
  const file0 = new File([new Uint8Array(buffer)], `${mfgSheetName}.xlsx`, {
    type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
  })
  const steps = await parseQAFTemplate(file0)
  const { summary, summaryMetrics } = parseSummarySheetFromWorkbook(wb)

  const materialParsed = await parseMaterialSheet(wb)
  const sbmParsed = await parseSbmSheet(wb)
  const rmrParsed = await parseRmrSheet(wb)

  const file: QafFileParsed = {
    ref: { id: mfgSheetName, fileName: `${mfgSheetName}.xlsx`, quotationDate: '2026-01-01' },
    summary: { ...summary, partNumber: { value: 'V88-TEST-001', cell: null } },
    steps,
    summaryMetrics: summaryMetrics ?? undefined,
    materialRows: materialRowsForReconciliation(materialParsed),
    sbmRows: sbmRowsForReconciliation(sbmParsed),
    rmrRows: rmrRowsForReconciliation(rmrParsed),
  }
  return { file, sheetNames: wb.worksheets.map((w) => w.name) }
}

describe('KAR-915 — V8.8 Cross-Language E2E: synthetic DE vs. synthetic EN V8.8-style workbook', () => {
  let alt: QafFileParsed
  let neu: QafFileParsed

  beforeAll(async () => {
    const deBuf = await buildV88WorkbookBuffer({
      mfgSheetName: 'Fertigungskosten',
      mfgHeaders: TEMPLATE_HEADERS,
      materialSheetName: 'Material',
      materialHeader: V88_DE_MATERIAL_HEADER,
      sbmSheetName: 'SBM-Vorrichtungen-FWZ',
      sbmHeader: V88_DE_SBM_HEADER,
      rmrSheetName: 'Rohstoffrisiken',
      rmrHeader: V88_DE_RMR_HEADER,
      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 buildV88WorkbookBuffer({
      mfgSheetName: 'Manufacturing costs',
      mfgHeaders: TEMPLATE_HEADERS_EN,
      materialSheetName: 'Material',
      materialHeader: V88_EN_MATERIAL_HEADER,
      sbmSheetName: 'SBM-devices-FWZ',
      sbmHeader: V88_EN_SBM_HEADER,
      rmrSheetName: 'Raw material risks',
      rmrHeader: V88_EN_RMR_HEADER,
      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,
      },
    })

    alt = (await parseSide(deBuf, 'Fertigungskosten')).file
    neu = (await parseSide(enBuf, 'Manufacturing costs')).file
  })

  it('MATERIAL: both V8.8-style DE and EN sides parse with coreFieldsFound (non-null materialRows, 1 row each)', () => {
    expect(alt.materialRows).not.toBeNull()
    expect(neu.materialRows).not.toBeNull()
    expect(alt.materialRows).toHaveLength(1)
    expect(neu.materialRows).toHaveLength(1)
  })

  it('SBM: both V8.8-style DE and EN sides parse with coreFieldsFound (non-null sbmRows, 1 row each)', () => {
    expect(alt.sbmRows).not.toBeNull()
    expect(neu.sbmRows).not.toBeNull()
    expect(alt.sbmRows).toHaveLength(1)
    expect(neu.sbmRows).toHaveLength(1)
  })

  it('RMR: both V8.8-style DE and EN sides parse with coreFieldsFound (non-null rmrRows, 1 row each)', () => {
    expect(alt.rmrRows).not.toBeNull()
    expect(neu.rmrRows).not.toBeNull()
    expect(alt.rmrRows).toHaveLength(1)
    expect(neu.rmrRows).toHaveLength(1)
  })

  it('compareQafPair runs end-to-end without throwing and produces a plausibility result array', () => {
    const r = compareQafPair(alt, neu)
    expect(r.stepComparisons).toHaveLength(1)
    expect(Array.isArray(r.plausibility)).toBe(true)
    // Nothing about parsing a V8.8-style DE/EN pair through the alias-fixed
    // parsers should itself raise a 'kritisch' plausibility finding.
    expect(r.plausibility.every((i) => i.severity !== 'kritisch')).toBe(true)
  })
})