-- ** TITULO      : ALTER reporte_estudios
-- ** OBJETIVO    : Agregar el valor del estudio a la vista de estudios.
-- ** DESCRIPCION : 
--                  Se elimina y se vuelve a crear la vista reporte_citas.
--                  Se le dan permisos al usuario metabase_user.

-- View: public.reporte_estudios

-- DROP VIEW public.reporte_estudios;

DROP VIEW IF EXISTS reporte_estudios;

CREATE OR REPLACE VIEW public.reporte_estudios AS
 SELECT study.id_room_appointment_study_receipt AS id_cita, 	
    study.study_name AS nombre_estudio,
    study.cups,
	study.final_value AS valor_final_estudio,
    study.id_modality AS modalidad,
    study.clinical AS datos_clinicos,
	contratos_examenes.valor_resultante AS valor_resultante_contratos_examenes,
    appointment.appointment_status AS status
   FROM study
   LEFT JOIN	room_appointment_study_receipt	ON room_appointment_study_receipt.id_room_appointment_study_receipt = study.id_room_appointment_study_receipt
   LEFT JOIN	appointment	ON	appointment.appointment_code = room_appointment_study_receipt.appointment_code
   LEFT JOIN contratos_examenes ON contratos_examenes.id_contratos_examenes = study.id_contratos_examenes
   WHERE	appointment.appointment_status <> 14;

ALTER TABLE public.reporte_estudios
    OWNER TO postgres;
    
GRANT ALL ON TABLE public.reporte_estudios TO postgres;

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