djalim
b8d359a507
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.
100 lines
3.0 KiB
TypeScript
100 lines
3.0 KiB
TypeScript
import {
|
|
date,
|
|
doublePrecision,
|
|
integer,
|
|
pgTable,
|
|
primaryKey,
|
|
serial,
|
|
text,
|
|
} from "npm:drizzle-orm/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),
|
|
});
|
|
|
|
export const promotions = pgTable("promotions", {
|
|
id: text("idPromo").primaryKey(),
|
|
annee: text("annee"),
|
|
});
|
|
|
|
export const students = pgTable("students", {
|
|
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: 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"),
|
|
});
|