---

, report_rows as (
    select
        si.id,
        sia.step,
        si.name,
        sia.accounts,
        a.s_pu,
        a.s_bu,
        coalesce(a.s_pu, 0) - coalesce(a.s_bu, 0) as razn
    from ourpension.spr_indicator_bu si
    join si_agg sia
        on si.id = sia.id
    left join agg_all a
        on si.id = a.id

    union all

    select
        id,
        step,
        '',
        '',
        s_pu,
        s_bu,
        coalesce(s_pu, 0) - coalesce(s_bu, 0)
    from all_sum

    union all

    select distinct
        si.is_aggregate,
        2,
        'в т.ч.',
        '',
        0,
        0,
        0
    from ourpension.spr_indicator_bu si
    join back_office.report_conformity_pu_bu r
        on r.id = $P{id}
       and r.service_type = any(si.service_type)
    where si.is_aggregate is not null
      and 'OPS' = any(si.service_type)

    union all

    select
        cast(
            (
                select coalesce(max(ind.id), 0)
                from ourpension.spr_indicator_bu ind
            )
            + row_number() over (order by m.account)
            as integer
        ) as id,
        1 as step,
        'Счёт ' || m.account as name,
        m.account as accounts,
        m.s_pu,
        m.s_bu,
        m.s_pu - m.s_bu as razn
    from (
        select
            rd.account,
            sum(
                coalesce(rd.sum_dt_pu, 0)
                - coalesce(rd.sum_ct_pu, 0)
            ) as s_pu,
            sum(
                coalesce(rd.sum_dt_bu, 0)
                - coalesce(rd.sum_ct_bu, 0)
            ) as s_bu
        from back_office.report_conformity_pu_bu_detail rd
        join back_office.report_conformity_pu_bu r
            on r.id = rd.report_conformity_pu_bu_id
        where rd.report_conformity_pu_bu_id = $P{id}
          and not exists (
              select 1
              from ourpension.spr_accounts_indicator_bu sa
              join ourpension.spr_indicator_bu si
                  on si.id = sa.spr_indicator_bu_id
              where sa.account = rd.account
                and r.service_type = any(si.service_type)
          )
        group by rd.account
    ) m
),
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)
    ) 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
            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'
        )
    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