-- Quiz CM - Schéma MySQL (v2)
-- Importez ce fichier via phpMyAdmin (cPanel Namecheap) après création de la base.
-- Cette version ajoute la gestion dynamique des Classes, Filières (Subjects) et des Paramètres (timer).

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS classes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE,
  -- ordre d'affichage dans l'application (1 = premier)
  sort_order INT NOT NULL DEFAULT 1,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subjects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE,
  sort_order INT NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  k VARCHAR(80) PRIMARY KEY,
  v VARCHAR(255) NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Paramètres par défaut
INSERT INTO settings (k, v)
VALUES
  ('quiz_time_seconds', '20'),
  ('carousel_interval_seconds', '5'),
  ('default_points', '5'),
  -- Ads (AdMob)
  ('ads_enabled', '0'),
  ('banner_enabled', '0'),
  ('interstitial_enabled', '0'),
  ('admob_banner_unit_id', ''),
  ('admob_interstitial_unit_id', ''),
  -- 1 = chaque clic sur une filière
  ('interstitial_every', '1'),
  -- délai mini entre 2 interstitiels (secondes)
  ('interstitial_cooldown_seconds', '30'),
  -- conformité "enfants"
  ('ads_child_directed', '1'),
  ('ads_non_personalized', '1'),
  -- Consentement (Google UMP). Si activé, l'app affiche le formulaire de consentement si nécessaire.
  ('ads_consent_enabled', '1')
ON DUPLICATE KEY UPDATE v = VALUES(v);

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  phone VARCHAR(30) NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(120) NOT NULL,
  class_id INT NOT NULL,
  points INT NOT NULL DEFAULT 0,
  role ENUM('student','admin') NOT NULL DEFAULT 'student',
  is_blocked TINYINT(1) NOT NULL DEFAULT 0,
  token VARCHAR(100) NULL,
  token_created_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_role_points (role, points),
  INDEX idx_token (token),
  INDEX idx_class_id (class_id),
  CONSTRAINT fk_users_class FOREIGN KEY (class_id) REFERENCES classes(id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS questions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  class_id INT NOT NULL,
  subject_id INT NOT NULL,
  question TEXT NOT NULL,
  option_a TEXT NOT NULL,
  option_b TEXT NOT NULL,
  option_c TEXT NOT NULL,
  option_d TEXT NOT NULL,
  correct_option CHAR(1) NOT NULL,
  explanation TEXT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_class_subject (class_id, subject_id),
  CONSTRAINT fk_questions_class FOREIGN KEY (class_id) REFERENCES classes(id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_questions_subject FOREIGN KEY (subject_id) REFERENCES subjects(id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Classes et filières par défaut (vous pouvez les gérer depuis /admin)
INSERT IGNORE INTO classes (name, sort_order) VALUES
  ('CM1', 1),('CM2', 2),('6ème', 3),('5ème', 4),('4ème', 5),('3ème', 6),('Seconde', 7),('Première', 8),('Terminale', 9);

INSERT IGNORE INTO subjects (name) VALUES
  ('SVT'),('Histoire'),('Français'),('Anglais'),('Géographie'),('Physique');

-- Diaporama (3 images affichées sur l'accueil de l'application)
-- Les images sont stockées dans /uploads/carousel/ (racine du site) et gérées via /admin/carousel.php
CREATE TABLE IF NOT EXISTS carousel_images (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(120) NULL,
  image_path VARCHAR(255) NOT NULL,
  link_url VARCHAR(255) NULL,
  is_clickable TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_active_order (is_active, sort_order, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- Signalements (les utilisateurs peuvent signaler une question depuis l'application)
CREATE TABLE IF NOT EXISTS reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  question_id INT NOT NULL,
  class_id INT NOT NULL,
  subject_id INT NOT NULL,
  question_text TEXT NOT NULL,
  message TEXT NOT NULL,
  is_resolved TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_resolved_date (is_resolved, created_at),
  INDEX idx_user (user_id),
  CONSTRAINT fk_reports_user FOREIGN KEY (user_id) REFERENCES users(id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_reports_question FOREIGN KEY (question_id) REFERENCES questions(id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_reports_class FOREIGN KEY (class_id) REFERENCES classes(id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_reports_subject FOREIGN KEY (subject_id) REFERENCES subjects(id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
