-- Active: xiaoding_test@@192.168.110.85@3306@xiaoding_test

/*
区域、交易时间、代理商分账都是wallet_trans_records表role字段为代理商角色然后分别获取districe、trans_time、amount字段，
代理商是wallet_trans_records表role字段为代理商角色然后获取wallet_id字段关联wallet表获取owner_id字段再与agent_infos表关联通过agent_infos表的id进行对比然后获取user_id字段和member_users表关联通过member_users的id对比最后获取nickname字段做为代理商，
平台分账与代理商分账是一样的数据，市场经费是代理商分账的一半
*/

/* 区域、交易时间、代理商、代理商分账、平台分账、市场经费统计 */
DROP TABLE IF EXISTS report_agent_income;

CREATE TABLE report_agent_income AS

DROP VIEW IF EXISTS view_agent_income;

CREATE OR REPLACE VIEW view_agent_income AS
WITH
    trans_base AS (
        SELECT wtr.district /* 区域 */, wtr.trans_time /* 交易时间 */, wtr.wallet_id /* 钱包ID */, wtr.amount /* 交易金额 */, w.owner_id /* 所属主体编号 */
        FROM
            wallet_trans_records wtr
            INNER JOIN wallet w ON w.id = wtr.wallet_id
        WHERE
            wtr.role = 6
            AND wtr.deleted_at IS NULL
    )
SELECT
    tb.district as district /* 区域 */,
    tb.trans_time as trans_time /* 交易时间 */,
    mu.nickname as nickname /* 代理商昵称 */,
    tb.amount as agent_amount /* 代理商分账 */,
    tb.amount as platform_amount /* 平台分账 */,
    tb.amount / 2 as market_amount /* 市场经费 */
FROM
    trans_base tb
    INNER JOIN agent_infos ai ON ai.id = tb.owner_id
    AND ai.deleted_at IS NULL
    INNER JOIN member_users mu ON mu.id = ai.user_id
    AND mu.deleted_at IS NULL
ORDER BY tb.district, tb.trans_time DESC;

select * from view_agent_income;