--
--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 pnad_ag 
AS 
SELECT
    'pnad_continua' "tipo",
    CAST((pnad_cont.cor_raca) AS VARCHAR(255)) AS cor_raca,
    CAST((pnad_cont.in_escola_rede) AS VARCHAR(255)) AS in_escola_rede,
    CAST((pnad_cont.nivel_escola) AS VARCHAR(255)) AS nivel_escola,
    estado.sigla,
    estado.regiao_id,
    ano_censo,
    COUNT(*) AS total 

FROM 
    pnad_cont 
        INNER JOIN 
    estado ON (pnad_cont.cod_uf=estado.id) 

WHERE 
    (idade >= 18) AND 
    (idade <= 24) 

GROUP BY 
    pnad_cont.cor_raca, 
    pnad_cont.in_escola_rede, 
    pnad_cont.nivel_escola, 
    estado.sigla, 
    estado.regiao_id, 
    ano_censo 

UNION ALL

SELECT
    'pnad_nao_continua' "tipo",
    CASE 
        WHEN pnad.cor_raca_original = 2 THEN 1
        WHEN pnad.cor_raca_original = 4 THEN 2
        WHEN pnad.cor_raca_original = 6 THEN 3
        WHEN pnad.cor_raca_original = 8 THEN 4
        WHEN pnad.cor_raca_original = 0 THEN 5
        WHEN pnad.cor_raca_original = 9 THEN 9
        ELSE null    
    END AS cor_raca,
    CASE
        WHEN pnad.rede_ensino = 2 THEN 2
        WHEN pnad.rede_ensino = 4 THEN 1
        ELSE null
    END AS in_escola_rede,
    -- pnad.curso_que_frequenta AS nivel_escola,
    CASE
        WHEN pnad.curso_que_frequenta = 01 THEN 04
        WHEN pnad.curso_que_frequenta = 02 THEN 06
        WHEN pnad.curso_que_frequenta = 03 THEN 05
        WHEN pnad.curso_que_frequenta = 04 THEN 07
        WHEN pnad.curso_que_frequenta = 05 THEN 08
        WHEN pnad.curso_que_frequenta = 06 THEN 03
        WHEN pnad.curso_que_frequenta = 07 THEN 43 -- This is about creches
        -- WHEN pnad.curso_que_frequenta = 08 THEN  No longer exists
        WHEN pnad.curso_que_frequenta = 09 THEN 02
        -- WHEN pnad.curso_que_frequenta = 10 THEN  No longer exists
        WHEN pnad.curso_que_frequenta = 11 THEN 44 -- Old Pnad has masters and doctors degree as one information, this is to separate it from Pnad Cont differentiated degrees
        ELSE null
    END AS nivel_escola,
    estado.sigla,
    estado.regiao_id,
    ano_censo,
    COUNT(*) AS total 

FROM 
    pnad 
        INNER JOIN 
    estado ON (pnad.estado_id=estado.id) 

WHERE 
    (idade >= 18) AND 
    (idade <= 24) 

GROUP BY 
    pnad.cor_raca_original , 
    pnad.rede_ensino, 
    pnad.curso_que_frequenta, 
    estado.sigla, 
    estado.regiao_id, 
    ano_censo 

ORDER BY 
    "tipo" ASC,
    ano_censo ASC,
    cor_raca ASC, 
    in_escola_rede ASC, 
    nivel_escola ASC, 
    sigla ASC, 
    regiao_id ASC
;
