CREATE VIEW $viewName AS
SELECT
    t.user_profile_id,
    t.user_profile_balance_id,
    CASE
        WHEN tu.ag_id IS NOT NULL THEN tu.ag_id
        WHEN role1.slug = 'agent' THEN usp1.id
        ELSE null
    END as ag_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 ma_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 op_id,
    t.game_provider_game_id,
    t.game_offering_id,
    t.transaction_no AS bet_id,
    usp.account_no AS player_id,
    t.balance_category,
    'RGP' AS channel_type,
    NULL AS outlet_id,
    NULL AS kiosk_terminal_id,
    CASE
        WHEN tf.is_progressive_jackpot_win = 1 THEN 1
        ELSE null
    END AS is_jackpot_win,
    t.amount AS total_bet_amount,
    CASE
        WHEN tf.id IS NULL THEN null
        WHEN tf.transaction_type_id = 15 THEN 0
        ELSE tf.amount
    END AS total_payout_amount,
    CASE
        WHEN tf.is_progressive_jackpot_win = 1 THEN tf.amount
        ELSE 0
    END AS total_jackpot_amount,
    0 AS sidebet_amount,
    CASE
        WHEN tf.id IS NULL THEN null
        WHEN tf.transaction_type_id = 15 THEN tf.amount
        ELSE 0
    END AS refund_amount,
    t.balance_before as bet_balance_before,
    t.balance_after as bet_balance_after,
    tf.balance_before as win_balance_before,
    tf.balance_after as win_balance_after,
    t.barkada_session_uuid,
    t.created_at AS bet_date_time,
    CASE
        WHEN tf.updated_at IS NULL THEN null
        ELSE tf.updated_at
    END AS settlement_date_time,
    CASE
        WHEN go.name = 'Sportsbetting'
        OR go.name = 'ESports' THEN 'SPORTSBOOK'
        WHEN go.name = 'Live Casino'
        OR go.name = 'LiveCasino' THEN 'EGAME'
        WHEN go.name = 'Electronic Games'
        OR go.name = 'ElectronicGames' THEN 'EGAME'
        ELSE UPPER(go.name)
    END AS platform_code,
    UPPER(go.name) as game_offering,
    CASE
        WHEN tf.id IS NULL THEN 'P'
        WHEN tf.transaction_type_id != 15 THEN 'D'
        ELSE 'X'
    END AS bet_status,
    g.audit_game_code as game_code,
    CASE
        WHEN go.name = 'Live Casino'
        OR go.name = 'LiveCasino'
        OR go.name = 'Electronic Games'
        OR go.name = 'ElectronicGames'
        OR go.name = 'Lotto' THEN JSON_OBJECT(
            'game_id',
            g.key_token,
            'game_name',
            g.game_name,
            'game_brand',
            gp.title,
            'game_type',
            gt.name,
            'live_game_round',
            NULL,
            'jackpot_contribution',
            0,
            'jackpot_type',
            CASE
                WHEN tf.is_progressive_jackpot_win = 1 THEN 'progressive'
                ELSE null
            END,
            'jackpot_payout',
            CASE
                WHEN tf.is_progressive_jackpot_win = 1 THEN tf.amount
                ELSE 0
            END,
            'seed_money',
            0,
            'seed_money_over',
            0,
            'seed_contribution',
            0
        )
        ELSE NULL
    END AS casino_bet_details,
    CASE
        WHEN go.name = 'Sportsbetting'
        OR go.name = 'ESports' THEN JSON_OBJECT(
            'game_brand',
            gp.title,
            'sports_name',
            CASE
                WHEN t.events IS NOT NULL
                AND (
                    JSON_TYPE(t.events) = 'OBJECT'
                    OR JSON_TYPE(t.events) = 'ARRA'
                    OR JSON_TYPE(t.events) = 'ARRAY'
                ) THEN CASE
                    WHEN JSON_LENGTH(t.events) <= 1 THEN JSON_EXTRACT(t.events, '$[0].sports_type')
                    ELSE 'MULTI'
                END
                ELSE NULL
            END,
            'league_name',
            CASE
                WHEN t.events IS NOT NULL
                AND (
                    JSON_TYPE(t.events) = 'OBJECT'
                    OR JSON_TYPE(t.events) = 'ARRA'
                    OR JSON_TYPE(t.events) = 'ARRAY'
                ) THEN CASE
                    WHEN JSON_LENGTH(t.events) <= 1 THEN JSON_EXTRACT(t.events, '$[0].sports_name')
                    ELSE 'PARLAY'
                END
                ELSE NULL
            END,
            'event_name',
            CASE
                WHEN t.events IS NOT NULL
                AND (
                    JSON_TYPE(t.events) = 'OBJECT'
                    OR JSON_TYPE(t.events) = 'ARRA'
                    OR JSON_TYPE(t.events) = 'ARRAY'
                ) THEN CASE
                    WHEN JSON_LENGTH(t.events) <= 1 THEN JSON_EXTRACT(t.events, '$[0].match_name')
                    ELSE 'PARLAY'
                END
                ELSE NULL
            END,
            'event_details',
            NULL,
            'bet_type',
            NULL
        )
        ELSE null
    END AS sports_bet_details

-- Source parent table
FROM transactions AS t

-- Win/Credit Transactions
LEFT JOIN transactions AS tf ON tf.transaction_reference_id = t.id

-- Transaction users
LEFT JOIN transaction_users AS tu ON tu.transaction_id = tf.id

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

-- Game offerings, Provider, Types and Games
LEFT JOIN game_offerings AS go ON go.id = t.game_offering_id
LEFT JOIN game_provider_games AS g ON g.id = t.game_provider_game_id
LEFT JOIN game_providers AS gp ON gp.id = g.game_provider_id
LEFT JOIN game_types AS gt ON gt.id = g.game_type_id

-- FILTERS & CONDITIONS
WHERE
    -- Only bet transactions
    t.transaction_type_id IN (5, 7)
    -- AND tf.transaction_reference_id IS NOT NULL
ORDER BY t.created_at ASC;
