-- Create employee hierarchy table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT,
    salary DECIMAL(10,2),
    department VARCHAR(50),
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

-- Insert sample data
INSERT INTO employees VALUES
(1, "Alice Johnson", NULL, 150000, "Executive"),
(2, "Bob Smith", 1, 120000, "Engineering"),
(3, "Carol Davis", 1, 120000, "Marketing"),
(4, "David Wilson", 2, 90000, "Engineering");

-- Get full organizational chart with level
WITH RECURSIVE org_chart AS (
    SELECT 
        employee_id,
        name,
        manager_id,
        1 AS level,
        CAST(name AS CHAR(1000)) AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT 
        e.employee_id,
        e.name,
        e.manager_id,
        oc.level + 1,
        CONCAT(oc.path, " -> ", e.name)
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT 
    level,
    REPEAT("  ", level - 1) || name AS indented_name,
    path
FROM org_chart
ORDER BY path;

-- Find all subordinates of a manager
WITH RECURSIVE subordinates AS (
    SELECT employee_id, name, manager_id, 1 AS depth
    FROM employees
    WHERE employee_id = 2
    
    UNION ALL
    
    SELECT e.employee_id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates
ORDER BY depth, name;