// RPC regression suite for create_value_stream_from_qaf (KAR-971, QVS-P2,
// PR #336 adversarial review — Review-Fixes 1/2/6).
//
// Every existing test that touches this RPC mocks it: creation.test.ts /
// qaf-actions.test.ts never execute the real SQL body (supabase.rpc() is
// always a vi.fn()); rls-value-stream-imports-isolation.test.ts (the only
// suite in this harness that runs against real Postgres) issues raw
// SELECT/UPDATE/DELETE/INSERT against value_stream_imports directly and
// never CALLS the RPC — SECURITY DEFINER bypasses RLS by design, so neither
// suite would ever exercise this function's OWN manual authorization check.
//
// Case (1) below is the committed regression test for the NULL-role bypass
// documented in the migration's own comment + CHANGELOG.md: an authenticated
// caller with no public.user_profiles row makes current_user_role() return
// SQL NULL; `NULL OR false` is NULL, and a bare `IF NOT (NULL) THEN` does
// NOT execute in PL/pgSQL (unlike an RLS USING-expression, where NULL
// correctly means "deny"). A future edit that drops the COALESCE(..., false)
// wrapper — indistinguishable from redundant defensive style to anyone
// unfamiliar with this trap — would silently reopen a real cross-tenant
// write path, and neither the mocked unit tests nor the RLS suite would
// turn red. This file is that missing safety net.
//
// Gated on RLS_TEST_DATABASE_URL → skips in CI, runs via scripts/rls-test/run.sh
// (which applies supabase-migration-value-stream-imports.sql before this
// test, same as rls-value-stream-imports-isolation.test.ts).

import { randomUUID } from 'node:crypto'
import { describe, it, expect, beforeAll, afterAll } from 'vitest'
import { Client } from 'pg'

const DB_URL = process.env.RLS_TEST_DATABASE_URL

// From scripts/rls-test/setup.sql — USER_A owns USER_A_PROJECT/USER_A_QAF_FILE.
// USER_B has no public.user_profiles row AND no ownership of USER_A_PROJECT —
// exactly the NULL-current_user_role(), non-owner caller case 1 requires.
const USER_A = 'aaaa1111-0000-0000-0000-000000000001'
const USER_B = 'bbbb2222-0000-0000-0000-000000000002'
const USER_A_QAF_FILE = '9999aaaa-0000-0000-0000-0000000000d1'

// Dedicated throwaway project + qaf_file for the project-deletion / orphan-
// visibility case (6) — deliberately NOT USER_A_PROJECT/USER_A_QAF_FILE,
// which the rest of this harness (5 other RLS suites) still depends on
// existing afterward.
const ORPHAN_PROJECT = '7777cccc-0000-0000-0000-000000000001'
const ORPHAN_QAF_FILE = '7777cccc-0000-0000-0000-000000000002'

const MINIMAL_NODES = JSON.stringify([{ id: 'n1', type: 'process', x: 40, y: 200, name: 'Schweißen' }])
const NO_CONNECTIONS = JSON.stringify([])

describe.skipIf(!DB_URL)('RPC regression — create_value_stream_from_qaf (KAR-971, Review-Fixes 1/2/6)', () => {
  let client: Client

  beforeAll(async () => {
    client = new Client({ connectionString: DB_URL })
    await client.connect()
    // Superuser (bypasses RLS) — same discipline as scripts/rls-test/setup.sql:
    // seeding is not itself part of what's under test.
    await client.query('RESET ROLE')
    await client.query(
      `INSERT INTO public.projects (id, user_id, supplier_name) VALUES ($1, $2, 'Orphan-Test Supplier') ON CONFLICT (id) DO NOTHING`,
      [ORPHAN_PROJECT, USER_A],
    )
    await client.query(
      `INSERT INTO public.qaf_file (id, project_id, original_file_name) VALUES ($1, $2, 'orphan.xlsx') ON CONFLICT (id) DO NOTHING`,
      [ORPHAN_QAF_FILE, ORPHAN_PROJECT],
    )
  })

  afterAll(async () => {
    await client?.end()
  })

  async function asUser(uid: string, sql: string, params: unknown[] = []) {
    await client.query('RESET ROLE')
    await client.query('SET ROLE authenticated')
    await client.query("SELECT set_config('request.jwt.claim.sub', $1, false)", [uid])
    return client.query(sql, params)
  }

  async function callRpc(
    uid: string,
    opts: {
      qafFileId: string
      title?: string
      importedStepCount?: number | null
      excludedStepCount?: number | null
      importId?: string | null
      previewToken?: string | null
    },
  ) {
    const payload = opts.previewToken ? { engineContext: { previewToken: opts.previewToken } } : {}
    const r = await asUser(
      uid,
      `SELECT public.create_value_stream_from_qaf(
         p_qaf_file_id => $1::uuid,
         p_title => $2::text,
         p_nodes => $3::jsonb,
         p_connections => $4::jsonb,
         p_parser_version => $5::text,
         p_mapping_version => $6::text,
         p_imported_step_count => $7::int,
         p_excluded_step_count => $8::int,
         p_import_id => $9::uuid,
         p_payload => $10::jsonb
       ) AS result`,
      [
        opts.qafFileId,
        opts.title ?? 'RPC-Regressionstest Wertstrom',
        MINIMAL_NODES,
        NO_CONNECTIONS,
        'p1',
        'qvs-1',
        opts.importedStepCount === undefined ? 1 : opts.importedStepCount,
        opts.excludedStepCount === undefined ? 0 : opts.excludedStepCount,
        opts.importId ?? null,
        JSON.stringify(payload),
      ],
    )
    return r.rows[0].result as { value_stream_id: string; import_id: string; idempotent_hit: boolean }
  }

  // Case 1 — THE regression test: a caller with no user_profiles row (so
  // current_user_role() is SQL NULL) and no ownership of the target project
  // must still be rejected. This is the exact bypass the PR body/CHANGELOG
  // documents finding and fixing against real PG17 — this test is what
  // makes that fix permanent and CI-catchable.
  it('rejects a caller with no user_profiles row and no project ownership (NULL-role bypass regression)', async () => {
    await expect(callRpc(USER_B, { qafFileId: USER_A_QAF_FILE })).rejects.toThrow(/qaf_file_not_found/)
  })

  // Case 2 — fresh-create happy path.
  it('creates a fresh value_stream_maps + value_stream_imports pair for the owning caller', async () => {
    const result = await callRpc(USER_A, { qafFileId: USER_A_QAF_FILE })

    expect(result.idempotent_hit).toBe(false)
    expect(result.value_stream_id).toBeTruthy()
    expect(result.import_id).toBeTruthy()

    const vsm = await asUser(USER_A, 'SELECT count(*)::int AS n FROM public.value_stream_maps WHERE id = $1', [result.value_stream_id])
    expect(vsm.rows[0].n).toBe(1)
    const imp = await asUser(USER_A, 'SELECT count(*)::int AS n FROM public.value_stream_imports WHERE id = $1', [result.import_id])
    expect(imp.rows[0].n).toBe(1)
  })

  // Case 3 — idempotency: a second call with the SAME previewToken must
  // return the SAME ids and add ZERO new rows — the actual concurrency-safe
  // mechanism is the partial-unique-index ON CONFLICT DO NOTHING, not just
  // "check-then-insert" (asserted here via a row-count, not just the
  // returned flag).
  it('is idempotent on previewToken — second call returns the same ids, adds zero rows', async () => {
    const token = `case3-${randomUUID()}`
    const first = await callRpc(USER_A, { qafFileId: USER_A_QAF_FILE, previewToken: token })
    expect(first.idempotent_hit).toBe(false)

    const second = await callRpc(USER_A, { qafFileId: USER_A_QAF_FILE, previewToken: token })
    expect(second.idempotent_hit).toBe(true)
    expect(second.value_stream_id).toBe(first.value_stream_id)
    expect(second.import_id).toBe(first.import_id)

    const importCount = await asUser(
      USER_A,
      "SELECT count(*)::int AS n FROM public.value_stream_imports WHERE engine_context ->> 'previewToken' = $1",
      [token],
    )
    expect(importCount.rows[0].n).toBe(1)
    const vsmCount = await asUser(USER_A, 'SELECT count(*)::int AS n FROM public.value_stream_maps WHERE id = $1', [first.value_stream_id])
    expect(vsmCount.rows[0].n).toBe(1)
  })

  // Case 4 — NULL imported/excluded step counts are rejected (the same
  // three-valued-logic trap the RPC's own comment documents for the auth
  // check, guarded explicitly for step counts too since a direct .rpc()
  // caller bypasses the app's zod validation entirely).
  it('rejects NULL imported_step_count / excluded_step_count', async () => {
    await expect(callRpc(USER_A, { qafFileId: USER_A_QAF_FILE, importedStepCount: null })).rejects.toThrow(/invalid_step_count/)
    await expect(callRpc(USER_A, { qafFileId: USER_A_QAF_FILE, excludedStepCount: null })).rejects.toThrow(/invalid_step_count/)
  })

  // Case 5 — Review-Fix 2: an explicit p_import_id colliding with an
  // existing row is rejected with a clean, app-parseable error instead of a
  // raw "duplicate key value violates unique constraint" from the INSERT —
  // and leaves no orphaned value_stream_maps row behind.
  it('rejects an explicit p_import_id that collides with an existing row (Review-Fix 2)', async () => {
    const collidingId = randomUUID()
    const first = await callRpc(USER_A, { qafFileId: USER_A_QAF_FILE, importId: collidingId, previewToken: `case5-first-${randomUUID()}` })
    expect(first.import_id).toBe(collidingId)

    const beforeCount = await asUser(USER_A, 'SELECT count(*)::int AS n FROM public.value_stream_maps')

    // Different previewToken so the idempotent-hit pre-check does NOT
    // short-circuit — isolates the PK-collision path specifically.
    await expect(
      callRpc(USER_A, { qafFileId: USER_A_QAF_FILE, importId: collidingId, previewToken: `case5-second-${randomUUID()}` }),
    ).rejects.toThrow(/import_id_conflict/)

    // The failed call's own value_stream_maps insert (earlier in the same
    // function body) must have rolled back with it — no orphan.
    const afterCount = await asUser(USER_A, 'SELECT count(*)::int AS n FROM public.value_stream_maps')
    expect(afterCount.rows[0].n).toBe(beforeCount.rows[0].n)
  })

  // Case 6 — Review-Fix 1: project deletion no longer cascades away the
  // import audit trail. Create via the REAL RPC, delete the owning project,
  // then confirm the import row survives (project_id now NULL, qaf_file_id
  // also NULL via the cascaded qaf_file deletion) and stays visible to its
  // creator via created_by — not just to admins, and not to a stranger.
  it('survives project deletion — the import audit record is visible to its creator afterward (Review-Fix 1)', async () => {
    const result = await callRpc(USER_A, { qafFileId: ORPHAN_QAF_FILE })

    await client.query('RESET ROLE')
    await client.query('DELETE FROM public.projects WHERE id = $1', [ORPHAN_PROJECT])

    const asCreator = await asUser(USER_A, 'SELECT project_id, qaf_file_id FROM public.value_stream_imports WHERE id = $1', [
      result.import_id,
    ])
    expect(asCreator.rows).toHaveLength(1)
    expect(asCreator.rows[0].project_id).toBeNull()

    const asStranger = await asUser(USER_B, 'SELECT 1 FROM public.value_stream_imports WHERE id = $1', [result.import_id])
    expect(asStranger.rows).toHaveLength(0)
  })
})