-- ================================================
-- Queries SQL para Diagnóstico do Sistema de Afiliados
-- ================================================

-- 1. VISÃO GERAL DO SISTEMA
-- ================================================

-- Total de registros em cada tabela
SELECT 'Afiliados' as tabela, COUNT(*) as total FROM affiliates
UNION ALL
SELECT 'Referrals', COUNT(*) FROM affiliate_referrals
UNION ALL
SELECT 'Comissões', COUNT(*) FROM affiliate_commissions
UNION ALL
SELECT 'Pagamentos', COUNT(*) FROM affiliate_payouts;

-- 2. REFERRALS - STATUS GERAL
-- ================================================

-- Contagem por status
SELECT 
    CASE 
        WHEN is_active = 1 THEN 'Ativo'
        ELSE 'Inativo'
    END as status,
    CASE 
        WHEN converted_at IS NOT NULL THEN 'Convertido'
        ELSE 'Não Convertido'
    END as conversao,
    COUNT(*) as quantidade
FROM affiliate_referrals
GROUP BY is_active, converted_at IS NOT NULL;

-- 3. PROBLEMA: Referrals Inativos com Comissões
-- ================================================

-- Listar referrals inativos que têm comissões (PROBLEMA!)
SELECT 
    ar.id as referral_id,
    ar.is_active,
    ar.converted_at,
    u.name as usuario,
    u.email,
    COUNT(ac.id) as total_comissoes,
    SUM(ac.commission_amount) as valor_total_comissoes,
    aff_user.name as afiliado
FROM affiliate_referrals ar
JOIN users u ON ar.referred_user_id = u.id
JOIN affiliates aff ON ar.affiliate_id = aff.id
JOIN users aff_user ON aff.user_id = aff_user.id
LEFT JOIN affiliate_commissions ac ON ar.id = ac.referral_id
WHERE ar.is_active = false
GROUP BY ar.id
HAVING COUNT(ac.id) > 0
ORDER BY total_comissoes DESC;

-- 4. USUÁRIOS COM PAGAMENTOS MAS REFERRAL INATIVO
-- ================================================

-- Usuários que foram indicados, pagaram, mas referral está inativo
SELECT 
    u.id as user_id,
    u.name,
    u.email,
    u.referred_by,
    ar.id as referral_id,
    ar.is_active as referral_ativo,
    ar.converted_at,
    COUNT(DISTINCT p.id) as pagamentos_aprovados,
    SUM(p.amount) as valor_total_pago
FROM users u
LEFT JOIN affiliate_referrals ar ON ar.referred_user_id = u.id
LEFT JOIN payments p ON p.user_id = u.id AND p.status IN ('completed', 'approved', 'paid')
WHERE u.referred_by IS NOT NULL
GROUP BY u.id
HAVING COUNT(DISTINCT p.id) > 0 AND (ar.is_active = false OR ar.is_active IS NULL)
ORDER BY pagamentos_aprovados DESC;

-- 5. DETALHES COMPLETOS DOS REFERRALS
-- ================================================

-- Visão detalhada de cada referral com todas as informações
SELECT 
    ar.id as referral_id,
    ar.created_at as data_criacao,
    u.name as usuario_indicado,
    u.email as email_indicado,
    aff_user.name as afiliado,
    aff_user.email as email_afiliado,
    ar.is_active as ativo,
    ar.converted_at as convertido_em,
    COUNT(DISTINCT ac.id) as total_comissoes,
    SUM(ac.commission_amount) as valor_comissoes,
    COUNT(DISTINCT p.id) as total_pagamentos,
    SUM(p.amount) as valor_pagamentos
FROM affiliate_referrals ar
JOIN users u ON ar.referred_user_id = u.id
JOIN affiliates aff ON ar.affiliate_id = aff.id
JOIN users aff_user ON aff.user_id = aff_user.id
LEFT JOIN affiliate_commissions ac ON ar.id = ac.referral_id
LEFT JOIN payments p ON p.user_id = u.id AND p.status IN ('completed', 'approved', 'paid')
GROUP BY ar.id
ORDER BY ar.created_at DESC;

-- 6. COMISSÕES POR REFERRAL
-- ================================================

-- Listar todas as comissões de cada referral
SELECT 
    ar.id as referral_id,
    u.name as usuario,
    ac.id as comissao_id,
    ac.type as tipo,
    ac.renewal_number as num_renovacao,
    ac.payment_amount as valor_pagamento,
    ac.commission_rate as taxa,
    ac.commission_amount as valor_comissao,
    ac.status as status_comissao,
    ac.created_at as data_comissao
FROM affiliate_referrals ar
JOIN users u ON ar.referred_user_id = u.id
LEFT JOIN affiliate_commissions ac ON ar.id = ac.referral_id
ORDER BY ar.id, ac.created_at;

-- 7. ESTATÍSTICAS POR AFILIADO
-- ================================================

-- Resumo de ganhos e indicações de cada afiliado
SELECT 
    aff.id as affiliate_id,
    u.name as afiliado,
    u.email,
    aff.approved as aprovado,
    aff.active as ativo,
    COUNT(DISTINCT ar.id) as total_indicacoes,
    COUNT(DISTINCT CASE WHEN ar.is_active = true THEN ar.id END) as indicacoes_ativas,
    COUNT(DISTINCT CASE WHEN ar.converted_at IS NOT NULL THEN ar.id END) as indicacoes_convertidas,
    COUNT(DISTINCT ac.id) as total_comissoes,
    SUM(ac.commission_amount) as valor_comissoes,
    aff.total_earnings as ganhos_totais,
    aff.paid_earnings as ganhos_pagos,
    aff.pending_earnings as ganhos_pendentes
FROM affiliates aff
JOIN users u ON aff.user_id = u.id
LEFT JOIN affiliate_referrals ar ON ar.affiliate_id = aff.id
LEFT JOIN affiliate_commissions ac ON ac.affiliate_id = aff.id
GROUP BY aff.id
ORDER BY valor_comissoes DESC;

-- 8. TIMELINE DE ATIVIDADES
-- ================================================

-- Últimas 20 atividades no sistema de afiliados
(
    SELECT 
        'Referral Criado' as evento,
        ar.created_at as data,
        u.name as usuario,
        aff_user.name as afiliado,
        NULL as valor
    FROM affiliate_referrals ar
    JOIN users u ON ar.referred_user_id = u.id
    JOIN affiliates aff ON ar.affiliate_id = aff.id
    JOIN users aff_user ON aff.user_id = aff_user.id
)
UNION ALL
(
    SELECT 
        CONCAT('Comissão - ', ac.type) as evento,
        ac.created_at as data,
        u.name as usuario,
        aff_user.name as afiliado,
        ac.commission_amount as valor
    FROM affiliate_commissions ac
    JOIN affiliate_referrals ar ON ac.referral_id = ar.id
    JOIN users u ON ar.referred_user_id = u.id
    JOIN affiliates aff ON ar.affiliate_id = aff.id
    JOIN users aff_user ON aff.user_id = aff_user.id
)
ORDER BY data DESC
LIMIT 20;

-- 9. CORREÇÃO MANUAL (USE COM CUIDADO!)
-- ================================================

-- ⚠️ IMPORTANTE: Prefira usar o comando PHP artisan ao invés destas queries
-- Estas queries são apenas para casos extremos ou análise

-- Ver quais referrals seriam corrigidos (somente visualização)
SELECT 
    ar.id,
    ar.is_active as status_atual,
    'true' as novo_status,
    ar.converted_at as converted_atual,
    COALESCE(ar.converted_at, ac.created_at) as novo_converted
FROM affiliate_referrals ar
LEFT JOIN affiliate_commissions ac ON ar.id = ac.referral_id
WHERE ar.is_active = false
GROUP BY ar.id
HAVING COUNT(ac.id) > 0;

-- ⚠️ Correção manual - EXECUTAR APENAS SE O COMANDO PHP ARTISAN FALHAR
-- UPDATE affiliate_referrals ar
-- SET 
--     is_active = true,
--     converted_at = COALESCE(
--         converted_at, 
--         (SELECT MIN(created_at) FROM affiliate_commissions WHERE referral_id = ar.id)
--     )
-- WHERE ar.is_active = false
-- AND EXISTS (
--     SELECT 1 FROM affiliate_commissions ac 
--     WHERE ac.referral_id = ar.id
-- );

-- 10. VALIDAÇÃO PÓS-CORREÇÃO
-- ================================================

-- Deve retornar 0 registros se tudo estiver correto
SELECT 
    'Referrals inativos com comissões' as problema,
    COUNT(*) as quantidade
FROM affiliate_referrals ar
WHERE ar.is_active = false
AND EXISTS (
    SELECT 1 FROM affiliate_commissions ac 
    WHERE ac.referral_id = ar.id
)
UNION ALL
SELECT 
    'Referrals convertidos mas inativos',
    COUNT(*)
FROM affiliate_referrals
WHERE converted_at IS NOT NULL AND is_active = false;

-- Deve retornar apenas registros com is_active = true se houver comissões
SELECT 
    CASE 
        WHEN ar.is_active = true AND COUNT(ac.id) > 0 THEN '✅ OK'
        WHEN ar.is_active = false AND COUNT(ac.id) > 0 THEN '❌ PROBLEMA'
        WHEN ar.is_active = false AND COUNT(ac.id) = 0 THEN '⏳ AGUARDANDO'
        ELSE '❓ VERIFICAR'
    END as status,
    COUNT(*) as quantidade
FROM affiliate_referrals ar
LEFT JOIN affiliate_commissions ac ON ar.id = ac.referral_id
GROUP BY 
    CASE 
        WHEN ar.is_active = true AND COUNT(ac.id) > 0 THEN '✅ OK'
        WHEN ar.is_active = false AND COUNT(ac.id) > 0 THEN '❌ PROBLEMA'
        WHEN ar.is_active = false AND COUNT(ac.id) = 0 THEN '⏳ AGUARDANDO'
        ELSE '❓ VERIFICAR'
    END;

-- ================================================
-- FIM DO DIAGNÓSTICO
-- ================================================

-- DICAS DE USO:
-- 1. Execute as queries na ordem
-- 2. Queries 1-8 são apenas para visualização
-- 3. Query 9 é para correção manual (evite usar)
-- 4. Query 10 é para validar se está tudo OK
-- 5. Prefira sempre usar: php artisan affiliates:fix-referrals