USE cyclistsafe;

TRUNCATE TABLE risk_cell;

INSERT INTO risk_cell (cell_lat, cell_lng, crashes_total, killed_total, serious_total)
SELECT
    ROUND(latitude, 2) AS cell_lat,
    ROUND(longitude, 2) AS cell_lng,
    COUNT(*) AS crashes_total,
    COALESCE(SUM(no_killed),0) AS killed_total,
    COALESCE(SUM(no_serious_inj),0) AS serious_total
FROM crash_cyclist
WHERE latitude IS NOT NULL AND longitude IS NOT NULL
GROUP BY ROUND(latitude, 2), ROUND(longitude, 2);

SET @maxF := (SELECT MAX(crashes_total) FROM risk_cell);
SET @maxS := (SELECT MAX(killed_total*3 + serious_total*2) FROM risk_cell);

UPDATE risk_cell
SET risk_index =
    CASE
        WHEN @maxF = 0 OR @maxS = 0 THEN 0
        ELSE
            (0.6 * (crashes_total / @maxF))
          + (0.4 * ((killed_total*3 + serious_total*2) / @maxS))
    END;
