CREATE DATABASE `system`;

USE `system`;

CREATE TABLE `alias` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(128) NOT NULL COMMENT 'local-part of email-address',
  `domain` varchar(64) NOT NULL COMMENT 'domain-part of email-address',
  `sendto` varchar(255) NOT NULL COMMENT 'address (or more comma-seperated) to forward mail to',
  `internal` set('YES','NO') NOT NULL default 'NO' COMMENT 'YES = for authenticated senders only',
  `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `username` (`username`),
  KEY `domain` (`domain`),
  KEY `internal` (`internal`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='alias definitions';

CREATE TABLE `catchall` (
  `id` int(11) NOT NULL auto_increment,
  `domain` varchar(64) NOT NULL COMMENT 'domain-part of email-address',
  `sendto` varchar(255) NOT NULL COMMENT 'address (or more comma-seperated) to forward mail to',
  `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `domain` (`domain`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='catchall definitions';

CREATE TABLE `user` (
  `username` varchar(64) character set utf8 collate utf8_bin NOT NULL COMMENT 'localpart of email-address',
  `domain` varchar(64) character set utf8 collate utf8_bin NOT NULL COMMENT 'domain-part of email-address',
  `password` varchar(28) character set utf8 collate utf8_bin NOT NULL COMMENT 'base64-encoded SHA1 hash of password',
  `SMTP_allowed` enum('NO','YES') NOT NULL default 'YES' COMMENT 'user may receive mail (has a mailbox)',
  `SMTPAUTH_allowed` enum('NO','YES') NOT NULL default 'YES' COMMENT 'user may send mails from anywhere (localhost always without auth)',
  `IMAP_allowed` enum('NO','YES') NOT NULL default 'YES' COMMENT 'user may fetch mail through pop3/imap',
  `spam_threshold` double NOT NULL default '5' COMMENT 'tag as spam, if above',
  `spam_tag` varchar(64) NOT NULL default '{SPAM?} ' COMMENT 'string to prepend to subject, if spam',
  `Full Name` varchar(255) default NULL COMMENT 'the user''s full name',
  `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`username`,`domain`),
  KEY `SMTP_allowed` (`SMTP_allowed`),
  KEY `SMTPAUTH_allowed` (`SMTPAUTH_allowed`),
  KEY `IMAP_allowed` (`IMAP_allowed`),
  KEY `spam_threshold` (`spam_threshold`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='user preferences';

CREATE TABLE `greylist` (
  `id` int(11) NOT NULL auto_increment COMMENT 'just a primary key',
  `SenderIP` varchar(15) NOT NULL COMMENT 'IP of Sender',
  `SenderAddress` varchar(1024) NOT NULL COMMENT 'email-address of Sender',
  `first_seen` int(11) NOT NULL COMMENT 'UNIX TimeStamp of first attempt',
  PRIMARY KEY  (`id`),
  KEY `SenderIP` (`SenderIP`),
  KEY `SenderAddress` (`SenderAddress`(333))
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='GreyListing';