USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [controlling].[usp_DEL_file_gp_ed_perm_people]    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: 07-01-2024
-- Description:	Initial creation
-- Deletes a record from user_restr.file_group_edit_perm_ppl_lst to de-authorise 
-- a people list of users to edit file metadata within a file group. 
-- Input is a file group ID, a people list 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
*/
-- =============================================
/**
* Deletes a record to de-authorise a peopole list to edit metadata for files within a file group
* 
* **Acceptable Inputs:**
*
* - @filegroupid bigint  Must be a non-null and non-empty valid identifier.
* - @peoplelistid bigint Must be a non-null and non-empty valid identifier.
*
* **Return Values:**
*
* - @message nvarchar(1000) OUTPUT: Descriptive status message of the procedure's execution.
* - @transaction_status nchar(50) OUTPUT: Indicates the transaction status ('Good', 'Bad', or default 'Transaction not attempted').
*
* **Error and Exception Conditions:**
*
* - User Role Validation Fail: Returns 'No Permission' message.
* - Data Validation Fail: Returns messages for missing or invalid file group ID or people list ID or no matching record.
*
* **Side Effects:**
*
* - Deletes a record in 'user_restr.file_group_edit_perm_ppl_lst' table if all conditions are met.
*
* **Preconditions:**
*
* - The user executing the procedure must have the 'Controller' role.
* - @filegroupid and @peoplelistid must be provided and be valid and match an existing record
*
* **Postconditions:**
* - The procedure returns status messages indicating the outcome of the operation.
*
*/
-- =========================================================
CREATE PROCEDURE [controlling].[usp_DEL_file_gp_ed_perm_people] 

     @filegroupid bigint              = NULL,             -- File group 
	 @peoplelistid bigint             = NULL,          -- User duty people list id
	 @inputnotes nvarchar(1000)       = '',
	 @app_reference nvarchar(1000)    = '', -- Application reference text
	 @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)           = '',
		@tempuserauth_status nchar(10)       = '',
	    @connectedusersid varbinary(100)     = SUSER_SID(ORIGINAL_LOGIN()),   -- The SID of the connected user
	    @username nvarchar(150)              = ORIGINAL_LOGIN(),     -- The username 
		@sidid bigint                        = NULL, 
		@filegroupname nvarchar(50)          = '',
		@peoplelistname nvarchar(50)         = '',
		@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';  
 
  -- Connected user authentication
  -- Authenticate the connected user for the role
  EXEC [internal].[usp_AUTHENTICATE_user_role] 
        @role_to_check = 'Controller',
		@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_WS(' | ', @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 @filegroupid = 0
		    SET @filegroupid = NULL; -- Check if a file group ID has been supplied
		 IF @filegroupid IS NULL
		   BEGIN
			 SET @data_validation_status = 'Fail';
			 SET @transaction_ready      = 'Fail';
			 EXEC internal.usp_SEL_message 
				  @message_id   = 'NoFileGroupID', 
				  @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 NoFileGroupID');
		   END


      -- Check the people list id has been supplied
	     IF @peoplelistid = 0
		    SET @peoplelistid = NULL; -- Check if a file group ID has been supplied
		 IF @peoplelistid IS NULL
		   BEGIN
			 SET @data_validation_status = 'Fail';
			 SET @transaction_ready      = 'Fail';
			 EXEC internal.usp_SEL_message 
				  @message_id   = 'NoFunctionListID', 
				  @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 NoFunctionListID');
		   END


      -- Check if a matching record exists
	  IF @data_validation_status = 'Pass'
	    BEGIN
			 IF NOT EXISTS (SELECT file_group_id
				   	          FROM user_restr.file_group_edit_perm_ppl_lst 
						     WHERE        file_group_id = @filegroupid
						           AND people_list_id = @peoplelistid)
			   BEGIN
				 SET @data_validation_status = 'Fail';
				 SET @transaction_ready      = 'Fail';
				 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');
			   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 delete query
  IF @transaction_ready = 'Ready'
	BEGIN
	  BEGIN TRY
	   BEGIN TRANSACTION;
	     DELETE FROM user_restr.file_group_edit_perm_ppl_lst
		       WHERE     file_group_id    = @filegroupid
			         AND people_list_id = @peoplelistid; 

			 -- Create an audit log entry
			   -- Select the SID ID for the connected user
			  SELECT @sidid = sl.sid_id
				FROM user_restr.sid_list AS sl
			   WHERE sl.sid = @connectedusersid;

			   SELECT @filegroupname = attr_name
			     FROM xref.file_group_names
				WHERE file_group_id =  @filegroupid

			   SELECT @peoplelistname = name
			     FROM people.people_list_names
				WHERE people_list_id =  @peoplelistid

	 			  IF EXISTS (SELECT gsg.file_doc_change_log
						   FROM base.global_settings_groups AS gsg
						  WHERE gsg.setting_group_name = 'Master'
							AND gsg.file_doc_change_log = 'On')
				   BEGIN
					 INSERT INTO base.file_doc_data_log
								 (created_by_username, created_by_sid_id, change_type, change_field,       
								  record_id,    record_name, original_value, new_value, notes, app_reference)
						  VALUES (@username,           @sidid,            'Delete',    'File Group to People List Edit Permission Link',     
								  @filegroupid, @filegroupname, @peoplelistname,           NULL, @inputnotes, @app_reference)
				   END	  

				   
         -- Write to the privilege log --
		 INSERT INTO user_restr.user_privilege_log
		             (privilege_type,                  privilege_name,           linked_to,    action_type, 
					 created_by_sid_id, valid_from, valid_until, notes, app_reference)
			  VALUES ('File Group to People List Link for Editing', 
			  @filegroupname,
			  (SELECT name
				 FROM people.people_list_names
				WHERE people_list_id = @peoplelistid), 
			  'Revoke',
			    (SELECT sid_id
		           FROM user_restr.sid_list
				  WHERE sid = @connectedusersid), 
					 NULL, NULL, @inputnotes, @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
GO