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,
    entrega_resultado.username as usuario_entrega,	
	(SELECT value FROM parameters_admin WHERE key = 'URL_REDIRECT_TO_APPOINTMENT') || appointment.appointment_code as url_ris,
    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,
	CASE 
		WHEN asignacion_cita.current IS NULL 
		THEN 'Sí' 
		ELSE 'No'
	END	AS atencion_inmediata,
    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,
    EXTRACT(EPOCH FROM (NOW() - appointment.current)) AS tiempo_desde_registrado_seg,
    EXTRACT(EPOCH FROM (NOW() - appointment.datetime_asistencial)) AS tiempo_desde_asistencial_seg,
    EXTRACT(EPOCH FROM (NOW() - recording.current)) AS tiempo_desde_lectura_seg,
    EXTRACT(EPOCH FROM (NOW() - transcription.current)) AS tiempo_desde_transcripcion_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 asignacion_cita_view_m asignacion_cita USING (appointment_code)
     LEFT JOIN confirmacion_cita_view_m 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 entrega_resultados_view_m entrega_resultado ON entrega_resultado.id_transcription = transcription.id_transcription
     WHERE  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$;