gistfile1.txt

DO $$
DECLARE
  iso TEXT;
  base_val DOUBLE PRECISION;
  min_val INTEGER;
  max_val INTEGER;
BEGIN
  FOR iso IN
    SELECT adm0_iso FROM geosure_country_modes
  LOOP

    WITH weights AS (
      SELECT 0.7 AS w_structure, 0.3 AS w_population
    ),
    base_and_normalized AS (
      SELECT
        t.h3,
        100.0 - (
          (
            COALESCE(1.25 * m.prosperity_mode, 0) +
            COALESCE(2.5  * m.safety_mode, 0) +
            COALESCE(0.75 * m.property_crime_mode, 0)
          ) /
          (
            CASE
              WHEN m.prosperity_mode IS NOT NULL AND m.safety_mode IS NOT NULL AND m.property_crime_mode IS NOT NULL THEN 4.5
              WHEN m.prosperity_mode IS NOT NULL AND m.safety_mode IS NOT NULL THEN 3.75
              WHEN m.safety_mode IS NOT NULL AND m.property_crime_mode IS NOT NULL THEN 3.25
              WHEN m.safety_mode IS NOT NULL THEN 2.5
              ELSE 1.0
            END
          )
        ) AS base,
        (
          ((COALESCE(t.geosure_structure_class, 6.5) - 1) / 11.0) * w.w_structure +
          ((COALESCE(t.geosure_population_class, 6.5) - 1) / 11.0) * w.w_population
        ) AS risk_raw
      FROM h3_l8_union_cities_urban t
      JOIN geosure_country_modes m ON t.adm0_iso = m.adm0_iso
      JOIN weights w ON TRUE
      WHERE t.adm0_iso = iso AND m.adm0_iso = iso
    ),
    rescaled AS (
      SELECT
        h3,
        base,
        ROUND(LEAST(GREATEST(base + (risk_raw * 6.0 - 2.0), 0), 100))::integer AS new_risk
      FROM base_and_normalized
    ),
    inserted AS (
      UPDATE h3_l8_union_cities_urban t
      SET geosure_baseline_physical = r.new_risk
      FROM rescaled r
      WHERE t.h3 = r.h3
      RETURNING r.base, r.new_risk
    )
    SELECT
      MIN(new_risk), MAX(new_risk), MAX(base)
    INTO min_val, max_val, base_val
    FROM inserted;

    RAISE NOTICE '✅ % | base: %.2f | range: %–%', iso, base_val, min_val, max_val;

  END LOOP;
END $$;