---

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 LATERAL (
    SELECT
        l.id,
        l.code
    FROM ourpension.contract_product_history cph
    JOIN prod_line.pens_product_hb_print_form pf
        ON pf.pens_product_id = cph.pens_product_id
    JOIN ourpension.landscape_form l
        ON l.type_group_form_id = pf.type_group_form_id
    WHERE cph.contract_id = c.id
      AND CURRENT_TIMESTAMP BETWEEN
          cph.begin_date
          AND COALESCE(cph.end_date, 'infinity')
      AND (l.start_date IS NULL OR l.start_date <= CURRENT_DATE)
      AND (l.end_date IS NULL OR l.end_date >= CURRENT_DATE)
    ORDER BY
        cph.begin_date DESC NULLS LAST,
        l.start_date DESC NULLS LAST,
        l.id DESC
    LIMIT 1
) lf ON TRUE
        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