-- SQL para listar duplas de um torneio específico
-- Retorna exatamente as 5 colunas solicitadas
-- Substitua {TOURNAMENT_ID} pelo ID do torneio desejado


SELECT 
    CONCAT(ua.name, ' ', ua.last_name) as 'Nome do estudante',
    CONCAT(pa.name, ' ', pa.last_name) as 'Nome do Pai/Mãe',
    COALESCE(ua.phone, pa.phone) as 'Telefone',
    COALESCE(ua.email, pa.email) as 'E-mail',
    r.id as 'Chave'

FROM registrations r
    INNER JOIN tournaments t ON r.tournament_id = t.id
    INNER JOIN users ua ON ua.id = COALESCE(r.student_a_user_id, r.inviter_id)
    INNER JOIN users ub ON ub.id = COALESCE(r.student_b_user_id, r.invitee_id)
    LEFT JOIN family_members fma ON fma.familymember_user_id = ua.id
    LEFT JOIN users pa ON pa.id = fma.user_id

WHERE 
    t.id = {TOURNAMENT_ID}  -- Substitua pelo ID do torneio
    AND r.status IN ('confirmed', 'confirmado', 'accepted')
    AND ua.id IS NOT NULL 
    AND ub.id IS NOT NULL

ORDER BY r.created_at DESC;
