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

CREATE TABLE usuarios (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(120) NOT NULL,
  correo VARCHAR(180) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  rol ENUM('profesor','estudiante') NOT NULL,
  creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE actividades (
  id INT AUTO_INCREMENT PRIMARY KEY,
  profesor_id INT NOT NULL,
  titulo VARCHAR(180) NOT NULL,
  tema TEXT NOT NULL,
  tipo ENUM('nube','glosario') NOT NULL DEFAULT 'nube',
  codigo VARCHAR(12) NOT NULL UNIQUE,
  activa TINYINT(1) NOT NULL DEFAULT 1,
  creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (profesor_id) REFERENCES usuarios(id) ON DELETE CASCADE
);

CREATE TABLE aportes_nube (
  id INT AUTO_INCREMENT PRIMARY KEY,
  actividad_id INT NOT NULL,
  estudiante_id INT NOT NULL,
  texto VARCHAR(255) NOT NULL,
  creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (actividad_id) REFERENCES actividades(id) ON DELETE CASCADE,
  FOREIGN KEY (estudiante_id) REFERENCES usuarios(id) ON DELETE CASCADE
);

CREATE TABLE aportes_glosario (
  id INT AUTO_INCREMENT PRIMARY KEY,
  actividad_id INT NOT NULL,
  estudiante_id INT NOT NULL,
  titulo VARCHAR(180) NOT NULL,
  definicion TEXT NOT NULL,
  creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (actividad_id) REFERENCES actividades(id) ON DELETE CASCADE,
  FOREIGN KEY (estudiante_id) REFERENCES usuarios(id) ON DELETE CASCADE
);