-- SQL para listar todas as duplas inscritas no torneio
-- com seus responsáveis, clube, emails e telefones

SELECT
    -- Informações do torneio
    t.id as tournament_id,
    t.name as tournament_name,

-- Informações da dupla - Estudante A
r.id as registration_id, r.status as registration_status,

-- Estudante A
ua.id as student_a_id,
CONCAT(ua.name, ' ', ua.last_name) as student_a_name,
ua.email as student_a_email,
ua.phone as student_a_phone,

-- Responsável do Estudante A
pa.id as parent_a_id,
CONCAT(pa.name, ' ', pa.last_name) as parent_a_name,
pa.email as parent_a_email,
pa.phone as parent_a_phone,

-- Clube do Estudante A
ca.id as club_a_id,
ca.name as club_a_name,
ca.city as club_a_city,
ca.state as club_a_state,

-- Estudante B
ub.id as student_b_id,
CONCAT(ub.name, ' ', ub.last_name) as student_b_name,
ub.email as student_b_email,
ub.phone as student_b_phone,

-- Responsável do Estudante B
pb.id as parent_b_id,
CONCAT(pb.name, ' ', pb.last_name) as parent_b_name,
pb.email as parent_b_email,
pb.phone as parent_b_phone,

-- Clube do Estudante B
cb.id as club_b_id,
cb.name as club_b_name,
cb.city as club_b_city,
cb.state as club_b_state,

-- Datas
r.created_at as registration_date,
r.updated_at as last_update
FROM
    registrations r
    -- Torneio
    INNER JOIN tournaments t ON r.tournament_id = t.id

-- Estudante A (usando fallback: student_a_user_id ou inviter_id)
LEFT JOIN users ua ON ua.id = COALESCE(
    r.student_a_user_id,
    r.inviter_id
)

-- Estudante B (usando fallback: student_b_user_id ou invitee_id)
LEFT JOIN users ub ON ub.id = COALESCE(
    r.student_b_user_id,
    r.invitee_id
)

-- Responsável do Estudante A (via family_members)
LEFT JOIN family_members fma ON fma.familymember_user_id = ua.id
LEFT JOIN users pa ON pa.id = fma.user_id

-- Responsável do Estudante B (via family_members)
LEFT JOIN family_members fmb ON fmb.familymember_user_id = ub.id
LEFT JOIN users pb ON pb.id = fmb.user_id

-- Clube do Estudante A (via family_members)
LEFT JOIN clubs ca ON ca.id = fma.club_id

-- Clube do Estudante B (via family_members)
LEFT JOIN clubs cb ON cb.id = fmb.club_id
WHERE
    -- Apenas inscrições confirmadas ou com ambos os estudantes definidos
    (
        r.status IN (
            'confirmed',
            'confirmado',
            'accepted'
        )
        OR (
            r.student_a_user_id IS NOT NULL
            AND r.student_b_user_id IS NOT NULL
        )
        OR (
            r.inviter_id IS NOT NULL
            AND r.invitee_id IS NOT NULL
        )
    )

-- Opcional: filtrar por torneio específico
-- AND t.id = ? -- substitua ? pelo ID do torneio desejado

ORDER BY t.name, r.created_at DESC;

-- Versão alternativa mais simples (apenas duplas confirmadas):
/*
SELECT 
t.name as tournament_name,
CONCAT(ua.name, ' ', ua.last_name) as student_a,
ua.email as student_a_email,
ua.phone as student_a_phone,
CONCAT(pa.name, ' ', pa.last_name) as parent_a,
pa.email as parent_a_email,
pa.phone as parent_a_phone,
ca.name as club_a,

CONCAT(ub.name, ' ', ub.last_name) as student_b,
ub.email as student_b_email,
ub.phone as student_b_phone,
CONCAT(pb.name, ' ', pb.last_name) as parent_b,
pb.email as parent_b_email,
pb.phone as parent_b_phone,
cb.name as club_b,

r.status,
r.created_at

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 r.status = 'confirmed'
ORDER BY t.name, r.created_at;
*/
