---

-- DROP FUNCTION actuary.get_pension_scheme(_int4, _int4, date, text);

CREATE OR REPLACE FUNCTION actuary.get_pension_scheme(p_sharer_ids integer[], p_contract_ids integer[], p_calc_date date, p_service_type text)
RETURNS TABLE(prod_line_pens_scheme_id bigint, sharer_id integer, contract_id integer, number text, name text, investment_portfolio_name text, min_pension_value numeric, min_deposit_value numeric, inheritance_on_pay text, inheritance_on_accum text, oud numeric, duration_max integer, duration_min integer, mgd_accum numeric, mgd_pay numeric)
LANGUAGE plpgsql
AS $function$
    declare
        v_calc_datetime timestamp;        
    begin
    
    v_calc_datetime = p_calc_date::timestamp + interval '23 hours 59 minutes';
        
        return query
            with base as (
                select
                    s.id as sharer_id,
                    c.id as contract_id,
                    case
                        when p_contract_ids is null and sharer_product.id is not null then sharer_product.id
                        else contract_product.id
                    end as product_id
                from ourpension.contract c
                left join ourpension.sharer s on s.contract_id = c.id
                left join lateral (
                    select distinct on (sph.sharer_id)
                        sph.pens_product_id as id
                    from ourpension.sharer_product_history sph
                    where sph.sharer_id = s.id
                        and sph.begin_date < v_calc_datetime
                        and (sph.end_date is null or sph.end_date > v_calc_datetime)
                    order by sph.sharer_id, sph.begin_date desc
                ) sharer_product on true
                left join lateral (
                    select distinct on (cph.contract_id)
                        cph.pens_product_id as id
                    from ourpension.contract_product_history cph
                    where cph.contract_id = c.id
                        and cph.begin_date < v_calc_datetime
                        and (cph.end_date is null or cph.end_date > v_calc_datetime)
                    order by cph.contract_id, cph.begin_date desc
                ) contract_product on true
                where (s.id = any(p_sharer_ids)) --or (c.id = any(p_contract_ids))
                    and (p_service_type is null or c.service_type = p_service_type)
            )
            select --distinct on (b.sharer_id)
                ps.id as prod_line_pens_scheme_id, --ИД для фильтров в платформе
                b.sharer_id::integer, --Номер счета, если выходим для участника
                b.contract_id::integer, --Номер договора, если для вкладчика
                ps.name::text as "number", --Имя пенс схемы для старого поля "Номер"
                ps.brief::text as "name", --Имя пенс схемы
                null::text as investment_portfolio_name, --Не найдено название(атрибут)
                min_pens.value::numeric as min_pension_value, --Мин. размер пенсии
                min_inpay.value_decimal::numeric as min_deposit_value, --Мин. размер взноса
                inher.on_pay::text as inheritance_on_pay, --Наследование в период накопления
                inher.on_accum::text as inheritance_on_accum, --Наследование в период выплат
                oud.value::numeric as oud, --админ. взнос в фонд
                duration.maximum::integer as duration_max, --Минимальный срок выплат
                duration.minimum::integer as duration_min, --Максимальный срок выплат
                mgd_accum.value_decimal::numeric as mgd_accum, --МГД в период накопления
                mgd_pay.value_decimal::numeric as mgd_pay --МГД в период накопит
            from base b
            join prod_line.pens_product pp on pp.id = b.product_id
            join prod_line.pens_scheme ps on ps.id = pp.pens_scheme_accum_stage_id
            left join lateral(
                select
                    pppl.value_decimal
                from prod_line.pens_product_param_pl pppl
                join prod_line.param_pl pl on pl.id = pppl.param_pl_id
                where pppl.pens_product_id = pp.id
                    and pl.brief = 'МГДНакПериодС'
                --limit 1
            ) mgd_accum on true
            left join lateral(
                select
                    pppl.value_decimal
                from prod_line.pens_product_param_pl pppl
                join prod_line.param_pl pl on pl.id = pppl.param_pl_id
                where pppl.pens_product_id = pp.id
                    and pl.brief = 'МГДВыпПериодС'
                --limit 1
            ) mgd_pay on true
            left join lateral(
                select
                    coalesce(tpppt.min_term_from, tpppt.min_term_to) as minimum,
                    coalesce(tpppt.max_term_to, tpppt.max_term_from) as maximum
                from prod_line.pens_product_param_pl pppl
                join prod_line.param_pl pl on pl.id = pppl.param_pl_id
                join prod_line.table_param_pl_payment_term tpppt on tpppt.pens_product_param_pl_id = pppl.id
                where pppl.pens_product_id = pp.id
                    and pl.brief = 'СрокВыплаты'
                --limit 1
            ) duration on true
            left join lateral(
                select
                    case
                        when hppm.brief = 'НеПредусмотрено' or hppm.brief is null
                        then 'false'
                        when hppm.brief = 'ПредусмотрПериодНакоплИВыплат' or hppm.brief = 'ПредусмотрПериодНакопл'
                        then 'true'
                    end as on_accum,
                    case
                        when hppm.brief = 'НеПредусмотрено' or hppm.brief is null
                        then 'false'
                        when hppm.brief = 'ПредусмотрПериодНакоплИВыплат' or hppm.brief = 'ПредусмотрПериодВыплат'
                        then 'true'
                    end as on_pay
                from prod_line.param_pl pl
                join prod_line.pens_product_param_pl pppp on pppp.param_pl_id = pl.id
                left join prod_line.pens_product_param_pl_hb_param_pl_multiselect pphm
                    on pphm.pens_product_param_pl_id = pppp.id
                left join prod_line.hb_param_pl_multiselect hppm on hppm.id = pphm.hb_param_pl_multiselect_id
                where pppp.pens_product_id = pp.id
                    and pl.brief = 'ПравопреемствоСмертьУчастника'            
            ) inher on true
            left join lateral(
                select
                    coalesce(tppmp.min_pension_from, tppmp.min_pension_to) as value
                from prod_line.pens_product_param_pl pppl
                join prod_line.param_pl pl on pl.id = pppl.param_pl_id
                join prod_line.table_param_pl_minimum_pension tppmp on tppmp.pens_product_param_pl_id = pppl.id
                where pppl.pens_product_id = pp.id
                    and pl.brief = 'МинРазмерПенсии'
            ) min_pens on true
            left join lateral(
                select
                    case
                        when bool_or(
                            pppl.value_bool
                        ) filter (
                            where pl.brief = 'ЕстьАдмВзнос'
                        ) is true
                        then max(tppaf.amount_av) filter (
                            where pl.brief = 'АдмВзносВФонд'
                        )
                        else 0::numeric
                    end as value
                from prod_line.pens_product_param_pl pppl
                join prod_line.param_pl pl on pl.id = pppl.param_pl_id
                left join prod_line.table_param_pl_administrative_fee tppaf
                    on tppaf.pens_product_param_pl_id = pppl.id
                where pppl.pens_product_id = pp.id
                    and pl.brief in ('ЕстьАдмВзнос','АдмВзносВФонд'--,'ТипАдминВзноса','ВидАдминВзноса'
                    )
                --limit 1
            ) oud on true
            left join lateral(
                select
                    coalesce(pppl.value_decimal, 0) as value_decimal
                from prod_line.pens_product_param_pl pppl
                join prod_line.param_pl pl on pl.id = pppl.param_pl_id
                where pppl.pens_product_id = pp.id
                    and pl.brief in ('МинРазмерРегулВзносаС', 'МинРазмерРегулВзносаПо')
                order by
                    case
                        when pl.brief = 'МинРазмерРегулВзносаС' then 1
                        when pl.brief = 'МинРазмерРегулВзносаПо' then 2
                    end
                limit 1
            ) min_inpay on true;
    end;
$function$
;