﻿{{! Copyright (c) Avanade. Licensed under the MIT License. See https://github.com/Avanade/Beef }}
CREATE PROCEDURE [{{Schema}}].[sp{{EventOutboxTableName}}Enqueue]
  @EventList AS [{{Schema}}].[udt{{EventOutboxTableName}}List] READONLY
AS
BEGIN
  /*
   * This is automatically generated; any changes will be lost.
   */
 
  SET NOCOUNT ON;

  BEGIN TRY
    -- Wrap in a transaction.
    BEGIN TRANSACTION

    -- Working variables.
    DECLARE @eventOutboxId BIGINT,
            @enqueuedDate DATETIME

    SET @enqueuedDate = SYSUTCDATETIME()

    -- Enqueued outbox resultant identifier.
    DECLARE @enqueuedIdentity TABLE([EventOutboxId] BIGINT)

    -- Cursor output variables.
    DECLARE @eventId UNIQUEIDENTIFIER,
            @subject NVARCHAR(1024),
            @action NVARCHAR(128),
            @correlationId NVARCHAR(64),
            @TenantId UNIQUEIDENTIFIER,
            @ValueType NVARCHAR(1024),
            @EventData VARBINARY(MAX)

    -- Declare, open, and fetch first event from cursor.
    DECLARE c CURSOR FORWARD_ONLY 
      FOR SELECT [EventId], [Subject], [Action], [CorrelationId], [TenantId], [ValueType], [EventData] FROM @EventList

    OPEN c
    FETCH NEXT FROM c INTO @eventId, @subject, @action, @correlationId, @tenantId, @valueType, @eventdata

    -- Iterate the event(s).
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Enqueue event into outbox 
        INSERT INTO [{{Schema}}].[{{EventOutboxTableName}}] ([EnqueuedDate])
          OUTPUT inserted.EventOutboxId INTO @enqueuedIdentity 
          VALUES (@enqueuedDate)

        SELECT @eventOutboxId = [EventOutboxId] FROM @enqueuedIdentity

        -- Insert corresponding event data.
        INSERT INTO [{{Schema}}].[{{EventOutboxTableName}}Data] (
          [EventOutboxId],
          [EventId],
          [Subject], 
          [Action], 
          [CorrelationId], 
          [TenantId], 
          [ValueType], 
          [EventData]
        ) 
        VALUES (
          @eventOutboxId,
          @eventId,
          @subject, 
          @action,
          @correlationId, 
          @tenantId, 
          @valueType, 
          @eventdata
        )

        -- Fetch the next event from the cursor.
        FETCH NEXT FROM c INTO @eventId, @subject, @action, @correlationId, @tenantId, @valueType, @eventdata
    END

    -- Close the cursor.
    CLOSE c
    DEALLOCATE c

    -- Commit the transaction.
    COMMIT TRANSACTION
    RETURN 0
  END TRY
  BEGIN CATCH
    -- Rollback transaction and rethrow error.
    IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION;

    THROW;
  END CATCH
END