import { describe, it, expect } from 'vitest'
import { gridFromCells } from '../summary-parser'
import {
  SUMMARY_METRIC_KEYS,
  parseSummaryMetrics,
  diffSummaryMetrics,
} from '../summary-metrics'

// Fixtures mirror the verified V11 summary layouts: metric labels in column G
// (rows 11–35), value column resolved via the quotation-currency code in
// C26 (legacy) / C25 (V9) matched against the row-10 column headers M..R.

describe('parseSummaryMetrics — legacy Zusammenfassung layout', () => {
  const grid = gridFromCells({
    C26: 'EUR', // V11: legacy reads the AW code from C26 (V9 from C25)
    N10: 'EUR',
    G11: 'Materialkosten',
    N11: 12.5,
    G12: 'Fertigungskosten',
    N12: '3,75', // DE locale string — must go through parseLocaleNumber
    G13: 'SUMME HERSTELLKOSTEN',
    N13: 16.25,
    G14: '3. Enthaltene Verpackung und Transport', // numbered prefix must be ignored
    N14: 0.35,
    G18: 'Ausschusskosten',
    N18: 0.2,
    N19: 0.1, // scrapManufacturing = row below scrapMaterial (no own label)
    N21: 19.9, // totalCosts: no label anywhere → fixedRow fallback on row 21
    G23: 'Sonstige Zuschläge',
    N23: 1.5,
    G25: 'Angebotsbasispreis', // shifted +1 vs. prior row 24 → label anchor wins
    N25: 18,
    G29: 'ANGEBOTSPREIS',
    N29: 20,
    G32: 'Einmalzahlungen',
    G33: 'Entwicklungskosten',
    N33: 100,
    G34: 'Sonderbetriebsmittel',
    N34: 50,
    G35: 'GESAMTEINMALZAHLUNG',
    N35: 150,
  })
  const parsed = parseSummaryMetrics(grid, 'Zusammenfassung')

  it('detects the legacy template from the sheet name', () => {
    expect(parsed.template).toBe('QAF_LEGACY_DE_SUMMARY')
  })

  it('resolves the value column from the C26 currency code and row-10 header', () => {
    expect(parsed.currency).toBe('EUR')
    expect(parsed.metrics.materialCosts.cell).toBe('N11')
  })

  it('extracts label-anchored metrics with provenance and confidence 1', () => {
    const m = parsed.metrics.materialCosts
    expect(m.value).toBe(12.5)
    expect(m.howLocated).toBe('labelMatch')
    expect(m.confidence).toBe(1)
  })

  it('parses DE locale number strings', () => {
    expect(parsed.metrics.manufacturingCosts.value).toBe(3.75)
  })

  it('ignores numbered label prefixes', () => {
    expect(parsed.metrics.packagingTransportIncluded.value).toBe(0.35)
  })

  it('follows a shifted label within the ±3 window', () => {
    const m = parsed.metrics.quotationBasePrice
    expect(m.value).toBe(18)
    expect(m.cell).toBe('N25')
    expect(m.howLocated).toBe('labelMatch')
  })

  it('falls back to the fixed prior row with reduced confidence when no label matches', () => {
    const m = parsed.metrics.totalCosts
    expect(m.value).toBe(19.9)
    expect(m.cell).toBe('N21')
    expect(m.howLocated).toBe('fixedRow')
    expect(m.confidence).toBe(0.6)
  })

  it('reads scrapManufacturing from the row below scrapMaterial', () => {
    expect(parsed.metrics.scrapMaterial.value).toBe(0.2)
    const m = parsed.metrics.scrapManufacturing
    expect(m.value).toBe(0.1)
    expect(m.cell).toBe('N19')
    expect(m.howLocated).toBe('aggregate')
  })

  it('extracts the one-time payment block below its anchor label', () => {
    expect(parsed.metrics.oneTimeDevelopment.value).toBe(100)
    expect(parsed.metrics.oneTimeTools.value).toBe(50)
    expect(parsed.metrics.totalOneTimePayment.value).toBe(150)
    expect(parsed.metrics.totalOneTimePayment.howLocated).toBe('labelMatch')
  })

  it('returns null values (not throws) for metrics that are simply absent', () => {
    expect(parsed.metrics.customsIncluded.value).toBeNull()
    expect(parsed.metrics.quotationPrice.value).toBe(20)
  })
})

describe('parseSummaryMetrics — V9 SUMMARY layout', () => {
  const grid = gridFromCells({
    C25: 'USD', // V11: V9 reads the AW code from C25
    // no matching row-10 header → fallback column P, confidence 0.5
    G11: 'Material costs',
    P11: 5,
    G27: 'Raw material price share energy',
    P27: 0.7,
    G30: 'QUOTATION PRICE',
    P30: 9.5,
  })
  const parsed = parseSummaryMetrics(grid, 'SUMMARY')

  it('detects the V9 template from the sheet name', () => {
    expect(parsed.template).toBe('QAF_V9_SUMMARY')
  })

  it('falls back to column P when no header matches the currency code', () => {
    expect(parsed.currency).toBe('USD')
    expect(parsed.metrics.materialCosts.cell).toBe('P11')
    expect(parsed.metrics.materialCosts.value).toBe(5)
  })

  it('uses the V9 row map (energy share on 27, quotation price on 30)', () => {
    expect(parsed.metrics.rawMaterialPriceShareEnergy.value).toBe(0.7)
    expect(parsed.metrics.quotationPrice.value).toBe(9.5)
  })
})

describe('parseSummaryMetrics — currency-code casing', () => {
  it('matches the row-10 header case-insensitively', () => {
    const grid = gridFromCells({
      C26: 'eur', // free-text cell — casing may differ from the header
      N10: 'EUR',
      G11: 'Materialkosten',
      N11: 7,
    })
    const parsed = parseSummaryMetrics(grid, 'Zusammenfassung')
    expect(parsed.metrics.materialCosts.cell).toBe('N11')
    expect(parsed.metrics.materialCosts.value).toBe(7)
    expect(parsed.currency).toBe('EUR')
  })
})

describe('parseSummaryMetrics — degenerate inputs', () => {
  it('flags a label collision and refuses to pick a value', () => {
    const grid = gridFromCells({
      C26: 'EUR',
      N10: 'EUR',
      G11: 'Materialkosten',
      N11: 1,
      G12: 'Materialkosten',
      N12: 2,
    })
    const m = parseSummaryMetrics(grid, 'Zusammenfassung').metrics.materialCosts
    expect(m.value).toBeNull()
    expect(m.howLocated).toBe('labelCollision')
    expect(m.confidence).toBe(0)
  })

  it('returns all-null metrics for an empty grid', () => {
    const parsed = parseSummaryMetrics([], 'Zusammenfassung')
    for (const key of SUMMARY_METRIC_KEYS) {
      expect(parsed.metrics[key].value).toBeNull()
    }
  })
})

describe('diffSummaryMetrics', () => {
  const alt = parseSummaryMetrics(
    gridFromCells({
      C26: 'EUR',
      N10: 'EUR',
      G11: 'Materialkosten',
      N11: 10,
      G12: 'Fertigungskosten',
      N12: 4,
      G29: 'ANGEBOTSPREIS',
      N29: 20,
    }),
    'Zusammenfassung',
  )
  const neu = parseSummaryMetrics(
    gridFromCells({
      C26: 'EUR',
      N10: 'EUR',
      G11: 'Materialkosten',
      N11: 16, // +60 % → kritisch_50
      G12: 'Fertigungskosten',
      N12: 4.2, // +5 % → anstieg
      G13: 'SUMME HERSTELLKOSTEN',
      N13: 20.2, // only NEU → status 'neu'
      G29: 'ANGEBOTSPREIS',
      N29: 20, // unchanged → konstant
    }),
    'Zusammenfassung',
  )
  const diffs = diffSummaryMetrics(alt, neu)
  const byKey = new Map(diffs.map((d) => [d.metricKey, d]))

  it('emits one row per metric present on at least one side', () => {
    expect(byKey.has('materialCosts')).toBe(true)
    expect(byKey.has('totalProductionCosts')).toBe(true)
    // absent on both sides → no row
    expect(byKey.has('customsIncluded')).toBe(false)
  })

  it('computes absolute and relative deltas with the shared status bands', () => {
    const mat = byKey.get('materialCosts')!
    expect(mat.altValue).toBe(10)
    expect(mat.neuValue).toBe(16)
    expect(mat.deltaAbsolute).toBe(6)
    expect(mat.deltaPercent).toBeCloseTo(0.6, 6)
    expect(mat.status).toBe('kritisch_50')

    expect(byKey.get('manufacturingCosts')!.status).toBe('anstieg')
    expect(byKey.get('quotationPrice')!.status).toBe('konstant')
  })

  it('marks one-sided metrics as neu/entfallen', () => {
    const tpc = byKey.get('totalProductionCosts')!
    expect(tpc.status).toBe('neu')
    expect(tpc.deltaAbsolute).toBeNull()
  })

  it('carries currency and per-side source cells', () => {
    const mat = byKey.get('materialCosts')!
    expect(mat.currency).toBe('EUR')
    expect(mat.sourceAlt).toBe('N11')
    expect(mat.sourceNeu).toBe('N11')
  })
})

// KAR-900/P2.1: formula provenance for summary metrics — parseSummaryMetrics
// takes an optional parallel formula grid; diffSummaryMetrics is formula-aware.
describe('parseSummaryMetrics — formula provenance (KAR-900/P2.1)', () => {
  const grid = gridFromCells({
    C26: 'EUR',
    N10: 'EUR',
    G11: 'Materialkosten',
    N11: 12.5,
    G12: 'Fertigungskosten',
    N12: 3.75,
    G13: 'SUMME HERSTELLKOSTEN',
    N13: 16.25,
  })
  const formulaGrid = gridFromCells({ N13: 'N11+N12' }) as (string | null)[][]

  it('attaches formula provenance for a metric whose cell carries a formula', () => {
    const parsed = parseSummaryMetrics(grid, 'Zusammenfassung', formulaGrid)
    expect(parsed.formulas?.totalProductionCosts?.raw).toBe('N11+N12')
    expect(parsed.formulas?.totalProductionCosts?.hash).toMatch(/^[0-9a-f]{64}$/)
  })

  it('does not attach formula provenance for a plain-value metric', () => {
    const parsed = parseSummaryMetrics(grid, 'Zusammenfassung', formulaGrid)
    expect(parsed.formulas?.materialCosts).toBeUndefined()
  })

  it('omits `formulas` entirely when no formula grid is passed (backward compat)', () => {
    const parsed = parseSummaryMetrics(grid, 'Zusammenfassung')
    expect(parsed.formulas).toBeUndefined()
  })

  it('leitet den Wertzustand an genau einer Stelle ab (Kriterium 2)', () => {
    const parsed = parseSummaryMetrics(grid, 'Zusammenfassung', formulaGrid)
    // Formel-Zelle → formula_and_cached; Wert ohne Formel bei erhobenem Grid
    // → constant; das Blatt kommt als Provenienz-Bestandteil mit.
    expect(parsed.valueStates?.totalProductionCosts).toBe('formula_and_cached')
    expect(parsed.valueStates?.materialCosts).toBe('constant')
    expect(parsed.sheet).toBe('Zusammenfassung')
  })

  it('behauptet ohne Formel-Grid keinen Wertzustand', () => {
    // Ohne Grid ist „keine Formel" nicht wissbar — constant zu behaupten wäre
    // eine Falschaussage im Provenienzfeld.
    const parsed = parseSummaryMetrics(grid, 'Zusammenfassung')
    expect(parsed.valueStates).toBeUndefined()
  })
})

describe('diffSummaryMetrics — formula-aware (KAR-900/P2.1)', () => {
  function withFormula(cells: Record<string, unknown>, formulaCells: Record<string, string>) {
    return parseSummaryMetrics(
      gridFromCells({ C26: 'EUR', N10: 'EUR', G13: 'SUMME HERSTELLKOSTEN', ...cells }),
      'Zusammenfassung',
      gridFromCells(formulaCells) as (string | null)[][],
    )
  }

  it('formula changed, value unchanged -> status overridden to formel_geaendert', () => {
    const alt = withFormula({ N13: 16 }, { N13: 'N11+N12' })
    const neu = withFormula({ N13: 16 }, { N13: 'N11+N12+0' })
    const diff = diffSummaryMetrics(alt, neu).find((d) => d.metricKey === 'totalProductionCosts')!
    expect(diff.status).toBe('formel_geaendert')
    expect(diff.formulaFinding?.kind).toBe('formel_geaendert_wert_gleich')
  })

  it('formula removed and replaced by a hardcoded constant -> formel_zu_konstante', () => {
    const alt = withFormula({ N13: 16 }, { N13: 'N11+N12' })
    const neu = withFormula({ N13: 999 }, {})
    const diff = diffSummaryMetrics(alt, neu).find((d) => d.metricKey === 'totalProductionCosts')!
    expect(diff.status).toBe('formel_zu_konstante')
  })

  it('same formula, different value -> normal delta status + formulaInputsChanged', () => {
    const alt = withFormula({ N13: 16 }, { N13: 'N11+N12' })
    const neu = withFormula({ N13: 30 }, { N13: 'N11+N12' })
    const diff = diffSummaryMetrics(alt, neu).find((d) => d.metricKey === 'totalProductionCosts')!
    expect(diff.status).not.toBe('formel_geaendert')
    expect(diff.formulaInputsChanged).toBe(true)
  })

  it('formulaEngineEnabled=false disables every formula-derived override', () => {
    const alt = withFormula({ N13: 16 }, { N13: 'N11+N12' })
    const neu = withFormula({ N13: 16 }, { N13: 'N11+N12+0' })
    const diff = diffSummaryMetrics(alt, neu, false).find((d) => d.metricKey === 'totalProductionCosts')!
    expect(diff.status).toBe('konstant')
    expect(diff.formulaFinding).toBeUndefined()
  })

  it('is unaffected for metrics with no formula provenance on either side', () => {
    const alt = parseSummaryMetrics(
      gridFromCells({ C26: 'EUR', N10: 'EUR', G11: 'Materialkosten', N11: 10 }),
      'Zusammenfassung',
    )
    const neu = parseSummaryMetrics(
      gridFromCells({ C26: 'EUR', N10: 'EUR', G11: 'Materialkosten', N11: 15 }),
      'Zusammenfassung',
    )
    const diff = diffSummaryMetrics(alt, neu).find((d) => d.metricKey === 'materialCosts')!
    expect(diff.formulaFinding).toBeUndefined()
    expect(diff.formulaInputsChanged).toBeUndefined()
  })
})