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

-- Creating tables

CREATE TABLE interfaces
(
  id serial NOT NULL,
  server_addr bigint NOT NULL,
  client_subnet bigint NOT NULL,
  attribute character varying(100) NOT NULL,
  "value" text,
  CONSTRAINT id PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE interfaces OWNER TO radius;

-- Index: client_subnet_index

CREATE INDEX client_subnet_index
  ON interfaces
  USING btree
  (client_subnet);

-- Index: server_addr_index

CREATE INDEX server_addr_index
  ON interfaces
  USING btree
  (server_addr);


-- Table: static_clients

CREATE TABLE static_clients
(
  id serial NOT NULL,
  client_addr character varying(100) NOT NULL,
  client_hw_addr character varying(20) NOT NULL,
  client_subnet bigint NOT NULL,
  server_addr bigint NOT NULL,
  end_lease timestamp without time zone
)
WITHOUT OIDS;
ALTER TABLE static_clients OWNER TO radius;

-- Index: client_subnet_static_index

CREATE INDEX client_subnet_static_index
  ON static_clients
  USING btree
  (client_subnet);

-- Index: server_addr_static_index

CREATE INDEX server_addr_static_index
  ON static_clients
  USING btree
  (server_addr);


-- Table: dynamic_clients

CREATE TABLE dynamic_clients
(
  id serial NOT NULL,
  client_addr character varying(100) NOT NULL,
  client_hw_addr character varying(20),
  client_subnet bigint NOT NULL,
  server_addr bigint NOT NULL,
  end_lease timestamp without time zone
)
WITHOUT OIDS;
ALTER TABLE dynamic_clients OWNER TO radius;

-- Index: client_subnet_dynamic_index

CREATE INDEX client_subnet_dynamic_index
  ON dynamic_clients
  USING btree
  (client_subnet);

-- Index: server_addr_dynamic_index

CREATE INDEX server_addr_dynamic_index
  ON dynamic_clients
  USING btree
  (server_addr);


-- Creating view's

CREATE OR REPLACE VIEW dhcp_reply AS 
 SELECT static_clients.id, static_clients.server_addr, static_clients.client_hw_addr, 'DHCP-Your-IP-Address' AS attribute, static_clients.client_addr AS value, static_clients.end_lease, static_clients.client_subnet
   FROM static_clients
UNION 
 SELECT static_clients.id, static_clients.server_addr, static_clients.client_hw_addr, 'DHCP-Client-Hardware-Address' AS attribute, '0x'::text || translate(static_clients.client_hw_addr::text, ':'::text, ''::text) AS value, static_clients.end_lease, static_clients.client_subnet
   FROM static_clients
UNION 
 SELECT dynamic_clients.id, dynamic_clients.server_addr, dynamic_clients.client_hw_addr, 'DHCP-Your-IP-Address' AS attribute, dynamic_clients.client_addr AS value, dynamic_clients.end_lease, dynamic_clients.client_subnet
   FROM dynamic_clients
UNION 
 SELECT dynamic_clients.id, dynamic_clients.server_addr, dynamic_clients.client_hw_addr, 'DHCP-Client-Hardware-Address' AS attribute, '0x'::text || translate(dynamic_clients.client_hw_addr::text, ':'::text, ''::text) AS value, dynamic_clients.end_lease, dynamic_clients.client_subnet
   FROM dynamic_clients
UNION 
 SELECT dynamic_clients.id, dynamic_clients.server_addr, dynamic_clients.client_hw_addr, 'DHCP-Dynamic-Client' AS attribute, '1'::text AS value, dynamic_clients.end_lease, dynamic_clients.client_subnet
   FROM dynamic_clients;

ALTER TABLE dhcp_reply OWNER TO radius;

-- View: dhcpcheck

CREATE OR REPLACE VIEW dhcpcheck AS 
 SELECT static_clients.id, static_clients.client_hw_addr AS username, 'User-Password' AS attribute, ':=' AS op, 'dhcpuser' AS value, static_clients.server_addr
   FROM static_clients
  WHERE static_clients.client_hw_addr IS NOT NULL
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 server_addr
  ORDER BY 1;

ALTER TABLE dhcpcheck OWNER TO radius;

-- Creating function

CREATE OR REPLACE FUNCTION get_id(character varying, bigint, smallint)
  RETURNS integer AS
$BODY$
DECLARE
hw_addr			ALIAS FOR $1;
server_address  ALIAS FOR $2;
message_type    ALIAS FOR $3;
discover		int2 = 1;
request			int2 = 3;

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 для погашения возможного расхождения
                       -- между часами клиента и сервера
offer_time    interval = '90 second';
tmp        bigint;

BEGIN
   time_now := 'now';	-- Get current time
     SELECT INTO tmp server_addr FROM dhcp_reply WHERE server_addr = server_address;
   IF(tmp IS NULL)		--Если NULL, то выходим, т.к. в базе не имеется адресов для данной подсети.
   THEN
       RETURN -1;
   END IF;

   IF(message_type::int2 = discover)
   THEN
       lease_time = '90 second';
   ELSE IF (message_type::int2 = request)
   THEN
       SELECT INTO lease_time value FROM interfaces -- Узнаём значение времени адренды для подсети клиента
           WHERE attribute = 'DHCP-IP-Address-Lease-Time' AND server_addr = server_address;
     ELSE    -- Не обрабатываемый тип запроса
       RETURN -1;
   END IF;
   END IF;
      SELECT INTO out_id id FROM dhcpcheck WHERE username = hw_addr and server_addr = server_address;
     IF out_id IS NULL -- MAC адрес не зарегистрирован.
   THEN    -- Незарегистрированный клиент
       SELECT INTO out_id id FROM dhcpcheck WHERE username = 'unregister';              SELECT INTO tmp_ip client_addr FROM dynamic_clients -- Возможно ему уже был выдан адрес
           WHERE client_hw_addr = hw_addr AND server_addr = server_address;
       IF tmp_ip IS NULL
       THEN    -- Новый MAC. Пытаемся выдать ему временный адрес.
           -- Перед этим удаляем все старые аренды
           UPDATE dynamic_clients SET client_hw_addr = NULL
               WHERE (end_lease + delta_time < time_now);

           --Selecting id IP for new temporary client
           SELECT INTO tmp_ip client_addr FROM dynamic_clients
               WHERE server_addr = server_address
               AND client_hw_addr IS NULL;
       ELSE    --Else found already registeged temporary user
           UPDATE dynamic_clients SET end_lease = time_now + lease_time
               WHERE client_hw_addr = hw_addr
               AND server_addr = server_address; --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
           UPDATE dynamic_clients SET client_hw_addr = hw_addr, end_lease = time_now + lease_time
               WHERE client_addr = tmp_ip;
       END IF;
   ELSE    -- MAC адрес зарегистрирован.
           -- Если на данный адрес ранее была выдана аренда из временного диапазона,
           -- то удаляем записи об этой аренде
       UPDATE dynamic_clients SET client_hw_addr = NULL
           WHERE client_hw_addr = hw_addr AND server_addr = server_address;
       -- Вносим информацию об аренде для данного MAC адреса.
       UPDATE static_clients SET end_lease = time_now + lease_time
           WHERE client_hw_addr = hw_addr;
   END IF;
   RETURN out_id; -- Возвращаем id данного адреса для возможности использования его в секции WHERE запроса на аутентификацию клиента
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

ALTER FUNCTION get_id(character varying, bigint, smallint) OWNER TO radius;