DROP MATERIALIZED VIEW IF EXISTS entrega_resultados_view_m;

CREATE MATERIALIZED VIEW entrega_resultados_view_m AS
select entrega_resultado.id_transcription,
	   entrega_resultado.fecha_entrega,
	   user_entrega.username 
from transcription
	 left join (SELECT id_transcription, id_user,
                TO_TIMESTAMP(dates || ' ' || "time", 'YYYY-MM-DD HH24:MI:SS')::timestamp without time zone fecha_entrega,                	
                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 entrega_resultado.id_transcription IS NOT NULL
WITH DATA;

CREATE UNIQUE INDEX idx_entrega_resultados_view_m_id_transcription ON entrega_resultados_view_m (id_transcription);


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