/*
 * PostGres SQL DDL for the BEAST Alarm Server Tables.
 *
 * Lana Abadie, based on original by chenx1@ornl.gov.
 *
 * Before using this file to create config tables, you must change hostname, 
 * username, password to the real name.
 * Under the directory containing this file, use this command to create the database:
 * su postgres -c "psql -q -f ALARM_POSTGRES.sql"
 */


set client_min_messages='warning';

-- 
-- TABLE: ALARM.ALARM_TREE 
--
DROP DATABASE IF EXISTS ALARM;

CREATE DATABASE ALARM;

\connect alarm;

set client_min_messages='warning';

CREATE SCHEMA alarm;

CREATE TABLE alarm.ALARM_TREE(
    COMPONENT_ID       INT           NOT NULL,
    PARENT_CMPNT_ID    INT                  ,
    NAME               VARCHAR(80)    NOT NULL,
    CONFIG_TIME        TIMESTAMP,
    PRIMARY KEY (COMPONENT_ID)
);

COMMENT ON COLUMN alarm.ALARM_TREE.COMPONENT_ID IS 'Component Identifier: The id for identification of each component.'
;
COMMENT ON COLUMN alarm.ALARM_TREE.PARENT_CMPNT_ID IS 'Parent Component Identifier:The parent id of the component in the configuration hierarchy, null for root of hierarchy.'
;
COMMENT ON COLUMN alarm.ALARM_TREE.NAME IS 'Name: Component name.'
;
COMMENT ON COLUMN alarm.ALARM_TREE.CONFIG_TIME IS 'Configuration Time: Time of last configuration update. '
;
-- 
-- TABLE: ALARM.COMMAND 
--

CREATE TABLE alarm.COMMAND(
    COMPONENT_ID     INT             NOT NULL,
    TITLE            VARCHAR(100)     NOT NULL ,
    COMMAND_ORDER    INT              NOT NULL ,
    DETAIL           VARCHAR(4000)    NOT NULL ,
    PRIMARY KEY (COMPONENT_ID, TITLE)
);


COMMENT on TABLE alarm.COMMAND IS  'command for the component.'
;

COMMENT ON COLUMN alarm.COMMAND.COMPONENT_ID IS 'Component Identifier: The id for identification of each component.'
;

COMMENT ON COLUMN alarm.COMMAND.TITLE IS 'Title: Brief description of the command, which will be displayed in the context menu.'
;

COMMENT ON COLUMN alarm.COMMAND.COMMAND_ORDER IS 'Order: The order by which the commands are arranged.'
;

COMMENT ON COLUMN alarm.COMMAND.DETAIL IS 'Detail: The related command which will be executed when you click on its title.'
;






-- 
-- TABLE: ALARM.DISPLAY 
--

CREATE TABLE alarm.DISPLAY(
    COMPONENT_ID     INT              NOT NULL ,
    TITLE            VARCHAR(100)     NOT NULL ,
    DISPLAY_ORDER    INT              NOT NULL ,
    DETAIL           VARCHAR(4000)    NOT NULL ,
    PRIMARY KEY (COMPONENT_ID, TITLE)
);

COMMENT ON TABLE alarm.DISPLAY IS  'Displays for the component.'
; 

COMMENT ON COLUMN alarm.DISPLAY.COMPONENT_ID  IS 'Component Identifier: The id for identification of each component.'
;

COMMENT ON COLUMN alarm.DISPLAY.TITLE  IS 'Title: Brief description of the display, which will be displayed in the context menu.'
;

COMMENT ON COLUMN alarm.DISPLAY.DISPLAY_ORDER  IS 'Order: The order by which the displays are arranged.'
;

COMMENT ON COLUMN alarm.DISPLAY.DETAIL   IS 'Detail: The related display which will be launched when you click on its title.'
;

-- 
-- TABLE: ALARM.GUIDANCE 
--

CREATE TABLE alarm.GUIDANCE(
    COMPONENT_ID      INT              NOT NULL ,
    TITLE             VARCHAR(100)     NOT NULL ,
    GUIDANCE_ORDER    INT              NOT NULL ,
    DETAIL            VARCHAR(4000)    NOT NULL ,
    PRIMARY KEY (COMPONENT_ID, TITLE)
);

COMMENT ON TABLE alarm.GUIDANCE IS  'Guidance information for the component.'
; 

COMMENT ON COLUMN alarm.GUIDANCE.COMPONENT_ID  IS 'Component Identifier: The id for identification of each component.'
;

COMMENT ON COLUMN alarm.GUIDANCE.TITLE  IS 'Title: Brief description of the guidance, which will be displayed in the context menu.'
;

COMMENT ON COLUMN alarm.GUIDANCE.GUIDANCE_ORDER  IS 'Order: The order by which the guidance are arranged.'
;

COMMENT ON COLUMN alarm.GUIDANCE.DETAIL  IS 'Detail: Guidance information which is displayed in the guidance dialog.'
;

-- 
-- TABLE: ALARM.AUTOMATED_ACTION
--

CREATE TABLE alarm.AUTOMATED_ACTION (
    COMPONENT_ID      INT              NOT NULL ,
    TITLE             VARCHAR(100)     NOT NULL ,
    AUTO_ACTION_ORDER INT              NOT NULL ,
    DETAIL            VARCHAR(4000)    NOT NULL ,
    DELAY             INT              NOT NULL ,
    PRIMARY KEY (COMPONENT_ID, TITLE)
);


COMMENT ON TABLE alarm.AUTOMATED_ACTION IS 'Automated actions for the component.'
;

COMMENT ON COLUMN alarm.AUTOMATED_ACTION.COMPONENT_ID IS 'Component Identifier: The id for identification of each component.'
;

COMMENT ON COLUMN alarm.AUTOMATED_ACTION.TITLE IS 'Title: The action title.'
;

COMMENT ON COLUMN alarm.AUTOMATED_ACTION.AUTO_ACTION_ORDER IS 'Order: The order by which the actions are arranged.'
;

COMMENT ON COLUMN alarm.AUTOMATED_ACTION.DETAIL IS 'Detail: The action value (send email, phone, etc.).'
;

COMMENT ON COLUMN alarm.AUTOMATED_ACTION.DELAY IS 'Delay: The action delay in seconds.'
;

-- 
-- TABLE: ALARM.PV 
--

CREATE TABLE alarm.PV(
    COMPONENT_ID       INT              NOT NULL           ,
    DESCR              VARCHAR(100)     ,
    ENABLED_IND        boolean    DEFAULT false NOT NULL ,
    ANNUNCIATE_IND     boolean    DEFAULT false NOT NULL ,
    LATCH_IND          boolean    DEFAULT false NOT NULL,
    DELAY              INT    ,
    FILTER             VARCHAR(4000)                       ,
    DELAY_COUNT        INT                                 ,
    STATUS_ID          INT                                 ,
    SEVERITY_ID        INT                                 ,
    CUR_STATUS_ID      INT                                 ,
    CUR_SEVERITY_ID    INT                                 ,
    PV_VALUE           VARCHAR(100)                        ,
    ALARM_TIME         TIMESTAMP                           ,
    ACT_GLOBAL_ALARM_IND  boolean    DEFAULT false NOT NULL,
    PRIMARY KEY (COMPONENT_ID)
);


COMMENT ON TABLE alarm.PV IS  'Process Variable:'
; 

COMMENT ON COLUMN alarm.PV.COMPONENT_ID  IS 'Component Identifier: The id for identification of each component.'
;

COMMENT ON COLUMN alarm.PV.DESCR IS 'Description: Description that might be more meaningful than PV name.'
;

COMMENT ON COLUMN alarm.PV.ENABLED_IND  IS 'Enabled Indicator: Indicates if alarms are enabled for a given PV.'
;

COMMENT ON COLUMN alarm.PV.ANNUNCIATE_IND  IS 'Annunciate Indicator:  Indicates if alarm should be annunciated.'
;

COMMENT ON COLUMN alarm.PV.LATCH_IND  IS 'Latch Indicator: Indicates that alarm should be latched for acknowledgement, even if PV recovers.'
;

COMMENT ON COLUMN alarm.PV.DELAY  IS 'Delay: Minimum time in seconds before raising the alarm.'
;

COMMENT ON COLUMN alarm.PV.FILTER  IS 'Filter: Filter expression, may be used to compute ''enabled'' from expression.'
;

COMMENT ON COLUMN alarm.PV.DELAY_COUNT  IS  'Delay Count: Alarm when PV != OK more often than this count within delay.'
;

COMMENT ON COLUMN alarm.PV.STATUS_ID   IS  'Status Identifier: Alarm system state for the severity identifier.'
;

COMMENT ON COLUMN alarm.PV.SEVERITY_ID  IS  'Severity Identifier: Alarm system severity.'
;

COMMENT ON COLUMN alarm.PV.CUR_SEVERITY_ID  IS 'Current Severity Identifier: Current severity of PV.'
;

COMMENT ON COLUMN alarm.PV.PV_VALUE  IS  'Process Variable Value: PV value that caused severity/status.'
;

COMMENT ON COLUMN alarm.PV.ALARM_TIME  IS  'Alarm Time: The time of the most recent alarm.'
;

COMMENT ON COLUMN alarm.PV.ACT_GLOBAL_ALARM_IND  IS  'Indicates if PV has an active global alarm.'
;

-- 
-- TABLE: ALARM.SEVERITY 
--

CREATE TABLE alarm.SEVERITY(
    SEVERITY_ID    INT             NOT NULL,
    NAME           VARCHAR(100)    NOT NULL,
    PRIMARY KEY (SEVERITY_ID)
);

COMMENT ON TABLE alarm.SEVERITY IS 'Severity of an alarm like "invalid", "major alarm" etc.'
;

COMMENT ON COLUMN alarm.SEVERITY.SEVERITY_ID IS 'Severity Identifier: Unique identifier for the alarm severity.'
;

COMMENT ON COLUMN alarm.SEVERITY.NAME IS 'Severity Name: '
;
-- 
-- TABLE: ALARM.STATUS 
--

CREATE TABLE alarm.STATUS(
    STATUS_ID    INT             NOT NULL ,
    NAME         VARCHAR(100)    NOT NULL,
    PRIMARY KEY (STATUS_ID)
);

COMMENT ON TABLE alarm.STATUS IS 'status of an alarm to provide more detail'
;

COMMENT ON COLUMN alarm.STATUS.STATUS_ID IS 'Status Identifier: Unique identifier for the alarm status.'
;
COMMENT ON COLUMN alarm.STATUS.NAME IS 'Status Name: such as "read error", "disconnected", ...'
;
-- 
-- INDEX: FK_ALARM_TREE_TO_ALARM_TREE 
--

CREATE INDEX FK_ALARM_TREE_TO_ALARM_TREE ON alarm.ALARM_TREE(PARENT_CMPNT_ID)
;
-- 
-- INDEX: FK_COMMAND_TO_ALARM_TREE 
--

CREATE INDEX FK_COMMAND_TO_ALARM_TREE ON alarm.COMMAND(COMPONENT_ID)
;
-- 
-- INDEX: FK_DISPLAY_TO_ALARM_TREE 
--

CREATE INDEX FK_DISPLAY_TO_ALARM_TREE ON alarm.DISPLAY(COMPONENT_ID)
;
-- 
-- INDEX: FK_GUIDANCE_TO_ALARM_TREE 
--

CREATE INDEX FK_GUIDANCE_TO_ALARM_TREE ON alarm.GUIDANCE(COMPONENT_ID)
;
-- 
-- INDEX: FK_AUTO_ACTION_TO_ALARM_TREE 
--

CREATE INDEX FK_AUTO_ACTION_TO_ALARM_TREE ON alarm.AUTOMATED_ACTION(COMPONENT_ID)
;
-- 
-- INDEX: FK_PV_TO_STATUS 
--

CREATE INDEX FK_PV_TO_STATUS ON alarm.PV(STATUS_ID)
;
-- 
-- INDEX: FK_PV_TO_SEVERITY 
--

CREATE INDEX FK_PV_TO_SEVERITY ON alarm.PV(SEVERITY_ID)
;
-- 
-- INDEX: FK_PV_TO_ALARM_TREE 
--

CREATE INDEX FK_PV_TO_ALARM_TREE ON alarm.PV(COMPONENT_ID)
;
-- 
-- TABLE: ALARM.ALARM_TREE 
--

ALTER TABLE alarm.ALARM_TREE ADD CONSTRAINT FK_ALARM_TREE_TO_ALARM_TREE 
    FOREIGN KEY (PARENT_CMPNT_ID)
    REFERENCES alarm.ALARM_TREE(COMPONENT_ID)
;


-- 
-- TABLE: ALARM.COMMAND 
--

ALTER TABLE alarm.COMMAND ADD CONSTRAINT FK_COMMAND_TO_ALARM_TREE 
    FOREIGN KEY (COMPONENT_ID)
    REFERENCES alarm.ALARM_TREE(COMPONENT_ID)
;


-- 
-- TABLE: ALARM.DISPLAY 
--

ALTER TABLE alarm.DISPLAY ADD CONSTRAINT FK_DISPLAY_TO_ALARM_TREE 
    FOREIGN KEY (COMPONENT_ID)
    REFERENCES alarm.ALARM_TREE(COMPONENT_ID)
;


-- 
-- TABLE: ALARM.GUIDANCE 
--

ALTER TABLE alarm.GUIDANCE ADD CONSTRAINT FK_GUIDANCE_TO_ALARM_TREE 
    FOREIGN KEY (COMPONENT_ID)
    REFERENCES alarm.ALARM_TREE(COMPONENT_ID)
;


-- 
-- TABLE: ALARM.AUTOMATED_ACTION
--

ALTER TABLE alarm.AUTOMATED_ACTION ADD CONSTRAINT FK_AUTO_ACTION_TO_ALARM_TREE 
    FOREIGN KEY (COMPONENT_ID)
    REFERENCES alarm.ALARM_TREE(COMPONENT_ID)
;


-- 
-- TABLE: ALARM.PV 
--

ALTER TABLE alarm.PV ADD CONSTRAINT FK_CUR_SVRTY_TO_SEVERITY 
    FOREIGN KEY (CUR_SEVERITY_ID)
    REFERENCES alarm.SEVERITY(SEVERITY_ID)
;

ALTER TABLE alarm.PV ADD CONSTRAINT FK_PV_TO_ALARM_TREE 
    FOREIGN KEY (COMPONENT_ID)
    REFERENCES alarm.ALARM_TREE(COMPONENT_ID)
;

ALTER TABLE alarm.PV ADD CONSTRAINT FK_PV_TO_SEVERITY 
    FOREIGN KEY (SEVERITY_ID)
    REFERENCES alarm.SEVERITY(SEVERITY_ID)
;

ALTER TABLE alarm.PV ADD CONSTRAINT FK_PV_TO_STATUS 
    FOREIGN KEY (STATUS_ID)
    REFERENCES alarm.STATUS(STATUS_ID)
;


-- The following would have to be executed _after_ creating the tables:
GRANT USAGE ON SCHEMA alarm TO alarm;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON alarm.alarm_tree, alarm.command, alarm.display, alarm.guidance, alarm.pv, alarm.severity, alarm.status, alarm.automated_action TO alarm;

GRANT USAGE ON SCHEMA alarm TO report;
GRANT SELECT
  ON alarm.alarm_tree, alarm.command, alarm.display, alarm.guidance, alarm.pv, alarm.severity, alarm.status, alarm.automated_action  TO report;
