-- Script para recalcular progress_percent baseado nos pontos REAIS ganhos
-- Execute cada bloco separadamente no MySQL

-- 1. Primeiro, verificar os pontos reais ganhos por usuário/trilha
SELECT 
    u.id AS user_id,
    u.name,
    t.intelligence_id,
    COALESCE(SUM(uqa.points_earned), 0) AS points_earned
FROM users u
JOIN user_quiz_answers uqa ON uqa.user_id = u.id
JOIN quizzes q ON q.id = uqa.quiz_id
LEFT JOIN days d ON d.id = q.day_id
LEFT JOIN daily_challenges dc ON dc.id = q.daily_challenge_id
LEFT JOIN trails t ON t.id = COALESCE(d.trail_id, dc.trail_id)
WHERE t.id IS NOT NULL
GROUP BY u.id, t.intelligence_id
ORDER BY u.name;

-- 2. Atualizar progress_intelligence com os pontos reais
-- (Meta = 100 pontos por trilha, então progress_percent = pontos ganhos)
UPDATE progress_intelligence pi
SET pi.progress_percent = LEAST(100, (
    SELECT COALESCE(SUM(uqa.points_earned), 0)
    FROM user_quiz_answers uqa
    JOIN quizzes q ON q.id = uqa.quiz_id
    LEFT JOIN days d ON d.id = q.day_id
    LEFT JOIN daily_challenges dc ON dc.id = q.daily_challenge_id
    LEFT JOIN trails t ON t.id = COALESCE(d.trail_id, dc.trail_id)
    WHERE uqa.user_id = pi.user_id 
    AND t.intelligence_id = pi.intelligence_id
));

-- 3. Verificar resultado
SELECT 
    u.name,
    i.name_pt AS intelligence,
    pi.progress_percent
FROM progress_intelligence pi
JOIN users u ON u.id = pi.user_id
JOIN intelligences i ON i.id = pi.intelligence_id
ORDER BY u.name, i.name_pt;
