--
--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 quilombola_eixo_4_ag 
AS 
SELECT
    familias_cadunico_2.ano_censo,
    familias_cadunico_2.ind_familia_quilombola, 
    familias_cadunico_2.id_familia,
    pessoas_cadunico_2.atividade_extrativista,
    pessoas_cadunico_2.idade,
    pessoas_cadunico_2.codigo_ibge,
    pessoas_cadunico_2.concluiu_frequentou,
    pessoas_cadunico_2.ano_serie_frequentou,
    pessoas_cadunico_2.curso_frequentou,
    pessoas_cadunico_2.genero,
    geographic.nome_regiao,
    geographic.sigla_estado,
    COUNT(*) AS Total 
    
FROM

    -- Data from CadUnico
    familias_cadunico_2 
        INNER JOIN 
    pessoas_cadunico_2 
        ON (familias_cadunico_2.id_familia=pessoas_cadunico_2.id_familia AND familias_cadunico_2.ano_censo=pessoas_cadunico_2.ano_censo) 
        INNER JOIN 
        
    -- Geogrphic (State/Region) data    
    (
        SELECT 
            municipio.id AS municipio_id,
            regiao.nome AS nome_regiao,
            estado.sigla AS sigla_estado
        FROM
            estado INNER JOIN municipio ON (estado.id = municipio.estado_id)
                INNER JOIN regiao ON (regiao.id = estado.regiao_id)
    ) AS geographic ON (geographic.municipio_id = pessoas_cadunico_2.codigo_ibge)
    
WHERE 
   familias_cadunico_2.ind_familia_quilombola = '1'

GROUP BY 
    familias_cadunico_2.ano_censo,
    familias_cadunico_2.ind_familia_quilombola,  
    familias_cadunico_2.id_familia,
    pessoas_cadunico_2.atividade_extrativista,
    pessoas_cadunico_2.idade,
    pessoas_cadunico_2.codigo_ibge, 
    pessoas_cadunico_2.concluiu_frequentou, 
    pessoas_cadunico_2.ano_serie_frequentou, 
    pessoas_cadunico_2.curso_frequentou,
    pessoas_cadunico_2.genero,
    geographic.nome_regiao,
    geographic.sigla_estado

ORDER BY 
    familias_cadunico_2.ano_censo ASC, 
    familias_cadunico_2.ind_familia_quilombola,    
    pessoas_cadunico_2.atividade_extrativista,
    pessoas_cadunico_2.idade ASC,
    pessoas_cadunico_2.codigo_ibge ASC, 
    pessoas_cadunico_2.concluiu_frequentou ASC, 
    pessoas_cadunico_2.ano_serie_frequentou ASC, 
    pessoas_cadunico_2.curso_frequentou ASC,
    geographic.nome_regiao ASC,
    geographic.sigla_estado ASC
    ;