// Multi-QAF column classifier (KAR-930 / Multi-QAF-Programm P1.2, Epic
// KAR-925).
//
// Master-Prompt §10 "each candidate column must be classified as one of"
// list — this module implements the classification DECISION only (pure,
// no ExcelJS). header-parser.ts builds the per-column facts (identity/
// volume/label/formula evidence) from a real header-row/formula grid and
// calls classifyColumn once per candidate column, in the two-pass order
// documented on classifyColumn below.
//
// Real-file evidence for the delta_column vs. comparison_scenario split
// (10-analyse-mx.md D, the module's own "most important regression case"):
// two of that file's non-standard columns are STRUCTURALLY IDENTICAL
// two-cell-difference formulas — they differ only in WHAT they reference.
// One references a benchmark pseudo-slot (a literal, no-own-data-basis
// column) and a real variant — a HELPER relating a benchmark to a variant,
// classified delta_column. The other references two REAL, already-
// identified product-variant columns — a standalone comparison between two
// genuine variants, classified comparison_scenario. This module resolves
// that distinction from the ALREADY-CLASSIFIED kind of each referenced
// column (see classifyColumn's `context.resolvedKinds`), never from the
// formula shape alone (both shapes are byte-identical `=X-Y`).
//
// unknown is a valid, deliberate result (types.ts ColumnClassificationKind
// doc + Master-Prompt §7 "never force an interpretation") — this module
// never invents a kind it cannot support with at least one evidence entry.

import { columnLetterToIndex, type ColumnClassification, type ColumnClassificationEvidence, type ColumnClassificationKind } from './types'
import { normalizeProcessName } from '../normalizer'

// ── Formula-shape regexes (bounded, deliberately narrow — see module header:
// "basissignale", not a full formula-lineage engine (that is P1.5)) ────────

// Leading "=" is OPTIONAL in all three patterns below — formula-engine.ts's
// own doc comment (FormulaProvenance.raw) is explicit that ExcelJS's
// cell.formula (this codebase's one real source of formula text, see
// workbook-adapter.ts worksheetToFormulaGrid) never includes it ("raw is
// exactly what ExcelJS returned for cell.formula (no leading "=" — ExcelJS
// already excludes it)"). A leading "=" is still accepted (not required) so
// hand-authored fixtures/tests that write formulas the human-readable way
// keep working identically — real-file regression proof this mattered:
// KAR-930 review, MX's real AC/AD/AV/AX formulas were silently
// misclassified as helper_calculation instead of delta_column/
// percentage_delta_column/comparison_scenario until this was fixed.
//
// Whitespace AROUND the operator/parens is also tolerated (`\s*`) — KAR-930
// review F4 fix: ExcelJS's cell.formula is exactly what the analyst typed,
// and real spreadsheets are frequently hand-edited with spaces ("=Q5 - S5",
// "=SUM( Q20 : AB20 )"); only `.trim()`-ing the whole string (still done in
// the parse* functions below) never accounted for THIS whitespace, so such
// a formula fell through to helper_calculation instead of delta_column.
// SUM's own case-insensitive 'i' flag already covered "sum(...)" — kept
// unchanged.
const CELL_REF = String.raw`\$?[A-Za-z]{1,3}\$?\d+`
const DIFFERENCE_FORMULA_RE = new RegExp(`^=?\\s*(${CELL_REF})\\s*-\\s*(${CELL_REF})\\s*$`)
const PERCENT_FORMULA_RE = new RegExp(`^=?\\s*(${CELL_REF})\\s*/\\s*(${CELL_REF})\\s*$`)
const SUM_RANGE_FORMULA_RE = new RegExp(`^=?\\s*SUM\\(\\s*(${CELL_REF})\\s*:\\s*(${CELL_REF})\\s*\\)\\s*$`, 'i')

function extractColumnLetters(cellRef: string): string {
  const m = /^\$?([A-Za-z]{1,3})\$?\d+$/.exec(cellRef)
  return m ? m[1].toUpperCase() : ''
}

/** Pure two-cell-difference formula ("=X-Y") — the delta_column/
 * comparison_scenario shape (10-analyse-mx.md D). Returns the two REFERENCED
 * column letters (not the formula's own column), or null when the formula
 * text does not match this exact shape. Deliberately strict (no leading
 * literal, no SUM, no third term) — a looser match would over-claim
 * evidence for a shape this module has real-file proof for only in this
 * exact form. */
export function parseDifferenceFormula(formula: string): { colA: string; colB: string } | null {
  const m = DIFFERENCE_FORMULA_RE.exec(formula.trim())
  if (!m) return null
  return { colA: extractColumnLetters(m[1]), colB: extractColumnLetters(m[2]) }
}

/** Pure two-cell-division formula ("=X/Y") — the percentage_delta_column
 * shape (10-analyse-mx.md D's AD column, "Delta% = Delta-Spalte / Variantenspalte"). */
export function parsePercentFormula(formula: string): { colA: string; colB: string } | null {
  const m = PERCENT_FORMULA_RE.exec(formula.trim())
  if (!m) return null
  return { colA: extractColumnLetters(m[1]), colB: extractColumnLetters(m[2]) }
}

/** SUM(range) formula spanning >=1 column — the `total` shape candidate;
 * caller (classifyColumn) additionally checks the spanned range width. */
export function parseSumRangeFormula(formula: string): { colFrom: string; colTo: string } | null {
  const m = SUM_RANGE_FORMULA_RE.exec(formula.trim())
  if (!m) return null
  return { colFrom: extractColumnLetters(m[1]), colTo: extractColumnLetters(m[2]) }
}

/** Minimum number of DISTINCT columns a SUM(...) range must span to count as
 * `total` evidence (Master-Prompt §10 "total") rather than an incidental
 * 2-cell sum — named per CLAUDE.md "no magic numbers". */
const MIN_TOTAL_SUM_SPAN = 3

// ── Label synonyms (comment columns — DE/EN, same discipline as summary-
// metrics.ts SYNONYMS) ──────────────────────────────────────────────────────

const COMMENT_LABEL_SYNONYMS = ['kommentar', 'bemerkung', 'anmerkung', 'comment', 'note', 'notes', 'remark'].map((s) =>
  normalizeProcessName(s),
)

export function isCommentLabel(label: string | null): boolean {
  if (label === null) return false
  const normalized = normalizeProcessName(label)
  return COMMENT_LABEL_SYNONYMS.some((syn) => normalized === syn || normalized.includes(syn))
}

const BENCHMARK_LABEL_HINT_RE = /\bvs\b|\bversus\b|benchmark|vergleich/i

// ── Per-column facts (built by header-parser.ts from the real grid) ────────

/**
 * Everything classifyColumn needs about ONE candidate column — deliberately
 * flat/plain so header-parser.ts can build it without importing this
 * module's internals, and so tests can construct it directly without a real
 * grid at all.
 */
export interface ColumnClassificationFacts {
  column: string
  columnIndex: number
  /** Primary slot-index-run value (header-parser.ts locateSlotIndexRow), or
   * null when this column is not part of the primary ascending run (either
   * an outlier index value or no index value at all). */
  slotIndex: number | null
  /** True when this column DID carry a numeric value in the slot-index row
   * that failed to extend the primary run (10-analyse-mx.md C's "AB=1
   * Ausreißer") — distinct from having no index value at all. */
  isIndexOutlier: boolean
  /** True when the column has ANY dimension value or non-empty original
   * label (identity.ts hasVariantIdentity). */
  hasIdentity: boolean
  /** True when any of annual/peak/lifetime volume is a positive number. */
  hasPositiveVolume: boolean
  /** Best-available label text for this column (a matched dimension-row
   * label, a variant-code-row value, or null) — used only for the
   * comment_column/benchmark_scenario semantic-hint checks below, never for
   * identity. */
  label: string | null
  /** Formula texts found for this column across the bounded scan region
   * (header-parser.ts), in row order. Empty when no formula grid was
   * supplied or none matched. */
  formulas: readonly string[]
  /** True when the column has at least one non-empty, non-formula NUMERIC
   * cell in the scan region (a literal value, e.g. the benchmark column's
   * hardcoded cost figures). */
  hasLiteralNumericContent: boolean
}

export interface ColumnClassificationContext {
  /** Column-letter -> already-resolved kind, for columns classified in an
   * EARLIER pass — see classifyColumn's two-pass doc. Empty on the first
   * pass. */
  resolvedKinds: ReadonlyMap<string, ColumnClassificationKind>
  /** Column-letter -> VariantDefinition.stableInternalId, for columns that
   * turned out to carry a real variant identity — populates
   * ColumnClassification.relatedVariantIds for delta/percentage/comparison
   * columns that reference them. */
  variantIdByColumn: ReadonlyMap<string, string>
}

const PRODUCT_VARIANT_KINDS: ReadonlySet<ColumnClassificationKind> = new Set(['active_product_variant', 'inactive_product_variant'])

function evidence(signal: string, detail: string): ColumnClassificationEvidence {
  return { signal, detail }
}

function relatedVariantIdsFor(columns: readonly string[], context: ColumnClassificationContext): readonly string[] | undefined {
  const ids = columns.map((c) => context.variantIdByColumn.get(c)).filter((id): id is string => id !== undefined)
  return ids.length > 0 ? ids : undefined
}

/**
 * Classify ONE candidate column. Master-Prompt §10's full evidence vocabulary
 * (header semantics, formula lineage, volume data, variant dimensions,
 * neighbouring-column context) is only PARTIALLY available to this module
 * (P1.2 scope — material-matrix/manufacturing-mapping participation is
 * P1.3/P1.4, not built yet); this function only ever uses what `facts` +
 * `context` actually carry, and returns 'unknown' rather than guessing when
 * no rule fires (Master-Prompt §7 doctrine, carried into §10).
 *
 * TWO-PASS CONTRACT (header-parser.ts orchestrates both passes):
 *   Pass 1 (context.resolvedKinds empty): resolves every column whose kind
 *     needs no cross-column knowledge — active/inactive/reserved (identity +
 *     slot-index membership alone), benchmark_scenario (own literal/label
 *     signals alone), comment_column, total, and helper_calculation. A
 *     column with a pure difference/percent FORMULA shape is tentatively
 *     resolved to delta_column/percentage_delta_column in this pass too
 *     (the safe default — see below), since its final kind depends on
 *     columns this pass has not necessarily classified yet.
 *   Pass 2 (context.resolvedKinds = the full Pass-1 result map): re-run ONLY
 *     the difference/percent-formula columns. If a difference formula's two
 *     referenced columns BOTH resolved to active_product_variant/
 *     inactive_product_variant in Pass 1, upgrade delta_column ->
 *     comparison_scenario (10-analyse-mx.md D's AV case). Otherwise the
 *     Pass-1 delta_column result stands (AC case — one side is a
 *     benchmark_scenario column, not a real variant).
 */
export function classifyColumn(facts: ColumnClassificationFacts, context: ColumnClassificationContext): ColumnClassification {
  const base = { column: facts.column, columnIndex: facts.columnIndex }

  // ── Slot-index-run membership: identity/volume alone decide active/
  // inactive/reserved, mirroring identity.ts deriveActiveState exactly. ────
  if (facts.slotIndex !== null) {
    if (!facts.hasIdentity) {
      return {
        ...base,
        kind: 'reserved_placeholder',
        confidence: 0.85,
        evidence: [evidence('slot_index_no_identity', `Slot-Index ${facts.slotIndex} ohne jede Dimension/Label-Daten.`)],
      }
    }
    const kind: ColumnClassificationKind = facts.hasPositiveVolume ? 'active_product_variant' : 'inactive_product_variant'
    return {
      ...base,
      kind,
      confidence: 0.9,
      evidence: [
        evidence('slot_index_with_identity', `Slot-Index ${facts.slotIndex} mit Dimension/Label-Daten.`),
        ...(facts.hasPositiveVolume
          ? [evidence('positive_volume', 'Mindestens ein Volumenfeld (Jahres-/Peak-/Lifetime) > 0.')]
          : [evidence('zero_volume', 'Kein Volumenfeld > 0 trotz vorhandener Identität.')]),
      ],
    }
  }

  // ── No slot index (or an index-outlier) but full identity + volume: a
  // real variant the primary index run did not capture (10-analyse-
  // clarwe-eu.md C.3's orphaned-but-real BOM columns for a further vehicle
  // programme). ────────────────────────────────────────────────────────
  if (facts.hasIdentity) {
    const kind: ColumnClassificationKind = facts.hasPositiveVolume ? 'active_product_variant' : 'inactive_product_variant'
    return {
      ...base,
      kind,
      confidence: 0.7,
      evidence: [
        evidence(
          facts.isIndexOutlier ? 'identity_index_outlier' : 'identity_no_index',
          facts.isIndexOutlier
            ? 'Volle Dimension/Label-Identität, aber Index-Zeilenwert bricht die primäre Slot-Sequenz (Ausreißer).'
            : 'Volle Dimension/Label-Identität ohne jede Slot-Index-Zuordnung.',
        ),
        ...(facts.hasPositiveVolume ? [evidence('positive_volume', 'Mindestens ein Volumenfeld > 0.')] : []),
      ],
    }
  }

  // ── No identity at all — formula-lineage / literal / label evidence only
  // (Master-Prompt §10 delta/percentage/comment/helper/total/benchmark/
  // unknown). ───────────────────────────────────────────────────────────
  const diffMatch = facts.formulas.map(parseDifferenceFormula).find((m): m is NonNullable<typeof m> => m !== null)
  if (diffMatch) {
    const bothResolvedAsVariants =
      PRODUCT_VARIANT_KINDS.has(context.resolvedKinds.get(diffMatch.colA) as ColumnClassificationKind) &&
      PRODUCT_VARIANT_KINDS.has(context.resolvedKinds.get(diffMatch.colB) as ColumnClassificationKind)
    const relatedVariantIds = relatedVariantIdsFor([diffMatch.colA, diffMatch.colB], context)
    if (bothResolvedAsVariants) {
      return {
        ...base,
        kind: 'comparison_scenario',
        confidence: 0.85,
        evidence: [
          evidence(
            'formula_lineage_difference_two_variants',
            `Reine Differenzformel zwischen zwei bereits als Produktvariante klassifizierten Spalten (${diffMatch.colA}, ${diffMatch.colB}).`,
          ),
        ],
        relatedVariantIds,
      }
    }
    return {
      ...base,
      kind: 'delta_column',
      confidence: 0.75,
      evidence: [
        evidence(
          'formula_lineage_difference',
          `Differenzformel referenziert ${diffMatch.colA} und ${diffMatch.colB} (mindestens eine Seite kein bestätigter Produktvarianten-Slot).`,
        ),
      ],
      relatedVariantIds,
    }
  }

  const percentMatch = facts.formulas.map(parsePercentFormula).find((m): m is NonNullable<typeof m> => m !== null)
  if (percentMatch) {
    const numeratorIsDelta =
      context.resolvedKinds.get(percentMatch.colA) === 'delta_column' || context.resolvedKinds.get(percentMatch.colA) === 'comparison_scenario'
    const relatedVariantIds = relatedVariantIdsFor([percentMatch.colA, percentMatch.colB], context)
    return {
      ...base,
      kind: 'percentage_delta_column',
      confidence: numeratorIsDelta ? 0.85 : 0.6,
      evidence: [
        evidence(
          'formula_lineage_division',
          `Divisionsformel referenziert ${percentMatch.colA} und ${percentMatch.colB}` +
            (numeratorIsDelta ? ` — Zähler ${percentMatch.colA} ist bereits als Delta-/Vergleichsspalte klassifiziert.` : '.'),
        ),
      ],
      relatedVariantIds,
    }
  }

  const sumMatch = facts.formulas.map(parseSumRangeFormula).find((m): m is NonNullable<typeof m> => m !== null)
  if (sumMatch) {
    const fromIdx = columnLetterToIndex(sumMatch.colFrom)
    const toIdx = columnLetterToIndex(sumMatch.colTo)
    const span = Math.abs(toIdx - fromIdx) + 1
    if (span >= MIN_TOTAL_SUM_SPAN) {
      return {
        ...base,
        kind: 'total',
        confidence: 0.8,
        evidence: [evidence('formula_lineage_sum_range', `SUM(${sumMatch.colFrom}:${sumMatch.colTo}) summiert ${span} Spalten.`)],
      }
    }
  }

  if (isCommentLabel(facts.label)) {
    return {
      ...base,
      kind: 'comment_column',
      confidence: 0.7,
      evidence: [evidence('header_semantics_comment_label', `Spaltenlabel "${facts.label}" entspricht einem Kommentar-Synonym.`)],
    }
  }

  if (facts.formulas.length === 0 && facts.hasLiteralNumericContent) {
    const labelHintsBenchmark = facts.label !== null && BENCHMARK_LABEL_HINT_RE.test(facts.label)
    if (facts.label !== null) {
      return {
        ...base,
        kind: 'benchmark_scenario',
        confidence: labelHintsBenchmark ? 0.9 : 0.6,
        evidence: [
          evidence('no_formula_literal_only', 'Nur Literalwerte, keine Formel-Referenz auf Material-/Fertigungskosten-Sheet.'),
          evidence(
            labelHintsBenchmark ? 'header_semantics_benchmark_hint' : 'header_semantics_label_present',
            labelHintsBenchmark
              ? `Spaltenlabel "${facts.label}" enthält ein Vergleichs-/Benchmark-Muster ("vs"/"Vergleich"/"Benchmark").`
              : `Spaltenlabel "${facts.label}" vorhanden, aber ohne Vergleichs-/Benchmark-Muster.`,
          ),
        ],
      }
    }
    // Literal numeric content but no label/formula/identity at all —
    // genuinely ambiguous, never guessed into benchmark_scenario.
    return {
      ...base,
      kind: 'unknown',
      confidence: 0.2,
      evidence: [evidence('no_formula_no_label', 'Nur Literalwerte ohne Label, Formel oder Identität — Klassifikation nicht entscheidbar.')],
    }
  }

  if (facts.formulas.length > 0) {
    return {
      ...base,
      kind: 'helper_calculation',
      confidence: 0.5,
      evidence: [evidence('formula_lineage_unclassified', 'Formel vorhanden, entspricht aber keinem bekannten Delta-/Prozent-/Summen-Muster.')],
    }
  }

  return {
    ...base,
    kind: 'unknown',
    confidence: 0.1,
    evidence: [evidence('no_signal', 'Keine Index-, Identitäts-, Formel- oder Literal-Evidenz gefunden.')],
  }
}