CREATE VIEW $viewName AS

    SELECT * FROM (
        SELECT
        -- Add Index
        CASE -- Date created based on win transaction
            WHEN t.updated_at IS NULL THEN DATE(c.created_at)
            ELSE DATE(t.updated_at)
            END AS transaction_date,

        c.user_profile_id, -- MA/Agent's ID
        bt.currency_id,
        bc.id AS balance_category_id,
        g.game_provider_id,
        t.game_offering_id,
        role.id AS role_id,
        CASE
            WHEN role1.slug = 'master-agent' THEN usp1.id
                ELSE NULL
        END as master_agent_id,
        CASE
            WHEN role1.slug = 'operator' THEN usp1.id
            WHEN role2.slug = 'operator' THEN usp2.id
                ELSE NULL
        END AS operator_id,

        -- Normal column without index
        bc.key_name AS balance_category,
        cr.code AS currency,
        go.name AS game_offering_name,
        gp.name AS game_provider_name,
        COUNT(1) AS total_data,
        SUM(c.rate) AS total_rate,
        (SUM(c.rate) / COUNT(1)) AS average_rate,
        SUM(c.amount) AS amount, -- commission amount
        SUM(CASE WHEN c.amount > 0 THEN c.amount ELSE 0 END) AS total_amount_positive, -- commission total amount positive
        SUM(CASE WHEN c.amount < 0 THEN c.amount ELSE 0 END) AS total_amount_negative -- commission total amount negative

        FROM commissions AS c

            -- MA/Agent user 1 [Owner of comms]
                LEFT JOIN user_profiles AS usp
                    ON usp.id = c.user_profile_id
                LEFT JOIN users AS usr
                    ON usr.id = usp.user_id
                LEFT JOIN user_roles AS usrl
                    ON usrl.user_id = usr.id
                LEFT JOIN roles AS role
                    ON role.id = usrl.role_id

            -- Master Agent User 2
            LEFT JOIN user_profiles as usp1
                ON usp1.id = usp.referred_by
            LEFT JOIN users AS usr1
                ON usr1.id = usp1.user_id
            LEFT JOIN user_roles AS usrl1
                ON usrl1.user_id = usr1.id
            LEFT JOIN roles AS role1
                ON role1.id = usrl1.role_id

        -- Operator user
            LEFT JOIN user_profiles as usp2
                ON usp2.id = usp1.referred_by
            LEFT JOIN users AS usr2
                ON usr2.id = usp2.user_id
            LEFT JOIN user_roles AS usrl2
                ON usrl2.user_id = usr2.id
            LEFT JOIN roles AS role2
                ON role2.id = usrl2.role_id

            -- Win Transactions Table
                LEFT JOIN transactions AS t
                    ON c.transaction_id = t.id

            -- Game provider games details
                LEFT JOIN game_provider_games AS g
                ON g.id = t.game_provider_game_id

        -- Game provider
            LEFT JOIN game_providers AS gp
                ON g.game_provider_id = gp.id

            -- Game offering details
            LEFT JOIN game_offerings AS go
                ON go.id = t.game_offering_id

            -- User balance category
            LEFT JOIN user_profile_balances AS uspb
                ON uspb.id = t.user_profile_balance_id
            LEFT JOIN balance_types AS bt
                ON bt.id = uspb.balance_type_id
            LEFT JOIN balance_categories AS bc
                ON bc.id = bt.balance_category_id
            LEFT JOIN currencies AS cr
                ON cr.id = bt.currency_id
        WHERE t.id IS NOT NULL -- Only present transaction id are allowed
            AND t.transaction_type_id != 15 -- All transaction not cancelled, only settled transactions only
        GROUP BY c.user_profile_id, transaction_date, bt.balance_category_id, bt.currency_id, g.game_provider_id, t.game_offering_id, role.id
            ORDER BY transaction_date ASC
        -- ORDER BY transaction_date ASC
    ) AS z WHERE operator_id IS NOT NULL
