data-structure

user (
  id,
  created
)

group (
  id
)

group_user (
  group_id,
  user_id,
  created
)

message (
  id,
  created,
  subject,
  body,
  type
)

message_user (
  id,
  user_id,
  message_id,
  read
)

message_global (
  id,
  message_id
)

message_group (
  id,
  group_id
)

message_status (
  id,
  user_id,
  message_id,
  read (datetime, null),
  deleted (datetime, null),
  created (datetime, current_timestamp)
)

unread-message-count.sql

# user messages

with this_user as (
  select
    id,
    created
  from
    user
  where
    id = @user
)
select
  m.*,
  ms.read,
  ms.deleted,
  case
    when ms.read is null then 0
    else 1
  end as is_read
from
  (
  # direct messages
  select
    message_id
  from
    message_user
  where
    user_id = this_user.id

  union all

  # global messages
  select
    mg.message_id
  from
    message_global mg
    inner join message m on m.id = mg.message_id
  where
    m.created > this_user.created

  union all

  # group messages
  select
    mg.message_id
  from
    message_group mg
    inner join message m on mg.message_id = m.id
    inner join group g on mg.group_id = g.id
    inner join group_user gu on g.id = gu.group_id
  where
    gu.user_id = this_user.id
    and gu.created > m.created ) ids
  inner join message m on m.id = ids.message_id
  left join message_status ms on ms.message_id = m.id and ms.user_id = this_user.id
order by
  m.created desc;



# unread message count

with this_user as (
  select
    id,
    created
  from
    user
  where
    id = @user
)
select
  count(*)
from
  (
  # direct messages
  select
    message_id
  from
    message_user
  where
    user_id = this_user.id

  union all

  # global messages
  select
    mg.message_id
  from
    message_global mg
    inner join message m on m.id = mg.message_id
  where
    m.created > this_user.created

  union all

  # group messages
  select
    mg.message_id
  from
    message_group mg
    inner join message m on mg.message_id = m.id
    inner join group g on mg.group_id = g.id
    inner join group_user gu on g.id = gu.group_id
  where
    gu.user_id = this_user.id
    and gu.created > m.created ) ids
  inner join message m on m.id = ids.message_id
  left join message_status ms on ms.message_id = m.id and ms.user_id = this_user.id
where
  ms.read is null

# send message (user to user)

@message_id = newid();

insert into message ( id, user_id, created, subject, body, type )
values ( @message_id, @sender_id, getdate(), @subject, @body, @type );

insert into message_user ( id, user_id, message_id )
values ( newid(), @recipient_id, @message_id );

# send message (user to group)

@message_id = newid();

insert into message ( id, user_id, created, subject, body, type )
values ( @message_id, @sender_id, getdate(), @subject, @body, @type );

insert into message_group ( id, group_id, message_id )
values ( newid(), @group_id, @message_id );

# send message (user to global)

@message_id = newid();

insert into message ( id, user_id, created, subject, body, type )
values ( @message_id, @sender_id, getdate(), @subject, @body, @type );

insert into message_global ( id, message_id )
values ( newid(), @message_id );

# mark message read
if exists (select * from message_status with (updlock,serializable) where message_id = @message_id and user_id = @user_id)
begin
   update message_status set read = getdate(), modified = getdate()
   where message_id = @message_id and user_id = @user_id
end
else
begin
   insert message_status (id, user_id, message_id, read, created)
   values (newid(), @user_id, @message_id, getdate(), getdate())
end;

# delete message
if exists (select * from message_status with (updlock,serializable) where message_id = @message_id and user_id = @user_id)
begin
   update message_status set deleted = getdate(), modified = getdate()
   where message_id = @message_id and user_id = @user_id
end
else
begin
   insert message_status (id, user_id, message_id, deleted, created, modified)
   values (newid(), @user_id, @message_id, getdate(), getdate(), getdate())
end;