-- Criar conquistas/medalhas para cada inteligência
-- Threshold = 100 significa que a medalha é concedida quando o progresso atinge 100%

-- Primeiro, verificar as inteligências existentes
SELECT id, name_pt FROM intelligences;

-- Inserir medalhas para cada inteligência (se ainda não existirem)
-- Usando INSERT IGNORE para não duplicar

INSERT INTO achievements (title, description, category, intelligence_id, threshold, created_at)
SELECT 
    CONCAT('Mestre em ', i.name_pt) AS title,
    CONCAT('Complete 100% da trilha de ', i.name_pt, ' para conquistar esta medalha!') AS description,
    'tree' AS category,
    i.id AS intelligence_id,
    100 AS threshold,
    NOW() AS created_at
FROM intelligences i
WHERE NOT EXISTS (
    SELECT 1 FROM achievements a 
    WHERE a.intelligence_id = i.id AND a.threshold = 100
);

-- Inserir medalha Super Bônus (para quem completar todas as inteligências)
INSERT INTO achievements (title, description, category, intelligence_id, threshold, created_at)
SELECT 
    'Super Bônus',
    'Parabéns! Você completou todas as 8 inteligências e conquistou o Super Bônus!',
    'special',
    NULL,
    100,
    NOW()
WHERE NOT EXISTS (
    SELECT 1 FROM achievements a 
    WHERE a.intelligence_id IS NULL AND a.category = 'special'
);

-- Verificar conquistas criadas
SELECT 
    a.id,
    a.title,
    a.category,
    i.name_pt AS intelligence,
    a.threshold
FROM achievements a
LEFT JOIN intelligences i ON i.id = a.intelligence_id
ORDER BY a.intelligence_id, a.threshold;

-- Para usuários que já atingiram 100%, conceder medalhas retroativamente
INSERT INTO user_achievements (user_id, achievement_id, earned_at, created_at)
SELECT DISTINCT
    pi.user_id,
    a.id AS achievement_id,
    NOW() AS earned_at,
    NOW() AS created_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
);

-- Verificar medalhas concedidas
SELECT 
    u.name,
    a.title AS medalha,
    ua.earned_at
FROM user_achievements ua
JOIN users u ON u.id = ua.user_id
JOIN achievements a ON a.id = ua.achievement_id
ORDER BY u.name, ua.earned_at;
