-- ============================================================
--  InfinityCapital — Stored Procedures MySQL
--  Archivo: sp_seguridad.sql
--
--  INSTRUCCIÓN: Ejecutar este script directamente en MySQL
--  contra la base de datos: infiny_capital
--
--  Comando:
--    mysql -u infiny_usercapital -p infiny_capital < sp_seguridad.sql
-- ============================================================

-- Eliminar si ya existen (para re-ejecución segura)
DROP PROCEDURE IF EXISTS sp_resumen_cartera;
DROP PROCEDURE IF EXISTS sp_clientes_con_mora;
DROP PROCEDURE IF EXISTS sp_estadisticas_mensuales;

DELIMITER $$

-- ============================================================
--  SP 1: sp_resumen_cartera
--  Retorna el conteo y monto total de créditos agrupados por estado.
--  Uso en dashboard principal.
--
--  Llamada: CALL sp_resumen_cartera();
-- ============================================================
CREATE PROCEDURE sp_resumen_cartera()
BEGIN
    SELECT
        c.estado                                        AS estado,
        COUNT(c.id)                                     AS total_creditos,
        COALESCE(SUM(c.monto_credito), 0)               AS monto_total,
        COALESCE(SUM(c.monto_aprobado), 0)              AS monto_aprobado_total,
        COALESCE(AVG(c.tem), 0)                         AS tem_promedio,
        COALESCE(AVG(c.plazo_meses), 0)                 AS plazo_promedio_meses
    FROM credito c
    GROUP BY c.estado
    ORDER BY total_creditos DESC;
END $$

-- ============================================================
--  SP 2: sp_clientes_con_mora
--  Lista clientes con cuotas vencidas (PENDIENTE y fecha < HOY).
--  Incluye días de mora y monto pendiente.
--
--  Llamada: CALL sp_clientes_con_mora();
-- ============================================================
CREATE PROCEDURE sp_clientes_con_mora()
BEGIN
    SELECT
        u.nombre_completo                               AS cliente_nombre,
        u.email                                         AS cliente_email,
        cl.numero_documento                             AS documento,
        cl.telefono                                     AS telefono,
        c.id                                            AS credito_id,
        c.estado                                        AS credito_estado,
        cuo.numero_cuota                                AS numero_cuota,
        cuo.fecha_vencimiento                           AS fecha_vencimiento,
        DATEDIFF(NOW(), cuo.fecha_vencimiento)          AS dias_mora,
        cuo.cuota_total                                 AS monto_cuota,
        cuo.saldo_capital                               AS saldo_pendiente
    FROM cuota cuo
    INNER JOIN credito c   ON cuo.credito_id  = c.id
    INNER JOIN cliente cl  ON c.cliente_id    = cl.id
    INNER JOIN usuario u   ON cl.usuario_id   = u.id
    WHERE
        cuo.estado_cuota = 'PENDIENTE'
        AND cuo.fecha_vencimiento < CURDATE()
        AND c.estado = 'ACTIVO'
    ORDER BY dias_mora DESC, u.nombre_completo ASC;
END $$

-- ============================================================
--  SP 3: sp_estadisticas_mensuales
--  Estadísticas de créditos otorgados y pagos recibidos
--  para un año y mes específicos.
--
--  Llamada: CALL sp_estadisticas_mensuales(2026, 5);
--           (año, mes — mes en número: 1=Ene, 12=Dic)
-- ============================================================
CREATE PROCEDURE sp_estadisticas_mensuales(
    IN p_year  INT,
    IN p_mes   INT
)
BEGIN
    -- Créditos nuevos en el período
    SELECT
        'CREDITOS_NUEVOS'                               AS categoria,
        COUNT(c.id)                                     AS cantidad,
        COALESCE(SUM(c.monto_credito), 0)               AS monto_total,
        p_year                                          AS anio,
        p_mes                                           AS mes
    FROM credito c
    WHERE
        YEAR(c.fecha_inicio) = p_year
        AND MONTH(c.fecha_inicio) = p_mes

    UNION ALL

    -- Créditos desembolsados (ACTIVO) en el período
    SELECT
        'CREDITOS_DESEMBOLSADOS'                        AS categoria,
        COUNT(c.id)                                     AS cantidad,
        COALESCE(SUM(c.monto_aprobado), 0)              AS monto_total,
        p_year                                          AS anio,
        p_mes                                           AS mes
    FROM credito c
    WHERE
        c.estado = 'ACTIVO'
        AND YEAR(c.fecha_inicio) = p_year
        AND MONTH(c.fecha_inicio) = p_mes

    UNION ALL

    -- Pagos recibidos en el período (movimientos tipo PAGO)
    SELECT
        'PAGOS_RECIBIDOS'                               AS categoria,
        COUNT(m.id)                                     AS cantidad,
        COALESCE(SUM(m.monto), 0)                       AS monto_total,
        p_year                                          AS anio,
        p_mes                                           AS mes
    FROM movimiento m
    WHERE
        m.tipo = 'PAGO'
        AND YEAR(m.fecha) = p_year
        AND MONTH(m.fecha) = p_mes

    UNION ALL

    -- Clientes en mora en el período
    SELECT
        'CLIENTES_EN_MORA'                              AS categoria,
        COUNT(DISTINCT cl.id)                           AS cantidad,
        COALESCE(SUM(cuo.saldo_capital), 0)             AS monto_total,
        p_year                                          AS anio,
        p_mes                                           AS mes
    FROM cuota cuo
    INNER JOIN credito c  ON cuo.credito_id = c.id
    INNER JOIN cliente cl ON c.cliente_id   = cl.id
    WHERE
        cuo.estado_cuota = 'PENDIENTE'
        AND cuo.fecha_vencimiento < CURDATE()
        AND YEAR(cuo.fecha_vencimiento) = p_year
        AND MONTH(cuo.fecha_vencimiento) = p_mes;
END $$

DELIMITER ;

-- ============================================================
--  Verificación de creación
-- ============================================================
SELECT
    ROUTINE_NAME            AS procedimiento,
    ROUTINE_TYPE            AS tipo,
    CREATED                 AS creado,
    LAST_ALTERED            AS modificado
FROM
    INFORMATION_SCHEMA.ROUTINES
WHERE
    ROUTINE_SCHEMA = DATABASE()
    AND ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME;