WITH cte_subject_geo_tag AS (
    SELECT
        gp.id
    FROM new_reloc_contracts c
        JOIN geo_tags gc ON c.mo_geo_tag_id = gc.id
        JOIN geo_tags gp ON gc.lft BETWEEN gp.lft AND gp.rgt AND gp.level = 2
    WHERE c.id = :contractId AND c.deleted_at IS NULL
),
cte_emrg_housing_doc_new AS (
    SELECT
        DISTINCT ON (d.geo_tag_id)
        d.id
    FROM emrg_housing_docs_new d
        JOIN statuses s ON d.status_id = s.id
        JOIN cte_subject_geo_tag g ON g.id = d.geo_tag_id
    WHERE
        d.deleted_at IS NULL
        AND s.code = 'new_emrg_doc.verified_by_fund'
    ORDER BY d.geo_tag_id, d.created_at DESC
),
cte_requests_mo AS (
    SELECT
        DISTINCT (frfmo.geo_tag_id) AS id
    FROM fin_requests_for_municipal_organizations frfmo
        JOIN fin_requests fr on frfmo.financing_request_id = fr.id
        JOIN statuses s ON s.id = fr.status_id
    WHERE fr.deleted_at IS NULL
        AND frfmo.deleted_at IS NULL
        AND fr.version = 'v9'
        AND fr.id = 0
        AND ((
            fin_request_is_change(fr.id) AND s.code IN (
                'approved_sf',
                'received_to_fund',
                'financing_request_verified_by_fund',
                'financing_request_fund_approved'
            ))
            OR s.code = 'financing_request_on_control'
        )
),
cte_houses AS (
    SELECT
        ehdnh.id,
        bool_or(hr.resettlement_program_id IS NULL) OR bool_or(r.code = '81005') is_relocation_with_fund,
        ehdnma.geo_tag_id,
        revision.address_id
    FROM cte_emrg_housing_doc_new d
        JOIN emrg_housing_docs_new_mun_areas ehdnma ON d.id = ehdnma.document_id
        JOIN geo_tags g ON ehdnma.geo_tag_id = g.id
        JOIN emrg_housing_docs_new_houses ehdnh ON ehdnma.id = ehdnh.municipal_area_id
        JOIN house_quarters_residents_snapshots hqrs ON ehdnh.snapshot_id = hqrs.id
        JOIN house_revision revision ON hqrs.house_revision_id = revision.id
        JOIN house_quarters hq ON hqrs.id = hq.snapshot_id
        LEFT JOIN house_resettlements hr ON hq.id = hr.quarters_id AND hr.deleted_at IS NULL
        LEFT JOIN reloc_programs_name_and_requisites rpnar ON hr.resettlement_program_id = rpnar.id
        LEFT JOIN refs r ON rpnar.program_type_id = r.id
    WHERE hq.deleted_at IS NULL AND (ehdnma.geo_tag_id IN (SELECT id FROM cte_requests_mo) OR :requestId = 0)
    GROUP BY ehdnh.id, ehdnma.geo_tag_id, revision.address_id
),
cte_data AS (
    SELECT
        cte_houses.id,
        g.name                            AS municipality,
        get_address_short(a.full_address) AS address
    FROM cte_houses
        JOIN geo_tags g ON g.id = cte_houses.geo_tag_id
        JOIN addresses a ON cte_houses.address_id = a.id
    WHERE CASE WHEN :isContractRelocationWithFund THEN cte_houses.is_relocation_with_fund ELSE TRUE END
)
SELECT *
FROM cte_data