---

sort_accounts as (
    select
        g.group_account,
        g.group_no,
        r.service_type,
        (
            select min(ind.id)
            from ourpension.spr_indicator_bu ind
            join ourpension.spr_accounts_indicator_bu sa
                on sa.spr_indicator_bu_id = ind.id
            where sa.account = g.anchor_account
              and r.service_type = any(ind.service_type)
        ) as sort_id
    from (
        values
            ('38911', '38911.1', 1),
            ('39011', '39011.1', 1),
            ('39031', '39033',   1),
            ('39032', '39033',   2),
            ('39033', '39033',   3),
            ('39034', '39034.2', 1),
            ('39035', '48305',   1),
            ('39036', '48305',   2),
            ('39037', '48305',   3),
            ('48305', '48305',   4),
            ('48306', '48306',   1)
    ) as g(group_account, anchor_account, group_no)
    join back_office.report_conformity_pu_bu r
        on r.id = $P{id}
       and r.service_type in ('NPO', 'PDS')
),
indicator_order as (
    select
        si.id,
        min(g.sort_id) as sort_id,
        min(g.group_no) as group_no,
        min(
            case
                when sa.account = g.group_account then 0
                when sa.account = g.group_account || '.1' then 1
                when sa.account = g.group_account || '.2' then 2

                when g.group_account in ('48305', '48306')
                 and sa.account = g.group_account || '.3'
                    then 2

                when g.group_account = '48305'
                 and sa.account = '48310' then 3

                when g.group_account = '48306'
                 and sa.account = '48309' then 3

                when g.group_account = '48305'
                 and sa.account = '60301' then 4

                when g.group_account = '48306'
                 and sa.account = '60302' then 4

                when g.group_account = '48305'
                 and sa.account = '60322' then 5

                when g.group_account = '48306'
                 and sa.account = '60323' then 5
            end
        ) as row_no
    from sort_accounts g
    join ourpension.spr_accounts_indicator_bu sa
        on sa.account in (
            g.group_account,
            g.group_account || '.1',
            g.group_account || '.2'
        )
        or (
            g.group_account = '48305'
            and sa.account in (
                '48305.3', '48310', '60301', '60322'
            )
        )
        or (
            g.group_account = '48306'
            and sa.account in (
                '48306.3', '48309', '60302', '60323'
            )
        )
    join ourpension.spr_indicator_bu si
        on si.id = sa.spr_indicator_bu_id
       and g.service_type = any(si.service_type)
    group by si.id
)
select
    rr.id,
    rr.step,
    rr.name,
    rr.accounts,
    rr.s_pu,
    rr.s_bu,
    rr.razn
from report_rows rr
left join indicator_order io
    on io.id = rr.id
order by
    coalesce(io.sort_id, rr.id),
    coalesce(io.group_no, 0),
    coalesce(io.row_no, 0),
    rr.id,
    rr.step