DELIMITER $$
CREATE TRIGGER count_and_insert BEFORE INSERT ON messages
FOR EACH ROW 
BEGIN
SET @CNT =  (SELECT count(*) FROM messages WHERE hash = NEW.hash);
	IF @CNT > 100 THEN
		INSERT INTO history_chats (id_table_messages,idchat, iduser,`text`,`hash`,message_sended,message_delivered,date_create,`delete`)
		SELECT * FROM (SELECT * FROM `messages` WHERE `hash` = NEW.hash ORDER BY id DESC LIMIT 1) t  ORDER BY t.id;
	END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER delete_messages AFTER INSERT ON history_chats
FOR EACH ROW 
BEGIN
		DELETE FROM messages WHERE id = NEW.id_table_messages;
END$$
DELIMITER ;

DELIMITER ||;
	BEGIN
    CREATE PROCEDURE delete_messages(IN id INT)
    DELETE FROM messages WHERE id =  id;
	END ||
DELIMITER ;

delimiter //
CREATE PROCEDURE deletemessages (IN id INT)
     BEGIN
       SET SQL_SAFE_UPDATES = 0;
       DELETE FROM messages WHERE id =  id;
    END//
delimiter ;

-- DROP TRIGGER count_and_insert;
DELIMITER $$
CREATE TRIGGER count_and_insert BEFORE INSERT ON messages
FOR EACH ROW 
BEGIN
SET @CNT =  (SELECT count(*) FROM messages WHERE hash = NEW.hash);
    IF @CNT > 5 THEN
            INSERT INTO history_chats (id_table_messages,idchat, iduser,`text`,`hash`,message_sended,message_delivered,date_create,`delete`)
	    SELECT * FROM (SELECT * FROM `messages` WHERE `hash` = NEW.hash ORDER BY id DESC LIMIT 1) t  ORDER BY t.id;
    END IF;
END$$
DELIMITER ;

-- DROP TRIGGER delete_messages;
DELIMITER $$
CREATE TRIGGER delete_messages AFTER INSERT ON history_chats
FOR EACH ROW 
BEGIN
		  CALL deletemessages(NEW.id_table_messages);
END$$
DELIMITER ;

-- DROP PROCEDURE deletemessages;
delimiter //
CREATE PROCEDURE deletemessages (IN id_message INT)
     BEGIN
            DELETE FROM messages WHERE id = id_message;
     END//
delimiter ;

 DROP TRIGGER count_and_insert;

DELIMITER $$
CREATE TRIGGER count_and_insert AFTER INSERT ON messages
FOR EACH ROW 
BEGIN
		CALL insert_into_messages (NEW.idchat,NEW.iduser,NEW.text,NEW.hash,NEW.message_sended,NEW.message_delivered);
END$$
DELIMITER ;

DROP PROCEDURE insert_into_messages;
delimiter //
CREATE PROCEDURE insert_into_messages (IN chat INT, user INT,message TEXT,m_hash varchar(255),m_s INT,m_d INT)
     BEGIN
      INSERT INTO messages (idchat,iduser,`text`,`hash`,message_sended,message_delivered) values (chat, user,message,m_hash,m_s,m_d);
    IF (SELECT COUNT(*) FROM messages WHERE `hash` = m_hash) > 5 THEN
		INSERT INTO history_chats (id_table_messages,idchat, iduser,`text`,`hash`,message_sended,message_delivered,date_create,`delete`)
	    SELECT * FROM (SELECT * FROM `messages` WHERE `hash` = m_hash  ORDER BY id DESC LIMIT 1) t  ORDER BY t.id;
		DELETE messages.* 
			FROM messages 
			INNER JOIN history_chats 
				ON messages.id = history_chats.id;
	END IF;
     END//
delimiter ;