---

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
    t.period_date >= '2024-01-01'
    AND t.period_date <= '2024-12-31'
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
    t.period_date >= '2024-01-01'
    AND t.period_date <= '2024-12-31'