--
--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_fies_ag 
AS 
SELECT 
    fies_temp.ano_censo, 
    fies_temp.codigo_ies, 
    fies_temp.nome_ies,
    sigla_uf,
    CASE WHEN ("sigla_uf"= 'RO' OR "sigla_uf"='AC' OR "sigla_uf"='AM' OR "sigla_uf"='RR' OR "sigla_uf"='PA' OR "sigla_uf"='AP' OR "sigla_uf"='TO') THEN 'Norte' WHEN ("sigla_uf"= 'MA' OR "sigla_uf"='PI' OR "sigla_uf"='CE' OR "sigla_uf"='RN' OR "sigla_uf"='PB' OR "sigla_uf"='PE' OR "sigla_uf"='AL' OR "sigla_uf"='SE' OR "sigla_uf"='BA')  THEN 'Nordeste' WHEN ("sigla_uf"= 'MG' OR "sigla_uf"='ES' OR "sigla_uf"='RJ' OR "sigla_uf"='SP')  THEN 'Sudeste' WHEN ("sigla_uf"= 'PR' OR "sigla_uf"='SC' OR "sigla_uf"='RS')  THEN 'Sul' WHEN ("sigla_uf"= 'MS' OR "sigla_uf"='MT' OR "sigla_uf"='GO' OR "sigla_uf"='DF')  THEN 'Centro-Oeste' ELSE null END AS region

FROM 
    (
        SELECT 
            distinct codigo_ies, 
            nome_ies,
            sigla_uf, 
            ano_censo 
        FROM 
            fies
    ) AS fies_temp 


GROUP BY 
    fies_temp.ano_censo, 
    fies_temp.codigo_ies, 
    fies_temp.nome_ies,
    sigla_uf,
    region

ORDER BY 
    fies_temp.ano_censo ASC, 
    fies_temp.codigo_ies ASC,
    sigla_uf,
    region
;