DROP PROCEDURE IF EXISTS `migrate`;
DELIMITER //
CREATE PROCEDURE `migrate`()
BEGIN
    DECLARE _id INT DEFAULT 0;
    DECLARE _type VARCHAR(255);
    DECLARE _title, _authors VARCHAR(255);
    DECLARE _year VARCHAR(255);
    DECLARE _fPage, _sPage VARCHAR(255);
    DECLARE _volume, _issue VARCHAR(255);
    DECLARE _journal, _publisher, _book, _bookEditor VARCHAR(255);
    DECLARE _seriestitle VARCHAR(255);
    DECLARE _abstract TEXT;
    DECLARE _isbn VARCHAR(255);
    DECLARE _file, _showFile VARCHAR(255);

    DECLARE items CURSOR FOR (SELECT `ctidx`, `cttype`, CONVERT(`cttitle` USING utf8) as cttitle,`ctauthors`,`ctyear`,`ctfpag`,`ctspag`,`ctjournal`, `ctvolume`, `ctbkchapter`, `cteditors`, `ctissue`, `ctisbn`, `ctpublisher`, `ctseriestitle`, CONVERT(`ctabstract` USING utf8) as ctabstract, `ctpdflink`, `showlink`
        FROM `test`.`citations_test`);
    
    TRUNCATE `sohodev_quicksilk2`.`bibliography_bibliography`;
    
    OPEN items;
        items_loop: 
        LOOP FETCH items INTO _id, _type, _title, _authors, _year, _fPage, _sPage, _journal, _volume, _book, _bookEditor, _issue, _isbn, _publisher, _seriestitle, _abstract, _file, _showFile;

            IF NOT _id THEN
                LEAVE items_loop;
            END IF;

            IF _showFile = 'Y' THEN 
                SET _showFile := 'Enabled';
            ELSE 
                SET _showFile := 'Disable';
            END IF;

            IF _file IS NOT NULL AND LENGTH(_file) > 1 THEN
                SET _file:=CONCAT('/cera/default/', _file);
            ELSE
                SET _file := '';
            END IF;

            INSERT INTO `sohodev_quicksilk2`.`bibliography_bibliography` 
                (`type`, `title`, `authors`, `year`, `firstPage`, `lastPage`, `journal`, `volume`, `book`, `bookEditor`, `issue`, 'isbn', `publisher`, `seriesTitle`, `abstract`, `file`, `showFile`) 
            VALUES 
                (_type, _title, _authors, _year, _fPage, _sPage, _journal, _volume, _book, _bookEditor, _issue, _isbn, _publisher, _seriestitle, _abstract, _file, _showFile);

        END LOOP items_loop;
    CLOSE items;   
END//
DELIMITER ;

DROP PROCEDURE IF EXISTS `fixedJournal`;
DELIMITER //
CREATE PROCEDURE `fixedJournal`()
BEGIN
    DECLARE _id INT DEFAULT 0;
    DECLARE _journal VARCHAR(255);
    DECLARE _exist INT DEFAULT 0;
    DECLARE _setID VARCHAR(255) DEFAULT '';
    DECLARE items CURSOR FOR (SELECT `id`, `journal` FROM bibliography_bibliography WHERE `journal` != '');

    TRUNCATE `sohodev_quicksilk2`.`bibliography_journal`;

    OPEN items;
        items_loop: 
        LOOP FETCH items INTO _id, _journal;

            IF NOT _id THEN
                LEAVE items_loop;
            END IF;

            -- SELECT _id, _publisher;

            IF _journal REGEXP '^[1-9]?[0-9]+$' = 0 THEN
                SET _exist:=( SELECT COUNT(*) FROM bibliography_journal WHERE `title` LIKE _journal );

                IF _exist THEN
                    SET _setID:=( SELECT `id` FROM bibliography_journal WHERE `title` LIKE _journal LIMIT 1);
                    UPDATE bibliography_bibliography SET journal = _setID WHERE id = _id;
                ELSE
                    INSERT INTO bibliography_journal SET title = _journal;
                    UPDATE bibliography_bibliography SET journal = LAST_INSERT_ID() WHERE id = _id;
                END IF;
            END IF;
        END LOOP items_loop;
    CLOSE items;
END//
DELIMITER ;

DROP PROCEDURE IF EXISTS `fixedPublisher`;
DELIMITER //
CREATE PROCEDURE `fixedPublisher`()
BEGIN
    DECLARE _id INT DEFAULT 0;
    DECLARE _publisher VARCHAR(255);
    DECLARE _exist INT DEFAULT 0;
    DECLARE _setID VARCHAR(255) DEFAULT '';
    DECLARE items CURSOR FOR (SELECT `id`, `publisher` FROM `bibliography_bibliography` WHERE `publisher` != '');

    TRUNCATE `sohodev_quicksilk2`.`bibliography_publisher`;

    OPEN items;
        items_loop: 
        LOOP FETCH items INTO _id, _publisher;

            IF NOT _id THEN
                LEAVE items_loop;
            END IF;

            -- SELECT _id, _publisher;

            IF _publisher REGEXP '^[1-9]?[0-9]+$' = 0 THEN
                SET _exist:=(SELECT COUNT(*) FROM bibliography_publisher WHERE `title` LIKE _publisher);

                IF _exist THEN
                    SET _setID:=( SELECT `id` FROM bibliography_publisher WHERE `title` LIKE _publisher LIMIT 1);
                    UPDATE bibliography_bibliography SET publisher = _setID WHERE id = _id;
                ELSE
                    INSERT INTO bibliography_publisher SET title = _publisher;
                    UPDATE bibliography_bibliography SET publisher = LAST_INSERT_ID() WHERE id = _id;
                END IF;
            END IF;
        END LOOP items_loop;
    CLOSE items;
END//
DELIMITER ;
