'use server'

// Server-actions voor de Idea-entity (M12). Volgt docs/patterns/server-action.md:
// auth → demo-guard → rate-limit → zod-validate → user_id-scope-check → write
// → revalidatePath. Idee is strikt user_id-only (zie M12 grill-keuze 8) — er
// is GEEN productAccessFilter; idee is privé voor de eigenaar, ook als-ie
// gekoppeld is aan een team-product.

import { revalidatePath } from 'next/cache'
import { cookies } from 'next/headers'
import { getIronSession } from 'iron-session'

import { z } from 'zod'

import { prisma } from '@/lib/prisma'
import { getJobConfigSnapshot } from '@/lib/job-config-snapshot'
import { SessionData, sessionOptions } from '@/lib/session'
import { enforceUserRateLimit } from '@/lib/rate-limit'
import { ideaCreateSchema, ideaUpdateSchema } from '@/lib/schemas/idea'
import { canTransition, isGrillMdEditable, isIdeaEditable, isPlanMdEditable } from '@/lib/idea-status'
import { nextIdeaCode } from '@/lib/idea-code-server'
import { parsePlanMd } from '@/lib/idea-plan-parser'
import { ACTIVE_JOB_STATUSES } from '@/lib/job-status'
import { parseCodeNumber } from '@/lib/code'

import type { ClaudeJobKind, Idea, IdeaStatus } from '@prisma/client'

// Worker-presence: aligned met /api/realtime/solo.
const WORKER_FRESH_MS = 15_000
async function countActiveWorkers(userId: string): Promise<number> {
  return prisma.claudeWorker.count({
    where: {
      user_id: userId,
      last_seen_at: { gt: new Date(Date.now() - WORKER_FRESH_MS) },
    },
  })
}

async function getSession() {
  return getIronSession<SessionData>(await cookies(), sessionOptions)
}

// Standaard error-shape voor consistente UI-rendering — zie ook actions/todos.ts.
type ActionResult<T = void> =
  | { success: true; data?: T }
  | { error: string; code?: number; details?: unknown }

// ---------------------------------------------------------------------------
// CRUD

export async function createIdeaAction(input: {
  title: string
  description?: string | null
  product_id?: string | null
}): Promise<ActionResult<{ id: string; code: string }>> {
  const session = await getSession()
  if (!session.userId) return { error: 'Niet ingelogd', code: 401 }
  if (session.isDemo) return { error: 'Niet beschikbaar in demo-modus', code: 403 }

  const limited = enforceUserRateLimit('create-idea', session.userId)
  if (limited) return limited

  const parsed = ideaCreateSchema.safeParse(input)
  if (!parsed.success) {
    return { error: 'Validatie mislukt', code: 422, details: parsed.error.flatten().fieldErrors }
  }

  const userId = session.userId
  // Atomair: code + create in dezelfde transactie zodat een crash tussenin geen
  // counter-gat veroorzaakt zonder bijbehorend idee.
  const idea = await prisma.$transaction(async (tx) => {
    const code = await nextIdeaCode(userId, tx)
    return tx.idea.create({
      data: {
        user_id: userId,
        product_id: parsed.data.product_id ?? null,
        code,
        title: parsed.data.title,
        description: parsed.data.description ?? null,
        status: 'DRAFT',
      },
      select: { id: true, code: true },
    })
  })

  revalidatePath('/ideas')
  return { success: true, data: idea }
}

export async function updateIdeaAction(
  id: string,
  input: { title?: string; description?: string | null; product_id?: string | null },
): Promise<ActionResult> {
  const session = await getSession()
  if (!session.userId) return { error: 'Niet ingelogd', code: 401 }
  if (session.isDemo) return { error: 'Niet beschikbaar in demo-modus', code: 403 }

  const parsed = ideaUpdateSchema.safeParse(input)
  if (!parsed.success) {
    return { error: 'Validatie mislukt', code: 422, details: parsed.error.flatten().fieldErrors }
  }

  const idea = await prisma.idea.findFirst({
    where: { id, user_id: session.userId },
    select: { id: true, status: true },
  })
  if (!idea) return { error: 'Idee niet gevonden', code: 404 }
  if (!isIdeaEditable(idea.status)) {
    return { error: `Idee is niet bewerkbaar in status ${idea.status}`, code: 422 }
  }

  await prisma.idea.update({
    where: { id },
    data: {
      ...(parsed.data.title !== undefined ? { title: parsed.data.title } : {}),
      ...(parsed.data.description !== undefined ? { description: parsed.data.description } : {}),
      ...(parsed.data.product_id !== undefined ? { product_id: parsed.data.product_id } : {}),
    },
  })
  revalidatePath('/ideas')
  revalidatePath(`/ideas/${id}`)
  return { success: true }
}

export async function archiveIdeaAction(id: string): Promise<ActionResult> {
  return setArchived(id, true)
}

export async function unarchiveIdeaAction(id: string): Promise<ActionResult> {
  return setArchived(id, false)
}

async function setArchived(id: string, archived: boolean): Promise<ActionResult> {
  const session = await getSession()
  if (!session.userId) return { error: 'Niet ingelogd', code: 401 }
  if (session.isDemo) return { error: 'Niet beschikbaar in demo-modus', code: 403 }

  const found = await prisma.idea.findFirst({
    where: { id, user_id: session.userId },
    select: { id: true },
  })
  if (!found) return { error: 'Idee niet gevonden', code: 404 }

  await prisma.idea.update({ where: { id }, data: { archived } })
  revalidatePath('/ideas')
  revalidatePath(`/ideas/${id}`)
  return { success: true }
}

export async function deleteIdeaAction(id: string): Promise<ActionResult> {
  const session = await getSession()
  if (!session.userId) return { error: 'Niet ingelogd', code: 401 }
  if (session.isDemo) return { error: 'Niet beschikbaar in demo-modus', code: 403 }

  const idea = await prisma.idea.findFirst({
    where: { id, user_id: session.userId },
    select: { id: true, pbi_id: true },
  })
  if (!idea) return { error: 'Idee niet gevonden', code: 404 }
  if (idea.pbi_id !== null) {
    return {
      error: 'Verwijder eerst de gekoppelde PBI; daarna kun je het idee weggooien.',
      code: 422,
    }
  }

  await prisma.idea.delete({ where: { id } })
  revalidatePath('/ideas')
  return { success: true }
}

// ---------------------------------------------------------------------------
// Secondary products

const secondaryProductsSchema = z.object({
  ideaId: z.string().cuid(),
  productIds: z.array(z.string().cuid()).max(10),
})

export async function updateSecondaryProductsAction(
  ideaId: string,
  productIds: string[],
): Promise<ActionResult> {
  const session = await getSession()
  if (!session.userId) return { error: 'Niet ingelogd', code: 401 }
  if (session.isDemo) return { error: 'Niet beschikbaar in demo-modus', code: 403 }

  const parsed = secondaryProductsSchema.safeParse({ ideaId, productIds })
  if (!parsed.success) return { error: 'Ongeldige invoer', code: 422 }

  const idea = await prisma.idea.findFirst({
    where: { id: parsed.data.ideaId, user_id: session.userId },
    select: { id: true, product_id: true },
  })
  if (!idea) return { error: 'Idee niet gevonden', code: 404 }

  // Verwijder primair product uit de lijst (mag niet dubbel)
  const filtered = parsed.data.productIds.filter((pid) => pid !== idea.product_id)

  // Valideer dat alle gevraagde producten toegankelijk zijn voor de user
  if (filtered.length > 0) {
    const { productAccessFilter } = await import('@/lib/product-access')
    const accessible = await prisma.product.findMany({
      where: { id: { in: filtered }, ...productAccessFilter(session.userId) },
      select: { id: true },
    })
    if (accessible.length !== filtered.length)
      return { error: 'Een of meer producten zijn niet toegankelijk', code: 403 }
  }

  // Atomisch: verwijder alle bestaande, voeg nieuwe in
  await prisma.$transaction([
    prisma.ideaProduct.deleteMany({ where: { idea_id: idea.id } }),
    ...(filtered.length > 0
      ? [
          prisma.ideaProduct.createMany({
            data: filtered.map((pid) => ({ idea_id: idea.id, product_id: pid })),
            skipDuplicates: true,
          }),
        ]
      : []),
  ])

  revalidatePath('/ideas/' + idea.id, 'page')
  revalidatePath('/ideas', 'page')
  return { success: true }
}

// ---------------------------------------------------------------------------
// Markdown-edits (grill_md & plan_md handmatig fine-tunen)

export async function updateGrillMdAction(
  id: string,
  markdown: string,
): Promise<ActionResult> {
  const session = await getSession()
  if (!session.userId) return { error: 'Niet ingelogd', code: 401 }
  if (session.isDemo) return { error: 'Niet beschikbaar in demo-modus', code: 403 }

  const limited = enforceUserRateLimit('edit-idea-md', session.userId)
  if (limited) return limited

  const idea = await loadOwnedIdea(id, session.userId, ['status'])
  if (!idea) return { error: 'Idee niet gevonden', code: 404 }
  if (!isGrillMdEditable(idea.status)) {
    return {
      error: `grill_md alleen bewerkbaar in GRILLED of PLAN_READY (huidige status: ${idea.status})`,
      code: 422,
    }
  }

  await prisma.$transaction([
    prisma.idea.update({ where: { id }, data: { grill_md: markdown } }),
    prisma.ideaLog.create({
      data: {
        idea_id: id,
        type: 'NOTE',
        content: 'User-edited grill_md',
        metadata: { length: markdown.length },
      },
    }),
  ])

  revalidatePath(`/ideas/${id}`)
  return { success: true }
}

export async function updatePlanMdAction(
  id: string,
  markdown: string,
): Promise<ActionResult> {
  const session = await getSession()
  if (!session.userId) return { error: 'Niet ingelogd', code: 401 }
  if (session.isDemo) return { error: 'Niet beschikbaar in demo-modus', code: 403 }

  const limited = enforceUserRateLimit('edit-idea-md', session.userId)
  if (limited) return limited

  const idea = await loadOwnedIdea(id, session.userId, ['status'])
  if (!idea) return { error: 'Idee niet gevonden', code: 404 }
  if (!isPlanMdEditable(idea.status)) {
    return {
      error: `plan_md alleen bewerkbaar in PLAN_READY (huidige status: ${idea.status})`,
      code: 422,
    }
  }

  // Validate frontmatter — voorkomt dat een onparseerbaar plan in de DB belandt
  // en bij Materialiseer pas faalt.
  const parsed = parsePlanMd(markdown)
  if (!parsed.ok) {
    return {
      error: 'plan_md is niet parseerbaar',
      code: 422,
      details: parsed.errors,
    }
  }

  await prisma.$transaction([
    prisma.idea.update({ where: { id }, data: { plan_md: markdown } }),
    prisma.ideaLog.create({
      data: {
        idea_id: id,
        type: 'NOTE',
        content: 'User-edited plan_md',
        metadata: { length: markdown.length },
      },
    }),
  ])

  revalidatePath(`/ideas/${id}`)
  return { success: true }
}

// ---------------------------------------------------------------------------
// Upload — gebruiker plakt/uploadt zelf een plan.md in plaats van de Make-Plan
// AI-flow. Skipt grill als gewenst. Status springt direct naar PLAN_READY.
// Bij parse-failure: NIET opslaan (return 422), zodat een onparseerbaar plan
// nooit in de DB belandt. Geen worker nodig — synchrone parser.

const UPLOAD_PLAN_FROM: IdeaStatus[] = ['DRAFT', 'GRILLED', 'PLAN_FAILED', 'PLAN_READY']
const MAX_PLAN_MD_LENGTH = 100_000

export async function uploadPlanMdAction(
  id: string,
  markdown: string,
): Promise<ActionResult> {
  const session = await getSession()
  if (!session.userId) return { error: 'Niet ingelogd', code: 401 }
  if (session.isDemo) return { error: 'Niet beschikbaar in demo-modus', code: 403 }

  const limited = enforceUserRateLimit('upload-idea-plan', session.userId)
  if (limited) return limited

  if (typeof markdown !== 'string' || markdown.trim().length === 0) {
    return { error: 'plan_md is leeg', code: 422 }
  }
  if (markdown.length > MAX_PLAN_MD_LENGTH) {
    return {
      error: `plan_md is te groot (${markdown.length} > ${MAX_PLAN_MD_LENGTH} chars)`,
      code: 422,
    }
  }

  const idea = await loadOwnedIdea(id, session.userId, ['status'])
  if (!idea) return { error: 'Idee niet gevonden', code: 404 }
  if (!UPLOAD_PLAN_FROM.includes(idea.status)) {
    return {
      error: `Upload plan alleen toegestaan vanuit ${UPLOAD_PLAN_FROM.join('/')} (huidige status: ${idea.status})`,
      code: 422,
    }
  }

  const parsed = parsePlanMd(markdown)
  if (!parsed.ok) {
    return {
      error: 'plan_md is niet parseerbaar',
      code: 422,
      details: parsed.errors,
    }
  }

  await prisma.$transaction([
    prisma.idea.update({
      where: { id },
      data: { plan_md: markdown, status: 'PLAN_READY' },
    }),
    prisma.ideaLog.create({
      data: {
        idea_id: id,
        type: 'NOTE',
        content: 'User-uploaded plan_md',
        metadata: { length: markdown.length, from_status: idea.status },
      },
    }),
  ])

  revalidatePath(`/ideas/${id}`)
  return { success: true }
}

// ---------------------------------------------------------------------------
// Download — geeft de raw markdown terug; UI bouwt een Blob.

export async function downloadIdeaMdAction(
  id: string,
  kind: 'grill' | 'plan',
): Promise<ActionResult<{ filename: string; markdown: string }>> {
  const session = await getSession()
  if (!session.userId) return { error: 'Niet ingelogd', code: 401 }
  // Demo MAG downloaden — read-only operatie, geen mutatie.

  const idea = await loadOwnedIdea(id, session.userId, ['code', 'grill_md', 'plan_md'])
  if (!idea) return { error: 'Idee niet gevonden', code: 404 }

  const md = kind === 'grill' ? idea.grill_md : idea.plan_md
  if (!md) {
    return { error: `Geen ${kind}_md beschikbaar voor dit idee`, code: 404 }
  }

  return {
    success: true,
    data: { filename: `${idea.code}-${kind}.md`, markdown: md },
  }
}

// ---------------------------------------------------------------------------
// Job-triggers (Grill Me / Make Plan / Cancel)

const GRILL_TRIGGERABLE_FROM: IdeaStatus[] = ['DRAFT', 'GRILLED', 'GRILL_FAILED', 'PLAN_READY', 'PLANNED']
const MAKE_PLAN_TRIGGERABLE_FROM: IdeaStatus[] = ['GRILLED', 'PLAN_FAILED', 'PLAN_READY']
const REVIEW_PLAN_TRIGGERABLE_FROM: IdeaStatus[] = ['PLAN_READY', 'PLAN_REVIEWED']

export async function startGrillJobAction(id: string): Promise<ActionResult<{ job_id: string }>> {
  return startIdeaJob(id, 'IDEA_GRILL', 'GRILLING', GRILL_TRIGGERABLE_FROM)
}

export async function startMakePlanJobAction(id: string): Promise<ActionResult<{ job_id: string }>> {
  return startIdeaJob(id, 'IDEA_MAKE_PLAN', 'PLANNING', MAKE_PLAN_TRIGGERABLE_FROM)
}

export async function startReviewPlanJobAction(id: string): Promise<ActionResult<{ job_id: string }>> {
  return startIdeaJob(id, 'IDEA_REVIEW_PLAN', 'REVIEWING_PLAN', REVIEW_PLAN_TRIGGERABLE_FROM)
}

async function startIdeaJob(
  id: string,
  kind: ClaudeJobKind,
  newStatus: IdeaStatus,
  allowedFrom: IdeaStatus[],
): Promise<ActionResult<{ job_id: string }>> {
  const session = await getSession()
  if (!session.userId) return { error: 'Niet ingelogd', code: 401 }
  if (session.isDemo) return { error: 'Niet beschikbaar in demo-modus', code: 403 }

  const limited = enforceUserRateLimit('start-idea-job', session.userId)
  if (limited) return limited

  // Laad idee + product (voor repo_url-validatie)
  const idea = await prisma.idea.findFirst({
    where: { id, user_id: session.userId },
    select: {
      id: true,
      status: true,
      product_id: true,
      product: { select: { id: true, repo_url: true } },
    },
  })
  if (!idea) return { error: 'Idee niet gevonden', code: 404 }
  if (!allowedFrom.includes(idea.status)) {
    return {
      error: `Actie niet toegestaan in status ${idea.status}`,
      code: 422,
    }
  }
  if (!canTransition(idea.status, newStatus)) {
    return { error: `Status-transitie ${idea.status}→${newStatus} ongeldig`, code: 422 }
  }

  // Product-met-repo verplicht (M12 grill-keuze 3)
  if (!idea.product_id || !idea.product?.repo_url) {
    return {
      error: 'Idee moet gekoppeld zijn aan een product met repo_url voordat je dit kunt starten.',
      code: 422,
    }
  }

  // Idempotency: weiger als er al een actieve job loopt voor dit idee.
  const existing = await prisma.claudeJob.findFirst({
    where: { idea_id: id, status: { in: ACTIVE_JOB_STATUSES } },
    select: { id: true },
  })
  if (existing) {
    return {
      error: 'Er loopt al een actieve agent voor dit idee.',
      code: 409,
      details: { job_id: existing.id },
    }
  }

  // Worker-presence — server-side check, naast UI-side disabled-rule.
  const workers = await countActiveWorkers(session.userId)
  if (workers === 0) {
    return {
      error: 'Geen Claude-worker actief. Start een lokale wait_for_job-loop en probeer opnieuw.',
      code: 422,
    }
  }

  const ideaSnapshot = await getJobConfigSnapshot({ kind, productId: idea.product_id! })

  // Atomic: create job + flip idea-status + log.
  const job = await prisma.$transaction(async (tx) => {
    const j = await tx.claudeJob.create({
      data: {
        user_id: session.userId,
        product_id: idea.product_id!,
        idea_id: id,
        kind,
        status: 'QUEUED',
        ...ideaSnapshot,
      },
      select: { id: true },
    })
    await tx.idea.update({ where: { id }, data: { status: newStatus } })
    await tx.ideaLog.create({
      data: {
        idea_id: id,
        type: 'JOB_EVENT',
        content: `${kind} queued`,
        metadata: { job_id: j.id, kind },
      },
    })
    return j
  })

  // Manual pg_notify zoals enqueueClaudeJobAction in actions/claude-jobs.ts.
  await prisma.$executeRaw`
    SELECT pg_notify('scrum4me_changes', ${JSON.stringify({
      type: 'claude_job_enqueued',
      job_id: job.id,
      idea_id: id,
      user_id: session.userId,
      product_id: idea.product_id,
      kind,
      status: 'queued',
    })}::text)
  `

  revalidatePath('/ideas')
  revalidatePath(`/ideas/${id}`)
  return { success: true, data: { job_id: job.id } }
}

export async function cancelIdeaJobAction(id: string): Promise<ActionResult> {
  const session = await getSession()
  if (!session.userId) return { error: 'Niet ingelogd', code: 401 }
  if (session.isDemo) return { error: 'Niet beschikbaar in demo-modus', code: 403 }

  const idea = await prisma.idea.findFirst({
    where: { id, user_id: session.userId },
    select: { id: true, status: true, grill_md: true, plan_md: true },
  })
  if (!idea) return { error: 'Idee niet gevonden', code: 404 }

  // Vind de actieve job — meest recente in QUEUED|CLAIMED|RUNNING.
  const job = await prisma.claudeJob.findFirst({
    where: { idea_id: id, status: { in: ACTIVE_JOB_STATUSES } },
    orderBy: { created_at: 'desc' },
    select: { id: true, kind: true },
  })
  if (!job) return { error: 'Geen actieve job om te annuleren', code: 404 }

  // Bepaal terugval-status. Bij een lopende grill: terug naar GRILLED als er
  // al eerder grill_md was, anders DRAFT. Bij plan-job: PLAN_READY als er al
  // plan_md was (re-plan-cancel), anders GRILLED. Bij review-plan: terug naar
  // PLAN_READY (review kan altijd opnieuw gestart worden).
  let revertStatus: IdeaStatus
  if (job.kind === 'IDEA_GRILL') {
    revertStatus = idea.grill_md ? 'GRILLED' : 'DRAFT'
  } else if (job.kind === 'IDEA_MAKE_PLAN') {
    revertStatus = idea.plan_md ? 'PLAN_READY' : 'GRILLED'
  } else if (job.kind === 'IDEA_REVIEW_PLAN') {
    revertStatus = 'PLAN_READY'
  } else {
    return { error: `Job kind ${job.kind} hoort niet bij een idee`, code: 422 }
  }

  await prisma.$transaction([
    prisma.claudeJob.update({
      where: { id: job.id },
      data: { status: 'CANCELLED', finished_at: new Date(), error: 'user_cancelled' },
    }),
    prisma.idea.update({ where: { id }, data: { status: revertStatus } }),
    prisma.ideaLog.create({
      data: {
        idea_id: id,
        type: 'JOB_EVENT',
        content: `${job.kind} cancelled by user`,
        metadata: { job_id: job.id, revert_status: revertStatus },
      },
    }),
  ])

  await prisma.$executeRaw`
    SELECT pg_notify('scrum4me_changes', ${JSON.stringify({
      type: 'claude_job_status',
      job_id: job.id,
      idea_id: id,
      user_id: session.userId,
      kind: job.kind,
      status: 'cancelled',
    })}::text)
  `

  revalidatePath('/ideas')
  revalidatePath(`/ideas/${id}`)
  return { success: true }
}

// ---------------------------------------------------------------------------
// Materialize: parse plan_md → INSERT PBI + stories + taken (atomic)

const PBI_AUTO_RE = /^PBI-(\d+)$/
const STORY_AUTO_RE = /^ST-(\d+)$/
const TASK_AUTO_RE = /^T-(\d+)$/

function nextNumber(existing: (string | null)[], re: RegExp): number {
  let max = 0
  for (const c of existing) {
    if (!c) continue
    const m = c.match(re)
    if (m) {
      const n = Number.parseInt(m[1], 10)
      if (!Number.isNaN(n) && n > max) max = n
    }
  }
  return max + 1
}

export async function materializeIdeaPlanAction(
  id: string,
  options?: { allowAlongside?: boolean },
): Promise<ActionResult<{ pbi_id: string; pbi_code: string; story_ids: string[]; task_ids: string[] }>> {
  const session = await getSession()
  if (!session.userId) return { error: 'Niet ingelogd', code: 401 }
  if (session.isDemo) return { error: 'Niet beschikbaar in demo-modus', code: 403 }

  const limited = enforceUserRateLimit('materialize-idea', session.userId)
  if (limited) return limited

  const idea = await prisma.idea.findFirst({
    where: { id, user_id: session.userId },
    select: { id: true, status: true, product_id: true, plan_md: true, pbi_id: true },
  })
  if (!idea) return { error: 'Idee niet gevonden', code: 404 }
  if (idea.status !== 'PLAN_READY') {
    return {
      error: `Materialiseren alleen toegestaan in PLAN_READY (huidige status: ${idea.status})`,
      code: 422,
    }
  }
  if (!idea.product_id) {
    return { error: 'Idee mist een gekoppeld product', code: 422 }
  }
  if (!idea.plan_md) {
    return { error: 'Idee heeft geen plan_md', code: 422 }
  }

  const parsed = parsePlanMd(idea.plan_md)
  if (!parsed.ok) {
    return { error: 'plan_md is niet parseerbaar', code: 422, details: parsed.errors }
  }

  const productId = idea.product_id
  const plan = parsed.plan

  let oldPbiId: string | null = null
  if (idea.pbi_id) {
    const executedCount = await prisma.task.count({
      where: {
        story: { pbi_id: idea.pbi_id },
        status: { in: ['DONE', 'IN_PROGRESS'] },
      },
    })
    if (executedCount > 0 && !options?.allowAlongside) {
      const existingPbi = await prisma.pbi.findUnique({
        where: { id: idea.pbi_id },
        select: { code: true },
      })
      return {
        error: `PBI_HAS_ACTIVE_TASKS:${existingPbi?.code ?? idea.pbi_id}`,
        code: 409,
      }
    }
    if (executedCount === 0) {
      oldPbiId = idea.pbi_id
    }
    // executedCount > 0 && allowAlongside: doorgaan zonder delete
  }

  try {
    const result = await prisma.$transaction(async (tx) => {
      if (oldPbiId) {
        await tx.pbi.delete({ where: { id: oldPbiId } })
      }

      // Codes: één keer SELECT max per type binnen de transactie. Bij P2002
      // (race met andere materialize) abort de transactie en gooien we 409.
      const [existingPbis, existingStories, existingTasks] = await Promise.all([
        tx.pbi.findMany({ where: { product_id: productId }, select: { code: true } }),
        tx.story.findMany({ where: { product_id: productId }, select: { code: true } }),
        tx.task.findMany({ where: { product_id: productId }, select: { code: true } }),
      ])
      let nextPbiN = nextNumber(existingPbis.map((p) => p.code), PBI_AUTO_RE)
      let nextStoryN = nextNumber(existingStories.map((s) => s.code), STORY_AUTO_RE)
      let nextTaskN = nextNumber(existingTasks.map((t) => t.code), TASK_AUTO_RE)

      // sort_order: vraag de huidige max binnen het product op (per priority)
      const lastPbi = await tx.pbi.findFirst({
        where: { product_id: productId, priority: plan.pbi.priority },
        orderBy: { sort_order: 'desc' },
        select: { sort_order: true },
      })
      const pbiSortOrder = (lastPbi?.sort_order ?? 0) + 1.0

      const pbi = await tx.pbi.create({
        data: {
          product_id: productId,
          code: `PBI-${nextPbiN++}`,
          title: plan.pbi.title,
          description: plan.pbi.description ?? null,
          priority: plan.pbi.priority,
          sort_order: pbiSortOrder,
        },
        select: { id: true, code: true },
      })

      const storyIds: string[] = []
      const taskIds: string[] = []

      for (let si = 0; si < plan.stories.length; si++) {
        const s = plan.stories[si]
        const storyCode = `ST-${String(nextStoryN++).padStart(3, '0')}`
        const story = await tx.story.create({
          data: {
            pbi_id: pbi.id,
            product_id: productId,
            code: storyCode,
            title: s.title,
            description: s.description ?? null,
            acceptance_criteria: s.acceptance_criteria ?? null,
            priority: s.priority,
            sort_order: parseCodeNumber(storyCode),
            status: 'OPEN',
          },
          select: { id: true },
        })
        storyIds.push(story.id)

        for (let ti = 0; ti < s.tasks.length; ti++) {
          const t = s.tasks[ti]
          const taskCode = `T-${nextTaskN++}`
          const task = await tx.task.create({
            data: {
              story_id: story.id,
              product_id: productId,
              code: taskCode,
              title: t.title,
              description: t.description ?? null,
              implementation_plan: t.implementation_plan ?? null,
              // Erf priority van de story zodat YAML-volgorde gerespecteerd
              // blijft. Worker sorteert op `priority ASC, sort_order ASC`;
              // gemixte task-priorities binnen één story zouden anders de
              // YAML-volgorde verstoren (zie plan-fix task-volgorde-na-upload).
              priority: s.priority,
              sort_order: parseCodeNumber(taskCode),
              status: 'TO_DO',
              verify_required: t.verify_required ?? 'ALIGNED_OR_PARTIAL',
              verify_only: t.verify_only ?? false,
            },
            select: { id: true },
          })
          taskIds.push(task.id)
        }
      }

      // Link idea → PBI + status PLANNED
      await tx.idea.update({
        where: { id },
        data: { pbi_id: pbi.id, status: 'PLANNED' },
      })

      // Audit log
      await tx.ideaLog.create({
        data: {
          idea_id: id,
          type: 'PLAN_RESULT',
          content: `Materialized into ${pbi.code} (${plan.stories.length} stories, ${taskIds.length} tasks)`,
          metadata: {
            pbi_id: pbi.id,
            pbi_code: pbi.code,
            story_count: storyIds.length,
            task_count: taskIds.length,
          },
        },
      })

      return { pbi_id: pbi.id, pbi_code: pbi.code, story_ids: storyIds, task_ids: taskIds }
    })

    revalidatePath(`/ideas/${id}`)
    revalidatePath(`/products/${productId}/backlog`)
    return { success: true, data: result }
  } catch (err) {
    // P2002 op code = race met andere materialize. Andere fouten = bug.
    const msg = err instanceof Error ? err.message : String(err)
    if (msg.includes('P2002') || msg.includes('Unique constraint')) {
      return {
        error: 'Code-conflict tijdens materialiseren (race). Probeer opnieuw.',
        code: 409,
      }
    }
    throw err
  }
}

// ---------------------------------------------------------------------------
// Re-link: een idee in PLANNED waarvan de PBI handmatig is verwijderd
// (Pbi.id → null door de SetNull-FK). Gebruiker klikt expliciet "Re-link plan"
// om terug naar PLAN_READY te gaan en eventueel opnieuw te materialiseren.

export async function relinkIdeaPlanAction(id: string): Promise<ActionResult> {
  const session = await getSession()
  if (!session.userId) return { error: 'Niet ingelogd', code: 401 }
  if (session.isDemo) return { error: 'Niet beschikbaar in demo-modus', code: 403 }

  const idea = await prisma.idea.findFirst({
    where: { id, user_id: session.userId },
    select: { id: true, status: true, pbi_id: true },
  })
  if (!idea) return { error: 'Idee niet gevonden', code: 404 }
  if (idea.status !== 'PLANNED' || idea.pbi_id !== null) {
    return {
      error: 'Re-link kan alleen wanneer status=PLANNED én PBI is verwijderd',
      code: 422,
    }
  }

  await prisma.$transaction([
    prisma.idea.update({ where: { id }, data: { status: 'PLAN_READY' } }),
    prisma.ideaLog.create({
      data: {
        idea_id: id,
        type: 'NOTE',
        content: 'PBI was deleted; relinked to PLAN_READY',
      },
    }),
  ])

  revalidatePath(`/ideas/${id}`)
  return { success: true }
}

// ---------------------------------------------------------------------------
// Helpers

type IdeaSelect = Array<keyof Idea>

async function loadOwnedIdea<S extends IdeaSelect>(
  id: string,
  userId: string,
  fields: S,
): Promise<Pick<Idea, S[number]> | null> {
  const select = Object.fromEntries(fields.map((f) => [f, true])) as {
    [K in S[number]]: true
  }
  return prisma.idea.findFirst({
    where: { id, user_id: userId },
    select,
  }) as Promise<Pick<Idea, S[number]> | null>
}
