#
# Copyright (c) 2004-2006, NFG Net Facilities Group BV, support@nfg.nl
# Copyright (c) 2006 Aaron Stone, aaron@serendipity.cx
#
# 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.mysql 2207 2006-07-24 15:35:35Z paul $

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)	
) ENGINE=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',
	maxsieve_size BIGINT DEFAULT '0' NOT NULL,
	cursieve_size BIGINT DEFAULT '0' NOT NULL,
	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)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS dbmail_usermap;
CREATE TABLE dbmail_usermap (
  login VARCHAR(100) NOT NULL,
  sock_allow varchar(100) NOT NULL,
  sock_deny varchar(100) NOT NULL,
  userid varchar(100) NOT NULL
) ENGINE=InnoDB;
CREATE UNIQUE INDEX usermap_idx_1 ON dbmail_usermap(login, sock_allow, userid);

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) 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
) ENGINE=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
) ENGINE=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
) ENGINE=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)
) ENGINE=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),
	INDEX mailbox_status (mailbox_idnr, 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
) ENGINE=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
) ENGINE=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
) ENGINE=InnoDB;

DROP TABLE IF EXISTS dbmail_auto_replies;
CREATE TABLE dbmail_auto_replies (
   	user_idnr bigint(21) DEFAULT '0' NOT NULL,
	start_date datetime not null,
	stop_date datetime 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
) ENGINE=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)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS dbmail_sievescripts;
CREATE TABLE dbmail_sievescripts (
  owner_idnr bigint(21) DEFAULT '0' NOT NULL,
  name varchar(100) NOT NULL,
  script text,
  active tinyint(1) default '0' not null,
  INDEX (name),
  INDEX (owner_idnr),
  INDEX (owner_idnr, name),
  FOREIGN KEY owner_idnr_fk (owner_idnr)
    REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;


#
# store all headers by storing all headernames and headervalues in separate
# tables.
#

DROP TABLE IF EXISTS dbmail_headername;
CREATE TABLE dbmail_headername (
	id		BIGINT NOT NULL AUTO_INCREMENT,
	headername	VARCHAR(100) NOT NULL DEFAULT '',
	PRIMARY KEY (id),
	UNIQUE (headername)
) ENGINE=InnoDB ;

DROP TABLE IF EXISTS dbmail_headervalue;
CREATE TABLE dbmail_headervalue (
	headername_id	BIGINT NOT NULL,
        physmessage_id	BIGINT NOT NULL,
	id		BIGINT NOT NULL AUTO_INCREMENT,
	headervalue	TEXT NOT NULL,
	PRIMARY KEY (id),
	UNIQUE (physmessage_id, id),
	UNIQUE (physmessage_id, headername_id, headervalue(255)),
	INDEX (headername_id),
	FOREIGN KEY (headername_id)
		REFERENCES dbmail_headername(id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (physmessage_id)
		REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE
	
		
) ENGINE=InnoDB ;

# Provide separate storage of commonly used headers

# These fields will typically be preparsed as specified by 
# http://www.ietf.org/internet-drafts/draft-ietf-imapext-sort-17.txt

# Threading

# support fast threading by breaking out In-Reply-To/References headers
# these fields contain zero or more Message-Id values that determine the message
# threading

DROP TABLE IF EXISTS dbmail_subjectfield;
CREATE TABLE dbmail_subjectfield (
        physmessage_id  BIGINT NOT NULL,
	id		BIGINT NOT NULL AUTO_INCREMENT,
	subjectfield	VARCHAR(255) NOT NULL DEFAULT '',
	PRIMARY KEY (id),
	UNIQUE (physmessage_id, subjectfield),
	FOREIGN KEY (physmessage_id)
			REFERENCES dbmail_physmessage(id)
			ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB ;

DROP TABLE IF EXISTS dbmail_datefield;
CREATE TABLE dbmail_datefield (
        physmessage_id  BIGINT NOT NULL,
	id		BIGINT NOT NULL AUTO_INCREMENT,
	datefield	DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
	PRIMARY KEY (id),
	UNIQUE (physmessage_id, datefield),
	FOREIGN KEY (physmessage_id)
			REFERENCES dbmail_physmessage(id)
			ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB ;

DROP TABLE IF EXISTS dbmail_referencesfield;
CREATE TABLE dbmail_referencesfield (
        physmessage_id  BIGINT NOT NULL,
	id		BIGINT NOT NULL AUTO_INCREMENT,
	referencesfield	VARCHAR(255) NOT NULL DEFAULT '',
	PRIMARY KEY (id),
	UNIQUE (physmessage_id, referencesfield),
	FOREIGN KEY (physmessage_id)
			REFERENCES dbmail_physmessage(id)
			ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB ;

# Searching and Sorting

# support fast sorting by breaking out and preparsing the fields most commonly used
# in searching and sorting: Subject, From, To, Reply-To, Cc. 

DROP TABLE IF EXISTS dbmail_fromfield;
CREATE TABLE dbmail_fromfield (
        physmessage_id  BIGINT NOT NULL,
	id		BIGINT NOT NULL AUTO_INCREMENT,
	fromname	VARCHAR(100) NOT NULL DEFAULT '',
	fromaddr	VARCHAR(100) NOT NULL DEFAULT '',
	PRIMARY KEY (id),
	UNIQUE (physmessage_id, id),
	FOREIGN KEY (physmessage_id)
			REFERENCES dbmail_physmessage(id)
			ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB ;

DROP TABLE IF EXISTS dbmail_tofield;
CREATE TABLE dbmail_tofield (
        physmessage_id  BIGINT NOT NULL,
	id		BIGINT NOT NULL AUTO_INCREMENT,
	toname		VARCHAR(100) NOT NULL DEFAULT '',
	toaddr		VARCHAR(100) NOT NULL DEFAULT '',
	PRIMARY KEY (id),
	UNIQUE (physmessage_id, id),
	FOREIGN KEY (physmessage_id)
			REFERENCES dbmail_physmessage(id)
			ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB ;

DROP TABLE IF EXISTS dbmail_replytofield;
CREATE TABLE dbmail_replytofield (
        physmessage_id  BIGINT NOT NULL,
	id		BIGINT NOT NULL AUTO_INCREMENT,
	replytoname	VARCHAR(100) NOT NULL DEFAULT '',
	replytoaddr	VARCHAR(100) NOT NULL DEFAULT '',
	PRIMARY KEY (id),
	UNIQUE (physmessage_id, id),
	FOREIGN KEY (physmessage_id)
			REFERENCES dbmail_physmessage(id)
			ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB ;

DROP TABLE IF EXISTS dbmail_ccfield;
CREATE TABLE dbmail_ccfield (
        physmessage_id  BIGINT NOT NULL,
	id		BIGINT NOT NULL AUTO_INCREMENT,
	ccname		VARCHAR(100) NOT NULL DEFAULT '',
	ccaddr		VARCHAR(100) NOT NULL DEFAULT '',
	PRIMARY KEY (id),
	UNIQUE (physmessage_id, id),
	FOREIGN KEY (physmessage_id)
			REFERENCES dbmail_physmessage(id)
			ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB ;

COMMIT;

-- Table structure for table `dbmail_replycache`

DROP TABLE IF EXISTS dbmail_replycache;
CREATE TABLE dbmail_replycache (
  to_addr varchar(100) NOT NULL default '',
  from_addr varchar(100) NOT NULL default '',
  handle varchar(100) NOT NULL default '',
  lastseen datetime NOT NULL default '0000-00-00 00:00:00',
  UNIQUE KEY replycache_1 (to_addr,from_addr, handle)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS dbmail_usermap;
CREATE TABLE dbmail_usermap (
  login VARCHAR(100) NOT NULL,
  sock_allow varchar(100) NOT NULL,
  sock_deny varchar(100) NOT NULL,
  userid varchar(100) NOT NULL
) ENGINE=InnoDB;

CREATE UNIQUE INDEX usermap_idx_1 ON dbmail_usermap(login, sock_allow, userid);


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