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

import { Router } from "express";
import { db, projectsTable, clientsTable, timeEntriesTable } from "@workspace/db";
import { eq, sql, and } from "drizzle-orm";
import {
  CreateProjectBody,
  UpdateProjectBody,
  ListProjectsQueryParams,
  GetProjectParams,
  UpdateProjectParams,
  DeleteProjectParams,
} from "@workspace/api-zod";

const router = Router();

router.get("/projects", async (req, res) => {
  const query = ListProjectsQueryParams.safeParse(req.query);
  if (!query.success) {
    res.status(400).json({ error: query.error.message });
    return;
  }

  const { status, clientId } = query.data;

  const rows = 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,
      driveUrl: projectsTable.driveUrl,
      planUrl: projectsTable.planUrl,
      createdAt: projectsTable.createdAt,
      updatedAt: projectsTable.updatedAt,
    })
    .from(projectsTable)
    .leftJoin(clientsTable, eq(projectsTable.clientId, clientsTable.id))
    .where(
      and(
        eq(projectsTable.userId, req.user!.id),
        status && clientId
          ? sql`${projectsTable.status} = ${status} AND ${projectsTable.clientId} = ${clientId}`
          : status
          ? eq(projectsTable.status, status)
          : clientId
          ? eq(projectsTable.clientId, clientId)
          : undefined
      )
    )
    .orderBy(projectsTable.createdAt);

  const withHours = await Promise.all(
    rows.map(async (p) => {
      const [{ total }] = await db
        .select({ total: sql<number>`coalesce(sum(${timeEntriesTable.hours}), 0)` })
        .from(timeEntriesTable)
        .where(eq(timeEntriesTable.projectId, p.id));
      return { ...p, totalHours: total };
    })
  );

  res.json(withHours);
});

router.post("/projects", async (req, res) => {
  const body = CreateProjectBody.safeParse(req.body);
  if (!body.success) {
    res.status(400).json({ error: body.error.message });
    return;
  }

  const [project] = await db.insert(projectsTable).values({ ...body.data, userId: req.user!.id }).returning();

  let clientName: string | null = null;
  if (project.clientId) {
    const [client] = await db.select().from(clientsTable).where(eq(clientsTable.id, project.clientId));
    clientName = client?.name ?? null;
  }

  res.status(201).json({ ...project, clientName, totalHours: 0 });
});

router.get("/projects/:id", async (req, res) => {
  const params = GetProjectParams.safeParse({ id: Number(req.params.id) });
  if (!params.success) {
    res.status(400).json({ error: params.error.message });
    return;
  }

  const [project] = 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,
      driveUrl: projectsTable.driveUrl,
      planUrl: projectsTable.planUrl,
      createdAt: projectsTable.createdAt,
      updatedAt: projectsTable.updatedAt,
    })
    .from(projectsTable)
    .leftJoin(clientsTable, eq(projectsTable.clientId, clientsTable.id))
    .where(and(eq(projectsTable.id, params.data.id), eq(projectsTable.userId, req.user!.id)));

  if (!project) {
    res.status(404).json({ error: "Project not found" });
    return;
  }

  const [{ total }] = await db
    .select({ total: sql<number>`coalesce(sum(${timeEntriesTable.hours}), 0)` })
    .from(timeEntriesTable)
    .where(eq(timeEntriesTable.projectId, project.id));

  res.json({ ...project, totalHours: total });
});

router.patch("/projects/:id", async (req, res) => {
  const params = UpdateProjectParams.safeParse({ id: Number(req.params.id) });
  if (!params.success) {
    res.status(400).json({ error: params.error.message });
    return;
  }

  const body = UpdateProjectBody.safeParse(req.body);
  if (!body.success) {
    res.status(400).json({ error: body.error.message });
    return;
  }

  const [project] = await db
    .update(projectsTable)
    .set(body.data)
    .where(and(eq(projectsTable.id, params.data.id), eq(projectsTable.userId, req.user!.id)))
    .returning();

  if (!project) {
    res.status(404).json({ error: "Project not found" });
    return;
  }

  let clientName: string | null = null;
  if (project.clientId) {
    const [client] = await db.select().from(clientsTable).where(eq(clientsTable.id, project.clientId));
    clientName = client?.name ?? null;
  }

  const [{ total }] = await db
    .select({ total: sql<number>`coalesce(sum(${timeEntriesTable.hours}), 0)` })
    .from(timeEntriesTable)
    .where(eq(timeEntriesTable.projectId, project.id));

  res.json({ ...project, clientName, totalHours: total });
});

router.delete("/projects/:id", async (req, res) => {
  const params = DeleteProjectParams.safeParse({ id: Number(req.params.id) });
  if (!params.success) {
    res.status(400).json({ error: params.error.message });
    return;
  }

  await db.delete(projectsTable).where(and(eq(projectsTable.id, params.data.id), eq(projectsTable.userId, req.user!.id)));
  res.status(204).send();
});

export default router;