USE gaia
GO
DECLARE
    @galenicaQueueID UNIQUEIDENTIFIER,
    @storageAccountName VARCHAR(MAX) = 'sttpsuntestphaqueues',
    @queue VARCHAR(63) = 'sun004-triafin',
    @payload VARCHAR(MAX)= '<Request RequestType="EntryCreate"><Data><EntryCreate><ReferencePharmacyId>706</ReferencePharmacyId><ReferenceOperationId>1215</ReferenceOperationId><OperationId>1215</OperationId><DebtorAccountId><PharmacyCode>GSU004</PharmacyCode><Id>136552</Id></DebtorAccountId><CreditorAccountId><PharmacyCode>GSU004</PharmacyCode><Id>1500090004</Id></CreditorAccountId><ReconciliationId><PharmacyCode>GSU004</PharmacyCode><Id>1501704897</Id></ReconciliationId><UserId>8231</UserId><ValueDate>2025-12-03</ValueDate><DocumentDate>2022-04-02</DocumentDate><Amount>48.4</Amount><Subject>BASSET JOSE - YUGRRP Hsnü - 1789 LUGNORRE</Subject><Remark>Zahlung ohne QRR</Remark><EntryInputMode>Excel</EntryInputMode><ConversationId>25306</ConversationId><ConversationCreationDate>2025-12-05T10:34:24</ConversationCreationDate></EntryCreate></Data></Request>',
    @headers VARCHAR(MAX),
    @headersBlob VARCHAR(MAX),
    @oldConversationID UNIQUEIDENTIFIER,
    @blobID UNIQUEIDENTIFIER,
    @messageTypeName VARCHAR(255) = 'CeresSenderMessageType',
    @statusCode INT,
    @statusText NVARCHAR(4000),
    @conversationID UNIQUEIDENTIFIER,
    @messageID UNIQUEIDENTIFIER,
    @insertionTime DATETIME
BEGIN
    SET NOCOUNT ON;

    /* -------------------- VALIDATION -------------------- */
    

    /* -------------------- VARIABLES -------------------- */
    DECLARE @token VARCHAR(MAX),
            @credentialName VARCHAR(256) = NULL,
            @timeoutSeconds INT = 30,
            @response VARCHAR(MAX),
            @rawResponse XML,
            @finalURL VARCHAR(MAX),
            @ttl CHAR(13) = 'messagettl=-1',
            @source VARBINARY(MAX),
            @originalPayload VARCHAR(MAX) = @payload,
            @url VARCHAR(MAX),
            @urlblob VARCHAR(MAX),
            @returnValue INT = 0,
            @payloadBytes INT,
            @createURL VARCHAR(MAX),
            @retryCount INT,
            @maxRetries INT,
            @retryDelaySeconds INT,
            @waitTime CHAR(8),
            @postURL VARCHAR(MAX);


    /* -------------------- GET TOKEN -------------------- */
    IF @credentialName IS NULL
    BEGIN
        EXEC @returnValue = sp_get_Bearer_token
            @Token = @token OUTPUT,
            @statusCode = @statusCode OUTPUT,
            @statusText = @statusText OUTPUT;

        IF @returnValue <> 0 OR @statusCode <> 200
            RAISERROR('Error: Get token failed! statusCode: %d statusText: %s',16,1,@statusCode,@statusText);
    END


    /* -------------------- HEADERS -------------------- */
    IF @headers IS NULL
        SET @headers = CONCAT(
            'Content-Type: application/xml',CHAR(10),
            'x-ms-version: 2025-07-05',CHAR(10),
            'Authorization: Bearer ',@token,CHAR(10)
        );

    IF @headersBlob IS NULL
        SET @headersBlob = CONCAT(
            'x-ms-blob-type: BlockBlob',CHAR(10),
            'x-ms-version: 2025-07-05',CHAR(10),
            'Authorization: Bearer ',@token,CHAR(10)
        );


    /* -------------------- PREPARE -------------------- */
    EXEC sp_manage_GalenicaQueue;

    SELECT @conversationID = ISNULL(@oldConversationID,NEWID());

    SET @url = CONCAT('https://',@storageAccountName,'.queue.core.windows.net/');
    SET @urlblob = CONCAT('https://',@storageAccountName,'.blob.core.windows.net/');

    IF RIGHT(@queue,1) <> '/' SET @queue = CONCAT(@queue,'/');
    SET @queue = LOWER(@queue);


    /* -------------------- INSERT MONITORING -------------------- */
    IF @galenicaQueueID IS NULL
    BEGIN
        SET @galenicaQueueID = NEWID();

        INSERT INTO GalenicaQueue(
            galenicaQueueID, conversationID, messageID,
            storageAccountName, queueName, headers,
            headersBlob, blobID, messageTypeName, payload
        ) VALUES (
            @galenicaQueueID, @conversationID, @messageID,
            @storageAccountName, REPLACE(@queue,'/',''), @headers,
            @headersBlob, @blobID, @messageTypeName, @originalPayload
        );
    END


    /* -------------------- BLOB UPLOAD IF NEEDED -------------------- */
    SET @payloadBytes = DATALENGTH(@originalPayload);

    IF @payloadBytes > 49152
    BEGIN
        IF @blobID IS NULL
        BEGIN
            EXEC @returnValue = sp_put_message_into_blob
                @url = @urlblob,
                @payload = @originalPayload,
                @headers = @headersBlob,
                @response = @response OUTPUT,
                @statusCode = @statusCode OUTPUT,
                @statusText = @statusText OUTPUT,
                @blobID = @blobID OUTPUT;

            IF @returnValue <> 0 RETURN;
        END
    END
    ELSE
        SET @blobID = NULL;


    /* -------------------- BUILD MESSAGE -------------------- */
    SET @postURL = CONCAT(@url,@queue,'messages?',CONCAT_WS('&',@ttl,NULLIF(@token,'')));

    IF @blobID IS NULL
        SET @payload = CONCAT(
            '<GalenicaQueue><conversationID>',CONVERT(VARCHAR(255),@conversationID),
            '</conversationID><inlinePayload><![CDATA[',@originalPayload,
            ']]></inlinePayload><messageTypeName>',@messageTypeName,
            '</messageTypeName></GalenicaQueue>'
        );
    ELSE
        SET @payload = CONCAT(
            '<GalenicaQueue><conversationID>',CONVERT(VARCHAR(255),@conversationID),
            '</conversationID><blobID>',CONVERT(VARCHAR(255),@blobID),
            '</blobID><messageTypeName>',@messageTypeName,
            '</messageTypeName></GalenicaQueue>'
        );

    --cast the payload to xml first to enforce a utf-16le bytes encoding
    SET @source = CONVERT(VARBINARY(MAX), CONVERT(XML, @payload));
    SET @payload =
        CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))','varchar(max)');

    SET @payload = CONCAT('<QueueMessage><MessageText>', @payload, '</MessageText></QueueMessage>');
	print @payload

    /* -------------------- SEND MESSAGE FIRST TRY -------------------- */
    BEGIN TRY
      EXEC @returnValue = sp_Make_Restful_Call
          @url = @postURL,
          @method = 'POST',
          @payload = @payload,
          @headers = @headers,
          @credentialName = @credentialName,
          @timeoutSeconds = @timeoutSeconds,
          @response = @response OUTPUT,
          @statusText = @statusText OUTPUT,
          @statusCode = @statusCode OUTPUT,
          @raiseError = 0 ;

      IF @statusCode BETWEEN 400 AND 499
        RAISERROR('Error in make restfull call',16,10);
	END TRY
	BEGIN CATCH


	  /* -------------------- QUEUE MISSING → CREATE IT -------------------- */
      IF @statusCode = 404
      BEGIN
          SET @createURL = CONCAT(@url,@queue,'?',@token);

          EXEC @returnValue = sp_Make_Restful_Call
              @url = @createURL,
              @method = 'PUT',
              @payload = NULL,
              @headers = @headers,
              @credentialName = @credentialName,
              @timeoutSeconds = @timeoutSeconds,
              @response = @response OUTPUT,
              @statusText = @statusText OUTPUT,
              @statusCode = @statusCode OUTPUT;


          /* -------------------- 409 CONFLICT → RETRY + CLEANUP -------------------- */
          IF @statusCode = 409
          BEGIN
              SET @retryCount = 0;
              SET @maxRetries = 10;
              SET @retryDelaySeconds = 10;

              WHILE @retryCount < @maxRetries
              BEGIN
                  EXEC @returnValue = sp_Make_Restful_Call
                      @url = @createURL,
                      @method = 'PUT',
                      @payload = NULL,
                      @headers = @headers,
                      @credentialName = @credentialName,
                      @timeoutSeconds = @timeoutSeconds,
                      @response = @response OUTPUT,
                      @statusText = @statusText OUTPUT,
                      @statusCode = @statusCode OUTPUT;

                  IF @statusCode IN (201,204)
                      BREAK;

                  /* Cleanup blob if needed */
                  IF @blobID IS NOT NULL
                  BEGIN
                      EXEC @returnValue = sp_delete_message_from_blob
                           @storageAccountName = @storageAccountName,
                           @headers = @headersBlob,
                           @blobID = @blobID,
                           @response = @response OUTPUT,
                           @statusCode = @statusCode OUTPUT,
                           @statusText = @statusText OUTPUT;
                  END

                  /* Wait */
                  SET @waitTime = CONCAT('00:00:', RIGHT('0'+CAST(@retryDelaySeconds AS VARCHAR(2)),2));
                  WAITFOR DELAY @waitTime;

                  SET @retryCount += 1;
              END

              IF @statusCode NOT IN (201,204)
                  RAISERROR(
                      'Error: Create the queue failed after %d retries! statusCode: %d statusText: %s',
                      16,1,@retryCount,@statusCode,@statusText
                  );
          END

          /* Retry sending */
          EXEC @returnValue = sp_Make_Restful_Call
              @url = @postURL,
              @method = 'POST',
              @payload = @payload,
              @headers = @headers,
              @credentialName = @credentialName,
              @timeoutSeconds = @timeoutSeconds,
              @response = @response OUTPUT,
              @statusText = @statusText OUTPUT,
              @statusCode = @statusCode OUTPUT;
      END

	END CATCH

	return



    /* -------------------- UPDATE MONITORING -------------------- */
    UPDATE GalenicaQueue
       SET blobID = @blobID,
           statusCode = @statusCode,
           statusText = @statusText,
           response = @response
     WHERE galenicaQueueID = @galenicaQueueID;


    /* -------------------- FINAL VALIDATION -------------------- */
    IF @returnValue <> 0 OR @statusCode NOT BETWEEN 200 and 299
        RAISERROR('Error: Create the message into the queue failed! statusCode: %d statusText: %s',
                  16,1,@statusCode,@statusText);


    /* -------------------- PARSE RESPONSE -------------------- */
    SET @rawResponse = CAST(@response AS XML);

    SELECT  @messageID = msg.value('(MessageId)[1]','UNIQUEIDENTIFIER'),
            @insertionTime =
                CONVERT(DATETIME,
                    CAST(
                        LTRIM(RTRIM(
                            REPLACE(
                                SUBSTRING(
                                    msg.value('(InsertionTime)[1]','VARCHAR(MAX)'),
                                    CHARINDEX(',',msg.value('(InsertionTime)[1]','VARCHAR(MAX)'))+1,
                                    LEN(msg.value('(InsertionTime)[1]','VARCHAR(MAX)'))
                                ),
                                'GMT',''
                            )
                        ))
                    AS DATETIMEOFFSET)
                AT TIME ZONE 'UTC'
                AT TIME ZONE 'Central European Standard Time')
    FROM @rawResponse.nodes('/QueueMessagesList/QueueMessage') AS T(msg);


    UPDATE GalenicaQueue
       SET messageID = @messageID,
           blobID = @blobID,
           statusCode = @statusCode,
           statusText = @statusText,
           response = @response,
           insertionTime = @insertionTime
     WHERE galenicaQueueID = @galenicaQueueID;

END


GO