-- ============================================
-- SICA - Stored Procedures
-- Versión: 1.0
-- Fecha: 20 de Octubre, 2025
-- ============================================

DELIMITER $$

-- ============================================
-- SP: Validar Día Laborable
-- ============================================

DROP PROCEDURE IF EXISTS sp_validar_dia_laborable$$

CREATE PROCEDURE sp_validar_dia_laborable(
    IN p_fecha DATE,
    IN p_ano_lectivo_id INT,
    OUT p_es_laborable BOOLEAN,
    OUT p_motivo VARCHAR(255)
)
BEGIN
    DECLARE v_count INT;

    -- Verificar si existe en calendario_no_laborables
    SELECT COUNT(*), COALESCE(MAX(motivo), '')
    INTO v_count, p_motivo
    FROM calendario_no_laborables
    WHERE fecha = p_fecha
      AND ano_lectivo_id = p_ano_lectivo_id;

    IF v_count > 0 THEN
        SET p_es_laborable = FALSE;
    ELSE
        -- Verificar si es domingo (DAYOFWEEK: 1=Domingo, 7=Sábado)
        IF DAYOFWEEK(p_fecha) = 1 THEN
            SET p_es_laborable = FALSE;
            SET p_motivo = 'Domingo';
        ELSE
            SET p_es_laborable = TRUE;
            SET p_motivo = '';
        END IF;
    END IF;
END$$

-- ============================================
-- SP: Obtener Estadísticas de Asistencia por Estudiante
-- ============================================

DROP PROCEDURE IF EXISTS sp_estadisticas_asistencia_estudiante$$

CREATE PROCEDURE sp_estadisticas_asistencia_estudiante(
    IN p_estudiante_id INT,
    IN p_ano_lectivo_id INT
)
BEGIN
    SELECT
        descripcion,
        COUNT(*) as cantidad,
        ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM asistencias WHERE estudiante_id = p_estudiante_id AND ano_lectivo_id = p_ano_lectivo_id), 2) as porcentaje
    FROM asistencias
    WHERE estudiante_id = p_estudiante_id
      AND ano_lectivo_id = p_ano_lectivo_id
    GROUP BY descripcion
    ORDER BY
        CASE descripcion
            WHEN 'PRESENTE' THEN 1
            WHEN 'TARDANZA' THEN 2
            WHEN 'TARDANZA_JUSTIFICADA' THEN 3
            WHEN 'FALTA_JUSTIFICADA' THEN 4
            WHEN 'FALTA_INJUSTIFICADA' THEN 5
        END;
END$$

-- ============================================
-- SP: Obtener Estudiantes con Inasistencias Críticas
-- ============================================

DROP PROCEDURE IF EXISTS sp_estudiantes_inasistencias_criticas$$

CREATE PROCEDURE sp_estudiantes_inasistencias_criticas(
    IN p_programacion_id INT,
    IN p_porcentaje_minimo DECIMAL(5,2),
    IN p_fecha_inicio DATE,
    IN p_fecha_fin DATE
)
BEGIN
    SELECT
        e.id,
        e.nombres,
        e.apellido_paterno,
        e.apellido_materno,
        e.dni,
        COUNT(DISTINCT CASE WHEN a.descripcion = 'PRESENTE' THEN a.fecha END) as presentes,
        COUNT(DISTINCT CASE WHEN a.descripcion = 'TARDANZA' THEN a.fecha END) as tardanzas,
        COUNT(DISTINCT CASE WHEN a.descripcion = 'FALTA_JUSTIFICADA' THEN a.fecha END) as faltas_justificadas,
        COUNT(DISTINCT CASE WHEN a.descripcion = 'FALTA_INJUSTIFICADA' THEN a.fecha END) as faltas_injustificadas,
        COUNT(DISTINCT a.fecha) as total_dias,
        ROUND((COUNT(DISTINCT CASE WHEN a.descripcion = 'FALTA_INJUSTIFICADA' THEN a.fecha END) * 100.0 / NULLIF(COUNT(DISTINCT a.fecha), 0)), 2) as porcentaje_inasistencias
    FROM estudiantes e
    INNER JOIN programacion_estudiantes pe ON e.id = pe.estudiante_id
    LEFT JOIN asistencias a ON e.id = a.estudiante_id
        AND a.programacion_id = pe.programacion_id
        AND a.fecha BETWEEN p_fecha_inicio AND p_fecha_fin
    WHERE pe.programacion_id = p_programacion_id
      AND pe.estado = 'MATRICULADO'
    GROUP BY e.id, e.nombres, e.apellido_paterno, e.apellido_materno, e.dni
    HAVING porcentaje_inasistencias >= p_porcentaje_minimo
    ORDER BY porcentaje_inasistencias DESC;
END$$

-- ============================================
-- SP: Registrar Asistencia con Validaciones
-- ============================================

DROP PROCEDURE IF EXISTS sp_registrar_asistencia$$

CREATE PROCEDURE sp_registrar_asistencia(
    IN p_fecha DATE,
    IN p_hora TIME,
    IN p_descripcion VARCHAR(50),
    IN p_programacion_id INT,
    IN p_estudiante_id INT,
    IN p_ano_lectivo_id INT,
    IN p_observaciones TEXT,
    IN p_registrado_por INT,
    IN p_metodo_registro VARCHAR(20),
    OUT p_resultado INT,
    OUT p_mensaje VARCHAR(255)
)
BEGIN
    DECLARE v_es_laborable BOOLEAN;
    DECLARE v_motivo VARCHAR(255);
    DECLARE v_existe INT;

    -- Validar día laborable
    CALL sp_validar_dia_laborable(p_fecha, p_ano_lectivo_id, v_es_laborable, v_motivo);

    IF NOT v_es_laborable THEN
        SET p_resultado = 0;
        SET p_mensaje = CONCAT('No se puede registrar asistencia: ', v_motivo);
    ELSE
        -- Verificar si ya existe registro
        SELECT COUNT(*) INTO v_existe
        FROM asistencias
        WHERE fecha = p_fecha
          AND programacion_id = p_programacion_id
          AND estudiante_id = p_estudiante_id;

        IF v_existe > 0 THEN
            SET p_resultado = 0;
            SET p_mensaje = 'Ya existe un registro de asistencia para este estudiante en esta fecha';
        ELSE
            -- Insertar asistencia
            INSERT INTO asistencias (
                fecha, hora, descripcion, programacion_id, estudiante_id,
                ano_lectivo_id, observaciones, registrado_por, metodo_registro
            ) VALUES (
                p_fecha, p_hora, p_descripcion, p_programacion_id, p_estudiante_id,
                p_ano_lectivo_id, p_observaciones, p_registrado_por, p_metodo_registro
            );

            SET p_resultado = LAST_INSERT_ID();
            SET p_mensaje = 'Asistencia registrada exitosamente';
        END IF;
    END IF;
END$$

-- ============================================
-- SP: Obtener Resumen de Incidencias por Estudiante
-- ============================================

DROP PROCEDURE IF EXISTS sp_resumen_incidencias_estudiante$$

CREATE PROCEDURE sp_resumen_incidencias_estudiante(
    IN p_estudiante_id INT,
    IN p_ano_lectivo_id INT
)
BEGIN
    SELECT
        ti.categoria,
        ti.gravedad,
        COUNT(*) as cantidad,
        GROUP_CONCAT(DISTINCT i.estado) as estados
    FROM incidencias i
    INNER JOIN tipos_incidencia ti ON i.tipo_incidencia_id = ti.id
    WHERE i.estudiante_id = p_estudiante_id
      AND i.ano_lectivo_id = p_ano_lectivo_id
    GROUP BY ti.categoria, ti.gravedad
    ORDER BY
        CASE ti.gravedad
            WHEN 'MUY_GRAVE' THEN 1
            WHEN 'GRAVE' THEN 2
            WHEN 'MODERADA' THEN 3
            WHEN 'LEVE' THEN 4
        END;
END$$

-- ============================================
-- SP: Obtener Dashboard de Estadísticas Generales
-- ============================================

DROP PROCEDURE IF EXISTS sp_dashboard_estadisticas$$

CREATE PROCEDURE sp_dashboard_estadisticas(
    IN p_ano_lectivo_id INT
)
BEGIN
    -- Total de estudiantes activos
    SELECT COUNT(DISTINCT e.id) as total_estudiantes
    FROM estudiantes e
    WHERE e.estado = 'ACTIVO';

    -- Asistencias del día
    SELECT
        descripcion,
        COUNT(*) as cantidad
    FROM asistencias
    WHERE fecha = CURDATE()
      AND ano_lectivo_id = p_ano_lectivo_id
    GROUP BY descripcion;

    -- Incidencias abiertas
    SELECT COUNT(*) as incidencias_abiertas
    FROM incidencias
    WHERE estado IN ('ABIERTA', 'EN_SEGUIMIENTO')
      AND ano_lectivo_id = p_ano_lectivo_id;

    -- Top 5 estudiantes con más incidencias
    SELECT
        e.id,
        CONCAT(e.apellido_paterno, ' ', e.apellido_materno, ', ', e.nombres) as nombre_completo,
        COUNT(*) as total_incidencias
    FROM incidencias i
    INNER JOIN estudiantes e ON i.estudiante_id = e.id
    WHERE i.ano_lectivo_id = p_ano_lectivo_id
    GROUP BY e.id, nombre_completo
    ORDER BY total_incidencias DESC
    LIMIT 5;
END$$

DELIMITER ;

-- Mensaje de confirmación
SELECT 'Stored Procedures creados exitosamente' AS mensaje;
