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,4 +1,5 @@
 import {
+  date,
   doublePrecision,
   integer,
   pgTable,
@@ -7,7 +8,30 @@ import {
   text,
 } from "npm:drizzle-orm/pg-core";
 
-// Ancien schéma conservé
+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),
+});
+
 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"),
+  nom: text("nom").notNull(),
-  prenom: text("prenom"),
+  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),
+  idProf: text("idProf").notNull().references(() => users.id),
-  idModule: text("idModule").references(() => modules.id),
+  idModule: text("idModule").notNull().references(() => modules.id),
-  idPromo: text("idPromo").references(() => promotions.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),
+  idModule: text("idModule").notNull().references(() => modules.id),
-  idUE: integer("idUE").references(() => ues.id),
+  idUE: integer("idUE").notNull().references(() => ues.id),
-  idPromo: text("idPromo").references(() => promotions.id),
+  idPromo: text("idPromo").notNull().references(() => promotions.id),
-  coeff: doublePrecision("coeff"),
+  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),
+  numEtud: integer("numEtud").notNull().references(() => students.numEtud),
-  idModule: text("idModule").references(() => modules.id),
+  idModule: text("idModule").notNull().references(() => modules.id),
-  note: doublePrecision("note"),
+  note: doublePrecision("note").notNull(),
 }, (t) => ({
   pk: primaryKey({ columns: [t.numEtud, t.idModule] }),
 }));
 
 export const ajustements = pgTable("ajustements", {
-  numEtud: integer("numEtud").references(() => students.numEtud),
+  numEtud: integer("numEtud").notNull().references(() => students.numEtud),
-  idUE: integer("idUE").references(() => ues.id),
+  idUE: integer("idUE").notNull().references(() => ues.id),
-  valeur: doublePrecision("valeur"),
+  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"),
+});