--# create.sql -- SQL to build the initial tables for the OpenNMS Project
--#
--# Modifications:
--# 2013 Nov 15: Added protocol field in datalinkinterface table
--# 2009 Sep 29: Added linkTypeId field in datalinkinterface table
--# 2009 Mar 27: Added Users, Groups tables
--# 2009 Jan 28: Added Acks tables - david@opennms.org
--# 2007 Apr 10: Added statistics report tables - dj@opennms.org
--# 2006 Apr 17: Added pathOutage table
--# 2005 Mar 11: Added alarms table
--# 2004 Aug 30: See create.sql.changes
--#
--# Copyright (C) 2005-2006 The OpenNMS Group, Inc., Inc.  All rights reserved.
--# Parts Copyright (C) 1999-2001 Oculan Corp.  All rights reserved.
--#
--# This program is free software; you can redistribute it and/or modify
--# it under the terms of the GNU General Public License as published by
--# the Free Software Foundation; either version 2 of the License, or
--# (at your option) any later version.
--#
--# This program is distributed in the hope that it will be useful,
--# but WITHOUT ANY WARRANTY; without even the implied warranty of
--# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--# GNU General Public License for more details.
--#
--# You should have received a copy of the GNU General Public License
--# along with this program; if not, write to the Free Software
--# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
--#
--# For more information contact:
--#      OpenNMS Licensing       <license@opennms.org>
--#      http://www.opennms.org/
--#      http://www.sortova.com/
--#

drop table accessLocks cascade;
drop table accesspoints cascade;
drop table category_node cascade;
drop table categories cascade;
drop table assets cascade;
drop table usersNotified cascade;
drop table notifications cascade;
drop table outages cascade;
drop table ifServices cascade;
drop table snmpInterface cascade;
drop table ipInterface cascade;
drop table alarms cascade;
drop table memos cascade;
drop table node cascade;
drop table service cascade;
drop table distPoller cascade;
drop table events cascade;
drop table vulnerabilities cascade;
drop table vulnPlugins cascade;
drop table serverMap cascade;
drop table serviceMap cascade;
drop table pathOutage cascade;
drop table demandPolls cascade;
drop table pollResults cascade;
drop table reportLocator cascade;
drop table atinterface cascade;
drop table stpnode cascade;
drop table stpinterface cascade;
drop table iprouteinterface cascade;
drop table datalinkinterface cascade;
drop table inventory cascade;
drop table element cascade;
drop table map cascade;
drop table location_monitors cascade;
drop table location_specific_status_changes cascade;
drop table vlan cascade;
drop table statisticsReportData cascade;
drop table resourceReference cascade;
drop table statisticsReport cascade;
drop table acks cascade;
drop table users cascade;
drop table groups cascade;
drop table group_user cascade;
drop table category_user cascade;
drop table category_group cascade;
drop table filterfavorites cascade;

drop sequence catNxtId;
drop sequence nodeNxtId;
drop sequence serviceNxtId;
drop sequence eventsNxtId;
drop sequence alarmsNxtId;
drop sequence memoNxtId;
drop sequence outageNxtId;
drop sequence notifyNxtId;
drop sequence userNotifNxtId;
drop sequence demandPollNxtId;
drop sequence pollResultNxtId;
drop sequence vulnNxtId;
drop sequence reportNxtId;
drop sequence reportCatalogNxtId;
drop sequence mapNxtId;
drop sequence opennmsNxtId;  --# should be used for all sequences, eventually
drop sequence filternextid;

drop index filternamesidx;

--# Begin quartz persistence 

drop table qrtz_job_listeners;
drop table qrtz_trigger_listeners;
drop table qrtz_fired_triggers;
drop table qrtz_paused_trigger_grps;
drop table qrtz_scheduler_state;
drop table qrtz_locks;
drop table qrtz_simple_triggers;
drop table qrtz_cron_triggers;
drop table qrtz_blob_triggers;
drop table qrtz_triggers;
drop table qrtz_job_details;
drop table qrtz_calendars;

--# End quartz persistence

CREATE FUNCTION plpgsql_call_handler () 
    RETURNS OPAQUE AS '$libdir/plpgsql.so' LANGUAGE 'c';

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' 
    HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL';

--##################################################################
--# The following commands set up automatic sequencing functionality
--# for fields which require this.
--#
--# DO NOT forget to add an "install" comment so that the installer
--# knows to fix and renumber the sequences if need be
--##################################################################

--# Sequence for the nodeID column in the aggregate_status_views and the
--# aggregate_status_definitions tables (eventually all tables, perhaps)
--#          sequence, column, table
--# install: opennmsNxtId id   aggregate_status_views
create sequence opennmsNxtId minvalue 1;

--# Sequence for the nodeID column in the node table
--#          sequence, column, table
--# install: nodeNxtId nodeID   node
create sequence nodeNxtId minvalue 1;

--# Sequence for the serviceID column in the service table
--#          sequence,    column,   table
--# install: serviceNxtId serviceID service
create sequence serviceNxtId minvalue 1;

--# Sequence for the eventID column in the events table
--#          sequence,   column, table
--# install: eventsNxtId eventID events
create sequence eventsNxtId minvalue 1;

--# Sequence for the alarmId column in the alarms table
--#          sequence,   column, table
--# install: alarmsNxtId alarmId alarms
create sequence alarmsNxtId minvalue 1;

--# Sequence for the id column in the memos table
--#          sequence,   column, table
--# install: memoNxtId id memos
create sequence memoNxtId minvalue 1;

--# Sequence for the outageID column in the outages table
--#          sequence,   column,  table
--# install: outageNxtId outageID outages
create sequence outageNxtId minvalue 1;

--# Sequence for the notifyID column in the notification table
--#          sequence,   column,  table
--# install: notifyNxtId notifyID notifications
create sequence notifyNxtId minvalue 1;

--# Sequence for the vulnerabilityID column in the vulnerabilities table
--#          sequence, column,         table
--# install: vulnNxtId vulnerabilityID vulnerabilities
create sequence vulnNxtId minvalue 1;

--# Sequence for the id column in the categories table
--#          sequence, column, table
--# install: catNxtId categoryid   categories
create sequence catNxtId minvalue 1;

--# Sequence for the id column in the usersNotified table
--#          sequence, column, table
--# install: userNotifNxtId id   usersNotified
create sequence userNotifNxtId minvalue 1;

--# Sequence for the id column in the demandPolls table
--#          sequence, column, table
--# install: demandPollNxtId id   demandPolls
create sequence demandPollNxtId minvalue 1;

--# Sequence for the id column in the pollResults table
--#          sequence, column, table
--# install: pollResultNxtId id   pollResults
create sequence pollResultNxtId minvalue 1;

--# Sequence for the mapID column in the map table
--#          sequence,   column, table
--# install: mapNxtId mapid map
create sequence mapNxtId minvalue 1;

--# Sequence for the filterid column in the filterfavorites table
--#          sequence, column, table
--# install: filternextid filterid filterfavorites
create sequence filternextid minvalue 1;


--# A table to use to manage upsert access

CREATE TABLE accessLocks (
    lockName varchar(40) not null,
    constraint pk_accessLocks PRIMARY KEY (lockName)
);


--# 


--########################################################################
--# serverMap table - Contains a list of IP Addresses mapped to
--#                   OpenNMS servers
--#
--# This table contains the following fields:
--#
--#  ipAddr      : IP address of the device to be monitored
--#  serverName  : Text field to store the server name
--#
--########################################################################

create table serverMap (
	ipAddr			text not null,
	serverName		varchar(64) not null );

create index server_name_idx on serverMap(serverName);

--########################################################################
--# serviceMap table - Contains a list of IP Addresses mapped to
--#                    OpenNMS services
--#
--# This table contains the following fields:
--#
--#  ipAddr          : IP address of the device to be monitored
--#  serviceName     : Text field to store the service name
--#
--########################################################################

create table serviceMap (
	ipAddr			text not null,
	serviceMapName		varchar(255) not null
);
create index servicemap_name_idx on serviceMap(serviceMapName);
create index serviceMap_ipaddr_idx on serviceMap(ipAddr);

--########################################################################
--# distPoller table - Contains information on Distributed Pollers
--#                    installed in this OpenNMS instance.
--#
--# This table contains the following fields:
--#
--#  dpName      : A human-readable name for each system.  Typically,
--#                the system's hostname (not fully qualified).
--#  dpIP        : IP address of the distributed poller.
--#  dpComment   : Free-form text field
--#  dpDiscLimit : Numeric representation of percentage of interface speed
--#                available to discovery process.  See documentation for
--#                "bandwidth troll"
--#  dpLastNodePull 	: Time of last pull of new nodes from the DP
--#  dpLastEventPull	: Time of last pull of events from the DP
--#  dpLastPackagePush	: Time of last push of Package (config) to the DP
--#  dpAdminState: Reflects desired state for this distributed poller.
--#                1 = Up, 0 = Down
--#  dpRunState  : Reflects the current perceived state of the distributed
--#                poller.  1 = Up, 0 = Down
--#
--########################################################################

create table distPoller (
	dpName			varchar(12) not null,
	dpIP			text not null,
	dpComment		varchar(256),
	dpDiscLimit		numeric(5,2),
	dpLastNodePull		timestamp with time zone,
	dpLastEventPull		timestamp with time zone,
	dpLastPackagePush	timestamp with time zone,
	dpAdminState 		integer,
	dpRunState		integer,

	constraint pk_dpName primary key (dpName)
);

--########################################################################
--# node Table - Contains information on nodes discovered and potentially
--#              managed by OpenNMS.  nodeSys* fields map to SNMP MIB 2
--#              system table information.
--#
--# This table contains the following fields:
--#
--#  nodeID          : Unique identifier for node.  Note that this is the
--#                    enabler for overlapping IP ranges and that uniquity
--#                    is dependent on combination of dpName & IP address
--#  dpName          : Distributed Poller responsible for this node
--#  nodeCreateTime  : Time node was added to the database
--#  nodeParentID    : In the case that the node is virtual or an independent
--#                    device in a chassis that should be reflected as a
--#                    subcomponent or "child", this field reflects the nodeID
--#                    of the chassis/physical node/"parent" device.
--#                    Currently unused.
--#  nodeType        :  Flag indicating status of node
--#			'A' - active
--#  			'D' - deleted
--#  nodeSysOID      : SNMP MIB-2 system.sysObjectID.0
--#  nodeSysName     : SNMP MIB-2 system.sysName.0
--#  nodeSysDescription    : SNMP MIB-2 system.sysDescr.0
--#  nodeSysLocation : SNMP MIB-2 system.sysLocation.0
--#  nodeSysContact  : SNMP MIB-2 system.sysContact.0
--#  nodeLabel	     : User-friendly name associated with the node.
--#  nodeLabelSource : Flag indicating source of nodeLabel
--#                      'U' = user defined
--#                      'H' = IP hostname
--#                      'S' = sysName
--#                      'A' = IP address
--# nodeNetBIOSName  : NetBIOS workstation name associated with the node.
--# nodeDomainName   : NetBIOS damain name associated with the node.
--# operatingSystem  : Operating system running on the node.
--# lastCapsdPoll    : Date and time of last Capsd scan.
--# foreignSource    : When importing nodes this contains the source of the
--#                       nodes, null otherwise
--# foriegnId        : When importing nodes this contains the id of the node
--#                       as known to the foriegn source, null otherwise
--########################################################################

create table node (
	nodeID		integer not null,
	dpName		varchar(12),
	nodeCreateTime	timestamp with time zone not null,
	nodeParentID	integer,
	nodeType	char(1),
	nodeSysOID	varchar(256),
	nodeSysName	varchar(256),
	nodeSysDescription	varchar(256),
	nodeSysLocation	varchar(256),
	nodeSysContact	varchar(256),
	nodeLabel	varchar(256),
	nodeLabelSource	char(1),
	nodeNetBIOSName varchar(16),
	nodeDomainName  varchar(16),
	operatingSystem varchar(64),
	lastCapsdPoll   timestamp with time zone,
	foreignSource	varchar(64),
	foreignId       varchar(64),

	constraint pk_nodeID primary key (nodeID),
	constraint fk_dpName foreign key (dpName) references distPoller
);

create index node_id_type_idx on node(nodeID, nodeType);
create index node_label_idx on node(nodeLabel);
create index node_dpname_idx on node(dpName);
create unique index node_foreign_unique_idx on node(foreignSource, foreignId);

--#########################################################################
--# snmpInterface Table - Augments the ipInterface table with information
--#                       available from IP interfaces which also support
--#                       SNMP.
--#
--# This table provides the following information:
--#
--#  nodeID             : Unique identifier for node to which this if belongs
--#  snmpIpAdEntNetMask : SNMP MIB-2 ipAddrTable.ipAddrEntry.ipAdEntNetMask
--#                       Value is interface's subnet mask
--#  snmpPhysAddr       : SNMP MIB-2 ifTable.ifEntry.ifPhysAddress
--#                       Value is interface's MAC Address
--#  snmpIfIndex        : SNMP MIB-2 ifTable.ifEntry.ifIndex
--#                       Value is interface's arbitrarily assigned index,
--#                       or -100 if we can query the agent, but we can't find
--#                       this IP address in the ifTable.
--#  snmpIfDescr        : SNMP MIB-2 ifTable.ifEntry.ifDescr
--#                       Value is interface's manufacturer/product name/version
--#  snmpIfType         : SNMP MIB-2 ifTable.ifEntry.ifType
--#                       Value is interface's physical/link protocol
--#  snmpIfName		: SNMP MIB-2 ifTable.ifEntry.ifName
--#			  Value is interface's device name
--#  snmpIfSpeed        : SNMP MIB-2 ifTable.ifEntry.ifSpeed
--#                       Value is estimate of interface's data rate
--#  snmpIfAdminStatus  : SNMP MIB-2 ifTable.ifEntry.ifAdminStatus
--#                       Value is interface's desired status
--#                       1 = Up, 2 = Down, 3 = Testing
--#  snmpIfOperStatus   : SNMP MIB-2 ifTable.ifEntry.ifOperStatus
--#                       Value is interface's current operational status
--#                       1 = Up, 2 = Down, 3 = Testing
--#  snmpIfAlias		: SNMP MIB-2 ifXTable.ifXEntry.ifAlias
--#			  Value is interface's device alias
--#  snmpCollect        : 'C' means collect 'N' means don't collect
--#                     : 'UC' means collect 'UN' means don't collect (user override)
--#                       This has been moved from the isSnmpPrimary field in the
--#                         ipinterface table
--#  snmpLastCapsdPoll  : Date and time of last poll by capsd or provisiond
--#  snmpPoll           : 'P' means polled 'N' means not polled (interface admin and oper status)
--#  snmpLastSnmpPoll   : Date and time of last snmp poll 
--#
--# NOTE:  Although not marked as "not null" the snmpIfIndex field
--#        should never be null.  This table is considered to be uniquely
--#        keyed by nodeId and snmpIfIndex.  Eventually ipAddr and
--#        snmpIpAdEntNetMask will be removed and netmask added to
--#        the ipInterface table.
--########################################################################

create table snmpInterface (
    id				INTEGER DEFAULT nextval('opennmsNxtId') NOT NULL,
	nodeID			integer not null,
	snmpIpAdEntNetMask	varchar(45),
	snmpPhysAddr		varchar(32),
	snmpIfIndex		integer not null,
	snmpIfDescr		varchar(256),
	snmpIfType		integer,
	snmpIfName		varchar(96),
	snmpIfSpeed		bigint,
	snmpIfAdminStatus	integer,
	snmpIfOperStatus	integer,
	snmpIfAlias		varchar(256),
    snmpLastCapsdPoll timestamp with time zone,
    snmpCollect     varchar(2) default 'N',
    snmpPoll     varchar(1) default 'N',
    snmpLastSnmpPoll timestamp with time zone,

    CONSTRAINT snmpinterface_pkey primary key (id),
	constraint fk_nodeID2 foreign key (nodeID) references node ON DELETE CASCADE
);

create unique index snmpinterface_nodeid_ifindex_unique_idx on snmpinterface(nodeID, snmpIfIndex);
create index snmpinterface_nodeid_idx on snmpinterface(nodeID);

--########################################################################
--# ipInterface Table - Contains information on interfaces which support
--#                     TCP/IP as well as current status information.
--#                     ipAddr is integer, to support easier filtering.
--#
--# This table contains the following information:
--#
--#  nodeID          : Unique identifier of the node that "owns" this interface
--#  ipAddr          : IP Address associated with this interface
--#  ifIndex	     : SNMP index of interface, used to uniquely identify
--# 		           unnumbered interfaces, or null if there is no mapping to
--#                    snmpInterface table.  Can be -100 if old code added an
--#                    snmpInterface table entry but no SNMP data could be gathered.
--#
--# NOTE: The combination of nodeID, ipAddr, and ifIndex must be unique,
--# and this must be enforced programmatically.
--#
--#  ipHostname      : IP Hostname associated with this interface
--#  isManaged       : Character used as a boolean flag
--#                     'M' - Managed
--#                     'A' - Alias
--#                     'D' - Deleted
--#                     'U' - Unmanaged
--#                     'F' - Forced Unmanaged (via the user interface)
--#                     'N' - Not polled as part of any package
--#                     'X' - Remotely Monitored only
--#  ipStatus        : If interface supports SNMP this field will
--#                    hold a numeric representation of interface's
--#                    operational status (same as 'snmpIfOperStatus'
--#                    field in the snmpInterface table).
--#                      1 = Up, 2 = Down, 3 = Testing
--#  ipLastCapsdPoll : Date and time of last poll by capsd or provisiond
--#  isSnmpPrimary   : Character used as a boolean flag
--#                      'P' - Primary SNMP
--#                      'S' - Secondary SNMP
--#                      'N' - Not eligible (does not support SNMP or
--#                               or has no ifIndex)
--#                     NOTE: 'C' is no longer a valid value for isSnmpPrimary
--#                       this has moved to the snmpinterface table
--#
--########################################################################

create table ipInterface (
    id              INTEGER DEFAULT nextval('opennmsNxtId') NOT NULL,
	nodeID			integer not null,
	ipAddr			text not null,
	ifIndex			integer,
	ipHostname		varchar(256),
	isManaged		char(1),
	ipStatus		integer,
    ipLastCapsdPoll timestamp with time zone,
	isSnmpPrimary   char(1),
	snmpInterfaceId	integer,

	CONSTRAINT ipinterface_pkey PRIMARY KEY (id),
	CONSTRAINT snmpinterface_fkey2 FOREIGN KEY (snmpInterfaceId) REFERENCES snmpInterface (id) ON DELETE SET NULL,
	constraint fk_nodeID1 foreign key (nodeID) references node ON DELETE CASCADE
);

create unique index ipinterface_nodeid_ipaddr_notzero_idx on ipInterface (nodeID, ipAddr) WHERE ipAddr != '0.0.0.0';
create index ipinterface_nodeid_ipaddr_ismanaged_idx on ipInterface (nodeID, ipAddr, isManaged);
create index ipinterface_ipaddr_ismanaged_idx on ipInterface (ipAddr, isManaged);
create index ipinterface_ipaddr_idx on ipInterface (ipAddr);
create index ipinterface_nodeid_ismanaged_idx on ipInterface (ipAddr);
create index ipinterface_nodeid_idx on ipInterface (nodeID);
create index ipinterface_snmpInterfaceId_idx on ipInterface (snmpInterfaceId);

--########################################################################
--# service Table - Contains a name<->number mapping for services
--#                 (e.g., poller packages)
--#
--# This table provides the following information:
--#
--#  serviceID   : Unique integer mapping to service/poller package
--#  serviceName : Name associated with service/poller package
--########################################################################

create table service (
	serviceID		integer default nextval('serviceNxtId') not null,
	serviceName		varchar(255) not null,

	constraint pk_serviceID primary key (serviceID)
);

create unique index service_servicename_key on service (serviceid);

--########################################################################
--# ifServices Table - Contains a mapping of interfaces to services available
--#                    on those interfaces (e.g., FTP, SMTP, DNS, etc.) and
--#                    recent polling status information.
--#
--# This table provides the following information:
--#
--#  nodeID    : Unique integer identifier for node
--#  ipAddr    : IP Address of node's interface
--#  ifIndex   : SNMP ifIndex, if available, null otherwise
--#  serviceID : Unique integer identifier of service/poller package
--#  lastGood  : Date and time of last successful poll by this poller package
--#  lastFail  : Date and time of last failed poll by this poller package
--#  qualifier : Service qualifier.  May be used to distinguish two
--#		         services which have the same serviceID.  For example, in the
--#              case of the HTTP service a qualifier might be the specific
--#              port on which the HTTP server was found.
--#  status    : Flag indicating the status of the service.
--#                'A' - Active
--#                'D' - Deleted
--#                'U' - Unmanaged (per capsd configuration change and CAPSD)
--#                'F' - Forced unmanaged (via user interface)
--#                'N' - Not polled as part of any of the packages that the
--#                      interface belongs to
--#                'X' - service is remotely monitored only
--#  source    : Flag indicating how the service was detected.
--#                'P' - Plugin
--#                'F' - Forced (via CapsdPluginBehavior.conf)
--#  notify    : Flag indicating if this service should be notified on or not
--#                'Y' - to notify
--#                'N' = not to notify
--########################################################################

create table ifServices (
    id				integer default nextval('opennmsNxtId') NOT NULL,
	nodeID			integer not null,
	ipAddr			text not null,
	ifIndex			integer,
	serviceID		integer not null,
	lastGood		timestamp with time zone,
	lastFail		timestamp with time zone,
	qualifier		char(16),
	status         	char(1),
	source			char(1),
	notify          char(1),
	ipInterfaceId	integer not null,

	CONSTRAINT ifservices_pkey PRIMARY KEY (id), 
	CONSTRAINT ipinterface_fkey FOREIGN KEY (ipInterfaceId) REFERENCES ipInterface (id) ON DELETE CASCADE,
	constraint fk_nodeID3 foreign key (nodeID) references node ON DELETE CASCADE,
	constraint fk_serviceID1 foreign key (serviceID) references service ON DELETE CASCADE
);

create unique index ifservices_nodeid_ipaddr_svc_unique on ifservices(nodeID, ipAddr, serviceId);
create index ifservices_nodeid_ipaddr_status on ifservices(nodeID, ipAddr, status);
create index ifservices_nodeid_status on ifservices(nodeid, status);
create index ifservices_nodeid_idx on ifservices(nodeID);
create index ifservices_serviceid_idx on ifservices(serviceID);
create index ifservices_nodeid_serviceid_idx on ifservices(nodeID, serviceID);
create index ifservicves_ipInterfaceId_idx on ifservices(ipInterfaceId);

--##################################################################
--# events Table -- This table provides information on the events
--#                 that are passed into the event subsystem.  It
--#                 contains information defining the event as
--#                 unique, while additional information is stored
--#                 in the eventsDetail table.
--#
--# This table provides the following information:
--#
--#  eventID   		: Unique identifier for the event
--#  eventUei		: Universal Event Identifer (UEI) for this event
--#  eventSnmp		: Contains the eid, eidtext (optionally), specific,
--#			  and generic identifier for the SNMP Trap.  This
--#			  maps directly to the <snmp> element in the
--#			  Event Data Stream DTD.
--#  eventTime		: The <time> element from the Event Data Stream DTD,
--#			  which is the time the event was received by the
--#			  source process.
--#  eventCreateTime 	: Creation time of event in database
--#  eventHost   	: The <host> element from the Event Data Stream DTD
--#  eventSource        : The entity/process which generated the event.
--#  eventSnmphost	: The <snmphost> element from the Event Data Stream DTD
--#  eventDpName	: The dpName of the Dist Poller which received the
--#			  event
--#  eventParms		: The <parms> element from the Event Data Stream DTD
--#  nodeID             : Unique integer identifier for node
--#  ifindex		: The <ifindex> element from the Event Data Stream DTD
--#  ipAddr             : IP Address of node's interface
--#  serviceID          : Unique integer identifier of service/poller package
--#  eventDescr		: Free-form textual description of the event
--#  eventLogmsg	: The log message for the event
--#  eventSeverity	: Severity of event
--#			   1 = Indeterminate
--#			   2 = Cleared (implementation is now in alarms)
--#			   3 = Normal
--#			   4 = Warning
--#			   5 = Minor
--#			   6 = Major
--#			   7 = Critical
--#  eventPathOutage	: Event Path outage information	
--#  eventCorrelation	: The event correlation configured for this event
--#			  (stored as an XML string)
--#  eventSuppressedCount	: The number of times the event was suppressed
--#			  (if event correlation was set for suppression)
--#  eventOperInstruct 	: Operator instruction for event.
--#  eventAutoAction	: Automated Action for event.  Should
--#			  consist of fully-qualfied pathname to
--#			  executable command, with possible variables
--#			  used to reference event-specific data
--#  eventOperAction   	: Operator Action for event.  Should
--#			  consist of fully-qualfied pathname to
--#			  executable command, with possible variables
--#			  used to reference event-specific data
--#  eventOperActionMenuText	: Menu text displayed to Operator, which if
--#			  selected, will invoke action described in
--#			  eventOperAction
--#  eventLoggroup	: Logical group with which to associate event.
--#			  This field provides a means of logically
--#			  grouping related events.
--#  eventNotification  : Notification string.  Should consist of
--#			  a fully-qualfied pathname to an executable
--#			  which invokes the notification software, and
--#			  will likely contain event-specific variables
--#  eventTticket       : Trouble ticket integration string.  Should
--#			  consist of fully-qualfied pathname to
--#			  executable command, with possible variables
--#			  used to reference event-specific data
--#  eventTticketState  : Trouble ticket on/off boolean
--#   				1=on, 0=off
--#  eventForward       : Contains a list of triplets:
--#	  		    Destination,State,Mechanism;Destination,State,Mechanism;
--#			  which reflect the following:
--#			      - State is a boolean flag as to whether the
--#				entry is active or not.  1=on, 0=off.
--#			      - Destination is hostname or IP of system to
--#				forward the event to
--#			      - Method is the means by which it will be
--#				forwarded.  A keyword, e.g., SNMP
--#  eventMouseOverText : Text to be displayed on MouseOver event, if
--#			  the event is displayed in the browser and
--#			  the operator needs additional info.
--#  eventLog		: Flag indicating if the event is to be logged, set
--#			  from the 'dest' attribute on the incoming event
--#                       Y = log, N = do not log
--#  eventDisplay	: Flag indicating if the event is to be displayed, set
--#			  from the 'dest' attribute on the incoming event
--#                       Y = display, N = do not display
--#  eventAckUser	: The user who acknowledged this event.  If
--#			  null, then this event has not been acknowledged.
--#  eventAckTime	: The time this event was acknowledged.
--#  alarmID : If this event is configured for alarmReduction, the alarmId
--#            of the reduced event will set in this column
--#
--##################################################################

create table events (
	eventID			integer not null,
	eventUei		varchar(256) not null,
	nodeID			integer,
	eventTime		timestamp with time zone not null,
	eventHost		varchar(256),
	eventSource		varchar(128) not null,
	ipAddr			text,
	eventDpName		varchar(12) not null,
	eventSnmphost		varchar(256),
	serviceID		integer,
	eventSnmp		varchar(256),
	eventParms		text,
	eventCreateTime		timestamp with time zone not null,
	eventDescr		text,
	eventLoggroup		varchar(32),
	eventLogmsg		text,
	eventSeverity		integer not null,
	eventPathOutage		varchar(1024),
	eventCorrelation	varchar(1024),
	eventSuppressedCount	integer,
	eventOperInstruct	varchar(1024),
	eventAutoAction		varchar(256),
	eventOperAction		varchar(256),
	eventOperActionMenuText	varchar(64),
	eventNotification	varchar(128),
	eventTticket		varchar(128),
	eventTticketState	integer,
	eventForward		varchar(256),
	eventMouseOverText	varchar(64),
	eventLog		char(1) not null,
	eventDisplay		char(1) not null,
    ifIndex             integer,
	eventAckUser		varchar(256),
	eventAckTime		timestamp with time zone,
	alarmID			integer,

	constraint pk_eventID primary key (eventID)
);

create index events_uei_idx on events(eventUei);
create index events_nodeid_idx on events(nodeID);
create index events_ipaddr_idx on events(ipaddr);
create index events_serviceid_idx on events(serviceID);
create index events_time_idx on events(eventTime);
create index events_severity_idx on events(eventSeverity);
create index events_log_idx on events(eventLog);
create index events_display_idx on events(eventDisplay);
create index events_ackuser_idx on events(eventAckUser);
create index events_acktime_idx on events(eventAckTime);
create index events_alarmid_idx on events(alarmID);
create index events_nodeid_display_ackuser on events(nodeid, eventdisplay, eventackuser);

--########################################################################
--#
--# outages table -- This table maintains a record of outage periods for
--#                  given services on specific interfaces.
--#
--# This table provides the following information:
--#
--#  outageID          : Unique integer identifier for the outage
--#  svcLostEventID    : ID of the event that caused the outage. Will be
--#                      a non-null value when a new outage is inserted
--#                      but might be null in case of an opennms upgrade
--#  svcRegainedEventID: ID of the event that cleared the outage
--#  nodeID            : Unique integer identifier for node
--#  ipAddr            : IP Address of node's interface
--#  serviceID         : Unique integer identifier of service/poller package
--#  ifLostService     : Time of lost service event
--#  ifRegainedService : Time of regained service event
--#  suppressTime 	   : Time to suppress the outage
--#  suppressedBy	   : The suppressor
--#
--########################################################################

create table outages (
	outageID		integer not null,
	svcLostEventID		integer,
	svcRegainedEventID	integer,
	nodeID			integer not null,
	ipAddr			text not null,
	serviceID		integer not null,
	ifLostService		timestamp with time zone not null,
	ifRegainedService	timestamp with time zone,
	suppressTime    	timestamp with time zone,
	suppressedBy		varchar(256),
	ifServiceId		INTEGER not null,

	constraint pk_outageID primary key (outageID),
	constraint fk_eventID1 foreign key (svcLostEventID) references events (eventID) ON DELETE CASCADE,
	constraint fk_eventID2 foreign key (svcRegainedEventID) references events (eventID) ON DELETE CASCADE,
	constraint fk_nodeID4 foreign key (nodeID) references node (nodeID) ON DELETE CASCADE,
	constraint fk_serviceID2 foreign key (serviceID) references service (serviceID) ON DELETE CASCADE,
	CONSTRAINT ifServices_fkey1 FOREIGN KEY (nodeId, ipAddr, serviceId) REFERENCES ifServices (nodeId, ipAddr, serviceId) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT ifServices_fkey2 FOREIGN KEY (ifServiceId) REFERENCES ifServices (id) ON DELETE CASCADE
);

create index outages_nodeid_ipaddr_svc_idx on outages(nodeID, ipAddr, serviceId);
create index outages_svclostid_idx on outages(svcLostEventID);
create index outages_svcregainedid_idx on outages(svcRegainedEventID);
create index outages_nodeid_idx on outages(nodeID);
create index outages_serviceid_idx on outages(serviceID);
create index outages_ipaddr_idx on outages(ipaddr);
create index outages_regainedservice_idx on outages(ifRegainedService);
create index outages_ifServivceId_idx on outages(ifServiceId);

--########################################################################
--#
--# vulnerabilities table -- This table maintains a record of vulnerabilites
--#                          that have been detected on target IP addresses.
--#
--# This table provides the following information:
--#
--#  vulnerabilityID   : Unique integer identifier for the outage
--#  nodeID            : Unique integer identifier for node
--#  ipAddr            : IP Address of node's interface
--#  serviceID         : Unique integer identifier of service/poller package
--#
--#  creationTime      : Initial creation time of the vulnerability
--#  lastAttemptTime   : Last time that an attempt was made to scan for
--#                      this vulnerability
--#  lastScanTime      : Most recent successful scan time
--#  resolvedTime      : Time after which the vulnerability was no longer
--#                      detected
--#
--#  severity          : Severity of the vulnerability (identical to event
--#                      severities
--#  pluginID          : ID number of the plugin that produced the vulnerability
--#  pluginSubID       : Specific vulnerability type generated by the plugin
--#  logmsg            : Terse description of vulnerability (usually
--#                      the plugin name plus short description)
--#  descr             : Verbose description of vulnerability
--#  port              : Port that the vulnerability affects
--#  protocol          : Network protocol of the attack (TCP, UDP, ICMP)
--#
--########################################################################

create table vulnerabilities (
	vulnerabilityID		integer not null,
	nodeID			integer,
	ipAddr			text,
	serviceID		integer,
	creationTime		timestamp with time zone not null,
	lastAttemptTime		timestamp with time zone not null,
	lastScanTime		timestamp with time zone not null,
	resolvedTime		timestamp with time zone,
	severity		integer not null,
	pluginID		integer not null,
	pluginSubID             integer not null,
	logmsg                  text,
	descr			text,
	port			integer,
	protocol		varchar(32),
	cveEntry		varchar(255),

	constraint pk_vulnerabilityID primary key (vulnerabilityID)
);

create index vulnerabilities_nodeid_idx on vulnerabilities(nodeID);
create index vulnerabilities_ipaddr_idx on vulnerabilities(ipAddr);
create index vulnerabilities_severity_idx on vulnerabilities(severity);
create index vulnerabilities_port_idx on vulnerabilities(port);
create index vulnerabilities_protocol_idx on vulnerabilities(protocol);

--########################################################################
--#
--# vulnPlugins table -- This table contains a list of information about
--#                      Nessus plugins that are in use by the nessusd
--#                      daemons that are being used by vulnscand.
--#
--# This table provides the following information:
--#
--#  pluginID          : Plugin ID number (from Nessus)
--#  pluginSubID       : Specific vulnerability type within the plugin
--#  name              : Short name of the plugin
--#  category          : Category of the plugin's behavior (scanner,
--#                      attack, etc)
--#  copyright         : Copyright notice for the plugin
--#  descr             : Verbose description of vulnerability
--#  summary           : Short description of plugin behavior
--#  family            : User-comprehensible type of attack (CGI abuses,
--#                      Backdoors, etc)
--#  version           : Version of the plugin code
--#  cveEntry          : CVE entry associated with the vulnerability
--#                      that this plugin tests
--#  md5               : 128-bit hex MD5 checksum of the plugin that
--#                      can be used to detect changes in the plugin code
--#
--########################################################################

create table vulnPlugins (
        pluginID                integer not null,
        pluginSubID             integer not null,
        name                    varchar(128),
        category                varchar(32),
        copyright               varchar(128),
        descr                   text,
        summary                 varchar(256),
        family                  varchar(32),
        version                 varchar(32),
        cveEntry                varchar(255),
        md5                     varchar(32)
);

--#  This constraint not understood installer
--#        CONSTRAINT pk_vulnplugins PRIMARY KEY (pluginID,pluginSubID));
--#

create unique index vulnplugins_plugin_idx on vulnPlugins(pluginID, pluginSubID);

--########################################################################
--# notification table - Contains information on acknowleged and outstanding
--#                      pages listed by user/groups
--#
--# This table contains the following fields:
--#
--# textMsg     : The message being sent in the page.
--# numericMsg  : The message being sent to a numeric pager
--# notifyID    : The primary key of this row, populated with the value from
--#               the notifyNxtId sequence.
--# pageTime    : A timestamp of when the page was originally sent.
--# respondTime : A timestamp of when the page was acknowleged. A null in this
--#               field means that the page has not been answered yet.
--# answeredBy  : The user id of the user that answered the page, set the same
--#               for all rows with the same groupId field.
--# nodeId      : The id of the node that has the problem
--# interfaceId : The id of the interface on the node that has the problem
--# serviceID   : The id of the service on the interface that has the problem
--# eventID     : The primary key of the event that spawned the notification
--# eventUEI    : The uei of the event that spawned the notification, placed here
--#               for speed of lookup as notifications are processed.
--#
--########################################################################

create table notifications (
       textMsg      text not null,
       subject      text,
       numericMsg   varchar(256),
       notifyID	    integer not null,
       pageTime     timestamp with time zone,
       respondTime  timestamp with time zone,
       answeredBy   varchar(256),
       nodeID	    integer,
       interfaceID  varchar(16),
       serviceID    integer,
       queueID		varchar(256),
       eventID      integer,
       eventUEI     varchar(256) not null,
       notifConfigName	varchar(63),

       constraint pk_notifyID primary key (notifyID),
       constraint fk_nodeID7 foreign key (nodeID) references node (nodeID) ON DELETE CASCADE,
       constraint fk_eventID3 foreign key (eventID) references events (eventID) ON DELETE CASCADE
);

create index notifications_nodeid_idx on notifications(nodeid);
create index notifications_ipaddr_idx on notifications(interfaceID);
create index notifications_serviceid_idx on notifications(serviceID);
create index notifications_eventid_idx on notifications(eventID);
create index notifications_respondtime_idx on notifications(respondTime);
create index notifications_answeredby_idx on notifications(answeredBy);
create index notifications_eventuei_idx on notifications (eventuei);

--########################################################################
--#
--# This table contains the following fields:
--# id			: ID column for the table
--# userID      : The user id of the person being paged, from the users.xml
--#               file.
--# notifyID    : The index of the row from the notification table.
--# notifyTime	: The timestamp of when the notification was sent
--# media       : A string describing the type of contact being made, ie text
--#               page, numeric page, email, etc...
--# contactInfo : A field for storing the information used to contact the user,
--#               e.g. an email address, the phone number and pin of the pager...
--# autonotify	: A character to determine how auto acknowledge is handled for
--#               this entry
--#
--########################################################################

create table usersNotified (
		id				integer not null, 
        userID          varchar(256) not null,
        notifyID        integer,
        notifyTime      timestamp with time zone,
        media           varchar(32),
        contactinfo     varchar(64),
        autonotify      char(1),

	constraint pk_userNotificationID primary key (id),
	constraint fk_notifID2 foreign key (notifyID) references notifications (notifyID) ON DELETE CASCADE
);

create index userid_notifyid_idx on usersNotified(userID, notifyID);

--#################################
--# This table contains memos used by alarms to represent StickyMemos and Journal / ReductionKeyMemos
create table memos (
  id integer NOT NULL,
  created timestamp with time zone,
  updated timestamp with time zone,
  author character varying(256),
  body text,
  reductionkey character varying(256),
  type character varying(64),
  CONSTRAINT memos_pkey PRIMARY KEY (id)
);
--########################################################################
--#
--# This table contains the following fields:
--# alarmID     : The id created from the alarmsNxtId sequence.
--# eventUei    : A reference to the eventUei that created this alarm.
--# nodeID      : A reference to the node represented by this alarm.
--# ipAddr      : IP Address of node's interface
--# serviceID   : A reference to the service represented by the alarm.
--# reductionKey: Used with nodeID and serviceID to match an event and
--#               increment the counter column.  Set by configuring the
--#               optional alarm-data elment in the eventConf.xml file.
--# alarmType   : Customizable column designed for use in automations and
--#               can be set in the eventConf.xml file by configuring the
--#               optional alarm-data element.
--# counter     : Incremented by the AlarmWriter instead of inserting
--#               a new row when matched node, service, and reductionKey
--# severity    : Severity of the Alarm... Initially set by the event
--#               can be changed with SQL update.
--# lastEventID : A reference to the event table with the ID of the last
--#               matching event (typically node, service, reductionkey)
--# firstEventTime: timestamp of the first event matching this alarm
--# lastEventTime: timestamp of the last event matching this alarm
--# description : description from the event
--# logMsg      : the logmsg from the event
--# ifIndex      : the ifindex from the event
--# operInstruct: the operator instructions from the event
--# tticketID   : helpdesk integration field
--# tticketState: helpdesk integration field
--# mouseOverTest: flyOverText for the webUI
--# suppressedUntil: used to suppress display an alarm until
--#                : timestamp time is reached
--# suppressedUser : user that suppressed alarm
--# suppressedTime : time the alarm was suppressed
--# alarmAckUser : user that acknowledged the alarm
--# alarmAckTime : time user Ack'd the alarm
--# stickymemo  : reference to the memo table
--########################################################################

create table alarms (
    alarmID                 INTEGER, CONSTRAINT pk_alarmID PRIMARY KEY (alarmID),
    eventUei                VARCHAR(256) NOT NULL,
    dpName                  VARCHAR(12) NOT NULL,
    nodeID                  INTEGER, CONSTRAINT fk_alarms_nodeid FOREIGN KEY (nodeID) REFERENCES node (nodeID) ON DELETE CASCADE,
    ipaddr                  VARCHAR(39),
    serviceID               INTEGER,
    reductionKey            VARCHAR(256),
    alarmType               INTEGER,
    counter                 INTEGER NOT NULL,
    severity                INTEGER NOT NULL,
    lastEventID             INTEGER, CONSTRAINT fk_eventIDak2 FOREIGN KEY (lastEventID)  REFERENCES events (eventID) ON DELETE CASCADE,
    firstEventTime          timestamp with time zone,
    lastEventTime           timestamp with time zone,
    firstAutomationTime     timestamp with time zone,
    lastAutomationTime      timestamp with time zone,
    description             text,
    logMsg                  text,
    operInstruct            VARCHAR(1024),
    tticketID               VARCHAR(128),
    tticketState            INTEGER,
    mouseOverText           VARCHAR(64),
    suppressedUntil         timestamp with time zone,
    suppressedUser          VARCHAR(256),
    suppressedTime          timestamp with time zone,
    alarmAckUser            VARCHAR(256),
    alarmAckTime            timestamp with time zone,
    managedObjectInstance   VARCHAR(512),
    managedObjectType       VARCHAR(512),
    applicationDN           VARCHAR(512),
    ossPrimaryKey           VARCHAR(512),
    x733AlarmType           VARCHAR(31),
    x733ProbableCause       INTEGER default 0 not null,
    qosAlarmState           VARCHAR(31),
    ifIndex                 INTEGER,
    clearKey                VARCHAR(256),
    eventParms              text,
    stickymemo              INTEGER, CONSTRAINT fk_stickyMemo FOREIGN KEY (stickymemo) REFERENCES memos (id) ON DELETE CASCADE
);

CREATE INDEX alarm_uei_idx ON alarms(eventUei);
CREATE INDEX alarm_nodeid_idx ON alarms(nodeID);
CREATE UNIQUE INDEX alarm_reductionkey_idx ON alarms(reductionKey);
CREATE INDEX alarm_clearkey_idx ON alarms(clearKey);
CREATE INDEX alarm_reduction2_idx ON alarms(alarmID, eventUei, dpName, nodeID, serviceID, reductionKey);
CREATE INDEX alarm_app_dn ON alarms(applicationDN);
CREATE INDEX alarm_oss_primary_key ON alarms(ossPrimaryKey);
CREATE INDEX alarm_eventid_idx ON alarms(lastEventID);
CREATE INDEX alarm_lasteventtime_idx on alarms(lasteventtime);
CREATE INDEX alarm_firstautomationtime_idx on alarms(firstautomationtime);
CREATE INDEX alarm_lastautomationtime_idx on alarms(lastautomationtime);

--########################################################################
--#
--# Use this table to add additional custom data about an alarm... somewhat
--# usefull with automations and will be viewable/editable in the alarm
--# details WebUI page.
--#
--# This table contains the following fields:
--# alarmID     : The id created from the alarmsNxtId sequence.
--# attribute   : The custom attribute name
--# attributeValue : The custom attribute value
--########################################################################

CREATE TABLE alarm_attributes (
    alarmID         INTEGER, CONSTRAINT fk_alarmID1 FOREIGN KEY (alarmID) REFERENCES alarms (alarmID) ON DELETE CASCADE,
    attributeName   VARCHAR(63),
    attributeValue  VARCHAR(255)
);

CREATE INDEX alarm_attributes_idx ON alarm_attributes(alarmID);
CREATE UNIQUE INDEX alarm_attributes_aan_idx ON alarm_attributes(alarmID, attributeName);

--# This constraint not understood by installer
--#        CONSTRAINT pk_usersNotified PRIMARY KEY (userID,notifyID) );
--#
--########################################################################
--# asset table - Contains inventory and other user-entered information
--#                     for nodes
--#
--# This table contains the following fields:
--#
--# nodeID           : The node id for the node this asset information belongs.
--# category         : A broad idea of what this asset does (examples are
--#                    desktop, printer, server, infrastructure, etc.).
--# manufacturer     : Name of the manufacturer of this asset.
--# vendor           : Vendor from whom this asset was purchased.
--# modelNumber      : The model number of this asset.
--# serialNumber     : The serial number of this asset.
--# description      : A free-form description.
--# circuitId        : The electrical/network circuit this asset connects to.
--# assetNumber      : A business-specified asset number.
--# operatingSystem  : The operating system, if any.
--# rack             : For servers, the rack it is installed in.
--# slot             : For servers, the slot in the rack it is installed in.
--# port             : For servers, the port in the slot it is installed in.
--# region           : A broad geographical or organizational area.
--# division         : A broad geographical or organizational area.
--# department       : The department this asset belongs to.
--# address1         : Add…