USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [authorising].[usp_UPD_cancel_authoriser_requ]    Script Date: Sat 05-09-2026 7:01:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Silkwood S11-07-2025
-- Description:	Initial creation
-- Cancels an authoriser pending grant request.   
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2025
*/
-- =============================================
CREATE PROCEDURE [authorising].[usp_UPD_cancel_authoriser_requ] 

	 @request_id bigint               = NULL,
	 @message nvarchar(1000)          = '' OUTPUT,
	 @transaction_status nvarchar(50) = NULL OUTPUT


AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  DECLARE 
        @tempmessage nvarchar(300)           = '',
		@username nvarchar(150)              = ORIGINAL_LOGIN(),     -- The username 
		@connectedusersid varbinary(100)     = SUSER_SID(ORIGINAL_LOGIN()),   -- The SID of the connected user
		@subject_sid varbinary(100)          = NULL, -- The SID of the user relating to the request being cancelled. 
		@subject_username nvarchar(150)      = '',  -- The username of the user relating to the request being cancelled.
		@request_type nvarchar(20)           = '', -- The type of privilege change request.
		@userauthentication_status nchar(10) = 'Fail', -- The outcome of the authentication check of the user 
		@transaction_ready nchar(10)         = 'Ready',
		@data_validation_status nchar(10)    = 'Pass';


  SET @transaction_status = 'Transaction not attempted';
	
 
  -- Connected user authentication
  -- Authenticate the connected user as an authoriser
  EXEC [internal].[usp_AUTHENTICATE_authoriser] 
		@user_authentication_result = @userauthentication_status OUTPUT;
  IF @userauthentication_status = 'Fail'
    BEGIN  -- The user does not have permission for this action
		SET @transaction_ready      = 'Fail';
	    EXEC internal.usp_SEL_message 
            @message_id   = 'NoPermission', 
			@message_text = @tempmessage OUTPUT;
	    IF (@tempmessage IS NOT NULL) 
  	       SET @message = CONCAT(@message, ' | ', ISNULL(@username, ''), '  ', @tempmessage);
	    ELSE 
		   SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NoPermission');
	END

  IF @userauthentication_status = 'Pass' -- Don't do anything if the user is not authorised.
    BEGIN
	  -- Data validation
	  -- Check if a request_id has been supplied
		 IF @request_id = 0
		    SET @request_id = NULL;
		 IF @request_id IS NULL
		   BEGIN
			 EXEC internal.usp_SEL_message 
				  @message_id   = 'NoRequestID', 
				  @message_text = @tempmessage OUTPUT;
			 IF (@tempmessage IS NOT NULL) 
			 	SET @message = CONCAT_WS(' | ', @message, @tempmessage);
		     ELSE 
				SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NoRequestID.');
			 SET @data_validation_status = 'Fail';
			 SET @transaction_ready      = 'Fail';
		   END
         ELSE 
		    BEGIN
				 IF NOT EXISTS (SELECT request_id 	     -- Check if the request_id exists
				  				  FROM user_restr.authoriser_privilege_requests 
								 WHERE request_id = @request_id) 
					   BEGIN
						 SET @data_validation_status = 'Fail';
						 SET @transaction_ready      = 'Fail';
						 EXEC internal.usp_SEL_message 
							  @message_id   = 'RequestIDNotExist', 
							  @message_text = @tempmessage OUTPUT;
						 IF (@tempmessage IS NOT NULL) 
  							SET @message = CONCAT(@message, ' | ', ISNULL(CONVERT(nvarchar, @request_id), 'NULL'), ' | ', @tempmessage);
						 ELSE 
				            SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on RequestIDNotExist.');
					   END
		   END

		   IF @data_validation_status = 'Pass'
		      BEGIN
				 IF NOT EXISTS (SELECT request_id 	     -- Check that the status = Pending
				  				  FROM user_restr.authoriser_privilege_requests AS apr
								 WHERE request_id = @request_id
								   AND apr.status = 'Pending') 
					   BEGIN
						 SET @data_validation_status = 'Fail';
						 SET @transaction_ready      = 'Fail';
						 EXEC internal.usp_SEL_message 
							  @message_id   = 'NotPending', 
							  @message_text = @tempmessage OUTPUT;
						 IF (@tempmessage IS NOT NULL) 
  							SET @message = CONCAT(@message, ' | ', ISNULL(CONVERT(nvarchar, @request_id), 'NULL'), ' | ', @tempmessage);
						 ELSE 
				            SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NotPending.');
					   END
				END


		   IF @data_validation_status = 'Pass'
		     BEGIN
				-- Authoriser cannot cancel a request to revoke their own authorisation privilege.
                  IF EXISTS (SELECT apr.sid_id 
				               FROM user_restr.authoriser_privilege_requests AS apr
						  LEFT JOIN user_restr.sid_list AS sl
						         ON  apr.sid_id
								    = sl.sid_id
                              WHERE apr.request_id = @request_id
							        AND sl.sid = @connectedusersid)
					   BEGIN
						 EXEC internal.usp_SEL_message 
							  @message_id   = 'NoSelfCancel', 
							  @message_text = @tempmessage OUTPUT;
						 IF (@tempmessage IS NOT NULL) 
			 				SET @message = CONCAT_WS(' | ', @message, @tempmessage);
						 ELSE 
							SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NoSelfCancel.');
						 SET @data_validation_status = 'Fail';
						 SET @transaction_ready      = 'Fail';
					   END
               END



	  -- Output the data validation status failed message
	  IF @data_validation_status = 'Fail'
		BEGIN
		  EXEC internal.usp_SEL_message 
			   @message_id   = 'FailedDataValidation', 
			   @message_text = @tempmessage OUTPUT;
		  IF (@tempmessage IS NOT NULL) 
			SET @message = CONCAT_WS(' | ', @message, @tempmessage);
		  ELSE 
			SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on FailedDataValidation.');
		END
	  -- End data validation
    END -- End of IF authentication status = Pass.

-- Execute the update query

  IF @transaction_ready = 'Ready'
	BEGIN
	  BEGIN TRY

	  BEGIN TRANSACTION;

	  SET @subject_sid = (SELECT sl.sid
	                        FROM user_restr.sid_list as sl
						   WHERE sl.sid_id = (SELECT apr.sid_id
						                        FROM user_restr.authoriser_privilege_requests AS apr
											   WHERE apr.request_id = @request_id))
	  SET @subject_username = ISNULL(SUSER_SNAME(@subject_sid), '');
	  SET @request_type = (SELECT apr.type
	                         FROM user_restr.authoriser_privilege_requests AS apr
							WHERE apr.request_id = @request_id)

	     UPDATE user_restr.authoriser_privilege_requests 
		    SET status = 'Cancelled'
		  WHERE request_id = @request_id; 

        COMMIT TRANSACTION;

         EXEC internal.usp_SEL_message 
              @message_id   = 'Success', 
              @message_text = @tempmessage OUTPUT;
  	     IF (@tempmessage IS NOT NULL) 
			SET @message = CONCAT_WS(' | ', @message, @tempmessage, @subject_username, @request_type, 'Request cancelled.');
		 ELSE 
			SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on Success.');
		 SET @transaction_status = 'Good';
   	  END TRY
	  BEGIN CATCH

	     IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

	     SET @transaction_status = 'Bad';
         EXEC internal.usp_SEL_message 
              @message_id   = 'UpdateError', 
              @message_text = @tempmessage OUTPUT;
		 IF (@tempmessage IS NOT NULL) 
		    SET @message = CONCAT(@message, ' |', @tempmessage, ' | ', 
		    CONVERT(nvarchar(10),ERROR_NUMBER()), ' | ', ERROR_MESSAGE());
		 ELSE 
			SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on UpdateError.');
	  END CATCH
    END

END
GO