with recursive cte_c as
(
  select
    1 as level
    ,call_group
    ,rule_id
    ,name
    ,parent_group
    ,null as keypress
    ,company_id
    ,call_group::text as route
    ,name::text as r_name
  from
    groups
  where
    company_id = 5
    and call_group = 1
    and rule_id = 4
union all
  select
    level + 1
    ,g.call_group
    ,g.rule_id
    ,g.name
    ,g.parent_group
    ,g.keypress::text
    ,g.company_id
    ,c.route || ',' || g.call_group::text
    ,repeat(', ', level) || g.name
  from
    cte_c c
    ,groups g
  where
    c.call_group = g.parent_group
    and c.company_id = g.company_id
    and not (g.call_group::text = any(string_to_array(c.route, ',')))
)
select
  row_number() over(order by route) as rn
  ,level
  ,call_group
  ,parent_group
  ,rule_id
  ,name
  ,keypress
  ,route
  ,(select
    filename
  from
    ivr_files i1
  where
    i1.company_id = c.company_id
    and i1.call_group = c.call_group)
  ,array_to_string(
    array(
      select
        line
      from
        groups_line gl
      where
        gl.company_id = c.company_id
        and gl.call_group = c.call_group
      order by
        random()
      )
    ,','
  ) as at1
from
  cte_c c
order by
  route;
