Where projected heat meets the lowest incomes

python3 - <<'EOF'
import zn
con = zn.lake_con()
DIV = zn.read('electoral_boundaries', file='division_h3r9_aec2025')
PRJ = zn.read('climate_projections', file='projections_ssp585')
DP = zn.read('division_profile')
con.execute(f"""
CREATE OR REPLACE TEMP TABLE seat_climate AS
WITH r4 AS (
  SELECT name AS division_name,
         h3_cell_to_parent(h3_r9, 4) AS h3_r4,
         count(*) AS r9_cells
  FROM {DIV}
  WHERE state = 'NSW'
  GROUP BY 1,2
), p AS (
  SELECT h3_r4, days_over_35, d_days_over_35
  FROM {PRJ}
  WHERE decade = 2050
)
SELECT r4.division_name,
       sum(r4.r9_cells * p.d_days_over_35) / sum(r4.r9_cells) AS heat_rise_days_2050,
       sum(r4.r9_cells * p.days_over_35) / sum(r4.r9_cells) AS heat_level_days_2050,
       sum(r4.r9_cells) AS matched_r9_cells
FROM r4 JOIN p USING (h3_r4)
GROUP BY 1
""")
# materialise complete seat table and top ten separately
con.execute(f"""
COPY (
WITH j AS (
 SELECT d.division_name,
        d.member_given_name || ' ' || d.member_surname AS member,
        d.member_party_name AS party,
        c.heat_rise_days_2050,
        c.heat_level_days_2050,
        d.bushfire_cell_share_pct,
        d.median_hh_income_weekly_avg,
        rank() OVER (ORDER BY c.heat_rise_days_2050 DESC) AS heat_rank,
        rank() OVER (ORDER BY d.median_hh_income_weekly_avg) AS income_rank,
        rank() OVER (ORDER BY c.heat_rise_days_2050 DESC)
          + rank() OVER (ORDER BY d.median_hh_income_weekly_avg) AS combined_rank
 FROM {DP} d JOIN seat_climate c USING (division_name)
 WHERE d.state = 'NSW'
)
SELECT division_name,
       member,
       party,
       heat_rise_days_2050,
       heat_level_days_2050,
       bushfire_cell_share_pct,
       median_hh_income_weekly_avg,
       heat_rank,
       income_rank,
       combined_rank
FROM j
ORDER BY combined_rank, heat_rank, division_name
) TO '/workspace/out/nsw_seats_all.csv' (HEADER)
""")
con.execute("""
COPY (
SELECT * FROM read_csv_auto('/workspace/out/nsw_seats_all.csv')
ORDER BY combined_rank, heat_rank, division_name
LIMIT 10
) TO '/workspace/out/nsw_seats_top10.csv' (HEADER)
""")
print(con.sql("SELECT count(*) AS seats, min(heat_rise_days_2050) AS min_rise, max(heat_rise_days_2050) AS max_rise, median(heat_rise_days_2050) AS median_rise, min(median_hh_income_weekly_avg) AS min_income, max(median_hh_income_weekly_avg) AS max_income, count(*) FILTER (WHERE bushfire_cell_share_pct IS NULL) AS bushfire_unassessed FROM read_csv_auto('/workspace/out/nsw_seats_all.csv')").fetchone())
print(con.sql("SELECT division_name, heat_rise_days_2050, median_hh_income_weekly_avg, combined_rank FROM read_csv_auto('/workspace/out/nsw_seats_top10.csv') ORDER BY combined_rank, heat_rank, division_name").fetchall())
EOF

python3 - <<'EOF'
import duckdb
con=duckdb.connect()
con.execute("COPY (SELECT * FROM read_csv_auto('/workspace/data/data_1.csv')) TO '/workspace/out/nsw_seats_chart.csv' (HEADER)")
con.execute("COPY (SELECT * FROM read_csv_auto('/workspace/data/data_1.csv')) TO '/workspace/out/nsw_seats_map.csv' (HEADER)")
print('written')
EOF