WITH county_base AS (
    SELECT
        state,
        county,
        COUNT(*) AS total_tracts,
        SUM(lilatracts_1and10) AS desert_tracts,
        ROUND(100.0 * SUM(lilatracts_1and10) / 
            NULLIF(COUNT(*), 0), 1) AS pct_desert,
        ROUND(AVG(povertyrate), 1) AS avg_poverty_rate,
        SUM(lapop1) AS people_affected,
        SUM(lahunv1) AS no_vehicle_pop,
        SUM(tractsnap) AS total_snap,
        SUM(lasnap1) AS snap_stranded,
        ROUND(100.0 * SUM(lasnap1) / 
            NULLIF(SUM(tractsnap), 0), 1) AS snap_strand_rate
    FROM food_access_raw
    WHERE tractsnap > 0
    GROUP BY state, county
    HAVING COUNT(*) >= 10
    AND SUM(lilatracts_1and10) > 0
),
scores AS (
    SELECT *,
        ROUND((pct_desert - MIN(pct_desert) OVER()) /
            NULLIF(MAX(pct_desert) OVER() - 
            MIN(pct_desert) OVER(), 0) * 100, 1) AS access_score,
        ROUND((avg_poverty_rate - MIN(avg_poverty_rate) OVER()) /
            NULLIF(MAX(avg_poverty_rate) OVER() - 
            MIN(avg_poverty_rate) OVER(), 0) * 100, 1) AS poverty_score,
        ROUND((snap_strand_rate - MIN(snap_strand_rate) OVER()) /
            NULLIF(MAX(snap_strand_rate) OVER() - 
            MIN(snap_strand_rate) OVER(), 0) * 100, 1) AS snap_score,
        ROUND((no_vehicle_pop - MIN(no_vehicle_pop) OVER()) /
            NULLIF(MAX(no_vehicle_pop) OVER() - 
            MIN(no_vehicle_pop) OVER(), 0) * 100, 1) AS vehicle_score
    FROM county_base
)
SELECT
    state,
    county,
    total_tracts,
    pct_desert,
    avg_poverty_rate,
    snap_strand_rate,
    people_affected,
    ROUND(
        (access_score * 0.30) +
        (poverty_score * 0.30) +
        (snap_score * 0.25) +
        (vehicle_score * 0.15)
    , 1) AS final_fipi_score
FROM scores
ORDER BY final_fipi_score DESC
LIMIT 25;