---

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.individual i
        on i.id = c.individual_id
     and lower(btrim(i.last_name)) = lower(btrim(l.person_last_name))
     and lower(btrim(i.first_name)) = lower(btrim(l.person_first_name))

    join ourpension.sharer sh
        on sh.contract_id = c.id

	left join ourpension.application_termination_contract_data ctd
    on sh.id = ctd.sharer_id
join ourpension.application_termination_contract ct
    on case
        when ctd.sharer_id is null then ct.sharer_id = sh.id
        else ct.id = ctd.application_termination_contract_id
    end
    and ct.accepted is not null
     and ct.rejected is null
     and ct.canceled is null
     and ct.termination_type in ('TRANSFER', 'PAYMENT', 'FULL', 'LIFE_SITUATION')
	
	/*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