-- 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 v_coach_list;

CREATE OR REPLACE VIEW v_coach_list AS
SELECT
    cu.id AS id,/*技师id*/
    ci.id AS info_record_id,/*技师信息记录id*/
    ci.nickname AS nickname,/*技师名称*/
    ci.mobile AS mobile,/*手机号码*/
    ci.avatar AS avatar,/*头像*/
    ci.gender AS gender,/*性别*/
    ci.birthday AS birthday,/*生日*/
    cu.created_at AS created_at,/*注册时间*/
    cu.state AS state,/*技师状态*/
    cu.invite_code AS invite_code,/*邀请码*/
    cu.qr_code AS qr_code,/*二维码*/
    cu.is_vip AS is_vip, /*是否开通会员*/
    cu.vip_time AS vip_time,/*开通会员时间*/
    cu.level AS level,/*技师等级*/
    cu.score AS score,/*技师评分*/
    cu.work_status AS work_status,/*工作状态*/
    cu.formal_photo AS formal_photo,/*正式照*/
    cu.virtual_order AS virtual_order,/*虚拟订单*/
    /* 认证信息 */
    crr.id AS real_auth_record_id,/*实名认证记录id*/
    crr.id_card AS id_card,/*身份证*/
    crr.real_name AS real_name,/*真实姓名*/
    crr.id_card_front_photo AS id_card_front_photo,/*身份证正面*/
    crr.id_card_back_photo AS id_card_back_photo,/*身份证反面*/
    cq.id AS qual_record_id,/*资格认证记录id*/
    cq.health_cert AS health_cert,/*健康证*/
    cq.qual_photo AS qual_photo,/*资格证*/
    cq.business_license AS business_license,/*营业执照*/
    /* 所属店铺 */
    sar.id AS shop_auth_record_id,/*店铺认证记录id*/
    sar.shop_name AS shop_name,/*店铺名称*/
    /* 当前位置信息 */
    cl.id AS location_id,/*位置记录id*/
    cl.city AS city,/*城市*/
    cl.address AS address,/*当前位置*/
    /* 钱包余额 */
    cw.id AS wallet_id,/*钱包id*/
    COALESCE(cw.total_income, 0) AS total_income,/*总收入*/
    COALESCE(cw.total_expense, 0) AS total_expense,/*总支出*/
    COALESCE(cw.total_balance, 0) AS total_balance,/*钱包余额*/
    COALESCE(cw.available_balance, 0) AS available_balance,/*可用余额*/
    COALESCE(cw.frozen_amount, 0) AS frozen_amount,/*冻结余额*/
    /* 订单统计 */
    COALESCE(os.completed_order_count, 0) AS completed_order_count, /*完成订单数*/
    COALESCE(os.overtime_order_count, 0) AS overtime_order_count, /*加钟订单数*/
    COALESCE(os.overtime_amount, 0) AS overtime_amount, /*加钟总金额*/
    COALESCE(os.avg_overtime_amount, 0) AS avg_overtime_amount, /*平均加钟金额*/
    /* 一级邀请人 */
    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 coach_users cu
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 coach_qual_records cq ON cu.qualification_record_id = cq.id
LEFT JOIN shop_auth_records sar ON cu.shop_id = sar.shop_id
LEFT JOIN coach_real_records crr ON cu.real_auth_record_id = crr.id
LEFT JOIN wallet cw ON cu.id = cw.owner_id AND cw.owner_type = 'App\\Models\\CoachUser'
/* 订单统计关联 */
LEFT JOIN (
    SELECT
        coach_id,
        COUNT(CASE WHEN state = 12 AND type != 3 THEN 1 END) AS completed_order_count,
        COUNT(CASE WHEN state = 12 AND type = 3 THEN 1 END) AS overtime_order_count,
        SUM(CASE WHEN state = 12 AND type = 3 THEN pay_amount + balance_amount ELSE 0 END) AS overtime_amount,
        ROUND(
            SUM(CASE WHEN state = 12 AND type = 3 THEN pay_amount + balance_amount ELSE 0 END) /
            NULLIF(COUNT(CASE WHEN state = 12 AND type = 3 THEN 1 END), 0),
            2
        ) AS avg_overtime_amount
    FROM `order`  -- 这里表名应该是orders而不是order
    GROUP BY coach_id
) os ON os.coach_id = cu.id
/* 一级邀请人关联 */
LEFT JOIN market_dist_teams mdt1 ON cu.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 cu.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
WHERE cu.deleted_at IS NULL