#!/bin/sh -x

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

createdb -U $DBUSER $DBNAME
psql -U $DBUSER $DBNAME << EOF

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

CREATE TABLE log (
  datetime timestamp,
  level float,
  virname varchar(80),
  status varchar(20),
  qname varchar(80),
  sender text,
  recipient text,
  size int,
  ip varchar(40)
);

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

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(255) DEFAULT '' NOT NULL,
  recipient varchar(255) 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 )
);

#CREATE INDEX greylist_idx ON greylist (ip,sender,recipient);
#DROP INDEX greylist_idx;

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