---

select
    l.id as load_snv_id,
    atc_one.id as id,
    c_one.individual_id,
    l.sum_tax_deduction,
    l.year,
    l.deduction_sign as type,
    coalesce(sum_by_year.value, 0) as sum_income,
    m_nv_one.value as max_tax_deduction
from ourpension.load_snv l

left join lateral (
    select
        c.id,
        c.individual_id,
        c.service_type
    from ourpension.contract c
    where c.number = l.contract_number
    order by c.id desc
    limit 1
) c_one on true

left join lateral (
    select
        sh.id,
        sh.sharer_id_1c
    from ourpension.sharer sh
    where sh.contract_id = c_one.id
    order by sh.id desc
    limit 1
) sh_one on true

left join lateral (
    select
        atc.id,
        atc.number,
        atc.date
    from ourpension.application_termination_contract atc
    where atc.sharer_id = sh_one.id
      and cast(atc.date as date) <= cast(l.document_date as date)
    order by
        cast(atc.date as date) desc,
        atc.id desc
    limit 1
) atc_one on true

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

left join lateral (
    select
        max(m_nv.value) as value
    from ourpension.max_year_tax_deduction m_nv
    where m_nv.year = cast(l.year as integer)
      and coalesce(sum_by_year.value, 0) >= 0
) m_nv_one on true

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
  )

order by l.id;