CREATE VIEW $viewName AS

SELECT
    DATE(t.created_at) AS transaction_date,
    t.user_profile_id,
    t.transaction_request_status_id,
    t.transaction_request_type_id,
    bc.id AS balance_category_id,
    c.id AS currency_id,
    CASE
        WHEN tru.ag_id IS NOT NULL THEN tru.ag_id
        WHEN role1.slug = 'agent' THEN usp1.id
        ELSE null
    END as agent_id,
    CASE
        WHEN tru.ma_id IS NOT NULL THEN tru.ma_id
        WHEN role1.slug = 'master-agent' THEN usp1.id
        WHEN role2.slug = 'master-agent' THEN usp2.id
        ELSE null
    END as master_agent_id,
    CASE
        WHEN tru.op_id IS NOT NULL THEN tru.op_id
        WHEN role2.slug = 'operator' THEN usp2.id
        WHEN role3.slug = 'operator' THEN usp3.id
        WHEN role1.slug = 'operator' THEN usp1.id
        ELSE null
    END as operator_id,
    SUM(ABS(t.amount)) as amount,
    SUM(ABS(t.fee_amount)) as fee_amount,
    SUM(ABS(t.fix_fee_amount)) as fix_fee_amount,
    SUM(ABS(t.convenience_fee_amount)) as convenience_fee_amount,
    SUM(ABS(t.platform_fee_amount)) as platform_fee_amount,
    pm.parent_method as payment_parent_method,
    pm.name as payment_method_name,
    trt.name AS type_name,
    trs.name AS status_name,
    bc.key_name AS balance_category,
    c.code AS currency
FROM transaction_requests AS t

-- Transaction request users
LEFT JOIN transaction_request_users as tru ON tru.transaction_request_id = t.id

-- Payment Method
LEFT JOIN payment_methods as pm ON pm.id = t.payment_method_id

-- Player User
LEFT JOIN user_profiles as usp ON usp.id = t.user_profile_id

-- Agent User
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

-- Master Agent 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

-- Operator User
LEFT JOIN user_profiles as usp3 ON usp3.id = usp2.referred_by
LEFT JOIN users AS usr3 ON usr3.id = usp3.user_id
LEFT JOIN user_roles AS usrl3 ON usrl3.user_id = usr3.id
LEFT JOIN roles AS role3 ON role3.id = usrl3.role_id

-- Transaction request types
LEFT JOIN transaction_request_types as trt ON trt.id = t.transaction_request_type_id

-- Transaction request status
LEFT JOIN transaction_request_statuses AS trs ON trs.id = t.transaction_request_status_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 c ON c.id = bt.currency_id
GROUP BY
    t.user_profile_id,
    DATE(t.created_at),
    t.transaction_request_type_id,
    t.transaction_request_status_id,
    bt.balance_category_id,
    t.payment_method_id
ORDER BY t.user_profile_id ASC, t.created_at ASC
