
ALTER TABLE appointment 
  DROP CONSTRAINT IF EXISTS appointment_id_appointment_status_fkey;
  
DROP VIEW IF EXISTS dim_status;  

DROP INDEX IF EXISTS fki_appointment_id_appointment_status_fkey;





CREATE TABLE public.appointment_status
(
    id_appointment_status integer NOT NULL,
    name character varying(40) NOT NULL,
    description character varying(500),
    display boolean NOT NULL DEFAULT true,
    CONSTRAINT appointment_status_id PRIMARY KEY (id_appointment_status),
    CONSTRAINT appointment_status_name_unique UNIQUE (name)
)
WITH (
    OIDS = FALSE
);

ALTER TABLE public.appointment_status
    OWNER to postgres;
COMMENT ON TABLE public.appointment_status
    IS 'Tabla Maestra para almacenar todos los estados utilizados en appointment';

COMMENT ON COLUMN public.appointment_status.id_appointment_status
    IS 'Identificador de la tabla.  Este valor se encuentra en el campo de status de la tabla appointment.';

COMMENT ON COLUMN public.appointment_status.name
    IS 'Nombre del estado.  Ejemplo: CANCELADA, VALIDADO, etc.';

COMMENT ON COLUMN public.appointment_status.description
    IS 'Descripción del estado.';

INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (0, 'CANCELADA', 'Cuando un paciente ha sido cancelado dentro del flujo.');
INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (1, 'PENDIENTE DE CONFIRMACIÓN', 'Es cuando se agenda un paciente y esta pendiente por confirmar la cita sea presencial o por telefono, este también se aplica para el listado pendiente de recepción.');
INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (2, 'CONFIRMADA', 'Este estado se usa solo para las citas que se confirman telefonicamente.');
INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (3, 'PENDIENTE DE ASISTENCIAL', 'En este estado se manejaba antes para alimentar el listado de actualización pero ya NO SE USA todo esta pasando con el estado REGISTRADO. No se puede borrar ya que los consecutivos de los procesos se perderian.');
INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (4, 'EN ASISTENCIAL', 'Este paso es el estado intermedio cuando un estudio esta en proceso de actualización.');
INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (5, 'PENDIENTE DE LECTURA', 'Es el estado que se usa para alimentar el listado del radiologo.');
INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (6, 'EN LECTURA', 'Es el estado intermedio cuando un estudio esta en el proceso de lectura del radiologo.');
INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (7, 'PENDIENTE DE TRANSCRIPCIÓN', 'Es el estado que alimenta el listado de transcripción.');
INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (8, 'EN TRANSCRIPCIÓN', 'Es el estado intermedio del proceso de transcripción de cada estudio.');
INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (9, 'PENDIENTE DE VALIDACIÓN', 'Es el estado que alimenta el listado de validación.');
INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (10, 'EN VALIDACIÓN', 'Es el estado intermedio del proceso de validación.');
INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (11, 'VALIDADO', 'Es el estado que determina que el estudio ya esta validado y es el estado que alimenta el listado de entrega de resultados.');
INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (12, 'EN ENTREGA', 'Es el estado intermedio del proceso de entrega de resultados.');
INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (13, 'ENTREGADO', 'Es el estado cuando ya se ha entregado el resultado del estudio al paciente.');
INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (14, 'ORDEN DE FACTURACIÓN', 'Este estado se usara en la parte de un proceso de facturación el cual no se ha desarrollado.');
INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (15, 'REGISTRADO', 'Es el estado que determina que un estudio esta registrado sea por que ya se ha recepcionado la cita o viene por cita prioritaria, este se maneja también para alimentar el listado de actualización.');
INSERT INTO appointment_status (id_appointment_status, name, description, display) VALUES (16, 'CANCELADA POR INASISTENCIA', 'Es el estado cuando el paciente no asiste a la cita.', false);
INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (17, 'ARCHIVO CLINICO', 'Es el estado de backup de integración del antiguo ris.');
INSERT INTO appointment_status (id_appointment_status, name, description, display) VALUES (18, 'PENDIENTE DE RECEPCIÓN', 'Es el estado que se presenta para alimentar el listado de los pacientes que estan para recepcionar.', false);
INSERT INTO appointment_status (id_appointment_status, name, description) VALUES (30, 'INVESTIGACIÓN', 'Es el estado que se presenta cuando se realizan ingresos que serán usados para la investigación.');

ALTER TABLE public.appointment
    ADD CONSTRAINT appointment_id_appointment_status_fkey FOREIGN KEY (appointment_status)
    REFERENCES public.appointment_status (id_appointment_status) MATCH SIMPLE
    ON UPDATE RESTRICT
    ON DELETE RESTRICT;
CREATE INDEX fki_appointment_id_appointment_status_fkey
    ON public.appointment(appointment_status);




DROP VIEW IF EXISTS reporte_citas;  -- Este paso es necesario para que evitar error al intentar eliminar la función que es usada por este reporte.

-- FUNCTION: public.convert_text_to_date(text)

-- DROP FUNCTION public.convert_text_to_date(text);

DROP FUNCTION IF EXISTS convert_text_to_date(text);

CREATE OR REPLACE FUNCTION convert_text_to_date(v_input text)
RETURNS date AS $$
DECLARE v_date_value date DEFAULT NULL;
BEGIN
    BEGIN
        v_date_value := v_input::date;
    EXCEPTION WHEN OTHERS THEN
        --RAISE NOTICE 'Invalid date value: "%".  Returning NULL.', v_input;
        RETURN NULL;
    END;
RETURN v_date_value;
END;
$$ LANGUAGE plpgsql;


-- View: public.addendums_report

-- DROP VIEW public.addendums_report;

DROP VIEW IF EXISTS addendums_report;

CREATE OR REPLACE VIEW public.addendums_report AS
    SELECT transcription.id_room_appointment_study_receipt AS id_cita,
        adendum.current AS addendum_date, 
        CAST(adendum.current AS date) AS addendum_day,
        user_val.username AS addendum_user,
        appointment_status AS status
    FROM public.adendum
    LEFT JOIN transcription USING(id_transcription)
    LEFT JOIN user_sonata user_val ON user_val.id = adendum.id_user
    LEFT JOIN	room_appointment_study_receipt ON	room_appointment_study_receipt.id_room_appointment_study_receipt = transcription.id_room_appointment_study_receipt 
    LEFT JOIN	appointment	ON	appointment.appointment_code = room_appointment_study_receipt.appointment_code
    ORDER BY    adendum.current ASC;

ALTER TABLE public.addendums_report
    OWNER TO postgres;
COMMENT ON VIEW public.addendums_report
    IS 'Vista creada para mostrar los Addendum creados en el RIS';        

GRANT ALL ON TABLE public.addendums_report TO postgres;

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

-- View: public.cancellations_report

-- DROP VIEW public.cancellations_report;

DROP VIEW IF EXISTS cancellations_report;

CREATE OR REPLACE VIEW public.cancellations_report AS
    SELECT room_appointment_study_receipt.id_room_appointment_study_receipt AS id_cita,
            cancelacion_cita.current AS cancelation_date,
            cancelacion_cita.current::date AS cancelation_day,
            cancelacion_cita.description AS description, 
            cancelacion_cita.username AS cancelation_user, 
            cancelacion_cita.previous_status AS previous_status
    FROM room_appointment_study_receipt     
    JOIN appointment USING (appointment_code)
    LEFT JOIN ( SELECT 
                       CASE WHEN schedule_logs.action::text = 'Modificación de estado'::text
                                THEN
                                     REPLACE(REPLACE(CAST(description::json -> 'Estado_old' AS text), '"',''), '\u00d3','Ó')
               				WHEN schedule_logs.action::text = 'CANCELACION'::text
                                THEN
               						'AGENDA'::text
                                ELSE
                                    NULL	
                            END AS previous_status,
                        CASE WHEN schedule_logs.action::text = 'Modificación de estado'::text
                            THEN
                                CAST(description::json -> 'Descripcion' AS text)
                            ELSE
                                description --
                            END AS description,                             
                        schedule_logs.appointment_code,
                       schedule_logs.current,
                        user_sonata.username
               FROM ( SELECT MAX(schedule_logs_1.id) AS id
                       FROM schedule_logs schedule_logs_1
                      WHERE schedule_logs_1.action::text = 'CANCELACION'::text 
                            OR
                            (schedule_logs_1.action::text = 'Modificación de estado'::text AND upper(schedule_logs_1.description) LIKE '%ESTADO":"CANCELADA"%')
                      GROUP BY schedule_logs_1.appointment_code) last_comment
                 JOIN schedule_logs USING (id)
                 JOIN user_sonata ON user_sonata.id = schedule_logs.id_user) cancelacion_cita USING (appointment_code)             
    WHERE appointment.appointment_status =0 
    ORDER BY	cancelacion_cita.current ASC;

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

GRANT ALL ON TABLE public.cancellations_report TO postgres;

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

   
-- DROP VIEW public.dim_status;

DROP VIEW IF EXISTS dim_status;

CREATE OR REPLACE VIEW public.dim_status AS
    SELECT id_appointment_status, name, description
	FROM public.appointment_status
    where display = true
    ORDER BY	id_appointment_status;

ALTER TABLE public.dim_status
    OWNER TO postgres;
COMMENT ON VIEW public.dim_status
    IS 'Vista creada para mostrar los estados del RIS que se encuentren activos.  Esta vista es usada por la herramienta de BI';    

GRANT ALL ON TABLE public.dim_status TO postgres;

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


-- View: public.readings_report

-- DROP VIEW public.readings_report;

DROP VIEW IF EXISTS readings_report;

CREATE OR REPLACE VIEW public.readings_report AS
    (SELECT        
        user_read.username AS reading_user,
        auxiliary_transcription.current AS reading_date,
        auxiliary_transcription.current::date AS reading_day,
        transcription.id_room_appointment_study_receipt AS id_cita,
        'Si'::text AS addition_reading,
     	appointment.appointment_status
   FROM auxiliary_transcription
     LEFT JOIN transcription USING (id_transcription)
     LEFT JOIN user_sonata user_read ON user_read.id = auxiliary_transcription.id_user
     LEFT JOIN	room_appointment_study_receipt	ON	room_appointment_study_receipt.id_room_appointment_study_receipt = transcription.id_room_appointment_study_receipt
     LEFT JOIN	appointment	ON	room_appointment_study_receipt.appointment_code =	appointment.appointment_code)     
UNION ALL
     (SELECT        
         user_read.username AS reading_user,
         recording.current AS reading_date,
         recording.current::date AS reading_day,
         room_appointment_study_receipt.id_room_appointment_study_receipt AS id_cita,
         'No'::text AS addition_reading,
      	 appointment.appointment_status
     FROM room_appointment_study_receipt
     LEFT JOIN recording ON recording.id_room_appointment_study_receipt = room_appointment_study_receipt.id_room_appointment_study_receipt   	
     LEFT JOIN user_sonata user_read ON user_read.id = recording.id_user
     LEFT JOIN	appointment	ON	room_appointment_study_receipt.appointment_code =	appointment.appointment_code)
     ORDER BY reading_date ASC;

ALTER TABLE public.readings_report
    OWNER TO postgres;
COMMENT ON VIEW public.readings_report
    IS 'Vista creada para mostrar las lecturas del RIS, incluye las lecturas normales y las adicionales.  Esta vista es usada por la herramienta de BI';        

GRANT ALL ON TABLE public.readings_report TO postgres;

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

        


-- View: public.reporte_citas

-- DROP VIEW public.reporte_citas;

DROP VIEW IF EXISTS reporte_citas;

CREATE OR REPLACE VIEW public.reporte_citas AS
SELECT room_appointment_study_receipt.id_room_appointment_study_receipt AS id_cita,  	
    room_appointment_study_receipt.validate_datetime AS fecha_validacion,
    CAST(room_appointment_study_receipt.validate_datetime AS date) AS dia_validacion,
    user_val.username AS usuario_validacion,    
    recording.current as fecha_lectura,
    recording.current::date AS dia_lectura,
    user_lectura.username as usuario_lectura,
    entrega_resultado.fecha_entrega as fecha_entrega,
    entrega_resultado.fecha_entrega::date as dia_entrega,
    user_entrega.username as usuario_entrega,
    appointment.current as fecha_recepcion,
    appointment.current::date as dia_recepcion,
    user_receipt.username AS usuario_recepcion,    
    appointment.appointment_date AS fecha_cita,
    CAST(appointment.appointment_date AS date) AS dia_cita,
    eps.name AS nombre_eps,
    appointment.appointment_status AS estado,
    appointment.datetime_asistencial AS fecha_asistencial,
    CAST(appointment.datetime_asistencial AS date) AS dia_asistencial,
    user_asistencial.username AS usuario_asistencial,
    provenance_date.name AS origen,
    priority.name AS prioridad,    
    internal_order.doctor_referring AS medico_remitente,
    internal_order.document_doctor_referring AS documento_medico_remitente,
    internal_order.codigo_cie10,
    cie_10.nombre_cie_10 AS nombre_cie10,
    room_appointment_study_receipt.birads AS birads,
    patient.contact_name AS nombre_acudiente,
    patient.contact_telephone AS telefono_acudiente,
    patient.weight AS peso_paciente,
    municipality.municipality_name AS municipio_paciente,
    department.department_name AS departamento_paciente,
    person_data.id_document_type AS tipo_documento_paciente,
    person_data.document AS documento_paciente,
    person_data.first_name AS primer_nombre_paciente,
    person_data.second_name AS segundo_nombre_paciente,
    person_data.first_last_name AS primer_apellido_paciente,
    person_data.second_last_name AS segundo_apellido_paciente,
    REPLACE(CONCAT_WS(' ',person_data.first_name, person_data.second_name, person_data.first_last_name, person_data.second_last_name), '  ', ' ') AS nombre_completo_paciente,
    person_data.date_birth AS fecha_nacimiento_paciente,
    EXTRACT(YEAR FROM age(appointment.datetime_asistencial,person_data.date_birth)) AS edad_anios,    
    round((EXTRACT(days FROM (appointment.datetime_asistencial  - person_data.date_birth)) / 7)::DECIMAL, 2) as edad_semanas,
    person_data.telephone AS telefono_paciente,
    person_data.email AS email_paciente,
    person_data.cellphone AS celular_paciente,
    person_data.address AS direccion_paciente,
    person_data.sex AS sexo_paciente,
    asignacion_cita.username AS usuario_asignacion_cita,
    asignacion_cita.current AS fecha_asignacion_cita,
    CAST(asignacion_cita.current AS date) AS dia_asignacion_cita,    
    convert_text_to_date(appointment.app_custom3::text) AS dia_deseado_ingreso,
    asignacion_cita.description AS comentario_asignacion_cita,
    confirmacion_cita.username AS usuario_confirmacion_cita,
    confirmacion_cita.current AS fecha_confirmacion_cita,
    CAST(confirmacion_cita.current AS date) AS dia_confirmacion_cita,
    confirmacion_cita.description AS comentario_confirmacion_cita,   
    transcription.current AS fecha_transcripcion,
    CAST(transcription.current AS date) AS dia_transcripcion,
    user_transcripcion.username AS usuario_transcripcion,
    room.id_modality AS modalidad,
    room.room_name AS nombre_sala,
    headquarter.headquarter_name AS nombre_sede,
    plates_registry_j.plates_good AS placas_buenas,
    plates_registry_j.plates_bad AS placas_malas,
    plates_registry_j.number_exposures AS numero_disparos,
    plates_registry_j.over_exposure AS "sobre_exposición",
    plates_registry_j.reason_overexposure AS motivo_sobre_exposicion,
    plates_registry_j.amount_mas AS mas,
    plates_registry_j.amount_ma AS ma,
    plates_registry_j.amount_kva AS kva,
    plates_registry_j.weight AS peso_asistencial,
    usuario_placas.username AS usuario_placas,
    remitente2.document AS documento_medico_remitente2,
    remitente2.first_name AS nombre_medico_remitente2,
    remitente2.last_name AS apellid_medico_remitente2,
    appointment.appointment_date::date - convert_text_to_date(appointment.app_custom3::text) as cita_deseada_dias,
    EXTRACT(EPOCH FROM (appointment.appointment_date - asignacion_cita.current)) AS cita_asignacion_seg,
    EXTRACT(EPOCH FROM (appointment.datetime_asistencial - appointment.appointment_date)) AS asistencial_cita_seg,
    EXTRACT(EPOCH FROM (appointment.datetime_asistencial - appointment.current)) AS asistencial_recepcion_seg,    
    EXTRACT(EPOCH FROM (recording.current - appointment.datetime_asistencial)) AS lectura_asistencial_seg,
    EXTRACT(EPOCH FROM (transcription.current - recording.current)) AS transcripcion_lectura_seg,
    EXTRACT(EPOCH FROM (room_appointment_study_receipt.validate_datetime - transcription.current)) AS validacion_transcripcion_seg,
    EXTRACT(EPOCH FROM (entrega_resultado.fecha_entrega - room_appointment_study_receipt.validate_datetime)) AS entrega_validacion_seg
   FROM room_appointment_study_receipt
     LEFT JOIN user_sonata user_val ON user_val.id = room_appointment_study_receipt.id_user_ras
     JOIN appointment USING (appointment_code)
     LEFT JOIN eps USING (id_eps)
     LEFT JOIN user_sonata user_asistencial ON appointment.id_user_assistencial = user_asistencial.id
     LEFT JOIN provenance_date ON provenance_date.id = appointment.id_provenance_date
     LEFT JOIN priority ON priority.id = appointment.id_priority
     LEFT JOIN user_sonata user_receipt ON appointment.id_user_receipt = user_receipt.id
     LEFT JOIN internal_order USING (internal_order_code)
     LEFT JOIN cie_10 ON internal_order.codigo_cie10::text = cie_10.codigo_cie_10::text
     LEFT JOIN patient ON patient.id_patient = appointment.id_patient
     LEFT JOIN person_data ON person_data.id_person = patient.id_person
     LEFT JOIN municipality USING (municipality_code)
     LEFT JOIN department USING (department_code) 
     LEFT JOIN ( SELECT schedule_logs.current,
            schedule_logs.description,
            schedule_logs.appointment_code,
            user_sonata.username
           FROM ( SELECT MAX(schedule_logs_1.id) AS id
                   FROM schedule_logs schedule_logs_1
                  WHERE schedule_logs_1.action::text = 'ASIGNACION'::text
                  GROUP BY schedule_logs_1.appointment_code) first_comment
             JOIN schedule_logs USING (id)
             JOIN user_sonata ON user_sonata.id = schedule_logs.id_user) asignacion_cita USING (appointment_code)
     LEFT JOIN ( SELECT schedule_logs.current,
            schedule_logs.description,
            schedule_logs.appointment_code,
            user_sonata.username
           FROM ( SELECT MAX(schedule_logs_1.id) AS id
                   FROM schedule_logs schedule_logs_1
                  WHERE schedule_logs_1.action::text = 'CONFIRMACION'::text
                  GROUP BY schedule_logs_1.appointment_code) first_comment
             JOIN schedule_logs USING (id)
             JOIN user_sonata ON user_sonata.id = schedule_logs.id_user) confirmacion_cita USING (appointment_code)    
     LEFT JOIN transcription USING (id_room_appointment_study_receipt)
     LEFT JOIN user_sonata user_transcripcion ON transcription.id_user = user_transcripcion.id
     LEFT JOIN room ON room.room_code = room_appointment_study_receipt.room_code
     LEFT JOIN headquarter ON headquarter.id_headquarter = room.id_headquarter
     LEFT JOIN ( SELECT plates_registry.appointment_code,
            min(plates_registry.id_user_tecno) AS id_user_tecno,
            sum(plates_registry.plates_good) AS plates_good,
            sum(plates_registry.plates_bad) AS plates_bad,
            sum(plates_registry.number_exposures) AS number_exposures,
            string_agg(plates_registry.over_exposure::text, ', '::text) AS over_exposure,
            string_agg(plates_registry.reason_overexposure::text, ', '::text) AS reason_overexposure,
            string_agg(plates_registry.amount_mas::text, ', '::text) AS amount_mas,
            string_agg(plates_registry.amount_ma::text, ', '::text) AS amount_ma,
            string_agg(plates_registry.amount_kva::text, ', '::text) AS amount_kva,
            max(plates_registry.weight::text) AS weight
           FROM plates_registry
          GROUP BY plates_registry.appointment_code) plates_registry_j USING (appointment_code)
     LEFT JOIN user_sonata usuario_placas ON plates_registry_j.id_user_tecno = usuario_placas.id
     LEFT JOIN out_site_user remitente2 ON remitente2.id = internal_order.id_out_site_user
     LEFT JOIN recording ON recording.id_room_appointment_study_receipt = room_appointment_study_receipt.id_room_appointment_study_receipt 
     LEFT JOIN user_sonata user_lectura ON user_lectura.id = recording.id_user
     LEFT JOIN (SELECT id_transcription, 
                TO_TIMESTAMP(dates || ' ' || "time", 'YYYY-MM-DD HH24:MI:SS')::timestamp without time zone fecha_entrega, 
                id_user,
                ROW_NUMBER () OVER (PARTITION BY id_transcription ORDER BY dates || ' ' || "time" DESC) as ranking
                FROM  data_result) AS entrega_resultado ON entrega_resultado.id_transcription = transcription.id_transcription 
     LEFT JOIN user_sonata user_entrega ON user_entrega.id = entrega_resultado.id_user
     WHERE  COALESCE(entrega_resultado.ranking,1) = 1
     		AND	appointment.appointment_status <> 14;
     
ALTER TABLE public.reporte_citas
    OWNER TO postgres;

GRANT ALL ON TABLE public.reporte_citas TO postgres;

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

-- View: public.devolutions_report

-- DROP VIEW public.devolutions_report;
DROP VIEW IF EXISTS devolutions_report;

CREATE OR REPLACE VIEW public.devolutions_report AS
 SELECT devolution_historic.id_devolution_historic AS id_devolution,
    room_appointment_study_receipt.id_room_appointment_study_receipt AS id_cita,
    devolution_historic.current AS devolution_date,
    devolution_historic.current::date AS devolution_day,
    devolution_historic.next_step AS state,
    devolution_historic.motive_devolution AS devolution_motive,
    priority.name AS priority_name,    
    user_devolution.username AS devolution_user,
    user_responsible.username AS responsible_user,
    devolution_historic.comment_new_send_technologist AS technologist_comment,
    devolution_historic.comment_new_send_radiologist AS radiologist_comment,
    devolution_historic.comment_new_send_transcription AS transcription_comment,
    appointment.appointment_status
   FROM devolution_historic
     LEFT JOIN user_sonata user_devolution ON user_devolution.id = devolution_historic.id_user
     LEFT JOIN user_sonata user_responsible ON user_responsible.id = devolution_historic.id_user_last_process
     LEFT JOIN appointment USING (appointment_code)
     LEFT JOIN priority ON priority.id = appointment.id_priority
     LEFT JOIN room_appointment_study_receipt USING (appointment_code)
   ORDER BY devolution_historic.current ASC;
   
ALTER TABLE public.devolutions_report
    OWNER TO postgres;
COMMENT ON VIEW public.devolutions_report
    IS 'Vista creada para mostrar las devoluciones realizadas a través del módulo de Devoluciones.  Esta vista es usada por la herramienta de BI';        

GRANT ALL ON TABLE public.devolutions_report TO postgres;

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


-- 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.id_modality AS modalidad,
    study.clinical AS datos_clinicos,
    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
   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$;

