-- Active: xiaoding_test@@192.168.110.85@3306@xiaoding_test
/*
您是一位专业的dba，同时会业务分析。请参考以上数据库的设计文档，给我写一个sql，关联查询出：代理昵称、申请时间、性别、头像、联系电话、代理地区、总收益金额、体现金额、余额
请按照的我的要求生成查询sql语句：
1. 请从如下 agent_infos   member_users   wallet  wallet_withdraw_records等一些表查找
2. agent_infos通过user_id关联的member_users表
3. wallet 和 member_users通过判断owner_type和owner_id关联
4. wallet_withdraw_records和wallet通过wallet_id关联
*/
CREATE OR REPLACE VIEW manage_agent_list AS
WITH
    agent_withdraw AS (
        SELECT w.owner_id, COALESCE(SUM(wr.amount), 0) AS total_withdraw_amount /* 总提现金额 */
        FROM
            wallet w /* 钱包 */
            LEFT JOIN wallet_withdraw_records wr ON w.id = wr.wallet_id /* 关联钱包提现记录 */
            AND wr.audit_state = 2 /* 审核通过 */
            AND wr.state = 2 /* 提现成功 */
        WHERE
            w.owner_type = 3 /* 代理钱包 */
        GROUP BY
            w.owner_id /* 代理编号 */
    )
SELECT
    mu.nickname as agent_nickname /* 代理昵称 */,
    ai.created_at as apply_time /* 申请时间 */,
    mu.gender as gender /* 性别 */,
    mu.avatar as avatar /* 头像 */,
    mu.mobile as mobile /* 联系电话 */,
    ai.area_code as area /* 代理地区 */,
    COALESCE(w.total_income, 0) as total_income /* 总收益金额 */,
    COALESCE(w.available_balance, 0) as balance /* 余额 */
FROM
    agent_infos ai /* 代理信息 */
    INNER JOIN member_users mu ON ai.user_id = mu.id /* 关联会员用户 */
    LEFT JOIN wallet w ON w.owner_id = ai.id /* 关联钱包 */
    AND w.owner_type = 3 /* 代理钱包 */
    LEFT JOIN agent_withdraw aw ON aw.owner_id = mu.id /* 关联代理提现 */
WHERE
    ai.deleted_at IS NULL /* 代理信息未删除 */
    AND mu.deleted_at IS NULL /* 会员用户未删除 */
    AND w.deleted_at IS NULL;
/* 钱包未删除 */