CREATE function [dbo].[udfGetUnreadAndScheduledMessageCount](@inSysUserId int, @subscribedOnly bit)
returns table as return (
    select
        ContactID = dbo.SakariContactSysuserMap.SakariContactFID,
        UpdateCount = IncomingMessageUpdateTally.Count + OutgoingMessageUpdateTally.Count,
        ScheduledMessageCount = ScheduledMessageTally.Count
    from SakariContactSysuserMap
    cross apply
    (
        select count(*) Count
        from SakariIncomingMessage
        where SakariIncomingMessage.SakariContactFID = SakariContactSysuserMap.SakariContactFID and
        SakariIncomingMessage.ImportedIntoMssAt > SakariContactSysuserMap.LastReadAt
    ) as IncomingMessageUpdateTally
    cross apply
    (
        select count(*) Count
        from dbo.SakariOutgoingMessage
        where
        SakariOutgoingMessage.SakariContactFID = SakariContactSysuserMap.SakariContactFID and
        SakariOutgoingMessage.UpdatedInMssAt > SakariContactSysuserMap.LastReadAt and
        ( SakariOutgoingMessage.ErrorMessage is not null or SakariOutgoingMessage.SentBy <> @inSysUserID )

    ) OutgoingMessageUpdateTally
    cross apply
    (
        select count(*) Count
        from SakariScheduledMessage
        left outer join SakariOutgoingMessage on SakariScheduledMessage.SakariScheduledMessageID = SakariOutgoingMessage.SakariScheduledMessageFID
        where
        SakariOutgoingMessage.SakariOutgoingMessageID is null and
        SakariScheduledMessage.SakariContactFID = SakariContactSysuserMap.SakariContactFID
    ) ScheduledMessageTally
    where SakariContactSysuserMap.SysuserFID = @inSysUserId and
        SakariContactSysuserMap.Subscribed >= @subscribedOnly
)
GO
GRANT SELECT ON  [dbo].[UdfGetUnreadAndScheduledMessageCount] TO [MssExec]
GO