---

with ops_sharer(id/*, individual_id*/, individual_death_date) as NOT MATERIALIZED (
    select s.id/*, s.individual_id*/, death_info.death_date
    from ourpension.sharer s
    join ourpension.contract c on s.contract_id = c.id
    join report.dia_param p on true
    join lateral (
        select a.death_date
        from ourpension.application_death_info a
        where a.individual_id = s.individual_id
            and a.accepted is not null
            and a.canceled is null
            and a.rejected is null
        order by a.version desc
        limit 1
    )death_info on true
    left join lateral(
        select d.sharer_id
        from back_office.ops_transfer_details d
        join back_office.ops_transfer_register tr on tr.id = d.ops_transfer_register_id
        join back_office.operation o on o.ops_transfer_register_id = tr.id
        where d.sharer_id = s.id
            and o.approved is not null
            /*and o.deleted is null and */
            and o.effective_date <= p.report_date
        limit 1
    )transfer on true
    where c.service_type = 'OPS'
    and death_info.death_date <= p.report_date
    and transfer.sharer_id is null
),t_balance_all as (
    select
        da.owner_sharer_id as sharer_id,
        sum(t.value) balance,
        sum(case when dat.mnemonics in ('ОПС', 'ОПС ГВ', 'ОПС КП', 'Р ПП ОПС') then t.value else 0 end) ops,
        sum(case when dat.mnemonics in ('ОПС ИД','ОПС ФР', 'Р ПП ОПС ИД') then t.value else 0 end) ops_id,
        sum(case when dat.mnemonics in ('ДПС Г', 'ДПС Г ГВ', 'ДПС Г КП', 'Р ПП ДПС Г') then t.value else 0 end) dps_g,
        sum(case when dat.mnemonics in ('ДПС Г ИД', 'ДПС Г ФР', 'Р ПП ДПС Г ИД') then t.value else 0 end) dps_g_id,
        sum(case when dat.mnemonics in ('ДПС Л', 'ДПС Л ГВ', 'ДПС Л КП', 'Р ПП ДПС Л' ) then t.value else 0 end) dps_l,
        sum(case when dat.mnemonics in ('ДПС Л ИД', 'ДПС Л ФР', 'Р ПП ДПС Л ИД') then t.value else 0 end) dps_l_id,
        sum(case when dat.mnemonics in ('ДПС МК', 'ДПС МК ГВ', 'ДПС МК КП', 'Р ПП ДПС МК') then t.value else 0 end) dps_mk,
        sum(case when dat.mnemonics in ('ДПС МК ИД', 'ДПС МК ФР', 'Р ПП ДПС МК ИД') then t.value else 0 end) dps_mk_id
     from ops_sharer s
     join back_office.account da on da.owner_sharer_id = s.id
     join back_office.account_type dat on dat.id = da.account_type_id
     join back_office.transaction t on t.debit_account_id = da.id
     /*join back_office.operation o on o.id = t.operation_id*/
     join back_office.account ca on t.credit_account_id = ca.id
     join back_office.account_type cat on cat.id = ca.account_type_id
     where dat.mnemonics in
         ('ОПС', 'ОПС ГВ', 'ОПС КП', 'Р ПП ОПС',
            'ОПС ИД','ОПС ФР', 'Р ПП ОПС ИД',
            'ДПС Г', 'ДПС Г ГВ', 'ДПС Г КП', 'Р ПП ДПС Г',
            'ДПС Г ИД', 'ДПС Г ФР', 'Р ПП ДПС Г ИД',
            'ДПС Л', 'ДПС Л ГВ', 'ДПС Л КП', 'Р ПП ДПС Л',
            'ДПС Л ИД', 'ДПС Л ФР', 'Р ПП ДПС Л ИД',
            'ДПС МК', 'ДПС МК ГВ', 'ДПС МК КП', 'Р ПП ДПС МК',
            'ДПС МК ИД', 'ДПС МК ФР', 'Р ПП ДПС МК ИД')
        and cat.mnemonics in ('Р ОПС', 'Р ОПС ИД')
     group by da.owner_sharer_id
     /*исходная версия без ограничения по дате операции/транзации и проверки на то что операция авторизована*/
union all
    select
        ca.owner_sharer_id sharer_id,
        - sum(t.value) balance,
        - sum(case when cat.mnemonics in ('ОПС', 'ОПС ГВ', 'ОПС КП', 'Р ПП ОПС') then t.value else 0 end) ops,
        - sum(case when cat.mnemonics in ('ОПС ИД','ОПС ФР', 'Р ПП ОПС ИД') then t.value else 0 end) ops_id,
        - sum(case when cat.mnemonics in ('ДПС Г', 'ДПС Г ГВ', 'ДПС Г КП', 'Р ПП ДПС Г') then t.value else 0 end) dps_g,
        - sum(case when cat.mnemonics in ('ДПС Г ИД', 'ДПС Г ФР', 'Р ПП ДПС Г ИД') then t.value else 0 end) dps_g_id,
        - sum(case when cat.mnemonics in ('ДПС Л', 'ДПС Л ГВ', 'ДПС Л КП', 'Р ПП ДПС Л' ) then t.value else 0 end) dps_l,
        - sum(case when cat.mnemonics in ('ДПС Л ИД', 'ДПС Л ФР', 'Р ПП ДПС Л ИД') then t.value else 0 end) dps_l_id,
        - sum(case when cat.mnemonics in ('ДПС МК', 'ДПС МК ГВ', 'ДПС МК КП', 'Р ПП ДПС МК') then t.value else 0 end) dps_mk,
        - sum(case when cat.mnemonics in ('ДПС МК ИД', 'ДПС МК ФР', 'Р ПП ДПС МК ИД') then t.value else 0 end) dps_mk_id
     from ops_sharer s
     join back_office.account ca on ca.owner_sharer_id = s.id
     join back_office.account_type cat on cat.id = ca.account_type_id
     join back_office.transaction t on t.credit_account_id = ca.id
     /*join back_office.operation o on o.id = t.operation_id*/
     join back_office.account da on t.debit_account_id = da.id
     join back_office.account_type dat on dat.id = da.account_type_id
     where cat.mnemonics in
             ('ОПС', 'ОПС ГВ', 'ОПС КП', 'Р ПП ОПС',
            'ОПС ИД','ОПС ФР', 'Р ПП ОПС ИД',
            'ДПС Г', 'ДПС Г ГВ', 'ДПС Г КП', 'Р ПП ДПС Г',
            'ДПС Г ИД', 'ДПС Г ФР', 'Р ПП ДПС Г ИД',
            'ДПС Л', 'ДПС Л ГВ', 'ДПС Л КП', 'Р ПП ДПС Л',
            'ДПС Л ИД', 'ДПС Л ФР', 'Р ПП ДПС Л ИД',
            'ДПС МК', 'ДПС МК ГВ', 'ДПС МК КП', 'Р ПП ДПС МК',
            'ДПС МК ИД', 'ДПС МК ФР', 'Р ПП ДПС МК ИД')    
        and dat.mnemonics in ('Р ОПС', 'Р ОПС ИД')
     group by ca.owner_sharer_id
     /*исходная версия без ограничения по дате операции/транзации и проверки на то что операция авторизована*/
)
insert into report.dia_rops(
id,
dia_register_id,
insurance_number,
    sharer_number,
    death_date,
ops,
ops_id,
dps_l,
dps_l_id,
dps_g,
dps_g_id,
dps_mk,
dps_mk_id,
total,
sharer_id
)
    select
     nextval('report.dia_rops_id_sequence') id,
currval('report.dia_register_id_sequence') dia_register_id,
        i.insurance_number insurance_number,
        s.number sharer_number,
        death_info.death_date ,
         b.ops,
         b.ops_id,
        b.dps_l,
         b.dps_l_id,
        b.dps_g,
        b.dps_g_id,
        b.dps_mk,
        b.dps_mk_id,
        b.balance as total,
        s.id sharer_id
    from
    (select sharer_id, sum(ops), sum(ops_id), sum(dps_g), sum(dps_g_id), sum(dps_l), sum(dps_l_id), sum(dps_mk), sum(dps_mk_id), sum(balance)
    from t_balance_all b
    group by sharer_id) as b(sharer_id, ops, ops_id, dps_g, dps_g_id, dps_l, dps_l_id, dps_mk, dps_mk_id, balance)
    join ourpension.sharer s on s.id = b.sharer_id
    join ourpension.individual i on s.individual_id = i.id
    join lateral (
        select a.death_date
        from ourpension.application_death_info a
        where a.individual_id = s.individual_id
            and a.accepted is not null
            and a.canceled is null
            and a.rejected is null
        order by a.version desc
        limit 1
    )death_info on true
    where b.balance <>0