-- Creating new database

CREATE DATABASE dhcp;
CREATE USER radius;
GRANT ALL ON DATABASE dhcp TO radius;

-- Connecting to new database
\c dhcp

-- Creating language for database

CREATE OR REPLACE FUNCTION plpgsql_call_handler()
  RETURNS language_handler AS
'$libdir/plpgsql', 'plpgsql_call_handler'
  LANGUAGE 'c' VOLATILE;
ALTER FUNCTION plpgsql_call_handler() OWNER TO radius;

CREATE OR REPLACE FUNCTION plpgsql_validator(oid)
  RETURNS void AS
'$libdir/plpgsql', 'plpgsql_validator'
  LANGUAGE 'c' VOLATILE;
ALTER FUNCTION plpgsql_validator(oid) OWNER TO radius;

CREATE LANGUAGE plpgsql HANDLER plpgsql_call_handler VALIDATOR plpgsql_validator;

-- TABLE: assigned_tmp_ips 

CREATE TABLE assigned_tmp_ips
(
  id serial NOT NULL,
  interface_id bigint,
  client_mac character varying(19),
  attribute character varying(30),
  "value" text,
  start_lease timestamp without time zone,
  int_subnet integer,
  CONSTRAINT assigned_tmp_ips_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE assigned_tmp_ips OWNER TO radius;

CREATE INDEX assigned_tmp_ips_addr_key
  ON assigned_tmp_ips
  USING btree
  (client_mac);

CREATE INDEX assigned_tmp_ips_network_key
  ON assigned_tmp_ips
  USING btree
  (int_subnet);

-- TABLE dhcp_reply

CREATE TABLE dhcp_reply
(
  id serial NOT NULL,
  interface_id integer,
  client_mac character varying(20) NOT NULL,
  attribute character varying(30),
  "value" character varying(1000),
  start_lease timestamp without time zone,
  int_subnet integer,
  CONSTRAINT dhcp_reply_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE dhcp_reply OWNER TO radius;

CREATE INDEX dhcp_reply_addr_key
  ON dhcp_reply
  USING btree
  (client_mac);

CREATE INDEX dhcp_reply_network_key
  ON dhcp_reply
  USING btree
  (int_subnet);

-- TABLE: if_table

CREATE TABLE if_table
(
  id serial NOT NULL,
  interface_id integer NOT NULL,
  attribute character varying(30),
  "value" character varying(1000),
  int_subnet integer,
  CONSTRAINT if_table_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE if_table OWNER TO radius;

CREATE INDEX if_table_interface_key
  ON if_table
  USING btree
  (interface_id);

CREATE INDEX if_table_subnet_key
  ON if_table
  USING btree
  (int_subnet);


-- VIEW: dhcpcheck

CREATE OR REPLACE VIEW dhcpcheck AS 
 SELECT dhcp_reply.id, dhcp_reply.client_mac AS username, 'User-Password'::character varying::character varying(20) AS attribute, ':='::character varying::character varying(5) AS op, 'dhcpuser'::character varying::character varying(127) AS value, dhcp_reply.interface_id
   FROM dhcp_reply
  WHERE dhcp_reply.client_mac IS NOT NULL AND dhcp_reply.client_mac::text <> ''::text AND dhcp_reply.attribute::text = 'You-Address'::text
UNION 
 SELECT '0' AS id, 'unregister'::character varying::character varying(19) AS username, 'User-Password'::character varying::character varying(20) AS attribute, ':='::character varying::character varying(5) AS op, 'dhcpuser'::character varying::character varying(127) AS value, 0 AS interface_id
  ORDER BY id;

ALTER TABLE dhcpcheck OWNER TO radius;

-- VIEW: dhcpreply_full

CREATE OR REPLACE VIEW dhcpreply_full AS 
 SELECT dhcp_reply.id, dhcp_reply.client_mac, dhcp_reply.attribute, dhcp_reply.value, ':=' AS op, dhcp_reply.interface_id, dhcp_reply.int_subnet
   FROM dhcp_reply
  WHERE dhcp_reply.client_mac IS NOT NULL
UNION 
 SELECT assigned_tmp_ips.id, assigned_tmp_ips.client_mac, assigned_tmp_ips.attribute, assigned_tmp_ips.value, ':=' AS op, assigned_tmp_ips.interface_id, assigned_tmp_ips.int_subnet
   FROM assigned_tmp_ips
  ORDER BY id;

ALTER TABLE dhcpreply_full OWNER TO radius;

-- FUNCTION: get_id()

CREATE OR REPLACE FUNCTION get_id(character varying, bigint)
  RETURNS integer AS
$BODY$
DECLARE
mac_addr	ALIAS FOR $1;
subnet		ALIAS FOR $2;
out_id		bigint;
tmp_ip		text;
lease_time	interval;			-- DHCP-lease time
time_now	timestamp with time zone;	-- Current time
delta_time	interval = '15 second';		-- Интервал прибавляемый к значению lease_time для погашения возможного расхождения
						-- между часами клиента и сервера
tmp		bigint;

BEGIN
	time_now := 'now'; -- Get current time
	
	SELECT INTO tmp interface_id FROM dhcpcheck WHERE interface_id = subnet;
	IF(tmp IS NULL)--Если NULL, то выходим, т.к. в базе не имеется адресов для данной подсети.
	THEN
		RETURN -1;
	END IF;
	SELECT INTO lease_time value FROM if_table -- Узнаём значение времени адренды для подсети клиента
		WHERE attribute = 'Lease-Time' AND interface_id = subnet;
	SELECT INTO out_id id FROM dhcpcheck WHERE username = mac_addr and interface_id = subnet;
	
	IF out_id IS NULL -- MAC адрес не зарегистрирован. 
	THEN	-- Незарегистрированный клиент
		SELECT INTO out_id id FROM dhcpcheck WHERE username = 'unregister';		
		SELECT INTO tmp_ip value FROM assigned_tmp_ips -- Возможно ему уже был выдан адрес
			WHERE client_mac = mac_addr AND interface_id = subnet AND attribute = 'You-Address';
		IF tmp_ip IS NULL 
		THEN	-- Новый MAC. Пытаемся выдать ему временный адрес.
			-- Перед этим удаляем все старые аренды
			DELETE FROM assigned_tmp_ips 
				WHERE 
					(start_lease + lease_time + delta_time < time_now)
				AND		(attribute = 'Client-MAC' OR attribute = 'No-Cache');
			UPDATE assigned_tmp_ips SET client_mac = NULL
				WHERE (start_lease + lease_time + delta_time < time_now);

			--Selecting id IP for new temporary client
			SELECT INTO tmp_ip value FROM assigned_tmp_ips
				WHERE interface_id = subnet
				AND client_mac IS NULL
				AND attribute = 'You-Address';
		ELSE	--Else found already registeged temporary user
			UPDATE assigned_tmp_ips SET start_lease = time_now 
				WHERE client_mac = mac_addr 
				AND interface_id = subnet; --Updating lease time
			RETURN out_id;--nothin to do, because this MAC already registered in DB
		END IF;
		
		IF tmp_ip IS NULL --No free leases in DB for this subnet
		THEN
			out_id := -2;
		ELSE	--Adding new temporary client
			INSERT INTO assigned_tmp_ips
				(interface_id, client_mac, attribute, value, start_lease) 
				values(subnet, mac_addr, 'Client-MAC', mac_addr, time_now);
			INSERT INTO assigned_tmp_ips
				(interface_id, client_mac, attribute, value, start_lease) 
				values(subnet, mac_addr, 'No-Cache', 1, time_now);
			UPDATE assigned_tmp_ips SET client_mac = mac_addr, start_lease = time_now
				WHERE value = tmp_ip;
		END IF;
	ELSE	-- MAC адрес зарегистрирован.
			-- Если на данный адрес ранее была выдана аренда из временного диапазона,
			-- то удаляем записи об этой аренде
		UPDATE assigned_tmp_ips SET client_mac = NULL WHERE client_mac = mac_addr AND interface_id = subnet AND attribute = 'You-Address';
		DELETE FROM assigned_tmp_ips WHERE client_mac = mac_addr AND interface_id = subnet;
		-- Вносим информацию об аренде для данного MAC адреса.
		UPDATE dhcp_reply SET start_lease = time_now
			WHERE client_mac = mac_addr;
	END IF;
	RETURN out_id; -- Возвращаем id данного адреса для возможности использования его в секции WHERE запроса на аутентификацию клиента
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION get_id(character varying, bigint) OWNER TO radius;