---

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="' || spl.scheme_number || '" class="label label-success">' || spl.scheme_number || '</span>'
   else '<span data-toggle="tooltip" title="' || spl.scheme_number || '" class="label label-primary">' || spl.scheme_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(termination_history.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,
  death_info.id as death_info_id,
  ct.service_type as service_type,
  fsg.name_in_pfr as fund_signed,
  fsr.name_in_pfr as fund_resource 
FROM ourpension.contract as ct

inner join ourpension.current_contract_status(ct.id) as ccs
    on true

inner join ourpension.contract_statuses as cs
    on cs.code = ccs.c_code

inner join ourpension.agent as a
    on a.id = ct.agent_id


-- ============================================================================
-- Классификация.
-- Логика оставлена как была: берём последнюю запись.
-- ============================================================================



-- ============================================================================
-- Номер участника.
-- ============================================================================



-- ============================================================================
-- Дата действия.
--
-- Раньше ради этого вычислялся min(status_date) по ВСЕЙ истории.
--
-- Теперь для конкретного договора просто берём самую раннюю дату
-- CA0 / HA0 / MA0.
--
-- Это та же бизнес-логика:
--     min(status_date)
-- но без глобального group by на ~13.7 млн строк.
-- ============================================================================



left join ourpension.uv_activity_type as uat
    on uat.activity_eng_type = ct.service_type
   and uat.client_type = ct.client_type


-- ============================================================================
-- Последнее/первое прекращение ровно по исходной сортировке:
-- order by t.date, t.version desc.
--
-- В исходнике row_number partition by contract_id был внутри запроса,
-- где contract_id уже фиксирован. Поэтому partition/window там ничего
-- полезного не давал.
-- ============================================================================

left join lateral
(
    select
        tc.contract_id,
        trm.contract_termination_reason_id as termination_reason_id,
        trm.date as termination_date
    from ourpension.termination_contract as tc

    join ourpension.termination as trm
        on trm.id = tc.termination_id

    where tc.contract_id = ct.id

    order by
        trm.date,
        trm.version desc

    limit 1
) as t
    on true


left join ourpension.entity as e
    on e.id = ct.entity_id

	left join lateral
(
    select
        max(h.status_date) as termination_date
    from ourpension.contract_statuses_history as h
    where h.contract_id = ct.id
      and h.code in ('DA0', 'IA0', 'NA0')
) as termination_history
    on true
	
left join ourpension.individual as i
    on i.id = ct.individual_id

left join ourpension.pension_scheme as p
    on p.id = ct.scheme_id

left join ourpension.contract_close_reason as cr
    on cr.id = ct.contract_close_reason_id

left join ourpension.contract_termination_reason as tr
    on tr.id = t.termination_reason_id

left join ourpension.information_source as sc
    on sc.id = ct.sale_channel_id

left join ourpension.fund_branch as fb
    on fb.id = a.fund_branch_id

left join ourpension.fund as f
    on f.id = fb.fund_id

left join ourpension.entity as fe
    on fe.id = f.entity_id

left join ourpension.contract_params as cpp
    on cpp.contract_id = ct.id

left join prod_line.pens_product as pp3
    on pp3.id = ct.product_id

left join prod_line.table_param_pl_pens_program as tpp
    on tpp.pens_program_id = pp3.id

left join prod_line.pens_product_param_pl as pppp
    on pppp.id = tpp.pens_product_param_pl_id

left join prod_line.pens_product as pp2
    on pp2.id = pppp.pens_product_id

left join ourpension.fund as fsg
    on fsg.id = ct.sign_fund_id

left join ourpension.fund as fsr
    on fsr.id = ct.fund_id


-- ============================================================================
-- Информация о смерти.
-- Здесь бизнес-логику не менял.
-- Для PDS эта lateral вообще не исполняется — planner это уже показал.
-- ============================================================================

left join lateral
(
    select
        adi.id,
        adi."number",
        adi.death_date

    from ourpension.application_death_info as adi

    join ourpension.individual as di
        on di.id = adi.individual_id

    join ourpension.agent as da
        on da.id = adi.agent_id

    join ourpension.death_info_certificate as dc
        on dc.code = adi.death_info_certificate_code

    join ourpension.contract_statuses_history as history
        on history.contract_id = ct.id

    where adi.accepted is not null
      and adi.canceled is null
      and adi.rejected is null
      and adi.individual_id = ct.individual_id
      and history.code = 'CA0'
      and adi.death_date <= history.status_date
      and ct.service_type = 'OPS'

) as death_info
    on true


-- ============================================================================
-- Актуальный продукт договора.
-- Логика оставлена без изменений.
-- ============================================================================

left join lateral
(
    select
        products.id,
        products.pens_scheme_accum_stage_id as scheme_id,
        ps.brief as scheme_number

    from ourpension.contract_product_history as history

    inner join prod_line.pens_product as products
        on products.id = history.pens_product_id

    left join prod_line.pens_scheme as ps
        on ps.id = products.pens_scheme_accum_stage_id

    where history.contract_id = ct.id
      and current_timestamp between
            history.begin_date
            and coalesce(history.end_date, 'infinity')

    limit 1

) as spl
    on true
WHERE     false