---

insert into back_office.report_conformity_pu_bu(
	m_y_report,
	"version",
	service_type,
	user_id,
	form_date
)
values (
       :date_report,
			 now(),
			 :service_type,
			 :user_id,
			 :date_form
)



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,
        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,
        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
)
insert into back_office.report_conformity_pu_bu_detail (
    report_conformity_pu_bu_id,
    account,
    pension_scheme_id,
    strategy_id,
    contract_class_code,
    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,
    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
		


with 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
),
accounts as (
    select account
    from accounts_npo
    where :service_type = 'NPO'

    union

    select sa.account
    from ourpension.spr_accounts_indicator_bu sa
    where :service_type <> 'NPO'
),
str_acc as (
    select string_agg(account, ',' order by account) as acc
    from accounts
),
bu_raw as materialized (
    select
        btrim(t.account) as account,
        t.debitbalance,
        t.creditbalance,
        t.uidpensionscheme,
        t.uidsubtypeactivity,
        lower(nullif(btrim(t.uidpensionscheme), ''))
            as uid_scheme_key,
        lower(nullif(btrim(t.uidsubtypeactivity), ''))
            as uid_strategy_key
    from str_acc s
    cross join lateral mtrustgate.get_list_account_balances_bu(
        to_date(:endDate, 'DD.MM.YYYY'),
        '1147799009115',
        :service_type,
        s.acc
    ) t
    where :service_type <> 'NPO'
       or exists (
           select 1
           from accounts_npo a
           where a.account = btrim(t.account)
       )
),
bu as (
    select
        br.*,
        count(*) over (
            partition by
                br.account,
                br.uid_scheme_key,
                br.uid_strategy_key
        ) as rows_with_key
    from bu_raw br
)
merge into back_office.report_conformity_pu_bu_detail as target
using (
    select
        b.*,
        pu.match_row_id,
        ps.pension_scheme_id,
        st.strategy_id
    from bu b

    left join lateral (
        select
            case
                when count(*) = 1
                 and count(*) filter (
                     where
                         nullif(btrim(rd.contract_class_code), '')
                             is null
                         and (
                             b.uid_scheme_key is not null
                             or rd.pension_scheme_id is null
                         )
                         and (
                             b.uid_strategy_key is not null
                             or rd.strategy_id is null
                         )
                 ) = 1
                then min(rd.id)
            end as match_row_id
        from back_office.report_conformity_pu_bu_detail rd
        where rd.report_conformity_pu_bu_id =
            currval('back_office.report_conformity_pu_bu_id_seq')
          and rd.account = b.account
          and lower(nullif(btrim(rd.uidpensionscheme), ''))
              is not distinct from b.uid_scheme_key
          and lower(nullif(btrim(rd.uidsubtypeactivity), ''))
              is not distinct from b.uid_strategy_key
    ) pu on true

    left join lateral (
        select
            case when count(*) = 1 then min(p.id) end
                as pension_scheme_id
        from prod_line.pens_scheme p
        where lower(nullif(btrim(cast(p.uid_1c as text)), ''))
            = b.uid_scheme_key
    ) ps on true

    left join lateral (
        select
            case when count(*) = 1 then min(s.id) end
                as strategy_id
        from prod_line.hb_invest_strategy s
        where lower(nullif(btrim(cast(s.uid_1c as text)), ''))
            = b.uid_strategy_key
    ) st on true
) as source
on (
    target.id = source.match_row_id
    and source.rows_with_key = 1
)
when matched then
    update set
        sum_dt_bu = source.debitbalance,
        sum_ct_bu = source.creditbalance,
        pension_scheme_id = coalesce(
            target.pension_scheme_id,
            source.pension_scheme_id
        ),
        strategy_id = coalesce(
            target.strategy_id,
            source.strategy_id
        )
when not matched then
    insert (
        report_conformity_pu_bu_id,
        account,
        pension_scheme_id,
        strategy_id,
        contract_class_code,
        uidpensionscheme,
        uidsubtypeactivity,
        sum_dt_bu,
        sum_ct_bu
    )
    values (
        currval('back_office.report_conformity_pu_bu_id_seq'),
        source.account,
        source.pension_scheme_id,
        source.strategy_id,
        null,
        source.uidpensionscheme,
        source.uidsubtypeactivity,
        source.debitbalance,
        source.creditbalance
    )