WITH Consolidado_TV_SR_VSR AS (
    SELECT 
        P.per_Consecutivo,
        VP.vac_Id,
        V.vac_Nombre,
        DO.dos_Id,
        DO.dos_Nombre,
        VP.vac_EdadVacunaAnios,
        CASE 
            WHEN VP.vac_EdadVacunaAnios = 1 THEN '1 año'
            WHEN VP.vac_EdadVacunaAnios BETWEEN 2 AND 4 THEN '2-4 años'
            WHEN VP.vac_EdadVacunaAnios = 5 THEN '5 años'
            WHEN VP.vac_EdadVacunaAnios BETWEEN 6 AND 16 THEN '6-16 años'
            WHEN VP.vac_EdadVacunaAnios BETWEEN 17 AND 59 THEN '17-59 años'
            WHEN VP.vac_EdadVacunaAnios >= 60 THEN '60 y más años'
            ELSE 'Menos de 1 año'
        END AS Grupo_Edad,
        
		CASE 
            WHEN VP.vac_EdadVacunaAnios = 1 THEN 2
            WHEN VP.vac_EdadVacunaAnios BETWEEN 2 AND 4 THEN 3
            WHEN VP.vac_EdadVacunaAnios = 5 THEN 4
            WHEN VP.vac_EdadVacunaAnios BETWEEN 6 AND 16 THEN 5
            WHEN VP.vac_EdadVacunaAnios BETWEEN 17 AND 59 THEN 6
            WHEN VP.vac_EdadVacunaAnios >= 60 THEN 7
            ELSE 1
        END AS orden_edad
    FROM PERSONA P 
    INNER JOIN VACUNA_PERSONA VP ON P.per_Consecutivo = VP.per_Consecutivo
    INNER JOIN VACUNA V ON V.vac_Id = VP.vac_Id
    INNER JOIN DOSIS DO ON DO.dos_Id = VP.dos_Id
    WHERE 
        P.per_Id IS NOT NULL AND P.per_Id <> '' AND P.per_Id <> '0'
        AND P.per_Nombre1 IS NOT NULL AND P.per_Nombre1 <> '' 
        AND P.per_FechaNac IS NOT NULL 
        AND VP.vac_EdadVacunaAnios >= 0 AND VP.vac_EdadVacunaAnios <= '69' 
        AND VP.vac_EdadVacunaTotalDias >= 0
        AND P.per_Estado IN ('2')--, '4')
        AND VP.vac_Id IN ('8', '30', '34', '15', '55')
        AND (
            VP.vac_Id != '55'  
            OR (VP.vac_Id = '55' AND VP.vac_EdadVacunaAnios > 9)
        )
),
Tabla AS (
    SELECT 
        Grupo_Edad,
        orden_edad,
        CONCAT('Vac_', vac_Id, '_', 
            CASE 
                WHEN dos_Nombre = 'Primera Dosis' THEN 'Primera'
                WHEN dos_Nombre = 'Primer Refuerzo' THEN 'Ref1'
                WHEN dos_Nombre = 'Refuerzo' THEN 'Ref2'
                WHEN dos_Nombre = 'Adicional' THEN 'Adicional'
                WHEN dos_Nombre = 'Única' THEN 'Unica'
                ELSE REPLACE(dos_Nombre, ' ', '_')
            END
        ) AS vacuna_dosis,
        per_Consecutivo
    FROM Consolidado_TV_SR_VSR
),
Tabla_edad AS (
    SELECT 
        Grupo_Edad,
        orden_edad,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_8_Primera' THEN per_Consecutivo END) AS Vac_8_Primera,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_8_Ref1' THEN per_Consecutivo END) AS Vac_8_Ref1,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_8_Ref2' THEN per_Consecutivo END) AS Vac_8_Ref2,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_8_Adicional' THEN per_Consecutivo END) AS Vac_8_Adicional,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_15_Primera' THEN per_Consecutivo END) AS Vac_15_Primera,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_15_Adicional' THEN per_Consecutivo END) AS Vac_15_Adicional,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_15_Unica' THEN per_Consecutivo END) AS Vac_15_Unica,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_30_Primera' THEN per_Consecutivo END) AS Vac_30_Primera,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_30_Ref1' THEN per_Consecutivo END) AS Vac_30_Ref1,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_30_Ref2' THEN per_Consecutivo END) AS Vac_30_Ref2,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_30_Adicional' THEN per_Consecutivo END) AS Vac_30_Adicional,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_34_Primera' THEN per_Consecutivo END) AS Vac_34_Primera,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_34_Ref1' THEN per_Consecutivo END) AS Vac_34_Ref1,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_34_Unica' THEN per_Consecutivo END) AS Vac_34_Unica,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_55_Unica' THEN per_Consecutivo END) AS Vac_55_Unica,
        COUNT(DISTINCT per_Consecutivo) AS Total_Personas
    FROM Tabla
    GROUP BY Grupo_Edad, orden_edad
    
    UNION ALL
    
    SELECT 
        'Nro Personas ' AS Grupo_Edad,
        999 AS orden_edad,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_8_Primera' THEN per_Consecutivo END) AS Vac_8_Primera,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_8_Ref1' THEN per_Consecutivo END) AS Vac_8_Ref1,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_8_Ref2' THEN per_Consecutivo END) AS Vac_8_Ref2,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_8_Adicional' THEN per_Consecutivo END) AS Vac_8_Adicional,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_15_Primera' THEN per_Consecutivo END) AS Vac_15_Primera,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_15_Adicional' THEN per_Consecutivo END) AS Vac_15_Adicional,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_15_Unica' THEN per_Consecutivo END) AS Vac_15_Unica,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_30_Primera' THEN per_Consecutivo END) AS Vac_30_Primera,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_30_Ref1' THEN per_Consecutivo END) AS Vac_30_Ref1,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_30_Ref2' THEN per_Consecutivo END) AS Vac_30_Ref2,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_30_Adicional' THEN per_Consecutivo END) AS Vac_30_Adicional,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_34_Primera' THEN per_Consecutivo END) AS Vac_34_Primera,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_34_Ref1' THEN per_Consecutivo END) AS Vac_34_Ref1,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_34_Unica' THEN per_Consecutivo END) AS Vac_34_Unica,
        COUNT(DISTINCT CASE WHEN vacuna_dosis = 'Vac_55_Unica' THEN per_Consecutivo END) AS Vac_55_Unica,
        COUNT(DISTINCT per_Consecutivo) AS Total_Personas
    FROM Tabla
)
SELECT 
    Grupo_Edad,
	Total_Personas AS 'Total Personas',
    Vac_8_Primera AS 'TV_8 - Primera Dosis',
    Vac_8_Ref1 AS 'TV_8 - Primer Refuerzo',
    Vac_8_Ref2 AS 'TV_8 - Refuerzo',
    Vac_8_Adicional AS 'TV_8 - Adicional',
    Vac_15_Primera AS 'S.R._15 - Primera Dosis',
    Vac_15_Adicional AS 'S.R._15 - Adicional',
    Vac_15_Unica AS 'S.R._15 - Unica',
    Vac_30_Primera AS 'TV_30- Primera Dosis',
    Vac_30_Ref1 AS 'TV_30 - Primer Refuerzo',
    Vac_30_Ref2 AS 'TV_30 - Refuerzo',
    Vac_30_Adicional AS 'TV_30 - Adicional',
    Vac_34_Primera AS 'Varicela + Triple Viral_34 - Primera Dosis',
    Vac_34_Ref1 AS 'Varicela + Triple Viral_34 - Primer Refuerzo',
    Vac_34_Unica AS 'Varicela + Triple Viral_34 - Unica',
    Vac_55_Unica AS 'VSR_55 - Unica'
FROM Tabla_edad
ORDER BY orden_edad;

---****** Total datos persona unica X ultima vacuna X edad

WITH Consolidado_Unica_Edad AS (
    -- Obtener la última vacunación en general para cada persona
    SELECT 
        P.per_Consecutivo,
        MAX(VP.vac_FechaVacuna) AS Ultima_Fecha_Vacuna
    FROM PERSONA P 
    INNER JOIN VACUNA_PERSONA VP ON P.per_Consecutivo = VP.per_Consecutivo
    WHERE 
        P.per_Id IS NOT NULL AND P.per_Id <> '' AND P.per_Id <> '0'
        AND P.per_Nombre1 IS NOT NULL AND P.per_Nombre1 <> '' 
        AND P.per_FechaNac IS NOT NULL 
        AND VP.vac_EdadVacunaAnios >= 0 AND VP.vac_EdadVacunaAnios <= '69' 
        AND VP.vac_EdadVacunaTotalDias >= 0
        AND P.per_Estado IN ('2')--, '4')
        AND VP.vac_Id IN ('8', '30', '34', '15', '55')
        AND (
            VP.vac_Id != '55'  
            OR (VP.vac_Id = '55' AND VP.vac_EdadVacunaAnios > 9)
        )
    GROUP BY P.per_Consecutivo
),
Ultima_Vacuna AS (
    -- Obtener la edad de esa última vacunación
    SELECT 
        P.per_Consecutivo,
        VP.vac_EdadVacunaAnios AS Edad_Ultima_Vacuna
    FROM PERSONA P
    INNER JOIN VACUNA_PERSONA VP ON P.per_Consecutivo = VP.per_Consecutivo
    INNER JOIN Consolidado_Unica_Edad UV ON P.per_Consecutivo = UV.per_Consecutivo 
        AND VP.vac_FechaVacuna = UV.Ultima_Fecha_Vacuna
    WHERE 
        P.per_Id IS NOT NULL AND P.per_Id <> '' AND P.per_Id <> '0'
        AND P.per_Nombre1 IS NOT NULL AND P.per_Nombre1 <> '' 
        AND P.per_FechaNac IS NOT NULL 
        AND VP.vac_EdadVacunaAnios >= 0 AND VP.vac_EdadVacunaAnios <= '69' 
        AND VP.vac_EdadVacunaTotalDias >= 0
        AND P.per_Estado IN ('2')--, '4')
        AND VP.vac_Id IN ('8', '30', '34', '15', '55')
        AND (
            VP.vac_Id != '55'  
            OR (VP.vac_Id = '55' AND VP.vac_EdadVacunaAnios > 9)
        )
),
Agrupado AS (
    SELECT 
        CASE 
            WHEN Edad_Ultima_Vacuna < 1 THEN 'Menor de 1 año'
            WHEN Edad_Ultima_Vacuna = 1 THEN '1 año'
            WHEN Edad_Ultima_Vacuna BETWEEN 2 AND 4 THEN '2 a 4 años'
            WHEN Edad_Ultima_Vacuna = 5 THEN '5 años'
            WHEN Edad_Ultima_Vacuna BETWEEN 6 AND 16 THEN '6 a 16 años'
            WHEN Edad_Ultima_Vacuna BETWEEN 17 AND 59 THEN '17 a 59 años'
            WHEN Edad_Ultima_Vacuna >= 60 THEN '60 y más años'
        END AS Grupo_Edad,
        CASE 
            WHEN Edad_Ultima_Vacuna < 1 THEN 1
            WHEN Edad_Ultima_Vacuna = 1 THEN 2
            WHEN Edad_Ultima_Vacuna BETWEEN 2 AND 4 THEN 3
            WHEN Edad_Ultima_Vacuna = 5 THEN 4
            WHEN Edad_Ultima_Vacuna BETWEEN 6 AND 16 THEN 5
            WHEN Edad_Ultima_Vacuna BETWEEN 17 AND 59 THEN 6
            WHEN Edad_Ultima_Vacuna >= 60 THEN 7
        END AS Orden,
        COUNT(*) AS Total_Personas
    FROM Ultima_Vacuna
    GROUP BY 
        CASE 
            WHEN Edad_Ultima_Vacuna < 1 THEN 'Menor de 1 año'
            WHEN Edad_Ultima_Vacuna = 1 THEN '1 año'
            WHEN Edad_Ultima_Vacuna BETWEEN 2 AND 4 THEN '2 a 4 años'
            WHEN Edad_Ultima_Vacuna = 5 THEN '5 años'
            WHEN Edad_Ultima_Vacuna BETWEEN 6 AND 16 THEN '6 a 16 años'
            WHEN Edad_Ultima_Vacuna BETWEEN 17 AND 59 THEN '17 a 59 años'
            WHEN Edad_Ultima_Vacuna >= 60 THEN '60 y más años'
        END,
        CASE 
            WHEN Edad_Ultima_Vacuna < 1 THEN 1
            WHEN Edad_Ultima_Vacuna = 1 THEN 2
            WHEN Edad_Ultima_Vacuna BETWEEN 2 AND 4 THEN 3
            WHEN Edad_Ultima_Vacuna = 5 THEN 4
            WHEN Edad_Ultima_Vacuna BETWEEN 6 AND 16 THEN 5
            WHEN Edad_Ultima_Vacuna BETWEEN 17 AND 59 THEN 6
            WHEN Edad_Ultima_Vacuna >= 60 THEN 7
        END
)
SELECT 
    Grupo_Edad,
    Total_Personas
FROM Agrupado
ORDER BY Orden;