/**
* This stored proceure is based upon what the GenerateAccountingBatches_XLedger does whenever
* a MoverSuite user "generates AcctTransactions" and we need to then upload them to XLedger.
*
* Please review the GenerateAccountingBatches_XLedger stored proc should you need to make
* changes here that may also need to be made there.
*/
CREATE procedure [dbo].[ReEnqueueFailedTransactions]
    @failedJournalEntryBatchItemIds IntList readonly,
    @sysUserId int
as
begin
    set nocount on

    declare @outXLedgerJournalEntryBatches table
    (
        SourceBatchId int,
        NewBatchId int
    )

    -- Insert our XLedger batch header.
    -- =================================================================================
    -- While @inSubmittedBySysUserId should always be valid, it's possible someone could
    -- modify a user between when the user logged in and when this code executes.  The
    -- SubmittedBySysUserFid column allows nulls.  We do need to make sure that we always
    -- insert a row into XLedgerJournalEntryBatch and that the SysUser is only inserted
    -- if it is valid.
    ;with SubmittedInfo as
    (
        select
            SysUserId = @sysUserId,
            CreatedOn = sysdatetimeoffset()
    ),
    SubmittedBatches as
    (
        select distinct
            BatchId = XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchFid
        from @failedJournalEntryBatchItemIds as failedBatchItemIds
        inner join XLedgerJournalEntryBatchItem on XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchItemId = failedBatchItemIds.Item
    )
    insert into XLedgerJournalEntryBatch
    (
        [Description],
        SubmittedBySysUserFid,
        CreatedOn,
        RetryOfXLedgerJournalEntryBatchFid
    )
    output INSERTED.RetryOfXLedgerJournalEntryBatchFid, INSERTED.XLedgerJournalEntryBatchId
    into @outXLedgerJournalEntryBatches( SourceBatchId, NewBatchId )
    select
        [Description] = XLedgerJournalEntryBatch.[Description],
        -- It's possible that the submitting SysUserId is invalid since it is looked up when the user logs
        -- into MoversSuite and an admin could merge it to some other Id or just delete it (we think this
        -- would be very rare).  If it does happen, this insert will still work but we wouldn't know who
        -- generated this and we wouldn't know to whom we should report any errors to (if that occurs).
        SubmittedBySysUserFid = SysUser.SysUserId,
        CreatedOn = SubmittedInfo.CreatedOn,
        RetryOfXLedgerJournalEntryBatchFid = SubmittedBatches.BatchId
    from SubmittedBatches
    inner join XLedgerJournalEntryBatch on XLedgerJournalEntryBatch.XLedgerJournalEntryBatchId = SubmittedBatches.BatchId
    cross join SubmittedInfo
    left outer join SysUser on SysUser.SysUserId = SubmittedInfo.SysUserId

    -- Insert our XLedger batch details.
    -- One detail per @failedJournalEntryBatchItemIds provided that it hasn't been requeued previously.
    declare @newBatchItemIds table
    (
        SourceItemId int,
        NewItemId int
    )
    declare @newBatchItemIdsRowCount int

    ;with Items as
    (
        select
            SourceItemId = SourceXLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchItemId,
            XLedgerJournalEntryBatchFid = outXLedgerJournalEntryBatches.NewBatchId,
            AcctTransactionFid = SourceXLedgerJournalEntryBatchItem.AcctTransactionFid,
            XglFid = SourceXLedgerJournalEntryBatchItem.XglFid,
            NominalXLedgerCompanyXLedgerDbId = SourceXLedgerJournalEntryBatchItem.NominalXLedgerCompanyXLedgerDbId,
            OpposingXLedgerCompanyXLedgerDbId = SourceXLedgerJournalEntryBatchItem.OpposingXLedgerCompanyXLedgerDbId,
            NominalBranchObjectValueXLedgerDbId = SourceXLedgerJournalEntryBatchItem.NominalBranchObjectValueXLedgerDbId,
            OpposingBranchObjectValueXLedgerDbId = SourceXLedgerJournalEntryBatchItem.OpposingBranchObjectValueXLedgerDbId,
            --NominalDivisionFid = SourceXLedgerJournalEntryBatchItem.NominalDivisionFid,
            --OpposingDivisionFid = SourceXLedgerJournalEntryBatchItem.OpposingDivisionFid,
            NominalAccountingAccountFid = SourceXLedgerJournalEntryBatchItem.NominalAccountingAccountFid,
            OpposingAccountingAccountFid = SourceXLedgerJournalEntryBatchItem.OpposingAccountingAccountFid,
            XLedgerCustomerXLedgerDbId = SourceXLedgerJournalEntryBatchItem.XLedgerCustomerXLedgerDbId,
            XLedgerSupplierXLedgerDbId = SourceXLedgerJournalEntryBatchItem.XLedgerSupplierXLedgerDbId,
            RequestGroupIdentifier = SourceXLedgerJournalEntryBatchItem.RequestGroupIdentifier,
            XLedgerBankAccountFid = SourceXLedgerJournalEntryBatchItem.XLedgerBankAccountFid
        from @failedJournalEntryBatchItemIds as failedJournalEntryBatchItemIds
        inner join XLedgerJournalEntryBatchItem as SourceXLedgerJournalEntryBatchItem on
            SourceXLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchItemId = failedJournalEntryBatchItemIds.Item
        inner join @outXLedgerJournalEntryBatches as outXLedgerJournalEntryBatches on
            outXLedgerJournalEntryBatches.SourceBatchId = SourceXLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchFid
        where SourceXLedgerJournalEntryBatchItem.Resolved = 0
    )
    merge XLedgerJournalEntryBatchItem with (tablock) using Items on 1 = 2
    when not matched then
        insert
        (
            XLedgerJournalEntryBatchFid,
            AcctTransactionFid,
            XglFid,
            NominalXLedgerCompanyXLedgerDbId,
            OpposingXLedgerCompanyXLedgerDbId,
            NominalBranchObjectValueXLedgerDbId,
            OpposingBranchObjectValueXLedgerDbId,
            -- XLTD: These will likely be the appropriate XLedgerDivisionObjectValue.XLedgerDbId
            --NominalDivisionFid,
            --OpposingDivisionFid,
            NominalAccountingAccountFid,
            OpposingAccountingAccountFid,
            XLedgerCustomerXLedgerDbId,
            XLedgerSupplierXLedgerDbId,
            RequestGroupIdentifier,
            XLedgerBankAccountFid,
            Resolved
        )
        values
        (
            Items.XLedgerJournalEntryBatchFid,
            Items.AcctTransactionFid,
            Items.XglFid,
            Items.NominalXLedgerCompanyXLedgerDbId,
            Items.OpposingXLedgerCompanyXLedgerDbId,
            Items.NominalBranchObjectValueXLedgerDbId,
            Items.OpposingBranchObjectValueXLedgerDbId,
            --Items.NominalDivisionFid,
            --Items.OpposingDivisionFid,
            Items.NominalAccountingAccountFid,
            Items.OpposingAccountingAccountFid,
            Items.XLedgerCustomerXLedgerDbId,
            Items.XLedgerSupplierXLedgerDbId,
            Items.RequestGroupIdentifier,
            Items.XLedgerBankAccountFid,
            0
        )
    output Items.SourceItemId, inserted.XLedgerJournalEntryBatchItemId
    into @newBatchItemIds( SourceItemId, NewItemId );

    set @newBatchItemIdsRowCount = @@ROWCOUNT

    -- Mark all of the XLedgerJournalEntryBatchItem rows that we are re-enqueuing as being resolved
    -- to make finding the unresolved XLedgerJournalEntryBatchItem rows easier to locate.  Only mark
    -- the ones as resolved that were previously not marked as resolved.
    declare @newResolvedBatchItemIds IntList
    declare @newResolvedBatchItemIdsRowCount int
    update XLedgerJournalEntryBatchItem set
        Resolved = 1
    output INSERTED.XLedgerJournalEntryBatchItemId
    into @newResolvedBatchItemIds( Item )
    from @newBatchItemIds as newBatchItemIds
    inner join XLedgerJournalEntryBatchItem on
        XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchItemId = newBatchItemIds.SourceItemId
    where XLedgerJournalEntryBatchItem.Resolved = 0

    set @newResolvedBatchItemIdsRowCount = @@ROWCOUNT

    if( @newBatchItemIdsRowCount != @newResolvedBatchItemIdsRowCount )
    begin
        -- OK, someone else queued up some or all of the same BatchItemIds before we did.
        -- Remove the ones we didn't mark as Resolved via the prior update statement.
        delete from XLedgerJournalEntryBatchItem
        from @newBatchItemIds as newBatchItemIds
        inner join XLedgerJournalEntryBatchItem on
            XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchItemId = newBatchItemIds.SourceItemId
        left outer join @newResolvedBatchItemIds as newResolvedBatchItemIds on
            newResolvedBatchItemIds.Item = newBatchItemIds.SourceItemId
        where newResolvedBatchItemIds.Item is null
    end

    -- At this point we have either:
    -- 1) (Ideally) copied all of the @failedJournalEntryBatchItemIds into new JournalEntryBatchItemIds and
    --    we marked all of the @failedJournalEntryBatchItemIds as being Resolved = 1.
    -- 2) We didn't copy any failedJournalEntryBatchItemIds into new JournalEntryBatchItemIds because someone
    --    else had already queued all of them up.  This is @newBatchItemIdsRowCount = 0.
    -- 3) We only copied some of the failedJournalEntryBatchItemIds into new JournalEntryBatchItemIds because
    --    someone else had already queued some, but not all, of them.  This is
    --    @newBatchItemIdsRowCount != @newResolvedBatchItemIdsRowCount.
    --
    -- So, if 2 or 3 happened, it is now possible that some or all of the new XLedgerJournalEntryBatches that
    -- we created now have no XLedgerJournalEntryBatchItems under them.  So, delete those XLedgerJournalEntryBatches
    -- that are now empty, if any:
    if( @newBatchItemIdsRowCount = 0 or @newBatchItemIdsRowCount != @newResolvedBatchItemIdsRowCount )
    begin
        ;with EmptyBatches as (
            select
                Id = XLedgerJournalEntryBatch.XLedgerJournalEntryBatchId
            from @outXLedgerJournalEntryBatches as outXLedgerJournalEntryBatches
            inner join XLedgerJournalEntryBatch on
                XLedgerJournalEntryBatch.XLedgerJournalEntryBatchId = outXLedgerJournalEntryBatches.NewBatchId
            left outer join XLedgerJournalEntryBatchItem on
                XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchFid = XLedgerJournalEntryBatch.XLedgerJournalEntryBatchId
            group by XLedgerJournalEntryBatch.XLedgerJournalEntryBatchId
            having count( XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchItemId ) = 0
        )
        delete from XLedgerJournalEntryBatch
        from XLedgerJournalEntryBatch
        inner join EmptyBatches on
                EmptyBatches.Id = XLedgerJournalEntryBatch.XLedgerJournalEntryBatchId
    end

    -- All done!  Queue these batches up for processing to XLedger.
    -- One queue per XLedgerJournalEntryBatch, if any.
    declare @finalBatchCount int
    insert into XLedgerJournalEntryBatchQueue
    (
        XLedgerJournalEntryBatchFid
    )
    select
        XLedgerJournalEntryBatchFid = outXLedgerJournalEntryBatches.NewBatchId
    from @outXLedgerJournalEntryBatches as outXLedgerJournalEntryBatches
    inner join XLedgerJournalEntryBatch on
        XLedgerJournalEntryBatch.XLedgerJournalEntryBatchId = outXLedgerJournalEntryBatches.NewBatchId

    set @finalBatchCount = @@ROWCOUNT

    -- It's entirely possible that there is actually nothing to queue up if someone else
    -- queued up everything we tried to queue up.
    if( @finalBatchCount > 0 )
    begin
        -- And lastly tell MssWeb that there is work to be done
        insert into MssWebEvent( MssWebEventTypeFid )
        select MssWebEventType.MssWebEventTypeId
        from MssWebEventType
        where MssWebEventType.EventTypeName = 'XLedgerTransactionUploadRequested'
    end

    -- Return a list of all the XLedgerJournalEntryBatch batches we re-enqueued.
    -- It is totally possible that this is an empty list if someone else re-enqueued
    -- everything before we did.
    select
        XLedgerJournalEntryBatchId = outXLedgerJournalEntryBatches.NewBatchId
    from @outXLedgerJournalEntryBatches as outXLedgerJournalEntryBatches
    inner join XLedgerJournalEntryBatch on
        XLedgerJournalEntryBatch.XLedgerJournalEntryBatchId = outXLedgerJournalEntryBatches.NewBatchId

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