--
--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 institution_ag 
AS 
SELECT 
    inst.ano_censo, 
    nome_ies, 
    cod_ies,
    sigla_uf_ies,
    CASE WHEN ("cod_uf_ies"= 11 OR "cod_uf_ies"=12 OR "cod_uf_ies"=13 OR "cod_uf_ies"=14 OR "cod_uf_ies"=15 OR "cod_uf_ies"=16 OR "cod_uf_ies"=17) THEN 'Norte' WHEN ("cod_uf_ies"= 21 OR "cod_uf_ies"=22 OR "cod_uf_ies"=23 OR "cod_uf_ies"=24 OR "cod_uf_ies"=25 OR "cod_uf_ies"=26 OR "cod_uf_ies"=27 OR "cod_uf_ies"=28 OR "cod_uf_ies"=29)  THEN 'Nordeste' WHEN ("cod_uf_ies"= 31 OR "cod_uf_ies"=32 OR "cod_uf_ies"=33 OR "cod_uf_ies"=35)  THEN 'Sudeste' WHEN ("cod_uf_ies"= 41 OR "cod_uf_ies"=42 OR "cod_uf_ies"=43)  THEN 'Sul' WHEN ("cod_uf_ies"= 50 OR "cod_uf_ies"=51 OR "cod_uf_ies"=52 OR "cod_uf_ies"=53)  THEN 'Centro-Oeste' ELSE null END AS region
    
FROM 
    ies_ens_superior as inst 

WHERE 
    (ano_censo = (
        SELECT 
            MAX(ano_censo) 
        FROM ies_ens_superior as ies 
        WHERE inst.cod_ies = ies.cod_ies)
    ) AND 
    (cod_categoria_administrativa IN (1)) AND 
    (cod_organizacao_academica IN (1, 4, 5)) 

GROUP BY 
    inst.ano_censo, 
    nome_ies, 
    cod_ies,
    sigla_uf_ies,
    region

ORDER BY 
    inst.ano_censo ASC, 
    nome_ies ASC,
    sigla_uf_ies,
    region
;