---

-- DROP FUNCTION back_office.get_eps_moving(date, date, _bpchar, varchar, bool, _int4, varchar);

CREATE OR REPLACE FUNCTION back_office.get_eps_moving(p_beg_period date DEFAULT NULL::date, p_end_period date DEFAULT CURRENT_DATE, p_service_types character[] DEFAULT NULL::character(3)[], p_eps_code character varying DEFAULT NULL::character varying, p_is_show_list boolean DEFAULT true, p_periodicityes integer[] DEFAULT NULL::integer[], p_account character varying DEFAULT NULL::character varying)
RETURNS TABLE(eps_id integer, eps_detail_id integer, eps_code character varying, service_type character varying, eps_name character varying, line_number integer, contract_model_label character varying, account_dt character varying, kind_sub_dt1 text, kind_sub_dt2 text, kind_sub_dt3 text, account_ct character varying, kind_sub_ct1 text, kind_sub_ct2 text, kind_sub_ct3 text, oper_content character varying, is_main_transaction boolean, trans_description character varying, operation_id integer, operation_date date, operation_sum numeric, operation_name character varying, strategy_id integer, classification_id smallint, individual_id integer, entity_id integer, citizenship_country_code text, contract_id integer, pension_schema_id integer, incoming_order_id integer, incoming_order_number character varying, contragent_name text, periodicity smallint, href_sub_dt1 text, href_sub_dt2 text, href_sub_dt3 text, href_sub_ct1 text, href_sub_ct2 text, href_sub_ct3 text, account_number character varying)
LANGUAGE plpgsql
AS $function$
-- ========================================================================
-- Получение списка Движение ЕПС
-- Получатель - данные для списка Движение ЕПС и для выгрузки ежедневных операций
-- Создание: 10.08.2026 dv.pakhomov
-- ========================================================================
BEGIN

IF p_service_types IS NULL
    OR ARRAY_LENGTH( p_service_types, 1 ) = 0
    THEN
    -- по умолчанию берем все Виды деятельности
    p_service_types =
    ( SELECT ARRAY_AGG( uat.activity_eng_type )
        FROM ourpension.uv_activity_type uat
        WHERE uat.client_type = 0
    );
END IF;

IF p_periodicityes IS NULL
    OR ARRAY_LENGTH( p_periodicityes, 1 ) = 0
    THEN
    -- по умолчанию берем все Периодичности
    p_periodicityes = ARRAY[ 1, 2 ];
END IF;

RETURN QUERY
WITH cte_var AS MATERIALIZED
( -- простые входные переменные
    SELECT
        p_eps_code as eps_code,
        -- параметры-даты приводим к timestamp: начальная дата это начало суток
        COALESCE( p_beg_period, '1990-01-01' )::timestamp as beg_period,
        -- параметры-даты приводим к timestamp: конечная дата это конец суток
        ( SELECT COALESCE( p_end_period, CURRENT_DATE )::timestamp
            + interval '1 day'
            - interval '1 msec'
        ) as end_period,
        p_is_show_list as is_show_list,
        p_account as "account"
),
cte_service_types AS MATERIALIZED
( -- входной массив видов деятельности как набор данных
    SELECT t.service_type
    FROM UNNEST(p_service_types) t(service_type)    
)
,
cte_periodicityes AS MATERIALIZED
( -- входной массив периодичностей как набор данных
    SELECT t.periodicity
    FROM UNNEST(p_periodicityes ) t(periodicity)
)
,
cte_href_parts AS MATERIALIZED
( -- постоянные фрагменты ссылок по ссылочным типам субконто
    SELECT *
    FROM
        ( VALUES
            ( 'PARTNERS_AGR', '', '', '' ),
            ( 'PARTNERS_I', '<a target="_blank" href="view?viewCode=Client_list&id=', '">',                 '</a>' ),
            ( 'PARTNERS_E', '<a target="_blank" href="view?viewCode=NCIuIMAW&id=',     '">',                 '</a>' ),
            ( 'CONTRACT',     '<a target="_blank" href="view?viewCode=7HPZX8vF&id=',     '" target="_blank">', '</a>' ),
            ( 'PAY_ORDER',    '<a target="_blank" href="view?viewCode=NL0wI3vg&id=',     '" target="_blank">', '</a>' )
        ) v ( type_sub,     id_prefix,                                                 id_name_text,         name_suffix )
)
,
cte_eps_transaction AS MATERIALIZED
( -- здесь данные ЕПС с витрины
    SELECT
        e.id as eps_id,
        s.operation_id,
        e.code,
        s.incoming_order_id,
        COALESCE( e.service_type, '' ) as service_type,
        COALESCE( e.contractor_type, -1 ) as contractor_type,
        et.contract_id_dt,
        et.contract_id_ct,
        et.individual_id_dt,
        et.individual_id_ct,
        et.entity_id_dt,
        et.entity_id_ct,
        et.pens_schema_dt,
        et.pens_schema_ct,
        et.citizenship_country_code_dt,
        et.citizenship_country_code_ct,
        COALESCE( et.operation_sum, 0 ) as operation_sum,
        et.strategy_id,
        et.oper_date
    FROM back_office.eps_transaction et
        CROSS JOIN cte_var cv
        INNER JOIN ourpension.eps e
        ON e.id = et.eps_id
        INNER JOIN back_office.eps_transaction_source s
        ON s.id = et.eps_transaction_source_id
        INNER JOIN cte_service_types cst
        ON cst.service_type = e.service_type
        INNER JOIN cte_periodicityes cp
        ON cp.periodicity = e.periodicity
    WHERE cv.is_show_list
        AND et.oper_date BETWEEN
            cv.beg_period AND cv.end_period
        AND
        ( cv.eps_code IS NULL
            OR e.code = cv.eps_code
        )    
)
,
cte_individual AS MATERIALIZED
( -- все ФЛ запроса с атрибутами (ФИО и гражданство)
    SELECT
        si.individual_id,
        RTRIM( i.last_name || ' ' || i.first_name || ' ' || COALESCE( i.middle_name, '' ) ) as individual_name,
        i.citizenship_country_code
    FROM
    ( SELECT sg.individual_id
        FROM
        ( SELECT dt.individual_id_dt AS individual_id
            FROM cte_eps_transaction dt
            WHERE dt.individual_id_dt IS NOT NULL
            UNION
            SELECT ct.individual_id_ct AS individual_id
            FROM cte_eps_transaction ct
            WHERE ct.individual_id_ct IS NOT NULL
        ) sg
        GROUP BY sg.individual_id
    ) si
        INNER JOIN ourpension.individual i
        ON i.id = si.individual_id
)
,
cte_entity AS MATERIALIZED
( -- все ЮЛ запроса с названиями
    SELECT
        se.entity_id,
        e.short_name as entity_name
    FROM
    ( SELECT sg.entity_id
        FROM
        ( SELECT dt.entity_id_dt AS entity_id
            FROM cte_eps_transaction dt
            WHERE dt.entity_id_dt IS NOT NULL
            UNION
            SELECT ct.entity_id_ct AS entity_id
            FROM cte_eps_transaction ct
            WHERE ct.entity_id_ct IS NOT NULL
        ) sg
        GROUP BY sg.entity_id
    ) se
        INNER JOIN ourpension.entity e
        ON e.id = se.entity_id
)
,
cte_transaction_text AS MATERIALIZED
( -- определение текста в ячейках субконто
    SELECT
        ct.eps_id,
        ed.id as eps_detail_id,
        ct.operation_sum,
        ct.code,
        lks.kind_subconto,
        lks.subconto_order,
        lns.text_sub,
        ( SELECT CONCAT            
                ( chp.id_prefix
                    || CASE lts.type_subconto
                            WHEN 'PARTNERS_I' THEN lks.individual_id::text
                            WHEN 'PARTNERS_E' THEN lks.entity_id::text
                            WHEN 'CONTRACT' THEN lks.contract_id::text
                            WHEN 'PAY_ORDER' THEN ct.incoming_order_id::text
                        END
                    || chp.id_name_text,
                    lns.text_sub,
                    chp.name_suffix
                ) as href_sub            
            FROM cte_href_parts chp
            WHERE chp.type_sub = lts.type_subconto
        ) as href_sub,
        lks.contract_id,
        ct.operation_id,
        ct.strategy_id,
        ct.incoming_order_id,
        lks.individual_id,
        lks.entity_id,
        lks.citizenship_country_code,
        lks.pension_schema_id,
        CASE WHEN ct.contractor_type = 1
            THEN ia."name" -- Агрегированный
            ELSE COALESCE( ci.individual_name, ce.entity_name ) -- Персонифицированный
        END as contragent_name,
        ct.oper_date
    FROM cte_eps_transaction ct
        INNER JOIN ourpension.eps_detail ed
        ON ed.eps_id = ct.eps_id
        INNER JOIN LATERAL
        ( SELECT
                s_d.kind_subconto,
                s_d.subconto_order, -- дебетовые ячейки субконто с 1 по 3
                ct.individual_id_dt as individual_id,
                ct.entity_id_dt as entity_id,
                ct.contract_id_dt as contract_id,
                ct.pens_schema_dt as pension_schema_id,
                ct.citizenship_country_code_dt as citizenship_country_code
            FROM ourpension.buh_account_subconto s_d
            WHERE s_d.code_account = ed.account_dt
            UNION
            SELECT
                s_c.kind_subconto,
                s_c.subconto_order + 3, -- сдвигаем ячейку в кредитную зону с 4 по 6
                ct.individual_id_ct as individual_id,
                ct.entity_id_ct as entity_id,
                ct.contract_id_ct as contract_id,
                ct.pens_schema_ct as pension_schema_id,
                ct.citizenship_country_code_ct as citizenship_country_code
            FROM ourpension.buh_account_subconto s_c
            WHERE s_c.code_account = ed.account_ct
        ) lks
        ON true
        INNER JOIN LATERAL
        ( -- Типизация субконто для ссылок
            SELECT
                CASE WHEN lks.kind_subconto = 'PARTNERS' THEN -- Контрагент
                        CASE
                            WHEN ct.contractor_type = 1 THEN 'PARTNERS_AGR'
                            WHEN lks.individual_id IS NOT NULL THEN 'PARTNERS_I'
                            WHEN lks.entity_id IS NOT NULL THEN 'PARTNERS_E'
                        END
                    ELSE lks.kind_subconto
                END as type_subconto
        ) lts
        ON true
        CROSS JOIN cte_var cv
        LEFT JOIN cte_individual ci
        ON ci.individual_id = lks.individual_id
        LEFT JOIN cte_entity ce
        ON ce.entity_id = lks.entity_id
        LEFT JOIN back_office.incoming_order io
        ON io.id = ct.incoming_order_id
        LEFT JOIN ourpension.contract c
        ON c.id = lks.contract_id
        LEFT JOIN ourpension.individual_aggregated ia
        ON ia.citizenship_country_code = lks.citizenship_country_code
        LEFT JOIN prod_line.pens_scheme ps
        ON ps.id = lks.pension_schema_id
        INNER JOIN LATERAL
        ( -- Текстовое название субконто
            SELECT
                CASE lks.kind_subconto
                    WHEN 'PARTNERS' THEN -- Контрагент
                        CASE WHEN ct.contractor_type = 1
                            THEN ia."name" -- Агрегированный
                            ELSE COALESCE( ci.individual_name, ce.entity_name ) -- Персонифицированный
                        END
                    WHEN 'CONTRACT' THEN c."number" -- Договор
                    WHEN 'PAY_ORDER' THEN io."number" -- Платежное поручение
                END as text_sub
        ) lns
        ON true
    WHERE lks.kind_subconto IS NOT null
        AND
        ( cv.account IS NULL
            OR ed.account_dt = cv.account
            OR ed.account_ct = cv.account
        )        
)
,
cte_transaction_subconto AS
( -- соединение субконто в строки по транзакции + номер строки (проводка ЕПС)
    SELECT
        ctt.operation_id,
        SUM(ctt.operation_sum) as operation_sum,
        ctt.eps_detail_id,
        -- колонки субконто раскладываем по ячейкам
        MAX
        ( CASE WHEN ctt.subconto_order = 1
                THEN ctt.text_sub
                ELSE ''
            END
        ) as kind_sub_dt1,
        MAX
        ( CASE WHEN ctt.subconto_order = 2
                THEN ctt.text_sub
                ELSE ''
            END
        ) as kind_sub_dt2,
        MAX
        ( CASE WHEN ctt.subconto_order = 3
                THEN ctt.text_sub
                ELSE ''
            END
        ) as kind_sub_dt3,
        MAX
        ( CASE WHEN ctt.subconto_order = 4
                THEN ctt.text_sub
                ELSE ''
            END
        ) as kind_sub_ct1,
        MAX
        ( CASE WHEN ctt.subconto_order = 5
                THEN ctt.text_sub
                ELSE ''
            END
        ) as kind_sub_ct2,
        MAX
        ( CASE WHEN ctt.subconto_order = 6
                THEN ctt.text_sub
                ELSE ''
            END
        ) as kind_sub_ct3,
        -- по ячейкам раскладываем типы субконто
        MAX
        ( CASE WHEN ctt.subconto_order = 1
                THEN ctt.href_sub
                ELSE ''
            END
        ) as href_sub_dt1,
        MAX
        ( CASE WHEN ctt.subconto_order = 2
                THEN ctt.href_sub
                ELSE ''
            END
        ) as href_sub_dt2,
        MAX
        ( CASE WHEN ctt.subconto_order = 3
                THEN ctt.href_sub
                ELSE ''
            END
        ) as href_sub_dt3,
        MAX
        ( CASE WHEN ctt.subconto_order = 4
                THEN ctt.href_sub
                ELSE ''
            END
        ) as href_sub_ct1,
        MAX
        ( CASE WHEN ctt.subconto_order = 5
                THEN ctt.href_sub
                ELSE ''
            END
        ) as href_sub_ct2,
        MAX
        ( CASE WHEN ctt.subconto_order = 6
                THEN ctt.href_sub
                ELSE ''
            END
        ) as href_sub_ct3,
        ctt.strategy_id,
        ctt.incoming_order_id,
        ctt.oper_date,
        MAX( ctt.contragent_name ) as contragent_name,
        MAX( ctt.individual_id ) as individual_id,
        MAX( ctt.entity_id ) as entity_id,
        MAX( ctt.citizenship_country_code ) as citizenship_country_code,
        MAX( ctt.contract_id ) as contract_id,
        MAX( ctt.pension_schema_id ) as pension_schema_id
    FROM cte_transaction_text ctt
    GROUP BY
        ctt.eps_detail_id,
        ctt.operation_id,
        ctt.strategy_id,
        ctt.incoming_order_id,
        ctt.oper_date        
)
,
cte_oper_sum AS
( -- соединение транзакций по операциям + номер строки (проводка ЕПС)
    SELECT
        cts.operation_id,
        cts.eps_detail_id,
        MAX( cts.operation_sum ) as operation_sum,
        ed.account_dt,
        cts.kind_sub_dt1,
        cts.kind_sub_dt2,
        cts.kind_sub_dt3,
        ed.account_ct,
        cts.kind_sub_ct1,
        cts.kind_sub_ct2,
        cts.kind_sub_ct3,
        cts.strategy_id,
        cts.incoming_order_id,
        cts.oper_date,
        MAX( cts.href_sub_dt1 ) as href_sub_dt1,
        MAX( cts.href_sub_dt2 ) as href_sub_dt2,
        MAX( cts.href_sub_dt3 ) as href_sub_dt3,
        MAX( cts.href_sub_ct1 ) as href_sub_ct1,
        MAX( cts.href_sub_ct2 ) as href_sub_ct2,
        MAX( cts.href_sub_ct3 ) as href_sub_ct3,
        MAX( cts.individual_id ) as individual_id,
        MAX( cts.entity_id ) as entity_id,
        MAX( cts.citizenship_country_code ) as citizenship_country_code,
        MAX( cts.contract_id ) as contract_id,
        MAX( cts.pension_schema_id ) as pension_schema_id,
        MAX( cts.contragent_name ) as contragent_name
    FROM cte_transaction_subconto cts
        INNER JOIN ourpension.eps_detail ed
        ON ed.id = cts.eps_detail_id
    GROUP BY
        cts.operation_id,
        cts.eps_detail_id,
        ed.account_dt,
        cts.kind_sub_dt1,
        cts.kind_sub_dt2,
        cts.kind_sub_dt3,
        ed.account_ct,
        cts.kind_sub_ct1,
        cts.kind_sub_ct2,
        cts.kind_sub_ct3,
        cts.strategy_id,
        cts.incoming_order_id,
        cts.oper_date
)
SELECT
    e.id as eps_id,
    c_os.eps_detail_id,
    e.code as eps_code,
    e.service_type,
    e.name as eps_name,
    ed.line_number,
    e.contract_model_label,
    ed.account_dt,
    c_os.kind_sub_dt1,
    c_os.kind_sub_dt2,
    c_os.kind_sub_dt3,
    ed.account_ct,
    c_os.kind_sub_ct1,
    c_os.kind_sub_ct2,
    c_os.kind_sub_ct3,
    ed.oper_content,
    ed.is_main_transaction,
    ed.trans_description,
    c_os.operation_id,
    c_os.oper_date::date as operation_date,
    c_os.operation_sum,
    CASE
        WHEN c_os.operation_id IS NOT NULL THEN ot.name
        WHEN c_os.incoming_order_id IS NOT NULL THEN CONCAT( 'ВХ ПП ', bs."number", ' от ', TO_CHAR( bs."date",'dd.MM.yyyy' ) )
    END as operation_name,
    c_os.strategy_id,
    e.classification as classification_id,
    c_os.individual_id,
    c_os.entity_id,
    c_os.citizenship_country_code,
    c_os.contract_id,
    c_os.pension_schema_id,
    c_os.incoming_order_id,
    bs."number" as incoming_order_number,
    c_os.contragent_name,
    e.periodicity,
    c_os.href_sub_dt1,
    c_os.href_sub_dt2,
    c_os.href_sub_dt3,
    c_os.href_sub_ct1,
    c_os.href_sub_ct2,
    c_os.href_sub_ct3,
    bs.payer_account_number as account_number
FROM cte_oper_sum c_os
    INNER JOIN ourpension.eps_detail ed
    ON ed.id = c_os.eps_detail_id
    INNER JOIN ourpension.eps e
    ON e.id = ed.eps_id
    LEFT JOIN back_office.operation o
    ON o.id = c_os.operation_id    
    LEFT JOIN back_office.operation_type ot
    ON ot.id = o.operation_type_id
    LEFT JOIN "data".bank_statement_load_data bs    
    ON bs.payorderid = c_os.incoming_order_id
;
END;
$function$
;