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,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"),
+  nom: text("nom").notNull(),
-  current: integer("current"),
+});
+
+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(),
+  numEtud: serial("numEtud").primaryKey(),
-  firstName: text("firstName"),
+  nom: text("nom").notNull(),
-  lastName: text("lastName"),
+  prenom: text("prenom").notNull(),
-  mail: text("mail"),
+  idPromo: text("idPromo").references(() => promotions.id),
-  promotionId: integer("promotionId").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)/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 });
+    }
+  },
+};