IF OBJECT_ID('auditlog.DialogPool') IS NOT NULL
    DROP TABLE [auditlog].[DialogPool];
GO

CREATE TABLE [auditlog].[DialogPool] (
  [FromService] SYSNAME NOT NULL,
  [ToService] SYSNAME NOT NULL,
  [OnContract] SYSNAME NOT NULL,
  [Handle] UNIQUEIDENTIFIER NOT NULL,
  [OwnerSPID] INT NOT NULL,
  [CreationTime] DATETIME NOT NULL,
  [SendCount] BIGINT NOT NULL,
  CONSTRAINT [UQ_DialogPool_Handle] UNIQUE (Handle)
);
GO


IF OBJECT_ID('auditlog.usp_get_dialog') IS NOT NULL
  DROP PROCEDURE [auditlog].[usp_get_dialog];
GO

CREATE PROCEDURE [auditlog].[usp_get_dialog]
  @fromService SYSNAME,
  @toService SYSNAME,
  @onContract SYSNAME,
  @dialogHandle UNIQUEIDENTIFIER OUTPUT,
  @sendCount BIGINT OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @dialog TABLE (
    [FromService] SYSNAME NOT NULL,
    [ToService] SYSNAME NOT NULL,
    [OnContract] SYSNAME NOT NULL,
    [Handle] UNIQUEIDENTIFIER NOT NULL,
    [OwnerSPID] INT NOT NULL,
    [CreationTime] DATETIME NOT NULL,
    [SendCount] BIGINT NOT NULL
  );

-- Try to claim an unused dialog in DialogPool
-- READPAST option avoids blocking on locked dialogs.
  BEGIN TRANSACTION;
    DELETE FROM @dialog;
    UPDATE TOP(1) [auditlog].[DialogPool] WITH(READPAST)
    SET [OwnerSPID] = @@SPID
    OUTPUT INSERTED.* INTO @dialog
    WHERE [FromService] = @fromService
      AND [ToService] = @toService
      AND [OnContract] = @OnContract
      AND [OwnerSPID] = -1;

  IF @@ROWCOUNT > 0 BEGIN
	   SET @dialogHandle = (SELECT [Handle] FROM @dialog);
	   SET @sendCount = (SELECT [SendCount] FROM @dialog);
  END
  ELSE BEGIN
    -- No free dialogs: need to create a new one
    BEGIN DIALOG CONVERSATION @dialogHandle
    FROM SERVICE @fromService
    TO SERVICE @toService
    ON CONTRACT @onContract
    WITH ENCRYPTION = OFF;

    INSERT INTO [auditlog].[DialogPool] 
    ([FromService], [ToService], [OnContract], [Handle], [OwnerSPID], [CreationTime], [SendCount])
    VALUES
    (@fromService, @toService, @onContract, @dialogHandle, @@SPID, GETDATE(), 0);
    
    SET @sendCount = 0;
    END
  
  COMMIT TRANSACTION;
END
GO

--------------------------------------------------------------------------
-- Free dialog procedure.
-- Return the dialog to the pool.
-- Inputs are dialog handle and updated send count.
--------------------------------------------------------------------------

IF OBJECT_ID('auditlog.usp_free_dialog') IS NOT NULL
  DROP PROCEDURE [auditlog].[usp_free_dialog];
GO

CREATE PROCEDURE [auditlog].[usp_free_dialog]
  @dialogHandle UNIQUEIDENTIFIER,
  @sendCount BIGINT
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @rowcount INT;
  DECLARE @string VARCHAR(50);

  BEGIN TRANSACTION;

  -- Release dialog by setting OwnerSPID to -1.
  UPDATE [auditlog].[DialogPool] SET [OwnerSPID] = -1, [SendCount] = @sendCount 
  WHERE [Handle] = @dialogHandle;

  SET @rowcount = @@ROWCOUNT;

  IF @rowcount = 0 BEGIN
    SET @string = (SELECT CAST( @dialogHandle AS VARCHAR(50)));
    RAISERROR('usp_free_dialog: dialog %s not found in dialog pool', 16, 1, @string) WITH LOG;
  END
  ELSE IF @rowcount > 1 BEGIN
    SET @string = (SELECT CAST( @dialogHandle AS VARCHAR(50)));
    RAISERROR('usp_free_dialog: duplicate dialog %s found in dialog pool', 16, 1, @string) WITH LOG;
  END
  COMMIT TRANSACTION;
END
GO

IF OBJECT_ID('auditlog.usp_delete_dialog') IS NOT NULL
  DROP PROCEDURE [auditlog].[usp_delete_dialog];
GO

CREATE PROCEDURE [auditlog].[usp_delete_dialog]
  @dialogHandle UNIQUEIDENTIFIER
AS
BEGIN
  SET NOCOUNT ON;

  BEGIN TRY
    BEGIN TRANSACTION;
    DELETE [DialogPool] WHERE Handle = @dialogHandle;
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    -- Rollback any active or uncommittable transactions before
    -- inserting information in the ErrorLog
    IF @@TRANCOUNT > 0 BEGIN
	    ROLLBACK TRANSACTION;
    END

    EXECUTE [auditlog].[uspLogError];
  END CATCH;
END
GO

IF OBJECT_ID('auditlog.usp_send') IS NOT NULL
  DROP PROCEDURE [auditlog].[usp_send];
GO

CREATE PROCEDURE [auditlog].[usp_send] 
  @fromService SYSNAME,
  @toService SYSNAME,
  @onContract SYSNAME,
  @messageType SYSNAME,
  @messageBody NVARCHAR(MAX)
WITH EXECUTE AS 'Kreta_tech_user'  
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @dialogHandle UNIQUEIDENTIFIER;
  DECLARE @sendCount BIGINT;
  DECLARE @counter INT;
  DECLARE @error INT;


  SET @counter = 1;
  BEGIN TRANSACTION;

    -- message validation check, preventing poison message at the target service validation
    -- target service validates WELL_FORMED_XML types, so it is best to prevent it at here
    
    IF EXISTS (SELECT 1 FROM sys.[service_message_types] WHERE [name] = @messageType AND [validation] = 'X')
		  AND (TRY_CONVERT(xml, @messageBody) IS NOT NULL)
    BEGIN
	    SET @messageBody = CAST(TRY_CONVERT(xml, @messageBody) AS nvarchar(max))
    END
    ELSE BEGIN
	    INSERT INTO [auditlog].[AuditServiceFailedItems] ([conversation_handle], [message_body])
	    VALUES ('00000000-0000-0000-0000-000000000000', @messageBody)
	    COMMIT TRANSACTION;
	    RAISERROR('XML validation failure before sending. Check [auditlog].[AuditServiceFailedItems] for failed message.', 16,1) WITH LOG;
    END

  -- Will need a loop to retry in case the dialog is
  -- in a state that does not allow transmission
    WHILE (1=1) BEGIN
	   -- Claim a dialog from the dialog pool.
	   -- A new one will be created if none are available.
	    EXEC [auditlog].[usp_get_dialog] @fromService, @toService, @onContract, @dialogHandle OUTPUT, @sendCount OUTPUT;

	   -- Attempt to SEND on the dialog
	    IF (@messageBody IS NOT NULL) BEGIN
			 -- If the @messageBody is not null it must be sent explicitly
        
			  SEND ON CONVERSATION @dialogHandle MESSAGE TYPE @messageType (@messageBody);
		  END
	    ELSE BEGIN
			 -- Messages with no body must *not* specify the body,
			 -- cannot send a NULL value argument
			  SEND ON CONVERSATION @dialogHandle MESSAGE TYPE @messageType;
		  END

      SET @error = @@ERROR;
      IF @error = 0 BEGIN
	   -- Successful send, increment count and exit the loop
	      SET @sendCount = @sendCount + 1;
	      BREAK;
      END

      SET @counter = @counter + 1;
      IF @counter > 10 BEGIN
	      -- We failed 10 times in a  row, something must be broken
	      RAISERROR('Failed to SEND on a conversation for more than 10 times. Error %i.', 16, 1, @error) WITH LOG;
	      BREAK;
      END

      -- Delete the associated dialog from the table and try again
      EXEC [auditlog].[usp_delete_dialog] @dialogHandle;
      SELECT @dialogHandle = NULL;
    END

  -- "Criterion" for dialog pool removal is send count > 1000.
  -- Modify to suit application.
  -- When deleting also inform the target to end the dialog.
    IF @sendCount > 1000 BEGIN
      EXEC [auditlog].[usp_delete_dialog] @dialogHandle ;
      SEND ON CONVERSATION @dialogHandle MESSAGE TYPE [EndOfStream];
    END 
    ELSE BEGIN
    -- Free the dialog.
      EXEC [auditlog].[usp_free_dialog] @dialogHandle, @sendCount;
    END

  COMMIT TRANSACTION;
END
GO