USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [internal].[usp_AUTHENTICATE_file_ed_free]    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: 27-12-2023
/*
 Description: Check if a file is free from edit restrictions for the connected user
 This is not the same as checking if the user has edit permission for the file.
 By default a user does not have edit permission for a file unless it is explicitly granted
 This check establishes if either the file is free from any edit permission restrictions
 or the user already has edit permission.  The procedure is used to check if it is ok 
 for a user to grant theselves edit permission for the given file. 
 Otherwise permission must be granted by a controler
 If the connected user SID exists in any pathway then the check passes
 If there are no SIDs for any user linked in any pathway the the check passes
 In all other cases the check fails. 
 Input is the file ID to check.
 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
*/

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

    @fileid_tocheck                         bigint,
    @user_authentication_reslt_fgef  nvarchar(10) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- Declare variables
    DECLARE
	    @usersid  varbinary(100)           = NULL, -- The SID of the connected user
		@fileiscontrollergroup nvarchar(5) = NULL,
        @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;

    -- Default user authentication result to Fail
    SET @user_authentication_reslt_fgef = 'Fail';

	-- Fail if the file is linked to any controller edit group.
	-- Otherwise:
	-- Pass if the file is linked to a SID that includes the connected user.
	-- Pass if the file is not linked to any SIDs
	-- Fail otherwise

	  IF EXISTS -- Is the file id linked to a controller group?
	    (SELECT cfgl.file_id 
		   FROM xref.controller_file_group_links AS cfgl
		  WHERE cfgl.file_id = @fileid_tocheck)
		BEGIN
		 SET @fileiscontrollergroup = 'Yes';
		 SET @user_authentication_reslt_fgef = 'Fail';
	    END
	  ELSE  SET @fileiscontrollergroup = 'No';


 IF @fileiscontrollergroup = 'No'  -- File listed in a controller group overrides other permissions
   BEGIN
	-- Check if the  connected user SID is linked through either pathway
    IF EXISTS (
            SELECT fgl.file_group_id    -- Check through duty function lists
              FROM xref.file_group_links AS fgl
        INNER JOIN user_restr.file_group_edit_perm_funct_lst AS fgepfl
                ON      fgl.file_group_id 
			       = fgepfl.file_group_id
        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 fgl.file_id = @fileid_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 fgl.file_group_id  -- Check through people lists
              FROM xref.file_group_links AS fgl
        INNER JOIN user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                ON      fgl.file_group_id 
				   = fgeppl.file_group_id
        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 fgl.file_id = @fileid_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
        -- If the file is linked to SIDs that include the connected user, set result to Pass
        SET @user_authentication_reslt_fgef = 'Pass';
    END

	-- Check if the file is not linked to any SID
    IF  @user_authentication_reslt_fgef = 'Fail' -- The connected user SID has not been already found to be linked
	   AND NOT EXISTS (
            SELECT fgl.file_group_id    -- Check through duty function lists
              FROM xref.file_group_links AS fgl
        INNER JOIN user_restr.file_group_edit_perm_funct_lst AS fgepfl
                ON      fgl.file_group_id 
			       = fgepfl.file_group_id
        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 fgl.file_id = @fileid_tocheck

        UNION
            SELECT fgl.file_group_id  -- Check through people lists
              FROM xref.file_group_links AS fgl
        INNER JOIN user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                ON      fgl.file_group_id 
				   = fgeppl.file_group_id
        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 fgl.file_id = @fileid_tocheck
    )
    BEGIN
        -- If the file is not linked to any users, set result to Pass
        SET @user_authentication_reslt_fgef = 'Pass';
    END

 END -- END IF @fileiscontrollergroup = 'No'  -- File listed in a controller group overrides other permissions
    -- The result is returned via the OUTPUT parameter



    IF @user_authentication_reslt_fgef = 'Fail'
      BEGIN
--  ============================================================================================
                   -- Authorisation failure diagnostics
                   -- ============================================================================================
                   --
                   -- This procedure checks whether a file is free from edit restrictions.
                   -- Failure occurs when:
                   --   1. The file is in a controller group (overrides all other permissions), OR
                   --   2. The file has edit restrictions (users ARE linked) but the connected
                   --      user is not among them, or their permissions have expired.
                   --
                   -- The "no users linked" case is a Pass, so it never reaches diagnostics.
                   -- ============================================================================================

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


                   -- ============================================================================================
                   -- CONTROLLER GROUP CHECK
                   -- ============================================================================================

                   IF @fileiscontrollergroup = 'Yes'
                   BEGIN
                       SET @controller_failure =
                           'File is linked to a Controller File Group';
                   END;


                   -- ============================================================================================
                   -- FUNCTION LIST PATH
                   --
                   -- File
                   --   -> File Group
                   --   -> Function List Edit Authorisation (fgepfl)
                   --   -> Function List (fl)
                   --   -> Duty Function (df)
                   --   -> User SID (dfsl -> sl)
                   --
                   -- Time-bound restrictions:
                   --   fgepfl.valid_from / valid_until
                   --   fl.valid_from / valid_until
                   --   dfsl.valid_from / valid_until
                   -- ============================================================================================

                   IF @fileiscontrollergroup = 'No'
                   BEGIN

                       -- Check that the file is linked to a file group
                       IF NOT EXISTS
                       (
                           SELECT 1
                             FROM xref.file_group_links AS fgl
                            WHERE fgl.file_id = @fileid_tocheck
                       )
                       BEGIN
                           SET @function_failure =
                               CONCAT_WS('; ',
                                   @function_failure,
                                   'Function Path: File Group Link Not Found'
                               );
                       END
                       ELSE
                       BEGIN

                           -- Check for a Function List edit authorisation
                           IF NOT EXISTS
                           (
                               SELECT 1
                                 FROM xref.file_group_links AS fgl
                           INNER JOIN user_restr.file_group_edit_perm_funct_lst AS fgepfl
                                   ON fgl.file_group_id = fgepfl.file_group_id
                                WHERE fgl.file_id = @fileid_tocheck
                           )
                           BEGIN
                               SET @function_failure =
                                   CONCAT_WS('; ',
                                       @function_failure,
                                       'Function Path: Edit Authorisation Not Found'
                                   );
                           END
                           ELSE
                           BEGIN

                               -- Check validity of the file-group / function-list authorisation
                               IF NOT EXISTS
                               (
                                   SELECT 1
                                     FROM xref.file_group_links AS fgl
                               INNER JOIN user_restr.file_group_edit_perm_funct_lst AS fgepfl
                                       ON fgl.file_group_id = fgepfl.file_group_id
                                    WHERE fgl.file_id = @fileid_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: Edit Authorisation Not Currently Valid'
                                       );
                               END;


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

                                   -- Check Function List validity
                                   IF NOT EXISTS
                                   (
                                       SELECT 1
                                         FROM xref.file_group_links AS fgl
                                   INNER JOIN user_restr.file_group_edit_perm_funct_lst AS fgepfl
                                           ON fgl.file_group_id = fgepfl.file_group_id
                                   INNER JOIN people.function_lists AS fl
                                           ON fgepfl.function_list_id = fl.function_list_id
                                        WHERE fgl.file_id = @fileid_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;


                                   -- Check whether the Function List ultimately provides
                                   -- a SID link for this user
                                   IF NOT EXISTS
                                   (
                                       SELECT 1
                                         FROM xref.file_group_links AS fgl
                                   INNER JOIN user_restr.file_group_edit_perm_funct_lst AS fgepfl
                                           ON fgl.file_group_id = fgepfl.file_group_id
                                   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 fgl.file_id = @fileid_tocheck
                                          AND sl.sid = @usersid
                                   )
                                   BEGIN
                                       SET @function_failure =
                                           CONCAT_WS('; ',
                                               @function_failure,
                                               'Function Path: User Not Linked Through Duty Function'
                                           );
                                   END
                                   ELSE
                                   BEGIN

                                       -- Check validity of the complete function-to-user path
                                       IF NOT EXISTS
                                       (
                                           SELECT 1
                                             FROM xref.file_group_links AS fgl
                                       INNER JOIN user_restr.file_group_edit_perm_funct_lst AS fgepfl
                                               ON fgl.file_group_id = fgepfl.file_group_id
                                       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 fgl.file_id = @fileid_tocheck
                                              AND sl.sid = @usersid

                                              -- File Group / Function List authorisation
                                              AND (fgepfl.valid_from IS NULL OR fgepfl.valid_from <= @now)
                                              AND (fgepfl.valid_until IS NULL OR fgepfl.valid_until >= @now)

                                              -- Function List
                                              AND (fl.valid_from IS NULL OR fl.valid_from <= @now)
                                              AND (fl.valid_until IS NULL OR fl.valid_until >= @now)

                                              -- Duty Function / User link
                                              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: One or More Authorisation Links Not Currently Valid'
                                               );
                                       END;

                                   END;
                               END;
                           END;
                       END;


                       -- ============================================================================================
                       -- PEOPLE LIST PATH
                       --
                       -- File
                       --   -> File Group
                       --   -> People List Edit Authorisation (fgeppl)
                       --   -> People List (pl)
                       --   -> User SID (sl)
                       --
                       -- Time-bound restrictions:
                       --   fgeppl.valid_from / valid_until
                       --   pl.valid_from / valid_until
                       -- ============================================================================================

                       -- Check that the file is linked to a file group
                       IF NOT EXISTS
                       (
                           SELECT 1
                             FROM xref.file_group_links AS fgl
                            WHERE fgl.file_id = @fileid_tocheck
                       )
                       BEGIN
                           SET @people_failure =
                               CONCAT_WS('; ',
                                   @people_failure,
                                   'People Path: File Group Link Not Found'
                               );
                       END
                       ELSE
                       BEGIN

                           -- Check for a People List edit authorisation
                           IF NOT EXISTS
                           (
                               SELECT 1
                                 FROM xref.file_group_links AS fgl
                           INNER JOIN user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                                   ON fgl.file_group_id = fgeppl.file_group_id
                                WHERE fgl.file_id = @fileid_tocheck
                           )
                           BEGIN
                               SET @people_failure =
                                   CONCAT_WS('; ',
                                       @people_failure,
                                       'People Path: Edit Authorisation Not Found'
                                   );
                           END
                           ELSE
                           BEGIN

                               -- Check validity of the file-group / people-list authorisation
                               IF NOT EXISTS
                               (
                                   SELECT 1
                                     FROM xref.file_group_links AS fgl
                               INNER JOIN user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                                       ON fgl.file_group_id = fgeppl.file_group_id
                                    WHERE fgl.file_id = @fileid_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: Edit Authorisation Not Currently Valid'
                                       );
                               END;


                               -- Check that the People List exists and contains the user's SID
                               IF NOT EXISTS
                               (
                                   SELECT 1
                                     FROM xref.file_group_links AS fgl
                               INNER JOIN user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                                       ON fgl.file_group_id = fgeppl.file_group_id
                               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 fgl.file_id = @fileid_tocheck
                                      AND sl.sid = @usersid
                               )
                               BEGIN

                                   -- Distinguish a missing People List from a People List
                                   -- which exists but does not contain the user
                                   IF NOT EXISTS
                                   (
                                       SELECT 1
                                         FROM xref.file_group_links AS fgl
                                   INNER JOIN user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                                           ON fgl.file_group_id = fgeppl.file_group_id
                                   INNER JOIN people.people_lists AS pl
                                           ON fgeppl.people_list_id = pl.people_list_id
                                        WHERE fgl.file_id = @fileid_tocheck
                                   )
                                   BEGIN
                                       SET @people_failure =
                                           CONCAT_WS('; ',
                                               @people_failure,
                                               'People Path: People List Not Found'
                                           );
                                   END
                                   ELSE
                                   BEGIN
                                       SET @people_failure =
                                           CONCAT_WS('; ',
                                               @people_failure,
                                               'People Path: User Not a Member of People List'
                                           );
                                   END;

                               END
                               ELSE
                               BEGIN

                                   -- Check validity of the complete people-list-to-user path
                                   IF NOT EXISTS
                                   (
                                       SELECT 1
                                         FROM xref.file_group_links AS fgl
                                   INNER JOIN user_restr.file_group_edit_perm_ppl_lst AS fgeppl
                                           ON fgl.file_group_id = fgeppl.file_group_id
                                   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 fgl.file_id = @fileid_tocheck
                                          AND sl.sid = @usersid

                                          -- File Group / People List authorisation
                                          AND (fgeppl.valid_from IS NULL OR fgeppl.valid_from <= @now)
                                          AND (fgeppl.valid_until IS NULL OR fgeppl.valid_until >= @now)

                                          -- People List
                                          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: One or More Authorisation Links Not Currently Valid'
                                           );
                                   END;

                               END;
                           END;
                       END;

                   END; -- END IF @fileiscontrollergroup = 'No' diagnostics


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

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


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

				   -- Write to authorisation fail log

					  INSERT INTO user_restr.authorisation_fail_log
								  (sid_id, privilege_type, privilege_id, privilege_name, stored_procedure, failure_type)
						   VALUES (
									@sid_id,
									'File Edit Free Check',
									@fileid_tocheck,
									@privilege_name,
									'[internal].[usp_AUTHENTICATE_file_ed_free]',
									@failure_type
									)
    END -- END IF @user_authentication_reslt_fgef = 'Fail'

END
GO