-- Base de données : gestion des notes du cours optionnel
-- Encodage : utf8mb4

CREATE TABLE enseignants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(100) NOT NULL,
  prenom VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  mot_de_passe_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE annees_scolaires (
  id INT AUTO_INCREMENT PRIMARY KEY,
  libelle VARCHAR(20) NOT NULL UNIQUE,      -- ex. "2026-2027"
  statut ENUM('ouverte','cloturee') NOT NULL DEFAULT 'ouverte',
  date_debut DATE,
  date_fin DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Périodes de vacances scolaires vaudoises, saisies par les enseignants
-- (sert à exclure ces jours de la "semaine ouvrable précédente")
CREATE TABLE vacances_scolaires (
  id INT AUTO_INCREMENT PRIMARY KEY,
  annee_scolaire_id INT NOT NULL,
  libelle VARCHAR(100),
  date_debut DATE NOT NULL,
  date_fin DATE NOT NULL,
  FOREIGN KEY (annee_scolaire_id) REFERENCES annees_scolaires(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE groupes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  annee_scolaire_id INT NOT NULL,
  nom VARCHAR(100) NOT NULL,               -- ex. "Lundi", "Mardi"
  FOREIGN KEY (annee_scolaire_id) REFERENCES annees_scolaires(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Un groupe peut être suivi par un ou plusieurs enseignants
CREATE TABLE groupe_enseignants (
  groupe_id INT NOT NULL,
  enseignant_id INT NOT NULL,
  PRIMARY KEY (groupe_id, enseignant_id),
  FOREIGN KEY (groupe_id) REFERENCES groupes(id) ON DELETE CASCADE,
  FOREIGN KEY (enseignant_id) REFERENCES enseignants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Mémorise, pour chaque enseignant, quels groupes il affiche sur son tableau de bord
CREATE TABLE affichage_prefs (
  enseignant_id INT NOT NULL,
  groupe_id INT NOT NULL,
  visible TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (enseignant_id, groupe_id),
  FOREIGN KEY (enseignant_id) REFERENCES enseignants(id) ON DELETE CASCADE,
  FOREIGN KEY (groupe_id) REFERENCES groupes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE eleves (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(100) NOT NULL,
  prenom VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,       -- fixe, non modifiable par l'élève
  mot_de_passe_hash VARCHAR(255) NULL,      -- NULL tant que le compte n'est pas activé
  classe_origine VARCHAR(50),
  token_invitation VARCHAR(64) NULL,
  token_invitation_expire DATETIME NULL,
  token_reset VARCHAR(64) NULL,
  token_reset_expire DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Inscription d'un élève à une année scolaire précise dans un groupe précis
-- (permet à un redoublant de garder son compte élève et d'avoir une 2e inscription)
CREATE TABLE inscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  eleve_id INT NOT NULL,
  annee_scolaire_id INT NOT NULL,
  groupe_id INT NOT NULL,
  statut ENUM('actif','redoublant') NOT NULL DEFAULT 'actif',
  FOREIGN KEY (eleve_id) REFERENCES eleves(id) ON DELETE CASCADE,
  FOREIGN KEY (annee_scolaire_id) REFERENCES annees_scolaires(id),
  FOREIGN KEY (groupe_id) REFERENCES groupes(id),
  UNIQUE KEY uniq_eleve_annee (eleve_id, annee_scolaire_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Catalogue fixe des métiers réutilisables d'une année à l'autre
CREATE TABLE metiers_catalogue (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(150) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Un métier suivi par un élève sur un semestre donné
-- (catalogue_id renseigné = métier du catalogue ; sinon nom_libre = métier libre)
CREATE TABLE metiers_instances (
  id INT AUTO_INCREMENT PRIMARY KEY,
  inscription_id INT NOT NULL,
  semestre TINYINT NOT NULL,                -- 1 ou 2
  catalogue_id INT NULL,
  nom_libre VARCHAR(150) NULL,
  description_libre TEXT NULL,
  date_debut DATE NOT NULL,
  date_fin DATE NULL,
  statut ENUM('en_cours','termine') NOT NULL DEFAULT 'en_cours',
  note_projet DECIMAL(3,1) NULL,            -- 1.0 à 6.0, arrondi au demi (70%)
  commentaire_projet TEXT NULL,
  FOREIGN KEY (inscription_id) REFERENCES inscriptions(id) ON DELETE CASCADE,
  FOREIGN KEY (catalogue_id) REFERENCES metiers_catalogue(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Notes de séance (2x/semaine, progression + attitude en une note), 30%
CREATE TABLE seances_notes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  metier_instance_id INT NOT NULL,
  date_seance DATE NOT NULL,
  note DECIMAL(3,1) NOT NULL,               -- 1.0 à 6.0, arrondi au dixième
  commentaire TEXT NULL,
  saisi_par INT NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (metier_instance_id) REFERENCES metiers_instances(id) ON DELETE CASCADE,
  FOREIGN KEY (saisi_par) REFERENCES enseignants(id),
  UNIQUE KEY uniq_metier_date (metier_instance_id, date_seance)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
