-- use innodb database
-- this is just a rough draft of how to set the tables up.
-- I'm sure it could be impreved a little.
-- 
create database logwatch;
use logwatch;
--

#DROP TABLE IF EXISTS header;
CREATE TABLE header 
(
       source_ip       VARCHAR(20) NOT NULL,
       PRIMARY KEY     (source_ip),
       ip_name         VARCHAR(50) NOT NULL,
       note            VARCHAR(40) NOT NULL,
       host            VARCHAR(20) NOT NULL,
       output_level    TINYINT UNSIGNED,
       city            VARCHAR(20),
       state           CHAR(2),
       cc              CHAR(4),
       country         VARCHAR(20),
       address1        VARCHAR(50),
       address2        VARCHAR(50),
       mnt_by	       VARCHAR(120),
       t_modified      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = InnoDB;

CREATE TABLE ports 
(
       source_ip       VARCHAR(20) NOT NULL,
       protocol        CHAR(4) NOT NULL,
       packet_count    INT UNSIGNED NOT NULL,
       port            INT UNSIGNED NOT NULL,
       process_date    DATE NULL DEFAULT '0000-00-00',
       FOREIGN KEY     (source_ip) REFERENCES header(source_ip)
               ON DELETE CASCADE
               ON UPDATE CASCADE
) ENGINE = InnoDB;    
-- this is just a dump or /etc/services into the database
CREATE TABLE services
(
       port_protocol    VARCHAR(20) NOT NULL,
       PRIMARY KEY     (port_protocol),
       name    		VARCHAR(20) NOT NULL,
       port             SMALLINT UNSIGNED NOT NULL,
       protocol         CHAR(4) NOT NULL,
       open_freq        DOUBLE(7,6) UNSIGNED,
       aliases    	VARCHAR(40) NULL,
       note        	VARCHAR(40) NULL,
       t_modified       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = InnoDB;    

CREATE TABLE mod_evasive
(
       source_ip        VARCHAR(20) NOT NULL,
       ip_name          VARCHAR(50) NOT NULL,
       date             DATETIME NULL DEFAULT '0000-00-00 00-00-00',
       protocol         CHAR(4) NOT NULL,
       message          VARCHAR(20) NOT NULL
) ENGINE = InnoDB;
-- #         'agent' => 'Mozilla/5.0 (X11; Linux x86_64; rv:20.0) Gecko/20100101 Firefox/20.0',
-- #         'cmd' => 'POST',
-- #         'date' => '2013-05-18',
-- #         'gmt' => '-0500',
-- #         'length' => '-',
-- #         'protocol' => 'HTTP/1.1',
-- #         'rcode' => '302',
-- #         'source_ip' => '69.128.193.177',
-- #         'time' => '14:01:07',
-- #         'url' => 'https://www.apriorifarm.com/wp-login.php'
CREATE TABLE http_log
(
       agent            VARCHAR(80) NOT NULL,
       cmd              VARCHAR(4) NOT NULL,
       date             DATETIME NULL DEFAULT '0000-00-00 00-00-00',
       gmt              CHAR(5) NOT NULL,
       length           VARCHAR(20) NOT NULL,
       request          VARCHAR(50) NOT NULL,
       protocol         VARCHAR(10) NOT NULL,
       rcode            SMALLINT UNSIGNED NOT NULL,
       rmessage         VARCHAR(40) NOT NULL,
       source_ip        VARCHAR(20) NOT NULL,
       ip_name          VARCHAR(50) NOT NULL,
       url              VARCHAR(80) NOT NULL
) ENGINE = InnoDB;