# Copyright (C) 1999-2004 IC & S  dbmail@ic-s.nl
#
# This program is free software; you can redistribute it and/or 
# modify it under the terms of the GNU General Public License 
# as published by the Free Software Foundation; either 
# version 2 of the License, or (at your option) any later 
# version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
# 

# schema for innodb tables with added indexes and foreign keys
# for more speed. 
# This file was originally created by Paul Stevens and added
# to dbmail on August 27th, 2003.
# 
# $Id: create_tables_innoDB.mysql 2190 2006-06-30 12:54:46Z paul $

# if database needs to be created, uncomment next line
SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS dbmail_aliases;
CREATE TABLE dbmail_aliases (
	alias_idnr bigint(21) NOT NULL auto_increment,
	alias varchar(100) NOT NULL default '',
	deliver_to varchar(250) NOT NULL default '',
	client_idnr bigint(21) NOT NULL default '0',
	PRIMARY KEY (alias_idnr),
	INDEX alias_index (alias),
	INDEX client_idnr_index (client_idnr)	
) TYPE=InnoDB;

DROP TABLE IF EXISTS dbmail_users;
CREATE TABLE dbmail_users (
	user_idnr bigint(21) NOT NULL auto_increment,
	userid varchar(100) NOT NULL default '',
	passwd varchar(34) NOT NULL default '',
	client_idnr bigint(21) NOT NULL default '0',
	maxmail_size bigint(21) NOT NULL default '0',
	curmail_size bigint(21) NOT NULL default '0',
	encryption_type varchar(20) NOT NULL default '',
	last_login datetime NOT NULL default '1979-11-03 22:05:58',
	PRIMARY KEY  (user_idnr),
	UNIQUE INDEX userid_index (userid)
) TYPE=InnoDB;

DROP TABLE IF EXISTS dbmail_mailboxes;
CREATE TABLE dbmail_mailboxes (
	mailbox_idnr bigint(21) NOT NULL auto_increment,
	owner_idnr bigint(21) NOT NULL default '0',
	name varchar(100) BINARY NOT NULL default '',
	seen_flag tinyint(1) NOT NULL default '0',
	answered_flag tinyint(1) NOT NULL default '0',
	deleted_flag tinyint(1) NOT NULL default '0',
	flagged_flag tinyint(1) NOT NULL default '0',
	recent_flag tinyint(1) NOT NULL default '0',
	draft_flag tinyint(1) NOT NULL default '0',
	no_inferiors tinyint(1) NOT NULL default '0',
	no_select tinyint(1) NOT NULL default '0',
	permission tinyint(1) default '2',
	PRIMARY KEY  (mailbox_idnr),
	INDEX name_index (name),
	INDEX owner_idnr_index (owner_idnr),
	UNIQUE INDEX owner_idnr_name_index (owner_idnr, name),
	FOREIGN KEY owner_idnr_fk (owner_idnr) 
		REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;

DROP TABLE IF EXISTS dbmail_subscription;
CREATE TABLE dbmail_subscription (
	user_id bigint(21) not null default '0',
	mailbox_id bigint(21) not null,
	primary key (user_id, mailbox_id),
	index user_id_index (user_id),
	index mailbox_id_index (mailbox_id),
	FOREIGN KEY user_id_fk (user_id) 
		REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY mailbox_id_fk (mailbox_id) 
		REFERENCES dbmail_mailboxes (mailbox_idnr) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;


DROP TABLE IF EXISTS dbmail_acl;
CREATE TABLE dbmail_acl (
	user_id bigint(21) NOT NULL,
	mailbox_id bigint(21) NOT NULL,
	lookup_flag tinyint(1) default '0' not null,
	read_flag tinyint(1) default '0' not null,
	seen_flag tinyint(1) default '0' not null,
	write_flag tinyint(1) default '0' not null,
	insert_flag tinyint(1) default '0' not null,	
	post_flag tinyint(1) default '0' not null,
	create_flag tinyint(1) default '0' not null,	
	delete_flag tinyint(1) default '0' not null,	
	administer_flag tinyint(1) default '0' not null,	
	PRIMARY KEY(user_id, mailbox_id),
	INDEX user_id_index (user_id),
	INDEX mailbox_id_index (mailbox_id),
	FOREIGN KEY user_id_fk (user_id) 
		REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE,	
	FOREIGN KEY mailbox_id_fk (mailbox_id) 
		REFERENCES dbmail_mailboxes (mailbox_idnr) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;

	
DROP TABLE IF EXISTS dbmail_physmessage;
CREATE TABLE dbmail_physmessage (
	id bigint(21) NOT NULL auto_increment,
	messagesize bigint(21) NOT NULL default '0',
	rfcsize bigint(21) NOT NULL default '0',
	internal_date datetime NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY (id)
) TYPE=InnoDB;

DROP TABLE IF EXISTS dbmail_messages;
CREATE TABLE dbmail_messages (
	message_idnr bigint(21) NOT NULL auto_increment,
	mailbox_idnr bigint(21) NOT NULL default '0',
	physmessage_id bigint(21) NOT NULL default '0',
	seen_flag tinyint(1) NOT NULL default '0',
	answered_flag tinyint(1) NOT NULL default '0',
	deleted_flag tinyint(1) NOT NULL default '0',
	flagged_flag tinyint(1) NOT NULL default '0',
	recent_flag tinyint(1) NOT NULL default '0',
	draft_flag tinyint(1) NOT NULL default '0',
	unique_id varchar(70) NOT NULL default '',
	status tinyint(3) unsigned NOT NULL default '0',
	PRIMARY KEY  (message_idnr),
	INDEX physmessage_id_index (physmessage_id),		
	INDEX mailbox_idnr_index (mailbox_idnr),
	INDEX seen_flag_index (seen_flag),
	INDEX unique_id_index (unique_id),
	INDEX status_index (status),
	FOREIGN KEY physmessage_id_fk (physmessage_id) 
		REFERENCES dbmail_physmessage (id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY mailbox_idnr_fk (mailbox_idnr) 
		REFERENCES dbmail_mailboxes (mailbox_idnr) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;

DROP TABLE IF EXISTS dbmail_messageblks;
CREATE TABLE dbmail_messageblks (
	messageblk_idnr bigint(21) NOT NULL auto_increment,
	physmessage_id bigint(21) NOT NULL default '0',
	messageblk longblob NOT NULL,
	blocksize bigint(21) NOT NULL default '0',
	is_header tinyint(1) NOT NULL default '0',
	PRIMARY KEY (messageblk_idnr),
	INDEX physmessage_id_index (physmessage_id),
	INDEX physmessage_id_is_header_index (physmessage_id, is_header),
	FOREIGN KEY physmessage_id_fk (physmessage_id) 
		REFERENCES dbmail_physmessage (id) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;

DROP TABLE IF EXISTS dbmail_auto_notifications;
CREATE TABLE dbmail_auto_notifications (
 	user_idnr bigint(21) NOT NULL,
	notify_address VARCHAR(100) NOT NULL,
	INDEX user_idnr_index (user_idnr),
	FOREIGN KEY user_idnr_fk (user_idnr) 
		REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;

DROP TABLE IF EXISTS dbmail_auto_replies;
CREATE TABLE dbmail_auto_replies (
   	user_idnr bigint(21) DEFAULT '0' NOT NULL,
   	reply_body mediumtext,
	INDEX user_idnr_index (user_idnr),
	FOREIGN KEY user_idnr_fk (user_idnr) 
		REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;

DROP TABLE IF EXISTS dbmail_pbsp;
CREATE TABLE dbmail_pbsp (
   idnr bigint(21) NOT NULL auto_increment,
   since datetime default '0000-00-00 00:00:00' not null,
   ipnumber varchar(40) NOT NULL,
   PRIMARY KEY (idnr),
   UNIQUE INDEX ipnumber_index (ipnumber),
   INDEX since_index (since)
) TYPE=InnoDB;

SET FOREIGN_KEY_CHECKS=1;

# create the user for the delivery chain
INSERT INTO dbmail_users (userid, passwd, encryption_type) 
	VALUES ('__@!internal_delivery_user!@__', '', 'md5');
# insert the 'anyone' user which is used for ACLs.
INSERT INTO dbmail_users (userid, passwd, encryption_type)
	VALUES ('anyone', '', 'md5');