CREATE TABLE automator (
 id serial,

 created timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Creation Date
--HELP_EN_LONG:The date and time this record was created.
--HELP_NL_INFO:Datum aanmaak
--HELP_NL_LONG:De tijd en datum waarop dit is aangemaakt.

 lastupdate timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Last Update
--HELP_EN_LONG:The date and time this record was updated.
--HELP_NL_INFO:Laatste wijziging
--HELP_NL_LONG:De tijd en datum van laatste wijziging.

 company integer NOT NULL default '1',
--HELP_EN_INFO:company
--HELP_EN_LONG:the company this record belongs to.
--HELP_NL_INFO:bedrijf
--HELP_NL_LONG:het bedrijf waar deze gegevens bij horen.

 name varchar(24) NOT NULL default '',
--HELP_EN_INFO:name
--HELP_EN_LONG:A short name for this automator
--HELP_NL_INFO:Naam
--HELP_NL_LONG:Een korte naam voor deze automator

 description text NOT NULL default '',
--HELP_EN_INFO:description
--HELP_EN_LONG:A full-text description of this automator, why does it exist
--HELP_NL_INFO:omschrijving
--HELP_NL_LONG:Een uitgebreide omschrijving van deze automator, wat is het doel

 status varchar(12) NOT NULL default 'active'
   CHECK (status IN ('active', 'deleted')),
--HELP_EN_INFO:status
--HELP_EN_LONG:The status of this record.
--HELP_NL_INFO:status
--HELP_NL_LONG:De status van dit record

 schedule integer NOT NULL default '1',
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

 switch integer NOT NULL default '1',
--HELP_EN_INFO:Switch
--HELP_EN_LONG:This switch will initiate the running of this automator
--HELP_NL_INFO:Schakelaar
--HELP_NL_LONG:De schakelaar die deze automator zal starten

 PRIMARY KEY  (id)
);

CREATE INDEX automator_company_index ON automator (company);
CREATE INDEX automator_switch_index ON automator (switch);

GRANT UPDATE, SELECT, INSERT, DELETE ON automator TO astium;

CREATE TABLE automatorscript (
 id serial,

 created timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Creation Date
--HELP_EN_LONG:The date and time this record was created.
--HELP_NL_INFO:Datum aanmaak
--HELP_NL_LONG:De tijd en datum waarop dit is aangemaakt.

 lastupdate timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Last Update
--HELP_EN_LONG:The date and time this record was updated.
--HELP_NL_INFO:Laatste wijziging
--HELP_NL_LONG:De tijd en datum van laatste wijziging.

 company integer NOT NULL default '1',
--HELP_EN_INFO:company
--HELP_EN_LONG:The company this record belongs to.
--HELP_NL_INFO:bedrijf
--HELP_NL_LONG:Het bedrijf waar record bij hoort.

 name varchar(32) NOT NULL default '',
--HELP_EN_INFO:notification role name
--HELP_EN_LONG:The name of this external site.
--HELP_NL_INFO:naam
--HELP_NL_LONG:De naam van deze externe site.

 description text NOT NULL default '',
--HELP_EN_INFO:Description
--HELP_EN_LONG:The description for this script
--HELP_NL_INFO:Omschrijving
--HELP_NL_LONG:Een omschrijving voor dit script

 script text NOT NULL default '',
--HELP_EN_INFO:Script
--HELP_EN_LONG:The program code of this script
--HELP_NL_INFO:Script
--HELP_NL_LONG:De programmacode van dit script

 PRIMARY KEY  (id)
);

GRANT UPDATE, SELECT, INSERT, DELETE ON automatorscript  TO astium;
GRANT UPDATE, SELECT ON automatorscript_id_seq TO astium;

INSERT INTO automatorscript (name) VALUES ('empty');

CREATE TABLE automator_automatorscript (
 id serial,

 created timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Creation Date
--HELP_EN_LONG:The date and time this record was created.
--HELP_NL_INFO:Datum aanmaak
--HELP_NL_LONG:De tijd en datum waarop dit is aangemaakt.

 lastupdate timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Last Update
--HELP_EN_LONG:The date and time this record was updated.
--HELP_NL_INFO:Laatste wijziging
--HELP_NL_LONG:De tijd en datum van laatste wijziging.

 automator integer NOT NULL default '1',
--HELP_EN_INFO:automator
--HELP_EN_LONG:The automator group
--HELP_NL_INFO:Automator
--HELP_NL_LONG:De Automator

 automatorscript integer NOT NULL default '1',
--HELP_EN_INFO:script
--HELP_EN_LONG:The script to execute
--HELP_NL_INFO:script
--HELP_NL_LONG:Het script wat moet worden uitgevoerd

 status varchar(8) NOT NULL default 'active',
--HELP_EN_INFO:status
--HELP_EN_LONG:record status
--HELP_NL_INFO:status
--HELP_NL_LONG:huidige status van dit record

 host integer NOT NULL default '1',
--HELP_EN_INFO:Host
--HELP_EN_LONG:The machine to run this script for
--HELP_NL_INFO:Host
--HELP_NL_LONG:De host waarvoor dit script moet worden uitgevoerd

 params text NOT NULL default '',
--HELP_EN_INFO:Parameters
--HELP_EN_LONG:The extra information to hand to this script
--HELP_NL_INFO:Parameters
--HELP_NL_LONG:De extra informatie die aan dit script gegeven wordt

 PRIMARY KEY  (id)
);

CREATE INDEX automator_automatorscript_automator_index ON automator_automatorscript (automator);
CREATE INDEX automator_automatorscript_host_index ON automator_automatorscript (host);

GRANT UPDATE, SELECT, INSERT, DELETE ON automator_automatorscript TO astium;
GRANT UPDATE, SELECT ON automator_automatorscript_id_seq TO astium;

CREATE TABLE automatorhist (
 id serial,

 stamp timestamp with time zone NOT NULL default CURRENT_TIMESTAMP,
--HELP_EN_INFO:stamp
--HELP_EN_LONG:De date/time this record was generated
--HELP_NL_INFO:tijd
--HELP_NL_LONG:De datum/tijd waarop deze regel werd aangemaakt

 automatorscript integer NOT NULL default '1',
--HELP_EN_INFO:script
--HELP_EN_LONG:The script which generated this record
--HELP_NL_INFO:script
--HELP_NL_LONG:Het script wat deze regel heeft aangemaakt

 status varchar(8) NOT NULL default 'OK',
--HELP_EN_INFO:status
--HELP_EN_LONG:The result status for this run
--HELP_NL_INFO:status
--HELP_NL_LONG:De status van dit script

 message text NOT NULL default '',
--HELP_EN_INFO:message
--HELP_EN_LONG:The result output for this run of the script
--HELP_NL_INFO:boodschap
--HELP_NL_LONG:De boodschap die door dit script werd afgegeven

 PRIMARY KEY  (id)
);

create index automatorhist_stamp_index on automatorhist (stamp);
create index automatorhist_automatorscript_index on automatorhist (automatorscript);

GRANT UPDATE, SELECT, INSERT, DELETE ON automatorhist TO astium;

alter table host add column protocol varchar(16) NOT NULL default 'telnet';
alter table host add column username varchar(64) NOT NULL default '';
alter table host add column password text NOT NULL default '';
alter table host add constraint "host_protocol_check" CHECK (protocol::text = 'ssh'::text OR protocol::text = 'telnet'::text);

