drop table if exists node cascade;
drop table if exists categories cascade;
drop table if exists category_node cascade;
drop table if exists ipinterface cascade;
drop table if exists ifservices cascade;
drop table if exists service cascade;

create table node (
	nodeId          serial not null,
	nodeLabel       varchar,

	constraint pk_nodeId primary key (nodeId)
);
insert into node values (1, 'node1');
insert into node values (2, 'node2');
insert into node values (3, 'node3');
insert into node values (4, 'node4');

create table ipInterface (
	id              serial not null,
	nodeId          integer not null,
	ipAddr          varchar(16) not null,

	constraint ipinterface_pkey PRIMARY KEY (id),
	constraint fk_nodeId1 foreign key (nodeId) references node ON DELETE CASCADE
);

insert into ipInterface values (1,  1, '192.168.1.1');
insert into ipInterface values (2,  1, '192.168.1.2');
insert into ipInterface values (3,  1, '192.168.1.3');
insert into ipInterface values (4,  2, '192.168.2.1');
insert into ipInterface values (5,  2, '192.168.2.2');
insert into ipInterface values (6,  2, '192.168.2.3');
insert into ipInterface values (7,  3, '192.168.3.1');
insert into ipInterface values (8,  3, '192.168.3.2');
insert into ipInterface values (9,  3, '192.168.3.3');
insert into ipInterface values (10, 4, '192.168.4.1');
insert into ipInterface values (11, 4, '192.168.4.2');
insert into ipInterface values (12, 4, '192.168.4.3');

create table categories (
	categoryId              serial not null,
	categoryName            varchar(64) not null,

	constraint category_pkey primary key (categoryId)
);
create unique index category_idx ON categories(categoryName);

insert into categories values (1, 'DEV_AC');
insert into categories values (2, 'IMP_mid');
insert into categories values (3, 'OPS_Online');
insert into categories values (4, 'some category');

create table category_node (
	categoryId              integer,
	nodeId                  integer,

	constraint categoryid_fkey1 foreign key (categoryId) references categories (categoryId) ON DELETE CASCADE,
	constraint nodeid_fkey1 foreign key (nodeId) references node ON DELETE CASCADE
);

insert into category_node values (1, 1);
insert into category_node values (2, 1);
insert into category_node values (3, 1);
insert into category_node values (2, 2);
insert into category_node values (3, 3);
insert into category_node values (1, 4);

create table service (
	serviceID               serial not null,
	serviceName             varchar(32) not null,

	constraint pk_serviceID primary key (serviceID)
);

insert into service values (1, 'ICMP');
insert into service values (2, 'SNMP');
insert into service values (3, 'HTTP');

create table ifServices (
	id              serial not null,
	nodeID          integer not null,
	ipAddr          varchar(16) not null,
	serviceID       integer not null,
	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_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);

insert into ifservices values (1,  1, '192.168.1.1', 1, 1);
insert into ifservices values (2,  1, '192.168.1.1', 2, 1);
insert into ifservices values (3,  1, '192.168.1.2', 1, 2);
insert into ifservices values (4,  1, '192.168.1.2', 3, 2);
insert into ifservices values (5,  1, '192.168.1.3', 1, 3);
insert into ifservices values (6,  2, '192.168.2.1', 1, 4);
insert into ifservices values (7,  2, '192.168.2.1', 2, 4);
insert into ifservices values (8,  2, '192.168.2.2', 1, 5);
insert into ifservices values (9,  2, '192.168.2.2', 3, 5);
insert into ifservices values (10, 2, '192.168.2.3', 1, 6);
insert into ifservices values (11, 3, '192.168.3.1', 1, 7);
insert into ifservices values (12, 3, '192.168.3.1', 2, 7);
insert into ifservices values (13, 3, '192.168.3.2', 1, 8);
insert into ifservices values (14, 3, '192.168.3.2', 3, 8);
insert into ifservices values (15, 3, '192.168.3.3', 1, 9);


--# SHOULD return:
--#    ipaddr    
--# -----------
--#  192.168.1.1
--# (1 row)
--#
--# But on 8.4, returns no rows

SELECT DISTINCT ipInterface.ipAddr FROM ipInterface
	JOIN node ON (ipInterface.nodeID = node.nodeID)
	JOIN ifServices ON (ipInterface.id = ifServices.ipInterfaceId)
	JOIN service ON (ifServices.serviceID = service.serviceID)
WHERE
	(
		node.nodeID IN (
			SELECT category_node.nodeID FROM category_node, categories
			WHERE categories.categoryID = category_node.categoryID
			AND categories.categoryName = 'IMP_mid'
		)
	) AND
	(
		node.nodeID IN (
			SELECT category_node.nodeID FROM category_node, categories
			WHERE categories.categoryID = category_node.categoryID
			AND categories.categoryName = 'DEV_AC'
		)
	) AND
	(
		node.nodeID IN (
			SELECT category_node.nodeID FROM category_node, categories
			WHERE categories.categoryID = category_node.categoryID
			AND categories.categoryName = 'OPS_Online'
		)
	) AND
	(node.nodeId = 1) AND
	(ipInterface.ipAddr = '192.168.1.1') AND
	(service.serviceName = 'ICMP')
LIMIT 1;