CREATE TABLE host (
	host_no serial,
	name varchar(255) not null,
	ip varchar(32) not null,
	info varchar(255) default '',
	online int default 0,
	primary key (host_no)
) ;
CREATE UNIQUE INDEX host_idx ON host USING BTREE (name, ip) ;

CREATE TABLE hostgroup (
	hostgroup_no serial,
	name varchar(255) not null,
	info varchar(255) default '',
	primary key (hostgroup_no)
) ;
CREATE UNIQUE INDEX hostgroup_idx ON hostgroup USING BTREE (name) ;

CREATE TABLE hostgroup_member (
	host_no bigint REFERENCES host (host_no) ON DELETE CASCADE,
	hostgroup_no bigint REFERENCES hostgroup (hostgroup_no) ON DELETE CASCADE,
	primary key(host_no, hostgroup_no)
) ;

INSERT INTO hostgroup (name) VALUES ('all') ; 

INSERT INTO host (name, ip) VALUES ('plume', '127.0.0.1') ;
INSERT INTO host (name, ip) VALUES ('plume2', '127.0.0.1') ;
INSERT INTO host (name, ip) VALUES ('master', '127.0.0.1') ;
INSERT INTO hostgroup_member (host_no, hostgroup_no) VALUES(1,1) ;
INSERT INTO hostgroup_member (host_no, hostgroup_no) VALUES(2,1) ;
INSERT INTO hostgroup_member (host_no, hostgroup_no) VALUES(3,1) ;


-- il faut ajouter un trigger quand on ajoute un host

-- 
--  SELECT count(*) 
--  FROM hostgroup_member 
--   INNER JOIN host on hostgroup_member.host_no = host.host_no
--   INNER JOIN hostgroup on hostgroup.hostgroup_no = hostgroup_member.hostgroup_no 
--  WHERE host.name = 'plume' and hostgroup.name = 'grp_prod';
-- 
-- 
-- 

CREATE TABLE job (
 	job_no serial,
	id varchar(255) not null,
	md5 varchar(127) not null,
	host text not null,
	date timestamp,
	comment text,
	xml text not null,
	primary key(id, md5)
) ; 

CREATE UNIQUE INDEX job_no_idx ON job USING BTREE (job_no) ;
CREATE UNIQUE INDEX job_md5_idx ON job USING BTREE (md5) ;

CREATE TABLE task (
	job_no bigint REFERENCES job (job_no) ON DELETE CASCADE,
	id varchar(255) not null,
	host varchar(255) not null,
	xml text not null,
	primary key (job_no, id)
) ;


-- que veut on en sortir...
CREATE TABLE task_hist (
	task_sid varchar(255), 
	job_no bigint  REFERENCES job (job_no) ON DELETE CASCADE,
	date timestamp,
	host varchar(255),
  	xml text,	
  	PRIMARY KEY  (task_sid)
) ;

CREATE TABLE task_notify (
 task_sid varchar(255) REFERENCES task_hist (task_sid) ON DELETE CASCADE,
 host varchar(255),
 date timestamp,
 primary key(task_sid, host)
) ; 

-- que veut on en sortir...
-- on supprime pas l historique
CREATE TABLE job_hist (
	job_sid varchar(255),
	job_no bigint REFERENCES job (job_no) ON DELETE CASCADE,
	start_date timestamp,
	end_date timestamp,
	host varchar(255),
  	xml text ,
	status int,
  	PRIMARY KEY  (job_sid)
) ;

CREATE INDEX job_hist_start_date_idx ON job_hist USING BTREE (start_date) ;
CREATE INDEX job_hist_end_date_idx ON job_hist USING BTREE (end_date) ;


-- INSERT INTO task_hist ( task_sid, job_no, date, host) VALUES ('toto', 40, now(), 'plume2') ;