---

with cte as (
    select
        2 as cid,
        0 as pid,
        'текущие обязательства' as name,
        '' as strateg,

        sum(case
            when account = '38302'
             and contract_class_code = 'INS'
             and contract_model_label = 'VFA'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end) as sVFA_рu,

        sum(case
            when account = '38902'
             and contract_class_code = 'INS'
             and contract_model_label = 'BBA'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end) as ВВА_рu,

        sum(case
            when account in ('39002', '39022', '39023', '39024')
             and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end) as iVFA_рu,

        sum(case
            when account = '38302'
             and coalesce(contract_class_code, 'INS') = 'INS'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end) as sVFA_bu,

        sum(case
            when account = '38902'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end) as ВВА_bu,

        sum(case
            when account in ('39002', '39022', '39023', '39024')
             and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end) as iVFA_bu

    from back_office.report_conformity_pu_bu_detail
    where report_conformity_pu_bu_id = $P{id}

    union all

    select
        3, 0, 'обязательства к выплате', '',

        sum(case
            when account = '38311.1'
             and contract_class_code = 'INS'
             and contract_model_label = 'VFA'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        sum(case
            when account = '38911.1'
             and contract_class_code = 'INS'
             and contract_model_label = 'BBA'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        sum(case
            when account in ('39011.1', '39032.1', '39033.1', '39037.1')
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        sum(case
            when account = '38311.1'
             and coalesce(contract_class_code, 'INS') = 'INS'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end),

        sum(case
            when account = '38911.1'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end),

        sum(case
            when account in ('39011.1', '39032.1', '39033.1', '39037.1')
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end)

    from back_office.report_conformity_pu_bu_detail
    where report_conformity_pu_bu_id = $P{id}

    union all

    select
        4, 0, 'обязательства, не исполненные в срок', '',

        sum(case
            when account = '38311.2'
             and contract_class_code = 'INS'
             and contract_model_label = 'VFA'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        sum(case
            when account = '38911.2'
             and contract_class_code = 'INS'
             and contract_model_label = 'BBA'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        sum(case
            when account in ('39011.2', '39032.2', '39033.2', '39037.2')
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        sum(case
            when account = '38311.2'
             and coalesce(contract_class_code, 'INS') = 'INS'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end),

        sum(case
            when account = '38911.2'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end),

        sum(case
            when account in ('39011.2', '39032.2', '39033.2', '39037.2')
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end)

    from back_office.report_conformity_pu_bu_detail
    where report_conformity_pu_bu_id = $P{id}

    union all

    select
        5, 0, 'Страховой резерв', '',
        null,

        sum(case
            when account = '39703'
             and contract_class_code = 'INS'
             and contract_model_label = 'BBA'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        sum(case
            when account = '39703'
             and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        null,

        sum(case
            when account = '39703'
             and coalesce(contract_class_code, 'INS') = 'INS'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end),

        sum(case
            when account = '39703'
             and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end)

    from back_office.report_conformity_pu_bu_detail
    where report_conformity_pu_bu_id = $P{id}

    union all

    select
        8, 0, 'Незавершенные (неопознанные) платежи', '',
        null,

        sum(case
            when account = '48309'
             and contract_class_code = 'INS'
             and contract_model_label = 'BBA'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        sum(case
            when account = '48309'
             and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        null,

        sum(case
            when account = '48309'
             and coalesce(contract_class_code, 'INS') = 'INS'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end),

        sum(case
            when account = '48309'
             and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end)

    from back_office.report_conformity_pu_bu_detail
    where report_conformity_pu_bu_id = $P{id}

    union all

    select
        9, 0, 'Расчеты по пенсионным взносам (переплаты)', '',
        null,

        sum(case
            when account = '48306.1'
             and contract_class_code = 'INS'
             and contract_model_label = 'BBA'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        sum(case
            when account = '48306.3'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        null,

        sum(case
            when account = '48306.1'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end),

        sum(case
            when account = '48306.3'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end)

    from back_office.report_conformity_pu_bu_detail
    where report_conformity_pu_bu_id = $P{id}

    union all

    select
        10, 0, 'Расчеты по пенсионным взносам с ФЛ', '',
        null,

        sum(case
            when account = '48301.1'
             and contract_class_code = 'INS'
             and contract_model_label = 'BBA'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        sum(case
            when account = '48301.3'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        null,

        sum(case
            when account = '48301.1'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end),

        sum(case
            when account = '48301.3'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end)

    from back_office.report_conformity_pu_bu_detail
    where report_conformity_pu_bu_id = $P{id}

    union all

    select
        11, 0, 'Расчеты по пенсионным взносам с ЮЛ', '',
        null,

        sum(case
            when account = '48303.1'
             and contract_class_code = 'INS'
             and contract_model_label = 'BBA'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        sum(case
            when account = '48303.3'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        null,

        sum(case
            when account = '48303.1'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end),

        sum(case
            when account = '48303.3'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end)

    from back_office.report_conformity_pu_bu_detail
    where report_conformity_pu_bu_id = $P{id}

    union all

    select
        13, 0, 'Незавершенные (неопознанные) платежи', '',
        null,

        sum(case
            when account = '48310'
             and contract_class_code = 'INS'
             and contract_model_label = 'BBA'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        sum(case
            when account = '48310'
             and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        null,

        sum(case
            when account = '48310'
             and coalesce(contract_class_code, 'INS') = 'INS'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end),

        sum(case
            when account = '48310'
             and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end)

    from back_office.report_conformity_pu_bu_detail
    where report_conformity_pu_bu_id = $P{id}

    union all

    select
        14, 0, 'Расчеты по выплатам по договорам', '',
        null,

        sum(case
            when account = '48305.1'
             and contract_class_code = 'INS'
             and contract_model_label = 'BBA'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        sum(case
            when account = '48305.3'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        null,

        sum(case
            when account = '48305.1'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end),

        sum(case
            when account = '48305.3'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end)

    from back_office.report_conformity_pu_bu_detail
    where report_conformity_pu_bu_id = $P{id}

    union all

    select
        15, 0, 'Расчеты по пенсионным взносам с ФЛ', '',
        null,

        sum(case
            when account = '48302.1'
             and contract_class_code = 'INS'
             and contract_model_label = 'BBA'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        sum(case
            when account = '48302.3'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        null,

        sum(case
            when account = '48302.1'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end),

        sum(case
            when account = '48302.3'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end)

    from back_office.report_conformity_pu_bu_detail
    where report_conformity_pu_bu_id = $P{id}

    union all

    select
        16, 0, 'Расчеты по пенсионным взносам с ЮЛ', '',
        null,

        sum(case
            when account = '48304.1'
             and contract_class_code = 'INS'
             and contract_model_label = 'BBA'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        sum(case
            when account = '48304.3'
            then abs(coalesce(sum_dt_pu, 0) - coalesce(sum_ct_pu, 0))
        end),

        null,

        sum(case
            when account = '48304.1'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end),

        sum(case
            when account = '48304.3'
            then abs(coalesce(sum_dt_bu, 0) - coalesce(sum_ct_bu, 0))
        end)

    from back_office.report_conformity_pu_bu_detail
    where report_conformity_pu_bu_id = $P{id}
),
cte_gr as (
    select
        1 as cid, 0 as pid,
        'РППО, итого' as name, '' as strateg,
        sum(svfa_рu) as svfa_рu,
        sum(ВВА_рu) as ВВА_рu,
        sum(ivfa_рu) as ivfa_рu,
        sum(svfa_bu) as svfa_bu,
        sum(ВВА_bu) as ВВА_bu,
        sum(ivfa_bu) as ivfa_bu
    from cte
    where cid in (2, 3, 4)

    union all

    select
        7, 0, 'Дебиторская задолженность, итого', '',
        sum(svfa_рu),
        sum(ВВА_рu),
        sum(ivfa_рu),
        sum(svfa_bu),
        sum(ВВА_bu),
        sum(ivfa_bu)
    from cte
    where cid in (8, 9, 10, 11)

    union all

    select
        12, 0, 'Кредиторская задолженность, итого', '',
        sum(svfa_рu),
        sum(ВВА_рu),
        sum(ivfa_рu),
        sum(svfa_bu),
        sum(ВВА_bu),
        sum(ivfa_bu)
    from cte
    where cid in (13, 14, 15, 16)
),
cte_gr_gr as (
    select
        0 as cid, 0 as pid,
        'ПЕНСИОННЫЕ ОБЯЗАТЕЛЬСТВА по НПО, итого' as name,
        '' as strateg,
        sum(svfa_рu) as svfa_рu,
        sum(ВВА_рu) as ВВА_рu,
        sum(ivfa_рu) as ivfa_рu,
        sum(svfa_bu) as svfa_bu,
        sum(ВВА_bu) as ВВА_bu,
        sum(ivfa_bu) as ivfa_bu
    from cte
    where cid in (1, 5)

    union all

    select
        6, 0, 'РАСЧЕТЫ ПО ДОГОВОРАМ НПО', '',
        sum(case when cid = 12 then svfa_рu * -1 else svfa_рu end),
        sum(case when cid = 12 then ВВА_рu * -1 else ВВА_рu end),
        sum(case when cid = 12 then ivfa_рu * -1 else ivfa_рu end),
        sum(case when cid = 12 then svfa_bu * -1 else svfa_bu end),
        sum(case when cid = 12 then ВВА_bu * -1 else ВВА_bu end),
        sum(case when cid = 12 then ivfa_bu * -1 else ivfa_bu end)
    from cte_gr
    where cid in (7, 12)
),
cte_ps as (
    select
        1 as cid,
        row_number() over (
            order by
                g.uidpensionscheme,
                g.pension_scheme_id,
                g.uidsubtypeactivity,
                g.strategy_id
        ) as pid,

        coalesce(
            ps_id.name,
            ps_uid.name,
            case
                when g.pension_scheme_id is null
                 and nullif(btrim(g.uidpensionscheme), '') is null
                then 'Пенсионная схема не указана'
                else 'Пенсионная схема не сопоставлена'
            end
        ) as name,

        coalesce(
            st_id.name,
            st_uid.name,
            case
                when g.strategy_id is null
                 and nullif(btrim(g.uidsubtypeactivity), '') is null
                then 'Стратегия не указана'
                else 'Стратегия не сопоставлена'
            end
        ) as strateg,

        g.svfa_рu,
        g.ВВА_рu,
        g.ivfa_рu,
        g.svfa_bu,
        g.ВВА_bu,
        g.ivfa_bu

    from (
        select
            rd.pension_scheme_id,
            rd.uidpensionscheme,
            rd.strategy_id,
            rd.uidsubtypeactivity,

            sum(case
                when rd.account = '38302'
                 and rd.contract_class_code = 'INS'
                 and rd.contract_model_label = 'VFA'
                then abs(
                    coalesce(rd.sum_dt_pu, 0)
                    - coalesce(rd.sum_ct_pu, 0)
                )
            end) as svfa_рu,

            sum(case
                when rd.account = '38902'
                 and rd.contract_class_code = 'INS'
                 and rd.contract_model_label = 'BBA'
                then abs(
                    coalesce(rd.sum_dt_pu, 0)
                    - coalesce(rd.sum_ct_pu, 0)
                )
            end) as ВВА_рu,

            sum(case
                when rd.account in (
                    '39002', '39022', '39023', '39024'
                )
                 and coalesce(rd.contract_class_code, 'INV') = 'INV'
                then abs(
                    coalesce(rd.sum_dt_pu, 0)
                    - coalesce(rd.sum_ct_pu, 0)
                )
            end) as ivfa_рu,

            sum(case
                when rd.account = '38302'
                 and coalesce(rd.contract_class_code, 'INS') = 'INS'
                then abs(
                    coalesce(rd.sum_dt_bu, 0)
                    - coalesce(rd.sum_ct_bu, 0)
                )
            end) as svfa_bu,

            sum(case
                when rd.account = '38902'
                then abs(
                    coalesce(rd.sum_dt_bu, 0)
                    - coalesce(rd.sum_ct_bu, 0)
                )
            end) as ВВА_bu,

            sum(case
                when rd.account in (
                    '39002', '39022', '39023', '39024'
                )
                 and coalesce(rd.contract_class_code, 'INV') = 'INV'
                then abs(
                    coalesce(rd.sum_dt_bu, 0)
                    - coalesce(rd.sum_ct_bu, 0)
                )
            end) as ivfa_bu

        from back_office.report_conformity_pu_bu_detail rd
        where rd.report_conformity_pu_bu_id = $P{id}
          and rd.account in (
              '38302', '38902', '39002',
              '39022', '39023', '39024'
          )
        group by
            rd.pension_scheme_id,
            rd.uidpensionscheme,
            rd.strategy_id,
            rd.uidsubtypeactivity
    ) g

    left join prod_line.pens_scheme ps_id
        on ps_id.id = g.pension_scheme_id

    left join prod_line.hb_invest_strategy st_id
        on st_id.id = g.strategy_id

    left join lateral (
        select max(ps.name) as name
        from prod_line.pens_scheme ps
        where g.pension_scheme_id is null
          and nullif(
              lower(btrim(cast(ps.uid_1c as text))), ''
          ) = nullif(
              lower(btrim(g.uidpensionscheme)), ''
          )
        having count(*) = 1
    ) ps_uid on true

    left join lateral (
        select max(st.name) as name
        from prod_line.hb_invest_strategy st
        where g.strategy_id is null
          and nullif(
              lower(btrim(cast(st.uid_1c as text))), ''
          ) = nullif(
              lower(btrim(g.uidsubtypeactivity)), ''
          )
        having count(*) = 1
    ) st_uid on true
),
cte_all as (
    select * from cte

    union all

    select * from cte_gr

    union all

    select * from cte_gr_gr

    union all

    select * from cte_ps
),
cte_all_itog as (
    select
        *,
        coalesce(svfa_рu, 0)
            + coalesce(ВВА_рu, 0) as s_pu,

        coalesce(svfa_рu, 0)
            + coalesce(ВВА_рu, 0)
            + coalesce(ivfa_рu, 0) as itog_pu,

        coalesce(svfa_bu, 0)
            + coalesce(ВВА_bu, 0) as s_bu,

        coalesce(svfa_bu, 0)
            + coalesce(ВВА_bu, 0)
            + coalesce(ivfa_bu, 0) as itog_bu

    from cte_all
)
select
    *,
    s_pu - s_bu as rash_s,
    coalesce(ivfa_рu, 0)
        - coalesce(ivfa_bu, 0) as rash_i,
    itog_pu - itog_bu as rash_itog
from cte_all_itog
order by cid, pid