
CREATE TABLE `notification`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`type` VARCHAR(256),
	PRIMARY KEY (`id`)
);

CREATE TABLE `message_template`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`notification_id` INTEGER  NOT NULL,
	`culture` VARCHAR(7)  NOT NULL,
	`reminder` INTEGER default null,
	`subject` TEXT,
	`text` TEXT,
	`grouped_text` TEXT,
	`grouped` INTEGER default 0,
	PRIMARY KEY (`id`,`notification_id`,`culture`),
	INDEX `message_template_FI_1` (`notification_id`),
	CONSTRAINT `message_template_FK_1`
		FOREIGN KEY (`notification_id`)
		REFERENCES `notification` (`id`)
		ON UPDATE RESTRICT
		ON DELETE CASCADE
);


CREATE TABLE `grouped_messages`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`to` VARCHAR(256),
	`content` TEXT,
	`notification_id` INTEGER default 0,
	PRIMARY KEY (`id`,`notification_id`),
	INDEX `grouped_messages_FI_1` (`notification_id`),
	CONSTRAINT `grouped_messages_FK_1`
		FOREIGN KEY (`notification_id`)
		REFERENCES `notification` (`id`)
		ON UPDATE RESTRICT
		ON DELETE CASCADE
);

CREATE TABLE `message_recipient`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`message_template_id` INTEGER  NOT NULL,
	`type` VARCHAR(256),
	`email` VARCHAR(256),
	PRIMARY KEY (`id`,`message_template_id`),
	INDEX `message_recipient_FI_1` (`message_template_id`),
	CONSTRAINT `message_recipient_FK_1`
		FOREIGN KEY (`message_template_id`)
		REFERENCES `message_template` (`id`)
		ON UPDATE RESTRICT
		ON DELETE CASCADE
);