---

with agg_p as (
select
    si.id,
    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 ourpension.spr_indicator_bu si
join ourpension.spr_indicator_bu si1
on si.id = si1.is_aggregate
join ourpension.spr_accounts_indicator_bu sa
    on sa.spr_indicator_bu_id = si1.id
join back_office.report_conformity_pu_bu_detail rd
    on rd.report_conformity_pu_bu_id = $P{id}
    and rd."account" = sa.account
group by si.id
),
agg_sum as (
 select
    si.id,
    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 ourpension.spr_indicator_bu si
  join ourpension.spr_accounts_indicator_bu sa
    on sa.spr_indicator_bu_id = si.id
  join back_office.report_conformity_pu_bu_detail rd
    on rd.report_conformity_pu_bu_id = $P{id}
    and rd."account" = sa.account
  join back_office.report_conformity_pu_bu r
    on r.id = rd.report_conformity_pu_bu_id
   and r."service_type" = any(si."service_type")
  group by si.id
),
all_sum as (
    select
        0 as id,
        1 as step,
        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
    where rd.report_conformity_pu_bu_id = $P{id}
),
agg_all as (
select id, s_pu, s_bu
from agg_p
union all
select a.id, a.s_pu, a.s_bu
from agg_sum a
left join agg_p b 
       on a.id = b.id
where b.id is null
),
si_agg as (
  select si.id, 1 as step, string_agg(sa.account,'+') as accounts
  from ourpension.spr_indicator_bu si
  join ourpension.spr_accounts_indicator_bu sa
    on sa.spr_indicator_bu_id = si.id
  join back_office.report_conformity_pu_bu r
    on r.id = $P{id}
   and r."service_type" = any(si."service_type")
  group by si.id
)
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
order by id, step