---

WITH step1 as ( -- группировка и без умерших ФЛ
	select distinct on (np.contract_id, np.numb_of_monts, np.payment_type_code)
		np.contract_id, 
		np.numb_of_monts,
		np.effective_date,
		np.ops_payment_decision_id as decision_id,
		np.payment_type_code,
		-- np.numb_of_monts - lag(np.numb_of_monts) over(partition by np.contract_id order by np.numb_of_monts asc) as payment_gap,
		-- lag(np.effective_date) over(partition by np.contract_id order by np.numb_of_monts asc) as prev_effective_date,
		c.number as contract_number,
		i.id as individual_id,
		i.last_name,
		i.first_name,
		i.middle_name,
		i.last_name || ' ' || i.first_name || ' ' || coalesce(i.middle_name, '') as fio, 
		i.insurance_number
	from back_office.ops_number_of_payments np
	join ourpension.contract c on c.id = np.contract_id
	join ourpension.sharer sh on sh.contract_id = np.contract_id
	join ourpension.individual i on i.id = sh.individual_id
	join ourpension.ops_pension_assignment_application pa on pa.sharer_id = sh.id
	left join ourpension.application_death_info adi on adi.individual_id = i.id
	where np.effective_date >= cast(:filter_period_date_from as date) -- - interval '2 month' -- приостановка выплаты могла быть на границе периода
		and np.effective_date <= cast(:filter_period_date_to as date)
		and pa.accepted is not null and pa.rejected is null and pa.canceled is null
		and adi.id is null -- без умерших ФЛ
	order by np.contract_id, np.payment_type_code, np.numb_of_monts, np.effective_date desc
),
step2 as ( -- разница между крайней записью на выбранный период и предыдущей записью
	select 
		*,
		numb_of_monts - lag(numb_of_monts) over(partition by contract_id, payment_type_code order by numb_of_monts asc) as payment_gap
	from step1
	-- where (payment_gap > 1 or payment_gap < 0) 
)
SELECT
  s.contract_id as contract_id,
  s.insurance_number as insurance_number,
  s.individual_id as individual_id,
  s.last_name as last_name,
  s.first_name as first_name,
  s.middle_name as middle_name,
  '<a target="_blank" href="view?viewCode=7HPZX8vF&id='|| s.contract_id ||'" target="_blank">' || s.contract_number || '</a>' as number,
  '<a target="_blank" href="view?viewCode=Client_list&id=' || s.individual_id || '"> ' || s.fio || '</a>' as fio,
  s.payment_type_code as payment_type_code,
  s.payment_gap as payment_gap,
  to_char(s.effective_date, 'dd.MM.yyyy') as effective_date,
  to_char(ds.suspension_date, 'dd.MM.yyyy') as suspension_date,
  to_char(dr.renewal_date, 'dd.MM.yyyy') as renewal_date,
  coalesce(rt_lat.has_return, 'Нет') as has_return,
  coalesce(sp_lat.has_second_payment, 'Нет') as has_second_payment 
FROM step2 s
left join back_office.ops_payment_decision_renewal dr on dr.ops_payment_decision_id = s.decision_id
	-- возобновление выплат
	and (dr.renewal_date >= cast(:filter_period_date_from as date) -- - interval '2 month'
	and dr.renewal_date <= cast(:filter_period_date_to as date))
	or dr.renewal_date is null
left join back_office.ops_payment_decision_suspension ds on ds.ops_payment_decision_id = s.decision_id
	-- приостановление выплат
	and (ds.suspension_date >= cast(:filter_period_date_from as date) -- - interval '2 month'
	and ds.suspension_date  <= cast(:filter_period_date_to as date))
	or ds.suspension_date is null
left join lateral (
	select 'Да' as has_second_payment
	from back_office.operation o
  join back_office.operation_type ot  on ot.id = o.operation_type_id
  join back_office.ops_payment_register r on r.id = o.ops_payment_register_id
  join back_office.ops_payment_register_decision rd on rd.ops_payment_register_id = r.id 
  join back_office.ops_payment_decision d on d.id = rd.ops_payment_decision_id
  join ourpension.sharer sh on sh.contract_id = s.contract_id
  join ourpension.ops_pension_assignment_application pa on pa.sharer_id = sh.id
  where d.ops_pension_assignment_application_id = pa.id
  	and ot.code = 'SECOND_PAYMENT'
	  and o.approved is not null 
	  and o.deleted is null 
	  and o.effective_date >= cast(:filter_period_date_from as date) -- - interval '2 month'
	  and o.effective_date <= cast(:filter_period_date_to as date)
	  and pa.accepted is not null and pa.rejected is null and pa.canceled is null
	limit 1
) sp_lat on true
left join lateral (
	select 'Да' as has_return
	from back_office.ops_payment_return rt 
	where rt.ops_payment_register_decision_id = s.decision_id
	limit 1
) rt_lat on true
WHERE case  
when :id = -1 then true
when :id is null then false 
else s.contract_id = :id
end

and (s.payment_gap > 1 or s.payment_gap < 0)
-- and (:filter_period_date_from is null or :filter_period_date_from = '' or s.effective_date >= cast(:filter_period_date_from as date))
-- and (:filter_period_date_to is null or :filter_period_date_to = '' or s.effective_date <= cast(:filter_period_date_to as date))