-- 测试邀请码功能
-- 执行时间：2026-02-03

USE fastadmin;

-- 1. 查看当前用户的邀请码信息
SELECT 
    id,
    username,
    nickname,
    invite_code AS '邀请码',
    invited_by AS '被谁邀请',
    invite_count AS '邀请人数',
    invite_reward_total AS '邀请奖励',
    money AS '金币余额'
FROM nf_user
WHERE id IN (
    SELECT id FROM nf_user 
    ORDER BY id DESC 
    LIMIT 10
)
ORDER BY id DESC;

-- 2. 查看有邀请码的用户
SELECT 
    id,
    username,
    nickname,
    invite_code AS '邀请码',
    invite_count AS '邀请人数',
    invite_reward_total AS '邀请奖励',
    money AS '金币余额'
FROM nf_user
WHERE invite_code IS NOT NULL
ORDER BY invite_count DESC, id DESC
LIMIT 20;

-- 3. 查看使用了邀请码的用户
SELECT 
    id,
    username,
    nickname,
    invited_by AS '使用的邀请码',
    money AS '金币余额',
    createtime AS '注册时间'
FROM nf_user
WHERE invited_by IS NOT NULL
ORDER BY id DESC
LIMIT 20;

-- 4. 查看邀请相关的金币日志
SELECT 
    l.id,
    l.user_id AS '用户ID',
    u.username AS '用户名',
    l.money AS '金币变动',
    l.before AS '变动前',
    l.after AS '变动后',
    l.memo AS '备注',
    FROM_UNIXTIME(l.createtime) AS '时间'
FROM nf_user_money_log l
LEFT JOIN nf_user u ON l.user_id = u.id
WHERE l.memo LIKE '%邀请%'
ORDER BY l.createtime DESC
LIMIT 20;

-- 5. 统计邀请数据
SELECT 
    COUNT(DISTINCT CASE WHEN invite_code IS NOT NULL THEN id END) AS '有邀请码的用户数',
    COUNT(DISTINCT CASE WHEN invited_by IS NOT NULL THEN id END) AS '使用邀请码的用户数',
    SUM(CASE WHEN invite_code IS NOT NULL THEN invite_count ELSE 0 END) AS '总邀请人数',
    SUM(CASE WHEN invite_code IS NOT NULL THEN invite_reward_total ELSE 0 END) AS '总邀请奖励'
FROM nf_user;