USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [authorising].[usp_DEL_contr_doc_group_sid]    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: 06-09-2024
-- Description:	Initial creation
-- Delete a record from user_restr.controller_doc_group_sid_links
-- Input is the sid_id of the user from the sid_list table and 
-- the controller level document group id.  
-- Output is a status message and a transaction status.  
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2024
*/
-- =============================================
CREATE PROCEDURE [authorising].[usp_DEL_contr_doc_group_sid] 

     @contr_doc_ed_grp_name_id bigint = NULL,  -- Controller level document group 
	 @user_sid_id bigint              = NULL,  -- User sid_id from the sid_list table
	 @notes nvarchar(1000)            = '',    -- 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 username 
		@connectedusersid varbinary(100)     = SUSER_SID(ORIGINAL_LOGIN()),   -- The SID of the connected user
		@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 sid_id has been supplied
		 IF @user_sid_id = 0
		    SET @user_sid_id = NULL;
		 IF @user_sid_id IS NULL
		   BEGIN
			 EXEC internal.usp_SEL_message 
				  @message_id   = 'NoUserSidID', 
				  @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 NoUserSidID.');
			 SET @data_validation_status = 'Fail';
			 SET @transaction_ready      = 'Fail';
		   END
         ELSE 
		    BEGIN
				 IF NOT EXISTS (SELECT sid_id 	     -- Check sid_id exists
				  				  FROM user_restr.sid_list 
								 WHERE sid_id = @user_sid_id) 
					   BEGIN
						 SET @data_validation_status = 'Fail';
						 SET @transaction_ready      = 'Fail';
						 EXEC internal.usp_SEL_message 
							  @message_id   = 'UserIdNotExist', 
							  @message_text = @tempmessage OUTPUT;
						 IF (@tempmessage IS NOT NULL) 
  							SET @message = CONCAT(@message, ' | ', ISNULL(CONVERT(nvarchar, @user_sid_id), 'NULL'), ' | ', @tempmessage);
						 ELSE 
				            SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on UserIdNotExist.');
					   END
                  IF EXISTS (SELECT sid -- Check that the supplied sid is not the same as the connected user sid
				               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   = 'SelfAuth', 
							  @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 SelfAuth.');
						 SET @data_validation_status = 'Fail';
						 SET @transaction_ready      = 'Fail';
					   END
		   END

		   -- Check the controller level document group
	     IF @contr_doc_ed_grp_name_id = 0
		    SET @contr_doc_ed_grp_name_id = NULL;
		 IF @contr_doc_ed_grp_name_id IS NULL
		   BEGIN
			 EXEC internal.usp_SEL_message 
				  @message_id   = 'NoConDocGroup', 
				  @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 NoConDocGroup.');
			 SET @data_validation_status = 'Fail';
			 SET @transaction_ready      = 'Fail';
		   END
		 ELSE
	        BEGIN -- Check the document group exists
				 IF NOT EXISTS (SELECT controller_doc_group_name_id
				                  FROM xref.controller_doc_group_names
								 WHERE controller_doc_group_name_id = @contr_doc_ed_grp_name_id)
				   BEGIN
					 EXEC internal.usp_SEL_message 
						  @message_id   = 'ConDocGpNotExist', 
						  @message_text = @tempmessage OUTPUT;
					 IF (@tempmessage IS NOT NULL) 
  						SET @message = CONCAT(@message, ' | ', ISNULL(CONVERT(nvarchar(10), @contr_doc_ed_grp_name_id), 'NULL'), ' | ', @tempmessage); 
					 ELSE 
			            SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on ConDocGpNotExist.');
					 SET @data_validation_status = 'Fail';
					 SET @transaction_ready      = 'Fail';
				   END
		    END

      -- Check if the record exists
	  IF @data_validation_status = 'Pass'
	    BEGIN
			 IF NOT EXISTS (SELECT controller_doc_group_name_id, sid_id
				   	          FROM user_restr.controller_doc_group_sid_links 
						     WHERE controller_doc_group_name_id = @contr_doc_ed_grp_name_id
						           AND sid_id = @user_sid_id)
			   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
		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


-- Execute the insert query

  IF @transaction_ready = 'Ready'
	BEGIN
	  BEGIN TRY
	  
	  BEGIN TRANSACTION;

	  INSERT INTO user_restr.user_privilege_revoke_log -- Create an audit log entry
	              (privilege, privilege_name, sid_id, username, granted_on, granted_by, revoked_by)
	       VALUES ('Controller Document Group',
		          (SELECT attr_name 
				     FROM xref.controller_doc_group_names
				    WHERE controller_doc_group_name_id  
						  = @contr_doc_ed_grp_name_id),
		          @user_sid_id,
		          (SELECT username 
				     FROM user_restr.sid_list
				    WHERE          sid_id 
						   = @user_sid_id),
		          (SELECT created 
				     FROM user_restr.controller_doc_group_sid_links
				    WHERE  controller_doc_group_name_id  
						   = @contr_doc_ed_grp_name_id
					  AND     sid_id 
						   = @user_sid_id),
		          (SELECT granted_by 
				     FROM user_restr.controller_doc_group_sid_links
				    WHERE  controller_doc_group_name_id  
						  = @contr_doc_ed_grp_name_id
						  AND     sid_id 
						  = @user_sid_id),
				  @username)

	    DELETE FROM user_restr.controller_doc_group_sid_links
		      WHERE  controller_doc_group_name_id  
		            = @contr_doc_ed_grp_name_id
		            AND     sid_id 
				    = @user_sid_id; 

         -- Write to the privilege log --


		 -- Write to the log
		 INSERT INTO user_restr.user_privilege_log
		             (privilege_type,                  privilege_name,           sid_id,      username, action_type, 
					 created_by_sid_id, valid_from, valid_until, notes, app_reference)
			  VALUES ('Controller Document Group User', 
				(SELECT attr_name
				   FROM xref.controller_doc_group_names
				  WHERE controller_doc_group_name_id = @contr_doc_ed_grp_name_id), 
			  @user_sid_id, 
			  (SELECT username
		         FROM user_restr.sid_list
				WHERE sid_id = @user_sid_id), 
			  'Revoke',
			    (SELECT sid_id
		           FROM user_restr.sid_list
				  WHERE sid = @connectedusersid), 
				  
				 NULL, NULL, @notes, @app_reference)


         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);
		 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   = 'DeleteError', 
              @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 DeleteError.');
	  END CATCH
    END
    END -- End of IF authentication status = Pass.
END
GO