CREATE DATABASE ipdb;

\connect ipdb ipdb

CREATE TABLE "customers" (
	"custid" character varying(16) DEFAULT '' NOT NULL,
	"name" character varying(64),
	"street" character varying(25),
	"street2" character varying(25),
	"city" character varying(30),
	"province" character(2),
	"pocode" character varying(7),
	"phone" character varying(15),
	"abuse" character varying(50),
	"def_rdns" character varying(40),
	"description" text,
	Constraint "customers_pkey" Primary Key ("custid")
);

REVOKE ALL on "customers" from PUBLIC;
GRANT ALL on "customers" to "kdeugau";
GRANT ALL on "customers" to "ipdb";

CREATE TABLE "masterblocks" (
	"cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY
);

REVOKE ALL on "masterblocks" from PUBLIC;
GRANT ALL on "masterblocks" to "kdeugau";
GRANT ALL on "masterblocks" to "ipdb";

CREATE TABLE "routed" (
	"cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
	"maskbits" integer DEFAULT 128,
	"city" character varying(30) DEFAULT ''
);

REVOKE ALL on "routed" from PUBLIC;
GRANT ALL on "routed" to "kdeugau";
GRANT ALL on "routed" to "ipdb";

CREATE TABLE "temp" (
	"ofs" integer
);

REVOKE ALL on "temp" from PUBLIC;
GRANT ALL on "temp" to "kdeugau";
GRANT ALL on "temp" to "ipdb";

CREATE TABLE "freeblocks" (
	"cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
	"maskbits" integer DEFAULT 128,
	"city" character varying(30) DEFAULT '',
	"routed" character(1) DEFAULT 'n'
);

REVOKE ALL on "freeblocks" from PUBLIC;
GRANT ALL on "freeblocks" to "kdeugau";
GRANT ALL on "freeblocks" to "ipdb";

CREATE TABLE "poolips" (
	"pool" cidr DEFAULT '255.255.255.255/32' NOT NULL,
	"ip" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
	"custid" character varying(16) DEFAULT '' NOT NULL,
	"city" character varying(30) DEFAULT '' NOT NULL,
	"type" character(2) DEFAULT '' NOT NULL,
	"available" character(1) DEFAULT 'y' NOT NULL,
	"notes" text DEFAULT '' NOT NULL,
	"description" character varying(64) DEFAULT '' NOT NULL,
	"circuitid" character varying(128) DEFAULT '' NOT NULL,
	"newcustid" integer,
	CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
);

REVOKE ALL on "poolips" from PUBLIC;
GRANT ALL on "poolips" to "kdeugau";
GRANT ALL on "poolips" to "ipdb";

CREATE TABLE "allocations" (
	"cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
	"custid" character varying(16) DEFAULT '',
	"type" character(2) DEFAULT '',
	"city" character varying(30) DEFAULT '',
	"description" character varying(64) DEFAULT '',
	"notes" text DEFAULT '',
	"maskbits" integer DEFAULT 128,
	"circuitid" character varying(128) DEFAULT '',
	"newcustid" integer
);

REVOKE ALL on "allocations" from PUBLIC;
GRANT ALL on "allocations" to "kdeugau";
GRANT ALL on "allocations" to "ipdb";

CREATE VIEW "searchme" as SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.type, poolips.city, poolips.description FROM poolips;

REVOKE ALL on "searchme" from PUBLIC;
GRANT ALL on "searchme" to "kdeugau";
GRANT ALL on "searchme" to "ipdb";

CREATE TABLE "alloctypes" (
	"type" character(2) DEFAULT '' NOT NULL PRIMARY KEY,
	"listname" character varying(40) DEFAULT '',
	"dispname" character varying(40) DEFAULT '',
	"listorder" integer DEFAULT 0,
	"def_custid" character varying(16) DEFAULT ''
);

REVOKE ALL on "alloctypes" from PUBLIC;
GRANT ALL on "alloctypes" to "kdeugau";
GRANT ALL on "alloctypes" to "ipdb";

CREATE TABLE "cities" (
	"city" character varying(30) DEFAULT '' NOT NULL PRIMARY KEY,
	"routing" character(1) DEFAULT 'n' NOT NULL
);

REVOKE ALL on "cities" from PUBLIC;
GRANT ALL on "cities" to "kdeugau";
GRANT ALL on "cities" to "ipdb";

COPY "alloctypes" FROM stdin;
cd	Static Pool - Cable	Cable pool	41	CBL-BUS
dp	Static Pool - DSL	DSL pool	42	DSL-BUS
mp	Static Pool - Dialup	Static dialup pool	43	DIAL-BUS
wp	Static Pool - Wireless	Static wireless pool	44	WL-BUS
dc	Dynamic cable block	Dynamic cable block	103	CBL-RES
dy	Dynamic DSL block	Dynamic DSL block	102	DSL-RES
dn	Dialup netblock	Dialup netblock	101	DIAL-RES
dw	Dynamic WiFi block	Dynamic WiFi block	104	WL-RES
mm	Master block	Master block	999	6750400
rr	Routing	Routed netblock	500	6750400
in	Internal netblock	Internal netblock	990	6750400
ee	End-use netblock	End-use netblock	100	6750400
sd	Static Pool - Servers	Server pool	40	6750400
cn	Customer netblock	Customer netblock	0	
ci	Static IP - Cable	Static cable IP	21	
di	Static IP - DSL	Static DSL IP	22	
mi	Static IP - Dialup	Static dialup IP	23	
wi	Static IP - Wireless	Static wireless IP	24	
si	Static IP - Server pool	Server pool IP	20	6750400
\.