---

do $fix_credit_group$
declare
    d record;
    v_child_id integer;
    v_count integer;
begin
    select count(distinct si.id)
    into v_count
    from (
        values
            (45, '48305'),
            (46, '48305.1'),
            (47, '48305.3'),
            (48, '48310')
    ) as expected(id, account)
    join ourpension.spr_indicator_bu si
        on si.id = expected.id
    join ourpension.spr_accounts_indicator_bu sa
        on sa.spr_indicator_bu_id = si.id
       and sa.account = expected.account;

    if v_count <> 4 then
        raise exception
            'Текущие показатели 45–48 не соответствуют проверенным счетам';
    end if;

    update ourpension.spr_indicator_bu
    set name = 'Кредиторская задолженность, в т.ч.'
    where id = 45;

    update ourpension.spr_indicator_bu
    set is_aggregate = 45
    where id in (46, 47, 48);

    update ourpension.spr_indicator_bu si
    set service_type = array(
        select distinct t.service
        from unnest(
            coalesce(
                si.service_type,
                cast(array[] as text[])
            ) || array['NPO', 'PDS']
        ) as t(service)
        order by t.service
    )
    where si.id in (45, 46, 47, 48);

    for d in
        select *
        from (
            values
                ('60301', 'Расчеты по НДФЛ'),
                ('60322', 'Расчеты по исполнительным листам')
        ) as details(account, name)
    loop
        select
            count(distinct si.id),
            min(si.id)
        into v_count, v_child_id
        from ourpension.spr_indicator_bu si
        join ourpension.spr_accounts_indicator_bu sa
            on sa.spr_indicator_bu_id = si.id
        where sa.account = d.account
          and si.id <> 45
          and (
              'NPO' = any(si.service_type)
              or 'PDS' = any(si.service_type)
          );

        if v_count > 1 then
            raise exception
                'Для счета % найдено несколько деталей',
                d.account;
        end if;

        if v_child_id is null then
            insert into ourpension.spr_indicator_bu (
                name,
                service_type,
                is_aggregate
            )
            values (
                d.name,
                array['NPO', 'PDS'],
                45
            )
            returning id into v_child_id;

            insert into ourpension.spr_accounts_indicator_bu (
                spr_indicator_bu_id,
                account
            )
            values (
                v_child_id,
                d.account
            );
        else
            if exists (
                select 1
                from ourpension.spr_indicator_bu
                where id = v_child_id
                  and coalesce(is_aggregate, 0) not in (0, 45)
            ) then
                raise exception
                    'Показатель % уже связан с другой группой',
                    v_child_id;
            end if;

            update ourpension.spr_indicator_bu si
            set
                name = d.name,
                is_aggregate = 45,
                service_type = array(
                    select distinct t.service
                    from unnest(
                        coalesce(
                            si.service_type,
                            cast(array[] as text[])
                        ) || array['NPO', 'PDS']
                    ) as t(service)
                    order by t.service
                )
            where si.id = v_child_id;
        end if;

        insert into ourpension.spr_accounts_indicator_bu (
            spr_indicator_bu_id,
            account
        )
        select 45, d.account
        where not exists (
            select 1
            from ourpension.spr_accounts_indicator_bu
            where spr_indicator_bu_id = 45
              and account = d.account
        );

        raise notice
            'Деталь кредиторской задолженности: ID %, счет %',
            v_child_id, d.account;
    end loop;
end;
$fix_credit_group$;