-- =====================================================================
-- Casos en Video · Licenciatura en Abogado (CUCosta, UdeG)
-- 01_estructura.sql — Estructura de la base de datos
-- Compatible con MySQL 5.7+ y MariaDB 10.3+ (utf8mb4, InnoDB).
-- Importar en phpMyAdmin sobre una base de datos VACÍA.
-- Este archivo NO crea cuentas de usuario: la primera cuenta docente
-- se crea desde el asistente protegido (index.php?r=setup).
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE usuarios (
  id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  rol                 ENUM('docente','alumno') NOT NULL,
  es_admin            TINYINT(1) NOT NULL DEFAULT 0,
  codigo              VARCHAR(40)  NOT NULL,
  nombre              VARCHAR(150) NOT NULL,
  correo              VARCHAR(150) NULL,
  password_hash       VARCHAR(255) NOT NULL,
  debe_cambiar_clave  TINYINT(1) NOT NULL DEFAULT 1,
  activo              TINYINT(1) NOT NULL DEFAULT 1,
  intentos_fallidos   INT NOT NULL DEFAULT 0,
  bloqueado_hasta     DATETIME NULL,
  ultimo_acceso       DATETIME NULL,
  creado_en           DATETIME NOT NULL,
  actualizado_en      DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_usuarios_codigo (codigo),
  KEY ix_usuarios_rol (rol, activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE grupos (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  clave      VARCHAR(40)  NOT NULL,
  nombre     VARCHAR(150) NOT NULL,
  ciclo      VARCHAR(20)  NULL,
  activo     TINYINT(1) NOT NULL DEFAULT 1,
  creado_en  DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_grupos_clave (clave)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE grupo_alumno (
  grupo_id   INT UNSIGNED NOT NULL,
  alumno_id  INT UNSIGNED NOT NULL,
  PRIMARY KEY (grupo_id, alumno_id),
  KEY ix_ga_alumno (alumno_id),
  CONSTRAINT fk_ga_grupo  FOREIGN KEY (grupo_id)  REFERENCES grupos(id)   ON DELETE CASCADE,
  CONSTRAINT fk_ga_alumno FOREIGN KEY (alumno_id) REFERENCES usuarios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE casos (
  id                      INT UNSIGNED NOT NULL AUTO_INCREMENT,
  titulo                  VARCHAR(200) NOT NULL,
  objetivo                TEXT NULL,
  lugar                   VARCHAR(150) NULL,
  dificultad              TINYINT UNSIGNED NOT NULL DEFAULT 1,
  guion                   MEDIUMTEXT NULL,
  hechos_complementarios  MEDIUMTEXT NULL,
  notas_docente           MEDIUMTEXT NULL,
  video_tipo              ENUM('ninguno','mp4','youtube') NOT NULL DEFAULT 'ninguno',
  video_archivo           VARCHAR(190) NULL,
  youtube_id              VARCHAR(11)  NULL,
  subtitulos_archivo      VARCHAR(190) NULL,
  transcripcion           MEDIUMTEXT NULL,
  descripcion_accesible   TEXT NULL,
  creado_por              INT UNSIGNED NULL,
  creado_en               DATETIME NOT NULL,
  actualizado_en          DATETIME NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_casos_autor FOREIGN KEY (creado_por) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- datos / clave / rubrica almacenan JSON como texto (compatibilidad MariaDB/MySQL).
CREATE TABLE preguntas (
  id                    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  caso_id               INT UNSIGNED NOT NULL,
  materia               ENUM('penal','civil','familiar','laboral') NOT NULL,
  tipo                  ENUM('opcion_multiple','seleccion_multiple','relacion','respuesta_breve') NOT NULL,
  enunciado             TEXT NOT NULL,
  datos                 MEDIUMTEXT NULL,
  clave                 MEDIUMTEXT NULL,
  rubrica               MEDIUMTEXT NULL,
  puntos                DECIMAL(6,2) NOT NULL DEFAULT 1.00,
  permite_insuficiente  TINYINT(1) NOT NULL DEFAULT 0,
  explicacion           MEDIUMTEXT NULL,
  orden                 INT NOT NULL DEFAULT 0,
  activo                TINYINT(1) NOT NULL DEFAULT 1,
  creado_en             DATETIME NOT NULL,
  actualizado_en        DATETIME NULL,
  PRIMARY KEY (id),
  KEY ix_preguntas_caso (caso_id, orden),
  CONSTRAINT fk_preguntas_caso FOREIGN KEY (caso_id) REFERENCES casos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- estado: verificado = validado por docente o cotejado con fuente oficial;
--         cotejado   = texto cotejado en compilación actualizada, falta validación docente;
--         pendiente  = no verificado. Solo "verificado" se muestra al alumnado.
CREATE TABLE fundamentos (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  caso_id         INT UNSIGNED NOT NULL,
  ambito          ENUM('federal','jalisco','otro') NOT NULL DEFAULT 'jalisco',
  ordenamiento    VARCHAR(200) NOT NULL,
  articulo        VARCHAR(80)  NOT NULL,
  descripcion     TEXT NULL,
  enlace          VARCHAR(500) NULL,
  fecha_consulta  DATE NULL,
  estado          ENUM('verificado','cotejado','pendiente') NOT NULL DEFAULT 'pendiente',
  nota            TEXT NULL,
  orden           INT NOT NULL DEFAULT 0,
  validado_por    INT UNSIGNED NULL,
  validado_en     DATETIME NULL,
  PRIMARY KEY (id),
  KEY ix_fund_caso (caso_id, orden),
  CONSTRAINT fk_fund_caso FOREIGN KEY (caso_id) REFERENCES casos(id) ON DELETE CASCADE,
  CONSTRAINT fk_fund_validador FOREIGN KEY (validado_por) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE actividades (
  id                        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  titulo                    VARCHAR(200) NOT NULL,
  caso_id                   INT UNSIGNED NOT NULL,
  instrucciones             TEXT NULL,
  estado                    ENUM('borrador','publicada','archivada') NOT NULL DEFAULT 'borrador',
  abre_en                   DATETIME NULL,
  cierra_en                 DATETIME NULL,
  duracion_min              INT UNSIGNED NULL,
  intentos_max              TINYINT UNSIGNED NOT NULL DEFAULT 1,
  criterio_final            ENUM('mejor','ultimo') NOT NULL DEFAULT 'mejor',
  publicacion               ENUM('al_entregar','al_cierre','manual') NOT NULL DEFAULT 'al_cierre',
  mostrar_claves            TINYINT(1) NOT NULL DEFAULT 1,
  resultados_publicados_en  DATETIME NULL,
  publicada_en              DATETIME NULL,
  creado_por                INT UNSIGNED NULL,
  creado_en                 DATETIME NOT NULL,
  actualizado_en            DATETIME NULL,
  PRIMARY KEY (id),
  KEY ix_act_estado (estado),
  CONSTRAINT fk_act_caso  FOREIGN KEY (caso_id)    REFERENCES casos(id),
  CONSTRAINT fk_act_autor FOREIGN KEY (creado_por) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE actividad_grupo (
  actividad_id  INT UNSIGNED NOT NULL,
  grupo_id      INT UNSIGNED NOT NULL,
  PRIMARY KEY (actividad_id, grupo_id),
  KEY ix_ag_grupo (grupo_id),
  CONSTRAINT fk_ag_act   FOREIGN KEY (actividad_id) REFERENCES actividades(id) ON DELETE CASCADE,
  CONSTRAINT fk_ag_grupo FOREIGN KEY (grupo_id)     REFERENCES grupos(id)      ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE actividad_pregunta (
  actividad_id  INT UNSIGNED NOT NULL,
  pregunta_id   INT UNSIGNED NOT NULL,
  orden         INT NOT NULL DEFAULT 0,
  puntos        DECIMAL(6,2) NULL,
  PRIMARY KEY (actividad_id, pregunta_id),
  KEY ix_ap_preg (pregunta_id),
  CONSTRAINT fk_ap_act  FOREIGN KEY (actividad_id) REFERENCES actividades(id) ON DELETE CASCADE,
  CONSTRAINT fk_ap_preg FOREIGN KEY (pregunta_id)  REFERENCES preguntas(id)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- snapshot: copia congelada de preguntas, claves, puntos y rúbricas usada en el intento.
CREATE TABLE intentos (
  id                    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  actividad_id          INT UNSIGNED NOT NULL,
  alumno_id             INT UNSIGNED NOT NULL,
  numero                TINYINT UNSIGNED NOT NULL DEFAULT 1,
  estado                ENUM('en_curso','entregado') NOT NULL DEFAULT 'en_curso',
  iniciado_en           DATETIME NOT NULL,
  vence_en              DATETIME NULL,
  guardado_en           DATETIME NULL,
  entregado_en          DATETIME NULL,
  entregado_por         ENUM('alumno','tiempo','cierre') NULL,
  rev                   INT UNSIGNED NOT NULL DEFAULT 0,
  snapshot              MEDIUMTEXT NOT NULL,
  respuestas            MEDIUMTEXT NULL,
  resultado_auto        MEDIUMTEXT NULL,
  puntaje_auto          DECIMAL(8,2) NULL,
  puntaje_auto_max      DECIMAL(8,2) NULL,
  puntaje_revision      DECIMAL(8,2) NULL,
  puntaje_revision_max  DECIMAL(8,2) NULL,
  abiertas_pendientes   INT NOT NULL DEFAULT 0,
  estado_revision       ENUM('no_aplica','pendiente','revisado') NULL,
  calificacion_final    DECIMAL(5,2) NULL,
  observaciones         TEXT NULL,
  folio                 VARCHAR(40) NULL,
  huella                CHAR(64) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_intento (actividad_id, alumno_id, numero),
  KEY ix_int_alumno (alumno_id, estado),
  KEY ix_int_revision (estado_revision),
  CONSTRAINT fk_int_act    FOREIGN KEY (actividad_id) REFERENCES actividades(id),
  CONSTRAINT fk_int_alumno FOREIGN KEY (alumno_id)    REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE revisiones (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  intento_id      INT UNSIGNED NOT NULL,
  pregunta_clave  VARCHAR(20) NOT NULL,
  puntajes        MEDIUMTEXT NULL,
  puntos          DECIMAL(6,2) NOT NULL DEFAULT 0,
  maximo          DECIMAL(6,2) NOT NULL DEFAULT 0,
  comentario      TEXT NULL,
  automatico      TINYINT(1) NOT NULL DEFAULT 0,
  revisor_id      INT UNSIGNED NULL,
  revisado_en     DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_rev (intento_id, pregunta_clave),
  CONSTRAINT fk_rev_int     FOREIGN KEY (intento_id) REFERENCES intentos(id) ON DELETE CASCADE,
  CONSTRAINT fk_rev_revisor FOREIGN KEY (revisor_id) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE intentos_acceso (
  id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ip       VARCHAR(45) NOT NULL,
  codigo   VARCHAR(40) NULL,
  exito    TINYINT(1) NOT NULL DEFAULT 0,
  momento  DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_acceso_ip (ip, momento)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ajustes (
  clave  VARCHAR(60) NOT NULL,
  valor  TEXT NULL,
  PRIMARY KEY (clave)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE bitacora (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  usuario_id  INT UNSIGNED NULL,
  accion      VARCHAR(60) NOT NULL,
  detalle     TEXT NULL,
  ip          VARCHAR(45) NULL,
  momento     DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_bit_momento (momento)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Modo en vivo (auditorio con equipos)
-- ---------------------------------------------------------------------
CREATE TABLE equipos (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  conjunto     VARCHAR(80)  NOT NULL,
  nombre       VARCHAR(80)  NOT NULL,
  emoji        VARCHAR(16)  NOT NULL DEFAULT '⭐',
  color        TINYINT UNSIGNED NOT NULL DEFAULT 0,
  codigo       CHAR(6)      NOT NULL,
  integrantes  TEXT NULL,
  activo       TINYINT(1) NOT NULL DEFAULT 1,
  creado_en    DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_equipos_codigo (codigo),
  KEY ix_equipos_conjunto (conjunto, activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- fase: lobby → caso → pregunta → revelada → marcador → (pregunta …) → final
CREATE TABLE vivo_sesiones (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  titulo        VARCHAR(200) NOT NULL,
  caso_id       INT UNSIGNED NULL,
  conjunto      VARCHAR(80)  NOT NULL,
  token         CHAR(24)     NOT NULL,
  fase          ENUM('lobby','caso','pregunta','revelada','marcador','final') NOT NULL DEFAULT 'lobby',
  indice        INT NOT NULL DEFAULT -1,
  q_inicio_ms   BIGINT NULL,
  q_duracion    INT NULL,
  version       INT UNSIGNED NOT NULL DEFAULT 1,
  config        TEXT NULL,
  snapshot      MEDIUMTEXT NOT NULL,
  activa        TINYINT(1) NOT NULL DEFAULT 1,
  creado_por    INT UNSIGNED NULL,
  creado_en     DATETIME NOT NULL,
  iniciada_en   DATETIME NULL,
  terminada_en  DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_vivo_token (token),
  KEY ix_vivo_conjunto (conjunto, activa),
  CONSTRAINT fk_vivo_caso  FOREIGN KEY (caso_id)    REFERENCES casos(id)    ON DELETE SET NULL,
  CONSTRAINT fk_vivo_autor FOREIGN KEY (creado_por) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE vivo_equipos (
  sesion_id     INT UNSIGNED NOT NULL,
  equipo_id     INT UNSIGNED NOT NULL,
  unido_en      DATETIME NOT NULL,
  ultimo_en     DATETIME NULL,
  dispositivos  INT NOT NULL DEFAULT 0,
  racha         INT NOT NULL DEFAULT 0,
  PRIMARY KEY (sesion_id, equipo_id),
  KEY ix_ve_equipo (equipo_id),
  CONSTRAINT fk_ve_sesion FOREIGN KEY (sesion_id) REFERENCES vivo_sesiones(id) ON DELETE CASCADE,
  CONSTRAINT fk_ve_equipo FOREIGN KEY (equipo_id) REFERENCES equipos(id)       ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE vivo_respuestas (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  sesion_id   INT UNSIGNED NOT NULL,
  equipo_id   INT UNSIGNED NOT NULL,
  pregunta_k  VARCHAR(20) NOT NULL,
  respuesta   TEXT NULL,
  ms          INT UNSIGNED NOT NULL DEFAULT 0,
  fraccion    DECIMAL(5,4) NULL,
  puntos      INT NOT NULL DEFAULT 0,
  bonus       INT NOT NULL DEFAULT 0,
  extra       INT NOT NULL DEFAULT 0,
  evaluada    TINYINT(1) NOT NULL DEFAULT 0,
  creado_en   DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_vivo_resp (sesion_id, equipo_id, pregunta_k),
  KEY ix_vr_pregunta (sesion_id, pregunta_k),
  CONSTRAINT fk_vr_sesion FOREIGN KEY (sesion_id) REFERENCES vivo_sesiones(id) ON DELETE CASCADE,
  CONSTRAINT fk_vr_equipo FOREIGN KEY (equipo_id) REFERENCES equipos(id)       ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO ajustes (clave, valor) VALUES ('version_esquema', '2');

SET FOREIGN_KEY_CHECKS = 1;