WITH user_top_artists AS (
    SELECT
        user_id,
        artist_id,
        COUNT(*) as play_count,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(*) DESC) as artist_rank
    FROM scrobbles s
    INNER JOIN artists a ON a.id = s.artist_id
    WHERE s.artist_id IS NOT NULL
        AND a.name != 'Various Artists'
        AND user_id IN ('rec_cug4h6ibhfbm7uq5dte0', 'rec_d3tv55lqrj614pp2mgag')
    GROUP BY user_id, artist_id
),
user_totals AS (
    SELECT
        user_id,
        COUNT(DISTINCT artist_id) as total_artists
    FROM user_top_artists
    WHERE artist_rank <= 50  -- Use fewer artists for normalization
    GROUP BY user_id
),
shared_weighted AS (
    SELECT
        u1.artist_id,
        u1.artist_rank as user1_rank,
        u2.artist_rank as user2_rank,
        (1.0 / u1.artist_rank) * (1.0 / u2.artist_rank) as artist_weight,
        ROW_NUMBER() OVER (ORDER BY (1.0 / u1.artist_rank) * (1.0 / u2.artist_rank) DESC) as weight_rank
    FROM user_top_artists u1
    INNER JOIN user_top_artists u2
        ON u1.artist_id = u2.artist_id
        AND u1.user_id = 'rec_cug4h6ibhfbm7uq5dte0'
        AND u2.user_id = 'rec_d3tv55lqrj614pp2mgag'
    WHERE u1.artist_rank <= 50  -- Consider fewer artists
        AND u2.artist_rank <= 50
),
compatibility_calc AS (
    SELECT
        SUM(sw.artist_weight) as weighted_overlap,
        COUNT(*) as shared_count,
        (SELECT total_artists FROM user_totals WHERE user_id = 'rec_cug4h6ibhfbm7uq5dte0') as user1_total,
        (SELECT total_artists FROM user_totals WHERE user_id = 'rec_d3tv55lqrj614pp2mgag') as user2_total
    FROM shared_weighted sw
)
SELECT
    -- Simpler Jaccard-like formula (more generous)
    ROUND(
        (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100, 
        1
    ) as compatibility_percentage,
    CASE
        WHEN (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100 < 20 THEN 'Low'
        WHEN (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100 < 40 THEN 'Medium'
        WHEN (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100 < 60 THEN 'High'
        WHEN (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100 < 75 THEN 'Very High'
        WHEN (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100 < 90 THEN 'Super'
        ELSE 'ZOMG!1!'
    END as compatibility_level,
    shared_count as shared_artists,
    user1_total as user1_artist_count,
    user2_total as user2_artist_count,
    LIST(a.name ORDER BY sw.artist_weight DESC) FILTER (WHERE sw.weight_rank <= 10) as top_shared_artists
FROM compatibility_calc
CROSS JOIN shared_weighted sw
INNER JOIN artists a ON a.id = sw.artist_id
GROUP BY weighted_overlap, shared_count, user1_total, user2_total;