-- =============================================
-- Author:		Caco
-- Create date: 2024-05-20
-- Description:	Devorve os tipos de endereço
-- =============================================
CREATE VIEW vw_S_TIPEND_HISTENDPESSOA AS
	SELECT * FROM (VALUES
		('P', 'Correspondência', 1, 2),
		('R', 'Residencial', 2, 1),
		('C', 'Comercial', 3, 3),
		('D', 'Recado', NULL, NULL),
		('T', 'Temporário', NULL, NULL)
		) TIPOENDERECO (S_TIPEND_HISTENDPESSOA, Descricao, Prioridade_Correio, Prioridade_Residencial)


-- =============================================
-- Author:		Caco
-- Create date: 2024-05-20
-- Description:	Devorve os tipos de endereço
-- =============================================
CREATE VIEW vw_N_TIPLOGRAD_HISTENDPESSOA AS
	SELECT * FROM (VALUES
		('4', 'Al.', 'Alameda'),
		('8', 'Av.', Avenida'),
		('31', 'Est.', 'Estrada'),
		('65', 'Pr.', 'Praça'),
		('77', 'Quadra', 'Quadra'),
		('81', 'R.', 'Rua'),
		('90', 'Rod.' ,'Rodovia'),
		('100', 'Tr', 'Travessa'),
		('104', 'Vl', 'Vila'),
		('101', 'Via', 'Via'),
		('105', 'Vla', 'Viela')
		) TIPOLOGRADOURO (N_TIPLOGRAD_HISTENDPESSOA, Sigla, Descricao)

CREATE VIEW vw_Endereco_Residencial AS
SELECT
	s.N_PESSOA_PESSOA, Endereco, s.Bairro, CEP, s.Municipio, UF
FROM (SELECT
		h.N_PESSOA_PESSOA
	   ,CONCAT(tl.Descricao + ' ', TRIM(h.S_DCRRUA_HISTENDPESSOA),
	   CONCAT(', ' + TRIM(h.S_NUMLOGRAD_HISTENDPESSOA),
		CASE COALESCE(TRIM(h.S_DCRCOMPL_HISTENDPESSOA), '')
			WHEN '' THEN ''
			ELSE ' - ' + TRIM(h.S_DCRCOMPL_HISTENDPESSOA)
		END)
	   )
	      AS Endereco
	   ,h.S_DCRBAIRRO_HISTENDPESSOA AS Bairro
	   ,h.S_CEP_HISTENDPESSOA AS CEP
	   ,COALESCE(m.S_NOM_MUN, h.S_DCRMUN_HISTENDPESSOA) AS Municipio
		,COALESCE(m.S_UF_EST, h.S_DCRUF_HISTENDPESSOA) AS UF
	   ,ROW_NUMBER() OVER (PARTITION BY h.N_PESSOA_PESSOA ORDER BY te.Prioridade_Residencial, h.DT_INIVIG_HISTENDPESSOA DESC) n
	FROM HISTORICOENDERECOPESSOA h
	LEFT JOIN MUNICIPIO m
		ON h.N_COD_MUN = m.N_COD_MUN
	LEFT JOIN vw_N_TIPLOGRAD_HISTENDPESSOA tl
		ON h.N_TIPLOGRAD_HISTENDPESSOA = tl.N_TIPLOGRAD_HISTENDPESSOA
	INNER JOIN vw_S_TIPEND_HISTENDPESSOA te
		ON h.S_TIPEND_HISTENDPESSOA = te.S_TIPEND_HISTENDPESSOA
		AND te.Prioridade_Residencial IS NOT NULL
	WHERE COALESCE(h.N_COD_PAIS, 1) = 1
	AND GETDATE() BETWEEN h.DT_INIVIG_HISTENDPESSOA AND COALESCE(h.DT_FIMVIG_HISTENDPESSOA, { D '2199-12-31' })
) s
WHERE n=1



CREATE VIEW vw_Endereco_Correio AS
SELECT
	s.N_PESSOA_PESSOA, Endereco, s.Bairro, CEP, s.Municipio, UF
FROM (SELECT
		h.N_PESSOA_PESSOA
	   ,CONCAT(tl.Descricao + ' ', TRIM(h.S_DCRRUA_HISTENDPESSOA),
	   CONCAT(', ' + TRIM(h.S_NUMLOGRAD_HISTENDPESSOA),
		CASE COALESCE(TRIM(h.S_DCRCOMPL_HISTENDPESSOA), '')
			WHEN '' THEN ''
			ELSE ' - ' + TRIM(h.S_DCRCOMPL_HISTENDPESSOA)
		END)
	   )
	      AS Endereco
	   ,h.S_DCRBAIRRO_HISTENDPESSOA AS Bairro
	   ,h.S_CEP_HISTENDPESSOA AS CEP
	   ,COALESCE(m.S_NOM_MUN, h.S_DCRMUN_HISTENDPESSOA) AS Municipio
		,COALESCE(m.S_UF_EST, h.S_DCRUF_HISTENDPESSOA) AS UF
	   ,ROW_NUMBER() OVER (PARTITION BY h.N_PESSOA_PESSOA ORDER BY te.Prioridade_Correio, h.DT_INIVIG_HISTENDPESSOA DESC) n
	FROM HISTORICOENDERECOPESSOA h
	LEFT JOIN MUNICIPIO m
		ON h.N_COD_MUN = m.N_COD_MUN
	LEFT JOIN vw_N_TIPLOGRAD_HISTENDPESSOA tl
		ON h.N_TIPLOGRAD_HISTENDPESSOA = tl.N_TIPLOGRAD_HISTENDPESSOA
	INNER JOIN vw_S_TIPEND_HISTENDPESSOA te
		ON h.S_TIPEND_HISTENDPESSOA = te.S_TIPEND_HISTENDPESSOA
		AND te.Prioridade_Correio IS NOT NULL
	WHERE COALESCE(h.N_COD_PAIS, 1) = 1
	AND GETDATE() BETWEEN h.DT_INIVIG_HISTENDPESSOA AND COALESCE(h.DT_FIMVIG_HISTENDPESSOA, { D '2199-12-31' })
) s
WHERE n=1