-- SQL para listar duplas inscritas no torneio
-- Retorna apenas as colunas solicitadas

SELECT
    -- Nome do estudante
    CONCAT(ua.name, ' ', ua.last_name) as 'Nome do estudante',

-- Nome do Pai/Mãe
CONCAT(pa.name, ' ', pa.last_name) as 'Nome do Pai/Mãe',

-- Telefone
COALESCE(ua.phone, pa.phone) as 'Telefone',

-- E-mail
COALESCE(ua.email, pa.email) as 'E-mail',

-- Chave (usando ID da inscrição como chave única)
r.id as 'Chave',

-- Informações adicionais para contexto (opcional)
CONCAT(ub.name, ' ', ub.last_name) as 'Parceiro',
t.name as 'Torneio',
r.status as 'Status'
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
WHERE
    -- Apenas inscrições confirmadas
    r.status IN (
        'confirmed',
        'confirmado',
        'accepted'
    )
    AND ua.id IS NOT NULL
    AND ub.id IS NOT NULL
ORDER BY t.name, r.created_at DESC;

-- Versão mais simples (apenas as 5 colunas solicitadas):
/*
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 
r.status IN ('confirmed', 'confirmado', 'accepted')
AND ua.id IS NOT NULL 
AND ub.id IS NOT NULL

ORDER BY r.created_at DESC;
*/
