-- Migration v3 (après diaporama):
-- - Ajout du numéro de téléphone
-- - Ajout des signalements (reports)
-- - Ajout du paramètre 'carousel_interval_seconds'

SET NAMES utf8mb4;

-- 1) Téléphone
ALTER TABLE users
  ADD COLUMN phone VARCHAR(30) NULL UNIQUE AFTER email;

-- 2) Paramètre diaporama (en secondes)
INSERT INTO settings (k, v)
VALUES ('carousel_interval_seconds', '5')
ON DUPLICATE KEY UPDATE v = VALUES(v);

-- 3) Table des signalements
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;
