import {
  doublePrecision,
  integer,
  pgEnum,
  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(),
  noteSession2: doublePrecision("noteSession2"),
}, (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(),
  malus: integer("malus").notNull().default(0),
}, (t) => ({
  pk: primaryKey({ columns: [t.numEtud, t.idUE] }),
}));

export const stages = pgTable("stages", {
  id: serial("idStage").primaryKey(),
  numEtud: integer("numEtud").notNull().references(() => students.numEtud),
  duree: integer("duree").notNull(),
  nomEntreprise: text("nomEntreprise").notNull(),
  mission: text("mission"),
});

export const mobilityStatusEnum = pgEnum("mobility_status", [
  "contracts_received",
  "under_revision",
  "done",
  "validated",
  "canceled",
]);

export const mobilites = pgTable("mobilites", {
  id: serial("idMob").primaryKey(),
  numEtud: integer("numEtud").notNull().references(() => students.numEtud),
  duree: integer("duree").notNull(),
  contratMob: text("contratMob"),
  ecole: text("ecole"),
  pays: text("pays"),
  status: mobilityStatusEnum("status").notNull().default("contracts_received"),
  idStage: integer("idStage").references(() => stages.id),
});