CREATE procedure [dbo].[MssWebGetSakariContactByPhoneNumbers]
(
    @inSysUserId int,
    @inOrderId int,
    @inProgrammaticPhoneNumbers NVarChar32List readonly
)
as
begin
    
;with OrderSakariAccount as (
    select top 1 SakariAccountBranchMap.SakariAccountFID
    from SakariAccountBranchMap
    join Orders on Orders.BranchPriKey = SakariAccountBranchMap.BranchFID
    where Orders.PriKey = @inOrderId
    ),
    orderContacts as (
        select
        ProgrammaticPhone = SakariContact.PhoneNumber,
        ContactPriKey = SakariContact.SakariContactID,
        SakariContact.ContactName,
        UserIsSubscribedToContact = convert(bit, isnull(SakariContactSysUserMap.Subscribed,0)),
        ContactIsMappedToOrder = convert(bit,1 )
        from @inProgrammaticPhoneNumbers inPhoneNumbers
        inner join SakariContact on inPhoneNumbers.Item =  SakariContact.PhoneNumber
        inner join  SakariContactOrderMap on SakariContactOrderMap.SakariContactFID = SakariContact.SakariContactID
        left outer join SakariContactSysUserMap on SakariContact.SakariContactID = SakariContactSysuserMap.SakariContactFID and
        SakariContactSysuserMap.SysuserFID = @inSysUserId
        where SakariContactOrderMap.OrderFID =  @inOrderId
    ),
    orderSakariContacts as (
        select
        ProgrammaticPhone = SakariContact.PhoneNumber,
        ContactPriKey = SakariContact.SakariContactID,
        SakariContact.ContactName,
        UserIsSubscribedToContact = convert(bit, isnull(SakariContactSysUserMap.Subscribed,0))
        from @inProgrammaticPhoneNumbers inPhoneNumbers
        inner join SakariContact on
            inPhoneNumbers.Item = SakariContact.PhoneNumber
        inner join OrderSakariAccount on OrderSakariAccount.SakariAccountFID = SakariContact.SakariAccountFID
        left outer join SakariContactSysUserMap on
            SakariContact.SakariContactID = SakariContactSysuserMap.SakariContactFID and
            SakariContactSysuserMap.SysuserFID = @inSysUserId
    )
    select
    ProgrammaticPhone = inPhoneNumbers.Item,
    ContactPriKey = case when orderContacts.ContactPriKey is not null then orderContacts.ContactPriKey else orderSakariContacts.ContactPriKey end,
    ContactName = case when orderContacts.ContactName is not null then orderContacts.ContactName else orderSakariContacts.ContactName end,
    UserIsSubscribedToContact= case when orderContacts.UserIsSubscribedToContact is not null then orderContacts.UserIsSubscribedToContact else orderSakariContacts.UserIsSubscribedToContact end,
    ContactIsMappedToOrder = orderContacts.ContactIsMappedToOrder
    from @inProgrammaticPhoneNumbers inPhoneNumbers
    left join orderContacts on orderContacts.ProgrammaticPhone = inPhoneNumbers.Item
    left join orderSakariContacts on orderSakariContacts.ProgrammaticPhone = inPhoneNumbers.Item
    where orderContacts.ContactPriKey is not null or  orderSakariContacts.ContactPriKey is not null
end
GO
GRANT EXECUTE ON  [dbo].[MssWebGetSakariContactByPhoneNumbers] TO [MssExec]
GO