WITH params AS (
    SELECT
     $P!{reportId} AS report_id,
     5             AS OFFSET
), geo_params AS (
    SELECT
      CASE WHEN params.report_id = 0 THEN 2208161 ELSE g.id END AS geo_tag_id,
      CASE WHEN params.report_id = 0 THEN 0 ELSE g.level END    AS level
    FROM emrg_housing_reports r
      RIGHT JOIN params ON  params.report_id = r.id
      LEFT JOIN geo_tags g ON g.id = r.geo_tag_id
), rf AS (
    SELECT
      0            AS pos,
      'rf' :: TEXT AS type,
      g.id,
      g.name,
      g.parent_geo_tag_id
    FROM geo_tags g
    WHERE level = 0
), fo AS (
    SELECT
      row_number() OVER (
        ORDER BY FIELD(g.id, 2208161, 2220462, 2216072, 2209857,
                       2214157, 2238752, 2272232, 2208162,
                       2213473)) * 10000000 AS pos,
      'fo' :: TEXT                          AS type,
      g.id,
      g.name,
      g.parent_geo_tag_id
    FROM geo_tags g
    WHERE g.is_archive = 0 AND level = 1
), sf AS (
    SELECT
      row_number() OVER (
        ORDER BY fo.pos, g.name) * 100000 + fo.pos AS pos,
      'sf' :: TEXT                                 AS type,
      g.id,
      g.name,
      g.parent_geo_tag_id
    FROM geo_tags g
      JOIN fo ON g.parent_geo_tag_id = fo.id
    WHERE g.is_archive = 0
), mo AS (
    SELECT
      row_number() OVER (
        ORDER BY sf.pos, g.name) + sf.pos AS pos,
      'mo' :: TEXT                        AS type,
      g.id,
      g.name,
      g.parent_geo_tag_id
    FROM geo_tags g
      JOIN sf ON g.parent_geo_tag_id = sf.id
    WHERE g.is_archive = 0
), geo_agg AS (
  SELECT rf.*
  FROM rf
    CROSS JOIN geo_params
  WHERE geo_params.level = 0
  UNION ALL
  SELECT fo.*
  FROM fo
    CROSS JOIN geo_params
  WHERE geo_params.level = 0
  UNION ALL
  SELECT sf.*
  FROM sf
    CROSS JOIN geo_params
  WHERE geo_params.level = 0 OR geo_params.geo_tag_id = sf.id
  UNION ALL
  SELECT mo.*
  FROM mo
    CROSS JOIN geo_params
  WHERE
    NOT (geo_params.geo_tag_id = ANY (ARRAY [2280999, 2276347, 2399515]))
    AND (
      (geo_params.level = 2 AND geo_params.geo_tag_id = mo.parent_geo_tag_id) OR
      (geo_params.level = 3 AND geo_params.geo_tag_id = mo.id)
    )
  ORDER BY pos
), geo_num AS (
    SELECT
      row_number() OVER () + params.offset idx,
      geo_agg.*
    FROM geo_agg
      CROSS JOIN params
), fo_rows AS (
    SELECT
      2208161                                                         AS id,
      array_to_string(array_agg(idx) FILTER (WHERE type = 'fo'), ',') AS rows
    FROM geo_num g
    WHERE type = 'fo'
    GROUP BY type
), sum_rows AS (
    SELECT
      DISTINCT ON (parent_geo_tag_id)
      parent_geo_tag_id AS                           id,
      min(idx) OVER (PARTITION BY parent_geo_tag_id) sum_from,
      max(idx) OVER (PARTITION BY parent_geo_tag_id) sum_to
    FROM geo_num
    WHERE NOT (type = ANY (ARRAY ['rf', 'fo']))
), geo_final AS (
    SELECT
      geo_num.*,
      fo_rows.rows,
      sum_rows.sum_from,
      sum_rows.sum_to
    FROM geo_num
      LEFT JOIN fo_rows ON geo_num.id = fo_rows.id
      LEFT JOIN sum_rows ON geo_num.id = sum_rows.id
), filtered_reports_rf_case AS (
    SELECT
      DISTINCT ON(geo_tag_id) id,
      geo_tag_id
    FROM emrg_housing_reports
    WHERE deleted_at IS NULL
    ORDER BY geo_tag_id, created_at DESC
), filtered_report_mo_rf_case AS (
    SELECT
      r.geo_tag_id,
      coalesce(sum(city_mkd_count),0)                                         AS f3,
      coalesce(sum(city_blocked_mkd_count),0)                                 AS f4,
      coalesce(sum(city_individual_mkd_count),0)                              AS f5,
      coalesce(sum(city_special_fond_mkd_count),0)                            AS f6,
      coalesce(sum(village_mkd_count),0)                                      AS f7,
      coalesce(sum(village_blocked_mkd_count),0)                              AS f8,
      coalesce(sum(village_individual_mkd_count),0)                           AS f9,
      coalesce(sum(village_special_fond_mkd_count),0)                         AS f10,
      coalesce(sum(city_area_residental)/1000,0)                              AS f11,
      coalesce(sum(city_blocked_area_residental)/1000,0)                      AS f12,
      coalesce(sum(city_individual_area_residental)/1000,0)                   AS f13,
      coalesce(sum(city_special_fond_area_residental)/1000,0)                 AS f14,
      coalesce(sum(village_area_residental)/1000,0)                           AS f15,
      coalesce(sum(village_blocked_area_residental)/1000,0)                   AS f16,
      coalesce(sum(village_individual_area_residental)/1000,0)                AS f17,
      coalesce(sum(village_special_fond_area_residental)/1000,0)              AS f18,
      coalesce(sum(city_area_residental_resettlement)/1000,0)                 AS f19,
      coalesce(sum(city_blocked_area_residental_resettlement)/1000,0)         AS f20,
      coalesce(sum(city_individual_area_residental_resettlement)/1000,0)      AS f21,
      coalesce(sum(city_special_fond_area_residental_resettlement)/1000,0)    AS f22,
      coalesce(sum(village_area_residental_resettlement)/1000,0)              AS f23,
      coalesce(sum(village_blocked_area_residental_resettlement)/1000,0)      AS f24,
      coalesce(sum(village_individual_area_residental_resettlement)/1000,0)   AS f25,
      coalesce(sum(village_special_fond_area_residental_resettlement)/1000,0) AS f26,
      coalesce(sum(city_people_count),0)                                      AS f27,
      coalesce(sum(city_blocked_people_count),0)                              AS f28,
      coalesce(sum(city_individual_people_count),0)                           AS f29,
      coalesce(sum(city_special_fond_people_count),0)                         AS f30,
      coalesce(sum(village_people_count),0)                                   AS f31,
      coalesce(sum(village_blocked_people_count),0)                           AS f32,
      coalesce(sum(village_individual_people_count),0)                        AS f33,
      coalesce(sum(village_special_fond_people_count),0)                      AS f34,
      coalesce(sum(city_people_count_resettled),0)                            AS f35,
      coalesce(sum(city_blocked_people_count_resettled),0)                    AS f36,
      coalesce(sum(city_individual_people_count_resettled),0)                 AS f37,
      coalesce(sum(city_special_fond_people_count_resettled),0)               AS f38,
      coalesce(sum(village_people_count_resettled),0)                         AS f39,
      coalesce(sum(village_blocked_people_count_resettled),0)                 AS f40,
      coalesce(sum(village_individual_people_count_resettled),0)              AS f41,
      coalesce(sum(village_special_fond_people_count_resettled),0)            AS f42,
      coalesce(sum(city_area_residental_resettled)/1000,0)                    AS f43,
      coalesce(sum(city_blocked_area_residental_resettled)/1000,0)            AS f44,
      coalesce(sum(city_individual_area_residental_resettled)/1000,0)         AS f45,
      coalesce(sum(city_special_fond_area_residental_resettled)/1000,0)       AS f46,
      coalesce(sum(village_area_residental_resettled)/1000,0)                 AS f47,
      coalesce(sum(village_blocked_area_residental_resettled)/1000,0)         AS f48,
      coalesce(sum(village_individual_area_residental_resettled)/1000,0)      AS f49,
      coalesce(sum(village_special_fond_area_residental_resettled)/1000,0)    AS f50,
      coalesce(sum(city_area_residental_demolished)/1000,0)                   AS f51,
      coalesce(sum(city_blocked_area_residental_demolished)/1000,0)           AS f52,
      coalesce(sum(city_individual_area_residental_demolished)/1000,0)        AS f53,
      coalesce(sum(city_special_fond_area_residental_demolished)/1000,0)      AS f54,
      coalesce(sum(village_area_residental_demolished)/1000,0)                AS f55,
      coalesce(sum(village_blocked_area_residental_demolished)/1000,0)        AS f56,
      coalesce(sum(village_individual_area_residental_demolished)/1000,0)     AS f57,
      coalesce(sum(village_special_fond_area_residental_demolished)/1000,0)   AS f58,
      coalesce(sum(mkd_in_reg_programm_count),0)                              AS f59,
      coalesce(sum(city_mkd_in_reg_programm_count),0)                         AS f60,
      coalesce(sum(area_residental_in_reloc_reg_prog)/1000,0)                 AS f61,
      coalesce(sum(city_area_residental_in_reloc_reg_prog)/1000,0)            AS f62,
      coalesce(sum(people_resettled_count),0)                                 AS f63,
      coalesce(sum(city_people_resettled_count),0)                            AS f64
    FROM emrg_housing_reports_mo mo
      JOIN filtered_reports_rf_case r ON r.id = mo.emrg_housing_reports_id
    GROUP BY r.geo_tag_id
), rf_case AS (
    SELECT
      geo_final.pos,
      geo_final.name                                              AS f2,
      geo_final.rows,
      geo_final.sum_from,
      geo_final.sum_to,
      mo.*
    FROM geo_final
    LEFT JOIN filtered_report_mo_rf_case mo ON geo_final.id = mo.geo_tag_id
    ORDER BY geo_final.pos
), sf_case AS (
    SELECT
      geo_final.pos,
      geo_final.name                                                     AS f2,
      geo_final.rows,
      geo_final.sum_from,
      geo_final.sum_to,
      0 :: INTEGER,
      coalesce(city_mkd_count,0)                                         AS f3,
      coalesce(city_blocked_mkd_count,0)                                 AS f4,
      coalesce(city_individual_mkd_count,0)                              AS f5,
      coalesce(city_special_fond_mkd_count,0)                            AS f6,
      coalesce(village_mkd_count,0)                                      AS f7,
      coalesce(village_blocked_mkd_count,0)                              AS f8,
      coalesce(village_individual_mkd_count,0)                           AS f9,
      coalesce(village_special_fond_mkd_count,0)                         AS f10,
      coalesce(city_area_residental/1000,0)                              AS f11,
      coalesce(city_blocked_area_residental/1000,0)                      AS f12,
      coalesce(city_individual_area_residental/1000,0)                   AS f13,
      coalesce(city_special_fond_area_residental/1000,0)                 AS f14,
      coalesce(village_area_residental/1000,0)                           AS f15,
      coalesce(village_blocked_area_residental/1000,0)                   AS f16,
      coalesce(village_individual_area_residental/1000,0)                AS f17,
      coalesce(village_special_fond_area_residental/1000,0)              AS f18,
      coalesce(city_area_residental_resettlement/1000,0)                 AS f19,
      coalesce(city_blocked_area_residental_resettlement/1000,0)         AS f20,
      coalesce(city_individual_area_residental_resettlement/1000,0)      AS f21,
      coalesce(city_special_fond_area_residental_resettlement/1000,0)    AS f22,
      coalesce(village_area_residental_resettlement/1000,0)              AS f23,
      coalesce(village_blocked_area_residental_resettlement/1000,0)      AS f24,
      coalesce(village_individual_area_residental_resettlement/1000,0)   AS f25,
      coalesce(village_special_fond_area_residental_resettlement/1000,0) AS f26,
      coalesce(city_people_count,0)                                      AS f27,
      coalesce(city_blocked_people_count,0)                              AS f28,
      coalesce(city_individual_people_count,0)                           AS f29,
      coalesce(city_special_fond_people_count,0)                         AS f30,
      coalesce(village_people_count,0)                                   AS f31,
      coalesce(village_blocked_people_count,0)                           AS f32,
      coalesce(village_individual_people_count,0)                        AS f33,
      coalesce(village_special_fond_people_count,0)                      AS f34,
      coalesce(city_people_count_resettled,0)                            AS f35,
      coalesce(city_blocked_people_count_resettled,0)                    AS f36,
      coalesce(city_individual_people_count_resettled,0)                 AS f37,
      coalesce(city_special_fond_people_count_resettled,0)               AS f38,
      coalesce(village_people_count_resettled,0)                         AS f39,
      coalesce(village_blocked_people_count_resettled,0)                 AS f40,
      coalesce(village_individual_people_count_resettled,0)              AS f41,
      coalesce(village_special_fond_people_count_resettled,0)            AS f42,
      coalesce(city_area_residental_resettled/1000,0)                    AS f43,
      coalesce(city_blocked_area_residental_resettled/1000,0)            AS f44,
      coalesce(city_individual_area_residental_resettled/1000,0)         AS f45,
      coalesce(city_special_fond_area_residental_resettled/1000,0)       AS f46,
      coalesce(village_area_residental_resettled/1000,0)                 AS f47,
      coalesce(village_blocked_area_residental_resettled/1000,0)         AS f48,
      coalesce(village_individual_area_residental_resettled/1000,0)      AS f49,
      coalesce(village_special_fond_area_residental_resettled/1000,0)    AS f50,
      coalesce(city_area_residental_demolished/1000,0)                   AS f51,
      coalesce(city_blocked_area_residental_demolished/1000,0)           AS f52,
      coalesce(city_individual_area_residental_demolished/1000,0)        AS f53,
      coalesce(city_special_fond_area_residental_demolished/1000,0)      AS f54,
      coalesce(village_area_residental_demolished/1000,0)                AS f55,
      coalesce(village_blocked_area_residental_demolished/1000,0)        AS f56,
      coalesce(village_individual_area_residental_demolished/1000,0)     AS f57,
      coalesce(village_special_fond_area_residental_demolished/1000,0)   AS f58,
      coalesce(mkd_in_reg_programm_count,0)                              AS f59,
      coalesce(city_mkd_in_reg_programm_count,0)                         AS f60,
      coalesce(area_residental_in_reloc_reg_prog/1000,0)                 AS f61,
      coalesce(city_area_residental_in_reloc_reg_prog/1000,0)            AS f62,
      coalesce(people_resettled_count,0)                                 AS f63,
      coalesce(city_people_resettled_count,0)                            AS f64
    FROM geo_final
      CROSS JOIN params
      LEFT JOIN emrg_housing_reports_mo dt ON dt.geo_tag_id = geo_final.id AND dt.emrg_housing_reports_id = params.report_id
    ORDER BY geo_final.pos
), union_data AS (
  SELECT
    sf_case.*
  FROM
    sf_case
    CROSS JOIN geo_params
  WHERE geo_params.geo_tag_id <> 2208161
  UNION
  SELECT
    rf_case.*
  FROM rf_case
    CROSS JOIN geo_params
  WHERE geo_params.geo_tag_id = 2208161
) SELECT * FROM union_data ORDER BY pos