-- SQL para listar duplas de um torneio específico
-- Substitua {TOURNAMENT_ID} pelo ID do torneio desejado

SELECT
    -- Informações básicas da dupla
    r.id as registration_id,
    CONCAT(ua.name, ' ', ua.last_name) as student_a_name,
    CONCAT(ub.name, ' ', ub.last_name) as student_b_name,

-- Contatos dos estudantes
ua.email as student_a_email,
ua.phone as student_a_phone,
ub.email as student_b_email,
ub.phone as student_b_phone,

-- Responsáveis
CONCAT(pa.name, ' ', pa.last_name) as parent_a_name,
pa.email as parent_a_email,
pa.phone as parent_a_phone,
CONCAT(pb.name, ' ', pb.last_name) as parent_b_name,
pb.email as parent_b_email,
pb.phone as parent_b_phone,

-- Clubes
ca.name as club_a_name,
CONCAT(ca.city, ' - ', ca.state) as club_a_location,
cb.name as club_b_name,
CONCAT(cb.city, ' - ', cb.state) as club_b_location,

-- Status e datas
r.status as registration_status,
    r.created_at as registration_date

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
    LEFT JOIN family_members fmb ON fmb.familymember_user_id = ub.id
    LEFT JOIN users pb ON pb.id = fmb.user_id
    LEFT JOIN clubs ca ON ca.id = fma.club_id
    LEFT JOIN clubs cb ON cb.id = fmb.club_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;
