# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

#-----------------------------------------------------------------------------
#-- role
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `role`;


CREATE TABLE `role`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255),
	`credentials` VARCHAR(255),
	PRIMARY KEY (`id`)
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- user
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `user`;


CREATE TABLE `user`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`parent_user_id` INTEGER default -1,
	`nickname` VARCHAR(50),
	`first_name` VARCHAR(100),
	`last_name` VARCHAR(100),
	`email` VARCHAR(255),
	`sha1_password` VARCHAR(40),
	`salt` VARCHAR(32),
	`role_id` INTEGER,
	`last_login` DATETIME,
	`created_at` DATETIME,
	PRIMARY KEY (`id`),
	INDEX `user_FI_1` (`parent_user_id`),
	CONSTRAINT `user_FK_1`
		FOREIGN KEY (`parent_user_id`)
		REFERENCES `user` (`id`)
		ON DELETE SET NULL,
	INDEX `user_FI_2` (`role_id`),
	CONSTRAINT `user_FK_2`
		FOREIGN KEY (`role_id`)
		REFERENCES `role` (`id`)
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- domain_permission
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `domain_permission`;


CREATE TABLE `domain_permission`
(
	`user_id` INTEGER,
	`domain_id` INTEGER,
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`),
	INDEX `domain_permission_FI_1` (`user_id`),
	CONSTRAINT `domain_permission_FK_1`
		FOREIGN KEY (`user_id`)
		REFERENCES `user` (`id`)
		ON DELETE CASCADE,
	INDEX `domain_permission_FI_2` (`domain_id`),
	CONSTRAINT `domain_permission_FK_2`
		FOREIGN KEY (`domain_id`)
		REFERENCES `domain` (`id`)
		ON DELETE CASCADE
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- domain
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `domain`;


CREATE TABLE `domain`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255),
	`creator_id` INTEGER,
	`mailbox_prefix` VARCHAR(255),
	`max_mailbox_count` INTEGER,
	`quota` INTEGER,
	`default_mailbox_quota` INTEGER,
	PRIMARY KEY (`id`),
	UNIQUE KEY `i2` (`name`),
	INDEX `domain_FI_1` (`creator_id`),
	CONSTRAINT `domain_FK_1`
		FOREIGN KEY (`creator_id`)
		REFERENCES `user` (`id`)
		ON DELETE SET NULL
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- mailbox
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `mailbox`;


CREATE TABLE `mailbox`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`domain_id` INTEGER,
	`name` VARCHAR(30),
	`password` VARCHAR(40),
	`max_quota` INTEGER,
	`max_address_count` INTEGER,
	`last_login` DATETIME,
	`active` INTEGER default 1,
	PRIMARY KEY (`id`),
	INDEX `mailbox_FI_1` (`domain_id`),
	CONSTRAINT `mailbox_FK_1`
		FOREIGN KEY (`domain_id`)
		REFERENCES `domain` (`id`)
		ON DELETE SET NULL
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- address
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `address`;


CREATE TABLE `address`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`mailbox_id` INTEGER,
	`localpart` VARCHAR(255),
	`domain_id` INTEGER,
	`alias` VARCHAR(255),
	`destination` TEXT,
	`active` INTEGER default 1,
	`save_in_mailbox` INTEGER default 1,
	PRIMARY KEY (`id`),
	INDEX `address_FI_1` (`mailbox_id`),
	CONSTRAINT `address_FK_1`
		FOREIGN KEY (`mailbox_id`)
		REFERENCES `mailbox` (`id`)
		ON DELETE CASCADE,
	INDEX `address_FI_2` (`domain_id`),
	CONSTRAINT `address_FK_2`
		FOREIGN KEY (`domain_id`)
		REFERENCES `domain` (`id`)
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- log
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `log`;


CREATE TABLE `log`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`user_id` INTEGER default null,
	`created_at` DATETIME,
	`message` TEXT,
	`host` VARCHAR(255),
	`priority` INTEGER,
	PRIMARY KEY (`id`),
	INDEX `log_FI_1` (`user_id`),
	CONSTRAINT `log_FK_1`
		FOREIGN KEY (`user_id`)
		REFERENCES `user` (`id`)
		ON DELETE SET NULL
)Type=MyISAM;

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;