-- ** TITULO      : Crear rate_list_report
-- ** OBJETIVO    : Crear la vista rate_list_report que permite revisar el manual tarifario de los estudios
-- ** DESCRIPCION : Se elimina la vista rate_list_report en caso que exista.
--                  Se crea  la vista.
--                  Se le dan permisos de lectura al usuario metabase_user sobre la vista rate_list_report.

-- View: public.rate_list_report

-- DROP VIEW public.rate_list_report;

DROP VIEW IF EXISTS rate_list_report;

CREATE OR REPLACE VIEW public.rate_list_report AS

    	SELECT base_price.name AS rate_base,
		base_price.description AS description_base_price,
		study_list.name_study AS study_name,
		rate.code AS code, 
		rate.value AS value,
		contratos_modalidades.id_modalidad AS modality,
		CASE WHEN contratos_modalidades.state = 1
			THEN
				'Activo'
			ELSE
				'Inactivo'
			END	AS modality_contract_status,		
		contratos.nombre_contrato AS contract_name,
		CASE WHEN	contratos.estado = TRUE
			THEN
				'Activo'
			ELSE
				'Inactivo'
			END AS contract_status,
		headquarter.headquarter_name AS headquarter_name,
		eps.name AS name_eps
	from rate
	LEFT JOIN base_price ON base_price.id = rate.id_base_price
	LEFT JOIN study_list ON study_list.id_study_list = rate.id_study_list
	LEFT JOIN contratos_examenes ON contratos_examenes.id_rate = rate.id
	LEFT JOIN contratos_modalidades ON contratos_modalidades.id_contratos_modalidad = contratos_examenes.id_contratos_modalidad
	LEFT JOIN contratos ON contratos.id_contratos = contratos_modalidades.id_contratos
	LEFT JOIN contratos_sedes ON contratos_sedes.id_contratos = contratos.id_contratos
	LEFT JOIN headquarter ON headquarter.id_headquarter = contratos_sedes.id_sede
	LEFT JOIN eps	ON	eps.id_eps = contratos.id_eps;    

ALTER TABLE public.rate_list_report
    OWNER TO postgres;
COMMENT ON VIEW public.rate_list_report
    IS 'Vista creada para mostrar los detalles de las tarifas de estudios en el RIS';            

GRANT ALL ON TABLE public.rate_list_report TO postgres;

DO 
$do$
BEGIN
   IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'metabase_user')     
   THEN
		GRANT SELECT ON TABLE public.rate_list_report TO metabase_user;
   END IF;
END
$do$;
