 --########################################################################
 
 create table snmpInterface (
+    id				INTEGER DEFAULT nextval('opennmsNxtId') NOT NULL,
 	nodeID			integer,
 	ipAddr			varchar(16) not null,
 	snmpIpAdEntNetMask	varchar(16),

--########################################################################

create table ifServices (
    id              integer,
    ipInterfaceID   integer not null,
	nodeID			integer,
	ipAddr			varchar(16) not null,
	ifIndex			integer,

	snmpIfOperStatus	integer,
	snmpIfAlias		varchar(256),

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

create index snmpinterface_nodeid_ifindex_idx on snmpinterface(nodeID, snmpIfIndex);
--create index snmpinterface_nodeid_idx on snmpinterface(nodeID);
create index snmpinterface_ipaddr_idx on snmpinterface(ipaddr);

--########################################################################
--# 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.
--#
--# 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
--#  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
--#  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)
--#
--########################################################################

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

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

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);

--########################################################################
--# service Table - Contains a name<->number mapping for services
