CREATE OR ALTER PROCEDURE dbo.get_menu_permissions
    @input_params NVARCHAR(MAX),
    @result NVARCHAR(MAX) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @v_user_id INT;
    DECLARE @v_menu_id INT;
    DECLARE @v_role_id INT;
    DECLARE @v_permission_type NVARCHAR(50);

    -- Extract values from JSON input
    SET @v_user_id = JSON_VALUE(@input_params, '$.user_id');
    SET @v_menu_id = JSON_VALUE(@input_params, '$.menu_id');
    SET @v_role_id = JSON_VALUE(@input_params, '$.role_id');
    SET @v_permission_type = JSON_VALUE(@input_params, '$.permission_type');

    -- Initialize the result as an empty JSON array if not provided
    IF @result IS NULL
    BEGIN
        SET @result = '[]';
    END;

    -- Recursive Common Table Expression for menu hierarchy
    WITH RecursiveMenu AS (
        -- Base case: Get top-level menu items
        SELECT 
            m.id,
            m.name AS parent_name,
            m.entity_id,
            m.menu_id,
            m.permission_id,
            m.link_type,
            m.menu_img,
            NULL AS parent_id_ref
        FROM menu_items m
        WHERE m.parent_id IS NULL AND m.menu_id = @v_menu_id

        UNION ALL

        -- Recursive case: Get child menu items
        SELECT 
            c.id,
            c.name AS parent_name,
            c.entity_id,
            c.menu_id,
            c.permission_id,
            c.link_type,
            c.menu_img,
            rm.id AS parent_id_ref
        FROM menu_items c
        INNER JOIN RecursiveMenu rm ON c.parent_id = rm.id
        WHERE c.menu_id = @v_menu_id
    )

    -- Final aggregation with permissions
    SELECT @result = (
        SELECT 
            r.id,
            r.parent_id_ref AS parent_id,
            r.parent_name,
            r.menu_img,
            CASE
                WHEN r.entity_id IS NOT NULL THEN (
                    SELECT 
                        c.id,
                        c.parent_name AS name,
                        c.entity_id,
                        c.menu_img,
                        c.link_type,
                        c.permission_id,
                        perm.name AS permission_name,
                        CASE 
                            WHEN @v_permission_type = 'userwise' 
                            THEN CASE WHEN up.permission_id IS NOT NULL THEN 'true' ELSE 'false' END
                            WHEN @v_permission_type = 'rolewise' 
                            THEN CASE WHEN rp.permission_id IS NOT NULL THEN 'true' ELSE 'false' END
                            ELSE 'false'
                        END AS permission_value,
                        view_perm.id AS entity_permission_id,
                        view_perm.name AS entity_permission_name,
                        CASE 
                            WHEN @v_permission_type = 'userwise' 
                            THEN CASE WHEN up_view.permission_id IS NOT NULL THEN 'true' ELSE 'false' END
                            WHEN @v_permission_type = 'rolewise' 
                            THEN CASE WHEN rp_view.permission_id IS NOT NULL THEN 'true' ELSE 'false' END
                            ELSE 'false'
                        END AS entity_permission_value
                    FROM RecursiveMenu c
                    LEFT JOIN permissions perm ON c.permission_id = perm.id
                    LEFT JOIN user_permissions up ON (@v_permission_type = 'userwise' AND up.permission_id = c.permission_id AND up.user_id = @v_user_id)
                    LEFT JOIN role_permissions rp ON (@v_permission_type = 'rolewise' AND rp.permission_id = c.permission_id AND rp.role_id = @v_role_id)
                    LEFT JOIN permissions view_perm ON view_perm.entity_id = c.entity_id AND view_perm.name = 'view'
                    LEFT JOIN user_permissions up_view ON (@v_permission_type = 'userwise' AND up_view.permission_id = view_perm.id AND up_view.user_id = @v_user_id)
                    LEFT JOIN role_permissions rp_view ON (@v_permission_type = 'rolewise' AND rp_view.permission_id = view_perm.id AND rp_view.role_id = @v_role_id)
                    WHERE (c.parent_id_ref = r.id OR c.id = r.id) AND (r.link_type != 2 AND r.link_type != 3)
                    FOR JSON PATH, INCLUDE_NULL_VALUES
                )
                ELSE NULL -- If parent's entity_id is null, permissions should be NULL
            END AS permissions
        FROM RecursiveMenu r
        WHERE (r.link_type != 2 AND r.link_type != 3)
        FOR JSON PATH, INCLUDE_NULL_VALUES
    );

    -- Print the result for debugging
    PRINT 'Result: ' + @result;
END;
