USE plib_namecheck;
GO

SET NOCOUNT ON;
SET XACT_ABORT ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

IF DB_NAME() <> 'plib_namecheck'
BEGIN
    RAISERROR('Wrong database.', 16, 10);
    RETURN;
END;
GO

IF NOT EXISTS (
       SELECT *
       FROM   sys.objects
       WHERE  [object_id] = OBJECT_ID(N'[dbo].[MatchName4_payments_I4C]')
              AND [type] IN (N'P', N'PC')
   )
BEGIN
    EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[MatchName4_payments_I4C] AS';
END;
GO

ALTER PROCEDURE [dbo].[MatchName4_payments_I4C](
    @threshold     FLOAT,
    @idName        VARCHAR(50),
    @DOB           VARCHAR(256),
    @params        dbo.[MN4_PARAMS_t] READONLY,
    @debug         INT = 0
)
AS
	SET CONCAT_NULL_YIELDS_NULL OFF;
	SET ANSI_NULL_DFLT_ON ON;
	
	DECLARE @NC_whitelist_check_IBAN BIT = 0;
	
	SELECT @NC_whitelist_check_IBAN = val_str
	FROM   @params
	WHERE  NAME = 'NC_whitelist_check_IBAN';
	
	DECLARE @NC_ADV_COMPARE_FOR_EXACT_SUBSTR INT;
	
	SELECT @NC_ADV_COMPARE_FOR_EXACT_SUBSTR = val_str
	FROM   @params
	WHERE  NAME = 'NC_ADV_COMPARE_FOR_EXACT_SUBSTR';
	
	DECLARE @PaymentSystem VARCHAR(255);
	DECLARE @ProcessCode INT;
	DECLARE @CREDITOR_LASTNAME VARCHAR(500);
	DECLARE @DEBITOR_LASTNAME VARCHAR(500);
	-- Flags
	DECLARE @IsInstPayment BIT = 0;
	DECLARE @IsOutgoing BIT = 0;
	DECLARE @IsIncoming BIT = 0;
	
	--add [MANDANT] field ([varchar](40) NULL) from staging_namecheck.dbo.namecheck
	DECLARE @ORG_CLIENT VARCHAR(40);
	DECLARE @ProfileName VARCHAR(255);

	SELECT @ORG_CLIENT = n.MANDANT,
	       @PaymentSystem         = n.F_PAYMENT_SYSTEM,
	       @ProcessCode           = n.F_PROCESS_CODE,
	       @CREDITOR_LASTNAME     = n.CREDITOR_LASTNAME,
	       @DEBITOR_LASTNAME      = n.DEBITOR_LASTNAME,
		   @ProfileName = staging_namecheck.dbo.gsf_Get_ClientName_ProfileName(MANDANT)
	FROM   staging_namecheck.dbo.namecheck n
	WHERE  n.BUSINESSCASEID = @idName;
	
	-- Check if payment system is instant (exists in CCR.dbo.InstantPaymentSystemTypes)
	SET @IsInstPayment =
		CASE 
			WHEN EXISTS (
				SELECT 1
				FROM CCR.dbo.InstantPaymentSystemTypes t
				WHERE t.PaymentSystem = @PaymentSystem
			)
			THEN 1
			ELSE 0
		END;
		
	-- If it's 'INST', determine outgoing/incoming
	IF @IsInstPayment = 1
	BEGIN
	    -- For outgoing (F_PROCESS_CODE=1)
	    SET @IsOutgoing = CASE 
	                           WHEN @ProcessCode = 1 THEN 1
	                           ELSE 0
	                      END;
	    -- For incoming (F_PROCESS_CODE=2)
	    SET @IsIncoming = CASE 
	                           WHEN @ProcessCode = 2 THEN 1
	                           ELSE 0
	                      END;
	END;
	
	--22012021 
	DECLARE @WHITELIST_SUBSTRING_BASED_LOGIC_FLAG INT = 0;
	
	SELECT @WHITELIST_SUBSTRING_BASED_LOGIC_FLAG = [VALUE]
	FROM   OrgClientParams
	WHERE  ProfileName     = 'SYSTEM'
	       AND NAME        = 'WHITELIST_SUBSTRING_BASED_LOGIC'; 
	
	--NC_I4C_sublists_filtering_Payments
	DECLARE @NC_I4C_sublists_filtering_Payments BIT = 0;
	
	SELECT @NC_I4C_sublists_filtering_Payments = ISNULL(val_str, 0)
	FROM   @params
	WHERE  NAME = 'NC_I4C_sublists_filtering_Payments';

	CREATE TABLE #Results
	(
		R                FLOAT NOT NULL,
		SIM_CODE         FLOAT NOT NULL,
		LIST_ID          BIGINT NOT NULL,
		C_DOB            VARCHAR(256),
		C_AGE            INT,
		C_UPDATED        VARCHAR(20),
		c_isOnlyYear     INT,
		c_fromDate       DATETIME,
		c_endDate        DATETIME,
		c_diffDate       AS DATEDIFF(DAY, c_fromDate, c_endDate),
		diffInDays       INT,
		diffInYears      INT,
		C_Type_of_list     VARCHAR(500) COLLATE DATABASE_DEFAULT, 
		SNC_LastName       VARCHAR(255) COLLATE DATABASE_DEFAULT, 
		LIST_CATEGORY	 VARCHAR(50) COLLATE DATABASE_DEFAULT NULL,
		List		     VARCHAR(500) COLLATE DATABASE_DEFAULT NULL
	);
	SET @threshold = @threshold -0.05;
	
	INSERT INTO #Results
	  (
	    SIM_CODE,
	    R,
	    LIST_ID,
	    C_DOB,
	    C_AGE,
	    C_UPDATED,
	    C_Type_of_list,
	    SNC_LastName,
		LIST_CATEGORY,
		List
	  )(
	       SELECT 0  AS sim_code,
	              SCORE,
	              LIST_ID,
	              C_DOB,
	              C_AGE,
	              C_UPDATED,
	              C_Type_of_list,
	              SNC_LastName,
				  LIST_CATEGORY,
				  List
	       FROM   (
	                  SELECT TB.RID       AS LIST_ID,
	                         TB.SCORE     AS SCORE,
	                         C.DOB        AS C_DOB,
	                         C.AGE        AS C_AGE,
	                         C.UPDATED    AS C_UPDATED,
	                         C.Country    AS C_COUNTRIES,
	                         C.Type_of_list AS C_Type_of_list,
	                         TB.LastName  AS SNC_LastName,
	                         staging_namecheck.dbo.gsf_GetListType(
	                             C.List,
	                             C.ID,
	                             C.Type_of_list,
	                             C.SUB_CATEGORY,
	                             C.KEYWORDS,
	                             C.type_of_list_internal_category
	                         )             LIST_CATEGORY,
							 C.List
	                  FROM   (
	                             SELECT sr.Score,
	                                    sr.RID,
	                                    sr.LastName
	                             FROM   (
	                                        SELECT TOP 400 s.Score,
	                                               s.RID,
	                                               s.FirstName,
	                                               s.LastName
	                                        FROM   plib_namecheck.dbo.SingleNamecheckResults s
	                                        WHERE  s.BID = @idName
	                                        ORDER BY
	                                               s.Score DESC
	                                    ) sr 
	                                    --- condtional on WHITELIST_SUBSTRING_BASED_LOGIC
	                                    
	                                    LEFT JOIN plib_namecheck.dbo.whitelist w
	                                         ON  (@WHITELIST_SUBSTRING_BASED_LOGIC_FLAG = 1)
	                                         AND (
	                                                 sr.rid = w.list_id
	                                                 AND w.name = LTRIM(
	                                                         RTRIM(
	                                                             COALESCE(sr.FirstName, '') + ' ' + COALESCE(sr.LastName, '')
	                                                         )
	                                                     )
	                                                 AND LEN(
	                                                         LTRIM(
	                                                             RTRIM(
	                                                                 COALESCE(sr.FirstName, '') + ' ' + COALESCE(sr.LastName, '')
	                                                             )
	                                                         )
	                                                     ) > 1
	                                                 AND (@DOB IS NULL OR LEN(@DOB) < 4)
	                                             )
	                             WHERE  w.id IS NULL
	                         ) TB
	                         JOIN SANCTION_LISTS_VIEW C
	                              ON  C.ID = TB.RID
	                              AND (
	                                      (C.OrgClient IS NOT NULL AND C.OrgClient = @ORG_CLIENT)
	                                      OR C.OrgClient IS NULL
	                                  )
	              )     RES
	   );
	
	/* =========================================================================
	INFO4C SUB-LIST FILTERING
	-------------------------------------------------------------------------
		When profile-level option = 1, return
		only those INFO4C matches whose (List, Type_of_list) combination is
		explicitly authorised for the current ProfileName in table
		dbo.Sublists_I4C_filter.
	========================================================================= */

	IF @NC_I4C_sublists_filtering_Payments = 1
	BEGIN
		DELETE R
		FROM   #Results AS R
		/* Build the “allowed” list inline and LEFT-JOIN to it          */
		LEFT  JOIN (
					SELECT DISTINCT
							ISNULL(List, '')        AS List,
							ISNULL(Type_of_list, '') AS Type_of_list
					FROM   dbo.Sublists_I4C_filter
					WHERE  ISNULL(ProfileName, '') = ISNULL(@ProfileName, '')
					) AS A
				ON  ISNULL(R.List,        '') = A.List
				AND ISNULL(R.C_Type_of_list, '') = A.Type_of_list
		/* Remove everything except internal hits that is not allowed   */
		WHERE  R.LIST_CATEGORY <> 'INTERNAL'
			AND  A.List IS NULL;      -- no authorised match found
	END;

	-- This block applies two deletes for INST payments:
	-- 1) The first DELETE removes rows from #Results where C_Type_of_list = 'European Union Lists'
	--    and the LastName does not match Debitor (outgoing) or Creditor (incoming).
	--    Rows with any other type_of_list are not affected.
	-- 2) The second DELETE removes rows that match a WhiteList entry
	--    if the F_CUSTOMER_ID (from namecheck) equals PartnerID in WhiteList
	--    and the WhiteList record is associated with the same orgClient.
	IF @IsInstPayment = 1
	BEGIN
	    DELETE R
	    FROM   #Results AS R
	    WHERE  (
	               @IsOutgoing = 1
	               AND R.C_Type_of_list = 'European Union Lists'
	               AND ISNULL(R.SNC_LastName, 'X') <> ISNULL(@DEBITOR_LASTNAME, 'Y')
	           )
	           OR  (
	                   @IsIncoming = 1
	                   AND R.C_Type_of_list = 'European Union Lists'
	                   AND ISNULL(R.SNC_LastName, 'X') <> ISNULL(@CREDITOR_LASTNAME, 'Y')
	               );
	    
	    DELETE R
	    FROM   #Results AS R
	           JOIN staging_namecheck.dbo.namecheck AS N
	                ON  N.BUSINESSCASEID = @idName
	           JOIN plib_namecheck.dbo.WhiteList AS W
	                ON  R.LIST_ID = W.LIST_ID
	                AND N.F_CUSTOMER_ID = W.PartnerID
	    WHERE  w.ProcessingID IS NOT NULL
	           AND W.orgClient = @ORG_CLIENT;
	END;	
	
	IF @debug = 1
	BEGIN
	    SELECT @PaymentSystem [@PaymentSystem],
	           @ProcessCode [@ProcessCode],
	           @CREDITOR_LASTNAME [@CREDITOR_LASTNAME],
	           @DEBITOR_LASTNAME [@DEBITOR_LASTNAME],
	           @IsOutgoing [@IsOutgoing],
	           @IsIncoming [@IsIncoming],
	           @IsInstPayment [@IsInstPayment];
	END;
		
	IF @NC_ADV_COMPARE_FOR_EXACT_SUBSTR = 1
	BEGIN
	    UPDATE #Results
	    SET    SIM_CODE                = SIM_CODE +
	           CASE 
	                WHEN score > 0.96 THEN 0.05
	                WHEN score > 0.94 THEN 0.25
	                WHEN score > 0.92 THEN 0.5
	                WHEN score > 0.88 THEN 1
	                ELSE 1.35
	           END * 0.6 *
	           dbo.CompareForExactMatches_WorldCheck(
	               tb.lastName,
	               c.full_name,
	               c.first_name,
	               c.last_name,
	               c.alias,
	               1
	           )
	    FROM   #Results r
	           JOIN (
	                    SELECT *
	                    FROM   SingleNamecheckResults sr
	                    WHERE  BID     = @idName
	                ) TB
	                ON  r.LIST_ID = tb.RID
	           JOIN SANCTION_LISTS_VIEW C
	                ON  C.ID = TB.RID;
	END;
	ELSE
	BEGIN
	    UPDATE #Results
	    SET    SIM_CODE                = SIM_CODE +
	           CASE 
	                WHEN score > 0.96 THEN 0.05
	                WHEN score > 0.94 THEN 0.25
	                WHEN score > 0.92 THEN 0.5
	                WHEN score > 0.88 THEN 1
	                ELSE 1.35
	           END * 0.6 *
	           dbo.CompareForExactMatches_I4C(
	               tb.lastName,
	               c.full_name,
	               c.first_name,
	               c.last_name,
	               c.alias
	           )
	    FROM   #Results r
	           JOIN (
	                    SELECT *
	                    FROM   SingleNamecheckResults sr
	                    WHERE  BID     = @idName
	                ) TB
	                ON  r.LIST_ID = tb.RID
	           JOIN SANCTION_LISTS_VIEW C
	                ON  C.ID = TB.RID;
	END;
	
	SET @threshold = @threshold + 0.05;
	
	EXEC UpdateResultsTableData_MN4 @threshold,
	     @DOB,
	     @params;
	
	SET NOCOUNT OFF;
	
	IF @debug = 1
	   OR @debug = 2
	BEGIN
	    DECLARE @r_count INT;
	    
	    SELECT @r_count = COUNT(*)
	    FROM   #Results RES
	           JOIN SANCTION_LISTS_VIEW SL
	                ON  SL.ID = RES.LIST_ID
	           LEFT JOIN staging_namecheck.dbo.namecheck n
	                ON  n.businesscaseid = @idName
	           LEFT JOIN (
	                    SELECT DISTINCT w.list_id,
	                           nw.F_CUSTOMER_ID
	                    FROM   whitelist w
	                           JOIN staging_namecheck.dbo.namecheck nw
	                                ON  w.PartnerID = nw.businesscaseid
	                ) w
	                ON  res.list_id = w.LIST_ID
	                AND n.F_CUSTOMER_ID = w.F_CUSTOMER_ID
	    WHERE  RES.R >= @threshold
	           AND w.LIST_ID IS NULL;
	    
	    
	    IF @r_count > 0
	    BEGIN
	        INSERT INTO debug_MN4_results
	        SELECT r.R,
	               r.FIRSTNAME,
	               r.LASTNAME,
	               r.ADD_NAME1,
	               r.ADD_NAME2,
	               r.Alias_,
	               r.Additional_Information_,
	               r.Case_,
	               r.ID,
	               @idName     bid,
	               NULL        TYPE,
	               'MatchName4_payments_I4C' ProcName
	        FROM   (
	                   SELECT RES.R AS R,
	                          SL.FIRST_NAME AS FIRSTNAME,
	                          (SL.LAST_NAME + ' ' + SL.FULL_NAME) AS LASTNAME,
	                          CAST(SL.Alias AS VARCHAR(4096)) AS ADD_NAME1,
	                          CAST(SL.Additional_Information AS VARCHAR(4096)) AS ADD_NAME2,
	                          CAST(SL.Alias AS VARCHAR(4096)) AS Alias_,
	                          CAST(SL.Additional_Information AS VARCHAR(4096)) AS Additional_Information_,
	                          CAST(SL.[Case] AS VARCHAR(4096)) AS Case_,
	                          SL.[ID],
	                          SL.[Title],
	                          SL.[Full_Name],
	                          SL.[Last_Name],
	                          SL.[First_Name],
	                          SL.[Alias],
	                          SL.[Companies],
	                          SL.[Type_of_list],
	                          SL.[List],
	                          SL.[Publication_date],
	                          SL.[Published_by],
	                          SL.[Editor],
	                          SL.[Whitelist],
	                          SL.[Alternative_Script],
	                          SL.[Function_],
	                          SL.[Category],
	                          SL.[AGE],
	                          SL.[DOB],
	                          SL.[POB],
	                          SL.[Country],
	                          SL.[Code],
	                          SL.[Country_Of_Origin],
	                          SL.[Country_Of_Activity],
	                          SL.[Address],
	                          SL.[Passport_Number],
	                          SL.[Date_of_Information],
	                          SL.[Authority],
	                          SL.[AGE_DATE],
	                          SL.[UPDATED]
	                   FROM   #Results RES
	                          JOIN SANCTION_LISTS_VIEW SL
	                               ON  SL.ID = RES.LIST_ID
	                          LEFT JOIN staging_namecheck.dbo.namecheck n
	                               ON  n.businesscaseid = @idName
	                          LEFT JOIN (
	                                   SELECT DISTINCT w.list_id,
	                                          nw.F_CUSTOMER_ID
	                                   FROM   plib_namecheck.dbo.WhiteList w
	                                          JOIN staging_namecheck.dbo.namecheck nw
	                                               ON  w.PartnerID = nw.businesscaseid
	                               ) w
	                               ON  res.list_id = w.LIST_ID
	                               AND n.F_CUSTOMER_ID = w.F_CUSTOMER_ID
	                   WHERE  RES.R >= @threshold
	                          AND w.LIST_ID IS NULL
	               )
	               r;
	    END;
	    ELSE
	    BEGIN
	        INSERT INTO debug_MN4_results
	        SELECT NULL,
	               NULL,
	               NULL,
	               NULL,
	               NULL,
	               NULL,
	               'no result from debug MN4',
	               NULL,
	               NULL,
	               @idName     bid,
	               NULL        TYPE,
	               'MatchName4_payments_I4C' ProcName;
	    END;
	END;
	
	IF @debug = 0
	   OR @debug = 1
	BEGIN
	    IF @NC_whitelist_check_IBAN = 1
	    BEGIN
	    	SELECT RES.R                      AS R,
	    	       SL.FIRST_NAME  AS FIRSTNAME,
	    	       (SL.LAST_NAME + ' ' + SL.FULL_NAME) AS LASTNAME,
	    	       CAST(SL.Alias AS VARCHAR(4096)) AS ADD_NAME1,
	    	       CAST(SL.Additional_Information AS VARCHAR(4096)) AS ADD_NAME2,
	    	       CAST(SL.Alias AS VARCHAR(4096)) AS Alias_,
	    	       CAST(SL.Additional_Information AS VARCHAR(4096)) AS Additional_Information_,
	    	       CAST(SL.[Case] AS VARCHAR(4096)) AS Case_,
	    	       SL.[ID],
	    	       SL.[Title],
	    	       SL.[Full_Name],
	    	       SL.[Last_Name],
	    	       SL.[First_Name],
	    	       SL.[Alias],
	    	       SL.[Companies],
	    	       SL.[Type_of_list],
	    	       SL.[List],
	    	       SL.[Publication_date],
	    	       SL.[Published_by],
	    	       SL.[Editor],
	    	       SL.[Whitelist],
	    	       SL.[Alternative_Script],
	    	       SL.[Function_],
	    	       SL.[Category],
	    	       SL.[AGE],
	    	       SL.[DOB],
	    	       SL.[POB],
	    	       SL.[Country],
	    	       SL.[Code],
	    	       SL.[Country_Of_Origin],
	    	       SL.[Country_Of_Activity],
	    	       SL.[Address],
	    	       SL.[Passport_Number],
	    	       SL.[Date_of_Information],
	    	       SL.[Authority],
	    	       SL.[AGE_DATE],
	    	       SL.[UPDATED]
	    	FROM   #Results RES
	    	       JOIN SANCTION_LISTS_VIEW SL
	    	            ON  SL.ID = RES.LIST_ID
	    	       LEFT JOIN staging_namecheck.dbo.namecheck n
	    	            ON  n.businesscaseid = @idName
	    	       LEFT JOIN (
	    	                SELECT DISTINCT w.list_id,
	    	                       nw.F_CUSTOMER_ID,
	    	                       nw.ACCOUNT, 
	    	                       nw.LASTNAME
	    	                FROM   plib_namecheck.dbo.WhiteList w
	    	                       JOIN staging_namecheck.dbo.namecheck nw
	    	                            ON  w.PartnerID = nw.businesscaseid
	    	            ) w
	    	            ON  res.list_id = w.LIST_ID
	    	            AND n.F_CUSTOMER_ID = w.F_CUSTOMER_ID
	    	            --31052022 if the account field in the whitelist entry is NULL, then the lastname condition was taken instead in whitelisting logic.
	    	            AND (
	    	                    (w.ACCOUNT = n.ACCOUNT AND w.ACCOUNT IS NOT NULL)
	    	                    OR (w.ACCOUNT IS NULL AND w.LASTNAME = n.LASTNAME)
	    	                )
	    	WHERE  RES.R >= @threshold
	    	       AND w.LIST_ID IS NULL;
	    END;
	    ELSE
	    BEGIN
	        SELECT RES.R             AS R,
	               SL.FIRST_NAME     AS FIRSTNAME,
	               (SL.LAST_NAME + ' ' + SL.FULL_NAME) AS LASTNAME,
	               CAST(SL.Alias AS VARCHAR(4096)) AS ADD_NAME1,
	               CAST(SL.Additional_Information AS VARCHAR(4096)) AS ADD_NAME2,
	               CAST(SL.Alias AS VARCHAR(4096)) AS Alias_,
	               CAST(SL.Additional_Information AS VARCHAR(4096)) AS Additional_Information_,
	               CAST(SL.[Case] AS VARCHAR(4096)) AS Case_,
	               SL.[ID],
	               SL.[Title],
	               SL.[Full_Name],
	               SL.[Last_Name],
	               SL.[First_Name],
	               SL.[Alias],
	               SL.[Companies],
	               SL.[Type_of_list],
	               SL.[List],
	               SL.[Publication_date],
	               SL.[Published_by],
	               SL.[Editor],
	               SL.[Whitelist],
	               SL.[Alternative_Script],
	               SL.[Function_],
	               SL.[Category],
	               SL.[AGE],
	               SL.[DOB],
	               SL.[POB],
	               SL.[Country],
	               SL.[Code],
	               SL.[Country_Of_Origin],
	               SL.[Country_Of_Activity],
	               SL.[Address],
	               SL.[Passport_Number],
	               SL.[Date_of_Information],
	               SL.[Authority],
	               SL.[AGE_DATE],
	               SL.[UPDATED]
	        FROM   #Results RES
	               JOIN SANCTION_LISTS_VIEW SL
	                    ON  SL.ID = RES.LIST_ID
	               LEFT JOIN staging_namecheck.dbo.namecheck n
	                    ON  n.businesscaseid = @idName
	               LEFT JOIN (
	                        SELECT DISTINCT w.list_id,
	                               nw.F_CUSTOMER_ID
	                        FROM   plib_namecheck.dbo.WhiteList w
	                               JOIN staging_namecheck.dbo.namecheck nw
	                                    ON  w.PartnerID = nw.businesscaseid
	                    ) w
	                    ON  res.list_id = w.LIST_ID
	                    AND n.F_CUSTOMER_ID = w.F_CUSTOMER_ID
	        WHERE  RES.R >= @threshold
	               AND w.LIST_ID IS     NULL;
	    END;
	END;
