USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [internal].[usp_AUTHENTICATE_user_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: 03-08-2023
-- Description:	Checks whether the connected user has permission
-- to view a given document group.    
-- Also returns fail if the id does not exist.
-- Input is a the id of the form to be checked.  
-- Output is a status string of 'Pass' or 'Fail'.
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023
*/

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

	@docgroupid_to_check_vp bigint,
	@user_authentication_result nchar(10) 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
		@sid_id bigint                     = NULL,
		@failure_type nvarchar(1000)       = NULL,
		@now datetime2(7)                  = SYSDATETIME(),
		@privilege_name nvarchar(50)       = '';


 -- Check if the user does not have permission
  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;
	

  -- User has permission for the document group if either the user is linked to
  -- the document group via doc_group_view_permissions or the document group exists but is not
  -- linked to any users via doc_group_view_permissions.
      
  IF NOT 
		 (EXISTS (SELECT dgvp.sid_id AS dgvpsid  -- Check if the user is linked to the document group
				   FROM user_restr.doc_group_view_permissions AS dgvp
			 INNER JOIN user_restr.sid_list AS sl
						ON dgvp.sid_id
						   = sl.sid_id
				  WHERE dgvp.doc_group_id = @docgroupid_to_check_vp
						 AND sl.sid = @usersid
			      AND (dgvp.valid_from IS NULL OR dgvp.valid_from <= @now)
				  AND (dgvp.valid_until IS NULL OR dgvp.valid_until >= @now)
						 ) 
					 
		  -- Check if the document group is not linked to any user
		  OR NOT EXISTS (SELECT dgvp.sid_id AS dgvpsid  
						   FROM user_restr.doc_group_view_permissions AS dgvp
				  WHERE dgvp.doc_group_id = @docgroupid_to_check_vp))
							 
	  -- Fail if the id does not exist		  
	  OR NOT EXISTS (SELECT dgn.doc_group_id AS dgi
	                   FROM xref.doc_group_names AS dgn
					WHERE dgn.doc_group_id = @docgroupid_to_check_vp)
									     

		BEGIN  -- The user does not have permission for this action
		  SET @user_authentication_result = 'Fail';
--  ============================================================================================
                   -- Authorisation failure diagnostics


          DECLARE
              @existence_failure  nvarchar(1000) = NULL,
              @permission_failure nvarchar(1000) = NULL;


          -- ============================================================================================
          -- DOCUMENT GROUP EXISTENCE
          -- ============================================================================================

          IF NOT EXISTS
          (
              SELECT 1
                FROM xref.doc_group_names AS dgn
               WHERE dgn.doc_group_id = @docgroupid_to_check_vp
          )
          BEGIN
              SET @existence_failure =
                  'Document Group: Group Does Not Exist';
          END;


          -- ============================================================================================
          -- VIEW PERMISSION
          --
          -- The authorisation logic is:
          --
          --   Pass if the user is explicitly permitted with a currently valid link
          --   OR
          --   Pass if the document group has no view permissions defined at all
          --
          -- Therefore, failure can only occur when:
          --
          --   1. The document group exists
          --   AND
          --   2. View permissions ARE defined for the group
          --   AND
          --   3. The connected user is NOT among them (or their link has expired)
          --
          -- ============================================================================================

          IF @existence_failure IS NULL  -- Only diagnose permissions if the group exists
          BEGIN

              -- Check whether any view permissions are defined for this group
              IF EXISTS
              (
                  SELECT 1
                    FROM user_restr.doc_group_view_permissions AS dgvp
                   WHERE dgvp.doc_group_id = @docgroupid_to_check_vp
              )
              BEGIN

                  -- View permissions exist. Check whether the user has any link
                  -- (irrespective of validity dates).
                  IF NOT EXISTS
                  (
                      SELECT 1
                        FROM user_restr.doc_group_view_permissions AS dgvp
                       INNER JOIN user_restr.sid_list AS sl
                               ON dgvp.sid_id = sl.sid_id
                       WHERE dgvp.doc_group_id = @docgroupid_to_check_vp
                         AND sl.sid = @usersid
                  )
                  BEGIN
                      SET @permission_failure =
                          'View Permission: User Not Linked to Document Group';
                  END
                  ELSE
                  BEGIN

                      -- The user is linked, but check whether the link
                      -- is currently valid.
                      IF NOT EXISTS
                      (
                          SELECT 1
                            FROM user_restr.doc_group_view_permissions AS dgvp
                           INNER JOIN user_restr.sid_list AS sl
                                   ON dgvp.sid_id = sl.sid_id
                           WHERE dgvp.doc_group_id = @docgroupid_to_check_vp
                             AND sl.sid = @usersid
                             AND (dgvp.valid_from IS NULL OR dgvp.valid_from <= @now)
                             AND (dgvp.valid_until IS NULL OR dgvp.valid_until >= @now)
                      )
                      BEGIN
                          SET @permission_failure =
                              'View Permission: User Link Not Currently Valid';
                      END;

                  END;

              END;

          END;


          -- ============================================================================================
          -- COMBINE FAILURE DIAGNOSTICS
          -- ============================================================================================

          SET @failure_type =
              CONCAT_WS('; ',
                  @existence_failure,
                  @permission_failure
              );
--  ============================================================================================

           -- Write to the log
          		INSERT INTO user_restr.authorisation_fail_log
					(sid_id, privilege_type, privilege_id, privilege_name, stored_procedure, failure_type)
			VALUES (
					@sid_id,
					'View Document Group',
					@docgroupid_to_check_vp,
					@privilege_name,
					'[internal].[usp_AUTHENTICATE_user_doc_grp]',
					@failure_type
					)

		END -- End authorisation failure diagnostics


	ELSE
	  SET @user_authentication_result = 'Pass';


END
GO