-- TimeCredit — Base Q&R (atelier de création/validation)
-- MySQL / MariaDB (o2switch). Encodage utf8mb4.
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS niveau (
  id           VARCHAR(4)  NOT NULL,          -- I..VII
  grade        VARCHAR(20) NOT NULL,          -- 6e..Terminale
  ordre        TINYINT     NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS matiere (
  id           VARCHAR(60) NOT NULL,          -- ex: math
  niveau_id    VARCHAR(4)  NOT NULL,
  nom          VARCHAR(160) NOT NULL,
  optional     TINYINT(1)  NOT NULL DEFAULT 0,
  PRIMARY KEY (id, niveau_id),
  FOREIGN KEY (niveau_id) REFERENCES niveau(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chapitre (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  pays           CHAR(2) NOT NULL DEFAULT 'FR',
  langue         CHAR(2) NOT NULL DEFAULT 'fr',
  niveau_id      VARCHAR(4)  NOT NULL,
  matiere_id     VARCHAR(60) NOT NULL,
  code           VARCHAR(80) NOT NULL,        -- ex: nombres_calculs_resolution_problemes
  nom            VARCHAR(200) NOT NULL,
  competence     TEXT,
  curriculum_ref VARCHAR(255),
  official       TINYINT(1) NOT NULL DEFAULT 1,
  ordre          INT NOT NULL DEFAULT 0,
  UNIQUE KEY uq_chap (pays, niveau_id, matiere_id, code),
  FOREIGN KEY (matiere_id, niveau_id) REFERENCES matiere(id, niveau_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS question (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  chapitre_id   INT NOT NULL,
  type          ENUM('qcm','numeric') NOT NULL DEFAULT 'qcm',
  enonce        TEXT NOT NULL,
  difficulte    TINYINT DEFAULT 1,
  explication   TEXT,
  -- champs specifiques 'numeric' (NULL si qcm)
  num_value     DECIMAL(18,6) NULL,
  num_tolerance DECIMAL(18,6) NULL DEFAULT 0,
  num_unit      VARCHAR(40) NULL,
  -- validation experte (le tag 'Validé')
  statut        ENUM('draft','in_review','validated','rejected') NOT NULL DEFAULT 'draft',
  valide_par    VARCHAR(120) NULL,
  valide_le     DATETIME NULL,
  note_qa       TEXT NULL,
  source        VARCHAR(120) NULL,            -- ia / humain / import
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (chapitre_id) REFERENCES chapitre(id) ON DELETE CASCADE,
  INDEX idx_chap (chapitre_id),
  INDEX idx_statut (statut)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS choix (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  question_id  INT NOT NULL,
  texte        VARCHAR(500) NOT NULL,
  est_correct  TINYINT(1) NOT NULL DEFAULT 0,
  position     TINYINT NOT NULL DEFAULT 0,    -- ordre canonique; l'app randomise a l'affichage
  FOREIGN KEY (question_id) REFERENCES question(id) ON DELETE CASCADE,
  INDEX idx_q (question_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
