---

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




update dev.unique_number 
set serial_number = serial_number+1
where code = 'dia_rops'


UPDATE report.dia_register SET register_kind = 'OPS', load_end = clock_timestamp() WHERE id = currval('report.dia_register_id_sequence')