CREATE TABLE `#__openaire_topic_hierarchy` (
	`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`parent` INT,
	`name` TEXT NOT NULL,
	`description` TEXT NOT NULL,
	`templateId` VARCHAR (36),
	`notificationService` VARCHAR (255),
	`queryId` VARCHAR (36),
	`resultId` VARCHAR (36),
	FOREIGN KEY (`parent`) REFERENCES `#__openaire_topic_hierarchy` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) DEFAULT CHARACTER SET = 'UTF8';

CREATE TABLE `#__openaire_alert_modes` (
	`id` VARCHAR (16) NOT NULL PRIMARY KEY,
	`name` VARCHAR (32) NOT NULL,
	`description` TEXT,
	`batch` BOOLEAN NOT NULL,
	`group` INTEGER UNSIGNED NOT NULL,
	FOREIGN KEY (`group`) REFERENCES `#__usergroups` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) DEFAULT CHARACTER SET = 'UTF8';
INSERT INTO `#__openaire_alert_modes` (`id`, `name`, `description`, `batch`, `group`)
	SELECT 'Atom', 'Atom Feed (RSS)', 'Post alert in OpenAIRE Atom feed.', FALSE, `id` FROM `#__usergroups` WHERE `title` = 'Administrator';
INSERT INTO `#__openaire_alert_modes` (`id`, `name`, `description`, `batch`, `group`)
	SELECT 'JDBC', 'OpenAIRE Portal - My Alerts', 'Post alert in OpenAIRE My Alerts.', FALSE, `id` FROM `#__usergroups` WHERE `title` = 'Registered';
INSERT INTO `#__openaire_alert_modes` (`id`, `name`, `description`, `batch`, `group`)
	SELECT 'Joomla!', 'OpenAIRE Portal - News', 'Post alert in OpenAIRE News.', FALSE, `id` FROM `#__usergroups` WHERE `title` = 'Administrator';
INSERT INTO `#__openaire_alert_modes` (`id`, `name`, `description`, `batch`, `group`)
	SELECT 'SMTP', 'Email', 'Send alert by email.', TRUE, `id` FROM `#__usergroups` WHERE `title` = 'Registered';
INSERT INTO `#__openaire_alert_modes` (`id`, `name`, `description`, `batch`, `group`)
	SELECT 'twitter', 'twitter', 'Post alert in OpenAIRE twitter account.', FALSE, `id` FROM `#__usergroups` WHERE `title` = 'Administrator';

