----------------------------------------------------------------------------------
-- alterações para o cabeçalho comum



--------------------------------
-- Substituição da tabela UF
--------------------------------
-- retira as constraints
alter table educacao.mat_documentos drop constraint fk_carteiratrabalhouf;
alter table educacao.mat_documentos drop constraint fk_rguf;
alter table educacao.mat_aluno drop constraint fk_ufnascimento;

-- apaga a tabela UF
drop table educacao.estado;
-- cria uma visão substituindo
CREATE OR REPLACE VIEW educacao.estado
AS
SELECT 
	id as codigoestado,
	sigla as siglaestado,
	nome as nomeestado
FROM
	cabcomum.estado;

-- recoloca as constraints (para isso é preciso converter os campos de UF de numeric(2,0) para integer
ALTER TABLE educacao.mat_documentos ALTER carteiratrabalhouf TYPE integer;
alter table educacao.mat_documentos add constraint fk_carteiratrabalhouf foreign key (carteiratrabalhouf) references cabcomum.estado(id);

ALTER TABLE educacao.mat_documentos ALTER rguf TYPE integer;
alter table educacao.mat_documentos add constraint fk_rguf foreign key (rguf) references cabcomum.estado(id);

ALTER TABLE educacao.mat_aluno ALTER ufnascimento TYPE integer;
ALTER TABLE educacao.mat_aluno add constraint fk_ufnascimento foreign key (ufnascimento) references cabcomum.estado(id);

--------------------------------
-- Substituição da tabela Municipio
--------------------------------

-- apaga constraints
ALTER TABLE educacao.mat_aluno DROP CONSTRAINT fk_aluno_escolaanterior;
-- apaga a tabela de municipio
DROP TABLE educacao.municipio;
-- cria a visão
CREATE OR REPLACE VIEW educacao.municipio AS
SELECT
	id as codigomunicipio,
	idestado as codigoestado,
	nome as nomemunicipio	
FROM
	cabcomum.municipio
-- insere as constraints (e altera o tipo de dado de numeric(2,0) para integer
ALTER TABLE educacao.mat_aluno ALTER COLUMN escolaanteriorcidade TYPE integer;
ALTER TABLE educacao.mat_aluno ALTER COLUMN escolaanterioruf TYPE integer;

-- não pude executar a consulta abaixo pq a cabcomum.municipio não tem chave primária
--ALTER TABLE educacao.mat_aluno ADD CONSTRAINT fk_aluno_escolaanterior FOREIGN KEY (escolaanterioruf, escolaanteriorcidade) 
--	REFERENCES cabcomum.municipio (idestado,id);

--------------------------------
-- Substituição da tabela Nacionalidade
--------------------------------

-- apaga constraints
ALTER TABLE educacao.mat_aluno DROP CONSTRAINT fk_nacionalidadealuno;
ALTER TABLE educacao.mat_professor DROP CONSTRAINT fk_nacionalidadeprofessor;
-- apaga a tabela de municipio
DROP TABLE educacao.nacionalidade;
-- cria a visão
CREATE OR REPLACE VIEW educacao.nacionalidade AS
SELECT
	id as nacionalidade,
	nome as descricaonacionalidade
FROM
	cabcomum.nacionalidade;
-- insere as constraints (e altera o tipo de dado de numeric(2,0) para integer
ALTER TABLE educacao.mat_aluno ADD COLUMN nacionalidadealuno2 smallint;
UPDATE educacao.mat_aluno set nacionalidadealuno2 = CAST(nacionalidadealuno as smallint)
ALTER TABLE educacao.mat_aluno DROP COLUMN nacionalidadealuno;
ALTER TABLE educacao.mat_aluno RENAME nacionalidadealuno2  TO nacionalidadealuno;
ALTER TABLE educacao.mat_aluno ADD CONSTRAINT fk_nacionalidadealuno FOREIGN KEY (nacionalidadealuno) REFERENCES cabcomum.nacionalidade(id);


ALTER TABLE educacao.mat_professor ADD COLUMN nacionalidadeprofessor2 smallint;
UPDATE educacao.mat_professor set nacionalidadeprofessor2 = CAST(nacionalidadeprofessor as smallint)
ALTER TABLE educacao.mat_professor DROP COLUMN nacionalidadeprofessor;
ALTER TABLE educacao.mat_professor RENAME nacionalidadeprofessor2  TO nacionalidadeprofessor;
ALTER TABLE educacao.mat_professor ADD CONSTRAINT fk_nacionalidadeprofessor FOREIGN KEY (nacionalidadeprofessor) REFERENCES cabcomum.nacionalidade(id);

--------------------------------
-- Substituição da tabela Orgaoemissor
--------------------------------

-- apaga constraints
ALTER TABLE educacao.mat_documentos DROP CONSTRAINT fk_orgaoemissor;
-- apaga a tabela de municipio
DROP TABLE educacao.orgaoemissor;
-- cria a visão
CREATE OR REPLACE VIEW educacao.orgaoemissor AS
SELECT
	id as orgaoemissor,
	nome as nomeorgaoemissor
FROM
	cabcomum.orgao_emissor;
-- insere as constraints (e altera o tipo de dado de numeric(2,0) para integer
ALTER TABLE educacao.mat_documentos ADD COLUMN orgaoemissor2 integer;
UPDATE educacao.mat_documentos set orgaoemissor2 = CAST(rgorgaoemissor as integer)
ALTER TABLE educacao.mat_documentos DROP COLUMN rgorgaoemissor;
ALTER TABLE educacao.mat_documentos RENAME orgaoemissor2  TO rgorgaoemissor;
ALTER TABLE educacao.mat_documentos ADD CONSTRAINT fk_orgaoemissor FOREIGN KEY (rgorgaoemissor) REFERENCES cabcomum.orgao_emissor(id);


--------------------------------
-- Substituição da tabela mat_aluno
--------------------------------
-- altera o nome da tabela de aluno para mat_alunoEspecificoTmp

ALTER TABLE educacao.mat_aluno RENAME TO "mat_alunoEspecificoTmp";

-- coluna que relaciona com a tabela pessoa em cabcomum

-- cria uma tabela clone à mat_alunoEspecificoTemp que será a verdadeira mat_alunoEspecifico 
-- já sem as colunas desnecessárias

CREATE TABLE educacao."mat_alunoEspecifico"
(
  codaluno bigint NOT NULL,
  blprimeiramatricula boolean NOT NULL,
  dtdeferido date,
  pathfotoaluno character varying(256),
  inep character varying(24),
  blnecessidadesespeciais boolean,
  dtingresso date,
  blalunotransferencia boolean,
  tpescolaorigem character(1),
  nomeescolaorigem character varying(100),
  escolaorigemoutros character varying(100),
  blbolsafamilia boolean,
  responsavelmatricula character(1),
  blenderecocorrespondencia boolean,
  nomeescolaanterior character varying(100),
  codigoescolaanterior numeric(10),
  bairroescolaanterior character varying(100),
  tipoescolaanterior character(1),
  ultimaescolaridadealuno numeric(3),
  blaprovadoultimaescolaridade boolean,
  turmaanterior character varying(25),
  turnoanterior character(1),
  blcompravoescolaridadeanterior boolean,
  blparticipouhorariointegral boolean,
  turnopreferencia character(1),
  bldesejahorariointegral boolean,
  blalunopasseescolar boolean,
  blresponsavelpasseescolar boolean,
  "blPaiTemDocumentos" boolean,
  "nomePai" character varying(100),
  "blMaeTemDocumentos" boolean,
  "nomeMae" character varying(100),
  "blResponsavelTemDocumentos" boolean,
  "nomeResponsavel" character varying(100),
  "dadoCenso" boolean NOT NULL DEFAULT false,
  "grauParentescoResponsavel" character(1),
  blescolaanteriornomunicipio boolean,
  escolaanterioruf integer DEFAULT NULL::numeric,
  escolaanteriorcidade integer DEFAULT NULL::numeric,
  idpessoa bigint,
  CONSTRAINT pk_alunoEspecifico PRIMARY KEY (codaluno),
  CONSTRAINT fk_alunoEspecificopessoa FOREIGN KEY (idpessoa)
      REFERENCES cabcomum.pessoa (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "fk_parentescoResponsavelEspecifico" FOREIGN KEY ("grauParentescoResponsavel")
      REFERENCES educacao.parentesco (grauparentesco) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT un_idpessoaAlunoEspecifico UNIQUE (idpessoa)
)
WITH (
  OIDS=FALSE
);

-- Agora cria uma visão que relaciona pessoa a aluno_Especifico (chamada mat_aluno)

CREATE OR REPLACE VIEW educacao.mat_aluno
AS
SELECT 
	codaluno, 
	blprimeiramatricula, 
	dtdeferido, 
	p.nome 					as nomealuno, 
	pathfotoaluno, 
	pdc.numero 				as cadsus, 
	idsexo 					as sexo, 
	data_nascimento 		as dtnascimento, 
	idestado_nascimento 	as ufnascimento, 
	idmunicipio_nascimento 	as municipionascimento, 
	idpais_nascimento 		as paisorigemaluno, 
	pdce.cartorio 			as certidaocartorio, 
	pdce.termo				as certidaotermo, 
	pdce.folha				as certidaofolha, 
	pdce.livro				as certidaolivro, 
	pdce.data_emissao 		as certidaoemissao, 
	pdce.idestado			as certidaoestado, 
	pdce.idtipo_certidao	as certidaotipo, 
	pdce.matricula_certidao	as certidaomatricula,
	CASE WHEN pdce.apresentado='1' THEN true ELSE false END as blcertidaocomprovada, 
	CASE WHEN pdce.img_documento_frente is NULL THEN false ELSE true END as blcertidaodigitalizada, 
	pdce.img_documento_frente as pathcertidaofrente, 
	pdce.img_documento_verso as pathcertidaoverso, 
	pdcc.numero				as cpf, 
	CASE WHEN pdcc.apresentado='1' THEN true ELSE false END as blcpfcomprovado, 
	CASE WHEN pdcc.img_documento_frente is NULL THEN false ELSE true END as blcpfdigitalizado, 
	pdn.numero 				as nis, 
	CASE WHEN pdn.apresentado='1' THEN true ELSE false END as blniscomprovado, 
	CASE WHEN pdn.img_documento_frente is NULL THEN false ELSE true END as blnisdigitalizado, 
	pdcc.img_documento_frente as pathcpffrente, 
	pdcc.img_documento_verso as pathcpfverso, 
	pdn.img_documento_frente as pathnisfrente, 
	pdn.img_documento_verso as pathnisverso, 
	inep, 
	blnecessidadesespeciais, 
	idraca as cor, 
	dtingresso, 
	blalunotransferencia, 
	tpescolaorigem, 
	nomeescolaorigem, 
	escolaorigemoutros, 
	beneficiario_bolsa_familia 		as blbolsafamilia, 
	responsavelmatricula, 
	fone_residencial 		as telresidencial, 
	fone_celular 			as telcelular, 
	fone_recado 			as telrecado, 
	email, 
	CASE WHEN pec.idtipo_endereco is NULL THEN false ELSE true END as blenderecocorrespondencia, 
	nomeescolaanterior, 
	codigoescolaanterior, 
	bairroescolaanterior, 
	tipoescolaanterior, 
	ultimaescolaridadealuno, 
	blaprovadoultimaescolaridade, 
	turmaanterior, 
	turnoanterior, 
	blcompravoescolaridadeanterior, 
	blparticipouhorariointegral, 
	turnopreferencia, 
	bldesejahorariointegral, 
	blalunopasseescolar, 
	blresponsavelpasseescolar, 
	"blPaiTemDocumentos", 
	"nomePai", 
	"blMaeTemDocumentos", 
	nome_mae as "nomeMae", 
	"blResponsavelTemDocumentos", 
	"nomeResponsavel", 
	"dadoCenso", 
	"grauParentescoResponsavel", 
	blescolaanteriornomunicipio, 
	escolaanterioruf, 
	escolaanteriorcidade, 
	idnacionalidade as nacionalidadealuno, 
	a.idpessoa
  FROM 
	educacao."mat_alunoEspecifico" a INNER JOIN cabcomum.pessoa p 
		ON (a.idpessoa = p.id )
	-- Documentos
	-- CADSUS
	LEFT OUTER JOIN cabcomum.pessoa_documento pdc
		ON (p.id = pdc.idpessoa AND pdc.idtipo_documento = 6)
	-- certidao civil
	LEFT OUTER JOIN cabcomum.pessoa_documento pdce
		ON (p.id = pdce.idpessoa AND pdce.idtipo_documento = 9)
	-- cpf
	LEFT OUTER JOIN cabcomum.pessoa_documento pdcc
		ON (p.id = pdcc.idpessoa AND pdcc.idtipo_documento = 5)
	-- nis
	LEFT OUTER JOIN cabcomum.pessoa_documento pdn
		ON (p.id = pdn.idpessoa AND pdn.idtipo_documento = 4)
	-- Endereços
	-- correspondencia
	LEFT OUTER JOIN cabcomum.pessoa_endereco pec
		ON(p.id = pec.idpessoa AND pec.idtipo_endereco = 2);

ALTER TABLE educacao."mat_alunoNecessidadeEspecial" DROP CONSTRAINT fk_aluno_necessidade;	

ALTER TABLE educacao."mat_alunoNecessidadeEspecial" RENAME TO "mat_alunoNecessidadeEspecialTMP";

CREATE TABLE educacao."mat_alunoNecessidadeEspecial"
(
  cod_aluno bigint NOT NULL,
  cod_necessidade character varying(2) NOT NULL,
  CONSTRAINT "pk_alunoNecessidadeEspeciall" PRIMARY KEY (cod_aluno, cod_necessidade),
  CONSTRAINT fk_necessidade_alunol FOREIGN KEY (cod_necessidade)
      REFERENCES educacao.necessidadesespeciais (tiponecessidadeespecial) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);	


ALTER TABLE educacao."mat_alunoNecessidadeEspecial" ADD 
	CONSTRAINT fk_aluno_necessidade FOREIGN KEY (cod_aluno)
     REFERENCES educacao."mat_alunoEspecifico" (codaluno);
     
ALTER TABLE educacao."mat_alunoEscola" RENAME TO "mat_alunoEscolaTMP";

CREATE TABLE educacao."mat_alunoEscola"
(
  "codAluno" bigint NOT NULL,
  "codEscola" bigint NOT NULL,
  "dataEntrada" date NOT NULL,
  "escolaAtual" boolean NOT NULL DEFAULT true,
  escolaridadealuno numeric(3) DEFAULT 1,
  CONSTRAINT "pkk_alunoEscola" PRIMARY KEY ("codAluno", "codEscola", "dataEntrada"),
  CONSTRAINT "fkk_alunoEscola" FOREIGN KEY ("codAluno")
      REFERENCES educacao."mat_alunoEspecifico" (codaluno) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "fkk_alunoEscolaEscolaridade" FOREIGN KEY (escolaridadealuno)
      REFERENCES educacao.escolaridadealuno (codigoescolaridade) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "fkk_escolaAluno" FOREIGN KEY ("codEscola")
      REFERENCES educacao.mat_escola (codescola) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE educacao."mat_alunoEscola" OWNER TO postgres;
COMMENT ON TABLE educacao."mat_alunoEscola" IS 'Relaciona um aluno a uma escola';

-- Index: educacao.in_atual

-- DROP INDEX educacao.in_atual;

CREATE INDEX inn_atual
  ON educacao."mat_alunoEscola"
  USING btree
  ("codAluno", "codEscola", "escolaAtual");
 
--------------------------------
-- Substituição da tabela CORRACA
--------------------------------
ALTER TABLE educacao."mat_alunoEspecificoTmp" DROP CONSTRAINT fk_coraluno;

ALTER TABLE educacao."mat_professor" DROP CONSTRAINT fk_corprofessor;
ALTER TABLE educacao.mat_professor ALTER corprofessor TYPE bigint USING CAST(corprofessor as bigint);
ALTER TABLE educacao.mat_professor ADD CONSTRAINT fk_corprofessor FOREIGN KEY (corprofessor) REFERENCES cabcomum.raca(id)

DROP VIEW educacao.estatisticacadalunodistribuicaocor;
DROP VIEW educacao.estatisticacadalunodistribuicaocorporescola;

DROP TABLE educacao.corracaaluno;

CREATE OR REPLACE VIEW educacao.corracaaluno
AS
	SELECT 
		id as corraca,
		nome as descricaocorraca
	FROM
		cabcomum.raca;

CREATE OR REPLACE VIEW educacao.estatisticacadalunodistribuicaocor AS 
 SELECT c.descricaocorraca, count(*) AS qtd
   FROM educacao."mat_aluno" a
   JOIN educacao.corracaaluno c ON a.cor = c.corraca
  GROUP BY c.descricaocorraca, a.cor
  ORDER BY c.descricaocorraca;

CREATE OR REPLACE VIEW educacao.estatisticacadalunodistribuicaocorporescola AS 
 SELECT esc."codEscola", c.descricaocorraca, count(*) AS qtd
   FROM educacao."mat_aluno" a
   JOIN educacao.corracaaluno c ON a.cor = c.corraca
   JOIN educacao."mat_alunoEscola" esc ON a.codaluno = esc."codAluno" AND esc."escolaAtual" = true
  GROUP BY c.descricaocorraca, esc."codEscola", a.cor
  ORDER BY c.descricaocorraca;
  
--------------------------------
-- Substituição da tabela SEXO
--------------------------------
DROP TABLE educacao.sexo;

CREATE OR REPLACE VIEW educacao.sexo
AS
	SELECT 
		id as sexo,
		nome as nomesexo
	FROM
		cabcomum.sexo;
--------------------------------
-- Substituição da tabela TIPO LOGRADOURO
--------------------------------

DROP TABLE educacao.tipologradouro;

CREATE OR REPLACE VIEW educacao.tipologradouro
AS
	SELECT
		id as tipologradourogeoiguacu,
		abreviatura as abreviaturatipologradourogeoiguacu
	FROM
		cabcomum.tipo_logradouro;

--------------------------------
-- Substituição da tabela PAIS
--------------------------------
ALTER TABLE educacao."mat_alunoEspecificoTmp" DROP CONSTRAINT fk_paisorigemaluno;

ALTER TABLE educacao.mat_professor DROP CONSTRAINT fk_paisprofessor;
ALTER TABLE educacao.mat_professor ALTER codigopaisprofessor TYPE integer;
ALTER TABLE educacao.mat_professor ADD CONSTRAINT fk_paisprofessor FOREIGN KEY (codigopaisprofessor) REFERENCES cabcomum.pais(id);

DROP TABLE educacao.pais;

CREATE OR REPLACE VIEW educacao.pais
AS
	SELECT 
		id as codigopais,
		nome as nomepais
	FROM
		cabcomum.pais;
		
--------------------------------
-- Substituição da tabela educacao.tiposcertidao
--------------------------------		
DROP TABLE educacao.tiposcertidao;

CREATE OR REPLACE VIEW educacao.tiposcertidao
AS
	SELECT 
		id as tipocertidao,
		nome as descricaotipocertidao
	FROM
		cabcomum.tipo_certidao;


--------------------------------
-- Substituição da tabela educacao.tiposdocumento
--------------------------------
DROP TABLE educacao.tiposdocumento;

CREATE OR REPLACE VIEW educacao.tiposdocumento
AS
	SELECT 
		id as tipodocumento,
		nome as descricaotipodocumento
	FROM
		cabcomum.tipo_documento;
--------------------------------
-- exclusão de algumas tabelas não usadas
--------------------------------  
DROP TABLE educacao.tabelao;
DROP TABLE educacao.geoiguacu;
  
-------------------------------------------------------------------------------------------------------------
-
-
- Atenção: antes de executar os scripts a partir desse ponto, executar o script de migração de dados educacao.php
-
-------------------------------------------------------------------------------------------------------------
ALTER TABLE educacao."mat_turmaAluno" DROP CONSTRAINT "fk_turmaAlunoAluno";
ALTER TABLE educacao."mat_turmaAluno" ADD CONSTRAINT "fk_turmaAlunoAluno" FOREIGN KEY (codaluno)
      REFERENCES educacao."mat_alunoEspecifico" (codaluno);      
