---

SELECT
    e.name AS schema_name,
    SUM(CASE WHEN s.name = 'Базовая (БОЛ)'                THEN t.value ELSE 0 END) AS bazovaya_bol,
    SUM(CASE WHEN s.name = 'Базовая (БУД)'                THEN t.value ELSE 0 END) AS bazovaya_bud,
    SUM(CASE WHEN s.name = 'Базовая (ДБ)'                 THEN t.value ELSE 0 END) AS bazovaya_db,
    SUM(CASE WHEN s.name = 'Базовая 2024 (БОЛ)'           THEN t.value ELSE 0 END) AS bazovaya_2024_bol,
    SUM(CASE WHEN s.name = 'Базовая 2024 (БУД)'           THEN t.value ELSE 0 END) AS bazovaya_2024_bud,
    SUM(CASE WHEN s.name = 'Базовая 2024 (ДБ)'            THEN t.value ELSE 0 END) AS bazovaya_2024_db,
    SUM(CASE WHEN s.name = 'Базовая 2024 (ОПФ)'           THEN t.value ELSE 0 END) AS bazovaya_2024_opf,
    SUM(CASE WHEN s.name = 'Базовая 2024 (ПЕР)'           THEN t.value ELSE 0 END) AS bazovaya_2024_per,
    SUM(CASE WHEN s.name = 'Базовая ПДС (БОЛ)'            THEN t.value ELSE 0 END) AS bazovaya_pds_bol,
    SUM(CASE WHEN s.name = 'Базовая ПДС (БУД)'            THEN t.value ELSE 0 END) AS bazovaya_pds_bud,
    SUM(CASE WHEN s.name = 'Базовая ПДС (ДБ)'             THEN t.value ELSE 0 END) AS bazovaya_pds_db,
    SUM(CASE WHEN s.name = 'Базовая ПДС (ОПФ)'            THEN t.value ELSE 0 END) AS bazovaya_pds_opf,
    SUM(CASE WHEN s.name = 'Базовая ПДС (ПЕР)'            THEN t.value ELSE 0 END) AS bazovaya_pds_per,
    SUM(CASE WHEN s.name = 'Базовая ПДС (ТС)'             THEN t.value ELSE 0 END) AS bazovaya_pds_ts,
    SUM(CASE WHEN s.name = 'Базовая ПДС (ФЕД)'            THEN t.value ELSE 0 END) AS bazovaya_pds_fed,
    SUM(CASE WHEN s.name = 'Базовый (ОПФ)'                THEN t.value ELSE 0 END) AS bazovyy_opf,
    SUM(CASE WHEN s.name = 'Базовый (ПЕР)'                THEN t.value ELSE 0 END) AS bazovyy_per,
    SUM(CASE WHEN s.name = 'Гарант_2027_ПДС (БУД)'       THEN t.value ELSE 0 END) AS garant_2027_pds_bud,
    SUM(CASE WHEN s.name = 'Гарант_2027_ПДС (ДБ)'        THEN t.value ELSE 0 END) AS garant_2027_pds_db,
    SUM(CASE WHEN s.name = 'Консервативная (Будущее)'     THEN t.value ELSE 0 END) AS konservativnaya,
    SUM(CASE WHEN s.name = 'Общий (старое НПО)'           THEN t.value ELSE 0 END) AS obshiy_npo,
    SUM(CASE WHEN s.name = 'С гарантированной доходностью' THEN t.value ELSE 0 END) AS guaranteed,
    SUM(CASE WHEN s.name = 'Сбалансированная'             THEN t.value ELSE 0 END) AS sbalansirovannaya,
    SUM(CASE WHEN s.name = 'Сбалансированная_2024'        THEN t.value ELSE 0 END) AS sbalansirovannaya_2024,
    SUM(t.value)                                                                     AS total
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
    (#date_from# IS NULL OR t.period_date >= TO_DATE(#date_from#, 'DD.MM.YYYY'))
    AND (#date_to# IS NULL OR t.period_date <= TO_DATE(#date_to#, 'DD.MM.YYYY'))
    AND (#schema_index# IS NULL OR e.code = #schema_index#)
    AND (#strategy# IS NULL OR s.id = #strategy#::bigint)
GROUP BY e.name

UNION ALL

SELECT
    'Итого:',
    SUM(CASE WHEN s.name = 'Базовая (БОЛ)'                THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Базовая (БУД)'                THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Базовая (ДБ)'                 THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Базовая 2024 (БОЛ)'           THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Базовая 2024 (БУД)'           THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Базовая 2024 (ДБ)'            THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Базовая 2024 (ОПФ)'           THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Базовая 2024 (ПЕР)'           THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Базовая ПДС (БОЛ)'            THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Базовая ПДС (БУД)'            THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Базовая ПДС (ДБ)'             THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Базовая ПДС (ОПФ)'            THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Базовая ПДС (ПЕР)'            THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Базовая ПДС (ТС)'             THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Базовая ПДС (ФЕД)'            THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Базовый (ОПФ)'                THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Базовый (ПЕР)'                THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Гарант_2027_ПДС (БУД)'       THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Гарант_2027_ПДС (ДБ)'        THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Консервативная (Будущее)'     THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Общий (старое НПО)'           THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'С гарантированной доходностью' THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Сбалансированная'             THEN t.value ELSE 0 END),
    SUM(CASE WHEN s.name = 'Сбалансированная_2024'        THEN t.value ELSE 0 END),
    SUM(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
    (#date_from# IS NULL OR t.period_date >= TO_DATE(#date_from#, 'DD.MM.YYYY'))
    AND (#date_to# IS NULL OR t.period_date <= TO_DATE(#date_to#, 'DD.MM.YYYY'))
    AND (#schema_index# IS NULL OR e.code = #schema_index#)
    AND (#strategy# IS NULL OR s.id = #strategy#::bigint)