WITH 
-- Transpose rows from/to columns for each user
transpose as (
  SELECT 
    u.userMessageTo AS userId,
    u.userMessageFrom AS partyUserId,
    u.userMessageId AS msgId,
    u.userCreated AS createdOn
  FROM users_messages u
  WHERE u.userMessageToDeleted = 0
  UNION
  
  SELECT 
    u.userMessageFrom AS userId,
    u.userMessageTo AS partyUserId,
    u.userMessageId AS msgId,
    u.userCreated AS createdOn
  FROM users_messages u
  WHERE u.userMessageFromDeleted = 0
),
-- Find last message for each thread
last_msg as (
  SELECT 
    t.userId,
    t.partyUserId,
    MAX(t.msgId) AS lastMsgId,
    MAX(t.createdOn) AS lastMsgDate
  FROM transpose t
  GROUP BY 
    userId,
    partyUserId
  ORDER BY lastMsgDate DESC
)
-- Build conversation list with content of last message in thread
SELECT 
  lm.userId,
  lm.partyUserId AS conversationWithId,
  lm.lastMsgDate AS lastMsgDate,
  m.userMessageContent AS lastMsgText,
  CASE 
    WHEN lm.userId = m.userMessageFrom THEN 'Sent'
    ELSE 'Received'
  END AS LastMsgDirection,
  -- Check if user has unread messages
  CASE 
    WHEN lm.userId = m.userMessageFrom THEN 0
    ELSE 1 - m.userMessageToRead
  END AS hasUnreadMsgFlag,
  -- Check if receiver has read message
  CASE 
    WHEN lm.userId = m.userMessageFrom THEN m.userMessageToRead
    ELSE 0
  END AS receiverReadFlag,
  u.userName AS conversationWithName,
  u.userEnum AS conversationWithEnum,
  u.userAvatar AS conversationWithAvatar
FROM last_msg lm
  JOIN users_messages m ON lm.lastMsgId = m.userMessageId
  JOIN users u ON lm.partyUserId = u.userUniqueId
ORDER BY userId, lastMsgDate;