-- Active: xiaoding_test@@192.168.110.85@3306
WITH
    user_base AS (
        -- 基础用户信息
        SELECT
            id AS user_id,
            nickname,
            created_at AS register_time,
            deleted_at AS logout_time,
            mobile AS contact,
            last_login_time,
            invite_code,
            qrcode,
            avatar,
            state AS user_status
        FROM member_users
        WHERE
            deleted_at IS NULL
    ),
    user_address AS (
        -- 用户默认地址信息
        SELECT
            user_id,
            location AS user_location,
            province,
            city,
            district
        FROM member_address
        WHERE
            is_default = 'YES'
    ),
    user_wallet AS (
        -- 用户钱包信息
        SELECT
            owner_id AS user_id,
            available_balance AS wallet_balance
        FROM wallet
        WHERE
            owner_type = 'USER'
    ),
    user_order_stats AS (
        -- 近3个月订单统计
        SELECT
            user_id,
            COUNT(DISTINCT id) AS order_count,
            COALESCE(AVG(total_amount), 0) AS avg_order_amount
        FROM `order`
        WHERE
            created_at >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
        GROUP BY
            user_id
    ),
    user_violation_stats AS (
        -- 近3个月违规统计
        SELECT user_id, COUNT(DISTINCT id) AS violation_count
        FROM violation_records
        WHERE
            created_at >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
        GROUP BY
            user_id
    ),
    user_inviters AS (
        -- 用户邀请人信息
        SELECT
            t1.user_id,
            l1.nickname AS level1_inviter,
            l2.nickname AS level2_inviter
        FROM
            market_dist_teams t1
            LEFT JOIN member_users l1 ON l1.id = t1.object_id
            AND t1.level = 'LEVEL_1'
            LEFT JOIN market_dist_teams t2 ON t2.user_id = t1.user_id
            AND t2.level = 'LEVEL_2'
            LEFT JOIN member_users l2 ON l2.id = t2.object_id
    )
    -- 最终查询
SELECT
    ub.*,
    ua.user_location,
    ua.province,
    ua.city,
    ua.district,
    uw.wallet_balance,
    COALESCE(uos.order_count, 0) AS order_count,
    COALESCE(uvs.violation_count, 0) AS violation_count,
    COALESCE(uos.avg_order_amount, 0) AS avg_order_amount,
    ui.level1_inviter,
    ui.level2_inviter
FROM
    user_base ub
    LEFT JOIN user_address ua ON ua.user_id = ub.user_id
    LEFT JOIN user_wallet uw ON uw.user_id = ub.user_id
    LEFT JOIN user_order_stats uos ON uos.user_id = ub.user_id
    LEFT JOIN user_violation_stats uvs ON uvs.user_id = ub.user_id
    LEFT JOIN user_inviters ui ON ui.user_id = ub.user_id
ORDER BY ub.register_time DESC
LIMIT 20;


/* 新的用户列表 2025-01-01 */
use xiaoding_dev;
DROP VIEW IF EXISTS v_member_list;

CREATE OR REPLACE VIEW v_member_list AS
SELECT
    mu.id AS id,/* 用户ID */
    mu.nickname AS nickname,/* 用户昵称 */
    mu.avatar AS avatar,/* 头像 */
    mu.mobile AS mobile,/* 联系方式 */
    mu.state AS state,/* 用户状态 */
    mu.last_login_time AS last_login_time,/* 最近登录时间 */
    mu.invite_code AS invite_code,/* 邀请码 */
    mu.qrcode AS qrcode,/* 二维码 */
    mu.created_at AS register_time,/* 注册时间 */
    mu.deleted_at AS logout_time,/* 注销时间 */
    mu.register_area AS register_area,/* 注册地区 */
    w.id AS wallet_id,/* 钱包ID */
    w.total_balance AS total_balance,/* 总余额 */
    w.available_balance AS wallet_balance,/* 可用余额 */
    w.frozen_amount AS frozen_amount,/* 冻结余额 */
    w.total_income AS total_income,/* 累计收入 */
    w.total_expense AS total_expense,/* 累计支出 */
    COALESCE(wwr.total_withdraw, 0) AS total_withdraw, /* 累计提现金额 */
    /* 订单统计 */
    COALESCE(o.order_count, 0) AS order_count, /* 下单次数 */
    COALESCE(o.overtime_count, 0) AS overtime_count, /* 加钟次数 */
    COALESCE(o.reward_count, 0) AS reward_count, /* 打赏次数 */
    COALESCE(o.total_amount, 0) AS total_amount, /* 累计消费金额 */
    COALESCE(o.overtime_amount, 0) AS overtime_amount, /* 加钟消费金额 */
    COALESCE(o.tip_amount, 0) AS tip_amount, /* 打赏消费金额 */
    COALESCE(o.order_amount, 0) AS order_amount, /* 订单消费金额 */
    COALESCE(ROUND(o.total_amount / NULLIF(o.order_count, 0), 2), 0) AS avg_amount, /* 平均消费金额 */
    COALESCE(vr.violation_count, 0) AS violation_count, /* 不良行为次数 */
    /* 一级邀请人 */
    CASE
        WHEN mdt1.object_type = 'App\\Models\\CoachUser' THEN ci1.nickname
        WHEN mdt1.object_type = 'App\\Models\\MemberUser' THEN mu1.nickname
    END AS inviter_level1_name,
    /* 二级邀请人 */
    CASE
        WHEN mdt2.object_type = 'App\\Models\\CoachUser' THEN ci2.nickname
        WHEN mdt2.object_type = 'App\\Models\\MemberUser' THEN mu2.nickname
    END AS inviter_level2_name
FROM member_users mu
LEFT JOIN wallet w ON w.owner_id = mu.id AND w.owner_type = 'App\\Models\\MemberUser'
LEFT JOIN (
    SELECT w.owner_id, SUM(wwr.amount) as total_withdraw
    FROM wallet_withdraw_records wwr
    LEFT JOIN wallet w ON w.id = wwr.wallet_id
    WHERE wwr.state = 2 AND w.owner_type = 'App\\Models\\MemberUser'
    GROUP BY w.owner_id
) wwr ON wwr.owner_id = mu.id
LEFT JOIN (
    SELECT
        user_id,
        COUNT(CASE WHEN type != 3 AND state = 12 THEN 1 END) as order_count,/* 订单次数 */
        COUNT(CASE WHEN type = 3 AND state = 12 THEN 1 END) as overtime_count,/* 加钟次数 */
        COUNT(CASE WHEN tip_amount IS NOT NULL AND tip_amount > 0 AND state = 12 THEN 1 END) as reward_count,/* 打赏次数 */
        SUM(CASE WHEN state = 12 THEN pay_amount + balance_amount ELSE 0 END) as total_amount,/* 总消费金额 */
        SUM(CASE WHEN type = 3 AND state = 12 THEN pay_amount + balance_amount ELSE 0 END) as overtime_amount,/* 加钟消费金额 */
        SUM(CASE WHEN state = 12 THEN COALESCE(tip_amount, 0) ELSE 0 END) as tip_amount,/* 打赏消费金额 */
        SUM(CASE WHEN type != 3 AND state = 12 THEN pay_amount + balance_amount ELSE 0 END) as order_amount/* 订单消费金额 */
    FROM `order`
    GROUP BY user_id
) o ON o.user_id = mu.id
LEFT JOIN (
    SELECT user_id, COUNT(*) as violation_count
    FROM violation_records
    GROUP BY user_id
) vr ON vr.user_id = mu.id
/* 一级邀请人关联 */
LEFT JOIN market_dist_teams mdt1 ON mu.id = mdt1.user_id AND mdt1.level = 1
LEFT JOIN coach_info_records ci1 ON mdt1.object_type = 'App\\Models\\CoachUser' AND mdt1.object_id = ci1.coach_id
LEFT JOIN member_users mu1 ON mdt1.object_type = 'App\\Models\\MemberUser' AND mdt1.object_id = mu1.id
/* 二级邀请人关联 */
LEFT JOIN market_dist_teams mdt2 ON mu.id = mdt2.user_id AND mdt2.level = 2
LEFT JOIN coach_info_records ci2 ON mdt2.object_type = 'App\\Models\\CoachUser' AND mdt2.object_id = ci2.coach_id
LEFT JOIN member_users mu2 ON mdt2.object_type = 'App\\Models\\MemberUser' AND mdt2.object_id = mu2.id