SELECT

	spool.id,

	handle.handle,

	spool.name,

	thread.id,

	notification.read,

	notification2.time,

	message.message,

	message.time,

	message.author,

	thread.name,

	"location".geo 

FROM

	spool

	JOIN handle ON handle.id = spool.id

	JOIN thread ON thread.spool = spool.id

	JOIN message ON message.thread = thread.id

	LEFT JOIN "location" ON "location".id = spool.location

	LEFT JOIN notification ON notification.user = 'b16690e4-a3c5-4868-945e-c2458c27a525'

		AND 

			notification.id = (

			SELECT

				notification3.id 

			FROM

				notification AS notification3

				JOIN notification_thread ON notification_thread.id = notification3.id

				JOIN thread AS thread2 ON thread2.id = notification_thread.thread 

			WHERE

				thread2.spool = spool.id

				AND notification3.user = 'b16690e4-a3c5-4868-945e-c2458c27a525'

				AND notification3.time <= '2020-10-30 16:32:38.054558'

			ORDER BY

				notification3.time DESC 

			LIMIT 1 

			)

	LEFT JOIN notification AS notification2 ON notification2.user = 'b16690e4-a3c5-4868-945e-c2458c27a525'

		AND notification2.id = (

			SELECT

				notification3.id 

			FROM

				notification AS notification3

				JOIN notification_thread ON notification_thread.id = notification3.id

				JOIN thread AS thread2 ON thread2.id = notification_thread.thread 

			WHERE

				thread2.spool = spool.id

				AND notification3.user = 'b16690e4-a3c5-4868-945e-c2458c27a525'

				AND notification3.time > '2020-10-30 16:32:38.054558'

			ORDER BY

				notification3.time DESC 

			LIMIT 1 

		) 

WHERE

		message.time = (

			SELECT

				MAX ( message2.time ) 

			FROM

				message AS message2

				JOIN thread AS thread2 ON thread2.id = message2.thread

				JOIN participant ON participant.thread = thread2.id

				JOIN identity ON identity.id = participant.identity

				LEFT JOIN relation ON relation.to = identity.id

					AND relation.from = 'b16690e4-a3c5-4868-945e-c2458c27a525'

					AND relation.manages = TRUE

			WHERE

				NOT message2.draft 

				AND ( identity.id = 'b16690e4-a3c5-4868-945e-c2458c27a525' OR NOT relation.to IS NULL )

				AND thread2.spool = spool.id

			LIMIT 1

		) 

	AND notification.id IS NOT NULL

ORDER BY

	message.time DESC 

LIMIT 31;