USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [internal].[usp_AUTHENTICATE_cont_doc_grp]    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: 04-09-2024
-- Description:	Checks whether the connected user is a member
-- of a given controller document edit group. 
-- Also returns fail if the id does not exist.
-- Input is a the id of the document group to be checked.  
-- Output is a status string of 'Pass' or 'Fail'.

-- If a document is linked to a controller group then if the connected user is not part of that group
-- then the user is excluded from editing rights.
-- There are two layers of editing permissions.  At one level, controllers are granted exclusive editing
-- permissions by an authoriser, referred to as controller level permission. Other controllers cannot alter those permissions.
-- At the other level controllers grant editing permissions to editors, referred to as editor level permission.  Controller level permissions
-- exclude and override any editor level permission.  A controller cannot delegate permission for a controller level permission. 



/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2024
*/

-- =============================================
CREATE PROCEDURE [internal].[usp_AUTHENTICATE_cont_doc_grp] 

	@contr_doc_grp_to_check bigint               = NULL,
	@user_authentication_result_cep nvarchar(10) = NULL OUTPUT


AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  DECLARE 
  		@usersid varbinary(100)            = NULL, -- The SID of the connected user
		@iscontroller nvarchar(5)          = NULL,
		@sid_id bigint                     = NULL,
		@failure_type nvarchar(1000)       = 'Fail',
		@now datetime2(7)                  = SYSDATETIME(),
		@privilege_name nvarchar(50)       = '';		

  -- Parameters which have been initialised at declaration but not explicitly set might be output as null to calling functions.
  SET @user_authentication_result_cep  = 'Fail'

  SET @usersid = SUSER_SID(ORIGINAL_LOGIN()); -- Read the SID of the connected user

  		SELECT @sid_id = sid_id
          FROM user_restr.sid_list
         WHERE sid = @usersid;
	

  SET @privilege_name = (SELECT attr_name
                           FROM xref.controller_doc_group_names
						  WHERE controller_doc_group_name_id = @contr_doc_grp_to_check)
			  
	  EXEC [internal].[usp_AUTHENTICATE_user_role] 
	  		 @role_to_check = 'Controller',
			 @user_authentication_result = @iscontroller OUTPUT;	
			 

		IF EXISTS 
			  (SELECT sl.sid_id
				 FROM user_restr.sid_list AS sl
		   INNER JOIN user_restr.controller_doc_group_sid_links AS cdgsl
				   ON      sl.sid_id
					  = cdgsl.sid_id
		   INNER JOIN xref.controller_doc_group_names AS cdgn 
				   ON  cdgsl.controller_doc_group_name_id
					  = cdgn.controller_doc_group_name_id
	 			WHERE cdgsl.controller_doc_group_name_id = @contr_doc_grp_to_check
				  AND  sl.sid = @usersid 
			      AND (cdgsl.valid_from IS NULL OR cdgsl.valid_from <= @now)
				  AND (cdgsl.valid_until IS NULL OR cdgsl.valid_until >= @now)
  			           )
		    AND @iscontroller = 'Pass'
		 SET @user_authentication_result_cep = 'Pass';
		 ELSE 
		  BEGIN
		    SET @user_authentication_result_cep = 'Fail';
--  ============================================================================================
		-- Authorisation failure diagnostics

		DECLARE
			@controller_failure nvarchar(1000) = NULL,
			@group_failure      nvarchar(1000) = NULL;


		-- ============================================================================================
		-- CONTROLLER ROLE
		-- ============================================================================================

		IF @iscontroller <> 'Pass'
		BEGIN
			SET @controller_failure =
				'Controller Role: User Is Not a Controller';
		END;


		-- ============================================================================================
		-- CONTROLLER DOCUMENT GROUP SID LINK
		--
		-- The user must:
		--   1. Have a SID entry
		--   2. Be linked to the specified Controller Document Group
		--   3. Have a currently valid link
		-- ============================================================================================

		-- Check whether the user has any SID link to this Controller Document Group
		IF NOT EXISTS
		(
			SELECT 1
			  FROM user_restr.sid_list AS sl
			 INNER JOIN user_restr.controller_doc_group_sid_links AS cdgsl
					 ON sl.sid_id = cdgsl.sid_id
			 INNER JOIN xref.controller_doc_group_names AS cdgn
					 ON cdgsl.controller_doc_group_name_id
						= cdgn.controller_doc_group_name_id
			 WHERE cdgsl.controller_doc_group_name_id = @contr_doc_grp_to_check
			   AND sl.sid = @usersid
		)
		BEGIN
			SET @group_failure =
				'Controller Document Group: User Not Linked to Document Group';
		END
		ELSE
		BEGIN

			-- The user is linked to the group, so determine whether
			-- the link is currently valid.
			IF NOT EXISTS
			(
				SELECT 1
				  FROM user_restr.sid_list AS sl
				 INNER JOIN user_restr.controller_doc_group_sid_links AS cdgsl
						 ON sl.sid_id = cdgsl.sid_id
				 INNER JOIN xref.controller_doc_group_names AS cdgn
						 ON cdgsl.controller_doc_group_name_id
							= cdgn.controller_doc_group_name_id
				 WHERE cdgsl.controller_doc_group_name_id = @contr_doc_grp_to_check
				   AND sl.sid = @usersid
				   AND (cdgsl.valid_from IS NULL
						OR cdgsl.valid_from <= @now)
				   AND (cdgsl.valid_until IS NULL
						OR cdgsl.valid_until >= @now)
			)
			BEGIN
				SET @group_failure =
					'Controller Document Group: User Link Not Currently Valid';
			END;

		END;


		-- ============================================================================================
		-- COMBINE FAILURE DIAGNOSTICS
		--
		-- The authorisation requires:
		--     Controller Role AND valid Controller Document Group membership
		--
		-- CONCAT_WS allows either or both failure reasons to be recorded.
		-- ============================================================================================

		SET @failure_type =
			CONCAT_WS('; ',
				@controller_failure,
				@group_failure
			);



		-- End authorisation failure diagnostics
--  ============================================================================================

			-- Write to authorisation failure log

					  INSERT INTO user_restr.authorisation_fail_log
								  (sid_id, privilege_type, privilege_id, privilege_name, stored_procedure, failure_type)
						   VALUES (
									@sid_id,
									'Controller Document Group Edit Permission',
									@contr_doc_grp_to_check,
									@privilege_name,
									'[internal].[usp_AUTHENTICATE_cont_doc_grp]',
									@failure_type
									)

		  END
   
END
GO