#
# Soft Fail, more info see README, COPYRIGHT and CHANGELOG files
#
# MySQL database scheme
#

DROP DATABASE IF EXISTS softfail;
CREATE DATABASE softfail;
GRANT all ON softfail.* to softfail@"localhost" identified by 'softfail40';

USE softfail;

create table softfail
(
        id              bigint          NOT NULL        auto_increment,
        relay_ip        char(16),                                       
        mail_from       varchar(255),                                  
        rcpt_to         varchar(255),                                   
        block_expires   datetime        NOT NULL,                       
        record_expires  datetime        NOT NULL,                       
        
        blocked_count   bigint          default 0 NOT NULL,             
        passed_count    bigint          default 0 NOT NULL,             
        aborted_count   bigint          default 0 NOT NULL,             
        origin_type     enum("MANUAL","AUTO") NOT NULL,                 
        create_time     datetime        NOT NULL,                       
        last_update     timestamp       NOT NULL,                       
        earlyseen_cnt   bigint          default 0 NOT NULL,             

        primary key(id),
        key(relay_ip),
        key(mail_from(20)),                                             
        key(rcpt_to(20))
);

# Whitelist por redes (exemplos apenas) / Network whitelisting (example only)
#insert into softfail values (0,"127.0.0.1"   ,NULL,NULL,"0000-00-00 00:00:00","9999-12-31 23:59:59",0,0,0,"MANUAL",NOW(),NOW(),0);
#insert into softfail values (0,"192.168"     ,NULL,NULL,"0000-00-00 00:00:00","9999-12-31 23:59:59",0,0,0,"MANUAL",NOW(),NOW(),0);

# Whitelist por dominio destinatario (exemplo) / Recipient domain whitelisting (example only)
#insert into softfail values (0,NULL,NULL,"sub.domain.com","0000-00-00 00:00:00","9999-12-31 23:59:59",0,0,0,"MANUAL",NOW(),NOW(),0);

# Whitelist por dominio remetente (exemplo) / Sender domain whitelisting (example only) - AVOID IT because SENDER can be easily FORGED
#insert into softfail values (0,NULL,"freebsdbrasil.com.br",NULL,"0000-00-00 00:00:00","9999-12-31 23:59:59",0,0,0,"MANUAL",NOW(),NOW(),0); 

# Blacklist por IP (exemplo) / IP address blacklisting (example only)
#insert into softfail values (0,"10.69.69.250",NULL,NULL,"9999-12-31 23:59:59","9999-12-31 23:59:59",0,0,0,"MANUAL",NOW(),NOW(),0);
#insert into softfail values (0,"201",NULL,NULL,"9999-12-31 23:59:59","9999-12-31 23:59:59",0,0,0,"MANUAL",NOW(),NOW(),0);