CREATE TABLE fr_ippool_status (
	status_id       INTEGER PRIMARY KEY,
	status          VARCHAR(10) NOT NULL
);

INSERT INTO fr_ippool_status (status_id, status) VALUES (1, 'dynamic');
INSERT INTO fr_ippool_status (status_id, status) VALUES (2, 'static');
INSERT INTO fr_ippool_status (status_id, status) VALUES (3, 'declined');
INSERT INTO fr_ippool_status (status_id, status) VALUES (4, 'disabled');

COMMIT;

#
# Table structure for table 'fr_ippool'
#
CREATE TABLE fr_ippool (
	id              BIGINT GENERATED BY DEFAULT AS IDENTITY,
	pool_name       VARCHAR(30) NOT NULL,
	address         VARCHAR(43) DEFAULT '' NOT NULL,
	owner 	        VARCHAR(128) DEFAULT '' NOT NULL,
	gateway         VARCHAR(128) DEFAULT '' NOT NULL,
	expiry_time     TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	status_id       INTEGER DEFAULT 1 REFERENCES fr_ippool_status (status_id),
	counter         INTEGER DEFAULT 1 NOT NULL
);

CREATE INDEX fr_ippool_poolname_expire ON fr_ippool (pool_name, expiry_time);
CREATE INDEX fr_ippool_address ON fr_ippool (address);
CREATE INDEX fr_ippool_poolname_poolkey ON fr_ippool (pool_name, owner, address);

COMMIT;
