-- 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 alert (
 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',
--GENERATE:/etc/asterisk/company%04d/alert.conf:OLD.company:
--GENERATE:/etc/asterisk/company%04d/alert.conf:NEW.company:
--GENERATE:/etc/asterisk/company%04d/extensions.conf:OLD.company:
--GENERATE:/etc/asterisk/company%04d/extensions.conf:NEW.company:
--SERVICE:asterisk:reload
--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.

 status varchar(8) NOT NULL default 'active'
   CHECK (status IN ('active', 'deleted')),
--HELP_EN_INFO:status
--HELP_EN_LONG:The alert number status.
--HELP_NL_INFO:status
--HELP_NL_LONG:De status van dit inkomende nummer.

 message varchar(24) 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

 phoneline1 integer NOT NULL default '1',
--HELP_EN_INFO:primary extension
--HELP_EN_LONG:First extension to be called
--HELP_NL_INFO:eerste extensie
--HELP_NL_LONG:De eerste extensie die wordt gebeld.

 phoneline2 integer NOT NULL default '1',
--HELP_EN_INFO:secondary extension
--HELP_EN_LONG:The extension that should be called if rejected by the first extension. 
--HELP_NL_INFO:extern nummer
--HELP_NL_LONG:De extensie waarmee gebeld moet worden wanneer de eerste extensie weigert.

 phoneline3 integer NOT NULL default '1',
--HELP_EN_INFO:third extension
--HELP_EN_LONG:The extension that should be called if rejected by the second extension. 
--HELP_NL_INFO:derde extensie
--HELP_NL_LONG:De extensie die als laatste gebeld moet worden, wanneer de eerste twee extensies weigeren.

 connect1 integer NOT NULL default '1',
--HELP_EN_INFO:primary extension
--HELP_EN_LONG:First extension to be called if someone has been found
--HELP_NL_INFO:eerste extensie
--HELP_NL_LONG:Hiermee wordt doorverbonden als iemand voor het alarm is gevonden.

 PRIMARY KEY  (id)
);

GRANT UPDATE, SELECT, INSERT, DELETE ON alert TO astium;
GRANT UPDATE, SELECT, INSERT, DELETE ON alert_id_seq TO astium;
INSERT INTO alert (message, status) VALUES ('empty', 'deleted');

ALTER TABLE alert ADD CONSTRAINT alert_company_constraint FOREIGN KEY (company) REFERENCES company (id);
ALTER TABLE alert ADD CONSTRAINT alert_phoneline1_constraint FOREIGN KEY (phoneline1) REFERENCES phoneline (id);
ALTER TABLE alert ADD CONSTRAINT alert_phoneline2_constraint FOREIGN KEY (phoneline2) REFERENCES phoneline (id);
ALTER TABLE alert ADD CONSTRAINT alert_phoneline3_constraint FOREIGN KEY (phoneline3) REFERENCES phoneline (id);
ALTER TABLE alert ADD CONSTRAINT alert_connect1_constraint FOREIGN KEY (connect1) REFERENCES phoneline (id);

COMMIT;

