create procedure [dbo].[GetContactMessages]
    @inContactId int,
    @inSysUserFID int
as
begin
set nocount on
    select
        PriKey = SakariIncomingMessage.SakariIncomingMessageID,
        IsInbound = 1,
        SakariIncomingMessage.SakariContactFID,
        SakariIncomingMessage.TextMessage,
        WhenSent = SakariIncomingMessage.CreatedAt,
        null as SentBy,
        'NA' as MessageStatus,
        WasRead =
            convert(bit,
                case
                    when SakariIncomingMessage.ImportedIntoMssAt > SakariContactSysuserMap.LastReadAt then 0
                    else 1
                end ),
        null as ErrorMessage,
        null as ErrorCode
    from SakariContactSysuserMap
    inner join SakariIncomingMessage on SakariIncomingMessage.SakariContactFID = SakariContactSysuserMap.SakariContactFID
    where SakariContactSysuserMap.SakariContactFID = @inContactId and
    SakariContactSysuserMap.SysuserFID = @inSysUserFID and
    SakariContactSysuserMap.Subscribed = 1
    union all
    select
        PriKey = SakariOutgoingMessage.SakariOutgoingMessageID,
        IsInbound = 0,
        SakariOutgoingMessage.SakariContactFID,
        SakariOutgoingMessage.TextMessage,
        WhenSent = SakariOutgoingMessage.CreatedAt,
        SentBy = dbo.FormatFirstNameLastName(Sysuser.FIRSTNAME, Sysuser.LASTNAME),
        SakariMessageStatusName as MessageStatus,
        WasRead =
            convert(bit,
                case
                    when SakariOutgoingMessage.UpdatedInMssAt > SakariContactSysuserMap.LastReadAt and isnull(dbo.SakariOutgoingMessage.SentBy, 0) <> @inSysUserFID then 0
                    else 1
                end),
        SakariOutgoingMessage.ErrorMessage as ErrorMessage,
        SakariOutgoingMessage.ErrorCode
        
    from SakariContactSysuserMap
    inner join SakariOutgoingMessage on  SakariContactSysuserMap.SakariContactFID = SakariOutgoingMessage.SakariContactFID
    left outer join Sysuser on SakariOutgoingMessage.SentBy = Sysuser.SysUserID
    inner join SakariMessageStatus on SakariOutgoingMessage.SakariMessageStatusFID = SakariMessageStatus.SakariMessageStatusID
    where
    SakariContactSysuserMap.SakariContactFID = @inContactId and
    SakariContactSysuserMap.SysuserFID = @inSysUserFID and
    SakariContactSysuserMap.Subscribed = 1
    union all
    select
        Prikey = SakariScheduledMessage.SakariScheduledMessageID,
        IsInbound = 0,
        SakariScheduledMessage.SakariContactFID,
                SakariScheduledMessage.TextMessage +
            case when SakariUserSignature.Signature is null then ''
            else char(13) + char(10) + SakariUserSignature.Signature
            end as TextMessage,
        WhenSent = SakariScheduledMessage.ToBeSentAt,
        SentBy = dbo.FormatFirstNameLastName(Sysuser.FIRSTNAME, Sysuser.LASTNAME),
        MessageStatus = 'Scheduled',
        WasRead =
            convert(bit,
                case
                    when SakariScheduledMessage.CreatedAt > SakariContactSysuserMap.LastReadAt then 0
                    else 1
                end ),
        ErrorMessage = null,
        ErrorCode = null
    from SakariContactSysuserMap
    inner join SakariScheduledMessage on  SakariContactSysuserMap.SakariContactFID = SakariScheduledMessage.SakariContactFID
    left outer join Sysuser on SakariScheduledMessage.SentBy = Sysuser.SysUserID
    left outer join SakariOutgoingMessage on SakariScheduledMessage.SakariScheduledMessageID = SakariOutgoingMessage.SakariScheduledMessageFID
    left outer join SakariUserSignature on SakariScheduledMessage.SentBy = SakariUserSignature.SysuserFID
    where
        SakariContactSysuserMap.SakariContactFID = @inContactId and
        SakariContactSysuserMap.SysuserFID = @inSysUserFID and
        SakariContactSysuserMap.Subscribed = 1 and
        SakariOutgoingMessage.SakariOutgoingMessageID is null
end
GO
GRANT EXECUTE ON  [dbo].[GetContactMessages] TO [MssExec]
GO