---

with

contract_status_current AS
    ( -- Последний (актуальный) статус
        SELECT
            cs."name" AS name,
            sh.code as code,
            sh.status_date as date        
        FROM ourpension.contract_statuses_history sh
            INNER JOIN ourpension.contract_statuses cs on cs.code = sh.code
        WHERE sh.contract_id = :id
            AND CURRENT_TIMESTAMP BETWEEN sh.status_date AND sh.status_end_date
        LIMIT 1
),

contract_status_effective AS
    ( -- Статус введения в действие
        SELECT sh.status_date as date
        FROM ourpension.contract_statuses_history sh        
        WHERE sh.contract_id = :id
            AND sh.code IN ( 'CA0', 'HA0', 'MA0' )
        ORDER BY sh.status_date
        LIMIT 1
    ),

termination as
( -- Прекращение
    select
        tr.name as reason,
        t.date as date
    from ourpension.termination_contract tc
        join ourpension.termination t on tc.termination_id = t.id
        left join ourpension.contract_termination_reason tr on tr.id = t.contract_termination_reason_id    
    where tc.contract_id = :id
        order by t.date desc, t.version desc
        limit 1
),


contract_invest as
( -- Инвестиционная стратегия
            select
                h.begin_date,
                s.name as name
            from ourpension.invest_strategy_history h
                join prod_line.hb_invest_strategy s on h.strategy_id = s.id
            where h.contract_id = :id
            order by h.begin_date desc
            limit 1

),


-- Классификация договора
contract_class as (
     SELECT
         cch.begin_date,
         cc."name" AS class_name
         FROM ourpension.contract_class_history cch
         JOIN ourpension.contract_classes cc ON cch.class_id = cc.id
         WHERE cch.contract_id = :id
         ORDER BY cch.begin_date DESC
         LIMIT 1
),

-- Продукт - последнее значение
cte_pens_product as (
    SELECT
        products.id,
        products."name"
    FROM ourpension.contract_product_history history
         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
(
    SELECT
        pp2.brief,
        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
         JOIN prod_line.pens_product pens_p ON pens_p.id = cpp.id
         JOIN prod_line.pens_product_param_pl pppp ON pppp.pens_product_id = pens_p.id
         JOIN prod_line.param_pl pp2 ON pp2.id = pppp.param_pl_id    
)



select        
     c.id as contract_id,
     CASE
                WHEN c.service_type = 'NPO' AND c.client_type = 0 THEN 'ИПП' -- 0 - Индивидуальный
                WHEN c.service_type = 'NPO' AND c.client_type = 1 THEN 'КПП' -- 1 - Корпоративный
                WHEN c.service_type = 'PDS' THEN 'ДДС'
                ELSE 'Не определен' -- Для ОПС будет - Не определен
     END as contract_type, -- Тип договора
     uat.activity_rus_type as service_type_rus, -- Вид деятельности
     coalesce(e.short_name, concat_ws(' ', i.last_name, i.first_name, i.middle_name)) as investor, -- Вкладчик
     status_cur.name as status_name,
     CASE WHEN status_cur.code IN ( 'EA0', 'JA0', 'PA0' ) THEN status_cur.date END as annulled_date,
     to_json(c.number) as contract_number,
     c.date as contract_date, -- Дата заключения
     ccls.begin_date as formed_date, -- Дата оформления = Дате классификации (? ve.kaydalov)
     ccls.class_name as contract_class_name, -- Классификация договора
     status_eff.date as effective_date, -- Дата статуса "Действия" - Вступление в силу
     t.date as termination_date, -- Дата прекращения
     t.reason as termination_reason, -- Причина прекращения
     cinv.name as invest_strategy_name, -- Инвестиционная стратегия
     coalesce(fu_sig.name_short, fu_sig.name_in_pfr) as fund_signed_name, -- Заключивший фонд
     coalesce(fu_src.name_short, fu_src.name_in_pfr) as fund_source_name, -- Фонд источник
     sct.brief AS cfo, -- ЦФО
     sct."name" AS sales_channel, -- Канал продаж
     e.short_name as employer, -- Работодатель
    
     to_json( CONCAT_WS(' ', ce.number, ' (' || ea.short_name || ')' ) ) as affiliate_program_contract_text, -- Договор КПП
     ha.short_name as department_name, -- Холдинг
     ag.code as agent_code, -- Код агента
     fb.name as fund_branch_name, -- Филиал заключения (подразделение агента)
     CONCAT_WS(
    ' ',
    ag.last_name,
        CASE
            WHEN ag.first_name IS NOT NULL THEN LEFT(ag.first_name, 1) || '.'
            ELSE ''
        END,
        CASE
            WHEN ag.middle_name IS NOT NULL THEN LEFT(ag.middle_name, 1) || '.'
            ELSE ''
        END
     ) AS agent_name,
        
        case WHEN c.individual_id <> sh.individual_id then true
        else false end as is_third, -- В пользу 3 лица
     c.family_member as is_family_member, -- Член семьи / близкий родственник
    
     cp.tariff_id as tariff_id,     -- ИД тарифа
    
    

     --=======УСЛОВИЯ ПО ДОГОВОРУ
     enums_nsc.enum_value as is_nonstandard_reason_text,     --Нестандартный договор Причина
     cp.min_actuarial_yield as actuarial_revenue ,-- Актуарная доходность
     cp.min_annual_income_period AS mgd_accumulation_period,         -- МГД в периоде накопления
     cp.min_annual_savings_income AS mgd_payment_period,             -- МГД в периоды выплат
     to_json( COALESCE( tl.tariff_name, '' ) ) AS tariff,            -- Тариф (оборачиваем в to_json, чтобы отсечь мусор в строке из-за миграции)
     pp."name" AS product_name,                                     -- Наименование продукта (Продукт)
     pp.id AS product_id,                                            -- ИД продукта (Продукт)
     replace(dt.name,'.'||dt.extension, '') as death_table_name ,     -- Таблица Дожития (файл ??)    
     case c.account_management_form
            when 'PERSONAL' then 'Именная'
            when 'JOINT' then 'Солидарная'
     end as account_management_form_text, -- Форма ведения счетов (Хард Коде потому что это тип такой) TODO -таблицу с наименованиями
    
        c.inheritance_type_code as inheritance_type_code, -- Параметр наследования код
        it.name as inheritance_type_text, -- Параметр наследования текст
        c.change_inheritance_type_by_sharer as is_change_inheritance_type_by_sharer, -- Разрешено ли Изменение параметров наследования по участнику
    
    
     --======УСЛОВИЯ ВЗНОСОВ
         ( SELECT mpf."name"
        FROM prod_line.hb_param_pl_multiselect mpf
        WHERE mpf.id = cp.payment_frequency
         ) AS contribution_frequency,                                     -- Периодичность взносов
     cp.initial_payment as initial_payment,    -- Первоначальный взнос
     ( SELECT param.param_value
        FROM cte_param_pl param
        WHERE param.brief = 'СрокОжиданияПервогоВзносаКоличДней'
     ) AS first_contribution_wait_period , -- Срок ожидания первого взноса
        cp.current_payment AS current_contribution,    -- Текущий взнос
        cp.contribution_ss AS amount_deductions_ss,                     -- Размер отчислений в СС
        cp.contribution_sr AS amount_deductions_sr,                     -- Размер отчислений в СР
     ( SELECT vst."label"
        FROM ourpension.voc_sharer_types vst
        WHERE vst."code" = cp.contribution_account
     ) AS contribution_account,     -- Счет учета взносов
    
        --======УСЛОВИЯ ВЫПЛАТ
         cp.indexing_value as indexing_value,     -- Величина прироста размера пенсии
        ( SELECT param.param_value
        FROM cte_param_pl param
        WHERE param.brief = 'ТипВыплаты'
         ) AS payment_type,         -- Тип выплаты
        cp.minimal_payment_amount AS min_payment_amount,                -- Минимальный размер выплаты
        cp.payment_term as payment_period,                             -- Срок выплаты
        cp.min_payment_term as min_payment_period,                     -- Минимальный срок выплаты
        
         --======УСЛОВИЯ ВЕСТИНГА
        
        case (SELECT 1
        FROM ourpension.sharer_params
        WHERE kpp_contract_id = :id
                AND is_active_vesting = true
        GROUP BY kpp_contract_id) when 1 then true
        else false end as vesting_state ,             -- Состояние вестинга    
         enums.enum_value AS vesting_way,             -- Способ вестинга        
        
        case WHEN cp.is_transfer_next_receipts THEN true
        ELSE false
        END AS is_transfer_next_receipts,                 --     Передача последующих поступлений    ( может и не быть поэтому CASE
        cp.date_start_vesting AS date_start_vesting,    --     Дата начала вестинга    
        pl_multi_2."name" AS rigth_to_pension,            --    Право на пенсию
        pl_multi_3."name" AS account_vesting_type,                     --    Тип счета вестинга
        pl_multi_4."name" AS period_calc_param,                            --     Параметры расчета срока
        pl_multi_1."name" AS period_right_claim                         --    Период предоставления права требования
        
        
        
        
    

    from ourpension.contract c    
        LEFT JOIN ourpension.uv_activity_type uat on uat.client_type = c.client_type AND uat.activity_eng_type = c.service_type
        
        
        --Нестандартный договор
        left join ourpension.enums enums_nsc on enums_nsc.id = c.nonstandard_reason_id
        
        -- Счет для индивидуального договора (для в пользу 3 лица)
        left join ourpension.sharer sh on sh.contract_id = c.id and c.client_type = 0
        
        -- Вкладчик
        LEFT JOIN ourpension.individual i ON c.individual_id = i.id
        LEFT JOIN ourpension.entity e ON e.id = c.entity_id
        -- Прекращение
        LEFT JOIN termination t on true
        
        -- Продукт - последнее значение
        left JOIN cte_pens_product pp on true
        
        -- Текущий статус
        LEFT JOIN contract_status_current status_cur on true
        
        -- Данные статуса "Действующий"
        LEFT JOIN contract_status_effective status_eff on true
        
        -- Классификация договора
        left join contract_class ccls on true
        
        --Инвестиционная стратегия
        left join contract_invest cinv on true
        
        -- Заключивший фонд
        LEFT JOIN ourpension.fund fu_sig ON fu_sig.id = c.sign_fund_id    
        -- Фонд источник
        LEFT JOIN ourpension.fund fu_src ON fu_src.id = c.fund_id
        
        
        -- Подразделение заключения(филиал) (? ve.kaydalov агент не может поменять подразделение?)
        LEFT JOIN ourpension.agent ag ON ag.id = c.agent_id
        LEFT JOIN ourpension.fund_branch fb ON fb.id = ag.fund_branch_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 ourpension.information_source isc on isc.id = c.sale_channel_id
    
        -- Договор КПП вкладчика
        LEFT JOIN ourpension.contract ce ON ce.id = c.affiliate_program_contract_id
        LEFT JOIN ourpension.entity ea ON ea.id = ce.entity_id
        LEFT JOIN ourpension.holding ha ON ha.id = ea.holding_id
        
        -- Доп параметры договора
        LEFT JOIN ourpension.contract_params cp ON cp.contract_id = c.id
        LEFT JOIN actuary.tariff_list tl ON tl.id = cp.tariff_id
        
        LEFT JOIN prod_line.hb_param_pl_multiselect pl_multi_2 ON pl_multi_2.id = cp.rigth_to_pension_id -- Право на пенсию
        LEFT JOIN prod_line.hb_param_pl_multiselect pl_multi_3 ON pl_multi_3.id = cp.account_vesting_type_id -- Тип счета вестинга
        LEFT JOIN prod_line.hb_param_pl_multiselect pl_multi_4 ON pl_multi_4.id = cp.time_calc_param_id -- Параметры расчета срока
        LEFT JOIN prod_line.hb_param_pl_multiselect pl_multi_1 ON pl_multi_1.id = cp.period_right_requirement_id -- Период предоставления права требования
        
        -- Таблица Дожития хз что это
        LEFT JOIN dev.query_datasource_file dt on dt.id=c.death_table_query_datasource_file_id
        
        -- Параметры наследования
        left join ourpension.inheritance_type it on c.inheritance_type_code = it.code
        
        -- Способ вестинга
        LEFT JOIN ourpension.enums enums ON enums.id = cp.vesting_type
    
    where c.id = :id