/**
 * DB-wired reimport/sync orchestration (QVS-P5, KAR-974, architecture.md §7 P5).
 *
 * Wires the pure `sync.ts` engine to real `value_stream_maps` /
 * `value_stream_imports` rows, mirroring the P2 preview.ts/creation.ts split:
 * this module loads state, calls the pure functions, and persists the
 * result. RLS-scoped Supabase client only (never `lib/supabase/admin.ts`) —
 * same discipline as every other P2-P4 service in this module.
 *
 * ── Reimport source ───────────────────────────────────────────────────────
 * `qaf_manufacturing_step` rows are immutable once ingested (no code path
 * updates `raw_values` in place — every upload, including a re-upload of the
 * literal same file, inserts a fresh `qaf_file` row). "The source QAF is
 * replaced" / "A newer QAF revision is uploaded" (Spec 21) therefore always
 * means comparing against a DIFFERENT `qaf_file_id`, not re-reading the same
 * one. `sourceQafFileId` defaults to the value stream's own last recorded
 * `qaf_file_id` (a legitimate, if inert, "confirm nothing changed" check —
 * that file's rows cannot change) and `listReimportSourceCandidates` lets a
 * caller offer picking a newer upload in the same project instead.
 *
 * Review-Fix (critical, KAR-974 adversarial review): `qaf_file` RLS is scoped
 * to OWNERSHIP (`project_id IN (SELECT id FROM projects WHERE user_id =
 * auth.uid())`), not to a single project — a caller with several projects can
 * legitimately SELECT a `qaf_file` row that belongs to a DIFFERENT project
 * than the value stream being reimported. Without a check, `loadSyncState`
 * would happily mix an unrelated project's manufacturing data into this
 * value stream's `value_stream_maps`/`value_stream_imports`. `loadSyncState`
 * therefore verifies `source.qafFile.projectId` against the target value
 * stream's OWN `project_id` (the vsm row, falling back to the import row's)
 * before returning anything — a mismatch fails CLOSED, collapsed into the
 * same "not found" `null` every other not-visible/nonexistent case already
 * produces (architecture.md §9: telling a caller "that file exists, just not
 * here" would itself leak the id's existence in another project — the same
 * reasoning this module already applies to RLS 0-row results). Logged
 * server-side under its own key (`qvs.reimport.cross_project_source_rejected`)
 * so the guard stays observable/testable without weakening the client-facing
 * collapse. Applies to BOTH `loadValueStreamSyncDelta` and
 * `applyValueStreamSyncSelection` — both funnel through this one
 * `loadSyncState` chokepoint. Same E4-equivalent discipline creation.ts
 * already enforces for a fresh import ("project_id is derived from the QAF
 * source, never accepted directly"). An orphaned value stream (`project_id`
 * null on both the vsm and import row — e.g. its project was since deleted)
 * has no project boundary left to compare against and is deliberately NOT
 * additionally restricted here — same tolerance this schema already extends
 * orphaned `value_stream_imports` rows elsewhere (see the RLS isolation
 * test's "orphaned row" cases).
 *
 * ── Persistence (no new migration — KAR-974 scope) ───────────────────────
 * `value_stream_maps.nodes/connections` and `value_stream_imports` (existing
 * row, UPDATEd in place — never a new row per reimport) are two separate
 * PostgREST requests, not one transaction (`create_value_stream_from_qaf`'s
 * SECURITY DEFINER RPC is the only cross-table-transaction path this schema
 * has, and it is insert-only — extending it would need a new migration,
 * explicitly out of scope). Order matters: `value_stream_maps` is updated
 * FIRST. If the second update (the audit/snapshot row) then fails, the live
 * document already carries the adopted values but `import_snapshot` still
 * points at the OLD source state.
 *
 * Honest correction (this comment previously claimed the next delta simply
 * "re-detects the same source changes as unseen" — verified false by direct
 * repro against `computeSyncDelta`): a field actually adopted by the first
 * (successful) update now has `liveValue` equal to the fresh source value
 * while `snapshotValue` is still stale, so `computeFieldDeltas` (sync.ts)
 * sees BOTH `sourceChanged` (fresh source vs. stale snapshot) AND
 * `localChanged` (live vs. stale snapshot) true — the field reappears as
 * `BOTH_CHANGED` ("conflict"), not a harmless repeat `SOURCE_CHANGED`. No
 * data is lost or silently duplicated (the live value is already correct,
 * and `BOTH_CHANGED` is never auto-resolved either way, Leitplanke), but the
 * user is asked to resolve a "conflict" that is in fact moot (source and
 * local already agree) — a stale-audit-trail annoyance, not a correctness
 * bug. It self-heals within one cycle: the NEXT successful
 * `applyValueStreamSyncSelection` call — regardless of which resolution the
 * user picks for that one field, since `take_source`/`keep_local` produce
 * the identical value here — unconditionally rewrites `import_snapshot` to
 * the then-current source state (see the update payload below), so the
 * staleness never compounds across more than one retry. The reverse write
 * order would risk the opposite: a snapshot advanced to "already
 * reconciled" while the live document never actually received the adopted
 * values — silent data loss, exactly what the Leitplanken forbid. Same
 * "honesty about atomicity" precedent as creation.ts's
 * `createValueStreamsForVariants`.
 *
 * `imported_step_count` / `excluded_step_count` / `warnings` /
 * `missing_field_reasons` describe the ORIGINAL import event and are left
 * untouched by a reimport — they are not redefined by a sync operation.
 * Reimport-specific facts (what was adopted/kept/removed, when, against
 * which source) are appended to `engine_context.reimportHistory` — schema
 * has no `additionalProperties: false` on `engine_context`, so this is a
 * schema-compatible additive key (schemas/qaf-value-stream-import.schema.json
 * updated to document it), not a new DB field.
 */

import { createHash } from 'node:crypto'
import type { SupabaseClient } from '@supabase/supabase-js'
import { logger } from '@/lib/logger'
import type { VsmConnection, VsmNode } from '@/lib/vsm-types'
import { applySyncAdoption, computeSyncDelta } from './sync'
import { loadQafSourceRows, mapAndFingerprint } from './qaf-source'
import { MAPPING_VERSION } from './mapper'
import { buildQafValueStreamPreview } from './preview'
import { createValueStreamFromQaf, type QvsCreationOutcome, type QvsPreviewConfirmation } from './creation'
import { buildReimportComparisonTitle } from './naming'
import type { QvsSyncAdoptionPlan, QvsSyncApplySummary, QvsSyncDelta } from './types'

const FALLBACK_PARSER_VERSION = 'unrecorded'
/** Bounds `engine_context.reimportHistory` growth over a long-lived value
 * stream's lifetime — oldest entries drop first. Arbitrary but generous. */
const MAX_REIMPORT_HISTORY_ENTRIES = 20

interface ValueStreamImportRow {
  id: string
  qaf_file_id: string | null
  project_id: string | null
  source_file_name: string | null
  file_hash: string | null
  parser_version: string
  mapping_version: string
  engine_context: Record<string, unknown> | null
  import_snapshot: { nodes?: unknown[]; connections?: unknown[] } | null
}

interface ValueStreamMapRow {
  id: string
  project_id: string | null
  title: string
  nodes: VsmNode[]
  connections: VsmConnection[]
  updated_at: string
}

/** Review-Fix (minor, KAR-974 adversarial review): a genuine 0-row result
 * ("not found/not visible") and a transient query failure (timeout,
 * connection reset) used to collapse into the identical `null` — a user saw
 * the same "kein Import-Verlauf gefunden" message for a retry-able hiccup as
 * for a value stream that was simply never QAF-imported. `failed: true`
 * keeps the two distinguishable for callers that want to react differently
 * (`loadSyncState` below), while `row: null, failed: false` still means
 * exactly the same "not found" it always did. */
interface RowLoadResult<T> {
  row: T | null
  failed: boolean
}

async function loadLatestImportRow(supabase: SupabaseClient, valueStreamId: string): Promise<RowLoadResult<ValueStreamImportRow>> {
  const { data, error } = await supabase
    .from('value_stream_imports')
    .select('id, qaf_file_id, project_id, source_file_name, file_hash, parser_version, mapping_version, engine_context, import_snapshot')
    .eq('value_stream_id', valueStreamId)
    .order('created_at', { ascending: false })
    .limit(1)
    .maybeSingle()
  if (error) {
    logger.warn('qvs.reimport.import_row_query_failed', { message: error.message, code: error.code })
    return { row: null, failed: true }
  }
  return { row: (data as ValueStreamImportRow | null) ?? null, failed: false }
}

async function loadVsmRow(supabase: SupabaseClient, valueStreamId: string): Promise<RowLoadResult<ValueStreamMapRow>> {
  const { data, error } = await supabase
    .from('value_stream_maps')
    .select('id, project_id, title, nodes, connections, updated_at')
    .eq('id', valueStreamId)
    .maybeSingle()
  if (error) {
    logger.warn('qvs.reimport.vsm_query_failed', { message: error.message, code: error.code })
    return { row: null, failed: true }
  }
  return { row: (data as ValueStreamMapRow | null) ?? null, failed: false }
}

interface LoadedSyncState {
  vsm: ValueStreamMapRow
  importRow: ValueStreamImportRow
  sourceQafFileId: string
  sourceFileName: string
  sourceFileHash: string | null
  sourceParserVersion: string | null
  sourceNodes: VsmNode[]
  sourceConnections: VsmConnection[]
  sourcePreviewToken: string
  delta: QvsSyncDelta
  deltaToken: string
}

function computeDeltaToken(input: {
  valueStreamId: string
  importId: string
  sourceQafFileId: string
  sourcePreviewToken: string
  vsmUpdatedAt: string
}): string {
  const key = [input.valueStreamId, input.importId, input.sourceQafFileId, input.sourcePreviewToken, input.vsmUpdatedAt].join(':')
  return createHash('sha256').update(key).digest('hex')
}

/** `loadSyncState`'s outcome — `'not_found'` for "not found / not visible to
 * caller" (RLS 0-row), "this value stream has no import record at all"
 * (nothing to sync), "the recorded/requested source qaf_file itself is gone
 * or not visible", AND "the source qaf_file belongs to a different project"
 * (same `null`-style collapse discipline as `loadQafSourceRows`,
 * architecture.md §9: no distinguishable existence leak — see module header).
 * `'load_failed'` (Review-Fix, minor) is its own, distinguishable reason: a
 * genuine query error (transient/infra), never folded into `'not_found'`. */
type LoadSyncStateResult = { ok: true; state: LoadedSyncState } | { ok: false; reason: 'not_found' | 'load_failed' }

/**
 * Loads the value stream's live nodes, its last sync snapshot, and a fresh
 * re-map of the chosen (or defaulted) source qaf_file, then computes the
 * delta. See `LoadSyncStateResult` doc for the two failure reasons.
 */
async function loadSyncState(supabase: SupabaseClient, valueStreamId: string, sourceQafFileId?: string): Promise<LoadSyncStateResult> {
  const [vsmResult, importResult] = await Promise.all([loadVsmRow(supabase, valueStreamId), loadLatestImportRow(supabase, valueStreamId)])
  if (vsmResult.failed || importResult.failed) return { ok: false, reason: 'load_failed' }

  const vsm = vsmResult.row
  const importRow = importResult.row
  if (!vsm || !importRow) return { ok: false, reason: 'not_found' }

  const resolvedSourceQafFileId = sourceQafFileId ?? importRow.qaf_file_id ?? undefined
  if (!resolvedSourceQafFileId) return { ok: false, reason: 'not_found' }

  const source = await loadQafSourceRows(supabase, resolvedSourceQafFileId)
  if (!source) return { ok: false, reason: 'not_found' }

  // Review-Fix (critical) — see module header "Reimport source": fail closed
  // when the resolved source qaf_file belongs to a DIFFERENT project than
  // this value stream (owner-scoped RLS alone does not guarantee same-project).
  const targetProjectId = vsm.project_id ?? importRow.project_id
  if (targetProjectId && source.qafFile.projectId !== targetProjectId) {
    logger.warn('qvs.reimport.cross_project_source_rejected', {
      valueStreamId,
      sourceQafFileId: resolvedSourceQafFileId,
      sourceProjectId: source.qafFile.projectId,
      targetProjectId,
    })
    return { ok: false, reason: 'not_found' }
  }

  const { mapped, previewToken } = mapAndFingerprint(source)
  const snapshotNodes = (importRow.import_snapshot?.nodes as VsmNode[] | undefined) ?? []
  const delta = computeSyncDelta({ sourceNodes: mapped.nodes, snapshotNodes, liveNodes: vsm.nodes })
  const deltaToken = computeDeltaToken({
    valueStreamId,
    importId: importRow.id,
    sourceQafFileId: resolvedSourceQafFileId,
    sourcePreviewToken: previewToken,
    vsmUpdatedAt: vsm.updated_at,
  })

  return {
    ok: true,
    state: {
      vsm,
      importRow,
      sourceQafFileId: resolvedSourceQafFileId,
      sourceFileName: source.qafFile.originalFileName,
      sourceFileHash: source.qafFile.fileHash,
      sourceParserVersion: source.qafFile.parserVersion,
      sourceNodes: mapped.nodes,
      sourceConnections: mapped.connections,
      sourcePreviewToken: previewToken,
      delta,
      deltaToken,
    },
  }
}

export interface QvsSyncDeltaView {
  valueStreamId: string
  importId: string
  sourceQafFileId: string
  sourceFileName: string
  deltaToken: string
  delta: QvsSyncDelta
}

/** Review-Fix (minor, KAR-974 adversarial review): `'not_found'` vs.
 * `'load_failed'` — see `LoadSyncStateResult` doc. Callers (the Action layer,
 * the dialog) must show a different message for a retry-able transient DB
 * error than for a value stream that genuinely has no sync history. */
export type QvsSyncDeltaOutcome = { ok: true; data: QvsSyncDeltaView } | { ok: false; error: 'not_found' | 'load_failed' }

export async function loadValueStreamSyncDelta(supabase: SupabaseClient, valueStreamId: string, options: { sourceQafFileId?: string } = {}): Promise<QvsSyncDeltaOutcome> {
  const result = await loadSyncState(supabase, valueStreamId, options.sourceQafFileId)
  if (!result.ok) return { ok: false, error: result.reason }
  const state = result.state
  return {
    ok: true,
    data: {
      valueStreamId,
      importId: state.importRow.id,
      sourceQafFileId: state.sourceQafFileId,
      sourceFileName: state.sourceFileName,
      deltaToken: state.deltaToken,
      delta: state.delta,
    },
  }
}

export interface QvsReimportSourceCandidate {
  qafFileId: string
  originalFileName: string
  createdAt: string
  /** The qaf_file this value stream's latest import row currently points at
   * — pre-selects this entry in a picker rather than defaulting to "most
   * recent upload", which may be unrelated. */
  isCurrentSource: boolean
}

/**
 * Other `qaf_file` rows in the same project — candidates for "reimport from
 * a different/newer upload" (Spec 21 "A newer QAF revision is uploaded").
 * `null` for "not found/not visible" or "no import record at all" (same
 * collapse as loadSyncState); `[]` when the value stream's project has no
 * (other) candidates — a normal, non-error outcome.
 */
export async function listReimportSourceCandidates(supabase: SupabaseClient, valueStreamId: string): Promise<QvsReimportSourceCandidate[] | null> {
  const [vsmResult, importResult] = await Promise.all([loadVsmRow(supabase, valueStreamId), loadLatestImportRow(supabase, valueStreamId)])
  const vsm = vsmResult.row
  const importRow = importResult.row
  if (!vsm || !importRow) return null

  const projectId = vsm.project_id ?? importRow.project_id
  if (!projectId) return []

  const { data, error } = await supabase.from('qaf_file').select('id, original_file_name, created_at').eq('project_id', projectId).order('created_at', { ascending: false }).limit(50)
  if (error) {
    logger.warn('qvs.reimport.candidates_query_failed', { message: error.message, code: error.code })
    return []
  }

  return ((data ?? []) as Array<{ id: string; original_file_name: string; created_at: string }>).map((f) => ({
    qafFileId: f.id,
    originalFileName: f.original_file_name,
    createdAt: f.created_at,
    isCurrentSource: f.id === importRow.qaf_file_id,
  }))
}

export type QvsSyncApplyError =
  | { code: 'not_found' }
  | { code: 'stale_delta' }
  | { code: 'db_error'; message: string }

export interface QvsSyncApplyResultView {
  valueStreamId: string
  summary: QvsSyncApplySummary
}

export type QvsSyncApplyOutcome = { ok: true; result: QvsSyncApplyResultView } | { ok: false; error: QvsSyncApplyError }

interface ReimportHistoryEntry {
  appliedAt: string
  sourceQafFileId: string
  sourceFileHash: string | null
  summary: QvsSyncApplySummary
}

/**
 * Applies an adoption plan and persists it — see module header for the
 * 2-request ordering rationale. `deltaToken` must match a FRESH recompute
 * (the same staleness discipline `previewToken` gives creation.ts): a
 * mismatch means the live document, the snapshot, or the source changed
 * since the caller loaded its delta, and the caller must re-load rather than
 * apply a decision made against stale data.
 */
export async function applyValueStreamSyncSelection(
  supabase: SupabaseClient,
  params: { valueStreamId: string; sourceQafFileId?: string; deltaToken: string; plan: QvsSyncAdoptionPlan },
): Promise<QvsSyncApplyOutcome> {
  const loaded = await loadSyncState(supabase, params.valueStreamId, params.sourceQafFileId)
  if (!loaded.ok) {
    // Review-Fix (minor): a genuine transient load failure maps to the
    // EXISTING `db_error` code (not `not_found`) — no new client-facing code
    // needed on this path, `db_error` already carries the right "please
    // retry" UI message (qvs-reimport-dialog.tsx APPLY_ERROR_MESSAGES).
    if (loaded.reason === 'load_failed') {
      return { ok: false, error: { code: 'db_error', message: 'Failed to load current sync state (transient database error) — please retry.' } }
    }
    return { ok: false, error: { code: 'not_found' } }
  }
  const state = loaded.state
  if (state.deltaToken !== params.deltaToken) return { ok: false, error: { code: 'stale_delta' } }

  const applyResult = applySyncAdoption(state.delta, state.vsm.nodes, state.vsm.connections, params.plan, state.importRow.id)

  // `.select().maybeSingle()` after the update (same pattern as
  // app/api/wertstrom/[id]/route.ts's PUT handler) — a bare `.update().eq()`
  // reports no error even when RLS silently filtered the target row out
  // (0 rows written); checking the returned row is the only way to tell
  // "wrote successfully" apart from "matched nothing" here.
  const { data: updatedVsm, error: vsmError } = await supabase
    .from('value_stream_maps')
    .update({ nodes: applyResult.nodes, connections: applyResult.connections, updated_at: new Date().toISOString() })
    .eq('id', params.valueStreamId)
    .select('id')
    .maybeSingle()
  if (vsmError) return { ok: false, error: { code: 'db_error', message: vsmError.message } }
  if (!updatedVsm) return { ok: false, error: { code: 'not_found' } }

  const historyEntry: ReimportHistoryEntry = {
    appliedAt: new Date().toISOString(),
    sourceQafFileId: state.sourceQafFileId,
    sourceFileHash: state.sourceFileHash,
    summary: applyResult.summary,
  }
  const priorHistory = Array.isArray(state.importRow.engine_context?.reimportHistory) ? (state.importRow.engine_context!.reimportHistory as ReimportHistoryEntry[]) : []
  const reimportHistory = [...priorHistory, historyEntry].slice(-MAX_REIMPORT_HISTORY_ENTRIES)

  const { data: updatedImportRow, error: importError } = await supabase
    .from('value_stream_imports')
    .update({
      qaf_file_id: state.sourceQafFileId,
      source_file_name: state.sourceFileName,
      file_hash: state.sourceFileHash,
      parser_version: state.sourceParserVersion ?? FALLBACK_PARSER_VERSION,
      mapping_version: MAPPING_VERSION,
      import_snapshot: { nodes: state.sourceNodes, connections: state.sourceConnections },
      engine_context: { ...(state.importRow.engine_context ?? {}), previewToken: state.sourcePreviewToken, mappingVersion: MAPPING_VERSION, reimportHistory },
    })
    .eq('id', state.importRow.id)
    .select('id')
    .maybeSingle()
  if (importError) {
    logger.warn('qvs.reimport.import_row_update_failed', { message: importError.message, code: importError.code, importId: state.importRow.id })
    return { ok: false, error: { code: 'db_error', message: importError.message } }
  }
  if (!updatedImportRow) {
    // value_stream_maps already updated above (see module header — this is
    // the documented, safe-direction partial-failure state, not a rollback).
    logger.warn('qvs.reimport.import_row_update_matched_nothing', { importId: state.importRow.id })
    return { ok: false, error: { code: 'db_error', message: 'value_stream_imports update matched no row' } }
  }

  return { ok: true, result: { valueStreamId: params.valueStreamId, summary: applyResult.summary } }
}

/**
 * "Separater Vergleichs-Wertstrom" (Spec 21 "Create a separate comparison
 * copy") — reuses the EXISTING P2 preview+creation services wholesale rather
 * than any bespoke logic: builds a normal fresh preview of `sourceQafFileId`
 * and confirms it under `<original title> · Reimport <Datum>`.
 *
 * Review-Fix (critical, KAR-974 adversarial review): `computePreviewToken`
 * (qaf-source.ts) is a pure hash of qafFileId+fileHash+parserVersion+
 * mappingVersion+includedRowIndexes — for an UNCHANGED source qaf_file (the
 * common case: the dialog defaults `sourceQafFileId` to the value stream's
 * own recorded source, see reimport.ts's header) this call would build the
 * IDENTICAL token the ORIGINAL import already persisted under
 * `engine_context.previewToken`. `create_value_stream_from_qaf`'s partial
 * unique index is scoped to `(project_id, previewToken)` — without a
 * distinguishing suffix, the RPC would find the ORIGINAL's row as an
 * "existing" match and return `idempotent_hit: true` pointing at the
 * ORIGINAL value stream, i.e. this function would silently hand back the
 * very thing being compared FROM instead of a new comparison copy.
 * `persistedTokenSuffix` (same mechanism as P4's per-variant suffix,
 * `QvsVariantTagging.persistedTokenSuffix` in creation.ts) is therefore
 * ALWAYS set here — `reimport-comparison:<valueStreamId>:<yyyy-mm-dd>` — so
 * this call's persisted/dedup key can never collide with the original
 * import's plain (unsuffixed) token. Day granularity intentionally still
 * makes a genuine same-day resubmit of the SAME comparison idempotent
 * (matches the title's own day-level "Reimport <Datum>" wording — two
 * presses on the same day would produce the identical title string anyway).
 *
 * The caller (createComparisonValueStreamFromSyncAction / qvs-reimport-
 * dialog.tsx) MUST read `result.idempotentHit`: only `false` means an
 * actually-new, independent `value_stream_maps` + `value_stream_imports`
 * pair was written; `true` means nothing new was written (a same-day
 * resubmit found the earlier comparison) and must be surfaced honestly,
 * never as a fresh-copy success. Either way, the ORIGINAL value stream and
 * its own import record are always left untouched by this function — only
 * ITS OWN persisted token ever changes, never the original's.
 */
export async function createComparisonValueStreamFromSync(
  supabase: SupabaseClient,
  params: { valueStreamId: string; sourceQafFileId: string; now?: Date },
): Promise<QvsCreationOutcome | null> {
  const { data: vsm, error } = await supabase.from('value_stream_maps').select('id, title').eq('id', params.valueStreamId).maybeSingle()
  if (error || !vsm) return null

  const preview = await buildQafValueStreamPreview(supabase, params.sourceQafFileId)
  if (!preview) return null

  const now = params.now ?? new Date()
  const confirmation: QvsPreviewConfirmation = {
    qafFileId: params.sourceQafFileId,
    previewToken: preview.previewToken,
    title: buildReimportComparisonTitle(vsm.title as string, now),
  }
  const persistedTokenSuffix = `reimport-comparison:${params.valueStreamId}:${now.toISOString().slice(0, 10)}`
  return createValueStreamFromQaf(supabase, confirmation, undefined, { persistedTokenSuffix })
}