WITH cte_granted_geo_tags AS (
    SELECT DISTINCT g.id
    FROM geo_tags g
        JOIN geo_tags gp ON g.lft BETWEEN gp.lft AND gp.rgt
    WHERE gp.id  = ANY(ARRAY[:grantedGeoTagIds]::INT[])
), cte_requests AS (
    SELECT
        fr.id                                                     AS request_id,
        stage.name::INT                                           AS stage,
        fr.registration_date                                      AS registration_date,
        concat_ws(' ',
            'от "' || extract(DAY FROM fr_first.registration_date) :: INT || '"' ||
            ' ' || initcap(get_month_name((extract(MONTH FROM fr_first.registration_date) :: INT), 'genitive')) ||
            ' ' || extract(YEAR FROM fr_first.registration_date) || ' года',
            '№' || fr_first.registration_number
        )                                                         AS requisites
    FROM fin_requests fr
        JOIN refs stage ON stage.id = fr.stage_id
        LEFT JOIN fin_requests fr_first ON fr_first.id = get_fin_request_branch_id(fr.id)
    WHERE fr.id = ANY (ARRAY[:requestIds] :: INT[])
), cte_provided AS (
    SELECT
        r.request_id,
        sum(frfmi.reloc_sq_total)           AS mo_reloc_sq_total,
        sum(frfmi.reloc_quarters_total)     AS mo_reloc_quarters_total,
        sum(frfmi.reloc_residents_total)    AS mo_reloc_residents_total,
        sum(frfmi.total_financing)          AS mo_total_financing,
        sum(frfmi.fond_financing)           AS mo_fond_financing,
        sum(frfmi.estimated_savings_budget) AS mo_estimated_savings_budget,
        sum(frfmi.compensation_total)       AS mo_compensation_total
    FROM cte_requests r
        JOIN fin_requests_for_municipal_organizations frfmo ON frfmo.financing_request_id = r.request_id AND frfmo.deleted_at IS NULL
        JOIN fin_requests_for_mo_info frfmi ON frfmi.municipal_organization_id = frfmo.id
    WHERE exists(SELECT 1 FROM cte_granted_geo_tags where id = frfmo.geo_tag_id)
    GROUP BY r.request_id
), cte_implemented AS (
    WITH cte_contracts AS (
        SELECT
            nrc.id                                                    AS id,
            r.request_id                                              AS request_id,
            nrc.total_amount_of_funds                                 AS total_amount_of_funds,
            nrc.total_amount_of_funds_subject                         AS total_amount_of_funds_subject,
            nrc.total_amount_of_funds_mo                              AS total_amount_of_funds_mo,
            nrc.total_amount_of_extrabudgetary_funds                  AS total_amount_of_extrabudgetary_funds,
            nrcq.house_quarter_id                                     AS quarters_id,
            nrcq.house_resettlement_id                                AS house_resettlement_id,
            nrc.is_acquired                                           AS is_acquired
        FROM cte_requests r
            JOIN new_reloc_contracts_quarters nrcq ON nrcq.fin_request_id = r.request_id
            JOIN new_reloc_contracts nrc ON nrcq.contract_id = nrc.id AND nrc.deleted_at IS NULL
            JOIN statuses contractStatus ON contractStatus.id = nrc.status_id
        WHERE contractStatus.code NOT IN (:statusDraft, :statusTerminated, :statusinArchive)
            AND exists(SELECT 1 FROM cte_granted_geo_tags where id = nrc.mo_geo_tag_id)
    ), cte_contracts_without_quarters AS (
        SELECT
            r.request_id                                  AS request_id,
            sum(nrc.quarters_square)                      AS quarters_square,
            sum(nrc.quarters_amount)                      AS quarters_amount,
            sum(rgkh_summ_nullable(
                nrc.total_amount_of_funds,
                nrc.total_amount_of_funds_subject,
                nrc.total_amount_of_funds_mo
            ))                                            AS total_relocatoion_cost,
            sum(nrc.total_amount_of_funds)                AS fund_cost,
            sum(nrc.total_amount_of_extrabudgetary_funds) AS extrabudgetary_cost
        FROM cte_requests r
            JOIN new_reloc_contracts nrc ON nrc.fin_request_id = r.request_id AND nrc.deleted_at IS NULL
            JOIN statuses contractStatus ON contractStatus.id = nrc.status_id
        WHERE contractStatus.code NOT IN (:statusDraft, :statusTerminated, :statusinArchive)
            AND exists(SELECT 1 FROM cte_granted_geo_tags where id = nrc.mo_geo_tag_id)
        GROUP BY  r.request_id
    ), cte_quarters AS (
        SELECT
            ccd.request_id,
            alarm_hq.square                            AS hq_suare,
            alarm_hq.residents_count                   AS hq_residents_count,
            alarm_hq.id                                AS hq_id,
            CASE WHEN NOT ccd.is_acquired
                THEN rgkh_summ_nullable(
                    ccd.total_amount_of_funds,
                    ccd.total_amount_of_funds_subject,
                    ccd.total_amount_of_funds_mo
                )
                ELSE rgkh_summ_nullable(
                    building_hs.amount_of_funds,
                    building_hs.amount_of_funds_subject,
                    building_hs.amount_of_funds_mo
                )
            END                                         AS total_relocatoion_cost,
            CASE WHEN NOT ccd.is_acquired
                THEN ccd.total_amount_of_funds
                ELSE building_hs.amount_of_funds
            END                                         AS fund_cost,
            CASE WHEN NOT ccd.is_acquired
                THEN ccd.total_amount_of_extrabudgetary_funds
                ELSE building_hs.amount_of_extrabudgetary_funds
            END                                         AS extrabudgetary_cost,
            building_hs.compensation_total              AS compensation_total
        FROM cte_contracts ccd
            JOIN house_resettlements alarm_hs ON ccd.house_resettlement_id = alarm_hs.id AND alarm_hs.deleted_at IS NULL
            JOIN house_quarters alarm_hq ON alarm_hq.id = alarm_hs.quarters_id AND alarm_hq.deleted_at IS NULL
            JOIN refs resettlementStatus ON resettlementStatus.id = alarm_hq.resettlement_status_id
            LEFT JOIN house_resettlements building_hs ON ccd.quarters_id = building_hs.quarters_id AND building_hs.deleted_at IS NULL
        WHERE resettlementStatus.code = :statusResettled
           OR exists(SELECT 1 FROM house_quarter_lawsuits WHERE quarters_id = alarm_hq.id AND lawsuit_date_end IS NULL)
    ), cte_prepared_data AS (
        SELECT
            request_id                  AS request_id,
            sum(hq_suare)               AS hq_suare,
            sum(hq_residents_count)     AS hq_residents_count,
            count(hq_id)                AS hq_count,
            sum(total_relocatoion_cost) AS total_relocatoion_cost,
            sum(fund_cost)              AS fund_cost,
            sum(extrabudgetary_cost)    AS extrabudgetary_cost,
            sum(compensation_total)     AS compensation_total
        FROM cte_quarters
        GROUP BY request_id
    )
    SELECT
        request_id,
        rgkh_summ_nullable(
            hq_suare,
            quarters_square
        )                       AS hq_suare,
        rgkh_summ_nullable(
            hq_count,
            quarters_amount
        )                       AS hq_count,
        hq_residents_count      AS hq_residents_count,
        rgkh_summ_nullable(
            pd.total_relocatoion_cost,
            ccwq.total_relocatoion_cost
        )                       AS total_relocatoion_cost,
        rgkh_summ_nullable(
            pd.fund_cost,
            ccwq.fund_cost
        )                       AS fund_cost,
        rgkh_summ_nullable(
            pd.extrabudgetary_cost,
            ccwq.extrabudgetary_cost
        )                       AS extrabudgetary_cost,
        compensation_total      AS compensation_total
    FROM cte_prepared_data pd
        LEFT JOIN cte_contracts_without_quarters ccwq USING (request_id)
)
SELECT
    1                                                     AS total,
    row_number() OVER (ORDER BY stage, registration_date) AS row,
    r.request_id,
    r.stage,
    r.requisites,
    coalesce(p.mo_reloc_sq_total, 0)           AS mo_reloc_sq_total,
    coalesce(p.mo_reloc_quarters_total, 0)     AS mo_reloc_quarters_total,
    coalesce(p.mo_reloc_residents_total, 0)    AS mo_reloc_residents_total,
    coalesce(p.mo_total_financing, 0)          AS mo_total_financing,
    coalesce(p.mo_fond_financing, 0)           AS mo_fond_financing,
    coalesce(p.mo_estimated_savings_budget, 0) AS mo_estimated_savings_budget,
    coalesce(p.mo_compensation_total, 0)       AS mo_compensation_total,
    coalesce(i.hq_suare, 0)                    AS hq_suare,
    coalesce(i.hq_count, 0)                    AS hq_count,
    coalesce(i.hq_residents_count, 0)          AS hq_residents_count,
    coalesce(i.total_relocatoion_cost, 0)      AS total_relocatoion_cost,
    coalesce(i.fund_cost, 0)                   AS fund_cost,
    coalesce(i.extrabudgetary_cost, 0)         AS extrabudgetary_cost,
    coalesce(i.compensation_total, 0)          AS compensation_total
FROM cte_requests r
    LEFT JOIN cte_provided p USING (request_id)
    LEFT JOIN cte_implemented i USING (request_id)
ORDER BY stage, registration_date