/*
Copyright (C) 2016 Centro de Computacao Cientifica e Software Livre
Departamento de Informatica - Universidade Federal do Parana - C3SL/UFPR

This file is part of database.

database 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.

database 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 database.  If not, see <https://www.gnu.org/licenses/>.
*/

-- depends: matricula 2017+
-- SimCAQ: tabela de projeção de matrículas --

CREATE TABLE projecao_matricula AS (

WITH urban_day AS
        (SELECT count(*) as urbano_dia_total, ano_censo, estado_id, municipio_id, serie_ano_id
        FROM matricula
        WHERE localizacao_id = 1 AND tipo <= 3 AND dependencia_adm_id IN (1, 2, 3) AND
        (turma_turno_id IN (1, 2, 99) OR turma_turno_id IS null)
        GROUP BY ano_censo, estado_id, municipio_id, serie_ano_id
        ORDER BY ano_censo, ano_censo, estado_id, municipio_id, serie_ano_id),
    
    urban_night AS
        (SELECT count(*) as urbano_noite_total, ano_censo, estado_id, municipio_id, serie_ano_id
        FROM matricula
        WHERE localizacao_id = 1 AND tipo <= 3 AND dependencia_adm_id IN (1, 2, 3) AND
        turma_turno_id = 3
        GROUP BY ano_censo, estado_id, municipio_id, serie_ano_id
        ORDER BY ano_censo, ano_censo, estado_id, municipio_id, serie_ano_id),
    
    rural_day AS
        (SELECT count(*) as rural_dia_total, ano_censo, estado_id, municipio_id, serie_ano_id
        FROM matricula
        WHERE localizacao_id = 2 AND tipo <= 3 AND dependencia_adm_id IN (1, 2, 3) AND
        (turma_turno_id IN (1, 2, 99) OR turma_turno_id IS null)
        GROUP BY ano_censo, estado_id, municipio_id, serie_ano_id
        ORDER BY ano_censo, ano_censo, estado_id, municipio_id, serie_ano_id),
    
    rural_night AS
        (SELECT count(*) as rural_noite_total, ano_censo, estado_id, municipio_id, serie_ano_id
        FROM matricula
        WHERE localizacao_id = 2 AND tipo <= 3 AND dependencia_adm_id IN (1, 2, 3) AND
        turma_turno_id = 3
        GROUP BY ano_censo, estado_id, municipio_id, serie_ano_id
        ORDER BY ano_censo, ano_censo, estado_id, municipio_id, serie_ano_id)
    
SELECT matricula.ano_censo, matricula.estado_id, matricula.municipio_id,
    matricula.serie_ano_id AS etapa_ensino_escola_ano_id, COALESCE(urbano_dia_total, 0) AS urbano_dia_total,
    COALESCE(rural_dia_total, 0) AS rural_dia_total, COALESCE(urbano_noite_total, 0) AS urbano_noite_total,
    COALESCE(rural_noite_total, 0) AS rural_noite_total
FROM matricula
    LEFT JOIN urban_day ON matricula.ano_censo=urban_day.ano_censo AND matricula.estado_id=urban_day.estado_id
        AND matricula.municipio_id=urban_day.municipio_id AND matricula.serie_ano_id=urban_day.serie_ano_id
    LEFT JOIN rural_day ON matricula.ano_censo=rural_day.ano_censo AND matricula.estado_id=rural_day.estado_id
        AND matricula.municipio_id=rural_day.municipio_id AND matricula.serie_ano_id=rural_day.serie_ano_id
    LEFT JOIN rural_night ON matricula.ano_censo=rural_night.ano_censo AND matricula.estado_id=rural_night.estado_id
        AND matricula.municipio_id=rural_night.municipio_id AND matricula.serie_ano_id=rural_night.serie_ano_id
    LEFT JOIN urban_night ON matricula.ano_censo=urban_night.ano_censo AND matricula.estado_id=urban_night.estado_id
        AND matricula.municipio_id=urban_night.municipio_id AND matricula.serie_ano_id=urban_night.serie_ano_id
GROUP BY
    matricula.ano_censo, matricula.estado_id, matricula.municipio_id, etapa_ensino_escola_ano_id, urbano_dia_total,
    rural_dia_total, urbano_noite_total, rural_noite_total
HAVING matricula.ano_censo >= 2017 AND etapa_ensino_escola_ano_id IS NOT NULL
ORDER BY matricula.ano_censo, matricula.estado_id, matricula.municipio_id, etapa_ensino_escola_ano_id
);

-- Create serie mod
INSERT INTO
    projecao_matricula(ano_censo, estado_id, municipio_id, etapa_ensino_escola_ano_id, urbano_dia_total, rural_dia_total,
    urbano_noite_total, rural_noite_total)
SELECT
    ano_censo, estado_id, municipio_id, LEFT(etapa_ensino_escola_ano_id, 1) as serie, sum(urbano_dia_total),
    sum(rural_dia_total), sum(urbano_noite_total), sum(rural_noite_total)
FROM projecao_matricula
GROUP BY ano_censo, estado_id, municipio_id, serie;