-- SQL structure for Trilha (will be executed by install.php)
CREATE DATABASE IF NOT EXISTS trilha_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE trilha_db;

CREATE TABLE usuarios (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(120) NOT NULL,
  email VARCHAR(120) NOT NULL UNIQUE,
  senha VARCHAR(255) NOT NULL,
  role ENUM('admin','professor','aluno') NOT NULL DEFAULT 'professor',
  criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE series (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(80) NOT NULL
);

CREATE TABLE turmas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(120) NOT NULL,
  serie_id INT,
  professor_id INT,
  FOREIGN KEY (serie_id) REFERENCES series(id) ON DELETE SET NULL,
  FOREIGN KEY (professor_id) REFERENCES usuarios(id) ON DELETE SET NULL
);

CREATE TABLE trilhas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(150) NOT NULL,
  descricao TEXT,
  professor_id INT,
  ativo TINYINT DEFAULT 1,
  FOREIGN KEY (professor_id) REFERENCES usuarios(id) ON DELETE SET NULL
);

CREATE TABLE trilha_turma (
  id INT AUTO_INCREMENT PRIMARY KEY,
  trilha_id INT,
  turma_id INT,
  FOREIGN KEY (trilha_id) REFERENCES trilhas(id) ON DELETE CASCADE,
  FOREIGN KEY (turma_id) REFERENCES turmas(id) ON DELETE CASCADE
);

CREATE TABLE fases (
  id INT AUTO_INCREMENT PRIMARY KEY,
  trilha_id INT,
  titulo VARCHAR(150),
  conteudo TEXT,
  ordem INT DEFAULT 0,
  media_path VARCHAR(255) DEFAULT NULL,
  media_type VARCHAR(20) DEFAULT NULL,
  FOREIGN KEY (trilha_id) REFERENCES trilhas(id) ON DELETE CASCADE
);

-- sample series and user placeholders - installer will create admin user
INSERT INTO series (nome) VALUES ('1º Ano'), ('2º Ano'), ('3º Ano');