---

-- DROP FUNCTION back_office.set_eps_transaction(int4, int4, bool);

CREATE OR REPLACE FUNCTION back_office.set_eps_transaction(p_operation_id integer DEFAULT NULL::integer, p_incoming_order_id integer DEFAULT NULL::integer, p_is_default boolean DEFAULT true)
 RETURNS TABLE(success boolean, eps_transaction_source_id integer, eps_transaction_id integer)
 LANGUAGE plpgsql
AS $function$
-- ===================================================================================
-- Запись данных витрины ЕПС при синхронизации с операциями и платежными поручениями
-- Параметры:
-- p_operation_id - ИД операции
-- p_incoming_order_id - ИД входящего платежного поручения
-- Создание: 22.09.2026 dv.pakhomov
-- ===================================================================================
DECLARE 
    v_eps_id integer;
    v_eps_transaction_source_id integer;
BEGIN
    DROP TABLE IF EXISTS tmp_eps_transactions;

    CREATE TEMP TABLE IF NOT EXISTS tmp_eps_transactions
    (
        eps_transaction_source_id integer,
        eps_transaction_id integer,
        eps_id integer,
        success boolean GENERATED ALWAYS AS ( eps_transaction_source_id IS NOT NULL 
            AND eps_transaction_id IS NOT NULL ) STORED
    )
    ;

    -- раскраска витрины ЕПС для операции
    IF p_operation_id IS NOT NULL
    THEN    
        INSERT INTO back_office.eps_transaction_source 
        ( operation_id )
        SELECT o.id  
        FROM back_office.operation o 
            INNER JOIN back_office.eps_operation_type eot 
            ON eot.operation_type_id = o.operation_type_id
            INNER JOIN ourpension.eps e 
            ON e.id = eot.eps_id 
        WHERE p_operation_id IS NOT NULL
            AND o.id = p_operation_id
            AND CURRENT_DATE 
                BETWEEN e.start_date AND COALESCE( e.end_date, CURRENT_DATE )            
        GROUP BY o.id
        RETURNING id
        INTO v_eps_transaction_source_id;
        
        IF v_eps_transaction_source_id IS NOT NULL
        THEN
            WITH cte_insert_eps_transaction AS
            (   -- добавление в транзакции ЕПС
                INSERT INTO back_office.eps_transaction
                (   eps_transaction_source_id,
                    eps_id,
                    oper_date,
                    operation_sum,
                    contract_id_dt,
                    individual_id_dt,
                    entity_id_dt,
                    contract_id_ct,
                    individual_id_ct,
                    entity_id_ct
                )
                SELECT 
                    v_eps_transaction_source_id,
                    eot.eps_id,
                    o.effective_date::timestamp as oper_date,
                    t."value" as operation_sum,
                    a_dt.owner_contract_id as contract_id_dt,
                    a_dt.owner_individual_id as individual_id_dt,
                    a_dt.owner_entity_id as entity_id_dt,
                    a_ct.owner_contract_id as contract_id_ct,
                    a_ct.owner_individual_id as individual_id_ct,
                    a_ct.owner_entity_id as entity_id_ct
                FROM back_office.operation o                
                    INNER JOIN back_office.eps_operation_type eot 
                    ON eot.operation_type_id = o.operation_type_id
                    INNER JOIN back_office.transaction t
                    ON t.operation_id = o.id
                    LEFT JOIN back_office.account a_dt 
                    ON a_dt.id = t.credit_account_id
                    LEFT JOIN back_office.account a_ct 
                    ON a_ct.id = t.debit_account_id
                WHERE o.id = p_operation_id
                RETURNING 
                    id as eps_transaction_id,
                    eps_id
            )
            -- добавление в возвращаемый набор данных
            INSERT INTO tmp_eps_transactions
            (   eps_transaction_source_id,            
                eps_transaction_id,            
                eps_id            
            )
            SELECT
                v_eps_transaction_source_id as eps_transaction_source_id,
                ciet.eps_transaction_id,
                ciet.eps_id
            FROM cte_insert_eps_transaction ciet
            ;
        END IF;
    END IF;

    -- раскраска витрины ЕПС для платежки
    IF p_incoming_order_id IS NOT NULL
    THEN    
        -- попытка определить значение ЕПС исходя из связи типа платежа с кодом ЕПС
        INSERT INTO tmp_eps_transactions
        ( eps_id )
        SELECT ote.eps_id
        FROM back_office.incoming_order io
            INNER JOIN back_office.order_payment_type_eps ote 
            ON ote.order_payment_type_id = io.order_payment_type_id
            INNER JOIN ourpension.eps e 
            ON e.id = ote.eps_id 
        WHERE io.id = p_incoming_order_id
            AND CURRENT_DATE 
                BETWEEN e.start_date AND COALESCE( e.end_date, CURRENT_DATE );
           
        IF p_is_default -- подстановка по умолчанию включена
            AND NOT EXISTS
                (   SELECT 1
                    FROM tmp_eps_transactions
                    LIMIT 1
                ) -- определить значение ЕПС по-хорошему не получилось
        THEN
            -- определяем значение по умолчанию для вида деятельности
            WITH cte_eps_service_type AS
            (   -- Коды ЕПС по умолчанию, соответствующие видам деятельности
                SELECT *
                FROM
                    (   VALUES
                        (   'PDS',          '4002' ),
                        (   'NPO',          '4001' ),
                        (   'OPS',          '3002' )
                    ) v (   service_type,   eps_code )
            )
            INSERT INTO tmp_eps_transactions
            ( eps_id )
            SELECT e.id
            FROM back_office.incoming_order io
                INNER JOIN back_office.order_payment_type ot 
                ON ot.id = io.order_payment_type_id
                INNER JOIN cte_eps_service_type cest
                ON cest.service_type = ot."service_type"
                INNER JOIN ourpension.eps e 
                ON e.code = cest.eps_code 
            WHERE io.id = p_incoming_order_id;
        END IF;

        IF EXISTS
            (   SELECT 1
                FROM tmp_eps_transactions
                LIMIT 1
            ) -- определить значение ЕПС получилось
        THEN
            -- добавление основания движения ЕПС для платежек
            INSERT INTO back_office.eps_transaction_source 
            ( incoming_order_id )
            VALUES
            ( p_incoming_order_id )
            RETURNING id
            INTO v_eps_transaction_source_id;
                
            -- добавление данных движения ЕПС
            WITH cte_insert_eps_transaction AS
            (   INSERT INTO back_office.eps_transaction
                (   eps_transaction_source_id,
                    eps_id,
                    oper_date,
                    operation_sum,
                    contract_id_ct,
                    individual_id_ct,
                    entity_id_ct
                )
                SELECT 
                    v_eps_transaction_source_id, 
                    et.eps_id,
                    CAST( io."date" AS timestamp ) as oper_date,
                    io.value as operation_sum,
                    io.contract_id as contract_id_ct,
                    io.individual_id as individual_id_ct,
                    io.entity_id as entity_id_ct
                FROM back_office.incoming_order io
                    CROSS JOIN tmp_eps_transactions et
                WHERE io.id = p_incoming_order_id
                RETURNING 
                    id,
                    eps_id
            )
            -- фиксируем данные для возврата
            UPDATE tmp_eps_transactions
            SET 
                eps_transaction_source_id = v_eps_transaction_source_id,
                eps_transaction_id = ciet.id
            FROM cte_insert_eps_transaction ciet
            WHERE ciet.eps_id = tmp_eps_transactions.eps_id;
        END IF;
    END IF;
    
    IF NOT EXISTS
        (   SELECT 1
            FROM tmp_eps_transactions
            LIMIT 1
        ) -- с окраской не получилось
    THEN
        -- добавляем пустую запись
        INSERT INTO tmp_eps_transactions
        ( eps_transaction_source_id )
        VALUES
        ( NULL );
    END IF;    

    RETURN QUERY
    SELECT
        et.success,
        et.eps_transaction_source_id,
        et.eps_transaction_id
    FROM tmp_eps_transactions et
    ;
        
END;
$function$
;