-- =============================================================
-- FASE 7 — Vista del estado de migración de todos los colegios
-- Ejecutar en cualquier momento para ver qué falta.
-- =============================================================

-- USE siscev;

SELECT
    c.idColegio                                  AS id,
    c.nkxs                                       AS nombre,
    c.bdColeg                                    AS bd,
    IF(c.status = 1, 'Activo', 'Inactivo')       AS estado,
    COUNT(DISTINCT cm.id_modulo)                 AS modulos_on,
    COUNT(DISTINCT cc.clave)                     AS constantes,
    CASE
        WHEN c.status != 1                         THEN 'Inactivo'
        WHEN COUNT(DISTINCT cm.id_modulo) = 0      THEN 'Sin módulos'
        WHEN COUNT(DISTINCT cc.clave) = 0          THEN 'Sin constantes (migrar inicia.php)'
        ELSE 'Listo'
    END                                           AS migracion
FROM colegio c
LEFT JOIN colegio_modulos cm
       ON cm.id_colegio = c.idColegio AND cm.habilitado = 1
LEFT JOIN colegio_config  cc
       ON cc.id_colegio = c.idColegio
GROUP BY c.idColegio, c.nkxs, c.bdColeg, c.status
ORDER BY migracion, c.nkxs;
