CREATE DEFINER=`myroot`@`%` PROCEDURE `SP_OrganizaEnvioPesquisa`()
BEGIN
    DECLARE no_more_rows BOOLEAN DEFAULT FALSE;
    DECLARE registrantsIds INT DEFAULT 0;

    DECLARE cur CURSOR FOR
    SELECT RegistrantId FROM db_didyoubuy.Questionario GROUP BY RegistrantId;

    DECLARE CONTINUE handler FOR NOT FOUND SET no_more_rows := TRUE;

    OPEN cur;

    questoes : LOOP

    FETCH cur into registrantsIds;
        IF no_more_rows THEN
            leave questoes;
        CLOSE cur;
    END IF;

    INSERT IGNORE INTO db_didyoubuy.Grupo (GrupoId, RegistrantIdReferencia) SELECT UUID(), registrantsIds;
    COMMIT;

    INSERT IGNORE INTO db_didyoubuy.QuestionarioENV (IdQuestionario, NomeDestinatario, EmailDestinatario, TelefoneDestinatario, GrupoId, DataVisita,
    DataEnvio, DataResposta, RegistrantId, HeadingId, HeadingName, CompanyId, CompanyName, TipoVisita, TotalAdPoints, AWSMessageId)
    SELECT
         Id AS IdQuestionario,
         NomeDestinatario,
         EmailDestinatario,
         TelefoneDestinatario,
         (select GrupoId from db_didyoubuy.Grupo where RegistrantIdReferencia = registrantsIds) as Grupo,
         DataVisita,
         now() AS DataEnvio,
         now() AS DataResposta,
         RegistrantId,
         HeadingId,
         HeadingName,
         CompanyId,
         CompanyName,
         TipoVisita,
         TotalAdPoints,
         UUID() AS AWSMessageId
     FROM
     db_didyoubuy.Questionario
     WHERE RegistrantId = registrantsIds ORDER BY TotalAdPoints DESC LIMIT 5;
    COMMIT;

    END LOOP questoes;

    CLOSE cur;

END