---

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.sharer sh
        on sh.contract_id = c.id

    join ourpension.application_termination_contract_data ctd
        on sh.id = ctd.sharer_id

    join ourpension.application_termination_contract ct
        on ct.id = ctd.application_termination_contract_id
       and ct.accepted is not null
       and ct.rejected is null
       and ct.canceled is null
       and ct.termination_type in ('TRANSFER', 'PAYMENT', 'FULL')

    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
      )
),

account_pairs as (
    select distinct
        b.sharer_id_1c,
        b.service_type
    from base b
),

operations as (
    select
        p.sharer_id_1c,
        p.service_type,
        cast(date_part('year', t.period_date) as integer) as year,
        t.amount
    from account_pairs p

    join mgr.ft_dwh_get__pension_account_operation(
        cast(
            '{"p_sPensionAccountId":"' || p.sharer_id_1c ||
            '", "p_sServiceType":"' || p.service_type || '"}'
            as jsonb
        )
    ) t on true

    where case
            when p.service_type = 'NPO'
                then t.operation_type_name in ('Ч/з банк', 'От работодателя')
            when p.service_type = 'PDS'
                then t.operation_type_name = 'Сберегательные взносы'
            else false
          end
),

sum_by_year as (
    select
        o.sharer_id_1c,
        o.service_type,
        o.year,
        sum(o.amount) as value
    from operations o
    group by
        o.sharer_id_1c,
        o.service_type,
        o.year
)

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.sharer_id_1c = b.sharer_id_1c
   and sum_by_year.service_type = b.service_type
   and sum_by_year.year = cast(b.year as integer)

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;