CREATE OR REPLACE PROCEDURE public.get_menu_permissions(
    IN input_params jsonb,
    INOUT result jsonb)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    v_user_id INT;
    v_menu_id INT;
    v_role_id INT;
    v_permission_type TEXT;
BEGIN
    -- Extract values from the input JSONB
    v_user_id := (input_params ->> 'user_id')::INT;
    v_menu_id := (input_params ->> 'menu_id')::INT;
    v_role_id := (input_params ->> 'role_id')::INT;
    v_permission_type := input_params ->> 'permission_type';

    -- Initialize the result as an empty JSONB array if it's not provided
    IF result IS NULL THEN
        result := '[]'::jsonb;
    END IF;

    -- Recursive query to build the hierarchical structure first
    WITH RECURSIVE RecursiveMenu AS (
        -- Base case: Select all top-level parent menu items (parent_id IS NULL) with the provided menu_id
        SELECT 
            m.id AS id,
            m.name AS parent_name,
            m.entity_id,
            m.menu_id,
            m.permission_id,
            m.link_type,
            m.menu_img,  -- Include menu_img here
            NULL::INTEGER AS parent_id_ref
        FROM menu_items m
        WHERE m.parent_id IS NULL AND m.menu_id = v_menu_id AND m.status_id = 1
        
        UNION ALL
        
        -- Recursive case: Join with the next level of child items
        SELECT 
            c.id AS id,
            c.name AS parent_name,
            c.entity_id,
            c.menu_id,
            c.permission_id,
            c.link_type,
            c.menu_img,  -- Include menu_img here
            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 AND c.status_id = 1
    ),
    
    -- Prepare a subquery to handle permissions based on userwise or rolewise
    AggregatedMenu AS (
        SELECT 
            p.id AS id,
            p.parent_id_ref AS parent_id,
            p.parent_name,
            p.entity_id,
            p.menu_img,  -- Include menu_img here
            json_build_object(
                'id', p.id,
                'parent_id', p.parent_id_ref,
                'parent_name', p.parent_name,
                'menu_img', p.menu_img  -- Include menu_img in the JSON object
            ) AS menu_item_json,
            CASE
                -- If the parent's entity_id is not null, aggregate both the parent and all children into a JSON array
                WHEN p.entity_id IS NOT NULL THEN (
                    SELECT json_agg(
                        json_build_object(
                            'id', child.id,
                            'name', child.parent_name,
                            'entity_id', child.entity_id,
                            'menu_img', child.menu_img,  -- Include menu_img here
                            'link_type', child.link_type,
                            'permission_id', child.permission_id,
                            'permission_name', perm.name,
                            'permission_value', 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,
                            -- Extra permissions (view permission)
                            'entity_permission_id', view_perm.id,
                            'entity_permission_name', view_perm.name,
                            'entity_permission_value', 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,
                            -- New field for child entity name when permission_name == 'child_details'
                            'child_entity_name', CASE
                                WHEN perm.name = 'child_details' THEN (
                                    SELECT e.entity_name
                                    FROM master_entities e
                                    WHERE e.id = child.entity_id
                                    LIMIT 1
                                )
                                ELSE NULL
                            END
                        )
                    )
                    FROM RecursiveMenu child
                    LEFT JOIN permissions perm ON child.permission_id = perm.id
                    -- Conditional JOIN based on permission_type
                    LEFT JOIN user_permissions up ON (v_permission_type = 'userwise' AND up.permission_id = child.permission_id AND up.user_id = v_user_id)
                    LEFT JOIN role_permissions rp ON (v_permission_type = 'rolewise' AND rp.permission_id = child.permission_id AND rp.role_id = v_role_id)
                    LEFT JOIN permissions view_perm ON view_perm.entity_id = child.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 (child.parent_id_ref = p.id OR child.id = p.id) AND (p.link_type != 2 AND p.link_type != 3)
                )
                ELSE NULL -- If parent's entity_id is null, permissions should be NULL
            END AS permissions
        FROM RecursiveMenu p where (p.link_type != 2 AND p.link_type != 3)
    )
    
    -- Final aggregation to collect the results into JSON format
    SELECT json_agg(
        json_build_object(
            'id', am.id,
            'parent_id', am.parent_id,
            'parent_name', am.parent_name,
            'menu_img', am.menu_img,  -- Include menu_img in the final result
            'permissions', am.permissions
        )
    ) INTO result
    FROM AggregatedMenu am;

    -- Raise a notice to show the result
    RAISE NOTICE 'Result: %', result::TEXT;
END;
$BODY$;

ALTER PROCEDURE public.get_menu_permissions(jsonb, jsonb)
    OWNER TO postgres;
