---

with accounts_npo as (
    select unnest(array[
        '34804', '38302', '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 rd.classification_id is null
and nullif(btrim(rd.contract_model_label), '')
    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
    )