| TennisProcessSubqueueV2 | IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`%` PROCEDURE `TennisProcessSubqueueV2`()
proc:BEGIN
        -- 26.08.2024.
        DECLARE pMessageSubqueueTennisId BIGINT(20);
        DECLARE pTEEventId BIGINT(20);
        DECLARE pAction VARCHAR(20);
        DECLARE pSubaction VARCHAR(20);
        DECLARE done BOOLEAN;
        DECLARE pStatus VARCHAR(20);
        DECLARE pMessage MEDIUMTEXT;
        DECLARE pBUFFERSIZE INT(11) DEFAULT 5;
        DECLARE pCLONEMESSAGES INT(11) DEFAULT 0;
        DECLARE pMon INT(11);
        DECLARE pInlinePrices MEDIUMBLOB;
        DECLARE pTradedPrices MEDIUMBLOB;
        DECLARE pManualPrices MEDIUMBLOB;
        DECLARE pHomeScore INT(11);
        DECLARE pAwayScore INT(11);
        DECLARE pJsonScore MEDIUMTEXT;
        DECLARE pPhase INT(11);
        DECLARE pSubscriberId INT(11);
        DECLARE pSuperfixtureId BIGINT(20);
        DECLARE pBetradarId BIGINT(20);
        DECLARE pMatchJson LONGTEXT;
        DECLARE pOutsideRef VARCHAR(25);
        DECLARE pTag VARCHAR(255);
        DECLARE pKickoffTime DATETIME;
        DECLARE pNumberOfSets INT(11);
        DECLARE pGroupId INT(11);
        DECLARE pCompetitionId INT(11);
        DECLARE pHomeId BIGINT(20);
        DECLARE pAwayId BIGINT(20);
        DECLARE pGroupName VARCHAR(255);
        DECLARE pCompetitionName VARCHAR(255);
        DECLARE pHomeTeamName VARCHAR(255);
        DECLARE pAwayTeamName VARCHAR(255);
        DECLARE CID BIGINT(20); -- premtch_main.competition
        DECLARE pEngineProfile INT(11);
        DECLARE pUserId INT(11);
        DECLARE pUserName VARCHAR(255);
        DECLARE curQueueId INT(11);
        DECLARE isSubscribed BOOLEAN DEFAULT TRUE;

        DECLARE curSubqueue CURSOR FOR
                SELECT message_subqueue_tennis_id, te_event_id, `action`, subaction, match_json, superfixture_id, betradar_id,
                `status`, inline_prices, traded_prices, manual_prices, home_score, away_score, json_score,
                `phase`, mon, user_id, outside_ref, tag, kickoff_time, number_of_sets,
                group_id, competition_id, home_id, away_id,
                group_name, competition_name, home_team_name, away_team_name,
                engine_profile, `user`
                FROM message_subqueue_tennis;
        DECLARE curQueueIds CURSOR FOR
                SELECT queue_id
                FROM prematch_main.pusher
                WHERE subscriber_id = pSubscriberId
                AND `active` = 1
                AND sport_id = 'TENNIS';
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

        SELECT value_int INTO pBUFFERSIZE FROM config WHERE `name` = 'MESSAGE_QUEUE_SIZE';
        SELECT value_int INTO pCLONEMESSAGES FROM config WHERE `name` = 'CLONE_MESSAGES';
        SELECT subscriber_id INTO pSubscriberId FROM prematch_main.subscriber WHERE db_database = DATABASE();

        OPEN curSubqueue;
lSubqueue:
        LOOP
                SET done = FALSE;

                FETCH NEXT FROM curSubqueue INTO
                        pMessageSubqueueTennisId, pTEEventId, pAction, pSubaction, pMatchJson, pSuperfixtureId, pBetradarId,
                        pStatus, pInlinePrices, pTradedPrices, pManualPrices, pHomeScore, pAwayScore, pJsonScore,
                        pPhase, pMon, pUserId, pOutsideRef, pTag, pKickoffTime, pNumberOfSets,
                        pGroupId, pCompetitionId, pHomeId, pAwayId,
                        pGroupName, pCompetitionName, pHomeTeamName, pAwayTeamName,
                        pEngineProfile, pUserName;

                IF done = TRUE THEN
                        LEAVE lSubqueue;
                END IF;

                SELECT competition_id INTO CID FROM prematch_main.superfixture WHERE superfixture_id = pSuperfixtureId;

                SET isSubscribed = TRUE;

                SELECT sc.auto_subscribe
                INTO isSubscribed
                FROM prematch_main.subscriber_competition sc
                WHERE sc.competition_id = CID
                AND subscriber_id = pSubscriberId;

                IF isSubscribed = FALSE AND pMon = 1 THEN
                        DELETE FROM message_subqueue_tennis WHERE message_subqueue_tennis_id = pMessageSubqueueTennisId;
                        ITERATE lSubqueue;
                END IF;

                OPEN curQueueIds;
l1:
                LOOP
                        SET done = FALSE;
                        FETCH NEXT FROM curQueueIds INTO curQueueId;
                        IF done = TRUE THEN
                                LEAVE l1;
                        END IF;

                        IF (SELECT COUNT(*) FROM message_queue_tennis WHERE queue_id = curQueueId) < pBUFFERSIZE AND pTradedPrices IS NOT NULL THEN
                                INSERT INTO message_queue_tennis
                                (queue_id, te_event_id, created, `action`, subaction, `status`, inline_prices, traded_prices, manual_prices,
                                home_score, away_score, json_score, `phase`, mon, match_json, superfixture_id, betradar_id,
                                outside_ref, tag, kickoff_time, number_of_sets, group_id, competition_id, home_id, away_id,
                                group_name, competition_name, home_team_name, away_team_name,
                                engine_profile, user_id, `user`)
                                VALUES
                                (curQueueId, pTEEventId, NOW(), pAction, pSubAction, pStatus, pInlinePrices, pTradedPrices, pManualPrices,
                                pHomeScore, pAwayScore, pJsonScore, pPhase, pMon, pMatchJson, pSuperfixtureId, pBetradarId,
                                pOutsideRef, pTag, pKickoffTime, pNumberOfSets, pGroupId, pCompetitionId, pHomeId, pAwayId,
                                pGroupName, pCompetitionName, pHomeTeamName, pAwayTeamName,
                                pEngineProfile, pUserId, pUserName);
                        END IF;

                END LOOP;

                CLOSE curQueueIds;

                UPDATE te_event SET mon = mon + 1 WHERE te_event_id = pTEEventId;

                DELETE FROM message_subqueue_tennis WHERE message_subqueue_tennis_id = pMessageSubqueueTennisId;
        END LOOP;

        CLOSE curSubqueue;
END | utf8mb4              | utf8mb4_general_ci   | utf8mb3_bin        |
+-------------------------+--------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+