CREATE TYPE radiusrow AS (
	id integer,
	UserName text,
	Attribute text,
	Value text,
	Op text
); 

CREATE OR REPLACE FUNCTION radcheck (username text) RETURNS SETOF radiusrow AS $$
DECLARE
	result radiusrow;
BEGIN
	IF username ~* '^[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}$' 
	THEN 
		SELECT 0, username, 'Password', '', '=='
		INTO result
		FROM nodes 
		WHERE LOWER(mac) = LOWER(username);
		IF FOUND THEN RETURN NEXT result; END IF;

		SELECT 1, username, 'NAS-Port-Id', netdevices.name, '=='
		INTO result
		FROM nodes n 
		LEFT JOIN netdevices ON (n.netdev = netdevices.id)
		WHERE LOWER(mac) = LOWER(username);
		IF FOUND THEN RETURN NEXT result; END IF; 
	ELSE 
		SELECT 0, username, 'Password', passwd, '=='
		INTO result
		FROM nodes 
		WHERE LOWER(name) = LOWER(username);
		IF FOUND THEN RETURN NEXT result; END IF;

		SELECT 1, username, 'NAS-Port-Id', netdevices.name, '=='
		INTO result
		FROM nodes n 
		LEFT JOIN netdevices ON (n.netdev = netdevices.id)
		WHERE LOWER(n.name) = LOWER(username);
		IF FOUND THEN RETURN NEXT result; END IF; 
	END IF;
	RETURN;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION radreply (username text) RETURNS SETOF radiusrow AS $$
DECLARE
	result radiusrow;
	up integer := 0;
	down integer := 0;
	theip text; themac text; themask text; thename text;	
BEGIN
	IF username ~* '^[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}$' 
	THEN 
		SELECT 0, username, 'NAS-Port-Id', netdevices.name, '=='
		INTO result
		FROM nodes n 
		LEFT JOIN netdevices ON (n.netdev = netdevices.id)
		WHERE LOWER(n.mac) = LOWER(username);
		IF FOUND THEN RETURN NEXT result; END IF; 
	ELSE 
		result.UserName := username;
		result.Op := '==';

		SELECT INET_NTOA(n.ipaddr), n.mac, netdevices.name, 
			(SELECT mask FROM networks WHERE n.ipaddr & INET_ATON(mask) = address) AS mask
		INTO theip, themac, thename, themask
		FROM nodes n
		LEFT JOIN netdevices ON (n.netdev = netdevices.id)
		WHERE LOWER(n.name) = LOWER(username);

		IF theip IS NOT NULL
		THEN 
			result.id := 0;
			result.Attribute := 'Framed-Ip-Address';
			result.Value := theip;
			RETURN NEXT result; 
		END IF; 
		IF themac IS NOT NULL
		THEN 
			result.id := 1;
			result.Attribute := 'Calling-Station-Id';
			result.Value := themac;
			RETURN NEXT result; 
		END IF; 
		IF themask IS NOT NULL
		THEN 
			result.id := 2;
			result.Attribute := 'Framed-IP-Netmask';
			result.Value := themask;
			RETURN NEXT result; 
		END IF; 
--		IF thename IS NOT NULL
--		THEN 
--			result.id := 3;
--			result.Attribute := 'NAS-Port-Id';
--			result.Value := thename;
--			RETURN NEXT result; 
--		END IF; 

		SELECT ROUND(SUM(upceil)/COUNT(DISTINCT n.id)),
			ROUND(SUM(downceil)/COUNT(DISTINCT n.id)) * 2
		INTO up, down
                FROM assignments
                LEFT JOIN tariffs ON (tariffid = tariffs.id) 
		LEFT JOIN nodes n ON (customerid = n.ownerid)
		WHERE EXISTS (SELECT 1 FROM nodes n2
				WHERE LOWER(n2.name) = LOWER(username) 
				AND n.ownerid = n2.ownerid
				AND n2.access = 1)
			AND n.access = 1 
			AND (datefrom <= EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)) OR datefrom = 0) 
			AND (dateto >= EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)) OR dateto = 0);
		
		result.id := 4;
		result.Attribute := 'Mikrotik-Rate-Limit';
		result.Value := up || 'k/' || down || 'k';
		RETURN NEXT result; 
	END IF;
	RETURN;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION radgroupcheck (username text) RETURNS SETOF radiusrow AS $$
DECLARE
	result radiusrow;
BEGIN
	IF username !~* '^[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}$' 
	THEN 
		SELECT 0, 'PPPOE', 'Auth-Type', 'MS-CHAP', ':='
		INTO result;
		RETURN NEXT result;
	END IF;
	RETURN;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION radgroupreply (username text) RETURNS SETOF radiusrow AS $$
DECLARE
	result radiusrow;
BEGIN
	IF username !~* '^[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}$' 
	THEN 
		SELECT 0, 'PPPOE', 'Framed-Protocol', 'PPP', '=='
		INTO result;
		RETURN NEXT result;
---		SELECT 1, 'PPPOE', 'Framed-MTU', '1500', '=='
---		INTO result;
---		RETURN NEXT result;
		SELECT 2, 'PPPOE', 'Service-Type', 'Framed-User', '=='
		INTO result;
		RETURN NEXT result;
		SELECT 3, 'PPPOE', 'Port-Limit', '1', '=='
		INTO result;
    		RETURN NEXT result;
	END IF;
	RETURN;
END;
$$ LANGUAGE plpgsql;
