---

WITH base AS (
    SELECT
        COALESCE(NULLIF(ps.index_scheme, ''), ps.number, ps.name) AS schema_name,
        s.name AS strategy_name,
        b.total_credit - b.total_debit AS value
    FROM back_office.account a
    JOIN back_office.balance b
        ON b.account_id = a.id

    JOIN ourpension.sharer sh
        ON sh.id = a.owner_sharer_id

    JOIN ourpension.contract c
        ON c.id = sh.contract_id

    JOIN ourpension.pension_scheme ps
        ON ps.id = sh.scheme_id

    JOIN ourpension.invest_strategy_history ish
        ON ish.contract_id = c.id

    JOIN prod_line.hb_invest_strategy s
        ON s.id = ish.strategy_id

    WHERE 1 = 1

      AND NULLIF($P{date_to}, '') IS NOT NULL

      AND TO_DATE($P{date_to}, 'DD.MM.YYYY') BETWEEN b.start_date AND b.end_date

      AND TO_DATE($P{date_to}, 'DD.MM.YYYY') BETWEEN ish.begin_date
          AND COALESCE(ish.end_date, CAST('infinity' AS timestamp))

      AND (
          NULLIF($P{schema_index}, '') IS NULL
          OR ps.id = CAST(NULLIF($P{schema_index}, '') AS integer)
      )

      AND (
          NULLIF($P{strategy}, '') IS NULL
          OR s.id = CAST(NULLIF($P{strategy}, '') AS bigint)
      )
),
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,

        SUM(value) AS total,
        0 AS sort_order
    FROM base
    GROUP BY schema_name

    UNION ALL

    SELECT
        'Итого:' AS 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,

        SUM(value) AS total,
        1 AS sort_order
    FROM base
)
SELECT
    sr.schema_name AS schema_name,
    sr.bazovaya_bol AS bazovaya_bol,
    sr.bazovaya_db AS bazovaya_db,
    sr.bazovaya_bud AS bazovaya_bud,
    sr.bazovaya_2024_bud AS bazovaya_2024_bud,
    sr.bazovaya_2024_bol AS bazovaya_2024_bol,
    sr.bazovaya_2024_db AS bazovaya_2024_db,
    sr.bazovaya_2024_opf AS bazovaya_2024_opf,
    sr.bazovaya_2024_per AS bazovaya_2024_per,
    sr.bazovaya_pds_bol AS bazovaya_pds_bol,
    sr.bazovaya_pds_bud AS bazovaya_pds_bud,
    sr.bazovaya_pds_db AS bazovaya_pds_db,
    sr.bazovaya_pds_opf AS bazovaya_pds_opf,
    sr.bazovaya_pds_per AS bazovaya_pds_per,
    sr.bazovaya_pds_ts AS bazovaya_pds_ts,
    sr.bazovaya_pds_fed AS bazovaya_pds_fed,
    sr.bazovyy_opf AS bazovyy_opf,
    sr.bazovyy_per AS bazovyy_per,
    sr.garant_2027_pds_bud AS garant_2027_pds_bud,
    sr.garant_2027_pds_db AS garant_2027_pds_db,
    sr.konservativnaya AS konservativnaya,
    sr.obshiy_npo AS obshiy_npo,
    sr.guaranteed AS guaranteed,
    sr.sbalansirovannaya AS sbalansirovannaya,
    sr.sbalansirovannaya_2024 AS sbalansirovannaya_2024,
    sr.total AS total
FROM strategies_report sr
ORDER BY
    sr.sort_order,
    sr.schema_name