---

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
        AND TO_DATE(:date_to, 'DD.MM.YYYY') BETWEEN ish.begin_date
            AND COALESCE(ish.end_date, CAST('infinity' AS timestamp))

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

    WHERE 1 = 1

    #if ("$!date_to" != "")
        AND TO_DATE(:date_to, 'DD.MM.YYYY') BETWEEN b.start_date AND b.end_date
    #end

    #if ("$!date_to" == "")
        AND 1 = 0
    #end

    #if ("$!schema_index" != "")
        AND ps.id = CAST(:schema_index AS integer)
    #end

    #if ("$!strategy" != "")
        AND s.id = CAST(:strategy AS bigint)
    #end
),