CREATE DATABASE IF NOT EXISTS `<%= masterDbName %>` DEFAULT CHARACTER SET utf8;
USE `<%= masterDbName %>`;

CREATE TABLE IF NOT EXISTS `release_version` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `patch_id` varchar(30) NOT NULL COMMENT 'Tag ID in gitlab',
  `release_desc` varchar(100) NOT NULL COMMENT 'release description',
  `patch_start_date` datetime DEFAULT NULL,
  `patch_end_date` datetime DEFAULT NULL,
  `rollback_date` varchar(500) DEFAULT NULL COMMENT 'System Notes',
  `rollback` tinyint(3) unsigned DEFAULT '0' COMMENT '0:No Rollback, 1:Rollback',
  `status` tinyint(3) unsigned DEFAULT '0' COMMENT '0-Pending, 1-Successful, 4-Failed, 3-InProgress',
  `cby` varchar(50) DEFAULT 'SYS',
  `cdate` datetime NOT NULL,
  `mby` varchar(50) DEFAULT NULL,
  `mdate` datetime DEFAULT NULL,
  `remarks` varchar(200) DEFAULT NULL COMMENT 'patching remarks',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `patch_id_UNIQUE` (`patch_id` ASC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='For database object patching purposes';

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_get_version`$$
CREATE PROCEDURE `sp_get_version`(
	IN p_target_version varchar(30)
)
SQL SECURITY INVOKER
BEGIN
    DECLARE latest varchar(30);

    SELECT patch_id INTO latest
    FROM release_version
    ORDER BY cdate DESC LIMIT 1;

    IF latest IS NOT NULL THEN
        IF latest != p_target_version THEN
            IF EXISTS(SELECT 1 FROM release_version WHERE patch_id = p_target_version) THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicated target version';
            END IF;
        END IF;
    END IF;

    SELECT IFNULL(latest, '') AS `version`;
END$$
DELIMITER ;

CALL sp_get_version('{{VERSION}}');
