// Tests for the CARBON FOOTPRINT (CO2e) sheet parser + Emissionen-Material-
// Formel-Nachrechnung (KAR-904 / P2.5). Fixtures are entirely synthetic
// (invented emission values) — no real Brose/Kiekert/Autoliv/BMW data or // allow-customer-string
// files.

import { describe, it, expect } from 'vitest'
import ExcelJS from 'exceljs'
import {
  isCo2eSheetName,
  findCo2eWorksheet,
  matchCo2eSummaryLabelCell,
  matchCo2eMaterialHeaderColumn,
  parseCo2eSheet,
  co2eSummaryForReconciliation,
  co2eMaterialRowsForReconciliation,
  co2eFromPersistedMeta,
  validateCo2eMaterialEmissionsFormula,
  evaluateCo2eValidation,
  co2eValidationResultToPlausibilityIssue,
  checkCo2eValidation,
  CORE_CO2E_SUMMARY_FIELD_KEYS,
  CORE_CO2E_MATERIAL_FIELD_KEYS,
  CO2E_VALIDATION_CONFIG,
  type Co2eMaterialRow,
} from '../co2e-parser'

async function workbookWithSheets(sheets: Array<{ name: string; rows: unknown[][] }>): Promise<ExcelJS.Workbook> {
  const wb = new ExcelJS.Workbook()
  for (const { name, rows } of sheets) {
    const ws = wb.addWorksheet(name)
    rows.forEach((row, i) => {
      ws.getRow(i + 1).values = row as ExcelJS.CellValue[]
    })
  }
  return wb
}

/** Summary panel rows: label in column A, value in column C (one blank
 * column between). */
function syntheticCo2eSummaryRows(): unknown[][] {
  return [
    ['PCF', '', 12.5],
    ['PCF-Einheit', '', 'kg CO2e'],
    ['Referenzeinheit', '', 'Stück'],
    ['CBAM Specific Direct Embedded Emissions', '', 3.2],
  ]
}

const MATERIAL_CO2E_HEADERS = [
  'Emissionsfaktor [kg CO2e / Mengeneinheit]',
  'Quelle des Emissionsfaktors',
  'Upstream Emissionen der Materialverpackung [kg CO2e / Mengeneinheit]',
  'Upstream Emissionen Transport [kg CO2e / Mengeneinheit]',
  'Gesamtentfernung [km]',
  'Transportmittel (Mehrfachnennung möglich)',
  'Emissionen Material [kg CO2e / Mengeneinheit]',
  'Gesamtemissionen [kg CO2e]',
  'Rezyklatgehalt (Rec-Q)',
  'CN-Code',
]

/** Emissionen Material = Emissionsfaktor + Verpackung + Transport (Leitfaden
 * [54]/[55]): row 1 is internally consistent (2.0 + 0.3 + 0.2 = 2.5), row 2
 * is deliberately inconsistent for the abweichung test. */
function syntheticCo2eMaterialRows(): unknown[][] {
  return [
    MATERIAL_CO2E_HEADERS,
    [2.0, 'ecoinvent v3.9', 0.3, 0.2, 800, 'Lkw', 2.5, 25, 10, '76061000'],
    [1.0, 'Lieferantenwert', 0.1, 0.1, 200, 'Schiff', 9.9, 20, 5, '76061000'],
  ]
}

async function cleanCo2eWorksheet(): Promise<ExcelJS.Worksheet> {
  const wb = await workbookWithSheets([
    { name: 'CO2e', rows: [...syntheticCo2eSummaryRows(), [], ...syntheticCo2eMaterialRows()] },
  ])
  return wb.worksheets[0]
}

// ── Sheet detection ──────────────────────────────────────────────────────

describe('isCo2eSheetName', () => {
  it('matches a tolerant CO2e/Carbon Footprint tab name', () => {
    expect(isCo2eSheetName('CO2e')).toBe(true)
    expect(isCo2eSheetName('co2e')).toBe(true)
    expect(isCo2eSheetName('Carbon Footprint')).toBe(true)
  })

  it('does not match unrelated sheet names', () => {
    expect(isCo2eSheetName('MATERIAL')).toBe(false)
    expect(isCo2eSheetName('SUMMARY')).toBe(false)
    expect(isCo2eSheetName('LC-CN')).toBe(false)
  })
})

describe('findCo2eWorksheet', () => {
  it('finds the CO2e sheet by name', async () => {
    const wb = await workbookWithSheets([
      { name: 'SUMMARY', rows: [['x']] },
      { name: 'CO2e', rows: syntheticCo2eSummaryRows() },
    ])
    expect(findCo2eWorksheet(wb)?.name).toBe('CO2e')
  })

  it('returns null when the workbook has no CO2e-relevant sheet', async () => {
    const wb = await workbookWithSheets([{ name: 'SUMMARY', rows: [['x']] }])
    expect(findCo2eWorksheet(wb)).toBeNull()
  })
})

// ── Label / header matching ────────────────────────────────────────────────

describe('matchCo2eSummaryLabelCell', () => {
  it('matches every documented summary DE label', async () => {
    const expected: Array<[string, string]> = [
      ['PCF', 'pcf'],
      ['PCF-Einheit', 'pcfUnit'],
      ['Referenzeinheit', 'referenceUnit'],
      ['CBAM Specific Direct Embedded Emissions', 'cbamSpecificDirectEmissions'],
    ]
    for (const [label, key] of expected) {
      const match = await matchCo2eSummaryLabelCell(label)
      expect(match?.key, `label "${label}"`).toBe(key)
    }
  })
})

describe('matchCo2eMaterialHeaderColumn', () => {
  it('matches every documented Material-row DE label', async () => {
    const expected: Array<[string, string]> = [
      ['Emissionsfaktor [kg CO2e / Mengeneinheit]', 'emissionFactor'],
      ['Quelle des Emissionsfaktors', 'emissionFactorSource'],
      ['Upstream Emissionen der Materialverpackung [kg CO2e / Mengeneinheit]', 'upstreamPackagingEmissions'],
      ['Upstream Emissionen Transport [kg CO2e / Mengeneinheit]', 'upstreamTransportEmissions'],
      ['Gesamtentfernung [km]', 'totalDistance'],
      ['Transportmittel (Mehrfachnennung möglich)', 'transportMode'],
      ['Emissionen Material [kg CO2e / Mengeneinheit]', 'materialEmissions'],
      ['Gesamtemissionen [kg CO2e]', 'totalMaterialEmissions'],
      ['Rezyklatgehalt (Rec-Q)', 'recycledContentRecq'],
      ['CN-Code', 'cnCode'],
    ]
    for (const [label, key] of expected) {
      const match = await matchCo2eMaterialHeaderColumn(label)
      expect(match?.key, `label "${label}"`).toBe(key)
    }
  })
})

// ── Parsing ──────────────────────────────────────────────────────────────

describe('parseCo2eSheet', () => {
  it('returns null when the workbook has no CO2e-relevant sheet (additive gate)', async () => {
    const wb = await workbookWithSheets([{ name: 'SUMMARY', rows: [['x']] }])
    expect(await parseCo2eSheet(wb)).toBeNull()
  })

  it('extracts the 4 summary fields', async () => {
    const wb = await workbookWithSheets([
      { name: 'CO2e', rows: [...syntheticCo2eSummaryRows(), [], ...syntheticCo2eMaterialRows()] },
    ])
    const result = await parseCo2eSheet(wb)
    expect(result?.summary.meta.coreFieldsFound).toBe(true)
    expect(result?.summary.values).toEqual({
      pcf: 12.5,
      pcfUnit: 'kg CO2e',
      referenceUnit: 'Stück',
      cbamSpecificDirectEmissions: 3.2,
    })
  })

  it('extracts the CO2e Material rows', async () => {
    const wb = await workbookWithSheets([
      { name: 'CO2e', rows: [...syntheticCo2eSummaryRows(), [], ...syntheticCo2eMaterialRows()] },
    ])
    const result = await parseCo2eSheet(wb)
    expect(result?.materialRows.coreFieldsFound).toBe(true)
    expect(result?.materialRows).toHaveLength(2)
    expect(result?.materialRows[0].materialEmissions).toBe(2.5)
    expect(result?.materialRows[0].cnCode).toBe('76061000')
  })

  it('flags summary.meta.coreFieldsFound:false when the panel fields are absent', async () => {
    const wb = await workbookWithSheets([{ name: 'CO2e', rows: syntheticCo2eMaterialRows() }])
    const result = await parseCo2eSheet(wb)
    expect(result?.summary.meta.coreFieldsFound).toBe(false)
    expect(result?.summary.values.pcf).toBeNull()
  })

  it('flags materialRows.coreFieldsFound:false and returns [] when the row block is absent', async () => {
    const wb = await workbookWithSheets([{ name: 'CO2e', rows: syntheticCo2eSummaryRows() }])
    const result = await parseCo2eSheet(wb)
    expect(result?.materialRows.coreFieldsFound).toBe(false)
    expect(result?.materialRows).toHaveLength(0)
  })

  it('never throws on an empty sheet', async () => {
    const wb = await workbookWithSheets([{ name: 'CO2e', rows: [] }])
    const result = await parseCo2eSheet(wb)
    expect(result?.summary.meta.coreFieldsFound).toBe(false)
    expect(result?.materialRows.coreFieldsFound).toBe(false)
  })

  // KAR-958/P2 (gate-audit.md B10, coreFieldsFound-Resilienz): only ONE of
  // the 2 summary core labels missing (pcfUnit) — the other located
  // labels/values (pcf, referenceUnit, cbamSpecificDirectEmissions) must
  // survive instead of being discarded wholesale.
  it('summary panel: extracts the other located labels when only ONE core label is missing (pcfUnit)', async () => {
    const rows = syntheticCo2eSummaryRows().filter((r) => r[0] !== 'PCF-Einheit')
    const wb = await workbookWithSheets([{ name: 'CO2e', rows }])
    const result = await parseCo2eSheet(wb)

    expect(result?.summary.meta.coreFieldsFound).toBe(false)
    expect(result?.summary.values.pcf).toBe(12.5) // the fix: survives
    expect(result?.summary.values.pcfUnit).toBeNull() // the missing one
    expect(result?.summary.meta.degradation).toEqual({
      facet: 'co2e_summary',
      reason: 'PARSE_FAILED',
      sheet: 'CO2e',
      message: expect.stringContaining('pcfUnit'),
    })
  })

  // KAR-958/P2 (gate-audit.md B11): only ONE of the 2 material-row core
  // fields missing (emissionFactor) — the other 9 mapped columns must still
  // be extracted. Row-push guard (KAR-958/P3 review fix, PR #325 finding #2):
  // "AT LEAST ONE of the two core fields (emissionFactor/materialEmissions)
  // present", not "any mapped column has a value" — materialEmissions is
  // still mapped and non-null here, so the row survives.
  it('material rows: extracts rows when only a NON-core-complete header is missing (emissionFactor)', async () => {
    const headers = [...MATERIAL_CO2E_HEADERS]
    const factorIdx = headers.indexOf('Emissionsfaktor [kg CO2e / Mengeneinheit]')
    const rows = syntheticCo2eMaterialRows().map((row) => {
      const r = [...row]
      r.splice(factorIdx, 1)
      return r
    })
    const wb = await workbookWithSheets([{ name: 'CO2e', rows }])
    const result = await parseCo2eSheet(wb)

    expect(result?.materialRows.coreFieldsFound).toBe(false)
    expect(result?.materialRows).toHaveLength(2) // the fix: both rows survive (materialEmissions is the identity anchor here)
    expect(result?.materialRows[0].materialEmissions).toBe(2.5)
    expect(result?.materialRows[0].emissionFactor).toBeNull()
    expect(result?.materialRows.degradation).toEqual({
      facet: 'co2e_material',
      reason: 'PARSE_FAILED',
      sheet: 'CO2e',
      message: expect.stringContaining('emissionFactor'),
    })
  })

  // KAR-958/P3 review fix (PR #325 finding #2, CONFIRMED correctness bug):
  // a header mapping only NON-core columns (neither emissionFactor NOR
  // materialEmissions) used to let every row with any value in those columns
  // through as "genuine CO2e-Material data" — the row-push guard was "any
  // mapped column has a value", with no requirement that the value relate to
  // CO2e emissions at all. The fix requires at least one of the two core
  // fields (the sheet's own defining "input, computed result" pair) present.
  it('material rows: rejects every row when NEITHER core field is mapped — the finding (was silently treated as real CO2e data before this fix)', async () => {
    const foreignHeaders = [
      'Quelle des Emissionsfaktors', // emissionFactorSource
      'Transportmittel (Mehrfachnennung möglich)', // transportMode
      'CN-Code', // cnCode
    ]
    const wb = await workbookWithSheets([
      {
        name: 'CO2e',
        rows: [foreignHeaders, ['Foo Corp', 'Truck', '12345678'], ['Bar Inc', 'Ship', '87654321']],
      },
    ])
    const result = await parseCo2eSheet(wb)

    expect(result?.materialRows.coreFieldsFound).toBe(false)
    expect(result?.materialRows).toHaveLength(0) // the fix: no identity signal, no rows
  })

  // PR #325 review fix (finding #7, cleanup): the anchor test material-/sbm-
  // /logistics-/lccn-parser.test.ts already have for their MIN_SIGNAL_
  // MAPPED_COLUMNS / HEADER_MATCH_MIN floor — a sheet whose name matches the
  // CO2e sheet-name heuristic but whose material-row header carries fewer
  // than HEADER_MATCH_MIN(3) recognizable columns must never even be located
  // as a header row at all — never mistaken for a degraded-but-real CO2e
  // Material block.
  it('material rows: a genuinely foreign/near-empty sheet (fewer than HEADER_MATCH_MIN recognizable columns) stays empty, no degradation', async () => {
    const wb = await workbookWithSheets([
      {
        name: 'CO2e',
        rows: [
          ['Emissionsfaktor [kg CO2e / Mengeneinheit]', 'Voellig', 'Andere', 'Spalten'],
          [1.5, 'a', 'b', 'c'],
        ],
      },
    ])
    const result = await parseCo2eSheet(wb)
    expect(result?.materialRows.coreFieldsFound).toBe(false)
    expect(result?.materialRows).toHaveLength(0)
    expect(result?.materialRows.parseConfidence).toBe(0)
    expect(result?.materialRows.degradation).toBeUndefined()
  })

  it('CORE_CO2E_SUMMARY_FIELD_KEYS / CORE_CO2E_MATERIAL_FIELD_KEYS name the defining fields', () => {
    expect([...CORE_CO2E_SUMMARY_FIELD_KEYS].sort()).toEqual(['pcf', 'pcfUnit'].sort())
    expect([...CORE_CO2E_MATERIAL_FIELD_KEYS].sort()).toEqual(['emissionFactor', 'materialEmissions'].sort())
  })
})

// ── Tri-state helpers ──────────────────────────────────────────────────────

describe('co2eSummaryForReconciliation / co2eMaterialRowsForReconciliation / co2eFromPersistedMeta', () => {
  it('collapses a degraded summary panel to null', async () => {
    const wb = await workbookWithSheets([{ name: 'CO2e', rows: syntheticCo2eMaterialRows() }])
    const parsed = await parseCo2eSheet(wb)
    expect(co2eSummaryForReconciliation(parsed)).toBeNull()
  })

  it('collapses degraded material rows to null', async () => {
    const wb = await workbookWithSheets([{ name: 'CO2e', rows: syntheticCo2eSummaryRows() }])
    const parsed = await parseCo2eSheet(wb)
    expect(co2eMaterialRowsForReconciliation(parsed)).toBeNull()
  })

  it('co2eSummaryForReconciliation(null)/co2eMaterialRowsForReconciliation(null) stay null', () => {
    expect(co2eSummaryForReconciliation(null)).toBeNull()
    expect(co2eMaterialRowsForReconciliation(null)).toBeNull()
  })

  it('co2eFromPersistedMeta: undefined/null/value tri-state', async () => {
    expect(co2eFromPersistedMeta(undefined)).toBeUndefined()
    expect(co2eFromPersistedMeta(null)).toBeNull()
    const ws = await cleanCo2eWorksheet()
    const parsed = await parseCo2eSheet({ worksheets: [ws] })
    // Same shape actions.ts's ingestQafUpload assembles for
    // qaf_file.g60_meta.co2e (values/sourceCells + parseMeta, rows + parseMeta).
    const persisted = {
      summary: { values: parsed!.summary.values, sourceCells: parsed!.summary.sourceCells, parseMeta: parsed!.summary.meta },
      material: {
        rows: [...parsed!.materialRows],
        parseMeta: {
          parseConfidence: parsed!.materialRows.parseConfidence,
          unmappedHeaders: parsed!.materialRows.unmappedHeaders,
          mappedFieldCount: parsed!.materialRows.mappedFieldCount,
          coreFieldsFound: parsed!.materialRows.coreFieldsFound,
        },
      },
    }
    expect(co2eFromPersistedMeta(persisted)?.summary.values.pcf).toBe(12.5)
  })
})

// ── Validierung: Emissionen Material = Emissionsfaktor + Verpackung + Transport ─

describe('validateCo2eMaterialEmissionsFormula', () => {
  const consistentRow: Co2eMaterialRow = {
    emissionFactor: 2.0,
    emissionFactorSource: '',
    upstreamPackagingEmissions: 0.3,
    upstreamTransportEmissions: 0.2,
    totalDistance: null,
    transportMode: '',
    materialEmissions: 2.5,
    totalMaterialEmissions: null,
    recycledContentRecq: null,
    cnCode: '',
    sourceCells: {},
    normalized: {},
    rawText: {},
  }

  it('bestanden when Excel matches Emissionsfaktor + Verpackung + Transport', () => {
    const r = validateCo2eMaterialEmissionsFormula(consistentRow, 0, 'ALT')
    expect(r?.status).toBe('bestanden')
    expect(r?.expected).toBeCloseTo(2.5, 6)
  })

  it('abweichung when Excel deviates beyond tolerance', () => {
    const r = validateCo2eMaterialEmissionsFormula({ ...consistentRow, materialEmissions: 9.9 }, 1, 'ALT')
    expect(r?.status).toBe('abweichung')
    expect(r?.messageDe).toContain('Emissionen Material')
  })

  it('returns null (gated) when materialEmissions itself is empty', () => {
    expect(validateCo2eMaterialEmissionsFormula({ ...consistentRow, materialEmissions: null }, 0, 'ALT')).toBeNull()
  })

  it('nicht_pruefbar when a component is missing', () => {
    const r = validateCo2eMaterialEmissionsFormula({ ...consistentRow, upstreamTransportEmissions: null }, 0, 'ALT')
    expect(r?.status).toBe('nicht_pruefbar')
    expect(r?.reason).toContain('Transport')
  })

  it('respects a custom tolerance config (zero tolerance flags even a tiny delta)', () => {
    const strict = { ...CO2E_VALIDATION_CONFIG, absoluteToleranceMinor: 0, relativeTolerance: 0 }
    const r = validateCo2eMaterialEmissionsFormula({ ...consistentRow, materialEmissions: 2.500001 }, 0, 'ALT', strict)
    expect(r?.status).toBe('abweichung')
  })
})

// ── Orchestrator + PlausibilityIssue bridge ────────────────────────────────

describe('evaluateCo2eValidation / checkCo2eValidation', () => {
  it('undefined materialRows -> no results at all (no parse attempted)', () => {
    expect(evaluateCo2eValidation({ side: 'ALT', materialRows: undefined })).toEqual([])
  })

  it('null materialRows -> one file-level nicht_pruefbar', () => {
    const results = evaluateCo2eValidation({ side: 'ALT', materialRows: null })
    expect(results).toHaveLength(1)
    expect(results[0].status).toBe('nicht_pruefbar')
  })

  it('a fully consistent row set yields zero PlausibilityIssues', () => {
    const rows: Co2eMaterialRow[] = [
      {
        emissionFactor: 2.0,
        emissionFactorSource: '',
        upstreamPackagingEmissions: 0.3,
        upstreamTransportEmissions: 0.2,
        totalDistance: null,
        transportMode: '',
        materialEmissions: 2.5,
        totalMaterialEmissions: null,
        recycledContentRecq: null,
        cnCode: '',
        sourceCells: {},
        normalized: {},
        rawText: {},
      },
    ]
    expect(checkCo2eValidation({ side: 'ALT', materialRows: rows })).toEqual([])
  })

  it('co2eValidationResultToPlausibilityIssue: bestanden never produces an issue', () => {
    const r = validateCo2eMaterialEmissionsFormula(
      {
        emissionFactor: 2.0,
        emissionFactorSource: '',
        upstreamPackagingEmissions: 0.3,
        upstreamTransportEmissions: 0.2,
        totalDistance: null,
        transportMode: '',
        materialEmissions: 2.5,
        totalMaterialEmissions: null,
        recycledContentRecq: null,
        cnCode: '',
        sourceCells: {},
        normalized: {},
        rawText: {},
      },
      0,
      'ALT',
    )!
    expect(co2eValidationResultToPlausibilityIssue(r)).toBeNull()
  })
})

// KAR-906/P3.2: co2e_* issues (abweichung AND nicht_pruefbar) carry an EN
// counterpart, field names sourced from the CO2e canonical registry.
describe('checkCo2eValidation — bilingual (KAR-906)', () => {
  const consistentRow: Co2eMaterialRow = {
    emissionFactor: 2.0,
    emissionFactorSource: '',
    upstreamPackagingEmissions: 0.3,
    upstreamTransportEmissions: 0.2,
    totalDistance: null,
    transportMode: '',
    materialEmissions: 2.5,
    totalMaterialEmissions: null,
    recycledContentRecq: null,
    cnCode: '',
    sourceCells: {},
    normalized: {},
    rawText: {},
  }

  it('an "abweichung" issue carries explanationEn distinct from explanation', () => {
    const rows: Co2eMaterialRow[] = [{ ...consistentRow, materialEmissions: 9.9 }]
    const issue = checkCo2eValidation({ side: 'ALT', materialRows: rows }).find((i) => i.type === 'co2e_material_emissions')
    expect(issue?.explanationEn).toBeTruthy()
    expect(issue?.explanationEn).not.toBe(issue?.explanation)
    expect(issue?.explanationEn).toMatch(/Material emissions/i)
  })

  it('a "nicht_pruefbar" issue names the missing field in English', () => {
    const rows: Co2eMaterialRow[] = [{ ...consistentRow, emissionFactor: null }]
    const issue = checkCo2eValidation({ side: 'ALT', materialRows: rows }).find(
      (i) => i.type === 'co2e_material_emissions_nicht_pruefbar',
    )
    expect(issue?.explanationEn).toMatch(/Emission factor/i)
  })

  it('the file-level "no sheet" nicht_pruefbar has an English reason', () => {
    const issue = checkCo2eValidation({ side: 'ALT', materialRows: null }).find(
      (i) => i.type === 'co2e_material_emissions_nicht_pruefbar',
    )
    expect(issue?.explanationEn).toMatch(/No CO2e material row block detected/)
  })
})