-- SQL para listar TODOS os estudantes de um torneio específico
-- Cada linha representa um estudante individual
-- Substitua {TOURNAMENT_ID} pelo ID do torneio desejado

-- Estudante A

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',
    CONCAT('A-', 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

UNION ALL

-- Estudante B

SELECT 
    CONCAT(ub.name, ' ', ub.last_name) as 'Nome do estudante',
    CONCAT(pb.name, ' ', pb.last_name) as 'Nome do Pai/Mãe',
    COALESCE(ub.phone, pb.phone) as 'Telefone',
    COALESCE(ub.email, pb.email) as 'E-mail',
    CONCAT('B-', 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 fmb ON fmb.familymember_user_id = ub.id
    LEFT JOIN users pb ON pb.id = fmb.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 'Nome do estudante';
