-- Script para recalcular progresso de TODOS os usuários automaticamente
-- Busca os IDs diretamente do banco
-- Data: 2025-12-19

-- 1. Atualiza progress_intelligence para todos os usuários e inteligências existentes
UPDATE progress_intelligence pi
SET progress_percent = (
    SELECT LEAST(100, 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 t1 ON t1.id = d.trail_id
    LEFT JOIN trails t2 ON t2.id = dc.trail_id
    WHERE uqa.user_id = pi.user_id
      AND (t1.intelligence_id = pi.intelligence_id OR t2.intelligence_id = pi.intelligence_id)
);

-- 2. Inserir registros de progresso para usuários que responderam quizzes mas não têm registro ainda
INSERT INTO progress_intelligence (user_id, intelligence_id, progress_percent, streak_days, last_activity, created_at)
SELECT 
    uqa.user_id,
    COALESCE(t1.intelligence_id, t2.intelligence_id) AS intelligence_id,
    LEAST(100, SUM(uqa.points_earned)) AS progress_percent,
    1 AS streak_days,
    MAX(uqa.answered_at) AS last_activity,
    NOW() AS created_at
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 t1 ON t1.id = d.trail_id
LEFT JOIN trails t2 ON t2.id = dc.trail_id
WHERE COALESCE(t1.intelligence_id, t2.intelligence_id) IS NOT NULL
GROUP BY uqa.user_id, COALESCE(t1.intelligence_id, t2.intelligence_id)
HAVING NOT EXISTS (
    SELECT 1 FROM progress_intelligence pi 
    WHERE pi.user_id = uqa.user_id 
      AND pi.intelligence_id = COALESCE(t1.intelligence_id, t2.intelligence_id)
);

-- 3. Conceder medalhas retroativamente para quem atingiu 100%
INSERT INTO user_achievements (user_id, achievement_id, earned_at)
SELECT DISTINCT
    pi.user_id,
    a.id AS achievement_id,
    NOW() AS earned_at
FROM progress_intelligence pi
JOIN achievements a ON a.intelligence_id = pi.intelligence_id AND a.threshold <= pi.progress_percent
WHERE pi.progress_percent >= 100
AND NOT EXISTS (
    SELECT 1 FROM user_achievements ua 
    WHERE ua.user_id = pi.user_id AND ua.achievement_id = a.id
);

-- 4. Mostrar resultado após atualização
SELECT 
    u.name AS usuario,
    i.name_pt AS inteligencia,
    pi.progress_percent AS progresso
FROM progress_intelligence pi
JOIN users u ON u.id = pi.user_id
JOIN intelligences i ON i.id = pi.intelligence_id
WHERE pi.progress_percent > 0
ORDER BY u.name, i.name_pt;
