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,
	"ctime" timestamp DEFAULT now()
);

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 '',
	"ctime" timestamp DEFAULT now()
);

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,
	"privdata" text DEFAULT '' NOT NULL,
	"newcustid" integer,
	"createstamp" timestamp DEFAULT now(),
	"modifystamp" timestamp DEFAULT now(),
	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 '',
	"createstamp" timestamp DEFAULT now(),
	"modifystamp" timestamp DEFAULT now(),
	"privdata" text DEFAULT '' NOT NULL,
	"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" (
	"id" serial NOT NULL PRIMARY KEY,
	"city" character varying(30) DEFAULT '' NOT NULL,
	"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";

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

REVOKE ALL on "alloctypes" from PUBLIC;
GRANT ALL on "alloctypes" to "kdeugau";
GRANT ALL on "alloctypes" 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
mm	Master block	Master block	999	6750400
in	Internal netblock	Internal netblock	990	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
wc	Reserve for WAN blocks	WAN IP blocks	200	6750400
en	End-use netblock	End-use netblock	100	6750400
me	Dialup netblock	Dialup netblock	101	DIAL-RES
de	Dynamic DSL block	Dynamic DSL block	102	DSL-RES
ce	Dynamic cable block	Dynamic cable block	103	CBL-RES
we	Dynamic WiFi block	Dynamic WiFi block	104	WL-RES
rm	Routing	Routed netblock	500	6750400
pc	Reserve for dynamic-route DSL netblocks	Dynamic-route netblocks	201	6750400
pr	Dynamic-route DSL netblock	Dynamic-route DSL	221	
wr	WAN block	WAN block	220	6750400
\.

--
-- Trigger and matching function to update modifystamp on allocations, poolips
--
CREATE FUNCTION up_modtime () RETURNS OPAQUE AS '
    BEGIN
	NEW.modifystamp := ''now'';
	RETURN NEW;
    END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations
    FOR EACH ROW EXECUTE PROCEDURE up_modtime();

CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips
    FOR EACH ROW EXECUTE PROCEDURE up_modtime();

--
-- User data table - required for proper ACLs
--

CREATE TABLE "users" (
	"username" varchar(16) NOT NULL PRIMARY KEY,
	"password" varchar(16) DEFAULT '',
	"acl" varchar(16) DEFAULT 'b'
);

CREATE TABLE "dns" (
	"ip" inet NOT NULL PRIMARY KEY,
	"hostname" character varying(128),
	"auto" character(1) DEFAULT 'y'
);