-- Script para recalcular progress_percent baseado nos pontos reais ganhos
-- Executar após deploy para corrigir dados existentes

-- Primeiro, criar uma tabela temporária com os pontos calculados por usuário/trilha/inteligência
CREATE TEMPORARY TABLE temp_progress AS
SELECT 
    u.id AS user_id,
    t.intelligence_id,
    t.id AS trail_id,
    (
        -- Pontos dos quizzes dos dias
        COALESCE((
            SELECT SUM(uqa.points_earned)
            FROM user_quiz_answers uqa
            JOIN quizzes q ON q.id = uqa.quiz_id
            JOIN days d ON d.id = q.day_id
            WHERE d.trail_id = t.id AND uqa.user_id = u.id
        ), 0)
        +
        -- Pontos dos quizzes dos desafios diários vinculados
        COALESCE((
            SELECT SUM(uqa.points_earned)
            FROM user_quiz_answers uqa
            JOIN quizzes q ON q.id = uqa.quiz_id
            JOIN daily_challenges dc ON dc.id = q.daily_challenge_id
            WHERE dc.trail_id = t.id AND uqa.user_id = u.id
        ), 0)
    ) AS total_points
FROM users u
CROSS JOIN trails t
WHERE EXISTS (
    SELECT 1 FROM user_quiz_answers uqa2
    JOIN quizzes q2 ON q2.id = uqa2.quiz_id
    LEFT JOIN days d2 ON d2.id = q2.day_id
    LEFT JOIN daily_challenges dc2 ON dc2.id = q2.daily_challenge_id
    WHERE uqa2.user_id = u.id
    AND (d2.trail_id = t.id OR dc2.trail_id = t.id)
);

-- Agrupar por usuário e inteligência (somando pontos de todas as trilhas da mesma inteligência)
CREATE TEMPORARY TABLE temp_intelligence_progress AS
SELECT 
    user_id,
    intelligence_id,
    LEAST(100, SUM(total_points)) AS total_points,
    LEAST(100, (SUM(total_points) / 100) * 100) AS progress_percent
FROM temp_progress
GROUP BY user_id, intelligence_id;

-- Atualizar registros existentes
UPDATE progress_intelligence pi
JOIN temp_intelligence_progress tip 
    ON pi.user_id = tip.user_id AND pi.intelligence_id = tip.intelligence_id
SET pi.progress_percent = tip.progress_percent;

-- Inserir novos registros para usuários que ainda não têm
INSERT INTO progress_intelligence (user_id, intelligence_id, progress_percent, streak_days, last_activity, created_at)
SELECT 
    tip.user_id,
    tip.intelligence_id,
    tip.progress_percent,
    1,
    CURDATE(),
    NOW()
FROM temp_intelligence_progress tip
LEFT JOIN progress_intelligence pi 
    ON pi.user_id = tip.user_id AND pi.intelligence_id = tip.intelligence_id
WHERE pi.id IS NULL
AND tip.total_points > 0;

-- Limpar tabelas temporárias
DROP TEMPORARY TABLE IF EXISTS temp_progress;
DROP TEMPORARY TABLE IF EXISTS temp_intelligence_progress;

-- Verificar resultados
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;
