CREATE DATABASE vmaildata DEFAULT CHARACTER SET latin1 COLLATE latin1_german1_ci;

USE vmaildata;

CREATE TABLE access (
  id          bigint(20)   unsigned NOT NULL AUTO_INCREMENT,
  source      varchar(128) NOT NULL,
  response    varchar(255) NOT NULL default 'DUNNO',
  type        enum('recipient','sender','client') NOT NULL default 'client',
  active      tinyint(1)   unsigned NOT NULL default '1',
  note        varchar(255) default '',
  PRIMARY KEY (id),
  INDEX source (type, source)
) TYPE=MyISAM;


CREATE TABLE domains (
  id          bigint(20)  unsigned NOT NULL AUTO_INCREMENT,
  domain      varchar(80) NOT NULL,
  transport   varchar(80) NOT NULL default 'maildrop:',
  active      tinyint(1)  unsigned NOT NULL default '1',
  PRIMARY KEY  (id),
  UNIQUE KEY domain (domain)
) TYPE=MyISAM;


CREATE TABLE forwardings (
  id          bigint(20)   unsigned NOT NULL AUTO_INCREMENT,
  source      varchar(128) NOT NULL,
  destination TEXT         NOT NULL,
  active      tinyint(1)   unsigned NOT NULL default '1',
  PRIMARY KEY (id),
  UNIQUE KEY source (source)
) TYPE=MyISAM;


CREATE TABLE maildropfilter (
  id          bigint(20)  unsigned NOT NULL AUTO_INCREMENT,
  ownerid     bigint(20)  unsigned NOT NULL default '0',
  position    tinyint(2)  unsigned NOT NULL default '50',
  datefrom    bigint(11)  unsigned NOT NULL default '0',
  dateend     bigint(11)  unsigned NOT NULL default '0',
  filtertype  enum('anymessage','startwith','contains','hasrecipient','mimemultipart','textplain','islargerthan') NOT NULL default 'anymessage',
  flags       tinyint(2)  unsigned NOT NULL default '0',
  fieldname   varchar(40) NOT NULL default '',
  fieldvalue  varchar(80) NOT NULL default '',
  tofolder    varchar(128) NOT NULL default '',
  body        TEXT,
  active      tinyint(1)  unsigned NOT NULL default '1',
  PRIMARY KEY (id),
  INDEX ownerid (ownerid, position),
  INDEX datefrom (datefrom),
  INDEX dateend (dateend)
) TYPE=MyISAM;

INSERT INTO domains (`domain`,`transport`,`active`)  VALUES ( 'virtual.test', 'maildrop:', 1 );

INSERT INTO forwardings (`source`,`destination`) VALUES ( 'abuse@virtual.test', 'user@virtual.test' );
INSERT INTO forwardings (`source`,`destination`) VALUES ( 'mailadmin@virtual.test', 'user@virtual.test' );
INSERT INTO forwardings (`source`,`destination`) VALUES ( 'postmaster@virtual.test', 'user@virtual.test' );

INSERT INTO `access` (`source`,`response`,`type`,`active`,`note`) VALUES ( 'abuse@','OK', 'recipient', 1, 'Dont do RBL checking for mail to abuse!' );
INSERT INTO `access` (`source`,`response`,`type`,`active`,`note`) VALUES ( 'postmaster@','OK', 'recipient', 0, 'Dont do RBL checking for mail to postmaster!' );
INSERT INTO `access` (`source`,`response`,`type`,`active`,`note`) VALUES ( 'freenet.de','reject_unverified_sender','sender',1,'check if freenet user exists');
INSERT INTO `access` (`source`,`response`,`type`,`active`,`note`) VALUES ( 'gmail.com','reject_unverified_sender','sender',1,'check if google-mailuser exists');
INSERT INTO `access` (`source`,`response`,`type`,`active`,`note`) VALUES ( 'gmx.de','reject_unverified_sender','sender',1,'check if gmx user exists');
INSERT INTO `access` (`source`,`response`,`type`,`active`,`note`) VALUES ( 'googlemail.com','reject_unverified_sender','sender',1,'check if google-mailuser exists');
INSERT INTO `access` (`source`,`response`,`type`,`active`,`note`) VALUES ( 'hotmail.com','reject_unverified_sender','sender',1,'check if hotmail-user exists');
INSERT INTO `access` (`source`,`response`,`type`,`active`,`note`) VALUES ( 'web.de','reject_unverified_sender','sender',1,'check if web user exists');
INSERT INTO `access` (`source`,`response`,`type`,`active`,`note`) VALUES ( 'yahoo.com','reject_unverified_sender','sender',1,'check if yahoo-user exists');
INSERT INTO `access` (`source`,`response`,`type`,`active`,`note`) VALUES ( 'mo-p00-ob.rzone.de','OK','client',1,'Strato Rechenzentrum');
INSERT INTO `access` (`source`,`response`,`type`,`active`,`note`) VALUES ( 'mo-p01-ob.rzone.de','OK','client',1,'Strato Rechenzentrum');
INSERT INTO `access` (`source`,`response`,`type`,`active`,`note`) VALUES ( 'mo-p02-ob.rzone.de','OK','client',1,'Strato Rechenzentrum');
INSERT INTO `access` (`source`,`response`,`type`,`active`,`note`) VALUES ( 'mo-p03-ob.rzone.de','OK','client',1,'Strato Rechenzentrum');
INSERT INTO `access` (`source`,`response`,`type`,`active`,`note`) VALUES ( 'mo-p04-ob.rzone.de','OK','client',1,'Strato Rechenzentrum');
INSERT INTO `access` (`source`,`response`,`type`,`active`,`note`) VALUES ( 'mo-p05-ob.rzone.de','OK','client',1,'Strato Rechenzentrum');
INSERT INTO `access` (`source`,`response`,`type`,`active`,`note`) VALUES ( 'mo-p06-ob.rzone.de','OK','client',1,'Strato Rechenzentrum');
INSERT INTO `access` (`source`,`response`,`type`,`active`,`note`) VALUES ( 'mo-p07-ob.rzone.de','OK','client',1,'Strato Rechenzentrum');

INSERT INTO maildropfilter (ownerid,position,datefrom,dateend,filtertype,flags,fieldname,fieldvalue,tofolder,body,active) VALUES (1,50,0,0,"anymessage",4,"","","!user2@virtual.test","Forward CC to: user2@virtual.test",0);
INSERT INTO maildropfilter (ownerid,position,datefrom,dateend,filtertype,flags,fieldname,fieldvalue,tofolder,body,active) VALUES (1,50,0,0,"startwith",0,"subject","[SPAM]","INBOX.Trash","Write spam to Trash",0);
INSERT INTO maildropfilter (ownerid,position,datefrom,dateend,filtertype,flags,fieldname,fieldvalue,tofolder,body,active) VALUES (1,75,0,0,"anymessage",0,"","","+days=4","Write the out of office message text here...",0);

COMMIT;
