create procedure [dbo].[MssWebGetOutgoingMessages]
@inContactId int,
@inSysUserId int,
@inBefore DateTimeOffSet
As
begin

    select
        PriKey = SakariOutgoingMessage.SakariOutgoingMessageID,
        TextMessage = SakariOutgoingMessage.TextMessage,
        SentAt = SakariOutgoingMessage.CreatedAt,
        SentBy = dbo.FormatFirstNameLastName(Sysuser.FIRSTNAME, Sysuser.LASTNAME),
        MessageStatus = SakariMessageStatus.SakariMessageStatusName ,
        WasRead =
            convert(bit,
                case
                    when SakariOutgoingMessage.UpdatedInMssAt > SakariContactSysuserMap.LastReadAt and isnull(dbo.SakariOutgoingMessage.SentBy, 0) <> @inSysUserId then 0
                    else 1
                end),
        ErrorMessage = SakariOutgoingMessage.ErrorMessage,
        ErrorCode = SakariOutgoingMessage.ErrorCode
    from SakariContactSysuserMap
    inner join SakariOutgoingMessage on  SakariContactSysuserMap.SakariContactFID = SakariOutgoingMessage.SakariContactFID
    left outer join Sysuser on SakariOutgoingMessage.SentBy = Sysuser.SysUserID
    left outer join SakariContact on SakariContact.SakariContactID = SakariOutgoingMessage.SakariContactFID
    inner join SakariMessageStatus on SakariOutgoingMessage.SakariMessageStatusFID = SakariMessageStatus.SakariMessageStatusID
    where
        SakariContactSysuserMap.SakariContactFID = @inContactId and
        SakariContactSysuserMap.SysuserFID = @inSysUserId and
        SakariOutgoingMessage.CreatedAt < @inBefore
    order by SakariOutgoingMessage.CreatedAt desc
end
GO
GRANT EXECUTE ON  [dbo].[MssWebGetOutgoingMessages] TO [MssExec]
GO