feat(database): add roles, permissions, users, modules, and related tables

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,9 +1,9 @@
 import {
   date,
-  doublePrecision,
   integer,
   pgTable,
   primaryKey,
+  real,
   serial,
   text,
 } from "npm:drizzle-orm/pg-core";
@@ -33,15 +33,16 @@ export const users = pgTable("users", {
 });
 
 export const promotions = pgTable("promotions", {
-  id: text("idPromo").primaryKey(),
+  id: serial("id").primaryKey(),
-  annee: text("annee"),
+  endyear: integer("endyear"),
+  current: integer("current"),
 });
 
 export const students = pgTable("students", {
   numEtud: serial("numEtud").primaryKey(),
   nom: text("nom").notNull(),
   prenom: text("prenom").notNull(),
-  idPromo: text("idPromo").references(() => promotions.id),
+  idPromo: text("idPromo").references(() => promotions.id as unknown as string),
 });
 
 export const modules = pgTable("modules", {
@@ -52,7 +53,7 @@ export const modules = pgTable("modules", {
 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),
+  idPromo: text("idPromo").notNull(),
 }, (t) => ({
   pk: primaryKey({ columns: [t.idProf, t.idModule, t.idPromo] }),
 }));
@@ -65,8 +66,8 @@ export const ues = pgTable("ues", {
 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),
+  idPromo: text("idPromo").notNull(),
-  coeff: doublePrecision("coeff").notNull(),
+  coeff: real("coeff").notNull(),
 }, (t) => ({
   pk: primaryKey({ columns: [t.idModule, t.idUE, t.idPromo] }),
 }));
@@ -74,7 +75,7 @@ export const ueModules = pgTable("ue_modules", {
 export const notes = pgTable("notes", {
   numEtud: integer("numEtud").notNull().references(() => students.numEtud),
   idModule: text("idModule").notNull().references(() => modules.id),
-  note: doublePrecision("note").notNull(),
+  note: real("note").notNull(),
 }, (t) => ({
   pk: primaryKey({ columns: [t.numEtud, t.idModule] }),
 }));
@@ -82,7 +83,7 @@ export const notes = pgTable("notes", {
 export const ajustements = pgTable("ajustements", {
   numEtud: integer("numEtud").notNull().references(() => students.numEtud),
   idUE: integer("idUE").notNull().references(() => ues.id),
-  valeur: doublePrecision("valeur").notNull(),
+  valeur: real("valeur").notNull(),
 }, (t) => ({
   pk: primaryKey({ columns: [t.numEtud, t.idUE] }),
 }));

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

@@ -1,13 +0,0 @@
-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/example.ts

@@ -1,22 +0,0 @@
-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/permissions.ts

@@ -1,22 +0,0 @@
-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/index.tsx

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

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

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

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

@@ -1,19 +0,0 @@
-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 });
-    }
-  },
-};