---

with dates as (
    select
        to_date(:startDate, 'DD.MM.YYYY') as start_date,
        to_date(:endDate, 'DD.MM.YYYY') as end_date
),
accounts_npo 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,
        e.classification as classification_id,
        e.contract_model_label,
        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)
    where e.service_type = :service_type
      and e.periodicity in (1, 2)
      and et.oper_date >= d.start_date
      and et.oper_date < d.end_date + interval '1 day'
      and side.account is not null
      and (
          :service_type <> 'NPO'
          or exists (
              select 1
              from accounts_npo a
              where a.account = side.account
          )
      )
),
grouped as (
    select
        account,
        pension_scheme_id,
        strategy_id,
        contract_class_code,
        classification_id,
        contract_model_label,
        sum(operation_sum)
            filter (where direction = 'DT') as sum_dt_pu,
        sum(operation_sum)
            filter (where direction = 'CT') as sum_ct_pu
    from moving
    group by
        account,
        pension_scheme_id,
        strategy_id,
        contract_class_code,
        classification_id,
        contract_model_label
)
insert into back_office.report_conformity_pu_bu_detail (
    report_conformity_pu_bu_id,
    account,
    pension_scheme_id,
    strategy_id,
    contract_class_code,
    classification_id,
    contract_model_label,
    uidpensionscheme,
    uidsubtypeactivity,
    sum_dt_pu,
    sum_ct_pu
)
select
    currval('back_office.report_conformity_pu_bu_id_seq'),
    g.account,
    g.pension_scheme_id,
    g.strategy_id,
    g.contract_class_code,
    g.classification_id,
    g.contract_model_label,
    cast(ps.uid_1c as text),
    cast(st.uid_1c as text),
    g.sum_dt_pu,
    g.sum_ct_pu
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