-- Copyright (c) 1997-now, NeoNova BV, The Netherlands. All rights Reserved.
--
-- This software is the copyrighted work of NeoNova BV.
-- Use of the software is subject to the general business terms and conditions of NeoNova BV
-- and its related transaction documents like license sheets or other written notices.#
-- Any reproduction or redistribution of the software not in accordance with the license terms
-- is expressly prohibited by law, and may result in severe civil and criminally penalties.
-- Violators will be prosecuted to the maximum extent possible.

BEGIN;

CREATE TABLE notification (
 id serial,

 stamp timestamp with time zone NOT NULL default CURRENT_TIMESTAMP,
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

 sessionid varchar(50) NOT NULL default '',
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

 src_name varchar(24) NOT NULL default '',
--HELP_EN_INFO:source
--HELP_EN_LONG:The system where this notification originated
--HELP_NL_INFO:Bron
--HELP_NL_LONG:Het systeem wat deze notificatie genereerde

 class varchar(8) NOT NULL default 'alarm'
   CHECK (class IN ('alarm', 'question', 'message')),
--HELP_EN_INFO:class
--HELP_EN_LONG:The class of this notification
--HELP_NL_INFO:klasse
--HELP_NL_LONG:De klasse van deze notificatie

 shortname varchar(24) NOT NULL default '',
--HELP_EN_INFO:shortname
--HELP_EN_LONG:Short name for this message
--HELP_NL_INFO:verkorte naam
--HELP_NL_LONG:Verkorte naam voor deze melding

 status varchar(8) NOT NULL default 'new'
   CHECK (status IN ('new', 'read', 'acknowledged', 'resolved', 'deleted')),
--HELP_EN_INFO:status
--HELP_EN_LONG:The alarm status.
--HELP_NL_INFO:status
--HELP_NL_LONG:De status van dit alarm.

 location varchar(24) default NULL,
--HELP_EN_INFO:location
--HELP_EN_LONG:The location where the alarm originated
--HELP_NL_INFO:locatie
--HELP_NL_LONG:De locatie waar het alarm is opgetreden

 message varchar(40) default NULL,
--HELP_EN_INFO:message
--HELP_EN_LONG:The alert message (or group) to respond to
--HELP_NL_INFO:melding
--HELP_NL_LONG:De meldingstekst (of groep) waarop gereageerd moet worden

 target integer NOT NULL default '0',
--HELP_EN_INFO:target
--HELP_EN_LONG:The target notification role for this message
--HELP_NL_INFO:bestemming
--HELP_NL_LONG:De bestemming voor deze notificatie

 resolvedby integer NOT NULL default '0',
--HELP_EN_INFO:resolved by
--HELP_EN_LONG:The person who handled this notification
--HELP_NL_INFO:Behandeld door
--HELP_NL_LONG:De persoon die deze notificatie uiteindelijk behandeld heeft.

 duration numeric NOT NULL default '0',
--HELP_EN_INFO:duration
--HELP_EN_LONG:The time until last response to this notification
--HELP_NL_INFO:behandeltijd
--HELP_NL_LONG:De tijd van optreden tot laatste handeling voor deze notificatie

 response varchar(40) default NULL,
--HELP_EN_INFO:response
--HELP_EN_LONG:the response to this notification, if applicable
--HELP_NL_INFO:antwoord
--HELP_NL_LONG:Het antwoord op deze notificatie indien van toepassing

 PRIMARY KEY  (id)
);

CREATE INDEX notification_stamp_index ON notification (stamp);
CREATE INDEX notification_sessionid_index ON notification (sessionid);
CREATE INDEX notification_class_index ON notification (class);
CREATE INDEX notification_target_index ON notification (target);
CREATE INDEX notification_resolvedby_index ON notification (resolvedby);

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

ALTER TABLE notification ADD CONSTRAINT notification_target_constraint FOREIGN KEY (target) REFERENCES notificationrole (id);
ALTER TABLE notification ADD CONSTRAINT notification_resolvedby_constraint FOREIGN KEY (resolvedby) REFERENCES notificationrole (id);

CREATE TABLE notificationhist (
 id serial,

 sessionid varchar(50) NOT NULL default '',
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

 stamp timestamp with time zone NOT NULL default CURRENT_TIMESTAMP,
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

 event integer NOT NULL default '0',
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

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

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

 message varchar(24) NOT NULL default '',
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

 PRIMARY KEY  (id)
);

create index notificationhist_sessionid_index on notificationhist (sessionid);
create index notificationhist_stamp_index on notificationhist (stamp);
create index notificationhist_event_index on notificationhist (event);
create index notificationhist_contact_index on notificationhist (contact);

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

ALTER TABLE notificationhist ADD CONSTRAINT notificationhist_contact_constraint FOREIGN KEY (contact) REFERENCES contact (id);
ALTER TABLE notificationhist ADD CONSTRAINT notificationhist_notificationrole_constraint FOREIGN KEY (notificationrole) REFERENCES notificationrole (id);

CREATE TABLE notificationrole_notification (
 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.

 notificationrole integer NOT NULL default '1',
--HELP_EN_INFO:notification role
--HELP_EN_LONG:The notification role for this agent.
--HELP_NL_INFO:notificatie rol
--HELP_NL_LONG:De notificatie rol voor deze medewerker.

 notification integer NOT NULL default '1',
--HELP_EN_INFO:notification
--HELP_EN_LONG:The notification for this notificationrole
--HELP_NL_INFO:notificatie
--HELP_NL_LONG:De notificatie voor deze notificatie rol.

 PRIMARY KEY  (id)
);

CREATE INDEX notificationrole_notification_notification_index ON notificationrole_notification (notification);
CREATE INDEX notificationrole_notification_notificationrole_index ON notificationrole_notification (notificationrole);

GRANT UPDATE, SELECT, INSERT, DELETE ON notificationrole_notification TO astium;
GRANT UPDATE, SELECT ON notificationrole_notification_id_seq TO astium;

ALTER TABLE notificationrole_notification ADD CONSTRAINT notificationrole_notification_notification_constraint FOREIGN KEY (notification) REFERENCES notification (id);
ALTER TABLE notificationrole_notification ADD CONSTRAINT notificationrole_notification_notificationrole_constraint FOREIGN KEY (notificationrole) REFERENCES notificationrole (id);

COMMIT;

