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

	 @revoke_request_id bigint           = NULL,
	 @request_result nvarchar(10) = NULL OUTPUT


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



 SET @request_result = 'Pass';

	  -- Data validation
	  -- Check if a request_id has been supplied
		 IF @revoke_request_id = 0
		    SET @revoke_request_id = NULL;

		 IF @revoke_request_id IS NULL
			 SET @request_result         = 'Fail1';

         ELSE 
		    BEGIN
				 IF NOT EXISTS (SELECT request_id 	     -- Check if the request_id exists and is approved.
				  				  FROM user_restr.authoriser_privilege_requests 
								 WHERE request_id = @revoke_request_id
								   AND type = 'Revoke'
								   AND status = 'Approved') 
						 SET @request_result         = 'Fail2';
		   END

		   IF @request_result = 'Pass'
		      BEGIN

				 IF NOT EXISTS (SELECT apr.sid_id 	     -- Check that the sid_id of the request is already in the list of authorisers. 
				   			      FROM user_restr.authoriser_privilege_requests AS apr
						    INNER JOIN user_restr.authorisers AS a
						            ON            apr.sid_id 
								       = a.authoriser_sid_id
							     WHERE apr.request_id = @revoke_request_id) 
						 SET @request_result         = 'Fail3';
			  END

		   IF @request_result = 'Pass'
		     BEGIN
				-- Authenticate both the authorisers against the list of authorisers and check that they are not the same.
                  IF NOT EXISTS (SELECT apr.request_id 
				                   FROM user_restr.authoriser_privilege_requests AS apr
							 INNER JOIN user_restr.authorisers AS a
						             ON apr.approved_by_sid_id_1
								        = a.authoriser_sid_id
                                  WHERE apr.request_id = @revoke_request_id)
					 OR NOT EXISTS (SELECT apr.request_id 
				                      FROM user_restr.authoriser_privilege_requests AS apr
							    INNER JOIN user_restr.authorisers AS a
						                ON apr.approved_by_sid_id_2
								           = a.authoriser_sid_id
                                     WHERE apr.request_id = @revoke_request_id)
					OR EXISTS (SELECT apr.request_id -- Check that the approvers are not the same
				                 FROM user_restr.authoriser_privilege_requests AS apr
                                WHERE apr.request_id = @revoke_request_id
								  AND   apr.approved_by_sid_id_1
								      = apr.approved_by_sid_id_2)								 

						 SET @request_result         = 'Fail4';
               END

			   -- Prevent authorisers from inadvertently revoking their own privileges.  This prevents the situation where there are fewer than two
			   -- authorisers left and hence dba support is necessary to re-bootstrap the authorisers.
		   IF @request_result = 'Pass'
		     BEGIN
			  IF EXISTS (SELECT sid_id
			               FROM user_restr.authoriser_privilege_requests AS apr
						  WHERE apr.approved_by_sid_id_1
						        = apr.sid_id
							 OR apr.approved_by_sid_id_2
							    = apr.sid_id
							AND apr.request_id = @revoke_request_id)

						 SET @request_result         = 'Fail5';

			 END

		   IF @request_result = 'Pass'
		     BEGIN  -- Re-validate the SID_ID
				IF NOT EXISTS (SELECT sid_id
								 FROM user_restr.sid_list
								WHERE sid_id = (SELECT apr.sid_id
								                  FROM user_restr.authoriser_privilege_requests AS apr
												 WHERE apr.request_id = @revoke_request_id))
						 SET @request_result         = 'Fail6';			   
		      END


-- Execute the update query

  IF @request_result = 'Pass'
	BEGIN
	  BEGIN TRY
	    BEGIN TRANSACTION

	     DELETE FROM user_restr.authorisers 
               WHERE authoriser_sid_id = (SELECT sid_id 
			                                FROM user_restr.authoriser_privilege_requests AS apr
										   WHERE apr.request_id = @revoke_request_id)

		COMMIT TRANSACTION
   	  END TRY
	  BEGIN CATCH

	     SET @request_result = 'Fail7';
	  END CATCH

    END

END


GO