WITH requests AS (
    SELECT
        rf.id,
        fin_requests_fd.get_first_request_id(rf.id) as parent_id
    FROM fin_requests_fd.requests_fd rf
    WHERE rf.deleted_at IS NULL
      AND rf.decision_date BETWEEN :dateFrom AND :dateTo
      AND (NOT (:hasStages :: BOOL) OR rf.stage_id = ANY (ARRAY[:stageIds] :: INT[]))
), unique_requests AS (
    SELECT DISTINCT ON (parent_id) *
    FROM requests
    ORDER BY parent_id, id DESC
), requests_with_parent AS (
    SELECT id, parent_id FROM unique_requests
    UNION
    SELECT parent_id AS id, null AS parent_id FROM unique_requests
    WHERE id <> parent_id
), cte_req_by_branch AS (
    WITH RECURSIVE cte_recursive_requests AS (
        SELECT
            fin_requests_fd.get_first_request_id(rf.id) AS parent_id,
            rf.id,
            rf.parent_id AS local_parent
        FROM fin_requests_fd.requests_fd rf
                 JOIN unique_requests r ON r.parent_id = rf.id
        UNION ALL
        SELECT
            fin_requests_fd.get_first_request_id(rf.id) AS parent_id,
            rf.id,
            rf.parent_id AS local_parent
        FROM fin_requests_fd.requests_fd rf
                 JOIN cte_recursive_requests ON rf.parent_id = cte_recursive_requests.id
    )
    SELECT * FROM cte_recursive_requests
), transfer_decisions AS (
    SELECT
        cte_req_by_branch.parent_id,
        sum(coalesce(td.amount,0)) FILTER (WHERE refs.code  = :typeTransfer)                                   AS amount_transfer,
        sum(coalesce(td.amount,0)) FILTER
            (WHERE
                    refs.code = :typeTransfer
                AND program_type.code = :typeOverhaulRelocation
                AND td.is_overhaul
            )                                                                                                  AS amount_transfer_overhaul,
        sum(coalesce(td.amount,0)) FILTER
            (WHERE
                    refs.code = :typeTransfer
                AND program_type.code = :typeOverhaulRelocation
                AND NOT td.is_overhaul
            )                                                                                                  AS amount_transfer_relocation,
        sum(rgkh_summ_nullable(td.sum_of_work,td.sum_on_percent)) FILTER (WHERE refs.code  = :typeTransfer)    AS amount_transfer_mski,
        sum(coalesce(td.amount,0)) FILTER (WHERE refs.code  = :typeRefund)                                     AS amount_refund,
        sum(coalesce(td.amount,0)) FILTER
            (WHERE
                    refs.code  = :typeRefund
                AND program_type.code = :typeOverhaulRelocation
                AND td.is_overhaul
            )                                                                                                  AS amount_refund_overhaul,
        sum(coalesce(td.amount,0)) FILTER
            (WHERE
                    refs.code  = :typeRefund
                AND program_type.code = :typeOverhaulRelocation
                AND NOT td.is_overhaul
            )                                                                                                  AS amount_refund_relocation,
        sum(rgkh_summ_nullable(td.sum_of_work,td.sum_on_percent)) FILTER (WHERE refs.code  = :typeRefund)      AS amount_refund_mski,
        sum(coalesce(td.amount,0)) FILTER (WHERE refs.code  = :typePenalty AND td.ps_changes)                  AS amount_ps_changes,
        sum(coalesce(td.amount,0)) FILTER (WHERE refs.code  = :typePenalty AND td.ms_changes)                  AS amount_ms_changes,
        sum(coalesce(td.amount,0)) FILTER (WHERE refs.code  = :typePenalty AND td.kr_changes)                  AS amount_kr_changes,
        sum(coalesce(td.amount,0)) FILTER (WHERE refs.code  = :typePenalty AND td.md_changes)                  AS amount_md_changes
    FROM cte_req_by_branch
             JOIN fin_requests_fd.transfer_decisions td ON td.request_fd_id = cte_req_by_branch.id
             JOIN fin_requests_fd.requests_fd rf ON rf.id = cte_req_by_branch.parent_id
             JOIN refs program_type ON program_type.id = rf.program_type_id
             LEFT JOIN refs ON refs.id = td.payment_type_id
    WHERE
        td.payment_document_date BETWEEN :dateFrom AND :dateTo
    GROUP BY cte_req_by_branch.parent_id
), cte_td_and_penalty_by_progr_type AS (
    SELECT
        requests_with_parent.parent_id,
        rf.id,
        rf.geo_tag_id,
        rf.stage_id,
        sum(coalesce(t.amount_ps_changes,0))                                                        AS amount_ps_changes,
        sum(coalesce(t.amount_ms_changes,0))                                                        AS amount_ms_changes,
        sum(coalesce(t.amount_kr_changes,0))                                                        AS amount_kr_changes,
        sum(coalesce(t.amount_md_changes,0))                                                        AS amount_md_changes,


        rgkh_summ_nullable(
                        sum(t.amount_transfer)
                        FILTER
                            (WHERE
                                    r.code like 'i.financing.program_type.%overhaul%'
                                AND NOT r.code = :typeOverhaulRelocation
                            )
            ,
                        sum(t.amount_transfer_overhaul)
                        FILTER (WHERE r.code = :typeOverhaulRelocation)
            )                                                                                           AS field_11,

        rgkh_summ_nullable(
                        sum(t.amount_refund)
                        FILTER
                            (WHERE
                                    r.code like 'i.financing.program_type.%overhaul%'
                                AND NOT r.code = :typeOverhaulRelocation
                            )
            ,
                        sum(t.amount_refund_overhaul)
                        FILTER (WHERE r.code = :typeOverhaulRelocation)
            )                                                                                           AS field_12,

        rgkh_summ_nullable(
                        sum(t.amount_transfer)
                        FILTER
                            (WHERE
                                    r.code like 'i.financing.program_type.%relocation%'
                                AND NOT r.code = :typeOverhaulRelocation
                            )
            ,
                        sum(t.amount_transfer_relocation)
                        FILTER (WHERE r.code = :typeOverhaulRelocation)
            )                                                                                           AS field_19,

        rgkh_summ_nullable(
                        sum(t.amount_refund)
                        FILTER
                            (WHERE
                                    r.code like 'i.financing.program_type.%relocation%'
                                AND NOT r.code = :typeOverhaulRelocation
                            )
            ,
                        sum(t.amount_refund_relocation)
                        FILTER (WHERE r.code = :typeOverhaulRelocation)
            )                                                                                           AS field_20,

        sum(coalesce(t.amount_transfer,0)) FILTER (WHERE r.code = :typeLowRise)                     AS field_28,
        sum(coalesce(t.amount_refund,0)) FILTER (WHERE r.code = :typeLowRise)                       AS field_29,

        sum(coalesce(t.amount_transfer_mski,0)) FILTER (WHERE r.code = :typeMSKI)                   AS field_35,
        sum(coalesce(t.amount_refund_mski,0)) FILTER (WHERE r.code = :typeMSKI)                     AS field_36
    FROM fin_requests_fd.requests_fd rf
             JOIN requests_with_parent ON rf.id = requests_with_parent.id
             JOIN refs r ON r.id = rf.program_type_id
             LEFT JOIN transfer_decisions t ON t.parent_id = rf.id
    GROUP BY rf.id, requests_with_parent.parent_id, rf.stage_id
), request_current AS (
    SELECT
        requests_with_parent.parent_id,
        rf.id,
        rf.geo_tag_id,
        rf.stage_id,
        sum(coalesce(rfm.amount_of_funds,0)) FILTER (WHERE r.code = :typeOverhaul
            OR (r.code like 'i.financing.program_type.%overhaul%'
                AND r1.code = :typeOverhaul
                                                               ))                                                   AS field_6,
        sum(coalesce(rfm.amount_of_subject,0)) FILTER (WHERE r.code = :typeOverhaul
            OR (r.code like 'i.financing.program_type.%overhaul%'
                AND r1.code = :typeOverhaul
                                                                 ))                                                 AS field_7,
        sum(coalesce(rfm.amount_of_mo,0)) FILTER (WHERE r.code = :typeOverhaul
            OR (r.code like 'i.financing.program_type.%overhaul%'
                AND r1.code = :typeOverhaul
                                                            ))                                                      AS field_8,
        sum(coalesce(rfm.amount_of_owner,0)) FILTER (WHERE r.code = :typeOverhaul
            OR (r.code like 'i.financing.program_type.%overhaul%'
                AND r1.code = :typeOverhaul
                                                               ))                                                   AS field_9,

        sum(coalesce(rfm.amount_of_funds,0)) FILTER (WHERE r.code = :typeRelocation
            OR (r.code like 'i.financing.program_type.%relocation%'
                AND r1.code = :typeRelocation
                                                               ))                                                   AS field_15,
        sum(coalesce(rfm.amount_of_subject,0)) FILTER (WHERE r.code = :typeRelocation
            OR (r.code like 'i.financing.program_type.%relocation%'
                AND r1.code = :typeRelocation
                                                                 ))                                                 AS field_16,
        sum(coalesce(rfm.amount_of_mo,0)) FILTER (WHERE r.code = :typeRelocation
            OR (r.code like 'i.financing.program_type.%relocation%'
                AND r1.code = :typeRelocation
                                                            ))                                                      AS field_17,

        sum(coalesce(rfm.amount_of_funds,0)) FILTER (WHERE r.code = :typeStimulation)                               AS field_22,

        sum(coalesce(rfm.amount_of_funds,0)) FILTER (WHERE r.code = :typeLowRise)                                   AS field_24,
        sum(coalesce(rfm.amount_of_subject,0)) FILTER (WHERE r.code = :typeLowRise)                                 AS field_25,
        sum(coalesce(rfm.amount_of_mo,0)) FILTER (WHERE r.code = :typeLowRise)                                      AS field_26,

        CASE WHEN r.code = :typeMSKI
                 THEN rf.program_sum
             ELSE 0 END                                                                                                  AS program_sum,
        sum(coalesce(rfm.amount_of_subject,0)) FILTER (WHERE r.code = :typeMSKI)                                    AS field_33,
        sum(coalesce(rfm.amount_of_mo,0)) FILTER (WHERE r.code = :typeMSKI)                                         AS field_34
    FROM fin_requests_fd.requests_fd rf
             JOIN refs r ON r.id = rf.program_type_id
             JOIN requests_with_parent ON rf.id = requests_with_parent.id
             LEFT JOIN fin_requests_fd.requests_fd_mo rfm ON rf.id = rfm.request_fd_id AND rfm.deleted_at IS NULL
             LEFT JOIN refs r1 ON r1.id = rfm.program_type_id
    WHERE
        rf.decision_date BETWEEN :dateFrom AND :dateTo
    --  AND rf.id NOT IN (SELECT parent_id FROM requests_with_parent)
    GROUP BY rf.id, r.code, requests_with_parent.parent_id, rf.stage_id
)  SELECT * FROM request_current WHERE id IN (5945, 5944)