#!/bin/sh -x

# Deleting old records may be done with:
# DELETE FROM log WHERE datetime<SUBDATE(now(),INTERVAL 90 DAY);

DBUSER=${DBUSER:=root}
DBNAME=${DBNAME:=sagator}
ARGS=${ARGS:="-p"}
NOW="`date +%s`"

mysql -u $DBUSER $ARGS << EOF
CREATE DATABASE $DBNAME;
USE $DBNAME;
SET NAMES utf8;

# update sagator's password and uncomment this line:
#GRANT INSERT,SELECT,UPDATE,DELETE ON sagator.* TO 'sagator'@'127.0.0.1' IDENTIFIED BY 'your_secret';

CREATE TABLE webaccess (
  email varchar(40) NOT NULL,
  pass varchar(256) NOT NULL,
  perms varchar(20) default '' NOT NULL,
  datetime datetime default NULL,
  lang varchar(20) default '' NOT NULL,
  showrows int(11) default 50 NOT NULL
);

CREATE TABLE log (
  id int unsigned NOT NULL auto_increment,
  datetime timestamp,
  level float,
  virname varchar(80),
  status varchar(20),
  qname varchar(80),
  sender text,
  recipient text,
  size int,
  ip varchar(40),
  PRIMARY KEY (id)
);

CREATE INDEX log_datetime ON log (datetime);
CREATE INDEX log_sender ON log (sender(80));
CREATE INDEX log_recipient ON log (recipient(80));

CREATE TABLE signatures (
  prio int,
  email text,
  ckey varchar(16)
);

CREATE TABLE policy_quota (
  timestamp bigint NOT NULL,
  username varchar(128) NOT NULL,
  recipient_count int DEFAULT 1 NOT NULL,
  ip varchar(39),
  PRIMARY KEY ( timestamp, username )
) ENGINE=MyISAM;

CREATE TABLE greylist (
  flags varchar(2),
  timestamp int DEFAULT $NOW NOT NULL,
  expire int DEFAULT -1 NOT NULL,
  ip varchar(39) DEFAULT '' NOT NULL,
  sender varchar(128) DEFAULT '' NOT NULL,
  recipient varchar(128) DEFAULT '' NOT NULL,
  repeats int DEFAULT 0 NOT NULL,
  block_count int DEFAULT 0 NOT NULL,
  pass_count int DEFAULT 0 NOT NULL,
  created int DEFAULT $NOW NOT NULL,
  last_update int DEFAULT $NOW NOT NULL,
  PRIMARY KEY ( flags, ip, sender, recipient )
) ENGINE=MyISAM;

# UPGRADE script
#DROP INDEX greylist_idx ON greylist;
#ALTER TABLE greylist CONVERT TO CHARACTER SET utf8;
#ALTER TABLE greylist MODIFY ip varchar(39) DEFAULT '' NOT NULL;

INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','127.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','10.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','172.16.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','172.17.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','172.18.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','172.19.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','172.20.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','172.21.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','172.22.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','172.23.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','172.24.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','172.25.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','172.26.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','172.27.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','172.28.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','172.29.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','172.30.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','172.31.%',-1);
INSERT INTO greylist (flags,ip,last_update) VALUES ('WA','192.168.%',-1);

EOF