create procedure [dbo].[GetUnreadMessageCountForUser]
    @inSysUserId int
as
begin
    set nocount on
    declare @theUnreadIncomingCount int
    declare @theUnreadOutgoingCount int
    
    select @theUnreadIncomingCount = count(*)
    from SakariContactSysuserMap
    inner join dbo.SakariIncomingMessage on
        SakariContactSysuserMap.SakariContactFID = SakariIncomingMessage.SakariContactFID and
        SakariIncomingMessage.CreatedAt > SakariContactSysuserMap.LastReadAt
    where dbo.SakariContactSysuserMap.SysuserFID = @inSysUserId and
    SakariContactSysuserMap.Subscribed = 1

    select @theUnreadOutgoingCount = count(*)
    from dbo.SakariContactSysuserMap
    inner join SakariOutgoingMessage on
        SakariContactSysuserMap.SakariContactFID = SakariOutgoingMessage.SakariContactFID and
        SakariOutgoingMessage.UpdatedInMssAt > SakariContactSysuserMap.LastReadAt and
        SakariOutgoingMessage.SentBy <> @inSysUserId
    where dbo.SakariContactSysuserMap.SysuserFID = @inSysUserId and
    SakariContactSysuserMap.Subscribed = 1

    select UnreadMessageCount = @theUnreadIncomingCount + @theUnreadOutgoingCount
end
GO
GRANT EXECUTE ON  [dbo].[GetUnreadMessageCountForUser] TO [MssExec]
GO