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

import { Router } from "express";
import { db, clientsTable, projectsTable } from "@workspace/db";
import { eq, sql, and } from "drizzle-orm";
import {
  CreateClientBody,
  UpdateClientBody,
  GetClientParams,
  UpdateClientParams,
  DeleteClientParams,
} from "@workspace/api-zod";

const router = Router();

router.get("/clients", async (req, res) => {
  const clients = await db.select().from(clientsTable).where(eq(clientsTable.userId, req.user!.id)).orderBy(clientsTable.createdAt);

  const withCounts = await Promise.all(
    clients.map(async (c) => {
      const [{ count }] = await db
        .select({ count: sql<number>`cast(count(*) as integer)` })
        .from(projectsTable)
        .where(eq(projectsTable.clientId, c.id));
      return { ...c, projectCount: count };
    })
  );

  res.json(withCounts);
});

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

  const [client] = await db.insert(clientsTable).values({ ...body.data, userId: req.user!.id }).returning();
  res.status(201).json({ ...client, projectCount: 0 });
});

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

  const [client] = await db.select().from(clientsTable).where(and(eq(clientsTable.id, params.data.id), eq(clientsTable.userId, req.user!.id)));
  if (!client) {
    res.status(404).json({ error: "Client not found" });
    return;
  }

  const [{ count }] = await db
    .select({ count: sql<number>`cast(count(*) as integer)` })
    .from(projectsTable)
    .where(eq(projectsTable.clientId, client.id));

  res.json({ ...client, projectCount: count });
});

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

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

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

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

  const [{ count }] = await db
    .select({ count: sql<number>`cast(count(*) as integer)` })
    .from(projectsTable)
    .where(eq(projectsTable.clientId, client.id));

  res.json({ ...client, projectCount: count });
});

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

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

export default router;