Dynamic Hierarchical Reporting with Recursive Common Table Expressions

Design a recursive CTE that can dynamically map organizational hierarchies with unlimited depth, supporting both top-down and bottom-up traversal. The solution must handle complex org structures where employees can have multiple managers or matrix reporting relationships. Include performance optimization techniques and demonstrate how to efficiently calculate reporting chains, total subordinate counts, and organizational path tracing.