CREATE DATABASE IF NOT EXISTS kaye CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE kaye;

CREATE TABLE cycles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nom VARCHAR(50) NOT NULL
);

CREATE TABLE niveaux (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nom VARCHAR(20) NOT NULL,
  cycle_id INT NOT NULL,
  ordre INT,
  FOREIGN KEY (cycle_id) REFERENCES cycles(id)
);

CREATE TABLE matieres (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nom VARCHAR(100) NOT NULL,
  cycle_id INT NOT NULL,
  niveau_id INT,
  couleur_bandeau VARCHAR(7) NOT NULL,
  couleur_texte VARCHAR(7) NOT NULL,
  FOREIGN KEY (cycle_id) REFERENCES cycles(id),
  FOREIGN KEY (niveau_id) REFERENCES niveaux(id)
);

CREATE TABLE domaines (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nom VARCHAR(150) NOT NULL,
  matiere_id INT NOT NULL,
  FOREIGN KEY (matiere_id) REFERENCES matieres(id)
);

CREATE TABLE competences (
  id INT PRIMARY KEY AUTO_INCREMENT,
  domaine_id INT NOT NULL,
  libelle TEXT NOT NULL,
  niveau VARCHAR(10),
  annee_application INT,
  ordre INT DEFAULT 0,
  FOREIGN KEY (domaine_id) REFERENCES domaines(id)
);

CREATE TABLE etiquettes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  libelle VARCHAR(100) NOT NULL,
  couleur_bandeau VARCHAR(7) NOT NULL,
  couleur_texte VARCHAR(7) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE seances (
  id INT PRIMARY KEY AUTO_INCREMENT,
  date DATE NOT NULL,
  heure_debut TIME NOT NULL,
  heure_fin TIME NOT NULL,
  titre VARCHAR(200),
  objectif TEXT,
  activites TEXT,
  type ENUM('seance', 'pause') NOT NULL DEFAULT 'seance',
  etiquette_id INT,
  realisee TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (etiquette_id) REFERENCES etiquettes(id)
);

CREATE TABLE ateliers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  seance_id INT NOT NULL,
  titre VARCHAR(255) NOT NULL DEFAULT 'Atelier',
  encadrement ENUM('dirige', 'semi_dirige', 'autonomie') NOT NULL DEFAULT 'dirige',
  description TEXT,
  ordre INT NOT NULL DEFAULT 0,
  FOREIGN KEY (seance_id) REFERENCES seances(id) ON DELETE CASCADE
);

CREATE TABLE seance_referentiel (
  id INT PRIMARY KEY AUTO_INCREMENT,
  seance_id INT NOT NULL,
  atelier_id INT,
  matiere_id INT NOT NULL,
  domaine_id INT,
  competence_id INT,
  FOREIGN KEY (seance_id) REFERENCES seances(id) ON DELETE CASCADE,
  FOREIGN KEY (atelier_id) REFERENCES ateliers(id) ON DELETE CASCADE,
  FOREIGN KEY (matiere_id) REFERENCES matieres(id),
  FOREIGN KEY (domaine_id) REFERENCES domaines(id),
  FOREIGN KEY (competence_id) REFERENCES competences(id)
);

CREATE TABLE materiels (
  id INT PRIMARY KEY AUTO_INCREMENT,
  seance_id INT,
  atelier_id INT,
  libelle VARCHAR(255) NOT NULL,
  ordre INT NOT NULL DEFAULT 0,
  FOREIGN KEY (seance_id) REFERENCES seances(id) ON DELETE CASCADE,
  FOREIGN KEY (atelier_id) REFERENCES ateliers(id) ON DELETE CASCADE
);

CREATE TABLE annees_scolaires (
  id INT PRIMARY KEY AUTO_INCREMENT,
  titre VARCHAR(50) NOT NULL,
  date_debut DATE,
  date_fin DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE periodes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  annee_scolaire_id INT NOT NULL,
  titre VARCHAR(100) NOT NULL,
  date_debut DATE NOT NULL,
  date_fin DATE NOT NULL,
  nb_semaines INT NOT NULL DEFAULT 1,
  ordre INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (annee_scolaire_id) REFERENCES annees_scolaires(id) ON DELETE CASCADE
);

-- ============================================================================
-- Onglet "Classe" (Réglages) — élèves, niveaux de la classe, champs custom.
-- ============================================================================
--
-- Décisions de modélisation :
--
-- 1. classes : une classe = les élèves d'une année scolaire donnée. L'utilisateur
--    est un enseignant mono-classe (cf. CLAUDE.md), mais la classe est tout de
--    même rattachée à annees_scolaires (1-1 dans les faits) plutôt que d'être un
--    singleton global, pour permettre l'historique d'une rentrée sur l'autre
--    (élèves de l'an dernier consultables, pas écrasés à la rentrée suivante).
--    Une classe peut couvrir plusieurs niveaux (ex: CE2/CM1) → table de liaison
--    classe_niveaux (many-to-many) vers la table `niveaux` déjà existante
--    (référentiel BO), pas de duplication d'un référentiel de niveaux.
--
-- 2. eleves : champs fixes communs à tout enseignant (nom, prenom, sexe,
--    date_naissance) + niveau_id obligatoire, qui DOIT faire partie des niveaux
--    de la classe (contrainte applicative, vérifiée côté PHP — un CHECK SQL
--    portable multi-niveaux est plus complexe que la validation en code).
--
-- 3. Champs personnalisés en EAV (Entity-Attribute-Value) :
--    champs_personnalises (définition d'une colonne dynamique : libellé, type)
--    + eleve_champs_valeurs (eleve_id, champ_id, valeur texte). C'est le pattern
--    le plus flexible pour des colonnes arbitraires ajoutées par l'utilisateur
--    (ex: "PAI", "Contact parent") sans jamais faire d'ALTER TABLE. Le type du
--    champ (texte, nombre, date, booléen) est indicatif pour l'UI (choix du
--    widget de saisie) ; la valeur est toujours stockée en texte pour rester
--    générique, et reformatée à l'affichage selon `type`.
-- ============================================================================

CREATE TABLE classes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  annee_scolaire_id INT NOT NULL,
  nom VARCHAR(100) NOT NULL DEFAULT 'Ma classe',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (annee_scolaire_id) REFERENCES annees_scolaires(id) ON DELETE CASCADE
);

CREATE TABLE classe_niveaux (
  id INT PRIMARY KEY AUTO_INCREMENT,
  classe_id INT NOT NULL,
  niveau_id INT NOT NULL,
  FOREIGN KEY (classe_id) REFERENCES classes(id) ON DELETE CASCADE,
  FOREIGN KEY (niveau_id) REFERENCES niveaux(id),
  UNIQUE KEY uniq_classe_niveau (classe_id, niveau_id)
);

CREATE TABLE eleves (
  id INT PRIMARY KEY AUTO_INCREMENT,
  classe_id INT NOT NULL,
  nom VARCHAR(100) NOT NULL,
  prenom VARCHAR(100) NOT NULL,
  sexe ENUM('F', 'M', 'Autre') DEFAULT NULL,
  date_naissance DATE DEFAULT NULL,
  niveau_id INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (classe_id) REFERENCES classes(id) ON DELETE CASCADE,
  FOREIGN KEY (niveau_id) REFERENCES niveaux(id)
);

CREATE TABLE champs_personnalises (
  id INT PRIMARY KEY AUTO_INCREMENT,
  classe_id INT NOT NULL,
  libelle VARCHAR(100) NOT NULL,
  type ENUM('texte', 'nombre', 'date', 'booleen') NOT NULL DEFAULT 'texte',
  ordre INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (classe_id) REFERENCES classes(id) ON DELETE CASCADE,
  UNIQUE KEY uniq_classe_champ_libelle (classe_id, libelle)
);

CREATE TABLE eleve_champs_valeurs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  eleve_id INT NOT NULL,
  champ_id INT NOT NULL,
  valeur TEXT,
  FOREIGN KEY (eleve_id) REFERENCES eleves(id) ON DELETE CASCADE,
  FOREIGN KEY (champ_id) REFERENCES champs_personnalises(id) ON DELETE CASCADE,
  UNIQUE KEY uniq_eleve_champ (eleve_id, champ_id)
);

-- ============================================================================
-- Registre d'appel — suivi de présence/absence, jour par jour, par demi-journée.
-- ============================================================================
--
-- Décisions de modélisation :
--
-- 1. Une "session" d'appel = (classe_id, date, creneau) où creneau vaut 'matin'
--    ou 'apres_midi' — deux demi-journées indépendantes par jour de classe,
--    reproduisant le fonctionnement observé (comportement, pas code) de la
--    fonctionnalité équivalente d'un logiciel du marché : chaque demi-journée a
--    son propre décompte présents/absents. La ligne `appels` est créée à la
--    demande (lazy), pas pré-générée pour tout le calendrier.
--
-- 2. appel_eleves : une ligne par élève par session d'appel. `present` est le
--    statut principal (TINYINT, 1 = présent). Un élève sans ligne pour une
--    session donnée est traité côté application comme "non pointé" = absent
--    par défaut (cohérent avec l'observation : le décompte "Absent(s)" inclut
--    tout élève non explicitement marqué présent). Les lignes ne sont donc
--    créées que lorsqu'une action est effectuée sur un élève (éviter 25 lignes
--    vides par demi-journée par défaut).
--
-- 3. type_absence : NULL si présent ou absence non qualifiée. Trois valeurs
--    qualifiées ('justifiee', 'medicale', 'injustifiee'), indépendantes du
--    champ `present` (on peut renseigner un type d'absence sans que
--    l'UI l'exige, mais il n'a de sens que pour un élève absent).
--
-- 4. retard_minutes : NULL = pas de retard. 0 accepté ("à l'heure" explicite,
--    rare) sinon nombre de minutes de retard, saisi via des raccourcis (5, 10,
--    15, 30, 60) ou une valeur libre côté UI — stocké comme entier simple.
--
-- 5. exclu_stats : TINYINT — équivalent de "ne pas compter" : exclut cet élève
--    de cette demi-journée du calcul des statistiques (ex : sortie scolaire),
--    sans supprimer la ligne (on garde une trace du commentaire éventuel).
--
-- 6. La fonctionnalité "cantine" observée sur l'outil de référence n'est pas
--    reprise (hors périmètre demandé).
-- ============================================================================

CREATE TABLE appels (
  id INT PRIMARY KEY AUTO_INCREMENT,
  classe_id INT NOT NULL,
  date DATE NOT NULL,
  creneau ENUM('matin', 'apres_midi') NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (classe_id) REFERENCES classes(id) ON DELETE CASCADE,
  UNIQUE KEY uniq_appel_classe_date_creneau (classe_id, date, creneau)
);

CREATE TABLE appel_eleves (
  id INT PRIMARY KEY AUTO_INCREMENT,
  appel_id INT NOT NULL,
  eleve_id INT NOT NULL,
  present TINYINT(1) NOT NULL DEFAULT 1,
  type_absence ENUM('justifiee', 'medicale', 'injustifiee') DEFAULT NULL,
  retard_minutes INT DEFAULT NULL,
  commentaire TEXT,
  exclu_stats TINYINT(1) NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (appel_id) REFERENCES appels(id) ON DELETE CASCADE,
  FOREIGN KEY (eleve_id) REFERENCES eleves(id) ON DELETE CASCADE,
  UNIQUE KEY uniq_appel_eleve (appel_id, eleve_id)
);

-- Volumes horaires hebdomadaires officiels (BOEN) par matière et cycle,
-- éditables dans Réglages. Sert de repère informatif pour le panneau
-- "Répartition horaire de la semaine" du cahier journal.
CREATE TABLE grilles_horaires_bo (
  id INT PRIMARY KEY AUTO_INCREMENT,
  cycle_id INT NOT NULL,
  matiere_nom VARCHAR(100) NOT NULL,
  minutes_semaine INT NOT NULL,
  FOREIGN KEY (cycle_id) REFERENCES cycles(id) ON DELETE CASCADE,
  UNIQUE KEY uniq_cycle_matiere (cycle_id, matiere_nom)
);


-- ============================================================================
-- Emploi du temps — grille hebdomadaire éditable par période (phase 1).
-- ============================================================================
--
-- Décisions de modélisation :
--
-- 1. `periode_id` obligatoire : chaque grille est nommée et rattachée à une
--    période scolaire (table `periodes` déjà existante) — reproduit le
--    fonctionnement observé chez Teetsh ("Emploi du temps P1", "P2"...) sans
--    créer de nouvelle notion de période.
--
-- 2. `type` détermine la source de couleur : `matiere` → couleur héritée de
--    `matieres.couleur_bandeau`/`couleur_texte` ; `rituel`/`pause` → couleur
--    héritée de `etiquettes` (déjà utilisée pour ce rôle dans
--    `seances.etiquette_id`). Un créneau n'a jamais de couleur en propre.
--
-- 3. `titre` optionnel : si vide et `type = matiere`, l'UI affiche
--    `matieres.nom`. Permet de personnaliser un intitulé ("Dictée", "Calcul
--    mental") sans créer une matière dédiée, comme observé chez Teetsh où ces
--    blocs colorés en `matiere_id` distinct du libellé affiché.
--
-- 4. `exclu_volume_horaire` : indicatif seulement dans cette phase (aucun
--    panneau de calcul ne l'exploite encore) — le champ existe pour ne pas
--    nécessiter de migration quand la répartition horaire sera ajoutée (phase
--    ultérieure).
--
-- 5. Pas de champ couleur propre, pas de table `ateliers_edt` : le "atelier
--    divise le temps par nb d'ateliers" observé chez Teetsh n'est pas repris —
--    la notion d'atelier existe déjà côté cahier journal (table `ateliers`,
--    liée à `seances`) avec un sens différent ; mélanger les deux ajouterait
--    de la confusion sans besoin exprimé.
--
-- 6. `atelier` et `preparer_seance_cahier_journal` (ajoutés après coup, pour
--    coller au formulaire de créneau de Teetsh) : simples cases à cocher
--    stockées pour fidélité de formulaire, sans logique métier branchée
--    dessus dans cette phase (pas de division du volume horaire par nombre
--    d'ateliers, pas de génération de séance dans le cahier journal — cf.
--    point 5 et "transfert cahier journal" en hors périmètre phase 1 plus
--    haut). Comportement à définir si/quand ces fonctionnalités sont
--    développées.
-- ============================================================================

CREATE TABLE emplois_du_temps (
  id INT PRIMARY KEY AUTO_INCREMENT,
  classe_id INT NOT NULL,
  periode_id INT NOT NULL,
  titre VARCHAR(100) NOT NULL,
  heure_debut TIME NOT NULL DEFAULT '08:00',
  heure_fin TIME NOT NULL DEFAULT '16:00',
  jours_actifs SET('lun','mar','mer','jeu','ven','sam','dim') NOT NULL DEFAULT 'lun,mar,jeu,ven',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (classe_id) REFERENCES classes(id) ON DELETE CASCADE,
  FOREIGN KEY (periode_id) REFERENCES periodes(id) ON DELETE CASCADE
);

CREATE TABLE creneaux_edt (
  id INT PRIMARY KEY AUTO_INCREMENT,
  emploi_du_temps_id INT NOT NULL,
  jour ENUM('lun','mar','mer','jeu','ven','sam','dim') NOT NULL,
  heure_debut TIME NOT NULL,
  heure_fin TIME NOT NULL,
  type ENUM('matiere', 'rituel', 'pause') NOT NULL DEFAULT 'matiere',
  titre VARCHAR(200),
  matiere_id INT,
  etiquette_id INT,
  exclu_volume_horaire TINYINT(1) NOT NULL DEFAULT 0,
  atelier TINYINT(1) NOT NULL DEFAULT 0,
  preparer_seance_cahier_journal TINYINT(1) NOT NULL DEFAULT 0,
  ordre INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (emploi_du_temps_id) REFERENCES emplois_du_temps(id) ON DELETE CASCADE,
  FOREIGN KEY (matiere_id) REFERENCES matieres(id),
  FOREIGN KEY (etiquette_id) REFERENCES etiquettes(id)
);

-- Répartition horaire d'un créneau de type 'rituel' entre plusieurs matières
-- (ex : un rituel de 20min = 10min Français + 10min Mathématiques), pour le
-- calcul du volume horaire hebdomadaire par matière. N'affecte jamais le
-- rendu du bloc dans la grille (couleur/libellé restent pilotés par
-- `etiquette_id` sur `creneaux_edt`) — donnée purement additive pour un
-- futur panneau de répartition horaire côté emploi du temps. La somme des
-- `minutes` doit correspondre à la durée du créneau (heure_fin - heure_debut),
-- contrôlé côté application, pas en base (pas de contrainte CHECK portable).
CREATE TABLE creneau_edt_repartitions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  creneau_id INT NOT NULL,
  matiere_id INT NOT NULL,
  minutes INT NOT NULL,
  FOREIGN KEY (creneau_id) REFERENCES creneaux_edt(id) ON DELETE CASCADE,
  FOREIGN KEY (matiere_id) REFERENCES matieres(id),
  UNIQUE KEY uniq_creneau_matiere (creneau_id, matiere_id)
);

-- ============================================================================
-- Programmations / progressions — grille "domaine × temps" par matière,
-- reproduisant le module observé chez Teetsh (menu Programmations).
-- ============================================================================
--
-- Décisions de modélisation :
--
-- 1. Une seule notion de document (`programmations`), avec un champ
--    `granularite` ENUM('periode','semaine') qui distingue les deux modes
--    observés chez Teetsh ("Une programmation" = détail par période,
--    "Une progression" = détail par semaine). Les deux partagent strictement
--    la même structure de grille (lignes = domaines, cellules = éléments) ;
--    seul l'axe temporel des colonnes change. Modéliser ça comme un simple
--    champ plutôt que deux tables parallèles évite de dupliquer tout le
--    reste (domaines, éléments, référentiel) pour une différence qui n'est
--    qu'un axe d'affichage.
--
-- 2. `classe_id` (pas `annee_scolaire_id` directement) : cohérent avec
--    emplois_du_temps qui se rattache à periodes -> annees_scolaires ; ici on
--    passe par classes (elle-même 1-1 avec une année scolaire, cf. commentaire
--    plus haut) pour rester dans le même style de rattachement que le reste
--    de l'app et permettre un futur filtre par niveau si besoin.
--
-- 3. `programmation_matieres` : table de liaison beaucoup plus qu'un simple
--    matiere_id sur programmations, car Teetsh permet explicitement plusieurs
--    matières dans UN même document ("Gérer les matières de ma
--    programmation"), chacune affichée comme un onglet avec sa propre grille
--    domaines × temps. Une matière donnée n'apparaît qu'une fois par
--    programmation (UNIQUE), l'ordre des onglets est géré par `ordre`.
--
-- 4. `programmation_domaines` : lignes de la grille, PAS un simple lien vers
--    la table `domaines` du référentiel BO. Chez Teetsh, ces lignes sont
--    librement nommées/réordonnées/recolorées par l'enseignant (ex: "Faire
--    société", regroupement transversal à plusieurs domaines BO) — donc
--    `nom` est du texte libre, avec `domaine_bo_id` optionnel (nullable) pour
--    les cas où la ligne a été créée à partir d'un domaine BO existant
--    (préremplissage pratique, sans empêcher le renommage libre ensuite).
--    `couleur_fond` : bandeau de couleur par ligne (observé chez Teetsh, une
--    couleur par domaine, indépendante des couleurs de matières).
--
-- 5. `programmation_elements` : les cartes de la grille. Le repérage temporel
--    utilise `periode_id` (toujours renseigné, référence `periodes`) +
--    `semaine_ordre` (NULL si granularite='periode', sinon numéro de semaine
--    1..nb_semaines RELATIF à cette période — pas un numéro de semaine
--    global sur l'année). Ce choix évite de matérialiser une table
--    `semaines` dédiée (36 lignes générées) alors que `periodes.nb_semaines`
--    permet déjà de calculer/valider les semaines disponibles à la volée,
--    et reste cohérent si les dates d'une période sont ajustées après coup
--    (une "Semaine 3 de P2" reste "Semaine 3 de P2" même si P2 est décalée).
--
-- 6. `contenu` : HTML riche (même éditeur contenteditable que les séances du
--    cahier journal, réutilisé tel quel côté JS/CSS) plutôt qu'un texte brut
--    + sous-liste structurée, pour permettre listes à puces/mise en forme
--    libre observées dans les cartes Teetsh ("Le harcèlement scolaire" +
--    sous-liste), sans multiplier les tables pour un besoin d'affichage.
--
-- 7. `programmation_element_competences` : liaison many-to-many élément <->
--    compétence du référentiel BO (même esprit que `seance_referentiel`),
--    pour permettre de rattacher une ou plusieurs compétences précises à une
--    carte (le "+" d'insertion depuis l'arbre Matière > Domaine > Compétence
--    observé dans la modale d'édition Teetsh), réutilisable plus tard pour
--    croiser programmation et cahier journal/carnet de notes. Le lien se
--    fait directement à `competence_id` (pas domaine/matiere séparés) : le
--    domaine et la matière d'une compétence se déduisent déjà de
--    `competences.domaine_id` -> `domaines.matiere_id`.
-- ============================================================================

CREATE TABLE programmations (
  id INT PRIMARY KEY AUTO_INCREMENT,
  classe_id INT NOT NULL,
  granularite ENUM('periode', 'semaine') NOT NULL DEFAULT 'periode',
  titre VARCHAR(150) NOT NULL,
  -- Niveau choisi à la création (ex CE2), indépendant de la matière : sert
  -- uniquement à pré-cibler la bonne branche du référentiel BO lors de
  -- l'insertion de compétences (une matière du référentiel existe en
  -- plusieurs déclinaisons par niveau ; sans ce champ, on ne pouvait deviner
  -- laquelle correspond réellement à la classe). NULL toléré : certaines
  -- programmations créées avant l'ajout de ce champ n'ont pas de niveau.
  niveau_id INT DEFAULT NULL,
  -- Période à laquelle la progression (granularite='semaine') est limitée :
  -- générer toutes les semaines de l'année scolaire d'un coup est peu
  -- lisible et rarement utile en pratique — l'enseignant choisit la période
  -- qui l'intéresse à la création. NULL pour une programmation (granularite
  -- ='periode'), qui continue de couvrir toutes les périodes de l'année.
  periode_id INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (classe_id) REFERENCES classes(id) ON DELETE CASCADE,
  FOREIGN KEY (niveau_id) REFERENCES niveaux(id),
  FOREIGN KEY (periode_id) REFERENCES periodes(id) ON DELETE CASCADE
);

CREATE TABLE programmation_matieres (
  id INT PRIMARY KEY AUTO_INCREMENT,
  programmation_id INT NOT NULL,
  matiere_id INT NOT NULL,
  ordre INT NOT NULL DEFAULT 0,
  FOREIGN KEY (programmation_id) REFERENCES programmations(id) ON DELETE CASCADE,
  FOREIGN KEY (matiere_id) REFERENCES matieres(id),
  UNIQUE KEY uniq_programmation_matiere (programmation_id, matiere_id)
);

CREATE TABLE programmation_domaines (
  id INT PRIMARY KEY AUTO_INCREMENT,
  programmation_matiere_id INT NOT NULL,
  nom VARCHAR(200) NOT NULL,
  domaine_bo_id INT DEFAULT NULL,
  couleur_fond VARCHAR(7) NOT NULL DEFAULT '#EDEFF2',
  ordre INT NOT NULL DEFAULT 0,
  FOREIGN KEY (programmation_matiere_id) REFERENCES programmation_matieres(id) ON DELETE CASCADE,
  FOREIGN KEY (domaine_bo_id) REFERENCES domaines(id)
);

CREATE TABLE programmation_elements (
  id INT PRIMARY KEY AUTO_INCREMENT,
  programmation_domaine_id INT NOT NULL,
  periode_id INT NOT NULL,
  semaine_ordre INT DEFAULT NULL,
  contenu TEXT,
  ordre INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (programmation_domaine_id) REFERENCES programmation_domaines(id) ON DELETE CASCADE,
  FOREIGN KEY (periode_id) REFERENCES periodes(id) ON DELETE CASCADE
);

CREATE TABLE programmation_element_competences (
  id INT PRIMARY KEY AUTO_INCREMENT,
  element_id INT NOT NULL,
  competence_id INT NOT NULL,
  FOREIGN KEY (element_id) REFERENCES programmation_elements(id) ON DELETE CASCADE,
  FOREIGN KEY (competence_id) REFERENCES competences(id) ON DELETE CASCADE,
  UNIQUE KEY uniq_element_competence (element_id, competence_id)
);

-- ============================================================================
-- Fiches de prep — séquences (regroupement de séances) et fiches de prep
-- (séances), reproduisant le module "Fiches de prep" observé chez Teetsh.
-- ============================================================================
--
-- Décisions de modélisation :
--
-- 1. Teetsh organise ce module sur 3 niveaux (Dossier > Séquence > Fiche de
--    prep/séance), plus une "fiche de prep unique" hors séquence. Un vrai
--    arbre de dossiers récursif (façon explorateur de fichiers) est un
--    surcroît de complexité pour un usage strictement personnel mono-
--    enseignant : on ne reprend que 2 niveaux utiles — `sequences`
--    (= dossier + séquence fusionnés, un regroupement nommé de séances,
--    optionnellement rattaché à une période pour le classement, comme
--    observé : "Période 1".."Période 4", "APC") et `fiches_prep`
--    (= la fiche de prep/séance elle-même). `sequence_id` est NULLABLE sur
--    `fiches_prep` pour couvrir le cas "fiche de prep unique" de Teetsh (une
--    fiche isolée, hors séquence).
--
-- 2. `classe_id` sur `sequences` uniquement (pas sur fiches_prep) : cohérent
--    avec programmations/emplois_du_temps, une fiche appartient à la classe
--    via sa séquence. Une fiche unique (sequence_id NULL) porte alors son
--    propre `classe_id` directement — d'où le champ dupliqué mais rendu
--    optionnel sur fiches_prep (NULL si rattachée à une séquence, auquel cas
--    on lit classe_id de la séquence parente).
--
-- 3. Niveau(x) de classe : Teetsh permet de cocher plusieurs niveaux
--    (TPS..3e) indépendamment de la classe réelle de l'enseignant (une fiche
--    peut être taguée CE2 ET CM1 même si l'enseignant n'a que du CE2). Table
--    de liaison many-to-many vers `niveaux` (référentiel BO), aussi bien sur
--    `sequences` que sur `fiches_prep` (les deux ont ce champ chez Teetsh).
--
-- 4. Référentiel BO (matière > domaine > compétence, cascade) : même table de
--    liaison que `seance_referentiel` dans son esprit (many-to-many vers
--    matieres/domaines/competences), déclinée ici en `sequence_referentiel`
--    et `fiche_prep_referentiel` (une ligne = une entrée ajoutée dans le
--    panneau "Éléments du référentiel", pas forcément les 3 niveaux remplis
--    — domaine/competence NULLABLE, seule matiere_id est obligatoire, comme
--    observé chez Teetsh où une matière peut être ajoutée seule).
--    RAPPEL (cf. CLAUDE.md) : le référentiel BO donne des id DISTINCTS par
--    niveau à la même matière/domaine conceptuel (ex Français CE2 et Français
--    CM1 ont des matiere_id différents) — toute présélection ou association
--    doit comparer par NOM, jamais par id brut, sous peine de résultats
--    incohérents en classe multi-niveaux.
--
-- 5. Objectifs : Teetsh permet plusieurs lignes d'objectif par fiche/séquence
--    (observé : jusqu'à 4 objectifs sur une même fiche). Table dédiée
--    `fiche_prep_objectifs` (texte libre + ordre), plutôt qu'un champ TEXT
--    unique, pour permettre l'ajout/suppression ligne par ligne comme dans
--    l'UI Teetsh (chaque objectif a sa propre icône/suppression).
--
-- 6. Matériels : même pattern que la table `materiels` déjà existante
--    (cahier journal), réutilisée telle quelle via un nouveau FK
--    `fiche_prep_id` nullable en plus de `seance_id`/`atelier_id` existants —
--    évite de dupliquer une table quasi identique (libellé + ordre).
--
-- 7. Éléments personnalisés (Organisation, Evaluation, Pré-requis, champs
--    libres) : observés chez Teetsh comme des paires libellé/valeur
--    ajoutables librement (l'enseignant peut ajouter un "Élément
--    personnalisé" avec un intitulé de son choix, en plus des 3 prédéfinis
--    Organisation/Evaluation/Pré-requis). Modélisé en EAV
--    (`fiche_prep_elements`, libelle + valeur TEXT + ordre), même esprit que
--    `champs_personnalises`/`eleve_champs_valeurs` déjà en place pour la
--    classe — un intitulé prédéfini n'est qu'une valeur de `libelle` comme
--    une autre, pas une colonne dédiée.
--
-- 8. Déroulement (phases) : `fiche_prep_phases`, une ligne = une phase
--    (Mise en situation, Recherche, Mise en commun...) avec titre libre,
--    durée en minutes (les puces 5/10/15/20/25/30/60 observées sont des
--    raccourcis UI, la valeur stockée reste un entier libre) et `contenu`
--    HTML riche (même éditeur contenteditable/richtext que les séances du
--    cahier journal et les éléments de programmation — réutilisation du
--    composant JS existant, pas de nouvel éditeur).
--
-- 9. Pas de table dédiée pour les "modèles" (templates Teetsh) ni pour le
--    "partage"/invitation (fonctionnalités multi-utilisateur, hors sujet
--    pour un outil strictement personnel, cf. CLAUDE.md) ni pour la
--    génération IA (explicitement hors périmètre de cette tâche).
-- ============================================================================

CREATE TABLE sequences (
  id INT PRIMARY KEY AUTO_INCREMENT,
  classe_id INT NOT NULL,
  titre VARCHAR(200) NOT NULL DEFAULT 'Séquence sans titre',
  periode_id INT DEFAULT NULL,
  objectif VARCHAR(500) DEFAULT NULL,
  prerequis VARCHAR(500) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (classe_id) REFERENCES classes(id) ON DELETE CASCADE,
  FOREIGN KEY (periode_id) REFERENCES periodes(id) ON DELETE SET NULL
);

CREATE TABLE sequence_niveaux (
  id INT PRIMARY KEY AUTO_INCREMENT,
  sequence_id INT NOT NULL,
  niveau_id INT NOT NULL,
  FOREIGN KEY (sequence_id) REFERENCES sequences(id) ON DELETE CASCADE,
  FOREIGN KEY (niveau_id) REFERENCES niveaux(id),
  UNIQUE KEY uniq_sequence_niveau (sequence_id, niveau_id)
);

CREATE TABLE sequence_referentiel (
  id INT PRIMARY KEY AUTO_INCREMENT,
  sequence_id INT NOT NULL,
  matiere_id INT NOT NULL,
  domaine_id INT DEFAULT NULL,
  competence_id INT DEFAULT NULL,
  FOREIGN KEY (sequence_id) REFERENCES sequences(id) ON DELETE CASCADE,
  FOREIGN KEY (matiere_id) REFERENCES matieres(id),
  FOREIGN KEY (domaine_id) REFERENCES domaines(id),
  FOREIGN KEY (competence_id) REFERENCES competences(id)
);

CREATE TABLE fiches_prep (
  id INT PRIMARY KEY AUTO_INCREMENT,
  sequence_id INT DEFAULT NULL,
  -- Renseigné uniquement pour une fiche unique (sequence_id NULL) ; sinon la
  -- classe se déduit de sequences.classe_id (cf. décision 2 ci-dessus).
  classe_id INT DEFAULT NULL,
  titre VARCHAR(200) NOT NULL DEFAULT 'Fiche sans titre',
  duree_minutes INT DEFAULT NULL,
  prerequis VARCHAR(500) DEFAULT NULL,
  ordre INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (sequence_id) REFERENCES sequences(id) ON DELETE CASCADE,
  FOREIGN KEY (classe_id) REFERENCES classes(id) ON DELETE CASCADE
);

CREATE TABLE fiche_prep_niveaux (
  id INT PRIMARY KEY AUTO_INCREMENT,
  fiche_prep_id INT NOT NULL,
  niveau_id INT NOT NULL,
  FOREIGN KEY (fiche_prep_id) REFERENCES fiches_prep(id) ON DELETE CASCADE,
  FOREIGN KEY (niveau_id) REFERENCES niveaux(id),
  UNIQUE KEY uniq_fiche_niveau (fiche_prep_id, niveau_id)
);

CREATE TABLE fiche_prep_referentiel (
  id INT PRIMARY KEY AUTO_INCREMENT,
  fiche_prep_id INT NOT NULL,
  matiere_id INT NOT NULL,
  domaine_id INT DEFAULT NULL,
  competence_id INT DEFAULT NULL,
  FOREIGN KEY (fiche_prep_id) REFERENCES fiches_prep(id) ON DELETE CASCADE,
  FOREIGN KEY (matiere_id) REFERENCES matieres(id),
  FOREIGN KEY (domaine_id) REFERENCES domaines(id),
  FOREIGN KEY (competence_id) REFERENCES competences(id)
);

CREATE TABLE fiche_prep_objectifs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  fiche_prep_id INT NOT NULL,
  libelle TEXT NOT NULL,
  ordre INT NOT NULL DEFAULT 0,
  FOREIGN KEY (fiche_prep_id) REFERENCES fiches_prep(id) ON DELETE CASCADE
);

-- Éléments personnalisés (Organisation, Evaluation, Pré-requis, ou tout
-- intitulé libre ajouté par l'enseignant) — cf. décision 7 ci-dessus.
CREATE TABLE fiche_prep_elements (
  id INT PRIMARY KEY AUTO_INCREMENT,
  fiche_prep_id INT NOT NULL,
  libelle VARCHAR(100) NOT NULL,
  valeur TEXT,
  ordre INT NOT NULL DEFAULT 0,
  FOREIGN KEY (fiche_prep_id) REFERENCES fiches_prep(id) ON DELETE CASCADE
);

-- Déroulement : une ligne = une phase (Mise en situation, Recherche...),
-- cf. décision 8 ci-dessus.
CREATE TABLE fiche_prep_phases (
  id INT PRIMARY KEY AUTO_INCREMENT,
  fiche_prep_id INT NOT NULL,
  titre VARCHAR(150) NOT NULL DEFAULT '',
  duree_minutes INT NOT NULL DEFAULT 10,
  contenu TEXT,
  ordre INT NOT NULL DEFAULT 0,
  FOREIGN KEY (fiche_prep_id) REFERENCES fiches_prep(id) ON DELETE CASCADE
);

-- Réutilisation de la table `materiels` existante (cahier journal) : ajout
-- d'un FK optionnel vers fiches_prep, cf. décision 6 ci-dessus.
ALTER TABLE materiels ADD COLUMN fiche_prep_id INT DEFAULT NULL AFTER atelier_id;
ALTER TABLE materiels ADD FOREIGN KEY (fiche_prep_id) REFERENCES fiches_prep(id) ON DELETE CASCADE;
