drop database if exists pmacct;
create database pmacct;

USE pmacct;

drop table if exists acct;
	create table acct (
		ip_src CHAR(39) NOT NULL,
		ip_dst CHAR(39) NOT NULL,
		as_src INT(4) UNSIGNED NOT NULL,
		as_dst INT(4) UNSIGNED NOT NULL,
		port_src INT(2) UNSIGNED NOT NULL,
		port_dst INT(2) UNSIGNED NOT NULL,
		packets INT UNSIGNED NOT NULL,
		bytes INT UNSIGNED NOT NULL,
		pkt_len_distrib CHAR(10) NOT NULL,
		stamp_inserted DATETIME NOT NULL,
		stamp_updated DATETIME,
		stamp_processed DATETIME,
		PRIMARY KEY (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, pkt_len_distrib, stamp_inserted)
);

DROP TABLE IF EXISTS clean_data;

CREATE TABLE clean_data (
	ipb_src CHAR(39) NOT NULL,
	ipb_dst CHAR(39) NOT NULL,
	as_src INT(4) UNSIGNED NOT NULL,
	as_dst INT(4) UNSIGNED NOT NULL,
	port_src INT(2) UNSIGNED NOT NULL,
	port_dst INT(2) UNSIGNED NOT NULL,
	occurences INT UNSIGNED NOT NULL,
	volume CHAR(10) NOT NULL,
	time_added DATETIME NOT NULL,
	time_privatized DATETIME,
	UNIQUE idx (ipb_src, ipb_dst, as_src, as_dst, port_src, port_dst, volume, time_added)
);

grant all privileges on pmacct.* to flowcleaner@localhost identified by 'nil' with grant option;
grant all privileges on pmacct.* to pmacct@localhost identified by 'arealsmartpwd' with grant option;