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

	 @request_id bigint               = NULL,
	 @user_sid_id bigint              = NULL,
	 @valid_from datetime2(7)         = NULL,  -- Optional
	 @valid_until datetime2(7)        = NULL,  -- Optional
	 @notes nvarchar(1000)            = NULL,  -- Optional
	 @app_reference nvarchar(1000)    = '',    -- Optional
	 @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 connected username 
		@connectedusersid varbinary(100)     = SUSER_SID(ORIGINAL_LOGIN()),   -- The SID of the connected user
		@connectedusersid_id bigint          = NULL,  -- SID ID of the connected user.
		@subject_sid varbinary(100)          = NULL, -- The SID of the user relating to the request . 
		@subject_username nvarchar(128)      = '',  -- The username of the user to be granted authoriser privileges. 
		@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';

  -- Parameters which have been initialised at declaration but not explicitly set might be output as null to calling functions.
  SET @transaction_status = 'Transaction not attempted'; 
  SET @connectedusersid_id = (SELECT sl.sid_id 
                                FROM user_restr.sid_list AS sl
							   WHERE sl.sid = @connectedusersid)
  
  -- 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
		 IF @request_id = 0
		    SET @request_id = NULL;

		 IF @user_sid_id = 0
		    SET @user_sid_id = NULL;
		
		 IF @request_id IS NULL AND @user_sid_id IS NULL
		   BEGIN
			 EXEC internal.usp_SEL_message 
				  @message_id   = 'NoSidNoRequ', 
				  @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 NoSidNoRequ.');
			 SET @data_validation_status = 'Fail';
			 SET @transaction_ready      = 'Fail';
		   END

		IF @user_sid_id IS NOT NULL  -- If a SID ID has been supplied then validate it. 
		  BEGIN
		    IF NOT EXISTS (SELECT sid_id
			                FROM user_restr.sid_list
						   WHERE sid_id = @user_sid_id)
					   BEGIN
						 EXEC internal.usp_SEL_message 
							  @message_id   = 'SidIDNotExist', 
							  @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 SidIDNotExist.');
						 SET @data_validation_status = 'Fail';
						 SET @transaction_ready      = 'Fail';
					   END				   
		  END


		IF @data_validation_status = 'Pass' AND @user_sid_id IS NOT NULL
		  BEGIN
			IF EXISTS (SELECT a.authoriser_sid_id 	     -- Check that the sid_id of the request is already in the list of authorisers. 
				    	 FROM user_restr.authorisers AS a						   
						 WHERE a.authoriser_sid_id = @user_sid_id) 
				   BEGIN
					 EXEC internal.usp_SEL_message 
						  @message_id   = 'RecordExists', 
						  @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 RecordExists.');
					 SET @data_validation_status = 'Fail';
					 SET @transaction_ready      = 'Fail';
				   END
				   ELSE -- If it is not already in the list of authorisers, check that it is not the same as the approver.
				     BEGIN
					   IF EXISTS (SELECT sl.sid_id
					                FROM user_restr.sid_list AS sl
								   WHERE sl.sid 
								         = @connectedusersid
									 AND sl.sid_id
									     = @user_sid_id)
							BEGIN
								 EXEC internal.usp_SEL_message 
									  @message_id   = 'UserSelfAuth', 
									  @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 UserSelfAuth.');
								 SET @data_validation_status = 'Fail';
								 SET @transaction_ready      = 'Fail';										 
							END
					 END
		   END

		IF @data_validation_status = 'Pass'
		  BEGIN
		    IF @request_id IS NOT NULL AND @user_sid_id IS NOT NULL
			  BEGIN -- If both request ID and sid_id are supplied then check if a record exists
			    IF NOT EXISTS (SELECT apr.request_id
				                 FROM user_restr.authoriser_privilege_requests AS apr
								WHERE apr.sid_id = @user_sid_id
								  AND apr.request_id = @request_id
								  AND apr.type = 'Grant'
								  AND apr.status = 'Pending')
							BEGIN
								 EXEC internal.usp_SEL_message 
									  @message_id   = 'NotExist', 
									  @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 NotExist.');
								 SET @data_validation_status = 'Fail';
								 SET @transaction_ready      = 'Fail';										 
							END
							ELSE  -- If a record does exist then check that the current approver is no the same as the first. 
							   BEGIN
							     IF EXISTS (SELECT apr.approved_by_sid_id_1
								              FROM user_restr.authoriser_privilege_requests AS apr
										     WHERE apr.request_id = @request_id
											   AND apr.type = 'Grant'
											   AND apr.status = 'Pending'
											   AND apr.approved_by_sid_id_1 = @connectedusersid_id)
									BEGIN
										 EXEC internal.usp_SEL_message 
											  @message_id   = 'DuplApprover', 
											  @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 DuplApprover.');
										 SET @data_validation_status = 'Fail';
										 SET @transaction_ready      = 'Fail';										 
									END					 
							   END
			  END
		  END

		IF @data_validation_status = 'Pass'
		  BEGIN
		    IF @request_id IS NOT NULL AND @user_sid_id IS NULL
			  BEGIN -- If request ID is supplied then check if a record exists
			    IF NOT EXISTS (SELECT apr.request_id
				                 FROM user_restr.authoriser_privilege_requests AS apr
								WHERE apr.request_id = @request_id
								  AND apr.type = 'Grant'
								  AND apr.status = 'Pending')
							BEGIN
								 EXEC internal.usp_SEL_message 
									  @message_id   = 'NotExist', 
									  @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 NotExist.');
								 SET @data_validation_status = 'Fail';
								 SET @transaction_ready      = 'Fail';										 
							END
							ELSE  -- If a record does exist then check that the current approver is not the same as the first. 
							   BEGIN
							     IF EXISTS (SELECT apr.approved_by_sid_id_1
								              FROM user_restr.authoriser_privilege_requests AS apr
										     WHERE apr.request_id = @request_id
											   AND apr.type = 'Grant'
											   AND apr.status = 'Pending'
											   AND apr.approved_by_sid_id_1 = @connectedusersid_id)
									BEGIN
										 EXEC internal.usp_SEL_message 
											  @message_id   = 'DuplApprover', 
											  @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 DuplApprover.');
										 SET @data_validation_status = 'Fail';
										 SET @transaction_ready      = 'Fail';										 
									END						 
							   END
			  END
		  END

		IF @data_validation_status = 'Pass'
		  BEGIN
		    IF @request_id IS NULL AND @user_sid_id IS NOT NULL
			  BEGIN -- If SID ID is supplied then check if a record exists
			    IF EXISTS (SELECT apr.request_id
				                 FROM user_restr.authoriser_privilege_requests AS apr
								WHERE apr.sid_id = @user_sid_id
								  AND apr.type = 'Grant'
								  AND apr.status = 'Pending')
					  -- If a record does exist then check that the current approver is no the same as the first. 
							   BEGIN

							   SET @request_id = (SELECT TOP 1 apr.request_id
							                        FROM user_restr.authoriser_privilege_requests AS apr
												   WHERE apr.sid_id = @user_sid_id
													 AND apr.type = 'Grant'
													 AND apr.status = 'Pending')

							     IF EXISTS (SELECT apr.approved_by_sid_id_1
								              FROM user_restr.authoriser_privilege_requests AS apr
										     WHERE apr.sid_id = @user_sid_id
											   AND apr.type = 'Grant'
											   AND apr.status = 'Pending'
											   AND apr.approved_by_sid_id_1 = @connectedusersid_id)
									BEGIN
										 EXEC internal.usp_SEL_message 
											  @message_id   = 'DuplApprover', 
											  @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 DuplApprover.');
										 SET @data_validation_status = 'Fail';
										 SET @transaction_ready      = 'Fail';										 
									END						 
							   END
			  END
		  END


	  -- Date validation
	  IF @valid_from IS NOT NULL
         AND @valid_until IS NOT NULL
         AND @valid_until < @valid_from
			      BEGIN
					SET @data_validation_status = 'Fail';
					SET @transaction_ready      = 'Fail';
					EXEC internal.usp_SEL_message 
						@message_id = 'DateInvalid', 
						@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 DateInvalid.');
				  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 insert query
  IF @transaction_ready = 'Ready'
	BEGIN
	  BEGIN TRY

	  BEGIN TRANSACTION

		    IF @request_id IS NULL AND @user_sid_id IS NOT NULL
			  BEGIN -- If sid_id is supplied then check if a record exists
			  		-- Retrieve the username of the user to be granted the privileges.
					SET @subject_sid = (SELECT sl.sid
										FROM user_restr.sid_list as sl
										WHERE sl.sid_id = @user_sid_id)
					SET @subject_username = ISNULL(SUSER_SNAME(@subject_sid), '');
			    IF NOT EXISTS (SELECT apr.request_id  -- If no record then create a new one and populate approver 1
				                 FROM user_restr.authoriser_privilege_requests AS apr
								WHERE apr.sid_id = @user_sid_id
								  AND apr.type = 'Grant'
								  AND apr.status = 'Pending')
							BEGIN
							  INSERT INTO user_restr.authoriser_privilege_requests
							              (sid_id,       type,    username,         approved_by_sid_id_1, approved_by_username_1, approved_on_1, status,
										  valid_from, valid_until, notes, app_reference)
								   VALUES (@user_sid_id, 'Grant', @subject_username, @connectedusersid_id, @username,              SYSDATETIME(), 'Pending',
								          @valid_from, @valid_until, @notes, @app_reference)
								 EXEC internal.usp_SEL_message 
									  @message_id   = 'Approver1Complete', 
									  @message_text = @tempmessage OUTPUT;
  								 IF (@tempmessage IS NOT NULL) 
									SET @message = CONCAT_WS(' | ', @message, @tempmessage, @subject_username);
								 ELSE 
									SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on Approver1Complete.');
								 SET @transaction_status = 'Good';
							END
					ELSE -- The record exists.  Update it with approver 2.  (@valid_from, @valid_until and @app_reference will be ignored.  Only the initial values will be used)
					  BEGIN

					   UPDATE user_restr.authoriser_privilege_requests 
					      SET approved_by_sid_id_2 = @connectedusersid_id,
						      approved_by_username_2 = @username,
						      approved_on_2 = SYSDATETIME(),
						      status = 'Approved'
					    WHERE sid_id = @user_sid_id
	                      AND type = 'Grant'
						  AND status = 'Pending'


						  EXEC internal.usp_AUTHORISE_authoriser
						       @grant_request_id = @request_id,
							   @request_result = @tempmessage OUTPUT;
						  IF @tempmessage = 'Pass'
						    BEGIN
								 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, 'Authoriser privileges granted');
								 ELSE 
									SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on Success.');
								 SET @transaction_status = 'Good';
		                   END
						   ELSE
								BEGIN
									 EXEC internal.usp_SEL_message 
										  @message_id   = 'ApprovalFailed', 
										  @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 ApprovalFailed.');
									 SET @transaction_status = 'Bad';
							   END						   
					  END
			  END
			  ELSE -- A record exists and is referred to by @request_id.  Populate approver 2. 
			    BEGIN

					  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), '');

					   UPDATE user_restr.authoriser_privilege_requests
					      SET approved_by_sid_id_2 = @connectedusersid_id,
						      approved_by_username_2 = @username,
						      approved_on_2 = SYSDATETIME(),
						      status = 'Approved'
					    WHERE request_id = @request_id

						  EXEC internal.usp_AUTHORISE_authoriser
						       @grant_request_id = @request_id,
							   @request_result = @tempmessage OUTPUT;
						  IF @tempmessage = 'Pass'
						    BEGIN
								 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, 'Authoriser privileges granted');
								 ELSE 
									SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on Success.');
								 SET @transaction_status = 'Good';
		                   END
						   ELSE
								BEGIN
									 EXEC internal.usp_SEL_message 
										  @message_id   = 'ApprovalFailed', 
										  @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 ApprovalFailed.');
									 SET @transaction_status = 'Bad';
							   END		
				END
       COMMIT TRANSACTION;

   	  END TRY
	  BEGIN CATCH

	     IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

	     SET @transaction_status = 'Bad';
         EXEC internal.usp_SEL_message 
              @message_id   = 'InsertError', 
              @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 InsertError.');
	  END CATCH
    END


END
GO