-- Parsing the applicationCode and productCode from the request path.
DECLARE @searchIndex INT = PATINDEX('%/check/%', @request_path);
DECLARE @remainder VARCHAR(200) = RIGHT(@request_path, LEN(@request_path) - @searchIndex - LEN('check/'));
DECLARE @applicationCode name = SUBSTRING(@remainder, 0, CHARINDEX('/', @remainder));
DECLARE @productCode name = SUBSTRING(@remainder, CHARINDEX('/', @remainder) + 1, LEN(@remainder) - CHARINDEX('/', @remainder));

-- Declaring variables for the query string parameters.
DECLARE @adminId name;
DECLARE @customerNumber name;
DECLARE @referenceDate name;

-- Parsing the query string parameters into their corresponding variables
SELECT @adminId        = t1.adminId
     , @customerNumber = t1.customerNumber
     , @referenceDate  = t1.referenceDate
FROM [dbo].[get_query_string_param_values](@request_query_string)
PIVOT
(
    MAX(value) FOR name IN ([adminId], [customerNumber], [referenceDate])
) t1

-- Fetching the license corresponding to the applicationCode, productCode and the provided filters
-- as a JSON object and storing it in the response body.
SET @response_body =
(
    SELECT TOP 1 l.license_id         AS 'coreData.licenseId'
               , c.customer_number    AS 'coreData.customerNumber'
               , l.admin_id           AS 'coreData.adminId'
               , a.application_code   AS 'coreData.applicationCode'
               , p.product_code       AS 'coreData.productCode'
               , l.start_date         AS 'coreData.startDate'
               , l.end_date           AS 'coreData.endDate'
               , l.reference          AS 'coreData.reference'
               , l.quantity           AS 'coreData.quantity'
               , l.license_state      AS 'coreData.licenseState'
               , l.mutation_date_time AS 'mutationDateTime'
               , (
                      SELECT 'License ' + l.license_id   AS 'relation' 
                           , '/licenses/' + l.license_id AS 'href'
                           , 'GET'                       AS 'action'
                      FOR JSON PATH
                 ) AS 'links'
     FROM license l
     JOIN customer c
       ON l.customer_id = c.customer_id
     JOIN application a
       ON l.application_id = a.application_id
     JOIN product p
       ON a.application_id = p.application_id
     WHERE a.application_code = @applicationCode
       AND p.product_code     = @productCode
       AND (@adminId        IS NULL OR l.admin_id        = @adminId)
       AND (@customerNumber IS NULL OR c.customer_number = @customerNumber)
       AND (@referenceDate  IS NULL OR l.reference       = @referenceDate)
    FOR JSON PATH
);

IF @response_body IS NULL
BEGIN
    SET @response_status_code = 404;
END
ELSE
BEGIN
    SET @response_status_code = 200;
END

SET @response_headers = '{ "Content-Type": [ "application/json" ] }'

DECLARE @customerNumber name;
DECLARE @adminId name;
DECLARE @applicationCode name;
DECLARE @pageIndex int;
DECLARE @pageSize int;

-- Parsing the query string parameters into their corresponding variables
SELECT @customerNumber   = t1.customerNumber
        , @adminId          = t1.adminId
        , @applicationCode  = t1.applicationCode
        , @pageIndex        = COALESCE(t1.[pageNr], 1) -- default to 1
        , @pageSize         = COALESCE(t1.[pageSize], 50) -- default to 50
FROM [dbo].[get_query_string_param_values](@request_query_string)
PIVOT
(
    MAX(value) FOR name IN ([customerNumber], [adminId], [applicationCode], [productCode], [mutationDateTime], [licenseState], [pageNr], [pageSize])
) t1

-- Fetching all applications results as a JSON array.
DECLARE @applicationsResult VARCHAR(MAX);
SET @applicationsResult =
(
    SELECT l.admin_id
         , a.application_code
         , l.start_date
         , l.end_date
         , (
             SELECT 'Application ' + a.application_code   AS 'relation' 
                  , '/applications/' + a.application_code AS 'href'
                  , 'GET'                                 AS 'action'
             FOR JSON PATH
           ) AS 'links'
      FROM application a
      JOIN license l
        ON a.application_id = l.application_id
      JOIN customer c
        ON l.customer_id = c.customer_id
     WHERE l.license_state    = 'ACTIVE'
       AND c.customer_number  = @customerNumber
       AND l.admin_id         = @adminId
       AND a.application_code = @applicationCode
    ORDER BY a.application_code ASC
    OFFSET (@pageIndex - 1) * @pageSize ROWS FETCH NEXT @pageSize ROWS ONLY
    FOR JSON PATH
);

-- Determining the number of records given the current filters.
DECLARE @nrOfRowsWithFilters INT;
SET @nrOfRowsWithFilters =
(
    SELECT COUNT(*)
      FROM application a
      JOIN license l
        ON a.application_id = l.application_id
      JOIN customer c
        ON l.customer_id = c.customer_id
     WHERE l.license_state    = 'ACTIVE'
       AND c.customer_number  = @customerNumber
       AND l.admin_id         = @adminId
       AND a.application_code = @applicationCode
);

-- Calculating what the last page index is given the number of records and page size.
DECLARE @lastPage INT = CONVERT(INT, CEILING(CONVERT(decimal, @nrOfRowsWithFilters) / CONVERT(decimal, @pageSize)));

-- Producing the pageLinks JSON array given the current page index.
DECLARE @pageLinkResults VARCHAR(MAX);
SET @pageLinkResults =
(
    SELECT pageLinks.relation, pageLinks.href, pageLinks.action
    FROM
    (
        SELECT 0 as order_no
             , 'first' AS relation
             , '/licenses/accesses' + dbo.get_page_query_string(@request_query_string, 1, @pageSize) AS href
             , 'GET' AS action

        UNION ALL

        SELECT 1 as order_no
             , 'previous' AS relation
             , '/licenses/accesses' + dbo.get_page_query_string(@request_query_string, @pageIndex - 1, @pageSize) AS href
             , 'GET' AS action
        WHERE @pageIndex > 1

        UNION ALL

        SELECT 2 as order_no
             , 'self' AS relation
             , '/licenses/accesses' + dbo.get_page_query_string(@request_query_string, @pageIndex, @pageSize) AS href
             , 'GET' AS action

        UNION ALL

        SELECT 3 as order_no
             , 'next' AS relation
             , '/licenses/accesses' + dbo.get_page_query_string(@request_query_string, @pageIndex + 1, @pageSize) AS href
             , 'GET' AS action
        WHERE @pageIndex < @lastPage

        UNION ALL

        SELECT 4 as order_no
             , 'last' AS relation
             , '/licenses/accesses' + dbo.get_page_query_string(@request_query_string, @lastPage, @pageSize) AS href
             , 'GET' AS action
    ) AS pageLinks
    WHERE @lastPage != 0
    ORDER BY pageLinks.order_no ASC
    FOR JSON PATH
)

-- Creating the final JSON response, coalescing both JSON arrays to an empty JSON array if null
SET @response_body = 
(
    SELECT JSON_QUERY(COALESCE(@applicationsResult, '[]')) AS accesses
         , JSON_QUERY(COALESCE(@pageLinkResults, '[]')) AS pageLinks
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);

SET @response_status_code = 200;
SET @response_headers = '{ "Content-Type": [ "application/json" ] }';