/**
 * QAF source loading (QVS-P2, KAR-971) — the DB-wiring boundary between the
 * pure P1 module (mapper.ts / manufacturing-capability.ts: no DB access, see
 * ADR 024 "Modul-Layout") and the P2 DB-backed services (preview.ts /
 * duplicates.ts / creation.ts).
 *
 * E1 (architecture.md §1): reads from `qaf_manufacturing_step`
 * (raw_values/normalized/source_cells), never from `qaf_uploads.parsed_data`
 * (legacy path B) and never re-parses a workbook.
 *
 * All reads go through the CALLER's RLS-scoped Supabase client (never
 * lib/supabase/admin.ts) — a non-owner's SELECT simply returns no row, which
 * this module surfaces as `null` ("not found"). Callers must not turn that
 * into a distinguishable "forbidden" message — no existence leak across
 * projects (architecture.md §9).
 */

import { createHash } from 'node:crypto'
import type { SupabaseClient } from '@supabase/supabase-js'
import { logger } from '@/lib/logger'
import type { QAFRow } from '@/lib/qaf-parser'
import { mapQafRowsToVsmNodes, MAPPING_VERSION } from './mapper'
import type { MapperResult } from './types'

/**
 * QVS-P4 (KAR-973) file-level context — `sum_planned_capacity`/`sum_lot_size`
 * (canonical-fields.ts, "Allgemeine Prämissen"), Datei-Ebene only (no
 * per-step equivalent, see reports/qaf-value-stream-corpus-evidence.md).
 * Sourced from `qaf_summary_metric` (new `metric_key` rows the ingest path
 * writes alongside the 20 money metrics, app/qaf-differences/actions.ts) —
 * NOT from `QafSummary.plannedCapacity`/`lotSize` (lib/qaf-differences),
 * which stays live-parse-only and comes back `null` on rehydrate (see that
 * module's rehydrate.ts) — QVS needs a value that is still readable long
 * after ingest, which only the persisted table provides. `null` per field
 * means "not present in the source file" (or the file predates this PR) —
 * never a fabricated 0.
 */
export interface QvsFileLevelContext {
  plannedCapacityPartsPerYear: number | null
  lotSizeParts: number | null
}

const EMPTY_FILE_LEVEL_CONTEXT: QvsFileLevelContext = { plannedCapacityPartsPerYear: null, lotSizeParts: null }

/**
 * Best-effort — a query failure here must never fail the whole preview/
 * creation flow over what is purely enrichment context (unlike
 * `qaf_manufacturing_step`, nothing structural depends on this). Degrades to
 * `EMPTY_FILE_LEVEL_CONTEXT` (both fields null) on any error, logged
 * server-side, same "advisory, never blocking" discipline duplicates.ts's
 * `findExistingImports` established for the duplicate-hint query.
 */
async function loadQvsFileLevelContext(supabase: SupabaseClient, qafFileId: string): Promise<QvsFileLevelContext> {
  const { data, error } = await supabase
    .from('qaf_summary_metric')
    .select('metric_key, value')
    .eq('file_id', qafFileId)
    .in('metric_key', ['plannedCapacity', 'lotSize'])

  if (error) {
    logger.warn('qvs.file_level_context.query_failed', { message: error.message, code: error.code })
    return { ...EMPTY_FILE_LEVEL_CONTEXT }
  }

  const byKey = new Map((data ?? []).map((r) => [r.metric_key as string, r.value as number | string | null]))
  const toNumber = (v: number | string | null | undefined): number | null => {
    if (v === null || v === undefined) return null
    // Postgres NUMERIC often round-trips as a string via supabase-js/postgrest
    // (precision safety) — coerce, never trust it's already a JS number.
    const n = typeof v === 'number' ? v : Number(v)
    return Number.isFinite(n) ? n : null
  }

  return {
    plannedCapacityPartsPerYear: toNumber(byKey.get('plannedCapacity')),
    lotSizeParts: toNumber(byKey.get('lotSize')),
  }
}

export interface QafFileRef {
  id: string
  projectId: string
  fileHash: string | null
  originalFileName: string
  /** May be null for a pre-versioning-era row — see creation.ts's fallback
   * for the value_stream_imports.parser_version NOT NULL column. */
  parserVersion: string | null
  /** QVS-P4 — see QvsFileLevelContext doc. Always present (never undefined),
   * both fields null when the source file predates this PR or the labels
   * were not found in it. */
  fileLevelContext: QvsFileLevelContext
}

export interface QafSourceRows {
  qafFile: QafFileRef
  /** Source-ordered (row_index ASC, id ASC tiebreak) — array index i
   * corresponds exactly to the `qafSource.rowIndex` the mapper (mapper.ts)
   * will assign to any node built from `rows[i]`. */
  rows: QAFRow[]
  /** Parallel to `rows` — the originating qaf_manufacturing_step.id per row.
   * Used to backfill `qafSource.manufacturingStepId` after mapping (the P1
   * mapper always sets this null; it has no DB row to attach — see
   * lib/vsm-types.ts doc comment on that field). */
  stepIds: string[]
}

/**
 * Load a QAF file's manufacturing-step rows, RLS-scoped to the caller.
 * Returns `null` when the file does not exist OR the caller cannot see it —
 * both collapse to the same "not found" (see module header).
 */
export async function loadQafSourceRows(supabase: SupabaseClient, qafFileId: string): Promise<QafSourceRows | null> {
  const { data: fileRow, error: fileError } = await supabase
    .from('qaf_file')
    .select('id, project_id, file_hash, original_file_name, parser_version')
    .eq('id', qafFileId)
    .maybeSingle()

  if (fileError || !fileRow) return null

  const [stepsResult, fileLevelContext] = await Promise.all([
    supabase
      .from('qaf_manufacturing_step')
      .select('id, raw_values')
      .eq('file_id', qafFileId)
      .order('row_index', { ascending: true })
      .order('id', { ascending: true }),
    loadQvsFileLevelContext(supabase, qafFileId),
  ])
  const { data: stepRows, error: stepsError } = stepsResult

  if (stepsError) return null

  const rows: QAFRow[] = []
  const stepIds: string[] = []
  for (const r of (stepRows ?? []) as Array<{ id: string; raw_values: unknown }>) {
    // raw_values is persisted as the full QAFRow shape (analyze-time parse) —
    // same direct cast every existing rehydration call site uses (e.g.
    // app/qaf-differences/actions.ts, lib/qaf-differences/internal/rehydrate.ts).
    rows.push(r.raw_values as QAFRow)
    stepIds.push(r.id)
  }

  return {
    qafFile: {
      id: fileRow.id as string,
      projectId: fileRow.project_id as string,
      fileHash: (fileRow.file_hash as string | null) ?? null,
      originalFileName: fileRow.original_file_name as string,
      parserVersion: (fileRow.parser_version as string | null) ?? null,
      fileLevelContext,
    },
    rows,
    stepIds,
  }
}

export interface PreviewTokenInput {
  qafFileId: string
  fileHash: string | null
  parserVersion: string | null
  mappingVersion: string
  /** The row_index set that WOULD be included as nodes (i.e. the array
   * indexes the mapper did not put in `excludedRows`) — order-independent
   * (sorted before hashing). */
  includedRowIndexes: readonly number[]
}

/**
 * Deterministic fingerprint of "this is the exact source snapshot a preview
 * was built from" (architecture.md §4 "idempotent über previewToken").
 *
 * Deliberately NOT a signature over user edits (title, deselection) — only
 * over facts that describe the SOURCE state (file identity/version + which
 * rows currently qualify as steps). Two purposes:
 *   1. Staleness check at create time: recomputing this from a fresh DB read
 *      and comparing to the confirmed preview's token detects "the qaf_file
 *      was replaced/reparsed between preview and confirm" (creation.ts
 *      returns `stale_preview` on mismatch) without persisting the preview
 *      itself anywhere.
 *   2. Idempotency key: stored in `value_stream_imports.engine_context.previewToken`
 *      and enforced by a partial unique index (see the migration) so a
 *      double-submit of the same confirmed preview is a no-op, not a second
 *      Wertstrom.
 */
export function computePreviewToken(input: PreviewTokenInput): string {
  const key = [
    input.qafFileId,
    input.fileHash ?? '',
    input.parserVersion ?? '',
    input.mappingVersion,
    [...input.includedRowIndexes].sort((a, b) => a - b).join(','),
  ].join(':')
  return createHash('sha256').update(key).digest('hex')
}

export interface MappedQafSource {
  mapped: MapperResult
  previewToken: string
}

/**
 * Single choke point for "map these rows and fingerprint the result" — used
 * by BOTH preview.ts and creation.ts so the previewToken formula can never
 * drift between the two call sites (a staleness/idempotency mechanism is
 * only trustworthy if both sides compute it identically). Backfills
 * `qafSource.manufacturingStepId` from `source.stepIds` (the P1 mapper always
 * leaves it null — see lib/vsm-types.ts).
 *
 * `importId` is left to the mapper's own default (a fresh random id, P1
 * behavior) for preview callers — that id is provisional and never persisted.
 * creation.ts passes the REAL id it is about to insert as
 * `value_stream_imports.id`, so every node's `qafSource.importId` matches the
 * row that will actually exist once the transaction commits.
 */
export function mapAndFingerprint(source: QafSourceRows, opts: { importId?: string } = {}): MappedQafSource {
  const mapped = mapQafRowsToVsmNodes(source.rows, { importId: opts.importId })

  for (const node of mapped.nodes) {
    if (node.qafSource) {
      node.qafSource.manufacturingStepId = source.stepIds[node.qafSource.rowIndex] ?? null
    }
  }

  const includedRowIndexes = mapped.nodes.map((n) => n.qafSource!.rowIndex)
  const previewToken = computePreviewToken({
    qafFileId: source.qafFile.id,
    fileHash: source.qafFile.fileHash,
    parserVersion: source.qafFile.parserVersion,
    mappingVersion: MAPPING_VERSION,
    includedRowIndexes,
  })

  return { mapped, previewToken }
}