---

WITH cte_s_act as
(
    SELECT 
        csh.contract_id,
        MIN(csh.status_date) AS status_date
    FROM ourpension.contract_statuses_history csh
    WHERE csh.code IN ( 'CA0', 'HA0', 'MA0' )
    GROUP BY csh.contract_id
)
SELECT
  ct.id as id,
  ct.id as contractId,
  '<a target="_blank" href="view?viewCode=7HPZX8vF&id=' || ct.id|| '"target="_blank">' || ct.number || '</a>' as number,
  to_char(ct.date, 'DD.MM.YYYY') as date,
  case 
  when p.lifetime_pension='true' 
   then '<span data-toggle="tooltip" title="' || p.name|| '" class="label label-success">' || p.number || '</span>'
   else '<span data-toggle="tooltip" title="' || p.name|| '" class="label label-primary">' || p.number || '</span>'
end as schemeNumber,
  '<span data-toggle="tooltip" title="' || coalesce(uat.activity_name, 'ОПС') || '" class="label label-success">' || coalesce(uat.activity_rus_type, 'ОПС') || '</span>' as clientType,
  case 
  when ct.entity_id is null
    then '<a target="_blank" href="view?viewCode=Client_list&id=' || ct.individual_id || '">' || i.last_name || ' ' || i.first_name || coalesce(' ' || i.middle_name, '') || '</a>'
  else '<a target="_blank" href="view?viewCode=NCIuIMAW&id=' || ct.entity_id || '">' || e.short_name || '</a>'
end as investor,
  '<span data-toggle="tooltip" title="' || to_char(
  case when t.termination_date is not null or ct.close_date is not null
  then coalesce(t.termination_date, ct.close_date)
  else ct.date
  end, 'DD.MM.YYYY') || '" class="label label-primary">' || cs.name || '</span>' as contractLifecycle,
  coalesce(a.last_name,'') || ' ' || left(coalesce(a.first_name, ''), 1) || '.' || coalesce(left(a.middle_name, 1) || '.', '') as agent,
  case
 when ct.service_type = 'PDS' then 'ДДС'
 when ct.service_type = 'NPO' and ct.individual_id is not null then 'ИПП'
 when ct.service_type = 'NPO' and ct.entity_id is not null then 'КПП'
 else ''
end as contractType,
  pp3."name" as program,
  pp2."name" as product,
  ct.number as contractNumber,
  cr.name as closeReasone,
  to_char(t.termination_date, 'DD.MM.YYYY') as terminationDate,
  tr.name as terminationReason,
  to_char(ct.create_date, 'DD.MM.YYYY') as createDate,
  to_char(coalesce(ct.close_date,t.termination_date,ct.date), 'dd.MM.yyyy') as state_date,
  pp3."name" || coalesce('(' || pp2."name" || ')','') as programProduct,
  cs.name as state,
  ct.client_type as clientTypeId,
  coalesce(uat.activity_rus_type, 'ОПС') as serviceType,
  sc.name as saleChannelName,
  fe.name as fund_name,
  i.last_name || ' ' || i.first_name || coalesce(' ' || i.middle_name, '') as investor_fio,
  ct.service_type as service_type 
FROM ourpension.contract ct
inner join ourpension.current_contract_status(ct.id) ccs
        on true
inner join ourpension.contract_statuses cs 
        on cs.code = ccs.c_code
inner join ourpension.agent a 
        on a.id = ct.agent_id
	




left join ourpension.uv_activity_type uat 
			 on uat.activity_eng_type = ct.service_type 
			and uat.client_type = ct.client_type 
left join lateral (
		 select contract_id, termination_reason_id, termination_date
 		 from (                 
           select tc.contract_id,
                  t.contract_termination_reason_id as termination_reason_id,
                  t.date as termination_date,
                  row_number() over (partition by tc.contract_id order by t.date, t.version desc) num
           from ourpension.termination_contract tc
           join ourpension.termination t 
						 on tc.termination_id = t.id
	         where tc.contract_id = ct.id
					) tt where tt.num = 1
        ) t on true
left join ourpension.entity e 
			 on e.id = ct.entity_id
left join ourpension.individual i 
			 on i.id = ct.individual_id
left join ourpension.pension_scheme p 
			 on p.id = ct.scheme_id
left join ourpension.contract_close_reason cr 
			 on cr.id = ct.contract_close_reason_id
left join ourpension.contract_termination_reason tr 
			 on  tr.id = t.termination_reason_id
left join ourpension.information_source sc 
			 on sc.id = ct.sale_channel_id
left join ourpension.fund_branch fb 
			 on fb.id = a.fund_branch_id 
left join ourpension.fund f 
			 on f.id = fb.fund_id
left join ourpension.entity fe 
			 on fe.id = f.entity_id 
left join ourpension.contract_params cpp 
			 on cpp.contract_id = ct.id 
left join prod_line.pens_product pp3 
			 on pp3.id = ct.product_id
left join prod_line.table_param_pl_pens_program tpp 
			 on tpp.pens_program_id = pp3.id
left join prod_line.pens_product_param_pl pppp 
			 on pppp.id = tpp.pens_product_param_pl_id
left join prod_line.pens_product pp2 
			on pp2.id = pppp.pens_product_id










select
i.id as smev_register_id,
    to_char(now(), 'DD.MM.YYYY') as date_doc,
-- атрибуты Файл
'UT_ZAPRSTRORGNPF_' || i.id || '_' || to_char(i.birth_date,'YYYYMMDD') || '_' || replace(cast(gen_random_uuid() as text),'-','') as file_id,
'5.02' as vers_form,
'КОНТУР-ЭКСТЕРН, ВЕРСИЯ 16.0' as vers_prog,

-- атрибуты Документ
coalesce(fc.const_value, '7702') as kod_no,
to_char(now(), 'DD.MM.YYYY') as doc_date,
'1184042' as knd,

-- СвНП/НПЮЛ
f.kpp as org_kpp,
f.inn as org_inn,
coalesce(f.name_full, f.name_in_pfr) as org_name,



(
    select     
                            cast ((serial_number) as varchar (10))
                         from dev.unique_number where code = 'smev_register'
) as req_number
from ourpension.individual i
left join ourpension.fund f on i.fund_id = f.id

left join ourpension.our_funds_constants fc on f.id = fc.fund_id and fc.const_name = 'kod_no'
--left join ourpension.agent a on a.id = i.agent_id
where i.id = :id