CREATE VIEW $viewName AS
-- GET ALL THE BET TRANSACTIONS
SELECT DATE(tf.created_at) AS transaction_date, tf.user_profile_id,

CASE
    WHEN tu.ag_id IS NOT NULL THEN tu.ag_id
    WHEN role1.slug = 'agent' THEN usp1.id
    ELSE null
END as agent_id,

CASE
    WHEN tu.ma_id IS NOT NULL THEN tu.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 tu.op_id IS NOT NULL THEN tu.op_id
    WHEN role2.slug = 'operator' THEN usp2.id
    WHEN role3.slug = 'operator' THEN usp3.id
    ELSE null
END as operator_id,

go.id AS game_offering_id, gp.id as game_provider_id,

t.game_provider_game_id game_id,
bc.id AS balance_category_id,
c.id AS currency_id,

go.name AS game_offering_name,
gp.name AS game_provider_name,
g.game_name,

bc.key_name AS balance_category,
c.code AS currency,
SUM(ABS(t.amount)) AS bet_amount,
SUM(ABS(t.preserve_amount)) AS preserve_amount,
-- SUM(ABS(t.amount) - ABS(t.preserve_amount)) as turn_over,
SUM(ABS(t.amount)) as turn_over,

CASE
    WHEN tf.is_progressive_jackpot_win >= 1 THEN 0.00
    ELSE SUM(tf.amount)
END AS win_amount,
CASE
    WHEN tf.is_progressive_jackpot_win >= 1 THEN SUM(tf.amount)
    ELSE 0.00
END AS jackpot_amount,
COUNT(1) AS total_bet_count

FROM
    transactions AS t
    -- Win/Credit Transactions
    LEFT JOIN transactions AS tf ON tf.transaction_reference_id = t.id
    LEFT JOIN game_provider_games AS g ON g.id = t.game_provider_game_id
    LEFT JOIN game_offerings AS go ON go.id = t.game_offering_id
    -- Transaction users
    LEFT JOIN transaction_users as tu ON tu.transaction_id = tf.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
    -- User balance category
    LEFT JOIN user_profile_balances AS uspb ON uspb.id = tf.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
    -- Game provider
    LEFT JOIN game_providers AS gp ON g.game_provider_id = gp.id
    -- Get only the settled bet transactions
WHERE
    tf.transaction_reference_id IS NOT NULL
    -- Only valid bets, remove the cancelled bets
    AND tf.transaction_type_id != 15
    -- Only bet transactiopns
    AND (
        t.transaction_type_id = 5
        OR t.transaction_type_id = 7
    )
    -- Only EGames & Live Casino (Just comment this filter if you want to include all game offerings
    -- AND (go.slug = 'electronic-games' OR go.slug = 'live-casino')
    -- Date must be based on settled date
    -- AND tf.created_at BETWEEN '2023-05-05 00:00:00'
    -- AND  '2023-05-15 23:59:59'
GROUP BY
    t.game_provider_game_id,
    t.user_profile_id,
    DATE(tf.created_at),
    tf.is_progressive_jackpot_win,
    bt.balance_category_id,
    tu.op_id,
    tu.ma_id,
    tu.ag_id
ORDER BY tf.user_profile_id ASC, tf.created_at ASC, bt.balance_category_id ASC;