WITH county_metrics AS (
    SELECT
        state,
        county,
        COUNT(*) AS total_tracts,
        SUM(lilatracts_1and10) AS desert_tracts,
        ROUND(100.0 * SUM(lilatracts_1and10) / COUNT(*), 1) AS pct_desert,
        SUM(lapop1) AS people_affected,
        ROUND(AVG(povertyrate), 1) AS avg_poverty_rate,
        ROUND(AVG(lahunv1), 1) AS avg_no_vehicle_pop
    FROM food_access_raw
    GROUP BY state, county
    HAVING COUNT(*) >= 10 AND SUM(lilatracts_1and10) > 0
),
normalized AS (
    SELECT
        state,
        county,
        total_tracts,
        desert_tracts,
        pct_desert,
        people_affected,
        avg_poverty_rate,
        avg_no_vehicle_pop,
        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((avg_no_vehicle_pop - MIN(avg_no_vehicle_pop) OVER()) /
            NULLIF(MAX(avg_no_vehicle_pop) OVER() - MIN(avg_no_vehicle_pop) OVER(), 0) * 100, 1)
            AS vehicle_score
    FROM county_metrics
)
SELECT
    state,
    county,
    total_tracts,
    pct_desert,
    avg_poverty_rate,
    people_affected,
    ROUND((access_score * 0.40) +
          (poverty_score * 0.35) +
          (vehicle_score * 0.25), 1) AS fipi_score
FROM normalized
ORDER BY fipi_score DESC

LIMIT 25;