CREATE OR ALTER PROCEDURE dbo.get_configurations_values
    @category_ids NVARCHAR(MAX), -- JSON object containing category IDs with numerical keys
    @result NVARCHAR(MAX) OUTPUT -- Output JSON object
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @current_category_id NVARCHAR(255);
    DECLARE @config_key NVARCHAR(255);
    DECLARE @config_value NVARCHAR(MAX);

    -- Initialize the result as an empty JSON object if it's not provided
    IF @result IS NULL
    BEGIN
        SET @result = '{}';
    END;

    -- Use OPENJSON to parse the JSON array of category IDs
    DECLARE category_cursor CURSOR FOR
    SELECT 
        [key], 
        [value]
    FROM 
        OPENJSON(@category_ids, '$.category_ids')
    WITH (
        [key] NVARCHAR(255) '$.key',
        [value] NVARCHAR(255) '$.value'
    );

    OPEN category_cursor;
    FETCH NEXT FROM category_cursor INTO @current_category_id, @config_key;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Debug: Output the current category ID
        PRINT 'Current category ID: ' + @current_category_id;

        -- Loop through records matching the criteria
        DECLARE config_cursor CURSOR FOR
        SELECT config_key, config_value
        FROM app_configurations
        WHERE category_id = @current_category_id
        ORDER BY order_no ASC;

        OPEN config_cursor;
        FETCH NEXT FROM config_cursor INTO @config_key, @config_value;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- Debug: Output the config_key and config_value
            PRINT 'Found config_key: ' + @config_key + ', config_value: ' + @config_value;

            -- Add each record to the result JSON object
            SET @result = JSON_MODIFY(@result, '$.' + @config_key, @config_value);

            FETCH NEXT FROM config_cursor INTO @config_key, @config_value;
        END;

        CLOSE config_cursor;
        DEALLOCATE config_cursor;

        -- Debug: Check if any records were found for the current category ID
        IF @@ROWCOUNT = 0
        BEGIN
            PRINT 'No records found for category ID: ' + @current_category_id;
        END;

        -- Fetch the next category ID
        FETCH NEXT FROM category_cursor INTO @current_category_id, @config_key;
    END;

    CLOSE category_cursor;
    DEALLOCATE category_cursor;

    -- Check if the result JSON object is empty
    IF JSON_QUERY(@result) = '{}'
    BEGIN
        PRINT 'No configurations found for the provided category_ids.';
    END
    ELSE
    BEGIN
        PRINT 'Configurations retrieved: ' + @result;
    END;
END;
