// QAF persistence mapper (KAR-799, spec B10 / B4).
//
// Pure transformation: engine comparison result -> DB-shaped row objects that
// match the qaf_* migration columns.
//
// Step matches and field diffs are intentionally NOT produced here: their DB
// rows use UUID foreign keys (alt_step_id / neu_step_id / step_match_id) that
// only exist after the step + match rows are inserted, so the server action
// builds them inline once it has resolved those ids. This mapper covers the
// rows whose shape is fully known ahead of insertion.
//
// Net-new. Keeping this pure means the whole persistence shape is unit-tested
// without a database.

import type { QAFFieldKey, QAFRow } from '@/lib/qaf-parser'
import type { QafComparisonResult } from './compare'
import { SUMMARY_METRIC_KEYS, type SummaryMetricsParse } from './summary-metrics'
import { buildEngineConfigDelta } from './engine-config'
import { encodeBilingual } from './bilingual-message'

export interface PersistenceContext {
  projectId: string
  /** Caller-generated id used as the FK for all child rows. */
  comparisonId: string
  baselineFileId: string | null
  comparisonFileId: string | null
  createdBy: string | null
  engineVersion: Record<string, unknown>
}

export interface ComparisonRow {
  id: string
  project_id: string
  part_number: string | null
  baseline_file_id: string | null
  comparison_file_id: string | null
  comparison_mode: string | null
  engine_version: Record<string, unknown>
  status: string
  created_by: string | null
}

export interface StructureChangeRow {
  project_id: string
  comparison_id: string
  change_type: 'new' | 'removed' | 'possible_rename'
  step_label: string
}

export interface PlausibilityIssueRow {
  project_id: string
  comparison_id: string
  issue_type: string
  severity: string
  step_label: string | null
  field: string | null
  explanation: string
}

export interface RootCauseRow {
  project_id: string
  comparison_id: string
  part_number: string | null
  top_drivers: unknown
  management_summary: string
  data_basis: unknown
  uncertainties: string | null
}

export interface SummaryDiffRow {
  project_id: string
  comparison_id: string
  metric_key: string
  alt_value: number | null
  neu_value: number | null
  delta_absolute: number | null
  delta_percent: number | null
  currency: string | null
  status: string
  source_alt: string | null
  source_neu: string | null
  /** Zeilenlabel wörtlich aus der jeweiligen Datei (KAR-996 / V2 R-04). */
  label_file_alt: string | null
  label_file_neu: string | null
  /** true bestätigt, false nicht bestätigt, null nicht erhoben/nicht prüfbar. */
  label_verified_alt: boolean | null
  label_verified_neu: boolean | null
}

export interface SummaryMetricRow {
  project_id: string
  file_id: string
  part_number: string | null
  metric_key: string
  currency: string | null
  value: number
  source_cell: string | null
  /** Zeilenlabel wörtlich aus der Datei (KAR-996 / V2 R-04). */
  label_file: string | null
  /** true bestätigt, false nicht bestätigt, null nicht erhoben/nicht prüfbar. */
  label_verified: boolean | null
  /** Kriterium-2-Provenienz (Provenance-Migration 2026-08-07): Blattname. */
  sheet: string | null
  /** Roher Formeltext der Metrik-Zelle; NULL = keine/nicht erhoben/unresolved. */
  formula: string | null
  /** Wertzustand (Spec Kap. 6.3); NULL = nicht erhoben. */
  value_state: string | null
}

export interface ComparisonRowset {
  comparison: ComparisonRow
  structureChanges: StructureChangeRow[]
  plausibilityIssues: PlausibilityIssueRow[]
  rootCause: RootCauseRow
  summaryDiffs: SummaryDiffRow[]
  materialDiffs: MaterialDiffRow[]
}

/** Eine Zeile qaf_material_diff — eine Material-Zuordnung des Vergleichs. */
export interface MaterialDiffRow {
  project_id: string
  comparison_id: string
  mapping_id: string
  award_rows: number[]
  current_rows: number[]
  award_names: string[]
  current_names: string[]
  match_type: string
  match_rule_id: string | null
  match_details_de: string | null
  cost_award: number | null
  cost_current: number | null
  delta: number | null
  /** Wirkungsart aus classifyMaterialEffects — persistiert, was der Vergleich
   * damals entschied (Reproduzierbarkeit, wie engine_version). */
  effect: string | null
  zero_cost: boolean
}

export function buildComparisonRowset(result: QafComparisonResult, ctx: PersistenceContext): ComparisonRowset {
  const base = { project_id: ctx.projectId, comparison_id: ctx.comparisonId }

  // P1.5/KAR-896: engine_version gets a configVersion stamp + a section-level
  // delta of the ACTUALLY-USED engine config against DEFAULT_ENGINE_CONFIG
  // ("nur Deltas, nicht das ganze Objekt — Platz", backlog design). The
  // common case (nobody overrode anything) persists only the configVersion
  // stamp, no overrides key at all — see buildEngineConfigDelta.
  const engineConfigDelta = buildEngineConfigDelta(result.engineConfig)

  const comparison: ComparisonRow = {
    id: ctx.comparisonId,
    project_id: ctx.projectId,
    part_number: result.partNumber,
    baseline_file_id: ctx.baselineFileId,
    comparison_file_id: ctx.comparisonFileId,
    comparison_mode: null,
    engine_version: {
      ...ctx.engineVersion,
      // ruleEnforcement (KAR-889 reviewer finding, reproducibility) comes off
      // the RESULT, not ctx — it's the mode actually resolved by
      // compareQafPair (default RULE_ENGINE_CONFIG, or an explicit
      // override), not a fixed caller-supplied code version like the rest of
      // ctx.engineVersion. Kept alongside configVersion/engineConfigOverrides
      // below for backward compat — resolvePersistedRuleEnforcement (and any
      // legacy reader) still reads this bare key directly.
      ruleEnforcement: result.ruleEnforcement,
      configVersion: engineConfigDelta.configVersion,
      ...(engineConfigDelta.overrides ? { engineConfigOverrides: engineConfigDelta.overrides } : {}),
    },
    status: 'draft',
    created_by: ctx.createdBy,
  }

  const structureChanges: StructureChangeRow[] = [
    ...result.structureChanges.new.map((label) => ({ ...base, change_type: 'new' as const, step_label: label })),
    ...result.structureChanges.removed.map((label) => ({ ...base, change_type: 'removed' as const, step_label: label })),
    ...result.structureChanges.possible.map((label) => ({
      ...base,
      change_type: 'possible_rename' as const,
      step_label: label,
    })),
  ]

  const plausibilityIssues: PlausibilityIssueRow[] = result.plausibility.map((i) => ({
    ...base,
    issue_type: i.type,
    severity: i.severity,
    step_label: i.step ?? null,
    field: i.field ?? null,
    // KAR-906/P3.2: qaf_plausibility_issue.explanation is a single TEXT
    // column (no schema change) — encodeBilingual folds DE+EN into it when
    // both exist (a plain DE string, byte-identical to pre-KAR-906 behavior,
    // when `explanationEn` is absent); bilingual-message.ts's decodeBilingual
    // unpacks it again for the UI/export, DE-only rows falling back cleanly.
    explanation: encodeBilingual(i.explanation, i.explanationEn),
  }))

  const rootCause: RootCauseRow = {
    ...base,
    part_number: result.rootCause.partNumber,
    top_drivers: result.rootCause.topAbsoluteDrivers,
    management_summary: result.rootCause.managementSummary,
    data_basis: {
      topRelativeDrivers: result.rootCause.topRelativeDrivers,
      structureChanges: result.rootCause.structureChanges,
      currencyEffect: result.rootCause.currencyEffect,
      matchReviewCount: result.matchReviewCount,
    },
    uncertainties: result.rootCause.requiresReviewNote ? 'Unsicheres Matching — Review erforderlich.' : null,
  }

  const summaryDiffs: SummaryDiffRow[] = result.summaryDiffs.map((d) => ({
    ...base,
    metric_key: d.metricKey,
    alt_value: d.altValue,
    neu_value: d.neuValue,
    delta_absolute: d.deltaAbsolute,
    delta_percent: d.deltaPercent,
    currency: d.currency,
    status: d.status,
    source_alt: d.sourceAlt,
    source_neu: d.sourceNeu,
    label_file_alt: d.labelFileAlt,
    label_file_neu: d.labelFileNeu,
    label_verified_alt: d.labelVerifiedAlt,
    label_verified_neu: d.labelVerifiedNeu,
  }))

  // Material-Zuordnungen (Block 2): eine Zeile je Mapping. Die
  // Positionsebenen-Reconciliation wird nicht persistiert — sie ist beim
  // Rehydrieren deterministisch aus diesen Zeilen plus dem
  // Summary-Materialkosten-Diff ableitbar (classifyMaterialEffects).
  const materialDiffs: MaterialDiffRow[] = (result.materialEffects?.mappings ?? []).map((m) => ({
    ...base,
    mapping_id: m.mappingId,
    award_rows: m.awardRows,
    current_rows: m.currentRows,
    award_names: m.awardNames,
    current_names: m.currentNames,
    match_type: m.matchType,
    match_rule_id: m.matchEvidence?.ruleId ?? null,
    match_details_de: m.matchEvidence?.detailsDe ?? null,
    cost_award: m.costAward,
    cost_current: m.costCurrent,
    delta: m.delta,
    effect: m.effect ?? null,
    zero_cost: m.zeroCost ?? false,
  }))

  return { comparison, structureChanges, plausibilityIssues, rootCause, summaryDiffs, materialDiffs }
}

export interface ManufacturingStepRow {
  project_id: string
  file_id: string
  row_index: number
  position_number: string | null
  process_name: string | null
  machine_name: string | null
  part_name: string | null
  raw_values: QAFRow
  normalized: Partial<Record<QAFFieldKey, string | number | null>> | null
  source_cells: Partial<Record<QAFFieldKey, string>> | null
}

/**
 * Map one parsed Fertigungskosten row (lib/qaf-parser.ts) to a
 * qaf_manufacturing_step insert row (KAR-886). `normalized`/`source_cells`
 * come straight from the row's own (optional) provenance — null when the row
 * predates KAR-886 or the parser found no mapped fields, never an empty
 * object (an empty JSONB object would read as "provenance checked, found
 * nothing" instead of "no provenance available").
 */
export function buildManufacturingStepRow(
  ctx: { projectId: string; fileId: string },
  rowIndex: number,
  row: QAFRow,
): ManufacturingStepRow {
  return {
    project_id: ctx.projectId,
    file_id: ctx.fileId,
    row_index: rowIndex,
    position_number: row.positionsnummer,
    process_name: row.prozessbezeichnung,
    machine_name: row.bezeichnungAnlage,
    part_name: row.teilebenennung,
    raw_values: row,
    normalized: row.normalized ?? null,
    source_cells: row.sourceCells ?? null,
  }
}

// ── Recompare replace-set (KAR-899) ─────────────────────────────────────────
//
// recompareComparison (actions.ts) replaces a comparison's derived qaf_*
// child rows insert-first-then-delete-old (no PostgREST transaction). Which
// tables are part of that replace-set differs by mode: a plain pin-recompare
// or role-swap must NEVER touch qaf_plausibility_issue — those findings are
// not matching-dependent (identity/reconciliation/rule-engine checks run per
// FILE, not per step-match) and cannot be re-derived from the single
// last-write-wins qaf_part row anyway (see rehydrate.ts's plausibilityOverride
// doc). A file replace (replaceComparisonFile) genuinely changes which file
// backs the comparison, so the OLD persisted findings describe a file that no
// longer exists in this comparison — recompareComparison's refreshPlausibility
// flag opts into replacing qaf_plausibility_issue too.
//
// Pulled out as a pure, named function (rather than an inline ternary in
// actions.ts) so the mode->table-set decision is unit-testable without a
// database — actions.ts is DB-bound integration glue (see its module header)
// and is deliberately NOT the place this policy is verified.

export const RECOMPARE_REPLACED_CORE_TABLES = [
  'qaf_step_match',
  'qaf_manufacturing_diff',
  'qaf_structure_change',
  'qaf_root_cause',
  'qaf_summary_diff',
  'qaf_material_diff',
] as const

export function recompareReplacedTables(refreshPlausibility: boolean): readonly string[] {
  return refreshPlausibility ? [...RECOMPARE_REPLACED_CORE_TABLES, 'qaf_plausibility_issue'] : RECOMPARE_REPLACED_CORE_TABLES
}

/**
 * Map one file's extracted summary metrics to qaf_summary_metric rows.
 * Metrics without a value are not persisted (absence = not found).
 */
export function buildSummaryMetricRows(
  ctx: { projectId: string; fileId: string; partNumber: string | null },
  parse: SummaryMetricsParse,
): SummaryMetricRow[] {
  const rows: SummaryMetricRow[] = []
  for (const key of SUMMARY_METRIC_KEYS) {
    const m = parse.metrics[key]
    if (!m || m.value === null) continue
    rows.push({
      project_id: ctx.projectId,
      file_id: ctx.fileId,
      part_number: ctx.partNumber,
      metric_key: key,
      currency: parse.currency,
      value: m.value,
      source_cell: m.cell,
      label_file: m.labelFile,
      label_verified: m.labelVerified,
      // Kriterium 2: die Ableitung liegt im Parser (die eine Stelle, die die
      // Grid-Existenz kennt) — hier wird nur durchgereicht. Der
      // unresolved-Platzhalter hat raw '' und wird nie als Formel persistiert.
      sheet: parse.sheet ?? null,
      formula: (() => {
        const p = parse.formulas?.[key]
        return p && !p.unresolved && p.raw !== '' ? p.raw : null
      })(),
      value_state: parse.valueStates?.[key] ?? null,
    })
  }
  return rows
}