-- 2329523
--SELECT * FROM geo_tags WHERE name like '%Тюме%' and level = 2;


WITH cte_sf_houses AS (
    SELECT
           h.id  AS house_id,
           hr.id AS house_revision_id
    FROM houses h
           JOIN house_revision hr ON hr.houses_id = h.id AND hr.is_current = 1
           JOIN geo_tags gt ON gt.id = hr.geo_tag_id
           JOIN geo_tags gp ON gt.lft BETWEEN  gp.lft AND gp.rgt
    WHERE
        h.deleted_at IS NULL
      AND hr.is_alarm = TRUE
      AND gp.id = 2329523
    ), cte_demolition_filter AS (
    SELECT
           csh.house_id,
           csh.house_revision_id
    FROM cte_sf_houses csh
           JOIN epd_house_accidents ha ON ha.house_revision_id = csh.house_revision_id
           JOIN refs alarm_reason_ref ON alarm_reason_ref.id = ha.alarm_reason_id
    WHERE
        (
            ha.actual_demolition_date IS NULL
              OR ha.actual_demolition_date >= '2019-01-01'
            )
      AND
        (
            ha.actual_settlement_completion_date IS NULL
              OR ha.actual_settlement_completion_date >= '2019-01-01'
            )
      AND ha.alarm_document_date < '2017-01-01'
      AND alarm_reason_ref.code = '0601' -- Физический износ
    ), cte_house_type_filter AS (
    SELECT
           cdf.house_id,
           cdf.house_revision_id
    FROM cte_demolition_filter cdf
           JOIN epd_house_passports hp ON hp.house_revision_id = cdf.house_revision_id
           JOIN refs house_type_ref ON house_type_ref.id = hp.house_type_id
    WHERE house_type_ref.code = '0501' -- Многоквартирный
    ), cte_houses_to_insert AS ( -- houses_to_insert
    SELECT
        DISTINCT ON (chtf.house_id)
                    chtf.house_id,
                    chtf.house_revision_id
    FROM cte_house_type_filter chtf
           JOIN house_quarters_residents_snapshots hqrs ON hqrs.house_revision_id = chtf.house_revision_id AND hqrs.is_main
           JOIN house_quarters hq ON hq.snapshot_id = hqrs.id
           LEFT JOIN refs resettlement_status_ref ON resettlement_status_ref.id = hq.resettlement_status_id
    WHERE
        hq.deleted_at IS NUlL
      AND resettlement_status_ref.code <> 'empty'
      AND (resettlement_status_ref.code <> 'resettled' OR hq.actual_settlement_completion_date >= '2019-01-01')
    ), cte_house_quarters AS (
    SELECT
           chti.house_id,
           hq.id AS hq_id,
           hq.square,
           hq.residents_count,
           hr.resettlement_program_id,
           program_type_ref.code AS program_type
    FROM cte_houses_to_insert chti
           JOIN house_quarters_residents_snapshots hqrs ON hqrs.house_revision_id = chti.house_revision_id AND hqrs.is_main
           JOIN house_quarters hq ON hq.snapshot_id = hqrs.id
           LEFT JOIN refs resettlement_status_ref ON resettlement_status_ref.id = hq.resettlement_status_id
           LEFT JOIN house_resettlements hr ON hr.quarters_id = hq.id
           LEFT JOIN reloc_programs_name_and_requisites rpnar ON rpnar.id = hr.resettlement_program_id
           LEFT JOIN refs program_type_ref ON program_type_ref.id = rpnar.program_type_id
    WHERE
        hq.deleted_at IS NULL
      AND hr.deleted_at IS NULL
    AND resettlement_status_ref.code <> 'empty'
    AND (resettlement_status_ref.code <> 'resettled' OR hq.actual_settlement_completion_date >= '2019-01-01')
    ) --SELECT * FROM cte_house_quarters WHERE house_id IN (9092341, 6583977);
    , cte_for_limits_filter AS (
    SELECT
        DISTINCT ON (chq.hq_id)
                    chq.house_id,
                    chq.hq_id,
                    chq.square,
                    chq.residents_count
    FROM cte_house_quarters chq
    WHERE chq.resettlement_program_id IS NULL
    ), sq AS (
    SELECT
           cflf.house_id,
           sum(cflf.square)          AS square_for_limits
          -- cflf.residents_count AS residents_for_limits,
          -- cflf.hq_id        AS quarters_for_limits
    FROM cte_for_limits_filter cflf
    GROUP BY cflf.house_id
    )
SELECT
      -- sum(square_for_limits)
    *
FROM sq-- WHERE house_id = 8646222