open-safetylog-ehs/server/routes/stats.ts

import { and, asc, count, desc, eq, gte, inArray, isNotNull, isNull, lt, ne, sql } from 'drizzle-orm'
import { Hono } from 'hono'
import type { OverviewStats } from '../../shared/api-types.ts'
import { incidentStatuses, type IncidentStatus } from '../../shared/enums.ts'
import { requireAuth } from '../auth/middleware.ts'
import { correctiveActions, incidents, sites } from '../db/schema.ts'
import { addDays, localDate } from '../lib/dates.ts'
import { computeAnalytics } from '../lib/analytics.ts'
import { currentUser, fail, validate, type AppEnv } from '../lib/http.ts'
import { analyticsQuery } from '../../shared/schemas.ts'
import { visibleTo } from '../lib/incidents.ts'

const DAY_MS = 86_400_000
const OPEN_ACTION = ['open', 'in_progress'] as const

/** Monday 00:00 UTC of the week containing `date` (matches Postgres `date_trunc('week', …)` in UTC). */
function weekStart(date: Date): Date {
  const start = new Date(Date.UTC(date.getUTCFullYear(), date.getUTCMonth(), date.getUTCDate()))
  start.setUTCDate(start.getUTCDate() - ((start.getUTCDay() + 6) % 7))
  return start
}

export const statsRoutes = new Hono<AppEnv>()
  .get('/overview', requireAuth, async (c) => {
    const me = currentUser(c)
    const db = c.get('db')
    const visible = visibleTo(db, me)
    const now = new Date()
    const today = localDate(now)

    const statusRows = await db.select({ status: incidents.status, n: count() }).from(incidents).where(visible).groupBy(incidents.status)
    const byStatus = Object.fromEntries(incidentStatuses.map((status) => [status, 0])) as Record<IncidentStatus, number>
    for (const row of statusRows) byStatus[row.status] = row.n
    const openIncidents = Object.entries(byStatus).reduce((sum, [status, n]) => (status === 'closed' ? sum : sum + n), 0)

    const [{ recent }] = await db
      .select({ recent: count() })
      .from(incidents)
      .where(and(visible, gte(incidents.createdAt, new Date(now.getTime() - 7 * DAY_MS))))

    const actionsBase = db.select({ n: count() }).from(correctiveActions).innerJoin(incidents, eq(incidents.id, correctiveActions.incidentId))
    const [{ n: dueThisWeek }] = await actionsBase.where(
      and(visible, inArray(correctiveActions.status, OPEN_ACTION), gte(correctiveActions.dueDate, today), sql`${correctiveActions.dueDate} <= ${addDays(today, 7)}`),
    )
    const [{ n: overdue }] = await db
      .select({ n: count() })
      .from(correctiveActions)
      .innerJoin(incidents, eq(incidents.id, correctiveActions.incidentId))
      .where(and(visible, inArray(correctiveActions.status, OPEN_ACTION), lt(correctiveActions.dueDate, today)))
    const progressRows = await db
      .select({ status: correctiveActions.status, n: count() })
      .from(correctiveActions)
      .innerJoin(incidents, eq(incidents.id, correctiveActions.incidentId))
      .where(and(visible, ne(incidents.status, 'closed'), ne(correctiveActions.status, 'cancelled')))
      .groupBy(correctiveActions.status)
    const actionProgress = {
      total: progressRows.reduce((sum, row) => sum + row.n, 0),
      completed: progressRows.filter((row) => row.status === 'done' || row.status === 'verified').reduce((sum, row) => sum + row.n, 0),
    }

    const [{ avgDays }] = await db
      .select({ avgDays: sql<string | null>`avg(extract(epoch from ${incidents.closedAt} - ${incidents.createdAt})) / 86400` })
      .from(incidents)
      .where(and(visible, isNotNull(incidents.closedAt), gte(incidents.closedAt, new Date(now.getTime() - 90 * DAY_MS))))

    const firstWeek = weekStart(new Date(now.getTime() - 11 * 7 * DAY_MS))
    const weekExpr = sql<string>`to_char(date_trunc('week', ${incidents.occurredAt} at time zone 'UTC'), 'YYYY-MM-DD')`
    const weekRows = await db
      .select({ week: weekExpr, n: count() })
      .from(incidents)
      .where(and(visible, gte(incidents.occurredAt, firstWeek)))
      .groupBy(weekExpr)
    const weeklyTrend = Array.from({ length: 12 }, (_, i) => {
      const start = new Date(firstWeek.getTime() + i * 7 * DAY_MS).toISOString().slice(0, 10)
      return { weekStart: start, count: weekRows.find((row) => row.week === start)?.n ?? 0 }
    })

    const yearStart = new Date(now.getFullYear(), 0, 1)
    const siteRows = await db
      .select({ siteId: sites.id, name: sites.name, n: count(incidents.id) })
      .from(sites)
      .leftJoin(incidents, and(eq(incidents.siteId, sites.id), gte(incidents.occurredAt, yearStart), visible))
      .where(and(eq(sites.organizationId, me.organizationId), isNull(sites.archivedAt)))
      .groupBy(sites.id, sites.name)
      .orderBy(desc(count(incidents.id)), asc(sites.name))

    const stats: OverviewStats = {
      openIncidents,
      reportedLast7Days: recent,
      actionsDueThisWeek: dueThisWeek,
      overdueActions: overdue,
      averageCloseDays: avgDays === null ? null : Math.round(Number(avgDays) * 10) / 10,
      byStatus,
      actionProgress,
      weeklyTrend,
      bySite: siteRows.map((row) => ({ siteId: row.siteId, name: row.name, count: row.n })),
    }
    return c.json(stats)
  })

  .get('/analytics', requireAuth, validate('query', analyticsQuery), async (c) => {
    const filters = c.req.valid('query')
    if (filters.from > filters.to) throw fail(400, 'The start date must be before the end date')
    const days = (Date.parse(filters.to) - Date.parse(filters.from)) / 86_400_000
    if (days > 3 * 366) throw fail(400, 'Choose a range of up to three years')
    return c.json(await computeAnalytics(c.get('db'), currentUser(c), filters))
  })