create procedure [dbo].[SubscribeToConversation]
    @inSysUserId int,
    @inOrderId int,
    @inContactPriKey int
as
begin
    set nocount on;
    ;with insertUpdateSakariContactSysuserMap as
    (
        select
        SysuserFID = @inSysUserId,
        SakariContactFID = @inContactPriKey,
        LastReadAt = sysdatetimeoffset(),
        Subscribed = 1
    )
    merge SakariContactSysuserMap with (tablock)
    using insertUpdateSakariContactSysuserMap on
        SakariContactSysuserMap.SysUserFID = insertUpdateSakariContactSysuserMap.SysuserFID and
        SakariContactSysuserMap.SakariContactFID = insertUpdateSakariContactSysuserMap.SakariContactFID
    when matched then
        update set
        LastReadAt = insertUpdateSakariContactSysuserMap.LastReadAt,
        Subscribed = insertUpdateSakariContactSysuserMap.Subscribed
    when not matched then
        insert
        (
            SysuserFID,
            SakariContactFID,
            LastReadAt,
            Subscribed
        )
        values
        (
            insertUpdateSakariContactSysuserMap.SysuserFID,
            insertUpdateSakariContactSysuserMap.SakariContactFID,
            insertUpdateSakariContactSysuserMap.LastReadAt,
            insertUpdateSakariContactSysuserMap.Subscribed
        );

    insert into SakariContactOrderMap
    (
        OrderFID,
        SakariContactFID
    )
    select
        @inOrderId,
        @inContactPriKey
    where not exists
    (
        select top 1 1
        from SakariContactOrderMap
        where
            OrderFID = @inOrderId and
            SakariContactFID = @inContactPriKey
    )
end
GO
GRANT EXECUTE ON  [dbo].[SubscribeToConversation] TO [MssExec]
GO