SQL query for chat application

I'm really struggling to understand how to write a query. There are two main tables for a chat feature. The UserConversation table holds the sender and recipient id's for a two way chat. The UserMessage table holds the corresponding messages. The User and UserProfile tables are needed just to pull some extra details from. Here is the table structure:

UserConversation

User

UserProfile

UserMesage

I'm trying to achieve the following output:

A few notes:

1. Search the UserConversation table for occurrences where JohnDoe is the SenderId or RecipientId, but I want to return the user details of the other user.
2. Count MessageRead column on the UserMessages table for each conversation, but only where the searched user (JohnDoe) is the message recipient.
3. Order the results by the most recent CreateDate

I haven't gotten very far, here is what I have so far.

select 
    uc.Id as Id,
    us.Id as UserId,
    us.UserName as Username,
    ups.AvatarImage as UserAvatar
from UserConversation uc 
join UserMessage um on uc.Id = um.ConversationId
join [User] us on uc.SenderId = us.Id
join [User] ur on uc.RecipientId = ur.Id
join UserProfile ups on us.Id = ups.UserId
join UserProfile upr on ur.Id = upr.UserId
where uc.SenderId = 1001 or uc.RecipientId = 1001

Since you want the rows with JohnDoe as either sender or recipient along with the other participant you should join User and UserProfile tables twice (once for sender and other for recipient). Having that done you can do aggregations and grouping and some case expressions (or Decode() function or anything else similar) to take the right data for your expected result. I don't know T-SQL but it's syntax is similar enough to Oracle's SQL. I'm sure you could adopt the code below and transcript it to T-SQL.

WITH       --  S a m p l e    D a t a :
    UserConversation (Id,   SenderId,   SenderDeleteDate,   RecipientId,    RecipientDeleteDate,    CreateDate) AS
        ( Select 1, 1002,   NULL,   1001,   NULL,   TIMESTAMP '2023-12-23 14:14:13.1152723 +07:00' From Dual Union All      
          Select 2, 1001,   NULL,   1003,   NULL,   TIMESTAMP '2023-12-23 14:15:20.1264302 +07:00' From Dual Union All
          Select 3, 1001,   NULL,   1004,   NULL,   TIMESTAMP '2023-12-23 14:16:57.4302621 +07:00' From Dual
        ),
    Users (Id,  UserName) AS
        ( Select 1001,  'JohnDoe' From Dual Union All
          Select 1002,  'BenDover' From Dual Union All
          Select 1003,  'JakeSmith' From Dual Union All
          Select 1004,  'KrisKringle' From Dual
        ),
    UserProfile (UserId,    Avatar) AS
        ( Select 1001,  'avatar/image/1001.jpg' From Dual Union All
          Select 1002,  'avatar/image/1002.jpg' From Dual Union All
          Select 1003,  'avatar/image/1003.jpg' From Dual Union All
          Select 1004,  'avatar/image/1004.jpg' From Dual 
        ),
    UserMessage (ConversationId,    SenderId,   RecipientId,    MessageRead,    Message,    CreateDate) AS
        ( Select 1, 1002,   1001,   0,  'Hello',    To_Date('2023-12-22 13:00:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
          Select 1, 1001,   1002,   0,  'Hi there', To_Date('2023-12-22 13:30:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
          Select 2, 1001,   1003,   1,  'Merry Christmas', To_Date('2023-12-22 14:00:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
          Select 2, 1003,   1001,   1,  'Same to you',  To_Date('2023-12-22 14:30:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
          Select 3, 1001,   1004,   1,  'Grab some beers?', To_Date('2023-12-22 15:00:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
          Select 3, 1004,   1001,   0,  'Sure', To_Date('2023-12-22 15:30:00', 'yyyy-mm-dd hh24:mi:ss') From Dual
        )

Oracle:

--  M a i n    S Q L :
Select      c.ID "CONVERSATION_ID", 
            Max(Case When usnd.USERNAME = 'JohnDoe' Then urcp.ID Else usnd.ID End) "USER_ID", 
            Max(Case When usnd.USERNAME = 'JohnDoe' Then urcp.USERNAME Else usnd.USERNAME End) "USER_NAME", 
            Max(Case When usnd.USERNAME = 'JohnDoe' Then uprcp.AVATAR Else upsnd.AVATAR End) "USER_AVATAR",
            To_Char(Max(um.CREATEDATE), 'yyyy-mm-dd hh24:mi:ss') "LATEST_DATE",
            Count(Case When urcp.USERNAME = 'JohnDoe' And um.MessageRead = 0 Then 1 End) "UNREAD_COUNT"
From        UserMessage um
Inner Join  UserConversation c ON(um.ConversationId = c.ID)
Inner Join  Users usnd ON(usnd.ID = um.SENDERID)
Inner Join  Users urcp ON(urcp.ID = um.RECIPIENTID)
Inner Join  UserProfile upsnd ON(upsnd.USERID = c.SENDERID)
Inner Join  UserProfile uprcp ON(uprcp.USERID = c.RECIPIENTID)
Where       'JohnDoe' IN(usnd.USERNAME, urcp.USERNAME)
Group By    c.ID
Order By    c.ID Desc

/*      R e s u l t :
CONVERSATION_ID    USER_ID USER_NAME   USER_AVATAR           LATEST_DATE         UNREAD_COUNT
--------------- ---------- ----------- --------------------- ------------------- ------------
              3       1004 KrisKringle avatar/image/1004.jpg 2023-12-22 15:30:00            1
              2       1003 JakeSmith   avatar/image/1003.jpg 2023-12-22 14:30:00            0
              1       1002 BenDover    avatar/image/1002.jpg 2023-12-22 13:30:00            1   */