WITH RECURSIVE role_tree AS (
    -- 최상위 권한그룹
    SELECT ROLE_CD,
           ROLE_NM,
           ROLE_TYPE_CD,
           UP_ROLE_CD,
           SYS_CD,
           ROLE_SORT_SEQ,
           1 AS depth,
           CAST(ROLE_CD AS TEXT) AS path,
           LPAD(CAST(COALESCE(ROLE_SORT_SEQ, 999) AS TEXT), 10, '0') AS sort_path
    FROM iccom.ROLE
    WHERE UP_ROLE_CD IS NULL
      AND ROLE_TYPE_CD != 'ROLTYP999'

    UNION ALL

    -- 하위 권한그룹 (재귀)
    SELECT r.ROLE_CD,
           r.ROLE_NM,
           r.ROLE_TYPE_CD,
           r.UP_ROLE_CD,
           r.SYS_CD,
           r.ROLE_SORT_SEQ,
           rt.depth + 1 AS depth,
           CAST(rt.path || ' > ' || r.ROLE_CD AS TEXT) AS path,
           rt.sort_path || '-' || LPAD(CAST(COALESCE(r.ROLE_SORT_SEQ, 999) AS TEXT), 10, '0') AS sort_path
    FROM iccom.ROLE r
    INNER JOIN role_tree rt ON r.UP_ROLE_CD = rt.ROLE_CD
    WHERE r.ROLE_TYPE_CD != 'ROLTYP999'
      AND r.UP_ROLE_CD IS NOT NULL
)
SELECT depth, ROLE_CD, ROLE_NM, UP_ROLE_CD, SYS_CD, ROLE_SORT_SEQ, path
FROM role_tree
ORDER BY sort_path;
