artifacts/api-server/src/routes/dashboard.ts

import { Router } from "express";
import { db, projectsTable, clientsTable, tasksTable, timeEntriesTable } from "@workspace/db";
import { eq, sql, gte, and, asc } from "drizzle-orm";
import { GetRecentActivityQueryParams } from "@workspace/api-zod";

const router = Router();

router.get("/dashboard/stats", async (req, res) => {
  const [{ activeProjects }] = await db
    .select({ activeProjects: sql<number>`cast(count(*) as integer)` })
    .from(projectsTable)
    .where(
      and(
        eq(projectsTable.userId, req.user!.id),
        sql`${projectsTable.status} NOT IN ('delivered', 'archived')`
      )
    );

  const [{ totalClients }] = await db
    .select({ totalClients: sql<number>`cast(count(*) as integer)` })
    .from(clientsTable)
    .where(eq(clientsTable.userId, req.user!.id));

  const [{ pendingTasks }] = await db
    .select({ pendingTasks: sql<number>`cast(count(*) as integer)` })
    .from(tasksTable)
    .where(and(eq(tasksTable.done, false), eq(tasksTable.userId, req.user!.id)));

  const now = new Date();
  const startOfMonth = new Date(now.getFullYear(), now.getMonth(), 1).toISOString();
  const [{ hoursThisMonth }] = await db
    .select({ hoursThisMonth: sql<number>`coalesce(sum(${timeEntriesTable.hours}), 0)` })
    .from(timeEntriesTable)
    .where(and(gte(timeEntriesTable.createdAt, new Date(startOfMonth)), eq(timeEntriesTable.userId, req.user!.id)));

  const projectsByStatusRaw = await db
    .select({
      status: projectsTable.status,
      count: sql<number>`cast(count(*) as integer)`,
    })
    .from(projectsTable)
    .where(eq(projectsTable.userId, req.user!.id))
    .groupBy(projectsTable.status);

  const statuses = ["briefing", "concept", "design", "review", "delivered", "archived"];
  const projectsByStatus = statuses.map((status) => ({
    status,
    count: projectsByStatusRaw.find((r) => r.status === status)?.count ?? 0,
  }));

  const deliveredProjects = await db
    .select({ budget: projectsTable.budget })
    .from(projectsTable)
    .where(
      and(
        eq(projectsTable.status, "delivered"),
        gte(projectsTable.updatedAt, new Date(startOfMonth)),
        eq(projectsTable.userId, req.user!.id)
      )
    );

  const revenueThisMonth = deliveredProjects.reduce((sum, p) => sum + (p.budget ?? 0), 0);

  res.json({
    activeProjects,
    totalClients,
    hoursThisMonth,
    pendingTasks,
    projectsByStatus,
    revenueThisMonth,
  });
});

router.get("/dashboard/activity", async (req, res) => {
  const query = GetRecentActivityQueryParams.safeParse(req.query);
  const limit = query.success ? (query.data.limit ?? 10) : 10;

  const recentProjects = await db
    .select({
      id: projectsTable.id,
      title: projectsTable.title,
      status: projectsTable.status,
      createdAt: projectsTable.createdAt,
      updatedAt: projectsTable.updatedAt,
    })
    .from(projectsTable)
    .where(eq(projectsTable.userId, req.user!.id))
    .orderBy(sql`${projectsTable.updatedAt} DESC`)
    .limit(5);

  const recentTasks = await db
    .select({
      id: tasksTable.id,
      title: tasksTable.title,
      done: tasksTable.done,
      createdAt: tasksTable.createdAt,
      updatedAt: tasksTable.updatedAt,
    })
    .from(tasksTable)
    .where(and(eq(tasksTable.done, true), eq(tasksTable.userId, req.user!.id)))
    .orderBy(sql`${tasksTable.updatedAt} DESC`)
    .limit(5);

  const recentClients = await db
    .select({ id: clientsTable.id, name: clientsTable.name, createdAt: clientsTable.createdAt })
    .from(clientsTable)
    .where(eq(clientsTable.userId, req.user!.id))
    .orderBy(sql`${clientsTable.createdAt} DESC`)
    .limit(3);

  const activities = [
    ...recentProjects.map((p) => ({
      id: `project-${p.id}-${p.updatedAt}`,
      type: "project_updated" as const,
      description: `Project "${p.title}" moved to ${p.status}`,
      entityId: p.id,
      createdAt: p.updatedAt.toISOString(),
    })),
    ...recentTasks.map((t) => ({
      id: `task-${t.id}-${t.updatedAt}`,
      type: "task_completed" as const,
      description: `Task completed: "${t.title}"`,
      entityId: t.id,
      createdAt: (t.updatedAt as Date).toISOString(),
    })),
    ...recentClients.map((c) => ({
      id: `client-${c.id}-${c.createdAt}`,
      type: "client_added" as const,
      description: `New client added: "${c.name}"`,
      entityId: c.id,
      createdAt: c.createdAt.toISOString(),
    })),
  ]
    .sort((a, b) => new Date(b.createdAt).getTime() - new Date(a.createdAt).getTime())
    .slice(0, limit);

  res.json(activities);
});

router.get("/dashboard/deadlines", async (req, res) => {
  const projects = await db
    .select({
      id: projectsTable.id,
      title: projectsTable.title,
      description: projectsTable.description,
      clientId: projectsTable.clientId,
      clientName: clientsTable.name,
      status: projectsTable.status,
      priority: projectsTable.priority,
      budget: projectsTable.budget,
      deadline: projectsTable.deadline,
      createdAt: projectsTable.createdAt,
      updatedAt: projectsTable.updatedAt,
    })
    .from(projectsTable)
    .leftJoin(clientsTable, eq(projectsTable.clientId, clientsTable.id))
    .where(
      and(
        sql`${projectsTable.deadline} IS NOT NULL`,
        sql`${projectsTable.status} NOT IN ('delivered', 'archived')`,
        eq(projectsTable.userId, req.user!.id)
      )
    )
    .orderBy(asc(projectsTable.deadline))
    .limit(10);

  res.json(projects.map((p) => ({ ...p, totalHours: null })));
});



export default router;