USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [internal].[usp_AUTHENTICATE_fl_grp_ed_pm]    Script Date: Fri 02-10-2026 8:29:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Silkwood Software
-- Create date: 06-01-2024
/*
 Description: Check if the connected user has edit permission for a given file group.
 Checks if there is any link from the file group through to the SID list for the connected user.
 Output is a user authentication status indicating 'Pass' or 'Fail'.
*/

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

-- =============================================
/**
* Check if the connected user has edit permission for a given file group.
* 
* **Acceptable Inputs:**
*
* - @filegroupid_tocheck bigint  Must be a non-null and non-empty valid identifier.
*
* **Return Values:**
*
* @user_authentication_reslt_fgep  nchar(10).
*
* **Error and Exception Conditions:**
*
* - None.  The procedure relies on the calling procedure to validate the input data
*
* **Side Effects:**
*
* - None
*
* **Preconditions:**
*
* - There must be an existing record with a matching @filegroupid_tocheck.
*
* **Postconditions:**
* - The procedure returns an output value.
*
*/
-- =========================================================
CREATE PROCEDURE [internal].[usp_AUTHENTICATE_fl_grp_ed_pm] 

    @filegroupid_tocheck                     bigint,
    @user_authentication_reslt_fgep  nvarchar(10) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- Declare variables
    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)       = '';
 
   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.file_group_names
						  WHERE file_group_id = @filegroupid_tocheck)
			  
    SET @user_authentication_reslt_fgep = 'Fail';


	-- Check if the  connected user SID is linked through either pathway
    IF EXISTS (
            SELECT fgepfl.file_group_id    -- Check through duty function lists
              FROM user_restr.file_group_edit_perm_funct_lst AS fgepfl
        INNER JOIN people.function_lists AS fl
                ON fgepfl.function_list_id 
				     = fl.function_list_id
        INNER JOIN people.duty_functions AS df
                ON   fl.function_id 
				   = df.function_id
        INNER JOIN people.duty_function_sid_links AS dfsl
                ON     df.function_id 
				   = dfsl.function_id
        INNER JOIN user_restr.sid_list AS sl
                ON dfsl.sid_id 
				   = sl.sid_id
             WHERE fgepfl.file_group_id= @filegroupid_tocheck
		           AND sl.sid = @usersid 
				   -- Check time-bound restrictions
				   AND (fgepfl.valid_from IS NULL OR fgepfl.valid_from <= @now)
				   AND (fgepfl.valid_until IS NULL OR fgepfl.valid_until >= @now)
				   AND (fl.valid_from IS NULL OR fl.valid_from <= @now)
				   AND (fl.valid_until IS NULL OR fl.valid_until >= @now)
				   AND (dfsl.valid_from IS NULL OR dfsl.valid_from <= @now)
				   AND (dfsl.valid_until IS NULL OR dfsl.valid_until >= @now)

        UNION
            SELECT fgeppl.file_group_id  -- Check through people lists
              FROM user_restr.file_group_edit_perm_ppl_lst AS fgeppl
        INNER JOIN people.people_lists AS pl
                ON  fgeppl.people_list_id 
				   =    pl.people_list_id
        INNER JOIN user_restr.sid_list AS sl
                ON   pl.sid_id 
				   = sl.sid_id
             WHERE fgeppl.file_group_id= @filegroupid_tocheck
		           AND sl.sid = @usersid
				   -- Check time-bound restrictions
				   AND (fgeppl.valid_from IS NULL OR fgeppl.valid_from <= @now)
				   AND (fgeppl.valid_until IS NULL OR fgeppl.valid_until >= @now)
				   AND (pl.valid_from IS NULL OR pl.valid_from <= @now)
				   AND (pl.valid_until IS NULL OR pl.valid_until >= @now)	        
    )
        BEGIN   -- The user has permission for this action
            SET @user_authentication_reslt_fgep = 'Pass';
        END
    ELSE
        BEGIN -- Authorisation failed
          SET @user_authentication_reslt_fgep = 'Fail';
 --  ============================================================================================
               -- Authorisation failure diagnostics


                       DECLARE
                           @function_failure nvarchar(1000) = NULL,
                           @people_failure   nvarchar(1000) = NULL;


                       -- ============================================================
                       -- FUNCTION LIST PATH
                       --
                       -- file_group_edit_perm_funct_lst
                       --     AND function_lists
                       --     AND duty_function_sid_links
                       -- ============================================================

                       -- File group -> Function List authorisation exists
                       IF NOT EXISTS
                       (
                           SELECT 1
                             FROM user_restr.file_group_edit_perm_funct_lst AS fgepfl
                            WHERE fgepfl.file_group_id = @filegroupid_tocheck
                       )
                       BEGIN
                           SET @function_failure =
                               CONCAT_WS('; ',
                                   @function_failure,
                                   'Function Path: Authorisation Not Found'
                               );
                       END
                       ELSE
                       BEGIN

                           -- File group -> Function List authorisation is currently valid
                           IF NOT EXISTS
                           (
                               SELECT 1
                                 FROM user_restr.file_group_edit_perm_funct_lst AS fgepfl
                                WHERE fgepfl.file_group_id = @filegroupid_tocheck
                                  AND (fgepfl.valid_from IS NULL
                                       OR fgepfl.valid_from <= @now)
                                  AND (fgepfl.valid_until IS NULL
                                       OR fgepfl.valid_until >= @now)
                           )
                           BEGIN
                               SET @function_failure =
                                   CONCAT_WS('; ',
                                       @function_failure,
                                       'Function Path: Authorisation Not Currently Valid'
                                   );
                           END;


                           -- Function List exists
                           IF NOT EXISTS
                           (
                               SELECT 1
                                 FROM user_restr.file_group_edit_perm_funct_lst AS fgepfl
                           INNER JOIN people.function_lists AS fl
                                   ON fgepfl.function_list_id = fl.function_list_id
                                WHERE fgepfl.file_group_id = @filegroupid_tocheck
                           )
                           BEGIN
                               SET @function_failure =
                                   CONCAT_WS('; ',
                                       @function_failure,
                                       'Function Path: Function List Not Found'
                                   );
                           END
                           ELSE
                           BEGIN

                               -- Function List is currently valid
                               IF NOT EXISTS
                               (
                                   SELECT 1
                                     FROM user_restr.file_group_edit_perm_funct_lst AS fgepfl
                               INNER JOIN people.function_lists AS fl
                                       ON fgepfl.function_list_id = fl.function_list_id
                                    WHERE fgepfl.file_group_id = @filegroupid_tocheck
                                      AND (fl.valid_from IS NULL
                                           OR fl.valid_from <= @now)
                                      AND (fl.valid_until IS NULL
                                           OR fl.valid_until >= @now)
                               )
                               BEGIN
                                   SET @function_failure =
                                       CONCAT_WS('; ',
                                           @function_failure,
                                           'Function Path: Function List Not Currently Valid'
                                       );
                               END;


                               -- User has a SID link through the Function List
                               IF NOT EXISTS
                               (
                                   SELECT 1
                                     FROM user_restr.file_group_edit_perm_funct_lst AS fgepfl
                               INNER JOIN people.function_lists AS fl
                                       ON fgepfl.function_list_id = fl.function_list_id
                               INNER JOIN people.duty_functions AS df
                                       ON fl.function_id = df.function_id
                               INNER JOIN people.duty_function_sid_links AS dfsl
                                       ON df.function_id = dfsl.function_id
                               INNER JOIN user_restr.sid_list AS sl
                                       ON dfsl.sid_id = sl.sid_id
                                    WHERE fgepfl.file_group_id = @filegroupid_tocheck
                                      AND sl.sid = @usersid
                               )
                               BEGIN
                                   SET @function_failure =
                                       CONCAT_WS('; ',
                                           @function_failure,
                                           'Function Path: Duty Function SID Link Not Found'
                                       );
                               END
                               ELSE
                               BEGIN

                                   -- User's SID link is currently valid
                                   IF NOT EXISTS
                                   (
                                       SELECT 1
                                         FROM user_restr.file_group_edit_perm_funct_lst AS fgepfl
                                   INNER JOIN people.function_lists AS fl
                                           ON fgepfl.function_list_id = fl.function_list_id
                                   INNER JOIN people.duty_functions AS df
                                           ON fl.function_id = df.function_id
                                   INNER JOIN people.duty_function_sid_links AS dfsl
                                           ON df.function_id = dfsl.function_id
                                   INNER JOIN user_restr.sid_list AS sl
                                           ON dfsl.sid_id = sl.sid_id
                                        WHERE fgepfl.file_group_id = @filegroupid_tocheck
                                          AND sl.sid = @usersid
                                          AND (fgepfl.valid_from IS NULL
                                               OR fgepfl.valid_from <= @now)
                                          AND (fgepfl.valid_until IS NULL
                                               OR fgepfl.valid_until >= @now)
                                          AND (fl.valid_from IS NULL
                                               OR fl.valid_from <= @now)
                                          AND (fl.valid_until IS NULL
                                               OR fl.valid_until >= @now)
                                          AND (dfsl.valid_from IS NULL
                                               OR dfsl.valid_from <= @now)
                                          AND (dfsl.valid_until IS NULL
                                               OR dfsl.valid_until >= @now)
                                   )
                                   BEGIN
                                       SET @function_failure =
                                           CONCAT_WS('; ',
                                               @function_failure,
                                               'Function Path: Duty Function SID Link Not Currently Valid'
                                           );
                                   END;

                               END;
                           END;
                       END;


                       -- ============================================================
                       -- PEOPLE LIST PATH
                       --
                       -- file_group_edit_perm_ppl_lst
                       --     AND people_lists
                       -- ============================================================

                       -- File group -> People List authorisation exists
                       IF NOT EXISTS
                       (
                           SELECT 1
                             FROM user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                            WHERE fgeppl.file_group_id = @filegroupid_tocheck
                       )
                       BEGIN
                           SET @people_failure =
                               CONCAT_WS('; ',
                                   @people_failure,
                                   'People Path: Authorisation Not Found'
                               );
                       END
                       ELSE
                       BEGIN

                           -- File group -> People List authorisation is currently valid
                           IF NOT EXISTS
                           (
                               SELECT 1
                                 FROM user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                                WHERE fgeppl.file_group_id = @filegroupid_tocheck
                                  AND (fgeppl.valid_from IS NULL
                                       OR fgeppl.valid_from <= @now)
                                  AND (fgeppl.valid_until IS NULL
                                       OR fgeppl.valid_until >= @now)
                           )
                           BEGIN
                               SET @people_failure =
                                   CONCAT_WS('; ',
                                       @people_failure,
                                       'People Path: Authorisation Not Currently Valid'
                                   );
                           END;


                           -- People List exists
                           IF NOT EXISTS
                           (
                               SELECT 1
                                 FROM user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                           INNER JOIN people.people_lists AS pl
                                   ON fgeppl.people_list_id = pl.people_list_id
                                WHERE fgeppl.file_group_id = @filegroupid_tocheck
                           )
                           BEGIN
                               SET @people_failure =
                                   CONCAT_WS('; ',
                                       @people_failure,
                                       'People Path: People List Not Found'
                                   );
                           END
                           ELSE
                           BEGIN

                               -- People List is currently valid
                               IF NOT EXISTS
                               (
                                   SELECT 1
                                     FROM user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                               INNER JOIN people.people_lists AS pl
                                       ON fgeppl.people_list_id = pl.people_list_id
                                    WHERE fgeppl.file_group_id = @filegroupid_tocheck
                                      AND (pl.valid_from IS NULL
                                           OR pl.valid_from <= @now)
                                      AND (pl.valid_until IS NULL
                                           OR pl.valid_until >= @now)
                               )
                               BEGIN
                                   SET @people_failure =
                                       CONCAT_WS('; ',
                                           @people_failure,
                                           'People Path: People List Not Currently Valid'
                                       );
                               END;


                               -- User has a SID membership in the People List
                               IF NOT EXISTS
                               (
                                   SELECT 1
                                     FROM user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                               INNER JOIN people.people_lists AS pl
                                       ON fgeppl.people_list_id = pl.people_list_id
                               INNER JOIN user_restr.sid_list AS sl
                                       ON pl.sid_id = sl.sid_id
                                    WHERE fgeppl.file_group_id = @filegroupid_tocheck
                                      AND sl.sid = @usersid
                               )
                               BEGIN
                                   SET @people_failure =
                                       CONCAT_WS('; ',
                                           @people_failure,
                                           'People Path: People List Membership Not Found'
                                       );
                               END
                               ELSE
                               BEGIN

                                   -- User's People List membership is currently valid
                                   IF NOT EXISTS
                                   (
                                       SELECT 1
                                         FROM user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                                   INNER JOIN people.people_lists AS pl
                                           ON fgeppl.people_list_id = pl.people_list_id
                                   INNER JOIN user_restr.sid_list AS sl
                                           ON pl.sid_id = sl.sid_id
                                        WHERE fgeppl.file_group_id = @filegroupid_tocheck
                                          AND sl.sid = @usersid
                                          AND (fgeppl.valid_from IS NULL
                                               OR fgeppl.valid_from <= @now)
                                          AND (fgeppl.valid_until IS NULL
                                               OR fgeppl.valid_until >= @now)
                                          AND (pl.valid_from IS NULL
                                               OR pl.valid_from <= @now)
                                          AND (pl.valid_until IS NULL
                                               OR pl.valid_until >= @now)
                                   )
                                   BEGIN
                                       SET @people_failure =
                                           CONCAT_WS('; ',
                                               @people_failure,
                                               'People Path: People List Membership Not Currently Valid'
                                           );
                                   END;

                               END;
                           END;
                       END;


                       -- ============================================================
                       -- COMBINE FUNCTION AND PEOPLE PATH DIAGNOSTICS
                       -- ============================================================

                       SET @failure_type =
                           CONCAT_WS('; ',
                               @function_failure,
                               @people_failure
                           );


               --End authorisation failure diagnostics
--  ============================================================================================
           -- 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,
					'File Group Edit Permission',
					@filegroupid_tocheck,
					@privilege_name,
					'[internal].[usp_AUTHENTICATE_fl_grp_ed_pm]',
					@failure_type
					)

        END -- END Authorisation failed



    -- The result is returned via the OUTPUT parameter
END
GO