---

with base as (
    select
        l.id as load_snv_id,
        ct.id as application_termination_contract_id,
        c.individual_id,
        l.sum_tax_deduction,
        l.year,
        l.deduction_sign as type,
        c.service_type,
        sh.sharer_id_1c
    from ourpension.load_snv l

    join ourpension.contract c
        on l.contract_number = c.number

    join ourpension.application_termination_contract ct
        on ct.individual_id = c.individual_id
       and ct.accepted is not null
       and ct.rejected is null
       and ct.canceled is null

    join ourpension.application_termination_contract_data ctd
        on ct.id = ctd.application_termination_contract_id

    join ourpension.sharer sh
        on sh.id = ctd.sharer_id
       and sh.contract_id = c.id

    where l.file_id = :fileId
      and exists (
          select 1
          from tools.selection s
          where s.row_id = l.id
            and s.username = :username
            and s.code = :query_table_code
      )
),

sum_by_year as (
    select
        b.load_snv_id,
        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 cast(date_part('year', t.period_date) as integer) = cast(b.year as integer)
      and 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 b.load_snv_id
)

select
    b.load_snv_id,
    b.application_termination_contract_id as id,
    b.individual_id,
    b.sum_tax_deduction,
    b.year,
    b.type,
    coalesce(sum_by_year.value, 0) as sum_income,
    m_nv.value as max_tax_deduction
from base b

left join sum_by_year
    on sum_by_year.load_snv_id = b.load_snv_id

left join ourpension.max_year_tax_deduction m_nv
    on m_nv.year = cast(b.year as integer)
   and coalesce(sum_by_year.value, 0) >= 0

order by b.load_snv_id;