-- ============================================
-- SICA - Migración: Módulo de Programaciones
-- ============================================
-- Este script crea las tablas necesarias para el módulo de Programaciones
-- que gestiona la asignación académica por año lectivo

USE SICA2025;

-- ============================================
-- TABLA: programaciones
-- Programación principal de cada sección por año lectivo
-- ============================================
CREATE TABLE IF NOT EXISTS programaciones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_seccion INT NOT NULL,
    id_anio_lectivo INT NOT NULL,
    id_tutor INT,
    turno ENUM('mañana', 'tarde') DEFAULT 'mañana',
    capacidad_maxima INT DEFAULT 30,
    estado ENUM('activo', 'inactivo', 'finalizado') DEFAULT 'activo',
    observaciones TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_seccion) REFERENCES secciones(id) ON DELETE CASCADE,
    FOREIGN KEY (id_anio_lectivo) REFERENCES anios_lectivos(id) ON DELETE CASCADE,
    FOREIGN KEY (id_tutor) REFERENCES docentes(id) ON DELETE SET NULL,
    UNIQUE KEY unique_programacion (id_seccion, id_anio_lectivo),
    INDEX idx_anio_lectivo (id_anio_lectivo),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLA: programaciones_estudiantes
-- Relación entre programaciones y estudiantes
-- ============================================
CREATE TABLE IF NOT EXISTS programaciones_estudiantes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_programacion INT NOT NULL,
    id_estudiante INT NOT NULL,
    fecha_asignacion DATE DEFAULT (CURDATE()),
    estado ENUM('activo', 'trasladado', 'retirado') DEFAULT 'activo',
    observaciones TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_programacion) REFERENCES programaciones(id) ON DELETE CASCADE,
    FOREIGN KEY (id_estudiante) REFERENCES estudiantes(id) ON DELETE CASCADE,
    UNIQUE KEY unique_estudiante_programacion (id_programacion, id_estudiante),
    INDEX idx_estudiante (id_estudiante),
    INDEX idx_estado (estado),
    INDEX idx_programacion (id_programacion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLA: programaciones_docentes_areas
-- Asignación de áreas académicas y docentes a una programación
-- ============================================
CREATE TABLE IF NOT EXISTS programaciones_docentes_areas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_programacion INT NOT NULL,
    id_docente INT NOT NULL,
    id_area_academica INT NOT NULL,
    fecha_asignacion DATE DEFAULT (CURDATE()),
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    observaciones TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_programacion) REFERENCES programaciones(id) ON DELETE CASCADE,
    FOREIGN KEY (id_docente) REFERENCES docentes(id) ON DELETE CASCADE,
    FOREIGN KEY (id_area_academica) REFERENCES areas_academicas(id) ON DELETE CASCADE,
    UNIQUE KEY unique_docente_area_programacion (id_programacion, id_docente, id_area_academica),
    INDEX idx_docente (id_docente),
    INDEX idx_area_academica (id_area_academica),
    INDEX idx_programacion (id_programacion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLA: programaciones_auxiliares
-- Asignación de auxiliares a una programación
-- ============================================
CREATE TABLE IF NOT EXISTS programaciones_auxiliares (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_programacion INT NOT NULL,
    id_auxiliar INT NOT NULL,
    fecha_asignacion DATE DEFAULT (CURDATE()),
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    observaciones TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_programacion) REFERENCES programaciones(id) ON DELETE CASCADE,
    FOREIGN KEY (id_auxiliar) REFERENCES auxiliares(id) ON DELETE CASCADE,
    UNIQUE KEY unique_auxiliar_programacion (id_programacion, id_auxiliar),
    INDEX idx_auxiliar (id_auxiliar),
    INDEX idx_programacion (id_programacion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- DATOS DE PRUEBA (Opcional)
-- ============================================

-- Insertar algunas programaciones de prueba
-- (Descomentar solo para pruebas)
/*
INSERT INTO programaciones (id_seccion, id_anio_lectivo, id_tutor, turno, capacidad_maxima)
SELECT s.id, a.id, d.id, 'mañana', 30
FROM secciones s
CROSS JOIN anios_lectivos a
CROSS JOIN docentes d
WHERE a.estado = 'activo'
AND s.id IN (1, 2, 3)
AND d.id = 1
LIMIT 3;
*/

-- ============================================
-- VISTAS ÚTILES
-- ============================================

-- Vista: Resumen de programaciones con información completa
CREATE OR REPLACE VIEW v_programaciones_resumen AS
SELECT
    p.id AS programacion_id,
    p.id_seccion,
    s.id_nivel,
    n.nombre AS nivel_nombre,
    s.grado,
    s.seccion,
    p.id_anio_lectivo,
    a.anio AS anio_lectivo,
    p.id_tutor,
    CONCAT(d.apellido_paterno, ' ', d.apellido_materno, ', ', d.nombres) AS tutor_nombre,
    p.turno,
    h.hora_ingreso,
    h.hora_salida,
    p.capacidad_maxima,
    COUNT(DISTINCT pe.id_estudiante) AS total_estudiantes,
    COUNT(DISTINCT pda.id_area_academica) AS total_areas,
    COUNT(DISTINCT paux.id_auxiliar) AS total_auxiliares,
    p.estado,
    p.created_at
FROM programaciones p
LEFT JOIN secciones s ON p.id_seccion = s.id
LEFT JOIN niveles n ON s.id_nivel = n.id
LEFT JOIN anios_lectivos a ON p.id_anio_lectivo = a.id
LEFT JOIN docentes d ON p.id_tutor = d.id
LEFT JOIN horarios h ON h.id_nivel = s.id_nivel AND h.id_anio_lectivo = p.id_anio_lectivo AND h.turno = p.turno
LEFT JOIN programaciones_estudiantes pe ON pe.id_programacion = p.id AND pe.estado = 'activo'
LEFT JOIN programaciones_docentes_areas pda ON pda.id_programacion = p.id AND pda.estado = 'activo'
LEFT JOIN programaciones_auxiliares paux ON paux.id_programacion = p.id AND paux.estado = 'activo'
GROUP BY p.id;

-- Vista: Estudiantes con su programación actual
CREATE OR REPLACE VIEW v_estudiantes_programacion AS
SELECT
    e.id AS estudiante_id,
    e.dni,
    CONCAT(e.apellido_paterno, ' ', e.apellido_materno, ', ', e.nombres) AS estudiante_nombre,
    p.id AS programacion_id,
    p.id_anio_lectivo,
    a.anio,
    s.id_nivel,
    n.nombre AS nivel,
    s.grado,
    s.seccion,
    p.turno,
    pe.estado AS estudiante_estado
FROM estudiantes e
INNER JOIN programaciones_estudiantes pe ON pe.id_estudiante = e.id AND pe.estado = 'activo'
INNER JOIN programaciones p ON p.id = pe.id_programacion AND p.estado = 'activo'
LEFT JOIN secciones s ON s.id = p.id_seccion
LEFT JOIN niveles n ON n.id = s.id_nivel
LEFT JOIN anios_lectivos a ON a.id = p.id_anio_lectivo
WHERE e.estado = 'activo';

-- ============================================
-- FIN DE MIGRACIÓN
-- ============================================
