START TRANSACTION;
SET FOREIGN_KEY_CHECKS = 0;

-- Table représentant les différentes langues
CREATE TABLE IF NOT EXISTS langue (
  id int NOT NULL AUTO_INCREMENT,
  nom varchar(20) NOT NULL,
  code varchar(4) NOT NULL,
  actif boolean NOT NULL DEFAULT TRUE,
  date_creation datetime DEFAULT CURRENT_TIMESTAMP,
  date_derniere_mise_a_jour datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY nom (nom),
  UNIQUE KEY code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

-- Table représentant les différents sites de EILCO (Dunkerque, Calais, etc.)
CREATE TABLE IF NOT EXISTS campus(id int NOT NULL AUTO_INCREMENT,nom varchar(20) NOT NULL,date_creation datetime DEFAULT CURRENT_TIMESTAMP,
  date_derniere_mise_a_jour datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (id),
  UNIQUE KEY nom (nom))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

-- Table représentant les spécialités des étudiants (INFO, etc)
CREATE TABLE IF NOT EXISTS specialite (
  id int NOT NULL AUTO_INCREMENT,
  nom varchar(100) NOT NULL,
  code varchar(15) NOT NULL,
  date_creation datetime DEFAULT CURRENT_TIMESTAMP,
  date_derniere_mise_a_jour datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY nom (nom),
  UNIQUE KEY code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

-- Table représentant les niveaux d'études/ Classe des étudiants (CP1, CP2, ING1, etc)
CREATE TABLE IF NOT EXISTS classe (
  id int NOT NULL AUTO_INCREMENT,
  code varchar(15) NOT NULL,
  date_creation datetime DEFAULT CURRENT_TIMESTAMP,
  date_derniere_mise_a_jour datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

-- Table représentant les différents niveaux de langues.
-- Cette table contient le nom du niveau et la correspondance avec la langue concerné
-- EX: (Débutant, Allemand) (Débutant, Espagnol)
CREATE TABLE IF NOT EXISTS niveau_langue (
  id int NOT NULL AUTO_INCREMENT,
  nom varchar(20) NOT NULL,
  langue_id int NOT NULL ,
  difficulty int not null,
  date_creation datetime DEFAULT CURRENT_TIMESTAMP,
  date_derniere_mise_a_jour datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY nom (nom,langue_id),
    FOREIGN KEY (langue_id) REFERENCES langue(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

-- Table pour enregistrer les informations sur les professeurs
CREATE TABLE IF NOT EXISTS professeur(id int not Null AUTO_INCREMENT,
    nom varchar(150) NOT NULL, prenom varchar(50) DEFAULT NULL,
    email varchar(100) NOT NULL,langue_id int NOT NULL,
    date_creation datetime DEFAULT CURRENT_TIMESTAMP,
    date_derniere_mise_a_jour datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    actif boolean NOT NULL DEFAULT TRUE,
    CONSTRAINT pk PRIMARY KEY(id) ,
    CONSTRAINT uk_email UNIQUE(email),
    CONSTRAINT fk_professeur_langue FOREIGN KEY (langue_id) REFERENCES langue (id) ON DELETE RESTRICT ON UPDATE CASCADE
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;


-- Table permettant de lier chaque professeur aux différents campus ou il enseigne
CREATE TABLE IF NOT EXISTS professeur_campus(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  professeur_id INT NOT NULL,
  campus_id INT NOT NULL,
  date_creation datetime DEFAULT CURRENT_TIMESTAMP,
  date_derniere_mise_a_jour datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_professeur_campus_campus FOREIGN KEY(campus_id) REFERENCES campus(id),
  CONSTRAINT fk_professeur_campus_professeur FOREIGN KEY(professeur_id) REFERENCES professeur(id),
  CONSTRAINT pk_professeur_campus UNIQUE(id,professeur_id,campus_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

-- Table pour conserver les étudiants inscritent dans chaque campus
CREATE TABLE IF NOT EXISTS etudiant (

  id int NOT NULL AUTO_INCREMENT,
  campus_id int NOT NULL,
  specialite_id int NOT NULL,
  classe_id int NOT NULL,
  annee_inscription datetime DEFAULT NULL,
  nom varchar(100) NOT NULL,
  email varchar(100) NOT NULL,
  prenom varchar(100) NOT NULL,
  date_creation datetime DEFAULT CURRENT_TIMESTAMP,
  date_derniere_mise_a_jour datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_nom_prenom (nom,prenom),
  UNIQUE KEY uk_email (email),
  CONSTRAINT fk_classe FOREIGN KEY (classe_id) REFERENCES classe (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_specialitee FOREIGN KEY (specialite_id) REFERENCES specialite (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_campus FOREIGN KEY (campus_id) REFERENCES campus (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;


-- Table permettant de conserver les différents groupes de langue dans chaque campus
CREATE TABLE IF NOT EXISTS groupe (
  id INT AUTO_INCREMENT,
  campus_id INT NOT NULL,
  nom VARCHAR(50) NOT NULL,
  niveau_langue_id INT NOT NULL,
  professeur_id INT DEFAULT NULL,
  annee datetime DEFAULT CURRENT_TIMESTAMP,
  date_creation datetime DEFAULT CURRENT_TIMESTAMP,
  date_derniere_mise_a_jour datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT pk PRIMARY KEY(id),
  CONSTRAINT fk_groupe_niveau_langue FOREIGN KEY(niveau_langue_id) REFERENCES niveau_langue(id),
  CONSTRAINT fk_groupe_campus FOREIGN KEY(campus_id) REFERENCES campus(id),
  CONSTRAINT uk_groupe UNIQUE(campus_id,niveau_langue_id,nom),
  CONSTRAINT fk_professeur FOREIGN KEY (professeur_id) REFERENCES professeur (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE IF NOT EXISTS etudiant_groupe(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,etudiant_id INT NOT NULL,groupe_id INT NOT NULL,
  groupe_actuel BOOLEAN DEFAULT true,
  date_creation datetime DEFAULT CURRENT_TIMESTAMP,
  date_derniere_mise_a_jour datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT etu_fk FOREIGN KEY(etudiant_id) REFERENCES etudiant(id),
  CONSTRAINT groupe_fk FOREIGN KEY(groupe_id) REFERENCES groupe(id),
  CONSTRAINT etudiant_group_uk UNIQUE(id,etudiant_id,groupe_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;


-- Table représentant les choix des étudiants. Cette table enregistre les données renseignées depuis le formulaire de choix
CREATE TABLE IF NOT EXISTS choix (
  etudiant_id int NOT NULL,
  campus_id int NOT NULL,
  langue_id int NOT NULL,
  nombre_annee int NOT NULL,
  date_creation datetime DEFAULT CURRENT_TIMESTAMP,
  date_derniere_mise_a_jour datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (etudiant_id,langue_id),
  CONSTRAINT fk_etudiant FOREIGN KEY (etudiant_id) REFERENCES etudiant (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_choix_langue FOREIGN KEY (langue_id) REFERENCES langue (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_campus_choix FOREIGN KEY (campus_id) REFERENCES campus (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT ck_nombre_annee CHECK  (nombre_annee IN (0,1,2,3,4))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

-- Table pour conserver les informations sur les administrateurs de l'application
CREATE TABLE user (
  id int PRIMARY KEY AUTO_INCREMENT,
  email varchar(255) NOT NULL UNIQUE,
  password varchar(255) NOT NULL,
  nom varchar(150) NOT NULL,
  prenom varchar(50) DEFAULT NULL,
    roles json NOT NULL DEFAULT(JSON_ARRAY()),
  date_creation datetime DEFAULT CURRENT_TIMESTAMP,
  date_derniere_mise_a_jour datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

-- Table représentant les notifications envoyées aux utilisateurs
CREATE TABLE notification (
    id int PRIMARY KEY AUTO_INCREMENT,
    title varchar(255)  DEFAULT NULL,
    content text(5000) NOT NULL,
    type VARCHAR(50) DEFAULT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    user_id int DEFAULT NULL,
    date_creation datetime DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES user(id)  ON DELETE  CASCADE  ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

-- Table contenant les configurations de notre application. Utile pour par exemple pour désactiver l'envoi des mails
CREATE TABLE `configuration` (
    `id` int PRIMARY KEY AUTO_INCREMENT,
    `nom` varchar(255) NOT NULL,
    `defaut` text DEFAULT NULL,
    `valeur` text DEFAULT NULL,
     `date_creation` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

COMMIT;
SET FOREIGN_KEY_CHECKS = 1;