-- 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_transaction_date DATETIME,
            v_user_profile_id BIGINT(20),
            v_agent_id BIGINT(20),
            v_master_agent_id BIGINT(20),
            v_operator_id BIGINT(20),
            v_game_offering_id BIGINT(20),
            v_game_provider_id BIGINT(20),
            v_game_id BIGINT(20),
            v_balance_category_id BIGINT(20),
            v_currency_id BIGINT(20),
            v_game_offering_name VARCHAR(255),
            v_game_provider_name VARCHAR(255),
            v_game_name VARCHAR(255),
            v_balance_category VARCHAR(255),
            v_currency VARCHAR(255),
            v_bet_amount DECIMAL(20,6),
            v_preserve_amount DECIMAL(20,6),
            v_turn_over DECIMAL(20,6),
            v_win_amount DECIMAL(20,6),
            v_jackpot_amount DECIMAL(20,6),
            v_total_bet_count BIGINT(20),
            v_key_token CHAR(36),
            v_check_sum VARCHAR(100),
            v_current_datetime VARCHAR(100)
        );

        DECLARE c CURSOR FOR
            SELECT
                *,
                UUID() as key_token,
                MD5(
                CONCAT(
                    COALESCE(transaction_date,''),
                    COALESCE(user_profile_id,''),
                    COALESCE(agent_id,''),
                    COALESCE(master_agent_id,''),
                    COALESCE(operator_id,''),
                    COALESCE(game_offering_id,''),
                    COALESCE(game_provider_id,''),
                    COALESCE(game_id,''),
                    COALESCE(balance_category_id,''),
                    COALESCE(currency_id,'')
                )
                ) as check_sum,
                CONVERT_TZ(NOW(), '+00:00', '+00:00') AS current_datetime -- Timezone GMT+08:00 is set
            FROM(
                SELECT
                    DATE_FORMAT(
                    t.settlement_date_time,
                    '%Y-%m-%d %H:00:00'
                ) AS transaction_date,
                t.user_profile_id,
                t.ag_id AS agent_id,
                t.ma_id AS master_agent_id,
                t.op_id AS operator_id,
                t.game_offering_id,
                g.game_provider_id,
                t.game_provider_game_id AS 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.total_bet_amount)) AS bet_amount,
                0 AS preserve_amount,
                SUM(ABS(t.total_bet_amount)) as turn_over,
                SUM(t.total_payout_amount) AS win_amount,
                SUM(t.total_jackpot_amount) AS jackpot_amount,
                COUNT(1) AS total_bet_count
                FROM audit_bet_transactions AS t
                    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_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 c ON c.id = bt.currency_id
                WHERE t.bet_status = 'D' AND t.settlement_date_time BETWEEN v_start AND v_end
                GROUP BY
                    t.game_provider_game_id,
                    t.user_profile_id,
                    t.is_jackpot_win,
                        DATE_FORMAT(t.settlement_date_time, '%Y-%m-%d %H:00:00'),
                        t.balance_category,
                        t.op_id,
                        t.ma_id,
                        t.ag_id
                ORDER BY t.user_profile_id ASC, t.bet_date_time ASC, t.balance_category ASC
            ) AS wrapped_table;

        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(
                        transaction_date,
                        user_profile_id,
                        agent_id,
                        master_agent_id,
                        operator_id,
                        game_offering_id,
                        game_provider_id,
                        game_id,
                        balance_category_id,
                        currency_id,
                        game_offering_name,
                        game_provider_name,
                        game_name,
                        balance_category,
                        currency,
                        bet_amount,
                        preserve_amount,
                        turn_over,
                        win_amount,
                        jackpot_amount,
                        total_bet_count,
                        key_token,
                        check_sum,
                        created_at
                    )
                    VALUES (
                        rec.v_transaction_date,
                        rec.v_user_profile_id,
                        rec.v_agent_id,
                        rec.v_master_agent_id,
                        rec.v_operator_id,
                        rec.v_game_offering_id,
                        rec.v_game_provider_id,
                        rec.v_game_id,
                        rec.v_balance_category_id,
                        rec.v_currency_id,
                        rec.v_game_offering_name,
                        rec.v_game_provider_name,
                        rec.v_game_name,
                        rec.v_balance_category,
                        rec.v_currency,
                        rec.v_bet_amount,
                        rec.v_preserve_amount,
                        rec.v_turn_over,
                        rec.v_win_amount,
                        rec.v_jackpot_amount,
                        rec.v_total_bet_count,
                        rec.v_key_token,
                        rec.v_check_sum,
                        rec.v_current_datetime
                    ) ON DUPLICATE KEY UPDATE
                        game_offering_name = rec.v_game_offering_name,
                        game_provider_name = rec.v_game_provider_name,
                        game_name          = rec.v_game_name,
                        balance_category   = rec.v_balance_category,
                        currency           = rec.v_currency,
                        bet_amount         = rec.v_bet_amount,
                        preserve_amount    = rec.v_preserve_amount,
                        turn_over          = rec.v_turn_over,
                        win_amount         = rec.v_win_amount,
                        jackpot_amount     = rec.v_jackpot_amount,
                        total_bet_count    = rec.v_total_bet_count,
                        updated_at         = rec.v_current_datetime;

            END LOOP;
        CLOSE c;
    END;
-- //
