DROP FUNCTION IF EXISTS public.get_pending_approval_levels(approval_process_job_workflows, TEXT);
CREATE OR REPLACE FUNCTION public.get_pending_approval_levels(job approval_process_job_workflows, status TEXT)
RETURNS JSONB
LANGUAGE plpgsql
AS $function$
DECLARE
    result jsonb;
BEGIN
    SELECT jsonb_agg(row_to_json(t))
    INTO result
    FROM (
        SELECT * 
        FROM approval_process_job_workflows
        WHERE 
            approval_process_job_id = job.approval_process_job_id AND
            approver_order_no > job.approver_order_no AND
            review_status = status::public."ApprovalStatus"
        ORDER BY approver_order_no
    ) t;
    RETURN COALESCE(result, '[]'::jsonb);
EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Unexpected error in get_pending_approval_levels: %', SQLERRM;
END;
$function$;