-- ============================================================================
-- Sistema de Gestión de Ayudantías - Instituto
-- Versión: 1.0
-- Fecha: 15 de Febrero de 2026
-- Arquitectura: MVC con camelCase
-- ============================================================================

DROP DATABASE IF EXISTS institutoAyudantias;
CREATE DATABASE institutoAyudantias CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE institutoAyudantias;

-- ============================================================================
-- TABLA: usuarios
-- Descripción: Almacena docentes y administradores del sistema
-- ============================================================================
CREATE TABLE usuarios (
    idUsuario INT AUTO_INCREMENT PRIMARY KEY,
    rut VARCHAR(12) NOT NULL UNIQUE,
    nombre VARCHAR(100) NOT NULL,
    correo VARCHAR(100) NOT NULL UNIQUE,
    telefono VARCHAR(20),
    perfil ENUM('docente', 'administrador') NOT NULL DEFAULT 'docente',
    activo TINYINT(1) NOT NULL DEFAULT 1,
    fechaCreacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fechaActualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_rut_formato CHECK (rut REGEXP '^[0-9]{7,8}-[0-9Kk]$'),
    CONSTRAINT chk_correo_formato CHECK (correo REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'),
    INDEX idx_perfil (perfil),
    INDEX idx_activo (activo),
    INDEX idx_correo (correo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLA: laboratorios
-- Descripción: Catálogo de laboratorios disponibles
-- ============================================================================
CREATE TABLE laboratorios (
    idLaboratorio INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    numero VARCHAR(20) NOT NULL UNIQUE,
    capacidad INT,
    ubicacion VARCHAR(200),
    equipamiento TEXT,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    fechaCreacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fechaActualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_capacidad CHECK (capacidad > 0),
    INDEX idx_activo (activo),
    INDEX idx_numero (numero)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLA: asignaturas
-- Descripción: Catálogo de asignaturas del instituto
-- ============================================================================
CREATE TABLE asignaturas (
    idAsignatura INT AUTO_INCREMENT PRIMARY KEY,
    ncr VARCHAR(20) NOT NULL UNIQUE,
    nombre VARCHAR(150) NOT NULL,
    descripcion TEXT,
    semestre INT,
    carrera VARCHAR(100),
    activo TINYINT(1) NOT NULL DEFAULT 1,
    fechaCreacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fechaActualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_semestre CHECK (semestre BETWEEN 1 AND 12),
    INDEX idx_ncr (ncr),
    INDEX idx_activo (activo),
    INDEX idx_carrera (carrera)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLA: ayudantias
-- Descripción: Registro de ayudantías agendadas
-- ============================================================================
CREATE TABLE ayudantias (
    idAyudantia INT AUTO_INCREMENT PRIMARY KEY,
    idUsuario INT NOT NULL,
    idAsignatura INT NOT NULL,
    idLaboratorio INT NOT NULL,
    fechaInicio DATETIME NOT NULL,
    fechaTermino DATETIME NOT NULL,
    estado ENUM('porConfirmar', 'confirmado', 'cancelado') NOT NULL DEFAULT 'porConfirmar',
    notas TEXT,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    fechaCreacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fechaActualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_ayudantia_usuario FOREIGN KEY (idUsuario) 
        REFERENCES usuarios(idUsuario) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_ayudantia_asignatura FOREIGN KEY (idAsignatura) 
        REFERENCES asignaturas(idAsignatura) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_ayudantia_laboratorio FOREIGN KEY (idLaboratorio) 
        REFERENCES laboratorios(idLaboratorio) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT chk_fechas CHECK (fechaTermino > fechaInicio),
    
    INDEX idx_usuario (idUsuario),
    INDEX idx_asignatura (idAsignatura),
    INDEX idx_laboratorio (idLaboratorio),
    INDEX idx_fecha_inicio (fechaInicio),
    INDEX idx_estado (estado),
    INDEX idx_activo (activo),
    INDEX idx_laboratorio_fecha (idLaboratorio, fechaInicio, fechaTermino)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================================================
-- TABLA: recuperaciones
-- Descripción: Registro de recuperaciones de clases
-- ==========================================================================
CREATE TABLE recuperaciones (
    idRecuperacion INT AUTO_INCREMENT PRIMARY KEY,
    idUsuario INT NOT NULL,
    idAsignatura INT NOT NULL,
    tipoClase ENUM('catedra', 'practica') NOT NULL,
    ncr VARCHAR(20) NOT NULL,
    seccion ENUM('1', '2') NOT NULL,
    grupo ENUM('diurno', 'vespertino') NOT NULL,
    totalModulos INT NOT NULL DEFAULT 0,
    cantidadAlumnos INT NOT NULL,
    notas TEXT,
    estado ENUM('porConfirmar', 'confirmado', 'cancelado') NOT NULL DEFAULT 'porConfirmar',
    activo TINYINT(1) NOT NULL DEFAULT 1,
    fechaCreacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fechaActualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_recuperacion_usuario FOREIGN KEY (idUsuario)
        REFERENCES usuarios(idUsuario) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_recuperacion_asignatura FOREIGN KEY (idAsignatura)
        REFERENCES asignaturas(idAsignatura) ON DELETE CASCADE ON UPDATE CASCADE,
    
    INDEX idx_recuperacion_usuario (idUsuario),
    INDEX idx_recuperacion_asignatura (idAsignatura),
    INDEX idx_recuperacion_estado (estado),
    INDEX idx_recuperacion_activo (activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================================================
-- TABLA: recuperacion_sesiones
-- Descripción: Sesiones recuperadas y clases perdidas asociadas
-- ==========================================================================
CREATE TABLE recuperacion_sesiones (
    idSesion INT AUTO_INCREMENT PRIMARY KEY,
    idRecuperacion INT NOT NULL,
    tipo ENUM('recuperar', 'perdida') NOT NULL,
    idLaboratorio INT NOT NULL,
    fechaInicio DATETIME NOT NULL,
    fechaTermino DATETIME NOT NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    fechaCreacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fechaActualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_rec_sesion_recuperacion FOREIGN KEY (idRecuperacion)
        REFERENCES recuperaciones(idRecuperacion) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_rec_sesion_laboratorio FOREIGN KEY (idLaboratorio)
        REFERENCES laboratorios(idLaboratorio) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT chk_rec_sesion_fechas CHECK (fechaTermino > fechaInicio),
    
    INDEX idx_rec_sesion_recuperacion (idRecuperacion),
    INDEX idx_rec_sesion_laboratorio (idLaboratorio),
    INDEX idx_rec_sesion_fecha_inicio (fechaInicio),
    INDEX idx_rec_sesion_tipo (tipo),
    INDEX idx_rec_sesion_activo (activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLA: notificaciones
-- Descripción: Registro de notificaciones enviadas
-- ============================================================================
CREATE TABLE notificaciones (
    idNotificacion INT AUTO_INCREMENT PRIMARY KEY,
    idAyudantia INT NOT NULL,
    idUsuario INT NOT NULL,
    tipo ENUM('creacion', 'modificacion', 'confirmacion', 'cancelacion') NOT NULL,
    asunto VARCHAR(200) NOT NULL,
    mensaje TEXT NOT NULL,
    enviado TINYINT(1) NOT NULL DEFAULT 0,
    fechaEnvio DATETIME,
    fechaCreacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_notif_ayudantia FOREIGN KEY (idAyudantia) 
        REFERENCES ayudantias(idAyudantia) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_notif_usuario FOREIGN KEY (idUsuario) 
        REFERENCES usuarios(idUsuario) ON DELETE CASCADE ON UPDATE CASCADE,
        
    INDEX idx_ayudantia (idAyudantia),
    INDEX idx_usuario (idUsuario),
    INDEX idx_enviado (enviado),
    INDEX idx_tipo (tipo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- DATOS DE PRUEBA - USUARIOS
-- ============================================================================
INSERT INTO usuarios (rut, nombre, correo, telefono, perfil) VALUES
('12345678-9', 'Carlos Rodríguez', 'carlos.rodriguez@instituto.cl', '+56912345678', 'administrador'),
('23456789-0', 'Ana María González', 'ana.gonzalez@instituto.cl', '+56923456789', 'docente'),
('34567890-1', 'Pedro Fernández', 'pedro.fernandez@instituto.cl', '+56934567890', 'docente'),
('45678901-2', 'María José Silva', 'maria.silva@instituto.cl', '+56945678901', 'docente'),
('56789012-3', 'Juan Pablo Morales', 'juan.morales@instituto.cl', '+56956789012', 'docente'),
('67890123-4', 'Claudia Ramírez', 'claudia.ramirez@instituto.cl', '+56967890123', 'docente'),
('78901234-5', 'Roberto Soto', 'roberto.soto@instituto.cl', '+56978901234', 'administrador');

-- ============================================================================
-- DATOS DE PRUEBA - LABORATORIOS
-- ============================================================================
INSERT INTO laboratorios (nombre, numero, capacidad, ubicacion, equipamiento) VALUES
('Laboratorio de Computación A', 'LAB-A101', 35, 'Edificio A - Piso 1', '35 PCs, Proyector 4K, Pizarra Digital'),
('Laboratorio de Computación B', 'LAB-A102', 30, 'Edificio A - Piso 1', '30 PCs, Proyector Full HD, Sistema Audio'),
('Laboratorio de Redes', 'LAB-B201', 25, 'Edificio B - Piso 2', '25 PCs, Equipos Cisco, Racks de Red'),
('Laboratorio de Desarrollo', 'LAB-B202', 28, 'Edificio B - Piso 2', '28 PCs de Alto Rendimiento, Monitores Duales'),
('Laboratorio de Electrónica', 'LAB-C301', 20, 'Edificio C - Piso 3', 'Osciloscopios, Multímetros, Estaciones de Soldadura'),
('Laboratorio Multimedia', 'LAB-C302', 22, 'Edificio C - Piso 3', '22 iMac, Tabletas Gráficas, Cámaras Profesionales');

-- ============================================================================
-- DATOS DE PRUEBA - ASIGNATURAS
-- ============================================================================
INSERT INTO asignaturas (ncr, nombre, descripcion, semestre, carrera) VALUES
('PRG101', 'Programación I', 'Introducción a la programación con Python', 1, 'Ingeniería en Informática'),
('PRG201', 'Programación II', 'Programación orientada a objetos con Java', 2, 'Ingeniería en Informática'),
('WEB301', 'Desarrollo Web', 'HTML, CSS, JavaScript y Frameworks Modernos', 3, 'Ingeniería en Informática'),
('BDD401', 'Bases de Datos', 'Modelado y gestión de bases de datos relacionales', 4, 'Ingeniería en Informática'),
('RED501', 'Redes de Computadores', 'Arquitectura y protocolos de redes', 5, 'Ingeniería en Informática'),
('MOV601', 'Desarrollo Móvil', 'Aplicaciones móviles nativas e híbridas', 6, 'Ingeniería en Informática'),
('ELC201', 'Electrónica Digital', 'Circuitos digitales y microcontroladores', 2, 'Ingeniería Electrónica'),
('DIS301', 'Diseño Gráfico', 'Principios de diseño y herramientas digitales', 3, 'Diseño Multimedia');

-- ============================================================================
-- DATOS DE PRUEBA - AYUDANTÍAS (Semana actual)
-- ============================================================================
-- Lunes 17 de Febrero 2026
INSERT INTO ayudantias (idUsuario, idAsignatura, idLaboratorio, fechaInicio, fechaTermino, estado, notas) VALUES
(2, 1, 1, '2026-02-17 08:00:00', '2026-02-17 10:00:00', 'confirmado', 'Revisión de ejercicios básicos de Python'),
(3, 3, 2, '2026-02-17 10:00:00', '2026-02-17 12:00:00', 'porConfirmar', 'Introducción a HTML5 y CSS3'),
(4, 5, 3, '2026-02-17 14:00:00', '2026-02-17 16:00:00', 'confirmado', 'Configuración de routers Cisco'),
(5, 2, 4, '2026-02-17 16:00:00', '2026-02-17 18:00:00', 'porConfirmar', 'POO con Java - Herencia y Polimorfismo');

-- Martes 18 de Febrero 2026
INSERT INTO ayudantias (idUsuario, idAsignatura, idLaboratorio, fechaInicio, fechaTermino, estado, notas) VALUES
(3, 3, 1, '2026-02-18 08:00:00', '2026-02-18 10:00:00', 'confirmado', 'JavaScript ES6+ y manipulación del DOM'),
(4, 4, 2, '2026-02-18 10:00:00', '2026-02-18 12:00:00', 'confirmado', 'Normalización de bases de datos'),
(2, 1, 4, '2026-02-18 14:00:00', '2026-02-18 16:00:00', 'porConfirmar', 'Estructuras de datos con Python'),
(6, 7, 5, '2026-02-18 16:00:00', '2026-02-18 18:00:00', 'confirmado', 'Implementación de circuitos con Arduino');

-- Miércoles 19 de Febrero 2026
INSERT INTO ayudantias (idUsuario, idAsignatura, idLaboratorio, fechaInicio, fechaTermino, estado, notas) VALUES
(5, 6, 4, '2026-02-19 08:00:00', '2026-02-19 10:00:00', 'porConfirmar', 'Desarrollo de apps con React Native'),
(2, 1, 1, '2026-02-19 10:00:00', '2026-02-19 12:00:00', 'confirmado', 'Algoritmos de búsqueda y ordenamiento'),
(3, 3, 2, '2026-02-19 14:00:00', '2026-02-19 16:00:00', 'confirmado', 'Frameworks CSS: Bootstrap y Tailwind'),
(6, 8, 6, '2026-02-19 16:00:00', '2026-02-19 18:00:00', 'porConfirmar', 'Adobe Photoshop e Illustrator avanzado');

-- Jueves 20 de Febrero 2026
INSERT INTO ayudantias (idUsuario, idAsignatura, idLaboratorio, fechaInicio, fechaTermino, estado, notas) VALUES
(4, 4, 2, '2026-02-20 08:00:00', '2026-02-20 10:00:00', 'confirmado', 'SQL avanzado y optimización de consultas'),
(5, 5, 3, '2026-02-20 10:00:00', '2026-02-20 12:00:00', 'porConfirmar', 'Seguridad en redes y VPNs'),
(3, 3, 4, '2026-02-20 14:00:00', '2026-02-20 16:00:00', 'confirmado', 'Vue.js y gestión de estado con Vuex'),
(2, 2, 1, '2026-02-20 16:00:00', '2026-02-20 18:00:00', 'porConfirmar', 'Patrones de diseño en Java');

-- Viernes 21 de Febrero 2026
INSERT INTO ayudantias (idUsuario, idAsignatura, idLaboratorio, fechaInicio, fechaTermino, estado, notas) VALUES
(6, 7, 5, '2026-02-21 08:00:00', '2026-02-21 10:00:00', 'confirmado', 'Proyecto final: Sistema embebido IoT'),
(4, 4, 2, '2026-02-21 10:00:00', '2026-02-21 12:00:00', 'confirmado', 'Procedimientos almacenados y triggers'),
(5, 6, 4, '2026-02-21 14:00:00', '2026-02-21 16:00:00', 'porConfirmar', 'Publicación de apps en Play Store y App Store'),
(3, 3, 1, '2026-02-21 16:00:00', '2026-02-21 18:00:00', 'confirmado', 'Deploy de aplicaciones web con Docker');

-- ============================================================================
-- PROCEDIMIENTOS ALMACENADOS
-- ============================================================================

DELIMITER $$

-- Procedimiento para validar solapamiento de ayudantías
CREATE PROCEDURE sp_validarSolapamiento(
    IN p_idLaboratorio INT,
    IN p_fechaInicio DATETIME,
    IN p_fechaTermino DATETIME,
    IN p_idAyudantia INT
)
BEGIN
        SELECT COUNT(*) as solapamientos
        FROM (
                SELECT a.fechaInicio, a.fechaTermino
                FROM ayudantias a
                WHERE a.idLaboratorio = p_idLaboratorio
                    AND a.activo = 1
                    AND a.estado != 'cancelado'
                    AND (p_idAyudantia IS NULL OR a.idAyudantia != p_idAyudantia)
        
                UNION ALL
        
                SELECT rs.fechaInicio, rs.fechaTermino
                FROM recuperacion_sesiones rs
                INNER JOIN recuperaciones r ON r.idRecuperacion = rs.idRecuperacion
                WHERE rs.idLaboratorio = p_idLaboratorio
                    AND rs.activo = 1
                    AND r.activo = 1
                    AND r.estado != 'cancelado'
        ) AS eventos
        WHERE (eventos.fechaInicio < p_fechaTermino AND eventos.fechaTermino > p_fechaInicio);
END$$

-- Procedimiento para obtener ayudantías de un usuario
CREATE PROCEDURE sp_obtenerAyudantiasUsuario(
    IN p_idUsuario INT,
    IN p_fechaInicio DATE,
    IN p_fechaFin DATE
)
BEGIN
    SELECT 
        a.idAyudantia,
        a.fechaInicio,
        a.fechaTermino,
        a.estado,
        a.notas,
        asig.nombre as asignatura,
        asig.ncr,
        lab.nombre as laboratorio,
        lab.numero as numeroLaboratorio,
        u.nombre as docente,
        u.perfil
    FROM ayudantias a
    INNER JOIN asignaturas asig ON a.idAsignatura = asig.idAsignatura
    INNER JOIN laboratorios lab ON a.idLaboratorio = lab.idLaboratorio
    INNER JOIN usuarios u ON a.idUsuario = u.idUsuario
    WHERE a.idUsuario = p_idUsuario
      AND a.activo = 1
      AND DATE(a.fechaInicio) BETWEEN p_fechaInicio AND p_fechaFin
    ORDER BY a.fechaInicio;
END$$

-- Procedimiento para obtener todas las ayudantías (administrador)
CREATE PROCEDURE sp_obtenerTodasAyudantias(
    IN p_fechaInicio DATE,
    IN p_fechaFin DATE
)
BEGIN
    SELECT 
        a.idAyudantia,
        a.fechaInicio,
        a.fechaTermino,
        a.estado,
        a.notas,
        asig.nombre as asignatura,
        asig.ncr,
        lab.nombre as laboratorio,
        lab.numero as numeroLaboratorio,
        lab.idLaboratorio,
        u.nombre as docente,
        u.idUsuario,
        u.perfil
    FROM ayudantias a
    INNER JOIN asignaturas asig ON a.idAsignatura = asig.idAsignatura
    INNER JOIN laboratorios lab ON a.idLaboratorio = lab.idLaboratorio
    INNER JOIN usuarios u ON a.idUsuario = u.idUsuario
    WHERE a.activo = 1
      AND DATE(a.fechaInicio) BETWEEN p_fechaInicio AND p_fechaFin
    ORDER BY a.fechaInicio, lab.nombre;
END$$

DELIMITER ;

-- ============================================================================
-- VISTAS
-- ============================================================================

-- Vista de ayudantías con información completa
CREATE VIEW vw_ayudantias_completas AS
SELECT 
    a.idAyudantia,
    a.fechaInicio,
    a.fechaTermino,
    a.estado,
    a.notas,
    a.fechaCreacion,
    a.fechaActualizacion,
    asig.idAsignatura,
    asig.ncr,
    asig.nombre as asignatura,
    asig.carrera,
    lab.idLaboratorio,
    lab.nombre as laboratorio,
    lab.numero as numeroLaboratorio,
    lab.ubicacion,
    u.idUsuario,
    u.rut as rutDocente,
    u.nombre as docente,
    u.correo as correoDocente,
    u.telefono as telefonoDocente,
    u.perfil,
    TIMESTAMPDIFF(MINUTE, a.fechaInicio, a.fechaTermino) as duracionMinutos
FROM ayudantias a
INNER JOIN asignaturas asig ON a.idAsignatura = asig.idAsignatura
INNER JOIN laboratorios lab ON a.idLaboratorio = lab.idLaboratorio
INNER JOIN usuarios u ON a.idUsuario = u.idUsuario
WHERE a.activo = 1;

-- ============================================================================
-- FIN DEL SCRIPT
-- ============================================================================