USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [internal].[usp_AUTHORISE_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
-- Grants authoriser privileges on request from 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 [internal].[usp_AUTHORISE_authoriser] 

	 @grant_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;

  DECLARE 

		@authoriser_sid_id  bigint           = NULL,
		@authoriser_sid varbinary(100)       = NULL,
		@authoriser_notes nvarchar(max)      = '';  

 SET @request_result = 'Pass';

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

		 IF @grant_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 = @grant_request_id
								   AND type = 'Grant'
								   AND status = 'Approved') 
						 SET @request_result         = 'Fail2';
		   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 = @grant_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 = @grant_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 = @grant_request_id
								  AND   apr.approved_by_sid_id_1
								      = apr.approved_by_sid_id_2)								 

						 SET @request_result         = 'Fail4';
               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 = @grant_request_id))
						 SET @request_result         = 'Fail5';			   
		      END



-- Execute the update query

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

		   -- Assemble the data
		   SET @authoriser_sid_id = (SELECT apr.sid_id
									   FROM user_restr.authoriser_privilege_requests AS apr
									  WHERE apr.request_id = @grant_request_id)
		   SELECT @authoriser_sid = sl.sid
			 FROM user_restr.sid_list AS sl
		LEFT JOIN user_restr.authoriser_privilege_requests AS apr
			   ON apr.approved_by_sid_id_1
				  = sl.sid_id
			WHERE apr.request_id = @grant_request_id
											      
		   SET @authoriser_notes = ISNULL(SUSER_SNAME(@authoriser_sid), '');

		   SELECT @authoriser_sid = sl.sid
			 FROM user_restr.sid_list AS sl
		LEFT JOIN user_restr.authoriser_privilege_requests AS apr
			   ON apr.approved_by_sid_id_2
				  = sl.sid_id
			WHERE apr.request_id = @grant_request_id

		   SET @authoriser_notes = CONCAT(@authoriser_notes, ' | ', ISNULL(SUSER_SNAME(@authoriser_sid), ''));

		   -- Note that if the user is already authorised as an Authoriser then re-certification requires revoking of the 
		   -- existing authorisation and following the same process as for an original grant.  

		   -- Execute the transaction
			 INSERT INTO user_restr.authorisers 
						 (authoriser_sid_id, valid_from, valid_until, notes, app_reference)
				  VALUES (@authoriser_sid_id,  
				  (SELECT apr.valid_from
					 FROM user_restr.authoriser_privilege_requests AS apr
					WHERE apr.request_id = @grant_request_id), 
				  (SELECT apr.valid_until
					 FROM user_restr.authoriser_privilege_requests AS apr
					WHERE apr.request_id = @grant_request_id), 
				  @authoriser_notes, 
				  (SELECT apr.app_reference
					 FROM user_restr.authoriser_privilege_requests AS apr
					WHERE apr.request_id = @grant_request_id))

        COMMIT TRANSACTION
   	  END TRY
	  BEGIN CATCH
	     IF XACT_STATE() <> 0
	           ROLLBACK TRANSACTION;
	     SET @request_result = 'Fail6';
	  END CATCH

    END

END


GO