USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [controlling].[usp_DEL_file_group_name]    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-01-2024
-- Description:	Initial creation
-- Deletes a file group name record.
-- Input is a file group id  
-- Output is a status message.  
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023
*/

-- =============================================
/**
* Deletes a file group name record.
* 
* **Acceptable Inputs:**
*
* - @id_to_delete 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, invalid or duplicate @attr_name.
*
* **Side Effects:**
*
* - Deletes a record in 'xref.file_group_names' table if all conditions are met.
*
* **Preconditions:**
*
* - The user executing the procedure must have the 'Controller' role.
* - @id_to_delete must be provided and valid.
* - There must be no references
*
* **Postconditions:**
* - The procedure returns status messages indicating the outcome of the operation.
*
*/
-- =========================================================
CREATE PROCEDURE [controlling].[usp_DEL_file_group_name] 

	 @id_to_delete bigint             = NULL,
	 @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 

	    @connectedusersid varbinary(100)        = SUSER_SID(ORIGINAL_LOGIN()),   -- The SID of the connected user
	    @username nvarchar(150)                 = ORIGINAL_LOGIN(),     -- The username 
		@userauthentication_status nchar(10)    = 'Fail', -- The outcome of the authentication check of the user 
	    @validationmessage nvarchar(200)        = '', -- Communicates that the transaction failed due to data validation
        @tempmessage nvarchar(300)              = '',
		@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

-- Data validation
  -- Validate the file group id
  IF @userauthentication_status = 'Pass' -- Don't do anything if the user is not authorised.
    BEGIN
	 IF @id_to_delete = 0
		SET @id_to_delete = NULL;
	 IF @id_to_delete IS NULL
		 BEGIN -- No ID has been supplied
		   SET @data_validation_status = 'Fail';
		   SET @transaction_ready      = 'Fail';
		   EXEC internal.usp_SEL_message 
				@message_id = 'NoRecordID', 
				@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 NoRecordID.');
		 END
	  ELSE -- A record id has been supplied
		 BEGIN  -- Check that the id exists
		   IF NOT EXISTS (SELECT file_group_id 
						FROM xref.file_group_names AS fgn
					   WHERE fgn.file_group_id = @id_to_delete)
			   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
  END  -- End IF @userauthentication_status = 'Pass'


  -- Check for any referential constraints
  IF @data_validation_status = 'Pass'
    BEGIN
	  IF EXISTS (SELECT fgl.file_group_id
				   FROM xref.file_group_links AS fgl
				  WHERE fgl.file_group_id = @id_to_delete)  
		OR EXISTS (SELECT fgepfl.file_group_id 
					 FROM user_restr.file_group_edit_perm_funct_lst AS fgepfl
					WHERE fgepfl.file_group_id  = @id_to_delete) 
		OR EXISTS (SELECT fgeppl.file_group_id 
					 FROM user_restr.file_group_edit_perm_ppl_lst AS fgeppl
					WHERE fgeppl.file_group_id  = @id_to_delete) 
		  BEGIN
			SET @data_validation_status = 'Fail';
		    SET @transaction_ready      = 'Fail';
 			EXEC internal.usp_SEL_message 
			   @message_id = 'XRefExists', @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 XRefExists.');

		  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 delete query
  IF @transaction_ready = 'Ready'
	BEGIN
	  BEGIN TRY
	   BEGIN TRANSACTION;
	    DELETE FROM xref.file_group_names 
		      WHERE file_group_id = @id_to_delete;
	   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(' | ', @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