-- É aconselhavel que você MARQUE cada bloco de Comandos e execute na mesma Query Tool
--
-- Este script faz ajustes nas tabelas logradouros e logradourostipos para possibilitar a criação da
-- visão LOGRCOMPLETO que figura no Modelo de Dados e no Dicionário de Dados da Base PraticaSQL
--
-- NOVAMENTE: É aconselhavel que você MARQUE cada bloco de Comandos e execute na mesma Query Tool



-- Bloco 1: Configurando o ambiente
SET transaction_timeout = 0;
SELECT pg_catalog.set_config('search_path', '', false);
-- Fim do Bloco 1


-- Bloco 2: Criação da Tabela logradourostipos (Tabela não definida no Script Inicial)
-- Na ordem faz-se a criação da tabela logradourostipos
CREATE TABLE public.logradourostipos (
    pktipologradouro SMALLINT NOT NULL,
    txnometipologradouro CHARACTER VARYING(250) NOT NULL,
    dtcadtipologradouro DATE NOT NULL
);
-- Comentando a tabela e os campos da tabela criada com os comandos: 
COMMENT ON TABLE logradourostipos IS 'Cadastro dos tipo de logradouros';
COMMENT ON COLUMN logradourostipos.pktipologradouro IS 'pk da Tabela [pktipologradouro]=[pktipologradouro].';
COMMENT ON COLUMN logradourostipos.txnometipologradouro IS 'Nome identificando o Tipo de Logradouro.';
COMMENT ON COLUMN logradourostipos.dtcadtipologradouro IS 'Data de geração do registro.';
-- Fim do Bloco 2


-- Bloco 3: Incluindo linhas em logradourostipos
-- Acrescentando linhas...
INSERT INTO public.logradourostipos VALUES (1, 'Aeroporto', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (2, 'Alameda', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (3, 'Área', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (4, 'Avenida', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (5, 'Campo', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (6, 'Chácara', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (7, 'Colônia', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (8, 'Condomínio', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (9, 'Conjunto', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (10, 'Distrito', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (11, 'Esplanada', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (12, 'Estação', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (13, 'Estrada', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (14, 'Favela', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (15, 'Fazenda', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (16, 'Feira', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (17, 'Jardim', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (18, 'Ladeira', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (19, 'Lago', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (20, 'Lagoa', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (21, 'Largo', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (22, 'Loteamento', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (23, 'Morro', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (24, 'Núcleo', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (25, 'Parque', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (26, 'Passarela', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (27, 'Pátio', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (28, 'Praça', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (29, 'Quadra', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (30, 'Recanto', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (31, 'Residencial', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (32, 'Rodovia', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (33, 'Rua', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (34, 'Setor', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (35, 'Sítio', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (36, 'Travessa', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (37, 'Trecho', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (38, 'Trevo', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (39, 'Vale', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (40, 'Vereda', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (41, 'Via', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (42, 'Viaduto', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (43, 'Viela', '2018-10-25');
INSERT INTO public.logradourostipos VALUES (44, 'Vila', '2018-10-25');
-- Fim do Bloco 3

-- A Tabela logradouros também precisa passar por alterações em seus dados.
-- No script de criação das Tabelas da Base, em 'logradouros' existe o fkbairro.
-- Podemos trocar o nome de fkbairro para fktipologradouro (como indicado no DD).
-- Para isso...


-- Bloco 4: Excliuindo as Chaves Estrangeiras que referenciam pklogradouro
-- Excluindo TODAS as Chaves estrangeiras que apontam para pklogradouro
ALTER TABLE IF EXISTS public.agencias DROP CONSTRAINT IF EXISTS fk2agencia;
ALTER TABLE IF EXISTS public.autores DROP CONSTRAINT IF EXISTS fk1autor;
ALTER TABLE IF EXISTS public.armazens DROP CONSTRAINT IF EXISTS fk1armazem;
ALTER TABLE IF EXISTS public.bancos DROP CONSTRAINT IF EXISTS fk1banco;
ALTER TABLE IF EXISTS public.clientes DROP CONSTRAINT IF EXISTS fk1cliente;
ALTER TABLE IF EXISTS public.editoras DROP CONSTRAINT IF EXISTS fk1editora;
ALTER TABLE IF EXISTS public.empresas DROP CONSTRAINT IF EXISTS fk1empresa;
ALTER TABLE IF EXISTS public.fabricantes DROP CONSTRAINT IF EXISTS fk1fabricante;
ALTER TABLE IF EXISTS public.fornecedores DROP CONSTRAINT IF EXISTS fk1fornecedor;
ALTER TABLE IF EXISTS public.funcionarios DROP CONSTRAINT IF EXISTS fk4funcionario;
ALTER TABLE IF EXISTS public.instituicoesdeensino DROP CONSTRAINT IF EXISTS fk1instituicao;
ALTER TABLE IF EXISTS public.bairroslogradouros DROP CONSTRAINT IF EXISTS fk2bairrologradouro;
ALTER TABLE IF EXISTS public.medicos DROP CONSTRAINT IF EXISTS fk3medico;  -- logradouromoradia
ALTER TABLE IF EXISTS public.medicos DROP CONSTRAINT IF EXISTS fk4medico;  -- logradouroclinica
ALTER TABLE IF EXISTS public.ocorrencias DROP CONSTRAINT IF EXISTS fk4ocorrencia;  -- logradouroprincipal
ALTER TABLE IF EXISTS public.ocorrencias DROP CONSTRAINT IF EXISTS fk5ocorrencia;  -- logradourosecundario
ALTER TABLE IF EXISTS public.oficinas DROP CONSTRAINT IF EXISTS fk1oficina;
ALTER TABLE IF EXISTS public.planosdesaude DROP CONSTRAINT IF EXISTS fk1planodesaude;
-- Fim do Bloco 4

-- Bloco 5: DELETANDO as linhas de LOGRADOUROS
-- Excluindo qualquer linha que exista em logradouros
truncate logradouros;
-- Fim do Bloco 5

-- Bloco 6: Trocando o Nome do campo fkbairro para fktipologradouro
-- Alterando a estrutura de Logradouros para ter a FK conectando com logradourostipos
ALTER TABLE IF EXISTS public.logradouros RENAME fkbairro TO fktipologradouro;
-- Fim do Bloco 6


-- Bloco 7: Fazendo a inclusão de linhas em logradouros
-- Incluindo as linhas que tornam possível a junção entre logradouros e logradourostipos
INSERT INTO public.logradouros VALUES (500, 'Bela Cintra', 5, 33, '3', '2010-10-10');
INSERT INTO public.logradouros VALUES (510, 'Quintino Bocaiuva', 5, 33, '3', '2010-10-10');
INSERT INTO public.logradouros VALUES (520, 'Pamplona', 15, 33, '3', '2011-06-01');
INSERT INTO public.logradouros VALUES (530, 'Bresser', 5, 33, '3', '2011-06-10');
INSERT INTO public.logradouros VALUES (540, 'Cristóvão Bueno', 5, 33, '3', '2011-10-22');
INSERT INTO public.logradouros VALUES (550, 'Gaspar Bueno de Almeida', 5, 33, '3', '2011-10-24');
INSERT INTO public.logradouros VALUES (560, 'Paraisópolis', 5, 33, '3', '2012-10-10');
INSERT INTO public.logradouros VALUES (570, 'Anhaguera', 5, 32, '3', '2012-10-10');
INSERT INTO public.logradouros VALUES (580, 'Alvarenga Peixoto', 5, 33, '3', '2013-03-20');
INSERT INTO public.logradouros VALUES (590, 'Castelo Branco', 5, 32, '3', '2014-01-20');
INSERT INTO public.logradouros VALUES (600, 'Pelotas', 5, 33, '3', '2014-03-10');
INSERT INTO public.logradouros VALUES (610, 'Vital Brasil', 5, 4, '3', '2014-03-14');
INSERT INTO public.logradouros VALUES (620, 'das Nações Unidas', 5, 4, '3', '2013-10-10');
INSERT INTO public.logradouros VALUES (280, 'Troncoso Perez', NULL, 33, '3', '2005-05-20');
INSERT INTO public.logradouros VALUES (10, 'do Tesouro', 5, 33, '3', '1996-01-01');
INSERT INTO public.logradouros VALUES (20, 'Horacio Soares', 40, 4, '3', '1998-10-10');
INSERT INTO public.logradouros VALUES (40, 'Brigadeiro Luiz Antônio', 5, 4, '3', '2000-05-29');
INSERT INTO public.logradouros VALUES (50, 'Ricardo Berrini', 5, 4, '3', '2000-06-14');
INSERT INTO public.logradouros VALUES (60, 'Universitária', 5, 4, '3', '2001-01-10');
INSERT INTO public.logradouros VALUES (70, 'Brigadeiro Luis Antonio', 5, 33, '3', '2001-01-10');
INSERT INTO public.logradouros VALUES (80, '12 de Outubro', 40, 33, '3', '2001-05-16');
INSERT INTO public.logradouros VALUES (100, 'Anchieta', 5, 32, '3', '2001-05-20');
INSERT INTO public.logradouros VALUES (110, 'Brigadeiro Faria Lima', 5, 4, '3', '2001-08-21');
INSERT INTO public.logradouros VALUES (120, 'Politécnica', 5, 4, '3', '2001-10-10');
INSERT INTO public.logradouros VALUES (130, 'das Laranjeiras', 5, 33, '3', '2002-01-21');
INSERT INTO public.logradouros VALUES (140, 'Direita', 5, 33, '3', '2002-05-13');
INSERT INTO public.logradouros VALUES (150, 'Roberto Simonsen', 5, 33, '3', '2002-05-16');
INSERT INTO public.logradouros VALUES (160, 'da Quintanda', 5, 33, '3', '2002-05-28');
INSERT INTO public.logradouros VALUES (170, 'Quintino Bocaiuva', 5, 33, '3', '2002-10-21');
INSERT INTO public.logradouros VALUES (180, 'João Cabral de Mello Netto', 5, 4, '3', '2003-04-23');
INSERT INTO public.logradouros VALUES (200, 'Tavares de Miranda', 40, 33, '3', '2004-02-10');
INSERT INTO public.logradouros VALUES (210, 'MMDC', 5, 33, '3', '2004-02-14');
INSERT INTO public.logradouros VALUES (220, 'Celso Garcia', 5, 4, '3', '2005-01-10');
INSERT INTO public.logradouros VALUES (250, 'Celso Garcia', 40, 33, '3', '2005-03-25');
INSERT INTO public.logradouros VALUES (90, 'Troncoso', 15, 33, '3', '2001-05-20');
INSERT INTO public.logradouros VALUES (190, 'Sta Maria da Conceição', 15, 33, '3', '2003-05-27');
INSERT INTO public.logradouros VALUES (230, 'Circular', 15, 33, '3', '2005-02-16');
INSERT INTO public.logradouros VALUES (240, 'das Trombetas', 15, 2, '3', '2005-03-12');
INSERT INTO public.logradouros VALUES (260, 'dos Autonomistas', 10, 4, '3', '2005-05-12');
INSERT INTO public.logradouros VALUES (270, 'Brigadeiro Faria Lima', 15, 33, '3', '2005-05-14');
INSERT INTO public.logradouros VALUES (290, 'dos Timbiras', 5, 4, '3', '2005-05-20');
INSERT INTO public.logradouros VALUES (300, 'Campos Elíseos', 5, 4, '3', '2005-05-28');
INSERT INTO public.logradouros VALUES (310, 'Filadélfia', 5, 4, '3', '2005-09-18');
INSERT INTO public.logradouros VALUES (320, 'Maranhão', 5, 4, '3', '2005-12-13');
INSERT INTO public.logradouros VALUES (330, 'Liberdade', 5, 4, '3', '2006-04-20');
INSERT INTO public.logradouros VALUES (340, 'Almeida Lima Gutemberg', 5, 33, '3', '2006-05-13');
INSERT INTO public.logradouros VALUES (350, 'Pamplona', 5, 2, '3', '2007-01-20');
INSERT INTO public.logradouros VALUES (360, 'Augusta', 5, 33, '3', '2007-02-02');
INSERT INTO public.logradouros VALUES (370, 'Ibitirama', 5, 13, '3', '2008-01-20');
INSERT INTO public.logradouros VALUES (380, 'Pérola Bygton', 5, 28, '3', '2009-10-10');
INSERT INTO public.logradouros VALUES (390, 'Paulo Sá', 5, 33, '3', '2009-12-10');
INSERT INTO public.logradouros VALUES (400, 'Altino Arantes', 40, 33, '3', '2010-02-12');
INSERT INTO public.logradouros VALUES (410, 'Rui Barbosa', 5, 33, '3', '2010-02-12');
INSERT INTO public.logradouros VALUES (420, 'dos Trabalhadores', 40, 33, '3', '2010-10-10');
INSERT INTO public.logradouros VALUES (430, 'São Joaquim', 5, 33, '3', '2010-10-10');
INSERT INTO public.logradouros VALUES (440, 'Alba', 5, 33, '3', '2010-10-10');
INSERT INTO public.logradouros VALUES (450, 'Rubens de Almeida Falcão', 15, 4, '3', '2010-10-10');
INSERT INTO public.logradouros VALUES (460, 'Rio Branco', 5, 4, '3', '2010-10-10');
INSERT INTO public.logradouros VALUES (470, 'Ruben Berta', 5, 4, '3', '2010-10-10');
INSERT INTO public.logradouros VALUES (480, 'dos Maias', 5, 33, '3', '2010-10-10');
INSERT INTO public.logradouros VALUES (490, 'Arthur Thomas', 5, 4, '3', '2010-10-10');
-- Fim do Bloco 7


-- Criando TODAS as Cjhaves estrangeiras que apontam para pklogradouro
-- Comandos para CRIAR todas as FK que referenciam logradouros
-- Execute na ordem abaixo:

--Bloco 8: Criando as FK que referenciam a tabela logradouros.
-- Comandos para CRIAR todas as FK que referenciam logradouros (uma linha cada)
ALTER TABLE public.agencias ADD CONSTRAINT fk2agencia FOREIGN KEY (fklogradouro) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE public.autores ADD CONSTRAINT fk1autor FOREIGN KEY (fklogradouro) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE public.armazens ADD CONSTRAINT fk1armazem FOREIGN KEY (fklogradouro) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE public.bancos ADD CONSTRAINT fk1banco FOREIGN KEY (fklogradourosede) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE public.clientes ADD CONSTRAINT fk1cliente FOREIGN KEY (fklogradouro) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE public.editoras ADD CONSTRAINT fk1editora FOREIGN KEY (fklogradouro) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE public.empresas ADD CONSTRAINT fk1empresa FOREIGN KEY (fklogradouro) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE public.fabricantes ADD CONSTRAINT fk1fabricante FOREIGN KEY (fklogradouro) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE public.fornecedores ADD CONSTRAINT fk1fornecedor FOREIGN KEY (fklogradouro) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE public.funcionarios ADD CONSTRAINT fk4funcionario FOREIGN KEY (fklogradouro) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE public.instituicoesdeensino ADD CONSTRAINT fk1instituicao FOREIGN KEY (fklogradouro) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE public.bairroslogradouros ADD CONSTRAINT fk2bairrologradouro FOREIGN KEY (fklogradouro) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE public.medicos ADD CONSTRAINT fk3medico FOREIGN KEY (fklogradouromoradia) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE public.medicos ADD CONSTRAINT fk4medico FOREIGN KEY (fklogradouroclinica) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE public.ocorrencias ADD CONSTRAINT fk4ocorrencia FOREIGN KEY (fklogradouroprincipal) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE public.ocorrencias ADD CONSTRAINT fk5ocorrencia FOREIGN KEY (fklogradourosecundario) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE public.oficinas ADD CONSTRAINT fk1oficina FOREIGN KEY (fklogradouro) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE public.planosdesaude ADD CONSTRAINT fk1planodesaude FOREIGN KEY (fklogradouro) REFERENCES public.logradouros(pklogradouro) ON UPDATE CASCADE ON DELETE SET NULL;-- AGORA é possível criar a visão logrcompleto.
-- Fim do Bloco 8


-- Bloco 9: Criando a VISÃO LOGRCOMPLETO
-- Agora vamos finalmente criar a visão LOGRCOMPLETO
CREATE OR REPLACE VIEW public.logrcompleto
 AS
 SELECT public.logradouros.pklogradouro,
    concat(public.logradourostipos.txnometipologradouro, ' ', public.logradouros.txnomelogradouro) AS txlogrcompleto,
    public.logradouros.fkcidade,
    public.logradouros.aotipodepiso,
    public.logradouros.dtcadlogradouro
   FROM public.logradouros inner JOIN public.logradourostipos ON public.logradouros.fktipologradouro = public.logradourostipos.pktipologradouro
  ORDER BY public.logradouros.pklogradouro;

ALTER TABLE public.logrcompleto OWNER TO postgres;
COMMENT ON VIEW public.logrcompleto IS 'Concatenação de dados das tabelas logradouros e logradourostipos';
-- Fim do Bloco 9
