PMPR-46/47 : PUT et DELETE /notes/{numEtud}/{idModule}

- GET /ajustements/{numEtud}/{idUE}: get ajustement by student numEtud
  and UE id
- Requires employee role

databases/db.ts

@@ -1,5 +1,5 @@
-import { drizzle } from "npm:drizzle-orm/node-postgres";
-import pg from "npm:pg";
+import { drizzle } from "npm:drizzle-orm@0.45.2/node-postgres";
+import pg from "npm:pg@8.20.0";
 
 const { Pool } = pg;
 

databases/schema.ts

@@ -1,13 +1,37 @@
 import {
+  date,
   doublePrecision,
   integer,
   pgTable,
   primaryKey,
   serial,
   text,
-} from "npm:drizzle-orm/pg-core";
+} from "npm:drizzle-orm@0.45.2/pg-core";
+
+export const roles = pgTable("roles", {
+  id: serial("id").primaryKey(),
+  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),
+});
 
-// Ancien schéma conservé
 export const promotions = pgTable("promotions", {
   id: text("idPromo").primaryKey(),
   annee: text("annee"),
@@ -15,56 +39,20 @@ export const promotions = pgTable("promotions", {
 
 export const students = pgTable("students", {
   numEtud: serial("numEtud").primaryKey(),
-  nom: text("nom"),
-  prenom: text("prenom"),
+  nom: text("nom").notNull(),
+  prenom: text("prenom").notNull(),
   idPromo: text("idPromo").references(() => promotions.id),
 });
 
-export const mobility = pgTable("mobility", {
-  id: serial("id").primaryKey(),
-  studentId: text("studentId").references(() => students.numEtud),
-  startDate: text("startDate"),
-  endDate: text("endDate"),
-  weeksCount: integer("weeksCount"),
-  destinationCountry: text("destinationCountry"),
-  destinationName: text("destinationName"),
-  mobilityStatus: text("mobilityStatus").default("N/A"),
-});
-
-// Nouveau schéma
-export const roles = pgTable("roles", {
-  id: serial("id").primaryKey(),
-  nom: text("nom"),
-});
-
-export const permissions = pgTable("permissions", {
-  id: serial("id").primaryKey(),
-  nom: text("nom"),
-});
-
-export const rolePermissions = pgTable("role_permissions", {
-  idRole: integer("idRole").references(() => roles.id),
-  idPermission: integer("idPermission").references(() => permissions.id),
-}, (t) => ({
-  pk: primaryKey({ columns: [t.idRole, t.idPermission] }),
-}));
-
-export const users = pgTable("users", {
-  id: text("id").primaryKey(),
-  nom: text("nom"),
-  prenom: text("prenom"),
-  idRole: integer("idRole").references(() => roles.id),
-});
-
 export const modules = pgTable("modules", {
   id: text("id").primaryKey(),
-  nom: text("nom"),
+  nom: text("nom").notNull(),
 });
 
 export const enseignements = pgTable("enseignements", {
-  idProf: text("idProf").references(() => users.id),
-  idModule: text("idModule").references(() => modules.id),
-  idPromo: text("idPromo").references(() => promotions.id),
+  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] }),
 }));
@@ -75,26 +63,37 @@ export const ues = pgTable("ues", {
 });
 
 export const ueModules = pgTable("ue_modules", {
-  idModule: text("idModule").references(() => modules.id),
-  idUE: integer("idUE").references(() => ues.id),
-  idPromo: text("idPromo").references(() => promotions.id),
-  coeff: doublePrecision("coeff"),
+  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").references(() => students.numEtud),
-  idModule: text("idModule").references(() => modules.id),
-  note: doublePrecision("note"),
+  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").references(() => students.numEtud),
-  idUE: integer("idUE").references(() => ues.id),
-  valeur: doublePrecision("valeur"),
+  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: integer("studentId").references(() => students.numEtud),
+  startDate: date("startDate"),
+  endDate: date("endDate"),
+  weeksCount: integer("weeksCount"),
+  destinationCountry: text("destinationCountry"),
+  destinationName: text("destinationName"),
+  mobilityStatus: text("mobilityStatus").default("N/A"),
+});

drizzle.config.ts

@@ -1,4 +1,5 @@
 import { defineConfig } from "drizzle-kit";
+import process from "node:process";
 
 export default defineConfig({
   dialect: "postgresql",

routes/(apps)/admin/(_props)/props.ts

@@ -0,0 +1,13 @@
+import { AppProperties } from "$root/defaults/interfaces.ts";
+
+const properties: AppProperties = {
+  name: "Admin",
+  icon: "school",
+  pages: {
+    index: "Homepage",
+  },
+  adminOnly: [],
+  hint: "PolyMPR module",
+};
+
+export default properties;

routes/(apps)/admin/api/enseignements.ts

@@ -0,0 +1,70 @@
+import { FreshContext, Handlers } from "$fresh/server.ts";
+import { db } from "$root/databases/db.ts";
+import { enseignements } from "$root/databases/schema.ts";
+import { AuthenticatedState } from "$root/defaults/interfaces.ts";
+import { and, eq } from "npm:drizzle-orm@0.45.2";
+
+const NOT_FOUND = new Response(
+  JSON.stringify({ error: "Ressource introuvable" }),
+  { status: 404, headers: { "content-type": "application/json" } },
+);
+
+const FORBIDDEN = new Response(null, { status: 403 });
+
+const CONFLICT = new Response(
+  JSON.stringify({ error: "Cet enseignement existe déjà." }),
+  { status: 409, headers: { "content-type": "application/json" } },
+);
+
+export const handler: Handlers<null, AuthenticatedState> = {
+  // #29 POST /enseignements
+  async POST(
+    request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return FORBIDDEN;
+    }
+
+    const body: {
+      idProf: string;
+      idModule: string;
+      idPromo: string;
+    } = await request.json();
+
+    if (!body.idProf || !body.idModule || !body.idPromo) {
+      return new Response(null, { status: 400 });
+    }
+
+    // Check if enseignement already exists
+    const existing = await db
+      .select()
+      .from(enseignements)
+      .where(
+        and(
+          eq(enseignements.idProf, body.idProf),
+          eq(enseignements.idModule, body.idModule),
+          eq(enseignements.idPromo, body.idPromo),
+        ),
+      )
+      .then((rows) => rows[0] ?? null);
+
+    if (existing) {
+      return CONFLICT;
+    }
+
+    const [created] = await db
+      .insert(enseignements)
+      .values({
+        idProf: body.idProf,
+        idModule: body.idModule,
+        idPromo: body.idPromo,
+      })
+      .returning();
+
+    return new Response(JSON.stringify(created), {
+      status: 201,
+      headers: { "content-type": "application/json" },
+    });
+  },
+};

routes/(apps)/admin/api/enseignements/[idProf]/[idModule]/[idPromo].ts

@@ -0,0 +1,75 @@
+import { FreshContext, Handlers } from "$fresh/server.ts";
+import { db } from "$root/databases/db.ts";
+import { enseignements } from "$root/databases/schema.ts";
+import { AuthenticatedState } from "$root/defaults/interfaces.ts";
+import { and, eq } from "npm:drizzle-orm@0.45.2";
+
+const NOT_FOUND = new Response(
+  JSON.stringify({ error: "Ressource introuvable" }),
+  { status: 404, headers: { "content-type": "application/json" } },
+);
+
+const FORBIDDEN = new Response(null, { status: 403 });
+
+export const handler: Handlers<null, AuthenticatedState> = {
+  // #30 GET /enseignements/{idProf}/{idModule}/{idPromo}
+  async GET(
+    _request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return FORBIDDEN;
+    }
+
+    const idProf = context.params.idProf;
+    const idModule = context.params.idModule;
+    const idPromo = context.params.idPromo;
+
+    const enseignement = await db
+      .select()
+      .from(enseignements)
+      .where(
+        and(
+          eq(enseignements.idProf, idProf),
+          eq(enseignements.idModule, idModule),
+          eq(enseignements.idPromo, idPromo),
+        ),
+      )
+      .then((rows) => rows[0] ?? null);
+
+    if (!enseignement) return NOT_FOUND;
+
+    return new Response(JSON.stringify(enseignement), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+
+  // #31 DELETE /enseignements/{idProf}/{idModule}/{idPromo}
+  async DELETE(
+    _request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return FORBIDDEN;
+    }
+
+    const idProf = context.params.idProf;
+    const idModule = context.params.idModule;
+    const idPromo = context.params.idPromo;
+
+    const [deleted] = await db
+      .delete(enseignements)
+      .where(
+        and(
+          eq(enseignements.idProf, idProf),
+          eq(enseignements.idModule, idModule),
+          eq(enseignements.idPromo, idPromo),
+        ),
+      )
+      .returning();
+
+    if (!deleted) return NOT_FOUND;
+
+    return new Response(null, { status: 204 });
+  },
+};

routes/(apps)/admin/api/example.ts

@@ -0,0 +1,22 @@
+import { Handlers } from "$fresh/server.ts";
+
+export const handler: Handlers = {
+  async POST(request, context) {
+    if (request.headers.get("content-type") != "application/json") {
+      return new Response(null, {
+        status: 400,
+      });
+    }
+
+    const responseBody = {
+      requestBody: await request.json(),
+      context,
+    };
+
+    return new Response(JSON.stringify(responseBody), {
+      headers: {
+        "content-type": "application/json",
+      },
+    });
+  },
+};

routes/(apps)/admin/api/modules.ts

@@ -0,0 +1,63 @@
+import { FreshContext, Handlers } from "$fresh/server.ts";
+import { db } from "$root/databases/db.ts";
+import { modules } from "$root/databases/schema.ts";
+import { AuthenticatedState } from "$root/defaults/interfaces.ts";
+import { eq } from "npm:drizzle-orm";
+
+export const handler: Handlers<null, AuthenticatedState> = {
+  // #23 GET /modules
+  async GET(
+    _request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return new Response(JSON.stringify([]), {
+        headers: { "content-type": "application/json" },
+      });
+    }
+
+    const rows = await db.select().from(modules);
+    return new Response(JSON.stringify(rows), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+
+  // #24 POST /modules
+  async POST(
+    request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return new Response(null, { status: 403 });
+    }
+
+    const body: { id: string; nom: string } = await request.json();
+
+    if (!body.id || !body.nom) {
+      return new Response(null, { status: 400 });
+    }
+
+    const existing = await db
+      .select()
+      .from(modules)
+      .where(eq(modules.id, body.id))
+      .then((rows) => rows[0] ?? null);
+
+    if (existing) {
+      return new Response(
+        JSON.stringify({ error: "Un module avec cet identifiant existe déjà" }),
+        { status: 409, headers: { "content-type": "application/json" } },
+      );
+    }
+
+    const [created] = await db
+      .insert(modules)
+      .values({ id: body.id, nom: body.nom })
+      .returning();
+
+    return new Response(JSON.stringify(created), {
+      status: 201,
+      headers: { "content-type": "application/json" },
+    });
+  },
+};

routes/(apps)/admin/api/modules/[idModule].ts

@@ -0,0 +1,65 @@
+import { FreshContext, Handlers } from "$fresh/server.ts";
+import { db } from "$root/databases/db.ts";
+import { modules } 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> = {
+  // #25 GET /modules/{idModule}
+  async GET(
+    _request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    const module = await db
+      .select()
+      .from(modules)
+      .where(eq(modules.id, context.params.idModule))
+      .then((rows) => rows[0] ?? null);
+
+    if (!module) return NOT_FOUND;
+
+    return new Response(JSON.stringify(module), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+
+  // #26 PUT /modules/{idModule}
+  async PUT(
+    request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    const body: { nom: string } = await request.json();
+
+    const [updated] = await db
+      .update(modules)
+      .set({ nom: body.nom })
+      .where(eq(modules.id, context.params.idModule))
+      .returning();
+
+    if (!updated) return NOT_FOUND;
+
+    return new Response(JSON.stringify(updated), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+
+  // #27 DELETE /modules/{idModule}
+  async DELETE(
+    _request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    const [deleted] = await db
+      .delete(modules)
+      .where(eq(modules.id, context.params.idModule))
+      .returning();
+
+    if (!deleted) return NOT_FOUND;
+
+    return new Response(null, { status: 204 });
+  },
+};

routes/(apps)/admin/api/permissions.ts

@@ -0,0 +1,22 @@
+import { Handlers } from "$fresh/server.ts";
+import { AuthenticatedState } from "$root/defaults/interfaces.ts";
+
+const PERMISSIONS = [
+  { id: "student_read", nom: "Consulter les élèves" },
+  { id: "student_write", nom: "Gérer les élèves" },
+  { id: "note_read", nom: "Consulter les notes" },
+  { id: "note_write", nom: "Gérer les notes" },
+  { id: "module_read", nom: "Consulter les modules" },
+  { id: "module_write", nom: "Gérer les modules" },
+  { id: "user_read", nom: "Consulter les utilisateurs" },
+  { id: "user_write", nom: "Gérer les utilisateurs" },
+  { id: "role_write", nom: "Gérer les rôles" },
+] as const;
+
+export const handler: Handlers<null, AuthenticatedState> = {
+  GET(_request, _context): Response {
+    return new Response(JSON.stringify(PERMISSIONS), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+};

routes/(apps)/admin/api/roles.ts

@@ -0,0 +1,68 @@
+import { FreshContext, Handlers } from "$fresh/server.ts";
+import { db } from "$root/databases/db.ts";
+import { rolePermissions, roles } from "$root/databases/schema.ts";
+import { AuthenticatedState } from "$root/defaults/interfaces.ts";
+import { eq } from "npm:drizzle-orm@0.45.2";
+
+async function getRoleWithPermissions(
+  id: number,
+): Promise<{ id: number; nom: string; permissions: string[] } | null> {
+  const role = await db
+    .select()
+    .from(roles)
+    .where(eq(roles.id, id))
+    .then((rows) => rows[0] ?? null);
+
+  if (!role) return null;
+
+  const perms = await db
+    .select({ idPermission: rolePermissions.idPermission })
+    .from(rolePermissions)
+    .where(eq(rolePermissions.idRole, id));
+
+  return {
+    id: role.id,
+    nom: role.nom,
+    permissions: perms.map((p) => p.idPermission),
+  };
+}
+
+export const handler: Handlers<null, AuthenticatedState> = {
+  // #65 GET /roles
+  async GET(
+    _request: Request,
+    _context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    const allRoles = await db.select().from(roles);
+
+    const result = await Promise.all(
+      allRoles.map((r) => getRoleWithPermissions(r.id)),
+    );
+
+    return new Response(JSON.stringify(result), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+
+  // #66 POST /roles
+  async POST(
+    request: Request,
+    _context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    const body: { nom: string } = await request.json();
+
+    if (!body.nom) {
+      return new Response(null, { status: 400 });
+    }
+
+    const [created] = await db
+      .insert(roles)
+      .values({ nom: body.nom })
+      .returning();
+
+    return new Response(
+      JSON.stringify({ id: created.id, nom: created.nom, permissions: [] }),
+      { status: 201, headers: { "content-type": "application/json" } },
+    );
+  },
+};

routes/(apps)/admin/api/roles/[idRole].ts

@@ -0,0 +1,101 @@
+import { FreshContext, Handlers } from "$fresh/server.ts";
+import { db } from "$root/databases/db.ts";
+import { rolePermissions, roles } from "$root/databases/schema.ts";
+import { AuthenticatedState } from "$root/defaults/interfaces.ts";
+import { eq } from "npm:drizzle-orm@0.45.2";
+
+const NOT_FOUND = new Response(
+  JSON.stringify({ error: "Ressource introuvable" }),
+  { status: 404, headers: { "content-type": "application/json" } },
+);
+
+async function getRoleWithPermissions(
+  id: number,
+): Promise<{ id: number; nom: string; permissions: string[] } | null> {
+  const role = await db
+    .select()
+    .from(roles)
+    .where(eq(roles.id, id))
+    .then((rows) => rows[0] ?? null);
+
+  if (!role) return null;
+
+  const perms = await db
+    .select({ idPermission: rolePermissions.idPermission })
+    .from(rolePermissions)
+    .where(eq(rolePermissions.idRole, id));
+
+  return {
+    id: role.id,
+    nom: role.nom,
+    permissions: perms.map((p) => p.idPermission),
+  };
+}
+
+export const handler: Handlers<null, AuthenticatedState> = {
+  // #67 GET /roles/{idRole}
+  async GET(
+    _request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    const id = Number(context.params.idRole);
+    const role = await getRoleWithPermissions(id);
+
+    if (!role) return NOT_FOUND;
+
+    return new Response(JSON.stringify(role), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+
+  // #68 PUT /roles/{idRole}
+  async PUT(
+    request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    const id = Number(context.params.idRole);
+    const body: { nom: string; permissions: string[] } = await request.json();
+
+    const [updated] = await db
+      .update(roles)
+      .set({ nom: body.nom })
+      .where(eq(roles.id, id))
+      .returning();
+
+    if (!updated) return NOT_FOUND;
+
+    // Reset permissions
+    await db.delete(rolePermissions).where(eq(rolePermissions.idRole, id));
+
+    if (body.permissions?.length) {
+      await db.insert(rolePermissions).values(
+        body.permissions.map((p) => ({ idRole: id, idPermission: p })),
+      );
+    }
+
+    const role = await getRoleWithPermissions(id);
+    return new Response(JSON.stringify(role), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+
+  // #69 DELETE /roles/{idRole}
+  async DELETE(
+    _request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    const id = Number(context.params.idRole);
+
+    // Cascade delete role_permissions first
+    await db.delete(rolePermissions).where(eq(rolePermissions.idRole, id));
+
+    const [deleted] = await db
+      .delete(roles)
+      .where(eq(roles.id, id))
+      .returning();
+
+    if (!deleted) return NOT_FOUND;
+
+    return new Response(null, { status: 204 });
+  },
+};

routes/(apps)/admin/api/users.ts

@@ -0,0 +1,67 @@
+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@0.45.2";
+
+export const handler: Handlers<null, AuthenticatedState> = {
+  // #60 GET /users
+  async GET(
+    request: Request,
+    _context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    const url = new URL(request.url);
+    const idRole = url.searchParams.get("idRole");
+
+    const rows = idRole
+      ? await db.select().from(users).where(eq(users.idRole, Number(idRole)))
+      : await db.select().from(users);
+
+    return new Response(JSON.stringify(rows), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+
+  // #61 POST /users
+  async POST(
+    request: Request,
+    _context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    const body: { id: string; nom: string; prenom: string; idRole: number } =
+      await request.json();
+
+    if (!body.id || !body.nom || !body.prenom) {
+      return new Response(null, { status: 400 });
+    }
+
+    const existing = await db
+      .select()
+      .from(users)
+      .where(eq(users.id, body.id))
+      .then((rows) => rows[0] ?? null);
+
+    if (existing) {
+      return new Response(
+        JSON.stringify({
+          error: "Un utilisateur avec cet identifiant existe déjà",
+        }),
+        { status: 409, headers: { "content-type": "application/json" } },
+      );
+    }
+
+    const [created] = await db
+      .insert(users)
+      .values({
+        id: body.id,
+        nom: body.nom,
+        prenom: body.prenom,
+        idRole: body.idRole,
+      })
+      .returning();
+
+    return new Response(JSON.stringify(created), {
+      status: 201,
+      headers: { "content-type": "application/json" },
+    });
+  },
+};

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@0.45.2";
+
+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)/admin/index.tsx

@@ -0,0 +1,2 @@
+import makeIndex from "$root/defaults/makeIndex.ts";
+export default makeIndex(import.meta.dirname!);

routes/(apps)/admin/partials/index.tsx

@@ -0,0 +1,13 @@
+import {
+  getPartialsConfig,
+  makePartials,
+} from "$root/defaults/makePartials.tsx";
+import { FreshContext } from "$fresh/server.ts";
+import { State } from "$root/routes/_middleware.ts";
+
+export function Index(_request: Request, _context: FreshContext<State>) {
+  return <h2>Welcome to Admin.</h2>;
+}
+
+export const config = getPartialsConfig();
+export default makePartials(Index);

routes/(apps)/mobility/api/insert_mobility.ts

@@ -1,7 +1,7 @@
 import { Handlers } from "$fresh/server.ts";
 import { db } from "$root/databases/db.ts";
 import { mobility, promotions, students } from "$root/databases/schema.ts";
-import { eq } from "npm:drizzle-orm";
+import { eq } from "npm:drizzle-orm@0.45.2";
 
 export const handler: Handlers = {
   async GET() {
@@ -21,7 +21,11 @@ export const handler: Handlers = {
       const mobilityRows = await db.select().from(mobility);
 
       const promotionRows = await db
-        .select({ id: promotions.id, endyear: promotions.endyear, current: promotions.current })
+        .select({
+          id: promotions.id,
+          endyear: promotions.endyear,
+          current: promotions.current,
+        })
         .from(promotions);
 
       return new Response(
@@ -107,7 +111,9 @@ export const handler: Handlers = {
           });
       }
 
-      return new Response("Data inserted/updated successfully", { status: 200 });
+      return new Response("Data inserted/updated successfully", {
+        status: 200,
+      });
     } catch (error) {
       console.error("Error inserting mobility data:", error);
       return new Response("Failed to insert/update data", { status: 500 });

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

@@ -0,0 +1,83 @@
+import { FreshContext, Handlers } from "$fresh/server.ts";
+import { db } from "$root/databases/db.ts";
+import { ajustements } from "$root/databases/schema.ts";
+import { AuthenticatedState } from "$root/defaults/interfaces.ts";
+import { eq } from "npm:drizzle-orm@0.45.2";
+
+export const handler: Handlers<null, AuthenticatedState> = {
+  // #48 GET /ajustements
+  async GET(request) {
+    try {
+      const url = new URL(request.url);
+      const numEtudParam = url.searchParams.get("numEtud");
+      const idUEParam = url.searchParams.get("idUE");
+
+      let query = db.select().from(ajustements).$dynamic();
+
+      if (numEtudParam) {
+        const numEtud = parseInt(numEtudParam);
+        if (isNaN(numEtud)) {
+          return new Response("Paramètre numEtud invalide", { status: 400 });
+        }
+        query = query.where(eq(ajustements.numEtud, numEtud));
+      }
+
+      if (idUEParam) {
+        const idUE = parseInt(idUEParam);
+        if (isNaN(idUE)) {
+          return new Response("Paramètre idUE invalide", { status: 400 });
+        }
+        query = query.where(eq(ajustements.idUE, idUE));
+      }
+
+      const result = await query;
+
+      return new Response(JSON.stringify(result), {
+        status: 200,
+        headers: { "Content-Type": "application/json" },
+      });
+    } catch (error) {
+      console.error("Error fetching ajustements:", error);
+      return new Response("Failed to fetch data", { status: 500 });
+    }
+  },
+
+  // #49 POST /ajustements
+  async POST(
+    request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return new Response(null, { status: 403 });
+    }
+
+    try {
+      const body: { numEtud: number; idUE: number; valeur: number } =
+        await request.json();
+
+      if (!body.numEtud || !body.idUE || body.valeur === undefined) {
+        return new Response(
+          JSON.stringify({ error: "Champs requis: numEtud, idUE, valeur" }),
+          { status: 400, headers: { "content-type": "application/json" } },
+        );
+      }
+
+      const [created] = await db
+        .insert(ajustements)
+        .values({
+          numEtud: body.numEtud,
+          idUE: body.idUE,
+          valeur: body.valeur,
+        })
+        .returning();
+
+      return new Response(JSON.stringify(created), {
+        status: 201,
+        headers: { "content-type": "application/json" },
+      });
+    } catch (error) {
+      console.error("Error creating ajustement:", error);
+      return new Response("Failed to create ajustement", { status: 500 });
+    }
+  },
+};

routes/(apps)/notes/api/ajustements/[numEtud]/[idUE].ts

@@ -0,0 +1,43 @@
+import { FreshContext, Handlers } from "$fresh/server.ts";
+import { db } from "$root/databases/db.ts";
+import { ajustements } from "$root/databases/schema.ts";
+import { AuthenticatedState } from "$root/defaults/interfaces.ts";
+import { eq } from "npm:drizzle-orm@0.45.2";
+
+const NOT_FOUND = new Response(
+  JSON.stringify({ error: "Ajustement introuvable" }),
+  { status: 404, headers: { "content-type": "application/json" } },
+);
+
+const FORBIDDEN = new Response(null, { status: 403 });
+
+export const handler: Handlers<null, AuthenticatedState> = {
+  // #50 GET /ajustements/{numEtud}/{idUE}
+  async GET(
+    _request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return FORBIDDEN;
+    }
+
+    const numEtud = Number(context.params.numEtud);
+    const idUE = Number(context.params.idUE);
+
+    if (isNaN(numEtud) || isNaN(idUE)) {
+      return new Response("Paramètres invalides", { status: 400 });
+    }
+
+    const ajustement = await db
+      .select()
+      .from(ajustements)
+      .where(eq(ajustements.numEtud, numEtud), eq(ajustements.idUE, idUE))
+      .then((rows) => rows[0] ?? null);
+
+    if (!ajustement) return NOT_FOUND;
+
+    return new Response(JSON.stringify(ajustement), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+};

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

@@ -0,0 +1,61 @@
+import { Handlers } from "$fresh/server.ts";
+import { db } from "../../../../databases/db.ts";
+import { notes } from "../../../../databases/schema.ts";
+import { eq } from "npm:drizzle-orm";
+
+export const handler: Handlers = {
+  // #42 GET /notes
+  async GET(request) {
+    try {
+      const url = new URL(request.url);
+      const numEtudParam = url.searchParams.get("numEtud");
+      const idModule = url.searchParams.get("idModule");
+
+      let query = db.select().from(notes).$dynamic();
+
+      if (numEtudParam) {
+        const numEtud = parseInt(numEtudParam);
+        if (isNaN(numEtud)) {
+          return new Response("Paramètre numEtud invalide", { status: 400 });
+        }
+        query = query.where(eq(notes.numEtud, numEtud));
+      }
+
+      if (idModule) {
+        query = query.where(eq(notes.idModule, idModule));
+      }
+
+      const result = await query;
+
+      return new Response(JSON.stringify(result), {
+        status: 200,
+        headers: { "Content-Type": "application/json" },
+      });
+    } catch (error) {
+      console.error("Error fetching notes:", error);
+      return new Response("Failed to fetch data", { status: 500 });
+    }
+  },
+
+  // #43 POST /notes
+  async POST(request) {
+    try {
+      const body = await request.json();
+      const { note, numEtud, idModule } = body;
+
+      if (note === undefined || !numEtud || !idModule) {
+        return new Response("Champs 'note', 'numEtud' et 'idModule' requis", { status: 400 });
+      }
+
+      const result = await db.insert(notes).values({ note, numEtud, idModule }).returning();
+
+      return new Response(JSON.stringify(result[0]), {
+        status: 201,
+        headers: { "Content-Type": "application/json" },
+      });
+    } catch (error) {
+      console.error("Error creating note:", error);
+      return new Response("Failed to create note", { status: 500 });
+    }
+  },
+};

routes/(apps)/notes/api/notes/[numEtud]/[idModule].ts

@@ -0,0 +1,121 @@
+import { Handlers } from "$fresh/server.ts";
+import { db } from "../../../../../../databases/db.ts";
+import { notes } from "../../../../../../databases/schema.ts";
+import { and, eq } from "npm:drizzle-orm";
+
+export const handler: Handlers = {
+  // #45 GET /notes/:numEtud/:idModule  
+  async GET(_request, context) {
+    try {
+      const numEtud = parseInt(context.params.numEtud);
+      const { idModule } = context.params;
+
+      if (isNaN(numEtud)) {
+        return new Response(JSON.stringify({ error: "Paramètre numEtud invalide" }), {
+          status: 400,
+          headers: { "Content-Type": "application/json" },
+        });
+      }
+
+      const result = await db.select().from(notes).where(
+        and(
+          eq(notes.numEtud, numEtud),
+          eq(notes.idModule, idModule),
+        ),
+      );
+
+      if (result.length === 0) {
+        return new Response(JSON.stringify({ error: "Ressource introuvable" }), {
+          status: 404,
+          headers: { "Content-Type": "application/json" },
+        });
+      }
+
+      return new Response(JSON.stringify(result[0]), {
+        status: 200,
+        headers: { "Content-Type": "application/json" },
+      });
+    } catch (error) {
+      console.error("Error fetching note:", error);
+      return new Response("Failed to fetch data", { status: 500 });
+    }
+  },
+
+  // #46 PUT /notes/:numEtud/:idModule
+  async PUT(request, context) {
+    try {
+      const numEtud = parseInt(context.params.numEtud);
+      const { idModule } = context.params;
+
+      if (isNaN(numEtud)) {
+        return new Response(JSON.stringify({ error: "Paramètre numEtud invalide" }), {
+          status: 400,
+          headers: { "Content-Type": "application/json" },
+        });
+      }
+
+      const body = await request.json();
+      const { note } = body;
+
+      if (note === undefined) {
+        return new Response("Champ 'note' manquant", { status: 400 });
+      }
+
+      const result = await db.update(notes).set({ note }).where(
+        and(
+          eq(notes.numEtud, numEtud),
+          eq(notes.idModule, idModule),
+        ),
+      ).returning();
+
+      if (result.length === 0) {
+        return new Response(JSON.stringify({ error: "Ressource introuvable" }), {
+          status: 404,
+          headers: { "Content-Type": "application/json" },
+        });
+      }
+
+      return new Response(JSON.stringify(result[0]), {
+        status: 200,
+        headers: { "Content-Type": "application/json" },
+      });
+    } catch (error) {
+      console.error("Error updating note:", error);
+      return new Response("Failed to update note", { status: 500 });
+    }
+  },
+
+  // #47 DELETE /notes/:numEtud/:idModule
+  async DELETE(_request, context) {
+    try {
+      const numEtud = parseInt(context.params.numEtud);
+      const { idModule } = context.params;
+
+      if (isNaN(numEtud)) {
+        return new Response(JSON.stringify({ error: "Paramètre numEtud invalide" }), {
+          status: 400,
+          headers: { "Content-Type": "application/json" },
+        });
+      }
+
+      const result = await db.delete(notes).where(
+        and(
+          eq(notes.numEtud, numEtud),
+          eq(notes.idModule, idModule),
+        ),
+      ).returning();
+
+      if (result.length === 0) {
+        return new Response(JSON.stringify({ error: "Ressource introuvable" }), {
+          status: 404,
+          headers: { "Content-Type": "application/json" },
+        });
+      }
+
+      return new Response(null, { status: 204 });
+    } catch (error) {
+      console.error("Error deleting note:", error);
+      return new Response("Failed to delete note", { status: 500 });
+    }
+  },
+};

routes/(apps)/notes/api/ue-modules.ts

@@ -0,0 +1,58 @@
+import { Handlers } from "$fresh/server.ts";
+import { db } from "../../../../databases/db.ts";
+import { ueModules } from "../../../../databases/schema.ts";
+import { and, eq } from "npm:drizzle-orm";
+
+export const handler: Handlers = {
+  // #37 GET /ue-modules  
+  async GET(request) {
+    try {
+      const url = new URL(request.url);
+      const idPromo = url.searchParams.get("idPromo");
+      const idUEParam = url.searchParams.get("idUE");
+
+      const idUE = idUEParam ? parseInt(idUEParam) : null;
+
+      if (idUEParam && isNaN(idUE!)) {
+        return new Response("Paramètre idUE invalide", { status: 400 });
+      }
+
+      const result = await db.select().from(ueModules).where(
+        and(
+          idPromo ? eq(ueModules.idPromo, idPromo) : undefined,
+          idUE ? eq(ueModules.idUE, idUE) : undefined,
+        ),
+      );
+
+      return new Response(JSON.stringify(result), {
+        status: 200,
+        headers: { "Content-Type": "application/json" },
+      });
+    } catch (error) {
+      console.error("Error fetching UE-modules:", error);
+      return new Response("Failed to fetch data", { status: 500 });
+    }
+  },
+  
+  // #38 POST /ue-modules
+  async POST(request) {
+    try {
+      const body = await request.json();
+      const { idModule, idUE, idPromo, coeff } = body;
+
+      if (!idModule || !idUE || !idPromo || coeff === undefined) {
+        return new Response("Champs 'idModule', 'idUE', 'idPromo' et 'coeff' requis", { status: 400 });
+      }
+
+      const result = await db.insert(ueModules).values({ idModule, idUE, idPromo, coeff }).returning();
+
+      return new Response(JSON.stringify(result[0]), {
+        status: 201,
+        headers: { "Content-Type": "application/json" },
+      });
+    } catch (error) {
+      console.error("Error creating UE-module:", error);
+      return new Response("Failed to create UE-module", { status: 500 });
+    }
+  },
+};

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

@@ -3,6 +3,7 @@ import { db } from "../../../../databases/db.ts";
 import { ues } from "../../../../databases/schema.ts";
 
 export const handler: Handlers = {
+  // #32 GET /ues
   async GET() {
     try {
       const result = await db.select().from(ues);
@@ -16,4 +17,26 @@ export const handler: Handlers = {
       return new Response("Failed to fetch data", { status: 500 });
     }
   },
+
+  // #33 POST /ues
+  async POST(request) {
+    try {
+      const body = await request.json();
+      const { nom } = body;
+
+      if (!nom) {
+        return new Response("Champ 'nom' manquant", { status: 400 });
+      }
+
+      const result = await db.insert(ues).values({ nom }).returning();
+
+      return new Response(JSON.stringify(result[0]), {
+        status: 201,
+        headers: { "Content-Type": "application/json" },
+      });
+    } catch (error) {
+      console.error("Error creating UE:", error);
+      return new Response("Failed to create UE", { status: 500 });
+    }
+  },
 };

routes/(apps)/notes/api/ues/[idUE].ts

@@ -0,0 +1,37 @@
+import { Handlers } from "$fresh/server.ts";
+import { db } from "../../../../../databases/db.ts";
+import { ues } from "../../../../../databases/schema.ts";
+import { eq } from "npm:drizzle-orm";
+
+export const handler: Handlers = {
+  // # 34 GET /ues/:idUE  
+  async GET(_request, context) {
+    try {
+      const idUE = parseInt(context.params.idUE);
+
+      if (isNaN(idUE)) {
+        return new Response(JSON.stringify({ error: "Paramètre idUE invalide" }), {
+          status: 400,
+          headers: { "Content-Type": "application/json" },
+        });
+      }
+
+      const result = await db.select().from(ues).where(eq(ues.id, idUE));
+
+      if (result.length === 0) {
+        return new Response(JSON.stringify({ error: "Ressource introuvable" }), {
+          status: 404,
+          headers: { "Content-Type": "application/json" },
+        });
+      }
+
+      return new Response(JSON.stringify(result[0]), {
+        status: 200,
+        headers: { "Content-Type": "application/json" },
+      });
+    } catch (error) {
+      console.error("Error fetching UE:", error);
+      return new Response("Failed to fetch data", { status: 500 });
+    }
+  },
+};

routes/(apps)/students/api/promotions.ts

@@ -0,0 +1,49 @@
+import { FreshContext, Handlers } from "$fresh/server.ts";
+import { db } from "$root/databases/db.ts";
+import { promotions } from "$root/databases/schema.ts";
+import { AuthenticatedState } from "$root/defaults/interfaces.ts";
+
+export const handler: Handlers<null, AuthenticatedState> = {
+  // #13 GET /promotions
+  async GET(
+    _request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return new Response(JSON.stringify([]), {
+        headers: { "content-type": "application/json" },
+      });
+    }
+
+    const rows = await db.select().from(promotions);
+    return new Response(JSON.stringify(rows), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+
+  // #14 POST /promotions
+  async POST(
+    request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return new Response(null, { status: 403 });
+    }
+
+    const body: { idPromo: string; annee: string } = await request.json();
+
+    if (!body.idPromo || !body.annee) {
+      return new Response(null, { status: 400 });
+    }
+
+    const [created] = await db
+      .insert(promotions)
+      .values({ id: body.idPromo, annee: body.annee })
+      .returning();
+
+    return new Response(JSON.stringify(created), {
+      status: 201,
+      headers: { "content-type": "application/json" },
+    });
+  },
+};

routes/(apps)/students/api/promotions/[idPromo].ts

@@ -0,0 +1,79 @@
+import { FreshContext, Handlers } from "$fresh/server.ts";
+import { db } from "$root/databases/db.ts";
+import { promotions } from "$root/databases/schema.ts";
+import { AuthenticatedState } from "$root/defaults/interfaces.ts";
+import { eq } from "npm:drizzle-orm@0.45.2";
+
+const NOT_FOUND = new Response(
+  JSON.stringify({ error: "Ressource introuvable" }),
+  { status: 404, headers: { "content-type": "application/json" } },
+);
+
+const FORBIDDEN = new Response(null, { status: 403 });
+
+export const handler: Handlers<null, AuthenticatedState> = {
+  // #15 GET /promotions/{idPromo}
+  async GET(
+    _request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return FORBIDDEN;
+    }
+
+    const promo = await db
+      .select()
+      .from(promotions)
+      .where(eq(promotions.id, context.params.idPromo))
+      .then((rows) => rows[0] ?? null);
+
+    if (!promo) return NOT_FOUND;
+
+    return new Response(JSON.stringify(promo), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+
+  // #16 PUT /promotions/{idPromo}
+  async PUT(
+    request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return FORBIDDEN;
+    }
+
+    const body: { annee: string } = await request.json();
+
+    const [updated] = await db
+      .update(promotions)
+      .set({ annee: body.annee })
+      .where(eq(promotions.id, context.params.idPromo))
+      .returning();
+
+    if (!updated) return NOT_FOUND;
+
+    return new Response(JSON.stringify(updated), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+
+  // #17 DELETE /promotions/{idPromo}
+  async DELETE(
+    _request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return FORBIDDEN;
+    }
+
+    const [deleted] = await db
+      .delete(promotions)
+      .where(eq(promotions.id, context.params.idPromo))
+      .returning();
+
+    if (!deleted) return NOT_FOUND;
+
+    return new Response(null, { status: 204 });
+  },
+};

routes/(apps)/students/api/students.ts

@@ -1,122 +1,61 @@
 import { FreshContext, Handlers } from "$fresh/server.ts";
 import { db } from "$root/databases/db.ts";
-import { promotions, students } from "$root/databases/schema.ts";
+import { students } from "$root/databases/schema.ts";
 import { AuthenticatedState } from "$root/defaults/interfaces.ts";
-import { eq, lt } from "npm:drizzle-orm";
-
-async function getItself(
-  userId: string,
-): Promise<{ student: Student | null; promo: Promotion | null }> {
-  const student = await db
-    .select()
-    .from(students)
-    .where(eq(students.userId, userId))
-    .limit(1)
-    .then((rows) => rows[0] ?? null);
-
-  if (!student) {
-    return { student: null, promo: null };
-  }
-
-  const promo = await db
-    .select()
-    .from(promotions)
-    .where(eq(promotions.id, student.promotionId!))
-    .limit(1)
-    .then((rows) => rows[0] ?? null);
-
-  return { student, promo };
-}
-
-async function getAll(): Promise<
-  { students: Student[]; promos: Promotion[] }
-> {
-  const rows = await db
-    .select({
-      userId: students.userId,
-      firstName: students.firstName,
-      lastName: students.lastName,
-      mail: students.mail,
-      promotionId: students.promotionId,
-    })
-    .from(students)
-    .innerJoin(promotions, eq(students.promotionId, promotions.id))
-    .where(lt(promotions.current, 6));
-
-  const promos = await db
-    .select()
-    .from(promotions)
-    .where(lt(promotions.current, 6));
-
-  return { students: rows as Student[], promos };
-}
-
-async function addStudents(
-  studentList: Student[],
-  promoId: number,
-): Promise<void> {
-  for (const student of studentList) {
-    await db
-      .insert(students)
-      .values({
-        userId: student.userId,
-        firstName: student.firstName,
-        lastName: student.lastName,
-        mail: student.mail,
-        promotionId: promoId,
-      })
-      .onConflictDoNothing();
-  }
-}
+import { eq } from "npm:drizzle-orm@0.45.2";
 
 export const handler: Handlers<null, AuthenticatedState> = {
+  // #7 GET /students
   async GET(
-    _request: Request,
+    request: Request,
     context: FreshContext<AuthenticatedState>,
   ): Promise<Response> {
-    if (context.state.session.eduPersonPrimaryAffiliation == "student") {
-      return new Response(
-        JSON.stringify(await getItself(context.state.session.uid)),
-        { headers: { "content-type": "application/json" } },
-      );
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return new Response(JSON.stringify([]), {
+        headers: { "content-type": "application/json" },
+      });
     }
 
-    return new Response(
-      JSON.stringify(await getAll()),
-      { headers: { "content-type": "application/json" } },
-    );
+    const url = new URL(request.url);
+    const idPromo = url.searchParams.get("idPromo");
+
+    const rows = idPromo
+      ? await db.select().from(students).where(eq(students.idPromo, idPromo))
+      : await db.select().from(students);
+
+    return new Response(JSON.stringify(rows), {
+      headers: { "content-type": "application/json" },
+    });
   },
 
+  // #8 POST /students
   async POST(
     request: Request,
-    _context: FreshContext<AuthenticatedState>,
+    context: FreshContext<AuthenticatedState>,
   ): Promise<Response> {
-    const { students: studentList, promo }: {
-      students: Student[];
-      promo: string;
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return new Response(null, { status: 403 });
+    }
+
+    const body: {
+      numEtud: number;
+      nom: string;
+      prenom: string;
+      idPromo: string;
     } = await request.json();
 
-    if (!promo || !promo.match(/^\d{4}-\dA$/) || !Array.isArray(studentList)) {
+    if (!body.nom || !body.prenom || !body.idPromo) {
       return new Response(null, { status: 400 });
     }
 
-    const { endyear, current } = promo.match(
-      /^(?<endyear>\d{4})-(?<current>\d)A$/,
-    )?.groups!;
+    const [created] = await db
+      .insert(students)
+      .values({ nom: body.nom, prenom: body.prenom, idPromo: body.idPromo })
+      .returning();
 
-    await db
-      .insert(promotions)
-      .values({ endyear: Number(endyear), current: Number(current) })
-      .onConflictDoNothing();
-
-    const promo_row = await db
-      .select()
-      .from(promotions)
-      .where(eq(promotions.endyear, Number(endyear)))
-      .then((rows) => rows.find((r) => r.current === Number(current))!);
-
-    await addStudents(studentList, promo_row.id);
-
-    return new Response(null, { status: 201 });
+    return new Response(JSON.stringify(created), {
+      status: 201,
+      headers: { "content-type": "application/json" },
+    });
   },
 };

routes/(apps)/students/api/students/[numEtud].ts

@@ -0,0 +1,83 @@
+import { FreshContext, Handlers } from "$fresh/server.ts";
+import { db } from "$root/databases/db.ts";
+import { students } from "$root/databases/schema.ts";
+import { AuthenticatedState } from "$root/defaults/interfaces.ts";
+import { eq } from "npm:drizzle-orm@0.45.2";
+
+const NOT_FOUND = new Response(
+  JSON.stringify({ error: "Ressource introuvable" }),
+  { status: 404, headers: { "content-type": "application/json" } },
+);
+
+const FORBIDDEN = new Response(null, { status: 403 });
+
+export const handler: Handlers<null, AuthenticatedState> = {
+  // #10 GET /students/{numEtud}
+  async GET(
+    _request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return FORBIDDEN;
+    }
+
+    const numEtud = Number(context.params.numEtud);
+    const student = await db
+      .select()
+      .from(students)
+      .where(eq(students.numEtud, numEtud))
+      .then((rows) => rows[0] ?? null);
+
+    if (!student) return NOT_FOUND;
+
+    return new Response(JSON.stringify(student), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+
+  // #11 PUT /students/{numEtud}
+  async PUT(
+    request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return FORBIDDEN;
+    }
+
+    const numEtud = Number(context.params.numEtud);
+    const body: { nom: string; prenom: string; idPromo: string } = await request
+      .json();
+
+    const [updated] = await db
+      .update(students)
+      .set({ nom: body.nom, prenom: body.prenom, idPromo: body.idPromo })
+      .where(eq(students.numEtud, numEtud))
+      .returning();
+
+    if (!updated) return NOT_FOUND;
+
+    return new Response(JSON.stringify(updated), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+
+  // #12 DELETE /students/{numEtud}
+  async DELETE(
+    _request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return FORBIDDEN;
+    }
+
+    const numEtud = Number(context.params.numEtud);
+    const [deleted] = await db
+      .delete(students)
+      .where(eq(students.numEtud, numEtud))
+      .returning();
+
+    if (!deleted) return NOT_FOUND;
+
+    return new Response(null, { status: 204 });
+  },
+};

routes/(apps)/students/api/students/import-csv.ts

@@ -0,0 +1,64 @@
+import { FreshContext, Handlers } from "$fresh/server.ts";
+import { db } from "$root/databases/db.ts";
+import { students } from "$root/databases/schema.ts";
+import { AuthenticatedState } from "$root/defaults/interfaces.ts";
+
+// #9 POST /students/import-csv
+export const handler: Handlers<null, AuthenticatedState> = {
+  async POST(
+    request: Request,
+    context: FreshContext<AuthenticatedState>,
+  ): Promise<Response> {
+    if (context.state.session.eduPersonPrimaryAffiliation !== "employee") {
+      return new Response(null, { status: 403 });
+    }
+
+    const formData = await request.formData();
+    const file = formData.get("file") as File | null;
+    const idPromo = formData.get("idPromo") as string | null;
+
+    if (!file || !idPromo) {
+      return new Response(null, { status: 400 });
+    }
+
+    const text = await file.text();
+    const lines = text.trim().split("\n");
+
+    let imported = 0;
+    const errors: { line: number; message: string }[] = [];
+
+    for (let i = 0; i < lines.length; i++) {
+      const lineNum = i + 1;
+      const cols = lines[i].split(",").map((c) => c.trim());
+
+      const [numEtudStr, nom, prenom] = cols;
+
+      if (!numEtudStr) {
+        errors.push({ line: lineNum, message: "Numéro étudiant manquant" });
+        continue;
+      }
+
+      const numEtud = Number(numEtudStr);
+      if (isNaN(numEtud)) {
+        errors.push({ line: lineNum, message: "Numéro étudiant invalide" });
+        continue;
+      }
+
+      if (!nom || !prenom) {
+        errors.push({ line: lineNum, message: "Nom ou prénom manquant" });
+        continue;
+      }
+
+      await db
+        .insert(students)
+        .values({ nom, prenom, idPromo })
+        .onConflictDoNothing();
+
+      imported++;
+    }
+
+    return new Response(JSON.stringify({ imported, errors }), {
+      headers: { "content-type": "application/json" },
+    });
+  },
+};