-- DELIMITER //

CREATE PROCEDURE $procName(IN v_start VARCHAR(100), IN v_end VARCHAR(100))
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
    BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE rec ROW(
            v_user_profile_id BIGINT(20),
            v_user_profile_balance_id BIGINT(20),
            v_op_id BIGINT(20),
            v_ma_id BIGINT(20),
            v_ag_id BIGINT(20),
            v_game_provider_game_id BIGINT(20),
            v_game_offering_id BIGINT(20),
            v_bet_id VARCHAR(255),
            v_player_id VARCHAR(255),
            v_balance_category VARCHAR(100),
            v_channel_type VARCHAR(255),
            v_outlet_id VARCHAR(255),
            v_kiosk_terminal_id VARCHAR(255),
            v_is_jackpot_win VARCHAR(255),
            v_total_bet_amount DECIMAL(65,30),
            v_total_payout_amount DECIMAL(65,30),
            v_total_jackpot_amount DECIMAL(65,30),
            v_sidebet_amount DECIMAL(65,30),
            v_refund_amount DECIMAL(65,30),
            v_bet_balance_before DECIMAL(65,30),
            v_bet_balance_after DECIMAL(65,30),
            v_win_balance_before DECIMAL(65,30),
            v_win_balance_after DECIMAL(65,30),
            v_barkada_session_uuid VARCHAR(100),
            v_bet_date_time VARCHAR(100),
            v_settlement_date_time VARCHAR(100),
            v_platform_code VARCHAR(100),
            v_game_offering VARCHAR(100),
            v_bet_status VARCHAR(100),
            v_game_code VARCHAR(100),
            v_casino_bet_details LONGTEXT,
            v_sports_bet_details LONGTEXT,
            v_key_token CHAR(36),
            v_current_datetime VARCHAR(100)
        );
        DECLARE c CURSOR FOR
            SELECT
                user_profile_id,
                user_profile_balance_id,
                op_id,
                ma_id,
                ag_id,
                game_provider_game_id,
                game_offering_id,
                bet_id,
                player_id,
                balance_category,
                channel_type,
                outlet_id,
                kiosk_terminal_id,
                is_jackpot_win,
                total_bet_amount,
                total_payout_amount,
                total_jackpot_amount,
                sidebet_amount,
                refund_amount,
                bet_balance_before,
                bet_balance_after,
                win_balance_before,
                win_balance_after,
                barkada_session_uuid,
                bet_date_time,
                settlement_date_time,
                platform_code,
                game_offering,
                bet_status,
                game_code,
                casino_bet_details,
                sports_bet_details,
                UUID() as key_token,
                CONVERT_TZ(NOW(), '+00:00', '+00:00') AS current_datetime -- Timezone GMT+08:00 is set
            FROM $viewName
            WHERE
                (settlement_date_time IS NOT NULL AND settlement_date_time BETWEEN v_start AND v_end)
                OR
                (bet_date_time IS NOT NULL AND bet_date_time BETWEEN v_start AND v_end);
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        OPEN c;
        read_loop:
            LOOP
                FETCH c INTO rec;
                    -- Break the loop if done
                    IF done THEN
                        LEAVE read_loop;
                    END IF;
                    INSERT INTO $reportName(
                        user_profile_id,
                        user_profile_balance_id,
                        op_id,
                        ma_id,
                        ag_id,
                        game_provider_game_id,
                        game_offering_id,
                        bet_id,
                        player_id,
                        balance_category,
                        channel_type,
                        outlet_id,
                        kiosk_terminal_id,
                        is_jackpot_win,
                        total_bet_amount,
                        total_payout_amount,
                        total_jackpot_amount,
                        sidebet_amount,
                        refund_amount,
                        bet_balance_before,
                        bet_balance_after,
                        win_balance_before,
                        win_balance_after,
                        barkada_session_uuid,
                        bet_date_time,
                        settlement_date_time,
                        platform_code,
                        game_offering,
                        bet_status,
                        game_code,
                        casino_bet_details,
                        sports_bet_details,
                        key_token,
                        created_at
                    )
                    VALUES (
                        rec.v_user_profile_id,
                        rec.v_user_profile_balance_id,
                        rec.v_op_id,
                        rec.v_ma_id,
                        rec.v_ag_id,
                        rec.v_game_provider_game_id,
                        rec.v_game_offering_id,
                        rec.v_bet_id,
                        rec.v_player_id,
                        rec.v_balance_category,
                        rec.v_channel_type,
                        rec.v_outlet_id,
                        rec.v_kiosk_terminal_id,
                        rec.v_is_jackpot_win,
                        rec.v_total_bet_amount,
                        rec.v_total_payout_amount,
                        rec.v_total_jackpot_amount,
                        rec.v_sidebet_amount,
                        rec.v_refund_amount,
                        rec.v_bet_balance_before,
                        rec.v_bet_balance_after,
                        rec.v_win_balance_before,
                        rec.v_win_balance_after,
                        rec.v_barkada_session_uuid,
                        rec.v_bet_date_time,
                        rec.v_settlement_date_time,
                        rec.v_platform_code,
                        rec.v_game_offering,
                        rec.v_bet_status,
                        rec.v_game_code,
                        rec.v_casino_bet_details,
                        rec.v_sports_bet_details,
                        rec.v_key_token,
                        rec.v_current_datetime
                    ) ON DUPLICATE KEY UPDATE
                        op_id                = rec.v_op_id,
                        ma_id                = rec.v_ma_id,
                        ag_id                = rec.v_ag_id,
                        total_bet_amount     = rec.v_total_bet_amount,
                        total_payout_amount  = rec.v_total_payout_amount,
                        total_jackpot_amount = rec.v_total_jackpot_amount,
                        sidebet_amount       = rec.v_sidebet_amount,
                        refund_amount        = rec.v_refund_amount,
                        bet_balance_before   = rec.v_bet_balance_before,
                        bet_balance_after    = rec.v_bet_balance_after,
                        win_balance_before   = rec.v_win_balance_before,
                        win_balance_after    = rec.v_win_balance_after,
                        barkada_session_uuid = rec.v_barkada_session_uuid,
                        bet_date_time        = rec.v_bet_date_time,
                        settlement_date_time = rec.v_settlement_date_time,
                        platform_code        = rec.v_platform_code,
                        game_offering        = rec.v_game_offering,
                        bet_status           = rec.v_bet_status,
                        game_code            = rec.v_game_code,
                        updated_at           = rec.v_current_datetime;
            END LOOP;
        CLOSE c;
    END;
-- //
