USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [reading].[usp_INS_self_onboard_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: 07-07-2025
-- Inserts a record into user_restr.sid_list directly from the connected user.
-- Note that adding a user to the SID list does not grant any permissions.
-- The permissions are mapped via the ACLs. 
-- Output is a status message and a transaction status and a sid_id. 
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2025
*/

-- =============================================
CREATE PROCEDURE [reading].[usp_INS_self_onboard_sid] 

	 @message nvarchar(1000)           = NULL 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)           = '',
	    @connectedusersid varbinary(100)     = SUSER_SID(ORIGINAL_LOGIN()),   -- The SID of the connected user
	    @username nvarchar(150)              = ORIGINAL_LOGIN(),     -- The username 
	    @transaction_ready nchar(10)       = 'Ready';

  -- 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';  
  
			   -- Check if a matching record already exists
				IF EXISTS (SELECT sid
				   			FROM user_restr.sid_list 
							WHERE restore_sid = @connectedusersid)
				BEGIN
					EXEC internal.usp_SEL_message 
						@message_id   = 'DuplSelfOnb', 
						@message_text = @tempmessage OUTPUT;
  				   IF @tempmessage IS NOT NULL 
				      SET @message = CONCAT_WS(' | ', @message, @tempmessage, @username);
			       ELSE  
				      SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on DuplSelfOnb.');
				   SET @transaction_ready      = 'Fail';
				END

		  -- Check if SID has resolved
		  IF @connectedusersid IS NULL OR @connectedusersid = 0
			BEGIN
					EXEC internal.usp_SEL_message 
						@message_id   = 'NoSID', 
						@message_text = @tempmessage OUTPUT;
  				   IF @tempmessage IS NOT NULL 
				      SET @message = CONCAT_WS(' | ', @message, @tempmessage, @username);
			       ELSE  
				      SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NoSID.');
				   SET @transaction_ready      = 'Fail';
			END


-- Execute the insert query
  IF @transaction_ready = 'Ready'
  	BEGIN
	  BEGIN TRY 
	     INSERT INTO user_restr.sid_list (sid,               username, restore_sid)
		      VALUES                     (@connectedusersid, @username, @connectedusersid);  
         EXEC internal.usp_SEL_message 
              @message_id   = 'Success', 
              @message_text = @tempmessage OUTPUT;
  			   IF @tempmessage IS NOT NULL 
  					SET @message = CONCAT_WS(' | ', @message, @tempmessage, @username, 'Onboarded');
				  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   = 'InsertError', 
              @message_text = @tempmessage OUTPUT;
		  IF @tempmessage IS NOT NULL 
				SET @message = CONCAT_WS(' | ', @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