-- 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 DATE,
            v_user_profile_id BIGINT(20),
            v_role_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_balance_category_id BIGINT(20),
            v_currency_id BIGINT(20),
            v_game_offering_name VARCHAR(255),
            v_game_provider_name VARCHAR(255),
            v_balance_category VARCHAR(255),
            v_currency VARCHAR(255),
            v_total_data INT(10),
            v_total_rate DECIMAL(20,6),
            v_average_rate DECIMAL(20,6),
            v_amount DECIMAL(20,6),
            v_total_amount_positive DECIMAL(65,30),
            v_total_amount_negative DECIMAL(65,30),
            v_key_token CHAR(36),
            v_check_sum VARCHAR(100),
            v_current_datetime VARCHAR(100)
        );

        DECLARE c CURSOR FOR
            SELECT
                transaction_date,
                user_profile_id,
                role_id,
                master_agent_id,
                operator_id,
                game_offering_id,
                game_provider_id,
                balance_category_id,
                currency_id,
                game_offering_name,
                game_provider_name,
                balance_category,
                currency,
                total_data,
                total_rate,
                average_rate,
                amount,
                total_amount_positive,
                total_amount_negative,
                UUID() as key_token,
                MD5(
                    CONCAT(
                        COALESCE(transaction_date,''),
                        COALESCE(user_profile_id,''),
                        COALESCE(role_id,''),
                        COALESCE(master_agent_id,''),
                        COALESCE(operator_id,''),
                        COALESCE(game_offering_id,''),
                        COALESCE(game_provider_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 $viewName
            WHERE transaction_date 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(
                        transaction_date,
                        user_profile_id,
                        role_id,
                        master_agent_id,
                        operator_id,
                        game_offering_id,
                        game_provider_id,
                        balance_category_id,
                        currency_id,
                        game_offering_name,
                        game_provider_name,
                        balance_category,
                        currency,
                        total_data,
                        total_rate,
                        average_rate,
                        amount,
                        total_amount_positive,
                        total_amount_negative,
                        key_token,
                        check_sum,
                        created_at
                    )
                    VALUES (
                        rec.v_transaction_date,
                        rec.v_user_profile_id,
                        rec.v_role_id,
                        rec.v_master_agent_id,
                        rec.v_operator_id,
                        rec.v_game_offering_id,
                        rec.v_game_provider_id,
                        rec.v_balance_category_id,
                        rec.v_currency_id,
                        rec.v_game_offering_name,
                        rec.v_game_provider_name,
                        rec.v_balance_category,
                        rec.v_currency,
                        rec.v_total_data,
                        rec.v_total_rate,
                        rec.v_average_rate,
                        rec.v_amount,
                        rec.v_total_amount_positive,
                        rec.v_total_amount_negative,
                        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,
                        balance_category      = rec.v_balance_category,
                        currency              = rec.v_currency,
                        total_data            = rec.v_total_data,
                        total_rate            = rec.v_total_rate,
                        average_rate          = rec.v_average_rate,
                        amount                = rec.v_amount,
                        total_amount_positive = rec.v_total_amount_positive,
                        total_amount_negative = rec.v_total_amount_negative,
                        key_token             = rec.v_key_token,
                        check_sum             = rec.v_check_sum,
                        updated_at            = rec.v_current_datetime;

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