import {
  date,
  doublePrecision,
  integer,
  pgTable,
  primaryKey,
  serial,
  text,
} 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),
});

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"),
});