Scrum4Me/lib/insights/backlog-health.ts

import { prisma } from '@/lib/prisma'
import { productAccessFilter } from '@/lib/product-access'

export interface StuckTask {
  taskId: string
  title: string
  productId: string
  productName: string
  sprintGoal: string | null
  daysStuck: number
  updatedAt: string
}

export interface BacklogHealth {
  storiesWithoutAc: number
  tasksWithoutPlan: number
  stuckTasks: StuckTask[]
}

const SEVEN_DAYS_MS = 7 * 86_400_000

export async function getBacklogHealth(userId: string): Promise<BacklogHealth> {
  const now = new Date()
  const stuckCutoff = new Date(now.getTime() - SEVEN_DAYS_MS)

  const [storiesWithoutAc, tasksWithoutPlan, rawStuck] = await Promise.all([
    prisma.story.count({
      where: {
        OR: [{ acceptance_criteria: null }, { acceptance_criteria: '' }],
        product: productAccessFilter(userId),
      },
    }),
    prisma.task.count({
      where: {
        implementation_plan: null,
        story: { product: productAccessFilter(userId) },
      },
    }),
    prisma.task.findMany({
      where: {
        status: 'IN_PROGRESS',
        updated_at: { lt: stuckCutoff },
        story: { product: productAccessFilter(userId) },
      },
      orderBy: { updated_at: 'asc' },
      take: 10,
      include: {
        story: {
          include: {
            product: { select: { id: true, name: true } },
            sprint: { select: { sprint_goal: true } },
          },
        },
      },
    }),
  ])

  const stuckTasks: StuckTask[] = rawStuck.map(t => ({
    taskId: t.id,
    title: t.title,
    productId: t.story.product.id,
    productName: t.story.product.name,
    sprintGoal: t.story.sprint?.sprint_goal ?? null,
    daysStuck: Math.floor((now.getTime() - t.updated_at.getTime()) / 86_400_000),
    updatedAt: t.updated_at.toISOString(),
  }))

  return { storiesWithoutAc, tasksWithoutPlan, stuckTasks }
}