-- TecnoLinguaSuite - Esquema base (versión 1.0)
-- Motor InnoDB, utf8mb4. Toda tabla con datos de cliente lleva organizacion_id.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------
-- Sistema
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sistema_config (
  clave VARCHAR(80) NOT NULL PRIMARY KEY,
  valor TEXT NULL,
  actualizado_en DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS modulos (
  codigo VARCHAR(40) NOT NULL PRIMARY KEY,
  nombre VARCHAR(120) NOT NULL,
  descripcion VARCHAR(255) NULL,
  grupo VARCHAR(60) NOT NULL DEFAULT 'General',
  es_nucleo TINYINT(1) NOT NULL DEFAULT 0,
  disponible TINYINT(1) NOT NULL DEFAULT 0,
  aplica_a VARCHAR(80) NOT NULL DEFAULT 'todos',
  orden INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permisos (
  codigo VARCHAR(80) NOT NULL PRIMARY KEY,
  modulo_codigo VARCHAR(40) NOT NULL,
  ambito ENUM('soporte','organizacion') NOT NULL DEFAULT 'organizacion',
  nombre VARCHAR(150) NOT NULL,
  orden INT NOT NULL DEFAULT 0,
  KEY idx_perm_modulo (modulo_codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Planes y organizaciones
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS planes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL,
  descripcion VARCHAR(255) NULL,
  max_estudiantes INT NOT NULL DEFAULT 0,
  max_sedes INT NOT NULL DEFAULT 0,
  max_usuarios INT NOT NULL DEFAULT 0,
  max_clases INT NOT NULL DEFAULT 0,
  max_cursos_propios INT NOT NULL DEFAULT 0,
  almacenamiento_mb INT NOT NULL DEFAULT 0,
  es_demo TINYINT(1) NOT NULL DEFAULT 0,
  dias_demo INT NOT NULL DEFAULT 0,
  precio_mensual DECIMAL(12,2) NOT NULL DEFAULT 0,
  precio_anual DECIMAL(12,2) NOT NULL DEFAULT 0,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  creado_en DATETIME NULL,
  actualizado_en DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS plan_modulos (
  plan_id INT UNSIGNED NOT NULL,
  modulo_codigo VARCHAR(40) NOT NULL,
  PRIMARY KEY (plan_id, modulo_codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS organizaciones (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(150) NOT NULL,
  nombre_corto VARCHAR(60) NULL,
  tipo ENUM('colegio','academia','instituto','universidad','libre') NOT NULL DEFAULT 'colegio',
  nit VARCHAR(30) NULL,
  slug VARCHAR(60) NOT NULL,
  dominio VARCHAR(150) NULL,
  logo VARCHAR(150) NULL,
  color_primario VARCHAR(7) NOT NULL DEFAULT '#1f4e79',
  color_secundario VARCHAR(7) NOT NULL DEFAULT '#f2a900',
  tema_id INT UNSIGNED NULL,
  portada VARCHAR(150) NULL,
  telefono VARCHAR(40) NULL,
  telefono2 VARCHAR(40) NULL,
  whatsapp VARCHAR(30) NULL,
  whatsapp_mensaje VARCHAR(255) NULL,
  whatsapp_boton TINYINT(1) NOT NULL DEFAULT 1,
  correo VARCHAR(150) NULL,
  correo2 VARCHAR(150) NULL,
  horario_atencion VARCHAR(255) NULL,
  direccion VARCHAR(255) NULL,
  municipio VARCHAR(80) NULL,
  departamento VARCHAR(80) NULL,
  pais VARCHAR(60) NOT NULL DEFAULT 'Guatemala',
  latitud DECIMAL(10,7) NULL,
  longitud DECIMAL(10,7) NULL,
  sitio_web VARCHAR(150) NULL,
  redes_json TEXT NULL,
  institucional_json TEXT NULL,
  bot_nombre VARCHAR(40) NOT NULL DEFAULT 'Lingo',
  bot_mascota VARCHAR(20) NOT NULL DEFAULT 'buho',
  bot_bienvenida VARCHAR(255) NULL,
  aprobar_porcentaje TINYINT UNSIGNED NOT NULL DEFAULT 80,
  ranking_activo TINYINT(1) NOT NULL DEFAULT 1,
  registro_codigo TINYINT(1) NOT NULL DEFAULT 1,
  plan_id INT UNSIGNED NULL,
  estado ENUM('activa','suspendida','cancelada') NOT NULL DEFAULT 'activa',
  es_demo TINYINT(1) NOT NULL DEFAULT 0,
  vence_en DATE NULL,
  limites_json TEXT NULL,
  config_json TEXT NULL,
  permisos_version INT UNSIGNED NOT NULL DEFAULT 1,
  creado_en DATETIME NULL,
  actualizado_en DATETIME NULL,
  UNIQUE KEY uq_org_slug (slug),
  KEY idx_org_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ajustes por organización sobre lo que da el plan (1 = forzar activo, 0 = forzar inactivo)
CREATE TABLE IF NOT EXISTS organizacion_modulos (
  organizacion_id INT UNSIGNED NOT NULL,
  modulo_codigo VARCHAR(40) NOT NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (organizacion_id, modulo_codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sedes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NOT NULL,
  nombre VARCHAR(120) NOT NULL,
  codigo VARCHAR(30) NULL,
  codigo_mineduc VARCHAR(40) NULL,
  direccion VARCHAR(255) NULL,
  municipio VARCHAR(80) NULL,
  departamento VARCHAR(80) NULL,
  telefono VARCHAR(40) NULL,
  telefono2 VARCHAR(40) NULL,
  whatsapp VARCHAR(30) NULL,
  correo VARCHAR(150) NULL,
  horario VARCHAR(255) NULL,
  latitud DECIMAL(10,7) NULL,
  longitud DECIMAL(10,7) NULL,
  director VARCHAR(120) NULL,
  es_principal TINYINT(1) NOT NULL DEFAULT 0,
  activa TINYINT(1) NOT NULL DEFAULT 1,
  mostrar_en_web TINYINT(1) NOT NULL DEFAULT 1,
  creado_en DATETIME NULL,
  actualizado_en DATETIME NULL,
  KEY idx_sede_org (organizacion_id, activa)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Usuarios, grupos y permisos
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS usuarios (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NULL,
  es_soporte TINYINT(1) NOT NULL DEFAULT 0,
  tipo ENUM('personal','encargado','estudiante') NOT NULL DEFAULT 'personal',
  nombre VARCHAR(80) NOT NULL,
  apellido VARCHAR(80) NOT NULL DEFAULT '',
  correo VARCHAR(150) NOT NULL,
  telefono VARCHAR(40) NULL,
  clave_hash VARCHAR(255) NOT NULL,
  codigo_autorizacion_hash VARCHAR(255) NULL,
  todas_sedes TINYINT(1) NOT NULL DEFAULT 0,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  debe_cambiar_clave TINYINT(1) NOT NULL DEFAULT 0,
  intentos_fallidos INT NOT NULL DEFAULT 0,
  bloqueado_hasta DATETIME NULL,
  ultimo_acceso DATETIME NULL,
  sesion_version INT UNSIGNED NOT NULL DEFAULT 1,
  creado_en DATETIME NULL,
  actualizado_en DATETIME NULL,
  UNIQUE KEY uq_usuario_correo (correo),
  KEY idx_usuario_org (organizacion_id, activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS grupos (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NULL,
  ambito ENUM('soporte','organizacion') NOT NULL DEFAULT 'organizacion',
  es_plantilla TINYINT(1) NOT NULL DEFAULT 0,
  nombre VARCHAR(100) NOT NULL,
  descripcion VARCHAR(255) NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  creado_en DATETIME NULL,
  actualizado_en DATETIME NULL,
  KEY idx_grupo_org (organizacion_id, ambito)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS grupo_permisos (
  grupo_id INT UNSIGNED NOT NULL,
  permiso_codigo VARCHAR(80) NOT NULL,
  PRIMARY KEY (grupo_id, permiso_codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS usuario_grupos (
  usuario_id INT UNSIGNED NOT NULL,
  grupo_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (usuario_id, grupo_id),
  KEY idx_ug_grupo (grupo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS usuario_sedes (
  usuario_id INT UNSIGNED NOT NULL,
  sede_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (usuario_id, sede_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Autorizaciones con código, notificaciones y bitácora
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS autorizaciones (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NULL,
  accion VARCHAR(60) NOT NULL,
  permiso_requerido VARCHAR(80) NOT NULL,
  referencia_id INT UNSIGNED NULL,
  descripcion VARCHAR(255) NOT NULL,
  motivo TEXT NOT NULL,
  datos_json TEXT NULL,
  estado ENUM('pendiente','aprobada','rechazada','fallida') NOT NULL DEFAULT 'pendiente',
  solicitante_id INT UNSIGNED NOT NULL,
  resolutor_id INT UNSIGNED NULL,
  comentario_resolucion VARCHAR(255) NULL,
  creado_en DATETIME NULL,
  resuelto_en DATETIME NULL,
  KEY idx_aut_org_estado (organizacion_id, estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notificaciones (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NULL,
  usuario_id INT UNSIGNED NOT NULL,
  titulo VARCHAR(150) NOT NULL,
  mensaje VARCHAR(500) NULL,
  enlace VARCHAR(255) NULL,
  leida TINYINT(1) NOT NULL DEFAULT 0,
  creado_en DATETIME NULL,
  KEY idx_notif_usuario (usuario_id, leida)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bitacora (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NULL,
  usuario_id INT UNSIGNED NULL,
  usuario_nombre VARCHAR(170) NULL,
  suplantado_por INT UNSIGNED NULL,
  modulo VARCHAR(40) NOT NULL,
  accion VARCHAR(60) NOT NULL,
  tabla VARCHAR(60) NULL,
  registro_id VARCHAR(40) NULL,
  descripcion VARCHAR(255) NULL,
  antes_json MEDIUMTEXT NULL,
  despues_json MEDIUMTEXT NULL,
  ip VARCHAR(45) NULL,
  agente VARCHAR(255) NULL,
  creado_en DATETIME NOT NULL,
  KEY idx_bit_org_fecha (organizacion_id, creado_en),
  KEY idx_bit_usuario (usuario_id, creado_en),
  KEY idx_bit_modulo (organizacion_id, modulo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS avisos (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  titulo VARCHAR(150) NOT NULL,
  mensaje TEXT NOT NULL,
  tipo ENUM('info','advertencia','critico') NOT NULL DEFAULT 'info',
  desde DATETIME NULL,
  hasta DATETIME NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  creado_en DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Pagos, tickets, errores, archivos y temas
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cuentas_pago (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  banco VARCHAR(80) NOT NULL,
  tipo VARCHAR(40) NOT NULL DEFAULT 'Monetaria',
  numero VARCHAR(60) NOT NULL,
  titular VARCHAR(150) NOT NULL,
  moneda VARCHAR(10) NOT NULL DEFAULT 'GTQ',
  instrucciones VARCHAR(255) NULL,
  activa TINYINT(1) NOT NULL DEFAULT 1,
  orden INT NOT NULL DEFAULT 1,
  creado_en DATETIME NULL,
  actualizado_en DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Comprobantes de pago enviados por las instituciones
CREATE TABLE IF NOT EXISTS pagos (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NOT NULL,
  plan_id INT UNSIGNED NULL,
  cuenta_id INT UNSIGNED NULL,
  monto DECIMAL(12,2) NOT NULL DEFAULT 0,
  fecha_pago DATE NOT NULL,
  referencia VARCHAR(80) NULL,
  comprobante VARCHAR(150) NULL,
  nota VARCHAR(255) NULL,
  estado ENUM('pendiente','aprobado','rechazado') NOT NULL DEFAULT 'pendiente',
  comentario_revision VARCHAR(255) NULL,
  periodo_desde DATE NULL,
  periodo_hasta DATE NULL,
  enviado_por INT UNSIGNED NULL,
  revisado_por INT UNSIGNED NULL,
  revisado_en DATETIME NULL,
  creado_en DATETIME NULL,
  KEY idx_pago_org (organizacion_id, estado),
  KEY idx_pago_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Permisos que soporte deja apagados para una organización
CREATE TABLE IF NOT EXISTS organizacion_permisos_bloqueados (
  organizacion_id INT UNSIGNED NOT NULL,
  permiso_codigo VARCHAR(80) NOT NULL,
  PRIMARY KEY (organizacion_id, permiso_codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tickets de soporte
CREATE TABLE IF NOT EXISTS tickets (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NOT NULL,
  usuario_id INT UNSIGNED NOT NULL,
  asunto VARCHAR(150) NOT NULL,
  categoria VARCHAR(40) NOT NULL DEFAULT 'consulta',
  prioridad ENUM('baja','normal','alta','urgente') NOT NULL DEFAULT 'normal',
  estado ENUM('abierto','en_proceso','respondido','cerrado') NOT NULL DEFAULT 'abierto',
  asignado_a INT UNSIGNED NULL,
  creado_en DATETIME NULL,
  actualizado_en DATETIME NULL,
  KEY idx_ticket_org (organizacion_id, estado),
  KEY idx_ticket_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ticket_mensajes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT UNSIGNED NOT NULL,
  usuario_id INT UNSIGNED NOT NULL,
  es_soporte TINYINT(1) NOT NULL DEFAULT 0,
  mensaje TEXT NOT NULL,
  adjunto VARCHAR(150) NULL,
  creado_en DATETIME NULL,
  KEY idx_tm_ticket (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Errores del sistema por organización
CREATE TABLE IF NOT EXISTS errores (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NULL,
  usuario_id INT UNSIGNED NULL,
  url VARCHAR(255) NULL,
  metodo VARCHAR(10) NULL,
  tipo VARCHAR(150) NULL,
  mensaje TEXT NULL,
  archivo VARCHAR(255) NULL,
  linea INT NULL,
  traza MEDIUMTEXT NULL,
  ip VARCHAR(45) NULL,
  revisado TINYINT(1) NOT NULL DEFAULT 0,
  creado_en DATETIME NOT NULL,
  KEY idx_err_org (organizacion_id, creado_en),
  KEY idx_err_revisado (revisado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Archivos subidos por la institución (imágenes de la página web, etc.)
CREATE TABLE IF NOT EXISTS archivos (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NOT NULL,
  nombre_original VARCHAR(200) NOT NULL,
  ruta VARCHAR(200) NOT NULL,
  mime VARCHAR(80) NOT NULL,
  tamano INT UNSIGNED NOT NULL DEFAULT 0,
  publico TINYINT(1) NOT NULL DEFAULT 1,
  subido_por INT UNSIGNED NULL,
  creado_en DATETIME NULL,
  KEY idx_arch_org (organizacion_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Página web pública
CREATE TABLE IF NOT EXISTS temas (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NULL,
  nombre VARCHAR(80) NOT NULL,
  colores_json TEXT NOT NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  orden INT NOT NULL DEFAULT 0,
  creado_en DATETIME NULL,
  actualizado_en DATETIME NULL,
  KEY idx_tema_org (organizacion_id, activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO temas (organizacion_id, nombre, colores_json, activo, orden, creado_en, actualizado_en) VALUES
(NULL, 'Azul institucional', '{"marca":"#1b2a41","acento":"#e8a33d","menu_fondo":"#1b2a41","menu_texto":"#ffffff","menu_activo":"#e8a33d","superior_fondo":"#ffffff","superior_texto":"#1d2321","fondo":"#f4f5f2","superficie":"#ffffff","texto":"#1d2321","boton":"#1b2a41","enlace":"#1b2a41","fuente":"sistema","radio":10,"menu_degradado":0}', 1, 10, NOW(), NOW()),
(NULL, 'Verde colegio', '{"marca":"#1e5631","acento":"#f2c94c","menu_fondo":"#174a2a","menu_texto":"#ffffff","menu_activo":"#f2c94c","superior_fondo":"#ffffff","superior_texto":"#1d2321","fondo":"#f3f6f1","superficie":"#ffffff","texto":"#1d2321","boton":"#1e5631","enlace":"#1e5631","fuente":"sistema","radio":10,"menu_degradado":1}', 1, 20, NOW(), NOW()),
(NULL, 'Vino y dorado', '{"marca":"#6d1a2c","acento":"#d4a72c","menu_fondo":"#5a1424","menu_texto":"#fff7ec","menu_activo":"#d4a72c","superior_fondo":"#ffffff","superior_texto":"#2a1a1d","fondo":"#f7f3f1","superficie":"#ffffff","texto":"#2a1a1d","boton":"#6d1a2c","enlace":"#6d1a2c","fuente":"clasica","radio":8,"menu_degradado":1}', 1, 30, NOW(), NOW()),
(NULL, 'Azul y amarillo', '{"marca":"#0b4f9c","acento":"#ffcc00","menu_fondo":"#0b4f9c","menu_texto":"#ffffff","menu_activo":"#ffcc00","superior_fondo":"#ffffff","superior_texto":"#14213d","fondo":"#f2f5fa","superficie":"#ffffff","texto":"#14213d","boton":"#0b4f9c","enlace":"#0b4f9c","fuente":"redondeada","radio":12,"menu_degradado":0}', 1, 40, NOW(), NOW()),
(NULL, 'Claro con menú blanco', '{"marca":"#2d5f8b","acento":"#2d5f8b","menu_fondo":"#ffffff","menu_texto":"#26313d","menu_activo":"#2d5f8b","superior_fondo":"#ffffff","superior_texto":"#26313d","fondo":"#f5f7fa","superficie":"#ffffff","texto":"#1f2933","boton":"#2d5f8b","enlace":"#2d5f8b","fuente":"moderna","radio":10,"menu_degradado":0}', 1, 50, NOW(), NOW()),
(NULL, 'Nocturno', '{"marca":"#111827","acento":"#38bdf8","menu_fondo":"#0f172a","menu_texto":"#e2e8f0","menu_activo":"#38bdf8","superior_fondo":"#1e293b","superior_texto":"#f1f5f9","fondo":"#eef1f5","superficie":"#ffffff","texto":"#0f172a","boton":"#0f766e","enlace":"#0e7490","fuente":"moderna","radio":12,"menu_degradado":1}', 1, 60, NOW(), NOW());


-- ---------------------------------------------------------------
-- Cursos y contenido (organizacion_id NULL = catálogo oficial de soporte)
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cursos (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NULL,
  nombre VARCHAR(120) NOT NULL,
  idioma VARCHAR(60) NOT NULL,
  codigo_voz VARCHAR(10) NOT NULL DEFAULT 'en-US',
  idioma_instruccion VARCHAR(10) NOT NULL DEFAULT 'es',
  descripcion VARCHAR(500) NULL,
  color VARCHAR(7) NOT NULL DEFAULT '#2d6cdf',
  icono VARCHAR(20) NOT NULL DEFAULT 'globo',
  estado ENUM('borrador','publicado','retirado') NOT NULL DEFAULT 'borrador',
  orden INT NOT NULL DEFAULT 0,
  creado_por INT UNSIGNED NULL,
  creado_en DATETIME NULL,
  actualizado_en DATETIME NULL,
  KEY idx_curso_org (organizacion_id, estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS org_cursos (
  organizacion_id INT UNSIGNED NOT NULL,
  curso_id INT UNSIGNED NOT NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (organizacion_id, curso_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS etapas (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  curso_id INT UNSIGNED NOT NULL,
  numero TINYINT UNSIGNED NOT NULL,
  nombre VARCHAR(100) NOT NULL,
  descripcion VARCHAR(255) NULL,
  KEY idx_etapa_curso (curso_id, numero)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS niveles (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  curso_id INT UNSIGNED NOT NULL,
  etapa_id INT UNSIGNED NULL,
  numero SMALLINT UNSIGNED NOT NULL,
  titulo VARCHAR(150) NOT NULL,
  objetivo VARCHAR(255) NULL,
  gramatica VARCHAR(255) NULL,
  juego_principal VARCHAR(30) NULL,
  es_reto_etapa TINYINT(1) NOT NULL DEFAULT 0,
  estado ENUM('borrador','publicado') NOT NULL DEFAULT 'borrador',
  creado_en DATETIME NULL,
  actualizado_en DATETIME NULL,
  UNIQUE KEY uq_nivel (curso_id, numero)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Banco de contenido de cada nivel: el motor arma los juegos a partir de aquí
CREATE TABLE IF NOT EXISTS contenidos (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NULL,
  curso_id INT UNSIGNED NOT NULL,
  nivel_id INT UNSIGNED NOT NULL,
  tipo ENUM('vocabulario','frase','regla','lectura','pregunta','dato') NOT NULL,
  texto VARCHAR(500) NOT NULL,
  traduccion VARCHAR(500) NULL,
  categoria VARCHAR(60) NULL,
  emoji VARCHAR(16) NULL,
  extra_json TEXT NULL,
  habilidad ENUM('vocabulario','gramatica','lectura','escucha','escritura','pronunciacion') NOT NULL DEFAULT 'vocabulario',
  dificultad TINYINT UNSIGNED NOT NULL DEFAULT 1,
  franjas VARCHAR(10) NOT NULL DEFAULT '1,2,3,4',
  imagen VARCHAR(150) NULL,
  audio VARCHAR(150) NULL,
  estado ENUM('borrador','publicado','retirado') NOT NULL DEFAULT 'publicado',
  version INT UNSIGNED NOT NULL DEFAULT 1,
  origen ENUM('editor','importacion','documento','semilla') NOT NULL DEFAULT 'editor',
  importacion_id INT UNSIGNED NULL,
  revisado_por INT UNSIGNED NULL,
  revisado_en DATETIME NULL,
  creado_en DATETIME NULL,
  actualizado_en DATETIME NULL,
  KEY idx_cont_nivel (nivel_id, tipo, estado),
  KEY idx_cont_curso (curso_id, estado),
  KEY idx_cont_import (importacion_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contenido_cambios (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  contenido_id INT UNSIGNED NOT NULL,
  usuario_id INT UNSIGNED NULL,
  motivo VARCHAR(60) NOT NULL,
  nota VARCHAR(255) NULL,
  antes_json TEXT NULL,
  creado_en DATETIME NOT NULL,
  KEY idx_cc_cont (contenido_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS importaciones (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NULL,
  curso_id INT UNSIGNED NOT NULL,
  archivo VARCHAR(200) NOT NULL,
  tipo VARCHAR(20) NOT NULL,
  filas_ok INT NOT NULL DEFAULT 0,
  filas_error INT NOT NULL DEFAULT 0,
  errores_json MEDIUMTEXT NULL,
  usuario_id INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL,
  KEY idx_imp_curso (curso_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bot_frases (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NULL,
  situacion VARCHAR(30) NOT NULL,
  franja TINYINT UNSIGNED NOT NULL DEFAULT 0,
  texto VARCHAR(255) NOT NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  creado_en DATETIME NULL,
  KEY idx_bf (situacion, franja)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Estudiantes, clases y padres
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS estudiantes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NOT NULL,
  usuario_id INT UNSIGNED NULL,
  nombres VARCHAR(100) NOT NULL,
  apellidos VARCHAR(100) NOT NULL DEFAULT '',
  fecha_nacimiento DATE NULL,
  apodo VARCHAR(30) NULL,
  avatar VARCHAR(20) NOT NULL DEFAULT 'zorro',
  avatar_color VARCHAR(7) NOT NULL DEFAULT '#2d6cdf',
  franja_fija TINYINT UNSIGNED NULL,
  xp INT UNSIGNED NOT NULL DEFAULT 0,
  monedas INT UNSIGNED NOT NULL DEFAULT 0,
  racha INT UNSIGNED NOT NULL DEFAULT 0,
  racha_max INT UNSIGNED NOT NULL DEFAULT 0,
  ultima_practica DATE NULL,
  protector_usado CHAR(8) NULL,
  limite_minutos SMALLINT UNSIGNED NULL,
  bienvenida_vista TINYINT(1) NOT NULL DEFAULT 0,
  estado ENUM('activo','inactivo') NOT NULL DEFAULT 'activo',
  creado_en DATETIME NULL,
  actualizado_en DATETIME NULL,
  KEY idx_est_org (organizacion_id, estado),
  KEY idx_est_usuario (usuario_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clases (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NOT NULL,
  sede_id INT UNSIGNED NULL,
  curso_id INT UNSIGNED NOT NULL,
  docente_id INT UNSIGNED NULL,
  nombre VARCHAR(100) NOT NULL,
  codigo CHAR(6) NOT NULL,
  franja TINYINT UNSIGNED NULL,
  nivel_inicio SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  acepta_registro TINYINT(1) NOT NULL DEFAULT 1,
  activa TINYINT(1) NOT NULL DEFAULT 1,
  creado_en DATETIME NULL,
  actualizado_en DATETIME NULL,
  UNIQUE KEY uq_clase_codigo (codigo),
  KEY idx_clase_org (organizacion_id, activa),
  KEY idx_clase_docente (organizacion_id, docente_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clase_estudiantes (
  clase_id INT UNSIGNED NOT NULL,
  estudiante_id INT UNSIGNED NOT NULL,
  organizacion_id INT UNSIGNED NOT NULL,
  unido_en DATETIME NULL,
  PRIMARY KEY (clase_id, estudiante_id),
  KEY idx_ce_est (estudiante_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS estudiante_padres (
  organizacion_id INT UNSIGNED NOT NULL,
  estudiante_id INT UNSIGNED NOT NULL,
  usuario_id INT UNSIGNED NOT NULL,
  parentesco VARCHAR(40) NOT NULL DEFAULT 'Encargado',
  PRIMARY KEY (estudiante_id, usuario_id),
  KEY idx_ep_usuario (usuario_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tareas (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NOT NULL,
  clase_id INT UNSIGNED NOT NULL,
  nivel_id INT UNSIGNED NOT NULL,
  titulo VARCHAR(150) NOT NULL,
  instrucciones VARCHAR(500) NULL,
  fecha_limite DATE NULL,
  creado_por INT UNSIGNED NULL,
  creado_en DATETIME NULL,
  actualizado_en DATETIME NULL,
  KEY idx_tarea_clase (organizacion_id, clase_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Progreso, intentos, repaso espaciado y bot
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS estudiante_cursos (
  organizacion_id INT UNSIGNED NOT NULL,
  estudiante_id INT UNSIGNED NOT NULL,
  curso_id INT UNSIGNED NOT NULL,
  nivel_actual SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  ultimo_acceso DATETIME NULL,
  PRIMARY KEY (estudiante_id, curso_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS nivel_progreso (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NOT NULL,
  estudiante_id INT UNSIGNED NOT NULL,
  nivel_id INT UNSIGNED NOT NULL,
  estrellas_json VARCHAR(100) NULL,
  mejor_reto TINYINT UNSIGNED NULL,
  intentos_reto SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  reto_espera_hasta DATETIME NULL,
  aprobado TINYINT(1) NOT NULL DEFAULT 0,
  aprobado_en DATETIME NULL,
  abierto_por INT UNSIGNED NULL,
  creado_en DATETIME NULL,
  actualizado_en DATETIME NULL,
  UNIQUE KEY uq_np (estudiante_id, nivel_id),
  KEY idx_np_org (organizacion_id, nivel_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sesiones (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NOT NULL,
  estudiante_id INT UNSIGNED NOT NULL,
  curso_id INT UNSIGNED NOT NULL,
  nivel_id INT UNSIGNED NULL,
  tipo ENUM('mision','repaso') NOT NULL DEFAULT 'mision',
  mision TINYINT UNSIGNED NULL,
  items_json MEDIUMTEXT NOT NULL,
  total SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  indice SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  aciertos SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  primeras SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  errores SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  xp SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  estado ENUM('en_curso','terminada','abandonada') NOT NULL DEFAULT 'en_curso',
  iniciada_en DATETIME NOT NULL,
  terminada_en DATETIME NULL,
  KEY idx_ses_est (organizacion_id, estudiante_id, estado),
  KEY idx_ses_nivel (organizacion_id, nivel_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS respuestas (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NOT NULL,
  estudiante_id INT UNSIGNED NOT NULL,
  sesion_id INT UNSIGNED NOT NULL,
  nivel_id INT UNSIGNED NULL,
  contenido_id INT UNSIGNED NULL,
  version INT UNSIGNED NOT NULL DEFAULT 1,
  actividad VARCHAR(20) NOT NULL,
  habilidad VARCHAR(20) NOT NULL,
  correcta TINYINT(1) NOT NULL,
  intento TINYINT UNSIGNED NOT NULL DEFAULT 1,
  respuesta VARCHAR(255) NULL,
  segundos SMALLINT UNSIGNED NULL,
  creado_en DATETIME NOT NULL,
  KEY idx_resp_cont (contenido_id, correcta),
  KEY idx_resp_est (organizacion_id, estudiante_id, creado_en),
  KEY idx_resp_hab (estudiante_id, nivel_id, habilidad)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS repasos (
  estudiante_id INT UNSIGNED NOT NULL,
  contenido_id INT UNSIGNED NOT NULL,
  organizacion_id INT UNSIGNED NOT NULL,
  curso_id INT UNSIGNED NOT NULL,
  caja TINYINT UNSIGNED NOT NULL DEFAULT 1,
  proxima DATE NOT NULL,
  aciertos SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  fallos SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  actualizado_en DATETIME NULL,
  PRIMARY KEY (estudiante_id, contenido_id),
  KEY idx_rep_proxima (estudiante_id, curso_id, proxima)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dominio (
  estudiante_id INT UNSIGNED NOT NULL,
  nivel_id INT UNSIGNED NOT NULL,
  habilidad VARCHAR(20) NOT NULL,
  organizacion_id INT UNSIGNED NOT NULL,
  porcentaje TINYINT UNSIGNED NOT NULL DEFAULT 0,
  intentos SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  actualizado_en DATETIME NULL,
  PRIMARY KEY (estudiante_id, nivel_id, habilidad)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS insignias_estudiante (
  estudiante_id INT UNSIGNED NOT NULL,
  codigo VARCHAR(30) NOT NULL,
  organizacion_id INT UNSIGNED NOT NULL,
  ganada_en DATETIME NOT NULL,
  PRIMARY KEY (estudiante_id, codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS datos_vistos (
  estudiante_id INT UNSIGNED NOT NULL,
  contenido_id INT UNSIGNED NOT NULL,
  organizacion_id INT UNSIGNED NOT NULL,
  visto_en DATETIME NOT NULL,
  PRIMARY KEY (estudiante_id, contenido_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS actividad_diaria (
  estudiante_id INT UNSIGNED NOT NULL,
  fecha DATE NOT NULL,
  organizacion_id INT UNSIGNED NOT NULL,
  segundos INT UNSIGNED NOT NULL DEFAULT 0,
  respuestas SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  xp SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (estudiante_id, fecha),
  KEY idx_ad_org (organizacion_id, fecha)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bot_decisiones (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  organizacion_id INT UNSIGNED NOT NULL,
  estudiante_id INT UNSIGNED NOT NULL,
  sesion_id INT UNSIGNED NULL,
  regla VARCHAR(40) NOT NULL,
  detalle VARCHAR(255) NULL,
  creado_en DATETIME NOT NULL,
  KEY idx_bd_est (organizacion_id, estudiante_id, creado_en)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Recomendaciones del asistente que soporte ya revisó o descartó
CREATE TABLE IF NOT EXISTS asistente_revisados (
  contenido_id INT UNSIGNED NOT NULL,
  organizacion_id INT UNSIGNED NOT NULL DEFAULT 0,
  decision ENUM('descartado','corregido') NOT NULL,
  usuario_id INT UNSIGNED NULL,
  respuestas_al_revisar INT UNSIGNED NOT NULL DEFAULT 0,
  creado_en DATETIME NOT NULL,
  PRIMARY KEY (contenido_id, organizacion_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;