---

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(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,
  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 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     #if ($filterShowList)
    true
#else
    false
#end


#if ($id != -1)
    and ct.id = :id
#end


#if ($filterNumber && !$filterNumber.isEmpty())
    and ct.number ilike
        replace(
            replace(
                trim(:filterNumber),
                '\',
                '\\'
            ),
            '/',
            '\/'
        ) || '%'
#end


#if ($filterPensionScheme && !$filterPensionScheme.isEmpty())
    and p.number ilike trim(:filterPensionScheme) || '%'
#end


#if ($filterStartDateContract && !$filterStartDateContract.isEmpty())
    and ct.date >= :filterStartDateContract
#end


#if ($filterEndDateContract && !$filterEndDateContract.isEmpty())
    and ct.date <= :filterEndDateContract
#end


#if ($filterInsuranceNumber && !$filterInsuranceNumber.isEmpty())
    and i.insurance_number like trim(:filterInsuranceNumber) || '%'
#end


#if ($filterInvestorLastName && !$filterInvestorLastName.isEmpty())
    and i.last_name ilike trim(:filterInvestorLastName) || '%'
#end
#if ($filterInvestorShortName && !$filterInvestorShortName.isEmpty())
    and e.short_name ilike '%' || trim(:filterInvestorShortName) || '%'
#end
#if ($filterInvestorFirstName && !$filterInvestorFirstName.isEmpty())
    and i.first_name ilike trim(:filterInvestorFirstName) || '%'
#end
#if ($filterInvestorMiddleName && !$filterInvestorMiddleName.isEmpty())
    and i.middle_name ilike trim(:filterInvestorMiddleName) || '%'
#end
#if ($filterInn && !$filterInn.isEmpty())
    and
    (
        i.inn like trim(:filterInn) || '%'
        or e.inn like trim(:filterInn) || '%'
    )
#end
-- ============================================================================
-- ВАЖНО.
-- Было:
--     coalesce(ct.service_type, 'OPS') = :filterServiceType
-- Для PDS это заставило planner идти по contract_pkey и отфильтровать
-- ~13.55 млн строк.
-- Ниже та же семантика:
-- NULL считается OPS, как и раньше.
-- Но обычный service_type теперь виден planner напрямую.
-- ============================================================================
#if ($filterServiceType && !$filterServiceType.isEmpty())
    and
    (
        ct.service_type = :filterServiceType
        or
        (
            ct.service_type is null
            and :filterServiceType = 'OPS'
        )
    )
#end
#if ($filterInvestorFullName && !$filterInvestorFullName.isEmpty())
    and concat(
        i.last_name,
        ' ',
        i.first_name,
        ' ',
        i.middle_name
    ) ilike '%' || trim(:filterInvestorFullName) || '%'
#end
#if ($filterStatus && !$filterStatus.isEmpty())
    and cs.name ilike trim(:filterStatus) || '%'
#end
#if ($filterProgram && !$filterProgram.isEmpty())
    and pp3.name ilike trim(:filterProgram) || '%'
#end
#if ($filterProduct && !$filterProduct.isEmpty())
    and pp2.name ilike trim(:filterProduct) || '%'
#end
#if ($filterContractType && !$filterContractType.isEmpty())
    and
    (
        :filterContractType = 3
        and ct.service_type = 'PDS'

        or :filterContractType = 2
        and ct.service_type = 'NPO'
        and ct.entity_id is not null

        or :filterContractType = 1
        and ct.service_type = 'NPO'
        and ct.individual_id is not null

        or :filterContractType = 0
        and ct.service_type = 'OPS'
    )
#end


#if ($filterNonstandardContract && !$filterNonstandardReason.isEmpty())
    and ct.nonstandard_reason_id = :filterNonstandardReason
#end
-- ============================================================================
-- Фильтр по самой ранней дате действия.
-- ============================================================================
#if ($filterStartDateAction && !$filterStartDateAction.isEmpty())
    and csa.status_date >= cast(:filterStartDateAction as timestamp)
#end
#if ($filterEndDateAction && !$filterEndDateAction.isEmpty())
    and csa.status_date <= cast(:filterEndDateAction as timestamp)
#end