CREATE PROCEDURE [dbo].[XLedgerImportCustomers]
    @items XLedgerCustomerImportItems readonly,
    @addresses XLedgerAddressImportItems readonly
AS
begin
    set nocount on

    declare @now datetimeoffset = sysdatetimeoffset()

    -- Step 1a: Deal with AccountingCustomerClass items.
    declare @AccountingCustomerClassIdHolder table
    (
        AccountingCustomerClassFid int not null,
        SubLedgerGroupExternalId int not null
    )

    ;with
    MergeData as
    (
        select distinct
            XLedgerCustomerClass.AccountingCustomerClassFid,
            Items.SubLedgerGroupExternalId,
            Items.SubLedgerGroupCode,
            Items.SubLedgerGroupDescription
        from @items Items
        left outer join XLedgerCustomerClass on Items.SubLedgerGroupExternalId = XLedgerCustomerClass.ExternalDbId
        where Items.SubLedgerGroupExternalId is not null
    )
    merge AccountingCustomerClass with (tablock) using MergeData on
        MergeData.AccountingCustomerClassFid = AccountingCustomerClass.AccountingCustomerClassId
    when matched then update set
        ClassCode = MergeData.SubLedgerGroupCode,
        ClassDescription = MergeData.SubLedgerGroupDescription
    when not matched then
        insert
        (
            ClassCode,
            ClassDescription
        )
        values
        (
            MergeData.SubLedgerGroupCode,
            MergeData.SubLedgerGroupDescription
        )
    output
        inserted.AccountingCustomerClassId,
        MergeData.SubLedgerGroupExternalId
    into @AccountingCustomerClassIdHolder;

    -- Step 1b: Use the 1a merge output table to update XLedgerCustomerClass table.
    ;with
    MergeData as
    (
        select distinct
            AccountingCustomerClassIdHolder.SubLedgerGroupExternalId,
            AccountingCustomerClassIdHolder.AccountingCustomerClassFid
        from @AccountingCustomerClassIdHolder as AccountingCustomerClassIdHolder
    )
    merge XLedgerCustomerClass with (tablock) using MergeData on
        MergeData.SubLedgerGroupExternalId = XLedgerCustomerClass.ExternalDbId
    when matched then update set
        LastImportedOn = @now
    when not matched then
        insert
        (
            ExternalDbId,
            AccountingCustomerClassFid,
            CreatedOn,
            LastImportedOn
        )
        values
        (
            MergeData.SubLedgerGroupExternalId,
            MergeData.AccountingCustomerClassFid,
            @now, -- CreatedOn
            @now -- LastImportedOn
        );

    -- Step 2a: Deal with AccountingCustomerPaymentTerms items.
    declare @AccountingCustomerPaymentTermsIdHolder table
    (
        AccountingCustomerPaymentTermsFid int not null,
        PaymentTermsExternalId int not null
    )

    ;with
    MergeData as
    (
        select distinct
            XLedgerCustomerPaymentTerms.AccountingCustomerPaymentTermsFid,
            Items.PaymentTermsExternalId,
            Items.PaymentTermsDescription,
            Items.PaymentTermsDays
        from @items Items
        left outer join XLedgerCustomerPaymentTerms on Items.PaymentTermsExternalId = XLedgerCustomerPaymentTerms.ExternalDbId
        where Items.PaymentTermsExternalId is not null
    )
    merge AccountingCustomerPaymentTerms with (tablock) using MergeData on
        MergeData.AccountingCustomerPaymentTermsFid = AccountingCustomerPaymentTerms.AccountingCustomerPaymentTermsId
    when matched then update set
        TermsDescription = MergeData.PaymentTermsDescription,
        DaysDue = isnull( MergeData.PaymentTermsDays, DaysDue )
    when not matched then
        insert
        (
            TermsDescription,
            DaysDue
        )
        values
        (
            MergeData.PaymentTermsDescription,
            MergeData.PaymentTermsDays
        )
    output
        inserted.AccountingCustomerPaymentTermsId,
        MergeData.PaymentTermsExternalId
    into @AccountingCustomerPaymentTermsIdHolder;

    -- Step 2b: Use the 2a merge output table to update XLedgerCustomerPaymentTerms table.
    ;with
    MergeData as
    (
        select distinct
            AccountingCustomerPaymentTermsIdHolder.PaymentTermsExternalId,
            AccountingCustomerPaymentTermsIdHolder.AccountingCustomerPaymentTermsFid
        from @AccountingCustomerPaymentTermsIdHolder as AccountingCustomerPaymentTermsIdHolder
    )
    merge XLedgerCustomerPaymentTerms with (tablock) using MergeData on
        MergeData.PaymentTermsExternalId = XLedgerCustomerPaymentTerms.ExternalDbId
    when matched then update set
        LastImportedOn = @now
    when not matched then
        insert
        (
            ExternalDbId,
            AccountingCustomerPaymentTermsFid,
            CreatedOn,
            LastImportedOn
        )
        values
        (
            MergeData.PaymentTermsExternalId,
            MergeData.AccountingCustomerPaymentTermsFid,
            @now, -- CreatedOn
            @now -- LastImportedOn
        );

    -- Step 3a: Deal with AccountingCustomer items.
    declare @AccountingCustomerIdHolder table
    (
        AccountingCustomerFid int not null,
        ExternalDbId int not null
    )

    ;with
    MergeData as
    (
        select
            XLedgerCustomer.AccountingCustomerFid,
            XLedgerCustomerClass.AccountingCustomerClassFid,
            XLedgerCustomerPaymentTerms.AccountingCustomerPaymentTermsFid,
            Items.ExternalDbId,
            Items.SubLedgerGroupExternalId,
            Items.[Name],
            Items.CustomerNumber,
            Items.Email,
            Items.OnHold,
            Items.CreditLimit,
            Items.NoCreditFlag,
            Items.Phone1,
            Items.Phone2,
            Items.Phone3,
            Items.Fax,
            Items.Contact,
            Items.TaxExempt
        from @items Items
        left outer join XLedgerCustomer on Items.ExternalDbId = XLedgerCustomer.ExternalDbId
        left outer join XLedgerCustomerClass on Items.SubLedgerGroupExternalId = XLedgerCustomerClass.ExternalDbId
        left outer join XLedgerCustomerPaymentTerms on Items.PaymentTermsExternalId = XLedgerCustomerPaymentTerms.ExternalDbId
    )
    merge AccountingCustomer with (tablock) using MergeData on
        MergeData.AccountingCustomerFid = AccountingCustomer.AccountingCustomerId
    when matched then update set
        [Name] = MergeData.[Name],
        CustomerNumber = MergeData.CustomerNumber,
        Email = MergeData.Email,
        OnHold = MergeData.OnHold,
        CreditLimit = MergeData.CreditLimit,
        NoCreditFlag = MergeData.NoCreditFlag,
        Phone1 = MergeData.Phone1,
        Phone2 = MergeData.Phone2,
        Phone3 = MergeData.Phone3,
        Fax = MergeData.Fax,
        Contact = MergeData.Contact,
        TaxExempt = MergeData.TaxExempt,
        AccountingCustomerClassFid = MergeData.AccountingCustomerClassFid,
        AccountingCustomerPaymentTermsFid = MergeData.AccountingCustomerPaymentTermsFid
    when not matched then
        insert
        (
            [Name],
            CustomerNumber,
            Email,
            OnHold,
            CreditLimit,
            NoCreditFlag,
            Phone1,
            Phone2,
            Phone3,
            Fax,
            Contact,
            TaxExempt,
            AccountingCustomerClassFid,
            AccountingCustomerPaymentTermsFid
        )
        values
        (
            MergeData.[Name],
            MergeData.CustomerNumber,
            MergeData.Email,
            MergeData.OnHold,
            MergeData.CreditLimit,
            MergeData.NoCreditFlag,
            MergeData.Phone1,
            MergeData.Phone2,
            MergeData.Phone3,
            MergeData.Fax,
            MergeData.Contact,
            MergeData.TaxExempt,
            MergeData.AccountingCustomerClassFid,
            MergeData.AccountingCustomerPaymentTermsFid
        )
    output
        inserted.AccountingCustomerId,
        MergeData.ExternalDbId
    into @AccountingCustomerIdHolder;

    -- Step 3b: Use the 3a merge output table to update XLedgerCustomer table.
    ;with
    MergeData as
    (
        select
            AccountingCustomerIdHolder.ExternalDbId,
            AccountingCustomerIdHolder.AccountingCustomerFid
        from @AccountingCustomerIdHolder as AccountingCustomerIdHolder
    )
    merge XLedgerCustomer with (tablock) using MergeData on
        MergeData.ExternalDbId = XLedgerCustomer.ExternalDbId
    when matched then update set
        LastImportedOn = @now
    when not matched then
        insert
        (
            ExternalDbId,
            AccountingCustomerFid,
            CreatedOn,
            LastImportedOn
        )
        values
        (
            MergeData.ExternalDbId,
            MergeData.AccountingCustomerFid,
            @now, -- CreatedOn
            @now -- LastImportedOn
        );

    -- Step 4: Deal with AccountingCustomerAddress (multiple addresses per customer)
    ;with MergeData as
    (
        select
            AccountingCustomer.AccountingCustomerId,
            AccountingCustomerAddressType.AccountingCustomerAddressTypeId,
            AddressDeleted = convert(bit, case when Addresses.ParentExternalDbId is null then 1 else 0 end ),
            Addresses.Address1,
            Addresses.Address2,
            Addresses.Address3,
            Addresses.City,
            Addresses.State,
            Addresses.PostalCode,
            CountryCodeStandard.CountryCodeStandardID
        from @items Items
        inner join XLedgerCustomer on Items.ExternalDbId = XLedgerCustomer.ExternalDbId
        cross join AccountingCustomerAddressType
        inner join AccountingCustomer on XLedgerCustomer.AccountingCustomerFid = AccountingCustomer.AccountingCustomerId
        left outer join @addresses Addresses on
            XLedgerCustomer.ExternalDbId = Addresses.ParentExternalDbId and
            AccountingCustomerAddressType.TypeName = Addresses.AddressType
        left outer join CountryCodeStandard on Addresses.CountryAlpha2Code = CountryCodeStandard.Alpha2Code
    ) merge AccountingCustomerAddress with (tablock) using MergeData ON
        MergeData.AccountingCustomerId = AccountingCustomerAddress.AccountingCustomerFid AND
        MergeData.AccountingCustomerAddressTypeId = AccountingCustomerAddress.AccountingCustomerAddressTypeFid
    when matched and MergeData.AddressDeleted = 0 then update SET
        Address1 = MergeData.Address1,
        Address2 = MergeData.Address2,
        Address3 = MergeData.Address3,
        City = MergeData.City,
        [State] = MergeData.State,
        [PostalCode] = MergeData.PostalCode,
        [CountryCodeStandardFID] = MergeData.CountryCodeStandardID
    --This commented-out code is a duct-tape stable fix.  Deleting causes foreign key violations.
    --We will change this strategy so that it just blanks out address fields, and may eventually change that approach
    -- to one where we introduce the concept of "hiding" addresses.
    --when matched and MergeData.AddressDeleted = 1 then delete
    when not matched and AddressDeleted = 0 then insert (
            AccountingCustomerFid,
            AccountingCustomerAddressTypeFid,
            Address1,
            Address2,
            Address3,
            City,
            State,
            PostalCode,
            CountryCodeStandardFID
    ) values (
        MergeData.AccountingCustomerId,
        MergeData.AccountingCustomerAddressTypeId,
        MergeData.Address1,
        MergeData.Address2,
        MergeData.Address3,
        MergeData.City,
        MergeData.State,
        MergeData.PostalCode,
        MergeData.CountryCodeStandardID
    );

    -- Step 5: Make sure every AccountingCustomer has an AccountingCustomerAddress of type Billing.
    -- This ensures that every customer has a default address, which is an assurance that Dynamics
    -- GP gave us.
    insert into AccountingCustomerAddress
    (
        AccountingCustomerFid,
        AccountingCustomerAddressTypeFid,
        CountryCodeStandardFID
    )
    select
        AccountingCustomerFid = XLedgerCustomer.AccountingCustomerFid,
        AccountingCustomerAddressTypeFid = AccountingCustomerAddressType.AccountingCustomerAddressTypeId,
        CountryCodeStandardFID = dbo.udfGetDefaultCountryCodeStandardID()
    from @items as Items
    inner join XLedgerCustomer on Items.ExternalDbId = XLedgerCustomer.ExternalDbId
    inner join AccountingCustomerAddressType on AccountingCustomerAddressType.TypeName = dbo.GetDefaultAccountingCustomerAddressTypeName()
    left outer join AccountingCustomerAddress on AccountingCustomerAddress.AccountingCustomerFid = XLedgerCustomer.AccountingCustomerFid and
        AccountingCustomerAddress.AccountingCustomerAddressTypeFid = AccountingCustomerAddressType.AccountingCustomerAddressTypeId
    where AccountingCustomerAddress.AccountingCustomerAddressId is null

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