USE [ArizonaREP];
GO
/*============================================================================= 

Re-implementation of SQL Server STRING_SPLIT() for older versions of SQL Server

Parameters
----------------------
@string    NVARCHAR(MAX)   The string to split
@delimiter NCHAR(1)        The delimiter to split on

Returns a table with a single column "value" of type NVARCHAR(MAX)

Creation : 29.09.2025 / TSC
Modifications:

=============================================================================*/ 

CREATE OR ALTER FUNCTION [dbo].[fnSplitString] (
    @string    NVARCHAR(MAX),
    @delimiter NCHAR(1)
)
RETURNS @output TABLE (
    [value] NVARCHAR(MAX) NULL 
)
BEGIN
    DECLARE @start INT,
            @end   INT;
    SELECT @start = 1,
           @end   = CHARINDEX(@delimiter, @string);
    WHILE @start < LEN(@string) + 1
    BEGIN
        IF @end = 0
            SET @end = LEN(@string) + 1;

        INSERT INTO @output (
            [value]
        )
        VALUES (
            SUBSTRING(@string, @start, @end - @start)
        );
        SET @start = @end + 1;
        SET @end = CHARINDEX(@delimiter, @string, @start);

    END;
    RETURN;
END;
GO
--#endregion fn_StringSplit()

--#region aps_get_address_authorization_granted_bulk
GO
/*============================================================================= 

Parse a list of comma separated addresses_guid and check for each of them
if a charte has been approved.

Extension of the scalar function [aps_fn_Is_Address_Authorization_Granted] to allow
more than 1 address to be validated at once

Parameters
----------------------
@addresse_guid_list   VARCHAR(MAX)  A comma delimited list of GUID to be checked for chart validation

Context
----------------------
Deployed and used in the adresse repositories

Creation : 25.09.2025 / TSC
Modifications:

=============================================================================*/ 
CREATE OR ALTER PROCEDURE dbo.aps_get_address_authorization_granted_bulk
  @addresse_guid_list VARCHAR(MAX)
AS 
BEGIN 

  WITH [cteValues] AS (
      --transform input values in a table
      SELECT [value] AS [address_guid]
      FROM [dbo].fnSplitString(@addresse_guid_list, ',')
  )

  SELECT 
      v.[address_guid]
      , dbo.[aps_fn_Is_Address_Authorization_Granted](v.[address_guid],1,null) AS isGranted
  FROM [cteValues] v;
END 
GO
--#endregion aps_get_address_authorization_granted_bulk