-- lists all regions under given id with regiontypeid
SELECT r.id, r.name_en AS regionname
FROM region r
WHERE r.id IN (
  WITH RECURSIVE tree_nodes AS (
    -- Base case: select the starting node
    SELECT id, parent_id, regiontypeid, defining_regions
    FROM region
    WHERE id = 817732709  -- Replace :starting_id with your actual starting id

    UNION ALL

    -- Recursive case: select all child nodes
    SELECT t.id, t.parent_id, t.regiontypeid, t.defining_regions
    FROM region t, tree_nodes tn
    WHERE (t.parent_id = tn.id OR t.id = ANY(tn.defining_regions))
      AND t.RegiontypeId < 20
  )
  SELECT DISTINCT id
  FROM tree_nodes
  WHERE regiontypeid = 19);

-- function to select regions recursively under the given region
DROP FUNCTION get_regions_below;
CREATE OR REPLACE FUNCTION get_regions_below(
    starting_region_id INTEGER,
    target_regiontypeid INTEGER DEFAULT 19
)
RETURNS TABLE (Id INTEGER, RegionName TEXT, RegionTypeId SMALLINT) AS $$
BEGIN
    RETURN QUERY
    SELECT r.id, r.name_en, r.regiontypeid AS regionname
    FROM region r
    INNER JOIN (
        WITH RECURSIVE tree_nodes AS (
            -- Base case: select the starting node
            SELECT r0.id, r0.parent_id, r0.regiontypeid, r0.defining_regions
            FROM region r0
            WHERE r0.id = starting_region_id
            UNION ALL
            -- Recursive case: select all child nodes
            SELECT t.id, t.parent_id, t.regiontypeid, t.defining_regions
            FROM region t, tree_nodes tn
            WHERE (t.parent_id = tn.id OR t.id = ANY(tn.defining_regions))
              --AND t.regiontypeid < 20
        )
        SELECT DISTINCT tree_nodes.id
        FROM tree_nodes
        WHERE tree_nodes.regiontypeid = target_regiontypeid
    ) s ON (r.Id = s.id)
    WHERE NOT r.Deleted;
END;
$$ LANGUAGE PlPgSql;

-- function to select regions recursively above the given region
DROP FUNCTION get_regions_above;
CREATE OR REPLACE FUNCTION get_regions_above(
    starting_region_id INTEGER
)
RETURNS TABLE (Id INTEGER, RegionName TEXT, RegionTypeId SMALLINT) AS $$
BEGIN
    RETURN QUERY
    SELECT r.id, r.name_en, r.regiontypeid AS regionname
    FROM region r
    WHERE r.id IN (
        WITH RECURSIVE tree_nodes AS (
            -- Base case: select the starting node
            SELECT r0.id, r0.parent_id, r0.regiontypeid, r0.defining_regions
            FROM region r0
            WHERE r0.id = starting_region_id
            UNION ALL
            -- Recursive case: select all child nodes
            SELECT t.id, t.parent_id, t.regiontypeid, t.defining_regions
            FROM region t, tree_nodes tn
            WHERE (tn.parent_id = t.id OR tn.id = ANY(t.defining_regions))
        )
        SELECT DISTINCT tree_nodes.id
        FROM tree_nodes
    );
END;
$$ LANGUAGE PlPgSql;


-- selecting skyscrapers/structures 100m+
SELECT r.id, r.RegionName AS regionname, COUNT(1) AS no
FROM Get_Regions_Below(817732709, 19) r
INNER JOIN object_region rr ON (rr.RegionId = r.Id)
INNER JOIN structure s ON (s.Id = rr.ObjectId)
WHERE s.structural_form LIKE 'structural_form.02.1%'
  AND s.status >= 'status.3' AND s.status <= 'status.5'
  AND (s.Height_Architectural > 99.9 OR s.Floors_Og > 31 OR structural_form = 'structural_form.02.1.1.2' OR structural_form = 'structural_form.02.1.1.3')
GROUP BY r.Id, regionname ORDER BY no DESC;

-- 10-11F - 12f+ comparison on cities of Finland
SELECT r.id, r.RegionName AS regionname,
    COUNT(1) AS no,
    SUM(CASE WHEN s.Floors_Og BETWEEN 10 AND 11 THEN 1 ELSE 0 END) AS "10-11F",
    SUM(CASE WHEN s.Floors_Og > 11 THEN 1 ELSE 0 END) AS "12F+"
FROM Get_Regions_Below(344873088, 7) r
INNER JOIN object_region rr ON (rr.RegionId = r.Id)
INNER JOIN structure s ON (s.Id = rr.ObjectId)
WHERE s.structural_form LIKE 'structural_form.02.1%'
  AND s.status >= 'status.3' AND s.status <= 'status.5'
  AND s.Floors_Og > 9
  AND NOT s.Deleted
GROUP BY r.Id, regionname ORDER BY no DESC;

-- nordic country metro area comparison
SELECT Row_Number() OVER (ORDER BY no DESC),
    s.RegionName,
    s.no AS total,
    s.a AS "10-11F",
    s.b AS "12F+"
FROM (
    SELECT r.RegionName,
       COUNT(1) AS no,
       SUM(CASE WHEN s.Floors_Og < 12 THEN 1 ELSE 0 END) AS a,
       SUM(CASE WHEN s.Floors_Og >= 12 THEN 1 ELSE 0 END) AS b
    FROM Get_Regions_Below(427414295, 19) r
        INNER JOIN object_region rr ON (rr.RegionId = r.Id)
        INNER JOIN structure s ON (s.Id = rr.ObjectId)
    WHERE s.structural_form LIKE 'structural_form.02.1%'
      AND s.status >= 'status.3' AND s.status <= 'status.5'
      AND s.Floors_Og > 9
    GROUP BY r.Id, regionname ORDER BY no DESC) s
ORDER BY no DESC;

-- nordic + baltic
SELECT Row_Number() OVER (ORDER BY no DESC),
    s.RegionName,
    s.no AS total,
    s.a AS "10-11F",
    s.b AS "12F+",
    s.c AS "61m+",
    s.d AS "100m+"
FROM (
    SELECT r.RegionName,
       COUNT(1) AS no,
       SUM(CASE WHEN s.Floors_Og < 12 THEN 1 ELSE 0 END) AS a,
       SUM(CASE WHEN s.Floors_Og >= 12 THEN 1 ELSE 0 END) AS b,
       SUM(CASE WHEN s.Height_Architectural > 50.9 THEN 1 ELSE 0 END) AS c,
       SUM(CASE WHEN s.Height_Architectural >= 99.9 THEN 1 ELSE 0 END) AS d
    FROM (SELECT r.RegionName, r.Id FROM Get_Regions_Below(751085940, 7) r UNION ALL SELECT r.RegionName, r.Id FROM Get_Regions_Below(427414295, 7) r) r
        INNER JOIN object_region rr ON (rr.RegionId = r.Id)
        INNER JOIN structure s ON (s.Id = rr.ObjectId)
    WHERE s.structural_form LIKE 'structural_form.02.1%'
      AND s.status >= 'status.3' AND s.status <= 'status.5'
      AND (s.Floors_Og > 9 OR s.Height_Architectural > 35)
    GROUP BY r.Id, regionname ORDER BY no DESC) s
ORDER BY no DESC LIMIT 31;
