-- ** TITULO      : Crear la vista integration_report.
-- ** OBJETIVO    : Crear la vista que permite consultar los datos de la tabla de integración: ti_hisris.
-- ** DESCRIPCION : 
--                 Se crea la columna bandera_oru si no existe
--                 Se crea la columna obsintegracion si no existe
--                 Se elimina la vista integration_report si existe.
--                 Se crea la vista integration_report.

ALTER TABLE public.ti_hisris ADD COLUMN IF NOT EXISTS bandera_oru character varying(1) DEFAULT '0';

ALTER TABLE public.ti_hisris ADD COLUMN IF NOT EXISTS obsintegracion character varying(10000);

-- View: public.integration_report

DROP VIEW IF EXISTS integration_report;

CREATE OR REPLACE VIEW public.integration_report AS
SELECT
    room_appointment_study_receipt.id_room_appointment_study_receipt as id_addmission,
    ti_hisris.fuente as source,
    ti_hisris.origen as source_order,
    ti_hisris.fechas as request_datetime,
	 ti_hisris.fechas::date as request_date,
    ti_hisris.codigounico as integration_code,
    CASE
		WHEN ti_hisris.esthis = 'OR' THEN 'EN SOLICITUD'
		WHEN ti_hisris.esthis = 'RE' THEN 'REPORTE ENVIADO'
		WHEN ti_hisris.esthis = 'CA' THEN 'CANCELADO'
		WHEN ti_hisris.esthis = 'PR' THEN 'RECEPCIONADO'
		WHEN ti_hisris.esthis = 'PL' THEN 'PENDIENTE LECTURA'
		WHEN ti_hisris.esthis = 'LE' THEN 'LECTURA ENVIADA'
		ELSE ti_hisris.esthis
		END as integration_status,  
    ti_hisris.appointment_code as appointment_code,
    ti_hisris.nroide as integration_document_patient,
	ti_hisris.nrosol as nrosol,
    ti_hisris.bandera_oru as oru_flag,
    ti_hisris.obsintegracion as observations,
	ti_hisris.concepto as concept,	
	ti_hisris.codemp as codemp,
	ti_hisris.docrem as docrem,
	ti_hisris.nomrem as nomrem,
	ti_hisris.nomser as nomser,
	ti_hisris.medico as medico,
	ti_hisris.nommed,
	CASE 
		WHEN ti_hisris.studyiuid = '' THEN NULL
		ELSE ti_hisris.studyiuid
	END AS studyiuid,
	ti_hisris.iddiagnostico,
	ti_hisris.tipoatencion,
	ti_hisris.tipmed,
	ti_hisris.codact,
	ti_hisris.nomact,
	ti_hisris.obsord,
	ti_hisris.contraste,
	ti_hisris.nrodoc,
	ti_hisris.histor,
	ti_hisris.nroide,
    ti_hisris.codrad as codrad,
	appointment.appointment_status AS ris_status,
	appointment.datetime_asistencial AS asistencial_datetime,
	CAST(appointment.datetime_asistencial AS date) AS asistencial_date,
	room_appointment_study_receipt.validate_datetime AS validation_datetime,
	CAST(room_appointment_study_receipt.validate_datetime AS date) AS validation_date,
   EXTRACT(EPOCH FROM (appointment.datetime_asistencial - ti_hisris.fechas::TIMESTAMP)) AS tiempo_asistencial_fechahis_seg,
   EXTRACT(EPOCH FROM (room_appointment_study_receipt.validate_datetime - ti_hisris.fechas::TIMESTAMP)) AS tiempo_validacion_fechahis_seg
FROM ti_hisris
LEFT JOIN room_appointment_study_receipt ON room_appointment_study_receipt.appointment_code = ti_hisris.appointment_code
LEFT JOIN appointment ON appointment.appointment_code = room_appointment_study_receipt.appointment_code;

ALTER TABLE public.integration_report
    OWNER TO postgres;
COMMENT ON VIEW public.integration_report
    IS 'Vista creada para mostrar los detalles de las integraciones';            

GRANT ALL ON TABLE public.integration_report TO postgres;

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