---

1)
with b (id, balance) as (
    select
    a.owner_contract_id,
    sum(b.total_credit - b.total_debit)
    from back_office.account a
        join back_office.account_type at on a.account_type_id = at.id
        and at.mnemonics in ('СПС', 'СПС ИД')
        join back_office.balance b on a.id = b.account_id
        and current_date between b.start_date and b.end_date
    where a.owner_contract_id = :id
    group by a.owner_contract_id
),
termination as (
    select 
        tr.name as termination_reason, 
        t.date as termination_date,
        row_number() over (partition by tc.contract_id order by t.date, t.version desc) as num
    from ourpension.termination_contract tc 
        join ourpension.termination t 
            left join ourpension.contract_termination_reason tr 
            on tr.id = t.contract_termination_reason_id
        on tc.termination_id = t.id
    where tc.contract_id = :id
),
s as(
    select         
        his.strategy_name, 
        c."service_type",
        c.close_date, 
        c.effective_date, 
        c.client_type, 
        c.account_management_form,
        case c.account_management_form 
            when 'PERSONAL' then 'Именная'
            when 'JOINT' then 'Солидарная'
        end as account_management_form_text,
        coalesce(b.balance, 0) as curr_state,
        case
            when c.client_type = 0 and c.service_type = 'NPO' then 'НПО индивидуальный'
            when c.service_type = 'NPO' then 'НПО корпоративный'
            else 'ОПС'
        end as client_type_text,
        r.name as close_reason, 
        c.invest_type, 
        c.death_table_query_datasource_file_id, 
        c.pension_rule_id,
        c.periodicity_payment_id, 
        c.periodicity_payout_id,
        replace(dt.name,'.'||dt.extension, '') as death_table_name,
        payment.name as periodicity_payment, payout.name as periodicity_payout,
        'Пенсионные правила редакции № '||pr.edition_number||' от ' 
            ||to_char(pr.date_reg, 'dd.MM.yyyy') as pension_rule,
        pr.edition_number,
        c.min_year_income, 
        c.min_tariff_rate, 
        c.k1,
        c.inheritance_type_code, it.name as inheritance_type_text,
        c.change_inheritance_type_by_sharer,
        case c.change_inheritance_type_by_sharer
            when true then 'Разрешено'
            when false then 'Запрещено'
        end as change_inheritance_type_by_sharer_text,
        c.postaccrual_income_action, 
        c.pension_deposit_value,
        trim(to_char(c.pension_deposit_value, '99 999 999 999 999 990D99')) as pension_deposit_value_formatted,
        c.entity_id,
        c.individual_id as contract_individual_id,
        sh.individual_id as sharer_individual_id,
        cp.min_annual_income_period,
        cp.min_annual_savings_income,
        t.termination_date,
        t.termination_reason,
        e.enum_value as nonstandard_reason,
        c.nonstandard_reason_id,
        urd.id as uorls_document_id,
        urd.file_name as uorls_document_file,
		lf.id AS contract_template_id,
		lf.code AS contract_template_code
    from ourpension.contract c
        left join b on b.id = c.id
        left join ourpension.contract_params cp on cp.contract_id = c.id
			  LEFT JOIN ourpension.landscape_form lf ON lf.id = cp.form_id
        left join ourpension.contract_close_reason r on c.contract_close_reason_id = r.id
        left join dev.query_datasource_file dt on dt.id=c.death_table_query_datasource_file_id
        left join ourpension.periodicity payment on payment.id = c.periodicity_payment_id
        left join ourpension.periodicity payout on payout.id = c.periodicity_payout_id
        left join ourpension.pension_rule pr on pr.id = c.pension_rule_id
        left join ourpension.inheritance_type it on c.inheritance_type_code = it.code
        left join ourpension.sharer sh on sh.contract_id = c.id and c.client_type = 0
        left join termination t on t.num =1
                left join ourpension.enums e on c.nonstandard_reason_id = e.id
                left join ourpension.enums_name en on e.enums_name_id = en.id and en.enum_name = 'ПричиныНестандарта'
        left join lateral (
            select 
                h.begin_date,
                s."name" as strategy_name
            from ourpension.invest_strategy_history h
                inner join prod_line.hb_invest_strategy s on h.strategy_id = s.id
            where h.contract_id = c.id
            order by h.begin_date desc
            limit 1
        ) his on true 
        left join lateral (
            select urd.id
                ,urd.file_name
            from back_office.changed_snils cs
            left join report.uorls_register_document urd on
                cs.uorls_register_document_id = urd.id
            where cs.contract_id = c.id
            order by cs.change_date desc
            limit 1
        ) urd on true
         LEFT JOIN LATERAL (
            SELECT 
                pl_schemas.index_scheme as scheme_number,                
                history.pens_scheme_id as scheme_id                
            FROM ourpension.contract_scheme_history history
                INNER JOIN prod_line.pens_scheme pl_schemas 
                ON pl_schemas.id = history.pens_scheme_id
            WHERE history.contract_id = c.id
                AND CURRENT_TIMESTAMP BETWEEN history.begin_date AND COALESCE( history.end_date, 'infinity' )
            LIMIT 1
        ) ls 
        ON true 
    where c.id = :id
)
SELECT
    s.account_management_form,
    s.account_management_form_text,
    s.change_inheritance_type_by_sharer_text,
    s.client_type, 
    s.client_type_text,
    s.close_date,
    s.close_reason,
    s.contract_individual_id,
    trim(to_char(s.curr_state, '99 999 999 999 999 990D99')) as curr_state,
    s.death_table_name,
    s.death_table_query_datasource_file_id,
    s.edition_number,
    s.effective_date,
    s.entity_id,
    s.inheritance_type_code,
    s.inheritance_type_text,
    s.invest_type,
    s.k1,
    s.min_annual_income_period as mgd_accumulation,
    s.min_annual_savings_income as mgd_payments,
    s.min_tariff_rate,
    trim(to_char(s.min_tariff_rate, '99 999 999 999 999 990D99')) as min_tariff_rate_formatted,
    s.min_year_income,
    s.nonstandard_reason,
    s.nonstandard_reason_id,
    s.postaccrual_income_action,
    s.pension_deposit_value,
    s.pension_deposit_value_formatted,
    s.pension_rule, 
    s.pension_rule_id,
    s.periodicity_payment, 
    s.periodicity_payment_id,
    s.periodicity_payout, 
    s.periodicity_payout_id,
		s.contract_template_id,
		s.contract_template_code,
    case s.postaccrual_income_action
        when 'ONE_TIME_PAYMENT' then 'Дополнительная разовая выплата пенсии в размере ИД'
        when 'RECALCULATION' then 'Перерасчет размера выплачиваемой негосударственной пенсии'
    end as postaccrual_income_action_text,
    s."service_type",
    s.sharer_individual_id,
    s.strategy_name,
    s.termination_date,
    s.termination_reason,
    s.uorls_document_id,
    s.uorls_document_file,
    s1.*
FROM s
    CROSS JOIN ourpension.get_contract_fields_main(:id) s1
LIMIT 1

2)
WITH cte_pens_product AS MATERIALIZED
(   -- Продукт - последнее значение
    SELECT 
        products.id, 
        products."name",
        products.pens_scheme_accum_stage_id as scheme_id
    FROM ourpension.contract_product_history history
        INNER JOIN prod_line.pens_product products 
        ON products.id = history.pens_product_id
    WHERE history.contract_id = :id
        AND CURRENT_TIMESTAMP BETWEEN 
            history.begin_date AND COALESCE( history.end_date, 'infinity' )
    LIMIT 1
), cte_param_pl AS MATERIALIZED 
(   -- Параметры из продуктовой линейки
    SELECT 
        pp2.brief, 
				pppp.value_int,
	pppp.value_decimal,
        prod_line.get_value_link_param
            (   CAST( pp2.id AS integer ), 
                CAST( pppp.value_ref AS integer ) 
            ) as param_value
    FROM cte_pens_product cpp
        INNER JOIN prod_line.pens_product pens_p 
        ON pens_p.id = cpp.id
        INNER JOIN prod_line.pens_product_param_pl pppp
        ON pppp.pens_product_id = pens_p.id
        INNER JOIN prod_line.param_pl pp2 
        ON pp2.id = pppp.param_pl_id     
)
SELECT 
    c.id,                                                           --  ИД договора
    (   SELECT param.param_value
        FROM cte_param_pl param
        WHERE param.brief = 'ФормаДоговора'
    ) AS contract_form,                                             --  Форма договора
    sct."name" AS sales_channel,                                    --  Канал продаж
    sct.brief AS cfo,                                               --  ЦФО
    e.short_name AS employer,                                       --  Работодатель
    pp."name" AS product_name,                                      --  Наименование продукта
    pp.id AS product_id,                                            --  ИД продукта
		(
    SELECT hp.name
    FROM prod_line.pens_product_param_pl p_i

    JOIN prod_line.param_pl def_i
        ON def_i.id = p_i.param_pl_id

    JOIN prod_line.table_param_pl_invest_profile ip
        ON ip.pens_product_param_pl_id = p_i.id

    JOIN prod_line.hb_invest_profile hp
        ON hp.id = ip.hb_invest_profile_id

    WHERE p_i.pens_product_id = pp.id
      AND def_i.brief = 'ИнвестПрофиль'
      AND (
          ip.validity_period_from IS NULL
          OR CURRENT_DATE >= ip.validity_period_from
      )
      AND (
          ip.validity_period_to IS NULL
          OR CURRENT_DATE <= ip.validity_period_to
      )

    ORDER BY ip.validity_period_from DESC NULLS LAST
    LIMIT 1
) AS invest_profile_name,
(
    SELECT hp.id
    FROM prod_line.pens_product_param_pl p_i
    JOIN prod_line.param_pl def_i
        ON def_i.id = p_i.param_pl_id
    JOIN prod_line.table_param_pl_invest_profile ip
        ON ip.pens_product_param_pl_id = p_i.id
    JOIN prod_line.hb_invest_profile hp
        ON hp.id = ip.hb_invest_profile_id
    WHERE p_i.pens_product_id = pp.id
      AND def_i.brief = 'ИнвестПрофиль'
      AND (
          ip.validity_period_from IS NULL
          OR CURRENT_DATE >= ip.validity_period_from
      )
      AND (
          ip.validity_period_to IS NULL
          OR CURRENT_DATE <= ip.validity_period_to
      )
    ORDER BY ip.validity_period_from DESC NULLS LAST
    LIMIT 1
) AS invest_profile_id,
    tv.amount_type,                                                 --  Тип суммы
    tv.month_since_open AS transfer_term_in_months,                 --  Срок перевода в месяцах с момента открытия ПС
    pr.edition_number AS pension_rules_edition,                     --  Редакция пенсионных правил
		pr.id as pension_rule_id,
		/*(
    SELECT param.param_value
    FROM cte_param_pl param
    WHERE param.brief = 'ПериодГарантВосполнения'
) AS guarantee_replenishment_period,*/
(
    SELECT w.warranty_period
    FROM prod_line.pens_product_param_pl p_w

    JOIN prod_line.param_pl def_w
        ON def_w.id = p_w.param_pl_id

    JOIN prod_line.table_param_pl_warrant_period w
        ON w.pens_product_param_pl_id = p_w.id

    JOIN prod_line.table_param_pl_invest_profile ip
        ON ip.hb_invest_profile_id = w.hb_invest_profileid

    JOIN prod_line.pens_product_param_pl p_i
        ON p_i.id = ip.pens_product_param_pl_id
       AND p_i.pens_product_id = p_w.pens_product_id

    JOIN prod_line.param_pl def_i
        ON def_i.id = p_i.param_pl_id

    WHERE p_w.pens_product_id = pp.id
      AND def_w.brief = 'ПериодГарантВосполнения'
      AND def_i.brief = 'ИнвестПрофиль'

      AND (
          w.year_conclusion_from IS NULL
          OR CAST(EXTRACT(YEAR FROM c.date) AS integer) >= w.year_conclusion_from
      )
      AND (
          w.year_conclusion_to IS NULL
          OR CAST(EXTRACT(YEAR FROM c.date) AS integer) <= w.year_conclusion_to
      )

      AND (
          w.validity_period_from IS NULL
          OR CURRENT_DATE >= w.validity_period_from
      )
      AND (
          w.validity_period_to IS NULL
          OR CURRENT_DATE <= w.validity_period_to
      )

      AND (
          ip.validity_period_from IS NULL
          OR CURRENT_DATE >= ip.validity_period_from
      )
      AND (
          ip.validity_period_to IS NULL
          OR CURRENT_DATE <= ip.validity_period_to
      )

    ORDER BY w.validity_period_from DESC NULLS LAST
    LIMIT 1
) AS guarantee_replenishment_period,

    to_json( COALESCE( tl.tariff_name, '' ) ) AS tariff,            --  Тариф (оборачиваем в to_json, чтобы отсечь мусор в строке из-за миграции)
    cp.tariff_id,                                                   --  ID тарифа
    cp.min_annual_income_period AS mgd_accumulation_period,         --  МГД в периоде накопления
    cp.min_annual_savings_income AS mgd_payment_period,             --  МГД в периоды выплат

    COALESCE(
    cp.first_payment_delay,
    (
        SELECT param.value_int
        FROM cte_param_pl param
        WHERE param.brief = 'СрокОжиданияПервогоВзносаКоличДней'
    )
) AS first_contribution_wait_period,                           --  Срок ожидания первого взноса
    --cp.current_payment AS current_contribution, 
		COALESCE(
    cp.current_payment,
    (
        SELECT param.value_decimal
        FROM cte_param_pl param
        WHERE param.brief = 'МинРазмерРегулВзносаС'
        LIMIT 1
    )
) AS current_contribution, --  Текущий взнос
    /*(   SELECT mpf."name"
        FROM prod_line.hb_param_pl_multiselect mpf
        WHERE mpf.id = cp.payment_frequency
    ) AS contribution_frequency,    */           
		(
    SELECT mpf."name"
    FROM prod_line.pens_product_param_pl ppp_freq
    JOIN prod_line.param_pl p_freq
        ON p_freq.id = ppp_freq.param_pl_id
    JOIN prod_line.pens_product_param_pl_hb_param_pl_multiselect ppm_freq
        ON ppm_freq.pens_product_param_pl_id = ppp_freq.id
    JOIN prod_line.hb_param_pl_multiselect mpf
        ON mpf.id = ppm_freq.hb_param_pl_multiselect_id
    WHERE ppp_freq.pens_product_id = pp.id
      AND p_freq.brief = 'ПериодичностьВзносов'
      AND mpf."name" <> 'Не установлено'
    LIMIT 1
) AS contribution_frequency,						--  Периодичность взносов
    cp.contribution_ss AS ss_deduction_rate,                        --  Размер отчислений в СС
    cp.contribution_sr AS sr_deduction_rate,                        --  Размер отчислений в СР
    (   SELECT vst."label"
        FROM ourpension.voc_sharer_types vst
        WHERE vst."code" = cp.contribution_account
    ) AS contribution_account,                                      --  Счет учета взносов
		(
    SELECT vst.label
    FROM ourpension.voc_sharer_types vst
    WHERE vst.code = cp.payment_source_code
      AND vst.is_payment_source = true
) AS payment_source,
   /* (   SELECT param.param_value
        FROM cte_param_pl param
        WHERE param.brief = 'ТипВыплаты'
    ) AS payment_type,  */
		(
    SELECT ms.name
    FROM prod_line.pens_product_param_pl p_term
    JOIN prod_line.param_pl def_term
        ON def_term.id = p_term.param_pl_id
    JOIN prod_line.table_param_pl_payment_term pt
        ON pt.pens_product_param_pl_id = p_term.id
    LEFT JOIN prod_line.hb_param_pl_multiselect ms
        ON ms.id = pt.payment_type_pl_multiselect_id
    WHERE p_term.pens_product_id = pp.id
      AND def_term.brief = 'СрокВыплаты'
      AND pt.payment_type_pl_multiselect_id IS NOT NULL
      AND (
          pt.app_date_from IS NULL
          OR c.date >= pt.app_date_from
      )
      AND (
          pt.app_date_to IS NULL
          OR c.date <= pt.app_date_to
      )
    ORDER BY pt.app_date_from DESC NULLS LAST, pt.id DESC
    LIMIT 1
) AS payment_type,																									--  Тип выплаты
(
    SELECT pt.payment_type_pl_multiselect_id
    FROM prod_line.pens_product_param_pl p_term
    JOIN prod_line.param_pl def_term
        ON def_term.id = p_term.param_pl_id
    JOIN prod_line.table_param_pl_payment_term pt
        ON pt.pens_product_param_pl_id = p_term.id
    WHERE p_term.pens_product_id = pp.id
      AND def_term.brief = 'СрокВыплаты'
      AND pt.payment_type_pl_multiselect_id IS NOT NULL
      AND (
          pt.app_date_from IS NULL
          OR c.date >= pt.app_date_from
      )
      AND (
          pt.app_date_to IS NULL
          OR c.date <= pt.app_date_to
      )
    ORDER BY pt.app_date_from DESC NULLS LAST, pt.id DESC
    LIMIT 1
) AS payment_type_id,
(
    SELECT pt.max_term_to
    FROM prod_line.pens_product_param_pl p_term
    JOIN prod_line.param_pl def_term
        ON def_term.id = p_term.param_pl_id
    JOIN prod_line.table_param_pl_payment_term pt
        ON pt.pens_product_param_pl_id = p_term.id
    WHERE p_term.pens_product_id = pp.id
      AND def_term.brief = 'СрокВыплаты'
      AND pt.max_term_to IS NOT NULL
      AND (
          pt.app_date_from IS NULL
          OR c.date >= pt.app_date_from
      )
      AND (
          pt.app_date_to IS NULL
          OR c.date <= pt.app_date_to
      )
    ORDER BY pt.app_date_from DESC NULLS LAST, pt.id DESC
    LIMIT 1
) AS max_payment_term,                                              --Максимальный срок
    --cp.minimal_payment_amount AS min_payment_amount,
		COALESCE(
    cp.minimal_payment_amount,
    (
        SELECT mp.min_pension_from
        FROM prod_line.pens_product_param_pl p_min
        JOIN prod_line.param_pl def_min
            ON def_min.id = p_min.param_pl_id
        JOIN prod_line.table_param_pl_minimum_pension mp
            ON mp.pens_product_param_pl_id = p_min.id
        WHERE p_min.pens_product_id = pp.id
          AND def_min.brief = 'МинРазмерПенсии'
          AND mp.min_pension_from IS NOT NULL
        ORDER BY mp.id DESC
        LIMIT 1
    )
) AS min_payment_amount,																						--  Минимальный размер выплаты
    --cp.payment_term,  
		COALESCE(
    cp.payment_term,
    (
        SELECT pt.max_term_to
        FROM prod_line.pens_product_param_pl p_term
        JOIN prod_line.param_pl def_term
            ON def_term.id = p_term.param_pl_id
        JOIN prod_line.table_param_pl_payment_term pt
            ON pt.pens_product_param_pl_id = p_term.id
        WHERE p_term.pens_product_id = pp.id
          AND def_term.brief = 'СрокВыплаты'
          AND pt.max_term_to IS NOT NULL
          AND (
                pt.app_date_from IS NULL
                OR c.date >= pt.app_date_from
              )
          AND (
                pt.app_date_to IS NULL
                OR c.date <= pt.app_date_to
              )
        ORDER BY pt.app_date_from DESC NULLS LAST, pt.id DESC
        LIMIT 1
    )
) AS payment_term,																								--  Срок выплаты
    cp.min_payment_term,                                            --  Минимальный срок выплаты
    (   SELECT param.param_value
        FROM cte_param_pl param
        WHERE param.brief = 'ПоэтапныйВестингПарамРасчетаСрока'
    ) AS term_calculation_parametr,                                 --  Параметр расчета срока
    (   SELECT param.param_value
        FROM cte_param_pl param
        WHERE param.brief = 'ПериодПредостПравТреб'
    ) AS choice_right_provision_parameter,                          --  Период предоставления права требования
    cp.initial_payment,                                             --  Первоначальный взнос
    cp.min_actuarial_yield,
    cp.indexing_value,                                              -- Величина прироста размера пенсии
    pp.scheme_id,                                                   -- Пенсионная схема (ИД)
		ps.brief as scheme_number                                       -- Пенсионная схема (название)
FROM ourpension.contract c
    --LEFT JOIN ourpension.pension_rule pr ON pr.id = c.pension_rule_id
    LEFT JOIN ourpension.contract_params cp ON cp.contract_id = c.id
    
    LEFT JOIN ourpension.contract affiliate_c
    ON affiliate_c.id = c.affiliate_program_contract_id
		LEFT JOIN ourpension.entity e ON e.id = affiliate_c.entity_id
	 -- LEFT JOIN ourpension.entity e ON e.id = c.entity_id
    LEFT JOIN ourpension.fund f ON f.id = c.fund_id
    LEFT JOIN actuary.tariff_list tl ON tl.id = cp.tariff_id
    LEFT JOIN ourpension.sale_channel sc ON sc.id = c.sale_channel_id
    LEFT JOIN prod_line.sale_channel_type sct ON sct.id = sc.type_sale_channel_id
    left JOIN cte_pens_product pp on true -- кросс на лефт для ОПС, чтобы возвращала ид, без него не отображается отказ СФР (модель uOKQnzZO)
    LEFT JOIN prod_line.pens_scheme ps
    ON ps.id = pp.scheme_id
		--LEFT JOIN ourpension.pension_rule pr ON pr.id = ps.pension_rule_id
		LEFT JOIN LATERAL (
    SELECT
        h.pension_rule_id
    FROM ourpension.npo_contract_pension_rules_history h
    WHERE h.contract_id = c.id
      AND h.period <= c.date
    ORDER BY
        h.period DESC,
        h.version DESC
    LIMIT 1
) prh ON true

LEFT JOIN ourpension.pension_rule pr
    ON pr.id = COALESCE(prh.pension_rule_id, c.pension_rule_id)
		
    LEFT JOIN prod_line.table_vesting tv ON tv.product_id = pp.id
WHERE c.id = :id