---

with dates as (
    select
        to_date(:startDate, 'DD.MM.YYYY') as start_date,
        to_date(:endDate, 'DD.MM.YYYY') as end_date
),
accounts as (
    select unnest(array[
        '34804', '38902', '38922', '38923', '38924',
        '39002', '39022', '39023', '39024', '39703',
        '48309', '48310',
        '34801.1', '34801.2', '34801.3',
        '34901.1', '34901.2', '34901.3',
        '38311.1', '38911.1', '38911.2',
        '39011.1', '39011.2',
        '39031.1', '39031.2',
        '39032.1', '39032.2',
        '39033.1', '39033.2',
        '39034.1', '39034.2',
        '39035.1', '39035.2',
        '39036.1', '39036.2',
        '39037.1', '39037.2',
        '48301.1', '48301.3',
        '48302.1', '48302.3',
        '48303.1', '48303.3',
        '48304.1', '48304.3',
        '48305.1', '48305.3',
        '48306.1', '48306.3'
    ]) as account
),
moving as (
    select
        side.account,
        side.pension_scheme_id,
        side.direction,
        et.strategy_id,
        et.contract_class_code,
        et.operation_sum
    from back_office.eps_transaction et
    join back_office.eps_transaction_source src
        on src.id = et.eps_transaction_source_id
    join ourpension.eps e
        on e.id = et.eps_id
    join ourpension.eps_detail ed
        on ed.eps_id = e.id
    cross join dates d
    cross join lateral (
        values
            (ed.account_dt, et.pens_schema_dt, 'DT'),
            (ed.account_ct, et.pens_schema_ct, 'CT')
    ) side(account, pension_scheme_id, direction)
    join accounts a
        on a.account = side.account
    where e.service_type = 'NPO'
      and e.periodicity in (1, 2)
      and et.oper_date >= d.start_date
      and et.oper_date < d.end_date + interval '1 day'
),
grouped as (
    select
        account,
        pension_scheme_id,
        strategy_id,
        contract_class_code,
        sum(operation_sum)
            filter (where direction = 'DT') as sum_dt_pu,
        sum(operation_sum)
            filter (where direction = 'CT') as sum_ct_pu,
        count(*) filter (
            where operation_sum is null
        ) as rows_without_sum
    from moving
    group by
        account,
        pension_scheme_id,
        strategy_id,
        contract_class_code
)
select
    g.account,
    g.pension_scheme_id,
    ps.name as pension_scheme_name,
    ps.uid_1c as uidpensionscheme,
    g.strategy_id,
    st.name as strategy_name,
    st.uid_1c as uidsubtypeactivity,
    g.contract_class_code,
    g.sum_dt_pu,
    g.sum_ct_pu,
    coalesce(g.sum_dt_pu, 0)
        - coalesce(g.sum_ct_pu, 0) as sum_pu,
    g.rows_without_sum
from grouped g
left join prod_line.pens_scheme ps
    on ps.id = g.pension_scheme_id
left join prod_line.hb_invest_strategy st
    on st.id = g.strategy_id
order by
    g.account,
    g.pension_scheme_id,
    g.strategy_id,
    g.contract_class_code;