create procedure [dbo].[BcImportBankAccounts]
    @items BcBankAccountImportItems readonly
as
begin
    set nocount on

        declare @now datetimeoffset = sysdatetimeoffset()

        declare @BankAccountToBcMap table
        (
            BankAccountFid int not null,
            BcId uniqueidentifier not null,
            LastUpdatedInBcOn datetimeoffset
        )

        ;with Items as
    (
        select
            BcBankAccount.AccountingBankAccountFid,
            ImportItems.[Description],
            ImportItems.GlAccountNumber,
            ImportItems.[Hidden],
            ImportItems.BcId,
            ImportItems.LastModified,
            ImportItems.BankAccountNumber,
            AccountingAccount.AccountingAccountId
        from @items ImportItems
        left outer join BcBankAccount on ImportItems.BcId = BcBankAccount.BcId

        --when originally implemented, this holds true.  We may need to ensure that BC "Numbers" on their Bank accounts always correspond with GL account codes in the future.
        inner join AccountingAccount on ImportItems.GlAccountNumber = AccountingAccount.Code
    )

    merge AccountingBankAccount with ( tablock ) using Items on Items.AccountingBankAccountFid = AccountingBankAccount.AccountingBankAccountId
    when not matched then
        insert(
            AccountingAccountFid,
            [Description],
            [Code],
            AccountNumber,
            [Hidden]
            
            )
    values(
        Items.AccountingAccountId,
        Items.[Description],
        Items.GlAccountNumber,
        Items.BankAccountNumber,
        [Hidden]
    )
    when matched then
        update set
            AccountingAccountFid = Items.AccountingAccountId,
            [Description] = Items.[Description],
            [Code] = Items.GlAccountNumber,
            AccountNumber = Items.BankAccountNumber,
            [Hidden] = Items.[Hidden]
    output inserted.AccountingBankAccountId, Items.BcId, Items.LastModified into @BankAccountToBcMap
    ;


    ;with Items as
    (
        select
            ImportItems.BcId,
            BankAccountToBcMap.BankAccountFid,
            BankAccountToBcMap.LastUpdatedInBcOn
            from @items ImportItems
            inner join @BankAccountToBcMap BankAccountToBcMap on
                ImportItems.BcId = BankAccountToBcMap.BcId
    )
    merge BcBankAccount with ( tablock ) using Items on
        Items.BankAccountFid = BcBankAccount.AccountingBankAccountFid
    when not matched
        then insert(
            BcId,
            CreatedOn,
            LastImportedOn,
            AccountingBankAccountFid,
            LastUpdatedInBcOn
        )
        values(
            Items.BcId,
            @now,
            @now,
            Items.BankAccountFid,
            Items.LastUpdatedInBcOn
        )
    when matched then update set
        LastImportedOn = @now,
        LastUpdatedInBcOn = Items.LastUpdatedInBcOn
    ;

end
GO
GRANT EXECUTE ON  [dbo].[BcImportBankAccounts] TO [MssExec]
GO