import { describe, it, expect } from 'vitest'
import type { QAFRow } from '@/lib/qaf-parser'
import type { QafSummary, QafSummaryKey } from '../types'
import { compareQafPair, type QafFileParsed } from '../compare'
import {
  buildComparisonRowset,
  buildManufacturingStepRow,
  buildSummaryMetricRows,
  recompareReplacedTables,
  RECOMPARE_REPLACED_CORE_TABLES,
  type PersistenceContext,
} from '../persistence-mapper'
import { SUMMARY_METRIC_KEYS, type SummaryMetricsParse } from '../summary-metrics'
import { metricsParse } from './summary-fixtures'
import { materialRowsFromPersistedMeta, type MaterialRow } from '../material-parser'
import { sbmRowsFromPersistedMeta, type SbmRow } from '../sbm-parser'
import { DEFAULT_ENGINE_CONFIG, type EngineConfig } from '../engine-config'
import { decodeBilingual } from '../bilingual-message'

function summary(values: Partial<Record<QafSummaryKey, string | null>>): QafSummary {
  const keys: QafSummaryKey[] = [
    'partNumber', 'quotationDate', 'supplier', 'partName', 'variant',
    'project', 'requestVersion', 'changeIndex', 'supplierNo',
    'peakVolumeYear', 'productionStartSop', 'deliverySite', 'shiftsPerWeek', // KAR-910
    'plannedCapacity', 'lotSize', // QVS-P4
  ]
  const out = {} as QafSummary
  for (const k of keys) out[k] = { value: values[k] ?? null, cell: null }
  return out
}

function mkRow(o: Partial<QAFRow> = {}): QAFRow {
  return {
    positionsnummer: '1', teilebenennung: 'Teil', prozessbezeichnung: 'Montage',
    bezeichnungAnlage: 'Anlage', standort: 'Werk', beschaffungswaehrung: 'EUR', angebotswaehrung: 'EUR',
    zykluszeit: 10, teileProZyklus: 1, anzahlMA: 1, lohnkosten: 30, lohnzuschlagssaetze: 10, mss: 50,
    ruestkosten: 0, fek: 0, rfgk: 0, fk: 100, wechselkurs: 1, anzahlProAngebotsteil: 1, fkAW: 100,
    ausschuss: 2, ausschusskosten: 1, ...o,
  }
}

function file(id: string, date: string, steps: QAFRow[]): QafFileParsed {
  return {
    ref: { id, fileName: `${id}.xlsx`, quotationDate: date },
    summary: summary({ partNumber: '7490365', quotationDate: date, partName: 'Blende' }),
    steps,
  }
}

const alt = file('old', '2023-01-01', [
  mkRow({ positionsnummer: '1', prozessbezeichnung: 'Montage', fk: 100 }),
  mkRow({ positionsnummer: '2', prozessbezeichnung: 'Schweissen', bezeichnungAnlage: 'Schweisszelle', fk: 200 }),
])
const neu = file('new', '2024-01-01', [
  mkRow({ positionsnummer: '1', prozessbezeichnung: 'Montage', fk: 130 }),
  mkRow({ positionsnummer: '2', prozessbezeichnung: 'Schweissen', bezeichnungAnlage: 'Schweisszelle', fk: 500 }),
  mkRow({ positionsnummer: '3', prozessbezeichnung: 'Pruefen', bezeichnungAnlage: 'Pruefstand', fk: 40 }),
])

const ctx: PersistenceContext = {
  projectId: 'proj-1',
  comparisonId: 'cmp-1',
  baselineFileId: 'file-old',
  comparisonFileId: 'file-new',
  createdBy: 'user-1',
  engineVersion: { matcher: '1.0.0' },
}

describe('buildComparisonRowset', () => {
  const rs = buildComparisonRowset(compareQafPair(alt, neu), ctx)

  it('maps the comparison row with context + part number', () => {
    expect(rs.comparison.id).toBe('cmp-1')
    expect(rs.comparison.project_id).toBe('proj-1')
    expect(rs.comparison.part_number).toBe('7490365')
    expect(rs.comparison.baseline_file_id).toBe('file-old')
    expect(rs.comparison.comparison_file_id).toBe('file-new')
    expect(rs.comparison.created_by).toBe('user-1')
  })

  it('maps structure changes (new step)', () => {
    const n = rs.structureChanges.find((s) => s.change_type === 'new')
    expect(n?.step_label).toContain('Pruefen')
  })

  it('maps the root-cause row', () => {
    expect(rs.rootCause.comparison_id).toBe('cmp-1')
    expect(rs.rootCause.management_summary.length).toBeGreaterThan(0)
    expect(rs.rootCause.part_number).toBe('7490365')
  })

  it('maps plausibility issues as an array carrying ownership', () => {
    expect(Array.isArray(rs.plausibilityIssues)).toBe(true)
    expect(rs.plausibilityIssues.every((i) => i.project_id === 'proj-1')).toBe(true)
  })

  it('maps no summary-diff rows when no summary metrics were parsed', () => {
    expect(rs.summaryDiffs).toEqual([])
  })

  it('maps no material-diff rows when a side has no material parse (tri-state)', () => {
    expect(rs.materialDiffs).toEqual([])
  })

  it('maps material mappings to qaf_material_diff rows with the persisted effect (Block 2)', () => {
    const materialRow = (materialCost: number, zeile: number, name: string) => ({
      positionNumber: '1',
      partDesignation: 'Teil',
      materialDesignation: name,
      supplier: '',
      technicalFunction: '',
      countryOfOrigin: '',
      htsCode: '',
      unitOfMeasure: 'Unit_(purchased_part)',
      procurementCurrency: 'EUR',
      costPerUnitBw: null,
      quotationCurrency: 'EUR',
      exchangeRate: null,
      packagingCostPerUnit: null,
      transportCostPerUnit: null,
      customsCbamCostPerUnit: null,
      overheadCost: null,
      costPerUnitAw: null,
      referenceQuantity: null,
      netQuantity: null,
      rebate: null,
      quantityPerQuotedPart: null,
      materialCost,
      scrapRate: null,
      scrapCost: null,
      rawMaterialDesignation: '',
      referenceWeight: null,
      rawMaterialQuotation: null,
      rawMaterialSurcharge: null,
      packagingCostOffer: null,
      sourceCells: { materialCost: `MATERIAL!W${zeile}` },
      normalized: {},
      rawText: {},
    })
    const rsMat = buildComparisonRowset(
      compareQafPair(
        { ...alt, materialRows: [materialRow(100, 11, 'Gehaeuse')] },
        { ...neu, materialRows: [materialRow(150, 11, 'Gehaeuse')] },
      ),
      ctx,
    )
    expect(rsMat.materialDiffs).toHaveLength(1)
    expect(rsMat.materialDiffs[0]).toMatchObject({
      project_id: 'proj-1',
      comparison_id: 'cmp-1',
      match_type: 'exact',
      cost_award: 100,
      cost_current: 150,
      delta: 50,
      effect: 'price_change',
      zero_cost: false,
    })
    expect(rsMat.materialDiffs[0].award_rows).toEqual([11])
  })
})

// Reviewer finding (KAR-889 adversarial review, "Reproduzierbarkeits-Lücke"):
// the active ruleEnforcement mode must be persisted per comparison — otherwise
// a later flip of RULE_ENGINE_CONFIG makes historical comparisons ambiguous
// about which mode produced their rule-engine issues.
describe('buildComparisonRowset — persists the active ruleEnforcement mode (KAR-889)', () => {
  it('folds result.ruleEnforcement into engine_version alongside the caller-supplied versions', () => {
    const result = compareQafPair(alt, neu, { ruleEngineConfig: { ruleEnforcement: 'block' } })
    const rs = buildComparisonRowset(result, ctx)
    expect(rs.comparison.engine_version).toMatchObject({ matcher: '1.0.0', ruleEnforcement: 'block' })
  })

  it('defaults to warn when no explicit config is passed to compareQafPair', () => {
    const result = compareQafPair(alt, neu)
    const rs = buildComparisonRowset(result, ctx)
    expect(rs.comparison.engine_version).toMatchObject({ ruleEnforcement: 'warn' })
  })
})

// P1.5/KAR-896: engine_version also carries a configVersion stamp + a
// section-level delta of the effective engine config against the default —
// "nur Deltas, nicht das ganze Objekt" (backlog design).
describe('buildComparisonRowset — persists configVersion + engineConfigOverrides delta (KAR-896)', () => {
  it('plain default run: configVersion stamp present, no engineConfigOverrides key at all', () => {
    const result = compareQafPair(alt, neu)
    const rs = buildComparisonRowset(result, ctx)
    expect(rs.comparison.engine_version).toMatchObject({ configVersion: DEFAULT_ENGINE_CONFIG.configVersion })
    expect(rs.comparison.engine_version).not.toHaveProperty('engineConfigOverrides')
  })

  it('an overridden section is persisted as a delta, untouched sections are not', () => {
    const overridden: EngineConfig = {
      ...DEFAULT_ENGINE_CONFIG,
      reconciliation: { relativeTolerance: 0.02, absoluteToleranceMinor: 5 },
    }
    const result = compareQafPair(alt, neu, { engineConfig: overridden })
    const rs = buildComparisonRowset(result, ctx)
    expect(rs.comparison.engine_version).toMatchObject({
      configVersion: DEFAULT_ENGINE_CONFIG.configVersion,
      engineConfigOverrides: { reconciliation: { relativeTolerance: 0.02, absoluteToleranceMinor: 5 } },
    })
    const overrides = (rs.comparison.engine_version as Record<string, unknown>).engineConfigOverrides as Record<
      string,
      unknown
    >
    expect(overrides).not.toHaveProperty('matching')
    expect(overrides).not.toHaveProperty('ruleEngine')
  })

  it('a discrete ruleEngineConfig override (pre-existing KAR-889 call shape) also lands in engineConfigOverrides.ruleEngine', () => {
    const result = compareQafPair(alt, neu, { ruleEngineConfig: { ruleEnforcement: 'block' } })
    const rs = buildComparisonRowset(result, ctx)
    expect(rs.comparison.engine_version).toMatchObject({
      ruleEnforcement: 'block',
      engineConfigOverrides: { ruleEngine: { ruleEnforcement: 'block' } },
    })
  })
})

describe('buildComparisonRowset — summary level', () => {
  const result = compareQafPair(
    { ...alt, summaryMetrics: metricsParse({ materialCosts: 10 }) },
    { ...neu, summaryMetrics: metricsParse({ materialCosts: 16 }) },
  )
  const rs = buildComparisonRowset(result, ctx)

  it('maps summary diffs to qaf_summary_diff-shaped rows with ownership', () => {
    const row = rs.summaryDiffs.find((d) => d.metric_key === 'materialCosts')
    expect(row).toBeDefined()
    expect(row).toMatchObject({
      project_id: 'proj-1',
      comparison_id: 'cmp-1',
      metric_key: 'materialCosts',
      alt_value: 10,
      neu_value: 16,
      delta_absolute: 6,
      currency: 'EUR',
      status: 'kritisch_50',
      source_alt: 'N11',
      source_neu: 'N11',
    })
  })
})

// KAR-887/P0.2: reconciliation issues reuse the existing qaf_plausibility_issue
// path (no schema change) — verifies the generic mapper carries a recon_*
// issue through with the same shape as any other plausibility issue.
describe('buildComparisonRowset — Summen-Rekonziliation (KAR-887)', () => {
  it('maps a broken cascade reconciliation issue into plausibilityIssues with ownership', () => {
    const result = compareQafPair(
      { ...alt, summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 900 }) },
      { ...neu, summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 700 }) },
    )
    const rs = buildComparisonRowset(result, ctx)
    const row = rs.plausibilityIssues.find((i) => i.issue_type === 'recon_herstellkosten_cascade')
    expect(row).toMatchObject({ project_id: 'proj-1', comparison_id: 'cmp-1', severity: 'kritisch', step_label: 'ALT' })
    expect(row?.explanation).toContain('700')
    expect(row?.explanation).toContain('900')
  })

  // KAR-906/P3.2: qaf_plausibility_issue has a single TEXT `explanation`
  // column (no schema change) — the bilingual DE/EN pair must survive the
  // persistence-mapper round-trip inside that one column, decodable via
  // bilingual-message.ts's decodeBilingual.
  it('encodes DE+EN into the single explanation TEXT column, decodable via decodeBilingual', () => {
    const result = compareQafPair(
      { ...alt, summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 900 }) },
      { ...neu, summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 700 }) },
    )
    const rs = buildComparisonRowset(result, ctx)
    const row = rs.plausibilityIssues.find((i) => i.issue_type === 'recon_herstellkosten_cascade')
    expect(row).toBeDefined()
    const decoded = decodeBilingual(row!.explanation)
    expect(decoded.de).toContain('900')
    expect(decoded.en).toContain('900')
    expect(decoded.en).not.toBe(decoded.de)
    expect(decoded.en).toMatch(/summary states|independent recomputation/i)
  })

  // #284 adversarial-review fix (confidence 88): R2/R3 rule-engine violations
  // used to interpolate the SAME German fieldLabel into both messageDe and
  // messageEn (rule-engine.ts's STEP_FIELD_LABELS/SUMMARY_FIELD_LABELS are
  // DE-only) — an EN-locale reader would have read `Required field "BMW // allow-customer-string
  // Sachnummer" is not filled in correctly...`. Verifies the REAL canonical
  // EN label survives all the way through encodeBilingual (persistence-
  // mapper.ts) and back out via decodeBilingual, not just that some EN text
  // exists.
  it('a rule_r2 violation (missing mandatory step field) encodes/decodes the REAL canonical EN field label, not the DE one', () => {
    const altMissingPositionsnummer = {
      ...alt,
      steps: [mkRow({ positionsnummer: '', prozessbezeichnung: 'Montage', fk: 100 })],
    }
    const result = compareQafPair(altMissingPositionsnummer, neu)
    const rs = buildComparisonRowset(result, ctx)
    const row = rs.plausibilityIssues.find((i) => i.issue_type === 'rule_r2_mandatory_warn')
    expect(row).toBeDefined()
    const decoded = decodeBilingual(row!.explanation)
    expect(decoded.de).toContain('Positionsnummer Fertigungsschritt')
    expect(decoded.en).toContain('Item number Manufacturing step')
    expect(decoded.en).not.toContain('Positionsnummer')
  })

  it('a rule_r2 violation on a missing SUMMARY field (BMW Sachnummer) encodes/decodes the REAL canonical EN label ("BMW part number")', () => { // allow-customer-string
    const altMissingPartNumber = { ...alt, summary: summary({ partNumber: '', quotationDate: '2023-01-01', partName: 'Blende' }) }
    const result = compareQafPair(altMissingPartNumber, neu)
    const rs = buildComparisonRowset(result, ctx)
    const row = rs.plausibilityIssues.find((i) => i.issue_type === 'rule_r2_mandatory_warn' && i.field === 'BMW Sachnummer') // allow-customer-string
    expect(row).toBeDefined()
    const decoded = decodeBilingual(row!.explanation)
    expect(decoded.de).toContain('BMW Sachnummer') // allow-customer-string
    expect(decoded.en).toContain('BMW part number') // allow-customer-string
    expect(decoded.en).not.toContain('Sachnummer') // allow-customer-string
  })

  it('a rule_r3 violation (optional field, e.g. Ausschusskosten Fertigung) encodes/decodes the REAL canonical EN label', () => {
    const neuWithUnparseableAusschusskosten = {
      ...neu,
      steps: [
        mkRow({
          positionsnummer: '1',
          prozessbezeichnung: 'Montage',
          fk: 100,
          ausschusskosten: null,
          rawText: { ausschusskosten: 'noch offen' },
        }),
      ],
    }
    const result = compareQafPair(alt, neuWithUnparseableAusschusskosten)
    const rs = buildComparisonRowset(result, ctx)
    const row = rs.plausibilityIssues.find((i) => i.issue_type === 'rule_r3_optional_invalid')
    expect(row).toBeDefined()
    const decoded = decodeBilingual(row!.explanation)
    expect(decoded.de).toContain('Ausschusskosten Fertigung')
    expect(decoded.en).toContain('Scrap costs Manufacturing [AW]')
    expect(decoded.en).not.toContain('Ausschusskosten')
  })
})

describe('buildSummaryMetricRows', () => {
  const parse = (() => {
    const metrics = Object.fromEntries(
      SUMMARY_METRIC_KEYS.map((k) => [
        k,
        k === 'materialCosts'
          ? { value: 12.5, cell: 'N11', howLocated: 'labelMatch' as const, confidence: 1 }
          : { value: null, cell: null, howLocated: 'fixedRow' as const, confidence: 0 },
      ]),
    ) as SummaryMetricsParse['metrics']
    return { template: 'QAF_LEGACY_DE_SUMMARY' as const, currency: 'EUR', metrics }
  })()

  const rows = buildSummaryMetricRows(
    { projectId: 'proj-1', fileId: 'file-old', partNumber: '7490365' },
    parse,
  )

  it('emits one qaf_summary_metric row per extracted (non-null) metric', () => {
    expect(rows).toEqual([
      {
        project_id: 'proj-1',
        file_id: 'file-old',
        part_number: '7490365',
        metric_key: 'materialCosts',
        currency: 'EUR',
        value: 12.5,
        source_cell: 'N11',
        // Kriterium-2-Spalten: ohne erhobene Provenienz ehrlich NULL —
        // nie geraten (Weg 4).
        sheet: null,
        formula: null,
        value_state: null,
      },
    ])
  })

  it('reicht die Kriterium-2-Provenienz durch, wo der Parser sie erhoben hat', () => {
    const withProv: SummaryMetricsParse = {
      ...parse,
      sheet: 'Zusammenfassung',
      formulas: { materialCosts: { raw: 'N9+N10', normalized: 'N9+N10', hash: 'h'.repeat(64) } },
      valueStates: { materialCosts: 'formula_and_cached' },
    }
    const [row] = buildSummaryMetricRows({ projectId: 'p', fileId: 'f', partNumber: null }, withProv)
    expect(row).toMatchObject({ sheet: 'Zusammenfassung', formula: 'N9+N10', value_state: 'formula_and_cached' })
  })

  it('persistiert den unresolved-Platzhalter nie als Formel', () => {
    const withUnresolved: SummaryMetricsParse = {
      ...parse,
      sheet: 'Zusammenfassung',
      formulas: { materialCosts: { raw: '', normalized: '', hash: '', unresolved: true } },
      valueStates: {},
    }
    const [row] = buildSummaryMetricRows({ projectId: 'p', fileId: 'f', partNumber: null }, withUnresolved)
    expect(row.formula).toBeNull()
    expect(row.value_state).toBeNull()
  })
})

// KAR-886: source_cells/normalized provenance for qaf_manufacturing_step inserts.
describe('buildManufacturingStepRow (KAR-886)', () => {
  const ctx = { projectId: 'proj-1', fileId: 'file-1' }

  it('carries sourceCells/normalized from a parsed row into source_cells/normalized columns', () => {
    const row = mkRow({
      positionsnummer: '1',
      prozessbezeichnung: 'Montage',
      fk: 100,
      sourceCells: { positionsnummer: 'Fertigungskosten!B2', fk: 'Fertigungskosten!Q2' },
      normalized: { positionsnummer: '1', fk: 100 },
    })

    const dbRow = buildManufacturingStepRow(ctx, 0, row)

    expect(dbRow).toMatchObject({
      project_id: 'proj-1',
      file_id: 'file-1',
      row_index: 0,
      position_number: '1',
      process_name: 'Montage',
      machine_name: 'Anlage',
      part_name: 'Teil',
    })
    expect(dbRow.raw_values).toBe(row)
    expect(dbRow.source_cells).toEqual({ positionsnummer: 'Fertigungskosten!B2', fk: 'Fertigungskosten!Q2' })
    expect(dbRow.normalized).toEqual({ positionsnummer: '1', fk: 100 })
  })

  it('maps to null (not undefined/empty object) when the row has no provenance (pre-KAR-886 shape)', () => {
    const row = mkRow() // no sourceCells / normalized keys at all
    const dbRow = buildManufacturingStepRow(ctx, 3, row)

    expect(dbRow.row_index).toBe(3)
    expect(dbRow.source_cells).toBeNull()
    expect(dbRow.normalized).toBeNull()
    expect(dbRow.raw_values).toBe(row)
  })
})

// KAR-899: recompareComparison's replace-set differs by mode — a plain
// pin-recompare/role-swap must never touch qaf_plausibility_issue (findings
// are not matching-dependent), a file replace must (the old findings describe
// a file that no longer backs the comparison). See persistence-mapper.ts
// module header for the full rationale.
describe('recompareReplacedTables (KAR-899)', () => {
  it('defaults (refreshPlausibility: false) to exactly the pre-existing 5-table replace-set — qaf_plausibility_issue is NOT included', () => {
    const tables = recompareReplacedTables(false)
    expect(tables).toEqual(RECOMPARE_REPLACED_CORE_TABLES)
    expect(tables).not.toContain('qaf_plausibility_issue')
  })

  it('refreshPlausibility: true adds qaf_plausibility_issue on top of the core 5 tables, unchanged', () => {
    const tables = recompareReplacedTables(true)
    expect(tables).toEqual([...RECOMPARE_REPLACED_CORE_TABLES, 'qaf_plausibility_issue'])
  })
})

// KAR-899: end-to-end reproduction of the PR #273 review finding at the pure
// (DB-free) level — recompareComparison/replaceComparisonFile are DB-bound
// glue (see actions.ts module header, tdd-guard:skip), so the actual
// behaviour this fix guarantees is proven here on the pure building blocks it
// composes: compareQafPair + buildComparisonRowset.
describe('KAR-899 — stale reconciliation/plausibility findings after a file replace', () => {
  function minimalMaterialRow(materialCost: number): MaterialRow {
    return {
      positionNumber: '1',
      partDesignation: 'Teil',
      materialDesignation: 'Stahl',
      supplier: '',
      technicalFunction: '',
      countryOfOrigin: '',
      htsCode: '',
      unitOfMeasure: '',
      procurementCurrency: 'EUR',
      costPerUnitBw: null,
      quotationCurrency: 'EUR',
      exchangeRate: 1,
      packagingCostPerUnit: null,
      transportCostPerUnit: null,
      customsCbamCostPerUnit: null,
      overheadCost: null,
      costPerUnitAw: null,
      referenceQuantity: null,
      netQuantity: null,
      rebate: null,
      quantityPerQuotedPart: null,
      materialCost,
      scrapRate: null,
      scrapCost: null,
      rawMaterialDesignation: '',
      referenceWeight: null,
      rawMaterialQuotation: null,
      rawMaterialSurcharge: null,
      packagingCostOffer: null, // KAR-910
      sourceCells: {},
      normalized: {},
      rawText: {},
    }
  }

  const altClean = {
    ...file('old', '2023-01-01', [mkRow({ positionsnummer: '1', prozessbezeichnung: 'Montage', fk: 100 })]),
    summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 700 }),
  }

  it('old (broken) file -> critical herstellkosten_cascade finding is persisted; replacing with a clean file makes it disappear from the fresh rowset', () => {
    const oldNeuBroken = {
      ...file('new', '2024-01-01', [mkRow({ positionsnummer: '1', prozessbezeichnung: 'Montage', fk: 130 })]),
      // 400 + 300 != 900 -> herstellkosten_cascade breach (severity 'kritisch').
      summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 900 }),
    }
    const newNeuClean = {
      ...file('new', '2024-01-01', [mkRow({ positionsnummer: '1', prozessbezeichnung: 'Montage', fk: 130 })]),
      // 400 + 300 == 700 -> reconciles cleanly, no cascade issue.
      summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 700 }),
    }

    const staleRowset = buildComparisonRowset(compareQafPair(altClean, oldNeuBroken), ctx)
    const freshRowset = buildComparisonRowset(compareQafPair(altClean, newNeuClean), ctx)

    expect(staleRowset.plausibilityIssues.some((i) => i.issue_type === 'recon_herstellkosten_cascade')).toBe(true)
    // This is exactly the KAR-899 bug: a plain recompare after replacing the
    // NEU file would keep persisting staleRowset's issue forever, because
    // recompareComparison never wrote qaf_plausibility_issue at all. The fix
    // (refreshPlausibility: true, see recompareReplacedTables above) swaps
    // the persisted rows for freshRowset's — which correctly has none.
    expect(freshRowset.plausibilityIssues.some((i) => i.issue_type === 'recon_herstellkosten_cascade')).toBe(false)
  })

  it('reverse: old (clean) file -> replacing with a broken file makes a NEW critical finding appear in the fresh rowset', () => {
    const oldNeuClean = {
      ...file('new', '2024-01-01', [mkRow({ positionsnummer: '1', prozessbezeichnung: 'Montage', fk: 130 })]),
      summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 700 }),
    }
    const newNeuBroken = {
      ...file('new', '2024-01-01', [mkRow({ positionsnummer: '1', prozessbezeichnung: 'Montage', fk: 130 })]),
      summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 900 }),
    }

    const staleRowset = buildComparisonRowset(compareQafPair(altClean, oldNeuClean), ctx)
    const freshRowset = buildComparisonRowset(compareQafPair(altClean, newNeuBroken), ctx)

    expect(staleRowset.plausibilityIssues.some((i) => i.issue_type === 'recon_herstellkosten_cascade')).toBe(false)
    expect(freshRowset.plausibilityIssues.some((i) => i.issue_type === 'recon_herstellkosten_cascade')).toBe(true)
  })

  // KAR-899 part 2: material/sbm detail-row rehydration. The persisted
  // g60_meta.material/.sbm JSONB round-trips through materialRowsFromPersistedMeta/
  // sbmRowsFromPersistedMeta (material-parser.ts/sbm-parser.ts) into
  // QafFileParsed.materialRows/sbmRows exactly like a live ingest would —
  // this proves compareQafPair sees a real Summen-Check for an intact
  // rehydrated side, and nicht_pruefbar (not a silently omitted check) for a
  // degraded-but-persisted side.
  it('rehydrated material/sbm rows: an intact persisted side reconciles normally, a degraded-persisted side is reported nicht_pruefbar (not silently omitted)', () => {
    const intactMeta = { parseConfidence: 1, unmappedHeaders: [], mappedFieldCount: 28, coreFieldsFound: true }
    const degradedMeta = { parseConfidence: 0, unmappedHeaders: ['??'], mappedFieldCount: 1, coreFieldsFound: false }

    const altWithMaterial: QafFileParsed = {
      ...altClean,
      materialRows: materialRowsFromPersistedMeta({ rows: [minimalMaterialRow(400)], parseMeta: intactMeta }),
    }
    const neuDegradedMaterial: QafFileParsed = {
      ...file('new', '2024-01-01', [mkRow({ positionsnummer: '1', prozessbezeichnung: 'Montage', fk: 130 })]),
      summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 700 }),
      materialRows: materialRowsFromPersistedMeta({ rows: [] as MaterialRow[], parseMeta: degradedMeta }),
      sbmRows: sbmRowsFromPersistedMeta(undefined) as SbmRow[] | null | undefined,
    }

    const result = compareQafPair(altWithMaterial, neuDegradedMaterial)
    const altMaterialIssue = result.plausibility.find((i) => i.type.startsWith('recon_material_detail_sum') && i.step === 'ALT')
    const neuMaterialIssue = result.plausibility.find((i) => i.type.startsWith('recon_material_detail_sum') && i.step === 'NEU')
    const neuSbmIssue = result.plausibility.find((i) => i.type.startsWith('recon_sbm_detail_sum') && i.step === 'NEU')

    // ALT: intact rehydrated MATERIAL rows summing to exactly materialCosts -> bestanden, no issue at all.
    expect(altMaterialIssue).toBeUndefined()
    // NEU: degraded-persisted MATERIAL header -> nicht_pruefbar, explicit finding (never silently dropped).
    expect(neuMaterialIssue?.type).toBe('recon_material_detail_sum_nicht_pruefbar')
    // NEU: sbmRows undefined (no SBM parse ever attempted for this rehydrated side) -> the check is skipped entirely, not even nicht_pruefbar.
    expect(neuSbmIssue).toBeUndefined()
  })
})

// KAR-899 follow-up (adversarial-review finding, confidence 90): before this
// fix, recompareComparison's sideOf() never set QafFileParsed.manufacturingParseMeta
// at all (the field was only ever populated at live ingest — actions.ts —
// and never persisted into qaf_file.g60_meta). Harmless while
// qaf_plausibility_issue was never rewritten, but once refreshPlausibility:true
// deletes+reinserts the persisted issues, a parser_degraded_manufacturing_headers
// finding could structurally never be regenerated via a rehydrated compare —
// it would vanish permanently on the very first file replace, even though the
// underlying file is unchanged and still genuinely degraded. Fixed by
// persisting manufacturingParseMeta into g60_meta at ingest and rehydrating it
// in sideOf() (actions.ts) — these tests exercise the pure compareQafPair
// behaviour that rehydration relies on.
describe('KAR-899 follow-up — manufacturingParseMeta must survive a refresh recompute', () => {
  const degradedMeta = { parseConfidence: 0.7, unmappedHeaders: ['Sonderspalte X'], mappedFieldCount: 18 }
  const cleanMeta = { parseConfidence: 1, unmappedHeaders: [], mappedFieldCount: 22 }
  const altClean = {
    ...file('old', '2023-01-01', [mkRow({ positionsnummer: '1', prozessbezeichnung: 'Montage', fk: 100 })]),
    summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 700 }),
  }

  it('a still-degraded NEU side keeps its parser_degraded_manufacturing_headers finding after the ALT side is replaced (rehydrated meta, not fabricated, not lost)', () => {
    // Simulates sideOf() AFTER the fix: ALT was just replaced (fresh ingest,
    // clean header), NEU is unchanged and was already degraded before the
    // replace — its manufacturingParseMeta comes from qaf_file.g60_meta,
    // rehydrated exactly like ALT's, not re-derived from a live parse.
    const altReplaced: QafFileParsed = { ...altClean, manufacturingParseMeta: cleanMeta }
    const neuUnchangedDegraded: QafFileParsed = {
      ...file('new', '2024-01-01', [mkRow({ positionsnummer: '1', prozessbezeichnung: 'Montage', fk: 130 })]),
      summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 700 }),
      manufacturingParseMeta: degradedMeta,
    }

    const rowset = buildComparisonRowset(compareQafPair(altReplaced, neuUnchangedDegraded), ctx)
    const finding = rowset.plausibilityIssues.find((i) => i.issue_type === 'parser_degraded_manufacturing_headers')
    expect(finding).toBeDefined()
    expect(finding?.step_label).toBe('NEU')
    expect(finding?.explanation).toContain('Sonderspalte X')
  })

  it('a historical file without a persisted manufacturingParseMeta key rehydrates to undefined — no crash, no phantom finding', () => {
    // Simulates sideOf() for a file ingested before this fix: the persisted
    // g60_meta carries no manufacturingParseMeta key at all, so optional
    // chaining yields undefined — never fabricated as clean or degraded.
    const altHistorical: QafFileParsed = { ...altClean, manufacturingParseMeta: undefined }
    const neuHistorical: QafFileParsed = {
      ...file('new', '2024-01-01', [mkRow({ positionsnummer: '1', prozessbezeichnung: 'Montage', fk: 130 })]),
      summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 700 }),
      manufacturingParseMeta: undefined,
    }

    expect(() => compareQafPair(altHistorical, neuHistorical)).not.toThrow()
    const result = compareQafPair(altHistorical, neuHistorical)
    expect(result.plausibility.some((i) => i.type === 'parser_degraded_manufacturing_headers')).toBe(false)
  })

  it('a clean (non-degraded) rehydrated manufacturingParseMeta produces no finding, same as before this fix', () => {
    const altReplaced: QafFileParsed = { ...altClean, manufacturingParseMeta: cleanMeta }
    const neuClean: QafFileParsed = {
      ...file('new', '2024-01-01', [mkRow({ positionsnummer: '1', prozessbezeichnung: 'Montage', fk: 130 })]),
      summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 700 }),
      manufacturingParseMeta: cleanMeta,
    }

    const result = compareQafPair(altReplaced, neuClean)
    expect(result.plausibility.some((i) => i.type === 'parser_degraded_manufacturing_headers')).toBe(false)
  })
})

// KAR-914/P4.4 adversarial-review F3 fix: exactly the same permanent-loss-
// on-first-file-replace bug the KAR-899 follow-up above fixed for
// manufacturingParseMeta, now for workbookSafety. Before this fix,
// recompareComparison's sideOf() never set QafFileParsed.workbookSafety at
// all (only ever populated at live ingest, never persisted into
// qaf_file.g60_meta) — harmless while qaf_plausibility_issue was never
// rewritten, but once refreshPlausibility:true deletes+reinserts the
// persisted issues, a security_macro_present/security_external_links
// finding could structurally never be regenerated via a rehydrated compare —
// it would vanish permanently on the very first file replace, even though
// the underlying file's macro/link content never changed. Fixed by
// persisting workbookSafety into g60_meta at ingest (actions.ts, both the
// G60 and the summary insert) and rehydrating it in sideOf() — these tests
// exercise the pure compareQafPair behaviour that rehydration relies on.
describe('KAR-914 F3 — workbookSafety must survive a refresh recompute', () => {
  const altClean = {
    ...file('old', '2023-01-01', [mkRow({ positionsnummer: '1', prozessbezeichnung: 'Montage', fk: 100 })]),
    summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 700 }),
  }

  it('a macro-bearing NEU side keeps its security_macro_present finding after the ALT side is replaced (rehydrated workbookSafety, not lost)', () => {
    // Simulates sideOf() AFTER the F3 fix: ALT was just replaced (fresh
    // ingest, no macro), NEU is unchanged and was already macro-bearing
    // before the replace — its workbookSafety comes from
    // qaf_file.g60_meta.workbookSafety, rehydrated exactly like ALT's, not
    // re-derived from a live parse (there is no live parse on a recompare).
    const altReplaced: QafFileParsed = { ...altClean, workbookSafety: { macroPresent: false, externalLinksPresent: false } }
    const neuUnchangedMacro: QafFileParsed = {
      ...file('new', '2024-01-01', [mkRow({ positionsnummer: '1', prozessbezeichnung: 'Montage', fk: 130 })]),
      summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 700 }),
      workbookSafety: { macroPresent: true, externalLinksPresent: false },
    }

    const rowset = buildComparisonRowset(compareQafPair(altReplaced, neuUnchangedMacro), ctx)
    const finding = rowset.plausibilityIssues.find((i) => i.issue_type === 'security_macro_present')
    expect(finding).toBeDefined()
    expect(finding?.step_label).toContain('NEU')
    expect(finding?.step_label).toContain('new.xlsx')
  })

  it('an external-link-bearing ALT side keeps its security_external_links finding after the NEU side is replaced', () => {
    const altUnchangedLinks: QafFileParsed = { ...altClean, workbookSafety: { macroPresent: false, externalLinksPresent: true } }
    const neuReplaced: QafFileParsed = {
      ...file('new', '2024-01-01', [mkRow({ positionsnummer: '1', prozessbezeichnung: 'Montage', fk: 130 })]),
      summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 700 }),
      workbookSafety: { macroPresent: false, externalLinksPresent: false },
    }

    const rowset = buildComparisonRowset(compareQafPair(altUnchangedLinks, neuReplaced), ctx)
    const finding = rowset.plausibilityIssues.find((i) => i.issue_type === 'security_external_links')
    expect(finding).toBeDefined()
    expect(finding?.step_label).toContain('ALT')
  })

  it('a historical file without a persisted workbookSafety key rehydrates to undefined — no crash, no phantom finding', () => {
    // Simulates sideOf() for a file ingested before this fix: the persisted
    // g60_meta carries no workbookSafety key at all, so optional chaining
    // yields undefined — never fabricated as clean.
    const altHistorical: QafFileParsed = { ...altClean, workbookSafety: undefined }
    const neuHistorical: QafFileParsed = {
      ...file('new', '2024-01-01', [mkRow({ positionsnummer: '1', prozessbezeichnung: 'Montage', fk: 130 })]),
      summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 700 }),
      workbookSafety: undefined,
    }

    expect(() => compareQafPair(altHistorical, neuHistorical)).not.toThrow()
    const result = compareQafPair(altHistorical, neuHistorical)
    expect(result.plausibility.some((i) => i.type.startsWith('security_'))).toBe(false)
  })

  it('a clean (no macro, no links) rehydrated workbookSafety produces no finding, same as before this fix', () => {
    const altReplaced: QafFileParsed = { ...altClean, workbookSafety: { macroPresent: false, externalLinksPresent: false } }
    const neuClean: QafFileParsed = {
      ...file('new', '2024-01-01', [mkRow({ positionsnummer: '1', prozessbezeichnung: 'Montage', fk: 130 })]),
      summaryMetrics: metricsParse({ materialCosts: 400, manufacturingCosts: 300, totalProductionCosts: 700 }),
      workbookSafety: { macroPresent: false, externalLinksPresent: false },
    }

    const result = compareQafPair(altReplaced, neuClean)
    expect(result.plausibility.some((i) => i.type.startsWith('security_'))).toBe(false)
  })
})