
WITH RECURSIVE tree AS (
  SELECT a.id, a.name, a.parent_id
  FROM assets a
  WHERE a.parent_id IS NULL
  UNION ALL
  SELECT a.id, a.name, a.parent_id
  FROM assets a
  JOIN tree t ON a.parent_id = t.id
),

event_costs AS (
  SELECT 
      asset_id,
      SUM(safety_cost) AS safety_cost
  FROM events
  WHERE safety_cost > 0
  GROUP BY asset_id
),

-- Helper to get all descendants of each node
descendants AS (
  SELECT id AS root_id, id AS child_id
  FROM assets
  UNION ALL
  SELECT d.root_id, a.id AS child_id
  FROM descendants d
  JOIN assets a ON a.parent_id = d.child_id
)

SELECT
    t.id       AS node_id,
    t.name     AS node_name,
    SUM(COALESCE(ec.safety_cost, 0)) AS rolled_safety_cost
FROM tree t
JOIN descendants d ON d.root_id = t.id
LEFT JOIN event_costs ec ON ec.asset_id = d.child_id
GROUP BY t.id, t.name
HAVING rolled_safety_cost > 0
ORDER BY rolled_safety_cost DESC;