// DB-backed duplicate hint (Wertstrom P6, Baustein 4a, §14.2/8 "detect
// duplicate imports"). Read-only — never called from anywhere that also
// writes in the same request. RLS-scoped client only (never
// lib/supabase/admin.ts): "have I already imported this content" is
// inherently a per-caller question, and `value_stream_maps`' own `vsm_own`
// policy already scopes a plain select to what the caller may see.
//
// No DDL available (brief), so there is no dedicated duplicate-lookup
// index — this queries the existing `layout` jsonb column via a PostgREST
// JSON-path filter (`layout->simvsmSource->>sourceSignature`), the same
// "jsonb bucket, no migration" approach `layout.viewport`/`layout.
// shiftModel` already established (see README.md "Warum keine Migration").

import type { SupabaseClient } from '@supabase/supabase-js'
import { logger } from '@/lib/logger'

export interface SimvsmDuplicateHit {
  valueStreamId: string
  title: string
}

export interface FindSimvsmDuplicatesResult {
  /** Keyed by sourceSignature. Only signatures that DID match a prior import
   * are present — a missing key means "no prior import found", not
   * "checked and confirmed clean" when `degraded` is true (see below). */
  bySignature: Record<string, SimvsmDuplicateHit>
  /** true when the lookup query itself failed — callers MUST show "Duplikat-
   * Check nicht verfügbar", never silently read `bySignature` as a confirmed
   * zero-duplicates result (same discipline as lib/qaf-value-stream's
   * `duplicateCheckDegraded`, Review-Fix 3 there). */
  degraded: boolean
}

export async function findSimvsmDuplicatesBySignature(
  supabase: SupabaseClient,
  signatures: readonly string[],
): Promise<FindSimvsmDuplicatesResult> {
  const uniqueSignatures = [...new Set(signatures)]
  if (uniqueSignatures.length === 0) return { bySignature: {}, degraded: false }

  const { data, error } = await supabase
    .from('value_stream_maps')
    .select('id, title, layout')
    .in('layout->simvsmSource->>sourceSignature', uniqueSignatures)

  if (error) {
    logger.warn('simvsm_import.duplicate_check_failed', { message: error.message })
    return { bySignature: {}, degraded: true }
  }

  const bySignature: Record<string, SimvsmDuplicateHit> = {}
  for (const row of (data ?? []) as Array<{ id: string; title: string; layout: unknown }>) {
    const layout = row.layout as { simvsmSource?: { sourceSignature?: string } } | null
    const sig = layout?.simvsmSource?.sourceSignature
    if (sig && uniqueSignatures.includes(sig) && !bySignature[sig]) {
      bySignature[sig] = { valueStreamId: row.id, title: row.title }
    }
  }
  return { bySignature, degraded: false }
}

/** All rows created by one prior confirm call sharing the same
 * `importSignature` (see persist.ts) — used for the idempotent-re-confirm
 * path so a repeat confirm returns the EXACT original result instead of
 * writing a second data set (§14.2/12–13). */
export async function findSimvsmImportByConfirmationSignature(
  supabase: SupabaseClient,
  confirmationSignature: string,
): Promise<{ ok: true; rows: Array<{ id: string; title: string; scenario_kind: string; parent_value_stream_id: string | null }> } | { ok: false }> {
  const { data, error } = await supabase
    .from('value_stream_maps')
    .select('id, title, scenario_kind, parent_value_stream_id, layout')
    .eq('layout->simvsmSource->>importSignature', confirmationSignature)

  if (error) {
    logger.warn('simvsm_import.idempotency_check_failed', { message: error.message })
    return { ok: false }
  }

  return {
    ok: true,
    rows: ((data ?? []) as Array<{ id: string; title: string; scenario_kind: string; parent_value_stream_id: string | null }>).map((r) => ({
      id: r.id,
      title: r.title,
      scenario_kind: r.scenario_kind,
      parent_value_stream_id: r.parent_value_stream_id,
    })),
  }
}