with phones_cte as (
    select
        *
    from
        "Contact" p
    where
        p."Contractor" = 21799108::integer and
        p."Type" = any(array[10, 11, 12, 13, 14]) and
        p."PrivatePerson" is NOT NULL and
        p."Active"
    limit null
    ),
    pack as (
            select  *
            from (
                select
                    p
                    ,unnest(array(
                        select t
                        from "Телефон" t
                        where t."НомерПолный" = "substring"(p."Value", '([^|]{0,})'::text)
                    )) a
                from
                    phones_cte p
            ) a
        )
 ,
    matched as (
        select
            p,
            a,
            exists (
                with
                search_pack as (
                        -- CTE для получения списка найденных телефонов.
                        select '{'|| (p)."Value" || '}' phone_other
                    ),

                from_other as (
                        with
                        phones_other as (
                            select unnest(p.phone_other::text[]) phone
                            from search_pack p
                        ),
                        region as (
                            select left(coalesce("KLADR", "КПП", "ИНН",'0'), 2) as region
                            from "Контрагент"
                            where "@Лицо" = 21799108::integer
                        )
                        select null
                        from (
                            select unnest(array(
                                SELECT
                                    cd
                                FROM
                                    "Contact" cd
                                WHERE
                                    reverse(substring(cd."Value", '([^|]{0,})')) = reverse(p.phone)
                                    AND cd."Type" = ANY(ARRAY[10, 11, 12, 13, 14])  -- все типы для телефонов
                                    AND cd."Active"
                                    AND (coalesce(cd."Contractor", 0) <> 21799108::integer)
                                    AND
                                        case
                                        when  cd."Contractor" is not null then
                                            exists (
                                                select null
                                                from "Контрагент" k
                                                where k."@Лицо" = cd."Contractor"
                                                    and k."НеОтображается" is null
                                                    and ((left(coalesce(k."KLADR", k."КПП", k."ИНН", '0'), 2) = (select region from region) and length(p.phone) between 5 and 7) or length(p.phone) > 7)
                                            )
                                        else
                                            exists (
                                                select null
                                                from "Контрагент" e
                                                where e."Entrepreneur" = cd."PrivatePerson"
                                                    and e."НеОтображается" is null
                                                    and ((left(coalesce(e."KLADR", e."КПП", e."ИНН", '0'), 2) = (select region from region) and length(p.phone) between 5 and 7) or length(p.phone) > 7)
                                            )
                                        end
                                LIMIT 1
                            )) t
                            from phones_other p
                            limit 1
                        ) t
                )
                 select null
                from
                from_other
            ) "ЕстьСовпавшие"
        from pack
        limit 50
    ),
    counted as (
        select
            (p)."PrivatePerson",
            array_agg(coalesce( (p)."Value"::text, (a)."НомерПолный"::text )) "НомерПолный",
            array_agg(coalesce('8' || (a)."Код" || (a)."Номер"::text, (a)."НомерПолный"::text)) "НомерНорм",
            array_agg((a)."Балл") "Баллы",
            array_agg((a)."Статус") "Статусы",
            array_agg((a)."@Телефон") "ИдТелефонов",
           /* array_agg((p)."Value") "НомерПолный",
            array_agg((p)."Value") "НомерНорм", */
            array_agg("ЕстьСовпавшие") "ЕстьСовпавшие"
        from
            matched
        group by
            (p)."PrivatePerson"
        )

        select
            p."@Представитель" "Представитель",
            (coalesce(pf."Фамилия",'') || coalesce(' ' || pf."Имя",'') || coalesce(' ' || pf."Отчество",'')) as "ФИО",
            p."Примечание",
           --  << phone section
            phone."ИдТелефонов",
            phone."НомерПолный",
            phone."НомерНорм",
            phone."Баллы",
            phone."Статусы",
            phone."ЕстьСовпавшие",
            -- <<
            ct."Value" as email,
            pf."@Лицо" as "Лицо",
            p."Тип"::smallint,
            p."Должность",
            p."Контрагент",
            -- Отдельно ФИО для Сервиса Профилей при отображении представителей в карточке контрагента
            pf."Фамилия",
            pf."Имя",
            pf."Отчество"
        from
            "Представитель" p
            inner join "ЧастноеЛицо" pf on pf."@Лицо" = p."ЧастноеЛицо" and pf."Тип" in (1, 2)
            left join "Contact" ct on ct."PrivatePerson" = pf."@Лицо" and ct."Type"= 20
            left join counted phone on p."ЧастноеЛицо" = phone."PrivatePerson"
        where
            p."Контрагент" = 21799108::integer
        order by
            "ФИО" nulls last, "НомерПолный" nulls last

-- select "Value", count(*)
-- from "Contact"
-- where "Contractor" =  21799108
-- group by "Value"