-- Active: xiaoding_test@@192.168.110.85@3306

/*
这段sql语句是根据"market_dist_teams"表获取的，这个表的解释是：
1、当一个用户A邀请另一个用户B的时候，数据是这样的
-  object_id是A的id
- object_type是A的用户类型
-  user_id是B的id
- 统计的时候A是B的一级邀请人
2、当用户B邀请C的时候，数据是这样的
-  object_id是B的id
- object_type是B的用户类型
-  user_id是C的id
- 统计的时候B是C的一级邀请人
- 统计的时候A是C的二级邀请人
请重写我的统计sql
*/

-- 技师列表视图
DROP VIEW IF EXISTS manage_coach_list;

CREATE OR REPLACE VIEW manage_coach_list AS
WITH
    /* 一级邀请人信息 */
    inviter_level1 AS (
        SELECT mdt1.user_id, m1.nickname
        FROM
            market_dist_teams mdt1
            LEFT JOIN member_users m1 ON mdt1.object_id = m1.id
        WHERE
            mdt1.level = 1
    ),
    /* 二级邀请人信息 */
    inviter_level2 AS (
        SELECT mdt1.user_id, m2.nickname
        FROM
            market_dist_teams mdt1
            LEFT JOIN market_dist_teams mdt2 ON mdt1.object_id = mdt2.user_id
            LEFT JOIN member_users m2 ON mdt2.object_id = m2.id
        WHERE
            mdt1.level = 1
            AND mdt2.level = 1
    ),
    /* 钱包余额信息 */
    coach_wallet AS (
        SELECT owner_id, total_balance
        FROM wallet
        WHERE
            owner_type = 'App\\Models\\CoachUser'
    )
SELECT
    cu.id,
    /* 性别 */
    mu.gender,
    /* 技师名称 */
    ci.nickname,
    /* 头像 */
    ci.avatar,
    /* 手机号码 */
    mu.mobile,
    /* 注册地址 */
    mu.register_area,
    /* 注册时间 */
    mu.created_at,
    /* 当前所属区域 */
    cl.city,
    /* 所属店铺 */
    sar.shop_name,
    /* 调度区域 */
    cl.location,
    /* 是否开通会员 */
    CASE
        WHEN cu.is_vip = 1 THEN '是'
        ELSE '否'
    END AS is_vip,
    /* 开通会员时间 */
    cu.vip_time,
    /* 一级邀请人 */
    il1.nickname AS inviter_level1_name,
    /* 二级邀请人 */
    il2.nickname AS inviter_level2_name,
    /* 余额 */
    COALESCE(cw.total_balance, 0) AS balance,
    /* 状态 */
    cu.state,
    /* 邀请码 */
    cu.invite_code,
    /* 二维码 */
    cu.qr_code,
    /* 认证状态 */
    CASE
        WHEN cq.state = 2 THEN '已认证'
        WHEN cq.state = 1 THEN '认证中'
        WHEN cq.state = 3 THEN '认证失败'
        ELSE '未认证'
    END AS auth_status
FROM
    coach_users cu
    LEFT JOIN member_users mu ON cu.user_id = mu.id
    LEFT JOIN coach_info_records ci ON cu.info_record_id = ci.id
    LEFT JOIN coach_locations cl ON cu.id = cl.coach_id
    AND cl.type = 2
    LEFT JOIN shop_auth_records sar ON cu.shop_id = sar.id
    LEFT JOIN coach_qual_records cq ON cu.qualification_record_id = cq.id
    LEFT JOIN inviter_level1 il1 ON cu.user_id = il1.user_id
    LEFT JOIN inviter_level2 il2 ON cu.user_id = il2.user_id
    LEFT JOIN coach_wallet cw ON cu.user_id = cw.owner_id
WHERE
    cu.deleted_at IS NULL;