DROP MATERIALIZED VIEW IF EXISTS asignacion_cita_view_m;

CREATE MATERIALIZED VIEW asignacion_cita_view_m AS
SELECT asignacion_cita.current,
		asignacion_cita.description,
        appointment.appointment_code,
        asignacion_cita.username
FROM appointment
INNER JOIN (SELECT schedule_logs.current,
            schedule_logs.description,
            schedule_logs.appointment_code,
            user_sonata.username, 
			row_number() OVER (PARTITION BY schedule_logs.appointment_code ORDER BY schedule_logs.id DESC) AS ranking
           FROM schedule_logs
		   LEFT JOIN user_sonata ON user_sonata.id = schedule_logs.id_user
		   WHERE schedule_logs.action = 'ASIGNACION') as  asignacion_cita ON asignacion_cita.appointment_code = appointment.appointment_code
WHERE COALESCE(asignacion_cita.ranking,1) = 1 AND  asignacion_cita.appointment_code IS NOT NULL
WITH DATA;

CREATE UNIQUE INDEX idx_asignacion_cita_view_m_appointment_code  ON asignacion_cita_view_m (appointment_code);

-- Refrescar la vista materializada
--REFRESH MATERIALIZED VIEW concurrently asignacion_cita_view_m;