CREATE TABLE netdevs(
	ip		VARCHAR(256)
			PRIMARY KEY,

	dev_type	INT4
			NOT NULL,

	name		VARCHAR(256)
			NOT NULL,

	community	VARCHAR(256)
			NOT NULL,

	first_found	INT8
			DEFAULT '0'
			NOT NULL,

	last_found	INT8
			DEFAULT '0'
			NOT NULL
) WITHOUT OIDS;

CREATE TABLE interfaces(
	netdev_ip	VARCHAR(256)
			NOT NULL
			REFERENCES netdevs(ip)
				ON DELETE CASCADE
				ON UPDATE CASCADE,

	ifindex		INT4
			NOT NULL,

	ifinternal	INT4,

	description	VARCHAR(256),

	alias		VARCHAR(256),

	has_netdev	INT4,

	PRIMARY KEY(netdev_ip, ifindex)
) WITHOUT OIDS;

CREATE TABLE macs(
	mac		VARCHAR(256)
			NOT NULL,

	netdev_ip	VARCHAR(256)
			NOT NULL,

	ifindex		INT4
			NOT NULL,

	PRIMARY KEY (mac,netdev_ip),

	FOREIGN KEY (netdev_ip, ifindex)
		REFERENCES interfaces(netdev_ip, ifindex)
			ON DELETE CASCADE
			ON UPDATE CASCADE
) WITHOUT OIDS;

CREATE TABLE ips(
	ip		VARCHAR(256)
			NOT NULL,

	mac		VARCHAR(256)
			NOT NULL,

	netdev_ip	VARCHAR(256)
			NOT NULL,

	ifindex		INT4
			NOT NULL,

	PRIMARY KEY(ip,mac,netdev_ip),

	FOREIGN KEY(netdev_ip,ifindex)
		REFERENCES interfaces(netdev_ip, ifindex)
			ON DELETE CASCADE
			ON UPDATE CASCADE
) WITHOUT OIDS;

CREATE TABLE vendors(
	prefix		VARCHAR(50)
			PRIMARY KEY
			NOT NULL,

	name		VARCHAR(256)
) WITHOUT OIDS;

CREATE SEQUENCE seq_history;

CREATE TABLE history(
	id		INT4
			PRIMARY KEY
			DEFAULT NEXTVAL('seq_history'),

	ts		INT8
			NOT NULL
			DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP),

	last_ts		INT8
			NOT NULL
			DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP),

	ip		VARCHAR(256),
--			NOT NULL,

	mac		VARCHAR(256),
--			NOT NULL,

	router_ip	VARCHAR(256),
	router_name	VARCHAR(256),
	router_ifindex	INT4,
	router_ifdescr	VARCHAR(256),
	router_ifalias	VARCHAR(256),
	sw_ip		VARCHAR(256),
	sw_name		VARCHAR(256),
	sw_ifindex	INT4,
	sw_ifdescr	VARCHAR(256),
	sw_ifalias	VARCHAR(256)

--	PRIMARY KEY(ts,ip,mac)
) WITHOUT OIDS;

CREATE TABLE history_state(
	hist_id		INT4
			PRIMARY KEY
			REFERENCES history(id)
				ON DELETE CASCADE
				ON UPDATE CASCADE,

	last_ignore	INT8,

	description	TEXT
) WITHOUT OIDS;

CREATE SEQUENCE seq_history_ignore;
CREATE TABLE history_ignore(
	id		INT4
			PRIMARY KEY
			DEFAULT NEXTVAL('seq_history_ignore'),

	ip		VARCHAR(256),

	mac		VARCHAR(256),

	router_name	VARCHAR(256),

	router_ifindex	VARCHAR(256),

	sw_name		VARCHAR(256),

	sw_ifindex	VARCHAR(256)
) WITHOUT OIDS;

--
-- v2.4.0
-- persistent interfaces
--

CREATE TABLE p_interfaces(
	netdev_ip	VARCHAR(256)
			NOT NULL
			REFERENCES netdevs(ip)
				ON DELETE CASCADE
				ON UPDATE CASCADE,

	ifindex		INT4
			NOT NULL,

	ifinternal	INT4,

	description	VARCHAR(256),

	alias		VARCHAR(256),

-- when this interface was last known to had a netdev
	last_had_netdev	INT8
			DEFAULT '0'
			NOT NULL,
	
-- when this interface was last found.
	ts		INT8
			DEFAULT '0'
			NOT NULL,
	
	PRIMARY KEY(netdev_ip, ifindex)
) WITHOUT OIDS;

CREATE TABLE	reg_persons (
	id		VARCHAR(256)
			PRIMARY KEY,

	fname		VARCHAR(256)
			NOT NULL,

	sname		VARCHAR(256)
			NOT NULL,

	description	TEXT
) WITHOUT OIDS;

CREATE TABLE	reg_ips (
	ip		VARCHAR(256)
			PRIMARY KEY,

	reged_macs	INTEGER
			DEFAULT '0',

	reged_ifs	INTEGER
			DEFAULT '0',

	personid	VARCHAR(256)
			REFERENCES reg_persons(id)
				ON UPDATE CASCADE
				ON DELETE SET NULL,

	shortdesc	VARCHAR(256),

	description	TEXT
) WITHOUT OIDS;

CREATE TABLE	reg_macs (
	mac		VARCHAR(256)
			PRIMARY KEY,

	reged_ips	INTEGER
			DEFAULT '0',

	reged_ifs	INTEGER
			DEFAULT '0',

	personid	VARCHAR(256)
			REFERENCES reg_persons(id)
				ON UPDATE CASCADE
				ON DELETE SET NULL,

	shortdesc	VARCHAR(256),

	description	TEXT
) WITHOUT OIDS;

CREATE TABLE	reg_ifaces(
	netdev_ip	VARCHAR(256),

	ifindex		INT4,

	reged_ips	INTEGER
			DEFAULT '0',

	reged_macs	INTEGER
			DEFAULT '0',

	personid	VARCHAR(256)
			REFERENCES reg_persons(id)
				ON UPDATE CASCADE
				ON DELETE SET NULL,

	shortdesc	VARCHAR(256),

	description	TEXT,

	do_locking	INTEGER
			DEFAULT '0',

	PRIMARY KEY(netdev_ip,ifindex),
	FOREIGN KEY(netdev_ip,ifindex)
		REFERENCES p_interfaces(netdev_ip, ifindex)
			ON DELETE CASCADE
			ON UPDATE CASCADE
) WITHOUT OIDS;

CREATE TABLE	reg_ip2mac (
--	ip		VARCHAR(256)
	ip		INET
			NOT NULL,

	mac		VARCHAR(256)
			NOT NULL,

	PRIMARY KEY (ip,mac)
) WITHOUT OIDS;

CREATE TABLE	reg_ip2iface (
--	ip		VARCHAR(256)
	ip		INET
			NOT NULL,

	netdev_ip	VARCHAR(256)
			NOT NULL,

	ifindex		INT4
			NOT NULL,

	PRIMARY KEY (ip,netdev_ip,ifindex),
	FOREIGN KEY(netdev_ip,ifindex)
		REFERENCES p_interfaces(netdev_ip, ifindex)
			ON DELETE CASCADE
			ON UPDATE CASCADE
) WITHOUT OIDS;

CREATE TABLE	reg_mac2iface (
	mac		VARCHAR(256)
			NOT NULL,

	netdev_ip	VARCHAR(256)
			NOT NULL,

	ifindex		INT4
			NOT NULL,

	PRIMARY KEY (mac,netdev_ip,ifindex),
	FOREIGN KEY(netdev_ip,ifindex)
		REFERENCES p_interfaces(netdev_ip, ifindex)
			ON DELETE CASCADE
			ON UPDATE CASCADE
) WITHOUT OIDS;

CREATE TABLE	blacklist (
	mac		VARCHAR(256)
			NOT NULL
			PRIMARY KEY,
--			REFERENCES reg_macs(mac)
--				ON DELETE CASCADE
--				ON UPDATE CASCADE,

	ts		INT8
			NOT NULL
			DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP),

	reason		TEXT
-- ,

--	PRIMARY KEY (mac,ts)
) WITHOUT OIDS;

CREATE SEQUENCE seq_ml_log;

CREATE TABLE	ml_log(
	id		INT4
			PRIMARY KEY
			DEFAULT NEXTVAL('seq_ml_log'),

	ts		INT8
			NOT NULL
			DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP),

	severity	INT4
			NOT NULL
			DEFAULT 10,

	msg		TEXT
			NOT NULL
) WITHOUT OIDS;

CREATE TABLE	ml_devs(
	netdev_ip	VARCHAR(256)
			PRIMARY KEY
			REFERENCES netdevs(ip)
				ON DELETE CASCADE
				ON UPDATE CASCADE,

	uname		VARCHAR(256),

	t_pass		VARCHAR(256),

	en_pass		VARCHAR(256),

	do_locking	INT4
			DEFAULT 0,

	last_update	INT8,

	last_try	INT8
) WITHOUT OIDS;



CREATE VIEW view_macs AS
	SELECT	macs.mac		AS mac,
		macs.netdev_ip		AS sw_ip,
		macs.ifindex		AS sw_ifindex,
		netdevs.name		AS sw_name,
		interfaces.description	AS sw_ifdescr,
		interfaces.alias	AS sw_ifalias

		FROM macs, interfaces, netdevs

		WHERE	macs.netdev_ip = netdevs.ip AND
			macs.netdev_ip = interfaces.netdev_ip AND
			macs.ifindex = interfaces.ifindex;

CREATE VIEW view_ips AS
	SELECT	ips.ip			AS ip,
		ips.mac			AS mac,
		netdevs.name		AS router_name,
		netdevs.ip		AS router_ip,
		interfaces.ifindex	AS router_ifindex,
		interfaces.description	AS router_ifdescr,
		interfaces.alias	AS router_ifalias

		FROM ips, netdevs, interfaces

		WHERE	ips.netdev_ip = netdevs.ip AND
			interfaces.netdev_ip = netdevs.ip AND
			ips.ifindex = interfaces.ifindex;

CREATE VIEW view_all_pre AS
	SELECT	i.ip			AS ip,
		mac			AS mac,
		i.router_ip		AS router_ip,
		i.router_name		AS router_name,
		i.router_ifindex	AS router_ifindex,
		i.router_ifdescr	AS router_ifdescr,
		i.router_ifalias	AS router_ifalias,
		m.sw_ip			AS sw_ip,
		m.sw_name		AS sw_name,
		m.sw_ifindex		AS sw_ifindex,
		m.sw_ifdescr		AS sw_ifdescr,
		m.sw_ifalias		AS sw_ifalias

		FROM view_ips i FULL JOIN view_macs m
			USING(mac);

CREATE VIEW view_all AS
	SELECT ip, mac, router_ip, router_name, router_ifindex, router_ifdescr,
		router_ifalias, sw_ip, sw_name, sw_ifindex, sw_ifdescr, sw_ifalias,
		vendors.name AS mac_name
		FROM view_all_pre LEFT OUTER JOIN vendors
			ON vendors.prefix = substr(mac,1,8);

CREATE VIEW view_history AS
	SELECT id, ts, last_ts, ip, mac, router_ip, router_name, router_ifindex,
		router_ifdescr, router_ifalias, sw_ip, sw_name, sw_ifindex, sw_ifdescr,
		sw_ifalias, vendors.name AS mac_name
		FROM history LEFT OUTER JOIN vendors
			ON vendors.prefix = substr(mac,1,8);

CREATE VIEW view_history_not_ignored AS
	SELECT * FROM view_history h 
		WHERE NOT EXISTS
			( SELECT * FROM history_ignore i
				WHERE ( i.router_name IS NULL OR i.router_name='' OR i.router_name=h.router_name ) AND
					( i.router_ifindex IS NULL OR i.router_ifindex='' OR i.router_ifindex=h.router_ifindex ) AND
					( i.sw_name IS NULL OR i.sw_name='' OR i.sw_name=h.sw_name ) AND
					( i.sw_ifindex IS NULL OR i.sw_ifindex='' OR i.sw_ifindex=h.sw_ifindex ) AND
					( i.ip IS NULL OR i.ip='' OR i.ip=substring(h.ip from 1 for octet_length(i.ip))) AND
					( i.mac IS NULL OR i.mac='' OR i.mac=substring(h.mac from 1 for octet_length(i.mac))) );

-- Same as view_history_not_ignore but don't include
-- entries with state ignored
CREATE VIEW view_history_not_ignore_state AS
	SELECT * FROM view_history_not_ignored h LEFT JOIN history_state hs
			ON h.id=hs.hist_id
		WHERE NOT EXISTS 
			( SELECT * FROM history_state hs
				WHERE h.id = hs.hist_id AND
					h.last_ts <= hs.last_ignore );

CREATE VIEW view_stated AS
	SELECT * FROM view_history_not_ignored h JOIN history_state hs
			ON  h.id=hs.hist_id;

--CREATE VIEW view_hist AS
--	SELECT ip, mac, sw_ip, sw_ifindex, ts, last_ts FROM history;

-- position change
CREATE VIEW view_hist_ip_mac_cnt AS
	SELECT ip, mac, count(*) FROM
		( SELECT ip, mac, last_ts FROM view_history_not_ignored
			GROUP BY ip, mac, last_ts ) lala
		GROUP BY ip,mac;
-- mac change
CREATE VIEW view_hist_ip_cnt AS
	SELECT ip, count(*) FROM
			( SELECT DISTINCT ip, mac
				FROM view_history_not_ignored ) koko
		GROUP BY ip;

-- ip change
CREATE VIEW view_hist_mac_cnt AS
	SELECT mac, count(*) FROM
			( SELECT DISTINCT ip, mac
				FROM view_history_not_ignored ) koko
		GROUP BY mac;

-- position change (without ignored)
CREATE VIEW view_hist_ip_mac_cnt_state AS
	SELECT ip, mac, count(*) FROM
		( SELECT ip, mac, last_ts FROM view_history_not_ignore_state
			GROUP BY ip, mac, last_ts ) lala
		GROUP BY ip,mac;
-- mac change (without ignored)
CREATE VIEW view_hist_ip_cnt_state AS
	SELECT ip, count(*) FROM
			( SELECT DISTINCT ip, mac
				FROM view_history_not_ignore_state ) koko
		GROUP BY ip;

-- ip change (without ignored)
CREATE VIEW view_hist_mac_cnt_state AS
	SELECT mac, count(*) FROM
			( SELECT DISTINCT ip, mac
				FROM view_history_not_ignore_state ) koko
		GROUP BY mac;

-- view_all with ip and mac registries
--CREATE VIEW view_all_reg AS
--	SELECT ip, a.mac, router_ip, router_name, router_ifindex, router_ifdescr,
--		router_ifalias, sw_ip, sw_name, sw_ifindex, sw_ifdescr, sw_ifalias,
--		mac_name, reg_ip_ip, ip_personid, reged_macs, ip_shortdesc,
--		reg_macs.mac AS reg_mac_mac, reg_macs.personid AS mac_personid,
--		reged_ips, reg_macs.shortdesc AS mac_shortdesc FROM
--			( SELECT view_all.ip, mac, router_ip, router_name, router_ifindex, router_ifdescr,
--				router_ifalias, sw_ip, sw_name, sw_ifindex, sw_ifdescr, sw_ifalias,
--				mac_name, personid AS ip_personid, reged_macs, shortdesc AS ip_shortdesc,
--				reg_ips.ip AS reg_ip_ip
--					FROM view_all LEFT JOIN reg_ips
--						ON view_all.ip=reg_ips.ip ) AS a LEFT JOIN reg_macs
--							ON a.mac=reg_macs.mac;

--CREATE VIEW view_history_reg AS
--	SELECT id, ts, last_ts, ip, a.mac, router_ip, router_name, router_ifindex,
--		router_ifdescr, router_ifalias, sw_ip, sw_name, sw_ifindex, sw_ifdescr,
--		sw_ifalias, mac_name, reg_ip_ip, ip_personid, reged_macs, ip_shortdesc,
--		reg_macs.mac AS reg_mac_mac, reg_macs.personid AS mac_personid,
--		reged_ips, reg_macs.shortdesc AS mac_shortdesc FROM
--			( SELECT id, ts, last_ts, view_history.ip, mac, router_ip, router_name, router_ifindex,
--				router_ifdescr, router_ifalias, sw_ip, sw_name, sw_ifindex, sw_ifdescr,
--				sw_ifalias, mac_name, personid AS ip_personid, reged_macs,
--				shortdesc AS ip_shortdesc, reg_ips.ip AS reg_ip_ip
--					FROM view_history LEFT JOIN reg_ips
--						ON view_history.ip=reg_ips.ip ) AS a LEFT JOIN reg_macs
--							ON a.mac=reg_macs.mac;

CREATE VIEW view_history_reg AS
	SELECT v.ip, v.mac,
		id, ts, last_ts, router_ip, router_name, router_ifindex, router_ifdescr, router_ifalias,
		sw_ip, sw_name, sw_ifindex, sw_ifdescr, sw_ifalias, mac_name,
		i.reged_macs AS ip_reged_macs, i.reged_ifs AS ip_reged_ifs,
		m.reged_ips AS mac_reged_ips, m.reged_ifs AS mac_reged_ifs,
		f.reged_ips AS if_reged_ips, f.reged_macs AS if_reged_macs,
		( SELECT 1 WHERE EXISTS ( SELECT * FROM reg_ip2mac ii WHERE ii.ip>>=INET(v.ip) AND ii.mac=v.mac ) ) AS i2m_assoc,
		( SELECT 1 WHERE EXISTS ( SELECT * FROM reg_ip2iface ii WHERE ii.ip>>=INET(v.ip) AND ii.ifindex=v.sw_ifindex AND ii.netdev_ip=v.sw_ip ) ) AS i2f_assoc,
		( SELECT 1 WHERE EXISTS ( SELECT * FROM reg_mac2iface ii WHERE ii.mac=v.mac AND ii.ifindex=v.sw_ifindex AND ii.netdev_ip=v.sw_ip ) ) AS m2f_assoc,
		( SELECT 1 WHERE i.ip IS NOT NULL ) AS ip_is_reg,
		( SELECT 1 WHERE m.mac IS NOT NULL ) AS mac_is_reg,
		( SELECT 1 WHERE f.netdev_ip IS NOT NULL ) AS if_is_reg,
		i.shortdesc AS ip_shortdesc, m.shortdesc AS mac_shortdesc, f.shortdesc AS if_shortdesc

		FROM view_history v
			LEFT JOIN reg_ips i ON INET(v.ip)<<=INET(i.ip)
			LEFT JOIN reg_macs m ON v.mac=m.mac
			LEFT JOIN reg_ifaces f ON ( v.sw_ip=f.netdev_ip AND v.sw_ifindex=f.ifindex )
			LEFT JOIN reg_ip2mac i2m ON ( INET(v.ip)<<=i2m.ip AND v.mac=i2m.mac )
			LEFT JOIN reg_ip2iface i2f ON ( INET(v.ip)<<=i2f.ip AND i2f.ifindex=v.sw_ifindex AND i2f.netdev_ip=v.sw_ip )
			LEFT JOIN reg_mac2iface m2f ON ( v.mac=m2f.mac AND m2f.ifindex=v.sw_ifindex AND m2f.netdev_ip=v.sw_ip )
;

CREATE VIEW view_all_reg AS
	SELECT v.ip, v.mac,
		router_ip, router_name, router_ifindex, router_ifdescr, router_ifalias,
		sw_ip, sw_name, sw_ifindex, sw_ifdescr, sw_ifalias, mac_name,
		i.reged_macs AS ip_reged_macs, i.reged_ifs AS ip_reged_ifs,
		m.reged_ips AS mac_reged_ips, m.reged_ifs AS mac_reged_ifs,
		f.reged_ips AS if_reged_ips, f.reged_macs AS if_reged_macs,
		( SELECT 1 WHERE EXISTS ( SELECT * FROM reg_ip2mac ii WHERE ii.ip>>=INET(v.ip) AND ii.mac=v.mac ) ) AS i2m_assoc,
		( SELECT 1 WHERE EXISTS ( SELECT * FROM reg_ip2iface ii WHERE ii.ip>>=INET(v.ip) AND ii.ifindex=v.sw_ifindex AND ii.netdev_ip=v.sw_ip ) ) AS i2f_assoc,
		( SELECT 1 WHERE EXISTS ( SELECT * FROM reg_mac2iface ii WHERE ii.mac=v.mac AND ii.ifindex=v.sw_ifindex AND ii.netdev_ip=v.sw_ip ) ) AS m2f_assoc,
		( SELECT 1 WHERE i.ip IS NOT NULL ) AS ip_is_reg,
		( SELECT 1 WHERE m.mac IS NOT NULL ) AS mac_is_reg,
		( SELECT 1 WHERE f.netdev_ip IS NOT NULL ) AS if_is_reg,
		i.shortdesc AS ip_shortdesc, m.shortdesc AS mac_shortdesc, f.shortdesc AS if_shortdesc, 
		f.do_locking AS if_do_locking

		FROM view_all v
			LEFT JOIN reg_ips i ON INET(v.ip)<<=INET(i.ip)
			LEFT JOIN reg_macs m ON v.mac=m.mac
			LEFT JOIN reg_ifaces f ON ( v.sw_ip=f.netdev_ip AND v.sw_ifindex=f.ifindex )
			LEFT JOIN reg_ip2mac i2m ON ( i2m.ip>>=INET(v.ip) AND v.mac=i2m.mac )
			LEFT JOIN reg_ip2iface i2f ON ( i2f.ip>>=INET(v.ip) AND i2f.ifindex=v.sw_ifindex AND i2f.netdev_ip=v.sw_ip )
			LEFT JOIN reg_mac2iface m2f ON ( v.mac=m2f.mac AND m2f.ifindex=v.sw_ifindex AND m2f.netdev_ip=v.sw_ip )
;


CREATE VIEW view_err AS
	SELECT * FROM view_all_reg WHERE
		sw_ip IS NOT NULL AND
		sw_ip <> '' AND
		mac IS NOT NULL AND
		if_do_locking = 1 AND (
		( mac IS NOT NULL AND
			m2f_assoc IS NULL AND
			( mac_reged_ifs = 1 OR
				if_reged_macs = 1 ) ) OR
		( ip IS NOT NULL AND
			i2f_assoc IS NULL AND
			( ip_reged_ifs = 1 OR
				if_reged_ips = 1 ) ) OR
		( ip IS NOT NULL AND
			mac IS NOT NULL AND
			i2m_assoc IS NULL AND
			( ip_reged_macs = 1 OR
				mac_reged_ips = 1 ) ) )
;

CREATE VIEW view_locks AS
	SELECT p.netdev_ip AS sw_ip, p.ifindex AS ifindex,
		p.description AS ifdescr, m2f.mac AS mac
		FROM p_interfaces p, reg_ifaces i
		LEFT JOIN reg_mac2iface m2f ON (
			m2f.netdev_ip = i.netdev_ip AND m2f.ifindex = i.ifindex )
		WHERE p.netdev_ip = i.netdev_ip AND
			p.ifindex = i.ifindex AND
			i.do_locking = '1' AND
			NOT EXISTS (
				SELECT * FROM blacklist bl
					WHERE bl.mac=m2f.mac )
;

-- Views for locating good associations

CREATE VIEW view_t_i2m AS
	SELECT DISTINCT ip, mac
		FROM history h
		WHERE
			(SELECT count(*)
				FROM ( SELECT DISTINCT ip
						FROM history h2
						WHERE mac=h.mac ) AS a
			) = 1 AND
			(SELECT count(*)
				FROM ( SELECT DISTINCT mac
						FROM history h2
						WHERE ip=h.ip ) AS a
			) = 1;

CREATE VIEW view_t_i2m_not AS
	SELECT DISTINCT ip,mac
		FROM history h
	EXCEPT SELECT ip,mac
		FROM view_t_i2m;

CREATE VIEW view_t_i2f AS
	SELECT DISTINCT ip, sw_ip, sw_ifindex
		FROM history h
		WHERE
			(SELECT count(*)
				FROM ( SELECT DISTINCT sw_ip, sw_ifindex
						FROM history h2
						WHERE ip=h.ip ) AS a
			) = 1 AND
			sw_ifindex IS NOT NULL;

CREATE VIEW view_t_i2f_not AS
	SELECT DISTINCT ip, sw_ip, sw_ifindex
		FROM history h
			WHERE sw_ifindex IS NOT NULL
	EXCEPT SELECT ip, sw_ip, sw_ifindex
		FROM view_t_i2f;

CREATE VIEW view_t_m2f AS
	SELECT DISTINCT mac, sw_ip, sw_ifindex
		FROM history h
		WHERE
			(SELECT count(*)
				FROM ( SELECT DISTINCT sw_ip, sw_ifindex
						FROM history h2
						WHERE mac=h.mac ) AS a
			) = 1 AND
			sw_ifindex IS NOT NULL;

CREATE VIEW view_t_m2f_not AS
	SELECT DISTINCT mac, sw_ip, sw_ifindex
		FROM history h
			WHERE sw_ifindex IS NOT NULL
	EXCEPT SELECT mac, sw_ip, sw_ifindex
		FROM view_t_m2f;

CREATE VIEW view_lockable_interfaces AS
	SELECT * FROM netdevs n, p_interfaces p
		WHERE  n.dev_type=2 AND
			n.ip=p.netdev_ip AND
			( p.description LIKE 'Fast%' OR
			p.description LIKE 'Giga%' );

CREATE VIEW view_lock_interfaces AS
	SELECT ip, name, tot_ifs, lockable_ifs, netdev_ifs,
		locked_ifs, uname, t_pass, en_pass,
		do_locking, last_update, last_try
		FROM
		( SELECT ip, name, count(*) AS tot_ifs
			FROM netdevs n, p_interfaces p
			WHERE n.dev_type=2 AND
				n.ip=p.netdev_ip
			GROUP BY ip,name ) AS a
		LEFT JOIN
		( SELECT ip, count(*) AS lockable_ifs
			FROM view_lockable_interfaces v
			GROUP BY ip ) AS b USING(ip)
		LEFT JOIN
		( SELECT ip, count(*) AS netdev_ifs
			FROM view_lockable_interfaces v
			WHERE v.last_had_netdev>0
			GROUP BY ip ) AS c USING(ip)
		LEFT JOIN
		( SELECT ip, count(*) AS locked_ifs
			FROM view_lockable_interfaces v,
				reg_ifaces rif
			WHERE v.netdev_ip=rif.netdev_ip AND
				v.ifindex=rif.ifindex AND
				rif.do_locking=1
			GROUP BY ip) AS d USING(ip)
		LEFT JOIN ml_devs m ON a.ip=m.netdev_ip
;

CREATE VIEW view_lock_interface AS
	SELECT p.netdev_ip, p.ifindex, p.description, p.alias, 
		( SELECT 1 WHERE p.last_had_netdev IS NOT NULL AND p.last_had_netdev > 0 ) AS has_netdev,
		r.do_locking,
		( SELECT 1 WHERE r.ifindex IS NOT NULL ) AS reged_if,
		( SELECT COUNT(DISTINCT mac) FROM macs m WHERE
			m.netdev_ip=p.netdev_ip AND m.ifindex=p.ifindex ) AS last_devs,
		( SELECT COUNT(DISTINCT mac) FROM history h WHERE
			h.sw_ip=p.netdev_ip AND h.sw_ifindex=p.ifindex ) AS hist_devs,
		( SELECT COUNT(*) FROM reg_ip2iface r WHERE
			r.netdev_ip=p.netdev_ip AND r.ifindex=p.ifindex) AS ip_assocs,
		( SELECT COUNT(*) FROM reg_mac2iface r WHERE
			r.netdev_ip=p.netdev_ip AND r.ifindex=p.ifindex) AS mac_assocs

		FROM p_interfaces p
		LEFT JOIN reg_ifaces r
			ON p.ifindex=r.ifindex AND p.netdev_ip=r.netdev_ip;

-- Devices that are candidates for locking
-- is_old == 1 if the device was last set more than 32 hours ago
CREATE VIEW view_ml_tolock AS
	SELECT *,
		( SELECT 1 WHERE
			( ((EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)) - last_try) > 115200) OR
			last_try IS NULL ) AS is_old
		FROM ml_devs
		WHERE ( t_pass IS NOT NULL AND
			en_pass IS NOT NULL AND
			t_pass <> '' AND
			en_pass <> '' ) AND
			do_locking=1;


--
-- ip,mac
--   +--- not exist -> INSERT
--   |
--   +--- exist
--          +--- old sw_ip is NULL -> UPDATE (delete & insert)
--          |
--          +--- old sw_ip is NOT NULL
--                 +-- new sw_ip is NULL -> do nothing
--                 |
--                 +-- new sw_ip is NOT NULL
--                       +-- old sw_ip == new sw_ip AND
--                       |   old sw_ifindex == new sw_ifindex -> UPDATE last_ts
--                       |
--                       +-- old sw_ip != new sw_ip OR
--                           old sw_ifindex != new sw_ifindex -> INSERT
--
--
--v2.4.0: Don't insert entries reported for interfaces which were known
--		to have netdevs (from p_interfaces)
--
--
--
--
--

CREATE OR REPLACE FUNCTION set_history()
	RETURNS INT4
	AS '
-- Delete old entries with unknown switch ip that are
-- now known
		DELETE FROM history
			WHERE history.sw_ip IS NULL AND
				EXISTS ( SELECT * FROM view_all v
						WHERE history.ip = v.ip AND
							history.mac = v.mac AND
							v.sw_ip IS NOT NULL );
-- Delete old entries with unknown ip that are now known
		DELETE FROM history
			WHERE history.ip IS NULL AND
				EXISTS ( SELECT * FROM view_all v
						WHERE history.mac=v.mac AND
							v.ip IS NOT NULL );
							

-- Insert New entries
		INSERT INTO history(ip,mac,router_ip,router_name,router_ifindex,router_ifdescr,router_ifalias,sw_ip,
				sw_name,sw_ifindex,sw_ifdescr,sw_ifalias)
		SELECT ip,mac,router_ip,router_name,router_ifindex,router_ifdescr,router_ifalias,
			sw_ip,sw_name,sw_ifindex,sw_ifdescr,sw_ifalias
			FROM view_all v
			WHERE
-- ip IS NOT NULL AND
--				mac IS NOT NULL AND
-- on interfaces where there was never a netdev
				( NOT EXISTS (
					SELECT last_had_netdev FROM p_interfaces p
						WHERE p.netdev_ip=v.sw_ip AND
							p.ifindex=v.sw_ifindex AND
							p.last_had_netdev > 0 ) ) AND
-- and there is no same history entry
				( NOT EXISTS ( SELECT * FROM history h
							WHERE ( h.ip = v.ip OR ( h.ip IS NULL AND v.ip IS NULL ) ) AND
								( h.mac = v.mac OR ( h.mac IS NULL AND v.mac IS NULL ) ) AND
								( h.sw_ip = v.sw_ip OR ( h.sw_ip IS NULL AND v.sw_ip IS NULL ) ) AND
								( h.sw_ifindex = v.sw_ifindex OR ( h.sw_ifindex IS NULL AND v.sw_ifindex IS NULL ) )
								) ) AND
-- and we know more info than we did (if it not a change)
--	if sw_ip IS NULL and ip/mac already exists dont add it (ip/mac cannot be null in that case)
				( v.sw_ip IS NOT NULL OR
					( NOT EXISTS ( SELECT * FROM history h
						WHERE h.ip = v.ip AND
							h.mac = v.mac )
					) ) AND
--	if ip IS NULL and mac/sw_ip/sw_ifindex already exists dont add it ( mac/sw_ip/sw_ifindex cannot be null )
				( v.ip IS NOT NULL OR
					( NOT EXISTS ( SELECT * FROM history h
						WHERE h.mac = v.mac  AND
							h.sw_ip = v.sw_ip AND
							h.sw_ifindex = v.sw_ifindex ) ) )
				;
-- AND
-- and:
--	there is a history entry with different sw_ip/sw_ifindex
--				( EXISTS ( SELECT * FROM history h
--						WHERE ( h.ip = v.ip OR ( h.ip IS NULL AND v.ip IS NULL ) ) AND
--							( h.mac = v.mac OR ( h.mac IS NULL AND v.mac IS NULL ) ) AND
--							h.sw_ip IS NOT NULL AND
--							( h.sw_ip <> v.sw_ip OR
--							h.sw_ifindex <> v.sw_ifindex ) AND
--							v.sw_ip IS NOT NULL ) );
-- and no same entry exists
-- Update existing entries
		UPDATE history SET last_ts = EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)
			WHERE history.sw_ip IS NOT NULL AND
				EXISTS ( SELECT * FROM view_all v
						WHERE ( v.ip = history.ip OR ( v.ip IS NULL AND history.ip IS NULL ) ) AND
							( v.mac = history.mac OR ( v.mac IS NULL AND history.mac IS NULL ) ) AND
							v.sw_ip = history.sw_ip AND
							v.sw_ifindex = history.sw_ifindex );

		SELECT 1;
	'
	LANGUAGE SQL;

-- Remove data that are older than a week
CREATE OR REPLACE FUNCTION expire_history()
	RETURNS INT4
	AS '
		DELETE FROM history
			WHERE last_ts < ( EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - 604800 );

		SELECT 1;
	'
	LANGUAGE SQL;

-- Remove reg_ip2mac entries that have no reged ip and no reged mac
CREATE OR REPLACE FUNCTION fix_ip2mac_ip_del()
	RETURNS OPAQUE
	AS '
		BEGIN
		DELETE FROM reg_ip2mac
			WHERE	reg_ip2mac.ip<<=INET(OLD.ip) AND
				NOT EXISTS (
					SELECT * FROM reg_macs m
						WHERE reg_ip2mac.mac=m.mac ) AND
				NOT EXISTS (
					SELECT * FROM reg_ips i
						WHERE reg_ip2mac.ip<<=INET(i.ip) );

		RETURN OLD;
		END
	'
	LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION fix_ip2mac_mac_del()
	RETURNS OPAQUE
	AS '
		BEGIN
		DELETE FROM reg_ip2mac
			WHERE	reg_ip2mac.mac=OLD.mac AND
				NOT EXISTS (
					SELECT * FROM reg_ips i
						WHERE reg_ip2mac.ip<<=INET(i.ip) );

		RETURN OLD;
		END
	'
	LANGUAGE 'plpgsql';

-- Generic fix - not called by a trigger
CREATE OR REPLACE FUNCTION fix_ip2mac()
	RETURNS INTEGER
	AS '
		BEGIN
-- TODO: Enable those
		DELETE FROM reg_ip2mac
			WHERE	NOT EXISTS (
					SELECT * FROM reg_ips i
						WHERE reg_ip2mac.ip<<=INET(i.ip) ) AND
				NOT EXISTS (
					SELECT * FROM reg_macs m
						WHERE reg_ip2mac.mac=m.mac );

		RETURN 1;
		END
	'
	LANGUAGE 'plpgsql';


-- When updating reg_ips or reg_macs also update reg_ip2mac
CREATE OR REPLACE FUNCTION fix_ip2mac_ip_upd()
	RETURNS OPAQUE
	AS '
		BEGIN
		UPDATE reg_ip2mac
			SET ip=INET(NEW.ip)
			WHERE ip=OLD.ip;

		RETURN NEW;
		END
	'
	LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION fix_ip2mac_mac_upd()
	RETURNS OPAQUE
	AS '
		BEGIN
		UPDATE reg_ip2mac
			SET mac=NEW.mac
			WHERE mac=OLD.mac;

		RETURN NEW;
		END
	'
	LANGUAGE 'plpgsql';


-- Auto run the above function
CREATE TRIGGER trig_fix_del AFTER DELETE
	ON reg_ips FOR EACH ROW
	EXECUTE PROCEDURE fix_ip2mac_ip_del();

CREATE TRIGGER trig_fix_del AFTER DELETE
	ON reg_macs FOR EACH ROW
	EXECUTE PROCEDURE fix_ip2mac_mac_del();

CREATE TRIGGER trig_fix_upd AFTER UPDATE
	ON reg_ips FOR EACH ROW
	EXECUTE PROCEDURE fix_ip2mac_ip_upd();
	
CREATE TRIGGER trig_fix_upd AFTER UPDATE
	ON reg_macs FOR EACH ROW
	EXECUTE PROCEDURE fix_ip2mac_mac_upd();

--
-- Set last_found when an interface is updated
--

CREATE OR REPLACE FUNCTION set_last_found()
	RETURNS OPAQUE
	AS '
		BEGIN

-- Reset first found if device was not updated for 5 days
		UPDATE netdevs
			SET first_found=EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)
			WHERE ip=NEW.netdev_ip AND
				( EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - last_found ) > 432000;
				
		UPDATE netdevs
			SET last_found=EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)
			WHERE ip=NEW.netdev_ip;

		RETURN NEW;
		END
	'
	LANGUAGE 'plpgsql';

	
CREATE TRIGGER trig_set_last_found
	AFTER INSERT OR UPDATE
	ON interfaces
	FOR EACH ROW EXECUTE PROCEDURE set_last_found();

	
-- update p_interfaces as needed
CREATE OR REPLACE FUNCTION set_piface()
	RETURNS INTEGER
	AS '
		BEGIN

-- delete changed interfaces and interfaces older than 5 days
		DELETE FROM p_interfaces
			WHERE EXISTS (
				SELECT * FROM interfaces i
					WHERE i.netdev_ip = p_interfaces.netdev_ip AND
						i.ifindex = p_interfaces.ifindex AND
						( (
-- If has_netdev changed from 0 to 1 , then this interface has changed
							i.has_netdev=1 AND
							p_interfaces.last_had_netdev<p_interfaces.ts
						) OR
-- If alias chaqnged
--							i.alias<>p_interfaces.alias
--						OR
-- If description changed
							i.description<>p_interfaces.description
						) ) OR
				( (EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - p_interfaces.ts) > 432000) OR
-- If this is not a netdev interface any more (5 days)
				( p_interfaces.last_had_netdev > 10 AND
				( EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - p_interfaces.last_had_netdev ) > 432000 );

-- If alias changed do not perform locking any more
		UPDATE reg_ifaces
			SET do_locking=0
			WHERE EXISTS (
				SELECT * FROM interfaces i, p_interfaces p
					WHERE i.netdev_ip = p.netdev_ip AND
						i.ifindex = p.ifindex AND
						i.netdev_ip = reg_ifaces.netdev_ip AND
						i.ifindex = reg_ifaces.ifindex AND (
						( i.alias IS NULL AND p.alias IS NOT NULL AND p.alias<>'' ) OR
						( p.alias IS NULL AND i.alias IS NOT NULL AND i.alias<>'' ) OR
						( i.alias IS NOT NULL AND p.alias IS NOT NULL AND i.alias <> p.alias ) ) );

-- insert non-existent interfaces into p_interfaces		
		INSERT INTO p_interfaces(netdev_ip, ifindex, ifinternal, description, alias)
			SELECT netdev_ip, ifindex, ifinternal, description, alias
				FROM interfaces i
				WHERE NOT EXISTS (
					SELECT * FROM p_interfaces
						WHERE netdev_ip=i.netdev_ip AND
							ifindex=i.ifindex) AND
					ifinternal<>-1;

-- update last_found
		UPDATE p_interfaces
			SET ts=EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)
			WHERE EXISTS (
				SELECT * FROM interfaces i
					WHERE i.netdev_ip=p_interfaces.netdev_ip AND
						i.ifindex=p_interfaces.ifindex );

-- update last_had_netdev
-- this has to be done AFTER updating last_found to prevent last_had_netdev<last_found
		UPDATE p_interfaces
			SET last_had_netdev=EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)
			WHERE EXISTS (
				SELECT * FROM interfaces i
					WHERE i.netdev_ip=p_interfaces.netdev_ip AND
						i.ifindex=p_interfaces.ifindex AND
						i.has_netdev>0 );

-- update alias
		UPDATE p_interfaces
			SET alias=(
				SELECT alias FROM interfaces i
					WHERE i.netdev_ip=p_interfaces.netdev_ip AND
						i.ifindex=p_interfaces.ifindex )
			WHERE ( alias<>(
				SELECT alias FROM interfaces i
					WHERE i.netdev_ip=p_interfaces.netdev_ip AND
						i.ifindex=p_interfaces.ifindex ) );

		RETURN 1;
		END
	'
	
	LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION update_blacklist()
	RETURNS INTEGER
	AS '
		BEGIN

		INSERT INTO blacklist(mac,reason)
			SELECT mac, \'(auto) MAC-Interface mismatch\' FROM view_err v
				WHERE NOT EXISTS ( SELECT * FROM blacklist b WHERE v.mac=b.mac ) AND
					( v.mac IS NOT NULL AND v.m2f_assoc IS NULL AND
						( v.mac_reged_ifs = 1 OR v.if_reged_macs = 1 ) ); 
--  AND
--					EXISTS ( SELECT * FROM reg_macs r WHERE r.mac=v.mac) );
					
		INSERT INTO blacklist(mac,reason)
			SELECT mac, \'(auto) IP-Interface mismatch\' FROM view_err v
				WHERE NOT EXISTS ( SELECT * FROM blacklist b WHERE v.mac=b.mac ) AND
					( v.ip IS NOT NULL AND v.i2f_assoc IS NULL AND
						( v.ip_reged_ifs = 1 OR v.if_reged_ips = 1 ) );
-- AND
--					EXISTS ( SELECT * FROM reg_macs r WHERE r.mac=v.mac) );
					
		INSERT INTO blacklist(mac,reason)
			SELECT mac, \'(auto) IP-MAC mismatch\' FROM view_err v
				WHERE NOT EXISTS ( SELECT * FROM blacklist b WHERE v.mac=b.mac ) AND
					( v.ip IS NOT NULL AND v.mac IS NOT NULL AND v.i2m_assoc IS NULL AND
						( v.ip_reged_macs = 1 OR v.mac_reged_ips = 1 ) );
-- AND
--					EXISTS ( SELECT * FROM reg_macs r WHERE r.mac=v.mac) );
					

		RETURN 1;

		END
	'
	LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION set_good_assoc()
	RETURNS INTEGER
	AS '
		BEGIN
			INSERT INTO reg_ip2mac(ip,mac)
				SELECT INET(ip),mac FROM (
					SELECT INET(ip) AS ip,mac FROM view_t_i2m
					EXCEPT SELECT ip,mac FROM reg_ip2mac ) AS a;

			INSERT INTO reg_ip2iface(ip, netdev_ip, ifindex)
				SELECT INET(ip), sw_ip, sw_ifindex FROM (
					SELECT INET(ip) AS ip, sw_ip, sw_ifindex FROM view_t_i2f
					EXCEPT SELECT ip, netdev_ip, ifindex FROM reg_ip2iface ) AS a;

			INSERT INTO reg_mac2iface(mac, netdev_ip, ifindex)
				SELECT mac, sw_ip, sw_ifindex FROM (
					SELECT mac, sw_ip, sw_ifindex FROM view_t_m2f
					EXCEPT SELECT mac, netdev_ip, ifindex FROM reg_mac2iface ) AS a;

			RETURN 1;
		END
	'
	LANGUAGE 'plpgsql';

--CREATE OR REPLACE FUNCTION trig_blacklist_add()
--	RETURNS OPAQUE
--	AS '
--		DECLARE
--			CNT INTEGER;
--		BEGIN
--			SELECT INTO CNT COUNT(*) FROM reg_mac2iface
--				WHERE mac=NEW.mac;
--
--			IF CNT = 0 THEN
--				RETURN NULL;
--			ELSE
--				RETURN NEW;
--			END IF;
--		END
--	'
--	LANGUAGE 'plpgsql';

--CREATE TRIGGER trig_blacklist_insert BEFORE INSERT
--	ON blacklist FOR EACH ROW
--	EXECUTE PROCEDURE trig_blacklist_add();

-- Expire log entries older than 7 days
CREATE OR REPLACE FUNCTION expire_ml_log()
	RETURNS INTEGER
	AS '
		DELETE FROM ml_log
			WHERE ts < ( EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - 604800 );

		SELECT 1;
	'
	LANGUAGE SQL;

CREATE OR REPLACE FUNCTION set_ml_upd(VARCHAR(256))
	RETURNS INTEGER
	AS '
		UPDATE ml_devs
			SET last_update = (EXTRACT (EPOCH FROM CURRENT_TIMESTAMP))
			WHERE netdev_ip = $1;

		SELECT 1;
	'
	LANGUAGE SQL;

CREATE OR REPLACE FUNCTION set_ml_try(VARCHAR(256))
	RETURNS INTEGER
	AS '
		UPDATE ml_devs
			SET last_try = (EXTRACT (EPOCH FROM CURRENT_TIMESTAMP))
			WHERE netdev_ip = $1;

		SELECT 1;
	'
	LANGUAGE SQL;