CREATE procedure [dbo].[MssWebGetConversationListByUser]
    @inSysUserId int
as
begin
    set nocount on
    ;with ContactAndUpdateCount as
    (
        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

            --We don't want a user's own message to be treated as un "update" UNLESS an error was encountered.
            ( SakariOutgoingMessage.ErrorMessage is not null or SakariOutgoingMessage.SentBy <> isnull( @inSysUserID, -1 ) )

        ) 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
    )
    select
        OrderId = Orders.PriKey,
        OrderNumber = coalesce(
        Orders.OrderNo,
        Orders.ArchivedOrderNo + ' [A]' ) ,
        AccountId  = SakariAccount.AccountID,
        SakariAccountPriKey = SakariAccount.SakariAccountID,
        CustomerName = dbo.FormatFirstNameLastName(Orders.LastName, Orders.FirstName ),
        ContactName = SakariContact.ContactName,
        ContactPhone = SakariContact.PhoneNumber,
        contactPriKey = SakariContact.SakariContactID,
        UpdateCount = Data.UpdateCount ,
        ScheduledMessageCount = Data.ScheduledMessageCount,
        LastUpdatedAt = SakariContact.LastUpdatedAt
    from ContactAndUpdateCount Data
    inner join SakariContact on Data.ContactID = SakariContact.SakariContactID
    left outer join SakariContactOrderMap on Data.ContactID = SakariContactOrderMap.SakariContactFID
    inner join SakariAccount on SakariContact.SakariAccountFID = SakariAccount.SakariAccountID
    left outer join Orders on SakariContactOrderMap.OrderFID = Orders.PriKey

    order by LastUpdatedAt desc
end
GO
GRANT EXECUTE ON  [dbo].[MssWebGetConversationListByUser] TO [MssExec]
GO