--
--Copyright (C) 2019 Centro de Computacao Cientifica e Software Livre
--Departamento de Informatica - Universidade Federal do Parana - C3SL/UFPR
--
--This file is part of SMPPIR-Tables.
--
--SMPPIR-Tables is free software: you can redistribute it and/or modify
--it under the terms of the GNU General Public License as published by
--the Free Software Foundation, either version 3 of the License, or
--(at your option) any later version.
--
--SMPPIR-Tables is distributed in the hope that it will be useful,
--but WITHOUT ANY WARRANTY; without even the implied warranty of
--MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--GNU General Public License for more details.
--
--You should have received a copy of the GNU General Public License
--along with SMPPIR-Tables.  If not, see <https://www.gnu.org/licenses/>.
--

CREATE TABLE indigenas_schools_ag
AS
SELECT
   escola.ano_censo,
   escola.localizacao_diferenciada_id,
   escola.agua_rede_publica,
   escola.energia_rede_publica,
   escola.esgoto_rede_publica,
   escola.lixo_coleta_periodica,
   estado.sigla,
   escola.id,
   matricula.modalidade_ensino,
   CASE 
        WHEN matricula.etapa_ensino_id = 1 THEN 'Educação Infantil'
        WHEN matricula.etapa_ensino_id = 2 THEN 'Educação Infantil'
        WHEN matricula.etapa_ensino_id = 4 THEN 'Ensino Fundamental de 8 anos'
        WHEN matricula.etapa_ensino_id = 5 THEN 'Ensino Fundamental de 8 anos'
        WHEN matricula.etapa_ensino_id = 6 THEN 'Ensino Fundamental de 8 anos'
        WHEN matricula.etapa_ensino_id = 7 THEN 'Ensino Fundamental de 8 anos'
        WHEN matricula.etapa_ensino_id = 8 THEN 'Ensino Fundamental de 8 anos'
        WHEN matricula.etapa_ensino_id = 9 THEN 'Ensino Fundamental de 8 anos'
        WHEN matricula.etapa_ensino_id = 10 THEN 'Ensino Fundamental de 8 anos'
        WHEN matricula.etapa_ensino_id = 11 THEN 'Ensino Fundamental de 8 anos'
        WHEN matricula.etapa_ensino_id = 14 THEN 'Ensino Fundamental de 9 anos'
        WHEN matricula.etapa_ensino_id = 15 THEN 'Ensino Fundamental de 9 anos'
        WHEN matricula.etapa_ensino_id = 16 THEN 'Ensino Fundamental de 9 anos' 
        WHEN matricula.etapa_ensino_id = 17 THEN 'Ensino Fundamental de 9 anos'
        WHEN matricula.etapa_ensino_id = 18 THEN 'Ensino Fundamental de 9 anos'
        WHEN matricula.etapa_ensino_id = 19 THEN 'Ensino Fundamental de 9 anos'
        WHEN matricula.etapa_ensino_id = 20 THEN 'Ensino Fundamental de 9 anos'
        WHEN matricula.etapa_ensino_id = 21 THEN 'Ensino Fundamental de 9 anos'
        WHEN matricula.etapa_ensino_id = 25 THEN 'Ensino Médio'
        WHEN matricula.etapa_ensino_id = 26 THEN 'Ensino Médio'
        WHEN matricula.etapa_ensino_id = 27 THEN 'Ensino Médio'
        WHEN matricula.etapa_ensino_id = 28 THEN 'Ensino Médio'
        WHEN matricula.etapa_ensino_id = 29 THEN 'Ensino Médio'
        WHEN matricula.etapa_ensino_id = 30 THEN 'Curso Técnico Integrado'
        WHEN matricula.etapa_ensino_id = 31 THEN 'Curso Técnico Integrado'
        WHEN matricula.etapa_ensino_id = 32 THEN 'Curso Técnico Integrado'
        WHEN matricula.etapa_ensino_id = 33 THEN 'Curso Técnico Integrado'
        WHEN matricula.etapa_ensino_id = 34 THEN 'Curso Técnico Integrado'
        WHEN matricula.etapa_ensino_id = 35 THEN 'Ensino Médio'
        WHEN matricula.etapa_ensino_id = 36 THEN 'Ensino Médio'
        WHEN matricula.etapa_ensino_id = 37 THEN 'Ensino Médio'
        WHEN matricula.etapa_ensino_id = 38 THEN 'Ensino Médio'
        WHEN matricula.etapa_ensino_id = 39 THEN 'Curso Técnico'
        WHEN matricula.etapa_ensino_id = 40 THEN 'Curso Técnico'
        WHEN matricula.etapa_ensino_id = 41 THEN 'Ensino Fundamental de 9 anos'
        WHEN matricula.etapa_ensino_id = 43 THEN 'EJA (Ensino Fundamental)'
        WHEN matricula.etapa_ensino_id = 44 THEN 'EJA (Ensino Fundamental)'
        WHEN matricula.etapa_ensino_id = 45 THEN 'EJA (Ensino Médio)'
        WHEN matricula.etapa_ensino_id = 46 THEN 'EJA (Ensino Fundamental)'
        WHEN matricula.etapa_ensino_id = 47 THEN 'EJA (Ensino Fundamental)'
        WHEN matricula.etapa_ensino_id = 48 THEN 'EJA (Ensino Médio)'
        WHEN matricula.etapa_ensino_id = 49 THEN 'EJA (Ensino Fundamental)'
        WHEN matricula.etapa_ensino_id = 50 THEN 'EJA (Ensino Fundamental)'
        WHEN matricula.etapa_ensino_id = 51 THEN 'EJA (Ensino Fundamental)'
        WHEN matricula.etapa_ensino_id = 52 THEN 'EJA (Ensino Médio)'
        WHEN matricula.etapa_ensino_id = 53 THEN 'EJA (Ensino Fundamental)'
        WHEN matricula.etapa_ensino_id = 54 THEN 'EJA (Ensino Fundamental)'
        WHEN matricula.etapa_ensino_id = 55 THEN 'EJA (Ensino Médio)'
        WHEN matricula.etapa_ensino_id = 57 THEN 'EJA (Curso Técnico Integrado)'
        WHEN matricula.etapa_ensino_id = 58 THEN 'EJA (Ensino Fundamental)'
        WHEN matricula.etapa_ensino_id = 59 THEN 'EJA (Curso Técnico Integrado)'
        WHEN matricula.etapa_ensino_id = 60 THEN 'EJA (Curso Técnico Integrado)'
        WHEN matricula.etapa_ensino_id = 61 THEN 'EJA (Curso Técnico Integrado)'
        WHEN matricula.etapa_ensino_id = 62 THEN 'EJA (Curso Técnico Integrado)'
        WHEN matricula.etapa_ensino_id = 63 THEN 'EJA (Curso Técnico Integrado)'
        WHEN matricula.etapa_ensino_id = 65 THEN 'EJA (Ensino Fundamental)'
        WHEN matricula.etapa_ensino_id = 67 THEN 'EJA (Curso Técnico Integrado)'
        WHEN matricula.etapa_ensino_id = 68 THEN 'Curso Técnico'
        WHEN matricula.etapa_ensino_id = 69 THEN 'EJA (Ensino Fundamental)'
        WHEN matricula.etapa_ensino_id = 70 THEN 'EJA (Ensino Fundamental)'
        WHEN matricula.etapa_ensino_id = 71 THEN 'EJA (Ensino Médio)'
        WHEN matricula.etapa_ensino_id = 72 THEN 'EJA (Ensino Fundamental)'
        WHEN matricula.etapa_ensino_id = 73 THEN 'EJA (Curso Técnico Integrado)'
        WHEN matricula.etapa_ensino_id = 74 THEN 'EJA (Curso Técnico Integrado)'
        ELSE 'NULL NO BANCO'
   END AS etapa_ensino,
   COUNT(*) AS Total
   
FROM 
    escola,
    matricula,
    estado
   
WHERE
    ((matricula.escola_id = escola.id) AND  (matricula.ano_censo = escola.ano_censo)) AND
    ((escola.dependencia_adm_id = '2') OR  (escola.dependencia_adm_id = '3')) AND
    (escola.educacao_indigena = true) AND
    (estado.id = escola.estado_id) AND
    (escola.ano_censo >= 2012)
    
GROUP BY
   escola.ano_censo,
   escola.localizacao_diferenciada_id,
   escola.agua_rede_publica,
   escola.energia_rede_publica,
   escola.esgoto_rede_publica,
   escola.lixo_coleta_periodica,
   escola.id,
   etapa_ensino,
   estado.sigla,
   matricula.modalidade_ensino
   
ORDER BY
   escola.ano_censo asc,
   estado.sigla asc,
   escola.localizacao_diferenciada_id asc,
   escola.agua_rede_publica asc,
   escola.energia_rede_publica asc,
   escola.esgoto_rede_publica asc,
   escola.lixo_coleta_periodica asc,
   etapa_ensino asc,
   matricula.modalidade_ensino ASC
   ;