---

WITH base AS (
    SELECT
        e.name AS schema_name,
        s.name AS strategy_name,
        t.value
    FROM back_office.eps_transaction et
    JOIN back_office.eps_transaction_source ets
        ON ets.id = et.eps_transaction_source_id
    JOIN ourpension.eps e
        ON e.id = et.eps_id
    JOIN back_office.transaction t
        ON t.operation_id = ets.operation_id
    JOIN back_office.incoming_order io
        ON io.id = ets.incoming_order_id
    JOIN ourpension.invest_strategy_history ish
        ON ish.contract_id = io.contract_id
        AND t.period_date BETWEEN ish.begin_date
            AND COALESCE(ish.end_date, 'infinity'::timestamp)
    JOIN prod_line.hb_invest_strategy s
        ON s.id = ish.strategy_id
    WHERE 1 = 1
    #if ("$!date_from" != "")
        AND t.period_date >= TO_DATE(:date_from, 'DD.MM.YYYY')
    #end
    #if ("$!date_to" != "")
        AND t.period_date <= TO_DATE(:date_to, 'DD.MM.YYYY')
    #end
    #if ("$!schema_index" != "")
        AND e.code = :schema_index
    #end
    #if ("$!strategy" != "")
        AND s.id = CAST(:strategy AS bigint)
    #end
),
strategies_report AS (
    SELECT
        schema_name,
        SUM(CASE WHEN strategy_name = 'Базовая (БОЛ)'                 THEN value ELSE 0 END) AS bazovaya_bol,
        SUM(CASE WHEN strategy_name = 'Базовая (БУД)'                 THEN value ELSE 0 END) AS bazovaya_bud,
        SUM(CASE WHEN strategy_name = 'Базовая (ДБ)'                  THEN value ELSE 0 END) AS bazovaya_db,
        SUM(CASE WHEN strategy_name = 'Базовая 2024 (БОЛ)'            THEN value ELSE 0 END) AS bazovaya_2024_bol,
        SUM(CASE WHEN strategy_name = 'Базовая 2024 (БУД)'            THEN value ELSE 0 END) AS bazovaya_2024_bud,
        SUM(CASE WHEN strategy_name = 'Базовая 2024 (ДБ)'             THEN value ELSE 0 END) AS bazovaya_2024_db,
        SUM(CASE WHEN strategy_name = 'Базовая 2024 (ОПФ)'            THEN value ELSE 0 END) AS bazovaya_2024_opf,
        SUM(CASE WHEN strategy_name = 'Базовая 2024 (ПЕР)'            THEN value ELSE 0 END) AS bazovaya_2024_per,
        SUM(CASE WHEN strategy_name = 'Базовая ПДС (БОЛ)'             THEN value ELSE 0 END) AS bazovaya_pds_bol,
        SUM(CASE WHEN strategy_name = 'Базовая ПДС (БУД)'             THEN value ELSE 0 END) AS bazovaya_pds_bud,
        SUM(CASE WHEN strategy_name = 'Базовая ПДС (ДБ)'              THEN value ELSE 0 END) AS bazovaya_pds_db,
        SUM(CASE WHEN strategy_name = 'Базовая ПДС (ОПФ)'             THEN value ELSE 0 END) AS bazovaya_pds_opf,
        SUM(CASE WHEN strategy_name = 'Базовая ПДС (ПЕР)'             THEN value ELSE 0 END) AS bazovaya_pds_per,
        SUM(CASE WHEN strategy_name = 'Базовая ПДС (ТС)'              THEN value ELSE 0 END) AS bazovaya_pds_ts,
        SUM(CASE WHEN strategy_name = 'Базовая ПДС (ФЕД)'             THEN value ELSE 0 END) AS bazovaya_pds_fed,
        SUM(CASE WHEN strategy_name = 'Базовый (ОПФ)'                 THEN value ELSE 0 END) AS bazovyy_opf,
        SUM(CASE WHEN strategy_name = 'Базовый (ПЕР)'                 THEN value ELSE 0 END) AS bazovyy_per,
        SUM(CASE WHEN strategy_name = 'Гарант_2027_ПДС (БУД)'        THEN value ELSE 0 END) AS garant_2027_pds_bud,
        SUM(CASE WHEN strategy_name = 'Гарант_2027_ПДС (ДБ)'         THEN value ELSE 0 END) AS garant_2027_pds_db,
        SUM(CASE WHEN strategy_name = 'Консервативная (Будущее)'      THEN value ELSE 0 END) AS konservativnaya,
        SUM(CASE WHEN strategy_name = 'Общий (старое НПО)'            THEN value ELSE 0 END) AS obshiy_npo,
        SUM(CASE WHEN strategy_name = 'С гарантированной доходностью' THEN value ELSE 0 END) AS guaranteed,
        SUM(CASE WHEN strategy_name = 'Сбалансированная'              THEN value ELSE 0 END) AS sbalansirovannaya,
        SUM(CASE WHEN strategy_name = 'Сбалансированная_2024'         THEN value ELSE 0 END) AS sbalansirovannaya_2024
    FROM base
    GROUP BY schema_name
)
SELECT
    schema_name,
    bazovaya_bol,
    bazovaya_bud,
    bazovaya_db,
    bazovaya_2024_bol,
    bazovaya_2024_bud,
    bazovaya_2024_db,
    bazovaya_2024_opf,
    bazovaya_2024_per,
    bazovaya_pds_bol,
    bazovaya_pds_bud,
    bazovaya_pds_db,
    bazovaya_pds_opf,
    bazovaya_pds_per,
    bazovaya_pds_ts,
    bazovaya_pds_fed,
    bazovyy_opf,
    bazovyy_per,
    garant_2027_pds_bud,
    garant_2027_pds_db,
    konservativnaya,
    obshiy_npo,
    guaranteed,
    sbalansirovannaya,
    sbalansirovannaya_2024
FROM strategies_report
ORDER BY schema_name