---

with base as (
    select
        case
            when rd.pension_scheme_id is not null
                then 'id:' || cast(rd.pension_scheme_id as text)
            when nullif(btrim(rd.uidpensionscheme), '') is not null
                then 'uid:' || lower(btrim(rd.uidpensionscheme))
            else 'unknown'
        end as scheme_key,

        coalesce(
            nullif(btrim(ps.name), ''),
            nullif(btrim(ps.brief), ''),
            case
                when rd.pension_scheme_id is not null
                    then 'Схема ID '
                        || cast(rd.pension_scheme_id as text)
                when nullif(btrim(rd.uidpensionscheme), '') is not null
                    then 'UID схемы: '
                        || lower(btrim(rd.uidpensionscheme))
                else 'Без указания схемы'
            end
        ) as scheme_name,

        coalesce(rd.sum_dt_pu, 0)
            - coalesce(rd.sum_ct_pu, 0) as s_pu,

        coalesce(rd.sum_dt_bu, 0)
            - coalesce(rd.sum_ct_bu, 0) as s_bu

    from back_office.report_conformity_pu_bu_detail rd
    left join prod_line.pens_scheme ps
        on ps.id = rd.pension_scheme_id
    where rd.report_conformity_pu_bu_id = $P{id}
      and rd.account = '38902'
),
grouped as (
    select
        scheme_key,
        min(scheme_name) as name,
        sum(s_pu) as s_pu,
        sum(s_bu) as s_bu
    from base
    group by scheme_key
),
report_rows as (
    select
        0 as step,
        'РППО по договорам НПО (ООЧП) (38902)' as name,
        coalesce(sum(s_pu), 0) as s_pu,
        coalesce(sum(s_bu), 0) as s_bu
    from base

    union all

    select
        1 as step,
        name,
        s_pu,
        s_bu
    from grouped
)
select
    step,
    name,
    s_pu,
    s_bu,
    s_pu - s_bu as razn
from report_rows
order by step, name