--
--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_territorial_ag
AS
SELECT 
    terras_indigenas.ano, 
    terras_indigenas.nome_terra, 
    terras_indigenas.nome_etnia,
    terras_indigenas.tamanho_superficie, 
    terras_indigenas.fase_procedimento, 
    terras_indigenas.tipo_modalidade, 
    terras_indigenas.nome_municipio,
    terras_indigenas.sigla_uf,
    CASE
        WHEN terras_indigenas.sigla_uf = 'DF' THEN 'Centro-Oeste'
        WHEN terras_indigenas.sigla_uf = 'GO' THEN 'Centro-Oeste'
        WHEN terras_indigenas.sigla_uf = 'MS' THEN 'Centro-Oeste'
        WHEN terras_indigenas.sigla_uf = 'MT' THEN 'Centro-Oeste'
        WHEN terras_indigenas.sigla_uf = 'ES' THEN 'Sudeste'
        WHEN terras_indigenas.sigla_uf = 'MG' THEN 'Sudeste'
        WHEN terras_indigenas.sigla_uf = 'RJ' THEN 'Sudeste'
        WHEN terras_indigenas.sigla_uf = 'SP' THEN 'Sudeste'
        WHEN terras_indigenas.sigla_uf = 'PR' THEN 'Sul'
        WHEN terras_indigenas.sigla_uf = 'RS' THEN 'Sul'
        WHEN terras_indigenas.sigla_uf = 'SC' THEN 'Sul'
        WHEN terras_indigenas.sigla_uf = 'BA' THEN 'Nordeste'
        WHEN terras_indigenas.sigla_uf = 'PI' THEN 'Nordeste'
        WHEN terras_indigenas.sigla_uf = 'MA' THEN 'Nordeste'
        WHEN terras_indigenas.sigla_uf = 'CE' THEN 'Nordeste'
        WHEN terras_indigenas.sigla_uf = 'RN' THEN 'Nordeste'
        WHEN terras_indigenas.sigla_uf = 'PB' THEN 'Nordeste'
        WHEN terras_indigenas.sigla_uf = 'PE' THEN 'Nordeste'
        WHEN terras_indigenas.sigla_uf = 'AL' THEN 'Nordeste'
        WHEN terras_indigenas.sigla_uf = 'SE' THEN 'Nordeste'
        WHEN terras_indigenas.sigla_uf = 'TO' THEN 'Norte'
        WHEN terras_indigenas.sigla_uf = 'PA' THEN 'Norte'
        WHEN terras_indigenas.sigla_uf = 'AP' THEN 'Norte'
        WHEN terras_indigenas.sigla_uf = 'RR' THEN 'Norte'
        WHEN terras_indigenas.sigla_uf = 'AM' THEN 'Norte'
        WHEN terras_indigenas.sigla_uf = 'RO' THEN 'Norte'
        WHEN terras_indigenas.sigla_uf = 'AC' THEN 'Norte'
        WHEN terras_indigenas.sigla_uf = 'AM,PA' THEN 'Norte'
        WHEN terras_indigenas.sigla_uf = 'AM,RO' THEN 'Norte'
        WHEN terras_indigenas.sigla_uf = 'AM,AC' THEN 'Norte'
        WHEN terras_indigenas.sigla_uf = 'RR,AM,PA' THEN 'Norte'
        WHEN terras_indigenas.sigla_uf = 'AM,RR' THEN 'Norte'
        WHEN terras_indigenas.sigla_uf = 'RR,AM' THEN 'Norte'
        WHEN terras_indigenas.sigla_uf = 'PA,MT' THEN 'Centro-Oeste e Norte'
        WHEN terras_indigenas.sigla_uf = 'MT,PA' THEN 'Centro-Oeste e Norte'
        WHEN terras_indigenas.sigla_uf = 'AP,PA' THEN 'Norte'
        WHEN terras_indigenas.sigla_uf = 'TO,PA' THEN 'Norte'
        WHEN terras_indigenas.sigla_uf = 'MT,RO' THEN 'Centro-Oeste e Norte'
        WHEN terras_indigenas.sigla_uf = 'RO,MT' THEN 'Centro-Oeste e Norte'
        WHEN terras_indigenas.sigla_uf = 'PR,SC' THEN 'Sul'
        ELSE 'NULL'
   END AS regiao,
    COUNT(*) AS total
    
FROM
    terras_indigenas
    
GROUP BY 
    terras_indigenas.ano, 
    terras_indigenas.nome_terra, 
    terras_indigenas.nome_etnia, 
    terras_indigenas.sigla_uf, 
    terras_indigenas.tamanho_superficie, 
    terras_indigenas.fase_procedimento, 
    terras_indigenas.tipo_modalidade, 
    terras_indigenas.nome_municipio,
    terras_indigenas.sigla_uf,
    regiao
    
ORDER BY 
    terras_indigenas.ano ASC, 
    terras_indigenas.nome_terra ASC, 
    terras_indigenas.nome_etnia ASC, 
    terras_indigenas.sigla_uf ASC, 
    terras_indigenas.tamanho_superficie ASC, 
    terras_indigenas.fase_procedimento ASC, 
    terras_indigenas.tipo_modalidade ASC, 
    terras_indigenas.nome_municipio ASC,
    terras_indigenas.sigla_uf ASC,
    regiao ASC
;