WITH cte_under_construction_quarters_data AS (
    SELECT
        DISTINCT ON (hq_to.id)
        nrcq.id                                 AS id,
        hq_to.id                                AS under_construction_quarter_id,
        hq_to.house_quarters_id                 AS emergency_quarter_id,
        hq_to.name                              AS under_construction_quarter_number,
        coalesce(
            hq_to.square,
            hrt_to.planned_providing_area_square
        )                                       AS under_construction_quarter_square,
        coalesce(
            get_address_full(a.full_address),
            ehp.building_address
        )                                       AS under_construction_quarter_address,
        hrt_to.amount_of_funds                  AS amount_of_funds,
        hrt_to.amount_of_funds_subject          AS amount_of_funds_subject,
        hrt_to.amount_of_funds_mo               AS amount_of_funds_mo,
        hrt_to.amount_of_extrabudgetary_funds   AS amount_of_extrabudgetary_funds,
        rgkh_summ_nullable(
            +hrt_to.amount_of_funds,
            +hrt_to.amount_of_funds_subject,
            +hrt_to.amount_of_funds_mo,
            +hrt_to.amount_of_extrabudgetary_funds
        )                                       AS sum_of_funds,
        gt.id                                   AS geo_tag_id,
        gt.name                                 AS geo_tag_name
    FROM new_reloc_contracts nrc
        JOIN new_reloc_contracts_quarters nrcq ON nrcq.contract_id = nrc.id
        JOIN house_quarters hq_to ON hq_to.id = nrcq.house_quarter_id
        JOIN house_quarters_residents_snapshots hqrs ON hq_to.snapshot_id = hqrs.id
        JOIN house_revision hr on hqrs.house_revision_id = hr.id
        LEFT JOIN addresses a ON hr.address_id = a.id
        LEFT JOIN epd_house_passports ehp ON hr.id = ehp.house_revision_id
        LEFT JOIN house_resettlements hrt_to ON hq_to.id = hrt_to.quarters_id AND hrt_to.deleted_at IS NULL
        LEFT JOIN geo_tags gt on hr.geo_tag_id = gt.id
    WHERE nrc.id = :contractId
        AND hq_to.deleted_at IS NULL
    ORDER BY hq_to.id, hrt_to.id
), cte_emergency_quarters_data AS (
    SELECT
        DISTINCT ON (hq_from.id)
        cucqd.under_construction_quarter_id     AS under_construction_quarter_id,
        hq_from.id                              AS emergency_quarter_id,
        hq_from.square                          AS emergency_quarter_square,
        concat_ws(
            ', ',
            coalesce(
                get_address_full(a.full_address),
                ehp.building_address
            ),
            hq_from.name
        )                                       AS emergency_quarter_address_with_number,
        hrt_stage.id                            AS resettlement_stage_id,
        hrt_stage.name                          AS resettlement_stage_name,
        hrt_from.residents_count                AS resettlement_residents,
        hrt_from.is_resident_consent            AS is_resettlement_resident_consent
    FROM house_quarters hq_from
        JOIN cte_under_construction_quarters_data cucqd ON cucqd.emergency_quarter_id = hq_from.id
        JOIN house_quarters_residents_snapshots hqrs ON hq_from.snapshot_id = hqrs.id
        JOIN house_revision hr on hqrs.house_revision_id = hr.id
        LEFT JOIN addresses a ON hr.address_id = a.id
        LEFT JOIN epd_house_passports ehp ON hr.id = ehp.house_revision_id
        LEFT JOIN house_resettlements hrt_from ON hq_from.id = hrt_from.quarters_id AND hrt_from.deleted_at IS NULL
        LEFT JOIN refs hrt_stage ON hrt_from.stage_id = hrt_stage.id
    WHERE hq_from.deleted_at IS NULL
    ORDER BY hq_from.id, hrt_from.id
), cte_data AS (
    SELECT
        qd_to.id,
        qd_to.under_construction_quarter_id,
        qd_from.emergency_quarter_id,
        qd_to.under_construction_quarter_number,
        qd_to.under_construction_quarter_address,
        qd_from.emergency_quarter_address_with_number,
        qd_from.resettlement_stage_id AS resettlement_stage,
        qd_from.resettlement_stage_name,
        qd_to.under_construction_quarter_square,
        qd_from.emergency_quarter_square,
        qd_from.resettlement_residents,
        qd_from.is_resettlement_resident_consent,
        qd_to.amount_of_funds,
        qd_to.amount_of_funds_subject,
        qd_to.amount_of_funds_mo,
        qd_to.amount_of_extrabudgetary_funds,
        qd_to.sum_of_funds,
        qd_to.geo_tag_id,
        qd_to.geo_tag_name
    FROM cte_under_construction_quarters_data qd_to
        LEFT JOIN cte_emergency_quarters_data qd_from USING(under_construction_quarter_id)
)
SELECT *
FROM cte_data