-- =====================================================
-- MIGRACIÓN: Modelo Correcto de Matrícula
-- Fecha: 08/11/2025
-- Descripción: Elimina nivel_id, grado_id, seccion_id de estudiantes
--              y los mueve a programacion_estudiantes para mantener historial
-- =====================================================

-- PASO 1: BACKUP DE DATOS ACTUALES
-- =====================================================
-- Crear tabla temporal de respaldo
CREATE TABLE IF NOT EXISTS estudiantes_backup_20251108 AS
SELECT * FROM estudiantes;

CREATE TABLE IF NOT EXISTS programacion_estudiantes_backup_20251108 AS
SELECT * FROM programacion_estudiantes;

-- PASO 2: AGREGAR COLUMNAS A programacion_estudiantes
-- =====================================================
ALTER TABLE programacion_estudiantes
  ADD COLUMN IF NOT EXISTS nivel_id INT NULL COMMENT 'Nivel del estudiante en esta matrícula',
  ADD COLUMN IF NOT EXISTS grado_id INT NULL COMMENT 'Grado del estudiante en esta matrícula',
  ADD COLUMN IF NOT EXISTS seccion_id INT NULL COMMENT 'Sección del estudiante en esta matrícula';

-- PASO 3: MIGRAR DATOS EXISTENTES
-- =====================================================
-- Migrar nivel_id, grado_id, seccion_id desde programaciones a programacion_estudiantes
UPDATE programacion_estudiantes pe
INNER JOIN programaciones p ON pe.programacion_id = p.id
SET
  pe.nivel_id = p.nivel_id,
  pe.grado_id = p.grado_id,
  pe.seccion_id = p.seccion_id
WHERE pe.nivel_id IS NULL OR pe.grado_id IS NULL OR pe.seccion_id IS NULL;

-- PASO 4: VERIFICACIÓN DE DATOS
-- =====================================================
-- Verificar que todos los registros tienen datos migrados
SELECT
  COUNT(*) as total,
  SUM(CASE WHEN nivel_id IS NULL THEN 1 ELSE 0 END) as sin_nivel,
  SUM(CASE WHEN grado_id IS NULL THEN 1 ELSE 0 END) as sin_grado,
  SUM(CASE WHEN seccion_id IS NULL THEN 1 ELSE 0 END) as sin_seccion
FROM programacion_estudiantes;

-- PASO 5: AGREGAR FOREIGN KEYS
-- =====================================================
-- Agregar claves foráneas a programacion_estudiantes
ALTER TABLE programacion_estudiantes
  ADD CONSTRAINT fk_pe_nivel FOREIGN KEY (nivel_id) REFERENCES niveles(id) ON DELETE RESTRICT,
  ADD CONSTRAINT fk_pe_grado FOREIGN KEY (grado_id) REFERENCES grados(id) ON DELETE RESTRICT,
  ADD CONSTRAINT fk_pe_seccion FOREIGN KEY (seccion_id) REFERENCES secciones(id) ON DELETE RESTRICT;

-- PASO 6: ELIMINAR COLUMNAS DE estudiantes (COMENTADO POR SEGURIDAD)
-- =====================================================
-- IMPORTANTE: Descomenta estas líneas SOLO después de verificar que todo funciona correctamente
-- y que tienes un backup completo de la base de datos

-- ALTER TABLE estudiantes DROP FOREIGN KEY IF EXISTS fk_estudiantes_nivel;
-- ALTER TABLE estudiantes DROP FOREIGN KEY IF EXISTS fk_estudiantes_grado;
-- ALTER TABLE estudiantes DROP FOREIGN KEY IF EXISTS fk_estudiantes_seccion;

-- ALTER TABLE estudiantes DROP COLUMN nivel_id;
-- ALTER TABLE estudiantes DROP COLUMN grado_id;
-- ALTER TABLE estudiantes DROP COLUMN seccion_id;

-- PASO 7: ÍNDICES PARA OPTIMIZACIÓN
-- =====================================================
-- Agregar índices para mejorar el rendimiento de consultas
CREATE INDEX idx_pe_nivel_grado ON programacion_estudiantes(nivel_id, grado_id);
CREATE INDEX idx_pe_seccion ON programacion_estudiantes(seccion_id);
CREATE INDEX idx_pe_estado ON programacion_estudiantes(estado);

-- PASO 8: VISTA PARA FACILITAR CONSULTAS
-- =====================================================
-- Vista que muestra la matrícula actual de cada estudiante (último año lectivo)
CREATE OR REPLACE VIEW vista_estudiantes_matricula_actual AS
SELECT
  e.*,
  pe.nivel_id,
  pe.grado_id,
  pe.seccion_id,
  pe.programacion_id,
  pe.fecha_inscripcion,
  pe.estado as estado_matricula,
  n.descripcion as nivel,
  g.descripcion as grado,
  s.nombre as seccion,
  al.ano as ano_lectivo,
  p.turno_id,
  t.descripcion as turno
FROM estudiantes e
LEFT JOIN (
  SELECT pe.*,
         ROW_NUMBER() OVER (PARTITION BY pe.estudiante_id ORDER BY al.fecha_inicio DESC) as rn
  FROM programacion_estudiantes pe
  INNER JOIN programaciones p ON pe.programacion_id = p.id
  INNER JOIN anos_lectivos al ON p.ano_lectivo_id = al.id
  WHERE pe.estado = 'MATRICULADO'
) pe ON e.id = pe.estudiante_id AND pe.rn = 1
LEFT JOIN niveles n ON pe.nivel_id = n.id
LEFT JOIN grados g ON pe.grado_id = g.id
LEFT JOIN secciones s ON pe.seccion_id = s.id
LEFT JOIN programaciones p ON pe.programacion_id = p.id
LEFT JOIN anos_lectivos al ON p.ano_lectivo_id = al.id
LEFT JOIN turnos t ON p.turno_id = t.id;

-- =====================================================
-- NOTAS IMPORTANTES
-- =====================================================
-- 1. Las columnas nivel_id, grado_id, seccion_id se mantienen en estudiantes
--    hasta verificar que todo funciona correctamente
-- 2. Puedes seguir usando estas columnas temporalmente para compatibilidad
-- 3. Una vez verificado, ejecuta el PASO 6 para eliminarlas
-- 4. Los backups están en: estudiantes_backup_20251108 y programacion_estudiantes_backup_20251108
-- 5. La vista vista_estudiantes_matricula_actual facilita obtener la matrícula actual

-- ROLLBACK (En caso de problemas)
-- =====================================================
-- Si algo sale mal, puedes revertir con:
-- DROP TABLE IF EXISTS programacion_estudiantes;
-- CREATE TABLE programacion_estudiantes AS SELECT * FROM programacion_estudiantes_backup_20251108;
-- DROP TABLE IF EXISTS estudiantes;
-- CREATE TABLE estudiantes AS SELECT * FROM estudiantes_backup_20251108;
