-- =====================================================
-- STORED PROCEDURE: Promoción Automática de Estudiantes
-- Fecha: 08/11/2025
-- Descripción: Permite promover estudiantes de un año lectivo al siguiente
-- =====================================================

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_promover_estudiantes$$

CREATE PROCEDURE sp_promover_estudiantes(
    IN p_ano_lectivo_origen_id INT,
    IN p_ano_lectivo_destino_id INT,
    IN p_nivel_id INT,
    IN p_grado_origen_id INT,
    IN p_grado_destino_id INT,
    IN p_seccion_destino_id INT,
    IN p_turno_id INT,
    OUT p_total_promovidos INT,
    OUT p_mensaje VARCHAR(500)
)
BEGIN
    DECLARE v_programacion_destino_id INT;
    DECLARE v_estudiantes_procesados INT DEFAULT 0;
    DECLARE v_error VARCHAR(500);
    DECLARE v_existe_programacion INT;

    -- Manejo de errores
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 v_error = MESSAGE_TEXT;
        SET p_total_promovidos = 0;
        SET p_mensaje = CONCAT('Error: ', v_error);
        ROLLBACK;
    END;

    -- Iniciar transacción
    START TRANSACTION;

    -- Validar que el año lectivo destino existe
    IF NOT EXISTS (SELECT 1 FROM anos_lectivos WHERE id = p_ano_lectivo_destino_id) THEN
        SET p_total_promovidos = 0;
        SET p_mensaje = 'Error: El año lectivo destino no existe';
        ROLLBACK;
        LEAVE sp_body;
    END IF;

    -- Validar que el grado destino existe
    IF NOT EXISTS (SELECT 1 FROM grados WHERE id = p_grado_destino_id) THEN
        SET p_total_promovidos = 0;
        SET p_mensaje = 'Error: El grado destino no existe';
        ROLLBACK;
        LEAVE sp_body;
    END IF;

    -- Buscar o crear la programación destino
    SELECT id INTO v_programacion_destino_id
    FROM programaciones
    WHERE ano_lectivo_id = p_ano_lectivo_destino_id
      AND nivel_id = p_nivel_id
      AND grado_id = p_grado_destino_id
      AND seccion_id = p_seccion_destino_id
      AND turno_id = p_turno_id
    LIMIT 1;

    -- Si no existe la programación, crearla
    IF v_programacion_destino_id IS NULL THEN
        INSERT INTO programaciones (
            ano_lectivo_id,
            nivel_id,
            grado_id,
            seccion_id,
            turno_id,
            estado
        ) VALUES (
            p_ano_lectivo_destino_id,
            p_nivel_id,
            p_grado_destino_id,
            p_seccion_destino_id,
            p_turno_id,
            'ACTIVO'
        );

        SET v_programacion_destino_id = LAST_INSERT_ID();
    END IF;

    -- Promover estudiantes
    INSERT INTO programacion_estudiantes (
        programacion_id,
        estudiante_id,
        nivel_id,
        grado_id,
        seccion_id,
        fecha_inscripcion,
        estado
    )
    SELECT
        v_programacion_destino_id,
        pe.estudiante_id,
        p_nivel_id,
        p_grado_destino_id,
        p_seccion_destino_id,
        CURRENT_DATE,
        'MATRICULADO'
    FROM programacion_estudiantes pe
    INNER JOIN programaciones p ON pe.programacion_id = p.id
    WHERE p.ano_lectivo_id = p_ano_lectivo_origen_id
      AND pe.nivel_id = p_nivel_id
      AND pe.grado_id = p_grado_origen_id
      AND pe.estado = 'MATRICULADO'
      -- Evitar duplicados: no promover si ya está matriculado en el año destino
      AND NOT EXISTS (
          SELECT 1
          FROM programacion_estudiantes pe2
          INNER JOIN programaciones p2 ON pe2.programacion_id = p2.id
          WHERE pe2.estudiante_id = pe.estudiante_id
            AND p2.ano_lectivo_id = p_ano_lectivo_destino_id
            AND pe2.estado = 'MATRICULADO'
      );

    SET v_estudiantes_procesados = ROW_COUNT();

    -- Actualizar las columnas legacy en estudiantes (opcional, para compatibilidad)
    UPDATE estudiantes e
    INNER JOIN programacion_estudiantes pe ON e.id = pe.estudiante_id
    INNER JOIN programaciones p ON pe.programacion_id = p.id
    SET
        e.nivel_id = pe.nivel_id,
        e.grado_id = pe.grado_id,
        e.seccion_id = pe.seccion_id
    WHERE p.id = v_programacion_destino_id
      AND pe.estado = 'MATRICULADO';

    -- Confirmar transacción
    COMMIT;

    SET p_total_promovidos = v_estudiantes_procesados;
    SET p_mensaje = CONCAT('Promoción exitosa: ', v_estudiantes_procesados, ' estudiantes promovidos');

END$$

DELIMITER ;

-- =====================================================
-- STORED PROCEDURE: Listar estudiantes candidatos a promoción
-- =====================================================

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_listar_candidatos_promocion$$

CREATE PROCEDURE sp_listar_candidatos_promocion(
    IN p_ano_lectivo_id INT,
    IN p_nivel_id INT,
    IN p_grado_id INT,
    IN p_seccion_id INT
)
BEGIN
    SELECT
        e.id,
        e.dni,
        CONCAT(e.apellido_paterno, ' ', e.apellido_materno, ', ', e.nombres) as nombre_completo,
        n.descripcion as nivel,
        g.descripcion as grado,
        s.nombre as seccion,
        pe.fecha_inscripcion,
        al.ano as ano_lectivo
    FROM estudiantes e
    INNER JOIN programacion_estudiantes pe ON e.id = pe.estudiante_id
    INNER JOIN programaciones p ON pe.programacion_id = p.id
    INNER JOIN anos_lectivos al ON p.ano_lectivo_id = al.id
    INNER JOIN niveles n ON pe.nivel_id = n.id
    INNER JOIN grados g ON pe.grado_id = g.id
    INNER JOIN secciones s ON pe.seccion_id = s.id
    WHERE p.ano_lectivo_id = p_ano_lectivo_id
      AND pe.nivel_id = p_nivel_id
      AND pe.grado_id = p_grado_id
      AND (p_seccion_id IS NULL OR pe.seccion_id = p_seccion_id)
      AND pe.estado = 'MATRICULADO'
      -- No mostrar estudiantes ya promovidos
      AND NOT EXISTS (
          SELECT 1
          FROM programacion_estudiantes pe2
          INNER JOIN programaciones p2 ON pe2.programacion_id = p2.id
          INNER JOIN anos_lectivos al2 ON p2.ano_lectivo_id = al2.id
          WHERE pe2.estudiante_id = e.id
            AND al2.fecha_inicio > al.fecha_inicio
            AND pe2.estado = 'MATRICULADO'
      )
    ORDER BY e.apellido_paterno, e.apellido_materno, e.nombres;
END$$

DELIMITER ;

-- =====================================================
-- FUNCIÓN: Obtener siguiente grado
-- =====================================================

DELIMITER $$

DROP FUNCTION IF EXISTS fn_obtener_siguiente_grado$$

CREATE FUNCTION fn_obtener_siguiente_grado(
    p_grado_actual_id INT
) RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE v_siguiente_grado_id INT;
    DECLARE v_nivel_id INT;
    DECLARE v_orden_actual INT;

    -- Obtener nivel y orden del grado actual
    SELECT nivel_id, orden INTO v_nivel_id, v_orden_actual
    FROM grados
    WHERE id = p_grado_actual_id;

    -- Buscar el siguiente grado en el mismo nivel
    SELECT id INTO v_siguiente_grado_id
    FROM grados
    WHERE nivel_id = v_nivel_id
      AND orden = v_orden_actual + 1
    LIMIT 1;

    RETURN v_siguiente_grado_id;
END$$

DELIMITER ;

-- =====================================================
-- STORED PROCEDURE: Promoción automática por nivel/grado
-- Promociona automáticamente al siguiente grado
-- =====================================================

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_promocion_automatica$$

CREATE PROCEDURE sp_promocion_automatica(
    IN p_ano_lectivo_origen_id INT,
    IN p_ano_lectivo_destino_id INT,
    IN p_nivel_id INT,
    IN p_grado_origen_id INT,
    IN p_seccion_origen_id INT,
    IN p_seccion_destino_id INT,
    IN p_turno_id INT,
    OUT p_total_promovidos INT,
    OUT p_mensaje VARCHAR(500)
)
sp_body: BEGIN
    DECLARE v_grado_destino_id INT;

    -- Obtener el siguiente grado
    SET v_grado_destino_id = fn_obtener_siguiente_grado(p_grado_origen_id);

    -- Validar que existe un siguiente grado
    IF v_grado_destino_id IS NULL THEN
        SET p_total_promovidos = 0;
        SET p_mensaje = 'Error: No existe un grado siguiente para promover';
        LEAVE sp_body;
    END IF;

    -- Ejecutar la promoción usando el procedimiento principal
    CALL sp_promover_estudiantes(
        p_ano_lectivo_origen_id,
        p_ano_lectivo_destino_id,
        p_nivel_id,
        p_grado_origen_id,
        v_grado_destino_id,
        p_seccion_destino_id,
        p_turno_id,
        p_total_promovidos,
        p_mensaje
    );
END$$

DELIMITER ;

-- =====================================================
-- COMENTARIOS Y DOCUMENTACIÓN
-- =====================================================

/*
EJEMPLOS DE USO:

1. Listar candidatos a promoción:
   CALL sp_listar_candidatos_promocion(1, 2, 5, 1);
   -- Parámetros: año_lectivo_id, nivel_id, grado_id, seccion_id

2. Promover estudiantes manualmente (especificando grado destino):
   CALL sp_promover_estudiantes(1, 2, 2, 5, 6, 1, 1, @total, @mensaje);
   SELECT @total, @mensaje;
   -- Parámetros: año_origen, año_destino, nivel, grado_origen, grado_destino, seccion_destino, turno

3. Promoción automática (calcula automáticamente el siguiente grado):
   CALL sp_promocion_automatica(1, 2, 2, 5, 1, 1, 1, @total, @mensaje);
   SELECT @total, @mensaje;
   -- Parámetros: año_origen, año_destino, nivel, grado_origen, seccion_origen, seccion_destino, turno

4. Obtener siguiente grado:
   SELECT fn_obtener_siguiente_grado(5);
   -- Retorna el ID del siguiente grado

NOTAS:
- Los procedimientos validan que no se dupliquen matrículas
- Se crea automáticamente la programación si no existe
- Se mantiene el historial completo en programacion_estudiantes
- Las columnas legacy en estudiantes se actualizan automáticamente
*/
