PRAGMA foreign_keys = ON;

-- object: instances | type: TABLE --
-- DROP TABLE IF EXISTS instances;
CREATE TABLE IF NOT EXISTS instances (
	id 		integer NOT NULL,
	instname	text UNIQUE,
	insttype	text,
	PRIMARY KEY ("id")
);

insert into instances (id,instname)
	values (0,'Unknown');

-- object: "Webhogs" | type: TABLE --
-- DROP TABLE IF EXISTS "webhogs" ;
CREATE TABLE IF NOT EXISTS "webhogs" (
	IP		text NOT NULL,
	type		text,
	affairs		integer,
	origin		text,
	ruleset		text,
	rule		text,
	class		text,
	web_class	text,
	method		text,
	severity	text,
	state		text,
	date_intro	date,
	date_event	date,
	instance	integer Default 0,
	comment		text,
	FOREIGN KEY (instance) REFERENCES instances (id)
		on update	cascade
		on delete	cascade,
	PRIMARY KEY ("IP")
);

-- object: affairs_by_ip | type: TABLE --
DROP TABLE IF EXISTS affairs_by_ip ;
-- 'ip' cannot be primary key here
-- So a running 'id' is used 
CREATE TABLE "affairs_by_ip" (
	id		integer,
	ip_addr		text,
	affair_num	integer,
	rule		text,
	date_event	date,
	time_useconds	integer,
	instance	integer,
	FOREIGN KEY (instance) REFERENCES instances (id),
	PRIMARY KEY ("id")
);
