-- 30_recursive_cte_org_hierarchy.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Recursive CTE for branch/org hierarchy.
-- Context: Retail bank example with customers, accounts, transactions, cards, loans.
-- Date: 2025-08-18
-- Notes:
--   * Safe to read; run on a development DB.
--   * Requires: CREATE privileges; some files may need superuser for extensions.
--   * Search path set to 'bank' schema for clarity.

-- Org hierarchy with recursive CTE
CREATE TABLE IF NOT EXISTS bank.employees (
  emp_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  name TEXT NOT NULL,
  manager_id BIGINT REFERENCES bank.employees(emp_id)
);

-- Hierarchy under CEO (emp_id = 1)
WITH RECURSIVE org AS (
  SELECT emp_id, name, manager_id, 1 AS lvl FROM bank.employees WHERE emp_id = 1
  UNION ALL
  SELECT e.emp_id, e.name, e.manager_id, o.lvl + 1 FROM bank.employees e JOIN org o ON e.manager_id = o.emp_id
)
SELECT * FROM org ORDER BY lvl, name;
