The exact derivation,
formula by formula.

-- Is this point inside a flood polygon? Returns the worst level (1-3)
-- found at this location, or NULL if outside every flood zone.
SELECT MAX(var) AS flood_level
FROM   flood_hazard
WHERE  ST_Contains(geom, ST_SetSRID(ST_MakePoint($lng, $lat), 4326));

-- When outside, how far to the nearest flood polygon?
-- Geography type ensures the result is in meters, not degrees.
SELECT MIN(ST_Distance(
         geom::geography,
         ST_SetSRID(ST_MakePoint($lng, $lat), 4326)::geography
       )) AS flood_nearest_m
FROM   flood_hazard
-- GIST-indexed bbox prefilter — without this, dense Manila addresses
-- match 2,000+ subdivided pieces and the geography cast pushes the
-- query past 40s. With it, ~200 ms.
WHERE  geom && ST_Expand(
         ST_SetSRID(ST_MakePoint($lng, $lat), 4326),
         2000.0 / 100000.0  -- ~2 km buffer in degrees at PH latitudes
       );

-- Elevation in meters and slope in degrees, sampled from the
-- 100 m bilinear-resampled DEM and 50 m slope grid (production
-- replaces the native 30 m rasters with these resampled pairs).
SELECT
  ST_Value(d.rast, p.geom)::float AS elevation_m,
  ST_Value(s.rast, p.geom)::float AS slope_deg
FROM   srtm_dem_100m d, srtm_slope_50m s,
       (SELECT ST_SetSRID(ST_MakePoint($lng, $lat), 4326) AS geom) p
WHERE  ST_Intersects(d.rast, p.geom)
  AND  ST_Intersects(s.rast, p.geom);

function categorizePolygonExposure(
  insideLevel: HazardLevel | null,        // 1 | 2 | 3 | null
  nearestM:    number | null,
  nearestLevel: HazardLevel | null,
): RiskCategory {
  // Inside the polygon — level decides directly.
  if (insideLevel === 3) return "high";
  if (insideLevel === 2) return "moderate";
  if (insideLevel === 1) return "low";

  if (nearestM === null) return "none";

  // Buffer #1: within 50 m of any zone counts as inside.
  if (nearestM <= 50) {
    return nearestLevel === 3 ? "high"
         : nearestLevel === 2 ? "moderate"
         :                      "low";
  }

  // Buffer #2: 50–200 m of a Level-3 polygon promotes to moderate.
  if (nearestM < 200 && nearestLevel === 3) return "moderate";

  return "none";
}

function computeSummary(findings: RiskFinding[]): Summary {
  return {
    overallCategory: overallRisk(findings),
    stackCounts:     countHighAndVeryHigh(findings),
    topConcerns:     pinKeyHazardsThenRankOthers(findings),
  };
}

// Worst category across MEASURED findings only.
function overallRisk(findings: RiskFinding[]): RiskCategory {
  let worst: RiskCategory = "none";
  for (const f of findings) {
    if (f.isEstimated) continue;                           // skip screens
    if (RANK[f.category] > RANK[worst]) worst = f.category;
  }
  return worst;
}

// Flood / Landslide / Storm-surge always occupy the first three slots,
// regardless of severity. Everything else is then sorted worst-first.
function pinKeyHazardsThenRankOthers(
  findings: RiskFinding[],
): RiskFinding[] {
  const pinned = ["Flood", "Landslide", "Storm surge"];
  const head = pinned
    .map(name => findings.find(f => f.label === name))
    .filter(Boolean) as RiskFinding[];
  const rest = findings
    .filter(f => !pinned.includes(f.label))
    .sort((a, b) => RANK[b.category] - RANK[a.category]);
  return [...head, ...rest];
}

const RANK: Record<RiskCategory, number> = {
  none: 0, low: 1, moderate: 2, high: 3, very_high: 4,
};