---

with base as (
    select
        c.id as contract_id,
        c.service_type,
        c.date as contract_date,
        sh.id as sharer_id,
        sh.sharer_id_1c
    from ourpension.application_termination_contract atc
        join ourpension.sharer sh on atc.sharer_id = sh.id
        join ourpension.contract c on c.id = sh.contract_id
    where atc.id = :p_application_termination_contract_id
),

sum_by_year(year, value) as (
    select
        cast(date_part('year', t.period_date) as integer) as year,
        sum(t.amount) as value
    from base b
        join mgr.ft_dwh_get__pension_account_operation(
            cast(
                '{"p_sPensionAccountId":"' || b.sharer_id_1c ||
                '", "p_sServiceType":"' || b.service_type || '"}'
                as jsonb
            )
        ) t on true
    where case
        when b.service_type = 'NPO'
            then t.operation_type_name in ('Ч/з банк', 'От работодателя')
        when b.service_type = 'PDS'
            then t.operation_type_name = 'Сберегательные взносы'
        else false
    end
    group by cast(date_part('year', t.period_date) as integer)
)

select
    m_nv.year,
    'NONE' as type,
    coalesce(sum_by_year.value, 0::numeric) as sum_income,
    null::numeric as sum_tax_deduction,
    null::date as date,
    m_nv.value as max_tax_deduction
from base b
    join ourpension.max_year_tax_deduction m_nv
        on m_nv.year = :p_year
    left join sum_by_year
        on sum_by_year.year = m_nv.year
        and sum_by_year.value >= 0
where m_nv.year >= cast(date_part('year', b.contract_date) as integer)
order by m_nv.year;