// Formel-Extraktion, -Normalisierung und -Vergleich (KAR-900/P2.1).
//
// Pure unit tests — no ExcelJS workbook needed here (cell-level extraction is
// covered in lib/__tests__/qaf-parser.test.ts and workbook-adapter.test.ts).

import { describe, it, expect } from 'vitest'
import {
  normalizeFormula,
  stripReferenceAnchors,
  formulaHash,
  buildFormulaProvenance,
  unresolvedFormulaProvenance,
  compareFormulaPair,
  formulaFindingToPlausibilityIssue,
  FORMULA_ENGINE_CONFIG,
  FUNCTION_NAME_DE_TO_EN,
} from '../formula-engine'

describe('normalizeFormula', () => {
  it('strips a leading "="', () => {
    expect(normalizeFormula('=A1+A2')).toBe('A1+A2')
  })

  it('uppercases function names and references outside string literals', () => {
    expect(normalizeFormula('sum(a1:a2)')).toBe('SUM(A1:A2)')
  })

  it('maps a German function name to its English equivalent (SUMME -> SUM)', () => {
    expect(normalizeFormula('SUMME(A1:A10)')).toBe('SUM(A1:A10)')
  })

  it('maps WENN -> IF and RUNDEN -> ROUND', () => {
    expect(normalizeFormula('WENN(A1>0,1,0)')).toBe('IF(A1>0,1,0)')
    expect(normalizeFormula('RUNDEN(A1,2)')).toBe('ROUND(A1,2)')
  })

  it('maps a lowercase German function name too (case-insensitive)', () => {
    expect(normalizeFormula('summe(a1:a2)')).toBe('SUM(A1:A2)')
  })

  it('strips whitespace outside string literals', () => {
    expect(normalizeFormula('SUM( A1 : A2 )')).toBe('SUM(A1:A2)')
  })

  it('does not alter the case or whitespace of a quoted string literal', () => {
    expect(normalizeFormula('IF(A1=1,"n. a.",A2)')).toBe('IF(A1=1,"n. a.",A2)')
  })

  it('does not rewrite a German function-like word that appears inside a string literal', () => {
    // "summe" is quoted text here, not a function call — must stay verbatim.
    expect(normalizeFormula('IF(A1=1,"summe",A2)')).toBe('IF(A1=1,"summe",A2)')
  })

  it('is idempotent on an already-normalized formula', () => {
    const once = normalizeFormula('SUMME( a1 ; a2 )')
    expect(normalizeFormula(once)).toBe(once)
  })

  it('leaves a function name unmapped when it has no DE->EN entry (already EN or unknown)', () => {
    expect(normalizeFormula('VLOOKUP(A1,B1:C10,2,0)')).toBe('VLOOKUP(A1,B1:C10,2,0)')
  })
})

describe('FUNCTION_NAME_DE_TO_EN', () => {
  it('is a conservative, documented DE->EN subset (not full Excel coverage)', () => {
    expect(FUNCTION_NAME_DE_TO_EN.SUMME).toBe('SUM')
    expect(FUNCTION_NAME_DE_TO_EN.WENN).toBe('IF')
    expect(FUNCTION_NAME_DE_TO_EN.RUNDEN).toBe('ROUND')
    // Every key is its own DE spelling in ALL CAPS (matches post-uppercase input).
    for (const key of Object.keys(FUNCTION_NAME_DE_TO_EN)) {
      expect(key).toBe(key.toUpperCase())
    }
  })
})

describe('stripReferenceAnchors', () => {
  it('removes $ from absolute and mixed cell references', () => {
    expect(stripReferenceAnchors('$A$1+B$2+$C3')).toBe('A1+B2+C3')
  })

  it('leaves a formula with no anchors unchanged', () => {
    expect(stripReferenceAnchors('SUM(A1:A2)')).toBe('SUM(A1:A2)')
  })

  it('does not strip a $ that appears inside a string literal', () => {
    expect(stripReferenceAnchors('IF(A1=1,"$ Preis",B1)')).toBe('IF(A1=1,"$ Preis",B1)')
  })
})

describe('formulaHash', () => {
  it('is deterministic for the same normalized input', () => {
    expect(formulaHash('SUM(A1:A2)')).toBe(formulaHash('SUM(A1:A2)'))
  })

  it('differs for genuinely different formulas', () => {
    expect(formulaHash('A1+A2')).not.toBe(formulaHash('A1+A3'))
  })

  it('is a hex sha256 digest (64 chars)', () => {
    expect(formulaHash('A1+A2')).toMatch(/^[0-9a-f]{64}$/)
  })
})

describe('buildFormulaProvenance', () => {
  it('keeps the original raw formula verbatim', () => {
    const p = buildFormulaProvenance('=SUMME(A1:A2)')
    expect(p.raw).toBe('=SUMME(A1:A2)')
  })

  it('normalized keeps $ anchors, but hash strips them (absolute vs relative refs unify for the hash)', () => {
    const abs = buildFormulaProvenance('$A$1+B2')
    const rel = buildFormulaProvenance('A1+B2')
    expect(abs.normalized).not.toBe(rel.normalized) // $ preserved in the display form
    expect(abs.hash).toBe(rel.hash) // but unified for the hash
  })

  it('DE and EN equivalent formulas hash identically', () => {
    const de = buildFormulaProvenance('SUMME(A1:A2)')
    const en = buildFormulaProvenance('SUM(A1:A2)')
    expect(de.hash).toBe(en.hash)
  })
})

describe('compareFormulaPair', () => {
  const P = (raw: string) => buildFormulaProvenance(raw)

  it('neither side has a formula -> no_formula_data (todays behavior, no noise)', () => {
    const r = compareFormulaPair({ altFormula: undefined, neuFormula: undefined, altValue: 100, neuValue: 100 })
    expect(r.kind).toBe('no_formula_data')
    expect(r.inputsChanged).toBe(false)
  })

  it('formula added where there was none before -> benign, no_formula_data', () => {
    const r = compareFormulaPair({ altFormula: undefined, neuFormula: P('A1+A2'), altValue: 100, neuValue: 100 })
    expect(r.kind).toBe('no_formula_data')
  })

  it('formula removed and replaced by a real hardcoded value -> formel_zu_konstante', () => {
    const r = compareFormulaPair({ altFormula: P('A1+A2'), neuFormula: undefined, altValue: 100, neuValue: 100 })
    expect(r.kind).toBe('formel_zu_konstante')
  })

  it('formula removed but the field is genuinely blank on the other side -> no signal', () => {
    const r = compareFormulaPair({ altFormula: P('A1+A2'), neuFormula: undefined, altValue: 100, neuValue: null })
    expect(r.kind).toBe('no_formula_data')
  })

  it('same formula (hash equal), same value -> unauffaellig, inputs not changed', () => {
    const r = compareFormulaPair({ altFormula: P('A1+A2'), neuFormula: P('$A$1+A2'), altValue: 100, neuValue: 100 })
    expect(r.kind).toBe('unauffaellig')
    expect(r.inputsChanged).toBe(false)
  })

  it('same formula (hash equal), different value -> unauffaellig + inputsChanged (normal delta semantics apply upstream)', () => {
    const r = compareFormulaPair({ altFormula: P('A1+A2'), neuFormula: P('A1+A2'), altValue: 100, neuValue: 150 })
    expect(r.kind).toBe('unauffaellig')
    expect(r.inputsChanged).toBe(true)
  })

  it('formula changed, value unchanged -> formel_geaendert_wert_gleich (the hidden-risk case)', () => {
    const r = compareFormulaPair({ altFormula: P('A1+A2'), neuFormula: P('A1+A3'), altValue: 100, neuValue: 100 })
    expect(r.kind).toBe('formel_geaendert_wert_gleich')
  })

  it('formula changed, value also changed -> formel_geaendert_wert_geaendert', () => {
    const r = compareFormulaPair({ altFormula: P('A1+A2'), neuFormula: P('A1+A3'), altValue: 100, neuValue: 120 })
    expect(r.kind).toBe('formel_geaendert_wert_geaendert')
  })

  it('treats values within epsilon as equal', () => {
    const r = compareFormulaPair({
      altFormula: P('A1+A2'),
      neuFormula: P('A1+A3'),
      altValue: 100,
      neuValue: 100 + 1e-10,
    })
    expect(r.kind).toBe('formel_geaendert_wert_gleich')
  })
})

// KAR-906/P3.2: every reportable FormulaComparisonResult carries a distinct
// English explanation too.
describe('compareFormulaPair — bilingual (KAR-906)', () => {
  const P = (raw: string) => buildFormulaProvenance(raw)

  it('formel_zu_konstante has a distinct English explanation', () => {
    const r = compareFormulaPair({ altFormula: P('A1+A2'), neuFormula: undefined, altValue: 100, neuValue: 100 })
    expect(r.explanationEn).toBeTruthy()
    expect(r.explanationEn).not.toBe(r.explanation)
    expect(r.explanationEn).toMatch(/removed and replaced/i)
  })

  it('formel_geaendert_wert_gleich has a distinct English explanation', () => {
    const r = compareFormulaPair({ altFormula: P('A1+A2'), neuFormula: P('A1+A3'), altValue: 100, neuValue: 100 })
    expect(r.explanationEn).toMatch(/Formula changed, displayed value unchanged/)
  })

  it('formel_geaendert_wert_geaendert has a distinct English explanation', () => {
    const r = compareFormulaPair({ altFormula: P('A1+A2'), neuFormula: P('A1+A3'), altValue: 100, neuValue: 120 })
    expect(r.explanationEn).toMatch(/Formula AND value changed/)
  })

  it('formulaFindingToPlausibilityIssue carries explanationEn through', () => {
    const r = compareFormulaPair({ altFormula: P('A1+A2'), neuFormula: undefined, altValue: 100, neuValue: 100 })
    const issue = formulaFindingToPlausibilityIssue(r, 'fk', 'Step 1')
    expect(issue?.explanationEn).toBe(r.explanationEn)
  })
})

describe('formulaFindingToPlausibilityIssue', () => {
  it('returns null for no_formula_data and unauffaellig (no finding)', () => {
    expect(
      formulaFindingToPlausibilityIssue(
        { kind: 'no_formula_data', inputsChanged: false, explanation: '' },
        'fk',
        'Step 1',
      ),
    ).toBeNull()
    expect(
      formulaFindingToPlausibilityIssue(
        { kind: 'unauffaellig', inputsChanged: true, explanation: '' },
        'fk',
        'Step 1',
      ),
    ).toBeNull()
  })

  it('formel_geaendert_wert_gleich -> severity pruefen', () => {
    const issue = formulaFindingToPlausibilityIssue(
      { kind: 'formel_geaendert_wert_gleich', inputsChanged: false, explanation: 'Formel geändert.' },
      'fk',
      'Step 1',
    )
    expect(issue?.severity).toBe('pruefen')
    expect(issue?.type).toBe('formel_geaendert_wert_gleich')
    expect(issue?.field).toBe('fk')
    expect(issue?.step).toBe('Step 1')
    expect(issue?.explanation).toBe('Formel geändert.')
  })

  it('formel_zu_konstante -> severity kritisch', () => {
    const issue = formulaFindingToPlausibilityIssue(
      { kind: 'formel_zu_konstante', inputsChanged: false, explanation: 'Formel entfernt.' },
      'fk',
      'Step 1',
    )
    expect(issue?.severity).toBe('kritisch')
    expect(issue?.type).toBe('formel_zu_konstante')
  })

  it('formel_geaendert_wert_geaendert -> severity hinweis', () => {
    const issue = formulaFindingToPlausibilityIssue(
      { kind: 'formel_geaendert_wert_geaendert', inputsChanged: false, explanation: 'Beides geändert.' },
      'fk',
      'Step 1',
    )
    expect(issue?.severity).toBe('hinweis')
  })
})

describe('FORMULA_ENGINE_CONFIG', () => {
  it('defaults to enabled', () => {
    expect(FORMULA_ENGINE_CONFIG).toEqual({ enabled: true })
  })
})

// Adversarial-review fix (KAR-900, 10.07.2026): unresolvedFormulaProvenance()
// / compareFormulaPair's unresolved short-circuit — see FormulaProvenance.unresolved
// doc. An unresolved shared-formula slave must NEVER be treated as "this
// side has no formula" (which would misfire formel_zu_konstante).
describe('unresolvedFormulaProvenance / compareFormulaPair unresolved short-circuit', () => {
  it('unresolvedFormulaProvenance returns the unresolved marker with empty placeholders', () => {
    expect(unresolvedFormulaProvenance()).toEqual({ raw: '', normalized: '', hash: '', unresolved: true })
  })

  it('unresolved on the ALT side alone short-circuits to no_formula_data, never formel_zu_konstante', () => {
    const r = compareFormulaPair({
      altFormula: unresolvedFormulaProvenance(),
      neuFormula: undefined,
      altValue: 100,
      neuValue: 999, // a real hardcoded value on the NEU side — would trigger formel_zu_konstante if misclassified
    })
    expect(r.kind).toBe('no_formula_data')
  })

  it('unresolved on the NEU side alone short-circuits to no_formula_data, never formel_zu_konstante', () => {
    const r = compareFormulaPair({
      altFormula: buildFormulaProvenance('A1+A2'),
      neuFormula: unresolvedFormulaProvenance(),
      altValue: 100,
      neuValue: 999,
    })
    expect(r.kind).toBe('no_formula_data')
  })

  it('unresolved on both sides short-circuits to no_formula_data', () => {
    const r = compareFormulaPair({
      altFormula: unresolvedFormulaProvenance(),
      neuFormula: unresolvedFormulaProvenance(),
      altValue: 100,
      neuValue: 999,
    })
    expect(r.kind).toBe('no_formula_data')
  })

  it('unresolved never produces a plausibility finding', () => {
    const r = compareFormulaPair({
      altFormula: unresolvedFormulaProvenance(),
      neuFormula: undefined,
      altValue: 100,
      neuValue: 999,
    })
    expect(formulaFindingToPlausibilityIssue(r, 'fk', 'Step 1')).toBeNull()
  })
})