Recursive CTE Employee Hierarchy Query – Name Optional

Task: Write a complete, production‑ready SQL script that uses a recursive Common Table Expression (CTE) to retrieve an employee hierarchy from a relational database. The script must be robust, well‑commented, and include optional filtering and ordering features.

Exact Requirements:

1. Schema Assumption

   • Table name: employees
   • Columns:
     • employee_id (PK, integer)
     • first_name (varchar)
     • last_name (varchar)
     • manager_id (FK to employees.employee_id, nullable) – the direct manager of the employee
     • department_id (integer, optional)
     • hire_date (date)
     • salary (numeric)

2. Recursive CTE Output Columns

   • employee_id
   • full_name – concatenation of first_name and last_name (e.g., “John Doe”)
   • manager_id
   • manager_name – full name of the direct manager (NULL for top‑level)
   • hierarchy_level – integer depth, where the top‑level (no manager) is 1
   • hierarchy_path – a string showing the chain of employee IDs from the root to the current row, separated by > (e.g., 1>4>9)
   • total_subordinates – count of all direct and indirect reports for each employee (calculated in a separate CTE or window function)

3. Cycle Detection

   • The query must detect and break potential management loops to avoid infinite recursion. Include a column is_cycle (boolean) that flags rows where a cycle was encountered.

4. Optional Filtering

   • Accept two optional parameters (placeholders) that can be replaced when the script is executed:
     • :dept_filter – if supplied (non‑NULL), return only employees belonging to that department_id.
     • :min_hire_date – if supplied (non‑NULL), return only employees hired on or after this date.

5. Ordering

   • Final result set should be ordered by hierarchy_path (so the hierarchy appears in depth‑first order) and then by full_name alphabetically.

6. Performance Considerations

   • Include indexes recommendations for the employees table that would optimize the recursive query.
   • Use OPTION (MAXRECURSION 0) (or the equivalent for the target RDBMS) to allow unlimited depth, but also explain how to limit depth if needed.

7. Compatibility

   • Provide the script for Microsoft SQL Server syntax. Include a brief note on how to adapt it for PostgreSQL and Oracle (e.g., WITH RECURSIVE, CONNECT BY).

8. Output Format

   • a. The full SQL script, wrapped in a markdown code block with language tag sql.
   • b. A concise explanation (max 200 words) of how the recursive CTE works, how cycle detection is implemented, and how total_subordinates is calculated.
   • c. A sample result set (3‑4 rows) in a markdown table that demonstrates the columns and typical values.

Deliverable: Produce the script and accompanying documentation exactly as described above, ensuring it can be copied and run without further modification (aside from replacing the optional parameters). Do not include any extraneous commentary.