use smart_data_db;

DELIMITER $$

DROP PROCEDURE IF EXISTS `debug_msg` $$
DROP PROCEDURE IF EXISTS test $$

CREATE PROCEDURE debug_msg(enabled INTEGER, msg VARCHAR(255))
BEGIN
  IF enabled THEN BEGIN
    select concat("** ", msg) AS '** DEBUG:';
  END; END IF;
END $$
CREATE PROCEDURE test()
block1: BEGIN
	DECLARE done INT;
    DECLARE count_debug INT;
	DECLARE person_name VARCHAR(100);
    DECLARE person_alias VARCHAR(100);
    DECLARE project_code VARCHAR(20);
    DECLARE author_name VARCHAR(100);
    DECLARE normalizations CURSOR FOR SELECT DISTINCT
											person.name, tct.alias, CONCAT('WOS:000', php.project_id), authors.name
										FROM
											graph.temp_conversion_table tct
												INNER JOIN
											graph.person ON tct.person_id = person.id
												INNER JOIN
											graph.project_has_person php ON php.person_id = tct.person_id
												INNER JOIN
											smart_data_db.isi_publication_author_institutions ipai ON ipai.isi_publication_UT = CONCAT('WOS:000', php.project_id)
												AND ipai.signature = graph.person.name
												INNER JOIN
											smart_data_db.authors ON smart_data_db.authors.id = ipai.author_id
										ORDER BY tct.date DESC , tct.alias;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
	SET done = 0;
    SET count_debug = 0;

    OPEN normalizations;
    loop1: LOOP
		FETCH normalizations INTO person_name, person_alias, project_code, author_name;
        IF done = 1 THEN LEAVE loop1; END IF;
        SET count_debug = count_debug + 1;
        IF (count_debug%1000)= 0 THEN CALL debug_msg(TRUE, CONCAT("PROCESADAS: ",count_debug)); END IF;
        IF author_name != person_alias THEN
			INSERT INTO smart_data_db.authors (name) SELECT 'name' FROM smart_data_db.authors WHERE NOT EXISTS (SELECT * FROM smart_data_db.authors WHERE name=person_alias) LIMIT 1;
            UPDATE smart_data_db.isi_publication_author_institutions SET author_id=(SELECT id FROM smart_data_db.authors WHERE smart_data_db.authors.name=person_alias LIMIT 1) WHERE smart_data_db.isi_publication_author_institutions.isi_publication_UT = project_code AND smart_data_db.isi_publication_author_institutions.signature = person_name;
        END IF;
    END LOOP loop1;

    CLOSE normalizations;
	SELECT @ipais;
END block1 $$

DELIMITER ;

CALL test();