---

WITH pens as (
    select 
		sh.id as sharer_id,
		c.number as contract_number, -- номер договора
		i.insurance_number as snils, -- снилс
		i.birth_date as birth_date, -- дата рождения
		case i.sex when 'F'
				then 'Женский'
				else 'Мужской'
		end as sex, -- пол
		opaa.date as date_of_application, -- дата заявления
		opaa.appeal_date as appeal_date, -- дата обращения
		opaa.pension_date as pension_date, -- дата назначения пенсии
		pc.retirement_date as retirement_date, --pc.retirement_date as retirement_date, -- дата назначения страховой
		canceled_deci.date as canceled_date, -- дата решения о прекращении выплат
		opt.group_code as pension_type, -- Тип пенсии
		case when suspension_deci.id is null
				then 'Нет'
				else 'Да'
		end as suspension_date, -- Была приостановка выплат пенсии
		case when canceled_deci.id is null
				then 'Нет'
				else 'Да'
		end as canceled_fact, -- Былао Прекращение выплат пенсии
		renewal_deci.date as renewal_date, -- Дата решения о возобновлении выплат
		adi.death_date as death_date, -- Дата смерти
		onop_max.max_months, -- Количество выплат
		ld.pension_value -- Размер пенсии на отчетную дату   
		,bln.v_rezerv_with_id, -- Размер остатка в выплатном резерве с учетом ИД ВР
		bln.id_vr_amount, -- Размер остатка ИД ВР
		bln.total_sum, -- Остаток по всем резервам общий
		bln.group_sv, -- ДСВ   
		bln.group_dsv,  -- СОФ
		bln.group_sof,  -- МК
		bln.group_mk,
		ferst_deci.pens_t	
	from ourpension.contract c
	join ourpension.sharer sh on sh.contract_id = c.id and c."service_type" = 'OPS' --and c.id = 9575012
	join lateral (
		select 1 
		FROM ourpension.sharer_statuses_history
		where cast(:reporting_date as date) between status_date and status_end_date and code = 'CA0' and sharer_id = sh.id
	) status on true
	join ourpension.ops_pension_assignment_application opaa on sh.id = opaa.sharer_id and opaa.accepted is not null and opaa.canceled is null and opaa.rejected is null
	left join ourpension.pfr_certificate pc on opaa.pfr_certificate_id = pc.id
	join lateral ( /*Первое решение об установлении и получаем с него кол-во выплат*/
		select 
			coalesce(opdl.duration, opdla.duration, opdft.duration) as pens_t, 
			opd.id as id_deci,
			opd.decision_payment_type_code
		from back_office.ops_payment_decision opd 
		left join back_office.ops_payment_decision_lifetime opdl on opd.id = opdl.ops_payment_decision_id
		left join back_office.ops_payment_decision_lifetime_all opdla on opd.id = opdla.ops_payment_decision_id
		left join back_office.ops_payment_decision_fixed_term opdft on opd.id = opdft.ops_payment_decision_id
		where opaa.id = opd.ops_pension_assignment_application_id
				and opd.decision_type_code = 'УСТН'
				and opd.accepted is not null
				and opd.date <= :reporting_date 
		order by opd.date ASC
		limit 1
	) ferst_deci on true
	join (
		select t.contract_id, t.max_months
		from (
			select
				contract_id,
				numb_of_monts as max_months,
				row_number() over(partition by contract_id order by effective_date DESC) as rn
				--max(numb_of_monts) as max_months
			from back_office.ops_number_of_payments
			where effective_date <= cast(:reporting_date as date)
		) t
		where t.rn = 1
			--group by contract_id
	) onop_max on c.id = onop_max.contract_id
	join ourpension.decision_payment_type opt on opt.code = ferst_deci.decision_payment_type_code 
		and case when :pension_type = '02'
					then opt.code = '02'
				 when :pension_type = '04'
					then opt.code in ('03', '04')
				else opt.code in ('02', '03', '04')
			end
		
	join lateral ( /*Последнее решение об УСТН или КОРР с которого берем актуальное значение выплаты*/
			select coalesce(opdl.pension_value, opdla.pension_value, opdft.pension_value) as pension_value,
					opd.date
			from back_office.ops_payment_decision opd        
			left join back_office.ops_payment_decision_lifetime opdl on opd.id = opdl.ops_payment_decision_id
			left join back_office.ops_payment_decision_lifetime_all opdla on opd.id = opdla.ops_payment_decision_id
			left join back_office.ops_payment_decision_fixed_term opdft on opd.id = opdft.ops_payment_decision_id
			where opaa.id = opd.ops_pension_assignment_application_id
					and opd.decision_type_code in ('УСТН','КОРР')
					and opd.accepted is not null
					and opd.date <= :reporting_date 
			order by opd.date desc
			limit 1
	) ld on true
	left join lateral ( /*Последнее решение об ПРЕК с которого берем дату*/
			select opd.id, opd.date
			from back_office.ops_payment_decision opd        
			where opaa.id = opd.ops_pension_assignment_application_id
					and opd.decision_type_code = 'ПРЕК'
					and opd.accepted is not null
					and opd.date <= :reporting_date 
			order by opd.date desc
			limit 1
	) canceled_deci on true
	left join lateral ( /*Последнее решение об ВОЗБ с которого берем дату*/
			select opd.id, opd.date
			from back_office.ops_payment_decision opd        
			where opaa.id = opd.ops_pension_assignment_application_id
					and opd.decision_type_code = 'ВОЗБ'
					and opd.accepted is not null
					and opd.date <= :reporting_date 
			order by opd.date desc
			limit 1
	) renewal_deci on true
	left join lateral ( /*Последнее решение об ВОЗБ с которого берем дату*/
			select opd.id as id
			from back_office.ops_payment_decision opd        
			where opaa.id = opd.ops_pension_assignment_application_id
					and opd.decision_type_code = 'ПРОС'
					and opd.accepted is not null
					and opd.date <= :reporting_date 
			order by opd.date desc
			limit 1
	) suspension_deci on true
	join ourpension.individual i on sh.individual_id = i.id
	left join lateral ( /*Дата смерти*/
		select adi.death_date
		from ourpension.application_death_info adi
		where i.id = adi.individual_id and adi.accepted is not null and adi.canceled is null and adi.rejected is null
	) adi on true
	join lateral ( /*Суммы по группам мнемоник*/
		select 
			sum(case 
					when (opt.pfr_code = 'НП' or opt.pfr_code = 'НОПС') and atg.mnemonics = 'Р(П)' and at.mnemonics in ('Р(П)', 'Р(П) ИД') 
								then b.total_credit - b.total_debit
						when opt.pfr_code = 'СПВ' and atg.mnemonics = 'Р(С)'  and at.mnemonics in ('Р(С)', 'Р(С) ИД') 
								then b.total_credit - b.total_debit
						else 0
					end
			) as v_rezerv_with_id,
			sum(case when (opt.pfr_code = 'НП' or opt.pfr_code = 'НОПС') and atg.mnemonics = 'Р(П) ИД'
							then b.total_credit - b.total_debit
					when opt.pfr_code = 'СПВ' and atg.mnemonics = 'Р(С) ИД'
							then b.total_credit - b.total_debit
					else 0
					end
			) as id_vr_amount, -- Размер остатка ИД ВР
			sum(b.total_credit - b.total_debit) as total_sum,
			sum(case 
					when (opt.pfr_code = 'НП' or opt.pfr_code = 'НОПС')
								and atg.mnemonics = 'Р(П)'
								and at.mnemonics in ('Р(П) ОПС', 'Р(П) ОПС ИД')
						then b.total_credit - b.total_debit --b.total_debit
						when opt.pfr_code = 'СПВ'
								and atg.mnemonics = 'Р(С)'
								and at.mnemonics in ('Р(С) ОПС', 'Р(С) ОПС ИД')
							then b.total_credit - b.total_debit --b.total_debit
							else 0
					end) as group_sv, -- ДСВ
			sum(case when (opt.pfr_code = 'НП' or opt.pfr_code = 'НОПС')
							and atg.mnemonics = 'Р(П)'
							and at.mnemonics in ('Р(П) ДПС Л', 'Р(П) ДПС Л ИД')
							then b.total_credit - b.total_debit --b.total_debit
					when opt.pfr_code = 'СПВ'
							and atg.mnemonics = 'Р(С)'
							and at.mnemonics in ('Р(С) ДПС Л', 'Р(С) ДПС Л ИД')
							then b.total_credit - b.total_debit --b.total_debit
					else 0
			end) as group_dsv,  -- СОФ
			sum(case when (opt.pfr_code = 'НП' or opt.pfr_code = 'НОПС')
							and atg.mnemonics = 'Р(П)'
							and at.mnemonics in ('Р(П) ДПС Г', 'Р(П) ДПС Г ИД')
							then b.total_credit - b.total_debit --b.total_debit
					when opt.pfr_code = 'СПВ'
							and atg.mnemonics = 'Р(С)'
							and at.mnemonics in ('Р(С) ДПС Г', 'Р(С) ДПС Г ИД')
							then b.total_credit - b.total_debit
					else 0
			end) as group_sof,  -- МК
			sum(case when (opt.pfr_code = 'НП' or opt.pfr_code = 'НОПС')
							and atg.mnemonics = 'Р(П)'
							and at.mnemonics in ('Р(П) ДПС МК', 'Р(П) ДПС МК ИД')
							then b.total_credit - b.total_debit
					when opt.pfr_code = 'СПВ'
							and atg.mnemonics = 'Р(С)'
							and at.mnemonics in ('Р(С) ДПС МК', 'Р(С) ДПС МК ИД')
							then b.total_credit - b.total_debit
					else 0
			end) as group_mk
		from back_office.account_type_group atg
		join back_office.account_type at on at.account_type_group_id = atg.id
		join back_office.account a on at.id = a.account_type_id and a.owner_sharer_id = sh.id
		join back_office.balance b on b.account_id = a.id
		where atg.mnemonics in ('Р(П)', 'Р(С)') 
			and :reporting_date between b.start_date and b.end_date
		) bln on true
)
SELECT
  to_char(p.birth_date, 'DD.MM.YYYY') as birth_date,
  p.contract_number as contract_number,
  p.snils as snils,
  p.sex as sex,
  p.pension_type as pension_type,
  to_char(p.date_of_application, 'DD.MM.YYYY') as date_of_application,
  to_char(p.retirement_date, 'DD.MM.YYYY') as retirement_date,
  p.canceled_fact as canceled_fact,
  to_char(p.canceled_date, 'DD.MM.YYYY') as canceled_date,
  to_char(p.pension_date, 'DD.MM.YYYY') as pension_date,
  to_char(p.renewal_date, 'DD.MM.YYYY') as renewal_date,
  p.suspension_date as suspension_date,
  to_char(p.death_date, 'DD.MM.YYYY') as death_date,
  to_char(p.appeal_date, 'DD.MM.YYYY') as appeal_date,
  p.max_months as numb_of_months,
  p.pension_value as pension_value,
  p.pens_t as duration,
  p.v_rezerv_with_id as v_rezerv_with_id,
  p.id_vr_amount as id_vr_amount,
  p.total_sum as total_sum,
  p.group_sv as group_sv,
  p.group_dsv as group_dsv,
  p.group_mk as group_mk,
  p.group_sof as group_sof 
FROM pens p