CREATE TABLE IF NOT EXISTS `tab_db_version` (
  `db_version_id` INT NOT NULL AUTO_INCREMENT,
  `db_version` VARCHAR(100) NOT NULL,
  `db_version_create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `db_version_update_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`db_version_id`),
  UNIQUE INDEX `uq_tab_db_version_db_version` (`db_version` ASC));

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_set_db_version`$$

CREATE PROCEDURE `sp_set_db_version`(
	IN `script_name` VARCHAR(100)
)
LANGUAGE SQL
DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY INVOKER
COMMENT 'Sets a DB version'
BEGIN
    IF (script_name NOT LIKE '%.sql') THEN
        SET script_name = CONCAT(script_name, '.sql');
    END IF;
   
    INSERT INTO `tab_db_version`(`db_version`)
    VALUES(script_name)
    ON DUPLICATE KEY UPDATE
    `db_version` = VALUES(`db_version`);
END$$

DELIMITER ;

call `sp_set_db_version`('0000-initial-db-structure');
call `sp_set_db_version`('0001-initial-db-load');
call `sp_set_db_version`('0002-db-version');
