feat: add CRUD endpoints for users by id

Add tables for role-based access control and academic entities.
Includes modules, UEs, notes, and adjustments.
Update students and mobility tables to reference new primary keys.
This enables richer data modeling for the application.

databases/schema.ts

@@ -1,28 +1,95 @@
 import {
   date,
+  doublePrecision,
   integer,
   pgTable,
+  primaryKey,
   serial,
   text,
 } from "npm:drizzle-orm/pg-core";
 
-export const promotions = pgTable("promotions", {
+export const roles = pgTable("roles", {
   id: serial("id").primaryKey(),
-  endyear: integer("endyear"),
-  current: integer("current"),
+  nom: text("nom").notNull(),
+});
+
+export const permissions = pgTable("permissions", {
+  id: text("id").primaryKey(),
+  nom: text("nom").notNull(),
+});
+
+export const rolePermissions = pgTable("role_permissions", {
+  idRole: integer("idRole").notNull().references(() => roles.id),
+  idPermission: text("idPermission").notNull().references(() => permissions.id),
+}, (t) => ({
+  pk: primaryKey({ columns: [t.idRole, t.idPermission] }),
+}));
+
+export const users = pgTable("users", {
+  id: text("id").primaryKey(),
+  nom: text("nom").notNull(),
+  prenom: text("prenom").notNull(),
+  idRole: integer("idRole").references(() => roles.id),
+});
+
+export const promotions = pgTable("promotions", {
+  id: text("idPromo").primaryKey(),
+  annee: text("annee"),
 });
 
 export const students = pgTable("students", {
-  userId: text("userId").primaryKey(),
-  firstName: text("firstName"),
-  lastName: text("lastName"),
-  mail: text("mail"),
-  promotionId: integer("promotionId").references(() => promotions.id),
+  numEtud: serial("numEtud").primaryKey(),
+  nom: text("nom").notNull(),
+  prenom: text("prenom").notNull(),
+  idPromo: text("idPromo").references(() => promotions.id),
 });
 
+export const modules = pgTable("modules", {
+  id: text("id").primaryKey(),
+  nom: text("nom").notNull(),
+});
+
+export const enseignements = pgTable("enseignements", {
+  idProf: text("idProf").notNull().references(() => users.id),
+  idModule: text("idModule").notNull().references(() => modules.id),
+  idPromo: text("idPromo").notNull().references(() => promotions.id),
+}, (t) => ({
+  pk: primaryKey({ columns: [t.idProf, t.idModule, t.idPromo] }),
+}));
+
+export const ues = pgTable("ues", {
+  id: serial("id").primaryKey(),
+  nom: text("nom").notNull(),
+});
+
+export const ueModules = pgTable("ue_modules", {
+  idModule: text("idModule").notNull().references(() => modules.id),
+  idUE: integer("idUE").notNull().references(() => ues.id),
+  idPromo: text("idPromo").notNull().references(() => promotions.id),
+  coeff: doublePrecision("coeff").notNull(),
+}, (t) => ({
+  pk: primaryKey({ columns: [t.idModule, t.idUE, t.idPromo] }),
+}));
+
+export const notes = pgTable("notes", {
+  numEtud: integer("numEtud").notNull().references(() => students.numEtud),
+  idModule: text("idModule").notNull().references(() => modules.id),
+  note: doublePrecision("note").notNull(),
+}, (t) => ({
+  pk: primaryKey({ columns: [t.numEtud, t.idModule] }),
+}));
+
+export const ajustements = pgTable("ajustements", {
+  numEtud: integer("numEtud").notNull().references(() => students.numEtud),
+  idUE: integer("idUE").notNull().references(() => ues.id),
+  valeur: doublePrecision("valeur").notNull(),
+}, (t) => ({
+  pk: primaryKey({ columns: [t.numEtud, t.idUE] }),
+}));
+
 export const mobility = pgTable("mobility", {
   id: serial("id").primaryKey(),
-  studentId: text("studentId").references(() => students.userId),
+  studentId: integer("studentId").references(() => students.numEtud),
   startDate: date("startDate"),
   endDate: date("endDate"),
   weeksCount: integer("weeksCount"),

routes/(apps)/admin/api/users/[id].ts

@@ -0,0 +1,66 @@
+import { FreshContext, Handlers } from "$fresh/server.ts";
+import { db } from "$root/databases/db.ts";
+import { users } from "$root/databases/schema.ts";
+import { AuthenticatedState } from "$root/defaults/interfaces.ts";
+import { eq } from "npm:drizzle-orm";
+
+const NOT_FOUND = new Response(
+  JSON.stringify({ error: "Ressource introuvable" }),
+  { status: 404, headers: { "content-type": "application/json" } },
+);
+
+export const handler: Handlers<null, AuthenticatedState> = {
+  // #62 GET /users/{id}
+  async GET(
+    _request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    const user = await db
+      .select()
+      .from(users)
+      .where(eq(users.id, context.params.id))
+      .then((rows) => rows[0] ?? null);
+
+    if (!user) return NOT_FOUND;
+
+    return new Response(JSON.stringify(user), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+
+  // #63 PUT /users/{id}
+  async PUT(
+    request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    const body: { nom: string; prenom: string; idRole: number } =
+      await request.json();
+
+    const [updated] = await db
+      .update(users)
+      .set({ nom: body.nom, prenom: body.prenom, idRole: body.idRole })
+      .where(eq(users.id, context.params.id))
+      .returning();
+
+    if (!updated) return NOT_FOUND;
+
+    return new Response(JSON.stringify(updated), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+
+  // #64 DELETE /users/{id}
+  async DELETE(
+    _request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    const [deleted] = await db
+      .delete(users)
+      .where(eq(users.id, context.params.id))
+      .returning();
+
+    if (!deleted) return NOT_FOUND;
+
+    return new Response(null, { status: 204 });
+  },
+};

routes/(apps)/notes/api/ues.ts

@@ -0,0 +1,19 @@
+import { Handlers } from "$fresh/server.ts";
+import { db } from "../../../../databases/db.ts";
+import { ues } from "../../../../databases/schema.ts";
+
+export const handler: Handlers = {
+  async GET() {
+    try {
+      const result = await db.select().from(ues);
+
+      return new Response(JSON.stringify(result), {
+        status: 200,
+        headers: { "Content-Type": "application/json" },
+      });
+    } catch (error) {
+      console.error("Error fetching UEs:", error);
+      return new Response("Failed to fetch data", { status: 500 });
+    }
+  },
+};