CREATE PROCEDURE [dbo].[XLedgerImportAccounts]
    @items XLedgerAccountImportItems readonly
AS
begin
    set nocount on

        declare @now datetimeoffset = sysdatetimeoffset()

        declare @AccountToXLedgerMap table
    (
        AccountFid int not null,
        ExternalDbId int not null
    )

        ;with Items as
    (
        select
            XLedgerAccount.AccountingAccountFid,
            ImportItems.CanPost,
            ImportItems.Description,
            ImportItems.Code,
            ImportItems.XLedgerDbId,
            AccountingAccountType.AccountingAccountTypeId
        from @items ImportItems
        inner join AccountingAccountType on ImportItems.AccountTypeName = AccountingAccountType.[Description]
        left outer join XLedgerAccount on ImportItems.XLedgerDbId = XLedgerAccount.ExternalDbId
    )

    merge AccountingAccount with (tablock) using Items on Items.AccountingAccountFid = AccountingAccount.AccountingAccountId
    when not matched then
        insert(
            [Code],
            [Description],
            [CanPost],
            [Hidden],
            AccountingAccountTypeFid )
    values(
        Items.Code,
        Items.Description,
        Items.CanPost,
        0,
        AccountingAccountTypeId
    )
    when matched then
        update set
            [Code] = Items.Code,
            Description = Items.Description,
            CanPost = Items.CanPost,
            [Hidden] = 0,
            AccountingAccountTypeFid = AccountingAccountTypeId
    output inserted.AccountingAccountId, Items.XLedgerDbId into @AccountToXLedgerMap
    ;


    ;with Items as
    (
        select
            ImportItems.XLedgerDbId,
            AccountToXLedgerMap.AccountFid
            from @items ImportItems
            inner join @AccountToXLedgerMap AccountToXLedgerMap on
                ImportItems.XLedgerDbId = AccountToXLedgerMap.ExternalDbId
    )
    merge XLedgerAccount with (tablock) using Items on
        Items.AccountFid = XLedgerAccount.AccountingAccountFid
    when not matched then insert(ExternalDbId,CreatedOn,LastImportedOn,AccountingAccountFid) values(Items.XLedgerDbId, @now, @now, Items.AccountFid)
    when matched then update set LastImportedOn = @now
    ;


    --mark anything as hidden that wasn't encountered via this import process (and un-hide everything else..)
    ;with HiddenStatus as
    (
        select
        XLedgerAccount.AccountingAccountFid,
        [Hidden] = convert(bit, case when AccountToXLedgerMap.ExternalDbId is null then 1 else 0 end )
        from XLedgerAccount
        left outer join @AccountToXLedgerMap AccountToXLedgerMap on XLedgerAccount.ExternalDbId = AccountToXLedgerMap.ExternalDbId
    )
    update AccountingAccount set [Hidden] = HiddenStatus.[Hidden]
    from HiddenStatus
    inner join AccountingAccount on HiddenStatus.AccountingAccountFid = AccountingAccount.AccountingAccountID


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