# 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.
# $Id: create_tables.mysql 1890 2005-10-01 12:48:02Z paul $

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

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

DROP TABLE IF EXISTS dbmail_mailboxes;
CREATE TABLE dbmail_mailboxes (
   mailbox_idnr bigint(21) NOT NULL auto_increment,
   owner_idnr bigint(21) DEFAULT '0' NOT NULL,
   name varchar(100) BINARY NOT NULL,
   seen_flag tinyint(1) default '0' not null,
   answered_flag tinyint(1) default '0' not null,
   deleted_flag tinyint(1) default '0' not null,
   flagged_flag tinyint(1) default '0' not null,
   recent_flag tinyint(1) default '0' not null,
   draft_flag tinyint(1) default '0' not null,
   no_inferiors tinyint(1) default '0' not null,
   no_select tinyint(1) default '0' not null,
   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)
 );

DROP TABLE IF EXISTS dbmail_subscription;
CREATE TABLE dbmail_subscription (
	user_id bigint(21) NOT NULL,
	mailbox_id bigint(21) NOT NULL,
	PRIMARY KEY (user_id, mailbox_id)
);

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)
);
	
DROP TABLE IF EXISTS dbmail_physmessage;
CREATE TABLE dbmail_physmessage (
   id bigint(21) NOT NULL auto_increment,
   messagesize bigint(21) DEFAULT '0' NOT NULL,
   rfcsize bigint(21) DEFAULT '0' NOT NULL,
   internal_date datetime default '0' not null,
   PRIMARY KEY(id)
);
 
DROP TABLE IF EXISTS dbmail_messages;
CREATE TABLE dbmail_messages (
   message_idnr bigint(21) NOT NULL auto_increment,
   mailbox_idnr bigint(21) DEFAULT '0' NOT NULL,
   physmessage_id bigint(21) DEFAULT '0' NOT NULL,
   seen_flag tinyint(1) default '0' not null,
   answered_flag tinyint(1) default '0' not null,
   deleted_flag tinyint(1) default '0' not null,
   flagged_flag tinyint(1) default '0' not null,
   recent_flag tinyint(1) default '0' not null,
   draft_flag tinyint(1) default '0' not null,
   unique_id varchar(70) NOT NULL,
   status tinyint(3) unsigned default '0' not null,
   PRIMARY KEY (message_idnr),
   INDEX mailbox_idnr_index (mailbox_idnr),
   INDEX physmessage_id_index (physmessage_id),
   INDEX seen_flag_index (seen_flag),
   INDEX unique_id_index (unique_id),
   INDEX status_index (status)
);

DROP TABLE IF EXISTS dbmail_messageblks;
CREATE TABLE dbmail_messageblks (
   messageblk_idnr bigint(21) NOT NULL auto_increment,
   physmessage_id bigint(21) DEFAULT '0' NOT NULL,
   messageblk longblob NOT NULL,
   blocksize bigint(21) DEFAULT '0' NOT NULL,
   is_header tinyint(1) DEFAULT '0' NOT NULL,
   PRIMARY KEY (messageblk_idnr),
   INDEX physmessage_id_index (physmessage_id),
   INDEX physmessage_id_is_header_index (physmessage_id, is_header)
);

DROP TABLE IF EXISTS dbmail_auto_notifications;
CREATE TABLE dbmail_auto_notifications (
   user_idnr bigint(21) DEFAULT '0' NOT NULL,
   notify_address VARCHAR(100),
   PRIMARY KEY(user_idnr)
);

DROP TABLE IF EXISTS dbmail_auto_replies;
CREATE TABLE dbmail_auto_replies (
   user_idnr bigint(21) DEFAULT '0' NOT NULL,
   reply_body mediumtext,
   PRIMARY KEY(user_idnr)
);

DROP TABLE IF EXISTS dbmail_pbsp;
CREATE TABLE dbmail_pbsp (
   idnr bigint(21) NOT NULL auto_increment,
   since datetime default '0' not null,
   ipnumber varchar(40) NOT NULL,
   PRIMARY KEY (idnr),
   UNIQUE INDEX ipnumber_index (ipnumber),
   INDEX since_index (since)
);

# 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');